Bug 6755 Web browser default lang != syspref selected langs

[koha.git] / C4 / Letters.pm

diff --git a/C4/Letters.pm b/C4/Letters.pm
index 3bcc514..6846a00 100644 (file)
--- a/C4/Letters.pm
+++ b/C4/Letters.pm
@@ -13,17 +13,26 @@ package C4::Letters;
 # WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
 # A PARTICULAR PURPOSE.  See the GNU General Public License for more details.
 #
 # WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
 # A PARTICULAR PURPOSE.  See the GNU General Public License for more details.
 #

-# You should have received a copy of the GNU General Public License along with
-# Koha; if not, write to the Free Software Foundation, Inc., 59 Temple Place,
-# Suite 330, Boston, MA  02111-1307 USA
+# You should have received a copy of the GNU General Public License along
+# with Koha; if not, write to the Free Software Foundation, Inc.,
+# 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.

 
 use strict;
 
 use strict;

+use warnings;
+

 use MIME::Lite;
 use Mail::Sendmail;
 use MIME::Lite;
 use Mail::Sendmail;

+use Encode;
+use Carp;
+

 use C4::Members;
 use C4::Members;

+use C4::Branch;

 use C4::Log;
 use C4::SMS;
 use C4::Log;
 use C4::SMS;

+use C4::Debug;
+use Date::Calc qw( Add_Delta_Days );

 use Encode;
 use Encode;

+use Carp;

 
 use vars qw($VERSION @ISA @EXPORT @EXPORT_OK %EXPORT_TAGS);
 
 
 use vars qw($VERSION @ISA @EXPORT @EXPORT_OK %EXPORT_TAGS);
 


@@ -33,7 +42,7 @@ BEGIN {
        $VERSION = 3.01;
        @ISA = qw(Exporter);
        @EXPORT = qw(
        $VERSION = 3.01;
        @ISA = qw(Exporter);
        @EXPORT = qw(

-       &GetLetters &getletter &addalert &getalert &delalert &findrelatedto &SendAlerts
+       &GetLetters &getletter &addalert &getalert &delalert &findrelatedto &SendAlerts GetPrintMessages

        );
 }
 
        );
 }
 


@@ -52,11 +61,9 @@ C4::Letters - Give functions for Letters management
 
   Letters are managed through "alerts" sent by Koha on some events. All "alert" related functions are in this module too.
 
 
   Letters are managed through "alerts" sent by Koha on some events. All "alert" related functions are in this module too.
 

-=cut
-
-=head2 GetLetters
+=head2 GetLetters([$category])

 
 

-  $letters = &getletters($category);
+  $letters = &GetLetters($category);

   returns informations about letters.
   if needed, $category filters for letters given category
   Create a letter selector with the following code
   returns informations about letters.
   if needed, $category filters for letters given category
   Create a letter selector with the following code


@@ -74,33 +81,33 @@ foreach my $thisletter (keys %$letters) {
     );
     push @letterloop, \%row;
 }
     );
     push @letterloop, \%row;
 }

+$template->param(LETTERLOOP => \@letterloop);

 
 =head3 in TEMPLATE
 
     <select name="letter">
         <option value="">Default</option>
 
 =head3 in TEMPLATE
 
     <select name="letter">
         <option value="">Default</option>

-    <!-- TMPL_LOOP name="letterloop" -->
+    <!-- TMPL_LOOP name="LETTERLOOP" -->

         <option value="<!-- TMPL_VAR name="value" -->" <!-- TMPL_IF name="selected" -->selected<!-- /TMPL_IF -->><!-- TMPL_VAR name="lettername" --></option>
     <!-- /TMPL_LOOP -->
     </select>
 
 =cut
 
         <option value="<!-- TMPL_VAR name="value" -->" <!-- TMPL_IF name="selected" -->selected<!-- /TMPL_IF -->><!-- TMPL_VAR name="lettername" --></option>
     <!-- /TMPL_LOOP -->
     </select>
 
 =cut
 

