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

$till_date = $_GET['till_date'];
$s="";
$exp = $_GET['exp'];

if($exp == 1){
	
$filename = "Transactions_Journal_listing.xls";
	header("Content-Disposition: attachment; filename=\"$filename\"");
	header("Content-Type: application/vnd.ms-excel");
}


$Option=$_GET['Option'];
//$Option2=$_GET['Option2'];
$Type=$_GET['Type']; 
$Folio=$_GET['Folio'];
$Amnt=$_GET['Amnt'];//echo $Amnt;
$border="border-bottom: dotted 1px #000000";
$count=0;
$fromdate= $_GET['fromdate']; 
$todate= $_GET['todate'];
$fromFolio= $_GET['fromfolio']; 
$toFolio= $_GET['tofolio']; 
$fromRef= $_GET['fromref']; 
$toRef= $_GET['toref']; 
$allTypes= $_GET['alltypes']; 
$jvtype= $_GET['jvtype']; 
$exclude= $_GET['exclude']; 
$costcent= $_GET['fromcostcent']; 



/*if($Option==1 )$s="Filtered by date from : ".$fromdate. " to ".$todate;
else if($Option==2 )$s="Filtered by Reference from : ".$fromRef. " to ".$toRef;
else if($Option==3 )$s="Filtered by Folio from : ".$fromFolio. " to ".$toFolio;*/
if($costcent!="") $s=$s." & Cost Center : ".$costcent;


$length=95;
$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];
}
$jvtypelen = 0;
	if (!empty($jvtype))
	   $jvtypelen = strlen($jvtype);
	   
	if (empty($jvtype))
	   $jvtype = 'null';  
   
$rowsCounter=0;
$curr="";
if($Option2=="default") $amount="journaldt_amount";
if($Option2=="base1"){$amount="journaldt_amount1"; $curr=$base1;}
if($Option2=="base2") {$amount="journaldt_amount2"; $curr=$base2;}



print <<<HERE
<html>
<head>
<title>Transaction Journal Listing</title>
<style>

@page {
    size: landscape;
    margin: 2mm; 
}
</style>
<style  media="print">
   .no-print { display: none; }
</style>
<script> 

function openDetails(client_code, valuedate ,currency){
	var str = "Rep_AgingOfInvoicingReportDetails.php?client_code="+client_code+"&valuedate="+valuedate+"&currency="+currency+"&Option2=$Option2";
	window.open(str);
}
</script>
</head>
<body>
HERE;
echo "<table style='width:99.5%; border-collapse: collapse'>
<tr>
<th colspan=90%% style='font-size: 18px; padding: 12px; border-bottom: solid 2px #000000; font-family: Arial;'>Transaction Journal Listing</th>
</tr>
<tr>
<th colspan=100% style='height: 15px'></th>
</tr>
<tr>
<td colspan=6 style='font-size: 11px; font-weight: bold; font-family: Arial'>$s</td>

";


$amount="";
$col=6;
$typeColumn="";
$folioColumn="";
$debit1Column="";
$credit1Column="";
$debit2Column="";
$credit2Column="";
//$CurrenryColumn="";
$CurrencyHeader="";
$SumDebit1=0;$SumCredit1=0;
$GrandSumDebit1=0;$GrandSumCredit1=0;
$typeC=0;$folio=0;$debit1=0;$debit2=0;$credit1=0;$credit2=0;
if ($Type==1)$typeC=1;
if ($Folio==1)$folio=1;
if ($Amnt1==1 ) {$debit1=1;$credit1=1;if ($Amnt2==0) $cond=" border-left: solid 1px #000000;";}
if ($Amnt2==1 ) {$debit2=1;$credit2=1;}

if ($fromref=='')
		$fromref = 'null';
	if ($toref=='')
		$toref = 'null';
	if ($fromfolio=='')
		$fromfolio = 'null';
	if ($tofolio=='')
		$tofolio = 'null';

if($typeC==1) {$typeColumn="<th  width=15px style='border: solid 1px #000000; font-size: 11px; font-weight: bold; font-family: Arial'>TP</th>";$col=$col+1;$length=$length-15;}
if($folio==1) {$folioColumn="<th  width=40px style='border: solid 1px #000000; font-size: 11px; font-weight: bold; font-family: Arial'>Folio</th>";$col=$col+1;$length=$length-15;}

if($debit1==1) {$debit1Column="<th width=85px style='border: solid 1px #000000; font-size: 11px; font-weight: bold; font-family: Arial'>Debit ".$base1."</th>";$debit1Total="<th style='text-align: right; font-family: Arial; font-size: 10px; border-bottom: solid 1px #000000; border-top: solid 1px #000000; background: #d9d7d7'>".number_format($SumDebit1, 2)."</th>";}

