<?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>DB-Ledger</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>
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\nContact Sales and Marketing division</div>");
}
else{
$conn=mysqli_connect("localhost","primarysales","S5L7lF01EQZvQrGK","primarysales_diana");

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';}

if(isset($_REQUEST['agen'])){
$agen=$_REQUEST['agen'];}
else{
$agen='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."' ";}

$flt=racs(1);
stps(1);

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

printf("</div>");

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

printf("<div style='font-size:18;font-weight:bold;color:#3498DB;'>DISTRIBUTOR - $agents </div>");

printf("<form method='post' action='dbledger.php?ranges=%s&items=%s'>",$ranges,$items);
printf("<br><table id='fms' style='width:100%%;background:silver;'><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;'>",$starts);
printf(" - <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;'>",$ends);

printf(" <input type='hidden' value='Go'  style='font-size:13;background:#CACACA;color:grey;border:1.5px solid #006699;border-radius:1pxfont-size:13;'>
</form>");
 
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>");

if(strtolower($agents)!='all'){
$sflt=$sflt." and agent_names='".$agents."' ";}

$erf="select sum(cheq_amount) as pdchq from st_paymode ".$flt." and chq_pass='Cheq to realize' ".$sflt."";
$qtys=mysqli_query($conn,$erf);
$pds=mysqli_fetch_row($qtys);

$erfg="select sum(cheq_amount) as cqrtn from st_paymode ".$flt." and pay_types
='Cheque-Rtn' and chq_pass<>'Return Settle' ".$sflt."";
$qtyss=mysqli_query($conn,$erfg);
$crtns=mysqli_fetch_row($qtyss);

$erfg="select sum(tot_amounts) as gurantamt from st_paymode ".$flt." and pay_types='Security' and dates>='2019-02-28' and dates<='".$ends."' ".$sflt."";
$qtys=mysqli_query($conn,$erfg);
$secdp=mysqli_fetch_row($qtys);

$hlclaim="select sum(tot_amounts) as  clhold from st_paymode ".$flt." and pay_types='Claim Hold' and dates>='2019-02-28' and dates<='".$ends."' ".$sflt."";
$totsec=0;
$qtys=mysqli_query($conn,$hlclaim);
$clbld=mysqli_fetch_row($qtys);

printf("<table style='width:60%%;color:black;border-collapse:collapse;font-size:13;'>");
printf("<tr><td style='width:20%%;'>Period</td><td>%s - %s</td></tr>",$starts,$ends);
printf("<tr><td style='width:20%%;'><font color='green'>Bank Gurantee</td><td>%s</td></tr>",number_format($secdp[0],2,'.',','));
printf("<tr><td style='width:20%%;'><font color='blue'>Claim Hold</td><td>%s</td></tr>",number_format($clbld[0],2,'.',','));
printf("<tr><td style='width:20%%;'><font color='purple'>Total Security</td><td>%s</td></tr>",number_format(($secdp[0]+$clbld[0]),2,'.',','));
printf("<tr><td style='width:20%%;'>Cheq-To Realize</td><td>%s</td></tr>",number_format($pds[0],2,'.',','));
printf("<tr><td style='width:20%%;'><font color='red'>Cheq-Return</td><td>%s</td></tr>",number_format($crtns[0],2,'.',','));
printf("</table><br>");


printf("<table id='dt' style='width:100%%;color:black;border-collapse:collapse;font-size:13;'>");
printf("<tr valign='top' style='border:1px solid silver;background:silver;color:grey;text-align:center;font-weight:bold;'>
<td style='border:1px solid white;width:8%%;'>Remarks</td>
<td style='border:1px solid white;width:10%%;'>Type</td>
<td style='border:1px solid white;width:8%%;'>Invoice number</td>
<td style='border:1px solid white;width:7%%;'>Debit</td>
<td style='border:1px solid white;width:7%%;'>Credit</td>
<td style='border:1px solid white;width:8%%;'>Balance</td>
</tr>");

$bfqtys=0;
$adqtys=0;
$dtqtys=0;

$sqst="select sum(tot_amounts) as opbll from st_paymode ".$flt." and dates>='2019-03-31' and dates<'".$starts."' ".$sflt." and pay_types<>'Security' group by agent_names ";

$opbl=0;
$qqst=mysqli_query($conn,$sqst);
$rvs=mysqli_fetch_row($qqst);
$opbl=$rvs[0];

$sqlst="select sum(box_qty*box_price)+sum(box_qty*box_price)*discount_rates as discount from rc_invoices ".$flt." ".$sflt." and dates>='2019-03-31' and invoice_types='primary invoice' and paysts>'0' ";

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

printf("<tr style='border:1px solid silver;'><td align='center'>%s</td><td>Opening Balance B/F</td><td></td><td></td><td></td><td align='right' style='font-size:13;'bgcolor='#d4f442'>%s</td></tr>",$starts,number_format($opbl,2,'.',','));
printf("<tr style='border:1px solid silver;'><td align='center'>%s</td><td><font color='red'>Pending Payment for Invoices</td><td></td><td></td><td></td><td align='right' style='font-size:13;' bgcolor='#f4c741'>%s</td></tr>",$ends,number_format($netpeninv,2,'.',','));

$bfqtys=$netpeninv+$opbl;
$sqlst="select dates,pay_types,invoice_number,inv_amount,tot_amounts,coment_area from st_paymode ".$flt." and dates>='".$starts."' and dates<='".$ends."' ".$sflt." and tot_amounts<>0 and pay_types<>'Open Balance' and pay_types<>'Security' order by dates";

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

$adqtys=0;
$dtqtys=0;
$bfqtys=$bfqtys+$rst[4];

if($rst[4]>0){
$adqtys=$rst[4];}
else{
$dtqtys=($rst[4]*-1);}
	
printf("<tr style='border:1px solid silver;'><td align='left'>%s</td><td>%s</td><td align='center'>%s</td><td align='right' style='font-size:13;'>%s</td><td align='right' style='font-size:13;'>%s</td>
<td align='right' style='font-size:13;' bgcolor='#A6F7CD'>%s</td>",
$rst[5],$rst[1],$rst[2],number_format($adqtys,2,'.',','),number_format($dtqtys,2,'.',','),
number_format($bfqtys,2,'.',','));
}
printf("</div>");
}
?>
</div>
</body>
</html>