Delimiter $$
/**
 * Author:  armen
 * Created: 25 juin 2020
 */

Drop Procedure if Exists BUSINESS_COMPUTE_PUBLICVIEWS_ENTRIES_LASTSMRS $$
Create Procedure BUSINESS_COMPUTE_PUBLICVIEWS_ENTRIES_LASTSMRS ()
Begin
    bcels_main : Begin
        bcels_init : Begin

            -- si besoin utiliser la table MISCELLANEOUS_PARAMS pour utiliser un autre langue
            set @bcels_smr_labal_lng = 1 ;

            bcels_prpe_table: Begin
    
                Drop Table If Exists TMP_ENTRIES_LAST_SMR;
                Create Table TMP_ENTRIES_LAST_SMR (
                        SUMMARIES_ID_SUMMARIES Int Unsigned Not Null
                        , DF_SUMMARIES_ID_DF_SUMMARIES Int Unsigned Null
                        , ENTRIES_ID_ENTRIES Int Unsigned Not Null
--                         , TMP_ENTRIES_LAST_SMR_TYPE Varchar(12) Not Null
                        , TMP_ENTRIES_LAST_SMR_TYPE Smallint Null
--                         , Index (SUMMARIES_ID_SUMMARIES, TMP_ENTRIES_LAST_SMR_TYPE)
--                         , Index (DF_SUMMARIES_ID_DF_SUMMARIES, TMP_ENTRIES_LAST_SMR_TYPE)
--                         , Index (SUMMARIES_ID_SUMMARIES)
                        , Index (DF_SUMMARIES_ID_DF_SUMMARIES)
                        , Index (TMP_ENTRIES_LAST_SMR_TYPE)
                        , Index (ENTRIES_ID_ENTRIES)
                    )
                ;

                Truncate Table TMP_V_PUBLIC_ENTRIES_LASTSMRS_VIEW;

            end;
            
            bcels_prpe_custm_rules: Begin



                -- Obtention des status de lettrage ouvert
                Set @bcels_OpenMtchStatsIds = (
                            Select Group_concat(ID_DF_MATCHINGCODE_STATE Separator ', ')
                            From DF_MATCHINGCODE_STATE
                            Where 
                                DF_MATCHINGCODE_STATE_CLOSED = 0
                        )
                    ;
                -- Obtention du status de lettrage fermé
                Set @bcels_ClosedMtchStatsId = (
                            Select ID_DF_MATCHINGCODE_STATE
                            From DF_MATCHINGCODE_STATE
                            Where 
                                DF_MATCHINGCODE_STATE_CLOSED = 1
                            Limit 1
                        )
                    ;

                Set @bcels_commonEntriesFilter = Concat(' (ent1.DF_MATCH_STAT_ID_DF_MATCH_STAT In ( ', @bcels_OpenMtchStatsIds, ') ) ' );
-- 
                -- obtention des paramètres
                Set @bcels_miscParamTextVal = Coalesce(
                                (
                                Select MISCELLANEOUS_PARAMS_TEXT_VAL1
                                from MISCELLANEOUS_PARAMS
                                where MISCELLANEOUS_PARAMS_KEY = 'PV.ENTRIES_LASTSMRS'
                                Limit 1
                            )
                            , ''
                        )
                    ;

                Set @bcels_closedMatchStatMaxDelay = extractValue(@bcels_miscParamTextVal,  '/MiscParamsRoot/filters/MATCHING_CODE_STATE/CLOSED/max_delay') ;
                Set @bcels_closedMatchStatMaxDelay = Case When Coalesce(CASH_ISINTEGER(@bcels_closedMatchStatMaxDelay), 0)=0 then 0 else Cast(@bcels_closedMatchStatMaxDelay as SIGNED) End;

                Drop Temporary Table if exists TMP_ENTRIES_LAST_SMR_FILTERED_ENTRES;
                Create Temporary Table TMP_ENTRIES_LAST_SMR_FILTERED_ENTRES (
                            ENTRIES_ID_ENTRIES Int Unsigned Not Null 
                            , Index(ENTRIES_ID_ENTRIES)
                        )
                    ;
-- --                 Select @bcels_closedMatchStatMaxDelay;
                if @bcels_closedMatchStatMaxDelay > 1 then
                    -- outre les écriture ouvertes, 
                    -- on dois également inclure les écritures clos depuis moins de @bcels_closedMatchStatMaxDelay jours
                    
                    -- ajout des écritures ouvertes
                    Set @bcels_query_OpenEntres = Concat(''
                                , ' Insert Into TMP_ENTRIES_LAST_SMR_FILTERED_ENTRES(ENTRIES_ID_ENTRIES) '
                                , ' Select ID_ENTRIES '
                                , ' From ENTRIES '
                                , ' Where (DF_MATCH_STAT_ID_DF_MATCH_STAT In ( ', @bcels_OpenMtchStatsIds, ') ) '
                            )
                        ;
                    PREPARE bcels_openEntriesSelec FROM @bcels_query_OpenEntres; 
                    execute bcels_openEntriesSelec ;
                    Deallocate prepare bcels_openEntriesSelec; 

                    Set @bcels_max_date = Date_Sub(AMO_BAL_REFDATE(), Interval @bcels_closedMatchStatMaxDelay DAY);
                    -- ajout dans la table, les écrtitures dont l'age de lettrage est inférieur ou égale au parametrage
                    -- ci dessus requete unique avec letf join, moins performante que 2 requetes
                     -- explain
