#### #### Filename: reminder.pl #### Version: .1.0 #### Written #### Written Date: 03/22/06 #### Last Change #### Comments: 03/22/06 This had to be completly re-written #### Comments: 03/27/06 Fixed logging to include date/timestamp and se +perate days with newlines #### Comments: 10/28/08 Updated From Email address to generic address +to exchange changes,added ### myself for debugging purposes. #### 12/19/06 Removed all references to old IDs #### #### Modules #### use OLE; use Mail::Sender; use Data::Dumper; #### 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"; ### changed for security.. 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=$SQLUnam +e;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.Event +Date) 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.idClas +s INNER JOIN ClassRoster ON ClassSchedule.idEvent = ClassRoster +.id_Event INNER JOIN ClassLocation ON ClassSchedule.id_Location = Class +Location.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.EventDat +e) < 7)"; $RS_UsersToRemind->Open($SQL_UsersToRemind, $Conn); local $Data::Dumper::Indent = 1; local $Data::Dumper::Deepcopy = 1; local $Data::Dumper::Sortkeys = 1; warn Dumper( $RS_UsersToRemind->Fields( 'idEnrollee' )); if ($RS_UsersToRemind) { while(!$RS_UsersToRemind->EOF()) { print "$RS_UsersToRemind"; my $SQL_UserUpdate = "update ClassRoster set Reminded= +{ fn NOW() } where idEnrollee = '".$RS_UsersToRemind->Fields('idEnrol +lee')->{Value}."';"; my $sender = new Mail::Sender { smtp => 'vaexch001.firstamericanmls.com', from => 'training@marketlinx.com' }; ( ref ($sender->MailMsg( { to => "".$RS_UsersToRemind->Fields('emailAddre +ss')->{Value}."", bcc => "training\@marketlinx.com, fran +cisco.oquendo\@firstamericanmls.com,medwards\@marketlinx.com", subject => "REMINDER: ".$RS_UsersToRem +ind->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, plea +se cancel on www.MLXhelp.com", auth => 'LOGIN', authid => 'FAMLS\\foquendo', authpwd => '', }) ) and print LOGFILE "Mail sent to ".$RS_UsersT +oRemind->Fields('emailAddress')->{Value}." OK.\n" ) or die "$Mail::Se +nder::Error\n"; $Conn->Execute($SQL_UserUpdate); print LOGFILE "Updated ".$RS_UsersToRemind->Fields('Sy +stem')->{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); }
In reply to Re^3: undefined value as a hash reference?
by grashoper
in thread undefined value as a hash reference?
by grashoper
| For: | Use: | ||
| & | & | ||
| < | < | ||
| > | > | ||
| [ | [ | ||
| ] | ] |