<?php
session_start();
if($HTTP_SESSION_VARS['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($HTTP_SESSION_VARS['dbName'],$connection) or die("Could not find DB");
//mysql_query("SET NAMES 'utf8'"); 
	//mysql_query('SET CHARACTER SET utf8'); 
	//mysql_set_charset('utf8', $connection);
$fromDate  = $_GET['fromDate'];
$toDate    = $_GET['toDate'];
$date_type = $_GET['date_type'];
$order_By  = $_GET['order_By'];
$banks     = $_GET['banks'];
$activity =$_GET['act'];
$collected =$_GET['coll'];
$uncollected =$_GET['uncoll'];
$fromLedger=$_GET['fromLedger'];
$toLedger=$_GET['toLedger'];
$OrderSalesMan=$_GET['OrderSalesMan'];
$Salesmen=$_GET['salesmen'];
//header('Content-Type: text/html; charset=utf-8');
if($date_type == "date") $date_label = "Date";
if($date_type == "chk_date") $date_label = "Check Date";

if($order_By == "date") $order_by_labels = "Order By $date_label";
if($order_By == "bank_and_date") $order_by_labels = "Order By Bank & $date_label";
print <<<HERE
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1256" />

<title>List of Checks</title>

</head>
HERE;

$query_setup  = "select COMPANY_LOGO as CompanyLogo, COMPANY_NAME as CompanyName, Company_memo as CompanyAddress from setup";
$result_setup = mysql_query($query_setup, $connection); 
$row_setup    = mysql_fetch_array($result_setup);
extract($row_setup);

$curr_date = date("d/m/Y H:i:s");
echo "<table style='width: 1000px; border-collapse: collapse;'>
<tr>
<td width=270 style='vertical-align: top;'><img src='LogoFile.php' width=200 /></td>
<td width=580><span style='font-size: 20px; font-weight: bold; font-family: Arial'><i>$CompanyName</i></span><br>
<pre><span style='font-size: 14px;'><i>$CompanyAddress</i></span></pre></td>
<td width=370 style='vertical-align: top;'>
<table width=100% style='border-collapse: collapse'>
<tr>
<td width=50% style='font-family: Arial; font-size: 14px; font-weight: bold; border: solid 1px #000000; padding: 5px;'>Print Date & Time</td>
<td width=50% style='font-family: Arial; font-size: 14px; border: solid 1px #000000; padding: 5px;'>$curr_date</td>
</tr>
<tr>
<td width=50% style='font-family: Arial; font-size: 14px; font-weight: bold; border: solid 1px #000000; padding: 5px;'>User</td>
<td width=50% style='font-family: Arial; font-size: 14px; border: solid 1px #000000; padding: 5px;'>".$_SESSION['useraccount']."</td>
</tr>
</table>
</td>
</tr>
</table>
<table style='width: 1000px; border-collapse: collapse;'>
<tr style='height: 15px'>
<th></th>
</tr>
<tr>
<th style='border-bottom: solid 1.5px #000000; font-size: 20px; font-weight: bold; font-family: Arial; padding-bottom: 5px'>List of Checks</th>
</tr>
</table>
<table style='width: 1000px; border-collapse: collapse;'>
<tr style='height: 10px'>
<th></th>
</tr>
<tr>
<td style='font-family: Arial; font-size: 14px; font-weight: bold' colspan='2'>Duration (By $date_label): $fromDate - $toDate</td>
<td style='font-family: Arial; font-size: 14px; font-weight: bold'>Bank Name</td>
<td style='font-family: Arial; font-size: 14px; '>$banks</td>
</tr>
<tr>
<td style='font-family: Arial; font-size: 14px; font-weight: bold'>$order_by_labels</td>
<td style='font-family: Arial; font-size: 14px; '></td>
</tr>
</table>
<table style='width: 1000px; border-collapse: collapse;'>
<tr style='height: 25px'>
<th></th>
</tr>
</table>
<table style='width: 1000px; border-collapse: collapse;'>
<tr>
<th width=120 style='font-size: 12px; border-bottom: solid 1.5px #000000; border-top: solid 1.5px #000000;  font-family: Arial; padding: 3px;'>Date</th>
<th width=50 style='font-size: 12px; border-bottom: solid 1.5px #000000; border-top: solid 1.5px #000000; font-family: Arial; padding: 3px; text-align: left;'>Check No.</th>
<th width=100 style='font-size: 12px; border-bottom: solid 1.5px #000000; border-top: solid 1.5px #000000; font-family: Arial; padding: 3px;'>Bank Name</th>
<th width=525 style='font-size: 12px; border-bottom: solid 1.5px #000000; border-top: solid 1.5px #000000; font-family: Arial; padding: 3px;'>Account</th>
<th width=50 style='font-size: 12px; border-bottom: solid 1.5px #000000; border-top: solid 1.5px #000000; font-family: Arial; padding: 3px;'>Currency</th>
<th width=110 style='font-size: 12px; border-bottom: solid 1.5px #000000; border-top: solid 1.5px #000000; font-family: Arial; padding: 3px; text-align: right'>Amount</th>
<th width=110 style='font-size: 12px; border-bottom: solid 1.5px #000000; border-top: solid 1.5px #000000; font-family: Arial; padding: 3px; text-align: right'>Collect Date</th>
<th width=110 style='font-size: 12px; border-bottom: solid 1.5px #000000; border-top: solid 1.5px #000000; font-family: Arial; padding: 3px; text-align: right'>Trans Reference</th>
</tr>";
$fromDate = explode("/", $fromDate);
$fromDate = $fromDate[2]."-".$fromDate[1]."-".$fromDate[0];

$toDate = explode("/", $toDate);
$toDate = $toDate[2]."-".$toDate[1]."-".$toDate[0];
/*$query_chks = "select  DATE_FORMAT(transdt.Trans_date, '%d/%m/%Y') as NormalDate, chkjourdt.Chk_ValueDate as ValueDate, chkjourdt.Chk_bank as BankName, chkjourdt.Chk_number as ChkNo, Trans.Trans_Representative as Beneficiary, transdt.Ledger_Number as LedgerNumber, chkjourdt.Currency_Code as ChkCurrency, chkjourdt.Chk_Amount as ChkAmount,journaldt.Journal_date as collectionDate
from chkjourdt inner join transdt on chkjourdt.transdt_id = transdt.transdt_id
inner join trans on trans.trans_id = transdt.trans_id inner join checks on chkjourdt.Chk_Number=checks.Chk_Number inner join journaldt on journaldt.journaldt_id=checks.journaldt_id1
where 1=1 and Ledger_Activity='$activity'";*/
if($OrderSalesMan==1){
	
	$query_salesmen="SELECT distinct Salesmen_Code from clients";
	if($Salesmen!="")
		$query_salesmen .= " where Salesmen_Code = '$Salesmen'";
	//echo $query_salesmen;
	$result_salesmen = mysql_query($query_salesmen, $connection);
	while($row_salesmen = mysql_fetch_array($result_salesmen)){
		extract($row_salesmen);
		$query_chks="SELECT DATE_FORMAT(checks.Chk_ValueDate, '%d/%m/%Y') AS date,
				checks.Chk_number AS ChkNo,
				checks.Chk_bank AS BankName,
				checks.Ledger_number AS LedgerNumber,
				ledger.ledger_name AS ledgerName,
				checks.Currency_code AS ChkCurrency,
				checks.Chk_amount AS ChkAmount,
				checks.Journaldt_id1 AS idd,
				DATE_FORMAT(transdt.trans_date, '%d/%m/%Y') AS collectionDate,
				concat(setup.Clientaccount, SUBSTR(checks.Ledger_number
												FROM 5)) AS ACCOUNT,
				checks.journaldt_id1, trans.trans_reference AS TransReference
				FROM checks
				left JOIN journaldt ON checks.journaldt_id1=journaldt.journaldt_id
				LEFT JOIN chkjour ON checks.journaldt_id2=chkjour.journaldt_id
				LEFT JOIN ledger ON checks.ledger_number=ledger.ledger_number
				LEFT JOIN setup ON Checkaccount=SUBSTRING(checks.Ledger_number, 1, 4)
				LEFT JOIN clients ON clients.Ledger_number=concat(setup.ClientFirstDigit, SUBSTR(checks.Ledger_number
																					FROM 5))
				LEFT JOIN trans ON trans.journal_id = journaldt.journal_id
				LEFT JOIN transdt ON transdt.trans_id = trans.trans_id and transdt.ledger_number = journaldt.ledger_number
				where checks.Ledger_Activity='$activity' and clients.Salesmen_Code = '$Salesmen_Code'";
		if($collected==1 && $uncollected==1) $query_chks=$query_chks;
		else if($collected==1) $query_chks=$query_chks." and Journaldt_id2 is NOT NULL ";
		else if($uncollected==1) $query_chks=$query_chks." and Journaldt_id2 is NULL ";

		if($fromLedger!="" && $toLedger!="") $query_chks=$query_chks." and (checks.ledger_number between '$fromLedger' and '$toLedger') ";
		if($date_type == "date"){
			$query_chks = $query_chks. " and checks.Chk_ValueDate between '$fromDate' and '$toDate 23:59:59'";
		}
		else{
			$query_chks = $query_chks. " and checks.Chk_ValueDate between '$fromDate' and '$toDate 23:59:59'";
		}
		if($banks != ""){
			$query_chks = $query_chks. " and CChk_bank= '$banks'";
		}
		if($order_By == "date"){
			if($date_type == "date"){
				$query_chks = $query_chks." order by checks.Chk_ValueDate asc";
			}
			else{
				$query_chks = $query_chks." order by Chk_ValueDate and chk_bank asc";
			}
		}
		//echo $query_chks;exit;
		$result_chks = mysql_query($query_chks, $connection);
		if(mysql_num_rows($result_chks)>0){
			if($Salesmen_Code != "")
				$query_salesmen_name="SELECT Ledger_Name from salesmen where Ledger_Number = '$Salesmen_Code'";
				$result_salesmen_name = mysql_query($query_salesmen_name, $connection);
				while($row_salesmen_name = mysql_fetch_array($result_salesmen_name))
					extract($row_salesmen_name);
				echo "<tr><td width=120 style='font-size: 20px; font-family: Arial; padding: 3px; text-align:left; border-bottom: solid 1.5px #000000' colspan='2'><b>$Ledger_Name<b></td></tr>";
			while($row_chks = mysql_fetch_array($result_chks)){
				$collectDate="";
							
				
				extract($row_chks);
				
				if($date_type == "date"){
					$date_display = $NormalDate;
				}
				else{
					$date_display = $ValueDate;
				}
				/*$query_date="select DATE_FORMAT(journaldt.Journaldt_ValueDate, '%d/%m/%Y') as collectionDate from journaldt where journaldt_id=$idd";
						$result_chks = mysql_query($query_chks, $connection);
						while($row_chks = mysql_fetch_array($result_chks)){
						$collectDate=$row[0];
						}*/
				echo "<tr>
			<td width=120 style='font-size: 14px; font-family: Arial; padding: 3px;text-align:Center'>$date</td>
			<td width=100 style='font-size: 14px; font-family: Arial; padding: 3px;'>$ChkNo</td>
			<td width=200 style='font-size: 14px; font-family: Arial; padding: 3px; text-align: center'>$BankName</td>
			<td width=265 style='font-size: 14px; font-family: Arial; padding: 3px; text-align: left'>$LedgerNumber - $ledgerName</td>
			<td width=50 style='font-size: 14px; font-family: Arial; padding: 3px; text-align: center'>$ChkCurrency</td>
			<td width=110 style='font-size: 14px; font-family: Arial; padding: 3px; text-align: right'>".number_format($ChkAmount, 2)."</td>
			<td width=110 style='font-size: 14px; font-family: Arial; padding: 3px; text-align: right'>$collectionDate</td>
			<td width=110 style='font-size: 14px; font-family: Arial; padding: 3px; text-align: right'>$TransReference</td>
			
			</tr>";
			}
			echo "<tr style='height: 25px'>
			<td colspan=6 style=''></td>
			</tr>";
			echo "<tr style='height: 5px'>
			<td colspan=8 style='border-top: solid 1.5px #000000'></td>
			</tr>";
			$query_totals  = "select concat('Sum ', ChkCurrency) as SumOf, format(sum(ChkAmount), 2) as SumAmount 
							from ($query_chks) as Q 
							group by ChkCurrency";
			$result_totals = mysql_query($query_totals, $connection);
			$num = mysql_num_rows($result_totals);
			$count = 0;
			while($row_total = mysql_fetch_array($result_totals)){
				extract($row_total);
				$count++;
				if($count != $num){
					$border = "border-bottom: dotted 1.5px #000000;";
				}
				else{
					$border = "";
				}
				echo "<tr>
				<th colspan=5 style='font-size: 16px; font-family: Arial; padding: 3px; $border'>$SumOf</th>
				<td colspan=3 style='font-size: 16px; font-family: Arial; padding: 3px; text-align: right; $border'>$SumAmount</td>
				</tr>";
				echo "<tr style='height: 5px'>
				<td colspan=8 style='border-top: solid 1.5px #000000'></td>
				</tr>";
			}	
		}
	}
}
else{

	$query_chks="SELECT DATE_FORMAT(checks.Chk_ValueDate, '%d/%m/%Y') AS date,
					checks.Chk_number AS ChkNo,
					checks.Chk_bank AS BankName,
					checks.Ledger_number AS LedgerNumber,
					ledger.ledger_name AS ledgerName,
					checks.Currency_code AS ChkCurrency,
					checks.Chk_amount AS ChkAmount,
					checks.Journaldt_id1 AS idd,
					DATE_FORMAT(transdt.trans_date, '%d/%m/%Y') AS collectionDate,
					concat(setup.Clientaccount, SUBSTR(checks.Ledger_number
													FROM 5)) AS ACCOUNT,
					checks.journaldt_id1, trans.trans_reference AS TransReference
					FROM checks
					left JOIN journaldt ON checks.journaldt_id1=journaldt.journaldt_id
					LEFT JOIN chkjour ON checks.journaldt_id2=chkjour.journaldt_id
					LEFT JOIN ledger ON checks.ledger_number=ledger.ledger_number
					LEFT JOIN setup ON Checkaccount=SUBSTRING(checks.Ledger_number, 1, 4)
					LEFT JOIN clients ON clients.Ledger_number=concat(setup.Clientaccount, SUBSTR(checks.Ledger_number
																						FROM 5))
					LEFT JOIN trans ON trans.journal_id = journaldt.journal_id
					LEFT JOIN transdt ON transdt.trans_id = trans.trans_id and transdt.ledger_number = journaldt.ledger_number
					where checks.Ledger_Activity='$activity'";

	if($OrderSalesMan==1) $query_chks = $query_chks." and Salesmen_Code == '$Salesmen_Code'";
	if($collected==1 && $uncollected==1) $query_chks=$query_chks;
	else if($collected==1) $query_chks=$query_chks." and Journaldt_id2 is NOT NULL ";
	else if($uncollected==1) $query_chks=$query_chks." and Journaldt_id2 is NULL ";

	if($fromLedger!="" && $toLedger!="") $query_chks=$query_chks." and (checks.ledger_number between '$fromLedger' and '$toLedger') ";
	if($date_type == "date"){
	$query_chks = $query_chks. " and checks.Chk_ValueDate between '$fromDate' and '$toDate 23:59:59'";
	}
	else{
		$query_chks = $query_chks. " and checks.Chk_ValueDate between '$fromDate' and '$toDate 23:59:59'";
	}
	if($banks != ""){
		$query_chks = $query_chks. " and CChk_bank= '$banks'";
	}
	if($order_By == "date"){
		if($date_type == "date"){
		$query_chks = $query_chks." order by checks.Chk_ValueDate asc";
		}
		else{
			$query_chks = $query_chks." order by Chk_ValueDate and chk_bank asc";
		}
	}


/*
else{
	if($date_type == "date"){
	$query_chks = $query_chks." order by Chk_bank asc, transdt.Trans_date asc";
	}
	else{
		$query_chks = $query_chks." order by Chk_bank asc, chkjourdt.Chk_ValueDate asc ";
	}
}*/
//echo $query_chks;
$result_chks = mysql_query($query_chks, $connection);
while($row_chks = mysql_fetch_array($result_chks)){
	$collectDate="";
				
	
	extract($row_chks);
	
	if($date_type == "date"){
		$date_display = $NormalDate;
	}
	else{
		$date_display = $ValueDate;
	}
	/*$query_date="select DATE_FORMAT(journaldt.Journaldt_ValueDate, '%d/%m/%Y') as collectionDate from journaldt where journaldt_id=$idd";
			$result_chks = mysql_query($query_chks, $connection);
			while($row_chks = mysql_fetch_array($result_chks)){
			$collectDate=$row[0];
			}*/
	echo "<tr>
<td width=120 style='font-size: 14px; font-family: Arial; padding: 3px;text-align:Center'>$date</td>
<td width=100 style='font-size: 14px; font-family: Arial; padding: 3px;'>$ChkNo</td>
<td width=200 style='font-size: 14px; font-family: Arial; padding: 3px; text-align: center'>$BankName</td>
<td width=265 style='font-size: 14px; font-family: Arial; padding: 3px; text-align: left'>$LedgerNumber - $ledgerName</td>
<td width=50 style='font-size: 14px; font-family: Arial; padding: 3px; text-align: center'>$ChkCurrency</td>
<td width=110 style='font-size: 14px; font-family: Arial; padding: 3px; text-align: right'>".number_format($ChkAmount, 2)."</td>
<td width=110 style='font-size: 14px; font-family: Arial; padding: 3px; text-align: right'>$collectionDate</td>
<td width=110 style='font-size: 14px; font-family: Arial; padding: 3px; text-align: right'>$TransReference</td>

</tr>";
}
echo "<tr style='height: 25px'>
<td colspan=6 style=''></td>
</tr>";
echo "<tr style='height: 5px'>
<td colspan=8 style='border-top: solid 1.5px #000000'></td>
</tr>";
$query_totals  = "select concat('Sum ', ChkCurrency) as SumOf, format(sum(ChkAmount), 2) as SumAmount 
                  from ($query_chks) as Q 
				  group by ChkCurrency";
$result_totals = mysql_query($query_totals, $connection);
$num = mysql_num_rows($result_totals);
$count = 0;
while($row_total = mysql_fetch_array($result_totals)){
	extract($row_total);
	$count++;
	if($count != $num){
		$border = "border-bottom: dotted 1.5px #000000;";
	}
	else{
		$border = "";
	}
	echo "<tr>
	<th colspan=5 style='font-size: 16px; font-family: Arial; padding: 3px; $border'>$SumOf</th>
	<td colspan=3 style='font-size: 16px; font-family: Arial; padding: 3px; text-align: right; $border'>$SumAmount</td>
	</tr>";
}
echo "<tr style='height: 5px'>
<td colspan=8 style='border-bottom: solid 1.5px #000000'></td>
</tr>";
echo"</table>";
}
print <<<HERE
<script>
window.print();
</script>
HERE;
?>