NDBGJK5.0/Kingo.Plugins/数据建库/Kingo.Plugin.DLTB_IDG/View/UCQualityCheckListPlus.xaml.cs

using DevExpress.Spreadsheet;
using ESRI.ArcGIS.Carto;
using ESRI.ArcGIS.Geodatabase;
using KGIS.Framework.DBOperator;
using KGIS.Framework.Utils;
using KGIS.Framework.Utils.Helper;
using KGIS.Framework.Views;
using Kingo.Plugin.DLTB_IDG.EntiyModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Windows;
using System.Windows.Controls;
using Path = System.IO.Path;
using KUI.Windows;
using KGIS.Framework.Platform;
using Kingo.RuleCheck;
using KGIS.Framework.Maps;
using Kingo.PluginServiceInterface;

namespace Kingo.Plugin.DLTB_IDG.View
{
    /// <summary>
    /// 国家质检错误信息列表 的交互逻辑
    /// </summary>
    public partial class UCQualityCheckListPlus : UserControl, IDockPanel3
    {
        private Dictionary<string, List<object>> fieldValueDict = null;
        private IRDBHelper rdbHelper = null;
        //private string DbPath = Path.Combine(SysAppPath.GetCurrentAppPath(), @"TempalateReports\质检DB文件模板\QualityCheckResult.db");
        private string DbPath = Path.Combine((MapsManager.Instance.MapService.GetProjectInfo() as ProjectInfo).ProjDir, "QualityCheckResult.db");
        public UCQualityCheckListPlus()
        {
            InitializeComponent();
            DevExpress.Xpf.Core.ThemeManager.SetTheme(this, DevExpress.Xpf.Core.Theme.Office2013LightGray);
            DockAreas = DockStyle.DockRight | DockStyle.DockBottom | DockStyle.DockLeft;
            FloatSize = new System.Drawing.Size(1600, 660);
            DockHeight = 380;
            DefaultArea = DockStyle.DockBottom;
            ShowCloseButton = true;
            ShowAutoHideButton = false;
            Title = "国家质检错误信息列表";
            IsShowInMap = true;
            this.Loaded += LoadViewData;
        }
        private Dictionary<string, string> ErrItems = new Dictionary<string, string>();
        private void LoadViewData(object sender, RoutedEventArgs e)
        {
            ErrItems.Clear();
            if (!File.Exists(DbPath))
            {
                MessageHelper.ShowTips($"不存在当前路径：{DbPath}上的DB数据库文件");
                return;
            }
            rdbHelper = RDBFactory.CreateDbHelper($"{DbPath}", DatabaseType.SQLite);
            try
            {
                if (rdbHelper.TableIsExist("ErrorTC"))
                {
                    DataTable ErrorLayerdt = rdbHelper.ExecuteDatatable("ErrorTC", "SELECT DISTINCT ErrorLayer FROM ErrorTC WHERE ErrorLayer<>''", true);
                    if (ErrorLayerdt != null && ErrorLayerdt.Rows.Count > 0)
                    {
                        ErrItems.Add("ErrorTC", "错误图层");
                    }
                }
                if (rdbHelper.TableIsExist("ErrorBGYLB"))
                {
                    DataTable ErrorLayerdt = rdbHelper.ExecuteDatatable("ErrorBGYLB", "SELECT DISTINCT ErrorType FROM ErrorBGYLB WHERE ErrorType<>''", true);
                    if (ErrorLayerdt != null && ErrorLayerdt.Rows.Count > 0)
                    {
                        ErrItems.Add("ErrorBGYLB", "一览表错误");
                    }
                }
                if (rdbHelper.TableIsExist("ErrorBasic"))
                {
                    DataTable ErrorLayerdt = rdbHelper.ExecuteDatatable("ErrorBasic", "SELECT DISTINCT ErrorDescription FROM ErrorBasic WHERE ErrorDescription<>'' ORDER by ErrorDescription", true);
                    if (ErrorLayerdt != null && ErrorLayerdt.Rows.Count > 0)
                    {
                        ErrItems.Add("ErrorBasic", "基本错误");
                    }
                }
                if (rdbHelper.TableIsExist("ErrorReport"))
                {
                    DataTable ErrorLayerdt = rdbHelper.ExecuteDatatable("ErrorReport", "SELECT DISTINCT StatisticalReport FROM ErrorReport WHERE StatisticalReport<>''", true);
                    if (ErrorLayerdt != null && ErrorLayerdt.Rows.Count > 0)
                    {
                        ErrItems.Add("ErrorReport", "错误报表");
                    }
                }
            }
            catch (Exception ex)
            {
                LogAPI.Debug("国家质检绑定错误类型异常：" + ex);
                throw;
            }
            ErrBBComboBox.DisplayMemberPath = "Value";
            ErrBBComboBox.SelectedValuePath = "Key";
            if (ErrItems.Count > 0)
            {
                ErrBBComboBox.ItemsSource = ErrItems;
                ErrBBComboBox.SelectedIndex = 0;
            }
        }

