<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<?php

session_start();
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: 13px; 
                  font-family:Times-New-Roman;
				  padding-left:3px;
				  padding-right:3px;
                }
table.master tr.group { font-size: 14px; 
                        font-weight: bold;
                        font-family:Times-New-Roman;
                      }				 				

table.master tr.totals { font-size: 12px; 
                         font-weight: bold;
                         font-family:Times-New-Roman;
                       }
		 

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


$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>';

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


$querysetup="select DecimalBase, DecimalBase1, DecimalBase2, report_color1, report_color2, Base1, Base2, Company_Name As CompanyName from setup";
$resultsetup=mysql_query($querysetup,$connection) or die(mysql_error());
while($rows=mysql_fetch_array($resultsetup)){
  extract($rows);
} 

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

//Read From Module Table
$querymodule=" Select ifNull(Module_Delivery,0) As Module_Delivery, ifNull(Module_AcctrxPostRV,0) As Module_AcctrxPostRV, ".
			 " ifNull(Module_AcctrxPostCV,0) As Module_AcctrxPostCV ".
			 " From Module ";
$resultmodule=mysql_query($querymodule,$connection) or die(mysql_error());
while($rowmodule=mysql_fetch_array($resultmodule)){
	extract($rowmodule);
}


$queryproject=" select distinct project.project_code, project_name ".
              " from project left join ".$_SESSION['dbLabel'].".loginstk on project.project_code like concat(ifnull(loginstk.project_code,project.project_code),'%') ".
			  " where user_account='".$_SESSION['useraccount']."' ".
			  " and (loginstk.project_code is not null and loginstk.project_code != '') ".
			  " order by project.project_code";
$projectresult = mysql_query($queryproject,$connection) or die(mysql_error());
$projectexist  = mysql_num_rows($projectresult);

$querycostcent=" select distinct costcent.costcent_code, costcent_name ".
               " from costcent left join ".$_SESSION['dbLabel'].".loginstk on costcent.costcent_code like concat(ifnull(loginstk.costcent_code,costcent.costcent_code),'%') ".
			   " where user_account='".$_SESSION['useraccount']."' ".
			   " and (loginstk.costcent_code is not null and loginstk.costcent_code != '') ".
			   " order by costcent.costcent_code";
$costcentresult = mysql_query($querycostcent,$connection) or die(mysql_error());
$costcentexist  = mysql_num_rows($costcentresult);	



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

print <<< HERE
<table width="100%" border="0" align="center">
<tr>
  <td width='30%'><b>$CompanyName</b></td>
  <td width='50%' align='center'><font size=4><b>Aging of Client Invoices</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 DATE("d"."/"."m"."/"."Y");
echo "<br>".$dt[1]." ".$dt[2];


