TeamAAS-VP-HeShuo/TeamAAS-VM/Data/DatabaseHelper.cs

using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using TeamAAS_VP;
using System.IO;
using System.Windows;
using System.Runtime.CompilerServices;
using TeamAAS_VP.Models;
using System.Web.UI.WebControls;
using TeamAAS_VP.Enums;
using System.Windows.Shapes;
using TeamAAS_VP.Core;
using NPOI.Util;
using NPOI.SS.Formula.Functions;
using TouchSocket.Core;
using System.Runtime.Remoting.Contexts;
using TeamAAS_VP.Views.Product;
using System.Windows.Documents;
using System.Runtime.InteropServices.ComTypes;
using System.Data.Entity.Core.Common.CommandTrees.ExpressionBuilder;
using NPOI.SS.UserModel;
using Cognex.VisionPro.ToolBlock;
using Org.BouncyCastle.Asn1.X509.Qualified;

namespace TeamAAS_VP.Data
{
    public class DatabaseHelper
    {
        /// <summary>
        /// 初始化数据库
        /// </summary>
        /// <returns></returns>
        public static bool InitDatabase()
        {
            try
            {
                //判断数据库文件是否存在，如若不存在，则创建默认数据库
                //用户数据库
                if (!File.Exists(FilePath.UserDbPath))
                {
                    if (!Directory.Exists(System.IO.Path.GetDirectoryName(FilePath.UserDbPath)))
                    {
                        Directory.CreateDirectory(System.IO.Path.GetDirectoryName(FilePath.UserDbPath));
                    }
                    //如果数据库文件不存在，则创建一个数据库
                    SQLiteConnection.CreateFile(FilePath.UserDbPath);
                }
                if (!IsHaveTable("Users", FilePath.UserDbPath))
                {
                    CreateTable("Users", "(UserName TEXT,UserPassword TEXT,UserPart TEXT,AddTime DATETIME,IsRemember BOOL)", FilePath.UserDbPath);
                    string cmd = $"insert into Users(UserName,UserPassword,UserPart,AddTime,IsRemember) values('操作员','','Operator','{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}',False)";
                    WriteCommandToDatabase(cmd, FilePath.UserDbPath);
                    cmd = $"insert into Users(UserName,UserPassword,UserPart,AddTime,IsRemember) values('工程师','123','Engineer','{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}',False)";
                    WriteCommandToDatabase(cmd, FilePath.UserDbPath);
                    cmd = $"insert into Users(UserName,UserPassword,UserPart,AddTime,IsRemember) values('管理员','team123456','Administrator','{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}',False)";
                    WriteCommandToDatabase(cmd, FilePath.UserDbPath);
                }

                //生产记录数据库
                if (!File.Exists(FilePath.ProductionRecordsDbPath))
                {
                    //如果数据库文件不存在，则创建一个数据库
                    SQLiteConnection.CreateFile(FilePath.ProductionRecordsDbPath);
                }
                //报警记录
                if (!IsHaveTable("Alarm", FilePath.ProductionRecordsDbPath))
                {
                    CreateTable("Alarm", "(Time DATETIME,ErrorCode TEXT,ErrorMessage TEXT,Countermeasure TEXT)", FilePath.ProductionRecordsDbPath);
                }
                //软件操作记录
                if (!IsHaveTable("OperationLog", FilePath.ProductionRecordsDbPath))
                {
                    CreateTable("OperationLog", "(Time DATETIME,UserName TEXT,UserPart TEXT,Content TEXT)", FilePath.ProductionRecordsDbPath);
                }
                ////生产记录
                //if (!IsHaveTable("ProductionRecords", FilePath.ProductionRecordsDbPath))
                //{
                //    CreateTable("ProductionRecords", "(Time DATETIME,Content TEXT)", FilePath.ProductionRecordsDbPath);
                //}
                ////视觉拍照记录
                //if (!IsHaveTable("CameraRecords", FilePath.ProductionRecordsDbPath))
                //{
                //    CreateTable("CameraRecords", "(Time DATETIME,ProductName TEXT,ProductSN TEXT,CameraName TEXT,ProcessName TEXT,Result TEXT,ResultContent TEXT,ProcessingTime DOUBLE)", FilePath.ProductionRecordsDbPath);
                //}
                //产品加载记录
                if (!IsHaveTable("LoadProductRecords", FilePath.ProductionRecordsDbPath))
                {
                    CreateTable("LoadProductRecords", "(Time DATETIME,ProductID TEXT,ProductName TEXT,IsAutoLoad BOOL)", FilePath.ProductionRecordsDbPath);
                }
                //锁付结果记录
                EnsureLockResultTable(FilePath.ProductionRecordsDbPath);
                return true;
            }
            catch (Exception ex)
            {
                LogHelper.WriteLogError("数据库初始化失败！", ex);
                return false;
            }

        }

        #region 用户权限
        /// <summary>
        /// 获取所有的用户
        /// </summary>
        /// <returns></returns>
        public static List<User> GetAllUser()
        {
            try
            {
                //创建连接字符串
                SQLiteConnection conn = new SQLiteConnection(string.Format("Data Source={0};Version=3;", FilePath.UserDbPath));
                //打开数据库
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
                string query = "select * from Users";
                //创建命令
                SQLiteCommand cmd = new SQLiteCommand(query, conn);
                //执行命令
                SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
                DataTable dt = new DataTable();
                da.Fill(dt);
                da.Dispose();
                List<User> users = new List<User>();
                users.Clear();
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    users.Add(new User()
                    {
                        UserName = dt.Rows[i].ItemArray[0].ToString(),
                        UserPassword = dt.Rows[i].ItemArray[1].ToString(),
                        userPart = (UserPart)Enum.Parse(typeof(UserPart), dt.Rows[i].ItemArray[2].ToString()),
                        CreateTime = Convert.ToDateTime(dt.Rows[i].ItemArray[3].ToString()),
                        IsRemember = (bool)(dt.Rows[i].ItemArray[4]),

                    }); ;
                }
                dt.Dispose();
                //释放资源
                conn.Close();
                cmd.Dispose();
                conn.Dispose();
                return users;
            }
            catch (Exception ex)
            {
                LogHelper.WriteLogError("加载用户权限信息时出错！", ex);
                return null;
            }
        }

