sagacloud-doc/datacenter/docs/relation_calc/Fl2Fl.md

楼层贯通关系

前置条件

两个楼层的model_id信息点需要一致并不能为空, 则认为两个楼层是贯通关系

FROM floor WHERE project_id='Pj4201050001' AND model_id is not null

处理方式

1. 先删除项目下所有的贯通关系(只删除sign为2的关系)
2. 多表连接更新:
    1. 根据model_id信息点多表连接
    2. 将结果插入到r_fl_through_fl表中, sign置为2

函数

create or replace function public.rel_fl2fl(project_id character varying, sign1 integer, sign2 integer) returns boolean
as
$$
try:
    # 将下面对数据库的操作作为一个事务, 出异常则自动rollback
    with plpy.subtransaction():
        # 删除原来楼层贯通关系
        plan1 = plpy.prepare("delete from r_fl_through_fl where project_id = $1 and (sign = $2 or sign = $3)", ["text", "integer", "integer"])
        plan1.execute([project_id, sign1, sign2])
        # 计算贯通关系
        plan2 = plpy.prepare("select f1.id as lid, f2.id as rid from floor f1 left join floor f2 on f1.model_id = f2.model_id where f1.id != f2.id and f1.project_id = $1 and f2.project_id = $1", ["text"])
        relation = plan2.execute([project_id])
        if len(relation) == 0:
            # plpy.info("计算出没有关系")
            return True
        for row in relation:
            plan3 = plpy.prepare("insert into r_fl_through_fl(floor_id, floor_other_id, project_id, sign) values($1, $2, $3, 2) ", ["text", "text", 'text'])
            plan3.execute([row['lid'], row['rid'], project_id])
except Exception as e:
    plpy.warning(e)
    return False
else:
    return True
$$
LANGUAGE 'plpython3u' VOLATILE;

输入

1. 项目id
2. 表示r_fl_through_fl表中要被删除的sign值
3. 同上, 与2是逻辑或的关系

返回结果

true    成功
false   失败