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

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>

<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />

<title>PAN ARAB RESEARCH CENETER - Mystery Shopping</title>
<link href="allocate.css" rel="stylesheet" type="text/css" />

<script type="text/javascript">
function plcCityName()
{
	frm=document.frmMain;
	frm.cityCode.value="$cityCode";
	frm.locCode.value="$locCode";
    frm.shID.value="$shID";
	frm.mailed.value="$hasMailed";
    frm.visited.value="$hasVisited";
	frm.conCode.value="$conCode";
    
	document.getElementById("msg").style.display="none";
	document.getElementById("tbl").style.display="";
	if("$cnt"==0)
	{
		document.getElementById("msg").style.display="";
		document.getElementById("tbl").style.display="none";
	}
}

function dosave()
{
    document.frmMain.method="post";
    document.frmMain.action="http://www.arabdemog.com/cf/visits/sendMail.pl"
	document.frmMain.submit();
    return true;
}
function dolink()
{
    document.frmMain.method="post";
    document.frmMain.action="http://www.arabdemog.com/cf/visits/makeLog.pl";
	document.frmMain.submit();
    return true;
}
function selfpost()
{
	var cityCode = document.getElementById('cityCode').value;
	var shID = document.getElementById('shID').value;
	var locCode = document.getElementById('locCode').value;
	var hasMailed = document.getElementById('mailed').value;
	var hasVisited = document.getElementById('visited').value;
	var conCode = document.getElementById('conCode').value;

	var path="http://www.arabdemog.com/cf/visits/visitAllocate.pl?shID=" + shID + "&cityCode="+cityCode + "&locCode="+locCode + "&hasMailed="+ hasMailed + "&hasVisited="+ hasVisited + "&conCode=" + conCode;
	//var path="http://www.arabdemog.com/cf/visits/visitAllocate.pl?shID=" + shID + "&cityName="+cityName + "&locCode="+locCode + "&hasMailed="+ hasMailed + "&hasVisited="+ hasVisited + "&conCode=" + conCode;
	
	lnk=encodeURI(path);
	document.frmMain.method="post";
    document.frmMain.action=lnk;
	document.frmMain.submit();
    return true;
}
</script>
</head>

<body style="background-color: #666666;color: inherit;" onload="plcCityName();">
<table class="TBL">
<tr>
<td style="width:2%;background-color:#666666">&nbsp;</td>


<td style="width:96%;background-color: #FFFFFF">
<div style='height:70px;padding-top: 15px;padding-bottom: 5px;line-height: 3px;'><h1 style='padding-left: 30px;color: #607291;padding-right: 30px;font-size:1.5em;'>PAN ARAB RESEARCH CENTER</h1>
<p style='padding-left: 30px;color: #607291;padding-right: 30px;font-size:1.2em;'>Visit Allocation</p>
</div>
<div id="navbar">&nbsp; </div>
<div id="content" >
<form method="post" name="frmMain">

<div id="srch" style="width:100%;border:0px solid black;vertical-align:middle;padding-right:1px;padding-left:1px;"><font size='1.5em'color='#000000'><p align="center">

<b>Country:</b>
<select name="conCode" id="conCode"  style="width:80px;border:1px solid #666666">
                    <option value="All">All</option>
blk1
    while (@con=$sthCon->fetchrow_array())
    {
        
        print "<option value=$con[0]>$con[1]</option>";
    }
print <<blk11;
</select>
&nbsp;<b>City:</b>
<select name="cityCode" id="cityCode"  style="width:120px;border:1px solid #666666">
                    <option value="All">All</option>
blk11
    while (@City=$sthC->fetchrow_array())
    {
        
        print "<option value=$City[0]>$City[1]</option>";
    }
print <<blk2;
</select>
&nbsp;<b>Location:</b>
<select name="locCode" id="locCode"  style="width:150px;border:1px solid #666666">
                    <option value="All">All</option>
blk2
 while (@loc=$sthL->fetchrow_array())
    {
        
        print "<option value=$loc[0]>$loc[1]</option>";
    }
