<?php

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



include("config.inc.php");
include("Functions_Trans.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 FA_InvoicesAllocation as loginaccess, 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,$connection) or die(mysql_error());
while($rows=mysql_fetch_array($resultlog)){
	extract($rows);
}

if ($loginaccess=='' or $loginaccess==0)
	header("location:menu.php");



if($exp ==1){
	$filename = "exp_".$ReportName."_". date('Ymd') . ".xls";
	header("Content-Disposition: attachment; filename=\"$filename\"");
	header("Content-Type: application/vnd.ms-excel");
} 

//Read From Setup Table							
$querysetup= " select DecimalBase, DecimalBase1, DecimalBase2, base1, base2, grid_color1, grid_color2, ifNull(Client_ValidateProject,0) As Client_ValidateProject, ".
             " Date_Format(Setup_VatStartDate,'%Y%m%d') As Setup_VatStartDate, ifNull(Setup_VatPercentage,0) As Setup_VatPercentage from setup ";
$resultsetup=mysql_query($querysetup,$connection) or die(mysql_error());
while($rowsetup=mysql_fetch_array($resultsetup)){
	extract($rowsetup);
} 



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

<style type="text/css">
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;
                       }
		 

</style>
</HEAD>

HERE;



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



 
$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 Supplier 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 ($displaycur == 'currency1'){
   $curr='null';  
   $displaycur = '';
}   
else {
   $displaycur = $curr;
   $curr="'".$curr."'";
}   
  	
$dispagingdate = $agingdate;
$supPT = getSuppliePT($fromledger, $connection);
$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: ". $fromledger ."<br>"; 
if ($fromledger != '')    $SelectedOptions .= "-> Ledger Name : ". getSupplierName($fromledger, $connection) ." <u> ( PT : $supPT )  </u> <br></a>"; 
if ($filterbydate != '')    $SelectedOptions .= "-> Days Filter: ". $filterbydate  ."<br>"; 

// modifying Date to start from 1-1-Y%m%d
$fromdate1 = date('01/01/Y');

if ($displaycur == $Base1) {
    $stmntLink = "rep_statementofaccount.php?fromdate=$fromdate1&todate=$todate&fromledger=$fromledger&toledger=$fromledger&dateoption=1&transaction=3&ledgeroption=1&curr=0&currbase1=1&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=$FI_BelongsToGrp&grpsalesman=0";
}else if ($displaycur == $Base2){
    $stmntLink = "rep_statementofaccount.php?fromdate=$fromdate1&todate=$todate&fromledger=$fromledger&toledger=$fromledger&dateoption=1&transaction=3&ledgeroption=1&curr=0&currbase1=0&currbase2=1&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=$FI_BelongsToGrp&grpsalesman=0";
}else{
    $stmntLink = "rep_statementofaccount.php?fromdate=$fromdate1&todate=$todate&fromledger=$fromledger&toledger=$fromledger&dateoption=1&transaction=3&ledgeroption=1&curr=10&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=$FI_BelongsToGrp&grpsalesman=0";
}
if($isdetailed != 1){
	$detailedLink = "Rep_NewSupAhInv_Det.php?fromdate=$fromdate&todate=$todate&fromledger=$fromledger&toledger=$fromledger&curr=$displaycur&dateoption=2&filterbydate=$filterbydate&isdetailed=1&agingdate=$dispagingdate";
	$exportLink = "Rep_NewSupAhInv_Det.php?fromdate=$fromdate&todate=$todate&fromledger=$fromledger&toledger=$fromledger&curr=$displaycur&dateoption=2&filterbydate=$filterbydate&isdetailed=1&exp=1&agingdate=$dispagingdate";
}
else{
	$detailedLink = "Rep_NewSupAhInv_Det.php?fromdate=$fromdate&todate=$todate&fromledger=$fromledger&toledger=$fromledger&curr=$displaycur&dateoption=2&filterbydate=$filterbydate&agingdate=$dispagingdate";
	$exportLink = "Rep_NewSupAhInv_Det.php?fromdate=$fromdate&todate=$todate&fromledger=$fromledger&toledger=$fromledger&curr=$displaycur&dateoption=2&filterbydate=$filterbydate&isdetailed=1&exp=1&agingdate=$dispagingdate";
}
 $SelectedOptions .= "<a target='_blank' href='$stmntLink' >&nbsp;View Statement</a>"; 
