

DELIMITER $$
DROP PROCEDURE IF EXISTS conceptos_timbre;
CREATE PROCEDURE conceptos_timbre
(recibo$ BIGINT)
BEGIN
	DECLARE recibo_$ INT;
	SET recibo_$ := (SELECT id FROM recibos WHERE folio = recibo$);
	CASE (SELECT estado FROM recibos WHERE id = recibo_$)
		WHEN 80 THEN
			SELECT '1' AS cantidad, '01010101' AS servicio, 'E48' AS unidad, CONCAT('Cuota mantto. ', fechas(m.periodo, 2)) AS concepto, obtiene_valor_mes(m.recibo, m.periodo) - calculaDescuento(m.descuento, obtiene_valor_mes(m.recibo, m.periodo)) + calculaActualizacion(m.indice, obtiene_valor_mes(m.recibo, m.periodo)-calculaDescuento(m.descuento, obtiene_valor_mes(m.recibo, m.periodo))) + calculaRecargo(m.periodo, cast(m.registro as date), obtiene_valor_mes(m.recibo, m.periodo)-calculaDescuento(m.descuento, obtiene_valor_mes(m.recibo, m.periodo)), m.recargo, m.indice) as importe, '01' AS objeto, obtiene_valor_mes(m.recibo, m.periodo) - calculaDescuento(m.descuento, obtiene_valor_mes(m.recibo, m.periodo)) + calculaActualizacion(m.indice, obtiene_valor_mes(m.recibo, m.periodo)-calculaDescuento(m.descuento, obtiene_valor_mes(m.recibo, m.periodo))) + calculaRecargo(m.periodo, cast(m.registro as date), obtiene_valor_mes(m.recibo, m.periodo)-calculaDescuento(m.descuento, obtiene_valor_mes(m.recibo, m.periodo)), m.recargo, m.indice) AS total
			FROM mantto AS m
			INNER JOIN recibos AS r ON m.recibo = r.id
			INNER JOIN cuotas AS c ON m.cuota = c.id
			LEFT JOIN descuentos AS d ON m.descuento = d.id
			WHERE m.recibo = recibo_$;
		WHEN 88 THEN
			SELECT s.cantidad AS cantidad, '01010101' AS servicio, 'E48' AS unidad, ss.descripcion AS concepto, ss.importe AS importe, '01' AS objeto, (ss.importe*s.cantidad) AS total
			FROM servicio AS s
			INNER JOIN servicios AS ss ON s.concepto = ss.id
			WHERE s.recibo = recibo_$;
		ELSE
			SELECT '1' AS cantidad, '01010101' AS servicio, 'E48' AS unidad, 'Pago de convenio' AS concepto, m.pago AS importe, '01' AS objeto, m.pago AS total
			FROM mantto AS m
			INNER JOIN recibos AS r ON m.recibo = r.id
			WHERE m.recibo = recibo_$;
	END CASE;
END
$$

DELIMITER $$
DROP PROCEDURE IF EXISTS datos_timbre;
CREATE PROCEDURE datos_timbre
(recibo$ BIGINT)
BEGIN
	DECLARE id_$ BIGINT;
	DECLARE folio_$ VARCHAR(30);
	SET id_$ := (SELECT id FROM recibos WHERE folio = recibo$);
	SET folio_$ = folio_timbre(id_$);
	SELECT CONCAT(DATE_FORMAT(NOW(), '%Y-%m-%d'), 'T', TIME(NOW())) AS fecha, folio_$ AS folio, f.clave AS forma, total_recibo(r.id, r.estado) AS subtotal, total_recibo(r.id, r.estado) AS total, YEAR(r.registro) AS periodo, DATE_FORMAT(r.registro, '%m') AS meses, u.clave AS uso
	FROM recibos AS r
	INNER JOIN formas AS f ON r.forma = f.id
	INNER JOIN usos AS u ON r.uso = u.id
	WHERE r.id = id_$;
END
$$