print <<blk3;
</select>
&nbsp;<b>Shopper:</b>
<select name="shID" id="shID"  style="width:140px;border:1px solid #666666">
                    <option value="All">All</option>
blk3
 while (@shop=$sthS->fetchrow_array())
    {
        
        print "<option value=$shop[0]>$shop[1] $shop[2]</option>";
    }
print <<blk4;
</select>
&nbsp;<b>Mailed:</b>
<select name="mailed" id="mailed"  style="width:50px;border:1px solid #666666">
                    <option value="All">All</option>
					<option value="T">True</option>
					<option value="F">False</option>
</select>
&nbsp;<b>Visited:</b>
<select name="visited" id="visited"  style="width:50px;border:1px solid #666666">
                    <option value="All">All</option>
					<option value="T">True</option>
					<option value="F">False</option>
</select>
<a href="#" style="text-decoration: none;" onclick="selfpost();"><img src="go.png" style='vertical-align:middle;' alt="GO" height="25" border="0" width="25"/> </a>
</p>
</font>
</div>
<div id="msg" style="display:none;width:100%"><font size='3'color='#000000'><p align="center"> No Records to Display</p></font></div>

<font size='2'color='#000000'>
<table width="100%" align='center' class="Mtable" id="tbl">
<tr height='25' bgcolor='#97a8bb'>

<td  style="display:none">planCode</td>
<td  style="display:none"> Code</td>
<td  class="Mtd" width="5%" style="font-size:0.86em;padding-left: 2px;"><b>City</b></td>
<td class="Mtd" width="14%" style="font-size:0.86em;padding-left: 2px;"><b>Location</b></td>
<td class="Mtd" width="10%" style="font-size:0.86em;padding-left: 2px;"><b>Store Type</b></td>
<td class="Mtd" width="8%" style="font-size:0.86em;padding-left: 2px;"><b>Date</b></td>
<td class="Mtd" width="9%" style="font-size:0.86em;padding-left: 2px;"><b>Time</b></td>
<td class="Mtd" width="17%" style="font-size:0.86em;padding-left: 2px;"><b>Name</b></td>
<td class="Mtd" width="6%" style="font-size:0.86em;padding-left: 2px;"><b>Gender</b></td>
<td class="Mtd" width="4%" style="font-size:0.86em;padding-left: 2px;"><b>Age</b></td>
<td class="Mtd" width="12%" style="font-size:0.86em;padding-left: 2px;"><b>Nationality</b></td>
<td class="Mtd" width="5%" style="font-size:0.86em;padding-left: 2px;"><b>Mailed</b></td>
<td class="Mtd" width="6%" style="font-size:0.86em;padding-left: 2px;"><b>Visited</b></td>
<td class="Mtd" width="4%" style="font-size:0.86em;padding-left: 2px;"><b>Send</b></td>

</tr>
<!-- while loop to populate rows -->

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 <<blk5;


<tr height='25' valign='center' bgcolor='$col'>
<font color='#000000' size='0.8em' face="Verdana, Arial, Helvetica, sans-serif"><b>

<tdclass="Mtd" style="display:none"><input type="hidden"  value="$planCode" name="$n14" id="$n14" style="border: 0px;solid #000000;background-color:#5f7287;width:0px;" readonly/></td>
<td class="Mtd" style="display:none"> <input type="hidden"  value="$code" name="$n1" id="$n1" style="border: 0px;solid #000000;background-color:#5f7287;" readonly/>
	                                  <input type="hidden"  value="$visitCode" name="$n15" id="$n15" style="border: 0px;solid #000000;background-color:#5f7287;" readonly/> 
                                      <input type="hidden"  value="$email" name="$n16" id="$n16" style="border: 0px;solid #000000;background-color:#5f7287;" readonly/> 
</td>
<td class="Mtd">
	$city
<!-- <input type="text" value="$city" name="$n2" id="$n2"  style="border: 0px;solid #000000;background-color:$col;width:40px;color:#000000;font-weight:bold;font-size:0.85em;margin-left : 1px;" readonly/> -->
</td>

