<?php
require "racs.php";
//require "vtshowsBBB.php";
require "salesstyles.php";
session_start();

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

<html>
<title>Distributor Performance</title>

<head>
<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>

<link rel="stylesheet" type="text/css" href="vtxstyles.css">
<script>

function scopys(pr)
{
var dts=document.getElementById("dt");

var sels=window.getSelection();
sels.removeAllRanges();
var ranges=document.createRange();
ranges.selectNodeContents(dts);
sels.addRange(ranges);
document.execCommand("copy");
alert("Copied - successfull");
sels.removeAllRanges();
}


function srgs(pr)
{
var sv=document.getElementById('srctxt').value.toLowerCase();
var dt=document.getElementById('dt');
var rn=dt.rows.length;

for(var cnt=1;cnt<rn-1;cnt++){
tvl=dt.rows[cnt].cells[0].innerText.toLowerCase();
rvl=dt.rows[cnt].cells[1].innerText.toLowerCase();
if(sv.length>1 && (tvl.indexOf(sv)>=0 || rvl.indexOf(sv)>=0)){
dt.rows[cnt].style.display='table-row';}
else{
dt.rows[cnt].style.display='none';}
if(sv.length<=1){
dt.rows[cnt].style.display='table-row';}	
}

}


</script>


</head>

<body style='font-family:calibri;font-size:small;'>

<div style='width=30%;height:100%%;'>

<?php


$flt=racs(1);

