
<?php
session_start();
//header("Content-type: text/html; charset=".$_SESSION['encodingmode']);
if($_SESSION['dbName']==""){
header("location:../index.php");
}


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

$client_code = $_GET['client_code'];
$valuedate = $_GET['valuedate'];
$Option2 = $_GET['Option2'];
$currency=$_GET['currency'];
$Amnt=$_GET['Amnt'];
$Amnt1=$_GET['Amnt1'];
$Amnt2=$_GET['Amnt2'];


$base1="";
$base2="";
$querysetup ="select base1, base2, grid_color1, grid_color2, if(DefaultBase=1, base1, base2) as defaultcurr from setup ";
$resultsetup=mysql_query($querysetup,$connection) or die(mysql_error());
$rowsetup=mysql_fetch_row($resultsetup);{
$defaultcurr = $rowsetup[4];
$base1=$rowsetup[0];
$base2=$rowsetup[1];
}


print <<<HERE
<html>
<head>
<title>Aging of Invoicing Details</title>

</head>
<body>
HERE;
$query_name  = "select ledger_name as client_name from clients where ledger_number = '$client_code'";
$result_name = mysql_query($query_name, $connection); 
$row_name    = mysql_fetch_array($result_name);
extract($row_name);
echo "<table style='width: 1050px; border-collapse: collapse'>
<tr>
<th colspan=7 style='font-size: 22px; padding: 12px; border-bottom: solid 2px #000000; font-family: Arial;'>Aging of Invoicing Details</th>
</tr>
<tr>
<th colspan=7 style='height: 15px'></th>
</tr>
<tr>
<td style='font-size: 14px; font-weight: bold; font-family: Arial'>Client</td>
<td style='font-size: 14px; font-family: Arial' colspan=2>$client_code - $client_name</td>


<td style='font-size: 14px; font-weight: bold; font-family: Arial'>Value Date</td>
<td style='font-size: 14px; font-family: Arial' colspan=2>";
$valuedate2 = explode(" ",$valuedate);
$valuedate2 = explode("-",$valuedate2[0]);
$valuedate2 = $valuedate2[2]."/".$valuedate2[1]."/".$valuedate2[0];
echo $valuedate2;
$curr="";
$amount="";
$condition="";
if($Amnt==1) {$amount="journaldt_amount"; $condition=" and journaldt.Currency_code='$currency'";}else
if($Amnt1==1){$amount="journaldt_amount1"; $curr=$base1;}else
if($Amnt2==1) {$amount="journaldt_amount2"; $curr=$base2;}
//echo $amount;
echo"</td>

</tr>
<tr>
<th colspan=6 style='height: 15px'></th>
</tr>
<tr>
 
<th width=100 style='border: solid 1px #000000; font-size: 14px; font-weight: bold; font-family: Arial'> Date</th>
<th width=100 style='border: solid 1px #000000; font-size: 14px; font-weight: bold; font-family: Arial'>Value Date</th>
<th width=75 style='border: solid 1px #000000; font-size: 14px; font-weight: bold; font-family: Arial'>Due Days</th>
<th width=250 style='border: solid 1px #000000; font-size: 14px; font-weight: bold; font-family: Arial'>Description</th>

<th width=100 style='border: solid 1px #000000; font-size: 14px; font-weight: bold; font-family: Arial'>Inv. Amout</th>
<th width=100 style='border: solid 1px #000000; font-size: 14px; font-weight: bold; font-family: Arial'>Deferred Payment</th>
<th width=100 style='border: solid 1px #000000; font-size: 14px; font-weight: bold; font-family: Arial'>Due Amount $currency</th>
<th width=100 style='border: solid 1px #000000; font-size: 14px; font-weight: bold; font-family: Arial'>Balance  $currency</th>
</tr>";

$query_aginginvoicingdt = "select date_format(journaldt_valuedate, '%d/%m/%Y') as valuedate2,
	  
      datediff('$valuedate 23:59:59',journaldt_valuedate) 'Due_days',
	    date_format(journal_date, '%d/%m/%Y') as journal_date,
      Journaldt_desc,
      Amount 'Amount_of_Inv',
      if(balance<0,amount+balance,Amount) 'Due_Amount',
	  @TotalBal:=@TotalBal+if(balance<0,amount+balance,Amount) Balance, invoicing.Invoicing_Amount   
from(
select @TotalBal:=0,journaldt_valuedate,
Journaldt_desc,Amount,@balance:=round(@balance-Amount,2) Balance, journal_id,journal_date from (
select  @balance:=(select sum(round(if(journaldt_dbcd='D',1,-1)*$amount,2))  Amount
from journaldt where ledger_number='$client_code' $condition and journaldt_valuedate<='$valuedate 23:59:59')
    ,journaldt_valuedate,Journaldt_desc, journaldt.journal_id,round(if(journaldt_dbcd='D',1,-1)*$amount,2)  Amount,journaldt.journal_Date
from journaldt
inner join journal on journaldt.journal_id=journal.journal_id and journal_type in ('SI')
 where ledger_number='$client_code' $condition and journaldt_valuedate<='$valuedate 23:59:59'
order by journaldt_valuedate desc
) stat) stat
inner join invoicing on stat.journal_id = invoicing.journal_id
 where if(balance<0,amount+balance,Amount)>0";
