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

$from_date = $_GET['from_date'];
$to_date   = $_GET['to_date'];
$trans_type = $_GET['trans_type'];
$trans_year = $_GET['trans_year'];
$trans_operator = $_GET['trans_operator'];
$trans_company = $_GET['trans_company'];
$journal_ref_search = $_GET['journal_ref_search'];
//$trans_year2 = explode("_", $trans_year);
//$trans_year2 = $trans_year2[2];

$query_module  = "select module_delivery as ModuleDelivery from module";
$result_module = mysql_query($query_module, $connection); 
$row_module    = mysql_fetch_array($result_module);
extract($row_module);

$query_setup  = "select base1, base2 from setup";
$result_setup = mysql_query($query_setup, $connection); 
$row_setup    = mysql_fetch_array($result_setup);
extract($row_setup);

if($ModuleDelivery == 1){
	$Deliveries = "Deliveries";
}
else{
	$Deliveries = "Sales";
}

print <<<HERE
<html>
<head>
<title>User Log: Journal</title>
</head>
<style>
@page { size: landscape; }
</style>
<body>
HERE;
echo "<table width=1500 style='border-collapse: collapse'>
<tr>
<th style='padding-top: 10px; padding-bottom: 10px; border-bottom: solid 1.5px #000000; font-size: 24px; font-family: Arial'>User Log: Journal</th>
</tr>
</table>";
$query_history = "SELECT Journal_ref_id AS JournalId,
       Journal_refgovernment as Journal_Reference,
	   journal_type as JournalType,
       concat(journal.Project_code, ' | ', journal.Project_Name) AS Project,
       date_format(Journal_date, '%d/%m/%Y') AS JournalDate,
       concat(journal.Costcent_Code, ' | ', journal.Costcent_Name) AS Costcenter,
       journal.Journal_Remark as Remarks,
       if(Journal_trans_type = 'I', 'Insert', if(Journal_trans_type = 'U', 'Update', 'Delete')) AS TransType,
       Journal_trans_company AS TransCompany,
       Journal_trans_schema AS TransYear,
       date_format(Journal_trans_date, '%d/%m/%Y %H:%i:%s') AS TransDate,
       Journal_trans_operator AS TransOperatorCode,
       users.user_name AS TransOperatorName