if ($curr == 'currency1'){
   $curr='null';  
   $displaycur = '';
}   
else {
   $displaycur = $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 ($fromsalesman != '')    $SelectedOptions .= "-> Salesman From: ". $fromsalesman . "  To: ". $tosalesman ."<br>";

if($chksalesman == 1)   $groupbyOption .= "Group by: -> Salesman ";
  
if (!empty($fromdate))  $fromdate = explodeword($fromdate);
if (!empty($todate))    $todate = explodeword($todate);  
if (!empty($agingdate)) $agingdate = explodeword($agingdate);  
   
$fromledger    = GetSQLValueString($fromledger, 'text');
$toledger      = GetSQLValueString($toledger, 'text');
$fromdate  	   = GetSQLValueString($fromdate, 'date');
$todate        = GetSQLValueString($todate, 'date');     
$agingdate     = GetSQLValueString($agingdate, 'date');     

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

$fromsalesman    = GetSQLValueString($fromsalesman, 'text');
$tosalesman      = GetSQLValueString($tosalesman, 'text');

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

if($prtreturn == 1) $Label = 'Invoice/Return';
else $Label = 'Invoice';
	

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%" valign='top'><b>$groupbyOption</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' cellpadding='0' cellspacing='0'>";
echo" <col width=9%>
	  <col width=9%><col width=9%>
	  <col width=3%>
	  <col width=15%>
	  <col width=13%>
	  <col width=13%>
	  <col width=13%>
	  <col width=9%>";


if ($prtcheck == 1) $ColsSpan = '13';

$MainCompanyName=explode('_',$_SESSION['dbName']);  
$QueryDir = " Select Concat(CompanyName, '_', DirYear) As DirSchema, Date_Format(StartFinancialDate,'%Y-%m-%d') As StartFinancialDate, Date_Format(EndFinancialDate,'%Y-%m-%d') As EndFinancialDate, CompanyName, SUBSTRING(DirYear,-4) ".
			" From ".$_SESSION['dbLabel'].".Directories ". 
			" Where Upper(CompanyName) = Upper('".$MainCompanyName[0]."') ".
			" And ( (ifNull(Date_Format(". $fromdate.",'%Y%m'),Date_Format(StartFinancialDate,'%Y%m')) >= Date_Format(StartFinancialDate,'%Y%m') and ifNull(Date_Format(". $todate.",'%Y%m'),Date_Format(EndFinancialDate,'%Y%m')) <= Date_Format(EndFinancialDate,'%Y%m')) or   ".
			"       (Date_Format(StartFinancialDate,'%Y%m') >= ifNull(Date_Format(". $fromdate.",'%Y%m'),Date_Format(StartFinancialDate,'%Y%m')) and Date_Format(StartFinancialDate,'%Y%m') <= ifNull(Date_Format(". $todate.",'%Y%m'),Date_Format(EndFinancialDate,'%Y%m')) ) or ".
            "       (Date_Format(EndFinancialDate,'%Y%m') >= ifNull(Date_Format(". $fromdate.",'%Y%m'),Date_Format(StartFinancialDate,'%Y%m')) and Date_Format(EndFinancialDate,'%Y%m') <= ifNull(Date_Format(". $todate.",'%Y%m'),Date_Format(EndFinancialDate,'%Y%m')) ) ".
			"      )  ".
			" Order By DirYear ";
			

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


$QueryRestrictPrj = " Inner join ( select distinct project_code from ".$_SESSION['dbLabel'].".loginstk ".
					" 			   where loginstk.user_account='".$_SESSION['useraccount']."' ".
					"              and (project_code is not null and project_code != '') ".
					"			 ) QP  ";
					
$QueryRestrictCst = " Inner join ( select distinct costcent_code from ".$_SESSION['dbLabel'].".loginstk ".
					"			   where loginstk.user_account='".$_SESSION['useraccount']."' ".
					"              and (costcent_code is not null and costcent_code != '') ".
					"			 ) QC  ";
					
					
$query = '';
$query = $query." select InvoiceId, ClientCode, ClientName, date_format(SalesDate,'%d/%m/%Y') as SalesDate,SalesRefer,headerjournal, SalesTotalBase1, SalesTotalBase2, TransPaidBase1, TransPaidBase2,TransRemBase1, 
				TransRemBase2, RemainDays, agingdate2,Q2.PrevAmount1, Q2.PrevAmount2, CreditLimits,PaymentTerms, description, date_format(value_date, '%d/%m/%Y') as value_date,
				saleman_number, SalesmanName ";
	
$query = $query." From ( ";

$j = 0;				
$k = 0;

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

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

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

if($chksalesman == 1)
	$query = $query." Order by saleman_number, ClientCode,date_format(SalesDate,'%Y-%m-%d'), SalesRefer  ";
else	
	$query = $query." Order by ClientCode,date_format(SalesDate,'%Y-%m-%d'), SalesRefer  ";

	

//echo $query;exit;
$ArrayTotalByReport   = Array();
$ArrayTotalByMaster   = Array();
$ArraySalesAmount     = Array();
$ArraySalesRefer      = Array();

$ArrayAllTotal        = Array();

$ArrayFinalTotal = Array();

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

$ref = '';
$jourtype = '';
$headerid = '';
$result = mysql_query($query,$connection) or die(mysql_error());
while($rows=mysql_fetch_array($result)){
	extract($rows); 
	if($col%2==0){
		$color = "style='background-color:".$report_color1."'";
	}else{
		$color = "style='background-color:".$report_color2."'";
	}
	$ColsSpan = '9';
    // Master By Options Names and Totals
	if (($MasterByCode != $ClientCode && $MasterByCode != '')) {
				
		echo "<tr><td colspan='$ColsSpan'><hr width='100%' color='black' size='1'></td></tr>";
				
		$StyleClass  = 'totals';
		$TotalsLabel = 'Client Total';
		DisplayTotals($ArrayTotalByMaster, $DecimalBase, $TotalsLabel, $StyleClass) ;
		
		echo "<tr><td colspan='$ColsSpan'>&nbsp;</td></tr>";
	}
	
	//group by salesman
	if($MasterByCode2 != $saleman_number && $chksalesman == 1 && $MasterByCode2 != '*'){
			
		echo "<tr><td colspan='$ColsSpan'><hr width='100%' color='white' size='1'></td></tr>";
		$StyleClass  = 'totals';
		$TotalsLabel = 'All Clients Total / salesman';	
		 DisplayAllTotals($ArrayAllTotal, $DecimalBase, $TotalsLabel, $StyleClass );
		
		unset($ArrayAllTotal);

	}
	//group by salesman
	if($MasterByCode2 != $saleman_number && $chksalesman == 1){
		if($MasterByCode2 !='*') echo "<tr><td colspan='$ColsSpan'><hr width='100%' color='black' size='1'></td></tr>";
		echo "<tr height='30px' style='background-color:#dcdcdc;'><td colspan='$ColsSpan'><b>Salesman: ".$saleman_number." - ".$SalesmanName."</b></td></tr>";
	} 
	
	if ($MasterByCode == '' || $MasterByCode != $ClientCode) { 
		if ($displaycur == $Base1) {
			$BalanceAmount = $PrevAmount1;
		}
		else if ($displaycur == $Base2){
			$BalanceAmount = $PrevAmount1;
		}
		
		echo "<tr class='group' >";
		echo "	 <td colspan='4'>Client: ".$ClientCode." - ".$ClientName."</td>";
		echo "	 <td colspan='2'>CL: ".number_format($CreditLimits, $DecimalBase)."</td>";
		echo "	 <td colspan='2'>PT: ".$PaymentTerms."</td>";
		echo "   <td colspan='7' align='left' valign='top'>Balance: ". format_currency($BalanceAmount, $DecimalBase) ." $displaycur</td>";
		echo "   <td>&nbsp;</td>";
		echo "</tr>";

    $bright='border-right:black solid 1px;';
	$brleft='border-left:black solid 1px;';
	$brtop='border-top:black solid 1px;';
	$brbottom='border-bottom:black solid 1px;';	

echo"<tr>";	  
echo"	<th align='left'  >&nbsp;</th>
		<th align='left'  ></th>
		<th align='left' '></th>
		<th align='left' ></th>
		<th align='left' '></th>
		<th align='right' ></th>
		<th align='right' ></th>
		<th align='right' ></th>
		<th align='right' ></th>";	
echo"</tr>";	
		
echo"<tr>";	  
echo"	<th align='left' style='font-size: 11pt;font-weight: bold;$bright;$brtop;'>Date</th>
		<th align='left' style='font-size: 11pt;font-weight: bold;$bright;$brtop;'>Value Date</th>
		<th align='left' style='font-size: 11pt;font-weight: bold;$bright;$brtop;'>Reference</th>
		<th align='left' style='font-size: 11pt;font-weight: bold;$bright;$brtop;'>Type</th>
		<th align='left' style='font-size: 11pt;font-weight: bold;$bright;$brtop;'>Description</th>
		<th align='right' style='font-size: 11pt;font-weight: bold;$bright;$brtop;'>Amount</th>
		<th align='right' style='font-size: 11pt;font-weight: bold;$bright;$brtop;'>Paid Amount</th>
		<th align='right' style='font-size: 11pt;font-weight: bold;$bright;$brtop;'>Remaining Amount</th>
		<th align='right' style='font-size: 11pt;font-weight: bold;$bright;$brtop;'>Due Days</th>";	
echo"</tr>";
 


		
		//echo "<tr><td colspan='10'>&nbsp;</td></tr>";
		
		$MasterByCode = $ClientCode;
		$MasterByName = $ClientName;
		
		unset($ArrayTotalByMaster);
		unset($ArraySalesAmount);
		unset($ArraySalesRefer);
	}
	
	
	
	if ($displaycur == $Base1) {
		if($jourtype != $headerjournal || $ref != $SalesRefer || $headerid != $InvoiceId) {
			$NetAmount     = $SalesTotalBase1;
			$RemainAmount  = $TransRemBase1;
		}
		else {
			$NetAmount = 0;
			$RemainAmount  = 0;
		}
		$PaidAmount    = $TransPaidBase1;
		
	}
	else if ($displaycur == $Base2){
		if($ref != $SalesRefer || $jourtype != $headerjournal || $headerid != $InvoiceId) {
			$NetAmount     = $SalesTotalBase2;
			$RemainAmount  = $TransRemBase2;
		}
		else {
			$NetAmount = 0;
			$RemainAmount = 0;
		}
		$PaidAmount    = $TransPaidBase2;
		
	}
	
	
	if($ref != $SalesRefer || $jourtype != $headerjournal || $headerid != $InvoiceId){
		$btop='border-top:1px solid black';
		echo "<tr $color>";
		echo "   <td align='left' valign='top' style='$btop;border-right:1px solid black; padding-left:2px;'>". $SalesDate ."</td>";
		echo "   <td align='left' valign='top' style='$btop;border-right:1px solid black; padding-left:2px;'>". $value_date ."</td>";
		echo "   <td align='left' valign='top' style='$btop;border-right:1px solid black; padding-left:2px;'>". $SalesRefer ."</td>";
		echo "   <td align='left' valign='top' style='$btop;border-right:1px solid black; padding-left:2px;'>". $headerjournal ."</td>";
		echo "   <td align='left' valign='top' style='$btop;border-right:1px solid black; padding-left:2px;'>". $description ."</td>";
		echo "   <td align='right' valign='top' style='$btop;border-right:1px solid black; padding-right:2px;'>". format_currency($NetAmount, $DecimalBase) ."</td>";
		$ArrayAllTotal['AllNetAmount'] = $ArrayAllTotal['AllNetAmount'] + $NetAmount;
		$ArrayFinalTotal['AllNetAmount'] = $ArrayFinalTotal['AllNetAmount'] + $NetAmount; 
	} else {
		
		$btop = '';
		echo "<tr $color>";
		echo "   <td align='left' valign='top' style='border-right:1px solid black; padding-left:2px;'></td>";
		echo "   <td align='left' valign='top' style='border-right:1px solid black; padding-left:2px;'></td>";
		echo "   <td align='left' valign='top' style='border-right:1px solid black; padding-left:2px;'></td>";
		echo "   <td align='left' valign='top' style='border-right:1px solid black; padding-left:2px;'></td>";
		echo "   <td align='right' valign='top' style='border-right:1px solid black; padding-right:2px;'></td>";
		echo "   <td align='right' valign='top' style='border-right:1px solid black; padding-right:2px;'></td>";
		
	}
	echo "   <td align='right' valign='top' style='$btop;border-right:1px solid black; padding-right:2px;'>". format_currency($PaidAmount, $DecimalBase) ."</td>";
	$ArrayAllTotal['AllPaidAmount'] = $ArrayAllTotal['AllPaidAmount'] + $PaidAmount;	
	$ArrayFinalTotal['AllPaidAmount'] = $ArrayFinalTotal['AllPaidAmount'] + $PaidAmount;	
	echo "   <td align='right' valign='top' style='$btop;border-right:1px solid black; padding-right:2px;'>";
	if($ref != $SalesRefer || $jourtype != $headerjournal || $headerid != $InvoiceId){
		echo format_currency($RemainAmount, $DecimalBase);
		$ArrayAllTotal['AllRemainAmount'] = $ArrayAllTotal['AllRemainAmount'] + $RemainAmount;	
		$ArrayFinalTotal['AllRemainAmount'] = $ArrayFinalTotal['AllRemainAmount'] + $RemainAmount;
	}
	echo "</td>";
	
	echo "   <td align='right' valign='top' style='$btop;border-right:1px solid black; padding-right:2px;'>". $RemainDays ."</td>";
	
	echo "</tr>"; 	
	
	$RefFound = 0;
	$count = count ($ArraySalesRefer); 
	for ($j=0; $j<$count; $j++){
		if ($ArraySalesRefer[$j] == $SalesRefer)
			$RefFound = 1;
	}
				
	if ($RefFound == 0) 
		$ArraySalesRefer[$count] = $SalesRefer;
					
	if($ref != $SalesRefer || $jourtype != $headerjournal || $headerid != $InvoiceId) $ArraySalesAmount[$SalesRefer] = $ArraySalesAmount[$SalesRefer] + $NetAmount;
	$ArrayTotalByMaster['NetAmount']  = 0;
	
	$count = count ($ArraySalesRefer); 
	for ($j=0; $j<$count; $j++){
		$ArrayTotalByMaster['NetAmount']  = $ArrayTotalByMaster['NetAmount']  + $ArraySalesAmount[$ArraySalesRefer[$j]];
	}
	
	
	
	$ArrayTotalByMaster['PaidAmount']   = $ArrayTotalByMaster['PaidAmount'] + $PaidAmount;
	$ArrayTotalByMaster['RemainAmount'] = $ArrayTotalByMaster['NetAmount'] - $ArrayTotalByMaster['PaidAmount'];
	$jourtype = $headerjournal;
	$ref = $SalesRefer;
	$headerid = $InvoiceId;
	$col+=1;
	$MasterByCode2 = $saleman_number;
}

if ($MasterByCode != '' || ($MasterByCode2 != $saleman_number && $chksalesman == 1)) {
				
	echo "<tr><td colspan='$ColsSpan'><hr width='100%' color='black' size='1'></td></tr>";
				
	$StyleClass  = 'totals';
	$TotalsLabel = 'Client Total';
	DisplayTotals($ArrayTotalByMaster, $DecimalBase, $TotalsLabel, $StyleClass) ;

}
	

echo "<tr><td colspan='$ColsSpan'><hr width='100%' color='white' size='1'></td></tr>";
$StyleClass  = 'totals';
$TotalsLabel = 'All Clients Total ';
if($chksalesman == 1) $TotalsLabel .= " / salesman ";	
DisplayAllTotals($ArrayAllTotal, $DecimalBase, $TotalsLabel, $StyleClass );	


if($chksalesman == 1){
echo "<tr><td colspan='$ColsSpan'><hr width='100%' color='white' size='1'></td></tr>";
$StyleClass  = 'totals';
$TotalsLabel = 'Final Clients Total ';	

 DisplayAllTotals($ArrayFinalTotal, $DecimalBase, $TotalsLabel, $StyleClass );
}
echo "</table>";


function DisplayTotals($PArrayTotal, $PDecimalBase, $PTotalsLabel, $PStyleClass) {
	
	echo "<tr class='".$PStyleClass."'>";
	echo "   <td align='left'  valign='top' colspan='5' >".$PTotalsLabel."</td>";
	echo "   <td align='right' valign='top' >". format_currency($PArrayTotal['NetAmount'], $PDecimalBase) ."</td>";
	echo "   <td align='right' valign='top' >". format_currency($PArrayTotal['PaidAmount'], $PDecimalBase) ."</td>";
	echo "   <td align='right' valign='top' >". format_currency($PArrayTotal['RemainAmount'], $PDecimalBase) ."</td>";
	echo "   <td align='left'  valign='top' colspan='2' >&nbsp;</td>";
	echo "</tr>";	

}


function DisplayAllTotals($PArrayTotal, $PDecimalBase, $PTotalsLabel, $PStyleClass) {
	
	echo "<tr class='".$PStyleClass."'>";
	echo "   <td align='left'  valign='top' colspan='5' >".$PTotalsLabel."</td>";
	echo "   <td align='right' valign='top' >". format_currency($PArrayTotal['AllNetAmount'], $PDecimalBase) ."</td>";
	echo "   <td align='right' valign='top' >". format_currency($PArrayTotal['AllPaidAmount'], $PDecimalBase) ."</td>";
	echo "   <td align='right' valign='top' >". format_currency($PArrayTotal['AllRemainAmount'], $PDecimalBase) ."</td>";
	echo "   <td align='left'  valign='top' colspan='2' >&nbsp;</td>";
	echo "</tr>";	

}

function format_currency($amount, $DecimalBase) { 

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

function current_server_time($connection){

  $string="Select date_format(NOW(),'%Y-%m-%d %h:%i %p') ";
  $result=mysql_query($string,$connection) or die(mysql_error());
  $now=mysql_fetch_row($result);

  return $now;
}

function explodeword($date)
{

$trim = explode('/',$date);
$trim = "$trim[2]"."-"."$trim[1]"."-"."$trim[0]";
return $trim;
}

function GetSQLValueString($theValue, $theType) 
{
  $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "Null";
      break;    
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "Null";
      break;
    case "double":
      $theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "Null";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "Null";
      break;
  }
  return $theValue;
}

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

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

?>