<?php
require_once('Spreadsheet/Excel/Writer.php');

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


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

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

$querysetup="select grid_color1,grid_color2, DecimalBase, DecimalBase1, DecimalBase2, 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);
}


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


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

if ($option == 1)
	$ReportTitle = 'List of Confirmed Sales Order';
else if ($option == 2)
	$ReportTitle = 'List of Unconfirmed Sales Order';	
else if ($option == 3)
	$ReportTitle = 'List of Cancelled Sales Order';	
else if ($option == 4)
	$ReportTitle = 'List of Sales Order';	
	
	
//Creating a workbook
$workbook = new Spreadsheet_Excel_Writer();

//Sending HTTP headers
$workbook->send('Exp_ListofSalesOrder.xls');
 
//Creating a worksheet
$worksheet=&$workbook->addWorksheet('List of SalesOrder');
$worksheet->setLandscape();
 
//set all columns same width
$columnWidth = 10;

//Title Format 
$sheetTitleFormat =& $workbook->addFormat(array('bold'=>1,
  'size'=>10));

//Column Title Format  
$columnTitleFormat =& $workbook->addFormat(array('bold'=>1,
  'top'=>1,
  'bottom'=>1 ,
  'size'=>15,
  'color'=>0x0A,
  ));
  
 
  
// Regular Format
$regularleftFormat =& $workbook->addFormat(array('size'=>9,
  'align'=>'left'  ));
$regularrightFormat =& $workbook->addFormat(array('size'=>9,
  'align'=>'right'  ));  
$regularrightFormatNum =& $workbook->addFormat(array('size'=>9,
  'align'=>'right'  ));  
$regularcenterFormat =& $workbook->addFormat(array('size'=>9,
  'align'=>'center'  ));    
  
$regularleftFormat1 =& $workbook->addFormat(array('size'=>8,
  'align'=>'left'  ));
$regularrightFormat1 =& $workbook->addFormat(array('size'=>8,
  'align'=>'right'  ));    
$regularrightFormat1Num =& $workbook->addFormat(array('size'=>8,
  'align'=>'right'  )); 
  
$TitleFormat =& $workbook->addFormat(array('size'=>11,
  'align'=>'left',
  'bold'=>1
  ));

$boldleftFormat =& $workbook->addFormat(array('size'=>9,
  'align'=>'left'  ,
  'valign'=>'top'  ,
  'bold'=>1
  ));  
$boldrightFormat =& $workbook->addFormat(array('size'=>9,
  'align'=>'right'  ,
  'bold'=>1
  )); 
$boldrightFormatNum =& $workbook->addFormat(array('size'=>9,
  'align'=>'right'  ,
  'bold'=>1
  )); 
$boldcenterFormat =& $workbook->addFormat(array('size'=>9,
  'align'=>'center'  ,
  'valign'=>'top'  ,
  'bold'=>1
  ));    
  
$grouplevel1Format =& $workbook->addFormat(array('size'=>10,
  'align'=>'left',
  'bold'=>1,
  'color'=>0x0C
  )); 
$grouplevel2Format =& $workbook->addFormat(array('size'=>10,
  'align'=>'left',
  'bold'=>0,
  'color'=>0x0A
  )); 
$grouplevel3Format =& $workbook->addFormat(array('size'=>9,
  'align'=>'left',
  'italic'=>1,
  'color'=>0x11
  ));   
$grouplevel1totalFormat =& $workbook->addFormat(array('size'=>10,
  'align'=>'right',
  'bold'=>1,
  'color'=>0x0C
  ));  
$grouplevel2totalFormat =& $workbook->addFormat(array('size'=>10,
  'align'=>'right',
  'bold'=>0,
  'color'=>0x0A
  ));  
$grouplevel3totalFormat =& $workbook->addFormat(array('size'=>9,
  'align'=>'right',
  'italic'=>1,
  'color'=>0x11
  ));   

