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

<html>
<title>Prasanna Mallawa</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';
}
</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=mysql_connect("localhost","primarysales","S5L7lF01EQZvQrGK");
mysql_select_db("primarysales_dianaold",$conn);


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';}

#$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($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=mysql_connect("localhost","primarysales","S5L7lF01EQZvQrGK");
mysql_select_db("primarysales_dianaold",$conn);



stps('pr');
printf("<div class='sds'>");
prshows('pr','startups','gobs','sales','Tonnage trend',$zones,$regions,$agents,$agenttypes,$terrys,$ranges,$subranges,$items);


Printf("<br><br>");
printf("<a href='prdashbsss.php'>System Admin</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("</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=mysql_query($sqlst,$conn);
if(mysql_num_rows($qry)==0){
$tdates=date('Y-m-d');}
else{
$rst=mysql_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</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=mysql_query($sqlst,$conn);
while($rst=mysql_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."') as ac ";
$qry=mysql_query($sqlst,$conn);
$rst=mysql_fetch_row($qry);

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


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

printf("<BR>");

printf("<table id='dta' style='width:75%%;font-size:13;border-collapse:collapse;color:#006699;border:1px solid silver;'>");
printf("<tr><td colspan='3' style='color:grey;border:1px solid white;font-size:16;'>Channel wise achievement - %s</td></tr>",date('Y-M'));
printf("<tr style='background:silver;border:1px solid white;text-align:right;'><td style='border:1px solid white;text-align:left;border'>Channel</td><td style='border:1px solid white;'>Target</td>
<td style='border:1px solid white;'>Gros-Sale</td><td style='border:1px solid white;text-align:center;'>Ach:</td><td>Variance</td><td>Discount</td><td>Net-Sale</td></tr>");

$sqlst="select mzones,tgvl,acvl,disvl from (select distinct chanell as mzones from rc_invoices where chanell<>''and zones<>'zone-1' order by zone_orders) as ags left join 
(select chanell,sum(box_qty*box_price) 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) as acvl,sum((box_qty*box_price)*discount_rates) as disvl 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;
$qry=mysql_query($sqlst,$conn);
while($rst=mysql_fetch_row($qry)){
$tgv=$tgv+$rst[1];
$acv=$acv+$rst[2];
$dvl=$dvl+$rst[3];

if($rst[1]==0){
$pcs=100;}
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],0,'.',','),number_format($rst[2],0,'.',','),
number_format($pcs,0,'.',','),number_format($rst[2]-$rst[1],0,'.',','),number_format($rst[3],0,'.',','),number_format($rst[2]+$rst[3],0,'.',','));
}
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,0,'.',','),number_format($acv,0,'.',','),
number_format($pcs,0,'.',','),number_format($tgv-$acv,0,'.',','),number_format($dvl,0,'.',','),number_format($acv+$dvl,0,'.',','));


printf("<BR>");

printf("<table id='dta' style='width:75%%;font-size:13;border-collapse:collapse;color:#006699;border:1px solid silver;'>");
printf("<tr><td colspan='3' style='color:grey;border:1px solid white;font-size:16;'>Zone wise achievement - %s</td></tr>",date('Y-M'));
printf("<tr style='background:silver;border:1px solid white;text-align:right;'><td style='border:1px solid white;text-align:left;border'>Zone</td><td style='border:1px solid white;'>Target</td>
<td style='border:1px solid white;'>Gros-Sale</td><td style='border:1px solid white;text-align:center;'>Ach:</td><td>Variance</td><td>Discount</td><td>Net-Sale</td></tr>");

$sqlst="select mzones,tgvl,acvl,dvl from (select distinct zones as mzones from rc_invoices where chanell<>''and zones<>'zone-1' order by zone_orders) as ags left join 
(select zones,sum(box_qty*box_price) as tgvl from rc_targets ".$flt." and 
dates>='".$starts."' and dates<='".$ends."' group by zones) as tgs on tgs.zones=mzones left join (select zones,sum(box_qty*box_price) as acvl,sum((box_qty*box_price)*discount_rates) as dvl from rc_invoices ".$flt." and dates>='".$starts."' and dates<='".$ends."' group by zones) 
as acs on acs.zones=mzones ";
 
$tgv=0;
$acv=0;
$dvl=0;
 
$qry=mysql_query($sqlst,$conn);
while($rst=mysql_fetch_row($qry)){
$tgv=$tgv+$rst[1];
$acv=$acv+$rst[2];
$dvl=$dvl+$rst[3];

if($rst[1]==0){
$pcs=100;}
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],0,'.',','),number_format($rst[2],0,'.',','),
number_format($pcs,0,'.',','),number_format($rst[2]-$rst[1],0,'.',','),number_format($rst[3],0,'.',','),number_format($rst[2]+$rst[3],0,'.',','));
}


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,0,'.',','),number_format($acv,0,'.',','),
number_format($pcs,0,'.',','),number_format($tgv-$acv,0,'.',','),number_format($dvl,0,'.',','),number_format($acv+$dvl,0,'.',','));