-sub GetLetters {
+sub GetLetters (;$) {

 
     # returns a reference to a hash of references to ALL letters...
     my $cat = shift;
     my %letters;
     my $dbh = C4::Context->dbh;
 
     # returns a reference to a hash of references to ALL letters...
     my $cat = shift;
     my %letters;
     my $dbh = C4::Context->dbh;

-    $dbh->quote($cat);

     my $sth;
     my $sth;

-    if ( $cat ne "" ) {
+    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 WHERE module = ? ORDER BY name";
         $sth = $dbh->prepare($query);
         $sth->execute($cat);
     }
     else {

-        my $query = " SELECT * FROM letter ORDER BY name";
+        my $query = "SELECT * FROM letter ORDER BY name";

         $sth = $dbh->prepare($query);
         $sth->execute;
     }
         $sth = $dbh->prepare($query);
         $sth->execute;
     }


@@ -110,7 +117,7 @@ sub GetLetters {
     return \%letters;
 }
 
     return \%letters;
 }
 

-sub getletter {
+sub getletter ($$) {

     my ( $module, $code ) = @_;
     my $dbh = C4::Context->dbh;
     my $sth = $dbh->prepare("select * from letter where module=? and code=?");
     my ( $module, $code ) = @_;
     my $dbh = C4::Context->dbh;
     my $sth = $dbh->prepare("select * from letter where module=? and code=?");


@@ -119,18 +126,18 @@ sub getletter {
     return $line;
 }
 
     return $line;
 }
 

-=head2 addalert
+=head2 addalert ($borrowernumber, $type, $externalid)

 
     parameters : 
     - $borrowernumber : the number of the borrower subscribing to the alert
     - $type : the type of alert.
 
     parameters : 
     - $borrowernumber : the number of the borrower subscribing to the alert
     - $type : the type of alert.

-    - externalid : the primary key of the object to put alert on. For issues, the alert is made on subscriptionid.
+    - $externalid : the primary key of the object to put alert on. For issues, the alert is made on subscriptionid.

     
     create an alert and return the alertid (primary key)
 
 =cut
 
     
     create an alert and return the alertid (primary key)
 
 =cut
 

-sub addalert {
+sub addalert ($$$) {

     my ( $borrowernumber, $type, $externalid ) = @_;
     my $dbh = C4::Context->dbh;
     my $sth =
     my ( $borrowernumber, $type, $externalid ) = @_;
     my $dbh = C4::Context->dbh;
     my $sth =


@@ -143,39 +150,37 @@ sub addalert {
     return $alertid;
 }
 
     return $alertid;
 }
 

-=head2 delalert
+=head2 delalert ($alertid)

 
     parameters :
     - alertid : the alert id
     deletes the alert
 
     parameters :
     - alertid : the alert id
     deletes the alert

-    
-=cut

 
 

-sub delalert {
-    my ($alertid) = @_;
+=cut

 
 

-    #warn "ALERTID : $alertid";
-    my $dbh = C4::Context->dbh;
-    my $sth = $dbh->prepare("delete from alert where alertid=?");
+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=?");

     $sth->execute($alertid);
 }
 
     $sth->execute($alertid);
 }
 

-=head2 getalert
+=head2 getalert ([$borrowernumber], [$type], [$externalid])

 
     parameters :
     - $borrowernumber : the number of the borrower subscribing to the alert
     - $type : the type of alert.
 
     parameters :
     - $borrowernumber : the number of the borrower subscribing to the alert
     - $type : the type of alert.

-    - externalid : the primary key of the object to put alert on. For issues, the alert is made on subscriptionid.
+    - $externalid : the primary key of the object to put alert on. For issues, the alert is made on subscriptionid.

     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.
 
 =cut
 
     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.
 
 =cut
 

-sub getalert {
+sub getalert (;$$$) {

     my ( $borrowernumber, $type, $externalid ) = @_;
     my $dbh   = C4::Context->dbh;
     my $query = "SELECT * FROM alert WHERE";
     my @bind;
     my ( $borrowernumber, $type, $externalid ) = @_;
     my $dbh   = C4::Context->dbh;
     my $query = "SELECT * FROM alert WHERE";
     my @bind;

-    if ($borrowernumber =~ /^\d+$/) {
+    if ($borrowernumber and $borrowernumber =~ /^\d+$/) {

         $query .= " borrowernumber=? AND ";
         push @bind, $borrowernumber;
     }
         $query .= " borrowernumber=? AND ";
         push @bind, $borrowernumber;
     }


@@ -190,14 +195,10 @@ sub getalert {
     $query =~ s/ AND $//;
     my $sth = $dbh->prepare($query);
     $sth->execute(@bind);
     $query =~ s/ AND $//;
     my $sth = $dbh->prepare($query);
     $sth->execute(@bind);

-    my @result;
-    while ( my $line = $sth->fetchrow_hashref ) {
-        push @result, $line;
-    }
-    return \@result;
+    return $sth->fetchall_arrayref({});

 }
 
 }
 

-=head2 findrelatedto
+=head2 findrelatedto($type, $externalid)

 
        parameters :
        - $type : the type of alert
 
        parameters :
        - $type : the type of alert


@@ -205,26 +206,24 @@ sub getalert {
        
        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.
        When type=issue, the id is related to a subscriptionid and this sub returns the name of the biblio.
        
        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.
        When type=issue, the id is related to a subscriptionid and this sub returns the name of the biblio.

-       When type=virtual, the id is related to a virtual shelf and this sub returns the name of the sub

 
 =cut
 
 =cut

-
-sub findrelatedto {
-    my ( $type, $externalid ) = @_;
-    my $dbh = C4::Context->dbh;
-    my $sth;
-    if ( $type eq 'issue' ) {
-        $sth =
-          $dbh->prepare(
-"select title as result from subscription left join biblio on subscription.biblionumber=biblio.biblionumber where subscriptionid=?"
-          );
-    }
-    if ( $type eq 'borrower' ) {
-        $sth =
-          $dbh->prepare(
-"select concat(firstname,' ',surname) from borrowers where borrowernumber=?"
-          );
+    
+# 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;
+    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;

     }
     }

+    my $sth = C4::Context->dbh->prepare($q);

     $sth->execute($externalid);
     my ($result) = $sth->fetchrow;
     return $result;
     $sth->execute($externalid);
     my ($result) = $sth->fetchrow;
     return $result;


@@ -277,7 +276,7 @@ sub SendAlerts {
 
             # and parse borrower ...
             my $innerletter = $letter;
 
             # and parse borrower ...
             my $innerletter = $letter;

-            my $borinfo = GetMember( $_->{'borrowernumber'}, 'borrowernumber' );
+            my $borinfo = C4::Members::GetMember('borrowernumber' => $_->{'borrowernumber'});

             parseletter( $innerletter, 'borrowers', $_->{'borrowernumber'} );
 
             # ... then send mail
             parseletter( $innerletter, 'borrowers', $_->{'borrowernumber'} );
 
             # ... then send mail


@@ -289,7 +288,7 @@ sub SendAlerts {
                     Message => "" . $innerletter->{content},
                     'Content-Type' => 'text/plain; charset="utf8"',
                     );
                     Message => "" . $innerletter->{content},
                     'Content-Type' => 'text/plain; charset="utf8"',
                     );

-                sendmail(%mail);
+                sendmail(%mail) or carp $Mail::Sendmail::error;

 
 # warn "sending to $mail{To} From $mail{From} subj $mail{Subject} Mess $mail{Message}";
             }
 
 # warn "sending to $mail{To} From $mail{From} subj $mail{Subject} Mess $mail{Message}";
             }


@@ -324,12 +323,14 @@ sub SendAlerts {
         $letter->{content} =~ s/<<LibrarianSurname>>/$userenv->{surname}/g;
         $letter->{content} =~
           s/<<LibrarianEmailaddress>>/$userenv->{emailaddress}/g;
         $letter->{content} =~ s/<<LibrarianSurname>>/$userenv->{surname}/g;
         $letter->{content} =~
           s/<<LibrarianEmailaddress>>/$userenv->{emailaddress}/g;

-        foreach my $data (@$dataorders) {
-            my $line = $1 if ( $letter->{content} =~ m/(<<.*>>)/ );
-            foreach my $field ( keys %$data ) {
-                $line =~ s/(<<[^\.]+.$field>>)/$data->{$field}/;
+        foreach my $data ( @{$dataorders} ) {
+            if ( $letter->{content} =~ m/(<<.*>>)/ ) {
+                my $line = $1;
+                foreach my $field ( keys %{$data} ) {
+                    $line =~ s/(<<[^\.]+.$field>>)/$data->{$field}/;
+                }
+                $letter->{content} =~ s/(<<.*>>)/$line\n$1/;

             }
             }

-            $letter->{content} =~ s/(<<.*>>)/$line\n$1/;

         }
         $letter->{content} =~ s/<<[^>]*>>//g;
         my $innerletter = $letter;
         }
         $letter->{content} =~ s/<<[^>]*>>//g;
         my $innerletter = $letter;


@@ -350,9 +351,7 @@ sub SendAlerts {
                 Message        => "" . $innerletter->{content},
                 'Content-Type' => 'text/plain; charset="utf8"',
             );
                 Message        => "" . $innerletter->{content},
                 'Content-Type' => 'text/plain; charset="utf8"',
             );

-            sendmail(%mail);
-            warn
-"sending to $mail{To} From $mail{From} subj $mail{Subject} Mess $mail{Message}";
+            sendmail(%mail) or carp $Mail::Sendmail::error;

         }
         if ( C4::Context->preference("LetterLog") ) {
             logaction(
         }
         if ( C4::Context->preference("LetterLog") ) {
             logaction(


@@ -394,33 +393,43 @@ sub SendAlerts {
         $letter->{content} =~ s/<<LibrarianSurname>>/$userenv->{surname}/g;
         $letter->{content} =~
           s/<<LibrarianEmailaddress>>/$userenv->{emailaddress}/g;
         $letter->{content} =~ s/<<LibrarianSurname>>/$userenv->{surname}/g;
         $letter->{content} =~
           s/<<LibrarianEmailaddress>>/$userenv->{emailaddress}/g;

-        foreach my $data (@$dataorders) {
-            my $line = $1 if ( $letter->{content} =~ m/(<<.*>>)/ );
-            foreach my $field ( keys %$data ) {
-                $line =~ s/(<<[^\.]+.$field>>)/$data->{$field}/;
+        foreach my $data ( @{$dataorders} ) {
+            if ( $letter->{content} =~ m/(<<.*>>)/ ) {
+                my $line = $1;
+                foreach my $field ( keys %{$data} ) {
+                    $line =~ s/(<<[^\.]+.$field>>)/$data->{$field}/;
+                }
+                $letter->{content} =~ s/(<<.*>>)/$line\n$1/;

             }
             }

-            $letter->{content} =~ s/(<<.*>>)/$line\n$1/;

         }
         $letter->{content} =~ s/<<[^>]*>>//g;
         my $innerletter = $letter;
 
         # ... then send mail
         if (   $databookseller->{bookselleremail}
         }
         $letter->{content} =~ s/<<[^>]*>>//g;
         my $innerletter = $letter;
 
         # ... then send mail
         if (   $databookseller->{bookselleremail}

-            || $databookseller->{contemail} )
-        {
+            || $databookseller->{contemail} ) {
+            my $mail_to = $databookseller->{bookselleremail};
+            if ($databookseller->{contemail}) {
+                if (!$mail_to) {
+                    $mail_to = $databookseller->{contemail};
+                } else {
+                    $mail_to .= q|,|;
+                    $mail_to .= $databookseller->{contemail};
+                }
+            }
+            my $mail_subj = $innerletter->{title};
+            my $mail_msg  = $innerletter->{content};
+            $mail_msg  ||= q{};
+            $mail_subj ||= q{};
+

             my %mail = (
             my %mail = (

-                To => $databookseller->{bookselleremail}
-                  . (
-                    $databookseller->{contemail}
-                    ? "," . $databookseller->{contemail}
-                    : ""
-                  ),
+                To => $mail_to,

                 From    => $userenv->{emailaddress},
                 From    => $userenv->{emailaddress},

-                Subject => "" . $innerletter->{title},
-                Message => "" . $innerletter->{content},
+                Subject => $mail_subj,
+                Message => $mail_msg,

                 'Content-Type' => 'text/plain; charset="utf8"',
             );
                 'Content-Type' => 'text/plain; charset="utf8"',
             );

-            sendmail(%mail);
+            sendmail(%mail) or carp $Mail::Sendmail::error;

             logaction(
                 "ACQUISITION",
                 "CLAIM ISSUE",
             logaction(
                 "ACQUISITION",
                 "CLAIM ISSUE",


@@ -433,29 +442,28 @@ sub SendAlerts {
                   . $innerletter->{content}
             ) if C4::Context->preference("LetterLog");
         }
                   . $innerletter->{content}
             ) if C4::Context->preference("LetterLog");
         }

-        warn
-"sending to From $userenv->{emailaddress} subj $innerletter->{title} Mess $innerletter->{content}";

     }    
    # send an "account details" notice to a newly created user 
     elsif ( $type eq 'members' ) {
     }    
    # send an "account details" notice to a newly created user 
     elsif ( $type eq 'members' ) {

-        $letter->{content} =~ s/<<borrowers.title>>/$externalid->{'title'}/g;
-        $letter->{content} =~ s/<<borrowers.firstname>>/$externalid->{'firstname'}/g;
-        $letter->{content} =~ s/<<borrowers.surname>>/$externalid->{'surname'}/g;
-        $letter->{content} =~ s/<<borrowers.userid>>/$externalid->{'userid'}/g;
+        # must parse the password special, before it's hashed.

         $letter->{content} =~ s/<<borrowers.password>>/$externalid->{'password'}/g;
 
         $letter->{content} =~ s/<<borrowers.password>>/$externalid->{'password'}/g;
 

+        parseletter( $letter, 'borrowers', $externalid->{'borrowernumber'});
+        parseletter( $letter, 'branches', $externalid->{'branchcode'} );
+
+        my $branchdetails = GetBranchDetail($externalid->{'branchcode'});

         my %mail = (
                 To      =>     $externalid->{'emailaddr'},
         my %mail = (
                 To      =>     $externalid->{'emailaddr'},

-                From    =>  C4::Context->preference("KohaAdminEmailAddress"),
+                From    =>  $branchdetails->{'branchemail'} || C4::Context->preference("KohaAdminEmailAddress"),

                 Subject => $letter->{'title'}, 
                 Message => $letter->{'content'},
                 'Content-Type' => 'text/plain; charset="utf8"',
         );
                 Subject => $letter->{'title'}, 
                 Message => $letter->{'content'},
                 'Content-Type' => 'text/plain; charset="utf8"',
         );

-        sendmail(%mail);
+        sendmail(%mail) or carp $Mail::Sendmail::error;

     }
 }
 
     }
 }
 

-=head2 parseletter
+=head2 parseletter($letter, $table, $pk)

 
     parameters :
     - $letter : a hash to letter fields (title & content useful)
 
     parameters :
     - $letter : a hash to letter fields (title & content useful)


@@ -466,53 +474,89 @@ sub SendAlerts {
 
 =cut
 
 
 =cut
 

-sub parseletter {
-    my ( $letter, $table, $pk, $pk2 ) = @_;
+our %handles = ();
+our %columns = ();

 
 

-    #  warn "Parseletter : ($letter,$table,$pk)";
-    my $dbh = C4::Context->dbh;
-    my $sth;
-    if ( $table eq 'biblio' ) {
-        $sth = $dbh->prepare("select * from biblio where biblionumber=?");
-    } elsif ( $table eq 'biblioitems' ) {
-        $sth = $dbh->prepare("select * from biblioitems where biblionumber=?");
-    } elsif ( $table eq 'items' ) {
-        $sth = $dbh->prepare("select * from items where itemnumber=?");
-    } elsif ( $table eq 'reserves' ) {
-        $sth = $dbh->prepare("select * from reserves where borrowernumber = ? and biblionumber=?");
-    } elsif ( $table eq 'borrowers' ) {
-        $sth = $dbh->prepare("select * from borrowers where borrowernumber=?");
-    } elsif ( $table eq 'branches' ) {
-        $sth = $dbh->prepare("select * from branches where branchcode=?");
-    } elsif ( $table eq 'aqbooksellers' ) {
-        $sth = $dbh->prepare("select * from aqbooksellers where id=?");
+sub parseletter_sth {
+    my $table = shift;
+    unless ($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 'suggestions'  ) ? "SELECT * FROM $table WHERE borrowernumber = ? and biblionumber = ?" :
+    ($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 ;
+    unless ($query) {
+        warn "ERROR: No parseletter_sth query for table '$table'";
+        return;     # nothing to get

     }
     }

+    unless ($handles{$table} = C4::Context->dbh->prepare($query)) {
+        warn "ERROR: Failed to prepare query: '$query'";
+        return;
+    }
+    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);
     }
 
     if ( $pk2 ) {
         $sth->execute($pk, $pk2);
     } else {
         $sth->execute($pk);
     }
 

-    # store the result in an hash

     my $values = $sth->fetchrow_hashref;
     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'};
+
+        $values->{'expirationdate'} = C4::Dates->new(
+            sprintf(
+                '%04d-%02d-%02d',
+                Add_Delta_Days( @waitingdate, C4::Context->preference( 'ReservesMaxPickUpDelay' ) )
+            ),
+            'iso'
+        )->output();
+    }
+

 
     # and get all fields from the table
 
     # and get all fields from the table

-    $sth = $dbh->prepare("show columns from $table");
-    $sth->execute;
-    while ( ( my $field ) = $sth->fetchrow_array ) {
+    my $columns = C4::Context->dbh->prepare("SHOW COLUMNS FROM $table");
+    $columns->execute;
+    while ( ( my $field ) = $columns->fetchrow_array ) {

         my $replacefield = "<<$table.$field>>";
         my $replacefield = "<<$table.$field>>";

+        $values->{$field} =~ s/\p{P}(?=$)//g if $values->{$field};

         my $replacedby   = $values->{$field} || '';
         my $replacedby   = $values->{$field} || '';

-        $letter->{title}   =~ s/$replacefield/$replacedby/g;
-        $letter->{content} =~ s/$replacefield/$replacedby/g;
+        ($letter->{title}  ) and $letter->{title}   =~ s/$replacefield/$replacedby/g;
+        ($letter->{content}) and $letter->{content} =~ s/$replacefield/$replacedby/g;

     }
     }

+    return $letter;

 }
 
 =head2 EnqueueLetter
 
 }
 
 =head2 EnqueueLetter
 

