kirankumer Posted February 9, 2012 Share Posted February 9, 2012 Hello Guys, Please explain me how to add these timings of an employee(These are punch in and out timings of an employee) .. 1 2 3 4 5 6 7 ..................... 09:00 09:10 09:15 09:20 09:25 10:30 11:11 ........... I want to store in timings and out timings separately ... like in_timings =( 1st + 2nd ) punch + (3rd + 4th ) punch + (5th + 6th) punch ..... out_timngs =(2nd + 3rd ) punch + (4th + 5th ) punch + (6th + 7th ) punch ..... I am trying it in php ... Please give me any ideas ....Thanks in advance Quote Link to comment Share on other sites More sharing options...
Psycho Posted February 9, 2012 Share Posted February 9, 2012 Really not enough information on what you currently have or your specific requirements to provide a good solution. Most time tracking systems (that I've worked with) create/update records in pairs. And, each pair is given a "type": work time, break time, lunch, etc. So, if the user clocks in at 9:00 a new record will be created such as date | start | stop | type 20120209 | 09:00 | | work Then, when the user clocks out for a break at 9:10 the system will set the stop time for the first event and and create a new event with the start time date | start | stop | type 20120209 | 09:00 | 09:10 | work 20120209 | 09:10 | | break That process will continue until the user clock-out for the day. Then you just set the stop time for the last record and do not create a new record. You can then add up all the hours for the day, week, etc for each type (work, break, lunch etc.) Quote Link to comment Share on other sites More sharing options...
kirankumer Posted February 9, 2012 Author Share Posted February 9, 2012 Actually my question is. I have database with table called "attendance".. Userid Date Punch_time 1 01-01-2012 09:00 am 1 01-01-2012 09:15 am 1 01-01-2012 11:02 am 1 01-01-2012 12:59 pm 1 01-01-2012 15:55 pm 1 01-01-2012 17:59 pm 2 01-01-2012 09:10 am 2 01-01-2012 10:45 am 2 01-01-2012 01:02 pm 2 01-01-2012 18:02 pm like this for every employee and for every day records will be inserted in " attendance " table ... Now i want to calculate timings of an employee inside and outside office ..... In coding language for employee userid 1 first punch is inside (09:00) , 2nd punch(09:15) is outside and 3rd punch(11:02) is inside and so on.... Now i have to calculate how much time he's inside office and outisde office in PHP Hope now you understand my question .. Quote Link to comment Share on other sites More sharing options...
Psycho Posted February 9, 2012 Share Posted February 9, 2012 Yes, I understand what you want. I was explaining one way the database records *should* be stored. The way you have them stored now it going to do nothing but cause problems. 1. If a punch is missed or a record doesn't get created properly your times are going to be messed up and you will have no easy way to identify the problem and/or fix it. 2. Calculating the time periods is going to require a VERY inefficient process of querying all the records and processing them one by one. I'm not going to try and provide a solution for a process that is so flawed to begin with. At the very least you should store an additional piece of data for each record to indicate if it is a punch out or in. That would solve problem #1, but it's still going to require an overly repetitive process to do the calculations. Quote Link to comment Share on other sites More sharing options...
facarroll Posted February 9, 2012 Share Posted February 9, 2012 You cannot derive information from the punch_time field as it stands because there is no identification of each time as an arrival or a departure. You need something like this in your database. Userid Date arrive_Punch_time depart_punch_time 1 01-01-2012 09:00 am 09:10 am 1 01-01-2012 09:15 am 09:20 am 1 01-01-2012 11:02 am 11:30 am 1 01-01-2012 12:59 pm 13:10 pm 1 01-01-2012 15:55 pm 16:10 pm 1 01-01-2012 17:59 pm 18:30 pm 2 01-01-2012 09:10 am 9:50 am Quote Link to comment Share on other sites More sharing options...
kirankumer Posted February 10, 2012 Author Share Posted February 10, 2012 Thanks Guys for your valuable replies... Really that's good idea to calculate ....,If i take another column "depart_punch_time" it will be easy for me to calculate timings ..... But we have a finger print device, this software unlocks the door when an employee punches, so at every time record will get inserted for every punch ( means door opens when i punch, if punch is not taken properly it will give warning message ) thats the reason till now we dint get the situation like " missing punch ".... I tried this problem by using " for loop " . If i have a fixed number of punches then i can get using for loop but here i dont have fixed punches in a day....I think am missing some logic , please help me how to calculate in this way (with out depart_punch_time ).. :'( Quote Link to comment Share on other sites More sharing options...
Psycho Posted February 10, 2012 Share Posted February 10, 2012 OK, so let me restate what I think I understand, then I'll provide some sample code: You aren't building a timeclock system. Instead you have a door entry system that is bio-metrically controlled and records when a user unlocks the door with a timestamp. You are wanting to generate some type of report to show when a user is inside or outside the office. One question would be whether user's have to use the scanner to leave the office. If yes, is that record recorded any differently (e.g. an id of the scanner used) than the ones used to enter? If you can identify whether the user is using the enter scanner vs the exit scanner that is useful information. However, even if you know which scanner a person used you cannot know whether the user is inside or outside the office. It isn't going to verify that the person walked through the door. And, if you can't differentiate which scanner the person used it is even more dangerous to assume whether the user entered or left. Take the situation of someone unlocking the door then suddenly realizing they left something in their car. It's not a matter of whether something like that will happen it is a matter of when. If you rely upon these reports to determine when someone entered/left you are going to have situations that may cause a lot of headaches. But, based upon the input data I believe you have and what you want to accomplish, this is what I would suggest: In the table that you have now, add a column called "processed" and make it a tiny Int type. Set the default value to 0. Then, create a process that will process new records. To process the records, create a new table like we discussed above with one record to hold both an in and out time. In order to prevent problems with incomplete records (i.e. no Out time) you should only run this on records for the previous day. Then the actual process would work something like this: 1. Select all records from the original table prior to the current day where the processed value is 0 sorted by UserID and Date. 2. Create a loop to run through the results taking two records at a time for each user using the first as the In time and the second as the Out time. Calculate the time difference. Then create a new record it he new table with the in/out times and the time period. 3. At the end of the processing, update the records in the original table (prior to current day where processed is 0) to have a processed value of 1. Also, you would need to handle those situation where there is an uneven number of entries for a user (such as would occur in the situation I described above). I would probably create a record with an In time and no value for out or period. Then have some sort of exception report for someone to review. Also note that this would not work very well if you have people that are working past midnight. In that case it would be very difficult to make any determinations on In/Out. You can then use the data in the new table to run your reports. Quote Link to comment Share on other sites More sharing options...
kirankumer Posted February 17, 2012 Author Share Posted February 17, 2012 Thanks Guys for your help...i solved the problem Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.