RRULE / iCal Ιδέες

Μπορούμε να ορίζουμε rrule σε client και server

βιβλιοθήκη php-rrule

$rset = new RSet( "DTSTART;TZID=America/New_York:19970901T090000 RRULE:FREQ=DAILY;COUNT=3 EXRULE:FREQ=DAILY;INTERVAL=2;COUNT=1 EXDATE;TZID=America/New_York:19970903T090000 RDATE;TZID=America/New_York:19970904T090000" );

βιβλιοθήκη rrule.js

rrule.js > rruleSet.valueOf() ['RRULE:FREQ=MONTHLY;COUNT=5;DTSTART=20120201T023000Z', 'RDATE:20120701T023000Z,20120702T023000Z', 'EXRULE:FREQ=MONTHLY;COUNT=2;DTSTART=20120301T023000Z', 'EXDATE:20120601T023000Z']

Η σκέψη μου μέχρι τώρα είναι να ανταλλάσουμε τέτοια patterns μεταξυ client/server.

php:

• https://github.com/plirof2/php-rrule (https://github.com/rlanvin/php-rrule/wiki/RSet),
• https://github.com/plirof2/recurr ,
• https://github.com/plirof2/When

JS:

• https://github.com/jakubroztocil/rrule rrule.js
• https://git.daplie.com/coolaj86/rrecurjs www.npmjs.com/package/rrecur

Δημιουργία RRULE pattern (DEMOs):

• http://jakubroztocil.github.io/rrule/
• https://plirof2.github.io/rrule-generator-form/

Αποθηκευση στην Βάση: http://www.telerik.com/forums/parsing-rrules-within-sql-for-recurring-events

http://www.telerik.com/forums/scheduler-expand-recurrenceevent-to-individual-events

Joomla extension : https://zcontent.net/products/zap-calendar-lite (Not enough space here to show table schema but you can download the free version of Zap Calendar Lite that has this function and look at the code (requires Joomla) )

https://stackoverflow.com/questions/39754097/how-to-query-recurring-appointments-stored-as-ical-rrule-in-a-database-table on but I'm the author of php-rrule and I think my answer will apply to your situation too.

Let's start by saying that, unless you have a very restricted set of rules and you know, for example, that no event will ever be infinite, or repeat more than X times or whatever, expanding the rule before storage is not going to work.

As far as I know, you can't do it only in SQL, you have to do it in two steps.

Query all the events where dt_start is or is before 2016-10-10 and dt_end is or is after 2016-10-10.
Discard all the events that won't occur on 2016-10-10 because of their rule (e.g. BYDAY). For this you will need a library (such as ICAL.net I guess) that can quickly calculate occurrences from the rrule field.

I recommend that you cache the result once this is done, so for any subsequent query you will be able to read the cache for 2016-10-10 instead of doing the calculation again. You have to take care of invalidating the cache anytime an event that includes 2016-10-10 between dt_start and dt_end is added, updated or deleted.