CHEP Pallets Monitoring And Report. Step 7

Had some time to work on this project. I managed to write code that fills suppliers array with 0 values if there are no CHEP pallets delivered by supplier for at least one month of all the months in database. So now if a supplier hasn’t delivered pallets for 1 month out of 4 months, that are in database, the result of supplier array in a JSON string looks like this:

Though this excel file should contain half a year of pallet transfer values, only 4 months have been generated. I figured out why this is, but will expand on this later or in another post.

To add zero values to associative result array for each supplier, I had to do these steps:

• Determine total amount of unique months.
• Gather distinct values for all months.
• Determine the number of suppliers.
• Gather distinct names of all suppliers.
• Code two nested loops that check if a month value exists in supplier’s array, if not, then a month value is added as a key and 0 as it’s value.

Finding the total number of months

The first step in order to create this array was to determine how many months of transfers have been added to database from excel file. To figure this out an SQL query was run against database with a combined SQL functions of COUNT() and DISTINCT() and the result is stored inside $numberOfMonth variable:

//--a query to determine how many months of transfers have been uploaded
$queryCountMonth = "
	SELECT 
		COUNT(DISTINCT(yearMonthDate)) AS numberOfMonths
	FROM ChepReport
		WHERE transactionType = 'Transfer In'";
$resultCountMonth = mysqli_query($shortageReportDB, $queryCountMonth);
$num = mysqli_num_rows($resultCountMonth);

if ($num>0){
	while ($row = mysqli_fetch_array($resultCountMonth, MYSQLI_ASSOC))
	{	
		$numberOfMonths = $row['numberOfMonths'];
	}
} else {
	$errors[] = 'Error! Please check that rows in excel file are not empty.';
	$arrayForFrontEnd += array("errors"=>$errors);
	Die ($jsonFile = json_encode($arrayForFrontEnd));	
}

$numberOfMonths value will be used to determine how many iterations the inner loop must perform.

Finding distinct values for all months

Second step was to find all distinct values for all months from the yearMonthDate column.

I must have all the distinct values for every month from the yearMonthDate column. This was achieved by running an DISTINCT(yearMonthDate) SQL query against database and then all month values were stored in $distinctMonths array. This array will be used in the inner loop.

//an array to store month values
$distinctMonths = array();
//a query to select distinct month values 
$querySelectDistinctMonths = "
	SELECT 
		DISTINCT(yearMonthDate) AS distinctMonths
	FROM ChepReport
		WHERE transactionType = 'Transfer In'";
$resultSelectDistinctMonths = mysqli_query($shortageReportDB, $querySelectDistinctMonths);
$num = mysqli_num_rows($resultSelectDistinctMonths);

if ($num>0){
	while ($row = mysqli_fetch_array($resultSelectDistinctMonths, MYSQLI_ASSOC))
	{	
		$monthValue = $row['distinctMonths'];
		array_push($distinctMonths, $monthValue);	
	}
} else {
	$errors[] = 'Error! Please check that rows in excel file are not empty.';
	$arrayForFrontEnd += array("errors"=>$errors);
	Die ($jsonFile = json_encode($arrayForFrontEnd));	
}

Finding the number of suppliers

As the $resultArray already has all the suppliers names as they are used as keys in it, I just need to find the length of this array. The length will be equal to the number of distinct suppliers names. This value will be used to determine how many iterations the outer loop must make.

$numberOfSuppliers = Count($resultArray);

Finding distinct suppliers names

To find distinct suppliers names values an SQL query was run against database, and results were stored in a $distinctSuppliersNamesArray array.

//an array to store month values
$distinctSuppliersNamesArray = array();
//a query to select distinct month values 
$querySelectDistinctSuppliersNames = "
	SELECT 
		DISTINCT(otherParty) AS distinctSupplierName
	FROM ChepReport
		WHERE transactionType = 'Transfer In'";
$resultSelectDistinctSuppliersNames = mysqli_query($shortageReportDB, $querySelectDistinctSuppliersNames);
$num = mysqli_num_rows($resultSelectDistinctSuppliersNames);

if ($num>0){
	while ($row = mysqli_fetch_array($resultSelectDistinctSuppliersNames, MYSQLI_ASSOC))
	{	
		$distinctSupplierName = $row['distinctSupplierName'];
		array_push($distinctSuppliersNamesArray, $distinctSupplierName);	
	}
} else {
	$errors[] = 'Error! Please check that rows in excel file are not empty.';
	$arrayForFrontEnd += array("errors"=>$errors);
	Die ($jsonFile = json_encode($arrayForFrontEnd));	
}

$distinctSuppliersNamesArray array will be used inside the inner loop.

Coding the loops

Once all required data for nested loops is retrieved (number of months, number of suppliers, distinct suppliers names and distinct month values) these nests can be created. The logic behind this is simple: loop trough every supplier and by using array_key_exists() function to determine if a given month exists in an array, decide what to do: if it does not exist a key-value pair will be added to this suppliers array (month value as a key, and 0 as a value).

For example in the data structure in the above picture, on a third outer loop iteration and second inner loop iteration, there is no month with value 202306 for supplier 3, which means a key with value 202306 does not exist and it needs to be filed. The code for these loops looks like this:

for ($i = 0; $i <$numberOfSuppliers; $i++){
	
	//in this loop I will check if each supplier has a quantity of pallets for each month
	//I will do it by checking if an array key exists for the month, if it does, 
	//nothing will be done, if it does not, a month value will be added as a key and 0 as it's value.
	for ($x = 0; $x <$numberOfMonths; $x++){
		if(!array_key_exists($distinctMonths[$x], $resultArray[$distinctSuppliersNamesArray[$i]])){
			$resultArray[$distinctSuppliersNamesArray[$i]][$distinctMonths[$x]]= 0;
		}		
	}
}

One more thing I learned while writing these loops is that there is no need to use array_push() function if only one value is being inserted into the array.

$resultArray[$distinctSuppliersNamesArray[$i]][$distinctMonths[$x]]= 0;

I also applied this approach to the code I wrote earlier where I couldn’t figure out where it picks up yearMonthDate value:

array_push($resultArray[$supplierName], $monthlySum);
$resultArray[$supplierName][$yearMonthDate]= $monthlySum;

Rounding monthly sum of pallets

One more thing I’ve done is I rounded $monthlySum variable. I remembered that after I do that in PHP, the numeric value is not being sent to front end wrapped in quotes, which might turn out to be useful when drawing a chart.

$monthlySum = ROUND($row['monthlySum'],0);

Before the results at front end would look like this:

After using the ROUND() function the “” are removed from the monthly quantity of pallets, and the result looks like this:

At the beginning of this post I mentioned that the excel file contains 6 months worth of transfers, however only 4 months get pick up by SQL queries. I will explain why this is in my next post 😉

Github.com repository has been updated.