AddAssetFmecaSyncFunctionsMigration

Database	Schema	Description
Transaction	ampc	Add functions for Asset Fmeca sync.

SQL Statements

DROP FUNCTION IF EXISTS ampc.asset_fmecas_starting_at_asset;

CREATE FUNCTION ampc.asset_fmecas_starting_at_asset(starting_asset_id bigint, starting_record_type ampc.asset_record_type[]) RETURNS TABLE (id bigint)
LANGUAGE SQL
AS $BODY$
    WITH ids AS
    (
        SELECT
            fmeca.asset_fmeca_id
        FROM
            ampc.asset asset
                JOIN ampc.asset_fmeca fmeca ON asset.asset_id = fmeca.asset_id
        WHERE
            asset.asset_id = $1 AND
            asset.record_type = ANY($2) AND
            fmeca.asset_fmeca_id > 0
    )
    SELECT * FROM ids
    UNION ALL
    SELECT fmeca.asset_fmeca_id FROM ids JOIN ampc.asset_fmeca fmeca ON (ids.asset_fmeca_id * -1) = fmeca.asset_fmeca_id;
$BODY$;



DROP FUNCTION IF EXISTS ampc.asset_fmecas_starting_at_asset_fmeca;

CREATE FUNCTION ampc.asset_fmecas_starting_at_asset_fmeca(asset_fmeca_id bigint) RETURNS TABLE (id bigint)
LANGUAGE SQL
AS $BODY$
    WITH ids AS
    (
        SELECT
            fmeca.asset_fmeca_id
        FROM
            ampc.asset_fmeca fmeca
        WHERE
            fmeca.asset_fmeca_id = $1 AND
            fmeca.asset_fmeca_id > 0
    )
    SELECT * FROM ids
    UNION ALL
    SELECT fmeca.asset_fmeca_id FROM ids JOIN ampc.asset_fmeca fmeca ON (ids.asset_fmeca_id * -1) = fmeca.asset_fmeca_id;
$BODY$;



DROP FUNCTION IF EXISTS ampc.asset_fmecas_starting_at_asset_ic;

CREATE FUNCTION ampc.asset_fmecas_starting_at_asset_ic(starting_asset_id bigint, starting_record_type ampc.asset_record_type[]) RETURNS TABLE (id bigint)
LANGUAGE SQL
AS $BODY$
    WITH ids AS
    (
        SELECT
            fmeca.asset_fmeca_id
        FROM
            ampc.asset asset
                JOIN ampc.asset_tree tree ON tree.path_parent_asset_id = asset.asset_id
                JOIN ampc.asset casset ON tree.asset_id = casset.asset_id
                JOIN ampc.asset_fmeca fmeca ON casset.asset_id = fmeca.asset_id
        WHERE
            asset.asset_id = $1 AND
            asset.record_type = ANY($2) AND
            asset.nearest_regular_asset_id = casset.nearest_regular_asset_id AND
            fmeca.asset_fmeca_id > 0
    )
    SELECT * FROM ids
    UNION ALL
    SELECT fmeca.asset_fmeca_id FROM ids JOIN ampc.asset_fmeca fmeca ON (ids.asset_fmeca_id * -1) = fmeca.asset_fmeca_id;
$BODY$;



DROP FUNCTION IF EXISTS ampc.asset_fmecas_starting_at_component;

CREATE FUNCTION ampc.asset_fmecas_starting_at_component(starting_component_id bigint, starting_record_type ampc.asset_component_record_type[]) RETURNS TABLE (id bigint)
LANGUAGE SQL
AS $BODY$
    WITH ids AS
    (
        SELECT
            fmeca.asset_fmeca_id
        FROM
            ampc.asset_component ac
            JOIN ampc.asset_fmeca fmeca ON ac.asset_component_id = fmeca.asset_component_id
        WHERE
            ac.asset_component_id = $1 AND
            ac.record_type = ANY($2) AND
            fmeca.asset_fmeca_id > 0
    )
    SELECT * FROM ids
    UNION ALL
    SELECT fmeca.asset_fmeca_id FROM ids JOIN ampc.asset_fmeca fmeca ON (ids.asset_fmeca_id * -1) = fmeca.asset_fmeca_id;
$BODY$;