FROM ".$config['dbName_history'].".journal
INNER JOIN ".$_SESSION['dbLabel'].".users ON users.user_account = journal.Journal_trans_operator
WHERE 1=1";
if($from_date != "" && $to_date != ""){
	$from_date = explode("/", $from_date);
	$from_date = $from_date[2]."-".$from_date[1]."-".$from_date[0];
	$to_date = explode("/", $to_date);
	$to_date = $to_date[2]."-".$to_date[1]."-".$to_date[0];
	$query_history = $query_history." and Journal_trans_date between '$from_date' and '$to_date 23:59:59'";
}
if($trans_type != "a"){
	$query_history = $query_history." and Journal_trans_type = '$trans_type'";
}
if($trans_company != ""){
	$query_history = $query_history." and Journal_trans_company = '$trans_company'";
}
else{
	$query_history = $query_history." and Journal_trans_company in (select distinct(CompanyName) from ".$_SESSION['dbLabel'].".listuserdir where usercode = '".$_SESSION['useraccount']."')";
}
if($trans_company != "" && $trans_year != ""){
	$query_history = $query_history." and Journal_trans_schema = concat('$trans_company', '_', '$trans_year')";
}
if($trans_operator != ""){
	$query_history = $query_history." and Journal_trans_operator = '$trans_operator'";
}
if($journal_ref_search != ""){
	$query_history = $query_history." and journal.Journal_refgovernment = '$journal_ref_search'";
}
if($TransRefId != ""){
	$query_history = $query_history." and journal.journal_ref_id = '$TransRefId'";
}
if($orderby == "date"){
$query_history = $query_history." order by Journal_trans_date desc";
}
else
if($orderby == "user_date"){
$query_history = $query_history." order by users.user_name asc, Journal_trans_date desc";
}
else
if($orderby == "company_date"){
$query_history = $query_history." order by Journal_trans_schema asc, Journal_trans_date desc";
}
else
if($orderby == "company_user_date"){
$query_history = $query_history." order by Journal_trans_schema asc, users.user_name asc, Journal_trans_date desc";
}
//echo $query_history;
//$query_history = $query_history." order by Journal_trans_date desc";
$result_history = mysql_query($query_history, $connection);
while($row_history = mysql_fetch_array($result_history)){
	extract($row_history);
	
	
	$TransYear = explode("_", $TransYear);
	$TransYear = $TransYear[1];
	echo "<table width=1500>
	<tr style='height: 8px'>
	<td></td>
	</tr>
	</td>";
	echo "<table width=1100 style='border-collapse: collapse'>
	<tr>
	<td style='font-weight: bold; width: 150px; font-family: Arial; font-size: 14px'><u>User:</u> </td>
	<td style='width: 170px; font-family: Arial; font-size: 14px'>$TransOperatorName [$TransOperatorCode]</td>
	<td style='font-weight: bold; width: 150px; font-family: Arial; font-size: 14px'><u>Transaction Date:</u></td>
	<td style='width: 200px; font-family: Arial; font-size: 14px'>$TransDate</td>
	<td style='font-weight: bold; width: 150px; font-family: Arial; font-size: 14px'><u>Transaction Type:</u></td>
	<td style='width: 100px; font-family: Arial; font-size: 14px'>$TransType</td>
	<td style='font-weight: bold; width: 150px; font-family: Arial; font-size: 14px'><u>Company / Year:</u></td>
	<td style='width: 170px; font-family: Arial; font-size: 14px'>$TransCompany | $TransYear</td>
	</tr>
	</table>";
	echo "<table width=1500>
	<tr style='height: 7px'>
	<td></td>
	</tr>
	</td>";
	echo "<table width=1500 style='border-collapse: collapse'>
	<tr>
	<th style='font-size: 14px; font-family: Arial; border: solid 1px #000000; width: 100px;'>Ref #</th>
	<th style='font-size: 14px; font-family: Arial; border: solid 1px #000000; width: 120px;'>Journal Type</th>
	<th style='font-size: 14px; font-family: Arial; border: solid 1px #000000; width: 150px;'>Date</th>
	<th style='font-size: 14px; font-family: Arial; border: solid 1px #000000; width: 400px;'>Project</th>
	<th style='font-size: 14px; font-family: Arial; border: solid 1px #000000; width: 400px;'>Costcenter</th>
	<th style='font-size: 14px; font-family: Arial; border: solid 1px #000000; width: 330px;'>Remarks</th>
	</tr>
	<tr>
	<th style='font-size: 14px; font-family: Arial; border: solid 1px #000000; width: 100px; vertical-align: top;'>$Journal_Reference</th>
	<th style='font-size: 14px; font-family: Arial; border: solid 1px #000000; width: 120px; vertical-align: top;'>$JournalType</th>
	<th style='font-size: 14px; font-family: Arial; border: solid 1px #000000; width: 150px; vertical-align: top;'>$JournalDate</th>
	<th style='font-size: 14px; font-family: Arial; border: solid 1px #000000; width: 400px; vertical-align: top; text-align: left'>$Project</th>
	<th style='font-size: 14px; font-family: Arial; border: solid 1px #000000; width: 400px; vertical-align: top; text-align: left'>$Costcenter</th>
	<th style='font-size: 14px; font-family: Arial; border: solid 1px #000000; width: 330px; vertical-align: top; text-align: left'>$Remarks</th>
	</tr>
	</table>";
	echo "<table width=1500>
	<tr style='height: 7px'>
	<td></td>
	</tr>
	</table>";
	echo "<table width=1500>
	<tr>
	<td style='padding-bottom: 7px; font-weight: bold; font-family: Arial; font-size: 14px;'><u>Details:</u></td>
	</tr>
	</table>";
	echo "<table width=1500>
	<tr style='height: 7px'>
	<td></td>
	</tr>
	</table>
	<table width=1500 style='border-collapse: collapse'>
	<tr>
	<th style='font-size: 14px; font-family: Arial; border: solid 1px #000000; width: 150px;'>Ledger Number</th>
	<th style='font-size: 14px; font-family: Arial; border: solid 1px #000000; width: 245px;'>Ledger Name</th>
	<th style='font-size: 14px; font-family: Arial; border: solid 1px #000000; width: 200px;'>Description</th>
	<th style='font-size: 14px; font-family: Arial; border: solid 1px #000000; width: 70px;'>Currency</th>
	<th style='font-size: 14px; font-family: Arial; border: solid 1px #000000; width: 50px;'>Type D/C</th>
	<th style='font-size: 14px; font-family: Arial; border: solid 1px #000000; width: 130px;'>Amount</th>
	<th style='font-size: 14px; font-family: Arial; border: solid 1px #000000; width: 130px;'>$base1</th>
	<th style='font-size: 14px; font-family: Arial; border: solid 1px #000000; width: 130px;'>$base2</th>
	<th style='font-size: 14px; font-family: Arial; border: solid 1px #000000; width: 120px;'>Value Date</th>
	<th style='font-size: 14px; font-family: Arial; border: solid 1px #000000; width: 75px;'>Manual No</th>
	<th style='font-size: 14px; font-family: Arial; border: solid 1px #000000; width: 200px;'>Desc 1</th>
	</tr>";
	$querydt  = "SELECT journaldt.Ledger_number AS LedgerNumber,
       journaldt.Ledger_Name AS LedgerName,
       journaldt.Journaldt_desc AS Description,
       journaldt.Currency_code AS CurrencyCode,
       journaldt.Journaldt_dbcd AS Type,
       journaldt.Journaldt_amount AS Amount,
       journaldt.Journaldt_amount1 AS AmountBase1,
       journaldt.Journaldt_amount2 AS AmountBase2,
       date_format(Journaldt_ValueDate, '%d/%m/%Y') as ValueDate,
       journaldt.Manual_no as ManualNo,
       Journaldt_desc1 as Desc1
FROM ".$config['dbName_history'].".journaldt
WHERE Journal_ref_id = '$JournalId'";
	//echo $querydt;
	$resultdt = mysql_query($querydt, $connection);
	while($rowdt = mysql_fetch_array($resultdt)){
		extract($rowdt);
		echo "<tr>
	<th style='font-size: 14px; font-family: Arial; border: solid 1px #000000; width: 150px; vertical-align: top; '>$LedgerNumber</th>
	<th style='font-size: 14px; font-family: Arial; border: solid 1px #000000; width: 245px; text-align: left; vertical-align: top;'>$LedgerName</th>
	<th style='font-size: 14px; font-family: Arial; border: solid 1px #000000; width: 200px; vertical-align: top; text-align: left'>$Description</th>
	<th style='font-size: 14px; font-family: Arial; border: solid 1px #000000; width: 70px; vertical-align: top;'>$CurrencyCode</th>
	<th style='font-size: 14px; font-family: Arial; border: solid 1px #000000; width: 50px; vertical-align: top;'>$Type</th>
	<th style='font-size: 14px; font-family: Arial; border: solid 1px #000000; width: 130px; vertical-align: top; text-align: right '>$Amount</th>
	<th style='font-size: 14px; font-family: Arial; border: solid 1px #000000; width: 130px; vertical-align: top; text-align: right '>$AmountBase1</th>
	<th style='font-size: 14px; font-family: Arial; border: solid 1px #000000; width: 130px; vertical-align: top; text-align: right '>$AmountBase2</th>
	<th style='font-size: 14px; font-family: Arial; border: solid 1px #000000; width: 120px; vertical-align: top;  '>$ValueDate</th>
	<th style='font-size: 14px; font-family: Arial; border: solid 1px #000000; width: 75px; vertical-align: top;  '>$ManualNo</th>
	<th style='font-size: 14px; font-family: Arial; border: solid 1px #000000; width: 200px; vertical-align: top; text-align: left '>$Desc1</th>
	</tr>";
	}
	echo"</table>
	
	<table width=1500>
	<tr style='height: 30px'>
	<td style='border-bottom: dotted 1.5px #000000'></td>
	</tr>
	</table>
	</td>";
}
				  //echo $query_history;
print <<<HERE
</body>
<script>
window.print();
</script>
</html>
HERE;
?>