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

$flt=racs(1);
$conn=mysqli_connect("localhost","salehljr","!2n+l)a3iX~U","salehljr_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['chanell'])){
$chanell=$_REQUEST['chanell'];}
else{
$chanell='all';}

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($_REQUEST['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($chanell)!='all'){
$flt=$flt." and chanell='".$chanell."' ";}
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."' ";}
?>

<!doctype html>
<script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
<div class="content-wrapper">
<!-- Content Header (Page header) -->
<section class="content-header">
<ol class="breadcrumb">
<li><a href="maindash.php"><i class="fa fa-dashboard"></i> Home</a></li>
<li><a href="reportpage.php"><i class="fa fa-list"></i> Reports</a></li>
<li class="active">Regions[<?php echo $regions ;?>]</li>
</ol>
</section>
<br>

<!-- Main content -->
<section class="content">
<div class="row">
<div class="col-md-6">  
<div class="box box-success">
<div class="box">    
<div class="box-body no-padding">
    <h4>Month - <?php echo date('M');?></h4>
<div id="donutchart" style="width: 500px; height: 300px;"></div>
</div>
</div>
</div>
</div>
<div class="col-md-6">  
<div class="box box-success">
<div class="box">    
<div class="box-body no-padding">
<div id="donutchart2" style="width: 500px; height: 300px;"></div>
</div>
</div>
</div>
</div>
<div class="col-md-12">
<div class="box box-success">
<div class="box-header">
<!--<h3 class="box-title">Regional Performance [<?php echo $starts;?> - <?php echo $ends;?>]</h3>-->
</div>

<?php
$paymodecash="select sum(box_qty*box_price) from rc_invoices ".$flt." and dates>='".$starts."' and dates<='".$ends."' and paymode='Cash' group by paymode";
$cashquery=mysqli_query($conn,$paymodecash);
while($cashpay=mysqli_fetch_row($cashquery)){
$cashsales=$cashpay[0];
}
$paycredit="select sum(box_qty*box_price) from rc_invoices ".$flt." and dates>='".$starts."' and dates<='".$ends."' and paymode='Credit' group by paymode";
$creditquery=mysqli_query($conn,$paycredit);
while($creditpay=mysqli_fetch_array($creditquery)){
$creditsales=$creditpay[0];    
}


?>

<!-- /.box-header -->    
<form method='post' class="form-inline" action='region_type.php?'>
<div class="form-group">
<td>From:<i class="fa fa-calendar-plus-o"></i><input type='text' class="form-control" id='inputField' name='starts' value='<?php echo $starts ?>' autocomplete='off'></td>
</div>
<div class="form-group">
To:<i class="fa fa-calendar-minus-o"></i><td><input type='text' class="form-control" id='inputField2' name='ends' value='<?php echo $ends ?>' autocomplete='off'></td>
</div>
<div class="btn-group">
<button type="submit" style="margin:5px;" class="btn btn-info">Generate-Report</button>
</form>
<button type="button"  style="margin:5px;" class="btn btn-primary" onclick="scopys(1)">Copy-Excel</button>

<button type="button"  style="margin:5px;" class="btn btn-secondary"><span class="badge bg-yellow"><?php echo number_format($cashsales,0,'.',',');?></span>Cash</button>


<button type="button"  style="margin:5px;" class="btn btn-secondary"><span class="badge bg-red"><?php echo number_format($creditsales,0,'.',',');?></span>Credit</button>
       
</div>
<br>
<div class="box box-primary">    
<div class="box-body table-responsive" style="overflow-x:auto;">
<table id="example" class="table table-bordered table-hover">
<thead>
<th class="not_mapped_style" style="text-align:center">Area</th>
<th class="text-center">Target</th>
<th class="text-center">Order</th>
<th class="text-center">Primary</th>
<th class="text-center">%</th>
<th class="text-center">Deficit</th>
<th class="text-center">RD-[DB+Dir]</th>
<th class="text-center">%</th>
<th class="text-center">DB-RD</th>
<th class="text-center">Direct</th>
<th class="text-center">Calls</th>
<th class="text-center">W-Days</th>
<th colspan="4" class="text-center">Details-View</th>
</thead>
<tbody>

<?php

//$sqlst="select rgs from (select distinct zone_orders as rds,regions as rgs from rc_invoices union select distinct zone_orders,regions from rc_targets order by rds) as sbs";
//$qry=mysqli_query($conn,$sqlst);


