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

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

if(isset($_REQUEST['tdates'])){
echo "starts ok";	
$tdates=$_REQUEST['tdates'];}
else{
$tdates=date('Y-m-d');}

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

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

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($fstores)!='all'){
$flt=$flt." and stores_names='".$fstores."' ";}
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($stores)!='all'){
$flt=$flt." and stores_names='".$stores."' ";}
if(strtolower($storesl)!='all'){
$flt=$flt." and locations='".$storesl."' ";}
?>

<html>
<title>Diana|Finish Goods</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>
//setTimeout(function(){ window.location = "vtprstdashbs.php"; },60000);

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");pend
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();
svl=dt.rows[cnt].cells[2].innerText.toLowerCase();

if(sv.length>1 && (tvl.indexOf(sv)>=0 || rvl.indexOf(sv)>=0 || svl.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;'>

<?php
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_diana");

$flt=racs(1);
stps(1);
printf("<div style='position:absolute;left:1%%;top:5%%;width:15%%;'>");
stksnew(1);//prshows('pr','zone','gobs','sales','Periodic sales',$zones,$regions,$agents,$agenttypes,$terrys,$ranges,$subranges,$items);

printf("</div>");
printf("<div style='position:absolute;left:15%%;top:5%%;width:80%%;'>");
printf("<div style='font-size:18;font-weight:bold;color:#3498DB;'>Trading - Chocolate</div>");

$tmpdt=date_create($tdates);
$starts=date_format($tmpdt,"Y-m-01");
$ends=$tdates;
$tmonths=date_format($tmpdt,"Y-m");


$sqlst="select max(dates) from st_notes where dates='".$tdates."' ";
$qry=mysqli_query($conn,$sqlst);
$rst=mysqli_fetch_row($qry);
if($rst[0]!=''){
$tdates=$rst[0];}

$sqlst="select dtypes,dvls,mvls from (select distinct note_types as dtypes from st_notes where note_types='Delivery note') as stypes

left join (select note_types,sum(box_price*box_qty) as dvls from st_notes where dates='".$tdates."' and note_types='Delivery note' group by note_types) as tds on dtypes=tds.note_types

left join (select note_types,sum(box_price*box_qty)as mvls from st_notes where dates>='".$starts."' and dates<='".$ends."'  and note_types='Delivery note' group by note_types) as tms on dtypes=tms.note_types ";

$tdv=0;
$tmv=0;
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

printf("<BR><table width='70%%'><tr><td width='40%%'>");
printf("<form method='post' action='vtprstdashbs.php?actp=dbsh'>
<table width='100%%' style='font-size:13;'><tr style='background:silver;border:1px solid silver;text-align:right;font-weight:bold;'>
<td align='left' width='40%%'>Summary</td>
<td width='30%%'>%s</td><td width='30%%'>%s</td></tr>",$tdates,$tmonths);

$tdv=0;
$tmv=0;
$qry=mysqli_query($conn,$sqlst);
while($rst=mysqli_fetch_row($qry)){
$tdv=$tdv+$rst[1];
$tmv=$tmv+$rst[2];

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],0,'.',','));
}
printf("<tr style='border:1px solid silver;'><td>Total</td><td align='right'>%s</td><td align='right'>%s</td></tr>",
number_format($tdv,0,'.',','),number_format($tmv,0,'.',','));

printf("<tr style='border:1px solid silver;'><td colspan='3' align='right' style='height:100%%;'>
<input type='text' id='inputField' name='tdates' style='color:grey;width:70%%;font-family:calibri;border:1px solid silver;text-align:center;' value='%s'>
<input type='submit' style='color:grey;border:1px solid silver;font-family:calibri;text-align:center;' value='Go'></td></tr></table></form>",$tdates);

$dts=date_create($tdates);
printf("</td><td width='60%%' align='center' style='color:#006699;font-size:18;font-weight:bold;'>%s</td></tr></table></td></tr></table>",
date_format($dts,"Y-M-d l"));

printf("<br>");

printf("<table width='90%%'><tr><td width='40%%' valign='top'>");

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

$sqlst="select dvh,dvs,dqs,mvh,mvs,mqs,dunit,munit from (select count(distinct vehicle_names)as dvh,sum(box_price*box_qty) as dvs,sum(box_qty*packets_per_box*packet_weights)/1000000 as dqs,sum(box_qty) as dunit from st_notes 
".$flt." and dates='".$tdates."' and note_types='Delivery note') as tds, (select count(distinct vehicle_names)as mva,sum(box_price*box_qty) as mvs,sum(box_qty*packets_per_box*packet_weights)/1000000 as mqs,sum(box_qty) as munit 
 from st_notes ".$flt." and dates>='".$starts."' and dates<='".$ends."' and note_types='Delivery note') as mds,(select sum(vts) as mvh from
 (select count(distinct vehicle_names) as vts from st_notes ".$flt." and dates>='".$starts."' and dates<='".$ends."' and note_types='Delivery note' 
 group by dates) as vns ) as vrn ";
 
$tdv=0;
$tmv=0;

$qry=mysqli_query($conn,$sqlst);
$rst=mysqli_fetch_row($qry);

