<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<?php

session_start();
if($_SESSION['dbName']==""){
header("location:../index.php");
}


print <<< HERE
<HTML>
<HEAD>
<TITLE> List of Transfer </TITLE>
<META NAME="Generator" CONTENT="EditPlus">
<META NAME="Author" CONTENT="">
<META NAME="Keywords" CONTENT="">
<META NAME="Description" CONTENT="">

<style type="text/css" >
table.params td { font-size: 12px; 
                  font-family:Times-New-Roman;
                 }

table.master th { font-size: 14px; 
                  font-weight: bold;
                  font-family:Times-New-Roman;
                      }				 
table.master td { font-size: 12px; 
                  font-family:Times-New-Roman;
                 }
table.group td { font-size: 12px; 
                 font-weight: bold;
                 font-family:Times-New-Roman;
               }				 
				 
table.detail td { font-size: 10px; 
                  font-family:Times-New-Roman;
                 }				 
table.headerdetail td { font-size: 10px; 
                        font-weight: bold;
                        font-family:Times-New-Roman;
                      }				 
table.totals td { font-size: 12px; 
                  font-weight: bold;
                  font-family:Times-New-Roman;
                 }					  

.header1 {
	font-size: 26px;
	font-weight: bold;
}
.header2 {
	font-size: 20px;
	font-weight: bold;
}
.header3 {
	font-size: 12px;
	font-weight: bold;
}	

.header4 {
	font-size: 14px;
	font-weight: bold;
}

.header5 {
	font-size: 14px;
	
}
.detail1 {
	font-size: 11px;
	font-weight: bold;
}

</style>


</HEAD>

HERE;


include("config.inc.php");

//Set Time Execution For the Report
ini_set('max_execution_time', $config['ReportTimeExecution']);

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



//maroun 30.03.2011
$querylog="SELECT print as printaccess from ".$_SESSION['dbLabel'].".login where user_account='".$_SESSION['useraccount']."' 	and Upper(Login_Schema) = Upper('".$_SESSION['dbName']."')	";
	$resultlog=mysql_query($querylog,$connection) or die(mysql_error());
	while($rows=mysql_fetch_array($resultlog)){
		extract($rows);
	}


if ($printaccess==0 or $printaccess=='')
echo'<BODY oncontextmenu="return false;">';
else
echo'<BODY>';

$querysetup="select grid_color1,grid_color2, DecimalBase, DecimalBase1, DecimalBase2, Base1, Base2, report_color1, report_color2, Company_Name As CompanyName from setup";
$resultsetup=mysql_query($querysetup,$connection) or die(mysql_error());
while($rows=mysql_fetch_array($resultsetup)){
  extract($rows);
}


$queryproject=" select distinct project.project_code, project_name ".
              " from project left join ".$_SESSION['dbLabel'].".loginstk on project.project_code like concat(ifnull(loginstk.project_code,project.project_code),'%') ".
			  " where user_account='".$_SESSION['useraccount']."' ".
			  " order by project.project_code";
$projectresult = mysql_query($queryproject,$connection) or die(mysql_error());
$projectexist   = mysql_num_rows($projectresult);

$querywh=" select distinct warehouse.warehouse_code, warehouse_name ".
         " from warehouse where warehouse_code in (select warehouse_code from ".$_SESSION['dbLabel'].".loginstk where user_account='".$_SESSION['useraccount']."') ".
		 " order by warehouse.warehouse_code";
$resultwh = mysql_query($querywh,$connection) or die(mysql_error());
$whexist   = mysql_num_rows($resultwh);
//echo $querywh;exit;

$fromdate 		= $_GET['fromdate'];
$todate 		= $_GET['todate'];
$fromitem 		= $_GET['fromitem'];
$toitem 		= $_GET['toitem'];

$fromgroup 		= $_GET['fromgroup'];
$togroup 		= $_GET['togroup'];
$fromsubgroup 	= $_GET['fromsubgroup'];
$tosubgroup 	= $_GET['tosubgroup'];
$fromcategory 	= $_GET['fromcategory'];
$tocategory 	= $_GET['tocategory'];

