<?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 = 'Branch Info';

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

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

$workbook->send('Exp_BranchInfo.xls');
 
//Creating a worksheet
$worksheet=&$workbook->addWorksheet('Branch Info');
$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);_(* "-"??_);_(@_)');


//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);



$RowNum    = 3;
$ColumnNum = 0;	   

//header1
		
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
	$worksheet->write($RowNum,$ColumnNum,'Code', $boldleftFormat);
	$ColumnNum += 1;
	
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 20);
	$worksheet->write($RowNum,$ColumnNum,"Name", $boldleftFormat);
	$ColumnNum += 1;
	
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 10);
	$worksheet->write($RowNum,$ColumnNum,"Starting Date", $boldleftFormat);
	$ColumnNum += 1;
	
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 10);
	$worksheet->write($RowNum,$ColumnNum,"Preventive Date", $boldleftFormat);
	$ColumnNum += 1;
	
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 8);
	$worksheet->write($RowNum,$ColumnNum,"No. of Visits", $boldleftFormat);
	$ColumnNum += 1;
	
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 8);
	$worksheet->write($RowNum,$ColumnNum,"No. of Months", $boldleftFormat);
	$ColumnNum += 1;
	
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
	$worksheet->write($RowNum,$ColumnNum,"State", $boldleftFormat);
	$ColumnNum += 1;
	
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
	$worksheet->write($RowNum,$ColumnNum,"LGA", $boldleftFormat);
	$ColumnNum += 1;
	
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
	$worksheet->write($RowNum,$ColumnNum,"Region", $boldleftFormat);
	$ColumnNum += 1;
	
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 30);
	$worksheet->write($RowNum,$ColumnNum,"Address", $boldleftFormat);
	$ColumnNum += 1;
	
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
	$worksheet->write($RowNum,$ColumnNum,"Branch Manager", $boldleftFormat);
	$ColumnNum += 1;
	
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
	$worksheet->write($RowNum,$ColumnNum,"Branch Manager Phone #", $boldleftFormat);
	$ColumnNum += 1;
	
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
	$worksheet->write($RowNum,$ColumnNum,"Type", $boldleftFormat);
	$ColumnNum += 1;
	
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 8);
	$worksheet->write($RowNum,$ColumnNum,"Counters", $boldleftFormat);
	$ColumnNum += 1;
	
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
	$worksheet->write($RowNum,$ColumnNum,"IP", $boldleftFormat);
	$ColumnNum += 1;
	
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
	$worksheet->write($RowNum,$ColumnNum,"Subnet", $boldleftFormat);
	$ColumnNum += 1;
	
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 15);
	$worksheet->write($RowNum,$ColumnNum,"Gateway", $boldleftFormat);
	$ColumnNum += 1;
	
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 30);
	$worksheet->write($RowNum,$ColumnNum,"HUB WAN MAC ADDRESS", $boldleftFormat);
	$ColumnNum += 1;
	
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 30);
	$worksheet->write($RowNum,$ColumnNum,"HUB LAN MAC ADDRESS", $boldleftFormat);
	$ColumnNum += 1;
	
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 30);
	$worksheet->write($RowNum,$ColumnNum,"PRINTER MAC ADDRESS", $boldleftFormat);
	$ColumnNum += 1;
	
	$worksheet->setColumn ($ColumnNum, $ColumnNum, 30);
	$worksheet->write($RowNum,$ColumnNum,"HUB SOFTWARE VERSION", $boldleftFormat);
	$ColumnNum += 1;


 $querysearch = "select project_code, project_name, date_format(project_startingDate,'%d/%m/%Y') as startingDate, date_format(project_preventifDate,'%d/%m/%Y') as preventifDate, project_nbrVisits, 
			project_periodMonth, Project_state, Project_LGA, Project_region, project_address, project_BranchManager, project_BranchManagerPhone, project_infoType, project_counters,
			project_IP, project_subnet, project_gateWay
			from project
			order by project_code ";
	
$result = mysql_query($querysearch, $connection);

$ColumnNum = 0;
$RowNum += 1;


while($rows = mysql_fetch_array($result)){
	extract($rows);
		
	$worksheet->write($RowNum, $ColumnNum, $project_code, $regularleftFormat);
	$ColumnNum += 1;
	
	$worksheet->write($RowNum, $ColumnNum, $project_name, $regularleftFormat);
	$ColumnNum += 1;
	
	$worksheet->write($RowNum, $ColumnNum, $startingDate, $regularleftFormat);
	$ColumnNum += 1;
	
	$worksheet->write($RowNum, $ColumnNum, $preventifDate, $regularleftFormat);
	$ColumnNum += 1;

	$worksheet->write($RowNum, $ColumnNum, $project_nbrVisits, $regularleftFormat);
	$ColumnNum += 1;
	
	$worksheet->write($RowNum, $ColumnNum, $project_periodMonth, $regularleftFormat);
	$ColumnNum += 1;
	
	$worksheet->write($RowNum, $ColumnNum, $Project_state, $regularleftFormat);
	$ColumnNum += 1;
	
	$worksheet->write($RowNum, $ColumnNum, $Project_LGA, $regularleftFormat);
	$ColumnNum += 1;
	
	$worksheet->write($RowNum, $ColumnNum, $Project_region, $regularleftFormat);
	$ColumnNum += 1;
	
	$worksheet->write($RowNum, $ColumnNum, $project_address, $regularleftFormat);
	$ColumnNum += 1;
	
	$worksheet->write($RowNum, $ColumnNum, $project_BranchManager, $regularleftFormat);
	$ColumnNum += 1;
	
	$worksheet->write($RowNum, $ColumnNum, $project_BranchManagerPhone, $regularleftFormat);
	$ColumnNum += 1;
	
	$worksheet->write($RowNum, $ColumnNum, $project_infoType, $regularleftFormat);
	$ColumnNum += 1;
	
	$worksheet->write($RowNum, $ColumnNum, $project_counters, $regularleftFormat);
	$ColumnNum += 1;
	
	$worksheet->write($RowNum, $ColumnNum, $project_IP, $regularleftFormat);
	$ColumnNum += 1;
	
	$worksheet->write($RowNum, $ColumnNum, $project_subnet, $regularleftFormat);
	$ColumnNum += 1;	
	
	$worksheet->write($RowNum, $ColumnNum, $project_gateWay, $regularleftFormat);
	$ColumnNum += 1;	
	
	$wanAddr = getAddress($project_code, 'wan', $connection);
	$worksheet->write($RowNum, $ColumnNum, $wanAddr, $regularleftFormat);
	$ColumnNum += 1;
	
	$lanAddr = getAddress($project_code, 'lan', $connection);
	$worksheet->write($RowNum, $ColumnNum, $lanAddr, $regularleftFormat);
	$ColumnNum += 1;
	
	$printAddr = getAddress($project_code, 'print', $connection);
	$worksheet->write($RowNum, $ColumnNum, $printAddr, $regularleftFormat);
	$ColumnNum += 1;
	
	$softAddr = getAddress($project_code, 'soft', $connection);
	$worksheet->write($RowNum, $ColumnNum, $softAddr, $regularleftFormat);
	$ColumnNum += 1;
	
	$ColumnNum = 0;	
	$RowNum += 1;	
}




$workbook->close();

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 getAddress($project_code, $macType, $connection){
	$queryAddr = "select group_concat(mac_address SEPARATOR ' - ')  from project_macAddress where project_code = '$project_code' and mac_type = '$macType' ";
	$resAddr = mysql_query($queryAddr, $connection);
	$rowAddr = mysql_fetch_row($resAddr);
	return $rowAddr[0];
}





?>