|
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 }
|
