<?php

session_start();
header("Content-type: text/html; charset=".$_SESSION['encodingmode']);
if($_SESSION['dbName']==""){
header("location:../index.php");
}


print <<< HERE
<HTML>
<HEAD>
<TITLE> Aging of Client Invoices Report </TITLE>

<style type="text/css">
table.params td { font-size: 12px; 
                  font-family:Times-New-Roman;
                 }

			 
table.master th { font-size: 14px; 
                  font-weight: bold;
                  font-family:Times-New-Roman;
				 }
				 
table.master td { font-size: 12px; 
                  font-family:Times-New-Roman;
                 }
				 
table.master td.grouplevel1 { font-size: 14px; 
							  font-weight: bold;
                              font-family:Times-New-Roman;
						      color: #0000FF;
							  background-color: #F0F0F0;
                            }				 									  
table.master td.grouplevel2 { font-size: 14px; 
                              font-family:Times-New-Roman;
						      color: #FF0000;
							  background-color: #F0F0F0;
                            }

table.master td.grouplevel3 { font-size: 13px; 
							  font-style: italic;
                              font-family:Times-New-Roman;
						      color: #336600;
							  background-color: #F0F0F0;
                            }				 
				 
			   
table.totals  { font-size: 13px; 
                font-weight: bold;
                font-family:Times-New-Roman;
              }				

table.grouplevel1total td { font-size: 14px; 
							font-weight: bold;
							font-family:Times-New-Roman;
							color: #0000FF;
							background-color: #F0F0F0;
						   }				 
table.grouplevel3total td { font-size: 13px; 
							font-style: italic;
							font-family:Times-New-Roman;
							color: #336600;
							background-color: #F0F0F0;
						  }				 
table.grouplevel2total td { font-size: 14px; 
							font-family:Times-New-Roman;
							color: #FF0000;
							background-color: #F0F0F0;
						  }				  
				
				 


</style>




</HEAD>
HERE;
include("config.inc.php");

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

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

if(	$_SESSION['encodingmode'] == 'utf8' || $_SESSION['encodingmode'] == 'utf-8'){
	mysql_query("SET NAMES 'utf8'"); 
	mysql_query('SET CHARACTER SET utf8'); 
	mysql_set_charset('utf8', $connection);
}

$querylog="SELECT print as printaccess from ".$_SESSION['dbLabel'].".login where user_account='".$_SESSION['useraccount']."' 	and Upper(Login_Schema) = Upper('".$_SESSION['dbName']."')	";
$resultlog=mysql_query($querylog,$connection) or die(mysql_error());
while($rows=mysql_fetch_array($resultlog)){
	extract($rows);
}


if ($printaccess==0 or $printaccess=='')
echo'<BODY oncontextmenu="return false;">';
else
echo'<BODY>';

$querysetup="select DecimalBase, DecimalBase1, DecimalBase2, Base1, Base2, report_color1, report_color2, 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 ".
          " 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);
}

//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';  
   $displaycurr = '';
}   
else {
   $displaycurr = $curr;
   $curr="'".$curr."'";
}

$dispagingdate = $agingdate;

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

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

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

if(empty($prtreturn))    $prtreturn = 0;
if(empty($prtclosedinv)) $prtclosedinv = 0;
if(empty($riskbal))      $riskbal = 0;
if(empty($shownondues))  $shownondues = 0;

if($prtreturn == 1) $Label = 'Invoice/Return';
else $Label = 'Invoice';
	
  
print <<< HERE
<table width="100%" border="0" align="left" >
<tr><td>
<table width="100%" border="0" align="center">
<tr>
  <td width='30%'><b>$CompanyName</b></td>
  <td width='55%' align='center'><font size=4><b>Aging of Client Invoices</b></font></td>
  <td width='5%'><font size=1><b> Printed Date: </b> <br><b>Time: </b>
  <td width='10%'><font size=1>
HERE;

echo $printdate[2]."/".$printdate[1]."/".$printdate[0];
echo "<br>".$dt[1]." ".$dt[2];
  
print <<<HERE
</font></td>
</tr>
<tr>
   <td colspan='4' width='100%'> <hr width='100%' color='black' size='1'> </td>
</tr>
</table>

<table class="params" width="100%" border="0" align="center" >
<tr>
  <td width="100%" valign='top'><b>$SelectedOptions</b></td>
</tr>
<tr>
   <td width='100%'> <hr width='100%' color='black' size='1'> </td>
</tr>
</table>

HERE;

$queryperiods  = " select period from aging order by period ";
$resultperiods = mysql_query($queryperiods,$connection) or die(mysql_error());
$numrows = mysql_num_rows($resultperiods);

$ArrayPeriods = Array();
$i = 0;
while($rows=mysql_fetch_array($resultperiods)){
	$ArrayPeriods[$i] = $rows[0];
	$i += 1;
}

echo "<table width='100%' border='0' class='master' align='center' cellpadding='0' cellspacing='0'>";
echo "  <tr><td colspan='3'>";

//Create Table Columns
CreateTableStructure($numrows, $shownondues, $riskbal);		

echo"	<tr>";
echo"		<th align='left'>Account No.</th>
			<th >Account Name</th>
			<th>PT</th> 
			<th>CL</th>";

if ($riskbal == 1) {	
	echo "<th align='center'>CheckBal</th>";
}
	
echo "	    <th align='center'>Not Allocated Amount</th>";
echo "	    <th align='center'>Balance</th>";
echo "		<th>Cur.</th><th align='center'>Total Bal</th>";

if ($shownondues == 1) 	 echo"<th align='center'>Non Dues</th>";

$PrevRange = 0;
for ( $d = 0; $d < count($ArrayPeriods); $d += 1) {
	echo "	<th align='center'>".$PrevRange.'-'.$ArrayPeriods[$d]."</th>";
	$PrevRange = $ArrayPeriods[$d] + 1;
}

echo"		<th align='center'> > ".($PrevRange - 1)." </th>";

echo"     </tr>";
echo"	</table></td></tr>";	
echo"  <tr><td colspan='3'><hr width='100%' color='black' size='2'></td></tr>";


$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, DirYear ".
			" 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 ";
			
//echo $QueryDir; exit;
$ResultDir=mysql_query($QueryDir,$connection) or die(mysql_error());
$ResultDir1=mysql_query($QueryDir,$connection) or die(mysql_error());
$ResultDir2=mysql_query($QueryDir,$connection) or die(mysql_error());
$ResultDir3=mysql_query($QueryDir,$connection) or die(mysql_error());
$ResultDir4=mysql_query($QueryDir,$connection) or die(mysql_error());
$ResultDir5=mysql_query($QueryDir,$connection) or die(mysql_error());
$ResultDir6=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 ,@RecordNum:=0,@VarGrouping:='x' ", $connection);

$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 ClientCode, ClientName, CreditLimits, PaymentTerms, CurrencyCode, PrevAmount, ifNull(Balance,0) as Balance, ".
				" Sum(TransRemAmount) As TransRemAmount, Sum(NonDueAmount) As NonDueAmount, ifNull(CheckBalance,0) as CheckBalance, ".
				" NotAllocatedAmount, (Sum(TransRemAmount) + ifNull(CheckBalance,0) + ifNull(NotAllocatedAmount,0)) As AccountBal,  ".
				" Sum(TransPaidAmount) As TransPaidAmount, ";
				
			$PrevRange = 0;	
			for ( $d = 0; $d < count($ArrayPeriods); $d += 1) {
				$query= $query." Sum(Amount".$ArrayPeriods[$d].") As Amount".$ArrayPeriods[$d].", ";
				$PrevRange = $ArrayPeriods[$d] + 1;
			}
			
$query = $query." Sum(Amount".$PrevRange.") As Amount".$PrevRange.
				" From (  ";
$query = $query." Select ClientCode, ClientName, CreditLimits, PaymentTerms, CurrencyCode, PrevAmount, ifNull(Balance,0) as Balance, ".
				" Sum(TransRemAmount) As TransRemAmount, Sum(NonDueAmount) As NonDueAmount, ifNull(CheckBalance,0) as CheckBalance, ".
				" NotAllocatedAmount, (Sum(TransRemAmount) + ifNull(CheckBalance,0) + ifNull(NotAllocatedAmount,0)) As AccountBal,  ".
				" Sum(TransPaidAmount) As TransPaidAmount, SalesRefer, ";
				
			$PrevRange = 0;	
			for ( $d = 0; $d < count($ArrayPeriods); $d += 1) {
				$query= $query." Sum(Amount".$ArrayPeriods[$d].") As Amount".$ArrayPeriods[$d].", ";
				$PrevRange = $ArrayPeriods[$d] + 1;
			}
			
$query = $query." Sum(Amount".$PrevRange.") As Amount".$PrevRange.
				" From (  ".
                " Select Q6.ClientCode, Q6.ClientName, Q6.CreditLimits, Q6.PaymentTerms, Q6.CurrencyCode, Q6.PrevAmount, Q6.Balance, ".
				" Q6.TransRemAmount, Q6.RemainDays, Q6.TransPaidAmount, Q6.CheckBalance, Q6.SalesRefer, Q6.OrderSalesDate, ".
				" if(Q6.RemainDays < 0, Q6.TransRemAmount, 0) As NonDueAmount, Q6.NotAllocatedAmount, ";
			
			$PrevRange = 0;	
			for ( $d = 0; $d < count($ArrayPeriods); $d += 1) {
				$query = $query." if(".$ArrayPeriods[$d]." > 0, if(Q6.RemainDays Between ".$PrevRange." and ".$ArrayPeriods[$d].", Q6.TransRemAmount, 0), Null) As Amount".$ArrayPeriods[$d].", ";
				$PrevRange = $ArrayPeriods[$d] + 1;
			}				
			