<td class="Mtd">
<input type="text" value="$location" name="$n3" id="$n3"  style="border: 0px;solid #000000;background-color:$col;width:125px;color:#000000;font-weight:bold;font-size:1.2em;margin-left : 1px;" readonly />
</td>

<td class="Mtd">
$type
<!-- <input type="text" value="$type" name="$n4" id="$n4"  style="border: 0px;solid #000000;background-color:$col;width:100px;color:#000000;font-weight:bold; font-size:3.0em;margin-left : 1px;" readonly /> -->
</td>

<td class="Mtd">
<input type="text" value="$pdate" name="$n5" id="$n5"  style="border: 0px;solid #000000;background-color:$col;width:70px;color:#000000;font-weight:bold;font-size:1.1em;margin-left : 1px;" readonly />
</td>

<td class="Mtd">
<input type="text" value="$planTime" name="$n6" id="$n6" style="border: 0px;solid #000000;background-color:$col;width:100px;color:#000000;font-weight:bold;font-size:1.1em;margin-left : 1px;" readonly />
</td>

<td class="Mtd">
$shopperName
<!-- <input type="text" value="$shopperName" name="$n7" id="$n7" style="border: 0px;solid #000000;background-color:$col;width:150px;color:#000000;font-weight:bold;font-size:0.85em;margin-left : 1px;" readonly  /> -->
</td>

<td class="Mtd">
$gender
<!--<input type="text" value="$gender" name="$n8" id="$n8" style="border: 0px;solid #000000;background-color:$col;width:50px;color:#000000;font-weight:bold; font-size:0.85em;margin-left : 1px;" readonly />-->
</td>
<td class="Mtd">
$age
<!-- <input type="text" value="$age" name="$n9" id="$n9" style="border: 0px;solid #000000;background-color:$col;width:20px;color:#000000;font-weight:bold; font-size:0.85em;margin-left : 1px;"readonly  /> -->
</td>
<td class="Mtd">
	$nationality
<!-- <input type="text" value="$nationality" name="$n10" id="$n10" style="border: 0px;solid #000000;background-color:$col;width:100px;color:#000000;font-weight:bold; font-size:0.85em;margin-left : 1px;" readonly /> -->
</td>
<td class="Mtd" align='center'>
$mailed
<!-- <input type="text" value="$mailed" name="$n11" id="$n11" style="border: 0px;solid #000000;background-color:$col;width:20px;color:#000000;font-weight:bold;font-size:0.85em;margin-left : 1px;" readonly /> -->
</td>
<td class="Mtd">
$visited
<!-- <input type="text" value="$visited" name="$n12" id="$n12" style="border: 0px;solid #000000;background-color:$col;width:20px;color:#000000;font-weight:bold;font-size:0.85em;margin-left : 1px;" readonly /> -->
</td>
<td class="Mtd">

blk5
	#if($mailed eq "T")
	#{
	#print "<input type='text' value='$mailed' name='$n13' id='$n13' style='border: 0px;solid #000000;background-color:#5f7287;width:20px;color:#FFFFFF;font-size:0.85em;margin-left : 1px;' readonly />";
	#}
	#else
	#{
    print "<input type='checkbox' value='1' name='$n13' id='$n13' style='margin-left : 2px;'/>";
	#}
print <<blk6;
</b></font>
</tr>
blk6
	
	}
print <<blk7;

<!-- End while loop -->
<tr>
<input type="hidden" value="$i" name="cnt" id="cnt">
<td colspan="14" align="center">&nbsp;</td>
</tr>
<tr>
<td colspan="14" align="center"><input type="button" Value="Send" name="save" onclick="dosave();" />&nbsp;&nbsp;<input type="button" Value="Link" name="link" disabled=true onclick="dolink();" /></td>
</tr>
</table>
</font>





</form>
</div>

<div id="footer">
<p class="footerpmiddle"><br /></p>
</div>	

</td>



<td style="width:2%;background-color:#666666"></td>
</tr>
</table>

</body>
</html>
blk7