        private Dictionary<string, string> ErrItem = new Dictionary<string, string>();
        /// <summary>
        /// 错误报表选择事件
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void ErrBBComboBox_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {
            string ErrBBName = ErrBBComboBox.SelectedValue.ToString();
            if (string.IsNullOrWhiteSpace(ErrBBName)) return;
            List<CheckErrorInfo> checkErrorInfos = new List<CheckErrorInfo>();
            string TableName = ErrBBName;
            ErrItem.Clear();
            ErrLXComboBox.ItemsSource = null;
            ErrLXComboBox.SelectedValuePath = "Key";
            ErrLXComboBox.DisplayMemberPath = "Key";
            if (!File.Exists(DbPath))
            {
                MessageHelper.ShowTips($"不存在当前路径：{DbPath}上的DB数据库文件");
                return;
            }
            rdbHelper = RDBFactory.CreateDbHelper($"{DbPath}", DatabaseType.SQLite);
            if (TableName.Equals("ErrorTC"))
            {//错误图层
                DataTable ErrorLayerdt = rdbHelper.ExecuteDatatable("ErrorTC", "SELECT DISTINCT ErrorLayer FROM ErrorTC WHERE ErrorLayer<>''", true);
                if (ErrorLayerdt != null && ErrorLayerdt.Rows.Count > 0)
                {
                    foreach (DataRow ErrorLayeritem in ErrorLayerdt.Rows)
                    {
                        ErrItem.Add(ErrorLayeritem["ErrorLayer"].ToString(), "ErrorTC");
                    }
                    ErrLXComboBox.ItemsSource = ErrItem;
                }
            }
            else if (TableName.Equals("ErrorBGYLB"))
            {//错误一栏表
                DataTable ErrorLayerdt = rdbHelper.ExecuteDatatable("ErrorBGYLB", "SELECT DISTINCT ErrorType FROM ErrorBGYLB WHERE ErrorType<>''", true);
                if (ErrorLayerdt != null && ErrorLayerdt.Rows.Count > 0)
                {
                    foreach (DataRow ErrorLayeritem in ErrorLayerdt.Rows)
                    {
                        ErrItem.Add(ErrorLayeritem["ErrorType"].ToString(), "ErrorBGYLB");
                    }
                    ErrLXComboBox.ItemsSource = ErrItem;
                }
            }
            else if (TableName.Equals("ErrorReport"))
            {//错误报表记录
                DataTable ErrorLayerdt = rdbHelper.ExecuteDatatable("ErrorReport", "SELECT DISTINCT StatisticalReport FROM ErrorReport WHERE StatisticalReport<>''", true);
                if (ErrorLayerdt != null && ErrorLayerdt.Rows.Count > 0)
                {
                    foreach (DataRow ErrorLayeritem in ErrorLayerdt.Rows)
                    {
                        ErrItem.Add(ErrorLayeritem["StatisticalReport"].ToString(), "ErrorReport");
                    }
                    ErrLXComboBox.ItemsSource = ErrItem;
                }
            }
            else if (TableName.Equals("ErrorBasic"))
            {//基本错误
                DataTable ErrorLayerdt = rdbHelper.ExecuteDatatable("ErrorBasic", "SELECT DISTINCT ErrorDescription FROM ErrorBasic WHERE ErrorDescription<>'' ORDER by ErrorDescription", true);
                if (ErrorLayerdt != null && ErrorLayerdt.Rows.Count > 0)
                {
                    string content = "";
                    foreach (DataRow ErrorLayeritem in ErrorLayerdt.Rows)
                    {
                        if (content.Equals(ErrorLayeritem["ErrorDescription"].ToString().Replace("层标识码", "_").Split('_')[0]))
                            continue;
                        content = ErrorLayeritem["ErrorDescription"].ToString().Replace("层标识码", "_").Split('_')[0];
                        ErrItem.Add(content, "ErrorBasic");
                    }
                    ErrLXComboBox.ItemsSource = ErrItem;
                }
            }
            if (ErrLXComboBox.ItemsSource != null)
            {
                ErrLXComboBox.SelectedIndex = 0;
            }
            if (rdbHelper != null)
            {//释放DB
                rdbHelper.DisConnect();
                rdbHelper = null;
            }
        }

