Delimiter //
/**
	procédure stoké qui permet de construire le corp des requete de vérification
	In tableName : le nom de la table ou sous requete
	In checkedFieldName : le nom du champ à vérifier
	In batchNumFieldName : le nom du pour le numéro de lot
	In batchnum : numero de lot
	In flagCountFieldName : le nom du champ totalisant les flags
	In flagMsgsFieldName : le nom du champ avec les message de flag
	In flagMsg : le message
        In lastStatusFieldName
        In lastStatus
	out query : la requête généré
*/

Drop Procedure if exists LOADER_BUILD_CHECK_BASE_QUERY//

Create Procedure LOADER_BUILD_CHECK_BASE_QUERY (
	In tableName varchar(255)
	, In checkedFieldName varchar(70)
	, In batchNumFieldName varchar(70)
	, In batchnum int
	, In flagCountFieldName varchar(70)
	, In flagMsgsFieldName varchar(70)
	, In flagMsg varchar(50)
    , In lastStatusFieldName varchar(70)
	, In lastStatus varchar(150)
	, out query text
)
Begin
	main: begin
-- 		Set @td = '`';
        Set @trgt = ' trgt.';
        set @tableName = concat(' `', tableName, '` trgt ');
-- 		if @td like '%`%' then
		if tableName regexp '(`|((^| )(join|select)($|[ \n])))' then
			-- expression sql, ne pas délimiter
-- 			Set @td = '';
            -- regexp '^[ \n]*\\(.+\\)[ \n]*$' tmp;
            set @tableName = tableName;
            if @tableName not like '%trgt%' Then
                 Set @trgt = ' ';
            End if;

		End If;
        
        -- // Gestion double champs
        set @checkedFieldName = checkedFieldName;
        set @checkedFieldName2 = null;
        If locate(',', checkedFieldName) Then
            set @position = locate(',', checkedFieldName);
            set @checkedFieldName2 = Substring(checkedFieldName, @position + 1);
            set @checkedFieldName = Substring(checkedFieldName, 1, @position - 1);
        End If;

--         call cash_print(@checkedFieldName, 'checkedFieldName');
--         call cash_print(@checkedFieldName2, 'checkedFieldName2');

        -- @tmp pour retro compatibilite
        set @flagCountFieldName = Case When flagCountFieldName Like '%WARN%' Then
                                    'LOADER_STATUS_WARNCOUNT'
                                  Else
                                    'LOADER_STATUS_ERRCOUNT'
                                  End ;

        
		Set @query = concat('update ', @tableName,  
                ' Inner Join LOADER_STATUS STS ', 
                '   ON ', @trgt,'LOADER_STATUS_ID_LOADER_STATUS = STS.ID_LOADER_STATUS ', 
				' Set STS.`', @flagCountFieldName, '` = coalesce(STS.`', @flagCountFieldName,'`, 0) + 1 ') ;

--         call cash_print(@query, 'DEBUG');

		if coalesce(flagMsgsFieldName, '') <> '' Then
--             set @flagMsgText = concat('', coalesce(flagMsg, concat('Champ non valide')));
            set @flagMsgText = concat('', coalesce(flagMsg, concat('unexpected value')));
            set @flagMsgTextContext = concat(''' (', @checkedFieldName, ' = '', coalesce(concat(''"'',', @trgt, '`', @checkedFieldName, '`, ''"'')' ,', ''NULL'')');
            if @checkedFieldName2 Is Not Null Then
--                 set @flagMsgTextContext = concat(@flagMsgTextContext, ', '' et '', ''', @checkedFieldName2, ' = '', coalesce(concat(''"'',', @trgt, '`', @checkedFieldName2, '`, ''"'')' ,', ''NULL'')');
                set @flagMsgTextContext = concat(@flagMsgTextContext, ', '' , '', ''', @checkedFieldName2, ' = '', coalesce(concat(''"'',', @trgt, '`', @checkedFieldName2, '`, ''"'')' ,', ''NULL'')');
            End If;
            set @flagMsgTextContext = concat(@flagMsgTextContext, ', '')''');

--             call cash_print(@flagMsgText, 'DEBUG');

            -- @tmp pour retro compatibilite
            set @flagMsgsFieldName = Case When flagMsgsFieldName Like '%WARN%' Then
                                        'LOADER_STATUS_WARNMSGS'
                                    Else
                                        'LOADER_STATUS_ERRMSGS'
                                    End ;
			
			set @query = Concat(@query, ' , STS.`', @flagMsgsFieldName, '` = concat(coalesce(concat(STS.`', @flagMsgsFieldName,'`, ''\n''), ''''), ''', @flagMsgText, ''',', @flagMsgTextContext ,') ');
		End if;

        
        If lastStatusFieldName Is Not Null Then
            Set @lastStatus = ' NULL ';
            If lastStatus Is Not Null Then
                Set @lastStatus = Concat(' ''', lastStatus, ''' ');
            End If;

            Set @query = Concat(@query, ' , STS.LOADER_CONTACTS_LASTSTATUS =  ', @lastStatus, ' ');

        End If;
		
		Set @query  = concat(@query, ' Where 1 = 1 ');

		if coalesce(batchNumFieldName, '') <> '' Then
			set @batchnum = coalesce(batchnum, 0);
			set @query = Concat(@query, ' And STS.LOADER_STATUS_BATCH_NUM = ', @batchnum);
		end if;

		set query = @query;
	end ;
End //
Delimiter ;