<?php
require "showshp.php";

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


?>


<html>
<title>Market Rtns</title>

<head>

<link rel="stylesheet" type="text/css" href="rdsty.css">
<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>

<script>

function fdtfinds(pr)
{

var vl=document.getElementById("dtfinds").value;
var dta=document.getElementById("dtda");
var dtb=document.getElementById("dtdb");
var dtc=document.getElementById("dtdc");
var cn=dta.rows.length;
var vls=vl.toLowerCase();

for(var cnt=2;cnt<cn;cnt++){
	
var tmp=dta.rows[cnt].cells[0].innerHTML;
var tmpa=tmp.toLowerCase();
var tmp=dta.rows[cnt].cells[1].innerHTML;
var tmpb=tmp.toLowerCase();
var sta=tmpa.indexOf(vls);
var stb=tmpb.indexOf(vls);

if((sta>=0 || stb>=0 ) && vls.length>0){
dta.rows[cnt].style.display="table-row";
dtb.rows[cnt+1].style.display="table-row";
dtc.rows[cnt].style.display="table-row";}
else{
dta.rows[cnt].style.display="none";
dtb.rows[cnt+1].style.display="none";
dtc.rows[cnt].style.display="none";}

if(vls.length==0){
dta.rows[cnt].style.display="table-row";
dtb.rows[cnt+1].style.display="table-row";
dtc.rows[cnt].style.display="table-row";}
}

	
}


</script>
</head>



<body style="font-family:calibri;font-size:small" onload="mnh('amnc','agentsb')">
<div class="hdn"></div>

<?php
printf("<div class='ses'>%s (%s)</div>",$_SESSION['titles'],$_SESSION['positions']);
?>

<div class="tops" align="center">Dashboard</div>
<div class="homes"><a href="index.php" style='color:#A9BCF5;'>Sign out</a></div>

</body>

</html>





<?php

date_default_timezone_set("Asia/Colombo");

$grt=0;
$grq=0;
$arr=array();
$arvl=array();
$tvt=0;

$starts=$_REQUEST['starts'];
$ends=$_REQUEST['ends'];

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

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

if(isset($_REQUEST['territorys'])){
$territorys=$_REQUEST['territorys'];}
else{
$territorys="All";}

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

if(isset($_REQUEST['rsms'])){
$rsms=$_REQUEST['rsms'];}
else{
$rsms="All";}

if(isset($_REQUEST['executives'])){
$executives=$_REQUEST['executives'];}
else{
$executives="All";}

if(isset($_REQUEST['reps'])){
$reps=$_REQUEST['reps'];}
else{
$reps="All";}

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

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

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

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


if(isset($_REQUEST['mrtypes'])){
$mrtypes=$_REQUEST['mrtypes'];}
else{
$mrtypes="All";}

if(isset($_REQUEST['mrreasons'])){
$mrreasons=$_REQUEST['mrreasons'];}
else{
$mrreasons="All";}

if(isset($_REQUEST['mractions'])){
$mractions=$_REQUEST['mractions'];}
else{
$mractions="All";}


$rpsty="regions=".$regions."&agents=".$agents."&territorys=".$territorys."&ranges=".$ranges."&zones=".$zones."&rsms=".$rsms."&executives=".$executives."&reps=".$reps
."&items=".$items."&subranges=".$subranges; 


$months=date('m');
$days=date('t');
$years=date('Y');

if($starts==0 || $starts==''){
$starts=$years."-".$months."-01";}
if($ends==0 || $ends==''){
$ends=$years."-".$months."-".$days;}

$conn=mysqli_connect("localhost","primarysales","S5L7lF01EQZvQrGK","primarysales_dianaold");
$seflt=" where agent_names is not null and ( ";

$sesqlst="select * from acs where user_names='".$_SESSION['usernames']."'";
$seqry=mysqli_query($conn,$sesqlst);

$cnt=mysqli_num_rows($seqry);