DELIMITER $$
DROP PROCEDURE IF EXISTS cRecibo;
CREATE PROCEDURE cRecibo
(recibo$ BIGINT)
BEGIN
	DECLARE id_$ BIGINT;
	SET id_$ := (SELECT id FROM recibos WHERE folio = recibo$);
SELECT descifrar(p.predio) AS predio, CONCAT('LOTE: ', descifrar(p.lote)) AS lote, CONCAT('CALLE: ', descifrar(p.calle), ' ', descifrar(p.exterior)) AS calle, u.clave AS clave_uso, u.descripcion AS uso, f.clave AS clave_forma, f.descripcion AS forma, m.clave AS clave_metodo, m.descripcion AS metodo, CASE WHEN r.convenio = 0 THEN '' ELSE CONCAT('CONVENIO<br>DE ', fechas(c.inicio, 2), ' A ', fechas(c.fin, 2), '<br>IMPORTE CONVENIADO: $', FORMAT(c.valor, 2)) END AS convenio, CONCAT('REFERENCIA DE PAGO: ', descifrar(r.referencia)) AS referencia, CASE WHEN r.timbre <> 0 AND t.estado <> 404 THEN t.clave ELSE '' END AS timbre, r.nota AS nota, CONCAT('FECHA DE PAGO: ', fechas(r.registro, 3)) AS registro, r.estado AS estado, CONCAT(estados(CAST(SUBSTRING(p.uso, 1, 3) AS INT)), ' - ', estados(CAST(SUBSTRING(p.uso, 5) AS INT))) AS uso_predio, propietario_predio(p.id) AS propietario, CASE WHEN r.timbre = 0 THEN '' ELSE fechas(t.registro, 2) END AS fecha_timbre, case when r.timbre = 0 then '' else ExtractValue(t.cfdi, '//@UUID') end AS uuid_factura
	FROM recibos AS r
	INNER JOIN predios AS p ON r.predio = p.id
	INNER JOIN usos AS u ON r.uso = u.id
	INNER JOIN formas AS f ON r.forma = f.id
	INNER JOIN metodos AS m ON r.metodo = m.id
	LEFT JOIN convenios AS c ON r.convenio = c.id
	LEFT JOIN timbres AS t ON r.timbre = t.id
	WHERE r.id = id_$;
END
$$

-- >> consulta los conceptos de pago
DELIMITER $$
DROP PROCEDURE IF EXISTS cConceptosRecibos;
CREATE PROCEDURE cConceptosRecibos
(recibo$ BIGINT)
BEGIN
	-- variable para id del recibo y tipo de pago
	DECLARE id_$ BIGINT;
	DECLARE estado_$ INT;
	SET id_$ := (SELECT id FROM recibos WHERE folio = recibo$);
	SET estado_$ := (SELECT estado FROM recibos WHERE id = id_$);
	IF estado_$ = 88 THEN
		SELECT ss.descripcion AS concepto, ss.importe AS importe, s.cantidad AS cantidad, (ss.importe*s.cantidad) AS total FROM servicio AS s
		INNER JOIN servicios AS ss ON s.concepto = ss.id
		WHERE s.recibo = id_$;
	ELSEIF estado_$ = 80 THEN
		SELECT fechas(m.periodo, 2) AS concepto, obtiene_valor_mes(id_$, m.periodo) AS importe, calculaDescuento(m.descuento, obtiene_valor_mes(id_$, m.periodo)) AS descuento, calculaActualizacion(m.indice, (obtiene_valor_mes(id_$, m.periodo) - calculaDescuento(m.descuento, obtiene_valor_mes(id_$, m.periodo)))) AS actualizacion, calculaRecargo(m.periodo, CAST(m.registro AS DATE), obtiene_valor_mes(id_$, m.periodo) - calculaDescuento(m.descuento, obtiene_valor_mes(id_$, m.periodo)), m.recargo, m.indice) AS recargo, (obtiene_valor_mes(id_$, m.periodo) - calculaDescuento(m.descuento, obtiene_valor_mes(id_$, m.periodo)) + calculaActualizacion(m.indice, (obtiene_valor_mes(id_$, m.periodo) - calculaDescuento(m.descuento, obtiene_valor_mes(id_$, m.periodo)))) + calculaRecargo(m.periodo, CAST(m.registro AS DATE), obtiene_valor_mes(id_$, m.periodo) - calculaDescuento(m.descuento, obtiene_valor_mes(id_$, m.periodo)), m.recargo, m.indice)) as total
		FROM mantto AS m
		WHERE m.recibo = id_$;
	ELSE
		SELECT pagado_convenio_fecha(r.convenio, r.registro) AS anterior, m.pago AS total, (pagado_convenio_fecha(r.convenio, r.registro) - m.pago) AS deuda
		FROM mantto AS m
		INNER JOIN recibos AS r ON m.recibo = r.id
		WHERE m.recibo = id_$;
	END IF;