$fromproject 	= $_GET['fromproject'];
$toproject 		= $_GET['toproject'];
$fromcostcent 	= $_GET['fromcostcent'];
$tocostcent 	= $_GET['tocostcent'];
$fromwh 		= $_GET['fromwh'];
$towh 			= $_GET['towh'];

$chkdetail 		= $_GET['chkdetail'];
$grpused 		= $_GET['grpused'];

if ($grpused == '21'){
	$dispgroupby = 'From Project';
	$groupby 	 = 'transferdt.Project_code1';
	$groupbyname = 'frmprjt.project_name';
} else if ($grpused == '22'){
	$dispgroupby = 'To Project';
	$groupby 	 = 'transferdt.Project_code2';
	$groupbyname = 'toprjt.project_name';
} 
else if ($grpused == '31'){
	$dispgroupby = 'From Costcenter';
	$groupby 	 = 'transferdt.Costcent_Code';
	$groupbyname = 'frmcostcent.Costcent_name';
}
else if ($grpused == '32'){
	$dispgroupby = 'To Costcenter';
	$groupby 	 = 'transferdt.ToCostcent_Code';
	$groupbyname = 'tocostcent.Costcent_name';
}
else if ($grpused == '41'){
	$dispgroupby = 'From Warehouse';
	$groupby 	 = 'transferdt.TransferDt_WarehouseFrom';
	$groupbyname = 'frmwh.warehouse_name';
}
else if ($grpused == '42'){
	$dispgroupby = 'To Warehouse';
	$groupby 	 = 'transferdt.TransferDt_WarehouseTo';
	$groupbyname = 'towh.warehouse_name';
}
else if ($grpused == '5'){
	$dispgroupby = 'Item Code';
	$groupby 	 = 'transferdt.item_code';
	$groupbyname = 'item_description';
}
else if ($grpused == '6'){
	$dispgroupby = 'Group';
	$groupby 	 = 'items.group_code';
	$groupbyname = 'group_name';
}
else if ($grpused == '7'){
	$dispgroupby = 'Subgroup';
	$groupby 	 = 'items.subgroup_code';
	$groupbyname = 'subgroup_name';
}
else if ($grpused == '8'){
	$dispgroupby = 'Category';
	$groupby 	 = 'items.category_code';
	$groupbyname = 'category_name';
}
$now=current_server_time($connection);
$dt=split(" ",$now[0]);
$printdate=split("-",$dt[0]);


// header
echo "<table border='0' width='100%' cellpadding='0' cellspacing='0'>
	<col width='85%'><col width='15%'>";
echo "<tr height='30px'><td ><span class='header2'>$CompanyName</span></td><td><span class='header3'>Print Date: $printdate[2]-$printdate[1]-$printdate[0]<br>Print Time: $dt[1]</span></td></tr>";
echo "<tr height='30px'><td colspan='2'></td></tr>";
echo "<tr height='30px'><td colspan='2' align='center'><span class='header1'>Transfer List</span></td></tr>";
echo "<tr height='30px'><td colspan='2'> </td></tr>";
echo "<tr height='10px'><td colspan='2'><span class='header3'>Filter By: </span></td></tr>";

echo "<tr height='10px'><td colspan='2'><span class='header3'>From/To Date: $fromdate / $todate </span></td></tr>";
if ($fromitem != '') 
	echo "<tr height='10px'><td colspan='2'><span class='header3'>From/To Item: $fromitem / $toitem</span></td></tr>";

if ($fromgroup != '') 
	echo "<tr height='10px'><td colspan='2'><span class='header3'>From/To Group: $fromgroup / $togroup</span></td></tr>";

if ($fromsubgroup != '') 
	echo "<tr height='10px'><td colspan='2'><span class='header3'>From/To Subgroup: $fromsubgroup / $tosubgroup</span></td></tr>";

if ($fromcategory != '') 
	echo "<tr height='10px'><td colspan='2'><span class='header3'>From/To Category: $fromcategory / $tocategory</span></td></tr>";

