<?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=mysql_connect("localhost","primarysales","S5L7lF01EQZvQrGK");

mysql_select_db("primarysales_dianaold",$conn);



$seflt=" where agent_names is not null ";



$sesqlst="select * from acs where user_names='".$_SESSION['usernames']."'";

$seqry=mysql_query($sesqlst,$conn);



while($serst=mysql_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]."' ";}

}



#echo $seflt;



$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."' ";}





$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 region IS NOT NULL ";

if(strtolower($regions)!='all'){

$rsqlst=$rsqlst." region='".$regions."'";}

if(strtolower($agents)!='all'){

$rsqlst=$rsqlst." and agent_name='".$agents."'";}



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





$sqlst=$rsqlst.$asqlst;

#echo $sqlst;



#printf("<div class='tdt'>");





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



$tgqt=0;

$acqt=0;

$tgvt=0;

$acvt=0;

$tgmg=0;

$acmg=0;



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



$cnt=5;

$rows=0;



$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,"Actual margin");

$wks->setCellValueByColumnAndRow(11,5,"Margin variance");



$wks->getStyle($wks->getCellByColumnAndRow(4,$cnt)->getCoordinate().':'.$wks->getCellByColumnAndRow(11,$cnt)->getCoordinate())->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);

$wks->getStyle($wks->getCellByColumnAndRow(4,$cnt)->getCoordinate().':'.$wks->getCellByColumnAndRow(11,$cnt)->getCoordinate())->getFill()->getStartColor()->setARGB('CCD1D1');





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

$itemshrf="<a href='prtgitemsummary.php?rpsty=agentsummarys&starts=".$starts."&ends=".$ends."&regions=".$regions."&agents=all&territorys=all&ranges=all&zones=".$zones."&rsms=All&executives=All&reps=All&items=All' target='_blank'>Item list</a>";

$agentshrf="<a href='prtgagentrangesummary.php?rpsty=agentsummarys&starts=".$starts."&ends=".$ends."&regions=".$regions."&agents=all&territorys=all&ranges=all&zones=".$zones."&rsms=All&executives=All&reps=All&items=All' target='_blank'>Agent list</a>";

$regionshrf="<a href='prtgregionrangesummary.php?rpsty=agentsummarys&starts=".$starts."&ends=".$ends."&regions=".$regions."&agents=all&territorys=all&ranges=all&zones=".$zones."&rsms=All&executives=All&reps=All&items=All' target='_blank'>Region list</a>";



if($rst[1]>0){

$tgv=$rst[1];}

else{

$tgv=$rst[2];}



if($tgv>0){

$pcn=($rst[2]/$tgv)*100;}

else{

$pcn=0;}



$cnt++;

$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($rst[4]));

$wks->setCellValueByColumnAndRow(11,$cnt,round($rst[4]-$rst[3]));





$wks->getStyle($wks->getCellByColumnAndRow(4,$cnt)->getCoordinate().':'.$wks->getCellByColumnAndRow(11,$cnt)->getCoordinate())->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);

$wks->getStyle($wks->getCellByColumnAndRow(4,$cnt)->getCoordinate().':'.$wks->getCellByColumnAndRow(11,$cnt)->getCoordinate())->getFill()->getStartColor()->setARGB('CCD1D1');



$asqlst=" left join (select item_ranges 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." and regions='".$rst[0]."' group by item_ranges) as targets on tgagent_names=magent_names ";



$asqlst=$asqlst." left join (select item_ranges 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." and regions='".$rst[0]."' group by item_ranges) as acts on acagent_names=magent_names ";





$rsqlst="select magent_names as region ";



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



$rsqlst=$rsqlst." from (select distinct item_ranges as magent_names from itemsmrd ";

if(strtolower($regions)!='all' || strtolower($agents)!='all'){

$rsqlst=$rsqlst." where ";

if(strtolower($regions)!='all'){

$rsqlst=$rsqlst." region='".$regions."'";}

if(strtolower($agents)!='all'){

$rsqlst=$rsqlst." and agent_name='".$agents."'";}

}

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





$sqlst=$rsqlst.$asqlst;

$rqry=mysql_query($sqlst,$conn);

while($qrst=mysql_fetch_row($rqry)){



$itemshrf="<a href='prtgitemsummary.php?rpsty=agentsummarys&starts=".$starts."&ends=".$ends."&regions=".$regions."&agents=all&territorys=all&ranges=".$qrst[0]."&zones=".$rst[0]."&rsms=All&executives=All&reps=All&items=All' target='_blank'>Item list</a>";

$regionshrf="<a href='prtgagentrangesummary.php?rpsty=agentsummarys&starts=".$starts."&ends=".$ends."&regions=".$regions."&agents=all&territorys=all&ranges=".$qrst[0]."&zones=".$rst[0]."&rsms=All&executives=All&reps=All&items=All' target='_blank'>Region list</a>";

$regionshrf="<a href='prtgregionrangesummary.php?rpsty=agentsummarys&starts=".$starts."&ends=".$ends."&regions=".$regions."&agents=all&territorys=all&ranges=".$qrst[0]."&zones=".$rst[0]."&rsms=All&executives=All&reps=All&items=All' target='_blank'>Region list</a>";



if($qrst[1]>0){

$tgv=$qrst[1];}

else{

$tgv=$qrst[2];}



if($tgv>0){

$pcn=($qrst[2]/$tgv)*100;}

else{

$pcn=0;}



$cnt++;

$wks->setCellValueByColumnAndRow(4,$cnt,$qrst[0]);

$wks->setCellValueByColumnAndRow(5,$cnt,round($qrst[1]));

$wks->setCellValueByColumnAndRow(6,$cnt,round($qrst[2]));

$wks->setCellValueByColumnAndRow(7,$cnt,round($pcn));

$wks->setCellValueByColumnAndRow(8,$cnt,round($qrst[2]-$qrst[1]));

$wks->setCellValueByColumnAndRow(9,$cnt,round($qrst[3]));

$wks->setCellValueByColumnAndRow(10,$cnt,round($qrst[4]));

$wks->setCellValueByColumnAndRow(11,$cnt,round($qrst[4]-$qrst[3]));



$wks->getStyle($wks->getCellByColumnAndRow(4,$cnt)->getCoordinate().':'.$wks->getCellByColumnAndRow(11,$cnt)->getCoordinate())->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);

$wks->getStyle($wks->getCellByColumnAndRow(4,$cnt)->getCoordinate().':'.$wks->getCellByColumnAndRow(11,$cnt)->getCoordinate())->getFill()->getStartColor()->setARGB('F2F2F2');

}



}	



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





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

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

$wks->getStyle($wks->getCellByColumnAndRow(4,5)->getCoordinate().':'.$wks->getCellByColumnAndRow(11,$cnt)->getCoordinate())->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);



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



$wks->getStyle($wks->getCellByColumnAndRow(4,5)->getCoordinate().':'.$wks->getCellByColumnAndRow(11,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(11,$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('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="prtgregionsummary".substr($_SESSION['usernames'],0,3).rand().".xlsx";



$esv=new PHPExcel_Writer_Excel2007($wbk);

$esv->save($nms);

header("location:".$nms);





?>