<?php
//error_reporting(E_ALL);
//ini_set('display_errors', 1);

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

<html>
<title>Diana-Product Performance - General Trade</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();
pvl=dt.rows[cnt].cells[2].innerText.toLowerCase();

if(sv.length>1 && (tvl.indexOf(sv)>=0 || rvl.indexOf(sv)>=0 || pvl.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['chanell'])){
$chanell=$_REQUEST['chanell'];}
else{
$chanell='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['executive'])){
$executive=$_REQUEST['executive'];}
else{
$executive='all';}

#$flt=racs(1);

if(strtolower($zones)!='all'){
$flt=$flt." and zones='".$zones."' ";}
if(strtolower($chanell)!='all'){
$flt=$flt." and chanell='".$chanell."' ";}
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($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(1);

printf("<div class='sds'>");
newdash(1);
printf("</div>");
printf("<div class='mnf'>");
printf("<div style='font-size:16;color:#006699;border-bottom:1px solid silver;width:30%%;'>PRODUCT ACH:GENERAL TRADE</div>");
printf("<div style='font-size:16;color:#006699;border-bottom:1px solid silver;width:30%%;'>CHANEL-$chanell&nbsp&nbspZONE-$zones&nbsp&nbspREGION-$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;

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

printf("<form method='post' action='rpmtgttitemwisetgsummary.php?actp=rpt%s'><table style='width:100%%;font-size:13;'>",$sprt);
printf("<tr><td style='color:grey;'>Select the date range</td><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>
<input type='submit' value='Go' style='font-size:13;background:#CACACA;color:#006699;border:1.5px solid #006699;border-radius:1pxfont-size:13;'></form>",$starts,$ends);

printf("<td width=40%% 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' style='width:105%%;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:center;'>Range</td>
<td style='border:1px solid white;text-align:center;'>Item</td>
<td style='border:1px solid white;text-align:center;'>Incen-cat</td>
<td style='border:1px solid white;text-align:center;'>Target Ton</td>
<td style='border:1px solid white;text-align:center;'>Primary Ton</td>
<td style='border:1px solid white;text-align:center;'>RD Ton</td>
<td style='border:1px solid white;text-align:center;'>Target Box</td>
<td style='border:1px solid white;text-align:center;'>Order qty</td>
<td style='border:1px solid white;text-align:center;'>Pri Box</td>
<td style='border:1px solid white;text-align:center;'>Pri Pcs</td>
<td style='border:1px solid white;text-align:center;'>Diff</td>
<td style='border:1px solid white;text-align:center;'>Rd-Box</td>
<td style='border:1px solid white;text-align:center;'>Rd-Pcs</td>
<td style='border:1px solid white;text-align:center;'>Target Value</td>
<td style='border:1px solid white;text-align:center;'>Order Value</td>
<td style='border:1px solid white;text-align:center;'>Sales Value</td>
<td style='border:1px solid white;text-align:center;'>RD Value</td>
<td style='border:1px solid white;text-align:center;'>Variance</td>
</tr>");


////With Stock Quntity Query /////
#<td style='border:1px solid white;text-align:center;'>Stock-Qty</td>

//$sqlst="select mranges,msubranges,mitems,tgvl,acvl,tgwt,acwt,odqt,odvl,rdqty,rdvl,pkbk,strd,stpri,optstk from (select distinct item_ranges as mranges,sub_ranges as msubranges,item_names as mitems from rc_invoices ".$flt." and dates>='".$starts."' and dates<='".$ends."' union select distinct item_ranges,sub_ranges,item_names from rc_targets ".$flt." and dates>='".$starts."' and dates<='".
//$ends."' union select distinct item_ranges,sub_ranges,item_names from rc_orders ".$flt." and dates>='".$starts."' and dates<='".
//$ends."' union select distinct item_ranges,sub_ranges,item_names from rd_saleinv ".$flt." and dates>='".$starts."' and dates<='".
//$ends."' union select distinct item_ranges,sub_ranges,item_names from rd_db_stocks ".$flt." and dates>='2021-04-19' and dates<='2021-04-20') as ags

//left join (select item_ranges,sub_ranges,item_names,
//sum(box_qty) as tgvl,sum(box_qty*box_price) as tgwt from rc_targets ".$flt." and 
//dates>='".$starts."' and dates<='".$ends."' group by item_ranges,sub_ranges,item_names) as tgs on tgs.item_ranges=mranges and tgs.sub_ranges=msubranges and tgs.item_names=mitems 

//left join (select item_ranges,sub_ranges,item_names,sum(box_qty) as acvl,
//sum(box_qty*box_price) as acwt from rc_invoices ".$flt." and dates>='".$starts."' and dates<='".$ends.
//"' group by item_ranges,sub_ranges,item_names) as acs on acs.item_ranges=mranges and acs.sub_ranges=msubranges and acs.item_names=mitems

//left join (select item_ranges,sub_ranges,item_names,sum(pcs_qty/packets_per_box) as rdqty,
//sum(pcs_qty*pcs_price) as rdvl,packets_per_box as pkbk from rd_saleinv ".$flt." and dates>='".$starts."' and dates<='".$ends.
//"' group by item_ranges,sub_ranges,item_names) as rdc on rdc.item_ranges=mranges and rdc.sub_ranges=msubranges and rdc.item_names=mitems

//left join (select item_ranges,sub_ranges,item_names,sum(box_qty) as odqt,
//sum(box_qty*box_price) as odvl from rc_orders ".$flt." and dates>='".$starts."' and dates<='".$ends.
//"' and order_status<>'CANCELLED' group by item_ranges,sub_ranges,item_names) as acss on acss.item_ranges=mranges and acss.sub_ranges=msubranges and acss.item_names=mitems

//left join (select item_ranges,sub_ranges,item_names,sum(pcs_qty/packets_per_box) as strd from rd_saleinv ".$flt." and dates>='2020-09-01' and dates<='".$ends."' group by item_ranges,sub_ranges,item_names) 
//as strdd on strdd.item_ranges=mranges and strdd.sub_ranges=msubranges and strdd.item_names=mitems

//left join (select item_ranges,sub_ranges,item_names,sum(box_qty) as stpri from rc_invoices ".$flt." and dates>='2020-09-01' and dates<='".$ends."' and chanell='GENERAL TRADE' and agent_types='AGENCY' group by item_ranges,sub_ranges,item_names) 
//as stprd on stprd.item_ranges=mranges and stprd.sub_ranges=msubranges and stprd.item_names=mitems

//left join (select item_ranges,sub_ranges,item_names,sum(pcs_qty/packets_per_box) as optstk from rd_db_stocks ".$flt." and dates>='2020-09-01' and dates<='".$ends."' group by item_ranges,sub_ranges,item_names) 
//as optks on optks.item_ranges=mranges and optks.sub_ranges=msubranges and optks.item_names=mitems order by mranges,msubranges DESC

//";


////Without Stock Quntity Query /////

$sqlst="select mranges,msubranges,mitems,tgvl,acvl,tgwt,acwt,odqt,odvl,rdqty,rdvl,pkbk,rdpcs,prdirect,prdirbox,prdirpcs,tton,prton,prtondi,rdton,prpcs,rnk from (select distinct item_ranges as mranges,sub_ranges as msubranges,item_names as mitems from rc_invoices ".$flt." and dates>='".$starts."' and dates<='".$ends."' and chanell='GENERAL TRADE' AND zones<>'H-OFFICE' union select distinct item_ranges,sub_ranges,item_names from rc_targets ".$flt." and dates>='".$starts."' and dates<='".
$ends."' union select distinct item_ranges,sub_ranges,item_names from rc_orders ".$flt." and dates>='".$starts."' and dates<='".
$ends."' and chanell='GENERAL TRADE' AND zones<>'H-OFFICE' union select distinct item_ranges,sub_ranges,item_names from rd_saleinv ".$flt." and dates>='".$starts."' and dates<='".
$ends."') as ags

left join (select item_ranges,sub_ranges,item_names,
sum(box_qty) as tgvl,sum(box_qty*box_price) as tgwt,sum(box_qty*packets_per_box*packet_weights/1000000) as tton from rc_targets ".$flt." and 
dates>='".$starts."' and dates<='".$ends."' group by item_ranges,sub_ranges,item_names) as tgs on tgs.item_ranges=mranges and tgs.sub_ranges=msubranges and tgs.item_names=mitems 



left join (select item_ranges,sub_ranges,item_names,sum(box_qty) as acvl,
sum(box_qty*box_price) as acwt,sum(box_qty*packets_per_box*packet_weights/1000000) as prton,sum(box_qty * packets_per_box) as prpcs,rank_items as rnk from rc_invoices ".$flt." and dates>='".$starts."' and dates<='".$ends.
"' and chanell='GENERAL TRADE' AND zones<>'H-OFFICE' group by item_ranges,sub_ranges,item_names) as acs on acs.item_ranges=mranges and acs.sub_ranges=msubranges and acs.item_names=mitems

left join (select item_ranges,sub_ranges,item_names,sum(pcs_qty/packets_per_box) as rdqty,
sum(pcs_qty*pcs_price) as rdvl,packets_per_box as pkbk,sum(pcs_qty) as rdpcs,sum(pcs_qty*packet_weights/1000000) as rdton from rd_saleinv ".$flt." and dates>='".$starts."' and dates<='".$ends.
"' group by item_ranges,sub_ranges,item_names) as rdc on rdc.item_ranges=mranges and rdc.sub_ranges=msubranges and rdc.item_names=mitems

left join (select item_ranges,sub_ranges,item_names,sum(box_qty*box_price) as prdirect,sum(box_qty) as prdirbox,sum(box_qty*packets_per_box) as prdirpcs,sum(box_qty*packets_per_box*packet_weights/1000000) as prtondi from rc_invoices ".$flt." and dates>='".$starts."' and dates<='".$ends.
"' and chanell='GENERAL TRADE' AND zones<>'H-OFFICE' and agent_types='DIRECT'  group by item_ranges,sub_ranges,item_names) as acdir on acdir.item_ranges=mranges and acdir.sub_ranges=msubranges and acdir.item_names=mitems

left join (select item_ranges,sub_ranges,item_names,sum(box_qty) as odqt,
sum(box_qty*box_price) as odvl from rc_orders ".$flt." and dates>='".$starts."' and dates<='".$ends.
"' and chanell='GENERAL TRADE' and order_status<>'CANCELLED' AND zones<>'H-OFFICE' group by item_ranges,sub_ranges,item_names) as acss on acss.item_ranges=mranges and acss.sub_ranges=msubranges and acss.item_names=mitems order by mranges,msubranges DESC

";
 
$tgv=0;
$acv=0;
$tgw=0;
$acw=0;
$rdsl=0;
$rdqtt=0;
 $odw=0;

$qry=mysqli_query($conn,$sqlst);
while ($rst = mysqli_fetch_row($qry)) {
    $tgw = $tgw + $rst[5];
    $acw = $acw + $rst[6];
    $odw = $odw + $rst[8];
    $rdsl = $rdsl + $rst[10] + $rst[13];
    if ($rst[11] != 0) {
        $rdqtt = $rst[9] / $rst[11];
    } else {
        $rdqtt = 0;
    }

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

    if ($rst[5] == 0) {
        $wpcs = 0;
    } else {
        $wpcs = $rst[6] * 100 / $rst[5];
    }

    printf("<tr style='border-bottom:1px solid silver;'>
    <td>%s</td>
    <td>%s</td>
    <td>%s</td>
    <td align='right'>%s</td>
    <td align='right'>%s</td>
    <td align='right'>%s</td>
    <td align='right'>%s</td>
    <td align='right'>%s</td>
    <td align='right'>%s</td>
    <td align='right'>%s</td>
    <td align='right' style='color:purple'bgcolor='#DAF7A6'>%s</td>
    <td align='right'>%s</td>
    <td align='right' style='color:purple'bgcolor='33FF7D'>%s</td>
    <td align='right'>%s</td>
    <td align='right'>%s</td>
    <td align='right'>%s</td>
    <td align='right'>%s</td>
    <td align='right'>%s</td>",
        $rst[0],
        $rst[2],
        $rst[21],
        number_format($rst[16], 3, '.', ','),
        number_format($rst[17], 2, '.', ','),
        number_format($rst[18] + $rst[19], 3, '.', ','),
        number_format($rst[3], 2, '.', ','),
        number_format($rst[7], 0, '.', ','),
        number_format($rst[4], 0, '.', ','),
        number_format($rst[20], 0, '.', ','),
        number_format($rst[3] - $rst[4], 0, '.', ','),
        number_format($rst[9] + $rst[14], 2, '.', ','), //box
        number_format($rst[12] + $rst[15], 2, '.', ','), //pcs
        number_format($rst[5], 0, '.', ','),
        number_format($rst[8], 0, '.', ','),
        number_format($rst[6], 0, '.', ','),
        number_format($rst[10] + $rst[13], 0, '.', ','),
        number_format($rst[6] - $rst[5], 0, '.', ',')
    );
}



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

printf("<tr style='border-bottom:1px solid silver;font-weight:bold;'>
<td>Total</td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td align='right'>%s</td>
<td align='right'>%s</td>
<td align='right'>%s</td>
<td align='right'>%s</td>
<td align='right'>%s</td>
</tr></table>",number_format($tgw,0,'.',','),number_format($odw,0,'.',','),number_format($acw,0,'.',','),number_format($rdsl,0,'.',','),number_format($tgw-$acw,0,'.',','));


printf("</div>");

}

?>

</div>
</body>
</html>