if($credit1==1) {$credit1Column="<th width=85px style='border: solid 1px #000000; font-size: 11px; font-weight: ".$cond." bold; font-family: Arial'>Credit ".$base1."</th>";}

if($debit2==1) {$debit2Column="<th width=85px style='border: solid 1px #000000; font-size: 11px; font-weight: bold; font-family: Arial'>Debit ".$base2."</th>";}

if($credit2==1) {$credit2Column="<th width=85px style='border: solid 1px #000000; border-right: solid 1px #000000; font-size: 11px; font-weight: bold; font-family: Arial'>Credit ".$base2."</th>";}

if($Option2=="default") {$amount="journaldt_amount";$currencyHeader="<th width=30 style='border: solid 1px #000000; font-size: 11px; font-weight: bold; font-family: Arial'>Currency</th>";$CurrenryColumn="<td style=' font-size: 10px; font-family: Arial; text-align: right; padding-top: 5px; padding-bottom: 5px; $border_bottom'>".number_format($Due, 2)."</td>";}

if($Option2=="base1") $amount="journaldt_amount1";
if($Option2=="base2") $amount="journaldt_amount2";
echo"</td>
</tr>
<tr>
<th colspan=6 style='height: 15px'></th>
</tr>
<tr>
<th width=55px style='border: solid 1px #000000; font-size: 11px; font-weight: bold; font-family: Arial'>Date</th>".$typeColumn."
<th width=40px style='border: solid 1px #000000; font-size: 11px; font-weight: bold; font-family: Arial'>REF</th>".$folioColumn."
<th width=55px style='border: solid 1px #000000; font-size: 11px; font-weight: bold; font-family: Arial'>Ledger No.</th>
<th width=450px style='border: solid 1px #000000; font-size: 11px; font-weight: bold; font-family: Arial'>Ledger Name / Description</th>
<th width=25px style='border: solid 1px #000000; font-size: 11px; font-weight: bold; font-family: Arial'>Curr</th>
<th width=70px style='border: solid 1px #000000; font-size: 11px; font-weight: bold; font-family: Arial'>Debit</th>
<th width=70px style='border: solid 1px #000000; font-size: 11px; font-weight: bold; font-family: Arial'>Credit</th>".$debit1Column .$credit1Column. $debit2Column .$credit2Column."

</tr>";
echo "</td>
</tr>
";
if($from_client_code != "" && $to_client_code != ""){
//

 "$from_client_name [$from_client_code] - $to_client_name [$to_client_code]";
}
if (!empty($fromdate))
   $fromdate = explodeword($fromdate);
if (!empty($todate))
   $todate = explodeword($todate);
//$till_date = explode("/", $till_date);
//$till_date = $till_date[2]."-".$till_date[1]."-".$till_date[0];
//<th width=110 style='border: solid 1px #000000; font-size: 11px; font-weight: bold; font-family: Arial'>Due Amount $curr</th>
//<th width=100 style='border: solid 1px #000000; font-size: 11px; font-weight: bold; font-family: Arial'>Balance $curr</th>
//echo $amount;
$query_aginginvoicing = "select DATE_FORMAT(journal.journal_date, '%d/%m/%Y') as date , journal.Journal_refgovernment as ref,
journal.Journal_folio as folio, journal.Journal_type as type, journaldt.ledger_number as id,ledger.ledger_name as name,
journaldt.Journaldt_desc as description, journaldt.Currency_code as currency,
(Case When journaldt_dbcd='D' Then journaldt_amount Else 0 End)as DebitAmnt,
(Case When journaldt_dbcd='C' Then journaldt_amount Else 0 End)as CreditAmnt,
(Case When journaldt_dbcd='D' Then journaldt_amount1 Else 0 End)as DebitAmnt1,
(Case When journaldt_dbcd='C' Then journaldt_amount1 Else 0 End)as CreditAmnt1,
(Case When journaldt_dbcd='D' Then journaldt_amount2 Else 0 End)as DebitAmnt2,
(Case When journaldt_dbcd='C' Then journaldt_amount2 Else 0 End)as CreditAmnt2
from journal inner join journaldt on journal.journal_id=journaldt.journal_id , ledger
where journaldt.ledger_number=ledger.ledger_number  ";

if($Option2 == "default"){
	//$query_aginginvoicing = $query_aginginvoicing." group by clients.ledger_number,journaldt.currency_code ";
}