//$sqlst="select mzones,tgvl,acvl,odvl,rdvl,odvlc,pcls,rdavgday,wdays,strd,stpri,optstk from (select distinct rgs as mzones from (select distinct zone_orders as rds,regions as rgs from rc_invoices ".$flt." and dates>='".$starts."' and dates<='".$ends."' and chanell='GENERAL TRADE' union select distinct zone_orders,regions from rc_targets ".$flt."  and dates>='".$starts."' and dates<='".$ends."' union select distinct zone_orders,regions from rc_orders ".$flt."  and dates>='".$starts."' and dates<='".$ends."' and chanell='GENERAL TRADE' order by rds,rgs) as zns) as ags 
//left join (select regions,sum(box_qty*box_price) as tgvl from rc_targets ".$flt." and dates>='".$starts."' and dates<='".$ends."' group by regions) as tgs on tgs.regions=mzones 

//left join (select regions,sum(box_qty*box_price) as acvl from rc_invoices ".$flt." and dates>='".$starts."' and dates<='".$ends."' group by regions) 
//as acs on acs.regions=mzones

//left join (select regions,sum(box_qty*box_price) as odvl from rc_orders ".$flt." and dates>='".$starts."' and dates<='".$ends."' and order_status<>'CANCELLED' group by regions) 
//as acss on acss.regions=mzones 

//left join (select regions,sum(box_qty*box_price) as odvlc from rc_orders ".$flt." and dates>='".$starts."' and dates<='".$ends."' group by regions) 
//as acssod on acssod.regions=mzones 

//left join (select regions,sum(pcs_qty*pcs_price) as rdvl,sum(pcalls) as pcls,sum(pcs_qty*pcs_price)/count(distinct(invoice_number))*count(distinct(agent_names)) as rdavgday,count(distinct(invoice_number)) as wdays from rd_saleinv ".$flt." and dates>='".$starts."' and dates<='".$ends."' group by regions) 
//as rds on rds.regions=mzones

//left join (select regions,sum(pcs_qty*pcs_price+box_qty*box_price) as strd from rd_saleinv ".$flt." and dates>='2020-09-01' and dates<='".$ends."' group by regions) 
//as strdd on strdd.regions=mzones

//left join (select regions,sum(box_qty*box_price) as stpri from rc_invoices ".$flt." and dates>='2020-09-01' and dates<='".$ends."' and chanell='GENERAL TRADE' and agent_types='AGENCY' group by regions) 
//as stprd on stprd.regions=mzones

//left join (select regions,sum(pcs_qty*pcs_price+box_qty*box_price) as optstk from rd_db_stocks ".$flt." and dates>='2020-09-01' and dates<='".$ends."' and agent_types='AGENCY' group by regions) 
//as optks on optks.regions=mzones order by acvl/tgvl*100 desc";


$sqlst="select mzones,tgvl,acvl,odvl,rdvl,odvlc,pcls,wdays,direct from (select distinct rgs as mzones from (select distinct zone_orders as rds,regions as rgs,zones as znn from rc_invoices ".$flt." and dates>='".$starts."' and dates<='".$ends."' and chanell='GENERAL TRADE' union select distinct zone_orders,regions,zones from rc_targets ".$flt."  and dates>='".$starts."' and dates<='".$ends."' union select distinct zone_orders,regions,zones from rc_orders ".$flt."  and dates>='".$starts."' and dates<='".$ends."' and chanell='GENERAL TRADE' order by znn) as zns) as ags 

left join (select regions,sum(box_qty*box_price) as tgvl from rc_targets ".$flt." and dates>='".$starts."' and dates<='".$ends."' group by regions) as tgs on tgs.regions=mzones 

left join (select regions,sum(box_qty*box_price) as acvl from rc_invoices ".$flt." and dates>='".$starts."' and dates<='".$ends."' and chanell='GENERAL TRADE' group by regions) 
as acs on acs.regions=mzones

left join (select regions,sum(box_qty*box_price) as odvl from rc_orders ".$flt." and dates>='".$starts."' and dates<='".$ends."' and order_status<>'CANCELLED' and chanell='GENERAL TRADE' group by regions) 
as acss on acss.regions=mzones 

left join (select regions,sum(box_qty*box_price) as odvlc from rc_orders ".$flt." and dates>='".$starts."' and dates<='".$ends."' and chanell='GENERAL TRADE' group by regions) 
as acssod on acssod.regions=mzones 

left join (select regions,sum(box_qty*box_price) as direct from rc_invoices ".$flt." and dates>='".$starts."' and dates<='".$ends."' and agent_types='DIRECT' and chanell='GENERAL TRADE' group by regions) 
as acdir on acdir.regions=mzones

left join (select regions,sum(pcs_qty*pcs_price) as rdvl,sum(pcalls) as pcls,count(distinct(invoice_number)) as wdays from rd_saleinv ".$flt." and dates>='".$starts."' and dates<='".$ends."' group by regions) 
as rds on rds.regions=mzones";

