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


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

function date_to_excel($date) {//converts a dd/mm/yyyy date to excel count of days
	if (ereg("^([0-9]{1,2})/([0-9]{1,2})/([0-9]{2,4})$",$date,$t)) {
		return ceil(strtotime("$t[2]/$t[1]/$t[3]")/60/60/24)+25569;
	}
	else return $date;
}


//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);
}

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



$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 Upper(loginstk.company_name) = Upper('".$_SESSION['dbName']."') ".
			  " 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 Upper(loginstk.company_name) = Upper('".$_SESSION['dbName']."') ".
			   " 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);


//maroun
$querymasterproject=" select distinct Masterproject from ".$_SESSION['dbLabel'].".loginstk ".
					" where loginstk.user_account='".$_SESSION['useraccount']."'  and Upper(loginstk.company_name) = Upper('".$_SESSION['dbName']."') ".
					" and (Masterproject is not null and Masterproject != '') ";
$projectmasterresult = mysql_query($querymasterproject,$connection) or die(mysql_error());
$projectmasterexist   = mysql_num_rows($projectmasterresult);

//maroun
$querymastercostcenter=" select distinct Mastercostcenter from ".$_SESSION['dbLabel'].".loginstk ".
					   " where loginstk.user_account='".$_SESSION['useraccount']."'  and Upper(loginstk.company_name) = Upper('".$_SESSION['dbName']."') ".
					   " and (Mastercostcenter is not null and Mastercostcenter != '') ";
$costcentermasterresult = mysql_query($querymastercostcenter,$connection) or die(mysql_error());
$costcentermasterexist   = mysql_num_rows($costcentermasterresult);

//maroun
$querysalesmen=" select distinct clientsalesmen ".
			   " from ".$_SESSION['dbLabel'].".loginstk ".
			   " where user_account='".$_SESSION['useraccount']."' ".
			   " and (clientsalesmen is not null and clientsalesmen != '' ) ";
$salesmenresult = mysql_query($querysalesmen,$connection) or die(mysql_error());
$salesmenexist   = mysql_num_rows($salesmenresult);


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


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

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

//Title Format 
 $boldleftFormat =& $workbook->addFormat(array('size'=>9,
  'align'=>'left'  ,
  'valign'=>'top'  ,
  'bold'=>1
  )); 
  
  
  //Date Format 
 $DateFormat =& $workbook->addFormat(array('size'=>9,
  'align'=>'right'  ,
  'valign'=>'top'  ,
  'NumFormat'=>'MM/DD/YYYY'
  )); 
  
//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
  ));    
  
$grouplevel1Format =& $workbook->addFormat(array('size'=>10,
  'align'=>'left',
  'bold'=>1,
  'color'=>0x0C
  )); 
$grouplevel2Format =& $workbook->addFormat(array('size'=>10,
  'align'=>'left',
  'bold'=>0,
  'color'=>0x0A
  )); 
$grouplevel3Format =& $workbook->addFormat(array('size'=>9,
  'align'=>'left',
  'italic'=>1,
  'color'=>0x11
  ));   
$grouplevel1totalFormat =& $workbook->addFormat(array('size'=>10,
  'align'=>'right',
  'bold'=>1,
  'color'=>0x0C
  ));  
$grouplevel2totalFormat =& $workbook->addFormat(array('size'=>10,
  'align'=>'right',
  'bold'=>0,
  'color'=>0x0A
  ));  
$grouplevel3totalFormat =& $workbook->addFormat(array('size'=>9,
  'align'=>'right',
  'italic'=>1,
  'color'=>0x11
  ));   

$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);_(* "-"??_);_(@_)');
   	
$worksheet->setColumn (0, 0, 20); 
$worksheet->setColumn (1, 1, 40); 
$worksheet->setColumn (2, 4, 15); 
$worksheet->setColumn (5, 1, 12); 
$worksheet->setColumn (6, 8, 15);


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

$subproj 	 = ereg_replace('-','%',$subproj);
$subcostcent = ereg_replace('-','%',$subcostcent);

$SelectedOptions = "Filter by: \n";
if ($fromdate != '')      $SelectedOptions .= "-> Date From: ". $fromdate . "  To: ". $todate ."\n";	
if ($agingdate != '')     $SelectedOptions .= "-> Aging Date: ". $agingdate ."\n";	
if ($displaycurr != '')   $SelectedOptions .= "-> Currency: ". $displaycurr ."\n";	
if ($fromledger != '')    $SelectedOptions .= "-> Ledger From: ". $fromledger . "  To: ". $toledger ."\n";
if ($fromsalesman != '')  $SelectedOptions .= "-> Salesman From: ". $fromsalesman . "  To: ". $tosalesman ."\n";
if ($fromproject != '')   $SelectedOptions .= "-> Project From: ". $fromproject . "\n";
if ($fromcst != '')  	  $SelectedOptions .= "-> Costcent From: ". $fromcst . "\n";
if ($cltprj != '')    	  $SelectedOptions .= "-> Client Project From: ". $cltprj . "\n";
if ($cltcst != '')  	  $SelectedOptions .= "-> Client Costcent From: ". $cltcst . "\n";
if ($subproj != '') 	  $SelectedOptions .= "-> Project Include: ". $subproj . "\n";
if ($subcostcent != '')	  $SelectedOptions .= "-> Costcent Include: ". $subcostcent . "\n";


if (!empty($fromdate))
   $fromdate = explodeword($fromdate);
if (!empty($agingdate))
   $agingdate = explodeword($agingdate);   
if (!empty($todate))
   $todate = explodeword($todate);     
      
$fromledger      = GetSQLValueString($fromledger, 'text');
$toledger        = GetSQLValueString($toledger, 'text');
$fromsalesman    = GetSQLValueString($fromsalesman, 'text');
$tosalesman      = GetSQLValueString($tosalesman, 'text');
$fromproject     = GetSQLValueString($fromproject, 'text');
$fromcst  		 = GetSQLValueString($fromcst, 'text');
$cltprj     	 = GetSQLValueString($cltprj, 'text');
$cltcst      	 = GetSQLValueString($cltcst, 'text');
$fromdate  	   	 = GetSQLValueString($fromdate, 'date');
$todate          = GetSQLValueString($todate, 'date');   
$agingdate       = GetSQLValueString($agingdate, 'date');   
$rangefrom       = GetSQLValueString($rangefrom, 'double');
$rangeto         = GetSQLValueString($rangeto, 'double');
$subproj 	     = GetSQLValueString($subproj, 'text');
$subcostcent     = GetSQLValueString($subcostcent, 'text');

if (empty($showaddress)) $showaddress = 0;
if (empty($prtcstjour)) $prtcstjour = 0;

if ($accountno == 1) {
  $title = 'Suppliers';
  $tablename = 'Suppliers';
}  
else if ($accountno == 2) {
  $title = 'Clients';
  $tablename = 'Clients';
}  
else if ($accountno == 3)  {
  $title = 'Salesmen';
  $tablename = 'Clients';
}     


if ($lastbal == 1) {
	if($balorderby == 1)
		$OrderByCond = 'Q7.Balance Asc';
	else	 
		$OrderByCond = 'Q7.Balance Desc';

} else
{
	if($orderby == 1) 
		$OrderByCond = 'Q7.Ledger_number';
	else	 
		$OrderByCond = 'Q7.Ledger_Name';
}


$groupby1name = '';
$groupby2name = '';   
$groupby3name = '';
$QryGroupby1Code = '';
$QryGroupby2Code = '';
$QryGroupby3Code = '';
$QryGroupby1Name = '';
$QryGroupby2Name = '';
$QryGroupby3Name = '';

$SelSalesman = 0;
$SelProject = 0;
$SelMasterProject = 0;
$SelCostCenter = 0;
$SelMasterCostCenter = 0;

if ($groupby3 != 1 ) {
	$pairs = explode(",", $groupby3);
	
	$vCount = count($pairs);
	if ($vCount > 1) {
		$QryGroupby3Code = 'Concat( ';
		$QryGroupby3Name = 'Concat( ';
	}	
	foreach ($pairs as $pair) {
		
		if ($groupby3name != '') {
			$groupby3name .= " & ";
			$QryGroupby3Code .= ", '*', ";
			$QryGroupby3Name .= ", '*', ";
		}	
			
		
		if ($pair == 3 ) {	
			$SelSalesman 	  = 1;
			$groupby3name    .= 'Salesman';
			$QryGroupby3Code .= 'ifNull(SalesmanCode,"None")';
			$QryGroupby3Name .= 'ifNull(SalesmanName,"None")';
		}
		else if ($pair == 20 ) {	
			$SelProject 	  = 1;
			$groupby3name    .= 'Transaction Project(Parent)';
			$QryGroupby3Code .= 'ifNull(ParentProjectCode,"None")';
			$QryGroupby3Name .= 'ifNull(ParentProjectName,"None")';
		}
		else if ($pair == 4 ) {	
			$SelProject 	  = 1;
			$groupby3name    .= 'Transaction Project(Detail)';
			$QryGroupby3Code .= 'ifNull(ProjectCode,"None")';
			$QryGroupby3Name .= 'ifNull(ProjectName,"None")';
		}
		else if ($pair == 21 ) {	
			$SelMasterProject = 1;
			$groupby3name 	 .= 'Archieve Project(Parent)';
			$QryGroupby3Code .= 'ifNull(ParentMasterProjectCode,"None")';
			$QryGroupby3Name .= 'ifNull(ParentMasterProjectName,"None")';
		}
		else if ($pair == 5 ) {	
			$SelMasterProject = 1;
			$groupby3name 	 .= 'Archieve Project(Detail)';
			$QryGroupby3Code .= 'ifNull(MasterProjectCode,"None")';
			$QryGroupby3Name .= 'ifNull(MasterProjectName,"None")';
		}
		else if ($pair == 22 ) {	
			$SelCostCenter 	  = 1;
			$groupby3name    .= 'Transaction Costcenter(Parent)';	
			$QryGroupby3Code .= 'ifNull(ParentCostcentCode,"None")';
			$QryGroupby3Name .= 'ifNull(ParentCostcentName,"None")';
		}
		else if ($pair == 6 ) {	
			$SelCostCenter 	  = 1;
			$groupby3name    .= 'Transaction Costcenter(Detail)';	
			$QryGroupby3Code .= 'ifNull(CostcentCode,"None")';
			$QryGroupby3Name .= 'ifNull(CostcentName,"None")';
		}
		else if ($pair == 23 ) {
			$SelMasterCostCenter = 1;
			$groupby3name 	 	.= 'Archieve Costcenter(Parent)';	
			$QryGroupby3Code 	.= 'ifNull(ParentMasterCostcentCode,"None")';
			$QryGroupby3Name 	.= 'ifNull(ParentMasterCostcentName,"None")';
		}
		else if ($pair == 7 ) {
			$SelMasterCostCenter = 1;
			$groupby3name 	 	.= 'Archieve Costcenter(Detail)';	
			$QryGroupby3Code 	.= 'ifNull(MasterCostcentCode,"None")';
			$QryGroupby3Name 	.= 'ifNull(MasterCostcentName,"None")';
		}
		else if ($pair == 9 ) {	
			$groupby3name    .= 'Business Type';
			$QryGroupby3Code .= 'ifNull(BusinessType,"None")';
			$QryGroupby3Name .= 'ifNull(BusinessType,"None")';
		}
		else if ($pair == 10 )	{
			$groupby3name 	.= 'Profit Center';
			$QryGroupby3Code .= 'ifNull(ProfitCenter,"None")';
			$QryGroupby3Name .= 'ifNull(ProfitCenter,"None")';
		}
		
	}
	
	if ($vCount > 1) {
		$QryGroupby3Code .= ' ) ';
		$QryGroupby3Name .= ' ) ';
	}
	
	$QryGroupby3Code .= ' As Groupby3Code, ';
	$QryGroupby3Name .= ' As Groupby3Name ';

} else 
{
	$QryGroupby3Code .= " -1 As Groupby3Code, ";
	$QryGroupby3Name .= " 'None' As Groupby3Name ";
}
//echo $QryGroupby3Code."\n";
//echo $QryGroupby3Name;
//exit;

