ALTER PROCEDURE [App].[Pr_Rpt_MembershipReportIncludingOLAS]
    @AssociationID BIGINT,
	@UserID BIGINT    
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @AssociationName VARCHAR(255) = ''
    
    IF @AssociationID <> -1
    BEGIN
        SELECT @AssociationName = AssociationName
        FROM Verified.Associations
        WHERE AssociationID = @AssociationID
    END

    DECLARE @Licences TABLE(Region VARCHAR(255), Association VARCHAR(255), MemberNumber BIGINT, MemberName VARCHAR(255),Initials VARCHAR(255), MemberIDNUmber VARCHAR(255), MemberContactNumber VARCHAR(255), OLAS_OL VARCHAR(255), Legitimate_OL VARCHAR(255), Legitimate_Status VARCHAR(255), Legitimate_DateOfExpiry DATE, Legitimate_Registration VARCHAR(255), Legitimate_AppInProgressReference VARCHAR(20))

    /**************************************************
    Licences migrated to legiti-mate (or in progress)
    **************************************************/
    INSERT INTO @Licences(Region, Association, MemberName,Initials, MemberIDNUmber, MemberContactNumber, OLAS_OL, Legitimate_OL, Legitimate_Status, Legitimate_DateOfExpiry, Legitimate_Registration, Legitimate_AppInProgressReference)
    SELECT R.Description AS Region, VerifiedA.AssociationName AS Association, VerifiedM.FullName,'', VerifiedM.IDNumberOrBusinessRegistrationNo, ISNULL(VerifiedM.TelCell, VerifiedM.TelPhone), OlasL.OperatingLicenceNumber, VerifiedL.OperatingLicenceNumber, Ls.Title AS Status, VerifiedL.DateOfExpiry, VerifiedV.RegistrationNumber, NULL
    FROM Verified.Licences VerifiedL
        INNER JOIN LegitimateKZN_OLAS.dbo.ImportedLicences OlasL ON VerifiedL.LicenceID = OlasL.VerifiedLicenceID
        INNER JOIN Verified.Members VerifiedM ON VerifiedL.MemberID = VerifiedM.MemberID
        INNER JOIN App.ApplicationApplicantDetails Aad ON VerifiedL.ApplicationRequestDetailID = Aad.ApplicationRequestDetailID
        INNER JOIN Verified.Associations VerifiedA ON Aad.VerifiedAssociationID = VerifiedA.AssociationID
        INNER JOIN Md.Regions R ON VerifiedA.RegionID = R.RegionID
        INNER JOIN Verified.LicenceStatus Ls ON VerifiedL.LicenceStatusID = Ls.LicenceStatusID
        LEFT OUTER JOIN Verified.Vehicles VerifiedV ON VerifiedL.VehicleID = VerifiedV.VehicleID
	WHERE VerifiedL.IsLatestIssue = 1
		AND VerifiedL.OperatingLicenceNumber NOT LIKE 'LT%'

    /**************************************************
    Licences only found in legiti-mate
    **************************************************/
    INSERT INTO @Licences(Region, Association, MemberName,Initials, MemberIDNUmber, MemberContactNumber, OLAS_OL, Legitimate_OL, Legitimate_Status, Legitimate_DateOfExpiry, Legitimate_Registration, Legitimate_AppInProgressReference)
    SELECT R.Description AS Region, VerifiedA.AssociationName AS Association, VerifiedM.FullName,'', VerifiedM.IDNumberOrBusinessRegistrationNo, ISNULL(VerifiedM.TelCell, VerifiedM.TelPhone), OlasL.OperatingLicenceNumber, VerifiedL.OperatingLicenceNumber, Ls.Title AS Status, VerifiedL.DateOfExpiry, VerifiedV.RegistrationNumber, NULL
    FROM Verified.Licences VerifiedL
        LEFT JOIN LegitimateKZN_OLAS.dbo.ImportedLicences OlasL ON VerifiedL.LicenceID = OlasL.VerifiedLicenceID
        INNER JOIN Verified.Members VerifiedM ON VerifiedL.MemberID = VerifiedM.MemberID
        INNER JOIN App.ApplicationApplicantDetails Aad ON VerifiedL.ApplicationRequestDetailID = Aad.ApplicationRequestDetailID
        INNER JOIN Verified.Associations VerifiedA ON Aad.VerifiedAssociationID = VerifiedA.AssociationID
        INNER JOIN Md.Regions R ON VerifiedA.RegionID = R.RegionID
        INNER JOIN Verified.LicenceStatus Ls ON VerifiedL.LicenceStatusID = Ls.LicenceStatusID
        LEFT OUTER JOIN Verified.Vehicles VerifiedV ON VerifiedL.VehicleID = VerifiedV.VehicleID
    WHERE OlasL.VerifiedLicenceID IS NULL
		AND VerifiedL.IsLatestIssue = 1 
		AND VerifiedL.OperatingLicenceNumber NOT LIKE 'LT%'

    /**************************************************
    Licences with applications in progress
    **************************************************/
    INSERT INTO @Licences(Region, Association, MemberName,Initials, MemberIDNUmber, MemberContactNumber, OLAS_OL, Legitimate_OL, Legitimate_Status, Legitimate_DateOfExpiry, Legitimate_Registration, Legitimate_AppInProgressReference)
    SELECT ISNULL(R.Description,'UNKNOWN') AS Region, ISNULL(VerifiedA.AssociationName,'UNKNOWN') AS Association, OlasL.MemberName,'', OlasL.MemberIdNumber, NULL, OlasL.OperatingLicenceNumber, NULL, NULL, NULL, NULL, Wi.ReferenceNumber
    FROM LegitimateKZN_OLAS.dbo.ImportedLicences OlasL
        INNER JOIN App.ApplicationRequestDetails Ard ON OlasL.ApplicationRequestDetailIDInProgress = Ard.ApplicationRequestDetailID
        INNER JOIN Wf.WorkflowInstances Wi ON Ard.WorkflowInstanceID = Wi.WorkflowInstanceID
        LEFT JOIN Verified.Associations VerifiedA ON OlasL.VerifiedAssociationID = VerifiedA.AssociationID
        LEFT JOIN Md.Regions R ON VerifiedA.RegionID = R.RegionID
    WHERE VerifiedLicenceID IS NULL 
        AND ApplicationRequestDetailIDInProgress IS NOT NULL
    
	/**************************************************
    Licences only found in OLAS data
    **************************************************/
    INSERT INTO @Licences(Region, Association, MemberName, Initials, MemberIDNUmber, MemberContactNumber, OLAS_OL, Legitimate_OL, Legitimate_Status, Legitimate_DateOfExpiry, Legitimate_Registration, Legitimate_AppInProgressReference)
    SELECT ISNULL(R.Description,'UNKNOWN') AS Region, ISNULL(VerifiedA.AssociationName,'UNKNOWN') AS Association, OlasL.MemberName,'', OlasL.MemberIdNumber, NULL, OlasL.OperatingLicenceNumber, NULL, NULL, NULL, NULL, NULL
    FROM LegitimateKZN_OLAS.dbo.ImportedLicences OlasL
        LEFT JOIN Verified.Associations VerifiedA ON OlasL.VerifiedAssociationID = VerifiedA.AssociationID
        LEFT JOIN Md.Regions R ON VerifiedA.RegionID = R.RegionID
    WHERE VerifiedLicenceID IS NULL 
        AND ApplicationRequestDetailIDInProgress IS NULL
    
	/**************************************************
    Update sequential member number START (OLD)
    **************************************************/
	--DECLARE @IDNumber VARCHAR(100)
	--DECLARE @Counter BIGINT = 1

	--DECLARE idNumbercursor CURSOR FAST_FORWARD READ_ONLY FOR 
	--	SELECT MemberIDNumber
	--	FROM @Licences
	--	WHERE @AssociationName = '' OR Association = @AssociationName
	--	GROUP BY MemberIDNumber
	--OPEN idNumbercursor
	--FETCH NEXT FROM idNumbercursor INTO @IDNumber
	--WHILE @@FETCH_STATUS = 0
	--BEGIN
	--	UPDATE @Licences
	--	SET MemberNumber = @Counter
	--	WHERE MemberIDNUmber = @IDNumber

	--	DECLARE @ContactNumber VARCHAR(255) = NULL
		
	--	SELECT @ContactNumber = MemberContactNumber
	--	FROM @Licences
	--	WHERE MemberIDNUmber = @IDNumber
	--		AND ISNULL(MemberContactNumber,'') <> ''

	--	IF ISNULL(@ContactNumber,'') <> ''
	--	BEGIN
	--		UPDATE @Licences
	--		SET MemberContactNumber = @ContactNumber
	--		WHERE MemberIDNUmber = @IDNumber
	--	END

	--	DECLARE @MemberName VARCHAR(255) = NULL
		
	--	SELECT @MemberName = MemberName
	--	FROM @Licences
	--	WHERE MemberIDNUmber = @IDNumber
	--		AND ISNULL(MemberName,'') <> ''

	--	IF ISNULL(@MemberName,'') <> ''
	--	BEGIN
	--		UPDATE @Licences
	--		SET MemberName = @MemberName
	--		WHERE MemberIDNUmber = @IDNumber
	--	END

	--	UPDATE @Licences
	--	SET Initials = LEFT(@MemberName, LEN(@MemberName) - CHARINDEX(' ',REVERSE(@MemberName)))
	--	WHERE MemberIDNUmber = @IDNumber

	--	UPDATE @Licences
	--	SET MemberName = REVERSE(RTRIM(SUBSTRING(REVERSE(MemberName), 1, CHARINDEX(' ', REVERSE(MemberName)))))
	--	WHERE MemberIDNUmber = @IDNumber
		
	--	SET @Counter = @Counter + 1
	--	FETCH NEXT FROM idNumbercursor INTO @IDNumber
	--END

	--CLOSE idNumbercursor
	--DEALLOCATE idNumbercursor

	--UPDATE @Licences
	--SET Legitimate_OL = NULL
	--WHERE Legitimate_AppInProgressReference IS NOT NULL
	/**************************************************
    Update sequential member number END
    **************************************************/
	
	/**************************************************
    Update sequential member number START (NEW)
    **************************************************/	
	DECLARE @CounterTable TABLE (MemberIDNumber VARCHAR(255), MemberNumber BIGINT, MemberContactNumber VARCHAR(255), MemberName VARCHAR(255));
	
	INSERT INTO @CounterTable (MemberIDNumber, /*MemberNumber,*/ MemberContactNumber, MemberName)
	SELECT 
		MemberIDNumber,
		/*ROW_NUMBER() OVER (ORDER BY MemberName) AS MemberNumber,*/
		MAX(COALESCE(MemberContactNumber, '')) AS MemberContactNumber,
		MAX(COALESCE(MemberName, '')) AS MemberName
	FROM 
		@Licences
	WHERE 
		@AssociationName = '' OR Association = @AssociationName
	GROUP BY 
		MemberIDNumber;

	UPDATE L
	SET 
		--L.MemberNumber = CT.MemberNumber,
		L.MemberContactNumber = COALESCE(L.MemberContactNumber, CT.MemberContactNumber),
		L.MemberName = COALESCE(L.MemberName, CT.MemberName)
	FROM 
		@Licences L
	JOIN 
		@CounterTable CT ON L.MemberIDNumber = CT.MemberIDNumber;
	
	UPDATE L
	SET 
		L.Initials = LEFT(L.MemberName, LEN(L.MemberName) - CHARINDEX(' ', REVERSE(L.MemberName)))
	FROM 
		@Licences L;

	UPDATE L
	SET 
		L.MemberName = REVERSE(RTRIM(SUBSTRING(REVERSE(L.MemberName), 1, CHARINDEX(' ', REVERSE(L.MemberName)))))
	FROM 
		@Licences L;

	WITH RowNumberCTE AS (
		SELECT 
			MemberIDNumber,
			MemberName,
			ROW_NUMBER() OVER (PARTITION BY MemberIDNumber ORDER BY MemberName) AS RowNum
		FROM 
			@Licences
	)
	UPDATE L
	SET 
		L.MemberNumber = R.RowNum
	FROM 
		@Licences L
	JOIN 
		RowNumberCTE R
		ON L.MemberIDNumber = R.MemberIDNumber AND L.MemberName = R.MemberName;

	UPDATE @Licences
	SET Legitimate_OL = NULL
	WHERE Legitimate_AppInProgressReference IS NOT NULL

	/**************************************************
    Update sequential member number END (NEW)
    **************************************************/
	

	/**************************************************
    Return report results
    **************************************************/
	SELECT Region, Association, MemberNumber, UPPER(MemberName) AS MemberName, UPPER(Initials) AS Initials, MemberIDNumber, MemberContactNumber, OLAS_OL, Legitimate_OL, Legitimate_Status, Legitimate_DateOfExpiry, Legitimate_Registration, Legitimate_AppInProgressReference
    FROM @Licences
    WHERE @AssociationName = '' OR Association = @AssociationName
	ORDER BY MemberName, MemberIDNumber
END




GO


