<?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 mnh(mn,bn)
{

}


function mch(mn)
{
if(mn.style.background!='silver'){
mn.style.background='silver';}
}


function mnc(mn)
{
if(mn.style.background!='silver'){
mn.style.background='white';}

}



function gsh(sgn)
{

var cle=document.getElementsByClassName("tdtc");
for(cn=0;cn<cle.length;cn++){
cle[cn].style.display="none";}

document.getElementById(sgn).style.display="block";

}


function fdtfinds(pr)
{

var vl=document.getElementById("dtfinds").value;
var dta=document.getElementById("dtda");
var dtb=document.getElementById("dtdb");

var cn=dta.rows.length;
var vls=vl.toLowerCase();

for(var cnt=3;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].style.display="table-row";}
else{
dta.rows[cnt].style.display="none";
dtb.rows[cnt].style.display="none";}

if(vls.length==0){
dta.rows[cnt].style.display="table-row";
dtb.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">Region sales summary</div>
<div class="homes"><a href="prdlogings.php" style='color:#A9BCF5;'>Sign out</a></div>

</body>

</html>






<?php

$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['reasons'])){
$reasons=$_REQUEST['reaons'];}
else{
$reasons="All";}

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

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


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 ";

$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]."' ";}
}



mrshowshps();


printf("<div class='dts'><table style='font-size:14'><tr style='border-bottom:1px solid silver;'><td width=200><b><font color=#0066cc>Report details</font></td></tr</table>");
printf("<table><table style='font-size:13'>");
printf("<tr><td>Title:</td><td>Regional sales summary</td></tr>");
printf("<tr><td>From:</td><td>%s</td></tr>",$starts);
printf("<tr><td>To:</td><td>%s</td></tr>",$ends);

if(isset($_REQUEST['zones'])){
$zones=$_REQUEST['zones'];
printf("<tr><td>Zone:</td><td>%s</td></tr>",$zones);}
else{
$zones="All";}

if(isset($_REQUEST['regions'])){
$regions=$_REQUEST['regions'];
printf("<tr><td>Region:</td><td>%s</td></tr>",$regions);}
else{
$regions="All";}

if(isset($_REQUEST['territorys'])){
$territorys=$_REQUEST['territorys'];
printf("<tr><td>Territory:</td><td>%s</td></tr>",$territorys);}
else{
$territorys="All";}

if(isset($_REQUEST['agents'])){
$agents=$_REQUEST['agents'];
printf("<tr><td>Agent:</td><td>%s</td></tr>",$agents);}
else{
$agents="All";}

if(isset($_REQUEST['rsms'])){
$rsms=$_REQUEST['rsms'];
printf("<tr><td>RSM:</td><td>%s</td></tr>",$rsms);}
else{
$rsms="All";}

if(isset($_REQUEST['executives'])){
$executives=$_REQUEST['executives'];
printf("<tr><td>Executive:</td><td>%s</td></tr>",$executives);}
else{
$executives="All";}

if(isset($_REQUEST['reps'])){
$reps=$_REQUEST['reps'];
printf("<tr><td>Rep:</td><td>%s</td></tr>",$reps);}
else{
$reps="All";}

if(isset($_REQUEST['ranges'])){
$ranges=$_REQUEST['ranges'];
printf("<tr><td>Range:</td><td>%s</td></tr>",$ranges);}
else{
$ranges="All";}

if(isset($_REQUEST['subranges'])){
$subranges=$_REQUEST['subranges'];
printf("<tr><td>Sub-range:</td><td>%s</td></tr>",$subranges);}
else{
$subranges="All";}

if(isset($_REQUEST['items'])){
$items=$_REQUEST['items'];
printf("<tr><td>Item:</td><td>%s</td></tr>",$items);}
else{
$items="All";}

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



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

printf("<div class='mains'>");
#printf("<div class='tnm'>");


