[isf-wifidog] Open Closed times

[Robin Jones]

Hi all,

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 & $nodeid = node_id & $timestamp = greater than open_time but less than close_time & ($dayofweek = day_of_week & $dayofmonth = day_of_month & $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.

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

Robin Jones,
www.NetworkFusion.co.uk

-----Original Message-----
From: Benoit Grégoire [mailto:bock at step.polymtl.ca] 
Sent: 16 June 2007 14:57
To: wifidog at listes.ilesansfil.org
Cc: Robin Jones
Subject: Re: [isf-wifidog] Open Closed times

On Friday 15 June 2007, Robin Jones wrote:
> Hi all,
>
> I have been interested in WiFiDog for quite a while but at the moment 
> it is not quite to the stage of development I need it to be... I am 
> currently using PublicIP (which is based on WiFiDog with a custom Auth 
> Server) but am not happy that I am not in full control of the Auth 
> Server and cannot push for features that I need.
>
> As such I would like to become more active with your community and 
> help get WiFiDog to a point where I can use It to offer my customers 
> the best solution for them!!!

Welcome!

> This is the first thing I would like to help with (it is already a 
> feature suggestion for Auth Server 2.0), and with a little help, I 
> will try to implement it based on the feedback I receive!

Yes, http://dev.wifidog.org/ticket/116

> As I see it there are three ways to add open and closed times to WiFiDog.
>
>
> 3)     Add rules to a file on the Auth Server through a PHP interface. The
> auth server continually checks the time and when it is out of scope 
> The portal page changes so that the login form is removed and replaced 
> with the open times. Again the heartbeat will stop so the existing 
> users are forcefully removed.
>
> Some more considerations would be a white list for super users or a 
> secret login page???

This will be easy once I finish the new role system (real soon now), by simply adding a NODE_BYPASS_OPENING_HOURS permission.  There would be a separate page when the hotspot is down instead of the login page.  The little login button on the top left would still be there, but the user woul need to have the appropriate permission to actually use it.

> What do you guys think, and would you do it differently?

Well, this definitely needs to go on the auth server side in the database (not a separate file, this code is very performance critical).  Implementing the actual closing is actually REALLY easy:  change the auth response to the gateways to deny.  But as I said in april:  
-------------------------------------------------------------------------------------------------------------------
Well, while that's cool UI sugar, the first thing we need is a good opening hours model.  Something that can do (minimally) different opening hours for different days of the week, and ideally deal with exceptions (holidays, etc.).

Anybody know a good data model for this type of thing?  We need something where we can have a "is this hotspot open" in a single SQL query, otherwise there will be a considerable performance hit.
-----------------------------------------------------------------------------------------------------------------------

I didn't get any replies, so here's a first stab at it to stimulate
discussions:

node_opening_hours
-node_id
-start_time NOT NULL
-end_time NOT NULL
-day_of_the_week
-day_of_the_month
-month

The most specific rule that matches the current day would be taken into account.  The advantage of doing it rule based like this, is that it handles all exceptions, can generate a table of opening hours easily, and has a fairly simple to code admin interface.
 
The drawback is that it takes some pretty clever SQL request to do the check in a single request, but it can be done.
--
Benoit Grégoire
Technologies Coeus inc.