<?php

set_time_limit(0);

require_once 'Classes/PHPExcel.php';


$start=$_REQUEST['starts'];
$end=$_REQUEST['ends'];
$param=$_REQUEST['params'];


$conn=mysqli_connect("localhost","primarysales","S5L7lF01EQZvQrGK","primarysales_dianaold");
$flr="";
if($param!='All'){
$flr="and market_returns.regions='".$param."'";}
$fld="where dates>='".$start."' and dates<='".$end."' ";

$exl=new PHPExcel();
$rn=4;
$agc=4;
$sarr=array();

$sqlst="select distinct agent_names,territory_names from market_returns ".$fld.$flr;

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


$wsh=$exl->getActiveSheet();
$wsh->getColumnDimension('A')->setWidth(35);

#$wsh->getStyle('A1:AZ5000')->getFont()->setName('Calibri');

$wsh->getStyle('B1')->getFont()->setBold(True);
$wsh->getStyle('B1')->getFont()->setSize(14);
$wsh->setCellValue('B1','Market return summary');

$wsh->setCellValue('B3','From:');
$wsh->setCellValue('E3','To:');
$wsh->setCellValue('C3',$start);
$wsh->setCellValue('F3',$end);

$wsh->getStyle('A'.$rn.':G'.$rn)->getFont()->setBold(True);
$wsh->setCellValue('A'.($rn),'Agent name');
$wsh->setCellValue('B'.($rn),'Territory name');
$wsh->setCellValue('F'.($rn),'Amount');
$wsh->setCellValue('G'.($rn),'Go To');
$wsh->getStyle('G'.($rn))->getFont()->getColor()->setRGB('3333FF');
$rn=$rn+mysqli_num_rows($aqry)+4;


$aqry=mysqli_query($conn,$sqlst);
while($arst=mysqli_fetch_row($aqry)){

$tvl=0;
$ttq=0;
$agc++;

$wsh->getStyle('B'.($rn+2))->getFont()->setBold(True);
$wsh->getStyle('B'.($rn+2))->getFont()->setSize(12);
$wsh->setCellValue('B'.($rn+2),'Market return summary');
$wsh->setCellValue('E'.($rn+2),'Go Back');
$wsh->getStyle('E'.($rn+2))->getFont()->getColor()->setRGB('3333FF');
$wsh->getCell('E'.($rn+2))->getHyperlink()->setUrl("sheet://Worksheet!A1");

$vurl=$rn;

$wsh->setCellValue('A'.($rn+4),'Agent:');
$wsh->setCellValue('A'.($rn+5),'Territory:');
$wsh->setCellValue('f'.($rn+4),'From:');
$wsh->setCellValue('f'.($rn+5),'To:');

$wsh->setCellValue('B'.($rn+4),$arst[0]);
$wsh->setCellValue('B'.($rn+5),$arst[1]);
$wsh->setCellValue('g'.($rn+4),$start);
$wsh->setCellValue('g'.($rn+5),$end);

$rtsqlst="select distinct reasons from market_returns ".$fld;
$rtqry=mysqli_query($conn,$rtsqlst);
$dcn=8;
while($rtrst=mysqli_fetch_row($rtqry)){
$sarr[$dcn-8]=$rtrst[0];
$wsh->setCellValueByColumnAndRow($dcn,4,$rtrst[0]);
$dcn++;
}
$wsh->getStyle($wsh->getCellByColumnAndRow(8,4)->getCoordinate().':'.$wsh->getCellByColumnAndRow(8+count($sarr),$rn)->getCoordinate())->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);



$flr="and market_returns.agent_names='".$arst[0]."' and market_returns.territory_names='".$arst[1]."' ";

$sqlst="select distinct reasons from market_returns ".$fld.$flr;
$qry=mysqli_query($conn,$sqlst);

$tmp="";
while($rst=mysqli_fetch_row($qry)){
$tmp=$tmp.$rst[0]."###";}
$arr=explode("###",$tmp);

$arvl=array();


$sqlst="select market_returns.item_names ";
for($cn=0;$cn<count($arr) && strlen($arr[$cn])>0;$cn++){
$tmp=str_replace(" ","_",$arr[$cn]);
$tmp=str_replace("-","_",$tmp);
$sqlst=$sqlst.",".$tmp.".qts ".",".$tmp.".vls ";}

