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

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

//Sending HTTP headers
$workbook->send('Exp_Ticketing.xls');
 
//Creating a worksheet
$worksheet=&$workbook->addWorksheet('Report of Ticketing');
$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);_(* "-"??_);_(@_)');


$RowNum    = 1;
$ColumnNum = 0;	   

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

$worksheet->setColumn ($ColumnNum, $ColumnNum, 10);
$worksheet->write($RowNum,$ColumnNum,"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,"Problem Type", $boldleftFormat);
$ColumnNum += 1;

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

$worksheet->setColumn ($ColumnNum, $ColumnNum, 10);
$worksheet->write($RowNum,$ColumnNum,"Max Date", $boldleftFormat);
$ColumnNum += 1;

$worksheet->setColumn ($ColumnNum, $ColumnNum, 10);
$worksheet->write($RowNum,$ColumnNum,"Max Time", $boldleftFormat);
$ColumnNum += 1;

$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
$worksheet->write($RowNum,$ColumnNum,"Support Team", $boldleftFormat);
$ColumnNum += 1;

$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
$worksheet->write($RowNum,$ColumnNum,"Counter No", $boldleftFormat);
$ColumnNum += 1;

$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
$worksheet->write($RowNum,$ColumnNum,"SLA Hour", $boldleftFormat);
$ColumnNum += 1;

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

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

$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
$worksheet->write($RowNum,$ColumnNum,"Consider the Time now", $boldleftFormat);
$ColumnNum += 1;

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

$RowNum    = 2;
$ColumnNum = 0;


$query = "select date_format(ticketSupport_date,'%d/%m/%Y') as ticketDate, date_format(ticketSupport_date,'%H:%i %p') as ticketTime, ticket_support.project_code as branch, project_name,
		ticketProblem_type, ticketSupport_ProblemDesc, ticketProblem_SLAHours, date_format(ticketSupport_MaxDate,'%d/%m/%Y') as MaxDate, date_format(ticketSupport_MaxDate,'%H:%i %p') as MaxTime,
		username as TeamName, ifnull(ticketProblem_showCounter,0), ticketSupport_CounterNo, ticketSupport_SolutionDesc, ticketVisit_Engineer, date_format(now(), '%H:%i %p') as nowDate,
		TIMESTAMPDIFF(hour, ticketSupport_MaxDate, now()) as remainingTime
		from ticket_support
		left join ticket_problem on ticket_problem.ticketProblem_id = ticket_support.ticketProblem_id
		left join user_support on user_id = ticket_support.ticketSupport_userId 
		left join project on project.project_code = ticket_support.project_code 
		where ticketSupport_status = 'PENDING' 
		order by date_format(ticketSupport_date,'%Y-%m-%d %H:%i') desc "; 



$result = mysql_query($query, $connection);
while($row = mysql_fetch_array($result)){
	extract($row);
	
	$queryColor = "select color_code from Ticket_ColorSetup where '$MaxTime' between date_format(setup_fromTime,'%H:%i %p') and date_format(setup_toTime,'%H:%i %p')";
	$resColor = mysql_query($queryColor, $connection);
	$rowColor = mysql_fetch_row($resColor);
	$color = $rowColor[0];
	
	$regularleftFormat1 =& $workbook->addFormat();
	$regularleftFormat1->setBgColor("'".$color."'");
	$regularleftFormat1->setPattern(1);
	
	$worksheet->write($RowNum, $ColumnNum, $ticketDate, $regularleftFormat1);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $ticketTime, $regularleftFormat1);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $branch, $regularleftFormat1);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $ticketProblem_type, $regularleftFormat1);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $ticketSupport_ProblemDesc, $regularleftFormat1);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $MaxDate, $regularleftFormat1);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $MaxTime, $regularleftFormat1);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $TeamName, $regularleftFormat1);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $ticketSupport_CounterNo, $regularleftFormat1);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $ticketProblem_SLAHours, $regularleftFormat1);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $ticketVisit_Engineer, $regularleftFormat1);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $ticketSupport_SolutionDesc, $regularleftFormat1);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $nowDate, $regularleftFormat1);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $remainingTime, $regularleftFormat1);
	$ColumnNum += 1;
	
	$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();


?>