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

if(!isset($_SESSION['titles']) || !isset($_SESSION['positions'])|| !isset($_SESSION['usernames'])){
header("location:index.php");
}
?>
<html>
<title>RPM-Regional Items</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();
bvl=dt.rows[cnt].cells[2].innerText.toLowerCase();
if(sv.length>1 && (tvl.indexOf(sv)>=0 || rvl.indexOf(sv)>=0 || bvl.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\Contact Sales and Marketing division</div>");
}
else{

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



if(isset($_REQUEST['starts'])){
echo "starts ok";	
$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['rmid'])){
$rmid=$_REQUEST['rmid'];}
else{
$rmid='all';}

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

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

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


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

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

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

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

if(isset($_REQUEST['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['arm_names'])){
$arm_names=$_REQUEST['arm_names'];}
else{
$arm_names='all';}

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

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

#$flt=racs(1);

if(strtolower($zones)!='all'){
$flt=$flt." and zones='".$zones."' ";}
if(strtolower($rmid)!='all'){
$flt=$flt." and rm_id='".$rmid."' ";}
if(strtolower($chanell)!='all'){
$flt=$flt." and chanell='".$chanell."' ";}
if(strtolower($agent_types)!='all'){
$flt=$flt." and agent_types='".$agent_types."' ";}
if(strtolower($regions)!='all'){
$flt=$flt." and regions='".$regions."' ";}
if(strtolower($sm_id)!='all'){
$flt=$flt." and sm_id='".$sm_id."' ";}
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."' ";}
if(strtolower($arm_names)!='all'){
$flt=$flt." and arm_names='".$arm_names."' ";}
if(strtolower($asm_names)!='all'){
$flt=$flt." and asm_names='".$asm_names."' ";}
if(strtolower($rsm_names)!='all'){
$flt=$flt." and rsm_names='".$rsm_names."' ";}

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:25%%;'>Item wise summary</div>");
printf("<div style='font-size:16;color:#006699;border-bottom:1px solid silver;width:25%%;'>$regions</div>");
printf("<BR><BR>");
$sprt="&chanell=".$chanell."&zones=".$zones."&regions=".$regions."&subregions=".$subregions."&agents=".$agents."&agenttypes=".$agenttypes."&terrys=".$terrys."&ranges=".$ranges."&items=".$items."&subranges=".$subranges;

printf("<div style='width:95%%;height:5%%;border:0px solid silver;border-radius:2px;display:table;vertical-align:middle;'>");
printf("<form method='post' action='rpmtgttitemwisetgsummary2.php?actp=rpt%s'><table style='width:90%%;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 rc_targets 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><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' 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;'>Code</td>
<td style='border:1px solid white;text-align:center;'>Item</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>");

$sqlst = "
SELECT 
    mranges, msubranges, mitems,
    SUM(tgvl) as tgvl, SUM(acvl) as acvl,
    SUM(tgwt) as tgwt, SUM(acwt) as acwt,
    SUM(odqt) as odqt, SUM(odvl) as odvl,
    SUM(rdqty) as rdqty, SUM(rdvl) as rdvl, 
    MAX(pkbk) as pkbk, 
    SUM(rdpcs) as rdpcs,
    SUM(prdirect) as prdirect, SUM(prdirbox) as prdirbox, SUM(prdirpcs) as prdirpcs,
    SUM(tton) as tton, SUM(prton) as prton, SUM(prtondi) as prtondi, SUM(rdton) as rdton,
    SUM(prpcs) as prpcs, 
    MAX(rnk) as rnk,
    MAX(ipcode) as ipcode
FROM (
    SELECT 
        mranges, msubranges, mitems,
        tgvl, acvl, tgwt, acwt, odqt, odvl, rdqty, rdvl, pkbk, rdpcs,
        prdirect, prdirbox, prdirpcs, tton, prton, prtondi, rdton, prpcs, rnk,
        COALESCE(acs.ipcode, tgt.ipcode) AS ipcode
    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'
    ) ags

    /* ========= ALL YOUR EXISTING JOINS (Keep them exactly as you had them) ========= */
    LEFT JOIN (
        SELECT item_ranges, sub_ranges, item_names,
            SUM(box_qty) tgvl, SUM(box_qty*box_price) tgwt,
            SUM(box_qty*packets_per_box*packet_weights/1000000) tton
        FROM rc_targets $flt AND dates>='$starts' AND dates<='$ends'
        GROUP BY item_ranges, sub_ranges, item_names
    ) 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) acvl, SUM(box_qty*box_price) acwt,
            SUM(box_qty*packets_per_box*packet_weights/1000000) prton,
            SUM(box_qty*packets_per_box) prpcs, MAX(rank_items) rnk, MAX(itemcodes) ipcode
        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
    ) 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, MAX(itemcode) ipcode
        FROM rc_targets $flt AND dates>='$starts' AND dates<='$ends'
        GROUP BY item_ranges, sub_ranges, item_names
    ) tgt ON tgt.item_ranges=mranges AND tgt.sub_ranges=msubranges AND tgt.item_names=mitems

    LEFT JOIN (
        SELECT item_ranges, sub_ranges, item_names,
            SUM(pcs_qty/packets_per_box) rdqty, SUM(pcs_qty*pcs_price) rdvl,
            MAX(packets_per_box) pkbk, SUM(pcs_qty) rdpcs,
            SUM(pcs_qty*packet_weights/1000000) rdton
        FROM rd_saleinv $flt AND dates>='$starts' AND dates<='$ends'
        GROUP BY item_ranges, sub_ranges, item_names
    ) 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) prdirect, SUM(box_qty) prdirbox,
            SUM(box_qty*packets_per_box) prdirpcs,
            SUM(box_qty*packets_per_box*packet_weights/1000000) 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
    ) 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) odqt, SUM(box_qty*box_price) 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
    ) acss ON acss.item_ranges=mranges AND acss.sub_ranges=msubranges AND acss.item_names=mitems
) final_table

/* STEP 3: The final Group By to collapse duplicates */
GROUP BY mitems
ORDER BY mranges, msubranges DESC
";


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

$qry=mysqli_query($conn,$sqlst);
while($rst=mysqli_fetch_row($qry)){
$tqty=$tqty+$rst[3];
$odw=$odw+$rst[8];
$tgw=$tgw+$rst[5];
$acw=$acw+$rst[6];
$rdsl=$rdsl+$rst[10]+$rst[13];

	
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'>%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>
</tr>",
$rst[0],
$rst[22],
$rst[2],
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,'.',','));
}
	
printf("<tr style='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("</table></div>");

}

?>

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