<?php
//error_reporting(E_ALL);
//ini_set('display_errors',1);
require "racs.php";
require "fgstyles.php";
session_start();
if(!isset($_SESSION['titles']) || !isset($_SESSION['positions'])|| !isset($_SESSION['usernames'])){
header("location:index.php");}
date_default_timezone_set('Asia/Colombo');
$dateod=date('y-m-d');
?>
<html>
<title>Diana|FG-Inventory</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;color:red;}
</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[0].innerText.toLowerCase();
rvl=dt.rows[cnt].cells[1].innerText.toLowerCase();
svl=dt.rows[cnt].cells[4].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");
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['stores'])){
$stores=$_REQUEST['stores'];}
else{
$stores='all';}

if(isset($_REQUEST['fstores'])){
$fstoress=$_REQUEST['fstores'];}
else{
$fstoress='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."' ";}
if(strtolower($stores)!='all'){
$flt=$flt." and stores_names='".$stores."' ";}
if(strtolower($fstoress)!='all'){
$flt=$flt." and com_tecs='".$fstoress."' ";}

$flt=racs(1);

$conn=mysqli_connect("localhost","primarysales","S5L7lF01EQZvQrGK","primarysales_diana");
$sqlst="select stkview from acs where user_names='".$_SESSION['usernames']."' ";
$qry=mysqli_query($conn,$sqlst);

$rst=mysqli_fetch_row($qry);
if($rst[0]==0){
printf("<div style='position:absolute;top:100;left:200;font-size:16;'>Access Denied</div>");}
else{


stps(1);
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);

printf("</div>");
printf("<div style='position:absolute;left:15%%;top:5%%;width:85%%;'>");
printf("<div style='font-size:18;font-weight:bold;color:#3498DB;'>STOCK MOVEMENT SUMMERY</div>");


printf("<form method='post' action='vtprststocksummary.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;'>",$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(" <select id='fstores' name='fstores' style='font-size:13;background:#CACACA;border:1px solid #006699;border-radius:1px;font-family:calibri;color:#006699;'>
//<option>%s</option><option>All</option>",$_REQUEST['fstores']);
//$sqlst="select distinct com_tec from itemsmrd ";
//$qry=mysqli_query($conn,$sqlst);

//while($rst=mysqli_fetch_row($qry)){
//printf("<option>%s</option>",$rst[0]);}
//printf("</select>");
printf(" <input type='submit' 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>");

printf("<table id='dt' style='width:100%%;color:grey;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:10%%;'>Range</td>
<td style='border:1px solid white;width:10%%;'>Channel</td>
<td style='border:1px solid white;width:10%%;'>W-House</td>
<td style='border:1px solid white;width:10%%;'>Code</td>
<td style='border:1px solid white;width:27%%;'>Item</td>
<td style='border:1px solid white;width:8%%;'>Open stock</td>
<td style='border:1px solid white;width:8%%;'>FG-GTN</td>
<td style='border:1px solid white;width:8%%;'>SR-GRN</td>
<td style='border:1px solid white;width:8%%;'>Inv-Dlv</td>
<td style='border:1px solid white;width:8%%;'>FG-Transfer</td>
<td style='border:1px solid white;width:8%%;'>GT-MT</td>
<td style='border:1px solid white;width:8%%;'>STK-Adjust</td>
<td style='border:1px solid white;width:8%%;'>Free-Dlv</td>
<td style='border:1px solid white;width:8%%;'>Donet-Dlv</td>
<td style='border:1px solid white;width:8%%;'>MRP</td>
<td style='border:1px solid white;width:8%%;'>Phy-qty</td>
<td style='border:1px solid white;width:8%%;'>Pending-Inv-Box</td>
<td style='border:1px solid white;width:8%%;'>Pending-Free-Pcs</td>
<td style='border:1px solid white;width:8%%;'>Pending-Donation-Pcs</td>
<td style='border:1px solid white;width:8%%;'>Invoiceable-Qty</td>
<td style='border:1px solid white;width:7%%;text-align:center;'>Open</td></tr>");

$sflt="";
$stores=$_REQUEST['stores'];
if($stores!='All'){
$sflt=" and stores_names='".$stores."' ";}
//echo $stores;

$sqlst="select rgs,ritems,fst,grns,uns,dvs,trf,pnvl,stprice,adj,mycom,frisu,itmcds,mrpp,store,gtmt,dnsdlv,pnfvl,pnogvl,itmranges from (select distinct item_ranges as rgs,item_names as ritems,itemcodes as itmcds from rc_invoices ".$flt." union select distinct item_ranges,item_names,itemcodes as itmcds from st_notes ".$flt." union select distinct item_ranges,item_names,itemcodes as itmcds from rc_freeinvoices ".$flt." union select distinct item_ranges,item_names,itemcodes as itmcds from rc_orgdonation ".$flt.") as tms 

left join (select item_names,itemcodes,box_price as stprice,com_tecs as mycom,box_mrp as mrpp,stores_names as store,item_ranges as itmranges from st_notes ".$flt." and dates>'2022-07-23' group by itemcodes) as ghy on ghy.itemcodes=itmcds

left join (select item_names,itemcodes,sum(stock_box_qty) as fst from st_notes ".$flt." and dates>='2023-07-23' and dates<'".$starts."' group by itemcodes) as bfs on bfs.itemcodes=itmcds

left join (select item_names,itemcodes,sum(stock_box_qty) as grns from st_notes ".$flt." and dates>='".$starts."' and dates<='".$ends."' and note_types='FG GTN' group by itemcodes) as fgrs on fgrs.itemcodes=itmcds

left join (select item_names,itemcodes,sum(stock_box_qty) as uns from st_notes ".$flt." and dates>='".$starts."' and dates<='".$ends."' and note_types='SR-GRN-STO' group by itemcodes) as nds on nds.itemcodes=itmcds

left join (select item_names,itemcodes,sum(stock_box_qty*-1) as dvs from st_notes ".$flt." and dates>='".$starts."' and dates<='".$ends."' and note_types='Delivery note' group by itemcodes) as dts on dts.itemcodes=itmcds

left join (select item_names,itemcodes,sum(stock_box_qty*-1) as trf from st_notes ".$flt." and dates>='".$starts."' and dates<='".$ends."' and 
note_types='FG TRF PRO' group by itemcodes) as trns on trns.itemcodes=itmcds

left join (select item_names,itemcodes,sum(stock_box_qty) as adj from st_notes ".$flt." and dates>='".$starts."' and dates<='".$ends."' and 
note_types='Stock Adjustment' group by itemcodes) as try on try.itemcodes=itmcds

left join (select item_names,itemcodes,sum(stock_box_qty*-1) as frisu from st_notes ".$flt." and dates>='".$starts."' and dates<='".$ends."' and 
note_types='Fisue note' group by itemcodes) as frs on frs.itemcodes=itmcds

left join (select item_names,itemcodes,sum(stock_box_qty*-1) as dnsdlv from st_notes ".$flt." and dates>='".$starts."' and dates<='".$ends."' and 
note_types='Donation note' group by itemcodes) as dndlv on dndlv.itemcodes=itmcds

left join (select item_names,itemcodes,sum(stock_box_qty) as gtmt from st_notes ".$flt." and dates>='".$starts."' and dates<='".$ends."' and 
note_types='GT ITM TRF' group by itemcodes) as gtmts on gtmts.itemcodes=itmcds

left join (select item_names,itemcodes,sum(box_qty)-sum((dv_box_qty-undv_box_qty)) as pnvl from rc_invoices ".$flt." and dates>='2023-07-23' and dates<='".$ends."' and invoice_types='Primary Invoice' group by itemcodes) as fgrsn on fgrsn.itemcodes=itmcds 

left join (select item_names,itemcodes,sum(pcs_qty)-sum(dv_pcs_qty) as pnfvl from rc_freeinvoices ".$flt." and dates>='2023-07-23' and dates<='".$ends."' and invoice_types='FREE ISSUE' group by itemcodes) as fris on fris.itemcodes=itmcds

left join (select item_names,itemcodes,sum(pcs_qty)-sum(dv_pcs_qty) as pnogvl from rc_orgdonation ".$flt." and dates>='2023-07-23' and dates<='".$ends."' and invoice_types='DONATION' group by itemcodes) as ogis on ogis.itemcodes=itmcds order by rgs,ritems,mycom";

//left join (select item_names,itemcodes,sum(box_qty)-sum((dv_box_qty+undv_box_qty)) as pnvl from rc_invoices ".$flt." and dates>='2021-08-01' and dates<='".$ends."' and invoice_types='Primary Invoice' and agent_names<>'Cash Sales' and agent_names<>'INTERNAL OTHER' and agent_names<>'INTERNAL SALES' group by itemcodes,item_names) as fgrsn on fgrsn.item_names=ritems and fgrsn.itemcodes=itmcds order by rgs,ritems,mycom";
//echo $sqlst;
//echo $dateod;
$sagents="";
//$storee=$rst[14];

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

$dhrf="<a href='vtprstbincard.php?ranges=".$rst[0]."&itemnames=".urlencode($rst[1])."&items=".urlencode($rst[12])."&fstores=".$rst[14]."&starts=".$starts."&ends=".$ends."' target='_blank'><font color='red'>BIN</font></a>";
$cbs=0;
$phybl=0;
$clbl=0;
if($rst[2]!=0 || $rst[3]!=0 || $rst[4]!=0 || $rst[5]!=0 || $rst[6]!=0){	 
$cbs=$rst[2]+$rst[3]+$rst[4]-$rst[5]-$rst[6]-$rst[11]+$rst[9]+$rst[15];
$phybl=$cbs-$rst[7];
$phyblval=$phybl*$rst[8];
$clbl=$cbs*$rst[8];

//rgs,1ritems,2fst,3grns,4uns,5dvs,6trf,7pnvl,8stprice,9adj,10mycom,11frisu,12itmcds,13mrpp
printf("<tr class='clss' style='border:1px solid silver;color:black;'>
<td>%s</td>
<td>%s</td>
<td>%s</td>
<td>%s</td>
<td>%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;'>%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;'>%s</td>
<td align='right' style='font-size:13;'><font color='red'>%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;color:red'>%s</td>
<td bgcolor='33FFDA' align='right' style='font-size:13;'>%s</td>
<td bgcolor='#FA8A72' align='right' style='font-size:13;'>%s</td>
<td bgcolor='60E7CF' align='right' style='font-size:13;'>%s</td>
<td bgcolor='C4E760' align='right' style='font-size:13;'>%s</td>
<td align='right' style='font-size:13;color:red'>%s</td>

<td align='center' bgcolor='EFAFF0'>%s</td>",
$rst[19],
$rst[10],
$rst[14],
$rst[12],
$rst[1],
number_format($rst[2],3,'.',','),
number_format($rst[3],0,'.',','),
number_format($rst[4],0,'.',','),
number_format($rst[5],0,'.',','),
number_format($rst[6],0,'.',','),
number_format($rst[15],0,'.',','),
number_format($rst[9],0,'.',','),
number_format($rst[11],0,'.',','),
number_format($rst[16],3,'.',''),
number_format($rst[13],0,'.',''),
number_format($cbs,0,'.',','),
//number_format($clbl,0,'.',','),
number_format($rst[7],1,'.',','),
number_format($rst[17],1,'.',','),
number_format($rst[18],1,'.',','),
number_format($phybl,0,'.',','),
//number_format($phyblval,0,'.',','),
$dhrf);
}
}
printf("</div>");
}
}
?>
</div>
</body>
</html>