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

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

        Set @becr_srcEntityId = srcEntityId;
        Set @becr_tarEntityId = tarEntityId;

        becr_cp_df_rcl_tbl: Begin
            Drop Temporary table if exists TMP_ENTTCP_DF_RECLAIMS;
            create Temporary table TMP_ENTTCP_DF_RECLAIMS (
                    SRC_ENTITIES_ID_ENTITIES    int unsigned not null
                    , SRC_ID_DF_RECLAIMS        int unsigned

                    , DF_PATTERN_ID_DF_PATTERN  int unsigned null
                    , DF_RECLAIMS_CODE          varchar(10)null
                    , DF_RECLAIMS_CATEGORY      varchar(10) null
                    , DF_RECLAIMS_LEAVEOUT_MODE smallint not null
                    , DF_RECLAIMS_DEAD_LINE     int null
                    , DF_RECLAIMS_TRASH         smallint not null
                    , DF_RECLAIMS_FAVORITE      smallint(5) not null

                    , TAR_ENTITIES_ID_ENTITIES  int unsigned not null
                    , TAR_ID_DF_RECLAIMS        int unsigned
                    , TAR_NEW_ID_DF_RECLAIMS    int unsigned

                    , index(DF_RECLAIMS_CODE)
                    , index(SRC_ENTITIES_ID_ENTITIES)
                    , index(TAR_ID_DF_RECLAIMS)
                )
            ;

            -- recherche des codes existants
            Insert Into TMP_ENTTCP_DF_RECLAIMS(
                    SRC_ENTITIES_ID_ENTITIES, SRC_ID_DF_RECLAIMS
                    , DF_PATTERN_ID_DF_PATTERN, DF_RECLAIMS_CODE, DF_RECLAIMS_CATEGORY, DF_RECLAIMS_LEAVEOUT_MODE
                    , DF_RECLAIMS_DEAD_LINE, DF_RECLAIMS_TRASH, DF_RECLAIMS_FAVORITE

                    , TAR_ENTITIES_ID_ENTITIES, TAR_ID_DF_RECLAIMS
                )
            Select
                src.ENTITIES_ID_ENTITIES, src.ID_DF_RECLAIMS
                , src.DF_PATTERN_ID_DF_PATTERN, src.DF_RECLAIMS_CODE, src.DF_RECLAIMS_CATEGORY, src.DF_RECLAIMS_LEAVEOUT_MODE
                , src.DF_RECLAIMS_DEAD_LINE, src.DF_RECLAIMS_TRASH, src.DF_RECLAIMS_FAVORITE
                , @becr_tarEntityId, tar.ID_DF_RECLAIMS
            From DF_RECLAIMS src
                Left Join DF_RECLAIMS tar
                    On src.DF_RECLAIMS_CODE = tar.DF_RECLAIMS_CODE
                    And tar.ENTITIES_ID_ENTITIES = @becr_tarEntityId
            Where
                src.ENTITIES_ID_ENTITIES = @becr_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_NEWID;
            Create Temporary table TMP_ENTTCP_DF_RECLAIMS_NEWID(
                        ID_TMP_ENTTCP_DF_RECLAIMS_NEWID    int unsigned not null primary key auto_increment
                        , DF_RECLAIMS_CODE                 varchar(10) not null
                        , index (DF_RECLAIMS_CODE)
                    )
                ;
            Insert Into TMP_ENTTCP_DF_RECLAIMS_NEWID(
                    DF_RECLAIMS_CODE
                )
            Select
                tar.DF_RECLAIMS_CODE
            From TMP_ENTTCP_DF_RECLAIMS tar
            Where
                  tar.TAR_ID_DF_RECLAIMS Is Null
            ;

            Set @ldr_lastid = Coalesce(
                        (
                            Select ID_DF_RECLAIMS From DF_RECLAIMS Order By 1 Desc Limit 1
                        ), 0
                    )
                ;
            Update TMP_ENTTCP_DF_RECLAIMS tar
                Inner Join TMP_ENTTCP_DF_RECLAIMS_NEWID src
                    On tar.DF_RECLAIMS_CODE = src.DF_RECLAIMS_CODE
            Set
                tar.TAR_NEW_ID_DF_RECLAIMS = src.ID_TMP_ENTTCP_DF_RECLAIMS_NEWID + @ldr_lastid
              , tar.TAR_ID_DF_RECLAIMS = src.ID_TMP_ENTTCP_DF_RECLAIMS_NEWID + @ldr_lastid

            ;

            INSERT Into DF_RECLAIMS (
                 ID_DF_RECLAIMS, ENTITIES_ID_ENTITIES, DF_PATTERN_ID_DF_PATTERN
                 , DF_RECLAIMS_CODE, DF_RECLAIMS_CATEGORY, DF_RECLAIMS_DEAD_LINE
            )
            Select
                TAR_NEW_ID_DF_RECLAIMS, TAR_ENTITIES_ID_ENTITIES, DF_PATTERN_ID_DF_PATTERN
                , DF_RECLAIMS_CODE, DF_RECLAIMS_CATEGORY, DF_RECLAIMS_DEAD_LINE
            From TMP_ENTTCP_DF_RECLAIMS src
            Where
                src.TAR_NEW_ID_DF_RECLAIMS Is Not Null
            ;
        end ;

        becr_cp_df_rcl_lng: Begin
            Insert Into DF_RECLAIMS_LNG (
                 DF_LANGUAGES_ID_DF_LANG, DF_RECLAIMS_ID_DF_RECLAIMS, DF_RECLAIMS_LNG_LABEL
            )
            Select
                src.DF_LANGUAGES_ID_DF_LANG, tbl1.TAR_ID_DF_RECLAIMS, src.DF_RECLAIMS_LNG_LABEL
            From TMP_ENTTCP_DF_RECLAIMS tbl1
                Inner Join DF_RECLAIMS_LNG src
                    On tbl1.SRC_ID_DF_RECLAIMS = src.DF_RECLAIMS_ID_DF_RECLAIMS
                Left Join DF_RECLAIMS_LNG chk
                    On tbl1.TAR_ID_DF_RECLAIMS = chk.DF_RECLAIMS_ID_DF_RECLAIMS
            Where
                chk.ID_DF_RECLAIMS_LNG Is Null
            ;
        end ;


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

        Drop Temporary table if exists TMP_ENTTCP_DF_RECLAIMS;
        Drop Temporary table if exists TMP_ENTTCP_DF_RECLAIMS_NEWID;
    end ;
End //
Delimiter ;