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

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

set_time_limit(0);

print <<< HERE
<HTML>
<HEAD>


<script type="text/javascript" src="js/grid/UI_DataGrid.js"></script>
<script type="text/javascript" src="js/grid/jquery-1.9.1.js"></script>


<link type="text/css" href="js/calendar/jquery.datepick.css" rel="stylesheet">
<script type="text/javascript" src="js/calendar/jquery.datepick.js"></script>

<link type="text/css" href="js/grid/scripts/jquery-ui.css" rel="stylesheet">
<script type="text/javascript" src="js/grid/scripts/jquery-ui.min.js"></script>

<TITLE> Aging of Client Invoices Report </TITLE>

<style type="text/css">

.Hoverimg{
	position:fixed;
	visibility: visible;
    max-width:600px;
	top: 0;
	left: 0; 
	z-index: 200;
    left:50%;
    top:25%;
}
.Hoverimg2{
	position:fixed;
	visibility: visible;
    height:100vh;
    width:100%;
	top: 0;
	left: 0; 
	z-index: 180;
    left:0%;
    top:0%;
}
body{
    font-family: Times-New-Roman;
}
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;
                       }
		 
a{
    text-decoration: none;
    color: inherit;
}
</style>

<script>


function UNAllocateSup(fromledger) {
	var vDelMsg   = 'Are you sure you want to Fully Unallocate this Client ?';
	if (confirm ( vDelMsg ))
	{
		
		document.getElementById("loadingImg").classList.add('Hoverimg');
		document.getElementById("loadingImg").removeAttribute('style');
	
		document.getElementById("loadingImg2").classList.add('Hoverimg2');
		document.getElementById("loadingImg2").removeAttribute('style');

		var xmlhttp = getXMLHttpRequest();	
		
		xmlhttp.onreadystatechange=function()
		{
			if (xmlhttp.readyState==4 && xmlhttp.status==200)
			{ 
				var data = xmlhttp.responseText.split("[BRK]");
				  
				if(data[1] =='Done'){
					alert('Client Unallocated Successfully \\r refresh to See changes ');
				}

				if ( document.getElementById("loadingImg").classList.contains('Hoverimg') ){
					document.getElementById("loadingImg").classList.remove('Hoverimg');
					document.getElementById("loadingImg").style.display = 'none';

					
					document.getElementById("loadingImg2").classList.remove('Hoverimg2');
					document.getElementById("loadingImg2").style.display = 'none';
				}
			}
	    }
		xmlhttp.open("GET","Form_trans_Actions.php?mode=unallocateSupRep&SendLedger_number="+fromledger);
		xmlhttp.send();
		}
}

function AllocateSup(fromledger){
	var vDelMsg   = 'Are you sure you want to Auto Allocate this Client ?';
	if (confirm ( vDelMsg )) {
		
		document.getElementById("loadingImg").classList.add('Hoverimg');
		document.getElementById("loadingImg").removeAttribute('style');
	
		document.getElementById("loadingImg2").classList.add('Hoverimg2');
		document.getElementById("loadingImg2").removeAttribute('style');

		var xmlhttp = getXMLHttpRequest();	
		
		xmlhttp.onreadystatechange=function()
		{
			if (xmlhttp.readyState==4 && xmlhttp.status==200)
			{ 
				var data = xmlhttp.responseText.split("[BRK]");
				  
					if(data[1] =='Done'){
						alert('Client Allocated Successfully \\r refresh to See changes');
					}
					if ( document.getElementById("loadingImg").classList.contains('Hoverimg') ){
						document.getElementById("loadingImg").classList.remove('Hoverimg');
						document.getElementById("loadingImg").style.display = 'none';

						
						document.getElementById("loadingImg2").classList.remove('Hoverimg2');
						document.getElementById("loadingImg2").style.display = 'none';
					}
				}
	    }
		xmlhttp.open("GET","form_autoAllocateFIFO.php?referer=REP&SendLedger_number="+fromledger);
		xmlhttp.send();
	}
}


