#!/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;
content-type: text/html

<html>
<head>
</head>
<body>
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;
Count of Visit Alert is $intCntVisit\n;
Count of Entry Alert is $intCntEnter\n;
Count of Deactivation is $intCntDeactivate\n;
</body>
</html>
blk2
