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

session_start();
header("Content-type: text/html; charset=".$_SESSION['encodingmode']);
if($_SESSION['dbName']==""){
header("location:../index.php");
}


print <<< HERE
<HTML>
<HEAD>
<TITLE> List of Adjustment </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.master tr.group { font-size: 14px; 
						font-weight: bold;
						font-family:Times-New-Roman;
					  }				 
				 
table.detail td { font-size: 11px; 
                  font-family:Times-New-Roman;
                 }				 
table.headerdetail td { font-size: 11px; 
                        font-weight: bold;
                        font-family:Times-New-Roman;
                      }				 
				 

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

if(	$_SESSION['encodingmode'] == 'utf8' || $_SESSION['encodingmode'] == 'utf-8'){
	mysql_query("SET NAMES 'utf8'"); 
	mysql_query('SET CHARACTER SET utf8'); 
	mysql_set_charset('utf8', $connection);
}

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


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

print <<< HERE
<table width="100%" border="0" align="center">
<tr>
  <td width='30%'><b>$CompanyName</b></td>
  <td width='50%' align='center'><font size=4><b>List of Adjustment</b></font></td>
  <td width='10%'><font size=1><b> Printed Date: </b> <br><b>Time: </b>
  <td width='10%'><font size=1>
HERE;

echo $printdate[2]."/".$printdate[1]."/".$printdate[0];
//echo DATE("d"."/"."m"."/"."Y");
echo "<br>".$dt[1]." ".$dt[2];

$dispfromref  = $fromref;
$disptoref = $toref;
$dispfromsupp = $fromsupp;
$disptosupp = $tosupp;
$dispfromdate = $fromdate;
$disptodate = $todate;

if (empty($fromref)) $fromref = 'null'; 
if (empty($toref))   $toref = 'null';   
  
if ($cur == 'currency1'){
   $cur='null';  
   $displaycur = '';
}   
else {
   $displaycur = $cur;
   $cur="'".$cur."'";
}   


if (empty($fromproject))     $fromproject = 'null';
else   $fromproject = "'".$fromproject."%'";    

if (!empty($fromdate))
   $fromdate = explodeword($fromdate);
if (!empty($todate))
   $todate = explodeword($todate);  

if (empty($fromdate)) $fromdate = 'null';
else   $fromdate = "'".$fromdate."'"; 
   
if (empty($todate))   $todate = 'null';
else   $todate = "'".$todate."'";
   
if(empty($AdjustIN))   $AdjustIN = 0;
if(empty($AdjustOUT))  $AdjustOUT = 0;
if(empty($showdetail)) $showdetail = 0;



print <<<HERE
</font></td>
</tr>
<tr>
   <td colspan='4' width='100%'> <hr width='100%' color='black' size='1'> </td>
</tr>
</table>


<table class="params" width="100%" border="0" align="center" cellpadding='0' cellspacing='0' >
<tr>
  <td width="10%" valign='top'><b>Reference From/To</b></td>
  <td width="20%">$dispfromref - $disptoref </td>
  <td width="70%" colspan="4">&nbsp;</td>
</tr>
<tr>
  <td width="10%"><b>Date From/To</b></td>
  <td width="20%">$dispfromdate - $disptodate </td>
  <td width="7%"><b>Currency</b></td>
  <td width="5%">$displaycur </td>
  <td width="10%" align='center'><b>Project:</b></td>
  <td width="40%" align='left'>$dispfromproject </td>
</tr>
</table>

HERE;

echo"<table width='100%' border='0' align='center' class='master' cellpadding='0' cellspacing='0' style='table-layout:fixed;'>
	<col width=70>
	<col width=70>
	<col width=70>
	<col width=400>
	<col width=60>
	<col width=120>";
	
echo"<tr>";
echo"	<th align='left'>Date</th>
		<th align='center'>Ref.</th>
		<th align='center'>Folio </th>
		<th align='left'>Remark</th>
		<th align='center'>Cur.</th>
		<th align='center'>Amount $displaycur</th>";
echo"</tr>";


if ($showdetail == 1) {
	echo "<tr>";
	echo "  <td colspan='6'><table class='headerdetail' width='100%' border='0' align='left' cellpadding='0' cellspacing='0' style='table-layout:fixed;'>
			<col width=40>
			<col width=100>
			<col width=250>
			<col width=80>
			<col width=70>
			<col width=100>
			<col width=120>
			<col width=30>";
	echo "<tr>";		
	echo "  <td>&nbsp;</td>";
	echo "  <td align='left' colspan='2'> Item Code/Description </td>";
	echo "  <td align='center'> Warehouse </td>";
	echo "  <td align='center'> Qty. </td>";
	echo "  <td align='center'> Unit Price </td>";
	echo "  <td align='center'> Total </td>";
	echo "  <td>&nbsp;</td>";
	echo "</tr></table></td>";
	echo "</tr>";
}	