while($serst=mysqli_fetch_row($seqry)){
$cnt--;

$seflt=$seflt." ( agent_names is not null ";

if(strtolower($serst[1])!='all'){
$seflt=$seflt." and zones='".$serst[1]."' ";}
if(strtolower($serst[2])!='all'){
$seflt=$seflt." and regions='".$serst[2]."' ";}
if(strtolower($serst[3])!='all'){
$seflt=$seflt." and territory_names='".$serst[3]."' ";}
if(strtolower($serst[4])!='all'){
$seflt=$seflt." and agent_names='".$serst[4]."' ";}
if(strtolower($serst[5])!='all'){
$seflt=$seflt." and item_names='".$serst[5]."' ";}
if(strtolower($serst[6])!='all'){
$seflt=$seflt." and item_ranges='".$serst[6]."' ";}
if(strtolower($serst[7])!='all'){
$seflt=$seflt." and sub_ranges='".$serst[7]."' ";}
$seflt=$seflt.") ";

if($cnt>0){
$seflt=$seflt." or ";}
}

$seflt=$seflt.") ";

#$flt=$seflt." and dates>='".$_REQUEST['starts']."' and dates<='".$_REQUEST['ends']."' ";
$flt=$seflt;

mrshowshps();

$sqlst="select max(dates) from market_returns where dates<'".date('Y-m-d')."'";
$qry=mysqli_query($conn,$sqlst);
$rst=mysqli_fetch_row($qry);
$todates=$rst[0];

#$todays=date("Y-m-d");
#$todates=date_create($todays);
#date_sub($todates,date_interval_create_from_date_string("1 days"));

$hrfnotes="<a href='mrnotessummarys.php?rpsty=agentsitems&starts=".$todates."
&ends=".$todates."&regions=all&agents=all&territorys=all&ranges=all' target='_blank'>".$todates." - Return notes</a>";

$hrfitems="<a href='mritemsummarys.php?rpsty=agentsitems&starts=".$todates."
&ends=".$todates."&regions=all&agents=all&territorys=all&ranges=all' target='_blank'>".$todates." - Returned items</a>";

$hrfagents="<a href='mragentwisesummarys.php?rpsty=agentsitems&starts=".$todates."
&ends=".$todates."&regions=all&agents=all&territorys=all&ranges=all' target='_blank'>".$todates." - Returned agents</a>";


printf("<div class='dtsdashbs'>
<table style='font-size:13;'>
<tr><td>%s</td></tr>
<tr><td>%s</td></tr>
<tr><td>%s</td></tr>
</table><br>
<table style='font-size:13;color:#2E2E2E;width:100%%;'>
<tr style='color:white;font-weight:bold;font-size:13;background:grey;border:1px solid grey;'><td colspan='2'>Daily Returns</td></tr>
<tr class'cls' style='background:silver;color:#2E2E2E;border:1px solid silver;'><td align='center'>Date</td><td align='right'>Return value</td></tr>",$hrfnotes,$hrfitems,$hrfagents);

$sqlst="select dates,sum(pcs_qty*pcs_price) from market_returns where dates>='".$starts."' and dates<='".$ends."' group by dates order by dates";
$qry=mysqli_query($conn,$sqlst);
while($rst=mysqli_fetch_row($qry)){
printf("<tr class='cls' style='border:1px solid silver;'><td align='center'>%s</td><td align='right'>%s</td></tr>",$rst[0],number_format($rst[1],0,'.',','));}

#printf("</table><div style='font-size:12;'><br>We are currently working on resolving past Agent names and Item names that currently not in use, of which consist around 1%% of total printf("

printf("</table><div style='color:green;font-size:13;'><br><a href='#' target='_blank'><b>Goto Channel - 01</b></a></div>");
printf("</table><div style='color:green;font-size:13;'><br><a href='#' target='_blank'><b>Goto Channel- 02</b></a></div>
<br>Channel Reports Can be view W.I.From - 2016-10-01
</div>");


printf("<div class='maindashbs'>");

$conn=mysqli_connect("localhost","primarysales","S5L7lF01EQZvQrGK","primarysales_dianaold");
date_default_timezone_set("Asia/Colombo");
$tmpdt=date("Y-m-d");


$qry=mysqli_query($conn,$sqlst);
$rst=mysqli_fetch_row($qry);
$todates=$rst[0];


$sqlst="select max(dates) from market_returns where dates<'".date('Y-m-d')."'";
$qry=mysqli_query($conn,$sqlst);
$rst=mysqli_fetch_row($qry);
$todates=$rst[0];

#$todays=date("Y-m-d");
#$todates=date_create($todays);
#date_sub($todates,date_interval_create_from_date_string("1 days"));



print("<div style='display:block;font-size:14;' id='tdta'>");
printf("<BR>");

printf("<table id='dtd' style='font-size:13;width:50%%;color:#2E2E2E;border:1px solid silver;'>");

$sqlst="select sts,tts from (select count(rts)sts from (select distinct mr_numbers as rts  from market_returns where dates='".
$todates."')as taa) as dtv,(select count(rts) as tts from (select distinct mr_numbers as rts from 
market_returns ".$flt." and dates>='".date('Y-m-01')."' and dates<='". date('Y-m-d')."')as tdd)as ttv "; 
$qry=mysqli_query($conn,$sqlst);
$rst=mysqli_fetch_row($qry);

