CREATE PROCEDURE [dbo].[FILTER_INVERTER_YIELD] (@database VARCHAR(100), @udid VARCHAR(100), @starttime varchar(40))
AS BEGIN

-- Function replace faulty yield values
-- Test 1: If the previous yield value is greater than the current yield value, current yield value will be replaced by the previous value
-- 
-- @author Milan Rompe
-- @version 1.0

SET NOCOUNT ON

DECLARE @sql nvarchar(2000),
		@yieldUvid nvarchar(100),
		@lastYield bigint,
		@lastYieldDate datetime,
		@lastValidYield bigint,
		@lastValidYieldDate datetime,
		@ParamDefinition nvarchar(500)
    
    -- create log table log_yield_changes
    IF OBJECT_ID('log_yield_changes') IS NULL BEGIN
		CREATE TABLE log_yield_changes (
            RowID INT IDENTITY ( 1 , 1 ),
            db varchar(64) NOT NULL,
            udid varchar(200) NOT NULL,
            date datetime NOT NULL,
	        oldyield bigint, 
	        newyield bigint,
	        changed datetime
	        );
	    -- create index on date to have fast search statements    
	    CREATE NONCLUSTERED INDEX [date_index] ON [dbo].[log_yield_changes] 
		(
			[date] DESC
		)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    
	    ALTER TABLE log_yield_changes 
		ADD CONSTRAINT log_yield_changes_changed
		DEFAULT GETDATE() FOR changed
	END
    
    SET @ParamDefinition = '@yieldOUT bigint OUTPUT,
                            @yieldDateOUT datetime OUTPUT'
    -- TODO MR: replace by value type TOTAL_YIELD
    --  set static yield uvid
	SET @yieldUvid = 'COL_SR_196628'

	-- fetch last inserted yield
	-- which should be testet
	SET @sql = 'SELECT TOP 1 @yieldDateOUT = Date_and_Time, @yieldOUT = ' + @yieldUvid + '
				FROM ' + @database + '.dbo.' + @udid + ' 
				WHERE Date_and_Time = CONVERT(datetime, ''' + @starttime + ''', 21)
				ORDER BY Date_and_time DESC';

	EXECUTE sp_executesql
            @sql,
            @ParamDefinition,
            @yieldOUT = @lastYield OUTPUT,
            @yieldDateOUT = @lastYieldDate OUTPUT

	print 'lastYield: ' +  CONVERT(varchar, @lastYieldDate, 20) + ' | ' + cast(@lastYield AS varchar(100))

	-- SELECT last values of the yield
	SET @sql = 'SELECT TOP 1 @yieldDateOUT = Date_and_Time, @yieldOUT = ' + @yieldUvid + '
				FROM ' + @database + '.dbo.' + @udid + ' 
				WHERE Date_and_Time < CONVERT(datetime, ''' + @starttime + ''', 21) AND ' + @yieldUvid + ' IS NOT NULL
				ORDER BY Date_and_time DESC';
	
	EXECUTE sp_executesql
            @sql,
            @ParamDefinition,
            @yieldOUT = @lastValidYield OUTPUT,
            @yieldDateOUT = @lastValidYieldDate OUTPUT
    
    print 'lastValidYield: ' +  CONVERT(varchar, @lastValidYieldDate, 20) + ' | ' + cast(@lastValidYield AS varchar(100))

    print 'INFO: Test yields for null.'
    
	IF @lastValidYield IS NOT NULL BEGIN
		print 'INFO: last valid yield is not null.'
		
		IF @lastYield is NOT NULL  BEGIN
			print 'INFO: last yield is not null.'
			print 'INFO Comparing yields.'
			IF  @lastValidYield > @lastYield BEGIN
				
				print 'INFO: insert changes into log table: commondb.dbo.log_yield_changes'
				SET @sql = 'USE COMMONDB INSERT INTO commondb.dbo.log_yield_changes 
							(db, udid, date, oldyield, newyield)
							VALUES(''' + @database + ''', 
								   ''' + @udid + ''', 
								   CONVERT(datetime, ''' + @starttime + ''', 21),
								   ' + cast(@lastYield AS varchar(30)) + ',
								   ' + cast(@lastValidYield AS varchar(30)) + ')'
				--print @sql
				EXECUTE sp_executesql
						@sql
				
				print 'WARNING: last valid yield > last yield: ' + cast(@lastValidYield as varchar(30)) + ' > ' +  cast(@lastYield as varchar(30))
				print 'WARNING: last yield will be changed to last valid yield'
				SET @sql = 'USE ' + @database + ' UPDATE ' + @database + '.dbo.' + @udid + ' SET ' + @yieldUvid + ' = ' + cast(@lastValidYield AS varchar(30)) + ' ' +
				           'WHERE Date_and_Time = CONVERT(datetime, ''' + @starttime + ''', 21)'
				print 'INTO: update sql: '
				--print @sql
				EXECUTE sp_executesql
						@sql
		    END
		    ELSE BEGIN 
				print 'INFO last yield not changed.';
			END 
		END
		ELSE BEGIN
			print 'INFO: last yield is null. Exit'
		END
	END
	ELSE BEGIN 
		print 'INFO: last valid yield is null. Exit'
	END
END