printf("<table style='width:90%%;font-size:13;color:grey;'>");
printf("<tr style='border-bottom:1px solid #006699;text-align:right;font-weight:bold;background:#006699;color:white;'>
<td style='font-size:15;text-align:left;' width='100%%'>Delivery details</td></tr></table><br>");

printf("<table style='width:90%%;font-size:13;color:grey;'>");
printf("<tr style='border-bottom:1px solid #006699;text-align:right;font-weight:bold;color:#006699;'>
<td style='font-size:15;text-align:left;' width='50%%'>Delivery on vehicles</td><td width='25%%'>%s</td><td width='25%%'>%s</td></tr>",$tdates,$tmonths);

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

if($rst[0]==0){
$dpv=0;}
else{
$dpv=$rst[1]/$rst[0];}
	
if($rst[3]==0){
$mpv=0;}
else{
$mpv=$rst[4]/$rst[3];}

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

//printf("<tr style='border:0px solid silver;'><td>Value per vehicle</td><td align='right'>%s</td><td align='right'>%s</td></tr>",
//number_format($dpv,0,'.',','),number_format($mpv,0,'.',','));

printf("<tr style='border:0px solid silver;'><td><font color=red>Tonnage</font></td><td align='right'><font color=red>%s</font></td><td align='right'><font color=red>%s</font></td></tr>",
number_format($rst[2],2,'.',','),number_format($rst[5],2,'.',','));

if($rst[0]==0){
$dpvv=0;}
else{
$dpvv=$rst[2]/$rst[0];}
	
if($rst[3]==0){
$mpvv=0;}
else{
$mpvv=$rst[5]/$rst[3];}

//printf("<tr style='border:0px solid silver;'><td>Tons per vehicle</td><td align='right'>%s</td><td align='right'>%s</td></tr>",
//number_format($dpvv,0,'.',','),number_format($mpvv,0,'.',','));

printf("<tr style='border:0px solid silver;'><td><font color=blue>Units</fonts></td><td align='right'>%s</td><td align='right'>%s</td></tr>",
number_format($rst[6],0,'.',','),number_format($rst[7],0,'.',','));

printf("</table><br><br>");


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

$sqlst="select dtypes,dvls,mvls from (select distinct stores_names as dtypes from st_notes ".$flt." ) as stypes left join (select stores_names,
sum(box_qty*packets_per_box*packet_weights)/1000000 as dvls from st_notes ".$flt." and dates='".$tdates."' and note_types='Delivery note' group by stores_names) as tds on 
dtypes=tds.stores_names left join (select stores_names,sum(box_qty*packets_per_box*packet_weights)/1000000 as mvls from st_notes ".$flt." and dates>='".$starts."' and dates<='".$ends."' 
 and note_types='Delivery note' group by stores_names) as tms on dtypes=tms.stores_names ";


$tdv=0;
$tmv=0;

printf("<table style='width:90%%;font-size:13;color:grey;'>");
printf("<tr style='border-bottom:1px solid #006699;text-align:right;font-weight:bold;color:#006699;'>
<td style='font-size:15;text-align:left;' width='50%%'>Delivery on Tonnage</td><td width='25%%'>%s</td><td width='25%%'>%s</td></tr>",$tdates,$tmonths);

$qry=mysqli_query($conn,$sqlst);
while($rst=mysqli_fetch_row($qry)){

$tdv=$tdv+$rst[1];
$tmv=$tmv+$rst[2];

printf("<tr style='border:0px solid silver;'>
<td>%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,'.',',')
);
}

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


$sqlst="select dtypes,dvls,mvls from (select distinct item_ranges as dtypes from st_notes ".$flt.") as stypes left join (select item_ranges,
sum(box_qty*packets_per_box*packet_weights)/1000000 as dvls from st_notes ".$flt." and dates='".$tdates."' and note_types='Delivery note' group by item_ranges) as tds on 
dtypes=tds.item_ranges left join (select item_ranges,sum(box_qty*packets_per_box*packet_weights)/1000000 as mvls from st_notes ".$flt." and dates>='".$starts."' and dates<='".$ends."' 
 and note_types='Delivery note' group by item_ranges) as tms on dtypes=tms.item_ranges ";

$tdv=0;
$tmv=0;

