<!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 Supplier 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");


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

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

$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='60%'><b>$CompanyName</b></td> 
  <td width='20%'><font size=1><b> Printed Date: </b> <br> <b>Time: </b>
  <td width='20%'><font size=1>
HERE;

echo $printdate[2]."/".$printdate[1]."/".$printdate[0]; 
echo "<br>".$dt[1]." ".$dt[2];


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

print <<<HERE
</font></td>
</tr>
<tr>
<td width='100%' align='center'><font size=4><b>Payment Ref. ($ref)</b></font></td>
</tr>
 
</table>


<table class="params" width="100%" border="0" align="center" >
<tr>
  <td width="100%" valign='top'>&nbsp;</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=15%>
	  <col width=15%>
	  <col width=15%>
	  <col width=15%>
	  <col width=15%>
	  <col width=15%> ";

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

$MainCompanyName=explode('_',$_SESSION['dbName']);   
			
 

 // $query= "
 // SELECT
 	// ledger_number, headerlink_Date, journal_type, amount,amountbase1,AmountBase2, Currency_Code, Description,Manual_No
 
// FROM
	// ".$_SESSION['dbLabel'].".headerlink 
// WHERE
	// form_id IN (
	// SELECT
		// form_id 
	// FROM
		// ".$_SESSION['dbLabel'].".detaillink 
	// WHERE
		// detaillink.relation_id IN (
		// SELECT
			// transdt_id 
		// FROM
			// transdt
			// INNER JOIN trans ON transdt.trans_id = trans.trans_id 
		// WHERE
			// Trans_Type = 'PV' 
			// AND Trans_Reference = '$ref' 
			// AND transdt_dbcd = 'D'
			// and Trans_BelongsToGroup >= 5
		// )
		// and upper(headerlink.company) =  Upper('".$MainCompanyName[0]."')
		// and year = '2020'
// )
// and upper(headerlink.company) =  Upper('".$MainCompanyName[0]."')
// and year = '2020'";

$query = "
select * from (
SELECT
	ledger_number,
	headerlink_Date,
	journal_type,
	amount,
	amountbase1,
	AmountBase2,
	Currency_Code,
	Description,
	Manual_No,
	form_id 
FROM
	". $_SESSION['dbLabel'].".headerlink 
WHERE
	form_id IN (
	SELECT
		form_id 
	FROM
		". $_SESSION['dbLabel'].".detaillink 
	WHERE
		detaillink.relation_id IN (
		SELECT
			transdt_id 
		FROM
			transdt
			INNER JOIN trans ON transdt.trans_id = trans.trans_id 
		WHERE
			Trans_Type = 'PV' 
			AND Trans_Reference = '$ref' 
			AND transdt_dbcd = 'D' 
			AND Trans_BelongsToGroup >= 5 
		) 
		AND upper( headerlink.company ) = Upper( '$MainCompanyName[0]' ) 
		AND YEAR = '2020' 
	) 
	AND upper( headerlink.company ) = Upper( '$MainCompanyName[0]' ) 
	AND YEAR = '2020'
	) Q 
	inner join 
(	SELECT
		* 
	FROM
		". $_SESSION['dbLabel'].".detaillink 
	WHERE
		detaillink.relation_id IN (
		SELECT
			transdt_id 
		FROM
			transdt
			INNER JOIN trans ON transdt.trans_id = trans.trans_id 
		WHERE
			Trans_Type = 'PV' 
			AND Trans_Reference = '$ref' 
			AND transdt_dbcd = 'D' 
			AND Trans_BelongsToGroup >= 5 
		) 
		AND upper( detaillink.company ) = Upper( '$MainCompanyName[0]' ) 
		AND YEAR = '2020') X	on Q.form_id=  X.form_id 
		
		";
 
// echo $query;//exit; 
$result = mysql_query($query,$connection) or die(mysql_error());

 
$colorSwitcherIndex = 1;
$FR = 0;
$currentSupp= '';

$ct = 0;

   
  GetSupTransInfoGetSupTransInfo($ref,'PV',$connection); 
 

