Bug 12237: Remove the "horrible hack" in C4::Templates

[koha_fer] / C4 / Letters.pm

diff --git a/C4/Letters.pm b/C4/Letters.pm
index 89213fe..1d916e0 100644 (file)
--- a/C4/Letters.pm
+++ b/C4/Letters.pm
@@ -23,12 +23,14 @@ use warnings;
 use MIME::Lite;
 use Mail::Sendmail;
 
 use MIME::Lite;
 use Mail::Sendmail;
 

+use C4::Koha qw(GetAuthorisedValueByCode);

 use C4::Members;
 use C4::Members::Attributes qw(GetBorrowerAttributes);
 use C4::Branch;
 use C4::Log;
 use C4::SMS;
 use C4::Debug;
 use C4::Members;
 use C4::Members::Attributes qw(GetBorrowerAttributes);
 use C4::Branch;
 use C4::Log;
 use C4::SMS;
 use C4::Debug;

+use Koha::DateUtils;

 use Date::Calc qw( Add_Delta_Days );
 use Encode;
 use Carp;
 use Date::Calc qw( Add_Delta_Days );
 use Encode;
 use Carp;


@@ -36,13 +38,13 @@ use Carp;
 use vars qw($VERSION @ISA @EXPORT @EXPORT_OK %EXPORT_TAGS);
 
 BEGIN {
 use vars qw($VERSION @ISA @EXPORT @EXPORT_OK %EXPORT_TAGS);
 
 BEGIN {

-       require Exporter;
-       # set the version for version checking
-       $VERSION = 3.01;
-       @ISA = qw(Exporter);
-       @EXPORT = qw(
-       &GetLetters &GetPreparedLetter &GetWrappedLetter &addalert &getalert &delalert &findrelatedto &SendAlerts &GetPrintMessages
-       );
+    require Exporter;
+    # set the version for version checking
+    $VERSION = 3.07.00.049;
+    @ISA = qw(Exporter);
+    @EXPORT = qw(
+        &GetLetters &GetPreparedLetter &GetWrappedLetter &addalert &getalert &delalert &findrelatedto &SendAlerts &GetPrintMessages &GetMessageTransportTypes
+    );

 }
 
 =head1 NAME
 }
 
 =head1 NAME


@@ -60,81 +62,70 @@ 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.
 

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

 
 

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

   returns informations about letters.
   returns informations about letters.

-  if needed, $category filters for letters given category
-  Create a letter selector with the following code
-
-=head3 in PERL SCRIPT
-
-my $letters = GetLetters($cat);
-my @letterloop;
-foreach my $thisletter (keys %$letters) {
-    my $selected = 1 if $thisletter eq $letter;
-    my %row =(
-        value => $thisletter,
-        selected => $selected,
-        lettername => $letters->{$thisletter},
-    );
-    push @letterloop, \%row;
-}
-$template->param(LETTERLOOP => \@letterloop);
-
-=head3 in TEMPLATE
-
-    <select name="letter">
-        <option value="">Default</option>
-    <!-- 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>
+  if needed, $module filters for letters given module

 
 =cut
 
 
 =cut
 

-sub GetLetters (;$) {
+sub GetLetters {
+    my ($filters) = @_;
+    my $module    = $filters->{module};
+    my $code      = $filters->{code};
+    my $dbh       = C4::Context->dbh;
+    my $letters   = $dbh->selectall_arrayref(
+        q|
+            SELECT module, code, branchcode, name
+            FROM letter
+            WHERE 1
+        |
+          . ( $module ? q| AND module = ?| : q|| )
+          . ( $code   ? q| AND code = ?|   : q|| )
+          . q| GROUP BY code ORDER BY name|, { Slice => {} }
+        , ( $module ? $module : () )
+        , ( $code ? $code : () )
+    );

 
 

-    # returns a reference to a hash of references to ALL letters...
-    my $cat = shift;
-    my %letters;
-    my $dbh = C4::Context->dbh;
-    my $sth;
-    if (defined $cat) {
-        my $query = "SELECT * FROM letter WHERE module = ? ORDER BY name";
-        $sth = $dbh->prepare($query);
-        $sth->execute($cat);
-    }
-    else {
-        my $query = "SELECT * FROM letter ORDER BY name";
-        $sth = $dbh->prepare($query);
-        $sth->execute;
-    }
-    while ( my $letter = $sth->fetchrow_hashref ) {
-        $letters{ $letter->{'code'} } = $letter->{'name'};
-    }
-    return \%letters;
+    return $letters;

 }
 
 }
 

