From fab7f668cee86a0c85c0a86d296e143117c7e795 Mon Sep 17 00:00:00 2001 From: Jonathan Druart Date: Mon, 5 Nov 2012 13:58:59 +0100 Subject: [PATCH] Bug 9016: Multi transport types for notices The goal of the patch: This patch allows to choose one (or more) message transport type for one overdue. Currently it is just possible to choose a letter. To prevent too many letters, we create an association table between overduerules and message_transport_types and change the primary key for the letter table. Like this, we are able to have an unique letter code for several transport types. The idea of this patch is to have a sms driver to send messages with a transport type defined as "sms". This patch: - adds a new jquery plugin : insertatcaret - adds datatable on the tools/letter.pl page - adds a new column letter.message_transport_type - adds a new table overduerules_transport_types : association table between overduerules and message_transport_types. - modifies the primary key for the letter table (become module, code, branchcode, message_transport_type) - rewrites a big part of the tools/overduerules.tt code The migration problem: A new updatedatabase entry changes the DB structures changes and set all the letter.message_transport_type to 'email'. The email type is the default type everywhere in the code. To test: 1/ Check that all your previous overdue rules are preserved 2/ Check that the tables on the tools/overduerules.pl page are filled with the email transport type (default type). 3/ On the tools/letters.pl check that your previous letters still exist. 4/ Now check the features :) The patch allows you to sent letters to the queue with an email or sms format. So try to create letters with 1 or both of these transport types and check the corresponding checkbox on the overdues management page. Play with the script misc/cronjobs/overdue_notices.pl in order to see yours notices in the message_queue table. --- C4/Letters.pm | 60 ++-- C4/Overdues.pm | 24 ++ installer/data/mysql/updatedatabase.pl | 56 ++++ .../en/lib/jquery/plugins/jquery.insertatcaret.js | 46 +++ .../intranet-tmpl/prog/en/modules/tools/letter.tt | 329 ++++++++++---------- .../prog/en/modules/tools/overduerules.tt | 202 +++++------- misc/cronjobs/overdue_notices.pl | 132 ++++---- tools/letter.pl | 165 ++++++---- tools/overduerules.pl | 126 ++++++-- 9 files changed, 698 insertions(+), 442 deletions(-) create mode 100644 koha-tmpl/intranet-tmpl/prog/en/lib/jquery/plugins/jquery.insertatcaret.js diff --git a/C4/Letters.pm b/C4/Letters.pm index f646241..4cf1f75 100644 --- a/C4/Letters.pm +++ b/C4/Letters.pm @@ -42,7 +42,7 @@ BEGIN { $VERSION = 3.07.00.049; @ISA = qw(Exporter); @EXPORT = qw( - &GetLetters &GetPreparedLetter &GetWrappedLetter &addalert &getalert &delalert &findrelatedto &SendAlerts &GetPrintMessages + &GetLetters &GetPreparedLetter &GetWrappedLetter &addalert &getalert &delalert &findrelatedto &SendAlerts &GetPrintMessages &GetMessageTransportTypes ); } @@ -97,20 +97,19 @@ $template->param(LETTERLOOP => \@letterloop); sub GetLetters { # returns a reference to a hash of references to ALL letters... - my $cat = shift; + my ( $cat, $message_transport_type ) = @_; + $message_transport_type ||= 'email'; my %letters; my $dbh = C4::Context->dbh; my $sth; - if (defined $cat) { - my $query = "SELECT * FROM letter WHERE module = ? ORDER BY name"; - $sth = $dbh->prepare($query); - $sth->execute($cat); - } - else { - my $query = "SELECT * FROM letter ORDER BY name"; - $sth = $dbh->prepare($query); - $sth->execute; - } + my $query = qq{ + SELECT * FROM letter WHERE + }; + $query .= qq{ module = ? AND } if defined $cat; + $query .= qq{ message_transport_type = ? ORDER BY name}; + $sth = $dbh->prepare($query); + $sth->execute((defined $cat ? $cat : ()), $message_transport_type); + while ( my $letter = $sth->fetchrow_hashref ) { $letters{ $letter->{'code'} } = $letter->{'name'}; } @@ -146,7 +145,8 @@ sub GetLetter { my %letter; sub getletter { - my ( $module, $code, $branchcode ) = @_; + my ( $module, $code, $branchcode, $message_transport_type ) = @_; + $message_transport_type ||= 'email'; $branchcode ||= ''; @@ -157,17 +157,22 @@ sub getletter { $branchcode = C4::Context->userenv->{'branch'}; } - if ( my $l = $letter{$module}{$code}{$branchcode} ) { + if ( my $l = $letter{$module}{$code}{$branchcode}{$message_transport_type} ) { return { %$l }; # deep copy } my $dbh = C4::Context->dbh; - my $sth = $dbh->prepare("select * from letter where module=? and code=? and (branchcode = ? or branchcode = '') order by branchcode desc limit 1"); - $sth->execute( $module, $code, $branchcode ); + my $sth = $dbh->prepare(qq{ + SELECT * + FROM letter + WHERE module=? AND code=? AND (branchcode = ? OR branchcode = '') AND message_transport_type = ? + ORDER BY branchcode DESC LIMIT 1 + }); + $sth->execute( $module, $code, $branchcode, $message_transport_type ); my $line = $sth->fetchrow_hashref or return; $line->{'content-type'} = 'text/html; charset="UTF-8"' if $line->{is_html}; - $letter{$module}{$code}{$branchcode} = $line; + $letter{$module}{$code}{$branchcode}{$message_transport_type} = $line; return { %$line }; } @@ -473,7 +478,7 @@ sub GetPreparedLetter { my $substitute = $params{substitute}; my $repeat = $params{repeat}; - my $letter = getletter( $module, $letter_code, $branchcode ) + my $letter = getletter( $module, $letter_code, $branchcode, $params{message_transport_type} ) or warn( "No $module $letter_code letter"), return; @@ -916,6 +921,25 @@ ENDSQL return $sth->fetchall_arrayref({}); } +=head2 GetMessageTransportTypes + + my @mtt = GetMessageTransportTypes(); + + returns a list of hashes + +=cut + +sub GetMessageTransportTypes { + my $dbh = C4::Context->dbh(); + my $sth = $dbh->prepare(" + SELECT message_transport_type + FROM message_transport_types + ORDER BY message_transport_type + "); + $sth->execute; + return $sth->fetchall_arrayref({}); +} + =head2 _add_attachements named parameters: diff --git a/C4/Overdues.pm b/C4/Overdues.pm index ac66c36..5fe74df 100644 --- a/C4/Overdues.pm +++ b/C4/Overdues.pm @@ -60,6 +60,7 @@ BEGIN { &GetOverduesForBranch &RemoveNotifyLine &AddNotifyLine + &GetOverdueMessageTransportTypes ); # subs to remove push @EXPORT, qw( @@ -1331,6 +1332,29 @@ sub RemoveNotifyLine { return 1; } +=head2 GetOverdueMessageTransportTypes + + my $message_transport_types = GetOverdueMessageTransportTypes( $branchcode, $categorycode, $letternumber); + + return a arrayref with message_transport_type for given branchcode, categorycode and letternumber(1,2 or 3) + +=cut + +sub GetOverdueMessageTransportTypes { + my ( $branchcode, $categorycode, $letternumber ) = @_; + my $dbh = C4::Context->dbh; + my $sth = $dbh->prepare(" + SELECT message_transport_type FROM overduerules_transport_types + WHERE branchcode = ? AND categorycode = ? AND letternumber = ? + "); + $sth->execute( $branchcode, $categorycode, $letternumber ); + my @mtts; + while ( my $mtt = $sth->fetchrow ) { + push @mtts, $mtt; + } + return \@mtts; +} + 1; __END__ diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl index 7ab48ee..dc7d77f 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -6020,6 +6020,62 @@ if (C4::Context->preference("Version") < TransformToNum($DBversion)) { SetVersion ($DBversion); } + + + + +$DBversion = "3.09.00.XXX"; +if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) { + + $dbh->do( qq{ + ALTER TABLE letter ADD COLUMN message_transport_type VARCHAR(20) NOT NULL DEFAULT 'email' + } ); + + $dbh->do( qq{ + ALTER TABLE letter ADD CONSTRAINT message_transport_type_fk FOREIGN KEY (message_transport_type) REFERENCES message_transport_types(message_transport_type); + } ); + + $dbh->do( qq{ + ALTER TABLE letter DROP PRIMARY KEY, ADD PRIMARY KEY (`module`,`code`,`branchcode`, message_transport_type); + } ); + + $dbh->do( qq{ + CREATE TABLE overduerules_transport_types( + id INT(11) NOT NULL AUTO_INCREMENT, + branchcode varchar(10) NOT NULL DEFAULT '', + categorycode VARCHAR(10) NOT NULL DEFAULT '', + letternumber INT(1) NOT NULL DEFAULT 1, + message_transport_type VARCHAR(20) NOT NULL DEFAULT 'email', + PRIMARY KEY (id), + CONSTRAINT overduerules_fk FOREIGN KEY (branchcode, categorycode) REFERENCES overduerules (branchcode, categorycode) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT mtt_fk FOREIGN KEY (message_transport_type) REFERENCES message_transport_types (message_transport_type) ON DELETE CASCADE ON UPDATE CASCADE + ) ENGINE=InnoDB DEFAULT CHARSET=utf8; + } ); + + my $sth = $dbh->prepare( qq{ + SELECT * FROM overduerules; + } ); + + $sth->execute; + my $sth_insert_mtt = $dbh->prepare( qq{ + INSERT INTO overduerules_transport_types (branchcode, categorycode, letternumber, message_transport_type) VALUES ( ?, ?, ?, ? ) + } ); + while ( my $row = $sth->fetchrow_hashref ) { + my $branchcode = $row->{branchcode}; + my $categorycode = $row->{categorycode}; + for my $letternumber ( 1..3 ) { + next unless $row->{"letter$letternumber"}; + $sth_insert_mtt->execute( + $branchcode, $categorycode, $letternumber, 'email' + ); + } + } + + print "Upgrade done (Adds tables and/or fields between letters and messages_transport_types)\n"; + SetVersion($DBversion); +} + + =head1 FUNCTIONS =head2 TableExists($table) diff --git a/koha-tmpl/intranet-tmpl/prog/en/lib/jquery/plugins/jquery.insertatcaret.js b/koha-tmpl/intranet-tmpl/prog/en/lib/jquery/plugins/jquery.insertatcaret.js new file mode 100644 index 0000000..408eb5d --- /dev/null +++ b/koha-tmpl/intranet-tmpl/prog/en/lib/jquery/plugins/jquery.insertatcaret.js @@ -0,0 +1,46 @@ +/*! + * jQuery insertAtCaret + * Allows inserting text where the caret is in a textarea + * Copyright (c) 2003-2010 phpMyAdmin devel team + * Version: 1.0 + * Developed by the phpMyAdmin devel team. Modified by Alex King and variaas + * http://alexking.org/blog/2003/06/02/inserting-at-the-cursor-using-javascript + * http://www.mail-archive.com/jquery-en@googlegroups.com/msg08708.html + * Licensed under the GPL license: + * http://www.gnu.org/licenses/gpl.html + */ +;(function($) { + +$.fn.insertAtCaret = function (myValue) { + + return this.each(function() { + + //IE support + if (document.selection) { + + this.focus(); + sel = document.selection.createRange(); + sel.text = myValue; + this.focus(); + + } else if (this.selectionStart || this.selectionStart == '0') { + + //MOZILLA / NETSCAPE support + var startPos = this.selectionStart; + var endPos = this.selectionEnd; + var scrollTop = this.scrollTop; + this.value = this.value.substring(0, startPos)+ myValue+ this.value.substring(endPos,this.value.length); + this.focus(); + this.selectionStart = startPos + myValue.length; + this.selectionEnd = startPos + myValue.length; + this.scrollTop = scrollTop; + + } else { + + this.value += myValue; + this.focus(); + } + }); +}; + +})(jQuery); diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/tools/letter.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/tools/letter.tt index c156e01..23f777e 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/modules/tools/letter.tt +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/tools/letter.tt @@ -1,15 +1,29 @@ [% INCLUDE 'doc-head-open.inc' %] Koha › Tools › Notices[% IF ( add_form ) %][% IF ( modify ) %] › Modify notice[% ELSE %] › Add notice[% END %][% END %][% IF ( add_validate ) %] › Notice added[% END %][% IF ( delete_confirm ) %] › Confirm deletion[% END %] + [% INCLUDE 'doc-head-close.inc' %] - + + +[% INCLUDE 'datatables-strings.inc' %] + @@ -162,64 +158,66 @@ $(document).ready(function() { [% END %] [% END %] - - - - - - - - - - - - [% FOREACH lette IN letter %] - [% can_edit = lette.branchcode || !independant_branch %] - [% UNLESS ( loop.odd ) %] - - [% ELSE %] - - [% END %] - - - - - - - [% END %] - -
LibraryModuleCodeNameCopy notice  
[% lette.branchname || "(All libraries)" %][% lette.module %][% lette.code %][% lette.name %] - [% IF !independant_branch || !lette.branchcode %] -
+ [% IF letter %] + + + + + + + + + + + + + + [% FOREACH lette IN letter %] + [% can_edit = lette.branchcode || !independant_branch %] + + + + + + - - + + + + [% END %] + +
LibraryModuleCodeNameCopy notice  
[% lette.branchname || "(All libraries)" %][% lette.module %][% lette.code %][% lette.name %] + [% IF !independant_branch || !lette.branchcode %] + - + - [% IF independant_branch %] - - [% ELSE %] - [% select_for_copy %] - [% END %] + [% IF independant_branch %] + + [% ELSE %] + [% select_for_copy %] + [% END %] - - [% END %] - - [% IF can_edit %] - Edit - [% END %] - - [% IF !lette.protected && can_edit %] - Delete + + [% END %] + + [% IF can_edit %] + Edit + [% END %] + + [% IF !lette.protected && can_edit %] + Delete + [% END %] +
+ [% ELSE %] + There are no notice or slip for this library. [% END %] -
[% END %] [% IF ( add_form ) %]

[% IF ( modify ) %]Modify notice[% ELSE %]Add notice[% END %]

-
+ [% IF ( modify ) %] @@ -247,45 +245,45 @@ $(document).ready(function() { [% IF ( modify ) %][% END %] - [% IF ( catalogue ) %] - + [% IF ( module == "catalogue" ) %] + [% ELSE %] - + [% END %] - [% IF ( circulation ) %] - + [% IF ( module == "circulation" ) %] + [% ELSE %] - + [% END %] - [% IF ( claimacquisition ) %] - + [% IF ( module == "claimacquisition" ) %] + [% ELSE %] - + [% END %] - [% IF ( claimissues ) %] - + [% IF ( module == "claimissues" ) %] + [% ELSE %] - + [% END %] - [% IF ( reserves ) %] - + [% IF ( module == "reserves" ) %] + [% ELSE %] - + [% END %] - [% IF ( members ) %] - + [% IF ( module == "members" ) %] + [% ELSE %] - + [% END %] - [% IF ( serial ) %] - + [% IF ( module == "serial" ) %] + [% ELSE %] - + [% END %] - [% IF ( suggestions ) %] - + [% IF ( module == "suggestions" ) %] + [% ELSE %] - + [% END %] @@ -295,33 +293,49 @@ $(document).ready(function() {
  • -
  • - - [% IF is_html %] - - [% ELSE %] - - [% END %] -
  • -
  • - -
  • -
  • - -
  • -
  • - -
    -
  • - - [% IF code.search('DGST') %] Warning, this is a template for a Digest, as such, any references to branch data ( e.g. branches.branchname ) will refer to the borrower's home branch. [% END %] + [% FOREACH letter IN letters %] +
  • +
    + [% letter.message_transport_type %] +
      +
    1. + + + [% IF letter.is_html %] + + [% ELSE %] + + [% END %] +
    2. +
    3. + +
    4. +
    5. + + + + + + + +
      + +
      +
    6. +
    +
    +
  • + [% END %] + + + [% IF code.search('DGST') %] Warning, this is a template for a Digest, as such, any references to branch data ( e.g. branches.branchname ) will refer to the borrower's home branch. [% END %] -
    Cancel
    +
    Cancel
    [% END %] @@ -356,6 +370,7 @@ $(document).ready(function() { + diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/tools/overduerules.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/tools/overduerules.tt index 12b53c3..22597de 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/modules/tools/overduerules.tt +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/tools/overduerules.tt @@ -4,12 +4,17 @@ @@ -72,131 +77,72 @@ $(document).ready(function() { [% IF ( datasaved ) %]
    INPUT SAVED
    [% END %]
    - + + [% FOR tab IN tabs %] +
    + + + + + + + + [% FOREACH mtt IN message_transport_types %] + + [% END %] + + + + [% FOREACH value IN tab.values %] + + + + + + [% FOREACH mtt IN value.message_transport_types %] + + [% END %] + + [% END %] + +
     DelayLetterRestrict[% mtt.message_transport_type %]
    [% value.line %] + + + [% IF ( value.noletter ) %] + + [% ELSE %] + + [% END %] + + [% IF ( value.debarred ) %] + + [% ELSE %] + + [% END %] + + [% IF mtt.selected %] + + [% ELSE %] + + [% END %] +
    +
    + [% END %] -
    - - [% FOREACH tabl IN table %] - [% UNLESS ( loop.odd ) %] - - [% ELSE %] - - [% END %] - - - - - - [% END %] -
     DelayFirst letterRestrict
    [% tabl.line %] - - - [% IF ( tabl.noletter ) %] - - [% ELSE %] - - [% END %] - - [% IF ( tabl.debarred1 ) %] - - [% ELSE %] - - [% END %] -
    -
    -
    - - [% FOREACH tabl IN table %] - [% UNLESS ( loop.odd ) %] - - [% ELSE %] - - [% END %] - - - - - - [% END %] -
     DelaySecond letterRestrict
    [% tabl.line %] - - - [% IF ( tabl.noletter ) %] - - [% ELSE %] - - [% END %] - - [% IF ( tabl.debarred2 ) %] - - [% ELSE %] - - [% END %] -
    -
    - -
    - - [% FOREACH tabl IN table %] - [% UNLESS ( loop.odd ) %] - - [% ELSE %] - - [% END %] - - - - - - [% END %] -
     DelayThird letterRestrict
    [% tabl.line %] - - - [% IF ( tabl.noletter ) %] - - [% ELSE %] - - [% END %] - - [% IF ( tabl.debarred3 ) %] - - [% ELSE %] - - [% END %] -
    -
    diff --git a/misc/cronjobs/overdue_notices.pl b/misc/cronjobs/overdue_notices.pl index e83bd39..62f4d39 100755 --- a/misc/cronjobs/overdue_notices.pl +++ b/misc/cronjobs/overdue_notices.pl @@ -39,7 +39,7 @@ use C4::Context; use C4::Dates qw/format_date/; use C4::Debug; use C4::Letters; -use C4::Overdues qw(GetFine); +use C4::Overdues qw(GetFine GetOverdueMessageTransportTypes); =head1 NAME @@ -417,7 +417,7 @@ END_SQL # my $outfile = 'overdues_' . ( $mybranch || $branchcode || 'default' ); while ( my $overdue_rules = $rqoverduerules->fetchrow_hashref ) { - PERIOD: foreach my $i ( 1 .. 3 ) { + PERIOD: foreach my $i ( 1..3 ) { $verbose and warn "branch '$branchcode', pass $i\n"; my $mindays = $overdue_rules->{"delay$i"}; # the notice will be sent after mindays days (grace period) @@ -507,68 +507,45 @@ END_SQL } $sth2->finish; - my $letter = parse_letter( - { letter_code => $overdue_rules->{"letter$i"}, - letter => $letter_template, - borrowernumber => $borrowernumber, - branchcode => $branchcode, - items => \@items, - substitute => { # this appears to be a hack to overcome incomplete features in this code. - bib => $branch_details->{'branchname'}, # maybe 'bib' is a typo for 'lib'? - 'items.content' => $titles, - 'count' => $itemcount, - } + my @message_transport_types = @{ GetOverdueMessageTransportTypes( $branchcode, $overdue_rules->{categorycode}, $i) }; + + for my $mtt ( @message_transport_types ) { + + my $letter = parse_letter( + { letter_code => $overdue_rules->{"letter$i"}, + letter => $letter_template, + borrowernumber => $borrowernumber, + branchcode => $branchcode, + items => \@items, + substitute => { # this appears to be a hack to overcome incomplete features in this code. + bib => $branch_details->{'branchname'}, # maybe 'bib' is a typo for 'lib'? + 'items.content' => $titles, + 'count' => $itemcount, + }, + message_transport_type => $mtt, + } + ); + unless ($letter) { + $verbose and warn "Message '$overdue_rules->{letter$i}' content not found"; + + # might as well skip while PERIOD, no other borrowers are going to work. + # FIXME : Does this mean a letter must be defined in order to trigger a debar ? + next PERIOD; } - ); - unless ($letter) { - $verbose and warn "Message '$overdue_rules->{letter$i}' content not found"; - # might as well skip while PERIOD, no other borrowers are going to work. - # FIXME : Does this mean a letter must be defined in order to trigger a debar ? - next PERIOD; - } - - if ( $exceededPrintNoticesMaxLines ) { - $letter->{'content'} .= "List too long for form; please check your account online for a complete list of your overdue items."; - } + if ( $exceededPrintNoticesMaxLines ) { + $letter->{'content'} .= "List too long for form; please check your account online for a complete list of your overdue items."; + } + + my @misses = grep { /./ } map { /^([^>]*)[>]+/; ( $1 || '' ); } split /\{'content'}; + if (@misses) { + $verbose and warn "The following terms were not matched and replaced: \n\t" . join "\n\t", @misses; + } + $letter->{'content'} =~ s/\<[^<>]*?\>//g; # Now that we've warned about them, remove them. + $letter->{'content'} =~ s/\<[^<>]*?\>//g; # 2nd pass for the double nesting. + + if ($nomail) { - my @misses = grep { /./ } map { /^([^>]*)[>]+/; ( $1 || '' ); } split /\{'content'}; - if (@misses) { - $verbose and warn "The following terms were not matched and replaced: \n\t" . join "\n\t", @misses; - } - $letter->{'content'} =~ s/\<[^<>]*?\>//g; # Now that we've warned about them, remove them. - $letter->{'content'} =~ s/\<[^<>]*?\>//g; # 2nd pass for the double nesting. - - if ($nomail) { - - push @output_chunks, - prepare_letter_for_printing( - { letter => $letter, - borrowernumber => $borrowernumber, - firstname => $firstname, - lastname => $lastname, - address1 => $address1, - address2 => $address2, - city => $city, - postcode => $postcode, - email => $email, - itemcount => $itemcount, - titles => $titles, - outputformat => defined $csvfilename ? 'csv' : defined $htmlfilename ? 'html' : '', - } - ); - } else { - if ($email) { - C4::Letters::EnqueueLetter( - { letter => $letter, - borrowernumber => $borrowernumber, - message_transport_type => 'email', - from_address => $admin_email_address, - } - ); - } else { - - # If we don't have an email address for this patron, send it to the admin to deal with. push @output_chunks, prepare_letter_for_printing( { letter => $letter, @@ -585,6 +562,36 @@ END_SQL outputformat => defined $csvfilename ? 'csv' : defined $htmlfilename ? 'html' : '', } ); + } else { + if ( ( $email and $mtt eq 'email' ) + or $mtt eq 'sms' ) { + C4::Letters::EnqueueLetter( + { letter => $letter, + borrowernumber => $borrowernumber, + message_transport_type => $mtt, + from_address => $admin_email_address, + } + ); + } else { + + # If we don't have an email address for this patron, send it to the admin to deal with. + push @output_chunks, + prepare_letter_for_printing( + { letter => $letter, + borrowernumber => $borrowernumber, + firstname => $firstname, + lastname => $lastname, + address1 => $address1, + address2 => $address2, + city => $city, + postcode => $postcode, + email => $email, + itemcount => $itemcount, + titles => $titles, + outputformat => defined $csvfilename ? 'csv' : defined $htmlfilename ? 'html' : '', + } + ); + } } } } @@ -711,7 +718,8 @@ sub parse_letter { branchcode => $params->{'branchcode'}, tables => \%tables, substitute => $substitute, - repeat => { item => \@item_tables } + repeat => { item => \@item_tables }, + message_transport_type => $params->{message_transport_type}, ); } diff --git a/tools/letter.pl b/tools/letter.pl index 317ea83..ddbe706 100755 --- a/tools/letter.pl +++ b/tools/letter.pl @@ -47,14 +47,16 @@ use C4::Auth; use C4::Context; use C4::Output; use C4::Branch; # GetBranches +use C4::Letters; use C4::Members::Attributes; -# _letter_from_where($branchcode,$module, $code) +# _letter_from_where($branchcode,$module, $code, $mtt) # - return FROM WHERE clause and bind args for a letter sub _letter_from_where { - my ($branchcode, $module, $code) = @_; + my ($branchcode, $module, $code, $mtt) = @_; my $sql = q{FROM letter WHERE branchcode = ? AND module = ? AND code = ?}; - my @args = ($branchcode || '', $module, $code); + $sql .= q{ AND message_transport_type = ?} if $mtt ne '*'; + my @args = ( $branchcode || '', $module, $code, ($mtt ne '*' ? $mtt : ()) ); # 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... @@ -68,12 +70,12 @@ sub _letter_from_where { return ($sql, \@args); } -# letter_exists($branchcode,$module, $code) -# - return true if a letter with the given $branchcode, $module and $code exists -sub letter_exists { +# get_letters($branchcode,$module, $code, $mtt) +# - return letters with the given $branchcode, $module, $code and $mtt exists +sub get_letters { my ($sql, $args) = _letter_from_where(@_); my $dbh = C4::Context->dbh; - my $letter = $dbh->selectrow_hashref("SELECT * $sql", undef, @$args); + my $letter = $dbh->selectall_hashref("SELECT * $sql", 'message_transport_type', undef, @$args); return $letter; } @@ -90,7 +92,7 @@ my $searchfield = $input->param('searchfield'); my $script_name = '/cgi-bin/koha/tools/letter.pl'; our $branchcode = $input->param('branchcode'); my $code = $input->param('code'); -my $module = $input->param('module'); +my $module = $input->param('module') || ''; my $content = $input->param('content'); my $op = $input->param('op') || ''; my $dbh = C4::Context->dbh; @@ -134,7 +136,8 @@ elsif ( $op eq 'delete_confirm' ) { delete_confirm($branchcode, $module, $code); } elsif ( $op eq 'delete_confirmed' ) { - delete_confirmed($branchcode, $module, $code); + my $mtt = $input->param('message_transport_type'); + delete_confirmed($branchcode, $module, $code, $mtt); $op = q{}; # next operation is to return to default screen } else { @@ -151,25 +154,56 @@ if ($op) { output_html_with_http_headers $input, $cookie, $template->output; sub add_form { - my ($branchcode,$module, $code ) = @_; + my ( $branchcode,$module, $code ) = @_; - my $letter; + my $letters; # if code has been passed we can identify letter and its an update action if ($code) { - $letter = letter_exists($branchcode,$module, $code); + $letters = get_letters($branchcode,$module, $code, '*'); } - if ($letter) { - $template->param( modify => 1 ); - $template->param( code => $letter->{code} ); + + my $message_transport_types = GetMessageTransportTypes(); + my @letter_loop; + if ($letters) { + my $first_flag = 1; + for my $mtt ( @$message_transport_types ) { + $mtt = $mtt->{message_transport_type}; + if ( $first_flag ) { + $template->param( + modify => 1, + code => $code, + branchcode => $branchcode, + name => $letters->{$mtt}{name}, + ); + $first_flag = 0; + } + + push @letter_loop, { + message_transport_type => $mtt, + is_html => $letters->{$mtt}{is_html}, + title => $letters->{$mtt}{title}, + content => $letters->{$mtt}{content}, + }; + } } else { # initialize the new fields - $letter = { + for my $mtt ( @$message_transport_types ) { + $mtt = $mtt->{message_transport_type}; + push @letter_loop, { + message_transport_type => $mtt, + } + } + $template->param( branchcode => $branchcode, module => $module, - }; + ); $template->param( adding => 1 ); } + $template->param( + letters => \@letter_loop, + ); + my $field_selection; push @{$field_selection}, add_fields('branches'); if ($module eq 'reserves') { @@ -210,13 +244,7 @@ sub add_form { } $template->param( - 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, ); @@ -231,22 +259,36 @@ sub add_validate { 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 - ); + my @mtt = $input->param('message_transport_type'); + my @title = $input->param('title'); + my @content = $input->param('content'); + for my $mtt ( @mtt ) { + my $is_html = $input->param("is_html_$mtt"); + my $title = shift @title; + my $content = shift @content; + my $letter = get_letters($oldbranchcode,$oldmodule, $code, $mtt); + unless ( $title and $content ) { + delete_confirmed( $oldbranchcode, $oldmodule, $code, $mtt ); + next; + } + if ( exists $letter->{$mtt} ) { + $dbh->do( + q{ + UPDATE letter + SET branchcode = ?, module = ?, name = ?, is_html = ?, title = ?, content = ? + WHERE branchcode = ? AND module = ? AND code = ? AND message_transport_type = ? + }, + undef, + $branchcode, $module, $name, $is_html || 0, $title, $content, + $oldbranchcode, $oldmodule, $code, $mtt + ); + } else { + $dbh->do( + q{INSERT INTO letter (branchcode,module,code,name,is_html,title,content,message_transport_type) VALUES (?,?,?,?,?,?,?,?)}, + undef, + $branchcode, $module, $code, $name, $is_html || 0, $title, $content, $mtt + ); + } } # set up default display default_display($branchcode); @@ -259,31 +301,42 @@ sub add_copy { my $module = $input->param('module'); my $code = $input->param('code'); - return if letter_exists($branchcode,$module, $code); + return if keys %{ get_letters($branchcode,$module, $code, '*') }; - my $old_letter = letter_exists($oldbranchcode,$module, $code); + my $old_letters = get_letters($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} - ); + my $message_transport_types = GetMessageTransportTypes(); + for my $mtt ( @$message_transport_types ) { + my $mtt = $mtt->{message_transport_type}; + next unless exists $old_letters->{$mtt}; + my $old_letter = $old_letters->{$mtt}; + + $dbh->do( + q{INSERT INTO letter (branchcode,module,code,name,is_html,title,content,message_transport_type) VALUES (?,?,?,?,?,?,?,?)}, + undef, + $branchcode, $module, $code, $old_letter->{name}, $old_letter->{is_html}, $old_letter->{title}, $old_letter->{content}, $mtt + ); + } } sub delete_confirm { my ($branchcode, $module, $code) = @_; my $dbh = C4::Context->dbh; - my $letter = letter_exists($branchcode, $module, $code); - $template->param( branchcode => $branchcode, branchname => GetBranchName($branchcode) ); - $template->param( code => $code ); - $template->param( module => $module); - $template->param( name => $letter->{name}); + my $letter = get_letters($branchcode, $module, $code, '*'); + my @values = values %$letter; + $template->param( + branchcode => $branchcode, + branchname => GetBranchName($branchcode), + code => $code, + module => $module, + name => $values[0]->{name}, + ); return; } sub delete_confirmed { - my ($branchcode, $module, $code) = @_; - my ($sql, $args) = _letter_from_where($branchcode, $module, $code); + my ($branchcode, $module, $code, $mtt) = @_; + my ($sql, $args) = _letter_from_where($branchcode, $module, $code, $mtt); my $dbh = C4::Context->dbh; $dbh->do("DELETE $sql", undef, @$args); # setup default display for screen @@ -300,7 +353,8 @@ sub retrieve_letters { my ($sql, @where, @args); $sql = "SELECT branchcode, module, code, name, branchname FROM letter - LEFT OUTER JOIN branches USING (branchcode)"; + LEFT OUTER JOIN branches USING (branchcode) + "; if ($searchstring && $searchstring=~m/(\S+)/) { $searchstring = $1 . q{%}; push @where, 'code LIKE ?'; @@ -316,8 +370,9 @@ sub retrieve_letters { } $sql .= " WHERE ".join(" AND ", @where) if @where; + $sql .= " GROUP BY branchcode,module,code"; $sql .= " ORDER BY module, code, branchcode"; -# use Data::Dumper; die Dumper($sql, \@args); + return $dbh->selectall_arrayref($sql, { Slice => {} }, @args); } diff --git a/tools/overduerules.pl b/tools/overduerules.pl index cc90d0b..c1ce466 100755 --- a/tools/overduerules.pl +++ b/tools/overduerules.pl @@ -27,6 +27,7 @@ use C4::Koha; use C4::Branch; use C4::Letters; use C4::Members; +use C4::Overdues; our $input = new CGI; my $dbh = C4::Context->dbh; @@ -44,11 +45,11 @@ sub blank_row { for my $rp (@rule_params) { for my $n (1 .. 3) { my $key = "${rp}${n}-$category_code"; - + if (utf8::is_utf8($key)) { utf8::encode($key); } - + my $value = $input->param($key); if ($value) { return 0; @@ -84,6 +85,18 @@ if ($op eq 'save') { my $sth_insert = $dbh->prepare("INSERT INTO overduerules (branchcode,categorycode, delay1,letter1,debarred1, delay2,letter2,debarred2, delay3,letter3,debarred3) VALUES (?,?,?,?,?,?,?,?,?,?,?)"); my $sth_update=$dbh->prepare("UPDATE overduerules SET delay1=?, letter1=?, debarred1=?, delay2=?, letter2=?, debarred2=?, delay3=?, letter3=?, debarred3=? WHERE branchcode=? AND categorycode=?"); my $sth_delete=$dbh->prepare("DELETE FROM overduerules WHERE branchcode=? AND categorycode=?"); + my $sth_insert_mtt = $dbh->prepare(" + INSERT INTO overduerules_transport_types( + branchcode, categorycode, letternumber, message_transport_type + ) VALUES ( + ?, ?, ?, ? + ) + "); + my $sth_delete_mtt = $dbh->prepare(" + DELETE FROM overduerules_transport_types + WHERE branchcode = ? AND categorycode = ? + "); + foreach my $key (@names){ # ISSUES if ($key =~ /(delay|letter|debarred)([1-3])-(.*)/) { @@ -166,6 +179,15 @@ if ($op eq 'save') { ($temphash{$bor}->{"debarred3"}?$temphash{$bor}->{"debarred3"}:0) ); } + + $sth_delete_mtt->execute( $branch, $bor ); + for my $letternumber ( 1..3 ) { + my @mtt = $input->param( "mtt${letternumber}-$bor" ); + next unless @mtt; + for my $mtt ( @mtt ) { + $sth_insert_mtt->execute( $branch, $bor, $letternumber, $mtt); + } + } } } } @@ -185,19 +207,21 @@ my $countletters = keys %{$letters}; my @line_loop; +my $message_transport_types = GetMessageTransportTypes(); +my ( @first, @second, @third ); for my $data (@categories) { - my %row = ( - overduename => $data->{'categorycode'}, - line => $data->{'description'} - ); if (%temphash and not $input_saved){ # if we managed to save the form submission, don't # reuse %temphash, but take the values from the # database - this makes it easier to identify # bugs where the form submission was not correctly saved - for (my $i=1;$i<=3;$i++){ - $row{"delay$i"}=$temphash{$data->{'categorycode'}}->{"delay$i"}; - $row{"debarred$i"}=$temphash{$data->{'categorycode'}}->{"debarred$i"}; + for my $i ( 1..3 ){ + my %row = ( + overduename => $data->{'categorycode'}, + line => $data->{'description'} + ); + $row{delay}=$temphash{$data->{'categorycode'}}->{"delay$i"}; + $row{debarred}=$temphash{$data->{'categorycode'}}->{"debarred$i"}; if ($countletters){ my @letterloop; foreach my $thisletter (sort { $letters->{$a} cmp $letters->{$b} } keys %$letters) { @@ -212,10 +236,26 @@ for my $data (@categories) { ); push @letterloop, \%letterrow; } - $row{"letterloop$i"}=\@letterloop; + $row{letterloop}=\@letterloop; } else { - $row{"noletter"}=1; - $row{"letter$i"}=$temphash{$data->{'categorycode'}}->{"letter$i"}; + $row{noletter}=1; + $row{letter}=$temphash{$data->{'categorycode'}}->{"letter$i"}; + } + my @selected_mtts = @{ GetOverdueMessageTransportTypes( $branch, $data->{'categorycode'}, $i) }; + my @mtts; + for my $mtt ( @$message_transport_types ) { + push @mtts, { + value => $mtt->{message_transport_type}, + selected => ( grep {$mtt->{message_transport_type} eq $_} @selected_mtts ) ? 1 : 0 , + } + } + $row{message_transport_types} = \@mtts; + if ( $i == 1 ) { + push @first, \%row; + } elsif ( $i == 2 ) { + push @second, \%row; + } else { + push @third, \%row; } } } else { @@ -223,7 +263,11 @@ for my $data (@categories) { my $sth2=$dbh->prepare("SELECT * from overduerules WHERE branchcode=? AND categorycode=?"); $sth2->execute($branch,$data->{'categorycode'}); my $dat=$sth2->fetchrow_hashref; - for (my $i=1;$i<=3;$i++){ + for my $i ( 1..3 ){ + my %row = ( + overduename => $data->{'categorycode'}, + line => $data->{'description'} + ); if ($countletters){ my @letterloop; foreach my $thisletter (sort { $letters->{$a} cmp $letters->{$b} } keys %$letters) { @@ -237,19 +281,57 @@ for my $data (@categories) { ); push @letterloop, \%letterrow; } - $row{"letterloop$i"}=\@letterloop; + $row{letterloop}=\@letterloop; } else { - $row{"noletter"}=1; - if ($dat->{"letter$i"}){$row{"letter$i"}=$dat->{"letter$i"};} + $row{noletter}=1; + if ($dat->{"letter$i"}){$row{letter}=$dat->{"letter$i"};} + } + if ($dat->{"delay$i"}){$row{delay}=$dat->{"delay$i"};} + if ($dat->{"debarred$i"}){$row{debarred}=$dat->{"debarred$i"};} + my @selected_mtts = @{ GetOverdueMessageTransportTypes( $branch, $data->{'categorycode'}, $i) }; + my @mtts; + for my $mtt ( @$message_transport_types ) { + push @mtts, { + value => $mtt->{message_transport_type}, + selected => ( grep {$mtt->{message_transport_type} eq $_} @selected_mtts ) ? 1 : 0 , + } } - if ($dat->{"delay$i"}){$row{"delay$i"}=$dat->{"delay$i"};} - if ($dat->{"debarred$i"}){$row{"debarred$i"}=$dat->{"debarred$i"};} + $row{message_transport_types} = \@mtts; + if ( $i == 1 ) { + push @first, \%row; + } elsif ( $i == 2 ) { + push @second, \%row; + } else { + push @third, \%row; + } + } } - push @line_loop,\%row; } -$template->param(table=> \@line_loop, - branchloop => $branchloop, - branch => $branch); +my @tabs = ( + { + id => 'first', + number => 1, + values => \@first, + }, + { + id => 'second', + number => 2, + values => \@second, + }, + { + id => 'third', + number => 3, + values => \@third, + }, +); + +$template->param( + table => ( @first or @second or @third ? 1 : 0 ), + branchloop => $branchloop, + branch => $branch, + tabs => \@tabs, + message_transport_types => $message_transport_types, +); output_html_with_http_headers $input, $cookie, $template->output; -- 1.7.10.4