-=over 4
-
-my $success = EnqueueLetter( { letter => $letter, borrowernumber => '12', message_transport_type => 'email' } )
+  my $success = EnqueueLetter( { letter => $letter, 
+        borrowernumber => '12', message_transport_type => 'email' } )

 
 places a letter in the message_queue database table, which will
 eventually get processed (sent) by the process_message_queue.pl
 
 places a letter in the message_queue database table, which will
 eventually get processed (sent) by the process_message_queue.pl


@@ -520,12 +564,10 @@ cronjob when it calls SendQueuedMessages.
 
 return true on success
 
 
 return true on success
 

-=back
-

 =cut
 
 =cut
 

-sub EnqueueLetter {
-    my $params = shift;
+sub EnqueueLetter ($) {
+    my $params = shift or return undef;

 
     return unless exists $params->{'letter'};
     return unless exists $params->{'borrowernumber'};
 
     return unless exists $params->{'letter'};
     return unless exists $params->{'borrowernumber'};


@@ -544,9 +586,9 @@ sub EnqueueLetter {
     my $dbh       = C4::Context->dbh();
     my $statement = << 'ENDSQL';
 INSERT INTO message_queue
     my $dbh       = C4::Context->dbh();
     my $statement = << 'ENDSQL';
 INSERT INTO message_queue

-( borrowernumber, subject, content, message_transport_type, status, time_queued, to_address, from_address, content_type )
+( borrowernumber, subject, content, metadata, letter_code, message_transport_type, status, time_queued, to_address, from_address, content_type )

 VALUES
 VALUES

-( ?,              ?,       ?,       ?,                      ?,      NOW(),       ?,          ?,            ? )
+( ?,              ?,       ?,       ?,        ?,           ?,                      ?,      NOW(),       ?,          ?,            ? )

 ENDSQL
 
     my $sth    = $dbh->prepare($statement);
 ENDSQL
 
     my $sth    = $dbh->prepare($statement);


@@ -554,6 +596,8 @@ ENDSQL
         $params->{'borrowernumber'},              # borrowernumber
         $params->{'letter'}->{'title'},           # subject
         $params->{'letter'}->{'content'},         # content
         $params->{'borrowernumber'},              # borrowernumber
         $params->{'letter'}->{'title'},           # subject
         $params->{'letter'}->{'content'},         # content

+        $params->{'letter'}->{'metadata'} || '',  # metadata
+        $params->{'letter'}->{'code'}     || '',  # letter_code

         $params->{'message_transport_type'},      # message_transport_type
         'pending',                                # status
         $params->{'to_address'},                  # to_address
         $params->{'message_transport_type'},      # message_transport_type
         'pending',                                # status
         $params->{'to_address'},                  # to_address


@@ -563,23 +607,17 @@ ENDSQL
     return $result;
 }
 
     return $result;
 }
 

-=head2 SendQueuedMessages
+=head2 SendQueuedMessages ([$hashref]) 

 
 

-=over 4
-
-SendQueuedMessages()
+  my $sent = SendQueuedMessages( { verbose => 1 } );

 
 sends all of the 'pending' items in the message queue.
 
 
 sends all of the 'pending' items in the message queue.
 

-my $sent = SendQueuedMessages( { verbose => 1 } )
-

 returns number of messages sent.
 
 returns number of messages sent.
 

-=back
-

 =cut
 
 =cut
 

-sub SendQueuedMessages {
+sub SendQueuedMessages (;$) {

     my $params = shift;
 
     my $unsent_messages = _get_unsent_messages();
     my $params = shift;
 
     my $unsent_messages = _get_unsent_messages();


@@ -588,13 +626,13 @@ sub SendQueuedMessages {
         warn sprintf( 'sending %s message to patron: %s',
                       $message->{'message_transport_type'},
                       $message->{'borrowernumber'} || 'Admin' )
         warn sprintf( 'sending %s message to patron: %s',
                       $message->{'message_transport_type'},
                       $message->{'borrowernumber'} || 'Admin' )

-          if $params->{'verbose'};
+          if $params->{'verbose'} or $debug;

         # This is just begging for subclassing
         # This is just begging for subclassing

-        next MESSAGE if ( lc( $message->{'message_transport_type'} eq 'rss' ) );
+        next MESSAGE if ( lc($message->{'message_transport_type'}) eq 'rss' );

         if ( lc( $message->{'message_transport_type'} ) eq 'email' ) {
         if ( lc( $message->{'message_transport_type'} ) eq 'email' ) {

-            _send_message_by_email( $message );
+            _send_message_by_email( $message, $params->{'username'}, $params->{'password'}, $params->{'method'} );

         }
         }

-        if ( lc( $message->{'message_transport_type'} ) eq 'sms' ) {
+        elsif ( lc( $message->{'message_transport_type'} ) eq 'sms' ) {

             _send_message_by_sms( $message );
         }
     }
             _send_message_by_sms( $message );
         }
     }


@@ -603,14 +641,10 @@ sub SendQueuedMessages {
 
 =head2 GetRSSMessages
 
 
 =head2 GetRSSMessages
 

-=over 4
-
-my $message_list = GetRSSMessages( { limit => 10, borrowernumber => '14' } )
+  my $message_list = GetRSSMessages( { limit => 10, borrowernumber => '14' } )

 
 returns a listref of all queued RSS messages for a particular person.
 
 
 returns a listref of all queued RSS messages for a particular person.
 

-=back
-

 =cut
 
 sub GetRSSMessages {
 =cut
 
 sub GetRSSMessages {


@@ -625,19 +659,31 @@ sub GetRSSMessages {
                                    borrowernumber         => $params->{'borrowernumber'}, } );
 }
 
                                    borrowernumber         => $params->{'borrowernumber'}, } );
 }
 