        private List<CheckErrorInfo> checkErrorInfos = new List<CheckErrorInfo>();
        /// <summary>
        /// 错误类型选择事件
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void ErrLXComboBox_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {
            if (ErrLXComboBox.ItemsSource == null) return;
            checkErrorInfos.Clear();
            lbTBList.ItemsSource = null;
            string ButtonContent = ErrLXComboBox.SelectedValue.ToString();
            string TableName = ErrItem[ButtonContent];
            if (!File.Exists(DbPath))
            {
                MessageHelper.ShowTips($"不存在当前路径：{DbPath}上的DB数据库文件");
                return;
            }
            rdbHelper = RDBFactory.CreateDbHelper($"{DbPath}", DatabaseType.SQLite);
            if (TableName.Equals("ErrorTC"))
            {
                DataTable AllDatadt = rdbHelper.ExecuteDatatable(TableName, $"select * from {TableName} where ErrorLayer='{ButtonContent}' ORDER by BSM", true);
                if (AllDatadt != null && AllDatadt.Rows.Count > 0)
                {
                    foreach (DataRow item in AllDatadt.Rows)
                    {
                        CheckErrorInfo checkErrorInfo = new CheckErrorInfo()
                        {
                            XH = item["XH"].ToString(),
                            BSM = item["BSM"].ToString(),
                            CheckType = "ErrorTC",
                            ErrorDescription = item["ErrorDescription"].ToString(),
                            ErrorCode = item["ErrorCode"].ToString(),
                            ErrorLayer = item["ErrorLayer"].ToString()
                        };
                        checkErrorInfos.Add(checkErrorInfo);
                    }
                }
                lbTBList.ItemsSource = checkErrorInfos.Distinct(new ErrorInfoComparer()).ToList();
                dgTableMapping.ItemsSource = null;
                dgTableMapping.ItemsSource = checkErrorInfos;
            }
            else if (TableName.Equals("ErrorReport"))
            {
                DataTable AllDatadt = rdbHelper.ExecuteDatatable(TableName, $"select * from {TableName} where StatisticalReport='{ButtonContent}'", true);
                if (AllDatadt != null && AllDatadt.Rows.Count > 0)
                {
                    foreach (DataRow item in AllDatadt.Rows)
                    {
                        CheckErrorInfo checkErrorInfo = new CheckErrorInfo()
                        {
                            XH = item["XH"].ToString(),
                            CheckType = "ErrorReport",
                            ErrorDescription = item["ErrorDescription"].ToString(),
                            ErrorCode = item["ErrorCode"].ToString()
                        };
                        checkErrorInfos.Add(checkErrorInfo);
                    }
                }
                lbTBList.ItemsSource = null;
                dgTableMapping.ItemsSource = null;
                dgTableMapping.ItemsSource = checkErrorInfos;
            }
            else if (TableName.Equals("ErrorBasic"))
            {
                DataTable AllDatadt = rdbHelper.ExecuteDatatable(TableName, $"select * from {TableName} where ErrorDescription<>'' AND ErrorDescription Like '{ButtonContent}%'", true);
                if (AllDatadt != null && AllDatadt.Rows.Count > 0)
                {
                    foreach (DataRow item in AllDatadt.Rows)
                    {
                        string ErrorDescriptionBSM = string.Empty;
                        string Erlayer = string.Empty;
                        if (item["ErrorDescription"].ToString().Contains("[") && item["ErrorDescription"].ToString().Contains("]"))
                            ErrorDescriptionBSM = item["ErrorDescription"].ToString().Replace('[', '_').Replace(']', '_').Split('_')[1];
                        else if (item["ErrorDescription"].ToString().Contains("VCT"))
                        {
                            ErrorDescriptionBSM = item["ErrorDescription"].ToString().Replace('：', '_').Split('_')[1].Substring(0, 18);
                            Erlayer = item["ErrorDescription"].ToString().ToString().Replace('】', '_').Split('_')[1].Split('层')[0];
                        }
                        CheckErrorInfo checkErrorInfo = new CheckErrorInfo()
                        {
                            XH = item["XH"].ToString(),
                            BSM = ErrorDescriptionBSM,
                            CheckType = "ErrorBasic",
                            ErrorDescription = item["ErrorDescription"].ToString(),
                            ErrorCode = item["ErrorCode"].ToString(),
                            ErrorLayer = Erlayer
                        };
                        checkErrorInfos.Add(checkErrorInfo);
                    }
                }
                lbTBList.ItemsSource = checkErrorInfos.Distinct(new ErrorInfoComparer()).ToList();
                dgTableMapping.ItemsSource = null;
                dgTableMapping.ItemsSource = checkErrorInfos;
            }
            if (rdbHelper != null)
            {
                rdbHelper.DisConnect();
                rdbHelper = null;
            }
        }

