<?php

require "showshp.php";

session_start();
if(!isset($_SESSION['titles']) || !isset($_SESSION['positions'])|| !isset($_SESSION['usernames'])){
header("location:index.php");
}
?>


<html>
<head>

<link rel="stylesheet" type="text/css" href="rdsty.css">
<link rel="stylesheet" type="text/css" media="all" href="jsDatePick_ltr.min.css" />
<link rel="stylesheet" type="text/css" media="all" href="jsDatePick_ltr.css" />
<script type="text/javascript" src="jsDatePick.min.1.3.js"></script>
<script src="datesps.js"></script>

</head>


<body style="font-family:calibri;font-size:small" onload="showsrw('dtvs')">
<div class="hdn"></div>

<?php
printf("<div class='ses'>%s (%s)</div>",$_SESSION['titles'],$_SESSION['positions']);
?>

<div class="tops" align="center">RD statement</div>
<div class="homes"><a href="index.php" style='color:#A9BCF5;'>Sign out</a></div>

</body>

</html>

<?php


$starts=$_REQUEST['starts'];
$ends=$_REQUEST['ends'];

if(isset($_REQUEST['zones'])){
$zones=$_REQUEST['zones'];}
else{
$zones="All";}

if(isset($_REQUEST['regions'])){
$regions=$_REQUEST['regions'];}
else{
$regions="All";}

if(isset($_REQUEST['territorys'])){
$territorys=$_REQUEST['territorys'];}
else{
$territorys="All";}

if(isset($_REQUEST['agents'])){
$agents=$_REQUEST['agents'];}
else{
$agents="All";}

if(isset($_REQUEST['rsms'])){
$rsms=$_REQUEST['rsms'];}
else{
$rsms="All";}

if(isset($_REQUEST['executives'])){
$executives=$_REQUEST['executives'];}
else{
$executives="All";}

if(isset($_REQUEST['reps'])){
$reps=$_REQUEST['reps'];}
else{
$reps="All";}

if(isset($_REQUEST['ranges'])){
$ranges=$_REQUEST['ranges'];}
else{
$ranges="All";}

if(isset($_REQUEST['subranges'])){
$subranges=$_REQUEST['subranges'];}
else{
$subranges="All";}

if(isset($_REQUEST['items'])){
$items=$_REQUEST['items'];}
else{
$items="All";}


$months=date('m');
$days=date('t');
$years=date('Y');


if($starts==0 || $starts==''){
$starts=date("Y-m-01");
$tdates=date('Y-m-d');}
else{
$tdates=$starts;}

$months=date_format(date_create($starts),'Y-m');
$starts=$months."-01";
$ends=date($months.'-t');

showsrds();

printf("<div class='dts'><table style='font-size:14'><tr style='border-bottom:1px solid silver;'><td width=200><b><font color=#0066cc>Report details</font></td></tr</table>");
printf("<table><table style='font-size:13'>");
printf("<tr><td>Title:</td><td>RD statement</td></tr>");
printf("<tr><td>From:</td><td>%s</td></tr>",$starts);
printf("<tr><td>To:</td><td>%s</td></tr>",$tdates);

if(isset($_REQUEST['zones'])){
$zones=$_REQUEST['zones'];
printf("<tr><td>Zone:</td><td>%s</td></tr>",$zones);}
else{
$zones="All";}

if(isset($_REQUEST['regions'])){
$regions=$_REQUEST['regions'];
printf("<tr><td>Region:</td><td>%s</td></tr>",$regions);}
else{
$regions="All";}

if(isset($_REQUEST['territorys'])){
$territorys=$_REQUEST['territorys'];
printf("<tr><td>Territory:</td><td>%s</td></tr>",$territorys);}
else{
$territorys="All";}

if(isset($_REQUEST['agents'])){
$agents=$_REQUEST['agents'];
printf("<tr><td>Agent:</td><td>%s</td></tr>",$agents);}
else{
$agents="All";}

