Повторяющиеся события, SQL-запрос
Мне известно, что вопросы о повторяющихся событиях встречаются часто, но мне не удалось найти вопрос с ответом на этот конкретный вопрос о повторяющихся событиях, кроме тех, которые связаны с приложениями календаря. Основным отличием является то, что события в нашем приложении. будет отображаться только в отчетах или самостоятельно, а не в формате календаря, хотя во многих отношениях они очень похожи, возможно, только с меньшим количеством багажа, связанного с календарями.
По аналогии с приложением календаря. события могут происходить либо на разовой основе, либо могут повторяться, например, каждый четверг или первый понедельник каждого месяца, до определенного заранее установленного времени в будущем.
События хранятся в таблице событий, которая содержит даты начала и окончания и «идентификатор типа рекуррентности». Если типом рекуррентности является «Нет», то даты начала и окончания будут одинаковыми. Таблица событий содержит идентификатор отдельной таблицы, в которой содержится имя типа события, например, «Встреча» или «Еженедельный отчет»
Существует еще одна таблица, которая содержит список «типов рекуррентности», например, «Нет повторений», «Каждый понедельник», «Первый понедельник месяца» и «Последняя суббота месяца».
Чтобы упростить поиск, другая таблица содержит список дат с 1960 по 2060 гг. Вместе с соответствующей информацией о каждой дате, например, является ли это понедельник и какое вхождение понедельника в месяце.
Это позволяет искать как:
SELECT DISTINCT(e.eventid),n.nameid,n.firstname,n.lastname,d.dt,r.recurring
FROM dates d
LEFT JOIN recurringtypes r
/* if event recurring every week E.g. 'Every Monday' */
ON (r.rectypeid BETWEEN 2 AND 8 AND r.day = d.dow)
/* if event recurring every month E.g. 'First Monday, every month' */
OR ((r.rectypeid BETWEEN 9 AND 36) AND r.day = d.dow AND r.occurrence = d.occurrence)
/* if event recurring every last week of month E.g. 'Last Monday, every month' */
OR (r.rectypeid >= 37 AND r.day = d.dow and r.islast = d.islast)
LEFT JOIN events e on e.rectypeid = r.rectypeid
LEFT JOIN eventtypes t ON e.eventtypeid = t.eventtypeid
LEFT JOIN names n ON e.namesid = n.namesid
WHERE (d.dt BETWEEN '2012/02/01' AND '2012/05/01')
ORDER BY d.dt;
Это именно то, что требуется для нахождения повторяющихся событий, выдавая вывод:
+-----------+---------------+-------------------+-----------+------------+-------------------------------+
| eventid | nameid | lastname | firstname | dt | recurring |
+-----------+---------------+-------------------+-----------+------------+-------------------------------+
| 3291788 | 1728449 | smith | zoe | 2012-02-02 | First Thursday, every month |
| 3291797 | 1765432 | | | 2012-02-05 | First Sunday, every month |
| 3291798 | 1730147 | | | 2012-02-05 | First Sunday, every month |
| 3291803 | 1790061 | Carpenter | Richie | 2012-02-06 | Every Monday |
| 3291805 | 1790061 | Carpenter | Richie | 2012-02-08 | Second Wednesday, every month |
| 3291803 | 1790061 | Carpenter | Richie | 2012-02-13 | Every Monday |
| 3291799 | 1790061 | Carpenter | Richie | 2012-02-15 | Third Wednesday, every month |
| 3291803 | 1790061 | Carpenter | Richie | 2012-02-20 | Every Monday |
Чтобы получить не повторяющиеся события, можно использовать более простой запрос:
SELECT n.nameid,n.lastname,n.firstname,e.firstdate,e.eventid,'No' as Recurring
FROM events e
LEFT JOIN names n ON n.names = e.namesid
AND e.rectypeid <= 1
AND e.firstdate BETWEEN '2012/02/01' AND '2012/05/01'
AND e.eventid IS NOT NULL ORDER BY e.firstdate;
Мой вопрос: как я могу объединить эти запросы, чтобы получить один список, который содержит все события, как повторяющиеся, так и не повторяющиеся в порядке дат?
Это таблицы и сокращенные выборки из них, некоторые столбцы и все индексы были удалены для краткости :). Таблица имен не была включена по той же причине.
CREATE TABLE events (
eventid int(11) NOT NULL AUTO_INCREMENT,
eventtypeid int(11) DEFAULT '0',
firstdate date DEFAULT '1960-01-01' COMMENT 'First event',
lastdate date DEFAULT '1960-01-01' COMMENT 'Last event',
rectypeid int(11) DEFAULT '1'
);
+---------+-------------+------------+------------+-----------+
| eventid | eventtypeid | firstdate | lastdate | rectypeid |
+---------+-------------+------------+------------+-----------+
| 3291803 | 16 | 2012-02-03 | 2012-04-11 | 3 |
| 3291797 | 8 | 2012-02-12 | 2012-02-22 | 9 |
| 3291798 | 5 | 2012-02-12 | 2012-02-12 | 9 |
| 3291788 | 8 | 2012-05-24 | 2015-01-16 | 13 |
| 3291805 | 10 | 2012-01-04 | 2012-02-14 | 19 |
| 3291799 | 16 | 2012-02-07 | 2012-10-24 | 26 |
| 3291804 | 5 | 2012-02-03 | 2012-08-22 | 41 |
+---------+-------------+------------+------------+-----------+
CREATE TABLE cmseventtypes (
eventtypeid int(11) NOT NULL AUTO_INCREMENT,
eventtype varchar(50) DEFAULT '' COMMENT 'Event type AKA name'
);
+-------------+----------------------+
| eventtypeid | eventype |
+-------------+----------------------+
| 1 | Follow up meeting |
| 2 | Reminder email due |
| 3 | Monthly meeting |
| 4 | Weekly report |
| 5 | Golf practice |
+------------------------------------+
CREATE TABLE recurringtypes (
rectypeid int(11) NOT NULL AUTO_INCREMENT,
recurring varchar(40) DEFAULT '',
day tinyint(4) DEFAULT '0',
occurrence tinyint(4) DEFAULT '0',
islast tinyint(4) DEFAULT '0'
);
+-----------+---------------------------+------+------------+--------+
| rectypeid | recurring | day | occurrence | islast |
+-----------+---------------------------+------+------------+--------+
| 1 | No | 0 | 0 | 0 |
| 2 | Every Sunday | 1 | 0 | 0 |
| 3 | Every Monday | 2 | 0 | 0 |
| 4 | Every Tuesday | 3 | 0 | 0 |
| 5 | Every Wednesday | 4 | 0 | 0 |
| 6 | Every Thursday | 5 | 0 | 0 |
| 7 | Every Friday | 6 | 0 | 0 |
| 8 | Every Saturday | 7 | 0 | 0 |
| 9 | First Sunday, every month | 1 | 1 | 0 |
| 10 | First Monday, every month | 2 | 1 | 0 |
+-----------+---------------------------+------+------------+--------+
CREATE TABLE dates (
dt date NOT NULL COMMENT 'Date',
daycount mediumint(9) NOT NULL DEFAULT '1',
year smallint(6) NOT NULL DEFAULT '1970',
month tinyint(4) NOT NULL DEFAULT '1',
dom tinyint(4) NOT NULL DEFAULT '1',
dow tinyint(4) NOT NULL DEFAULT '1',
occurrence tinyint(4) NOT NULL DEFAULT '0',
islast tinyint(1) NOT NULL DEFAULT '0'
);
+------------+----------+------+-------+-----+-----+------------+--------+
| dt | daycount | year | month | dom | dow | occurrence | islast |
+------------+----------+------+-------+-----+-----+------------+--------+
| 2012-02-02 | 734900 | 2012 | 2 | 2 | 5 | 1 | 0 |
| 2012-02-03 | 734901 | 2012 | 2 | 3 | 6 | 1 | 0 |
| 2012-02-04 | 734902 | 2012 | 2 | 4 | 7 | 1 | 0 |
| 2012-02-05 | 734903 | 2012 | 2 | 5 | 1 | 1 | 0 |
| 2012-02-06 | 734904 | 2012 | 2 | 6 | 2 | 1 | 0 |
| 2012-02-07 | 734905 | 2012 | 2 | 7 | 3 | 1 | 0 |
| 2012-02-08 | 734906 | 2012 | 2 | 8 | 4 | 2 | 0 |
| 2012-02-09 | 734907 | 2012 | 2 | 9 | 5 | 2 | 0 |
+------------+----------+------+-------+-----+-----+------------+--------+
Мы не совсем настроены на использование вышеуказанного кода или макета таблицы, любые рабочие решения приветствуются. Пожалуйста, не указывайте мне на:
Как бы вы могли хранить повторяющиеся времена?
Как лучше всего моделировать повторяющиеся события в приложении календаря?
или же
http://tools.ietf.org/html/rfc5545
Я проверил их, и они были очень полезны, но делали не так, как мы собираемся.
ТИА