[WIP]

[lbartoletti]

This is a test to fix https://github.com/qwat/QWAT/issues/169

[lbartoletti]

CREATE OR REPLACE FUNCTION qwat_od.ft_element_hydrant_update()
  RETURNS trigger AS
$BODY$
    BEGIN

        DELETE FROM qwat_od.hydrant WHERE id = OLD.id;

        DELETE FROM qwat_od.network_element WHERE id = OLD.id;

        PERFORM qwat_od.fn_node_set_type(OLD.id);

-- altitude is prioritary on Z value of the geometry (if both changed, only altitude is taken into account)
        IF NEW.altitude IS NULL THEN
            NEW.altitude := NULLIF( ST_Z(NEW.geometry), 0.0); -- 0 is the NULL value
        END IF;
        -- TODO handle going to NULL on update
        IF  NEW.altitude IS NULL     AND ST_Z(NEW.geometry) <> 0.0 OR
                NEW.altitude IS NOT NULL AND ( ST_Z(NEW.geometry) IS NULL OR ST_Z(NEW.geometry) <> NEW.altitude ) THEN
                NEW.geometry := ST_SetSRID( ST_MakePoint( ST_X(NEW.geometry), ST_Y(NEW.geometry), COALESCE(NEW.altitude,0) ), ST_SRID(NEW.geometry) );
        END IF;

        -- The function creates or gets a parent row.
        NEW.id := qwat_od.fn_node_create(NEW.geometry);
        -- If it previously existed with another subtype, it should raise an exception
        IF (SELECT _oid IS NOT NULL FROM 
            (
                SELECT id AS _oid FROM qwat_od.network_element
            ) AS foo WHERE _oid = NEW.id
        ) THEN
            RAISE EXCEPTION 'Cannot insert node as element since it already has another subtype. ID: %', NEW.id;
        END IF;

        -- altitude is prioritary on Z value of the geometry (if both changed, only altitude is taken into account)
        IF NEW.altitude IS NULL THEN
            NEW.altitude := NULLIF( ST_Z(NEW.geometry), 0.0); -- 0 is the NULL value
        END IF;
        -- TODO handle going to NULL on update
        IF  NEW.altitude IS NULL     AND ST_Z(NEW.geometry) <> 0.0 OR
                NEW.altitude IS NOT NULL AND ( ST_Z(NEW.geometry) IS NULL OR ST_Z(NEW.geometry) <> NEW.altitude ) THEN
                NEW.geometry := ST_SetSRID( ST_MakePoint( ST_X(NEW.geometry), ST_Y(NEW.geometry), COALESCE(NEW.altitude,0) ), ST_SRID(NEW.geometry) );
        END IF;

        INSERT INTO qwat_od.network_element (
            id
            , identification
            , fk_distributor
            , fk_status
            , fk_folder
            , fk_locationtype
            , fk_precision
            , fk_precisionalti
            , fk_object_reference
            , altitude
            , year
            , year_end
            , orientation
            , remark
            , label_1_visible
            , label_1_x
            , label_1_y
            , label_1_rotation
            , label_1_text
            , label_2_visible
            , label_2_x
            , label_2_y
            , label_2_rotation
            , label_2_text
        ) VALUES (
            NEW.id 
            , NEW.identification
            , NEW.fk_distributor
            , NEW.fk_status
            , NEW.fk_folder
            , NEW.fk_locationtype
            , NEW.fk_precision
            , NEW.fk_precisionalti
            , NEW.fk_object_reference
            , NEW.altitude
            , NEW.year
            , NEW.year_end
            , NEW.orientation
            , NEW.remark
            , NEW.label_1_visible
            , NEW.label_1_x
            , NEW.label_1_y
            , NEW.label_1_rotation
            , NEW.label_1_text
            , NEW.label_2_visible
            , NEW.label_2_x
            , NEW.label_2_y
            , NEW.label_2_rotation
            , NEW.label_2_text
        );

        INSERT INTO qwat_od.hydrant (
            id
            , fk_provider
            , fk_model_sup
            , fk_model_inf
            , fk_material
            , fk_output
            , underground
            , marked
            , pressure_static
            , pressure_dynamic
            , flow
            , observation_date
            , observation_source
        ) VALUES (
            NEW.id 
            , NEW.fk_provider
            , NEW.fk_model_sup
            , NEW.fk_model_inf
            , NEW.fk_material
            , NEW.fk_output
            , NEW.underground
            , NEW.marked
            , NEW.pressure_static
            , NEW.pressure_dynamic
            , NEW.flow
            , NEW.observation_date
            , NEW.observation_source
        );

        RETURN NEW;
    END
$BODY$
LANGUAGE plpgsql;