Jump to content

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

newphpbees

By newphpbees
in MySQL Help

 Share

Recommended Posts

newphpbees Member

newphpbees

Posted
Posted

Hi...

 

I have a php code for importing attendance data to database.

 

I have here the sample data that was imported in database:

 

EMP_NO--------DATE_DTR----------DTR

9300127--------2011-11-14---------2011-11-14 05:35:00

9300127--------2011-11-14---------2011-11-14 13:35:00

9300127--------2011-11-15---------2011-11-15 13:35:00

9300127--------2011-11-15---------2011-11-15 21:35:00

9300127--------2011-11-16---------2011-11-16 21:35:00

9300127--------2011-11-17---------2011-11-17 05:35:00

9300127--------2011-11-17---------2011-11-17 21:35:00

9300127--------2011-11-18---------2011-11-18 05:35:00

 

As you can see I have 3 different shifts:

 

-- 05:35:00 - 13:35:00

-- 13:35:00 - 21:35:00

-- 21:35:00 - 05:35:00

 

because of the time in and timeout is in one field, i used the mysql function max and min to get time in and time out and I inserted it in different tables.

 

I used this code for inserting data with the time in and timeout is separately.

 

INSERT INTO regular_dtr_total(EMP_NO, DATE_DTR, max_dtr, min_dtr) 
SELECT a.EMP_NO, a.DATE_DTR, max(b.DTR),min(a.dtr)
FROM regular_dtr a
LEFT JOIN regular_dtr b ON (a.EMP_NO = b.EMP_NO AND a.DATE_DTR = b.DATE_DTR)
GROUP BY a.EMP_NO, a.DATE_DTR;

 

and the output of this code is:

 

EMP_NO ---------- DATE_DTR--------- max_dtr--------------------min_dtr

9300127-----------2011-11-14---------2011-11-14 13:35:00-----2011-11-14 05:35:00 // this is correct output because his shift is 05:35:00 - 13:35:00

9300127-----------2011-11-15---------2011-11-15 21:35:00-----2011-11-15 13:35:00 // this is also correct output because his shift is 13:35:00 - 21:35:00

9300127-----------2011-11-16---------2011-11-16 21:35:00-----2011-11-16 21:35:00 // wrong output

9300127-----------2011-11-17---------2011-11-17 21:35:00-----2011-11-17 05:35:00 //wrong output

9300127-----------2011-11-18---------2011-11-18 05:35:00-----2011-11-18 05:35:00 // wrong output

 

 

the max_dtr = time out of employee

the min_dtr = time in of employee

 

I want output is like this:

 

EMP_NO ---------- DATE_DTR--------- max_dtr--------------------min_dtr

9300127-----------2011-11-14---------2011-11-14 13:35:00-----2011-11-14 05:35:00 // this is correct output because his shift is 05:35:00 - 13:35:00

9300127-----------2011-11-15---------2011-11-15 21:35:00-----2011-11-15 13:35:00 // this is also correct output because his shift is 13:35:00 - 21:35:00

9300127-----------2011-11-16---------2011-11-17 05:35:00-----2011-11-16 21:35:00 // because it is from shift 21:35:00 TO 05:35:00

9300127-----------2011-11-17---------2011-11-18 05:35:00-----2011-11-17 21:35:00 // same with the above example with 21:35;00 - 05:35:00 shift.

 

As you noticed i have only problem in using max and min to get the timein and timeout in the shift for 21:35:00 - 05:35:00 because they are diiferent date.

 

I have no idea how can i resolved my problem in 21:35:00-05:35:00 shift. I don't know how can i revised my syntax that will not affect the two shifts that has a correct output.

 

Any help is highly appreciated and I want you to know that the reason why I posted my problem it's because its urgent and I'm not good in logic in mysql query and also i'm not familiar with other functions of mysql..

 

Again Thank you so much in advance.

 

If you have any questions for further understanding of my problem feel free to ask me..

 

Thank you

Link to comment
Share on other sites
newphpbees Member

newphpbees

Posted
  • Author
Posted

I have an idea to get the21:35:00 - 05:35:00 shfit but I dpn't know how can I query it.

 

Like for example I have this data December 2, 2011 and December 3, 2011

 

00011 2011-12-02 05:35:00

00011 2011-12-02 13:35:00

00011 2011-12-02 13:35:00

00011 2011-12-02 21:35:00

00011 2011-12-02 21:35:00

00011 2011-12-03 05:35:00

 

I think I need to get the range from the previous date up to next date 05:35:00 to get the 21:35:00 - 05:35:00 shift.

 

Is it possible?And how??

 

I try to code it but i don't know what syntax.

 

Thank you

Link to comment
Share on other sites
newphpbees Member

newphpbees

Posted
  • Author
Posted

Hi...

 

There's a changes in my data..

 

Now I only have EMP_NO and DTR..

 

So now how can I solve my problem?

 

and you mean i need to join the 2 table?

 

and also how can i  find the row that has no greater time (for the max) and no earlier time (for the min).

 

Thank you

Link to comment
Share on other sites
newphpbees Member

newphpbees

Posted
  • Author
Posted

I see the thread that looks like my problem..

 

But, I don't know how can I do that in my table :(

 

because i only have EMP_NO and DTR..

 

Thank you so much...

 

I also want it to insert that data in another table but in this format:

 

EMP_NO  DATE    MIN_DTR    MAX_DTR

 

But I don't know how can i extract DTR(datetime) to DATE and also DTR to min_dtr and max_dtr..:(

 

 

Thank you

 

Link to comment
Share on other sites
newphpbees Member

newphpbees

Posted
  • Author
Posted

