Delimiter //
/**
 * Author:  Benoit DD <b.danquechindorval@amo-soft.com>
 * @param entriesId identifiant de l'ecriture à supprimer 
 * @param useTransaction flag indiquant si 'il faut gerer la transaction
 *   - 1 : gerer les transaction
 *   - 0 : ne pas gérer la transaction. Implique que la transaction est gérée en externe 
 * @param tmpTmTableNameOfEntrToDel nom de la table temporaire contentant les écritures à supprimer
 *   - n'est pris compte que si useTransaction = 0 (gestion des transaction externe)
 *     et si entriesId = 0 (ne correspond à aucune ecriture car l'auto icrement des écritures
 *     commence à 1)
 * la table temporaire doit avoir une colonne ENTRIES_ID_ENTRIES
 * la table ne dois pas s'appeler TMP_ENTRIES_TO_DELETE
 * Created: 11 avr. 2016
 */
Drop Procedure If Exists BUSINESS_DELETE_ENTRIES //
Create Procedure BUSINESS_DELETE_ENTRIES(
    In entriesId int Unsigned
    , In useTransaction TinyInt
    , In tmpTmTableNameOfEntrToDel Varchar(50)
)
Begin
-- Code erreur 1 146, état SQL 42S02: Table 'CASHBOX.TMP_ENTREIS_TO_DEL_01' doesn't exist
    bdelent_main: Begin

        Declare transactionStarted int default 0;
        
        set @bdelent_entriesId = Coalesce(entriesId, 0);
        set @bdelent_useTransaction = Coalesce(useTransaction, 1);
        set @bdelent_tmpTmTableNameOfEntrToDel = trim(Coalesce(tmpTmTableNameOfEntrToDel, ''));


        bde_singleOrMultipleDelete: Begin
            Drop Temporary Table if Exists TMP_ENTRIES_TO_DELETE;
            Create Temporary Table TMP_ENTRIES_TO_DELETE (
                ID_ENTRIES_TO_DEL Int Unsigned Not Null Primary Key
            );



            bde_multipleDelete: Begin
                -- est ce une supression mutliple ou simple ?
                -- on test 
                --  - si la transaction est extene (useTransaction)
                --  - si l'id entrie vaut 0 (ne correponspond à aucune entries 0)
                --  - si tmpTmTableNameOfEntrToDel  est fourni

               
                if @bdelent_entriesId <> 0 Or @bdelent_useTransaction = 1 Or @bdelent_tmpTmTableNameOfEntrToDel = '' Then
                    leave bde_multipleDelete;
                End If;
                bde_multipleDelete_copyentries: Begin
                    -- on test la copie des écrtiture depuis la table temporaire indiquée
                    -- en cas d'erreur on propage l'exeception
                    -- autre solution à affiner on fait une pseudo copie
-- 
--                     Declare Continue Handler for SQLEXCEPTION 
                    Declare Continue Handler For SQLSTATE '42S02'
--                     Declare Exit Handler For Sqlexception 
--                     Declare Exit Handler For SQLSTATE '42S02'
                    Begin
--                         If transactionStarted  <> 0 then
--                             rollback;
--                             set transactionStarted  = 0;
--                         End if;
--                         leave bde_multipleDelete_copyentries;
                        Resignal;
                    End ;
                    Set @bde_copyQuery = Concat(
                                'Insert Into TMP_ENTRIES_TO_DELETE (ID_ENTRIES_TO_DEL) '
                                , 'Select ENTRIES_ID_ENTRIES From ' 
                                ,  @bdelent_tmpTmTableNameOfEntrToDel
                            )
                        ;
                    PREPARE bde_copyentiresStmt FROM @bde_copyQuery; 
                    execute bde_copyentiresStmt 
                        ;
                    Deallocate prepare bde_copyentiresStmt; 		

                End ;

            end ;

        End ;

        bde_deletionMain: Begin

            Declare Exit Handler For Sqlexception
            Begin
                If transactionStarted  <> 0 then
                    rollback;
                    set transactionStarted  = 0;
                End if;
                Resignal;
            End;

            Set useTransaction = Coalesce(useTransaction, 1);
            If useTransaction <> 0 Then
                Start Transaction;
                Set transactionStarted  = 1;
            End If;


            bde_singleDelete_copyentries: Begin
                -- en mode multiple @bdelent_entriesId = 0
                -- donc pas de doublons d'entries
                Insert Into TMP_ENTRIES_TO_DELETE (ID_ENTRIES_TO_DEL)
                Select ID_ENTRIES ID_ENTRIES_TO_DEL
                From ENTRIES 
                Where 
                    ID_ENTRIES = @bdelent_entriesId
                ;
            End ;

        -- ici TMP_ENTRIES_TO_DELETE contient toutes les écritures à supprimer
        -- que ce soit une éctiture unique (fourni par entriesId)
        -- ou une liste d'ecriture (dans la table de nom tmpTmTableNameOfEntrToDel)

