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

Discussion in 'PHP' started by newphpcoder, Dec 22, 2011.

0
  1. #1
    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 = 05:35:00But i have a lot of shift:21:35:00 - 05:35:0005:35:00 - 13:35:0013:35:00 - 21:35:0007:00:00 - 16:00:0008:00:00 - 16:00:0008:00:00 - 17:00:0008:00:00 - 18:00:00when I used this code: 
    UPDATE payroll.reg_att SET Rendered =  casewhen time_to_sec(time(TotalHours)) = 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('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('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)) = 'SHIFT IN' AND LOGOUT
    Code (markup):

     
    newphpcoder, Dec 22, 2011 IP