Delimiter $$
/**
 * compute BUSINESS_UPDATE_ENTRIES_NB_RECLAIMS
 * optimisation pour le calcul fiana et autre table 
 * remplissage de la table ENTRIES_NB_RECLAIMS_VIEW_FOR_VIEW
 * contient le nombre de reclamtion par mouvement
 *
 * @TODO à appeler dans la procédure ComputeEntriesData
 * @TODO utiliser la table ENTRIES_COMPUTED_DATA 
 * !! les paramètres ne sont pas de le même ordre que dans la ps BUSINESS_UPDATE_ENTRIES_RECLAIMS !! *
 */ 
Drop Procedure if Exists BUSINESS_UPDATE_ENTRIES_NB_RECLAIMS $$
Create Procedure BUSINESS_UPDATE_ENTRIES_NB_RECLAIMS(In refdate DateTime, In idaccounts varchar(250), In identries varchar(250))
Begin
    v_entries_nb_reclaims_view: begin

-- 		set @accFilter = ifnull(idaccounts, '%');
-- 		set @entFilter = ifnull(identries, '%');

        if idaccounts Is null Then
            Set @bufenr_accMinVal = 0;
            Set @bufenr_accMaxVal = 4e7;
        Else
            Set @bufenr_accMinVal = idaccounts;
            Set @bufenr_accMaxVal = idaccounts;
        End if;

        if identries Is null Then
            Set @bufenr_entMinVal = 0;
            Set @bufenr_entMaxVal = 4e9;
        Else
            Set @bufenr_entMinVal = identries;
            Set @bufenr_entMaxVal = identries;
        End if;



		-- les champe de date des réclamation contienne l'heure
		-- on ajoute un jour pour simplifie les tests
-- 		set @buenr_refDate = date_add(refdate, interval 1 day) ;
--         set @buenr_refDate = date(refdate);
		set @buenr_refDate = date(date_add(refdate, interval 1 day)) ;


		if idaccounts is null Then
# 			truncate table ENTRIES_NB_RECLAIMS_VIEW_FOR_VIEW; -- !! commit implicite
			Delete tar from  ENTRIES_NB_RECLAIMS_VIEW_FOR_VIEW tar;

            -- provisoire
            -- correction des reclamations sans auteur (champ MEMBERS_ID_MEMBERS null)
            -- Affection du membre robot (1)
            update RECLAIMS 
            Set MEMBERS_ID_MEMBERS = 1 
            Where MEMBERS_ID_MEMBERS is null
            ;
            

		Else
			Delete ENTRIES_NB_RECLAIMS_VIEW_FOR_VIEW 
			From ENTRIES_NB_RECLAIMS_VIEW_FOR_VIEW 
			Where ENTRIES_NB_RECLAIMS_VIEW_FOR_VIEW.ENTRIES_ID_ENTRIES In (
				Select  EHA.ENTRIES_ID_ENTRIES
				From ENTRIES_has_ACCOUNTS EHA
				Where 
                    EHA.ACCOUNTS_ID_ACCOUNTS between @bufenr_accMinVal And @bufenr_accMaxVal
--                     EHA.ACCOUNTS_ID_ACCOUNTS like @accFilter
					And EHA.ENTRIES_ID_ENTRIES between @bufenr_entMinVal And @bufenr_entMaxVal
-- 					And EHA.ENTRIES_ID_ENTRIES Like @entFilter
					And ENTRIES_NB_RECLAIMS_VIEW_FOR_VIEW.ENTRIES_ID_ENTRIES = EHA.ENTRIES_ID_ENTRIES
			);
		End if;