In the link that you gave the date is > in my situation the a.date = b.date or a.date < b.date

 

when i tried this:

 

INSERT INTO regular_dtr_total(EMP_NO, DATE_DTR, max_dtr, min_dtr, TotalHours) 
SELECT a.EMP_NO, a.DATE_DTR, max(b.DTR),min(a.DTR),
TIMEDIFF(max(b.DTR), min(a.DTR))
FROM regular_dtr a
LEFT JOIN regular_dtr b ON (a.EMP_NO = b.EMP_NO AND a.DATE_DTR <= b.DATE_DTR) 
GROUP BY a.EMP_NO, a.DATE_DTR;

 

only the a.DATE_DTR < b.DATE_DTR was work

Link to comment
Share on other sites
newphpbees Member

newphpbees

Posted
  • Author
Posted

1. Actually, this is the scenario..i upload that data in my database. And I only used mysql insert statement. And after the data inserted in a table i have again the another table where inserted the data but separate the min and max  of DTR. I only used min and max top distinguish what is the min or check in and max or check out.

2.Yes, it happens...with the used of min and max i only get the minimum time for check in and maximum time for check out.

3.the programs for attendance is separately...I only get the data from the database.

4. the program for attendance is 24 hours run..but I get only the data before the cut off period like for example i get the attendance from december 1, 2011 - december 15, 2011 I will get it on december 16, 2011 so that the data is completed.

5.the table contain all the history of attendance. for the reference.

6. I don't have programs to.. i only have  upload programs to upload the attendance and i used insert statement to save the data in my database.

Link to comment
Share on other sites
newphpbees Member

newphpbees

Posted
  • Author
Posted

I only want to know is what can i do?what is the syntax if i only have data EMP_NO and Daily_Time_Record which mix check in and check out and also theirs a scenario that the employee check in twice or check out twice or sometimes no checkin or no check out.

 

It's hard for me to figured out how can I get the date in check in, and the check in and checkout of an employee. Like i've said before i used min and max and i found out I have problem if the shift or his Daily_Time_Record is 2011-11-21 21:35:00 - 2011-11-22 05:35:00 the date is different...I don't have problem if the schedule is 2011-11-20 05:35:00 - 2011-11-20 13:35:00

 

My head was crushing i don't know what to do..what the syntax is.. :-[

 

If it is php code or pure mysql and how..

:confused:

Thank you for your help..

 

Link to comment
Share on other sites
fenway Advanced Member

fenway

Posted
Posted

In the link that you gave the date is > in my situation the a.date = b.date or a.date < b.date

 

when i tried this:

 

INSERT INTO regular_dtr_total(EMP_NO, DATE_DTR, max_dtr, min_dtr, TotalHours) 
SELECT a.EMP_NO, a.DATE_DTR, max(b.DTR),min(a.DTR),
TIMEDIFF(max(b.DTR), min(a.DTR))
FROM regular_dtr a
LEFT JOIN regular_dtr b ON (a.EMP_NO = b.EMP_NO AND a.DATE_DTR <= b.DATE_DTR) 
GROUP BY a.EMP_NO, a.DATE_DTR;

 

only the a.DATE_DTR < b.DATE_DTR was work

 

Then try "a.date < b.date" instead.

Link to comment
Share on other sites
newphpbees Member

newphpbees

Posted
  • Author
Posted

Someone give me this idea but my problem I need to code it in php but I have no idea how cn I code it in php.

 

(1) Use the simple query

SELECT EMP_NO, DTR FROM regular_dtr ORDER BY EMP_NO, DTR

 

(2) Read one record. Presumably, it will be a CHECKIN DTR. Remember the DTR value from that records as the "checkin" time.

(3) In a loop, read the next records. When you find one that is obviously for the same SHIFT as the record from (2) you remember its DTR value as the "checkout" time. You may only find one record for the same SHIFT or you may find 2 or 3 more for that shift. [*You* will have to define what a "SHFIT" is. I would assume it is a checkout time that is no more than, say, 12 hours (?? maybe??) from the checkin.

(4) When you read a DTR time that obviously is *NOT* from the same SHIFT, then you write a record to the new table:

EMP_NO, CHECKIN_DTR, CHECKOUT_DTR

(5) After writing that record, you use the DTR time that is not from the same SHIFT as the new checkin time for the *next* SHIFT. And you loop back to (3).

 

Notice that if the EMP_NO changes, that is *automatically* a change of SHIFT.

 

EMP_NO   DTR
110011    Dec 3, 2011, 8:35 AM
110011    Dec 3, 2011, 9:05 AM
110011    Dec 3, 2011, 5:20 PM
110011    Dec 4, 2011, 9:20 PM
110011    Dec 4, 2011, 9:50 PM
110011    Dec 5, 2011, 3:50 AM
110011    Dec 5, 2011, 4:05 AM
220022    Dec 3, 2011, 8:40 AM
...

Isn't it *OBVIOUS* when looking at those date/times that the following is true?


EMP_NO    BEGINSHIFT              ENDSHIFT
110011    Dec 3, 2011 8:35 AM     Dec 3, 2011 5:20 PM
110011    Dec 4, 2011 9:20 PM     Dec 5, 2011 4:05 AM
220022    Dec 3, 2011 8:40 AM     ... etc. ...

 

So by making one run through the "raw" DTR data, you should be able to create a table with BEGINSHIFT and ENDSHIFT and then you can do *ALL* your computations (e.g, total time worked, etc.) from that new table.

 

Link to comment
Share on other sites
newphpbees Member

newphpbees

Posted
  • Author
Posted

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:

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

 

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...

Link to comment
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.