Go4Expert

Go4Expert (http://www.go4expert.com/)
-   PHP (http://www.go4expert.com/forums/php/)
-   -   Problem in coding with three different scenario (http://www.go4expert.com/forums/coding-scenario-t27650/)

newphpcoder 24Jan2012 14:46

Problem in coding with three different scenario
 
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:
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.


All times are GMT +5.5. The time now is 01:45.