--                     Set @bcels_query_lastCloseEntres = Concat(''
-- --                                 , ' Insert Into TMP_ENTRIES_LAST_SMR_FILTERED_ENTRES(ENTRIES_ID_ENTRIES) '
--                                 , ' Select '
--                                     , ' ent.ID_ENTRIES '
--                                 , ' From '
--                                     , ' ENTRIES ent '
--                                     , ' Left Join ( '
--                                         , ' ENTRIES_MATCHCOD em '
--                                         , ' Inner Join MATCHINGCODES mc '
--                                             , ' On em.MATCHCOD_ID_MATCHCOD = mc.ID_MATCHINGCODES '
--                                             , ' And em.ENTRIES_MATCHCOD_CURRENT = 1 '
--                                             , ' And mc.MATCHINGCODES_DATE >= ? ' -- param 1 @bcels_max_date
--                                     , ' )   On ent.ID_ENTRIES = em.ENTRIES_ID_ENTRIES '
-- 
--                                     , ' left Join ENTRIES_COMPUTED_DATA ecd '
--                                         , ' On ent.ID_ENTRIES = ecd.ENTRIES_ID_ENTRIES '
--                                         , ' And ecd.ENTRIES_COMPUTED_DATA_KEY = ''FORCEDMATCHSTS'' '
--                                         , ' And ecd.ENTRIES_COMPUTED_DATA_DATE >= ? ' -- param 2 @bcels_max_date
--                                 , ' Where '
--                                     , ' ent.DF_MATCH_STAT_ID_DF_MATCH_STAT = ? ' -- param 3 @bcels_ClosedMtchStatsId
--                                     , ' And ( '
--                                         , ' em.ENTRIES_ID_ENTRIES Is Not Null '
--                                         , ' Or ecd.ENTRIES_ID_ENTRIES Is Not Null '
--                                     , ' ) '
--                             )
--                         ;
--                     PREPARE bcels_lastCloseEntres FROM @bcels_query_lastCloseEntres ; 
--                     execute bcels_lastCloseEntres 
--                             Using
--                                 @bcels_max_date, @bcels_max_date
--                                 , @bcels_ClosedMtchStatsId 
--                         ;
--                     Deallocate prepare bcels_lastCloseEntres; 


                        
                        -- 1 ere requete lettrage forcé
                        -- explain
                        Set @bcels_query_lastForceCloseEntrs = Concat(''
                                , ' Insert Into TMP_ENTRIES_LAST_SMR_FILTERED_ENTRES(ENTRIES_ID_ENTRIES) '
                                    , ' Select '
                                        , ' ent.ID_ENTRIES '
                                    , ' From '
                                        , ' ENTRIES ent '
                                        , ' Inner Join ENTRIES_COMPUTED_DATA ecd '
                                            , ' On ent.ID_ENTRIES = ecd.ENTRIES_ID_ENTRIES '
                                            , ' And ecd.ENTRIES_COMPUTED_DATA_KEY = ''FORCEDMATCHSTS'' '
                                    , ' Where '
                                        , ' ent.DF_MATCH_STAT_ID_DF_MATCH_STAT = ? ' -- 1 @bcels_ClosedMtchStatsId
                                        , ' And ecd.ENTRIES_COMPUTED_DATA_EXTID = ? ' -- 2 @bcels_ClosedMtchStatsId
                                        , ' And ecd.ENTRIES_COMPUTED_DATA_DATE >= ? ' -- 3 @bcels_max_date
                                )
                            ;
                        PREPARE bcels_lastForcedCloseEntrs FROM @bcels_query_lastForceCloseEntrs ; 
                        execute bcels_lastForcedCloseEntrs 
                            Using
                                @bcels_ClosedMtchStatsId , @bcels_ClosedMtchStatsId , @bcels_max_date
                        ;

                        Deallocate prepare bcels_lastForcedCloseEntrs; 

                        -- 2 eme requête lettré total, sans lettrage force
                        Set @bcels_query_lastCloseEntrs = Concat(''
                                , ' Insert Into TMP_ENTRIES_LAST_SMR_FILTERED_ENTRES(ENTRIES_ID_ENTRIES) '
                                    , ' Select '
                                        , ' ent.ID_ENTRIES '
                                    , ' From '
                                        , ' ENTRIES ent '
                                        , ' Inner Join ENTRIES_MATCHCOD em '
                                           , ' On ent.ID_ENTRIES = em.ENTRIES_ID_ENTRIES '
                                        , ' Inner Join MATCHINGCODES mc '
                                            , 'On em.MATCHCOD_ID_MATCHCOD = mc.ID_MATCHINGCODES '
                                        , ' Left Join ENTRIES_COMPUTED_DATA ecd '
                                            , ' On ent.ID_ENTRIES = ecd.ENTRIES_ID_ENTRIES '
                                            , ' And ecd.ENTRIES_COMPUTED_DATA_KEY = ''FORCEDMATCHSTS'' '
                                    , ' Where '
                                        , ' ent.DF_MATCH_STAT_ID_DF_MATCH_STAT = ? ' -- 1 @bcels_ClosedMtchStatsId
                                        , ' And em.ENTRIES_MATCHCOD_CURRENT = 1 '
                                        , ' And mc.MATCHINGCODES_DATE >= ? ' -- 2 @bcels_max_date
                                        , ' And ecd.ENTRIES_ID_ENTRIES Is Null '
                                )
                            ;

                        PREPARE bcels_lastCloseEntrs FROM @bcels_query_lastCloseEntrs ; 
                        execute bcels_lastCloseEntrs 
                            Using
                                @bcels_ClosedMtchStatsId, @bcels_max_date
                        ;

                        Deallocate prepare bcels_lastCloseEntrs; 

                    -- modification du filtre
                    Set @bcels_commonEntriesFilter = Concat(' ( '
                                                                , ' ent1.ID_ENTRIES In ( '
                                                                    , ' Select entsel.ENTRIES_ID_ENTRIES '
                                                                    , ' From TMP_ENTRIES_LAST_SMR_FILTERED_ENTRES entsel '
                                                                    , ' Where '
                                                                        , '  entsel.ENTRIES_ID_ENTRIES = ent1.ID_ENTRIES'
                                                                , ' ) '
                                                            , ' ) ' 
                                                        )
                                                    ;
                        -- ci dessous; bisarrement moins permf
