<?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 print as printaccess from ".$_SESSION['dbLabel'].".login where user_account='".$_SESSION['useraccount']."' 	and Upper(Login_Schema) = Upper('".$_SESSION['dbName']."')	";
$resultlog=mysql_query($querylog,$connection) or die(mysql_error());
while($rows=mysql_fetch_array($resultlog)){
	extract($rows);
}

$querysetup="select grid_color1,grid_color2, DecimalBase, DecimalBase1, DecimalBase2, Base1, Base2, report_color1, report_color2, 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 ".$_SESSION['dbLabel'].".loginstk on project.project_code like concat(ifnull(loginstk.project_code,project.project_code),'%') ".
			  " where user_account='".$_SESSION['useraccount']."' ".
			  " order by project.project_code";
$projectresult = mysql_query($queryproject,$connection) or die(mysql_error());
$projectexist   = mysql_num_rows($projectresult);


$fromdate 		= $_GET['fromdate'];
$todate 		= $_GET['todate'];
$fromclient 	= $_GET['fromclient'];
$toclient 		= $_GET['toclient'];
$fromproject 	= $_GET['fromproject'];
$toproject 		= $_GET['toproject'];

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


$ReportTitle = 'Tender Status Report';

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

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


$SelectedOptions = "Filter by: \n";

if ($fromdate != '')      $SelectedOptions .= "-> Date From: ". $fromdate . "  To: ". $todate ."\n";	
if ($fromclient != '')    $SelectedOptions .= "-> Item From: ". $fromclient . "  To: ". $toclient ."\n";
if ($fromproject != '')   $SelectedOptions .= "-> Project From: ". $fromproject . "  To: ". $toproject . "\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->setMerge($RowNum, $ColumnNum, $RowNum+1, $ColumnNum);	
$worksheet->setColumn ($ColumnNum, $ColumnNum, 5);
$worksheet->write($RowNum,$ColumnNum,'S/No', $boldcenterFormat);
$ColumnNum += 1;

$worksheet->setMerge($RowNum, $ColumnNum, $RowNum+1, $ColumnNum);	
$worksheet->setColumn ($ColumnNum, $ColumnNum, 20);
$worksheet->write($RowNum,$ColumnNum,"Tender/Project", $boldcenterFormat);
$ColumnNum += 1;

$worksheet->setMerge($RowNum, $ColumnNum, $RowNum+1, $ColumnNum);	
$worksheet->setColumn ($ColumnNum, $ColumnNum, 20);
$worksheet->write($RowNum,$ColumnNum,"Client", $boldcenterFormat);
$ColumnNum += 1;

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

$worksheet->setMerge($RowNum, $ColumnNum, $RowNum+1, $ColumnNum);	
$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
$worksheet->write($RowNum,$ColumnNum,"Available Document", $boldcenterFormat);
$ColumnNum += 1;

$worksheet->setMerge($RowNum, $ColumnNum, $RowNum+1, $ColumnNum);	
$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
$worksheet->write($RowNum,$ColumnNum,"Scope of Work", $boldcenterFormat);
$ColumnNum += 1;

$worksheet->setMerge($RowNum, $ColumnNum, $RowNum+1, $ColumnNum);	
$worksheet->setColumn ($ColumnNum, $ColumnNum, 10);
$worksheet->write($RowNum,$ColumnNum,"Submission Due Date", $boldcenterFormat);
$ColumnNum += 1;

$worksheet->setMerge($RowNum, $ColumnNum, $RowNum+1, $ColumnNum);	
$worksheet->setColumn ($ColumnNum, $ColumnNum, 10);
$worksheet->write($RowNum,$ColumnNum,"To Be Ready For Review", $boldcenterFormat);
$ColumnNum += 1;


$worksheet->setMerge($RowNum, $ColumnNum, $RowNum+1, $ColumnNum);	
$worksheet->setColumn ($ColumnNum, $ColumnNum, 10);
$worksheet->write($RowNum,$ColumnNum,"Urgent Action Req'd", $boldcenterFormat);
$ColumnNum += 1;

$worksheet->setMerge($RowNum, $ColumnNum, $RowNum+1, $ColumnNum);	
$worksheet->setColumn ($ColumnNum, $ColumnNum, 10);
$worksheet->write($RowNum,$ColumnNum,"Status At", $boldcenterFormat);
$ColumnNum += 1;

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

$worksheet->setMerge($RowNum, $ColumnNum, $RowNum+1, $ColumnNum);	
$worksheet->setColumn ($ColumnNum, $ColumnNum, 10);
$worksheet->write($RowNum,$ColumnNum,"Tender Amount", $boldcenterFormat);
$ColumnNum += 1;

$worksheet->setMerge($RowNum, $ColumnNum, $RowNum+1, $ColumnNum);	
$worksheet->setColumn ($ColumnNum, $ColumnNum, 10);
$worksheet->write($RowNum,$ColumnNum,"Direct Cost", $boldcenterFormat);
$ColumnNum += 1;

