Delimiter //
/**
 * copy reclaims status from one entity to another
 *
 * @author Armen
 */
Drop Procedure if Exists BUSINESS_ENTITIES_COPY_RECLAIMS_STATUS//

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

        Set @becrs_srcEntityId = srcEntityId;
        Set @becrs_tarEntityId = tarEntityId;

        becrs_cp_df_rcl_st_tbl: Begin
            Drop Temporary table if exists TMP_ENTTCP_DF_RECLAIMS_STATUS;
            Create Temporary table TMP_ENTTCP_DF_RECLAIMS_STATUS (
                        SRC_ENTITIES_ID_ENTITIES            int unsigned not null
                        , SRC_ID_DF_RECLAIMS_STATUS         int unsigned

                        , DF_R_ST_A_TYP_ID_DF_R_ST_A_TYP    int unsigned not null
                        , DF_RECLAIMS_STATUS_CODE           char(10) null
                        , DF_RECLAIMS_STATUS_CATEG          varchar(10) null
                        , DF_RECLAIMS_STATUS_CLOSED         smallint not null
                        , DF_RECLAIMS_STATUS_TRASH          smallint not null
                        , DF_RECLAIMS_STATUS_DEFAULT        smallint(5) not null

                        , TAR_ENTITIES_ID_ENTITIES          int unsigned not null
                        , TAR_ID_DF_RECLAIMS_STATUS         int unsigned
                        , TAR_NEW_ID_DF_RECLAIMS_STATUS     int unsigned

                        , index (DF_RECLAIMS_STATUS_CODE)
                        , index (SRC_ENTITIES_ID_ENTITIES)
                        , index (TAR_ID_DF_RECLAIMS_STATUS)
                    )
                ;

            -- recherche des codes existants
            Insert Into TMP_ENTTCP_DF_RECLAIMS_STATUS (
                    SRC_ENTITIES_ID_ENTITIES, SRC_ID_DF_RECLAIMS_STATUS
                    , DF_R_ST_A_TYP_ID_DF_R_ST_A_TYP, DF_RECLAIMS_STATUS_CODE, DF_RECLAIMS_STATUS_CATEG
                    , DF_RECLAIMS_STATUS_CLOSED, DF_RECLAIMS_STATUS_TRASH, DF_RECLAIMS_STATUS_DEFAULT

                    , TAR_ENTITIES_ID_ENTITIES, TAR_ID_DF_RECLAIMS_STATUS
                )
            Select
                src.ENTITIES_ID_ENTITIES, src.ID_DF_RECLAIMS_STATUS
                , src.DF_R_ST_A_TYP_ID_DF_R_ST_A_TYP, src.DF_RECLAIMS_STATUS_CODE, src.DF_RECLAIMS_STATUS_CATEG
                , src.DF_RECLAIMS_STATUS_CLOSED, src.DF_RECLAIMS_STATUS_TRASH, 0
                , @becrs_tarEntityId, tar.ID_DF_RECLAIMS_STATUS
            From DF_RECLAIMS_STATUS src
                Left Join DF_RECLAIMS_STATUS tar
                    On src.DF_RECLAIMS_STATUS_CODE = tar.DF_RECLAIMS_STATUS_CODE
                    And tar.ENTITIES_ID_ENTITIES = @becrs_tarEntityId
            Where
                src.ENTITIES_ID_ENTITIES = @becrs_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_RECLAIMS_STATUS_NEWID;
            Create Temporary table TMP_ENTTCP_DF_RECLAIMS_STATUS_NEWID(
                        ID_TMP_ENTTCP_DF_RECLAIMS_STATUS_NEWID    int unsigned not null primary key auto_increment
                        , DF_RECLAIMS_STATUS_CODE                 varchar(10) not null
                        , index (DF_RECLAIMS_STATUS_CODE)
                    )
                ;


            Insert Into TMP_ENTTCP_DF_RECLAIMS_STATUS_NEWID (
                DF_RECLAIMS_STATUS_CODE
            )
            Select
                tar.DF_RECLAIMS_STATUS_CODE
            From TMP_ENTTCP_DF_RECLAIMS_STATUS tar
            Where
                tar.TAR_ID_DF_RECLAIMS_STATUS Is Null
            ;

            Set @ldrs_lastid = Coalesce(
                    (
                        Select ID_DF_RECLAIMS_STATUS From DF_RECLAIMS_STATUS Order By 1 Desc Limit 1
                    ), 0
                )
            ;

            Update TMP_ENTTCP_DF_RECLAIMS_STATUS tar
                Inner Join TMP_ENTTCP_DF_RECLAIMS_STATUS_NEWID src
                On tar.DF_RECLAIMS_STATUS_CODE = src.DF_RECLAIMS_STATUS_CODE
            Set
                tar.TAR_NEW_ID_DF_RECLAIMS_STATUS = src.ID_TMP_ENTTCP_DF_RECLAIMS_STATUS_NEWID + @ldrs_lastid
              , tar.TAR_ID_DF_RECLAIMS_STATUS = src.ID_TMP_ENTTCP_DF_RECLAIMS_STATUS_NEWID + @ldrs_lastid

            ;

            Insert Into DF_RECLAIMS_STATUS (
                    ID_DF_RECLAIMS_STATUS, ENTITIES_ID_ENTITIES
                    , DF_R_ST_A_TYP_ID_DF_R_ST_A_TYP, DF_RECLAIMS_STATUS_CODE, DF_RECLAIMS_STATUS_CATEG
                    , DF_RECLAIMS_STATUS_CLOSED, DF_RECLAIMS_STATUS_TRASH, DF_RECLAIMS_STATUS_DEFAULT
                ) 
            Select
                TAR_NEW_ID_DF_RECLAIMS_STATUS, TAR_ENTITIES_ID_ENTITIES
                 , DF_R_ST_A_TYP_ID_DF_R_ST_A_TYP, DF_RECLAIMS_STATUS_CODE, DF_RECLAIMS_STATUS_CATEG
                 , DF_RECLAIMS_STATUS_CLOSED, DF_RECLAIMS_STATUS_TRASH, DF_RECLAIMS_STATUS_DEFAULT
            From TMP_ENTTCP_DF_RECLAIMS_STATUS
            Where
                  TAR_NEW_ID_DF_RECLAIMS_STATUS Is Not Null
            ;
            
        end;
        becrs_cp_df_rcl_st_lng: Begin
            Insert Into DF_RECLAIMS_STATUS_LNG (
                    DF_RCLM_STATS_ID_DF_RCLM_STATS, DF_LANGUAGES_ID_DEF_LANGUAGE, DF_RECLAIMS_STATUS_LNG_LABEL                                               
                )
            Select
                tbl1.TAR_ID_DF_RECLAIMS_STATUS, src.DF_LANGUAGES_ID_DEF_LANGUAGE, src.DF_RECLAIMS_STATUS_LNG_LABEL
            From TMP_ENTTCP_DF_RECLAIMS_STATUS tbl1
                Inner Join DF_RECLAIMS_STATUS_LNG src
                    On tbl1.SRC_ID_DF_RECLAIMS_STATUS = src.DF_RCLM_STATS_ID_DF_RCLM_STATS
                Left Join DF_RECLAIMS_STATUS_LNG chk
                    On tbl1.TAR_ID_DF_RECLAIMS_STATUS = chk.DF_RCLM_STATS_ID_DF_RCLM_STATS
            Where
                chk.ID_DF_RECLAIMS_STATUS_LNG IS Null
            ;
        end ;

        if(becrs_transactionStarted <> 0 ) Then
            -- pour dev / debug
#             rollback ;
            commit;
            Set becrs_transactionStarted = 0;
        End if;

        Drop Temporary table if exists TMP_ENTTCP_DF_RECLAIMS_STATUS;
        Drop Temporary table if exists TMP_ENTTCP_DF_RECLAIMS_STATUS_NEWID;

    end ;
End //
Delimiter ;