misc/cronjobs/notifyMailsOp.pl

   1 #!/usr/bin/perl
   2 use strict;
   3 #use warnings; FIXME - Bug 2505
   4 use Carp;
   5 BEGIN {
   6     # find Koha's Perl modules
   7     # test carefully before changing this
   8     use FindBin;
   9     eval { require "$FindBin::Bin/../kohalib.pl" };
  10 }
  11 use C4::Context;
  12 use C4::Dates qw/format_date/;
  13 use Mail::Sendmail;  # comment out if not doing e-mail notices
  14 use Getopt::Long;
  15 use C4::Circulation;
  16 # use C4::Members;
  17 #  this module will notify only the mail case
  18 # Now it's only programmed for ouest provence, you can modify it for yourself
  19 # sub function for get all notifications are not sends
  20 sub GetNotifys {
  21 #       my($branch) = @_;
  22         my $dbh = C4::Context->dbh;
  23         my $sth=$dbh->prepare("SELECT DISTINCT notifys.borrowernumber , borrowers.surname , borrowers.firstname , borrowers.title AS borrower_title , categories.category_type AS categorycode , borrowers.email , borrowers.contacttitle , borrowers.contactname , borrowers.contactfirstname ,
  24         notifys.notify_level , notifys.method
  25         FROM notifys,borrowers,categories WHERE (notifys.borrowernumber=borrowers.borrowernumber) AND (notifys.notify_send_date IS NULL) AND (borrowers.categorycode = categories.categorycode)");
  26
  27         $sth->execute();
  28                 my @getnotifys;
  29                 my $i=0;
  30                 while (my $data=$sth->fetchrow_hashref){
  31                         $getnotifys[$i]=$data;
  32                         $i++;
  33                 }
  34                 $sth->finish;
  35                 return(@getnotifys);
  36
  37 }
  38
  39 sub GetBorrowerNotifys{
  40         my ($borrowernumber) = @_;
  41         my $dbh = C4::Context->dbh;
  42         my @getnotifys2;
  43         my $sth2=$dbh->prepare("SELECT notifys.itemnumber,notifys.notify_level,biblio.title ,itemtypes.description,
  44                         issues.date_due
  45                         FROM notifys,biblio,items,itemtypes,biblioitems,issues
  46                         WHERE
  47                         (items.itemnumber=notifys.itemnumber
  48                         AND biblio.biblionumber=items.biblionumber)
  49                         AND (itemtypes.itemtype=biblioitems.itemtype AND biblioitems.biblionumber=biblio.biblionumber)
  50                         AND
  51                         (notifys.borrowernumber=issues.borrowernumber AND notifys.itemnumber=issues.itemnumber)
  52                         AND
  53                         notifys.borrowernumber=?
  54                         AND notify_send_date IS NULL");
  55                         $sth2->execute($borrowernumber);
  56                         my $j=0;
  57                         while (my $data2=$sth2->fetchrow_hashref){
  58                                 $getnotifys2[$j]=$data2;
  59                                 $j++;
  60                         }
  61                         $sth2->finish;
  62                         return(@getnotifys2);
  63
  64 }
  65
  66 sub GetOverduerules{
  67         my($category,$notify_level) = @_;
  68         my $dbh = C4::Context->dbh;
  69         my $sth=$dbh->prepare("SELECT letter".$notify_level.",debarred".$notify_level." FROM overduerules WHERE categorycode=?");
  70         $sth->execute($category);
  71         my (@overduerules)=$sth->fetchrow_array;
  72         $sth->finish;
  73         return(@overduerules);
  74
  75 }
  76
  77 sub GetLetter{
  78
  79         my($letterid) = @_;
  80         my $dbh = C4::Context->dbh;
  81         my $sth=$dbh->prepare("SELECT title,content FROM letter WHERE code=?");
  82         $sth->execute($letterid);
  83         my (@getletter)=$sth->fetchrow_array;
  84         $sth->finish;
  85         return(@getletter);
  86
  87 }
  88
  89 sub UpdateBorrowerDebarred{
  90         my($borrowernumber) = @_;
  91         my $dbh = C4::Context->dbh;
  92         my $sth=$dbh->prepare("UPDATE borrowers SET debarred='1' WHERE borrowernumber=?");
  93         $sth->execute($borrowernumber);
  94         $sth->finish;
  95         return 1;
  96 }
  97
  98 sub UpdateNotifySendDate{
  99         my($borrowernumber,$itemnumber,$notifyLevel) = @_;
 100         my $dbh = C4::Context->dbh;
 101         my $sth=$dbh->prepare("UPDATE notifys SET notify_send_date=now()
 102         WHERE borrowernumber=? AND itemnumber=? AND notify_send_date IS NULL AND notify_level=?");
 103         $sth->execute($borrowernumber,$itemnumber,$notifyLevel);
 104         $sth->finish;
 105         return 1;
 106
 107 }
 108
 109 # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #
 110
 111 # work with get notifys
 112 my $smtpserver = 'smtp.yoursmtpserver'; # your smtp server (the server who sent mails)
 113 my $from = 'your@librarymailadress'; # all the mails sent to the borrowers will appear coming from here.
 114
 115
 116 # initiate file for wrong_mails
 117 my $outfile = 'wrong_mails.txt';
 118 open( OUT, ">$outfile" );
 119 binmode(OUT, 'utf8');
 120
 121 my @getnofifys = GetNotifys();
 122 foreach my $num (@getnofifys) {
 123         my %notify;
 124 #       if we have a method mail, we check witch mail letter we launch
 125         if ($num->{'method'} eq 'mail'){
 126                 my ($letterid,$debarred) = GetOverduerules($num->{'categorycode'},$num->{'notify_level'});
 127 #                       now, we get the letter associated to letterid
 128                         my($title,$content) = GetLetter($letterid);
 129                         my $email = $num->{'email'};
 130                         #my $email = 'alaurin@ouestprovence.fr';
 131                         my $mailtitle = $title; # the title of the mails
 132 # Work with the adult category code
 133                                 if ($num->{'categorycode'} eq 'A') {
 134         #                       now deal with $content
 135                                         $content =~ s/\<<borrowers.title>\>/$num->{'borrower_title'}/g ;
 136                                         $content =~ s/\<<borrowers.surname>\>/$num->{'surname'}/g ;
 137                                         $content =~ s/\<<borrowers.firstname>\>/$num->{'firstname'}/g ;
 138
 139                                         my @getborrowernotify=GetBorrowerNotifys($num->{'borrowernumber'});
 140                                         my $overdueitems;
 141                                         foreach my $notif(@getborrowernotify){
 142                                                 my $date=format_date($notif->{'date_due'});
 143                                                 if ($notif->{'notify_level'} eq $num->{'notify_level'}){
 144                                                 $overdueitems .= " - <b>".$notif->{'title'}."</b>" ;
 145                                                 $overdueitems .= "  ( ".$notif->{'description'}." )  " ;
 146                                                 $overdueitems .= "emprunté le :".$date;
 147                                                 $overdueitems .= "<br>";
 148
 149 # FIXME at this time, the program consider the mail is send (in notify_send_date) but with no real check must be improved , we don't know if the mail was really to a real adress, and if there is a problem, we don't know how to return the notification to koha...
 150         UpdateNotifySendDate($num->{'borrowernumber'},$notif->{'itemnumber'},$num->{'notify_level'});
 151 }
 152                                         }
 153                                 # if we don't have overdueitem replace content by nonotifys value, deal with it later
 154                                         if ($overdueitems){
 155                                         $content =~ s/\<<items.content>\>/$overdueitems/g;
 156                                 }
 157                                 else {
 158                                 $content = 'nonotifys';
 159                                 }
 160                         }
 161 # Work with the child category code (we add the parents infos)
 162                                 if ($num->{'categorycode'} eq 'C') {
 163                                         $content =~ s/\<<borrowers.contacttitle>\>/$num->{'contacttitle'}/g ;
 164                                         $content =~ s/\<<borrowers.contactname>\>/$num->{'contactname'}/g ;
 165                                         $content =~ s/\<<borrowers.contactfirstname>\>/$num->{'contactfirstname'}/g ;
 166                                         $content =~ s/\<<borrowers.title>\>/$num->{'borrower_title'}/g ;
 167                                         $content =~ s/\<<borrowers.surname>\>/$num->{'surname'}/g ;
 168                                         $content =~ s/\<<borrowers.firstname>\>/$num->{'firstname'}/g ;
 169
 170                                         my @getborrowernotify=GetBorrowerNotifys($num->{'borrowernumber'});
 171                                         my $overdueitems;
 172                                         foreach my $notif(@getborrowernotify){
 173                                                 my $date=format_date($notif->{'date_due'});
 174
 175                                                 $overdueitems .= " - <b>".$notif->{'title'}."</b>" ;
 176                                                 $overdueitems .= "  ( ".$notif->{'description'}." )  " ;
 177                                                 $overdueitems .= "emprunté le :".$date;
 178                                                 $overdueitems .= "<br>";
 179 # FIXME at this time, the program consider the mail is send (in notify_send_date) but with no real check must be improved ...
 180                                 UpdateNotifySendDate($num->{'borrowernumber'},$notif->{'itemnumber'},$num->{'notify_level'});
 181                                                 }
 182
 183                                         if ($overdueitems){
 184                                                 $content =~ s/\<<items.content>\>/$overdueitems/g;
 185                                         }
 186                                         else {
 187                                         $content = 'nonotifys';
 188                                         }
 189                                 }
 190 # initiate the send mail
 191
 192 #       decoding mailtitle for lisibility of mailtitle (bug with utf-8 values, so decoding it)
 193         utf8::decode($mailtitle);
 194
 195                         my $mailtext = $content;
 196                                 unshift @{$Mail::Sendmail::mailcfg{'smtp'}} , $smtpserver;
 197 #                                         set your own mail server name here
 198                                         my %mail = ( To      => $email,
 199                                                                 From    => $from,
 200                                                                 Subject => $mailtitle,
 201                                                                 Message => $mailtext,
 202                                                                 'content-type' => 'text/html; charset="utf-8"',
 203                                         );
 204                                 # if we don't have any content for the mail, we don't launch mail, but notify it in a file
 205                 if ($mailtext ne 'nonotifys') {
 206                     sendmail(%mail) or carp $Mail::Sendmail::error;
 207                 }
 208                 else {
 209                     print OUT $email ;
 210                 }
 211
 212 # now deal with the debarred mode
 213 #               if ($debarred eq 1) {
 214 #               �ajouter : si le lecteur est en mode debarred, ajouter la fonction qui nous permettra cela
 215 #               UpdateBorrowerDebarred($num->{'borrowernumber'});
 216 #               }
 217         close(OUT);
 218         }
 219 }