if($flt=="access failed"){
printf("<div style='font-color:red;'>>Access failed\nContanct Sales and Marketing division</div>");
}
else{

$conn=mysqli_connect("localhost","primarysales","S5L7lF01EQZvQrGK","primarysales_dianaold");


if(isset($_REQUEST['starts'])){
$starts=$_REQUEST['starts'];}
else{
$starts=date('Y-m-01');}

if(isset($_REQUEST['ends'])){
$ends=$_REQUEST['ends'];}
else{
$ends=date('Y-m-t');}

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

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

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

if(isset($_REQUEST['terrys'])){
$terrys=$_REQUEST['terrys'];}
else{
$terrys='all';}

if(isset($subregions)){
$subregions=$_REQUEST['subregions'];}
else{
$subregions='all';}

if(isset($_REQUEST['agenttypes'])){
$agenttypes=$_REQUEST['agenttypes'];}
else{
$agenttypes='all';}

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

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

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

if(isset($_REQUEST['dbids'])){
$dbids=$_REQUEST['dbids'];}
else{
$dbids='all';}

if(isset($_REQUEST['executive'])){
$executive=$_REQUEST['executive'];}
else{
$executive='all';}

#$flt=racs(1);

if(strtolower($zones)!='all'){
$flt=$flt." and zones='".$zones."' ";}
if(strtolower($regions)!='all'){
$flt=$flt." and regions='".$regions."' ";}
if(strtolower($subregions)!='all'){
$flt=$flt." and sub_regions='".$subregions."' ";}
if(strtolower($agents)!='all'){
$flt=$flt." and agent_names='".$agents."' ";}
if(strtolower($dbids)!='all'){
$flt=$flt." and group_dbid='".$dbids."' ";}
if(strtolower($agenttypes)!='all'){
$flt=$flt." and agent_types='".$agenttypes."' ";}
if(strtolower($terrys)!='all'){
$flt=$flt." and territory_names='".$terrys."' ";}
if(strtolower($items)!='all'){
$flt=$flt." and item_names='".$items."' ";}
if(strtolower($ranges)!='all'){
$flt=$flt." and item_ranges='".$ranges."' ";}
if(strtolower($subranges)!='all'){
$flt=$flt." and sub_ranges='".$subranges."' ";}
if(strtolower($executive)!='all'){
$flt=$flt." and executive_names='".$executive."' ";}


stps('pr');
printf("<div class='sds'>");
rdentrysalestondash(1);
printf("</div>");


printf("<div class='mnf'>");

printf("<div style='font-size:16;color:#006699;border-bottom:1px solid silver;width:30%%;'>TERRITORY PERFORMANCE ZONE-$zones REGION-$regions</div>");

$tv=0;
$tz=0;
$arr=array();

printf("<BR><BR>");
$sprt="&zones=".$zones."&regions=".$regions."&subregions=".$subregions."&agents=".$agents."&agenttypes=".$agenttypes."&terrys=".$terrys."&ranges=".$ranges."&items=".$items."&subranges=".$subranges."&dbids=".$dbids;

printf("<div style='width:90%%;height:5%%;border:0px solid silver;border-radius:2px;display:table;vertical-align:middle;'>");

printf("<form method='post' action='rpmrdagenttgsummaryton.php?actp=rpt%s'><table style='width:100%%;font-size:13;'>",$sprt);
$sqlst="select distinct rgs from (select distinct zone_orders as rds,regions as rgs from rc_invoices union select distinct zone_orders,regions from rd_targets union select distinct zone_orders,regions from rd_saleinv order by rds) as zns";
$qry=mysqli_query($conn,$sqlst);

printf("<tr><td width=5%%><input type='text' id='inputField' value='%s' name='starts' size=8 style='font-size:13;background:#CACACA;border:1px solid #006699;border-radius:1px;font-family:calibri;color:#006699;'></td><td width=1%%>-</td><td width=5%%><input type='text' id='inputField2' value='%s' name='ends' size=8 style='font-size:13;background:#CACACA;border:1px solid #006699;border-radius:1px;font-family:calibri;color:#006699;'></td>
<td width=20%%><select name='regions' style='width:95%%;font-size:13;background:#CACACA;border:1px solid #006699;border-radius:1px;font-family:calibri;color:#006699;'><option>%s</option><option>All</option>",$starts,$ends,$regions);
//while($rst=mysqli_fetch_row($qry)){
//printf("<option>%s</option>",$rst[0]);}	
printf("</select></td><td><input type='submit' value='Go' style='font-size:13;background:#CACACA;color:#006699;border:1.5px solid #006699;border-radius:1pxfont-size:13;'></td></form>",$starts,$ends);

printf("<td width=38%% align='right' style='color:grey;'>Search</td><td width=20%%>
<input type='text' id='srctxt' size=28 style='font-family:calibri;font-size:14;border:1px solid silver;border-radius:1.5px;' onkeyup=\"srgs(event)\"></td><td style='font-size:12;color:green;text-align:center;background:silver;border:1px solid white;' onclick=\"scopys(1)\">Copy</td></tr>");
printf("</table>");

printf("<table id='dt'  class='vh' style='width:110%%;font-size:13;border-collapse:collapse;color:#006699;'>
<tr style='background:silver;border:1px solid white;text-align:right;'>
<td style='border:1px solid white;text-align:left;'>Agent</td>
<td style='border:1px solid white;text-align:left;'>Territory</td>
<td style='border:1px solid white;'>WDays</td>
<td style='border:1px solid white;text-align:center;'>Target Kg</td>
<td style='border:1px solid white;text-align:center;'>Primary Kg</td>
<td style='border:1px solid white;text-align:center;'>RD Kg</td>

<td style='border:1px solid white;text-align:center;'>pri%%</td>

<td style='border:1px solid white;text-align:center;'>Rd%%</td>
<td style='border:1px solid white;text-align:center;'>M-Calls</td>
<td style='border:1px solid white;text-align:center;'>P-Calls</td>

<td style='background:silver;border:1px solid white;' align='center'>Open</td>
<td style='background:silver;border:1px solid white;' align='center'>Open</td>
<td style='background:silver;border:1px solid white;' align='center'>Trend-Ton</td>
<td style='background:silver;border:1px solid white;' align='center'>Trend-Ton</td>
</tr>");

//<td style='border:1px solid white;text-align:center;'>Target</td>
//<td style='border:1px solid white;text-align:center;'>Orders</td>
//<td style='border:1px solid white;text-align:center;'>Primary</td>
//<td style='border:1px solid white;text-align:center;'>Rd-Sales</td>
//<td style='border:1px solid white;text-align:center;'>Discount</td>
//<td style='border:1px solid white;text-align:center;'>Dis%%</td>
//$sqlst="select magents,mterrys,tgvl,acvl,tgwt,acwt,odvl,odwt,emp,rdvl,rddisc,atwds,pcalls,dbcomi,strd,stpri,optstk,optpri,optrdv,stfrop,stfree,acvldir,mcalls from (select distinct agent_names as magents,territory_names as mterrys,executive_names as emp from rc_invoices ".$flt." and dates>='".$starts."' and dates<='".$ends."' union select distinct agent_names,territory_names,executive_names from rc_targets ".$flt." and dates>='".$starts."' and dates<='".
//$ends."' union select distinct agent_names,territory_names,executive_names from rc_orders ".$flt." and dates>='".$starts."' and dates<='".
//$ends."' union select distinct agent_names,territory_names,executive_names from rd_saleinv ".$flt." and dates>='".$starts."' and dates<='".
//$ends."' union select distinct agent_names,territory_names,executive_names from rc_freeinvoices ".$flt." and dates>='".$starts."' and dates<='".
//$ends."' union select distinct agent_names,territory_names,executive_names from rd_db_stocks ".$flt." and dates>='".$starts."' and dates<='".$ends."') as ags 

//left join(select agent_names,territory_names,sum(box_qty*box_price) as tgvl,sum(box_qty*packets_per_box*packet_weights/1000) as tgwt from rc_targets ".$flt." and dates>='".$starts."' and dates<='".$ends."' group by agent_names,territory_names) as tgs on tgs.agent_names=magents and tgs.territory_names=mterrys

//left join (select agent_names,territory_names,sum(box_qty*box_price) as acvl,sum(box_qty*packets_per_box*packet_weights/1000) as acwt from rc_invoices "
//.$flt." and dates>='".$starts."' and dates<='".$ends."' group by agent_names,territory_names) as acs on acs.agent_names=magents and acs.territory_names=mterrys

//left join (select agent_names,territory_names,sum(box_qty*box_price) as odvl,sum(box_qty*packets_per_box*packet_weights/1000) as odwt from rc_orders "
//.$flt." and dates>='".$starts."' and dates<='".$ends."' and order_status<>'CANCELLED' group by agent_names,territory_names) as ocs on ocs.agent_names=magents and ocs.territory_names=mterrys

//left join (select agent_names,territory_names,sum(pcs_qty*pcs_price) as rdvl,sum(rtdiscount_values+wsdiscount_values) as rddisc,count(distinct(dates)) as atwds,sum(pcalls) as pcalls,sum((pcs_qty*pcs_price)/100*8) as dbcomi,sum(mcalls) as mcalls from rd_saleinv "
//.$flt." and dates>='".$starts."' and dates<='".$ends."' group by agent_names//,territory_names) as rds on rds.agent_names=magents and rds.territory_names=mterrys

//left join (select agent_names,territory_names,sum(pcs_qty*pcs_price+free_qtys*pcs_price) as strd from rd_saleinv ".$flt." and dates>='2020-09-01' and dates<='".$ends."' and chanell='GENERAL TRADE' and agent_types='AGENCY' group by agent_names,territory_names) 
//as strdd on strdd.agent_names=magents and strdd.territory_names=mterrys

//left join (select agent_names,territory_names,sum(box_qty*box_price) as stpri from rc_invoices ".$flt." and dates>='2021-04-19' and dates<='".$ends."' and chanell='GENERAL TRADE' and agent_types='AGENCY' group by agent_names,territory_names) 
//as stprd on stprd.agent_names=magents and stprd.territory_names=mterrys

//left join (select agent_names,territory_names,sum(pcs_qty*pcs_price) as stfree from rc_freeinvoices ".$flt." and dates>='2021-04-19' and dates<='".$ends."' and chanell='GENERAL TRADE' and agent_types='AGENCY' group by agent_names,territory_names) 
//as stfreep on stfreep.agent_names=magents and stfreep.territory_names=mterrys

///left join (select agent_names,territory_names,sum(pcs_qty*pcs_price) as optstk from rd_db_stocks ".$flt." and dates>='2021-04-19' and dates<='".$ends."' group by agent_names,territory_names) 
//as optks on optks.agent_names=magents and optks.territory_names=mterrys

//left join (select agent_names,territory_names,sum(pcs_qty*pcs_price+free_qtys*pcs_price) as optrdv from rd_saleinv ".$flt." and dates>='2021-04-19' and dates<'".$starts."' and chanell='GENERAL TRADE' and agent_types='AGENCY' group by agent_names,territory_names) 
//as strddop on strddop.agent_names=magents and strddop.territory_names=mterrys

//left join (select agent_names,territory_names,sum(box_qty*box_price) as optpri from rc_invoices ".$flt." and dates>='2021-04-19' and dates<'".$starts."' and chanell='GENERAL TRADE' and agent_types='AGENCY' group by agent_names,territory_names) 
//as stprdop on stprdop.agent_names=magents and stprdop.territory_names=mterrys

//left join (select agent_names,territory_names,sum(pcs_qty*pcs_price) as stfrop from rc_freeinvoices ".$flt." and dates>='2021-04-19' and dates<'".$starts."' and chanell='GENERAL TRADE' and agent_types='AGENCY' group by agent_names,territory_names) 
//as stfreeop on stfreeop.agent_names=magents and stfreeop.territory_names=mterrys

//left join (select agent_names,territory_names,sum(box_qty*box_price) as acvldir from rc_invoices ".$flt." and dates>='".$starts."' and dates<='".$ends."' and chanell='GENERAL TRADE' and agent_types='DIRECT' group by agent_names,territory_names) as acsdir on acsdir.agent_names=magents and acsdir.territory_names=mterrys
//";


//////////////////Without Balance Stock///////////////////////////////

$sqlst="select magents,mterrys,tgwt,acwt,odwt,emp,atwds,pcalls,acvldir,mcalls,rdton from 
(select distinct agent_names as magents,territory_names as mterrys,executive_names as emp from rc_invoices ".$flt." and dates>='".$starts."' and dates<='".$ends."'
union select distinct agent_names,territory_names,executive_names from rd_targets ".$flt." and dates>='".$starts."' and dates<='".
$ends."' union select distinct agent_names,territory_names,executive_names from rc_orders ".$flt." and dates>='".$starts."' and dates<='".
$ends."' union select distinct agent_names,territory_names,executive_names from rd_saleinv ".$flt." and dates>='".$starts."' and dates<='".
$ends."') as ags 

left join(select agent_names,territory_names,sum(box_qty*packets_per_box*packet_weights/1000) as tgwt from rd_targets ".$flt." and dates>='".$starts."' and dates<='".$ends."' group by agent_names,territory_names) as tgs on tgs.agent_names=magents and tgs.territory_names=mterrys

left join (select agent_names,territory_names,sum(box_qty*packets_per_box*packet_weights/1000) as acwt from rc_invoices "
.$flt." and dates>='".$starts."' and dates<='".$ends."' group by agent_names) as acs on acs.agent_names=magents and acs.territory_names=mterrys

left join (select agent_names,territory_names,sum(box_qty*packets_per_box*packet_weights/1000) as odwt from rc_orders "
.$flt." and dates>='".$starts."' and dates<='".$ends."' and order_status<>'CANCELLED' group by agent_names) as ocs on ocs.agent_names=magents and ocs.territory_names=mterrys

left join (select agent_names,territory_names,count(distinct(dates)) as atwds,sum(pcalls) as pcalls,sum(mcalls) as mcalls,sum(pcs_qty*packet_weights/1000)as rdton from rd_saleinv"
.$flt." and dates>='".$starts."' and dates<='".$ends."' group by agent_names) as rds on rds.agent_names=magents and rds.territory_names=mterrys

left join (select agent_names,territory_names,sum(box_qty*packets_per_box*packet_weights/1000) as acvldir from rc_invoices ".$flt." and dates>='".$starts."' and dates<='".$ends."' and chanell='GENERAL TRADE' and agent_types='DIRECT' group by agent_names) as acsdir on acsdir.agent_names=magents and acsdir.territory_names=mterrys
";

$tgv=0;
$acv=0;
$tgw=0;
$acw=0;
$odvl=0;
$rdvl=0;
$rddisc=0;
$pcalls=0;
$dbcomi=0;
$sttot=0;
$opttot=0;
$rdton=0;

//magents,mterrys,tgwt,acwt,odwt,emp,atwds,pcalls,acvldir,mcalls,rdton

$qry=mysqli_query($conn,$sqlst);
while($rst=mysqli_fetch_row($qry)){
$tgv=$tgv+$rst[2];
$acv=$acv+$rst[3];
//$tgw=$tgw+$rst[2];
//$acw=$acw+$rst[3];
//$odvl=$odvl+$rst[6];
//$rdvl=$rdvl+$rst[9];
$rddir=$rddir+$rst[8];
//$rddisc=$rddisc+$rst[10];
$pcalls=$pcalls+$rst[7];
$mcalls=$mcalls+$rst[9];
//$dbcomi=$dbcomi+$rst[13];
//$sttot=$sttot+$rst[15]+$rst[16]+$rst[20]-$rst[14];
//$opttot=$opttot+$rst[16]+$rst[17]+$rst[19]-$rst[18];
$rdton=$rdton+$rst[10]+$rst[8];

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

if($rst[2]==0){
$rds=0;}
else{
$rds=($rst[10]+$rst[8])*100/$rst[2];}
	
if($rst[4]==0){
$wpcs=0;}
else{
$wpcs=$rst[5]*100/$rst[4];}

printf("<tr style='border-bottom:1px solid silver;'>
<td>%s</td>
<td>%s</td>
<td>%s</td>
<td align='left'>%s</td>
<td align='left'>%s</td>
<td align='left' style='color:purple'>%s</td>
<td align='center' style='color:red' bgcolor='#DAF7A6'>%s%%</td>
<td align='center' style='color:red' bgcolor='#DAF7A6'>%s%%</td>
<td align='right' style='color:purple' bgcolor='#D6EAF8'>%s</td>
<td align='right' style='color:522109'>%s</td>

<td align='center'><a href='rpmrangenewtonn.php?starts=%s&ends=%s&agents=%s&terrys=%s' target='_blank'>Range</a></td>

<td align='center'><a href='rpmtgttitemwisetgsummarytonq.php?starts=%s&ends=%s&agents=%s&terrys=%s' target='_blank'>items</a></td>

<td align='center'><a href='sku_saletrendgtrangetonn.php?starts=%s&ends=%s&agents=%s&terrys=%s' target='_blank'>Range</a></td>

<td align='center'><a href='sku_saletrendgtproducttonn.php?starts=%s&ends=%s&agents=%s&terrys=%s' target='_blank'>SKU</a></td>


</tr>",
$rst[0],
$rst[1],
number_format($rst[6],0,'.',','),
number_format($rst[2],2,'.',','),
number_format($rst[3],2,'.',','),
number_format($rst[10]+$rst[8],2,'.',','),
number_format($pcs,1,'.',','),
//number_format($rst[9]+$rst[14],0,'.',','),
number_format($rds,1,'.',','),
number_format($rst[9],0,'.',','),
number_format($rst[7],0,'.',','),
//number_format($rst[10],0,'.',','),
//number_format($rst[10]/$rst[9]*100,0,'.',','),
$starts,$ends,$rst[0],$rst[1],$starts,$ends,$rst[0],$rst[1],$starts,$ends,$rst[0],$rst[1],$starts,$ends,$rst[0],$rst[1]);
}

if($tgv==0){
$pcs=100;}
else{
$pcs=$acv*100/$tgv;}
	
if($tgw==0){
$wpcs=100;}
else{
$wpcs=$acw*100/$tgw;}

printf("<tr style='border-bottom:1px solid silver;font-weight:bold;'>
<td>Total</td>
<td></td>
<td></td>
<td align='center'>%s</td>
<td align='center'>%s</td>
<td align='center'>%s</td>
<td align='center'>%s</td>
<td align='center'>%s</td>
<td align='center'>%s</td>
<td align='center'>%s</td>
</tr></table>",

number_format($tgv,0,'.',','),
number_format($acv,0,'.',','),
number_format($rdton,0,'.',','),
number_format($acv/$tgv*100,1,'.',','),
number_format($rdton/$tgv*100,1,'.',','),
number_format($mcalls,0,'.',','),
number_format($pcalls,0,'.',','));
printf("</div>");
}
?>
</div>
</body>
</html>


<!--<td align='center'><a href='rpmslvinvlogitems.php?starts=%s&ends=%s&agents=%s&terrys=%s' target='_blank'>items</a></td>-->