if ($groupby1 != '' ) {
		
	if ($groupby1 == 3 ) {	
	    $SelSalesman 	  = 1;
		$groupby1name    .= 'Salesman';
		$QryGroupby1Code .= 'ifNull(SalesmanCode,"None")';
		$QryGroupby1Name .= 'ifNull(SalesmanName,"None")';
	}
	else if ($groupby1 == 20 ) {	
		$SelProject 	  = 1;
		$groupby1name    .= 'Transaction Project(Parent)';
		$QryGroupby1Code .= 'ifNull(ParentProjectCode,"None")';
		$QryGroupby1Name .= 'ifNull(ParentProjectName,"None")';
	}
	else if ($groupby1 == 4 ) {	
		$SelProject 	  = 1;
		$groupby1name    .= 'Transaction Project(Detail)';
		$QryGroupby1Code .= 'ifNull(ProjectCode,"None")';
		$QryGroupby1Name .= 'ifNull(ProjectName,"None")';
	}
	else if ($groupby1 == 21 ) {	
		$SelMasterProject = 1;
		$groupby1name 	 .= 'Archieve Project(Parent)';
		$QryGroupby1Code .= 'ifNull(ParentMasterProjectCode,"None")';
		$QryGroupby1Name .= 'ifNull(ParentMasterProjectName,"None")';
	}
	else if ($groupby1 == 5 ) {	
		$SelMasterProject = 1;
		$groupby1name 	 .= 'Archieve Project(Detail)';
		$QryGroupby1Code .= 'ifNull(MasterProjectCode,"None")';
		$QryGroupby1Name .= 'ifNull(MasterProjectName,"None")';
	}
	else if ($groupby1 == 22 ) {
		$SelCostCenter 	  = 1;
		$groupby1name    .= 'Transaction Costcenter(Parent)';	
		$QryGroupby1Code .= 'ifNull(ParentCostcentCode,"None")';
		$QryGroupby1Name .= 'ifNull(ParentCostcentName,"None")';
	}
	else if ($groupby1 == 6 ) {	
		$SelCostCenter 	  = 1;
		$groupby1name    .= 'Transaction Costcenter(Detail)';	
		$QryGroupby1Code .= 'ifNull(CostcentCode,"None")';
		$QryGroupby1Name .= 'ifNull(CostcentName,"None")';
	}
	else if ($groupby1 == 23 ) {
		$SelMasterCostCenter = 1;
		$groupby1name 	 	.= 'Archieve Costcenter(Parent)';	
		$QryGroupby1Code 	.= 'ifNull(ParentMasterCostcentCode,"None")';
		$QryGroupby1Name 	.= 'ifNull(ParentMasterCostcentName,"None")';
	}
	else if ($groupby1 == 7 ) {
		$SelMasterCostCenter = 1;
		$groupby1name 	 	.= 'Archieve Costcenter(Detail)';	
		$QryGroupby1Code 	.= 'ifNull(MasterCostcentCode,"None")';
		$QryGroupby1Name 	.= 'ifNull(MasterCostcentName,"None")';
	}
	else if ($groupby1 == 9 ) {	
		$groupby1name    .= 'Business Type';
		$QryGroupby1Code .= 'ifNull(BusinessType,"None")';
		$QryGroupby1Name .= 'ifNull(BusinessType,"None")';
	}
	else if ($groupby1 == 10 )	{
		$groupby1name 	 .= 'Profit Center';
		$QryGroupby1Code .= 'ifNull(ProfitCenter,"None")';
		$QryGroupby1Name .= 'ifNull(ProfitCenter,"None")';
	}
	else {
		$groupby1name    .= '';				
		$QryGroupby1Code .= "-1";
		$QryGroupby1Name .= "'None'";
	}
		
	
	$QryGroupby1Code .= ' As Groupby1Code, ';
	$QryGroupby1Name .= ' As Groupby1Name, ';

} else 
{
	$QryGroupby1Code .= " -1 As Groupby1Code, ";
	$QryGroupby1Name .= " 'None' As Groupby1Name, ";
}

if ($groupby2 != '' ) {
		
	if ($groupby2 == 3 ) {	
		$SelSalesman 	  = 1;
		$groupby2name    .= 'Salesman';
		$QryGroupby2Code .= 'ifNull(SalesmanCode,"None")';
		$QryGroupby2Name .= 'ifNull(SalesmanName,"None")';
	}
	else if ($groupby2 == 20 ) {
		$SelProject 	  = 1;
		$groupby2name    .= 'Transaction Project(Parent)';
		$QryGroupby2Code .= 'ifNull(ParentProjectCode,"None")';
		$QryGroupby2Name .= 'ifNull(ParentProjectName,"None")';
	}
	else if ($groupby2 == 4 ) {	
		$SelProject 	  = 1;
		$groupby2name    .= 'Transaction Project(Detail)';
		$QryGroupby2Code .= 'ifNull(ProjectCode,"None")';
		$QryGroupby2Name .= 'ifNull(ProjectName,"None")';
	}
	else if ($groupby2 == 21 ) {
		$SelMasterProject = 1;
		$groupby2name 	 .= 'Archieve Project(Parent)';
		$QryGroupby2Code .= 'ifNull(ParentMasterProjectCode,"None")';
		$QryGroupby2Name .= 'ifNull(ParentMasterProjectName,"None")';
	}
	else if ($groupby2 == 5 ) {	
		$SelMasterProject = 1;
		$groupby2name 	 .= 'Archieve Project(Detail)';
		$QryGroupby2Code .= 'ifNull(MasterProjectCode,"None")';
		$QryGroupby2Name .= 'ifNull(MasterProjectName,"None")';
	}
	else if ($groupby2 == 22 ) {	
		$SelCostCenter 	  = 1;
		$groupby2name    .= 'Transaction Costcenter(Parent)';	
		$QryGroupby2Code .= 'ifNull(ParentCostcentCode,"None")';
		$QryGroupby2Name .= 'ifNull(ParentCostcentName,"None")';
	}
	else if ($groupby2 == 6 ) {	
		$SelCostCenter 	  = 1;
		$groupby2name    .= 'Transaction Costcenter(Detail)';	
		$QryGroupby2Code .= 'ifNull(CostcentCode,"None")';
		$QryGroupby2Name .= 'ifNull(CostcentName,"None")';
	}
	else if ($groupby2 == 23 ) {
		$SelMasterCostCenter = 1;
		$groupby2name 	 	.= 'Archieve Costcenter(Parent)';	
		$QryGroupby2Code 	.= 'ifNull(ParentMasterCostcentCode,"None")';
		$QryGroupby2Name 	.= 'ifNull(ParentMasterCostcentName,"None")';
	}
	else if ($groupby2 == 7 ) {
		$SelMasterCostCenter = 1;
		$groupby2name 	 	.= 'Archieve Costcenter(Detail)';	
		$QryGroupby2Code 	.= 'ifNull(MasterCostcentCode,"None")';
		$QryGroupby2Name 	.= 'ifNull(MasterCostcentName,"None")';
	}
	else if ($groupby2 == 9 ) {	
		$groupby2name    .= 'Business Type';
		$QryGroupby2Code .= 'ifNull(BusinessType,"None")';
		$QryGroupby2Name .= 'ifNull(BusinessType,"None")';
	}
	else if ($groupby2 == 10 )	{
		$groupby2name 	.= 'Profit Center';
		$QryGroupby2Code .= 'ifNull(ProfitCenter,"None")';
		$QryGroupby2Name .= 'ifNull(ProfitCenter,"None")';
	}
	else {
		$groupby2name    .= '';				
		$QryGroupby2Code .= "-1";
		$QryGroupby2Name .= "'None'";
	}
		
	
	$QryGroupby2Code .= ' As Groupby2Code, ';
	$QryGroupby2Name .= ' As Groupby2Name, ';

} else 
{
	$QryGroupby2Code .= " -1 As Groupby2Code, ";
	$QryGroupby2Name .= " 'None' As Groupby2Name, ";
}

$SelectedGroupBy = "Group by:  ";
if ($groupby1 != '' ) $SelectedGroupBy = $SelectedGroupBy . $groupby1name.", ";
if ($groupby2 != '' ) $SelectedGroupBy = $SelectedGroupBy . $groupby2name.", ";
if ($groupby3 != 1 )  $SelectedGroupBy = $SelectedGroupBy . $groupby3name.", ";
$SelectedOptions = substr($SelectedGroupBy, 0, strlen($SelectedGroupBy)-2) . "\n" . $SelectedOptions;


//Display Report Header
$worksheet->write(1,0,$CompanyName, $TitleFormat);
$worksheet->write(1,2,"Detail Aging-".$title, $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, 3);
$worksheet->write(3,0,$SelectedOptions, $seloptionFormat);  

$RowNum    = 10;
$ColumnNum = 0;
  