$query = $query." if($PrevRange > 0, if(Q6.RemainDays >= $PrevRange, Q6.TransRemAmount, 0), Null) As Amount".$PrevRange.
				" From (  ".
				" Select Q1.ClientCode, Q1.ClientName, Q1.CreditLimits, Q1.PaymentTerms, Q1.CurrencyCode, Q1.PrevAmount, (Q1.CurrAmount + Q1.PrevAmount) as Balance, ".
				" if(upper($curr) = '".$Base1."', ifNull(Q2.TransRemBase1,0), ifNull(Q2.TransRemBase2,0)) as TransRemAmount, Q2.RemainDays, ".
				" if(upper($curr) = '".$Base1."', ifNull(Q2.TransPaidBase1,0), ifNull(Q2.TransPaidBase2,0)) As TransPaidAmount, ".
				" Q2.SalesRefer, Q2.OrderSalesDate, ifNull(QNotAllocated.NotAllocatedAmount,0) As NotAllocatedAmount, ";

if ($riskbal == 1)	
	$query = $query." QCheckBal.CheckBalance As CheckBalance ";
else	
	$query = $query." 0 As CheckBalance ";			
	
$query = $query." From ".
				"(  ";
				

$j = 0;				
$k = 0; // Count For First Existsing Db
$QueryMaster = 	'';
$QueryMaster = 	" Select ClientCode, ClientName, CreditLimits, ifNull(PaymentTerms,0) As PaymentTerms, CurrencyCode, ifNull(PrevAmount,0) as PrevAmount, ifNull(CurrAmount,0) as CurrAmount ".
				" From ".
				" (  ";
	
// 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];
	//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) 
					$QueryMaster= $QueryMaster."  Select Clients.Ledger_number As ClientCode, Clients.Ledger_name As ClientName, ";
				else
					$QueryMaster= $QueryMaster."  Select JournalDt.Ledger_number As ClientCode, ledger.Ledger_name As ClientName,  ";
			
				$QueryMaster = $QueryMaster." Clients.Client_CreditLimit CreditLimits, Client_PaymentTerms PaymentTerms, ";
			    $QueryMaster = $QueryMaster." if(upper($curr) = '".$Base1."', '".$Base1."', '".$Base2."') As CurrencyCode , ";
				$QueryMaster = $QueryMaster." Sum(if(date_format(if($dateoption = 1, JournalDt.Journal_date, JournalDt.Journaldt_ValueDate),'%Y-%m-%d') < ifNull(".$fromdate.",date_format(if($dateoption = 1, JournalDt.Journal_date, JournalDt.Journaldt_ValueDate),'%Y-%m-%d')) , if(upper($curr) = '".$Base1."', if(Journaldt_dbcd = 'D', Journaldt_amount1, (-1)*Journaldt_amount1), if(Journaldt_dbcd = 'D', Journaldt_amount2, (-1)*Journaldt_amount2)),0)) PrevAmount, ".
											" Sum(if(date_format(if($dateoption = 1, JournalDt.Journal_date, JournalDt.Journaldt_ValueDate),'%Y-%m-%d') between ifNull(".$fromdate.",date_format(if($dateoption = 1, JournalDt.Journal_date, JournalDt.Journaldt_ValueDate),'%Y-%m-%d')) and ifNull(".$todate.",date_format(if($dateoption = 1, JournalDt.Journal_date, JournalDt.Journaldt_ValueDate),'%Y-%m-%d')), if(upper($curr) = '".$Base1."', if(Journaldt_dbcd = 'D', Journaldt_amount1, (-1)*Journaldt_amount1), if(Journaldt_dbcd = 'D', Journaldt_amount2, (-1)*Journaldt_amount2)),0)) CurrAmount ";
					
				$QueryMaster = $QueryMaster." From $DirSchema.JournalDt As JournalDt ".
											" Inner Join $DirSchema.Journal As Journal on JournalDt.Journal_id = Journal.Journal_id ";
					
					
				if ($riskbal == 1) {
					$QueryMaster = $QueryMaster."   Inner join ".
												"   ( select  clients.ledger_number, ledger.Ledger_name, clients.ledger_number accountnumber, Clients.Client_CreditLimit, Clients.Client_PaymentTerms  ".
												"     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) ".
												"     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 ".
                                                "     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 acctypesetup.ledger_number Is Not Null ".
												"    ) Clients on JournalDt.Ledger_Number = Clients.accountnumber  ";
													
				} else {
				    $QueryMaster = $QueryMaster." Inner join ledger on JournalDt.Ledger_number = ledger.Ledger_number ".
				              			        " Inner join Clients on JournalDt.Ledger_number = Clients.Ledger_number ";
				}
			   
						
                if ($riskbal == 1) {
				    //$QueryMaster = $QueryMaster." Where JournalDt.JournalDt_Amount1 <> 0 ";
					$QueryMaster = $QueryMaster." Where 1 = 1 ".
												" And IfNull(JournalDt.JournalDt_OldData,0) = 0 ";
				}	
				else {	
				    $QueryMaster = $QueryMaster." Where JournalDt.Ledger_Number between ifNull(".$fromledger.",JournalDt.Ledger_Number) and ifNull(". $toledger.",JournalDt.Ledger_Number) ".
												" And IfNull(JournalDt.JournalDt_OldData,0) = 0 ";
												//" And JournalDt.JournalDt_Amount1 <> 0 ";
				}
				
				if ($RowsDirCount > 1 && $k != 1)	
					$QueryMaster= $QueryMaster." And Journal.Journal_Type <> 'OP' ";
					
				$QueryMaster = $QueryMaster." Group by ClientCode, ClientName  ";
				
				if ($RowsDirCount > 1 && $j < $RowsDirCount)	
                    $QueryMaster= $QueryMaster." Union ";
				
	}			
} // End Loop For Directories				

$QueryMaster = $QueryMaster." Order by ClientCode ";
$QueryMaster = $QueryMaster.
				 " ) QDirMaster ".
				 " Order By ClientCode ";
	

	
//echo $QueryMaster; exit;					

$query = $query." ".$QueryMaster.
				" ) As Q1 Left Join  ". 
				" (  ";
				 
$j = 0;				
$k = 0;
$QueryDetail = '';
$QueryDetail =  " Select ClientCode, ClientName, SalesDate, SalesValueDate, SalesRefer, SalesTotalBase1, SalesTotalBase2, ".
				" Sum(TransPaidBase1) As TransPaidBase1, Sum(TransPaidBase2) As TransPaidBase2, ".
				" (SalesTotalBase1 - Sum(TransPaidBase1)) As TransRemBase1, (SalesTotalBase2 - Sum(TransPaidBase2)) As TransRemBase2, ".
				" Min(RemainDays) As RemainDays, OrderSalesDate ".
				" From ".
				" (  ";
				