--             Drop Temporary Table if Exists TMP_ENTRIES_TO_DELETE;
--             Create Temporary Table TMP_ENTRIES_TO_DELETE (
--                 ID_ENTRIES_TO_DEL Int Unsigned Not Null Primary Key
--             )
--             Select ID_ENTRIES ID_ENTRIES_TO_DEL
--             From ENTRIES 
--             Where 
--                 ID_ENTRIES = entriesId
--             ;

            Drop Temporary Table if Exists TMP_RECLAIMS_TO_DELETE;
            Create Temporary Table TMP_RECLAIMS_TO_DELETE (
--                 Unique Index(ID_RECLAIMS_DETAILS)
                Index(ID_RECLAIMS_DETAILS)
                , Index(RECLAIMS_ID_RECLAIMS)
            )
            Select 
                ID_RECLAIMS_DETAILS
                , RECLAIMS_ID_RECLAIMS
            From
                RECLAIMS_DETAILS
                Inner Join ENTRIES
                    On ENTRIES_ID_ENTRIES = ID_ENTRIES
                Inner Join TMP_ENTRIES_TO_DELETE
                    On ID_ENTRIES = ID_ENTRIES_TO_DEL
            ;

            Set @partialMatch = (Select  ID_DF_MATCHINGCODE_STATE From DF_MATCHINGCODE_STATE where DF_MATCHINGCODE_STATE_PARTIAL = 1 Limit 1 );
            Update ENTRIES_MATCHCOD
                Inner Join ENTRIES
                    On ENTRIES_ID_ENTRIES = ID_ENTRIES
                Inner Join TMP_ENTRIES_TO_DELETE
                    On ID_ENTRIES = ID_ENTRIES_TO_DEL
            Set
                ENTRIES.DF_MATCH_STAT_ID_DF_MATCH_STAT = @partialMatch
            ;

            Delete ENTRIES_MATCHCOD 
            From ENTRIES_MATCHCOD
                Inner Join ENTRIES
                    On ENTRIES_ID_ENTRIES = ID_ENTRIES
                Inner Join TMP_ENTRIES_TO_DELETE
                    On ID_ENTRIES = ID_ENTRIES_TO_DEL
            ;
            Delete MATCHINGCODES
            From
                MATCHINGCODES
                Left Join ENTRIES_MATCHCOD
                    On ID_MATCHINGCODES = MATCHCOD_ID_MATCHCOD
            Where ID_ENTRIES_MATCHCOD Is Null
            ;
            Delete ENTRIES_COMPUTED_DATA
            From
                ENTRIES_COMPUTED_DATA
                Inner Join ENTRIES
                    On ENTRIES_ID_ENTRIES = ID_ENTRIES
                Inner Join TMP_ENTRIES_TO_DELETE
                    On ID_ENTRIES = ID_ENTRIES_TO_DEL
            ;
            Delete ENTRIES_NB_RECLAIMS_VIEW_FOR_VIEW
            From
                ENTRIES_NB_RECLAIMS_VIEW_FOR_VIEW
                Inner Join ENTRIES
                    On ENTRIES_ID_ENTRIES = ID_ENTRIES
                Inner Join TMP_ENTRIES_TO_DELETE
                    On ID_ENTRIES = ID_ENTRIES_TO_DEL
            ;
            Delete ENTRIES_REF_CUST
            From
                ENTRIES_REF_CUST
            ;
            Delete ENTRIES_REF_DATAS
            From
                ENTRIES_REF_DATAS
                Inner Join ENTRIES
                    On ENTRIES_ID_ENTRIES = ID_ENTRIES
                Inner Join TMP_ENTRIES_TO_DELETE
                    On ID_ENTRIES = ID_ENTRIES_TO_DEL
            ;
            Delete ENTRIES_has_ACCOUNTS
            From
                ENTRIES_has_ACCOUNTS
                Inner Join ENTRIES
                    On ENTRIES_ID_ENTRIES = ID_ENTRIES
                Inner Join TMP_ENTRIES_TO_DELETE
                    On ID_ENTRIES = ID_ENTRIES_TO_DEL
            ;
            Delete ENTRIES_has_ATTACHED_DOCUMENTS
            From
                ENTRIES_has_ATTACHED_DOCUMENTS
                Inner Join ENTRIES
                    On ENTRIES_ID_ENTRIES = ID_ENTRIES
                Inner Join TMP_ENTRIES_TO_DELETE
                    On ID_ENTRIES = ID_ENTRIES_TO_DEL
            ;
            Delete ENTRIES_has_PAYMT_SCHEDLS
            From
                ENTRIES_has_PAYMT_SCHEDLS
                Inner Join ENTRIES
                    On ENTRIES_ID_ENTRIES = ID_ENTRIES
                Inner Join TMP_ENTRIES_TO_DELETE
                    On ID_ENTRIES = ID_ENTRIES_TO_DEL
            ;
            Delete RECLAIMS_DETAILS
            From
                RECLAIMS_DETAILS
                Inner Join TMP_RECLAIMS_TO_DELETE
                    On TMP_RECLAIMS_TO_DELETE.ID_RECLAIMS_DETAILS = RECLAIMS_DETAILS.ID_RECLAIMS_DETAILS
            ;
            Delete RECLAIMS_has_TASKS
            From
                RECLAIMS_has_TASKS
                Left Join RECLAIMS_DETAILS
                    On RECLAIMS_DET_ID_RECLAIMS_DET = ID_RECLAIMS_DETAILS
            ;
