<?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 Dashboard</title>
<head>
<link rel="stylesheet" type="text/css" href="vtxstyles.css">
<script>
function rgshows(pr,pv)
{
var dv=document.getElementById("dvshw");
dv.style.display="block";
dv.innerHTML=pv;
dv.style.top=pr.clientY;
dv.style.left='74%';
}

function rghides(pr)
{
document.getElementById("dvshw").style.display='none';
}

//setTimeout(function(){ window.location = "rpmodinvdash.php"; },40000);
</script>

</head>
<body style='font-family:calibri;font-size:small;'>
<?php

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

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

$sqlst="select 	report_block from acs where user_names='".$_SESSION['usernames']."' ";
$qry=mysqli_query($conn,$sqlst);

$rst=mysqli_fetch_row($qry);
if($rst[0]==1){
printf("<div style='position:absolute;top:100;left:200;font-size:16;'>Access Denied</div>");}
else{



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

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

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

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

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

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

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

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

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

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


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


stps('pr');
printf("<div class='sds'>");
#odrdash(1);
newdash(1);


Printf("<br><br>");
#printf("<a href='prdashbs.php'>Previous mode</a>");
Printf("<br><br>");
#printf("<a href='rddashbs.php'>RD-Sales</a>");
Printf("<br><br>");
#printf("<a href='mrdashbs.php'>Market Rtn</a>");
Printf("<br><br>");
#printf("<a href='dsrddashbs.php'>Sales-Accounts</a>");
Printf("<br><br>");
#printf("<a href='ppp1.php'>CH-01-SKU</a>");
Printf("<br><br>");
#printf("<a href='ppp2.php'>CH-02-SKU</a>");
Printf("<br><br>");
#printf("<a href='chrange.php'>Channel Sales</a>");
printf("</div>");


printf("<div class='mns' style='width:55%%;'>");

$starts=date('Y-m-01');
$ends=date('Y-m-t');

$sqlst="select max(dates) from rc_invoices where dates>='".$starts."' and dates<='".$ends."' ";
$qry=mysqli_query($conn,$sqlst);
if(mysqli_num_rows($qry)==0){
$tdates=date('Y-m-d');}
else{
$rst=mysqli_fetch_row($qry);
$tdates=$rst[0];}
	


printf("<table width=70%%><tr><td width=70%%>
<table width=100%%  style='width:85%%;color:grey;font-size:13;border:1px solid silver;border-collapse:collapse;'><tr style='font-size:13;'>
<td colspan='3' style='font-size:18;color:grey;border-bottom:1px solid silver;' >Day summary-Group</td>");

printf("<tr style='background:silver;border:0px solid silver;font-size:14;color:white;'><td style='border:1px solid sivler;'>Sale type</td>
<td align='right' style='border:1px solid sivler;'>%s</td><td align='right' style='border:1px solid sivler;border-right:1px solid silver;'>%s</td></tr>",
$tdates,date('Y-M')); 
	
$tdv=0;
$mnv=0;

$sqlst="select invs,tv,mv from (select distinct invoice_types as invs from rc_invoices ".$flt." and  dates>='".$starts."' and dates<='".$ends."') as tps left join 
 (select invoice_types,sum(box_qty*box_price) as tv from rc_invoices ".$flt." and dates='".$tdates."' group by invoice_types) as tvl on invs=tvl.invoice_types left join 
(select invoice_types,sum(box_qty*box_price) as mv from rc_invoices ".$flt." and dates>='".$starts."' and dates<='".$ends."' group by invoice_types) as mvs on invs=mvs.invoice_types ";

$qry=mysqli_query($conn,$sqlst);
while($rst=mysqli_fetch_row($qry)){
printf("<tr class='bls'><td>%s</td><td align='right'>%s<td align='right' style='border-right:1px solid silver;'>%s</td></tr>",
$rst[0],number_format($rst[1],0,'.',','),number_format($rst[2],0,'.',','));
$tdv=$tdv+$rst[1];
$mnv=$mnv+$rst[2];
}

printf("<tr class='bls' style='font-weight:bold;font-size:13;'><td>Total</td>
<td align='right'>%s<td align='right' style='border-right:1px solid silver;'>%s</td></tr></table></td>",number_format($tdv,0,'.',','),number_format($mnv,0,'.',','));

$sqlst="select tgs,acs from (select sum(box_qty*box_price) as tgs from rc_targets ".$flt." and  dates>='".$starts."' and dates<='".$ends."') as tg,
(select sum(box_qty*box_price) as acs from rc_invoices ".$flt." and  dates>='".$starts."' and dates<='".$ends."' and regions<>'ARMY SUPPLY' and regions<>'MODERN TRADE' and regions<>'MPCS' and regions<>'PVT LABEL') as ac ";
$qry=mysqli_query($conn,$sqlst);
$rst=mysqli_fetch_row($qry);

if($rst[0]==0){
$ach="Budget not set";}
else{
$ach="<br>".round($rst[1]*100/$rst[0],0)."% <br>GT";}
printf("<td align='center' style='color:red;font-size:20;width:30%%;'>%s</td></tr></table>",$ach);


//////////////////////////////////////////

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

$sqlst="select 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 sbs";
$qry=mysqli_query($conn,$sqlst);
printf("<table id='dta' style='width:75%%;font-size:13;border-collapse:collapse;color:#006699;border:1px solid silver;'>");


/////////////////////////////////////////////

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




///////////////////////////////////////////////////////

printf("<BR>");

echo "<div style='display:block;font-size:14;left:5%;' id='tdta'>";
echo "<table id='dtda' style='font-size:13;width:75%;color:#2E2E2E;'>";
echo "<th style='color:white;font-size:13;background:#5D6D7E;border:1px solid grey;' colspan='7' align='left'><b>Group Performance-Gt/Mt " . date('Y-M') . "</b></th>";

printf("<tr style='background:silver;border:1px solid white;text-align:right;'><td style='border:1px solid white;text-align:center;border'>Channel</td>
<td style='border:1px solid white;text-align:center;'>Target</td>
<td style='border:1px solid white;text-align:center;'>Gros-Sale</td>
<td style='border:1px solid white;text-align:center;'>Ach:</td>
<td style='border:1px solid white;text-align:center;'>Vat-%%</td>
<td style='border:1px solid white;text-align:center;'>Discount</td>
<td style='border:1px solid white;text-align:center;'>Net-Sale</td></tr>");

$sqlst="select mzones,tgvl,acvl,dvl,vatrates from (select distinct chanell as mzones from rc_invoices where dates>='2023-04-01' AND chanell<>'' order by chanell) as ags left join 
(select chanell,sum(box_qty*box_price)/1000000 as tgvl from rc_targets ".$flt." and 
dates>='".$starts."' and dates<='".$ends."' group by chanell) as tgs on tgs.chanell=mzones 
left join (select chanell,sum(box_qty*box_price)/1000000 as acvl,sum((box_qty*box_price)*discount_rates)/1000000 as dvl,sum((box_qty*box_price)*vat_rates)/1000000 as vatrates from rc_invoices ".$flt." and dates>='".$starts."' and dates<='".$ends."' group by chanell) 
as acs on acs.chanell=mzones ";
 
$tgv=0;
$acv=0;
$dvl=0;
$vattot=0;
 
$qry=mysqli_query($conn,$sqlst);
while($rst=mysqli_fetch_row($qry)){
$tgv=$tgv+$rst[1];
$acv=$acv+$rst[2];
$dvl=$dvl+$rst[3];
$vattot=$vattot+$rst[4];


if($rst[1]==0){
$pcs=0;}
else{
$pcs=$rst[2]*100/$rst[1];}
	
printf("<tr style='border-bottom:1px solid silver;'><td>%s</td>
<td align='right'>%s</td><td align='right'>%s</td><td align='center'>%s%%</td><td align='right'>%s</td><td align='right'>%s</td><td align='right'>%s</td></tr>",
$rst[0],number_format($rst[1],2,'.',','),number_format($rst[2],2,'.',','),
number_format($pcs,2,'.',','),number_format($rst[4],2,'.',','),number_format($rst[3],2,'.',','),number_format($rst[2]+$rst[3]+$rst[4],2,'.',','));
}


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

printf("<tr style='border-bottom:1px solid silver;font-weight:bold;'><td>Total</td>
<td align='right'>%s</td><td align='right'>%s</td><td align='center'>%s%%</td><td align='right'>%s</td><td align='right'>%s</td><td align='right'>%s</td></tr></table>",
number_format($tgv,2,'.',','),number_format($acv,2,'.',','),
number_format($pcs,2,'.',','),number_format($vattot,2,'.',','),number_format($dvl,2,'.',','),number_format($acv+$dvl,2,'.',','));



printf("<BR>");


echo "<div style='display:block;font-size:14;left:5%;' id='tdta'>";
echo "<table id='dtda' style='font-size:13;width:75%;color:#2E2E2E;'><th style='color:white;font-size:13;background:#5D6D7E;border:1px solid grey;' colspan='7' align='left'><b>Primary-Range Sales trend - Group</th>";

echo "<tr class='cls' style='border:1px solid silver;color:#2E2E2E;'><td padding=1% align='center' bgcolor='silver'>Range</td>";

$arr = array();

// Get the current month and year
$currentMonth = date('m');
$currentYear = date('Y');

// Prepare to get the last 5 months in ascending order
$monthsList = [];
for ($cnt = 0; $cnt < 5; $cnt++) {
    // Calculate the previous months and handle the year decrement
    $month = ($currentMonth - 5 + $cnt - 12) % 12;
    $year = $currentYear;
    
    if ($month <= 0) {
        $month += 12;
        $year--;
    }

    $dates = date_create($year . '-' . $month);
    $monthsList[] = [
        'year' => $year,
        'month' => $month,
        'date' => $dates,
    ];
}

// Build SQL statement and output months
$sqlst = "SELECT mranges";
$rsqlst = "";

foreach ($monthsList as $monthData) {
    $month = $monthData['month'];
    $year = $monthData['year'];
    $date = $monthData['date'];

    echo sprintf("<td padding=1%% align='center' style='background:silver;'>%s</td>", date_format($date, 'M-Y'));

    $sqlst = $sqlst . ", xx" . $month . $year . ".vls";

    $rsqlst = $rsqlst . " LEFT JOIN (SELECT item_ranges AS tranges, SUM(box_qty*box_price)/1000000 AS vls FROM rc_invoices " . $flt . " AND dates >= '" . $year . "-" . $month . "-1' AND dates <= '" . $year . "-" . $month . "-" . cal_days_in_month(CAL_GREGORIAN, $month, $year) . "' GROUP BY item_ranges) AS xx" . $month . $year . " ON xx" . $month . $year . ".tranges = mitems.mranges ";
}

$sqlst = $sqlst . " FROM (SELECT DISTINCT item_ranges AS mranges FROM itemsmrd " . $rlt . ") AS mitems ";
$sqlst = $sqlst . $rsqlst . " ORDER BY mranges ";

// echo $sqlst;

echo "</tr>";

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

while ($rst = mysqli_fetch_row($qry)) {
    $ttv = 0;

    for ($cn = 1; $cn < $fcn; $cn++) {
        $ttv = $ttv + $rst[$cn];
    }

    if ($ttv != 0) {
        echo sprintf("<tr class='cls' style='border:1px solid silver;'><td padding=1%%>%s</td>", $rst[0]);

        for ($cn = 1; $cn < $fcn; $cn++) {
            $arr[$cn] = $arr[$cn] + $rst[$cn];
            echo sprintf("<td align='right' padding=1%%>%s</td>", number_format($rst[$cn], 2, '.', ','));
        }
        echo "</tr>";
    }
}

$ttv = 0;
echo "<tr class='cls' style='font-weight:bold;border:1px solid silver;'><td>Total</td>";
for ($cn = 1; $cn < $fcn; $cn++) {
    $ttv = $ttv + $arr[$cn];
    echo sprintf("<td align='right' padding=1%%>%s</td>", number_format($arr[$cn], 2, '.', ','));
}
echo "</tr></table>";


printf("<BR>");
printf("</table>");
echo "<div style='display:block;font-size:14;left:5%;' id='tdta'>";
echo "<table id='dtda' style='font-size:13;width:75%;color:#2E2E2E;'><th style='color:white;font-size:13;background:#5D6D7E;border:1px solid grey;' colspan='7' align='left'><b>Catergory Sales-GT " . date('Y-M') . "</th>";
printf("<table id='dt' style='width:75%%;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;'>Target Value</td>
<td style='border:1px solid white;text-align:center;'>Primary Sale</td>
<td style='border:1px solid white;text-align:center;'>Pri%%</td>
<td style='border:1px solid white;text-align:center;'>Pri-Diff</td>
<td style='border:1px solid white;text-align:center;'>RD Sale</td>
<td style='border:1px solid white;text-align:center;'>Rd%%</td>
<td style='border:1px solid white;text-align:center;'>Target-Ton</td>
<td style='border:1px solid white;text-align:center;'>RD-Ton</td>
<td style='border:1px solid white;text-align:center;'>Primary-Ton</td>
<td style='border:1px solid white;text-align:center;'>Ton%%</td>
<td style='border:1px solid white;text-align:center;'>Ton-Diff</td>

</tr>");

$sqlst="select mranges,tgvl,prvl,odvl,rdvl,prvldir,prton,trton,odton,prdton,rddton from (select distinct item_ranges as mranges from rc_invoices ".$flt." and dates>='".$starts."' and dates<='".$ends."' union select distinct item_ranges from rc_targets ".$flt." and dates>='".$starts."' and dates<='".
$ends."' union select distinct item_ranges from rc_orders ".$flt." and dates>='".$starts."' and dates<='".
$ends."' union select distinct item_ranges from rd_saleinv ".$flt." and dates>='".$starts."' and dates<='".
$ends."') as ags 

left join (select item_ranges,sum(box_qty*box_price)/1000000 as tgvl,sum(box_qty*packets_per_box*packet_weights/1000000) as trton from rc_targets ".$flt." and 
dates>='".$starts."' and dates<='".$ends."' and chanell='GENERAL TRADE' group by item_ranges) as tgs on tgs.item_ranges=mranges

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

left join (select item_ranges,sum(box_qty*box_price)/1000000 as prvldir,sum(box_qty*packets_per_box*packet_weights/1000000) as prdton from rc_invoices ".$flt." and dates>='".$starts."' and dates<='".$ends.
"' and chanell='GENERAL TRADE' and agent_types='DIRECT' AND zones<>'H-OFFICE' group by item_ranges) as acsdir on acsdir.item_ranges=mranges

left join (select item_ranges,sum(pcs_qty*pcs_price)/1000000 as rdvl,sum(pcs_qty*packet_weights/1000000) as rddton from rd_saleinv ".$flt." and dates>='".$starts."' and dates<='".$ends.
"' group by item_ranges) as rdc on rdc.item_ranges=mranges

left join (select item_ranges,sum(box_qty*box_price) as odvl,sum(box_qty*packets_per_box*packet_weights/1000000) as odton from rc_orders ".$flt." and dates>='".$starts."' and dates<='".$ends.
"' and order_status<>'CANCELLED' and chanell='GENERAL TRADE' AND zones<>'H-OFFICE' group by item_ranges) as acss on acss.item_ranges=mranges";
 
$tgv=0;
$acv=0;
$tgw=0;
$acw=0;
$rdsl=0;
$rdqtt=0;
$optstktot=0;
$clostktot=0;
$prton=0;
$trton=0;
$totodton=0;
$tottondif=0;
$rdtontot=0;

$qry=mysqli_query($conn,$sqlst);
while($rst=mysqli_fetch_row($qry)){
$tgvll=$tgvll+$rst[1];
$prvll=$prvll+$rst[2];
$odwl=$odwl+$rst[3];
$rdsl=$rdsl+$rst[4]+$rst[5];
//$optstktot=$optstktot+$rst[7]+$rst[9]+$rst[11]-$rst[8];
//$clostktot=$clostktot+$rst[6]+$rst[7]+$rst[10]-$rst[5];
$prton=$prton+$rst[6];
$trton=$trton+$rst[7];
$diftot=$diftot+$rst[1]-$rst[2];
$tondif=$tondif+$rst[7]-$rst[6];
$totodton=$totodton+$rst[8];
$rdtontot=$rdtontot+$rst[9]+$rst[10];


if($rst[1]==0){
$prii=0;}
else{
$prii=$rst[2]*100/$rst[1];}
	
if($rst[1]==0){
$rdii=0;}
else{
$rdii=($rst[4]+$rst[5])*100/$rst[1];}

if($rst[7]==0){
    $prtonn=0;}
    else{
        $prtonn=$rst[6]*100/$rst[7];}
        
        
//This Need to check Later ///        

printf("<tr style='border-bottom:1px solid silver;'>
<td>%s</td>
<td align='right'>%s</td>
<td align='right'>%s</td>
<td align='right'style='color:black'bgcolor='#faa997'>%s%%</td>
<td align='right'>%s</td>
<td align='right'>%s</td>
<td align='right'style='color:black'bgcolor='#faa997'>%s%%</td>
<td align='right'>%s</td>
<td align='right'>%s</td>
<td align='right'>%s</td>
<td align='right'style='color:black'bgcolor='#faa997'>%s%%</td>
<td align='right'>%s</td>
",
$rst[0],
number_format($rst[1],2,'.',','),
number_format($rst[2],2,'.',','),
number_format($prii,1,'.',','),
number_format($rst[1]-$rst[2],2,'.',','),
number_format($rst[4]+$rst[5],2,'.',','),
number_format($rdii,1,'.',','),
number_format($rst[7],2,'.',','),
number_format($rst[9]+$rst[10],2,'.',','),
number_format($rst[6],2,'.',','),
number_format($prtonn,1,'.',','),
number_format($rst[7]-$rst[6],2,'.',','));
}


if($tgvll==0){
$pcs=0;}
else{
$pcs=$prvll*100/$tgvll;}

if($prton==0){
$toton=0;}
else{
$toton=$prton*100/$trton;}
	
if($tgvll==0){
$wpcs=0;}
else{
$wpcs=$rdsl*100/$tgvll;}

printf("<tr style='border-bottom:1px solid silver;font-weight:bold;'>
<td>Total</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></table>",
number_format($tgvll,2,'.',','),
number_format($prvll,2,'.',','),
number_format($pcs,1,'.',','),
number_format($diftot,2,'.',','),
number_format($rdsl,2,'.',','),
number_format($wpcs,1,'.',','),
number_format($trton,2,'.',','),
number_format($rdtontot,2,'.',','),
number_format($prton,2,'.',','),
number_format($toton,2,'.',','),
number_format($trton-$prton,2,'.',','));



printf("</div>");




echo "</div>";


printf("<BR>");
printf("</table>");
echo "<div style='display:block;font-size:14;left:5%;' id='tdta'>";
echo "<table id='dtda' style='font-size:13;width:75%;color:#2E2E2E;'><th style='color:white;font-size:13;background:#5D6D7E;border:1px solid grey;' colspan='7' align='left'><b>SM-Biscuit " . date('Y-M') . "</th>";
printf("<table id='dt' style='width:75%%;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;'>Target Value</td>
<td style='border:1px solid white;text-align:center;'>Primary Sale</td>
<td style='border:1px solid white;text-align:center;'>Pri%%</td>
<td style='border:1px solid white;text-align:center;'>Pri-Diff</td>
<td style='border:1px solid white;text-align:center;'>RD Sale</td>
<td style='border:1px solid white;text-align:center;'>Rd%%</td>
<td style='border:1px solid white;text-align:center;'>Target-Ton</td>
<td style='border:1px solid white;text-align:center;'>RD-Ton</td>
<td style='border:1px solid white;text-align:center;'>Primary-Ton</td>
<td style='border:1px solid white;text-align:center;'>Ton%%</td>
<td style='border:1px solid white;text-align:center;'>Ton-Diff</td>

</tr>");

$sqlst="select mranges,tgvl,prvl,odvl,rdvl,prvldir,prton,trton,odton,prdton,rddton from (select distinct item_ranges as mranges from rc_invoices ".$flt." and dates>='".$starts."' and dates<='".$ends."' AND item_ranges IN ('BISCUITS', 'GEM', 'CRACKER', 'WAFER') union select distinct item_ranges from rc_targets ".$flt." and dates>='".$starts."' and dates<='".
$ends."' AND item_ranges IN ('BISCUITS', 'GEM', 'CRACKER', 'WAFER') union select distinct item_ranges from rc_orders ".$flt." and dates>='".$starts."' and dates<='".
$ends."' AND item_ranges IN ('BISCUITS', 'GEM', 'CRACKER', 'WAFER') union select distinct item_ranges from rd_saleinv ".$flt." and dates>='".$starts."' and dates<='".
$ends."' AND item_ranges IN ('BISCUITS', 'GEM', 'CRACKER', 'WAFER')) as ags 

left join (select item_ranges,sum(box_qty*box_price)/1000000 as tgvl,sum(box_qty*packets_per_box*packet_weights/1000000) as trton from rc_targets ".$flt." and 
dates>='".$starts."' and dates<='".$ends."' AND item_ranges IN ('BISCUITS', 'GEM', 'CRACKER', 'WAFER') and chanell='GENERAL TRADE' group by item_ranges) as tgs on tgs.item_ranges=mranges

left join (select item_ranges,sum(box_qty*box_price)/1000000 as prvl,sum(box_qty*packets_per_box*packet_weights/1000000) as prton from rc_invoices ".$flt." and dates>='".$starts."' and dates<='".$ends.
"' AND item_ranges IN ('BISCUITS', 'GEM', 'CRACKER', 'WAFER') and chanell='GENERAL TRADE' AND zones<>'H-OFFICE' group by item_ranges) as acs on acs.item_ranges=mranges

left join (select item_ranges,sum(box_qty*box_price)/1000000 as prvldir,sum(box_qty*packets_per_box*packet_weights/1000000) as prdton from rc_invoices ".$flt." and dates>='".$starts."' and dates<='".$ends.
"' AND item_ranges IN ('BISCUITS', 'GEM', 'CRACKER', 'WAFER') and chanell='GENERAL TRADE' and agent_types='DIRECT' AND zones<>'H-OFFICE' group by item_ranges) as acsdir on acsdir.item_ranges=mranges

left join (select item_ranges,sum(pcs_qty*pcs_price)/1000000 as rdvl,sum(pcs_qty*packet_weights/1000000) as rddton from rd_saleinv ".$flt." and dates>='".$starts."' and dates<='".$ends.
"' AND item_ranges IN ('BISCUITS', 'GEM', 'CRACKER', 'WAFER') group by item_ranges) as rdc on rdc.item_ranges=mranges

left join (select item_ranges,sum(box_qty*box_price) as odvl,sum(box_qty*packets_per_box*packet_weights/1000000) as odton from rc_orders ".$flt." and dates>='".$starts."' and dates<='".$ends.
"' and order_status<>'CANCELLED' and chanell='GENERAL TRADE' AND zones<>'H-OFFICE' AND item_ranges IN ('BISCUITS', 'GEM', 'CRACKER', 'WAFER') group by item_ranges) as acss on acss.item_ranges=mranges";
 
$tgvr=0;
$acvr=0;
$tgwr=0;
$acwr=0;
$rdslr=0;
$rdqttr=0;
$optstktotr=0;
$clostktotr=0;
$prtonr=0;
$trtonr=0;
$totodtonr=0;
$tottondifr=0;
$rdtontotr=0;

$qry=mysqli_query($conn,$sqlst);
while($rstt=mysqli_fetch_row($qry)){
$tgvllrT=$tgvllrT+$rstt[1];
$prvllrT=$prvllrT+$rstt[2];
$odwlrT=$odwlrT+$rstt[3];
$rdslrT=$rdslrT+$rstt[4]+$rstt[5];
//$optstktot=$optstktot+$rst[7]+$rst[9]+$rst[11]-$rst[8];
//$clostktot=$clostktot+$rst[6]+$rst[7]+$rst[10]-$rst[5];
$prtonrT=$prtonrT+$rstt[6];
$trtonrT=$trtonrT+$rstt[7];
$diftotrT=$diftotrT+$rstt[1]-$rstt[2];
$tondifrT=$tondifrT+$rstt[7]-$rstt[6];
$totodtonrT=$totodtonrT+$rstt[8];
$rdtontotrT=$rdtontotrT+$rstt[9]+$rstt[10];


if($rstt[1]==0){
$priir=0;}
else{
$priir=$rstt[2]*100/$rstt[1];}
	
if($rstt[1]==0){
$rdiir=0;}
else{
$rdiir=($rstt[4]+$rstt[5])*100/$rstt[1];}

if($rstt[7]==0){
    $prtonnr=0;}
    else{
        $prtonnr=$rstt[6]*100/$rstt[7];}
        
        
//This Need to check Later ///        

printf("<tr style='border-bottom:1px solid silver;'>
<td>%s</td>
<td align='right'>%s</td>
<td align='right'>%s</td>
<td align='right'style='color:black'bgcolor='#faa997'>%s%%</td>
<td align='right'>%s</td>
<td align='right'>%s</td>
<td align='right'style='color:black'bgcolor='#faa997'>%s%%</td>
<td align='right'>%s</td>
<td align='right'>%s</td>
<td align='right'>%s</td>
<td align='right'style='color:black'bgcolor='#faa997'>%s%%</td>
<td align='right'>%s</td>
",
$rstt[0],
number_format($rstt[1],2,'.',','),
number_format($rstt[2],2,'.',','),
number_format($priir,1,'.',','),
number_format($rstt[1]-$rstt[2],2,'.',','),
number_format($rstt[4]+$rstt[5],2,'.',','),
number_format($rdiir,1,'.',','),
number_format($rstt[7],2,'.',','),
number_format($rstt[9]+$rstt[10],2,'.',','),
number_format($rstt[6],2,'.',','),
number_format($prtonnr,1,'.',','),
number_format($rstt[7]-$rstt[6],2,'.',','));
}


if($tgvllT==0){
$pcsrT=0;}
else{
$pcsrT=$prvlrlT*100/$tgvllrT;}

if($prtonrT==0){
$totonrT=0;}
else{
$totonrT=$prtonTr*100/$trtonrT;}
	
if($tgvllrT==0){
$wpcsrT=0;}
else{
$wpcsrT=$rdslrT*100/$tgvllrT;}

printf("<tr style='border-bottom:1px solid silver;font-weight:bold;'>
<td>Total</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></table>",
number_format($tgvllrT,2,'.',','),
number_format($prvllrT,2,'.',','),
number_format($prvllrT/$tgvllrT*100 ,1,'.',','),
number_format($diftotrT,2,'.',','),
number_format($rdslrT,2,'.',','),
number_format($wpcsrT,1,'.',','),
number_format($trtonrT,2,'.',','),
number_format($rdtontotrT,2,'.',','),
number_format($prtonrT,2,'.',','),
number_format($totonrT,2,'.',','),
number_format($trtonrT-$prtonrT,2,'.',','));

printf("<BR>");

echo "<div style='display:block;font-size:14;left:5%;' id='tdta'>";
echo "<table id='dtda' style='font-size:13;width:75%;color:#2E2E2E;'><th style='color:white;font-size:13;background:#5D6D7E;border:1px solid grey;' colspan='7' align='left'><b>SM-Chocolate " . date('Y-M') . "</th>";
printf("<table id='dt' style='width:75%%;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;'>Target Value</td>
<td style='border:1px solid white;text-align:center;'>Primary Sale</td>
<td style='border:1px solid white;text-align:center;'>Pri%%</td>
<td style='border:1px solid white;text-align:center;'>Pri-Diff</td>
<td style='border:1px solid white;text-align:center;'>RD Sale</td>
<td style='border:1px solid white;text-align:center;'>Rd%%</td>
<td style='border:1px solid white;text-align:center;'>Target-Ton</td>
<td style='border:1px solid white;text-align:center;'>RD-Ton</td>
<td style='border:1px solid white;text-align:center;'>Primary-Ton</td>
<td style='border:1px solid white;text-align:center;'>Ton%%</td>
<td style='border:1px solid white;text-align:center;'>Ton-Diff</td>

</tr>");

$sqlst="select mranges,tgvl,prvl,odvl,rdvl,prvldir,prton,trton,odton,prdton,rddton from (select distinct item_ranges as mranges from rc_invoices ".$flt." and dates>='".$starts."' and dates<='".$ends."' AND item_ranges IN ('CHOCOLATE', 'SNACK') union select distinct item_ranges from rc_targets ".$flt." and dates>='".$starts."' and dates<='".
$ends."' AND item_ranges IN ('CHOCOLATE', 'SNACK') union select distinct item_ranges from rc_orders ".$flt." and dates>='".$starts."' and dates<='".
$ends."' AND item_ranges IN ('CHOCOLATE', 'SNACK') union select distinct item_ranges from rd_saleinv ".$flt." and dates>='".$starts."' and dates<='".
$ends."' AND item_ranges IN ('CHOCOLATE', 'SNACK')) as ags 

left join (select item_ranges,sum(box_qty*box_price)/1000000 as tgvl,sum(box_qty*packets_per_box*packet_weights/1000000) as trton from rc_targets ".$flt." and 
dates>='".$starts."' and dates<='".$ends."' AND item_ranges IN ('CHOCOLATE', 'SNACK') and chanell='GENERAL TRADE' group by item_ranges) as tgs on tgs.item_ranges=mranges

left join (select item_ranges,sum(box_qty*box_price)/1000000 as prvl,sum(box_qty*packets_per_box*packet_weights/1000000) as prton from rc_invoices ".$flt." and dates>='".$starts."' and dates<='".$ends.
"' AND item_ranges IN ('CHOCOLATE', 'SNACK') and chanell='GENERAL TRADE' AND zones<>'H-OFFICE' group by item_ranges) as acs on acs.item_ranges=mranges

left join (select item_ranges,sum(box_qty*box_price)/1000000 as prvldir,sum(box_qty*packets_per_box*packet_weights/1000000) as prdton from rc_invoices ".$flt." and dates>='".$starts."' and dates<='".$ends.
"' AND item_ranges IN ('CHOCOLATE', 'SNACK') and chanell='GENERAL TRADE' and agent_types='DIRECT' AND zones<>'H-OFFICE' group by item_ranges) as acsdir on acsdir.item_ranges=mranges

left join (select item_ranges,sum(pcs_qty*pcs_price)/1000000 as rdvl,sum(pcs_qty*packet_weights/1000000) as rddton from rd_saleinv ".$flt." and dates>='".$starts."' and dates<='".$ends.
"' AND item_ranges IN ('CHOCOLATE', 'SNACK') group by item_ranges) as rdc on rdc.item_ranges=mranges

left join (select item_ranges,sum(box_qty*box_price) as odvl,sum(box_qty*packets_per_box*packet_weights/1000000) as odton from rc_orders ".$flt." and dates>='".$starts."' and dates<='".$ends.
"' and order_status<>'CANCELLED' and chanell='GENERAL TRADE' AND zones<>'H-OFFICE' AND item_ranges IN ('CHOCOLATE', 'SNACK') group by item_ranges) as acss on acss.item_ranges=mranges";
 
$tgvr=0;
$acvr=0;
$tgwr=0;
$acwr=0;
$rdslr=0;
$rdqttr=0;
$optstktotr=0;
$clostktotr=0;
$prtonr=0;
$trtonr=0;
$totodtonr=0;
$tottondifr=0;
$rdtontotr=0;

$qry=mysqli_query($conn,$sqlst);
while($rstt=mysqli_fetch_row($qry)){
$tgvllr=$tgvllr+$rstt[1];
$prvllr=$prvllr+$rstt[2];
$odwlr=$odwlr+$rstt[3];
$rdslr=$rdslr+$rstt[4]+$rstt[5];
//$optstktot=$optstktot+$rst[7]+$rst[9]+$rst[11]-$rst[8];
//$clostktot=$clostktot+$rst[6]+$rst[7]+$rst[10]-$rst[5];
$prtonr=$prtonr+$rstt[6];
$trtonr=$trtonr+$rstt[7];
$diftotr=$diftotr+$rstt[1]-$rstt[2];
$tondifr=$tondifr+$rstt[7]-$rstt[6];
$totodtonr=$totodtonr+$rstt[8];
$rdtontotr=$rdtontotr+$rstt[9]+$rstt[10];


if($rstt[1]==0){
$priir=0;}
else{
$priir=$rstt[2]*100/$rstt[1];}
	
if($rstt[1]==0){
$rdiir=0;}
else{
$rdiir=($rstt[4]+$rstt[5])*100/$rstt[1];}

if($rstt[7]==0){
    $prtonnr=0;}
    else{
        $prtonnr=$rstt[6]*100/$rstt[7];}
        
        
//This Need to check Later ///        

printf("<tr style='border-bottom:1px solid silver;'>
<td>%s</td>
<td align='right'>%s</td>
<td align='right'>%s</td>
<td align='right'style='color:black'bgcolor='#faa997'>%s%%</td>
<td align='right'>%s</td>
<td align='right'>%s</td>
<td align='right'style='color:black'bgcolor='#faa997'>%s%%</td>
<td align='right'>%s</td>
<td align='right'>%s</td>
<td align='right'>%s</td>
<td align='right'style='color:black'bgcolor='#faa997'>%s%%</td>
<td align='right'>%s</td>
",
$rstt[0],
number_format($rstt[1],2,'.',','),
number_format($rstt[2],2,'.',','),
number_format($priir,1,'.',','),
number_format($rstt[1]-$rstt[2],2,'.',','),
number_format($rstt[4]+$rstt[5],2,'.',','),
number_format($rdiir,1,'.',','),
number_format($rstt[7],2,'.',','),
number_format($rstt[9]+$rstt[10],2,'.',','),
number_format($rstt[6],2,'.',','),
number_format($prtonnr,1,'.',','),
number_format($rstt[7]-$rstt[6],2,'.',','));
}


if($tgvll==0){
$pcsr=0;}
else{
$pcsr=$prvlrl*100/$tgvllr;}

if($prtonr==0){
$totonr=0;}
else{
$totonr=$prtonr*100/$trtonr;}
	
if($tgvllr==0){
$wpcsr=0;}
else{
$wpcsr=$rdslr*100/$tgvllr;}

printf("<tr style='border-bottom:1px solid silver;font-weight:bold;'>
<td>Total</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></table>",
number_format($tgvllr,2,'.',','),
number_format($prvllr,2,'.',','),
number_format($prvllr/$tgvllr*100 ,1,'.',','),
number_format($diftotr,2,'.',','),
number_format($rdslr,2,'.',','),
number_format($wpcsr,1,'.',','),
number_format($trtonr,2,'.',','),
number_format($rdtontotr,2,'.',','),
number_format($prtonr,2,'.',','),
number_format($totonr,2,'.',','),
number_format($trtonr-$prtonr,2,'.',','));
printf("</div>");




echo "</div>";





/////////////////////////////////////////////////

printf("<BR>");


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


  
printf("<div class='rds' style='left:70%%;'>");

#printf("<BR><table style='font-size:13;'>");
#printf("<tr><td>%s - Invoice list</td></tr>",date('Y-m-d'));
#printf("<tr><td>%s - Item list</td></tr>",date('Y-m-d'));
#printf("<tr><td>%s - Agents list</td></tr>",date('Y-m-d'));
#printf("</tr></table>");

$sprt="?actp=rtp&zones=all&regions=all&agents=all&agenttypes=all&terrys=all&items=all&ranges=all&subranges=all&starts=".
$tdates."&ends=".$tdates."' target='_blank'";


printf("<BR><table style='width:95%%;height:9%%;font-size:13;color:silver;'>
<tr><td style='background:none;text-align:left;color:#006699;'>%s</td>
<td style='border-left:1px solid silver;background:none;text-align:center'><a href='rpmpragentinvoices.php?%s>Goto invoices</a></td>
<td style='border-left:1px solid silver;background:none;text-align:center'><a href='rpmpragentsales.php?%s>Goto Agents</a></td>
<td style='border-left:1px solid silver;background:none;text-align:center'><a href='vtprrtitemwise.php?%s>Goto Items</a></td>
</tr></table>",$tdates,$sprt,$sprt,$sprt);

printf("<BR><BR><table style='font-size:13;border:0px solid silver;border-collapse:collapse;width:110%%;color:grey;'>
<th colspan='2' style='font-size:18;color:grey;font-weight:normal;text-align:left;border-bottom:1px solid silver;'>Daily sales -GT</th>
<tr style='background:silver;font-size:14;color:white;text-align:center;border:1px solid silver;'>
<td style='border:1px solid white;text-align:center;'>Date</td>
<td style='border:1px solid white;text-align:center;'>Primary</td>
<td style='border:1px solid white;text-align:center;'>RD</td>
</tr>");

$sqlst="select mzones,prvl,rdvl,prvll from (select distinct dates as mzones from rc_invoices ".$flt." and dates>='".date('Y-m-01').
"' and dates<='".date('Y-m-t')."' union select distinct dates as mzones from rd_saleinv ".$flt." and dates>='".date('Y-m-01').
"' and dates<='".date('Y-m-t')."') as ags

left join (select dates,sum(box_qty*box_price)as prvl from rc_invoices ".$flt." and dates>='".date('Y-m-01').
"' and dates<='".date('Y-m-t')."' AND chanell='GENERAL TRADE' group by dates) as prpr on prpr.dates=mzones

left join (select dates,sum(pcs_qty*pcs_price)as rdvl from rd_saleinv ".$flt." and dates>='".date('Y-m-01').
"' and dates<='".date('Y-m-t')."' group by dates) as rdrd on rdrd.dates=mzones

left join (select dates,sum(box_qty*box_price)as prvll from rc_invoices ".$flt." and dates>='".date('Y-m-01').
"' and dates<='".date('Y-m-t')."' AND chanell='GENERAL TRADE' AND agent_types='DIRECT' group by dates) as prprr on prprr.dates=mzones order by mzones";

$odtt=0;
$prtt=0;
$rdtt=0;

$qry=mysqli_query($conn,$sqlst);
while($rst=mysqli_fetch_row($qry)){
//$odtt=$odtt+$rst[3];
$prtt=$prtt+$rst[1];
$rdtt=$rdtt+$rst[2]+$rst[3];

printf("<tr style='border:1px solid silver;'>
<td>%s</td>
<td align='right'>%s</td>
<td align='right'>%s</td></tr>",
$rst[0],
number_format($rst[1],0,'.',','),
number_format(($rst[2]+$rst[3]),0,'.',','));	
}

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


printf("</table>");


printf("</div>");
printf("<div class='shws' style='display:none;' id='dvshw'>dsalkfjaskljflkajslkfjasdlkfjklasjflkj</div>");
}
}
?>

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


