3 # Copyright 2000-2002 Katipo Communications
5 # This file is part of Koha.
7 # Koha is free software; you can redistribute it and/or modify it
8 # under the terms of the GNU General Public License as published by
9 # the Free Software Foundation; either version 3 of the License, or
10 # (at your option) any later version.
12 # Koha is distributed in the hope that it will be useful, but
13 # WITHOUT ANY WARRANTY; without even the implied warranty of
14 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
15 # GNU General Public License for more details.
17 # You should have received a copy of the GNU General Public License
18 # along with Koha; if not, see <http://www.gnu.org/licenses>.
24 use Date::Calc qw( Add_Delta_Days );
28 use Module::Load::Conditional qw(can_load);
31 use C4::Members::Attributes qw(GetBorrowerAttributes);
36 use Koha::SMS::Providers;
39 use Koha::Notice::Messages;
40 use Koha::DateUtils qw( format_sqldatetime dt_from_string );
43 use vars qw(@ISA @EXPORT @EXPORT_OK %EXPORT_TAGS);
49 &GetLetters &GetLettersAvailableForALibrary &GetLetterTemplates &DelLetter &GetPreparedLetter &GetWrappedLetter &addalert &getalert &delalert &findrelatedto &SendAlerts &GetPrintMessages &GetMessageTransportTypes
55 C4::Letters - Give functions for Letters management
63 "Letters" is the tool used in Koha to manage informations sent to the patrons and/or the library. This include some cron jobs like
64 late issues, as well as other tasks like sending a mail to users that have subscribed to a "serial issue alert" (= being warned every time a new issue has arrived at the library)
66 Letters are managed through "alerts" sent by Koha on some events. All "alert" related functions are in this module too.
68 =head2 GetLetters([$module])
70 $letters = &GetLetters($module);
71 returns informations about letters.
72 if needed, $module filters for letters given module
78 my $module = $filters->{module};
79 my $code = $filters->{code};
80 my $branchcode = $filters->{branchcode};
81 my $dbh = C4::Context->dbh;
82 my $letters = $dbh->selectall_arrayref(
84 SELECT module, code, branchcode, name
88 . ( $module ? q| AND module = ?| : q|| )
89 . ( $code ? q| AND code = ?| : q|| )
90 . ( defined $branchcode ? q| AND branchcode = ?| : q|| )
91 . q| GROUP BY code ORDER BY name|, { Slice => {} }
92 , ( $module ? $module : () )
93 , ( $code ? $code : () )
94 , ( defined $branchcode ? $branchcode : () )
100 =head2 GetLetterTemplates
102 my $letter_templates = GetLetterTemplates(
104 module => 'circulation',
106 branchcode => 'CPL', # '' for default,
110 Return a hashref of letter templates.
114 sub GetLetterTemplates {
117 my $module = $params->{module};
118 my $code = $params->{code};
119 my $branchcode = $params->{branchcode} // '';
120 my $dbh = C4::Context->dbh;
121 my $letters = $dbh->selectall_arrayref(
123 SELECT module, code, branchcode, name, is_html, title, content, message_transport_type, lang
130 , $module, $code, $branchcode
136 =head2 GetLettersAvailableForALibrary
138 my $letters = GetLettersAvailableForALibrary(
140 branchcode => 'CPL', # '' for default
141 module => 'circulation',
145 Return an arrayref of letters, sorted by name.
146 If a specific letter exist for the given branchcode, it will be retrieve.
147 Otherwise the default letter will be.
151 sub GetLettersAvailableForALibrary {
153 my $branchcode = $filters->{branchcode};
154 my $module = $filters->{module};
156 croak "module should be provided" unless $module;
158 my $dbh = C4::Context->dbh;
159 my $default_letters = $dbh->selectall_arrayref(
161 SELECT module, code, branchcode, name
165 . q| AND branchcode = ''|
166 . ( $module ? q| AND module = ?| : q|| )
167 . q| ORDER BY name|, { Slice => {} }
168 , ( $module ? $module : () )
171 my $specific_letters;
173 $specific_letters = $dbh->selectall_arrayref(
175 SELECT module, code, branchcode, name
179 . q| AND branchcode = ?|
180 . ( $module ? q| AND module = ?| : q|| )
181 . q| ORDER BY name|, { Slice => {} }
183 , ( $module ? $module : () )
188 for my $l (@$default_letters) {
189 $letters{ $l->{code} } = $l;
191 for my $l (@$specific_letters) {
192 # Overwrite the default letter with the specific one.
193 $letters{ $l->{code} } = $l;
196 return [ map { $letters{$_} }
197 sort { $letters{$a}->{name} cmp $letters{$b}->{name} }
203 my ( $module, $code, $branchcode, $message_transport_type, $lang) = @_;
204 $message_transport_type //= '%';
205 $lang = 'default' unless( $lang && C4::Context->preference('TranslateNotices') );
208 my $only_my_library = C4::Context->only_my_library;
209 if ( $only_my_library and $branchcode ) {
210 $branchcode = C4::Context::mybranch();
214 my $dbh = C4::Context->dbh;
215 my $sth = $dbh->prepare(q{
218 WHERE module=? AND code=? AND (branchcode = ? OR branchcode = '')
219 AND message_transport_type LIKE ?
221 ORDER BY branchcode DESC LIMIT 1
223 $sth->execute( $module, $code, $branchcode, $message_transport_type, $lang );
224 my $line = $sth->fetchrow_hashref
226 $line->{'content-type'} = 'text/html; charset="UTF-8"' if $line->{is_html};
236 module => 'circulation',
242 Delete the letter. The mtt parameter is facultative.
243 If not given, all templates mathing the other parameters will be removed.
249 my $branchcode = $params->{branchcode};
250 my $module = $params->{module};
251 my $code = $params->{code};
252 my $mtt = $params->{mtt};
253 my $lang = $params->{lang};
254 my $dbh = C4::Context->dbh;
261 . ( $mtt ? q| AND message_transport_type = ?| : q|| )
262 . ( $lang? q| AND lang = ?| : q|| )
263 , undef, $branchcode, $module, $code, ( $mtt ? $mtt : () ), ( $lang ? $lang : () ) );
266 =head2 addalert ($borrowernumber, $type, $externalid)
269 - $borrowernumber : the number of the borrower subscribing to the alert
270 - $type : the type of alert.
271 - $externalid : the primary key of the object to put alert on. For issues, the alert is made on subscriptionid.
273 create an alert and return the alertid (primary key)
278 my ( $borrowernumber, $type, $externalid ) = @_;
279 my $dbh = C4::Context->dbh;
282 "insert into alert (borrowernumber, type, externalid) values (?,?,?)");
283 $sth->execute( $borrowernumber, $type, $externalid );
285 # get the alert number newly created and return it
286 my $alertid = $dbh->{'mysql_insertid'};
290 =head2 delalert ($alertid)
293 - alertid : the alert id
299 my $alertid = shift or die "delalert() called without valid argument (alertid)"; # it's gonna die anyway.
300 $debug and warn "delalert: deleting alertid $alertid";
301 my $sth = C4::Context->dbh->prepare("delete from alert where alertid=?");
302 $sth->execute($alertid);
305 =head2 getalert ([$borrowernumber], [$type], [$externalid])
308 - $borrowernumber : the number of the borrower subscribing to the alert
309 - $type : the type of alert.
310 - $externalid : the primary key of the object to put alert on. For issues, the alert is made on subscriptionid.
311 all parameters NON mandatory. If a parameter is omitted, the query is done without the corresponding parameter. For example, without $externalid, returns all alerts for a borrower on a topic.
316 my ( $borrowernumber, $type, $externalid ) = @_;
317 my $dbh = C4::Context->dbh;
318 my $query = "SELECT a.*, b.branchcode FROM alert a JOIN borrowers b USING(borrowernumber) WHERE 1";
320 if ($borrowernumber and $borrowernumber =~ /^\d+$/) {
321 $query .= " AND borrowernumber=?";
322 push @bind, $borrowernumber;
325 $query .= " AND type=?";
329 $query .= " AND externalid=?";
330 push @bind, $externalid;
332 my $sth = $dbh->prepare($query);
333 $sth->execute(@bind);
334 return $sth->fetchall_arrayref({});
337 =head2 findrelatedto($type, $externalid)
340 - $type : the type of alert
341 - $externalid : the id of the "object" to query
343 In the table alert, a "id" is stored in the externalid field. This "id" is related to another table, depending on the type of the alert.
344 When type=issue, the id is related to a subscriptionid and this sub returns the name of the biblio.
349 # When type=virtual, the id is related to a virtual shelf and this sub returns the name of the sub
352 my $type = shift or return;
353 my $externalid = shift or return;
354 my $q = ($type eq 'issue' ) ?
355 "select title as result from subscription left join biblio on subscription.biblionumber=biblio.biblionumber where subscriptionid=?" :
356 ($type eq 'borrower') ?
357 "select concat(firstname,' ',surname) from borrowers where borrowernumber=?" : undef;
359 warn "findrelatedto(): Illegal type '$type'";
362 my $sth = C4::Context->dbh->prepare($q);
363 $sth->execute($externalid);
364 my ($result) = $sth->fetchrow;
370 my $err = &SendAlerts($type, $externalid, $letter_code);
373 - $type : the type of alert
374 - $externalid : the id of the "object" to query
375 - $letter_code : the notice template to use
377 C<&SendAlerts> sends an email notice directly to a patron or a vendor.
379 Currently it supports ($type):
380 - claim serial issues (claimissues)
381 - claim acquisition orders (claimacquisition)
382 - send acquisition orders to the vendor (orderacquisition)
383 - notify patrons about newly received serial issues (issue)
384 - notify patrons when their account is created (members)
386 Returns undef or { error => 'message } on failure.
387 Returns true on success.
392 my ( $type, $externalid, $letter_code ) = @_;
393 my $dbh = C4::Context->dbh;
394 if ( $type eq 'issue' ) {
396 # prepare the letter...
397 # search the subscriptionid
400 "SELECT subscriptionid FROM serial WHERE serialid=?");
401 $sth->execute($externalid);
402 my ($subscriptionid) = $sth->fetchrow
403 or warn( "No subscription for '$externalid'" ),
406 # search the biblionumber
409 "SELECT biblionumber FROM subscription WHERE subscriptionid=?");
410 $sth->execute($subscriptionid);
411 my ($biblionumber) = $sth->fetchrow
412 or warn( "No biblionumber for '$subscriptionid'" ),
416 # find the list of borrowers to alert
417 my $alerts = getalert( '', 'issue', $subscriptionid );
419 my $patron = Koha::Patrons->find( $_->{borrowernumber} );
420 next unless $patron; # Just in case
421 my $email = $patron->email or next;
423 # warn "sending issues...";
424 my $userenv = C4::Context->userenv;
425 my $library = Koha::Libraries->find( $_->{branchcode} );
426 my $letter = GetPreparedLetter (
428 letter_code => $letter_code,
429 branchcode => $userenv->{branch},
431 'branches' => $_->{branchcode},
432 'biblio' => $biblionumber,
433 'biblioitems' => $biblionumber,
434 'borrowers' => $patron->unblessed,
435 'subscription' => $subscriptionid,
436 'serial' => $externalid,
442 my $message = Koha::Email->new();
443 my %mail = $message->create_message_headers(
446 from => $library->branchemail,
447 replyto => $library->branchreplyto,
448 sender => $library->branchreturnpath,
449 subject => Encode::encode( "UTF-8", "" . $letter->{title} ),
450 message => $letter->{'is_html'}
451 ? _wrap_html( Encode::encode( "UTF-8", $letter->{'content'} ),
452 Encode::encode( "UTF-8", "" . $letter->{'title'} ))
453 : Encode::encode( "UTF-8", "" . $letter->{'content'} ),
454 contenttype => $letter->{'is_html'}
455 ? 'text/html; charset="utf-8"'
456 : 'text/plain; charset="utf-8"',
459 unless( Mail::Sendmail::sendmail(%mail) ) {
460 carp $Mail::Sendmail::error;
461 return { error => $Mail::Sendmail::error };
465 elsif ( $type eq 'claimacquisition' or $type eq 'claimissues' or $type eq 'orderacquisition' ) {
467 # prepare the letter...
472 if ( $type eq 'claimacquisition') {
474 SELECT aqorders.*,aqbasket.*,biblio.*,biblioitems.*
476 LEFT JOIN aqbasket ON aqbasket.basketno=aqorders.basketno
477 LEFT JOIN biblio ON aqorders.biblionumber=biblio.biblionumber
478 LEFT JOIN biblioitems ON aqorders.biblionumber=biblioitems.biblionumber
479 WHERE aqorders.ordernumber IN (
483 carp "No order selected";
484 return { error => "no_order_selected" };
486 $strsth .= join( ",", ('?') x @$externalid ) . ")";
487 $action = "ACQUISITION CLAIM";
488 $sthorders = $dbh->prepare($strsth);
489 $sthorders->execute( @$externalid );
490 $dataorders = $sthorders->fetchall_arrayref( {} );
493 if ($type eq 'claimissues') {
495 SELECT serial.*,subscription.*, biblio.*, aqbooksellers.*,
496 aqbooksellers.id AS booksellerid
498 LEFT JOIN subscription ON serial.subscriptionid=subscription.subscriptionid
499 LEFT JOIN biblio ON serial.biblionumber=biblio.biblionumber
500 LEFT JOIN aqbooksellers ON subscription.aqbooksellerid=aqbooksellers.id
501 WHERE serial.serialid IN (
505 carp "No Order selected";
506 return { error => "no_order_selected" };
509 $strsth .= join( ",", ('?') x @$externalid ) . ")";
510 $action = "CLAIM ISSUE";
511 $sthorders = $dbh->prepare($strsth);
512 $sthorders->execute( @$externalid );
513 $dataorders = $sthorders->fetchall_arrayref( {} );
516 if ( $type eq 'orderacquisition') {
518 SELECT aqorders.*,aqbasket.*,biblio.*,biblioitems.*
520 LEFT JOIN aqbasket ON aqbasket.basketno=aqorders.basketno
521 LEFT JOIN biblio ON aqorders.biblionumber=biblio.biblionumber
522 LEFT JOIN biblioitems ON aqorders.biblionumber=biblioitems.biblionumber
523 WHERE aqbasket.basketno = ?
524 AND orderstatus IN ('new','ordered')
528 carp "No basketnumber given";
529 return { error => "no_basketno" };
531 $action = "ACQUISITION ORDER";
532 $sthorders = $dbh->prepare($strsth);
533 $sthorders->execute($externalid);
534 $dataorders = $sthorders->fetchall_arrayref( {} );
538 $dbh->prepare("select * from aqbooksellers where id=?");
539 $sthbookseller->execute( $dataorders->[0]->{booksellerid} );
540 my $databookseller = $sthbookseller->fetchrow_hashref;
542 my $addressee = $type eq 'claimacquisition' || $type eq 'orderacquisition' ? 'acqprimary' : 'serialsprimary';
545 $dbh->prepare("SELECT * FROM aqcontacts WHERE booksellerid=? AND $type=1 ORDER BY $addressee DESC");
546 $sthcontact->execute( $dataorders->[0]->{booksellerid} );
547 my $datacontact = $sthcontact->fetchrow_hashref;
551 push @email, $databookseller->{bookselleremail} if $databookseller->{bookselleremail};
552 push @email, $datacontact->{email} if ( $datacontact && $datacontact->{email} );
554 warn "Bookseller $dataorders->[0]->{booksellerid} without emails";
555 return { error => "no_email" };
558 while ($addlcontact = $sthcontact->fetchrow_hashref) {
559 push @cc, $addlcontact->{email} if ( $addlcontact && $addlcontact->{email} );
562 my $userenv = C4::Context->userenv;
563 my $letter = GetPreparedLetter (
565 letter_code => $letter_code,
566 branchcode => $userenv->{branch},
568 'branches' => $userenv->{branch},
569 'aqbooksellers' => $databookseller,
570 'aqcontacts' => $datacontact,
572 repeat => $dataorders,
574 ) or return { error => "no_letter" };
576 # Remove the order tag
577 $letter->{content} =~ s/<order>(.*?)<\/order>/$1/gxms;
580 my $library = Koha::Libraries->find( $userenv->{branch} );
582 To => join( ',', @email),
583 Cc => join( ',', @cc),
584 From => $library->branchemail || C4::Context->preference('KohaAdminEmailAddress'),
585 Subject => Encode::encode( "UTF-8", "" . $letter->{title} ),
586 Message => $letter->{'is_html'}
587 ? _wrap_html( Encode::encode( "UTF-8", $letter->{'content'} ),
588 Encode::encode( "UTF-8", "" . $letter->{'title'} ))
589 : Encode::encode( "UTF-8", "" . $letter->{'content'} ),
590 'Content-Type' => $letter->{'is_html'}
591 ? 'text/html; charset="utf-8"'
592 : 'text/plain; charset="utf-8"',
595 if ($type eq 'claimacquisition' || $type eq 'claimissues' ) {
596 $mail{'Reply-to'} = C4::Context->preference('ReplytoDefault')
597 if C4::Context->preference('ReplytoDefault');
598 $mail{'Sender'} = C4::Context->preference('ReturnpathDefault')
599 if C4::Context->preference('ReturnpathDefault');
600 $mail{'Bcc'} = $userenv->{emailaddress}
601 if C4::Context->preference("ClaimsBccCopy");
604 unless ( Mail::Sendmail::sendmail(%mail) ) {
605 carp $Mail::Sendmail::error;
606 return { error => $Mail::Sendmail::error };
614 . join( ',', @email )
619 ) if C4::Context->preference("LetterLog");
621 # send an "account details" notice to a newly created user
622 elsif ( $type eq 'members' ) {
623 my $library = Koha::Libraries->find( $externalid->{branchcode} )->unblessed;
624 my $letter = GetPreparedLetter (
626 letter_code => $letter_code,
627 branchcode => $externalid->{'branchcode'},
629 'branches' => $library,
630 'borrowers' => $externalid->{'borrowernumber'},
632 substitute => { 'borrowers.password' => $externalid->{'password'} },
635 return { error => "no_email" } unless $externalid->{'emailaddr'};
636 my $email = Koha::Email->new();
637 my %mail = $email->create_message_headers(
639 to => $externalid->{'emailaddr'},
640 from => $library->{branchemail},
641 replyto => $library->{branchreplyto},
642 sender => $library->{branchreturnpath},
643 subject => Encode::encode( "UTF-8", "" . $letter->{'title'} ),
644 message => $letter->{'is_html'}
645 ? _wrap_html( Encode::encode( "UTF-8", $letter->{'content'} ),
646 Encode::encode( "UTF-8", "" . $letter->{'title'} ) )
647 : Encode::encode( "UTF-8", "" . $letter->{'content'} ),
648 contenttype => $letter->{'is_html'}
649 ? 'text/html; charset="utf-8"'
650 : 'text/plain; charset="utf-8"',
653 unless( Mail::Sendmail::sendmail(%mail) ) {
654 carp $Mail::Sendmail::error;
655 return { error => $Mail::Sendmail::error };
659 # If we come here, return an OK status
663 =head2 GetPreparedLetter( %params )
666 module => letter module, mandatory
667 letter_code => letter code, mandatory
668 branchcode => for letter selection, if missing default system letter taken
669 tables => a hashref with table names as keys. Values are either:
670 - a scalar - primary key value
671 - an arrayref - primary key values
672 - a hashref - full record
673 substitute => custom substitution key/value pairs
674 repeat => records to be substituted on consecutive lines:
675 - an arrayref - tries to guess what needs substituting by
676 taking remaining << >> tokensr; not recommended
677 - a hashref token => @tables - replaces <token> << >> << >> </token>
678 subtemplate for each @tables row; table is a hashref as above
679 want_librarian => boolean, if set to true triggers librarian details
680 substitution from the userenv
682 letter fields hashref (title & content useful)
686 sub GetPreparedLetter {
689 my $module = $params{module} or croak "No module";
690 my $letter_code = $params{letter_code} or croak "No letter_code";
691 my $branchcode = $params{branchcode} || '';
692 my $mtt = $params{message_transport_type} || 'email';
693 my $lang = $params{lang} || 'default';
695 my $letter = getletter( $module, $letter_code, $branchcode, $mtt, $lang );
698 $letter = getletter( $module, $letter_code, $branchcode, $mtt, 'default' )
699 or warn( "No $module $letter_code letter transported by " . $mtt ),
703 my $tables = $params{tables} || {};
704 my $substitute = $params{substitute} || {};
705 my $loops = $params{loops} || {}; # loops is not supported for historical notices syntax
706 my $repeat = $params{repeat};
707 %$tables || %$substitute || $repeat || %$loops
708 or carp( "ERROR: nothing to substitute - both 'tables', 'loops' and 'substitute' are empty" ),
710 my $want_librarian = $params{want_librarian};
713 while ( my ($token, $val) = each %$substitute ) {
714 if ( $token eq 'items.content' ) {
715 $val =~ s|\n|<br/>|g if $letter->{is_html};
718 $letter->{title} =~ s/<<$token>>/$val/g;
719 $letter->{content} =~ s/<<$token>>/$val/g;
723 my $OPACBaseURL = C4::Context->preference('OPACBaseURL');
724 $letter->{content} =~ s/<<OPACBaseURL>>/$OPACBaseURL/go;
726 if ($want_librarian) {
727 # parsing librarian name
728 my $userenv = C4::Context->userenv;
729 $letter->{content} =~ s/<<LibrarianFirstname>>/$userenv->{firstname}/go;
730 $letter->{content} =~ s/<<LibrarianSurname>>/$userenv->{surname}/go;
731 $letter->{content} =~ s/<<LibrarianEmailaddress>>/$userenv->{emailaddress}/go;
734 my ($repeat_no_enclosing_tags, $repeat_enclosing_tags);
737 if (ref ($repeat) eq 'ARRAY' ) {
738 $repeat_no_enclosing_tags = $repeat;
740 $repeat_enclosing_tags = $repeat;
744 if ($repeat_enclosing_tags) {
745 while ( my ($tag, $tag_tables) = each %$repeat_enclosing_tags ) {
746 if ( $letter->{content} =~ m!<$tag>(.*)</$tag>!s ) {
749 my %subletter = ( title => '', content => $subcontent );
750 _substitute_tables( \%subletter, $_ );
753 $letter->{content} =~ s!<$tag>.*</$tag>!join( "\n", @lines )!se;
759 _substitute_tables( $letter, $tables );
762 if ($repeat_no_enclosing_tags) {
763 if ( $letter->{content} =~ m/[^\n]*<<.*>>[^\n]*/so ) {
768 $c =~ s/<<count>>/$i/go;
769 foreach my $field ( keys %{$_} ) {
770 $c =~ s/(<<[^\.]+.$field>>)/$_->{$field}/;
774 } @$repeat_no_enclosing_tags;
776 my $replaceby = join( "\n", @lines );
777 $letter->{content} =~ s/\Q$line\E/$replaceby/s;
781 $letter->{content} = _process_tt(
783 content => $letter->{content},
786 substitute => $substitute,
790 $letter->{content} =~ s/<<\S*>>//go; #remove any stragglers
795 sub _substitute_tables {
796 my ( $letter, $tables ) = @_;
797 while ( my ($table, $param) = each %$tables ) {
800 my $ref = ref $param;
803 if ($ref && $ref eq 'HASH') {
807 my $sth = _parseletter_sth($table);
809 warn "_parseletter_sth('$table') failed to return a valid sth. No substitution will be done for that table.";
812 $sth->execute( $ref ? @$param : $param );
814 $values = $sth->fetchrow_hashref;
818 _parseletter ( $letter, $table, $values );
822 sub _parseletter_sth {
826 carp "ERROR: _parseletter_sth() called without argument (table)";
829 # NOTE: we used to check whether we had a statement handle cached in
830 # a %handles module-level variable. This was a dumb move and
831 # broke things for the rest of us. prepare_cached is a better
832 # way to cache statement handles anyway.
834 ($table eq 'biblio' ) ? "SELECT * FROM $table WHERE biblionumber = ?" :
835 ($table eq 'biblioitems' ) ? "SELECT * FROM $table WHERE biblionumber = ?" :
836 ($table eq 'items' ) ? "SELECT * FROM $table WHERE itemnumber = ?" :
837 ($table eq 'issues' ) ? "SELECT * FROM $table WHERE itemnumber = ?" :
838 ($table eq 'old_issues' ) ? "SELECT * FROM $table WHERE itemnumber = ? ORDER BY timestamp DESC LIMIT 1" :
839 ($table eq 'reserves' ) ? "SELECT * FROM $table WHERE borrowernumber = ? and biblionumber = ?" :
840 ($table eq 'borrowers' ) ? "SELECT * FROM $table WHERE borrowernumber = ?" :
841 ($table eq 'branches' ) ? "SELECT * FROM $table WHERE branchcode = ?" :
842 ($table eq 'suggestions' ) ? "SELECT * FROM $table WHERE suggestionid = ?" :
843 ($table eq 'aqbooksellers') ? "SELECT * FROM $table WHERE id = ?" :
844 ($table eq 'aqorders' ) ? "SELECT * FROM $table WHERE ordernumber = ?" :
845 ($table eq 'opac_news' ) ? "SELECT * FROM $table WHERE idnew = ?" :
846 ($table eq 'article_requests') ? "SELECT * FROM $table WHERE id = ?" :
847 ($table eq 'borrower_modifications') ? "SELECT * FROM $table WHERE verification_token = ?" :
848 ($table eq 'subscription') ? "SELECT * FROM $table WHERE subscriptionid = ?" :
849 ($table eq 'serial') ? "SELECT * FROM $table WHERE serialid = ?" :
852 warn "ERROR: No _parseletter_sth query for table '$table'";
853 return; # nothing to get
855 unless ($sth = C4::Context->dbh->prepare_cached($query)) {
856 warn "ERROR: Failed to prepare query: '$query'";
859 return $sth; # now cache is populated for that $table
862 =head2 _parseletter($letter, $table, $values)
865 - $letter : a hash to letter fields (title & content useful)
866 - $table : the Koha table to parse.
867 - $values_in : table record hashref
868 parse all fields from a table, and replace values in title & content with the appropriate value
869 (not exported sub, used only internally)
874 my ( $letter, $table, $values_in ) = @_;
876 # Work on a local copy of $values_in (passed by reference) to avoid side effects
877 # in callers ( by changing / formatting values )
878 my $values = $values_in ? { %$values_in } : {};
880 if ( $table eq 'borrowers' && $values->{'dateexpiry'} ){
881 $values->{'dateexpiry'} = format_sqldatetime( $values->{'dateexpiry'} );
884 if ( $table eq 'reserves' && $values->{'waitingdate'} ) {
885 $values->{'waitingdate'} = output_pref({ dt => dt_from_string( $values->{'waitingdate'} ), dateonly => 1 });
888 if ($letter->{content} && $letter->{content} =~ /<<today>>/) {
889 my $todaysdate = output_pref( DateTime->now() );
890 $letter->{content} =~ s/<<today>>/$todaysdate/go;
893 while ( my ($field, $val) = each %$values ) {
894 $val =~ s/\p{P}$// if $val && $table=~/biblio/;
895 #BZ 9886: Assuming that we want to eliminate ISBD punctuation here
896 #Therefore adding the test on biblio. This includes biblioitems,
897 #but excludes items. Removed unneeded global and lookahead.
899 if ( $table=~/^borrowers$/ && $field=~/^streettype$/ ) {
900 my $av = Koha::AuthorisedValues->search({ category => 'ROADTYPE', authorised_value => $val });
901 $val = $av->count ? $av->next->lib : '';
905 my $replacedby = defined ($val) ? $val : '';
907 and not $replacedby =~ m|0000-00-00|
908 and not $replacedby =~ m|9999-12-31|
909 and $replacedby =~ m|^\d{4}-\d{2}-\d{2}( \d{2}:\d{2}:\d{2})?$| )
911 # If the value is XXXX-YY-ZZ[ AA:BB:CC] we assume it is a date
912 my $dateonly = defined $1 ? 0 : 1; #$1 refers to the capture group wrapped in parentheses. In this case, that's the hours, minutes, seconds.
913 my $re_dateonly_filter = qr{ $field( \s* \| \s* dateonly\s*)?>> }xms;
915 for my $letter_field ( qw( title content ) ) {
916 my $filter_string_used = q{};
917 if ( $letter->{ $letter_field } =~ $re_dateonly_filter ) {
918 # We overwrite $dateonly if the filter exists and we have a time in the datetime
919 $filter_string_used = $1 || q{};
920 $dateonly = $1 unless $dateonly;
922 my $replacedby_date = eval {
923 output_pref({ dt => dt_from_string( $replacedby ), dateonly => $dateonly });
926 if ( $letter->{ $letter_field } ) {
927 $letter->{ $letter_field } =~ s/\Q<<$table.$field$filter_string_used>>\E/$replacedby_date/g;
928 $letter->{ $letter_field } =~ s/\Q<<$field$filter_string_used>>\E/$replacedby_date/g;
932 # Other fields replacement
934 for my $letter_field ( qw( title content ) ) {
935 if ( $letter->{ $letter_field } ) {
936 $letter->{ $letter_field } =~ s/<<$table.$field>>/$replacedby/g;
937 $letter->{ $letter_field } =~ s/<<$field>>/$replacedby/g;
943 if ($table eq 'borrowers' && $letter->{content}) {
944 if ( my $attributes = GetBorrowerAttributes($values->{borrowernumber}) ) {
946 foreach (@$attributes) {
947 my $code = $_->{code};
948 my $val = $_->{value_description} || $_->{value};
949 $val =~ s/\p{P}(?=$)//g if $val;
950 next unless $val gt '';
952 push @{ $attr{$code} }, $val;
954 while ( my ($code, $val_ar) = each %attr ) {
955 my $replacefield = "<<borrower-attribute:$code>>";
956 my $replacedby = join ',', @$val_ar;
957 $letter->{content} =~ s/$replacefield/$replacedby/g;
966 my $success = EnqueueLetter( { letter => $letter,
967 borrowernumber => '12', message_transport_type => 'email' } )
969 places a letter in the message_queue database table, which will
970 eventually get processed (sent) by the process_message_queue.pl
971 cronjob when it calls SendQueuedMessages.
973 return message_id on success
978 my $params = shift or return;
980 return unless exists $params->{'letter'};
981 # return unless exists $params->{'borrowernumber'};
982 return unless exists $params->{'message_transport_type'};
984 my $content = $params->{letter}->{content};
985 $content =~ s/\s+//g if(defined $content);
986 if ( not defined $content or $content eq '' ) {
987 warn "Trying to add an empty message to the message queue" if $debug;
991 # If we have any attachments we should encode then into the body.
992 if ( $params->{'attachments'} ) {
993 $params->{'letter'} = _add_attachments(
994 { letter => $params->{'letter'},
995 attachments => $params->{'attachments'},
996 message => MIME::Lite->new( Type => 'multipart/mixed' ),
1001 my $dbh = C4::Context->dbh();
1002 my $statement = << 'ENDSQL';
1003 INSERT INTO message_queue
1004 ( borrowernumber, subject, content, metadata, letter_code, message_transport_type, status, time_queued, to_address, from_address, content_type )
1006 ( ?, ?, ?, ?, ?, ?, ?, NOW(), ?, ?, ? )
1009 my $sth = $dbh->prepare($statement);
1010 my $result = $sth->execute(
1011 $params->{'borrowernumber'}, # borrowernumber
1012 $params->{'letter'}->{'title'}, # subject
1013 $params->{'letter'}->{'content'}, # content
1014 $params->{'letter'}->{'metadata'} || '', # metadata
1015 $params->{'letter'}->{'code'} || '', # letter_code
1016 $params->{'message_transport_type'}, # message_transport_type
1018 $params->{'to_address'}, # to_address
1019 $params->{'from_address'}, # from_address
1020 $params->{'letter'}->{'content-type'}, # content_type
1022 return $dbh->last_insert_id(undef,undef,'message_queue', undef);
1025 =head2 SendQueuedMessages ([$hashref])
1027 my $sent = SendQueuedMessages({
1028 letter_code => $letter_code,
1029 borrowernumber => $who_letter_is_for,
1035 Sends all of the 'pending' items in the message queue, unless
1036 parameters are passed.
1038 The letter_code, borrowernumber and limit parameters are used
1039 to build a parameter set for _get_unsent_messages, thus limiting
1040 which pending messages will be processed. They are all optional.
1042 The verbose parameter can be used to generate debugging output.
1043 It is also optional.
1045 Returns number of messages sent.
1049 sub SendQueuedMessages {
1052 my $which_unsent_messages = {
1053 'limit' => $params->{'limit'} // 0,
1054 'borrowernumber' => $params->{'borrowernumber'} // q{},
1055 'letter_code' => $params->{'letter_code'} // q{},
1056 'type' => $params->{'type'} // q{},
1058 my $unsent_messages = _get_unsent_messages( $which_unsent_messages );
1059 MESSAGE: foreach my $message ( @$unsent_messages ) {
1060 my $message_object = Koha::Notice::Messages->find( $message->{message_id} );
1061 # If this fails the database is unwritable and we won't manage to send a message that continues to be marked 'pending'
1062 $message_object->make_column_dirty('status');
1063 return unless $message_object->store;
1065 # warn Data::Dumper->Dump( [ $message ], [ 'message' ] );
1066 warn sprintf( 'sending %s message to patron: %s',
1067 $message->{'message_transport_type'},
1068 $message->{'borrowernumber'} || 'Admin' )
1069 if $params->{'verbose'} or $debug;
1070 # This is just begging for subclassing
1071 next MESSAGE if ( lc($message->{'message_transport_type'}) eq 'rss' );
1072 if ( lc( $message->{'message_transport_type'} ) eq 'email' ) {
1073 _send_message_by_email( $message, $params->{'username'}, $params->{'password'}, $params->{'method'} );
1075 elsif ( lc( $message->{'message_transport_type'} ) eq 'sms' ) {
1076 if ( C4::Context->preference('SMSSendDriver') eq 'Email' ) {
1077 my $patron = Koha::Patrons->find( $message->{borrowernumber} );
1078 my $sms_provider = Koha::SMS::Providers->find( $patron->sms_provider_id );
1079 unless ( $sms_provider ) {
1080 warn sprintf( "Patron %s has no sms provider id set!", $message->{'borrowernumber'} ) if $params->{'verbose'} or $debug;
1081 _set_message_status( { message_id => $message->{'message_id'}, status => 'failed' } );
1084 unless ( $patron->smsalertnumber ) {
1085 _set_message_status( { message_id => $message->{'message_id'}, status => 'failed' } );
1086 warn sprintf( "No smsalertnumber found for patron %s!", $message->{'borrowernumber'} ) if $params->{'verbose'} or $debug;
1089 $message->{to_address} = $patron->smsalertnumber; #Sometime this is set to email - sms should always use smsalertnumber
1090 $message->{to_address} .= '@' . $sms_provider->domain();
1091 _update_message_to_address($message->{'message_id'},$message->{to_address});
1092 _send_message_by_email( $message, $params->{'username'}, $params->{'password'}, $params->{'method'} );
1094 _send_message_by_sms( $message );
1098 return scalar( @$unsent_messages );
1101 =head2 GetRSSMessages
1103 my $message_list = GetRSSMessages( { limit => 10, borrowernumber => '14' } )
1105 returns a listref of all queued RSS messages for a particular person.
1109 sub GetRSSMessages {
1112 return unless $params;
1113 return unless ref $params;
1114 return unless $params->{'borrowernumber'};
1116 return _get_unsent_messages( { message_transport_type => 'rss',
1117 limit => $params->{'limit'},
1118 borrowernumber => $params->{'borrowernumber'}, } );
1121 =head2 GetPrintMessages
1123 my $message_list = GetPrintMessages( { borrowernumber => $borrowernumber } )
1125 Returns a arrayref of all queued print messages (optionally, for a particular
1130 sub GetPrintMessages {
1131 my $params = shift || {};
1133 return _get_unsent_messages( { message_transport_type => 'print',
1134 borrowernumber => $params->{'borrowernumber'},
1138 =head2 GetQueuedMessages ([$hashref])
1140 my $messages = GetQueuedMessage( { borrowernumber => '123', limit => 20 } );
1142 fetches messages out of the message queue.
1145 list of hashes, each has represents a message in the message queue.
1149 sub GetQueuedMessages {
1152 my $dbh = C4::Context->dbh();
1153 my $statement = << 'ENDSQL';
1154 SELECT message_id, borrowernumber, subject, content, message_transport_type, status, time_queued
1160 if ( exists $params->{'borrowernumber'} ) {
1161 push @whereclauses, ' borrowernumber = ? ';
1162 push @query_params, $params->{'borrowernumber'};
1165 if ( @whereclauses ) {
1166 $statement .= ' WHERE ' . join( 'AND', @whereclauses );
1169 if ( defined $params->{'limit'} ) {
1170 $statement .= ' LIMIT ? ';
1171 push @query_params, $params->{'limit'};
1174 my $sth = $dbh->prepare( $statement );
1175 my $result = $sth->execute( @query_params );
1176 return $sth->fetchall_arrayref({});
1179 =head2 GetMessageTransportTypes
1181 my @mtt = GetMessageTransportTypes();
1183 returns an arrayref of transport types
1187 sub GetMessageTransportTypes {
1188 my $dbh = C4::Context->dbh();
1189 my $mtts = $dbh->selectcol_arrayref("
1190 SELECT message_transport_type
1191 FROM message_transport_types
1192 ORDER BY message_transport_type
1199 my $message = C4::Letters::Message($message_id);
1204 my ( $message_id ) = @_;
1205 return unless $message_id;
1206 my $dbh = C4::Context->dbh;
1207 return $dbh->selectrow_hashref(q|
1208 SELECT message_id, borrowernumber, subject, content, metadata, letter_code, message_transport_type, status, time_queued, to_address, from_address, content_type
1210 WHERE message_id = ?
1211 |, {}, $message_id );
1214 =head2 ResendMessage
1216 Attempt to resend a message which has failed previously.
1218 my $has_been_resent = C4::Letters::ResendMessage($message_id);
1220 Updates the message to 'pending' status so that
1221 it will be resent later on.
1223 returns 1 on success, 0 on failure, undef if no message was found
1228 my $message_id = shift;
1229 return unless $message_id;
1231 my $message = GetMessage( $message_id );
1232 return unless $message;
1234 if ( $message->{status} ne 'pending' ) {
1235 $rv = C4::Letters::_set_message_status({
1236 message_id => $message_id,
1237 status => 'pending',
1239 $rv = $rv > 0? 1: 0;
1240 # Clear destination email address to force address update
1241 _update_message_to_address( $message_id, undef ) if $rv &&
1242 $message->{message_transport_type} eq 'email';
1247 =head2 _add_attachements
1250 letter - the standard letter hashref
1251 attachments - listref of attachments. each attachment is a hashref of:
1252 type - the mime type, like 'text/plain'
1253 content - the actual attachment
1254 filename - the name of the attachment.
1255 message - a MIME::Lite object to attach these to.
1257 returns your letter object, with the content updated.
1261 sub _add_attachments {
1264 my $letter = $params->{'letter'};
1265 my $attachments = $params->{'attachments'};
1266 return $letter unless @$attachments;
1267 my $message = $params->{'message'};
1269 # First, we have to put the body in as the first attachment
1271 Type => $letter->{'content-type'} || 'TEXT',
1272 Data => $letter->{'is_html'}
1273 ? _wrap_html($letter->{'content'}, $letter->{'title'})
1274 : $letter->{'content'},
1277 foreach my $attachment ( @$attachments ) {
1279 Type => $attachment->{'type'},
1280 Data => $attachment->{'content'},
1281 Filename => $attachment->{'filename'},
1284 # we're forcing list context here to get the header, not the count back from grep.
1285 ( $letter->{'content-type'} ) = grep( /^Content-Type:/, split( /\n/, $params->{'message'}->header_as_string ) );
1286 $letter->{'content-type'} =~ s/^Content-Type:\s+//;
1287 $letter->{'content'} = $message->body_as_string;
1293 =head2 _get_unsent_messages
1295 This function's parameter hash reference takes the following
1296 optional named parameters:
1297 message_transport_type: method of message sending (e.g. email, sms, etc.)
1298 borrowernumber : who the message is to be sent
1299 letter_code : type of message being sent (e.g. PASSWORD_RESET)
1300 limit : maximum number of messages to send
1302 This function returns an array of matching hash referenced rows from
1303 message_queue with some borrower information added.
1307 sub _get_unsent_messages {
1310 my $dbh = C4::Context->dbh();
1312 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
1313 FROM message_queue mq
1314 LEFT JOIN borrowers b ON b.borrowernumber = mq.borrowernumber
1318 my @query_params = ('pending');
1319 if ( ref $params ) {
1320 if ( $params->{'message_transport_type'} ) {
1321 $statement .= ' AND mq.message_transport_type = ? ';
1322 push @query_params, $params->{'message_transport_type'};
1324 if ( $params->{'borrowernumber'} ) {
1325 $statement .= ' AND mq.borrowernumber = ? ';
1326 push @query_params, $params->{'borrowernumber'};
1328 if ( $params->{'letter_code'} ) {
1329 $statement .= ' AND mq.letter_code = ? ';
1330 push @query_params, $params->{'letter_code'};
1332 if ( $params->{'type'} ) {
1333 $statement .= ' AND message_transport_type = ? ';
1334 push @query_params, $params->{'type'};
1336 if ( $params->{'limit'} ) {
1337 $statement .= ' limit ? ';
1338 push @query_params, $params->{'limit'};
1342 $debug and warn "_get_unsent_messages SQL: $statement";
1343 $debug and warn "_get_unsent_messages params: " . join(',',@query_params);
1344 my $sth = $dbh->prepare( $statement );
1345 my $result = $sth->execute( @query_params );
1346 return $sth->fetchall_arrayref({});
1349 sub _send_message_by_email {
1350 my $message = shift or return;
1351 my ($username, $password, $method) = @_;
1353 my $patron = Koha::Patrons->find( $message->{borrowernumber} );
1354 my $to_address = $message->{'to_address'};
1355 unless ($to_address) {
1357 warn "FAIL: No 'to_address' and INVALID borrowernumber ($message->{borrowernumber})";
1358 _set_message_status( { message_id => $message->{'message_id'},
1359 status => 'failed' } );
1362 $to_address = C4::Members::GetNoticeEmailAddress( $message->{'borrowernumber'} );
1363 unless ($to_address) {
1364 # warn "FAIL: No 'to_address' and no email for " . ($member->{surname} ||'') . ", borrowernumber ($message->{borrowernumber})";
1365 # warning too verbose for this more common case?
1366 _set_message_status( { message_id => $message->{'message_id'},
1367 status => 'failed' } );
1372 my $utf8 = decode('MIME-Header', $message->{'subject'} );
1373 $message->{subject}= encode('MIME-Header', $utf8);
1374 my $subject = encode('UTF-8', $message->{'subject'});
1375 my $content = encode('UTF-8', $message->{'content'});
1376 my $content_type = $message->{'content_type'} || 'text/plain; charset="UTF-8"';
1377 my $is_html = $content_type =~ m/html/io;
1378 my $branch_email = undef;
1379 my $branch_replyto = undef;
1380 my $branch_returnpath = undef;
1382 my $library = $patron->library;
1383 $branch_email = $library->branchemail;
1384 $branch_replyto = $library->branchreplyto;
1385 $branch_returnpath = $library->branchreturnpath;
1387 my $email = Koha::Email->new();
1388 my %sendmail_params = $email->create_message_headers(
1391 from => $message->{'from_address'} || $branch_email,
1392 replyto => $branch_replyto,
1393 sender => $branch_returnpath,
1394 subject => $subject,
1395 message => $is_html ? _wrap_html( $content, $subject ) : $content,
1396 contenttype => $content_type
1400 $sendmail_params{'Auth'} = {user => $username, pass => $password, method => $method} if $username;
1401 if ( my $bcc = C4::Context->preference('NoticeBcc') ) {
1402 $sendmail_params{ Bcc } = $bcc;
1405 _update_message_to_address($message->{'message_id'},$to_address) unless $message->{to_address}; #if initial message address was empty, coming here means that a to address was found and queue should be updated
1407 if ( Mail::Sendmail::sendmail( %sendmail_params ) ) {
1408 _set_message_status( { message_id => $message->{'message_id'},
1409 status => 'sent' } );
1412 _set_message_status( { message_id => $message->{'message_id'},
1413 status => 'failed' } );
1414 carp $Mail::Sendmail::error;
1420 my ($content, $title) = @_;
1422 my $css = C4::Context->preference("NoticeCSS") || '';
1423 $css = qq{<link rel="stylesheet" type="text/css" href="$css">} if $css;
1425 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
1426 "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
1427 <html lang="en" xml:lang="en" xmlns="http://www.w3.org/1999/xhtml">
1429 <title>$title</title>
1430 <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
1441 my ( $message ) = @_;
1442 my $dbh = C4::Context->dbh;
1443 my $count = $dbh->selectrow_array(q|
1446 WHERE message_transport_type = ?
1447 AND borrowernumber = ?
1449 AND CAST(time_queued AS date) = CAST(NOW() AS date)
1452 |, {}, $message->{message_transport_type}, $message->{borrowernumber}, $message->{letter_code}, $message->{content} );
1456 sub _send_message_by_sms {
1457 my $message = shift or return;
1458 my $patron = Koha::Patrons->find( $message->{borrowernumber} );
1460 unless ( $patron and $patron->smsalertnumber ) {
1461 _set_message_status( { message_id => $message->{'message_id'},
1462 status => 'failed' } );
1466 if ( _is_duplicate( $message ) ) {
1467 _set_message_status( { message_id => $message->{'message_id'},
1468 status => 'failed' } );
1472 my $success = C4::SMS->send_sms( { destination => $patron->smsalertnumber,
1473 message => $message->{'content'},
1475 _set_message_status( { message_id => $message->{'message_id'},
1476 status => ($success ? 'sent' : 'failed') } );
1480 sub _update_message_to_address {
1482 my $dbh = C4::Context->dbh();
1483 $dbh->do('UPDATE message_queue SET to_address=? WHERE message_id=?',undef,($to,$id));
1486 sub _set_message_status {
1487 my $params = shift or return;
1489 foreach my $required_parameter ( qw( message_id status ) ) {
1490 return unless exists $params->{ $required_parameter };
1493 my $dbh = C4::Context->dbh();
1494 my $statement = 'UPDATE message_queue SET status= ? WHERE message_id = ?';
1495 my $sth = $dbh->prepare( $statement );
1496 my $result = $sth->execute( $params->{'status'},
1497 $params->{'message_id'} );
1502 my ( $params ) = @_;
1504 my $content = $params->{content};
1505 my $tables = $params->{tables};
1506 my $loops = $params->{loops};
1507 my $substitute = $params->{substitute} || {};
1509 my $use_template_cache = C4::Context->config('template_cache_dir') && defined $ENV{GATEWAY_INTERFACE};
1510 my $template = Template->new(
1514 PLUGIN_BASE => 'Koha::Template::Plugin',
1515 COMPILE_EXT => $use_template_cache ? '.ttc' : '',
1516 COMPILE_DIR => $use_template_cache ? C4::Context->config('template_cache_dir') : '',
1518 ENCODING => 'UTF-8',
1520 ) or die Template->error();
1522 my $tt_params = { %{ _get_tt_params( $tables ) }, %{ _get_tt_params( $loops, 'is_a_loop' ) }, %$substitute };
1524 $content = add_tt_filters( $content );
1525 $content = qq|[% USE KohaDates %][% USE Remove_MARC_punctuation %]$content|;
1528 $template->process( \$content, $tt_params, \$output ) || croak "ERROR PROCESSING TEMPLATE: " . $template->error();
1533 sub _get_tt_params {
1534 my ($tables, $is_a_loop) = @_;
1540 article_requests => {
1541 module => 'Koha::ArticleRequests',
1542 singular => 'article_request',
1543 plural => 'article_requests',
1547 module => 'Koha::Biblios',
1548 singular => 'biblio',
1549 plural => 'biblios',
1550 pk => 'biblionumber',
1553 module => 'Koha::Biblioitems',
1554 singular => 'biblioitem',
1555 plural => 'biblioitems',
1556 pk => 'biblioitemnumber',
1559 module => 'Koha::Patrons',
1560 singular => 'borrower',
1561 plural => 'borrowers',
1562 pk => 'borrowernumber',
1565 module => 'Koha::Libraries',
1566 singular => 'branch',
1567 plural => 'branches',
1571 module => 'Koha::Items',
1577 module => 'Koha::News',
1583 module => 'Koha::Acquisition::Orders',
1584 singular => 'order',
1586 pk => 'ordernumber',
1589 module => 'Koha::Holds',
1592 fk => [ 'borrowernumber', 'biblionumber' ],
1595 module => 'Koha::Serials',
1596 singular => 'serial',
1597 plural => 'serials',
1601 module => 'Koha::Subscriptions',
1602 singular => 'subscription',
1603 plural => 'subscriptions',
1604 pk => 'subscriptionid',
1607 module => 'Koha::Suggestions',
1608 singular => 'suggestion',
1609 plural => 'suggestions',
1610 pk => 'suggestionid',
1613 module => 'Koha::Checkouts',
1614 singular => 'checkout',
1615 plural => 'checkouts',
1619 module => 'Koha::Old::Checkouts',
1620 singular => 'old_checkout',
1621 plural => 'old_checkouts',
1625 module => 'Koha::Checkouts',
1626 singular => 'overdue',
1627 plural => 'overdues',
1630 borrower_modifications => {
1631 module => 'Koha::Patron::Modifications',
1632 singular => 'patron_modification',
1633 plural => 'patron_modifications',
1634 fk => 'verification_token',
1638 foreach my $table ( keys %$tables ) {
1639 next unless $config->{$table};
1641 my $ref = ref( $tables->{$table} ) || q{};
1642 my $module = $config->{$table}->{module};
1644 if ( can_load( modules => { $module => undef } ) ) {
1645 my $pk = $config->{$table}->{pk};
1646 my $fk = $config->{$table}->{fk};
1649 my $values = $tables->{$table} || [];
1650 unless ( ref( $values ) eq 'ARRAY' ) {
1651 croak "ERROR processing table $table. Wrong API call.";
1653 my $key = $pk ? $pk : $fk;
1654 # $key does not come from user input
1655 my $objects = $module->search(
1656 { $key => $values },
1658 # We want to retrieve the data in the same order
1660 # field is a MySQLism, but they are no other way to do it
1661 # To be generic we could do it in perl, but we will need to fetch
1662 # all the data then order them
1663 @$values ? ( order_by => \[ "field($key, " . join( ', ', @$values ) . ")" ] ) : ()
1666 $params->{ $config->{$table}->{plural} } = $objects;
1668 elsif ( $ref eq q{} || $ref eq 'HASH' ) {
1669 my $id = ref $ref eq 'HASH' ? $tables->{$table}->{$pk} : $tables->{$table};
1671 if ( $fk ) { # Using a foreign key for lookup
1672 if ( ref( $fk ) eq 'ARRAY' ) { # Foreign key is multi-column
1674 foreach my $key ( @$fk ) {
1675 $search->{$key} = $id->{$key};
1677 $object = $module->search( $search )->last();
1678 } else { # Foreign key is single column
1679 $object = $module->search( { $fk => $id } )->last();
1681 } else { # using the table's primary key for lookup
1682 $object = $module->find($id);
1684 $params->{ $config->{$table}->{singular} } = $object;
1686 else { # $ref eq 'ARRAY'
1688 if ( @{ $tables->{$table} } == 1 ) { # Param is a single key
1689 $object = $module->search( { $pk => $tables->{$table} } )->last();
1691 else { # Params are mutliple foreign keys
1692 croak "Multiple foreign keys (table $table) should be passed using an hashref";
1694 $params->{ $config->{$table}->{singular} } = $object;
1698 croak "ERROR LOADING MODULE $module: $Module::Load::Conditional::ERROR";
1702 $params->{today} = output_pref({ dt => dt_from_string, dateformat => 'iso' });
1707 =head3 add_tt_filters
1709 $content = add_tt_filters( $content );
1711 Add TT filters to some specific fields if needed.
1713 For now we only add the Remove_MARC_punctuation TT filter to biblio and biblioitem fields
1717 sub add_tt_filters {
1718 my ( $content ) = @_;
1719 $content =~ s|\[%\s*biblio\.(.*?)\s*%\]|[% biblio.$1 \| \$Remove_MARC_punctuation %]|gxms;
1720 $content =~ s|\[%\s*biblioitem\.(.*?)\s*%\]|[% biblioitem.$1 \| \$Remove_MARC_punctuation %]|gxms;
1724 =head2 get_item_content
1726 my $item = Koha::Items->find(...)->unblessed;
1727 my @item_content_fields = qw( date_due title barcode author itemnumber );
1728 my $item_content = C4::Letters::get_item_content({
1730 item_content_fields => \@item_content_fields
1733 This function generates a tab-separated list of values for the passed item. Dates
1734 are formatted following the current setup.
1738 sub get_item_content {
1739 my ( $params ) = @_;
1740 my $item = $params->{item};
1741 my $dateonly = $params->{dateonly} || 0;
1742 my $item_content_fields = $params->{item_content_fields} || [];
1744 return unless $item;
1746 my @item_info = map {
1750 { dt => dt_from_string( $item->{$_} ), dateonly => $dateonly } );
1754 } @$item_content_fields;
1755 return join( "\t", @item_info ) . "\n";