
SELECT tp.id, tp.numeroPrescription,tp.idSaisie,tp.userP,tp.quantite,tp.idDetailprescription,tp.fraisReel,tp.exclu
,tpa.idBeneficiaire,fn_delai_carence_acte_beneficiaire(@idBeneficiaire,"PH") AS delais, "PHA" AS codeGarantie,
fn_ticket_moderateur_garantie(tpa.idBeneficiaire,"PHA"),b.idPolice, b.idCollege, b.codeProduit,c.idBaremePriseEnCharge
,ap.nbreTransaction,ap.forfaitPlafond
-- INTO @numeroPrescription,@idSaisie,@userP,@quantite,@idDetailprescription,@fraisReel,@exclu,
-- @idBeneficiaire,@DateFindelaiCarence,@codeGarantie ,@tmGarantie,@idPolice, @idCollege, @codeProduit
-- @idBaremePriseEnCharge,@nbreTransaction, @forfaitPlafond
FROM p_prestationmedicaments_temp tp 
INNER JOIN p_prescription tpa ON (tpa.numeroPrescription=tp.numeroPrescription) 
INNER JOIN beneficiaire b ON (b.id =tpa.idBeneficiaire )
INNER JOIN college c ON  ((c.id=b.idCollege) AND (c.codeProduit=b.codeProduit))
INNER JOIN actesbaremepriseencharge ap ON ((c.idBaremePriseEnCharge=ap.idBaremePriseEnCharge) AND (c.codeProduit=ap.codeProduit)  AND ap.codeActe= "PH")
-- WHERE (id=p_idMedicament);   
WHERE tp.numeroPrescription=25227
LIMIT 10;

IF (@tmGarantie != "1000" AND p_tm != @tmGarantie) THEN
	SET p_tm = @tmGarantie;

DROP TEMPORARY TABLE IF EXISTS temp_situation;	 

CREATE TEMPORARY TABLE temp_situation

SELECT @idBeneficiaire AS idBeneficiaire,"PH" AS codeActe,@forfaitPlafond AS plafond,
fn_conso_acte_beneficiaire(@idBeneficiaire,"PH") AS consommation,@nbreTransaction AS nbreTransaction,
fn_nbre_sinistre_acte_beneficiaire_periode(@idBeneficiaire,"PH") AS nbreSinistre;
		

DROP TEMPORARY TABLE IF EXISTS temp_situation_temp;
	 
CREATE TEMPORARY TABLE temp_situation_temp

SELECT A.*,CASE WHEN (A.plafond > "0" AND A.plafond !="9999999999") THEN
			CASE WHEN (A.plafond-A.consommation<"0") THEN
				"0"
			ELSE
				IFNULL(A.plafond-A.consommation,0) 
			END
		ELSE
			CASE WHEN (A.plafond="0") THEN
				"0"
			ELSE
				IFNULL(A.plafond-A.consommation,0)
			END
		END AS soldeConsommation,
	CASE WHEN (A.nbreTransaction > "0") THEN
			CASE WHEN (A.nbreTransaction-A.nbreSinistre<"0") THEN
				"0"
			ELSE
				A.nbreTransaction-A.nbreSinistre 
			END
		ELSE
			"0" 
		END AS soldeNbreTransaction
	FROM temp_situation A;
	
SELECT plafond, consommation, nbreTransaction, nbreSinistre, soldeConsommation, soldeNbreTransaction
-- INTO @plafond, @consommation, @nbreTransaction, @nbreSinistre, @soldeConsommation, @soldeNbreTransaction
FROM temp_situation_temp;

SELECT p_valeurActe*@quantite INTO @prixManuel;


	IF ((@exclu="1") OR (@DateFindelaiCarence!="1970-01-01") OR (@soldeConsommation<="0")) THEN
		IF (@prixManuel > @soldeConsommation) THEN
			SELECT (@prixManuel-@soldeConsommation) INTO @montantTmTemp;
			SELECT @soldeConsommation INTO @montantArembourserTemp;
			SELECT (@prixManuel-@soldeConsommation) INTO @fraisExclu;
		
		ELSE
			SELECT @prixManuel INTO @montantTmTemp;
			SELECT "0" INTO @montantArembourserTemp;
			SELECT @prixManuel INTO @fraisExclu;
		END IF;
	ELSE
		IF (@nbreTransaction > "0") THEN
			IF (@soldeNbreTransaction <="0") THEN
				SELECT @prixManuel INTO @montantTmTemp;
				SELECT "0" INTO @montantArembourserTemp;
				SELECT @prixManuel INTO @fraisExclu;
			ELSE
				SELECT ROUND((@prixManuel*IFNULL(p_tm,"0"))/"100") INTO @montantTmTemp;
				SELECT (@prixManuel - @montantTmTemp) INTO @montantArembourserTemp;
				SELECT "0" INTO @fraisExclu;
			END IF;
		ELSE
			SELECT ROUND((@prixManuel*IFNULL(p_tm,"0"))/"100") INTO @montantTmTemp;
			SELECT (@prixManuel - @montantTmTemp) INTO @montantArembourserTemp;
			SELECT "0" INTO @fraisExclu;
		END IF; 
	END IF;
	 
	 
	UPDATE p_detailprescription SET
		valeurActeManuel= p_valeurActe,
		prixManuel = IFNULL(@prixManuel,"0"),
		montantTmTemp	= IFNULL(@montantTmTemp,"0"), 
		montantArembourserTemp  = IFNULL(@montantArembourserTemp,"0"),
		fraisRetenuTemp	 = IFNULL(@montantArembourserTemp,"0"),
		deltaPrix = p_valeurActe - valeurActe,
		fraisExclu = IFNULL(@fraisExclu,"0")
	WHERE (id=@idDetailprescription);
	 	 
	UPDATE p_prestationmedicaments_temp SET
		valeurActeManuel = p_valeurActe,
		prixManuel= IFNULL(@prixManuel,"0"),
		montantTmTemp= IFNULL(@montantTmTemp,"0"), 
		montantArembourserTemp 	= IFNULL(@montantArembourserTemp,"0"),
		fraisRetenuTemp	= IFNULL(@montantArembourserTemp,"0"),
		deltaPrix  = p_valeurActe - valeurActe,
		fraisExclu = IFNULL(@fraisExclu,"0")
	WHERE (id=p_idMedicament);
	 
	CALL `sp_p_recap_medicament_temp`(@numeroPrescription, @idSaisie, @userP);