<?php
include("config.inc.php");
require_once('Spreadsheet/Excel/Writer.php');

session_start();
if($HTTP_SESSION_VARS['dbName']==""){
	header("location:../index.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 DecimalBase, DecimalBase1, DecimalBase2, 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);
} 

//Read From SalesSetup Table
$querySalesSetup="Select SalesTTC From SalesSetup";
$resultSalesSetup=mysql_query($querySalesSetup,$connection) or die(mysql_error());
while($rowSalesSetup=mysql_fetch_array($resultSalesSetup)){
	extract($rowSalesSetup);
}

//Read From Module Table
$querymodule=" Select ifNull(Module_Delivery,0) As Module_Delivery, ifNull(Module_AcctrxPostRV,0) As Module_AcctrxPostRV, ".
			 " ifNull(Module_AcctrxPostCV,0) As Module_AcctrxPostCV ".
			 " 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);	


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


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

$dispagingdate = $agingdate;



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

//Sending HTTP headers
$workbook->send('Exp_AgingClientInvoices.xls');
 
//Creating a worksheet
$worksheet=&$workbook->addWorksheet('Aging of Client Invoices');
$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();


$regularrightFormat->setNumFormat('_(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)');
$boldrightFormat->setNumFormat('_(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)');

$SelectedOptions = "Filter by: \n";
if ($fromdate != '')      $SelectedOptions .= "-> Date From: ". $fromdate . "  To: ". $todate ."\n";	
if ($agingdate != '')     $SelectedOptions .= "-> Aging Date: ". $agingdate ."\n";	
if ($displaycur != '')    $SelectedOptions .= "-> Currency: ". $displaycur ."\n";	
if ($fromledger != '')    $SelectedOptions .= "-> Ledger From: ". $fromledger . "  To: ". $toledger ."\n";

  
if (!empty($fromdate))  $fromdate = explodeword($fromdate);
if (!empty($todate))    $todate = explodeword($todate);  
if (!empty($agingdate)) $agingdate = explodeword($agingdate);  
   
$fromledger    = GetSQLValueString($fromledger, 'text');
$toledger      = GetSQLValueString($toledger, 'text');
$fromdate  	   = GetSQLValueString($fromdate, 'date');
$todate        = GetSQLValueString($todate, 'date');     
$agingdate     = GetSQLValueString($agingdate, 'date');    

$fromproject   = GetSQLValueString($fromproject, 'text');
$fromcst   	   = GetSQLValueString($fromcst, 'text'); 

if(empty($prtreturn))    $prtreturn = 0;
if(empty($prtclosedinv)) $prtclosedinv = 0;
if(empty($prtdesc)) 	 $prtdesc = 0;

if($prtreturn == 1) $Label = 'Invoice/Return';
else $Label = 'Invoice';
	
//Display Report Header
$worksheet->write(1,0,$CompanyName, $TitleFormat);
$worksheet->write(1,6,"Aging of Client Invoices", $TitleFormat);
               
$worksheet->write(1,11,"Printed Date:", $boldleftFormat);
$worksheet->write(1,12,$printdate[2]."/".$printdate[1]."/".$printdate[0], $regularleftFormat);
$worksheet->write(2,11,"Time:", $boldleftFormat);		
$worksheet->write(2,12,$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->setMerge($RowNum, $ColumnNum, $RowNum, $ColumnNum + 4);
$worksheet->write($RowNum,$ColumnNum,$Label, $boldcenterFormat);
$ColumnNum += 5;
$worksheet->setMerge($RowNum, $ColumnNum, $RowNum, $ColumnNum + 4);
$worksheet->write($RowNum,$ColumnNum,"Receipt/Credit Note", $boldcenterFormat);
$ColumnNum += 5;
$worksheet->setMerge($RowNum, $ColumnNum, $RowNum, $ColumnNum + 3);
$worksheet->write($RowNum,$ColumnNum,"Return Invoice", $boldcenterFormat);
$ColumnNum += 4;
$worksheet->setMerge($RowNum, $ColumnNum, $RowNum, $ColumnNum + 1);
$worksheet->write($RowNum,$ColumnNum,"Remaining up to ".$dispagingdate, $boldcenterFormat);
$ColumnNum += 2;

$RowNum += 1;
$ColumnNum = 0;	

$worksheet->setColumn ($ColumnNum, $ColumnNum, 12);
$worksheet->write($RowNum,$ColumnNum,"Date", $boldleftFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 12);
$worksheet->write($RowNum,$ColumnNum,"Value Date", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 10);
$worksheet->write($RowNum,$ColumnNum,"Reference", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 8);
$worksheet->write($RowNum,$ColumnNum,"Type", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
$worksheet->write($RowNum,$ColumnNum,"Amount", $boldcenterFormat);
$ColumnNum += 1;

$worksheet->setColumn ($ColumnNum, $ColumnNum, 12);
$worksheet->write($RowNum,$ColumnNum,"Date", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 10);
$worksheet->write($RowNum,$ColumnNum,"Reference", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 8);
$worksheet->write($RowNum,$ColumnNum,"Type", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
$worksheet->write($RowNum,$ColumnNum,"Paid Amount", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 8);
$worksheet->write($RowNum,$ColumnNum,"Days", $boldcenterFormat);
$ColumnNum += 1;

$worksheet->setColumn ($ColumnNum, $ColumnNum, 12);
$worksheet->write($RowNum,$ColumnNum,"Date", $boldleftFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 10);
$worksheet->write($RowNum,$ColumnNum,"Reference", $boldleftFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
$worksheet->write($RowNum,$ColumnNum,"Return Amount", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 8);
$worksheet->write($RowNum,$ColumnNum,"Days", $boldcenterFormat);
$ColumnNum += 1;

$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
$worksheet->write($RowNum,$ColumnNum,"Amount", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 8);
$worksheet->write($RowNum,$ColumnNum,"Days", $boldcenterFormat);
$ColumnNum += 1;
	  
	  
$MainCompanyName=explode('_',$HTTP_SESSION_VARS['dbName']);  
$QueryDir = " Select Concat(CompanyName, '_', DirYear) As DirSchema, Date_Format(StartFinancialDate,'%Y-%m-%d') As StartFinancialDate, Date_Format(EndFinancialDate,'%Y-%m-%d') As EndFinancialDate, CompanyName, DirYear ".
			" From ".$HTTP_SESSION_VARS['dbLabel'].".Directories ". 
			" Where Upper(CompanyName) = Upper('".$MainCompanyName[0]."') ".
			" And ( (ifNull(Date_Format(". $fromdate.",'%Y%m'),Date_Format(StartFinancialDate,'%Y%m')) >= Date_Format(StartFinancialDate,'%Y%m') and ifNull(Date_Format(". $todate.",'%Y%m'),Date_Format(EndFinancialDate,'%Y%m')) <= Date_Format(EndFinancialDate,'%Y%m')) or   ".
			//"  		(ifNull(Date_Format(". $fromdate.",'%Y%m'),Date_Format(StartFinancialDate,'%Y%m')) <= Date_Format(StartFinancialDate,'%Y%m') and ifNull(Date_Format(". $todate.",'%Y%m'),Date_Format(EndFinancialDate,'%Y%m')) >= Date_Format(EndFinancialDate,'%Y%m')) or ".
			"       (Date_Format(StartFinancialDate,'%Y%m') >= ifNull(Date_Format(". $fromdate.",'%Y%m'),Date_Format(StartFinancialDate,'%Y%m')) and Date_Format(StartFinancialDate,'%Y%m') <= ifNull(Date_Format(". $todate.",'%Y%m'),Date_Format(EndFinancialDate,'%Y%m')) ) or ".
            "       (Date_Format(EndFinancialDate,'%Y%m') >= ifNull(Date_Format(". $fromdate.",'%Y%m'),Date_Format(StartFinancialDate,'%Y%m')) and Date_Format(EndFinancialDate,'%Y%m') <= ifNull(Date_Format(". $todate.",'%Y%m'),Date_Format(EndFinancialDate,'%Y%m')) ) ".
			"      )  ".
			" Order By DirYear ";
			
//echo $QueryDir; exit;
$ResultDir=mysql_query($QueryDir,$connection) or die(mysql_error());
$ResultDir1=mysql_query($QueryDir,$connection) or die(mysql_error());
$RowsDirCount = mysql_num_rows($ResultDir);

 
$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 != '') ".
					"			 ) QP  ";
					
$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 != '') ".
					"			 ) QC  ";
					
					
$query = '';
$query = $query." Select ClientCode, ClientName, SalesDate, SalesValueDate, SalesRefer, SalesTotalBase1, SalesTotalBase2, ".
				" TransDate, TransRefer, TransPaidBase1, TransPaidBase2, PaidDays, ReturnDate, ReturnRefer, ReturnAmtBase1, ReturnAmtBase2, ReturnDays, ".
				" TransRemBase1, TransRemBase2, RemainDays, OrderSalesDate, OrderTransDate, OrderReturnDate, Q2.PrevAmount1, Q2.PrevAmount2, CreditLimits, ".
				" PaymentTerms, TransDtDescription, AccID, LinkType, TransType ";
	
$query = $query." From ( ";

$j = 0;				
$k = 0;

				
// Reading Data For Many Years Or Different Directories					 
while($RowsDir=mysql_fetch_row($ResultDir)){

    $j = $j + 1;    
	//if ($RowsDirCount > 1)
	   $DirSchema   = $RowsDir[0];
	   $DirFromDate = $RowsDir[1];
	   $DirToDate   = $RowsDir[2];
	   $DirCompany  = $RowsDir[3];
	   $DirYear	    = $RowsDir[4];
	   
	//else
       //$DirSchema = '';	
    
	if ($RowsDirCount > 1) {
		$QuerySchemaExists = "Show Schemas where `Database` = '".$DirSchema."'  ";   
		$ResultSchemaExists=mysql_query($QuerySchemaExists,$connection) or die(mysql_error());
		$SchemaExists = mysql_num_rows($ResultSchemaExists);
		
	}
	
	if (($RowsDirCount > 1 && $SchemaExists > 0) || ($RowsDirCount == 1) ) {
	
		$k = $k + 1; 

		if($Module_Delivery == 0 ) {
			$query = $query." Select Deliveries.Delivery_Id As InvoiceId, Deliveries.Client_Code As ClientCode, ledger.Ledger_name As ClientName, date_format(Deliveries.Delivery_Date,'%d/%m/%Y') As SalesDate, ".
							" date_format(Deliveries.Journaldt_ValueDate,'%d/%m/%Y') As SalesValueDate, Deliveries.Delivery_Reference As SalesRefer, ".
							" ifNull(AccLink.Sale_NetTotalBase1,0) As SalesTotalBase1, ifNull(AccLink.Sale_NetTotalBase2,0) As SalesTotalBase2, ".
							" date_format(AccLink.Trans_Date,'%d/%m/%Y') As TransDate, AccLink.Trans_Ref As TransRefer, ".
							" ifNull(AccLink.Sale_PaidAmntBase1,0) As TransPaidBase1, ifNull(AccLink.Sale_PaidAmntBase2,0) As TransPaidBase2, ".
							" DATEDIFF(date_format(if($dateoption = 1, AccLink.Trans_Date, AccLink.Trans_ValueDate),'%Y-%m-%d'), date_format(if($dateoption = 1, Deliveries.Delivery_Date, Deliveries.Journaldt_ValueDate),'%Y-%m-%d')) PaidDays, ".
							" date_format(AccLink.Return_Date,'%d/%m/%Y') As ReturnDate, AccLink.Return_Ref As ReturnRefer, ".
							" ifNull(AccLink.Sale_ReturnAmntbase1,0) As ReturnAmtBase1, ifNull(AccLink.Sale_ReturnAmntbase2,0) As ReturnAmtBase2, ".
							" DATEDIFF(date_format(if($dateoption = 1, AccLink.Return_Date, AccLink.Return_ValueDate),'%Y-%m-%d'), date_format(if($dateoption = 1, Deliveries.Delivery_Date, Deliveries.Journaldt_ValueDate),'%Y-%m-%d')) ReturnDays, ".
							" ifNull(AccLink.Sale_RemainAmntBase1,0) As TransRemBase1, ifNull(AccLink.Sale_RemainAmntBase2,0) As TransRemBase2, ".
							" DATEDIFF(date_format(".$agingdate.",'%Y-%m-%d'), date_format(if($dateoption = 1, AccLink.Trans_Date, AccLink.Trans_ValueDate),'%Y-%m-%d')) RemainDays, ".
							" Deliveries.Delivery_Date As OrderSalesDate, AccLink.Trans_Date As OrderTransDate, AccLink.Return_Date As OrderReturnDate, Clients.Client_CreditLimit As CreditLimits, Client_PaymentTerms As PaymentTerms, ".
							" AccLink.Trans_Description As TransDtDescription, Acclink.Acc_ID As AccID, Acclink.Link_Type As LinkType, Acclink.Journal_Type As TransType ".
							" From $DirSchema.Deliveries ".
							" Inner Join ".$HTTP_SESSION_VARS['dbLabel'].".Acclink on Deliveries.Delivery_Id = Acclink.Link_ID ".
							//" Left Join $DirSchema.TransDt on Acclink.Journal_Id = TransDt.Transdt_Id ".
							//" Left Join $DirSchema.Trans on TransDt.Trans_Id = Trans.Trans_Id And Trans.Trans_Type In ('RV','CV') ".
							//" Left Join $DirSchema.Deliveries As RDeliveries on RDeliveries.Delivery_Id = Acclink.Return_ID and RDeliveries.Journal_Type = 'DR' ".
							" Inner Join $DirSchema.Ledger on Deliveries.Client_Code = Ledger.ledger_Number ".
							" Inner join $DirSchema.Clients on Deliveries.Client_Code = Clients.Ledger_number ";
							
			if ($prtclosedinv == 0) {
				$query = $query." Inner Join ( Select Link_ID, Link_Ref, Link_Type, (ifNull(AccLink.Sale_NetTotalBase1,0) - Sum(ifNull(AccLink.Sale_PaidAmntBase1,0) + ifNull(AccLink.Sale_ReturnAmntbase1,0))) As TransRemBase1 ".
								" 			   From ".$HTTP_SESSION_VARS['dbLabel'].".Acclink ".
								" 			   Where Acclink.Link_Type In ('DI','SI') ".
								" 			   And upper(Acclink.CompanyName) = upper('".$DirCompany."') ".
								" 			   And upper(Acclink.Link_DirYear) = upper('".$DirYear."') ".
								"              Group By Link_ID, Link_Type ".
								"    		   Having TransRemBase1 > 0 ".
								"           ) as QRem on Deliveries.Delivery_Id = QRem.Link_ID ";
			
			}
			if ($projectexist >	0 && $FI_RepRestrictProject == 1)
				$query = $query.$QueryRestrictPrj." on Deliveries.project_code like concat(QP.project_code,'%') ";
											 
			if ($costcentexist > 0 && $FI_RepRestrictCostcenter == 1)
				$query = $query.$QueryRestrictCst." on Deliveries.costcent_code like concat(QC.costcent_code,'%') ";
				
			$query = $query." Where Date_format(Deliveries.Delivery_Date,'%Y-%m-%d') between ifNull('".$DirFromDate."',date_format(Deliveries.Delivery_Date,'%Y-%m-%d')) and ifNull('".$DirToDate."',date_format(Deliveries.Delivery_Date,'%Y-%m-%d'))  ".
						    " 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 Deliveries.Client_Code between ifNull(".$fromledger.",Deliveries.Client_Code) and ifNull(". $toledger.",Deliveries.Client_Code) ".
							" And Deliveries.Journal_Type = 'DI' ".
							" And IfNull(Deliveries.Delivery_OldData,0) = 0 ".
							" And Acclink.Link_Type In ('DI','SI') ".
							" And upper(Acclink.CompanyName) = upper('".$DirCompany."') ".
							" And upper(Acclink.Link_DirYear) = upper('".$DirYear."') ".
							" And upper(ifNull(Deliveries.project_code,'-1')) like ifNull(upper(".$fromproject."), upper(ifNull(Deliveries.project_code,'-1'))) ".
							" And upper(ifNull(Deliveries.Costcent_Code,'-1')) like ifNull(upper(".$fromcst."), upper(ifNull(Deliveries.Costcent_Code,'-1'))) ".
							//" And upper(ifNull(Trans.project_code,'-1')) like ifNull(upper(".$fromproject."), upper(ifNull(Trans.project_code,'-1'))) ".
							//" And upper(ifNull(Trans.Costcent_Code,'-1')) like ifNull(upper(".$fromcst."), upper(ifNull(Trans.Costcent_Code,'-1'))) ".
							" Union ".
							" Select Deliveries.Delivery_Id As InvoiceId, Deliveries.Client_Code As ClientCode, ledger.Ledger_name As ClientName, date_format(Deliveries.Delivery_Date,'%d/%m/%Y') As SalesDate, ".
							" date_format(Deliveries.Journaldt_ValueDate,'%d/%m/%Y') As SalesValueDate, Deliveries.Delivery_Reference As SalesRefer, ".
							" (ifNull(Deliveries.Delivery_TotalBase1,0) - ifNull(Deliveries.Delivery_DiscountBase1,0) + ifNull(Deliveries.Delivery_VatBase1,0)) As SalesTotalBase1, ".
							" (ifNull(Deliveries.Delivery_TotalBase2,0) - ifNull(Deliveries.Delivery_DiscountBase2,0) + ifNull(Deliveries.Delivery_VatBase2,0)) As SalesTotalBase2, ".
							" Null As TransDate, Null As TransRefer, 0 As TransPaidBase1, 0 As TransPaidBase2, 0 PaidDays, ".
							" Null As ReturnDate, Null As ReturnRefer, 0 As ReturnAmtBase1, 0 As ReturnAmtBase2, 0 ReturnDays, ".
							" (ifNull(Deliveries.Delivery_TotalBase1,0) - ifNull(Deliveries.Delivery_DiscountBase1,0) + ifNull(Deliveries.Delivery_VatBase1,0)) As TransRemBase1, ".
							" (ifNull(Deliveries.Delivery_TotalBase2,0) - ifNull(Deliveries.Delivery_DiscountBase2,0) + ifNull(Deliveries.Delivery_VatBase2,0)) As TransRemBase2, ".
							" DATEDIFF(date_format(".$agingdate.",'%Y-%m-%d'), date_format(if($dateoption = 1, Deliveries.Delivery_Date, Deliveries.Journaldt_ValueDate),'%Y-%m-%d')) RemainDays, ".
							" Deliveries.Delivery_Date As OrderSalesDate, Null As OrderTransDate, Null As OrderReturnDate, Clients.Client_CreditLimit As CreditLimits, Client_PaymentTerms As PaymentTerms, ".
							" Null as TransDtDescription, Null As AccID, 'SI' As LinkType, Null As TransType ".
							" From $DirSchema.Deliveries ".
							" Inner Join $DirSchema.Ledger on Deliveries.Client_Code = Ledger.ledger_Number ".
							" Inner join $DirSchema.Clients on Deliveries.Client_Code = Clients.Ledger_number ";
							
			if ($projectexist >	0 && $FI_RepRestrictProject == 1)
				$query = $query.$QueryRestrictPrj." on Deliveries.project_code like concat(QP.project_code,'%') ";
											 
			if ($costcentexist > 0 && $FI_RepRestrictCostcenter == 1)
				$query = $query.$QueryRestrictCst." on Deliveries.costcent_code like concat(QC.costcent_code,'%') ";				
				
			$query = $query." Where Date_format(Deliveries.Delivery_Date,'%Y-%m-%d') between ifNull('".$DirFromDate."',date_format(Deliveries.Delivery_Date,'%Y-%m-%d')) and ifNull('".$DirToDate."',date_format(Deliveries.Delivery_Date,'%Y-%m-%d'))  ".
							" 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 Deliveries.Client_Code between ifNull(".$fromledger.",Deliveries.Client_Code) and ifNull(". $toledger.",Deliveries.Client_Code) ".
							" And Deliveries.Journal_Type = 'DI' ".
							" And IfNull(Deliveries.Delivery_OldData,0) = 0 ".
							" And Deliveries.Delivery_Id Not In (Select ifNull(Link_ID,-1) From ".$HTTP_SESSION_VARS['dbLabel'].".Acclink  ".
							"                          		 	 Where Acclink.Link_Type In ('DI','SI') ".
							" 								 	 And upper(Acclink.Link_DirYear) = upper('".$DirYear."') ".
							"                                	 And upper(Acclink.CompanyName) = upper('".$DirCompany."') ) ".
							" And upper(ifNull(Deliveries.project_code,'-1')) like ifNull(upper(".$fromproject."), upper(ifNull(Deliveries.project_code,'-1'))) ".
							" And upper(ifNull(Deliveries.Costcent_Code,'-1')) like ifNull(upper(".$fromcst."), upper(ifNull(Deliveries.Costcent_Code,'-1'))) ";
		} else {
			$query = $query." Select Sales.Sale_Id As InvoiceId, Sales.Client_Code As ClientCode, ledger.Ledger_name As ClientName, date_format(Sales.Sale_Date,'%d/%m/%Y') As SalesDate, ".
							" date_format(Sales.Journaldt_ValueDate,'%d/%m/%Y') As SalesValueDate, Sales.Sale_Reference As SalesRefer, ".
							" ifNull(AccLink.Sale_NetTotalBase1,0) As SalesTotalBase1, ifNull(AccLink.Sale_NetTotalBase2,0) As SalesTotalBase2, ".
							" date_format(Acclink.Trans_Date,'%d/%m/%Y') As TransDate, Acclink.Trans_Ref As TransRefer, ".
							" ifNull(AccLink.Sale_PaidAmntBase1,0) As TransPaidBase1, ifNull(AccLink.Sale_PaidAmntBase2,0) As TransPaidBase2, ".
							" DATEDIFF(date_format(if($dateoption = 1, Acclink.Trans_Date, Acclink.Trans_ValueDate),'%Y-%m-%d'), date_format(if($dateoption = 1, Sales.Sale_Date, Sales.Journaldt_ValueDate),'%Y-%m-%d')) PaidDays, ".
							" date_format(Acclink.Return_Date,'%d/%m/%Y') As ReturnDate, Acclink.Return_Ref As ReturnRefer, ".
							" ifNull(AccLink.Sale_ReturnAmntbase1,0) As ReturnAmtBase1, ifNull(AccLink.Sale_ReturnAmntbase2,0) As ReturnAmtBase2, ".
							" DATEDIFF(date_format(if($dateoption = 1, Acclink.Return_Date, Acclink.Return_ValueDate),'%Y-%m-%d'), date_format(if($dateoption = 1, Sales.Sale_Date, Sales.Journaldt_ValueDate),'%Y-%m-%d')) ReturnDays, ".
							" ifNull(AccLink.Sale_RemainAmntBase1,0) As TransRemBase1, ifNull(AccLink.Sale_RemainAmntBase2,0) As TransRemBase2, ".
							" DATEDIFF(date_format(".$agingdate.",'%Y-%m-%d'), date_format(if($dateoption = 1, Acclink.Trans_Date, Acclink.Trans_ValueDate),'%Y-%m-%d')) RemainDays, ".
							" Sales.Sale_Date As OrderSalesDate, Acclink.Trans_Date As OrderTransDate, Sales.Sale_Date As OrderReturnDate, Clients.Client_CreditLimit As CreditLimits, Client_PaymentTerms As PaymentTerms, ".
							" Acclink.Trans_Description As TransDtDescription, Acclink.Acc_ID As AccID, Acclink.Link_Type As LinkType, Acclink.Journal_Type As TransType ".
							" From $DirSchema.Sales ".
							" Inner Join ".$HTTP_SESSION_VARS['dbLabel'].".Acclink on Sales.Sale_Id = Acclink.Link_ID ".
							//" Left Join $DirSchema.TransDt on Acclink.Journal_Id = TransDt.Transdt_Id ".
							//" Left Join $DirSchema.Trans on TransDt.Trans_Id = Trans.Trans_Id And Trans.Trans_Type In ('RV','CV') ".
							//" Left Join $DirSchema.Sales As RSales on RSales.Sale_Id = Acclink.Return_ID and RSales.Journal_Type = 'SR' ".
							" Inner Join $DirSchema.Ledger on Sales.Client_Code = Ledger.ledger_Number ".
							" Inner join $DirSchema.Clients on Sales.Client_Code = Clients.Ledger_number ";
							
			if ($prtclosedinv == 0) {
				$query = $query." Inner Join ( Select Link_ID, Link_Ref, Link_Type, (ifNull(AccLink.Sale_NetTotalBase1,0) - Sum(ifNull(AccLink.Sale_PaidAmntBase1,0) + ifNull(AccLink.Sale_ReturnAmntbase1,0))) As TransRemBase1 ".
								" 			   From ".$HTTP_SESSION_VARS['dbLabel'].".Acclink ".
								" 			   Where Acclink.Link_Type In ('DI','SI') ".
								" 			   And upper(Acclink.CompanyName) = upper('".$DirCompany."') ".
								" 			   And upper(Acclink.Link_DirYear) = upper('".$DirYear."') ".
								"              Group By Link_ID, Link_Type ".
								"    		   Having TransRemBase1 > 0 ".
								"           ) as QRem on Sales.Sale_Id = QRem.Link_ID ";
			
			}				
							
			if ($projectexist >	0 && $FI_RepRestrictProject == 1)
				$query = $query.$QueryRestrictPrj." on Sales.project_code like concat(QP.project_code,'%') ";
											 
			if ($costcentexist > 0 && $FI_RepRestrictCostcenter == 1)
				$query = $query.$QueryRestrictCst." on Sales.Costcenter_code like concat(QC.costcent_code,'%') ";	
				
			$query = $query." Where Date_format(Sales.Sale_Date,'%Y-%m-%d') between ifNull('".$DirFromDate."',date_format(Sales.Sale_Date,'%Y-%m-%d')) and ifNull('".$DirToDate."',date_format(Sales.Sale_Date,'%Y-%m-%d'))  ".
							" 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 Sales.Client_Code between ifNull(".$fromledger.",Sales.Client_Code) and ifNull(". $toledger.",Sales.Client_Code) ".
							" And Sales.Journal_Type = 'SI' ".
							" And IfNull(Sales.Sale_OldData,0) = 0 ".
							" And Acclink.Link_Type In ('DI','SI') ".
							" And upper(Acclink.CompanyName) = upper('".$DirCompany."') ".
							" And upper(Acclink.Link_DirYear) = upper('".$DirYear."') ".
							" And upper(ifNull(Sales.project_code,'-1')) like ifNull(upper(".$fromproject."), upper(ifNull(Sales.project_code,'-1'))) ".
							" And upper(ifNull(Sales.Costcenter_code,'-1')) like ifNull(upper(".$fromcst."), upper(ifNull(Sales.Costcenter_code,'-1'))) ".
							//" And upper(ifNull(Trans.project_code,'-1')) like ifNull(upper(".$fromproject."), upper(ifNull(Trans.project_code,'-1'))) ".
							//" And upper(ifNull(Trans.Costcent_Code,'-1')) like ifNull(upper(".$fromcst."), upper(ifNull(Trans.Costcent_Code,'-1'))) ".
							" Union ".
							" Select Sales.Sale_Id As InvoiceId, Sales.Client_Code As ClientCode, ledger.Ledger_name As ClientName, date_format(Sales.Sale_Date,'%d/%m/%Y') As SalesDate, ".
							" date_format(Sales.Journaldt_ValueDate,'%d/%m/%Y') As SalesValueDate, Sales.Sale_Reference As SalesRefer, ".
							" (ifNull(Sales.Sale_TotalBase1,0) - ifNull(Sales.Sale_DiscountBase1,0) + ifNull(Sales.Sale_VATBase1,0)) As SalesTotalBase1, ".
							" (ifNull(Sales.Sale_TotalBase2,0) - ifNull(Sales.Sale_DiscountBase2,0) + ifNull(Sales.Sale_VATBase2,0)) As SalesTotalBase2, ".
							" Null As TransDate, Null As TransRefer, 0 As TransPaidBase1, 0 As TransPaidBase2, 0 PaidDays, ".
							" Null As ReturnDate, Null As ReturnRefer, 0 As ReturnAmtBase1, 0 As ReturnAmtBase2, 0 ReturnDays, ".
							" (ifNull(Sales.Sale_TotalBase1,0) - ifNull(Sales.Sale_DiscountBase1,0) + ifNull(Sales.Sale_VATBase1,0)) As TransRemBase1, ".
							" (ifNull(Sales.Sale_TotalBase2,0) - ifNull(Sales.Sale_DiscountBase2,0) + ifNull(Sales.Sale_VATBase2,0)) As TransRemBase2, ".
							" DATEDIFF(date_format(".$agingdate.",'%Y-%m-%d'), date_format(if($dateoption = 1, Sales.Sale_Date, Sales.Journaldt_ValueDate),'%Y-%m-%d')) RemainDays, ".
							" Sales.Sale_Date As OrderSalesDate, Null As OrderTransDate, Null As OrderReturnDate, Clients.Client_CreditLimit As CreditLimits, Client_PaymentTerms As PaymentTerms, ".
							" Null as TransDtDescription, Null As AccID, 'SI' As LinkType, Null As TransType ".
							" From $DirSchema.Sales ".
							" Inner Join $DirSchema.Ledger on Sales.Client_Code = Ledger.ledger_Number ".
							" Inner join $DirSchema.Clients on Sales.Client_Code = Clients.Ledger_number ";
							
			if ($projectexist >	0 && $FI_RepRestrictProject == 1)
				$query = $query.$QueryRestrictPrj." on Sales.project_code like concat(QP.project_code,'%') ";
											 
			if ($costcentexist > 0 && $FI_RepRestrictCostcenter == 1)
				$query = $query.$QueryRestrictCst." on Sales.Costcenter_code like concat(QC.costcent_code,'%') ";	
				
			$query = $query." Where Date_format(Sales.Sale_Date,'%Y-%m-%d') between ifNull('".$DirFromDate."',date_format(Sales.Sale_Date,'%Y-%m-%d')) and ifNull('".$DirToDate."',date_format(Sales.Sale_Date,'%Y-%m-%d'))  ".
							" 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 Sales.Client_Code between ifNull(".$fromledger.",Sales.Client_Code) and ifNull(". $toledger.",Sales.Client_Code) ".
							" And Sales.Journal_Type = 'SI' ".
							" And IfNull(Sales.Sale_OldData,0) = 0 ".
							" And Sales.Sale_Id Not In (Select ifNull(Link_ID,-1) From ".$HTTP_SESSION_VARS['dbLabel'].".Acclink  ".
							"                           Where Acclink.Link_Type In ('DI','SI') ".
							" 							And upper(Acclink.Link_DirYear) = upper('".$DirYear."') ".
							"                           And upper(Acclink.CompanyName) = upper('".$DirCompany."') ) ".
							" And upper(ifNull(Sales.project_code,'-1')) like ifNull(upper(".$fromproject."), upper(ifNull(Sales.project_code,'-1'))) ".
							" And upper(ifNull(Sales.Costcenter_code,'-1')) like ifNull(upper(".$fromcst."), upper(ifNull(Sales.Costcenter_code,'-1'))) ";

		}
		
		
		//Debit Notes
		$query = $query." Union ".
					    " Select JournalDt.JournalDt_Id As InvoiceId, JournalDt.Ledger_number As ClientCode, ledger.Ledger_name As ClientName, date_format(Journal.Journal_Date,'%d/%m/%Y') As SalesDate, ".
						" date_format(JournalDt.Journaldt_ValueDate,'%d/%m/%Y') As SalesValueDate, Journal.Journal_refgovernment As SalesRefer, ".
						" ifNull(Journaldt_amount1,0) As SalesTotalBase1, ifNull(Journaldt_amount2,0) As SalesTotalBase2, ".
						" date_format(Acclink.Trans_Date,'%d/%m/%Y') As TransDate, Acclink.Trans_Ref As TransRefer, ".
						" ifNull(AccLink.Sale_PaidAmntBase1,0) As TransPaidBase1, ifNull(AccLink.Sale_PaidAmntBase2,0) As TransPaidBase2, ".
						" DATEDIFF(date_format(if($dateoption = 1, Acclink.Trans_Date, Acclink.Trans_ValueDate),'%Y-%m-%d'), date_format(if($dateoption = 1, Journal.Journal_Date, JournalDt.Journaldt_ValueDate),'%Y-%m-%d')) PaidDays, ".
						" date_format(Acclink.Return_Date,'%d/%m/%Y') As ReturnDate, Acclink.Return_Ref As ReturnRefer, ".
						" ifNull(AccLink.Sale_ReturnAmntbase1,0) As ReturnAmtBase1, ifNull(AccLink.Sale_ReturnAmntbase2,0) As ReturnAmtBase2, ".
						" DATEDIFF(date_format(if($dateoption = 1, Acclink.Return_Date, Acclink.Return_ValueDate),'%Y-%m-%d'), date_format(if($dateoption = 1, Journal.Journal_Date, JournalDt.Journaldt_ValueDate),'%Y-%m-%d')) ReturnDays, ".
						" ifNull(AccLink.Sale_RemainAmntBase1,0) As TransRemBase1, ifNull(AccLink.Sale_RemainAmntBase2,0) As TransRemBase2, ".
						" DATEDIFF(date_format(".$agingdate.",'%Y-%m-%d'), date_format(if($dateoption = 1, Acclink.Trans_Date, Acclink.Trans_ValueDate),'%Y-%m-%d')) RemainDays, ".
						" Journal.Journal_Date As OrderSalesDate, Acclink.Trans_Date As OrderTransDate, Acclink.Return_Date As OrderReturnDate, Clients.Client_CreditLimit As CreditLimits, Client_PaymentTerms As PaymentTerms, ".
						" Acclink.Trans_Description As TransDtDescription, Acclink.Acc_ID As AccID, Acclink.Link_Type As LinkType, Acclink.Journal_Type As TransType ".
						" From $DirSchema.JournalDt As JournalDt ".
						" Inner join $DirSchema.Journal As Journal on JournalDt.Journal_id = Journal.Journal_id ".
						" Inner Join ".$HTTP_SESSION_VARS['dbLabel'].".Acclink As Acclink on Acclink.Link_ID = JournalDt.JournalDt_Id And Acclink.Link_Type = Journal.Journal_Type ".
						//" Left Join $DirSchema.TransDt on Acclink.Journal_Id = TransDt.Transdt_Id ".
						//" Left Join $DirSchema.Trans on TransDt.Trans_Id = Trans.Trans_Id And Trans.Trans_Type In ('RV','CV') ".
						//" Left Join $DirSchema.Deliveries As RDeliveries on RDeliveries.Delivery_Id = Acclink.Return_ID and RDeliveries.Journal_Type = 'DR' ".
						" Inner Join $DirSchema.Ledger on JournalDt.ledger_Number = Ledger.ledger_Number ".
						" Inner join $DirSchema.Clients on JournalDt.ledger_Number = Clients.Ledger_number ";
						
		if ($prtclosedinv == 0) {
			$query = $query." Inner Join ( Select Link_ID, Link_Ref, Link_Type, (ifNull(AccLink.Sale_NetTotalBase1,0) - Sum(ifNull(AccLink.Sale_PaidAmntBase1,0) + ifNull(AccLink.Sale_ReturnAmntbase1,0))) As TransRemBase1 ".
							" 			   From ".$HTTP_SESSION_VARS['dbLabel'].".Acclink ".
							" 			   Where Acclink.Link_Type Not In ('DI','SI') ".
							" 			   And upper(Acclink.CompanyName) = upper('".$DirCompany."') ".
							" 			   And upper(Acclink.Link_DirYear) = upper('".$DirYear."') ".
							"              Group By Link_ID, Link_Type ".
							"    		   Having TransRemBase1 > 0 ".
							"           ) as QRem on JournalDt.JournalDt_Id = QRem.Link_ID ";
			
		}
		if ($projectexist >	0 && $FI_RepRestrictProject == 1)
			$query = $query.$QueryRestrictPrj." on Journal.project_code like concat(QP.project_code,'%') ";
											 
		if ($costcentexist > 0 && $FI_RepRestrictCostcenter == 1)
			$query = $query.$QueryRestrictCst." on Journal.costcent_code like concat(QC.costcent_code,'%') ";
				
		
		$query = $query." Where Date_format(Journal.Journal_Date,'%Y-%m-%d') between ifNull('".$DirFromDate."',date_format(Journal.Journal_Date,'%Y-%m-%d')) and ifNull('".$DirToDate."',date_format(Journal.Journal_Date,'%Y-%m-%d'))  ".
						" And Date_format(Journal.Journal_Date,'%Y-%m-%d') between ifNull(".$fromdate.",date_format(Journal.Journal_Date,'%Y-%m-%d')) and ifNull(".$todate.",date_format(Journal.Journal_Date,'%Y-%m-%d'))  ".
						" And JournalDt.Ledger_number between ifNull(".$fromledger.",JournalDt.Ledger_number) and ifNull(". $toledger.",JournalDt.Ledger_number) ".
						" And JournalDt.Journaldt_dbcd = 'D' ".
						" And Journal.Journal_Type Not In ('CV','SI','SR') ".
						" And IfNull(JournalDt.JournalDt_OldData,0) = 0 ".
						" And upper(Acclink.CompanyName) = upper('".$DirCompany."') ".
						" And upper(Acclink.Link_DirYear) = upper('".$DirYear."') ".
						" And upper(ifNull(Journal.project_code,'-1')) like ifNull(upper(".$fromproject."), upper(ifNull(Journal.project_code,'-1'))) ".
						" And upper(ifNull(Journal.Costcent_Code,'-1')) like ifNull(upper(".$fromcst."), upper(ifNull(Journal.Costcent_Code,'-1'))) ";
						//" And upper(ifNull(Trans.project_code,'-1')) like ifNull(upper(".$fromproject."), upper(ifNull(Trans.project_code,'-1'))) ".
						//" And upper(ifNull(Trans.Costcent_Code,'-1')) like ifNull(upper(".$fromcst."), upper(ifNull(Trans.Costcent_Code,'-1'))) ";
						
		if ($RowsDirCount > 1 && $k != 1)	
			$query = $query." And Journal.Journal_Type <> 'OP' ";
						
		$query = $query." Union ".
						" Select JournalDt.JournalDt_Id As InvoiceId, JournalDt.Ledger_number As ClientCode, ledger.Ledger_name As ClientName, date_format(Journal.Journal_Date,'%d/%m/%Y') As SalesDate, ".
						" date_format(JournalDt.Journaldt_ValueDate,'%d/%m/%Y') As SalesValueDate, Journal.Journal_refgovernment As SalesRefer, ".
						" ifNull(Journaldt_amount1,0) As SalesTotalBase1, ifNull(Journaldt_amount2,0) As SalesTotalBase2, ".
						" Null As TransDate, Null As TransRefer, 0 As TransPaidBase1, 0 As TransPaidBase2, 0 PaidDays, ".
						" Null As ReturnDate, Null As ReturnRefer, 0 As ReturnAmtBase1, 0 As ReturnAmtBase2, 0 ReturnDays, ".
						" ifNull(Journaldt_amount1,0) As TransRemBase1, ifNull(Journaldt_amount2,0) As TransRemBase2, ".
						" DATEDIFF(date_format(".$agingdate.",'%Y-%m-%d'), date_format(if($dateoption = 1, Journal.Journal_Date, JournalDt.Journaldt_ValueDate),'%Y-%m-%d')) RemainDays, ".
						" Journal.Journal_Date As OrderSalesDate, Null As OrderTransDate, Null As OrderReturnDate, Clients.Client_CreditLimit As CreditLimits, Client_PaymentTerms As PaymentTerms, ".
						" Null as TransDtDescription, Null As AccID, Journal.Journal_Type As LinkType, Null As TransType ".
						" From $DirSchema.JournalDt As JournalDt ".
						" Inner join $DirSchema.Journal As Journal on JournalDt.Journal_id = Journal.Journal_id ".
						" Inner Join $DirSchema.Ledger on JournalDt.ledger_Number = Ledger.ledger_Number ".
						" Inner join $DirSchema.Clients on JournalDt.ledger_Number = Clients.Ledger_number ";
							
		if ($projectexist >	0 && $FI_RepRestrictProject == 1)
			$query = $query.$QueryRestrictPrj." on Journal.project_code like concat(QP.project_code,'%') ";
											 
		if ($costcentexist > 0 && $FI_RepRestrictCostcenter == 1)
			$query = $query.$QueryRestrictCst." on Journal.costcent_code like concat(QC.costcent_code,'%') ";				
				
		$query = $query." Where Date_format(Journal.Journal_Date,'%Y-%m-%d') between ifNull('".$DirFromDate."',date_format(Journal.Journal_Date,'%Y-%m-%d')) and ifNull('".$DirToDate."',date_format(Journal.Journal_Date,'%Y-%m-%d'))  ".
						" And Date_format(Journal.Journal_Date,'%Y-%m-%d') between ifNull(".$fromdate.",date_format(Journal.Journal_Date,'%Y-%m-%d')) and ifNull(".$todate.",date_format(Journal.Journal_Date,'%Y-%m-%d'))  ".
						" And JournalDt.Ledger_number between ifNull(".$fromledger.",JournalDt.Ledger_number) and ifNull(". $toledger.",JournalDt.Ledger_number) ".
						" And JournalDt.Journaldt_dbcd = 'D' ".
						" And Journal.Journal_Type Not In ('CV','SI','SR') ".
						" And IfNull(JournalDt.JournalDt_OldData,0) = 0 ".
						" And JournalDt.JournalDt_Id Not In (Select ifNull(Link_ID,-1) From ".$HTTP_SESSION_VARS['dbLabel'].".Acclink  ".
						"                          		 	 Where Acclink.Link_Type Not In ('DI','SI') ".
						" 								 	 And upper(Acclink.Link_DirYear) = upper('".$DirYear."') ".
						"                                	 And upper(Acclink.CompanyName) = upper('".$DirCompany."') ) ".
						" And upper(ifNull(Journal.project_code,'-1')) like ifNull(upper(".$fromproject."), upper(ifNull(Journal.project_code,'-1'))) ".
						" And upper(ifNull(Journal.Costcent_Code,'-1')) like ifNull(upper(".$fromcst."), upper(ifNull(Journal.Costcent_Code,'-1'))) ";
						
		if ($RowsDirCount > 1 && $k != 1)	
			$query = $query." And Journal.Journal_Type <> 'OP' ";
		
		if ($RowsDirCount > 1 && $j < $RowsDirCount)	
            $query = $query." Union ";	
	}	
}
				
$query = $query." ) As Q1 ".
			    " Left Join ".
			    " ( Select JournalDt.Ledger_Number, ".
			    "   Sum((if(Journaldt_dbcd = 'D', Journaldt_amount1, 0) - if(Journaldt_dbcd = 'C', Journaldt_amount1, 0))) PrevAmount1, ".
				"   Sum((if(Journaldt_dbcd = 'D', Journaldt_amount2, 0) - if(Journaldt_dbcd = 'C', Journaldt_amount2, 0))) PrevAmount2 ".
				" From JournalDt ".
				" Where Date_format(JournalDt.Journal_Date,'%Y-%m-%d') < ifNull(". $fromdate.",date_format(JournalDt.Journal_Date,'%Y-%m-%d'))  ".
				" And JournalDt.Ledger_Number between ifNull(".$fromledger.",JournalDt.Ledger_Number) and ifNull(". $toledger.",JournalDt.Ledger_Number) ".
				" And IfNull(JournalDt.JournalDt_OldData,0) = 0 ".
				" Group By JournalDt.Ledger_Number ".
				" ) As Q2 on Q2.Ledger_Number = Q1.ClientCode ";
				
$query = $query." Order by ClientCode, OrderSalesDate, SalesRefer, AccID  ";
	
//echo $query; exit;

$ArrayTotalByReport   = Array();
$ArrayTotalByMaster   = Array();
$ArraySalesAmount     = Array();
$ArraySalesRefer      = Array();

$MasterByCode = '';
$MasterByName = '';

$RowNum    = 13;
$ColumnNum = 0;

$result = mysql_query($query,$connection) or die(mysql_error());
while($rows=mysql_fetch_row($result)){

    // Master By Options Names and Totals
	if (($MasterByCode != $rows[0] && $MasterByCode != '')) {
				
		$StyleClass  = $boldrightFormat;
		$TotalsLabel = 'Client Total';
		DisplayTotals($worksheet, $ArrayTotalByMaster, $DecimalBase, $TotalsLabel, $StyleClass, $RowNum, $ColumnNum) ;
		
		//Increment the Rows Counter By 1 and Reset the Columns Counter
		$RowNum += 2;
		$ColumnNum = 0;	
	}
	
	
	if ($MasterByCode == '' || $MasterByCode != $rows[0]) { 
		if ($displaycur == $Base1) {
			$BalanceAmount = $rows[23];
		}
		else if ($displaycur == $Base2){
			$BalanceAmount = $rows[24];
		}
		
		$worksheet->setMerge($RowNum, $ColumnNum, $RowNum, $ColumnNum + 3);
		$worksheet->write($RowNum,$ColumnNum,"Client: ".$rows[0]." - ".$rows[1], $boldleftFormat);
		$ColumnNum += 4;
		$worksheet->setMerge($RowNum, $ColumnNum, $RowNum, $ColumnNum + 1);
		$worksheet->write($RowNum,$ColumnNum,"CL: ".number_format($rows[25], $DecimalBase), $boldleftFormat);
		$ColumnNum += 2;
		$worksheet->setMerge($RowNum, $ColumnNum, $RowNum, $ColumnNum + 1);
		$worksheet->write($RowNum,$ColumnNum,"PT: ".$rows[26], $boldleftFormat);
		$ColumnNum += 2;
		$worksheet->setMerge($RowNum, $ColumnNum, $RowNum, $ColumnNum + 1);
		$worksheet->write($RowNum,$ColumnNum,"Balance: ".format_currency($BalanceAmount, $DecimalBase)."  ".$displaycur, $boldleftFormat);
		$ColumnNum += 2;
		
		//Increment the Rows Counter By 1 and Reset the Columns Counter
		$RowNum += 1;
		$ColumnNum = 0;	
		
		$MasterByCode = $rows[0];
		$MasterByName = $rows[1];
		
		unset($ArrayTotalByMaster);
		unset($ArraySalesAmount);
		unset($ArraySalesRefer);
	}
	
	if ($displaycur == $Base1) {
		$NetAmount     = $rows[5];
		$PaidAmount    = $rows[9];
		$ReturnAmount  = $rows[14];
		$RemainAmount  = $rows[17];
	}
	else if ($displaycur == $Base2){
		$NetAmount     = $rows[6];
		$PaidAmount    = $rows[10];
		$ReturnAmount  = $rows[15];
		$RemainAmount  = $rows[18];
	}
	
	$worksheet->write($RowNum, $ColumnNum, $rows[2], $regularleftFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $rows[3], $regularleftFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $rows[4], $regularleftFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $rows[29], $regularleftFormat);
	$ColumnNum += 1;
	$worksheet->writeNumber($RowNum, $ColumnNum, $NetAmount, $regularrightFormat);
	$ColumnNum += 1;
	
	$worksheet->write($RowNum, $ColumnNum, $rows[7], $regularleftFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $rows[8], $regularleftFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $rows[30], $regularleftFormat);
	$ColumnNum += 1;
	$worksheet->writeNumber($RowNum, $ColumnNum, $PaidAmount, $regularrightFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $rows[11], $regularrightFormat);
	$ColumnNum += 1;
	
	$worksheet->write($RowNum, $ColumnNum, $rows[12], $regularleftFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $rows[13], $regularleftFormat);
	$ColumnNum += 1;
	$worksheet->writeNumber($RowNum, $ColumnNum, $ReturnAmount, $regularrightFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $rows[16], $regularrightFormat);
	$ColumnNum += 1;
	
	$worksheet->writeNumber($RowNum, $ColumnNum, $RemainAmount, $regularrightFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $rows[19], $regularrightFormat);
	$ColumnNum += 1;
			
	if ($prtdesc == 1 && $rows[27] != '') {
		$RowNum += 1;
		$ColumnNum = 4;	
	
		$worksheet->write($RowNum, $ColumnNum, $rows[27], $regularleftFormat);
		$ColumnNum += 1;
	}
		
	$RefFound = 0;
	$count = count ($ArraySalesRefer); 
	for ($j=0; $j<$count; $j++){
		if ($ArraySalesRefer[$j] == $rows[4])
			$RefFound = 1;
	}
				
	if ($RefFound == 0) 
		$ArraySalesRefer[$count] = $rows[4];
					
	$ArraySalesAmount[$rows[4]] = $NetAmount;
	$ArrayTotalByMaster['NetAmount']  = 0;
	
	$count = count ($ArraySalesRefer); 
	for ($j=0; $j<$count; $j++){
		$ArrayTotalByMaster['NetAmount']  = $ArrayTotalByMaster['NetAmount']  + $ArraySalesAmount[$ArraySalesRefer[$j]];
	}
	
	
	
	$ArrayTotalByMaster['PaidAmount']   = $ArrayTotalByMaster['PaidAmount'] + $PaidAmount;
	$ArrayTotalByMaster['ReturnAmount'] = $ArrayTotalByMaster['ReturnAmount'] + $ReturnAmount;
	$ArrayTotalByMaster['RemainAmount'] = $ArrayTotalByMaster['NetAmount'] - $ArrayTotalByMaster['PaidAmount'] - $ArrayTotalByMaster['ReturnAmount'];
	
	//Increment the Rows Counter By 1 and Reset the Columns Counter
	$RowNum += 1;
	$ColumnNum = 0;	
	
}

if ($MasterByCode != '') {
				
	$StyleClass  = $boldrightFormat;
	$TotalsLabel = 'Client Total';
	DisplayTotals($worksheet, $ArrayTotalByMaster, $DecimalBase, $TotalsLabel, $StyleClass, $RowNum, $ColumnNum) ;

}
	
	



function DisplayTotals($worksheet, $PArrayTotal, $PDecimalBase, $PTotalsLabel, $PStyleClass, $PRowNum, $PColumnNum) {

	$worksheet->setMerge($PRowNum, $PColumnNum, $PRowNum, $PColumnNum + 2);
	$worksheet->write($PRowNum, $PColumnNum, $PTotalsLabel, $PStyleClass);
	$PColumnNum += 4;
		
	$worksheet->writeNumber($PRowNum, $PColumnNum, $PArrayTotal['NetAmount'], $PStyleClass);
	$PColumnNum += 4;
	
	$worksheet->writeNumber($PRowNum, $PColumnNum, $PArrayTotal['PaidAmount'], $PStyleClass);
	$PColumnNum += 4;
	
	$worksheet->writeNumber($PRowNum, $PColumnNum, $PArrayTotal['ReturnAmount'], $PStyleClass);
	$PColumnNum += 2;
		
	$worksheet->writeNumber($PRowNum, $PColumnNum, $PArrayTotal['RemainAmount'], $PStyleClass);
	$PColumnNum += 1;
	
}

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

?>