SQL

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Version 1.1
-- =============================================
-- Author:    Pavel Lastovka
-- Create date: 2007-07-05
-- Modification date: 2010-02-17 15:15
-- Description:  Update type of curves and variables
--        in local database from the server
-- =============================================
ALTER Procedure [object].[usp_update_export_types] 
  @lIdTypeProductionLine As UniqueIdentifier = Null,
  @lLoggingLevel As TinyInt = Null,
  @lUserId As UniqueIdentifier = Null Output
As
Begin
  Set NoCount On

  -- Default variables
  Declare @lProcName As NVarChar(128)
  Declare @lExecStr As NVarChar(MAX)
  Set @lProcName = N'[object].[usp_update_export_types]'
  
  -- Retrieve the user id
  If @lUserId Is Null
  Begin
    Begin Try
      Select Top 1
        @lUserId = [id_user]
      From
        [core].[user]
      Where
        [identifier] = N'system'
    End Try
    Begin Catch
      Print N'Exp: Cannot obtain a system user - ' + ERROR_MESSAGE()
      Return -1
    End Catch
    If @lUserId Is Null
    Begin
      Print N'Err: Cannot obtain a system user - the account doesn''t exist in the system'
      Return -1
    End
  End
  
  -- Obtain parameters
  If @lLoggingLevel Is Null
  Begin
    Begin Try
      Exec [core].[csp_get_param] @lName = N'LoggingLevel', @lGroupName = N'Local',
        @lDefaultValue = N'2', @lValue = @lExecStr Output, @lDataType = N'TinyInt',
        @lUserId = @lUserId
      Set @lLoggingLevel = Cast (@lExecStr As TinyInt)
    End Try
    Begin Catch
      Exec [core].[csp_write_to_log] @lLoggingLevel = 2, @lType = N'Exp', @lProcName = @lProcName,
        @lDescription = N'Cannot retrieve parameter LoggingLevel', @lUserId = @lUserId
      Set @lLoggingLevel = 2
    End Catch
  End
  
  -- Updating users
  Exec [core].[csp_write_to_log] @lLoggingLevel = @lLoggingLevel, @lType = N'Msg', @lProcName = @lProcName,
    @lDescription = N'Updating users', @lUserId = @lUserId
  
  Begin Try
    Delete From
      [core].[user]
    Where
      [id_user] Not In
      (
        Select
          [id_core_user]
        From
          [MIXCS].[dbo].[core_user]
      )
  End Try
  Begin Catch
    Exec [core].[csp_write_to_log] @lLoggingLevel = @lLoggingLevel, @lType = N'Exp', @lProcName = @lProcName,
      @lDescription = N'Cannot delete users', @lUserId = @lUserId
  End Catch
  
  Begin Try
    Update
      [core].[user]
    Set
      [identifier] = [mixcs_user].[identifier],
      [full_name] = [mixcs_user].[full_name],
      [password] = [mixcs_user].[password],
      [email] = [mixcs_user].[email],
      [x_user] = [mixcs_user].[x_user],
      [x_created] = [mixcs_user].[x_created],
      [x_modified] = GetUTCDate()
    From
      [core].[user] As [mixit_user]
    Inner Join
      [MIXCS].[dbo].[core_user] As [mixcs_user]
    On
      [mixit_user].[id_user] = [mixcs_user].[id_core_user]
  End Try
  Begin Catch
    Exec [core].[csp_write_to_log] @lLoggingLevel = @lLoggingLevel, @lType = N'Exp', @lProcName = @lProcName,
      @lDescription = N'Cannot update users', @lUserId = @lUserId
  End Catch
  
  Begin Try
    Insert Into
      [core].[user]
    (
      [id_user],
      [identifier],
      [full_name],
      [password],
      [email],
      [x_user],
      [x_created],
      [x_modified]
    )
    Select
      [id_core_user] As [id_user],
      [identifier] As [identifier],
      [full_name] As [full_name],
      [password] As [password],
      [email] As [email],
      [x_user] As [x_user],
      [x_created] As [x_created],
      GetUTCDate() As [x_modified]
    From
      [MIXCS].[dbo].[core_user]
    Where
      [id_core_user] Not In
      (
        Select
          [id_user]
        From
          [core].[user]
      )
  End Try
  Begin Catch
    Exec [core].[csp_write_to_log] @lLoggingLevel = @lLoggingLevel, @lType = N'Exp', @lProcName = @lProcName,
      @lDescription = N'Cannot insert a new user into the database', @lUserId = @lUserId
  End Catch
  
  Begin Try
    If Not Exists(Select [id_user] From [core].[user] Where [id_user] = @lUserId)
    Begin
      Select Top 1
        @lUserId = [id_user]
      From
        [core].[user]
      Where
        [identifier] = N'system'
    End
  End Try
  Begin Catch
    Exec [core].[csp_write_to_log] @lLoggingLevel = @lLoggingLevel, @lType = N'Exp', @lProcName = @lProcName,
      @lDescription = N'Cannot update an user id', @lUserId = @lUserId
  End Catch
  
  -- Update curve types
  Exec [core].[csp_write_to_log] @lLoggingLevel = @lLoggingLevel, @lType = N'Msg', @lProcName = @lProcName,
    @lDescription = N'Updating curve types', @lUserId = @lUserId
    
  Begin Try
    Set @lExecStr =