if($isdetailed != 1)
	$SelectedOptions .= "<br><a target='_blank' href='$detailedLink' >&nbsp;View Details</a>"; 
else{
	$SelectedOptions .= "<br><a href='$detailedLink' >&nbsp;Less Details</a>"; 
	$SelectedOptions .= "<br><a href='$exportLink' >&nbsp;Export Results</a>"; 
}
if (!empty($fromdate))  $fromdate = explodeword($fromdate);
if (!empty($todate))    $todate = explodeword($todate);  
if (!empty($agingdate)) $agingdate = explodeword($agingdate);  
   
$fromledger    = GetSQLValueString($fromledger, 'text');
$toledger      = GetSQLValueString($toledger, 'text');
$fromdate  	   = GetSQLValueString($fromdate, 'date');
$todate        = GetSQLValueString($todate, 'date');     
$agingdate     = GetSQLValueString($agingdate, 'date');     

$fromproject   = GetSQLValueString($fromproject, 'text');
$fromcst   	   = GetSQLValueString($fromcst, 'text');
 
if(empty($prtreturn))    $prtreturn = 0;
if(empty($prtclosedinv)) $prtclosedinv = 0;
if(empty($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;
  

$comp = explode('_' , $_SESSION['dbName']);
$AllocComp = $comp[0];
preg_match_all('!\d+!', $comp[1], $matches);
$AllocYear = $matches[0][0];



$ref = '';
$color1 = "style='Height: 20px;background-color: #FFFFFF'";	
$color2 = "style='Height: 20px;background-color: #d3d3d3'";







// --- jp Start table


$FR = 0;
	
	
	

$QueryAllocs = "
	
SELECT
suppliers.LEDGER_NUMBER,
suppliers.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,
round(
IF(journaldt.journaldt_dbcd = 'D',
	IF(
			suppliers.Currency_Code = 'GHC',
			journaldt.Journaldt_amount1,
		IF
			(
				suppliers.Currency_Code = 'USD',
				journaldt.Journaldt_amount2,
				convertcurFunc ( journaldt.Currency_code, suppliers.Currency_Code, now( ), 0, 0, 'GHC', 'USD', journaldt.Journaldt_amount ) 
			) 
		),
		'-' 
	),2) AS journalOriginalAmount_D,
round(IF(
		journaldt.journaldt_dbcd = 'C',
	IF
		(
			suppliers.Currency_Code = 'GHC',
			journaldt.Journaldt_amount1,
		IF
			(
				suppliers.Currency_Code = 'USD',
				journaldt.Journaldt_amount2,
				convertcurFunc ( journaldt.Currency_code, suppliers.Currency_Code, now( ), 0, 0, 'GHC', 'USD', journaldt.Journaldt_amount ) 
			) 
		),
		'-' 
	),2) AS journalOriginalAmount_C,
round(IF(
		journaldt.journaldt_dbcd = 'C',
	IF
		(
			suppliers.Currency_Code = 'GHC',
			ifnull(ALLCOnC.AllocatedAmount1,0),
		IF
			(
				suppliers.Currency_Code = 'USD',
				ifnull(ALLCOnC.AllocatedAmount2,0),
				convertcurFunc ( ALLCOnC.currency_code, suppliers.Currency_Code, now( ), 0, 0, 'GHC', 'USD', ifnull(ALLCOnC.AllocatedAmount,0) ) 
			) 
		),
		'-' 
	),2) AS journalAllocatedAmount_C,
round(IF(
		journaldt.journaldt_dbcd = 'D',
	IF
		(
			suppliers.Currency_Code = 'GHC',
			ifnull(ALLCOnD.AllocatedAmount1,0),
		IF
			(
				suppliers.Currency_Code = 'USD',
				ifnull(ALLCOnD.AllocatedAmount2,0),
				convertcurFunc ( ALLCOnD.currency_code, suppliers.Currency_Code, now( ), 0, 0, 'GHC', 'USD', ifnull(ALLCOnD.AllocatedAmount,0) ) 
			) 
		),
		'-' 
	),2) 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,
	ifnull(
		IF
	(
		suppliers.Currency_Code = 'GHC',
		ALLC.amountbase1,
	IF
		(
			suppliers.Currency_Code = 'USD',
			ALLC.amountbase2,
			convertcurFunc ( ALLC.Currency_code, suppliers.Currency_Code, now( ), 0, 0, 'GHC', 'USD', ALLC.amount ) 
		) 
	),0) AS allocatedOnDAmount,
    IF
        ( journaldt.journaldt_dbcd = 'C', DATEDIFF($agingdate, journaldt.Journaldt_ValueDate ), ' ' ) AS overDueBy,
	IF
		( journaldt.journaldt_dbcd = 'C', DATEDIFF( $agingdate , journaldt.Journaldt_ValueDate ), ' ' ) AS overDueByD,
	IF
		( journaldt.journaldt_dbcd = 'D', ALLCateJVDT.Journaldt_amount, '-' ) AS JournalCreditSideOriginalAmount,
	
	IF
	(
		journaldt.journaldt_dbcd = 'C',
		concat(
			ifnull( trans1.Trans_ManualNo, '' ),
			ifnull( Delivery1.Delivery_ManualNo, '' ), 
			ifnull( purchase1.purchase_invoiceno, '' ),
		IF
			( journal.JOURNAL_TYPE IN ( 'JV', 'EX', 'OP' ), ifnull( journal.manual_no, '' ), '' ) 
		),
		'-' 
	) AS ManualNo,
	

	IF
	(
		journaldt.journaldt_dbcd = 'D',
		concat(
			ifnull( trans2.Trans_ManualNo, '' ),
			ifnull( Delivery2.Delivery_ManualNo, '' ),
 			ifnull( purchase2.purchase_invoiceno, '' ),
		IF
			( ALLCateJV.JOURNAL_TYPE IN ( 'JV', 'EX', 'OP' ), ifnull( ALLCateJV.manual_no, '' ), '' )
		),
		'-' 
	) AS ManualNO2
	

FROM
	journaldt
	INNER JOIN journal ON journaldt.journal_id = journal.journal_id
	INNER JOIN suppliers ON journaldt.Ledger_number = suppliers.Ledger_number
	LEFT JOIN ".$_SESSION['dbLabel'].".alloclink ALLC ON journaldt.journal_id = ALLC.journal_idD and ALLC.Ledger_number = $fromledger 
	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 journaldt ALLCateJVDT ON ALLC.journal_idC = ALLCateJVDT.journal_id and ALLC.ledger_number = ALLCateJVDT.ledger_number

	LEFT JOIN trans trans1 ON journal.journal_id = trans1.journal_id 						and journal.JOURNAL_TYPE = trans1.trans_type
	LEFT JOIN deliveries Delivery1 ON journal.journal_id = Delivery1.journal_id 	and journal.JOURNAL_TYPE = Delivery1.JOURNAL_TYPE
	LEFT JOIN sales Sale1 ON journal.journal_id = Sale1.journal_id 						and journal.JOURNAL_TYPE = Sale1.JOURNAL_TYPE
	LEFT JOIN purchases purchase1 ON journal.journal_id = purchase1.journal_id 		and journal.JOURNAL_TYPE = purchase1.JOURNAL_TYPE
	
	LEFT JOIN trans trans2 ON ALLCateJV.journal_id = trans2.journal_id 				and   ALLCateJV.JOURNAL_TYPE = trans2.trans_type
	LEFT JOIN deliveries Delivery2 ON ALLCateJV.journal_id = Delivery2.journal_id 	and journal.JOURNAL_TYPE = Delivery2.JOURNAL_TYPE
	LEFT JOIN sales Sale2 ON ALLCateJV.journal_id = Sale2.journal_id 					and ALLCateJV.JOURNAL_TYPE = Sale2.JOURNAL_TYPE
	LEFT JOIN purchases purchase2 ON ALLCateJV.journal_id = purchase2.journal_id 	and ALLCateJV.JOURNAL_TYPE = purchase2.JOURNAL_TYPE
	
	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('$AllocComp')
		and Ledger_number = $fromledger 
	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('$AllocComp')
			and Ledger_number = $fromledger 

		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 = $fromledger 
	and journal.Journal_BelongsToGroup >= 5
	and journaldt.Journal_BelongsToGroup >= 5

 Having 
 if(journalOriginalAmount_D = '-' , journalOriginalAmount_C - journalAllocatedAmount_C > 0.01 , 1=1)
 AND if(journalOriginalAmount_D != '-' , journalOriginalAmount_D  > 0.01 , 1=1)



ORDER BY
	journaldt.journal_date,journaldt.journaldt_id ASC

	";
    
 
$ref = '';
$oldJDesc_D = '';
$color1 = "style='Height: 20px;background-color: #FFFFFF'";	
$color2 = "style='Height: 20px;background-color: #d3d3d3'";

echo '<table width="100%" cellpadding=0 cellspacing=0>';

echo"	<tr height='30'>";
echo"	</tr>";
echo"	<tr height='30'>"; 
if ($isdetailed ==1 ){
echo"		<th align='left'><span class='style13'>REF</span></th>"; 
echo"		<th align='left'><span class='style13'>Date</span></th>";
echo"		<th align='left'><span class='style13'>Type</span></th>";
echo"		<th align='right'><span class='style13'>Amount $fromledgercurr</span></th>";
}
echo"		<th align='center'><span> &nbsp;</span></th>";
echo"		<th align='center'><span> &nbsp;</span></th>";
echo"		<th align='left'><span class='style13'>REF</span></th>"; 
echo"		<th align='left'><span class='style13'>Date</span></th>";
echo"		<th align='left'><span class='style13'>Type</span></th>"; 
 
	echo"		<th align='right'><span class='style13'>Org. Amount $fromledgercurr</span></th>";
	echo"		<th align='right'><span class='style13'>Amount Allocated $fromledgercurr</span></th>";
	echo"		<th align='right'><span class='style13'>Rem from Inv. $fromledgercurr</span></th>";
 
echo"		<th align='right'><span class='style13'>Alloc. Balance $fromledgercurr</span></th>";   
echo"		<th align='center'><span class='style13'>Value Date</span></th>";   
echo"		<th align='right'><span class='style13'>Overdue By</span></th>";   
echo"	</tr>";

// echo "<br>".$QueryAllocs."<br>"; //exit;

$resultAlloc=mysql_query($QueryAllocs,$connection) or die(mysql_error());
$SuppTotal = 0; 
$SupDues = 0;
$SupUnDues = 0;
    while ($rowsAlloc=mysql_fetch_array($resultAlloc)) {
        
        if($isdetailed == 1 ) $FR++;
        extract($rowsAlloc); 
		if($ref == $journalID_D && $journalID_D != '-' && $oldJDesc_D != $journaldesc_D ){
			$colorSwitcherIndex++;
			$remainingAmount     = $journalOriginalAmount_D;
		}
        if ($ref != $journalID_D || $jourtype != $journalType_D || $ref == '-')  {
            if ($FR > 1) {
                $colorSwitcherIndex++;
            } 
            $remainingAmount     = $journalOriginalAmount_D;
        }
        
        // calculate Sup Overall Balance
         
			if($ref != $journalID_D && $journalID_D != '-' ){
				$SuppTotal  += round($journalOriginalAmount_D - $allocatedOnDAmount,2) ;
			}else if($ref == $journalID_D && $journalID_D != '-' && $oldJDesc_D != $journaldesc_D ){
				$SuppTotal  += round($journalOriginalAmount_D - $allocatedOnDAmount,2) ;
			}elseif($ref == $journalID_D && $journalID_D != '-'  && $oldJDesc_D == $journaldesc_D ){
				$SuppTotal -= round($allocatedOnDAmount ,2) ; 
			}else{
				$SuppTotal -= round(($journalOriginalAmount_C - $journalAllocatedAmount_C),2) ;
			}
		 


		if($isdetailed == 0)
        ($FR % 2 == 0) ? $color = $color2 : $color = $color1;
		else
		($colorSwitcherIndex % 2 == 0) ? $color = $color2 : $color = $color1;
        
        $PaidAmount    = round($allocatedOnDAmount,2); 
        $remainingAmount = round($remainingAmount -  $allocatedOnDAmount,2);

         
			if( $overDueByD  >= 0 ) $SupDues += round($journalOriginalAmount_C - $journalAllocatedAmount_C,3);
			if( $overDueByD  < 0 ) $SupUnDues += round($journalOriginalAmount_C - $journalAllocatedAmount_C,3);
        // -- end jp color switcher
        if($overDueByD != ' ' && $isdetailed == 0){
			$FR++;
			echo"<tr $color >";
				if($ref != $journalID_D && $journalID_D != '-'   ){
				
					echo "	<td align='left'><span>$Debit_Ref</span></td>";
					echo " <td align='left'>$journalDate_D</td>"; 
					echo "	<td align='left'><span>$journalType_D</span></td>"; 
					echo "	<td align='right'><span>".number_format(round($journalOriginalAmount_D ,2),$DecimalBase1)."</span></td>"; 
					
				} else {
					if ($isdetailed ==1 ){
						echo " <td align='left'>&nbsp;</td>";
						echo "	<td align='left'><span>&nbsp;</span></td>";
						echo "	<td align='left'><span>&nbsp;</span></td>"; 
						echo "	<td align='right'><span>&nbsp;</span></td>"; 
					}
				}

					//spacer
					echo " <td align='left'> &nbsp;</td>";

				   
						echo " <td align='left'> &nbsp;</td>";
					 
					//start of Credit Part  
					if($journalID_D != '-'){
						echo "	<td align='left'><span>$Alloc_Ref</span></td>";
						echo " <td align='left'>$AllocatedJounal_Date</td>"; 
						echo "	<td align='left'><span> $AllocatedJounal_Type  ( $ManualNo ) </span></td>";
						echo "	<td align='right'><span>".number_format(round($JournalCreditSideOriginalAmount,2),$DecimalBase1)." </span></td>";
						echo "	<td align='right'><span>".number_format(round($allocatedOnDAmount,2),$DecimalBase1)." </span></td>";
						echo "	<td align='right'><span>".number_format(round(($JournalCreditSideOriginalAmount - $journalOriginalAmount_C),2),$DecimalBase1)." </span></td>";
						echo "	<td align='right'><span>".number_format(round($remainingAmount,2),$DecimalBase2) ." </span></td>";
						echo "	<td align='right'><span> ".$overDueBy." </span></td>";

					}else{ // Case if allocated
						echo "	<td align='left'><span> $Credit_Ref </span></td>";
						echo "	<td align='left'><span> $journalDate_C </span></td>";
						echo "	<td align='left'><span> $journalType_C   ( $ManualNo ) </span></td>";
						 
							echo "	<td align='right'><span>".number_format(round($journalOriginalAmount_C ,2),$DecimalBase1)."  </span></td>";
							echo "	<td align='right'><span>".number_format(round($journalAllocatedAmount_C ,2),$DecimalBase1)."  </span></td>";
							echo "	<td align='right'><span>".number_format(round(($journalOriginalAmount_C - $journalAllocatedAmount_C) ,2),
							$DecimalBase1)."  </span></td>";
						 
						echo "	<td align='right'><span>".number_format(round($journalOriginalAmount_C - $journalAllocatedAmount_C,2),$DecimalBase1)."  </span></td>";
						
						echo "	<td align='center'><span> $journalValueDate_C </span></td>"; 
						echo "	<td align='right'><span><b>".$overDueByD."</b> </span></td>";
					} 
				echo"</tr>";
		}else if ($isdetailed ==1 ){
			
			echo"<tr $color >";
				if($ref != $journalID_D && $journalID_D != '-'  ){
					
						echo "	<td align='left'><span>$Debit_Ref</span></td>";
						echo " <td align='left'>$journalDate_D</td>"; 
						echo "	<td align='left'><span>$journalType_D</span></td>"; 
						echo "	<td align='right'><span>".number_format(round($journalOriginalAmount_D ,2),$DecimalBase1)."</span></td>"; 
					
				} else if($ref == $journalID_D && $journalID_D != '-' && $oldJDesc_D != $journaldesc_D ){
						
						echo "	<td align='left'><span>$Debit_Ref</span></td>";
						echo " <td align='left'>$journalDate_D</td>"; 
						echo "	<td align='left'><span>$journalType_D</span></td>"; 
						echo "	<td align='right'><span>".number_format(round($journalOriginalAmount_D ,2),$DecimalBase1)."</span></td>"; 
				}
				else {
					echo " <td align='left'>&nbsp;</td>";
					echo "	<td align='left'><span>&nbsp;</span></td>";
					echo "	<td align='left'><span>&nbsp;</span></td>"; 
					echo "	<td align='right'><span>&nbsp;</span></td>"; 
				}

					//spacer
					echo " <td align='left'> &nbsp;</td>";

				   
						echo " <td align='left'> &nbsp;</td>";
					 
					//start of Credit Part  
					if($journalID_D != '-'){
						// echo "<br>zzz- $oldJDesc_D != $journaldesc_D";
						echo "	<td align='left'><span>$Alloc_Ref</span></td>";
						echo " <td align='left'>$AllocatedJounal_Date</td>"; 
						if  ( $ManualNo != '-') $ManualNo1 = " ( $ManualNo )";
						else $ManualNo1 = '';
						if  ( $ManualNO2 != '-') $ManualNO2x = " ( $ManualNO2 )";
						else $ManualNO2x = '';
						echo "	<td align='left'><span> $AllocatedJounal_Type      $ManualNo1 $ManualNO2x     </span></td>";
						echo "	<td align='right'><span>".number_format(round($JournalCreditSideOriginalAmount,2),$DecimalBase1)." </span></td>";
						echo "	<td align='right'><span>".number_format(round($allocatedOnDAmount,2),$DecimalBase1)." </span></td>";
						echo "	<td align='right'><span>".number_format(round(($JournalCreditSideOriginalAmount - $allocatedOnDAmount) ,2),$DecimalBase1)." </span></td>";
						echo "	<td align='right'><span>".number_format(round($remainingAmount,2),$DecimalBase2) ." </span></td>";
						echo "	<td align='right'><span> ".$journalValueDate_C." </span></td>";
						echo "	<td align='right'><span> ".$overDueBy." </span></td>";

					}else{ // Case if allocated
						echo "	<td align='left'><span> $Credit_Ref </span></td>";
						echo "	<td align='left'><span> $journalDate_C </span></td>";
						echo "	<td align='left'><span> $journalType_C  ( $ManualNo ) </span></td>";
						IF($JournalCreditSideOriginalAmount=='-') $JournalCreditSideOriginalAmount = $journalOriginalAmount_C;
						echo "	<td align='right'><span>".number_format(round($JournalCreditSideOriginalAmount ,2),$DecimalBase1)." </span></td>";
						echo "	<td align='right'><span>".number_format(round($journalOriginalAmount_C ,2),$DecimalBase1)." </span></td>";
						echo "	<td align='right'><span>".number_format(round(($JournalCreditSideOriginalAmount - $journalOriginalAmount_C) ,2),$DecimalBase1)." </span></td>";
						echo "	<td align='right'><span>".number_format(round($journalOriginalAmount_C - $journalAllocatedAmount_C,2),$DecimalBase1)."  </span></td>";
						echo "	<td align='right'><span><b>".$journalValueDate_C."</b> </span></td>";
						echo "	<td align='right'><span><b>".$overDueByD."</b> </span></td>";
					} 
				echo"</tr>";
		}
            $ref = $journalID_D; 
			$oldJDesc_D = $journaldesc_D;
            $jourtype = $journalType_D; 
    }
	
	if ($isdetailed ==1 ){
		$colspan = 11;
	}else{
		$colspan = 7;
	}
    echo"<TR height='32px'>
	<td colspan='$colspan'align = 'right'><b>Supplier Total : </b></td>
	<td colspan='2'align = 'right'>" . number_format(round($SuppTotal,2),$DecimalBase1) . "
    </td></TR>";
	
	
	echo"<TR height='32px' >
	<td colspan='$colspan'align = 'right'><b> Supplier Dues : </b></td>
	<td colspan='2'align = 'right'><b><u>" . number_format(round($SupDues,2),$DecimalBase1) . "</u></b></td>
	</TR>";
	
	
	echo"<TR height='32px'>
	<td colspan='$colspan'align = 'right'><b>Supplier Not Dues : </b></td>
	<td colspan='2'align = 'right'> " . number_format(round($SupUnDues,2),$DecimalBase1) . "</td>
	</TR>";
echo '</table>';
// --- jp End  table





    
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";
      $theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "Null";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "Null";
      break;
  }
  return $theValue;
}

function explodeword($date)
{

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


function getSupplierName($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];
} 

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


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;
}
echo"  
</form>
 
</body>
</html>";


?>