        #region IDockPanel接口属性
        public event EventHandler CloseViewHandler;

        public bool IsDockToPanel { get; set; }
        public DockStyle DockToPanelStyle { get; set; }
        public bool IsShowInMap { get; set; }
        public Guid ID { get; set; }
        public DockStyle DockAreas { get; set; }
        public System.Drawing.Size FloatSize { get; set; }
        public int DockWidth { get; set; }
        public int DockHeight { get; set; }
        public DockStyle DefaultArea { get; set; }
        public bool ShowCloseButton { get; set; }
        public bool ShowAutoHideButton { get; set; }
        public string Title { get; set; }
        #endregion

        private void LbTBList_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {
            try
            {
                if (lbTBList.SelectedItem is CheckErrorInfo)
                {
                    string SelecyKey = (lbTBList.SelectedItem as CheckErrorInfo).BSM;
                    if (string.IsNullOrWhiteSpace(SelecyKey)) return;
                    dgTableMapping.ItemsSource = null;
                    dgTableMapping.ItemsSource = checkErrorInfos.FindAll(x => x.BSM.Equals(SelecyKey)).ToList();
                }
            }
            catch (Exception ex)
            {
                LogAPI.Debug("切换错误节点失败：" + ex.Message);
            }
        }

        /// <summary>
        /// 双击定位-仅为矢量可定位数据
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void TvAttr_RowDoubleClick(object sender, DevExpress.Xpf.Grid.RowDoubleClickEventArgs e)
        {
            try
            {
                //错误图层定位逻辑
                if (dgTableMapping.SelectedItem is CheckErrorInfo checkErrorInfo && checkErrorInfo.CheckType.Equals("ErrorTC") && !string.IsNullOrWhiteSpace(checkErrorInfo.ErrorLayer))
                {
                    IQueryFilter queryFilter = new QueryFilterClass()
                    {
                        WhereClause = $"BSM='{checkErrorInfo.BSM}'"
                    };
                    string ErrorLayerName = checkErrorInfo.ErrorLayer;
                    switch (checkErrorInfo.ErrorLayer)
                    {
                        case "城镇村等用地更新":
                            ErrorLayerName = "城镇村更新";
                            break;
                        case "城镇村等用地更新过程":
                            ErrorLayerName = "城镇村更新过程";
                            break;
                    }
                    IFeatureLayer layer = KGIS.Framework.Maps.MapsManager.Instance.MapService.GetFeatureLayerByLayerName(ErrorLayerName);
                    if (layer != null)
                    {
                        IFeatureCursor featureCursor = layer.Search(queryFilter, true);
                        IFeature feature = featureCursor.NextFeature();
                        if (feature != null)
                        {
                            KGIS.Framework.Maps.MapsManager.Instance.MapService.SelectFeature(((IDataset)layer.FeatureClass).Name, feature.OID.ToString());
                            //根据错误编码执行质检库
                            List<RuleEntity> ruleEntities = RuleCheck.XJRuleCheck.RuleCheckOpertion_DTB.StartGJZJJGCheck(feature, new List<Tuple<string>>() { Tuple.Create(checkErrorInfo.ErrorCode) });
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                LogAPI.Debug("错误图层定位逻辑" + ex.Message);
            }
        }

        /// <summary>
        ///读取质检错误表格到DB中
        /// </summary>
        private void BtnDataExtract_Click(object sender, RoutedEventArgs e)
        {
            try
            {
                string excelPath = string.Empty;
                KGIS.Framework.Utils.Dialog.OpenFileDialog openFileDialog = new KGIS.Framework.Utils.Dialog.OpenFileDialog
                {
                    Title = "国家质检错误表EXCEL",
                    Filter = "Excel Files(*.xlsx)| *.xlsx",
                    FileName = string.Empty,
                    FilterIndex = 1
                };
                if (openFileDialog.ShowDialog())
                {
                    excelPath = openFileDialog.FileName;
                }
                else
                    return;
                this.ShowLoading("正在加载质检错误信息", 0, 0);
                System.Threading.Thread.Sleep(1000);
                if (!File.Exists(DbPath))
                {
                    MessageHelper.ShowTips($"不存在当前路径：{DbPath}上的DB数据库文件");
                    return;
                }
                rdbHelper = RDBFactory.CreateDbHelper($"{DbPath}", DatabaseType.SQLite);
                rdbHelper.BeginTransaction();
                // 打开 Excel 文件 
                FileStream stream = new FileStream(excelPath, FileMode.Open);
                Workbook workbook = new Workbook();
                workbook.LoadDocument(stream, DocumentFormat.OpenXml);
                if (workbook.Worksheets != null && workbook.Worksheets.Count >= 1)
                {
                    try
                    {
                        foreach (Worksheet sheet in workbook.Worksheets)
                        {
                            Range usedRange = sheet.GetUsedRange();
                            int rowCount = usedRange.RowCount;
                            int columnCount = usedRange.ColumnCount;

                            // 获取 Excel 表格中的字段名和值
                            List<string> fieldNames = new List<string>();
                            fieldValueDict = new Dictionary<string, List<object>>();
                            int indexValue = 0;
                            string fields = string.Empty;
                            for (int i = 0; i < columnCount; i++)
                            {
                                string fieldName = sheet.Rows[indexValue][i].DisplayText.ToString();
                                while (i == 0 && fieldName != "序号")
                                {
                                    indexValue++;
                                    fieldName = sheet.Rows[indexValue][i].DisplayText.ToString();
                                }
                                fieldNames.Add(fieldName);
                            }
                            for (int j = 1; j < rowCount; j++)
                            {
                                List<object> fieldValues = new List<object>();
                                for (int i = 0; i < columnCount; i++)
                                {
                                    object value = sheet.Rows[j][i].Value;
                                    if (value != null)
                                    {
                                        fieldValues.Add(value);
                                    }
                                }
                                fieldValueDict[j.ToString()] = fieldValues;
                            }
                            string sheetName = string.Empty;
                            if (sheet.Name == "错误图层")
                            {
                                fields = "XH,ErrorType,ErrorLayer,BSM,ErrorCode,ErrorDescription,IsException,ExceptionDescription";
                                sheetName = "ErrorTC";
                            }
                            else if (sheet.Name == "错误报表")
                            {
                                fields = "XH,ErrorType,StatisticalReport,XZQCode,ErrorCode,ErrorDescription,IsException,ExceptionDescription";
                                sheetName = "ErrorReport";
                            }
                            else if (sheet.Name == "基本错误")
                            {
                                fields = "XH,ErrorType,ErrorCode,ErrorDescription,IsException,ExceptionDescription";
                                sheetName = "ErrorBasic";
                            }
                            else if (sheet.Name == "变更一览表错误")
                            {
                                fields = "XH,ErrorType,BGQTBBSM,BGQTBBH,BGQZLDWDM,BGQZLDWMC,BGQQSDWDM,BGQQSDWMC,BGQQSXZ,BGQDLBM,BGQGDLX,BGQGDPDJB,BGQCZCSXM,BGQTBXHDM,BGQTBXHMC,BGQMSSM,BGQGDDB,BGHTBBSM,BGHTBBH,BGHZLDWDM,BGHZLDWMC,BGHQSDWDM,BGHQSDWMC,BGHQSXZ,BGHDLBM,BGHGDLX,BGHGDPDJB,BGHCZCSXM,BGHTBXHDM,BGHTBXHMC,BGHMSSM,BGHGDDB,BGMJ,XZQTZLX";
                                sheetName = "ErrorBGYLB";
                            }
                            else if (sheet.Name == "变更一览表错误说明")
                            {
                                fields = "XH,ErrorEntrySM,ErrorType,BGQTBBSM,BGQTBBH,BGQZLDWDM,BGQZLDWMC,BGQQSDWDM,BGQQSDWMC,BGQQSXZ,BGQDLBM,BGQGDLX,BGQGDPDJB,BGQCZCSXM,BGQTBXHDM,BGQTBXHMC,BGQMSSM,BGQGDDB,BGHTBBSM,BGHTBBH,BGHZLDWDM,BGHZLDWMC,BGHQSDWDM,BGHQSDWMC,BGHQSXZ,BGHDLBM,BGHGDLX,BGHGDPDJB,BGHCZCSXM,BGHTBXHDM,BGHTBXHMC,BGHMSSM,BGHGDDB,BGMJ,XZQTZLX";
                                sheetName = "ErrorBGYLBSM";
                            }
                            else
                            {
                                continue;
                            }
                            rdbHelper.ExecuteNonQueryWithException($"delete from {sheetName}", CommandType.Text);
                            foreach (var fieldValues in fieldValueDict.Values)
                            {
                                string fieldValue = string.Empty;
                                foreach (var item in fieldValues)
                                {
                                    fieldValue += $"'{item}',";
                                }
                                string excuteSQL = string.Format("insert into {0}({1})values({2})", sheetName, fields, fieldValue.TrimEnd(','));
                                rdbHelper.ExecuteNonQueryWithException(excuteSQL, CommandType.Text);
                            }
                        }
                        rdbHelper.Commit();
                    }
                    catch (Exception ex)
                    {
                        rdbHelper.Rollback();
                        LogAPI.Debug("国家质检信息导入DB异常：" + ex);
                        if (ex.Message.Contains("database is locked"))
                        {
                            MessageHelper.ShowError("文档已被占用！");
                        }
                        else
                            MessageHelper.ShowError("国家质检信息导入DB异常");
                    }
                    finally
                    {
                        if (rdbHelper != null)
                        {
                            rdbHelper.DisConnect();
                            rdbHelper = null;
                        }
                        if (workbook != null)
                        {
                            workbook.Dispose();
                            workbook = null;
                        }
                        if (stream != null)
                        {
                            stream.Dispose();
                            stream = null;
                        }
                    }
                }
                this.CloseLoading();
                LoadViewData(null, null);
                MessageHelper.ShowTips("加载成功！");
            }
            catch (Exception ex)
            {
                MessageHelper.ShowTips("加载表格异常：" + ex.Message);
            }
        }

        public void ShowPanel()
        {
            Platform.Instance.OpenView(this, false);
            if (MapsManager.Instance.MapService != null)
            {
                MapsManager.Instance.MapService.ProjectClosed += MapsService_ProjectClosed;
            }
        }

        private void MapsService_ProjectClosed(object sender, EventArgs e)
        {
            ClosePanel();
        }

        public void ClosePanel()
        {
            Platform.Instance.CloseView(this);
        }

        public void ClosePanelInvoke()
        {
            CloseViewHandler?.Invoke(null, null);
        }


    }
}