PHP/MySQL Functions (5 Required)

Find attached a MySQL file which contains opening hours for 2 restaurants. Restaurant 1 and Restaurant 2 (restid). day_of_week is the MySQL numerical reference to a day of the week, eg. Sunday = 1, Monday = 2, etc. hours_open is the opening time of a restaurant, hours_close are the opening and closing time on each day of the week. So for example, on a Saturday, Restaurant 1 opens at 17:30:00 and closes at 23:00:00. It should be noted that the closing time may be past midnight, that is 03:00:00 for example.

I require the following functions:

***** Based on the current server time, is the restaurant open or closed. This function should operate at any time, any day of the week. Please note clearly that opening hours may span on to the next day (past midnight). So you would need to get the closing time for the day before... for example. We have this built in PHP at present, but we are looking for an entirely MySQL solution to this. The function should take the following form: isRestOpen($restid); so only the restaurant ID (long int) is supplied to the function, and a boolean (or 0/1) response is returned by the function. Please note that while this is in PHP, we do not wish to see PHP functionality within the function, the query will be ENTIRELY MySQL based for speed.

***** The time the restaurant opens (or opened today) - again, you need to consider whether the restaurant is open past midnight when considering which day's opening hours you are selecting from the database. Again - we have this built in PHP, we are now looking for a MySQL function for speed.

Examples (assume the restaurant opens on Monday at 17:00 and closes at 23:00, and opens on Tuesday at 19:00 and closes at 03:00)
-> at 11:00 on Monday, this would return 17:00
-> at 18:00 on Monday, this would return 17:00
-> at 23:30 on Monday, this would return 19:00 (tomorrow's opening hours)

***** The time the restaurant closes. As above, but with closing times.

Examples (assume the restaurant opens on Monday at 17:00 and closes at 23:00, and opens on Tuesday at 19:00 and closes at 03:00. On Wednesday, the restaurant opens at 17:00 and closes at 23:00)
-> at 11:00 on Monday, this would return 23:00
-> at 18:00 on Monday, this would return 23:00
-> at 23:30 on Monday, this would return 03:00 (tomorrow's closing hour)
-> at 20:00 on Tuesday, this would return 03:00
-> at 01:00 on Wedneday, this would return 03:00
-> at 03:30 on Wedneday, this would return 23:00 (Wedneday's closing hour)

***** We currently run the following MySQL query:

SELECT * FROM (restaurants LEFT JOIN postcodes ON restaurants.id = postcodes.restid) LEFT JOIN reviews ON postcodes.restid = reviews.restid WHERE postcodes.postcode = '$search' GROUP BY restaurants.id

to select restaurants which serve a particular postcode. We would like this optimized and we would like 3 new variables returned: opening_time (as defined in the functions above) closing_time (as defined int he function above) and is_open (as defined above).

***** Slightly more complex. This function uses the opening hours data again. This time, pure MySQL is not required, as you can see from the spec, it is probably necessary to use PHP. Based on the current time, the function will take twoinputs: $id (the restaurant id) and $delay (a floating point value which represents minutes.)

What happens: based on the current server time, and opening times and closing times we want to produce a list of times which the user can select, at which point they can request their food.

That is - the user can request food to be delivered/collected between the current time until closing time of the restaurant on that day (again, consider that restaurants may be open past midnight), but the $delay variable will cause an offset. That is, if the $delay variable is 45, then food can only be requested in 45 minutes from the current time. So at 18:00, the earliest option to be returned is 18:45. The values returned should be in an array, and the values should be in 15 minute intervals (to the nearest quarter of an hour.) - the function will also return another variable $preorder which is boolean

Example:
Restaurant 1 opens at 17:00 and closes at 23:00 Monday, it then opens at 19;00 and closes at 03:00 on Tuesday. Delay is 50.5.

At 14:00 on Monday, this funciton would return:: all 15 minute intervals from opening + delay the next day (that is 17:00 + 50.5 rounded up to the next 15 minute segment): so every 15 minutes 18:00 - 23:00. $preorder is true.
At 18:04 on Monday, this function would return array: 19:00, 19:15, 19:30, 19:45, 20:00, 20:15, 20:30, 20:45, 21:00, 21:15, 21:30, 21:45, 22:00, 22:15, 22:30, 22:45, 23:00, $preorder would be false
At 22:09 on Monday, this function would return: 23:00, $preorder would be false
At 23:30 on Monday, this funciton would return:: all 15 minute intervals from opening + delay the next day (that is 19:00 + 50.5 rounded up to the next 15 minute segment): so every 15 minute 20:00 - 03:00. $preorder is true.

Anything when the restaurant is closed $preorder = true, anything while the restaurant is currently open $preorder = false.

Any clarification can be posted on the clarification board.

Urgent bids please. Thanks.