printf("<table style='width:90%%;font-size:13;color:grey;'>");
printf("<tr style='border-bottom:1px solid #006699;text-align:right;font-weight:bold;color:#006699;'>
<td style='font-size:15;text-align:left;' width='50%%'>Delivery on Tonnage</td><td width='25%%'>%s</td><td width='25%%'>%s</td></tr>",$tdates,$tmonths);

$qry=mysqli_query($conn,$sqlst);
while($rst=mysqli_fetch_row($qry)){

$tdv=$tdv+$rst[1];
$tmv=$tmv+$rst[2];

printf("<tr style='border:0px solid silver;'><td>%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,'.',','));
}

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

printf("</td><td width='40%%' valign='top'>");

printf("<table style='width:90%%;font-size:13;color:grey;'>");
printf("<tr style='border-bottom:1px solid #006699;text-align:right;font-weight:bold;background:#006699;color:white;'>
<td style='font-size:15;text-align:left;' width='100%%'>Stocks and pending invoice details</td></tr></table><br>");

$sqlst="select stores_names,sum(stock_box_qty*packets_per_box*packet_weights)/1000000,sum(stock_box_qty*box_price) from st_notes ".$flt." and dates>='2022-04-30' and  dates<='".$tdates."' group by stores_names ";

$tdv=0;
$tmv=0;

printf("<table style='width:90%%;font-size:13;color:grey;'>");
printf("<tr style='border-bottom:1px solid #006699;text-align:right;font-weight:bold;color:#006699;'>
<td style='font-size:15;text-align:left;' width='50%%'>Stock on warehouse</td><td width='25%%'>M-Ton</td><td width='25%%'>Value</td></tr>");

$qry=mysqli_query($conn,$sqlst);
while($rst=mysqli_fetch_row($qry)){
$tdv=$tdv+$rst[1];
$tmv=$tmv+$rst[2];

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

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


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

$sqlst="select item_ranges,sum(stock_box_qty*packets_per_box*packet_weights)/1000000,sum(stock_box_qty*box_price) from st_notes ".$flt." and dates>='2022-04-30' and dates<='".$tdates."' group by item_ranges ";

$tdv=0;
$tmv=0;

printf("<table style='width:90%%;font-size:13;color:grey;'>");
printf("<tr style='border-bottom:1px solid #006699;text-align:right;font-weight:bold;color:#006699;'>
<td style='font-size:15;text-align:left;' width='50%%'>Stock on range</td><td width='25%%'>M-Ton</td><td width='25%%'>Value</td></tr>");

$qry=mysqli_query($conn,$sqlst);
while($rst=mysqli_fetch_row($qry)){

$tdv=$tdv+$rst[1];
$tmv=$tmv+$rst[2];

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

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


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

$sqlst="select item_ranges,sum(box_qty*box_price)-sum((dv_box_qty+undv_box_qty)*box_price),sum(box_qty*packets_per_box*packet_weights)/1000000-sum((dv_box_qty+packets_per_box*packet_weights)/1000000) from rc_invoices ".$flt." and dates>='2022-03-28' and dates<='".$ends."' and invoice_types='Primary invoice' group by item_ranges ";

printf("<table style='width:90%%;font-size:13;color:grey;'>");
printf("<tr style='border-bottom:1px solid #006699;text-align:right;font-weight:bold;color:#006699;'>
<td style='font-size:15;text-align:left;'width='60%%'>Pending Delivery</td><td>M-Ton</td><td>Value</td></tr>");


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

$tdv=0;
$tdvvt=0;
while($rst=mysqli_fetch_row($qry)){
printf("<tr><td>%s</td><td align='right'>%s</td>
<td align='right'>%s</td>
</tr>",$rst[0],number_format($rst[2],2,'.',','),number_format($rst[1],0,'.',','));
$tdv=$tdv+$rst[1];
$tdvvt=$tdvvt+$rst[2];
}

printf("<tr style='font-weight:bold;'><td>Total</td><td align='right'>%s</td><td align='right'>%s</td></tr></table><br><br>",number_format($tdvvt,2,'.',','),number_format($tdv,0,'.',','));

printf("</td><td width='100%%' valign='top'>");
///////////////////////////////////////////////////////////////////////////////

$sqlst="select dates,count(distinct vehicle_names),sum(box_price*box_qty),sum(box_qty*packets_per_box*packet_weights)/1000000 from st_notes ".$flt." and dates>='".$starts."' and dates<='".$ends."' and 
note_types='Delivery note' group by dates";

$tdv=0;
$tmv=0;
$mton=0;

printf("<table style='width:120%%;font-size:13;color:grey;'>");
printf("<tr style='border-bottom:1px solid #006699;text-align:right;font-weight:bold;color:#006699;'>
<td colspan='3' style='font-size:15;text-align:left;'>Daily delivery</td></tr><tr style='color:#00669;font-weight:bold;text-align:right;'>
<td align='left'>Day</td><td>Vehicles</td><td>M-Ton</td><td align='center'>Value</td></tr>");

$qry=mysqli_query($conn,$sqlst);
while($rst=mysqli_fetch_row($qry)){

$tdv=$tdv+$rst[1];
$tmv=$tmv+$rst[2];
$mton=$mton+$rst[3];
printf("<tr style='border:1px solid silver;'><td>%s</td><td align='right'>%s</td><td align='right'><font color=red>%s</font></td><td align='right'>%s</td></tr>",
$rst[0],number_format($rst[1],0,'.',','),number_format($rst[3],2,'.',','),number_format($rst[2],0,'.',','));
}
printf("<tr style='border:1px solid silver;'><td>Total</td><td align='right'>%s</td><td align='right'><font color=red>%s</font></td><td align='right'>%s</td></tr>",
number_format($tdv,0,'.',','),number_format($mton,2,'.',','),number_format($tmv,0,'.',','));
printf("</table><br><br>");
printf("</td></table>");
printf("</div>");
}
?>
</div>
</body>
</html>