CHEP Pallets Monitoring And Report. Step 9

The idea for a second chart is simple: display two chart bars that show how many pallets were transferred in and how many pallets were returned for the time period.

As this chart is very basic only two quantities are necessary to draw it:

• Sum of pallets transferred in.
• Sum of pallets returned.

In order to make the user aware of the time period for which these two sums are collected, I will also fetch the start date and end date for the time frame for these transfers/returns .

Finding the SUM of pallets transferred in

To find the sum of pallets transferred to us, I wrote a simple SUM query to retrieve the result from database, and stored the result in $totalPalletsTransfered variable.

$totalPalletsTransfered = 0;
//a query to select SUM of pallets transfered in
$querySelectPalletSum = "
	SELECT 
		SUM(quantity) AS totalPalletsTransfered
	FROM ChepReport
		WHERE transactionType = 'Transfer In'";
$resultPalletSum= mysqli_query($shortageReportDB, $querySelectPalletSum);
$num = mysqli_num_rows($resultPalletSum);

if ($num>0){
	while ($row = mysqli_fetch_array($resultPalletSum, MYSQLI_ASSOC))
	{	
		$totalPalletsTransfered = ROUND($row['totalPalletsTransfered'],0);	
	}
} else {
	ReportErrorForEmptyExcelFile();	
}

Finding the sum of pallets returned

To find the sum of pallets returned from us, I wrote a simple SUM query to retrieve the result from database, and stored the result in $totalPalletsReturned variable.

$totalPalletsReturned = 0;
//a query to select SUM of pallets returned from our site
$querySelectPalletSumReturned = "
	SELECT 
		SUM(quantity) AS totalPalletsReturned
	FROM ChepReport
		WHERE transactionType = 'Returns'";
$resultPalletSumReturned= mysqli_query($shortageReportDB, $querySelectPalletSumReturned);
$num = mysqli_num_rows($resultPalletSumReturned);

if ($num>0){
	while ($row = mysqli_fetch_array($resultPalletSumReturned, MYSQLI_ASSOC))
	{	
		//since this value is negative, I need to change that in order to draw a chart, hence the -
		$totalPalletsReturned = ROUND(-$row['totalPalletsReturned'],0);	
	}
} else {
	ReportErrorForEmptyExcelFile();
}

Finding start and end dates

To find start and end dates for this chart, I run an SQL query against database where a MIN and MAX shipment dates are selected where transfer type is either “Returns” or “Transfer IN”.

//a query to select MIN and MAX dates for transfers/returns
$querySelectMinMaxDates = "
	SELECT 
		MIN(shipmentDate) AS startDate,
		MAX(shipmentDate) AS endDate
	FROM ChepReport
		WHERE transactionType IN('Returns', 'Transfer IN')";
$resultMinMaxDates= mysqli_query($shortageReportDB, $querySelectMinMaxDates);
$num = mysqli_num_rows($resultMinMaxDates);

if ($num>0){
	while ($row = mysqli_fetch_array($resultMinMaxDates, MYSQLI_ASSOC))
	{	
		$startDate = $row['startDate'];	
		$endDate = $row['endDate'];	
	}
} else {
	ReportErrorForEmptyExcelFile();
}

And that is all the data I need for this second chart!

Preventing SQL injection

I did wrote a script earlier that removes ‘ on all values sent to back end in my JavaScript file, however it’s time to prevent SQL injection properly, by using htmlentities and mysqli_real_escape_string functions in PHP script.

//since suppliers name might contain characters that interfare with insert into query, need to make sure this is avoided.
$otherPartyArray[$i] = htmlentities(mysqli_real_escape_string($shortageReportDB, $otherPartyArray[$i]));

//just in case I will do the same safety check with other values as well	
$transactionTypeAray[$i] = htmlentities(mysqli_real_escape_string($shortageReportDB, $transactionTypeAray[$i]));
$quantityArray[$i] = htmlentities(mysqli_real_escape_string($shortageReportDB, $quantityArray[$i]));
$shipmentDateArray[$i] = htmlentities(mysqli_real_escape_string($shortageReportDB, $shipmentDateArray[$i]));

I also must not forget to decode suppliers names when retrieving them form database.

$supplierName = html_entity_decode($row['otherParty']);	

Since the SQL injection prevention was moved to back end, I can delete the script that removes ‘ from all values in JavaScript file.

//replace ' with '' as it acts an sql injection
//this is only neccesasry for suppliers name, but just in case I remove it from all values.
parsedObject[i]["Other Party"] = parsedObject[i]["Other Party"].replace(/'/g, "''");
parsedObject[i]["Transaction Type"] = parsedObject[i]["Transaction Type"].replace(/'/g, "''");
parsedObject[i]["Quantity"] = parsedObject[i]["Quantity"].replace(/'/g, "''");
parsedObject[i]["Shipment Date"] = parsedObject[i]["Shipment Date"].replace(/'/g, "''");
//USED htmlentities(mysqli_real_escape_string)) at back end to esace the insertion of ' into DB, 

Managing repetitive code

As this code become repetitive in PHP script (appeared 6 times already) I moved it to a function named ReportErrorForEmptyExcelFile.

$errors[] = 'Error! Please check that rows in excel file are not empty.';
$arrayForFrontEnd += array("errors"=>$errors);
Die ($jsonFile = json_encode($arrayForFrontEnd));

//report error function which gets called if the row inside the excel file are empty
function ReportErrorForEmptyExcelFile() {
	$arrayForFrontEnd = array(); //needs to be redeclared, as global variables are not accessible inside PHP function
	$errors[] = 'Error! Please check that rows in excel file are not empty.';
	$arrayForFrontEnd += array("errors"=>$errors);
	Die ($jsonFile = json_encode($arrayForFrontEnd));
}

On my next post I intend to discuss the data structure I require for my third chart: the one based on transfer types, as I intend to arrange the data from database a bit differently in this part.

Github.com repository has been updated.