<?php

session_start();

require_once 'Classes/PHPExcel.php';

$wbk=new PHPExcel();
$wks=$wbk->getActiveSheet();

$wks->setCellValueByColumnAndRow(0,4,"Report summary");

$cnt=4;

$cnt++;
if(isset($_REQUEST['zones'])){
$zones=$_REQUEST['zones'];}
else{
$zones="All";}
$wks->setCellValueByColumnAndRow(0,$cnt,"Zone");
$wks->setCellValueByColumnAndRow(1,$cnt,$zones);


$cnt++;
if(isset($_REQUEST['regions'])){
$regions=$_REQUEST['regions'];}
else{
$regions="All";}
$wks->setCellValueByColumnAndRow(0,$cnt,"Region");
$wks->setCellValueByColumnAndRow(1,$cnt,$regions);


$cnt++;
if(isset($_REQUEST['territorys'])){
$territorys=$_REQUEST['territorys'];}
else{
$territorys="All";}
$wks->setCellValueByColumnAndRow(0,$cnt,"Territory");
$wks->setCellValueByColumnAndRow(1,$cnt,$territorys);


$cnt++;
if(isset($_REQUEST['agents'])){
$agents=$_REQUEST['agents'];}
else{
$agents="All";}
$wks->setCellValueByColumnAndRow(0,$cnt,"Agent");
$wks->setCellValueByColumnAndRow(1,$cnt,$agents);


$cnt++;
if(isset($_REQUEST['rsms'])){
$rsms=$_REQUEST['rsms'];}
else{
$rsms="All";}
$wks->setCellValueByColumnAndRow(0,$cnt,"RSM");
$wks->setCellValueByColumnAndRow(1,$cnt,$rsms);


$cnt++;
if(isset($_REQUEST['executives'])){
$executives=$_REQUEST['executives'];}
else{
$executives="All";}
$wks->setCellValueByColumnAndRow(0,$cnt,"Executive");
$wks->setCellValueByColumnAndRow(1,$cnt,$executives);


$cnt++;
if(isset($_REQUEST['reps'])){
$reps=$_REQUEST['reps'];}
else{
$reps="All";}
$wks->setCellValueByColumnAndRow(0,11,"Rep");
$wks->setCellValueByColumnAndRow(1,11,$reps);


$cnt++;
if(isset($_REQUEST['ranges'])){
$ranges=$_REQUEST['ranges'];}
else{
$ranges="All";}
$wks->setCellValueByColumnAndRow(0,12,"Range");
$wks->setCellValueByColumnAndRow(1,12,$ranges);


$cnt++;
if(isset($_REQUEST['subranges'])){
$subranges=$_REQUEST['subranges'];}
else{
$subranges="All";}
$wks->setCellValueByColumnAndRow(0,13,"Sub range");
$wks->setCellValueByColumnAndRow(1,13,$subranges);


$cnt++;
if(isset($_REQUEST['items'])){
$items=$_REQUEST['items'];}
else{
$items="All";}
$wks->setCellValueByColumnAndRow(0,14,"Item");
$wks->setCellValueByColumnAndRow(1,14,$items);

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

$starts=$_REQUEST['starts'];
$ends=$_REQUEST['ends'];

$wks->setCellValueByColumnAndRow(0,15,"From");
$wks->setCellValueByColumnAndRow(1,15,$starts);

$wks->setCellValueByColumnAndRow(0,16,"To");
$wks->setCellValueByColumnAndRow(1,16,$ends);

$wks->getStyle($wks->getCellByColumnAndRow(0,3)->getCoordinate().':'.$wks->getCellByColumnAndRow(1,18)->getCoordinate())->getFont()->setSize(10);


$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[1])!='all'){
$seflt=$seflt." and zones='".$serst[1]."' ";}
if(strtolower($serst[2])!='all'){
$seflt=$seflt." and regions='".$serst[2]."' ";}
if(strtolower($serst[3])!='all'){
$seflt=$seflt." and territory_names='".$serst[3]."' ";}
if(strtolower($serst[4])!='all'){
$seflt=$seflt." and agent_names='".$serst[4]."' ";}
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]."' ";}
}


$flt=$seflt." and dates>='".$_REQUEST['starts']."' and dates<='".$_REQUEST['ends']."' ";
if(strtolower($regions)!='all'){
$flt=$flt." and regions='".$regions."' ";}
if(strtolower($zones)!='all'){
$flt=$flt." and zones='".$zones."' ";}
if(strtolower($agents)!='all'){
$flt=$flt." and agent_names='".$agents."' ";}
if(strtolower($territorys)!='all'){
$flt=$flt." and territory_names='".$territorys."' ";}
if(strtolower($ranges)!='all'){
$flt=$flt." and item_ranges='".$ranges."' ";}
if(strtolower($subranges)!='all'){
$flt=$flt." and sub_ranges='".$subranges."' ";}
if(strtolower($agenttypes)!='all'){
$flt=$flt." and agent_types='".$agenttypes."' ";}