        /// <summary>
        /// 记住用户
        /// </summary>
        /// <param name="UserName"></param>
        public static void RememberUser(string UserName)
        {
            using (SQLiteConnection connection = new SQLiteConnection(string.Format("Data Source={0};Version=3;", FilePath.UserDbPath)))
            {
                connection.Open();
                using (SQLiteCommand command = new SQLiteCommand("UPDATE Users SET IsRemember = False", connection))
                {
                    command.ExecuteNonQuery();
                    command.CommandText= $"UPDATE Users SET IsRemember = True WHERE UserName = '{UserName}'";
                    command.ExecuteNonQuery();
                }
            }
        }

        /// <summary>
        /// 清除记住用户
        /// </summary>
        public static void NotRememberUser()
        {
            using (SQLiteConnection connection = new SQLiteConnection(string.Format("Data Source={0};Version=3;", FilePath.UserDbPath)))
            {
                connection.Open();
                using (SQLiteCommand command = new SQLiteCommand("UPDATE Users SET IsRemember = False", connection))
                {
                    command.ExecuteNonQuery();
                }
            }
        }

        /// <summary>
        /// 注册用户
        /// </summary>
        /// <param name="user"></param>
        /// <returns></returns>
        public static Tuple<bool,string> SignUp(User user)
        {
            using (SQLiteConnection connection = new SQLiteConnection(string.Format("Data Source={0};Version=3;", FilePath.UserDbPath)))
            {
                using (SQLiteCommand command = new SQLiteCommand($"select * from Users WHERE UserName='{user.UserName}'", connection))
                {
                    connection.Open();
                    // 执行命令
                    if (command.ExecuteScalar() != null)
                    {
                        return new Tuple<bool, string>(false,"已存在同名用户");
                    }
                    command.CommandText = $"insert into Users(UserName, UserPassword, UserPart, AddTime,IsRemember) values('{user.UserName}', '{user.UserPassword}', '{user.userPart.ToString()}', '{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff")}',False)";
                    command.ExecuteNonQuery();
                    return new Tuple<bool, string>(true, "");
                }
            }
        }
        #endregion

        #region 产品加载记录
        /// <summary>
        /// 添加一条产品加载记录至数据库
        /// </summary>
        /// <param name="id"></param>
        /// <param name="productName"></param>
        /// <param name="IsAuto"></param>
        public static void AddLoadProductRecord(Guid id, string productName, bool IsAuto)
        {
            try
            {
                string cmd = $"insert into LoadProductRecords(Time,ProductID,ProductName,IsAutoLoad) values('{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}','{id.ToString()}','{productName}',{IsAuto})";
                WriteCommandToDatabase(cmd, FilePath.ProductionRecordsDbPath);
            }
            catch (Exception ex)
            {
                LogHelper.WriteLogError("添加一条产品加载记录至数据库时出错!", ex);
            }
        }

        /// <summary>
        /// 获取最后一次加载的产品
        /// </summary>
        /// <returns></returns>
        public static Guid GetLastLoadProduct()
        {
            try
            {
                using (SQLiteConnection conn = new SQLiteConnection(string.Format("Data Source={0};Version=3;", FilePath.ProductionRecordsDbPath)))
                {
                    if (conn.State != ConnectionState.Open)
                    {
                        conn.Open();
                    }
                    string query = "select ProductID from LoadProductRecords order by Time desc limit 1";
                    // 创建命令对象
                    using (SQLiteCommand command = new SQLiteCommand(query, conn))
                    {
                        // 执行查询，获取结果
                        object result = command.ExecuteScalar();

                        // 检查结果是否为 null
                        if (result != null)
                        {
                            return Guid.Parse(result.ToString());
                        }
                        else
                        {
                            return Guid.Empty;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLogError("获取最后一次加载的产品时出错！", ex);
                return Guid.Empty;
            }
        }

        /// <summary>
        /// 修改产品名称
        /// </summary>
        /// <param name="NewProduct"></param>
        /// <param name="OldProductName"></param>
        public static void RevampProductName(ProductModel NewProduct, string OldProductName)
        {
            string dbpath = FilePath.ProductsPath + "//" + NewProduct.Name + "//" + NewProduct.Name + ".db";
            using (SQLiteConnection conn = new SQLiteConnection(string.Format("Data Source={0};Version=3;", dbpath)))
            {
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
                foreach (var item in NewProduct.CameraProcedures)
                {
                    foreach (var item1 in item.ProcedureModels)
                    {
                        string procname = item1.Name.Split('-')[2];
                        string strold= $"{OldProductName}_{item1.CameraName}_{procname}";
                        string strnew = $"{NewProduct.Name}_{item1.CameraName}_{procname}";
                        string query = $"ALTER TABLE {strold} RENAME TO {strnew}";
                        // 创建命令对象
                        using (SQLiteCommand command = new SQLiteCommand(query, conn))
                        {
                            /// 执行命令
                            command.ExecuteNonQuery();
                        }
                    }
                }
                
            }
        }
        #endregion

        #region 产品生产数据记录

        /// <summary>
        /// 更新产品数据库表格配置
        /// </summary>
        /// <param name="product"></param>
        public static void UpdateProductDatabase(ProductModel product)
        {
            string productname = product.Name;
            if (char.IsDigit(productname[0]))
            {
                productname = "a" + productname;
            }
            string dbpath = FilePath.ProductsPath + "//" + product.Name + "//" + productname + ".db";
            if (!File.Exists(dbpath))
            {
                //如果数据库文件不存在，则创建一个数据库
                SQLiteConnection.CreateFile(dbpath);
            }

            foreach (var item in product.CameraProcedures)
            {
                //遍历所有的流程
                foreach (var item1 in item.ProcedureModels)
                {
                    if (item1.ToolBlock==null)
                    {
                        continue;
                    }
                    CogToolBlockTerminalCollection outputCollection = item1.ToolBlock.Outputs;
                    string tablename = item1.Name.Replace('-','_').Replace(' ', '_').Trim();
                    if (char.IsDigit(tablename[0]))
                    {
                        tablename = "a" + tablename;
                    }
                    //如果不存在表，则创建一个表
                    if (!IsHaveTable(tablename, dbpath))
                    {
                        StringBuilder sb = new StringBuilder();
                        foreach (CogToolBlockTerminal output in outputCollection)
                        {
                            sb.Append($"_{output.Name.Replace(' ', '_').Trim()} TEXT,");
                        }
                        CreateTable(tablename, $"(_Time DATETIME,{sb.ToString().TrimEnd(',')})", dbpath);
                    }
                    else
                    {
                        if (!IsHaveColumn("_Time", tablename, dbpath))
                        {
                            AddColumn("_Time", "DATETIME", tablename, dbpath);
                        }
                        foreach (CogToolBlockTerminal output in outputCollection)
                        {
                            if (!IsHaveColumn("_" + output.Name.Replace(' ', '_').Trim(), tablename, dbpath))
                            {
                                AddColumn("_" + output.Name.Replace(' ', '_').Trim(), "TEXT", tablename, dbpath);
                            }
                        }
                    }
                }
            }
            //如果不存在表，则创建一个表
            if (!IsHaveTable("ProductionRecords", dbpath))
            {
                CreateTable("ProductionRecords", $"(_Time DATETIME,_Content TEXT)", dbpath);
            }
            EnsureLockResultTable(dbpath);
        }

        /// <summary>
        /// 增加一条相机拍照记录
        /// </summary>
        /// <param name="ProductName"></param>
        /// <param name="ProcedureName"></param>
        /// <param name="result"></param>
        public static void AddCameraRecords(string ProductName, string ProcedureName, CogToolBlockTerminalCollection result)
        {
            try
            {
                string productname = ProductName;
                if (char.IsDigit(productname[0]))
                {
                    productname = "a" + productname;
                }
                string dbpath = FilePath.ProductsPath + "//" + ProductName + "//" + productname + ".db";
                if (!File.Exists(dbpath))
                {
                    //如果数据库文件不存在，则创建一个数据库
                    SQLiteConnection.CreateFile(dbpath);
                }
                string tablename = ProcedureName.Replace('-', '_').Replace(' ', '_').Trim();
                if (char.IsDigit(tablename[0]))
                {
                    tablename = "a" + tablename;
                }
                if (!IsHaveTable(tablename, dbpath))
                {
                    StringBuilder sb = new StringBuilder();
                    foreach (CogToolBlockTerminal output in result)
                    {
                        sb.Append($"_{output.Name.Replace(' ', '_').Trim()} TEXT,");
                    }
                    CreateTable(tablename, $"(_Time DATETIME,{sb.ToString().TrimEnd(',')})", dbpath);
                }
                StringBuilder ha = new StringBuilder();
                StringBuilder vs = new StringBuilder();
                foreach (CogToolBlockTerminal output in result)
                {
                    ha.Append($"_{output.Name.Replace(' ', '_').Trim()},");
                    if (output.Value!=null)
                    {
                        vs.Append($"'{output.Value.ToString()}',");
                    }
                    else
                    {
                        vs.Append($"'null',");
                    }
                }
                string cmd = $"insert into {tablename}(_Time,{ha.ToString().TrimEnd(',')}) values('{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}',{vs.ToString().TrimEnd(',')})";
                WriteCommandToDatabase(cmd, dbpath);
            }
            catch (Exception ex)
            {
                LogHelper.WriteLogError("增加一条相机拍照记录至数据库时出错！", ex);
            }
            
        }

        /// <summary>
        /// 增加一条产品的生产记录至数据库
        /// </summary>
        /// <param name="ProductName"></param>
        /// <param name="_Content"></param>
        /// <param name="_value"></param>
        public static void AddProductProductionRecords(string ProductName,string _Content)
        {
            try
            {
                string productname = ProductName;
                if (char.IsDigit(productname[0]))
                {
                    productname = "a" + productname;
                }
                string dbpath = FilePath.ProductsPath + "//" + ProductName + "//" + productname + ".db";
                string tablename = "ProductionRecords";
                string cmd = $"insert into {tablename}(_Time,_Content) values('{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}','{_Content}')";
                WriteCommandToDatabase(cmd, dbpath);
            }
            catch (Exception ex)
            {
                LogHelper.WriteLogError("增加一条产品的生产记录至数据库时出错！", ex);
            }
        }

        private const string LockResultTableName = "LockResults";
        private const string LockResultTableColumns =
            "(Time DATETIME, Number INTEGER, LockTurns REAL, LockTorque REAL, LockDuration REAL, LockPassed BOOL, ScrewdriverProgramNumber INTEGER, CurveCsvPath TEXT, CurveImagePath TEXT)";

        /// <summary>
        /// 确保锁付结果表存在
        /// </summary>
        public static void EnsureLockResultTable(string dbpath)
        {
            if (!File.Exists(dbpath))
            {
                string dir = System.IO.Path.GetDirectoryName(dbpath);
                if (!string.IsNullOrEmpty(dir) && !Directory.Exists(dir))
                {
                    Directory.CreateDirectory(dir);
                }
                SQLiteConnection.CreateFile(dbpath);
            }
            if (!IsHaveTable(LockResultTableName, dbpath))
            {
                CreateTable(LockResultTableName, LockResultTableColumns, dbpath);
            }
        }

        private static string GetProductDatabasePath(string productName)
        {
            string productname = productName;
            if (char.IsDigit(productname[0]))
            {
                productname = "a" + productname;
            }
            return FilePath.ProductsPath + "//" + productName + "//" + productname + ".db";
        }

        private static string EscapeSqlString(string value)
        {
            return (value ?? string.Empty).Replace("'", "''");
        }

        /// <summary>
        /// 增加一条锁付结果记录至数据库（全局库；若已加载产品则同步写入产品库）
        /// </summary>
        public static void AddLockResultRecord(LockResult result, string curveCsvPath, string curveImagePath, string productName = null)
        {
            try
            {
                EnsureLockResultTable(FilePath.ProductionRecordsDbPath);
                InsertLockResultRecord(result, curveCsvPath, curveImagePath, FilePath.ProductionRecordsDbPath);

                if (!string.IsNullOrWhiteSpace(productName))
                {
                    string dbpath = GetProductDatabasePath(productName);
                    EnsureLockResultTable(dbpath);
                    InsertLockResultRecord(result, curveCsvPath, curveImagePath, dbpath);
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLogError("增加锁付结果记录至数据库时出错！", ex);
            }
        }

        private static void InsertLockResultRecord(LockResult result, string curveCsvPath, string curveImagePath, string dbpath)
        {
            string time = result.Timestamp.ToString("yyyy-MM-dd HH:mm:ss");
            string passed = result.LockPassed ? "True" : "False";
            var inv = System.Globalization.CultureInfo.InvariantCulture;
            string cmd = $"insert into {LockResultTableName}(Time,Number,LockTurns,LockTorque,LockDuration,LockPassed,ScrewdriverProgramNumber,CurveCsvPath,CurveImagePath) " +
                $"values('{time}',{result.Number},{result.LockTurns.ToString(inv)},{result.LockTorque.ToString(inv)},{result.LockDuration.ToString(inv)},{passed},{result.ScrewdriverProgramNumber}," +
                $"'{EscapeSqlString(curveCsvPath)}','{EscapeSqlString(curveImagePath)}')";
            WriteCommandToDatabase(cmd, dbpath);
        }

        /// <summary>
        /// 获取生产总数
        /// </summary>
        /// <param name="ProductName"></param>
        /// <returns></returns>
        public static Int64 GetTotalQuantity(string ProductName)
        {
            try
            {
                string productname = ProductName;
                if (char.IsDigit(productname[0]))
                {
                    productname = "a" + productname;
                }
                string dbpath = FilePath.ProductsPath + "//" + ProductName + "//" + productname + ".db";
                using (SQLiteConnection conn = new SQLiteConnection(string.Format("Data Source={0};Version=3;", dbpath)))
                {
                    if (conn.State != ConnectionState.Open)
                    {
                        conn.Open();
                    }
                    string query = "SELECT COUNT(*) FROM ProductionRecords";
                    // 创建命令对象
                    using (SQLiteCommand command = new SQLiteCommand(query, conn))
                    {
                        // 执行查询，获取结果
                        object result = command.ExecuteScalar();

                        // 检查结果是否为 null
                        if (result != null)
                        {
                            return Int64.Parse(result.ToString());
                        }
                        else
                        {
                            return 0;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLogError("从数据库获取生产总数时出错！", ex);
                return 0;
            }
        }

        /// <summary>
        /// 获取当月的生产总数
        /// </summary>
        /// <param name="ProductName"></param>
        /// <returns></returns>
        public static Int64 GetTotalQuantityMonth(string ProductName)
        {
            try
            {
                string productname = ProductName;
                if (char.IsDigit(productname[0]))
                {
                    productname = "a" + productname;
                }
                string dbpath = FilePath.ProductsPath + "//" + ProductName + "//" + productname + ".db";
                using (SQLiteConnection conn = new SQLiteConnection(string.Format("Data Source={0};Version=3;", dbpath)))
                {
                    if (conn.State != ConnectionState.Open)
                    {
                        conn.Open();
                    }

                    DateTime start = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1, 0, 0, 0);
                    DateTime end = start.AddMonths(1);

                    string query = $"SELECT COUNT(*) FROM ProductionRecords WHERE _Time>='{start.ToString("yyyy-MM-dd HH:mm:ss")}' AND _Time<='{end.ToString("yyyy-MM-dd HH:mm:ss")}'";
                    // 创建命令对象
                    using (SQLiteCommand command = new SQLiteCommand(query, conn))
                    {
                        // 执行查询，获取结果
                        object result = command.ExecuteScalar();

                        // 检查结果是否为 null
                        if (result != null)
                        {
                            return Int64.Parse(result.ToString());
                        }
                        else
                        {
                            return 0;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLogError("获取获取当月的生产总数时出错！", ex);
                return 0;
            }
        }

        /// <summary>
        /// 获取当前周的生产总数
        /// </summary>
        /// <param name="ProductName"></param>
        /// <returns></returns>
        public static Int64 GetTotalQuantityWeek(string ProductName)
        {
            try
            {
                string productname = ProductName;
                if (char.IsDigit(productname[0]))
                {
                    productname = "a" + productname;
                }
                string dbpath = FilePath.ProductsPath + "//" + ProductName + "//" + productname + ".db";
                using (SQLiteConnection conn = new SQLiteConnection(string.Format("Data Source={0};Version=3;", dbpath)))
                {
                    if (conn.State != ConnectionState.Open)
                    {
                        conn.Open();
                    }

                    DateTime start = DateTime.Now.AddDays(-((int)DateTime.Now.DayOfWeek-1));
                    DateTime end = start.AddDays(7);
                    start = new DateTime(start.Year, start.Month, start.Day, 0, 0, 0);
                    end= new DateTime(end.Year, end.Month, end.Day, 0, 0, 0);
                    string query = $"SELECT COUNT(*) FROM ProductionRecords WHERE _Time>='{start.ToString("yyyy-MM-dd HH:mm:ss")}' AND _Time<='{end.ToString("yyyy-MM-dd HH:mm:ss")}'";
                    // 创建命令对象
                    using (SQLiteCommand command = new SQLiteCommand(query, conn))
                    {
                        // 执行查询，获取结果
                        object result = command.ExecuteScalar();

                        // 检查结果是否为 null
                        if (result != null)
                        {
                            return Int64.Parse(result.ToString());
                        }
                        else
                        {
                            return 0;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLogError("获取当前周的生产总数时出错！", ex);
                return 0;
            }
        }

        /// <summary>
        /// 获取当天的生产总数
        /// </summary>
        /// <param name="ProductName"></param>
        /// <returns></returns>
        public static Int64 GetTotalQuantityToday(string ProductName)
        {
            try
            {
                string productname = ProductName;
                if (char.IsDigit(productname[0]))
                {
                    productname = "a" + productname;
                }
                string dbpath = FilePath.ProductsPath + "//" + ProductName + "//" + productname + ".db";
                using (SQLiteConnection conn = new SQLiteConnection(string.Format("Data Source={0};Version=3;", dbpath)))
                {
                    if (conn.State != ConnectionState.Open)
                    {
                        conn.Open();
                    }

                    DateTime now= DateTime.Now;
                    DateTime start = new DateTime(now.Year, now.Month, now.Day,0,0,0);

                    string query = $"SELECT COUNT(*) FROM ProductionRecords WHERE _Time>='{start.ToString("yyyy-MM-dd HH:mm:ss")}' AND _Time<='{now.ToString("yyyy-MM-dd HH:mm:ss")}'";
                    // 创建命令对象
                    using (SQLiteCommand command = new SQLiteCommand(query, conn))
                    {
                        // 执行查询，获取结果
                        object result = command.ExecuteScalar();

                        // 检查结果是否为 null
                        if (result != null)
                        {
                            return Int64.Parse(result.ToString());
                        }
                        else
                        {
                            return 0;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLogError("获取当天的生产总数时出错！", ex);
                return 0;
            }
        }

        /// <summary>
        /// 获取UPH
        /// </summary>
        /// <param name="ProductName"></param>
        /// <returns></returns>
        public static Int64 GetUPH(string ProductName)
        {
            try
            {
                string productname = ProductName;
                if (char.IsDigit(productname[0]))
                {
                    productname = "a" + productname;
                }
                string dbpath = FilePath.ProductsPath + "//" + ProductName + "//" + productname + ".db";
                using (SQLiteConnection conn = new SQLiteConnection(string.Format("Data Source={0};Version=3;", dbpath)))
                {
                    if (conn.State != ConnectionState.Open)
                    {
                        conn.Open();
                    }

                    DateTime now = DateTime.Now;
                    DateTime start = now.AddHours(-1);

                    string query = $"SELECT COUNT(*) FROM ProductionRecords WHERE _Time>='{start.ToString("yyyy-MM-dd HH:mm:ss")}' AND _Time<='{now.ToString("yyyy-MM-dd HH:mm:ss")}'";
                    // 创建命令对象
                    using (SQLiteCommand command = new SQLiteCommand(query, conn))
                    {
                        // 执行查询，获取结果
                        object result = command.ExecuteScalar();

                        // 检查结果是否为 null
                        if (result != null)
                        {
                            return Int64.Parse(result.ToString());
                        }
                        else
                        {
                            return 0;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLogError("获取UPH时出错！", ex);
                return 0;
            }
        }

        /// <summary>
        /// 产品计数清零
        /// </summary>
        /// <param name="ProductName"></param>
        public static void ResetCounter(string ProductName)
        {
            try
            {
                string productname = ProductName;
                if (char.IsDigit(productname[0]))
                {
                    productname = "a" + productname;
                }
                string dbpath = FilePath.ProductsPath + "//" + ProductName + "//" + productname + ".db";
                using (SQLiteConnection conn = new SQLiteConnection(string.Format("Data Source={0};Version=3;", dbpath)))
                {
                    if (conn.State != ConnectionState.Open)
                    {
                        conn.Open();
                    }
                    int index = 1;
                    string newTableName = $"ProductionRecords1";
                    while (true)
                    {
                        newTableName =$"ProductionRecords{index}";
                        string query1 = $"select COUNT(*) from sqlite_master where type='table' and name ='{newTableName}'";
                        // 创建命令对象
                        using (SQLiteCommand command = new SQLiteCommand(query1, conn))
                        {
                            // 执行查询，获取结果
                            object result = command.ExecuteScalar();

                            if (!(result != null && Convert.ToBoolean(result)))
                            {
                                break;
                            }
                        }
                        index++;
                    }

                    //复制数据表
                    string query = $"CREATE TABLE {newTableName} AS SELECT * FROM ProductionRecords";
                    using (SQLiteCommand command = new SQLiteCommand(query, conn))
                    {
                        command.ExecuteNonQuery();
                    }

                    //清空数据表
                    query = $"DELETE FROM ProductionRecords";
                    using (SQLiteCommand command = new SQLiteCommand(query, conn))
                    {
                        command.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLogError("产品计数清零时出错！", ex);
            }
        }
        #endregion

        #region 报警记录
        /// <summary>
        /// 添加一条报警记录
        /// </summary>
        /// <param name="alarmmesg"></param>
        public static void AppendAlarm(string alarmmesg)
        {
            try
            {
                string cmd = $"insert into Alarm(Time,ErrorCode,ErrorMessage,Countermeasure) values('{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}','0','alarmmesg','')";
                WriteCommandToDatabase(cmd, FilePath.ProductionRecordsDbPath);
            }
            catch (Exception ex)
            {
                LogHelper.WriteLogError("添加一条报警记录至数据库时出错!", ex);
            }
        }
        #endregion

        #region 数据统计查询
        /// <summary>
        /// 获取产品的总的统计数据
        /// </summary>
        /// <param name="ProductName"></param>
        /// <returns></returns>
        public static Dictionary<string,Int64> GetTotalProductStatistical(string ProductName)
        {
            Dictionary<string, Int64> list = new Dictionary<string, Int64>();
            var items = GetAllItemForProductionRecords(ProductName);
            for (int i = 0; i < items.Length; i++)
            {
                var count = GetItemCountForProductionRecords(ProductName, items[i]);
                list.Add(items[i], count);
            }
            return list;
        }

        /// <summary>
        /// 获取当前月每天的产能统计
        /// </summary>
        /// <param name="ProductName"></param>
        /// <returns></returns>
        public static Dictionary<DateTime, Dictionary<string, Int64>> GetProductStatisticalMonth(string ProductName)
        {
            Dictionary<DateTime, Dictionary<string, Int64>> list = new Dictionary<DateTime, Dictionary<string, long>>();
            try
            {
                var Items = GetAllItemForProductionRecords(ProductName);
                string dbpath = FilePath.ProductsPath + "//" + ProductName + "//" + ProductName + ".db";
                using (SQLiteConnection conn = new SQLiteConnection(string.Format("Data Source={0};Version=3;", dbpath)))
                {
                    if (conn.State != ConnectionState.Open)
                    {
                        conn.Open();
                    }

                    DateTime start = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1, 0, 0, 0);
                    DateTime end = start.AddMonths(1);
                    
                    int index = 1;
                    while (true)
                    {
                        DateTime curent= start.AddDays(index);
                        list.Add(curent.AddDays(-1),new Dictionary<string, long>());
                        foreach (var item in Items)
                        {
                            string query = $"SELECT COUNT(*) FROM ProductionRecords WHERE _Time>='{curent.AddDays(-1).ToString("yyyy-MM-dd HH:mm:ss")}' AND _Time<='{curent.ToString("yyyy-MM-dd HH:mm:ss")}' AND _Content='{item}'";
                            // 创建命令对象
                            using (SQLiteCommand command = new SQLiteCommand(query, conn))
                            {
                                // 执行查询，获取结果
                                object result = command.ExecuteScalar();

                                // 检查结果是否为 null
                                if (result != null)
                                {
                                    list[curent.AddDays(-1)].Add(item, Int64.Parse(result.ToString()));
                                }
                                else
                                {
                                    list[curent.AddDays(-1)].Add(item, 0);
                                }
                            }
                        }
                        if (curent >= end)
                            break;
                        index++;
                    }
                }
                return list;
            }
            catch (Exception ex)
            {
                LogHelper.WriteLogError("获取当前月每天的产能统计时出错！", ex);
                return list;
            }
        }

        /// <summary>
        /// 获取当前周每天的产能统计
        /// </summary>
        /// <param name="ProductName"></param>
        /// <returns></returns>
        public static Dictionary<int, Dictionary<string, Int64>> GetProductStatisticalWeek(string ProductName)
        {
            Dictionary<int, Dictionary<string, Int64>> list = new Dictionary<int, Dictionary<string, long>>();
            try
            {
                var Items = GetAllItemForProductionRecords(ProductName);
                string dbpath = FilePath.ProductsPath + "//" + ProductName + "//" + ProductName + ".db";
                using (SQLiteConnection conn = new SQLiteConnection(string.Format("Data Source={0};Version=3;", dbpath)))
                {
                    if (conn.State != ConnectionState.Open)
                    {
                        conn.Open();
                    }

                    DateTime start = DateTime.Now.AddDays(-((int)DateTime.Now.DayOfWeek - 1));
                    DateTime end = start.AddDays(7);

                    int index = 1;
                    while (true)
                    {
                        DateTime curent = start.AddDays(index);
                        list.Add(index, new Dictionary<string, long>());
                        foreach (var item in Items)
                        {
                            string query = $"SELECT COUNT(*) FROM ProductionRecords WHERE _Time>='{curent.AddDays(-1).ToString("yyyy-MM-dd HH:mm:ss")}' AND _Time<='{curent.ToString("yyyy-MM-dd HH:mm:ss")}' AND _Content='{item}'";
                            // 创建命令对象
                            using (SQLiteCommand command = new SQLiteCommand(query, conn))
                            {
                                // 执行查询，获取结果
                                object result = command.ExecuteScalar();

                                // 检查结果是否为 null
                                if (result != null)
                                {
                                    list[index].Add(item, Int64.Parse(result.ToString()));
                                }
                                else
                                {
                                    list[index].Add(item, 0);
                                }
                            }
                        }
                        if (curent >= end)
                            break;
                        index++;
                    }
                }
                return list;
            }
            catch (Exception ex)
            {
                LogHelper.WriteLogError("获取当前周每天的产能统计时出错！", ex);
                return list;
            }
        }

        /// <summary>
        /// 获取今天每小时的产能统计
        /// </summary>
        /// <param name="ProductName"></param>
        /// <returns></returns>
        public static Dictionary<int, Dictionary<string, Int64>> GetProductStatisticalDay(string ProductName)
        {
            Dictionary<int, Dictionary<string, Int64>> list = new Dictionary<int, Dictionary<string, long>>();
            try
            {
                var Items = GetAllItemForProductionRecords(ProductName);
                string dbpath = FilePath.ProductsPath + "//" + ProductName + "//" + ProductName + ".db";
                using (SQLiteConnection conn = new SQLiteConnection(string.Format("Data Source={0};Version=3;", dbpath)))
                {
                    if (conn.State != ConnectionState.Open)
                    {
                        conn.Open();
                    }
                    DateTime start = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, 0, 0, 0);
                    DateTime end = start.AddDays(1);

                    int index = 1;
                    while (true)
                    {
                        DateTime curent = start.AddHours(index);
                        list.Add(index, new Dictionary<string, long>());
                        foreach (var item in Items)
                        {
                            string query = $"SELECT COUNT(*) FROM ProductionRecords WHERE _Time>='{curent.AddHours(-1).ToString("yyyy-MM-dd HH:mm:ss")}' AND _Time<='{curent.ToString("yyyy-MM-dd HH:mm:ss")}' AND _Content='{item}'";
                            // 创建命令对象
                            using (SQLiteCommand command = new SQLiteCommand(query, conn))
                            {
                                // 执行查询，获取结果
                                object result = command.ExecuteScalar();

                                // 检查结果是否为 null
                                if (result != null)
                                {
                                    list[index].Add(item, Int64.Parse(result.ToString()));
                                }
                                else
                                {
                                    list[index].Add(item, 0);
                                }
                            }
                        }
                        if (curent >= end)
                            break;
                        index++;
                    }
                }
                return list;
            }
            catch (Exception ex)
            {
                LogHelper.WriteLogError("获取今天每小时的产能统计时出错！", ex);
                return list;
            }
        }

        /// <summary>
        /// 从数据库获取生产计数的类别项
        /// </summary>
        /// <param name="ProductName"></param>
        /// <returns></returns>
        public static string[] GetAllItemForProductionRecords(string ProductName)
        {
            try
            {
                List<string> items=new List<string>();
                string dbpath = FilePath.ProductsPath + "//" + ProductName + "//" + ProductName + ".db";
                using (SQLiteConnection conn = new SQLiteConnection(string.Format("Data Source={0};Version=3;", dbpath)))
                {
                    if (conn.State != ConnectionState.Open)
                    {
                        conn.Open();
                    }
                    string query = $"SELECT DISTINCT _Content FROM ProductionRecords";
                    // 创建命令对象
                    using (SQLiteCommand command = new SQLiteCommand(query, conn))
                    {
                        using (SQLiteDataReader reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                string item = reader["_Content"].ToString();
                                items.Add(item);
                            }
                        }
                        return items.ToArray();
                    }
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLogError("从数据库获取生产计数的类别项时出错！", ex);
                return null;
            }
        }

        /// <summary>
        /// 从数据库中获取指定项的统计数量
        /// </summary>
        /// <param name="ProductName"></param>
        /// <param name="item"></param>
        /// <returns></returns>
        public static Int64 GetItemCountForProductionRecords(string ProductName,string item) 
        {
            try
            {
                List<string> items = new List<string>();
                string dbpath = FilePath.ProductsPath + "//" + ProductName + "//" + ProductName + ".db";
                using (SQLiteConnection conn = new SQLiteConnection(string.Format("Data Source={0};Version=3;", dbpath)))
                {
                    if (conn.State != ConnectionState.Open)
                    {
                        conn.Open();
                    }
                    string query = $"SELECT COUNT(*) FROM ProductionRecords WHERE _Content='{item}'";
                    // 创建命令对象
                    using (SQLiteCommand command = new SQLiteCommand(query, conn))
                    {
                        // 执行查询，获取结果
                        object result = command.ExecuteScalar();

                        // 检查结果是否为 null
                        if (result != null)
                        {
                            return Int64.Parse(result.ToString());
                        }
                        else
                        {
                            return 0;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLogError("从数据库中获取指定项的统计数量时出错！", ex);
                return 0;
            }
        }

        /// <summary>
        /// 从数据库中获取指定项的统计数量
        /// </summary>
        /// <param name="ProductName"></param>
        /// <param name="item"></param>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <returns></returns>
        public static Int64 GetItemCountForProductionRecords(string ProductName, string item,DateTime start,DateTime end)
        {
            try
            {
                List<string> items = new List<string>();
                string dbpath = FilePath.ProductsPath + "//" + ProductName + "//" + ProductName + ".db";
                using (SQLiteConnection conn = new SQLiteConnection(string.Format("Data Source={0};Version=3;", dbpath)))
                {
                    if (conn.State != ConnectionState.Open)
                    {
                        conn.Open();
                    }
                    string query = $"SELECT COUNT(*) FROM ProductionRecords WHERE _Content='{item}' AND _Time>='{start.ToString("yyyy-MM-dd HH:mm:ss")}' AND _Time<='{end.ToString("yyyy-MM-dd HH:mm:ss")}'";
                    // 创建命令对象
                    using (SQLiteCommand command = new SQLiteCommand(query, conn))
                    {
                        // 执行查询，获取结果
                        object result = command.ExecuteScalar();

                        // 检查结果是否为 null
                        if (result != null)
                        {
                            return Int64.Parse(result.ToString());
                        }
                        else
                        {
                            return 0;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLogError("从数据库中获取指定项的统计数量时出错！", ex);
                return 0;
            }
        }

        /// <summary>
        /// 获取产品流程相机数据记录
        /// </summary>
        /// <param name="ProductName"></param>
        /// <param name="ProcedureName"></param>
        /// <returns></returns>
        public static Task<DataTable> GetProductProcessRecords(string ProductName, string ProcedureName, DateTime? dt1 = null, DateTime? dt2 = null)
        {
            try
            {
                string dbpath = FilePath.ProductsPath + "//" + ProductName + "//" + ProductName + ".db";
                using (SQLiteConnection conn = new SQLiteConnection(string.Format("Data Source={0};Version=3;", dbpath)))
                {
                    if (conn.State != ConnectionState.Open)
                    {
                        conn.Open();
                    }
                    string query = $"select * from {ProcedureName.Replace('-','_').Replace(' ', '_').Trim()}";
                    if (dt1!=null && dt2!=null)
                    {
                        query = $"select * from {ProcedureName.Replace('-', '_').Replace(' ', '_').Trim()} WHERE Time>='{((DateTime)dt1).ToString("yyyy-MM-dd HH:mm:ss")}' AND Time<='{((DateTime)dt2).ToString("yyyy-MM-dd HH:mm:ss")}'";
                    }
                    // 创建命令对象
                    using (SQLiteCommand command = new SQLiteCommand(query, conn))
                    {
                        
                        using (SQLiteDataAdapter reader = new SQLiteDataAdapter(command))
                        {
                            DataTable table = new DataTable();
                            reader.Fill(table);
                            return Task.FromResult(table);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLogError("获取产品流程相机数据记录时出错！", ex);
                return Task.FromResult(new DataTable());
            }
        }

        /// <summary>
        /// 获取报警记录
        /// </summary>
        /// <returns></returns>
        public static Task<DataTable> GetAlarmRecords(DateTime dt1, DateTime dt2)
        {
            try
            {
                using (SQLiteConnection conn = new SQLiteConnection(string.Format("Data Source={0};Version=3;", FilePath.ProductionRecordsDbPath)))
                {
                    if (conn.State != ConnectionState.Open)
                    {
                        conn.Open();
                    }
                    string query = $"select * from Alarm WHERE Time>='{dt1.ToString("yyyy-MM-dd HH:mm:ss")}' AND Time<='{dt2.ToString("yyyy-MM-dd HH:mm:ss")}'";
                    // 创建命令对象
                    using (SQLiteCommand command = new SQLiteCommand(query, conn))
                    {

                        using (SQLiteDataAdapter reader = new SQLiteDataAdapter(command))
                        {
                            DataTable table = new DataTable();
                            reader.Fill(table);
                            return Task.FromResult(table);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLogError("获取获取报警记录时出错！", ex);
                return Task.FromResult(new DataTable());
            }
        }
        #endregion

        /// <summary>
        /// 检测数据库库中是否存在表
        /// </summary>
        /// <param name="TableName">表名称</param>
        public static bool IsHaveTable(string TableName,string dbpath)
        {
            try
            {
                //创建连接字符串
                SQLiteConnection conn = new SQLiteConnection(string.Format("Data Source={0};Version=3;", dbpath));
                //打开数据库
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
                string cmdStr = $"select COUNT(*) from sqlite_master where type='table' and name ='{TableName}' ";       //sql语句，查询表
                //创建命令
                SQLiteCommand cmd = new SQLiteCommand(cmdStr, conn);
                bool _Flog = false;
                // 执行命令
                if (Convert.ToInt32(cmd.ExecuteScalar()) == 0)
                {
                    //该表不存在
                    _Flog = false;
                }
                else
                {
                    //该表存在
                    _Flog = true;
                }
                //释放资源
                conn.Close();
                cmd.Dispose();
                conn.Dispose();
                return _Flog;
            }
            catch (Exception ex)
            {
                LogHelper.WriteLogError($"检测数据库库中是否存在表（{TableName}）时出错", ex);
                return false;
            }
        }

        /// <summary>
        /// 为数据创建表
        /// </summary>
        /// <param name="TableName">表的名称</param>
        /// <param name="column">列的名称和类型字符串(Name varchar,Team varchar, Number varchar)</param>
        public static bool CreateTable(string TableName, string column,string dbpath)
        {
            string query = "CREATE TABLE " + TableName + " " + column;
            return WriteCommandToDatabase(query, dbpath);
        }

        /// <summary>
        /// 为表增加列
        /// </summary>
        /// <param name="ColumnName"></param>
        /// <param name="ColumnType"></param>
        /// <param name="TableName"></param>
        /// <param name="dbpath"></param>
        public static void AddColumn(string ColumnName,string ColumnType,string TableName, string dbpath)
        {
            using (SQLiteConnection connection = new SQLiteConnection(string.Format("Data Source={0};Version=3;", dbpath)))
            {
                // 打开数据库连接
                connection.Open();
                string query = $"ALTER TABLE {TableName} ADD COLUMN {ColumnName} {ColumnType}";
                // 创建命令对象
                using (SQLiteCommand command = new SQLiteCommand(query, connection))
                {
                    // 执行 SQL 语句
                    command.ExecuteNonQuery();
                }
            }
        }

        /// <summary>
        /// 将命令写入数据库
        /// </summary>
        /// <param name="cmdstr"></param>
        public static bool WriteCommandToDatabase(string cmdstr,string dbpath)
        {
            try
            {
                //创建连接字符串
                SQLiteConnection conn = new SQLiteConnection(string.Format("Data Source={0};Version=3;", dbpath));
                //打开数据库
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
                //创建命令
                SQLiteCommand cmd = new SQLiteCommand(cmdstr, conn);
                // 执行命令
                cmd.ExecuteNonQuery();
                //释放资源
                conn.Close();
                cmd.Dispose();
                conn.Dispose();
                return true;
            }
            catch (Exception ex)
            {
                LogHelper.WriteLogError($"写入命令至数据库时出错:【{cmdstr}】", ex);
                return false;
            }
        }

        /// <summary>
        /// 检索数据
        /// </summary>
        /// <param name="strCmd"></param>
        /// <param name="tab"></param>
        /// <returns></returns>
        public static bool Select(string strCmd, ref DataTable tab,string dbPath)
        {
            SQLiteConnection conn;
            SQLiteCommand cmd;
            SQLiteDataAdapter da;
            try
            {
                //创建连接字符串
                conn = new SQLiteConnection(string.Format("Data Source={0};Version=3;", dbPath));
                //打开数据库
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
                //创建命令
                cmd = new SQLiteCommand(strCmd, conn);

                //执行命令
                //SQLiteDataReader sr = cmd.ExecuteReader();
                da = new SQLiteDataAdapter(cmd);
                da.Fill(tab);
                da.Dispose();
                //释放资源
                conn.Close();
                cmd.Dispose();
                return true;
            }
            catch (Exception ex)
            {
                LogHelper.WriteLogError("检索数据库时出错！", ex);
                return false;
            }
        }

        /// <summary>
        /// 检查是否存在包含特定字符串的记录
        /// </summary>
        /// <param name="TableName"></param>
        /// <param name="ColumnName"></param>
        /// <param name="searchString"></param>
        /// <returns></returns>
        public static bool RecordExists(string TableName, string ColumnName, string searchString,string dbPath)
        {
            try
            {
                //创建连接字符串
                SQLiteConnection conn = new SQLiteConnection(string.Format("Data Source={0};Version=3;", dbPath));
                //打开数据库
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
                using (SQLiteCommand command = new SQLiteCommand($"SELECT COUNT(*) FROM {TableName} WHERE {ColumnName} LIKE '%{searchString}%'", conn))
                {
                    long count = (long)command.ExecuteScalar();
                    conn.Close();
                    conn.Dispose();
                    return count > 0;
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLogError($"检查是否存在包含特定字符串的记录时出错", ex);
                return false;
            }
        }

        /// <summary>
        /// 检测表中是否存在列
        /// </summary>
        /// <param name="columnName"></param>
        /// <param name="TableName"></param>
        /// <param name="dbpath"></param>
        /// <returns></returns>
        public static bool IsHaveColumn(string columnName, string TableName, string dbpath)
        {
            try
            {
                using (SQLiteConnection connection = new SQLiteConnection(string.Format("Data Source={0};Version=3;", dbpath)))
                {
                    // 打开数据库连接
                    connection.Open();
                    string query = $"PRAGMA table_info({TableName});";
                    // 创建命令对象
                    using (SQLiteCommand command = new SQLiteCommand(query, connection))
                    {
                        // 执行查询
                        using (SQLiteDataReader reader = command.ExecuteReader())
                        {
                            // 检查是否存在指定列
                            bool columnExists = false;
                            while (reader.Read())
                            {
                                string columnNameFromDB = reader["name"].ToString();
                                if (columnNameFromDB == columnName)
                                {
                                    columnExists = true;
                                    break;
                                }
                            }

                            // 输出结果
                            if (columnExists)
                            {
                                return true;
                            }
                            else
                            {
                                return false;
                            }
                        }
                    }
                }
            }
            catch (Exception)
            {
                return false;
            }
        }
    }
}