CREATE OR REPLACE FUNCTION is_point_in_polygon_v1(x double precision, y double precision, json_poly jsonb) RETURNS boolean AS
$$
from matplotlib.path import Path
import json
try:
    poly = []
    polygon = json.loads(json_poly)
    l = len(polygon)
    points = (x, y)
    for i in range(l):
        pair = polygon[i]
        poly.append((pair["X"], pair["Y"]))

    p = Path(poly)
except Exception as e:
    return False
else:
    return p.contains_points([points], None, -0.0001)

$$
    LANGUAGE 'plpython3u' VOLATILE;

CREATE OR REPLACE FUNCTION is_point_in_polygon_v2(x double precision, y double precision, json_poly jsonb) RETURNS boolean AS
$$
from matplotlib.path import Path
import json

def is_in_meta_polygon(point, single_poly, radius):
    poly_len = len(single_poly)
    poly = []
    for i in range(poly_len):
        pair = single_poly[i]
        poly.append((pair["X"], pair["Y"]))
    p = Path(poly)
    return p.contains_points([point], None, radius)

try:
    polygons = json.loads(json_poly)
    total_len = len(polygons)
    point_pair = (x, y)
    if total_len == 0:
        return False
    for i in range(total_len):
        polygon = polygons[i]
        if(i == 0):
            if not is_in_meta_polygon(point_pair, polygon, -0.001):
                return False
        else:
            if is_in_meta_polygon(point_pair, polygon, 0.001):
                return False
    return True
except Exception as e:
    return False
else:
    return True

$$
    LANGUAGE 'plpython3u' VOLATILE;


CREATE OR REPLACE FUNCTION is_point_in_polygon_v3(x double precision, y double precision, json_poly jsonb) RETURNS boolean AS
$$
from matplotlib.path import Path
import json

def is_in_meta_polygon(point, single_poly, radius):
    poly_len = len(single_poly)
    poly = []
    for i in range(poly_len):
        pair = single_poly[i]
        poly.append((pair["X"], pair["Y"]))
    p = Path(poly)
    return p.contains_points([point], None, radius)


def is_in_polygon(point, polygons):
    length = len(polygons)
    if length == 0:
        return False
    for j in range(length):
        polygon = polygons[j]
        if j == 0:
            if not is_in_meta_polygon(point, polygon, -0.001):
                return False
        else:
            if is_in_meta_polygon(point, polygon, 0.001):
                return False
    return True

try:
    polygon_list = json.loads(json_poly)
    total_len = len(polygon_list)
    point_pair = (x, y)
    for index in range(total_len):
        if is_in_polygon(point_pair, polygon_list[index]):
            return True
    return False
except Exception as e:
    return False
$$
    LANGUAGE 'plpython3u' VOLATILE;


CREATE OR REPLACE FUNCTION is_point_in_polygon_v1_str(x character varying, y character varying, json_poly jsonb) RETURNS boolean AS
$$
from matplotlib.path import Path
import json
try:
    poly = []
    polygon = json.loads(json_poly)
    l = len(polygon)
    points = (float(x), float(y))
    for i in range(l):
        pair = polygon[i]
        poly.append((pair["X"], pair["Y"]))

    p = Path(poly)
except Exception as e:
    return False
else:
    return p.contains_points([points], None, -0.0001)

$$
    LANGUAGE 'plpython3u' VOLATILE;

CREATE OR REPLACE FUNCTION is_point_in_polygon_v2_str(x character varying, y character varying, json_poly jsonb) RETURNS boolean AS
$$
from matplotlib.path import Path
import json

def is_in_meta_polygon(point, single_poly, radius):
    poly_len = len(single_poly)
    poly = []
    for i in range(poly_len):
        pair = single_poly[i]
        poly.append((pair["X"], pair["Y"]))
    p = Path(poly)
    return p.contains_points([point], None, radius)

try:
    polygons = json.loads(json_poly)
    total_len = len(polygons)
    point_pair = (float(x), float(y))
    if total_len == 0:
        return False
    for i in range(total_len):
        polygon = polygons[i]
        if(i == 0):
            if not is_in_meta_polygon(point_pair, polygon, -0.001):
                return False
        else:
            if is_in_meta_polygon(point_pair, polygon, 0.001):
                return False
    return True
except Exception as e:
    return False
else:
    return True

