#!/usr/bin/perl -w use CGI qw/:all/; use CGI::Carp qw(fatalsToBrowser); use DBI; #my $dbh=DBI->connect('dbi:mysql:parc_demog:localhost:3306','root','root') or die "Unable to connect: $DBI::errstr\n"; my $dbh=DBI->connect('dbi:mysql:parc_demog:localhost','parcdemog','parc9904')or die "Couldn't connect to database: " . DBI->errstr; my $shID= url_param('shID'); my $cityName=url_param('cityName'); my $hasMailed= url_param('hasMailed'); my $hasVisited= url_param('hasVisited'); my $locCode=url_param('locCode'); my $conCode=url_param('conCode'); my $cityCode=url_param('cityCode'); if ($shID eq '') { $shID= "All"; } if ($cityName eq '') { $cityName= "All"; } if ($cityCode eq '') { $cityCode= "All"; } if ($hasMailed eq '') { $hasMailed= "All"; } if ($hasVisited eq '') { $hasVisited= "F"; } if ($locCode eq '') { $locCode= "All"; } if ($conCode eq '') { $conCode= "All"; } $strCity="select distinct cityCode,cityName from cf_location order by cityName"; $sthC=$dbh->prepare($strCity); $sthC->execute; #### $strCon="select distinct conCode,conName from cf_location"; $sthCon=$dbh->prepare($strCon); $sthCon->execute; ### $strLoc="select distinct locCode,locName from cf_location order by locName"; $sthL=$dbh->prepare($strLoc); $sthL->execute; ### $strS="select distinct ShID,fName,lName from shopper order by fName"; $sthS=$dbh->prepare($strS); $sthS->execute; ### ######Checking for City $strCond=""; if ($cityCode ne "All") { $strCond="l.cityCode='$cityCode'"; } #########Checking for Location if ($locCode ne "All") { if ($strCond ne "") { $strCond="$strCond and v.locCode='$locCode'"; } else { $strCond="v.locCode='$locCode'"; } } #########Checking for Mailed if ($hasMailed ne "All") { if ($strCond ne "") { $strCond="$strCond and hasMailed='$hasMailed'"; } else { $strCond="hasMailed='$hasMailed'"; } } #########Checking for Visited if ($hasVisited ne "All") { if ($strCond ne "") { $strCond="$strCond and hasVisited='$hasVisited'"; } else { $strCond="hasVisited='$hasVisited'"; } } #########Checking for Shopper if ($shID ne "All") { if ($strCond ne "") { $strCond="$strCond and v.shID='$shID'"; } else { $strCond="v.shID='$shID'"; } } ######## if ($conCode ne "All") { if ($strCond ne "") { $strCond="$strCond and l.conCode='$conCode'"; } else { $strCond="l.conCode='$conCode'"; } } ######### if ($strCond eq "") { $strVisit="SELECT planCode,visitCode,v.locCode,l.cityName,l.locName,l.typeName,visitDate,visitTime,s.ShID,fName,lName,gender,age,natName,hasMailed,hasVisited,email from cf_visitDetail v,cf_location l,shopper s,nationality n where v.shID=s.ShID and v.locCode=l.locCode and s.nationality=n.natCode order by cityName,locName"; } else { $strVisit="SELECT planCode,visitCode,v.locCode,l.cityName,l.locName,l.typeName,visitDate,visitTime,s.ShID,fName,lName,gender,age,natName,hasMailed,hasVisited,email from cf_visitDetail v,cf_location l,shopper s,nationality n where v.shID=s.ShID and v.locCode=l.locCode and s.nationality=n.natCode and $strCond order by cityName,locName"; } $sthV=$dbh->prepare($strVisit); $sthV->execute; $cnt=$sthV->rows; print < PAN ARAB RESEARCH CENETER - Mystery Shopping
 

PAN ARAB RESEARCH CENTER

Visit Allocation

Country: blk11 while (@City=$sthC->fetchrow_array()) { print ""; } print <  Location: blk3 while (@shop=$sthS->fetchrow_array()) { print ""; } print <  Mailed:  Visited: GO

blk4 my $i= 0; while ($visit=$sthV->fetchrow_hashref()) { $i = $i + 1; my $n1 = "pa".$i; #location Code my $n2 = "pb".$i; #City Name my $n3 = "pc".$i; #Location Name my $n4 = "pd".$i; #Store Type my $n5 = "pe".$i; #Plan Date my $n6 = "pf".$i; #Plan Time my $n7 = "pg".$i; #Shopper Name my $n8 = "ph".$i; #Gender my $n9 = "pi".$i; #Age my $n10 = "pj".$i; #Nationality my $n11 = "pk".$i; #Mailed my $n12 = "pl".$i; #Visited my $n13 = "pm".$i; #send my $n14 = "pn".$i; #Plan Code my $n15 = "po".$i; #visit Code my $n16 = "pp".$i; #Email my $planCode ="$visit->{'planCode'}"; my $code ="$visit->{'locCode'}"; my $city ="$visit->{'cityName'}"; my $location ="$visit->{'locName'}"; my $type ="$visit->{'typeName'}"; my $pdate ="$visit->{'visitDate'}"; my $planTime ="$visit->{'visitTime'}"; my $email ="$visit->{'email'}"; my $shopperName ="$visit->{'fName'}"." "."$visit->{'lName'}"; if("$visit->{'gender'}" == 1){$gender="Male"}else{$gender="Female"}; my $age ="$visit->{'age'}"; my $nationality ="$visit->{'natName'}"; my $mailed ="$visit->{'hasMailed'}"; my $visited ="$visit->{'hasVisited'}"; my $visitCode ="$visit->{'visitCode'}"; $mod=$i%2; if($mod == 0) { $col='#D7DADE'; } else { $col='#FFFFFF'; } print < blk6 } print <
planCode Code City Location Store Type Date Time Name Gender Age Nationality Mailed Visited Send
$city $type $shopperName $gender $age $nationality $mailed $visited blk5 #if($mailed eq "T") #{ #print ""; #} #else #{ print ""; #} print <
 
  
blk7