<!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> Adjustment Report </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");
include 'function.inc.php';

set_time_limit(0);

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

$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>';

$querylog="select fi_showcostprice,FI_ItemSelling1,FI_ItemSelling2,FI_ItemSelling3,FI_ItemSelling4,FI_ItemSelling5 from ".$_SESSION['dbLabel'].".login where user_account='".$_SESSION['useraccount']."' and Upper(Login_Schema) = Upper('".$HTTP_SESSION_VARS['dbName']."')";
$resultlog=mysql_query($querylog) or die(mysql_error());
while($rows=mysql_fetch_array($resultlog)){
  extract($rows);
}
 

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

$querylog=" select ifNull(FI_RepRestrictProject,0) As FI_RepRestrictProject, ifNull(FI_RepRestrictCostcenter,0) As FI_RepRestrictCostcenter, ".
		  " ifNull(FI_RepRestrictWarehouse,0) As FI_RepRestrictWarehouse ".
          " from ".$_SESSION['dbLabel'].".login where user_account='".$_SESSION['useraccount']."' and Upper(Login_Schema) = Upper('".$_SESSION['dbName']."') ";
$resultlog=mysql_query($querylog) or die(mysql_error());
while($rows=mysql_fetch_array($resultlog)){
	extract($rows);
}

//Read From Module Table
$querymodule=" Select ifNull(Module_Grn,0) As Module_Grn, ifNull(Module_DoubleUnit,0) As Module_DoubleUnit, ifNull(Module_Heatno,0) As Module_Heatno From Module ";
$resultmodule=mysql_query($querymodule,$connection) or die(mysql_error());
while($rowmodule=mysql_fetch_array($resultmodule)){
	extract($rowmodule);
}

$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']."' ".
			  " and (loginstk.project_code is not null and loginstk.project_code != '') ".
			  " order by project.project_code";
$projectresult = mysql_query($queryproject,$connection) or die(mysql_error());
$projectexist  = mysql_num_rows($projectresult);

$querycostcent=" select distinct costcent.costcent_code, costcent_name ".
               " from costcent left join ".$_SESSION['dbLabel'].".loginstk on costcent.costcent_code like concat(ifnull(loginstk.costcent_code,costcent.costcent_code),'%') ".
			   " where user_account='".$_SESSION['useraccount']."' ".
			   " and (loginstk.costcent_code is not null and loginstk.costcent_code != '') ".
			   " order by costcent.costcent_code";
$costcentresult = mysql_query($querycostcent,$connection) or die(mysql_error());
$costcentexist  = mysql_num_rows($costcentresult);		

$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']."' ".
		 "                                          and (loginstk.warehouse_code is not null and loginstk.warehouse_code != '') ) ".
		 " order by warehouse.warehouse_code";
$resultwh = mysql_query($querywh,$connection) or die(mysql_error());
$whexist  = mysql_num_rows($resultwh);


$querygroup=" select distinct group_code,group_name ".
			" from grouping where group_code in (select group_code from ".$_SESSION['dbLabel'].".loginstk where user_account='".$_SESSION['useraccount']."') ".
			" order by group_code";
$resultgroup = mysql_query($querygroup,$connection) or die(mysql_error());
$groupexist  = mysql_num_rows($resultgroup);

$now=current_server_time($connection);
$dt=split(" ",$now[0]);
$printdate=split("-",$dt[0]);


if($adtype==1)	
	$title = 'Adjustment\In\Out';
else if($adtype==2)		
	$title = 'Stock In';
else if($adtype==3)		
	$title = 'Stock Out';
else if($adtype==4)		
	$title = 'Adjustment';
	

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'>$title List</span></td></tr>";
echo "<tr height='30px'><td colspan='2'> </td></tr>";
echo "</table>";	


$SelectedOptions = "Filter by: <br>";
if ($fromdate != '')      $SelectedOptions .= "-> Date From: ". $fromdate . "  To: ". $todate ."<br>";	
if ($curr != '')    	  $SelectedOptions .= "-> Currency: ". $curr ."<br>";	
if ($fromitem != '')      $SelectedOptions .= "-> Item From: ". $fromitem . "  To: ". $toitem ."<br>";

if($Module_Heatno==1 and $vShowHeatno==1)
if ($fromheatno != '')      $SelectedOptions .= "-> HeatNo From: ". $fromheatno . "  To: ". $toheatno ."<br>";