END
$$

-- >>> consulta los pagos
DELIMITER $$
DROP PROCEDURE IF EXISTS cRecibos;
CREATE PROCEDURE cRecibos
(inicio$ DATE, fin$ DATE, predio$ VARCHAR(10), filtro$ INT, servicio$ VARCHAR(20))
BEGIN
	-- variable para predio
	DECLARE predio_$ INT DEFAULT(0);
	DECLARE servicio_$ INT;
	IF predio$ <> '' THEN
		SET predio_$ := (SELECT id FROM predios WHERE descifrar(predio) = predio$);
	END IF;
	CASE filtro$
		WHEN 0 THEN
			IF predio_$ = 0 THEN
				SELECT DISTINCT r.folio AS folio, CASE WHEN t.id IS NULL THEN '' ELSE CONCAT('FCB', t.clave) END AS timbre, fechas(r.registro, 3) AS fecha, CONCAT('Predio ', descifrar(p.predio), ', Lote: ', descifrar(p.lote), ', Calle ', descifrar(calle), ' ', descifrar(exterior)) AS predio, descifrar(r.referencia) AS referencia, CASE r.estado WHEN 88 THEN ss.descripcion WHEN 80 THEN 'MANTENIMIENTO' ELSE 'CONVENIO' END AS estado, total_recibo(r.id, r.estado) AS total
				FROM recibos AS r
				INNER JOIN predios AS p ON r.predio = p.id
				LEFT JOIN timbres AS t ON r.timbre = t.id
				LEFT JOIN servicio AS s ON r.id = s.recibo
				LEFT JOIN servicios AS ss ON s.concepto = ss.id
				WHERE CAST(r.registro AS DATE) BETWEEN inicio$ AND fin$ AND r.estado <> 1000
				ORDER BY r.registro;
			ELSE
				SELECT DISTINCT r.folio AS folio, CASE WHEN t.id IS NULL THEN '' ELSE CONCAT('FCB', t.clave) END AS timbre, descifrar(r.referencia) AS referencia, CASE r.estado WHEN 11 THEN 'Convenio' WHEN 88 THEN ss.descripcion ELSE 'Mantenimiento' END AS estado, total_recibo(r.id, r.estado) AS total, fechas(r.registro, 3) AS fecha
				FROM recibos AS r
				LEFT JOIN timbres AS t ON r.timbre = t.id
				LEFT JOIN servicio AS s ON r.id = s.recibo
				LEFT JOIN servicios AS ss ON s.concepto = ss.id
				WHERE CAST(r.registro AS DATE) BETWEEN inicio$ AND fin$ AND r.estado <> 1000 AND r.predio = predio_$
				ORDER BY r.registro;
			END IF;
		WHEN 11 THEN
			IF predio_$ = 0 THEN
				SELECT DISTINCT r.folio AS folio, CASE WHEN t.id IS NULL THEN '' ELSE CONCAT('FCB', t.clave) END AS timbre, CONCAT('Predio ', descifrar(p.predio), ', Lote: ', descifrar(p.lote), ', Calle ', descifrar(p.calle), ' ', descifrar(p.exterior)) AS predio, descifrar(r.referencia) AS referencia, 'Convenio' AS estado, total_recibo(r.id, r.estado) AS total, fechas(r.registro, 3) AS fecha
				FROM recibos AS r
				INNER JOIN predios AS p ON r.predio = p.id
				LEFT JOIN timbres AS t ON r.timbre = t.id
				WHERE CAST(r.registro AS DATE) BETWEEN inicio$ AND fin$ AND r.estado = 11
				ORDER BY r.registro;
			ELSE
				SELECT DISTINCT r.folio AS folio, CASE WHEN t.id IS NULL THEN '' ELSE CONCAT('FCB', t.clave) END AS timbre, descifrar(r.referencia) AS referencia, 'Convenio' AS estado, total_recibo(r.id, r.estado) AS total, fechas(r.registro, 3) AS fecha
				FROM recibos AS r
				LEFT JOIN timbres AS t ON r.timbre = t.id
				WHERE CAST(r.registro AS DATE) BETWEEN inicio$ AND fin$ AND r.estado = 11 AND r.predio = predio_$
				ORDER BY r.registro;
			END IF;
		WHEN 88 THEN
			SET servicio_$ := (SELECT id FROM servicios WHERE clave = servicio$ AND estado = 11);
			IF predio_$ = 0 THEN
				SELECT DISTINCT r.folio AS folio, CASE WHEN t.id IS NULL THEN '' ELSE CONCAT('FCB', t.clave) END AS timbre, CONCAT('Predio ', descifrar(p.predio), ', Lote: ', descifrar(p.lote), ', Calle ', descifrar(p.calle), ' ', descifrar(p.exterior)) AS predio, descifrar(r.referencia) AS referencia, ss.descripcion AS estado, total_recibo(r.id, r.estado) AS total, fechas(r.registro, 3) AS fecha
				FROM recibos AS r
				INNER JOIN predios AS p ON r.predio = p.id
				LEFT JOIN timbres AS t ON r.timbre = t.id
				INNER JOIN servicio AS s ON r.id = s.recibo
				INNER JOIN servicios AS ss ON s.concepto = ss.id
				WHERE CAST(r.registro AS DATE) BETWEEN inicio$ AND fin$ AND r.estado = 88 AND s.concepto = servicio_$
				ORDER BY r.registro;
			ELSE
				SELECT DISTINCT r.folio AS folio, CASE WHEN t.id IS NULL THEN '' ELSE CONCAT('FCB', t.clave) END AS timbre, CONCAT('Predio ', descifrar(p.predio), ', Lote: ', descifrar(p.lote), ', Calle ', descifrar(p.calle), ' ', descifrar(p.exterior)) AS predio, descifrar(r.referencia) AS referencia, ss.descripcion AS estado, total_recibo(r.id, r.estado) AS total, fechas(r.registro, 3) AS fecha
				FROM recibos AS r
				INNER JOIN predios AS p ON r.predio = p.id
				LEFT JOIN timbres AS t ON r.timbre = t.id
				INNER JOIN servicio AS s ON r.id = s.recibo
				INNER JOIN servicios AS ss ON s.concepto = ss.id
				WHERE CAST(r.registro AS DATE) BETWEEN inicio$ AND fin$ AND r.estado = 88 AND s.concepto = servicio_$ AND r.predio = predio_$
				ORDER BY r.registro;
			END IF;
		ELSE
			IF predio_$ = 0 THEN
				SELECT DISTINCT r.folio AS folio, CASE WHEN t.id IS NULL THEN '' ELSE CONCAT('FCB', t.clave) END AS timbre, CONCAT('Predio ', descifrar(p.predio), ', Lote: ', descifrar(p.lote), ', Calle ', descifrar(p.calle), ' ', descifrar(p.exterior)) AS predio, descifrar(r.referencia) AS referencia, 'Mantenimiento' AS estado, total_recibo(r.id, r.estado) AS total, fechas(r.registro, 3) AS fecha
				FROM recibos AS r
				INNER JOIN predios AS p ON r.predio = p.id
				LEFT JOIN timbres AS t ON r.timbre = t.id
				WHERE CAST(r.registro AS DATE) BETWEEN inicio$ AND fin$ AND r.estado = 80
				ORDER BY r.registro;
			ELSE
				SELECT DISTINCT r.folio AS folio, CASE WHEN t.id IS NULL THEN '' ELSE CONCAT('FCB', t.clave) END AS timbre, descifrar(r.referencia) AS referencia, 'Mantenimiento' AS estado, total_recibo(r.id, r.estado) AS total, fechas(r.registro, 3) AS fecha
				FROM recibos AS r
				LEFT JOIN timbres AS t ON r.timbre = t.id
				WHERE CAST(r.registro AS DATE) BETWEEN inicio$ AND fin$ AND r.estado = 80 AND r.predio = predio_$
				ORDER BY r.registro;
			END IF;
	END CASE;	