//left join (select regions,sum(pcs_qty*pcs_price+box_qty*box_price) as strd from rd_saleinv ".$flt." and dates>='2020-09-01' and dates<='".$ends."' group by regions) 
//as strdd on strdd.regions=mzones

//left join (select regions,sum(box_qty*box_price) as stpri from rc_invoices ".$flt." and dates>='2020-09-01' and dates<='".$ends."' and chanell='GENERAL TRADE' and agent_types='AGENCY' group by regions) 
//as stprd on stprd.regions=mzones

//left join (select regions,sum(pcs_qty*pcs_price+box_qty*box_price) as optstk from rd_db_stocks ".$flt." and dates>='2020-09-01' and dates<='".$ends."' and agent_types='AGENCY' group by regions) 
//as optks on optks.regions=mzones order by acvl/tgvl*100 desc";
 
 
$tgv=0;
$acv=0;
$tgw=0;
$acw=0;
$odv=0;
$rdv=0;
$pcl=0;
$odvlc=0;
$rdavgday=0;
$wdays=0;
$sttot=0;
 
$qry=mysqli_query($conn,$sqlst);
while($rst=mysqli_fetch_row($qry)){
$tgv=$tgv+$rst[1];
$odv=$odv+$rst[3];
$acv=$acv+$rst[2];
$rdv=$rdv+$rst[4]+$rst[8];
$pcl=$pcl+$rst[6];
$odvlc=$odvlc+$rst[5];
$dbrdtot=$dbrdtot+$rst[4];
$dirtot=$dirtot+$rst[8];
//$rdavgday=$rdavgday+$rst[7];
$wdays=$wdays+$rst[7];
//$sttot=$sttot+$rst[10]+$rst[11]-$rst[9];

if($rst[1]==0){
$pcs=0;}
else{
$pcs=$rst[2]*100/$rst[1];}

if($rst[1]==0){
$rdss=0;}
else{
$rdss=($rst[4]+$rst[9])*100/$rst[1];}
?> 
<tr>
<td><?php echo ucwords(strtolower($rst[0])); ?></td>
<td align='center'><?php echo number_format($rst[1],0,'.',','); ?></td>
<td align='center'><?php echo number_format($rst[3],0,'.',','); ?></td>
<td align='center'><?php echo number_format($rst[2],0,'.',','); ?></td>
<td align='center' class="info text-danger"><?php echo number_format($pcs,0,'.',','); ?></td>
<td align='center'><?php echo number_format($rst[1]-$rst[2],0,'.',','); ?></td>
<td align='center'><?php echo number_format($rst[4]+$rst[8],0,'.',','); ?></td>
<td align='center' class="info text-danger"><?php echo number_format($rdss,0,'.',','); ?></td>
<td align='center'><?php echo number_format($rst[4],0,'.',','); ?></td>
<td align='center'><?php echo number_format($rst[8],0,'.',','); ?></td>
<td align='center'><?php echo number_format($rst[6],0,'.',','); ?></td>
<td align='center' class="p-3 mb-2 bg-warning text-white"><?php echo number_format($rst[7],0,'.',','); ?></td>

<td align='center'>  
<a href='dbsalesvalue.php?regions=<?php echo $rst[0];?>&starts=<?php echo $starts;?>&ends=<?php echo $ends;?>' input type="submit" name="view" class="btn btn-secondary btn-xs"><i class="fa fa-list"></i></i>Agent</a>
</td>

<td align='center'>  
<a href='rangesales.php?regions=<?php echo $rst[0];?>&starts=<?php echo $starts;?>&ends=<?php echo $ends;?>' input type="submit" name="view" class="btn btn-secondary btn-xs"><i class="fa fa-list"></i></i>Range</a>
</td>

<td align='center'>  
<a href='productsku.php?regions=<?php echo $rst[0];?>&starts=<?php echo $starts;?>&ends=<?php echo $ends;?>' input type="submit" name="view" class="btn btn-secondary btn-xs"><i class="fa fa-list"></i></i>Items</a>
</td>

<td align='center'>  
<a href='/prd/sku_saletrendprimarynew.php?regions=<?php echo $rst[0];?>&starts=<?php echo $starts;?>&ends=<?php echo $ends;?>' input type="submit" name="view" class="btn btn-secondary btn-xs"><i class="fa fa-list"></i></i>SKU-Trend</a>
</td>

</tr>
<?php } ?>
<td class="success">Total</td><td class="success" align='center'><?php echo number_format($tgv,0);?><td class="success" align='center'><?php echo number_format($odv,0);?><td class="success" align='center'><?php echo number_format($acv,0);?><td class="success" align='center'><?php echo number_format($acv/$tgv*100,2);?></td><td class="success" align='center'><?php echo number_format($tgv-$acv,0);?></td><td class="success" align='center'><?php echo number_format($rdv,0);?></td><td class="success" align='center'><?php echo number_format($rdv/$tgv*100,2);?></td>

