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

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

        Set @beca_srcEntityId = srcEntityId;
        Set @beca_tarEntityId = tarEntityId;

        beca_cp_df_tbl: Begin
            Drop Temporary table if exists TMP_ENTTCP_ENTITIES_AGENCIES;
            Create Temporary table TMP_ENTTCP_ENTITIES_AGENCIES (
                        SRC_ENTITIES_ID_ENTITIES            int unsigned not null
                        , SRC_ID_ENTITIES_AGENCIES          int unsigned not null
                        , ENTITIES_AGENCIES_CODE            varchar (45) not null
                        , ENTITIES_AGENCIES_HEADER_PICTURE  int unsigned null
                        , ENTITIES_AGENCIES_FOOTER_PICTURE  int unsigned null
                        , ENTITIES_AGENCIES_EXTRA_PICTURE   int unsigned null
                        , ENTITIES_AGENCIES_LABEL_1         varchar (255) null
                        , ENTITIES_AGENCIES_LABEL_2         varchar (255) null
                        , ENTITIES_AGENCIES_LABEL_3         varchar (255) null
                        , ENTITIES_AGENCIES_LABEL_4         varchar (255) null
                        , ENTITIES_AGENCIES_LABEL_5         varchar (255) null

                        , TAR_ENTITIES_ID_ENTITIES          int unsigned not null
                        , TAR_ID_ENTITIES_AGENCIES          int unsigned null
                        , TAR_NEW_ID_ENTITIES_AGENCIES      int unsigned null

                        , index(ENTITIES_AGENCIES_CODE)
                        , index(SRC_ID_ENTITIES_AGENCIES)
                        , index(TAR_NEW_ID_ENTITIES_AGENCIES)
                    )
                ;
            Insert Into TMP_ENTTCP_ENTITIES_AGENCIES (
                    SRC_ENTITIES_ID_ENTITIES, SRC_ID_ENTITIES_AGENCIES
                    , ENTITIES_AGENCIES_CODE, ENTITIES_AGENCIES_HEADER_PICTURE, ENTITIES_AGENCIES_FOOTER_PICTURE
                    , ENTITIES_AGENCIES_EXTRA_PICTURE, ENTITIES_AGENCIES_LABEL_1, ENTITIES_AGENCIES_LABEL_2
                    , ENTITIES_AGENCIES_LABEL_3, ENTITIES_AGENCIES_LABEL_4, ENTITIES_AGENCIES_LABEL_5

                    , TAR_ENTITIES_ID_ENTITIES, TAR_ID_ENTITIES_AGENCIES
                )
            Select
                src.ENTITIES_ID_ENTITIES, src.ID_ENTITIES_AGENCIES
                , src.ENTITIES_AGENCIES_CODE, src.ENTITIES_AGENCIES_HEADER_PICTURE, src.ENTITIES_AGENCIES_FOOTER_PICTURE
                , src.ENTITIES_AGENCIES_EXTRA_PICTURE, src.ENTITIES_AGENCIES_LABEL_1, src.ENTITIES_AGENCIES_LABEL_2
                , src.ENTITIES_AGENCIES_LABEL_3, src.ENTITIES_AGENCIES_LABEL_4, src.ENTITIES_AGENCIES_LABEL_5
                , @beca_tarEntityId, tar.ID_ENTITIES_AGENCIES
            From ENTITIES_AGENCIES src
                Left Join ENTITIES_AGENCIES tar
                    On src.ENTITIES_AGENCIES_CODE = tar.ENTITIES_AGENCIES_CODE
                    And tar.ENTITIES_ID_ENTITIES = @beca_tarEntityId
            where
                src.ENTITIES_ID_ENTITIES = @beca_srcEntityId 
            ;
            -- il n'y a pas de langue pour les agence, pas besoin de passer par une table intermédiaure
            -- pour calculer les nouveau id
            -- on peut injecter directement
            Insert Into ENTITIES_AGENCIES (
                    ENTITIES_ID_ENTITIES, ENTITIES_AGENCIES_CODE, ENTITIES_AGENCIES_HEADER_PICTURE
                    , ENTITIES_AGENCIES_FOOTER_PICTURE, ENTITIES_AGENCIES_EXTRA_PICTURE, ENTITIES_AGENCIES_LABEL_1
                    , ENTITIES_AGENCIES_LABEL_2, ENTITIES_AGENCIES_LABEL_3, ENTITIES_AGENCIES_LABEL_4
                    , ENTITIES_AGENCIES_LABEL_5
                ) 
            Select
                TAR_ENTITIES_ID_ENTITIES, ENTITIES_AGENCIES_CODE, ENTITIES_AGENCIES_HEADER_PICTURE
                , ENTITIES_AGENCIES_FOOTER_PICTURE, ENTITIES_AGENCIES_EXTRA_PICTURE, ENTITIES_AGENCIES_LABEL_1
                , ENTITIES_AGENCIES_LABEL_2, ENTITIES_AGENCIES_LABEL_3, ENTITIES_AGENCIES_LABEL_4
                , ENTITIES_AGENCIES_LABEL_5
            From TMP_ENTTCP_ENTITIES_AGENCIES
            Where TAR_ID_ENTITIES_AGENCIES is null
            ;

        end ;

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

        Drop Temporary table if exists TMP_ENTTCP_ENTITIES_AGENCIES;

    end;
End //
Delimiter ;