X-Git-Url: http://git.rot13.org/?a=blobdiff_plain;f=C4%2FLetters.pm;h=23b7fdd78ebc8fe240f94d3c1da166b7a48b3fca;hb=0a366e19decc8bf8d8d9ecf854e10ce3d3a19363;hp=514b355377fbcd77bd6147ce45e846897acbffbe;hpb=b93e6df3a1b7051bc92220cf6bd829c2229bfa70;p=koha.git diff --git a/C4/Letters.pm b/C4/Letters.pm index 514b355377..23b7fdd78e 100644 --- a/C4/Letters.pm +++ b/C4/Letters.pm @@ -23,12 +23,14 @@ use warnings; use MIME::Lite; use Mail::Sendmail; +use C4::Koha qw(GetAuthorisedValueByCode); use C4::Members; use C4::Members::Attributes qw(GetBorrowerAttributes); use C4::Branch; use C4::Log; use C4::SMS; use C4::Debug; +use Koha::DateUtils; use Date::Calc qw( Add_Delta_Days ); use Encode; use Carp; @@ -36,13 +38,13 @@ use Carp; use vars qw($VERSION @ISA @EXPORT @EXPORT_OK %EXPORT_TAGS); BEGIN { - require Exporter; - # set the version for version checking + require Exporter; + # set the version for version checking $VERSION = 3.07.00.049; - @ISA = qw(Exporter); - @EXPORT = qw( - &GetLetters &GetPreparedLetter &GetWrappedLetter &addalert &getalert &delalert &findrelatedto &SendAlerts &GetPrintMessages - ); + @ISA = qw(Exporter); + @EXPORT = qw( + &GetLetters &GetPreparedLetter &GetWrappedLetter &addalert &getalert &delalert &findrelatedto &SendAlerts &GetPrintMessages &GetMessageTransportTypes + ); } =head1 NAME @@ -93,48 +95,62 @@ $template->param(LETTERLOOP => \@letterloop); =cut -sub GetLetters (;$) { +sub GetLetters { # returns a reference to a hash of references to ALL letters... - my $cat = shift; + my ( $cat ) = @_; 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 = q{ + SELECT * FROM letter WHERE 1 + }; + $query .= q{ AND module = ? } if defined $cat; + $query .= q{ GROUP BY code ORDER BY name}; + $sth = $dbh->prepare($query); + $sth->execute((defined $cat ? $cat : ())); + while ( my $letter = $sth->fetchrow_hashref ) { $letters{ $letter->{'code'} } = $letter->{'name'}; } return \%letters; } -my %letter; -sub getletter ($$$) { - my ( $module, $code, $branchcode ) = @_; +# FIXME: using our here means that a Plack server will need to be +# restarted fairly regularly when working with this routine. +# A better option would be to use Koha::Cache and use a cache +# that actually works in a persistent environment, but as a +# short-term fix, our will work. +our %letter; +sub getletter { + my ( $module, $code, $branchcode, $message_transport_type ) = @_; + $message_transport_type ||= 'email'; + + + if ( C4::Context->preference('IndependentBranches') + and $branchcode + and C4::Context->userenv ) { - if (C4::Context->preference('IndependantBranches') && $branchcode){ $branchcode = C4::Context->userenv->{'branch'}; } + $branchcode //= ''; - 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(q{ + 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 }; } @@ -149,7 +165,7 @@ sub getletter ($$$) { =cut -sub addalert ($$$) { +sub addalert { my ( $borrowernumber, $type, $externalid ) = @_; my $dbh = C4::Context->dbh; my $sth = @@ -170,7 +186,7 @@ sub addalert ($$$) { =cut -sub delalert ($) { +sub delalert { my $alertid = shift or die "delalert() called without valid argument (alertid)"; # it's gonna die anyway. $debug and warn "delalert: deleting alertid $alertid"; my $sth = C4::Context->dbh->prepare("delete from alert where alertid=?"); @@ -187,7 +203,7 @@ sub delalert ($) { =cut -sub getalert (;$$$) { +sub getalert { my ( $borrowernumber, $type, $externalid ) = @_; my $dbh = C4::Context->dbh; my $query = "SELECT a.*, b.branchcode FROM alert a JOIN borrowers b USING(borrowernumber) WHERE"; @@ -224,16 +240,16 @@ sub getalert (;$$$) { # outmoded POD: # When type=virtual, the id is related to a virtual shelf and this sub returns the name of the sub -sub findrelatedto ($$) { - my $type = shift or return undef; - my $externalid = shift or return undef; +sub findrelatedto { + my $type = shift or return; + my $externalid = shift or return; my $q = ($type eq 'issue' ) ? "select title as result from subscription left join biblio on subscription.biblionumber=biblio.biblionumber where subscriptionid=?" : ($type eq 'borrower') ? "select concat(firstname,' ',surname) from borrowers where borrowernumber=?" : undef; unless ($q) { warn "findrelatedto(): Illegal type '$type'"; - return undef; + return; } my $sth = C4::Context->dbh->prepare($q); $sth->execute($externalid); @@ -277,6 +293,7 @@ sub SendAlerts { # warn "sending issues..."; my $userenv = C4::Context->userenv; + my $branchdetails = GetBranchDetail($_->{'branchcode'}); my $letter = GetPreparedLetter ( module => 'serial', letter_code => $letter_code, @@ -293,7 +310,7 @@ sub SendAlerts { # ... then send mail my %mail = ( To => $email, - From => $email, + From => $branchdetails->{'branchemail'} || C4::Context->preference("KohaAdminEmailAddress"), Subject => Encode::encode( "utf8", "" . $letter->{title} ), Message => Encode::encode( "utf8", "" . $letter->{content} ), 'Content-Type' => 'text/plain; charset="utf8"', @@ -312,7 +329,7 @@ sub SendAlerts { FROM aqorders LEFT JOIN aqbasket ON aqbasket.basketno=aqorders.basketno LEFT JOIN biblio ON aqorders.biblionumber=biblio.biblionumber - LEFT JOIN biblioitems ON aqorders.biblioitemnumber=biblioitems.biblioitemnumber + LEFT JOIN biblioitems ON aqorders.biblionumber=biblioitems.biblionumber LEFT JOIN aqbooksellers ON aqbasket.booksellerid=aqbooksellers.id WHERE aqorders.ordernumber IN ( } @@ -434,9 +451,10 @@ sub GetPreparedLetter { my $module = $params{module} or croak "No module"; my $letter_code = $params{letter_code} or croak "No letter_code"; my $branchcode = $params{branchcode} || ''; + my $mtt = $params{message_transport_type} || 'email'; - my $letter = getletter( $module, $letter_code, $branchcode ) - or warn( "No $module $letter_code letter"), + my $letter = getletter( $module, $letter_code, $branchcode, $mtt ) + or warn( "No $module $letter_code letter transported by " . $mtt ), return; my $tables = $params{tables}; @@ -454,6 +472,9 @@ sub GetPreparedLetter { } } + my $OPACBaseURL = C4::Context->preference('OPACBaseURL'); + $letter->{content} =~ s/<>/$OPACBaseURL/go; + if ($want_librarian) { # parsing librarian name my $userenv = C4::Context->userenv; @@ -536,43 +557,48 @@ sub _substitute_tables { $sth->execute( $ref ? @$param : $param ); $values = $sth->fetchrow_hashref; + $sth->finish(); } _parseletter ( $letter, $table, $values ); } } -my %handles = (); sub _parseletter_sth { my $table = shift; + my $sth; unless ($table) { carp "ERROR: _parseletter_sth() called without argument (table)"; return; } - # check cache first - (defined $handles{$table}) and return $handles{$table}; + # NOTE: we used to check whether we had a statement handle cached in + # a %handles module-level variable. This was a dumb move and + # broke things for the rest of us. prepare_cached is a better + # way to cache statement handles anyway. my $query = - ($table eq 'biblio' ) ? "SELECT * FROM $table WHERE biblionumber = ?" : - ($table eq 'biblioitems' ) ? "SELECT * FROM $table WHERE biblionumber = ?" : - ($table eq 'items' ) ? "SELECT * FROM $table WHERE itemnumber = ?" : - ($table eq 'issues' ) ? "SELECT * FROM $table WHERE itemnumber = ?" : - ($table eq 'reserves' ) ? "SELECT * FROM $table WHERE borrowernumber = ? and biblionumber = ?" : - ($table eq 'borrowers' ) ? "SELECT * FROM $table WHERE borrowernumber = ?" : - ($table eq 'branches' ) ? "SELECT * FROM $table WHERE branchcode = ?" : - ($table eq 'suggestions' ) ? "SELECT * FROM $table WHERE suggestionid = ?" : - ($table eq 'aqbooksellers') ? "SELECT * FROM $table WHERE id = ?" : - ($table eq 'aqorders' ) ? "SELECT * FROM $table WHERE ordernumber = ?" : - ($table eq 'opac_news' ) ? "SELECT * FROM $table WHERE idnew = ?" : + ($table eq 'biblio' ) ? "SELECT * FROM $table WHERE biblionumber = ?" : + ($table eq 'biblioitems' ) ? "SELECT * FROM $table WHERE biblionumber = ?" : + ($table eq 'items' ) ? "SELECT * FROM $table WHERE itemnumber = ?" : + ($table eq 'issues' ) ? "SELECT * FROM $table WHERE itemnumber = ?" : + ($table eq 'old_issues' ) ? "SELECT * FROM $table WHERE itemnumber = ? ORDER BY timestamp DESC LIMIT 1" : + ($table eq 'reserves' ) ? "SELECT * FROM $table WHERE borrowernumber = ? and biblionumber = ?" : + ($table eq 'borrowers' ) ? "SELECT * FROM $table WHERE borrowernumber = ?" : + ($table eq 'branches' ) ? "SELECT * FROM $table WHERE branchcode = ?" : + ($table eq 'suggestions' ) ? "SELECT * FROM $table WHERE suggestionid = ?" : + ($table eq 'aqbooksellers') ? "SELECT * FROM $table WHERE id = ?" : + ($table eq 'aqorders' ) ? "SELECT * FROM $table WHERE ordernumber = ?" : + ($table eq 'opac_news' ) ? "SELECT * FROM $table WHERE idnew = ?" : + ($table eq 'borrower_modifications') ? "SELECT * FROM $table WHERE borrowernumber = ? OR verification_token =?": undef ; unless ($query) { warn "ERROR: No _parseletter_sth query for table '$table'"; return; # nothing to get } - unless ($handles{$table} = C4::Context->dbh->prepare($query)) { + unless ($sth = C4::Context->dbh->prepare_cached($query)) { warn "ERROR: Failed to prepare query: '$query'"; return; } - return $handles{$table}; # now cache is populated for that $table + return $sth; # now cache is populated for that $table } =head2 _parseletter($letter, $table, $values) @@ -586,40 +612,38 @@ sub _parseletter_sth { =cut -my %columns = (); sub _parseletter { my ( $letter, $table, $values ) = @_; - # TEMPORARY hack until the expirationdate column is added to reserves if ( $table eq 'reserves' && $values->{'waitingdate'} ) { my @waitingdate = split /-/, $values->{'waitingdate'}; - $values->{'expirationdate'} = C4::Dates->new( - sprintf( - '%04d-%02d-%02d', - Add_Delta_Days( @waitingdate, C4::Context->preference( 'ReservesMaxPickUpDelay' ) ) - ), - 'iso' - )->output(); + $values->{'expirationdate'} = ''; + if( C4::Context->preference('ExpireReservesMaxPickUpDelay') && + C4::Context->preference('ReservesMaxPickUpDelay') ) { + my $dt = dt_from_string(); + $dt->add( days => C4::Context->preference('ReservesMaxPickUpDelay') ); + $values->{'expirationdate'} = output_pref({ dt => $dt, dateonly => 1 }); + } + + $values->{'waitingdate'} = output_pref({ dt => dt_from_string( $values->{'waitingdate'} ), dateonly => 1 }); + } if ($letter->{content} && $letter->{content} =~ /<>/) { - my @da = localtime(); - my $todaysdate = "$da[2]:$da[1] " . C4::Dates->today(); + my $todaysdate = output_pref( DateTime->now() ); $letter->{content} =~ s/<>/$todaysdate/go; } - # and get all fields from the table -# my $columns = $columns{$table}; -# unless ($columns) { -# $columns = $columns{$table} = C4::Context->dbh->selectcol_arrayref("SHOW COLUMNS FROM $table"); -# } -# foreach my $field (@$columns) { - while ( my ($field, $val) = each %$values ) { my $replacetablefield = "<<$table.$field>>"; my $replacefield = "<<$field>>"; - $val =~ s/\p{P}(?=$)//g if $val; + $val =~ s/\p{P}$// if $val && $table=~/biblio/; + #BZ 9886: Assuming that we want to eliminate ISBD punctuation here + #Therefore adding the test on biblio. This includes biblioitems, + #but excludes items. Removed unneeded global and lookahead. + + $val = GetAuthorisedValueByCode ('ROADTYPE', $val, 0) if $table=~/^borrowers$/ && $field=~/^streettype$/; my $replacedby = defined ($val) ? $val : ''; ($letter->{title} ) and do { $letter->{title} =~ s/$replacetablefield/$replacedby/g; @@ -665,13 +689,20 @@ return message_id on success =cut -sub EnqueueLetter ($) { - my $params = shift or return undef; +sub EnqueueLetter { + my $params = shift or return; return unless exists $params->{'letter'}; - return unless exists $params->{'borrowernumber'}; +# return unless exists $params->{'borrowernumber'}; return unless exists $params->{'message_transport_type'}; + my $content = $params->{letter}->{content}; + $content =~ s/\s+//g if(defined $content); + if ( not defined $content or $content eq '' ) { + warn "Trying to add an empty message to the message queue" if $debug; + return; + } + # If we have any attachments we should encode then into the body. if ( $params->{'attachments'} ) { $params->{'letter'} = _add_attachments( @@ -716,7 +747,7 @@ returns number of messages sent. =cut -sub SendQueuedMessages (;$) { +sub SendQueuedMessages { my $params = shift; my $unsent_messages = _get_unsent_messages(); @@ -771,7 +802,8 @@ sub GetPrintMessages { my $params = shift || {}; return _get_unsent_messages( { message_transport_type => 'print', - borrowernumber => $params->{'borrowernumber'}, } ); + borrowernumber => $params->{'borrowernumber'}, + } ); } =head2 GetQueuedMessages ([$hashref]) @@ -815,6 +847,24 @@ ENDSQL return $sth->fetchall_arrayref({}); } +=head2 GetMessageTransportTypes + + my @mtt = GetMessageTransportTypes(); + + returns an arrayref of transport types + +=cut + +sub GetMessageTransportTypes { + my $dbh = C4::Context->dbh(); + my $mtts = $dbh->selectcol_arrayref(" + SELECT message_transport_type + FROM message_transport_types + ORDER BY message_transport_type + "); + return $mtts; +} + =head2 _add_attachements named parameters: @@ -861,13 +911,14 @@ sub _add_attachments { } -sub _get_unsent_messages (;$) { +sub _get_unsent_messages { my $params = shift; my $dbh = C4::Context->dbh(); my $statement = << 'ENDSQL'; -SELECT message_id, borrowernumber, subject, content, message_transport_type, status, time_queued, from_address, to_address, content_type - FROM message_queue +SELECT mq.message_id, mq.borrowernumber, mq.subject, mq.content, mq.message_transport_type, mq.status, mq.time_queued, mq.from_address, mq.to_address, mq.content_type, b.branchcode, mq.letter_code + FROM message_queue mq + LEFT JOIN borrowers b ON b.borrowernumber = mq.borrowernumber WHERE status = ? ENDSQL @@ -886,6 +937,7 @@ ENDSQL push @query_params, $params->{'limit'}; } } + $debug and warn "_get_unsent_messages SQL: $statement"; $debug and warn "_get_unsent_messages params: " . join(',',@query_params); my $sth = $dbh->prepare( $statement ); @@ -893,26 +945,20 @@ ENDSQL return $sth->fetchall_arrayref({}); } -sub _send_message_by_email ($;$$$) { +sub _send_message_by_email { my $message = shift or return; my ($username, $password, $method) = @_; - my $to_address = $message->{to_address}; + my $member = C4::Members::GetMember( 'borrowernumber' => $message->{'borrowernumber'} ); + my $to_address = $message->{'to_address'}; unless ($to_address) { - my $member = C4::Members::GetMember( 'borrowernumber' => $message->{'borrowernumber'} ); unless ($member) { warn "FAIL: No 'to_address' and INVALID borrowernumber ($message->{borrowernumber})"; _set_message_status( { message_id => $message->{'message_id'}, status => 'failed' } ); return; } - my $which_address = C4::Context->preference('AutoEmailPrimaryAddress'); - # If the system preference is set to 'first valid' (value == OFF), look up email address - if ($which_address eq 'OFF') { - $to_address = GetFirstValidEmailAddress( $message->{'borrowernumber'} ); - } else { - $to_address = $member->{$which_address}; - } + $to_address = C4::Members::GetNoticeEmailAddress( $message->{'borrowernumber'} ); unless ($to_address) { # warn "FAIL: No 'to_address' and no email for " . ($member->{surname} ||'') . ", borrowernumber ($message->{borrowernumber})"; # warning too verbose for this more common case? @@ -928,9 +974,12 @@ sub _send_message_by_email ($;$$$) { my $content = encode('utf8', $message->{'content'}); my $content_type = $message->{'content_type'} || 'text/plain; charset="UTF-8"'; my $is_html = $content_type =~ m/html/io; + + my $branch_email = ( $member ) ? GetBranchDetail( $member->{'branchcode'} )->{'branchemail'} : undef; + my %sendmail_params = ( To => $to_address, - From => $message->{'from_address'} || C4::Context->preference('KohaAdminEmailAddress'), + From => $message->{'from_address'} || $branch_email || C4::Context->preference('KohaAdminEmailAddress'), Subject => $subject, charset => 'utf8', Message => $is_html ? _wrap_html($content, $subject) : $content, @@ -975,10 +1024,36 @@ $content EOS } -sub _send_message_by_sms ($) { - my $message = shift or return undef; +sub _is_duplicate { + my ( $message ) = @_; + my $dbh = C4::Context->dbh; + my $count = $dbh->selectrow_array(q| + SELECT COUNT(*) + FROM message_queue + WHERE message_transport_type = ? + AND borrowernumber = ? + AND letter_code = ? + AND CAST(time_queued AS date) = CAST(NOW() AS date) + AND status="sent" + |, {}, $message->{message_transport_type}, $message->{borrowernumber}, $message->{letter_code} ); + return $count; +} + +sub _send_message_by_sms { + my $message = shift or return; my $member = C4::Members::GetMember( 'borrowernumber' => $message->{'borrowernumber'} ); - return unless $member->{'smsalertnumber'}; + + unless ( $member->{smsalertnumber} ) { + _set_message_status( { message_id => $message->{'message_id'}, + status => 'failed' } ); + return; + } + + if ( _is_duplicate( $message ) ) { + _set_message_status( { message_id => $message->{'message_id'}, + status => 'failed' } ); + return; + } my $success = C4::SMS->send_sms( { destination => $member->{'smsalertnumber'}, message => $message->{'content'}, @@ -994,11 +1069,11 @@ sub _update_message_to_address { $dbh->do('UPDATE message_queue SET to_address=? WHERE message_id=?',undef,($to,$id)); } -sub _set_message_status ($) { - my $params = shift or return undef; +sub _set_message_status { + my $params = shift or return; foreach my $required_parameter ( qw( message_id status ) ) { - return undef unless exists $params->{ $required_parameter }; + return unless exists $params->{ $required_parameter }; } my $dbh = C4::Context->dbh();