$seloptionFormat =& $workbook->addFormat(array('size'=>9,
  'align'=> 'left'  ,
  'valign'=> 'top'  ,
  'bold'=> 1
  ));
$seloptionFormat->setTextWrap();

$regularrightFormatNum->setNumFormat('_(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)');
$regularrightFormat1Num->setNumFormat('_(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)');
$boldrightFormatNum->setNumFormat('_(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)');
$grouplevel1totalFormat->setNumFormat('_(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)');


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);
}


$SelectedOptions = "Filter by: \n";
if ($budgetdate != '')
	$SelectedOptions .= "-> Budget Date From/To: $dispbudgetdate - $disptobudgetdate \n";
if ($fromdate != '')
	$SelectedOptions .= "-> Transaction From/To Date: $dispfromdate - $disptodate \n";
if ($fromitem != '')
	$SelectedOptions .= "-> From/To Item: $fromitem - $toitem \n";
if ($project_code != '')
	$SelectedOptions .= "-> Project: $project_code \n";
if ($costcent_code != '')
	$SelectedOptions .= "-> Costcenter: $costcent_code \n";
//Display Report Header
$worksheet->write(1,0,$CompanyName, $TitleFormat);
$worksheet->write(1,3,$ReportTitle, $TitleFormat);
               
$worksheet->write(1,7,"Printed Date:", $boldleftFormat);
$worksheet->write(1,8,$printdate[2]."/".$printdate[1]."/".$printdate[0], $regularleftFormat);
$worksheet->write(2,7,"Time:", $boldleftFormat);		
$worksheet->write(2,8,$dt[1]." ".$dt[2], $regularleftFormat);

//Displaying Selected Options	
// Merge cells from row 3, col 0 to row 3, col 0
$worksheet->setMerge(3, 0, 8, 6);
$worksheet->write(3,0,$SelectedOptions, $seloptionFormat);  

$RowNum    = 10;
$ColumnNum = 0;	   


$worksheet->setColumn ($ColumnNum, $ColumnNum, 40);
$worksheet->write($RowNum,$ColumnNum,"Item", $boldleftFormat);
$ColumnNum += 1;

