<?php
require "racs.php";
require "vtshowsBBB.php";
session_start();
if(!isset($_SESSION['titles']) || !isset($_SESSION['positions']) || !isset($_SESSION['usernames'])){
header("location:index.php");}
?>
<html>
<title>Rtn Cheq-Payment</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">

<style>
tr:hover.clss{background-color:silver;}
</style>
<script>

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

$flt=racs(1);

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_dianaold");
if(isset($_REQUEST['starts'])){
//echo "starts ok";	
$starts=$_REQUEST['starts'];}
else{
$starts=date('Y-m-01');}

if(isset($_REQUEST['ends'])){
$ends=$_REQUEST['ends'];}
else{
$ends=date('Y-m-t');}

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

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

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

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

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

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

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

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

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

#$flt=racs(1);

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($agenttypes)!='all'){
$flt=$flt." and agent_types='".$agenttypes."' ";}
if(strtolower($terrys)!='all'){
$flt=$flt." and territory_names='".$terrys."' ";}
if(strtolower($items)!='all'){
$flt=$flt." and item_names='".$items."' ";}
if(strtolower($ranges)!='all'){
$flt=$flt." and item_ranges='".$ranges."' ";}
if(strtolower($subranges)!='all'){
$flt=$flt." and sub_ranges='".$subranges."' ";}

$flt=racs(1);
stps(1);
sacnew(1);//('pr','zone','gobs','sales','Periodic sales',$zones,$regions,$agents,$agenttypes,$terrys,$ranges,$subranges,$items);

printf("<div style='position:absolute;left:1%%;top:5%%;width:15%%;'>");

//stksnew(1);//prshows('pr','zone','gobs','sales','Periodic sales',$zones,$regions,$agents,$agenttypes,$terrys,$ranges,$subranges,$items);
//stksdvs(1);




