Delimiter //
/**
 * Author:  armen
 * Created: 27 sept. 2016
 */

Drop Procedure If Exists BUSINESS_COPY_DFMESSAGES //
Create Procedure BUSINESS_COPY_DFMESSAGES(
    In idDfMessagesTypes int    
    , In newDfMessagesTypesCode Varchar(25)    
    , In newDfMessagesTypesLabel Varchar(50)    
    , In newDfMessagesTypesNote Varchar(150)    
    , In useTransaction TinyInt
)
Begin
    Main: Begin
        Declare transactionStarted int default 0 ;
        DECLARE Exit HANDLER FOR SQLEXCEPTION
        Begin
                if transactionStarted  <> 0 then
                        rollback;
                        set transactionStarted  = 0;
                End if;
                resignal;
        end ;
        Set useTransaction = Coalesce(useTransaction, 0) ;

		set @idDfMessagesTypes = idDfMessagesTypes;
		set @newDfMessagesTypesCode = newDfMessagesTypesCode;
		set @newDfMessagesTypesLabel = newDfMessagesTypesLabel;
		set @newDfMessagesTypesNote = newDfMessagesTypesNote;

		if useTransaction <> 0 Then
			start transaction;
			set transactionStarted  = 1;
		End If;

		-- vérification si le nouveau code existe
		bsncpdfmdls_chkCodeExists: Begin
			Set @psCount = Coalesce(
									(
										Select Count(*) 
										from DF_MESSAGES_TYPES T1
										Where T1.DF_MESSAGES_TYPES_CODE = @newDfMessagesTypesCode
									)
								, 0
							)
						;
			if(@psCount > 0) Then

				Set @bsncpdfmdls_txt = 'Could not copy message model because the new code exists';
				SIGNAL SQLSTATE '99002'
					SET MESSAGE_TEXT = @bsncpdfmdls_txt
				;
			End if;
		End ;
        

        --  obtention des ids
        bsncpdfmdls_preparaIds: Begin 
			
			Set @maxIdDfMessagesTypes = (select max(ID_DF_MESSAGES_TYPES) From DF_MESSAGES_TYPES);
		end;

        -- copying data
        bsncpdfmdls_copyData: Begin 
            
			-- ------------------
			-- ------------------
			Insert Into DF_MESSAGES_TYPES(
				`ID_DF_MESSAGES_TYPES`, `ADMIN_PARAMS_ID_ADMIN_PARAMS`
				, `DF_MESSAGES_TYPES_LABEL`, `DF_MESSAGES_TYPES_CODE`
				, `DF_MESSAGES_TYPES_NOTES`, `DF_MESSAGES_TYPES_TRASH`
			)
			Select 
				@maxIdDfMessagesTypes + 1 `ID_DF_MESSAGES_TYPES`, SRC.`ADMIN_PARAMS_ID_ADMIN_PARAMS`
				,  Coalesce(NullIf(@newDfMessagesTypesLabel, ''), SRC.`DF_MESSAGES_TYPES_LABEL`)
				,  @newDfMessagesTypesCode DF_MESSAGES_TYPES_CODE
				,  Coalesce(NullIf(@newDfMessagesTypesNote, ''), SRC.`DF_MESSAGES_TYPES_NOTES`)
				,  SRC.`DF_MESSAGES_TYPES_TRASH`
			From DF_MESSAGES_TYPES SRC
			Where SRC.`ID_DF_MESSAGES_TYPES` = @idDfMessagesTypes
			;

			-- ------------------
			-- ------------------
			Insert Into DF_MESSAGES(
				`DF_LANGUAGES_ID_DF_LANGUAGES`
				, `DF_MSG_TYPES_ID_DF_MSG_TYPES`
				, `DF_MESSAGES_MERGE_MODEL`, `DF_MESSAGES_EDITOR_MODEL`, `DF_MESSAGES_MODEL_DATA`
			)
			Select
				SRC.`DF_LANGUAGES_ID_DF_LANGUAGES`
				, @maxIdDfMessagesTypes + 1 `DF_MSG_TYPES_ID_DF_MSG_TYPES`
				, SRC.`DF_MESSAGES_MERGE_MODEL`, SRC.`DF_MESSAGES_EDITOR_MODEL`
				, SRC.`DF_MESSAGES_MODEL_DATA`
			From DF_MESSAGES SRC
			Where SRC.`DF_MSG_TYPES_ID_DF_MSG_TYPES` = @idDfMessagesTypes
			;


        End;
        -- validating all
        if transactionStarted <> 0 Then
            commit ;
        End If;
    End ;


End //

Delimiter ;