$worksheet->write($RowNum,$ColumnNum,"Account No.", $boldleftFormat);
$ColumnNum += 1;
$worksheet->write($RowNum,$ColumnNum,"Account Name", $boldleftFormat);
$ColumnNum += 1;
$worksheet->write($RowNum,$ColumnNum,"Credit Limit", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->write($RowNum,$ColumnNum,"PT", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->write($RowNum,$ColumnNum,"Previous Debit", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->write($RowNum,$ColumnNum,"Previous Credit", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->write($RowNum,$ColumnNum,"Cur.", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->write($RowNum,$ColumnNum,"Previous Balance", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->write($RowNum,$ColumnNum,"Current Balance", $boldcenterFormat);

$RowNum   += 1;
$ColumnNum = 0;

$worksheet->setMerge($RowNum, $ColumnNum, $RowNum, $ColumnNum + 2);
$worksheet->write($RowNum,$ColumnNum,"Description", $boldleftFormat);
$ColumnNum += 3;

$worksheet->write($RowNum,$ColumnNum,"Folio", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->write($RowNum,$ColumnNum,"Invoice Date", $boldcenterFormat);
$ColumnNum += 1;
if($showvdate == 1){
	$worksheet->write($RowNum,$ColumnNum,"Value Date", $boldcenterFormat);
	$ColumnNum += 1;
}
$worksheet->write($RowNum,$ColumnNum,"Due Days", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->write($RowNum,$ColumnNum,"Amount", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->write($RowNum,$ColumnNum,"Balance", $boldcenterFormat);


$RowNum    = 13;
$ColumnNum = 0;
 
$MainCompanyName=explode('_',$_SESSION['dbName']);  
$QueryDir = " Select Concat(CompanyName, '_', DirYear) As DirSchema ".
			" 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   ".
			//"  		(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 ";


    

if ($fromproject != 'Null' || $fromcst != 'Null') {
  $QueryCstJour ='CstJour.Cst_increment is null';
}  else
{
  $QueryCstJour ='True';
}


$byledgergrp = $_GET['byledgergrp'];

if($byledgergrp == '') $byledgergrp = 0;
$count = 0;
if($byledgergrp == 1)
	$querygrp = "select ledger_number, ledger_name from ledger where ledger_grpdet=1 and length(ledger_number) = 4 ";
else
	$querygrp = "select '' as ledger_number, '' as ledger_name";
$resgrp = mysql_query($querygrp,$connection);
while($rowgrp = mysql_fetch_array($resgrp)){
$ledgergrp_code = $rowgrp[0];	
$ledgergrp_name = $rowgrp[1];

$ResultDir=mysql_query($QueryDir,$connection) or die(mysql_error());
$ResultDir1=mysql_query($QueryDir,$connection) or die(mysql_error());
$RowsDirCount = mysql_num_rows($ResultDir);  

$result = mysql_query("start transaction", $connection);
$resultvars = mysql_query("set @PrevAmount:=0.0, @PrevBal:=0.0 ,@ActPrevBal:=0.0, @RecordNum:=0, @VarGrouping:='x' ", $connection);


$queryjournal=  " Select Q6.Groupby1Code, Q6.Groupby1Name, Q6.Groupby2Code, Q6.Groupby2Name, Q6.Groupby3Code, Q6.Groupby3Name, Q6.Ledger_number, Q6.Ledger_name, ".
				" Q6.CreditLimits, Q6.SalesmanCode, Q6.SalesmanName, Q6.ProjectCode, Q6.ProjectName, Q6.CostcentCode, Q6.CostcentName, Q6.Currency_code, ".
                " Q6.PrevDebitAmount, Q6.PrevCreditAmount, Q6.PrevBalance, Q6.Journaldt_desc, Q6.Journal_folio, Q6.JournalDt_DBCD, date_format(Q6.JDate,'%d/%m/%Y') as JDate, ".
				" Q6.Days,Q6.Balance,Q6.AgingAmount, Q6.RecordNum, ".
				" if($accountno!=1, if(Q6.TotalCredits >= Q6.PrevBalance, if (Q6.AgingBalance > 0, Q6.AgingBalance*-1, abs(Q6.AgingBalance)), Q6.AgingBalance), if(Q6.TotalDebits >= Q6.PrevBalance, if (Q6.AgingBalance > 0,  Q6.AgingBalance*-1, Q6.AgingBalance), Q6.AgingBalance)) as AgingBalance, ".
				//" if($accountno!=1, if(Q6.TotalCredits >= Q6.PrevBalance, Q6.PrevActualBal, (Q6.PrevBalance-Q6.TotalCredits)), if(Q6.TotalDebits >= Q6.PrevBalance, if (Q6.PrevBalance>=0, Q6.PrevActualBal, (Q6.TotalDebits+Q6.PrevBalance)), (Q6.PrevBalance-Q6.TotalDebits))) As ActualBalance, ".
				" if($accountno!=1,Q6.TotalDebits+Q6.PrevBalance-Q6.TotalCredits, Q6.TotalDebits+Q6.PrevBalance-Q6.TotalCredits) As ActualBalance, ".
				" Q6.OrdDate, Q6.JournalDt_ID, Q6.ClientAddress, Q6.PaymentTerms, date_format(Q6.VDate,'%d/%m/%Y') as VDate ";
$queryjournal = $queryjournal." , if(Q6.Days >= 0, Q6.AgingAmount, 0) As DueAmount , if(Q6.Days < 0, Q6.AgingAmount, 0) As NonDueAmount ";
				
$queryjournal .= " from (  ".
				" Select Q5.Groupby1Code, Q5.Groupby1Name, Q5.Groupby2Code, Q5.Groupby2Name, Q5.Groupby3Code, Q5.Groupby3Name, Q5.JournalDt_ID, Q5.Ledger_number, Q5.Ledger_name, Q5.ClientAddress, ".
				" Q5.CreditLimits, Q5.PaymentTerms, Q5.SalesmanCode, Q5.SalesmanName, Q5.ProjectCode, Q5.ProjectName, Q5.CostcentCode, Q5.CostcentName, Q5.Currency_code, Q5.PrevDebitAmount, ".
				" Q5.PrevCreditAmount, Q5.PrevBalance, Q5.Journaldt_desc, Q5.Journal_folio,Q5.JournalDt_DBCD, Q5.JDate,Q5.OrdDate,Q5.Days,Q5.Balance,Q5.AgingAmount, Q5.TotalCredits, Q5.TotalDebits, ".
				" if((Q5.GroupingOptions != Concat(Q5.Groupby1Code, Q5.Groupby2Code, Q5.Groupby3Code, Q5.Ledger_number, Q5.Currency_code, ifNull(Q5.ProjectCode,''), ifNull(Q5.CostcentCode,''))),@RecordNum:=1,@RecordNum:=(ifNull(@RecordNum,0)+1)) As RecordNum, ".
				" if((Q5.GroupingOptions != Concat(Q5.Groupby1Code, Q5.Groupby2Code, Q5.Groupby3Code, Q5.Ledger_number, Q5.Currency_code, ifNull(Q5.ProjectCode,''), ifNull(Q5.CostcentCode,''))),@PrevBal:=0.0,0), ".
				" if((Q5.GroupingOptions != Concat(Q5.Groupby1Code, Q5.Groupby2Code, Q5.Groupby3Code, Q5.Ledger_number, Q5.Currency_code, ifNull(Q5.ProjectCode,''), ifNull(Q5.CostcentCode,''))),@ActPrevBal:=0.0,0), ".
				" IF((Q5.GroupingOptions != Concat(Q5.Groupby1Code, Q5.Groupby2Code, Q5.Groupby3Code, Q5.Ledger_number, Q5.Currency_code, ifNull(Q5.ProjectCode,''), ifNull(Q5.CostcentCode,''))),@hiderow:=0,0), ". 
				" if($accountno!=1, if(@RecordNum=1, @PrevBal:=if(Q5.TotalCredits >= Q5.PrevBalance, (Q5.TotalCredits-Q5.PrevBalance-Q5.AgingAmount), (Q5.PrevBalance-Q5.TotalCredits+Q5.AgingAmount)), @PrevBal:=if(Q5.TotalCredits >= Q5.PrevBalance, (@PrevBal - Q5.AgingAmount), (@PrevBal + Q5.AgingAmount))), ".

				
				" 	if(@RecordNum=1, @PrevBal:= Q5.PrevBalance + Q5.TotalDebits - Q5.AgingAmount, @PrevBal:=  @PrevBal - Q5.AgingAmount )".
				" )	As AgingBalance, ".
				" if(ifNull(Balance,0) >= 0, if(@PrevBal > 0, @ActPrevBal:=@PrevBal, 0), Balance) as PrevActualBal ".
				" ,IF(ifNull(Balance,0) >= 0,IF(@PrevBal <0 or @hiderow=1,@hiderow:=1, @hiderow:=0), Balance)  AS showrow, Q5.VDate ".
				" from (  ".
        		" Select Q4.Groupby1Code, Q4.Groupby1Name, Q4.Groupby2Code, Q4.Groupby2Name, Q4.Groupby3Code, Q4.Groupby3Name, Q4.JournalDt_ID, Q4.Ledger_number, Q4.Ledger_name, Q4.ClientAddress, ".
				" Q4.CreditLimits, Q4.PaymentTerms, Q4.SalesmanCode, Q4.SalesmanName, Q4.ProjectCode, Q4.ProjectName, Q4.CostcentCode, Q4.CostcentName, Q4.Currency_code, Q4.PrevDebitAmount, Q4.PrevCreditAmount, ".
				" Q4.PrevBalance, Q4.Journaldt_desc, Q4.Journal_folio,Q4.JournalDt_DBCD, Q4.JDate,Q4.OrdDate,Q4.Days,Q4.Amount as AgingAmount, Q4.Balance, Q4.TotalCredits, Q4.TotalDebits, ".
				" @VarGrouping As GroupingOptions, ".
				" @VarGrouping := Concat(Q4.Groupby1Code, Q4.Groupby2Code, Q4.Groupby3Code, Q4.Ledger_number, Q4.Currency_code, ifNull(Q4.ProjectCode,''), ifNull(Q4.CostcentCode,'')), Q4.VDate ".
				" from ".
				" ( ".
				" Select Q1.Groupby1Code, Q1.Groupby1Name, Q1.Groupby2Code, Q1.Groupby2Name, Q1.Groupby3Code, Q1.Groupby3Name, Q2.JournalDt_ID, Q1.Ledger_number, Q1.Ledger_name, Q1.ClientAddress, ".
				" Q1.CreditLimits, Q1.PaymentTerms, Q1.SalesmanCode, Q1.SalesmanName, Q1.ProjectCode, Q1.ProjectName, Q1.CostcentCode, Q1.CostcentName, Q1.Currency_code, Q1.PrevDebitAmount, ".
				" Q1.PrevCreditAmount, (Q1.PrevDebitAmount - Q1.PrevCreditAmount) As PrevBalance, Q2.Journaldt_desc, Q2.Journal_folio,Q2.JournalDt_DBCD, Q2.JDate,Q2.OrdDate,".
				" ifNull(Q2.Days,0) As Days,ifNull(Q2.Amount,0) As Amount, ifNull(Q1.Balance,0) As Balance, ifNull(Q1.TotalCredits,0) As TotalCredits, ifNull(Q1.TotalDebits,0) As TotalDebits, Q2.VDate ".
				" from ".
				"(  ";
				
$j = 0;				
$k = 0; // Count For First Existsing Db
$QueryDirSchema = '';

$QueryDirSchema = " Select Ledger_number, Ledger_name, ClientAddress, CreditLimits, SalesmanCode, SalesmanName, ProjectCode, ProjectName, PaymentTerms, ".
                  " CostcentCode, CostcentName, Currency_code, ";
$QueryDirSchema = $QueryDirSchema.$QryGroupby1Code . $QryGroupby1Name.
								  $QryGroupby2Code . $QryGroupby2Name.
								  $QryGroupby3Code . $QryGroupby3Name;
								
$QueryDirSchema = $QueryDirSchema.					 
				 ", Sum(PrevDebitAmount) As PrevDebitAmount, Sum(PrevCreditAmount) As PrevCreditAmount, Sum(Balance) As Balance, Sum(TotalCredits) As TotalCredits, Sum(TotalDebits) As TotalDebits ".
				 " from ".
				 "(  ";


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

    $j = $j + 1;    
	//if ($RowsDirCount > 1)
	   $DirSchema = $RowsDir[0];
	//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 ($riskbal == 1 && $accountno == 2) 
					   $QueryDirSchema= $QueryDirSchema."  Select clients.Ledger_number, concat(clients.Ledger_name,' - ',COALESCE(Clients.Client_Remark,'')) Ledger_Name, ";
					else
					   $QueryDirSchema= $QueryDirSchema."  Select JournalDt.Ledger_number, ledger.Ledger_name Ledger_Name,  ";
			
					if ($accountno == 1) {
					  $QueryDirSchema= $QueryDirSchema."Suppliers.Supplier_CreditLimit CreditLimits, Null BusinessType, Null ProfitCenter, Null SalesmanCode, Null SalesmanName, Null as ClientAddress,  Suppliers.Supplier_PaymentTerms as PaymentTerms,";
					}  
					else if ($accountno == 2) {
					  $QueryDirSchema= $QueryDirSchema."Clients.Client_CreditLimit CreditLimits, Clients.Client_BusinessType As BusinessType, Clients.ProfitCenter_Code As ProfitCenter, Salesmen.Ledger_Number SalesmanCode,Salesmen.Ledger_Name SalesmanName, concat(ifNull(trim(Clients.Client_Address),''), '-', ifNull(trim(Clients.Client_Telephone1),'')) as ClientAddress, Clients.Client_PaymentTerms PaymentTerms, ";
					}  
					else if ($accountno == 3)  {
					  $QueryDirSchema= $QueryDirSchema." '' CreditLimits, Null BusinessType, Null ProfitCenter, Salesmen.Ledger_Number SalesmanCode, Salesmen.Ledger_Name SalesmanName, Null as ClientAddress, Null PaymentTerms, ";
					}      
					
					//if ($SelProject == 1 || $SelCostCenter == 1 || $prtcstjour == 1) 
					if ($prtcstjour == 1) 
						$QueryDirSchema= $QueryDirSchema." if($SelProject = 1, ifNull(CstJour.project_code,'None'), '') As ProjectCode, if($SelProject = 1, Project.Project_Name, '') As ProjectName, if($SelCostCenter = 1, ifNull(CstJour.Costcent_Code,'None'), '') As CostcentCode, if($SelCostCenter = 1, Costcent.Costcent_name, '') As CostcentName, ".
														 " if($SelProject = 1, ifNull(Project.Parent_Code, Project.Project_Code), '') As ParentProjectCode, if($SelProject = 1, Project.Project_Name, '') As ParentProjectName, ".
														 " if($SelCostCenter = 1, ifNull(Costcent.Parent_Code, Costcent.Costcent_Code), '') As ParentCostcentCode, if($SelCostCenter = 1, Costcent.Costcent_Name, '') As ParentCostcentName, ";
					else
						$QueryDirSchema= $QueryDirSchema." Null As ProjectCode, Null As ProjectName, Null As CostcentCode, Null As CostcentName, Null As ParentProjectCode, Null As ParentProjectName, Null As ParentCostcentCode, Null As ParentCostcentName, ";
					
					if ($SelMasterProject == 1 || $SelMasterCostCenter == 1) 
						$QueryDirSchema= $QueryDirSchema." if($SelMasterProject = 1, ifNull($tablename.project_code,'None'), '') As MasterProjectCode, if($SelMasterProject = 1, Project1.Project_Name, '') As MasterProjectName, if($SelMasterCostCenter = 1, ifNull($tablename.Costcenter_Code,'None'), '') As MasterCostcentCode, if($SelMasterCostCenter = 1, Costcent1.Costcent_name, '') As MasterCostcentName, ".
														 " if($SelMasterProject = 1, ifNull(Project1.Parent_Code, $tablename.Project_Code), '') As ParentMasterProjectCode, if($SelMasterProject = 1, Project1.Project_Name, '') As ParentMasterProjectName, ".
														 " if($SelMasterCostCenter = 1, ifNull(Costcent1.Parent_Code, $tablename.CostCenter_Code), '') As ParentMasterCostcentCode, if($SelMasterCostCenter = 1, Costcent1.Costcent_name, '') As ParentMasterCostcentName, ";
					else
						$QueryDirSchema= $QueryDirSchema." Null As MasterProjectCode, Null As MasterProjectName, Null As MasterCostcentCode, Null As MasterCostcentName, Null As ParentMasterProjectCode, Null As ParentMasterProjectName, Null As ParentMasterCostcentCode, Null As ParentMasterCostcentName, ";
						
						
				    $QueryDirSchema= $QueryDirSchema." if(upper($curr) = '".$Base1."', '".$Base1."', if(upper($curr) = '".$Base2."', '".$Base2."', JournalDt.Currency_code)) Currency_code , ";
					

					//if ($fromproject != 'Null' || $fromcst != 'Null' || $prtcstjour == 1) {
					if ($prtcstjour == 1) { 
				       $QueryDirSchema= $QueryDirSchema." sum(if(date_format(JournalDt.Journal_date,'%Y-%m-%d') < ifNull(". $fromdate.",date_format(JournalDt.Journal_date,'%Y-%m-%d')) , if(Journaldt_dbcd = 'D', if(upper($curr) = '".$Base1."', ifNull(Cstjour_amount1,Journaldt_amount1), if(upper($curr) = '".$Base2."', ifNull(Cstjour_amount2,Journaldt_amount2), ifNull(Cstjour_amount,Journaldt_amount))), 0),0)) PrevDebitAmount, ".
														" sum(if(date_format(JournalDt.Journal_date,'%Y-%m-%d') < ifNull(". $fromdate.",date_format(JournalDt.Journal_date,'%Y-%m-%d')) , if(Journaldt_dbcd = 'C', if(upper($curr) = '".$Base1."', ifNull(Cstjour_amount1,Journaldt_amount1), if(upper($curr) = '".$Base2."', ifNull(Cstjour_amount2,Journaldt_amount2), ifNull(Cstjour_amount,Journaldt_amount))), 0),0)) PrevCreditAmount, ".
														" Sum(if(date_format(JournalDt.Journal_date,'%Y-%m-%d') between ifNull(". $fromdate.",date_format(JournalDt.Journal_date,'%Y-%m-%d')) and ifNull(". $todate.",date_format(JournalDt.Journal_date,'%Y-%m-%d')), if(upper($curr) = '".$Base1."', if(Journaldt_dbcd = 'D', ifNull(Cstjour_amount1,Journaldt_amount1), (-1)*ifNull(Cstjour_amount1,Journaldt_amount1)), if(upper($curr) = '".$Base2."', if(Journaldt_dbcd = 'D', ifNull(Cstjour_amount2,Journaldt_amount2), (-1)*ifNull(Cstjour_amount2,Journaldt_amount2)), if(Journaldt_dbcd = 'D', ifNull(Cstjour_amount,Journaldt_amount), (-1)*ifNull(Cstjour_amount,Journaldt_amount)))),0) ) Balance, ".
														" Sum(if(date_format(JournalDt.Journal_date,'%Y-%m-%d') between ifNull(". $fromdate.",date_format(JournalDt.Journal_date,'%Y-%m-%d')) and ifNull(". $todate.",date_format(JournalDt.Journal_date,'%Y-%m-%d')), if(upper($curr) = '".$Base1."', if(Journaldt_dbcd = 'C'  , ifNull(Cstjour_amount1,Journaldt_amount1), 0), if(upper($curr) = '".$Base2."', if(Journaldt_dbcd = 'C' , ifNull(Cstjour_amount2,Journaldt_amount2), 0), if(Journaldt_dbcd = 'C' , ifNull(Cstjour_amount,Journaldt_amount), 0))),0) ) TotalCredits, ".
														" Sum(if(date_format(JournalDt.Journal_date,'%Y-%m-%d') between ifNull(". $fromdate.",date_format(JournalDt.Journal_date,'%Y-%m-%d')) and ifNull(". $todate.",date_format(JournalDt.Journal_date,'%Y-%m-%d')), if(upper($curr) = '".$Base1."', if(Journaldt_dbcd = 'D' , ifNull(Cstjour_amount1,Journaldt_amount1), 0), if(upper($curr) = '".$Base2."', if(Journaldt_dbcd = 'D' , ifNull(Cstjour_amount2,Journaldt_amount2), 0), if(Journaldt_dbcd = 'D' , ifNull(Cstjour_amount,Journaldt_amount), 0))),0) ) TotalDebits ";
                     } else {
					   $QueryDirSchema= $QueryDirSchema." sum(if(date_format(JournalDt.Journal_date,'%Y-%m-%d') < ifNull(". $fromdate.",date_format(JournalDt.Journal_date,'%Y-%m-%d')) , if(Journaldt_dbcd = 'D', if(upper($curr) = '".$Base1."', Journaldt_amount1, if(upper($curr) = '".$Base2."', Journaldt_amount2, Journaldt_amount)), 0),0)) PrevDebitAmount, ".
														" sum(if(date_format(JournalDt.Journal_date,'%Y-%m-%d') < ifNull(". $fromdate.",date_format(JournalDt.Journal_date,'%Y-%m-%d')) , if(Journaldt_dbcd = 'C', if(upper($curr) = '".$Base1."', Journaldt_amount1, if(upper($curr) = '".$Base2."', Journaldt_amount2, Journaldt_amount)), 0),0)) PrevCreditAmount, ".
														" Sum(if(date_format(JournalDt.Journal_date,'%Y-%m-%d') between ifNull(". $fromdate.",date_format(JournalDt.Journal_date,'%Y-%m-%d')) and ifNull(". $todate.",date_format(JournalDt.Journal_date,'%Y-%m-%d')), if(upper($curr) = '".$Base1."', if(Journaldt_dbcd = 'D', Journaldt_amount1, (-1)*Journaldt_amount1), if(upper($curr) = '".$Base2."', if(Journaldt_dbcd = 'D', Journaldt_amount2, (-1)*Journaldt_amount2), if(Journaldt_dbcd = 'D', Journaldt_amount, (-1)*Journaldt_amount))),0) ) Balance, ".
														" Sum(if(date_format(JournalDt.Journal_date,'%Y-%m-%d') between ifNull(". $fromdate.",date_format(JournalDt.Journal_date,'%Y-%m-%d')) and ifNull(". $todate.",date_format(JournalDt.Journal_date,'%Y-%m-%d')), if(upper($curr) = '".$Base1."', if(Journaldt_dbcd = 'C'  , Journaldt_amount1, 0), if(upper($curr) = '".$Base2."', if(Journaldt_dbcd = 'C'  , Journaldt_amount2, 0), if(Journaldt_dbcd = 'C' , Journaldt_amount, 0))),0) ) TotalCredits, ".
														" Sum(if(date_format(JournalDt.Journal_date,'%Y-%m-%d') between ifNull(". $fromdate.",date_format(JournalDt.Journal_date,'%Y-%m-%d')) and ifNull(". $todate.",date_format(JournalDt.Journal_date,'%Y-%m-%d')), if(upper($curr) = '".$Base1."', if(Journaldt_dbcd = 'D' , Journaldt_amount1, 0), if(upper($curr) = '".$Base2."', if(Journaldt_dbcd = 'D' , Journaldt_amount2, 0), if(Journaldt_dbcd = 'D' , Journaldt_amount, 0))),0) ) TotalDebits ";
						}
					
					$QueryDirSchema= $QueryDirSchema.
				    " from $DirSchema.JournalDt As JournalDt ".
				    " Inner join $DirSchema.Journal As Journal on JournalDt.Journal_id = Journal.Journal_id ";
				    //" Inner join $tablename on JournalDt.Ledger_number = $tablename.Ledger_number ";
					
					
					if ($riskbal == 1 && $accountno == 2) {
					   $QueryDirSchema= $QueryDirSchema."   Inner join ".
														"   ( select  clients.ledger_number, ledger.Ledger_name, clients.ledger_number accountnumber, Clients.Client_CreditLimit, Clients.Client_PaymentTerms, Clients.Client_BusinessType, Clients.ProfitCenter_Code, Clients.Salesmen_Code ,clients.project_code, Clients.Client_Address, Clients.Client_Telephone1 ".
														"     from clients ".
														"     Inner join ledger on clients.Ledger_number = ledger.Ledger_number ".
														"     where Clients.Ledger_Number between ifNull(".$fromledger.",Clients.Ledger_Number) and ifNull(". $toledger.",Clients.Ledger_Number) ".
														"     And upper(ifNull(Clients.CostCenter_Code,'-1')) like ifNull(upper(".$cltcst."), upper(ifNull(Clients.CostCenter_Code,'-1')))   ".
														//maroun
														"     And upper(ifNull(Clients.Project_Code,'-1')) like ifNull(upper(".$cltprj."), upper(ifNull(Clients.Project_Code,'-1')))   ".
														"     union ".													
														"     select clients.ledger_number, ledger.Ledger_name, concat(acctypesetup.ledger_number,substr(clients.ledger_number,5,length(clients.ledger_number))) accountnumber, Clients.Client_CreditLimit, Clients.Client_PaymentTerms, Clients.Client_BusinessType, Clients.ProfitCenter_Code, Clients.Salesmen_Code,clients.project_code, Clients.Client_Address, Clients.Client_Telephone1 ".
														"     from clients ".
														"     Inner join ledger on clients.Ledger_number = ledger.Ledger_number ".
														"     left join acctype on substr(clients.ledger_number,1,4) = acctype.acctype_ledger ".
														"     left join acctypesetup on upper(acctypesetup.type_code) = upper(acctype.acctype_code) ".
														"     where Clients.Ledger_Number between ifNull(".$fromledger.",Clients.Ledger_Number) and ifNull(". $toledger.",Clients.Ledger_Number) ".
														"     And upper(ifNull(Clients.CostCenter_Code,'-1')) like ifNull(upper(".$cltcst."), upper(ifNull(Clients.CostCenter_Code,'-1')))   ".
														//maroun
														"     And upper(ifNull(Clients.Project_Code,'-1')) like ifNull(upper(".$cltprj."), upper(ifNull(Clients.Project_Code,'-1')))   ".
														"     And acctypesetup.ledger_number Is Not Null ".
														"    ) Clients on JournalDt.Ledger_Number = Clients.accountnumber  ";
													
					} else {
					   $QueryDirSchema= $QueryDirSchema." Inner join ledger on JournalDt.Ledger_number = ledger.Ledger_number ".
														" Inner join $tablename on JournalDt.Ledger_number = $tablename.Ledger_number ";
					}
			   
					if ($SelMasterProject == 1 || $SelMasterCostCenter == 1) {
						$QueryDirSchema= $QueryDirSchema." Left Join Project As Project1 on $tablename.Project_Code = Project1.Project_Code ".
														 " Left Join Costcent As Costcent1 on $tablename.Costcenter_Code = Costcent1.Costcent_Code ";
					}

					if ($accountno == 2 || $accountno == 3 )			   
					  $QueryDirSchema= $QueryDirSchema."  Left Join Salesmen As Salesmen On Clients.Salesmen_Code = Salesmen.Ledger_Number ";

                    //if ($fromproject != 'Null' || $fromcst != 'Null' || $SelProject == 1 || $SelCostCenter == 1 || $prtcstjour == 1) {
					if ($prtcstjour == 1) {
						$QueryDirSchema= $QueryDirSchema." Left outer Join $DirSchema.CstJour As CstJour on Journaldt.JournalDt_ID = Cstjour.JournalDt_ID ".
														 " Left Join Project on CstJour.Project_Code = Project.Project_Code ".
														 " Left Join Costcent on CstJour.Costcent_Code = Costcent.Costcent_Code ";	

						if ($projectexist > 0 && $FI_RepRestrictProject == 1)
							$QueryDirSchema= $QueryDirSchema." Inner join ( select distinct project_code from ".$_SESSION['dbLabel'].".loginstk ".
															 "			    where loginstk.user_account='".$_SESSION['useraccount']."'  and Upper(loginstk.company_name) = Upper('".$_SESSION['dbName']."') ".
															 "              and (project_code is not null and project_code != '') ".
															 "			  ) PrjQry on CstJour.project_code like concat(PrjQry.project_code,'%') ";
														 
						if ($costcentexist > 0 && $FI_RepRestrictCostcenter == 1)
							$QueryDirSchema= $QueryDirSchema." Inner join ( select distinct costcent_code from ".$_SESSION['dbLabel'].".loginstk ".
															 "			    where loginstk.user_account='".$_SESSION['useraccount']."'  and Upper(loginstk.company_name) = Upper('".$_SESSION['dbName']."') ".
															 "              and (costcent_code is not null and costcent_code != '') ".
															 "			  ) CstQry on CstJour.Costcent_code like concat(CstQry.costcent_code,'%')  ";
															 
                    } else {
						if ($projectexist > 0 && $FI_RepRestrictProject == 1)
							$QueryDirSchema= $QueryDirSchema." Inner join ( select distinct project_code from ".$_SESSION['dbLabel'].".loginstk ".
															 "			    where loginstk.user_account='".$_SESSION['useraccount']."'  and Upper(loginstk.company_name) = Upper('".$_SESSION['dbName']."') ".
															 "              and (project_code is not null and project_code != '') ".
															 "			  ) PrjQry on Journal.project_code like concat(PrjQry.project_code,'%') ";
														 
						if ($costcentexist > 0 && $FI_RepRestrictCostcenter == 1)
							$QueryDirSchema= $QueryDirSchema." Inner join ( select distinct costcent_code from ".$_SESSION['dbLabel'].".loginstk ".
															 "			    where loginstk.user_account='".$_SESSION['useraccount']."'  and Upper(loginstk.company_name) = Upper('".$_SESSION['dbName']."') ".
															 "              and (costcent_code is not null and costcent_code != '') ".
															 "			  ) CstQry on Journal.Costcent_code like concat(CstQry.costcent_code,'%')  ";
					}
					
					//maroun
					if (($accountno == 2 || $accountno == 3) && $projectmasterexist > 0) {	
					    $QueryDirSchema= $QueryDirSchema." inner join ( select distinct Masterproject from ".$_SESSION['dbLabel'].".loginstk ".
														 "			   where loginstk.user_account='".$_SESSION['useraccount']."' and Upper(loginstk.company_name) = Upper('".$_SESSION['dbName']."')  ".
														 "             and (Masterproject is not null and Masterproject != '') ".
														 "			 ) PrjMasterQry on Clients.project_code like concat(PrjMasterQry.Masterproject,'%') ";
						
					}
			
					//maroun
					if (($accountno == 2 || $accountno == 3) && $costcentermasterexist > 0) {	
					    $QueryDirSchema= $QueryDirSchema." inner join ( select distinct Mastercostcenter from ".$_SESSION['dbLabel'].".loginstk ".
														 "			   where loginstk.user_account='".$_SESSION['useraccount']."'  and Upper(loginstk.company_name) = Upper('".$_SESSION['dbName']."') ".
														 "             and (Mastercostcenter is not null and Mastercostcenter != '') ".
														 "			 ) CstMasterQry on Clients.CostCenter_Code like concat(CstMasterQry.Mastercostcenter,'%') ";
						
					}
						
						
													 
                    if ($riskbal == 1 && $accountno == 2)
					    //$QueryDirSchema= $QueryDirSchema." where JournalDt.JournalDt_Amount1 <> 0 ";
						$QueryDirSchema= $QueryDirSchema." where 1 = 1 ";
					else {	
					    if ($accountno == 3) {
							$QueryDirSchema= $QueryDirSchema." where Salesmen.Ledger_number between ifNull(".$fromledger.",Salesmen.Ledger_number) and ifNull(". $toledger.",Salesmen.Ledger_number)  ";
															 //" And JournalDt.JournalDt_Amount1 <> 0 ";
						} else {							 
						    $QueryDirSchema= $QueryDirSchema." where JournalDt.Ledger_number between ifNull(".$fromledger.",JournalDt.Ledger_number) and ifNull(". $toledger.",JournalDt.Ledger_number)  ";
															 //" And JournalDt.JournalDt_Amount1 <> 0 ";
						}								 
					}
					//maroun
					if (($accountno == 2 || $accountno == 3) && $salesmenexist > 0) {
						$QueryDirSchema = $QueryDirSchema . " and clients.Salesmen_Code in 
															(select distinct clientsalesmen from ".$_SESSION['dbLabel'].".loginstk where user_account = '".$_SESSION['useraccount']."' and Upper(loginstk.company_name) = Upper('".$_SESSION['dbName']."') 
															 and (clientsalesmen !='' and clientsalesmen is not null)
															)";	
					}
						
					if ($RowsDirCount > 1 && $k != 1)	
						$QueryDirSchema= $QueryDirSchema." And Journal.Journal_Type <> 'OP' ";
						
                    if (($fromproject != 'Null' || $fromcst != 'Null' || $SelProject == 1 || $SelCostCenter == 1) && $prtcstjour == 1) {
						$QueryDirSchema= $QueryDirSchema." and upper(ifNull(CstJour.project_code,'-1')) like ifNull(upper(".$fromproject."), upper(ifNull(CstJour.project_code,'-1'))) ".
														 " and upper(ifNull(CstJour.Costcent_Code,'-1')) like ifNull(upper(".$fromcst."), upper(ifNull(CstJour.Costcent_Code,'-1')))   ";
                    } 
					else if (($fromproject != 'Null' || $fromcst != 'Null' || $SelProject == 1 || $SelCostCenter == 1) && $prtcstjour != 1) {
						$QueryDirSchema= $QueryDirSchema." 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 ($subproj != 'Null' && ($SelProject == 1 || $SelCostCenter == 1) && $prtcstjour == 1){
						$QueryDirSchema= $QueryDirSchema." And ifNull(CstJour.project_code,'-1')  Like ".$subproj; 
					} 
					else if ($subproj != 'Null' && ($SelProject == 1 || $SelCostCenter == 1) && $prtcstjour != 1){
						$QueryDirSchema= $QueryDirSchema." And ifNull(Journal.project_code,'-1')  Like ".$subproj; 
					}	
					if ($subcostcent != 'Null' && ($SelProject == 1 || $SelCostCenter == 1) && $prtcstjour == 1){
						$QueryDirSchema= $QueryDirSchema." And ifNull(CstJour.Costcent_Code,'-1')  Like ".$subcostcent;  
					} 
					else if ($subcostcent != 'Null' && ($SelProject == 1 || $SelCostCenter == 1) && $prtcstjour != 1){
						$QueryDirSchema= $QueryDirSchema." And ifNull(Journal.Costcent_Code,'-1')  Like ".$subcostcent;  
					}
					
					if ($riskbal == 0 && $accountno == 2) {
                        $QueryDirSchema= $QueryDirSchema." And upper(ifNull(Clients.CostCenter_Code,'-1')) like ifNull(upper(".$cltcst."), upper(ifNull(Clients.CostCenter_Code,'-1')))   ";
						//maroun
                        $QueryDirSchema= $QueryDirSchema." And upper(ifNull(Clients.Project_Code,'-1')) like ifNull(upper(".$cltprj."), upper(ifNull(Clients.Project_Code,'-1')))   ";
					}
					$QueryDirSchema .=  " And ifNull(journal.Journal_BelongsToGroup,0) >= ifNull($FI_BelongsToGrp,0) ";

					//if ($accountno == 2 && $SelSalesman == 1) {
					if ($accountno == 2) {
                        $QueryDirSchema= $QueryDirSchema." And ifNull(Clients.Salesmen_Code,'-1') between ifNull(".$fromsalesman.",ifNull(Clients.Salesmen_Code,'-1')) and ifNull(". $tosalesman.",ifNull(Clients.Salesmen_Code,'-1'))  ";
					}
					

					
					if ($riskbal == 1 && $accountno == 2) {
                        $QueryDirSchema= $QueryDirSchema." group by clients.Ledger_number, clients.Ledger_name, Currency_code, CreditLimits, BusinessType, SalesmanCode, SalesmanName, ProjectCode, CostcentCode, MasterProjectCode, MasterCostcentCode ";
					} else {				
						$QueryDirSchema= $QueryDirSchema." group by JournalDt.Ledger_number, ledger.Ledger_name, Currency_code, CreditLimits, BusinessType, SalesmanCode, SalesmanName, ProjectCode, CostcentCode, MasterProjectCode, MasterCostcentCode ";
					}	
					
					if ($RowsDirCount > 1 && $j < $RowsDirCount)	
                        $QueryDirSchema= $QueryDirSchema." Union ";
	}			
} // End Loop For Directories

$QueryDirSchema= $QueryDirSchema." order by Ledger_number, Currency_code, ProjectCode, CostcentCode, MasterProjectCode, MasterCostcentCode ";	
$QueryDirSchema= $QueryDirSchema.
				 " ) QDirMaster ".
				 " Group By Groupby1Code, Groupby2Code, Groupby3Code, Ledger_number, Currency_code ".
                 " Order By Groupby1Code, Groupby2Code, Groupby3Code, Ledger_number, Currency_code, ProjectCode, CostcentCode ";
				 
	
//echo $QueryDirSchema; exit;					

$queryjournal= $queryjournal." ".$QueryDirSchema.
				 " ) As Q1 Left Join  ". 
				 " ( ";
				 
				 
$j = 0;				
$k = 0;
$QueryDirSchemaDt = '';
$QueryDirSchemaDt = " Select JournalDt_ID, Ledger_number,  Currency_code, ProjectCode, CostcentCode, Journaldt_desc, Journal_folio, ".
				    " JournalDt_DBCD, JDate, OrdDate, Days, Amount, VDate ".
				    " from ".
				    " (  ";

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

    $j = $j + 1;    
	//if ($RowsDirCount > 1)
	   $DirSchema = $RowsDir[0];
	//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;    		
		$QueryDirSchemaDt= $QueryDirSchemaDt.  				 
				 " Select JournalDt.JournalDt_ID, JournalDt.Ledger_number, if(upper($curr) = '".$Base1."', '".$Base1."', if(upper($curr) = '".$Base2."', '".$Base2."', JournalDt.Currency_code)) Currency_code, ".
				 "  Journaldt_desc, Journal_folio, JournalDt_DBCD,  JournalDt.Journal_date as JDate, JournalDt.Journaldt_ValueDate as VDate, Cast(if($dateoption = 1, JournalDt.Journal_date, JournalDt.Journaldt_ValueDate) As Date) As OrdDate, ".
                 "  DATEDIFF(date_format(".$agingdate.",'%Y-%m-%d'), date_format(if($dateoption = 1, JournalDt.Journal_date, JournalDt.Journaldt_ValueDate),'%Y-%m-%d')) Days, ";
					
					if ($SelProject == 1 || $SelCostCenter == 1) 
						$QueryDirSchemaDt= $QueryDirSchemaDt." if($SelProject = 1, ifNull(CstJour.project_code,'None'), '') As ProjectCode, if($SelCostCenter = 1, ifNull(CstJour.Costcent_Code,'None'), '') As CostcentCode, ";
					else
						$QueryDirSchemaDt= $QueryDirSchemaDt." Null As ProjectCode, Null As CostcentCode, ";
					
					
					//if ($fromproject != 'Null' || $fromcst != 'Null' || $SelProject == 1 || $SelCostCenter == 1 || $prtcstjour == 1) 
				if ($prtcstjour == 1) 
					    $QueryDirSchemaDt= $QueryDirSchemaDt."  if(upper($curr) = '".$Base1."', ifNull(Cstjour_amount1,Journaldt_amount1), if(upper($curr) = '".$Base2."', ifNull(Cstjour_amount2,Journaldt_amount2), ifNull(Cstjour_amount,Journaldt_amount)))*IF(JOURNALDT_DBCD='C' AND JOURNAL_TYPE IN ('DR','SR'),-1,1) Amount ";
					else
					    $QueryDirSchemaDt= $QueryDirSchemaDt."  if(upper($curr) = '".$Base1."', Journaldt_amount1, if(upper($curr) = '".$Base2."', Journaldt_amount2, Journaldt_amount))*IF(JOURNALDT_DBCD='C' AND JOURNAL_TYPE IN ('DR','SR'),-1,1) Amount ";
						
					$QueryDirSchemaDt= $QueryDirSchemaDt."  from $DirSchema.JournalDt As JournalDt ".
														 "  Inner join $DirSchema.Journal As Journal on JournalDt.Journal_id = Journal.Journal_id ";
														 //"  Inner join $tablename on JournalDt.Ledger_number = $tablename.Ledger_number ".
					if ($accountno == 3 ) {			   
						$QueryDirSchemaDt= $QueryDirSchemaDt." Inner join Clients on JournalDt.Ledger_number = Clients.Ledger_number ".
															 " Left Join Salesmen As Salesmen On Clients.Salesmen_Code = Salesmen.Ledger_Number ";
					}	  
					  
				    //if ($fromproject != 'Null' || $fromcst != 'Null' || $SelProject == 1 || $SelCostCenter == 1 || $prtcstjour == 1) {	
					if ($prtcstjour == 1) {
					    $QueryDirSchemaDt= $QueryDirSchemaDt."  Left outer Join $DirSchema.CstJour As CstJour on Journaldt.JournalDt_ID = Cstjour.JournalDt_ID ";
					  
						if ($projectexist > 0  && $FI_RepRestrictProject == 1)
							$QueryDirSchemaDt= $QueryDirSchemaDt." Inner join ( select distinct project_code from ".$_SESSION['dbLabel'].".loginstk ".
																 "			    where loginstk.user_account='".$_SESSION['useraccount']."'  and Upper(loginstk.company_name) = Upper('".$_SESSION['dbName']."') ".
																 "              and (project_code is not null and project_code != '') ".
															     "		      ) PrjQry1 on CstJour.project_code like concat(PrjQry1.project_code,'%') ";
															   
						if ($costcentexist > 0 && $FI_RepRestrictCostcenter == 1)
							$QueryDirSchemaDt= $QueryDirSchemaDt." Inner join ( select distinct costcent_code from ".$_SESSION['dbLabel'].".loginstk ".
															     "			    where loginstk.user_account='".$_SESSION['useraccount']."'  and Upper(loginstk.company_name) = Upper('".$_SESSION['dbName']."') ".
														         "              and (costcent_code is not null and costcent_code != '') ".
														         "			  ) CstQry1 on CstJour.Costcent_code like concat(CstQry1.costcent_code,'%')  ";
						 
				    } else {
						if ($projectexist > 0  && $FI_RepRestrictProject == 1)
							$QueryDirSchemaDt= $QueryDirSchemaDt." Inner join ( select distinct project_code from ".$_SESSION['dbLabel'].".loginstk ".
																 "			    where loginstk.user_account='".$_SESSION['useraccount']."'  and Upper(loginstk.company_name) = Upper('".$_SESSION['dbName']."') ".
																 "              and (project_code is not null and project_code != '') ".
															     "		      ) PrjQry1 on Journal.project_code like concat(PrjQry1.project_code,'%') ";
															   
						if ($costcentexist > 0 && $FI_RepRestrictCostcenter == 1)
							$QueryDirSchemaDt= $QueryDirSchemaDt." Inner join ( select distinct costcent_code from ".$_SESSION['dbLabel'].".loginstk ".
															     "			    where loginstk.user_account='".$_SESSION['useraccount']."'  and Upper(loginstk.company_name) = Upper('".$_SESSION['dbName']."') ".
														         "              and (costcent_code is not null and costcent_code != '') ".
														         "			  ) CstQry1 on Journal.Costcent_code like concat(CstQry1.costcent_code,'%')  ";
					}
					
					
					//if($dateoption == 1) 
						$QueryDirSchemaDt= $QueryDirSchemaDt." where date_format(JournalDt.Journal_date,'%Y-%m-%d') between ifNull(". $fromdate.",date_format(JournalDt.Journal_date,'%Y-%m-%d')) and ifNull(". $todate.",date_format(JournalDt.Journal_date,'%Y-%m-%d'))   ";
					//else
						//$QueryDirSchemaDt= $QueryDirSchemaDt." where date_format(JournalDt.Journaldt_ValueDate,'%Y-%m-%d') between ifNull(". $fromdate.",date_format(JournalDt.Journaldt_ValueDate,'%Y-%m-%d')) and ifNull(". $todate.",date_format(JournalDt.Journaldt_ValueDate,'%Y-%m-%d'))   ";

                    if (($fromproject != 'Null' || $fromcst != 'Null' || $SelProject == 1 || $SelCostCenter == 1) && $prtcstjour == 1) {	
						$QueryDirSchemaDt= $QueryDirSchemaDt." and upper(ifNull(CstJour.project_code,'-1')) like ifNull(upper(".$fromproject."), upper(ifNull(CstJour.project_code,'-1'))) ".
															 " and upper(ifNull(CstJour.Costcent_Code,'-1')) like ifNull(upper(".$fromcst."), upper(ifNull(CstJour.Costcent_Code,'-1')))   ";
					} 
					else if (($fromproject != 'Null' || $fromcst != 'Null' || $SelProject == 1 || $SelCostCenter == 1) && $prtcstjour != 1) {
						$QueryDirSchemaDt= $QueryDirSchemaDt." 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 (($subproj != 'Null' && ($SelProject == 1 || $SelCostCenter == 1) && $prtcstjour == 1)){
						$QueryDirSchemaDt= $QueryDirSchemaDt." And ifNull(CstJour.project_code,'-1')  Like ".$subproj; 
					} 
					else if (($subproj != 'Null' && ($SelProject == 1 || $SelCostCenter == 1) && $prtcstjour != 1)){
						$QueryDirSchemaDt= $QueryDirSchemaDt." And ifNull(Journal.project_code,'-1')  Like ".$subproj; 
					}	
					if (($subcostcent != 'Null' && ($SelProject == 1 || $SelCostCenter == 1) && $prtcstjour == 1)){
						$QueryDirSchemaDt= $QueryDirSchemaDt." And ifNull(CstJour.Costcent_Code,'-1')  Like ".$subcostcent;  
					} 
					else if (($subcostcent != 'Null' && ($SelProject == 1 || $SelCostCenter == 1) && $prtcstjour != 1)){
						$QueryDirSchemaDt= $QueryDirSchemaDt." And ifNull(Journal.Costcent_Code,'-1')  Like ".$subcostcent;  
					}
					
                    
					if ($accountno != 3) 
					    $QueryDirSchemaDt= $QueryDirSchemaDt." And JournalDt.Ledger_Number between ifNull(".$fromledger.",JournalDt.Ledger_Number) and ifNull(". $toledger.",JournalDt.Ledger_Number) ";
					else
                        $QueryDirSchemaDt= $QueryDirSchemaDt." And Salesmen.Ledger_number between ifNull(".$fromledger.",Salesmen.Ledger_number) and ifNull(". $toledger.",Salesmen.Ledger_number)  ";					
					

					
					$QueryDirSchemaDt= $QueryDirSchemaDt." And (($accountno=1 and Journaldt_dbcd = 'C') or ($accountno!=1 and (Journaldt_dbcd = 'D'  )                                   )) ";
					$QueryDirSchemaDt= $QueryDirSchemaDt." And (JournalDt.JournalDt_Amount <> 0 or JournalDt.JournalDt_Amount1 <> 0 or JournalDt.JournalDt_Amount2 <> 0) ";


					if ($RowsDirCount > 1 && $k != 1)	
						$QueryDirSchemaDt= $QueryDirSchemaDt." And Journal.Journal_Type <> 'OP' ";
						$QueryDirSchemaDt .=  " And ifNull(journal.Journal_BelongsToGroup,0) >= ifNull($FI_BelongsToGrp,0) ";
	                
					if ($RowsDirCount > 1 && $j < $RowsDirCount)	
                        $QueryDirSchemaDt= $QueryDirSchemaDt." Union ";
	}					
}


$QueryDirSchemaDt= $QueryDirSchemaDt." ) QDirDetail ".
									 " Order by Ledger_number, Currency_code, ProjectCode, CostcentCode, OrdDate, Journal_folio, JournalDt_ID ";


//echo $QueryDirSchemaDt;exit;					 
$queryjournal= $queryjournal." ".$QueryDirSchemaDt.											
	                ") Q2 on Q2.Ledger_Number = Q1.Ledger_Number And Q2.Currency_Code = Q1.Currency_Code ";
					
if ($SelProject == 1 || $SelCostCenter == 1)					
	$queryjournal= $queryjournal." And Q2.ProjectCode = Q1.ProjectCode And Q2.CostcentCode = Q1.CostcentCode ";

$queryjournal= $queryjournal.	
					" Order By Q1.Groupby1Code, Q1.Groupby2Code, Q1.Groupby3Code, Q2.Ledger_Number, Q2.Currency_code, Q2.ProjectCode, Q2.CostcentCode, Q2.OrdDate, Q2.Journal_folio, Q2.JournalDt_ID ".
				" ) As Q4  ".
				" Order By Q4.Groupby1Code, Q4.Groupby2Code, Q4.Groupby3Code, Q4.Ledger_Number, Q4.Currency_code, Q4.ProjectCode, Q4.CostcentCode, Q4.OrdDate, Q4.Journal_folio, Q4.JournalDt_ID ".
				" ) As Q5 ".
				" Order By Q5.Groupby1Code, Q5.Groupby2Code, Q5.Groupby3Code, Q5.Ledger_Number, Q5.Currency_code, Q5.ProjectCode, Q5.CostcentCode, Q5.OrdDate, Q5.Journal_folio, Q5.JournalDt_ID ".
				" ) As Q6  ".
				" where (($showbal=1 and Q6.Balance Between ifNull($rangefrom,0) and ifNull($rangeto,0)) or ($showbal=0 and 1=1)) ".
				" And (($accountno=1 and if(Q6.TotalDebits >= Q6.PrevBalance, Q6.AgingBalance < 0, 1 = 1)) or ($accountno!=1 and if(Q6.TotalCredits >= Q6.PrevBalance ,Q6.AgingBalance < 0, 1 = 1) )) ".
				" And Q6.Balance != 0  and round(if($accountno!=1,Q6.TotalDebits+Q6.PrevBalance-Q6.TotalCredits, Q6.TotalDebits+Q6.PrevBalance-Q6.TotalCredits),$DecimalBase) <> 0  
				and Q6.AgingAmount <> 0  ";
				//" AND if(".$accountno."=2,if(Q6.Balance<0,0,Q6.Balance),if(Q6.Balance>0,0,Q6.Balance)) != 0 ";
				
if($showdetail==1 and $termsdays <> 'Null')
	$queryjournal = $queryjournal." And Q6.Days > '$termsdays' ";
 if($byledgergrp == 1) $queryjournal .= " and Q6.Ledger_number like '".$ledgergrp_code."%' ";								
$queryjournal= $queryjournal." order by Q6.Groupby1Code, Q6.Groupby2Code, Q6.Groupby3Code, if($orderby=1, Q6.Ledger_number, Q6.Ledger_Name), Q6.Currency_code, Q6.OrdDate, Q6.Journal_folio, Q6.JournalDt_ID  ";
			
	
//echo $queryjournal;exit;
$ArrayTotalByReport   = Array();
$ArrayTotalByGroup1   = Array();
$ArrayTotalByGroup2   = Array();
$ArrayTotalByGroup3   = Array();

$MainRecord = '';
$MainGroup1 = '';
$MainGroup2 = '';
$MainGroup3 = '';
$groupby1desc = '';
$groupby2desc = '';
$groupby3desc = '';

$firstBalance = 0;	

$resultjournal=mysql_query($queryjournal,$connection) or die(mysql_error());
$numjournal = mysql_num_rows($resultjournal);

if($numjournal > 0 && $byledgergrp == 1){
	$ColumnNum = 0;
	$RowNum++;
	$worksheet->write($RowNum, $ColumnNum, "-->  ".$ledgergrp_code, $grouplevel1Format);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $ledgergrp_name, $grouplevel1Format);
	$ColumnNum = 0;
	$RowNum++;
}

	
while($rowsjournal=mysql_fetch_row($resultjournal)){

	//Group By Options Level 1 Names 
	if ( ($groupby1 != '' && ($showdetail == 1 || $groupby2 != '' || $groupby3 != 1)) && ($MainGroup1 == '' || $MainGroup1 != $rowsjournal[0])  ){
			
		$groupby1code = '';
		if ($rowsjournal[0] == -1)
		   $groupby1code = '';
		else
            $groupby1code = $rowsjournal[0];	

		$worksheet->write($RowNum, $ColumnNum, "Group by  ".$groupby1name, $grouplevel1Format);
		
		$ColumnNum += 1;
		$worksheet->write($RowNum, $ColumnNum, str_replace('*',' --> ',$groupby1code)." - ".str_replace('*',' --> ',$rowsjournal[1]), $grouplevel1Format);		
				
		//Increment the Rows Counter By 1 and Reset the Columns Counter
		$RowNum += 1;
		$ColumnNum = 0;	
		
		
		if  ($groupby1 != '' && $groupby2 == '' && $groupby3 == 1) {
			$RowNum += 1;
		}
				
						
		$MainGroup1   = $rowsjournal[0];
		$groupby1desc = $rowsjournal[1];
		
		$MainRecord   = '';
		$MainGroup2   = '';
		$groupby2desc = '';
		$MainGroup3   = '';
		$groupby3desc = '';	
		
		unset($ArrayTotalByGroup1);		
		unset($ArrayTotalByGroup2);		
		unset($ArrayTotalByGroup3);
		
	}
	
   
   //Group By Options Level 2 Names 
	if ( ($groupby2 != '' && ($showdetail == 1 || $groupby3 != 1)) && ($MainGroup2 == '' || $MainGroup2 != $rowsjournal[2])  ){
			
		$groupby2code = '';
		if ($rowsjournal[2] == -1)
		   $groupby2code = '';
		else
              $groupby2code = $rowsjournal[2];		

        $worksheet->write($RowNum, $ColumnNum, "Group by  ".$groupby2name, $grouplevel2Format);
		$ColumnNum += 1;
		$worksheet->write($RowNum, $ColumnNum, str_replace('*',' --> ',$groupby2code)." - ".str_replace('*',' --> ',$rowsjournal[3]), $grouplevel2Format);		
			
		//Increment the Rows Counter By 1 and Reset the Columns Counter
		$RowNum += 1;
		$ColumnNum = 0;			  
		
		if  ($groupby1 == '' && $groupby2 != '' && $groupby3 == 1) {
			$RowNum += 1;
		}
				
						
		$MainGroup2   = $rowsjournal[2];
		$groupby2desc = $rowsjournal[3];
		
		$MainRecord   = '';
		$MainGroup3   = '';
		$groupby3desc = '';	
		
		unset($ArrayTotalByGroup2);		
		unset($ArrayTotalByGroup3);

	}
		
	//Group By Options Level 3 Names 
	if ( ($groupby3 != 1 && $showdetail == 1) && ($MainGroup3 == '' || $MainGroup3 != $rowsjournal[4])  ){
			
		$groupby3code = '';
		if ($rowsjournal[4] == -1)
		   $groupby3code = '';
		else
            $groupby3code = $rowsjournal[4];	

		$worksheet->write($RowNum, $ColumnNum, "Group by  ".$groupby3name, $grouplevel3Format);
		$ColumnNum += 1;
		$worksheet->write($RowNum, $ColumnNum, str_replace('*',' --> ',$groupby3code)." - ".str_replace('*',' --> ',$rowsjournal[5]), $grouplevel3Format);		
				
		//Increment the Rows Counter By 1 and Reset the Columns Counter
		$RowNum += 2;
		$ColumnNum = 0;		
			   
						
		$MainGroup3   = $rowsjournal[4];
		$groupby3desc = $rowsjournal[5];
			
		$MainRecord = '';	
		unset($ArrayTotalByGroup3);
	}
	
       
	if ($MainRecord == '' || $MainRecord != $rowsjournal[6]) {
		
	    if($count > 0 && $numjournal > 0 ){
			$ActualBal = $totalDuePerAcc + $totalNonDuePerAcc;
			$ColumnNum = 0;
			$RowNum += 1;
			$worksheet->write($RowNum, $ColumnNum, "--> Total Bills Due:", $boldleftFormat);
			$ColumnNum += 1;
			$worksheet->writeNumber($RowNum, $ColumnNum, $totalDuePerAcc, $boldrightFormat);
			$ColumnNum += 1;
			$worksheet->write($RowNum, $ColumnNum, "--> Total Bills Not Due:", $boldleftFormat);
			$ColumnNum += 1;
			$worksheet->writeNumber($RowNum, $ColumnNum, $totalNonDuePerAcc, $boldrightFormat);
			$ColumnNum += 1;
			$worksheet->write($RowNum, $ColumnNum, "--> Total:", $boldleftFormat);
			$ColumnNum += 1;
			$worksheet->writeNumber($RowNum, $ColumnNum, $ActualBal, $boldrightFormat);
			$ColumnNum = 0;
			$RowNum += 1;
			$totalDuePerAcc = 0;
			$totalNonDuePerAcc = 0;
			$ActualBal = 0;
			$firstBalance = 0;	
		}
		if ($MainRecord != '') {
			$RowNum += 1;
		}	
		
		$worksheet->write($RowNum, $ColumnNum, $rowsjournal[6], $boldleftFormat);
		$ColumnNum += 1;
		//$worksheet->write($RowNum, $ColumnNum, $rowsjournal[7], $boldleftFormat);
		
		$ColumnNum += 1;
		$worksheet->writeNumber($RowNum, $ColumnNum, $rowsjournal[8], $boldrightFormat);
		$ColumnNum += 1;
		$worksheet->writeNumber($RowNum, $ColumnNum, $rowsjournal[32], $boldrightFormat);
		$ColumnNum += 1;
		$worksheet->writeNumber($RowNum, $ColumnNum, $rowsjournal[16], $boldrightFormat);
		$ColumnNum += 1;
		$worksheet->writeNumber($RowNum, $ColumnNum, $rowsjournal[17], $boldrightFormat);
		$ColumnNum += 1;
		$worksheet->write($RowNum, $ColumnNum, $rowsjournal[15], $boldleftFormat);
		$ColumnNum += 1;
		$worksheet->writeNumber($RowNum, $ColumnNum, $rowsjournal[18], $boldrightFormat);
		$ColumnNum += 1;
		$worksheet->writeNumber($RowNum, $ColumnNum, $rowsjournal[28], $boldrightFormat);
			
		if ($showaddress == 1 && $rowsjournal[31] != '-') {
			$RowNum += 1;
			$ColumnNum = 1;
			
			$worksheet->write($RowNum, $ColumnNum, $rowsjournal[31], $boldleftFormat);
		}
		
		
		$MainRecord = $rowsjournal[6];
		
		//Increment the Rows Counter By 1 and Reset the Columns Counter
		$RowNum += 1;
		$ColumnNum = 0;
		$totalDuePerAcc = 0;	
		$totalNonDuePerAcc = 0;	
	}	
			  
	
	if ($rowsjournal[27] != 0) {
		
		$worksheet->setMerge($RowNum, $ColumnNum, $RowNum, $ColumnNum + 2);
		$worksheet->write($RowNum, $ColumnNum, $rowsjournal[19], $regularleftFormat);
		$ColumnNum += 3;
		$worksheet->write($RowNum, $ColumnNum, $rowsjournal[20], $regularleftFormat);
		
		$ColumnNum += 1;
		//$worksheet->write($RowNum, $ColumnNum, $rowsjournal[22], $regularleftFormat);
	
	
	

		$worksheet->write($RowNum, $ColumnNum, ($rowsjournal[22]), $DateFormat);
		$ColumnNum += 1;
		
		if($showvdate == 1){
			$worksheet->write($RowNum, $ColumnNum, ($rowsjournal[33]), $DateFormat);
			$ColumnNum += 1;
		}
		$worksheet->write($RowNum, $ColumnNum, $rowsjournal[23], $regularleftFormat);
		//$worksheet->write($RowNum, $ColumnNum, 'X3', $boldleftFormat);
		$ColumnNum += 1;
		$worksheet->writeNumber($RowNum, $ColumnNum, $rowsjournal[25], $regularrightFormat);
		//$worksheet->write($RowNum, $ColumnNum, 'X4', $boldleftFormat);
		$ColumnNum += 1;
		$worksheet->writeNumber($RowNum, $ColumnNum, $rowsjournal[27], $regularrightFormat);
		
			
		
		//Increment the Rows Counter By 1 and Reset the Columns Counter
		$RowNum += 1;
		$ColumnNum = 0;
		if($firstBalance == 0 && $rowsjournal[34] != 0){
			$totalDuePerAcc =  abs($rowsjournal[27]);
		} else $totalDuePerAcc +=  abs($rowsjournal[34]);
		
		if($firstBalance == 0 && $rowsjournal[35] != 0){
			$totalNonDuePerAcc =  abs($rowsjournal[27]);
		} else $totalNonDuePerAcc +=  abs($rowsjournal[35]);
		
		$firstBalance++;
	}
	
	$count++;
}

if($numjournal > 0){
	$ActualBal = $totalDuePerAcc + $totalNonDuePerAcc;
	$ColumnNum = 0;
	$RowNum += 1;
	$worksheet->write($RowNum, $ColumnNum, "--> Total Bills Due:", $boldleftFormat);
	$ColumnNum += 1;
	$worksheet->writeNumber($RowNum, $ColumnNum, $totalDuePerAcc, $boldrightFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, "--> Total Bills Not Due:", $boldleftFormat);
	$ColumnNum += 1;
	$worksheet->writeNumber($RowNum, $ColumnNum, $totalNonDuePerAcc, $boldrightFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, "--> Total:", $boldleftFormat);
	$ColumnNum += 1;
	$worksheet->writeNumber($RowNum, $ColumnNum, $ActualBal, $boldrightFormat);
	$ColumnNum = 0;
	$RowNum += 1;
}
$result = mysql_query("rollback", $connection);
}

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 format_currency($amount, $DecimalBase) { 

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

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

?>