I’ve been doing a lot of work recently on Flickerbook, both on back end and front end. I will now post some SQL examples as I finally got myself to work with SQL JOINS. In Flickerbook for weekly payments, deductions and miss payments I have 3 tables in a database and they all have a primary key that consists of 3 columns: user_id, taxPeriodNr and jobID. So used a LEFT JOIN to join deductions and payments tables, as they both have the same number of rows and a LEFT JOIN to join miss payments tables, as this table will only have a rows inserted if a miss payment occurs, which doesn’t happen very often.
SQL And PHP
So the query below is so far the longest I have ever written, as it joins deductions, payments and missed payments. Before using SQL JOIN I would query deduction and payments tables separately. And mis payments table would only get queried if a column ‘misspaymentCheck‘ had a value of 1 in weekly_payments_amount table. Then the results from miss payments would be added in PHP to main payment. So this JOIN query makes things less complicated to keep an eye on 😉
require('../weeklyPayDed_connectDB.php');
$querySelectPaymentsSum = "
SELECT
COUNT(*) AS taxPeriodQuantity,
COALESCE(SUM(gross_pay),0) AS gross_paySum,
COALESCE(SUM(basic_pay),0) + COALESCE(SUM(missBasicPay),0) AS basicPaySum,
COALESCE(SUM(ot1_pay),0) + COALESCE(SUM(missOT1Pay), 0) AS ot1_paySum,
COALESCE(SUM(ot2_pay),0) + COALESCE(SUM(missOT2Pay),0) AS ot2_paySum,
COALESCE(SUM(hol_pay),0) + COALESCE(SUM(missHolidayPay),0) AS hol_paySum,
COALESCE(SUM(enhol_pay),0) + COALESCE(SUM(missEnHolidayPay),0) AS enhol_paySum,
COALESCE(SUM(bhol_pay),0) + COALESCE(SUM(missBHolPay),0) AS bhol_paySum,
COALESCE(SUM(bhol_bonus),0) + COALESCE(SUM(missBHolBonus),0) AS bhol_bonusSum,
COALESCE(SUM(sick_pay),0) + COALESCE(SUM(missSicknessPay),0) AS sick_paySum,
COALESCE(SUM(fam_pay),0) + COALESCE(SUM(missPaternityPay),0) AS fam_paySum,
COALESCE(SUM(ber_pay),0) + COALESCE(SUM(missBerPay),0) AS ber_paySum,
COALESCE(SUM(comp_pay),0) + COALESCE(SUM(missCompPay),0) AS comp_paySum,
COALESCE(SUM(SSP),0) AS SSP_Sum,
COALESCE(SUM(SPP),0) AS SPP_Sum,
COALESCE(SUM(satExtraPay),0) + COALESCE(SUM(missSatPay),0) AS saturdayExtraPaySum,
COALESCE(SUM(sunExtraPay),0) + COALESCE(SUM(missSunPay),0) AS sundayExtraPaySum,
COALESCE(SUM(pieceWork),0) AS pieceWorkSum,
COALESCE(SUM(basic_h),0) + COALESCE(SUM(missBasicHours),0) AS basicHoursSum,
COALESCE(SUM(ot1_units),0) + COALESCE(SUM(missOT1Hours),0) AS ot1_unitsSum,
COALESCE(SUM(ot2_units),0) + COALESCE(SUM(missOT2Hours),0) AS ot2_unitsSum,
COALESCE(SUM(hol_units),0) + COALESCE(SUM(missHolidayHours),0) AS hol_unitsSum,
COALESCE(SUM(enhol_units),0) + COALESCE(SUM(missEnHolidayHours),0) AS enhol_unitsSum,
COALESCE(SUM(bhol_units),0) + COALESCE(SUM(missBHolHours),0) AS bhol_unitsSum,
COALESCE(SUM(sick_units),0) + COALESCE(SUM(missSicknessHours),0) AS sick_unitsSum,
COALESCE(SUM(fam_units),0) + COALESCE(SUM(missPaternityHours),0) AS fam_unitsSum,
COALESCE(SUM(ber_units),0) + COALESCE(SUM(missBerHours),0) AS ber_unitsSum,
COALESCE(SUM(comp_units),0) + COALESCE(SUM(missCompHours),0) AS comp_unitsSum,
COALESCE(SUM(satExtraH),0) + COALESCE(SUM(missSatHours),0) AS saturdayHoursSum,
COALESCE(SUM(sunExtraH),0) + COALESCE(SUM(missSunHours),0) AS sundayHoursSum,
COALESCE(SUM(uns_prem),0) + COALESCE(SUM(missUnsocPay),0) AS uns_premSum,
COALESCE(SUM(uns_prem_un),0) + COALESCE(SUM(missUnsocHours),0) AS uns_prem_unSum,
COALESCE(SUM(uns_hol),0) + COALESCE(missUnsocHolPay,0) AS uns_holSum,
COALESCE(SUM(taxablePay),0) AS taxablePaySum,
COALESCE(SUM(uns_hol_un),0) + COALESCE(SUM(missUnsocHolHours),0) AS uns_hol_unSum,
COALESCE(SUM(uns_sick),0) + COALESCE(SUM(missUnsocSickPay),0) AS uns_sickSum,
COALESCE(SUM(uns_sick_un),0) + COALESCE(SUM(missUnsocSickHours), 0) AS uns_sick_unSum,
COALESCE(SUM(uns_family),0) + COALESCE(SUM(missUnsocPaterntityPay),0) AS uns_familySum,
COALESCE(SUM(uns_family_un),0)+ COALESCE(SUM(missUnsocPaterntityHours),0) AS uns_family_unSum,
COALESCE(SUM(uns_ber),0) + COALESCE(SUM(missUnsocBerPay),0) AS uns_berSum,
COALESCE(SUM(uns_ber_un),0) + COALESCE(SUM(missUnsocBerHours),0) AS uns_ber_unSum,
COALESCE(SUM(uns_comp),0) + COALESCE(SUM(missUnsocCompPay),0) AS uns_compSum,
COALESCE(SUM(uns_comp_un),0) + COALESCE(SUM(missUnsocCompHours),0) AS uns_comp_unSum,
COALESCE(SUM(add_pay2),0) AS add_pay2Sum,
COALESCE(SUM(add_pay3),0) AS add_pay3Sum,
COALESCE(SUM(add_pay),0) AS add_paySum,
COALESCE(SUM(payback),0) AS paybackSum,
COALESCE(SUM(holidayPay),0) AS holidayPaySum,
COALESCE(SUM(SAP),0) AS SAPSum,
COALESCE(SUM(salary),0) AS salarySum,
COALESCE(SUM(bonus),0) AS bonusSum,
COALESCE(SUM(commissions),0) AS commissionsSum,
COALESCE(SUM(redundancy),0) AS redundancySum,
COALESCE(SUM(taxFreePay),0) AS taxFreePaySum,
COALESCE(SUM(taxFreePay2),0) AS taxFreePay2Sum,
COALESCE(SUM(taxFreePay3),0) AS taxFreePay3Sum,
COALESCE(SUM(unpaidBreaks),0) AS unpaidBreaksSum,
COALESCE(SUM(unpaidBreaks2),0) AS unpaidBreaks2Sum,
COALESCE(SUM(fur_pay),0) + COALESCE(SUM(missFurPay),0) AS fur_paySum,
COALESCE(SUM(fur_units),0) + COALESCE(SUM(missFurHours),0) AS fur_unitsSum,
COALESCE(SUM(uns_fur),0) + COALESCE(SUM(missUnsocFurPay),0) AS uns_furSum,
COALESCE(SUM(uns_fur_un),0)+ COALESCE(SUM(missUnsocFurHours),0) AS uns_fur_unSum,
COALESCE(SUM(furloughPayment),0) AS furloughPaymentSum,
COALESCE(SUM(basic_h),0) + COALESCE(SUM(ot1_units),0) + COALESCE(SUM(ot2_units),0) AS paidHoursSpentAtWork,
COALESCE(SUM(redundancy),0) + COALESCE(SUM(taxFreePay),0) + COALESCE(SUM(taxFreePay2),0) + COALESCE(SUM(taxFreePay3),0) AS totalTaxFreePaySum,
COALESCE(SUM(redundancy),0) + COALESCE(SUM(taxFreePay),0) + COALESCE(SUM(taxFreePay2),0) + COALESCE(SUM(taxFreePay3),0) + COALESCE(SUM(taxablePay),0) AS totalPayments,
COALESCE(SUM(tax),0) + COALESCE(SUM(taxAdj),0) AS taxSum,
COALESCE(SUM(NI),0), + COALESCE(SUM(NIAdj),0) AS NISum,
COALESCE(SUM(union_de),0) AS union_deSum,
COALESCE(SUM(pension),0) AS pensionSum,
COALESCE(SUM(other_de),0) AS other_de,
COALESCE(SUM(net_pay),0) AS netPaySum,
COALESCE(SUM(pensionAmountEmp),0) AS pensionEmpSum,
COALESCE(SUM(comp_loan),0) AS companyLoanSum,
COALESCE(SUM(stud_loan),0) AS studentLoanDeductionSum,
COALESCE(SUM(add_ded2),0) AS add_deSum2,
COALESCE(SUM(add_ded3),0) AS add_deSum3,
COALESCE(SUM(travel),0) AS travelDeductionSum,
COALESCE(SUM(taxFreeDed1),0) AS taxFreeDeduction1Sum,
COALESCE(SUM(taxFreeDed2),0) AS taxFreeDeduction2Sum,
COALESCE(SUM(taxFreeDed3),0) AS taxFreeDeduction3Sum,
COALESCE(SUM(pension),0) + COALESCE(SUM(pensionAmountEmp),0) AS totalPension,
COALESCE(SUM(travel),0)+COALESCE(SUM(taxFreeDed1),0)+ COALESCE(SUM(taxFreeDed2),0) + COALESCE(SUM(taxFreeDed3),0) AS totalPreTaxDeductionSum
FROM weekly_payments_amount
INNER JOIN weekly_deductions_amount
ON weekly_payments_amount.user_id=weekly_deductions_amount.user_id
AND weekly_payments_amount.jobID=weekly_deductions_amount.jobID
AND weekly_payments_amount.taxPeriodNr=weekly_deductions_amount.taxPeriodNr
LEFT JOIN weekly_missPayments
ON weekly_payments_amount.user_id=weekly_missPayments.user_id
AND weekly_payments_amount.jobID=weekly_missPayments.jobID
AND weekly_payments_amount.taxPeriodNr=weekly_missPayments.taxPeriodNr
WHERE weekly_payments_amount.user_id = '$user_id'
AND weekly_payments_amount.taxPeriodNr BETWEEN '$firstTaxPeriodOfYear' AND '$taxPeriodNumber'
AND weekly_payments_amount.employer = '$employer'
AND weekly_payments_amount.job_title = '$jobTitle'";
$resultSelectPaymentsSum = mysqli_query($weeklyPaymentsDeductions, $querySelectPaymentsSum);
$num = mysqli_num_rows($resultSelectPaymentsSum);
if ($num>0){
while ($row = mysqli_fetch_array($resultSelectPaymentsSum, MYSQLI_ASSOC))
{
//assign query results to PHP variables.
require('payDedValues/payDedValues.php');
}
}else{
//if no results assign 0.00 to PHP variables.
require('payDedValues/payDedValues0.php');
}
PHP For Hours Table
When adding results in PHP I would encounter issues when rounding the results. When results from SQL are assigned to PHP variable they are of string type, summing them would sometimes produce a very long number after decimal like “5”+”7″ = 12.00000039 and to get around it I used a PHP ROUND() function. However the ROUND() function caused another issue: for example if the number is 14.50 after rounding it it would lose the zero and come out as 14.5. As at the front end I want to maintain symmetry I want the 0 to be displayed. So to sort this issue I used a toFixed() method in JavaScript. Now when I send data as it is retrieved from database, it is always in proper decimal values and shows zeros as well so I can discard both the ROUND() function at back end and the toFixed() method at front end, which reduces the possibility of an error, as toFixed() only works on numbers and after last PHP upgrade sending NULL values to ROUND() was deprecated. And both of these have caused me a lot of issues in the past.
Once I have retrieved data from database, I need to organise it for front end. I do it in separate scripts where I store hour values and names in a multidimensional array. In this post I will showcase how data was organised for hours and days tables. The code below stores data for hours: hoursArrayForTable array consists of 6 inner arrays, that contain different types of hours. I chose this structure, because with JavaScript I have a function that iterates through both dimensions, and for every inner array, as long as it has at least one value that is greater then zero, it separates this section in the table by drawing a top border line.
Every inner array consists of at least one associative array that store values for: days name, days quantity, and the text for either “day” or “days”. Previously I would add the names and text for days in JavaScript, but now I moved this to back end, which I believe is a better way to do, as I want to reduce the size of JavaScript file and move as much decision making to back end as possible.
$hoursArrayForTable = array();
//to make sure hours are not 0 I will add all hours retrieved to allHoursSum variable.
$allHoursSum = 0;
$symbolH = "h";
if($basicHoursSum>0){$allHoursSum+=$basicHoursSum;}
if($ot1_unitsSum>0){$allHoursSum+=$ot1_unitsSum;}
if($hol_unitsSum>0){$allHoursSum+=$hol_unitsSum;}
if($ot2_unitsSum>0){$allHoursSum+=$ot2_unitsSum;}
if($enhol_unitsSum>0){$allHoursSum+=$enhol_unitsSum;}
if($unpaidBreaksSum>0){$allHoursSum+=$unpaidBreaksSum;}
if($unpaidBreaks2Sum>0){$allHoursSum+=$unpaidBreaks2Sum;}
if($sick_unitsSum>0){$allHoursSum+=$sick_unitsSum;}
if($fam_unitsSum>0){$allHoursSum+=$fam_unitsSum;}
if($ber_unitsSum>0){$allHoursSum+=$ber_unitsSum;}
if($comp_unitsSum>0){$allHoursSum+=$comp_unitsSum;}
if($fur_unitsSum>0){$allHoursSum+=$fur_unitsSum;}
//to avoid deletion from zero
if($allHoursSum>0) {
//SECTION 1
if($totalHoursWorked != 0 OR $basicHoursSum != 0 OR $unpaidBreaksSum != 0 OR $unpaidBreaks2Sum != 0) {
$hoursInnerArray1 = array ();
if($totalHoursWorked != 0) {$hoursInnerArray1[] = array ("earnings"=>$totalHoursWorked,"name"=>"Hours Spent At Work","symbol"=>$symbolH);}
if($paidHoursSpentAtWork != 0) {$hoursInnerArray1[] = array ("earnings"=>$paidHoursSpentAtWork,"name"=>"Paid Hours Spent At Work","symbol"=>$symbolH);}
if($unpaidBreaksSum != 0) {$hoursInnerArray1[] = array ("earnings"=>$unpaidBreaksSum,"name"=>"Unpaid Break Hours For Day In","symbol"=>$symbolH);}
if($unpaidBreaks2Sum != 0) {$hoursInnerArray1[] = array ("earnings"=>$unpaidBreaks2Sum,"name"=>"Unpaid Break Hours Other","symbol"=>$symbolH);}
$hoursArrayForTable[] = $hoursInnerArray1;
}
//SECTION 2
if($basicHoursSum != 0 OR $uns_prem_unSum != 0 OR $ot1_unitsSum != 0 OR $ot2_unitsSum != 0) {
$hoursInnerArray2 = array ();
if($basicHoursSum != 0) {$hoursInnerArray2[] = array ("earnings"=>$basicHoursSum,"name"=>"Basic Hours","symbol"=>$symbolH);}
if($uns_prem_unSum != 0) {$hoursInnerArray2[] = array ("earnings"=>$uns_prem_unSum,"name"=>"Unsocial Basic Hours","symbol"=>$symbolH);}
if($ot1_unitsSum != 0) {$hoursInnerArray2[] = array ("earnings"=>$ot1_unitsSum,"name"=>"Overtime 1 Hours","symbol"=>$symbolH);}
if($ot2_unitsSum != 0) {$hoursInnerArray2[] = array ("earnings"=>$ot2_unitsSum,"name"=>"Overtime 2 Hours","symbol"=>$symbolH);}
$hoursArrayForTable[] = $hoursInnerArray2;
}
//SECTION 3
if ($hol_unitsSum != 0 OR $enhol_unitsSum != 0 OR $uns_hol_unSum != 0 OR $bhol_unitsSum != 0) {
$hoursInnerArray3 = array ();
if($hol_unitsSum != 0) {$hoursInnerArray3[] = array ("earnings"=>$hol_unitsSum,"name"=>"Holiday Hours","symbol"=>$symbolH);}
if($enhol_unitsSum != 0) {$hoursInnerArray3[] = array ("earnings"=>$enhol_unitsSum,"name"=>"Enhanced Holiday Hours","symbol"=>$symbolH);}
if($uns_hol_unSum != 0) {$hoursInnerArray3[] = array ("earnings"=>$uns_hol_unSum,"name"=>"Unsocial Holiday Hours","symbol"=>$symbolH);}
if($bhol_unitsSum != 0) {$hoursInnerArray3[] = array ("earnings"=>$bhol_unitsSum,"name"=>"Bank Holiday Hours","symbol"=>$symbolH);}
$hoursArrayForTable[] = $hoursInnerArray3;
}
//SECTION 4
if($saturdayHoursSum != 0 OR $sundayHoursSum != 0) {
$hoursInnerArray4 = array ();
if($saturdayHoursSum != 0) {$hoursInnerArray4[] = array ("earnings"=>$saturdayHoursSum,"name"=>"Saturday Hours","symbol"=>$symbolH);}
if($sundayHoursSum != 0) {$hoursInnerArray4[] = array ("earnings"=>$sundayHoursSum,"name"=>"Sunday Hours","symbol"=>$symbolH);}
$hoursArrayForTable[] = $hoursInnerArray4;
}
//SECTION 5
if($uns_sick_unSum != 0 OR $sick_unitsSum != 0 OR $uns_family_unSum != 0 OR $fam_unitsSum != 0) {
$hoursInnerArray5 = array ();
if($uns_sick_unSum != 0) {$hoursInnerArray5[] = array ("earnings"=>$uns_sick_unSum,"name"=>"Unsocial Sickness Hours","symbol"=>$symbolH);}
if($sick_unitsSum != 0) {$hoursInnerArray5[] = array ("earnings"=>$sick_unitsSum,"name"=>"Paid Sickness Hours","symbol"=>$symbolH);}
if($uns_family_unSum != 0) {$hoursInnerArray5[] = array ("earnings"=>$uns_family_unSum,"name"=>"Unsocial Parental Hours","symbol"=>$symbolH);}
if($fam_unitsSum != 0) {$hoursInnerArray5[] = array ("earnings"=>$fam_unitsSum,"name"=>"Parental Leave Hours","symbol"=>$symbolH);}
$hoursArrayForTable[] = $hoursInnerArray5;
}
//SECTION 6
if($uns_ber_unSum != 0 OR $ber_unitsSum != 0 OR $uns_comp_unSum != 0 OR $comp_unitsSum != 0 OR $uns_fur_unSum != 0 OR $fur_unitsSum != 0) {
$hoursInnerArray6 = array ();
if($uns_ber_unSum != 0) {$hoursInnerArray6[] = array ("earnings"=>$uns_ber_unSum,"name"=>"Unsocial Bereav. Hours","symbol"=>$symbolH);}
if($ber_unitsSum != 0) {$hoursInnerArray6[] = array ("earnings"=>$ber_unitsSum,"name"=>"Bereavement Leave Hours","symbol"=>$symbolH);}
if($uns_comp_unSum != 0) {$hoursInnerArray6[] = array ("earnings"=>$uns_comp_unSum,"name"=>"Unsocial Compass. Hours","symbol"=>$symbolH);}
if($comp_unitsSum != 0) {$hoursInnerArray6[] = array ("earnings"=>$comp_unitsSum,"name"=>"Compassionate Leave Hours","symbol"=>$symbolH);}
if($uns_fur_unSum != 0) {$hoursInnerArray6[] = array ("earnings"=>$uns_fur_unSum,"name"=>"Unsocial Furl. Hours","symbol"=>$symbolH);}
if($fur_unitsSum != 0) {$hoursInnerArray6[] = array ("earnings"=>$fur_unitsSum,"name"=>"Furlough Hours","symbol"=>$symbolH);}
$hoursArrayForTable[] = $hoursInnerArray6;
}
$loadIndexesArray += array('hoursArrayForTable'=>$hoursArrayForTable);
} else {
//empty array for hours
$hoursInnerArray1[] = array("earnings"=>0, "name"=>"Paid Hours Spent At Work", "symbol"=>$symbolH);
$hoursArrayForTable[] = $hoursInnerArray1;
$loadIndexesArray += array ('hoursArrayForTable'=>$hoursArrayForTable);
}PHP For Days Table
To store days data in a multidimensional array I used arrays that contain day names and day colours. Then depending on results I assign day name, day amount and day colour values to dayTypeArrayForTable inner arrays using for loop. What complicated this looping was that a full holiday day can have two values: Holiday and Half enhanced/Half holiday values, So had to find a way to combine them into 1 value.
//a function tat determines if text need's to be of value day (for example for 1 day) or days (for everything else the 1 day).
function dayDays ($dayAmount) {
//default value is " days"
$dayText = " days";
if ($dayAmount == 1) {$dayText = " day";}
return $dayText;
}
// day colour values
$notSelectedColor = '#b3daff';
$dayOffColor = '#c3c3a2';
$halfInHalfOffColor = '#e6ffb3';
$unpaidHolColor = '#e6ffe6';
$dayInSickColor = '#ffcccc';
$absenceColor = '#ccebff';
$strikeColor = '#ff8080';
$dayInColor = '#e6e600';
$holidayColor = '#009900';
$sicknessColor = '#ff9999';
$familyLeaveColor = '#ffa64d';
$bereavementColor = '#000033';
$compassionateColor = '#ffce99';
$furloughColor = '#c2efef';
//day types names full text for result tables
//thought dayTypeCount is the length of this array below.
$dayTypesNamesText = array("Days Not Defined", "Days In", "Days Off", "Holidays", "Half Days In/Half Holidays", "Holidays", "Unpaid Holidays",
"Days In/Sickness", "Days On Sick","Days On Absence", "Parental Leave Days","Bereavement Leave Days", "Compassionate Days","Strike Days", "Furlough Days");
//number of day types available.
$dayTypeCount = COUNT($dayTypesNamesText); //second holiday color will be skipped in a loop, //just need it to make the loops of same length //dayTypesNamesText and dayStatisticsColorArray
$dayStatisticsColorArray = array($notSelectedColor, $dayInColor, $dayOffColor, $holidayColor, $halfInHalfOffColor, $holidayColor, $unpaidHolColor, $dayInSickColor,
$sicknessColor, $absenceColor, $familyLeaveColor, $bereavementColor, $compassionateColor, $strikeColor, $furloughColor);
$resultSelectDay = mysqli_query($dayIndexes, $querySelectDay);
$num11 = mysqli_num_rows($resultSelectDay);
$groupedResultFromDatabase = array();
//need total days to determine if a chart needs to be rendered.
$totalDays = 0;
if ($num11>0){
while ($row11 = mysqli_fetch_array($resultSelectDay, MYSQLI_ASSOC))
{
$groupedResultFromDatabase[$row11['dayValue']]=$row11['sumForDayType'];
$totalDays += $row11['sumForDayType'];
}
}else{ $totalDays = 0;}
//array to store day results.
$dayTypeArrayForTable = array();
//these will be calculated by adding values to them in several places in this script.
$daysInForAverages = 0;
//as full holiday day can be of daytype 3 and 5 must sum them up
$daysHoliday = 0;
//only run these script if there results from database.
if ($totalDays>0) {
//a function that checks if an array key exists, if it does, a new array is structured with values required to fill the days table
//like text, value and day/days value.
function checkIfKeyExists ($keyValue, $array, $dayTypesNamesText ){
$oneArrayForDays = array();
$daysInCount = 0;
$daysCount = 0;
$daysCountHoliday = 0;
if (array_key_exists($keyValue,$array)) {
//sum holiday days will be added separately to the array with key value 3.
//as there are two types of full day holidays.
if ($keyValue != 3 AND $keyValue != 5){
//if the key exists it means it is at leats 1 day for the day type.
$oneArrayForDays = array("name"=>$dayTypesNamesText[$keyValue],"earnings"=>$array[$keyValue],"symbol"=>dayDays($array[$keyValue]));
} else {
//count holiday days
$daysCountHoliday = $array[$keyValue];
}
//count the number of days work was attended: day in, half holiday and half sick.
if ($keyValue == 1 OR $keyValue == 4 OR $keyValue == 7) {
$daysInCount = $array[$keyValue];
}
//count total days
$daysCount = $array[$keyValue];
$resultArray = array($oneArrayForDays, $daysInCount, $daysCount, $daysCountHoliday);
return $resultArray;
} else {
//return empty array.
$resultArray = array($oneArrayForDays, $daysInCount, $daysCount, $daysCountHoliday);
return $resultArray;
}
}
$dayTypeArrayForTableInnerArray1 = array();
//for every possible day type call the checkIfKeyExists function.
//if key exists a one dimension array will be returned, that will be added to result array
//first get the $dayTypesNamesText array and $dayTypeCount variables
for ($x = 0; $x<$dayTypeCount; $x++) {
//an array is sent back from this function.
$result = checkIfKeyExists($x, $groupedResultFromDatabase, $dayTypesNamesText );
//if returnded array is not empty, add values to global variables.
if ($result[0]) {
$dayTypeArrayForTableInnerArray1[] = $result[0];
$daysInForAverages += $result[1];
//on first condition this gets missed out as it returns an empty array
} else if ($result[3]){
$daysHoliday +=$result[3];
}
}
//if daysHoliday > 0 add an array to result array.
if ($daysHoliday>0) {
$oneArrayForDays = array("name"=>$dayTypesNamesText[3],"earnings"=>$daysHoliday,"symbol"=>dayDays($daysHoliday));
$dayTypeArrayForTableInnerArray1[] = $oneArrayForDays;
}
//found this solution here https://www.codexworld.com/how-to/sort-multi-dimensional-array-by-key-value-in-php/
$key_values = array_column($dayTypeArrayForTableInnerArray1, 'earnings');
array_multisort($key_values, SORT_DESC, $dayTypeArrayForTableInnerArray1);
$dayTypeArrayForTable[] = $dayTypeArrayForTableInnerArray1;
} else {
//send empty values
$dayTypeArrayForTableInnerArray1[] = array("earnings"=>0, "name"=>"None", "symbol"=>dayDays(0));
$dayTypeArrayForTable[] = $dayTypeArrayForTableInnerArray1;
}
JavaScript
As Flickerbook‘s totals calculator now contains 13 tables and charts, in order to avoid repetitive HTML code I wrote a JavaScript function that generates the container <div> elements, which creates table and canvas elements to store data and render a chart. This makes the code look cleaner and I will later on use the same function to replace other HTML code. createTableAndChartSection() receives two parameters: the element, in which the table and chart will be appended and an array with table values such as title and caption. The code for this function is demonstrated below:
const createTableAndChartSection = (rowDivID, attributeArray) => {
//create table element.
let table = document.createElement("table");
table.setAttribute("class", "table");
//create caption with title and info icon
let caption = document.createElement("caption");
//title text
let titleText= document.createTextNode(attributeArray[0]);
//add title text to caption
caption.appendChild(titleText);
//i element for info icon
//as not all tables have his icon need a condition to determine if it is necessary.
if (attributeArray[1] !== false){
let iInfo = document.createElement("i");
iInfo.setAttribute("class", "fas fa-info-circle");
iInfo.setAttribute("data-toggle", "modal");
iInfo.setAttribute("data-target", "#infoModal");
iInfo.setAttribute("id", attributeArray[1]);
//add i element to caption
caption.appendChild(iInfo);
}
//add caption to table
table.appendChild(caption);
//if this is true, create a heading for table.
if (attributeArray[2] == true){
let tHead = document.createElement("thead");
//create row
let tableRow = document.createElement("tr");
//table width 80% and 20% gets inherrited so no need to add these classess!
let tableHeading = document.createElement("th");
tableHeading.setAttribute("class", "col-xs-8");
tableHeading.textContent = "Name";
tableRow.appendChild(tableHeading);
let tableHeading2 = document.createElement("th");
tableHeading2.setAttribute("class", "col-xs-4");
tableHeading2.textContent = "Amount";
tableRow.appendChild(tableHeading2);
//add row to thead
tHead.appendChild(tableRow);
//add thead to table
table.appendChild(tHead);
}
//table row for 1 line of empty data
let tableRow = document.createElement("tr");
let tableData = document.createElement("td");
tableData.setAttribute("class", "col-xs-8");
tableData.textContent = "None";
tableRow.appendChild(tableData);
let tableData2 = document.createElement("td");
tableData2.setAttribute("class", "col-xs-4");
tableData2.textContent = "0";
tableRow.appendChild(tableData2);
//create tbody element
let tableBody = document.createElement("tbody");
tableBody.setAttribute("id", attributeArray[3]);
//add empty data table row to tbody
tableBody.appendChild(tableRow);
//add tbody to table
table.appendChild(tableBody);
//create nested div elements that will contain table
let div1 = document.createElement("div");
div1.setAttribute("class", "table-responsive");
//add table to most inner div.
div1.appendChild(table);
let div2 = document.createElement("div");
div2.setAttribute("class", "col-xs-12 TB");
div2.appendChild(div1);
//determine if canvas has to be added or not
if (attributeArray[4] == true){
//create div that will wrap canvas
let divCanvas = document.createElement("div");
divCanvas.setAttribute("class", "col-xs-12 hidden");
divCanvas.setAttribute("id", attributeArray[5]);
//create canvas element
let canvas = document.createElement("canvas");
canvas.setAttribute("class", "col-xs-12");
canvas.setAttribute("id", attributeArray[6]);
//add canvas to div
divCanvas.appendChild(canvas);
//add canvasDiv to div that stores with class "col-xs-12 TB"
div2.appendChild(divCanvas);
}
//this div is for centering content
let div3 = document.createElement("div");
div3.setAttribute("class", "col-md-3 hidden-xs");
rowDivID.appendChild(div3);
let div4 = document.createElement("div");
div4.setAttribute("class", "col-md-6 col-xs-12");
div4.appendChild(div2);
//finally add all the created elements to the row in html file
rowDivID.appendChild(div4);
}
//totals hours table
//array values [0:table title, 1:info icon id, 2:add thead or not, 3:tbody id, 4:render a chart or not, 5:id of div that wraps canvas, 6:canvas id]
hoursTableTotalsElements = ["Total Hours For Period", false, true, "totalHoursTable", true, "totalHoursChartDiv", "totalHoursChart"];
createTableAndChartSection(document.getElementById("hoursTotalsTableSection"), hoursTableTotalsElements);
//Total days table
daysTableTotalsElements = ["Total Days For Period", "totalDaysInfo", true, "totalDaysTable", true, "totalDaysChartDiv", "totalDaysChart"];
createTableAndChartSection(document.getElementById("daysTotalsTableSection"), daysTableTotalsElements);createTableAndChartSection creates
element with id. This id is used in a loadTotasTables() function to fill the table with data retrieved from back end.//function that creates tbody and its's child elements and fill them with data
const loadTotasTables = (tableName, response) => {
let parentArrayLength = Object.keys(response).length;
for(let x=0; x<parentArrayLength;x++){
let arrayLength = Object.keys(response[x]).length;
let i=0;
do {
let tableRow = document.createElement("tr");
//since the data is structured into multidimensional arrays, it is done so that it would be possible to determine when to add a
//top border to separate data in the table.
//if the parent array length is more then 1, it means that for every additional array inside a parent array, a top border must be added.
//so if x!=0 (means there are several sections of data to be added)
//and i== 0 (only add top border on first iteration ) --->
//if these two conditions are met a class that adds top border is added.
if (x!==0 && i== 0){
tableRow.setAttribute("class", "BT");
}
let tableData = document.createElement("td");
tableData.setAttribute("class", "col-xs-8");
tableData.textContent = response[x][i]['name'];
tableRow.appendChild(tableData);
let tableData2 = document.createElement("td");
tableData2.setAttribute("class", "col-xs-4");
tableData2.textContent = response[x][i]['earnings']+" "+response[x][i]['symbol'];
tableRow.appendChild(tableData2);
tableName.appendChild(tableRow);
i++;
}
while (i < arrayLength);
}
}
//load hours table with data
loadTotasTables(document.getElementById("totalHoursTable"), response.hoursArrayForTable);
//load days table with data
loadTotasTables(document.getElementById("totalDaysTable"), response.dayTypeArrayForTable);
Total Hours And Days Tables
Below are images of new tables for total hours and total days. I’m happy with the results, though I have to admit it was time consuming to achieve it.
And I still have two tables and charts left to sort out: Payments and Deductions. Since I already have the data for it from the JOIN query, I need to organise it for front end and create tables and charts containers with JavaScript. However this data will require some more additional SQL querying and it’s gonna be a chance to test some new things with SQL for me 😉