<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<?php
header("Content-type: text/html; charset=windows-1256");
session_start();
if($_SESSION['dbName']==""){
header("location:../index.php");
}


print <<< HERE
<HTML>
<HEAD>
<TITLE> Production Template Report </TITLE>

<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: 13px; 
                  font-family:Times-New-Roman;
                 }

table.detail th { font-size: 12px; 
                  font-weight: bold;
                  font-family:Times-New-Roman;
                 }				 			   
table.detail td { font-size: 12px; 
				  font-weight: normal;
                  font-family:Times-New-Roman;
                }			   

.dtitalic {
			font-style: italic;
		}
		
.totals { font-size: 12px; 
          font-weight: bold;
          font-family:Times-New-Roman;
        }					  
				 
h4 { border-top-style: solid; 
	 border-top-color: black; 
	 border-top-width: 2px;
   }
h5 { border-top-style: solid; 
	 border-top-color: black; 
	 border-top-width: 1px;
   }   
   
</style>




</HEAD>

HERE;



include("config.inc.php");
include 'function.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");

$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, ifNull(ListOfProductionMaskQty,2) as ListOfProductionMaskQty from setup";
$resultsetup=mysql_query($querysetup,$connection) or die(mysql_error());
while($rows=mysql_fetch_array($resultsetup)){
  extract($rows);
}

$querymodule=" Select ifNull(Module_NewProduction,0) As Module_NewProduction	
				From Module ";
$resultmodule=mysql_query($querymodule,$connection) or die(mysql_error());
while($rowmodule=mysql_fetch_array($resultmodule)){
	extract($rowmodule);
}

$querylog=" select ifNull(FI_RepRestrictProject,0) As FI_RepRestrictProject, ifNull(FI_RepRestrictCostcenter,0) As FI_RepRestrictCostcenter, ".
		  " ifNull(FI_RepRestrictWarehouse,0) As FI_RepRestrictWarehouse, ifNull(FI_ShowCostPrice,0) As FI_ShowCostPrice  ".
          " 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);
}

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


$ReportTitle = 'Production Template List'; 

print <<< HERE
<table width="100%" border="0" align="center">
<tr><td>
<table width='860' border='0' align='left' style='table-layout:fixed'>
HERE;

if ($prtheader == 1){
	ShowReportHeader('List of Productions','portrait', $connection);
} else {
	ShowReportTitle('List of Productions','portrait', $connection);
}

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

if (empty($curr))  $curr = $Base1;

$dispfromitem = $fromitem;
$disptoitem   = $toitem;
$dispcurr     = $curr;


  
if (empty($fromitem))  $fromitem = 'null';
else  $fromitem = "'".$fromitem."'";   
if (empty($toitem))  $toitem = 'null';
else  $toitem = "'".$toitem."'";      

if (empty($fromitemRM))  $fromitemRM = 'null';
else  $fromitemRM = "'".$fromitemRM."'";   
if (empty($toitemRM))  $toitemRM = 'null';
else  $toitemRM = "'".$toitemRM."'"; 

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

if (empty($fromcostcent))  $fromcostcent = 'null';
else  $fromcostcent = "'".$fromcostcent."'";    
if (empty($tocostcent))  $tocostcent = 'null';
else  $tocostcent = "'".$tocostcent."'";       
   
if (empty($fromwarehouse))  $fromwarehouse = 'null';
else  $fromwarehouse = "'".$fromwarehouse."'";      

if (empty($qtyprod)) $qtyprod = 1;
if (empty($showqtyexists)) $showqtyexists = 0;

print <<<HERE
</font></td>
</tr>
<tr height='5'><td colspan=4><h5></td></tr>
</table>
</td></tr>

<tr><td>
<table class='params' width='860' border='0' align='left' style='table-layout:fixed' >
<tr>
  <td width="12%"><b>Item From/To:</b></td>
  <td width="88%">$dispfromitem - $disptoitem </td>
</tr>
<tr height='5'><td colspan=2><h4></td></tr>
</table>
</td></tr>

<tr><td><br>
HERE;


echo "<table width='860' border='0' align='left' class='master' style='table-layout:fixed'>
	  <col width=150>
	  <col width=300>
	  <col width=100>
	  <col width=310>";

echo"  <tr>";
echo"	<th colspan='2' align='left'>Item Code / Description</th>";
echo"	<th>Qty To Be Prod.</th>";
echo"	<th>&nbsp;</th>";
echo"  </tr>";


echo"<tr height='5'><td colspan='4'><h5></td></tr>";
echo"<tr height='20'><td colspan='5'>";
echo "<table width='830' border='0' align='left' class='detail' style='table-layout:fixed'>
		<col width=20>
		<col width=130>
		<col width=300>
		<col width=90>
		<col width=90>
		<col width=90>
		<col width=90>
		<tr>";
	echo"	<th >&nbsp;</th>";
	echo"	<th align='left'>Item Code</th>";
	echo"	<th align='left'>Description</th>";
	echo"	<th align='center'>Qty</th>";
	echo"	<th align='center'>Cost</th>";
	echo"	<th align='center'>Total</th>";
	echo"</tr>";
		
