-- >>> consulta los convenios de los predios
DELIMITER $$
DROP PROCEDURE IF EXISTS cConveniosPredio;
CREATE PROCEDURE cConveniosPredio
(predio$ VARCHAR(8))
BEGIN
	IF EXISTS (SELECT c.id FROM convenios AS c INNER JOIN predios AS p ON c.predio = p.id WHERE descifrar(p.predio) = predio$) THEN
		SELECT c.clave AS clave, CONCAT(fechas(c.inicio, 2), ' A ', fechas(c.fin, 2)) AS periodo, c.valor AS valor, pagado_convenio(c.id) AS pagado, (c.valor - pagado_convenio(c.id)) AS deuda 
		FROM convenios AS c
		INNER JOIN predios AS p ON c.predio = p.id 
		WHERE descifrar(p.predio) = predio$;
	ELSE
		SELECT 500 AS estado, 'El predio no tiene convenios' AS mssg;
	END IF;
END
$$

-- >>> consulta el convenio
DELIMITER $$
DROP PROCEDURE IF EXISTS cConvenio;
CREATE PROCEDURE cConvenio
(clave$ VARCHAR(10))
BEGIN
	-- variable para id del convenio
	DECLARE id_$ INT;
	SET id_$ := (SELECT id FROM convenios WHERE clave = clave$);
	SELECT descifrar(p.predio) AS predio, descifrar(p.lote) AS lote, CONCAT(descifrar(p.calle), ' ', descifrar(p.exterior)) AS calle, propietario_predio(c.predio) AS propietario, estados(CAST(SUBSTRING(p.uso, 1, 3) AS INT)) AS uso, CONCAT('De ', fechas(c.inicio, 2), ' A ', fechas(c.fin, 2)) AS periodo, c.valor AS valor, c.nota AS nota, CASE WHEN r.id IS NULL THEN 0 ELSE SUM(m.pago) END AS pagado, CASE WHEN r.id IS NULL THEN c.valor ELSE (c.valor-SUM(m.pago)) END AS deuda, c.nota AS nota, CONCAT('FECHA DE REGISTRO DEL CONVENIO: ', fechas(c.registro, 3)) AS registro
	FROM convenios AS c
	INNER JOIN predios AS p ON c.predio = p.id
	LEFT JOIN recibos AS r ON c.id = r.convenio
	LEFT JOIN mantto AS m ON r.id = m.recibo
	WHERE c.id = id_$;

	SELECT r.folio AS folio, CASE WHEN t.id IS NOT NULL THEN CONCAT('CC', t.clave) ELSE '' END AS timbre , descifrar(r.referencia) AS referencia, fechas(r.registro, 3) AS fecha, m.pago AS pago
	FROM recibos AS r
	INNER JOIN mantto AS m ON m.recibo = r.id
	LEFT JOIN timbres AS t ON r.timbre = t.id
	WHERE r.convenio = id_$ AND r.estado = 11;
END
$$

-- >>> registra los convenios
DELIMITER $$
DROP PROCEDURE IF EXISTS iConvenio;
CREATE PROCEDURE iConvenio
(clave$ VARCHAR(10), predio$ VARCHAR(5), inicio$ DATE, fin$ DATE, valor$ FLOAT, nota$ TEXT)
BEGIN
	-- vairable para id del predio y convenio si se actualiza
	DECLARE id_$ INT;
	DECLARE predio_$ INT;
	SET predio_$ := (SELECT id FROM predios WHERE descifrar(predio) = predio$ and estado_registro = 11);

		IF NOT EXISTS (SELECT 88 FROM convenios WHERE DATE_FORMAT(inicio, '%Y-%m-01') BETWEEN DATE_FORMAT(inicio$, '%Y-%m-01') AND DATE_FORMAT(fin$, '%Y-%m-01') AND predio = predio_$ OR DATE_FORMAT(fin, '%Y-%m-01') BETWEEN DATE_FORMAT(inicio$, '%Y-%m-01') AND DATE_FORMAT(fin$, '%Y-%m-01') AND predio = predio_$) and exists (select 88 from mantto where recibo = (cast(predio$ as int) * -1) and periodo between date_format(inicio$, '%y-%m-01') and date_format(fin$, '%y-%m-01')) THEN
			INSERT INTO convenios(clave, predio, inicio, fin, valor, nota, registro)
			VALUES(clave(), predio_$, inicio$, fin$, valor$, nota$, NOW());
        	IF (ROW_COUNT()) = 1 THEN
            delete from mantto where recibo = (cast(predio$ as int) * -1) and periodo between date_format(inicio$, '%y-%m-01') and date_format(fin$, '%y-%m-01');
		      SELECT 800 AS estado, 'Se registró el convenio' AS mssg;
	     ELSE
		      SELECT 500 AS estado, 'Intenta de nuevo' AS mssg;
	     END IF;
		ELSE
			SELECT 500 AS estado, 'Ya existe un convenio en ese periodo' AS mssg;
		END IF;

END
$$
