# coding=utf-8
#author:        4N
#createtime:    2021/10/11
#email:         nheweijun@sina.com


from ..models import InsertingLayerName,Columns
from sqlalchemy.orm import Session
import json
from sqlalchemy import distinct

import time

from app.util.component.SQLUtil import SQLUtil

import datetime

from ..models import Table, Database, DES,Task,db,TableVacuate,Process

from app.util.component.StructurePrint import StructurePrint

from osgeo.ogr import DataSource,Layer,Geometry

from app.util.component.VacuateConf import VacuateConf
from app.util.component.GeometryAdapter import GeometryAdapter

import traceback
from osgeo.ogr import DataSource,Layer,FeatureDefn,FieldDefn,Feature
from osgeo import gdal,ogr
import os
import uuid
import configure

from app.util.component.PGUtil import PGUtil
from app.util.component.ZipUtil import ZipUtil
import multiprocessing


def task_consumer():

    running_dict = {}
    sys_session: Session = PGUtil.get_db_session(
        configure.SQLALCHEMY_DATABASE_URI)

    while True:

        try:
            time.sleep(3)

            # 已经结束的进程 从监测中删除
            remove_process = []

            for process, layer_names in running_dict.items():
                if not process.is_alive():
                    for l in layer_names:
                        inserted = sys_session.query(
                            InsertingLayerName).filter_by(name=l).one_or_none()
                        if inserted:
                            sys_session.delete(inserted)
                    sys_session.commit()
                    remove_process.append(process)
            for process in remove_process:
                running_dict.pop(process)


            # 入库进程少于阈值，开启入库进程

            inter_size = sys_session.query(
                distinct(InsertingLayerName.task_guid)).count()

            if inter_size < configure.entry_data_thread:
                # 锁表啊
                ready_task: Task = sys_session.query(Task).filter_by(state=0).order_by(
                    Task.create_time).with_lockmode("update").limit(1).one_or_none()
                if ready_task:

                    if ready_task.task_type == 1:
                        task_entry_data(ready_task,sys_session,running_dict)
                    elif ready_task.task_type == 2:
                        task_table_refresh()
                    elif ready_task.task_type == 3:
                        task_vacuate()
                    elif ready_task.task_type == 4:
                        task_download()

                else:
                    # 解表啊
                    sys_session.commit()
        except Exception as e:
            sys_session.commit()
            StructurePrint().print(e.__str__(), "error")