// Reading Data For Many Years Or Different Directories					 
while($RowsDir=mysql_fetch_row($ResultDir1)){

    $j = $j + 1;    
	//if ($RowsDirCount > 1)
	   $DirSchema   = $RowsDir[0];
	   $DirFromDate = $RowsDir[1];
	   $DirToDate   = $RowsDir[2];
	   $DirCompany  = $RowsDir[3];
	   $DirYear	    = $RowsDir[4];
	//else
       //$DirSchema = '';	
    
	if ($RowsDirCount > 1) {
		$QuerySchemaExists = "Show Schemas where `Database` = '".$DirSchema."'  ";   
		$ResultSchemaExists=mysql_query($QuerySchemaExists,$connection) or die(mysql_error());
		$SchemaExists = mysql_num_rows($ResultSchemaExists);
		
	}
	
	if (($RowsDirCount > 1 && $SchemaExists > 0) || ($RowsDirCount == 1) ) {
	
		$k = $k + 1; 
				
		if($Module_Delivery == 0 ) {
			$QueryDetail = $QueryDetail.
					" Select Deliveries.Delivery_Id As InvoiceId, Deliveries.Client_Code As ClientCode, ledger.Ledger_name As ClientName, date_format(Deliveries.Delivery_Date,'%d/%m/%Y') As SalesDate, ".
					" date_format(Deliveries.Journaldt_ValueDate,'%d/%m/%Y') As SalesValueDate, Deliveries.Delivery_Reference As SalesRefer, ".
					" ifNull(AccLink.Sale_NetTotalBase1,0) As SalesTotalBase1, ifNull(AccLink.Sale_NetTotalBase2,0) As SalesTotalBase2, ".
					" (ifNull(AccLink.Sale_PaidAmntBase1,0) + ifNull(AccLink.Sale_ReturnAmntbase1,0)) As TransPaidBase1, ".
					" (ifNull(AccLink.Sale_PaidAmntBase2,0) + ifNull(AccLink.Sale_ReturnAmntbase2,0)) As TransPaidBase2, ".
					" ifNull(AccLink.Sale_RemainAmntBase1,0) As TransRemBase1, ifNull(AccLink.Sale_RemainAmntBase2,0) As TransRemBase2, ".
					" DATEDIFF(date_format(".$agingdate.",'%Y-%m-%d'), date_format(if(AccLink.Journal_Id != '' and AccLink.Journal_Id is not null, if($dateoption = 1, Acclink.Trans_Date, Acclink.Trans_ValueDate), if($dateoption = 1, Acclink.Return_Date, Acclink.Return_ValueDate)),'%Y-%m-%d')) RemainDays, ".
					" Deliveries.Delivery_Date As OrderSalesDate, Acclink.Trans_Date As OrderTransDate ".
					" From $DirSchema.Deliveries As Deliveries ".
					" Inner Join ".$_SESSION['dbLabel'].".Acclink As Acclink on Deliveries.Delivery_Id = Acclink.Link_ID ".
					//" Left Join $DirSchema.TransDt As TransDt on Acclink.Journal_Id = TransDt.Transdt_Id ".
					//" Left Join $DirSchema.Trans As Trans on TransDt.Trans_Id = Trans.Trans_Id And Trans.Trans_Type In ('RV','CV') ".
					//" Left Join $DirSchema.Deliveries As RDeliveries on RDeliveries.Delivery_Id = Acclink.Return_ID and RDeliveries.Journal_Type = 'DR' ".
					" Inner Join $DirSchema.Ledger As Ledger on Deliveries.Client_Code = Ledger.ledger_Number ";
					
			if ($projectexist >	0 && $FI_RepRestrictProject == 1)
				$QueryDetail = $QueryDetail.$QueryRestrictPrj." on Deliveries.project_code like concat(QP.project_code,'%') ";
											 
			if ($costcentexist > 0 && $FI_RepRestrictCostcenter == 1)
				$QueryDetail = $QueryDetail.$QueryRestrictCst." on Deliveries.costcent_code like concat(QC.costcent_code,'%') ";
		
			$QueryDetail = $QueryDetail.
					" Where Date_format(Deliveries.Delivery_Date,'%Y-%m-%d') between ifNull('".$DirFromDate."',date_format(Deliveries.Delivery_Date,'%Y-%m-%d')) and ifNull('".$DirToDate."',date_format(Deliveries.Delivery_Date,'%Y-%m-%d'))  ".
					" And Date_format(Deliveries.Delivery_Date,'%Y-%m-%d') between ifNull(".$fromdate.",date_format(Deliveries.Delivery_Date,'%Y-%m-%d')) and ifNull(".$todate.",date_format(Deliveries.Delivery_Date,'%Y-%m-%d'))  ".
					" And Deliveries.Client_Code between ifNull(".$fromledger.",Deliveries.Client_Code) and ifNull(". $toledger.",Deliveries.Client_Code) ".
					" And Deliveries.Journal_Type = 'DI' ".
					" And IfNull(Deliveries.Delivery_OldData,0) = 0 ".
					" And Acclink.Link_Type In ('DI','SI') ".
					" And upper(Acclink.CompanyName) = upper('".$DirCompany."') ".
					" And upper(Acclink.Link_DirYear) = upper('".$DirYear."') ".
					" And upper(ifNull(Deliveries.project_code,'-1')) like ifNull(upper(".$fromproject."), upper(ifNull(Deliveries.project_code,'-1'))) ".
					" And upper(ifNull(Deliveries.Costcent_Code,'-1')) like ifNull(upper(".$fromcst."), upper(ifNull(Deliveries.Costcent_Code,'-1'))) ".
					//" And upper(ifNull(Trans.project_code,'-1')) like ifNull(upper(".$fromproject."), upper(ifNull(Trans.project_code,'-1'))) ".
					//" And upper(ifNull(Trans.Costcent_Code,'-1')) like ifNull(upper(".$fromcst."), upper(ifNull(Trans.Costcent_Code,'-1'))) ".
					" Union ".
					" Select Deliveries.Delivery_Id As InvoiceId, Deliveries.Client_Code As ClientCode, ledger.Ledger_name As ClientName, date_format(Deliveries.Delivery_Date,'%d/%m/%Y') As SalesDate, ".
					" date_format(Deliveries.Journaldt_ValueDate,'%d/%m/%Y') As SalesValueDate, Deliveries.Delivery_Reference As SalesRefer, ".
					" (ifNull(Deliveries.Delivery_TotalBase1,0) - ifNull(Deliveries.Delivery_DiscountBase1,0) + ifNull(Deliveries.Delivery_VatBase1,0)) As SalesTotalBase1, ".
					" (ifNull(Deliveries.Delivery_TotalBase2,0) - ifNull(Deliveries.Delivery_DiscountBase2,0) + ifNull(Deliveries.Delivery_VatBase2,0)) As SalesTotalBase2, ".
					" 0 As TransPaidBase1, 0 As TransPaidBase2, ".
					" (ifNull(Deliveries.Delivery_TotalBase1,0) - ifNull(Deliveries.Delivery_DiscountBase1,0) + ifNull(Deliveries.Delivery_VatBase1,0)) As TransRemBase1, ".
					" (ifNull(Deliveries.Delivery_TotalBase2,0) - ifNull(Deliveries.Delivery_DiscountBase2,0) + ifNull(Deliveries.Delivery_VatBase2,0)) As TransRemBase2, ".
					" DATEDIFF(date_format(".$agingdate.",'%Y-%m-%d'), date_format(if($dateoption = 1, Deliveries.Delivery_Date, Deliveries.Journaldt_ValueDate),'%Y-%m-%d')) RemainDays, ".
					" Deliveries.Delivery_Date As OrderSalesDate, Null As OrderTransDate ".
					" From $DirSchema.Deliveries As Deliveries ".
					" Inner Join $DirSchema.Ledger As Ledger on Deliveries.Client_Code = Ledger.ledger_Number ";
					
			if ($projectexist >	0 && $FI_RepRestrictProject == 1)
				$QueryDetail = $QueryDetail.$QueryRestrictPrj." on Deliveries.project_code like concat(QP.project_code,'%') ";
											 
			if ($costcentexist > 0 && $FI_RepRestrictCostcenter == 1)
				$QueryDetail = $QueryDetail.$QueryRestrictCst." on Deliveries.costcent_code like concat(QC.costcent_code,'%') ";
		
			$QueryDetail = $QueryDetail.
					" Where Date_format(Deliveries.Delivery_Date,'%Y-%m-%d') between ifNull('". $DirFromDate."',date_format(Deliveries.Delivery_Date,'%Y-%m-%d')) and ifNull('". $DirToDate."',date_format(Deliveries.Delivery_Date,'%Y-%m-%d'))  ".
					" And Deliveries.Client_Code between ifNull(".$fromledger.",Deliveries.Client_Code) and ifNull(". $toledger.",Deliveries.Client_Code) ".
					" And Date_format(Deliveries.Delivery_Date,'%Y-%m-%d') between ifNull(".$fromdate.",date_format(Deliveries.Delivery_Date,'%Y-%m-%d')) and ifNull(".$todate.",date_format(Deliveries.Delivery_Date,'%Y-%m-%d'))  ".
					" And Deliveries.Journal_Type = 'DI' ".
					" And IfNull(Deliveries.Delivery_OldData,0) = 0 ".
					" And Deliveries.Delivery_Id Not In (Select ifNull(Link_ID,-1) From ".$_SESSION['dbLabel'].".Acclink As Acclink  ".
					"                          		 	 Where Acclink.Link_Type In ('DI','SI') ".
					" 								 	 And upper(Acclink.Link_DirYear) = upper('".$DirYear."') ".
					"                                	 And upper(Acclink.CompanyName) = upper('".$DirCompany."') ) ".
					" And upper(ifNull(Deliveries.project_code,'-1')) like ifNull(upper(".$fromproject."), upper(ifNull(Deliveries.project_code,'-1'))) ".
					" And upper(ifNull(Deliveries.Costcent_Code,'-1')) like ifNull(upper(".$fromcst."), upper(ifNull(Deliveries.Costcent_Code,'-1'))) ";
		} else {
			$QueryDetail = $QueryDetail.
					" Select Sales.Sale_Id As InvoiceId, Sales.Client_Code As ClientCode, ledger.Ledger_name As ClientName, date_format(Sales.Sale_Date,'%d/%m/%Y') As SalesDate, ".
					" date_format(Sales.Journaldt_ValueDate,'%d/%m/%Y') As SalesValueDate, Sales.Sale_Reference As SalesRefer, ".
					" ifNull(AccLink.Sale_NetTotalBase1,0) As SalesTotalBase1, ifNull(AccLink.Sale_NetTotalBase2,0) As SalesTotalBase2, ".
					" (ifNull(AccLink.Sale_PaidAmntBase1,0) + ifNull(AccLink.Sale_ReturnAmntbase1,0)) As TransPaidBase1, ".
					" (ifNull(AccLink.Sale_PaidAmntBase2,0) + ifNull(AccLink.Sale_ReturnAmntbase2,0)) As TransPaidBase2, ".
					" ifNull(AccLink.Sale_RemainAmntBase1,0) As TransRemBase1, ifNull(AccLink.Sale_RemainAmntBase2,0) As TransRemBase2, ".
					" DATEDIFF(date_format(".$agingdate.",'%Y-%m-%d'), date_format(if(AccLink.Journal_Id != '' and AccLink.Journal_Id is not null, if($dateoption = 1, AccLink.Trans_Date, AccLink.Trans_ValueDate), if($dateoption = 1, AccLink.Return_Date, AccLink.Return_ValueDate)),'%Y-%m-%d')) RemainDays, ".
					" Sales.Sale_Date As OrderSalesDate, AccLink.Trans_Date As OrderTransDate ".
					" From $DirSchema.Sales As Sales ".
					" Inner Join ".$_SESSION['dbLabel'].".Acclink As Acclink on Sales.Sale_Id = Acclink.Link_ID ".
					//" Left Join $DirSchema.TransDt As TransDt on Acclink.Journal_Id = TransDt.Transdt_Id ".
					//" Left Join $DirSchema.Trans As Trans on TransDt.Trans_Id = Trans.Trans_Id And Trans.Trans_Type In ('RV','CV') ".
					//" Left Join $DirSchema.Sales As RSales on RSales.Sale_Id = Acclink.Return_ID and RSales.Journal_Type = 'SR' ".
					" Inner Join $DirSchema.Ledger As Ledger on Sales.Client_Code = Ledger.ledger_Number ";
					
			if ($projectexist >	0 && $FI_RepRestrictProject == 1)
				$QueryDetail = $QueryDetail.$QueryRestrictPrj." on Sales.project_code like concat(QP.project_code,'%') ";
											 
			if ($costcentexist > 0 && $FI_RepRestrictCostcenter == 1)
				$QueryDetail = $QueryDetail.$QueryRestrictCst." on Sales.Costcenter_code like concat(QC.costcent_code,'%') ";	
		
			$QueryDetail = $QueryDetail.
					" Where Date_format(Sales.Sale_Date,'%Y-%m-%d') between ifNull('".$DirFromDate."',date_format(Sales.Sale_Date,'%Y-%m-%d')) and ifNull('".$DirToDate."',date_format(Sales.Sale_Date,'%Y-%m-%d'))  ".
					" And Date_format(Sales.Sale_Date,'%Y-%m-%d') between ifNull(".$fromdate.",date_format(Sales.Sale_Date,'%Y-%m-%d')) and ifNull(".$todate.",date_format(Sales.Sale_Date,'%Y-%m-%d'))  ".
					" And Sales.Client_Code between ifNull(".$fromledger.",Sales.Client_Code) and ifNull(". $toledger.",Sales.Client_Code) ".
					" And Sales.Journal_Type = 'SI' ".
					" And IfNull(Sales.Sale_OldData,0) = 0 ".
					" And Acclink.Link_Type In ('DI','SI') ".
					" And upper(Acclink.CompanyName) = upper('".$DirCompany."') ".
					" And upper(Acclink.Link_DirYear) = upper('".$DirYear."') ".
					" And upper(ifNull(Sales.project_code,'-1')) like ifNull(upper(".$fromproject."), upper(ifNull(Sales.project_code,'-1'))) ".
					" And upper(ifNull(Sales.Costcenter_code,'-1')) like ifNull(upper(".$fromcst."), upper(ifNull(Sales.Costcenter_code,'-1'))) ".
					//" And upper(ifNull(Trans.project_code,'-1')) like ifNull(upper(".$fromproject."), upper(ifNull(Trans.project_code,'-1'))) ".
					//" And upper(ifNull(Trans.Costcent_Code,'-1')) like ifNull(upper(".$fromcst."), upper(ifNull(Trans.Costcent_Code,'-1'))) ".
					" Union ".
					" Select Sales.Sale_Id As InvoiceId, Sales.Client_Code As ClientCode, ledger.Ledger_name As ClientName, date_format(Sales.Sale_Date,'%d/%m/%Y') As SalesDate, ".
					" date_format(Sales.Journaldt_ValueDate,'%d/%m/%Y') As SalesValueDate, Sales.Sale_Reference As SalesRefer, ".
					" (ifNull(Sales.Sale_TotalBase1,0) - ifNull(Sales.Sale_DiscountBase1,0) + ifNull(Sales.Sale_VATBase1,0)) As SalesTotalBase1, ".
					" (ifNull(Sales.Sale_TotalBase2,0) - ifNull(Sales.Sale_DiscountBase2,0) + ifNull(Sales.Sale_VATBase2,0)) As SalesTotalBase2, ".
					" 0 As TransPaidBase1, 0 As TransPaidBase2, ".
					" (ifNull(Sales.Sale_TotalBase1,0) - ifNull(Sales.Sale_DiscountBase1,0) + ifNull(Sales.Sale_VATBase1,0)) As TransRemBase1, ".
					" (ifNull(Sales.Sale_TotalBase2,0) - ifNull(Sales.Sale_DiscountBase2,0) + ifNull(Sales.Sale_VATBase2,0)) As TransRemBase2, ".
					" DATEDIFF(date_format(".$agingdate.",'%Y-%m-%d'), date_format(if($dateoption = 1, Sales.Sale_Date, Sales.Journaldt_ValueDate),'%Y-%m-%d')) RemainDays, ".
					" Sales.Sale_Date As OrderSalesDate, Null As OrderTransDate ".
					" From $DirSchema.Sales As Sales ".
					" Inner Join $DirSchema.Ledger As Ledger on Sales.Client_Code = Ledger.ledger_Number ";
					
			if ($projectexist >	0 && $FI_RepRestrictProject == 1)
				$QueryDetail = $QueryDetail.$QueryRestrictPrj." on Sales.project_code like concat(QP.project_code,'%') ";
											 
			if ($costcentexist > 0 && $FI_RepRestrictCostcenter == 1)
				$QueryDetail = $QueryDetail.$QueryRestrictCst." on Sales.Costcenter_code like concat(QC.costcent_code,'%') ";	
		
			$QueryDetail = $QueryDetail.
					" Where Date_format(Sales.Sale_Date,'%Y-%m-%d') between ifNull('".$DirFromDate."',date_format(Sales.Sale_Date,'%Y-%m-%d')) and ifNull('".$DirToDate."',date_format(Sales.Sale_Date,'%Y-%m-%d'))  ".
					" And Date_format(Sales.Sale_Date,'%Y-%m-%d') between ifNull(".$fromdate.",date_format(Sales.Sale_Date,'%Y-%m-%d')) and ifNull(".$todate.",date_format(Sales.Sale_Date,'%Y-%m-%d'))  ".
					" And Sales.Client_Code between ifNull(".$fromledger.",Sales.Client_Code) and ifNull(". $toledger.",Sales.Client_Code) ".
					" And Sales.Journal_Type = 'SI' ".
					" And IfNull(Sales.Sale_OldData,0) = 0 ".
					" And Sales.Sale_Id Not In (Select ifNull(Link_ID,-1) From ".$_SESSION['dbLabel'].".Acclink As Acclink  ".
					"                           Where Acclink.Link_Type In ('DI','SI') ".
					" 							And upper(Acclink.Link_DirYear) = upper('".$DirYear."') ".
					"                           And upper(Acclink.CompanyName) = upper('".$DirCompany."') ) ".
					" And upper(ifNull(Sales.project_code,'-1')) like ifNull(upper(".$fromproject."), upper(ifNull(Sales.project_code,'-1'))) ".
					" And upper(ifNull(Sales.Costcenter_code,'-1')) like ifNull(upper(".$fromcst."), upper(ifNull(Sales.Costcenter_code,'-1'))) ";

		}

					
		//Debit Notes
		$QueryDetail = $QueryDetail.
						" Union ".
					    " Select JournalDt.JournalDt_Id As InvoiceId, JournalDt.Ledger_number As ClientCode, ledger.Ledger_name As ClientName, date_format(Journal.Journal_Date,'%d/%m/%Y') As SalesDate, ".
						" date_format(JournalDt.Journaldt_ValueDate,'%d/%m/%Y') As SalesValueDate, Journal.Journal_refgovernment As SalesRefer, ".
						" ifNull(Journaldt_amount1,0) As SalesTotalBase1, ifNull(Journaldt_amount2,0) As SalesTotalBase2, ".
						" (ifNull(AccLink.Sale_PaidAmntBase1,0) + ifNull(AccLink.Sale_ReturnAmntbase1,0)) As TransPaidBase1, ".
						" (ifNull(AccLink.Sale_PaidAmntBase2,0) + ifNull(AccLink.Sale_ReturnAmntbase2,0)) As TransPaidBase2, ".
						" ifNull(AccLink.Sale_RemainAmntBase1,0) As TransRemBase1, ifNull(AccLink.Sale_RemainAmntBase2,0) As TransRemBase2, ".
						" DATEDIFF(date_format(".$agingdate.",'%Y-%m-%d'), date_format(if(AccLink.Journal_Id != '' and AccLink.Journal_Id is not null, if($dateoption = 1, Acclink.Trans_Date, Acclink.Trans_ValueDate), if($dateoption = 1, Acclink.Return_Date, Acclink.Return_ValueDate)),'%Y-%m-%d')) RemainDays, ".
						" Journal.Journal_Date As OrderSalesDate, Acclink.Trans_Date As OrderTransDate ".
						" From $DirSchema.JournalDt As JournalDt ".
						" Inner join $DirSchema.Journal As Journal on JournalDt.Journal_id = Journal.Journal_id ".
						" Inner Join ".$_SESSION['dbLabel'].".Acclink As Acclink on Acclink.Link_ID = JournalDt.JournalDt_Id And Acclink.Link_Type = Journal.Journal_Type ".
						//" Left Join $DirSchema.TransDt on Acclink.Journal_Id = TransDt.Transdt_Id ".
						//" Left Join $DirSchema.Trans on TransDt.Trans_Id = Trans.Trans_Id And Trans.Trans_Type In ('RV','CV') ".
						//" Left Join $DirSchema.Deliveries As RDeliveries on RDeliveries.Delivery_Id = Acclink.Return_ID and RDeliveries.Journal_Type = 'DR' ".
						" Inner Join $DirSchema.Ledger on JournalDt.ledger_Number = Ledger.ledger_Number ";
						
		if ($projectexist >	0 && $FI_RepRestrictProject == 1)
			$QueryDetail = $QueryDetail.$QueryRestrictPrj." on Journal.project_code like concat(QP.project_code,'%') ";
											 
		if ($costcentexist > 0 && $FI_RepRestrictCostcenter == 1)
			$QueryDetail = $QueryDetail.$QueryRestrictCst." on Journal.costcent_code like concat(QC.costcent_code,'%') ";
				
		$QueryDetail = $QueryDetail.
						" Where Date_format(Journal.Journal_Date,'%Y-%m-%d') between ifNull('".$DirFromDate."',date_format(Journal.Journal_Date,'%Y-%m-%d')) and ifNull('".$DirToDate."',date_format(Journal.Journal_Date,'%Y-%m-%d'))  ".
						" And Date_format(Journal.Journal_Date,'%Y-%m-%d') between ifNull(".$fromdate.",date_format(Journal.Journal_Date,'%Y-%m-%d')) and ifNull(".$todate.",date_format(Journal.Journal_Date,'%Y-%m-%d'))  ".
						" And JournalDt.Ledger_number between ifNull(".$fromledger.",JournalDt.Ledger_number) and ifNull(". $toledger.",JournalDt.Ledger_number) ".
						" And JournalDt.Journaldt_dbcd = 'D' ".
						" And Journal.Journal_Type Not In ('CV','SI','SR') ".
						" And IfNull(JournalDt.JournalDt_OldData,0) = 0 ".
						" And upper(Acclink.CompanyName) = upper('".$DirCompany."') ".
						" And upper(Acclink.Link_DirYear) = upper('".$DirYear."') ".
						" And upper(ifNull(Journal.project_code,'-1')) like ifNull(upper(".$fromproject."), upper(ifNull(Journal.project_code,'-1'))) ".
						" And upper(ifNull(Journal.Costcent_Code,'-1')) like ifNull(upper(".$fromcst."), upper(ifNull(Journal.Costcent_Code,'-1'))) ";
						//" And upper(ifNull(Trans.project_code,'-1')) like ifNull(upper(".$fromproject."), upper(ifNull(Trans.project_code,'-1'))) ".
						//" And upper(ifNull(Trans.Costcent_Code,'-1')) like ifNull(upper(".$fromcst."), upper(ifNull(Trans.Costcent_Code,'-1'))) ";
						
		if ($RowsDirCount > 1 && $k != 1)	
			$QueryDetail = $QueryDetail." And Journal.Journal_Type <> 'OP' ";
					
		$QueryDetail = $QueryDetail.
						" Union ".
						" Select JournalDt.JournalDt_Id As InvoiceId, JournalDt.Ledger_number As ClientCode, ledger.Ledger_name As ClientName, date_format(Journal.Journal_Date,'%d/%m/%Y') As SalesDate, ".
						" date_format(JournalDt.Journaldt_ValueDate,'%d/%m/%Y') As SalesValueDate, Journal.Journal_refgovernment As SalesRefer, ".
						" ifNull(Journaldt_amount1,0) As SalesTotalBase1, ifNull(Journaldt_amount2,0) As SalesTotalBase2, ".
						" 0 As TransPaidBase1, 0 As TransPaidBase2, ".
						" ifNull(Journaldt_amount1,0) As TransRemBase1, ifNull(Journaldt_amount2,0) As TransRemBase2, ".
						" DATEDIFF(date_format(".$agingdate.",'%Y-%m-%d'), date_format(if($dateoption = 1, Journal.Journal_Date, JournalDt.Journaldt_ValueDate),'%Y-%m-%d')) RemainDays, ".
						" Journal.Journal_Date As OrderSalesDate, Null As OrderTransDate ".
						" From $DirSchema.JournalDt As JournalDt ".
						" Inner join $DirSchema.Journal As Journal on JournalDt.Journal_id = Journal.Journal_id ".
						" Inner Join $DirSchema.Ledger on JournalDt.ledger_Number = Ledger.ledger_Number ";
							
		if ($projectexist >	0 && $FI_RepRestrictProject == 1)
			$QueryDetail = $QueryDetail.$QueryRestrictPrj." on Journal.project_code like concat(QP.project_code,'%') ";
											 
		if ($costcentexist > 0 && $FI_RepRestrictCostcenter == 1)
			$QueryDetail = $QueryDetail.$QueryRestrictCst." on Journal.costcent_code like concat(QC.costcent_code,'%') ";				
			
		$QueryDetail = $QueryDetail.
						" Where Date_format(Journal.Journal_Date,'%Y-%m-%d') between ifNull('".$DirFromDate."',date_format(Journal.Journal_Date,'%Y-%m-%d')) and ifNull('".$DirToDate."',date_format(Journal.Journal_Date,'%Y-%m-%d'))  ".
						" And Date_format(Journal.Journal_Date,'%Y-%m-%d') between ifNull(".$fromdate.",date_format(Journal.Journal_Date,'%Y-%m-%d')) and ifNull(".$todate.",date_format(Journal.Journal_Date,'%Y-%m-%d'))  ".
						" And JournalDt.Ledger_number between ifNull(".$fromledger.",JournalDt.Ledger_number) and ifNull(". $toledger.",JournalDt.Ledger_number) ".
						" And JournalDt.Journaldt_dbcd = 'D' ".
						" And Journal.Journal_Type Not In ('CV','SI','SR') ".
						" And IfNull(JournalDt.JournalDt_OldData,0) = 0 ".
						" And JournalDt.JournalDt_Id Not In (Select ifNull(Link_ID,-1) From ".$_SESSION['dbLabel'].".Acclink  ".
						"                          		 	 Where Acclink.Link_Type Not In ('DI','SI') ".
						" 								 	 And upper(Acclink.Link_DirYear) = upper('".$DirYear."') ".
						"                                	 And upper(Acclink.CompanyName) = upper('".$DirCompany."') ) ".
						" And upper(ifNull(Journal.project_code,'-1')) like ifNull(upper(".$fromproject."), upper(ifNull(Journal.project_code,'-1'))) ".
						" And upper(ifNull(Journal.Costcent_Code,'-1')) like ifNull(upper(".$fromcst."), upper(ifNull(Journal.Costcent_Code,'-1'))) ";
						
		if ($RowsDirCount > 1 && $k != 1)	
			$QueryDetail = $QueryDetail." And Journal.Journal_Type <> 'OP' ";
	            
		if ($RowsDirCount > 1 && $j < $RowsDirCount)	
            $QueryDetail = $QueryDetail." Union ";	
			
	}					
}

