tww.tww

Parameters

Name	Type	Mode
		IN

Definition

DECLARE
      dx float;
      dy float;
    BEGIN
      UPDATE tww_od.cover co SET
          brand = NEW.co_brand
          , cover_shape = NEW.co_shape
          , diameter = NEW.co_diameter
          , fastening = NEW.co_fastening
          , level = NEW.co_level
          , material = NEW.co_material
          , positional_accuracy = NEW.co_positional_accuracy
          -- , situation3d_geometry = NEW.co_situation3d_geometry
          , sludge_bucket = NEW.co_sludge_bucket
          , venting = NEW.co_venting
        WHERE obj_id = OLD.co_obj_id;
      UPDATE tww_od.structure_part sp SET
          fk_dataowner = NEW.fk_dataowner
          , fk_provider = NEW.fk_provider
          -- , fk_wastewater_structure = NEW.co_fk_wastewater_structure
          , identifier = NEW.co_identifier
          , last_modification = NEW.last_modification
          , remark = NEW.co_remark
          , renovation_demand = NEW.co_renovation_demand
        WHERE obj_id = OLD.co_obj_id;
      UPDATE tww_od.wastewater_structure ws SET
          obj_id = NEW.obj_id
          -- , _bottom_label = NEW._bottom_label
          -- , _cover_label = NEW._cover_label
          -- , _depth = NEW._depth
          -- , _input_label = NEW._input_label
          -- , _label = NEW._label
          -- , _output_label = NEW._output_label
          , accessibility = NEW.accessibility
          , condition_score = NEW.condition_score
          , contract_section = NEW.contract_section
          -- , detail_geometry3d_geometry = NEW.detail_geometry3d_geometry
          , elevation_determination = NEW.elevation_determination
          , financing = NEW.financing
          , fk_dataowner = NEW.fk_dataowner
          -- , fk_main_cover = NEW.fk_main_cover
          -- , fk_main_wastewater_node = NEW.fk_main_wastewater_node
          , fk_operator = NEW.fk_operator
          , fk_owner = NEW.fk_owner
          , fk_provider = NEW.fk_provider
          , gross_costs = NEW.gross_costs
          , identifier = NEW.identifier
          , inspection_interval = NEW.inspection_interval
          -- , last_modification = NEW.last_modification
          , location_name = NEW.location_name
          , records = NEW.records
          , remark = NEW.remark
          , renovation_necessity = NEW.renovation_necessity
          , replacement_value = NEW.replacement_value
          , rv_base_year = NEW.rv_base_year
          , rv_construction_type = NEW.rv_construction_type
          , status = NEW.status
          , status_survey_year = NEW.status_survey_year
          , structure_condition = NEW.structure_condition
          , subsidies = NEW.subsidies
          , urgency_figure = NEW.urgency_figure
          , year_of_construction = NEW.year_of_construction
          , year_of_replacement = NEW.year_of_replacement
        WHERE obj_id = OLD.obj_id;
      UPDATE tww_od.wastewater_node wn SET
          -- _function_hierarchic = NEW.wn__function_hierarchic
          -- _status = NEW.wn__status
          -- _usage_current = NEW.wn__usage_current
          backflow_level_current = NEW.wn_backflow_level_current
          , bottom_level = NEW.wn_bottom_level
          , elevation_accuracy = NEW.wn_elevation_accuracy
          , fk_hydr_geometry = NEW.wn_fk_hydr_geometry
          , function_node_amelioration = NEW.wn_function_node_amelioration
          -- , situation3d_geometry = NEW.wn_situation3d_geometry
          , wwtp_number = NEW.wn_wwtp_number
        WHERE obj_id = OLD.wn_obj_id;

IF OLD.ws_type <> NEW.ws_type THEN
        CASE WHEN OLD.ws_type <> 'unknown' THEN
          BEGIN
            EXECUTE FORMAT('DELETE FROM tww_od.%I WHERE obj_id = %L',OLD.ws_type,OLD.obj_id);
          END;
        END CASE;

CASE WHEN NEW.ws_type = ANY(ARRAY['manhole','special_structure','discharge_point','infiltration_installation','drainless_toilet','wwtp_structure','small_treatment_plant']) THEN
          BEGIN
            EXECUTE FORMAT('INSERT INTO tww_od.%I(obj_id) VALUES (%L)',NEW.ws_type,OLD.obj_id);
          END;
        END CASE;
      END IF;

CASE
        WHEN NEW.ws_type = 'wwtp_structure' THEN
          UPDATE tww_od.wwtp_structure wt SET
          fk_waste_water_treatment_plant = NEW.wt_fk_waste_water_treatment_plant
          , kind = NEW.wt_kind
        WHERE obj_id = OLD.obj_id;

WHEN NEW.ws_type = 'small_treatment_plant' THEN
          UPDATE tww_od.small_treatment_plant sm SET
          approval_number = NEW.sm_approval_number
          , function = NEW.sm_function
          , installation_number = NEW.sm_installation_number
          , remote_monitoring = NEW.sm_remote_monitoring
        WHERE obj_id = OLD.obj_id;