--                     Set @bcels_commonEntriesFilter = Concat(' ( '
--                                                                 , ' Exists ( '
--                                                                     , ' Select entsel.ENTRIES_ID_ENTRIES '
--                                                                     , ' From TMP_ENTRIES_LAST_SMR_FILTERED_ENTRES entsel '
--                                                                     , ' Where '
--                                                                         , '  entsel.ENTRIES_ID_ENTRIES = ent1.ID_ENTRIES'
--                                                                     , ' Limit 1 '
--                                                                 , ' ) '
--                                                             , ' ) ' 
--                                                         )
--                                                     ;
                    

                    
               

                End if;

            End ;
        End ;
        bcels_trt: 
        Begin

            Start Transaction;
            bcels_custm_smrs: Begin
                Set @bcels_query = Concat(''
                            , ' Insert Into TMP_ENTRIES_LAST_SMR( '
                                    , ' SUMMARIES_ID_SUMMARIES, DF_SUMMARIES_ID_DF_SUMMARIES '
                                    , ' , ENTRIES_ID_ENTRIES, TMP_ENTRIES_LAST_SMR_TYPE '
                                , ' ) '
                            , ' Select '
                                , ' Max(smr1.ID_SUMMARIES), dfs1.ID_DF_SUMMARIES '
--                                 , ' , ent1.ID_ENTRIES, Max(dfs1.DF_SUMMARIES_CODE) '
--                                 , ' , ent1.ID_ENTRIES, Max(Substring(dfs1.DF_SUMMARIES_CODE, -2)) '
                                , ' , ent1.ID_ENTRIES, null '
                            , ' From SUMMARIES smr1 '
                                , ' Inner join DF_SUMMARIES dfs1 '
                                    , ' On smr1.DF_SUMMARIES_ID_DF_SUMMR = dfs1.ID_DF_SUMMARIES '
                                , ' Inner Join ENTRIES ent1 '
                                    , ' On smr1.ENTRIES_ID_ENTRIES = ent1.ID_ENTRIES '
--                                 , ' Inner Join DF_MATCHINGCODE_STATE dms1 '
--                                     , ' On ent1.DF_MATCH_STAT_ID_DF_MATCH_STAT = dms1.ID_DF_MATCHINGCODE_STATE '
                            , ' where '
                                , ' dfs1.DF_SUMMARIES_CODE Like ''CUSTOMENTR__'' '
--                                 , ' And dms1.DF_MATCHINGCODE_STATE_CLOSED = 0 '
--                                 , ' And ent1.DF_MATCH_STAT_ID_DF_MATCH_STAT In ( ', @bcels_OpenMtchStatsIds, ') '
                                   , ' And ( '
                                        , @bcels_commonEntriesFilter
                                   , ' ) '
                            , ' group By ent1.ID_ENTRIES, dfs1.ID_DF_SUMMARIES '
                        )
                    ;

                PREPARE bcels_insert1stmt FROM @bcels_query; 
                execute bcels_insert1stmt ;
                Deallocate prepare bcels_insert1stmt; 
                
            End ;

            bcels_mbr_batch_smrs: Begin
                Set @bcels_query = Concat(''
                            , ' Insert Into TMP_ENTRIES_LAST_SMR( '
                                    , ' SUMMARIES_ID_SUMMARIES, DF_SUMMARIES_ID_DF_SUMMARIES '
                                    , ' , ENTRIES_ID_ENTRIES, TMP_ENTRIES_LAST_SMR_TYPE '
                                , ' ) '
                            , ' Select '
                                , ' Max(smr1.ID_SUMMARIES), NULL '
--                                 , ' , ent1.ID_ENTRIES, ''BATCH'' '
                                , ' , ent1.ID_ENTRIES, 100 '
                            , ' From SUMMARIES smr1 '
                                , ' Inner Join ENTRIES ent1 '
                                    , ' On smr1.ENTRIES_ID_ENTRIES = ent1.ID_ENTRIES '
--                                 , ' Inner Join DF_MATCHINGCODE_STATE dms1 '
--                                     , ' On ent1.DF_MATCH_STAT_ID_DF_MATCH_STAT = dms1.ID_DF_MATCHINGCODE_STATE '
                            , ' where '
                                , ' smr1.SUMMARIES_MEMBER_AUTHOR = 1 '
--                                 , ' And dms1.DF_MATCHINGCODE_STATE_CLOSED = 0 '
--                                 , ' And ent1.DF_MATCH_STAT_ID_DF_MATCH_STAT In ( ', @bcels_OpenMtchStatsIds, ') '
                                , ' And ( '
                                        , @bcels_commonEntriesFilter
                                   , ' ) '
                            , ' group By ent1.ID_ENTRIES '
                        )
                    ;

                PREPARE bcels_insert2stmt FROM @bcels_query; 
                execute bcels_insert2stmt ;
                Deallocate prepare bcels_insert2stmt; 
            End ;

            bcels_other_mbr_smrs: Begin
                Set @bcels_query = Concat(''
                            , ' Insert Into TMP_ENTRIES_LAST_SMR( '
                                    , ' SUMMARIES_ID_SUMMARIES, DF_SUMMARIES_ID_DF_SUMMARIES '
                                    , ' , ENTRIES_ID_ENTRIES, TMP_ENTRIES_LAST_SMR_TYPE '
                                , ' ) '
                            , ' Select '
                                , ' Max(smr1.ID_SUMMARIES), NULL '
--                                 , ' , ent1.ID_ENTRIES, ''USER'' '
                                , ' , ent1.ID_ENTRIES, 200 '
                            , ' From SUMMARIES smr1 '
                                , ' Inner Join ENTRIES ent1 '
                                    , ' On smr1.ENTRIES_ID_ENTRIES = ent1.ID_ENTRIES '
--                                 , ' Inner Join DF_MATCHINGCODE_STATE dms1 '
--                                     , ' On ent1.DF_MATCH_STAT_ID_DF_MATCH_STAT = dms1.ID_DF_MATCHINGCODE_STATE '
                            , ' where '
                                , ' smr1.SUMMARIES_MEMBER_AUTHOR <> 1 '
--                                 , ' And dms1.DF_MATCHINGCODE_STATE_CLOSED = 0 '
--                                 , ' And ent1.DF_MATCH_STAT_ID_DF_MATCH_STAT In ( ', @bcels_OpenMtchStatsIds, ') '
                                , ' And ( '
                                        , @bcels_commonEntriesFilter
                                   , ' ) '
                            , ' group By ent1.ID_ENTRIES '
                        )
                    ;

                            -- select * from TMP_ENTRIES_LAST_SMR where ENTRIES_ID_ENTRIES in (14685)

                PREPARE bcels_insert3stmt FROM @bcels_query; 
                execute bcels_insert3stmt ;
                Deallocate prepare bcels_insert3stmt; 
            End ;

            bcels_upd_lst_smrs: Begin