END
$$

-- >>> registra el pago de los convenios
DELIMITER $$
DROP PROCEDURE IF EXISTS iReciboConvenio;
CREATE PROCEDURE iReciboConvenio
(predio$ VARCHAR(10), usuario$ VARCHAR(10), uso$ VARCHAR(5), forma$ VARCHAR(3), metodo$ VARCHAR(3), referencia$ VARCHAR(20), nota$ TEXT, convenio$ VARCHAR(10), pago$ FLOAT)
BEGIN
	-- variable para id de predio, usuario, uso CFDI, forma de pago, metodo de pago, convenio, el valor del convenio y los pagado del convenio y folio del recibo
        DECLARE predio_$ INT;
        DECLARE usuario_$ INT;
        DECLARE uso_$ INT;
        DECLARE forma_$ INT;
        DECLARE metodo_$ INT;
		DECLARE convenio_$ INT;
		DECLARE valor_$ FLOAT;
		DECLARE pagado_$ FLOAT;
        DECLARE folio_$ BIGINT DEFAULT(88);
        SET predio_$ := (SELECT id FROM predios WHERE descifrar(predio) = predio$ and estado_registro = 11);
        SET usuario_$ := (SELECT id FROM usuarios WHERE clave = usuario$);
        SET uso_$ := (SELECT id FROM usos WHERE clave = uso$);
        SET metodo_$ := (SELECT id FROM metodos WHERE clave = metodo$);
        SET forma_$ := (SELECT id FROM formas WHERE clave = forma$);
	SET convenio_$ := (SELECT id FROM convenios WHERE clave = convenio$);
	SET valor_$ := (SELECT valor FROM convenios WHERE id = convenio_$);
	SET pagado_$ := (SELECT CASE WHEN r.id IS NULL THEN 0 ELSE SUM(m.pago) END FROM recibos AS r JOIN mantto AS m ON r.id = m.recibo WHERE r.convenio = convenio_$ AND r.estado = 11);
        IF EXISTS (SELECT id FROM recibos) THEN
                SET folio_$ := (SELECT (folio+1) FROM recibos ORDER BY folio DESC LIMIT 1);
        END IF;
	IF (valor_$ - pagado_$) > 1 THEN
				INSERT INTO recibos(folio, predio, usuario, uso, forma, metodo, convenio, referencia, nota, timbre, estado, registro)
                VALUES(folio_$, predio_$, usuario_$, uso_$, forma_$, metodo_$, convenio_$, cifrar(referencia$), nota$, 0, 11, NOW());
		IF (ROW_COUNT()) = 1 THEN
			SET folio_$ := (SELECT id FROM recibos WHERE folio = folio_$);
			IF (valor_$-(pagado_$+pago$)) <= 1 THEN
				INSERT INTO mantto(recibo, periodo, cuota, descuento, recargo, pago, estado)
				SELECT folio_$, fin, 0, 0, 0, (valor_$ - pagado_$), '' FROM convenios WHERE id = convenio_$;
			ELSE
				INSERT INTO mantto(recibo, periodo, cuota, descuento, recargo, pago, estado)
                SELECT folio_$, inicio, 0, 0, 0, pago$, '' FROM convenios WHERE id = convenio_$;
			END IF;
			SELECT 800 AS estado, 'Se registró el pago' AS mssg, folio FROM recibos WHERE id = folio_$;
		ELSE
			SELECT 500 AS estado, 'Intenta de nuevo' AS mssg;
		END IF;
	ELSE
		SELECT 500 AS estado, 'El convenio ya esta pagado' AS mssg;
	END IF;