$sqlst=$sqlst." from market_returns ";


for($cn=0;$cn<count($arr) && strlen($arr[$cn])>0;$cn++){
$tmp=str_replace(" ","_",$arr[$cn]);
$tmp=str_replace("-","_",$tmp);
$sqlst=$sqlst." left join (select agent_names,territory_names,item_names,sum(pcs_qty) as qts,sum(pcs_qty*pcs_price*(100-discount_rates)/100) as vls 
from market_returns ".$fld.$flr." and reasons='".$arr[$cn]."' group by agent_names,territory_names,item_names ) as ".$tmp." on 
market_returns.agent_names=".$tmp.".agent_names and market_returns.territory_names=".$tmp.".territory_names and market_returns.item_names=".$tmp.".item_names ";
}


$sqlst=$sqlst.$fld.$flr." group by market_returns.agent_names,market_returns.item_names order by market_returns.agent_names,
market_returns.territory_names,market_returns.item_names";
$wsh->setCellValueByColumnAndRow(0,$rn+8,'Item');

#echo $sqlst;

for($cn=0;$cn<count($arr) && strlen($arr[$cn])>0;$cn++){
$arvl[$cn]=0;
$wsh->setCellValueByColumnAndRow(($cn*2)+1,$rn+8,$arr[$cn]);}

for($cn=0;$cn<count($arr) && strlen($arr[$cn])>0;$cn++){
$wsh->setCellValueByColumnAndRow(($cn*2)+1,$rn+9,'Qty');
$wsh->setCellValueByColumnAndRow(($cn*2)+2,$rn+9,'Value');}

$wsh->setCellValueByColumnAndRow((count($arr)*2)-1,$rn+8,'Total qty');
$wsh->setCellValueByColumnAndRow((count($arr)*2),$rn+8,'Total value');

$wsh->getStyle($wsh->getCellByColumnAndRow(0,$rn+8)->getCoordinate().':'.$wsh->getCellByColumnAndRow(count($arr)*2,$rn+9)->getCoordinate())->getFont()->setSize(11);
$wsh->getStyle($wsh->getCellByColumnAndRow(0,$rn+8)->getCoordinate().':'.$wsh->getCellByColumnAndRow(count($arr)*2,$rn+9)->getCoordinate())->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$wsh->getStyle($wsh->getCellByColumnAndRow(0,$rn+8)->getCoordinate().':'.$wsh->getCellByColumnAndRow(count($arr)*2,$rn+9)->getCoordinate())->getFill()->getStartColor()->setARGB('A9A9F5');
$wsh->getStyle($wsh->getCellByColumnAndRow(0,$rn+8)->getCoordinate().':'.$wsh->getCellByColumnAndRow(count($arr)*2,$rn+9)->getCoordinate())->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_border::BORDER_THIN);
$wsh->getStyle($wsh->getCellByColumnAndRow(0,$rn+8)->getCoordinate().':'.$wsh->getCellByColumnAndRow(count($arr)*2,$rn+9)->getCoordinate())->getBorders()->getAllBorders()->getColor()->setARGB('FFFFFF');

$wsh->getStyle($wsh->getCellByColumnAndRow(0,$rn+8)->getCoordinate().':'.$wsh->getCellByColumnAndRow(count($arr)*2,$rn+9)->getCoordinate())->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_border::BORDER_THIN);
$wsh->getStyle($wsh->getCellByColumnAndRow(0,$rn+8)->getCoordinate().':'.$wsh->getCellByColumnAndRow(count($arr)*2,$rn+9)->getCoordinate())->getBorders()->getAllBorders()->getColor()->setARGB('FFFFFF');

#echo $sqlst."<br><br>";

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

$wsh->getStyle($wsh->getCellByColumnAndRow(0,$rn+8)->getCoordinate().':'.$wsh->getCellByColumnAndRow(mysqli_num_fields($qry)+1,mysqli_num_rows($qry)+$rn+10)->getCoordinate())->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
$wsh->getStyle($wsh->getCellByColumnAndRow(0,$rn+8)->getCoordinate().':'.$wsh->getCellByColumnAndRow(mysqli_num_fields($qry)+1,mysqli_num_rows($qry)+$rn+10)->getCoordinate())->getBorders()->getAllBorders()->getColor()->setARGB('FFFFFF');
$wsh->getStyle($wsh->getCellByColumnAndRow(0,$rn+8)->getCoordinate().':'.$wsh->getCellByColumnAndRow(mysqli_num_fields($qry)+1,mysqli_num_rows($qry)+$rn+10)->getCoordinate())->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_border::BORDER_THIN);

