Work On Flickerbook Holiday Summary Part 2

I improved the holiday summary for Flickerbook and now it combines consecutive holiday dates into one date that shows the start and the end of holidays. Now it produces a much more convenient summary table:

In order to achieve this had to write additional code in the PHP file. I declared and used these new variables:

$iterationCounter: is set to 0. On the first iteration of the while loop, once the data is retrieved from database, it is used to update variables $previuosHolidayDate and $previuosHolidayValue whose values are later used to compare dates and determine if they are consecutive or not.
$previuosHolidayDate: stores a date value which was in used in the previous iteration of the while loop.
$previuosHolidayValue: stores a previous holiday value. I need this variable to determine if the consecutive holidays are full holiday days or not. If a half day is used I want to separate it as a single date in order to identify it. In other words I don’t want it to be added to consecutive holidays concatenated date, as it would make it impossible to determine which date was half holiday.
$startDateForConsecutiveHolidays: start date for consecutive holidays.
$endDateForConsecutiveHolidays: end date for consecutive holidays.
$combinedValue: concatenated date that shows the start and end dates of consecutive holidays.
$consecutiveHolidaysEnded: default value is set to true. I require this variable because it indicates if consecutive holidays have ended. If the date comparison returns true, this $consecutiveHolidaysEnded is set to false. If the date comparison returns false (means that the dates compared are not consecutive) and the $consecutiveHolidaysEnded value is false, it means that start and and dates have to be concatenated, and the $consecutiveHolidaysEnded will be reset to true.
$combinedValue: a variable that stores sum of total consecutive holiday days.

In order to compare dates, I convert both dates to UNIX timestamp seconds using strtotime() function , and compare the difference of seconds between two dates to 86400s, which is equal to the amount of seconds in one day.

$currentDateMS = strtotime($holidayDate);
$previuosHolidayDateMS = strtotime($previuosHolidayDate);
if (($currentDateMS - $previuosHolidayDateMS) == 86400) {
     //CODE TO EXECUTE
}

The whole code for fetching data and concatenating consecutive holiday dates is demonstrated below:

//QUERY TO SELECT HOLIDAYS USED
//if daytype is 4 holidayValue will be set to 0.5 as it is half day, other dayTypes will be assign a value of 1 for full day.
$querySelectHolidayDaysUsed = "
	SELECT
		date AS holidayDate,
		CASE WHEN dayType = '4' THEN '0.5'
		ELSE '1'
		END AS holidayValue
	FROM day_indexes 
		WHERE user_id = '$user_id'
		AND jobID ='$jobID'
		AND date BETWEEN '$holidayStart' AND '$currentDate'
		AND dayType IN ( '3','4','5')
	GROUP BY
		date
	ORDER BY
		date"; 