N'Delete From
  [type].[type_curve]
Where' +
  Case When
    @lIdTypeProductionLine Is Not Null
  Then
    N'
  [id_type_production_line] = N''' + Cast(@lIdTypeProductionLine As NVarChar(64)) + N'''
  And '
  Else
    N'
'
  End +
    N'[id_type_curve] Not In
  (
    Select
      [id_type_curve]
    From
      [MIXCS].[dbo].[type_curve]' +
    Case When
      @lIdTypeProductionLine Is Not Null
    Then
      N'
    Where
      [id_type_production_line] = N''' + Cast(@lIdTypeProductionLine As NVarChar(64)) + N''''
    Else
      N''
    End +
      N'
  )'
    Exec(@lExecStr)
  End Try
  Begin Catch
    Exec [core].[csp_write_to_log] @lLoggingLevel = @lLoggingLevel, @lType = N'Exp', @lProcName = @lProcName,
      @lDescription = N'Cannot delete curve types', @lUserId = @lUserId
  End Catch
  
  -- Update variable types
  Exec [core].[csp_write_to_log] @lLoggingLevel = @lLoggingLevel, @lType = N'Msg', @lProcName = @lProcName,
    @lDescription = N'Updating variable types', @lUserId = @lUserId
    
  Begin Try
    Set @lExecStr =
N'Delete From
  [type].[type_variable]
Where' +
  Case When
    @lIdTypeProductionLine Is Not Null
  Then
    N'
  [id_type_production_line] = N''' + Cast(@lIdTypeProductionLine As NVarChar(64)) + N'''
  And '
  Else
    N'
'
  End +
N'[id_type_variable] Not In
  (
    Select
      [id_type_variable]
    From
      [MIXCS].[dbo].[type_variable]' +
    Case When
      @lIdTypeProductionLine Is Not Null
    Then
      N'
    Where
      [id_type_production_line] = N''' + Cast(@lIdTypeProductionLine As NVarChar(64)) + N''''
    Else
      N''
    End + N'
  )'
    Exec(@lExecStr)
  End Try
  Begin Catch
    Exec [core].[csp_write_to_log] @lLoggingLevel = @lLoggingLevel, @lType = N'Exp', @lProcName = @lProcName,
      @lDescription = N'Cannot delete variable types', @lUserId = @lUserId
  End Catch
  
  -- Update production lines
  Exec [core].[csp_write_to_log] @lLoggingLevel = @lLoggingLevel, @lType = N'Msg', @lProcName = @lProcName,
    @lDescription = N'Updating production line types', @lUserId = @lUserId
    
  Begin Try
    Set @lExecStr = 
N'Delete From
  [type].[type_production_line]
Where
  [id_type_production_line] Not In
  (
    Select
      [id_type_production_line]
    From
      [MIXCS].[dbo].[type_production_line] ' +
      Case When
        @lIdTypeProductionLine Is Not Null
      Then
        N'
    Where
      [id_type_production_line] = N''' + Cast(@lIdTypeProductionLine As NVarChar(64)) + N'''
  )
  And [id_type_production_line] = N''' + Cast(@lIdTypeProductionLine As NVarChar(64)) + N''''
      Else
        N'
  )'
      End
    Exec(@lExecStr)
  End Try
  Begin Catch
    Exec [core].[csp_write_to_log] @lLoggingLevel = @lLoggingLevel, @lType = N'Exp', @lProcName = @lProcName,
      @lDescription = N'Cannot delete production line types', @lUserId = @lUserId
  End Catch
  
  Begin Try
    Set @lExecStr =