#$wsh->getStyle($wsh->getCellByColumnAndRow(0,$rn+10)->getCoordinate().':'.$wsh->getCellByColumnAndRow(mysqli_num_fields($qry)+1,mysqli_num_rows($qry)+$rn+10)->getCoordinate())->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
#$wsh->getStyle($wsh->getCellByColumnAndRow(0,$rn+10)->getCoordinate().':'.$wsh->getCellByColumnAndRow(mysqli_num_fields($qry)+1,mysqli_num_rows($qry)+$rn+10)->getCoordinate())->getFill()->getStartColor()->setARGB('FA5858');

$gqty=0;
$gvls=0;
$types=0;
$rn=$rn+10;
$cn=$rn;


$bcl='EFEFFB';

while($rst=mysqli_fetch_row($qry)){
for($cnt=0;$cnt<count($rst);$cnt++){

if($cnt>0 && $rst[$cnt]==NULL){
$rst[$cnt]=0;}

if($cnt>0 && $types==0){
$gqty=$gqty+$rst[$cnt];
$types=1;}
else{
$gvls=$gvls+$rst[$cnt];
$arvl[($cnt-1)/2]=$arvl[($cnt-1)/2]+$rst[$cnt];
$types=0;}


}
$wsh->fromArray($rst,NULL,'A'.$cn);

if($bcl=='EFEFFB'){
$bcl='E0E0F8';}
else{
$bcl='EFEFFB';}

$wsh->getStyle($wsh->getCellByColumnAndRow(0,$rn)->getCoordinate().':'.$wsh->getCellByColumnAndRow(mysqli_num_fields($qry)+1,$rn)->getCoordinate())->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$wsh->getStyle($wsh->getCellByColumnAndRow(0,$rn)->getCoordinate().':'.$wsh->getCellByColumnAndRow(mysqli_num_fields($qry)+1,$rn)->getCoordinate())->getFill()->getStartColor()->setARGB($bcl);

$cn++;
$rn++;

$wsh->setCellValueByColumnAndRow(mysqli_num_fields($qry),$rn-1,$gqty);
$wsh->setCellValueByColumnAndRow(mysqli_num_fields($qry)+1,$rn-1,$gvls);

$ttq=$ttq+$gqty;
$tvl=$tvl+$gvls;

$gqty=0;
$gvls=0;
}


$wsh->getStyle($wsh->getCellByColumnAndRow(0,$rn)->getCoordinate().':'.$wsh->getCellByColumnAndRow(mysqli_num_fields($qry)+2,$rn)->getCoordinate())->getFont()->setBold(True);
$wsh->setCellValueByColumnAndRow(0,$rn,'Total');
$wsh->setCellValueByColumnAndRow(mysqli_num_fields($qry),$rn,$ttq);
$wsh->setCellValueByColumnAndRow(mysqli_num_fields($qry)+1,$rn,$tvl);
$ttq=0;
$tvl=0;

$rn=$rn+2;

$tvl=0;

$tvl=0;

$wsh->setCellValueByColumnAndRow(0,$rn,'Summary');
$wsh->getStyle($wsh->getCellByColumnAndRow(0,$rn)->getCoordinate().':'.$wsh->getCellByColumnAndRow(1,$rn)->getCoordinate())->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$wsh->getStyle($wsh->getCellByColumnAndRow(0,$rn)->getCoordinate().':'.$wsh->getCellByColumnAndRow(1,$rn)->getCoordinate())->getFill()->getStartColor()->setARGB('EFEFFB');


for($cnt=0;$cnt<count($arvl);$cnt++){
$ky=array_search($arr[$cnt],$sarr);
$wsh->setCellValueByColumnAndRow($ky+8,$agc,$arvl[$cnt]);

$rn++;
$tvl=$tvl+$arvl[$cnt];
$wsh->setCellValueByColumnAndRow(0,$rn,$arr[$cnt]);
$wsh->setCellValueByColumnAndRow(1,$rn,$arvl[$cnt]);}