function AllocateSupManual(fromledger) {
	var vDelMsg   = 'Are you sure you want to Allocate Manually this Client ?';
	if (confirm ( vDelMsg ))
	{
		 window.open('Form_ADvancedPaymentAllocation1.php?fromledger='+fromledger);
	}
}

</script>
</HEAD>


HERE;


include("config.inc.php");


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


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



$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 ($fromledger != '')    $SelectedOptions .= "-> Ledger From: ". $fromledger . "  To: ". $toledger ."<br>"; 
if ($fromledger == $toledger)    $SelectedOptions .= "-> Ledger Name : ". getClientName($fromledger, $connection) ."<br>"; 

$disp_fromdate = $fromdate;
$disp_fromdate1 = date("01/01/Y");  
$disp_todate = $todate;
  
if (!empty($fromdate))  $fromdate = explodeword($fromdate);
if (!empty($todate))    $todate = explodeword($todate);  
if (!empty($agingdate)) $agingdate = explodeword($agingdate);  
   
   
   
 if($fromledger == ''){
	$querySups = "select min(ledger_number),max(ledger_number) from clients where ledger_number like '41%'";
	$resSups = mysql_query($querySups,$connection);
	$rowSups = mysql_fetch_array($resSups);
	$fromledger = $rowSups[0];
	$toledger = $rowSups[1];
 }
 
$fromledger    = GetSQLValueString($fromledger, 'text');
$toledger      = GetSQLValueString($toledger, 'text');
$fromdate  	   = GetSQLValueString($fromdate, 'date');
$todate        = GetSQLValueString($todate, 'date');     
$agingdate     = GetSQLValueString($agingdate, 'date');     

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

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

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

print <<<HERE
</font></td>
</tr>
 
</table>


<table class="params" width="100%" border="0" align="center" >
<tr>
  <td width="100%" valign='top'><b>$SelectedOptions</b></td>
</tr> 
</table>
HERE;

$MainCompanyName=explode('_',$_SESSION['dbName']);  
$QueryDir = " Select Concat(CompanyName, '_', DirYear) As DirSchema, Date_Format(StartFinancialDate,'%Y-%m-%d') As StartFinancialDate, Date_Format(EndFinancialDate,'%Y-%m-%d') As EndFinancialDate, CompanyName, SUBSTRING(DirYear,-4) ".
			" From ".$_SESSION['dbLabel'].".Directories ". 
			" Where Upper(CompanyName) = Upper('".$MainCompanyName[0]."') ".
			" And ( (ifNull(Date_Format(". $fromdate.",'%Y%m'),Date_Format(StartFinancialDate,'%Y%m')) >= Date_Format(StartFinancialDate,'%Y%m') and ifNull(Date_Format(". $todate.",'%Y%m'),Date_Format(EndFinancialDate,'%Y%m')) <= Date_Format(EndFinancialDate,'%Y%m')) or   ".
			"       (Date_Format(StartFinancialDate,'%Y%m') >= ifNull(Date_Format(". $fromdate.",'%Y%m'),Date_Format(StartFinancialDate,'%Y%m')) and Date_Format(StartFinancialDate,'%Y%m') <= ifNull(Date_Format(". $todate.",'%Y%m'),Date_Format(EndFinancialDate,'%Y%m')) ) or ".
            "       (Date_Format(EndFinancialDate,'%Y%m') >= ifNull(Date_Format(". $fromdate.",'%Y%m'),Date_Format(StartFinancialDate,'%Y%m')) and Date_Format(EndFinancialDate,'%Y%m') <= ifNull(Date_Format(". $todate.",'%Y%m'),Date_Format(EndFinancialDate,'%Y%m')) ) ".
			"      )  ".
			" Order By DirYear ";