N'Update
  [type].[type_production_line]
Set
  [identifier] = [mixcs_line].[identifier],
  [identification] = [mixcs_line].[identification],
  [x_user] = N''' + Cast(@lUserId As NVarChar(64)) + N''',
  [x_modified] = GetUTCDate()
From
  [type].[type_production_line] As [mixit_line]
Inner Join
  [MIXCS].[dbo].[type_production_line] As [mixcs_line]
On
  [mixcs_line].[id_type_production_line] = [mixit_line].[id_type_production_line]' +
  Case When
    @lIdTypeProductionLine Is Not Null
  Then
    N'
  And [mixcs_line].[id_type_production_line] = N''' + Cast(@lIdTypeProductionLine As NVarChar(64)) + N''''
  Else
    N''
  End
    Exec(@lExecStr)
  End Try
  Begin Catch
    Exec [core].[csp_write_to_log] @lLoggingLevel = @lLoggingLevel, @lType = N'Exp', @lProcName = @lProcName,
      @lDescription = N'Cannot update production line types', @lUserId = @lUserId
  End Catch
  
  Begin Try
    Set @lExecStr =
N'Insert Into
  [type].[type_production_line]
(
  [id_type_production_line],
  [identifier],
  [identification],
  [x_user],
  [x_created]
)
Select
  [id_type_production_line] As [id_type_production_line],
  [identifier] As [identifier],
  [identification] As [identification],
  N''' + Cast(@lUserId As NVarChar(64)) + N''' As [x_user],
  [x_created] As [x_created]
From
  [MIXCS].[dbo].[type_production_line]
Where
  [id_type_production_line] Not In
  (
    Select
      [id_type_production_line]
    From
      [type].[type_production_line]' +
    Case When
      @lIdTypeProductionLine Is Not Null
    Then
      N'
    Where
      [id_type_production_line] = N''' + Cast(@lIdTypeProductionLine As NVarChar(64)) + N'''
  )
  And [id_type_production_line] = N''' + Cast(@lIdTypeProductionLine As NVarChar(64)) + N''''
    Else
  N'
  )'
    End
    Exec(@lExecStr)
  End Try
  Begin Catch
    Exec [core].[csp_write_to_log] @lLoggingLevel = @lLoggingLevel, @lType = N'Exp', @lProcName = @lProcName,
      @lDescription = N'Cannot insert production line types', @lUserId = @lUserId
  End Catch

  -- Update curve types
  Exec [core].[csp_write_to_log] @lLoggingLevel = @lLoggingLevel, @lType = N'Msg', @lProcName = @lProcName,
    @lDescription = N'Updating curve types', @lUserId = @lUserId
    
  Begin Try
    Set @lExecStr =
N'Update
  [type].[type_curve]
Set
  [tag_id] = [mixcs_curve].[tag_index],
  [identifier] = [mixcs_curve].[identifier],
  [scale_factor] = [mixcs_curve].[scale_factor],
  [id_type_production_line] = [mixcs_curve].[id_type_production_line],
  [x_user] = N''' + Cast(@lUserId As NVarChar(64)) + N''',
  [x_modified] = GetUTCDate()
From
  [type].[type_curve] As [mixit_curve]
Inner Join
  [MIXCS].[dbo].[type_curve] As [mixcs_curve]
On
  [mixcs_curve].[id_type_curve] = [mixit_curve].[id_type_curve]' +
  Case When
    @lIdTypeProductionLine Is Not Null
  Then
    N'
  And [mixit_curve].[id_type_production_line] = N''' + Cast(@lIdTypeProductionLine As NVarChar(64)) + N''''
  Else
    N''
  End
    Exec(@lExecStr)
  End Try
  Begin Catch
    Exec [core].[csp_write_to_log] @lLoggingLevel = @lLoggingLevel, @lType = N'Exp', @lProcName = @lProcName,
      @lDescription = N'Cannot update curve types', @lUserId = @lUserId
  End Catch

  Begin Try
    Set @lExecStr =
N'Insert Into
  [type].[type_curve]
