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

session_start();
if($HTTP_SESSION_VARS['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($HTTP_SESSION_VARS['dbName'],$connection) or die("Could not find DB");


$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 ".$HTTP_SESSION_VARS['dbLabel'].".loginstk on project.project_code like concat(ifnull(loginstk.project_code,project.project_code),'%') ".
			  " where user_account='".$HTTP_SESSION_VARS['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]);

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

//Sending HTTP headers
$workbook->send('Exp_ListofAdjustment.xls');
 
//Creating a worksheet
$worksheet=&$workbook->addWorksheet('List of Adjustment');
$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'  ));    
  
$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
  ));    
  

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

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

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

$SelectedOptions = "Filter by: \n";
if ($fromdate != '')      $SelectedOptions .= "-> Date From: ". $fromdate . "  To: ". $todate ."\n";	
if ($displaycur != '')    $SelectedOptions .= "-> Currency: ". $displaycur ."\n";	
if ($fromref != '')    	  $SelectedOptions .= "-> Reference From: ". $fromref . "  To: ". $toref ."\n";
if ($fromproject != '')   $SelectedOptions .= "-> Project: ". $fromproject . "\n";



if (empty($fromref)) $fromref = 'null'; 
if (empty($toref))   $toref = 'null';   
 
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;



//Display Report Header
$worksheet->write(1,0,$CompanyName, $TitleFormat);
$worksheet->write(1,3,"List of Adjustment", $TitleFormat);
               
$worksheet->write(1,4,"Printed Date:", $boldleftFormat);
$worksheet->write(1,5,$printdate[2]."/".$printdate[1]."/".$printdate[0], $regularleftFormat);
$worksheet->write(2,4,"Time:", $boldleftFormat);		
$worksheet->write(2,5,$dt[1]." ".$dt[2], $regularleftFormat);
  
//Displaying Selected Options	
// Merge cells from row 3, col 0 to row 8, col 5
$worksheet->setMerge(3, 0, 8, 5);
$worksheet->write(3,0,$SelectedOptions, $seloptionFormat);  


$RowNum    = 10;
$ColumnNum = 0;

$worksheet->setColumn ($ColumnNum, $ColumnNum, 12);
$worksheet->write($RowNum,$ColumnNum,"Date", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 12);
$worksheet->write($RowNum,$ColumnNum,"Ref.", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 12);
$worksheet->write($RowNum,$ColumnNum,"Folio", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 60);
$worksheet->write($RowNum,$ColumnNum,"Remark", $boldleftFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 10);
$worksheet->write($RowNum,$ColumnNum,"Cur.", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 20);
$worksheet->write($RowNum,$ColumnNum,"Amount ".$displaycur, $boldcenterFormat);
$ColumnNum += 1;
 

if ($showdetail == 1) {
	$RowNum   += 1;
	$ColumnNum = 1;
	
	$worksheet->write($RowNum,$ColumnNum,"Item Code ", $boldleftFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum,$ColumnNum,"Item Description ", $boldleftFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum,$ColumnNum,"Warehouse ", $boldcenterFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum,$ColumnNum,"Qty. ", $boldcenterFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum,$ColumnNum,"Unit Price ", $boldcenterFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum,$ColumnNum,"Total ", $boldcenterFormat);
	$ColumnNum += 1;
	
}	


   
$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 ".$HTTP_SESSION_VARS['dbLabel'].".loginstk ".
					"			  where loginstk.user_account='".$HTTP_SESSION_VARS['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 ";
				

//echo $query;
				
$SumTotalAmount = 0;
$SumGroupAmount = 0;
$MasterProject  = '';
	
$RowNum    = 13;
$ColumnNum = 0;
	


$result=mysql_query($query,$connection) or die(mysql_error());
while($rows=mysql_fetch_row($result)){
	
	if (($grpproject == 1) && ($MasterProject == '' || $MasterProject != $rows[6])) {
	  
		if (($cur != 'null') && ($MasterProject != '' && $MasterProject != $rows[6])){
			$worksheet->setMerge($RowNum, $ColumnNum, $RowNum, $ColumnNum + 4);
			$worksheet->write($RowNum,$ColumnNum,"Total by Project ".$displaycur, $boldrightFormat);
			$ColumnNum += 5;
			
			$worksheet->writeNumber($RowNum, $ColumnNum, $SumGroupAmount, $boldrightFormatNum);
			$ColumnNum += 1;
			
			//Increment the Rows Counter By 1 and Reset the Columns Counter
			$RowNum += 2;
			$ColumnNum = 0;
		
			$SumGroupAmount = 0;
		}	
		  
		$worksheet->write($RowNum, $ColumnNum, "Group by Project ".$rows[6]." - ".$rows[7], $boldleftFormat);
		$ColumnNum += 1;
			
		//Increment the Rows Counter By 1 and Reset the Columns Counter
		$RowNum += 1;
		$ColumnNum = 0;
		
		$MasterProject = $rows[6];
	
	}
	
	if ($showdetail == 1) {
		$ClassLeft   = $boldleftFormat;
		$ClassRight  = $boldrightFormatNum;
		$ClassCenter = $boldcenterFormat;
	} else {
		$ClassLeft   = $regularleftFormat;
		$ClassRight  = $regularrightFormatNum;
		$ClassCenter = $regularcenterFormat;
	}
	
	$worksheet->write($RowNum, $ColumnNum, $rows[1], $ClassLeft);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $rows[2], $ClassLeft);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $rows[3], $ClassLeft);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $rows[5], $ClassLeft);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $rows[9], $ClassCenter);
	$ColumnNum += 1;
	$worksheet->writeNumber($RowNum, $ColumnNum, $rows[10], $ClassRight);
	$ColumnNum += 1;
		
		
	$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());
		while($rowsdt=mysql_fetch_row($resultdt)){
			
			$RowNum += 1;
			$ColumnNum = 1;
			
			$worksheet->write($RowNum, $ColumnNum, $rowsdt[0], $regularleftFormat);
			$ColumnNum += 1;
			$worksheet->write($RowNum, $ColumnNum, $rowsdt[1], $regularleftFormat);
			$ColumnNum += 1;
			$worksheet->write($RowNum, $ColumnNum, $rowsdt[2], $regularleftFormat);
			$ColumnNum += 1;
			$worksheet->writeNumber($RowNum, $ColumnNum, $rowsdt[3], $regularrightFormatNum);
			$ColumnNum += 1;
			$worksheet->writeNumber($RowNum, $ColumnNum, $rowsdt[4], $regularrightFormatNum);
			$ColumnNum += 1;
			$worksheet->writeNumber($RowNum, $ColumnNum, $rowsdt[5], $regularrightFormatNum);
			$ColumnNum += 1;
			
		}
	}	
	
	
	//Increment the Rows Counter By 1 and Reset the Columns Counter
	$RowNum += 1;
	$ColumnNum = 0;

}

