CREATE OR ALTER PROC Verified.Pr_AssociationsByMemberId
(
	@IDNumberOrBusinessRegistrationNo VARCHAR(50)
)
--EXEC Verified.Pr_AssociationsByMemberId '5607170813080'
AS
BEGIN 
	SELECT A.AssociationID, A.AssociationName, A.RegionID, R.ProvinceID 
	FROM Verified.Associations A INNER JOIN Verified.AssociationMembers AM
	ON A.AssociationID = AM.AssociationID INNER JOIN Verified.Members M
	ON AM.MemberID = M.MemberID INNER JOIN Md.Regions R 
	ON A.RegionID = R.RegionID 
	WHERE (CASE WHEN ISNULL(@IDNumberOrBusinessRegistrationNo, '') <> '' THEN CASE WHEN M.IDNumberOrBusinessRegistrationNo = @IDNumberOrBusinessRegistrationNo THEN 1 ELSE 0 END ELSE 0 END) = 1
	ORDER BY AssociationName ASC
END
GO

IF NOT EXISTS (SELECT TOP 1 1 FROM [Integration].[DataSources] WHERE DataSourceName = 'MemberAssociations')
BEGIN
	INSERT INTO [Integration].[DataSources](DataSourceName, ConnectionID,CommandTypeID,RequestQuery,EntitySchema,
		IsCacheEnabled,CacheMinutes,IsCancelled, CreatedByID,CreatedDate,UpdatedByID, UpdatedDate)
	VALUES('MemberAssociations', 1, 1,'EXEC Verified.Pr_AssociationsByMemberId @IDNumberOrBusinessRegistrationNo', 
		'<entity name="Associations"><property name="AssociationID" isKey="true" /><property name="AssociationName" isTitle="true" /><property name="ProvinceID" /><property name="RegionID" /></entity>',
		0,0,0,1,'2024-06-30 12:52:21.487',1,'2024-06-30 12:52:21.487')
END
GO