if($Option==1 )$query_aginginvoicing = $query_aginginvoicing." and journal.journal_date between DATE_FORMAT('$fromdate','%Y/%m/%d') and DATE_FORMAT('$todate', '%Y/%m/%d') ";
else if($Option==2 )$query_aginginvoicing = $query_aginginvoicing." and journal.Journal_refgovernment between ifnull($fromref,0) and ifnull($toref,99999999999) ";
else if($Option==3 )$query_aginginvoicing = $query_aginginvoicing." and journal.Journal_folio between ifnull($fromfolio,0) and ifnull($tofolio,99999999999)  ";
if($costcent!="") $query_aginginvoicing = $query_aginginvoicing." and journal.Costcent_Code='$costcent'  ";

if($exclude==1) $query_aginginvoicing = $query_aginginvoicing." and (($jvtypelen > 0 and Journal.Journal_type not in ($jvtype)) or ($jvtypelen = 0 and 1 = 1)) ";
else if($allTypes==0 && $jvtype!="" ) $query_aginginvoicing = $query_aginginvoicing." and (($jvtypelen > 0 and Journal.Journal_type in ($jvtype)) or ($jvtypelen = 0 and 1 = 1)) ";

if($Option==1 )$query_aginginvoicing = $query_aginginvoicing." order by journal.journal_date,journal.journal_id ";
else if($Option==2 )$query_aginginvoicing = $query_aginginvoicing." order by journal.Journal_refgovernment,journal.journal_id  ";
else if($Option==3 )$query_aginginvoicing = $query_aginginvoicing." order by journal.Journal_folio,journal.journal_id  ";