if ($fromgroup != '')     $SelectedOptions .= "-> Group From: ". $fromgroup . "  To: ". $togroup ."<br>";
if ($fromsubgroup != '')  $SelectedOptions .= "-> SubGroup From: ". $fromsubgroup . "  To: ". $tosubgroup ."<br>";
if ($fromcategory != '')  $SelectedOptions .= "-> Category From: ". $fromcategory . "  To: ". $tocategory ."<br>";
if ($fromproject != '')   $SelectedOptions .= "-> Project From: ". $fromproject . "  To: ". $toproject . "<br>";
if ($fromcostcent != '')  $SelectedOptions .= "-> Costcent From: ". $fromcostcent . "  To: ". $tocostcent . "<br>";
if ($fromwarehouse != '') $SelectedOptions .= "-> Warehouse From: ". $fromwarehouse . "  To: ". $towarehouse . "<br>";
if ($fromsupplier != '')  $SelectedOptions .= "-> Supplier From: ". $fromsupplier . "  To: ". $tosupplier . "<br>";

if ($fromledger != ''){
if ($dbcd == '1') 
	$SelectedOptions .= "-> Credit Accounts From: ". $fromledger . "  To: ". $toledger . "<br>";
else if ($dbcd == '2') 
	$SelectedOptions .= "-> Debit Accounts From: ". $fromledger . "  To: ". $toledger . "<br>";
else if ($dbcd == '3') 	
	$SelectedOptions .= "-> Credit/Debit Accounts From: ". $fromledger . "  To: ". $toledger . "<br>";
}
if (!empty($fromdate))   $fromdate = explodeword($fromdate);
if (!empty($todate))     $todate = explodeword($todate);  



$fromdate	   = GetSQLValueString($fromdate, 'date');
$todate        = GetSQLValueString($todate, 'date');   

  
  
print <<<HERE
<table width="100%" border="0" align="left" >
<tr><td>
<table class="report-master" width="100%" border="0" align="center" style='table-layout:fixed'>
<col width=100>
<tr><td>&nbsp;</td>
  <td  valign='top' width=100%><span class='header3'><b>$SelectedOptions</b></span></td>
</tr>
<tr height='5'><td><h5></td></tr>
</table></td></tr>
</table>
HERE;



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

if($adtype==2)
	$adjustmenttype='i';
else if($adtype==3)
	$adjustmenttype='o';
else if($adtype==4)
	$adjustmenttype='a';
else $adjustmenttype='';

if ($availgroupby == '2'){
	$dispgroupby = 'Project';
	$groupby 	 = 'adjustmentdt.project_code';
	$groupbyname = 'project_name';
} 
else if ($availgroupby == '3'){
	$dispgroupby = 'Costcenter';
	$groupby 	 = 'adjustmentdt.Costcent_code';
	$groupbyname = 'Costcent_name';
}
else if ($availgroupby == '4'){
	$dispgroupby = 'Warehouse';
	$groupby 	 = 'adjustmentdt.Warehouse_Code';
	$groupbyname = 'Warehouse_name';
}
else if ($availgroupby == '5'){
	$dispgroupby = 'Group';
	$groupby 	 = 'items.group_code';
	$groupbyname = 'group_name';
}
else if ($availgroupby == '6'){
	$dispgroupby = 'Subgroup';
	$groupby 	 = 'items.subgroup_code';
	$groupbyname = 'subgroup_name';
}
else if ($availgroupby == '7'){
	$dispgroupby = 'Category';
	$groupby 	 = 'items.category_code';
	$groupbyname = 'category_name';
}
else if ($availgroupby == '10'){
	$dispgroupby = 'Supplier';
	$groupby 	 = 'items.ledger_number';
	$groupbyname = 'ledger_name';
}
else if ($availgroupby == '11'){
	$dispgroupby = 'Item Code';
	$groupby 	 = 'adjustmentdt.item_code';
	$groupbyname = 'item_description';
}


if ($chkcost == 1)
	$wdt = "1180px";
else $wdt = "940px";


echo "<table width='$wdt' cellspacing='4' cellpadding='0' border='0'>";

echo "<col width='50px'><col width='120px'><col width='130px'><col width='80px'><col width='160px'><col width='160px'>
		<col width='160px'><col width='80px'>";

if ($chkcost == 1)
echo "<col width='120px'><col width='120px'>";

if ($availgroupby == '1' || $availgroupby == ''){
	echo "<tr height='20px'>
			<td align='left' style='$btop2;$bbottom;'><span class='header4'>Type</span></td>
			<td align='left' style='$btop2;$bbottom;'><span class='header4'>Ref.</span></td>
			<td align='left' style='$btop2;$bbottom;'><span class='header4'>Date</span></td>
			<td align='left' style='$btop2;$bbottom;' colspan='2'><span class='header4'>Project</span></td>
			<td align='left' style='$btop2;$bbottom;'><span class='header4'>Costcenter</span></td>
			<td align='left' style='$btop2;$bbottom;' colspan='2'><span class='header4'>Remark</span></td>";
	if ($chkcost == 1) 
	echo "	<td colspan='2' align='right' style='$btop2;$bbottom;'><span class='header4'>Total Amount</span></td>";
	echo "</tr>";
}

