dbms_sanya/python/plugins/ResourceTree/ui/ResourceTree.py

# -*- coding: utf-8 -*-
import ftplib
import inspect
import uuid
import os
import shutil
import subprocess
from PyQt5.QtWidgets import QMessageBox
import psycopg2
import redis
from types import MethodType
from PyQt5 import QtCore, QtGui, QtWidgets
from PyQt5.QtCore import QSize, QUrl, Qt, QTimer
from PyQt5.QtGui import QIcon, QDrag
from PyQt5.QtWidgets import QVBoxLayout, QHBoxLayout, QPushButton, QTreeWidget, QTreeWidgetItem, QMessageBox, \
    QInputDialog, QLineEdit, QWidget, QFileDialog, QMenu, QDialog, QFormLayout, QLabel, QTimeEdit, QDateTimeEdit
# 导入QGIS相关模块
from qgis.core import QgsProject, QgsVectorLayer, QgsRasterLayer
from .TreeWidget import Tree
from .FtpUitl import FtpOper
from .FtpConfig import *
from .PostgreSQL import PostgreSQL
from qgis.utils import iface
from qgis.core import QgsRectangle, QgsCoordinateReferenceSystem, QgsCoordinateTransform, QgsProject, QgsVectorLayer
from qgis import processing
from .CombinedDialog import CombinedDialog
from PyQt5.QtWidgets import QAbstractItemView


# from .ModelWebView import ModelWebView

current_directory = os.path.dirname(os.path.abspath(inspect.getfile(inspect.currentframe())))  # 获取当前路径



class FormDialog(QDialog):
    def __init__(self, data):
        super().__init__()
        self.setWindowTitle("元数据信息")
        # 设置固定宽度和高度
        self.setFixedWidth(450)  # 设置固定宽度为 400
        self.setFixedHeight(300)  # 设置固定高度为 300
        # 创建表单布局
        self.layout = QFormLayout()
        # 创建输入框
        self.type_line_edit = QLineEdit(self)
        self.type_line_edit.setText(data[13])
        self.tablename_line_edit = QLineEdit(self)
        self.tablename_line_edit.setText(data[0])
        self.tablename_line_edit.setReadOnly(True)
        self.tablealias_line_edit = QLineEdit(self)
        self.tablealias_line_edit.setText(data[12])
        self.xzqfield_line_edit = QLineEdit(self)
        self.xzqfield_line_edit.setText(data[7])
        self.sjly_line_edit = QLineEdit(self)
        self.sjly_line_edit.setText(data[2])
        self.sjsx_line_edit = QLineEdit(self)
        self.sjsx_line_edit.setText(data[1])
        self.ywlx_line_edit = QLineEdit(self)
        self.ywlx_line_edit.setText(data[8])
        self.glbm_line_edit = QLineEdit(self)
        self.glbm_line_edit.setText(data[11])
        self.sjxzqh_line_edit = QLineEdit(self)
        self.sjxzqh_line_edit.setText(data[3])
        self.rksj_time_edit = QDateTimeEdit(self)
        self.rksj_time_edit.setDateTime(data[5])
        self.rksj_time_edit.setReadOnly(True)
        # 将控件添加到表单布局
        self.layout.addRow(QLabel("数据类型:"), self.type_line_edit)
        self.layout.addRow(QLabel("数据库表名:"), self.tablename_line_edit)
        self.layout.addRow(QLabel("表别名:"), self.tablealias_line_edit)
        self.layout.addRow(QLabel("行政区字段:"), self.xzqfield_line_edit)
        self.layout.addRow(QLabel("数据来源:"), self.sjly_line_edit)
        self.layout.addRow(QLabel("数据时效:"), self.sjsx_line_edit)
        self.layout.addRow(QLabel("业务类型:"), self.ywlx_line_edit)
        self.layout.addRow(QLabel("管理部门:"), self.glbm_line_edit)
        self.layout.addRow(QLabel("所属行政区:"), self.sjxzqh_line_edit)
        self.layout.addRow(QLabel("入库时间:"), self.rksj_time_edit)
        # 创建修改按钮
        # self.edit_button = QPushButton("修改", self)
        # self.edit_button.clicked.connect(self.on_edit)
        # self.layout.addWidget(self.edit_button)
        # 设置对话框的主布局
        self.setLayout(self.layout)

    def on_edit(self):
        # 获取输入的内容
        name = self.tablealias_line_edit.text()
        # 打印输入内容
        print(f"姓名: {name}")
        # 关闭对话框
        self.accept()


