<?php

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

print <<< HERE
<HTML>
<HEAD>
<TITLE> Quantity Budget Report </TITLE>

<style type="text/css">
	.header1 {
		font-size: 26px;
		font-weight: bold;
		font-family: serif;
	}
	.header2 {
		font-size: 20px;
		font-weight: bold;
		font-family: serif;
	}
	.header3 {
		font-size: 14px;
		font-family: serif;
	}
	.header4 {
		font-size: 14px;
		font-weight: bold;
		font-family: serif;
	}	
</style>
</HEAD>
<BODY>
HERE;

include("config.inc.php");

$connection = mysql_connect($config['dbServer'],$config['dbUser'],$config['dbPass'],true,65536)	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);
}

//Read From Setup Table							
$querysetup= " select Company_Name ".
			 " from setup ";
$resultsetup=mysql_query($querysetup,$connection) or die(mysql_error());
while($rowsetup=mysql_fetch_array($resultsetup)){
	extract($rowsetup);
}

if ($tobudgetdate == '') $tobudgetdate = $budgetdate;
if ($todate == '') $todate = $fromdate;

$dispfromdate = $fromdate;
$disptodate = $todate;
$dispbudgetdate = $budgetdate;
$disptobudgetdate = $tobudgetdate;

if ($budgetdate != ''){
	$ndate = explode('/', $budgetdate);
	$budgetdate = $ndate[2]."-".$ndate[1]."-".$ndate[0];
}

if ($tobudgetdate != ''){
	$ndate = explode('/', $tobudgetdate);
	$tobudgetdate = $ndate[2]."-".$ndate[1]."-".$ndate[0];
}

if ($fromdate != ''){
	$ndate = explode('/', $fromdate);
	$fromdate = $ndate[2]."-".$ndate[1]."-".$ndate[0];
	$month1 = (int)$ndate[1];
}
if ($todate != ''){
	$ndate = explode('/', $todate);
	$todate = $ndate[2]."-".$ndate[1]."-".$ndate[0];
	$month2 = (int)$ndate[1];
}
$project_code = "".$project_code;
$costcent_code = "".$costcent_code;


if ($month1>$month2){
	$temp = $month1;
	$month1 = $month2;
	$month2 = $month1;
}
$months = array();
for($i=$month1;$i<=$month2;$i++){
	array_push($months, $i);
}

echo "<table border = '0' cellspacing=0 cellpadding=0 width='1070px'><col width='285px'><col width='500px'><col width='285px'>";
echo "<tr><td colspan='3'><span class='header2'>$Company_Name</span></td></tr>";
echo "<tr><td colspan='3' align='center'><span class='header1'>Monthly Distribution of Quantities</span></td></tr>";
echo "<tr><td colspan='3' align='right'><span class='header3'><b>Printed Date:</b>".date("d/m/Y")."<br><b>Time:</b>".date("h:i:sa")."</span></td></tr>";
echo "<tr height='25px'><td colspan='3'><hr></td></tr>";
echo "<tr height='25px'><td colspan='3'><b>Filter By:</b></td></tr>";
if ($budgetdate != '')
echo "<tr height='25px'><td colspan='3'><span class='header3'><b>&#8594;Budget Date From/To: $dispbudgetdate - $disptobudgetdate</b></span></td></tr>";
if ($fromdate != '')
echo "<tr height='25px'><td colspan='3'><span class='header3'><b>&#8594;Transaction From/To Date: $dispfromdate - $disptodate</b></span></td></tr>";
if ($fromitem != '')
echo "<tr><td colspan='3'><span class='header3'><b>&#8594;From/To Item: $fromitem - $toitem</b></span></td></tr>";
if ($project_code != '')
echo "<tr><td colspan='3'><span class='header3'><b>&#8594;Project: $project_code</b></span></td></tr>";
if ($costcent_code != '')
echo "<tr><td colspan='3'><span class='header3'><b>&#8594;Costcenter: $costcent_code</b></span></td></tr>";

echo "<tr height='25px'><td colspan='3'></td></tr>";
echo "</table>";	

$btop = "border-top: 2px solid black";
$bright = "border-right: 1px solid black";
$bleft = "border-left: 1px solid black";
$bbottom = "border-bottom: 1px solid black";


echo "<table border = '0' cellspacing=0 cellpadding=0 >";
	
echo "<tr height='30px'>
		<td width='260px' align='center' style='$btop;$bbottom;'><span class='header4'>Item</span></td>
		<td width='80px' align='center' style='$btop;$bbottom;'><span class='header4'>Start Date</span></td>
		<td width='80px' align='center' style='$btop;$bbottom;'><span class='header4'>End Date</span></td>";