--             Delete SUMMARIES
--             From
--                 SUMMARIES
--                 Inner Join TMP_RECLAIMS_TO_DELETE
--                     On TMP_RECLAIMS_TO_DELETE.RECLAIMS_ID_RECLAIMS = SUMMARIES.RECLAIMS_ID_RECLAIMS
--             ;
            
            Delete SUMMARIES
            From
                SUMMARIES
            Where SUMMARIES.RECLAIMS_ID_RECLAIMS In (
                    Select TMP_RECLAIMS_TO_DELETE.RECLAIMS_ID_RECLAIMS
                    From TMP_RECLAIMS_TO_DELETE
                    Where TMP_RECLAIMS_TO_DELETE.RECLAIMS_ID_RECLAIMS = SUMMARIES.RECLAIMS_ID_RECLAIMS
                )
                
            ;


--             Delete RECLAIMS
--             From
--                 RECLAIMS
--                 Inner Join TMP_RECLAIMS_TO_DELETE
--                     On TMP_RECLAIMS_TO_DELETE.RECLAIMS_ID_RECLAIMS = RECLAIMS.ID_RECLAIMS
--                 Left Join RECLAIMS_DETAILS
--                     On RECLAIMS_DETAILS.RECLAIMS_ID_RECLAIMS = RECLAIMS.ID_RECLAIMS
--             Where 
--                 RECLAIMS_DETAILS.ID_RECLAIMS_DETAILS Is Null
            Delete RECLAIMS
            From
                RECLAIMS
                Left Join RECLAIMS_DETAILS
                    On RECLAIMS_DETAILS.RECLAIMS_ID_RECLAIMS = RECLAIMS.ID_RECLAIMS
            Where 
                RECLAIMS.ID_RECLAIMS In (
                    Select TMP_RECLAIMS_TO_DELETE.RECLAIMS_ID_RECLAIMS
                    From TMP_RECLAIMS_TO_DELETE
                    Where TMP_RECLAIMS_TO_DELETE.RECLAIMS_ID_RECLAIMS = RECLAIMS.ID_RECLAIMS
                )
                And RECLAIMS_DETAILS.ID_RECLAIMS_DETAILS Is Null
            ;



            Delete SUMMARIES
            From
                SUMMARIES
                Inner Join TMP_ENTRIES_TO_DELETE
                    On TMP_ENTRIES_TO_DELETE.ID_ENTRIES_TO_DEL = SUMMARIES.ENTRIES_ID_ENTRIES
            ;
            Delete ENTRIES
            From
                ENTRIES
                Inner Join TMP_ENTRIES_TO_DELETE
                    On ID_ENTRIES = ID_ENTRIES_TO_DEL
            ;

            If transactionStarted <> 0 Then
                Commit ;
            End If;
        End ;
    End ;
End //
Delimiter ;