USE [Elyse_DB]
GO
/****** Object:  StoredProcedure [internal].[usp_AUTHENTICATE_doc_grp_ed_pm]    Script Date: Sat 05-09-2026 7:01:55 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Silkwood Software
-- Create date: 18-12-2023
-- Description:	Checks whether the connected user has edit permission
-- for a given document group. 
-- Also returns fail if the id does not exist.
-- Input is a the id of the document group to be checked.  
-- Output is a status string of 'Pass' or 'Fail'.
/*
COPYRIGHT NOTICE
This database schema and stored procedures are protected by copyright.
Copyright.  Silkwood Software Pty. Ltd. 2023
*/
-- ===============================================

CREATE PROCEDURE [internal].[usp_AUTHENTICATE_doc_grp_ed_pm] 

	@doc_grp_to_check_ep bigint = NULL,
	@user_authentication_reslt_dgep nvarchar(10) OUTPUT


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

  DECLARE 
  		@usersid varbinary(100)            = NULL, -- The SID of the connected user
		@sid_id bigint                     = NULL,
		@failure_type nvarchar(1000)       = NULL,
		@now datetime2(7)                  = SYSDATETIME(),
		@privilege_name nvarchar(50)       = '',
        @user_authentication_reslt_dgep_udg nvarchar(10) = '';
 
   SET @user_authentication_reslt_dgep = 'Fail';

   SET @usersid = SUSER_SID(ORIGINAL_LOGIN()); -- Read the SID of the connected user

  		SELECT @sid_id = sid_id
          FROM user_restr.sid_list
         WHERE sid = @usersid;
	
  SET @privilege_name = (SELECT attr_name
                           FROM xref.doc_group_names
						  WHERE doc_group_id = @doc_grp_to_check_ep)
			  

  -- To have edit permission for a document the user must first have view permission for that document
  -- Edit permissions work differently from view permissions.  With view permissions a document
  -- is viewable by default.  With edit permissions the document is restricted by default. 
  -- A user has edit permission for a document if there is a chain from the document ID through
  -- either a function list or a people list to a sid in the sid list which is the sid for the user.

  -- Check if the user has view permission for the document group.

	  EXEC [internal].[usp_AUTHENTICATE_user_doc_grp] 
		@docgroupid_to_check_vp = @doc_grp_to_check_ep,
		@user_authentication_result = @user_authentication_reslt_dgep_udg OUTPUT

        SET @user_authentication_reslt_dgep = @user_authentication_reslt_dgep_udg;


      -- Check if the user is linked to the document group through either a function list or a people list
			  IF  @user_authentication_reslt_dgep = 'Pass'
			      AND

			        (EXISTS (SELECT df.function_id AS dfid
					           FROM xref.doc_group_names AS dgn
						  LEFT JOIN user_restr.doc_group_edit_perm_funct_lst AS dgepfl -- Check for membership of a duty function list
						            ON      dgn.doc_group_id
									   = dgepfl.doc_group_id
						  LEFT JOIN people.function_list_names AS fln
						            ON dgepfl.function_list_id
									   =  fln.function_list_id
						  LEFT JOIN people.function_lists AS fl
						            ON  fln.function_list_id
									   = fl.function_list_id
						  LEFT JOIN people.duty_functions AS df
						            ON   fl.function_id
									   = df.function_id
						  LEFT JOIN people.duty_function_sid_links AS dfsl
						            ON  dfsl.function_id
									   =  df.function_id
						  LEFT JOIN user_restr.sid_list AS sl
									ON dfsl.sid_id
									   = sl.sid_id
							  WHERE dgn.doc_group_id = @doc_grp_to_check_ep
									 AND sl.sid = @usersid
									 -- Check time-bound restrictions
									 AND (dgepfl.valid_from IS NULL OR dgepfl.valid_from <= @now)
									 AND (dgepfl.valid_until IS NULL OR dgepfl.valid_until >= @now)
									 AND (fl.valid_from IS NULL OR fl.valid_from <= @now)
									 AND (fl.valid_until IS NULL OR fl.valid_until >= @now)
									 AND (dfsl.valid_from IS NULL OR dfsl.valid_from <= @now)
									 AND (dfsl.valid_until IS NULL OR dfsl.valid_until >= @now)
                     UNION ALL

					        (SELECT pl.sid_id AS sid
					           FROM xref.doc_group_names AS dgn						            
						  LEFT JOIN user_restr.doc_group_edit_perm_people_lst AS dgeppl -- Check for membership of a people list
						            ON      dgn.doc_group_id
									   = dgeppl.doc_group_id
						  LEFT JOIN people.people_list_names AS pln
						            ON dgeppl.people_list_id
									   =  pln.people_list_id
						  LEFT JOIN people.people_lists AS pl
						            ON  pln.people_list_id
									   = pl.people_list_id
						  LEFT JOIN user_restr.sid_list AS sl
									ON   pl.sid_id
									   = sl.sid_id
							  WHERE dgn.doc_group_id = @doc_grp_to_check_ep
									 AND sl.sid = @usersid
									 -- Check time-bound restrictions
									 AND (dgeppl.valid_from IS NULL OR dgeppl.valid_from <= @now)
									 AND (dgeppl.valid_until IS NULL OR dgeppl.valid_until >= @now)
									 AND (pl.valid_from IS NULL OR pl.valid_from <= @now)
									 AND (pl.valid_until IS NULL OR pl.valid_until >= @now)
  							         ))) 
					BEGIN  -- The user has permission for this action
					  SET @user_authentication_reslt_dgep = 'Pass';
					END
				ELSE -- The user does not have permission for this action. 
				  BEGIN
					SET @user_authentication_reslt_dgep = 'Fail';
