USE [interslisko5]
GO
/****** Object:  StoredProcedure [dbo].[csp_afterupdate]    Script Date: 2016-08-24 10:34:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- This procedure is run after each add/update/delete of data in LIME.
-- IMPORTANT: Always avoid cursors if possible!
-- Version 5.2.0

ALTER PROCEDURE [dbo].[csp_afterupdate]
    @@sessionidentifier NVARCHAR(64) = N''
    , @@iduser INT = NULL
    , @@transactionid UNIQUEIDENTIFIER
AS
BEGIN

    -- Variables needed
    DECLARE @recordcount INT
    DECLARE @idrecord INT
    DECLARE @NEW INT
    DECLARE @updated INT
    DECLARE @deleted INT

    -- Table to hold data from the updatelog table
      DECLARE @temptable TABLE
    (
        [TABLE] nvarchar(64),
        [idrecord] INT,
        [NEW] INT,
        [updated] INT,
        [deleted] INT,
        [USER] INT
    )

    -- Find data to work with
    INSERT INTO @temptable([TABLE], [idrecord], [NEW], [updated], [deleted], [USER])
    SELECT [TABLE], [idrecord], [NEW], [updated], [deleted], [iduser]
    FROM updatelog ul
        WHERE transactionid = @@transactionid
        AND ul.[TABLE] IN ('deal')


--  SELECT [table]
    --  , [idrecord]
--      , [new]
    --  , [updated]
    --  , [deleted]
--      , [relation]
--  FROM [updatelog]
--  WHERE [transactionid] = @@transactionid

    -- Start doing stuff if we should
--  SELECT @relevantrecordscount = COUNT(*)
--  FROM @temptable
--  WHERE [table] IN (N'company')       -- ##TODO: Add all tables that there is code for in this procedure

--  IF @relevantrecordscount > 0


    BEGIN

        -- ##TODO: The line below can be removed when adding other code!
        --         Just a place holder since there must be some piece of code between BEGIN and END.
        IF EXISTS
        (
            SELECT 0 FROM @temptable WHERE [TABLE] = 'deal' AND ([NEW] = 1 OR [updated]=1)
        )

            UPDATE c
            SET revenueold =  (SELECT SUM(VALUE) FROM deal b INNER JOIN @temptable t ON  b.iddeal = t.[idrecord] INNER JOIN company c ON c.idcompany = b.company
                                WHERE company = c.idcompany AND b.quotesent >= DateAdd(YEAR, -2, GetDate()) AND b.quotesent < DateAdd(YEAR, -1, GetDate()) AND b.probability = 1)
            , revenuenew = (SELECT SUM(VALUE) FROM deal b INNER JOIN @temptable t ON  b.iddeal = t.[idrecord] INNER JOIN company c ON c.idcompany = b.company
                                WHERE company = c.idcompany AND b.quotesent >= DateAdd(YEAR, -1, GetDate()) AND b.probability = 1)

            FROM company c
            INNER JOIN deal b ON c.idcompany=b.company
            INNER JOIN @temptable t ON b.iddeal= t.[idrecord]
            WHERE t.[TABLE] = 'deal' AND (t.[NEW] = 1 OR t.[updated] =1)
             AND c.[STATUS] = 0
    END
END