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

$querylog=" select ifNull(FI_RepRestrictProject,0) As FI_RepRestrictProject, ifNull(FI_RepRestrictCostcenter,0) As FI_RepRestrictCostcenter, ".
		  " ifNull(FI_RepRestrictWarehouse,0) As FI_RepRestrictWarehouse ".
          " from ".$HTTP_SESSION_VARS['dbLabel'].".login where user_account='".$HTTP_SESSION_VARS['useraccount']."' and Upper(Login_Schema) = Upper('".$HTTP_SESSION_VARS['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 ".$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);

$querycostcent=" select distinct costcent.costcent_code, costcent_name ".
               " from costcent left join ".$HTTP_SESSION_VARS['dbLabel'].".loginstk on costcent.costcent_code like concat(ifnull(loginstk.costcent_code,costcent.costcent_code),'%') ".
			   " where user_account='".$HTTP_SESSION_VARS['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 ".$HTTP_SESSION_VARS['dbLabel'].".loginstk where user_account='".$HTTP_SESSION_VARS['useraccount']."') ".
		 " order by warehouse.warehouse_code";
$resultwh = mysql_query($querywh,$connection) or die(mysql_error());
$whexist   = mysql_num_rows($resultwh);
//echo $querywh;

$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_ListofTransfer.xls');
 
//Creating a worksheet
$worksheet=&$workbook->addWorksheet('List of Transfer');
$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'  )); 
$regularcenterFormat =& $workbook->addFormat(array('size'=>9,
  'align'=>'center'  ));    
  
$regularleftFormat1 =& $workbook->addFormat(array('size'=>8,
  'align'=>'left'  ));
$regularrightFormat1 =& $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
  )); 
$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();

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 From: ". $fromproject . "\n";



if (!empty($fromdate))   $fromdate = explodeword($fromdate);
if (!empty($todate))     $todate = explodeword($todate);  


$fromref      = GetSQLValueString($fromref, 'text');
$toref        = GetSQLValueString($toref, 'text');
$fromproject  = GetSQLValueString($fromproject, 'text');
$fromdate	  = GetSQLValueString($fromdate, 'date');
$todate       = GetSQLValueString($todate, 'date');   


//Display Report Header
$worksheet->write(1,0,$CompanyName, $TitleFormat);
$worksheet->write(1,4,"List of Transfer", $TitleFormat);
               
