Schema Model Help

Anonymous · ‎05-28-2021

Need help with my schema. I have 2 different types of PMS(single pms and group pms). With the work_logs and assignees tables they have a column called type which is goup pm and single pm and use the record_id column as the key for pms.id and pms.grouped_pm id. Is my shcema correct? Below along with the schema i am using is also a sql function for what i am trying to do in power bi

CREATE FUNCTION pm_aggregate_report (_facility_ids uuid[], _risk_ids uuid[] DEFAULT NULL::uuid[], _assignee_ids uuid[] DEFAULT NULL::uuid[], _start_date date DEFAULT NULL::date, _end_date date DEFAULT NULL::date) RETURNS TABLE(facility character varying, pm_id uuid, grouped_pm boolean, risk_id uuid, risk character varying, pm_status_id uuid, user_id uuid, assignee text, completed_by uuid, total_labor bigint)
 VOLATILE
AS $dbvis$
/*
       USAGE: 
       SELECT * FROM pm_aggregate_report(ARRAY['0e9783a5-c585-44d8-8677-dd7e4afda029']::UUID[], NULL, NULL, '2018-09-30', '2019-09-30')
       SELECT * FROM pm_aggregate_report(ARRAY['8d735407-744f-4940-bab8-67e40f929c5a']::UUID[], NULL, NULL, NULL, NULL)
        
       --filter on assignees
       SELECT * FROM pm_aggregate_report(ARRAY['0e9783a5-c585-44d8-8677-dd7e4afda029']::UUID[], NULL, ARRAY['eb38dca5-45a6-44db-99ae-aa88aca8c06e']::UUID[], '2018-09-30', '2019-09-30')
        
       --filter on risks
       SELECT * FROM pm_aggregate_report(ARRAY['0e9783a5-c585-44d8-8677-dd7e4afda029']::UUID[], ARRAY['31b838f0-730b-4415-80bd-e84f4323ab10', 'b3c10d9f-7529-468e-811f-1dbbde63bbfd']::UUID[], NULL, '2018-09-30', '2019-09-30')
       SELECT * FROM pm_aggregate_report(ARRAY['0e9783a5-c585-44d8-8677-dd7e4afda029']::UUID[], ARRAY['b3c10d9f-7529-468e-811f-1dbbde63bbfd']::UUID[], NULL, '2018-09-30', '2019-09-30')
   */
    
    BEGIN
     
       CREATE TEMP TABLE tmp_pm_aggregate
       AS
       SELECT DISTINCT 
           COALESCE(gp.facility_id, a.facility_id) as facility_id,
           COALESCE(p.grouped_pm_id, p.id) as pm_id,
           CASE WHEN p.grouped_pm_id IS NULL THEN false ELSE true END as grouped_pm,
           COALESCE(gp.risk_id, a.risk_id) as risk_id,
           COALESCE(gp.pm_status_id, p.pm_status_id) as pm_status_id,
           COALESCE(gass.user_id, sass.user_id) as user_id,
           COALESCE(gp.completed_by, p.completed_by) as completed_by
       FROM pms p
       JOIN assets a
           ON p.asset_id = a.id
       LEFT JOIN grouped_pms gp
           ON p.grouped_pm_id = gp.id
       LEFT JOIN assignees sass
         ON p.id = sass.record_id
         AND sass.type = 'single_pm'
       LEFT JOIN assignees gass
         ON p.grouped_pm_id = gass.record_id
         AND gass.type = 'grouped_pm'
       LEFT JOIN users u
         ON (sass.user_id = u.id OR gass.user_id = u.id)
       WHERE a.facility_id = ANY(_facility_ids)
         AND NOT a.is_component
         AND COALESCE(gp.pm_status_id, p.pm_status_id) in ('f9bdfc17-3bb5-4ec0-8477-24ef05ea3b9b', '06fc910c-3d07-4284-8f6e-8fb3873f5333')
         AND COALESCE(gp.completion_date, p.completion_date) BETWEEN COALESCE(_start_date, '1/1/2000') AND COALESCE(_end_date, '1/1/3000')
         AND COALESCE(gp.show_date, p.show_date) <= CURRENT_TIMESTAMP
         AND COALESCE(gass.user_id, sass.user_id) IS NOT NULL
         AND u.user_type_id != 'ec823d98-7023-4908-8006-2e33ddf2c11b' --exclude shared_services users
         AND (_risk_ids IS NULL OR COALESCE(gp.risk_id, a.risk_id) = ANY(_risk_ids))   
         AND (_assignee_ids IS NULL OR COALESCE(gass.user_id, sass.user_id) = ANY(_assignee_ids));
     
       RETURN QUERY 
        
       SELECT
f.name as facility,
          t.pm_id,
          t.grouped_pm,  
          t.risk_id,
r.name as risk, 
          t.pm_status_id,
          t.user_id,
          u.name_last || ', ' || u.name_first as assignee, 
          t.completed_by,
          COALESCE(gwl.total_labor, swl.total_labor) as total_labor
       FROM tmp_pm_aggregate t
       JOIN facilities f
           ON t.facility_id = f.id
       JOIN risks r
           ON t.risk_id = r.id
       JOIN users u
           ON t.user_id = u.id
       LEFT JOIN (SELECT wl.record_id, wl.user_id, SUM(wl.labor_time) as total_labor
               FROM work_logs wl
               WHERE wl.type = 'single_pm'
               GROUP BY wl.record_id, wl.user_id) as swl
          ON t.pm_id = swl.record_id
          AND t.user_id = swl.user_id
          AND t.grouped_pm = false
       LEFT JOIN (SELECT wl.record_id, wl.user_id, SUM(wl.labor_time) as total_labor
               FROM work_logs wl
               WHERE wl.type = 'grouped_pm'
               GROUP BY wl.record_id, wl.user_id) as gwl
          ON t.pm_id = gwl.record_id
          AND t.user_id = gwl.user_id
          AND t.grouped_pm = true
        ORDER BY facility,
            assignee,
            risk;
           
       DROP TABLE tmp_pm_aggregate;
    END;
$dbvis$ LANGUAGE plpgsql