WHEN NEW.ws_type = 'drainless_toilet' THEN
          UPDATE tww_od.drainless_toilet dt SET
          kind = NEW.to_kind
        WHERE obj_id = OLD.obj_id;

ELSE -- do nothing
      END CASE;

-- Cover geometry has been moved
      IF NOT ST_Equals( OLD.situation3d_geometry, NEW.situation3d_geometry) THEN
        dx = ST_X(NEW.situation3d_geometry) - ST_X(OLD.situation3d_geometry);
        dy = ST_Y(NEW.situation3d_geometry) - ST_Y(OLD.situation3d_geometry);

-- Move wastewater node as well
        -- comment: TRANSLATE((ST_MakePoint(500, 900, 'NaN')), 10, 20, 0) would return NaN NaN NaN - so we have this workaround
        UPDATE tww_od.wastewater_node WN
        SET situation3d_geometry = ST_SetSRID( ST_MakePoint(
        ST_X(ST_TRANSLATE(ST_MakePoint(ST_X(WN.situation3d_geometry), ST_Y(WN.situation3d_geometry)), dx, dy )),
        ST_Y(ST_TRANSLATE(ST_MakePoint(ST_X(WN.situation3d_geometry), ST_Y(WN.situation3d_geometry)), dx, dy )),
        ST_Z(WN.situation3d_geometry)), 2056 )
        WHERE obj_id IN
        (
          SELECT obj_id FROM tww_od.wastewater_networkelement
          WHERE fk_wastewater_structure = NEW.obj_id
        );

-- Move covers
        UPDATE tww_od.cover CO
        SET situation3d_geometry = ST_SetSRID( ST_MakePoint(
        ST_X(ST_TRANSLATE(ST_MakePoint(ST_X(CO.situation3d_geometry), ST_Y(CO.situation3d_geometry)), dx, dy )),
        ST_Y(ST_TRANSLATE(ST_MakePoint(ST_X(CO.situation3d_geometry), ST_Y(CO.situation3d_geometry)), dx, dy )),
        ST_Z(CO.situation3d_geometry)), 2056 )
        WHERE obj_id IN
        (
          SELECT obj_id FROM tww_od.structure_part
          WHERE fk_wastewater_structure = NEW.obj_id
        );

-- Move reach(es) as well
        UPDATE tww_od.reach RE
        SET progression3d_geometry =
          ST_ForceCurve (ST_SetPoint(
            ST_CurveToLine (RE.progression3d_geometry ),
            0, -- SetPoint index is 0 based, PointN index is 1 based.
            ST_SetSRID( ST_MakePoint(
                ST_X(ST_TRANSLATE(ST_MakePoint(ST_X(ST_PointN(RE.progression3d_geometry, 1)), ST_Y(ST_PointN(RE.progression3d_geometry, 1))), dx, dy )),
                ST_Y(ST_TRANSLATE(ST_MakePoint(ST_X(ST_PointN(RE.progression3d_geometry, 1)), ST_Y(ST_PointN(RE.progression3d_geometry, 1))), dx, dy )),
                ST_Z(ST_PointN(RE.progression3d_geometry, 1))), 2056 )
          ) )
        WHERE fk_reach_point_from IN
        (
          SELECT RP.obj_id FROM tww_od.reach_point RP
          LEFT JOIN tww_od.wastewater_networkelement NE ON RP.fk_wastewater_networkelement = NE.obj_id
          WHERE NE.fk_wastewater_structure = NEW.obj_id
        );

UPDATE tww_od.reach RE
        SET progression3d_geometry =
          ST_ForceCurve( ST_SetPoint(
            ST_CurveToLine( RE.progression3d_geometry ),
            ST_NumPoints(RE.progression3d_geometry) - 1,
            ST_SetSRID( ST_MakePoint(
                ST_X(ST_TRANSLATE(ST_MakePoint(ST_X(ST_EndPoint(RE.progression3d_geometry)), ST_Y(ST_EndPoint(RE.progression3d_geometry))), dx, dy )),
                ST_Y(ST_TRANSLATE(ST_MakePoint(ST_X(ST_EndPoint(RE.progression3d_geometry)), ST_Y(ST_EndPoint(RE.progression3d_geometry))), dx, dy )),
                ST_Z(ST_PointN(RE.progression3d_geometry, 1))), 2056 )
          ) )
        WHERE fk_reach_point_to IN
        (
          SELECT RP.obj_id FROM tww_od.reach_point RP
          LEFT JOIN tww_od.wastewater_networkelement NE ON RP.fk_wastewater_networkelement = NE.obj_id
          WHERE NE.fk_wastewater_structure = NEW.obj_id
        );
      END IF;

RETURN NEW;
    END;

ft_vw_tww_additional_ws_update()

Parameters

Definition