SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO





-- =============================================
-- Author:		M. Jordaan
-- Create date: 
-- Description:	
-- =============================================
CREATE PROCEDURE [dbo].[SB_AT_K_PLC9_Mesal_Ins]
  @inserted	XML,
  @deleted	XML = NULL
AS
BEGIN

  -- SET NOCOUNT ON added to prevent extra result sets from
  -- interfering with SELECT statements.
  SET NOCOUNT ON;

  --nothing to do?
  IF (@inserted IS NULL) AND (@deleted IS NULL)
    RETURN;

  -- variabelen voor waarden welke net gelogd zijn
  DECLARE @S015_CokeSilo1LV float;
  DECLARE @S016_CokeSilo2LV float;
  DECLARE @S016_CokeSilo3LV float;
  DECLARE @S016_CokeSilo4LV float;
  DECLARE @my_route INTEGER                    -- column RT_SILO
  DECLARE @my_gewicht FLOAT                    -- column AI_29
  DECLARE @S015_CokeSilo1OutputMS_CSUM FLOAT   --column AI_2
  DECLARE @S016_CokeSilo2OutputMS_CSUM FLOAT   --column AI_25
  DECLARE @S016_CokeSilo3OutputMS_CSUM FLOAT   --column AI_26
  DECLARE @S016_CokeSilo4OutputMS_CSUM FLOAT   --column AI_27
  DECLARE @MesalDatumTijd datetime;
  DECLARE @TijdTussenSamples float
  DECLARE @DoCSUM INT = 1;
  DECLARE @NoCSUM INT = 0;
  DECLARE @HistLog INT = -1;
  DECLARE @rc INT;
  DECLARE @i INT;
  DECLARE @trancount INT;

  SET @TijdTussenSamples = 5.0
  SET @trancount = @@TRANCOUNT;

  BEGIN TRY

    -- Create temp table for inserted record(s)
	DECLARE @MASSA_K_PLC9 TABLE (
	  [ID] [INT] IDENTITY(1,1) PRIMARY KEY,
	  [FL_SECTIME] [int] NULL,
	  [LEEG] [float] NULL,
	  [DATUM_TIJD] [char](19) NULL,
	  [AI_1] [float] NULL,
	  [AI_2] [float] NULL,
	  [AI_3] [float] NULL,
	  [AI_4] [float] NULL,
	  [AI_5] [float] NULL,
	  [AI_9] [float] NULL,
	  [AI_10] [float] NULL,
	  [AI_11] [float] NULL,
	  [AI_12] [float] NULL,
	  [AI_13] [float] NULL,
	  [AI_17] [float] NULL,
	  [AI_18] [float] NULL,
	  [AI_19] [float] NULL,
	  [AI_21] [float] NULL,
	  [AI_22] [float] NULL,
	  [AI_24] [float] NULL,
	  [AI_25] [float] NULL,
	  [AI_26] [float] NULL,
	  [AI_27] [float] NULL,
	  [AI_28] [float] NULL,
	  [AI_29] [float] NULL,
	  [AI_53] [float] NULL,
	  [RT_SILO] [int] NULL);

    INSERT INTO @MASSA_K_PLC9
	  ([FL_SECTIME], [LEEG], [DATUM_TIJD], [AI_1], [AI_2], [AI_3], [AI_4], [AI_5],
	   [AI_9], [AI_10], [AI_11], [AI_12], [AI_13], [AI_17], [AI_18], [AI_19], 
	   [AI_21], [AI_22], [AI_24], [AI_25], [AI_26], [AI_27], [AI_28], [AI_29], 
	   [AI_53], [RT_SILO])
        SELECT
           X.value('(row/FL_SECTIME/text())[1]', 'int')      AS FL_SECTIME
          ,X.value('(row/LEEG/text())[1]',       'float')    AS LEEG
          ,X.value('(row/DATUM_TIJD/text())[1]', 'char(19)') AS DATUM_TIJD
          ,X.value('(row/AI_1/text())[1]',       'float')    AS AI_1
          ,X.value('(row/AI_2/text())[1]',       'float')    AS AI_2
          ,X.value('(row/AI_3/text())[1]',       'float')    AS AI_3
          ,X.value('(row/AI_4/text())[1]',       'float')    AS AI_4
          ,X.value('(row/AI_5/text())[1]',       'float')    AS AI_5
          ,X.value('(row/AI_9/text())[1]',       'float')    AS AI_9
          ,X.value('(row/AI_10/text())[1]',      'float')    AS AI_10
          ,X.value('(row/AI_11/text())[1]',      'float')    AS AI_11
          ,X.value('(row/AI_12/text())[1]',      'float')    AS AI_12
          ,X.value('(row/AI_13/text())[1]',      'float')    AS AI_13
          ,X.value('(row/AI_17/text())[1]',      'float')    AS AI_17
          ,X.value('(row/AI_18/text())[1]',      'float')    AS AI_18
          ,X.value('(row/AI_19/text())[1]',      'float')    AS AI_19
          ,X.value('(row/AI_21/text())[1]',      'float')    AS AI_21
          ,X.value('(row/AI_22/text())[1]',      'float')    AS AI_22
          ,X.value('(row/AI_24/text())[1]',      'float')    AS AI_24
          ,X.value('(row/AI_25/text())[1]',      'float')    AS AI_25
          ,X.value('(row/AI_26/text())[1]',      'float')    AS AI_26
          ,X.value('(row/AI_27/text())[1]',      'float')    AS AI_27
          ,X.value('(row/AI_28/text())[1]',      'float')    AS AI_28
          ,X.value('(row/AI_29/text())[1]',      'float')    AS AI_29
          ,X.value('(row/AI_53/text())[1]',      'float')    AS AI_53
          ,X.value('(row/RT_SILO/text())[1]',    'int')      AS RT_SILO
          FROM @inserted.nodes('inserted') AS T(X);

    --bewaar het aantal opgeslagen regels in de tijdelijke tabel
    SET @rc = @@ROWCOUNT; --SCOPE_IDENTITY() would also work

    IF (@rc = 0)
	  THROW 50002, 'Logging into table variable failed', 1;
  END TRY

  BEGIN CATCH

	-- rethrow the error
	THROW;
  END CATCH
  
  -- Assume more then one row can be inserted for a trigger.
  -- haal actuele waarden op, FL logt standaard 1 record per keer
  -- maar kan uit back-log meerdere records in een keer sturen.

  SET @i = 1;

  WHILE @i <= @rc
  BEGIN
	
    BEGIN TRY

      -- fetch one row at a time
      SELECT @S015_CokeSilo1LV = AI_1, 
             @S016_CokeSilo2LV = AI_17,
             @S016_CokeSilo3LV = AI_18,
             @S016_CokeSilo4LV = AI_19,
             @my_route = RT_SILO,
             @my_gewicht = AI_29,
             @S015_CokeSilo1OutputMS_CSUM = AI_2,
             @S016_CokeSilo2OutputMS_CSUM = AI_25,
             @S016_CokeSilo3OutputMS_CSUM = AI_26,
             @S016_CokeSilo4OutputMS_CSUM = AI_27,
             @MesalDatumTijd = CONVERT(datetime, DATUM_TIJD, 104)
             FROM @MASSA_K_PLC9 WHERE [ID] = @i
		     ORDER BY [FL_SECTIME] ASC;
    END TRY

    BEGIN CATCH -- Error handling
      -- Select in temp table failed, record is not processed for MESAL
	  -- update the rowcounter
	  SET @i = @i + 1;
	  CONTINUE;
    END CATCH

    -- update the rowcounter
	SET @i = @i + 1;
	
    BEGIN TRY

      IF @trancount = 0 BEGIN TRANSACTION;
      ELSE SAVE TRANSACTION SB_AT_K_PLC9_Mesal_Ins;

	  --update mesal database, table
      EXEC master.dbo.msp_MesalUpdateTable 'S015_CokeSilo1LV', @S015_CokeSilo1LV, @MesalDatumTijd, @NoCSUM, @HistLog; 
      EXEC master.dbo.msp_MesalUpdateTable 'S016_CokeSilo2LV', @S016_CokeSilo2LV, @MesalDatumTijd, @NoCSUM, @HistLog;
      EXEC master.dbo.msp_MesalUpdateTable 'S016_CokeSilo3LV', @S016_CokeSilo3LV, @MesalDatumTijd, @NoCSUM, @HistLog;
      EXEC master.dbo.msp_MesalUpdateTable 'S016_CokeSilo4LV', @S016_CokeSilo4LV, @MesalDatumTijd, @NoCSUM, @HistLog;

      --log mseal tags voor ingaand materiaal in silo's
      IF @my_route > 0
      BEGIN

        SET @my_gewicht = ((@my_gewicht + ABS(@my_gewicht)) * @TijdTussenSamples) /7200.0

        IF @my_route = 1
          EXEC master.dbo.msp_MesalUpdateTable 'S015_CokeSilo1InputMS_CSUM', @my_gewicht, @MesalDatumTijd, @DoCSUM, @HistLog;

        IF @my_route = 2
          EXEC master.dbo.msp_MesalUpdateTable 'S016_CokeSilo2InputMS_CSUM', @my_gewicht, @MesalDatumTijd, @DoCSUM, @HistLog;

        IF @my_route = 3
          EXEC master.dbo.msp_MesalUpdateTable 'S016_CokeSilo3InputMS_CSUM', @my_gewicht, @MesalDatumTijd, @DoCSUM, @HistLog;

        IF @my_route = 4
          EXEC master.dbo.msp_MesalUpdateTable 'S016_CokeSilo4InputMS_CSUM', @my_gewicht, @MesalDatumTijd, @DoCSUM, @HistLog;
      END

      --log mesal tags voor uitgaand materiaal uit silo's
      SET @S015_CokeSilo1OutputMS_CSUM = ((@S015_CokeSilo1OutputMS_CSUM + ABS(@S015_CokeSilo1OutputMS_CSUM))* @TijdTussenSamples) /7200.0
      EXEC master.dbo.msp_MesalUpdateTable 'S015_CokeSilo1OutputMS_CSUM', @S015_CokeSilo1OutputMS_CSUM, @MesalDatumTijd, @DoCSUM, @HistLog;

      SET @S016_CokeSilo2OutputMS_CSUM = ((@S016_CokeSilo2OutputMS_CSUM + ABS(@S016_CokeSilo2OutputMS_CSUM))* @TijdTussenSamples) /7200.0
      EXEC master.dbo.msp_MesalUpdateTable 'S016_CokeSilo2OutputMS_CSUM', @S016_CokeSilo2OutputMS_CSUM, @MesalDatumTijd, @DoCSUM, @HistLog;

      SET @S016_CokeSilo3OutputMS_CSUM = ((@S016_CokeSilo3OutputMS_CSUM + ABS(@S016_CokeSilo3OutputMS_CSUM))* @TijdTussenSamples) /7200.0
      EXEC master.dbo.msp_MesalUpdateTable 'S016_CokeSilo3OutputMS_CSUM', @S016_CokeSilo3OutputMS_CSUM, @MesalDatumTijd, @DoCSUM, @HistLog;

      SET @S016_CokeSilo4OutputMS_CSUM = ((@S016_CokeSilo4OutputMS_CSUM + ABS(@S016_CokeSilo4OutputMS_CSUM))* @TijdTussenSamples) /7200.0
      EXEC master.dbo.msp_MesalUpdateTable 'S016_CokeSilo4OutputMS_CSUM', @S016_CokeSilo4OutputMS_CSUM, @MesalDatumTijd, @DoCSUM, @HistLog;

      IF @trancount = 0 COMMIT TRANSACTION;
    END TRY

    BEGIN CATCH -- Error handling
	  DECLARE @xstate INT = XACT_STATE();

	  -- Rollback transaction, including mesal update(s)
	  IF @xstate = -1 ROLLBACK TRANSACTION;
	  IF @xstate = 1 AND @trancount = 0 ROLLBACK TRANSACTION;
	  IF @xstate = 1 AND @trancount > 0 ROLLBACK TRANSACTION SB_AT_K_PLC9_Mesal_Ins;

	  THROW; --rethrow the message
    END CATCH
  END --end of while loop
END











GO