-=head2 GetQueuedMessages
+=head2 GetPrintMessages
+
+  my $message_list = GetPrintMessages( { borrowernumber => $borrowernumber } )
+
+Returns a arrayref of all queued print messages (optionally, for a particular
+person).
+
+=cut
+
+sub GetPrintMessages {
+    my $params = shift || {};
+    
+    return _get_unsent_messages( { message_transport_type => 'print',
+                                   borrowernumber         => $params->{'borrowernumber'}, } );
+}

 
 

-=over 4
+=head2 GetQueuedMessages ([$hashref])

 
 

-my $messages = GetQueuedMessage( { borrowernumber => '123', limit => 20 } );
+  my $messages = GetQueuedMessage( { borrowernumber => '123', limit => 20 } );

 
 fetches messages out of the message queue.
 
 returns:
 list of hashes, each has represents a message in the message queue.
 
 
 fetches messages out of the message queue.
 
 returns:
 list of hashes, each has represents a message in the message queue.
 

-=back
-

 =cut
 
 sub GetQueuedMessages {
 =cut
 
 sub GetQueuedMessages {


@@ -667,8 +713,7 @@ ENDSQL
 
     my $sth = $dbh->prepare( $statement );
     my $result = $sth->execute( @query_params );
 
     my $sth = $dbh->prepare( $statement );
     my $result = $sth->execute( @query_params );

-    my $messages = $sth->fetchall_arrayref({});
-    return $messages;
+    return $sth->fetchall_arrayref({});

 }
 
 =head2 _add_attachements
 }
 
 =head2 _add_attachements