if(isset($_REQUEST['rsms'])){
$rsms=$_REQUEST['rsms'];
printf("<tr><td>RSM:</td><td>%s</td></tr>",$rsms);}
else{
$rsms="All";}

if(isset($_REQUEST['executives'])){
$executives=$_REQUEST['executives'];
printf("<tr><td>Executive:</td><td>%s</td></tr>",$executives);}
else{
$executives="All";}

if(isset($_REQUEST['reps'])){
$reps=$_REQUEST['reps'];
printf("<tr><td>Rep:</td><td>%s</td></tr>",$reps);}
else{
$reps="All";}

if(isset($_REQUEST['ranges'])){
$ranges=$_REQUEST['ranges'];
printf("<tr><td>Range:</td><td>%s</td></tr>",$ranges);}
else{
$ranges="All";}

if(isset($_REQUEST['subranges'])){
$subranges=$_REQUEST['subranges'];
printf("<tr><td>Sub-range:</td><td>%s</td></tr>",$subranges);}
else{
$subranges="All";}

if(isset($_REQUEST['items'])){
$items=$_REQUEST['items'];
printf("<tr><td>Item:</td><td>%s</td></tr>",$items);}
else{
$items="All";}


printf("</table></div>");


printf("<div class='srg'></div>");
printf("<div class='mains' style='left:15%%;'>");

$conn=mysqli_connect("localhost","primarysales","S5L7lF01EQZvQrGK","primarysales_diana");
$seflt=" where agent_names is not null ";
$agflt=" where agent_name is not null ";

$sesqlst="select * from acs where user_names='".$_SESSION['usernames']."'";
$seqry=mysqli_query($conn,$sesqlst);

while($serst=mysqli_fetch_row($seqry)){
if(strtolower($serst[1])!='all'){
$seflt=$seflt." and zones='".$serst[1]."' ";
$agflt=$agflt." and zone='".$serst[1]."' ";}
if(strtolower($serst[2])!='all'){
$seflt=$seflt." and regions='".$serst[2]."' ";
$agflt=$agflt." and region='".$serst[2]."' ";}
if(strtolower($serst[3])!='all'){
$seflt=$seflt." and territory_names='".$serst[3]."' ";
$agflt=$agflt." and territory_name='".$serst[3]."' ";}
if(strtolower($serst[4])!='all'){
$seflt=$seflt." and agent_names='".$serst[4]."' ";
$agflt=$agflt." and agent_name='".$serst[4]."' ";}
if(strtolower($serst[5])!='all'){
$seflt=$seflt." and item_names='".$serst[5]."' ";}
if(strtolower($serst[6])!='all'){
$seflt=$seflt." and item_ranges='".$serst[6]."' ";}
if(strtolower($serst[7])!='all'){
$seflt=$seflt." and sub_ranges='".$serst[7]."' ";}
if(strtolower($serst[8])!='all'){
$seflt=$seflt." and executive_names='".$serst[8]."' ";}
}

$rpsty="actp=stgs&regions=".$regions."&agents=".urlencode($agents)."&territorys=".$territorys."&ranges=".$ranges."&zones=".$zones."&rsms=".$rsms."&executives=".$executives."&reps=".$reps
."&items=".$items."&subranges=".$subranges; 

