Problem in coding with three different scenario

Discussion in 'PHP' started by newphpcoder, Jan 24, 2012.

  1. newphpcoder

    newphpcoder New Member

    Joined:
    Sep 24, 2010
    Messages:
    101
    Likes Received:
    0
    Trophy Points:
    0
    Occupation:
    Web Programmer
    Location:
    Philippines
    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.
     

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice