USE [COMMONDB]
GO
/****** Object:  StoredProcedure [dbo].[REPLACE_INVERTER_BY_SERIAL]    Script Date: 07/24/2019 14:48:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[REPLACE_INVERTER_BY_SERIAL](@database VARCHAR(100), @oldserial VARCHAR(100), @newserial VARCHAR(100))
AS BEGIN

-- Function copies the data of one device to another device of the same structure.
--    OTHER FUNCTION SHOULD IMPLEMENT 
--    rename new device
--      drop table from old device
--      change alarm groups for device


-- @author Milan Rompe
-- @version 1.2
-- @changed

-- 2019-07-24 MR: FIXED: only timestamps will be copied which are not part of the new table already.
-- 2018-09-05 MR: FIXED increased lenghts of paramDefinition 200 characters are not enough.  

SET NOCOUNT ON
DECLARE @sql nvarchar(2000),
        @cursorsql nvarchar(2000),
        @paramDefinition nvarchar(500),
        @old_udid nvarchar(200),
        @new_udid nvarchar(200),
        @insertsql nvarchar(2000),
        @column_name nvarchar(100),
        @device_alias nvarchar(100),
        @device_group nvarchar(100),
        @device_type nvarchar(100),
        @device_ppeak real, 
        @device_pnominal real,
        @ts nvarchar

IF @new_udid=@old_udid BEGIN
    print 'new and old device are equal. exit(1)'
    return
END 

SET @ParamDefinition = '@udidOUT nvarchar(200) OUTPUT'

-- first of all we need to get the data tables of the deivces.    
SET @sql = 'SELECT @udidOUT= table_name FROM ' + @database + '.dbo.xxx_RAW_Device_Properties 
            WHERE DEVICE_VALUE=''' + @oldserial +  ''''
print  @sql
EXECUTE sp_executesql
        @sql,
        @ParamDefinition,
        @udidOUT = @old_udid OUTPUT
        
print 'old device udid: ' + cast(ISNULL(@old_udid, 'NULL') as nvarchar(200))
IF @old_udid IS NULL BEGIN
    print 'OLD device could not be found by serial: ' + cast(ISNULL(@oldserial, 'NULL') as nvarchar)
    print 'function return without changing anything. exit(1)'
    return 
END


SET @sql = 'SELECT @udidOUT=table_name FROM ' + @database + '.dbo.xxx_RAW_Device_Properties 
            WHERE DEVICE_VALUE=''' + @newserial +  ''''
EXECUTE sp_executesql
        @sql,
        @ParamDefinition,
        @udidOUT = @new_udid OUTPUT

print 'new device udid: ' + cast(ISNULL(@new_udid, 'NULL') as nvarchar(200))
IF @new_udid IS NULL BEGIN
    print 'NEW device could not be found by serial: ' + cast(ISNULL(@newserial, 'NULL') as nvarchar)
    print 'function return without changing anything. exit(1)'
    return 
END


print 'check if data there is no crossing timestamps'
SET @ParamDefinition = '@tsOUT nvarchar OUTPUT'
SET @sql = 'SELECT TOP 1 @tsOUT = convert(nvarchar, A.Date_and_Time, 21) FROM ' + @database + '.dbo.' + @old_udid + ' AS A INNER JOIN 
            ' + @database + '.dbo.' + @new_udid +' AS B ON A.Date_and_time=B.Date_and_time' 

print @sql
EXECUTE sp_executesql
        @sql,
        @ParamDefinition,
        @tsOUT = @ts
        

if @ts IS NOT NULL BEGIN
    print 'There are crossing timestamps in devices: ' + @ts + '. exit(1)'
    return 
END 

SET @ParamDefinition = '@aliasOUT nvarchar(100) OUTPUT, 
                        @groupOUT nvarchar(100) OUTPUT,
                        @typeOUT nvarchar(100) OUTPUT,
                        @ppeakOUT real OUTPUT,
                        @pnominalOUT real OUTPUT' 

SET @sql = 'SELECT @aliasOUT=Virtual_Device_Name,
                   @groupOUT=Station_ID,
                   @typeOUT=Device_Key,
                   @ppeakOUT=Fix_Value,
                   @pnominalOUT=Maximum_Value 
            FROM (SELECT TOP 1 Virtual_Device_Name,
                                Station_ID,
                                Device_Key,
                                Fix_Value, 
                                Maximum_Value 
                   FROM ' + @database + '.dbo.xxx_Virtual_Devices 
                   WHERE table_name= ''' + @old_udid + '''
                   AND Device_Class=''P_AC'' 
                   AND Device_Class_2=''P_AC'') AS A'
--print @sql
EXECUTE sp_executesql
        @sql,
        @ParamDefinition,
        @aliasOUT = @device_alias OUTPUT,
        @groupOUT = @device_group OUTPUT,
        @typeOUT = @device_type OUTPUT,
        @ppeakOUT = @device_ppeak OUTPUT, 
        @pnominalOUT = @device_pnominal OUTPUT
        
        
IF @device_alias IS NULL BEGIN
    print 'Device alias for old device could not be found.exit(1)'
    return
END 
print ''
print 'PROCESSING COPY DATA'
print ''


CREATE TABLE #coltbl(
    COLUMN_NAME nvarchar(100) NULL);

-- copy data
SET @cursorsql = 'INSERT INTO #coltbl
                SELECT DISTINCT A.COLUMN_NAME FROM '+ @database + '.INFORMATION_SCHEMA.COLUMNS AS A
                   INNER JOIN '+ @database + '.INFORMATION_SCHEMA.COLUMNS AS B 
                   ON A.TABLE_NAME = ''' + @new_udid + ''' AND B.TABLE_NAME = ''' + @old_udid + ''' AND A.Column_Name=B.column_Name'
EXECUTE sp_executesql
        @cursorsql

DECLARE data_cursor CURSOR LOCAL FORWARD_ONLY FAST_FORWARD FOR
    SELECT COLUMN_NAME FROM #coltbl 

OPEN data_cursor

SET @sql = 'INSERT INTO ' + @database + '.DBO.' + @new_udid + ' ('  

FETCH NEXT FROM data_cursor INTO @column_name
SET @sql = @sql + @column_name
SET @insertsql = @column_name

WHILE @@FETCH_STATUS = 0 BEGIN

    FETCH NEXT FROM data_cursor INTO @column_name
    IF @@FETCH_STATUS = 0 BEGIN
        SET @sql = @sql + ', ' + @column_name
        SET @insertsql = @insertsql + ', ' + @column_name   
    END
END

SET @sql = @sql + ') SELECT ' + @insertsql + ' FROM ' + @database + '.dbo.' + @old_udid + 
           ' WHERE Date_and_time NOT IN (SELECT Date_and_time FROM ' + @database + '.dbo.' + @new_udid + ')'

CLOSE data_cursor
DEALLOCATE data_cursor

print 'copy data from: ' + @old_udid
print 'copy data to: ' + @new_udid 

print 'SQL CMD: ' + ISNULL(@sql, 'NULL')
EXECUTE sp_executesql
        @sql

print ''
print 'PROCESSING RENAMING NEW DEVICE'
print ''

print 'renaming the new device to: ' + ISNULL(@device_alias, 'NULL')

-- update virtual device alias for all values of the device
SET @sql = 'UPDATE '+ @database+ '.dbo.xxx_Virtual_Devices 
            SET Virtual_Device_Name=''' + @device_alias + '''
            WHERE Table_Name=''' + @new_udid + ''''
print @sql
EXECUTE sp_executesql @sql

print ''
print 'update group to:            ' + ISNULL(@device_group, 'NULL')
print 'update device type to:      ' + ISNULL(@device_type, 'NULL')
print 'update ppeak to:            ' + ISNULL(cast(@device_ppeak as nvarchar(100)), 'NULL')
print 'update pnomunal to:         ' + ISNULL(cast(@device_pnominal as nvarchar(100)), 'NULL')


SET @sql = 'UPDATE '+ @database+ '.dbo.xxx_Virtual_Devices 
            SET Station_ID=''' + @device_group + ''',
                Device_Key=''' + @device_type + ''',
                Fix_Value=' + cast(@device_ppeak as nvarchar) + ',
                Maximum_Value=' + cast(@device_pnominal as nvarchar) +' 
            WHERE Table_Name=''' + @new_udid + '''
            AND Device_Class=''P_AC'' 
            AND Device_Class_2=''P_AC'''
print ''
print @sql
EXECUTE sp_executesql @sql

print ''
print 'PROCESSING DELETE OLD DEVICE'
print ''

print 'deleting old virtual device'
SET @sql = 'DELETE FROM ' + @database + '.dbo.xxx_Virtual_Devices WHERE Table_Name=''' + @old_udid + ''''
print @sql
EXECUTE sp_executesql @sql

END
