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

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

$mUser				= $_GET['user'];
$mPwd				= $_GET['password'];
$mHost              = $_GET['host'];
$mDbName			= $_GET['database'];

if(isset($_FILES['upload']))
{
    $errors= array();
    $file_name = $_FILES['upload']['name'];
    $file_size = $_FILES['upload']['size'];
    $file_tmp = $_FILES['upload']['tmp_name'];
    $file_type = $_FILES['upload']['type'];
    $file_ext=strtolower(end(explode('.',$_FILES['upload']['name'])));
 
    if(empty($errors)==true) 
	{
		move_uploaded_file($file_tmp,"../uploads/".$file_name);
		if (!file_exists("../uploads/".$file_name)) 
		{
			exit("FAIL-Please Upload File first");
		}
		partUpdate($mUser, $mPwd, $mHost, $mDbName, $file_name);
		echo ("Success:");
    }
	else
	{
		print_r($errors);
    }
}

function partUpdate($mUser, $mPwd, $mHost, $mDbName, $file_name)
{
	$mConnection=mysqli_connect($mHost,$mUser,$mPwd,$mDbName) or
	exit("FAIL-Connect failed: ".mysqli_connect_errno()." : ". mysqli_connect_error());

// $objPHPExcel = PHPExcel_IOFactory::load("../uploads/partupdate.xlsx");
	$inputFileName = "../uploads/".$file_name;
	$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();

	if ((strtolower($objWorksheet->getCellByColumnAndRow(0, 1)->getValue()) == "part number") || (strtolower($objWorksheet->getCellByColumnAndRow(0, 1)->getValue()) == "part_number"));
	{
		$mLocationTypeIndex=-1;
		$mLocationSubtypeIndex=-1;
		$mRdtPromptIndex=-1;
	
		for ($col = 0; $col <= $highestColumnIndex; ++$col) 
		{
			if ((strtolower($objWorksheet->getCellByColumnAndRow($col, 1)->getValue()) == "location type code") || (strtolower($objWorksheet->getCellByColumnAndRow($col, 1)->getValue()) == "location_type_code"))
				$mLocationTypeIndex = $col;
			if ((strtolower($objWorksheet->getCellByColumnAndRow($col, 1)->getValue()) == "location sub type code") || (strtolower($objWorksheet->getCellByColumnAndRow($col, 1)->getValue()) == "location_sub_type_code"))
				$mLocationSubtypeIndex = $col;
			if ((strtolower($objWorksheet->getCellByColumnAndRow($col, 1)->getValue()) == "rdt prompt") || (strtolower($objWorksheet->getCellByColumnAndRow($col, 1)->getValue()) == "rdt_prompt"))
				$mRdtPromptIndex = $col;
		}
		for ($row = 2; $row <= $highestRow; ++$row) 
		{
			$mLocationTypeId="";
			$mLocationSubtypeId="";
			$mRdtPromptId="";
			if ($mLocationTypeIndex >= 0)
			{
				$mDataQuery = "SELECT id AS id FROM location_type WHERE location_type_code ='".$objWorksheet->getCellByColumnAndRow($mLocationTypeIndex, $row)->getValue()."' LIMIT 1";
				$mData = mysqli_query($mConnection,$mDataQuery);
				$i = 0;
				while($mRow = mysqli_fetch_assoc($mData)) 
				{
					$mLocationTypeId = $mRow['id'];
				}
			}
			if ($mLocationSubtypeIndex >= 0)
			{
				$mDataQuery = "SELECT id AS id FROM location_sub_type WHERE location_sub_type_code ='".$objWorksheet->getCellByColumnAndRow($mLocationSubtypeIndex, $row)->getValue()."' LIMIT 1";
				$mData = mysqli_query($mConnection,$mDataQuery);
				$i = 0;
				while($mRow = mysqli_fetch_assoc($mData)) 
				{
					$mLocationSubtypeId = $mRow['id'];
				}
			}
			if ($mRdtPromptIndex >= 0)
			{
				$mRdtPromptId = $objWorksheet->getCellByColumnAndRow($mRdtPromptIndex, $row)->getValue();
			}
			$mDataQuery = "UPDATE part SET last_updated=now(), last_updated_by='SQL'";
			if ($mLocationTypeId != "")
				$mDataQuery = $mDataQuery.", fixed_location_type_id=".$mLocationTypeId;
			if ($mLocationSubtypeId != "")
				$mDataQuery = $mDataQuery.", fixed_sub_type_id=".$mLocationSubtypeId;
			if ($mRdtPromptId != "")
				$mDataQuery = $mDataQuery.", rdt_prompt='".$mRdtPromptId."'";
			$mDataQuery = $mDataQuery." WHERE part_number LIKE('%".$objWorksheet->getCellByColumnAndRow(0, $row)->getValue()."%')";
			mysqli_query($mConnection,$mDataQuery);
			echo("SQL:".$mDataQuery);
		}
	}
	echo("Test");
}

?>
<html>
   <body>
      
      <form action = "" method = "POST" enctype = "multipart/form-data">
	  <div id="Popup_Table" class="Popup">
         <input type = "file" name = "upload" />
         <input type = "submit"/>
			
         <ul>
            <li>Sent file: <?php echo $_FILES['upload']['name'];  ?>
            <li>File size: <?php echo $_FILES['upload']['size'];  ?>
            <li>File type: <?php echo $_FILES['upload']['type'] ?>
         </ul>
	  </div>
      </form>
      
   </body>
</html>