@@ -716,17 +761,17 @@ 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
     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
-WHERE status = 'pending'
+  FROM message_queue
+ WHERE status = ?

 ENDSQL
 
 ENDSQL
 

-    my @query_params;
+    my @query_params = ('pending');

     if ( ref $params ) {
         if ( $params->{'message_transport_type'} ) {
             $statement .= ' AND message_transport_type = ? ';
     if ( ref $params ) {
         if ( $params->{'message_transport_type'} ) {
             $statement .= ' AND message_transport_type = ? ';


@@ -741,70 +786,89 @@ ENDSQL
             push @query_params, $params->{'limit'};
         }
     }
             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 );
     my $result = $sth->execute( @query_params );
     my $sth = $dbh->prepare( $statement );
     my $result = $sth->execute( @query_params );

-    my $unsent_messages = $sth->fetchall_arrayref({});
-    return $unsent_messages;
+    return $sth->fetchall_arrayref({});

 }
 
 }
 

-sub _send_message_by_email {
-    my $message = shift;
-
-    my $member = C4::Members::GetMember( $message->{'borrowernumber'} );
-    return unless $message->{'to_address'} or $member->{'email'};
+sub _send_message_by_email ($;$$$) {
+    my $message = shift or return;
+    my ($username, $password, $method) = @_;
+
+    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};
+        }
+        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?
+            _set_message_status( { message_id => $message->{'message_id'},
+                                   status     => 'failed' } );
+            return;
+        }
+    }

 
 