$QueryDetail = $QueryDetail.
			" ) QDirDetail ".
			//" Where  ifNull(TransRemBase1,0) > 0 ".
			" Group By ClientCode, SalesRefer ".
			" Order by ClientCode, OrderSalesDate Asc, SalesRefer Asc  ";
			
//echo $QueryDetail;exit;					 


$query = $query." ".$QueryDetail.						
				") Q2 on Q2.ClientCode = Q1.ClientCode ";
					

if ($riskbal == 1) {
	$query = $query." Left Outer Join (  ";

	
$j = 0;
$k = 0; // Count For First Existsing Db
$QueryCheckBal = '';
$QueryCheckBal = " Select ClientCode, CurrencyCode, Sum(CheckBalance) As CheckBalance ".
				 " from ".
				 " (  ";

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

    $j = $j + 1;    
	//if ($RowsDirCount > 1)
	   $DirSchema   = $RowsDir[0];
	   $DirFromDate = $RowsDir[1];
	   $DirToDate   = $RowsDir[2];
	//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; 
		
		$QueryCheckBal = $QueryCheckBal." Select clients.Ledger_number As ClientCode, if(upper($curr) = '".$Base1."', '".$Base1."', '".$Base2."') CurrencyCode , ";
		$QueryCheckBal = $QueryCheckBal." Sum(if(date_format(if($dateoption = 1, JournalDt.Journal_date, JournalDt.Journaldt_ValueDate),'%Y-%m-%d') between ifNull('". $DirFromDate."',date_format(if($dateoption = 1, JournalDt.Journal_date, JournalDt.Journaldt_ValueDate),'%Y-%m-%d')) and ifNull('". $DirToDate."',date_format(if($dateoption = 1, JournalDt.Journal_date, JournalDt.Journaldt_ValueDate),'%Y-%m-%d')), if(upper($curr) = '".$Base1."', if(Journaldt_dbcd = 'D', Journaldt_amount1, (-1)*Journaldt_amount1), if(Journaldt_dbcd = 'D', Journaldt_amount2, (-1)*Journaldt_amount2)),0) ) As CheckBalance ";
		$QueryCheckBal = $QueryCheckBal." From $DirSchema.JournalDt As JournalDt ".
										" Inner join $DirSchema.Journal As Journal on JournalDt.Journal_id = Journal.Journal_id ".
										" Inner join ".
										"   ( select clients.ledger_number, concat(acctypesetup.ledger_number,substr(clients.ledger_number,5,length(clients.ledger_number))) accountnumber ".
										"     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 acctypesetup.ledger_number Is Not Null ".
										"    ) Clients on JournalDt.Ledger_Number = Clients.accountnumber  ";
															
		//$QueryCheckBal = $QueryCheckBal." Where JournalDt.JournalDt_Amount1 <> 0 ";
		$QueryCheckBal = $QueryCheckBal." Where 1 = 1 ".
										" And IfNull(JournalDt.JournalDt_OldData,0) = 0 ";
		
		if ($RowsDirCount > 1 && $k != 1)	
			$QueryCheckBal = $QueryCheckBal." And Journal.Journal_Type <> 'OP' ";
			
		$QueryCheckBal = $QueryCheckBal." Group by ClientCode, CurrencyCode ";
		
		if ($RowsDirCount > 1 && $j < $RowsDirCount)	
            $QueryCheckBal= $QueryCheckBal." Union ";
		
	}			
} // End Loop For Directories
		
