-- =============================================
-- Author:      Gartle LLC
-- Release:     10.0, 2022-07-05
-- Description: The procedure processes changes of usp_web_form_01
-- =============================================

CREATE PROCEDURE [s20].[usp_web_form_01_change]
    @account_id int = NULL
    , @entity_id int = NULL
    , @category_id int = NULL
    , @time_id int = NULL
    , @cell_number_value money = NULL
    , @year int = NULL
AS
BEGIN

SET NOCOUNT ON

DECLARE @user_id int = (SELECT id FROM s20.users WHERE username = USER_NAME())

IF @user_id IS NULL
    BEGIN
    RAISERROR ('No permission', 16, 1)
    RETURN
    END

IF NOT EXISTS (SELECT permission FROM s20.entity_users WHERE entity_id = @entity_id AND user_id = @user_id AND permission = 2)
    BEGIN
    RAISERROR ('No permission', 16, 1)
    RETURN
    END

IF NOT EXISTS (SELECT permission FROM s20.category_users WHERE category_id = @category_id AND user_id = @user_id AND permission = 2)
    BEGIN
    RAISERROR ('No permission', 16, 1)
    RETURN
    END

IF NOT EXISTS (SELECT permission FROM s20.category_times WHERE category_id = @category_id AND time_id = @time_id AND permission = 2)
    BEGIN
    RAISERROR ('Period closed', 16, 1)
    RETURN
    END

SET NOCOUNT OFF;

MERGE s20.facts t
USING (SELECT @account_id, @entity_id, @category_id, @time_id, @cell_number_value) s(
    account_id
    , entity_id
    , category_id
    , time_id
    , value
    ) ON
    t.account_id = s.account_id
    AND t.entity_id = s.entity_id
    AND t.category_id = s.category_id
    AND t.time_id = s.time_id
WHEN MATCHED THEN
    UPDATE
    SET
        value = s.value
WHEN NOT MATCHED THEN
    INSERT (
        account_id
        , entity_id
        , category_id
        , time_id
        , value
        )
    VALUES (
        s.account_id
        , s.entity_id
        , s.category_id
        , s.time_id
        , s.value
        );

END