-my %letter;
-sub getletter ($$$) {
-    my ( $module, $code, $branchcode ) = @_;
+# FIXME: using our here means that a Plack server will need to be
+#        restarted fairly regularly when working with this routine.
+#        A better option would be to use Koha::Cache and use a cache
+#        that actually works in a persistent environment, but as a
+#        short-term fix, our will work.
+our %letter;
+sub getletter {
+    my ( $module, $code, $branchcode, $message_transport_type ) = @_;
+    $message_transport_type ||= 'email';
+
+
+    if ( C4::Context->preference('IndependentBranches')
+            and $branchcode
+            and C4::Context->userenv ) {

 
 

-    if (C4::Context->preference('IndependantBranches') && $branchcode){

         $branchcode = C4::Context->userenv->{'branch'};
     }
         $branchcode = C4::Context->userenv->{'branch'};
     }

+    $branchcode //= '';

 
 

-    if ( my $l = $letter{$module}{$code}{$branchcode} ) {
+    if ( my $l = $letter{$module}{$code}{$branchcode}{$message_transport_type} ) {

         return { %$l }; # deep copy
     }
 
     my $dbh = C4::Context->dbh;
         return { %$l }; # deep copy
     }
 
     my $dbh = C4::Context->dbh;

-    my $sth = $dbh->prepare("select * from letter where module=? and code=? and (branchcode = ? or branchcode = '') order by branchcode desc limit 1");
-    $sth->execute( $module, $code, $branchcode );
+    my $sth = $dbh->prepare(q{
+        SELECT *
+        FROM letter
+        WHERE module=? AND code=? AND (branchcode = ? OR branchcode = '') AND message_transport_type = ?
+        ORDER BY branchcode DESC LIMIT 1
+    });
+    $sth->execute( $module, $code, $branchcode, $message_transport_type );

     my $line = $sth->fetchrow_hashref
       or return;
     $line->{'content-type'} = 'text/html; charset="UTF-8"' if $line->{is_html};
     my $line = $sth->fetchrow_hashref
       or return;
     $line->{'content-type'} = 'text/html; charset="UTF-8"' if $line->{is_html};

-    $letter{$module}{$code}{$branchcode} = $line;
+    $letter{$module}{$code}{$branchcode}{$message_transport_type} = $line;

     return { %$line };
 }
 
     return { %$line };
 }
 