DROP FUNCTION IF EXISTS ampc.asset_fmecas_starting_at_component_ic;

CREATE FUNCTION ampc.asset_fmecas_starting_at_component_ic(starting_component_id bigint, starting_record_type ampc.asset_component_record_type[]) RETURNS TABLE (id bigint)
    LANGUAGE SQL
AS $BODY$
    WITH ids AS
    (
     SELECT
         fmeca.asset_fmeca_id
     FROM
         ampc.asset_component ac
             JOIN ampc.asset_component_tree tree ON ac.asset_component_id = tree.path_parent_asset_component_id
             JOIN ampc.asset_component component ON tree.asset_component_id = component.asset_component_id
             JOIN ampc.asset_fmeca fmeca ON component.asset_component_id = fmeca.asset_component_id
     WHERE
         ac.asset_component_id = $1 AND
         ac.record_type = ANY($2) AND
         fmeca.asset_fmeca_id > 0
    )
SELECT * FROM ids
UNION ALL
SELECT fmeca.asset_fmeca_id FROM ids JOIN ampc.asset_fmeca fmeca ON (ids.asset_fmeca_id * -1) = fmeca.asset_fmeca_id;
$BODY$;



DROP FUNCTION IF EXISTS ampc.asset_fmeca_tree_load_filter;

CREATE FUNCTION ampc.asset_fmeca_tree_load_filter(starting_entity_id bigint, starting_entity_type system.entity_type, include_children boolean)
    RETURNS TABLE (id bigint)
    LANGUAGE plpgsql AS
$BODY$
DECLARE
    starting_entity_id ALIAS FOR $1;
    starting_entity_type ALIAS FOR $2;
    include_children ALIAS FOR $3;
BEGIN
    IF starting_entity_type = 'RegularAsset' THEN
        IF include_children THEN
            RETURN QUERY SELECT * FROM ampc.asset_fmecas_starting_at_asset_ic(starting_entity_id, ARRAY['Regular']::ampc.asset_record_type[]);
        ELSE
            RETURN QUERY SELECT * FROM ampc.asset_fmecas_starting_at_asset(starting_entity_id, ARRAY['Regular']::ampc.asset_record_type[]);
        END IF;
    ELSEIF starting_entity_type = 'InPlaceAsset' THEN
        IF include_children THEN
            RETURN QUERY SELECT * FROM ampc.asset_fmecas_starting_at_asset_ic(starting_entity_id, ARRAY['InPlace']::ampc.asset_record_type[]);
        ELSE
            RETURN QUERY SELECT * FROM ampc.asset_fmecas_starting_at_asset(starting_entity_id, ARRAY['InPlace']::ampc.asset_record_type[]);
        END IF;
    ELSEIF starting_entity_type = 'PlaceholderAsset' THEN
        IF include_children THEN
            RETURN QUERY SELECT * FROM ampc.asset_fmecas_starting_at_asset_ic(starting_entity_id, ARRAY['EmptyPlaceholder', 'LinkedPlaceholder']::ampc.asset_record_type[]);
        ELSE
            RETURN QUERY SELECT * FROM ampc.asset_fmecas_starting_at_asset(starting_entity_id, ARRAY['EmptyPlaceholder', 'LinkedPlaceholder']::ampc.asset_record_type[]);
        END IF;
    ELSEIF starting_entity_type = 'PlaceholderAssetComponent' THEN
        IF include_children THEN
            RETURN QUERY SELECT * FROM ampc.asset_fmecas_starting_at_component_ic(starting_entity_id, ARRAY['EmptyPlaceholder', 'LinkedPlaceholder']::ampc.asset_component_record_type[]);
        ELSE
            RETURN QUERY SELECT * FROM ampc.asset_fmecas_starting_at_component(starting_entity_id, ARRAY['EmptyPlaceholder', 'LinkedPlaceholder']::ampc.asset_component_record_type[]);
        END IF;
    ELSEIF starting_entity_type = 'RegularAssetComponent' THEN
        IF include_children THEN
            RETURN QUERY SELECT * FROM ampc.asset_fmecas_starting_at_component_ic(starting_entity_id, ARRAY['Regular']::ampc.asset_component_record_type[]);
        ELSE
            RETURN QUERY SELECT * FROM ampc.asset_fmecas_starting_at_component(starting_entity_id, ARRAY['Regular']::ampc.asset_component_record_type[]);
        END IF;
    ELSEIF starting_entity_type = 'AssetFmeca' THEN
        RETURN QUERY SELECT * FROM ampc.asset_fmecas_starting_at_asset_fmeca(starting_entity_id);
    ELSE
        RAISE EXCEPTION 'Incorrect starting_entity_type: %', starting_entity_type;
    END IF;