$worksheet->setMerge($RowNum, $ColumnNum, $RowNum, $ColumnNum+1);
$worksheet->setColumn ($ColumnNum, $ColumnNum, 10);
$worksheet->write($RowNum,$ColumnNum,"Margin", $boldcenterFormat);

//row under Margin
$worksheet->setColumn ($ColumnNum, $ColumnNum, 5);
$worksheet->write($RowNum+1,$ColumnNum,"'=N=", $boldcenterFormat);
$ColumnNum += 1;
$worksheet->setColumn ($ColumnNum, $ColumnNum, 5);
$worksheet->write($RowNum+1,$ColumnNum,"%", $boldcenterFormat);
$ColumnNum += 1;

$worksheet->setMerge($RowNum, $ColumnNum, $RowNum+1, $ColumnNum);	
$worksheet->setColumn ($ColumnNum, $ColumnNum, 10);
$worksheet->write($RowNum,$ColumnNum,"Award Status", $boldcenterFormat);
$ColumnNum += 1;

$ndate = explode("/", $fromdate);	
$fromdate = $ndate[2]."-".$ndate[1]."-".$ndate[0];
$ndate = explode("/", $todate);	
$todate = $ndate[2]."-".$ndate[1]."-".$ndate[0];

$query = " select tenders.project_code as project_code, project_name, client_code, ledger_name, date_format(tender_receivedDate,'%d/%m/%Y') as receivedDate, tender_documents, tender_scope,
			date_format(tender_dueDate,'%d/%m/%Y') as dueDate, if (ifnull(tender_reviewReady,0)=0, 'No', 'Yes'), tender_actionReq, tender_statusAt,	date_format(tender_submitDate,'%d/%m/%Y') as submitDate,  
			tender_amount, tender_directCost, tender_margin, tender_marginPer, if (ifnull(tender_awardStatus,0)=0, 'No', 'Yes') 
			from tenders
			inner join clients on Ledger_number = client_code
			inner join project on project.Project_Code = tenders.project_code ";  
			 
if ($projectexist >	0 && $FI_RepRestrictProject == 1)
  $query .= " Inner join ( select distinct project_code from ".$_SESSION['dbLabel'].".loginstk ".
		   " 			  where loginstk.user_account='".$_SESSION['useraccount']."' ".
		   "              and (project_code is not null and project_code != '') ".
		   "			 ) Q1 on tenders.project_code like concat(Q1.project_code,'%')   ";	 
						   
 $query .=  " Where (if ('$fromproject' = '', 1=1, tenders.Project_code between '$fromproject' and '$toproject' )) and 
			(if ('$fromclient' = '', 1=1, tenders.client_code between '$fromclient' and '$toclient' ))  and 
			Date_Format(tender_receivedDate,'%Y-%m-%d') between '$fromdate' and '$todate' ";  

$query .= " Order By tender_receivedDate Desc ";

$ct = 1;

$ColumnNum = 0;
$RowNum += 2;

$result = mysql_query($query, $connection);

while($row = mysql_fetch_array($result)){
	
	$worksheet->write($RowNum, $ColumnNum, $ct, $regularleftFormat1);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $row[7], $regularleftFormat1);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $row[3], $regularleftFormat1);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $row[4], $regularleftFormat1);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $row[5], $regularleftFormat1);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $row[6], $regularleftFormat1);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $row[7], $regularleftFormat1);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $row[8], $regularleftFormat1);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $row[9], $regularleftFormat1);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $row[10], $regularleftFormat1);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $row[11], $regularleftFormat1);
	$ColumnNum += 1;
	$worksheet->writeNumber($RowNum, $ColumnNum, round($row[12],$DecimalBase), $regularleftFormat1);
	$ColumnNum += 1;
	$worksheet->writeNumber($RowNum, $ColumnNum, round($row[13],$DecimalBase), $regularleftFormat1);
	$ColumnNum += 1;
	$worksheet->writeNumber($RowNum, $ColumnNum, round($row[14],$DecimalBase), $regularleftFormat1);
	$ColumnNum += 1;
	$worksheet->writeNumber($RowNum, $ColumnNum, round($row[15],$DecimalBase), $regularleftFormat1);
	$ColumnNum += 1;
	$worksheet->write($RowNum, $ColumnNum, $row[16], $regularleftFormat1);
	$ColumnNum += 1;
	
	$ColumnNum = 0;
	$RowNum += 1;
	$ct++;	
}


if ($availgroupby != '1' && $availgroupby != '')	{
	$RowNum += 1;
	$ColumnNum = 7;
	$worksheet->write($RowNum, $ColumnNum,"Total in ".$tempgrpby, $boldleftFormat);
	$ColumnNum += 1;
	
	$worksheet->writeNumber($RowNum, $ColumnNum, $totalQtyByGrp, $boldcenterFormat);
	$ColumnNum += 1;
	
	if ($chkcost == 1) {
		$worksheet->write($RowNum, $ColumnNum,'', $boldleftFormat);
		$ColumnNum += 1;
		
		$worksheet->writeNumber($RowNum, $ColumnNum, $totalAmntByGrp, $boldcenterFormat);
		$ColumnNum += 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();


?>