$QueryCheckBal = $QueryCheckBal." ) QDirCheckBal ".
							   " Group By ClientCode, CurrencyCode ".
							   " Order By ClientCode, CurrencyCode ";
	
//echo $QueryCheckBal; exit;					

$query = $query." ".$QueryCheckBal.
				  " ) As QCheckBal on QCheckBal.ClientCode = Q1.ClientCode ";
}

$query = $query." Left Outer Join (  ";

$j = 0;				
$k = 0; // Count For First Existsing Db
$QueryNotAllocated = '';
$QueryNotAllocated = " Select QClt.ClientCode, QClt.CurrencyCode, ifNull(NotAllocatedAmount,0) As NotAllocatedAmount ".
					 " From ".
					 " ( Select Clients.ledger_Number As ClientCode, if(upper($curr) = '".$Base1."', '".$Base1."', '".$Base2."') CurrencyCode  ".
					 "   From Clients ".
					 "   Where Clients.Ledger_Number between ifNull(".$fromledger.",Clients.Ledger_Number) and ifNull(". $toledger.",Clients.Ledger_Number) ".   
					 " ) As QClt Left Join ".
					 " (  ";
					 
$QueryNotAllocated = $QueryNotAllocated.
					" Select ClientCode, CurrencyCode, Sum(if(upper($curr) = '".$Base1."', IfNull(NotAllocatedBase1,0),IfNull(NotAllocatedBase2,0))) As NotAllocatedAmount ".
					" From (  ";
					 