if (($grpproject == 1) && ($MasterProject != '')) {
	  
	if (($cur != 'null') && ($MasterProject != '' && $MasterProject != $rows[6])){
		$worksheet->setMerge($RowNum, $ColumnNum, $RowNum, $ColumnNum + 4);
		$worksheet->write($RowNum,$ColumnNum,"Total by Project ".$displaycur, $boldrightFormat);
		$ColumnNum += 5;
			
		$worksheet->writeNumber($RowNum, $ColumnNum, $SumGroupAmount, $boldrightFormatNum);
		$ColumnNum += 1;
			
		//Increment the Rows Counter By 1 and Reset the Columns Counter
		$RowNum += 2;
		$ColumnNum = 0;
		
		$SumGroupAmount = 0;
	}	
	
}

	
if ($cur != 'null'){
	$worksheet->setMerge($RowNum, $ColumnNum, $RowNum, $ColumnNum + 4);
	$worksheet->write($RowNum,$ColumnNum,"Total ".$displaycur, $boldrightFormat);
	$ColumnNum += 5;
			
	$worksheet->writeNumber($RowNum, $ColumnNum, $SumTotalAmount, $boldrightFormatNum);
	$ColumnNum += 1;
			
	//Increment the Rows Counter By 1 and Reset the Columns Counter
	$RowNum += 2;
	$ColumnNum = 0;
}



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

$workbook->close();

?>