<?php

require "showshp.php";

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



?>



<html>

<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 dt=document.getElementById("dtd");
var cn=dt.rows.length;
var vls=vl.toLowerCase();

for(var cnt=2;cnt<cn;cnt++){
	
var tmp=dt.rows[cnt].cells[0].innerHTML;
var tmpa=tmp.toLowerCase();
var tmp=dt.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){
dt.rows[cnt].style.display="table-row";}
else{
dt.rows[cnt].style.display="none";}

if(vls.length==0){
dt.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">Market return - Range wide agent summary</div>
<div class="homes"><a href="prdlogings.php" style='color:#A9BCF5;'>Sign out</a></div>

</body>

</html>




<?php

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

$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=".urlencode($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;}


mrshowshps();

reportdets("Range wide agents");


printf("</div><div class='srg'></div>");
printf("<div class='mains'>");

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

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

while($serst=mysqli_fetch_row($seqry)){
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]."' ";}
}


printf("<form method='post' action='mragentsummarys.php?%s'>",$rpsty);
printf("<div class='fra'>From</div><div class='frb'>
<input type='text' size=10 class='starts'  name='starts' value='%s' id='inputField'></div><div class='frc'>To</div><div class='frd'><input type='text class='ends' size=10 name='ends' value='%s' id='inputField2'></div>
<div class='fre'><input type='submit' value='go' style='width:70'></div><div class='frf'>Search</div><div class='frg'>
<input type='text' size=20 id='dtfinds' autocomplete='off' onkeydown=\"fdtfinds(event)\"></div></form>",$starts,$ends);



$flt=$seflt." and dates>='".$_REQUEST['starts']."' and dates<='".$_REQUEST['ends']."' ";
if(strtolower($zones)!='all'){
$flt=$flt." and zones='".$zones."' ";}
if(strtolower($regions)!='all'){
$flt=$flt." and regions='".$regions."' ";}
if(strtolower($agents)!='all'){
$flt=$flt." and agent_names='".$agents."' ";}
if(strtolower($territorys)!='all'){
$flt=$flt." and territory_names='".$territorys."' ";}
if(strtolower($ranges)!='all'){
$flt=$flt." and item_ranges='".$ranges."' ";}
if(strtolower($subranges)!='all'){
$flt=$flt." and sub_ranges='".$subranges."' ";}
if(strtolower($items)!='all'){
$flt=$flt." and item_names='".$items."' ";}
if(strtolower($agenttypes)!='all'){
$flt=$flt." and agent_types='".$agenttypes."' ";}
if(strtolower($mrtypes)!='all'){
$flt=$flt." and mr_types='".$mrtypes."' ";}
if(strtolower($mrreasons)!='all'){
$flt=$flt." and reasons='".$mrreasons."' ";}
if(strtolower($mractions)!='all'){
$flt=$flt." and actions='".$mractions."' ";}





printf("<div style='color:#045FB4' class='frk'><input type='radio' name='chsh' checked onclick=\"document.getElementById('tdta').style.display='block';document.getElementById('tdtb').style.display='block';\">Values</div>");
printf("<div style='color:#045FB4'  class='frm'><input type='radio' name='chsh'  onclick=\"document.getElementById('tdta').style.display='none';document.getElementById('tdtb').style.display='block';\"> %%</div>");
printf("<div  style='color:#045FB4' class='frn'><input type='radio' name='chsh'  onclick=\"document.getElementById('tdta').style.display='block';document.getElementById('tdtb').style.display='block';\">Both</div>");


$flt=$seflt." and dates>='".$_REQUEST['starts']."' and dates<='".$_REQUEST['ends']."' ";
if(strtolower($zones)!='all'){
$flt=$flt." and zones='".$zones."' ";}
if(strtolower($regions)!='all'){
$flt=$flt." and regions='".$regions."' ";}
if(strtolower($agents)!='all'){
$flt=$flt." and agent_names='".$agents."' ";}
if(strtolower($territorys)!='all'){
$flt=$flt." and territory_names='".$territorys."' ";}
if(strtolower($ranges)!='all'){
$flt=$flt." and item_ranges='".$ranges."' ";}
if(strtolower($subranges)!='all'){
$flt=$flt." and sub_ranges='".$subranges."' ";}
if(strtolower($items)!='all'){
$flt=$flt." and item_names='".$items."' ";}
if(strtolower($agenttypes)!='all'){
$flt=$flt." and agent_types='".$agenttypes."' ";}
if(strtolower($mrtypes)!='all'){
$flt=$flt." and mr_types='".$mrtypes."' ";}
if(strtolower($mrreasons)!='all'){
$flt=$flt." and reasons='".$mrreasons."' ";}
if(strtolower($mractions)!='all'){
$flt=$flt." and actions='".$mractions."' ";}

