Problem in getting the attendance of employee based on their login and logout

Discussion in 'Database' started by newphpcoder, Dec 23, 2011.

  1. newphpcoder

    newphpcoder New Member

    Joined:
    Sep 24, 2010
    Messages:
    101
    Likes Received:
    0
    Trophy Points:
    0
    Occupation:
    Web Programmer
    Location:
    Philippines
    Hi...
    I'm sorry if i posted again this issue..
    I just really want a help to resolved my problem...
    I just want to know what logic..what syntax should i need to used to satisfied the conditions that I needed so that the rendered will have a correct output.
    Because in rendered will depend the salary of an employee :(
    Honestly, I always think what syntax should i need but still I really don't know how to do it..
    I hope somebody will understand my situation and help me to solve it.

    Thank you so much...

    EMP_NO-------DATE_DTR-------LOGIN------------------------LOGOUT---------------RENDERED-------
    ---This shift is 21:35:00 - 05:35:00
    00300395-----2011-12-01-----2011-12-01 21:30:00----------2011-12-02 05:45:00--08:00:00

    //rendered should be 08:00:00 because his login <= 21:35:00 and his logout is >= 05:35:00
    But i have a lot of shift:
    21:35:00 - 05:35:00
    05:35:00 - 13:35:00
    13:35:00 - 21:35:00
    07:00:00 - 16:00:00
    08:00:00 - 16:00:00
    08:00:00 - 17:00:00
    08:00:00 - 18:00:00
    when I used this code:
    Code:
    UPDATE payroll.reg_att SET Rendered =  case
when time_to_sec(time(TotalHours)) <= time_to_sec('02:00:00')
then sec_to_time(time_to_sec('00:00:00'))
when time_to_sec(time(LOGIN)) <= time_to_sec('05:35:00') AND time_to_sec(time(LOGOUT)) >= time_to_sec('13:35:00') 
then sec_to_time(time_to_sec('08:00:00'))
when time_to_sec(time(LOGIN)) <= time_to_sec('07:00:00') AND time_to_sec(time(LOGOUT)) >= time_to_sec('16:00:00') 
then sec_to_time(time_to_sec('08:00:00'))
when time_to_sec(time(LOGIN)) <= time_to_sec('08:00:00') AND time_to_sec(time(LOGOUT)) >= time_to_sec('16:00:00') 
then sec_to_time(time_to_sec('08:00:00'))
when time_to_sec(time(LOGIN)) <= time_to_sec('13:35:00') AND time_to_sec(time(LOGOUT)) >= time_to_sec('21:35:00') 
then sec_to_time(time_to_sec('08:00:00'))
when time_to_sec(time(LOGOUT)) < time_to_sec('05:35:00') AND time_to_sec(time(LOGIN)) >= time_to_sec('21:35:00')
then sec_to_time(time_to_sec(concat(substr(LOGOUT, 1,10), ' ' , '08:00:00')) -
(time_to_sec(LOGOUT) - time_to_sec(concat(substr(LOGOUT, 1,10), ' ' , '05:35:00'))))
when time_to_sec(time(LOGOUT)) <= time_to_sec('05:35:00') AND time_to_sec(time(LOGIN)) <= time_to_sec('21:35:00')
then sec_to_time(time_to_sec(concat(substr(LOGOUT, 1,10), ' ' , '08:00:00')) + 
(time_to_sec(LOGOUT) - time_to_sec(concat(substr(LOGOUT, 1,10), ' ' , '05:35:00'))) + time_to_sec(concat(substr(LOGIN, 1,10), ' ' , '08:00:00')) - 
(time_to_sec(LOGIN) - time_to_sec(concat(substr(LOGIN, 1,10), ' ' , '21:35:00'))) - time_to_sec('08:00:00'))
when time_to_sec(time(LOGOUT)) >= time_to_sec('05:35:00') AND time_to_sec(time(LOGIN)) >= time_to_sec('21:35:00')
then sec_to_time(time_to_sec(concat(substr(LOGOUT, 1,10), ' ' , '08:00:00')) + 
(time_to_sec(LOGOUT) - time_to_sec(concat(substr(LOGOUT, 1,10), ' ' , '05:35:00'))) + time_to_sec(concat(substr(LOGIN, 1,10), ' ' , '08:00:00')) - 
(time_to_sec(LOGIN) - time_to_sec(concat(substr(LOGIN, 1,10), ' ' , '21:35:00'))) - time_to_sec('08:00:00'))
when time_to_sec(time(LOGOUT)) < time_to_sec('13:35:00') AND time_to_sec(time(LOGIN)) >= time_to_sec('05:35:00')
then sec_to_time(time_to_sec(concat(substr(LOGOUT, 1,10), ' ' , '08:00:00')) + 
(time_to_sec(LOGOUT) - time_to_sec(concat(substr(LOGOUT, 1,10), ' ' , '13:35:00'))) + time_to_sec(concat(substr(LOGIN, 1,10), ' ' , '08:00:00')) - 
(time_to_sec(LOGIN) - time_to_sec(concat(substr(LOGIN, 1,10), ' ' , '05:35:00'))) - time_to_sec('08:00:00')) 
when time_to_sec(time(LOGIN)) > time_to_sec('13:35:00') AND time_to_sec(time(LOGOUT)) >= time_to_sec('21:35:00') 
then sec_to_time(time_to_sec(concat(substr(LOGIN, 1,10), ' ' , '08:00:00')) -
(time_to_sec(LOGIN) - time_to_sec(concat(substr(LOGIN, 1,10), ' ' , '13:35:00'))))
when time_to_sec(time(LOGOUT)) <= time_to_sec('21:35:00') AND time_to_sec(time(LOGIN)) <= time_to_sec('13:35:00')
then sec_to_time(time_to_sec(concat(substr(LOGOUT, 1,10), ' ' , '08:00:00')) + 
(time_to_sec(LOGOUT) - time_to_sec(concat(substr(LOGOUT, 1,10), ' ' , '21:35:00'))) + time_to_sec(concat(substr(LOGIN, 1,10), ' ' , '08:00:00')) - 
(time_to_sec(LOGIN) - time_to_sec(concat(substr(LOGIN, 1,10), ' ' , '13:35:00'))) - time_to_sec('08:00:00'))
when time_to_sec(time(LOGOUT)) < time_to_sec('21:35:00') AND time_to_sec(time(LOGIN)) >= time_to_sec('13:35:00')
then sec_to_time(time_to_sec(concat(substr(LOGOUT, 1,10), ' ' , '08:00:00')) + 
(time_to_sec(LOGOUT) - time_to_sec(concat(substr(LOGOUT, 1,10), ' ' , '21:35:00'))) + time_to_sec(concat(substr(LOGIN, 1,10), ' ' , '08:00:00')) - 
(time_to_sec(LOGIN) - time_to_sec(concat(substr(LOGIN, 1,10), ' ' , '13:35:00'))) - time_to_sec('08:00:00')) 
when time_to_sec(time(LOGIN)) > time_to_sec('05:35:00') AND time_to_sec(time(LOGOUT)) >= time_to_sec('13:35:00') 
then sec_to_time(time_to_sec(concat(substr(LOGIN, 1,10), ' ' , '08:00:00')) - 
(time_to_sec(LOGIN) - time_to_sec(concat(substr(LOGIN, 1,10), ' ' , '05:35:00'))))

when time_to_sec(time(LOGOUT)) < time_to_sec('21:35:00') AND time_to_sec(time(LOGIN)) >= time_to_sec('13:35:00')
then sec_to_time(time_to_sec(concat(substr(LOGOUT, 1,10), ' ' , '08:00:00')) + 
(time_to_sec(LOGOUT) - time_to_sec(concat(substr(LOGOUT, 1,10), ' ' , '21:35:00'))))
when time_to_sec(time(LOGOUT)) < time_to_sec('13:35:00') AND time_to_sec(time(LOGIN)) >= time_to_sec('05:35:00')
then sec_to_time(time_to_sec(concat(substr(LOGOUT, 1,10), ' ' , '08:00:00')) + 
(time_to_sec(LOGOUT) - time_to_sec(concat(substr(LOGOUT, 1,10), ' ' , '13:35:00'))))

when time_to_sec(time(LOGOUT)) <= time_to_sec('13:35:00') AND time_to_sec(time(LOGIN)) <= time_to_sec('05:35:00')
then sec_to_time(time_to_sec(concat(substr(LOGOUT, 1,10), ' ' , '08:00:00')) + 
(time_to_sec(LOGOUT) - time_to_sec(concat(substr(LOGOUT, 1,10), ' ' , '13:35:00'))) + time_to_sec(concat(substr(LOGIN, 1,10), ' ' , '08:00:00')) - 
(time_to_sec(LOGIN) - time_to_sec(concat(substr(LOGIN, 1,10), ' ' , '05:35:00'))) - time_to_sec('08:00:00'))
END;
    I got a problem to get the rendered for 21:35:00 - 05:35:00 shift if the login <= 21:35:00 logout >= 05:35:00
    Code:
    when time_to_sec(time(LOGIN)) <= time_to_sec('21:35:00') AND time_to_sec(time(LOGOUT)) >= time_to_sec('05:35:00') 
then sec_to_time(time_to_sec('08:00:00'))
    when I used it all shifts will affected..Because I think it only check in time.
    I have a lot of conditions needed per shift.
    Like this:

    //if the employee is early login from his shift and late logout from his shift the rendered will be 08:00:00
    //if the employee is late to login the 08:00:00 will minus
    //if the employee is undertime or early to logout 08:00:00 will minus
    //if the employee is late to login and early to logout the the sum of late login and early logout will minus in 08:00:00
    IF LOGIN <= 'SHIFT IN' AND LOGOUT >= 'SHIFT OUT' THEN RENDERED WILL 08:00:00
    IF LOGIN <= 'SHIFT IN' AND LOGOUT < 'SHIFT OUT' THEN RENDERED WILL 08:00:00 - (LOGOUT - SHIFT OUT)
    IF LOGIN > 'SHIFT IN' AND LOGOUT >= 'SHIFT OUT' THEN RENDERED WILL 08:00:00 - (LOGIN - SHIFT IN)
    IF LOGIN >= 'SHIFT IN' AND LOGOUT <= 'SHIFT OUT'THEN RENDERED WILL 08:00:00 + (LOGOUT - SHIFT) 08:00:00 - (LOGIN - SHIFT)

    Thank you so much...
     
    newphpcoder, Dec 23, 2011
    SHARE #1

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.
    Accept Learn More...
    Dismiss Notice