using Bro.Common.Helper; using Bro.Common.Interface; using Bro.Common.Model; using Bro.M135.Common; using MySql.Data.MySqlClient; using Newtonsoft.Json; using Sunny.UI; using System; using System.Collections.Generic; using System.Data; using System.Linq; using System.Text; using System.Threading.Tasks; using Windows.Globalization.PhoneNumberFormatting; using static Org.BouncyCastle.Math.EC.ECCurve; using static System.Windows.Forms.VisualStyles.VisualStyleElement.ListView; namespace Bro.M141.Process { public class M141Process_Mysql { 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"; #region 基础操作 public void IniDBIP(string IP) { connStrAll = $"server={IP};port=3306;user id=root;password=123456;database=BroDB;charset=utf8"; } public bool Operatoremysql(string sql, string connStr)//操作 { MySqlConnection con = new MySqlConnection(connStr); try { if (con.State == ConnectionState.Open) { con.Close(); } con.Open(); MySqlCommand coon = new MySqlCommand(sql, con); coon.ExecuteNonQuery(); con.Close(); } catch (Exception e) { con.Close(); return false; //CommonLogger.LogAsync(DateTime.Now, EnumHelper.LogLevel.Exception, $"数据库操作异常 {sql} {e.ToString()}"); } return true; } public DataTable selectdt(string str, string connStr)//查询表格 { MySqlConnection con = new MySqlConnection(connStr); DataTable dt = new DataTable(); try { if (con.State == ConnectionState.Open) { con.Close(); } 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()}"); con.Close(); return null; } return dt; } public static string selectmysql(string str, string connStr)//查询 { MySqlConnection con = new MySqlConnection(connStr); MySqlCommand sqlCmd = new MySqlCommand(); sqlCmd.Connection = con; sqlCmd.CommandText = str; string strr = ""; try { if (con.State == ConnectionState.Open) { con.Close(); } con.Open(); strr = sqlCmd.ExecuteScalar().ToString(); } catch (Exception e) { //CommonLogger.LogAsync(DateTime.Now, EnumHelper.LogLevel.Exception, $"数据库查询数据异常 {str} {e.ToString()}"); } con.Close(); return strr; } #endregion public void NewProduct(ProductModel pro) { try { //保留数据库最新的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); 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")}')"; 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) { CommonLogger.LogAsync(DateTime.Now, EnumHelper.LogLevel.Exception, $"数据库forlocal插入数据异常 {e.ToString()}"); } } public void UpdateProduct(ProductModel pro) { try { if (pro.Details!=null) { foreach (var item in pro.Details) { if (item.ResultList!=null) { item.ResultList.ForEach(u => { if (u.NetResults != null) { u.NetResults.ForEach(x => { if (x.DetectDetails != null) { var distinctItems = x.DetectDetails.GroupBy(i => new { i.ClassName, i.FinalResult }) // 根据Id和Name去重 .Select(g => g.First()) // 选择每个组的第一个元素 .ToList(); x.DetectDetails = distinctItems; } }); } }); } } } 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}' 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) { CommonLogger.LogAsync(DateTime.Now, EnumHelper.LogLevel.Exception, $"数据库forlocal更新数据异常 {e.ToString()}"); } } public void NewForAll(ProductModel pro, string devicestr) { Task.Run(() => { 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 (!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) { 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}' order by id desc limit 1"; string str = selectmysql(mysqlstr, connStrLocal); if (string.IsNullOrEmpty(str)) { return null; } else { ProductModel p = null; p = JsonConvert.DeserializeObject(str, new JsonSerializerSettings() { TypeNameHandling = TypeNameHandling.All }); return p; } } catch (Exception e) { CommonLogger.LogAsync(DateTime.Now, EnumHelper.LogLevel.Exception, $"数据库forlocal获取对象异常 {e.ToString()}"); } 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 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 plist = new List(); for (int i = 0; i < dt.Rows.Count; i++) { ProductModel p = JsonConvert.DeserializeObject(dt.Rows[i][0].ToString(), new JsonSerializerSettings() { TypeNameHandling = TypeNameHandling.All }); //JsonConvert.DeserializeObject(_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; } } }