$wsh->setCellValueByColumnAndRow(0,$rn+1,'Total');
$wsh->setCellValueByColumnAndRow(1,$rn+1,$tvl);


$wsh->getStyle($wsh->getCellByColumnAndRow(0,$rn+1)->getCoordinate().':'.$wsh->getCellByColumnAndRow(1,$rn+1)->getCoordinate())->getFont()->setBold(True);
$wsh->getStyle($wsh->getCellByColumnAndRow(0,$rn-(count($arvl)))->getCoordinate().':'.$wsh->getCellByColumnAndRow(1,$rn+1)->getCoordinate())->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
$wsh->getStyle($wsh->getCellByColumnAndRow(0,$rn-(count($arvl)))->getCoordinate().':'.$wsh->getCellByColumnAndRow(1,$rn+1)->getCoordinate())->getBorders()->getAllBorders()->getColor()->setARGB('EFEFFB');
$wsh->getStyle($wsh->getCellByColumnAndRow(0,$rn-(count($arvl)))->getCoordinate().':'.$wsh->getCellByColumnAndRow(1,$rn+1)->getCoordinate())->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_border::BORDER_THIN);



$rn=$rn+5;

$wsh->setCellValue('A'.$agc,$arst[0]);
$wsh->setCellValue('B'.$agc,$arst[1]);
$wsh->setCellValue('F'.$agc,$tvl);
$wsh->getStyle('F'.$agc)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
$wsh->setCellValue('G'.$agc,'Goto');
$wsh->getCell('G'.$agc)->getHyperlink()->setUrl("sheet://Worksheet!A".($vurl+10));
$wsh->getStyle('G'.$agc)->getFont()->getColor()->setRGB('3333FF');
$wsh->getStyle('G'.$agc)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);


$wsh->getStyle($wsh->getCellByColumnAndRow(0,4)->getCoordinate().':'.$wsh->getCellByColumnAndRow($ky+9,$agc)->getCoordinate())->getBorders()->getAllBorders()->getColor()->setARGB('FFFFFF');
$wsh->getStyle($wsh->getCellByColumnAndRow(0,4)->getCoordinate().':'.$wsh->getCellByColumnAndRow($ky+9,$agc)->getCoordinate())->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_border::BORDER_THIN);
$wsh->getStyle($wsh->getCellByColumnAndRow(0,4)->getCoordinate().':'.$wsh->getCellByColumnAndRow($ky+9,$agc)->getCoordinate())->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$wsh->getStyle($wsh->getCellByColumnAndRow(0,4)->getCoordinate().':'.$wsh->getCellByColumnAndRow($ky+9,4)->getCoordinate())->getFill()->getStartColor()->setARGB('A9A9F5');
$wsh->getStyle($wsh->getCellByColumnAndRow(0,5)->getCoordinate().':'.$wsh->getCellByColumnAndRow($ky+9,$agc)->getCoordinate())->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$wsh->getStyle($wsh->getCellByColumnAndRow(0,5)->getCoordinate().':'.$wsh->getCellByColumnAndRow($ky+9,$agc)->getCoordinate())->getFill()->getStartColor()->setARGB('EFEFFB');

$srn=$rn-2;
$wsh->setCellValue('A'.$srn,"Monthly RD sales");
$ssqlst="select sum(pcs_qty*pcs_price) from rd_invoices where dates>='".$start."' and dates<='".$end." ' and agent_names='".$arst[0]."' group by agent_names";
#echo $ssqlst;
$sqry=mysqli_query($conn,$ssqlst);
$srst=mysqli_fetch_row($sqry);
$wsh->setCellValue('B'.$srn,$srst[0]);
$wsh->getStyle('A'.$srn)->getFont()->setBold(True);
$wsh->getStyle('B'.$srn)->getFont()->setBold(True);
$wsh->getStyle('B'.$srn)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
}


$esv=new PHPExcel_Writer_Excel2007($exl);
$nms="mrexports/exp".rand().".xlsx";
$esv->save($nms);

header("location:".$nms);

?>