END;
$BODY$;



CREATE OR REPLACE FUNCTION ampc.asset_fmeca_path(asset_fmeca_id bigint)
    RETURNS TABLE
            (
                entity_type system.entity_type,
                record_type text,
                entity_id   bigint,
                entity_code text,
                type_order  int,
                tree_level  int,
                path_order  bigint
            )
AS $$
WITH fmeca AS
         (
             SELECT
                 3 AS type_order,
                 f.linked_to_entity_type AS entity_type,
                 CASE WHEN c.asset_component_id IS NULL THEN a.record_type::TEXT ELSE c.record_type::TEXT END AS record_type,
                 f.asset_fmeca_id AS entity_id,
                 '' AS entity_code,
                 1 AS tree_level,
                 CASE WHEN f.asset_component_id IS NOT NULL THEN 'Component' ELSE 'Type' END AS parent_entity_type,
                 CASE WHEN f.asset_component_id IS NOT NULL THEN f.asset_component_id ELSE f.asset_id END AS parent_entity_id
             FROM
                 ampc.asset_fmeca f
                     JOIN ampc.asset a ON a.asset_id = f.asset_id
                     LEFT JOIN ampc.asset_component c ON c.asset_component_id = f.asset_component_id
             WHERE
                     f.asset_fmeca_id = $1
             LIMIT 1
         ),
     components AS
         (
             SELECT * FROM fmeca

             UNION ALL

             SELECT
                 2 AS type_order,
                 ampc.asset_component_record_type_entity(ac.record_type) AS entity_type,
                 ac.record_type::text,
                 t.path_parent_asset_component_id AS entity_id,
                 ac.code AS entity_code,
                 t.path_parent_asset_component_tree_level AS tree_level,
                 CASE WHEN ac.parent_component_id IS NOT NULL THEN 'Component' ELSE 'Type' END AS parent_entity_type,
                 CASE WHEN ac.parent_component_id IS NOT NULL THEN ac.parent_component_id ELSE ac.asset_id END AS parent_entity_id
             FROM
                 ampc.asset_component_tree t
                     JOIN ampc.asset_component ac ON t.path_parent_asset_component_id = ac.asset_component_id
             WHERE
                     t.asset_component_id =
                     (
                         SELECT
                             CASE WHEN parent_entity_type = 'Component' THEN parent_entity_id ELSE NULL END
                         FROM
                             fmeca LIMIT 1
                     )
         ),
     assets AS
         (
             SELECT * FROM components

             UNION ALL

             SELECT
                 1 AS type_order,
                 ampc.asset_record_type_entity(a.record_type) AS entity_type,
                 a.record_type::text,
                 t.path_parent_asset_id AS entity_id,
                 a.code AS entity_code,
                 t.path_parent_asset_tree_level AS tree_level,
                 null AS parent_entity_type,
                 null AS parent_entity_id
             FROM
                 ampc.asset_tree t
                     JOIN ampc.asset a ON t.path_parent_asset_id = a.asset_id
             WHERE
                     t.asset_id =
                     (
                         SELECT CASE WHEN (SELECT parent_entity_type FROM fmeca LIMIT 1) = 'Component' THEN
                                         (
                                             SELECT
                                                 asset_id
                                             FROM
                                                 ampc.asset_component
                                             WHERE
                                                     asset_component_id =
                                                     (
                                                         SELECT
                                                             entity_id
                                                         FROM
                                                             components
                                                         WHERE
                                                             (entity_type = 'RegularAssetComponent'::system.entity_type OR entity_type = 'PlaceholderAssetComponent'::system.entity_type) AND
                                                                 tree_level = 1
                                                         LIMIT 1
                                                     )
                                         )
                                     ELSE
                                         (
                                             SELECT parent_entity_id FROM fmeca LIMIT 1
                                         ) END
                     )
         )

