<?php
require_once('Spreadsheet/Excel/Writer.php');

session_start();
if($HTTP_SESSION_VARS['dbName']==""){
header("location:../index.php");
}


include("config.inc.php");

//Set Time Execution For the Report
ini_set('max_execution_time', $config['ReportTimeExecution']);

$connection = mysql_connect($config['dbServer'],$config['dbUser'],$config['dbPass']) or die("Could not connect to DB");
mysql_select_db($HTTP_SESSION_VARS['dbName'],$connection) or die("Could not find DB");


$querysetup="select grid_color1,grid_color2, DecimalBase, DecimalBase1, DecimalBase2, ifNull(InventoryRMaskqty,0) As InventoryRMaskqty, Base1, Base2, Company_Name As CompanyName from setup";
$resultsetup=mysql_query($querysetup,$connection) or die(mysql_error());
while($rows=mysql_fetch_array($resultsetup)){
  extract($rows);
}

$querysalessetup="select SalesTTC from salessetup";
$resultsalessetup=mysql_query($querysalessetup,$connection) or die(mysql_error());
while($rowssalessetup=mysql_fetch_array($resultsalessetup)){
  extract($rowssalessetup);
}

$querylog=" select ifNull(FI_RepRestrictProject,0) As FI_RepRestrictProject, ifNull(FI_RepRestrictCostcenter,0) As FI_RepRestrictCostcenter, ".
		  " ifNull(FI_RepRestrictWarehouse,0) As FI_RepRestrictWarehouse ".
          " from ".$HTTP_SESSION_VARS['dbLabel'].".login where user_account='".$HTTP_SESSION_VARS['useraccount']."' and Upper(Login_Schema) = Upper('".$HTTP_SESSION_VARS['dbName']."') ";
$resultlog=mysql_query($querylog) or die(mysql_error());
while($rows=mysql_fetch_array($resultlog)){
	extract($rows);
}

//Read From Module Table
$querymodule=" Select ifNull(Module_Delivery,0) As Module_Delivery From Module ";
$resultmodule=mysql_query($querymodule,$connection) or die(mysql_error());
while($rowmodule=mysql_fetch_array($resultmodule)){
	extract($rowmodule);
}

$queryproject=" select distinct project.project_code, project_name ".
              " from project left join ".$HTTP_SESSION_VARS['dbLabel'].".loginstk on project.project_code like concat(ifnull(loginstk.project_code,project.project_code),'%') ".
			  " where user_account='".$HTTP_SESSION_VARS['useraccount']."' ".
			  " and (loginstk.project_code is not null and loginstk.project_code != '') ".
			  " order by project.project_code";
$projectresult = mysql_query($queryproject,$connection) or die(mysql_error());
$projectexist  = mysql_num_rows($projectresult);

$querycostcent=" select distinct costcent.costcent_code, costcent_name ".
               " from costcent left join ".$HTTP_SESSION_VARS['dbLabel'].".loginstk on costcent.costcent_code like concat(ifnull(loginstk.costcent_code,costcent.costcent_code),'%') ".
			   " where user_account='".$HTTP_SESSION_VARS['useraccount']."' ".
			   " and (loginstk.costcent_code is not null and loginstk.costcent_code != '') ".
			   " order by costcent.costcent_code";
$costcentresult = mysql_query($querycostcent,$connection) or die(mysql_error());
$costcentexist  = mysql_num_rows($costcentresult);		

$querywh=" select distinct warehouse.warehouse_code, warehouse_name ".
         " from warehouse where warehouse_code in ( select warehouse_code from ".$HTTP_SESSION_VARS['dbLabel'].".loginstk ".
		 "                                          where user_account='".$HTTP_SESSION_VARS['useraccount']."' ".
		 "                                          and (loginstk.warehouse_code is not null and loginstk.warehouse_code != '') ) ".
		 " order by warehouse.warehouse_code";
$resultwh = mysql_query($querywh,$connection) or die(mysql_error());
$whexist  = mysql_num_rows($resultwh);


$now=current_server_time($connection);
$dt=split(" ",$now[0]);
$printdate=split("-",$dt[0]);