class Ui_ResourceDockWidget(object):
    contexMenu = None
    # **修改1: 延迟初始化Redis连接**
    redis_client = None
    tableattr = None
    dbcoon = {
        "dbname": "real3d",
        "user": "postgres",
        "password": "postgis",
        "host": "192.168.60.2",
        "port": "5432",
        "schema": "base"
    }

    # **修改2: 添加数据库连接池标志**
    _db_connection = None
    _tree_initialized = False

    def setupUi(self, ResourceDockWidget):
        ResourceDockWidget.setObjectName("ResourceDockWidget")
        font = QtGui.QFont()
        font.setFamily("微软雅黑")
        ResourceDockWidget.setFont(font)
        self.dockWidgetContents = QtWidgets.QWidget()
        self.dockWidgetContents.setObjectName("resourceDockWidgetContents")
        # 添加FTP管理界面
        self.mainLayout = QVBoxLayout(self.dockWidgetContents)
        self.layoutH = QHBoxLayout()  # 按钮布局
        self.layoutV = QVBoxLayout()  # 树结构的布局

        # **修改3: 延迟初始化树结构**
        self.setupButtons()
        self.setupTreeWidget()
        self.setup_tree_drag_drop()

        ResourceDockWidget.setWidget(self.dockWidgetContents)
        self.retranslateUi(ResourceDockWidget)
        QtCore.QMetaObject.connectSlotsByName(ResourceDockWidget)
        self.setContextMenuPolicy(Qt.CustomContextMenu)  # 右键菜单
        self.customContextMenuRequested.connect(self.rightClickMenu)

        # **修改4: 使用定时器延迟加载数据库数据**
        QTimer.singleShot(100, self.initializeTreeData)

    def setupButtons(self):
        """分离按钮初始化逻辑"""
        # 功能按钮区域
        # 加载到地图
        self.viewButton = QPushButton()
        self.viewButton.setCursor(Qt.PointingHandCursor)
        self.setBtnStyleSheet(self.viewButton)
        self.viewButton.setIcon(QIcon(os.path.join(current_directory + "\\icon", "map.png")))
        self.viewButton.setIconSize(QSize(20, 20))
        self.viewButton.setFixedSize(25, 25)
        self.viewButton.setToolTip("加载到地图")
        self.viewButton.clicked.connect(self.actionViewHandler)
        self.layoutH.addWidget(self.viewButton)
        # 数据统计
        self.staticButton = QPushButton()
        self.staticButton.setCursor(Qt.PointingHandCursor)
        self.setBtnStyleSheet(self.staticButton)
        self.staticButton.setIcon(QIcon(os.path.join(current_directory + "\\icon", "static.png")))
        self.staticButton.setIconSize(QSize(20, 20))
        self.staticButton.setFixedSize(25, 25)
        self.staticButton.setToolTip("数据统计")
        self.staticButton.clicked.connect(self.actionstaticHandler)
        self.layoutH.addWidget(self.staticButton)
        # 元数据
        self.resourceButton = QPushButton()
        self.resourceButton.setCursor(Qt.PointingHandCursor)
        self.setBtnStyleSheet(self.resourceButton)
        self.resourceButton.setIcon(QIcon(os.path.join(current_directory + "\\icon", "resource.png")))
        self.resourceButton.setIconSize(QSize(20, 20))
        self.resourceButton.setFixedSize(25, 25)
        self.resourceButton.setToolTip("元数据")
        self.resourceButton.clicked.connect(self.actionAttrHandler)
        self.layoutH.addWidget(self.resourceButton)
        # 导入矢量
        self.importButton = QPushButton()
        self.importButton.setCursor(Qt.PointingHandCursor)
        self.setBtnStyleSheet(self.importButton)
        self.importButton.setIcon(QIcon(os.path.join(current_directory + "\\icon", "importvector.png")))
        self.importButton.setIconSize(QSize(20, 20))
        self.importButton.setFixedSize(25, 25)
        self.importButton.setToolTip("导入矢量数据")
        self.importButton.clicked.connect(self.actionImportVectorHandler)
        self.layoutH.addWidget(self.importButton)
        # 导入栅格
        self.importRasterButton = QPushButton()
        self.importRasterButton.setCursor(Qt.PointingHandCursor)
        self.setBtnStyleSheet(self.importRasterButton)
        self.importRasterButton.setIcon(QIcon(os.path.join(current_directory + "\\icon", "importimage.png")))
        self.importRasterButton.setIconSize(QSize(20, 20))
        self.importRasterButton.setFixedSize(25, 25)
        self.importRasterButton.setToolTip("导入栅格数据")
        self.importRasterButton.clicked.connect(self.actionImportRasterHandler)
        self.layoutH.addWidget(self.importRasterButton)
        # 导出
        self.exportButton = QPushButton()
        self.exportButton.setCursor(Qt.PointingHandCursor)
        self.setBtnStyleSheet(self.exportButton)
        self.exportButton.setIcon(QIcon(os.path.join(current_directory + "\\icon", "export.png")))
        self.exportButton.setIconSize(QSize(20, 20))
        self.exportButton.setFixedSize(25, 25)
        self.exportButton.setToolTip("导出数据")
        self.exportButton.clicked.connect(self.actionExportHandler)
        self.layoutH.addWidget(self.exportButton)
        # 新建
        self.newButton = QPushButton()
        self.newButton.setCursor(Qt.PointingHandCursor)
        self.setBtnStyleSheet(self.newButton)
        self.newButton.setIcon(QIcon(os.path.join(current_directory + "\\icon", "new.png")))
        self.newButton.setIconSize(QSize(20, 20))
        self.newButton.setFixedSize(25, 25)
        self.newButton.setToolTip("新建节点")
        self.newButton.clicked.connect(self.actionNewNodeHandler)
        self.layoutH.addWidget(self.newButton)
        # 取消选中
        self.unSelectButton = QPushButton()
        self.unSelectButton.setCursor(Qt.PointingHandCursor)
        self.setBtnStyleSheet(self.unSelectButton)
        self.unSelectButton.setIcon(QIcon(os.path.join(current_directory + "\\icon", "unselect.png")))
        self.unSelectButton.setIconSize(QSize(20, 20))
        self.unSelectButton.setFixedSize(25, 25)
        self.unSelectButton.setToolTip("取消选中")
        self.unSelectButton.clicked.connect(self.actionUnselectHandler)
        self.layoutH.addWidget(self.unSelectButton)
        # 刷新树
        self.refreshButton = QPushButton()
        self.refreshButton.setCursor(Qt.PointingHandCursor)
        self.setBtnStyleSheet(self.refreshButton)
        self.refreshButton.setIcon(QIcon(os.path.join(current_directory + "\\icon", "refresh.png")))
        self.refreshButton.setIconSize(QSize(20, 20))
        self.refreshButton.setFixedSize(25, 25)
        self.refreshButton.setToolTip("刷新")
        self.refreshButton.clicked.connect(self.refreshTreeWidget)
        self.layoutH.addWidget(self.refreshButton)
        # 服务发布
        self.publishButton = QPushButton()
        self.publishButton.setCursor(Qt.PointingHandCursor)
        self.setBtnStyleSheet(self.publishButton)
        self.publishButton.setIcon(QIcon(os.path.join(current_directory + "\\icon", "publishserver.png")))
        self.publishButton.setIconSize(QSize(20, 20))
        self.publishButton.setFixedSize(25, 25)
        self.publishButton.setToolTip("刷新")
        self.publishButton.clicked.connect(self.publishserverHandler)
        self.layoutH.addWidget(self.publishButton)
        # 添加动态布局 元素自动左对齐
        self.layoutH.addStretch()

        self.mainLayout.addLayout(self.layoutH)

    def setupTreeWidget(self):
        self.treeWidget = QTreeWidget()
        self.treeWidget.setHeaderLabel("资源目录")
        self.treeWidget.setDragEnabled(True)
        self.treeWidget.setAcceptDrops(True)
        self.treeWidget.setDragDropMode(QTreeWidget.InternalMove)
        self.treeWidget.setDefaultDropAction(Qt.MoveAction)

        self.treeWidget.setDropIndicatorShown(True)
        self.treeWidget.model().rowsMoved.connect(self.on_tree_item_dropped)  # 拖动后保存排序

        self.layoutV.addWidget(self.treeWidget)
        self.treeWidget.itemSelectionChanged.connect(self.parametersHaveChanged)
        self.treeWidget.itemDoubleClicked.connect(self.on_item_double_clicked)
        self.treeWidget.viewport().setAcceptDrops(True)

        self.mainLayout.addLayout(self.layoutV)


    def get_redis_client(self):
        """延迟初始化Redis连接"""
        if self.redis_client is None:
            try:
                self.redis_client = redis.Redis(host='192.168.60.2', port=6379, db=0, socket_timeout=5)
                # 测试连接
                self.redis_client.ping()
            except Exception as e:
                print(f"Redis连接失败: {e}")
                self.redis_client = None
        return self.redis_client

    def get_db_connection(self):
        """获取数据库连接，使用连接池思想"""
        if self._db_connection is None or self._db_connection.close:
            try:
                dbcoon = self.dbcoon
                self._db_connection = PostgreSQL(
                    schema=dbcoon["schema"],
                    host=dbcoon["host"],
                    port=dbcoon["port"],
                    user=dbcoon["user"],
                    password=dbcoon["password"],
                    dbname=dbcoon["dbname"]
                )
            except Exception as e:
                print(f"数据库连接失败: {e}")
                return None
        return self._db_connection


    # 设置按钮样式
    def setBtnStyleSheet(self, btn):
        btn.setStyleSheet("""
            QPushButton {
                border: 2px solid transparent;  /* 默认边框透明 */
                padding: 10px;
                background-color: #f0f0f0;
            }
            QPushButton:hover {
                background-color: #f6f6f6;
                border: 1px solid #958585;  /* 鼠标悬浮时显示蓝色边框 */
            }
        """)

    # 右键菜单
    def rightClickMenu(self, pos):
        try:
            self.currentItem = self.treeWidget.currentItem()
            self.contexMenu = QMenu()
            layertype = self.getNodeType(self.currentItem)
            if (layertype is not None and layertype != ""):
                if layertype == "table":
                    self.actionView = self.contexMenu.addAction('打开属性表')
                    self.actionView.triggered.connect(self.actionViewHandler)
                    actionDelete = self.contexMenu.addAction('删除')
                    actionDelete.triggered.connect(self.actionDeleteTableHandler)
                elif layertype == "osgb":
                    self.actionView = self.contexMenu.addAction('加载三维数据')
                    self.actionView.triggered.connect(self.actionViewHandler)
                    actionDelete = self.contexMenu.addAction('删除')
                    actionDelete.triggered.connect(self.actionDeleteTableHandler1)
                else:
                    self.actionView = self.contexMenu.addAction('加载到地图')
                    self.actionView.triggered.connect(self.actionViewHandler)
                    actionExport = self.contexMenu.addAction('导出数据')
                    actionExport.triggered.connect(self.actionExportHandler)
                    self.actionAttr = self.contexMenu.addAction('元数据信息')
                    self.actionAttr.triggered.connect(self.actionAttrHandler)
                    if layertype == "vector":
                        actionUpdate = self.contexMenu.addAction('数据更新')
                        actionUpdate.triggered.connect(self.actionVectorUpdateHandler)
                        actionRestore = self.contexMenu.addAction('版本回退')
                        actionRestore.triggered.connect(self.actionVectorRestoreHandler)
                        # self.actionAttributeTable = self.contexMenu.addAction('属性表')
                        # self.actionAttributeTable.triggered.connect(self.actionAttrTableHandler)
                    self.actionLayerAttr = self.contexMenu.addAction('图层属性')
                    self.actionLayerAttr.triggered.connect(self.actionLayerAttrHandler)
                    actionDelete = self.contexMenu.addAction('删除')
                    actionDelete.triggered.connect(self.actionDeleteTableHandler)
            else:
                self.actionNewNode = self.contexMenu.addAction('新建资源目录')
                self.actionNewNode.triggered.connect(self.actionNewNodeHandler)
                self.actionImport = self.contexMenu.addAction('导入矢量数据')
                self.actionImport.triggered.connect(self.actionImportVectorHandler)
                self.actionImportRaster = self.contexMenu.addAction('导入栅格数据')
                self.actionImportRaster.triggered.connect(self.actionImportRasterHandler)
                rename = self.contexMenu.addAction('重命名')
                rename.triggered.connect(self.actionRenameNodeHandler)
                delete = self.contexMenu.addAction('删除')
                delete.triggered.connect(self.actionDeleteNodeHandler)
            self.contexMenu.exec_(self.mapToGlobal(pos))
            if self.contexMenu is not None:
                self.contexMenu.show()

        except Exception as e:
            print(e)

    def startDrag(self, supportedActions):
        # 创建一个drag item
        item = self.currentItem()
        print(item)
        if item:
            drag = QDrag(self)
            mimeData = self.model().mimeData([item])
            drag.setMimeData(mimeData)
            drag.exec_(Qt.MoveAction)

    def parametersHaveChanged(self):
        print("资源目录树节点选中变化了")

    def retranslateUi(self, SearchDockWidget):
        _translate = QtCore.QCoreApplication.translate
        SearchDockWidget.setWindowTitle(_translate("SearchDockWidget", "资源目录"))

    def actionUnselectHandler(self):
        self.treeWidget.clearSelection()  # 清除所有选中的项
        self.treeWidget.setCurrentItem(None)  # 清除当前选中的项

    def actionVectorUpdateHandler(self):
        self.deleteMenu()
        print("导入矢量数据操作，调用其他工具面板")
        self.currentItem = self.treeWidget.currentItem()
        params = {}
        if self.currentItem is not None:
            name = self.getNodeId(self.currentItem)
            # **修改8: 使用优化的数据库连接**
            pg = self.get_db_connection()
            if pg is None:
                QMessageBox.warning(None, "错误", "数据库连接失败")
                return
            rows = pg.getManagerTables()
            for i in range(len(rows)):
                row = rows[i]
                if name == row[1]:
                    params = {"TARGET": i}
                    break
        processing.execAlgorithmDialog("gdal:postgisupdate", params)

    def actionVectorRestoreHandler(self):
        self.deleteMenu()
        print("导入矢量数据操作，调用其他工具面板")
        self.currentItem = self.treeWidget.currentItem()
        params = {}
        if self.currentItem is not None:
            name = self.getNodeId(self.currentItem)
            # **修改9: 使用优化的数据库连接**
            pg = self.get_db_connection()
            if pg is None:
                QMessageBox.warning(None, "错误", "数据库连接失败")
                return
            rows = pg.getManagerTables()
            for i in range(len(rows)):
                row = rows[i]
                if name == row[1]:
                    params = {"RESTORETABLE": i}
                    break
            # **修改10: 使用优化的Redis连接**
            redis_client = self.get_redis_client()
            if redis_client:
                try:
                    redis_client.set("curRestoreTable", name, 60 * 30)
                except Exception as e:
                    print(f"Redis操作失败: {e}")
        processing.execAlgorithmDialog("gdal:postgisrestore", params)

    def actionImportVectorHandler(self):
        self.deleteMenu()
        print("导入矢量数据操作，调用其他工具面板")
        self.currentItem = self.treeWidget.currentItem()
        params = {}
        if self.currentItem is not None:
            name = self.getNodeName(self.currentItem)
            # **修改11: 使用优化的数据库连接**
            pg = self.get_db_connection()
            if pg is None:
                QMessageBox.warning(None, "错误", "数据库连接失败")
                return
            rows = pg.getVectorZyml()
            for i in range(len(rows)):
                row = rows[i]
                if name == row[1]:
                    params = {"VECTOR_ZYML": i}
                    break
        processing.execAlgorithmDialog("gdal:importvectorintopostgisdatabaseavailableconnections", params)

    def publishserverHandler(self):
        processing.execAlgorithmDialog("gdal:postgistogeoserver")

    def actionImportRasterHandler(self):
        self.deleteMenu()
        print("导入栅格数据操作，调用其他工具面板")
        self.currentItem = self.treeWidget.currentItem()
        params = {}
        if self.currentItem is not None:
            name = self.getNodeName(self.currentItem)
            # **修改12: 使用优化的数据库连接**
            pg = self.get_db_connection()
            if pg is None:
                QMessageBox.warning(None, "错误", "数据库连接失败")
                return
            rows = pg.getVectorZyml()
            for i in range(len(rows)):
                row = rows[i]
                if name == row[1]:
                    params = {"VECTOR_ZYML": i}
                    break
        processing.execAlgorithmDialog("gdal:importrasterintopostgisdatabase", params)

    def actionExportHandler(self):
        self.deleteMenu()
        print("导出数据操作，调用其他工具面板")
        self.currentItem = self.treeWidget.currentItem()
        if self.currentItem is not None:
            type = self.getNodeType(self.currentItem)
            self.actionViewHandler()
            if type == "vector":
                processing.execAlgorithmDialog("gdal:vectorexport")
            elif type == "raster":
                processing.execAlgorithmDialog("gdal:rasterexport")

    def deleteMenu(self):
        if self.contexMenu is not None:
            self.contexMenu.hide()
            self.contexMenu.close()
            self.contexMenu.clear()
            del self.contexMenu
            # self.contexMenu = None

    def actionDeleteTableHandler(self):
        print("")
        self.deleteMenu()
        self.currentItem = self.treeWidget.currentItem()
        id = self.getNodeId(self.currentItem)
        question = QMessageBox.question(None, '删除确认', '确认删除此项数据资源吗？')
        if question == QMessageBox.Yes:
            # **修改13: 使用优化的数据库连接**
            pg = self.get_db_connection()
            if pg is None:
                QMessageBox.warning(None, "错误", "数据库连接失败")
                return
            pg.dropTable(id)
            self.delete_node_and_children_with_widgets(self.currentItem)

    def actionDeleteTableHandler1(self):
        print("")
        self.deleteMenu()
        self.currentItem = self.treeWidget.currentItem()
        id = self.getNodeId(self.currentItem)
        question = QMessageBox.question(None, '删除确认', '确认删除此项数据资源吗？')
        if question == QMessageBox.Yes:
            # **修改14: 使用优化的数据库连接和错误处理**
            try:
                conn_params = {
                    'host': "192.168.60.2",
                    'port': "5432",
                    'dbname': "real3d",
                    'user': "postgres",
                    'password': "postgis"
                }
                conn = psycopg2.connect(**conn_params)
                cursor = conn.cursor()

                # 删除 t_vector_storage 中对应行（使用参数化查询）
                delete_sql = "DELETE FROM base.t_vector_storage WHERE name = %s"
                cursor.execute(delete_sql, (id,))

                conn.commit()
                cursor.close()
                conn.close()

                self.delete_node_and_children_with_widgets(self.currentItem)
            except Exception as e:
                QMessageBox.warning(None, "错误", f"删除操作失败: {str(e)}")

    def actionRenameNodeHandler(self):
        self.deleteMenu()
        self.currentItem = self.treeWidget.currentItem()
        id = self.getNodeId(self.currentItem)
        oldname = self.getNodeName(self.currentItem)
        newname, ok = QInputDialog.getText(QWidget(), '重命名', '请输入新名称', QLineEdit.Normal, oldname)
        if ok and newname != oldname:
            # **修改15: 使用优化的数据库连接**
            pg = self.get_db_connection()
            if pg is None:
                QMessageBox.warning(None, "错误", "数据库连接失败")
                return
            pg.renameZyml(id, newname)
            self.currentItem.setText(0, newname)

    def actionDeleteNodeHandler(self):
        self.deleteMenu()
        self.currentItem = self.treeWidget.currentItem()
        id = self.getNodeId(self.currentItem)
        # msgBox = QMessageBox()
        # # 自定义按钮
        # msgBox.setStandardButtons(QMessageBox.Yes | QMessageBox.No)
        # msgBox.setButtonText(QMessageBox.Yes, "确定")
        # msgBox.setButtonText(QMessageBox.No, "取消")
        question = QMessageBox.question(None, '删除确认', '确认删除此项目录吗？')
        if question == QMessageBox.Yes:
            # **修改16: 使用优化的数据库连接**
            pg = self.get_db_connection()
            if pg is None:
                QMessageBox.warning(None, "错误", "数据库连接失败")
                return
            pg.deleteZyml(id)
            self.delete_node_and_children_with_widgets(self.currentItem)

    def delete_node_and_children_with_widgets(self, item):
        # 删除节点的所有子节点（递归）
        while item.childCount() > 0:
            child = item.child(0)
            self.delete_node_and_children_with_widgets(child)  # 递归删除子节点

        # 移除小部件（如果有）
        self.treeWidget.removeItemWidget(item, 0)  # 假设小部件在第0列
        if item.parent() is not None:
            item.parent().removeChild(item)  # 删除该节点
        else:
            # 从树中删除该顶级节点
            index = self.treeWidget.indexOfTopLevelItem(item)  # 获取该项的索引
            if index != -1:
                self.treeWidget.takeTopLevelItem(index)  # 删除指定的顶级节点

    def actionNewNodeHandler(self):
        print("新建目录节点")
        self.deleteMenu()
        self.currentItem = self.treeWidget.currentItem()
        print(self.currentItem)
        name, ok = QInputDialog.getText(QWidget(), '资源目录名称', '请输入资源目录名称：')
        if ok:
            uid = self.getUUID()
            pid = ""
            if self.currentItem is not None:
                pid = self.getNodeId(self.currentItem)
            # **修改17: 使用优化的数据库连接**PostgreSQL
            pg = self.get_db_connection()
            if pg is None:
                QMessageBox.warning(None, "错误", "数据库连接失败")
                return
            pg.addZyml(uid, pid, name)
            item = None
            if self.currentItem is not None:
                item = QTreeWidgetItem(self.currentItem)
            else:
                item = QTreeWidgetItem(self.treeWidget)
            item.setText(0, name)
            item.setText(1, uid)
            item.setText(2, "")

    # === 以下为新增支持资源目录排序及保存的函数 ===

    def save_tree_sort_order(self):
        """保存树形结构的排序顺序到数据库"""
        pg = self.get_db_connection()
        if pg is None:
            QMessageBox.warning(None, "错误", "数据库连接失败")
            return False

        try:
            # 开始事务
            pg.execute("BEGIN")

            def save_node_order(item: QTreeWidgetItem, parent_bsm: str, level=0):
                bsm = item.text(1)
                sort_order = 0

                # 获取在父节点中的索引位置作为排序值
                if parent_bsm == '':  # 顶级节点
                    for i in range(self.treeWidget.topLevelItemCount()):
                        if self.treeWidget.topLevelItem(i) == item:
                            sort_order = i
                            break
                else:  # 子节点
                    parent_item = self.find_item_by_bsm(parent_bsm)
                    if parent_item:
                        for i in range(parent_item.childCount()):
                            if parent_item.child(i) == item:
                                sort_order = i
                                break

                # 更新数据库中的父节点和排序
                pg.execute("UPDATE t_vector_zyml SET pbsm = %s, sort = %s WHERE bsm = %s",
                           (parent_bsm, sort_order, bsm))

                print(f"{'  ' * level}更新节点: {item.text(0)} (bsm: {bsm}) -> 父节点: {parent_bsm or 'ROOT'}, 排序: {sort_order}")

                # 递归处理子节点
                for i in range(item.childCount()):
                    save_node_order(item.child(i), bsm, level + 1)

            # 保存所有顶级节点及其子节点
            for i in range(self.treeWidget.topLevelItemCount()):
                top_item = self.treeWidget.topLevelItem(i)
                save_node_order(top_item, '')  # 顶层节点 pbsm 为空字符串

            # 提交事务
            pg.execute("COMMIT")
            print("树形结构排序保存成功")
            return True

        except Exception as e:
            # 回滚事务
            pg.execute("ROLLBACK")
            print(f"保存树形结构排序失败: {e}")
            return False
        finally:
            pg.close()

    def find_item_by_bsm(self, bsm: str) -> QTreeWidgetItem:
        """根据bsm查找树形控件中的项目"""
        def search_item(parent_item):
            if parent_item.text(1) == bsm:
                return parent_item
            for i in range(parent_item.childCount()):
                result = search_item(parent_item.child(i))
                if result:
                    return result
            return None

        # 搜索顶级项目
        for i in range(self.treeWidget.topLevelItemCount()):
            top_item = self.treeWidget.topLevelItem(i)
            if top_item.text(1) == bsm:
                return top_item
            result = search_item(top_item)
            if result:
                return result
        return None

    def setup_tree_drag_drop(self):
        """设置树形控件的拖拽功能"""
        # 启用拖拽功能
        self.treeWidget.setDragDropMode(QAbstractItemView.InternalMove)
        self.treeWidget.setDefaultDropAction(Qt.MoveAction)
        self.treeWidget.setDragEnabled(True)
        self.treeWidget.setAcceptDrops(True)
        self.treeWidget.setDropIndicatorShown(True)

        # 保存原始的dropEvent方法
        original_drop_event = self.treeWidget.dropEvent

        def custom_drop_event(event):
            """自定义拖拽放置事件处理"""
            # 获取拖拽前的信息
            dragged_items = self.treeWidget.selectedItems()
            if not dragged_items:
                return original_drop_event(event)

            dragged_item = dragged_items[0]
            old_parent_bsm = dragged_item.text(2)  # 原父节点bsm
            old_parent_item = None
            old_position = -1

            # 记录拖拽前的位置信息
            if old_parent_bsm == '':  # 原来是顶级节点
                for i in range(self.treeWidget.topLevelItemCount()):
                    if self.treeWidget.topLevelItem(i) == dragged_item:
                        old_position = i
                        break
            else:  # 原来是子节点
                old_parent_item = self.find_item_by_bsm(old_parent_bsm)
                if old_parent_item:
                    for i in range(old_parent_item.childCount()):
                        if old_parent_item.child(i) == dragged_item:
                            old_position = i
                            break

            # 执行原始的拖拽操作
            original_drop_event(event)

            # 获取拖拽后的信息
            new_parent_item = dragged_item.parent()
            new_parent_bsm = new_parent_item.text(1) if new_parent_item else ''
            new_position = -1

            if new_parent_item is None:  # 现在是顶级节点
                for i in range(self.treeWidget.topLevelItemCount()):
                    if self.treeWidget.topLevelItem(i) == dragged_item:
                        new_position = i
                        break
            else:  # 现在是子节点
                for i in range(new_parent_item.childCount()):
                    if new_parent_item.child(i) == dragged_item:
                        new_position = i
                        break

            # 更新节点的pbsm字段（用于界面显示同步）
            dragged_item.setText(2, new_parent_bsm)

            # 判断拖拽类型并处理
            drag_type = self.get_drag_type(old_parent_bsm, new_parent_bsm, old_position, new_position)
            print(f"拖拽类型: {drag_type}")
            print(f"节点 '{dragged_item.text(0)}' 从 {old_parent_bsm or 'ROOT'}[{old_position}] 移动到 {new_parent_bsm or 'ROOT'}[{new_position}]")

            # 实时保存到数据库
            self.handle_drag_operation(drag_type, dragged_item, old_parent_bsm, new_parent_bsm)

        # 替换dropEvent方法
        self.treeWidget.dropEvent = custom_drop_event

    def get_drag_type(self, old_parent_bsm, new_parent_bsm, old_position, new_position):
        """判断拖拽操作类型"""
        if old_parent_bsm == new_parent_bsm:
            return "同级排序"  # 同级目录拖动改变顺序
        elif old_parent_bsm == None and new_parent_bsm != None:
            return "父变子"    # 父节点变子节点
        elif old_parent_bsm != None and new_parent_bsm == None:
            return "子变父"    # 子节点变父节点
        else:
            return "跨级移动"  # 不同层级间的移动

    def handle_drag_operation(self, drag_type, dragged_item, old_parent_bsm, new_parent_bsm):
        """处理不同类型的拖拽操作并实时保存"""
        print(f"\n=== 开始处理拖拽操作: {drag_type} ===")

        pg = self.get_db_connection()
        if pg is None:
            print("错误: 无法获取数据库连接")
            QMessageBox.warning(None, "错误", "数据库连接失败")
            return False

        try:
            # 测试数据库连接
            pg.execute("SELECT 1")
            test_result = pg.fetchone()
            print(f"数据库连接测试: {test_result}")

            # 显式开始事务
            pg.execute("BEGIN")
            print("事务已开始")

            # 获取被拖动节点的BSM
            dragged_bsm = dragged_item.text(1)
            print(f"被拖动节点BSM: {dragged_bsm}")

            # 查询拖拽前的数据库状态
            pg.execute("SELECT pbsm, sort FROM t_vector_zyml WHERE bsm = %s", (dragged_bsm,))
            before_result = pg.fetchone()
            print(f"拖拽前数据库状态: {before_result}")

            success = False

            if drag_type == "同级排序":
                # 同级目录拖动改变顺序：只需要重新排序同级的所有节点
                success = self.update_sibling_sort_order(pg, new_parent_bsm)
                print(f"同级排序操作结果: {success}")

            elif drag_type in ["父变子", "子变父", "跨级移动"]:
                # 这三种操作都需要更新父子关系
                print(f"执行{drag_type}操作: 节点{dragged_bsm} 从 {old_parent_bsm or 'ROOT'} 移动到 {new_parent_bsm or 'ROOT'}")

                # 1. 更新被拖动节点的父节点关系
                update_sql = "UPDATE t_vector_zyml SET pbsm = %s WHERE bsm = %s"
                print(f"执行SQL: {update_sql} with params: ({new_parent_bsm}, {dragged_bsm})")

                pg.execute(update_sql, (new_parent_bsm, dragged_bsm))
                affected_rows = pg.rowcount if hasattr(pg, 'rowcount') else 'unknown'
                print(f"父节点关系更新完成, 影响行数: {affected_rows}")

                # 验证更新是否成功
                pg.execute("SELECT pbsm, sort FROM t_vector_zyml WHERE bsm = %s", (dragged_bsm,))
                after_parent_update = pg.fetchone()
                print(f"父节点更新后数据库状态: {after_parent_update}")

                # 2. 更新新父节点下所有子节点的排序
                success1 = self.update_sibling_sort_order(pg, new_parent_bsm)
                print(f"新父节点排序更新结果: {success1}")

                # 3. 更新原父节点下剩余子节点的排序
                success2 = self.update_sibling_sort_order(pg, old_parent_bsm)
                print(f"原父节点排序更新结果: {success2}")

                success = success1 and success2

            if success:
                # 提交事务
                pg.execute("COMMIT")
                print("事务提交成功")

                # 最终验证
                verify_pg = self.get_db_connection()
                if verify_pg:
                    verify_pg.execute("SELECT pbsm, sort FROM t_vector_zyml WHERE bsm = %s", (dragged_bsm,))
                    final_result = verify_pg.fetchone()
                    print(f"最终验证结果: {final_result}")
                    verify_pg.close()

                print(f"拖拽操作保存成功: {drag_type}")
                return True
            else:
                # 回滚事务
                pg.execute("ROLLBACK")
                print("操作失败，事务已回滚")
                return False

        except Exception as e:
            # 回滚事务
            try:
                pg.execute("ROLLBACK")
                print("异常发生，事务已回滚")
            except:
                print("回滚事务失败")

            print(f"拖拽操作保存失败: {e}")
            import traceback
            print(f"详细错误信息: {traceback.format_exc()}")
            QMessageBox.warning(None, "错误", f"保存失败: {str(e)}")
            return False
        finally:
            pg.close()
            print("数据库连接已关闭")
            print("=== 拖拽操作处理结束 ===\n")

    def update_sibling_sort_order(self, pg, parent_bsm):
        """更新指定父节点下所有子节点的排序"""
        try:
            print(f"  >>> 开始更新父节点 '{parent_bsm or 'ROOT'}' 下的子节点排序...")

            # 获取界面中的实际排序
            sibling_items = []

            if parent_bsm == '':  # 顶级节点
                for i in range(self.treeWidget.topLevelItemCount()):
                    item = self.treeWidget.topLevelItem(i)
                    sibling_items.append((item.text(1), i, item.text(0)))  # (bsm, sort_order, name)
            else:  # 子节点
                parent_item = self.find_item_by_bsm(parent_bsm)
                if parent_item:
                    for i in range(parent_item.childCount()):
                        item = parent_item.child(i)
                        sibling_items.append((item.text(1), i, item.text(0)))  # (bsm, sort_order, name)
                else:
                    print(f"  !!! 未找到父节点: {parent_bsm}")
                    return False

            print(f"  >>> 找到 {len(sibling_items)} 个同级节点需要更新排序")

            # 批量更新排序
            update_count = 0
            for bsm, sort_order, name in sibling_items:
                update_sql = "UPDATE t_vector_zyml SET sort = %s WHERE bsm = %s"
                print(f"    -> 更新节点 {name}({bsm}) 的排序为 {sort_order}")

                pg.execute(update_sql, (sort_order, bsm))
                affected_rows = pg.rowcount if hasattr(pg, 'rowcount') else 'unknown'
                print(f"       影响行数: {affected_rows}")

                # 验证单个更新是否成功
                pg.execute("SELECT sort FROM t_vector_zyml WHERE bsm = %s", (bsm,))
                verify_result = pg.fetchone()
                if verify_result:
                    actual_sort = verify_result[0]
                    if actual_sort == sort_order:
                        print(f"       ✓ 验证成功: {actual_sort}")
                        update_count += 1
                    else:
                        print(f"       ✗ 验证失败: 期望{sort_order}, 实际{actual_sort}")
                else:
                    print(f"       ✗ 验证失败: 未找到记录")

            print(f"  >>> 完成父节点 '{parent_bsm or 'ROOT'}' 下的排序更新，成功更新 {update_count}/{len(sibling_items)} 个节点")
            return update_count == len(sibling_items)

        except Exception as e:
            print(f"  !!! 更新同级排序失败: {e}")
            import traceback
            print(f"  !!! 详细错误: {traceback.format_exc()}")
            return False

    def sort_tree_items(self, parent_item):
        """对指定父节点下的子项进行排序"""
        child_count = parent_item.childCount()
        if child_count <= 1:
            return

        children = [parent_item.child(i) for i in range(child_count)]

        def sort_key(item):
            try:
                return int(item.data(0, Qt.UserRole + 1))
            except:
                return 9999

        children.sort(key=sort_key)

        # 清除原有子项并重新添加
        for i in reversed(range(child_count)):
            parent_item.takeChild(i)
        for child in children:
            parent_item.addChild(child)

    def initializeTreeData(self):
        """初始化树形数据"""
        if not self._tree_initialized:
            try:
                tree = Tree()
                temp_tree = tree.initTreeWidget(self.dbcoon)

                # 清空现有数据
                self.treeWidget.clear()

                # 添加新数据
                for i in range(temp_tree.topLevelItemCount()):
                    item = temp_tree.topLevelItem(i).clone()
                    self.treeWidget.addTopLevelItem(item)
                    self.sort_tree_items(item)  # 对一级节点排序其子项

                # 设置拖拽功能
                self.setup_tree_drag_drop()

                self._tree_initialized = True
                print("树形数据初始化完成，拖拽功能已启用")
            except Exception as e:
                print(f"初始化树形数据失败: {e}")
                error_item = QTreeWidgetItem(self.treeWidget)
                error_item.setText(0, "数据加载失败，请点击刷新重试")

    def refreshTreeWidget(self):
        """刷新树形控件"""
        try:
            print("开始刷新树形控件...")

            # 清空并重新初始化
            self.treeWidget.clear()
            self._tree_initialized = False
            self.initializeTreeData()

            print("树形控件刷新完成")
        except Exception as e:
            print(f"刷新树形控件失败: {e}")
            QMessageBox.warning(None, "错误", f"刷新失败: {str(e)}")

    def test_database_connection(self):
        """测试数据库连接和基本操作"""
        print("=== 测试数据库连接 ===")
        pg = self.get_db_connection()
        if pg is None:
            print("数据库连接失败")
            return False

        try:
            # 测试基本查询
            pg.execute("SELECT COUNT(*) FROM t_vector_zyml")
            count = pg.fetchone()[0]
            print(f"表中总记录数: {count}")

            # 测试更新操作
            pg.execute("SELECT bsm, pbsm, sort FROM t_vector_zyml LIMIT 1")
            test_record = pg.fetchone()
            if test_record:
                bsm, pbsm, sort_val = test_record
                print(f"测试记录: bsm={bsm}, pbsm={pbsm}, sort={sort_val}")

                # 尝试更新（然后回滚）
                pg.execute("BEGIN")
                pg.execute("UPDATE t_vector_zyml SET sort = sort + 1 WHERE bsm = %s", (bsm,))
                affected = pg.rowcount if hasattr(pg, 'rowcount') else 'unknown'
                print(f"测试更新影响行数: {affected}")
                pg.execute("ROLLBACK")
                print("测试更新已回滚")

            print("数据库连接测试通过")
            return True

        except Exception as e:
            print(f"数据库连接测试失败: {e}")
            return False
        finally:
            pg.close()

    def on_tree_item_dropped(self):
        self.save_tree_sort_order()



    def getNodeType(self, node):
        return node.text(2)

    def getUUID(self):
        id = uuid.uuid4().__str__()
        return id.replace("-", "")

    def getNodeName(self, node):
        return node.text(0)

    def getNodeId(self, node):
        return node.text(1)

    def actionAttrHandler(self):
        print("元数据信息")
        self.deleteMenu()
        if self.treeWidget.currentItem():
            self.currentItem = self.treeWidget.currentItem()
            print(self.currentItem)
            layertype = self.getNodeType(self.currentItem)
            print(layertype)
            if layertype is not None and layertype != "":  # 选择的是数据项
                tablename = self.getNodeId(self.currentItem)
                # **修改18: 使用优化的数据库连接**
                # 获取元数据信息
                pg = self.get_db_connection()
                if pg is None:
                    QMessageBox.warning(None, "错误", "数据库连接失败")
                    return
                self.tableattr = pg.getResourceAttr(tablename)
                dia = FormDialog(self.tableattr)
                dia.exec_()
            else:  # 选择的是目录
                QMessageBox.information(self, "提示信息", "请选择数据项")

    def on_item_double_clicked(self):
        self.actionViewHandler()

    # 属性表
    def actionAttrTableHandler(self):
        self.deleteMenu()
        if self.treeWidget.currentItem():
            self.currentItem = self.treeWidget.currentItem()
            layertype = self.getNodeType(self.currentItem)
            print(layertype)
            if layertype is not None and layertype != "":  # 选择的是数据项
                tablename = self.getNodeId(self.currentItem)
                print(tablename)
                pglayer = None
                if layertype == "vector":
                    pglayer = self.add_postgis_vector(tablename, True)
                elif layertype == "raster":
                    pglayer = self.add_postgis_raster(tablename, True)
                # if pglayer is not None and pglayer.isValid() and pglayer.fields():
                #     iface.showAttributeTable(pglayer)  # Show the attribute table
                # else:
                #     print("No valid layer selected or layer has no attributes.")
                if pglayer:
                    dialog = CombinedDialog(pglayer)
                    dialog.showDialog()
                else:
                    print("No layer selected")

    # 图层属性
    def actionLayerAttrHandler(self):
        self.deleteMenu()
        if self.treeWidget.currentItem():
            self.currentItem = self.treeWidget.currentItem()
            layertype = self.getNodeType(self.currentItem)
            print(layertype)
            if layertype is not None and layertype != "":  # 选择的是数据项
                tablename = self.getNodeId(self.currentItem)
                print(tablename)
                pglayer = None
                if layertype == "vector":
                    pglayer = self.add_postgis_vector(tablename, True)
                elif layertype == "raster":
                    pglayer = self.add_postgis_raster(tablename, True)
                # if pglayer is not None:
                #     iface.showLayerProperties(pglayer)
                if pglayer:
                    dialog = CombinedDialog(pglayer)
                    dialog.showDialog()
                else:
                    print("No layer selected")

    def actionViewHandler(self):
        print(self.contexMenu)
        self.deleteMenu()
        if self.treeWidget.currentItem():
            self.currentItem = self.treeWidget.currentItem()
            print(self.currentItem)
            layertype = self.getNodeType(self.currentItem)
            print(layertype)
            if layertype is not None and layertype != "":  # 选择的是数据项
                tablename = self.getNodeId(self.currentItem)
                print(tablename)
                if layertype == "vector":
                    self.add_postgis_vector(tablename, False)
                elif layertype == "raster":
                    self.add_postgis_raster(tablename, False)
                elif layertype == "osgb":
                    self.load_osgb_layer(tablename)
                elif layertype == "table":
                    self.load_table_layer(tablename)

    def actionstaticHandler(self):
        # **修改19: 优化统计功能，添加异常处理和连接超时**
        import os
        import psycopg2
        import matplotlib.pyplot as plt
        from matplotlib import font_manager
        from PyQt5.QtWidgets import (
            QDialog, QVBoxLayout, QMessageBox, QPushButton,
            QFileDialog, QHBoxLayout, QLabel, QGridLayout
        )
        from matplotlib.backends.backend_qt5agg import FigureCanvasQTAgg as FigureCanvas
        from PyQt5.QtCore import Qt
        from qgis.utils import iface

        def set_matplotlib_font():
            try:
                font_path = 'C:/Windows/Fonts/msyh.ttc'
                prop = font_manager.FontProperties(fname=font_path)
                plt.rcParams['font.family'] = prop.get_name()
            except Exception as e:
                print(f"字体设置失败：{e}")

        def get_xmlx_name_mapping(cursor):
            xmlx_name_map = {}
            cursor.execute("SELECT bsm, name, pbsm FROM base.t_vector_zyml")
            rows = cursor.fetchall()
            pbsm_map = {}
            for bsm, name, pbsm in rows:
                if bsm:
                    xmlx_name_map[bsm] = name
                if pbsm:
                    pbsm_map[bsm] = pbsm
            for bsm, pbsm in pbsm_map.items():
                if pbsm in xmlx_name_map:
                    xmlx_name_map[bsm] = xmlx_name_map[pbsm]
            return xmlx_name_map

        def show_matplotlib_dialog(fig, total_size_mb, total_count, glbm_count, xmlx_count_unique):
            dialog = QDialog(iface.mainWindow())
            dialog.setWindowTitle("数据统计")

            layout = QVBoxLayout()
            top_bar = QHBoxLayout()
            export_btn = QPushButton("导出图表")
            export_btn.setFixedWidth(100)
            export_btn.setCursor(Qt.PointingHandCursor)

            def export_chart():
                save_path, _ = QFileDialog.getSaveFileName(
                    dialog, "保存图表", "", "PNG 图片 (*.png);;PDF 文件 (*.pdf)"
                )
                if save_path:
                    try:
                        fig.savefig(save_path, dpi=300)
                        QMessageBox.information(dialog, "成功", f"图表已导出到：\n{save_path}")
                    except Exception as e:
                        QMessageBox.critical(dialog, "导出失败", f"导出图表时出错：{str(e)}")

            export_btn.clicked.connect(export_chart)
            top_bar.addStretch()
            top_bar.addWidget(export_btn)
            layout.addLayout(top_bar)

            info_grid = QGridLayout()
            info_titles = ["总体数据量", "总体数据大小", "涉及部门", "数据分类"]
            info_values = [f"{total_count} 条", f"{total_size_mb:.2f} MB", f"{glbm_count} 个",
                           f"{xmlx_count_unique} 个"]
            for i, (title, val) in enumerate(zip(info_titles, info_values)):
                label_title = QLabel(f"<b>{title}：</b>")
                label_value = QLabel(val)
                info_grid.addWidget(label_title, 0, i, alignment=Qt.AlignCenter)
                info_grid.addWidget(label_value, 1, i, alignment=Qt.AlignCenter)
            layout.addLayout(info_grid)

            canvas = FigureCanvas(fig)
            layout.addWidget(canvas)
            dialog.setLayout(layout)
            dialog.resize(1200, 900)
            dialog.exec_()

        try:
            # **修改20: 添加连接超时和错误处理**
            conn = psycopg2.connect(
                host='192.168.60.2', port='5432',
                dbname='real3d', user='postgres', password='postgis',
                connect_timeout=10)  # 添加连接超时
            conn.autocommit = True
            cursor = conn.cursor()

            schema = 'base'
            table = 't_vector_storage'
            full_table = f'"{schema}"."{table}"'

            xmlx_name_map = get_xmlx_name_mapping(cursor)

            cursor.execute(f"SELECT xmlx FROM {full_table} WHERE xmlx IS NOT NULL")
            xmlx_values = cursor.fetchall()
            xmlx_count_map = {}
            for (x,) in xmlx_values:
                name = xmlx_name_map.get(x, str(x))
                xmlx_count_map[name] = xmlx_count_map.get(name, 0) + 1
            stat_results = {'xmlx': sorted(xmlx_count_map.items(), key=lambda x: x[1], reverse=True)}

            for field in ['sjlx', 'glbm']:
                cursor.execute(f"""
                    SELECT {field}, COUNT(*) FROM {full_table}
                    WHERE {field} IS NOT NULL
                    GROUP BY {field}
                    ORDER BY COUNT(*) DESC
                """)
                stat_results[field] = cursor.fetchall()

            cursor.execute(f"SELECT sjlx, name, sjywz, xmlx, glbm FROM {full_table}")
            rows = cursor.fetchall()

            size_map = {'vector': 0, 'raster': 0, 'table': 0, 'osgb': 0}
            size_map_by_field = {'xmlx': {}, 'sjlx': {}, 'glbm': {}}

            sjlx_ch_map = {'vector': '矢量', 'raster': '栅格', 'osgb': '三维', 'table': '附件'}

            for sjlx, name, sjywz, xmlx, glbm in rows:
                size = 0
                try:
                    if sjlx == 'osgb' and sjywz and os.path.exists(sjywz):
                        if os.path.isdir(sjywz):
                            for dirpath, _, filenames in os.walk(sjywz):
                                for fname in filenames:
                                    fpath = os.path.join(dirpath, fname)
                                    if os.path.exists(fpath):
                                        size += os.path.getsize(fpath)
                        else:
                            size = os.path.getsize(sjywz)
                    elif sjlx in ['vector', 'raster']:
                        if not name:
                            continue
                        for tname in [name, f'"{name}"', f'"{schema}"."{name}"']:
                            try:
                                cursor.execute("SELECT pg_total_relation_size(%s);", (tname,))
                                res = cursor.fetchone()
                                if res and res[0]:
                                    size = res[0]
                                    break
                            except Exception:
                                continue
                    elif sjlx == 'table' and sjywz:
                        for tname in [sjywz, f'"{sjywz}"', f'"{schema}"."{sjywz}"']:
                            try:
                                cursor.execute("SELECT pg_total_relation_size(%s);", (tname,))
                                res = cursor.fetchone()
                                if res and res[0]:
                                    size = res[0]
                                    break
                            except Exception:
                                continue
                except Exception:
                    pass

                size_mb = size / 1024 / 1024
                size_map[sjlx] = size_map.get(sjlx, 0) + size_mb

                xmlx_name = xmlx_name_map.get(xmlx, str(xmlx)) if xmlx else "未知"
                size_map_by_field['xmlx'][xmlx_name] = size_map_by_field['xmlx'].get(xmlx_name, 0) + size_mb
                if sjlx:
                    ch_sjlx = sjlx_ch_map.get(sjlx, sjlx)
                    size_map_by_field['sjlx'][ch_sjlx] = size_map_by_field['sjlx'].get(ch_sjlx, 0) + size_mb
                if glbm:
                    size_map_by_field['glbm'][glbm] = size_map_by_field['glbm'].get(glbm, 0) + size_mb

            total_size_mb = sum(size_map.values())
            cursor.execute(f"SELECT COUNT(*) FROM {full_table}")
            total_count = cursor.fetchone()[0]
            glbm_count = len(stat_results['glbm'])
            xmlx_count_unique = len(stat_results['xmlx'])

            set_matplotlib_font()
            fig, axes = plt.subplots(2, 3, figsize=(18, 10))
            field_names = {'xmlx': '数据分类', 'sjlx': '数据类型', 'glbm': '管理部门'}
            fields = ['xmlx', 'sjlx', 'glbm']

            for idx, field in enumerate(fields):
                data = stat_results.get(field, [])
                labels = [sjlx_ch_map.get(str(r[0]), str(r[0])) if field == 'sjlx' else str(r[0]) for r in data]
                sizes = [r[1] for r in data]
                ax = axes[0][idx]

                if not sizes or sum(sizes) == 0:
                    ax.axis('off')
                    ax.set_title(f"{field_names[field]}（无数据）")
                    continue

                if len(labels) > 6:
                    others_sum = sum(sizes[5:])
                    labels = labels[:5] + ["其他"]
                    sizes = sizes[:5] + [others_sum]

                wedges, texts, autotexts = ax.pie(
                    sizes, labels=labels, autopct='%1.1f%%', startangle=90,
                    wedgeprops=dict(width=0.4), textprops={'fontsize': 9})
                for i, autotext in enumerate(autotexts):
                    autotext.set_text(f'{sizes[i]}')
                ax.axis('equal')
                ax.set_title(f"{field_names[field]}", fontsize=12)
                ax.legend(labels, loc='best', fontsize=8)

            for idx, field in enumerate(fields):
                ax = axes[1][idx]
                field_data = size_map_by_field[field]
                if not field_data:
                    ax.axis('off')
                    ax.set_title(f"{field_names[field]}（无数据）")
                    continue

                sorted_items = sorted(field_data.items(), key=lambda x: x[1], reverse=True)
                if len(sorted_items) > 6:
                    others = sum(v for _, v in sorted_items[5:])
                    sorted_items = sorted_items[:5] + [("其他", others)]

                labels = [str(k) for k, _ in sorted_items]
                values = [round(v, 2) for _, v in sorted_items]
                bars = ax.bar(labels, values, color='steelblue', width=0.5, edgecolor='black')

                ax.set_title(f"{field_names[field]}", fontsize=12)
                ax.set_ylabel("大小 (MB)")
                ax.tick_params(axis='x', rotation=30)
                ax.set_ylim(0, max(values) * 1.2)
                for bar in bars:
                    h = bar.get_height()
                    ax.annotate(f'{h:.2f}', xy=(bar.get_x() + bar.get_width() / 2, h),
                                xytext=(0, 3), textcoords="offset points",
                                ha='center', va='bottom', fontsize=9)

            fig.suptitle("数据量与存储大小统计", fontsize=16)
            plt.tight_layout(rect=[0, 0.05, 1, 0.95])

            show_matplotlib_dialog(fig, total_size_mb, total_count, glbm_count, xmlx_count_unique)

            cursor.close()
            conn.close()

        except Exception as e:
            QMessageBox.critical(iface.mainWindow(), "错误", f"统计失败: {str(e)}")

    def load_table_layer(self, name: str, layer_name: str = None):
        """
        根据 name（源文件名）从 t_vector_storage 获取 sjywz，加载 PostgreSQL 非空间表。
        """
        # **修改21: 优化数据库连接和错误处理**
        try:
            conn = psycopg2.connect(
                host="192.168.60.2",
                port="5432",
                dbname="real3d",
                user="postgres",
                password="postgis",
                connect_timeout=10
            )
            cursor = conn.cursor()

            # 按 name 字段查询（模糊匹配也可以加）
            cursor.execute(
                "SELECT sjywz FROM base.t_vector_storage WHERE name = %s ORDER BY rksj DESC LIMIT 1",
                (name,))
            result = cursor.fetchone()

            if not result:
                raise Exception(f"未找到 name 为 {name} 的记录。")

            sjywz = result[0]
            print(f"查到的 sjywz: {sjywz}")

            if '.' not in sjywz:
                raise Exception(f"sjywz 字段格式非法：{sjywz}，应为 schema.tablename")

            schema, table = sjywz.split('.')
            if layer_name is None:
                layer_name = table

            uri = (
                f"dbname='real3d' host=192.168.60.2 port=5432 user='postgres' "
                f"password='postgis' sslmode=disable table=\"{schema}\".\"{table}\" sql="
            )

            layer = QgsVectorLayer(uri, layer_name, "postgres")

            if not layer.isValid():
                raise Exception(f"表格图层加载失败：{schema}.{table}")

            QgsProject.instance().addMapLayer(layer)
            iface.showAttributeTable(layer)

            cursor.close()
            conn.close()
            return layer

        except Exception as e:
            QMessageBox.warning(None, "错误", f"加载表格数据失败: {str(e)}")
            return None

    def load_osgb_layer(self, name):
        print(f"加载三维数据（OSGB）: {name}")

        # **修改22: 优化OSGB数据加载，添加错误处理**
        try:
            # 连接到 PostgreSQL 数据库
            conn_params = {
                'host': "192.168.60.2",
                'port': "5432",
                'dbname': "real3d",
                'user': "postgres",
                'password': "postgis",
                'connect_timeout': 10
            }
            # 连接数据库
            conn = psycopg2.connect(**conn_params)
            cursor = conn.cursor()

            # 查询数据库获取路径
            cursor.execute("SELECT sjywz FROM base.t_vector_storage WHERE name = %s", (name,))
            result = cursor.fetchone()
            cursor.close()
            conn.close()

            if result is None:
                QMessageBox.warning(None, "错误", f"未找到三维数据路径: {name}")
                return

            osgb_dir = result[0]  # 获取路径

        except Exception as e:
            QMessageBox.warning(None, "错误", f"数据库连接失败或查询错误: {str(e)}")
            return

        # 检查路径是否存在
        if not os.path.exists(osgb_dir):
            QMessageBox.warning(None, "错误", f"三维数据目录不存在: {osgb_dir}")
            return

        # 构造命令行命令来打开 OSGB 文件
        dasviewer_path = r"D:\app\DasViewer.exe"

        # **修改23: 检查DasViewer是否存在**
        if not os.path.exists(dasviewer_path):
            QMessageBox.warning(None, "错误", f"DasViewer程序不存在: {dasviewer_path}")
            return

        command = [dasviewer_path, osgb_dir]

        try:
            # 使用 subprocess 启动 DasViewer
            subprocess.run(command, check=True)
            QMessageBox.information(None, "提示", f"已启动 DasViewer 并加载三维数据: {osgb_dir}")
        except subprocess.CalledProcessError as e:
            QMessageBox.warning(None, "错误", f"打开 DasViewer 时出错: {str(e)}")
        except Exception as e:
            QMessageBox.warning(None, "错误", f"发生错误: {str(e)}")

    def add_local_vector_layer(self, layer_path, layer_name):
        # 添加矢量图层
        vector_layer = QgsVectorLayer(layer_path, layer_name, "ogr")
        # 将图层添加到项目中
        QgsProject.instance().addMapLayer(vector_layer)
        self.zoomToLayer(vector_layer)

    def add_postgis_vector(self, tablename, getlayer):
        # **修改24: 优化PostGIS矢量图层加载，添加错误处理**
        try:
            # 创建PostgreSQL图层
            pgtable = tablename.split(".")
            postgresLayer = QgsVectorLayer(
                "dbname='{}' host={} port={} user='{}' password='{}' sslmode=disable checkPrimaryKeyUnicity='1' table=\"{}\".\"{}\" (geom)".format(
                    self.dbcoon["dbname"],
                    self.dbcoon["host"],
                    self.dbcoon["port"],
                    self.dbcoon["user"],
                    self.dbcoon["password"],
                    pgtable[0],
                    pgtable[1]
                ),
                pgtable[1], 'postgres')

            if not postgresLayer.isValid():
                QMessageBox.warning(None, "错误", f"无法加载矢量图层: {tablename}")
                return None

            if getlayer == True:
                return postgresLayer
            # 添加图层到QGIS项目
            QgsProject.instance().addMapLayer(postgresLayer)
            self.zoomToLayer(postgresLayer)
            return postgresLayer

        except Exception as e:
            QMessageBox.warning(None, "错误", f"加载矢量图层失败: {str(e)}")
            return None

    def add_postgis_raster(self, tablename, getlayer):
        # **修改25: 优化PostGIS栅格图层加载，添加错误处理**
        try:
            # 创建PostgreSQL图层
            pgtable = tablename.split(".")
            postgresLayer = QgsRasterLayer(
                "dbname='{}' host={} port={} user='{}' password='{}' sslmode=disable checkPrimaryKeyUnicity='1' table=\"{}\".\"{}\" (rast)".format(
                    self.dbcoon["dbname"],
                    self.dbcoon["host"],
                    self.dbcoon["port"],
                    self.dbcoon["user"],
                    self.dbcoon["password"],
                    pgtable[0],
                    pgtable[1]
                ),
                pgtable[1], "postgresraster")

            if not postgresLayer.isValid():
                QMessageBox.warning(None, "错误", f"无法加载栅格图层: {tablename}")
                return None

            if getlayer == True:
                return postgresLayer
            # 添加图层到QGIS项目
            QgsProject.instance().addMapLayer(postgresLayer)
            self.zoomToLayer(postgresLayer)
            return postgresLayer

        except Exception as e:
            QMessageBox.warning(None, "错误", f"加载栅格图层失败: {str(e)}")
            return None

    def add_local_raster_layer(self, layer_path, layer_name):
        # 添加栅格图层
        raster_layer = QgsRasterLayer(layer_path, layer_name)
        # 将图层添加到项目中
        QgsProject.instance().addMapLayer(raster_layer)
        self.zoomToLayer(raster_layer)

    def zoomToLayer(self, layer):
        # 当前活动图层
        activelayer = iface.activeLayer()
        # 缩放到图层范围
        # 获取当前项目的 CRS
        current_canvas_crs = QgsProject.instance().crs()
        current_layer_crs = layer.crs()
        if activelayer is None or activelayer == layer or current_canvas_crs.authid() == current_layer_crs.authid():
            iface.mapCanvas().setExtent(layer.extent())
        else:
            print(current_canvas_crs.authid())
            print(current_layer_crs.authid())
            rect = layer.extent()
            print(rect)
            # 创建坐标转换器
            transform = QgsCoordinateTransform(current_layer_crs, current_canvas_crs, QgsProject.instance())
            print(current_layer_crs)
            print(current_layer_crs)
            # 使用坐标转换器将 QgsRectangle 转换为目标 CRS
            # 我们需要将矩形的四个角进行转换
            rect_min_x, rect_min_y = transform.transform(rect.xMinimum(), rect.yMinimum())
            rect_max_x, rect_max_y = transform.transform(rect.xMaximum(), rect.yMaximum())
            # 创建新的转换后的
            transformed_rect = QgsRectangle(rect_min_x, rect_min_y, rect_max_x, rect_max_y)
            iface.mapCanvas().setExtent(transformed_rect)
        iface.mapCanvas().refresh()