$tsqlst="select distinct item_ranges from itemsmrd";
$tqry=mysqli_query($conn,$tsqlst);
$cn=mysqli_num_rows($tqry);
$cnt=0;
while($trst=mysqli_fetch_row($tqry)){
$arr[$cnt]=$trst[0];
$cnt++;}


$asqlst="";
for($cnt=0;$cnt<count($arr);$cnt++){
$asqlst=$asqlst." left join (select agent_names as agent_names,sum(((pcs_qty*pcs_price)+(box_qty*box_price))*(100-6)/100) as vls from market_returns ".$flt." and item_ranges='".$arr[$cnt]."' 
group by agent_names) as ".$arr[$cnt]." on ".$arr[$cnt].".agent_names=magent_names ";}

$rsqlst="select magent_names as region ";

for($cnt=0;$cnt<count($arr);$cnt++){
$rsqlst=$rsqlst.",".$arr[$cnt].".vls as ".$arr[$cnt];
}

$rsqlst=$rsqlst." from (select distinct agent_name as magent_names from agentsmrd where agent_name is not null   ";
if(strtolower($regions)!='all'){
$rsqlst=$rsqlst." and region='".$regions."'";}
if(strtolower($agents)!='all'){
$rsqlst=$rsqlst." and agent_name='".$agents."'";}

$rsqlst=$rsqlst.") as rgs ";


$sqlst=$rsqlst.$asqlst;
#echo $sqlst;

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

