For a new project a have to automate time sheets and the calculations behind it and I’m looking for the best technical approach.
User fills out a time sheet with 3 columns ‘Day’, ‘In’ and ‘Out’. Day contains 1-31 and both In and Out contain a hour format (24 hrs). So the first line would be ‘1’, ‘09:00’ ‘17:00’. The sheet gets filled on the last day of the current month.
Depending on the labour contract, 4 different time blocks exist, e.g. 00:00-06:00, 06:00-19:00, 19:00-22:00 and 22:00-00:00.
I need to figure out how many hours there are in every block and calculate the applicable overtime compensation, block 1 = 110%, block 2 = 100% etc.
So I have 30 or 31 lines every month and need to break them up in applicable blocks.
As an example: day 21, in 05:00, out 21:00. That would be 1 hr block 1, 13 hrs block 2 and 2 hrs block 3.
How to put the 4 rules in a method? Would a dictionary come in handy with 4 entries?
22:00-00:00 is better written in 22 to 23:59 or 24:00
in case of 09:15 its better to use minutes, a method that calculate this 9 * 60 +15 from your input.
a little class with some properties and methods.
it is something like
[code]dim countblock1 as new CountBlock(0,6)
dim countblock2 as new CountBlock(6,19)
dim countblock3 as new CountBlock(19,22)
dim countblock4 as new CountBlock(22,24)
for each row in list
start = row.starttime
end = row.endtime
for hour = start to end
countblock1.Count(hour) < it will count if between 0 and 6
countblock2.Count(hour)
countblock3.Count(hour)
countblock4.Count(hour)
next
next
overtime1 = countblock1.overtime(workdays)
[/code]
00:00-06:00 = 100% time a day is used it this block
so it is maximal = 6 hours * list.count (workdays in month)
Don’t put the rules on a method. Create a class to represent a block with properties for start time, end time, and overtime percent. Include a method that takes a range and returns the overlap.
I’d go further and create a Contract class that stores these rules and maybe start and end dates. You can give it a range and it will do all the calculations for you, perhaps in some Results class.
The advantage of this type of encapsulation is flexibility. When the contract changes or you have to deal with multiple contracts, you won’t have to change code, just input.