1
+ − 1 #!/usr/local/bin/perl
+ − 2
+ − 3 use DBI;
+ − 4
+ − 5 $user = "";
+ − 6 $passwd = "";
+ − 7 $dbname = "scs";
+ − 8
+ − 9 MAIN:
+ − 10 {
+ − 11 # Work out the date 7 days ahead
+ − 12 ($sec, $min, $hour, $mday, $mon, $year, $wday, $yday, $isdst) = localtime(time + (24 * 60 * 60 * 7));
+ − 13
+ − 14 $mon += 1; #Months start from 0!!
+ − 15
+ − 16 $date = "$year-$mon-$mday";
+ − 17
+ − 18 # Connect to the Database
+ − 19 $dbh = DBI->connect("dbi:Pg:dbname=$dbname", $user, $passwd) || bad_exit("Couldn\'t open DB - $sth->errstr");
+ − 20
+ − 21 # Prepare the select statement
+ − 22 $sth1 = $dbh->prepare("SELECT eventid, name, description, when, duration, cost, contact FROM events WHERE when > \'$date\' and when < (\'$date\'::datetime + \'24 hours\'::timespan)") || bad_exit("Prepare for description failed - $sth1->errstr");
+ − 23
+ − 24 # Execute it
+ − 25 $numrows1 = $sth1->execute || bad_exit("Execute for description failed - $sth1->errstr");
+ − 26
+ − 27 while (@array = $sth1->fetchrow_array) {
+ − 28 $eventid = @array[0];
+ − 29 $name = @array[1];
+ − 30 $text = @array[2];
+ − 31 $when = @array[3];
+ − 32 $length = @array[4];
+ − 33 $cost = @array[5];
+ − 34 $contact = @array[6];
+ − 35
+ − 36 $mailtxt = sprintf("Hi,\nThis is an automated letter reminding you of an\nevent on %s.\nIt should go for about %s.\n\n%s\n\nIt will cost %s.\n\nIf you want more information contact\n%s.\n", $when, $length, $text, $cost, $contact);
+ − 37
+ − 38 # Prepare the select statement
+ − 39 $sth2 = $dbh->prepare("SELECT mailto(\'$eventid\')") || bad_exit("Couldn\'t prepare ppl list - $sth->errstr");
+ − 40
+ − 41 $numrows2 = $sth2->execute || bad_exit("Couldn\'t execute ppl list - $sth->errstr");
+ − 42
+ − 43 while (@array = $sth2->fetchrow_array)
+ − 44 {
+ − 45 print "Email this\n---\n$mailtxt\n---\nTo this person @array[0]\n";
+ − 46 }
+ − 47
+ − 48 $sth2->finish || bad_exit("Couldn\'t finish for ppl list - $sth->errstr");
+ − 49 }
+ − 50
+ − 51 $sth1->finish || bad_exit("Couldn\'t finish for mailtxt - $sth->errstr");
+ − 52 }
+ − 53
+ − 54 sub bad_exit
+ − 55 {
+ − 56 print "The following error occured - $_[0]";
+ − 57
+ − 58 exit(0);
+ − 59 }