CREATE OR ALTER PROCEDURE [Verified].[Pr_MemberGet]
	@MemberID BIGINT,
	@IDNumberOrBusinessRegistrationNo VARCHAR(50),
	@UserID BIGINT
AS
BEGIN
	SET NOCOUNT ON;

	/********************************************************************************************
	Get user details and perform security checks
	*********************************************************************************************/	
	DECLARE @EntityIDToCheck INT = 1009 /*Members*/
	DECLARE @ItemIDToCheck BIGINT = NULL
	DECLARE @Action VARCHAR(255) = 'Search'
	DECLARE @OfficeLocationID BIGINT, @DivisionTypeID SMALLINT, @ProvinceID SMALLINT, @RegionID SMALLINT, @HasAccess BIT
	
	EXEC App.Pr_GetUserLocationAndSecurity @UserID, @EntityIDToCheck, @ItemIDToCheck, @Action, @OfficeLocationID OUTPUT, @DivisionTypeID OUTPUT, @ProvinceID OUTPUT, @RegionID OUTPUT, @HasAccess OUTPUT

	IF @HasAccess = 0
	BEGIN
		RAISERROR('User location and security error', 16, 1)
		RETURN
	END	
	
	/********************************************************************************************
	Perform search
	*********************************************************************************************/
	SELECT M.MemberID,
           M.ReferenceNumber,
           M.FullName,
           M.RegionID,
           M.IdentityDocumentDocID,
           M.TaxClearanceCertificateDocID,
           M.SurnameOrCompanyName,
           M.FirstNames,
           M.IDTypeID,
           M.IDTypeDescription,
           M.IDNumberOrBusinessRegistrationNo,
           M.PostalAddress,
           M.PostalAddressCode,
           M.PhysicalAddress,
           M.PhysicalAddressCode,
           M.TelPhone,
           M.TelCell,
           M.TelFax,
           M.Email,
           M.SARSIncomeTaxNumber,
           M.TradeName,
           M.MemberStatusID,
           M.OlasID,
           M.LtpsID,
           M.NeoID,
           M.BusinessTypeID,
		   R.ProvinceID,
		   (SELECT TOP 1 AssociationID FROM Verified.AssociationMembers WHERE AssociationMembers.MemberID = M.MemberID) AssociationID
	FROM Verified.Members M INNER JOIN Md.Regions R 
	ON R.RegionID = M.RegionID INNER JOIN Md.Provinces P 
	ON P.ProvinceID = R.ProvinceID
	WHERE 	
	(
		CASE
			WHEN ISNULL(@MemberID,-1) <> -1 AND MemberID = @MemberID THEN 1
			WHEN ISNULL(@MemberID,-1) = -1 AND IDNumberOrBusinessRegistrationNo LIKE @IDNumberOrBusinessRegistrationNo THEN 1
			ELSE 0
		END
	) = 1

END
GO