//order by journaldt_valuedate";

/*$query_aginginvoicingdt = "select date_format(journaldt_valuedate, '%d/%m/%Y') as journaldt_valuedate,
      datediff('$valuedate 23:59:59',journaldt_valuedate) 'Due_days',
      Journaldt_desc,
      Amount 'Amount_of_Inv',
      if(balance<0,amount+balance,Amount) 'Due_Amount',
	  @TotalBal:=@TotalBal+if(balance<0,amount+balance,Amount) Balance   
from(
select @TotalBal:=0,journaldt_valuedate,
Journaldt_desc,Amount,@balance:=round(@balance-Amount,2) Balance from (
select  @balance:=(select sum(round(if(journaldt_dbcd='D',1,-1)*journaldt_amount2,2))  Amount
from journaldt where ledger_number='$client_code' and journaldt_valuedate<='$valuedate 23:59:59')
    ,journaldt_valuedate,Journaldt_desc,round(if(journaldt_dbcd='D',1,-1)*journaldt_amount2,2)  Amount
from journaldt
inner join journal on journaldt.journal_id=journal.journal_id and journal_type in ('SI')
 where ledger_number='$client_code' and journaldt_valuedate<='$valuedate 23:59:59'
order by journaldt_valuedate desc
) stat) stat where if(balance<0,amount+balance,Amount)>0
order by journaldt_valuedate";*/

//echo $query_aginginvoicingdt;
$result_aginginvoicingdt = mysql_query($query_aginginvoicingdt);
$number_aginginvoicingdt = mysql_num_rows($result_aginginvoicingdt);
$prvSalesmanCode = "";
$count = 0;
$sumDue = 0;
$sumBalance = 0;
$sumAmountInv = 0;
while($row_aginginvoicingdt = mysql_fetch_array($result_aginginvoicingdt)){
	extract($row_aginginvoicingdt);
	$count++;
	
		$border="border-bottom: dotted 1px #000000";

	if($number_aginginvoicing == $count){
		$border_bottom="border-bottom: solid 1px #000000";
	}
	echo "<tr>";
	echo "<td style='$border; font-size: 13px; font-family: Arial; border-left: solid 1px #000000; padding-top: 5px; padding-bottom: 5px; $border_bottom; text-align: center'>";
	echo $journal_date;
	
	echo"</td>";
	echo "<td style='$border; font-size: 13px; font-family: Arial; border-left: solid 1px #000000; padding-top: 5px; padding-bottom: 5px; $border_bottom; text-align: center'>";
	echo $valuedate2;
	
	echo"</td>
	<td style='$border; font-size: 13px; font-family: Arial; padding-top: 5px; padding-bottom: 5px; $border_bottom; text-align: center'>$Due_days</td>
	<td style='$border; font-size: 13px; font-family: Arial; padding-top: 5px; padding-bottom: 5px; $border_bottom'>$Journaldt_desc</td>
	
	<td style='$border; font-size: 13px; font-family: Arial; text-align: center; padding-top: 5px; padding-bottom: 5px; $border_bottom; text-align: right'>".number_format($Invoicing_Amount, 2)."</td>
	<td style='$border; font-size: 13px; font-family: Arial; text-align: center; padding-top: 5px; padding-bottom: 5px; $border_bottom; text-align: right'>".number_format($Amount_of_Inv, 2)."</td>
	<td style='$border; font-size: 13px; font-family: Arial; text-align: right; padding-top: 5px; padding-bottom: 5px; $border_bottom'; text-align: right>".number_format($Due_Amount, 2)."</td>
	<td style='$border; font-size: 13px; font-family: Arial; text-align: right; padding-top: 5px; padding-bottom: 5px; border-right: solid 1px #000000; $border_bottom'>".number_format($Balance, 2)."</td>
	</tr>";
	$sumDue = $sumDue + str_replace(",", "", $Due_Amount);
	$sumBalance = $sumBalance + str_replace(",", "", $Balance);
	$sumAmountInv = $sumAmountInv + str_replace(",", "", $Amount_of_Inv);
}
echo "<tr>
		<th colspan=5 style='font-family: Arial; font-size: 16px; border-left: solid 1px #000000; border-bottom: solid 1px #000000; border-top: solid 1px #000000; background: #d9d7d7'>Total</th>
		<th style='text-align: right; font-family: Arial; font-size: 16px; border-bottom: solid 1px #000000; background: #d9d7d7; border-top: solid 1px #000000;'>".number_format($sumAmountInv, 2)."</th>
		<th style='text-align: right; font-family: Arial; font-size: 16px; border-bottom: solid 1px #000000; background: #d9d7d7; border-top: solid 1px #000000;'>".number_format($sumDue, 2)."</th>
		<th style='text-align: right; border-right: solid 1px #000000; font-family: Arial; font-size: 16px; border-bottom: solid 1px #000000; background: #d9d7d7; border-top: solid 1px #000000;'></th>
		</tr>";
echo"</table>";
print <<<HERE
</body>
<script>
//window.print();
</script>
</html>
HERE;
?>