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

  • Maybe I'm too dumb, but I don't get what the working time of the traffic wardens has to do with the customers payments? SMS is sent Mo 01.01.2013 20:30 with payment for 11 hours. Store that in a record valid_from, valid_to as datetime. – thorsten müller Jul 5 '13 at 10:33
  • Traffic wardens working time is the payment time, for that day. The thing is, that the SMS is valid for one hour, and I can't figure out how to implement these rules. The information is stored as valid_from and valid_to in datetime, that is already done... I can't figure out how to skip "non work" time, or hours, that is. Currently, if vehicle doesn't have payement, valid_from is date("Y-m-d H:i:s"), and valid_to is that plus one hour. If the vehicle HAS a valid ticket, and it wants to extended payment, valid_from for the new ticket is valid_to from the last ticket. – user2553268 Jul 5 '13 at 10:41
  • Like this: User sends a SMS, mobile operator sends the values from the SMS to my script (phone number and vehicle registration plate). My script first checks if the vehicle has payments (an union of all payments is made, ordered by descending value of valid_to). If the vehicle has payment, the aforemented valid_to is taken, and turned into NEW valid_from, and the valid to is that value + 1 hour (strtotime). If the vehicle doesn't have payment, the valid_from is current datetime, from php, (date("Y-m-d H:i:s")), and the valid_to is that + 1 hour. – user2553268 Jul 5 '13 at 10:53
  • Wouldn't it be easier to just keep an integer "minutes_paid" and increase by 60 with each SMS and then reduce by parking time (maybe roubded to some degree)? – thorsten müller Jul 5 '13 at 10:59
  • Can't be done, because I also wrote the android app (which hits a bunch of php scripts)... Here's the logic. A payment is "valid" if three requirements are met (in the query) - vehicle registration, zone and if current datetime is in between valid_from and valid_to. It had to be done that way :/ Like this - Vehicle KV123KV has payed by SMS in zone 1 from 11:00:00 to 12:00:00. Traffic warden checks him at 11:15:00. SELECT * FROM sms WHERE vehicle_reg = 'KV123KV' AND zone = '1' AND NOW() BETWEEN valid_from AND valid_to – user2553268 Jul 5 '13 at 11:06

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.

| improve this answer | |

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.

| improve this answer | |

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

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.