if ($fromproject != '') 
	echo "<tr height='10px'><td colspan='2'><span class='header3'>From/To Project: $fromproject / $toproject</span></td></tr>";

if ($fromcostcent != '') 
	echo "<tr height='10px'><td colspan='2'><span class='header3'>From/To Costcenter: $fromcostcent / $tocostcent</span></td></tr>";

if ($fromwh != '') 
	echo "<tr height='10px'><td colspan='2'><span class='header3'>From/To Warehouse: $fromwh / $towh</span></td></tr>";

if ($dispgroupby != '') 
	echo "<tr height='10px'><td colspan='2'><span class='header3'>Group by: $dispgroupby </span></td></tr>";

echo "<tr height='20px'><td colspan='2'><span class='header3'></span></td></tr>";
echo "</table>";

echo "<table border='0' width='100%' cellpadding='0' cellspacing='4'>
	<col width='3%'>
	<col width='8%'>
	<col width='18%'>
	<col width='16%'>
	<col width='16%'>
	<col width='10%'>
	<col width='10%'>
	<col width='10%'>";

	
$ndate = explode("/", $fromdate);	
$fromdate = $ndate[2]."-".$ndate[1]."-".$ndate[0];
$ndate = explode("/", $todate);	
$todate = $ndate[2]."-".$ndate[1]."-".$ndate[0];

$btop2 		= "border-top: 2px solid #000";	
if ($grpused != '1' && $grpused != '')
	$btop1 = $btop2;
else $btop1 = "";	
$bbottom 	= "border-bottom: 1px solid #000";	


	//if (!($ct%2))
	///	$bgcolor = "bgcolor='#ABD4F1'";
	//else 
	//	
    $bgcolor = "bgcolor='#ABD4F1'";	 


if ($grpused == '1' || $grpused == '')	
echo "<tr height='20px'>
			<td align='center' style='$btop2;$bbottom;'><span class='header4'></span></td>
			<td align='left' style='$btop2;$bbottom;'><span class='header4'>Reference</span></td>
			<td align='left' style='$btop2;$bbottom;'><span class='header4'>Date</span></td>
			<td align='left' style='$btop2;$bbottom;'><span class='header4'>From WH.</span></td>
			<td align='left' style='$btop2;$bbottom;'><span class='header4'>To WH.</span></td>
			<td align='left' style='$btop2;$bbottom;'><span class='header4'>From Project</span></td>
			<td align='left' style='$btop2;$bbottom;'><span class='header4'>To Project</span></td>
			<td align='left' style='$btop2;$bbottom;'><span class='header4'>Remark</span></td>
	 </tr>";	
	 
if (($grpused != '1' && $grpused != '') || $chkdetail == 1)	
echo "<tr height='15px'>
			
			<td align='left' style='$btop1;$bbottom;' colspan='2'><span class='header4'>Item Code</span></td>
			<td align='left' style='$btop1;$bbottom;' colspan='3'><span class='header4'>Item Description</span></td>
			<td align='left' style='$btop1;$bbottom;'><span class='header4'>From WH.</span></td>
			<td align='left' style='$btop1;$bbottom;'><span class='header4'>To WH.</span></td>
			<td align='right' style='$btop1;$bbottom;'><span class='header4'>Qty</span></td>
	 </tr>";
	 
$querytransfer = "select Transfer_Reference, date_format(transfer.Transfer_Date, '%d-%m-%Y') as transferdate, Transfer_Remark, transferdt.item_code, item_description,  ";
if ($groupby == '')
	$querytransfer .= " TransferDt_Quantity, '' as groupby, '' as groupbyname, ";
else 
	$querytransfer .= " sum(TransferDt_Quantity), $groupby as groupby, $groupbyname as groupbyname,  ";
	$querytransfer .= " frmwh.warehouse_code as fromwharehouse, towh.warehouse_code as towharehouse, frmprjt.project_code as fromproject, toprjt.project_code as toproject ";