printf("<BR>");



printf("<table id='dtb' style='width:75%%;font-size:13;border-collapse:collapse;color:#006699;border:1px solid silver;'>");
printf("<tr><td colspan='3' style='color:grey;border:1px solid white;font-size:16;'>Group Range wise achievement - %s</td></tr>",date('Y-M'));
printf("<tr style='background:silver;border:1px solid white;text-align:right;'><td style='border:1px solid white;text-align:left;'>Range</td><td style='border:1px solid white;'>Target</td>
<td style='border:1px solid white;'>Gros-Sale</td><td style='border:1px solid white;text-align:center;'>Ach:</td><td>Variance</td><td>Discount</td><td>Net-Sale</td></tr>");

$sqlst="select mzones,tgvl,acvl,dvl from (select distinct item_ranges as mzones from itemsmrd   where item_status='Active' order by item_ranges) as ags left join 
(select item_ranges,sum(box_qty*box_price) as tgvl from rc_targets ".$flt." and 
dates>='".$starts."' and dates<='".$ends."' group by item_ranges) as tgs on tgs.item_ranges=mzones left join (select item_ranges,sum(box_qty*box_price) as acvl,sum((box_qty*box_price)*discount_rates) as dvl from rc_invoices ".$flt." and dates>='".$starts."' and dates<='".$ends."' group by item_ranges) 
as acs on acs.item_ranges=mzones ";

 
$tgv=0;
$acv=0;
$dvl=0;

 
$qry=mysql_query($sqlst,$conn);
while($rst=mysql_fetch_row($qry)){
$tgv=$tgv+$rst[1];
$acv=$acv+$rst[2];
$dvl=$dvl+$rst[3];

if($rst[1]==0){
$pcs=100;}
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],0,'.',','),number_format($rst[2],0,'.',','),
number_format($pcs,0,'.',','),number_format($rst[2]-$rst[1],0,'.',','),number_format($rst[3],0,'.',','),number_format($rst[2]+$rst[3],0,'.',','));
}


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,0,'.',','),number_format($acv,0,'.',','),
number_format($pcs,0,'.',','),number_format($tgv-$acv,0,'.',','),number_format($dvl,0,'.',','),number_format($acv-$dvl,0,'.',','));



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

printf("<BR>");


printf("<table id='dta' style='width:75%%;font-size:13;border-collapse:collapse;color:#006699;border:1px solid silver;'>");
printf("<tr><td colspan='3' style='color:grey;border:1px solid white;font-size:16;'>Channel Range achievement - %s</td></tr>",date('Y-M'));
$sqlst="select distinct item_ranges as mranges from rc_invoices ".$flt." and dates>='".$starts."' and dates<='".$ends."' ";
$aqry=mysql_query($sqlst,$conn);

$rsqlst=" from (select distinct chanell as mzones from rc_invoices ".$flt." and  dates>='".$starts."' and dates<='".$ends."' order by chanell) as ags ";
$sqlst="select mzones";

printf("<table id='dt' style='width:100%%;font-size:13;border-collapse:collapse;color:#006699;'><tr><td colspan='2' style='background:silver;border:1px solid white;'>Channel</td>");