END
$$

-- >>> registra el pago de los servicios
DELIMITER $$
DROP PROCEDURE IF EXISTS iReciboServicio;
CREATE PROCEDURE iReciboServicio
(predio$ VARCHAR(10), usuario$ VARCHAR(10), uso$ VARCHAR(5), forma$ VARCHAR(3), metodo$ VARCHAR(3), referencia$ VARCHAR(20), nota$ TEXT, servicio$ VARCHAR(20), cantidad$ INT)
BEGIN
        DECLARE predio_$ INT;
        DECLARE usuario_$ INT;
        DECLARE uso_$ INT;
        DECLARE forma_$ INT;
        DECLARE metodo_$ INT;
        DECLARE folio_$ BIGINT DEFAULT(88);
		DECLARE servicio_$ INT;
		SET servicio_$ := (SELECT id FROM servicios WHERE clave = servicio$ AND estado = 11);
        SET predio_$ := (SELECT id FROM predios WHERE descifrar(predio) = predio$ and estado_registro = 11);
        SET usuario_$ := (SELECT id FROM usuarios WHERE clave = usuario$);
        SET uso_$ := (SELECT id FROM usos WHERE clave = uso$);
        SET metodo_$ := (SELECT id FROM metodos WHERE clave = metodo$);
        SET forma_$ := (SELECT id FROM formas WHERE clave = forma$);
        IF EXISTS (SELECT id FROM recibos) THEN
                SET folio_$ := (SELECT (folio+1) FROM recibos ORDER BY folio DESC LIMIT 1);
        END IF;
		INSERT INTO recibos(folio, predio, usuario, uso, forma, metodo, convenio, referencia, timbre, nota, estado, registro)
        VALUES(folio_$, predio_$, usuario_$, uso_$, forma_$, metodo_$, 0, cifrar(referencia$), 0, nota$, 88, NOW());
        IF (ROW_COUNT()) = 1 THEN
            INSERT INTO servicio(recibo, concepto, cantidad)
			SELECT id, servicio_$, cantidad$ FROM recibos WHERE folio = folio_$;
            SELECT 800 AS estado, 'Se registró el pago' AS mssg, folio_$ AS folio;
        ELSE
        	SELECT 500 AS estado, 'Intenta de nuevo' AS mssg;
       	END IF;