-       my $content = encode('utf8', $message->{'content'});
+    my $utf8   = decode('MIME-Header', $message->{'subject'} );
+    $message->{subject}= encode('MIME-Header', $utf8);
+    my $content = encode('utf8', $message->{'content'});

     my %sendmail_params = (
     my %sendmail_params = (

-        To   => $message->{'to_address'}   || $member->{'email'},
+        To   => $to_address,

         From => $message->{'from_address'} || C4::Context->preference('KohaAdminEmailAddress'),
         From => $message->{'from_address'} || C4::Context->preference('KohaAdminEmailAddress'),

-        Subject => $message->{'subject'},
-               charset => 'utf8',
+        Subject => encode('utf8', $message->{'subject'}),
+        charset => 'utf8',

         Message => $content,
         Message => $content,

+        'content-type' => $message->{'content_type'} || 'text/plain; charset="UTF-8"',

     );
     );

-    if ($message->{'content_type'}) {
-        $sendmail_params{'content-type'} = $message->{'content_type'};
+    $sendmail_params{'Auth'} = {user => $username, pass => $password, method => $method} if $username;
+    if ( my $bcc = C4::Context->preference('OverdueNoticeBcc') ) {
+       $sendmail_params{ Bcc } = $bcc;

     }
     }

-    my $success = sendmail( %sendmail_params );
+    

 
 

