<?php
date_default_timezone_set("Asia/Colombo");
require "showshp.php";
session_start();
if(!isset($_SESSION['titles']) || !isset($_SESSION['positions'])|| !isset($_SESSION['usernames'])){
header("location:index.php");
}


if(isset($_REQUEST['actp'])){

$states=0;
$dates=$_REQUEST['rddates'];
$agents=$_REQUEST['rdagents'];
$rws=$_REQUEST['rws'];
$dcsvalues=$_REQUEST['rtdcvalues']+$_REQUEST['wsdcvalues'];
$rtdsvalues=$_REQUEST['rtdcvalues'];
$wsdsvalues=$_REQUEST['wsdcvalues'];
$mrks=$_REQUEST['rdmrks'];
$scalls=$_REQUEST['rdscalls'];
$mcalls=$_REQUEST['rdmcalls'];
$pcalls=$_REQUEST['rdpcalls'];
$routes=$_REQUEST['routes'];
$wd=$wd+($rws);
$avdistot=$dcsvalues/$wd;
$rtdis=$rtdsvalues/$wd;
$wsdis=$wsdsvalues/$wd;
$pcl=$pcalls/$wd;
$mcl=$mcalls/$wd;
$scl=$scalls/$wd;
$distot=$rtdis+$wsdis;
$userdates=date("Y-m-d");


$conn=mysqli_connect("localhost","primarysales","S5L7lF01EQZvQrGK","primarysales_dianaold");
$sqlst="select max(invoice_number) from dbopenstks ";
$qry=mysqli_query($conn,$sqlst);
$rst=mysqli_fetch_row($qry);

if(mysqli_num_rows($qry)==0){
$invoicenumbers=date('Y')."0001";}
else{
if(intval($rst[0],10)==0){
$invoicenumbers=date('y')."0001";}
else{
$invoicenumbers=intval($rst[0],10)+1;}
}

$sqlst="select distinct zone_order,zone,region,rsm_name,executive_name,agent_type,territory_name,range_type,rep_name,chanelname,sub_region,agent_name from agentsmrd where agent_replc='".$agents."'";
#echo $sqlst;

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

$zoneorders=$rst[0];
$zones=$rst[1];
$regions=$rst[2];
$rsmnames=$rst[3];
$executivenames=$rst[4];
$agenttypes=$rst[5];
$terrys=$rst[6];
$rangetypes=$rst[7];
$reps=$rst[8];
$chl=$rst[9];
$subrgn=$rst[10];
$agtname=$rst[11];


for($cnt=0;$cnt<$rws;$cnt++){
$items=$_REQUEST['items'.$cnt];
$prices=$_REQUEST['prices'.$cnt];
$qtys=$_REQUEST['qtys'.$cnt];
$frees=$_REQUEST['frees'.$cnt];
$vll=$_REQUEST['prices'.$cnt]*$_REQUEST['qtys'.$cnt];
$rtrate=$rtdsvalues/$vll;
$wsrate=$wsdsvalues/$vll;
$disrate=$dcsvalues/$vll;

$sqlst="select distinct item_ranges,sub_ranges,rd_uom,packet_weight from itemsmrd where item_name='".$items."'";
$qry=mysqli_query($conn,$sqlst);
$rst=mysqli_fetch_row($qry);

$ranges=$rst[0];
$subranges=$rst[1];
$packetsperbox=$rst[2];
$packetweights=$rst[3];


$sqlst="insert into dbopenstks  (dates,invoice_number,invoice_types,item_names,item_ranges,sub_ranges,packets_per_box,packet_weights,zone_orders,zones,regions,rsm_names,executive_names,agent_names,agent_types,
territory_names, rep_names,range_types,pcs_price,pcs_qty,free_qtys,discount_values,rtdiscount_values,wsdiscount_values,market_return_values,scalls,mcalls,pcalls,routes,grand_values,rtdiscount_rates,wsdiscount_rates,discount_rates,base_values,chanell,sub_regions,user_names,user_dates) values ('".$dates."','".$invoicenumbers."','Open Stock','".$items."','".$ranges."','".$subranges."',".$packetsperbox.",".$packetweights.
",".$zoneorders.",'".$zones."','".$regions."','".$rsmnames."','".$executivenames."','".$agtname."','".$agenttypes."','".$terrys."','".$reps."','".$rangetypes."',".$prices.",".$qtys.
",".$frees.",".$distot.",".$rtdis.",".$wsdis.",".$mrks.",".$scl.",".$mcl.",".$pcl.",'".$routes."','".$vll."','".$rtrate."','".$wsrate."','".$disrate."','".$avdistot."','".$chl."','".$subrgn."','".$_SESSION['usernames']."','".$userdates."')";

#echo $sqlst;

if(mysqli_query($conn,$sqlst)==true){
$states++;}

}

#echo $sqlst;

if($states>0){
$tstarts=date('Y-m-01');
$tends=date('Y-m-t');

$sqlst="select distinct rgs,tvls,avls from (select distinct item_ranges as rgs from itemsmrd) as sgs left join (select item_ranges as trgs,
sum(box_qty*box_price) as tvls from rc_targets where agent_names='".$agents."' and dates>='".$tstarts."' and dates<='".$tends."' group by item_ranges) 
as tgs on rgs=trgs left join (select item_ranges as args,sum(pcs_qty*pcs_price) as avls from dbopenstks where agent_names='".$agents.
"' and dates>='".$tstarts."' and dates<='".$tends."' group by item_ranges) as ags on rgs=args ";

#echo $sqlst;

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

while($rst=mysqli_fetch_row($qry)){
$ttv=$ttv+$rst[1];
$tav=$tav+$rst[2];
}

if($ttv==0){
$pcn=100;}
else{
$pcn=$tav*100/$ttv;
}	

$str="<BR><BR><div style='font-size:18;color:#0066CC;font-weight:bold;'>".number_format($pcn,2,'.',',')."% Achieved</div>";
$str=$str."<BR><table style='width:20%;font-size:13;'><tr><td colspan='2'><b>Summary - ".date('Y-m-d')."</td></tr><tr><td>Target sales</td>
<td align='right'>".number_format($ttv,0,'.',',')."</td></tr><tr><td>Actual sales</td><td align='right'>".number_format($tav,0,'.',',')."</td></tr><tr>
<td>Achievemnt %%</td><td align='right'>".number_format($pcn,2,'.',',')."%</td></tr><tr><td>Variance</td><td align='right'>"
.number_format($ttv=$tav,0,'.',',')."</td></tr>";

$sqlst="select sum(scalls),sum(mcalls),sum(pcalls) from (select distinct dates,scalls,mcalls,pcalls from dbopenstks where 
agent_names='".$agents."' and dates>='".$tstarts."' and dates<='".$tends."') as acs ";
$qry=mysqli_query($conn,$sqlst);
$rst=mysqli_fetch_row($qry);
$str=$str."<tr><td>Schedule calls</td><td align='right'>".$rst[0]."</td></tr>";
$str=$str."<tr><td>Made calls</td><td align='right'>".$rst[1]."</td></tr>";
$str=$str."<tr><td>Productive calls</td><td align='right'>".$rst[2]."</td></tr>";

$sqlst="select agent_names,rnk,@cn from (select agent_names,@cn:=@cn+1 as rnk from(select agent_names,@cn:=0,
sum(pcs_qty*pcs_price) as vl from dbopenstks where dates>='".$tstarts."' and dates<='".$tends."' group by agent_names 
order by vl DESC) as sss)as ttt where agent_names='".$agents."' ";