--                 Update TMP_ENTRIES_LAST_SMR tar 
--                     Inner Join SUMMARIES src 
--                          On tar.SUMMARIES_ID_SUMMARIES = src.ID_SUMMARIES 
--                  Set 
--                      tar.DF_SUMMARIES_ID_DF_SUMMARIES = src.DF_SUMMARIES_ID_DF_SUMMR 
--                  where 
--                      tar.DF_SUMMARIES_ID_DF_SUMMARIES Is Null 
--                 ;
--             -- ci dessous si besoin de le rendre dynamique
--             --     Set @bcels_query = Concat(''
--             --                 , ' Update TMP_ENTRIES_LAST_SMR tar '
--             --                     , ' Inner Join SUMMARIES src '
--             --                         , ' On tar.SUMMARIES_ID_SUMMARIES = src.ID_SUMMARIES '
--             --                 , ' Set '
--             --                     , ' tar.DF_SUMMARIES_ID_DF_SUMMARIES = src.DF_SUMMARIES_ID_DF_SUMMR '
--             --                 , ' where '
--             --                     , ' tar.DF_SUMMARIES_ID_DF_SUMMARIES Is Null '
--             --             )
--             --         ;
-- 
-- 
--                 Update TMP_ENTRIES_LAST_SMR tar
--                     Inner Join DF_SUMMARIES dfs
--                         On tar.DF_SUMMARIES_ID_DF_SUMMARIES = dfs.ID_DF_SUMMARIES
--                 Set
--                     tar.TMP_ENTRIES_LAST_SMR_TYPE = Substring(dfs.DF_SUMMARIES_CODE, -2)
--                 Where
--                     tar.TMP_ENTRIES_LAST_SMR_TYPE Is null
--                     And dfs.DF_SUMMARIES_CODE Like 'CUSTOMENTR__'
--                 ;

                Update TMP_ENTRIES_LAST_SMR tar
                    Inner Join SUMMARIES src 
                         On tar.SUMMARIES_ID_SUMMARIES = src.ID_SUMMARIES 
                    Left Join DF_SUMMARIES dfs
                        On tar.DF_SUMMARIES_ID_DF_SUMMARIES = dfs.ID_DF_SUMMARIES
                Set
                    tar.DF_SUMMARIES_ID_DF_SUMMARIES = Coalesce(tar.DF_SUMMARIES_ID_DF_SUMMARIES, src.DF_SUMMARIES_ID_DF_SUMMR)
                    , tar.TMP_ENTRIES_LAST_SMR_TYPE = Coalesce(tar.TMP_ENTRIES_LAST_SMR_TYPE
                                                                , Case When dfs.DF_SUMMARIES_CODE Like 'CUSTOMENTR__' 
                                                                        Then Substring(dfs.DF_SUMMARIES_CODE, -2) 
                                                                        else 0 
                                                                    End
                                                            )
                Where
                    tar.TMP_ENTRIES_LAST_SMR_TYPE Is null 
                    Or tar.DF_SUMMARIES_ID_DF_SUMMARIES Is Null 
                ;

            End ;

            bcels_copy_data: Begin
                bcels_insert_entries: Begin
    --                 Set @bcels_query = Concat(''
    --                             , ' Insert Into TMP_V_PUBLIC_ENTRIES_LASTSMRS_VIEW ( '
    --                                 , ' ENTITIES_ID_ENTITIES, ID_ACCOUNTS '
    --                                 , ' , ID_ENTRIES, ENTRIES_REF, ENTRIES_DATE, ENTRIES_DUE_DATE '
    --                                 , ' , ENTRIES_ENT_BALANCE, ENTRIES_ENT_DEBIT, ENTRIES_ENT_CREDIT '
    -- 
    --                                 , ' , LAST_CSTMTENTR01_SMRS_AUTHOR '
    --                                 , ' , LAST_CSTMTENTR01_SMRS_DATE '
    --                                 , ' , LAST_CSTMTENTR01_SMRS_SUBJECT '
    --                                 , ' , LAST_CSTMTENTR01_SMRS_COMMENTS '
    -- 
    --                                 , ' , LAST_CSTMTENTR02_SMRS_AUTHOR '
    --                                 , ' , LAST_CSTMTENTR02_SMRS_DATE '
    --                                 , ' , LAST_CSTMTENTR02_SMRS_SUBJECT '
    --                                 , ' , LAST_CSTMTENTR02_SMRS_COMMENTS '
    -- 
    --                                 , ' , LAST_CSTMTENTR03_SMRS_AUTHOR '
    --                                 , ' , LAST_CSTMTENTR03_SMRS_DATE '
    --                                 , ' , LAST_CSTMTENTR03_SMRS_SUBJECT '
    --                                 , ' , LAST_CSTMTENTR03_SMRS_COMMENTS '
    -- 
    --                                 , ' , LAST_CSTMTENTR04_SMRS_AUTHOR '
    --                                 , ' , LAST_CSTMTENTR04_SMRS_DATE '
    --                                 , ' , LAST_CSTMTENTR04_SMRS_SUBJECT '
    --                                 , ' , LAST_CSTMTENTR04_SMRS_COMMENTS '
    -- 
    --                                 , ' , LAST_CSTMTENTR05_SMRS_AUTHOR '
    --                                 , ' , LAST_CSTMTENTR05_SMRS_DATE '
    --                                 , ' , LAST_CSTMTENTR05_SMRS_SUBJECT '
    --                                 , ' , LAST_CSTMTENTR05_SMRS_COMMENTS '
    -- 
    --                                 , ' , LAST_USER_SMRS_ID_DF_SUMMARIES '
    --                                 , ' , LAST_USER_SMRS_CODE '
    --                                 , ' , LAST_USER_SMRS_LABEL '
    --                                 , ' , LAST_USR_SMRS_AUTHOR '
    --                                 , ' , LAST_USR_SMRS_DATE '
    --                                 , ' , LAST_USR_SMRS_SUBJECT '
    --                                 , ' , LAST_USR_SMRS_COMMENTS '
    -- 
    --                                 , ' , LAST_BATCH_SMRS_ID_DF_SUMMARIES '
    --                                 , ' , LAST_BATCH_SMRS_CODE '
    --                                 , ' , LAST_BATCH_SMRS_LABEL '
    --                                 , ' , LAST_BATCH_SMRS_AUTHOR '
    --                                 , ' , LAST_BATCH_SMRS_DATE '
    --                                 , ' , LAST_BATCH_SMRS_SUBJECT '
    --                                 , ' , LAST_BATCH_SMRS_COMMENTS '
    --                             , ' ) '
    --                             , ' Select  '
    --                                 , ' acc.ENTITIES_ID_ENTITIES, acc.ID_ACCOUNTS '
    --                                 , ' , ent1.ID_ENTRIES, ent1.ENTRIES_REF, ent1.ENTRIES_DATE, ent1.ENTRIES_DUE_DATE '
    --                                 , ' , ent1.ENTRIES_ENT_BALANCE, ent1.ENTRIES_ENT_DEBIT, ent1.ENTRIES_ENT_CREDIT '
    -- 
    --                                 , ' , smrs_1.SUMMARIES_MEMBER_AUTHOR LAST_CSTMTENTR01_SMRS_AUTHOR '
    --                                 , ' , smrs_1.SUMMARIES_DATE LAST_CSTMTENTR01_SMRS_DATE '
    --                                 , ' , smrs_1.SUMMARIES_SUBJECT LAST_CSTMTENTR01_SMRS_SUBJECT '
    --                                 , ' , smrs_1.SUMMARIES_COMMENTS LAST_CSTMTENTR01_SMRS_COMMENTS '
    -- 
    --                                 , ' , smrs_2.SUMMARIES_MEMBER_AUTHOR LAST_CSTMTENTR02_SMRS_AUTHOR '
    --                                 , ' , smrs_2.SUMMARIES_DATE LAST_CSTMTENTR02_SMRS_DATE '
    --                                 , ' , smrs_2.SUMMARIES_SUBJECT LAST_CSTMTENTR02_SMRS_SUBJECT '
    --                                 , ' , smrs_2.SUMMARIES_COMMENTS LAST_CSTMTENTR02_SMRS_COMMENTS '
    -- 
    --                                 , ' , smrs_3.SUMMARIES_MEMBER_AUTHOR LAST_CSTMTENTR03_SMRS_AUTHOR '
    --                                 , ' , smrs_3.SUMMARIES_DATE LAST_CSTMTENTR03_SMRS_DATE '
    --                                 , ' , smrs_3.SUMMARIES_SUBJECT LAST_CSTMTENTR03_SMRS_SUBJECT '
    --                                 , ' , smrs_3.SUMMARIES_COMMENTS LAST_CSTMTENTR03_SMRS_COMMENTS '
    -- 
    --                                 , ' , smrs_4.SUMMARIES_MEMBER_AUTHOR LAST_CSTMTENTR04_SMRS_AUTHOR '
    --                                 , ' , smrs_4.SUMMARIES_DATE LAST_CSTMTENTR04_SMRS_DATE '
    --                                 , ' , smrs_4.SUMMARIES_SUBJECT LAST_CSTMTENTR04_SMRS_SUBJECT '
    --                                 , ' , smrs_4.SUMMARIES_COMMENTS LAST_CSTMTENTR04_SMRS_COMMENTS '
    -- 
    --                                 , ' , smrs_5.SUMMARIES_MEMBER_AUTHOR LAST_CSTMTENTR05_SMRS_AUTHOR '
    --                                 , ' , smrs_5.SUMMARIES_DATE LAST_CSTMTENTR05_SMRS_DATE '
    --                                 , ' , smrs_5.SUMMARIES_SUBJECT LAST_CSTMTENTR05_SMRS_SUBJECT '
    --                                 , ' , smrs_5.SUMMARIES_COMMENTS LAST_CSTMTENTR05_SMRS_COMMENTS '
    -- 
    --                                 , ' , dfsmrlst_user.ID_DF_SUMMARIES LAST_USER_SMRS_ID_DF_SUMMARIES '
    --                                 , ' , dfsmrlst_user.DF_SUMMARIES_CODE LAST_USER_SMRS_CODE '
    --                                 , ' , dfsmrlstlng_user.DF_SUMMARIES_LNG_LABEL LAST_USER_SMRS_LABEL '
    --                                 , ' , smrs_user.SUMMARIES_MEMBER_AUTHOR LAST_USR_SMRS_AUTHOR '
    --                                 , ' , smrs_user.SUMMARIES_DATE LAST_USR_SMRS_DATE '
    --                                 , ' , smrs_user.SUMMARIES_SUBJECT LAST_USR_SMRS_SUBJECT '
    --                                 , ' , smrs_user.SUMMARIES_COMMENTS LAST_USR_SMRS_COMMENTS '
    -- 
    --                                 , ' , dfsmrlst_batch.ID_DF_SUMMARIES LAST_BATCH_SMRS_ID_DF_SUMMARIES '
    --                                 , ' , dfsmrlst_batch.DF_SUMMARIES_CODE LAST_BATCH_SMRS_CODE '
    --                                 , ' , dfsmrlstlng_batch.DF_SUMMARIES_LNG_LABEL LAST_BATCH_SMRS_LABEL '
    --                                 , ' , smrs_batch.SUMMARIES_MEMBER_AUTHOR LAST_BATCH_SMRS_AUTHOR '
    --                                 , ' , smrs_batch.SUMMARIES_DATE LAST_BATCH_SMRS_DATE '
    --                                 , ' , smrs_batch.SUMMARIES_SUBJECT LAST_BATCH_SMRS_SUBJECT '
    --                                 , ' , smrs_batch.SUMMARIES_COMMENTS LAST_BATCH_SMRS_COMMENTS '
    --                             , ' from ENTRIES ent1 '
    --                                 , ' Inner Join ENTRIES_has_ACCOUNTS eha '
    --                                     , ' On ent1.ID_ENTRIES = eha.ENTRIES_ID_ENTRIES '
    --                                 , ' Inner join ACCOUNTS acc '
    --                                     , ' On eha.ACCOUNTS_ID_ACCOUNTS = acc.ID_ACCOUNTS '
    --                                     , ' And eha.ENTRIES_PERMANENT_ATTACHEMENT = 1 '
    -- --                                 , ' Inner Join DF_MATCHINGCODE_STATE dms '
    -- --                                     , ' On ent1.DF_MATCH_STAT_ID_DF_MATCH_STAT = dms.ID_DF_MATCHINGCODE_STATE '
    --                                 , ' Left Join ( '
    --                                     , ' TMP_ENTRIES_LAST_SMR smrlst_1 '
    --                                     , ' Inner Join SUMMARIES smrs_1 '
    --                                         , ' On smrlst_1.SUMMARIES_ID_SUMMARIES = smrs_1.ID_SUMMARIES '
    --                                     , ' Inner Join DF_SUMMARIES dms_1 '
    --                                         , ' On smrlst_1.DF_SUMMARIES_ID_DF_SUMMARIES =dms_1.ID_DF_SUMMARIES '
    -- --                                         , ' And smrlst_1.TMP_ENTRIES_LAST_SMR_TYPE = ''CUSTOMENTR01'' '
    --                                         , ' And smrlst_1.TMP_ENTRIES_LAST_SMR_TYPE = 1 '
    --                                 , ' ) On ent1.ID_ENTRIES = smrs_1.ENTRIES_ID_ENTRIES '
    --                                 , ' Left Join ( '
    --                                     , ' TMP_ENTRIES_LAST_SMR smrlst_2 '
    --                                     , ' Inner Join SUMMARIES smrs_2 '
    --                                         , ' On smrlst_2.SUMMARIES_ID_SUMMARIES = smrs_2.ID_SUMMARIES '
    -- --                                         , ' And smrlst_2.TMP_ENTRIES_LAST_SMR_TYPE = ''CUSTOMENTR02'' '
    --                                         , ' And smrlst_2.TMP_ENTRIES_LAST_SMR_TYPE = 2 '
    --                                 , ' ) On ent1.ID_ENTRIES = smrs_2.ENTRIES_ID_ENTRIES '
    --                                 , ' Left Join ( '
    --                                     , ' TMP_ENTRIES_LAST_SMR smrlst_3 '
    --                                     , ' Inner Join SUMMARIES smrs_3 '
    --                                         , ' On smrlst_3.SUMMARIES_ID_SUMMARIES = smrs_3.ID_SUMMARIES '
    -- --                                         , ' And smrlst_3.TMP_ENTRIES_LAST_SMR_TYPE = ''CUSTOMENTR03'' '
    --                                         , ' And smrlst_3.TMP_ENTRIES_LAST_SMR_TYPE = 3 '
    --                                 , ' ) On ent1.ID_ENTRIES = smrs_3.ENTRIES_ID_ENTRIES '
    --                                 , ' Left Join ( '
    --                                     , ' TMP_ENTRIES_LAST_SMR smrlst_4 '
    --                                     , ' Inner Join SUMMARIES smrs_4 '
    --                                         , ' On smrlst_4.SUMMARIES_ID_SUMMARIES = smrs_4.ID_SUMMARIES '
    -- --                                         , ' And smrlst_4.TMP_ENTRIES_LAST_SMR_TYPE = ''CUSTOMENTR04'' '
    --                                         , ' And smrlst_4.TMP_ENTRIES_LAST_SMR_TYPE = 4 '
    --                                 , ' ) On ent1.ID_ENTRIES = smrs_4.ENTRIES_ID_ENTRIES '
    --                                 , ' Left Join ( '
    --                                     , ' TMP_ENTRIES_LAST_SMR smrlst_5 '
    --                                     , ' Inner Join SUMMARIES smrs_5 '
    --                                         , ' On smrlst_5.SUMMARIES_ID_SUMMARIES = smrs_5.ID_SUMMARIES '
    -- --                                         , ' And smrlst_5.TMP_ENTRIES_LAST_SMR_TYPE = ''CUSTOMENTR05'' '
    --                                         , ' And smrlst_5.TMP_ENTRIES_LAST_SMR_TYPE = 5 '
    --                                 , ' ) On ent1.ID_ENTRIES = smrs_5.ENTRIES_ID_ENTRIES '
    --                                 , ' Left Join ( '
    --                                     , ' TMP_ENTRIES_LAST_SMR smrlst_user '
    --                                     , ' Inner Join SUMMARIES smrs_user '
    --                                         , ' On smrlst_user.SUMMARIES_ID_SUMMARIES = smrs_user.ID_SUMMARIES '
    -- --                                         , ' And smrlst_user.TMP_ENTRIES_LAST_SMR_TYPE = ''USER'' '
    --                                         , ' And smrlst_user.TMP_ENTRIES_LAST_SMR_TYPE = 200 '
    --                                     , ' Inner Join DF_SUMMARIES dfsmrlst_user '
    --                                         , ' On smrs_user.DF_SUMMARIES_ID_DF_SUMMR = dfsmrlst_user.ID_DF_SUMMARIES '
    --                                     , ' Left Join DF_SUMMARIES_LNG dfsmrlstlng_user '
    --                                         , ' On dfsmrlst_user.ID_DF_SUMMARIES = dfsmrlstlng_user.DF_SUMMARIES_ID_DF_SUMMARIES '
    --                                         , ' And dfsmrlstlng_user.DF_LANG_ID_DEF_LANG = 1 '
    --                                 , ' ) On ent1.ID_ENTRIES = smrs_user.ENTRIES_ID_ENTRIES '
    --                                 , ' Left Join ( '
    --                                     , ' TMP_ENTRIES_LAST_SMR smrlst_batch '
    --                                     , ' Inner Join SUMMARIES smrs_batch '
    --                                         , ' On smrlst_batch.SUMMARIES_ID_SUMMARIES = smrs_batch.ID_SUMMARIES '
    -- --                                         , ' And smrlst_batch.TMP_ENTRIES_LAST_SMR_TYPE = ''BATCH'' '
    --                                         , ' And smrlst_batch.TMP_ENTRIES_LAST_SMR_TYPE = 100 '
    --                                     , ' Inner Join DF_SUMMARIES dfsmrlst_batch '
    --                                         , ' On smrs_batch.DF_SUMMARIES_ID_DF_SUMMR = dfsmrlst_batch.ID_DF_SUMMARIES '
    --                                     , ' Left Join DF_SUMMARIES_LNG dfsmrlstlng_batch '
    --                                         , ' On dfsmrlst_batch.ID_DF_SUMMARIES = dfsmrlstlng_batch.DF_SUMMARIES_ID_DF_SUMMARIES '
    --                                         , ' And dfsmrlstlng_batch.DF_LANG_ID_DEF_LANG = 1 '
    --                                 , ' ) On ent1.ID_ENTRIES = smrs_batch.ENTRIES_ID_ENTRIES '
    -- --                             , ' Where dms.DF_MATCHINGCODE_STATE_CLOSED = 0 '
    -- --                                 , ' Where ent1.DF_MATCH_STAT_ID_DF_MATCH_STAT In ( ', @bcels_OpenMtchStatsIds, ') '
    --                                 , ' Where ( '
    --                                         , @bcels_commonEntriesFilter
    --                                    , ' ) '
    --                         )
    --                     ;
    -- --         select @bcels_query; leave bcels_main;
    --                 PREPARE bcels_insert4stmt FROM @bcels_query; 
    --                 execute bcels_insert4stmt ;
    --                 Deallocate prepare bcels_insert4stmt; 




                    Set @bcels_query = Concat(''
                                , ' Insert Into TMP_V_PUBLIC_ENTRIES_LASTSMRS_VIEW ( '
                                    , ' ENTITIES_ID_ENTITIES, ID_ACCOUNTS '
                                    , ' , ID_ENTRIES, ENTRIES_REF, ENTRIES_DATE, ENTRIES_DUE_DATE '
                                    , ' , ENTRIES_ENT_BALANCE, ENTRIES_ENT_DEBIT, ENTRIES_ENT_CREDIT '
                                , ' ) '
                                , ' Select  '
                                    , ' acc.ENTITIES_ID_ENTITIES, acc.ID_ACCOUNTS '
                                    , ' , ent1.ID_ENTRIES, ent1.ENTRIES_REF, ent1.ENTRIES_DATE, ent1.ENTRIES_DUE_DATE '
                                    , ' , ent1.ENTRIES_ENT_BALANCE, ent1.ENTRIES_ENT_DEBIT, ent1.ENTRIES_ENT_CREDIT '

                                , ' from ENTRIES ent1 '
                                    , ' Inner Join ENTRIES_has_ACCOUNTS eha '
                                        , ' On ent1.ID_ENTRIES = eha.ENTRIES_ID_ENTRIES '
                                    , ' Inner join ACCOUNTS acc '
                                        , ' On eha.ACCOUNTS_ID_ACCOUNTS = acc.ID_ACCOUNTS '
                                        , ' And eha.ENTRIES_PERMANENT_ATTACHEMENT = 1 '
                                    , ' Where ( '
                                            , @bcels_commonEntriesFilter
                                       , ' ) '
                            )
                        ;
    -- --         select @bcels_query; leave bcels_main;
                    PREPARE bcels_insert4stmt FROM @bcels_query; 
                    execute bcels_insert4stmt ;
                    Deallocate prepare bcels_insert4stmt; 

                End ;

            Commit;

                bcels_append_summaries: Begin
                    -- custom 1
                    Update TMP_V_PUBLIC_ENTRIES_LASTSMRS_VIEW tar
                        Inner Join TMP_ENTRIES_LAST_SMR smrlst
                            On tar.ID_ENTRIES = smrlst.ENTRIES_ID_ENTRIES
                        Inner Join SUMMARIES smrs
                            On smrlst.SUMMARIES_ID_SUMMARIES = smrs.ID_SUMMARIES
                    Set
                        tar.LAST_CSTMTENTR01_SMRS_AUTHOR        = smrs.SUMMARIES_MEMBER_AUTHOR
                        , tar.LAST_CSTMTENTR01_SMRS_DATE        = smrs.SUMMARIES_DATE
                        , tar.LAST_CSTMTENTR01_SMRS_SUBJECT     = smrs.SUMMARIES_SUBJECT
                        , tar.LAST_CSTMTENTR01_SMRS_COMMENTS    = smrs.SUMMARIES_COMMENTS
                    Where 
                        smrlst.TMP_ENTRIES_LAST_SMR_TYPE = 1
                    ;

                    -- custom 2
                    Update TMP_V_PUBLIC_ENTRIES_LASTSMRS_VIEW tar
                        Inner Join TMP_ENTRIES_LAST_SMR smrlst
                            On tar.ID_ENTRIES = smrlst.ENTRIES_ID_ENTRIES
                        Inner Join SUMMARIES smrs
                            On smrlst.SUMMARIES_ID_SUMMARIES = smrs.ID_SUMMARIES
                    Set
                        tar.LAST_CSTMTENTR02_SMRS_AUTHOR        = smrs.SUMMARIES_MEMBER_AUTHOR
                        , tar.LAST_CSTMTENTR02_SMRS_DATE        = smrs.SUMMARIES_DATE
                        , tar.LAST_CSTMTENTR02_SMRS_SUBJECT     = smrs.SUMMARIES_SUBJECT
                        , tar.LAST_CSTMTENTR02_SMRS_COMMENTS    = smrs.SUMMARIES_COMMENTS
                    Where 
                        smrlst.TMP_ENTRIES_LAST_SMR_TYPE = 2
                    ;

                    -- custom 3
                    Update TMP_V_PUBLIC_ENTRIES_LASTSMRS_VIEW tar
                        Inner Join TMP_ENTRIES_LAST_SMR smrlst
                            On tar.ID_ENTRIES = smrlst.ENTRIES_ID_ENTRIES
                        Inner Join SUMMARIES smrs
                            On smrlst.SUMMARIES_ID_SUMMARIES = smrs.ID_SUMMARIES
                    Set
                        tar.LAST_CSTMTENTR03_SMRS_AUTHOR        = smrs.SUMMARIES_MEMBER_AUTHOR
                        , tar.LAST_CSTMTENTR03_SMRS_DATE        = smrs.SUMMARIES_DATE
                        , tar.LAST_CSTMTENTR03_SMRS_SUBJECT     = smrs.SUMMARIES_SUBJECT
                        , tar.LAST_CSTMTENTR03_SMRS_COMMENTS    = smrs.SUMMARIES_COMMENTS
                    Where 
                        smrlst.TMP_ENTRIES_LAST_SMR_TYPE = 3
                    ;

                    -- custom 4
                    Update TMP_V_PUBLIC_ENTRIES_LASTSMRS_VIEW tar
                        Inner Join TMP_ENTRIES_LAST_SMR smrlst
                            On tar.ID_ENTRIES = smrlst.ENTRIES_ID_ENTRIES
                        Inner Join SUMMARIES smrs
                            On smrlst.SUMMARIES_ID_SUMMARIES = smrs.ID_SUMMARIES
                    Set
                        tar.LAST_CSTMTENTR04_SMRS_AUTHOR        = smrs.SUMMARIES_MEMBER_AUTHOR
                        , tar.LAST_CSTMTENTR04_SMRS_DATE        = smrs.SUMMARIES_DATE
                        , tar.LAST_CSTMTENTR04_SMRS_SUBJECT     = smrs.SUMMARIES_SUBJECT
                        , tar.LAST_CSTMTENTR04_SMRS_COMMENTS    = smrs.SUMMARIES_COMMENTS
                    Where 
                        smrlst.TMP_ENTRIES_LAST_SMR_TYPE = 4
                    ;

                    -- custom 5
                    Update TMP_V_PUBLIC_ENTRIES_LASTSMRS_VIEW tar
                        Inner Join TMP_ENTRIES_LAST_SMR smrlst
                            On tar.ID_ENTRIES = smrlst.ENTRIES_ID_ENTRIES
                        Inner Join SUMMARIES smrs
                            On smrlst.SUMMARIES_ID_SUMMARIES = smrs.ID_SUMMARIES
                    Set
                        tar.LAST_CSTMTENTR05_SMRS_AUTHOR        = smrs.SUMMARIES_MEMBER_AUTHOR
                        , tar.LAST_CSTMTENTR05_SMRS_DATE        = smrs.SUMMARIES_DATE
                        , tar.LAST_CSTMTENTR05_SMRS_SUBJECT     = smrs.SUMMARIES_SUBJECT
                        , tar.LAST_CSTMTENTR05_SMRS_COMMENTS    = smrs.SUMMARIES_COMMENTS
                    Where 
                        smrlst.TMP_ENTRIES_LAST_SMR_TYPE = 5
                    ;

                    -- batch  (100)
                    -- explain
                    Update TMP_V_PUBLIC_ENTRIES_LASTSMRS_VIEW tar
                        Inner Join TMP_ENTRIES_LAST_SMR smrlst
                            On tar.ID_ENTRIES = smrlst.ENTRIES_ID_ENTRIES
                        Inner Join SUMMARIES smrs
                            On smrlst.SUMMARIES_ID_SUMMARIES = smrs.ID_SUMMARIES
                        Inner Join DF_SUMMARIES dfs
                            On smrs.DF_SUMMARIES_ID_DF_SUMMR = dfs.ID_DF_SUMMARIES
                        Left Join DF_SUMMARIES_LNG dfsl
                            On dfs.ID_DF_SUMMARIES = dfsl.DF_SUMMARIES_ID_DF_SUMMARIES 
                            and dfsl.DF_LANG_ID_DEF_LANG = @bcels_smr_labal_lng
                    Set
                        tar.LAST_BATCH_SMRS_AUTHOR              = smrs.SUMMARIES_MEMBER_AUTHOR
                        , tar.LAST_BATCH_SMRS_DATE              = smrs.SUMMARIES_DATE
                        , tar.LAST_BATCH_SMRS_SUBJECT           = smrs.SUMMARIES_SUBJECT
                        , tar.LAST_BATCH_SMRS_COMMENTS          = smrs.SUMMARIES_COMMENTS

                        , tar.LAST_BATCH_SMRS_ID_DF_SUMMARIES   = dfs.ID_DF_SUMMARIES
                        , tar.LAST_BATCH_SMRS_CODE              = dfs.DF_SUMMARIES_CODE
                        , tar.LAST_BATCH_SMRS_LABEL             = dfsl.DF_SUMMARIES_LNG_LABEL

                    Where 
                        smrlst.TMP_ENTRIES_LAST_SMR_TYPE = 100
                    ;

                    -- user  (200)
                    -- explain
                    Update TMP_V_PUBLIC_ENTRIES_LASTSMRS_VIEW tar
                        Inner Join TMP_ENTRIES_LAST_SMR smrlst
                            On tar.ID_ENTRIES = smrlst.ENTRIES_ID_ENTRIES
                        Inner Join SUMMARIES smrs
                            On smrlst.SUMMARIES_ID_SUMMARIES = smrs.ID_SUMMARIES
                        Inner Join DF_SUMMARIES dfs
                            On smrs.DF_SUMMARIES_ID_DF_SUMMR = dfs.ID_DF_SUMMARIES
                        Left Join DF_SUMMARIES_LNG dfsl
                            On dfs.ID_DF_SUMMARIES = dfsl.DF_SUMMARIES_ID_DF_SUMMARIES 
                            and dfsl.DF_LANG_ID_DEF_LANG = @bcels_smr_labal_lng
                    Set
                        tar.LAST_USR_SMRS_AUTHOR                = smrs.SUMMARIES_MEMBER_AUTHOR
                        , tar.LAST_USR_SMRS_DATE                = smrs.SUMMARIES_DATE
                        , tar.LAST_USR_SMRS_SUBJECT             = smrs.SUMMARIES_SUBJECT
                        , tar.LAST_USR_SMRS_COMMENTS            = smrs.SUMMARIES_COMMENTS

                        , tar.LAST_USER_SMRS_ID_DF_SUMMARIES    = dfs.ID_DF_SUMMARIES
                        , tar.LAST_USER_SMRS_CODE               = dfs.DF_SUMMARIES_CODE
                        , tar.LAST_USER_SMRS_LABEL              = dfsl.DF_SUMMARIES_LNG_LABEL


                    Where 
                        smrlst.TMP_ENTRIES_LAST_SMR_TYPE = 200
                    ;

                End ;
            End ;

        End ;
    End ;
End $$
Delimiter ;