if (($availgroupby != '1' && $availgroupby != '') || $vgroupbydetail == '1'){
	echo "<tr height='15px'>
			<td colspan=2 align='center' style='$btop1;$bbottom;'><span class='header4'>Item Code</span></td>
			<td colspan=2 align='center' style='$btop1;$bbottom;'><span class='header4'>Description</span></td>
			<td align='center' style='$btop1;$bbottom;'><span class='header4'>Warehouse</span></td>
			<td align='center' style='$btop1;$bbottom;'><span class='header4'>Project</span></td>
			<td align='center' style='$btop1;$bbottom;'><span class='header4'>Costcenter</span></td>
			<td align='center' style='$btop1;$bbottom;'><span class='header4'>Qty.</span></td>";
	if ($chkcost == 1)
	echo "	<td align='center' style='$btop1;$bbottom;'><span class='header4'>Cost</span></td>
			<td align='center' style='$btop1;$bbottom;'><span class='header4'>Total</span></td>";
	echo "</tr>";
}


$queryadjustment = "select Adjustment_Reference, date_format(adjustment.Adjustment_Date, '%d-%m-%Y'), Adjustment_Remak,
					round(if('$curr'='$base1', Adjustment_AmountBase1, Adjustment_AmountBase2),$DecimalBase),
					adjustmentdt.item_code, item_description, adjustmentdt.Warehouse_Code, AdjustmentDt_Quantity, round(if('$curr'='$base1', AdjustmentDt_PriceBase1,AdjustmentDt_PriceBase2),$DecimalBase), ";
if ($availgroupby == '' || $availgroupby == '1')
	$queryadjustment .= " '' as groupby, '' as groupby ";
else 
	$queryadjustment .= " $groupby as groupby, $groupbyname as groupby ";