--  ============================================================================================
                   -- Authorisation failure diagnostics
				   -- Write to authorisation fail log
	
						SET @failure_type = 'User does not have edit privileges for the document group'

                            BEGIN

                                DECLARE
                                    @view_failure     nvarchar(1000) = NULL,
                                    @function_failure nvarchar(1000) = NULL,
                                    @people_failure   nvarchar(1000) = NULL;


                                -- ============================================================
                                -- VIEW PERMISSION
                                -- ============================================================

                                IF @user_authentication_reslt_dgep_udg <> 'Pass'
                                BEGIN
                                    SET @view_failure = 'View: Permission Failed';
                                END;


                                -- ============================================================
                                -- FUNCTION LIST PATH
                                --
                                -- Function path is:
                                --
                                --   Function-list authorization
                                --   AND Function-list validity
                                --   AND SID authorization
                                --   AND SID authorization validity
                                --
                                -- We diagnose the path independently of the authorization
                                -- decision above.
                                -- ============================================================

                                -- Document group -> Function List authorization exists
                                IF NOT EXISTS
                                (
                                    SELECT 1
                                    FROM user_restr.doc_group_edit_perm_funct_lst AS dgepfl
                                    WHERE dgepfl.doc_group_id = @doc_grp_to_check_ep
                                )
                                BEGIN
                                    SET @function_failure =
                                        CONCAT_WS('; ',
                                            @function_failure,
                                            'Function Path: Authorization Not Found'
                                        );
                                END
                                ELSE
                                BEGIN

                                    -- Document group -> Function List authorization is currently valid
                                    IF NOT EXISTS
                                    (
                                        SELECT 1
                                        FROM user_restr.doc_group_edit_perm_funct_lst AS dgepfl
                                        WHERE dgepfl.doc_group_id = @doc_grp_to_check_ep
                                          AND (dgepfl.valid_from IS NULL OR dgepfl.valid_from <= @now)
                                          AND (dgepfl.valid_until IS NULL OR dgepfl.valid_until >= @now)
                                    )
                                    BEGIN
                                        SET @function_failure =
                                            CONCAT_WS('; ',
                                                @function_failure,
                                                'Function Path: Authorization Not Currently Valid'
                                            );
                                    END;


                                    -- Function List exists
                                    IF NOT EXISTS
                                    (
                                        SELECT 1
                                        FROM user_restr.doc_group_edit_perm_funct_lst AS dgepfl
                                        INNER JOIN people.function_list_names AS fln
                                            ON dgepfl.function_list_id = fln.function_list_id
                                        INNER JOIN people.function_lists AS fl
                                            ON fln.function_list_id = fl.function_list_id
                                        WHERE dgepfl.doc_group_id = @doc_grp_to_check_ep
                                    )
                                    BEGIN
                                        SET @function_failure =
                                            CONCAT_WS('; ',
                                                @function_failure,
                                                'Function Path: Function List Not Found'
                                            );
                                    END
                                    ELSE
                                    BEGIN

                                        -- Function List is currently valid
                                        IF NOT EXISTS
                                        (
                                            SELECT 1
                                            FROM user_restr.doc_group_edit_perm_funct_lst AS dgepfl
                                            INNER JOIN people.function_list_names AS fln
                                                ON dgepfl.function_list_id = fln.function_list_id
                                            INNER JOIN people.function_lists AS fl
                                                ON fln.function_list_id = fl.function_list_id
                                            WHERE dgepfl.doc_group_id = @doc_grp_to_check_ep
                                              AND (fl.valid_from IS NULL OR fl.valid_from <= @now)
                                              AND (fl.valid_until IS NULL OR fl.valid_until >= @now)
                                        )
                                        BEGIN
                                            SET @function_failure =
                                                CONCAT_WS('; ',
                                                    @function_failure,
                                                    'Function Path: Function List Not Currently Valid'
                                                );
                                        END;


                                        -- User has a SID link through the function
                                        IF NOT EXISTS
                                        (
                                            SELECT 1
                                            FROM user_restr.doc_group_edit_perm_funct_lst AS dgepfl
                                            INNER JOIN people.function_list_names AS fln
                                                ON dgepfl.function_list_id = fln.function_list_id
                                            INNER JOIN people.function_lists AS fl
                                                ON fln.function_list_id = fl.function_list_id
                                            INNER JOIN people.duty_functions AS df
                                                ON fl.function_id = df.function_id
                                            INNER JOIN people.duty_function_sid_links AS dfsl
                                                ON df.function_id = dfsl.function_id
                                            INNER JOIN user_restr.sid_list AS sl
                                                ON dfsl.sid_id = sl.sid_id
                                            WHERE dgepfl.doc_group_id = @doc_grp_to_check_ep
                                              AND sl.sid = @usersid
                                        )
                                        BEGIN
                                            SET @function_failure =
                                                CONCAT_WS('; ',
                                                    @function_failure,
                                                    'Function Path: User SID Not Linked Through Duty Function'
                                                );
                                        END
                                        ELSE
                                        BEGIN

                                            -- User's SID link is currently valid
                                            IF NOT EXISTS
                                            (
                                                SELECT 1
                                                FROM user_restr.doc_group_edit_perm_funct_lst AS dgepfl
                                                INNER JOIN people.function_list_names AS fln
                                                    ON dgepfl.function_list_id = fln.function_list_id
                                                INNER JOIN people.function_lists AS fl
                                                    ON fln.function_list_id = fl.function_list_id
                                                INNER JOIN people.duty_functions AS df
                                                    ON fl.function_id = df.function_id
                                                INNER JOIN people.duty_function_sid_links AS dfsl
                                                    ON df.function_id = dfsl.function_id
                                                INNER JOIN user_restr.sid_list AS sl
                                                    ON dfsl.sid_id = sl.sid_id
                                                WHERE dgepfl.doc_group_id = @doc_grp_to_check_ep
                                                  AND sl.sid = @usersid
                                                  AND (dgepfl.valid_from IS NULL OR dgepfl.valid_from <= @now)
                                                  AND (dgepfl.valid_until IS NULL OR dgepfl.valid_until >= @now)
                                                  AND (fl.valid_from IS NULL OR fl.valid_from <= @now)
                                                  AND (fl.valid_until IS NULL OR fl.valid_until >= @now)
                                                  AND (dfsl.valid_from IS NULL OR dfsl.valid_from <= @now)
                                                  AND (dfsl.valid_until IS NULL OR dfsl.valid_until >= @now)
                                            )
                                            BEGIN
                                                SET @function_failure =
                                                    CONCAT_WS('; ',
                                                        @function_failure,
                                                        'Function Path: Duty Function SID Link Not Currently Valid'
                                                    );
                                            END;

                                        END;
                                    END;
                                END;


                                -- ============================================================
                                -- PEOPLE LIST PATH
                                --
                                -- People path is:
                                --
                                --   People-list authorization
                                --   AND People-list validity
                                --   AND SID membership
                                --   AND membership validity
                                --
                                -- Again, diagnosed independently of the authorization decision.
                                -- ============================================================

                                -- Document group -> People List authorization exists
                                IF NOT EXISTS
                                (
                                    SELECT 1
                                    FROM user_restr.doc_group_edit_perm_people_lst AS dgeppl
                                    WHERE dgeppl.doc_group_id = @doc_grp_to_check_ep
                                )
                                BEGIN
                                    SET @people_failure =
                                        CONCAT_WS('; ',
                                            @people_failure,
                                            'People Path: Authorization Not Found'
                                        );
                                END
                                ELSE
                                BEGIN

                                    -- Document group -> People List authorization is currently valid
                                    IF NOT EXISTS
                                    (
                                        SELECT 1
                                        FROM user_restr.doc_group_edit_perm_people_lst AS dgeppl
                                        WHERE dgeppl.doc_group_id = @doc_grp_to_check_ep
                                          AND (dgeppl.valid_from IS NULL OR dgeppl.valid_from <= @now)
                                          AND (dgeppl.valid_until IS NULL OR dgeppl.valid_until >= @now)
                                    )
                                    BEGIN
                                        SET @people_failure =
                                            CONCAT_WS('; ',
                                                @people_failure,
                                                'People Path: Authorization Not Currently Valid'
                                            );
                                    END;


                                    -- People List exists
                                    IF NOT EXISTS
                                    (
                                        SELECT 1
                                        FROM user_restr.doc_group_edit_perm_people_lst AS dgeppl
                                        INNER JOIN people.people_list_names AS pln
                                            ON dgeppl.people_list_id = pln.people_list_id
                                        INNER JOIN people.people_lists AS pl
                                            ON pln.people_list_id = pl.people_list_id
                                        WHERE dgeppl.doc_group_id = @doc_grp_to_check_ep
                                    )
                                    BEGIN
                                        SET @people_failure =
                                            CONCAT_WS('; ',
                                                @people_failure,
                                                'People Path: People List Not Found'
                                            );
                                    END
                                    ELSE
                                    BEGIN

                                        -- People List is currently valid
                                        IF NOT EXISTS
                                        (
                                            SELECT 1
                                            FROM user_restr.doc_group_edit_perm_people_lst AS dgeppl
                                            INNER JOIN people.people_list_names AS pln
                                                ON dgeppl.people_list_id = pln.people_list_id
                                            INNER JOIN people.people_lists AS pl
                                                ON pln.people_list_id = pl.people_list_id
                                            WHERE dgeppl.doc_group_id = @doc_grp_to_check_ep
                                              AND (pl.valid_from IS NULL OR pl.valid_from <= @now)
                                              AND (pl.valid_until IS NULL OR pl.valid_until >= @now)
                                        )
                                        BEGIN
                                            SET @people_failure =
                                                CONCAT_WS('; ',
                                                    @people_failure,
                                                    'People Path: People List Not Currently Valid'
                                                );
                                        END;


                                        -- User is a member of the People List
                                        IF NOT EXISTS
                                        (
                                            SELECT 1
                                            FROM user_restr.doc_group_edit_perm_people_lst AS dgeppl
                                            INNER JOIN people.people_list_names AS pln
                                                ON dgeppl.people_list_id = pln.people_list_id
                                            INNER JOIN people.people_lists AS pl
                                                ON pln.people_list_id = pl.people_list_id
                                            INNER JOIN user_restr.sid_list AS sl
                                                ON pl.sid_id = sl.sid_id
                                            WHERE dgeppl.doc_group_id = @doc_grp_to_check_ep
                                              AND sl.sid = @usersid
                                        )
                                        BEGIN
                                            SET @people_failure =
                                                CONCAT_WS('; ',
                                                    @people_failure,
                                                    'People Path: People List Membership Not Found'
                                                );
                                        END
                                        ELSE
                                        BEGIN

                                            -- User's People List membership is currently valid
                                            IF NOT EXISTS
                                            (
                                                SELECT 1
                                                FROM user_restr.doc_group_edit_perm_people_lst AS dgeppl
                                                INNER JOIN people.people_list_names AS pln
                                                    ON dgeppl.people_list_id = pln.people_list_id
                                                INNER JOIN people.people_lists AS pl
                                                    ON pln.people_list_id = pl.people_list_id
                                                INNER JOIN user_restr.sid_list AS sl
                                                    ON pl.sid_id = sl.sid_id
                                                WHERE dgeppl.doc_group_id = @doc_grp_to_check_ep
                                                  AND sl.sid = @usersid
                                                  AND (dgeppl.valid_from IS NULL OR dgeppl.valid_from <= @now)
                                                  AND (dgeppl.valid_until IS NULL OR dgeppl.valid_until >= @now)
                                                  AND (pl.valid_from IS NULL OR pl.valid_from <= @now)
                                                  AND (pl.valid_until IS NULL OR pl.valid_until >= @now)
                                            )
                                            BEGIN
                                                SET @people_failure =
                                                    CONCAT_WS('; ',
                                                        @people_failure,
                                                        'People Path: People List Membership Not Currently Valid'
                                                    );
                                            END;

                                        END;
                                    END;
                                END;


                                -- ============================================================
                                -- COMBINE THE DIAGNOSTIC RESULTS
                                -- ============================================================

                                SET @failure_type =
                                    CONCAT_WS('; ',
                                        @view_failure,
                                        @function_failure,
                                        @people_failure
                                    );

                            END -- End authorisation failure diagnostics
--  ============================================================================================
                       -- Write to the log
					  INSERT INTO user_restr.authorisation_fail_log
								  (sid_id, privilege_type, privilege_id, privilege_name, stored_procedure, failure_type)
						   VALUES (
									@sid_id,
									'Document Group Edit Permission',
									@doc_grp_to_check_ep,
									@privilege_name,
									'[internal].[usp_AUTHENTICATE_doc_grp_ed_pm]',
									@failure_type
									)
				  END -- END User does not have permission for this action

	 
END
GO