-    if ( $success ) {
-        # warn "OK. Log says:\n", $Mail::Sendmail::log;
+    if ( sendmail( %sendmail_params ) ) {

         _set_message_status( { message_id => $message->{'message_id'},
         _set_message_status( { message_id => $message->{'message_id'},

-                               status     => 'sent' } );
-        return $success;
+                status     => 'sent' } );
+        return 1;

     } else {
     } else {

-        # warn $Mail::Sendmail::error;

         _set_message_status( { message_id => $message->{'message_id'},
         _set_message_status( { message_id => $message->{'message_id'},

-                               status     => 'failed' } );
+                status     => 'failed' } );
+        carp $Mail::Sendmail::error;

         return;
     }
 }
 
         return;
     }
 }
 

-sub _send_message_by_sms {
-    my $message = shift;
-
-    my $member = C4::Members::GetMember( $message->{'borrowernumber'} );
+sub _send_message_by_sms ($) {
+    my $message = shift or return undef;
+    my $member = C4::Members::GetMember( 'borrowernumber' => $message->{'borrowernumber'} );

     return unless $member->{'smsalertnumber'};
 
     my $success = C4::SMS->send_sms( { destination => $member->{'smsalertnumber'},
                                        message     => $message->{'content'},
                                      } );
     return unless $member->{'smsalertnumber'};
 
     my $success = C4::SMS->send_sms( { destination => $member->{'smsalertnumber'},
                                        message     => $message->{'content'},
                                      } );

-    if ( $success ) {
-        _set_message_status( { message_id => $message->{'message_id'},
-                               status     => 'sent' } );
-        return $success;
-    } else {
-        _set_message_status( { message_id => $message->{'message_id'},
-                               status     => 'failed' } );
-        return;
-    }
+    _set_message_status( { message_id => $message->{'message_id'},
+                           status     => ($success ? 'sent' : 'failed') } );
+    return $success;

 }
 
 }
 

-sub _set_message_status {
-    my $params = shift;
+sub _set_message_status ($) {
+    my $params = shift or return undef;

 
     foreach my $required_parameter ( qw( message_id status ) ) {
 
     foreach my $required_parameter ( qw( message_id status ) ) {

-        return unless exists $params->{ $required_parameter };
+        return undef unless exists $params->{ $required_parameter };

     }
 
     my $dbh = C4::Context->dbh();
     }
 
     my $dbh = C4::Context->dbh();