if ($showMonth == 0 || ($showMonth == 1 && in_array('1', $months))){
	echo "<td width='50px' align='center' style='$btop;$bbottom;'><span class='header4'>Jan</span></td>";
	if ($showQtyExists == 1)
		echo "<td width='50px' align='center' style='$btop;$bbottom;'><span class='header4'>Rem 1</span></td>";
}

if ($showMonth == 0 || ($showMonth == 1 && in_array('2', $months))){
	echo "<td width='50px' align='center' style='$btop;$bbottom;'><span class='header4'>Feb</span></td>";
	if ($showQtyExists == 1)
		echo "<td width='50px' align='center' style='$btop;$bbottom;'><span class='header4'>Rem 2</span></td>";
}

if ($showMonth == 0 || ($showMonth == 1 && in_array('3', $months))){
	echo "<td width='50px' align='center' style='$btop;$bbottom;'><span class='header4'>Mar</span></td>";
	if ($showQtyExists == 1)
		echo "<td width='50px' align='center' style='$btop;$bbottom;'><span class='header4'>Rem 3</span></td>";
}

if ($showMonth == 0 || ($showMonth == 1 && in_array('4', $months))){
	echo "<td width='50px' align='center' style='$btop;$bbottom;'><span class='header4'>Apr</span></td>";
	if ($showQtyExists == 1)
		echo "<td width='50px' align='center' style='$btop;$bbottom;'><span class='header4'>Rem 4</span></td>";
}

if ($showMonth == 0 || ($showMonth == 1 && in_array('5', $months))){
	echo "<td width='50px' align='center' style='$btop;$bbottom;'><span class='header4'>May</span></td>";
	if ($showQtyExists == 1)
		echo "<td width='50px' align='center' style='$btop;$bbottom;'><span class='header4'>Rem 5</span></td>";
}

if ($showMonth == 0 || ($showMonth == 1 && in_array('6', $months))){
	echo "<td width='50px' align='center' style='$btop;$bbottom;'><span class='header4'>Jun</span></td>";
	if ($showQtyExists == 1)
		echo "<td width='50px' align='center' style='$btop;$bbottom;'><span class='header4'>Rem 6</span></td>";
}

if ($showMonth == 0 || ($showMonth == 1 && in_array('7', $months))){
	echo "<td width='50px' align='center' style='$btop;$bbottom;'><span class='header4'>Jul</span></td>";
	if ($showQtyExists == 1)
		echo "<td width='50px' align='center' style='$btop;$bbottom;'><span class='header4'>Rem 7</span></td>";
}
if ($showMonth == 0 || ($showMonth == 1 && in_array('8', $months))){
	echo "<td width='50px' align='center' style='$btop;$bbottom;'><span class='header4'>Aug</span></td>";
	if ($showQtyExists == 1)
		echo "<td width='50px' align='center' style='$btop;$bbottom;'><span class='header4'>Rem 8</span></td>";
}
if ($showMonth == 0 || ($showMonth == 1 && in_array('9', $months))){
	echo "<td width='50px' align='center' style='$btop;$bbottom;'><span class='header4'>Sep</span></td>";
	if ($showQtyExists == 1)
		echo "<td width='50px' align='center' style='$btop;$bbottom;'><span class='header4'>Rem 9</span></td>";
}

if ($showMonth == 0 || ($showMonth == 1 && in_array('10', $months))){
	echo "<td width='50px' align='center' style='$btop;$bbottom;'><span class='header4'>Oct</span></td>";
	if ($showQtyExists == 1)
		echo "<td width='50px' align='center' style='$btop;$bbottom;'><span class='header4'>Rem 10</span></td>";
}
if ($showMonth == 0 || ($showMonth == 1 && in_array('11', $months))){
	echo "<td width='50px' align='center' style='$btop;$bbottom;'><span class='header4'>Nov</span></td>";
	if ($showQtyExists == 1)
		echo "<td align='center' style='$btop;$bbottom;'><span class='header4'>Rem 11</span></td>";
}
if ($showMonth == 0 || ($showMonth == 1 && in_array('12', $months))){
echo "<td width='50px'  align='center' style='$btop;$bbottom;'><span class='header4'>Dec</span></td>";
if ($showQtyExists == 1)
	echo "<td align='center' style='$btop;$bbottom;'><span class='header4'>Rem 12</span></td>";
}

