using MeterVision.model; using System; using System.Collections.Generic; using System.Data.SQLite; using System.Linq; using System.Text; using System.Threading.Tasks; namespace MeterVision.db { //识别结果比较表数据库表类 public class DBComp { public static bool InsertTComp(TComp tComp) { // 构建插入的 SQL 语句 string sql = "INSERT INTO t_comp (comp_id, create_time, n_patch_id,o_patch_id,stand_id) " + "VALUES (@CompId, @CreateTime, @NPatchId, @OPatchId, @StandId)"; // 创建参数数组 SQLiteParameter[] parameters = new SQLiteParameter[] { new SQLiteParameter("@CompId", tComp.CompId), new SQLiteParameter("@CreateTime", tComp.CreateTime), new SQLiteParameter("@NPatchId", tComp.NPatchId), new SQLiteParameter("@OPatchId", tComp.OPatchId), new SQLiteParameter("@StandId", tComp.StandId), }; try { // 调用 SQLiteHelper 执行插入操作 int rowsAffected = SQLiteHelper.ExecuteSql(sql, parameters); // 如果插入成功，返回 true，否则返回 false return rowsAffected > 0; } catch (Exception ex) { // 处理异常（如果有的话） Console.WriteLine("Error inserting data: " + ex.Message); return false; } } //删除比较表记录 public static bool DelteTComp(string compId) { string sql = "DELETE FROM t_comp WHERE comp_id =@CompId"; SQLiteParameter[] parameters = new SQLiteParameter[] { new SQLiteParameter("@CompId",compId) }; try { //执行 SQL 删除操作 int rowsAffected = SQLiteHelper.ExecuteSql(sql, parameters); //返回是否成功删除 return rowsAffected > 0; } catch (Exception ex) { //处理异常 Console.WriteLine("Error deleting data: " + ex.Message); return false; } } public static VComp GetVCompById(string compId) { VComp vComp = null; // 查询 SQL，从 v_comp 视图中获取数据 string query = @" SELECT comp_id, create_time, n_patch_id, n_patch_time, n_patch_name, n_patch_detail_count, n_patch_equal_count, o_patch_id, o_patch_time, o_patch_name, o_patch_detail_count, o_patch_equal_count, stand_id, stand_name, stand_count FROM v_comp WHERE comp_id = @CompId;"; // 构造参数 SQLiteParameter[] parameters = new SQLiteParameter[] { new SQLiteParameter("@CompId", compId) }; // 执行查询并映射到实体 using (SQLiteDataReader reader = SQLiteHelper.ExecuteReader(query, parameters)) { if (reader.Read()) { vComp = new VComp { CompId = reader["comp_id"].ToString(), CreateTime = reader["create_time"].ToString(), NPatchId = reader["n_patch_id"].ToString(), NPatchTime = reader["n_patch_time"].ToString(), NPatchName = reader["n_patch_name"].ToString(), NPatchDetailCount = reader["n_patch_detail_count"] == DBNull.Value ? 0 : Convert.ToInt32(reader["n_patch_detail_count"]), NPatchEqualCount = reader["n_patch_equal_count"] == DBNull.Value ? 0 : Convert.ToInt32(reader["n_patch_equal_count"]), OPatchId = reader["o_patch_id"].ToString(), OPatchTime = reader["o_patch_time"].ToString(), OPatchName = reader["o_patch_name"].ToString(), OPatchDetailCount = reader["o_patch_detail_count"] == DBNull.Value ? 0 : Convert.ToInt32(reader["o_patch_detail_count"]), OPatchEqualCount = reader["o_patch_equal_count"] == DBNull.Value ? 0 : Convert.ToInt32(reader["o_patch_equal_count"]), StandId = reader["stand_id"].ToString(), StandName = reader["stand_name"].ToString(), StandCount = reader["stand_count"] == DBNull.Value ? 0 : Convert.ToInt32(reader["stand_count"]) }; } } return vComp; } public static Tuple> GetPagedVComps(int pageNumber,int pageSize) { // 计算 OFFSET 值 int offset = (pageNumber - 1) * pageSize; // 定义 SQL 查询语句，带有分页 string sql = @" SELECT comp_id, create_time, n_patch_id, n_patch_time, n_patch_name, n_patch_detail_count, n_patch_equal_count, o_patch_id, o_patch_time, o_patch_name, o_patch_detail_count, o_patch_equal_count, stand_id, stand_name, stand_count FROM v_comp ORDER BY create_time DESC LIMIT @PageSize OFFSET @Offset;"; // 创建 SQL 参数 SQLiteParameter[] parameters = new SQLiteParameter[] { new SQLiteParameter("@PageSize", pageSize), new SQLiteParameter("@Offset", offset) }; // 执行查询并获取结果 List comps = new List(); using (SQLiteDataReader reader = SQLiteHelper.ExecuteReader(sql, parameters)) { while (reader.Read()) { // 将查询结果映射到 TStand 对象 VComp comp = new VComp { CompId = reader["comp_id"].ToString(), CreateTime = reader["create_time"].ToString(), NPatchId = reader["n_patch_id"].ToString(), NPatchTime = reader["n_patch_time"].ToString(), NPatchName = reader["n_patch_name"].ToString(), NPatchDetailCount = reader["n_patch_detail_count"] == DBNull.Value ? 0 : Convert.ToInt32(reader["n_patch_detail_count"]), NPatchEqualCount = reader["n_patch_equal_count"] == DBNull.Value ? 0 : Convert.ToInt32(reader["n_patch_equal_count"]), OPatchId = reader["o_patch_id"].ToString(), OPatchTime = reader["o_patch_time"].ToString(), OPatchName = reader["o_patch_name"].ToString(), OPatchDetailCount = reader["o_patch_detail_count"] == DBNull.Value ? 0 : Convert.ToInt32(reader["o_patch_detail_count"]), OPatchEqualCount = reader["o_patch_equal_count"] == DBNull.Value ? 0 : Convert.ToInt32(reader["o_patch_equal_count"]), StandId = reader["stand_id"].ToString(), StandName = reader["stand_name"].ToString(), StandCount = reader["stand_count"] == DBNull.Value ? 0 : Convert.ToInt32(reader["stand_count"]) }; comps.Add(comp); } } //获取总记录数，用于计算总页数 string countSql = "SELECT COUNT(*) FROM v_comp"; int totalRecords = Convert.ToInt32(SQLiteHelper.GetSingle(countSql)); // 计算总页数 int totalPages = (int)Math.Ceiling((double)totalRecords / pageSize); // 返回元组，包含总页数和结果列表 return Tuple.Create(totalRecords, totalPages, comps); } //获取数据 public static Tuple> GetPagedCompDetails(CompFindModel findModel) { // 计算偏移量 int offset = (findModel.PageNumber - 1) * findModel.PageSize; // 动态构造 WHERE 子句 StringBuilder whereClause = new StringBuilder("WHERE comp_id = @CompId"); List parameters = new List { new SQLiteParameter("@CompId", findModel.CompId), new SQLiteParameter("@PageSize", findModel.PageSize), new SQLiteParameter("@Offset", offset) }; if(findModel.CompFindType == Patch.CompFindType.NERROR_ORIGHT) { //新错旧对 whereClause.Append(" AND equal_flag1 != 1 AND equal_flag2 = 1"); } else if(findModel.CompFindType == Patch.CompFindType.NRIGHT_OERROR) { //新对旧错 whereClause.Append(" AND equal_flag1 = 1 AND equal_flag2 != 1"); } else if(findModel.CompFindType == Patch.CompFindType.NRIGHT_ORIGHT) { //新对旧对 whereClause.Append(" AND equal_flag1 = 1 AND equal_flag2 = 1"); } else if(findModel.CompFindType == Patch.CompFindType.NERROR_OERROR) { //新错旧错 whereClause.Append(" AND equal_flag1 != 1 AND equal_flag2 != 1"); } // 定义 SQL 查询语句，从视图中分页查询 string sql = $@" SELECT comp_id, create_time, n_patch_id, o_patch_id, stand_id, patch_detail_id1, create_time1, stand_detail_id1, stand_value1, src_image1, run_flag1, run_time1, dst_image1, meter_type1, digit_count1, pointer_count1, last_unit1, result_type1, raw_value1, final_value1, equal_flag1, ai_ver1, debug_info1, log_path1, memo1, patch_detail_id2, create_time2, stand_detail_id2, stand_value2, src_image2, run_flag2, run_time2, dst_image2, meter_type2, digit_count2, pointer_count2, last_unit2, result_type2, raw_value2, final_value2, equal_flag2, ai_ver2, debug_info2, log_path2, memo2 FROM v_comp_detail {whereClause} ORDER BY create_time ASC LIMIT @PageSize OFFSET @Offset;"; // 执行查询并读取数据 List compDetails = new List(); using (SQLiteDataReader reader = SQLiteHelper.ExecuteReader(sql, parameters.ToArray())) { while (reader.Read()) { VCompDetail compDetail = new VCompDetail { CompId = reader["comp_id"].ToString(), CreateTime = reader["create_time"].ToString(), NPatchId = reader["n_patch_id"].ToString(), OPatchId = reader["o_patch_id"].ToString(), StandId = reader["stand_id"].ToString(), PatchDetail1 = new TPatchDetail { //PatchId = reader["n_patch_id"].ToString(), PatchDetailId = reader["patch_detail_id1"].ToString(), CreateTime = reader["create_time1"].ToString(), StandDetailId = reader["stand_detail_id1"].ToString(), StandValue = reader["stand_value1"].ToString(), SrcImage = reader["src_image1"].ToString(), RunFlag = Convert.ToInt32(reader["run_flag1"]), RunTime = reader["run_time1"].ToString(), DstImage = reader["dst_image1"].ToString(), MeterType = Convert.ToInt32(reader["meter_type1"]), DigitCount = Convert.ToInt32(reader["digit_count1"]), PointerCount = Convert.ToInt32(reader["pointer_count1"]), //LastUnit = reader["last_unit1"].ToString(), ResultType = Convert.ToInt32(reader["result_type1"]), RawValue = Convert.ToInt64(reader["raw_value1"]), FinalValue = Convert.ToInt64(reader["final_value1"]), EqualFlag = Convert.ToInt32(reader["equal_flag1"]), AiVer = reader["ai_ver1"].ToString(), DebugInfo = reader["debug_info1"].ToString(), LogPath = reader["log_path1"].ToString(), Memo = reader["memo1"].ToString() }, PatchDetail2 = new TPatchDetail { PatchDetailId = reader["patch_detail_id2"].ToString(), CreateTime = reader["create_time2"].ToString(), StandDetailId = reader["stand_detail_id2"].ToString(), StandValue = reader["stand_value2"].ToString(), SrcImage = reader["src_image2"].ToString(), RunFlag = Convert.ToInt32(reader["run_flag2"]), RunTime = reader["run_time2"].ToString(), DstImage = reader["dst_image2"].ToString(), MeterType = Convert.ToInt32(reader["meter_type2"]), DigitCount = Convert.ToInt32(reader["digit_count2"]), PointerCount = Convert.ToInt32(reader["pointer_count2"]), //LastUnit = reader["last_unit2"].ToString(), ResultType = Convert.ToInt32(reader["result_type2"]), RawValue = Convert.ToInt64(reader["raw_value2"]), FinalValue = Convert.ToInt64(reader["final_value2"]), EqualFlag = Convert.ToInt32(reader["equal_flag2"]), AiVer = reader["ai_ver2"].ToString(), DebugInfo = reader["debug_info2"].ToString(), LogPath = reader["log_path2"].ToString(), Memo = reader["memo2"].ToString() } }; compDetails.Add(compDetail); } } // 获取总记录数，用于计算总页数 string countSql = "SELECT COUNT(*) FROM v_comp_detail " + whereClause; int totalRecords = Convert.ToInt32(SQLiteHelper.GetSingle(countSql, parameters.ToArray())); // 计算总页数 int totalPages = (int)Math.Ceiling((double)totalRecords / findModel.PageSize); // 返回总记录数、总页数和分页结果 return Tuple.Create(totalRecords, totalPages, compDetails); } //------------------------------------------------------------------------------- } }