END
$$

DELIMITER $$
DROP PROCEDURE IF EXISTS iReciboParcial;
CREATE PROCEDURE iReciboParcial
(predio$ VARCHAR(5), usuario$ VARCHAR(10), uso$ VARCHAR(5), forma$ VARCHAR(3), metodo$ VARCHAR(3), referencia$ VARCHAR(20), nota$ TEXT, conceptos$ LONGTEXT)
BEGIN
	 -- variable para id de predio, usuario, uso CFDI, forma de pago, metodo de pago y folio del recibo
	DECLARE predio_$ INT;
	DECLARE usuario_$ INT;
	DECLARE uso_$ INT;
	DECLARE forma_$ INT;
	DECLARE metodo_$ INT;
	DECLARE folio_$ BIGINT DEFAULT(88);
	SET predio_$ := (SELECT id FROM predios WHERE descifrar(predio) = predio$ AND estado_registro = 11);
	SET usuario_$ := (SELECT id FROM usuarios WHERE clave = usuario$);
	SET uso_$ := (SELECT id FROM usos WHERE clave = uso$);
	SET metodo_$ := (SELECT id FROM metodos WHERE clave = metodo$);
	SET forma_$ := (SELECT id FROM formas WHERE clave = forma$);
	IF EXISTS (SELECT id FROM recibos) THEN
		SET folio_$ := (SELECT (folio+1) FROM recibos ORDER BY folio DESC LIMIT 1);
	END IF;
	 INSERT INTO recibos(folio, predio, usuario, uso, forma, metodo, convenio, referencia, timbre, nota, estado, registro)
	 VALUES(folio_$, predio_$, usuario_$, uso_$, forma_$, metodo_$, 0, cifrar(referencia$), 0, nota$, 80, NOW());
	 IF (ROW_COUNT()) = 1 THEN
        delete from mantto where estado = usuario$;
		  SET folio_$ := (SELECT id FROM recibos WHERE folio = folio_$);
		  insert into mantto(recibo, periodo, cuota, descuento, indice, recargo, pago, estado, registro)
        select case when jt.recibo = 0 then folio_$ else jt.recibo end, jt.periodo, obtieneCuota(predio_$, year(cast(jt.periodo as date))), 0, obtieneIndice(jt.periodo, curdate()), obtieneRecargo(jt.periodo), jt.pago, '', now()
        from json_table(
            conceptos$, '$[*]' columns(
                recibo bigint path '$.recibo',
                periodo varchar(30) path '$.periodo',
                pago decimal(10,2) path '$.pago'
            )
        ) as jt;
		  SELECT 800 AS estado, 'Se registró el pago' AS mssg, folio FROM recibos WHERE id = folio_$;
	 ELSE
		  SELECT 500 AS estado, 'Intenta de nuevo' AS mssg;
	 END IF;