echo "<td width='50px' align='center' style='$btop;$bbottom;'><span class='header4'>Total</span></td>";
echo "</tr>";

$query = "select qty_budget.item_code as item_code, item_description, date_format(sdate,'%d/%m/%Y') as sdate, date_format(edate,'%d/%m/%Y') as edate,
		ifnull(qty_month1,0) as qty_month1, ifnull(qty_month2,0) as qty_month2, ifnull(qty_month3,0) as qty_month3, ifnull(qty_month4,0) as qty_month4, 
		ifnull(qty_month5,0) as qty_month5, ifnull(qty_month6,0) as qty_month6, ifnull(qty_month7,0) as qty_month7, ifnull(qty_month8,0) as qty_month8,
		ifnull(qty_month9,0) as qty_month9, ifnull(qty_month10,0) as qty_month10, ifnull(qty_month11,0) as qty_month11, ifnull(qty_month12,0) as qty_month12
		from qty_budget
		inner join items on items.item_code = qty_budget.item_code
		where 1=1 and if('$fromitem' = '', 1=1, qty_budget.item_code between '$fromitem' and '$toitem')
		and if('$project_code'='', 1=1, qty_budget.project_code = '$project_code') and if('$costcent_code'='', 1=1, qty_budget.costcent_code = '$costcent_code')
		and if('$budgetdate'='', 1=1,  date_format(sdate, '%Y-%m-%d') >= '$budgetdate')
		and if('$tobudgetdate'='', 1=1,  date_format(sdate, '%Y-%m-%d') <= '$tobudgetdate')	";
	//echo $query;
$ct=0;
$totalmonth1 = 0;
$totalmonth2 = 0;
$totalmonth3 = 0;
$totalmonth4 = 0;
$totalmonth5 = 0;
$totalmonth6 = 0;

$totalmonth7 = 0;
$totalmonth8 = 0;
$totalmonth9 = 0;
$totalmonth10 = 0;
$totalmonth11 = 0;
$totalmonth12 = 0;

