MeterVision_v2/MeterVision/db/DBComp.cs

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<int,int,List<VComp>> 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<VComp> comps = new List<VComp>();
            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<int,int,List<VCompDetail>> GetPagedCompDetails(CompFindModel findModel)
        {
            // 计算偏移量
            int offset = (findModel.PageNumber - 1) * findModel.PageSize;

            // 动态构造 WHERE 子句
            StringBuilder whereClause = new StringBuilder("WHERE comp_id = @CompId");

            List<SQLiteParameter> parameters = new List<SQLiteParameter>
            {
                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<VCompDetail> compDetails = new List<VCompDetail>();
            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);
        }
        //-------------------------------------------------------------------------------
    }
}