printf("</div>");
printf("<div style='position:absolute;left:10%%;top:5%%;width:85%%;'>");
printf("<form method='post' action='addrtncqcpay.php'>");
printf("<br><table id='fms' style='width:100%%'><tr>");
printf("<td><input type='text' id='inputField' name='starts' size=8  value='%s'
style='font-size:13;background:#CACACA;border:1px solid #006699;border-radius:1px;font-family:calibri;color:#006699;'>
 - <input type='text' id='inputField2' name='ends' size=8  value='%s'
 style='font-size:13;background:#CACACA;border:1px solid #006699;border-radius:1px;font-family:calibri;color:#006699;'>
<input type='submit' value='Go'  style='font-size:13;background:#CACACA;color:#006699;border:1.5px solid #006699;border-radius:1pxfont-size:13;'>
</form>",$starts,$ends);
 printf("<td align='right' style='color:grey;font-size:12;'>Search
<input type='text' autocomplete='off' id='srctxt' size=28 style='font-family:calibri;font-size:13;border:1px solid silver;border-radius:1.5px;' onkeyup=\"srgs(event)\"></td>
<td style='font-size:12;color:green;text-align:center;background:silver;border:1px solid white;' onclick=\"scopys(1)\">Copy</td></tr></table>");
printf("<table id='dt' style='width:100%%;font-size:12;color:grey;border-collapse:collapse;'>");
printf("<tr style='border:1px solid silver;background:silver;'>
<td width='8%%' align='center'>Cheq-Date</td>
<td width='8%%' align='center'>Cheq-Num</td>
<td width='8%%' align='center'>Invoice</td>
<td width='25%%' align='center'>Customer</td>
<td width='10%%' align='center'>Territory</td>
<td width='10%%' align='center'>Status</td>
<td width='10%%' align='center'>Rtn-Cheq</td>
<td width='10%%' align='center'>Claim</td>
<td width='5%%' align='center'>Srtn</td>
<td width='5%%' align='center'>Undlv</td>
<td width='5%%' align='center'>Mrtn</td>
<td width='10%%' align='center'>Cash</td>
<td width='10%%' align='center'>Cheq</td>
<td width='10%%' align='center'>Charges</td>
<td width='5%%' align='center'>Salry</td>
<td width='5%%' align='center'>Fisu</td>
<td width='5%%' align='center'>Interst</td>
<td width='10%%' align='center'>Due</td>
<td width='10%%'>Payment</td>
<td width='10%%'>Status</td>
</tr>");

$result="select date,manubil,agent,agtype,srtnv,mrtnv,cashv,cheqv,claimamt,chgamt,undlv,invnum,claimdate,dlvdtpay,cheqrtn,salry,fisu,intscl,chargesrtn from(select distinct chq_rtn_num as manubil,agent_names as agent,invoice_number as invnum from st_paymode ".$flt." and dates>='".$starts."' and dates<='".$ends."' and setl_types='Rtn' group by chq_rtn_num,agent_names,invoice_number) as ags

left join(select agent_names,chq_rtn_num,invoice_number,territory_names as agtype,sum(claim_amount) as claimamt,sum(charges_amount )as chgamt,sum(srtn_amount) as srtnv,sum(mrtn_amount) as mrtnv,sum(cash_amount) as cashv,sum(undlv_amount) as undlv,dlv_dates as dlvdtpay,claim_month as claimdate,dates as date,sum(salwage) as salry,sum(fisue) as fisu,sum(clminterest) as intscl from st_paymode ".$flt." and dates>='".$starts."' and dates<='".$ends."' and pay_types<>'Charges-Rtnchq' group by chq_rtn_num,agent_names,invoice_number) as agm on agm.chq_rtn_num=manubil and agm.agent_names=agent and agm.invoice_number=invnum

left join(select invoice_number,agent_names,chq_rtn_num,sum(cheq_amount) as cheqrtn from st_paymode ".$flt." and dates>='".$starts."' and dates<='".$ends."' and pay_types='Cheque-Rtn' group by chq_rtn_num,agent_names,invoice_number) as agmd on agmd.chq_rtn_num=manubil and agmd.agent_names=agent and agmd.invoice_number=invnum

left join(select invoice_number,agent_names,chq_rtn_num,sum(charges_amount) as chargesrtn from st_paymode ".$flt." and dates>='".$starts."' and dates<='".$ends."' and pay_types='Charges-Rtnchq' group by chq_rtn_num,agent_names,invoice_number) as agmdu on agmdu.chq_rtn_num=manubil and agmdu.agent_names=agent and agmdu.invoice_number=invnum

left join(select invoice_number,agent_names,chq_rtn_num,sum(cheq_amount) as cheqv from st_paymode ".$flt." and dates>='".$starts."' and dates<='".$ends."' and pay_types<>'Cheque-Rtn' group by chq_rtn_num,agent_names,invoice_number) as agmdn on agmdn.chq_rtn_num=manubil and agmdn.agent_names=agent and agmdn.invoice_number=invnum ";

//$result="select chq_number,invoice_number,agent_names,territory_names,sum(claim_amount),sum(charges_amount ),sum(srtn_amount),sum(mrtn_amount),sum(cash_amount),sum(undlv_amount),dates,salwage,fisue,clminterest,sum(cheq_amount) from st_paymode ".$flt." and dates>='".$starts."' and dates<='".$ends."' and chq_number<>'' and pay_types='Cheque-Rtn' group by chq_number,agent_names,invoice_number";

$due=0;
$invamt=0;
$netchq=0;
$qrym=mysqli_query($conn,$result);
while($rstv=mysqli_fetch_row($qrym)){
//$netchq=$rstv[8]-$rstv[18];    
$chqamt=$rstv[14]+$rstv[18];    
$due=$chqamt-$rstv[4]-$rstv[5]-$rstv[6]-$rstv[7]-$rstv[8]-$rstv[9]-$rstv[10]-$rstv[15]-$rstv[16]-$rstv[17];
//11Cheq-Date	0Cheq-Num	1Invoice	2Customer	3Territory	3Status	15Rtn-Cheq	4Claim	6Srtn	10Undlv	7Mrtn	8Cash	9Cheq	5Charges	12Salry	13Fisu	14Interst	15Due	Payment	Status

//$dhrf="<a href='dbchqrtdeposit.php?actp=dvnts&agents=".$rstv[2]."&chqnum=".$rstv[0]."&invnum=".$rstv[1]."&chqdt=".$rstv[11]."&terri=".$rstv[3]."&dates=".$rstv[11]."&chqamount=".$rstv[15]."' target='_blank'>Action</a>";

//$dhrf="<a href='dbchqrtdeposit.php?actp=dvnts&ddates=".$rstv[10]."&invnum=".$rstv[1]."&chqamount=".$rstv[14]."&chqdt=".$rstv[11]."&chqnum=".$rstv[0]."&voucher=".$rstv[1]."&agents=".$rstv[2]."&claims=".$rstv[4]."&charges=".$rstv[5]."&srtnvl=".$rstv[6]."&mrtnvl=".$rstv[7]."&duamt=".$due."&undnvl=".$rstv[10]."&chqdate=".$rstv[10]."&chqamt=".$chqamt."' target='_blank'>Payment</a>";

//0date,1manubil,2agent,3agtype,4srtnv,5mrtnv,6cashv,7cheqv,8claimamt,9chgamt,10undlv,11invnum,12claimdate,13dlvdtpay,14cheqrtn,15salry,16fisu,17intscl
//Cheq-Date	Cheq-Num	Invoice	Customer	Territory	Status	Rtn-Cheq	Claim	Srtn	Undlv	Mrtn	Cash	Cheq	Charges	Salry	Fisu	Interst	Due	Payment	Status

$dhrf="<a href='dbchqrtdeposit.php?actp=dvnts&ddates=".$rstv[0]."&invnum=".$rstv[11]."&chqamount=".$due."&chqdt=".$rstv[0]."&chqnum=".$rstv[1]."&voucher=".$rstv[11]."&agents=".$rstv[2]."&claims=".$rstv[8]."&charges=".$rstv[9]."&srtnvl=".$rstv[4]."&mrtnvl=".$rstv[5]."&duamt=".$due."&undnvl=".$rstv[10]."&chqdate=".$rstv[0]."&chqamt=".$chqamt."&salwage=".$rstv[15]."&fisue=".$rstv[16]."&clinter=".$rstv[17]."' target='_blank'>Payment</a>";

//$dhrff="<a href='rpmpaysts.php?actp=dvnts&voucher=".$rstv[1]."&agents=".$rstv[2]."&status=".$rstv[16]."' target='_blank'>Status</a>";

printf("<tr class='clss' style='border:1px solid silver;'><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td bgcolor='#7AD1F9'>%s</td><td bgcolor='#7AF9A6'>%s</td><td bgcolor='#E1E106'>%s</td><td bgcolor='#DCC4E5'>%s</td><td bgcolor='#F3637F'>%s</td><td bgcolor='#63C3F3'>%s</td><td>%s</td><td bgcolor='#F1D6F3'>%s</td><td bgcolor='#F1D6F3'>%s</td><td bgcolor='#A6F7CD' >%s</td><td>%s</td><td>%s</td><td>%s</td><td bgcolor='#D9DDDF'>%s</td></tr>",

//$rstv[10],$rstv[0],$rstv[1],$rstv[2],$rstv[3],$rstv[3],number_format($rstv[14],2,'.',','),number_format($rstv[4],2,'.',','),number_format($rstv[6],2,'.',','),number_format($rstv[10],2,'.',','),number_format($rstv[7],2,'.',','),number_format($rstv[8],2,'.',','),number_format($rstv[9],2,'.',','),number_format($rstv[5],2,'.',','),number_format($rstv[12],2,'.',','),number_format($rstv[13],2,'.',','),number_format($rstv[14],2,'.',','),number_format($rstv[15],2,'.',','),$dhrf,$dhrff);}

$rstv[0],$rstv[1],$rstv[11],$rstv[2],$rstv[3],$rstv[3],number_format($rstv[14],2,'.',','),number_format($rstv[8],2,'.',','),number_format($rstv[4],2,'.',','),number_format($rstv[10],2,'.',','),number_format($rstv[5],2,'.',','),number_format($rstv[6],2,'.',','),number_format($rstv[7],2,'.',','),number_format($rstv[9],2,'.',','),number_format($rstv[15],2,'.',','),number_format($rstv[16],2,'.',','),number_format($rstv[17],2,'.',','),number_format($due,2,'.',','),$dhrf,$dhrff);}
//}
printf("</div>");

}
?>
</div>
</body>
</html>