// Reading Data For Many Years Or Different Directories					 
while($RowsDir=mysql_fetch_row($ResultDir3)){

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

		if($Module_Delivery == 0 ) {
			$QueryNotAllocated1 = $QueryNotAllocated1.
					" Select Deliveries.Delivery_Id As InvoiceId, Deliveries.Client_Code As ClientCode, if(upper($curr) = '".$Base1."', '".$Base1."', '".$Base2."') CurrencyCode , ".
					" ((ifNull(Deliveries.Delivery_TotalBase1,0) - ifNull(Deliveries.Delivery_DiscountBase1,0) + ifNull(Deliveries.Delivery_VatBase1,0)) - Sum(ifNull(AccLink.Sale_ReturnAmntBase1,0))) As NotAllocatedBase1, ".
					" ((ifNull(Deliveries.Delivery_TotalBase2,0) - ifNull(Deliveries.Delivery_DiscountBase2,0) + ifNull(Deliveries.Delivery_VatBase2,0)) - Sum(ifNull(AccLink.Sale_ReturnAmntBase2,0))) As NotAllocatedBase2, ".
					" Deliveries.Journal_Type As TransType ".
					" From $DirSchema.Deliveries As Deliveries ".
					" Inner Join ".$_SESSION['dbLabel'].".Acclink As Acclink on Deliveries.Delivery_Id = Acclink.Return_ID ".
					" Inner Join $DirSchema.Ledger As Ledger on Deliveries.Client_Code = Ledger.ledger_Number ";
					
			if ($projectexist >	0 && $FI_RepRestrictProject == 1)
				$QueryNotAllocated1 = $QueryNotAllocated1.$QueryRestrictPrj." on Deliveries.project_code like concat(QP.project_code,'%') ";
											 
			if ($costcentexist > 0 && $FI_RepRestrictCostcenter == 1)
				$QueryNotAllocated1 = $QueryNotAllocated1.$QueryRestrictCst." on Deliveries.costcent_code like concat(QC.costcent_code,'%') ";
		
			$QueryNotAllocated1 = $QueryNotAllocated1.
					" Where Date_format(Deliveries.Delivery_Date,'%Y-%m-%d') between ifNull('". $DirFromDate."',date_format(Deliveries.Delivery_Date,'%Y-%m-%d')) and ifNull('". $DirToDate."',date_format(Deliveries.Delivery_Date,'%Y-%m-%d'))  ".
					" And Date_format(Deliveries.Delivery_Date,'%Y-%m-%d') between ifNull(".$fromdate.",date_format(Deliveries.Delivery_Date,'%Y-%m-%d')) and ifNull(".$todate.",date_format(Deliveries.Delivery_Date,'%Y-%m-%d'))  ".
					" And Deliveries.Client_Code between ifNull(".$fromledger.",Deliveries.Client_Code) and ifNull(". $toledger.",Deliveries.Client_Code) ".
					" And Deliveries.journal_Type = 'DR' ".
					" And IfNull(Deliveries.Delivery_OldData,0) = 0 ".
					" And Acclink.Return_Type In ('DR','SR') ".
					" And upper(Acclink.CompanyName) = upper('".$DirCompany."') ".
					" And upper(Acclink.DirYear) = upper('".$DirYear."') ".
					" Group By Deliveries.Delivery_Id  ".
					" Having ifNull(NotAllocatedBase1,0) > 0 ".
					" Union ".
					" Select Deliveries.Delivery_Id As InvoiceId, Deliveries.Client_Code As ClientCode, if(upper($curr) = '".$Base1."', '".$Base1."', '".$Base2."') CurrencyCode , ".
					" (ifNull(Deliveries.Delivery_TotalBase1,0) - ifNull(Deliveries.Delivery_DiscountBase1,0) + ifNull(Deliveries.Delivery_VatBase1,0)) As NotAllocatedBase1, ".
					" (ifNull(Deliveries.Delivery_TotalBase2,0) - ifNull(Deliveries.Delivery_DiscountBase2,0) + ifNull(Deliveries.Delivery_VatBase2,0)) As NotAllocatedBase2, ".
					" Deliveries.Journal_Type As TransType ".
					" From $DirSchema.Deliveries As Deliveries ".
					" Inner Join $DirSchema.Ledger As Ledger on Deliveries.Client_Code = Ledger.ledger_Number ";
					
			if ($projectexist >	0 && $FI_RepRestrictProject == 1)
				$QueryNotAllocated1 = $QueryNotAllocated1.$QueryRestrictPrj." on Deliveries.project_code like concat(QP.project_code,'%') ";
											 
			if ($costcentexist > 0 && $FI_RepRestrictCostcenter == 1)
				$QueryNotAllocated1 = $QueryNotAllocated1.$QueryRestrictCst." on Deliveries.costcent_code like concat(QC.costcent_code,'%') ";
		
			$QueryNotAllocated1 = $QueryNotAllocated1.
					" Where Date_format(Deliveries.Delivery_Date,'%Y-%m-%d') between ifNull('". $DirFromDate."',date_format(Deliveries.Delivery_Date,'%Y-%m-%d')) and ifNull('". $DirToDate."',date_format(Deliveries.Delivery_Date,'%Y-%m-%d'))  ".
					" And Date_format(Deliveries.Delivery_Date,'%Y-%m-%d') between ifNull(".$fromdate.",date_format(Deliveries.Delivery_Date,'%Y-%m-%d')) and ifNull(".$todate.",date_format(Deliveries.Delivery_Date,'%Y-%m-%d'))  ".
					" And Deliveries.Client_Code between ifNull(".$fromledger.",Deliveries.Client_Code) and ifNull(". $toledger.",Deliveries.Client_Code) ".
					" And Deliveries.journal_Type = 'DR' ".
					" And IfNull(Deliveries.Delivery_OldData,0) = 0 ".
					" And Deliveries.Delivery_Id Not In (Select ifNull(Return_ID,-1) From ".$_SESSION['dbLabel'].".Acclink  ".
					"                          		 	 Where Acclink.Return_Type In ('DR','SR') ".
					" 								 	 And upper(Acclink.DirYear) = upper('".$DirYear."') ".
					" 								 	 And upper(Acclink.CompanyName) = upper('".$DirCompany."')) ";
					
		} else {
						
			$QueryNotAllocated1 = $QueryNotAllocated1.
					" Select Sales.Sale_Id As InvoiceId, Sales.Client_Code As ClientCode, if(upper($curr) = '".$Base1."', '".$Base1."', '".$Base2."') CurrencyCode , ".
					" ((ifNull(Sales.Sale_TotalBase1,0) - ifNull(Sales.Sale_DiscountBase1,0) + ifNull(Sales.Sale_VATBase1,0)) - Sum(ifNull(AccLink.Sale_PaidAmntBase1,0) + ifNull(AccLink.Sale_ReturnAmntBase1,0))) As NotAllocatedBase1, ".
					" ((ifNull(Sales.Sale_TotalBase2,0) - ifNull(Sales.Sale_DiscountBase2,0) + ifNull(Sales.Sale_VATBase2,0)) - Sum(ifNull(AccLink.Sale_PaidAmntBase2,0) + ifNull(AccLink.Sale_ReturnAmntBase2,0))) As NotAllocatedBase2, ".
					" Sales.Journal_Type As TransType ".
					" From $DirSchema.Sales As Sales ".
					" Inner Join ".$_SESSION['dbLabel'].".Acclink As Acclink on Sales.Sale_Id = Acclink.Return_ID ".
					" Inner Join $DirSchema.Ledger As Ledger on Sales.Client_Code = Ledger.ledger_Number ";
					
			if ($projectexist >	0 && $FI_RepRestrictProject == 1)
				$QueryNotAllocated1 = $QueryNotAllocated1.$QueryRestrictPrj." on Sales.project_code like concat(QP.project_code,'%') ";
											 
			if ($costcentexist > 0 && $FI_RepRestrictCostcenter == 1)
				$QueryNotAllocated1 = $QueryNotAllocated1.$QueryRestrictCst." on Sales.Costcenter_code like concat(QC.costcent_code,'%') ";	
		
			$QueryNotAllocated1 = $QueryNotAllocated1.
					" Where Date_format(Sales.Sale_Date,'%Y-%m-%d') between ifNull('". $DirFromDate."',date_format(Sales.Sale_Date,'%Y-%m-%d')) and ifNull('". $DirToDate."',date_format(Sales.Sale_Date,'%Y-%m-%d'))  ".
					" And Date_format(Sales.Sale_Date,'%Y-%m-%d') between ifNull(".$fromdate.",date_format(Sales.Sale_Date,'%Y-%m-%d')) and ifNull(".$todate.",date_format(Sales.Sale_Date,'%Y-%m-%d'))  ".
					" And Sales.Client_Code between ifNull(".$fromledger.",Sales.Client_Code) and ifNull(". $toledger.",Sales.Client_Code) ".
					" And Sales.Journal_Type = 'SR' ".
					" And IfNull(Sales.Sale_OldData,0) = 0 ".
					" And Acclink.Return_Type In ('DR','SR') ".
					" And upper(Acclink.CompanyName) = upper('".$DirCompany."') ".
					" And upper(Acclink.DirYear) = upper('".$DirYear."') ".
					" Group By Sales.Sale_Id  ".
					" Having ifNull(NotAllocatedBase1,0) > 0 ".
					" Union ".
					" Select Sales.Sale_Id As InvoiceId, Sales.Client_Code As ClientCode, if(upper($curr) = '".$Base1."', '".$Base1."', '".$Base2."') CurrencyCode , ".
					" (ifNull(Sales.Sale_TotalBase1,0) - ifNull(Sales.Sale_DiscountBase1,0) + ifNull(Sales.Sale_VATBase1,0)) As NotAllocatedBase1, ".
					" (ifNull(Sales.Sale_TotalBase2,0) - ifNull(Sales.Sale_DiscountBase2,0) + ifNull(Sales.Sale_VATBase2,0)) As NotAllocatedBase2, ".
					" Sales.Journal_Type As TransType ".
					" From $DirSchema.Sales As Sales ".
					" Inner Join $DirSchema.Ledger As Ledger on Sales.Client_Code = Ledger.ledger_Number ";
					
			if ($projectexist >	0 && $FI_RepRestrictProject == 1)
				$QueryNotAllocated1 = $QueryNotAllocated1.$QueryRestrictPrj." on Sales.project_code like concat(QP.project_code,'%') ";
											 
			if ($costcentexist > 0 && $FI_RepRestrictCostcenter == 1)
				$QueryNotAllocated1 = $QueryNotAllocated1.$QueryRestrictCst." on Sales.Costcenter_code like concat(QC.costcent_code,'%') ";	
		
			$QueryNotAllocated1 = $QueryNotAllocated1.
					" Where Date_format(Sales.Sale_Date,'%Y-%m-%d') between ifNull('". $DirFromDate."',date_format(Sales.Sale_Date,'%Y-%m-%d')) and ifNull('". $DirToDate."',date_format(Sales.Sale_Date,'%Y-%m-%d'))  ".
					" And Date_format(Sales.Sale_Date,'%Y-%m-%d') between ifNull(".$fromdate.",date_format(Sales.Sale_Date,'%Y-%m-%d')) and ifNull(".$todate.",date_format(Sales.Sale_Date,'%Y-%m-%d'))  ".
					" And Sales.Client_Code between ifNull(".$fromledger.",Sales.Client_Code) and ifNull(". $toledger.",Sales.Client_Code) ".
					" And Sales.Journal_Type = 'SR' ".
					" And IfNull(Sales.Sale_OldData,0) = 0 ".
					" And Sales.Sale_Id Not In (Select ifNull(Return_ID,-1) From ".$_SESSION['dbLabel'].".Acclink  ".
					"                           Where Acclink.Return_Type In ('DR','SR') ".
					" 							And upper(Acclink.DirYear) = upper('".$DirYear."') ".
					" 							And upper(Acclink.CompanyName) = upper('".$DirCompany."')) ";
					
		}
		
		$QueryNotAllocated1 = $QueryNotAllocated1." Union ".
					" Select Trans.Trans_Id As InvoiceId, TransDt.Ledger_number As ClientCode, if(upper($curr) = '".$Base1."', '".$Base1."', '".$Base2."') CurrencyCode , ".
					" (ifNull(TransDt.Transdt_Amount1,0) - Sum(ifNull(AccLink.Sale_PaidAmntBase1,0))) As NotAllocatedBase1, ".
					" (ifNull(TransDt.Transdt_Amount2,0) - Sum(ifNull(AccLink.Sale_PaidAmntBase2,0))) As NotAllocatedBase2, ".
					" Trans.Trans_Type As TransType ".
					" From $DirSchema.TransDt As TransDt ".
					" Inner Join $DirSchema.Trans As Trans on TransDt.Trans_Id = Trans.Trans_Id ".
					" Inner Join ".$_SESSION['dbLabel'].".Acclink As Acclink on Acclink.Journal_Id = TransDt.TransDt_Id ".
					" Inner Join $DirSchema.Ledger As Ledger on TransDt.ledger_Number = Ledger.ledger_Number ";
					
		if ($projectexist >	0 && $FI_RepRestrictProject == 1)
			$QueryNotAllocated1 = $QueryNotAllocated1.$QueryRestrictPrj." on Trans.project_code like concat(QP.project_code,'%') ";
											 
		if ($costcentexist > 0 && $FI_RepRestrictCostcenter == 1)
			$QueryNotAllocated1 = $QueryNotAllocated1.$QueryRestrictCst." on Trans.costcent_code like concat(QC.costcent_code,'%') ";
		
		$QueryNotAllocated1 = $QueryNotAllocated1.
					" Where TransDt.Ledger_number between ifNull(".$fromledger.",TransDt.Ledger_number) and ifNull(". $toledger.",TransDt.Ledger_number) ".
					" And Date_format(Trans.Trans_Date,'%Y-%m-%d') between ifNull('". $DirFromDate."',date_format(Trans.Trans_Date,'%Y-%m-%d')) and ifNull('". $DirToDate."',date_format(Trans.Trans_Date,'%Y-%m-%d'))  ".
					" And Date_format(Trans.Trans_Date,'%Y-%m-%d') between ifNull(".$fromdate.",date_format(Trans.Trans_Date,'%Y-%m-%d')) and ifNull(".$todate.",date_format(Trans.Trans_Date,'%Y-%m-%d'))  ".
					" And Trans.Trans_Type In ('RV','CV') ".
					" And IfNull(TransDt.TransDt_OldData,0) = 0 ".
					" And TransDt.TransDt_dbcd = 'C' ".
					" And Acclink.Journal_Type In ('RV','CV') ".
					" And upper(Acclink.CompanyName) = upper('".$DirCompany."') ".
					" And upper(Acclink.DirYear) = upper('".$DirYear."') ".
					" Group By Trans.Trans_Id  ".
					" Having ifNull(NotAllocatedBase1,0) > 0 ".
					" Union ".
					" Select Trans.Trans_Id As InvoiceId, TransDt.Ledger_number As ClientCode, if(upper($curr) = '".$Base1."', '".$Base1."', '".$Base2."') CurrencyCode , ".
					" ifNull(TransDt.Transdt_Amount1,0) As NotAllocatedBase1, ifNull(TransDt.Transdt_Amount2,0) As NotAllocatedBase2, ".
					" Trans.Trans_Type As TransType ".
					" From $DirSchema.TransDt As TransDt ".
					" Inner Join $DirSchema.Trans As Trans on TransDt.Trans_Id = Trans.Trans_Id ".
					" Inner Join $DirSchema.Ledger As Ledger on TransDt.ledger_Number = Ledger.ledger_Number ";
					
		if ($projectexist >	0 && $FI_RepRestrictProject == 1)
			$QueryNotAllocated1 = $QueryNotAllocated1.$QueryRestrictPrj." on Trans.project_code like concat(QP.project_code,'%') ";
											 
		if ($costcentexist > 0 && $FI_RepRestrictCostcenter == 1)
			$QueryNotAllocated1 = $QueryNotAllocated1.$QueryRestrictCst." on Trans.costcent_code like concat(QC.costcent_code,'%') ";
		
		$QueryNotAllocated1 = $QueryNotAllocated1.
					" Where TransDt.Ledger_number between ifNull(".$fromledger.",TransDt.Ledger_number) and ifNull(". $toledger.",TransDt.Ledger_number) ".
					" And Date_format(Trans.Trans_Date,'%Y-%m-%d') between ifNull('". $DirFromDate."',date_format(Trans.Trans_Date,'%Y-%m-%d')) and ifNull('". $DirToDate."',date_format(Trans.Trans_Date,'%Y-%m-%d'))  ".
					" And Date_format(Trans.Trans_Date,'%Y-%m-%d') between ifNull(".$fromdate.",date_format(Trans.Trans_Date,'%Y-%m-%d')) and ifNull(".$todate.",date_format(Trans.Trans_Date,'%Y-%m-%d'))  ".
					" And Trans.Trans_Type In ('RV','CV') ".
					" And TransDt.TransDt_dbcd = 'C' ".
					" And IfNull(TransDt.TransDt_OldData,0) = 0 ".
					" And TransDt.Transdt_Id Not In (Select ifNull(Acclink.Journal_Id,-1) From ".$_SESSION['dbLabel'].".Acclink  ".
					"                          		 Where Acclink.Journal_Type In ('RV','CV') ".
					" 								 And upper(Acclink.DirYear) = upper('".$DirYear."') ".
					" 								 And upper(Acclink.CompanyName) = upper('".$DirCompany."')) ";
					
					
					
		$QueryNotAllocated1 = $QueryNotAllocated1." Union ".
					" Select Journal.Journal_Id As InvoiceId, JournalDt.Ledger_number As ClientCode, if(upper($curr) = '".$Base1."', '".$Base1."', '".$Base2."') CurrencyCode , ".
					" (ifNull(Journaldt_amount1,0) - Sum(ifNull(AccLink.Sale_PaidAmntBase1,0))) As NotAllocatedBase1, ".
					" (ifNull(Journaldt_amount2,0) - Sum(ifNull(AccLink.Sale_PaidAmntBase2,0))) As NotAllocatedBase2, ".
					" Journal.Journal_Type As TransType ".
					" From $DirSchema.JournalDt As JournalDt ".
					" Inner join $DirSchema.Journal As Journal on JournalDt.Journal_id = Journal.Journal_id ".
					" Inner Join ".$_SESSION['dbLabel'].".Acclink As Acclink on Acclink.Journal_Id = JournalDt.JournalDt_Id And Acclink.Journal_Type = Journal.Journal_Type ".
					" Inner Join $DirSchema.Ledger As Ledger on JournalDt.ledger_Number = Ledger.ledger_Number ".
					" Where JournalDt.Ledger_number between ifNull(".$fromledger.",JournalDt.Ledger_number) and ifNull(". $toledger.",JournalDt.Ledger_number) ".
					" And Date_format(Journal.Journal_Date,'%Y-%m-%d') between ifNull('". $DirFromDate."',date_format(Journal.Journal_Date,'%Y-%m-%d')) and ifNull('". $DirToDate."',date_format(Journal.Journal_Date,'%Y-%m-%d'))  ".
					" And Date_format(Journal.Journal_Date,'%Y-%m-%d') between ifNull(".$fromdate.",date_format(Journal.Journal_Date,'%Y-%m-%d')) and ifNull(".$todate.",date_format(Journal.Journal_Date,'%Y-%m-%d'))  ".
					" And JournalDt.Journaldt_dbcd = 'C' ".
					" And Journal.Journal_Type Not In ('RV','CV','SI','SR','OP') ".
					" And IfNull(JournalDt.JournalDt_OldData,0) = 0 ".
					" And upper(Acclink.CompanyName) = upper('".$DirCompany."') ".
					" And upper(Acclink.DirYear) = upper('".$DirYear."') ".
					" Group By Journal.Journal_Id  ".
					" Having ifNull(NotAllocatedBase1,0) > 0 ".
					" Union ".
					" Select Journal.Journal_Id As InvoiceId, JournalDt.Ledger_number As ClientCode, if(upper($curr) = '".$Base1."', '".$Base1."', '".$Base2."') CurrencyCode , ".
					" ifNull(Journaldt_amount1,0) As NotAllocatedBase1, ifNull(Journaldt_amount2,0) As NotAllocatedBase2, ".
					" Journal.Journal_Type As TransType ".
					" From $DirSchema.JournalDt As JournalDt ".
					" Inner join $DirSchema.Journal As Journal on JournalDt.Journal_id = Journal.Journal_id ".
					" Inner Join $DirSchema.Ledger As Ledger on JournalDt.ledger_Number = Ledger.ledger_Number ".
					" Where JournalDt.Ledger_number between ifNull(".$fromledger.",JournalDt.Ledger_number) and ifNull(". $toledger.",JournalDt.Ledger_number) ".
					" And Date_format(Journal.Journal_Date,'%Y-%m-%d') between ifNull('". $DirFromDate."',date_format(Journal.Journal_Date,'%Y-%m-%d')) and ifNull('". $DirToDate."',date_format(Journal.Journal_Date,'%Y-%m-%d'))  ".
					" And Date_format(Journal.Journal_Date,'%Y-%m-%d') between ifNull(".$fromdate.",date_format(Journal.Journal_Date,'%Y-%m-%d')) and ifNull(".$todate.",date_format(Journal.Journal_Date,'%Y-%m-%d'))  ".
					" And JournalDt.Journaldt_dbcd = 'C' ".
					" And Journal.Journal_Type Not In ('RV','CV','SI','SR','OP') ".
					" And IfNull(JournalDt.JournalDt_OldData,0) = 0 ".
					" And JournalDt.JournalDt_Id Not In (Select ifNull(Acclink.Journal_Id,-1) From ".$_SESSION['dbLabel'].".Acclink  ".
					"                          		 	 Where Acclink.Journal_Type Not In ('RV','CV','PV','DV') ".
					" 									 And upper(Acclink.DirYear) = upper('".$DirYear."') ".
					" 									 And upper(Acclink.CompanyName) = upper('".$DirCompany."')) ";			
					 
		if ($RowsDirCount > 1 && $j < $RowsDirCount)	
            $QueryNotAllocated1 = $QueryNotAllocated1." Union ";
		
	}			
} // End Loop For Directories