echo"</table>";
echo"</td></tr>";		
echo"<tr height='5'><td colspan=4><h4></td></tr>";

		 


$query = '';

$QueryRestrictPrj = " Inner join ( select distinct project_code from ".$_SESSION['dbLabel'].".loginstk ".
					" 			   where loginstk.user_account='".$_SESSION['useraccount']."' ".
					"              and (project_code is not null and project_code != '') ".
					"			 ) Q4  ";
					
$QueryRestrictCst = " Inner join ( select distinct costcent_code from ".$_SESSION['dbLabel'].".loginstk ".
					"			   where loginstk.user_account='".$_SESSION['useraccount']."' ".
					"              and (costcent_code is not null and costcent_code != '') ".
					"			 ) Q5  ";
	

$query = $query." Select ItemCode, ItemDescription, QtyProd, ItemCodeDt, ItemDescriptionDt, PackDtQuantity, (QtyProd*PackDtQuantity) as QtyUsed, ".
				" QtyExists, (QtyExists - (QtyProd*PackDtQuantity)) as QtyNet ".
$query = $query." From ( ";
$query = $query." 	Select PackDt.Item_Code As ItemCode, ItemsFG.Item_Description As ItemDescription, $qtyprod As QtyProd, ".
				" 	PackDt.Item_CodePackDt As ItemCodeDt, ItemsRM.Item_Description As ItemDescriptionDt, ifNull(PackDt_Quantity,0) As PackDtQuantity,  ".
				" 	ifnull(QtyExists(PackDt.Item_CodePackDt, Null, now(), 0, 'PD', '', ''),0) As QtyExists ".
				" 	From PackDt ".
				" 	Inner Join Items as ItemsFG on PackDt.Item_Code = ItemsFG.Item_Code ".
				" 	Inner Join Items as ItemsRM on PackDt.Item_CodePackDt = ItemsRM.Item_Code ".
				"	left join ( select sum(ProductionFG_Quantity) as qtyProduce, item_code from productionfg group by item_code)
					as proFG on proFG.item_code = ItemsFG.item_code".
				" 	Where PackDt.Item_Code Between ifNull(".$fromitem.", PackDt.Item_Code) and ifNull(". $toitem.", PackDt.Item_Code) ".
				" 	And (PackDt.Group_Code IS NULL OR PackDt.Group_Code = '') ";
					  
if ($groupexist > 0)
	$query = $query." And ItemsFG.group_code In (select distinct group_code from ".$_SESSION['dbLabel'].".loginstk where user_account='".$_SESSION['useraccount']."')  ";  

$query = $query." 	Union ";
$query = $query." 	Select PackDt.Item_Code As ItemCode, ItemsFG.Item_Description As ItemDescription, $qtyprod As QtyProd, ".
				" 	null As ItemCodeDt, null As ItemDescriptionDt, ifNull(PackDt_Quantity,0) As PackDtQuantity, null As QtyExists ".
				" 	From PackDt ".
				" 	Inner Join Items as ItemsFG on PackDt.Item_Code = ItemsFG.Item_Code ".
				"	left join ( select sum(ProductionFG_Quantity) as qtyProduce, item_code from productionfg group by item_code)
						as proFG on proFG.item_code = ItemsFG.item_code ".
				" 	Where PackDt.Item_Code Between ifNull(".$fromitem.", PackDt.Item_Code) and ifNull(". $toitem.", PackDt.Item_Code) ".
				" 	And PackDt.Group_Code != '' ";
					  
if ($groupexist > 0)
	$query = $query." And ItemsFG.group_code In (select distinct group_code from ".$_SESSION['dbLabel'].".loginstk where user_account='".$_SESSION['useraccount']."')  ";  
	
$query = $query." ) as Q1  ";
$query = $query." Order By ItemCode, ItemCodeDt ";

	

$MasterItemCode = '';