echo"	 
		
		<th align='left'> &nbsp;Date</th>
		<th align='left'> &nbsp;Invoice #</th> 
		<th align='center'> &nbsp;Invoice Value </th> 
		<th align='center'> &nbsp;Amount Paid</th>
		<th align='center'> &nbsp;Currency</th>
		<th align='left'colspan = 3>Description</th>";	
echo"</tr>";
$i = 1;
while($rows=mysql_fetch_array($result)){
if( $i%2 == 0) $trStyle = "style='background-color: #d4d1d1;'";
else $trStyle = "";

	extract($rows);
	if($Manual_No== '') $Manual_No = '-';
    echo"<tr $trStyle >";	  
    echo"<td align='left'> &nbsp;". $rows[1] ."</td>";	 
	echo"<td align='left'> &nbsp;". $rows[8] ."</td> ";	 
	echo"<td align='center'> &nbsp;".format_currency(abs( $rows[3]),2)."</td>";	 
	echo"<td align='center'> &nbsp;".format_currency(abs( $rows[20]),2)."</td>";	 
	echo"<td align='center'> &nbsp;".  $rows[6] ."</td>";	 
	echo"<td align='left' colspan = 3>".  $rows[7] ."</td> ";
echo"</tr>";
$i++;
$GTSup += abs($rows[3]);
$GTAZ += abs($rows[20]);
}
echo"<tr height='25' style='background-color: #b7b7b7;' >";	  
    echo"<td align='right' colspan = 2> &nbsp; <span style='font-size:16px;'><b>Total :</b><span> </td>  
		<td align='center'> <b>&nbsp;".format_currency(abs( $GTSup ),2)."</b></td>   
		<td align='center'> <b>&nbsp;".format_currency(abs( $GTAZ ),2)."</b></td>  
		<td align='center' colspan =4> </td> ";
echo"</tr>";
echo "</table>";
  
function GetInvNo($invRef,$invType,$connection){
 $queryInv = "select purchase_InvoiceNo from purchases where purchase_reference = $invRef and journal_type = '$invType' limit 1"; 
$invRes = mysql_query($queryInv,$connection);
$invNovalue = mysql_fetch_array($invRes);
return $invNovalue[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;
}

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


function GetSupTransInfoGetSupTransInfo($invRef,$invType,$connection){
	if($invType == 'JV'){
		$queryInv = "select manual_no from journal where journal_refGovernment =   $invRef  limit 1"; 
		$invRes = mysql_query($queryInv,$connection);
		$invNovalue = mysql_fetch_array($invRes);	
	}else{
		// $queryInv = "select purchase_InvoiceNo from purchases where purchase_reference = $invRef and journal_type = '$invType' limit 1"; 
		  $queryInv = "SELECT
						ledger.ledger_number,
						ledger.ledger_name,
						TransDt_Description
					FROM
						transdt
						INNER JOIN trans ON transdt.trans_id = trans.trans_id 
						left join ledger on transdt.ledger_number = ledger.ledger_number
					WHERE
						Trans_Type = 'PV' 
						AND Trans_Reference = '$invRef' 
						AND transdt_dbcd = 'D' 
						AND Trans_BelongsToGroup >= 5
				 "; 
		$invRes = mysql_query($queryInv,$connection);
		$invNovalue = mysql_fetch_array($invRes);
		echo"<TR>";
			echo"<TD> <b>Ledger :</b> </TD>";
			echo"<TD colspan =2 > <b> " . $invNovalue [0]. "</b>  -  <b><span style='font-size:14px;'>" . $invNovalue [1]. "</span></b>  </TD>";
		echo"</TR>";
		echo"<TR>";
			echo"<TD> <b>Description : </b> </TD>";
			echo"<TD> <b>" . $invNovalue [2]. "</b>  </TD>";
		echo"</TR>";
			echo"<TR>";
			echo"<TD> &nbsp;  </TD>";
			echo"<TD> &nbsp;  </TD>";
		echo"</TR>";
	} 
}

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

</BODY>
</HTML>
HERE;
?>