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


print <<< HERE
<HTML>
<HEAD>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1256" />
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<TITLE> Aging Report </TITLE>
<META NAME="Generator" CONTENT="EditPlus">
<META NAME="Author" CONTENT="">
<META NAME="Keywords" CONTENT="">
<META NAME="Description" CONTENT="">

<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 tr.header { font-size: 12px; 
						 font-weight: bold;
						 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.detail th { font-size: 12px; 
                  font-weight: bold;
                  font-family:Times-New-Roman;
				 }				 
				
table.detail td { font-size: 11px; 
                  font-family:Times-New-Roman;
                 }	
				 
table.group td { font-size: 12px; 
                 font-weight: bold;
                 font-family:Times-New-Roman;
               }				 				 


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

$linkfromdate = $fromdate;
$linktodate = $todate;
$linkfromprj = $fromproject;
$linktoprj = $toproject;

$linkfromcst = $fromcst;
$linkDateOpt = 1;
if($showvdate == 1) $linkDateOpt = 2;

if($curr == $base1) {
	$linkcurrbase1 = 1;
	$linkcurrbase2 = 0;
	$linkcurr= 0;
}
else if($curr == $base2) {
	$linkcurrbase1 = 0;
	$linkcurrbase2 = 1;
	$linkcurr= 0;
}
else {
	$linkcurrbase1 = 0;
	$linkcurrbase2 = 0;
	$linkcurr= 1;
}  

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

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



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

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');
$termsdays     = GetSQLValueString($termsdays, 'double');

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."<br>";
//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) . "<br>" . $SelectedOptions;

  
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='50%' align='center'><font size=4><b>Detail Aging-$title</b></font></td>
  <td width='10%'><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;

echo "<table  border='0' width='100%' align='center' class='master' style='table-layout:fixed; border-collapse:collapse;' cellpadding='0' cellspacing='0'>
      <col width=80>
	  <col width=150>
	  <col width=80>
	  <col width=40>
	  <col width=100>
	  <col width=100>
	  <col width=40>
	  <col width=110>
	  <col width=100>
	  <thead>
<tr height=30px bgcolor='lightgray'>
    <th>Account No.</th>
	<th>Account Name</th>
	<th>Credit Limit</th>
	<th>PT</th>
	<th>Previous Debit</th>
	<th>Previous Credit</th>
	<th>Cur.</th>
	<th>Previous Balance</th>
	<th>Actual Balance</th>
</tr></thead>";
echo "<tr><td colspan='9' align='left' valign='top'><table width='100%' border='0' align='left' class='detail'  style='border-collapse:collapse;' cellpadding='0' cellspacing='0'>		
		  <col width=340>
	      <col width=60>
	      <col width=90>";
	if($showvdate == 1)
	echo "<col width=90>";		
	echo "<col width=60>
	      <col width=90>
		  <col width=100>
		  
		  <tr height=20px bgcolor='#e0ffff'>
			<th align='left'>Description</th>
			<th>Folio</th>
			<th>Invoice Date</th>";
	if($showvdate == 1)
		echo "<th>Value Date</th>";
	echo "<th>Due Days</th>
			<th>Amount</th>
			<th>Balance</th>
		  </tr>
	 </table></td></tr>";


print <<<HERE
<tr>
    <td colspan='8'><hr width='100%' color='black' size='2'></td>
</tr>
 
HERE;
 
$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';
}

$count = 0;
$byledgergrp = $_GET['byledgergrp'];

if($byledgergrp == '') $byledgergrp = 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 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, '' as 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')))   ";
					}
					
					//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'))  ";
					}
					$QueryDirSchema .=  " And ifNull(journal.Journal_BelongsToGroup,0) >= ifNull($FI_BelongsToGrp,0) ";
					
					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 ";
/*xxxxxxxxxx
$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 and Q6.Balance > 0, Q6.AgingBalance < 0, 1 = 1) )) ".
				" And Q6.Balance != 0 ";
*/
$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  and Q6.AgingAmount <> 0, 1 = 1) )) ".
				" And round(abs(Q6.Balance),$DecimalBase) != 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."<br><br>";
//exit;				
	
//echo $queryjournal;exit;
$ArrayTotalByReport   = Array();
$ArrayTotalByGroup1   = Array();
$ArrayTotalByGroup2   = Array();
$ArrayTotalByGroup3   = Array();

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