printf("<form method='post' action='rdstatements.php?%s'>",$rpsty);
printf("<div class='fra'>Date</div><div class='frb'>
<input type='text' size=10 class='starts'  name='starts' value='%s' id='inputField'></div><div class='frc'>
<select id='params' name='agents'><option>%s</option><option>All</option>",$tdates,$agents);
$sqlst="select distinct agent_name from agentsmrd ".$agflt;

$qry=mysqli_query($conn,$sqlst);
while($rst=mysqli_fetch_row($qry)){
printf("<option>%s</option>",$rst[0]);}
printf("</select></div><div class='frg'><input type='submit' value='go' style='width:70'></div></form>");

if($_REQUEST['actp']=='stgs')
{

printf("<BR><BR>");

$flt=$seflt." and dates>='".$starts."' and dates<='".$tdates."' ";
if(strtolower($regions)!='all'){
$flt=$flt." and regions='".$regions."' ";}
if(strtolower($zones)!='all'){
$flt=$flt." and zones='".$zones."' ";}
if(strtolower($agents)!='all'){
$flt=$flt." and agent_names='".$agents."' ";}
if(strtolower($ranges)!='all'){
$flt=$flt." and item_ranges='".$ranges."' ";}
if(strtolower($items)!='all'){
$flt=$flt." and item_names='".$items."' ";}
if(strtolower($subranges)!='all'){
$flt=$flt." and sub_ranges='".$subranges."' ";}
if(strtolower($territorys)!='all'){
$flt=$flt." and territory_names='".$territorys."' ";}
if(strtolower($executives)!='all'){
$flt=$flt." and executive_names='".$executives."' ";}


$sqlst="select distinct rgs,tvls,avls,pvls from (select distinct item_ranges as rgs from itemsmrd) as sgs left join (select item_ranges as trgs,
sum(box_qty*box_price) as tvls from rc_targets ".$flt." group by item_ranges) 
as tgs on rgs=trgs left join (select item_ranges as args,sum(pcs_qty*pcs_price) as avls from rd_invoices ".$flt." group by item_ranges) as ags on rgs=args left join (select item_ranges as prgs,sum(box_qty*box_price) as pvls from rc_invoices ".$flt." group by item_ranges) as pgs on rgs=prgs";

#echo $sqlst;

$qry=mysqli_query($conn,$sqlst);

while($rst=mysqli_fetch_row($qry)){
$ttv=$ttv+$rst[1];
$tav=$tav+$rst[2];
$pav=$pav+$rst[3];
}

if($ttv==0){
$rdpcn=100;}
else{
$rdpcn=$tav*100/$ttv;
}	

if($ttv==0){
$prpcn=100;}
else{
$prpcn=$pav*100/$ttv;
}	


$str="<BR><BR><div style='font-size:18;color:#0066CC;font-weight:bold;'>".$agents."<BR>".number_format($rdpcn,2,'.',',')."% Achieved</div>";
$str=$str."<BR><table style='width:40%;font-size:13;'><tr><td><b>Summary - ".$tdates."</td><td align='right'><b>RD sales</td><td align='right'><b>Primary sales</td></tr><tr><td>Target sales</td>
<td align='right'>".number_format($ttv,0,'.',',')."</td><td align='right'>".number_format($ttv,0,'.',',')."</td></tr><tr><td>Actual sales</td><td align='right'>".number_format($tav,0,'.',',')."</td><td align='right'>".number_format($pav,0,'.',',')."</td></tr><tr>
<td>Achievemnt %</td><td align='right'>".number_format($rdpcn,2,'.',',')."%</td><td align='right'>".number_format($prpcn,2,'.',',')."%</td></tr><tr><td>Variance</td><td align='right'>"
.number_format($ttv-$tav,0,'.',',')."</td><td align='right'>"
.number_format($ttv-$tav,0,'.',',')."</td></tr>";

$sqlst="select sum(scalls),sum(mcalls),sum(pcalls) from (select distinct dates,scalls,mcalls,pcalls from rd_invoices".$flt." ) as acs ";
$qry=mysqli_query($conn,$sqlst);
$rst=mysqli_fetch_row($qry);
$str=$str."<tr><td>Schedule calls</td><td align='right'>".$rst[0]."</td></tr>";
$str=$str."<tr><td>Made calls</td><td align='right'>".$rst[1]."</td></tr>";
$str=$str."<tr><td>Productive calls</td><td align='right'>".$rst[2]."</td></tr>";

$sqlst="select agent_names,rnk,@cn from (select agent_names,@cn:=@cn+1 as rnk from(select agent_names,@cn:=0,
sum(pcs_qty*pcs_price) as vl from rd_invoices where dates>='".$starts."' and dates<='".$tdates."' group by agent_names 
order by vl DESC) as sss)as ttt where agent_names='".$agents."' ";

#echo $sqlst;

