###   Rewrite of reminder
###   added dbi & dbd::odbc modules for debugging
###   added data:dumper also for debugging
###   added tracelevel and trace.txt for debugging

use OLE;
use DBI;
use DBD::ODBC;
	use Mail::Sender;
my $trace_level=1;
my $trace_file='tracefile.txt';
DBI->trace($trace_level,$trace_file);

#### lnitialization of Global Variables & Arrays ####
	my $debug =0;
	my $t = time;
	(my $sec, my $min, my $hour, my $mday, my $mon, my $year, my $wday, my $yday, my $isdst)=localtime($t);
	$year = $year + "1900";
	my $SQLServer = "servername";
	my $DB = "dbname";
	my $SQLUname = "username";
	my $SQLpass="userpass";
       
	my $query="SELECT ClassSchedule.EventDate AS EventDate, 
				DATEDIFF(dd, { fn NOW() }, ClassSchedule.EventDate) AS Until, 
				DATEDIFF(dd, ClassRoster.Enrolled, ClassSchedule.EventDate) AS Since, 
				Classes.Duration AS Duration, 
				Classes.Topic AS Topic, 
				Classes.Description AS Description, 
				ClassLocation.Location AS Location, 
				ClassLocation.Address AS Address, 
				ClassLocation.Directions AS Directions, 
				ClassLocation.Map AS Map, 
				ClassRoster.idEnrollee AS idEnrollee,
				ClassRoster.System AS System,
				ClassRoster.Account AS Account,
				ClassRoster.PublicID AS PublicID,
				ClassRoster.Name AS Name,
				ClassRoster.Email AS emailAddress
			FROM ClassSchedule 
				INNER JOIN
					Classes ON ClassSchedule.id_Class = Classes.idClass 
				INNER JOIN
					ClassRoster ON ClassSchedule.idEvent = ClassRoster.id_Event
				INNER JOIN
					ClassLocation ON ClassSchedule.id_Location = ClassLocation.idLocation
				WHERE (ClassSchedule.EventDate > { fn NOW() }) AND 
		            (ClassRoster.Cancelled IS NULL) AND 
		            (ClassRoster.Reminded IS NULL) AND 
		            (DATEDIFF(dd, ClassRoster.Enrolled, ClassSchedule.EventDate) > 7) AND 
		            (NOT (DATEPART(dw, { fn NOW() }) IN (1, 7))) AND 
		            (DATEDIFF(dd, { fn NOW() }, ClassSchedule.EventDate) < 7)";

	# Connect to the database
# See footnote 1

my $dbh = DBI->connect("dbi:ODBC:driver=SQL Server;Server=VASVCSSQL;database=MLXhelp;uid=$SQLUname;pwd=$SQLpass;") or die  "Couldn't open database: $DBI::errstr; stopped";
print "connected finally";

# Prepare the SQL query for execution
my $sth=$dbh->prepare($query);
$sth->execute() or die "Couldn't execute statement: $DBI::errstr; stopped";

$sth->finish();


$dbh->disconnect();