$$
    LANGUAGE 'plpython3u' VOLATILE;


CREATE OR REPLACE FUNCTION is_point_in_polygon_v3_str(x character varying, y character varying, json_poly jsonb) RETURNS boolean AS
$$
from matplotlib.path import Path
import json

def is_in_meta_polygon(point, single_poly, radius):
    poly_len = len(single_poly)
    poly = []
    for i in range(poly_len):
        pair = single_poly[i]
        poly.append((pair["X"], pair["Y"]))
    p = Path(poly)
    return p.contains_points([point], None, radius)


def is_in_polygon(point, polygons):
    length = len(polygons)
    if length == 0:
        return False
    for j in range(length):
        polygon = polygons[j]
        if j == 0:
            if not is_in_meta_polygon(point, polygon, -0.001):
                return False
        else:
            if is_in_meta_polygon(point, polygon, 0.001):
                return False
    return True

try:
    polygon_list = json.loads(json_poly)
    total_len = len(polygon_list)
    point_pair = (float(x), float(y))
    for index in range(total_len):
        if is_in_polygon(point_pair, polygon_list[index]):
            return True
    return False
except Exception as e:
    return False
$$
    LANGUAGE 'plpython3u' VOLATILE;


select is_point_in_polygon_v1(1.1, 1.1, '[{"X":1.1, "Y":1.1},{"X":1.1, "Y":4.1},{"X":4.1, "Y":4.1},{"X":4.1, "Y":1.1}]');
select is_point_in_polygon_v2(2.1, 2.1, '[[{"X":1.1, "Y":1.1},{"X":1.1, "Y":4.1},{"X":4.1, "Y":4.1},{"X":4.1, "Y":1.1}], [{"X":2.1, "Y":2.1}, {"X":2.1, "Y":3.1}, {"X":3.1, "Y":3.1}, {"X":3.1, "Y":2.1}]]');
select is_point_in_polygon_v3(2.1, 2.1, '[[[{"X":1.1, "Y":1.1},{"X":1.1, "Y":4.1},{"X":4.1, "Y":4.1},{"X":4.1, "Y":1.1}], [{"X":2.1, "Y":2.1}, {"X":2.1, "Y":3.1}, {"X":3.1, "Y":3.1}, {"X":3.1, "Y":2.1}]],[[{"X":11.1, "Y":11.1},{"X":11.1, "Y":14.1},{"X":14.1, "Y":14.1},{"X":14.1, "Y":11.1}], [{"X":12.1, "Y":12.1}, {"X":12.1, "Y":13.1}, {"X":13.1, "Y":13.1}, {"X":13.1, "Y":12.1}]]]');





create function model_floor_file_func(projectid character varying)
    returns TABLE(id character varying, floor_name character varying, project_id character varying, folder_id character varying, fid character varying, accept_time timestamp with time zone, version integer, note text, user_id character varying, user_name character varying, log jsonb, url text, md5 character varying, status smallint)
    language plpgsql
as
$$
BEGIN
    return QUERY

        SELECT floor.id,
               floor.floor_name,
               floor.project_id,
               floor.folder_id,
               file.id AS fid,
               file.accept_time,
               file.version,
               file.note,
               file.user_id,
               file.user_name,
               file.log,
               file.url,
               file.md5,
               file.status
        FROM (revit.model_floor floor
                 JOIN revit.model_file file ON (((floor.current_model_id)::text = (file.id)::text)))
        where floor.project_id = projectId and file.removed != true;

END;

$$;

alter function model_floor_file_func(varchar) owner to postgres;


-- 计算空间邻接关系
create function calcspaceadjoinrelation(model character varying) returns boolean
    language plpython3u
as
$$
    from relations import adjacent

    segments = plpy.execute("SELECT * FROM revit.boundary_segment where model_id = '" + model + "'")
    columns = plpy.execute("SELECT * FROM revit.column where model_id = '" + model + "'")
    vwalls = plpy.execute("SELECT * FROM revit.virtual_wall where model_id = '" + model + "'")
    walls = plpy.execute("SELECT * FROM revit.wall where model_id = '" + model + "'")
    try:
        # 返回结果为关系二维数组, 类似入参格式
        relations = adjacent.calc_adjacent_relation(columns, segments, vwalls, walls)
        # 将下面对数据库的操作作为一个事务, 出异常则自动rollback
        with plpy.subtransaction():
            plpy.execute("delete from revit.rel_space_adjacent where model_id ='" + model + "'")
            for item in relations:
                from_var = item["from"]
                to_var = item["to"]
                plpy.execute("INSERT INTO revit.rel_space_adjacent(model_id, from_id, to_id) VALUES ('" + model + "', '" + from_var + "', '" + to_var + "')")
    except Exception as e:
        return False
    else:
        return True