$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
$worksheet->write($RowNum,$ColumnNum,"Start Date", $boldleftFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
$worksheet->write($RowNum,$ColumnNum,"End Date", $boldleftFormat);
$ColumnNum += 1;


if ($showMonth == 0 || ($showMonth == 1 && in_array('1', $months))){
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
	$worksheet->write($RowNum,$ColumnNum,"Jan ", $boldcenterFormat);
	$ColumnNum += 1;
	if ($showQtyExists == 1){
		$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
		$worksheet->write($RowNum,$ColumnNum,"Rem. 1 ", $boldcenterFormat);
		$ColumnNum += 1;
	}
}



if ($showMonth == 0 || ($showMonth == 1 && in_array('2', $months))){
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
	$worksheet->write($RowNum,$ColumnNum,"Feb ", $boldcenterFormat);
	$ColumnNum += 1;
	if ($showQtyExists == 1){
		$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
		$worksheet->write($RowNum,$ColumnNum,"Rem. 2 ", $boldcenterFormat);
		$ColumnNum += 1;
	}
}

if ($showMonth == 0 || ($showMonth == 1 && in_array('3', $months))){
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
	$worksheet->write($RowNum,$ColumnNum,"Mar ", $boldcenterFormat);
	$ColumnNum += 1;
	if ($showQtyExists == 1){
		$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
		$worksheet->write($RowNum,$ColumnNum,"Rem. 3 ", $boldcenterFormat);
		$ColumnNum += 1;
	}
}

if ($showMonth == 0 || ($showMonth == 1 && in_array('4', $months))){
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
	$worksheet->write($RowNum,$ColumnNum,"Apr ", $boldcenterFormat);
	$ColumnNum += 1;
	if ($showQtyExists == 1){
		$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
		$worksheet->write($RowNum,$ColumnNum,"Rem. 4 ", $boldcenterFormat);
		$ColumnNum += 1;
	}
}

if ($showMonth == 0 || ($showMonth == 1 && in_array('5', $months))){
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
	$worksheet->write($RowNum,$ColumnNum,"May ", $boldcenterFormat);
	$ColumnNum += 1;
	if ($showQtyExists == 1){
		$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
		$worksheet->write($RowNum,$ColumnNum,"Rem. 5 ", $boldcenterFormat);
		$ColumnNum += 1;
	}
}

if ($showMonth == 0 || ($showMonth == 1 && in_array('6', $months))){
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
	$worksheet->write($RowNum,$ColumnNum,"Jun ", $boldcenterFormat);
	$ColumnNum += 1;
	if ($showQtyExists == 1){
		$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
		$worksheet->write($RowNum,$ColumnNum,"Rem. 6 ", $boldcenterFormat);
		$ColumnNum += 1;
	}
}

if ($showMonth == 0 || ($showMonth == 1 && in_array('7', $months))){
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
	$worksheet->write($RowNum,$ColumnNum,"Jul ", $boldcenterFormat);
	$ColumnNum += 1;
	if ($showQtyExists == 1){
		$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
		$worksheet->write($RowNum,$ColumnNum,"Rem. 7 ", $boldcenterFormat);
		$ColumnNum += 1;
	}
}
if ($showMonth == 0 || ($showMonth == 1 && in_array('8', $months))){
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
	$worksheet->write($RowNum,$ColumnNum,"Aug ", $boldcenterFormat);
	$ColumnNum += 1;
	if ($showQtyExists == 1){
		$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
		$worksheet->write($RowNum,$ColumnNum,"Rem. 8 ", $boldcenterFormat);
		$ColumnNum += 1;
	}
}
if ($showMonth == 0 || ($showMonth == 1 && in_array('9', $months))){
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
	$worksheet->write($RowNum,$ColumnNum,"Sep ", $boldcenterFormat);
	$ColumnNum += 1;
	if ($showQtyExists == 1){
		$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
		$worksheet->write($RowNum,$ColumnNum,"Rem. 9 ", $boldcenterFormat);
		$ColumnNum += 1;
	}
}

if ($showMonth == 0 || ($showMonth == 1 && in_array('10', $months))){
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
	$worksheet->write($RowNum,$ColumnNum,"Oct ", $boldcenterFormat);
	$ColumnNum += 1;
	if ($showQtyExists == 1){
		$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
		$worksheet->write($RowNum,$ColumnNum,"Rem. 10 ", $boldcenterFormat);
		$ColumnNum += 1;
	}
}
if ($showMonth == 0 || ($showMonth == 1 && in_array('11', $months))){
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
	$worksheet->write($RowNum,$ColumnNum,"Nov ", $boldcenterFormat);
	$ColumnNum += 1;
	if ($showQtyExists == 1){
		$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
		$worksheet->write($RowNum,$ColumnNum,"Rem. 11 ", $boldcenterFormat);
		$ColumnNum += 1;
	}
}

if ($showMonth == 0 || ($showMonth == 1 && in_array('12', $months))){
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
	$worksheet->write($RowNum,$ColumnNum,"Dec ", $boldcenterFormat);
	$ColumnNum += 1;
	if ($showQtyExists == 1){
		$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
		$worksheet->write($RowNum,$ColumnNum,"Rem. 12 ", $boldcenterFormat);
		$ColumnNum += 1;
	}
}

$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
$worksheet->write($RowNum,$ColumnNum,"Total ", $boldcenterFormat);
$ColumnNum += 1;


 
$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;
$RowNum    = 12;
$ColumnNum = 0;


$result = mysql_query($query, $connection);
while($row = mysql_fetch_array($result)){
	extract($row);

	$totalQtyRow = 0;
	
	$worksheet->write($RowNum, $ColumnNum, $item_code." - ".$item_description, $regularleftFormat1);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $sdate, $regularleftFormat1);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $edate, $regularleftFormat1);
	$ColumnNum += 1;
	
	if ($showMonth == 0 || ($showMonth == 1 && in_array('1', $months))){	
		$worksheet->writeNumber($RowNum, $ColumnNum, $qty_month1, $regularcenterFormat);
		$ColumnNum += 1;
		if ($showQtyExists == 1){
			$rem1 = getRemainingQty($item_code, $qty_month1, '1', $connection);
			$worksheet->writeNumber($RowNum, $ColumnNum, $rem1, $regularcenterFormat);
			$ColumnNum += 1;
		}
		$totalQtyRow += $qty_month1;
		$totalmonth1 += $qty_month1;
	}
	
	if ($showMonth == 0 || ($showMonth == 1 && in_array('2', $months))){
		$worksheet->writeNumber($RowNum, $ColumnNum, $qty_month2, $regularcenterFormat);
		$ColumnNum += 1;
		if ($showQtyExists == 1){
			$rem2 = getRemainingQty($item_code, $qty_month2, '2', $connection);
			$worksheet->writeNumber($RowNum, $ColumnNum, $rem2, $regularcenterFormat);
			$ColumnNum += 1;
		}
		$totalQtyRow += $qty_month2;
		$totalmonth2 += $qty_month2;
	}
	
	if ($showMonth == 0 || ($showMonth == 1 && in_array('3', $months))){
		$worksheet->writeNumber($RowNum, $ColumnNum, $qty_month3, $regularcenterFormat);
		$ColumnNum += 1;
		if ($showQtyExists == 1){
			$rem3 = getRemainingQty($item_code, $qty_month3, '3', $connection);
			$worksheet->writeNumber($RowNum, $ColumnNum, $rem3, $regularcenterFormat);
			$ColumnNum += 1;
		}
		$totalQtyRow += $qty_month3;
		$totalmonth3 += $qty_month3;
	}
	
	if ($showMonth == 0 || ($showMonth == 1 && in_array('4', $months))){
		$worksheet->writeNumber($RowNum, $ColumnNum, $qty_month4, $regularcenterFormat);
		$ColumnNum += 1;
		if ($showQtyExists == 1){
			$rem4 = getRemainingQty($item_code, $qty_month4, '4', $connection);
			$worksheet->writeNumber($RowNum, $ColumnNum, $rem4, $regularcenterFormat);
			$ColumnNum += 1;
		}
		$totalQtyRow += $qty_month4;
		$totalmonth4 +=  $qty_month4;
	}

	if ($showMonth == 0 || ($showMonth == 1 && in_array('5', $months))){	
		$worksheet->writeNumber($RowNum, $ColumnNum, $qty_month5, $regularcenterFormat);
		$ColumnNum += 1;
		if ($showQtyExists == 1){
			$rem5 = getRemainingQty($item_code, $qty_month5, '5', $connection);
			$worksheet->writeNumber($RowNum, $ColumnNum, $rem5, $regularcenterFormat);
			$ColumnNum += 1;
		}
		$totalQtyRow += $qty_month5;
		$totalmonth5 += $qty_month5;
	}
	
	if ($showMonth == 0 || ($showMonth == 1 && in_array('6', $months))){
		$worksheet->writeNumber($RowNum, $ColumnNum, $qty_month6, $regularcenterFormat);
		$ColumnNum += 1;
		if ($showQtyExists == 1){
			$rem6 = getRemainingQty($item_code, $qty_month6, '6', $connection);
			$worksheet->writeNumber($RowNum, $ColumnNum, $rem6 , $regularcenterFormat);
			$ColumnNum += 1;
		}
		$totalQtyRow += $qty_month6;
		$totalmonth6 += $qty_month6;
	}
	
	if ($showMonth == 0 || ($showMonth == 1 && in_array('7', $months))){
		$worksheet->writeNumber($RowNum, $ColumnNum, $qty_month7, $regularcenterFormat);
		$ColumnNum += 1;
		if ($showQtyExists == 1){
			$rem7 = getRemainingQty($item_code, $qty_month7, '7', $connection);
			$worksheet->writeNumber($RowNum, $ColumnNum, $rem7, $regularcenterFormat);
			$ColumnNum += 1;
		}
		$totalQtyRow += $qty_month7;
		$totalmonth7 += $qty_month7;
	}
	
	if ($showMonth == 0 || ($showMonth == 1 && in_array('8', $months))){
		$worksheet->writeNumber($RowNum, $ColumnNum, $qty_month8, $regularcenterFormat);
		$ColumnNum += 1;
		if ($showQtyExists == 1){
			$rem8 = getRemainingQty($item_code, $qty_month8, '8', $connection);
			$worksheet->writeNumber($RowNum, $ColumnNum, $rem8, $regularcenterFormat);
			$ColumnNum += 1;
		}
		$totalQtyRow += $qty_month8;
		$totalmonth8 += $qty_month8;
	}

	if ($showMonth == 0 || ($showMonth == 1 && in_array('9', $months))){	
		$worksheet->writeNumber($RowNum, $ColumnNum, $qty_month9, $regularcenterFormat);
		$ColumnNum += 1;
		if ($showQtyExists == 1){
			$rem9 = getRemainingQty($item_code, $qty_month9, '9', $connection);
			$worksheet->writeNumber($RowNum, $ColumnNum, $rem9 , $regularcenterFormat);
			$ColumnNum += 1;
		}
		$totalQtyRow += $qty_month9;
		$totalmonth9 += $qty_month9;
	}
	
	if ($showMonth == 0 || ($showMonth == 1 && in_array('10', $months))){	
		$worksheet->writeNumber($RowNum, $ColumnNum, $qty_month10, $regularcenterFormat);
		$ColumnNum += 1;
		if ($showQtyExists == 1){
			$rem10 = getRemainingQty($item_code, $qty_month10, '10', $connection);
			$worksheet->writeNumber($RowNum, $ColumnNum, $rem10, $regularcenterFormat);
			$ColumnNum += 1;
		}
		$totalQtyRow += $qty_month10;
		$totalmonth10 += $qty_month10;
	}
	
	if ($showMonth == 0 || ($showMonth == 1 && in_array('11', $months))){
		$worksheet->writeNumber($RowNum, $ColumnNum, $qty_month11, $regularcenterFormat);
		$ColumnNum += 1;
		if ($showQtyExists == 1){
			$rem11 = getRemainingQty($item_code, $qty_month11, '11', $connection);
			$worksheet->writeNumber($RowNum, $ColumnNum, $rem11, $regularcenterFormat);
			$ColumnNum += 1;
		}
		$totalQtyRow += $qty_month11;
		$totalmonth11 += $qty_month11;
	}
	if ($showMonth == 0 || ($showMonth == 1 && in_array('12', $months))){
		$worksheet->writeNumber($RowNum, $ColumnNum, $qty_month12, $regularcenterFormat);
		$ColumnNum += 1;
		if ($showQtyExists == 1){
			$rem12 = getRemainingQty($item_code, $qty_month12, '12', $connection);
			$worksheet->writeNumber($RowNum, $ColumnNum, $rem12, $regularcenterFormat);
			$ColumnNum += 1;
		}
		$totalQtyRow += $qty_month12;
		$totalmonth12 += $qty_month12;
	}
	$worksheet->writeNumber($RowNum, $ColumnNum, $totalQtyRow, $regularcenterFormat);
	$ColumnNum += 1;
	$totalQty = $totalQty + $totalQtyRow;
	
	//Increment the Rows Counter By 1 and Reset the Columns Counter
	$RowNum += 1;
	$ColumnNum = 0;

}