$ReportTitle = '';
if ($reports == 1) 	     { $ReportTitle = 'Target Report'; $ReportLabel = 'Target'; }
else if ($reports == 2)  { $ReportTitle = 'Rebate Report'; $ReportLabel = 'Rebate'; }

//Creating a workbook
$workbook = new Spreadsheet_Excel_Writer();

//Sending HTTP headers
$workbook->send('Exp_TargetRebateReport.xls');
 
//Creating a worksheet
$worksheet=&$workbook->addWorksheet('Target Rebate Report');
$worksheet->setLandscape();
 
//set all columns same width
$columnWidth = 10;

//Title Format 
$sheetTitleFormat =& $workbook->addFormat(array('bold'=>1,
  'size'=>10));

//Column Title Format  
$columnTitleFormat =& $workbook->addFormat(array('bold'=>1,
  'top'=>1,
  'bottom'=>1 ,
  'size'=>15,
  'color'=>0x0A,
  ));
  
 
  
// Regular Format
$regularleftFormat =& $workbook->addFormat(array('size'=>9,
  'align'=>'left'  ));
$regularrightFormat =& $workbook->addFormat(array('size'=>9,
  'align'=>'right'  ));  
  
$TitleFormat =& $workbook->addFormat(array('size'=>11,
  'align'=>'left',
  'bold'=>1
  ));

$boldleftFormat =& $workbook->addFormat(array('size'=>9,
  'align'=>'left'  ,
  'valign'=>'top'  ,
  'bold'=>1
  ));  
$boldrightFormat =& $workbook->addFormat(array('size'=>9,
  'align'=>'right'  ,
  'bold'=>1
  )); 
$boldcenterFormat =& $workbook->addFormat(array('size'=>9,
  'align'=>'center'  ,
  'valign'=>'top'  ,
  'bold'=>1
  ));    
  

$seloptionFormat =& $workbook->addFormat(array('size'=>9,
  'align'=> 'left'  ,
  'valign'=> 'top'  ,
  'bold'=> 1
  ));
$seloptionFormat->setTextWrap();


if ($curr == ''){
   $curr='';  
   $displaycur = '';
}   
else {
   $displaycur = $curr;
   $curr="'".$curr."'";
}  


$SelectedOptions = "Filter by: \n";
if ($fromdate != '')      $SelectedOptions .= "-> Date From: ". $fromdate . "  To: ". $todate ."\n";	
if ($displaycur != '')    $SelectedOptions .= "-> Currency: ". $displaycur ."\n";	
if ($fromclient != '')    $SelectedOptions .= "-> Client From: ". $fromclient . "  To: ". $toclient ."\n";


if (!empty($fromdate))   $fromdate = explodeword($fromdate);
if (!empty($todate))     $todate = explodeword($todate);  

$fromdate	   = GetSQLValueString($fromdate, 'date');
$todate        = GetSQLValueString($todate, 'date');   
$fromclient    = GetSQLValueString($fromclient, 'text');
$toclient      = GetSQLValueString($toclient, 'text');
  
  

//Display Report Header
$worksheet->write(1,0,$CompanyName, $TitleFormat);
$worksheet->write(1,2,$ReportTitle, $TitleFormat);
               
$worksheet->write(1,6,"Printed Date:", $boldleftFormat);
$worksheet->write(1,7,$printdate[2]."/".$printdate[1]."/".$printdate[0], $regularleftFormat);
$worksheet->write(2,6,"Time:", $boldleftFormat);		
$worksheet->write(2,7,$dt[1]." ".$dt[2], $regularleftFormat);
  
//Displaying Selected Options	
// Merge cells from row 3, col 0 to row 3, col 0
$worksheet->setMerge(3, 0, 8, 6);
$worksheet->write(3,0,$SelectedOptions, $seloptionFormat);  

$RowNum    = 10;
$ColumnNum = 0;