@@ -149,7 +140,7 @@ sub getletter ($$$) {
 
 =cut
 
 
 =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 =


@@ -170,7 +161,7 @@ sub addalert ($$$) {
 
 =cut
 
 
 =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=?");
     my $alertid = shift or die "delalert() called without valid argument (alertid)";    # it's gonna die anyway.
     $debug and warn "delalert: deleting alertid $alertid";
     my $sth = C4::Context->dbh->prepare("delete from alert where alertid=?");


@@ -187,7 +178,7 @@ sub delalert ($) {
 
 =cut
 
 
 =cut
 

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

     my ( $borrowernumber, $type, $externalid ) = @_;
     my $dbh   = C4::Context->dbh;
     my $query = "SELECT a.*, b.branchcode FROM alert a JOIN borrowers b USING(borrowernumber) WHERE";
     my ( $borrowernumber, $type, $externalid ) = @_;
     my $dbh   = C4::Context->dbh;
     my $query = "SELECT a.*, b.branchcode FROM alert a JOIN borrowers b USING(borrowernumber) WHERE";


@@ -224,16 +215,16 @@ sub getalert (;$$$) {
 # outmoded POD:
 # When type=virtual, the id is related to a virtual shelf and this sub returns the name of the sub
 
 # 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'";
     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);
     }
     my $sth = C4::Context->dbh->prepare($q);
     $sth->execute($externalid);


@@ -277,6 +268,7 @@ sub SendAlerts {
 
             #          warn "sending issues...";
             my $userenv = C4::Context->userenv;
 
             #          warn "sending issues...";
             my $userenv = C4::Context->userenv;

+            my $branchdetails = GetBranchDetail($_->{'branchcode'});

             my $letter = GetPreparedLetter (
                 module => 'serial',
                 letter_code => $letter_code,
             my $letter = GetPreparedLetter (
                 module => 'serial',
                 letter_code => $letter_code,


@@ -293,7 +285,7 @@ sub SendAlerts {
             # ... then send mail
             my %mail = (
                 To      => $email,
             # ... then send mail
             my %mail = (
                 To      => $email,

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

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


@@ -312,7 +304,7 @@ sub SendAlerts {
             FROM aqorders
             LEFT JOIN aqbasket ON aqbasket.basketno=aqorders.basketno
             LEFT JOIN biblio ON aqorders.biblionumber=biblio.biblionumber
             FROM aqorders
             LEFT JOIN aqbasket ON aqbasket.basketno=aqorders.basketno
             LEFT JOIN biblio ON aqorders.biblionumber=biblio.biblionumber

-            LEFT JOIN biblioitems ON aqorders.biblioitemnumber=biblioitems.biblioitemnumber
+            LEFT JOIN biblioitems ON aqorders.biblionumber=biblioitems.biblionumber

             LEFT JOIN aqbooksellers ON aqbasket.booksellerid=aqbooksellers.id
             WHERE aqorders.ordernumber IN (
             }
             LEFT JOIN aqbooksellers ON aqbasket.booksellerid=aqbooksellers.id
             WHERE aqorders.ordernumber IN (
             }


@@ -434,9 +426,10 @@ sub GetPreparedLetter {
     my $module      = $params{module} or croak "No module";
     my $letter_code = $params{letter_code} or croak "No letter_code";
     my $branchcode  = $params{branchcode} || '';
     my $module      = $params{module} or croak "No module";
     my $letter_code = $params{letter_code} or croak "No letter_code";
     my $branchcode  = $params{branchcode} || '';

+    my $mtt         = $params{message_transport_type} || 'email';

 
 

-    my $letter = getletter( $module, $letter_code, $branchcode )
-        or warn( "No $module $letter_code letter"),
+    my $letter = getletter( $module, $letter_code, $branchcode, $mtt )
+        or warn( "No $module $letter_code letter transported by " . $mtt ),

             return;
 
     my $tables = $params{tables};
             return;
 
     my $tables = $params{tables};


@@ -454,6 +447,9 @@ sub GetPreparedLetter {
        }
     }
 
        }
     }
 

+    my $OPACBaseURL = C4::Context->preference('OPACBaseURL');
+    $letter->{content} =~ s/<<OPACBaseURL>>/$OPACBaseURL/go;
+

     if ($want_librarian) {
         # parsing librarian name
         my $userenv = C4::Context->userenv;
     if ($want_librarian) {
         # parsing librarian name
         my $userenv = C4::Context->userenv;


@@ -536,43 +532,48 @@ sub _substitute_tables {
             $sth->execute( $ref ? @$param : $param );
 
             $values = $sth->fetchrow_hashref;
             $sth->execute( $ref ? @$param : $param );
 
             $values = $sth->fetchrow_hashref;

+            $sth->finish();

         }
 
         _parseletter ( $letter, $table, $values );
     }
 }
 
         }
 
         _parseletter ( $letter, $table, $values );
     }
 }
 

-my %handles = ();

 sub _parseletter_sth {
     my $table = shift;
 sub _parseletter_sth {
     my $table = shift;

+    my $sth;

     unless ($table) {
         carp "ERROR: _parseletter_sth() called without argument (table)";
         return;
     }
     unless ($table) {
         carp "ERROR: _parseletter_sth() called without argument (table)";
         return;
     }

-    # check cache first
-    (defined $handles{$table}) and return $handles{$table};
+    # NOTE: we used to check whether we had a statement handle cached in
+    #       a %handles module-level variable. This was a dumb move and
+    #       broke things for the rest of us. prepare_cached is a better
+    #       way to cache statement handles anyway.

     my $query = 
     my $query = 

-    ($table eq 'biblio'       ) ? "SELECT * FROM $table WHERE   biblionumber = ?"                      :
-    ($table eq 'biblioitems'  ) ? "SELECT * FROM $table WHERE   biblionumber = ?"                      :
-    ($table eq 'items'        ) ? "SELECT * FROM $table WHERE     itemnumber = ?"                      :
-    ($table eq 'issues'       ) ? "SELECT * FROM $table WHERE     itemnumber = ?"                      :
-    ($table eq 'reserves'     ) ? "SELECT * FROM $table WHERE borrowernumber = ? and biblionumber = ?" :
-    ($table eq 'borrowers'    ) ? "SELECT * FROM $table WHERE borrowernumber = ?"                      :
-    ($table eq 'branches'     ) ? "SELECT * FROM $table WHERE     branchcode = ?"                      :
-    ($table eq 'suggestions'  ) ? "SELECT * FROM $table WHERE   suggestionid = ?"                      :
-    ($table eq 'aqbooksellers') ? "SELECT * FROM $table WHERE             id = ?"                      :
-    ($table eq 'aqorders'     ) ? "SELECT * FROM $table WHERE    ordernumber = ?"                      :
-    ($table eq 'opac_news'    ) ? "SELECT * FROM $table WHERE          idnew = ?"                      :
+    ($table eq 'biblio'       ) ? "SELECT * FROM $table WHERE   biblionumber = ?"                                  :
+    ($table eq 'biblioitems'  ) ? "SELECT * FROM $table WHERE   biblionumber = ?"                                  :
+    ($table eq 'items'        ) ? "SELECT * FROM $table WHERE     itemnumber = ?"                                  :
+    ($table eq 'issues'       ) ? "SELECT * FROM $table WHERE     itemnumber = ?"                                  :
+    ($table eq 'old_issues'   ) ? "SELECT * FROM $table WHERE     itemnumber = ? ORDER BY timestamp DESC LIMIT 1"  :
+    ($table eq 'reserves'     ) ? "SELECT * FROM $table WHERE borrowernumber = ? and biblionumber = ?"             :
+    ($table eq 'borrowers'    ) ? "SELECT * FROM $table WHERE borrowernumber = ?"                                  :
+    ($table eq 'branches'     ) ? "SELECT * FROM $table WHERE     branchcode = ?"                                  :
+    ($table eq 'suggestions'  ) ? "SELECT * FROM $table WHERE   suggestionid = ?"                                  :
+    ($table eq 'aqbooksellers') ? "SELECT * FROM $table WHERE             id = ?"                                  :
+    ($table eq 'aqorders'     ) ? "SELECT * FROM $table WHERE    ordernumber = ?"                                  :
+    ($table eq 'opac_news'    ) ? "SELECT * FROM $table WHERE          idnew = ?"                                  :
+    ($table eq 'borrower_modifications') ? "SELECT * FROM $table WHERE borrowernumber = ? OR verification_token =?":

     undef ;
     unless ($query) {
         warn "ERROR: No _parseletter_sth query for table '$table'";
         return;     # nothing to get
     }
     undef ;
     unless ($query) {
         warn "ERROR: No _parseletter_sth query for table '$table'";
         return;     # nothing to get
     }

-    unless ($handles{$table} = C4::Context->dbh->prepare($query)) {
+    unless ($sth = C4::Context->dbh->prepare_cached($query)) {

         warn "ERROR: Failed to prepare query: '$query'";
         return;
     }
         warn "ERROR: Failed to prepare query: '$query'";
         return;
     }

-    return $handles{$table};    # now cache is populated for that $table
+    return $sth;    # now cache is populated for that $table

 }
 
 =head2 _parseletter($letter, $table, $values)
 }
 
 =head2 _parseletter($letter, $table, $values)


@@ -586,40 +587,38 @@ sub _parseletter_sth {
 
 =cut
 
 
 =cut
 

-my %columns = ();

 sub _parseletter {
     my ( $letter, $table, $values ) = @_;
 
 sub _parseletter {
     my ( $letter, $table, $values ) = @_;
 

-    # TEMPORARY hack until the expirationdate column is added to reserves

     if ( $table eq 'reserves' && $values->{'waitingdate'} ) {
         my @waitingdate = split /-/, $values->{'waitingdate'};
 
     if ( $table eq 'reserves' && $values->{'waitingdate'} ) {
         my @waitingdate = split /-/, $values->{'waitingdate'};
 

-        $values->{'expirationdate'} = C4::Dates->new(
-            sprintf(
-                '%04d-%02d-%02d',
-                Add_Delta_Days( @waitingdate, C4::Context->preference( 'ReservesMaxPickUpDelay' ) )
-            ),
-            'iso'
-        )->output();
+        $values->{'expirationdate'} = '';
+        if( C4::Context->preference('ExpireReservesMaxPickUpDelay') &&
+        C4::Context->preference('ReservesMaxPickUpDelay') ) {
+            my $dt = dt_from_string();
+            $dt->add( days => C4::Context->preference('ReservesMaxPickUpDelay') );
+            $values->{'expirationdate'} = output_pref({ dt => $dt, dateonly => 1 });
+        }
+
+        $values->{'waitingdate'} = output_pref({ dt => dt_from_string( $values->{'waitingdate'} ), dateonly => 1 });
+

     }
 
     if ($letter->{content} && $letter->{content} =~ /<<today>>/) {
     }
 
     if ($letter->{content} && $letter->{content} =~ /<<today>>/) {

-        my @da = localtime();
-        my $todaysdate = "$da[2]:$da[1]  " . C4::Dates->today();
+        my $todaysdate = output_pref( DateTime->now() );

         $letter->{content} =~ s/<<today>>/$todaysdate/go;
     }
 
         $letter->{content} =~ s/<<today>>/$todaysdate/go;
     }
 

-    # and get all fields from the table
-#   my $columns = $columns{$table};
-#   unless ($columns) {
-#       $columns = $columns{$table} =  C4::Context->dbh->selectcol_arrayref("SHOW COLUMNS FROM $table");
-#   }
-#   foreach my $field (@$columns) {
-

     while ( my ($field, $val) = each %$values ) {
         my $replacetablefield = "<<$table.$field>>";
         my $replacefield = "<<$field>>";
     while ( my ($field, $val) = each %$values ) {
         my $replacetablefield = "<<$table.$field>>";
         my $replacefield = "<<$field>>";

-        $val =~ s/\p{P}(?=$)//g if $val;
+        $val =~ s/\p{P}$// if $val && $table=~/biblio/;
+            #BZ 9886: Assuming that we want to eliminate ISBD punctuation here
+            #Therefore adding the test on biblio. This includes biblioitems,
+            #but excludes items. Removed unneeded global and lookahead.
+
+        $val = GetAuthorisedValueByCode ('ROADTYPE', $val, 0) if $table=~/^borrowers$/ && $field=~/^streettype$/;

         my $replacedby   = defined ($val) ? $val : '';
         ($letter->{title}  ) and do {
             $letter->{title}   =~ s/$replacetablefield/$replacedby/g;
         my $replacedby   = defined ($val) ? $val : '';
         ($letter->{title}  ) and do {
             $letter->{title}   =~ s/$replacetablefield/$replacedby/g;


@@ -661,17 +660,24 @@ places a letter in the message_queue database table, which will
 eventually get processed (sent) by the process_message_queue.pl
 cronjob when it calls SendQueuedMessages.
 
 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
 
 
 =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->{'letter'};

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

     return unless exists $params->{'message_transport_type'};
 
     return unless exists $params->{'message_transport_type'};
 

+    my $content = $params->{letter}->{content};
+    $content =~ s/\s+//g if(defined $content);
+    if ( not defined $content or $content eq '' ) {
+        warn "Trying to add an empty message to the message queue" if $debug;
+        return;
+    }
+

     # If we have any attachments we should encode then into the body.
     if ( $params->{'attachments'} ) {
         $params->{'letter'} = _add_attachments(
     # If we have any attachments we should encode then into the body.
     if ( $params->{'attachments'} ) {
         $params->{'letter'} = _add_attachments(


@@ -703,7 +709,7 @@ ENDSQL
         $params->{'from_address'},                # from_address
         $params->{'letter'}->{'content-type'},    # content_type
     );
         $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]) 
 }
 
 =head2 SendQueuedMessages ([$hashref]) 


@@ -716,7 +722,7 @@ returns number of messages sent.
 
 =cut
 
 
 =cut
 

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

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


@@ -771,7 +777,8 @@ sub GetPrintMessages {
     my $params = shift || {};
     
     return _get_unsent_messages( { message_transport_type => 'print',
     my $params = shift || {};
     
     return _get_unsent_messages( { message_transport_type => 'print',

-                                   borrowernumber         => $params->{'borrowernumber'}, } );
+                                   borrowernumber         => $params->{'borrowernumber'},
+                                 } );

 }
 
 =head2 GetQueuedMessages ([$hashref])
 }
 
 =head2 GetQueuedMessages ([$hashref])


@@ -815,6 +822,24 @@ ENDSQL
     return $sth->fetchall_arrayref({});
 }
 
     return $sth->fetchall_arrayref({});
 }
 

+=head2 GetMessageTransportTypes
+
+  my @mtt = GetMessageTransportTypes();
+
+  returns an arrayref of transport types
+
+=cut
+
+sub GetMessageTransportTypes {
+    my $dbh = C4::Context->dbh();
+    my $mtts = $dbh->selectcol_arrayref("
+        SELECT message_transport_type
+        FROM message_transport_types
+        ORDER BY message_transport_type
+    ");
+    return $mtts;
+}
+

 =head2 _add_attachements
 
 named parameters:
 =head2 _add_attachements
 
 named parameters:


@@ -861,13 +886,14 @@ sub _add_attachments {
 
 }
 
 
 }
 

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

     my $params = shift;
 
     my $dbh = C4::Context->dbh();
     my $statement = << 'ENDSQL';
     my $params = shift;
 
     my $dbh = C4::Context->dbh();
     my $statement = << 'ENDSQL';

-SELECT message_id, borrowernumber, subject, content, message_transport_type, status, time_queued, from_address, to_address, content_type
-  FROM message_queue
+SELECT mq.message_id, mq.borrowernumber, mq.subject, mq.content, mq.message_transport_type, mq.status, mq.time_queued, mq.from_address, mq.to_address, mq.content_type, b.branchcode, mq.letter_code
+  FROM message_queue mq
+  LEFT JOIN borrowers b ON b.borrowernumber = mq.borrowernumber

  WHERE status = ?
 ENDSQL
 
  WHERE status = ?
 ENDSQL
 


@@ -886,6 +912,7 @@ 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 );
     $debug and warn "_get_unsent_messages SQL: $statement";
     $debug and warn "_get_unsent_messages params: " . join(',',@query_params);
     my $sth = $dbh->prepare( $statement );


@@ -893,26 +920,20 @@ ENDSQL
     return $sth->fetchall_arrayref({});
 }
 
     return $sth->fetchall_arrayref({});
 }
 

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

     my $message = shift or return;
     my ($username, $password, $method) = @_;
 
     my $message = shift or return;
     my ($username, $password, $method) = @_;
 

-    my $to_address = $message->{to_address};
+    my $member = C4::Members::GetMember( 'borrowernumber' => $message->{'borrowernumber'} );
+    my $to_address = $message->{'to_address'};

     unless ($to_address) {
     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;
         }
         unless ($member) {
             warn "FAIL: No 'to_address' and INVALID borrowernumber ($message->{borrowernumber})";
             _set_message_status( { message_id => $message->{'message_id'},
                                    status     => 'failed' } );
             return;
         }

-        my $which_address = C4::Context->preference('AutoEmailPrimaryAddress');
-        # If the system preference is set to 'first valid' (value == OFF), look up email address
-        if ($which_address eq 'OFF') {
-            $to_address = GetFirstValidEmailAddress( $message->{'borrowernumber'} );
-        } else {
-            $to_address = $member->{$which_address};
-        }
+        $to_address = C4::Members::GetNoticeEmailAddress( $message->{'borrowernumber'} );

         unless ($to_address) {  
             # warn "FAIL: No 'to_address' and no email for " . ($member->{surname} ||'') . ", borrowernumber ($message->{borrowernumber})";
             # warning too verbose for this more common case?
         unless ($to_address) {  
             # warn "FAIL: No 'to_address' and no email for " . ($member->{surname} ||'') . ", borrowernumber ($message->{borrowernumber})";
             # warning too verbose for this more common case?


@@ -928,9 +949,12 @@ sub _send_message_by_email ($;$$$) {
     my $content = encode('utf8', $message->{'content'});
     my $content_type = $message->{'content_type'} || 'text/plain; charset="UTF-8"';
     my $is_html = $content_type =~ m/html/io;
     my $content = encode('utf8', $message->{'content'});
     my $content_type = $message->{'content_type'} || 'text/plain; charset="UTF-8"';
     my $is_html = $content_type =~ m/html/io;

+
+    my $branch_email = ( $member ) ? GetBranchDetail( $member->{'branchcode'} )->{'branchemail'} : undef;
+

     my %sendmail_params = (
         To   => $to_address,
     my %sendmail_params = (
         To   => $to_address,

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

         Subject => $subject,
         charset => 'utf8',
         Message => $is_html ? _wrap_html($content, $subject) : $content,
         Subject => $subject,
         charset => 'utf8',
         Message => $is_html ? _wrap_html($content, $subject) : $content,


@@ -975,10 +999,37 @@ $content
 EOS
 }
 
 EOS
 }
 

-sub _send_message_by_sms ($) {
-    my $message = shift or return undef;
+sub _is_duplicate {
+    my ( $message ) = @_;
+    my $dbh = C4::Context->dbh;
+    my $count = $dbh->selectrow_array(q|
+        SELECT COUNT(*)
+        FROM message_queue
+        WHERE message_transport_type = ?
+        AND borrowernumber = ?
+        AND letter_code = ?
+        AND CAST(time_queued AS date) = CAST(NOW() AS date)
+        AND status="sent"
+        AND content = ?
+    |, {}, $message->{message_transport_type}, $message->{borrowernumber}, $message->{letter_code}, $message->{content} );
+    return $count;
+}
+
+sub _send_message_by_sms {
+    my $message = shift or return;

     my $member = C4::Members::GetMember( 'borrowernumber' => $message->{'borrowernumber'} );
     my $member = C4::Members::GetMember( 'borrowernumber' => $message->{'borrowernumber'} );

-    return unless $member->{'smsalertnumber'};
+
+    unless ( $member->{smsalertnumber} ) {
+        _set_message_status( { message_id => $message->{'message_id'},
+                               status     => 'failed' } );
+        return;
+    }
+
+    if ( _is_duplicate( $message ) ) {
+        _set_message_status( { message_id => $message->{'message_id'},
+                               status     => 'failed' } );
+        return;
+    }

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


@@ -994,11 +1045,11 @@ sub _update_message_to_address {
     $dbh->do('UPDATE message_queue SET to_address=? WHERE message_id=?',undef,($to,$id));
 }
 
     $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 ) ) {
 
     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();
     }
 
     my $dbh = C4::Context->dbh();