USE [Elyse_DB]
GO
/****** Object:  StoredProcedure [internal].[usp_AUTHENTICATE_cont_file_grp]    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: 04-09-2024
-- Description:	Checks whether the connected user is a member
-- of a given controller file edit 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'.

-- If a file is linked to a controller group then if the connected user is not part of that group
-- then the user is excluded from editing rights.
-- There are two layers of editing permissions.  At one level, controllers are granted exclusive editing
-- permissions by an authoriser, referred to as controller level permission. Other controllers cannot alter those permissions.
-- At the other level controllers grant editing permissions to editors, referred to as editor level permission.  Controller level permissions
-- exclude and override any editor level permission.  A controller cannot delegate permission for a controller level permission. 



/*
COPYRIGHT NOTICE
This database schema and stored procedures are protected by copyright.
Copyright.  Silkwood Software Pty. Ltd. 2024
*/

-- =============================================
CREATE PROCEDURE [internal].[usp_AUTHENTICATE_cont_file_grp] 

	@contr_file_grp_to_check bigint               = NULL,
	@user_authentication_result_cep nvarchar(10) = NULL 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
		@view_permission  nvarchar(10)     = NULL,
		@iscontroller nvarchar(5)          = NULL,
		@sid_id bigint                     = NULL,
		@failure_type nvarchar(1000)       = 'Fail',
		@now datetime2(7)                  = SYSDATETIME(),
		@privilege_name nvarchar(50)       = '';	

  -- Parameters which have been initialised at declaration but not explicitly set might be output as null to calling functions.
  SET @user_authentication_result_cep  = '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.controller_file_group_names
						  WHERE controller_file_group_name_id = @contr_file_grp_to_check)
			  
	  EXEC [internal].[usp_AUTHENTICATE_user_role] 
	  		 @role_to_check = 'Controller',
			 @user_authentication_result = @iscontroller OUTPUT;			  
			 

		IF EXISTS 
			  (SELECT sl.sid_id
				 FROM user_restr.sid_list AS sl
		   INNER JOIN user_restr.controller_file_grp_sid_links AS cdgsl
				   ON      sl.sid_id
					  = cdgsl.sid_id
		   INNER JOIN xref.controller_file_group_names AS cdgn 
				   ON  cdgsl.controller_file_group_name_id
					  = cdgn.controller_file_group_name_id
	 			WHERE cdgsl.controller_file_group_name_id = @contr_file_grp_to_check
				  AND  sl.sid = @usersid 
			      AND (cdgsl.valid_from IS NULL OR cdgsl.valid_from <= @now)
				  AND (cdgsl.valid_until IS NULL OR cdgsl.valid_until >= @now)
  			           )
		     AND @iscontroller = 'Pass'
		 SET @user_authentication_result_cep = 'Pass';
        ELSE	 
		  BEGIN
		    SET @user_authentication_result_cep = 'Fail';
	       -- Write to authorisation fail log
			  IF @iscontroller <> 'Pass'
				SET @failure_type = 'User does not have Controller privileges'
				ELSE 
				 BEGIN
					IF NOT EXISTS 
					  (SELECT sl.sid_id
						 FROM user_restr.sid_list AS sl
				   INNER JOIN user_restr.controller_file_grp_sid_links AS cdgsl
						   ON      sl.sid_id
							  = cdgsl.sid_id
				   INNER JOIN xref.controller_file_group_names AS cdgn 
						   ON  cdgsl.controller_file_group_name_id
							  = cdgn.controller_file_group_name_id
	 					WHERE cdgsl.controller_file_group_name_id = @contr_file_grp_to_check
						  AND  sl.sid = @usersid 
  							   )
						SET @failure_type = 'User is Controller but does not have group rights'
					   ELSE  
						SET @failure_type = 'Authorization registered but not currently valid'
				END
			  INSERT INTO user_restr.authorisation_fail_log
						  (sid_id, privilege_type, privilege_id, privilege_name, stored_procedure, failure_type)
				   VALUES (
							@sid_id,
							'Controller File Group Edit Permission',
							@contr_file_grp_to_check,
							@privilege_name,
							'[internal].[usp_AUTHENTICATE_cont_file_grp]',
							@failure_type
							)
		  END

   
END
GO