SELECT
    entity_type,
    record_type,
    entity_id,
    entity_code,
    type_order,
    tree_level,
    row_number() OVER (ORDER BY type_order, tree_level) AS path_order
FROM
    assets
ORDER BY
    type_order,
    tree_level;

$$ LANGUAGE sql;



DROP FUNCTION IF EXISTS ampc.asset_fmeca_function_remapping;

CREATE FUNCTION ampc.asset_fmeca_function_remapping(assetFmecaIds bigint[])
    RETURNS TABLE
            (
                asset_fmeca_id bigint,
                target_asset_function_id bigint
            )
AS $$
SELECT
    af.asset_fmeca_id,
    link_target.asset_function_id AS target_asset_function_id
FROM
    unnest($1) i

        JOIN ampc.asset_fmeca af ON i = af.asset_fmeca_id
        JOIN ampc.asset_type_fmeca atf ON af.inherited_from_id = atf.asset_type_fmeca_id

        JOIN ampc.asset_type_fmeca parent_fmeca ON atf.asset_type_fmeca_id = parent_fmeca.asset_type_fmeca_id
        JOIN ampc.asset_type pfmeca_type ON parent_fmeca.asset_type_id = pfmeca_type.asset_type_id

        JOIN ampc.asset_type_function target_function ON atf.asset_type_function_id = target_function.asset_type_function_id
        JOIN ampc.asset_type tfunction_type ON target_function.asset_type_id = tfunction_type.asset_type_id

        JOIN ampc.asset_fmeca inh_parent_fmeca ON af.asset_fmeca_id = inh_parent_fmeca.asset_fmeca_id
        JOIN ampc.asset inh_parent_asset ON inh_parent_fmeca.asset_id = inh_parent_asset.asset_id

        JOIN LATERAL (
        SELECT
            entity_type AS target_boundary_type,
            entity_id AS target_boundary_id
        FROM
            ampc.asset_fmeca_path(af.asset_fmeca_id)
        WHERE
                entity_type = 'RegularAsset' OR
                entity_type = 'PlaceholderAsset' OR
                entity_type = 'PlaceholderAssetComponent'
        ORDER BY path_order DESC
        LIMIT 1
        ) inh_fmeca_path ON true

        JOIN LATERAL (
        SELECT
            f.asset_function_id,
            f.inherited_from_id,
            f.asset_component_id,
            f.asset_id,
            a.nearest_regular_asset_id
        FROM
            ampc.asset_function f
                JOIN ampc.asset a ON f.asset_id = a.asset_id
        WHERE
                f.inherited_from_id = target_function.asset_type_function_id AND
                a.nearest_regular_asset_id = inh_parent_asset.nearest_regular_asset_id
        ) link_target ON true
WHERE
        link_target.asset_function_id > 0 AND
    CASE
        WHEN target_boundary_type = 'PlaceholderAssetComponent' AND (SELECT EXISTS(SELECT * FROM ampc.asset_component WHERE asset_component_id = target_boundary_id AND linked_on_type_side = false AND linked_to_asset_type_id = pfmeca_type.nearest_regular_asset_type_id)) THEN
                    link_target.asset_component_id = ANY(SELECT asset_component_id FROM ampc.asset_component_tree WHERE path_parent_asset_component_id = target_boundary_id) AND
                    target_boundary_id = (SELECT entity_id FROM ampc.asset_fmeca_path(i) WHERE entity_type = 'PlaceholderAssetComponent' ORDER BY path_order DESC LIMIT 1)
        --We don't really know how asset placeholders will work yet
        --WHEN target_boundary_type = 'PlaceholderAsset' AND (SELECT EXISTS(SELECT * FROM ampc.asset WHERE asset_id = target_boundary_id AND linked_to_asset_type_id = pfmeca_type.nearest_regular_asset_type_id)) THEN
        --    link_target.asset_type_id = ANY(SELECT asset_type_id FROM ampc.asset_type_tree WHERE path_parent_asset_type_id = target_boundary_id) --AND
        ELSE
                link_target.nearest_regular_asset_id = inh_parent_asset.nearest_regular_asset_id
        END;

$$ LANGUAGE sql;