$qry=mysqli_query($conn,$sqlst);
$rst=mysqli_fetch_row($qry);
$str=$str."<tr><td>Rank</td><td align='right'>".$rst[1]." out of ".$rst[2]."</td>";


$sqlst="select agent_names,rnk,@cn from (select agent_names,@cn:=@cn+1 as rnk from(select agent_names,@cn:=0,
sum(pcs_qty*pcs_price) as vl from rc_invoices where dates>='".$starts."' and dates<='".$tdates."' group by agent_names 
order by vl DESC) as sss)as ttt where agent_names='".$agents."' ";

#echo $sqlst;

$qry=mysqli_query($conn,$sqlst);
$rst=mysqli_fetch_row($qry);
$str=$str."<td align='right'>".$rst[1]." out of ".$rst[2]."</td></tr></table>";


$sqlst="select distinct rgs,tvls,avls,pvls from (select distinct item_ranges as rgs from itemsmrd where item_status='ACTIVE') as sgs left join (select item_ranges as trgs,
sum(box_qty*box_price) as tvls from rc_targets ".$flt." group by item_ranges) 
as tgs on rgs=trgs left join (select item_ranges as args,sum(pcs_qty*pcs_price) as avls from rd_invoices ".$flt." group by item_ranges) as ags on rgs=args left join (select item_ranges as prgs,sum(box_qty*box_price) as pvls from rc_invoices ".$flt." group by item_ranges) as pgs on rgs=prgs ";

$str=$str."<BR><BR><table style='width:75%;font-size:13;'><tr class='cls'><td colspan='5'>Range wise acheivement - ".$tdates."</td></tr>";
$str=$str."<tr class='cls' style='background:silver;text-align:right;'><td width=15%>Item ranges</td><td width=10%>Target sales</td><td width=10%>RD sales</td>
<td align='center' width=10%>%</td><td width=10%>Primary sales</td><td  align='center' width=10%>%</td><td width=12% style='color:grey;'>RD Variance</td><td width=14% style='color:grey;'>Primary Variance</td></tr>";

$ttv=0;
$tav=0;
$pav=0;

$qry=mysqli_query($conn,$sqlst);

while($rst=mysqli_fetch_row($qry)){

if($rst[1]==0){
$rdpcn=100;}
else{
$rdpcn=$rst[2]*100/$rst[1];}	
	
if($rst[1]==0){
$prpcn=100;}
else{
$prpcn=$rst[3]*100/$rst[1];}	


$str=$str."<tr class='cls'><td>".$rst[0]."</td><td align='right'>".number_format($rst[1],0,'.',',')."</td><td align='right'>".number_format($rst[2],0,'.',',').
"</td><td align='center'>".number_format($rdpcn,2,'.',',')."%</td><td align='right'>".number_format($rst[3],0,'.',',').
"</td><td align='center'>".number_format($prpcn,2,'.',',')."%</td><td align='right' style='color:silver;'>".number_format($rst[1]-$rst[2],0,'.',',')."</td><td align='right' style='color:silver;'>".number_format($rst[1]-$rst[3],0,'.',',')."</td></tr>";
$ttv=$ttv+$rst[1];
$tav=$tav+$rst[2];
$pav=$pav+$rst[3];
}

if($ttv==0){
$rdpcn=100;}
else{
$rdpcn=$tav*100/$ttv;}	

if($ttv==0){
$prpcn=100;}
else{
$prpcn=$pav*100/$ttv;}	


$str=$str."<tr class='cls' style='font-weight:bold;'><td>".Total."</td><td align='right'>".number_format($ttv,0,'.',',')."</td><td align='right'>".number_format($tav,0,'.',',').
"</td><td align='center'>".number_format($rdpcn,2,'.',',')."%</td><td align='right'>".number_format($pav,0,'.',',').
"</td><td align='center'>".number_format($prpcn,2,'.',',')."%</td><td align='right' style='color:silver;'>".number_format($ttv-$tav,0,'.',',')."</td><td align='right' style='color:silver;'>".number_format($ttv-$pav,0,'.',',')."</td></tr></table>";