$LastBalance=0;
$firstBalance=0;

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

if($numjournal > 0 && $byledgergrp == 1)
echo "<tr height=25px>
		<td colspan='3' style='color:blue;'><b> --> $ledgergrp_code - $ledgergrp_name </b></td>
	</tr>";
while($rowsjournal=mysql_fetch_row($resultjournal)){
	//Group By Options Level 1 Names 
	if ( ($groupby1 != '' && ($showdetail == 1 || $groupby2 != '' || $groupby3 != 1)) && ($MainGroup1 == '' || $MainGroup1 != $rowsjournal[0])  ){
			
		if ($MainGroup1 != '') {
			echo "<tr>";
			echo "   <td align='left' valign='top' colspan='7' class='grouplevel1'>&nbsp;</td>";
			echo "   <td align='right' valign='top' class='grouplevel1' style='border-top:1px solid black;'>".format_currency($ArrayTotalByGroup1['Balance'], $DecimalBase)."</td>";
			echo "</tr>";
			
			unset($ArrayTotalByGroup1);		
			unset($ArrayTotalByGroup2);		
			unset($ArrayTotalByGroup3);
		}
		
		$groupby1code = '';
		if ($rowsjournal[0] == -1)
		   $groupby1code = '';
		else
            $groupby1code = $rowsjournal[0];			
		
		echo "<tr>";
		echo "   <td align='left' valign='top' colspan='8' class='grouplevel1'>Group by  ". $groupby1name." :&nbsp;&nbsp;". 
		         str_replace('*',' --> ',$groupby1code) ."&nbsp;&nbsp; - &nbsp;&nbsp;".
		         str_replace('*',' --> ',$rowsjournal[1]) ."</td>";
		echo "</tr>"; 
		
		if  ($groupby1 != '' && $groupby2 == '' && $groupby3 == 1) {
			echo "<tr>";
			echo "   <td align='left' valign='top' colspan='8'>&nbsp;</td>";
			echo "</tr>";
		}
				
						
		$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])  ){
			
		if ($MainGroup2 != '') {
			echo "<tr>";
			echo "   <td align='left' valign='top' colspan='7' class='grouplevel2'>&nbsp;</td>";
			echo "   <td align='right' valign='top' class='grouplevel2' style='border-top:1px solid black;'>".format_currency($ArrayTotalByGroup2['Balance'], $DecimalBase)."</td>";
			echo "</tr>";
			
			unset($ArrayTotalByGroup2);		
			unset($ArrayTotalByGroup3);
		}
		
		$groupby2code = '';
		if ($rowsjournal[2] == -1)
		   $groupby2code = '';
		else
              $groupby2code = $rowsjournal[2];			
		
		echo "<tr>";
		echo "   <td align='left' valign='top' colspan='8' class='grouplevel2'>Group by  ". $groupby2name." :&nbsp;&nbsp;". 
		         str_replace('*',' --> ',$groupby2code) ."&nbsp;&nbsp; - &nbsp;&nbsp;".
		         str_replace('*',' --> ',$rowsjournal[3]) ."</td>";
		echo "</tr>"; 
		
		if  ($groupby1 == '' && $groupby2 != '' && $groupby3 == 1) {
			echo "<tr>";
			echo "   <td align='left' valign='top' colspan='8'>&nbsp;</td>";
			echo "</tr>";
		}
				
						
		$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])  ){
			
		if ($MainGroup3 != '') {
			echo "<tr>";
			echo "   <td align='left' valign='top' colspan='7' class='grouplevel3'>&nbsp;</td>";
			echo "   <td align='right' valign='top' class='grouplevel3' style='border-top:1px solid black;'>".format_currency($ArrayTotalByGroup3['Balance'], $DecimalBase)."</td>";
			echo "</tr>";
			
			unset($ArrayTotalByGroup3);
		}
		
		$groupby3code = '';
		if ($rowsjournal[4] == -1)
		   $groupby3code = '';
		else
            $groupby3code = $rowsjournal[4];			
			   
		echo "<tr>";
		echo "   <td align='left' valign='top' colspan='8' class='grouplevel3'>Group by  ". $groupby3name." :&nbsp;&nbsp;". 
		         str_replace('*',' --> ',$groupby3code) ."&nbsp;&nbsp; - &nbsp;&nbsp;".
		         str_replace('*',' --> ',$rowsjournal[5]) ."</td>";
		echo "</tr>"; 
				
		echo "<tr>";
		echo "   <td align='left' valign='top' colspan='8'>&nbsp;</td>";
		echo "</tr>"; 
						
		$MainGroup3   = $rowsjournal[4];
		$groupby3desc = $rowsjournal[5];
			
		$MainRecord = '';	
		//unset($ArrayTotalByGroup3);
	}
	
       
	if ($MainRecord == '' || $MainRecord != $rowsjournal[6]) {
	    
		if($count > 0 && $numjournal > 0 ){
		$ActualBal = $totalDuePerAcc + $totalNonDuePerAcc;
		echo "<tr height=25px class='header' style='background-color: #F0F0F0;'>
				<td style='color:#2f4f4f;' colspan='2'><b> --> Total Bills Due: ".format_currency($totalDuePerAcc, $DecimalBase)."</b></td>
				<td style='color:#2f4f4f;' colspan='3'><b> --> Total Bills Not Due: ".format_currency($totalNonDuePerAcc, $DecimalBase)."</b></td>
				<td style='color:#2f4f4f;' colspan='2'><b> --> Total: ".format_currency($ActualBal, $DecimalBase)."</b></td>
			</tr>";	
			
		}
		$totalDuePerAcc = 0;
		$totalNonDuePerAcc = 0;
		$ActualBal = 0;
		if ($MainRecord != '') {
			echo "<tr>";
			echo "<td colspan='8'><hr width='100%' color='black' size='1'></td>";
			echo "</tr>";
		}	
				
		$link = "rep_statementofaccount.php?fromdate=$linkfromdate&todate=$linktodate&fromledger=$rowsjournal[6]&toledger=$rowsjournal[6]&fromproject=$linkfromprj&fromcst=$linkfromcst&fromcur=&dateoption=1&transaction=3&ledgeroption=1&curr=$linkcurr&currbase1=$linkcurrbase1&currbase2=$linkcurrbase2&cum=0&alltypes=0&exclude=0&jvtype=&showunposted=0&prtfolio=1&prtref=1&prtdesc=1&prtvaluedate=0&prtjvtype=0&prtcostcentc=0&prtcostcent=0&prtproject=0&prtprojectc=0&prevbal=1&dtjv=0&dtdate=1&checks=0&cltcst=&cltprj=&parentprj=0&prtaging=0&prtheader=1&prtcstjour=0&prtwording=0&salesman=&prtwnac=0&showman=1&varprtbreak=1&prtshowuser=0&reporttype=1";
		echo "<tr class='header' bgcolor='lightgrey'>"; 
		echo "<td align='left' valign='top'><a href='$link' target='_blank' style='text-decoration: none;color:#000000;'>". $rowsjournal[6] ."</a></td>";
		echo "<td align='left' valign='top'>". $rowsjournal[7] ."</td>";
		echo "<td align='right' valign='top'>". number_format($rowsjournal[8], $DecimalBase) ."</td>";
		echo "<td align='right' valign='top'>". number_format($rowsjournal[32], $DecimalBase) ."</td>";
		echo "<td align='right' valign='top'>". number_format($rowsjournal[16], $DecimalBase) ."</td>";
		
		echo "<td align='right' valign='top'>". number_format($rowsjournal[17], $DecimalBase) ."</td>";
		echo "<td align='center' valign='top'>". $rowsjournal[15] ."</td>";
		echo "<td align='right' valign='top'>". format_currency($rowsjournal[18], $DecimalBase) ."</td>";
		echo "<td align='right' valign='top'>". format_currency($rowsjournal[28], $DecimalBase) ."</td>";
		echo "</tr>";
		
		if ($showaddress == 1 && $rowsjournal[31] != '-') {
			echo "<tr class='header'>"; 
			echo "	<td align='left' valign='top'>&nbsp;</td>";
			echo "	<td align='left' valign='top' colspan='7'>".$rowsjournal[31]."</td>";
			echo "</tr>";
		}
		
		$MainRecord = $rowsjournal[6];
		
		if ($rowsjournal[27] != 0) {
			$ArrayTotalByGroup1['Balance'] += $rowsjournal[24];
			$ArrayTotalByGroup2['Balance'] += $rowsjournal[24];
			$ArrayTotalByGroup3['Balance'] += $rowsjournal[24];
		}
		$totalDuePerAcc = 0;		
		$totalNonDuePerAcc = 0;		
		$firstBalance = 0;		
	}	
		
	  
	if (($rowsjournal[27] != 0) or (1)) {
		
		echo "<tr>"; 
		echo "<td colspan='9' align='left' valign='top'><table width='100%' border='0' align='left' class='detail'  style='border-collapse:collapse;' cellpadding='0' cellspacing='0'>		
			  <col width=45>
			  <col width=350>
			  <col width=60>
			  <col width=90>";
		if($showvdate == 1)	  
			echo "<col width=90>";
			 echo "<col width=60>
			  <col width=90>
			  <col width=100>";
		   
		echo "<tr>";
		echo "<td valign='top' >&nbsp;</td>";
		echo "<td valign='top' >".$rowsjournal[19]."</td>";
		echo "<td valign='top' >".$rowsjournal[20]."</td>";
		echo "<td valign='top' >".$rowsjournal[22]."</td>";
		if($showvdate == 1)
			echo "<td valign='top' >".$rowsjournal[33]."</td>";
		echo "<td valign='top' >".$rowsjournal[23]."</td>";
		echo "<td valign='top' align='right' > ".format_currency($rowsjournal[25], $DecimalBase)."</td>";
		echo "<td valign='top' align='right' > ".format_currency($rowsjournal[27], $DecimalBase)."</td>";	  
		echo "</tr>";
		  
		echo "</table></td>";
		echo "</tr>"; 
		
		if($firstBalance == 0 && abs($rowsjournal[34]) > 0){
			$totalDuePerAcc =  abs($rowsjournal[27]);
		} else $totalDuePerAcc +=  abs($rowsjournal[34]);
		
		if($firstBalance == 0 && abs($rowsjournal[35]) > 0){
			$totalNonDuePerAcc =  abs($rowsjournal[27]);
		} else $totalNonDuePerAcc +=  abs($rowsjournal[35]);
		
		$firstBalance++;
	}
	
	
	$count++;
	
}
if($numjournal > 0){
	$ActualBal = $totalDuePerAcc + $totalNonDuePerAcc;
echo "<tr height=25px class='header' style='background-color: #F0F0F0;'>
				<td style='color:#2f4f4f;' colspan='2'><b> --> Total Bills Due: ".format_currency($totalDuePerAcc, $DecimalBase)."</b></td>
				<td style='color:#2f4f4f;' colspan='3'><b> --> Total Bills Not Due: ".format_currency($totalNonDuePerAcc, $DecimalBase)."</b></td>
				<td style='color:#2f4f4f;' colspan='2'><b> --> Total: ".format_currency($ActualBal, $DecimalBase)."</b></td>
			</tr>";	
}
if ($MainGroup1 != '') {
	echo "<tr>";
	echo "   <td align='left' valign='top' colspan='7' class='grouplevel1'>&nbsp;</td>";
	echo "   <td align='right' valign='top' class='grouplevel1' style='border-top:1px solid black;'>".format_currency($ArrayTotalByGroup1['Balance'], $DecimalBase)."</td>";
	echo "</tr>";
			
	unset($ArrayTotalByGroup1);		
	unset($ArrayTotalByGroup2);		
	unset($ArrayTotalByGroup3);
}
if ($MainGroup2 != '') {
	echo "<tr>";
	echo "   <td align='left' valign='top' colspan='7' class='grouplevel2'>&nbsp;</td>";
	echo "   <td align='right' valign='top' class='grouplevel2' style='border-top:1px solid black;'>".format_currency($ArrayTotalByGroup2['Balance'], $DecimalBase)."</td>";
	echo "</tr>";
			
	unset($ArrayTotalByGroup2);		
	unset($ArrayTotalByGroup3);
}
if ($MainGroup3 != '') {
	echo "<tr>";
	echo "   <td align='left' valign='top' colspan='7' class='grouplevel3'>&nbsp;</td>";
	echo "   <td align='right' valign='top' class='grouplevel3' style='border-top:1px solid black;'>".format_currency($ArrayTotalByGroup3['Balance'], $DecimalBase)."</td>";
	echo "</tr>";
			
	unset($ArrayTotalByGroup3);
}



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





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

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

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

?>