Open alangmaid opened 4 months ago
@lgiannini1 Please update the description for this ticket based on ECMPS 1.0 functionality for unit data.
Non load based indicator will handle by #6342 (this ticket should be worked on by the same developer).
Questions:
@yonatan-dp, the Unit Type Code and Operation Status Code and Begin Date come from the UNIT_BOILER_TYPE and UNIT_OP_STATUS tables in the CAMD schema.
I have added a script based on the SQL Server script for this data. Note that the were clause for a MON_LOC_ID is commented out, and that clause is the only reason that the MONITOR_LOCATION table is included in the script. Note that I am only providing the script as an example of how to get the Unit Type Code, Operation Status Code, and Operation Status Begin Date.
select unt.UNIT_ID, UNITID,
unt.NON_LOAD_BASED_IND,
unt.SOURCE_CATEGORY_CD,
to_char( unt.COMM_OP_DATE, 'mm/dd/yyyy' ) as COMM_OP_DATE,
to_char( unt.COMR_OP_DATE, 'mm/dd/yyyy' ) as COMR_OP_DATE,
(
select ubt.UNIT_TYPE_CD
from camd.UNIT_BOILER_TYPE ubt
where coalesce( ubt.END_DATE, to_date( '12/31/9999', 'mm/dd/yyyy' ) ) =
(
select MAX( coalesce( sel.END_DATE, to_date( '12/31/9999', 'mm/dd/yyyy' ) ) )
from camd.UNIT_BOILER_TYPE sel
where sel.UNIT_ID = unt.UNIT_ID
)
AND ubt.UNIT_ID = unt.UNIT_ID
) as UNIT_TYPE_CD,
uos.OP_STATUS_CD,
to_char( uos.BEGIN_DATE, 'mm/dd/yyyy' ) as STATUS_BEGIN_DATE,
unt.USERID as AUDIT_USER,
coalesce( unt.UPDATE_DATE, unt.ADD_DATE ) as AUDIT_DATE
from camd.UNIT unt
join camdecmps.MONITOR_LOCATION loc
on unt.UNIT_ID = loc.UNIT_ID
join
(
select UNIT_ID,
OP_STATUS_CD,
BEGIN_DATE
from camd.UNIT_OP_STATUS s1
where BEGIN_DATE = ( select max(BEGIN_DATE) from camd.UNIT_OP_STATUS s2 where s2.UNIT_ID = s1.UNIT_ID)
) as uos on uos.UNIT_ID = unt.UNIT_ID
--where ( @V_MON_LOC_ID is null or @V_MON_LOC_ID = MON_LOC_ID )
order
by UNITID
Currently, the page that displays the unit information has a checkbox for showing active/inactive data
For Unit program, there is an 'end_date' in CAMD.UNIT_PROGRAM that I can use to determine if that program is active or not (so that the user interaction is seemless). However, for camd.unit and monitor_plan_reporting_freq, there is no such field. I am making the assumption that those will be set to active always. Is the assumption correct?
I will defer to @esaber76 and @djw4erg as to how the "Show Inactive" checkbox should affect what data appears.
I can say that for monitor_plan_reporting_freq, there are fields for BEGIN_RPT_PERIOD_ID and END_RPT_PERIOD_ID (instead of begin/end date fields).
I wouldn't think that "inactive" would apply to unit records, unless retired units should be treated as "inactive". That would be based on the current active operating status for the unit in the unit_op_status table.
Using the following query for getting data for Reporting Frequency (this is the non-workspace version)
SELECT mprf.mon_plan_rf_id AS "id", mprf.report_freq_cd AS "reportFrequencyCode", string_agg(COALESCE(u.unit_id::text, sp.stack_name), ', ') AS "monitoringPlanLocations", rp_begin.period_abbreviation AS "beginQuarter", rp_end.period_abbreviation AS "endQuarter", rp_end.end_date, CASE WHEN rp_end.end_date IS NULL OR rp_end.end_date > CURRENT_DATE THEN true -- Future end date ELSE false END AS "active" FROM camdecmps.monitor_plan_reporting_freq mprf JOIN camdecmps.monitor_plan_location mpl ON mprf.mon_plan_id = mpl.mon_plan_id JOIN camdecmps.monitor_location ml ON mpl.mon_loc_id = ml.mon_loc_id LEFT JOIN camd.unit u ON ml.unit_id = u.unit_id LEFT JOIN camdecmps.stack_pipe sp ON ml.stack_pipe_id = sp.stack_pipe_id JOIN camdecmpsmd.reporting_period rp_begin ON mprf.begin_rpt_period_id = rp_begin.rpt_period_id LEFT JOIN camdecmpsmd.reporting_period rp_end ON mprf.end_rpt_period_id = rp_end.rpt_period_id WHERE ml.unit_id = $1 GROUP BY mprf.mon_plan_rf_id, mprf.report_freq_cd, rp_begin.period_abbreviation, rp_end.period_abbreviation, rp_end.end_date;
Not directly testing this but noticed the Show Inactive checkbox is no longer selectable in Unit Information. ORIS 1012, CS023, MS2, 2, 3. In particular, location 2 is an example.
The following read-only fields are present on dev and the following fields in the new grids have the correct data from the corresponding database tables:
Unit Attributes
Unit Programs
Reporting Frequencies
The Unit Id field in the Unit Attributes grid and the Monitoring Plan Locations field in the Reporting Frequencies grid displays the unit_id instead of the unitid for the location. For example, ORIS 628, unit 2GTB displays 91098 instead of 2GTB:
For units part of a common stack, multi-stack, or common pipe configuration, all monitoring plan locations tied to the stack/ pipe are not displaying in the Monitoring Plan Locations field of the Reporting Frequencies grid. For example, checking out ORIS 3, configuration CS0AAN, 1, 2 and looking at the Reporting Frequencies grid for unit 1 only shows that unit in the Monitoring Plan Locations field for the active and inactive records.
Looking at the same unit in 1.0 for ORIS 3, this field also displays the stack and other units associated with the stack.
ECMPS 1.0 has the following read-only fields in the Unit Characteristics section that need to be added to 2.0. A new Unit Program and Reporting Frequency grid needs to be added to the "Unit Information" section of the "Monitoring Plans" screen. Adding the editable Non Load Based Indicator field will be handled in #6342:
Acceptance Tests