# 业务空间所属楼层 (关系由前端生成, 此算法是在多个楼层划分一个模型时, 根据已有的业务空间所属楼层数据重新计算业务空间所属楼层)
## 前置条件
    1. 楼层有外轮廓
    2. 业务空间有外轮廓
    3. 有2个或2个以上的楼层的ModelId相同(意思是一个模型划分成了多个楼层)
    4. 这些ModelId相同的楼层下有所属的业务空间关系
    5. 业务空间所属楼层表内维护的关系是正确的(需项目上的人员确认)
    
## 处理逻辑
    1. 查出项目内所有ModelId相同的楼层(如果没有则结束)
    2. 按照ModelId信息点将楼层分组, 相同ModelId的楼层为一组, 每组内按照以下规则计算
    3. 获取组内所有楼层下的业务空间, 将查出的业务空间的轮廓与组内楼层的轮廓比对是否有重叠部分(所有业务空间 X 组内楼层个数次对比), 如果有重叠则认为该业务空间和该楼层有关系
    4. 在业务空间所属楼层表内删除该组楼层下的所有业务空间关系, 并插入新计算出的业务空间所属楼层的关系
## 函数

<details>
<summary>源码</summary>

```

CREATE OR REPLACE FUNCTION "public"."rel_sp2fl"("project_id" varchar)
  RETURNS "pg_catalog"."bool" AS $BODY$
from matplotlib.path import Path
import json
from shapely.geometry import Polygon

# 获取Polygon对象
def get_polygon(single_poly):
    poly_len = len(single_poly)
    poly = []
    for i in range(poly_len):
        pair = single_poly[i]
        poly.append((pair["X"], pair["Y"]))
    return Polygon(poly)

# 判断业务空间轮廓是否跟楼层轮廓有重叠
def is_space_floor_overlap(space_outline, floor_outline):
    try:
        if space_outline is None or floor_outline is None:
            return False
        space_json = json.loads(space_outline)
        floor_json = json.loads(floor_outline)
        if len(floor_json) < 3 or len(space_json) == 0:
            return False
        floor_poly = get_polygon(floor_json)
        for i in range(len(space_json)):
            sub_space = space_json[i]
            sub_space_poly = get_polygon(sub_space[0])
            if floor_poly.contains(sub_space_poly) or floor_poly.equals(sub_space_poly) or floor_poly.overlaps(sub_space_poly) or sub_space_poly.contains(floor_poly):
                return True
        return False
    except Exception as e:
        plpy.info('计算轮廓异常')
        plpy.info(e)
        return False

column_model_id = 'model_id'
column_space_id = 'space_id'
column_floor_id1 = 'id1'
column_floor_outline1 = 'outline1'
column_floor_building1 = 'bd1'
column_floor_id2 = 'id2'
column_floor_outline2 = 'outline2'
column_floor_building2 = 'bd2'
column_space_zone = 'object_type'

# 构建一个  model_id --> set{floor_id} 的dict
def compose_model_id_floor_dict(involved_floors):
    model_id_floor_dict = dict()
    floor_outline_dict = dict()
    floor_building_dict = dict()
    for row in involved_floors:
        model_id = row.get(column_model_id)
        if model_id not in model_id_floor_dict:
            model_id_floor_dict[model_id] = set()
        floor_set = model_id_floor_dict[model_id]
        id1 = row.get(column_floor_id1)
        outline1 = row.get(column_floor_outline1)
        building_id1 = row.get(column_floor_building1)
        id2 = row.get(column_floor_id2)
        outline2 = row.get(column_floor_outline2)
        building_id2 = row.get(column_floor_building2)
        floor_set.add(id1)
        floor_set.add(id2)
        floor_building_dict[id1] = building_id1
        floor_building_dict[id2] = building_id2
        if id1 not in floor_outline_dict:
            floor_outline_dict[id1] = outline1
        if id2 not in floor_outline_dict:
            floor_outline_dict[id2] = outline2
    return model_id_floor_dict, floor_outline_dict, floor_building_dict

# 计算一个楼层组内的业务空间与楼层的轮廓关系
def calc_floor_group(floor_set, floor_outline_dict):
    sql_str = ""
    for floor_id in floor_set:
        sql_str += '\'' + floor_id + '\','
    if sql_str.endswith(','):
        sql_str = sql_str[0:-1]
    plpy.info('sql : {0}'.format(sql_str))
    involved_space_plan = plpy.prepare("SELECT distinct rel.space_id space_id, sp.outline outline1, sp.object_type object_type " +
        "FROM r_sp_in_fl rel inner join zone_space_base sp on sp.id = rel.space_id where rel.floor_id in ({0}) and sp.outline is not null".format(sql_str), ["text"])
    involved_space = involved_space_plan.execute([project_id])
    plpy.info("involved space:{}".format(len(involved_space)))
    result_dict = dict()    # space_id --> set{floor_id}
    space_zone_dict = dict()   # space_id --> object_type
    for space_row in involved_space:
        space_outline = space_row.get(column_floor_outline1)
        space_id = space_row.get(column_space_id)
        space_zone = space_row.get(column_space_zone)
        space_zone_dict[space_id] = space_zone
        result_dict[space_id] = set()
        rel_floor_set = result_dict[space_id]
        for floor_id in floor_set:
        #for floor_id, floor_outline in floor_outline_dict.items():
            floor_outline = floor_outline_dict[floor_id]
            if is_space_floor_overlap(space_outline, floor_outline):
                plpy.info('{0} : {1}'.format(space_id, floor_id))
                rel_floor_set.add(floor_id)
    return result_dict, space_zone_dict

# 删除以前的关系
def delete_prev_rel(floor_set):
    sql_str = ''
    for floor_id in floor_set:
        sql_str += '\'{0}\','.format(floor_id)
    if sql_str.endswith(','):
        sql_str = sql_str[0:-1]
    delete_rel_plan = plpy.prepare("delete from public.r_sp_in_fl where floor_id in ({0})".format(sql_str), [])
    delete_rel_plan.execute([])

# 添加关系到数据库
def add_rel(result_dict, space_zone_dict, floor_building_dict):
    for space_id, floor_set in result_dict.items():
        space_zone = space_zone_dict[space_id]
        for floor_id in floor_set:
            floor_building = floor_building_dict[floor_id]
            plpy.info("space_id : {0}, floor_id : {1}".format(space_id, floor_id))
            insert_rel_plan = plpy.prepare("insert into public.r_sp_in_fl(floor_id, space_id, object_type, project_id, sign, building_id) " +
                "values($1, $2, $3, $4, 2, $5)", ["text", "text", "text", "text", "text"])
            try:
                insert_rel_plan.execute([floor_id, space_id, space_zone, project_id, floor_building])
            except Exception as e:
                plpy.info(e)

try:
    # 将下面对数据库的操作作为一个事务, 出异常则自动rollback

    involved_floors_plan = plpy.prepare("select f1.id id1, f1.building_id bd1, f2.id id2, f2.building_id bd2, f1.model_id model_id, f1.outline outline1, f2.outline outline2 " +
        "from floor f1 inner join floor f2 on f1.model_id = f2.model_id " +
        "where f1.id != f2.id and f1.outline is not null and f2.outline is not null and f1.project_id = $1 and f2.project_id = $1", ["text"])
    involved_floors = involved_floors_plan.execute([project_id])
    if len(involved_floors) == 0 :
        plpy.info('没有需要计算的改变')
        return True
    plpy.info(len(involved_floors))
    model_id_floor_dict, floor_outline_dict, floor_building_dict = compose_model_id_floor_dict(involved_floors)
    plpy.info(len(floor_outline_dict))
    plpy.info(len(floor_building_dict))
    plpy.info(len(model_id_floor_dict))
    for model_id, floor_set in model_id_floor_dict.items():
        with plpy.subtransaction():
            plpy.info(model_id)
            plpy.info(floor_set)
            result_dict, space_zone_dict = calc_floor_group(floor_set, floor_outline_dict)  # space_id -->所属 set{floor_id},  space_id --> object_type
            delete_prev_rel(floor_set)
            add_rel(result_dict, space_zone_dict, floor_building_dict)
    return True
except Exception as e:
    plpy.warning(e)
    return False
$BODY$
  LANGUAGE plpython3u VOLATILE
  COST 100;


select public.rel_sp2fl('Pj1101050001')
```
</details>

## 入参
    1. 项目id
## 例子
    select public.rel_sp2fl('Pj1102290002');