Backfill signal component completion dates

[johnclary]

Now that we have migrated historical signal projects into Moped, there are many signal components which do not have a completion date. We should backfill the component's completion date based on data available in the AMD Data Tracker.

Todo:

1. Extract a list of all traffic signal or PHB components from Moped where the work type is new and the completion date is null. The list should include the signal feature's signal_id so that is can be compared to the signal assets in the Data Tracker. The component_arcgis_online_view can be used for this! it includes a component URL which will be helpful for AMD
2. Use the open traffic signals dataset to add the Turn On Date column to the list you created from Moped data ☝️.
3. Provide this list to Ivonne and ask them to confirm if it's ok that the projects you listed should be updated
4. Take action based on Ivonne's response

[johnclary]

cc @ChristinaTremel we will get this assigned in the upcoming sprint

[chiaberry]

do we have a response from Ivonne about the signals?

[roseeichelmann]

@chiaberry @johnclary does anything need to be done here still?

[chiaberry]

the milestone for turn on date in moped is the same as the turn on date in data tracker. turning on a signal doesnt necessarily mean complete.

[chiaberry]

todo: put the date in the turn on date miliestone. mark complete but without a date

[mddilley]

Dev + Product Sync 5/29/2024:

• this came up when we were migrating projects with signal components that were in the access database
• think about project completion date vs component completion date override
• @ChristinaTremel will set up meeting with Ivonne to check in with AMD 🙏
• Kate: possibly used to report on # of signals turned on and when?
• John: Completion date versus substantial completion date
  • Substantial completion date is calculated in the project list DB view
  • Substantial completion date has only been introduced to ATSD

[mddilley]

Update from meeting with Christina and Ivonne on 6/5/2024

• Missing completion dates are not high priority to AMD
• Ivonne said we can use whatever is in Data Tracker
• Our takeaway is that this issue did not come from AMD

Next steps

• I will ask Nathan about how having these missing completion dates affects ATSD reporting. This also picks up on some discussion that came up in MUG today about bond reporting as well. Meeting with him on 6/18.
• If having the backfill is a need for ATSD reporting, then it sounds like AMD does not have a strong opinion about the date - just the "Complete" status. Whatever direction this goes, it needs to be low or no effort for AMD imo.

[mddilley]

Coming back here to update that this surfaced again when we received an outside request for project data that needed to be filtered on work done after 1/1/2023. It became difficult to query this reliably when project records were missing dates for complete or construction phases.

Query for reference:

SELECT
    p.project_id,
    mp.phase_name,
    mpp.is_current_phase,
    mpp.phase_start,
    me.entity_name,
    plv.project_council_districts
FROM
    moped_proj_phases mpp
    LEFT JOIN moped_phases mp ON mpp.phase_id = mp.phase_id
    LEFT JOIN moped_project p ON mpp.project_id = p.project_id
    LEFT JOIN moped_entity me ON p.project_lead_id = me.entity_id
    LEFT JOIN project_list_view plv ON plv.project_id = p.project_id 
WHERE
    TRUE
    AND is_current_phase = TRUE
    AND phase_start IS NULL
    AND(phase_name = 'Complete'
        OR phase_name = 'Construction')
--  AND 5 = ANY(plv.project_council_districts)

Edit on 8/26/2024: The query above needs to be modified to look at missing Complete phase end dates since that is what we show in the project list view.