#echo $sqlst;

$qry=mysqli_query($conn,$sqlst);
$rst=mysqli_fetch_row($qry);
$str=$str."<tr><td>Rank</td><td align='right'>".$rst[1]." out of ".$rst[2]."</tr></table>";


$sqlst="select distinct rgs,tvls,avls from (select distinct item_ranges as rgs from itemsmrd) as sgs left join (select item_ranges as trgs,
sum(box_qty*box_price) as tvls from rc_targets where agent_names='".$agents."' and dates>='".$tstarts."' and dates<='".$tends."' group by item_ranges) 
as tgs on rgs=trgs left join (select item_ranges as args,sum(pcs_qty*pcs_price) as avls from dbopenstks where agent_names='".$agents.
"' and dates>='".$tstarts."' and dates<='".$tends."' group by item_ranges) as ags on rgs=args ";

$str=$str."<BR><BR><table style='width:40%;font-size:12;'><tr class='cls'><td colspan='5'>Range wise acheivement - ".date('Y-m-d')."</td></tr>";
$str=$str."<tr class='cls' style='background:silver;text-align:center;'><td>Item ranges</td><td>Target sales</td><td>Actual sales</td>
<td>Achievemnt %%</td><td>Variance</td></tr>";

$ttv=0;
$tav=0;

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

while($rst=mysqli_fetch_row($qry)){

if($rst[1]==0){
$pcn=100;}
else{
$pcn=$rst[2]*100/$rst[1];}	
	
$str=$str."<tr class='cls'><td>".$rst[0]."</td><td align='right'>".number_format($rst[1],0,'.',',')."</td><td align='right'>".number_format($rst[2],0,'.',',').
"</td><td align='center'>".number_format($pcn,2,'.',',')."%</td><td align='right'>".number_format($rst[1]-$rst[2],0,'.',',')."</td></tr>";
$ttv=$ttv+$rst[1];
$tav=$tav+$rst[2];
}

if($ttv==0){
$pcn=100;}
else{
$pcn=$tav*100/$ttv;}	

$str=$str."<tr class='cls' style='font-weight:bold;'><td>".Total."</td><td align='right'>".number_format($ttv,0,'.',',')."</td><td align='right'>".number_format($tav,0,'.',',').
"</td><td align='center'>".number_format($pcn,2,'.',',')."%</td><td align='right'>".number_format($ttv-$tav,0,'.',',')."</td></tr></table>";

$str=$str."<BR><BR></table><table style='width:25%;font-size:12'><tr class='cls'><td colspan='5'>Daily sales - ".date('Y-M')."</td></tr>";
$str=$str."<tr class='cls' style='background:silver;text-align:center;'><td>Date</td><td>Day sale</td><td>Total sale</td></tr>";

$sqlst="select dates,sum(pcs_qty*pcs_price) from dbopenstks where agent_names='".$agents."' and dates>='".$tstarts.
"' and dates<='".$tends."' group by dates";
$qry=mysqli_query($conn,$sqlst);

$tav=0;
while($rst=mysqli_fetch_row($qry)){
$tav=$tav+$rst[1];
	
$str=$str."<tr class='cls'><td>".$rst[0]."</td><td align='right'>".number_format($rst[1],0,'.',',')."</td>
<td align='right'>".number_format($tav,0,'.',',');}

$str=$str."<tr class='cls' style='font-weight:bold;'><td>Total</td><td align='right'>".number_format($tav,0,'.',',')
."</td><td align='right'>".number_format($tav,0,'.',',')."</td></tr></table>";


$sqlst="select distinct rss,rgs,aqtys,tqtys,tvls,avls from (select distinct item_name as rgs,item_ranges as rss from itemsmrd) as sgs left join 
(select item_names as trgs,sum(box_qty) as tqtys,sum(box_qty*box_price) as tvls 
from rc_targets where agent_names='".$agents."' and dates>='".$tstarts."' and dates<='".$tends."' group by item_names) 
as tgs on rgs=trgs left join (select item_names as args,sum(pcs_qty/packets_per_box) as aqtys,sum(pcs_qty*pcs_price) as avls from dbopenstks where agent_names='".$agents.
"' and dates>='".$tstarts."' and dates<='".$tends."' group by item_names) as ags on rgs=args order by  rss,avls DESC";

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

$str=$str."<BR><BR><table style='width:70%;font-size:12'><tr class='cls'><td colspan='5'>Item wise acheivement - ".date('Y-m-d')."</td></tr>";
$str=$str."<tr class='cls' style='background:silver;text-align:center;'><td>Range</td><td>Item</td><td>Target qty</td><td>Actual qty</td><td>Target sales</td>
<td>Actual sales</td><td>Achievemnt %</td><td>Variance</td></tr>";

$ttv=0;
$tav=0;

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

while($rst=mysqli_fetch_row($qry)){
if(($rst[2] +$rst[3])>0){
if($rst[4]==0){
$pcn=100;}
else{
$pcn=$rst[5]*100/$rst[4];}
	
$str=$str."<tr class='cls'><td>".$rst[0]."</td><td>".$rst[1]."</td><td align='right'>".
number_format($rst[2],0,'.',',')."</td><td align='right'>".number_format($rst[3],0,'.',',').
"<td align='right'>".number_format($rst[4],0,'.',',')."</td><td align='right'>".number_format($rst[5],0,'.',',').
"</td><td align='center'>".number_format($pcn,2,'.',',')."%</td><td align='right'>".number_format($rst[4]-$rst[5],0,'.',',')."</td></tr>";}

$ttv=$ttv+$rst[4];
$tav=$tav+$rst[5];
}

if($ttv==0){
$pcn=100;}
else{
$pcn=$tav*100/$ttv;}	

$str=$str."<tr class='cls' style='font-weight:bold;'><td>".Total."</td><td></td><td></td><td></td><td align='right'>".number_format($ttv,0,'.',',')."</td><td align='right'>".number_format($tav,0,'.',',').
"</td><td align='center'>".number_format($pcn,2,'.',',')."%</td><td align='right'>".number_format($ttv-$tav,0,'.',',')."</td></tr></table>";


echo "oks####".$invoicenumbers."####".$dates."####".$str;}
else{
echo "errors<td>";}

}

	


