Version 1 (modified by Robin Jones, 13 years ago)

Open Hours design doc

I would like to get the ball rolling on implimentation of Open Hours ruleset:

sorry if this is terrible coding but I haven't done SQL since I left college... but I wanted to keep the thread alive and will continue to develop the code (with all your help of course :))

my first stab at the WiFidog?-postgress-schema.sql

CREATE TABLE hotspotopenhours
         node_id text NOT NULL,
         open_time text NOT NULL,
         close_time text NOT NULL,
         day_of_week text DEFAULT 0 NOT NULL,                    //0 - 7,  0 = all
         day_of_month text DEFAULT 0 NOT NULL,                   //0 - 31, 0 = all
         month_of_year text DEFAULT 0 NOT NULL,                  //0 - 12, 0 = all
         specified_date date,
         rule_active boolean DEFAULT false NOT NULL,
         adminpassthrough boolean DEFAULT true NOT NULL

and my probably awfull and incorrect stab at an sql statement to process the rule! I have not gone through the code enough to find where this statement should be put and also the Vars are probably incorrect but hopefully readable!

SELECT * FROM hotspotopenhours WHERE rule_active = true AND $nodeid = node_id AND ($timestamp >= open_time AND $timestamp < close_time) AND ($dayofweek = day_of_week AND $dayofmonth = day_of_month AND $monthofyear = month_of_year OR date = now) 

THEN true 

ELSE false

the only other thing I can see that is wrong is it would be prefered that specified dates would have priority, then day of week, month etc with a value greater than 0 and lastly those with a value of 0.

This may be done through an IF statement...

if specified_date is not null then select return  
 end if  
 If day_of_week OR dat_of_month OR month_of_year < 0 then return  
 end if 

lastly I thought about an administrator/MAC passthrough rule, but don't know the best way of achiving this yet!