<?php
require "racs.php";
require "vtshowsBBB.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($_REQUEST['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['fstores'])){
$fstores=$_REQUEST['fstores'];}
else{
$fstores='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($terrys)!='all'){
$flt=$flt." and territory_names='".$terrys."' ";}
?>

<html>
<title>Payment Dashboard</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>
//This code will refresh page with updated data
//setTimeout(function(){ window.location = "saccdash.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");
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%%;'>");
sacnew(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;'>Payment Details - Group</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,vattd,vattm,disday,dismon from (select distinct paymode as dtypes from rc_invoices ".$flt." and dates>='".$starts."' and dates<='".$ends."' and invoice_types='Primary Invoice') as stypes

left join (select distinct paymode,sum(box_qty*box_price) as dvls,sum((box_qty*box_price)*discount_rates) as disday,(sum(box_qty*box_price)+sum(box_qty*box_price)*discount_rates)*vat_rates as vattd from rc_invoices ".$flt." and dates='".$tdates."' and invoice_types='Primary Invoice' group by paymode) as tds on dtypes=tds.paymode

left join (select distinct paymode,sum(box_qty*box_price) as mvls,sum((box_qty*box_price)*discount_rates) as dismon,(sum(box_qty*box_price)+sum(box_qty*box_price)*discount_rates)*vat_rates as vattm from rc_invoices ".$flt." and dates>='".$starts."' and dates<='".$ends."'  and invoice_types='Primary Invoice' group by paymode) as tms on dtypes=tms.paymode ";

/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////\\\\\\\\\\\\\\\\\\\

$sqq="select sum(box_qty*box_price+box_qty*box_price*discount_rates) as netsales from rc_invoices ".$flt." and dates='".$tdates."' and invoice_types='Primary Invoice' and paymode='cash'";

$sq="select sum(box_qty*box_price+box_qty*box_price*discount_rates)*vat_rates as vatss from rc_invoices ".$flt." and dates='".$tdates."' and invoice_types='Primary Invoice' and paymode='cash' group by invoice_number";

$sqqr="select sum(box_qty*box_price+box_qty*box_price*discount_rates) as netsalesd from rc_invoices ".$flt." and dates='".$tdates."' and invoice_types='Primary Invoice' and paymode='credit'";

$sqr="select sum(box_qty*box_price+box_qty*box_price*discount_rates)*vat_rates as vatssd from rc_invoices ".$flt." and dates='".$tdates."' and invoice_types='Primary Invoice' and paymode='credit' group by invoice_number";

$sqqt="select sum(box_qty*box_price+box_qty*box_price*discount_rates) as netslm from rc_invoices ".$flt." and dates>='".$starts."' and dates<='".$ends."' and invoice_types='Primary Invoice' and paymode='cash'";

$sqt="select sum(box_qty*box_price+box_qty*box_price*discount_rates)*vat_rates as vatsm from rc_invoices ".$flt." and dates>='".$starts."' and dates<='".$ends."' and invoice_types='Primary Invoice' and paymode='cash' group by invoice_number";

$sqqtc="select sum(box_qty*box_price+box_qty*box_price*discount_rates) as netslmcr from rc_invoices ".$flt." and dates>='".$starts."' and dates<='".$ends."' and invoice_types='Primary Invoice' and paymode='credit'";

$sqtc="select sum(box_qty*box_price+box_qty*box_price*discount_rates)*vat_rates as vatsmcr from rc_invoices ".$flt." and dates>='".$starts."' and dates<='".$ends."' and invoice_types='Primary Invoice' and paymode='credit' group by invoice_number";

printf("<BR><table width='70%%'><tr><td width='40%%'>");
printf("<form method='post' action='saccdash.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);

$vatmoncr=0;
$qr=mysqli_query($conn,$sqtc);
while($rsvatmcr=mysqli_fetch_row($qr)){
$vatmoncr=$vatmoncr+$rsvatmcr[0];
}

$salmoncr=0;
$qr=mysqli_query($conn,$sqqtc);
while($rssalmc=mysqli_fetch_row($qr)){
$salmoncr=$salmoncr+$rssalmc[0];
}

$vatmon=0;
$qr=mysqli_query($conn,$sqt);
while($rsvatm=mysqli_fetch_row($qr)){
$vatmon=$vatmon+$rsvatm[0];
}

$salmon=0;
$qr=mysqli_query($conn,$sqqt);
while($rssalm=mysqli_fetch_row($qr)){
$salmon=$salmon+$rssalm[0];
}

$vatd=0;
$qr=mysqli_query($conn,$sq);
while($rstt=mysqli_fetch_row($qr)){
$vatd=$vatd+$rstt[0];
}

$vatdc=0;
$qr=mysqli_query($conn,$sqr);
while($rstc=mysqli_fetch_row($qr)){
$vatdc=$vatdc+$rstc[0];
}

$vatdcm=0;
$qr=mysqli_query($conn,$sqqr);
while($rstcm=mysqli_fetch_row($qr)){
$vatdcm=$vatdcm+$rstcm[0];
}

$tdv=0;
$tmv=0;
$qry=mysqli_query($conn,$sqq);
while($rst=mysqli_fetch_row($qry)){
$tdv=$tdv+$rst[0];
}

$totpaymd=$tdv+$vatd+$vatdc+$vatdcm;
$totpaymdm=$vatmon+$salmon+$vatmoncr+$salmoncr;

printf("<tr style='border:1px solid silver;'><td><font color='blue'>Cash</td><td align='right'>%s</td><td align='right'>%s</td></tr>",
number_format($tdv+$vatd,0,'.',','),number_format($vatmon+$salmon,0,'.',','));

printf("<tr style='border:1px solid silver;'><td><font color='red'>Credit</td><td align='right'>%s</td><td align='right'>%s</td></tr>",
number_format($vatdc+$vatdcm,0,'.',','),number_format($vatmoncr+$salmoncr,0,'.',','));

printf("<tr style='border:1px solid silver;font-weight:bold;'><td>Total</td><td align='right'>%s</td><td align='right'>%s</td></tr>",number_format($totpaymd,0,'.',','),number_format($totpaymdm,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'>");

////////////////////////////////////////////////////////////////////////////////////////////////////////////////////AGENCY SALES PAYMENT SETTLEMENTS/////////////////


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%%'>Payment Distributor</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%%'>Payment Detail</td><td width='25%%'>%s</td><td width='25%%'>%s</td></tr>",$tdates,$tmonths);

//////////////////////////////AGENCY INVOICED CODE//////////////
$gtslmonth="select sum(box_qty*box_price+box_qty*box_price*discount_rates) as netslgt from rc_invoices ".$flt." and dates>='".$starts."' and dates<='".$ends."' and invoice_types='Primary Invoice' and agent_types='AGENCY'";

$gtvatmonth="select sum(box_qty*box_price+box_qty*box_price*discount_rates)*vat_rates as vatsmgt from rc_invoices ".$flt." and dates>='".$starts."' and dates<='".$ends."' and invoice_types='Primary Invoice' and agent_types='AGENCY' group by invoice_number";

$gtslm=0;
$qr=mysqli_query($conn,$gtslmonth);
while($rsgtsl=mysqli_fetch_row($qr)){
$gtslm=$gtslm+$rsgtsl[0];
}

$gtvatm=0;
$qr=mysqli_query($conn,$gtvatmonth);
while($rsgtvat=mysqli_fetch_row($qr)){
$gtvatm=$gtvatm+$rsgtvat[0];
}

$gtslday="select sum(box_qty*box_price+box_qty*box_price*discount_rates) as netslgt from rc_invoices ".$flt." and dates='".$tdates."' and invoice_types='Primary Invoice' and agent_types='AGENCY'";

$gtvatday="select sum(box_qty*box_price+box_qty*box_price*discount_rates)*vat_rates as vatsmgt from rc_invoices ".$flt." and dates='".$tdates."' and invoice_types='Primary Invoice' and agent_types='AGENCY' group by invoice_number";

$gtsldy=0;
$qr=mysqli_query($conn,$gtslday);
while($rsgtsld=mysqli_fetch_row($qr)){
$gtsldy=$gtsldy+$rsgtsld[0];
}

$gtvatdy=0;
$qr=mysqli_query($conn,$gtvatday);
while($rsgtvatd=mysqli_fetch_row($qr)){
$gtvatdy=$gtvatdy+$rsgtvatd[0];
}

printf("<tr style='border:0px solid silver;'><td><font color='blue'>Invoiced</td><td align='right'>%s</td><td align='right'>%s</td></tr>",
number_format($gtsldy+$gtvatdy,0,'.',','),number_format($gtslm+$gtvatm,0,'.',','));

//////***************************////////////////////////////////////

$gtslmonpyntr="select sum(box_qty*box_price+box_qty*box_price*discount_rates) as netslgt from rc_invoices ".$flt." and dates>='".$starts."' and dates<='".$ends."' and invoice_types='Primary Invoice' and agent_types='AGENCY' and payments='PAYMENT NR'";

$gtvatmonthpyntr="select sum(box_qty*box_price+box_qty*box_price*discount_rates)*vat_rates as vatsmgt from rc_invoices ".$flt." and dates>='".$starts."' and dates<='".$ends."' and invoice_types='Primary Invoice' and agent_types='AGENCY' and payments='PAYMENT NR' group by invoice_number";

$gtslmpnr=0;
$qr=mysqli_query($conn,$gtslmonpyntr);
while($rsgtslpnr=mysqli_fetch_row($qr)){
$gtslmpnr=$gtslmpnr+$rsgtslpnr[0];
}

$gtvatmpnr=0;
$qr=mysqli_query($conn,$gtvatmonthpyntr);
while($rsgtvatpnr=mysqli_fetch_row($qr)){
$gtvatmpnr=$gtvatmpnr+$rsgtvatpnr[0];
}

$gtslday="select sum(box_qty*box_price+box_qty*box_price*discount_rates) as netslgtnr from rc_invoices ".$flt." and dates='".$tdates."' and invoice_types='Primary Invoice' and agent_types='AGENCY' and payments='PAYMENT NR'";

$gtvatday="select sum(box_qty*box_price+box_qty*box_price*discount_rates)*vat_rates as vatsmgtnr from rc_invoices ".$flt." and dates='".$tdates."' and invoice_types='Primary Invoice' and agent_types='AGENCY' and payments='PAYMENT NR' group by invoice_number";

$gtsldy=0;
$qr=mysqli_query($conn,$gtslday);
while($rsgtsld=mysqli_fetch_row($qr)){
$gtsldy=$gtsldy+$rsgtsld[0];
}

$gtvatdy=0;
$qr=mysqli_query($conn,$gtvatday);
while($rsgtvatd=mysqli_fetch_row($qr)){
$gtvatdy=$gtvatdy+$rsgtvatd[0];
}

printf("<tr style='border:0px solid silver;'><td>Payment Not Recieved</td><td align='right'>%s</td><td align='right'>%s</td></tr>",
number_format($gtsldy+$gtvatdy,0,'.',','),number_format($gtslmpnr+$gtvatmpnr,0,'.',','));


///////////***************////////////////////////break---ALTERNATION/////////

$gtslmonalter="select sum(box_qty*box_price+box_qty*box_price*discount_rates) as netslgtalter from rc_invoices ".$flt." and dates>='".$starts."' and dates<='".$ends."' and invoice_types='Primary Invoice' and agent_types='AGENCY' and payments='ALTERNATION'";

$gtvatmonalter="select sum(box_qty*box_price+box_qty*box_price*discount_rates)*vat_rates as vatsmgtalter from rc_invoices ".$flt." and dates>='".$starts."' and dates<='".$ends."' and invoice_types='Primary Invoice' and agent_types='AGENCY' and payments='ALTERNATION' group by invoice_number";

$gtslmtalterm=0;
$qr=mysqli_query($conn,$gtslmonalter);
while($rsgtslalter=mysqli_fetch_row($qr)){
$gtslmtalterm=$gtslmtalterm+$rsgtslalter[0];
}

$gtvatmalter=0;
$qr=mysqli_query($conn,$gtvatmonalter);
while($rsgtvatalter=mysqli_fetch_row($qr)){
$gtvatmalter=$gtvatmalter+$rsgtvatalter[0];
}

$gtsldayalt="select sum(box_qty*box_price+box_qty*box_price*discount_rates) as netslgtalt from rc_invoices ".$flt." and dates='".$tdates."' and invoice_types='Primary Invoice' and agent_types='AGENCY' and payments='ALTERNATION'";

$gtvatdayalt="select sum(box_qty*box_price+box_qty*box_price*discount_rates)*vat_rates as vatsmgtalt from rc_invoices ".$flt." and dates='".$tdates."' and invoice_types='Primary Invoice' and agent_types='AGENCY' and payments='ALTERNATION' group by invoice_number";

$gtsldyalt=0;
$qr=mysqli_query($conn,$gtsldayalt);
while($rsgtsldalt=mysqli_fetch_row($qr)){
$gtsldyalt=$gtsldyalt+$rsgtsldalt[0];
}

$gtvatdyalt=0;
$qr=mysqli_query($conn,$gtvatdayalt);
while($rsgtvatdalt=mysqli_fetch_row($qr)){
$gtvatdyalt=$gtvatdyalt+$rsgtvatdalt[0];
}


printf("<tr style='border:0px solid silver;'><td>Chequ-Alteration</td><td align='right'>%s</td><td align='right'>%s</td></tr>",
number_format($gtsldyalt+$gtvatdyalt,0,'.',','),number_format($gtslmtalterm+$gtvatmalter,0,'.',','));



///////////////////////////////break AGENCY UNLOAD ISSU////////////////////////////////

$gtslmonundl="select sum(box_qty*box_price+box_qty*box_price*discount_rates) as netslgtundl from rc_invoices ".$flt." and dates>='".$starts."' and dates<='".$ends."' and invoice_types='Primary Invoice' and agent_types='AGENCY' and payments='UNLD UNDLV ISSUE'";

$gtvatmonundl="select sum(box_qty*box_price+box_qty*box_price*discount_rates)*vat_rates as vatsmgtundl from rc_invoices ".$flt." and dates>='".$starts."' and dates<='".$ends."' and invoice_types='Primary Invoice' and agent_types='AGENCY' and payments='UNLD UNDLV ISSUE' group by invoice_number";

$gtslundlm=0;
$qr=mysqli_query($conn,$gtslmonundl);
while($rsgtslundlm=mysqli_fetch_row($qr)){
$gtslundlm=$gtslundlm+$rsgtslundlm[0];
}

$gtvatmanundl=0;
$qr=mysqli_query($conn,$gtvatmonundl);
while($rsgtvatundl=mysqli_fetch_row($qr)){
$gtvatmanundl=$gtvatmanundl+$rsgtvatundl[0];
}

$gtsldayundl="select sum(box_qty*box_price+box_qty*box_price*discount_rates) as netslgtunld from rc_invoices ".$flt." and dates='".$tdates."' and invoice_types='Primary Invoice' and agent_types='AGENCY' and payments='UNLD UNDLV ISSUE'";

$gtvatdayaundl="select sum(box_qty*box_price+box_qty*box_price*discount_rates)*vat_rates as vatsmgtundld from rc_invoices ".$flt." and dates='".$tdates."' and invoice_types='Primary Invoice' and agent_types='AGENCY' and payments='UNLD UNDLV ISSUE' group by invoice_number";

$gtsldyundl=0;
$qr=mysqli_query($conn,$gtsldayundl);
while($rsgtsldundl=mysqli_fetch_row($qr)){
$gtsldyundl=$gtsldyundl+$rsgtsldundl[0];
}

$gtvatdyundl=0;
$qr=mysqli_query($conn,$gtvatdayaundl);
while($rsgtvatdundl=mysqli_fetch_row($qr)){
$gtvatdyundl=$gtvatdyundl+$rsgtvatdundl[0];
}

printf("<tr style='border:0px solid silver;'><td>Unlod-Undeliver not update</td><td align='right'>%s</td><td align='right'>%s</td></tr>",
number_format($gtsldyundl+$gtvatdyundl,0,'.',','),number_format($gtslundlm+$gtvatmanundl,0,'.',','));


////////////////////////////////break AGENCY CASH NDP//////////////////////////////

$gtslmoncd="select sum(box_qty*box_price+box_qty*box_price*discount_rates) as netslgtcdm from rc_invoices ".$flt." and dates>='".$starts."' and dates<='".$ends."' and invoice_types='Primary Invoice' and agent_types='AGENCY' and payments='CASH NOT DEPOSIT'";

$gtvatmoncdm="select sum(box_qty*box_price+box_qty*box_price*discount_rates)*vat_rates as vatsmgtcdm from rc_invoices ".$flt." and dates>='".$starts."' and dates<='".$ends."' and invoice_types='Primary Invoice' and agent_types='AGENCY' and payments='CASH NOT DEPOSIT' group by invoice_number";

$gtslcdm=0;
$qr=mysqli_query($conn,$gtslmoncd);
while($rsgtslcdm=mysqli_fetch_row($qr)){
$gtslcdm=$gtslcdm+$rsgtslcdm[0];
}

$gtvatmancdm=0;
$qr=mysqli_query($conn,$gtvatmoncdm);
while($rsgtvatcdm=mysqli_fetch_row($qr)){
$gtvatmancdm=$gtvatmancdm+$rsgtvatcdm[0];
}

$gtsldaycd="select sum(box_qty*box_price+box_qty*box_price*discount_rates) as netslgtcdd from rc_invoices ".$flt." and dates='".$tdates."' and invoice_types='Primary Invoice' and agent_types='AGENCY' and payments='CASH NOT DEPOSIT'";

$gtvatdaycd="select sum(box_qty*box_price+box_qty*box_price*discount_rates)*vat_rates as vatsgtmcdd from rc_invoices ".$flt." and dates='".$tdates."' and invoice_types='Primary Invoice' and agent_types='AGENCY' and payments='CASH NOT DEPOSIT' group by invoice_number";

$gtsldycd=0;
$qr=mysqli_query($conn,$gtsldaycd);
while($rsgtsldcd=mysqli_fetch_row($qr)){
$gtsldycd=$gtsldycd+$rsgtsldcd[0];
}

$gtvatdycd=0;
$qr=mysqli_query($conn,$gtvatdaycd);
while($rsgtvatdcd=mysqli_fetch_row($qr)){
$gtvatdycd=$gtvatdycd+$rsgtvatdcd[0];
}

printf("<tr style='border:0px solid silver;'><td>Cash Not Deposited</td><td align='right'>%s</td><td align='right'>%s</td></tr>",
number_format($gtsldycd+$gtvatdycd,0,'.',','),number_format($gtslcdm+$gtvatmancdm,0,'.',','));



//////////////////////////////break AGENCY OVEVRDATE/////////////////////////////////////////

$gtslmonovd="select sum(box_qty*box_price+box_qty*box_price*discount_rates) as netslgtovdm from rc_invoices ".$flt." and dates>='".$starts."' and dates<='".$ends."' and invoice_types='Primary Invoice' and agent_types='AGENCY' and payments='OVER DATE'";

$gtvatmonovdm="select sum(box_qty*box_price+box_qty*box_price*discount_rates)*vat_rates as vatsmgtovdm from rc_invoices ".$flt." and dates>='".$starts."' and dates<='".$ends."' and invoice_types='Primary Invoice' and agent_types='AGENCY' and payments='OVER DATE' group by invoice_number";

$gtslovdm=0;
$qr=mysqli_query($conn,$gtslmonovd);
while($rsgtslovdm=mysqli_fetch_row($qr)){
$gtslovdm=$gtslovdm+$rsgtslovdm[0];
}

$gtvatmanovdm=0;
$qr=mysqli_query($conn,$gtvatmonovdm);
while($rsgtvatovdm=mysqli_fetch_row($qr)){
$gtvatmanovdm=$gtvatmanovdm+$rsgtvatovdm[0];
}

$gtsldayovd="select sum(box_qty*box_price+box_qty*box_price*discount_rates) as netslgtovdd from rc_invoices ".$flt." and dates='".$tdates."' and invoice_types='Primary Invoice' and agent_types='AGENCY' and payments='OVER DATE'";

$gtvatdayovd="select sum(box_qty*box_price+box_qty*box_price*discount_rates)*vat_rates as vatsmgtovdd from rc_invoices ".$flt." and dates='".$tdates."' and invoice_types='Primary Invoice' and agent_types='AGENCY' and payments='OVER DATE' group by invoice_number";

$gtsldyovd=0;
$qr=mysqli_query($conn,$gtsldayovd);
while($rsgtsldovd=mysqli_fetch_row($qr)){
$gtsldyovd=$gtsldyovd+$rsgtsldovd[0];
}

$gtvatdyovd=0;
$qr=mysqli_query($conn,$gtvatdayovd);
while($rsgtvatdovd=mysqli_fetch_row($qr)){
$gtvatdyovd=$gtvatdyovd+$rsgtvatdovd[0];
}

printf("<tr style='border:0px solid silver;'><td>Over Dated Cheque</td><td align='right'>%s</td><td align='right'>%s</td></tr>",
number_format($gtsldyovd+$gtvatdyovd,0,'.',','),number_format($gtslovdm+$gtvatmanovdm,0,'.',','));


//////////////////////////////break AGENCY invoice copy.......//////////////////

$gtslmoninvnr="select sum(box_qty*box_price+box_qty*box_price*discount_rates) as netslgtinvnr from rc_invoices ".$flt." and dates>='".$starts."' and dates<='".$ends."' and invoice_types='Primary Invoice' and agent_types='AGENCY' and payments='INV COPY NR'";

$gtvatmoninvnr="select sum(box_qty*box_price+box_qty*box_price*discount_rates)*vat_rates as vatsmgtinvnr from rc_invoices ".$flt." and dates>='".$starts."' and dates<='".$ends."' and invoice_types='Primary Invoice' and agent_types='AGENCY' and payments='INV COPY NR' group by invoice_number";

$gtslinvnr=0;
$qr=mysqli_query($conn,$gtslmoninvnr);
while($rsgtslinvnr=mysqli_fetch_row($qr)){
$gtslinvnr=$gtslinvnr+$rsgtslinvnr[0];
}

$gtvatmaninvnr=0;
$qr=mysqli_query($conn,$gtvatmoninvnr);
while($rsgtvatinvnr=mysqli_fetch_row($qr)){
$gtvatmaninvnr=$gtvatmaninvnr+$rsgtvatinvnr[0];
}

$gtsldayinvnr="select sum(box_qty*box_price+box_qty*box_price*discount_rates) as netslgtinvnrr from rc_invoices ".$flt." and dates='".$tdates."' and invoice_types='Primary Invoice' and agent_types='AGENCY' and payments='INV COPY NR'";

$gtvatdayinvnr="select sum(box_qty*box_price+box_qty*box_price*discount_rates)*vat_rates as vatsgtinvnr from rc_invoices ".$flt." and dates='".$tdates."' and invoice_types='Primary Invoice' and agent_types='AGENCY' and payments='INV COPY NR' group by invoice_number";

$gtsldyinvnr=0;
$qr=mysqli_query($conn,$gtsldayinvnr);
while($rsgtsldinvnr=mysqli_fetch_row($qr)){
$gtsldyinvnr=$gtsldyinvnr+$rsgtsldinvnr[0];
}

$gtvatdyinvnr=0;
$qr=mysqli_query($conn,$gtvatdayinvnr);
while($rsgtvatdinvnr=mysqli_fetch_row($qr)){
$gtvatdyinvnr=$gtvatdyinvnr+$rsgtvatdinvnr[0];
}

printf("<tr style='border:0px solid silver;'><td>Inv Copy Not Recived</td><td align='right'>%s</td><td align='right'>%s</td></tr>",
number_format($gtsldyinvnr+$gtvatdyinvnr,0,'.',','),number_format($gtslinvnr+$gtvatmaninvnr,0,'.',','));


///////////////////////break//////////AGENCY partpayment due//////////////////

$sqq="select invnom,patpm,patpd from (select distinct invoice_number as invnom from st_paymode ".$flt." and dates>='".$starts."' and dates<='".$ends."' and payments='PART PAYMENT' OR payments='CLAIM-PARTSETLEMENT') as patypes

left join(select invoice_number,sum(tot_amounts) as patpm from st_paymode ".$flt." and dates>='".$starts."' and dates<='".$ends."' and payments='PART PAYMENT' OR payments='CLAIM-PARTSETLEMENT' and pay_types<>'Open Balance' and pay_types<>'Cheque-Rtn' and pay_types<>'Security' and pay_types<>'Claim Hold' and agent_types='AGENCY') as agg on agg.invoice_number=invnom

left join(select invoice_number,sum(tot_amounts) as patpd from st_paymode ".$flt." and dates='".$tdates."' and payments='PART PAYMENT' OR payments='CLAIM-PARTSETLEMENT' and pay_types<>'Open Balance' and pay_types<>'Cheque-Rtn' and pay_types<>'Security' and pay_types<>'Claim Hold' and agent_types='AGENCY') as aggm on aggm.invoice_number=invnom ";

$qry=mysqli_query($conn,$sqq);
$rstj=mysqli_fetch_row($qry);

//$tddv=$tddv+$rst[2]+$rst[4]+$rst[6]+$rst[8]+$rst[10]+$rst[12]+$rst[14]+$rstj[2];
//$tmmv=$tmmv+$rst[3]+$rst[5]+$rst[7]+$rst[9]+$rst[11]+$rst[13]+$rst[15]+$rstj[1];

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



////////////////////AGENCY not deliver//////////////////////////

$gtslmonndlv="select sum(box_qty*box_price+box_qty*box_price*discount_rates) as netslgtntdlv from rc_invoices ".$flt." and dates>='".$starts."' and dates<='".$ends."' and invoice_types='Primary Invoice' and agent_types='AGENCY' and payments='NOT DELIVERD'";

$gtvatmonntdlv="select sum(box_qty*box_price+box_qty*box_price*discount_rates)*vat_rates as vatsmgtntdlv from rc_invoices ".$flt." and dates>='".$starts."' and dates<='".$ends."' and invoice_types='Primary Invoice' and agent_types='AGENCY' and payments='NOT DELIVERD' group by invoice_number";

$gtslntdlv=0;
$qr=mysqli_query($conn,$gtslmonndlv);
while($rsgtslntdlv=mysqli_fetch_row($qr)){
$gtslntdlv=$gtslntdlv+$rsgtslntdlv[0];
}

$gtvatmanntdlv=0;
$qr=mysqli_query($conn,$gtvatmonntdlv);
while($rsgtvatntdlv=mysqli_fetch_row($qr)){
$gtvatmanntdlv=$gtvatmanntdlv+$rsgtvatntdlv[0];
}

$gtsldayntdlv="select sum(box_qty*box_price+box_qty*box_price*discount_rates) as netslgtntdlvv from rc_invoices ".$flt." and dates='".$tdates."' and invoice_types='Primary Invoice' and agent_types='AGENCY' and payments='NOT DELIVERD'";

$gtvatdayntdlv="select sum(box_qty*box_price+box_qty*box_price*discount_rates)*vat_rates as vatsgtntdlve from rc_invoices ".$flt." and dates='".$tdates."' and invoice_types='Primary Invoice' and agent_types='AGENCY' and payments='NOT DELIVERD' group by invoice_number";

$gtsldyntdlv=0;
$qr=mysqli_query($conn,$gtsldayntdlv);
while($rsgtsldntdlv=mysqli_fetch_row($qr)){
$gtsldyntdlv=$gtsldyntdlv+$rsgtsldntdlv[0];
}

$gtvatdyntdlv=0;
$qr=mysqli_query($conn,$gtvatdayntdlv);
while($rsgtvatdntdlv=mysqli_fetch_row($qr)){
$gtvatdyntdlv=$gtvatdyntdlv+$rsgtvatdntdlv[0];
}

printf("<tr style='border:0px solid silver;'><td><font color='red'>Not Delivered</td><td align='right'>%s</td><td align='right'>%s</td></tr>",
number_format($gtsldyntdlv+$gtvatdyntdlv,0,'.',','),number_format($gtslntdlv+$gtvatmanntdlv,0,'.',','));

///////////////total agency//////////////

$totagday=$gtsldy+$gtvatdy+$gtsldyalt+$gtvatdyalt+$gtsldyundl+$gtvatdyundl+$gtsldycd+$gtvatdycd+$gtsldyovd+$gtvatdyovd+$gtsldyinvnr+$gtvatdyinvnr+$rstj[2]+$gtsldyntdlv+$gtvatdyntdlv;
$totagmon=$gtslmpnr+$gtvatmpnr+$gtslmtalterm+$gtvatmalter+$gtslundlm+$gtvatmanundl+$gtslcdm+$gtvatmancdm+$gtslovdm+$gtvatmanovdm+$gtslinvnr+$gtvatmaninvnr+$rstj[1]+$gtslntdlv+$gtvatmanntdlv;

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

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


//////////////////////////////////////////////////////////////////////////////
//////////////////////////////////
          ///////////////////////PAYMENT DIRECT & ARMY/////////////////////////

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%%'>Payment Direct & Army</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%%'>Payment Detail</td><td width='25%%'>%s</td><td width='25%%'>%s</td></tr>",$tdates,$tmonths);

//////******///////////////////direct INVOICED CODE//////////////*******//////
$drslmonth="select sum(box_qty*box_price+box_qty*box_price*discount_rates) as netsldr from rc_invoices ".$flt." and dates>='".$starts."' and dates<='".$ends."' and invoice_types='Primary Invoice' and agent_types='DIRECT'";

$drvatmonth="select sum(box_qty*box_price+box_qty*box_price*discount_rates)*vat_rates as vatsmdr from rc_invoices ".$flt." and dates>='".$starts."' and dates<='".$ends."' and invoice_types='Primary Invoice' and agent_types='DIRECT' group by invoice_number";

$drslm=0;
$qr=mysqli_query($conn,$drslmonth);
while($rsdrsl=mysqli_fetch_row($qr)){
$drslm=$drslm+$rsdrsl[0];
}

$drvatm=0;
$qr=mysqli_query($conn,$drvatmonth);
while($rsdrvat=mysqli_fetch_row($qr)){
$drvatm=$drvatm+$rsdrvat[0];
}

$drslday="select sum(box_qty*box_price+box_qty*box_price*discount_rates) as netsldr from rc_invoices ".$flt." and dates='".$tdates."' and invoice_types='Primary Invoice' and agent_types='DIRECT'";

$drvatday="select sum(box_qty*box_price+box_qty*box_price*discount_rates)*vat_rates as vatsmdr from rc_invoices ".$flt." and dates='".$tdates."' and invoice_types='Primary Invoice' and agent_types='DIRECT' group by invoice_number";

$drsldy=0;
$qr=mysqli_query($conn,$drslday);
while($rsdrsld=mysqli_fetch_row($qr)){
$drsldy=$drsldy+$rsdrsld[0];
}

$drvatdy=0;
$qr=mysqli_query($conn,$drvatday);
while($rsdrvatd=mysqli_fetch_row($qr)){
$drvatdy=$drvatdy+$rsdrvatd[0];
}

printf("<tr style='border:0px solid silver;'><td>Invoiced</td><td align='right'>%s</td><td align='right'>%s</td></tr>",
number_format($drsldy+$drvatdy,0,'.',','),number_format($drslm+$drvatm,0,'.',','));

//////////////////////////break DIRECT PYNR///////////////////////////////////////////////
$drslmonpyntr="select sum(box_qty*box_price+box_qty*box_price*discount_rates) as netsldr from rc_invoices ".$flt." and dates>='".$starts."' and dates<='".$ends."' and invoice_types='Primary Invoice' and agent_types='DIRECT' and payments='PAYMENT NR'";

$drvatmonthpyntr="select sum(box_qty*box_price+box_qty*box_price*discount_rates)*vat_rates as vatsmdr from rc_invoices ".$flt." and dates>='".$starts."' and dates<='".$ends."' and invoice_types='Primary Invoice' and agent_types='DIRECT' and payments='PAYMENT NR' group by invoice_number";

$drslmpnr=0;
$qr=mysqli_query($conn,$gtslmonpyntr);
while($rsdrslpnr=mysqli_fetch_row($qr)){
$drslmpnr=$drslmpnr+$rsdrslpnr[0];
}

$drvatmpnr=0;
$qr=mysqli_query($conn,$gtvatmonthpyntr);
while($rsdrvatpnr=mysqli_fetch_row($qr)){
$drvatmpnr=$drvatmpnr+$rsdrvatpnr[0];
}

$drslday="select sum(box_qty*box_price+box_qty*box_price*discount_rates) as netsldrnr from rc_invoices ".$flt." and dates='".$tdates."' and invoice_types='Primary Invoice' and agent_types='DIRECT' and payments='PAYMENT NR'";

$drvatday="select sum(box_qty*box_price+box_qty*box_price*discount_rates)*vat_rates as vatsmdrnr from rc_invoices ".$flt." and dates='".$tdates."' and invoice_types='Primary Invoice' and agent_types='DIRECT' and payments='PAYMENT NR' group by invoice_number";

$drsldy=0;
$qr=mysqli_query($conn,$drslday);
while($rsdrsld=mysqli_fetch_row($qr)){
$drsldy=$drsldy+$rsdrsld[0];
}

$drvatdy=0;
$qr=mysqli_query($conn,$drvatday);
while($rsdrvatd=mysqli_fetch_row($qr)){
$drvatdy=$drvatdy+$rsdrvatd[0];
}
printf("<tr style='border:0px solid silver;'><td>Payment Not Recieved</td><td align='right'>%s</td><td align='right'>%s</td></tr>",
number_format($drsldy+$drvatdy,0,'.',','),number_format($drslmpnr+$drvatmpnr,0,'.',','));
////////////////////////////////break DIRECT ALT/////////////////////////////////////////

$drslmonalter="select sum(box_qty*box_price+box_qty*box_price*discount_rates) as netsldralter from rc_invoices ".$flt." and dates>='".$starts."' and dates<='".$ends."' and invoice_types='Primary Invoice' and agent_types='DIRECT' and payments='ALTERNATION'";

$drvatmonalter="select sum(box_qty*box_price+box_qty*box_price*discount_rates)*vat_rates as vatsmdralter from rc_invoices ".$flt." and dates>='".$starts."' and dates<='".$ends."' and invoice_types='Primary Invoice' and agent_types='DIRECT' and payments='ALTERNATION' group by invoice_number";

$drslmtalterm=0;
$qr=mysqli_query($conn,$drslmonalter);
while($rsdrslalter=mysqli_fetch_row($qr)){
$drslmtalterm=$drslmtalterm+$rsdrslalter[0];
}

$drvatmalter=0;
$qr=mysqli_query($conn,$drvatmonalter);
while($rsdrvatalter=mysqli_fetch_row($qr)){
$drvatmalter=$drvatmalter+$rsdrvatalter[0];
}

$drsldayalt="select sum(box_qty*box_price+box_qty*box_price*discount_rates) as netsldralt from rc_invoices ".$flt." and dates='".$tdates."' and invoice_types='Primary Invoice' and agent_types='DIRECT' and payments='ALTERNATION'";

$drvatdayalt="select sum(box_qty*box_price+box_qty*box_price*discount_rates)*vat_rates as vatsmdralt from rc_invoices ".$flt." and dates='".$tdates."' and invoice_types='Primary Invoice' and agent_types='DIRECT' and payments='ALTERNATION' group by invoice_number";

$drsldyalt=0;
$qr=mysqli_query($conn,$drsldayalt);
while($rsdrsldalt=mysqli_fetch_row($qr)){
$drsldyalt=$drsldyalt+$rsdrsldalt[0];
}

$drvatdyalt=0;
$qr=mysqli_query($conn,$drvatdayalt);
while($rsdrvatdalt=mysqli_fetch_row($qr)){
$drvatdyalt=$drvatdyalt+$rsdrvatdalt[0];
}

printf("<tr style='border:0px solid silver;'><td>Chequ-Alteration</td><td align='right'>%s</td><td align='right'>%s</td></tr>",
number_format($drsldyalt+$drvatdyalt,0,'.',','),number_format($drslmtalterm+$drvatmalter,0,'.',','));

/////////////////////////////break DIRECT UNDLV ISU///////////////////////////////
$drslmonundl="select sum(box_qty*box_price+box_qty*box_price*discount_rates) as netsldrundl from rc_invoices ".$flt." and dates>='".$starts."' and dates<='".$ends."' and invoice_types='Primary Invoice' and agent_types='DIRECT' and payments='UNLD UNDLV ISSUE'";

$drvatmonundl="select sum(box_qty*box_price+box_qty*box_price*discount_rates)*vat_rates as vatsmdrundl from rc_invoices ".$flt." and dates>='".$starts."' and dates<='".$ends."' and invoice_types='Primary Invoice' and agent_types='DIRECT' and payments='UNLD UNDLV ISSUE' group by invoice_number";

$drslundlm=0;
$qr=mysqli_query($conn,$drslmonundl);
while($rsdrslundlm=mysqli_fetch_row($qr)){
$drslundlm=$drslundlm+$rsdrslundlm[0];
}

$drvatmanundl=0;
$qr=mysqli_query($conn,$drvatmonundl);
while($rsdrvatundl=mysqli_fetch_row($qr)){
$drvatmanundl=$drvatmanundl+$rsdrvatundl[0];
}

$drsldayundl="select sum(box_qty*box_price+box_qty*box_price*discount_rates) as netslgrunld from rc_invoices ".$flt." and dates='".$tdates."' and invoice_types='Primary Invoice' and agent_types='DIRECT' and payments='UNLD UNDLV ISSUE'";

$drvatdayaundl="select sum(box_qty*box_price+box_qty*box_price*discount_rates)*vat_rates as vatsmgrundld from rc_invoices ".$flt." and dates='".$tdates."' and invoice_types='Primary Invoice' and agent_types='DIRECT' and payments='UNLD UNDLV ISSUE' group by invoice_number";

$drsldyundl=0;
$qr=mysqli_query($conn,$drsldayundl);
while($rsdrsldundl=mysqli_fetch_row($qr)){
$drsldyundl=$drsldyundl+$rsdrsldundl[0];
}

$drvatdyundl=0;
$qr=mysqli_query($conn,$drvatdayaundl);
while($rsdrvatdundl=mysqli_fetch_row($qr)){
$drvatdyundl=$drvatdyundl+$rsdrvatdundl[0];
}

printf("<tr style='border:0px solid silver;'><td>Unlod-Undeliver not update</td><td align='right'>%s</td><td align='right'>%s</td></tr>",
number_format($drsldyundl+$drvatdyundl,0,'.',','),number_format($drslundlm+$drvatmanundl,0,'.',','));
////////////////////////BREAK DIRECT CNPD//////////////////////////////////////////////
$drslmoncd="select sum(box_qty*box_price+box_qty*box_price*discount_rates) as netsldrcdm from rc_invoices ".$flt." and dates>='".$starts."' and dates<='".$ends."' and invoice_types='Primary Invoice' and agent_types='DIRECT' and payments='CASH NOT DEPOSIT'";

$drvatmoncdm="select sum(box_qty*box_price+box_qty*box_price*discount_rates)*vat_rates as vatsmdrcdm from rc_invoices ".$flt." and dates>='".$starts."' and dates<='".$ends."' and invoice_types='Primary Invoice' and agent_types='DIRECT' and payments='CASH NOT DEPOSIT' group by invoice_number";

$drslcdm=0;
$qr=mysqli_query($conn,$drslmoncd);
while($rsdrslcdm=mysqli_fetch_row($qr)){
$drslcdm=$drslcdm+$rsdrslcdm[0];
}

$drvatmancdm=0;
$qr=mysqli_query($conn,$drvatmoncdm);
while($rsdrvatcdm=mysqli_fetch_row($qr)){
$drvatmancdm=$drvatmancdm+$rsdrvatcdm[0];
}

$drsldaycd="select sum(box_qty*box_price+box_qty*box_price*discount_rates) as netsldrcdd from rc_invoices ".$flt." and dates='".$tdates."' and invoice_types='Primary Invoice' and agent_types='DIRECT' and payments='CASH NOT DEPOSIT'";

$drvatdaycd="select sum(box_qty*box_price+box_qty*box_price*discount_rates)*vat_rates as vatsdrmcdd from rc_invoices ".$flt." and dates='".$tdates."' and invoice_types='Primary Invoice' and agent_types='DIRECT' and payments='CASH NOT DEPOSIT' group by invoice_number";

$drsldycd=0;
$qr=mysqli_query($conn,$drsldaycd);
while($rsdrsldcd=mysqli_fetch_row($qr)){
$drsldycd=$drsldycd+$rsdrsldcd[0];
}

$drvatdycd=0;
$qr=mysqli_query($conn,$drvatdaycd);
while($rsdrvatdcd=mysqli_fetch_row($qr)){
$drvatdycd=$drvatdycd+$rsdrvatdcd[0];
}

printf("<tr style='border:0px solid silver;'><td>Cash Not Deposited</td><td align='right'>%s</td><td align='right'>%s</td></tr>",
number_format($drsldycd+$drvatdycd,0,'.',','),number_format($drslcdm+$drvatmancdm,0,'.',','));

/////////////////////////////BREAK DIRECT OVDCHQ\\\\\\\\\\\\\\\\/////////////////////////////////////////////
$drslmonovd="select sum(box_qty*box_price+box_qty*box_price*discount_rates) as netsldrovdm from rc_invoices ".$flt." and dates>='".$starts."' and dates<='".$ends."' and invoice_types='Primary Invoice' and agent_types='DIRECT' and payments='OVER DATE'";

$drvatmonovdm="select sum(box_qty*box_price+box_qty*box_price*discount_rates)*vat_rates as vatsmdrovdm from rc_invoices ".$flt." and dates>='".$starts."' and dates<='".$ends."' and invoice_types='Primary Invoice' and agent_types='DIRECT' and payments='OVER DATE' group by invoice_number";

$drslovdm=0;
$qr=mysqli_query($conn,$drslmonovd);
while($rsdrslovdm=mysqli_fetch_row($qr)){
$drslovdm=$drslovdm+$rsdrslovdm[0];
}

$drvatmanovdm=0;
$qr=mysqli_query($conn,$drvatmonovdm);
while($rsdrvatovdm=mysqli_fetch_row($qr)){
$drvatmanovdm=$drvatmanovdm+$rsdrvatovdm[0];
}

$drsldayovd="select sum(box_qty*box_price+box_qty*box_price*discount_rates) as netsldrovdd from rc_invoices ".$flt." and dates='".$tdates."' and invoice_types='Primary Invoice' and agent_types='DIRECT' and payments='OVER DATE'";

$drvatdayovd="select sum(box_qty*box_price+box_qty*box_price*discount_rates)*vat_rates as vatsmdrovdd from rc_invoices ".$flt." and dates='".$tdates."' and invoice_types='Primary Invoice' and agent_types='DIRECT' and payments='OVER DATE' group by invoice_number";

$drsldyovd=0;
$qr=mysqli_query($conn,$drsldayovd);
while($rsdrsldovd=mysqli_fetch_row($qr)){
$drsldyovd=$drsldyovd+$rsdrsldovd[0];
}

$drvatdyovd=0;
$qr=mysqli_query($conn,$drvatdayovd);
while($rsdrvatdovd=mysqli_fetch_row($qr)){
$drvatdyovd=$drvatdyovd+$rsdrvatdovd[0];
}

printf("<tr style='border:0px solid silver;'><td>Over Dated Cheque</td><td align='right'>%s</td><td align='right'>%s</td></tr>",
number_format($drsldyovd+$drvatdyovd,0,'.',','),number_format($drslovdm+$drvatmanovdm,0,'.',','));

///////////////////////to be continue////////////////////////////////////

printf("<tr style='border:0px solid silver;'><td>Inv Copy Not Recived</td><td align='right'>%s</td><td align='right'>%s</td></tr>",
number_format($rstp[12],0,'.',','),number_format($rstp[13],0,'.',','));

printf("<tr style='border:0px solid silver;'><td>Not Delivered</td><td align='right'>%s</td><td align='right'>%s</td></tr>",
number_format($rstp[14],0,'.',','),number_format($rstp[15],0,'.',','));

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

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


////////////////////////////////////////////////////////////////////////////////////////////////////////////////////BREAK//////////////////////////////////

////////MODERN //////////////// TRADEEE /////////////////// PAYMENT//////////////////////////////////////////////////\\\\\\\\\\\\\\\\\\\\\\\\\\

$mtslmonth="select sum(box_qty*box_price+box_qty*box_price*discount_rates) as netslmt from rc_invoices ".$flt." and dates>='".$starts."' and dates<='".$ends."' and invoice_types='Primary Invoice' and zones='modern'";

$mtvatmonth="select sum(box_qty*box_price+box_qty*box_price*discount_rates)*vat_rates as vatsmmt from rc_invoices ".$flt." and dates>='".$starts."' and dates<='".$ends."' and invoice_types='Primary Invoice' and zones='modern' group by invoice_number";

$mtslm=0;
$qr=mysqli_query($conn,$mtslmonth);
while($rsmtsl=mysqli_fetch_row($qr)){
$mtslm=$mtslm+$rsmtsl[0];
}

$mtvatm=0;
$qr=mysqli_query($conn,$mtvatmonth);
while($rsmtvat=mysqli_fetch_row($qr)){
$mtvatm=$mtvatm+$rsmtvat[0];
}

$mtslday="select sum(box_qty*box_price+box_qty*box_price*discount_rates) as netslmt from rc_invoices ".$flt." and dates='".$tdates."' and invoice_types='Primary Invoice' and zones='modern'";

$mtvatday="select sum(box_qty*box_price+box_qty*box_price*discount_rates)*vat_rates as vatsmmt from rc_invoices ".$flt." and dates='".$tdates."' and invoice_types='Primary Invoice' and zones='modern' group by invoice_number";

$mtsldy=0;
$qr=mysqli_query($conn,$mtslday);
while($rsmtsld=mysqli_fetch_row($qr)){
$mtsldy=$mtsldy+$rsmtsld[0];
}

$mtvatdy=0;
$qr=mysqli_query($conn,$mtvatday);
while($rsmtvatd=mysqli_fetch_row($qr)){
$mtvatdy=$mtvatdy+$rsmtvatd[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;background:#006699;color:white;'>
<td style='font-size:15;text-align:left;' width='100%%'>Payment Modern Trade</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%%'>Payment Detail</td><td width='25%%'>%s</td><td width='25%%'>%s</td></tr>",$tdates,$tmonths);

printf("<tr style='border:0px solid silver;'><td><font color='blue'>Invoiced</td><td align='right'>%s</td><td align='right'>%s</td></tr>",
number_format($mtsldy+$mtvatdy,0,'.',','),number_format($mtslm+$mtvatm,0,'.',','));

$mtslmonpyntr="select sum(box_qty*box_price+box_qty*box_price*discount_rates) as netslmt from rc_invoices ".$flt." and dates>='".$starts."' and dates<='".$ends."' and invoice_types='Primary Invoice' and zones='modern' and payments='PAYMENT NR'";

$mtvatmonthpyntr="select sum(box_qty*box_price+box_qty*box_price*discount_rates)*vat_rates as vatsmmt from rc_invoices ".$flt." and dates>='".$starts."' and dates<='".$ends."' and invoice_types='Primary Invoice' and zones='modern' and payments='PAYMENT NR' group by invoice_number";

$mtslmpnr=0;
$qr=mysqli_query($conn,$mtslmonpyntr);
while($rsmtslpnr=mysqli_fetch_row($qr)){
$mtslmpnr=$mtslmpnr+$rsmtslpnr[0];
}

$mtvatmpnr=0;
$qr=mysqli_query($conn,$mtvatmonthpyntr);
while($rsmtvatpnr=mysqli_fetch_row($qr)){
$mtvatmpnr=$mtvatmpnr+$rsmtvatpnr[0];
}

$mtslday="select sum(box_qty*box_price+box_qty*box_price*discount_rates) as netslmtnr from rc_invoices ".$flt." and dates='".$tdates."' and invoice_types='Primary Invoice' and zones='modern' and payments='PAYMENT NR'";

$mtvatday="select sum(box_qty*box_price+box_qty*box_price*discount_rates)*vat_rates as vatsmmtnr from rc_invoices ".$flt." and dates='".$tdates."' and invoice_types='Primary Invoice' and zones='modern' and payments='PAYMENT NR' group by invoice_number";

$mtsldy=0;
$qr=mysqli_query($conn,$mtslday);
while($rsmtsld=mysqli_fetch_row($qr)){
$mtsldy=$mtsldy+$rsmtsld[0];
}

$mtvatdy=0;
$qr=mysqli_query($conn,$mtvatday);
while($rsmtvatd=mysqli_fetch_row($qr)){
$mtvatdy=$mtvatdy+$rsmtvatd[0];
}

printf("<tr style='border:0px solid silver;'><td>Payment Not Recieved</td><td align='right'>%s</td><td align='right'>%s</td></tr>",
number_format($mtsldy+$mtvatdy,0,'.',','),number_format($mtslmpnr+$mtvatmpnr,0,'.',','));

///////////////////////////BREAK///////////////////////////////////////////



$mtslmonalter="select sum(box_qty*box_price+box_qty*box_price*discount_rates) as netslmtalter from rc_invoices ".$flt." and dates>='".$starts."' and dates<='".$ends."' and invoice_types='Primary Invoice' and zones='modern' and payments='ALTERNATION'";

$mtvatmonalter="select sum(box_qty*box_price+box_qty*box_price*discount_rates)*vat_rates as vatsmmtalter from rc_invoices ".$flt." and dates>='".$starts."' and dates<='".$ends."' and invoice_types='Primary Invoice' and zones='modern' and payments='ALTERNATION' group by invoice_number";

$mtslmtalterm=0;
$qr=mysqli_query($conn,$mtslmonalter);
while($rsmtslalter=mysqli_fetch_row($qr)){
$mtslmtalterm=$mtslmtalterm+$rsmtslalter[0];
}

$mtvatmalter=0;
$qr=mysqli_query($conn,$mtvatmonalter);
while($rsmtvatalter=mysqli_fetch_row($qr)){
$mtvatmalter=$mtvatmalter+$rsmtvatalter[0];
}

$mtsldayalt="select sum(box_qty*box_price+box_qty*box_price*discount_rates) as netslmtalt from rc_invoices ".$flt." and dates='".$tdates."' and invoice_types='Primary Invoice' and zones='modern' and payments='ALTERNATION'";

$mtvatdayalt="select sum(box_qty*box_price+box_qty*box_price*discount_rates)*vat_rates as vatsmmtalt from rc_invoices ".$flt." and dates='".$tdates."' and invoice_types='Primary Invoice' and zones='modern' and payments='ALTERNATION' group by invoice_number";

$mtsldyalt=0;
$qr=mysqli_query($conn,$mtslday);
while($rsmtsldalt=mysqli_fetch_row($qr)){
$mtsldyalt=$mtsldyalt+$rsmtsldalt[0];
}

$mtvatdyalt=0;
$qr=mysqli_query($conn,$mtvatday);
while($rsmtvatdalt=mysqli_fetch_row($qr)){
$mtvatdyalt=$mtvatdyalt+$rsmtvatdalt[0];
}

printf("<tr style='border:0px solid silver;'><td>Chequ-Alteration</td><td align='right'>%s</td><td align='right'>%s</td></tr>",
number_format($mtsldyalt+$mtvatdyalt,0,'.',','),number_format($mtslmtalterm+$mtvatmalter,0,'.',','));

////////////////////////////////\\\\\\\\\\\\\\\\\\\\\\\\\//////////////////////


$mtslmonundl="select sum(box_qty*box_price+box_qty*box_price*discount_rates) as netslmtundl from rc_invoices ".$flt." and dates>='".$starts."' and dates<='".$ends."' and invoice_types='Primary Invoice' and zones='modern' and payments='UNLD UNDLV ISSUE'";

$mtvatmonundl="select sum(box_qty*box_price+box_qty*box_price*discount_rates)*vat_rates as vatsmmtundl from rc_invoices ".$flt." and dates>='".$starts."' and dates<='".$ends."' and invoice_types='Primary Invoice' and zones='modern' and payments='UNLD UNDLV ISSUE' group by invoice_number";

$mtslundlm=0;
$qr=mysqli_query($conn,$mtslmonundl);
while($rsmtslundlm=mysqli_fetch_row($qr)){
$mtslundlm=$mtslundlm+$rsmtslundlm[0];
}

$mtvatmanundl=0;
$qr=mysqli_query($conn,$mtvatmonundl);
while($rsmtvatundl=mysqli_fetch_row($qr)){
$mtvatmanundl=$mtvatmanundl+$rsmtvatundl[0];
}

$mtsldayundl="select sum(box_qty*box_price+box_qty*box_price*discount_rates) as netslmtunld from rc_invoices ".$flt." and dates='".$tdates."' and invoice_types='Primary Invoice' and zones='modern' and payments='UNLD UNDLV ISSUE'";

$mtvatdayaundl="select sum(box_qty*box_price+box_qty*box_price*discount_rates)*vat_rates as vatsmmundld from rc_invoices ".$flt." and dates='".$tdates."' and invoice_types='Primary Invoice' and zones='modern' and payments='UNLD UNDLV ISSUE' group by invoice_number";

$mtsldyundl=0;
$qr=mysqli_query($conn,$mtsldayundl);
while($rsmtsldundl=mysqli_fetch_row($qr)){
$mtsldyundl=$mtsldyundl+$rsmtsldundl[0];
}

$mtvatdyundl=0;
$qr=mysqli_query($conn,$mtvatdayaundl);
while($rsmtvatdundl=mysqli_fetch_row($qr)){
$mtvatdyundl=$mtvatdyundl+$rsmtvatdundl[0];
}

printf("<tr style='border:0px solid silver;'><td>Unlod-Undeliver not update</td><td align='right'>%s</td><td align='right'>%s</td></tr>",
number_format($mtvatdyundl+$mtsldyundl,0,'.',','),number_format($mtslundlm+$mtvatmanundl,0,'.',','));

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

$mtslmoncd="select sum(box_qty*box_price+box_qty*box_price*discount_rates) as netslmtcdm from rc_invoices ".$flt." and dates>='".$starts."' and dates<='".$ends."' and invoice_types='Primary Invoice' and zones='modern' and payments='CASH NOT DEPOSIT'";

$mtvatmoncdm="select sum(box_qty*box_price+box_qty*box_price*discount_rates)*vat_rates as vatsmmtcdm from rc_invoices ".$flt." and dates>='".$starts."' and dates<='".$ends."' and invoice_types='Primary Invoice' and zones='modern' and payments='CASH NOT DEPOSIT' group by invoice_number";

$mtslcdm=0;
$qr=mysqli_query($conn,$mtslmoncd);
while($rsmtslcdm=mysqli_fetch_row($qr)){
$mtslcdm=$mtslcdm+$rsmtslcdm[0];
}

$mtvatmancdm=0;
$qr=mysqli_query($conn,$mtvatmoncdm);
while($rsmtvatcdm=mysqli_fetch_row($qr)){
$mtvatmancdm=$mtvatmancdm+$rsmtvatcdm[0];
}

$mtsldaycd="select sum(box_qty*box_price+box_qty*box_price*discount_rates) as netslmtcdd from rc_invoices ".$flt." and dates='".$tdates."' and invoice_types='Primary Invoice' and zones='modern' and payments='CASH NOT DEPOSIT'";

$mtvatdaycd="select sum(box_qty*box_price+box_qty*box_price*discount_rates)*vat_rates as vatsmmcdd from rc_invoices ".$flt." and dates='".$tdates."' and invoice_types='Primary Invoice' and zones='modern' and payments='CASH NOT DEPOSIT' group by invoice_number";

$mtsldycd=0;
$qr=mysqli_query($conn,$mtsldaycd);
while($rsmtsldcd=mysqli_fetch_row($qr)){
$mtsldycd=$mtsldycd+$rsmtsldcd[0];
}

$mtvatdycd=0;
$qr=mysqli_query($conn,$mtvatdaycd);
while($rsmtvatdcd=mysqli_fetch_row($qr)){
$mtvatdycd=$mtvatdycd+$rsmtvatdcd[0];
}

printf("<tr style='border:0px solid silver;'><td>Cash Not Deposited</td><td align='right'>%s</td><td align='right'>%s</td></tr>",
number_format($mtsldycd+$mtvatdycd,0,'.',','),number_format($mtslcdm+$mtvatmancdm,0,'.',','));

////////////////////////
$mtslmonovd="select sum(box_qty*box_price+box_qty*box_price*discount_rates) as netslmtovdm from rc_invoices ".$flt." and dates>='".$starts."' and dates<='".$ends."' and invoice_types='Primary Invoice' and zones='modern' and payments='OVER DATE'";

$mtvatmonovdm="select sum(box_qty*box_price+box_qty*box_price*discount_rates)*vat_rates as vatsmmtovdm from rc_invoices ".$flt." and dates>='".$starts."' and dates<='".$ends."' and invoice_types='Primary Invoice' and zones='modern' and payments='OVER DATE' group by invoice_number";

$mtslovdm=0;
$qr=mysqli_query($conn,$mtslmonovd);
while($rsmtslovdm=mysqli_fetch_row($qr)){
$mtslovdm=$mtslovdm+$rsmtslovdm[0];
}

$mtvatmanovdm=0;
$qr=mysqli_query($conn,$mtvatmonovdm);
while($rsmtvatovdm=mysqli_fetch_row($qr)){
$mtvatmanovdm=$mtvatmanovdm+$rsmtvatovdm[0];
}

$mtsldayovd="select sum(box_qty*box_price+box_qty*box_price*discount_rates) as netslmtovdd from rc_invoices ".$flt." and dates='".$tdates."' and invoice_types='Primary Invoice' and zones='modern' and payments='OVER DATE'";

$mtvatdayovd="select sum(box_qty*box_price+box_qty*box_price*discount_rates)*vat_rates as vatsmmovdd from rc_invoices ".$flt." and dates='".$tdates."' and invoice_types='Primary Invoice' and zones='modern' and payments='OVER DATE' group by invoice_number";

$mtsldyovd=0;
$qr=mysqli_query($conn,$mtsldayovd);
while($rsmtsldovd=mysqli_fetch_row($qr)){
$mtsldyovd=$mtsldyovd+$rsmtsldovd[0];
}

$mtvatdyovd=0;
$qr=mysqli_query($conn,$mtvatdayovd);
while($rsmtvatdovd=mysqli_fetch_row($qr)){
$mtvatdyovd=$mtvatdyovd+$rsmtvatdovd[0];
}

printf("<tr style='border:0px solid silver;'><td>Over Dated Cheque</td><td align='right'>%s</td><td align='right'>%s</td></tr>",
number_format($mtvatdyovd+$mtsldyovd,0,'.',','),number_format($mtslovdm+$mtvatmanovdm,0,'.',','));


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

$mtslmoninvnr="select sum(box_qty*box_price+box_qty*box_price*discount_rates) as netslmtinvnr from rc_invoices ".$flt." and dates>='".$starts."' and dates<='".$ends."' and invoice_types='Primary Invoice' and zones='modern' and payments='INV COPY NR'";

$mtvatmoninvnr="select sum(box_qty*box_price+box_qty*box_price*discount_rates)*vat_rates as vatsmmtinvnr from rc_invoices ".$flt." and dates>='".$starts."' and dates<='".$ends."' and invoice_types='Primary Invoice' and zones='modern' and payments='INV COPY NR' group by invoice_number";

$mtslinvnr=0;
$qr=mysqli_query($conn,$mtslmoninvnr);
while($rsmtslinvnr=mysqli_fetch_row($qr)){
$mtslinvnr=$mtslinvnr+$rsmtslinvnr[0];
}

$mtvatmaninvnr=0;
$qr=mysqli_query($conn,$mtvatmoninvnr);
while($rsmtvatinvnr=mysqli_fetch_row($qr)){
$mtvatmaninvnr=$mtvatmaninvnr+$rsmtvatinvnr[0];
}

$mtsldayinvnr="select sum(box_qty*box_price+box_qty*box_price*discount_rates) as netslmtinvnrr from rc_invoices ".$flt." and dates='".$tdates."' and invoice_types='Primary Invoice' and zones='modern' and payments='INV COPY NR'";

$mtvatdayinvnr="select sum(box_qty*box_price+box_qty*box_price*discount_rates)*vat_rates as vatsminvnr from rc_invoices ".$flt." and dates='".$tdates."' and invoice_types='Primary Invoice' and zones='modern' and payments='INV COPY NR' group by invoice_number";

$mtsldyinvnr=0;
$qr=mysqli_query($conn,$mtsldayinvnr);
while($rsmtsldinvnr=mysqli_fetch_row($qr)){
$mtsldyinvnr=$mtsldyinvnr+$rsmtsldinvnr[0];
}

$mtvatdyinvnr=0;
$qr=mysqli_query($conn,$mtvatdayinvnr);
while($rsmtvatdinvnr=mysqli_fetch_row($qr)){
$mtvatdyinvnr=$mtvatdyinvnr+$rsmtvatdinvnr[0];
}

printf("<tr style='border:0px solid silver;'><td>Inv Copy Not Recived</td><td align='right'>%s</td><td align='right'>%s</td></tr>",
number_format($mtsldyinvnr+$mtvatdyinvnr,0,'.',','),number_format($mtslinvnr+$mtvatmaninvnr,0,'.',','));
//////////////



$sqmt="select invnommt,patpmmt,patpdmt from (select distinct invoice_number as invnommt from st_paymode ".$flt." and dates>='".$starts."' and dates<='".$ends."' and payments='PART PAYMENT' OR payments='CLAIM-PARTSETLEMENT') as patypes

left join(select invoice_number,sum(tot_amounts) as patpmmt from st_paymode ".$flt." and dates>='".$starts."' and dates<='".$ends."' and payments='PART PAYMENT' OR payments='CLAIM-PARTSETLEMENT' and pay_types<>'Open Balance' and pay_types<>'Cheque-Rtn' and pay_types<>'Security' and pay_types<>'Claim Hold' and zones='modern') as agg on agg.invoice_number=invnommt

left join(select invoice_number,sum(tot_amounts) as patpdmt from st_paymode ".$flt." and dates='".$tdates."' and payments='PART PAYMENT' OR payments='CLAIM-PARTSETLEMENT' and pay_types<>'Open Balance' and pay_types<>'Cheque-Rtn' and pay_types<>'Security' and pay_types<>'Claim Hold' and zones='modern') as aggm on aggm.invoice_number=invnommt ";

$qry=mysqli_query($conn,$sqmt);
$rstmt=mysqli_fetch_row($qry);

//$tddmt=$tddv+$rst[2]+$rst[4]+$rst[6]+$rst[8]+$rst[10]+$rst[12]+$rst[14]+$rstmt[2];
//$tmmt=$tmmv+$rst[3]+$rst[5]+$rst[7]+$rst[9]+$rst[11]+$rst[13]+$rst[15]+$rstmt[1];

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

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

$mtslmonndlv="select sum(box_qty*box_price+box_qty*box_price*discount_rates) as netslmtntdlv from rc_invoices ".$flt." and dates>='".$starts."' and dates<='".$ends."' and invoice_types='Primary Invoice' and zones='modern' and payments='NOT DELIVERD'";

$mtvatmonntdlv="select sum(box_qty*box_price+box_qty*box_price*discount_rates)*vat_rates as vatsmmtntdlv from rc_invoices ".$flt." and dates>='".$starts."' and dates<='".$ends."' and invoice_types='Primary Invoice' and zones='modern' and payments='NOT DELIVERD' group by invoice_number";

$mtslntdlv=0;
$qr=mysqli_query($conn,$mtslmonndlv);
while($rsmtslntdlv=mysqli_fetch_row($qr)){
$mtslntdlv=$mtslntdlv+$rsmtslntdlv[0];
}

$mtvatmanntdlv=0;
$qr=mysqli_query($conn,$mtvatmonntdlv);
while($rsmtvatntdlv=mysqli_fetch_row($qr)){
$mtvatmanntdlv=$mtvatmanntdlv+$rsmtvatntdlv[0];
}

$mtsldayntdlv="select sum(box_qty*box_price+box_qty*box_price*discount_rates) as netslmtntdlvv from rc_invoices ".$flt." and dates='".$tdates."' and invoice_types='Primary Invoice' and zones='modern' and payments='NOT DELIVERD'";

$mtvatdayntdlv="select sum(box_qty*box_price+box_qty*box_price*discount_rates)*vat_rates as vatsmntdlve from rc_invoices ".$flt." and dates='".$tdates."' and invoice_types='Primary Invoice' and zones='modern' and payments='NOT DELIVERD' group by invoice_number";

$mtsldyntdlv=0;
$qr=mysqli_query($conn,$mtsldayntdlv);
while($rsmtsldntdlv=mysqli_fetch_row($qr)){
$mtsldyntdlv=$mtsldyntdlv+$rsmtsldntdlv[0];
}

$mtvatdyntdlv=0;
$qr=mysqli_query($conn,$mtvatdayntdlv);
while($rsmtvatdntdlv=mysqli_fetch_row($qr)){
$mtvatdyntdlv=$mtvatdyntdlv+$rsmtvatdntdlv[0];
}

printf("<tr style='border:0px solid silver;'><td><font color='red'>Not Delivered</td><td align='right'>%s</td><td align='right'>%s</td></tr>",
number_format($mtvatdyntdlv+$mtsldyntdlv,0,'.',','),number_format($mtslntdlv+$mtvatmanntdlv,0,'.',','));

$tdmt=$mtsldy+$mtvatdy+$mtsldyalt+$mtvatdyalt+$mtvatdyundl+$mtsldyundl+$mtsldycd+$mtvatdycd+$mtvatdyovd+$mtsldyovd+$mtsldyinvnr+$mtvatdyinvnr+$rstmt[2]+$mtvatdyntdlv+$mtsldyntdlv;

$tmmt=$mtslmpnr+$mtvatmpnr+$mtslmtalterm+$mtvatmalter+$mtslundlm+$mtvatmanundl+$mtslcdm+$mtvatmancdm+$mtslovdm+$mtvatmanovdm+$mtslinvnr+$mtvatmaninvnr+$rstmt[1]+$mtslntdlv+$mtvatmanntdlv;

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

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


/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////next part of page//////////////////////////////////////////////////////

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%%'>Realized and Return Payment</td></tr></table><br>");

$sqlst="select chq_pass,sum(tot_amounts) from st_paymode where deposit_date>='".$starts."' and  deposit_date<='".$tdates."' and agent_types='AGENCY' and chq_pass<>'Return' and chq_pass<>'' group by chq_pass ";

$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%%'>Distributor</td><td width='25%%'>Value</td></tr>");

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

printf("<tr style='border:0px solid silver;'><td>%s</td><td align='right'>%s</td></tr>",
$rst[0],number_format($rst[1]*-1,0,'.',','));
}
printf("<tr style='border:0px solid silver;font-weight:bold;'><td>Total-Realized</td><td align='right'>%s</td></tr>",
number_format($tdv,0,'.',','));
printf("</table><br><br>");


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

$sqlsttm="select chq_pass,sum(tot_amounts) from st_paymode where deposit_date>='".$starts."' and  deposit_date<='".$tdates."' and agent_types='DIRECT' and chq_pass<>'Return' and chq_pass<>'' group by chq_pass ";

$tdvvf=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%%'>Direct</td><td width='25%%'>Value</td></tr>");

$qry=mysqli_query($conn,$sqlsttm);
while($rstm=mysqli_fetch_row($qry)){
$tdvvf=$tdvvf+$rstm[1]*-1;

printf("<tr style='border:0px solid silver;'><td>%s</td><td align='right'>%s</td></tr>",
$rstm[0],number_format($rstm[1]*-1,0,'.',','));
}
printf("<tr style='border:0px solid silver;font-weight:bold;'><td>Total-Realized</td><td align='right'>%s</td></tr>",
number_format($tdvvf,0,'.',','));
printf("</table><br><br>");

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

$sqlsttmt="select chq_pass,sum(tot_amounts) from st_paymode where deposit_date>='".$starts."' and  deposit_date<='".$tdates."' and zones='Modern' and zones='CITY OFFICE' and chq_pass<>'Return' and chq_pass<>'' group by chq_pass ";

$tdvvft=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%%'>Modern</td><td width='25%%'>Value</td></tr>");

$qry=mysqli_query($conn,$sqlsttmt);
while($rstmt=mysqli_fetch_row($qry)){
$tdvvft=$tdvvft+$rstmt[1]*-1;

printf("<tr style='border:0px solid silver;'><td>%s</td><td align='right'>%s</td></tr>",
$rstmt[0],number_format($rstmt[1]*-1,0,'.',','));
}
printf("<tr style='border:0px solid silver;font-weight:bold;'><td>Total-Realized</td><td align='right'>%s</td></tr>",
number_format($tdvvft,0,'.',','));
printf("</table><br><br>");

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


$sqlsttmt="select chq_pass,sum(tot_amounts) from st_paymode where deposit_date>='".$starts."' and  deposit_date<='".$tdates."' and zones='ARMY SUPPLY' and chq_pass<>'Return' and chq_pass<>'' group by chq_pass ";

$tdvamt=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%%'>Army</td><td width='25%%'>Value</td></tr>");

$qry=mysqli_query($conn,$sqlsttmt);
while($rstm=mysqli_fetch_row($qry)){
$tdvamt=$tdvamt+$rstm[1]*-1;

printf("<tr style='border:0px solid silver;'><td>%s</td><td align='right'>%s</td></tr>",
$rstm[0],number_format($rstm[1]*-1,0,'.',','));
}
printf("<tr style='border:0px solid silver;font-weight:bold;'><td>Total-Realized</td><td align='right'>%s</td></tr>",
number_format($tdvamt,0,'.',','));
printf("</table><br><br>");


////////////////////////////////////////////////////////
//////////////next page line start after this///////////////////


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

$sqlst="select mzones,prvl,rdvl from (select distinct deposit_date as mzones from st_paymode ".$flt." and deposit_date>='".date('Y-m-01').
"' and deposit_date<='".date('Y-m-t')."' order by deposit_date) as ags

left join (select deposit_date,sum(tot_amounts)as prvl from st_paymode ".$flt." and deposit_date>='".date('Y-m-01')."' and deposit_date<='".date('Y-m-t')."' and chq_pass='Realized' group by deposit_date) as prpr on prpr.deposit_date=mzones

left join (select deposit_date,sum(tot_amounts)as rdvl from st_paymode ".$flt." and deposit_date>='".date('Y-m-01')."' and deposit_date<='".date('Y-m-t')."' and chq_pass='Cheque-Rtn' group by deposit_date) as rdrd on rdrd.deposit_date=mzones";

$tdp=0;
$trtn=0;

printf("<table style='width:100%%;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 Realization</td></tr><tr style='color:#00669;font-weight:bold;text-align:right;'>
<td align='left'>Day</td><td>Realized</td><td align='center'>Return</td></tr>");

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

$tdp=$tdp+$rst[1];
$trtn=$trtn+$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($tdp,0,'.',','),number_format($trtn,0,'.',','));
printf("</table><br><br>");
printf("</td></table>");
printf("</div>");
}
?>
</div>
</body>
</html>