$totalQty = 0;
$result = mysql_query($query, $connection);
while($row = mysql_fetch_array($result)){
	extract($row);
	if (!($ct%2))
		$bgcolor = "bgcolor=#ABD4F1";
	else 
		$bgcolor = "bgcolor=#EFF1F3";
	
	$totalQtyRow = 0;
	
	echo "<tr height='20px' $bgcolor>
			<td width='260px' align='left'><span class='header4'>$item_code - $item_description</span></td>
			<td width='80px' align='center'><span class='header4'>$sdate</span></td>
			<td width='80px' align='center'><span class='header4'>$edate</span></td>";
	
	if ($showMonth == 0 || ($showMonth == 1 && in_array('1', $months))){	
		echo "<td width='50px' align='center'><span class='header4'>$qty_month1</span></td>";
		if ($showQtyExists == 1)
			echo "<td width='50px' align='center'><span class='header4'>".getRemainingQty($item_code, $qty_month1, '1', $connection)."</span></td>";
		$totalQtyRow += $qty_month1;
		$totalmonth1 += $qty_month1;
	}
	
	if ($showMonth == 0 || ($showMonth == 1 && in_array('2', $months))){
		echo "<td width='50px' align='center'><span class='header4'>$qty_month2</span></td>";
		if ($showQtyExists == 1)
			echo "<td width='50px' align='center'><span class='header4'>".getRemainingQty($item_code, $qty_month2, '2', $connection)."</span></td>";
		$totalQtyRow += $qty_month2;
		$totalmonth2 += $qty_month2;
	}
	
	if ($showMonth == 0 || ($showMonth == 1 && in_array('3', $months))){
		echo "<td width='50px' align='center'><span class='header4'>$qty_month3</span></td>";
		if ($showQtyExists == 1)
			echo "<td width='50px' align='center'><span class='header4'>".getRemainingQty($item_code, $qty_month3, '3', $connection)."</span></td>";
		$totalQtyRow += $qty_month3;
		$totalmonth3 += $qty_month3;
	}
	
	if ($showMonth == 0 || ($showMonth == 1 && in_array('4', $months))){
		echo "<td width='50px' align='center'><span class='header4'>$qty_month4</span></td>";
		if ($showQtyExists == 1)
			echo "<td width='50px' align='center'><span class='header4'>".getRemainingQty($item_code, $qty_month4, '4', $connection)."</span></td>";
		$totalQtyRow += $qty_month4;
		$totalmonth4 +=  $qty_month4;
	}

	if ($showMonth == 0 || ($showMonth == 1 && in_array('5', $months))){	
		echo "<td width='50px' align='center'><span class='header4'>$qty_month5</span></td>";
		if ($showQtyExists == 1)
			echo "<td width='50px' align='center'><span class='header4'>".getRemainingQty($item_code, $qty_month5, '5', $connection)."</span></td>";
		$totalQtyRow += $qty_month5;
		$totalmonth5 += $qty_month5;
	}
	
	if ($showMonth == 0 || ($showMonth == 1 && in_array('6', $months))){
		echo "<td width='50px' align='center'><span class='header4'>$qty_month6</span></td>";
		if ($showQtyExists == 1)
			echo "<td width='50px' align='center'><span class='header4'>".getRemainingQty($item_code, $qty_month6, '6', $connection)."</span></td>";
		$totalQtyRow += $qty_month6;
		$totalmonth6 += $qty_month6;
	}
	
	if ($showMonth == 0 || ($showMonth == 1 && in_array('7', $months))){
		echo "<td width='50px' align='center'><span class='header4'>$qty_month7</span></td>";
		if ($showQtyExists == 1)
			echo "<td width='50px' align='center'><span class='header4'>".getRemainingQty($item_code, $qty_month7, '7', $connection)."</span></td>";
		$totalQtyRow += $qty_month7;
		$totalmonth7 += $qty_month7;
	}
	
	if ($showMonth == 0 || ($showMonth == 1 && in_array('8', $months))){
		echo "<td width='50px' align='center'><span class='header4'>$qty_month8</span></td>";
		if ($showQtyExists == 1)
			echo "<td width='50px' align='center'><span class='header4'>".getRemainingQty($item_code, $qty_month8, '8', $connection)."</span></td>";
		$totalQtyRow += $qty_month8;
		$totalmonth8 += $qty_month8;
	}

	if ($showMonth == 0 || ($showMonth == 1 && in_array('9', $months))){	
		echo "<td width='50px' align='center'><span class='header4'>$qty_month9</span></td>";
		if ($showQtyExists == 1)
			echo "<td width='50px' align='center'><span class='header4'>".getRemainingQty($item_code, $qty_month9, '9', $connection)."</span></td>";
		$totalQtyRow += $qty_month9;
		$totalmonth9 += $qty_month9;
	}
	
	if ($showMonth == 0 || ($showMonth == 1 && in_array('10', $months))){	
		echo "<td width='50px' align='center'><span class='header4'>$qty_month10</span></td>";
		if ($showQtyExists == 1)
			echo "<td width='50px' align='center'><span class='header4'>".getRemainingQty($item_code, $qty_month10, '10', $connection)."</span></td>";
		$totalQtyRow += $qty_month10;
		$totalmonth10 += $qty_month10;
	}
	
	if ($showMonth == 0 || ($showMonth == 1 && in_array('11', $months))){
		echo "<td width='50px' align='center'><span class='header4'>$qty_month11</span></td>";
		if ($showQtyExists == 1)
			echo "<td width='50px' align='center'><span class='header4'>".getRemainingQty($item_code, $qty_month11, '11', $connection)."</span></td>";
		$totalQtyRow += $qty_month11;
		$totalmonth11 += $qty_month11;
	}
	if ($showMonth == 0 || ($showMonth == 1 && in_array('12', $months))){
		echo "<td width='50px' align='center'><span class='header4'>$qty_month12</span></td>";
		if ($showQtyExists == 1)
			echo "<td width='50px' align='center'><span class='header4'>".getRemainingQty($item_code, $qty_month12, '12', $connection)."</span></td>";
		$totalQtyRow += $qty_month12;
		$totalmonth12 += $qty_month12;
	}
	
	echo "<td width='50px' align='center'><span class='header4'>$totalQtyRow</span></td>";
	echo "</tr>";
	
	
		
	
		
	
	$totalQty = $totalQty + $totalQtyRow;
	$ct++;
}

echo "<tr height='10px'><td colspan='16'></td></tr>";

echo "<tr height='20px'>
			<td width='260px' align='center' style='$btop;'></td>
			<td width='80px' align='center' style='$btop;'></td>
			<td width='80px' align='center' style='$btop;'><span class='header4'>Total</span></td>";

