xiaomiMes/MainForm/ClassFile/ProjectClass/SQLHelper.cs

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.IO;
using HslCommunication.LogNet;
using Org.BouncyCastle.Ocsp;
using System.Windows.Forms;
using SqlSugar;
using MainForm.ClassFile.ProjectClass;
using NPOI.SS.Formula.Functions;



namespace MainForm
{
    public class SQLHelper
    {
        public static string DBDir = GlobalContext.DBDir;
        //用于数据库日志记录
        private static ILogNet logNet = new LogNetDateTime(GlobalContext.SQLLogDir, GenerateMode.ByEveryDay);
        /// <summary>
        /// 使用锁防止多线程同时操作数据库表
        /// </summary>
        private static readonly object sqlLock = new object();

        public class ResponseMessage
        {
            public string text { get; set; }
            public bool result { get; set; }
        }
        /// <summary>
        /// SQL连接
        /// </summary>
        private static SqlConnection connection = null;

        public static string connString = "";
        public static void DBInitWork()
        {
            GlobalContext.User = "sa";
            GlobalContext.PassWord = "Bb123456";
            //以月为单位存
            string subDir = DateTime.Now.ToString("yyyyMM");
            string dataBaseName = "DB" + subDir;
            connString =
                @"server= " + GlobalContext.Server +
                ";database= " + dataBaseName +
                ";uid=" + GlobalContext.User +
                ";pwd=" + GlobalContext.PassWord;
            //判断路径是否存在,不存在则创建路径
            if (!Directory.Exists(DBDir + subDir)) Directory.CreateDirectory(DBDir + subDir);
            //创建数据库和表
            SQLHelper.CreateDataBase(DBDir, subDir, dataBaseName);
            SQLHelper.CreateDataBase();
        }