printf("<tr class='cls' style='background:silver;font-weight:bold;'><td>Summary</td><td align='right' style='padding-right:1%%;'>%s<td align='right' style='padding-right:1%%;'>%s</td></tr>",$todates,date('Y-M'));
printf("<tr class='cls'><td>Number of returns</td><td align='right'>%s</td><td align='right'>%s</td></tr>",number_format($rst[0],0,'.',','),number_format($rst[1],0,'.',','));

$sqlst="select sts,tts from (select sum(pcs_qty) as sts  from market_returns where dates='".
$todates."') as dtv,(select sum(pcs_qty) as tts from 
market_returns ".$flt." and dates>='".date('Y-m-01')."' and dates<='". date('Y-m-d')."')as ttv "; 
$qry=mysqli_query($conn,$sqlst);
$rst=mysqli_fetch_row($qry);
printf("<tr class='cls'><td>Return Qty (Pcs)</td><td align='right'>%s</td><td align='right'>%s</td></tr>",number_format($rst[0],0,'.',','),number_format($rst[1],0,'.',','));

$sqlst="select sts,tts from (select sum(pcs_qty*packet_weights/1000) as sts  from market_returns where dates='".
$todates."') as dtv,(select sum(pcs_qty*packet_weights/1000) as tts from 
market_returns ".$flt." and dates>='".date('Y-m-01')."' and dates<='". date('Y-m-d')."')as ttv "; 
$qry=mysqli_query($conn,$sqlst);
$rst=mysqli_fetch_row($qry);
printf("<tr class='cls'><td>Return Weight (Kg)</td><td align='right'>%s</td><td align='right'>%s</td></tr>",number_format($rst[0],0,'.',','),number_format($rst[1],0,'.',','));

$sqlst="select sts,tts from (select sum(pcs_qty*pcs_price) as sts  from market_returns where dates='".
$todates."') as dtv,(select sum(pcs_qty*pcs_price) as tts from 
market_returns ".$flt." and  dates>='".date('Y-m-01')."' and dates<='". date('Y-m-d')."')as ttv "; 
$qry=mysqli_query($conn,$sqlst);
$rst=mysqli_fetch_row($qry);
printf("<tr class='cls'><td>Return value</td><td align='right'>%s</td><td align='right'>%s</td></tr>",number_format($rst[0],0,'.',','),number_format($rst[1],0,'.',','));
 

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


print("<div style='display:block;font-size:14;left:5%%;' id='tdta'>");
printf("<table id='dtda' style='font-size:13;width:100%%;color:#2E2E2E;'><th style='color:white;font-size:13;background:grey;border:1px solid grey;' colspan='7' align='left'><b>Zone market return trend</th>",$exchrf);

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


$arr=array();

$months=date('m');
$days=date('t');
$years=date('Y');
 
$sqlst="select mranges";
$rsqlst="";
 
for($cnt=0;$cnt<6;$cnt++){
$arr[$cnt]=0;	
if($months==0){
$months=12;
$years--;}

$flt=$seflt;

$dates=date_create($years.'-'.$months);
printf("<td style='background:silver;padding-right:1%%;' align='right'>%s</td>",date_format($dates,'M-Y'));
 
$sqlst=$sqlst.",xx".$months.$years.".vls";
 
$rsqlst=$rsqlst." left join (select zones as tranges,sum(pcs_qty*pcs_price) as vls from market_returns ".$flt." and dates>='".$years."-".$months."-1' and
dates<='".$years."-".$months."-".cal_days_in_month(CAL_GREGORIAN,$months,$years)."' group by zones) as xx".$months.$years." on xx".$months.$years.".tranges=mitems.mranges ";
$months--;        
}
 
$sqlst=$sqlst." from (select distinct zone as mranges, zone_order as zoneorders from agentsmrd) as mitems ";
$sqlst=$sqlst.$rsqlst." order by zoneorders ";
 
#echo $sqlst;
printf("</tr>");

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

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

