use Mail::Sendmail;
use C4::Members;
+use C4::Members::Attributes qw(GetBorrowerAttributes);
use C4::Branch;
use C4::Log;
use C4::SMS;
use C4::Debug;
use Date::Calc qw( Add_Delta_Days );
use Encode;
+use Unicode::Normalize;
use Carp;
use vars qw($VERSION @ISA @EXPORT @EXPORT_OK %EXPORT_TAGS);
BEGIN {
require Exporter;
# set the version for version checking
- $VERSION = 3.01;
+ $VERSION = 3.07.00.049;
@ISA = qw(Exporter);
@EXPORT = qw(
- &GetLetters &getletter &addalert &getalert &delalert &findrelatedto &SendAlerts GetPrintMessages
+ &GetLetters &GetPreparedLetter &GetWrappedLetter &addalert &getalert &delalert &findrelatedto &SendAlerts &GetPrintMessages
);
}
=cut
-sub GetLetters (;$) {
+sub GetLetters {
# returns a reference to a hash of references to ALL letters...
my $cat = shift;
return \%letters;
}
-sub getletter ($$) {
- my ( $module, $code ) = @_;
+=head2 GetLetter( %params )
+
+ retrieves the letter template
+
+ %params hash:
+ module => letter module, mandatory
+ letter_code => letter code, mandatory
+ branchcode => for letter selection, if missing default system letter taken
+ Return value:
+ letter fields hashref (title & content useful)
+
+=cut
+
+sub GetLetter {
+ my %params = @_;
+
+ my $module = $params{module} or croak "No module";
+ my $letter_code = $params{letter_code} or croak "No letter_code";
+ my $branchcode = $params{branchcode} || '';
+
+ my $letter = getletter( $module, $letter_code, $branchcode )
+ or warn( "No $module $letter_code letter"),
+ return;
+
+ return $letter;
+}
+
+my %letter;
+sub getletter {
+ my ( $module, $code, $branchcode ) = @_;
+
+ $branchcode ||= '';
+
+ if ( C4::Context->preference('IndependantBranches')
+ and $branchcode
+ and C4::Context->userenv ) {
+
+ $branchcode = C4::Context->userenv->{'branch'};
+ }
+
+ if ( my $l = $letter{$module}{$code}{$branchcode} ) {
+ return { %$l }; # deep copy
+ }
+
my $dbh = C4::Context->dbh;
- my $sth = $dbh->prepare("select * from letter where module=? and code=?");
- $sth->execute( $module, $code );
- my $line = $sth->fetchrow_hashref;
- return $line;
+ 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 $line = $sth->fetchrow_hashref
+ or return;
+ $line->{'content-type'} = 'text/html; charset="UTF-8"' if $line->{is_html};
+ $letter{$module}{$code}{$branchcode} = $line;
+ return { %$line };
}
=head2 addalert ($borrowernumber, $type, $externalid)
=cut
-sub addalert ($$$) {
+sub addalert {
my ( $borrowernumber, $type, $externalid ) = @_;
my $dbh = C4::Context->dbh;
my $sth =
=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=?");
=cut
-sub getalert (;$$$) {
+sub getalert {
my ( $borrowernumber, $type, $externalid ) = @_;
my $dbh = C4::Context->dbh;
- my $query = "SELECT * FROM alert WHERE";
+ my $query = "SELECT a.*, b.branchcode FROM alert a JOIN borrowers b USING(borrowernumber) WHERE";
my @bind;
if ($borrowernumber and $borrowernumber =~ /^\d+$/) {
$query .= " borrowernumber=? AND ";
# 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);
parameters :
- $type : the type of alert
- $externalid : the id of the "object" to query
- - $letter : the letter to send.
+ - $letter_code : the letter to send.
send an alert to all borrowers having put an alert on a given subject.
=cut
sub SendAlerts {
- my ( $type, $externalid, $letter ) = @_;
+ my ( $type, $externalid, $letter_code ) = @_;
my $dbh = C4::Context->dbh;
- my $strsth;
if ( $type eq 'issue' ) {
- # warn "sending issues...";
- my $letter = getletter( 'serial', $letter );
-
# prepare the letter...
# search the biblionumber
my $sth =
$dbh->prepare(
"SELECT biblionumber FROM subscription WHERE subscriptionid=?");
$sth->execute($externalid);
- my ($biblionumber) = $sth->fetchrow;
-
- # parsing branch info
- my $userenv = C4::Context->userenv;
- parseletter( $letter, 'branches', $userenv->{branch} );
-
- # parsing librarian name
- $letter->{content} =~ s/<<LibrarianFirstname>>/$userenv->{firstname}/g;
- $letter->{content} =~ s/<<LibrarianSurname>>/$userenv->{surname}/g;
- $letter->{content} =~
- s/<<LibrarianEmailaddress>>/$userenv->{emailaddress}/g;
-
- # parsing biblio information
- parseletter( $letter, 'biblio', $biblionumber );
- parseletter( $letter, 'biblioitems', $biblionumber );
+ my ($biblionumber) = $sth->fetchrow
+ or warn( "No subscription for '$externalid'" ),
+ return;
+ my %letter;
# find the list of borrowers to alert
my $alerts = getalert( '', 'issue', $externalid );
foreach (@$alerts) {
- # and parse borrower ...
- my $innerletter = $letter;
my $borinfo = C4::Members::GetMember('borrowernumber' => $_->{'borrowernumber'});
- parseletter( $innerletter, 'borrowers', $_->{'borrowernumber'} );
+ my $email = $borinfo->{email} or next;
+
+ # warn "sending issues...";
+ my $userenv = C4::Context->userenv;
+ my $letter = GetPreparedLetter (
+ module => 'serial',
+ letter_code => $letter_code,
+ branchcode => $userenv->{branch},
+ tables => {
+ 'branches' => $_->{branchcode},
+ 'biblio' => $biblionumber,
+ 'biblioitems' => $biblionumber,
+ 'borrowers' => $borinfo,
+ },
+ want_librarian => 1,
+ ) or return;
# ... then send mail
- if ( $borinfo->{email} ) {
- my %mail = (
- To => $borinfo->{email},
- From => $borinfo->{email},
- Subject => "" . $innerletter->{title},
- Message => "" . $innerletter->{content},
- 'Content-Type' => 'text/plain; charset="utf8"',
- );
- sendmail(%mail) or carp $Mail::Sendmail::error;
-
- }
+ my %mail = (
+ To => $email,
+ From => $email,
+ Subject => Encode::encode( "utf8", "" . $letter->{title} ),
+ Message => Encode::encode( "utf8", "" . $letter->{content} ),
+ 'Content-Type' => 'text/plain; charset="utf8"',
+ );
+ sendmail(%mail) or carp $Mail::Sendmail::error;
}
}
- elsif ( $type eq 'claimacquisition' ) {
-
- $letter = getletter( 'claimacquisition', $letter );
+ elsif ( $type eq 'claimacquisition' or $type eq 'claimissues' ) {
# prepare the letter...
# search the biblionumber
- $strsth = qq{
- SELECT aqorders.*,aqbasket.*,biblio.*,biblioitems.*,aqbooksellers.*
+ my $strsth = $type eq 'claimacquisition'
+ ? qq{
+ SELECT aqorders.*,aqbasket.*,biblio.*,biblioitems.*,aqbooksellers.*,
+ aqbooksellers.id AS booksellerid
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 aqbooksellers ON aqbasket.booksellerid=aqbooksellers.id
WHERE aqorders.ordernumber IN (
- }
- . join( ",", @$externalid ) . ")";
- }
- elsif ( $type eq 'claimissues' ) {
-
- $letter = getletter( 'claimissues', $letter );
-
- # prepare the letter...
- # search the biblionumber
- $strsth = qq{
- SELECT serial.*,subscription.*, biblio.*, aqbooksellers.*
+ }
+ : qq{
+ SELECT serial.*,subscription.*, biblio.*, aqbooksellers.*,
+ aqbooksellers.id AS booksellerid
FROM serial
LEFT JOIN subscription ON serial.subscriptionid=subscription.subscriptionid
LEFT JOIN biblio ON serial.biblionumber=biblio.biblionumber
LEFT JOIN aqbooksellers ON subscription.aqbooksellerid=aqbooksellers.id
WHERE serial.serialid IN (
- }
- . join( ",", @$externalid ) . ")";
- }
-
- if ( $type eq 'claimacquisition' or $type eq 'claimissues' ) {
+ };
+ $strsth .= join( ",", @$externalid ) . ")";
my $sthorders = $dbh->prepare($strsth);
$sthorders->execute;
- my @fields = map {
- $sthorders->{mysql_table}[$_] . "." . $sthorders->{NAME}[$_] }
- (0 .. $#{$sthorders->{NAME}} ) ;
-
- my @orders_infos;
- while ( my $row = $sthorders->fetchrow_arrayref() ) {
- my %rec = ();
- @rec{@fields} = @$row;
- push @orders_infos, \%rec;
+ my $dataorders = $sthorders->fetchall_arrayref( {} );
+
+ my $sthbookseller =
+ $dbh->prepare("select * from aqbooksellers where id=?");
+ $sthbookseller->execute( $dataorders->[0]->{booksellerid} );
+ my $databookseller = $sthbookseller->fetchrow_hashref;
+
+ my @email;
+ push @email, $databookseller->{bookselleremail} if $databookseller->{bookselleremail};
+ push @email, $databookseller->{contemail} if $databookseller->{contemail};
+ unless (@email) {
+ warn "Bookseller $dataorders->[0]->{booksellerid} without emails";
+ return { error => "no_email" };
}
- # parsing branch info
my $userenv = C4::Context->userenv;
- parseletter( $letter, 'branches', $userenv->{branch} );
-
- # parsing librarian name
- $letter->{content} =~ s/<<LibrarianFirstname>>/$userenv->{firstname}/g;
- $letter->{content} =~ s/<<LibrarianSurname>>/$userenv->{surname}/g;
- $letter->{content} =~ s/<<LibrarianEmailaddress>>/$userenv->{emailaddress}/g;
-
- # Get Fields remplacement
- my $order_format = $1 if ( $letter->{content} =~ m/(<order>.*<\/order>)/xms );
-
- # Foreach field to remplace
- while ( $letter->{content} =~ m/<<([^>]*)>>/g ) {
- my $field = $1;
- my $value = $orders_infos[0]->{$field} || "";
- $value = sprintf("%.2f", $value) if $field =~ /price/;
- $letter->{content} =~ s/<<$field>>/$value/g;
- }
-
- if ( $order_format ) {
- # For each order
- foreach my $infos ( @orders_infos ) {
- my $order_content = $order_format;
- # We replace by value
- while ( $order_content =~ m/<<([^>]*)>>/g ) {
- my $field = $1;
- my $value = $infos->{$field} || "";
- $value = sprintf("%.2f", $value) if $field =~ /price/;
- $order_content =~ s/(<<$field>>)/$value/g;
- }
- $order_content =~ s/<\/{0,1}?order>//g;
- $letter->{content} =~ s/<order>.*<\/order>/$order_content\n$order_format/xms;
- }
- $letter->{content} =~ s/<order>.*<\/order>//xms;
- }
-
- my $innerletter = $letter;
+ my $letter = GetPreparedLetter (
+ module => $type,
+ letter_code => $letter_code,
+ branchcode => $userenv->{branch},
+ tables => {
+ 'branches' => $userenv->{branch},
+ 'aqbooksellers' => $databookseller,
+ },
+ repeat => $dataorders,
+ want_librarian => 1,
+ ) or return;
# ... then send mail
- if ( $orders_infos[0]->{'aqbooksellers.bookselleremail'}
- || $orders_infos[0]->{'aqbooksellers.contemail'} ) {
- my $to = $orders_infos[0]->{'aqbooksellers.bookselleremail'};
- $to .= ", " if $to;
- $to .= $orders_infos[0]->{'aqbooksellers.contemail'} || "";
- my %mail = (
- To => $to,
- From => $userenv->{emailaddress},
- Subject => Encode::encode( "utf8", "" . $innerletter->{title} ),
- Message => Encode::encode( "utf8", "" . $innerletter->{content} ),
- 'Content-Type' => 'text/plain; charset="utf8"',
- );
- sendmail(%mail) or carp $Mail::Sendmail::error;
- warn "sending to $mail{To} From $mail{From} subj $mail{Subject} Mess $mail{Message}";
- if ( C4::Context->preference("LetterLog") ) {
- logaction( "ACQUISITION", "Send Acquisition claim letter", "", "order list : " . join( ",", @$externalid ) . "\n$innerletter->{title}\n$innerletter->{content}" ) if $type eq 'claimacquisition';
- logaction( "ACQUISITION", "CLAIM ISSUE", undef, "To=" . $mail{To} . " Title=" . $innerletter->{title} . " Content=" . $innerletter->{content} ) if $type eq 'claimissues';
- }
- } else {
- die "This bookseller has no email\n";
- }
+ my %mail = (
+ To => join( ',', @email),
+ From => $userenv->{emailaddress},
+ Subject => Encode::encode( "utf8", "" . $letter->{title} ),
+ Message => Encode::encode( "utf8", "" . $letter->{content} ),
+ 'Content-Type' => 'text/plain; charset="utf8"',
+ );
+ sendmail(%mail) or carp $Mail::Sendmail::error;
- warn "sending to From $userenv->{emailaddress} subj $innerletter->{title} Mess $innerletter->{content}";
+ logaction(
+ "ACQUISITION",
+ $type eq 'claimissues' ? "CLAIM ISSUE" : "ACQUISITION CLAIM",
+ undef,
+ "To="
+ . $databookseller->{contemail}
+ . " Title="
+ . $letter->{title}
+ . " Content="
+ . $letter->{content}
+ ) if C4::Context->preference("LetterLog");
}
-
- # send an "account details" notice to a newly created user
+ # send an "account details" notice to a newly created user
elsif ( $type eq 'members' ) {
- # must parse the password special, before it's hashed.
- $letter->{content} =~ s/<<borrowers.password>>/$externalid->{'password'}/g;
-
- parseletter( $letter, 'borrowers', $externalid->{'borrowernumber'});
- parseletter( $letter, 'branches', $externalid->{'branchcode'} );
-
my $branchdetails = GetBranchDetail($externalid->{'branchcode'});
+ my $letter = GetPreparedLetter (
+ module => 'members',
+ letter_code => $letter_code,
+ branchcode => $externalid->{'branchcode'},
+ tables => {
+ 'branches' => $branchdetails,
+ 'borrowers' => $externalid->{'borrowernumber'},
+ },
+ substitute => { 'borrowers.password' => $externalid->{'password'} },
+ want_librarian => 1,
+ ) or return;
+
+ return { error => "no_email" } unless $externalid->{'emailaddr'};
my %mail = (
To => $externalid->{'emailaddr'},
From => $branchdetails->{'branchemail'} || C4::Context->preference("KohaAdminEmailAddress"),
- Subject => $letter->{'title'},
- Message => $letter->{'content'},
+ Subject => Encode::encode( "utf8", $letter->{'title'} ),
+ Message => Encode::encode( "utf8", $letter->{'content'} ),
'Content-Type' => 'text/plain; charset="utf8"',
);
sendmail(%mail) or carp $Mail::Sendmail::error;
}
}
-=head2 parseletter($letter, $table, $pk)
+=head2 GetPreparedLetter( %params )
+
+ retrieves letter template and performs substituion processing
+
+ %params hash:
+ module => letter module, mandatory
+ letter_code => letter code, mandatory
+ branchcode => for letter selection, if missing default system letter taken
+ tables => a hashref with table names as keys. Values are either:
+ - a scalar - primary key value
+ - an arrayref - primary key values
+ - a hashref - full record
+ substitute => custom substitution key/value pairs
+ repeat => records to be substituted on consecutive lines:
+ - an arrayref - tries to guess what needs substituting by
+ taking remaining << >> tokensr; not recommended
+ - a hashref token => @tables - replaces <token> << >> << >> </token>
+ subtemplate for each @tables row; table is a hashref as above
+ want_librarian => boolean, if set to true triggers librarian details
+ substitution from the userenv
+ Return value:
+ letter fields hashref (title & content useful)
- parameters :
- - $letter : a hash to letter fields (title & content useful)
- - $table : the Koha table to parse.
- - $pk : the primary key to query on the $table table
- parse all fields from a table, and replace values in title & content with the appropriate value
- (not exported sub, used only internally)
+=cut
+
+sub GetPreparedLetter {
+ my %params = @_;
+
+ my $module = $params{module} or croak "No module";
+ my $letter_code = $params{letter_code} or croak "No letter_code";
+ my $branchcode = $params{branchcode} || '';
+ my $tables = $params{tables};
+ my $substitute = $params{substitute};
+ my $repeat = $params{repeat};
+
+ my $letter = getletter( $module, $letter_code, $branchcode )
+ or warn( "No $module $letter_code letter"),
+ return;
+
+ my $prepared_letter = GetProcessedLetter(
+ module => $module,
+ letter_code => $letter_code,
+ letter => $letter,
+ branchcode => $branchcode,
+ tables => $tables,
+ substitute => $substitute,
+ repeat => $repeat
+ );
+
+ return $prepared_letter;
+}
+
+=head2 GetProcessedLetter( %params )
+
+ given a letter, with possible pre-processing do standard processing
+ allows one to perform letter template processing beforehand
+
+ %params hash:
+ module => letter module, mandatory
+ letter_code => letter code, mandatory
+ letter => letter, mandatory
+ branchcode => for letter selection, if missing default system letter taken
+ tables => a hashref with table names as keys. Values are either:
+ - a scalar - primary key value
+ - an arrayref - primary key values
+ - a hashref - full record
+ substitute => custom substitution key/value pairs
+ repeat => records to be substituted on consecutive lines:
+ - an arrayref - tries to guess what needs substituting by
+ taking remaining << >> tokensr; not recommended
+ - a hashref token => @tables - replaces <token> << >> << >> </token>
+ subtemplate for each @tables row; table is a hashref as above
+ want_librarian => boolean, if set to true triggers librarian details
+ substitution from the userenv
+ Return value:
+ letter fields hashref (title & content useful)
=cut
-our %handles = ();
-our %columns = ();
+sub GetProcessedLetter {
+ my %params = @_;
+
+ my $module = $params{module} or croak "No module";
+ my $letter_code = $params{letter_code} or croak "No letter_code";
+ my $letter = $params{letter} or croak "No letter";
+ my $branchcode = $params{branchcode} || '';
+ my $tables = $params{tables};
+ my $substitute = $params{substitute};
+ my $repeat = $params{repeat};
+
+ $tables || $substitute || $repeat
+ or carp( "ERROR: nothing to substitute - both 'tables' and 'substitute' are empty" ),
+ return;
+ my $want_librarian = $params{want_librarian};
+
+ if ($substitute) {
+ while ( my ($token, $val) = each %$substitute ) {
+ $letter->{title} =~ s/<<$token>>/$val/g;
+ $letter->{content} =~ s/<<$token>>/$val/g;
+ }
+ }
+
+ if ($want_librarian) {
+ # parsing librarian name
+ my $userenv = C4::Context->userenv;
+ $letter->{content} =~ s/<<LibrarianFirstname>>/$userenv->{firstname}/go;
+ $letter->{content} =~ s/<<LibrarianSurname>>/$userenv->{surname}/go;
+ $letter->{content} =~ s/<<LibrarianEmailaddress>>/$userenv->{emailaddress}/go;
+ }
+
+ my ($repeat_no_enclosing_tags, $repeat_enclosing_tags);
+
+ if ($repeat) {
+ if (ref ($repeat) eq 'ARRAY' ) {
+ $repeat_no_enclosing_tags = $repeat;
+ } else {
+ $repeat_enclosing_tags = $repeat;
+ }
+ }
+
+ if ($repeat_enclosing_tags) {
+ while ( my ($tag, $tag_tables) = each %$repeat_enclosing_tags ) {
+ if ( $letter->{content} =~ m!<$tag>(.*)</$tag>!s ) {
+ my $subcontent = $1;
+ my @lines = map {
+ my %subletter = ( title => '', content => $subcontent );
+ _substitute_tables( \%subletter, $_ );
+ $subletter{content};
+ } @$tag_tables;
+ $letter->{content} =~ s!<$tag>.*</$tag>!join( "\n", @lines )!se;
+ }
+ }
+ }
+
+ if ($tables) {
+ _substitute_tables( $letter, $tables );
+ }
+
+ if ($repeat_no_enclosing_tags) {
+ if ( $letter->{content} =~ m/[^\n]*<<.*>>[^\n]*/so ) {
+ my $line = $&;
+ my $i = 1;
+ my @lines = map {
+ my $c = $line;
+ $c =~ s/<<count>>/$i/go;
+ foreach my $field ( keys %{$_} ) {
+ $c =~ s/(<<[^\.]+.$field>>)/$_->{$field}/;
+ }
+ $i++;
+ $c;
+ } @$repeat_no_enclosing_tags;
+
+ my $replaceby = join( "\n", @lines );
+ $letter->{content} =~ s/\Q$line\E/$replaceby/s;
+ }
+ }
+
+ $letter->{content} =~ s/<<\S*>>//go; #remove any stragglers
+# $letter->{content} =~ s/<<[^>]*>>//go;
+
+ return $letter;
+}
+
+sub _substitute_tables {
+ my ( $letter, $tables ) = @_;
+ while ( my ($table, $param) = each %$tables ) {
+ next unless $param;
+
+ my $ref = ref $param;
+
+ my $values;
+ if ($ref && $ref eq 'HASH') {
+ $values = $param;
+ }
+ else {
+ my @pk;
+ my $sth = _parseletter_sth($table);
+ unless ($sth) {
+ warn "_parseletter_sth('$table') failed to return a valid sth. No substitution will be done for that table.";
+ return;
+ }
+ $sth->execute( $ref ? @$param : $param );
+
+ $values = $sth->fetchrow_hashref;
+ }
+
+ _parseletter ( $letter, $table, $values );
+ }
+}
-sub parseletter_sth {
+my %handles = ();
+sub _parseletter_sth {
my $table = shift;
unless ($table) {
- carp "ERROR: parseletter_sth() called without argument (table)";
+ carp "ERROR: _parseletter_sth() called without argument (table)";
return;
}
# check cache first
(defined $handles{$table}) and return $handles{$table};
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 = ?" : undef ;
+ ($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 = ?" :
+ undef ;
unless ($query) {
- warn "ERROR: No parseletter_sth query for table '$table'";
+ warn "ERROR: No _parseletter_sth query for table '$table'";
return; # nothing to get
}
unless ($handles{$table} = C4::Context->dbh->prepare($query)) {
return $handles{$table}; # now cache is populated for that $table
}
-sub parseletter {
- my ( $letter, $table, $pk, $pk2 ) = @_;
- unless ($letter) {
- carp "ERROR: parseletter() 1st argument 'letter' empty";
- return;
- }
- my $sth = parseletter_sth($table);
- unless ($sth) {
- warn "parseletter_sth('$table') failed to return a valid sth. No substitution will be done for that table.";
- return;
- }
- if ( $pk2 ) {
- $sth->execute($pk, $pk2);
- } else {
- $sth->execute($pk);
- }
+=head2 _parseletter($letter, $table, $values)
+
+ parameters :
+ - $letter : a hash to letter fields (title & content useful)
+ - $table : the Koha table to parse.
+ - $values : table record hashref
+ parse all fields from a table, and replace values in title & content with the appropriate value
+ (not exported sub, used only internally)
+
+=cut
+
+my %columns = ();
+sub _parseletter {
+ my ( $letter, $table, $values ) = @_;
- my $values = $sth->fetchrow_hashref;
-
# TEMPORARY hack until the expirationdate column is added to reserves
if ( $table eq 'reserves' && $values->{'waitingdate'} ) {
my @waitingdate = split /-/, $values->{'waitingdate'};
)->output();
}
+ if ($letter->{content} && $letter->{content} =~ /<<today>>/) {
+ my @da = localtime();
+ my $todaysdate = "$da[2]:$da[1] " . C4::Dates->today();
+ $letter->{content} =~ s/<<today>>/$todaysdate/go;
+ }
# and get all fields from the table
- my $columns = C4::Context->dbh->prepare("SHOW COLUMNS FROM $table");
- $columns->execute;
- while ( ( my $field ) = $columns->fetchrow_array ) {
- my $replacefield = "<<$table.$field>>";
- $values->{$field} =~ s/\p{P}(?=$)//g if $values->{$field};
- my $replacedby = $values->{$field} || '';
- ($letter->{title} ) and $letter->{title} =~ s/$replacefield/$replacedby/g;
- ($letter->{content}) and $letter->{content} =~ s/$replacefield/$replacedby/g;
+# 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;
+ my $replacedby = defined ($val) ? $val : '';
+ ($letter->{title} ) and do {
+ $letter->{title} =~ s/$replacetablefield/$replacedby/g;
+ $letter->{title} =~ s/$replacefield/$replacedby/g;
+ };
+ ($letter->{content}) and do {
+ $letter->{content} =~ s/$replacetablefield/$replacedby/g;
+ $letter->{content} =~ s/$replacefield/$replacedby/g;
+ };
+ }
+
+ if ($table eq 'borrowers' && $letter->{content}) {
+ if ( my $attributes = GetBorrowerAttributes($values->{borrowernumber}) ) {
+ my %attr;
+ foreach (@$attributes) {
+ my $code = $_->{code};
+ my $val = $_->{value_description} || $_->{value};
+ $val =~ s/\p{P}(?=$)//g if $val;
+ next unless $val gt '';
+ $attr{$code} ||= [];
+ push @{ $attr{$code} }, $val;
+ }
+ while ( my ($code, $val_ar) = each %attr ) {
+ my $replacefield = "<<borrower-attribute:$code>>";
+ my $replacedby = join ',', @$val_ar;
+ $letter->{content} =~ s/$replacefield/$replacedby/g;
+ }
+ }
}
return $letter;
}
eventually get processed (sent) by the process_message_queue.pl
cronjob when it calls SendQueuedMessages.
-return true on success
+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->{'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;
+ }
+
+ # It was found that the some utf8 codes, cause the text to be truncated from that point onward when stored,
+ # so we normalize utf8 with NFC so that mysql will store 'all' of the content in its TEXT column type
+ # Note: It is also done in _add_attachments accordingly.
+ $params->{'letter'}->{'title'} = NFC($params->{'letter'}->{'title'}); # subject
+ $params->{'letter'}->{'content'} = NFC($params->{'letter'}->{'content'});
+
# If we have any attachments we should encode then into the body.
if ( $params->{'attachments'} ) {
$params->{'letter'} = _add_attachments(
$params->{'from_address'}, # from_address
$params->{'letter'}->{'content-type'}, # content_type
);
- return $result;
+ return $dbh->last_insert_id(undef,undef,'message_queue', undef);
}
=head2 SendQueuedMessages ([$hashref])
=cut
-sub SendQueuedMessages (;$) {
+sub SendQueuedMessages {
my $params = shift;
my $unsent_messages = _get_unsent_messages();
my $params = shift || {};
return _get_unsent_messages( { message_transport_type => 'print',
- borrowernumber => $params->{'borrowernumber'}, } );
+ borrowernumber => $params->{'borrowernumber'},
+ } );
}
=head2 GetQueuedMessages ([$hashref])
sub _add_attachments {
my $params = shift;
- return unless 'HASH' eq ref $params;
- foreach my $required_parameter (qw( letter attachments message )) {
- return unless exists $params->{$required_parameter};
- }
- return $params->{'letter'} unless @{ $params->{'attachments'} };
+ my $letter = $params->{'letter'};
+ my $attachments = $params->{'attachments'};
+ return $letter unless @$attachments;
+ my $message = $params->{'message'};
# First, we have to put the body in as the first attachment
- $params->{'message'}->attach(
- Type => 'TEXT',
- Data => $params->{'letter'}->{'content'},
+ $message->attach(
+ Type => $letter->{'content-type'} || 'TEXT',
+ Data => $letter->{'is_html'}
+ ? _wrap_html($letter->{'content'}, NFC($letter->{'title'}))
+ : NFC($letter->{'content'}),
);
- foreach my $attachment ( @{ $params->{'attachments'} } ) {
- $params->{'message'}->attach(
+ foreach my $attachment ( @$attachments ) {
+
+ if ($attachment->{'content'} =~ m/text/o) { # NFC normailze any "text" related content-type attachments
+ $attachment->{'content'} = NFC($attachment->{'content'});
+ }
+ $attachment->{'filename'} = NFC($attachment->{'filename'});
+
+ $message->attach(
Type => $attachment->{'type'},
Data => $attachment->{'content'},
Filename => $attachment->{'filename'},
);
}
# we're forcing list context here to get the header, not the count back from grep.
- ( $params->{'letter'}->{'content-type'} ) = grep( /^Content-Type:/, split( /\n/, $params->{'message'}->header_as_string ) );
- $params->{'letter'}->{'content-type'} =~ s/^Content-Type:\s+//;
- $params->{'letter'}->{'content'} = $params->{'message'}->body_as_string;
+ ( $letter->{'content-type'} ) = grep( /^Content-Type:/, split( /\n/, $params->{'message'}->header_as_string ) );
+ $letter->{'content-type'} =~ s/^Content-Type:\s+//;
+ $letter->{'content'} = $message->body_as_string;
- return $params->{'letter'};
+ return $letter;
}
-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
+ FROM message_queue mq
+ LEFT JOIN borrowers b ON b.borrowernumber = mq.borrowernumber
WHERE status = ?
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 );
return $sth->fetchall_arrayref({});
}
-sub _send_message_by_email ($;$$$) {
+sub _send_message_by_email {
my $message = shift or return;
my ($username, $password, $method) = @_;
my $utf8 = decode('MIME-Header', $message->{'subject'} );
$message->{subject}= encode('MIME-Header', $utf8);
+ my $subject = encode('utf8', $message->{'subject'});
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 %sendmail_params = (
To => $to_address,
From => $message->{'from_address'} || C4::Context->preference('KohaAdminEmailAddress'),
- Subject => encode('utf8', $message->{'subject'}),
+ Subject => $subject,
charset => 'utf8',
- Message => $content,
- 'content-type' => $message->{'content_type'} || 'text/plain; charset="UTF-8"',
+ Message => $is_html ? _wrap_html($content, $subject) : $content,
+ 'content-type' => $content_type,
);
$sendmail_params{'Auth'} = {user => $username, pass => $password, method => $method} if $username;
if ( my $bcc = C4::Context->preference('OverdueNoticeBcc') ) {
}
}
-sub _send_message_by_sms ($) {
- my $message = shift or return undef;
+sub _wrap_html {
+ my ($content, $title) = @_;
+
+ my $css = C4::Context->preference("NoticeCSS") || '';
+ $css = qq{<link rel="stylesheet" type="text/css" href="$css">} if $css;
+ return <<EOS;
+<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
+ "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
+<html lang="en" xml:lang="en" xmlns="http://www.w3.org/1999/xhtml">
+<head>
+<title>$title</title>
+<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
+$css
+</head>
+<body>
+$content
+</body>
+</html>
+EOS
+}
+
+sub _send_message_by_sms {
+ my $message = shift or return;
my $member = C4::Members::GetMember( 'borrowernumber' => $message->{'borrowernumber'} );
return unless $member->{'smsalertnumber'};
$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();