I’m working on development of SMS parking software, and I’m stuck at one point for a month… I need to implement periods of payment (or work time, of a work day, if you will). Here’s the problem:
For an example, traffic wardens work from Monday to Saturday. From Monday to Friday, the work times are from 07:00 to 21:00, and on Saturday, the work time is from 07:00 to 14:00. The project request was, that the customer can pay parking by SMS unlimited, which I did, but didn’t implement this logic. I started with making a table for this periods of payment, it consists of: dayofweek (INT, for use with mysql function DAYOFWEEK, to tell me which day in week is the current date), work_start and work_stop (DATETIME, for defining starting and ending the work day), but I’m unsure if I should use DATETIME, beacuse of the date, or should I use only TIME.
The idea is this:
If you send an SMS, at 20:50, Monday, it should be valid until 07:50, Tuesday (it’s payed by the hour). Extending the time of payment, regarding the work time in week. Currently, it works with extending time by the hour without this rule. Really would use some help, or some ideas, I’m stuck with this for quite some time…
8
I believe it would be much easier to have a table that stores for every hour (assuming that is your resolution) whether parking is billed (=1) or free (=0). Then iterate over the time your customer paid for by subtracting the value until his credit is used up to determine the last admissible period.
I would even suggest using two tables: the first one for the regular weekly schedule, and the second one for holidays. That way, you can easily keep track of holidays and adapt it quickly (e.g. for special events that offer free parking, etc), while being able to manage a default pattern very conveniently as well. You would need to multiply both values (a binary AND statement), then proceed as above.
You need your program code logic to implement paid time periods. If you allow for payment on one day to carry over to the next, you really want to have two records of “paid time.”
(After all, what happens when someone pays on Friday Dec 29th, and doesn’t pick up their car until Tuesday Jan 2nd, after two weekends and a holiday in a separate year? And the car park wants to know their revenue by month, quarter, and year?)
When you serialize the information to a database, you’ll want two tables anyway. “SMS payments” and “parking time”, which allows for future payments for other things (i.e., SMS to pay the higher rate for a ticket) as well as legal parking for other reasons (employee/resident guests, promotional coupons, etc.)
Alternately, if the parking garage has a slick system for tracking what times cars enter and leave, you can just record the events, extract a bill, and compare it to the record of payments, be they by SMS or not. One “SMS payment” would give a credit for 60 minutes (or 3600 seconds, depending on your resolution), and if the car has recorded time without payments they get the second, higher, rate for not paying on time.
The easiest way to take the payment periods (working hours) into account is to turn the things around and store the periods during which parking is free.
Then you can adjust the valid_from and valid_to timestamps using an algorithm like this:
if (active_payment)
valid_from = active_payment.valid_to
else
valid_from = now
if (valid_from in free_parking_period)
valid_from = free_parking_period.end
valid_to = valid_from + 1 hour
if (valid_to in free_parking_period)
valid_to = free_parking_period.end + (valid_to - free_parking_period.start)
For storing the free parking periods, I would just go with the simplest solution of two DATETIME columns with the start and end of each period. If there is free parking on Sundays and public holidays, this will amount to approximately 300 entries for a year.