0

I am building a Time Clock application with PHP and Laravel 4.

My boss requires that he is able to pull and build different reports based on the data I store in the database for a Time Card record.

Right now I store a DateTime for clock in and clock out as well as a Timestamp for both those times as well into the Database.

I need to be able to Query the database and build reports for different Pay Periods for a user.

So for example I will store in another Database Table, records that will be for a User ID and will have different Pay Periods. So a Start day may be the 1st of the month and end date the 15th and that is 1 pay period (roughly 2 weeks) I am not sure the best way to store these records really.

Another will be the 16th of the month to the end of the month. So the end date would be different depending on how many days are in a month

I am not sure about the best way to define these Pay periods for a user. I can't simply say 1-15 and then 16-30 since the 30 would be a different number for each month.

Would appreciate any insight into how this could be done?

So I can build reports for any Pay Periods since not every user gets paid every 2 weeks it needs to be flexible so that I can define it on a per user basis

This question is more about the Logic instead of actual code.

Matt Johnson-Pint
  • 230,703
  • 74
  • 448
  • 575
JasonDavis
  • 48,204
  • 100
  • 318
  • 537

2 Answers2

4

Welcome to the wonderful world of Time and Attendance. You are touching the tip of the iceberg. You may find that purchasing a pre-packaged product may be easier than writing your own.

That said, I can offer you the following general advice:

  • Be very careful of your data types and how they are used, both in PHP and in MySQL.

  • You need to make sure you understand local time vs UTC, time zones, and daylight saving time. In general, you don't want to store local time unless you also store its offset from UTC. Otherwise you will have ambiguity around daylight saving time changes. This is important even if you only have one time zone to deal with.

  • When it comes to Pay Periods, the common types are:

    • Weekly
    • Bi-Weekly
    • Semi-Monthly
    • Monthly
    • Every X days starting from Y

  • In some systems, each individual pay period can be adjusted +/- a number of days from it's normal date. When doing so, the bordering period must also be adjusted to compensate.

  • You should start with business logic that can calculate the start and end date for a pay period given any particular date and time. You can then expand that to easily get the prior or next pay period.

  • You can store each pay period into it's own table, but it's not necessarily required. That will depend on a lot of specifics about your system internals.

  • Because a pay period is defined by dates, you have the "Whose Day is it?" problem. It might be the day as defined by the company, or if employees are in different time zones, then it might be the "logical day". If you only have one time zone to deal with then, you are lucky in this regard.

  • When comparing against the pay period, use half-open intervals, [start, end). In other words:

    periodStart <= punchTime < periodEnd

    or likewise

    periodStart <= punchTime && periodEnd > punchTime

    The end of one period should be exactly the same as the start of the next. Don't try to define the end of the period at some silly value like 23:59:59.999...

As you can see, this is just the beginning. I hope this is useful to you. If you can narrow the focus of your question further, I'll be happy to help more. Otherwise, it's like asking for how to build an ERP system when you're not sure what structure to store inventory.

Community
  • 1
  • 1
Matt Johnson-Pint
  • 230,703
  • 74
  • 448
  • 575
1

I think you are over thinking this. Let thte user define the start and end dates.

You will need the UserId, a timestamp (time in and time out) of the user and that should be about it.

I picture something like this:

UserId | DateIn | DateOut

On the page you could put put dropdowns (or if you want a nifty interface a datepicker that uses javascript) and allow the manager to pick a start and end date that he wants to choose.

So if he wants to see an employees time between Jan. 1 and Feb. 31 he can choose those as his start and end dates.

This will allow things to be very flexible, for example the manager can choose Feb 16 as start date and Feb 29 as end date. It makes sense to allow him to choose the data requirements so he can view whatever he wants.

EDIT:

An example from my comment below this post you could do something like:

$startDate = new DateTime();
$startDate->modify('first day of this month'); //or 16th for second part of bi-monthly
$startDate->format(#some date formatting as you need#);

$endDate = new DateTime();
$endDate->modify('last day of this month'); //or 15th for first part of bi-monthly
$endDate->format(#some date formatting as you need#);

If things are even less defined however you could always try doing special math. date('t') will give you the number of days in a month. I would refrain from using this unless your pay days are fixed such as paid every 6 days.

In general I would harness the power of the PHP DateTime class over using date() function. http://php.net/manual/en/class.datetime.php

Shawn
  • 3,583
  • 8
  • 46
  • 63
  • I actually have it like that now but my boss is requiring in addition to the date selections, that each user has defined pay periods that are set by the Admin user for each user. So a user can view there timecards for like pay period 1, pay period 2, etc. This would be on a different page then the page that will allow date selections – JasonDavis Sep 03 '13 at 17:19
  • @jasondavis Can you give more examples of "pay dates". For example I am thinking you could have defined "account types"... For example bi-weekly, monthly, bi-monthly, bi-annually, annually. I think this would be much better of an admin option. – Shawn Sep 03 '13 at 17:24
  • Thanks for the help. My boss is very picky about this so he is requiring that each user can be defined as he has some users that are weekly, bi-weekly, monthly, and some are more custom. I do like the idea of doing the account types and think I will try to sell him on that idea instead, thanks. Also I am already using DateTime's extensively, I am just not sure on how to do these poay periods that my boss insists on. The user groups seems like the way to go though – JasonDavis Sep 03 '13 at 17:50
  • One thing I just though of is if you are doing the account types you can figure out the dates via MYSQL (or right in your database selects). I am thinking this would mean you wouldn't have to do math in your PHP, but instead can harness most of the logic from the DB engine. For example DateDiff, etc. http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html – Shawn Sep 03 '13 at 17:58
  • All good ideas. I am still not 100% sure how I could do it with the groups though. Since each time period would need a start and stop date. I mean if there was a group where pay period was ever 5 days...just an example because it needs to be that flexible, when I load a page I need to show all records for the current pay period. So if today was Tuesday it would need to show records for his pay period that today is inside of, if that makes sense. So a Time period group couldn't simply have a start and stop date since some groups would have multiple start and stop times I think – JasonDavis Sep 03 '13 at 19:30
  • I would do this with PHP logic here. Define the rules such that you get the date('m') //and/or date('d'), and then if bi-weekly say if(date >=15) //we know you are in the second pay cycle of the month you are in, thus do a query lookup of our users here; where dateIn is BETWEEN 15 AND 31, getting 31 from date('d', strtotime('last day of month'))... this is seudocode b/c i know you are using DateTime but using Datetime you can say "last day of month" – Shawn Sep 04 '13 at 19:51