$QueryNotAllocated = $QueryNotAllocated.$QueryNotAllocated1.
					" 	) As QBal Group by ClientCode, CurrencyCode ".
					" ) Q11 on Q11.ClientCode = QClt.ClientCode ";
					
$QueryNotAllocated = $QueryNotAllocated.
					//" Group By ClientCode, CurrencyCode ".
					" Order By ClientCode, CurrencyCode ";
	
//echo $QueryNotAllocated; exit;					

$query = $query." ".$QueryNotAllocated.
				" ) As QNotAllocated on QNotAllocated.ClientCode = Q1.ClientCode ";

$query = $query." 	Order By Q1.ClientCode, Q1.CurrencyCode, Q2.OrderSalesDate ".
				" ) As Q6  ";
				//" where Q6.TransRemAmount != 0   ";
				
$query = $query." ) Q7  Group By ClientCode, CurrencyCode, SalesRefer Having ((TransRemAmount != TransPaidAmount And TransRemAmount != 0) or (TransRemAmount = 0))  ";
$query = $query." ) Q8  Group By ClientCode, CurrencyCode Having ((TransRemAmount != TransPaidAmount And TransRemAmount != 0) or (TransRemAmount = 0))  ";
$query = $query." Order By ClientCode, CurrencyCode  ";
	