        /// <summary>
        /// 查询
        /// </summary>
        /// <param name="SQLString"></param>
        /// <param name="cmdParms"></param>
        /// <returns></returns>
        /// <exception cref="Exception"></exception>
        public static DataSet Query(string SQLString, List<SqlParameter> cmdParms, string connectionString)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand();
                PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                {
                    DataSet ds = new DataSet();
                    try
                    {
                        da.Fill(ds, "ds");
                        cmd.Parameters.Clear();
                    }
                    catch (System.Data.SqlClient.SqlException ex)
                    {
                        throw new Exception(ex.Message);
                    }
                    return ds;
                }
            }
        }

        /// <summary>
        /// 执行SQL语句，返回影响的记录数
        /// </summary>
        /// <param name="SQLString">SQL语句</param>
        /// <returns>影响的记录数</returns>
        public static int ExecuteSQL(string SQLString, List<SqlParameter> cmdParms, string connectionString)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    try
                    {
                        PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                        int rows = cmd.ExecuteNonQuery();
                        cmd.Parameters.Clear();
                        return rows;
                    }
                    catch (System.Data.SqlClient.SqlException e)
                    {
                        throw e;
                    }
                }
            }
        }
        private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string cmdText, List<SqlParameter> cmdParms)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            if (trans != null)
                cmd.Transaction = trans;
            cmd.CommandType = CommandType.Text;//cmdType;
            if (cmdParms != null)
            {
                foreach (SqlParameter parameter in cmdParms)
                {
                    if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                        (parameter.Value == null))
                    {
                        parameter.Value = DBNull.Value;
                    }
                    cmd.Parameters.Add(parameter);
                }
            }
        }
        /// <summary>
        /// 创建SQL连接属性
        /// </summary>
        public static SqlConnection Connection
        {
            get
            {
                //DBInitWork();
                try
                {
                    if (connection == null)//如果没有创建连接，则先创建
                    {
                        //从配置文件中获取SQL连接字段
                        //string connStr = ConfigurationManager.ConnectionStrings["ConnetcionNmae"].ToString();

                        connection = new SqlConnection(connString);//创建连接
                        connection.Open();//打开连接
                    }
                    else if (connection.State == ConnectionState.Broken)//如果连接中断，则重现打开
                    {
                        connection.Close();
                        connection.Open();
                    }
                    else if (connection.State == ConnectionState.Closed)//如果关闭，则打开
                    {
                        connection.Open();
                    }
                    return connection;
                }
                catch (Exception ex)
                {
                    if (connection != null)
                    {
                        connection.Close();
                        connection.Dispose();
                    }
                    logNet.WriteError(ex.Message.ToString());
                    return null;
                }
            }
        }

        /// <summary>
        /// 重置连接
        /// </summary>
        public static void ResetConnection()
        {
            if (connection != null)
            {
                connection.Close();
                connection.Dispose();
                connection = null;
            }
        }

        /// <summary>
        /// 获取数据集
        /// </summary>
        /// <param name="str">执行字符串</param>
        /// <returns></returns>
        public static DataSet GetDataSet(string str)
        {
            lock (sqlLock)
            {
                try
                {
                    SqlDataAdapter sda = new SqlDataAdapter(str, Connection);
                    DataSet ds = new DataSet();
                    sda.Fill(ds);
                    return ds;
                }
                catch (Exception ex)
                {
                    ResetConnection();
                    logNet.WriteError(ex.Message.ToString());
                    return null;
                }
            }
        }
        /// <summary>
        /// 获取表格
        /// </summary>
        /// <param name="str">执行字符串</param>
        /// <returns></returns>
        public static DataTable GetDataTable(string str)
        {
            return GetDataSet(str).Tables[0] ?? null;
        }

        /// <summary>
        /// 执行SQL语句
        /// </summary>
        /// <param name="str"></param>
        public static string ExecuteNonQuery(string str)
        {
            string ret = string.Empty;
            try
            {
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = Connection;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = str;
                cmd.ExecuteNonQuery();
                ret = "成功";
            }
            catch (Exception ex)
            {
                ret = ex.Message.ToString();
                logNet.WriteError(ex.Message.ToString());
            }
            return ret;
        }

        /////////////////////////////////////////////////////////////////////////
        ///创建数据库和表
        /////////////////////////////////////////////////////////////////////////
        /// <summary>
        /// 判断数据库是否存在
        /// </summary>
        /// <param name="db">数据库名称</param>
        /// <returns></returns>
        public static bool IsDBExist(string db)
        {
            string createDbStr = " select * from master.dbo.sysdatabases where name " + "= '" + db + "'";
            DataTable dt = GetDataTable(createDbStr);
            if (dt?.Rows.Count > 0)
            {
                return true;
            }
            return false;
        }

        /// <summary>
        /// 判断数据库中指定表格是否存在
        /// </summary>
        /// <param name="db"></param>
        /// <param name="tb"></param>
        /// <returns></returns>
        public static bool IsTableExist(string db, string tb)
        {
            string createTbStr = "USE " + db + " select 1 from sysobjects where id =object_id('" + tb + "') and type = 'U'";
            DataTable dt = GetDataTable(createTbStr);
            if (dt?.Rows.Count > 0)
            {
                return true;
            }
            return false;
        }

        /// <summary>
        /// 判断数据库中指定表格是否存在
        /// </summary>
        /// <param name="db"></param>
        /// <param name="tb"></param>
        /// <returns></returns>
        public static bool IsTableExist(string db, string tb, string connString)
        {
            string createTbStr = "USE " + db + " select 1 from sysobjects where id =object_id('" + tb + "') and type = 'U'";
            DataSet ds = Query(createTbStr, null, connString);
            if (ds?.Tables["ds"]?.Rows.Count > 0)
            {
                return true;
            }
            return false;
        }

        /// <summary>
        /// 创建数据库表
        /// </summary>
        /// <param name="db">数据库名</param>
        /// <param name="tb">表名</param>
        public static void CreateDataTable(string db, string tb, string content)
        {
            if (IsDBExist(db) == false)
            {
                throw new Exception("数据库不存在！");
            }
            if (IsTableExist(db, tb))
            {
                throw new Exception("数据库表已经存在！");
            }
            else
            {
                string createTableStr = "USE " + db + " Create table " + tb + "(" + content + ")";
                ExecuteNonQuery(createTableStr);
            }
        }
        public static void CreateDataBase(string dbDir, string subDir, string dataBaseName)
        {
            string fileMDF = dbDir + subDir + @"\" + dataBaseName + @".mdf";
            string fileLDF = dbDir + subDir + @"\" + dataBaseName + @".ldf";

            if ((!File.Exists(fileMDF)) && (!File.Exists(fileLDF)))
            {
                SqlConnection myConn = new SqlConnection("Server=" + GlobalContext.Server
                    + ";Integrated security=SSPI;database=master");
                String strSQL;
                strSQL = @"CREATE DATABASE " + dataBaseName +
                     " ON PRIMARY " +
                     "(NAME = " + dataBaseName + "_Data, " +
                     "FILENAME = '" + fileMDF + "', " +
                     "SIZE = 64MB, " +
                     "MAXSIZE = UNLIMITED," +
                     "FILEGROWTH = 64MB)" +
                     "LOG ON (NAME = " + dataBaseName + "_Log, " +
                     "FILENAME = '" + fileLDF + "', " +
                     "SIZE = 64MB, " +
                     "MAXSIZE = UNLIMITED, " +
                     "FILEGROWTH = 64MB)";

                SqlCommand myCommand = new SqlCommand(strSQL, myConn);
                try
                {
                    myConn.Open();
                    myCommand.ExecuteNonQuery();
                    logNet.WriteInfo("DataBase is Created Successfully");
                    //创建StationIn表
                    string content = @"[GUID] [nvarchar](36) NOT NULL,
                                   [Workorder_code] [nvarchar](50) NOT NULL,
                                   [Mtltmrk] [nvarchar](50) NOT NULL,
                                   [Sn] [nvarchar](64) NOT NULL,
                                   [StationIn_body] [nvarchar](MAX) NOT NULL,
                                   [Parameter_values] [nvarchar](MAX) NULL,
                                   [Write_user] [nvarchar](20) NOT NULL,
                                   [Test_time] [varchar](23) NOT NULL,
                                   [Upload] [nvarchar](10) NOT NULL";
                    CreateDataTable(dataBaseName, "StationIn", content);
                    //创建ProcessData表
                    content = @"[ID] [nvarchar](50) NOT NULL,
                                   [Equipment_code] [nvarchar](50) NOT NULL,
                                   [Workorder_code] [nvarchar](50) NOT NULL,
                                   [Batch_number] [nvarchar](32) NULL,
                                   [Sn] [nvarchar](64) NULL,
                                   [Testitem] [varchar](50) NULL,
                                   [Parameter_values] [nvarchar](MAX) NOT NULL,
                                   [Write_user] [nvarchar](20) NOT NULL,
                                   [Test_time] [varchar](23) NOT NULL,
                                   [Upload] [nvarchar](10) NOT NULL";
                    CreateDataTable(dataBaseName, "ProcessData", content);
                    //创建OneCheckData表
                    content = @"    [ID] [nvarchar](50) NOT NULL,
	                                [Line_code] [nvarchar](50) NOT NULL,
	                                [Line_name] [nvarchar](50) NOT NULL,
	                                [Equipment_code] [nvarchar](50) NOT NULL,
	                                [Equipment_name] [nvarchar](50) NOT NULL,
	                                [Workorder_code] [nvarchar](50) NOT NULL,
	                                [Procedure_code] [nvarchar](32) NOT NULL,
	                                [Procedure_name] [nvarchar](32) NOT NULL,
	                                [Oneckeck_values] [nvarchar](MAX) NOT NULL,
	                                [Onecheck_empcode] [nvarchar](32) NULL,
	                                [Onecheck_empname] [nvarchar](32) NULL,
	                                [Onecheck_time] [varchar](20) NOT NULL,
	                                [Upload] [nvarchar](10) NOT NULL";
                    CreateDataTable(dataBaseName, "OneCheckData", content);
                    //创建WorkingData表-单机
                    content = @"
	                                [Date] [nvarchar](10) NOT NULL,
	                                [Equipment_code] [nvarchar](32) NOT NULL,
	                                [BootTime] [varchar](20) NOT NULL,
	                                [Bootmoment] [nvarchar](20) NOT NULL,
	                                [RunTime] [varchar](20) NOT NULL,
	                                [WoringTime] [varchar](20) NOT NULL,
                                    [RepairTime] [varchar](20) NOT NULL,
	                                [StopTime] [varchar](20) NOT NULL,
	                                [WoringNum] [nvarchar](20) NOT NULL,
	                                [NeedTime] [varchar](20) NOT NULL,
	                                [PlanStopMoment] [nvarchar](20) NOT NULL,
	                                [Equipment_status] [nvarchar](20) NOT NULL,
	                                [WorkingQty] [nchvarcharar](20) NOT NULL,
	                                [QualifiedQty] [nvarchar](20) NOT NULL,
	                                [TotalQty] [nvarchar](20) NOT NULL,
	                                [NGset] [nvarchar](20) NOT NULL,
	                                [QualifiedRateSet] [nvarchar](20) NOT NULL,
	                                [RhySet] [nvarchar](20) NOT NULL,
	                                [Update_time] [varchar](20) NOT NULL";
                    CreateDataTable(dataBaseName, "WorkingData", content);
                    //创建LineWorkingData表-整线
                    content = @"    [GUID] [nvarchar](36) NOT NULL,
	                                [LineName] [nvarchar](36) NOT NULL,
	                                [BootTimeLong] [float] NULL,
	                                [NormalTimeLong] [float] NULL,
	                                [StandbyTimeLong] [float] NULL,
	                                [FaultTimeLong] [float] NULL,
	                                [MaterialShortageTimeLong] [float] NULL,
	                                [MaintenanceTimeLong] [float] NULL,
	                                [FaultNumber] [int] NULL,
	                                [OutputNumber] [int] NULL,
	                                [QualifiedNumber] [int] NULL,
	                                [QualifiedRate] [float] NULL,
	                                [DesignRhythm] [float] NULL,
	                                [RealityRhythm] [float] NULL,
	                                [CreateTime] [datetime] NULL";
                    CreateDataTable(dataBaseName, "LineWorkingData", content);
                    //创建AlarmData表
                    content = @"    [GUID] [nvarchar](36) NOT NULL,
	                                [LineName] [nvarchar](36) NOT NULL,
	                                [AlarmType] [nvarchar](36) NOT NULL,
	                                [AlarmDesc] [nvarchar](64) NOT NULL,
	                                [StartTime] [datetime] NOT NULL,
	                                [EndTime] [datetime] NULL,
	                                [PersistTime] [int] NULL";
                    CreateDataTable(dataBaseName, "AlarmData", content);
                }
                catch (System.Exception ex)
                {
                    logNet.WriteError(ex.Message.ToString());
                }
                finally
                {
                    if (myConn.State == ConnectionState.Open)
                    {
                        myConn.Close();
                    }
                }
            }
        }

        #region
        /// <summary>
        /// 检验并创建工单信息表
        /// </summary>
        public static void CreateDataBase_DBMain()
        {
            try
            {
                // 数据库链接
                string connString =
                    "server=" + GlobalContext.Server +
                    ";database=DBMain" +
                    ";uid=" + GlobalContext.User +
                    ";pwd=" + GlobalContext.PassWord;

                // 创建OrderTable表
                if (!IsTableExist("DBMain", "OrderTable", connString))
                {
                    string content =
    @"
CREATE TABLE [dbo].[OrderTable](
	[WorkOrderNum] [nvarchar](150) NOT NULL,
	[WorkOrderStatus] [nvarchar](32) NOT NULL,
	[BatchNumber] [nvarchar](36) NULL,
	[ProductMtltmrk] [nvarchar](32) NULL,
	[ProductNo] [nvarchar](32) NULL,
	[ProductName] [nvarchar](32) NULL,
	[SupplierCode] [nvarchar](32) NULL,
	[PlannedQuantity] [nvarchar](32) NULL,
	[CompletedQuantity] [int] NULL,
	[Plnsign] [nvarchar](32) NULL,
	[Soreqdat] [nvarchar](32) NULL,
	[Process] [nvarchar](32) NULL,
	[CreatedTime] [datetime] NOT NULL,
 CONSTRAINT [PK__OrderTab__33E32040C02E055A] PRIMARY KEY CLUSTERED 
(
	[WorkOrderNum] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'车间订单号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'OrderTable', @level2type=N'COLUMN',@level2name=N'WorkOrderNum'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'订单状态' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'OrderTable', @level2type=N'COLUMN',@level2name=N'WorkOrderStatus'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'批次号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'OrderTable', @level2type=N'COLUMN',@level2name=N'BatchNumber'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'产品型号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'OrderTable', @level2type=N'COLUMN',@level2name=N'ProductMtltmrk'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'产品代号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'OrderTable', @level2type=N'COLUMN',@level2name=N'ProductNo'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'产品名称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'OrderTable', @level2type=N'COLUMN',@level2name=N'ProductName'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'供应商代码' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'OrderTable', @level2type=N'COLUMN',@level2name=N'SupplierCode'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'计划数量' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'OrderTable', @level2type=N'COLUMN',@level2name=N'PlannedQuantity'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'已完成数量' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'OrderTable', @level2type=N'COLUMN',@level2name=N'CompletedQuantity'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'计划标记' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'OrderTable', @level2type=N'COLUMN',@level2name=N'Plnsign'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'需求日期' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'OrderTable', @level2type=N'COLUMN',@level2name=N'Soreqdat'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'所属流程' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'OrderTable', @level2type=N'COLUMN',@level2name=N'Process'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'订单创建时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'OrderTable', @level2type=N'COLUMN',@level2name=N'CreatedTime'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'订单信息表' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'OrderTable'";
                    ExecuteSQL(content, null, connString);
                }
            }
            catch (System.Exception ex)
            {
                logNet.WriteError(ex.Message.ToString());
            }
        }
        #endregion


        #region 进出站相关数据记录
        //连接本地数据库
        public static SqlSugarClient db = new SqlSugarClient(new ConnectionConfig
        {
            ConnectionString = connString, // 连接到 SqlServer（不指定数据库）
            DbType = SqlSugar.DbType.SqlServer,      // 数据库类型：SqlServer
            IsAutoCloseConnection = true,        // 自动关闭连接
            InitKeyType = InitKeyType.Attribute  // 使用实体类属性来初始化表结构
        });

        //连网数据库
        public static SqlSugarClient dbmain = new SqlSugarClient(new ConnectionConfig
        {
            ConnectionString = "server=" + GlobalContext.Server +
                  ";database=DBMain" +
                  ";uid=" + GlobalContext.User +
                  ";pwd=" + GlobalContext.PassWord, // 连接到 SqlServer（不指定数据库）
            DbType = SqlSugar.DbType.SqlServer,      // 数据库类型：SqlServer
            IsAutoCloseConnection = true,        // 自动关闭连接
            InitKeyType = InitKeyType.Attribute  // 使用实体类属性来初始化表结构
        });

        // 判断表格是否存在
        public static bool CheckTableExists(SqlSugarClient db, string tableName)
        {
            //判断表是否存在
            var result = db.Ado.SqlQuery<int>($@"
                SELECT COUNT(*) 
                FROM INFORMATION_SCHEMA.TABLES 
                WHERE TABLE_NAME = @tableName", new { tableName }).FirstOrDefault() > 0;

            return result;
        }
        //创建 载具绑定物料码表、物料码绑定部件码表、PLC返回数据记录表
        public static void CreateDataBase()
        {
            try
            {
                // 3. 判断并创建表格
                if (!CheckTableExists(dbmain, "carrierbind"))
                {
                    CreateTables<CarrierBind>(dbmain);
                }

                if (!CheckTableExists(db, "productbind"))
                {
                    CreateTables<ProductBind>(db);
                }

                if (!CheckTableExists(db, "testdata"))
                {
                    CreateTables<TestData>(db);
                }
                logNet.WriteInfo("表格已创建或已存在。");
            }
            catch (Exception ex)
            {
                logNet.WriteError("表格创建失败，错误原因：" + ex.Message);
                MessageBox.Show("表格创建失败，错误码：" + ex.Message);
            }
        }

        public static void CreateTables<T>(SqlSugarClient db) where T : class, new()
        {
            db.CodeFirst.InitTables<T>();  // 根据传入的实体类类型来创建表格
            logNet.WriteInfo($"表格 {typeof(T).Name} 已创建。");
        }

        //获取载具绑定的产品码
        public static string GetProductBarcodeByCarrierCode(string CarrierCode)
        {
                string ProductBarcode = db.Queryable<CarrierBind>()
                                     .Where(x => x.CarrierCode == CarrierCode)
                                     .OrderByDescending(x => x.ID)
                                     .Select(x => x.ProductBarcode)
                                     .First();
                return ProductBarcode;
        }

        //载具码和产品码绑定关系
        public static ResponseMessage InsertCarrierBind(string carrierCode, string productBarcode, string stationId)
        {
            #region 新建CarrierBind实体，并将数据插入carrierBind表格
            // 创建 CarrierBind 实体，并通过传入的参数设置字段值
            var CarrierBind_sumRecord = new CarrierBind
            {
                CarrierCode = carrierCode,
                ProductBarcode = productBarcode,
                stationId = stationId,
            };

            // 检查记录是否已经存在
            var CarrierBind_exists = db.Queryable<CarrierBind>().Where(x => x.CarrierCode == carrierCode);
            try
            {
                if (CarrierBind_exists != null)
                {
                    db.Deleteable<CarrierBind>().Where(x => x.CarrierCode == carrierCode).ExecuteCommand();
                    logNet.WriteInfo($"载具码与产品码已存在绑定关系，先解绑。");
                }

                // 插入数据
                db.Insertable(CarrierBind_sumRecord).ExecuteCommand();
                logNet.WriteInfo($"载具码与产品码绑定成功。");
                return new ResponseMessage { result = true, text = "载具码与产品码绑定成功" };
            }
            catch (Exception ex)
            {
                logNet.WriteError($"载具码与产品码绑定失败，错误" + ex.Message);
                return new ResponseMessage { result = false, text = "载具码与产品码绑定失败，错误" + ex.Message };
            }
            #endregion
        }

        public void InsertOp10Data(string carrierCode, string productBarcode, string stationId, string userId, string hydrocooling_Barcode, int bindOrder,
                                          int throwingAmount, float cleaningPower, float cleaningPressure, float cleaningSpeed,
                                          float airKnifeHeight, float cleaningTime, int cleaningCount)
        {

            #region 新建ProductBind实体，并将数据插入productBind表格
            // 创建 ProductBind 实体，并通过传入的参数设置字段值
            var ProductBind_sumRecord = new ProductBind
            {
                CarrierCode = carrierCode,//载具码
                Hydrocooling_Barcode = hydrocooling_Barcode,  // 水冷壳体条码
                BindOrder = bindOrder                         // 绑定顺序
            };

            // 检查记录是否已经存在
            bool ProductBind_exists = db.Queryable<ProductBind>().Any(x => x.CarrierCode == ProductBind_sumRecord.CarrierCode &&
                                                          x.Hydrocooling_Barcode == ProductBind_sumRecord.Hydrocooling_Barcode &&
                                                          x.BindOrder == ProductBind_sumRecord.BindOrder);
            if (ProductBind_exists)
            {
                logNet.WriteError("OP10-ProductBind：记录已经存在！bindOrder:" + bindOrder);
            }
            else
            {
                // 插入数据
                try
                {
                    db.Insertable(ProductBind_sumRecord).ExecuteCommand();
                    logNet.WriteInfo("OP10-数据插入到 productbind 表格成功！");
                }
                catch (Exception ex)
                {
                    logNet.WriteError("OP10-数据插入到 productbind 表格出错！错误码：" + ex.Message);
                }
            }
            #endregion

            #region 将OP10数据插入testdata表格
            // 检查主键是否存在
            var existingRecord = db.Queryable<TestData>()
                    .Where(x => x.CarrierCode == carrierCode && x.Hydrocooling_Barcode == hydrocooling_Barcode && x.BindOrder == bindOrder)
                    .First();
            if (existingRecord != null)
            {
                logNet.WriteError("OP10错误：数据已存在，载具码、水冷壳体码和绑定顺序相同！");
                return; // 如果数据已存在，停止插入
            }

            // 创建 Op10 实体，并通过传入的参数设置字段值
            var op10 = new TestData
            {
                CarrierCode = carrierCode,                  // 载具码
                Hydrocooling_Barcode = hydrocooling_Barcode,// 水冷壳体码
                BindOrder = bindOrder,                      // 绑定顺序

                OP10_ThrowingAmount = throwingAmount,       // 抛料数量
                OP10_CleaningPower = cleaningPower,         // 清洗功率
                OP10_CleaningPressure = cleaningPressure,   // 清洗气压
                OP10_CleaningSpeed = cleaningSpeed,         // 清洗速度
                OP10_AirKnifeHeight = airKnifeHeight,       // 风刀高度
                OP10_CleaningTime = cleaningTime,           // 清洗时间
                OP10_CleaningCount = cleaningCount          // 清洗次数
            };

            try
            {
                db.Insertable(op10).ExecuteCommand();
                logNet.WriteInfo("OP10记录插入成功！");
            }
            catch (Exception ex)
            {
                logNet.WriteError("OP10记录插入出错！错误码：" + ex.Message);
            }
            #endregion
        }




        #region 实体
        // 定义实体类（对应数据库表）载具绑定表
        public class CarrierBind
        {
            // 主键ID，设置为自增且非空
            [SqlSugar.SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
            public int ID { get; set; }                             // 主键ID

            // 其他列
            public string CarrierCode { get; set; } = "";           // 载具码
            public string ProductBarcode { get; set; } = "";  // 产品码
            public string stationId { get; set; } = "";  // 工位ID
            public DateTime CreateTime { get; set; } = DateTime.Now;    // 创建时间
        }

        // 定义ProductBind实体类，表示表格结构
        public class ProductBind
        {
            // 主键ID，设置为自增且非空
            [SqlSugar.SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
            public int ID { get; set; }                                 // 主键ID

            // 其他列
            public DateTime CreateTime { get; set; } = DateTime.Now;    // 创建时间
            public string CarrierCode { get; set; } = "";               // 载具码
            public string Hydrocooling_Barcode { get; set; } = "";      // 水冷壳体码
            public string TopCover_Barcode { get; set; } = "";          // 上盖板条码
            public string AddPCB_Barcode { get; set; } = "";            // ADD板条码
            public int BindOrder { get; set; } = 0;                     // 绑定顺序
        }

        // 定义TestData实体类，表示表格TestData结构
        public class TestData
        {
            // 主键ID，设置为自增且非空
            [SqlSugar.SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
            public int ID { get; set; }                                 // 主键ID

            // 其他列
            public DateTime CreateTime { get; set; } = DateTime.Now;    // 创建时间
            public string CarrierCode { get; set; } = "";               // 载具码
            public string Hydrocooling_Barcode { get; set; } = "";      // 水冷壳体码
            public string TopCover_Barcode { get; set; } = "";          // 上盖板条码
            public string AddPCB_Barcode { get; set; } = "";            // ADD板条码
            public int BindOrder { get; set; } = 0;                     // 绑定顺序

            //op10
            public int OP10_ThrowingAmount { get; set; } = 0;           // 抛料数量
            public float OP10_CleaningPower { get; set; } = 0.0f;       // 清洗功率
            public float OP10_CleaningPressure { get; set; } = 0.0f;    // 清洗气压
            public float OP10_CleaningSpeed { get; set; } = 0.0f;       // 清洗速度
            public float OP10_AirKnifeHeight { get; set; } = 0.0f;      // 风刀高度
            public float OP10_CleaningTime { get; set; } = 0.0f;        // 清洗时间
            public int OP10_CleaningCount { get; set; } = 0;            // 清洗次数

            //op20
            public int OP20_ThrowingAmount { get; set; } = 0;           // 抛料数量

            //op301
            public float OP301_GluingSpeed { get; set; } = 0.0f;            // 供胶速度
            public float OP301_PressureA { get; set; } = 0.0f;              // A管气压
            public float OP301_PressureB { get; set; } = 0.0f;              // B管气压
            public float OP301_PressureDifferenceAB { get; set; } = 0.0f;   // AB管气压差
            public float OP301_ProductHeightInfo { get; set; } = 0.0f;      // 产品测高信息
            public float OP301_PeriodicWeightData { get; set; } = 0.0f;     // 定期称重数据
            public float OP301_RemainingGlueAmount { get; set; } = 0.0f;    // 剩余胶量

            //op302
            public float OP302_GluingSpeed { get; set; } = 0.0f;            // 供胶速度
            public float OP302_PressureA { get; set; } = 0.0f;              // A管气压
            public float OP302_PressureB { get; set; } = 0.0f;              // B管气压
            public float OP302_PressureDifferenceAB { get; set; } = 0.0f;   // AB管气压差
            public float OP302_ProductHeightInfo { get; set; } = 0.0f;      // 产品测高信息
            public float OP302_PeriodicWeightData { get; set; } = 0.0f;     // 定期称重数据
            public float OP302_RemainingGlueAmount { get; set; } = 0.0f;    // 剩余胶量

            //op40
            public string OP40_GluePosition { get; set; } = "";         // 点胶位置
            public float OP40_GlueLineArea { get; set; } = 0.0f;        // 胶线面积
            public float OP40_GlueLineHeight { get; set; } = 0.0f;      // 胶线高度
            public string OP40_InspectionImagePath { get; set; } = "";  // 检测图片路径

            //op50
            public int OP50_AssemblyStatus { get; set; } = 2;               // 组装是否到位
            public string OP50_ReinspectionImagePath { get; set; } = "";    // 设备复检图片路径
            public int OP50_AddBoardStatus { get; set; } = 2;               // ADD板有无
            public float OP50_AddBoardPressure { get; set; } = 0.0f;        // ADD板压合压力

            //op60
            public int OP60_AssemblyStatus { get; set; } = 2;               // 组装是否到位
            public string OP60_ReinspectionImagePath { get; set; } = "";    // 设备复检图片路径
            public int OP60_TopCoverStatus { get; set; } = 2;               // 上盖板有无
            public float OP60_TopCoverPressure { get; set; } = 0.0f;        // 上盖板压合压力

            //op701
            public string OP701_TorqueCurveDataPath { get; set; } = ""; // 扭力曲线数据表位置
            public float OP701_ScrewPressure { get; set; } = 0.0f;      // 螺丝压力
            public int OP701_Turns { get; set; } = 0;                   // 圈数
            public float OP701_LockTime { get; set; } = 0.0f;           // 锁附时间
            public string OP701_LockOrder { get; set; } = "";           // 锁附顺序
            public string OP701_LockResult { get; set; } = "";          // 锁附结果

            //op702
            public string OP702_TorqueCurveDataPath { get; set; } = ""; // 扭力曲线数据表位置
            public float OP702_ScrewPressure { get; set; } = 0.0f;      // 螺丝压力
            public int OP702_Turns { get; set; } = 0;                   // 圈数
            public float OP702_LockTime { get; set; } = 0.0f;           // 锁附时间
            public string OP702_LockOrder { get; set; } = "";           // 锁附顺序
            public string OP702_LockResult { get; set; } = "";          // 锁附结果
        }
        #endregion

        #endregion
    }
}