<?php

/*
 * PHPExcel
 */
 
header('Cache-Control: no-cache, no-store, must-revalidate');   
header('Expires: 0');
/** Error reporting */
// error_reporting(E_ALL);
// ini_set('display_errors', TRUE);
// ini_set('display_startup_errors', TRUE);
date_default_timezone_set('Europe/London');
define('EOL',(PHP_SAPI == 'cli') ? PHP_EOL : '<br />');

// Shipping Detail
$mShippingRef 		= $_GET['shippingreference'];
$mTrailerRef 		= $_GET['trailerreference'];
$mConnect   		= $_GET['connection'];
$mUser				= $_GET['user'];
$mPwd				= $_GET['pword'];
$mHost              = $_GET['host'];
$mDbName			= $_GET['dbase'];
$mTableName 		= $_GET['table'];
$mFilter			= $_GET['filter'];
$mReturnFields		= $_GET['returnfields'];
$mCurrentUser       = $_GET['currentuser'];
$mOutput			= $_GET['output'];

$mFilter = str_replace("|AND|","&",$mFilter);
$mFilter = str_replace("/dbase/",$mDbName,$mFilter);

/** Include PHPExcel */
require_once dirname(__FILE__) . '/../../../phpexcel/Classes/PHPExcel/IOFactory.php';

// Load data
function LoadData($mHost, $mUser, $mPwd, $mDbName, $mShippingRef)
{
	$mConnection=mysqli_connect($mHost,$mUser,$mPwd,$mDbName) or
		exit("FAIL-Connect failed: ".mysqli_connect_errno()." : ". mysqli_connect_error());
// Read file lines
	$data = array();		
	$mDataQuery = "SELECT shipping_header.document_reference AS document_reference, shipping_header.trailer_reference AS trailer_reference, shipping_detail.part_number AS part_number, shipping_body.ran_order AS ran_order, shipping_detail.serial_reference AS serial_reference, shipping_detail.qty AS qty FROM shipping_header LEFT JOIN shipping_body on shipping_header.id=shipping_body.shipping_header_id JOIN shipping_detail ON shipping_body.id=shipping_detail.shipping_body_id WHERE shipping_header.document_reference='".$mShippingRef."'";
	if (!mysqli_query($mConnection,$mDataQuery))
	{
		print_r("FAIL-".mysql_error($mConnection)."-".$mDataQuery);
	}
// mysql query construct
	$mData = mysqli_query($mConnection,$mDataQuery);
	$i = 0;
	while($mRow = mysqli_fetch_assoc($mData)) 
	{
		$data[] =($mRow);
		$i++;
	}
	mysqli_close($mConnection);
	return $data;
//    print_r($data.":".$mDataQuery.":".$i);
}

// Simple sheet
function BasicTable($header, $data, $mShippingRef, $mTrailerRef)
{
// date / time
	$date = date('d/m/Y h:i:s a', time());
// Columns
	$w = array("A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z");
// Create new PHPExcel object
	$objPHPExcel = new PHPExcel();

// Set document properties
	$objPHPExcel->getProperties()->setCreator("vims")
		->setLastModifiedBy("vims")
		->setTitle("Shipping Detail")
		->setSubject("PHPExcel")
		->setDescription("PHPExcel")
		->setKeywords("office PHPExcel php")
		->setCategory("PHPExcel");
// formatting
	$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setAutoSize(false);
	$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth("18");
	$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(false);
	$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth("15");
	$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setAutoSize(false);
	$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth("15");
	$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setAutoSize(false);
	$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth("15");
	$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setAutoSize(false);
	$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth("15");
	$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setAutoSize(false);
	$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth("10");
// Add some data
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, 1, "Shipping Detail");
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, 2, "Shipping Reference:");
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, 2, $mShippingRef);
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2, 2, $mTrailerRef);
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(4, 2, "Report Date:");
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(5, 2, $date);
// Header
	for($i=0;$i<count($header);$i++)
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($i, 4, $header[$i]);
// Data
	$r = 5;
	foreach($data as $row)
	{			
		$c = 0;
		foreach($row as $col)
		{
			$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($c, $r, $col);
			$c++;
		}
		$r++;
	}
// Print Header & Footer
	$objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddHeader('&L&G&C&H'.$objPHPExcel->getProperties()->getTitle());
	$objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddFooter('&L&B' . $objPHPExcel->getProperties()->getTitle() . '&RPage &P of &N');
// Add image to the header
	$objDrawing = new PHPExcel_Worksheet_HeaderFooterDrawing();
	$objDrawing->setName('VEL logo');
	$objDrawing->setPath('../../../images/vel_logo_large.jpg');
	$objDrawing->setHeight(36);
	$objPHPExcel->getActiveSheet()->getHeaderFooter()->addImage($objDrawing, PHPExcel_Worksheet_HeaderFooter::IMAGE_HEADER_LEFT);
// Rename worksheet
	$objPHPExcel->getActiveSheet()->setTitle('ShippingDetail');
// Set Cell Border
	$styleArray = array('borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN)));
	$objPHPExcel->getActiveSheet()->getStyle("A1:I{$r}")->applyFromArray($styleArray);	
	unset($styleArray);
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
	$objPHPExcel->setActiveSheetIndex(0);
	return $objPHPExcel;
}

// Column headings
$header = array("Shipping Ref.","Trailer","Part Number","Ran / Order","Serial Ref.","Quantity");

// Data loading
if ($mHost == "")
	$mHost = "localhost";

if ($mUser == "")
	$mUser = "root";
if ($mPwd == "")
	$mPwd = "root";
if ($mDbName == "")
	$mDbName = "vims";

// date / time
$date = date('d/m/Y h:i:s a', time());
// Default Settings
if ($mFilter == "")
{
	include("../../vimsconfig/phpConfig.php");
	$mConnection = defaultDbConnectionVal("DEFAULT");
	$mConnectionVals = explode(",",$mConnection);
	$mHost = $mConnectionVals[1];
	$mUser = $mConnectionVals[2];
	$mPwd = $mConnectionVals[3];
	$mDbName = $mConnectionVals[4];
}	

// Create Sheet
$data = LoadData($mHost, $mUser, $mPwd, $mDbName, $mShippingRef);
$mTrailerRef = ($data[0]['trailer_reference']);
$objPHPExcel = BasicTable($header, $data, $mShippingRef,$mTrailerRef);

// Set page orientation and size
$objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
$objPHPExcel->getActiveSheet()->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);

// Save Excel 2007 file

// $objWriter->save($mXls);
if ($mOutput == "D")
{
	$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
	$mXls = "C:\\web\\veldb\\vimsreports\\shipdetail".$mCurrentUser.".xlsx";
	if ($mCurrentUser == "")
		$mCurrentUser="xxx";
	$objWriter->save("C:\\web\\veldb\\vimsreports\\shipdetail".$mCurrentUser.".xlsx");
}
else
{
// Redirect output to a client’s web browser (Excel2007)
	header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
	header('Content-Disposition: attachment;filename="shipdetail.xlsx"');
	header('Cache-Control: max-age=0');
// If you're serving to IE 9, then the following may be needed
	header('Cache-Control: max-age=1');

// If you're serving to IE over SSL, then the following may be needed
	header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
	header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
	header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
	header ('Pragma: public'); // HTTP/1.0

	$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
	$objWriter->save('php://output');
}

// echo ("Files have been created in C:\web\veldb\vimsreports\receiptdetail.xlsx");
?>