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<Netdefectdetail> allnetdetail = new List<Netdefectdetail>();
|
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<ProductModel>(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<Netdefectdetail> 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<ProductModel>(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 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<ProductModel> 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<ProductModel> plist = new List<ProductModel>();
|
for (int i = 0; i < dt.Rows.Count; i++)
|
{
|
string strjson = dt.Rows[i][0].ToString();
|
strjson = strjson.Replace(@"\", @"\\");
|
ProductModel p = JsonConvert.DeserializeObject<ProductModel>(strjson, 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;
|
}
|
|
|
|
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()}");
|
}
|
}
|
|
}
|
}
|
