using Bro.Common.Helper; using Bro.Common.Interface; using Bro.Common.Model; using Bro.M135.Common; using HalconDotNet; 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 Windows.Media.Ocr; using static Bro.Common.Helper.EnumHelper; using static NPOI.HSSF.Util.HSSFColor; 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 != 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) { CommonLogger.LogAsync(DateTime.Now, EnumHelper.LogLevel.Exception, $"数据库forlocal更新数据异常 {e.ToString()}"); } } public void NewForAll(ProductModel pro, string devicestr, string netname) { 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); if (string.IsNullOrEmpty(netname) /*|| devicestr != "S3" */) { 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(); } } } } else { List allnetdetail = new List(); 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(); var defecttem = item.ResultList.SelectMany(u => u.AllNetResults.SelectMany(m => m.DetectDetails)).Where(u => u.ClassName == netname).ToList(); //CommonLogger.LogAsync(DateTime.Now, EnumHelper.LogLevel.Exception, $"{netname}个数{defecttem.Count}"); int num = 0; foreach (var item1 in defecttem) { double x1 = item1.Rect.Point_LU.X + item1.Rect.Width / 2.0; double y1 = item1.Rect.Point_LU.Y + item1.Rect.Height / 2.0; num++; //HOperatorSet.AffineTransPoint2d(new HTuple(pro.Centermatrix.ToArray()), x1, y1, out HTuple qx, out HTuple qy); CommonLogger.LogAsync(DateTime.Now, EnumHelper.LogLevel.Detail, $"sql {pro.SN}原坐标{num} {x1},{y1}"); HOperatorSet.ProjectiveTransPixel(new HTuple(pro.Centermatrix.ToArray()), x1, y1, out HTuple qx, out HTuple qy); CommonLogger.LogAsync(DateTime.Now, EnumHelper.LogLevel.Detail, $"sql {pro.SN}新坐标{num} {qx},{qy}"); Netdefectdetail temc = new Netdefectdetail() { centerX = qx, centerY = qy, name = netname, }; allnetdetail.Add(temc); } } } pro.Netdefectdetails = allnetdetail; } } 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 + "' "; 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); 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 List GetNetdefectdetails(string plcnum) { try { string mysqlstr = $"select data from forall where plcnum='{plcnum}' && device='S3' order by id desc limit 1"; string str = selectmysql(mysqlstr, connStrAll); if (string.IsNullOrEmpty(str)) { return null; } else { ProductModel p = null; p = JsonConvert.DeserializeObject(str, new JsonSerializerSettings() { TypeNameHandling = TypeNameHandling.All }); return p.Netdefectdetails; } } catch (Exception e) { CommonLogger.LogAsync(DateTime.Now, EnumHelper.LogLevel.Exception, $"数据库forlocal获取对象异常 {e.ToString()}"); } return null; } public bool GetS2Result(string plcnum) { try { string mysqlstr = $"select data from forall where plcnum='{plcnum}' && device='S2' order by id desc limit 1"; string str = selectmysql(mysqlstr, connStrAll); if (string.IsNullOrEmpty(str)) { return false; } else { ProductModel p = null; p = JsonConvert.DeserializeObject(str, new JsonSerializerSettings() { TypeNameHandling = TypeNameHandling.All }); return p.Result=="OK"; } } catch (Exception e) { CommonLogger.LogAsync(DateTime.Now, EnumHelper.LogLevel.Exception, $"数据库forlocal获取对象异常 {e.ToString()}"); } return false; } public string Getbasketcode(string plcnum, out string sn) { sn = "Noread"; try { 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 "Noread"; } else { sn = dt.Rows[0][1].ToString(); return dt.Rows[0][0].ToString(); } } catch (Exception e) { } return "NoRead"; } public List GetProductList(string plcnum) { try { string mysqlstr = $"select data from forall where plcnum='{plcnum}' order by device"; var dt = selectdt(mysqlstr, connStrAll); if (dt == null) { return null; } else { List plist = new List(); for (int i = 0; i < dt.Rows.Count; i++) { string strjson = dt.Rows[i][0].ToString(); strjson = strjson.Replace(@"\", @"\\"); ProductModel p = JsonConvert.DeserializeObject(strjson, 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; } public DataTable getBasketCode() { try { string mysqlstr = $"select id,code,time from basketcode order by id desc limit 200"; var dt = selectdt(mysqlstr, connStrAll); if (dt == null) { return null; } else { return dt; } } catch (Exception e) { CommonLogger.LogAsync(DateTime.Now, EnumHelper.LogLevel.Exception, $"数据库forall数据异常 {e.ToString()}"); } return null; } public void NewBasketcode(string code) { try { string mysqlstr = $"insert into basketcode(code,time) values('{code}','{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}')"; if (Operatoremysql(mysqlstr, connStrAll)) { CommonLogger.LogAsync(DateTime.Now, EnumHelper.LogLevel.Action, $"数据库basketcode插入数据成功"); } else { CommonLogger.LogAsync(DateTime.Now, EnumHelper.LogLevel.Error, $"数据库basketcode插入数据异失败 {mysqlstr}"); } } catch (Exception e) { CommonLogger.LogAsync(DateTime.Now, EnumHelper.LogLevel.Exception, $"数据库basketcode插入数据异常 {e.ToString()}"); } } } }