<?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'];
$salesjv_ref_search = $_GET['salesjv_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);

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

print <<<HERE
<html>
<head>
<title>User Log: Sales JV</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: Sales JV</th>
</tr>
</table>";
$query_history = "SELECT SalesJv_Ref_Id AS SalesJvRef,
       SalesJV_Reference,
       date_format(SalesJV_Date, '%d/%m/%Y') AS SalesJvDate,
       salesjv.client_code AS ClientCode,
       clients.ledger_name AS ClientName,
       date_format(JournalDt_ValueDate, '%d/%m/%Y') AS MaturityDate,
       salesjv.salesman_code AS SalesmanCode,
       clients.ledger_name as SalesmanName,
       salesjv.Currency_code AS CurrencyCode,
       salesjv.project_code AS ProjectCode,
       salesjv.Project_Name as ProjectName,
       salesjv.costcenter_code AS CostcentCode,
       salesjv.Costcent_Name as CostcentName,
       SalesJv_ManualNo as ManualNo,
       SalesJv_VatPrefix as VatPrefix,
       Sales_Remark AS Remarks,
       if(SalesJv_Trans_Type = 'I', 'Insert', if(SalesJv_Trans_Type = 'U', 'Update', 'Delete')) AS TransType,
       SalesJv_Trans_Company AS TransCompany,
       SalesJv_Trans_Schema AS TransYear,
       date_format(SalesJv_Trans_Date, '%d/%m/%Y %H:%i:%s') AS TransDate,
       SalesJv_Trans_Operator AS TransOperatorCode,
       users.user_name AS TransOperatorName
FROM ".$config['dbName_history'].".salesjv
INNER JOIN ".$_SESSION['dbLabel'].".users ON users.user_account = salesjv.SalesJv_Trans_Operator
inner join ".$_SESSION['dbName'].".clients on clients.ledger_number = salesjv.client_code
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 SalesJv_Trans_Date between '$from_date' and '$to_date 23:59:59'";
}
if($trans_type != "a"){
	$query_history = $query_history." and SalesJv_Trans_Type = '$trans_type'";
}
if($trans_company != ""){
	$query_history = $query_history." and SalesJv_Trans_Company = '$trans_company'";
}
else{
	$query_history = $query_history." and SalesJv_Trans_Company in (select distinct(CompanyName) from ".$_SESSION['dbLabel'].".listuserdir where usercode = '".$_SESSION['useraccount']."')";
}
if($trans_company != "" && $trans_year != ""){
	$query_history = $query_history." and SalesJv_Trans_Schema = concat('$trans_company', '_', '$trans_year')";
}
if($trans_operator != ""){
	$query_history = $query_history." and SalesJv_Trans_Operator = '$trans_operator'";
}
if($salesjv_ref_search != ""){
	$query_history = $query_history." and salesjv.SalesJV_Reference = '$salesjv_ref_search'";
}
if($TransRefId != ""){
	$query_history = $query_history." and salesjv.salesjv_ref_id = '$TransRefId'";
}
if($orderby == "date"){
$query_history = $query_history." order by SalesJv_Trans_Date desc";
}
else
if($orderby == "user_date"){
$query_history = $query_history." order by users.user_name asc, SalesJv_Trans_Date desc";
}
else
if($orderby == "company_date"){
$query_history = $query_history." order by SalesJv_Trans_Schema asc, SalesJv_Trans_Date desc";
}
else
if($orderby == "company_user_date"){
$query_history = $query_history." order by SalesJv_Trans_Schema asc, users.user_name asc, SalesJv_Trans_Date desc";
}
// echo $query_history;
//$query_history = $query_history." order by SalesJv_Trans_Date desc";
$result_history = mysql_query($query_history, $connection);
while($row_history = mysql_fetch_array($result_history)){
	extract($row_history);
	
	$query_salesman  = "select ledger_name as SalesmanName from ".$TransYear.".salesmen where ledger_number = '$SalesmanCode'";
	$result_salesman = mysql_query($query_salesman, $connection);
	$row_salesman    = mysql_fetch_array($result_salesman);
	extract($row_salesman);
	$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: 50px;'>Ref #</th>
	<th style='font-size: 14px; font-family: Arial; border: solid 1px #000000; width: 120px;'>Date</th>
	<th style='font-size: 14px; font-family: Arial; border: solid 1px #000000; width: 250px;'>Client</th>
	<th style='font-size: 14px; font-family: Arial; border: solid 1px #000000; width: 120px;'>Maturity Date</th>
	<th style='font-size: 14px; font-family: Arial; border: solid 1px #000000; width: 150px;'>Salesman</th>
	<th style='font-size: 14px; font-family: Arial; border: solid 1px #000000; width: 100px;'>Currency</th>
	<th style='font-size: 14px; font-family: Arial; border: solid 1px #000000; width: 100px;'>Project</th>
	<th style='font-size: 14px; font-family: Arial; border: solid 1px #000000; width: 100px;'>Costcenter</th>
	<th style='font-size: 14px; font-family: Arial; border: solid 1px #000000; width: 100px;'>Manual No</th>
	<th style='font-size: 14px; font-family: Arial; border: solid 1px #000000; width: 100px;'>VAT Prefix</th>
	<th style='font-size: 14px; font-family: Arial; border: solid 1px #000000; width: 200px;'>Remarks</th>
	</tr>
	<tr>
	<th style='font-size: 14px; font-family: Arial; border: solid 1px #000000; width: 50px; vertical-align: top;'>$SalesJV_Reference</th>
	<th style='font-size: 14px; font-family: Arial; border: solid 1px #000000; width: 120px;  vertical-align: top;'>$SalesJvDate</th>
	<th style='font-size: 14px; font-family: Arial; border: solid 1px #000000; width: 250px; text-align: left; vertical-align: top;'>$ClientCode | $ClientName</th>
	<th style='font-size: 14px; font-family: Arial; border: solid 1px #000000; width: 120px; vertical-align: top;'>$MaturityDate</th>
	<th style='font-size: 14px; font-family: Arial; border: solid 1px #000000; width: 150px; vertical-align: top; text-align: left;'>$SalesmanCode | $SalesmanName</th>
	<th style='font-size: 14px; font-family: Arial; border: solid 1px #000000; width: 100px; vertical-align: top;'>$CurrencyCode</th>
	<th style='font-size: 14px; font-family: Arial; border: solid 1px #000000; width: 100px; vertical-align: top; text-align: left;'>$ProjectCode | $ProjectName</th>
	<th style='font-size: 14px; font-family: Arial; border: solid 1px #000000; width: 100px; vertical-align: top; text-align: left;'>$CostcentCode | $CostcentName</th>
	<th style='font-size: 14px; font-family: Arial; border: solid 1px #000000; width: 100px;  vertical-align: top;'>$ManualNo</th>
	<th style='font-size: 14px; font-family: Arial; border: solid 1px #000000; width: 100px;  vertical-align: top;'>$VatPrefix</th>
	<th style='font-size: 14px; font-family: Arial; border: solid 1px #000000; width: 200px; text-align: left; vertical-align: top;'>$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: 120px;'>Ledger Number</th>
	<th style='font-size: 14px; font-family: Arial; border: solid 1px #000000; width: 250px;'>Ledger Name</th>
	<th style='font-size: 14px; font-family: Arial; border: solid 1px #000000; width: 120px;'>Description</th>
	<th style='font-size: 14px; font-family: Arial; border: solid 1px #000000; width: 120px;'>Description 2</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: 50px;'>Quantity</th>
	<th style='font-size: 14px; font-family: Arial; border: solid 1px #000000; width: 75px;'>Price</th>
	<th style='font-size: 14px; font-family: Arial; border: solid 1px #000000; width: 50px;'>Disc. %</th>
	<th style='font-size: 14px; font-family: Arial; border: solid 1px #000000; width: 75px;'>Total</th>
	<th style='font-size: 14px; font-family: Arial; border: solid 1px #000000; width: 75px;'>VAT</th>
	<th style='font-size: 14px; font-family: Arial; border: solid 1px #000000; width: 245px;'>Project</th>
	<th style='font-size: 14px; font-family: Arial; border: solid 1px #000000; width: 245px;'>Costcenter</th>
	</tr>";
	$querydt  = "select salesjvdt.Ledger_Number as LedgerNumber,
salesjvdt.Ledger_Name as LedgerName,
salesjvdt.SalesJVDt_Description as Description,
salesjvdt.SalesJVDt_Description2 as Description2,
salesjvdt.SalesJvDt_ManualNo as ManualNo,
salesjvdt.salesjvdt_quantity as Qty,
salesjvdt.salesjvdt_price as Price,
salesjvdt.salesjvdt_DiscountPer as DiscPer,
(salesjvdt_price - (salesjvdt_price*salesjvdt_DiscountPer/100)) * salesjvdt_quantity as Total,
if(SalesJVDt_VAT = 1, 'True', 'False') as VAT,
salesjvdt.Project_Code as ProjectCode,
salesjvdt.Project_Name as ProjectName,
salesjvdt.Costcenter_Code as CostcentCode,
salesjvdt.Costcent_Name as CostcentName
from ".$config['dbName_history'].".salesjvdt
where salesjv_ref_id = '$SalesJvRef'";
	// 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: 120px; vertical-align: top; text-align: left'>$LedgerNumber</th>
	<th style='font-size: 14px; font-family: Arial; border: solid 1px #000000; width: 250px; text-align: left; vertical-align: top;'>$LedgerName</th>
	<th style='font-size: 14px; font-family: Arial; border: solid 1px #000000; width: 120px; vertical-align: top; text-align: left'>$Description</th>
	<th style='font-size: 14px; font-family: Arial; border: solid 1px #000000; width: 120px; vertical-align: top; text-align: left'>$Description2</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: 50px; vertical-align: top;'>$Qty</th>
	<th style='font-size: 14px; font-family: Arial; border: solid 1px #000000; width: 75px; vertical-align: top; text-align: right;'>$Price</th>
	<th style='font-size: 14px; font-family: Arial; border: solid 1px #000000; width: 50px; text-align: right; vertical-align: top;'>$DiscPer</th>
	<th style='font-size: 14px; font-family: Arial; border: solid 1px #000000; width: 75px; text-align: right; vertical-align: top;'>$Total</th>
	<th style='font-size: 14px; font-family: Arial; border: solid 1px #000000; width: 75px; vertical-align: top;'>$VAT</th>
	<th style='font-size: 14px; font-family: Arial; border: solid 1px #000000; width: 245px; text-align: left; vertical-align: top;'>$ProjectCode | $ProjectName</th>
	<th style='font-size: 14px; font-family: Arial; border: solid 1px #000000; width: 245px; text-align: left; vertical-align: top;'>$CostcentCode | $CostcentName</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;
?>