by: adminPosted on:

Adding Holiday Summary Feature To Flickerbook

As I mentioned in the previous post, this holiday summary feature will be the first thing I will be working on. I have already done the HTML, CSS, PHP and SQL coding and in this post I will explain the logic behind it.

HTML and CSS

First I added a FontAwesome icon next to “Holidays” name.

<div class="col-sm-10 col-xs-8 P0">
	Holidays
	<span id="holidaySummaryButton">
		<i class="fas fa-bars clickable" data-toggle="modal" data-target="#holidaySummary"></i>
	</span>
</div>

Now this icon appears on website and once clicked it loads the modal.

Holiday summary button.

As the modal currently does not load any data retrieved from database, I just structured it with a sample HTML table, which later will be generated using JavaScript. The HTML code for this table is below.

<div id="holidaySummary" class="modal fade" role="dialog" data-backdrop="false">
	<div class="modal-dialog">
		<div class="modal-content">
			<div class="modal-header">
				<button type="button" class="close" data-dismiss="modal">&times;</button>
			<div class="modal-title">Holiday Summary</div>
		</div>
		<div class="modal-body P0" id="holidaySummaryBody">
			<table id="holidaySummaryTable">
				<tr>
					<th colspan="2">Holidays Used</th>
				</tr>
				<tr>
					<th class="width80">Date</th>
					<th class="width20">Days</th>
				</tr>
				<tr>
					<td class="width80 holidayUsedColor">2023-10-15</td>
					<td class="width20 holidayUsedColor">1</td>
				</tr>
				<tr>
					<td class="width80 holidayUsedColor">2023-10-16</td>
					<td class="width20 holidayUsedColor">1</td>
				</tr>
				<tr>
					<td class="width80 TARI">Total Holidays Used</td>
					<td class="width20">2</td>
				</tr>					
					<tr>
					<th colspan="2">Holidays Booked</th>
				</tr>				  
				<tr>
					<th class="width80">Date</th>
					<th class="width20">Days</th>
				</tr>
				<tr>
					<td class="width80 holidayBookedColor">2024-02-09</td>
					<td class="width20 holidayBookedColor">1</td>
				</tr>
				<tr>
					<td class="width80 holidayBookedColor">2024-02-10</td>
					<td class="width20 holidayBookedColor">1</td>
				</tr>			
				<tr>
					<td class="width80 TARI">Total Holidays Booked</td>
					<td class="width20">2</td>
				</tr>
			</table>
		</div>
		<div class="modal-footer marB2">
			<button type="button" class="btn btn-default" data-dismiss="modal">Close</button>
		</div>
	</div>
</div>

Once a JavaScript code that generates and fills a table with data is coded, this table in HTML file will be deleted. I now just need it as an example how to compose the table. This table is styled using this CSS code:

.width80 {
  width: 80%;
}
.width20 {
  width: 20%;
}
#holidaySummaryTable tr th {
  text-align: center;
  border: 2px solid grey;
}
#holidaySummaryTable tr td {
  text-align: center;
  border: 1px solid grey;
}
#holidaySummaryTable{
  width: 100%;
  border: 2px solid grey;
}
#holidaySummaryBody{
  padding: 3% 10%;
}
.holidayUsedColor {
  background-color:#99ff99
}
.holidayBookedColor {
  background-color: #ccffcc;
}
#holidaySummaryTable tr{
  background-color: #e6e6e6;
}
//holidays used and booked titles
#holidaySummaryTable tr [colspan="2"] {
  background-color: #cccccc;
}
.TARI {
  text-align: right !important;
  padding-right: 2%;
}

And the image below demonstrates how I want this table to look once loaded.

Holiday summary table inside modal.

Once the data is retrieved from back end I will write JavaScript code that will generate table in this structure and load it with relevant data.

PHP and SQL

At back and I wrote this script that selects dates for holidays used and booked, counts holidays used, booked and still available to use. As it is possible to book half day holiday, I included an SQL CASE expression in two queries to count the holiday used as either 1 for full holiday or 0.5 for half holiday.

Some variables in this script were picked by requiring external PHP files, but I’m not gonna include them in this post. I left comments what variables these external files provide to this script. In grey background I marked SQL queries.

