优化数据传递

kingno

2025-06-09 305e2990bb2161177e87ab7711f9dfb205762bcd

 src/Bro.M141.Process/M141Process_Mysql.cs

@@ -1,5 +1,6 @@
using Bro.Common.Helper;
using Bro.Common.Interface;
using Bro.Common.Model;
using Bro.M135.Common;
using MySql.Data.MySqlClient;
using Newtonsoft.Json;
@@ -11,6 +12,8 @@
using System.Text;
using System.Threading.Tasks;
using Windows.Globalization.PhoneNumberFormatting;
using Windows.Media.Ocr;
using static Org.BouncyCastle.Math.EC.ECCurve;
using static System.Windows.Forms.VisualStyles.VisualStyleElement.ListView;

namespace Bro.M141.Process
@@ -22,14 +25,13 @@
        string connStrLocal = "server=localhost;port=3306;user id=root;password=123456;database=BroDB;charset=utf8";



        #region 基础操作
        public void IniDBIP(string IP)
        {
            connStrAll = $"server={IP};port=3306;user id=root;password=123456;database=BroDB;charset=utf8";
        }

        public void Operatoremysql(string sql, string connStr)//操作
        public bool Operatoremysql(string sql, string connStr)//操作
        {
            MySqlConnection con = new MySqlConnection(connStr);
            try
@@ -41,12 +43,16 @@
                con.Open();
                MySqlCommand coon = new MySqlCommand(sql, con);
                coon.ExecuteNonQuery();
                con.Close();
            }
            catch (Exception e)
            {
                CommonLogger.LogAsync(DateTime.Now, EnumHelper.LogLevel.Exception, $"数据库操作异常 {sql} {e.ToString()}");
                con.Close();
                return false;
                //CommonLogger.LogAsync(DateTime.Now, EnumHelper.LogLevel.Exception, $"数据库操作异常 {sql} {e.ToString()}");
            }
            con.Close();
            return true;

        }

        public DataTable selectdt(string str, string connStr)//查询表格
@@ -62,12 +68,14 @@
                con.Open();
                MySqlDataAdapter da = new MySqlDataAdapter(str, con);
                da.Fill(dt);
                con.Close();
            }
            catch (Exception e)
            {
                CommonLogger.LogAsync(DateTime.Now, EnumHelper.LogLevel.Exception, $"数据库查询异常 {str} {e.ToString()}");
                //CommonLogger.LogAsync(DateTime.Now, EnumHelper.LogLevel.Exception, $"数据库查询异常 {str} {e.ToString()}");
                con.Close();
                return null;
            }
            con.Close();
            return dt;
        }

@@ -89,9 +97,8 @@
            }
            catch (Exception e)
            {
                CommonLogger.LogAsync(DateTime.Now, EnumHelper.LogLevel.Exception, $"数据库查询数据异常 {str}  {e.ToString()}");
                //CommonLogger.LogAsync(DateTime.Now, EnumHelper.LogLevel.Exception, $"数据库查询数据异常 {str}  {e.ToString()}");
            }

            con.Close();
            return strr;
        }
