#!/usr/bin/perl -w use CGI qw/:all/; use CGI::Carp qw(fatalsToBrowser); use warnings; use DBI; my $dbh=DBI->connect('dbi:mysql:parc_demog:localhost','parcdemog','parc9904')or die "Couldn't connect to database: " . DBI->errstr; use Date::Calc qw(:all); #($year, $month, $day) = Add_Delta_Days(1985,11,4,60); ($year, $month, $day) = Today(); $emailFrom="parcdxb\@emirates.net.ae"; $strV="select year( visitDate ) as year1,MONTH (visitDate) as month1, DAY(visitDate) as day1,hasVisited,v.locCode,l.locName,visitDate,visitTime,fName,email,visitCode,planCode from cf_visitDetail v, cf_location l, shopper s where v.shID=s.ShID and v.locCode=l.locCode and hasMailed='T' and hasVisited='F'"; $sthVisit=$dbh->prepare($strV); $sthVisit->execute; $intCntVisit=0; $intCntEnter=0; $intCntDeactivate=0; print < blk1 while ($sthV=$sthVisit->fetchrow_hashref()) { $year1="$sthV->{'year1'}"; $month1="$sthV->{'month1'}"; $day1="$sthV->{'day1'}"; $Dd = Delta_Days($year,$month,$day,$year1,$month1,$day1); if ($Dd ==1) { ##Alert to Go for shopping on Tommorow $emailTo="$sthV->{'email'}"; $fName="$sthV->{'fName'}"; $visitDate="$sthV->{'visitDate'}"; $visitTime="$sthV->{'visitTime'}"; $locName="$sthV->{'locName'}"; $comments="Dear Mr/Mrs.$fName, \nKindly note that, you have been already assigned with a Mystery Shopping in CARREFOUR $locName on $visitDate at $visitTime.\n\n\nNOTE:-IF YOU ARE NOT ABLE TO DO THE VISIT, PLEASE DO INFORM US.\n\n"; ######### # Email the form results $subject="Your Mystery Shopping in $locName is due on $visitDate at $visitTime"; $sendmailpath="/usr/sbin/sendmail"; open (sil, "| $sendmailpath -t"); print sil "Subject: $subject\n"; print sil "From: $emailFrom\n"; print sil "To: $emailTo\n"; print sil "$comments\n\n"; print sil "Thanks"; $intCntVisit=$intCntVisit+1; close (sil)|| print("unable to send $!"); ######### } elsif ($Dd ==0) { ##Alert to Go for shopping on Tommorow $emailTo="$sthV->{'email'}"; $fName="$sthV->{'fName'}"; $visitDate="$sthV->{'visitDate'}"; $visitTime="$sthV->{'visitTime'}"; $locName="$sthV->{'locName'}"; $comments="Dear Mr/Mrs.$fName, \nKindly note that, you have been already assigned with a Mystery Shopping in CARREFOUR $locName on $visitDate at $visitTime.\n\n\nNOTE:-IF YOU ARE NOT ABLE TO DO THE VISIT, PLEASE DO INFORM US.\n\n"; ######### # Email the form results $subject="Your Mystery Shopping in $locName is due TODAY at $visitTime"; $sendmailpath="/usr/sbin/sendmail"; open (sil, "| $sendmailpath -t"); print sil "Subject: $subject\n"; print sil "From: $emailFrom\n"; print sil "To: $emailTo\n"; print sil "$comments\n\n"; print sil "Thanks"; $intCntVisit=$intCntVisit+1; close (sil)|| print("unable to send $!"); ######### } elsif ($Dd ==-1) { ##Alert to Do Questionnaire Entry $emailTo="$sthV->{'email'}"; $fName="$sthV->{'fName'}"; $visitDate="$sthV->{'visitDate'}"; $visitTime="$sthV->{'visitTime'}"; $locName="$sthV->{'locName'}"; $comments="Dear Mr/Mrs.$fName, \nThis is to REMIND you that, our system will automatically disable your opportunity to fill the Online Questionniare for the $locName by today. \n\n\nNOTE:-IF YOU WERE NOT ABLE TO DO THE VISIT, PLEASE DO INFORM US.\n\n"; ######### # Email the form results $subject="Your opportunity to fill the QUESTIONNAIRE for $locName will be closed by Tommorow"; $sendmailpath="/usr/sbin/sendmail"; open (sil, "| $sendmailpath -t"); print sil "Subject: $subject\n"; print sil "From: $emailFrom\n"; print sil "To: $emailTo\n"; print sil "$comments\n\n"; print sil "Thanks"; $intCntEnter=$intCntEnter+1; close (sil)|| print("unable to send $!"); } elsif ($Dd >=-2) { ##Deactivation of Link $visitCode="$sthV->{'visitCode'}"; $planCode="$sthV->{'planCode'}"; $strsql="UPDATE cf_visitDetail SET hasMailed='F' where visitCode='$visitCode'"; $rv=$dbh->do($strsql); print "$strsql"; $strsql1="UPDATE cf_visitPlan SET hasMailed='F' where planCode='$planCode'"; print "$strsql1"; $rv1=$dbh->do($strsql1); $intCntDeactivate=$intCntDeactivate+1; } } $dbh->disconnect; print < blk2