echo" <tr>
		<td colspan='6'><hr width='100%' color='black' size='2'></td>
	  </tr>";
 
    

   
$query= " Select Adjustment_Id, date_format(Adjustment_Date,'%d/%m/%Y') Adjustment_Date, Adjustment_Reference, Journal.Journal_Folio, ".
        " Adjustment_EntryType, Adjustment_Remak, Adjustment.Project_Code, Project_Name, Adjustment.Costcent_Code, Adjustment.Currency_Code, ".
		" ifnull(if(upper($cur)='".$Base1."',Adjustment_AmountBase1,if(upper($cur)='".$Base2."',Adjustment_AmountBase2, Adjustment_Amount)),0) AdjustmentAmount, ".
		" if('".$date."' = 'date', date_format(Adjustment_Date,'%Y-%m-%d'), Adjustment_Reference) OrderOption ".
 		" From Adjustment ".
		" Left Join Project on Adjustment.Project_Code = Project.Project_Code ".
		" Left Join Journal on Adjustment.Journal_Id = Journal.Journal_Id ";
				
if ($projectexist >	0)
	$query = $query." Left Join ( select distinct project_code from ".$_SESSION['dbLabel'].".loginstk ".
					"			  where loginstk.user_account='".$_SESSION['useraccount']."' ".
					"             and (loginstk.project_code is not null and loginstk.project_code != '') ".
					"	       ) Q1 on Adjustment.Project_Code like concat(Q1.project_code,'%') ";

$query = $query." Where Adjustment_Reference between ifNull(". $fromref .",Adjustment_Reference) and ifNull(". $toref.",Adjustment_Reference) ".
				" And upper(ifNull(Adjustment.Project_Code,-1)) like ifNull(upper(".$fromproject."), upper(ifNull(Adjustment.Project_Code,-1))) ".
				" And date_format(Adjustment_Date,'%Y-%m-%d') between ifNull(". $fromdate.",date_format(Adjustment_Date,'%Y-%m-%d')) and ifNull(". $todate.",date_format(Adjustment_Date,'%Y-%m-%d'))  ".
				" And (($AdjustIN = 1 and $AdjustOUT = 1 and 1 = 1) or  ".
				"      ($AdjustIN = 1 and $AdjustOUT = 0 and Upper(Adjustment.Adjustment_EntryType) = 'I') or ".
				"      ($AdjustIN = 0 and $AdjustOUT = 1 and Upper(Adjustment.Adjustment_EntryType) = 'O') or ".
				"      ($AdjustIN = 0 and $AdjustOUT = 0 and Upper(Adjustment.Adjustment_EntryType) = 'A'))  ";
				
$query = $query." Order By if($grpproject=1, Adjustment.Project_Code, null), OrderOption, Adjustment_Reference ";
				


				
$SumTotalAmount = 0;
$SumGroupAmount = 0;
$MasterProject  = '';
	
//echo $query;