printf("<form method='post' action='mrdaysummarys.php?%s&ends=0&actpr=1'>",$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'><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);



$grt=0;
$grq=0;
$arr=array();
$arv=array();
$tvt=0;
$tsv=0;



$flt=$seflt;
if(strtolower($regions)!='all'){
$flt=$flt." and regions='".$regions."' ";}
if(strtolower($zones)!='all'){
$flt=$flt." and zones='".$zones."' ";}
if(strtolower($agents)!='all'){
$flt=$flt." and agent_names='".$agents."' ";}
if(strtolower($ranges)!='all'){
$flt=$flt." and item_ranges='".$ranges."' ";}
if(strtolower($items)!='all'){
$flt=$flt." and item_names='".$items."' ";}
if(strtolower($subranges)!='all'){
$flt=$flt." and sub_ranges='".$subranges."' ";}
if(strtolower($territorys)!='all'){
$flt=$flt." and territory_names='".$territorys."' ";}


if($_REQUEST['actpr']==1)
{

$months=date_format(date_create($starts),"m");
$years=date_format(date_create($starts),"Y");
if($months==1){
$years--;
$months=12;
}

$mends=$years."-".$months."-".cal_days_in_month(CAL_GREGORIAN,$months,$years);

$sqlst="select count(mrs) from (select distinct mr_numbers as mrs from market_returns ".$flt." and user_dates>='".$starts." 00:00:00' and user_dates<='".$starts." 24:59:00') as mrks ";
$qry=mysqli_query($conn,$sqlst);
$rst=mysqli_fetch_row($qry); 
printf("<BR><BR><BR><b>Number of returns - %s</b><br>",number_format($rst[0],0,'.',','));


$sqlst="select rgs,drts,mrts,svls from (select distinct item_ranges  as rgs from itemsmrd) mrs left join (select item_ranges as drgs, sum(pcs_qty*pcs_price) drts from market_returns ".$flt." and user_dates>='".$starts." 00:00:00' and user_dates<='".$starts." 24:59:00' group by item_ranges) as dmr on drgs=rgs left join (select item_ranges as mrgs, sum(pcs_qty*pcs_price) mrts from market_returns ".$flt." and dates>date_sub('".$mends."',INTERVAL 3 MONTH) and dates<='".$mends."' group by item_ranges) as mmr on mrgs=rgs left join (select item_ranges as srgs,sum(box_qty*box_price) svls from rc_invoices ".$flt." and dates>date_sub('".$mends."',INTERVAL 3 MONTH) and dates<='".$mends."' group by item_ranges) as mvls on srgs=rgs";

$dts=0;
$mts=0;
$sts=0;

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

$dts=$dts+$rst[1];
$mts=$mts+$rst[2];
$sts=$sts+$rst[3];
}

printf("<b>Total returns - %s (%s%%)</b>",number_format($dts,0,'.',','),number_format($dts*100/$sts,2,'.',''));
 

$sqlst="select rgs,drts,mrts,svls from (select distinct item_ranges  as rgs from itemsmrd) mrs left join (select item_ranges as drgs, sum(pcs_qty*pcs_price) drts from market_returns ".$flt
." and user_dates>='".$starts." 00:00:00' and user_dates<='".$starts." 24:59:00' group by item_ranges) as dmr on drgs=rgs left join (select item_ranges as mrgs, sum(pcs_qty*pcs_price)
 mrts from market_returns ".$flt." and dates>date_sub('".$mends."',INTERVAL 3 MONTH) and dates<='".$mends."' group by item_ranges) as mmr on mrgs=rgs left join (select item_ranges as srgs,sum(box_qty*box_price) svls from rc_invoices ".$flt
." and dates>date_sub('".$mends."',INTERVAL 3 MONTH) and dates<='".$mends."' group by item_ranges) as mvls on srgs=rgs";

$dts=0;
$mts=0;
$sts=0;

printf("<BR><BR>Range wise returns - %s",$starts);
printf("<table style='font-size:13;width:60%%;'><tr class='cls' style='background:silver;text-align:center;'><td>Range</td><td>Day returns</td><td>3 months returns</td><td>3 months sales</td><td>%%</td></tr>");
$qry=mysqli_query($conn,$sqlst);
while($rst=mysqli_fetch_row($qry)){

$dts=$dts+$rst[1];
$mts=$mts+$rst[2];
$sts=$sts+$rst[3];

#if($rst[1]>0 || $rst[2]>0){
if($rst[3]==0){
$pcs=0;}
else{
$pcs=($rst[2]/3)*100/($rst[3]/3);}

printf("<tr class='cls'><td>%s</td><td align='right'>%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]/3,0,'.',','),number_format($rst[3]/3,0,'.',',')
,number_format($pcs,2,'.',','));}

#}

printf("<tr class='cls'><td>Total</td><td align='right'>%s</td><td align='right'>%s</td><td align='right'>%s</td><td align='right'>%s%%</td></tr>",
number_format($dts,0,'.',','),number_format($mts,0,'.',','),number_format($sts/3,0,'.',','),number_format($mts*100/$sts,2,'.',','));
printf("</tr></table>");


$sqlst="select rgs,drts,mrts,svls from (select distinct mr_types as rgs from market_returns where dates>=date_sub('".$mends."',INTERVAL 3 MONTH) and dates<='".$mends."') mrs left join (select mr_types as drgs, sum(pcs_qty*pcs_price) drts from market_returns ".$flt
." and user_dates>='".$starts." 00:00:00' and user_dates<='".$starts." 24:59:00' group by mr_types) as dmr on drgs=rgs left join (select mr_types as mrgs, sum(pcs_qty*pcs_price) as 
 mrts from market_returns ".$flt." and dates>date_sub('".$mends."',INTERVAL 3 MONTH) and dates<='".$mends."' group by mr_types) as mmr on mrgs=rgs inner join (select sum(box_qty*box_price) svls from rc_invoices ".$flt." and dates>date_sub('".$mends."',INTERVAL 3 MONTH) and dates<='".$mends."' ) as mvls ";