$worksheet->setColumn ($ColumnNum, $ColumnNum, 20);
$worksheet->write($RowNum, $ColumnNum, "Client Code", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 35);
$worksheet->write($RowNum, $ColumnNum, "Client Name", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 12);
$worksheet->write($RowNum, $ColumnNum, "Total Qty", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
$worksheet->write($RowNum, $ColumnNum, "Total Selling", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
$worksheet->write($RowNum, $ColumnNum, "Total VAT", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
$worksheet->write($RowNum, $ColumnNum, "Selling + Vat", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 10);
$worksheet->write($RowNum, $ColumnNum, "$ReportLabel", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
$worksheet->write($RowNum, $ColumnNum, "$ReportLabel Amount", $boldcenterFormat);
$ColumnNum += 1;


$QueryRestrictPrj = " Inner join ( select distinct project_code from ".$HTTP_SESSION_VARS['dbLabel'].".loginstk ".
					" 			   where loginstk.user_account='".$HTTP_SESSION_VARS['useraccount']."' ".
					"              and (project_code is not null and project_code != '') ".
					"			 ) Q4  ";
					
$QueryRestrictCst = " Inner join ( select distinct costcent_code from ".$HTTP_SESSION_VARS['dbLabel'].".loginstk ".
					"			   where loginstk.user_account='".$HTTP_SESSION_VARS['useraccount']."' ".
					"              and (costcent_code is not null and costcent_code != '') ".
					"			 ) Q5  ";
					


$query = '';
$query = $query." Select TransType, CurrencyCode, ClientCode, ClientName, ClientRebate, ClientTarget, TransQty, ".
				" SellingAmount, VatAmount, SellingAmountVat, (ClientRebate*SellingAmountVat/100) As RebateAmount, ".
				" (SellingAmountVat - ClientTarget) As TargetAmount ".
				" From ( ";


if ($Module_Delivery == 1) {
	$query = $query." Select TransType, CurrencyCode, ClientCode, ClientName, ClientRebate, ClientTarget, Sum(TransQty) As TransQty, ".
					" Sum(if(TransType = 'DI', ifNull(SellingAmount,0), (-1)*ifNull(SellingAmount,0))) As SellingAmount,  ".
					" Sum(if(TransType = 'DI', ifNull(VatAmount,0), (-1)*ifNull(VatAmount,0))) As VatAmount,  ".
					" Sum(if(TransType = 'DI', ifNull(SellingAmountVat,0), (-1)*ifNull(SellingAmountVat,0))) As SellingAmountVat  ".
					" From ( ";
	
	$query = $query." Select Sales.Sale_Reference As TransRef, Sales.Journal_Type As TransType, Sales.Currency_Code As CurrencyCode, ".
					" Sales.Client_Code As ClientCode, Clients.Ledger_Name As ClientName, ifNull(Clients.Client_Rebate,0) As ClientRebate, ifNull(Clients.Client_Target,0) As ClientTarget, ".
					" Sum(if(SalesDt.Journal_Type = 'SI', ifNull(SalesDt.SaleDt_Quantity,0), (-1)*ifNull(SalesDt.SaleDt_Quantity,0))) As TransQty,  ".
					" if(upper($curr)='".$Base1."', (ifNull(Sales.Sale_TaxableBase1,0) + ifNull(Sales.Sale_NoneTaxableBase1,0) - ifNull(Sales.Sale_DiscountBase1,0)), ".
					"     (ifNull(Sales.Sale_TaxableBase2,0) + ifNull(Sales.Sale_NoneTaxableBase2,0) - ifNull(Sales.Sale_DiscountBase2,0))) As SellingAmount, ".
					" if(upper($curr)='".$Base1."', ifNull(Sales.Sale_VATBase1,0), ifNull(Sales.Sale_VATBase2,0)) As VatAmount, ".
					" if(upper($curr)='".$Base1."', (ifNull(Sales.Sale_TaxableBase1,0) + ifNull(Sales.Sale_NoneTaxableBase1,0) + ifNull(Sales.Sale_VATBase1,0) - ifNull(Sales.Sale_DiscountBase1,0)), ".
					"     (ifNull(Sales.Sale_TaxableBase2,0) + ifNull(Sales.Sale_NoneTaxableBase2,0) + ifNull(Sales.Sale_VATBase2,0) - ifNull(Sales.Sale_DiscountBase2,0))) As SellingAmountVat ".
					" From SalesDt  ".
					" Inner Join Sales on SalesDt.Sale_ID = Sales.Sale_ID ".
					" Inner Join Clients on Clients.Ledger_Number = Sales.Client_Code ";

	if ($projectexist >	0 && $FI_RepRestrictProject == 1)
		$query= $query.$QueryRestrictPrj." on Sales.project_code like concat(Q4.project_code,'%') ";
								 
	if ($costcentexist > 0 && $FI_RepRestrictCostcenter == 1)
		$query= $query.$QueryRestrictCst." on Sales.Costcenter_Code like concat(Q5.costcent_code,'%') ";		 

	$query= $query.	" Where (Sales.Sale_Reference Is Not Null And Sales.Sale_Reference != '')  ".
					" And Sales.Journal_Type in ('SI','SR') ".
					" And Date_format(Sales.Sale_Date,'%Y-%m-%d') between ifNull(". $fromdate.",date_format(Sales.Sale_Date,'%Y-%m-%d')) and ifNull(". $todate.",date_format(Sales.Sale_Date,'%Y-%m-%d'))  ".
					" And ifNull(Sales.Client_Code,'-1') between ifNull(".$fromclient.",ifNull(Sales.Client_Code,'-1')) and ifNull(". $toclient.",ifNull(Sales.Client_Code,'-1')) ";
	  

	$query= $query." Group By TransRef, ClientCode ".
				   " Order by TransRef, ClientCode  ";
	$query= $query." ) Q1 ";
	$query= $query." 	Group By ClientCode ".
				   " 	Order by ClientCode  ";
				   
	
	

}

else if ($Module_Delivery == 0) {

	$query = $query." Select TransType, CurrencyCode, ClientCode, ClientName, ClientRebate, ClientTarget, Sum(TransQty) As TransQty, ".
					" Sum(if(TransType = 'DI', ifNull(SellingAmount,0), (-1)*ifNull(SellingAmount,0))) As SellingAmount,  ".
					" Sum(if(TransType = 'DI', ifNull(VatAmount,0), (-1)*ifNull(VatAmount,0))) As VatAmount,  ".
					" Sum(if(TransType = 'DI', ifNull(SellingAmountVat,0), (-1)*ifNull(SellingAmountVat,0))) As SellingAmountVat  ".
					" From ( ";
	
	$query = $query." Select Deliveries.Delivery_Reference As TransRef, Deliveries.Journal_Type As TransType, Deliveries.Currency_Code As CurrencyCode, ".
					" Deliveries.Client_Code As ClientCode, Clients.Ledger_Name As ClientName, ifNull(Clients.Client_Rebate,0) As ClientRebate, ifNull(Clients.Client_Target,0) As ClientTarget,  ".
					" Sum(if(DeliveryDt.Journal_Type = 'DI', ifNull(DeliveryDt.DeliveryDt_Quantity,0), (-1)*ifNull(DeliveryDt.DeliveryDt_Quantity,0))) As TransQty,  ".
					" if(upper($curr)='".$Base1."', (ifNull(Deliveries.Delivery_TaxableBase1,0) + ifNull(Deliveries.Delivery_NoneTaxableBase1,0) - ifNull(Deliveries.Delivery_DiscountBase1,0)), ".
					"     (ifNull(Deliveries.Delivery_TaxableBase2,0) + ifNull(Deliveries.Delivery_NoneTaxableBase2,0) - ifNull(Deliveries.Delivery_DiscountBase2,0))) As SellingAmount, ".
					" if(upper($curr)='".$Base1."', ifNull(Deliveries.Delivery_VATBase1,0), ifNull(Deliveries.Delivery_VATBase2,0)) As VatAmount, ".
					" if(upper($curr)='".$Base1."', (ifNull(Deliveries.Delivery_TaxableBase1,0) + ifNull(Deliveries.Delivery_NoneTaxableBase1,0) + ifNull(Deliveries.Delivery_VATBase1,0) - ifNull(Deliveries.Delivery_DiscountBase1,0)), ".
					"     (ifNull(Deliveries.Delivery_TaxableBase2,0) + ifNull(Deliveries.Delivery_NoneTaxableBase2,0) + ifNull(Deliveries.Delivery_VATBase2,0) - ifNull(Deliveries.Delivery_DiscountBase2,0))) As SellingAmountVat ".
					" From DeliveryDt  ".
					" Inner Join Deliveries on DeliveryDt.Delivery_ID = Deliveries.Delivery_ID ".
					" Inner Join Clients on Clients.Ledger_Number = Deliveries.Client_Code ";

	if ($projectexist >	0 && $FI_RepRestrictProject == 1)
		$query= $query.$QueryRestrictPrj." on Deliveries.project_code like concat(Q4.project_code,'%') ";
								 
	if ($costcentexist > 0 && $FI_RepRestrictCostcenter == 1)
		$query= $query.$QueryRestrictCst." on Deliveries.Costcent_Code like concat(Q5.costcent_code,'%') ";		 

	$query= $query.	" Where (Deliveries.Delivery_Reference Is Not Null And Deliveries.Delivery_Reference != '')  ".
					" And Deliveries.Journal_Type in ('DI','DR') ".
					" And Date_format(Deliveries.Delivery_Date,'%Y-%m-%d') between ifNull(". $fromdate.",date_format(Deliveries.Delivery_Date,'%Y-%m-%d')) and ifNull(". $todate.",date_format(Deliveries.Delivery_Date,'%Y-%m-%d'))  ".
					" And ifNull(Deliveries.Client_Code,'-1') between ifNull(".$fromclient.",ifNull(Deliveries.Client_Code,'-1')) and ifNull(". $toclient.",ifNull(Deliveries.Client_Code,'-1')) ";
	  

	$query= $query." Group By TransRef, ClientCode ".
				   " Order by TransRef, ClientCode  ";
	$query= $query." ) Q1 ";
	$query= $query." 	Group By ClientCode ".
				   " 	Order by ClientCode  ";
	
	
	
}


$query= $query." ) Q2 ";
$query= $query."Order by ClientCode  ";				
	
//echo $query; 
//exit;	

$RowNum    = 13;
$ColumnNum = 0;	

$result=mysql_query($query,$connection) or die(mysql_error());
while($rows=mysql_fetch_row($result)){
			
	$worksheet->write($RowNum, $ColumnNum, $rows[2], $regularleftFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $rows[3], $regularleftFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, number_format($rows[6], $InventoryRMaskqty), $regularrightFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, format_currency($rows[7], $DecimalBase), $regularrightFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, format_currency($rows[8], $DecimalBase), $regularrightFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, format_currency($rows[9], $DecimalBase), $regularrightFormat);
	$ColumnNum += 1;
	
	if ($reports == 1) {
		$worksheet->write($RowNum, $ColumnNum, $rows[5], $regularrightFormat);
		$ColumnNum += 1;
		$worksheet->write($RowNum, $ColumnNum, format_currency($rows[11], $DecimalBase), $regularrightFormat);
		$ColumnNum += 1;
	}
	else if ($reports == 2){
		$worksheet->write($RowNum, $ColumnNum, $rows[4], $regularrightFormat);
		$ColumnNum += 1;
		$worksheet->write($RowNum, $ColumnNum, format_currency($rows[10], $DecimalBase), $regularrightFormat);
		$ColumnNum += 1;
	}


	//Increment the Rows Counter By 1 and Reset the Columns Counter
	$RowNum += 1;
	$ColumnNum = 0;
	
}


	

function format_currency($amount, $DecimalBase) { 

    if($amount < 0) 
        return "(".number_format(abs($amount),$DecimalBase).")"; 
 
    else return number_format($amount, $DecimalBase); 
} 



function current_server_time($connection){

  $string="Select date_format(NOW(),'%Y-%m-%d %h:%i %p') ";
  $result=mysql_query($string,$connection) or die(mysql_error());
  $now=mysql_fetch_row($result);

  return $now;
}

function explodeword($date)
{

$trim = explode('/',$date);
$trim = "$trim[2]"."-"."$trim[1]"."-"."$trim[0]";
return $trim;
}

function GetSQLValueString($theValue, $theType) 
{
  $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "Null";
      break;    
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "Null";
      break;
    case "double":
      $theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "Null";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "Null";
      break;
  }
  return $theValue;
}



$workbook->close();

?>