$str=$str."<BR><BR></table><table style='width:25%;font-size:13'><tr class='cls'><td colspan='5'>Daily sales - ".date('Y-M')."</td></tr>";
$str=$str."<tr class='cls' style='background:silver;text-align:center;'><td>Date</td><td>Day sale</td><td>Total sale</td></tr>";

$sqlst="select dates,sum(pcs_qty*pcs_price) from rd_invoices where agent_names='".$agents."' and dates>='".$starts.
"' and dates<='".$tdates."' group by dates";
#echo $sqlst;

$qry=mysqli_query($conn,$sqlst);

$tav=0;
while($rst=mysqli_fetch_row($qry)){
$tav=$tav+$rst[1];
	
$str=$str."<tr class='cls'><td>".$rst[0]."</td><td align='right'>".number_format($rst[1],0,'.',',')."</td>
<td align='right'>".number_format($tav,0,'.',',');}

$str=$str."<tr class='cls' style='font-weight:bold;'><td>Total</td><td align='right'>".number_format($tav,0,'.',',')
."</td><td align='right'>".number_format($tav,0,'.',',')."</td></tr></table>";


$sqlst="select distinct rss,rgs,tqtys,aqtys,tvls,avls,pqtys from (select distinct item_name as rgs,item_ranges as rss from itemsmrd) as sgs left join 
(select item_names as trgs,sum(box_qty) as tqtys,sum(box_qty*box_price) as tvls 
from rc_targets ".$flt." group by item_names) 
as tgs on rgs=trgs left join (select item_names as args,sum(pcs_qty/packets_per_box) as aqtys,sum(pcs_qty*pcs_price) as avls from rd_invoices ".$flt.
" group by item_names) as ags on rgs=args left join (select item_names as prgs,sum(box_qty) as pqtys from rc_invoices ".$flt.
" group by item_names) as pgs on rgs=prgs order by  rss,avls DESC";

$qry=mysqli_query($conn,$sqlst);

$str=$str."<BR><BR><table style='width:95%;font-size:13'><tr class='cls'><td colspan='5'>Item wise acheivement - ".$tdates."</td></tr>";
$str=$str."<tr class='cls' style='background:silver;text-align:center;'><td>Range</td><td>Item</td><td>Target qty</td><td style='color:grey;'>Primary qty</td><td>RD qty</td><td>Target sales</td>
<td>Actual sales</td><td>Achievemnt %</td><td>Variance</td></tr>";

$ttv=0;
$tav=0;

$qry=mysqli_query($conn,$sqlst);

while($rst=mysqli_fetch_row($qry)){
if(($rst[2] +$rst[3])>0){
if($rst[4]==0){
$pcn=100;}
else{
$pcn=$rst[5]*100/$rst[4];}
	
$str=$str."<tr class='cls'><td>".$rst[0]."</td><td>".$rst[1]."</td><td align='right'>".
number_format($rst[2],0,'.',',')."</td><td align='right' style='color:silver;'>".number_format($rst[6],0,'.',',')."</td><td align='right'>".number_format($rst[3],0,'.',',').
"<td align='right'>".number_format($rst[4],0,'.',',')."</td><td align='right'>".number_format($rst[5],0,'.',',').
"</td><td align='center'>".number_format($pcn,2,'.',',')."%</td><td align='right'>".number_format($rst[4]-$rst[5],0,'.',',')."</td></tr>";}

$ttv=$ttv+$rst[4];
$tav=$tav+$rst[5];
}

if($ttv==0){
$pcn=100;}
else{
$pcn=$tav*100/$ttv;}	

$str=$str."<tr class='cls' style='font-weight:bold;'><td>".Total."</td><td></td><td></td><td></td><td></td><td align='right'>".number_format($ttv,0,'.',',')."</td><td align='right'>".number_format($tav,0,'.',',').
"</td><td align='center'>".number_format($pcn,2,'.',',')."%</td><td align='right'>".number_format($ttv-$tav,0,'.',',')."</td></tr></table>";

printf("%s",$str);

}


?>
	