$asqlst=" left join (select regions as tgagent_names,sum((pcs_qty*pcs_price)+(box_qty*box_price)) as tgvls,sum(margin_rates*((pcs_qty*pcs_price)+(box_qty*box_price))) as tgmargins
 from rc_targets ".$flt." group by regions) as targets on tgagent_names=magent_names ";

$asqlst=$asqlst." left join (select regions as acagent_names,sum((pcs_qty*pcs_price)+(box_qty*box_price)) as acvls,sum(((pcs_qty*pcs_price)+(box_qty*box_price))*margin_rates) as acmargins 
from rc_invoices ".$flt." group by regions) as acts on acagent_names=magent_names ";


$rsqlst="select magent_names as region ";

$rsqlst=$rsqlst.",tgvls,acvls,tgmargins,acmargins ";

$rsqlst=$rsqlst." from (select distinct region as magent_names from agentsmrd where agent_name is NOT NULL ";
if(strtolower($regions)!='all'){
$rsqlst=$rsqlst." and region='".$regions."'";}
if(strtolower($zones)!='all'){
$rsqlst=$rsqlst." and zone='".$zones."'";}
if(strtolower($agents)!='all'){
$rsqlst=$rsqlst." and agent_name='".$agents."'";}

$rsqlst=$rsqlst.") as rgs ";


$sqlst=$rsqlst.$asqlst." ";

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

$tgqt=0;
$acqt=0;
$tgvt=0;
$acvt=0;
$tgmg=0;
$acmg=0;

$wks->setCellValueByColumnAndRow(4,4,"Target region summary");

$wks->setCellValueByColumnAndRow(4,5,"Region");
$wks->setCellValueByColumnAndRow(5,5,"Target sale");
$wks->setCellValueByColumnAndRow(6,5,"Actual sale");
$wks->setCellValueByColumnAndRow(7,5,"Achievent %%");
$wks->setCellValueByColumnAndRow(8,5,"Sales variance");
$wks->setCellValueByColumnAndRow(9,5,"Target margin");
$wks->setCellValueByColumnAndRow(10,5,"Margin %");
$wks->setCellValueByColumnAndRow(11,5,"Actual margin");
$wks->setCellValueByColumnAndRow(12,5,"Margin %");
$wks->setCellValueByColumnAndRow(13,5,"Margin variance");

$cnt=6;
$rows=0;

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

$tgqt=$tgqt+$rst[1];
$acqt=$acqt+$rst[2];
$tgvt=$tgvt+$rst[3];
$acvt=$acvt+$rst[4];

if($rst[1]!=0 && $rst[3]!=0){
	
$tgmg=$rst[3]*100/$rst[1];
$tgq=$rst[1];
$tgv=$rst[3];}
else{
$tgmg=0;
$tgq=$rst[2];
$tgv=$rst[4];}

if($tgv!=0 && $tgq!=0 && $rst[2]!=0 && $rst[4]!=0){
$pcn=$rst[2]/$tgq*100;}
else{
$pcn=0;}

if($rst[2]!=0 && $rst[4]!=0){
$acmg=$rst[4]*100/$rst[2];}
else{
$acmg=0;}

$wks->setCellValueByColumnAndRow(4,$cnt,$rst[0]);
$wks->setCellValueByColumnAndRow(5,$cnt,round($rst[1]));
$wks->setCellValueByColumnAndRow(6,$cnt,round($rst[2]));
$wks->setCellValueByColumnAndRow(7,$cnt,round($pcn));
$wks->setCellValueByColumnAndRow(8,$cnt,round(($rst[2]-$rst[1])));
$wks->setCellValueByColumnAndRow(9,$cnt,round($rst[3]));
$wks->setCellValueByColumnAndRow(10,$cnt,round($tgmg)."%");
$wks->setCellValueByColumnAndRow(11,$cnt,round($rst[4]));
$wks->setCellValueByColumnAndRow(12,$cnt,round($acmg)."%");
$wks->setCellValueByColumnAndRow(13,$cnt,round(($rst[4]-$rst[3])));

$cnt++;
}



if($tgqt!=0 && $tgvt!=0){
$pcn=($acqt-$tgqt)*100/$tgqt;
$tgmg=$tgvt*100/$tgqt;}
else{
$pcn=0;
$tgmg=0;
}

