数据汇总调整

kingno

2025-04-01 2120c4a86e6b7bfe2b8e27f79c0ebe2efddbc0e0

 src/Bro.M141.Process/M141Process_Mysql.cs

@@ -1,4 +1,5 @@
using Bro.Common.Helper;
using Bro.Common.Interface;
using Bro.M135.Common;
using MySql.Data.MySqlClient;
using Newtonsoft.Json;
@@ -17,8 +18,8 @@
    public class M141Process_Mysql
    {

        string connStrAll = "server=127.0.0.1;port=3306;user id=root;password=123456;database=BroDB;charset=utf8";
        string connStrLocal = "server=127.0.0.1;port=3306;user id=root;password=123456;database=BroDB;charset=utf8";
        string connStrAll = "server=localhost;port=3306;user id=root;password=123456;database=BroDB;charset=utf8";
        string connStrLocal = "server=localhost;port=3306;user id=root;password=123456;database=BroDB;charset=utf8";



@@ -43,7 +44,7 @@
            }
            catch (Exception e)
            {
                CommonLogger.LogAsync(DateTime.Now, EnumHelper.LogLevel.Exception, $"数据库操作异常 {e.ToString()}");
                CommonLogger.LogAsync(DateTime.Now, EnumHelper.LogLevel.Exception, $"数据库操作异常 {sql} {e.ToString()}");
            }
            con.Close();
        }
@@ -64,7 +65,7 @@
            }
            catch (Exception e)
            {
                CommonLogger.LogAsync(DateTime.Now, EnumHelper.LogLevel.Exception, $"数据库查询异常 {e.ToString()}");
                CommonLogger.LogAsync(DateTime.Now, EnumHelper.LogLevel.Exception, $"数据库查询异常 {str} {e.ToString()}");
            }
            con.Close();
            return dt;
@@ -88,7 +89,7 @@
            }
            catch (Exception e)
            {
                CommonLogger.LogAsync(DateTime.Now, EnumHelper.LogLevel.Exception, $"数据库查询数据异常 {e.ToString()}");
                CommonLogger.LogAsync(DateTime.Now, EnumHelper.LogLevel.Exception, $"数据库查询数据异常 {str}  {e.ToString()}");
            }

            con.Close();
@@ -103,7 +104,18 @@
        {
            try
            {
                string mysqlstr = $"insert into forlocal (pid,plcnum,sn,data,time) values('{pro.PID}','{pro.SEQUENCE}','{pro.SN}','{JsonConvert.SerializeObject(pro)}','{DateTime.Now.ToString("yyyyMMddHHmmss")}')";
                //保留数据库最新的1000条数据
                //string destr = "delete from forlocal where id not in (select id from (select id from forlocal order BY id desc limit 1000) as subquery)";
                //Operatoremysql(destr, connStrAll);

                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);


                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);
            }
@@ -128,32 +140,46 @@

        public void NewForAll(ProductModel pro, string devicestr)
        {
            try
            Task.Run(() =>
            {
                string mysqlstr = $"insert into forall (device,plcnum,sn,data,time) values('{devicestr}','{pro.SEQUENCE}','{pro.SN}','{JsonConvert.SerializeObject(pro)}','{DateTime.Now.ToString("yyyyMMddHHmmss")}')";
                try
                {

                Operatoremysql(mysqlstr, connStrAll);
            }
            catch (Exception e)
            {
                CommonLogger.LogAsync(DateTime.Now, EnumHelper.LogLevel.Exception, $"数据库forall插入数据异常 {e.ToString()}");
            }
                    //保留数据库最新的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);

                    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)}','{DateTime.Now.ToString("yyyyMMddHHmmss")}')";

                    Operatoremysql(mysqlstr, connStrAll);
                }
                catch (Exception e)
                {
                    CommonLogger.LogAsync(DateTime.Now, EnumHelper.LogLevel.Exception, $"数据库forall插入数据异常 {e.ToString()}");
                }
            });
        }


        public ProductModel GetProduct(string plcnum)
        {
            try
            {
                string mysqlstr = $"select data from forlocal where plcnum='{plcnum}'";
                string str= selectmysql(mysqlstr, connStrLocal);
                string mysqlstr = $"select data from forlocal where plcnum='{plcnum}' order by id desc limit 1";
                string str = selectmysql(mysqlstr, connStrLocal);
                if (string.IsNullOrEmpty(str))
                {
                    return null;
                }
                else
                {
                    ProductModel p = JsonConvert.DeserializeObject<ProductModel>(str);
                    ProductModel p = JsonConvert.DeserializeObject<ProductModel>(str, new JsonSerializerSettings() { TypeNameHandling = TypeNameHandling.All });
                    return p;
                }
            }
@@ -164,6 +190,54 @@
            return null;
        }

        public string Getbasketcode(string plcnum)
        {
            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))
                {
                    return str;
                }
            }
            catch (Exception e)
            {
                CommonLogger.LogAsync(DateTime.Now, EnumHelper.LogLevel.Exception, $"数据库forlocal更新数据异常 {e.ToString()}");
            }
            return "NoRead";
        }


        public List<ProductModel> GetProductList(string plcnum)
        {
            try
            {
                string mysqlstr = $"select data from forall where plcnum='{plcnum}'";
                var dt = selectdt(mysqlstr, connStrAll);
                if (dt == null)
                {
                    return null;
                }
                else
                {
                    List<ProductModel> plist = new List<ProductModel>();
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        ProductModel p = JsonConvert.DeserializeObject<ProductModel>(dt.Rows[i][0].ToString(), new JsonSerializerSettings() { TypeNameHandling = TypeNameHandling.All });

                        //JsonConvert.DeserializeObject<IProcessConfig>(_configBackupStr, new JsonSerializerSettings() { TypeNameHandling = TypeNameHandling.All });
                        plist.Add(p);
                    }
                    return plist;
                }
            }
            catch (Exception e)
            {
                CommonLogger.LogAsync(DateTime.Now, EnumHelper.LogLevel.Exception, $"数据库forall数据异常 {e.ToString()}");
            }
            return null;
        }


    }