dbscripts/table2mail.pl

   1 #!/usr/bin/perl
   2
   3 use DBI;
   4
   5 $0=~m#[^/]+$#;
   6 $cmdpath=$`;
   7 if ($cmdpath eq "") { $cmdpath="./"; }
   8
   9 unshift(@INC,$cmdpath."lib");
  10 require "property.pl";
  11 require "log.pl";
  12 require "convert.pl";
  13
  14 ### configure here
  15
  16 $config_file=$cmdpath."mail.config";
  17 # print $config_file,"\n";
  18
  19 ### no configuration beyond this line
  20
  21 $props=&Property::read($config_file);
  22
  23 ### connect logfile
  24
  25 tie *LOGFILE, 'Logfile', $$props{'public'}{'logfile'}, 'MAIL';
  26 print LOGFILE "starting: ".localtime(time)."\n";
  27
  28 # print $$props{'db'}{'dsn'},$$props{'db'}{'user'},$$props{'db'}{'passwd'},"\n";
  29
  30
  31 ### connect to db
  32 $dbh=DBI->connect($$props{'db'}{'dsn'},$$props{'db'}{'user'},$$props{'db'}{'passwd'});
  33 if ($dbh->errstr ne "") {
  34         print LOGFILE "Error while connecting DB - ".$dbh->error."\n";
  35         exit(0);
  36         }
  37
  38 ### prepare some statements
  39
  40 $select_unsend=$dbh->prepare("select id,ort,titel,urheber,urheber_id,date_format(datum,'%d.%m.%Y') as pdatum,contentdata,abstract,islink,ishtml,link_url from ".$$props{'mail'}{'table'}." where mail_sent=0 and freigeschaltet=1 and adddate(lastchange,interval 30 MINUTE) < current_timestamp()");
  41 $select_urheber=$dbh->prepare("select name,email,homepage from gruppen where id=?");
  42 $update_unsend=$dbh->prepare("update ".$$props{'mail'}{'table'}." set mail_sent=1 where id=?");
  43
  44 $select_unsend->execute;
  45 while ($hash=$select_unsend->fetchrow_hashref) {
  46         if (open(MAILER,"|".$$props{'public'}{'sendmail'})) {
  47                 print MAILER "To: ".$$props{'mail'}{'to'}."\n";
  48                 print MAILER "From: ".$$props{'mail'}{'from'}."\n";
  49                 print MAILER "Reply-To: ".$$props{'mail'}{'reply-to'}."\n";
  50                 print MAILER "Subject: ".$$hash{'ort'}.": ".$$hash{'titel'}."\n";
  51
  52                 print MAILER "\n"; # end of header
  53
  54                 print MAILER $$hash{'titel'}."\n";
  55
  56                 $urheber="";
  57                 $u_email="";
  58                 $u_homepage="";
  59                 if ($$hash{'urheber'} eq "") {
  60                         if ($$hash{'urheber_id'} != 0) {
  61                                 $select_urheber->execute($$hash{'urheber_id'});
  62                                 if ($line=$select_urheber->fetchrow_hashref()) {
  63                                         $urheber=$$line{'name'};
  64                                         $u_email=$$line{'email'};
  65                                         $u_hompage=$$line{'homepage'};
  66                                         }
  67                                 }
  68                         }
  69                 else {
  70                         $urheber=$$hash{'urheber'};
  71                         }
  72
  73                 if ($urheber ne "") { print MAILER "Von  : ".$urheber."\n"; }
  74                 if ($u_email ne "") { print MAILER "Email: ".$u_email."\n"; }
  75                 if ($u_homepage ne "") { print MAILER "Page  : ".$u_homepage."\n"; }
  76
  77                 print MAILER "Ort  : ".$$hash{'ort'}."\n";
  78                 print MAILER "Datum: ".$$hash{'pdatum'}."\n";
  79                 if ($$hash{'islink'} == 1) {
  80                         print MAILER "\n\n".$$hash{'abstract'}."\n";
  81                         }
  82                 else {
  83                         if ($$hash{'ishtml'}>0) {
  84                                 print MAILER "\n\n".&html_to_text($props,$$hash{'contentdata'})."\n";
  85                                 }
  86                         else {
  87                                 print MAILER "\n\n".$$hash{'contentdata'}."\n";
  88                                 }
  89                         }
  90
  91                 if ($$hash{'link_url'} ne "") {
  92                         print MAILER "Weiteres: ";
  93                         if (substr($$hash{'link_url'},0,1) eq "/") {
  94                                 print MAILER "http://www.nadir.org";
  95                                 }
  96                         print MAILER $$hash{'link_url'}."\n";
  97                         }
  98                 ### footer
  99                 print MAILER "\n*** $LIST -- Aboliste mit Nachrichten von http://www.nadir.org\n";
 100                 print MAILER "*** Beitraege: nadir-aktuell\@nadir.org / Redaktion: nadir-aktuell-red\@nadir.org\n";
 101                 print MAILER "*** Unsubscribe: majordomo\@nadir.org mit unsubscribe $LIST im body\n";
 102
 103                 close(MAILER);
 104
 105                 $update_unsend->execute($$hash{'id'});
 106                 print LOGFILE "[".$$hash{'id'}."] send\n"
 107                 }
 108         else {
 109                 print LOGFILE "Error cannot open ".$$props{'public'}{'sendmail'}." - $!\n";
 110                 }
 111         }
 112