X-Git-Url: http://koha-dev.rot13.org:8081/gitweb/?a=blobdiff_plain;f=tools%2Fletter.pl;h=eb9d6a631260044e8ac02f30096a13ff2fdd0920;hb=fb3f624f09c536494ff221618b38e050483363d3;hp=abf85fb406d3b01003d44dc7cbf26d6051e5483b;hpb=e2a41bded10aea743055651ebee01bfb2d3e2048;p=koha_fer diff --git a/tools/letter.pl b/tools/letter.pl index abf85fb406..eb9d6a6312 100755 --- a/tools/letter.pl +++ b/tools/letter.pl @@ -13,311 +13,401 @@ # WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR # A PARTICULAR PURPOSE. See the GNU General Public License for more details. # -# You should have received a copy of the GNU General Public License along with -# Koha; if not, write to the Free Software Foundation, Inc., 59 Temple Place, -# Suite 330, Boston, MA 02111-1307 USA +# You should have received a copy of the GNU General Public License along +# with Koha; if not, write to the Free Software Foundation, Inc., +# 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA. =head1 tools/letter.pl ALGO : this script use an $op to know what to do. - if $op is empty or none of the above values, - - the default screen is build (with all records, or filtered datas). - - the user can clic on add, modify or delete record. + if $op is empty or none of the values listed below, + - the default screen is built (with all or filtered (if search string is set) records). + - the user can click on add, modify or delete record. + - filtering is done on the code field if $op=add_form - - if primkey exists, this is a modification,so we read the $primkey record + - if primary key (module + code) exists, this is a modification,so we read the required record - builds the add/modify form if $op=add_validate - - the user has just send datas, so we create/modify the record + - the user has just send data, so we create/modify the record if $op=delete_form - - we show the record having primkey=$primkey and ask for deletion validation form + - we show the record selected and ask for confirmation if $op=delete_confirm - - we delete the record having primkey=$primkey + - we delete the designated record =cut +# TODO This script drives the CRUD operations on the letter table +# The DB interaction should be handled by calls to C4/Letters.pm + use strict; +use warnings; use CGI; -use C4::Dates; use C4::Auth; use C4::Context; use C4::Output; +use C4::Branch; # GetBranches +use C4::Members::Attributes; + +# _letter_from_where($branchcode,$module, $code) +# - return FROM WHERE clause and bind args for a letter +sub _letter_from_where { + my ($branchcode, $module, $code) = @_; + my $sql = q{FROM letter WHERE branchcode = ? AND module = ? AND code = ?}; + my @args = ($branchcode || '', $module, $code); +# Mysql is retarded. cause branchcode is part of the primary key it cannot be null. How does that +# work with foreign key constraint I wonder... -sub StringSearch { - my ( $searchstring, $type ) = @_; +# if ($branchcode) { +# $sql .= " AND branchcode = ?"; +# push @args, $branchcode; +# } else { +# $sql .= " AND branchcode IS NULL"; +# } + + return ($sql, \@args); +} + +# letter_exists($branchcode,$module, $code) +# - return true if a letter with the given $branchcode, $module and $code exists +sub letter_exists { + my ($sql, $args) = _letter_from_where(@_); my $dbh = C4::Context->dbh; - $searchstring =~ s/\'/\\\'/g; - my @data = split( ' ', $searchstring ); - my $count = @data; - my $sth = - $dbh->prepare( - "Select * from letter where (code like ?) order by module,code"); - $sth->execute("$data[0]%"); - my @results; - my $cnt = 0; - - while ( my $data = $sth->fetchrow_hashref ) { - push( @results, $data ); - $cnt++; - } - $sth->finish; - return ( $cnt, \@results ); + my $letter = $dbh->selectrow_hashref("SELECT * $sql", undef, @$args); + return $letter; +} + +# $protected_letters = protected_letters() +# - return a hashref of letter_codes representing letters that should never be deleted +sub protected_letters { + my $dbh = C4::Context->dbh; + my $codes = $dbh->selectall_arrayref(q{SELECT DISTINCT letter_code FROM message_transports}); + return { map { $_->[0] => 1 } @{$codes} }; } my $input = new CGI; my $searchfield = $input->param('searchfield'); -my $offset = $input->param('offset'); -my $script_name = "/cgi-bin/koha/tools/letter.pl"; +my $script_name = '/cgi-bin/koha/tools/letter.pl'; +my $branchcode = $input->param('branchcode'); my $code = $input->param('code'); my $module = $input->param('module'); my $content = $input->param('content'); -my $pagesize = 20; -my $op = $input->param('op'); -$searchfield =~ s/\,//g; +my $op = $input->param('op') || ''; my $dbh = C4::Context->dbh; -my ( $template, $borrowernumber, $cookie ) = get_template_and_user( +my ( $template, $borrowernumber, $cookie, $staffflags ) = get_template_and_user( { - template_name => "tools/letter.tmpl", + template_name => 'tools/letter.tmpl', query => $input, - type => "intranet", + type => 'intranet', authnotrequired => 0, - flagsrequired => { tools => 1 }, + flagsrequired => { tools => 'edit_notices' }, debug => 1, } ); -if ($op) { - $template->param( - script_name => $script_name, - $op => 1 - ); # we show only the TMPL_VAR names $op +my $my_branch = C4::Context->preference("IndependantBranches") && !$staffflags->{'superlibrarian'} + ? C4::Context->userenv()->{'branch'} + : undef; +# we show only the TMPL_VAR names $op + +$template->param( + independant_branch => $my_branch, + script_name => $script_name, + searchfield => $searchfield, + action => $script_name +); + +if ($op eq 'copy') { + add_copy(); + $op = 'add_form'; +} + +if ($op eq 'add_form') { + add_form($branchcode, $module, $code); +} +elsif ( $op eq 'add_validate' ) { + add_validate(); + $op = q{}; # next operation is to return to default screen +} +elsif ( $op eq 'delete_confirm' ) { + delete_confirm($branchcode, $module, $code); +} +elsif ( $op eq 'delete_confirmed' ) { + delete_confirmed($branchcode, $module, $code); + $op = q{}; # next operation is to return to default screen } else { - $template->param( - script_name => $script_name, - else => 1 - ); # we show only the TMPL_VAR names $op + default_display($branchcode,$searchfield); +} + +# Do this last as delete_confirmed resets +if ($op) { + $template->param($op => 1); +} else { + $template->param(no_op_set => 1); } -$template->param( action => $script_name ); -################## ADD_FORM ################################## -# called by default. Used to create form to add or modify a record -if ( $op eq 'add_form' ) { +output_html_with_http_headers $input, $cookie, $template->output; + +sub add_form { + my ($branchcode,$module, $code ) = @_; - #---- if primkey exists, it's a modify action, so read values to modify... my $letter; + # if code has been passed we can identify letter and its an update action if ($code) { - my $sth = $dbh->prepare("select * from letter where module=? and code=?"); - $sth->execute( $module, $code ); - $letter = $sth->fetchrow_hashref; - $sth->finish; + $letter = letter_exists($branchcode,$module, $code); } - - # build field list - my @SQLfieldname; - my %line = ( 'value' => "LibrarianFirstname", 'text' => 'LibrarianFirstname' ); - push @SQLfieldname, \%line; - my %line = ( 'value' => "LibrarianSurname", 'text' => 'LibrarianSurname' ); - push @SQLfieldname, \%line; - my %line = ( 'value' => "LibrarianEmailaddress", 'text' => 'LibrarianEmailaddress' ); - push @SQLfieldname, \%line; - my $sth2 = $dbh->prepare("SHOW COLUMNS from branches"); - $sth2->execute; - my %line = ( 'value' => "", 'text' => '---BRANCHES---' ); - push @SQLfieldname, \%line; - - while ( ( my $field ) = $sth2->fetchrow_array ) { - my %line = ( 'value' => "branches." . $field, 'text' => "branches." . $field ); - push @SQLfieldname, \%line; + if ($letter) { + $template->param( modify => 1 ); + $template->param( code => $letter->{code} ); + } + else { # initialize the new fields + $letter = { + branchcode => $branchcode, + module => $module, + }; + $template->param( adding => 1 ); } - # add acquisition specific tables - if ( index( $module, "acquisition" ) > 0 ) { - $sth2 = $dbh->prepare("SHOW COLUMNS from aqbooksellers"); - $sth2->execute; - my %line = ( 'value' => "", 'text' => '---BOOKSELLERS---' ); - push @SQLfieldname, \%line; - while ( ( my $field ) = $sth2->fetchrow_array ) { - my %line = ( - 'value' => "aqbooksellers." . $field, - 'text' => "aqbooksellers." . $field - ); - push @SQLfieldname, \%line; - } - $sth2 = $dbh->prepare("SHOW COLUMNS from aqorders"); - $sth2->execute; - my %line = ( 'value' => "", 'text' => '---ORDERS---' ); - push @SQLfieldname, \%line; - while ( ( my $field ) = $sth2->fetchrow_array ) { - my %line = ( - 'value' => "aqorders." . $field, - 'text' => "aqorders." . $field - ); - push @SQLfieldname, \%line; - } - - # add issues specific tables + my $field_selection; + push @{$field_selection}, add_fields('branches'); + if ($module eq 'reserves') { + push @{$field_selection}, add_fields('borrowers', 'reserves', 'biblio', 'items'); } - elsif ( index( $module, "issues" ) > 0 ) { - $sth2 = $dbh->prepare("SHOW COLUMNS from aqbooksellers"); - $sth2->execute; - my %line = ( 'value' => "", 'text' => '---BOOKSELLERS---' ); - push @SQLfieldname, \%line; - while ( ( my $field ) = $sth2->fetchrow_array ) { - my %line = ( - 'value' => "aqbooksellers." . $field, - 'text' => "aqbooksellers." . $field - ); - push @SQLfieldname, \%line; - } - $sth2 = $dbh->prepare("SHOW COLUMNS from serial"); - $sth2->execute; - my %line = ( 'value' => "", 'text' => '---SERIALS---' ); - push @SQLfieldname, \%line; - while ( ( my $field ) = $sth2->fetchrow_array ) { - my %line = ( 'value' => "serial." . $field, 'text' => "serial." . $field ); - push @SQLfieldname, \%line; - } - $sth2 = $dbh->prepare("SHOW COLUMNS from subscription"); - $sth2->execute; - my %line = ( 'value' => "", 'text' => '---SUBSCRIPTION---' ); - push @SQLfieldname, \%line; - while ( ( my $field ) = $sth2->fetchrow_array ) { - my %line = ( - 'value' => "subscription." . $field, - 'text' => "subscription." . $field - ); - push @SQLfieldname, \%line; - } - my %line = ('value' => "", 'text' => '---Biblio---' ); - push @SQLfieldname, \%line; - foreach(qw(title author serial)) { - my %line = ('value' => "biblio.$_", 'text' => ucfirst($_)); - push @SQLfieldname, \%line; - } + elsif ($module eq 'claimacquisition') { + push @{$field_selection}, add_fields('aqbooksellers', 'aqorders', 'biblio', 'biblioitems'); } - else { - $sth2 = $dbh->prepare("SHOW COLUMNS from biblio"); - $sth2->execute; - my %line = ( 'value' => "", 'text' => '---BIBLIO---' ); - - push @SQLfieldname, \%line; - while ( ( my $field ) = $sth2->fetchrow_array ) { - -# note : %line is redefined, otherwise \%line contains the same value for every entry of the list - my %line = ( 'value' => "biblio." . $field, 'text' => "biblio." . $field ); - push @SQLfieldname, \%line; - } - $sth2 = $dbh->prepare("SHOW COLUMNS from biblioitems"); - $sth2->execute; - my %line = ( 'value' => "", 'text' => '---BIBLIOITEMS---' ); - push @SQLfieldname, \%line; - while ( ( my $field ) = $sth2->fetchrow_array ) { - my %line = ( - 'value' => "biblioitems." . $field, - 'text' => "biblioitems." . $field - ); - push @SQLfieldname, \%line; - } - my %line = ( 'value' => "", 'text' => '---ITEMS---' ); - push @SQLfieldname, \%line; - my %line = ( 'value' => "items.content", 'text' => 'items.content' ); - push @SQLfieldname, \%line; - - $sth2 = $dbh->prepare("SHOW COLUMNS from borrowers"); - $sth2->execute; - my %line = ( 'value' => "", 'text' => '---BORROWERS---' ); - push @SQLfieldname, \%line; - while ( ( my $field ) = $sth2->fetchrow_array ) { - my %line = ( - 'value' => "borrowers." . $field, - 'text' => "borrowers." . $field - ); - push @SQLfieldname, \%line; + elsif ($module eq 'claimissues') { + push @{$field_selection}, add_fields('aqbooksellers', 'serial', 'subscription'); + push @{$field_selection}, + { + value => q{}, + text => '---BIBLIO---' + }; + foreach(qw(title author serial)) { + push @{$field_selection}, {value => "biblio.$_", text => ucfirst $_ }; } } - if ($code) { - $template->param( modify => 1 ); - $template->param( code => $letter->{code} ); + elsif ($module eq 'suggestions') { + push @{$field_selection}, add_fields('suggestions', 'borrowers', 'biblio'); } else { - $template->param( adding => 1 ); + push @{$field_selection}, add_fields('biblio','biblioitems'), + {value => q{}, text => '---ITEMS---' }, + {value => 'items.content', text => 'items.content'}, + add_fields('issues','borrowers'); + if ($module eq 'circulation') { + push @{$field_selection}, add_fields('opac_news'); + } } + $template->param( - name => $letter->{name}, - title => $letter->{title}, - content => ( $content ? $content : $letter->{content} ), - ( $module ? $module : $letter->{module} ) => 1, - SQLfieldname => \@SQLfieldname, + branchcode => $letter->{branchcode}, + name => $letter->{name}, + is_html => $letter->{is_html}, + title => $letter->{title}, + content => $letter->{content}, + module => $module, + $module => 1, + branchloop => _branchloop($branchcode), + SQLfieldname => $field_selection, ); -################## ADD_VALIDATE ################################## - # called by add_form, used to insert/modify data in DB + return; } -elsif ( $op eq 'add_validate' ) { - my $dbh = C4::Context->dbh; - my $sth = - $dbh->prepare( - "replace letter (module,code,name,title,content) values (?,?,?,?,?)"); - $sth->execute( - $input->param('module'), $input->param('code'), - $input->param('name'), $input->param('title'), - $input->param('content') + +sub add_validate { + my $dbh = C4::Context->dbh; + my $oldbranchcode = $input->param('oldbranchcode'); + my $branchcode = $input->param('branchcode') || ''; + my $module = $input->param('module'); + my $oldmodule = $input->param('oldmodule'); + my $code = $input->param('code'); + my $name = $input->param('name'); + my $is_html = $input->param('is_html'); + my $title = $input->param('title'); + my $content = $input->param('content'); + if (letter_exists($oldbranchcode,$oldmodule, $code)) { + $dbh->do( + q{UPDATE letter SET branchcode = ?, module = ?, name = ?, is_html = ?, title = ?, content = ? WHERE branchcode = ? AND module = ? AND code = ?}, + undef, + $branchcode, $module, $name, $is_html || 0, $title, $content, + $oldbranchcode, $oldmodule, $code + ); + } else { + $dbh->do( + q{INSERT INTO letter (branchcode,module,code,name,is_html,title,content) VALUES (?,?,?,?,?,?,?)}, + undef, + $branchcode, $module, $code, $name, $is_html || 0, $title, $content + ); + } + # set up default display + default_display($branchcode); +} + +sub add_copy { + my $dbh = C4::Context->dbh; + my $oldbranchcode = $input->param('oldbranchcode'); + my $branchcode = $input->param('branchcode'); + my $module = $input->param('module'); + my $code = $input->param('code'); + + return if letter_exists($branchcode,$module, $code); + + my $old_letter = letter_exists($oldbranchcode,$module, $code); + + $dbh->do( + q{INSERT INTO letter (branchcode,module,code,name,is_html,title,content) VALUES (?,?,?,?,?,?,?)}, + undef, + $branchcode, $module, $code, $old_letter->{name}, $old_letter->{is_html}, $old_letter->{title}, $old_letter->{content} ); - $sth->finish; - print $input->redirect("letter.pl"); - exit; -################## DELETE_CONFIRM ################################## - # called by default form, used to confirm deletion of data in DB } -elsif ( $op eq 'delete_confirm' ) { + +sub delete_confirm { + my ($branchcode, $module, $code) = @_; my $dbh = C4::Context->dbh; - my $sth = $dbh->prepare("select * from letter where code=?"); - $sth->execute($code); - my $data = $sth->fetchrow_hashref; - $sth->finish; + my $letter = letter_exists($branchcode, $module, $code); + $template->param( branchcode => $branchcode, branchname => GetBranchName($branchcode) ); $template->param( code => $code ); - foreach (qw(module name content)) { - $template->param( $_ => $data->{$_} ); - } -################## DELETE_CONFIRMED ################################## - # called by delete_confirm, used to effectively confirm deletion of data in DB + $template->param( module => $module); + $template->param( name => $letter->{name}); + return; } -elsif ( $op eq 'delete_confirmed' ) { + +sub delete_confirmed { + my ($branchcode, $module, $code) = @_; + my ($sql, $args) = _letter_from_where($branchcode, $module, $code); my $dbh = C4::Context->dbh; - my $code = uc( $input->param('code') ); - my $module = $input->param('module'); - my $sth = $dbh->prepare("delete from letter where module=? and code=?"); - $sth->execute( $module, $code ); - $sth->finish; - print $input->redirect("/cgi-bin/koha/tools/letter.pl"); + $dbh->do("DELETE $sql", undef, @$args); + # setup default display for screen + default_display($branchcode); return; -################## DEFAULT ################################## } -else { # DEFAULT - if ( $searchfield ne '' ) { + +sub retrieve_letters { + my ($branchcode, $searchstring) = @_; + + $branchcode = $my_branch if $branchcode && $my_branch; + + my $dbh = C4::Context->dbh; + my ($sql, @where, @args); + $sql = "SELECT branchcode, module, code, name, branchname + FROM letter + LEFT OUTER JOIN branches USING (branchcode)"; + if ($searchstring && $searchstring=~m/(\S+)/) { + $searchstring = $1 . q{%}; + push @where, 'code LIKE ?'; + push @args, $searchstring; + } + elsif ($branchcode) { + push @where, 'branchcode = ?'; + push @args, $branchcode || ''; + } + elsif ($my_branch) { + push @where, "(branchcode = ? OR branchcode = '')"; + push @args, $my_branch; + } + + $sql .= " WHERE ".join(" AND ", @where) if @where; + $sql .= " ORDER BY module, code, branchcode"; +# use Data::Dumper; die Dumper($sql, \@args); + return $dbh->selectall_arrayref($sql, { Slice => {} }, @args); +} + +sub default_display { + my ($branchcode, $searchfield) = @_; + + if ( $searchfield ) { $template->param( search => 1 ); - $template->param( searchfield => $searchfield ); } - my ( $count, $results ) = StringSearch( $searchfield, 'web' ); - my $toggle = 0; - my @loop_data = (); - for ( - my $i = $offset ; - $i < ( $offset + $pagesize < $count ? $offset + $pagesize : $count ) ; - $i++ - ) - { - $toggle = ($toggle) ? 0 : 1; - my %row_data; - $row_data{toggle} = $toggle; - foreach (qw(module code name)) { - $row_data{$_} = $results->[$i]{$_}; - } - push( @loop_data, \%row_data ); + my $results = retrieve_letters($branchcode,$searchfield); + + my $loop_data = []; + my $protected_letters = protected_letters(); + foreach my $row (@{$results}) { + $row->{protected} = !$row->{branchcode} && $protected_letters->{ $row->{code} }; + push @{$loop_data}, $row; + } - $template->param( letter => \@loop_data ); -} #---- END $OP eq DEFAULT -output_html_with_http_headers $input, $cookie, $template->output; + $template->param( + letter => $loop_data, + branchloop => _branchloop($branchcode), + ); +} + +sub _branchloop { + my ($branchcode) = @_; + my $branches = GetBranches(); + my @branchloop; + for my $thisbranch (sort { $branches->{$a}->{branchname} cmp $branches->{$b}->{branchname} } keys %$branches) { + push @branchloop, { + value => $thisbranch, + selected => $branchcode && $thisbranch eq $branchcode, + branchname => $branches->{$thisbranch}->{'branchname'}, + }; + } + + return \@branchloop; +} + +sub add_fields { + my @tables = @_; + my @fields = (); + + for my $table (@tables) { + push @fields, get_columns_for($table); + + } + return @fields; +} + +sub get_columns_for { + my $table = shift; +# FIXME untranslateable + my %column_map = ( + aqbooksellers => '---BOOKSELLERS---', + aqorders => '---ORDERS---', + serial => '---SERIALS---', + reserves => '---HOLDS---', + suggestions => '---SUGGESTIONS---', + ); + my @fields = (); + if (exists $column_map{$table} ) { + push @fields, { + value => q{}, + text => $column_map{$table} , + }; + } + else { + my $tlabel = '---' . uc $table; + $tlabel.= '---'; + push @fields, { + value => q{}, + text => $tlabel, + }; + } + + my $sql = "SHOW COLUMNS FROM $table";# TODO not db agnostic + my $table_prefix = $table . q|.|; + my $rows = C4::Context->dbh->selectall_arrayref($sql, { Slice => {} }); + for my $row (@{$rows}) { + next if $row->{'Field'} eq 'timestamp'; # this is really an irrelevant field and there may be other common fields that should be excluded from the list + push @fields, { + value => $table_prefix . $row->{Field}, + text => $table_prefix . $row->{Field}, + } + } + if ($table eq 'borrowers') { + if ( my $attributes = C4::Members::Attributes::GetAttributes() ) { + foreach (@$attributes) { + push @fields, { + value => "borrower-attribute:$_", + text => "attribute:$_", + } + } + } + } + return @fields; +}