#!/usr/local/bin/perl

use DBI;

$user	= "";
$passwd	= "";
$dbname	= "scs";

MAIN:
{
    # Work out the date 7 days ahead
    ($sec, $min, $hour, $mday, $mon, $year, $wday, $yday, $isdst) = localtime(time + (24 * 60 * 60 * 7));

    $mon += 1; #Months start from 0!!

    $date = "$year-$mon-$mday";

# Connect to the Database
    $dbh = DBI->connect("dbi:Pg:dbname=$dbname", $user, $passwd) || bad_exit("Couldn\'t open DB - $sth->errstr");
    
# Prepare the select statement
    $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");

# Execute it
    $numrows1 = $sth1->execute || bad_exit("Execute for description failed - $sth1->errstr");

    while (@array = $sth1->fetchrow_array) {
		$eventid = @array[0];
		$name = @array[1];
		$text = @array[2];
		$when = @array[3];
		$length = @array[4];
		$cost = @array[5];
		$contact = @array[6];

		$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);

# Prepare the select statement
		$sth2 = $dbh->prepare("SELECT mailto(\'$eventid\')") || bad_exit("Couldn\'t prepare ppl list - $sth->errstr");

		$numrows2 = $sth2->execute || bad_exit("Couldn\'t execute ppl list - $sth->errstr");
		
		while (@array = $sth2->fetchrow_array) 
		{
			print "Email this\n---\n$mailtxt\n---\nTo this person @array[0]\n";
		}

		$sth2->finish || bad_exit("Couldn\'t finish for ppl list - $sth->errstr");
	}

	$sth1->finish || bad_exit("Couldn\'t finish for mailtxt - $sth->errstr");
}

sub bad_exit
{
    print "The following error occured - $_[0]";
    
    exit(0);
}