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


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

	
//Creating a workbook
$workbook = new Spreadsheet_Excel_Writer();

//Sending HTTP headers
$workbook->send('Exp_MonthlyQuantityBudget.xls');
 
//Creating a worksheet
$worksheet=&$workbook->addWorksheet('Monthly Distribution');
$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 ($todate == '') $todate = $fromdate;

$dispfromdate = $fromdate;
$disptodate = $todate;


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


$SelectedOptions = "Filter by: \n";

if ($fromdate != '')
	$SelectedOptions .= "-> Transaction From/To Date: $dispfromdate - $disptodate \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, 20);
$worksheet->write($RowNum,$ColumnNum,"Item", $boldleftFormat);
$ColumnNum += 1;

$worksheet->setColumn ($ColumnNum, $ColumnNum, 30);
$worksheet->write($RowNum,$ColumnNum,"Description", $boldleftFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 8);
$worksheet->write($RowNum,$ColumnNum,"Quantity", $boldleftFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 8);
$worksheet->write($RowNum,$ColumnNum,"Unit", $boldleftFormat);
$ColumnNum += 1;

$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
$worksheet->write($RowNum,$ColumnNum,"Previous Received", $boldleftFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
$worksheet->write($RowNum,$ColumnNum,"Monthly Received", $boldleftFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
$worksheet->write($RowNum,$ColumnNum,"Total Received", $boldleftFormat);
$ColumnNum += 1;

$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
$worksheet->write($RowNum,$ColumnNum,"Previous issued on the project", $boldleftFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
$worksheet->write($RowNum,$ColumnNum,"Previous issued to other project", $boldleftFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
$worksheet->write($RowNum,$ColumnNum,"Monthly issued on the project", $boldleftFormat);
$ColumnNum += 1;

$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
$worksheet->write($RowNum,$ColumnNum,"Monthly issued to other project", $boldleftFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
$worksheet->write($RowNum,$ColumnNum,"Total issued on project", $boldleftFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
$worksheet->write($RowNum,$ColumnNum,"Total issued to other project", $boldleftFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
$worksheet->write($RowNum,$ColumnNum,"Total issued", $boldleftFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
$worksheet->write($RowNum,$ColumnNum,"Monthly Balance", $boldleftFormat);
$ColumnNum += 1; 


$query = "select budget_qtydt.item_code as item_code, item_description, Item_UnitSalesDesc, budget_qtyhd.project_code as project_code, qty
			 from budget_qtydt 
			 inner join items on items.item_code = budget_qtydt.item_code
			 inner join budget_qtyhd on budget_qtyhd.BQ_Id = budget_qtydt.BQ_Id
			 where  budget_qtyhd.project_code = '$project_code'
			 order by item_code ";

$RowNum    = 12;
$ColumnNum = 0;


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

	$queryPrevRecvd = "select ifnull(sum(QuantityEntered),0) from stktransaction where item_code = '$item_code' and project_code = '$project_code' and date_format(Transaction_Date,'%Y-%m-%d') < '$fromdate' 
						and Transaction_Type in ('TRT', 'AI', 'PI');"; //echo $queryPrevRecvd;
	$resPrevRcvd  	= mysql_query($queryPrevRecvd, $connection);
	$rowPrevRecvd 	= mysql_fetch_row($resPrevRcvd);
	$PrevRecvdQty 	= $rowPrevRecvd[0];
	
	$queryMonRecvd 	 = "select ifnull(sum(QuantityEntered),0) from stktransaction where item_code = '$item_code' and if('$project_code'='',1=1, project_code = '$project_code') 
						and Transaction_Type in ('TRT', 'AI', 'PI') and date_format(Transaction_Date,'%Y-%m-%d') between '$fromdate' and '$todate' ;";
	$resMonRcvd  	 = mysql_query($queryMonRecvd, $connection);
	$rowMonRecvd 	 = mysql_fetch_row($resMonRcvd);
	$MonthlyRecvdQty = $rowMonRecvd[0];
	
	$totalRecvd = $PrevRecvdQty + $MonthlyRecvdQty;
	
	$queryPrevIssued = "select ifnull(sum(abs(QuantityEntered)),0), ifnull(sum(QuantityDelivered),0) from stktransaction 
						where item_code = '$item_code' and project_code = '$project_code' and date_format(Transaction_Date,'%Y-%m-%d') < '$fromdate' and Transaction_Type in ('AR', 'PR');";
	$resPrevIssued  	= mysql_query($queryPrevIssued, $connection);
	$rowPrevIssued 	= mysql_fetch_row($resPrevIssued);
	$PrevIssuedQty 	= $rowPrevIssued[0]+ $rowPrevIssued[1];
	
	$queryPrevIssOth = "select ifnull(sum(abs(QuantityEntered)),0), ifnull(sum(QuantityDelivered),0) from stktransaction 
						where item_code = '$item_code' and project_code = '$project_code' and date_format(Transaction_Date,'%Y-%m-%d') < '$fromdate' and Transaction_Type in ('TR');";
	$resPrevIssOth  	= mysql_query($queryPrevIssOth, $connection);
	$rowPrevIssOth 	= mysql_fetch_row($resPrevIssOth);
	$PrevIssOthQty 	= $rowPrevIssOth[0]+ $rowPrevIssOth[1];
	
	$queryMonIssued 	 = "select ifnull(sum(abs(QuantityEntered)),0), ifnull(sum(QuantityDelivered),0)
						from stktransaction where item_code = '$item_code' and project_code = '$project_code' and Transaction_Type in ('AR', 'PR')
						and date_format(Transaction_Date,'%Y-%m-%d') between '$fromdate' and '$todate' ;";
	$resMonIssued  	 = mysql_query($queryMonIssued, $connection);
	$rowMonIssued 	 = mysql_fetch_row($resMonIssued);
	$MonthlyIssuedQty = $rowMonIssued[0] + $rowMonIssued[1];
	
	$queryMonIssOth	 = "select ifnull(sum(abs(QuantityEntered)),0), ifnull(sum(QuantityDelivered),0)
						from stktransaction where item_code = '$item_code' and project_code = '$project_code' and Transaction_Type in ('TR')
						and date_format(Transaction_Date,'%Y-%m-%d') between '$fromdate' and '$todate' ;";
	$resMonIssOth  	 = mysql_query($queryMonIssOth, $connection);
	$rowMonIssOth 	 = mysql_fetch_row($resMonIssOth);
	$MonthlyIssOthQty = $rowMonIssOth[0] + $rowMonIssOth[1];
	
	$totalIssuedProject = $MonthlyIssuedQty + $PrevIssuedQty;
	$totalIssuedOthers  = $MonthlyIssOthQty + $PrevIssOthQty;
	
	$totalIssued = $totalIssuedProject + $totalIssuedOthers;
	
	$MonthlyBalance = $MonthlyRecvdQty- $totalIssuedProject;
	
	$worksheet->write($RowNum, $ColumnNum, $item_code, $regularleftFormat1);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $item_description, $regularleftFormat1);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $qty, $regularleftFormat1);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $Item_UnitSalesDesc, $regularcenterFormat1);
	$ColumnNum += 1;
	
		
	$worksheet->writeNumber($RowNum, $ColumnNum, $PrevRecvdQty, $regularrightFormat);
	$ColumnNum += 1;
	$worksheet->writeNumber($RowNum, $ColumnNum, $MonthlyRecvdQty, $regularrightFormat);
	$ColumnNum += 1;
	$worksheet->writeNumber($RowNum, $ColumnNum, $totalRecvd, $regularrightFormat);
	$ColumnNum += 1;
	$worksheet->writeNumber($RowNum, $ColumnNum, $PrevIssuedQty, $regularrightFormat);
	$ColumnNum += 1;
	
	$worksheet->writeNumber($RowNum, $ColumnNum, $PrevIssOthQty, $regularrightFormat);
	$ColumnNum += 1;
	$worksheet->writeNumber($RowNum, $ColumnNum, $MonthlyIssuedQty, $regularrightFormat);
	$ColumnNum += 1;
	$worksheet->writeNumber($RowNum, $ColumnNum, $MonthlyIssOthQty, $regularrightFormat);
	$ColumnNum += 1;
	$worksheet->writeNumber($RowNum, $ColumnNum, $totalIssuedProject, $regularrightFormat);
	$ColumnNum += 1;
	$worksheet->writeNumber($RowNum, $ColumnNum, $totalIssuedOthers, $regularrightFormat);
	$ColumnNum += 1;
	$worksheet->writeNumber($RowNum, $ColumnNum, $totalIssued, $regularrightFormat);
	$ColumnNum += 1;
	$worksheet->writeNumber($RowNum, $ColumnNum, $MonthlyBalance, $regularrightFormat);
	$ColumnNum += 1;
		
	//Increment the Rows Counter By 1 and Reset the Columns Counter
	$RowNum += 1;
	$ColumnNum = 0;

}

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



$workbook->close();


?>