misc/cronjobs/notifyMailsOp.pl

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