def task_download(para,task_guid):
    sys_session = None
    ds: DataSource = None

    # 设置编码
    encoding = para.get("encoding")
    if encoding:
        gdal.SetConfigOption("SHAPE_ENCODING", encoding)
    else:
        gdal.SetConfigOption("SHAPE_ENCODING", "UTF-8")

    try:

        sys_session = PGUtil.get_db_session(configure.SQLALCHEMY_DATABASE_URI)

        table_names = para.get("table_name").split(",")
        database_guid = para.get("database_guid")
        database = sys_session.query(Database).filter_by(guid=database_guid).one_or_none()
        if not database:
            raise Exception("数据库不存在！")

        ds: DataSource = PGUtil.open_pg_data_source(0, DES.decode(database.sqlalchemy_uri))

        download_type = para.get("download_type")

        data = None
        if download_type.__eq__("shp"):
            data = download_shp(table_names, ds)
        if download_type.__eq__("gdb"):
            data = download_gdb(sys_session, table_names, ds, database_guid)

        sys_session.query(Task).filter_by(guid=task_guid).update({"state": 1, "update_time": datetime.datetime.now(),
                                                                  "process": "下载完成",
                                                                  "parameter": data[0]["download_url"]})
        sys_session.commit()


    except Exception as e:
        try:
            sys_session.query(Task).filter_by(guid=task_guid).update(
                {"state": -1, "update_time": datetime.datetime.now(),
                 "process": "下载失败"})

            message = "{} {}".format(datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'), e.__str__())
            task_process_guid = uuid.uuid1().__str__()
            task_process = Process(guid=task_process_guid, message=message, time=datetime.datetime.now(),
                                   task_guid=task_guid)
            sys_session.add(task_process)
            sys_session.commit()
        except Exception as ee:
            print(traceback.format_exc())
        raise e
    finally:
        try:
            if ds:
                ds.Destroy()
            if sys_session:
                sys_session.close()
        except:
            print(traceback.format_exc())


def download_shp(table_names, ds):
    data = []
    for table_name in table_names:
        url = download_one(ds, table_name)
        data.append({"name": table_name, "download_url": url})
    return data


def download_one( ds, table_name):
    layer: Layer = ds.GetLayerByName(table_name)
    driver = ogr.GetDriverByName("ESRI Shapefile")
    uuid_ = uuid.uuid1().__str__()
    parent = os.path.dirname(os.path.dirname(os.path.realpath(__file__)))
    dirpath = os.path.join(parent, "file_tmp", uuid_)
    os.makedirs(dirpath)
    data_source: DataSource = driver.CreateDataSource(dirpath + "/{}.shp".format(table_name))
    # data_source.CopyLayer(layer, table_name)

    fid = layer.GetFIDColumn()
    pg_layer: Layer = data_source.CreateLayer(table_name, layer.GetSpatialRef(), layer.GetGeomType())
    schema = [sche for sche in layer.schema if not sche.name.__eq__(fid)]

    pg_layer.CreateFields(schema)
    layer.ResetReading()
    for feature in layer:
        pg_layer.CreateFeature(feature)

    data_source.Destroy()

    ZipUtil.create_zip(os.path.join(parent, "file_tmp", table_name + "_" + uuid_) + ".zip", [dirpath])

    return "http://" + configure.deploy_ip_host + "/API/IO/Download/{}".format(table_name + "_" + uuid_ + ".zip")


def download_gdb( sys_session, table_names, ds, database_guid):
    ogr.RegisterAll()
    data = []
    gdal.UseExceptions()
    gdal.SetConfigOption("GDAL_FILENAME_IS_UTF8", "YES")

    # 创建一个gdb datasource
    gdb_driver = ogr.GetDriverByName('FileGDB')
    uuid_ = uuid.uuid1().__str__()
    parent = os.path.dirname(os.path.dirname(os.path.realpath(__file__)))
    gdb_path = os.path.join(parent, "file_tmp", uuid_ + ".gdb")

    gdb_ds: DataSource = gdb_driver.CreateDataSource(gdb_path)

    for table_name in table_names:

        layer: Layer = ds.GetLayerByName(table_name)
        table = sys_session.query(Table).filter_by(name=table_name, database_guid=database_guid).one_or_none()
        feature_defn: FeatureDefn = layer.GetLayerDefn()

        for i in range(feature_defn.GetFieldCount()):
            field_defn: FieldDefn = feature_defn.GetFieldDefn(i)
            field_alias = sys_session.query(Columns).filter_by(table_guid=table.guid,
                                                               name=field_defn.GetName()).one_or_none().alias
            field_defn.SetAlternativeName(field_alias)

        table_alias = table.alias


        fid = layer.GetFIDColumn()
        pg_layer: Layer = gdb_ds.CreateLayer(table_name, layer.GetSpatialRef(), layer.GetGeomType(),
                                             ["LAYER_ALIAS={}".format(table_alias)])
        schema = [sche for sche in layer.schema if not sche.name.__eq__(fid)]
        # schema = layer.schema
        pg_layer.CreateFields(schema)

        # gdb 不支持fid=0的要素，所以识别到后要+1
        offset = 0
        f1: Feature = layer.GetNextFeature()
        if f1:
            if f1.GetFID().__eq__(0):
                offset = 1
        layer.ResetReading()
        for feature in layer:
            feature.SetFID(feature.GetFID() + offset)
            pg_layer.CreateFeature(feature)



    gdb_ds.Destroy()
    ZipUtil.create_zip(gdb_path + ".zip", [gdb_path])
    data.append({"name": ",".join(table_names),
                 "download_url": "http://" + configure.deploy_ip_host + "/API/IO/Download/{}".format(
                     uuid_ + ".gdb" + ".zip")})

    return data


def task_entry_data(ready_task,sys_session,running_dict):
    try:
        parameter = json.loads(ready_task.parameter)
        StructurePrint().print("检测到入库任务")
        ready_task.state = 2
        ready_task.process = "入库中"
        sys_session.commit()

        metas: list = json.loads(
            parameter.get("meta").__str__())
        parameter["meta"] = metas

        database = sys_session.query(Database).filter_by(
            guid=ready_task.database_guid).one_or_none()
        pg_ds: DataSource = PGUtil.open_pg_data_source(
            1, DES.decode(database.sqlalchemy_uri))

        this_task_layer = []
        for meta in metas:
            overwrite = parameter.get("overwrite", "no")

            for layer_name_origin, layer_name in meta.get("layer").items():
                origin_name = layer_name
                no = 1

                while (overwrite.__eq__("no") and pg_ds.GetLayerByName(layer_name)) or sys_session.query(
                        InsertingLayerName).filter_by(name=layer_name).one_or_none():
                    layer_name = origin_name + "_{}".format(no)
                    no += 1

                # 添加到正在入库的列表中
                iln = InsertingLayerName(guid=uuid.uuid1().__str__(),
                                         task_guid=ready_task.guid,
                                         name=layer_name)

                sys_session.add(iln)
                sys_session.commit()
                this_task_layer.append(layer_name)
                # 修改表名
                meta["layer"][layer_name_origin] = layer_name

        pg_ds.Destroy()
        entry_data_process = multiprocessing.Process(
            target=EntryDataVacuate().entry, args=(parameter,))
        entry_data_process.start()
        running_dict[entry_data_process] = this_task_layer
    except Exception as e:
        sys_session.query(Task).filter_by(guid=ready_task.guid).update(
            {"state": -1, "process": "入库失败"})
        sys_session.commit()
        StructurePrint().print(e.__str__(), "error")


def task_table_refresh(database,task_guid):
    pg_ds =None
    sys_ds =None
    data_session=None
    result = {}
    sys_session = None
    db_tuple = PGUtil.get_info_from_sqlachemy_uri(DES.decode(database.sqlalchemy_uri))

    try:
        sys_session = PGUtil.get_db_session(configure.SQLALCHEMY_DATABASE_URI)
        sys_ds = PGUtil.open_pg_data_source(0,configure.SQLALCHEMY_DATABASE_URI)

        this_time = datetime.datetime.now()
        database_guid = database.guid

        # 已注册空间表
        spatial_tables = sys_session.query(Table).order_by(Table.create_time.desc()).filter_by(database_guid=database_guid).filter(
            Table.table_type != 0).all()

        # 已注册空间表名
        spatial_tables_names = [table.name for table in spatial_tables]

        # 实体库datasource
        pg_ds: DataSource = PGUtil.open_pg_data_source(1, DES.decode(database.sqlalchemy_uri))

        # 更新空间表
        # 增加表
        db_tables_names = add_spatail_table(database, pg_ds, sys_session,spatial_tables_names, this_time,db_tuple)# 实体库中空间表名

        # 删除/修改表
        edit_spatial_table(pg_ds, sys_session,spatial_tables, db_tables_names, this_time,db_tuple)

        # 空间表处理完毕
        sys_session.commit()


        # 空间表处理完毕
        sys_session.commit()

        # 注册普通表
        # 实体库连接
        data_session: Session = PGUtil.get_db_session(DES.decode(database.sqlalchemy_uri))

        # 处理后空间表
        spatial_tables = sys_session.query(Table).order_by(Table.create_time.desc()).filter_by(database_guid=database_guid).filter(
            Table.table_type != 0).all()
        # 处理后空间表名
        spatial_tables_names = [table.name for table in spatial_tables]

        # 原有普通表
        common_tables = sys_session.query(Table).order_by(Table.create_time.desc()).filter_by(database_guid=database_guid).filter(
            Table.table_type == 0).all()
        # 原有普通表 名
        origin_common_tables_name = [table.name for table in common_tables]

        # 现有普通表
        real_common_tables_name = []

        # 只注册public中的表
        common_result = data_session.execute(
            "select relname as tabname from pg_class c where  relkind = 'r' and relnamespace=2200 and  relname not like 'pg_%' and relname not like 'sql_%' order by relname").fetchall()
        for re in common_result:
            table_name = re[0]
            if table_name not in spatial_tables_names and (not table_name.__contains__("_vacuate_")):
                real_common_tables_name.append(table_name)

        # 增加新普通表

        add_common_table(data_session, sys_session, database_guid, real_common_tables_name, origin_common_tables_name,
                              this_time,db_tuple)

        # 删除、修改普通表
        edit_common_table(data_session,sys_session, database_guid, real_common_tables_name, origin_common_tables_name,
                               this_time,db_tuple)

        sys_session.commit()
        result["data"] = "刷新数据成功！"
        result["state"] = 1
        sys_session.query(Task).filter_by(guid=task_guid).update(
            {"state": 1, "update_time": datetime.datetime.now(),"process":"更新成功"})
        sys_session.commit()

    except Exception as e:
        try:
            print(traceback.format_exc())
            sys_session.query(Task).filter_by(guid=task_guid).update(
                {"state": -1, "update_time": datetime.datetime.now(),"process":"更新失败"})
            message = "{} {}".format(datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'), e.__str__())
            task_process_guid = uuid.uuid1().__str__()
            task_process = Process(guid=task_process_guid, message=message, time=datetime.datetime.now(),
                                   task_guid=task_guid)
            sys_session.add(task_process)
            sys_session.commit()
        except Exception as ee:
            print(traceback.format_exc())
    finally:
        if pg_ds:
            pg_ds.Destroy()
        if data_session:
            data_session.close()
        if sys_session:
            sys_session.close()
        if sys_ds:
            sys_ds.Destroy()
    return result

def add_spatail_table(database,pg_ds,sys_session,spatial_tables_names,this_time,db_tuple):
    '''
    注册新增空间表
    :param database:
    :param pg_ds:
    :param spatial_tables_names: 已注册空间表名
    :param this_time:
    :return: 实体库中空间表名
    '''

    db_tables_names=[]

    for i in range(pg_ds.GetLayerCount()):
        layer: Layer = pg_ds.GetLayer(i)
        geom_column = layer.GetGeometryColumn()
        db_tables_names.append(layer.GetName())
        if not geom_column:
            continue
        if layer.GetName() not in spatial_tables_names:
            l_name = layer.GetName()

            try:
                # 只注册public的空间表,其他表空间的表名会有.
                if layer.GetName().__contains__("."):
                    continue
                # 略过抽稀表
                if layer.GetName().__contains__("_vacuate_"):
                    continue

                # 没有权限的表跳过
                if not PGUtil.check_table_privilege(l_name, "SELECT", db_tuple[0], pg_ds):
                    StructurePrint().print("用户{}对表{}没有select权限！".format(db_tuple[0], l_name), "warn")
                    continue


                # 范围统计和数量统计以100w为界限
                query_count_layer: Layer = pg_ds.ExecuteSQL(
                    '''SELECT reltuples::bigint AS ec FROM pg_class WHERE  oid = 'public."{}"'::regclass'''.format(
                        l_name))

                feature_count = query_count_layer.GetFeature(0).GetField("ec")
                # 要素少于100w可以精确统计
                if feature_count < 1000000:
                    feature_count = layer.GetFeatureCount()
                    ext = layer.GetExtent()
                else:
                    query_ext_layer: Layer = pg_ds.ExecuteSQL(
                        "select geometry(ST_EstimatedExtent('public', '{}','{}'))".format(l_name,
                                                                                          layer.GetGeometryColumn()))
                    ext = query_ext_layer.GetExtent()
                if ext[0] < 360:
                    ext = [round(e, 6) for e in ext]
                else:
                    ext = [round(e, 2) for e in ext]
                extent = "{},{},{},{}".format(ext[0], ext[1], ext[2], ext[3])

                StructurePrint().print("空间表增加！")

                geom_type = GeometryAdapter.get_geometry_type(layer)
            except:
                StructurePrint().print("表{}注册失败！".format(l_name), "warn")
                continue

            table_guid = uuid.uuid1().__str__()
            table = Table(guid=table_guid,
                          database_guid=database.guid,
                          # alias=layer.GetName(),
                          name=layer.GetName(), create_time=this_time, update_time=this_time,
                          table_type=GeometryAdapter.get_table_type(geom_type),
                          extent=extent,
                          feature_count=feature_count
                          )
            sys_session.add(table)
            feature_defn: FeatureDefn = layer.GetLayerDefn()

            for i in range(feature_defn.GetFieldCount()):
                field_defn: FieldDefn = feature_defn.GetFieldDefn(i)
                field_name = field_defn.GetName()
                field_alias = field_name if field_defn.GetAlternativeName() is None or field_defn.GetAlternativeName().__eq__(
                    "") else field_defn.GetAlternativeName()
                column = Columns(guid=uuid.uuid1().__str__(), table_guid=table_guid,
                                 name=field_name, alias=field_alias, create_time=this_time, update_time=this_time)
                sys_session.add(column)
    return db_tables_names

def deal_vacuate_table(sys_ds,sys_session,database_guid):


    for i in range(sys_ds.GetLayerCount()):
        layer: Layer = sys_ds.GetLayer(i)
        geom_column = layer.GetGeometryColumn()

        if not geom_column:
            continue



        if layer.GetName().__contains__("_vacuate_"):
            l_name = layer.GetName()

            base_layer_name = l_name.split("_vacuate_")[0].split("_")[1]

            level = l_name.split("_")[-2]

            pixel_distance_str: str ="0"
            try:
                pixel_distance_str: str = l_name.split("_")[-1]
                if pixel_distance_str.startswith("0"):
                    pixel_distance_str = "0.{}".format(pixel_distance_str)
            except:
                pass

            base_table =sys_session.query(Table).filter_by(name=base_layer_name,database_guid=database_guid).one_or_none()
            if base_table:
                if not sys_session.query(TableVacuate).filter_by(table_guid=base_table.guid,name=l_name).one_or_none():
                    table_vacuate = TableVacuate(guid=uuid.uuid1().__str__(),
                                                 table_guid=base_table.guid,
                                                 level=level,
                                                 name=l_name,
                                                 pixel_distance=float(pixel_distance_str))
                    sys_session.add(table_vacuate)

                    sys_session.query(Table).filter_by(guid=base_table.guid).update({"is_vacuate": 1})
            else:
                kk=1



def edit_spatial_table(pg_ds,sys_session,spatial_tables,db_tables_names,this_time,db_tuple):



    for table in spatial_tables:

        # 删除表
        if table.name not in db_tables_names:
            StructurePrint().print("空间表减少！")
            sys_session.delete(table)
        # 修改表
        else:
            layer: Layer = pg_ds.GetLayerByName(table.name)
            l_name = layer.GetName()

            # 只注册public的空间表,其他表空间的表名会有.
            if layer.GetName().__contains__("."):
                continue

            if layer.GetName().__contains__("_vacuate_"):
                continue

            # 没有权限的表跳过
            if not PGUtil.check_table_privilege(l_name, "SELECT", db_tuple[0], pg_ds):
                StructurePrint().print("用户{}对表{}没有select权限！".format(db_tuple[0], l_name), "warn")
                sys_session.delete(table)
                continue

            columns = table.relate_columns
            columns_names = [column.name for column in columns]
            feature_defn: FeatureDefn = layer.GetLayerDefn()
            db_columns_names = []

            # 增加列
            for i in range(feature_defn.GetFieldCount()):
                field_defn: FieldDefn = feature_defn.GetFieldDefn(i)
                field_name = field_defn.GetName()
                db_columns_names.append(field_name)

                if field_name not in columns_names:
                    StructurePrint().print("{}空间表属性增加！".format(table.name))
                    field_alias = field_name if field_defn.GetAlternativeName() is None or field_defn.GetAlternativeName().__eq__(
                        "") else field_defn.GetAlternativeName()
                    column = Columns(guid=uuid.uuid1().__str__(), table_guid=table.guid,
                                     name=field_name, alias=field_alias, create_time=this_time,
                                     update_time=this_time)
                    sys_session.add(column)

            # 删除列
            for column in columns:
                if column.name not in db_columns_names:
                    StructurePrint().print("{}空间表属性减少！".format(table.name))
                    sys_session.delete(column)

            # 范围统计和数量统计以100w为界限
            query_count_layer: Layer = pg_ds.ExecuteSQL(
                '''SELECT reltuples::bigint AS ec FROM pg_class WHERE  oid = 'public."{}"'::regclass'''.format(
                    l_name))
            feature_count = query_count_layer.GetFeature(0).GetField("ec")
            # 要素少于100w可以精确统计
            if feature_count < 1000000:
                feature_count = layer.GetFeatureCount()
                ext = layer.GetExtent()

            else:
                query_ext_layer: Layer = pg_ds.ExecuteSQL(
                    "select geometry(ST_EstimatedExtent('public', '{}','{}'))".format(l_name,
                                                                                      layer.GetGeometryColumn()))
                ext = query_ext_layer.GetExtent()
            if ext[0] < 360:
                ext = [round(e, 6) for e in ext]
            else:
                ext = [round(e, 2) for e in ext]
            extent = "{},{},{},{}".format(ext[0], ext[1], ext[2], ext[3])

            # 修改要素量
            if not table.feature_count.__eq__(feature_count):
                StructurePrint().print("{}空间表要素！".format(table.name))
                sys_session.query(Table).filter_by(guid=table.guid).update({"feature_count": feature_count,
                                                               "extent": extent})


def add_common_table(data_session,sys_session,database_guid,real_common_tables_name,origin_common_tables_name,this_time,db_tuple):
    for table_name in real_common_tables_name:
        if table_name not in origin_common_tables_name:
            StructurePrint().print("{}非空间表增加！".format(table_name))
            table_guid = uuid.uuid1().__str__()


            # 没有权限的表跳过
            if not SQLUtil.check_table_privilege(table_name, "SELECT", db_tuple[0], data_session):
                StructurePrint().print("用户{}对表{}没有select权限！".format(db_tuple[0], table_name), "warn")
                continue

            count = data_session.execute('select count(*) from "{}"'.format(table_name)).fetchone()[0]

            table = Table(guid=table_guid,
                          database_guid=database_guid,
                          name=table_name, create_time=this_time, update_time=this_time,
                          table_type=0,
                          feature_count=count
                          )

            sys_session.add(table)

            sql = '''
               SELECT
                   a.attnum,
                   a.attname AS field
               FROM
                   pg_class c,
                   pg_attribute a,
                   pg_type t
               WHERE
                   c.relname = '{}'
                   and a.attnum > 0
                   and a.attrelid = c.oid
                   and a.atttypid = t.oid
               ORDER BY a.attnum 
               '''.format(table_name)

            cols = data_session.execute(sql).fetchall()
            for col in cols:
                column = Columns(guid=uuid.uuid1().__str__(), table_guid=table_guid,
                                 name=col[1], create_time=this_time, update_time=this_time)
                sys_session.add(column)

            # 删除不存在的表
            for n in origin_common_tables_name:
                if n not in real_common_tables_name:
                    tables = Table.query.filter_by(name=n).filter_by(database_guid=database_guid).all()
                    for table in tables:
                        sys_session.delete(table)

def edit_common_table(data_session,sys_session,database_guid,real_common_tables_name,origin_common_tables_name,this_time,db_tuple):
    for table_name in origin_common_tables_name:
        tables = sys_session.query(Table).filter_by(name=table_name).filter_by(database_guid=database_guid).all()
        for table in tables:
            if table_name not in real_common_tables_name:
                StructurePrint().print("{}非空间表减少！".format(table_name))
                sys_session.delete(table)
            # 修改表
            else:

                # 没有权限的表删除
                if not SQLUtil.check_table_privilege(table_name, "SELECT", db_tuple[0], data_session):
                    StructurePrint().print("用户{}对表{}没有select权限！".format(db_tuple[0], table_name), "warn")
                    sys_session.delete(table)
                    continue

                columns = table.relate_columns
                columns_names = [column.name for column in columns]

                sql = '''
                   SELECT
                       a.attnum,
                       a.attname AS field
                   FROM
                       pg_class c,
                       pg_attribute a,
                       pg_type t
                   WHERE
                       c.relname = '{}'
                       and a.attnum > 0
                       and a.attrelid = c.oid
                       and a.atttypid = t.oid
                   ORDER BY a.attnum 
                   '''.format(table_name)

                cols = data_session.execute(sql).fetchall()
                real_cols_name = [col[1] for col in cols]

                # 属性增加
                for col in real_cols_name:
                    if col not in columns_names:
                        StructurePrint().print("{}表要素属性增加！".format(table_name))
                        column = Columns(guid=uuid.uuid1().__str__(), table_guid=table.guid,
                                         name=col, create_time=this_time, update_time=this_time)
                        sys_session.add(column)

                # 属性减少
                for column in columns:
                    if column.name not in real_cols_name:
                        StructurePrint().print("{}表要素属性减少！".format(table_name))
                        sys_session.delete(column)

                # 修改要素量
                # sql = 'select count(*) from "{}"'.format(table_name)
                # count = data_session.execute(sql).fetchone()[0]

                count = SQLUtil.get_table_count(table_name,data_session)

                if not table.feature_count.__eq__(count):
                    StructurePrint().print("{}表要素变化！".format(table_name))
                    sys_session.query(Table).filter_by(guid=table.guid).update({"feature_count": count})


def task_vacuate(table,task_guid):

    sys_session = None
    pg_session = None
    pg_ds = None
    vacuate_process = None
    try:
        sys_session = PGUtil.get_db_session(configure.SQLALCHEMY_DATABASE_URI)
        sys_session.query(Table).filter_by(guid=table.guid).update(
            {"is_vacuate": 2, "update_time": datetime.datetime.now()})
        sys_session.commit()

        database = sys_session.query(Database).filter_by(guid=table.database_guid).one_or_none()
        pg_session = PGUtil.get_db_session(DES.decode(database.sqlalchemy_uri))

        pg_ds: DataSource = PGUtil.open_pg_data_source(0, DES.decode(database.sqlalchemy_uri))

        # 删除原有数据
        tvs = sys_session.query(TableVacuate).filter_by(table_guid=table.guid).all()
        for tv in tvs:
            sys_session.delete(tv)


        # 创建抽稀过程
        options = ["OVERWRITE=yes", "GEOMETRY_NAME={}".format(PGUtil.get_geo_column(table.name, pg_session)),
                   "PRECISION=NO"]

        layer = pg_ds.GetLayerByName(table.name)

        vacuate_process: VacuateProcess = VacuateProcess(layer, table.guid, options, database.sqlalchemy_uri)

        for feature in layer:
            geo = feature.GetGeometryRef()
            # 插入抽稀图层
            if geo is not None:
                vacuate_process.vacuate(geo, feature)

        vacuate_process.set_vacuate_count()

        # 新增
        if configure.VACUATE_DB_URI:
            user, passwd, host, port, datab = PGUtil.get_info_from_sqlachemy_uri(configure.VACUATE_DB_URI)
        else:
            user, passwd, host, port, datab = PGUtil.get_info_from_sqlachemy_uri(DES.decode(database.sqlalchemy_uri))
        connectstr = "hostaddr={} port={} dbname='{}' user='{}' password='{}'".format(host, port, datab, user,
                                                                                      passwd)
        for l in range(vacuate_process.max_level):
            lev = vacuate_process.t_grid_size.index(vacuate_process.this_gridsize[l])

            table_vacuate = TableVacuate(guid=uuid.uuid1().__str__(),
                                         table_guid=table.guid,
                                         level=lev,
                                         name=vacuate_process.vacuate_layers[l].GetName(),
                                         pixel_distance=vacuate_process.this_gridsize[l],
                                         connectstr=DES.encode(connectstr))
            sys_session.add(table_vacuate)

        sys_session.query(Task).filter_by(guid=task_guid).update({"state": 1, "update_time": datetime.datetime.now(),
                                                                  "process": "精化完成"})
        sys_session.query(Table).filter_by(guid=table.guid).update(
            {"is_vacuate": 1, "update_time": datetime.datetime.now()})
        sys_session.commit()

    except Exception as e:
        try:
            sys_session.query(Task).filter_by(guid=task_guid).update(
                {"state": -1, "update_time": datetime.datetime.now(),
                 "process": "精化失败"})
            sys_session.query(Table).filter_by(guid=table.guid).update(
                {"is_vacuate": 0, "update_time": datetime.datetime.now()})

            message = "{} {}".format(datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'), e.__str__())
            task_process_guid = uuid.uuid1().__str__()
            task_process = Process(guid=task_process_guid, message=message, time=datetime.datetime.now(),
                                   task_guid=task_guid)
            sys_session.add(task_process)
            sys_session.commit()
            if vacuate_process:
                vacuate_process.rollback()

            print(traceback.format_exc())
        except Exception as ee:
            print(traceback.format_exc())
    finally:
        if vacuate_process:
            vacuate_process.end()
        if sys_session:
            sys_session.close()
        if pg_session:
            pg_session.close()
        if pg_ds:
            pg_ds.Destroy()


class VacuateProcess:

    max_level=0
    fill_dict={}
    vacuate_layers={}
    vacuate_layers_gridsize={}
    pg_ds_dict = {}
    # 图层要素大于5W才抽稀
    least_vacuate_count = VacuateConf.least_vacuate_count

    extent=[]
    is_spatial=False

    lonlat_gridsize = VacuateConf.lonlat_gridsize
    project_gridsize = VacuateConf.project_gridsize

    # 该抽稀过程使用的grid_size
    t_grid_size = []

    # 该抽稀过程的抽稀网格
    this_gridsize=[]


    def __init__(self,layer:Layer,table_guid, options,sqlalchemy_uri):

        #是空间图层才初始化
        if layer.GetExtent()[0] > 0 or layer.GetExtent()[0] < 0:

            self.is_spatial=True

            # 判断需要抽稀多少级

            lc = layer.GetFeatureCount()
            extent = layer.GetExtent()
            self.extent=extent

            #判断疏密程度
            p_x = (extent[1]-extent[0])/10.0
            p_y = (extent[3] - extent[2]) / 10.0
            fill_precent=0
            StructurePrint().print("判断疏密")
            for ix in range(10):
                for iy in range(10):
                    grid_extent = [extent[0]+ix*p_x,extent[0]+ix*p_x+p_x,extent[2]+iy*p_y,extent[2]+iy*p_y+p_y]
                    poly = GeometryAdapter.envelop_2_polygon(grid_extent)

                    layer.SetSpatialFilter(None)
                    layer.SetSpatialFilter(poly)
                    layer.ResetReading()
                    if layer.GetNextFeature():
                        fill_precent += 1

            print(fill_precent)
            StructurePrint().print("判断疏密结束")

            layer.SetSpatialFilter(None)
            layer.ResetReading()
            # 固有疏密程度
            original_density=8


            # 额外一层
            # self.this_gridsize.append(0.000075)
            # self.max_level += 1
            ######

            if extent[0]>180:
                self.t_grid_size=self.project_gridsize
            else:
                self.t_grid_size = self.lonlat_gridsize

            for grid_size in self.t_grid_size:
                # 最少抽稀个数
                if lc > self.least_vacuate_count:
                    # 网格数至少大于
                    if ((extent[1] - extent[0]) * (extent[3] - extent[2])) / (grid_size**2)>self.least_vacuate_count:
                        # 要素数量大于网格数量
                        # 要考虑图层的疏密程度，original_density*(100.0/fill_precent) 为疏密指数
                        if lc * original_density * (100.0/fill_precent)>((extent[1] - extent[0])*(extent[3] - extent[2]))/(grid_size**2) :
                            print(grid_size)
                            self.this_gridsize.append(grid_size)
                            self.max_level += 1



            # 创建抽稀ds
            for l in range(self.max_level):
                # pg_ds_l: DataSource = PGUtil.open_pg_data_source(1, DES.decode(sqlalchemy_uri))
                if configure.VACUATE_DB_URI:
                    pg_ds_l: DataSource = PGUtil.open_pg_data_source(1, configure.VACUATE_DB_URI)
                else:
                    pg_ds_l: DataSource = PGUtil.open_pg_data_source(1, DES.decode(sqlalchemy_uri))
                pg_ds_l.StartTransaction()
                self.pg_ds_dict[l] = pg_ds_l

            # 生成抽稀图层
            options = options[1:]
            options.append("OVERWRITE=yes")
            options.append("LAUNDER=no")

            schema = layer.schema
            # 增加统计字段
            schema.append(ogr.FieldDefn("_dcigrid_count_", ogr.OFTInteger))
            schema.append(ogr.FieldDefn("_dcigrid_name_", ogr.OFTString))

            for l in range(self.max_level):
                this_grid_len = self.this_gridsize[l]

                self.vacuate_layers_gridsize[l] = this_grid_len

                pg = self.pg_ds_dict[l]

                grid_name = str(this_grid_len)
                if this_grid_len<1:
                    grid_name = str(this_grid_len).split(".")[-1]
                    if this_grid_len.__eq__(0.00008):
                        grid_name = "00008"

                # 抽稀图层是点面混合的
                # 抽稀表有固定的命名规则
                # 抽稀表一定要覆盖


                print("{}:{}".format(self.t_grid_size.index(this_grid_len),this_grid_len))


                v_ln = "z{}_vacuate_{}_{}".format(table_guid, self.t_grid_size.index(this_grid_len), grid_name)
                vl = pg.CreateLayer(v_ln, layer.GetSpatialRef(),ogr.wkbUnknown, options)
                # 抽稀表需要属性
                vl.CreateFields(schema)
                self.vacuate_layers[l] = vl

        else:
            pass


    def vacuate(self,g,feature):

        if self.is_spatial:

            # 插入到所有抽稀图层中
            for level in range(self.max_level):

                center: Geometry = g.Centroid()

                extent = g.GetEnvelope()
                long_extent= extent[1]-extent[0]
                lat_extent = extent[3]-extent[2]

                this_grid_len =self.vacuate_layers_gridsize[level]
                #超大的直接加入
                # if long_extent > 10*this_grid_len or lat_extent >10*this_grid_len:
                #     vacuate_layer: Layer = self.vacuate_layers.get(level)
                #     feat = ogr.Feature(vacuate_layer.GetLayerDefn())
                #     feat.SetGeometry(g)
                #     vacuate_layer.CreateFeature(feat)
                # else:

                row = int((center.GetY() - self.extent[2]) / this_grid_len)
                col = int((center.GetX() - self.extent[0]) / this_grid_len)
                key = "{}.{}.{}".format(level, row, col)

                if not self.fill_dict.get(key):
                    self.fill_dict[key] = 0
                if self.fill_dict[key] == 0:

                    vacuate_layer: Layer = self.vacuate_layers.get(level)
                    feat = ogr.Feature(vacuate_layer.GetLayerDefn())
                    # 如果图形比网格小，直接存储其中心点
                    if this_grid_len>long_extent and this_grid_len>lat_extent:
                        feat.SetGeometry(center)
                    else:
                        feat.SetGeometry(g)

                    # 复制旧feature属性
                    field_dict = feature.items()
                    for field_name in field_dict:
                        feat.SetField(field_name, field_dict[field_name])
                    feat.SetField("_dcigrid_name_",".".join(key.split(".")[1:]))

                    vacuate_layer.CreateFeature(feat)
                    self.fill_dict[key] += 1
                #超大的还有机会
                elif (long_extent > 10*this_grid_len or lat_extent >10*this_grid_len) and self.fill_dict[key]<5:
                    vacuate_layer: Layer = self.vacuate_layers.get(level)
                    feat = ogr.Feature(vacuate_layer.GetLayerDefn())
                    feat.SetGeometry(g)

                    # 复制旧feature属性
                    field_dict = feature.items()
                    for field_name in field_dict:
                        feat.SetField(field_name, field_dict[field_name])
                    feat.SetField("_dcigrid_name_",".".join(key.split(".")[1:]))

                    vacuate_layer.CreateFeature(feat)
                    self.fill_dict[key] += 1
                else:
                    self.fill_dict[key] += 1

    def set_vacuate_count(self):
        if self.is_spatial:
            # 插入到所有抽稀图层中
            for level in range(self.max_level):
                vacuate_layer: Layer = self.vacuate_layers.get(level)
                for feat in vacuate_layer:
                    key = "{}.{}".format(level,feat.GetField("_dcigrid_name_"))
                    feat.SetField("_dcigrid_count_",self.fill_dict.get(key))
                    vacuate_layer.SetFeature(feat)

    def end(self):
        for pg in self.pg_ds_dict.values():
            pg.Destroy()

    def rollback(self):
        for pg in self.pg_ds_dict.values():
            pg.RollbackTransaction()