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


$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;}


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


$conn=mysqli_connect("localhost","primarysales","S5L7lF01EQZvQrGK","primarysales_diana");
$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]."' ";}
if(strtolower($serst[8])!='all'){
$seflt=$seflt." and executive_names='".$serst[8]."' ";}
if(strtolower($serst[9])!='all'){
$seflt=$seflt." and chanell='".$serst[9]."' ";}
}

$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 agent_names 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 agent_names) as targets on tgagent_names=magent_names ";

$asqlst=$asqlst." left join (select agent_names 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 agent_names) as acts on acagent_names=magent_names ";


$rsqlst="select magent_names as region ";

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

$rsqlst=$rsqlst." from (select distinct agent_name as magent_names,territory_name as territorys,region as zdregions 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."  order by zdregions,territorys";

$tmg=0;

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

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

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

$wks->setCellValueByColumnAndRow(4,5,"Agent");
$wks->setCellValueByColumnAndRow(5,5,"Territory");
$wks->setCellValueByColumnAndRow(6,5,"Target sale");
$wks->setCellValueByColumnAndRow(7,5,"ACT sale");
$wks->setCellValueByColumnAndRow(8,5,"Achievement %");
$wks->setCellValueByColumnAndRow(9,5,"Sales variance");
$wks->setCellValueByColumnAndRow(10,5,"Target Margin");
$wks->setCellValueByColumnAndRow(10,5,"Target Margin %");
$wks->setCellValueByColumnAndRow(11,5,"Actual margin");
$wks->setCellValueByColumnAndRow(11,5,"Actual margin %");
$wks->setCellValueByColumnAndRow(13,5,"Margin variance");


$cnt=6;
$rows=0;

while($rst=mysqli_fetch_row($qry)){
if($rst[1]!=0 || $rst[2]!=0){

if($rst[1]==0){
$pcn=100;}
if($rst[2]==0){
$pcn=0;}
if($rst[1]>0 && $rst[2]>0){
$pcn=$rst[2]*100/$rst[1];}

if($rst[1]!=0){
$tmg=$rst[3]*100/$rst[1];}
else{
$tmg=0;}

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


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

$cnt++;
}}


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

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

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

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

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

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

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

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

$wks->getStyle($wks->getCellByColumnAndRow(4,5)->getCoordinate().':'.$wks->getCellByColumnAndRow(14,$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->getColumnDimension('O')->setAutoSize(true);

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

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

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

?>