if($acqt!=0 && $acvt!=0){
$acmg=$acvt*100/$acqt;}
else{
$acmg=0;}

$wks->setCellValueByColumnAndRow(4,$cnt,"Total");
$wks->setCellValueByColumnAndRow(5,$cnt,round($tgqt));
$wks->setCellValueByColumnAndRow(6,$cnt,round($acqt));
$wks->setCellValueByColumnAndRow(7,$cnt,round($pcn));
$wks->setCellValueByColumnAndRow(8,$cnt,round($acqt-$tgqt));
$wks->setCellValueByColumnAndRow(9,$cnt,round($tgvt));
$wks->setCellValueByColumnAndRow(10,$cnt,round($tgmg)."%");
$wks->setCellValueByColumnAndRow(11,$cnt,round($acvt));
$wks->setCellValueByColumnAndRow(12,$cnt,round($acmg)."%");
$wks->setCellValueByColumnAndRow(13,$cnt,round($acvt-$tgvt));

$wks->getStyle($wks->getCellByColumnAndRow(4,5)->getCoordinate().':'.$wks->getCellByColumnAndRow(13,$cnt)->getCoordinate())->getFont()->setSize(10);

$wks->getStyle($wks->getCellByColumnAndRow(4,5)->getCoordinate().':'.$wks->getCellByColumnAndRow(13,$cnt)->getCoordinate())->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$wks->getStyle($wks->getCellByColumnAndRow(4,6)->getCoordinate().':'.$wks->getCellByColumnAndRow(13,$cnt)->getCoordinate())->getFill()->getStartColor()->setARGB('F2F2F2');
$wks->getStyle($wks->getCellByColumnAndRow(4,5)->getCoordinate().':'.$wks->getCellByColumnAndRow(13,5)->getCoordinate())->getFill()->getStartColor()->setARGB('BDBDBD');

$wks->getStyle($wks->getCellByColumnAndRow(4,5)->getCoordinate().':'.$wks->getCellByColumnAndRow(13,$cnt)->getCoordinate())->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_border::BORDER_THIN);
$wks->getStyle($wks->getCellByColumnAndRow(4,5)->getCoordinate().':'.$wks->getCellByColumnAndRow(13,$cnt)->getCoordinate())->getBorders()->getAllBorders()->getColor()->setARGB('FFFFFF');
$wks->getStyle($wks->getCellByColumnAndRow(4,5)->getCoordinate().':'.$wks->getCellByColumnAndRow(13,$cnt)->getCoordinate())->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);

$wks->getStyle($wks->getCellByColumnAndRow(4,5)->getCoordinate().':'.$wks->getCellByColumnAndRow(13,$cnt)->getCoordinate())->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_border::BORDER_THIN);

$wks->getStyle($wks->getCellByColumnAndRow(4,5)->getCoordinate().':'.$wks->getCellByColumnAndRow(13,5)->getCoordinate())->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

$wks->getStyle($wks->getCellByColumnAndRow(10,5)->getCoordinate().':'.$wks->getCellByColumnAndRow(10,$cnt)->getCoordinate())->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

$wks->getStyle($wks->getCellByColumnAndRow(12,5)->getCoordinate().':'.$wks->getCellByColumnAndRow(12,$cnt)->getCoordinate())->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

$wks->getStyle($wks->getCellByColumnAndRow(4,5)->getCoordinate().':'.$wks->getCellByColumnAndRow(13,$cnt)->getCoordinate())->getBorders()->getAllBorders()->getColor()->setARGB('FFFFFF');

$wks->getStyle("A4")->getFont()->getColor()->setARGB('0174DF');
$wks->getStyle("A4")->getFont()->setBold(true);

$wks->getStyle("E4")->getFont()->getColor()->setARGB('0174DF');
$wks->getStyle("E4")->getFont()->setBold(true);


$wks->getColumnDimension('E')->setAutoSize(true);
$wks->getColumnDimension('F')->setAutoSize(true);
$wks->getColumnDimension('G')->setAutoSize(true);
$wks->getColumnDimension('H')->setAutoSize(true);
$wks->getColumnDimension('I')->setAutoSize(true);
$wks->getColumnDimension('J')->setAutoSize(true);
$wks->getColumnDimension('K')->setAutoSize(true);
$wks->getColumnDimension('L')->setAutoSize(true);
$wks->getColumnDimension('M')->setAutoSize(true);
$wks->getColumnDimension('N')->setAutoSize(true);

$wks->removeRow(1);
$wks->removeRow(1);


$nms="prtgitemsummary".substr($_SESSION['usernames'],0,3).rand().".xlsx";

$esv=new PHPExcel_Writer_Excel2007($wbk);
$esv->save($nms);
header("location:".$nms);

?>