$worksheet->write(1,6,"Printed Date:", $boldleftFormat);
$worksheet->write(1,7,$printdate[2]."/".$printdate[1]."/".$printdate[0], $regularleftFormat);
$worksheet->write(2,6,"Time:", $boldleftFormat);		
$worksheet->write(2,7,$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, 15);
$worksheet->write($RowNum,$ColumnNum,"Date", $boldleftFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 10);
$worksheet->write($RowNum,$ColumnNum,"Ref.", $boldleftFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
$worksheet->write($RowNum,$ColumnNum,"Costcenter", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
$worksheet->write($RowNum,$ColumnNum,"Project From", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
$worksheet->write($RowNum,$ColumnNum,"To", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
$worksheet->write($RowNum,$ColumnNum,"Wh. From", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
$worksheet->write($RowNum,$ColumnNum,"To", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 50);
$worksheet->write($RowNum,$ColumnNum,"Remark", $boldcenterFormat);
$ColumnNum += 1;  


if ($showdetail == 1) {
	$RowNum += 1;
	$ColumnNum = 0;
	
	$worksheet->setMerge($RowNum, $ColumnNum, $RowNum, $ColumnNum + 3);
	$worksheet->write($RowNum,$ColumnNum,"Item Code/Description", $boldleftFormat);
	$ColumnNum += 4;
	$worksheet->write($RowNum,$ColumnNum,"Wh.From", $boldcenterFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum,$ColumnNum,"Wh.To", $boldcenterFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum,$ColumnNum,"Qty.", $boldcenterFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum,$ColumnNum,"Remark", $boldcenterFormat);
	$ColumnNum += 1;
		
}	
    

   
$querytransfer= " select Transfer_Id, date_format(Transfer_Date,'%d/%m/%Y') Transfer_Date, Transfer_Reference, Transfer_Remark,  ".
                " Costcenter_Code, Project_code1 , Project_code2, Transfer_WarehouseFrom, Transfer_WarehouseTo, ".
				" if('".$date."' = 'date', date_format(Transfer_Date,'%Y-%m-%d'), Transfer_Reference) ord ".
 				" from Transfer ";
				
if ($projectexist >	0)
  //$querytransfer= $querytransfer." left join ".$HTTP_SESSION_VARS['dbLabel'].".loginstk on Transfer.Project_code1 like concat(loginstk.project_code,'%') ";
  $querytransfer= $querytransfer." left join ( select distinct project_code from ".$HTTP_SESSION_VARS['dbLabel'].".loginstk ".
							   	 "			   where loginstk.user_account='".$HTTP_SESSION_VARS['useraccount']."' ".
								 "             and (project_code is not null and project_code != '') ".
								 "			 ) Q1 on Transfer.Project_code1 like concat(Q1.project_code,'%') ";

$querytransfer= $querytransfer.  				
				" where Transfer_Reference between ifNull(". $fromref .",Transfer_Reference) and ifNull(". $toref.",Transfer_Reference) ".
				" and date_format(Transfer_Date,'%Y-%m-%d') between ifNull(". $fromdate.",date_format(Transfer_Date,'%Y-%m-%d')) and ifNull(". $todate.",date_format(Transfer_Date,'%Y-%m-%d'))  ";

if ($fromproject != '')				
	$querytransfer= $querytransfer." and (upper(Transfer.Project_code1) like Concat(upper(ifNull(".$fromproject.", Transfer.Project_code1)),'%') Or upper(Transfer.Project_code2) like Concat(upper(ifNull(".$fromproject.", Transfer.Project_code2)),'%') ) ";
								

//if ($projectexist >	0)					  
  //$querytransfer= $querytransfer." and loginstk.user_account='".$HTTP_SESSION_VARS['useraccount']."' ";
  
if ($whexist >	0)
  $querytransfer= $querytransfer." and ( Transfer_WarehouseFrom In (select distinct warehouse_code from ".$HTTP_SESSION_VARS['dbLabel'].".loginstk where user_account='".$HTTP_SESSION_VARS['useraccount']."') or  ".
								 "       Transfer_WarehouseTo In (select distinct warehouse_code from ".$HTTP_SESSION_VARS['dbLabel'].".loginstk where user_account='".$HTTP_SESSION_VARS['useraccount']."')  or ".
								 "       Transfer_WarehouseFrom Is Null or Transfer_WarehouseTo Is Null )  ";
  
$querytransfer= $querytransfer.  
				" order by if($grpproject=1, Transfer.Project_code1, null ), ord, Transfer_Reference ";
				
	
//echo $querytransfer;

$RowNum    = 13;
$ColumnNum = 0;

$resulttransfer=mysql_query($querytransfer,$connection) or die(mysql_error());
while($rowtransfer=mysql_fetch_row($resulttransfer)){

    
	$querytransferdt= " select transferdt.Item_code, items.item_description, TransferDt_WarehouseFrom, TransferDt_WarehouseTo, ".
	                  " TransferDt_Quantity, Transferdt_remark ".
					  " from transferdt ".
					  " left join items on transferdt.Item_code = items.item_code ".
					  " where transferdt.Transfer_Id = ". $rowtransfer[0];
    if ($whexist >	0)
      $querytransferdt= $querytransferdt." and ( TransferDt_WarehouseFrom In (select distinct warehouse_code from ".$HTTP_SESSION_VARS['dbLabel'].".loginstk where user_account='".$HTTP_SESSION_VARS['useraccount']."') or ".
                                         "       TransferDt_WarehouseTo In (select distinct warehouse_code from ".$HTTP_SESSION_VARS['dbLabel'].".loginstk where user_account='".$HTTP_SESSION_VARS['useraccount']."') or  ".
										 "       TransferDt_WarehouseFrom Is Null or  TransferDt_WarehouseTo Is Null ) ";
					

	//echo $querytransferdt;
	
		
	$worksheet->write($RowNum, $ColumnNum, $rowtransfer[1], $regularleftFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $rowtransfer[2], $regularleftFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $rowtransfer[4], $regularleftFormat);
	$ColumnNum += 1;
	$worksheet->writestring($RowNum, $ColumnNum, $rowtransfer[5], $regularleftFormat);
	$ColumnNum += 1;
	$worksheet->writestring($RowNum, $ColumnNum, $rowtransfer[6], $regularcenterFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $rowtransfer[7], $regularcenterFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $rowtransfer[8], $regularcenterFormat);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $rowtransfer[3], $regularcenterFormat);
	$ColumnNum += 1;
	
	

	if ($showdetail == 1) {
		$resulttransferdt=mysql_query($querytransferdt,$connection) or die(mysql_error());
		while($rowtransferdt=mysql_fetch_row($resulttransferdt)){
		
			$RowNum += 1;
			$ColumnNum = 0;
		
			$worksheet->write($RowNum, $ColumnNum, $rowtransferdt[0], $regularleftFormat1);
			$ColumnNum += 1;
			//$worksheet->setMerge($RowNum, $ColumnNum, $RowNum, $ColumnNum + 1);
			$worksheet->write($RowNum, $ColumnNum, $rowtransferdt[1], $regularleftFormat1);
			$ColumnNum += 3;
			$worksheet->write($RowNum, $ColumnNum, $rowtransferdt[2], $regularleftFormat1);
			$ColumnNum += 1;
			$worksheet->write($RowNum, $ColumnNum, $rowtransferdt[3], $regularleftFormat1);
			$ColumnNum += 1;
			$worksheet->write($RowNum, $ColumnNum, $rowtransferdt[4], $regularrightFormat1);
			$ColumnNum += 1;
			$worksheet->write($RowNum, $ColumnNum, $rowtransferdt[5], $regularleftFormat1);
			$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();

?>