HUFERSIL.WEBDEVELOPER

Recorrência de Datas com MySQL

Geralmente isto é usado para quando você quer montar uma agenda, que tenha tarefas recorrentes.

Primeiramente, temos que definir os níveis de repetição que quero:

0 = não repetir
1 = diário
2 = semanal
3 = quinzenal
4 = mensal

Neste artigo, mostrarei do diário para frente.

Segunda coisa, será montar uma tabela-calendário.

Esta técnica é muito recomendada para banco de dados MySQL, e você verá que sua vida nunca mais será a mesma depois dela :D .

Vamos colocar 3 informações nesta tabela:

  • A data propriamente dita;
  • Dia da semana (indexando por 0, como no PHP);
  • Semana do ano.
CREATE TABLE calendario (
	`data` DATE NOT NULL,
	dia_semana INT NOT NULL,
	semana_ano INT NOT NULL,
	PRIMARY KEY(`data`)
) ENGINE=MyISAM;

Para popular esta tabela, vamos criar uma tabela auxiliar, com um unico campo auto-incrementável, para fazermos um insert em massa posteriormente.

CREATE TABLE numero (
	id INT NOT NULL AUTO_INCREMENT, 
	PRIMARY KEY(id)
) ENGINE=MyISAM;
 
INSERT INTO NUMERO VALUES(NULL);
INSERT INTO NUMERO SELECT NULL FROM NUMERO; // executando 12 vezes = 4096 registros

Agora, populamos nossa tabela-calendario:

INSERT INTO calendario 
SELECT
	DATE_ADD('2013-01-01', INTERVAL id-1 DAY),
	DAYOFWEEK(DATE_ADD('2013-01-01', INTERVAL id-1 DAY))-1,
	WEEKOFYEAR(DATE_ADD('2013-01-01', INTERVAL id-1 DAY))
FROM numero;

Pronto, agora temos um calendário lindão para trabalhar no banco de dados.
Neste tutorial, pensei em duas tabelas simples para começar.
Uma indicando a tarefa, e outra indicando em quais dias da semana aquela tarefa vai se repetir.
Cheguei nos seguintes exemplos:

CREATE TABLE tarefa (
	codtarefa INT NOT NULL AUTO_INCREMENT,
	codusuario INT NOT NULL,
	codtiporepeticao INT NOT NULL,
	dtainicio DATE NOT NULL,
	PRIMARY KEY(codtarefa)
) ENGINE=innodb;
 
CREATE TABLE tarefa_dia (
	codtarefadia INT NOT NULL AUTO_INCREMENT,
	codtarefa INT NOT NULL,
	dia_semana INT NOT NULL,
	PRIMARY KEY(codtarefadia),
	FOREIGN  KEY(codtarefa) REFERENCES tarefa(codtarefa)
		ON UPDATE cascade ON DELETE cascade
) ENGINE=innodb;

Também, o dia da semana é indexado por zero, como no PHP.
Agora, nossa vida ficou mega fácil!

Primeiro exemplo
Diário, se repetindo de segunda a sexta

INSERT INTO tarefa VALUES (NULL,1,1,'2013-06-28');
 
-- pega o id
SET @id = SELECT LAST_INSERT_ID();
 
-- insere os dias da semana
INSERT INTO tarefa_dia VALUES (NULL, @id, 1),(NULL, @id, 2),(NULL, @id, 3),(NULL, @id, 4),(NULL, @id, 5);
 
SELECT c.`data`, c.dia_semana
FROM calendario c, tarefa t, tarefa_dia d
WHERE t.codtarefa = d.codtarefa
  AND t.codtarefa = @id
  AND c.`data` >= t.dtainicio
  AND c.dia_semana = d.dia_semana
ORDER BY c.`data`
LIMIT 30;

Segundo exemplo
semanal, repetindo nas quartas e quintas

INSERT INTO tarefa VALUES (NULL,1,2,'2013-06-28');
 
-- pega o id
SET @id = SELECT LAST_INSERT_ID();
 
-- insere os dias da semana desejados
INSERT INTO tarefa_dia VALUES (NULL,@id,3),(NULL,@id,4);
 
-- selecionando os dias
SELECT `data`,c.dia_semana
FROM calendario c, tarefa t, tarefa_dia d
WHERE c.`data` >= t.dtainicio
  AND c.dia_semana = d.dia_semana
  AND t.codtarefa = d.codtarefa
  AND t.codtarefa = @id
ORDER BY c.`data`
LIMIT 10;

Terceiro exemplo
quinzenal, repetindo nas segundas e sextas

INSERT INTO tarefa VALUES (NULL,1,3,'2013-06-28');
 
-- pega o id inserido
SET @id = SELECT LAST_INSERT_ID();
 
-- insere os dias da semana desejados
INSERT INTO tarefa_dia VALUES (NULL,@id,1),(NULL,@id,5);
 
-- pega as datas quinzenais
SELECT `data`,c.dia_semana
FROM calendario c, tarefa t, tarefa_dia d
WHERE c.`data` >= t.dtainicio
  AND c.dia_semana = d.dia_semana
  AND t.codtarefa = d.codtarefa
  AND t.codtarefa = @id
  AND (c.semana_ano - WEEKOFYEAR(t.dtainicio)) % 2 = 0
ORDER BY c.`data`
LIMIT 10;

Quarto exemplo
mensal, repetindo todo dia 28

INSERT INTO tarefa VALUES (NULL,1,4,'2013-06-28');
 
-- pega o id
SET @id := SELECT LAST_INSERT_ID();
 
-- variavel para poder pegar os meses mais facil
SET @mes := 0;
 
-- consulta as datas
SELECT c.`data`, @mes := @mes + 1
FROM calendario c, tarefa t
WHERE c.`data` >= t.dtainicio
  AND t.codtarefa = @id
  AND c.`data` = DATE_ADD(t.dtainicio, INTERVAL @mes MONTH)
  ORDER BY c.`data`
LIMIT 10;

Como vimos, podemos fazer todos os nossos calculos agora no MySQL, sem precisar utilizar o PHP.

Desta forma, também, ficará mais fácil para extração de relatórios.

@braços e fiquem com Deus!

3 Responses to “Recorrência de Datas com MySQL”

Deixar uma resposta

O seu endereço de email não será publicado. Campos obrigatórios marcados com *

*

Pode usar estas etiquetas HTML e atributos: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre lang="" line="" escaped="" highlight="">