END
$$

-- >>> finaliza un pago de mantto
DELIMITER $$
DROP PROCEDURE IF EXISTS iReciboMantto;
CREATE PROCEDURE iReciboMantto
(predio$ VARCHAR(8), usuario$ VARCHAR(10), uso$ VARCHAR(5), forma$ VARCHAR(3), metodo$ VARCHAR(3), referencia$ VARCHAR(20), nota$ TEXT)
BEGIN
	 -- variable para id de predio, usuario, uso CFDI, forma de pago, metodo de pago y folio del recibo
	DECLARE predio_$ INT;
	DECLARE usuario_$ INT;
	DECLARE uso_$ INT;
	DECLARE forma_$ INT;
	DECLARE metodo_$ INT;
	DECLARE folio_$ BIGINT DEFAULT(88);
	SET predio_$ := (SELECT id FROM predios WHERE descifrar(predio) = predio$ AND estado_registro = 11);
	SET usuario_$ := (SELECT id FROM usuarios WHERE clave = usuario$);
	SET uso_$ := (SELECT id FROM usos WHERE clave = uso$);
	SET metodo_$ := (SELECT id FROM metodos WHERE clave = metodo$);
	SET forma_$ := (SELECT id FROM formas WHERE clave = forma$);
	IF EXISTS (SELECT id FROM recibos) THEN
		SET folio_$ := (SELECT (folio+1) FROM recibos ORDER BY folio DESC LIMIT 1);
	END IF;
		INSERT INTO recibos(folio, predio, usuario, uso, forma, metodo, convenio, referencia, timbre, nota, estado, registro)
		VALUES(folio_$, predio_$, usuario_$, uso_$, forma_$, metodo_$, 0, cifrar(referencia$), 0, nota$, 80, NOW());
		IF (ROW_COUNT()) = 1 THEN
			SET folio_$ := (SELECT id FROM recibos WHERE folio = folio_$);
			UPDATE mantto set recibo = folio_$, estado = '', registro = now() where recibo = (predio$*-1) and estado = usuario$;
			SELECT 800 AS estado, 'Se registró el pago' AS mssg, folio FROM recibos WHERE id = folio_$;
		ELSE
			SELECT 500 AS estado, 'Intenta de nuevo' AS mssg;
		END IF;
END
$$
