<?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>Items|Trend</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();
pvl=dt.rows[cnt].cells[2].innerText.toLowerCase();

if(sv.length>1 && (tvl.indexOf(sv)>=0 || rvl.indexOf(sv)>=0 || pvl.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;'>

<div style='width=30%;height:100%%;'>

<?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_diana");

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


$conn=mysqli_connect("localhost","primarysales","S5L7lF01EQZvQrGK","primarysales_diana");

stps(1);#printf("<div class='mds'></div>");

printf("<div class='sds'>");
newdash('pr','rt','pvts','sales','Item sales trend',$zones,$regions,$agents,$agenttypes,$terrys,$ranges,$subranges,$items);

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

printf("</div>");


printf("<div class='mnf'>");

if($actp=='fpt'){
$months=date('m');
$years=date('Y');
$mdt=$months-3;
	
if($mdt>=0){
$mdt++;	
$starts=$years."-".$mdt."-01";
$ends=date('Y-m-t');}
else{
$years--;
$months=12+$mdt+1;	
$starts=$years."-".$months."-".cal_days_in_month(CAL_GREGORIAN,$months,$years);}
}

printf("<div style='font-size:16;color:#006699;border-bottom:1px solid silver;width:30%%;'>Products - Item sales trend</div>");

printf("<BR><BR>");
$sprt="&zones=".$zones."&regions=".$regions."&subregions=".$subregions."&agents=".$agents."&agenttypes=".$agenttypes."&terrys=".$terrys."&ranges=".$ranges."&items=".$items."&subranges=".$subranges;

printf("<div style='width:95%%;height:5%%;border:0px solid silver;border-radius:2px;display:table;vertical-align:middle;'>");

printf("<form method='post' action='rpmitemwisetrendgt.php?actp=rpt%s'><table style='width:100%%;font-size:13;'>",$sprt);
printf("<tr><td style='color:grey;'>Select the date range</td><td width=5%%><input type='text' id='inputField' value='%s' name='starts' size=8 style='font-size:13;background:#CACACA;border:1px solid #006699;border-radius:1px;font-family:calibri;color:#006699;'></td><td width=1%%>-</td><td width=5%%><input type='text' id='inputField2' value='%s' name='ends' size=8 style='font-size:13;background:#CACACA;border:1px solid #006699;border-radius:1px;font-family:calibri;color:#006699;'></td><td>
<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 width=45%% align='right' style='color:grey;'>Search</td><td width=20%%><input type='text' id='srctxt' size=28 style='font-family:calibri;font-size:14;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>");
printf("</table>");

$arr=array();

//db===sales{in_sec
//need to change rs_sales = = = rc_invoices
//$db=mysqli_query($conect)_fetch_row($consolidated);
printf("<table id='dt' style='width:100%%;color:#006699;font-size:13;border-collapse:collapse;'>");
printf("<tr><td style='background:silver;border:1px solid white;'>Range</td><td style='background:silver;border:1px solid white;'>Sub range</td>
<td style='background:silver;border:1px solid white;'>Item</td>");

$sqlst="select distinct YEAR(dates),MONTH(dates) from rc_invoices ".$flt." and dates>='".$starts."' and dates<='".$ends."' order by YEAR(dates) ASC,MONTH(dates) ASC";
$aqry=mysqli_query($conn,$sqlst);

$rsqlst="from (select distinct item_ranges as mranges,sub_ranges as msubranges,item_names as mitems from rc_invoices ".$flt.
" and dates>='".$starts."' and dates<='".$ends."' and chanell='GENERAL TRADE' order by item_ranges,sub_ranges,item_names) as tms ";

$sqlst="select mranges,msubranges,mitems";

while($arst=mysqli_fetch_row($aqry)){
printf("<td align='right' style='background:silver;border:1px solid white;'>%s</td>",date_format(date_create($arst[0]."-".$arst[1]),'Y-M'));
$sqlst=$sqlst.",COALESCE(t".$arst[0].$arst[1].".vl,0) ";	
$rsqlst=$rsqlst." left join (select item_ranges,sub_ranges,item_names,sum(box_qty*box_price) as vl from rc_invoices ".$flt." AND chanell='GENERAL TRADE' and dates>='".
date($arst[0]."-".$arst[1]."-01")."' and dates<='".date($arst[0]."-".$arst[1]."-".cal_days_in_month(CAL_GREGORIAN,$arst[1],$arst[0])).
"' group by item_ranges,sub_ranges,item_names)as t".$arst[0].$arst[1]." on t".$arst[0].$arst[1].
".item_ranges=mranges and t".$arst[0].$arst[1].".sub_ranges=msubranges and t".$arst[0].$arst[1].".item_names=mitems ";
}


printf("<td align='right' style='background:silver;border:1px solid white;'>Total</td><td></td><td></td></tr>");
$sqlst=$sqlst.$rsqlst;
#echo $sqlst;	

$qry=mysqli_query($conn,$sqlst);
$rn=mysqli_num_fields($qry);

$tv=0;
$tf=0;

while($rst=mysqli_fetch_row($qry)){
printf("<tr style='border-bottom:1px solid silver;'><td>%s</td><td>%s</td><td>%s</td>",$rst[0],$rst[1],$rst[2]);
for($cnt=3;$cnt<$rn;$cnt++){
$arr[$cnt-3]=$arr[$cnt-3]+$rst[$cnt];	
$tf=$tf+$rst[$cnt];
$tv=$tv+$rst[$cnt];	
printf("<td align='right'>%s</td>",number_format($rst[$cnt],0,'.',','));}
printf("<td align='right'>%s</td></tr>",number_format($tv,0,'.',','));	
$tv=0;}

printf("<tr style='font-weight:bold;'><td>Total</td><td></td><td></td>");

for($cnt=0;$cnt<$rn-3;$cnt++){
printf("<td align='right'>%s</td>",number_format($arr[$cnt],0,'.',','));}
printf("<td align='right'>%s</td></tr>",number_format($tf,0,'.',','));	

printf("</div>");

}

?>

</div>
</body>
</html>