Eventos recorrentes, consulta SQL
Estou ciente de que perguntas sobre eventos recorrentes são comuns, mas não consegui encontrar uma com uma resposta a essa pergunta específica sobre eventos recorrentes que não sejam relacionados a aplicativos de calendário. A principal diferença é que os eventos em nosso aplicativo. só serão vistos nos relatórios ou por eles mesmos, e não no formato de calendário, embora, de muitas maneiras, sejam muito semelhantes, talvez apenas com menos bagagem associada aos calendários.
De maneira semelhante a um aplicativo de calendário. os eventos podem ocorrer de forma única ou podem ser recorrentes, por exemplo. toda quinta-feira ou primeira segunda-feira de cada mês, até um horário predefinido no futuro.
Os eventos são armazenados em uma tabela de eventos que contém as datas de início e término e o 'ID do tipo de recorrência'. Se o 'tipo de recorrência' for 'Nenhum', as datas de início e término serão as mesmas. A tabela de eventos contém um ID para uma tabela separada que contém o nome do tipo de evento, por exemplo. 'Reunião' ou 'Relatório semanal'
Existe uma tabela adicional que contém a lista de 'tipos de recorrência'. 'Sem recorrência', 'Toda segunda-feira', 'Primeira segunda-feira do mês' e 'Último sábado do mês'.
Para facilitar as pesquisas, outra tabela contém uma lista de datas de 1960 a 2060, além de informações relevantes sobre cada data, como se é uma segunda-feira e qual a ocorrência da segunda-feira no mês.
Isto permite uma pesquisa como:
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;
Qual é exatamente o necessário para encontrar eventos recorrentes, fornecendo resultados como:
+-----------+---------------+-------------------+-----------+------------+-------------------------------+
| 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 |
Para obter nenhum evento recorrente, uma consulta mais simples pode ser usada:
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;
Minha pergunta é: como posso combinar essas consultas para criar uma lista que contenha todos os eventos, recorrentes e não recorrentes em ordem de dat
Estas são as tabelas e as seleções abreviadas delas, algumas colunas e todos os índices foram removidos por questões de brevidade :). A tabela 'names' não foi incluída pelo mesmo motivo.
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 |
+------------+----------+------+-------+-----+-----+------------+--------+
Não estamos absolutamente decididos a usar o código ou o layout da tabela acima; todas as soluções de trabalho serão bem-vindas. Por favor, não me aponte para:
Como você armazenaria tempos possivelmente recorrentes?
Qual é a melhor maneira de modelar eventos recorrentes em um aplicativo de calendário?
o
http: //tools.ietf.org/html/rfc554
Eu os verifiquei e eles foram muito úteis, mas não estão fazendo o mesmo que pretendemo
TIA