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

// echo ("FAIL-TEST1");
// return;


date_default_timezone_set('Europe/London');
define('EOL',(PHP_SAPI == 'cli') ? PHP_EOL : '<br />');

// Receipt Summary
$mHaulierRef 		= $_GET['haulierreference'];
$mDocumentRef 		= $_GET['documentreference'];
$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, $mHaulierRef, $mDocumentRef)
{
// Read file lines
	$data = array();
// mysql connection
	$mConnection=mysqli_connect($mHost,$mUser,$mPwd,$mDbName) or
		exit("FAIL-Connect failed: ".mysqli_connect_errno()." : ". mysqli_connect_error());
		
	$mDataQuery = "SELECT receipt_header.document_reference AS document_reference, receipt_header.customer_reference AS customer_reference, receipt_body.part_number AS part_number,receipt_detail.serial_reference AS serial_reference, (CASE WHEN receipt_detail.received_qty > 0 THEN (receipt_detail.received_qty/(CASE WHEN part.conversion_factor > 0 THEN part.conversion_factor ELSE 1 END)) ELSE 1 END) AS received_qty";
	$mDataQuery .= ",(CASE WHEN receipt_detail.cts_qty > 0 THEN (receipt_detail.cts_qty/(CASE WHEN part.conversion_factor > 0 THEN part.conversion_factor ELSE 1 END)) ELSE 0 END) AS cts_qty, inventory_master.location_code AS location_code";
	$mDataQuery .= ", CASE WHEN inventory_master.requires_inspection=1 THEN 'Y' ELSE '' END AS inspect, CASE WHEN inventory_master.requires_decant=1 THEN 'Y' ELSE '' END AS decant";
	$mDataQuery .= ", product_type.product_name AS product_name, CASE WHEN part.requires_count =1 THEN 'Y' ELSE '' END AS count";
	$mDataQuery .= ", CASE WHEN inventory_status.inventory_status_code='QTINE' THEN 'Y' ELSE '' END AS QTINE";
	$mDataQuery .= " FROM receipt_header";
	$mDataQuery .= " LEFT JOIN product_type ON receipt_header.product_type_id=product_type.id";
	$mDataQuery .= " LEFT JOIN receipt_body ON receipt_header.id=receipt_body.receipt_header_id";
	$mDataQuery .= " LEFT JOIN part ON receipt_body.part_id=part.id";
	$mDataQuery .= " LEFT JOIN receipt_detail ON receipt_body.id=receipt_detail.receipt_body_id";
	$mDataQuery .= " LEFT JOIN inventory_master ON receipt_detail.part_number=inventory_master.part_number AND receipt_detail.serial_reference=inventory_master.serial_reference";
	$mDataQuery .= " LEFT JOIN inventory_status ON inventory_master.inventory_status_id = inventory_status.id";
	$mDataQuery .= " WHERE receipt_detail.received_qty>0 and receipt_header.document_reference = '".$mDocumentRef."'";
	//if (($mDocumentRef != null) && ($mDocumentRef != ""))
		//$mDataQuery .= " AND receipt_header.document_reference = '".$mDocumentRef."'";
// mysql query construct
	if(!$mData = mysqli_query($mConnection,$mDataQuery))
		print_r("FAIL-".mysqli_error($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, $mHaulierRef)
{
// 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("Receipt 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("15");
	$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setAutoSize(false);
	$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth("15");
	$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setAutoSize(false);
	$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth("5");
	$objPHPExcel->getActiveSheet()->getColumnDimension('I')->setAutoSize(false);
	$objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth("5");
	$objPHPExcel->getActiveSheet()->getColumnDimension('J')->setAutoSize(false);
	$objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth("15");
	$objPHPExcel->getActiveSheet()->getColumnDimension('K')->setAutoSize(false);
	$objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth("5");
	$objPHPExcel->getActiveSheet()->getColumnDimension('L')->setAutoSize(false);
	$objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth("10");
// Add some data
// Haulier Ref.
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, 2, "Haulier Reference:");
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, 2, $mHaulierRef);
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(6, 2, "Report Date:");
	$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(7, 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('ReceiptDetail');
// Set Cell Border
	$styleArray = array('borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN)));
	$objPHPExcel->getActiveSheet()->getStyle("A1:L{$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;
}

// Return Default mySql Connection
function defaultDbConnection()
{
// get default System Settings from xml file	
	$mSystem = "DEFAULT";
// Get System Definition XML from URL
	$mData = file_get_contents("../../vimsconfig/systemsettings.xml");
// Parse XML to XML Object Array
	$xml = simplexml_load_string($mData, 'SimpleXMLElement', LIBXML_NOCDATA);
// Split-out System Definitions from XML 'fields' Nodes
	$i = 0;

	for ($i = 0; $i <= 150; $i++) 
	{			
		$mSystemId = "";
		$mPath = "";
		$mDbHost = "";
		$mDbUser = "";
		$mDbPwd = "";
		$mDbase = "";
		$mMqServer = "";
		$mCompany = "";
		if (("".$xml->systems->system[$i]->systemid."" == "") || ("".$xml->systems->system[$i]->systemid."" == false) || ("".$xml->systems->system[$i]->systemid."" == false))
			break;
		
		$mSystemId = "".$xml->systems->system[$i]->systemid."";
		if (($mSystemId == "") || ($mSystemId == false))
			break;
		
// Store System Settings from "trandef/systemsettings.xml"		
		if (strToUpper($mSystem) == strToUpper($mSystemId))
		{
			$mPath = "".$xml->systems->system[$i]->path."";
			$mDbHost = "".$xml->systems->system[$i]->dbhost."";
			$mDbUser = "".$xml->systems->system[$i]->dbuser."";
			$mDbPwd = "".$xml->systems->system[$i]->dbpwd."";
			$mDbase = "".$xml->systems->system[$i]->dbase."";
			break;
		}
		if (($mSystemId == "") || ($mSystemId == false))
			break;
	}

//	$mConnection = mysqli_connect($mDbHost,$mDbUser,$mDbPwd,$mDbase)or die("FAIL-Failed to connect to Database: " . mysqli_connect_error().":".$mSystem);
	$mDbDefaults = array();
	$mDbDefaults[0] = $mDbHost;
	$mDbDefaults[1] = $mDbUser;
	$mDbDefaults[2] = $mDbPwd;
	$mDbDefaults[3] = $mDbase;
	$mDbDefaults[4] = $mPath;
	return $mDbDefaults;
}

// Column headings
$header = array("Receipt Ref.","Customer Ref.","Part Number","Serial Ref.","Scan Qty.","CTS_Qty","Location","QA","DEC","Prod.Type","Count","QTINE");

// Data loading
if (($mHost == "") || ($mHost == null))
{
	$mDbDefaults = defaultDbConnection();
	$mHost = $mDbDefaults[0];
	$mUser = $mDbDefaults[1];
	$mPwd = $mDbDefaults[2];
	$mDbName = $mDbDefaults[3];
	$mPath = $mDbDefaults[4];
}

// date / time
$date = date('d/m/Y h:i:s a', time());

// Create Sheet
$data = LoadData($mHost, $mUser, $mPwd, $mDbName, $mHaulierRef, $mDocumentRef);
$objPHPExcel = BasicTable($header, $data, $mHaulierRef);

// 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\\receiptdetail".$mCurrentUser.".xlsx";
	if ($mCurrentUser == "")
		$mCurrentUser="xxx";
	$objWriter->save("C:\\web\\veldb\\vimsreports\\receiptdetail".$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="receiptdetail.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");
?>