$itemshrf="<a href='mrhsitemsummary.php?rpsty=agentsummarys&dtd=dtdaa&starts=".$starts."&ends=".$ends."&regions=".$regions."&agents=".$agents."&territorys=all&ranges=all&zones=".$rst[0]."&rsms=All&executives=All&reps=All&items=All' target='_blank'>Items</a>";
$agentshrf="<a href='mrhsagentsummary.php?rpsty=agentsummarys&dtd=dtdaa&starts=".$starts."&ends=".$ends."&regions=".$regions."&agents=".$agents."&territorys=all&ranges=all&zones=".$rst[0]."&rsms=All&executives=All&reps=All&items=All' target='_blank'>Agents</a>";
$regionhrf="<a href='mrhsregionsummary.php?rpsty=agentsummarys&dtd=dtdaa&starts=".$starts."&ends=".$ends."&regions=".$regions."&agents=".$agents."&territorys=all&ranges=all&zones=".$rst[0]."&rsms=All&executives=All&reps=All&items=All' target='_blank'>Regions</a>";


printf("<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];
$ttv=$ttv+$rst[$cn];
	
printf("<td align='right' style='padding-right:1%%;'>%s</td>",number_format($rst[$cn],0,'.',','));}

printf("</tr>");

$itemshrf="<a href='mrhsitemsummary.php?rpsty=agentsummarys&dtd=dtdaa&starts=".$starts."&ends=".$ends."&regions=".$regions."&agents=".$agents."&territorys=all&ranges=all&zones=all&rsms=All&executives=All&reps=All&items=All' target='_blank'>Items</a>";
$agentshrf="<a href='mrhsagentsummary.php?rpsty=agentsummarys&dtd=dtdaa&starts=".$starts."&ends=".$ends."&regions=".$regions."&agents=".$agents."&territorys=all&ranges=all&zones=all&rsms=All&executives=All&reps=All&items=All' target='_blank'>Agents</a>";
$regionhrf="<a href='mrhsregionsummary.php?rpsty=agentsummarys&dtd=dtdaa&starts=".$starts."&ends=".$ends."&regions=".$regions."&agents=".$agents."&territorys=all&ranges=all&zones=all&rsms=All&executives=All&reps=All&items=All' target='_blank'>Regions</a>";
}

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

printf("</tr></table>",$itemshrf,$agentshrf,$regionhrf);



printf("<BR>");

print("<div style='display:block;font-size:14;left:5%%;' id='tdta'>");
printf("<table id='dtda' style='font-size:13;width:100%%;color:#2E2E2E;'><th style='color:white;font-size:13;background:grey;border:1px solid grey;' colspan='7' align='left'><b>Range market return trend</th>",$exchrf);

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


$arr=array();

$months=date('m');
$days=date('t');
$years=date('Y');
 
$sqlst="select mranges";
$rsqlst="";
 
for($cnt=0;$cnt<6;$cnt++){
$arr[$cnt]=0;	
if($months==0){
$months=12;
$years--;}

$dates=date_create($years.'-'.$months);
printf("<td style='background:silver;padding-right:1%%;' align='right'>%s</td>",date_format($dates,'M-Y'));
 
$sqlst=$sqlst.",xx".$months.$years.".vls";
 
$rsqlst=$rsqlst." left join (select item_ranges as tranges,sum(pcs_qty*pcs_price) as vls from market_returns ".$flt." and dates>='".$years."-".$months."-1' and
dates<='".$years."-".$months."-".cal_days_in_month(CAL_GREGORIAN,$months,$years)."' group by item_ranges) as xx".$months.$years." on xx".$months.$years.".tranges=mitems.mranges ";
$months--;        
}
 
$sqlst=$sqlst." from (select distinct item_ranges as mranges from itemsmrd ".$rlt.") as mitems ";
$sqlst=$sqlst.$rsqlst." order by mranges ";
 
#echo $sqlst;

printf("</tr>");

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

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

