tww.tww

Parameters

Name	Type	Mode
_obj_id	text	IN
_all	boolean	IN

Definition

DECLARE
  myrec record;

BEGIN
UPDATE tww_od.wastewater_structure ws
SET _label = label,
    _cover_label = cover_label,
    _bottom_label = bottom_label,
    _input_label = input_label,
    _output_label = output_label
    FROM(

SELECT   ws_obj_id,
          COALESCE(ws_identifier, '') as label,
          CASE WHEN count(co_level)<2 THEN array_to_string(array_agg(E'\nC' || '=' || co_level ORDER BY idx DESC), '', '') ELSE
		  array_to_string(array_agg(E'\nC' || idx || '=' || co_level ORDER BY idx ASC), '', '') END as cover_label,
          array_to_string(array_agg(E'\nB' || '=' || bottom_level), '', '') as bottom_label,
          CASE WHEN count(rpi_level)<2 THEN array_to_string(array_agg(E'\nI' || '=' || rpi_level ORDER BY idx DESC), '', '') ELSE
		  array_to_string(array_agg(E'\nI' || idx || '=' || rpi_level ORDER BY idx ASC), '', '') END as input_label,
          CASE WHEN count(rpo_level)<2 THEN array_to_string(array_agg(E'\nO' || '=' || rpo_level ORDER BY idx DESC), '', '') ELSE
		  array_to_string(array_agg(E'\nO' || idx || '=' || rpo_level ORDER BY idx ASC), '', '') END as output_label
  FROM (
		  SELECT ws.obj_id AS ws_obj_id, ws.identifier AS ws_identifier, parts.co_level AS co_level, parts.rpi_level AS rpi_level, parts.rpo_level AS rpo_level, parts.obj_id, idx, bottom_level AS bottom_level
    FROM tww_od.wastewater_structure WS

LEFT JOIN (
		With outputs AS (
		SELECT NULL AS co_level,
		  NULL::text AS rpi_level,
			coalesce(round(RP.level, 2)::text, '?') AS rpo_level,
			NE.fk_wastewater_structure ws, RP.obj_id,
			row_number() OVER(PARTITION BY NE.fk_wastewater_structure
							  ORDER BY
							  fh.tww_symbology_order,
							  uc.tww_symbology_order,
							  ST_Azimuth(RP.situation3d_geometry,ST_PointN(RE_from.progression3d_geometry,2)) ASC) AS idx,
		  NULL::text AS bottom_level,
		  ST_Azimuth(RP.situation3d_geometry,ST_PointN(RE_from.progression3d_geometry,2)) AS azimuth
		  FROM tww_od.reach_point RP
		  LEFT JOIN tww_od.wastewater_networkelement NE ON RP.fk_wastewater_networkelement = NE.obj_id
		  INNER JOIN tww_od.reach RE_from ON RP.obj_id = RE_from.fk_reach_point_from
		  LEFT JOIN tww_od.wastewater_networkelement NE_RE ON NE_RE.obj_id::text = RE_from.obj_id::text
		  LEFT JOIN tww_od.wastewater_structure ws ON NE_RE.fk_wastewater_structure::text = ws.obj_id::text
		  LEFT JOIN tww_od.channel ch ON ch.obj_id::text = ws.obj_id::text
		  LEFT JOIN tww_vl.channel_function_hierarchic fh ON ch.function_hierarchic  = fh.code
		  LEFT JOIN tww_vl.channel_usage_current uc ON ch.usage_current = uc.code
		  WHERE (_all OR NE.fk_wastewater_structure = _obj_id)
		)
      SELECT coalesce(round(CO.level, 2)::text, '?') AS co_level, NULL::text AS rpi_level, NULL::text AS rpo_level, SP.fk_wastewater_structure ws, SP.obj_id, row_number() OVER(PARTITION BY SP.fk_wastewater_structure) AS idx, NULL::text AS bottom_level
      FROM tww_od.structure_part SP
      RIGHT JOIN tww_od.cover CO ON CO.obj_id = SP.obj_id
      WHERE _all OR SP.fk_wastewater_structure = _obj_id
      -- Inputs
      UNION

SELECT NULL AS co_level,
	  coalesce(round(RP.level, 2)::text, '?') AS rpi_level,
	  NULL::text AS rpo_level,
	  NE.fk_wastewater_structure ws, RP.obj_id,
	  row_number() OVER(PARTITION BY NE.fk_wastewater_structure
						ORDER BY (mod((2*pi()+(ST_Azimuth(RP.situation3d_geometry,ST_PointN(RE_to.progression3d_geometry,-2))-outs.azimuth))::numeric , 2*pi()::numeric)) ASC) AS idx,
	  NULL::text AS bottom_level
      FROM tww_od.reach_point RP
      LEFT JOIN tww_od.wastewater_networkelement NE ON RP.fk_wastewater_networkelement = NE.obj_id
	  INNER JOIN tww_od.reach RE_to ON RP.obj_id = RE_to.fk_reach_point_to
	  LEFT JOIN tww_od.reach RE_from ON RP.obj_id = RE_from.fk_reach_point_from
      LEFT JOIN tww_od.wastewater_networkelement NE_to ON NE_to.obj_id = RE_to.obj_id
      LEFT JOIN tww_od.channel CH_to ON NE_to.fk_wastewater_structure = CH_to.obj_id
      LEFT JOIN tww_vl.channel_function_hierarchic fh ON CH_to.function_hierarchic  = fh.code
	  LEFT JOIN outputs outs on outs.ws = NE.fk_wastewater_structure AND outs.idx=1
      WHERE (_all OR NE.fk_wastewater_structure = _obj_id) and fh.tww_use_in_labels
      -- Outputs
      UNION
      SELECT co_level, rpi_level,rpo_level,ws,obj_id,idx,bottom_level FROM outputs
      -- Bottom
      UNION
      SELECT NULL AS co_level, NULL::text AS rpi_level, NULL::text AS rpo_level, ws1.obj_id ws, NULL, NULL, round(wn.bottom_level, 2)::text AS wn_bottom_level
      FROM tww_od.wastewater_structure ws1
      LEFT JOIN tww_od.wastewater_node wn ON wn.obj_id = ws1.fk_main_wastewater_node
      WHERE _all OR ws1.obj_id = _obj_id
	)AS parts ON ws = ws.obj_id
    WHERE _all OR ws.obj_id = _obj_id
	  ) parts
  GROUP BY ws_obj_id, COALESCE(ws_identifier, '')
) labeled_ws
WHERE ws.obj_id = labeled_ws.ws_obj_id;

END

update_wastewater_structure_label(_obj_id text, _all boolean DEFAULT false)

Parameters

Definition