while($arst=mysql_fetch_row($aqry)){
$sqlst=$sqlst.",COALESCE(tgrn".$arst[0].".vl,0),COALESCE(acrn".$arst[0].".vl,0)";
$rsqlst=$rsqlst." left join (select chanell,sum(box_qty*box_price) as vl from rc_targets ".$flt." and dates>='".$starts."' and dates<='".$ends.
"'  and item_ranges='".$arst[0]."' group by chanell) as tgrn".$arst[0]." on tgrn".$arst[0].".chanell=mzones left join 
(select chanell,sum(box_qty*box_price) as vl from rc_invoices ".$flt." and dates>='".$starts."' and dates<='".$ends."' and item_ranges='".$arst[0].
"' group by chanell) as acrn".$arst[0]." on acrn".$arst[0].".chanell=mzones ";

printf("<td align='center' style='background:silver;border:1px solid white;margin-top:2%%;'>%s</td>",$arst[0]);
}

$sqlst=$sqlst.$rsqlst;
printf("<td style='background:silver;border:1px solid white;' align='center'>Total</td></tr>");

#echo $sqlst;

$qry=mysql_query($sqlst,$conn);
$rn=mysql_num_fields($qry);

$tv=0;
$tf=0;
$ttv=0;
$ttf=0;

while($rst=mysql_fetch_row($qry)){
$cn=0;
	
printf("<tr style='border-bottom:1px solid silver;'><td>%s</td><td>
<table style='width:100%%;font-size:13;color:#006699;'><tr><td>Target</td></tr><tr><td>Actual</td></tr></table>",$rst[0]);
for($cnt=1;$cnt<$rn;$cnt++){
$tgarr[$cn]=$tgarr[$cn]+$rst[$cnt];	
$acarr[$cn]=$acarr[$cn]+$rst[$cnt+1];	
$cn++;

$tf=$tf+$rst[$cnt];
$tv=$tv+$rst[$cnt+1];
$ttv=$ttv+$rst[$cnt];
$ttf=$ttf+$rst[$cnt+1];

if($rst[$cnt]==0){
$pcs=0.0;}
else{
$pcs=$rst[$cnt+1]*100/$rst[$cnt];}

printf("<td><table style='color:#006699;font-size:13;width:100%%;'><tr><td align='right' width=70%%>%s</td><td rowspan='2' align='right' width=30%%>%s%%</td></tr><tr>
<td align='right' width=70%%>%s</td></tr></table></td>",
number_format($rst[$cnt],0,'.',','),number_format($pcs,0,'.',','),number_format($rst[$cnt+1],0,'.',','));

$cnt++;
}

if($tf==0){
$pcs=0.0;}
else{
$pcs=$tv*100/$tf;}
printf("<td><table style='color:#006699;font-size:13;width:100%%;'><tr><td align='right'>%s</td><td rowspan='2' align='right'>%s%%</td></tr><tr>
<td align='right'>%s</td></tr></table></td></tr>",
number_format($tf,0,'.',','),number_format($pcs,0,'.',','),number_format($tv,0,'.',','));

$tf=0;
$tv=0;}

printf("<tr style='font-weight:bold;border-bottom:1px solid silver;'><td>Total</td><td><table style='font-size:13;color:#006699;'><tr style='font-weight:bold;'><td>Target</td>
</tr><tr style='font-weight:bold;'><td>Actual</td></tr></table></td>");

for($cnt=0;$cnt<($rn-1)/2;$cnt++){
if($tgarr[$cnt]==0){
$pcs=0.0;}
else{
$pcs=$acarr[$cnt]*100/$tgarr[$cnt];}
printf("<td><table style='color:#006699;font-size:13;width:100%%;'><tr style='font-weight:bold;'><td align='right' width=70%%>%s</td>
<td rowspan='2' align='right' width=30%%>%s%%</td></tr><tr style='font-weight:bold;'><td align='right'>%s</td></tr></table></td>",
number_format($tgarr[$cnt],0,'.',','),number_format($pcs,0,'.',','),number_format($acarr[$cnt],0,'.',','));}

if($ttv==0){
$pcs=0.0;}
else{
$pcs=$ttf*100/$ttv;}
printf("<td><table style='color:#006699;font-size:13;width:100%%;'><tr style='font-weight:bold;'><td align='right'>%s</td>
<td rowspan='2' align='right'>%s%%</td></tr><tr style='font-weight:bold;'><td align='right'>%s</td></tr></table></td>",
number_format($ttf,0,'.',','),number_format($pcs,0,'.',','),number_format($ttv,0,'.',','));

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






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

#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='vtpragentinvoices.php?%s>Goto invoices</a></td>
<td style='border-left:1px solid silver;background:none;text-align:center'><a href='vtpragentsales.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><table style='font-size:13;border:0px solid silver;border-collapse:collapse;width:95%%;color:grey;'>
<th colspan='2' style='font-size:18;color:grey;font-weight:normal;text-align:left;border-bottom:1px solid silver;'>Daily sales</th>
<tr style='background:silver;font-size:14;color:white;text-align:center;border:1px solid silver;'><td width=45%%>Date</td><td width=55%%>Sale</td></tr>");

$sqlst="select dates,sum((box_qty*box_price)+(pcs_qty*pcs_price)) from rc_invoices ".$flt." and dates>='".date('Y-m-01').
"' and dates<='".date('Y-m-t')."' group by dates order by dates";
$qry=mysql_query($sqlst,$conn);

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


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

}


?>

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