(
  [id_type_curve],
  [id_type_production_line],
  [tag_id],
  [identifier],
  [scale_factor],
  [x_user],
  [x_created]
)
Select
  [id_type_curve] As [id_type_curve],
  [id_type_production_line] As [id_type_production_line],
  [tag_index] As [tag_id],
  [identifier] As [identifier],
  [scale_factor] As [scale_factor],
  N''' + Cast(@lUserId As NVarChar(64)) + N''' As [x_user],
  [x_created] As [x_created]
From
  [MIXCS].[dbo].[type_curve]
Where
  [id_type_curve] Not In
  (
    Select
      [id_type_curve]
    From
      [type].[type_curve]' +
    Case When
      @lIdTypeProductionLine Is Not Null
    Then
      N'
    Where
      [id_type_production_line] = N''' + Cast(@lIdTypeProductionLine As NVarChar(64)) + N'''
  )
  And [id_type_production_line] = N''' + Cast(@lIdTypeProductionLine As NVarChar(64)) + N''''
    Else
  N'
  )'
    End
    Exec(@lExecStr)
  End Try
  Begin Catch
    Exec [core].[csp_write_to_log] @lLoggingLevel = @lLoggingLevel, @lType = N'Exp', @lProcName = @lProcName,
      @lDescription = N'Cannot insert curve types', @lUserId = @lUserId
  End Catch

  -- Update variable types
  Exec [core].[csp_write_to_log] @lLoggingLevel = @lLoggingLevel, @lType = N'Msg', @lProcName = @lProcName,
    @lDescription = N'Updating variable types', @lUserId = @lUserId
    
  Begin Try
    Set @lExecStr =
N'Update
  [type].[type_variable]
Set
  [tag_id] = [mixcs_variable].[tag_index],
  [identifier] = [mixcs_variable].[identifier],
  [scale_factor] = [mixcs_variable].[scale_factor],
  [id_type_production_line] = [mixcs_variable].[id_type_production_line],
  [coding_system] = [mixcs_variable].[group],
  [x_user] = N''' + Cast(@lUserId As NVarChar(64)) + N''',
  [x_modified] = GetUTCDate()
From
  [type].[type_variable] As [mixit_variable]
Inner Join
  [MIXCS].[dbo].[type_variable] As [mixcs_variable]
On
  [mixcs_variable].[id_type_variable] = [mixit_variable].[id_type_variable]' +
  Case When
    @lIdTypeProductionLine Is Not Null
  Then
    N'
  And [mixcs_variable].[id_type_production_line] = N''' + Cast(@lIdTypeProductionLine As NVarChar(64)) + N''''
  Else
    N''
  End
    Exec(@lExecStr)
  End Try
  Begin Catch
    Exec [core].[csp_write_to_log] @lLoggingLevel = @lLoggingLevel, @lType = N'Exp', @lProcName = @lProcName,
      @lDescription = N'Cannot update variable types', @lUserId = @lUserId
  End Catch

  Begin Try
    Set @lExecStr =
N'Insert Into
  [type].[type_variable]
(
  [id_type_variable],
  [id_type_production_line],
  [tag_id],
  [identifier],
  [scale_factor],
  [coding_system],
  [x_user],
  [x_created]
)
Select
  [id_type_variable] As [id_type_variable],
  [id_type_production_line] As [id_type_production_line],
  [tag_index] As [tag_id],
  [identifier] As [identifier],
  [scale_factor] As [scale_factor],
  [group] As [coding_system],
  N''' + Cast(@lUserId As NVarChar(64)) + N''' As [x_user],
  [x_created] As [x_created]
From
  [MIXCS].[dbo].[type_variable]
Where
  [id_type_variable] Not In
  (
    Select
      [id_type_variable]
    From
      [type].[type_variable]' +
    Case When
      @lIdTypeProductionLine Is Not Null
    Then
      N'
    Where
      [id_type_production_line] = N''' + Cast(@lIdTypeProductionLine As NVarChar(64)) + N'''
  )
  And [id_type_production_line] = N''' + Cast(@lIdTypeProductionLine As NVarChar(64)) + N''''
    Else
  N'
  )'
    End
    Exec(@lExecStr)
  End Try
  Begin Catch
    Exec [core].[csp_write_to_log] @lLoggingLevel = @lLoggingLevel, @lType = N'Exp', @lProcName = @lProcName,
      @lDescription = N'Cannot insert variable types', @lUserId = @lUserId
  End Catch

  Return 1
End