$querytransfer .= "	from transferdt
				inner join transfer on transfer.transfer_id = transferdt.transfer_id
				inner join items on items.item_code = transferdt.item_code
				left join grouping on grouping.group_code = items.group_code
				left join subgroup on subgroup.subgroup_code = items.subgroup_code
				left join category on category.category_code = items.category_code
				left join warehouse frmwh on frmwh.warehouse_code = transferdt.TransferDt_WarehouseFrom
				left join warehouse towh on towh.warehouse_code = transferdt.TransferDt_WarehouseTo
				left join project frmprjt on frmprjt.project_code = transferdt.Project_code1
				left join project toprjt on toprjt.project_code = transferdt.Project_code2
				left join costcent frmcostcent on frmcostcent.Costcent_code = transferdt.Costcent_Code
				left join costcent tocostcent on tocostcent.Costcent_code = transferdt.ToCostcent_Code
				where if('$fromitem'='', 1=1, transferdt.item_code between '$fromitem' and '$toitem')
				and if('$fromgroup'='', 1=1, items.group_code between '$fromgroup' and '$togroup')
				and if('$fromsubgroup'='', 1=1, items.subgroup_code between '$fromsubgroup' and '$tosubgroup')
				and if('$fromcategory'='', 1=1, items.category_code between '$fromcategory' and '$tocategory')
				and date_format(transfer.Transfer_Date,'%Y-%m-%d') between '$fromdate' and '$todate' ";
			
if ($grpused == '21')
	$querytransfer .= " and (if ('$fromproject' = '', 1=1, transferdt.Project_code1 between '$fromproject' and '$toproject' ))";
else if ($grpused == '22')
	$querytransfer .= " and (if ('$fromproject' = '', 1=1, transferdt.Project_code2 between '$fromproject' and '$toproject' ))";
else 
	$querytransfer .= " and (if ('$fromproject' = '', 1=1, transferdt.Project_code1 like '%$fromproject%' or transferdt.Project_code2 like '%$toproject%' ))";

if ($grpused == '31')
	$querytransfer .= " and (if ('$fromcostcent' = '', 1=1, transferdt.Costcent_Code between '$fromcostcent' and '$tocostcent' ))";
else if ($grpused == '32')
	$querytransfer .= " and (if ('$fromcostcent' = '', 1=1, transferdt.ToCostcent_Code between '$fromcostcent' and '$tocostcent' ))";
else 
	$querytransfer .= " and (if ('$fromcostcent' = '', 1=1,  transferdt.Costcent_Code like '%$fromcostcent%' or transferdt.ToCostcent_Code like '%$tocostcent%' ))";

if ($grpused == '41')
	$querytransfer .= " and (if ('$fromwh' = '', 1=1, transferdt.TransferDt_WarehouseFrom between '$fromwh' and '$towh' ))";
else if ($grpused == '42')
	$querytransfer .= " and (if ('$fromwh' = '', 1=1, transferdt.TransferDt_WarehouseTo between '$fromwh' and '$towh' ))";
else 
	$querytransfer .= " and (if ('$fromwh' = '', 1=1,  transferdt.TransferDt_WarehouseFrom like '%$fromwh%' or transferdt.TransferDt_WarehouseTo like '%$towh%' ))";


if ($whexist > 0)
{
  $querytransfer= $querytransfer." and (";
  $querytransfer= $querytransfer."  (transferdt.transferdt_warehousefrom In (select distinct warehouse_code from ".$_SESSION['dbLabel'].".loginstk where user_account='".$_SESSION['useraccount']."')  )"; 
  $querytransfer= $querytransfer." or (transferdt.transferdt_warehouseto In (select distinct warehouse_code from ".$_SESSION['dbLabel'].".loginstk where user_account='".$_SESSION['useraccount']."')  )";  
  $querytransfer= $querytransfer." )";
  }	

	
if ($groupby == '')
	$querytransfer .= " order by Transfer_Reference "; 			
else	{
	$querytransfer .= " group by $groupby, transferdt.item_code, transferdt.TransferDt_WarehouseFrom "; 			
	$querytransfer .= " order by $groupby "; 	
}
	//echo $querytransfer;