$RowNum += 1;
$ColumnNum = 2;
$worksheet->write($RowNum, $ColumnNum, "Total", $boldcenterFormat);
$ColumnNum += 1;

if ($showMonth == 0 || ($showMonth == 1 && in_array('1', $months))){			
	$worksheet->writeNumber($RowNum, $ColumnNum, $totalmonth1, $boldcenterFormat);
	$ColumnNum += 1;
	if ($showQtyExists == 1)
		$ColumnNum += 1;
}
if ($showMonth == 0 || ($showMonth == 1 && in_array('2', $months))){		
	$worksheet->writeNumber($RowNum, $ColumnNum, $totalmonth2, $boldcenterFormat);
	$ColumnNum += 1;
	if ($showQtyExists == 1)
		$ColumnNum += 1;
}
if ($showMonth == 0 || ($showMonth == 1 && in_array('3', $months))){		
	$worksheet->writeNumber($RowNum, $ColumnNum, $totalmonth3, $boldcenterFormat);
	$ColumnNum += 1;
	if ($showQtyExists == 1)
		$ColumnNum += 1;
}
if ($showMonth == 0 || ($showMonth == 1 && in_array('4', $months))){		
	$worksheet->writeNumber($RowNum, $ColumnNum, $totalmonth4, $boldcenterFormat);
	$ColumnNum += 1;
	if ($showQtyExists == 1)
		$ColumnNum += 1;
}
if ($showMonth == 0 || ($showMonth == 1 && in_array('5', $months))){		
	$worksheet->writeNumber($RowNum, $ColumnNum, $totalmonth5, $boldcenterFormat);
	$ColumnNum += 1;
	if ($showQtyExists == 1)
		$ColumnNum += 1;
}
if ($showMonth == 0 || ($showMonth == 1 && in_array('6', $months))){	
	$worksheet->writeNumber($RowNum, $ColumnNum, $totalmonth6, $boldcenterFormat);
	$ColumnNum += 1;
	if ($showQtyExists == 1)
		$ColumnNum += 1;
}
if ($showMonth == 0 || ($showMonth == 1 && in_array('7', $months))){	
	$worksheet->writeNumber($RowNum, $ColumnNum, $totalmonth7, $boldcenterFormat);
	$ColumnNum += 1;
	if ($showQtyExists == 1)
		$ColumnNum += 1;
}
if ($showMonth == 0 || ($showMonth == 1 && in_array('8', $months))){	
	$worksheet->writeNumber($RowNum, $ColumnNum, $totalmonth8, $boldcenterFormat);
	$ColumnNum += 1;
	if ($showQtyExists == 1)
		$ColumnNum += 1;
}
if ($showMonth == 0 || ($showMonth == 1 && in_array('9', $months))){	
	$worksheet->writeNumber($RowNum, $ColumnNum, $totalmonth9, $boldcenterFormat);
	$ColumnNum += 1;
	if ($showQtyExists == 1)
		$ColumnNum += 1;
}
if ($showMonth == 0 || ($showMonth == 1 && in_array('10', $months))){	
	$worksheet->writeNumber($RowNum, $ColumnNum, $totalmonth10, $boldcenterFormat);
	$ColumnNum += 1;
	if ($showQtyExists == 1)
		$ColumnNum += 1;
}
if ($showMonth == 0 || ($showMonth == 1 && in_array('11', $months))){		
	$worksheet->writeNumber($RowNum, $ColumnNum, $totalmonth11, $boldcenterFormat);
	$ColumnNum += 1;
	if ($showQtyExists == 1)
		$ColumnNum += 1;
}
if ($showMonth == 0 || ($showMonth == 1 && in_array('12', $months))){	
	$worksheet->writeNumber($RowNum, $ColumnNum, $totalmonth12, $boldcenterFormat);
	$ColumnNum += 1;
	if ($showQtyExists == 1)
		$ColumnNum += 1;
}	
$worksheet->writeNumber($RowNum, $ColumnNum, $totalQty, $boldcenterFormat);
	$ColumnNum += 1;
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 GetSQLValueString($theValue, $theType) 
{
  $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "Null";
      break;    
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "Null";
      break;
    case "double":
      $theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "Null";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "Null";
      break;
  }
  return $theValue;
}



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;
	
}
$workbook->close();


?>