<?php

require "showshp.php";

session_start();
if(!isset($_SESSION['titles']) || !isset($_SESSION['positions'])|| !isset($_SESSION['usernames'])){
header("location:index.php");
}

?>

<html>
<head>


<link rel="stylesheet" type="text/css" href="rdsty.css">
<link rel="stylesheet" type="text/css" media="all" href="jsDatePick_ltr.min.css" />
<link rel="stylesheet" type="text/css" media="all" href="jsDatePick_ltr.css" />
<script type="text/javascript" src="jsDatePick.min.1.3.js"></script>
<script src="datesps.js"></script>

<script>

function scopys(pr)
{
var dts=document.getElementById("dtd");

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 fdtfinds(pr)
{

var vl=document.getElementById("dtfinds").value;
var dt=document.getElementById("dtd");
var cn=dt.rows.length;
var vls=vl.toLowerCase();

for(var cnt=2;cnt<cn;cnt++){
	
var tmp=dt.rows[cnt].cells[0].innerHTML;
var tmpa=tmp.toLowerCase();
var tmp=dt.rows[cnt].cells[1].innerHTML;
var tmpb=tmp.toLowerCase();
var sta=tmpa.indexOf(vls);
var stb=tmpb.indexOf(vls);

if((sta>=0 || stb>=0 ) && vls.length>0){
dt.rows[cnt].style.display="table-row";}
else{
dt.rows[cnt].style.display="none";}

if(vls.length==0){
dt.rows[cnt].style.display="table-row";}
}

	
}


</script>

</head>


<body style="font-family:calibri;font-size:small" onload="mnh('amnc','agentsb')">
<div class="hdn"></div>


<?php
printf("<div class='ses'>%s (%s)</div>",$_SESSION['titles'],$_SESSION['positions']);
?>

<div class="tops" align="center">Stock Reoder summary</div>
<div class="homes"><a href="index.php" style='color:#A9BCF5;'>Sign out</a></div>


<?php

$grt=0;
$grq=0;
$arr=array();


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

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

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


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

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

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

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

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

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




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


$months=date('m');
$days=date('t');
$years=date('Y');


if($starts==0 || $starts==''){
$starts=$years."-".$months."-01";}
if($ends==0 || $ends==''){
$ends=$years."-".$months."-".$days;}


showsdsr();

printf("</div><div class='srg'></div>");
printf("<div class='mains' style='width:80%%;'>");





$conn=mysqli_connect("localhost","primarysales","S5L7lF01EQZvQrGK","primarysales_dianaold");
$seflt=" where agent_names is not null ";

$sesqlst="select * from acs where user_names='".$_SESSION['usernames']."'";
$seqry=mysqli_query($conn,$sesqlst);

while($serst=mysqli_fetch_row($seqry)){
if(strtolower($serst[5])!='all'){
$seflt=$seflt." and item_names='".$serst[5]."' ";}
if(strtolower($serst[6])!='all'){
$seflt=$seflt." and item_ranges='".$serst[6]."' ";}
if(strtolower($serst[7])!='all'){
$seflt=$seflt." and sub_ranges='".$serst[7]."' ";}
}


printf("<form method='post' action='reorderlevel.php?%s'>",$rpsty);


printf("<div class='fra'>From</div><div class='frb'>
<input type='text' size=10 class='starts'  name='starts' value='%s' id='inputField'></div><div class='frc'>To</div><div class='frd'><input type='text class='ends' size=10 name='ends' value='%s' id='inputField2'></div><div class='fre'><input type='submit' value='go' 

<div class='frf'></div><div class='frg'><input type='text' size=20 id='dtfinds' autocomplete='off' onkeydown=\"fdtfinds(event)\">
<td style='font-size:12;color:green;text-align:center;background:silver;border:1px solid white;' onclick=\"scopys(1)\">Copy</td></div></form>",$starts,$ends);


printf("<div class='tdt'><br><table id='dtd' style='font-size:13'><th border='white' bgcolor='silver' colspan='13'><b>Stock value Rs.</th>");
printf("<tr bgcolor='grey'  style='color:#E6E6E6;'><td width=75 align='center' style='border:1px solid white' >Range</td><td width=1500 align='center' style='border:1px solid white' >Items</td><td width=200 align='center' style='border:1px solid white'>Opening Units</td><td width=200 align='center' style='border:1px solid white'>Primary Units
</td><td width=200 align='center' style='border:1px solid white'>Grn-Units</td><td width=200 align='center' style='border:1px solid white'>Cls-stk-Units</td>
<td style='border:1px solid white' width=200 align='center'>Opening Value</td><td width=200 style='border:1px solid white' align='center'>Primary Value</td>
<td width=200 style='border:1px solid white' align='center' >Grn-Value</td>
<td width=200 style='border:1px solid white' align='center'>Cls-Stk-Value</td><td width=350 align='center' style='border:1px solid white'>Re-Order</td><td width=220 style='border:1px solid white' align='center' >Balance</td></tr>");


$con=mysqli_connect("localhost","primarysales","S5L7lF01EQZvQrGK","primarysales_dianaold");

$fltt=$seflt." and dates>='2017-05-01' and dates<'".$_REQUEST['starts']."' ";
if(strtolower($ranges)!='all'){
$fltt=$fltt." and item_ranges='".$ranges."' ";}
if(strtolower($items)!='all'){
$fltt=$fltt." and item_names='".$items."' ";}
if(strtolower($subranges)!='all'){
$fltt=$fltt." and sub_ranges='".$subranges."' ";}



$flt=$seflt." and dates>='".$_REQUEST['starts']."' and dates<='".$_REQUEST['ends']."' ";
if(strtolower($ranges)!='all'){
$flt=$flt." and item_ranges='".$ranges."' ";}
if(strtolower($items)!='all'){
$flt=$flt." and item_names='".$items."' ";}
if(strtolower($subranges)!='all'){
$flt=$flt." and sub_ranges='".$subranges."' ";}



$result=mysqli_query($con,"select mranges,msubranges,mitems,tgvl,acvl,tgwt,acwt,rdqt,rdvl,acvll,acwtt,rdqtt,rdvll,frrd,mrvl from (select  distinct item_ranges as mranges,sub_ranges as msubranges,item_names as mitems
from rc_invoices order by mranges,msubranges,mitems) as avs 

left outer join(select item_ranges,sub_ranges,item_names,sum(pcs_qty/packets_per_box)+sum(box_qty) as acvl,
sum(box_qty*box_price) as acwt from rc_invoices  ".$flt." group by item_ranges,sub_ranges,item_names) as acs on acs.item_ranges=mranges and acs.sub_ranges=msubranges and acs.item_names=mitems

left outer join(select item_ranges,sub_ranges,item_names,sum(box_qty)+sum(pcs_qty) as rdqt,
sum(pcs_qty*pcs_price+box_qty*box_price) as rdvl,sum(pcs_qty*pcs_price+box_qty*box_price) as frrd from grn_stks ".$flt." group by item_ranges,sub_ranges,item_names) as abs on abs.item_ranges=mranges and abs.sub_ranges=msubranges and abs.item_names=mitems

left outer join(select item_ranges,sub_ranges,item_names,sum(box_qty)+sum(pcs_qty) mrvl from item_levels group by item_ranges,sub_ranges,item_names) as mrm on mrm.item_ranges=mranges and mrm.sub_ranges=msubranges and mrm.item_names=mitems


left outer join(select item_ranges,sub_ranges,item_names,sum(pcs_qty/packets_per_box)+sum(box_qty) as acvll,
sum(box_qty*box_price) as acwtt from rc_invoices  ".$fltt." group by item_ranges,sub_ranges,item_names) as acss on acss.item_ranges=mranges and acss.sub_ranges=msubranges and acss.item_names=mitems

left outer join(select item_ranges,sub_ranges,item_names,sum(pcs_qty)/packets_per_box+sum(box_qty) as rdqtt,
sum(pcs_qty*pcs_price)+sum(box_qty*box_price) as rdvll from grn_stks ".$fltt." group by item_ranges,sub_ranges,item_names) as abss on abss.item_ranges=mranges and abss.sub_ranges=msubranges and abss.item_names=mitems

left outer join(select item_ranges,sub_ranges,item_names,sum(box_qty)+sum(pcs_qty) as tgvl,sum(box_qty*box_price)+sum(pcs_qty*pcs_price) as tgwt from reoderr_stks ".$fltt." group by item_ranges,sub_ranges,item_names) as tgs on tgs.item_ranges=mranges and tgs.sub_ranges=msubranges 
and tgs.item_names=mitems");



$opnvl=0;
$privl=0;
$rdvlt=0;
$clvl=0;
$mrvl=0;
$frprvl=0;
$frrvl=0;
while($row = mysqli_fetch_array($result)) {
$opnvl=$opnvl+$row[tgwt]+$row[acwtt]-$row[rdvll];
$privl=$privl+$row[acwt];
$rdvlt=$rdvlt+$row[rdvl];
$clvl=$clvl+$row[tgwt]+$row[acwtt]-$row[rdvll]+$row[acwt]-$row[rdvl];
$mrvl=$mrvl+$row[mrvl];
$frprvl=$frprvl+$row[frrd];
$frrvl=$frrvl+$row[frrd];

printf("<tr class='cls' bgcolor='white' onmouseover=\"this.style.backgroundColor='#f5f5f5';\" onmouseout=\"this.style.backgroundColor='white';\"><td align='left'>%s</td><td align='left'>%s</td>
<td align='right'>%s</td><td align='right'>%s</td><td align='right'>%s</td><td align='right'>%s</td><td align='right'>%s</td><td align='right'>%s</td><td align='right'>%s</td><td align='right'>%s</td><td align='right'>%s</td><td align='right'>%s</td></tr>",$row[mranges],$row[mitems],number_format(($row['tgvl'] + $row['acvll']-$row['rdqtt']),0,'.',','),number_format($row[acvl],0,'.',','),number_format($row[rdqt],0,'.',','),number_format(($row['tgvl'] + $row['acvll']-$row['rdqtt']+$row['acvl']-$row['rdqt']),0,'.',','),number_format(($row['tgwt'] + $row['acwtt']-$row['rdvll']),2,'.',','),number_format($row[acwt],2,'.',','),number_format($row[rdvl],2,'.',','),number_format(($row['tgwt'] + $row['acwtt']-$row['rdvll']+$row['acwt']-$row['rdvl']),2,'.',','),number_format($row[mrvl],2,'.',','),number_format($row[frrd],2,'.',','));

}
printf("<tr bgcolor='white' onmouseover=\"this.style.backgroundColor='silver';\" onmouseout=\"this.style.backgroundColor='white';\"><td></td><td width=150><b>Total</td><td></td><td></td><td></td><td></td><td align='right'><b>%s</td><td align='center'><b>%s</td><td align='center'><b>%s</td><td align='center'><b>%s</td><td align='center'><b>%s</td><td align='center'><b>%s</td>",number_format($opnvl,2,'.',','),number_format($privl,2,'.',','),number_format($rdvlt,2,'.',','),number_format($clvl,2,'.',','),number_format($mrvl,2,'.',','),number_format($frprvl,2,'.',','));


mysqli_close($con);
?>




