Database Design

UCONN Now for Crew Supervisors is driven by a series of custom metrics tables:

METRICS_ACTIVE_WORK_ORDERS – Stores the minimal details of work orders that are in OPEN, ASSIGNED,  or PARTS status
METRICS_CURRENT_ASSIGN – Stores counts of who work orders are assigned to and how many non-assigned work orders an Employee has charged time against
METRICS_CURRENT_CREWS – Stores Counts of work orders in various categorizations (e.g., New, Priority, etc) for Crews and Complexes
METRICS_RECENT_CLOSE_WO – Stores the minimal details of work orders that closed in the past 7 days
UCONN WEATHER – Stores current and forecast data for all campuses
*FAMIS_NOTE – Stores the banner messages 

The METRICS_ tables are populated via a series of stored procedures that connect to STORPRD or external APIs:

COLLECT_WEATHER – gets forecast data from api.worldweatheronline.com and stores it locally so that it can be served up securely using https
MAX_TIME – is used to determine the most recent time_recorded for data in the metrics tables
METRICS_COLLECT_ACTIVE_WO – Collects the minimal details of work orders that are in OPEN, ASSIGNED,  or PARTS
METRICS_COLLECT_CURRENT_ASSIGN – Collects counts of who work orders are assigned to and how many non-assigned work orders an Employee has charged time against
METRICS_COLLECT_CURRENT_CREWS – Collects Counts of work orders in various categorizations (e.g., New, Priority, etc) for Crews and Complexes
METRICS_COLLECT_RECENT_CLOSE – Collects the minimal details of work orders that closed in the past 7 days
*SHADOW_COPY_FAMIS_NOTE – Collects only the BULLETIN type notes from FAMIS to be displayed on the banner.

 The stored procedures are run periodically to give a near real-time view of the data in the system.  The scheduled jobs that run are:

UPDATE_CURRENT_CREW_ASSIGN – Calls METRICS_COLLECT_CURRENT_ASSIGN every 5 minutes
UPDATE_CURRENT_CREW_METRICS – Calls METRICS_COLLECT_CURRENT_CREWS every 5 minutes
UPDATE_CURRENT_ACTIVE_WOS – Calls METRICS_COLLECT_ACTIVE_WO every 5 minutes
UPDATE_RECENT_CLOSE_WOS – Calls METRICS_COLLECT_RECENT_CLOSE every 5 minutes
UPDATE_WEATHER – Calls COLLECT_WEATHER every 10 minutes
*UPDATE_BANNER – Calls SHADOW_COPY_FAMIS_NOTE every 5 minutes