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

session_start();
if($_SESSION['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($_SESSION['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 ".$_SESSION['dbLabel'].".loginstk on project.project_code like concat(ifnull(loginstk.project_code,project.project_code),'%') ".
			  " where user_account='".$_SESSION['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 ".$_SESSION['dbLabel'].".loginstk on costcent.costcent_code like concat(ifnull(loginstk.costcent_code,costcent.costcent_code),'%') ".
			   " where user_account='".$_SESSION['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 ($fromsalesman != '')    $SelectedOptions .= "-> Salesman From: ". $fromsalesman . "  To: ". $tosalesman ."<br>";

if($chksalesman == 1)   $groupbyOption .= "Group by: -> Salesman ";
  
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'); 

$fromsalesman    = GetSQLValueString($fromsalesman, 'text');
$tosalesman      = GetSQLValueString($tosalesman, '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,$groupbyOption, $seloptionFormat);  

$worksheet->setMerge(9, 0, 10, 6);
$worksheet->write(10,0,$SelectedOptions, $seloptionFormat);  

$RowNum    = 13;
$ColumnNum = 0;


$RowNum += 1;
$ColumnNum = 0;	

$worksheet->setColumn ($ColumnNum, $ColumnNum, 14);
$worksheet->write($RowNum,$ColumnNum,"Date", $boldleftFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 10);
$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, 30);
$worksheet->write($RowNum,$ColumnNum,"Description", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 16);
$worksheet->write($RowNum,$ColumnNum,"Amount", $boldcenterFormat);
$ColumnNum += 1;

$worksheet->setColumn ($ColumnNum, $ColumnNum, 16);
$worksheet->write($RowNum,$ColumnNum,"Paid Amount", $boldcenterFormat);
$ColumnNum += 1;

$worksheet->setColumn ($ColumnNum, $ColumnNum, 16);
$worksheet->write($RowNum,$ColumnNum,"Remaining Amount", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum,14);
$worksheet->write($RowNum,$ColumnNum,"Up to Day", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum,8);
$worksheet->write($RowNum,$ColumnNum,"Due Days", $boldcenterFormat);
$ColumnNum += 1;
	 
$RowNum += 1;	  
$MainCompanyName=explode('_',$_SESSION['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, SUBSTRING(DirYear,-4) ".
			" From ".$_SESSION['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   ".
			"       (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 ".$_SESSION['dbLabel'].".loginstk ".
					" 			   where loginstk.user_account='".$_SESSION['useraccount']."' ".
					"              and (project_code is not null and project_code != '') ".
					"			 ) QP  ";
					
$QueryRestrictCst = " Inner join ( select distinct costcent_code from ".$_SESSION['dbLabel'].".loginstk ".
					"			   where loginstk.user_account='".$_SESSION['useraccount']."' ".
					"              and (costcent_code is not null and costcent_code != '') ".
					"			 ) QC  ";
					
					
$query = '';
$query = $query." select InvoiceId, ClientCode, ClientName, date_format(SalesDate,'%d/%m/%Y') as SalesDate,SalesRefer,headerjournal, SalesTotalBase1, SalesTotalBase2, TransPaidBase1, TransPaidBase2,TransRemBase1, 
				TransRemBase2, RemainDays, agingdate2, Q2.PrevAmount1, Q2.PrevAmount2, CreditLimits,PaymentTerms, description, date_format(value_date, '%d/%m/%Y') as value_date,
				saleman_number, SalesmanName ";
	
$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; 

		$query = $query." Select headerlink.headerlink_id As InvoiceId, headerlink.ledger_number As ClientCode, ledger.Ledger_name As ClientName, headerlink.headerlink_date As SalesDate, ".
						" headerlink.reference As SalesRefer, headerlink.journal_type as headerjournal,".
						" ifNull(headerlink.amountbase1,0) As SalesTotalBase1, ifNull(headerlink.amountbase2,0) As SalesTotalBase2, ".
						" sum(ifNull(detaillink.amountbase1,0)) As TransPaidBase1, sum(ifNull(detaillink.amountbase2,0)) As TransPaidBase2, ".
						" ifNull(Q.amountbase1,headerlink.amountbase1) As TransRemBase1, ifNull(Q.amountbase2,headerlink.amountbase2) As TransRemBase2, ".
						" DATEDIFF(if(date_format(detaillink.Detaillink_date,'%Y-%m-%d') is not null and ifNull(Q.amountbase1,headerlink.amountbase1) = 0 ,
						date_format(detaillink.Detaillink_date,'%Y-%m-%d'),date_format(".$agingdate.",'%Y-%m-%d')),date_format(headerlink.headerlink_date,'%Y-%m-%d'))
						RemainDays, date_format($agingdate,'%d/%m/%Y') as agingdate2,Clients.Client_CreditLimit As CreditLimits, Client_PaymentTerms As PaymentTerms, headerlink.description as description, headerlink.value_date as value_date  ".
						" , headerlink.saleman_number as saleman_number,  salesmen.Ledger_name As SalesmanName ".
						" From ".$_SESSION['dbLabel'].".headerlink as headerlink ".
						" left Join ".$_SESSION['dbLabel'].".detaillink as detaillink on  detaillink.form_id = headerlink.form_id ".
						" left join(  select d1.form_id as form_id,
									(h1.amountbase1-sum(d1.amountbase1)) as amountbase1, (h1.amountbase2-sum(d1.amountbase2)) as amountbase2,d1.year as year,
									d1.company as company, d1.related_journalType as journal_type
									from ".$_SESSION['dbLabel'].".detaillink as d1
									left join ".$_SESSION['dbLabel'].".headerlink as h1 on h1.form_id = d1.form_id
									group by d1.form_id
							) as Q on Q.form_id = headerlink.form_id and Q.year=headerlink.year
							and Q.company=headerlink.company and Q.journal_type=headerlink.journal_type ".
						" Inner Join $DirSchema.Ledger on  Ledger.ledger_Number = headerlink.ledger_number ".
						" Inner join $DirSchema.Clients on Clients.Ledger_number = headerlink.ledger_number ".
						" left join $DirSchema.salesmen on salesmen.Ledger_number = headerlink.saleman_number ";
		
		if ($projectexist >	0 && $FI_RepRestrictProject == 1)
			$query = $query.$QueryRestrictPrj." on headerlink.project_code like concat(QP.project_code,'%') ";
										 
		if ($costcentexist > 0 && $FI_RepRestrictCostcenter == 1)
			$query = $query.$QueryRestrictCst." on headerlink.costcent_code like concat(QC.costcent_code,'%') ";	
			
		$query = $query." Where  ";
		if($dateoption == 1)
			$query = $query." Date_format(headerlink.headerlink_date,'%Y-%m-%d') between ifNull(".$fromdate.",date_format(headerlink.headerlink_date,'%Y-%m-%d')) and ifNull(".$todate.",date_format(headerlink.headerlink_date,'%Y-%m-%d'))  ";
		else if($dateoption == 2)
			$query = $query." Date_format(headerlink.value_date,'%Y-%m-%d') between ifNull(".$fromdate.",date_format(headerlink.value_date,'%Y-%m-%d')) and ifNull(".$todate.",date_format(headerlink.value_date,'%Y-%m-%d'))  ";
						
		$query = $query." And headerlink.ledger_number between ifNull(".$fromledger.",headerlink.ledger_number) and ifNull(". $toledger.",headerlink.ledger_number) ".
						" And if($fromsalesman is null or $fromsalesman = '',1=1,headerlink.saleman_number between ifNull(".$fromsalesman.",headerlink.saleman_number) and ifNull(". $tosalesman.",headerlink.saleman_number)) ";
		if($Module_Delivery == 0 ) $query = $query." And headerlink.journal_type in ('DI','DR','JS','DV','SO')  ";
		else  $query = $query." And headerlink.journal_type in ('SI','SR','JS','DV','SO')  ";
			$query = $query." And upper(headerlink.company) = upper('".$DirCompany."') And upper(headerlink.year) = upper('".$DirYear."') ".
						" And upper(ifNull(headerlink.project_code,'-1')) like ifNull(upper(".$fromproject."), upper(ifNull(headerlink.project_code,'-1'))) ".
						" And upper(ifNull(headerlink.costcent_code,'-1')) like ifNull(upper(".$fromcst."), upper(ifNull(headerlink.costcent_code,'-1'))) ";
		if ($prtclosedinv == 1)	$query = $query." and ifNull(Q.amountbase1,headerlink.amountbase1) = 0 ";	
        else 	$query = $query." and ifNull(Q.amountbase1,headerlink.amountbase1) <> 0 ";
		if($examnt1 != '' && $examnt2 != '')	
			$query = $query." and ifNull(Q.amountbase1,headerlink.amountbase1) not between '$examnt1' and '$examnt2' ";	
		if($chksalesman == 1)
			$query = $query." group by saleman_number,InvoiceId,SalesDate,SalesRefer,ClientCode ";
		else 
		$query = $query." group by InvoiceId, SalesDate,SalesRefer,ClientCode ";
	
		
		
		//Debit Notes
		$query = $query." Union ".
					    " Select headerlink.headerlink_id As InvoiceId, headerlink.ledger_number As ClientCode, ledger.Ledger_name As ClientName, headerlink.headerlink_date As SalesDate, ".
						" headerlink.reference As SalesRefer, headerlink.journal_type as headerjournal,".
						" ifNull(headerlink.amountbase1,0) As SalesTotalBase1, ifNull(headerlink.amountbase2,0) As SalesTotalBase2, ".
						" sum(ifNull(detaillink.amountbase1,0)) As TransPaidBase1, sum(ifNull(detaillink.amountbase2,0)) As TransPaidBase2, ".
						" ifNull(Q.amountbase1,headerlink.amountbase1) As TransRemBase1, ifNull(Q.amountbase2,headerlink.amountbase2) As TransRemBase2, ".
						" DATEDIFF(if(date_format(detaillink.Detaillink_date,'%Y-%m-%d') is not null and ifNull(Q.amountbase1,headerlink.amountbase1) ,
						date_format(detaillink.Detaillink_date,'%Y-%m-%d'),date_format(".$agingdate.",'%Y-%m-%d')),date_format(headerlink.headerlink_date,'%Y-%m-%d'))
						RemainDays, date_format($agingdate,'%d/%m/%Y') as agingdate2,Clients.Client_CreditLimit As CreditLimits, Client_PaymentTerms As PaymentTerms, headerlink.description as description, headerlink.value_date as value_date  ".
						" , headerlink.saleman_number as saleman_number,  salesmen.Ledger_name As SalesmanName ".
						" From ".$_SESSION['dbLabel'].".headerlink as headerlink ".
						" left Join ".$_SESSION['dbLabel'].".detaillink as detaillink on  detaillink.form_id = headerlink.form_id ".
						" left join(  select d1.form_id as form_id,
										(h1.amountbase1-sum(d1.amountbase1)) as amountbase1, (h1.amountbase2-sum(d1.amountbase2)) as amountbase2,d1.year as year,
										d1.company as company, d1.related_journalType as journal_type
										from ".$_SESSION['dbLabel'].".detaillink as d1
										left join ".$_SESSION['dbLabel'].".headerlink as h1 on h1.form_id = d1.form_id
										group by d1.form_id
								) as Q on Q.form_id = headerlink.form_id and Q.year=headerlink.year
								and Q.company=headerlink.company and Q.journal_type=headerlink.journal_type ".
						" left join journaldt on concat(headerlink.journal_type,journaldt.journaldt_id,headerlink.year) = headerlink.form_id	".
						" left join journal on journal.journal_id = journaldt.journal_id ".
						" Inner Join $DirSchema.Ledger on  Ledger.ledger_Number = headerlink.ledger_number ".
						" Inner join $DirSchema.Clients on Clients.Ledger_number = headerlink.ledger_number ".
						" left join $DirSchema.salesmen on salesmen.Ledger_number = headerlink.saleman_number ";
						
		
		if ($projectexist >	0 && $FI_RepRestrictProject == 1)
				$query = $query.$QueryRestrictPrj." on headerlink.project_code like concat(QP.project_code,'%') ";
											 
		if ($costcentexist > 0 && $FI_RepRestrictCostcenter == 1)
				$query = $query.$QueryRestrictCst." on headerlink.costcent_code like concat(QC.costcent_code,'%') ";	
		
		$query = $query." Where  ";
		if($dateoption == 1)
			$query = $query." Date_format(headerlink.headerlink_date,'%Y-%m-%d') between ifNull(".$fromdate.",date_format(headerlink.headerlink_date,'%Y-%m-%d')) and ifNull(".$todate.",date_format(headerlink.headerlink_date,'%Y-%m-%d'))  ";
		else if($dateoption == 2)
			$query = $query." Date_format(headerlink.value_date,'%Y-%m-%d') between ifNull(".$fromdate.",date_format(headerlink.value_date,'%Y-%m-%d')) and ifNull(".$todate.",date_format(headerlink.value_date,'%Y-%m-%d'))  ";
							
		$query = $query." And headerlink.ledger_number between ifNull(".$fromledger.",headerlink.ledger_number) and ifNull(". $toledger.",headerlink.ledger_number) ".
						" And if($fromsalesman is null or $fromsalesman = '',1=1,headerlink.saleman_number between ifNull(".$fromsalesman.",headerlink.saleman_number) and ifNull(". $tosalesman.",headerlink.saleman_number)) ".
						" And headerlink.journal_type in ('JV','OP') ".
						" And JournalDt.Journaldt_dbcd = 'D' ".
						" And upper(headerlink.company) = upper('".$DirCompany."') And upper(headerlink.year) = upper('".$DirYear."') ".
						//" And if(detaillink_id is null, 1=1, upper(detaillink.company) = upper('".$DirCompany."') And upper(detaillink.year) = upper('".$DirYear."')  And detaillink.related_journalType in ('JV','OP')) ".
						" And upper(ifNull(headerlink.project_code,'-1')) like ifNull(upper(".$fromproject."), upper(ifNull(headerlink.project_code,'-1'))) ".
						" And upper(ifNull(headerlink.costcent_code,'-1')) like ifNull(upper(".$fromcst."), upper(ifNull(headerlink.costcent_code,'-1'))) ";
		if ($prtclosedinv == 1)	$query = $query." and ifNull(Q.amountbase1,headerlink.amountbase1) = 0 ";	
        else 	$query = $query." and ifNull(Q.amountbase1,headerlink.amountbase1) <> 0 ";
		if($examnt1 != '' && $examnt2 != '')	
			$query = $query." and ifNull(Q.amountbase1,headerlink.amountbase1) not between '$examnt1' and '$examnt2' ";	
		if($chksalesman == 1)
			$query = $query." group by InvoiceId,SalesDate,SalesRefer,ClientCode, saleman_number ";
		else 
		$query = $query." group by InvoiceId,SalesDate,SalesRefer,ClientCode ";
		
		$query = $query." Union ".
					    " Select null As InvoiceId, detaillink.ledger_number As ClientCode, ledger.Ledger_name As ClientName, null As SalesDate, ".
						" null As SalesRefer, null as headerjournal,".
						" 0 As SalesTotalBase1, 0 As SalesTotalBase2, ".
						" sum(ifNull(detaillink.amountbase1,0)) As TransPaidBase1, sum(ifNull(detaillink.amountbase2,0)) As TransPaidBase2, ".
						" - sum(ifNull(detaillink.amountbase1,0)) As TransRemBase1, - sum(ifNull(detaillink.amountbase2,0)) As TransRemBase2, ".
						" null RemainDays, date_format($agingdate,'%d/%m/%Y') as agingdate2,Clients.Client_CreditLimit As CreditLimits, Client_PaymentTerms As PaymentTerms, null as description, null as value_date  ".
						" , detaillink.saleman_number as saleman_number,  salesmen.Ledger_name As SalesmanName ".
						" From ".$_SESSION['dbLabel'].".detaillink as detaillink ".
						" Inner Join $DirSchema.Ledger on  Ledger.ledger_Number = detaillink.ledger_number ".
						" Inner join $DirSchema.Clients on Clients.Ledger_number = detaillink.ledger_number ".
						" left join $DirSchema.salesmen on salesmen.Ledger_number = detaillink.saleman_number ";
						
		
		if ($projectexist >	0 && $FI_RepRestrictProject == 1)
				$query = $query.$QueryRestrictPrj." on detaillink.project_code like concat(QP.project_code,'%') ";
											 
		if ($costcentexist > 0 && $FI_RepRestrictCostcenter == 1)
				$query = $query.$QueryRestrictCst." on detaillink.costcent_code like concat(QC.costcent_code,'%') ";	
		
		$query = $query." Where (detaillink.form_id is null or detaillink.form_id = '') ".
							" And Date_format(Detaillink_date,'%Y-%m-%d') between ifNull(".$fromdate.",date_format(Detaillink_date,'%Y-%m-%d')) and ifNull(".$todate.",date_format(Detaillink_date,'%Y-%m-%d'))  ".
							" And detaillink.ledger_number between ifNull(".$fromledger.",detaillink.ledger_number) and ifNull(". $toledger.",detaillink.ledger_number) ".
							" And if($fromsalesman is null or $fromsalesman = '',1=1,detaillink.saleman_number between ifNull(".$fromsalesman.",detaillink.saleman_number) and ifNull(". $tosalesman.",detaillink.saleman_number)) ".
							" And detaillink.journal_type in ('RV','CV') ".
							" And upper(detaillink.company) = upper('".$DirCompany."') And upper(detaillink.year) = upper('".$DirYear."') ".
							" And upper(ifNull(detaillink.project_code,'-1')) like ifNull(upper(".$fromproject."), upper(ifNull(detaillink.project_code,'-1'))) ".
							" And upper(ifNull(detaillink.costcent_code,'-1')) like ifNull(upper(".$fromcst."), upper(ifNull(detaillink.costcent_code,'-1'))) ";
		if($chksalesman == 1)
			$query = $query." group by SalesDate,SalesRefer,ClientCode, saleman_number ";
		else 	
			$query = $query." group by SalesDate,SalesRefer,ClientCode ";
				
		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 ";
if($chksalesman == 1)
	$query = $query." Order by saleman_number, ClientCode,date_format(SalesDate,'%Y-%m-%d'), SalesRefer  ";
else	
	$query = $query." Order by ClientCode,date_format(SalesDate,'%Y-%m-%d'), SalesRefer  ";
	
//echo $query; exit;

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

$ArrayAllTotal        = Array();

$ArrayFinalTotal = Array();

$MasterByCode = '';
$MasterByName = '';
$MasterByCode2 = '*';

$RowNum    = 13;
$ColumnNum = 0;

$ref = '';
$jourtype = '';
$headerid = '';
$result = mysql_query($query,$connection) or die(mysql_error());
while($rows=mysql_fetch_array($result)){
	extract($rows);
    // Master By Options Names and Totals
	if (($MasterByCode != $ClientCode && $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 += 1;
		$ColumnNum = 0;	
	}
	
	//group by salesman
	if($MasterByCode2 != $saleman_number && $chksalesman == 1 && $MasterByCode2 != '*'){
		
		$RowNum += 1;
		$ColumnNum = 0;	
		$StyleClass  = $boldrightFormat;
		$TotalsLabel = 'All Clients Total / salesman';	
		 DisplayAllTotals($worksheet, $ArrayAllTotal, $DecimalBase, $TotalsLabel, $StyleClass, $RowNum, $ColumnNum);
		
		unset($ArrayAllTotal);

	}
	//group by salesman
	if($MasterByCode2 != $saleman_number && $chksalesman == 1){
		$RowNum += 3;
		$ColumnNum = 0;	
		$worksheet->setMerge($RowNum, $ColumnNum, $RowNum, $ColumnNum + 3);
		$worksheet->write($RowNum,$ColumnNum,"Salesman: ".$saleman_number." - ".$SalesmanName, $boldleftFormat);
	
	} 
	
	if ($MasterByCode == '' || $MasterByCode != $ClientCode) { 
		if ($displaycur == $Base1) {
			$BalanceAmount = $PrevAmount1 ;
		}
		else if ($displaycur == $Base2){
			$BalanceAmount = $PrevAmount2;
		} 
		
		$RowNum += 2;
		$ColumnNum = 0;	
		
		$worksheet->setMerge($RowNum, $ColumnNum, $RowNum, $ColumnNum + 3);
		$worksheet->write($RowNum,$ColumnNum,"Client: ".$ClientCode." - ".$ClientName, $boldleftFormat);
		$ColumnNum += 4;
		$worksheet->setMerge($RowNum, $ColumnNum, $RowNum, $ColumnNum );
		$worksheet->write($RowNum,$ColumnNum,"CL: ".$CreditLimits, $boldleftFormat);
		$ColumnNum += 1;
		$worksheet->setMerge($RowNum, $ColumnNum, $RowNum, $ColumnNum);
		$worksheet->write($RowNum,$ColumnNum,"PT: ".$PaymentTerms, $boldleftFormat);
		$ColumnNum += 1;
		$worksheet->setMerge($RowNum, $ColumnNum, $RowNum, $ColumnNum + 2);
		$worksheet->write($RowNum,$ColumnNum,"Balance: ".format_currency($BalanceAmount, $DecimalBase)."  ".$displaycur, $boldleftFormat);
		
		//Increment the Rows Counter By 1 and Reset the Columns Counter
		$RowNum += 1;
		$ColumnNum = 0;	
		
		$MasterByCode = $ClientCode;
		$MasterByName = $ClientName;
		
		unset($ArrayTotalByMaster);
		unset($ArraySalesAmount);
		unset($ArraySalesRefer);
	}
	
	if ($displaycur == $Base1) {
		if($jourtype != $headerjournal || $ref != $SalesRefer || $headerid != $InvoiceId) {
			$NetAmount     = $SalesTotalBase1;
			$RemainAmount  = $TransRemBase1;
		}
		else {
			$NetAmount = 0;
			$RemainAmount  = 0;
		}
		$PaidAmount    = $TransPaidBase1;
		
	}
	else if ($displaycur == $Base2){
		if($ref != $SalesRefer || $jourtype != $headerjournal || $headerid != $InvoiceId) {
			$NetAmount     = $SalesTotalBase2;
			$RemainAmount  = $TransRemBase2;
		}
		else {
			$NetAmount = 0;
			$RemainAmount = 0;
		}
		$PaidAmount    = $TransPaidBase2;
		
	}
	
	$RowNum += 1;
	if($ref != $SalesRefer || $jourtype != $headerjournal || $headerid != $InvoiceId){
		$worksheet->write($RowNum, $ColumnNum, $SalesDate, $regularleftFormat);
		$ColumnNum += 1;
		$worksheet->write($RowNum, $ColumnNum, $value_date, $regularleftFormat);
		$ColumnNum += 1;
		$worksheet->write($RowNum, $ColumnNum, $SalesRefer, $regularleftFormat);
		$ColumnNum += 1;
		$worksheet->write($RowNum, $ColumnNum, $headerjournal, $regularleftFormat);
		$ColumnNum += 1;
		$worksheet->write($RowNum, $ColumnNum, $description, $regularleftFormat);
		$ColumnNum += 1;
		$worksheet->writeNumber($RowNum, $ColumnNum, $NetAmount, $regularrightFormat);
		$ColumnNum += 1;
		$ArrayAllTotal['AllNetAmount'] = $ArrayAllTotal['AllNetAmount'] + $NetAmount;
		$ArrayFinalTotal['AllNetAmount'] = $ArrayFinalTotal['AllNetAmount'] + $NetAmount; 
	} else{
		$worksheet->write($RowNum, $ColumnNum, '', $regularleftFormat);
		$ColumnNum += 1;
		$worksheet->write($RowNum, $ColumnNum, '', $regularleftFormat);
		$ColumnNum += 1;
		$worksheet->write($RowNum, $ColumnNum, '', $regularleftFormat);
		$ColumnNum += 1;
		$worksheet->write($RowNum, $ColumnNum, '', $regularleftFormat);
		$ColumnNum += 1;
		$worksheet->write($RowNum, $ColumnNum, '', $regularleftFormat);
		$ColumnNum += 1;
		$worksheet->writeNumber($RowNum, $ColumnNum, '', $regularrightFormat);
		$ColumnNum += 1;
		
	}
	
	
	$worksheet->writeNumber($RowNum, $ColumnNum, $PaidAmount, $regularrightFormat);
	$ColumnNum += 1;
	
	$ArrayAllTotal['AllPaidAmount'] = $ArrayAllTotal['AllPaidAmount'] + $PaidAmount;
	$ArrayFinalTotal['AllPaidAmount'] = $ArrayFinalTotal['AllPaidAmount'] + $PaidAmount;	
	
	if($ref != $SalesRefer || $jourtype != $headerjournal || $headerid != $InvoiceId){	
		$worksheet->writeNumber($RowNum, $ColumnNum, $RemainAmount, $regularrightFormat);
		$ArrayAllTotal['AllRemainAmount'] = $ArrayAllTotal['AllRemainAmount'] + $RemainAmount;	
		$ArrayFinalTotal['AllRemainAmount'] = $ArrayFinalTotal['AllRemainAmount'] + $RemainAmount;
	}
	else
		$worksheet->writeNumber($RowNum, $ColumnNum, '', $regularrightFormat);
	
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $agingdate2, $regularrightFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $RemainDays, $regularrightFormat);
	$ColumnNum += 1;
			
		
	if($prtdesc == 1 && $description != '' && ($ref != $SalesRefer || $jourtype != $headerjournal || $headerid != $InvoiceId)){
		$RowNum += 1;
		$ColumnNum = 0;	
		$ColumnNum += 2;
		$worksheet->write($RowNum, $ColumnNum, 'Description: ', $regularrightFormat);
		$ColumnNum += 1;
		$worksheet->write($RowNum, $ColumnNum, $description, $regularrightFormat);
	}		
	$RefFound = 0;
	$count = count ($ArraySalesRefer); 
	for ($j=0; $j<$count; $j++){
		if ($ArraySalesRefer[$j] == $SalesRefer)
			$RefFound = 1;
	}
				
	if ($RefFound == 0) 
		$ArraySalesRefer[$count] = $SalesRefer;
					
	if($ref != $SalesRefer || $jourtype != $headerjournal || $headerid != $InvoiceId) $ArraySalesAmount[$SalesRefer] = $ArraySalesAmount[$SalesRefer] + $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['RemainAmount'] = $ArrayTotalByMaster['NetAmount'] - $ArrayTotalByMaster['PaidAmount'];
	
	//Increment the Rows Counter By 1 and Reset the Columns Counter
	$RowNum += 1;
	$ColumnNum = 0;	
	$jourtype = $headerjournal;
	$ref = $SalesRefer;
	$headerid = $InvoiceId;
	$MasterByCode2 = $saleman_number;
}

if ($MasterByCode != '' || ($MasterByCode2 != $saleman_number && $chksalesman == 1)) {
				
	$StyleClass  = $boldrightFormat;
	$TotalsLabel = 'Client Total';
	DisplayTotals($worksheet, $ArrayTotalByMaster, $DecimalBase, $TotalsLabel, $StyleClass, $RowNum, $ColumnNum) ;

}
	$ColumnNum=0;
	$RowNum++;
	$StyleClass  = $boldrightFormat;
	$TotalsLabel = 'All Clients Total';
	if($chksalesman == 1) $TotalsLabel .= " / salesman ";	
	DisplayAllTotals($worksheet, $ArrayAllTotal, $DecimalBase, $TotalsLabel, $StyleClass, $RowNum, $ColumnNum) ;
	


if($chksalesman == 1){
	$ColumnNum=0;
	$RowNum = $RowNum+2;
	$StyleClass  = $boldrightFormat;
	$TotalsLabel = 'Final Clients Total ';	

	DisplayAllTotals($worksheet, $ArrayFinalTotal, $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 += 5;
		
	$worksheet->writeNumber($PRowNum, $PColumnNum, $PArrayTotal['NetAmount'], $PStyleClass);
	$PColumnNum += 1;
	
	$worksheet->writeNumber($PRowNum, $PColumnNum, $PArrayTotal['PaidAmount'], $PStyleClass);
	$PColumnNum += 1;
	
	
	$worksheet->writeNumber($PRowNum, $PColumnNum, $PArrayTotal['RemainAmount'], $PStyleClass);
	$PColumnNum += 1;
	
}

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

	$worksheet->setMerge($PRowNum, $PColumnNum, $PRowNum, $PColumnNum + 2);
	$worksheet->write($PRowNum, $PColumnNum, $PTotalsLabel, $PStyleClass);
	$PColumnNum += 5;
		
	$worksheet->writeNumber($PRowNum, $PColumnNum, $PArrayTotal['AllNetAmount'], $PStyleClass);
	$PColumnNum += 1;
	
	$worksheet->writeNumber($PRowNum, $PColumnNum, $PArrayTotal['AllPaidAmount'], $PStyleClass);
	$PColumnNum += 1;
	
	
	$worksheet->writeNumber($PRowNum, $PColumnNum, $PArrayTotal['AllRemainAmount'], $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();

?>