printf("<BR><BR>Reason wise returns - %s",$starts);
printf("<table style='font-size:13;width:60%%;'><tr class='cls' style='background:silver;text-align:center;'><td>Reasons</td><td>Day returns</td><td>3 months returns</td><td>3 months sales</td><td>%%</td></tr>");
$qry=mysqli_query($conn,$sqlst);
while($rst=mysqli_fetch_row($qry)){

if($rst[1]>0 && $rst[2]>0){
if($rst[3]==0){
$pcs=0;}
else{
$pcs=($rst[2]/3)*100/($rst[3]/3);}

printf("<tr class='cls'><td>%s</td><td align='right'>%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]/3,0,'.',','),number_format($rst[3]/3,0,'.',',')
,number_format($pcs,2,'.',','));}}

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


$sqlst="select rgs,drts,mrts,svls from (select distinct item_name  as rgs from itemsmrd) as mrs left join (select item_names as drgs, sum(pcs_qty*pcs_price) drts from market_returns ".$flt." and user_dates>='".$starts." 00:00:00' and user_dates<='".$starts." 24:59:00' group by item_names) as dmr on drgs=rgs left join (select item_names as mrgs, sum(pcs_qty*pcs_price)
 mrts from market_returns ".$flt." and dates>date_sub('".$mends."',INTERVAL 3 MONTH) and dates<='".$mends."' group by item_names) as mmr on mrgs=rgs left join (select item_names as srgs,sum(box_qty*box_price) svls from rc_invoices ".$flt
." and dates>date_sub('".$mends."',INTERVAL 3 MONTH) and dates<='".$mends."' group by item_names) as mvls on srgs=rgs";

printf("<BR><BR>Item wise returns - %s",$starts);
printf("<table style='font-size:13;width:80%%;'><tr class='cls' style='background:silver;text-align:center;'><td>Item</td><td>Day returns</td><td>3 months returns</td><td>3 months sales</td><td>%%</td></tr>");
$qry=mysqli_query($conn,$sqlst);
while($rst=mysqli_fetch_row($qry)){

if($rst[1]>0){
if($rst[3]==0){
$pcs=0;}
else{
$pcs=($rst[2]/3)*100/($rst[3]/3);}

printf("<tr class='cls'><td>%s</td><td align='right'>%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]/3,0,'.',','),number_format($rst[3]/3,0,'.',',')
,number_format($pcs,2,'.',','));}}

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


$sqlst="select rgs,drts,mrts,svls from (select distinct agent_names as rgs from market_returns where dates>=date_sub('".$mends."',INTERVAL 3 MONTH) and dates<='".$mends."') mrs left join (select agent_names as drgs, sum(pcs_qty*pcs_price) drts from market_returns ".$flt
." and user_dates>='".$starts." 00:00:00' and user_dates<='".$starts." 24:59:00' group by agent_names) as dmr on drgs=rgs left join (select agent_names as mrgs, sum(pcs_qty*pcs_price) as 
 mrts from market_returns ".$flt." and dates>date_sub('".$mends."',INTERVAL 3 MONTH) and dates<='".$mends."' group by agent_names) as mmr on mrgs=rgs left join (select agent_names as srgs,sum(box_qty*box_price) svls from rc_invoices ".$flt
." and dates>date_sub('".$mends."',INTERVAL 3 MONTH) and dates<='".$mends."' group by agent_names) as mvls on rgs=srgs ";


printf("<BR><BR>Agent wise returns - %s",$starts);
printf("<table style='font-size:13;width:80%%;'><tr class='cls' style='background:silver;text-align:center;'><td>Agent</td><td>Day returns</td><td>3 months returns</td><td>3 months sales</td><td>%%</td></tr>");
$qry=mysqli_query($conn,$sqlst);
while($rst=mysqli_fetch_row($qry)){

if($rst[1]>0 && $rst[2]>0){
if($rst[3]==0){
$pcs=0;}
else{
$pcs=($rst[2]/3)*100/($rst[3]/3);}

printf("<tr class='cls'><td>%s</td><td align='right'>%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]/3,0,'.',','),number_format($rst[3]/3,0,'.',',')
,number_format($pcs,2,'.',','));}}

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


$sqlst="select ritems,rreasons,drts,mrts,svls from (select distinct item_names as ritems,mr_types as rreasons from market_returns) as mrs left join (select item_names as ditems,mr_types as dreasons, sum(pcs_qty*pcs_price) as drts from market_returns ".$flt
." and user_dates>='".$starts." 00:00:00' and user_dates<='".$starts." 24:59:00' group by item_names,mr_types) as dmr on ritems=ditems and dreasons=rreasons left join (select item_names as mitems,mr_types as mreasons, sum(pcs_qty*pcs_price) as 
 mrts from market_returns ".$flt." and dates>date_sub('".$mends."',INTERVAL 3 MONTH) and dates<='".$mends."' group by item_names,mr_types) as mmr on mitems=ritems  and rreasons=mreasons left join (select item_names as sitems,sum(box_qty*box_price) svls from rc_invoices ".$flt
." and dates>date_sub('".$mends."',INTERVAL 3 MONTH) and dates<='".$mends."' group by item_names) as mvls on sitems=ritems where drts>0";

#echo $sqlst;

printf("<BR><BR>Detail item wise returns - %s",$starts);
printf("<table style='font-size:13;width:100%%;'><tr class='cls' style='background:silver;text-align:center;'><td>Item</td><td>Reason</td><td>Day returns</td><td>3 months returns</td><td>3 months sales</td><td>%%</td></tr>");
$qry=mysqli_query($conn,$sqlst);
while($rst=mysqli_fetch_row($qry)){

if($rst[2]>0 ){
if($rst[4]==0){
$pcs=0;}
else{
$pcs=($rst[3]/3)*100/($rst[4]/3);}

printf("<tr class='cls'><td>%s</td><td>%s</td><td align='right'>%s</td><td align='right'>%s</td><td align='right'>%s</td><td align='right'>%s%%</td></tr>",$rst[0],$rst[1],
number_format($rst[2],0,'.',','),number_format($rst[3]/3,0,'.',','),number_format($rst[4]/3,0,'.',','),number_format($pcs,0,'.',','));}
}

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



$sqlst="select rgs,ritems,rreasons,drts,mrts,svls from (select distinct agent_names as rgs,item_names as ritems,mr_types as rreasons from market_returns where dates>=date_sub('".$mends."',INTERVAL 3 MONTH) and dates<='".$mends."') mrs left join (select agent_names as drgs,item_names as ditems,mr_types as dreasons, sum(pcs_qty*pcs_price) drts from market_returns ".$flt
." and user_dates>='".$starts." 00:00:00' and user_dates<='".$starts." 24:59:00' group by agent_names,item_names,mr_types) as dmr on drgs=rgs  and ritems=ditems and dreasons=rreasons left join (select agent_names as mrgs,item_names as mitems,mr_types as mreasons, sum(pcs_qty*pcs_price) as 
 mrts from market_returns ".$flt." and dates>date_sub('".$mends."',INTERVAL 3 MONTH) and dates<='".$mends."' group by agent_names,item_names,mr_types) as mmr on mrgs=rgs and mitems=ritems  and rreasons=mreasons left join (select agent_names as srgs,item_names as sitems,sum(box_qty*box_price) svls from rc_invoices ".$flt
." and dates>date_sub('".$mends."',INTERVAL 3 MONTH) and dates<='".$mends."' group by agent_names,item_names) as mvls on rgs=srgs and sitems=ritems";


printf("<BR><BR>Detail Agent wise returns - %s",$starts);
printf("<table style='font-size:13;width:100%%;'><tr class='cls' style='background:silver;text-align:center;'><td>Agent</td><td>Item</td><td>Reason</td><td>Day returns</td><td>3 months returns</td><td>3 months sales</td><td>%%</td></tr>");
$qry=mysqli_query($conn,$sqlst);
while($rst=mysqli_fetch_row($qry)){

#if($rst[3]>0 && $rst[4]>0){
if($rst[5]==0){
$pcs=0;}
else{
$pcs=($rst[4]/3)*100/($rst[5]/3);}

printf("<tr class='cls'><td>%s</td><td>%s</td><td>%s</td><td align='right'>%s</td><td align='right'>%s</td><td align='right'>%s</td><td align='right'>%s%%</td></tr>",$rst[0],$rst[1],$rst[2],
number_format($rst[3],0,'.',','),number_format($rst[4]/3,0,'.',','),number_format($rst[5]/3,0,'.',','),number_format($pcs,2,'.',','));}
#}

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

}

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

 


?>