using LB_SmartVisionCommon;
|
using MySql.Data.MySqlClient;
|
using System;
|
using System.Collections.Generic;
|
using System.Diagnostics;
|
using System.Linq;
|
using System.Text;
|
using System.Threading.Tasks;
|
|
namespace LB_SmartVision.SQL
|
{
|
/// <summary>
|
/// 数据访问层
|
/// </summary>
|
public class RecordProductDataRepository
|
{
|
private static object recordProductDataRepositoryObject = new object();
|
/// <summary>
|
/// 添加数据
|
/// </summary>
|
/// <param name="record">生产数据记录</param>
|
/// <returns>是否添加成功</returns>
|
public static bool AddRecord(RecordProductData record)
|
{
|
Stopwatch stopwatch = Stopwatch.StartNew();
|
lock (recordProductDataRepositoryObject)
|
{
|
stopwatch.Start();
|
try
|
{
|
using (var connection = DatabaseRecordProductDataHelper.GetConnection())
|
{
|
connection.Open();
|
string sql = @"
|
INSERT INTO RecordProductData
|
(ProductName, ProductSN, InspectionOperator, NGType, NGSize,DetectionTime,CameraInspection)
|
VALUES
|
(@ProductName, @ProductSN, @InspectionOperator, @NGType, @NGSize, @DetectionTime, @CameraInspection)";
|
using (var cmd = new MySqlCommand(sql, connection))
|
{
|
cmd.Parameters.AddWithValue("@ProductName", record.ProductName);
|
cmd.Parameters.AddWithValue("@ProductSN", record.ProductSN);
|
cmd.Parameters.AddWithValue("@InspectionOperator", record.InspectionOperator);
|
cmd.Parameters.AddWithValue("@NGType", record.NGType);
|
cmd.Parameters.AddWithValue("@NGSize", record.NGSize);
|
cmd.Parameters.AddWithValue("@DetectionTime", record.DetectionTime);
|
cmd.Parameters.AddWithValue("@CameraInspection", record.CameraInspection);
|
int rowsAffected = cmd.ExecuteNonQuery();
|
stopwatch.Stop();
|
Task.Factory.StartNew(() =>
|
{
|
LogHelper.Info($"添加信息耗时: {stopwatch.ElapsedMilliseconds}ms");
|
});
|
return rowsAffected > 0;
|
}
|
}
|
}
|
catch (Exception ex)
|
{
|
AsyncLogHelper.Error($"添加记录失败: {ex.Message}");
|
return false;
|
}
|
}
|
}
|
/// <summary>
|
/// 查询所有数据
|
/// </summary>
|
/// <returns>List<RecordProductData></returns>
|
public static List<RecordProductData> GetAllRecords()
|
{
|
lock (recordProductDataRepositoryObject)
|
{
|
var records = new List<RecordProductData>();
|
try
|
{
|
using (var connection = DatabaseRecordProductDataHelper.GetConnection())
|
{
|
connection.Open();
|
string sql = "SELECT * FROM RecordProductData ORDER BY CreatedDate DESC";
|
using (var cmd = new MySqlCommand(sql, connection))
|
using (var reader = cmd.ExecuteReader())
|
{
|
while (reader.Read())
|
{
|
records.Add(MapReaderToRecord(reader));
|
}
|
}
|
}
|
}
|
catch (Exception ex)
|
{
|
AsyncLogHelper.Error($"查询记录失败: {ex.Message}");
|
}
|
return records;
|
}
|
}
|
/// <summary>
|
/// 根据产品编号查询
|
/// </summary>
|
/// <param name="productSN">产品编号</param>
|
/// <returns>List<RecordProductData></returns>
|
public static List<RecordProductData> GetRecordsByProductNumber(string productSN)
|
{
|
lock (recordProductDataRepositoryObject)
|
{
|
var records = new List<RecordProductData>();
|
try
|
{
|
using (var connection = DatabaseRecordProductDataHelper.GetConnection())
|
{
|
connection.Open();
|
string sql = "SELECT * FROM RecordProductData WHERE productSN = @productSN ORDER BY CreatedDate DESC";
|
using (var cmd = new MySqlCommand(sql, connection))
|
{
|
cmd.Parameters.AddWithValue("@productSN", productSN);
|
using (var reader = cmd.ExecuteReader())
|
{
|
while (reader.Read())
|
{
|
records.Add(MapReaderToRecord(reader));
|
}
|
}
|
}
|
}
|
}
|
catch (Exception ex)
|
{
|
AsyncLogHelper.Error($"根据产品编号查询失败: {ex.Message}");
|
}
|
return records;
|
}
|
}
|
/// <summary>
|
/// 根据操作者查询
|
/// </summary>
|
/// <param name="inspectionOperator">操作者</param>
|
/// <returns>List<RecordProductData></returns>
|
public static List<RecordProductData> GetRecordsByOperator(string inspectionOperator)
|
{
|
lock (recordProductDataRepositoryObject)
|
{
|
var records = new List<RecordProductData>();
|
try
|
{
|
using (var connection = DatabaseRecordProductDataHelper.GetConnection())
|
{
|
connection.Open();
|
string sql = "SELECT * FROM RecordProductData WHERE InspectionOperator = @InspectionOperator ORDER BY CreatedDate DESC";
|
using (var cmd = new MySqlCommand(sql, connection))
|
{
|
cmd.Parameters.AddWithValue("@InspectionOperator", inspectionOperator);
|
using (var reader = cmd.ExecuteReader())
|
{
|
while (reader.Read())
|
{
|
records.Add(MapReaderToRecord(reader));
|
}
|
}
|
}
|
}
|
}
|
catch (Exception ex)
|
{
|
AsyncLogHelper.Error($"根据操作者查询失败: {ex.Message}");
|
}
|
return records;
|
}
|
}
|
/// <summary>
|
/// 根据时间段查询 - 使用CreatedDate
|
/// </summary>
|
/// <param name="startTime">起始时间</param>
|
/// <param name="endTime">结束时间</param>
|
/// <returns>List<RecordProductData></returns>
|
public static List<RecordProductData> GetRecordsByTimeRange(DateTime startTime, DateTime endTime)
|
{
|
lock (recordProductDataRepositoryObject)
|
{
|
var records = new List<RecordProductData>();
|
try
|
{
|
using (var connection = DatabaseRecordProductDataHelper.GetConnection())
|
{
|
connection.Open();
|
string sql = @"
|
SELECT * FROM RecordProductData
|
WHERE CreatedDate BETWEEN @StartTime AND @EndTime
|
ORDER BY CreatedDate DESC";
|
using (var cmd = new MySqlCommand(sql, connection))
|
{
|
cmd.Parameters.AddWithValue("@StartTime", startTime);
|
cmd.Parameters.AddWithValue("@EndTime", endTime.AddDays(1).AddSeconds(-1)); // 包含结束日期的全天
|
using (var reader = cmd.ExecuteReader())
|
{
|
while (reader.Read())
|
{
|
records.Add(MapReaderToRecord(reader));
|
}
|
}
|
}
|
}
|
}
|
catch (Exception ex)
|
{
|
AsyncLogHelper.Error($"根据时间段查询失败: {ex.Message}");
|
}
|
return records;
|
}
|
}
|
|
/// <summary>
|
/// 组合查询：产品编号 + 时间段
|
/// </summary>
|
/// <param name="productSN">产品编号</param>
|
/// <param name="startTime">起始时间</param>
|
/// <param name="endTime">结束时间</param>
|
/// <returns>List<RecordProductData></returns>
|
public static List<RecordProductData> GetRecordsByProductAndTimeRange(string productSN, DateTime startTime, DateTime endTime)
|
{
|
lock (recordProductDataRepositoryObject)
|
{
|
var records = new List<RecordProductData>();
|
try
|
{
|
using (var connection = DatabaseRecordProductDataHelper.GetConnection())
|
{
|
connection.Open();
|
string sql = @"
|
SELECT * FROM RecordProductData
|
WHERE productSN = @productSN
|
AND CreatedDate BETWEEN @StartTime AND @EndTime
|
ORDER BY CreatedDate DESC";
|
using (var cmd = new MySqlCommand(sql, connection))
|
{
|
cmd.Parameters.AddWithValue("@productSN", productSN);
|
cmd.Parameters.AddWithValue("@StartTime", startTime);
|
cmd.Parameters.AddWithValue("@EndTime", endTime.AddDays(1).AddSeconds(-1));
|
using (var reader = cmd.ExecuteReader())
|
{
|
while (reader.Read())
|
{
|
records.Add(MapReaderToRecord(reader));
|
}
|
}
|
}
|
}
|
}
|
catch (Exception ex)
|
{
|
AsyncLogHelper.Error($"组合查询失败: {ex.Message}");
|
}
|
return records;
|
}
|
}
|
/// <summary>
|
/// 组合查询：操作者 + 时间段
|
/// </summary>
|
/// <param name="inspectionOperator">操作者</param>
|
/// <param name="startTime">起始时间</param>
|
/// <param name="endTime">结束时间</param>
|
/// <returns>List<RecordProductData></returns>
|
public static List<RecordProductData> GetRecordsByOperatorAndTimeRange(string inspectionOperator, DateTime startTime, DateTime endTime)
|
{
|
lock (recordProductDataRepositoryObject)
|
{
|
var records = new List<RecordProductData>();
|
try
|
{
|
using (var connection = DatabaseRecordProductDataHelper.GetConnection())
|
{
|
connection.Open();
|
string sql = @"
|
SELECT * FROM RecordProductData
|
WHERE InspectionOperator = @InspectionOperator
|
AND CreatedDate BETWEEN @StartTime AND @EndTime
|
ORDER BY CreatedDate DESC";
|
using (var cmd = new MySqlCommand(sql, connection))
|
{
|
cmd.Parameters.AddWithValue("@InspectionOperator", inspectionOperator);
|
cmd.Parameters.AddWithValue("@StartTime", startTime);
|
cmd.Parameters.AddWithValue("@EndTime", endTime.AddDays(1).AddSeconds(-1));
|
using (var reader = cmd.ExecuteReader())
|
{
|
while (reader.Read())
|
{
|
records.Add(MapReaderToRecord(reader));
|
}
|
}
|
}
|
}
|
}
|
catch (Exception ex)
|
{
|
AsyncLogHelper.Error($"组合查询失败: {ex.Message}");
|
}
|
return records;
|
}
|
}
|
/// <summary>
|
/// 高级组合查询：支持多个条件
|
/// </summary>
|
/// <param name="productSN">产品编号</param>
|
/// <param name="operatorName">操作者</param>
|
/// <param name="startTime">起始时间</param>
|
/// <param name="endTime">结束时间</param>
|
/// <returns>List<RecordProductData></returns>
|
public static List<RecordProductData> GetRecordsByMultipleConditions(
|
string productSN = null,
|
string operatorName = null,
|
DateTime? startTime = null,
|
DateTime? endTime = null)
|
{
|
lock (recordProductDataRepositoryObject)
|
{
|
var records = new List<RecordProductData>();
|
try
|
{
|
using (var connection = DatabaseRecordProductDataHelper.GetConnection())
|
{
|
connection.Open();
|
var sql = "SELECT * FROM RecordProductData WHERE 1=1";
|
var parameters = new List<MySqlParameter>();
|
if (!string.IsNullOrEmpty(productSN))
|
{
|
sql += " AND ProductSN = @ProductSN";
|
parameters.Add(new MySqlParameter("@ProductSN", productSN));
|
}
|
if (!string.IsNullOrEmpty(operatorName))
|
{
|
sql += " AND MeasurementOperator = @InspectionOperator";
|
parameters.Add(new MySqlParameter("@InspectionOperator", operatorName));
|
}
|
if (startTime.HasValue)
|
{
|
sql += " AND CreatedDate >= @StartTime";
|
parameters.Add(new MySqlParameter("@StartTime", startTime.Value));
|
}
|
if (endTime.HasValue)
|
{
|
sql += " AND CreatedDate <= @EndTime";
|
parameters.Add(new MySqlParameter("@EndTime", endTime.Value.AddDays(1).AddSeconds(-1)));
|
}
|
sql += " ORDER BY CreatedDate DESC";
|
using (var cmd = new MySqlCommand(sql, connection))
|
{
|
foreach (var param in parameters)
|
{
|
cmd.Parameters.Add(param);
|
}
|
using (var reader = cmd.ExecuteReader())
|
{
|
while (reader.Read())
|
{
|
records.Add(MapReaderToRecord(reader));
|
}
|
}
|
}
|
}
|
}
|
catch (Exception ex)
|
{
|
AsyncLogHelper.Error($"高级组合查询失败: {ex.Message}");
|
}
|
return records;
|
}
|
}
|
/// <summary>
|
/// 统计查询：获取某个时间段内的记录数量
|
/// </summary>
|
/// <param name="startTime">起始时间</param>
|
/// <param name="endTime">结束时间</param>
|
/// <returns>记录数量</returns>
|
public static int GetRecordCountByTimeRange(DateTime startTime, DateTime endTime)
|
{
|
lock (recordProductDataRepositoryObject)
|
{
|
try
|
{
|
using (var connection = DatabaseRecordProductDataHelper.GetConnection())
|
{
|
connection.Open();
|
string sql = @"
|
SELECT COUNT(*) FROM RecordProductData
|
WHERE CreatedDate BETWEEN @StartTime AND @EndTime";
|
using (var cmd = new MySqlCommand(sql, connection))
|
{
|
cmd.Parameters.AddWithValue("@StartTime", startTime);
|
cmd.Parameters.AddWithValue("@EndTime", endTime.AddDays(1).AddSeconds(-1));
|
return Convert.ToInt32(cmd.ExecuteScalar());
|
}
|
}
|
}
|
catch (Exception ex)
|
{
|
AsyncLogHelper.Error($"统计查询失败: {ex.Message}");
|
return 0;
|
}
|
}
|
}
|
/// <summary>
|
/// 辅助方法：将数据读取器映射到RecordProductData对象
|
/// </summary>
|
/// <param name="reader">MySqlDataReader reader</param>
|
/// <returns>RecordProductData</returns>
|
private static RecordProductData MapReaderToRecord(MySqlDataReader reader)
|
{
|
return new RecordProductData
|
{
|
ProductName = reader["ProductName"]?.ToString() ?? string.Empty,
|
ProductSN = reader["ProductSN"]?.ToString() ?? string.Empty,
|
InspectionOperator = reader["InspectionOperator"]?.ToString() ?? string.Empty,
|
NGType = reader["NGType"]?.ToString() ?? string.Empty,
|
NGSize = reader["NGSize"]?.ToString() ?? string.Empty,
|
DetectionTime = reader["DetectionTime"]?.ToString() ?? string.Empty,
|
CameraInspection = reader["CameraInspection"]?.ToString() ?? string.Empty,
|
};
|
}
|
}
|
}
|