<td class="success" align='center'><?php echo number_format($dbrdtot,0);?></td>
<td class="success" align='center'><?php echo number_format($dirtot,0);?></td>
<td class="success" align='center'><?php echo number_format($pcl,0);?></td><td class="success" align='center'><?php echo number_format($wdays,0);?></td><td align='center'>  
<a href='dbsalesvalue.php?starts=<?php echo $starts;?>&ends=<?php echo $ends;?>' input type="submit" name="view" class="btn btn-info btn-xs"><i class="fa fa-list"></i></i>Agent</a>
</td>

<td align='center'>  
<a href='invitems.php?invnum=<?php echo $rst[1];?>' input type="submit" name="view" class="btn btn-warning btn-xs"><i class="fa fa-list"></i></i>Range</a>
</td>

<td align='center'>  
<a href='invitems.php?invnum=<?php echo $rst[1];?>' input type="submit" name="view" class="btn btn-primary btn-xs"><i class="fa fa-list"></i></i>Items</a>
</td>
<td align='center'>  
<a href='#?invnum=<?php echo $rst[0];?>' input type="submit" name="view" class="btn btn-primary btn-xs"><i class="fa fa-list"></i></i>Items</a>
</td>
</tbody>
</table>	
</div>
</div>
</div>
</section>
</div>
<?php include_once('footer.php');?>

<script>
  $(function () {
    $('#example1').DataTable()
    $('#example2').DataTable({
      'paging'      : true,
      'lengthChange': false,
      'searching'   : false,
      'ordering'    : true,
      'info'        : true,
      'autoWidth'   : false,
    })
  })
  
function scopys(pr)
{
var dts=document.getElementById("example");

var sels=window.getSelection();
sels.removeAllRanges();
var ranges=document.createRange();
ranges.selectNodeContents(dts);
sels.addRange(ranges);
document.execCommand("copy");
swal('Coppied!','Open Excel Sheet Press CTRL+V','success');
sels.removeAllRanges();
} 

 //Date picker
    $('#datepicker').datepicker({
        format: 'yyyy/mm/dd',
      autoclose: true
    })
function myFunction() {
  // Declare variables 
  var input, filter, table, tr, td, i, txtValue;
  input = document.getElementById("myInput");
  filter = input.value.toUpperCase();
  table = document.getElementById("example");
  tr = table.getElementsByTagName("tr");

  // Loop through all table rows, and hide those who don't match the search query
  for (i = 0; i < tr.length; i++) {
    td = tr[i].getElementsByTagName("td")[2];
    if (td) {
      txtValue = td.textContent || td.innerText;
      if (txtValue.toUpperCase().indexOf(filter) > -1) {
        tr[i].style.display = "";
      } else {
        tr[i].style.display = "none";
      }
    } 
  }
}

google.charts.load("current", {packages:["corechart"]});
google.charts.setOnLoadCallback(drawChartt);
function drawChartt() {
var data = google.visualization.arrayToDataTable([
['regions', 'contribution'],
<?php
$sql = "SELECT distinct regions,sum(box_qty*box_price) FROM rc_invoices ".$flt." and chanell='GENERAL TRADE' and dates between '".$starts."' and '".$ends."' group by regions";
$firee = mysqli_query($conn,$sql);
while ($resultt = mysqli_fetch_assoc($firee)) {
echo"['".$resultt['regions']."',".$resultt['sum(box_qty*box_price)']."],";
}
?>
]);
var options = {
title: 'Monthly Contribution',
pieHole: 0.4,
};
var chart = new google.visualization.PieChart(document.getElementById('donutchart'));
chart.draw(data, options);
}

google.charts.load("current", {packages:["corechart"]});
google.charts.setOnLoadCallback(drawChartp);
function drawChartp() {
var data = google.visualization.arrayToDataTable([
['Region', 'Sales Value'],
<?php
$fstarts=date('Y-04-01');
$sql = "SELECT distinct regions,sum(box_qty*box_price) FROM rc_invoices ".$flt." and chanell='GENERAL TRADE' and dates between '".$fstarts."' and '".$ends."' group by regions";
$firee = mysqli_query($conn,$sql);
while ($resultt = mysqli_fetch_assoc($firee)) {
echo"['".$resultt['regions']."',".$resultt['sum(box_qty*box_price)']."],";
}
?>
]);

var options = {
title: 'Financial Year Cont:',
pieHole: 0.4,
};
var chart = new google.visualization.PieChart(document.getElementById('donutchart2'));
chart.draw(data, options);
}

</script>
</body>
</html>