$resultSelectHolidayDaysUsed = mysqli_query($dayIndexes, $querySelectHolidayDaysUsed);
$numOfRows = mysqli_num_rows($resultSelectHolidayDaysUsed);
$usedHolidaysArray = array();
$iterationCounter = 0;
//previous holiday date and value
$previuosHolidayDate = "";
$previuosHolidayValue = 0;
//a variable to sum day values when holidays are consecutive.
$combinedValue = 1;
$consecutiveHolidaysEnded = true;
//variables to concatenate start and end dates for consecutive holidays.
$startDateForConsecutiveHolidays = "";
$endDateForConsecutiveHolidays = "";
//a variable to store concatenated date value when holidays are consecutive.
$combinedDate = "";
if ($numOfRows>0){
	while ($row = mysqli_fetch_array($resultSelectHolidayDaysUsed, MYSQLI_ASSOC))
	{
		$holidayDate = $row['holidayDate'];
		$holidayValue = $row['holidayValue'];
		//on the first iteration of this while loop I do not add values to $usedHolidaysArray
		//first I fill the previuosHolidayDate and previuosHolidayValue as I need to have two date values in order to compare them.
		if ($iterationCounter != 0) {
			//as I only want to calculate consecutive holiday days for full holiday, means that if between full holidays a half holiday is used,
			//it breaks the consecutiveness of holidays, as I want to be able to determine which days are half days in a series of dates of holidays.
			//so if a half day is used it will be separeted as single day, even if several half days are used in a row.
			// the $previuosHolidayValue == 1 AND $holidayValue == 1 condition determines if there has been a change to half half or not
			//if both the current and the previous values are 1, means no half day was used.
			//if this condition is not met an else statement gets executed.
			if ($previuosHolidayValue == 1 AND $holidayValue == 1) {
				$currentDateMS = strtotime($holidayDate);
				$previuosHolidayDateMS = strtotime($previuosHolidayDate);
				if (($currentDateMS - $previuosHolidayDateMS) == 86400) {
					//if the ($currentDateMS - $previuosHolidayDateMS) == 86400) is true and the $combinedValue == 1
					//it means that the previous holiday date was the first date for consecutive holidays and this value
					//of first date will be stored in a $previuosHolidayDate variable.
					if ($combinedValue == 1) {
						$startDateForConsecutiveHolidays = $previuosHolidayDate;
					}
					$consecutiveHolidaysEnded = false;
					$combinedValue++;
					
				} else {
					//if the ($currentDateMS - $previuosHolidayDateMS) == 86400) is false, I need to determine if this false value is the
					//first one after being true
					//if the previuos value was true, it means that the variable consecutiveHolidaysEnded is still set to false 
					//$consecutiveHolidaysEnded == false in which case it means that I need to concatanate dates for consecutive holidays.
					if ($consecutiveHolidaysEnded == false ) {
						$combinedDate = $startDateForConsecutiveHolidays.' to '.$previuosHolidayDate;
						$combidedDateAndValueArray = array("holidayDate"=>$combinedDate, "holidayValue"=>$combinedValue);
						$usedHolidaysArray[] = $combidedDateAndValueArray;
						//reset the combined value to 1 to calculate the following appearance of consecutive holidays.
						$combinedValue = 1;
						//set the consecutiveHolidaysEnded = true 
						$consecutiveHolidaysEnded = true;
					} else {
						//$combinedValue in this part is always 1 as only one full holiday is being sent.
						$combidedDateAndValueArray = array("holidayDate"=>$previuosHolidayDate, "holidayValue"=>$previuosHolidayValue);
						$usedHolidaysArray[] = $combidedDateAndValueArray;
					}
				}
			//end of ($holidayValue != 0.5)  condition
			} else {
				//if there is a consecutive holidays that are not finished, add the last date to the consecutive holidays
				if ($consecutiveHolidaysEnded == false ) {		
					$combinedDate = $startDateForConsecutiveHolidays.' to '.$previuosHolidayDate;
					$combidedDateAndValueArray = array("holidayDate"=>$combinedDate, "holidayValue"=>$combinedValue);
					$usedHolidaysArray[] = $combidedDateAndValueArray;
				} else {
					//if the condition for consecutive holidays is finished, just add the last date value
					$combidedDateAndValueArray = array("holidayDate"=>$previuosHolidayDate, "holidayValue"=>$previuosHolidayValue);
					$usedHolidaysArray[] = $combidedDateAndValueArray;
				}
				//reset the combined value to 1 to calculate the following appearance of consecutive holidays.
				$combinedValue = 1;
				//set the consecutiveHolidaysEnded = true 
				$consecutiveHolidaysEnded = true;
			}
		//end of this ($iterationCounter != 0) condition
		}
		//calculate how many holidays have been used.
		$holidaysUsedAndBooked+= $row['holidayValue'];
		//before picking up another holiday date, save the current ones value as a previuosHolidayDate.
		$previuosHolidayDate = $holidayDate;
		$previuosHolidayValue = $holidayValue;
		$iterationCounter++;
	}
}
// if $numOfRows is more than 0 it means there were holidays that were retrieved from database.
//as on first iteration of the while loop instead of adding values I stored them in the variables named previous
//it means that on the last iteration last values have not been added to the usedHolidaysArray
if ($numOfRows>0) {
	//if there is a consecutive holidays that are not finished, add the last date to the consecutive holidays
	if ($consecutiveHolidaysEnded == false ) {			
		$combinedDate = $startDateForConsecutiveHolidays.' to '.$previuosHolidayDate;
		$combidedDateAndValueArray = array("holidayDate"=>$combinedDate, "holidayValue"=>$combinedValue);
		$usedHolidaysArray[] = $combidedDateAndValueArray;
	} else {
		//if the condition for consecutive holidays is finished, just add the last date value
		$combidedDateAndValueArray = array("holidayDate"=>$previuosHolidayDate, "holidayValue"=>$previuosHolidayValue);
		$usedHolidaysArray[] = $combidedDateAndValueArray;
	}
	//reset the combined value to 1 to calculate the following appearance of consecutive holidays.
	$combinedValue = 1;
	//set the consecutiveHolidaysEnded = true 
	$consecutiveHolidaysEnded = true;
}

I Updated the code to concatenate consecutive booked holiday days in the same way, but I will not post it, as it is identical code, just different variable names. Now will start working on totals calculator!

October 16, 2023

Leave a Reply Cancel reply