Problem in coding with three different scenario

[newphpbees]

Hi...

 

 

I have three types of night premium and it only computed of the schedule is  night shift

 

I have this Night Premium Types:

- NightPremiumRegular (Monday-Saturday)

-NightPremiumSunday (Sunday)

- NightPremiumHoliday

 

I created table which has a listed of holidays date.

 

Now I encountered problem in sum of hours in NightPremiumHoliday

 

here is my code:

//===================Display Night Premium=========================

$sql = "SELECT  r.EMP_NO, r.LOGIN, DATE(LOGIN) AS DATE_LOGIN, DATE_FORMAT(LOGIN, '%W') AS SUNDAY, r.LOGOUT, sec_to_time(SUM(time_to_sec(Rendered))) AS Rendered FROM $PAYROLL.reg_att r, $ADODB_DB.employment em, $ADODB_DB.personal p WHERE time_to_sec(time(LOGIN)) BETWEEN time_to_sec('17:00:00') AND time_to_sec('22:35:00') AND time_to_sec(time(LOGOUT)) BETWEEN time_to_sec('05:35:00') AND time_to_sec('09:35:00') AND r.EMP_NO = em.EMP_NO AND em.EMP_ID = '$currentEmpID' AND p.EMP_ID = '$currentEmpID' AND DATE_FORMAT(LOGIN, '%W') = 'Sunday'";
$rsNPSun = $conn2->Execute($sql);

$NPSunHours = $rsNPSun->fields['Rendered'];
$NPSunDate = $rsNPSun->fields['SUNDAY'];

$NPSunHours = substr($NPSunHours, 0, 5);
$NPSunHours = str_replace(':', '.', $NPSunHours);
     
$sql = "SELECT OP FROM ot WHERE Category = 'NightPremiumSunday'";
$rsOTCatSun = $conn2->Execute($sql);
$NPSun_OP = $rsOTCatSun->fields['OP'];

$NPSunAmt = (($Rate / 8 * 1.35 * $NPSun_OP) * $NPSunHours); 
$NPSunAmt = number_format($NPSunAmt, 2, '.', '');         

$smarty->assign('NPSunHours', $NPSunHours);
$smarty->assign('NPSunAmt', $NPSunAmt);

//=================NP REG=======
$sql = "SELECT  r.EMP_NO, r.LOGIN, DATE(LOGIN) AS DATE_LOGIN, DATE_FORMAT(LOGIN, '%W') AS Week_Days, r.LOGOUT, sec_to_time(SUM(time_to_sec(Rendered))) AS Rendered FROM $PAYROLL.reg_att r, $ADODB_DB.employment em, $ADODB_DB.personal p WHERE time_to_sec(time(LOGIN)) BETWEEN time_to_sec('17:00:00') AND time_to_sec('22:35:00') AND time_to_sec(time(LOGOUT)) BETWEEN time_to_sec('05:35:00') AND time_to_sec('09:35:00') AND r.EMP_NO = em.EMP_NO AND em.EMP_ID = '$currentEmpID' AND p.EMP_ID = '$currentEmpID' AND DATE_FORMAT(LOGIN, '%W') IN ('Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday')";
$rsNPReg = $conn2->Execute($sql);

$NPRegHours = $rsNPReg->fields['Rendered'];
$NPRegDate = $rsNPReg->fields['Week_Days'];

$NPRegHours = substr($NPRegHours, 0, 5);
$NPRegHours = str_replace(':', '.', $NPRegHours);


$sql = "SELECT OP FROM ot WHERE Category = 'NightPremiumRegular'";
$rsOTCatSun = $conn2->Execute($sql);
$NPReg_OP = $rsOTCatSun->fields['OP'];

$NPRegAmt = (($Rate / 8 * $NPReg_OP) * $NPRegHours); 
$NPRegAmt = number_format($NPRegAmt, 2, '.', '');         

$smarty->assign('NPRegHours', $NPRegHours);
$smarty->assign('NPRegAmt', $NPRegAmt);

//=======================Night Premium Holiday==============
$sql = "SELECT r.EMP_NO, r.LOGIN, DATE(LOGIN) AS DATE_LOGIN, r.LOGOUT, sec_to_time(SUM(time_to_sec(Rendered))) AS Rendered FROM $PAYROLL.reg_att r, $ADODB_DB.employment em, $PAYROLL.holiday_date h WHERE time_to_sec(time(LOGIN)) BETWEEN time_to_sec('17:00:00') AND time_to_sec('22:35:00') AND time_to_sec(time(LOGOUT)) BETWEEN time_to_sec('05:35:00') AND time_to_sec('09:35:00') AND DATE(LOGIN) = h.holiday_date AND r.EMP_NO = em.EMP_NO AND em.EMP_ID = '$currentEmpID'";
$rsNPHol = $conn2->Execute($sql);

$NPHolHours = $rsNPHol->fields['Rendered'];

$NPHolHours = substr($NPHolHours, 0, 5);
$NPHolHours = str_replace(':', '.', $NPHolHours);

$sql = "SELECT OP FROM ot WHERE Category = 'NightPremiumHoliday'";
$rsOTCatSun = $conn2->Execute($sql);
$NPHol_OP = $rsOTCatSun->fields['OP'];

$NPHolAmt = (($Rate / 8 * 2.05 * $NPHol_OP) * $NPHolHours); 
$NPHolAmt = number_format($NPHolAmt, 2, '.', '');         


$smarty->assign('NPHolHours', $NPHolHours);
$smarty->assign('NPHolAmt', $NPHolAmt);

$NPHours = ($NPRegHours + $NPSunHours + $NPHolHours);
$NPHours = number_format($NPHours, 2, '.', '');     

$NP_Amt = ($NPRegAmt + $NPSunAmt + $NPHolAmt);
$NP_Amt = number_format($NP_Amt, 2, '.', '');    

$smarty->assign('NP_Hours', $NPHours);
$smarty->assign('NP_Amt', $NP_Amt);

 

for example:

I have attendance 2012-01-01  and it's sunday and it is also holiday..

And the rendered is 8

 

so the NightPremiumSunday and NightPremiumHolidays computed, but i got problem in computations of NPHours.

 

I got NPHours = 16, supposively 8, but because it is sunday and holiday it sum the result of hours in Sunday and Holiday..

 

How can I only get 8 hours if my date is holiday?

 

Thank you so much..

 

Any help is highly appreciated..

 

feel free to ask me if theirs a question.