CMS: Compare Mental Health phone number reports

[dsinla]

Description

We want to compare Mental health phone numbers come from the CMS vs LH. Refer to: https://dsva.slack.com/archives/C02BTJTDFTN/p1665092143341629 Next step is to filter the existing list, remove anything where the results are fields migrated in directly, we only want to see the 'non migrated' fields

Acceptance Criteria

• [x] Filter the existing list, remove anything where the results are fields migrated in directly, we only want to see the 'non migrated' fields -
• [x] Note where the CMS Editor values differ from what we received from LH - we might see additional numbers and/or different numbers (NOTE: phone num + extension is different from phone num with no ext)

CMS Team

Please check the team(s) that will do this work.

• [ ] Program
• [ ] Platform CMS Team
• [ ] Sitewide Crew
• [ ] ⭐️ Sitewide CMS
• [ ] ⭐️ Public Websites
• [x] ⭐️ Facilities
• [ ] ⭐️ User support

[JayDarnell]

Updating the estimate as this initially requires a fair bit of database wrangling and I need to refresh myself on how I did it the first time.

[JayDarnell]

Mental Health Phone Number Audit.xlsx

There are two tabs in this sheet. In the first tab I've trimmed down the result set significantly to 143 items. There are no archived items in this list. For each item that remains there are either phone numbers in the Appointment Phone or Service Location fields that differ from the number we get from lighthouse. A few rows are marked in red showing a difference between lighthouse and the CMS but I think these are flukes from the out of date lighthouse data I'm using. Every one I spot checked matched so I think we can ignore these.

The second tab contains the complete list of facility data for all facilities that aren't archived.

[JayDarnell]

Moving to closed as the original ACs were met. @dsinla which ticket holds our next steps for this?

[omahane]

Here's the fullest discussion of the spreadsheet: https://dsva.slack.com/archives/C02BTJTDFTN/p1665092143341629

[swirtSJW]

Jay shared the query with me with the following notes

This .sql file contains several select statements, many of which are sub queries I was running to wrap my head around everything we needed. I think I used some of the smaller queries to create temporary tables locally. I think the big query that starts with the comment -- Concatenate all numbers together is what I used to generate the full report:

-- lh_mental_health_phone is a database dump from lighthouse
SELECT count(*) FROM lh_mental_health_phone lmhp 

-- mental_health_phone gets the mental_health_phone from the vamc facility node
SELECT * FROM mental_health_phone mhp 

-- appointment_phone gets the  phone and extension from the phone number paragraph type referenced by the appointment phone field on the mental health facility service node for a given facility
SELECT * FROM appointment_phone ap 

-- service_location_phone gets the phone and extension from the phone number paragraph type referenced by the service location field on the mental health facility service node for a given facility
SELECT * FROM service_location_phone slp 

-- Get lighthouse facilities that don't exist in CMS
SELECT lmhp.facility_locator_id, lmhp.lighthouse_phone FROM lh_mental_health_phone lmhp 
LEFT JOIN mental_health_phone mhp ON lmhp.facility_locator_id = mhp.facility_locator_id 
WHERE mhp.facility_locator_id IS NULL

SELECT * FROM lh_mental_health_phone lmhp 
SELECT count(*) FROM mental_health_phone mhp 

-- Get CMS facilities that don't exist in the Lighthouse data dump
SELECT mhp.facility_locator_id, mhp.mental_health_phone FROM mental_health_phone mhp 
LEFT JOIN lh_mental_health_phone lmhp ON mhp.facility_locator_id = lmhp.facility_locator_id 
WHERE lmhp.facility_locator_id IS NULL

-- Concatenate all numbers together
SELECT 
    mhp.facility_locator_id,
    mhp.facility,
    mhp.url,
    mhp.moderation_state,
    REPLACE(lmhp.lighthouse_phone, 'x', ' x') as lighthouse_phone,
    mhp.mental_health_phone,
    app_phones.appointment_phone_count,
    -- REPLACE(app_phones.appointment_phones, 'x', ' x') as appointment_phones,
    app_phones.appointment_phones as appointment_phones,
    service_phones.service_location_phone_count,
    -- REPLACE(service_phones.service_location_phones, 'x', ' x') as service_location_phones
    service_phones.service_location_phones as service_location_phones
FROM 
    mental_health_phone mhp 
LEFT JOIN lh_mental_health_phone lmhp 
    ON lmhp.facility_locator_id = mhp.facility_locator_id
LEFT JOIN 
(SELECT 
    ap.facility_locator_id,
    COUNT(ap.appointment_phone) as appointment_phone_count, 
    GROUP_CONCAT(ap.appointment_phone SEPARATOR ', ') as appointment_phones 
FROM appointment_phone ap 
GROUP BY ap.facility_locator_id) app_phones
ON app_phones.facility_locator_id = mhp.facility_locator_id
LEFT JOIN 
(SELECT 
    slp.facility_locator_id, 
    count(slp.service_location_phone) as service_location_phone_count,
    GROUP_CONCAT(slp.service_location_phone SEPARATOR ', ') as service_location_phones
FROM service_location_phone slp 
GROUP BY slp.facility_locator_id) service_phones
on service_phones.facility_locator_id = mhp.facility_locator_id
WHERE mhp.moderation_state != 'Archived'
ORDER BY mhp.facility_locator_id

-- Lighthouse Phones
SELECT 
    lmhp.facility_locator_id,
    COUNT(lmhp.lighthouse_phone) as lh_phone_count,
    GROUP_CONCAT(lmhp.lighthouse_phone SEPARATOR ', ') as lh_phones
FROM lh_mental_health_phone lmhp
GROUP BY lmhp.facility_locator_id 

-- 1321 records from lighthouse
-- SELECT * FROM lh_mental_health_phone lmhp

-- Appointment Phones
SELECT 
    ap.facility_locator_id, 
    COUNT(ap.appointment_phone) as appointment_phone_count, 
    GROUP_CONCAT(ap.appointment_phone SEPARATOR ', ') as appointment_phones 
FROM appointment_phone ap 
GROUP BY ap.facility_locator_id

-- SELECT * FROM (SELECT ap.facility_locator_id, count(ap.appointment_phone) as row_count FROM appointment_phone ap GROUP BY ap.facility_locator_id) sub WHERE sub.row_count >1
SELECT * FROM appointment_phone ap 

-- Service Location Phones
SELECT 
    slp.facility_locator_id, 
    count(slp.service_location_phone) as service_location_phone_count,
    GROUP_CONCAT(slp.service_location_phone SEPARATOR ', ') as service_location_phones
FROM service_location_phone slp 
GROUP BY slp.facility_locator_id

-- SELECT * FROM (SELECT slp.facility_locator_id, count(slp.service_location_phone) as row_count FROM service_location_phone slp GROUP BY slp.facility_locator_id) sub WHERE sub.row_count >1
SELECT * FROM service_location_phone slp