<?php
//loadIndexesArray is declared in this file and $user_id value is picked in this file
require('postCheck.php');
//jobID comes from here. I need job id in order to query database
require ('selectJobID.php');
//from this file I pick up $holidayStart and $holidaysPerYear variables from payment_settings table
require('holidayCalculatorStart.php');
//connect to database
require('/../connectDB.php');
//this query below determines the end date for holiday year
$querySelectHolidayEnd = "SELECT DATE_ADD('$holidayStart',INTERVAL 1 YEAR) AS holidayEnd"; 
$resultSelectHolidayEnd = mysqli_query($dayIndexes, $querySelectHolidayEnd);
$numOfRows = mysqli_num_rows($resultSelectHolidayEnd);

if ($numOfRows>0){
	while ($row = mysqli_fetch_array($resultSelectHolidayEnd, MYSQLI_ASSOC))
	{$holidayEnd = $row['holidayEnd'];}
} else { $holidayEnd = "Not found";}

//this script picks up current date in a $currentDate variable
require('determineCurrentDate.php');

//this is a variable which counts how many holidays have been booked and used.
//I need it in order to determine how many holidays are left unused.
$holidaysUsedAndBooked = 0;

//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 >= '$holidayStart'
		AND date < '$currentDate'
		AND dayType IN ( '3','4','5')
	GROUP BY
		date
	ORDER BY
		date"; 
$resultSelectHolidayDaysUsed = mysqli_query($dayIndexes, $querySelectHolidayDaysUsed);
$numOfRows = mysqli_num_rows($resultSelectHolidayDaysUsed);

//array to store used holidays data: date and day value (0.5 or 1).
$usedHolidaysArray = array();
$iterationCounter = 0;
if ($numOfRows>0){
	while ($row = mysqli_fetch_array($resultSelectHolidayDaysUsed, MYSQLI_ASSOC))
	{
		$usedHolidaysArray += array($iterationCounter=>$row);
		//calculate how many holidays have been used.
		$holidaysUsedAndBooked+= $row['holidayValue'];
		$iterationCounter++;
	}
}
//a sum of total holidays used at this point in the script is equal to variable $holidaysUsedAndBooked
$holidaysUsed = $holidaysUsedAndBooked;
//QUERY TO SELECT HOLIDAYS BOOKED
//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.
$querySelectHolidayDaysBooked = "
	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 >= '$currentDate'
		AND date < '$holidayEnd'
		AND dayType IN ( '3','4','5')
	GROUP BY
		date
	ORDER BY
		date"; 
$resultSelectHolidayDaysBooked = mysqli_query($dayIndexes, $querySelectHolidayDaysBooked);
$numOfRows = mysqli_num_rows($resultSelectHolidayDaysBooked);

//array to store booked holidays data: date and day value (0.5 or 1).
$bookedHolidaysArray = array();
$iterationCounter = 0;
if ($numOfRows>0){
	while ($row = mysqli_fetch_array($resultSelectHolidayDaysBooked, MYSQLI_ASSOC))
	{
		$bookedHolidaysArray += array($iterationCounter=>$row);
		//calculate how many holidays have been used.
		$holidaysUsedAndBooked+= $row['holidayValue'];
		$iterationCounter++;
	}
}
//a sum of total holidays booked
$holidaysBooked = $holidaysUsedAndBooked - $holidaysUsed;
//Calculate holidays that are still available to book
$availableHolidays = $holidaysPerYear - $holidaysUsedAndBooked; 
mysqli_close($dayIndexes);
$loadIndexesArray += array ("errors"=>$errors,"usedHolidaysArray"=>$usedHolidaysArray, "bookedHolidaysArray"=>$bookedHolidaysArray);
$loadIndexesArray += array ("availableHolidays"=>$availableHolidays,"holidaysBooked"=>$holidaysBooked, "holidaysUsed"=>$holidaysUsed );
$jsonFile = json_encode($loadIndexesArray);
echo $jsonFile;
?>

If I console.log() the bookedHolidaysArray and usedHolidaysArray in Chrome DevTools I can see that data is organised in a way that will enable me to loop through it with JavaScript. When I was working with CHEP pallets report, I created an object where the first key was not a numeric value, so this time I structured the data properly in order to use loops iteration count to access every line on object.

I will discuss JavaScript functions that fetch data from back end, generate table and load data in to it in next post.

Leave a Reply

Your email address will not be published. Required fields are marked *