$queryadjustment .= "  , adjustment.project_code, adjustment.Costcent_code, adjustmentdt.project_code, adjustmentdt.Costcent_code,adjustment.Adjustment_EntryType ";
$queryadjustment .= " from adjustmentdt
					inner join adjustment on adjustment.Adjustment_Id=adjustmentdt.Adjustment_Id
					inner join items on items.item_code = adjustmentdt.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 on warehouse.Warehouse_Code = adjustmentdt.Warehouse_Code
					left join suppliers on suppliers.Ledger_Number = items.Ledger_Number
					left join project on project.project_code = adjustmentdt.project_code
					left join costcent on costcent.costcent_code = adjustmentdt.Costcent_code
					where (date_format(adjustment.Adjustment_Date, '%Y-%m-%d') between $fromdate and $todate) 
					and if ('$fromitem'='', 1=1, adjustmentdt.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 if('$fromsupplier'='', 1=1, items.Ledger_Number between '$fromsupplier' and '$tosupplier') 
					and if('$fromproject'='', 1=1, adjustmentdt.project_code between '$fromproject' and '$toproject') 
					and if('$fromcostcent'='', 1=1, adjustmentdt.Costcent_code between '$fromcostcent' and '$tocostcent') 
					and if('$fromwarehouse'='', 1=1, adjustmentdt.Warehouse_Code between '$fromwarehouse' and '$towarehouse') 
					and Adjustment.Adjustment_EntryType = if('".$adjustmenttype."'='',Adjustment.Adjustment_EntryType,'".$adjustmenttype."') ";
if ($dbcd == '1') 
	$queryadjustment .=	" and if('$fromledger' = '', 1=1, adjustmentdt.Invcreditaccount between '$fromledger' and '$toledger' ) ";
else if ($dbcd == '2') 
	$queryadjustment .=	" and if('$fromledger' = '', 1=1, adjustmentdt.InvDEBITaccount between '$fromledger' and '$toledger' ) ";
else if ($dbcd == '3') 
	$queryadjustment .=	" and if('$fromledger' = '', 1=1, (adjustmentdt.Invcreditaccount between '$fromledger' and '$toledger') or (adjustmentdt.InvDEBITaccount between '$fromledger' and 'toledger') ) ";
	
if ($whexist>0)	{
	$queryadjustment .= " and (adjustmentdt.Warehouse_Code In (select distinct warehouse_code from ".$_SESSION['dbLabel'].".loginstk where user_account='".$_SESSION['useraccount']."')  )";
}
if ($availgroupby == '' || $availgroupby == '1')				
	$queryadjustment .=	" order by Adjustment_Reference ";
else 
	$queryadjustment .=	" order by $groupby ";
//echo $queryadjustment;
$resadjustment = mysql_query($queryadjustment, $connection);

$tempref = '';
$tempgrpby = '**';

$ct = 0;		 		
$totalAmount = 0;
$totalQtyByGrp = 0;
$totalAmntByGrp = 0;
 
while($rw = mysql_fetch_array($resadjustment)){
	
	if ($availgroupby != '1'){
		$bgcolor2 = "bgcolor='#ABD4F1'";
		$bgcolor = "";
	}
	else {
		if (!($ct%2))
			$bgcolor = "bgcolor='#ABD4F1'";
		else 
			$bgcolor = "bgcolor='#EFF1F3'";
	}
	// reference
	if ($tempref != $rw[0] && ($availgroupby == '1' || $availgroupby == '')){
		echo "<tr $bgcolor2>
				<td>$rw[15]</td>
				<td><span class='header3'>$rw[0]</span></td>
				<td><span class='header3'>$rw[1]</span></td>
				<td align='left' colspan='2'><span class='header3'>$rw[11]</span></td>
				<td align='left' ><span class='header3'>$rw[12]</span></td>
				<td colspan='2'><span class='header3'>$rw[2]</span></td>";
		if ($chkcost == 1)
		echo "	<td align='right' colspan='2'><span class='header3'>".number_format($rw[3],$DecimalBase)."</span></td>";	
		echo "</tr>";
		
	}
	
	// group by
	if (($availgroupby != '1' && $availgroupby != '') && $tempgrpby != $rw[9]){
		
		if ($ct != 0){
			echo "<tr>
				<td align='right' colspan='7'><span class='header3'>Total in $tempgrpby</span></td>
				<td align='right' ><span class='header3'>".number_format($totalQtyByGrp,$DecimalBase)."</span></td>";
			if ($chkcost == 1) 
					echo "<td align='center'><span class='header3'></span></td>
						<td align='right' ><span class='header3'>".number_format($totalAmntByGrp,$DecimalBase)."</span></td>";	
			echo "</tr>";
			
			$totalQtyByGrp = 0;
			$totalAmntByGrp = 0;
		}
		echo "<tr $bgcolor2>
				<td>&#8594; by</td>
				<td colspan='2'><span class='header3'>$rw[9]</span></td>
				<td colspan='7'><span class='header3'>$rw[10]</span></td>
			</tr>";
	}
	
	// Details 
	if (($availgroupby != '1' && $availgroupby != '') || $vgroupbydetail == '1'){
		echo "<tr $bgcolor>
				<td colspan=2 align='left' ><span class='detail1'>$rw[4]</span></td>
				<td colspan=2 align='left' ><span class='detail1'>$rw[5]</span></td>
				<td align='left' ><span class='detail1'>$rw[6]</span></td>
				<td align='left' ><span class='detail1'>$rw[13]</span></td>
				<td align='left' ><span class='detail1'>$rw[14]</span></td>
				<td align='right' ><span class='detail1'>".number_format($rw[7],$DecimalBase)."</span></td>";
		if ($chkcost == 1)
		echo "	<td align='right'><span class='detail1'>".number_format($rw[8],$DecimalBase)."</span></td>
				<td align='right'><span class='detail1'>".number_format($rw[7]*$rw[8],$DecimalBase)."</span></td>";
		echo "</tr>";
		$totalQty = $totalQty + $rw[7];
				
		$totalQtyByGrp  = $totalQtyByGrp + $rw[7];	
		$totalAmntByGrp  = $totalAmntByGrp + ($rw[7]*$rw[8]);	
	
	}
	
	if (($availgroupby != '1' && $availgroupby != '') || $vgroupbydetail == '1'){
		$totalAmount = $totalAmount + ($rw[7]*$rw[8]);
	} else {
		if ($tempref != $rw[0]){
			$totalAmount = $totalAmount + $rw[3];
		}
	}
	$tempref   = $rw[0];
	$tempgrpby = $rw[9];
	
	
	
	$ct++;
}


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

echo "<tr>
		<td></td>
		<td><span class='header3'></span></td>
		<td><span class='header3'></span></td>
		<td colspan='4'></td><td align='right'><span class='header3'>";
if (($availgroupby != '1' && $availgroupby != '') || $vgroupbydetail == '1')
	echo number_format($totalQty,$DecimalBase);
echo " </span></td>";
if ($chkcost == 1){
	echo "	<td align='center'><span class='header3'></span></td><td align='right'><span class='header3'>".number_format($totalAmount,$DecimalBase)."</span></td>";	
}
echo "</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;
