USE [LegitimateNATIONAL]
GO
/*Associations*/
-- SELECT RegionID,* FROM [Verified].[Associations]
-- DELETE FROM [Verified].[Associations] 
-- DBCC CHECKIDENT ('Verified.Associations', RESEED, 0)
INSERT INTO [Verified].[Associations](AssociationStatusID,AssociationModeID,AssociationTypeID,RegionID,AssociationName,
	AssociationNameAbbrev,RegistrationNumber,PostalAddress,PostalAddressCode,PhysicalAddress,PhysicalAddressCode, PostalAddressIsDomicilium,
	BusRegNumber,TaxNumber,TelNumber,FaxNumber,CellNumber,EmailAddress,ActiveMemberCount,AdjudicationOfficeLocationID,OlasID,LtpsID,NeoID)
SELECT CASE WHEN fkiAssocStatusId = 8 THEN 1 ELSE 2 END,
	(SELECT AssociationModeID FROM Md.AssociationModes WHERE OlasID = fkiModeId),(SELECT AssociationTypeID FROM Md.AssociationTypes WHERE OlasID = fkiAssocTypeId),
	(SELECT TOP 1 RegionID FROM Md.OfficeLocationSetup WHERE ProvinceID = PhysicalAddress.pkiProvinceId),sAssocName,sAssocAbbrevName,sAssocRegistrationNum,PostalAddress.sAddress,LEFT(PostalAddress.sPOCode,4),PhysicalAddress.sAddress,LEFT(PhysicalAddress.sPOCode,4),CASE WHEN DomiciliumAddress.pkiAddressId = PostalAddress.pkiAddressId THEN 1 ELSE 0 END,
	sBusRegistrationNum,sTaxNum,sTelephoneNum,sFaxNum,sCellNum,sEMailAddress,iNumberOfMembers,1,pkiAssocId,NULL,NULL
FROM [OLASJun2023].[dbo].[tblAssociation] LEFT JOIN (SELECT tluProvince.pkiProvinceId, pkiAddressId,sAddressLine1 + CASE WHEN ISNULL(sAddressLine2,'') = '' THEN '' ELSE CHAR(13) + ISNULL(sAddressLine2,'') END + CASE WHEN ISNULL(sAddressLine3,'') = '' THEN '' ELSE CHAR(13) + ISNULL(sAddressLine3,'') END AS sAddress,
		sPOCode,tblAddress.bActive,sProvinceDesc 
	FROM [OLASJun2023].[dbo].[tblAddress] INNER JOIN [OLASJun2023].[dbo].[tluProvince]
	ON tblAddress.fkiProvinceId = tluProvince.pkiProvinceId) PostalAddress
ON tblAssociation.fkiPOAddressId = PostalAddress.pkiAddressId LEFT JOIN (SELECT tluProvince.pkiProvinceId,pkiAddressId,sAddressLine1 + CASE WHEN ISNULL(sAddressLine2,'') = '' THEN '' ELSE CHAR(13) + ISNULL(sAddressLine2,'') END + CASE WHEN ISNULL(sAddressLine3,'') = '' THEN '' ELSE CHAR(13) + ISNULL(sAddressLine3,'') END AS sAddress,
		sPOCode,tblAddress.bActive,sProvinceDesc,sAddressLine2
	FROM [OLASJun2023].[dbo].[tblAddress] INNER JOIN [OLASJun2023].[dbo].[tluProvince]
	ON tblAddress.fkiProvinceId = tluProvince.pkiProvinceId) PhysicalAddress 
ON tblAssociation.fkiPhysicalAddressId = PhysicalAddress.pkiAddressId LEFT JOIN (SELECT tluProvince.pkiProvinceId,pkiAddressId,sAddressLine1 + CASE WHEN ISNULL(sAddressLine2,'') = '' THEN '' ELSE CHAR(13) + ISNULL(sAddressLine2,'') END + CASE WHEN ISNULL(sAddressLine3,'') = '' THEN '' ELSE CHAR(13) + ISNULL(sAddressLine3,'') END AS sAddress,
		sPOCode,tblAddress.bActive,sProvinceDesc 
	FROM [OLASJun2023].[dbo].[tblAddress] INNER JOIN [OLASJun2023].[dbo].[tluProvince]
	ON tblAddress.fkiProvinceId = tluProvince.pkiProvinceId) DomiciliumAddress
ON tblAssociation.fkiAddressDomicilliumId = DomiciliumAddress.pkiAddressId
WHERE pkiAssocId NOT IN (SELECT OlasID FROM Verified.Associations) AND
	pkiAssocId IN (SELECT MAX(pkiAssocId) FROM [OLASJun2023].[dbo].[tblAssociation] GROUP BY sAssocName)