//echo $Option; 
//echo $query_aginginvoicing;
$result_aginginvoicing = mysql_query($query_aginginvoicing);
$number_aginginvoicing = mysql_num_rows($result_aginginvoicing);
$prvSalesmanCode = "";
//echo $number_aginginvoicing;
$sumDue = 0;
$sumBalance = 0;
$flag=1;
while($row_aginginvoicing = mysql_fetch_array($result_aginginvoicing)){
	
	extract($row_aginginvoicing);
	$countt++;
	if($Option2=="base1") { $currency=$base1;}
if($Option2=="base2") { $currency=$base2;}
	if($count>17){
echo"</td>
</tr>
<tr>
<th colspan=100% style='font-size: 18px; padding: 12px; border-bottom: solid 2px #000000; font-family: Arial;'>Transaction Journal Listing</th>
</tr>
<tr>
<th colspan=100% style='height: 15px'></th>
</tr>
<tr>
<td colspan=6 style='font-size: 11px; font-weight: bold; font-family: Arial'>$s</td>

<tr>
<th colspan=6 style='height: 15px'></th>
</tr>
<tr>
<th width=55px style='border: solid 1px #000000; font-size: 11px; font-weight: bold; font-family: Arial'>Date</th>".$typeColumn."
<th width=40px style='border: solid 1px #000000; font-size: 11px; font-weight: bold; font-family: Arial'>REF</th>".$folioColumn."
<th width=55px style='border: solid 1px #000000; font-size: 11px; font-weight: bold; font-family: Arial'>Ledger No.</th>
<th width=450px style='border: solid 1px #000000; font-size: 11px; font-weight: bold; font-family: Arial'>Ledger Name / Description</th>
<th width=15px style='border: solid 1px #000000; font-size: 11px; font-weight: bold; font-family: Arial'>Curr</th>
<th width=70px style='border: solid 1px #000000; font-size: 11px; font-weight: bold; font-family: Arial'>Debit</th>
<th width=70px style='border: solid 1px #000000; font-size: 11px; font-weight: bold; font-family: Arial'>Credit</th>".$debit1Column .$credit1Column. $debit2Column .$credit2Column."

</tr>";
$count=0;
}
	$count++;
	if($ref==$ref1) {
		$r="";
		$borderr="";
	}
	else {
		 $r=$ref;
		 $borderr="border-top: dotted 1px #000000;";
		
	}
			
	
	if($debit1==1) {$debit1field="<td style=' font-size: 10px;$borderr text-align: right; font-family: Arial; padding-top: 5px; padding-bottom: 5px; $border_bottom'>".number_format($DebitAmnt1,2)."</td>";}

if($credit1==1) {$credit1field="<td style=' font-size: 10px;$borderr text-align: right;font-family: Arial; padding-top: 5px; padding-bottom: 5px; $border_bottom'>".number_format($CreditAmnt1,2)."</td>";}

if($debit2==1) {$debit2field="<td style=' font-size: 10px;$borderr text-align: right;font-family: Arial; padding-top: 5px;".$cond." padding-bottom: 5px; $border_bottom'>".number_format($DebitAmnt2,2)."</td>";}

if($credit2==1) {$credit2field="<td style=' font-size: 10px;$borderr text-align: right;font-family: Arial; border-right: solid 1px #000000; padding-top: 5px; padding-bottom: 5px; $border_bottom'>".number_format($CreditAmnt2,2)."</td>";}
if($Type==1) {$typeField="<td  style=' font-size: 10px;text-align: center;$borderr font-family: Arial; padding-top: 5px; padding-bottom: 5px; $border_bottom'>$type</td>";$TPSpacing="<th  style='font-family: Arial; font-size: 10px; border-bottom: solid 1px #000000 ; $borderr border-top: solid 1px #000000; background: #d9d7d7'>&nbsp;</th>";}
if($Folio==1) {$folioField="<td  style='$borderr font-size: 10px;text-align: left; font-family: Arial; padding-top: 5px; padding-bottom: 5px; $border_bottom'>$folio</td>";$folioSpacing="<th  style='font-family: Arial; font-size: 10px; border-bottom: solid 1px #000000 ; border-top: solid 1px #000000; background: #d9d7d7'>&nbsp;</th>";}


	if($salesmen_code == "") $salesmen_code = "UNKNOWN";
	if($salesman == "") $salesman = "UNKNOWN";
	if($orderby_salesman == 1 && $prvSalesmanCode!=$salesmen_code){
		//$border = "border-top: dotted 1px #000000";
		
		
		
		$prvSalesmanCode = $salesmen_code;
		$sumDue = 0;
		$sumBalance = 0;
	}
	else{
		$border="";
		$printSalesman = 0;
	}
	
	if($orderby_salesman == 0){
		$printSalesman = 1;
		$border="border-bottom: dotted 1px #000000";
	}
	if($number_aginginvoicing == $count){
		$border_bottom="border-bottom: solid 1px #000000";
	}
	$nextcurr="";
	if($Option2=="default") $nextcurr=$currency;
	if($Option2=="base1") $nextcurr=$base1;
	if($Option2=="base2") $nextcurr=$base2;
	$SumDebit1=$SumDebit1+$DebitAmnt1;
	$GrandSumDebit1=$GrandSumDebit1+$DebitAmnt1;
	
	$SumCredit1=$SumCredit1+$CreditAmnt1;
	$GrandSumCredit1=$GrandSumCredit1+$CreditAmnt1;
	
	$SumDebit2=$SumDebit2+$DebitAmnt2;
	$GrandSumDebit2=$GrandSumDebit2+$DebitAmnt2;

	$SumCredit2=$SumCredit2+$CreditAmnt2;
	$GrandSumCredit2=$GrandSumCredit2+$CreditAmnt2;
	
	$cond="if($Amnt1==0 && $Amnt2==0)echo border-right: solid 1px #000000;";
	
	
	echo "<tr  	>
	<td  style=' font-size: 10px; font-family:  Arial; $borderr text-align: center; padding-top: 5px; border-left: solid 1px #000000; padding-bottom: 5px; $border_bottom'>$date</td>".$typeField."
	<td style=' font-size: 10px; font-family: Arial; $borderr text-align: center; padding-top: 5px; padding-bottom: 5px; $border_bottom'>" .$r. "</td>".$folioField."
	
	<td style=' font-size: 10px; font-family: Arial; $borderr padding-top: 5px; padding-bottom: 5px; $border_bottom'>$id</td>
	<td style=' font-size: 10px; font-family: Arial; $borderr padding-top: 5px; padding-bottom: 5px; $border_bottom'>".substr_with_ellipsis($name,$length )." </br> ".substr_with_ellipsis($description,$length )."</td>

	
	<td style=' font-size: 10px; font-family: Arial; $borderr text-align: center; padding-top: 5px; padding-bottom: 5px; $border_bottom'>".$currency."</td>
	<td style=' font-size: 10px; font-family: Arial;$borderr  text-align: right; padding-top: 5px; padding-bottom: 5px; $border_bottom'>".number_format($DebitAmnt, 2)."</td>
	<td style=' font-size: 10px; font-family: Arial; $borderr text-align: right; padding-top: 5px; padding-bottom: 5px; ".$cond." $border_bottom'>".number_format($CreditAmnt, 2)."</td>".$debit1field ."".$credit1field."" .$debit2field ."".$credit2field ."
	</tr>";
	
	$ref1=$ref;
	
	
	
	$sumDue = $sumDue + str_replace(",", "", $DebitAmnt);
	$sumBalance = $sumBalance + str_replace(",", "", $CreditAmnt);
	
	if($count>17 || $countt==mysql_num_rows($result_aginginvoicing)) {
		
if($debit1==1) {$debit1Total="<th style='text-align: right; font-family: Arial; font-size: 11px; border-bottom: solid 1px #000000; border-top: solid 1px #000000; background: #d9d7d7'>".number_format($SumDebit1,2)."</th>";$Gdebit1Total="<th style='text-align: right; font-family: Arial; font-size: 11px; border-bottom: solid 1px #000000; border-top: solid 1px #000000; background: #d9d7d7'>".number_format($GrandSumDebit1,2)."</th>";}

if($credit1==1) {$credit1Total="<th style='text-align: right; font-family: Arial; font-size: 11px; border-bottom: solid 1px #000000; border-top: solid 1px #000000; background: #d9d7d7'>".number_format($SumCredit1,2)."</th>";$Gcredit1Total="<th style='text-align: right; font-family: Arial; font-size: 11px; border-bottom: solid 1px #000000; border-top: solid 1px #000000; background: #d9d7d7'>".number_format($GrandSumCredit1,2)."</th>";}

if($debit2==1) {$debit2Total="<th style='text-align: right; font-family: Arial; font-size: 11px; border-bottom: solid 1px #000000; border-top: solid 1px #000000; background: #d9d7d7'>".number_format($SumDebit2, 2)."</th>";$Gdebit2Total="<th style='text-align: right; font-family: Arial; font-size: 11px; border-bottom: solid 1px #000000; border-top: solid 1px #000000; background: #d9d7d7'>".number_format($GrandSumDebit2, 2)."</th>";}

if($credit2==1) {$credit2Total="<th style='text-align: right; font-family: Arial; font-size: 11px;border-right: solid 1px #000000; border-bottom: solid 1px #000000; border-top: solid 1px #000000; background: #d9d7d7'>".number_format($SumCredit2, 2)."</th>";$Gcredit2Total="<th style='text-align: right; font-family: Arial; font-size: 11px;border-right: solid 1px #000000; border-bottom: solid 1px #000000; border-top: solid 1px #000000; background: #d9d7d7'>".number_format($GrandSumCredit2, 2)."</th>";}
$ref1=$ref;
$col=$col+2;
if($Amnt1==0 && $Amnt2==0) $total="&nbsp;"; else $total="Sub Total";
if($Amnt1==0 && $Amnt2==0) $Gtotal="&nbsp;"; else $Gtotal="Grand Total";
echo "<tr>
		<th style='font-family: Arial; font-size: 11px; border-bottom: solid 1px #000000 ; border-left: solid 1px #000000; border-top: solid 1px #000000; background: #d9d7d7'>&nbsp;</th>".$TPSpacing."
		<th style='font-family: Arial; font-size: 11px; border-bottom: solid 1px #000000 ; border-top: solid 1px #000000; background: #d9d7d7'>&nbsp;</th>".$folioSpacing."
		
		<th colspan=5 style='font-family: Arial; font-size: 11px; border-top: solid 1px #000000; border-bottom: solid 1px #000000; background: #d9d7d7'>$total</th>$debit1Total $credit1Total $debit2Total $credit2Total
		</tr>";
		
echo "<tr>
		<th style='font-family: Arial; font-size: 11px; border-bottom: solid 1px #000000 ; border-left: solid 1px #000000; border-top: solid 1px #000000; background: #d9d7d7'>&nbsp;</th>".$TPSpacing."
		<th style='font-family: Arial; font-size: 11px; border-bottom: solid 1px #000000 ; border-top: solid 1px #000000; background: #d9d7d7'>&nbsp;</th>".$folioSpacing."
		
		<th colspan=5 style='font-family: Arial; font-size: 11px;  border-bottom: solid 1px #000000; background: #d9d7d7'>$Gtotal</th>$Gdebit1Total $Gcredit1Total $Gdebit2Total $Gcredit2Total
		</tr>
		<tr><td colspan=100%>&nbsp;</td></tr>";	
$SumDebit1=0;		$SumDebit2=0;$SumCredit1=0;$SumCredit2=0;
		
	}
}

		
echo"</table>";


function substr_with_ellipsis($string, $chars )
{
    preg_match('/^.{0,' . $chars. '}(?:.*?)\b/iu', $string, $matches);
    $new_string = $matches[0];
    return ($new_string === $string) ? $string : $new_string . '&hellip;';
}


function explodeword($date)
{

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




print <<<HERE
</body>
<script>
//window.print();
</script>
</html>
HERE;
?>