print("<div class='tdta' style='display:block;font-size:13;' id='tdta'>");
printf("<br>");
printf("<table id='dtd' style='font-size:13'><th bgcolor='silver' colspan='%s'><b>Market return - value</th><tr  style='color:#E6E6E6' bgcolor='grey'>
<td width=300 align='center'  style='border:1px solid white' >Agent</td>",$cn+6);

$cnt=0;
mysqli_data_seek($tqry,0);
while($trst=mysqli_fetch_row($tqry)){
$tsv=0;

$arr[$cnt]=$trst[0];
printf("<td align='center' style='border:1px solid white' width=100>%s</td>",$trst[0]);
$arvl[$cnt]=0;
$cnt++;
}

printf("<td width=100 style='border:1px solid white' align='center' >Total</td><td align='center' width=90 style='font-size:12;border:1px solid white;' >Items</td><td align='center' width=90 style='font-size:12;border:1px solid white;' >Reasons</td>
<td align='center' width=90 style='font-size:12;border:1px solid white;' >Types</td><td align='center' width=90 style='font-size:12;border:1px solid white;' >Actions</td></tr>");

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

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

for($rnt=1;$rnt<$cn;$rnt++){
$tvt=$tvt+$rst[$rnt];
$tsv=$tsv+$rst[$rnt];
$arvl[$rnt-1]=$arvl[$rnt-1]+$rst[$rnt];}

if($tsv!=0){

$hrfitems="<a href='mritemsummarys.php?rpsty=agentsitems&starts=".$starts."
&ends=".$ends."&zones=".$zones."&regions=".$regions."&agents=".urlencode($rst[0])."&territorys=".$territorys."&ranges=".$ranges."&subranges=".$subranges."&items=".$items."&mrtypes=".$mrtypes."&reasons=".$reasons."&actions=".$actions."' target='_blank'>Items</a>";

$hrfreasons="<a href='mrreasonsummarys.php?rpsty=agentsitems&starts=".$starts."
&ends=".$ends."&zones".$zones."&regions=".$regions."&agents=".urlencode($rst[0])."&territorys=".$territorys."&ranges=".$ranges."&subranges=".$subranges."&items=".$items."&mrtypes=".$mrtypes."&reasons=".$reasons."&actions=".$actions."' target='_blank'>Reason</a>";

$hrfactions="<a href='mractionsummarys.php?rpsty=agentsitems&starts=".$starts."
&ends=".$ends."&zones=".$zones."&regions=".$regions."&agents=".urlencode($rst[0])."&territorys=".$territorys."&ranges=".$ranges."&subranges=".$subranges."&items=".$items."&mrtypes=".$mrtypes."&reasons=".$reasons."&actions=".$actions."' target='_blank'>Actions</a>";

$hrfnotes="<a href='mrnotesummarys.php?rpsty=agentsitems&starts=".$starts."
&ends=".$ends."&zones=".$zones."&regions=".$regions."&agents=".urlencode($rst[0])."&territorys=".$territorys."&ranges=".$ranges."&subranges=".$subranges."&items=".$items."&mrtypes=".$mrtypes."&reasons=".$reasons."&actions=".$actions."' target='_blank'>Notes	</a>";

printf("<tr class='cls' bgcolor='white' onmouseover=\"this.style.backgroundColor='silver';\" onmouseout=\"this.style.backgroundColor='white';\"><td width=300>%s</td>",$rst[0]);
for($rnt=1;$rnt<$cn;$rnt++){
printf("<td align='right' width=80 style='padding-right::2%%;';'>%s</td>",number_format($rst[$rnt],0,'.',','));}
printf("<td align='right'><b>%s</td><td  width=80 style='font-size:13' align='center'>%s</td><td  width=80 style='font-size:13' align='center'>%s</td>
<td  width=80 style='font-size:13' align='center'>%s</td><td  width=80 style='font-size:13' align='center'>%s</td></tr>",number_format($tsv,0,'.',','),$hrfitems,$hrfreasons,$hrfactions,$hrfnotes);
}
}

$hrfitems="<a href='mritemsummarys.php?rpsty=agentsitems&starts=".$starts."
&ends=".$ends."&zones=".$zones."&regions=".$regions."&agents=".$agents."&territorys=".$territorys."&ranges=".$ranges."&subranges=".$subranges."&items=".$items."&mrtypes=".$mrtypes."&reasons=".$reasons."&actions=".$actions."' target='_blank'>Items</a>";

$hrfreasons="<a href='mrreasonsummarys.php?rpsty=agentsitems&starts=".$starts."
&ends=".$ends."&zones=".$zones."&regions=".$regions."&agents=".$agents."&territorys=".$territorys."&ranges=".$ranges."&subranges=".$subranges."&items=".$items."&mrtypes=".$mrtypes."&reasons=".$reasons."&actions=".$actions."' target='_blank'>Reasons</a>";

$hrfactions="<a href='mractionsummarys.php?rpsty=agentsitems&starts=".$starts."
&ends=".$ends."&zones=".$zones."&regions=".$regions."&agents=".$agents."&territorys=".$territorys."&ranges=".$ranges."&subranges=".$subranges."&items=".$items."&mrtypes=".$mrtypes."&reasons=".$reasons."&actions=".$actions."' target='_blank'>Actions</a>";

$hrfnotes="<a href='mrnotesummarys.php?rpsty=agentsitems&starts=".$starts."
&ends=".$ends."&zones=".$zones."&regions=".$regions."&agents=".$agents."&territorys=".$territorys."&ranges=".$ranges."&subranges=".$subranges."&items=".$items."&mrtypes=".$mrtypes."&reasons=".$reasons."&actions=".$actions."' target='_blank'>Notes</a>";


printf("<tr class='cls' style='font-weight:bold;'><td>Total</td>");
for($rnt=0;$rnt<$cn-1;$rnt++){
printf("<td align='right'  width=80 style='padding-right::2%%;';'><b>%s</td>",number_format($arvl[$rnt],0,'.',','));}
printf("<td align='right'><b>%s</td>",number_format($tvt,0,'.',','));
printf("<td align='center'>%s</td><td align='center'>%s</td><td align='center'>%s</td><td align='center'>%s</td>",$hrfitems,$hrfreasons,$hrfactions,$hrfnotes);
printf("</tr></table></div>");

printf("<BR>");

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

$tvt=0;

$tsqlst="select distinct item_ranges from itemsmrd";
$tqry=mysqli_query($conn,$tsqlst);
$cn=mysqli_num_rows($tqry);
$cnt=0;
while($trst=mysqli_fetch_row($tqry)){
$arr[$cnt]=$trst[0];
$cnt++;}


$asqlst="";
for($cnt=0;$cnt<count($arr);$cnt++){
$asqlst=$asqlst." left join (select agent_names as agent_names,sum(((pcs_qty*pcs_price)+(box_qty*box_price))*(100-6)/100) as vls from market_returns ".$flt." and item_ranges='".$arr[$cnt]."' group by agent_names) as ".$arr[$cnt]." on ".$arr[$cnt].".agent_names=magent_names ";}

$rsqlst="select magent_names as region ";

for($cnt=0;$cnt<count($arr);$cnt++){
$rsqlst=$rsqlst.",".$arr[$cnt].".vls as ".$arr[$cnt];
}

$rsqlst=$rsqlst." from (select distinct agent_name as magent_names from agentsmrd where agent_name is not null ";
if(strtolower($regions)!='all'){
$rsqlst=$rsqlst." and region='".$regions."'";}
if(strtolower($agents)!='all'){
$rsqlst=$rsqlst." and agent_name='".$agents."'";}

$rsqlst=$rsqlst.") as rgs ";


$sqlst=$rsqlst.$asqlst;
#echo $sqlst;

print("<div class='tdta' style='display:block;font-size:13;' id='tdta'>");
printf("<br>");
printf("<table id='dtd' style='font-size:13'><th bgcolor='silver' colspan='%s'><b>Market return - Qty (Pcs)</th><tr  style='color:#E6E6E6' bgcolor='grey'>
<td width=300 align='center'  style='border:1px solid white' >Zone</td>",$cn+6);

$cnt=0;
mysqli_data_seek($tqry,0);
while($trst=mysqli_fetch_row($tqry)){
$tsv=0;

$arr[$cnt]=$trst[0];
printf("<td align='center' style='border:1px solid white' width=100>%s</td>",$trst[0]);
$arvl[$cnt]=0;
$cnt++;
}

printf("<td width=100 style='border:1px solid white' align='center' >Total</td><td align='center' width=90 style='font-size:12;border:1px solid white;' >Items</td><td align='center' width=90 style='font-size:12;border:1px solid white;' >Reasons</td>
<td align='center' width=90 style='font-size:12;border:1px solid white;' >Regions</td><td align='center' width=90 style='font-size:12;border:1px solid white;' >Agents</td></tr>");

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

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

for($rnt=1;$rnt<$cn;$rnt++){
$tvt=$tvt+$rst[$rnt];
$tsv=$tsv+$rst[$rnt];
$arvl[$rnt-1]=$arvl[$rnt-1]+$rst[$rnt];}

if($tsv!=0){

$hrfitems="<a href='mritemsummarys.php?rpsty=agentsitems&starts=".$starts."
&ends=".$ends."&zones=".$zones."&regions=".$regions."&agents=".urlencode($rst[0])."&territorys=".$territorys."&ranges=".$ranges."&subranges=".$subranges."&items=".$items."&mrtypes=".$mrtypes."&reasons=".$reasons."&actions=".$actions."' target='_blank'>Items</a>";

$hrfreasons="<a href='mrreasonsummarys.php?rpsty=agentsitems&starts=".$starts."
&ends=".$ends."&zones".$zones."&regions=".$regions."&agents=".urlencode($rst[0])."&territorys=".$territorys."&ranges=".$ranges."&subranges=".$subranges."&items=".$items."&mrtypes=".$mrtypes."&reasons=".$reasons."&actions=".$actions."' target='_blank'>Reason</a>";

$hrfactions="<a href='mractionsummarys.php?rpsty=agentsitems&starts=".$starts."
&ends=".$ends."&zones=".$zones."&regions=".$regions."&agents=".urlencode($rst[0])."&territorys=".$territorys."&ranges=".$ranges."&subranges=".$subranges."&items=".$items."&mrtypes=".$mrtypes."&reasons=".$reasons."&actions=".$actions."' target='_blank'>Actions</a>";

$hrfnotes="<a href='mrnotesummarys.php?rpsty=agentsitems&starts=".$starts."
&ends=".$ends."&zones=".$zones."&regions=".$regions."&agents=".urlencode($rst[0])."&territorys=".$territorys."&ranges=".$ranges."&subranges=".$subranges."&items=".$items."&mrtypes=".$mrtypes."&reasons=".$reasons."&actions=".$actions."' target='_blank'>Notes</a>";

printf("<tr class='cls' bgcolor='white' onmouseover=\"this.style.backgroundColor='silver';\" onmouseout=\"this.style.backgroundColor='white';\"><td width=300>%s</td>",$rst[0]);
for($rnt=1;$rnt<$cn;$rnt++){
printf("<td align='right' width=80 style='padding-right::2%%;';'>%s</td>",number_format($rst[$rnt],0,'.',','));}
printf("<td align='right'><b>%s</td><td  width=80 style='font-size:13' align='center'>%s</td><td  width=80 style='font-size:13' align='center'>%s</td>
<td  width=80 style='font-size:13' align='center'>%s</td><td  width=80 style='font-size:13' align='center'>%s</td></tr>",number_format($tsv,0,'.',','),$hrfitems,$hrfreasons,$hrfactions,$hrfnotes);
}
}

$hrfitems="<a href='mritemsummarys.php?rpsty=agentsitems&starts=".$starts."
&ends=".$ends."&zones=".$zones."&regions=".$regions."&agents=".$agents."&territorys=".$territorys."&ranges=".$ranges."&subranges=".$subranges."&items=".$items."&mrtypes=".$mrtypes."&reasons=".$reasons."&actions=".$actions."' target='_blank'>Items</a>";

$hrfreasons="<a href='mrreasonsummarys.php?rpsty=agentsitems&starts=".$starts."
&ends=".$ends."&zones=".$zones."&regions=".$regions."&agents=".$agents."&territorys=".$territorys."&ranges=".$ranges."&subranges=".$subranges."&items=".$items."&mrtypes=".$mrtypes."&reasons=".$reasons."&actions=".$actions."' target='_blank'>Reasons</a>";

$hrfactions="<a href='mractionsummarys.php?rpsty=agentsitems&starts=".$starts."
&ends=".$ends."&zones=".$zones."&regions=".$regions."&agents=".$agents."&territorys=".$territorys."&ranges=".$ranges."&subranges=".$subranges."&items=".$items."&mrtypes=".$mrtypes."&reasons=".$reasons."&actions=".$actions."' target='_blank'>Actions</a>";

$hrfnotes="<a href='mrnotesummarys.php?rpsty=agentsitems&starts=".$starts."
&ends=".$ends."&zones=".$zones."&regions=".$regions."&agents=".$agents."&territorys=".$territorys."&ranges=".$ranges."&subranges=".$subranges."&items=".$items."&mrtypes=".$mrtypes."&reasons=".$reasons."&actions=".$actions."' target='_blank'>Notes</a>";


printf("<tr class='cls' style='font-weight:bold;'><td>Total</td>");
for($rnt=0;$rnt<$cn-1;$rnt++){
printf("<td align='right'  width=80 style='padding-right::2%%;';'><b>%s</td>",number_format($arvl[$rnt],0,'.',','));}
printf("<td align='right'><b>%s</td>",number_format($tvt,0,'.',','));
printf("<td align='center'>%s</td><td align='center'>%s</td><td align='center'>%s</td><td align='center'>%s</td>",$hrfitems,$hrfreasons,$hrfactions,$hrfnotes);
printf("</tr></table></div>");

printf("<BR>");


////////////////////////////////////
$tvt=0;

$tsqlst="select distinct item_ranges from itemsmrd";
$tqry=mysqli_query($conn,$tsqlst);
$cn=mysqli_num_rows($tqry);
$cnt=0;

while($trst=mysqli_fetch_row($tqry)){
$tsv=0;
$arr[$cnt]=$trst[0];
$cnt++;}


$asqlst="";
for($cnt=0;$cnt<count($arr);$cnt++){
$asqlst=$asqlst." left join (select agent_names as agent_names,sum((pcs_qty*packet_weights/1000)+(box_qty*packets_per_box*packet_weights/1000)) as vls from market_returns ".$flt." and item_ranges='".$arr[$cnt]."' group by agent_names) as ".$arr[$cnt]." on ".$arr[$cnt].".agent_names=magent_names ";}

$rsqlst="select magent_names as region ";

for($cnt=0;$cnt<count($arr);$cnt++){
$rsqlst=$rsqlst.",".$arr[$cnt].".vls as ".$arr[$cnt];
}

$rsqlst=$rsqlst." from (select distinct agent_name as magent_names from agentsmrd where agent_name is not null   ";
if(strtolower($regions)!='all'){
$rsqlst=$rsqlst." and region='".$regions."'";}
if(strtolower($agents)!='all'){
$rsqlst=$rsqlst." and agent_name='".$agents."'";}

$rsqlst=$rsqlst.") as rgs ";


$sqlst=$rsqlst.$asqlst;
#echo $sqlst;

print("<div class='tdta' style='display:block;font-size:13;' id='tdta'>");
printf("<br>");
printf("<table id='dtd' style='font-size:13'><th bgcolor='silver' colspan='%s'><b>Market return - Tonnage (Kg)</th><tr  style='color:#E6E6E6' bgcolor='grey'>
<td width=300 align='center'  style='border:1px solid white' >Zone</td>",$cn+6);


$cnt=0;
mysqli_data_seek($tqry,0);
while($trst=mysqli_fetch_row($tqry)){
$tsv=0;

$arr[$cnt]=$trst[0];
printf("<td align='center' style='border:1px solid white' width=100>%s</td>",$trst[0]);
$arvl[$cnt]=0;
$cnt++;
}

printf("<td width=100 style='border:1px solid white' align='center' >Total</td><td align='center' width=90 style='font-size:12;border:1px solid white;' >Items</td><td align='center' width=90 style='font-size:12;border:1px solid white;' >Reasons</td>
<td align='center' width=90 style='font-size:12;border:1px solid white;' >Regions</td><td align='center' width=90 style='font-size:12;border:1px solid white;' >Agents</td></tr>");


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

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

for($rnt=1;$rnt<$cn;$rnt++){
$tvt=$tvt+$rst[$rnt];
$tsv=$tsv+$rst[$rnt];
$arvl[$rnt-1]=$arvl[$rnt-1]+$rst[$rnt];}

if($tsv!=0){
$hrfitems="<a href='mritemsummarys.php?rpsty=agentsitems&starts=".$starts."
&ends=".$ends."&zones=".$zones."&regions=".$regions."&agents=".urlencode($rst[0])."&territorys=".$territorys."&ranges=".$ranges."&subranges=".$subranges."&items=".$items."&mrtypes=".$mrtypes."&reasons=".$reasons."&actions=".$actions."' target='_blank'>Items</a>";

$hrfreasons="<a href='mrreasonsummarys.php?rpsty=agentsitems&starts=".$starts."
&ends=".$ends."&zones".$zones."&regions=".$regions."&agents=".urlencode($rst[0])."&territorys=".$territorys."&ranges=".$ranges."&subranges=".$subranges."&items=".$items."&mrtypes=".$mrtypes."&reasons=".$reasons."&actions=".$actions."' target='_blank'>Reason</a>";

$hrfactions="<a href='mractionsummarys.php?rpsty=agentsitems&starts=".$starts."
&ends=".$ends."&zones=".$zones."&regions=".$regions."&agents=".urlencode($rst[0])."&territorys=".$territorys."&ranges=".$ranges."&subranges=".$subranges."&items=".$items."&mrtypes=".$mrtypes."&reasons=".$reasons."&actions=".$actions."' target='_blank'>Actions</a>";

$hrfnotes="<a href='mrnotesummarys.php?rpsty=agentsitems&starts=".$starts."
&ends=".$ends."&zones=".$zones."&regions=".$regions."&agents=".urlencode($rst[0])."&territorys=".$territorys."&ranges=".$ranges."&subranges=".$subranges."&items=".$items."&mrtypes=".$mrtypes."&reasons=".$reasons."&actions=".$actions."' target='_blank'>Notes</a>";

printf("<tr class='cls' bgcolor='white' onmouseover=\"this.style.backgroundColor='silver';\" onmouseout=\"this.style.backgroundColor='white';\"><td width=300>%s</td>",$rst[0]);
for($rnt=1;$rnt<$cn;$rnt++){
printf("<td align='right' width=80 style='padding-right::2%%;';'>%s</td>",number_format($rst[$rnt],0,'.',','));}
printf("<td align='right'><b>%s</td><td  width=80 style='font-size:13' align='center'>%s</td><td  width=80 style='font-size:13' align='center'>%s</td>
<td  width=80 style='font-size:13' align='center'>%s</td><td  width=80 style='font-size:13' align='center'>%s</td></tr>",number_format($tsv,0,'.',','),$hrfitems,$hrfreasons,$hrfactions,$hrfnotes);
}
}

$hrfitems="<a href='mritemsummarys.php?rpsty=agentsitems&starts=".$starts."
&ends=".$ends."&zones=".$zones."&regions=".$regions."&agents=".$agents."&territorys=".$territorys."&ranges=".$ranges."&subranges=".$subranges."&items=".$items."&mrtypes=".$mrtypes."&reasons=".$reasons."&actions=".$actions."' target='_blank'>Items</a>";

$hrfreasons="<a href='mrreasonsummarys.php?rpsty=agentsitems&starts=".$starts."
&ends=".$ends."&zones=".$zones."&regions=".$regions."&agents=".$agents."&territorys=".$territorys."&ranges=".$ranges."&subranges=".$subranges."&items=".$items."&mrtypes=".$mrtypes."&reasons=".$reasons."&actions=".$actions."' target='_blank'>Reasons</a>";

$hrfactions="<a href='mractionsummarys.php?rpsty=agentsitems&starts=".$starts."
&ends=".$ends."&zones=".$zones."&regions=".$regions."&agents=".$agents."&territorys=".$territorys."&ranges=".$ranges."&subranges=".$subranges."&items=".$items."&mrtypes=".$mrtypes."&reasons=".$reasons."&actions=".$actions."' target='_blank'>Actions</a>";

$hrfnotes="<a href='mrnotesummarys.php?rpsty=agentsitems&starts=".$starts."
&ends=".$ends."&zones=".$zones."&regions=".$regions."&agents=".$agents."&territorys=".$territorys."&ranges=".$ranges."&subranges=".$subranges."&items=".$items."&mrtypes=".$mrtypes."&reasons=".$reasons."&actions=".$actions."' target='_blank'>Notes</a>";


printf("<tr class='cls' style='font-weight:bold;'><td>Total</td>");
for($rnt=0;$rnt<$cn-1;$rnt++){
printf("<td align='right'  width=80 style='padding-right::2%%;';'><b>%s</td>",number_format($arvl[$rnt],0,'.',','));}
printf("<td align='right'><b>%s</td>",number_format($tvt,0,'.',','));
printf("<td align='center'>%s</td><td align='center'>%s</td><td align='center'>%s</td><td align='center'>%s</td>",$hrfitems,$hrfreasons,$hrfactions,$hrfnotes);
printf("</tr></table></div>");

printf("<BR>");

	
/////////////////////////
$tvt=0;

$tsqlst="select distinct item_ranges from itemsmrd";
$tqry=mysqli_query($conn,$tsqlst);
$cn=mysqli_num_rows($tqry);
$cnt=0;

while($trst=mysqli_fetch_row($tqry)){
$tsv=0;

$arr[$cnt]=$trst[0];
$cnt++;}


$asqlst="";
for($cnt=0;$cnt<count($arr);$cnt++){
$asqlst=$asqlst." left join (select agent_names as agent_names,sum((pcs_qty*pcs_price)+(box_qty*box_price)) as vls from market_returns ".$flt." and item_ranges='".$arr[$cnt]."' group by agent_names) as ".$arr[$cnt]." on ".$arr[$cnt].".agent_names=magent_names ";}

$rsqlst="select magent_names as region ";

for($cnt=0;$cnt<count($arr);$cnt++){
$rsqlst=$rsqlst.",".$arr[$cnt].".vls as ".$arr[$cnt];
}

$rsqlst=$rsqlst." from (select distinct agent_name as magent_names from agentsmrd where agent_name is not null   ";
if(strtolower($regions)!='all'){
$rsqlst=$rsqlst." and region='".$regions."'";}
if(strtolower($agents)!='all'){
$rsqlst=$rsqlst." and agent_name='".$agents."'";}

$rsqlst=$rsqlst.") as rgs ";


$sqlst=$rsqlst.$asqlst;
#echo $sqlst;


print("<div class='tdta' style='display:block;font-size:13;' id='tdta'>");
printf("<br>");
printf("<table id='dtd' style='font-size:13'><th bgcolor='silver' colspan='%s'><b>Market return - %% (Value)</th><tr  style='color:#E6E6E6' bgcolor='grey'>
<td width=300 align='center'  style='border:1px solid white' >Zone</td>",$cn+6);


$cnt=0;
mysqli_data_seek($tqry,0);
while($trst=mysqli_fetch_row($tqry)){
$tsv=0;

$arr[$cnt]=$trst[0];
printf("<td align='center' style='border:1px solid white' width=100>%s</td>",$trst[0]);
$arvl[$cnt]=0;
$cnt++;
}

printf("<td width=100 style='border:1px solid white' align='center' >Total</td><td align='center' width=90 style='font-size:12;border:1px solid white;' >Items</td><td align='center' width=90 style='font-size:12;border:1px solid white;' >Reasons</td>
<td align='center' width=90 style='font-size:12;border:1px solid white;' >Regions</td><td align='center' width=90 style='font-size:12;border:1px solid white;' >Agents</td></tr>");


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

$arvl=array();

while($rst=mysqli_fetch_row($qry)){
for($rnt=1;$rnt<$cn;$rnt++){
$tvt=$tvt+$rst[$rnt];
$arvl[$rnt-1]=$arvl[$rnt-1]+$rst[$rnt];}}

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

for($rnt=1;$rnt<$cn;$rnt++){
$tsv=$tsv+$rst[$rnt];}

if($tsv!=0){
printf("<tr class='cls'><td width=300>%s</td>",$rst[0]);

for($rnt=1;$rnt<$cn;$rnt++){

if($arvl[$rnt-1]==0){
$pcn=0;}
else{
$pcn=$rst[$rnt]*100/$arvl[$rnt-1];}	

$hrfitems="<a href='mritemsummarys.php?rpsty=agentsitems&starts=".$starts."
&ends=".$ends."&zones=".$zones."&regions=".$regions."&agents=".urlencode($rst[0])."&territorys=".$territorys."&ranges=".$ranges."&subranges=".$subranges."&items=".$items."&mrtypes=".$mrtypes."&reasons=".$reasons."&actions=".$actions."' target='_blank'>Items</a>";

$hrfreasons="<a href='mrreasonsummarys.php?rpsty=agentsitems&starts=".$starts."
&ends=".$ends."&zones".$zones."&regions=".$regions."&agents=".urlencode($rst[0])."&territorys=".$territorys."&ranges=".$ranges."&subranges=".$subranges."&items=".$items."&mrtypes=".$mrtypes."&reasons=".$reasons."&actions=".$actions."' target='_blank'>Reason</a>";

$hrfactions="<a href='mractionsummarys.php?rpsty=agentsitems&starts=".$starts."
&ends=".$ends."&zones=".$zones."&regions=".$regions."&agents=".urlencode($agents)."&territorys=".$territorys."&ranges=".$ranges."&subranges=".$subranges."&items=".$items."&mrtypes=".$mrtypes."&reasons=".$reasons."&actions=".$actions."' target='_blank'>Actions</a>";

$hrfnotes="<a href='mrnotesummarys.php?rpsty=agentsitems&starts=".$starts."
&ends=".$ends."&zones=".$zones."&regions=".$regions."&agents=".urlencode($rst[0])."&territorys=".$territorys."&ranges=".$ranges."&subranges=".$subranges."&items=".$items."&mrtypes=".$mrtypes."&reasons=".$reasons."&actions=".$actions."' target='_blank'>Notes</a>";

printf("<td align='right' width=80 style='padding-right::2%%;';'>%s</td>",number_format($pcn,2,'.',','));}
printf("<td align='right'><b>%s</td><td width=80  style='font-size:13' align='center'>%s</td><td  width=80 style='font-size:13' align='center'>%s</td>
<td  width=80 style='font-size:13' align='center'>%s</td><td  width=80 style='font-size:13' align='center'>%s</td></tr>",number_format($tsv*100/$tvt,2,'.',','),$hrfitems,$hrfreasons,$hrfactions,$hrfnotes);
}
}

$hrfitems="<a href='mritemsummarys.php?rpsty=agentsitems&starts=".$starts."
&ends=".$ends."&zones=".$zones."&regions=".$regions."&agents=".$agents."&territorys=".$territorys."&ranges=".$ranges."&subranges=".$subranges."&items=".$items."&mrtypes=".$mrtypes."&reasons=".$reasons."&actions=".$actions."' target='_blank'>Items</a>";

$hrfreasons="<a href='mrreasonsummarys.php?rpsty=agentsitems&starts=".$starts."
&ends=".$ends."&zones=".$zones."&regions=".$regions."&agents=".$agents."&territorys=".$territorys."&ranges=".$ranges."&subranges=".$subranges."&items=".$items."&mrtypes=".$mrtypes."&reasons=".$reasons."&actions=".$actions."' target='_blank'>Reasons</a>";

$hrfactions="<a href='mractionsummarys.php?rpsty=agentsitems&starts=".$starts."
&ends=".$ends."&zones=".$zones."&regions=".$regions."&agents=".$agents."&territorys=".$territorys."&ranges=".$ranges."&subranges=".$subranges."&items=".$items."&mrtypes=".$mrtypes."&reasons=".$reasons."&actions=".$actions."' target='_blank'>Actions</a>";

$hrfnotes="<a href='mrnotesummarys.php?rpsty=agentsitems&starts=".$starts."
&ends=".$ends."&zones=".$zones."&regions=".$regions."&agents=".$agents."&territorys=".$territorys."&ranges=".$ranges."&subranges=".$subranges."&items=".$items."&mrtypes=".$mrtypes."&reasons=".$reasons."&actions=".$actions."' target='_blank'>Notes</a>";


printf("<tr class='cls' style='font-weight:bold;'><td>Total</td>");
for($rnt=0;$rnt<$cn-1;$rnt++){
printf("<td align='right'  width=80 style='padding-right::2%%;';'><b>%s</td>",number_format(100,2,'.',','));}
printf("<td align='right'><b>%s</td>",number_format(100,2,'.',','));
printf("<td align='center'>%s</td><td align='center'>%s</td><td align='center'>%s</td><td align='center'>%s</td>",$hrfitems,$hrfreasons,$hrfactions,$hrfnotes);
printf("</tr></table></div>");

printf("</div></div>");
?>