$restransfer = mysql_query($querytransfer, $connection);

$tempref = '';
$ct = 0;

$tempgrpby = '**';

$totalQty = 0;
$totalQtyByGrp = 0;

while($rw = mysql_fetch_array($restransfer)){
	//if (!($ct%2))
	///	$bgcolor = "bgcolor='#ABD4F1'";
	//else 
	//	$bgcolor = "bgcolor='#EFF1F3'";	 
	
	// header
	if ($tempref != $rw[0] && ($grpused == '1' || $grpused == '')){
		echo "<tr $bgcolor>
				<td>&#8594;</td>
				<td><span class='header5'>$rw[0]</span></td>
				<td><span class='header5'>$rw[1]</span></td>
				<td><span class='header5'>$rw[8]</span></td>
				<td><span class='header5'>$rw[9]</span></td>
				<td><span class='header5'>$rw[10]</span></td>
				<td><span class='header5'>$rw[11]</span></td>
				<td><span class='header5'>$rw[2]</span></td>
			</tr>";
		
	}
	
	// group by
	if (($grpused != '1' && $grpused != '') && $tempgrpby != $rw[6]){
		
		if ($ct != 0){
			echo "<tr>
				<td align='right' style='$btop2;$bbottom;' colspan='7'><span class='header3'>Total Qty in $tempgrpby</span></td>
				<td align='right' style='$btop2;$bbottom;'><span class='header3'>".number_format($totalQtyByGrp,$DecimalBase)."</span></td>
			</tr>";
			echo "<tr height='20px'><td align='right' colspan='8'></td></tr>";
			$totalQtyByGrp = 0;
		}
		echo "<tr $bgcolor>
				<td><span class='header5' >&#8594; by </span></td>
				<td colspan='2'>$rw[6]</span></td>
				<td colspan='3'>$rw[7]</span></td>
				<td colspan='2'></span></td>
			</tr>";
	}
	
	// details
	if (($grpused != '1' && $grpused != '') || $chkdetail == 1)	{
		echo "<tr >
				
				<td colspan='2'><span class='detail1'>$rw[3]</span></td>
				<td colspan='3'><span class='detail1'>$rw[4]</span></td>
				<td><span class='detail1'>$rw[8]</span></td>
				<td><span class='detail1'>$rw[9]</span></td>
				<td align='right'><span class='detail1'>".number_format($rw[5],$DecimalBase)."</span></td>
			</tr>";
			
		$totalQty 		= $totalQty + $rw[5];	
		$totalQtyByGrp  = $totalQtyByGrp + $rw[5];	
	}
	$tempref   = $rw[0];
	$tempgrpby = $rw[6];
	
	$ct++;
}	

if ($grpused != '1' && $grpused != '')	{
	echo "<tr>
			<td align='right' style='$btop2;$bbottom;' colspan='7'><span class='header3'>Total Qty in $tempgrpby</span></td>
			<td align='center' style='$btop2;$bbottom;'><span class='header3'>".number_format($totalQtyByGrp,$DecimalBase)."</span></td>
		</tr>";
	echo "<tr height='10px'><td colspan='8'></td></tr>";
}



if (($grpused != '1' && $grpused != '') || $chkdetail == 1)	{
	echo "<tr>
			<td align='right' style='$btop2;$bbottom;' colspan='7'><span class='header3'>Total</span></td>
			<td align='center' style='$btop2;$bbottom;'><span class='header3'>".number_format($totalQty,$DecimalBase)."</span></td>
		</tr>";
} 	
echo "</table>";		 
function current_server_time($connection){

  $string="Select date_format(NOW(),'%Y-%m-%d %h:%i %p') ";
  $result=mysql_query($string,$connection) or die(mysql_error());
  $now=mysql_fetch_row($result);

  return $now;
}

function explodeword($date)
{

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

if ($printaccess==1)
print <<< HERE

<script language=javascript>
if (typeof(window.print) != 'undefined')
{
    window.print();
}
</script>
</BODY>
</HTML>
HERE;

?>