###   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();


##</code><code>##

 DBI 1.48-ithread default trace level set to 0x0/1 (pid 5748)
    -> DBI->connect(dbi:ODBC:driver=SQL Server;Server=VASVCSSQL;database=MLXhelp;uid=Frank;pwd=fu3go;, , ****)
    -> DBI->install_driver(ODBC) for MSWin32 perl=5.006001 pid=5748 ruid=0 euid=0
       install_driver: DBD::ODBC version 1.13 loaded from C:/Perl/site/lib/DBD/ODBC.pm
    <- install_driver= DBI::dr=HASH(0x2b1bf3c)
    <- default_user(undef undef ...)= ( undef undef ) [2 items] at DBI.pm line 580
    <- connect('driver=SQL Server;Server=servername;database=dbname;uid=username;pwd=userpass;' undef ...)= DBI::db=HASH(0x2e8b33c) at DBI.pm line 598
    <- STORE('PrintError' 1)= 1 at DBI.pm line 645
    <- STORE('AutoCommit' 1)= 1 at DBI.pm line 645
    <- STORE('Username' undef)= 1 at DBI.pm line 648
    <> FETCH('Username')= undef ('Username' from cache) at DBI.pm line 648
    <- connect= DBI::db=HASH(0x2e8b33c)
    <- STORE('dbi_connect_closure' CODE(0x2e8b294))= 1 at DBI.pm line 668
    <- prepare('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.Dire...')= DBI::st=HASH(0x2e8b9d4) at test2.pl line 61
dbd_describe failed, dbd_st_execute #2...!
    <- execute= -1 at test2.pl line 62
    <- finish= 1 at test2.pl line 64
    <- disconnect= 1 at test2.pl line 67
    <- DESTROY(DBI::st=HASH(02E8B9D4))= undef
    <- DESTROY(DBI::db=HASH(02E8B33C))= undef
    <- disconnect_all= '' at DBI.pm line 677
!   <- DESTROY(DBI::dr=HASH(02B1BF3C))= undef during global destruction