$result=mysql_query($query,$connection) or die(mysql_error());
$col=0;
while($rows=mysql_fetch_row($result)){
	
		if($col%2==0){
			$color = "style='background-color:".$report_color1."'";
		}else{
			$color = "style='background-color:".$report_color2."'";
		}
	
	if (($grpproject == 1) && ($MasterProject == '' || $MasterProject != $rows[6])) {
	  
		if (($cur != 'null') && ($MasterProject != '' && $MasterProject != $rows[6])){
			echo " <tr class='group'>";
			echo "  <td colspan='5' align='right'> Total by Project $displaycur </td>";
			echo "  <td align='right'> ".format_currency($SumGroupAmount, $DecimalBase)." </td>";
			echo " </tr>";
		   
			$SumGroupAmount = 0;
		}	
		  
		echo "<tr class='group'>"; 
		echo "	<td colspan='6'> Group by Project  ". $rows[6] ." - ". $rows[7]."</td>";
		echo "</tr>"; 
		  
		  
		echo "<tr><td colspan='6'>&nbsp;</td></tr>";
		$MasterProject = $rows[6];
	
	}
if ($showdetail == 1) {	
	echo "<tr>";
	echo "	<td align='center'>". $rows[1] ."</td>";
	echo "	<td align='left'  >". $rows[2] ."</td>";
    echo "	<td align='center'>". $rows[3] ."</td>"; 	
	echo "	<td align='left'  >". $rows[5] ."</td>";
	echo "	<td align='center'>". $rows[9] ."</td>";
	echo "	<td align='right' >". format_currency($rows[10], $DecimalBase) ."</td>";
}else{
	echo "<tr $color>"; 
	echo "	<td align='center' style='border-right:1px solid black; '>". $rows[1] ."</td>";
	echo "	<td align='left'  style='border-right:1px solid black; padding-left:2px;'>". $rows[2] ."</td>";
    if($rows[3]!='')
		echo "	<td align='left'  style='border-right:1px solid black; '>". $rows[3] ."</td>";
	else	
		echo "	<td align='left'  style='border-right:1px solid black; '>&nbsp;</td>";
		
	if($rows[5]!='')
		echo "	<td align='left'  style='border-right:1px solid black; '>". $rows[5] ."</td>";
	else	
		echo "	<td align='left'  style='border-right:1px solid black; '>&nbsp;</td>";
		
	echo "	<td align='center' style='border-right:1px solid black; '>". $rows[9] ."</td>";
	echo "	<td align='right' style='border-right:1px solid black; padding-right:2px;'>". format_currency($rows[10], $DecimalBase) ."</td>";
}
	$col+=1;
	echo "</tr>";
	$SumTotalAmount  = $SumTotalAmount  + $rows[10];
	$SumGroupAmount  = $SumGroupAmount  + $rows[10];
	
	if ($showdetail == 1) {
		$querydt =  " Select AdjustmentDt.Item_Code, Items.Item_Description, AdjustmentDt.Warehouse_Code, AdjustmentDt_Quantity, ".
				  	" ifNull(if(upper($cur)='".$Base1."',AdjustmentDt_PriceBase1,if(upper($cur)='".$Base2."',AdjustmentDt_PriceBase2, AdjustmentDt_Price)),0) AdjustmentDtPrice, ".
					" (ifNull(AdjustmentDt_Quantity,0) * ifNull(if(upper($cur)='".$Base1."',AdjustmentDt_PriceBase1,if(upper($cur)='".$Base2."',AdjustmentDt_PriceBase2,AdjustmentDt_Price)),0)) AdjustmentDtTotal ".
					" From AdjustmentDt ".
					" Left Join Items on AdjustmentDt.Item_Code = Items.Item_Code ".
					" Where AdjustmentDt.Adjustment_Id = ". $rows[0];				
					
		//echo $querydt;
		
		$resultdt=mysql_query($querydt,$connection) or die(mysql_error());
		$col1=0;
		while($rowsdt=mysql_fetch_row($resultdt)){
				if($col1%2==0){
					$color1 = "style='background-color:".$report_color1."'";
				}else{
					$color1 = "style='background-color:".$report_color2."'";
				}
			echo "<tr $color1>";
			echo "  <td colspan='6'><table class='detail' width='100%' border='0' align='center' cellpadding='0' cellspacing='0' style='table-layout:fixed;' >
					<col width=40>
					<col width=100>
					<col width=250>
					<col width=80>
					<col width=70>
					<col width=100>
					<col width=120>
					<col width=30>";
			echo "<tr>";
			echo "  <td>&nbsp;</td>";
			echo "  <td align='left' style='border-right:1px solid black; '>". $rowsdt[0] ."</td>";
			echo "  <td align='left' style='border-right:1px solid black; padding-left:2px;'>". $rowsdt[1] ."</td>";
			echo "  <td align='left' style='border-right:1px solid black; padding-left:2px;'>". $rowsdt[2] ."</td>";
			echo "  <td align='right' style='border-right:1px solid black; padding-right:2px;'> ". number_format($rowsdt[3], $DecimalBase) ."</td>";
			echo "  <td align='right' style='border-right:1px solid black; padding-right:2px;'> ". number_format($rowsdt[4], $DecimalBase) ."</td>";
			echo "  <td align='right' style='border-right:1px solid black; padding-right:2px;'> ". number_format($rowsdt[5], $DecimalBase) ."</td>";
			echo "  <td>&nbsp;</td>";
			echo " </tr></table></td></tr>";
			$col1+=1;
		}
	}	
	

}

if (($grpproject == 1) && ($MasterProject != '')) {
	  
	if (($cur != 'null') && ($MasterProject != '' && $MasterProject != $rows[6])){
		echo " <tr class='group'>";
		echo "  <td colspan='5' align='right'> Total by Project $displaycur </td>";
		echo "  <td align='right'> ".format_currency($SumGroupAmount, $DecimalBase)." </td>";
		echo " </tr>";
		   
		$SumGroupAmount = 0;
	}	
		  
	echo "<tr><td colspan='6'>&nbsp;</td></tr>";
	
}

	
if ($cur != 'null'){
	echo "<tr><td colspan='6'>&nbsp;</td></tr>";
	echo "<tr class='group'>";
	echo "  <td colspan='5' align='right'> Total $displaycur </td>";
	echo "  <td align='right'> ".format_currency($SumTotalAmount, $DecimalBase)." </td>";
	echo "</tr>";
}

echo "</table>";

function format_currency($amount, $DecimalBase) { 

    if($amount < 0) 
        return "(".number_format(abs($amount),$DecimalBase).")"; 
 
    else return number_format($amount, $DecimalBase); 
} 

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;

?>