<?php

include('includes/session.php');
$Title = _('Import Stock Re-order Levels');
include('includes/header.php');
echo '<p class="page_title_text"><img alt="" src="' . $RootPath . '/css/' . $Theme .
		'/images/maintenance.png" title="' .
		_('Import Price List from CSV file') . '" />' . ' ' .
		_('Import Price List from CSV file') . '</p>';

$FieldHeadings = array(
	'StockID',			//  0 'STOCKID',
	'LocationCode',		//  1 'Location Code',
	'Reorderlevel',		//  2 'Reorder Level',
);

if (isset($_FILES['ReorderLevelFile']) and $_FILES['ReorderLevelFile']['name']) { //start file processing
	//check file info
	$FileName = $_FILES['ReorderLevelFile']['name'];
	$TempName  = $_FILES['ReorderLevelFile']['tmp_name'];
	$FileSize = $_FILES['ReorderLevelFile']['size'];
	$FieldTarget = 3;
	$InputError = 0;

	//get file handle
	$FileHandle = fopen($TempName, 'r');

	//get the header row
	$HeadRow = fgetcsv($FileHandle, 10000, ',');

	//check for correct number of fields
	if ( count($HeadRow) != count($FieldHeadings) ) {
		prnMsg (_('File contains') . ' '. count($HeadRow). ' ' . _('columns, expected') . ' '. count($FieldHeadings). '. ' . _('Download the template to see the expected columns.'),'error');
		fclose($FileHandle);
		include('includes/footer.php');
		exit;
	}

	//test header row field name and sequence
	$HeadingColumnNumber = 0;
	foreach ($HeadRow as $HeadField) {
		if ( trim(mb_strtoupper($HeadField)) != trim(mb_strtoupper($FieldHeadings[$HeadingColumnNumber]))) {
			prnMsg (_('The file to import the price list from contains incorrect column headings') . ' '. mb_strtoupper($HeadField). ' != '. mb_strtoupper($FieldHeadings[$HeadingColumnNumber]). '<br />' . _('The column headings must be') . ' StockID, SalesType, CurrencyCode, Price','error');
			fclose($FileHandle);
			include('includes/footer.php');
			exit;
		}
		$HeadingColumnNumber++;
	}

	//start database transaction
	DB_Txn_Begin();

	//loop through file rows
	$LineNumber = 1;
	while ( ($myrow = fgetcsv($FileHandle, 10000, ',')) !== FALSE ) {

		//check for correct number of fields
		$FieldCount = count($myrow);
		if ($FieldCount != $FieldTarget){
			prnMsg ($FieldTarget . ' ' . _('fields required') . ', '. $FieldCount. ' ' . _('fields received'),'error');
			fclose($FileHandle);
			include('includes/footer.php');
			exit;
		}

		// cleanup the data (csv files often import with empty strings and such)
		$StockID = mb_strtoupper($myrow[0]);
		foreach ($myrow as &$value) {
			$value = trim($value);
			$value = str_replace('"', '', $value);
		}

		//first off check that the item actually exist
		$sql = "SELECT COUNT(stockid) FROM stockmaster WHERE stockid='" . $StockID . "'";
		$result = DB_query($sql);
		$testrow = DB_fetch_row($result);
		if ($testrow[0] == 0) {
			$InputError = 1;
			prnMsg (_('Stock item') . ' "'. $myrow[0]. '" ' . _('does not exist'),'error');
		}
		//Then check that the location actually exists
		$sql = "SELECT COUNT(loccode) FROM locations WHERE loccode='" . $myrow[1] . "'";
		$result = DB_query($sql);
		$testrow = DB_fetch_row($result);
		if ($testrow[0] == 0) {
			$InputError = 1;
			prnMsg (_('Location') . ' "' . $myrow[1]. '" ' . _('does not exist'),'error');
		}

		if (!is_numeric($myrow[2])) {
			$InputError = 1;
			prnMsg (_('The reorderlevel must be numeric') ,'error');
		}

		if ($InputError !=1){

			//Firstly close any open prices for this item
			$sql = "UPDATE locstock
						SET reorderlevel='" . $myrow[2] . "'
						WHERE stockid='" . $StockID . "'
						AND loccode='" . $myrow[1] . "'";
			$result = DB_query($sql);

			$ErrMsg =  _('The reorder level could not be updated because');
			$DbgMsg = _('The SQL that was used to update the reorder level failed was');
			$result = DB_query($sql, $ErrMsg, $DbgMsg);
		}

		if ($InputError == 1) { //this row failed so exit loop
			break;
		}
		$LineNumber++;
	}

	if ($InputError == 1) { //exited loop with errors so rollback
		prnMsg(_('Failed on row '. $LineNumber. '. Batch import has been rolled back.'),'error');
		DB_Txn_Rollback();
	} else { //all good so commit data transaction
		DB_Txn_Commit();
		prnMsg( _('Batch Import of') .' ' . $FileName  . ' '. _('has been completed. All transactions committed to the database.'),'success');
	}

	fclose($FileHandle);

} else { //show file upload form

	echo '<form action="' . htmlspecialchars($_SERVER['PHP_SELF'], ENT_QUOTES, 'UTF-8') . '" method="post" class="noPrint" enctype="multipart/form-data">';
	echo '<div class="centre">';
	echo '<input type="hidden" name="FormID" value="' . $_SESSION['FormID'] . '" />';
	echo '<div class="page_help_text">' .
			_('This function loads a reorder level list from a comma separated variable (csv) file.') . '<br />' .
			_('The file must contain three columns, and the first row should be the following headers:') . '<br />StockID, LocationCode, Reorderlevel<br />' .
			_('followed by rows containing these three fields for each reorder level to be uploaded.') .  '<br />' .
			_('The StockID and LocationCode fields must have a corresponding entry in the stockmaster and locations tables.') . '</div>';

	echo '<br /><input type="hidden" name="MAX_FILE_SIZE" value="1000000" />' .
			_('Upload file') . ': <input name="ReorderLevelFile" type="file" />
			<input type="submit" name="submit" value="' . _('Send File') . '" />
		</div>
		</form>';

}

include('includes/footer.php');

?>