@@ -111,13 +118,22 @@
                string str1 = "select id from forlocal  order by id desc limit 1";
                string index = selectmysql(str1, connStrLocal);

                string str2 = $"delete from forlocal where id < ({index}-1000)";
                Operatoremysql(str2, connStrLocal);
                if (!string.IsNullOrEmpty(index))
                {
                    string str2 = $"delete from forlocal where id < ({index}-1000)";
                    Operatoremysql(str2, connStrLocal);
                }

                string mysqlstr = $"insert into forlocal (basketcode,pid,plcnum,sn,data,time) values('{pro.BasketCode}','{pro.PID}','{pro.SEQUENCE}','{pro.SN}','{JsonConvert.SerializeObject(pro, new JsonSerializerSettings() { TypeNameHandling = TypeNameHandling.All })}','{DateTime.Now.ToString("yyyyMMddHHmmss")}')";

                string mysqlstr = $"insert into forlocal (basketcode,pid,plcnum,sn,data,time) values('{pro.BasketCode}','{pro.PID}','{pro.SEQUENCE}','{pro.SN}','{JsonConvert.SerializeObject(pro)}','{DateTime.Now.ToString("yyyyMMddHHmmss")}')";

                Operatoremysql(mysqlstr, connStrLocal);
                if (Operatoremysql(mysqlstr, connStrLocal))
                {
                    CommonLogger.LogAsync(DateTime.Now, EnumHelper.LogLevel.Action, $"数据库forlocal插入数据成功");
                }
                else
                {
                    CommonLogger.LogAsync(DateTime.Now, EnumHelper.LogLevel.Error, $"数据库forlocal插入数据异失败  {mysqlstr}");
                }
            }
            catch (Exception e)
            {
@@ -129,8 +145,40 @@
        {
            try
            {
                string mysqlstr = $"update forlocal set data='{JsonConvert.SerializeObject(pro)}' where plcnum='{pro.SEQUENCE}'";
                Operatoremysql(mysqlstr, connStrLocal);
                if (pro.Details != null)
                {
                    foreach (var item in pro.Details)
                    {
                        if (item != null)
                        {
                            var defects = item.ResultList.GetDefectDescList();
                            var ngSpecCodes = item.SpecList.Where(u => u.MeasureResult != true).Select(u => u.Code);
                            defects.AddRange(ngSpecCodes);
                            item.DefectList.AddRange(defects.Distinct());
                        }
                    }
                }

                JsonSerializerSettings settings = new JsonSerializerSettings
                {
                    TypeNameHandling = TypeNameHandling.All
                };

                string jsondata = JsonConvert.SerializeObject(pro, settings);


                //CommonLogger.LogAsync(DateTime.Now, EnumHelper.LogLevel.Exception, $"{jsondata}");

                string mysqlstr = $"update forlocal set data='{jsondata}',pid='{pro.PID}' where plcnum='{pro.SEQUENCE}'";

                if (Operatoremysql(mysqlstr, connStrLocal))
                {
                    CommonLogger.LogAsync(DateTime.Now, EnumHelper.LogLevel.Action, $"数据库forlocal更新数据成功");
                }
                else
                {
                    CommonLogger.LogAsync(DateTime.Now, EnumHelper.LogLevel.Error, $"数据库forlocal更新数据失败 {mysqlstr}");
                }
            }
            catch (Exception e)
            {
@@ -144,21 +192,64 @@
            {
                try
                {

                    //保留数据库最新的1000条数据
                    //string destr = "delete from forall where id not in (select id from (select id from forall order BY id desc limit 1000) as subquery)";
                    //Operatoremysql(destr, connStrAll);

                    string str1 = "select id from forall  order by id desc limit 1";
                    string index = selectmysql(str1, connStrAll);
                    if (pro.Details != null)
                    {
                        foreach (var item in pro.Details)
                        {
                            if (item != null)
                            {
                                var defects = item.ResultList.GetDefectDescList();
                                var ngSpecCodes = item.SpecList.Where(u => u.MeasureResult != true).Select(u => u.Code);
                                defects.AddRange(ngSpecCodes);
                                item.DefectList.AddRange(defects.Distinct());
                                item.DefectList = item.DefectList.Distinct().ToList();
                            }
                        }
                    }

                    string str2 = $"delete from forall where id < ({index}-1000)";
                    Operatoremysql(str2, connStrAll);
                    string strtem = "select * from forall  where plcnum='" + pro.SEQUENCE + "' and device='" + devicestr + "' ";

                    var temdb = selectdt(strtem, connStrAll);
                    if (temdb != null && temdb.Rows.Count > 0)
                    {
                        string mysqlstr = $"update forall set data='" + JsonConvert.SerializeObject(pro, new JsonSerializerSettings() { TypeNameHandling = TypeNameHandling.All }) + "' where plcnum='" + pro.SEQUENCE + "' and device='" + devicestr + "' ";

                    string mysqlstr = $"insert into forall (basketcode,device,plcnum,sn,data,time) values('{pro.BasketCode}','{devicestr}','{pro.SEQUENCE}','{pro.SN}','{JsonConvert.SerializeObject(pro)}','{DateTime.Now.ToString("yyyyMMddHHmmss")}')";
                        if (Operatoremysql(mysqlstr, connStrAll))
                        {
                            CommonLogger.LogAsync(DateTime.Now, EnumHelper.LogLevel.Action, $"数据库forall更新数据成功");
                        }
                        else
                        {
                            CommonLogger.LogAsync(DateTime.Now, EnumHelper.LogLevel.Error, $"数据库forall更新数据异常  {mysqlstr}");
                        }
                    }
                    else
                    {
                        string str1 = "select id from forall  order by id desc limit 1";
                        string index = selectmysql(str1, connStrAll);

                    Operatoremysql(mysqlstr, connStrAll);
                        if (!string.IsNullOrEmpty(index))
                        {
                            string str2 = $"delete from forall where id < ({index}-1000)";
                            Operatoremysql(str2, connStrAll);
                        }

                        string mysqlstr = $"insert into forall (basketcode,device,plcnum,sn,data,time) values('{pro.BasketCode}','{devicestr}','{pro.SEQUENCE}','{pro.SN}','{JsonConvert.SerializeObject(pro, new JsonSerializerSettings() { TypeNameHandling = TypeNameHandling.All })}','{DateTime.Now.ToString("yyyyMMddHHmmss")}')";

                        if (Operatoremysql(mysqlstr, connStrAll))
                        {
                            CommonLogger.LogAsync(DateTime.Now, EnumHelper.LogLevel.Action, $"数据库forall插入数据成功");
                        }
                        else
                        {
                            CommonLogger.LogAsync(DateTime.Now, EnumHelper.LogLevel.Error, $"数据库forall插入数据异失败  {mysqlstr}");
                        }
                    }

                }
                catch (Exception e)
                {
@@ -179,31 +270,38 @@
                }
                else
                {
                    ProductModel p = JsonConvert.DeserializeObject<ProductModel>(str, new JsonSerializerSettings() { TypeNameHandling = TypeNameHandling.All });
                    ProductModel p = null;
                    p = JsonConvert.DeserializeObject<ProductModel>(str, new JsonSerializerSettings() { TypeNameHandling = TypeNameHandling.All });
                    return p;
                }
            }
            catch (Exception e)
            {
                CommonLogger.LogAsync(DateTime.Now, EnumHelper.LogLevel.Exception, $"数据库forlocal更新数据异常 {e.ToString()}");
                CommonLogger.LogAsync(DateTime.Now, EnumHelper.LogLevel.Exception, $"数据库forlocal获取对象异常 {e.ToString()}");
            }
            return null;
        }

        public string Getbasketcode(string plcnum)
        public string Getbasketcode(string plcnum, out string sn)
        {
            sn = "Noread";
            try
            {
                string mysqlstr = $"select basketcode from forall where plcnum='{plcnum}' && device='S2' order by id desc limit 1";
                string str = selectmysql(mysqlstr, connStrAll);
                if (!string.IsNullOrEmpty(str))
                string mysqlstr = $"select basketcode,sn from forall where plcnum='{plcnum}' && device='S2' order by id desc limit 1";
                var dt = selectdt(mysqlstr, connStrAll);
                if (dt == null || dt.Rows.Count == 0)
                {
                    return str;
                    return "Noread";
                }
                else
                {
                    sn = dt.Rows[0][1].ToString();
                    return dt.Rows[0][0].ToString();
                }
            }
            catch (Exception e)
            {
                CommonLogger.LogAsync(DateTime.Now, EnumHelper.LogLevel.Exception, $"数据库forlocal更新数据异常 {e.ToString()}");

            }
            return "NoRead";
        }
@@ -213,7 +311,7 @@
        {
            try
            {
                string mysqlstr = $"select data from forall where plcnum='{plcnum}'";
                string mysqlstr = $"select data from forall where plcnum='{plcnum}' order by device";
                var dt = selectdt(mysqlstr, connStrAll);
                if (dt == null)
                {