// echo $CompanyName;			
$CompName = $MainCompanyName[0];
$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  ";
					
	//Range Loop
	$lstRangeStart = 0;
	
	$allRanges = explode(',',$filterbydate);
			
$query = "	
		Select LEDGER_NUMBER,
			Ledger_name,

			round(Sum(Client_Total),3) SupTotal,
			if(abs(remaingUnallocated) > 10 ,round(Sum(Client_Total - remaingUnallocated),3),sum(remaingUnallocated)) remaingUnallocated,
			Sum(Total_due_after_Allocations) as Total_due_after_Allocations  ,

			Sum(notDue) as NotDue, Sum( ";
			if ($filterbydate != '')  {
				 
				for($i = 0 ; $i < count($allRanges) ; $i++){
					$query .= " RNG".$i." + ";
					$lstRangeStart = $allRanges[$i] +1;
					
					if($i == count($allRanges)-1){
						$query .= "  RNG".($i + 1)."   " ;
					}
				}
				
			}
			$query .= ") as RNG ";
$query .=	"
			 
		from
		(
		SELECT
			LEDGER_NUMBER,
			Ledger_name,

			round(Sum( ifnull(journalOriginalAmount_D,0) - IFNULL(journalAllocatedAmount_D,0) )  - Sum( IFNULL(journalOriginalAmount_C,0) - IFNULL(journalAllocatedAmount_C ,0)),2) AS Client_Total,
			round(Sum( journalOriginalAmount_C - journalAllocatedAmount_C ),2) AS remaingUnallocated,
			round(Sum( journalOriginalAmount_D - journalAllocatedAmount_D ),2) AS Total_due_after_Allocations  ";
			if ($filterbydate != '')  {
				$query .=	", ";
				$lstRangeStart = 0;
				$query .= " round(if( OVDUEBY <= ".$lstRangeStart." , Sum( journalOriginalAmount_D - journalAllocatedAmount_D ) , 0 ),2) as notDue , ";
				for($i = 0 ; $i < count($allRanges) ; $i++){
					$query .= " round(if( OVDUEBY > $lstRangeStart AND OVDUEBY <= ".$allRanges[$i]." , Sum( journalOriginalAmount_D - journalAllocatedAmount_D ) , 0 ),2) as RNG".$i.", ";
					$lstRangeStart = $allRanges[$i] +1;
					
					if($i == count($allRanges)-1){
						$query .= " round(if( OVDUEBY >= $lstRangeStart  , Sum( journalOriginalAmount_D - journalAllocatedAmount_D ) , 0 ),2) as RNG". ($i + 1) . "  ";
					}
				} 
			} 
$query .= "FROM
			(
			SELECT
			clients.LEDGER_NUMBER,
			clients.Ledger_name,
		IF ( journaldt.journaldt_dbcd = 'D', journaldt.journal_id, '-' ) AS journalID_D,
		IF ( journaldt.journaldt_dbcd = 'C', journaldt.journal_id, '-' ) AS journalID_C,
		IF ( journaldt.journaldt_dbcd = 'D', journaldt.journaldt_desc, '-' ) AS journaldesc_D,
		IF ( journaldt.journaldt_dbcd = 'C', journaldt.journaldt_desc, '-' ) AS journalIdesc_C,
		IF ( journaldt.journaldt_dbcd = 'D', journal.JOURNAL_TYPE, '-' ) AS journalType_D,
		IF ( journaldt.journaldt_dbcd = 'C', journal.JOURNAL_TYPE, '-' ) AS journalType_C,
		IF ( journaldt.journaldt_dbcd = 'D', date_format( journaldt.Journal_Date, '%d/%m/%Y' ), '-' ) AS journalDate_D,
		IF ( journaldt.journaldt_dbcd = 'C', date_format( journaldt.Journal_Date, '%d/%m/%Y' ), '-' ) AS journalDate_C,
		IF ( journaldt.journaldt_dbcd = 'D', date_format( journaldt.Journaldt_ValueDate, '%d/%m/%Y' ), '-' ) AS journalValueDate_D,
		IF ( journaldt.journaldt_dbcd = 'C', date_format( journaldt.Journaldt_ValueDate, '%d/%m/%Y' ), '-' ) AS journalValueDate_C,
		IF ( journaldt.journaldt_dbcd = 'D', journaldt.Currency_code, '-' ) AS journalCurr_D,
		IF ( journaldt.journaldt_dbcd = 'C', journaldt.Currency_code, '-' ) AS journalCurr_C,
		IF (
				journaldt.journaldt_dbcd = 'D',
			IF (
					clients.Currency_Code = 'GHC',
					ifnull(journaldt.Journaldt_amount1,0),
				IF (
						clients.Currency_Code = 'USD',
						journaldt.Journaldt_amount2,
						convertcurFunc ( journaldt.Currency_code, clients.Currency_Code, now( ), 0, 0, 'GHC', 'USD', ifnull(journaldt.Journaldt_amount,0) ) 
					) 
				),
				'-' 
			) AS journalOriginalAmount_D,
		IF (
				journaldt.journaldt_dbcd = 'C',
			IF (
					clients.Currency_Code = 'GHC',
					ifnull(journaldt.Journaldt_amount1,0),
				IF (
						clients.Currency_Code = 'USD',
						ifnull(journaldt.Journaldt_amount2,0),
						convertcurFunc ( journaldt.Currency_code, clients.Currency_Code, now( ), 0, 0, 'GHC', 'USD', ifnull(journaldt.Journaldt_amount,0) ) 
					) 
				),
				'-' 
			) AS journalOriginalAmount_C,
		IF (
				journaldt.journaldt_dbcd = 'C',
			IF (
					clients.Currency_Code = 'GHC',
					ifnull( ALLCOnC.AllocatedAmount1, 0 ),
				IF (
						clients.Currency_Code = 'USD',
						ifnull( ALLCOnC.AllocatedAmount2, 0 ),
						convertcurFunc (
							ALLCOnC.currency_code,
							clients.Currency_Code,
							now( ),
							0,
							0,
							'GHC',
							'USD',
							ifnull( ALLCOnC.AllocatedAmount, 0 ) 
						) 
					) 
				),
				'-' 
			) AS journalAllocatedAmount_C,
		IF (
				journaldt.journaldt_dbcd = 'D',
			IF (
					clients.Currency_Code = 'GHC',
					ifnull( ALLCOnD.AllocatedAmount1, 0 ),
				IF (
						clients.Currency_Code = 'USD',
						ifnull( ALLCOnD.AllocatedAmount2, 0 ),
						convertcurFunc (
							ALLCOnD.currency_code,
							clients.Currency_Code,
							now( ),
							0,
							0,
							'GHC',
							'USD',
							ifnull( ALLCOnD.AllocatedAmount, 0 ) 
						) 
					) 
				),
				'-' 
			) AS journalAllocatedAmount_D,
		IF ( journaldt.journaldt_dbcd = 'D', ALLC.journal_idC, '-' ) AS AllocatedJournal_id,
		IF (
				journaldt.journaldt_dbcd = 'D',
				concat(
					ifnull( trans1.trans_reference, '' ),
					ifnull( Delivery1.Delivery_Reference, '' ),
					ifnull( Sale1.Sale_Reference, '' ),
					ifnull( purchase1.purchase_Reference, '' ),
				IF ( journal.JOURNAL_TYPE IN ( 'JV', 'EX', 'OP' ), ifnull( journal.Journal_refgovernment, '' ), '' ) 
				),
				'-' 
			) AS Debit_Ref,
		IF (
				journaldt.journaldt_dbcd = 'C',
				concat(
					ifnull( trans1.trans_reference, '' ),
					ifnull( Delivery1.Delivery_Reference, '' ),
					ifnull( Sale1.Sale_Reference, '' ),
					ifnull( purchase1.purchase_Reference, '' ),
				IF ( journal.JOURNAL_TYPE IN ( 'JV', 'EX', 'OP' ), ifnull( journal.Journal_refgovernment, '' ), '' ) 
				),
				'-' 
			) AS Credit_Ref,
		IF (
				journaldt.journaldt_dbcd = 'D',
				concat(
					ifnull( trans2.trans_reference, '' ),
					ifnull( Delivery2.Delivery_Reference, '' ),
					ifnull( Sale2.Sale_Reference, '' ),
					ifnull( purchase2.purchase_Reference, '' ),
				IF ( ALLCateJV.JOURNAL_TYPE IN ( 'JV', 'EX', 'OP' ), ifnull( ALLCateJV.Journal_refgovernment, '' ), '' ) 
				),
				'-' 
			) AS Alloc_Ref,
		IF (
				journaldt.journaldt_dbcd = 'D',
				concat(
					ifnull( date_format( trans2.trans_date, '%d/%m/%Y' ), '' ),
					ifnull( date_format( Delivery2.Delivery_date, '%d/%m/%Y' ), '' ),
					ifnull( date_format( Sale2.Sale_date, '%d/%m/%Y' ), '' ),
					ifnull( date_format( purchase2.purchase_date, '%d/%m/%Y' ), '' ),
				IF (
						ALLCateJV.JOURNAL_TYPE IN ( 'JV', 'EX', 'OP' ),
						ifnull( date_format( ALLCateJV.Journal_date, '%d/%m/%Y' ), '' ),
						'' 
					) 
				),
				'-' 
			) AS AllocatedJounal_Date,
		IF (
				journaldt.journaldt_dbcd = 'D',
				concat(
					ifnull( trans2.trans_Type, '' ),
					ifnull( Delivery2.journal_Type, '' ),
					ifnull( Sale2.journal_Type, '' ),
					ifnull( purchase2.journal_Type, '' ),
				IF ( ALLCateJV.JOURNAL_TYPE IN ( 'JV', 'EX', 'OP' ), ifnull( ALLCateJV.Journal_Type, '' ), '' ) 
				),
				'-' 
			) AS AllocatedJounal_Type,
		IF ( journaldt.journaldt_dbcd = 'D', concat( ifnull( ALLC.allocRemark, '' ) ), '-' ) AS AllocatedJournal_Remark,
		IF ( journaldt.journaldt_dbcd = 'D', ALLC.AllocLink_id, '' ) AS LinkID,
		IF (
				clients.Currency_Code = 'GHC',
				ifnull(ALLC.amountbase1,0),
			IF (
					clients.Currency_Code = 'USD',
					ifnull(ALLC.amountbase2,0),
					convertcurFunc ( ALLC.Currency_code, clients.Currency_Code, now( ), 0, 0, 'GHC', 'USD', ifnull(ALLC.amount,0) ) 
				) 
			) AS allocatedOnDAmount,
			if(journaldt.Journaldt_dbcd = 'D' , DATEDIFF(journaldt.journaldt_valuedate,$agingdate),0) as OVDUEBY
		FROM
			journaldt
			INNER JOIN journal ON journaldt.journal_id = journal.journal_id
			INNER JOIN clients ON journaldt.Ledger_number = clients.Ledger_number
			LEFT JOIN " . $_SESSION['dbLabel']. ".alloclink ALLC ON journaldt.journal_id = ALLC.journal_idD AND journaldt.Ledger_number = ALLC.Ledger_number
			LEFT JOIN journaldt ALLCatedDT ON ALLC.journal_idC = ALLCatedDT.journal_id  AND ALLCatedDT.Ledger_number = journaldt.Ledger_number
			LEFT JOIN journal ALLCateJV ON ALLC.journal_idC = ALLCateJV.journal_id 
			LEFT JOIN trans trans1 ON journal.journal_id = trans1.journal_id
 			LEFT JOIN deliveries Delivery1 ON journal.journal_id = Delivery1.journal_id 
 			LEFT JOIN sales Sale1 ON journal.journal_id = Sale1.journal_id 
 			LEFT JOIN purchases purchase1 ON journal.journal_id = purchase1.journal_id 
 			LEFT JOIN trans trans2 ON ALLCateJV.journal_id = trans2.journal_id 
 			LEFT JOIN deliveries Delivery2 ON ALLCateJV.journal_id = Delivery2.journal_id 
 			LEFT JOIN sales Sale2 ON ALLCateJV.journal_id = Sale2.journal_id 
 			LEFT JOIN purchases purchase2 ON ALLCateJV.journal_id = purchase2.journal_id
 			LEFT JOIN (
			SELECT
				journal_idC,
				ledger_number,
				currency_code,
				ifnull( sum( amount ), 0 ) AllocatedAmount,
				ifnull( sum( amountbase1 ), 0 ) AllocatedAmount1,
				ifnull( sum( amountbase2 ), 0 ) AllocatedAmount2 
			FROM
				" . $_SESSION['dbLabel']. ".alloclink 
			WHERE
				journal_idC IS NOT NULL 
				AND upper( AllocComp ) = upper( '$CompName' )
			GROUP BY
				journal_idC,
				ledger_number
			) ALLCOnC ON journaldt.journal_id = ALLCOnC.journal_idC AND journaldt.ledger_number = ALLCOnC.ledger_number
			LEFT JOIN (
			SELECT
				journal_idD,
				ledger_number,
				currency_code,
				ifnull( sum( amount ), 0 ) AllocatedAmount,
				ifnull( sum( amountbase1 ), 0 ) AllocatedAmount1,
				ifnull( sum( amountbase2 ), 0 ) AllocatedAmount2 
			FROM
				" . $_SESSION['dbLabel']. ".alloclink 
			WHERE
				journal_idD IS NOT NULL 
				AND upper( AllocComp ) = upper( '$CompName' ) 
			GROUP BY
				journal_idD,
				ledger_number 
			) ALLCOnD ON journaldt.journal_id = ALLCOnD.journal_idD 	AND journaldt.ledger_number = ALLCOnD.ledger_number 
		WHERE
			journaldt.Ledger_number between $fromledger and  $toledger
		
		And journal.journal_belongstogroup >= $FI_BelongsToGrp

		group by ledger_number, journalID_D


		HAVING
		IF ( ifnull(journalOriginalAmount_D,0) = '-', ifnull(journalOriginalAmount_C,0) - ifnull(journalAllocatedAmount_C,0) >= 0.001, 1 = 1 )
		AND
		IF ( journalOriginalAmount_D != '-', journalOriginalAmount_D >= 0.001, 1 = 1 )
		ORDER BY
			ledger_number,journaldt.journal_date ASC
			
		)Q
			group by Ledger_number,OVDUEBY

		)BulkQuery
		group by ledger_number";
// var_dump($ResultDir);
 
   // echo "<br>".$query."<br>";
 // Exit;
 
 //initializing colors for rows
 $color1 = "style='background-color: #FFFFFF!important;'";
 $color2 = "style='background-color: #d3d3d3'";
 $colorSwitcherIndex = 1; 
$result = mysql_query($query,$connection) or die(mysql_error());

$CumBalsN = 0;
$CumBalsP = 0;
$totalSupInvs = 0;
$totalSupAdjusted = 0;
$RemaingAmounts = 0;
$InvSettlements =  Array();
$InvTotalsSettlements =  Array();
$totalExtraAmounts = 0;
$totalBillAmounts = 0;
$totalAdjustedAmounts = 0;
$totalRemainingAmounts = 0;
echo "<table  border='0' width='100%'  align='center' cellpadding='0' cellspacing='0'>";
 

// header def
$bright='border-right: 1px  solid black ;';
$brleft='border-left: 1px  solid black ;';
$brtop='border-top: 1px  solid black ;';
$brbottom='border-bottom: 1px  solid black ;';			
        
echo"<tr height='25' bgcolor='lightgray'>";	  
    echo"<th align='left' style='font-size: 11pt;font-weight: bold;$brleft;$brtop;$brbottom'>&nbsp;Ledger number</th> ";
    echo"<th align='left' style='font-size: 11pt;font-weight: bold;$brtop;$brbottom'>&nbsp;Client Name</th>";
    echo"<th align='left' style='font-size: 11pt;font-weight: bold;$brtop;$brbottom'>&nbsp;PT</th>";
    echo"<th align='left' style='font-size: 11pt;font-weight: bold;$brtop;$brbottom'>&nbsp;Curr.</th>";
    echo"<th align='center' style='font-size: 11pt;font-weight: bold;$brtop;$brbottom'>&nbsp;Statement Balances </th>";
    echo"<th align='center' style='font-size: 11pt;font-weight: bold;$brtop;$brbottom'>&nbsp;UnAllocated Amounts  </th> ";
    echo"<th align='center' style='font-size: 11pt;font-weight: bold;$brtop;$brbottom'>&nbsp;Due Amounts  </th>";
    echo"<th align='center' style='font-size: 11pt;font-weight: bold;$brtop;$brbottom'>&nbsp;Not Due  </th> ";
    echo"<th align='center' style='font-size: 11pt;font-weight: bold;$brtop;$brbottom'>&nbsp;Auto   </th> ";
    echo"<th align='center' style='font-size: 11pt;font-weight: bold;$brtop;$brbottom'>&nbsp;Manual   </th> ";
echo"</tr>"; 
    $i = 0;
    $CurrLedger = '';
    $countRes = 0;
while($rows=mysql_fetch_array($result)){
    
    $i++;
    $FR++;
    if($FR > 1)		{
        	
        $ct = 1;
    } 
    ($colorSwitcherIndex % 2 == 0 ) ? $color = $color2 : $color=$color1;
    extract($rows); 
     
        $stmntLink = "rep_statementofaccount.php?fromdate=$disp_fromdate1&todate=$disp_todate&fromledger=$LEDGER_NUMBER&toledger=$LEDGER_NUMBER&dateoption=1&transaction=3&ledgeroption=1&curr=1&currbase1=0&currbase2=0&cum=0&alltypes=0&exclude=0&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&parentprj=0&prtaging=0&prtheader=1&prtcstjour=0&prtwording=0&prtwnac=0&showman=0&varprtbreak=1&prtshowuser=0&reporttype=1&userlevel=All&grpsalesman=0";
	$supBalX = getClienttotBal($LEDGER_NUMBER, $connection, $base, $FI_BelongsToGrp);
	
	if( abs($supBalX) > 0.1 ){
		echo"<tr  $color height='25'>";  
        echo"<td align='left' style='font-size: 11pt;font-weight: bold; ' >";
		echo	"<a target='_blank' href='Rep_NewSupAhInv_DetClients.php?fromdate=$disp_fromdate&todate=$disp_todate&agingdate=$dispagingdate&fromledger=$LEDGER_NUMBER&toledger=$LEDGER_NUMBER&curr=$Base1&dateoption=2&filterbydate=$filterbydate'>$LEDGER_NUMBER</a>";
		echo" </td> "; 
        echo"<td align='left' style='font-size: 11pt;'>";
				echo	"<a target='_blank' href='$stmntLink'>$Ledger_name</a>";
		echo"</td>";
		
        echo"<td align='center' style='font-size: 11pt;font-weight: bold;'>&nbsp;".getClientPT($LEDGER_NUMBER, $connection)."</td>";
        echo"<td align='center' style='font-size: 11pt;font-weight: bold;'>&nbsp;".getClientCurr($LEDGER_NUMBER, $connection)."</td>";
        echo"<td align='right' style='font-size: 11pt;'>&nbsp;". format_currency(   $supBalX  , 2)."</td>"; 
        echo"<td align='center' style='font-size: 11pt;'>&nbsp;".format_currency($remaingUnallocated, $DecimalBase1)."</td>";  
        echo"<td align='center' style='font-size: 11pt;'>&nbsp;".format_currency($NotDue, $DecimalBase1)."</td>";  
        echo"<td align='center' style='font-size: 11pt;'>&nbsp;".format_currency($RNG, $DecimalBase1)."</td>";  
        echo"<td align='center' style='font-size: 11pt;'>&nbsp;<input type='button' onclick='AllocateSup(\"".$LEDGER_NUMBER."\")' value = 'Allocate'>&nbsp;<input type='button' onclick='UNAllocateSup(\"".$LEDGER_NUMBER."\")' value = 'Unallocated'></td>";  
        echo"<td align='center' style='font-size: 11pt;'>&nbsp;<input type='button' onclick='AllocateSupManual(\"".$LEDGER_NUMBER."\")' value = 'Allocate'></td>";  
        
        echo"</tr>";
		$colorSwitcherIndex++;
	}
	
}
echo "</table>";
 
 

function getClientPT($ledger_number, $connection) { 
    $queryName = "select Client_PaymentTerms from clients where ledger_number = $ledger_number ";
    $res = mysql_query($queryName,$connection);
    $name = mysql_fetch_row($res);
    return $name[0];
} 
function getClientCurr($ledger_number, $connection) { 
    $queryName = "select currency_code from clients where ledger_number = $ledger_number ";
    $res = mysql_query($queryName,$connection);
    $name = mysql_fetch_row($res);
    return $name[0];
} 
function getClienttotBal($ledger_number, $connection, $base, $FI_BelongsToGrp) {
	$SumC = 0; 
    $queryName = "select
					sum(
					if(journaldt.journaldt_dbcd = 'D',
					if(ledger.Currency_code =  JournalDt.CURRENCY_CODE ,
								round(Journaldt_amount,3),
								if(ledger.Currency_code = 'GHC', round(Journaldt_amount1,3) ,
									if(ledger.Currency_code = 'USD', round(journaldt_amount2,3),
									round(convertcurFunc(journaldt.Currency_code, ledger.currency_code , journaldt.journal_date, 0, 0, '', '',journaldt.Journaldt_amount),3)
									)
								)
							)
					 , -1* if(ledger.Currency_code =  JournalDt.CURRENCY_CODE ,
								round(Journaldt_amount,3),
								if(ledger.Currency_code = 'GHC', round(Journaldt_amount1 ,3),
									if(ledger.Currency_code = 'USD', round(journaldt_amount2,3),
									round(convertcurFunc(journaldt.Currency_code, ledger.currency_code , journaldt.journal_date, 0, 0, '', '', journaldt.Journaldt_amount),3)
									)
								)
							))
					)
					 from journaldt
					  left join ledger on journaldt.ledger_number = ledger.ledger_number
					 where
					 journaldt.ledger_number = '$ledger_number'
					and journaldt.journal_belongstogroup >= $FI_BelongsToGrp"; 
    $res = mysql_query($queryName,$connection);
    $SumC = mysql_fetch_row($res);
	$Balance = $SumC[0];
	 
	return round($Balance,2); 
} 

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)
	// <script language=javascript>
// if (typeof(window.print) != 'undefined')
// {
    // window.print();
// }
// </script>
print <<< HERE


</BODY>
<img id="loadingImg" src="img/Loading_icon.gif" style="display:none;">
<img id="loadingImg2" src="img/bkground.jpg" style="display:none;">
</HTML>
HERE;


function getClientName ($ledger_number, $connection) { 
    $queryName = "select ledger_name from ledger where Ledger_number = $ledger_number limit 1 ";
    $res = mysql_query($queryName,$connection);
    $name = mysql_fetch_row($res);
    return $name[0];
} 

?>