if ($showMonth == 0 || ($showMonth == 1 && in_array('1', $months))){			
	echo "<td width='50px' align='center' style='$btop;'><span class='header4'>$totalmonth1</span></td>";
	if ($showQtyExists == 1)
		echo "<td width='50px' align='center' style='$btop;'><span class='header4'></span></td>";
}
if ($showMonth == 0 || ($showMonth == 1 && in_array('2', $months))){		
	echo "<td width='50px' align='center' style='$btop;'><span class='header4'>$totalmonth2</span></td>";
	if ($showQtyExists == 1)
		echo "<td width='50px' align='center' style='$btop;'><span class='header4'></span></td>";
}
if ($showMonth == 0 || ($showMonth == 1 && in_array('3', $months))){		
	echo "<td width='50px' align='center' style='$btop;'><span class='header4'>$totalmonth3</span></td>";
	if ($showQtyExists == 1)
		echo "<td width='50px' align='center' style='$btop;'><span class='header4'></span></td>";
}
if ($showMonth == 0 || ($showMonth == 1 && in_array('4', $months))){		
	echo "<td width='50px' align='center' style='$btop;'><span class='header4'>$totalmonth4</span></td>";
	if ($showQtyExists == 1)
		echo "<td width='50px' align='center' style='$btop;'><span class='header4'></span></td>";
}
if ($showMonth == 0 || ($showMonth == 1 && in_array('5', $months))){		
	echo "<td width='50px' align='center' style='$btop;'><span class='header4'>$totalmonth5</span></td>";
	if ($showQtyExists == 1)
		echo "<td width='50px' align='center' style='$btop;'><span class='header4'></span></td>";
}
if ($showMonth == 0 || ($showMonth == 1 && in_array('6', $months))){	
	echo "<td width='50px' align='center' style='$btop;'><span class='header4'>$totalmonth6</span></td>";
	if ($showQtyExists == 1)
		echo "<td width='50px' align='center' style='$btop;'><span class='header4'></span></td>";
}
if ($showMonth == 0 || ($showMonth == 1 && in_array('7', $months))){	
	echo "<td width='50px' align='center' style='$btop;'><span class='header4'>$totalmonth7</span></td>";
	if ($showQtyExists == 1)
		echo "<td width='50px' align='center' style='$btop;'><span class='header4'></span></td>";
}
if ($showMonth == 0 || ($showMonth == 1 && in_array('8', $months))){	
	echo "<td width='50px' align='center' style='$btop;'><span class='header4'>$totalmonth8</span></td>";
	if ($showQtyExists == 1)
		echo "<td width='50px' align='center' style='$btop;'><span class='header4'></span></td>";
}
if ($showMonth == 0 || ($showMonth == 1 && in_array('9', $months))){	
	echo "<td width='50px' align='center' style='$btop;'><span class='header4'>$totalmonth9</span></td>";
	if ($showQtyExists == 1)
		echo "<td width='50px' align='center' style='$btop;'><span class='header4'></span></td>";
}
if ($showMonth == 0 || ($showMonth == 1 && in_array('10', $months))){	
	echo "<td width='50px' align='center' style='$btop;'><span class='header4'>$totalmonth10</span></td>";
	if ($showQtyExists == 1)
		echo "<td width='50px' align='center' style='$btop;'><span class='header4'></span></td>";
}
if ($showMonth == 0 || ($showMonth == 1 && in_array('11', $months))){		
	echo "<td width='50px' align='center' style='$btop;'><span class='header4'>$totalmonth11</span></td>";
	if ($showQtyExists == 1)
		echo "<td width='50px' align='center' style='$btop;'><span class='header4'></span></td>";
}
if ($showMonth == 0 || ($showMonth == 1 && in_array('12', $months))){	
	echo "<td width='50px' align='center' style='$btop;'><span class='header4'>$totalmonth12</span></td>";
	if ($showQtyExists == 1)
		echo "<td width='50px' align='center' style='$btop;'><span class='header4'></span></td>";
}	
	echo "<td width='50px' align='center' style='$btop;'><span class='header4'>$totalQty</span></td>";
	echo "</tr>";
echo "</table>";



print <<< HERE

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

function getRemainingQty($item_code, $budgetQty, $month, $connection){
	$query = "select sum(ifnull(QuantityProduceOut,0) + ifnull(QuantityDelivered,0) + ifnull(QuantitySold,0))
			from stktransaction
			where item_code= '$item_code' and date_format(transaction_date, '%c') = '$month'
			group by item_code ";
	$res = mysql_query($query, $connection);		
	$row = mysql_fetch_row($res);
	$qty = $row[0];
	if ($qty == '') $qty=0;
	$qty = $budgetQty - $qty;
	return $qty;
	
}
	?>