--         Drop Table If exists tmp_entries_nb_reclaims_view;
--         Create Table tmp_entries_nb_reclaims_view AS
        Insert Into ENTRIES_NB_RECLAIMS_VIEW_FOR_VIEW (  
            ENTRIES_ID_ENTRIES
            , RECLAIMS_NB_OPENED
            , RECLAIMS_NB_CLOSED
            , RECLAIMS_NB
            , RECLAIMS_NB_BLOCKED
        )
        SELECT
            RCLDT.ENTRIES_ID_ENTRIES
            ,count(
                Case
                    -- cas standard ouvert
                    When RCLDT.RECLAIMS_DETAILS_CLOSED = 0 
                            And RCLDT.RECLAIMS_DETAILS_LAST_UPDATE < @buenr_refDate
                        Then RCL.ID_RECLAIMS
                    
                    -- cas clos dans le futur (retro calcul) -> ouvert
                    When RCLDT.RECLAIMS_DETAILS_CLOSED <> 0
                            And RCLDT.RECLAIMS_DETAILS_LAST_UPDATE >= @buenr_refDate
                        Then RCL.ID_RECLAIMS
                    Else
                        -
                        null
                End
--                 Case 
-- 						When 
-- 							(
-- 								(
-- 									RCLDT.RECLAIMS_DETAILS_CLOSED = 0 
-- 									And RCLDT.RECLAIMS_DETAILS_LAST_UPDATE < @buenr_refDate
-- 								)
-- 								Or (
-- 									RCLDT.RECLAIMS_DETAILS_CLOSED <> 0
-- 									And RCLDT.RECLAIMS_DETAILS_LAST_UPDATE >= @buenr_refDate
-- 								)
-- 							)
-- 						Then 
-- 								RCL.ID_RECLAIMS
-- 						else 
-- 								null 
--                         end
            ) RECLAIMS_NB_OPENED
            ,count(
                Case
                    -- cas standard fermé
                    When RCLDT.RECLAIMS_DETAILS_CLOSED <> 0
                            AND RCLDT.RECLAIMS_DETAILS_LAST_UPDATE < @buenr_refDate
                        Then RCL.ID_RECLAIMS

                    -- cas ouvert dans le future -> retro calcul
                    When RCLDT.RECLAIMS_DETAILS_CLOSED = 0  
                            AND RCLDT.RECLAIMS_DETAILS_LAST_UPDATE >= @buenr_refDate
                        Then RCL.ID_RECLAIMS
                    Else
                        null
                End 
--                 Case 
-- 						When 
-- 							(
-- 								(
-- 									RCLDT.RECLAIMS_DETAILS_CLOSED <> 0
-- 									AND RCLDT.RECLAIMS_DETAILS_LAST_UPDATE < @buenr_refDate
-- 								)
-- 								OR  (
-- 									RCLDT.RECLAIMS_DETAILS_CLOSED = 0  
-- 									AND RCLDT.RECLAIMS_DETAILS_LAST_UPDATE >= @buenr_refDate
-- 								)
-- 							)
-- 						Then 
-- 							RCL.ID_RECLAIMS
-- 						else 
-- 							Null	
--                         end
            ) RECLAIMS_NB_CLOSED
            , count(
               RCL.ID_RECLAIMS
            ) RECLAIMS_NB
            , count(
                Case
                    -- cas non bloqué standard
                    When RCLDT.RECLAIMS_DETAILS_BLOCKED = 0 
                        Then null

                    -- cas blocqué standard
                    When RCLDT.RECLAIMS_DETAILS_CLOSED = 0 
                            And RCLDT.RECLAIMS_DETAILS_LAST_UPDATE < @buenr_refDate
                        Then RCL.ID_RECLAIMS 


                    -- cas blocqué car fermé dans le futur (retro-calcul)
                    When RCLDT.RECLAIMS_DETAILS_CLOSED <> 0
                            And RCLDT.RECLAIMS_DETAILS_LAST_UPDATE >= @buenr_refDate
                        Then RCL.ID_RECLAIMS 
                    Else 
                        Null
                    
                End
-- 					Case 
-- 						When 
-- 							RCLDT.RECLAIMS_DETAILS_BLOCKED <> 0 
-- 							And (
-- 								(
-- 									RCLDT.RECLAIMS_DETAILS_CLOSED = 0 
-- 									And RCLDT.RECLAIMS_DETAILS_LAST_UPDATE < @buenr_refDate
-- 								)
-- 								Or (
-- 									RCLDT.RECLAIMS_DETAILS_CLOSED <> 0
-- 									And RCLDT.RECLAIMS_DETAILS_LAST_UPDATE >= @buenr_refDate
-- 								)
-- 							)
-- 
-- 						Then 
-- 							RCL.ID_RECLAIMS 
-- 						else null 
-- 					end
            ) RECLAIMS_NB_BLOCKED
        FROM
            RECLAIMS_DETAILS RCLDT
			Inner Join RECLAIMS RCL
				On RCLDT.RECLAIMS_ID_RECLAIMS = RCL.ID_RECLAIMS
            Inner Join DF_RECLAIMS DFR
                On RCLDT.DF_RCLM_ID_DF_RCLM = DFR.ID_DF_RECLAIMS
            Inner Join DF_RECLAIMS_STATUS DFRST
                On RCLDT.DF_RCLM_STT_ID_DF_RCLM_STT = DFRST.ID_DF_RECLAIMS_STATUS
        WHERE 
			RCL.RECLAIMS_CREATED < @buenr_refDate 
			And RCLDT.RECLAIMS_DETAILS_IS_ACTIVE = 1 
			AND RCLDT.RECLAIMS_DETAILS_IS_TMP = 0
            And DFR.DF_RECLAIMS_TRASH = 0
            And DFRST.DF_RECLAIMS_STATUS_TRASH = 0

-- 			And RCLDT.ENTRIES_ID_ENTRIES In (
			And Exists (
				Select  EHA.ENTRIES_ID_ENTRIES
				From ENTRIES_has_ACCOUNTS EHA
				Where 
                    EHA.ACCOUNTS_ID_ACCOUNTS between @bufenr_accMinVal And @bufenr_accMaxVal
--                     EHA.ACCOUNTS_ID_ACCOUNTS like @accFilter
					And EHA.ENTRIES_ID_ENTRIES between @bufenr_entMinVal And @bufenr_entMaxVal
-- 					And EHA.ENTRIES_ID_ENTRIES Like @entFilter
					And RCLDT.ENTRIES_ID_ENTRIES = EHA.ENTRIES_ID_ENTRIES
                Limit 1
			)
        GROUP BY ENTRIES_ID_ENTRIES
        ;
--         Create Index ix_tmp_entries_nb_reclaims_view On tmp_entries_nb_reclaims_view(ENTRIES_ID_ENTRIES);
    end;
end $$
Delimiter ;