Delimiter //
/**
 * copy accounts categories from one entity to another
 *
 * @author Armen
 */
Drop Procedure if Exists BUSINESS_ENTITIES_COPY_ACC_CATEG//

Create Procedure BUSINESS_ENTITIES_COPY_ACC_CATEG(
    In srcEntityId Int Unsigned
    , In tarEntityId Int Unsigned
    , In inTransaction smallint
)
Begin
    becac_Main: begin
        Declare becac_transactionStarted int default 0 ;
        DECLARE Exit HANDLER FOR SQLEXCEPTION
            Begin
                if becac_transactionStarted  <> 0 then
                    rollback;
                    set becac_transactionStarted  = 0;
                End if;
                resignal;
            end ;
        if Coalesce(inTransaction, 0) = 0 Then
            Start Transaction;
            Set becac_transactionStarted  = 1;
        else
            Set becac_transactionStarted  = 0;
        end if;

        Set @becac_srcEntityId = srcEntityId;
        Set @becac_tarEntityId = tarEntityId;

        -- rappel la creation/suppression de table temporaire ne cause pas un commit implicite
        -- en revanche un alter le fait
        becac_cp_df_tbl: Begin
            -- table temporaire avec les nouveau id de code categori
            Drop Temporary table if exists TMP_ENTTCP_DF_ACC_CATEG;
            Create Temporary table TMP_ENTTCP_DF_ACC_CATEG (
                    SRC_ENTITIES_ID_ENTITIES            int unsigned not null
                    , SRC_ID_DF_ACCOUNTS_CATEGORIES     int unsigned not null

                    , DF_ACC_CATEG_CODE                 varchar(12) not null
                    , DF_ACC_CATEG_BACKGROUND_COLOR     char(6) null
                    , DF_ACC_CATEG_FONT_COLOR           char(6) null
                    , DF_ACC_STATE_ID_DF_ACC_STATE      int unsigned null
                    , DF_ACC_CATEG_TRASH                smallint default 0 not null

                    , TAR_ENTITIES_ID_ENTITIES          int unsigned Not null
                    , TAR_ID_DF_ACCOUNTS_CATEGORIES     int unsigned null
                    , TAR_NEW_ID_DF_ACCOUNTS_CATEGORIES int unsigned null

                    , index (DF_ACC_CATEG_CODE)
                    , index (SRC_ENTITIES_ID_ENTITIES)
                    , index (TAR_ENTITIES_ID_ENTITIES)
                )
            ;

            -- recherche des codes existants

            Insert Into TMP_ENTTCP_DF_ACC_CATEG (

                    SRC_ENTITIES_ID_ENTITIES, SRC_ID_DF_ACCOUNTS_CATEGORIES
                    , DF_ACC_CATEG_CODE, DF_ACC_CATEG_BACKGROUND_COLOR
                    , DF_ACC_CATEG_FONT_COLOR, DF_ACC_STATE_ID_DF_ACC_STATE, DF_ACC_CATEG_TRASH

                    , TAR_ENTITIES_ID_ENTITIES, TAR_ID_DF_ACCOUNTS_CATEGORIES
            )
            select
                src.ENTITIES_ID_ENTITIES, src.ID_DF_ACCOUNTS_CATEGORIES
                , src.DF_ACC_CATEG_CODE, src.DF_ACC_CATEG_BACKGROUND_COLOR
                , src.DF_ACC_CATEG_FONT_COLOR, das.ID_DF_ACC_STATE, src.DF_ACC_CATEG_TRASH
                , @becac_tarEntityId, tar.ID_DF_ACCOUNTS_CATEGORIES
            from DF_ACC_CATEG src
                Left Join DF_ACC_CATEG tar
                    On src.DF_ACC_CATEG_CODE = tar.DF_ACC_CATEG_CODE
                    And tar.ENTITIES_ID_ENTITIES = @becac_tarEntityId
                -- en raison de la présences de donné ne respectant pas les FK
                -- (données initiales incorrect)
                -- on utilise une jointure pour les détecter est les remplacer par des null
                Left Join DF_ACC_STATE das
                    On src.DF_ACC_STATE_ID_DF_ACC_STATE = das.ID_DF_ACC_STATE
                    -- ci dessous implicite
    #                 And src.ENTITIES_ID_ENTITIES = @becac_srcEntityId
            where src.ENTITIES_ID_ENTITIES = @becac_srcEntityId
            ;

            -- calcules des nouveaux id pour les codes n'existants pas dans l'entité cible
            -- table tmp avec auto increment
            Drop Temporary table if exists TMP_ENTTCP_DF_ACC_CATEG_NEWID;
            Create Temporary table TMP_ENTTCP_DF_ACC_CATEG_NEWID(
                        ID_TMP_ENTTCP_DF_ACC_CATEG_NEWID    int unsigned not null primary key auto_increment
                        , DF_ACC_CATEG_CODE                 varchar(12) not null
                        , index (DF_ACC_CATEG_CODE)
                    )
                ;
            Insert Into TMP_ENTTCP_DF_ACC_CATEG_NEWID (
                    DF_ACC_CATEG_CODE
                )
            Select
                tar.DF_ACC_CATEG_CODE
            From
                TMP_ENTTCP_DF_ACC_CATEG tar
            WHERE
                TAR_ID_DF_ACCOUNTS_CATEGORIES IS Null
            ;

            Set @lac_lastid = Coalesce(
                        (
                            Select ID_DF_ACCOUNTS_CATEGORIES From DF_ACC_CATEG Order By 1 Desc Limit 1
                        ), 0
                    )
                ;
    #         call CASH_AUTOINCREMENT_VALUE('DF_ACC_CATEG', @lac_lastid);

            Update TMP_ENTTCP_DF_ACC_CATEG tar
                Inner Join TMP_ENTTCP_DF_ACC_CATEG_NEWID src
                    On tar.DF_ACC_CATEG_CODE = src.DF_ACC_CATEG_CODE
            Set
                tar.TAR_NEW_ID_DF_ACCOUNTS_CATEGORIES = src.ID_TMP_ENTTCP_DF_ACC_CATEG_NEWID + @lac_lastid
                , tar.TAR_ID_DF_ACCOUNTS_CATEGORIES = src.ID_TMP_ENTTCP_DF_ACC_CATEG_NEWID + @lac_lastid

            -- ci dessous implicite
    #         WHERE tar.TAR_ID_DF_ACCOUNTS_CATEGORIES is null
            ;
            Insert Into DF_ACC_CATEG(
                         ID_DF_ACCOUNTS_CATEGORIES, ENTITIES_ID_ENTITIES, DF_ACC_CATEG_CODE
                         , DF_ACC_CATEG_BACKGROUND_COLOR, DF_ACC_CATEG_FONT_COLOR, DF_ACC_STATE_ID_DF_ACC_STATE
                    )
            Select TAR_NEW_ID_DF_ACCOUNTS_CATEGORIES, TAR_ENTITIES_ID_ENTITIES, DF_ACC_CATEG_CODE
                 , DF_ACC_CATEG_BACKGROUND_COLOR, DF_ACC_CATEG_FONT_COLOR, DF_ACC_STATE_ID_DF_ACC_STATE
            From TMP_ENTTCP_DF_ACC_CATEG
            WHERE
                TAR_NEW_ID_DF_ACCOUNTS_CATEGORIES Is NOT Null
            ;
        End ;


        becac_cp_ln_tbl: Begin
            INSERT Into DF_ACC_CATEG_LNG (
                     DF_ACC_CATEG_LNG_LABEL, DF_ACC_CATG_ID_DF_ACCNTS_CATG, DF_LANG_ID_DEF_LANG
                )
            Select
                src.DF_ACC_CATEG_LNG_LABEL, tbl1.TAR_ID_DF_ACCOUNTS_CATEGORIES, src.DF_LANG_ID_DEF_LANG
            FROM TMP_ENTTCP_DF_ACC_CATEG tbl1
                Inner Join DF_ACC_CATEG_LNG src
                    On tbl1.SRC_ID_DF_ACCOUNTS_CATEGORIES = src.DF_ACC_CATG_ID_DF_ACCNTS_CATG
                Left Join DF_ACC_CATEG_LNG chk
                    On tbl1.TAR_ID_DF_ACCOUNTS_CATEGORIES = chk.DF_ACC_CATG_ID_DF_ACCNTS_CATG
            Where chk.ID_DF_ACC_CATEG_LNG Is Null
            ;
        end ;

#         select * from DF_ACC_CATEG;
        if(becac_transactionStarted <> 0 ) Then
            -- pour dev / debug
#             rollback ;
            commit;
            Set becac_transactionStarted = 0;
        End if;

        Drop Temporary table if exists TMP_ENTTCP_DF_ACC_CATEG;
        Drop Temporary table if exists TMP_ENTTCP_DF_ACC_CATEG_NEWID;

    end ;
End //
Delimiter ;