USE POLARIS

-- This script is for use with replacing incorrect postal code entries with good ones.
-- It runs a replacement script, which will automatucally update all patron address entries using the incorrect entry, 
-- however this won't update the patron modification date.

-- Make sure to run either the COMMIT TRANSACTION or ROLLBACK TRANSACTION code (commented out after the CURSOR is closed) 
-- after running, or the tables will remain locked and cause an issue.



DROP TABLE IF EXISTS #RepCodes
DROP TABLE IF EXISTS #RepCodes2



CREATE TABLE #RepCodes (
    BadPostalCode int,
    GoodPostalCode int
)

INSERT INTO #RepCodes
VALUES
    (1, 2) -- list all bad and replacement PostalCodeID entries here


-- Add to a table with PatronIDs, for review counts
CREATE TABLE #RepCodes2 (
    BadPostalCode int,
    GoodPostalCode int,
    PatronID int,
    AddressID int
)

INSERT INTO #RepCodes2
SELECT
    pc.PostalCodeID,
    rc.GoodPostalCode,
    pr.PatronID,
    a.AddressID
FROM Polaris.PostalCodes pc
INNER JOIN #RepCodes rc
    on pc.PostalCodeID = rc.BadPostalCode
INNER JOIN Polaris.Addresses a
    on pc.PostalCodeID = a.PostalCodeID
INNER JOIN Polaris.PatronAddresses pa
    on a.AddressID = pa.AddressID
INNER JOIN Polaris.PatronRegistration pr (nolock)
    on pa.PatronID = pr.PatronID
--WHERE pc.PostalCodeID = 890845
GROUP BY
    pc.PostalCodeID,
    rc.GoodPostalCode,
    pr.PatronID,
    a.AddressID


-- Count of updates
SELECT
    COUNT(DISTINCT rc.BadPostalCode) BadPostalCodes_toReplace,
    COUNT(DISTINCT rc.AddressID) Addresses_toUpdate,
    COUNT(DISTINCT rc.PatronID) UniquePatrons_w_Addresses
FROM Polaris.PostalCodes pc
INNER JOIN #RepCodes2 rc
    on pc.PostalCodeID = rc.BadPostalCode


-- List of bad addresses with the values replacing them
SELECT
    pa.PatronID,
    a.AddressID,
    pr.PatronFullName,
    pc.PostalCodeID,
    pc.City,
    pc.County,
    pc.[State],
    pc.CountryID,
    pc.PostalCode,
    pc2.PostalCodeID Good_PostalCodeID,
    pc2.City Good_City,
    pc2.County Good_County
FROM Polaris.PostalCodes pc
INNER JOIN #RepCodes2 rc
    on pc.PostalCodeID = rc.BadPostalCode
LEFT JOIN Polaris.PostalCodes pc2
    on rc.GoodPostalCode = pc2.PostalCodeID
INNER JOIN Polaris.Addresses a
    on pc.PostalCodeID = a.PostalCodeID
INNER JOIN Polaris.PatronAddresses pa
    on a.AddressID = pa.AddressID
INNER JOIN Polaris.PatronRegistration pr (nolock)
    on pa.PatronID = pr.PatronID
GROUP BY
    pa.PatronID,
    a.AddressID,
    pr.PatronFullName,
    pc.PostalCodeID,
    pc.City,
    pc.County,
    pc.[State],
    pc.CountryID,
    pc.PostalCode,
    pc2.PostalCodeID,
    pc2.City,
    pc2.County
ORDER BY
    pa.PatronID,
    a.AddressID


DECLARE @BadPostalCode int,
        @GoodPostalCode int


BEGIN TRANSACTION


DECLARE PC_Delete CURSOR FOR
SELECT
    rc.BadPostalCode,
    rc.GoodPostalCode
FROM #RepCodes2 rc
GROUP BY
    rc.BadPostalCode,
    rc.GoodPostalCode

OPEN PC_Delete

FETCH NEXT FROM PC_Delete INTO 
        @BadPostalCode,
        @GoodPostalCode

WHILE @@FETCH_STATUS = 0

    BEGIN

        EXEC Polaris.SA_DeletePostalCodeRow
            @BadPostalCode, -- DoomedPostalCodeID
            @GoodPostalCode -- ReplacementPostalCodeID

		FETCH NEXT FROM PC_Delete INTO 
			@BadPostalCode,
			@GoodPostalCode

    END


CLOSE PC_Delete

DEALLOCATE PC_Delete


-- COMMIT TRANSACTION

-- ROLLBACK TRANSACTION



-- Bad postal code entries that haven't been removed - should be blank
SELECT
    pa.PatronID,
    a.AddressID,
    a.StreetOne,
    a.StreetTwo,
    a.StreetThree,
    rc.BadPostalCode,
    pc.City,
    pc.State,
    pc.PostalCode,
    pc.County,
    pc.CountryID
FROM Polaris.PostalCodes pc
INNER JOIN #RepCodes2 rc
    on pc.PostalCodeID = rc.BadPostalCode
LEFT JOIN Polaris.Addresses a
    on rc.BadPostalCode = a.PostalCodeID
LEFT JOIN Polaris.PatronAddresses pa
    on a.AddressID = pa.AddressID
WHERE a.AddressID IS NOT NULL
GROUP BY
    pa.PatronID,
    a.AddressID,
    a.StreetOne,
    a.StreetTwo,
    a.StreetThree,
    rc.BadPostalCode,
    pc.City,
    pc.State,
    pc.PostalCode,
    pc.County,
    pc.CountryID



-- validation

-- Count of updates - should be 0
SELECT
    COUNT(DISTINCT rc.BadPostalCode) BadPostalCodes_toReplace,
    COUNT(DISTINCT rc.AddressID) Addresses_toUpdate,
    COUNT(DISTINCT rc.PatronID) UniquePatrons_w_Addresses
FROM Polaris.PostalCodes pc
INNER JOIN #RepCodes2 rc
    on pc.PostalCodeID = rc.BadPostalCode


-- List of patron addresses
SELECT
    rc.PatronID,
    a.AddressID,
    pr.PatronFullName,
    pc.PostalCodeID,
    pc.City,
    pc.County,
    pc.[State],
    pc.CountryID,
    pc.PostalCode
FROM #RepCodes2 rc
LEFT JOIN Polaris.PatronRegistration pr
    on rc.PatronID = pr.PatronID
LEFT JOIN Polaris.PatronAddresses pa
    on rc.PatronID = pa.PatronID
        and rc.AddressID = pa.AddressID
LEFT JOIN Polaris.Addresses a
    on a.AddressID = pa.AddressID
LEFT JOIN Polaris.PostalCodes pc
    on pc.PostalCodeID = a.PostalCodeID
GROUP BY
    rc.PatronID,
    a.AddressID,
    pr.PatronFullName,
    pc.PostalCodeID,
    pc.City,
    pc.County,
    pc.[State],
    pc.CountryID,
    pc.PostalCode
ORDER BY
    rc.PatronID,
    a.AddressID



DROP TABLE IF EXISTS #RepCodes
DROP TABLE IF EXISTS #RepCodes2