-- =============================================
-- Author:      Gartle LLC
-- Release:     10.0, 2022-07-05
-- Description: The procedure updates data of s17.requests
-- =============================================

CREATE PROCEDURE [s17].[usp_request_update]
    @category_id int = NULL
    , @time_id int = NULL
    , @entity_id int = NULL
    , @rownum int = NULL
    , @row_index int = NULL
    , @row_format int = NULL
    , @comment nvarchar(255) = NULL
    , @account_id int = NULL
    , @subaccount_id int = NULL
    , @product_id int = NULL
    , @region_id int = NULL
    , @total float = NULL
    , @01 float = NULL
    , @02 float = NULL
    , @03 float = NULL
    , @04 float = NULL
    , @05 float = NULL
    , @06 float = NULL
    , @07 float = NULL
    , @08 float = NULL
    , @09 float = NULL
    , @10 float = NULL
    , @11 float = NULL
    , @12 float = NULL
    , @row_formulas nvarchar(MAX) = NULL
    , @row_comments nvarchar(MAX) = NULL
    , @transaction_start_time datetime = NULL
    , @is_insert bit = NULL
AS
BEGIN

SET NOCOUNT ON

-- Delete the row at the old row_index if changed

IF COALESCE(@is_insert, 0) = 0
    DELETE FROM s17.requests
    WHERE
        entity_id = @entity_id
        AND category_id = @category_id
        AND time_id = @time_id
        AND row_index = @row_index
        AND NOT row_index = @rownum
        AND transaction_start_time < @transaction_start_time

-- Update the row completely at the new row_index

SET NOCOUNT OFF

UPDATE s17.requests
SET
    @row_index = @rownum
    , row_format = @row_format
    , comment = @comment
    , account_id = @account_id
    , subaccount_id = @subaccount_id
    , product_id = @product_id
    , region_id = @region_id
    , total = COALESCE(@01, 0)
        + COALESCE(@02, 0)
        + COALESCE(@03, 0)
        + COALESCE(@04, 0)
        + COALESCE(@05, 0)
        + COALESCE(@06, 0)
        + COALESCE(@07, 0)
        + COALESCE(@08, 0)
        + COALESCE(@09, 0)
        + COALESCE(@10, 0)
        + COALESCE(@11, 0)
        + COALESCE(@12, 0)
    , [01] = @01
    , [02] = @02
    , [03] = @03
    , [04] = @04
    , [05] = @05
    , [06] = @06
    , [07] = @07
    , [08] = @08
    , [09] = @09
    , [10] = @10
    , [11] = @11
    , [12] = @12
    , row_formulas = @row_formulas
    , row_comments = @row_comments
    , transaction_start_time = @transaction_start_time
WHERE
    entity_id = @entity_id
    AND category_id = @category_id
    AND time_id = @time_id
    AND row_index = @rownum

-- Insert a new non-empty row if the update command fails

IF @@ROWCOUNT = 0
    IF COALESCE(@is_insert, 0) = 1
        OR @comment IS NOT NULL
        OR @account_id IS NOT NULL
        OR @subaccount_id IS NOT NULL
        OR @product_id IS NOT NULL
        OR @region_id IS NOT NULL
        OR @01 IS NOT NULL
        OR @02 IS NOT NULL
        OR @03 IS NOT NULL
        OR @04 IS NOT NULL
        OR @05 IS NOT NULL
        OR @06 IS NOT NULL
        OR @07 IS NOT NULL
        OR @08 IS NOT NULL
        OR @09 IS NOT NULL
        OR @10 IS NOT NULL
        OR @11 IS NOT NULL
        OR @12 IS NOT NULL
        INSERT INTO s17.requests (
            entity_id
            , category_id
            , time_id
            , row_index
            , row_format
            , comment
            , account_id
            , subaccount_id
            , product_id
            , region_id
            , total
            , [01]
            , [02]
            , [03]
            , [04]
            , [05]
            , [06]
            , [07]
            , [08]
            , [09]
            , [10]
            , [11]
            , [12]
            , row_formulas
            , row_comments
            , transaction_start_time
            )
        VALUES (
            @entity_id
            , @category_id
            , @time_id
            , @rownum
            , @row_format
            , @comment
            , @account_id
            , @subaccount_id
            , @product_id
            , @region_id
            , COALESCE(@01, 0)
                + COALESCE(@02, 0)
                + COALESCE(@03, 0)
                + COALESCE(@04, 0)
                + COALESCE(@05, 0)
                + COALESCE(@06, 0)
                + COALESCE(@07, 0)
                + COALESCE(@08, 0)
                + COALESCE(@09, 0)
                + COALESCE(@10, 0)
                + COALESCE(@11, 0)
                + COALESCE(@12, 0)
            , @01
            , @02
            , @03
            , @04
            , @05
            , @06
            , @07
            , @08
            , @09
            , @10
            , @11
            , @12
            , @row_formulas
            , @row_comments
            , @transaction_start_time
            )

END