123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385 |
- using CommonStorage.Model;
- using MySql.Data.MySqlClient;
- using Newtonsoft.Json;
- using System;
- using System.Collections.Generic;
- using System.Data;
- using System.IO;
- using System.Linq;
- using System.Runtime.ConstrainedExecution;
- using System.Runtime.InteropServices.ComTypes;
- using System.Runtime.Remoting.Metadata.W3cXsd2001;
- using System.Text;
- using static Google.Protobuf.Reflection.FieldOptions.Types;
- namespace CommonStorage
- {
- public class DBHelper
- {
- static string connectionString = "server=localhost;port=3306;database=jf200;uid=mysqltest;password=test1234";
- public static bool InsertIntoLog(string msgType, Model.LevelEnum msgLevel, string msg)
- {
- using (MySqlConnection connection = new MySqlConnection(connectionString))
- {
- connection.Open();
- // MySqlTransaction transaction = connection.BeginTransaction();
- try
- {
- using (var context = new MyDb(connection, false))
- {
- // Passing an existing transaction to the context
- // context.Database.UseTransaction(transaction);
- context.logs.Add(new Model.Log() { MsgType = msgType, MsgLevel = msgLevel, Message = msg, CreateTime = DateTime.Now });
- context.SaveChanges();
- }
- // transaction.Commit();
- }
- catch(Exception ex)
- {
- throw ex;
- }
- }
- return true;
- }//end InsertIntoLog
- public static User QueryUser(string userName)
- {
- using (MySqlConnection connection = new MySqlConnection(connectionString))
- {
- connection.Open();
- try
- {
- using (var context = new MyDb(connection, false))
- {
- var user = context.users.FirstOrDefault(x => x.UserName == userName);
- return user;
- }
- }
- catch (Exception ex)
- {
- throw ex;
- }
- }
- }
- /// <summary>
- /// 查询配置的测试项目
- /// </summary>
- /// <param name="ProductCode">产品代号</param>
- /// <returns></returns>
- public static List<List<ValueItem>> QueryTestPar(string ProductCode)
- {
- List<List<ValueItem>> TpsDatas = new List<List<ValueItem>>();
- using (MySqlConnection connection = new MySqlConnection(connectionString))
- {
- connection.Open();
- try
- {
- using (var context = new MyDb(connection, false))
- {
- var config = context.tpsConfigs.FirstOrDefault(x => x.ProductCode == ProductCode);
- if(string.IsNullOrEmpty(config.Values))
- {
- TpsDatas = JsonConvert.DeserializeObject<List<List<ValueItem>>>(config.Values);
- }
- }
- }
- catch (Exception ex)
- {
- throw ex;
- }
- }
- return TpsDatas;
- }
- /// <summary>
- /// 新增或者更新产品测试配置数据
- /// </summary>
- /// <param name="ProductCode"></param>
- /// <returns></returns>
- public static bool AddOrUpdateTestPar(string ProductCode, List<List<ValueItem>> dataItems)
- {
- using (MySqlConnection connection = new MySqlConnection(connectionString))
- {
- connection.Open();
- try
- {
- using (var context = new MyDb(connection, false))
- {
- var config = context.tpsConfigs.FirstOrDefault(x => x.ProductCode == ProductCode);
- if(config == null)
- {
- TpsConfig tpsConfig = new TpsConfig();
- tpsConfig.ProductCode = ProductCode;
- tpsConfig.TpsName = "JF200测试程序";
- tpsConfig.TestItem = "空载-负载-短路";
- tpsConfig.TestProject = "常温测试";
- if(dataItems != null)
- {
- tpsConfig.Values = JsonConvert.SerializeObject(dataItems);
- }
- context.tpsConfigs.Add(tpsConfig);
- }
- else
- {
- config.Values = "";
- if (dataItems != null)
- {
- config.Values = JsonConvert.SerializeObject(dataItems);
- }
- }
- context.SaveChanges();
- }
- }
- catch (Exception ex)
- {
- throw ex;
- }
- }
- return true;
- }
- //删除产品测试配置项
- public static bool DeleteTestPar(string ProductCode)
- {
- using (MySqlConnection connection = new MySqlConnection(connectionString))
- {
- connection.Open();
- try
- {
- using (var context = new MyDb(connection, false))
- {
- var config = context.tpsConfigs.FirstOrDefault(x => x.ProductCode == ProductCode);
- if (config != null)
- {
- context.tpsConfigs.Remove(config);
- }
-
- context.SaveChanges();
- }
- }
- catch (Exception ex)
- {
- throw ex;
- }
- }
- return true;
- }
- /// <summary>
- /// 根据筛选信息查询测试数据,不含Values,Values是表内容,以加快查询返回
- /// </summary>
- /// <param name="Serial">产品编号</param>
- /// <param name="ProductCode">产品代号</param>
- /// <param name="BarCode">产品批次</param>
- /// <param name="startTime">开始时间</param>
- /// <param name="stopTime">结束数据</param>
- /// <returns></returns>
- public static List<TestDataBase> QueryTestData(string Serial, string ProductCode, string BarCode, DateTime startTime, DateTime stopTime)
- {
- List<TestDataBase> datas = new List<TestDataBase>();
- using (MySqlConnection connection = new MySqlConnection(connectionString))
- {
- connection.Open();
- try
- {
- using (var context = new MyDb(connection, false))
- {
- //不含Values,Values是表内容,以加快查询返回
- //var query = from x in context.testDatas
- // where ((string.IsNullOrEmpty(Serial) || x.Serial.Contains(Serial)) &&
- // (string.IsNullOrEmpty(ProductCode) || x.ProductCode.Equals(ProductCode)) &&
- // (string.IsNullOrEmpty(BarCode) || x.BarCode.Equals(BarCode)) &&
- // x.TestTime >= startTime &&
- // x.TestTime <= stopTime)
- // select new TestDataBase
- // {
- // ID = x.ID,
- // BarCode = x.BarCode,
- // DataName = x.DataName,
- // TestTime = x.TestTime,
- // ProductCode = x.ProductCode,
- // ProductName = x.ProductName,
- // ProductNum = x.ProductNum,
- // Serial = x.Serial,
- // IsPass = x.IsPass,
- // Tester = x.Tester,
- // TestProject = x.TestProject
- // };
- var query = from x in context.testDatas
- where ((string.IsNullOrEmpty(Serial) || x.Serial.Contains(Serial)) &&
- (x.ProductCode.Equals(ProductCode)) &&
- (string.IsNullOrEmpty(BarCode) || x.BarCode.Equals(BarCode)) &&
- x.TestTime >= startTime &&
- x.TestTime <= stopTime)
- select new TestDataBase
- {
- ID = x.ID,
- BarCode = x.BarCode,
- DataName = x.DataName,
- TestTime = x.TestTime,
- ProductCode = x.ProductCode,
- ProductName = x.ProductName,
- ProductNum = x.ProductNum,
- Serial = x.Serial,
- IsPass = x.IsPass,
- Tester = x.Tester,
- TestProject = x.TestProject
- };
- datas = query.ToList();
- }
- }
- catch (Exception ex)
- {
- throw ex;
- }
- }
- return datas;
- }
- /// <summary>
- /// 查询测试数据,ID是QueryTestData查出来的数据ID
- /// 用于数据查询,带Values
- /// </summary>
- /// <param name="ID">数据ID</param>
- /// <returns></returns>
- public static TestData QueryTestData(long ID)
- {
- using (MySqlConnection connection = new MySqlConnection(connectionString))
- {
- connection.Open();
- try
- {
- using (var context = new MyDb(connection, false))
- {
- var query = context.testDatas.FirstOrDefault(x => x.ID == ID);
- return query;
- }
- }
- catch (Exception ex)
- {
- throw ex;
- }
- }
- return null;
- }
- /// <summary>
- /// 根据ID删除指定的测试数据
- /// </summary>
- /// <param name="ID"></param>
- /// <returns></returns>
- public static bool DeleteTestData(long ID)
- {
- List<long> ids = new List<long>();
- ids.Add(ID);
- return DeleteTestDatas(ids);
- }
- /// <summary>
- /// 根据ID删除指定的测试数据,可以一次删除多条
- /// </summary>
- /// <param name="ids"></param>
- public static bool DeleteTestDatas(List<long> ids)
- {
- if(ids == null)
- return false;
- using (MySqlConnection connection = new MySqlConnection(connectionString))
- {
- connection.Open();
- try
- {
- using (var context = new MyDb(connection, false))
- {
- foreach (var id in ids)
- {
- var query = context.testDatas.FirstOrDefault(x => x.ID == id);
- if(query != null)
- {
- context.testDatas.Remove(query);
- }
- }
- context.SaveChanges();
- }
- return true;
- }
- catch (Exception ex)
- {
- throw ex;
- }
- }
- }
- /// <summary>
- /// 添加测试项
- /// </summary>
- /// <param name="testData"></param>
- /// <returns></returns>
- public static bool AddTestData(TestData testData)
- {
- using (MySqlConnection connection = new MySqlConnection(connectionString))
- {
- connection.Open();
- try
- {
- using (var context = new MyDb(connection, false))
- {
- context.testDatas.Add(testData);
- context.SaveChanges();
- return true;
- }
- }
- catch (Exception ex)
- {
- throw ex;
- }
- }
- return true;
- }
- /// <summary>
- /// 查询日志
- /// </summary>
- /// <param name="workstation">工位号</param>
- /// <param name="startTime">开始时间</param>
- /// <param name="stopTime">截止时间</param>
- /// <returns></returns>
- public static List<Log> QueryLog(string workstation, DateTime startTime, DateTime stopTime)
- {
- List<Log> logItems = new List<Log>();
- using (MySqlConnection connection = new MySqlConnection(connectionString))
- {
- connection.Open();
- try
- {
- using (var context = new MyDb(connection, false))
- {
- //不含Values,Values是表内容,以加快查询返回
- var query = from x in context.logs
- where ((string.IsNullOrEmpty(workstation) || x.Workstation.Equals(workstation)) &&
- x.CreateTime >= startTime &&
- x.CreateTime <= stopTime) select x;
- logItems = query.ToList();
- }
- }
- catch (Exception ex)
- {
- throw ex;
- }
- }
- return logItems;
- }
- }//class DBHelper
- }
|