GO

UPDATE [Verified].[Associations]
SET RegionID = (SELECT TOP 1 RegionID FROM Md.OfficeLocationSetup WHERE ProvinceID = PhysicalAddress.pkiProvinceId)
FROM [OLASJun2023].[dbo].[tblAssociation] LEFT JOIN (SELECT CASE WHEN tluProvince.pkiProvinceId = 11 THEN 10 ELSE tluProvince.pkiProvinceId END pkiProvinceId,pkiAddressId,sAddressLine1 + CASE WHEN ISNULL(sAddressLine2,'') = '' THEN '' ELSE CHAR(13) + ISNULL(sAddressLine2,'') END + CASE WHEN ISNULL(sAddressLine3,'') = '' THEN '' ELSE CHAR(13) + ISNULL(sAddressLine3,'') END AS sAddress,
		sPOCode,tblAddress.bActive,sProvinceDesc,sAddressLine2
	FROM [OLASJun2023].[dbo].[tblAddress] INNER JOIN [OLASJun2023].[dbo].[tluProvince]
ON tblAddress.fkiProvinceId = tluProvince.pkiProvinceId) PhysicalAddress
ON tblAssociation.fkiPhysicalAddressId = PhysicalAddress.pkiAddressId
WHERE Associations.OlasID =  pkiAssocId 
GO

-- SELECT * FROM Verified.AssociationMembers
-- DELETE FROM Verified.AssociationMembers
-- DELETE FROM Verified.AssociationRoutes
-- SELECT * FROM OLASJun2023.[dbo].[tlkAssociationMember]
INSERT INTO Verified.AssociationMembers(AssociationID,MemberID)
SELECT DISTINCT VA.AssociationID , VM.MemberID
FROM OLASJun2023.dbo.tblMember M INNER JOIN OLASJun2023.dbo.tblPerson P
on M.fkiPersonId = P.pkiPersonId INNER JOIN OLASJun2023.dbo.tblAddress A
on A.pkiAddressID in (P.fkiPOAddressId, P.fkiPhysicalAddressId) INNER JOIN OLASJun2023.dbo.tlkAssociationMember AM
on M.pkiMemberId = AM.fkiMemberId INNER JOIN OLASJun2023.dbo.tblAssociation AAA
on AAA.pkiassocId = AM.fkiAssocId INNER JOIN Verified.Members VM
ON P.pkiPersonId = VM.OlasID INNER JOIN Verified.Associations VA
ON AAA.pkiAssocId = VA.OlasID LEFT JOIN Verified.AssociationMembers VMA
ON  VM.MemberID = VMA.MemberID AND
	VA.AssociationID =  VMA.AssociationID	
WHERE VMA.MemberID IS NULL
GO

-- DELETE FROM Verified.AssociationRoutes
INSERT INTO Verified.AssociationRoutes(AssociationID,RouteID)
SELECT DISTINCT VA.AssociationID, VR.RouteID
FROM OLASJun2023.dbo.tblAssociation A INNER JOIN OLASJun2023.dbo.tlkHumanEntity HE
ON HE.mfkiRealEntityId = A.pkiAssocId AND fkiEntityTypeId = 2 INNER JOIN OLASJun2023.dbo.tblRasRoute RR
ON HE.pkiEntityId = RR.fkiEntityId  INNER JOIN OLASJun2023.dbo.tblApprovedRoute AR
ON RR.fkiApprovedRouteId = AR.pkiApprovedRouteId INNER JOIN Verified.[Routes] VR
ON AR.pkiApprovedRouteId = VR.OlasID INNER JOIN Verified.Associations VA
ON A.pkiAssocId = VA.OlasID LEFT JOIN Verified.AssociationRoutes VRA
ON  VA.AssociationID = VRA.AssociationID AND
	VR.RouteID =  VRA.RouteID
WHERE VRA.AssociationID IS NULL

UPDATE Verified.Licences
SET AssociationID = A.AssociationID
FROM (SELECT L.pkiLicenceId,A.AssociationID FROM [OLASJun2023].dbo.tblLicence L
			INNER JOIN [OLASJun2023].dbo.tblApplication AA ON AA.pkiApplicationId = L.fkiApplicationId
			INNER JOIN [OLASJun2023].dbo.tlkHumanEntity HE ON AA.fkiApplicantAssocEntityId = HE.pkiEntityId 
			INNER JOIN Verified.Associations A ON HE.mfkiRealEntityId = A.OlasID) A
WHERE Verified.Licences.OlasId = A.pkiLicenceId