$$;

alter function calcspaceadjoinrelation(varchar) owner to postgres;








-- 检查saga标记和saga标记前的楼层名是否合法
CREATE OR REPLACE FUNCTION revit_calc.check_saga_mark(IN project_id varchar, IN folder_id varchar) RETURNS
    TABLE(id character varying, floor_name character varying, project_id character varying, folder_id character varying, fid character varying, accept_time timestamp with time zone, version integer, note text, user_id character varying, user_name character varying, log jsonb, url text, md5 character varying, status smallint, has_error bool, error character varying)
AS
$$
    from relations.src.saga_mark.check_saga_mark import check_saga_mark

    involved_model_sql = "select * from revit.model_floor_file_func('" + project_id + "') where folder_id = '"+ folder_id +"' and status in (3, 31, 4) "
    level_sql = "select * from revit.level where model_id = "

    involved_model = plpy.execute(involved_model_sql)
    level_data = dict()
    for item in involved_model:
        current_level_sql = level_sql + '\'{model_id}\''.format(model_id=item.get('fid'))
        single_model_level = plpy.execute(current_level_sql)
        level_data[item.get('fid')] = single_model_level
    try:
        # 返回结果为关系二维数组, 类似入参格式
        result = check_saga_mark(involved_model, level_data)
        return involved_model
    except Exception as e:
        return False
    else:
        return False
$$
    LANGUAGE 'plpython3u' VOLATILE;




-- 检查标高是否合法
CREATE OR REPLACE FUNCTION revit_calc.check_level_valid(IN project_id varchar, IN folder_id varchar) RETURNS
    TABLE(id character varying, floor_name character varying, project_id character varying, folder_id character varying, fid character varying, accept_time timestamp with time zone, version integer, note text, user_id character varying, user_name character varying, log jsonb, url text, md5 character varying, status smallint, has_error bool, error character varying)
AS
$$
    from relations.src.level.check_level import check_level_reasonable

    involved_model_sql = "select * from revit.model_floor_file_func('" + project_id + "') where folder_id = '"+ folder_id +"' and status in (3, 31, 4) "
    level_sql = "select * from revit.level where name like '%-saga' and model_id = "

    involved_model = plpy.execute(involved_model_sql)
    level_data = dict()
    for item in involved_model:
        current_level_sql = level_sql + '\'{model_id}\''.format(model_id=item.get('fid'))
        single_model_level = plpy.execute(current_level_sql)
        level_data[item.get('fid')] = single_model_level
    try:
        # 返回结果为关系二维数组, 类似入参格式
        result = check_level_reasonable(involved_model, level_data)
        return involved_model
    except Exception as e:
        return False
    else:
        return False
$$
    LANGUAGE 'plpython3u' VOLATILE;





-- 检查轴网是否合法
CREATE OR REPLACE FUNCTION revit_calc.check_grid_valid(IN project_id varchar, IN folder_id varchar) RETURNS
    TABLE(id character varying, floor_name character varying, project_id character varying, folder_id character varying, fid character varying, accept_time timestamp with time zone, version integer, note text, user_id character varying, user_name character varying, log jsonb, url text, md5 character varying, status smallint, group_id integer)
AS
$$
    from relations.src.grid.check_grid import check_grid_upright

    involved_model_sql = "select * from revit.model_floor_file_func('" + project_id + "') where folder_id = '"+ folder_id +"' and status in (3, 31, 4) "
    grid_sql = "select * from revit.grid where model_id = "

    involved_model = plpy.execute(involved_model_sql)
    grid_data = dict()
    for item in involved_model:
        current_grid_sql = grid_sql + '\'{model_id}\''.format(model_id=item.get('fid'))
        single_model_grid = plpy.execute(current_grid_sql)
        grid_data[item.get('fid')] = single_model_grid
    result = check_grid_upright(involved_model, grid_data)
    return involved_model
$$
    LANGUAGE 'plpython3u' VOLATILE;






