####
#### Filename: reminder.pl
#### Version: .1.0
#### Written By: Matthew Lukaszewicz
#### Written Date: 03/22/06
#### Last Change Date: 12/19/06 by Francisco Oquendo
#### Comments: 03/22/06 This had to be completly re-written due to the excahnge server change
#### Comments: 03/27/06 Fixed logging to include date/timestamp and seperate days with newlines
#### Comments: 10/28/08 Updated From Email address to generic training@marketlinx.com due to exchange changes,added ### myself for debugging purposes.
#### 12/19/06 Removed all references to Matt's IDs
####

#### Modules ####
	use OLE;
	use Mail::Sender;

#### 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="pass";

#### Get List of Users to Remind ####
	open(LOGFILE,">>Reminder.log");
	print LOGFILE "\n\n".localtime($t)."\n";
	
	my $Conn = CreateObject OLE "ADODB.Connection";
	$Conn->Open("PROVIDER=SQLOLEDB;DATA SOURCE=$SQLServer;UID=$SQLUname;PWD=$SQLpass;DATABASE=$DB");
	my $RS_UsersToRemind = CreateObject OLE "ADODB.Recordset";
	my $RS_UserUpdate = CreateObject OLE "ADODB.Recordset";
	
    if ($Conn)
	{
		my $SQL_UsersToRemind = "
			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)";
					
		$RS_UsersToRemind->Open($SQL_UsersToRemind, $Conn);
		if (!defined href $RS_UsersToRemind){
		 ref $RS_UsersToRemind={};
		print "this is defined ";
		}
		if ($RS_UsersToRemind)
		{
			while(!$RS_UsersToRemind->EOF())
			{
				my $SQL_UserUpdate = "update ClassRoster set Reminded={ fn NOW() } where idEnrollee = '".$RS_UsersToRemind->Fields('idEnrollee')->{Value}."';";
			    my $sender = new Mail::Sender
			    {
			        smtp => 'vaexch001.firstamericanmls.com', 
			        from => 'training@marketlinx.com'
			    };
			    (
			        ref ($sender->MailMsg( 
							{
			            to => "".$RS_UsersToRemind->Fields('emailAddress')->{Value}."",
								bcc => "somuser\@somewhere.com",
								subject => "REMINDER: ".$RS_UsersToRemind->Fields('Topic')->{Value}."",
								msg => 
"This message is to remind you that you are registered to attend ".$RS_UsersToRemind->Fields('Topic')->{Value}.". 

Date/Time: ".$RS_UsersToRemind->Fields('EventDate')->{Value}."

Location: ".$RS_UsersToRemind->Fields('Location')->{Value}."

Address : ".$RS_UsersToRemind->Fields('Address')->{Value}."

Seating is limited, so if you no longer plan to attend the class, please cancel on www.MLXhelp.com",
								auth => 'LOGIN',
								authid => 'username',
								authpwd => 'password',
	##This field was missing which caused the problem!						})
						) and print LOGFILE "Mail sent to ".$RS_UsersToRemind->Fields('emailAddress')->{Value}." OK.\n" ) or die "$Mail::Sender::Error\n";
				$Conn->Execute($SQL_UserUpdate);	
				print LOGFILE "Updated ".$RS_UsersToRemind->Fields('System')->{Value}.",".$RS_UsersToRemind->Fields('Account')->{Value}.",".$RS_UsersToRemind->Fields('PublicID')->{Value}.",".$RS_UsersToRemind->Fields('Name')->{Value}.",".$RS_UsersToRemind->Fields('emailAddress')->{Value}." for ".$RS_UsersToRemind->Fields('EventDate')->{Value}.",".$RS_UsersToRemind->Fields('Topic')->{Value}.",".$RS_UsersToRemind->Fields('Location')->{Value}." OK.\n" or die "Unable to Update\n";
				$RS_UsersToRemind->MoveNext();
			}
		$RS_UsersToRemind->Close();
		}
	$Conn->Close;
    close(LOGFILE);
	}
	}