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

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


$ReportTitle = 'Ticket Visit';

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

//Sending HTTP headers
$workbook->setVersion(8);

$workbook->send('Exp_TicketVisit.xls');
 
//Creating a worksheet
$worksheet=&$workbook->addWorksheet('Ticket Visit');
$worksheet->setLandscape();
 
$worksheet->setInputEncoding('utf-8'); 
//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);_(* "-"??_);_(@_)');

$PProjectCode 	= $_GET['PProjectCode'];
$fromdate 		= $_GET['fromdate'];
$todate			= $_GET['todate'];
$tvType			= $_GET['tvType'];
$tsSupTeamId 	= $_GET['tsSupTeamId'];
$tvClosed		= $_GET['tvClosed'];

if ($tsSupTeamId != ''){
	$queryTeam 	 = "select username from user_support where user_id = $tsSupTeamId";
	$resTeam 	 = mysql_query($queryTeam, $connection);
	$rowTeam 	 = mysql_fetch_row($resTeam);
	$Team		 = $rowTeam[0];
	
}

if ($ticketVisit_type == 1)	$visitType = 'Maintenance';
else if ($ticketVisit_type == 2) $visitType = 'Ticket'; 
else $visitType = '';

if ($tvClosed == '1') $isClosed = 'Closed';
else if ($tvClosed == '2') $isClosed = 'Not Closed';
else $isClosed = '';

$SelectedOptions = "Filter by: \n";

$SelectedOptions = "Filter by: \n";
if ($fromdate != '')      		$SelectedOptions .= "-> Date From: ". $fromdate . "  To: ". $todate ."\n";	
if ($PProjectCode != '')  		$SelectedOptions .= "-> Branch: ". $PProjectCode ."\n";	
if ($ticketVisit_type != '')  	$SelectedOptions .= "-> Visit Type: ". $visitType."\n";
if ($tsSupTeamId != '')   		$SelectedOptions .= "-> Support Team: ". $Team ."\n";
if ($tvClosed != '')   	  		$SelectedOptions .= "-> Visit Status: ". $isClosed ."\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;	   

//header1
		 
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
	$worksheet->write($RowNum,$ColumnNum,'Date/Time', $boldleftFormat);
	$ColumnNum += 1;
	
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 20);
	$worksheet->write($RowNum,$ColumnNum,"Branch", $boldleftFormat);
	$ColumnNum += 1;
	
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 20);
	$worksheet->write($RowNum,$ColumnNum,"Visit Type", $boldleftFormat);
	$ColumnNum += 1;
	
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 12);
	$worksheet->write($RowNum,$ColumnNum,"Support Team", $boldleftFormat);
	$ColumnNum += 1;
	
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 12);
	$worksheet->write($RowNum,$ColumnNum,"Status", $boldleftFormat);
	$ColumnNum += 1;
	
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 8);
	$worksheet->write($RowNum,$ColumnNum,"Closed", $boldleftFormat);
	$ColumnNum += 1;
	
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 8);
	$worksheet->write($RowNum,$ColumnNum,"Approved", $boldleftFormat);
	$ColumnNum += 1;
	
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 20);
	$worksheet->write($RowNum,$ColumnNum,"Contact Person", $boldleftFormat);
	$ColumnNum += 1;
	
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 40);
	$worksheet->write($RowNum,$ColumnNum,"Remark", $boldleftFormat);
	$ColumnNum += 1;
	
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 10);
	$worksheet->write($RowNum,$ColumnNum,"User Account", $boldleftFormat);
	$ColumnNum += 1;
	
	

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

$fromdate	   = GetSQLValueString($fromdate, 'date');
$todate        = GetSQLValueString($todate, 'date');

 $querysearch = "select date_format(ticketVisit_date, '%d/%m/%Y %h:%i %p') as VisitDate, project_code, ticketVisit_type, username as TeamName, ticketVisit_status,
				if(ticketVisit_closed='1','✔','✖') as ticketClosed, if(ticketVisit_approved='1','✔','✖') as ticketApproved, ticketVisit_contactPerson,
				ticketVisit_remark, user_account
				from ticket_visit
				left join user_support on user_id = ticketVisit_userId
			where date_format(ticketVisit_date,'%Y-%m-%d') between $fromdate and $todate
				and if('$PProjectCode'='', 1=1, project_code = '$PProjectCode') 
				and if('$tvType' = '', 1=1, ticketVisit_type = '$tvType')	
				and if('$tsSupTeamId' = '', 1=1, ticketVisit_userId = '$tsSupTeamId')	
				and if('$tvClosed' = '2', ifnull(ticketVisit_closed,0) = 0, if('$tvClosed' = '1', ifnull(ticketVisit_closed,0) = 1, 1=1))	
			order by date_format(ticketVisit_date,'%Y-%m-%d %H:%i') desc  ";
	
$result = mysql_query($querysearch, $connection);

$ColumnNum = 0;
$RowNum += 1;


while($rows = mysql_fetch_array($result)){
	extract($rows);
	
	if ($ticketVisit_type == 1)	$visitType = 'Maintenance';
	else if ($ticketVisit_type == 2) $visitType = 'Ticket'; 
	else $visitType = '';
	
	$worksheet->write($RowNum, $ColumnNum, $VisitDate, $regularleftFormat);
	$ColumnNum += 1;
	
	$worksheet->write($RowNum, $ColumnNum, $project_code, $regularleftFormat);
	$ColumnNum += 1;
	
	$worksheet->write($RowNum, $ColumnNum, $visitType, $regularleftFormat);
	$ColumnNum += 1;
	
	$worksheet->write($RowNum, $ColumnNum, $TeamName, $regularleftFormat);
	$ColumnNum += 1;
	
	$worksheet->write($RowNum, $ColumnNum, $ticketVisit_status, $regularleftFormat);
	$ColumnNum += 1;
	
	$worksheet->write($RowNum, $ColumnNum, $ticketClosed, $regularcenterFormat);
	$ColumnNum += 1;
	
	$worksheet->write($RowNum, $ColumnNum, $ticketApproved, $regularcenterFormat);
	$ColumnNum += 1;
	
	$worksheet->write($RowNum, $ColumnNum, $ticketVisit_contactPerson, $regularleftFormat);
	$ColumnNum += 1;
	
	$worksheet->write($RowNum, $ColumnNum, $ticketVisit_remark, $regularleftFormat);
	$ColumnNum += 1;
	
	$worksheet->write($RowNum, $ColumnNum, $user_account, $regularleftFormat);
	$ColumnNum += 1;
	
			
	$ColumnNum = 0;	
	$RowNum += 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;
}


$workbook->close();


?>