Go4Expert

Go4Expert (http://www.go4expert.com/)
-   Database (http://www.go4expert.com/forums/database-forum/)
-   -   Problem in getting the attendance of employee based on their login and logout (http://www.go4expert.com/forums/getting-attendance-employee-based-login-t27420/)

newphpcoder 23Dec2011 06:25

Problem in getting the attendance of employee based on their login and logout
 
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...


All times are GMT +5.5. The time now is 00:19.