$result=mysql_query($query,$connection) or die(mysql_error());
$col=0;
$sum = 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 ($MasterItemCode == '' || $MasterItemCode != $rows[0]) {
			
		echo "<tr>"; 
		echo "	<th align='left' valign='top' >". $rows[0] ."</th>";
		echo "	<th align='left' valign='top'>". $rows[1] ."</th>";
		echo "	<th align='center' valign='top'>". number_format($rows[2], $ListOfProductionMaskQty) ." </th>";
		echo "	<th align='center' valign='top'>&nbsp;</th>";
		echo "</tr>";

		$MasterItemCode = $rows[0];
	}
	$queryItemNonStock = " select item_nonstock, item_costmethod, currency_code from items where item_code = '$rows[3]';";
	$resultItemNonStock = mysql_query($queryItemNonStock, $connection);
	$rowItemNonStock = mysql_fetch_row($resultItemNonStock);
	$ItemNonStock = $rowItemNonStock[0];
	$ItemCostMethod = $rowItemNonStock[1];
	$ItemCurr = $rowItemNonStock[2];
	
	$queryPreCost = " select packdt_precosting, currency_code from packdt where item_code = '$rows[0]' and item_codepackdt = '$rows[3]';";
	$resultPreCost = mysql_query($queryPreCost, $connection);
	$rowPreCost = mysql_fetch_row($resultPreCost);
	$vPackDtPreCost = $rowPreCost[0];
	$vPrdCurr = $rowPreCost[1];
	
	$vPrdDate = $printdate[2]."/".$printdate[1]."/".$printdate[0];
	
	if ($ItemNonStock == 1 && $vPackDtPreCost == 1) {
		$vPrdDtCost = Get_RawMaterialCost($row[3], $vPrdDate, $vPrdCurr, $connection);
	}
	else{
		$vPrdDtCost = Get_CostPrice($rows[3], $ItemCurr, $ItemCostMethod, $vPrdDate, $vPrdCurr, $Base1, $Base2, $connection);
	}
	$vPrdDtCost *= number_format($rows[2], $ListOfProductionMaskQty);
	$total = $rows[5] * $vPrdDtCost;
	$sum += $total;

	echo "<tr><td colspan='4'>"; 
	echo "<table width='830' border='0' align='left' class='detail' style='table-layout:fixed'>
			<col width=20>
			<col width=130>
			<col width=300>
			<col width=90>
			<col width=90>
			<col width=90>
			<col width=90>";
	

	echo "<tr $color>";
	echo "	<td align='left' valign='top' >&nbsp;</td>";
	echo "	<td align='left' valign='top' >". $rows[3] ."</td>";
	echo "	<td align='left' valign='top' >". $rows[4] ."</td>";
	echo "	<td align='right' valign='top'>". number_format($rows[5], $ListOfProductionMaskQty) ."</td>";
	echo "	<td align='right' valign='top' >". number_format($vPrdDtCost, $ListOfProductionMaskQty) ."</td>";
	echo "	<td align='right' valign='top'>". number_format($total, $ListOfProductionMaskQty) ."</td>";
	echo "</tr>";
	
	if ($showqtyexists == 1) {
		$sub_query = " Select Warehouse_Code, Warehouse_Name, ifnull(QtyExists('".$rows[3]."', Warehouse_Code, now(), 0, 'PD', '', ''),0) As QtyExists From Warehouse";
		$sub_result=mysql_query($sub_query,$connection) or die(mysql_error());
		
		while($sub_rows=mysql_fetch_row($sub_result)){
			
			if ($sub_rows[2] != 0) {
				echo "<tr $color>"; 
				echo "	<td align='left' valign='top' colspan='2'>&nbsp;</td>";
				echo "	<td align='left' valign='top' class='dtitalic'>". $sub_rows[0] ." - ". $sub_rows[1] ."</td>";
				echo "	<td align='right' valign='top' class='dtitalic'>". number_format($sub_rows[2], $ListOfProductionMaskQty) ."</td>";
				echo "	<td align='left' valign='top' colspan='3'>&nbsp;</td>";
				echo "</tr>";
			}	
			
		}
	}	
	$col+=1;
	echo "</table>";	
	echo "</td></tr>";
}	
echo "<tr $color>"; 
echo "	<td align='right' valign='top' colspan='4'>Total Sum&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;". number_format($sum, $ListOfProductionMaskQty) ."</td>";
echo "</tr>";
echo "</table></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;
}

function insertMySQLDate($data){
	$data = insertDate($data);
	return substr($data, 0, 4) ."-". substr($data, 4, 2) ."-".substr($data, 6, 2)." ".strftime("%H").":".strftime("%M").":".strftime("%S");;
}

function insertDate($data){
	return substr($data, 6, 4) . substr($data, 3, 2) . substr($data, 0, 2);
}

function selectData($conn, $query){
	$result = mysql_query($query, $conn);
	$cur = mysql_fetch_row($result);		
	return $cur;
}

function Get_CostPrice($PItemCode, $PItemCurr, $PCostMethod, $PDate, $PCurrCode, $PCurBase1, $PCurBase2, $connection) {

	$ItemCost = 0;
			
	if ($PCostMethod == 1) {
		$query = "SELECT ifNull(GetLastValuesFunc('".$PCurBase1."', '".$PCurBase2."', '".$PItemCode."'),0) From Dual ";
		$result = selectData($connection, $query);
		//echo $query;
		$ItemCost = $result[0];
		$ItemCost = ConvertCurrency($PItemCurr, $PCurrCode, insertMySQLDate($PDate), $ItemCost, $connection);
	}
	else if ($PCostMethod == 0) {
		$query = "SELECT ifNull(MeanCostCur('".$PItemCode."', '".insertMySQLDate($PDate)."', 1, '".$PCurBase1."', '".$PCurrCode."'),0) From Dual ";
		$result = selectData($connection, $query);
		//echo $query;
		$ItemCost = $result[0];
	}

	Return	$ItemCost;
}

function Get_RawMaterialCost($PItemCode, $PDate, $PCurrCode, $connection) {

	$ItemCost = 0;
			
	$query = "SELECT ifNull(GetRawMaterialCost('".$PItemCode."', '".insertMySQLDate($PDate)."', '".$PCurrCode."'),0) From Dual ";
	$result = selectData($connection, $query);
	//echo $query;
	$ItemCost = $result[0];

	Return	$ItemCost;
}

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

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

?>