CHEP Pallets Monitoring And Report. Step 11

As I mentioned in my previous post, I will do my second chart differently then initially intended. After running this query against the database:

	SELECT 
		transactionType,
		SUM(quantity) AS palletSum
	FROM ChepReport
		WHERE 1
	GROUP BY
		transactionType
	ORDER BY
		transactionType

I got these results:

After some thinking I decided that instead of just using sums of “Returns” and “Transfer IN” transaction types, I will instead find these two sumes:

• sum of all positive sums for transaction types.
• sum of all negative sums for transaction types.

This way a chart will represent more accurate data on pallets transferred in and out to us.

These results also gave me an idea for one more chart, so this one will be a fourth chart in total. So this new chart will display all sums for each transfer type for the time period like this:

Will still place Returns and Transfer IN next to each other as these two transfer types are used most commonly. I did check the excel file to inspect the Unknown transfer type, after filtering data by “Transaction Type” column it appears to be rejected pallets, as that is the only Transaction Type that has a status of Rejected. I Might change the “Unknown” name to “Rejected” to make things clearer.

Retrieving data for chart 3 and chart 4

Since I decided to create one more additional chart I will need to retrieve data from database for it. Luckily for chart 3 and 4 I can get data for both charts with the same SQL query and some PHP scripting.

//store all possible transactions type names inside an array
$transferTypesNamesArray = array("Admin IN","Admin OUT","Correction IN","Returns","Reversed Transfer IN","Transfer IN","Transfer OUT", "Unknown");
$transferTypseSumsArray = array();
$palletsIN = 0;
$palletsOUT = 0;
$querySelectPalletSum = "
	SELECT 
		transactionType,
		SUM(quantity) AS palletSum
	FROM ChepReport
		WHERE 1
	GROUP BY
		transactionType
	ORDER BY
		transactionType";
$resultPalletSum= mysqli_query($shortageReportDB, $querySelectPalletSum);
$num = mysqli_num_rows($resultPalletSum);
$arrayIndex = 0;
if ($num>0){
	while ($row = mysqli_fetch_array($resultPalletSum, MYSQLI_ASSOC))
	{	
		$palletSum = ROUND($row['palletSum'],0);
		//add pallet sum from row to either $palletsIN or PalletsOUT variable 
		if($palletSum > 0) {
			$palletsIN+=$palletSum;
		} else {
			$palletsOUT += $palletSum;
		}
		//for chart I dont want negative values
		$palletSum = abs($palletSum );
		$transferTypseSumsArray += array($transferTypesNamesArray[$arrayIndex]=>$palletSum);
		$arrayIndex++;
	}
} else {
	ReportErrorForEmptyExcelFile();	
}
//for chart I don't want to have negative values
$palletsOUT = abs($palletsOUT );

As chart 3 only consists of two bars (pallets in and pallets out), I just added an if/else statment inside the while loop, which adds the value retrieved from a row to $palletsOUT variable, if it is negative and to $palletsIN variable if it is positive. At the end of the PHP script I use and abs() function to convert $palletsOUT variable to positive, as I don’t want a negative value for my bar chart. When I send these two variables to fron end and check the response in Chrome DevTools I can see that it worked as intended:

Data for chart 4 is collected with this code in the script:

                $palletSum = abs($palletSum );
		$transferTypseSumsArray += array($transferTypesNamesArray[$arrayIndex]=>$palletSum);
		$arrayIndex++;

The first thing this script does is convert $palletSum value from negative, as negative values are not desired for the chart. Secondly this script assigns transfer type name and pallet sum value to $transferTypseSumsArray array. I declared an $arrayIndex variable that is set to 0 before the while loop in order to loop through $transferTypesNamesArray from index 0 to the last index, as values in $transferTypesNamesArray are used as keys in nested array. Once I send $transferTypseSumsArray to front end and check the repsonse in Chrome DevTools I can see that this script works as intended.

As I no longer intend to use two sums for transfer types of “Returns” and “Transfer IN” this code is no longer usefull and it has been deleted:

$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();	
}

$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();
}

I do feel that now I have all the data necessary to draw the charts and that work on back end is completed and I can start working on front end. But before that I will write a summary on my next post of what has been achieved so that I have a proper guideline before I start working with the charts.

Github.com repository has been updated.