-- CREATION DU CURSEUR

DELIMITER $$

USE `test_vitalis`$$

DROP PROCEDURE IF EXISTS `sp_valid_fact_prest_periode_rp`$$

CREATE PROCEDURE sp_valid_fact_prest_periode_rp (
	p_debut DATE,
	p_fin 	DATE,
	p_idSaisie VARCHAR(50),
	p_codeUtilisateur VARCHAR(50)
)
BEGIN
	DECLARE finished INTEGER DEFAULT 0;
	DECLARE c_numero INTEGER DEFAULT 0;

	DECLARE curnumeroFeuilleMaladie 
		CURSOR FOR
				SELECT 	a.numeroFeuilleMaladie
				FROM p_feuillemaladie a
				JOIN prestataire b ON (b.codePrestataire = a.codePrestataire)
				JOIN beneficiaire c ON (c.id = a.idBeneficiaire)
				WHERE a.facture = "0"
				AND a.userFacturation = ""
				AND a.idPolice !="62"
				AND a.idPolice IN (SELECT id FROM police)
				AND dateConsultation BETWEEN (@debut) AND (@fin)
				AND idFactureFeuille="0" 
				AND a.fraisRetenu > "0"
				ORDER BY numeroFeuilleMaladie;
	DECLARE CONTINUE HANDLER 
        FOR NOT FOUND SET finished = 1;

	OPEN curnumeroFeuilleMaladie;

	    getnumeroFeuilleMaladie: LOOP
		FETCH curnumeroFeuilleMaladie INTO c_numero;
			IF finished = 1 THEN 
				LEAVE getnumeroFeuilleMaladie;
			END IF;
		       CALL sp_facturer_feuille_maladie_rp(c_numero,p_idSaisie,p_codeUtilisateur) ;
		-- CALL sp_facturer_feuille_maladie_rp(curnumeroFeuilleMaladie,"62972cbfbd192","kramo");
	        END LOOP getnumeroFeuilleMaladie;
	CLOSE curnumeroFeuilleMaladie;
END$$
DELIMITER ;


-- EXECUTION DU CURSEURS

SET @debut ="2022-06-16", @fin ="2022-06-30",@idSaisie="62972cbfbd192",@code="kramo"; 

CALL sp_valid_fact_prest_periode_rp(@debut,@fin,@idSaisie,@code); 

SELECT facture,userFacturation,user_id_substitut
FROM p_feuillemaladie a
JOIN prestataire b ON (b.codePrestataire = a.codePrestataire)
JOIN beneficiaire c ON (c.id = a.idBeneficiaire)
WHERE a.facture = "1"
AND a.userFacturation = "kramo"
AND a.idPolice !="62"
AND a.idPolice IN (SELECT id FROM police)
AND dateConsultation BETWEEN (@debut) AND (@fin)
-- AND idFactureFeuille="0" 
-- AND a.fraisRetenu > "0"
ORDER BY numeroFeuilleMaladie;