$itemshrf="<a href='prhsitemsummary.php?rpsty=agentsummarys&dtd=dtdaa&starts=".$starts."&ends=".$ends."&regions=".$regions."&agents=".$agents."&territorys=all&ranges=".$rst[0]."&zones=".$zones."&rsms=All&executives=All&reps=All&items=All' target='_blank'>Items</a>";
$agentshrf="<a href='prhsagentsummary.php?rpsty=agentsummarys&dtd=dtdaa&starts=".$starts."&ends=".$ends."&regions=".$regions."&agents=".$agents."&territorys=all&ranges=".$rst[0]."&zones=".$zones."&rsms=All&executives=All&reps=All&items=All' target='_blank'>Agents</a>";
$regionhrf="<a href='prhsregionsummary.php?rpsty=agentsummarys&dtd=dtdaa&starts=".$starts."&ends=".$ends."&regions=".$regions."&agents=".$agents."&territorys=all&ranges=".$rst[0]."&zones=".$zones."&rsms=All&executives=All&reps=All&items=All' target='_blank'>Regions</a>";



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

if($ttv!=0){
printf("<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];
$ttv=$ttv+$rst[$cn];
	
printf("<td align='right' style='padding-right:1%%;'>%s</td>",number_format($rst[$cn],0,'.',','));}
printf("</tr>",number_format($ttv,0,'.',','),$itemshrf,$agentshrf,$regionhrf);
$itemshrf="<a href='prhsitemsummary.php?rpsty=agentsummarys&dtd=dtdaa&starts=".$starts."&ends=".$ends."&regions=".$regions."&agents=".$agents."&territorys=all&ranges=all&zones=".$zones."&rsms=All&executives=All&reps=All&items=All' target='_blank'>Items</a>";
$agentshrf="<a href='prhsagentsummary.php?rpsty=agentsummarys&dtd=dtdaa&starts=".$starts."&ends=".$ends."&regions=".$regions."&agents=".$agents."&territorys=all&ranges=all&zones=".$zones."&rsms=All&executives=All&reps=All&items=All' target='_blank'>Agents</a>";
$regionhrf="<a href='prhsregionsummary.php?rpsty=agentsummarys&dtd=dtdaa&starts=".$starts."&ends=".$ends."&regions=".$regions."&agents=".$agents."&territorys=all&ranges=all&zones=".$zones."&rsms=All&executives=All&reps=All&items=All' target='_blank'>Regions</a>";
}
}

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

printf("</tr></table>",$itemshrf,$agentshrf,$regionhrf);

printf("</div>");

printf("<BR>");

print("<div style='display:block;font-size:14;left:5%%;' id='tdta'>");
printf("<table id='dtda' style='font-size:13;width:100%%;color:#2E2E2E;'>
<th style='color:white;font-size:13;background:grey;border:1px solid grey;' colspan='6' align='left'><b>Highest returns - %s</th><tr><td>",date('Y-M'));

printf("<table style='font-size:13;width:100%%;'><tr class='cls' align='center' style='background:silver;'><td colspan='3'><b>Top 5 return reason</b></td></tr><tr><td align='center'>Reason</td><td align='center'>Kg</td><td align='center'>Vale</td></tr>");

$sqlst="select mr_types,sum(pcs_qty*packet_weights/1000),sum(pcs_qty*pcs_price) as vl from market_returns ".$flt." and dates >='".date('Y-m-01').
"' and dates<='".date('Y-m-d')."' group by mr_types order by vl DESC limit 5";
$qry=mysqli_query($conn,$sqlst);
while($rst=mysqli_fetch_row($qry)){
printf("<tr class='cls'><td>%s</td><td align='right' style='padding-right:1%%;'>%s</td><td align='right' style='padding-right:1%%;'>%s</td></tr>",$rst[0],number_format($rst[1],0,'.',','),number_format($rst[2],0,'.',','));	
}

printf("</table></div></td><td><table style='font-size:13;width:100%%;'>");

printf("<tr class='cls' align='center' style='background:silver;'><td colspan='3'><b>Top 5 return items</b></td></tr><tr><td align='center'>Item</td><td align='center'>Kg</td><td align='center'>Vale</td></tr>");

$sqlst="select item_names,sum(pcs_qty*packet_weights/1000),sum(pcs_qty*pcs_price) as vl from market_returns ".$flt." and dates >='".date('Y-m-01').
"' and dates<='".date('Y-m-d')."' group by item_names order by vl DESC limit 5";
$qry=mysqli_query($conn,$sqlst);
while($rst=mysqli_fetch_row($qry)){
printf("<tr class='cls'><td>%s</td><td align='right' style='padding-right:1%%;'>%s</td><td align='right' style='padding-right:1%%;'>%s</td></tr>",$rst[0],number_format($rst[1],0,'.',','),number_format($rst[2],0,'.',','));	
}

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


?>