//echo $query;exit;

$ArrayTotalByReport   = Array();

$result=mysql_query($query,$connection) or die(mysql_error());
$col=0;
while($rows=mysql_fetch_row($result)){
	if($col%2==0){
		$color = "style='background-color:".$report_color1."'";
	}else{
		$color = "style='background-color:".$report_color2."'";
	}		
	echo "<tr><td colspan='3'>";
	
	//Create Table Columns
	CreateTableStructure($numrows, $shownondues, $riskbal);

	echo"	<tr $color>";
	echo "		<td align='left'   valign='top' style='border-right:1px solid black; padding-left:2px;'>". $rows[0] ."</td>";
	echo "		<td align='left'   valign='top' style='border-right:1px solid black; padding-left:2px;'>". $rows[1] ."</td>";
	echo "		<td align='center' valign='top' style='border-right:1px solid black; '>". $rows[3] ."</td>";
	echo "		<td align='right'  valign='top' style='border-right:1px solid black; padding-right:2px;'>". format_currency($rows[2], $DecimalBase) ."</td>";
	
	$col+=1;
	
	if ($riskbal == 1) {	
		echo "	<td align='right' valign='top' style='border-right:1px solid black; padding-right:2px;'>". format_currency($rows[9], $DecimalBase) ."</td>";
	}
	
	echo "		<td align='right' valign='top' style='border-right:1px solid black; padding-right:2px;'>". format_currency($rows[10], $DecimalBase) ."</td>";
	echo "		<td align='right' valign='top' style='border-right:1px solid black; padding-right:2px;'>". format_currency($rows[11], $DecimalBase) ."</td>";
	
	echo "		<td align='center' valign='top' style='border-right:1px solid black; '>". $rows[4] ."</td>";
	echo "		<td align='right' valign='top' style='border-right:1px solid black; padding-right:2px;'>". format_currency($rows[7], $DecimalBase) ."</td>";
	
	if ($shownondues == 1)
		echo "	<td align='right' valign='top' style='border-right:1px solid black; padding-right:2px;'>". format_currency($rows[8], $DecimalBase) ."</td>";
	
	
	for ( $d = 0; $d < count($ArrayPeriods); $d += 1) {
		$j = 12 + $d;
		echo "	<td align='right' valign='top' style='border-right:1px solid black; padding-right:2px;'>&nbsp;"; if($rows[$j] != '') echo format_currency($rows[$j], $DecimalBase); else $rows[$j]; echo"</td>";
	}
	
	echo "		<td align='right' valign='top' style='border-right:1px solid black; padding-right:2px;'>"; if($rows[$j+1] != '') echo format_currency($rows[$j+1], $DecimalBase); else $rows[$j+1]; echo"</td>";		
			
	echo"   </tr>";	
	echo"</table></td></tr>";

	
	
	
	$ArrayTotalByReport['Balance']  = $ArrayTotalByReport['Balance'] + $rows[7];
	$ArrayTotalByReport['NonDues']  = $ArrayTotalByReport['NonDues'] + $rows[9];
	$ArrayTotalByReport['NotAllocated']  = $ArrayTotalByReport['NotAllocated'] + $rows[10];
	$ArrayTotalByReport['ActBal']   = $ArrayTotalByReport['ActBal']  + $rows[11];
	
	for ( $d = 0; $d < count($ArrayPeriods); $d += 1) {
		$j = 12 + $d;
		$ArrayTotalByReport['Amount'][$d]  = $ArrayTotalByReport['Amount'][$d] + $rows[$j];
		
		if ($d + 1 == count($ArrayPeriods))
			$ArrayTotalByReport['Amount'][$d + 1]  = $ArrayTotalByReport['Amount'][$d + 1] + $rows[$j + 1];
	}
	
			
				
		
}

//$result = mysql_query("rollback", $connection);

	
// Totals By Report
echo "<tr>";
echo "  <td colspan='3'>&nbsp;</td>";
echo "</tr>";

echo "  <tr style='background-color: #F0F0F0;'><td colspan='3'>";

$StyleClass  = 'totals';
$TotalsLabel = 'Total';
DisplayTotals($numrows, $shownondues, $riskbal, $ArrayTotalByReport, $DecimalBase, $TotalsLabel, $StyleClass);

echo"	</td></tr>";
	
	
	
echo "</table></tr></td></table>";

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

function CreateTableStructure($PColsCount, $PShowNonDues, $PRiskBal) {
	
	echo "    <table  border='0' align='left' class='master' style='table-layout:fixed;width:200px;' cellpadding='0' cellspacing='0'>
				<col width=80>
				<col width=150>
				<col width=40>
				<col width=70>";

	if ($PRiskBal == 1) echo" <col width=100>";
	
	echo "<col width=100>";
	echo "<col width=100>";
	echo" <col width=40> <col width=110>";
	
	if ($PShowNonDues == 1) echo" <col width=110>";
	
	for ( $d = 1; $d <= $PColsCount; $d += 1) {
		echo" <col width=110>";
	}
	
	echo" <col width=110>";
	

}

function DisplayTotals($PColsCount, $PShowNonDues, $PRiskBal, $PArrayTotal, $PDecimalBase, $PTotalsLabel, $PStyleClass) {
	
	echo "    <table  border='0' align='left' class='".$PStyleClass."' style='table-layout:fixed' cellpadding='0' cellspacing='0'>
				<col width=80>
				<col width=150>
				<col width=40>
				<col width=70>";

	if ($PRiskBal == 1) echo" <col width=100>";
	
	echo" <col width=100>";
	echo" <col width=100>";
	echo" <col width=40> <col width=110>";
	
	if ($PShowNonDues == 1) echo" <col width=110>";
	
	for ( $d = 1; $d <= $PColsCount; $d += 1) {
		echo" <col width=110>";
	}
	
	echo" <col width=110>";
	
	echo"<tr>";
	
	if ($PRiskBal == 1)
		echo "  <td align='right' valign='top' colspan='5'>".$PTotalsLabel."</td>";
	else	
		echo "  <td align='right' valign='top' colspan='4'>".$PTotalsLabel."</td>";	
	
	echo "<td align='right' valign='top'>". format_currency($PArrayTotal['NotAllocated'], 0) ."</td>";
	echo "<td align='right' valign='top'>". format_currency($PArrayTotal['ActBal'], 0) ."</td>";
	echo "<td align='right' valign='top'>&nbsp;</td>";
	echo "<td align='right' valign='top'>". format_currency($PArrayTotal['Balance'], 0) ."</td>";
	
	if ($PShowNonDues == 1)
		echo "<td align='right' valign='top'>". format_currency($PArrayTotal['NonDues'], 0) ."</td>";
	
	for ( $d = 0; $d < $PColsCount; $d += 1) {
		echo "<td align='right' valign='top'>". format_currency($PArrayTotal['Amount'][$d], 0) ."</td>";
		
		if ($d + 1 == $PColsCount)
			echo "<td align='right' valign='top'>". format_currency($PArrayTotal['Amount'][$d + 1], 0) ."</td>";
	}
	
	echo"     </tr>";	
	echo"	</table>";
	
}

if ($printaccess==1)
print <<< HERE

<script language=javascript>
if (typeof(window.print) != 'undefined')
{
    window.print();
}
</script>
</BODY>
</HTML>
HERE;

?>