<?php 
header('Cache-Control: no-cache, no-store, must-revalidate');   
header('Expires: 0');

// error_reporting(E_ALL);
// ini_set('display_errors', 1);

$error = false;
$fileName = "Test.xls";

date_default_timezone_set('Europe/London');
/** Include PHPExcel_IOFactory */
require_once dirname(__FILE__) . '/../../../phpexcel/Classes/PHPExcel/IOFactory.php';
require_once dirname(__FILE__) . '/../../vimsconfig/phpConfig.php';

function upload($index,$destination)
{
//Test1:
	if (!isset($_FILES[$index]) OR $_FILES[$index]['error'] > 0)
	return false;

	move_uploaded_file($_FILES[$index]['tmp_name'],$destination. '/'.basename($_FILES[$index]['name']));
	return true;
	
}

function excelValidate1($connection,$fileName)
{
//	$inputFileName = dirname(__FILE__) . "/../../uploads/".$fileName;
	$inputFileName = "c:/upload/input/".$fileName;
	$objReader = PHPExcel_IOFactory::createReader('Excel2007');
	$objReader->setReadDataOnly(true);
	$objPHPExcel = $objReader->load($inputFileName);
	$objWorksheet = $objPHPExcel->getActiveSheet();

	$highestRow = $objWorksheet->getHighestRow();
	$highestColumn = $objWorksheet->getHighestColumn();
	$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
	$rows = array();
	
	$mStartRow = 1;
	
	if ((strtolower($objWorksheet->getCellByColumnAndRow(0,1)->getValue()) == "ref") || (strtolower($objWorksheet->getCellByColumnAndRow(0,1)->getValue()) == "ref"))
	{
		$mChkRef = $objWorksheet->getCellByColumnAndRow(1,1)->getValue();
		$mDataQuery = "SELECT id FROM order_header WHERE customer_reference='".$mChkRef."' LIMIT 1";
		if (!mysqli_query($connection,$mDataQuery))
			return "FAIL-".mysqli_error($mConnection)."-".$mDataQuery;
		$mData = mysqli_query($connection,$mDataQuery);
		$mErrMsg1 = "";
		if (mysqli_num_rows($mData) > 0)
			$mErrMsg1 = "FAIL-Customer Reference - ".$mChkRef." already Exists";
	}

	if ((strtolower($objWorksheet->getCellByColumnAndRow(0,2)->getValue()) == "part number") || (strtolower($objWorksheet->getCellByColumnAndRow(0,2)->getValue()) == "part_number"))
	{
		$mStartRow = 3;
		for ($row = $mStartRow; $row <= $highestRow; ++$row) 
		{
		
			$mDataQuery = "SELECT id AS id FROM part WHERE part_number ='".$objWorksheet->getCellByColumnAndRow(0, $row)->getValue()."' LIMIT 1";
			if (!mysqli_query($connection,$mDataQuery))
				return "FAIL-".mysqli_error($mConnection)."-".$mDataQuery;
			$mData = mysqli_query($connection,$mDataQuery);
			$mErrMsg2 = "";
			if (mysqli_num_rows($mData) == 0)
			{
				if ($mErrMsg2 != "")
					$mErrMsg2 .= ",";
				if ($mErrMsg2 == "")
					$mErrMsg2 = "Invalid Part Numbers - ";
				$mErrMsg2 .= $objWorksheet->getCellByColumnAndRow(0, $row)->getValue();
			}
		}
		if (($mErrMsg1 != "") || ($mErrMsg2 != ""))
		{
			$mDataQuery = "INSERT INTO audit_log(id,type,table_name,date_time,before_data,after_data) VALUES(DEFAULT,'UPLOAD','".$fileName."',now(),'".$mErrMsg1." ".$mErrMsg2."','')";
			if (!mysqli_query($connection,$mDataQuery))
				return "FAIL-".mysqli_error($mConnection)."-".$mDataQuery;
			return "FAIL-".$mErrMsg1." ".$mErrMsg2;
		}
		else
			return "OK  -";
	}
//	echo("Test");
}

// echo("Uploading - Please Wait..."."<BR><BR>");
// $upload = upload('uploadFile','../uploads');
 
// $upload = "Test";
if($upload)
{
//	$mUser		= "root";
//	$mPwd		= "root";
//	$mHost  	= "172.16.1.70";
//	$mDbName	= "vantec";
//	$mHost		= $_POST['host'];
//	$fileName = $_FILES['uploadFile']['name'];
	$fileName="test.xlsx";
}	
	$fileName="test.xlsx";
	$mReturn = excelValidate1($connection,$fileName);
	exit($mReturn);
	
?>