MeterVision_v2/MeterVision/db/DBPatch.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 DBPatch
    {
        public static bool InsertTPatch(TPatch tPatch)
        {
            // 构建插入的 SQL 语句
            string sql = "INSERT INTO t_patch (patch_id, create_time, patch_name,stand_id,stand_name) " +
                         "VALUES (@PatchId, @CreateTime, @PatchName, @StandId, @StandName)";

            // 创建参数数组
            SQLiteParameter[] parameters = new SQLiteParameter[]
            {
                new SQLiteParameter("@PatchId", tPatch.PatchId),
                new SQLiteParameter("@CreateTime", tPatch.CreateTime),
                new SQLiteParameter("@PatchName", tPatch.PatchName),
                new SQLiteParameter("@StandId", tPatch.StandId),
                new SQLiteParameter("@StandName", tPatch.StandName),
            };

            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 DeletePatchAndDetails(string patchId)
        {
            // 定义删除的 SQL 语句，使用事务来保证一致性
            string sql = @"
                BEGIN TRANSACTION;

                -- 删除 t_patch_detail 表中与 stand_id 相关的数据
                DELETE FROM t_patch_detail WHERE patch_id = @PatchId;

                -- 删除 t_patch 表中与 patch_id 相关的数据
                DELETE FROM t_patch WHERE patch_id = @PatchId;

                COMMIT;";

            // 创建参数
            SQLiteParameter[] parameters = new SQLiteParameter[]
            {
                new SQLiteParameter("@PatchId", patchId)
            };

            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 Tuple<int,int, List<VPatch>> GetPagedVPatchs(int pageNumber, int pageSize)
        {
            // 计算 OFFSET 值
            int offset = (pageNumber - 1) * pageSize;

            // 定义 SQL 查询语句，带有分页
            string sql = @"
                SELECT patch_id, create_time, patch_name, stand_id, stand_name, 
                    detail_count,run_count,equal_count,invalid_count,error_count
                FROM v_patch ORDER BY create_time DESC
                LIMIT @PageSize OFFSET @Offset;";

            // 创建 SQL 参数
            SQLiteParameter[] parameters = new SQLiteParameter[]
            {
                new SQLiteParameter("@PageSize", pageSize),
                new SQLiteParameter("@Offset", offset)
            };

            // 执行查询并获取结果
            List<VPatch> patchs = new List<VPatch>();
            using (SQLiteDataReader reader = SQLiteHelper.ExecuteReader(sql, parameters))
            {
                while (reader.Read())
                {
                    // 将查询结果映射到 TStand 对象
                    VPatch patch = new VPatch
                    {
                        PatchId = reader.GetString(reader.GetOrdinal("patch_id")),
                        CreateTime = reader.GetString(reader.GetOrdinal("create_time")),
                        PatchName = reader.GetString(reader.GetOrdinal("patch_name")),
                        StandId = reader.GetString(reader.GetOrdinal("stand_id")),
                        StandName = reader.GetString(reader.GetOrdinal("stand_name")),
                        DetailCount = reader.GetInt32(reader.GetOrdinal("detail_count")),
                        RunCount = reader.GetInt32(reader.GetOrdinal("run_count")),
                        EqualCount = reader.GetInt32(reader.GetOrdinal("equal_count")),
                        InvalidCount = reader.GetInt32(reader.GetOrdinal("invalid_count")),
                        ErrorCount = reader.GetInt32(reader.GetOrdinal("error_count"))
                    };
                    patchs.Add(patch);
                }
            }

            // 获取总记录数，用于计算总页数
            string countSql = "SELECT COUNT(*) FROM v_patch";
            int totalRecords = Convert.ToInt32(SQLiteHelper.GetSingle(countSql));

            // 计算总页数
            int totalPages = (int)Math.Ceiling((double)totalRecords / pageSize);

            // 返回元组，包含总页数和结果列表
            return Tuple.Create(totalRecords,totalPages, patchs);
        }

        public static bool InsertPatchDetail(TPatchDetail patchDetail)
        {
            // 插入SQL语句
            string sql = @"
                INSERT INTO t_patch_detail (patch_detail_id, patch_id, create_time, stand_detail_id, stand_value, src_image)
                VALUES (@PatchDetailId, @PatchId, @CreateTime, @StandDetailId, @StandValue, @SrcImage);";

            // 定义参数
            SQLiteParameter[] parameters = new SQLiteParameter[]
            {
                new SQLiteParameter("@PatchDetailId", patchDetail.PatchDetailId),
                new SQLiteParameter("@PatchId", patchDetail.PatchId),
                new SQLiteParameter("@CreateTime", patchDetail.CreateTime),
                new SQLiteParameter("@StandDetailId", patchDetail.StandDetailId),
                new SQLiteParameter("@StandValue", patchDetail.StandValue),
                new SQLiteParameter("@SrcImage", patchDetail.SrcImage)
            };

            // 执行插入操作
            try
            {
                int rowsAffected = SQLiteHelper.ExecuteNonQuery(sql, parameters);
                return rowsAffected > 0;  // 返回是否成功插入
            }
            catch (Exception ex)
            {
                // 处理异常
                Console.WriteLine(ex.Message);
                return false;
            }
        }

        public static bool InsertPatchDetails(List<TPatchDetail> patchDetails)
        {
            // 插入SQL语句
            string sql = @"
                INSERT INTO t_patch_detail (patch_detail_id, patch_id, create_time, stand_detail_id, stand_value, src_image)
                VALUES (@PatchDetailId, @PatchId, @CreateTime, @StandDetailId, @StandValue, @SrcImage);";

            try
            {
                // 开始事务
                using (SQLiteConnection connection = SQLiteHelper.GetConnection())
                {
                    connection.Open();
                    using (SQLiteTransaction transaction = connection.BeginTransaction())
                    {
                        // 遍历列表插入每一条数据
                        foreach (var patchDetail in patchDetails)
                        {
                            SQLiteParameter[] parameters = new SQLiteParameter[]
                            {
                                new SQLiteParameter("@PatchDetailId", patchDetail.PatchDetailId),
                                new SQLiteParameter("@PatchId", patchDetail.PatchId),
                                new SQLiteParameter("@CreateTime", patchDetail.CreateTime),
                                new SQLiteParameter("@StandDetailId", patchDetail.StandDetailId),
                                new SQLiteParameter("@StandValue", patchDetail.StandValue),
                                new SQLiteParameter("@SrcImage", patchDetail.SrcImage)
                            };

                            // 执行插入操作
                            SQLiteHelper.ExecuteNonQuery(sql, parameters, transaction);
                        }

                        // 提交事务
                        transaction.Commit();
                    }
                }
                return true;
            }
            catch (Exception ex)
            {
                // 处理异常
                Console.WriteLine(ex.Message);
                return false;
            }
        }

        //根据识别结果修改表内容
        public static bool UpdatePatchDetailWithResult(TPatchDetail detail)
        {
            string sql = @"
                UPDATE t_patch_detail                 
                SET run_flag = @RunFlag,
                    run_time = @RunTime,
                    dst_image = @DstImage,
                    result_meter = @ResultMeter,
                    result_type = @ResultType,
                    raw_value = @RawValue,
                    final_value = @FinalValue,
                    complete_value = @CompleteValue,
                    equal_flag = @EqualFlag,
                    ai_ver = @AiVer,
                    debug_info = @DebugInfo,
                    log_path = @LogPath
                WHERE patch_detail_id = @PatchDetailId;";

            // 创建 SQL 参数
            SQLiteParameter[] parameters = new SQLiteParameter[]
            {
                new SQLiteParameter("@PatchDetailId", detail.PatchDetailId),
                new SQLiteParameter("@RunFlag", detail.RunFlag),
                new SQLiteParameter("@RunTime", detail.RunTime),
                new SQLiteParameter("@DstImage", detail.DstImage),
                new SQLiteParameter("@ResultMeter", detail.ResultMeter),
                new SQLiteParameter("@ResultType", detail.ResultType),
                new SQLiteParameter("@RawValue", detail.RawValue),
                new SQLiteParameter("@FinalValue", detail.FinalValue),
                new SQLiteParameter("@CompleteValue",detail.CompleteValue),
                new SQLiteParameter("@EqualFlag", detail.EqualFlag),
                new SQLiteParameter("@AiVer", detail.AiVer),
                new SQLiteParameter("@DebugInfo", detail.DebugInfo),
                new SQLiteParameter("@LogPath", detail.LogPath)
            };
            // 执行更新操作并返回受影响的行数
            return SQLiteHelper.ExecuteNonQuery(sql, parameters) > 0;
        }




        public static bool UpdatePatchDetailStandValue(string patchDetailId,string standValue,int equalFlag)
        {
            string sql = @"
                UPDATE t_patch_detail
                SET stand_value = @StandValue,
                    equal_flag = @EqualFlag
                WHERE patch_detail_id = @PatchDetailId;";

            // 创建 SQL 参数
            SQLiteParameter[] parameters = new SQLiteParameter[]
            {
                new SQLiteParameter("@PatchDetailId", patchDetailId),
                new SQLiteParameter("@EqualFlag", equalFlag),
                new SQLiteParameter("@StandValue", standValue)
            };

            // 执行更新操作并返回受影响的行数
            return SQLiteHelper.ExecuteNonQuery(sql, parameters) > 0;
        }

        //同时插入t_patch与t_patch_detail(在一个事务中)
        public static bool InsertPatchAndDetails(TPatch patch, List<TPatchDetail> patchDetails)
        {
            try
            {
                using (SQLiteConnection connection = SQLiteHelper.GetConnection())
                {
                    connection.Open();
                    using (SQLiteTransaction transaction = connection.BeginTransaction())
                    {
                        // 插入 t_patch 表
                        string patchSql = "INSERT INTO t_patch (patch_id, create_time, patch_name, stand_id, stand_name) VALUES (@PatchId, @CreateTime, @PatchName, @StandId, @StandName)";
                        SQLiteParameter[] patchParams = new SQLiteParameter[]
                        {
                            new SQLiteParameter("@PatchId", patch.PatchId),
                            new SQLiteParameter("@CreateTime", patch.CreateTime),
                            new SQLiteParameter("@PatchName", patch.PatchName),
                            new SQLiteParameter("@StandId", patch.StandId),
                            new SQLiteParameter("@StandName", patch.StandName)
                        };
                        SQLiteHelper.ExecuteNonQuery(patchSql, patchParams, transaction);

                        // 插入 t_patch_detail 表
                        foreach (var patchDetail in patchDetails)
                        {
                            //string detailSql = "INSERT INTO t_patch_detail (patch_detail_id, patch_id, create_time, stand_detail_id, stand_value, src_image) VALUES (@PatchDetailId, @PatchId, @CreateTime, @StandDetailId, @StandValue, @SrcImage)";

                            string detailSql = @"
                                INSERT INTO t_patch_detail (
                                    patch_detail_id, patch_id, create_time, 
                                    stand_detail_id, stand_value, src_image, 
                                    sample_time, station_key, station_id, 
                                    meter_type, bright_val, flow_rate, 
                                    digit_count, pointer_count, last_unit, 
                                    meter_region, feature_region, last_compress, 
                                    latest_value, latest_time, latest_complete
                                ) VALUES (
                                    @PatchDetailId, @PatchId, @CreateTime, 
                                    @StandDetailId, @StandValue, @SrcImage, 
                                    @SampleTime, @StationKey, @StationId, 
                                    @MeterType, @BrightVal, @FlowRate, 
                                    @DigitCount, @PointerCount, @LastUnit, 
                                    @MeterRegion, @FeatureRegion, @LastCompress, 
                                    @LatestValue, @LatestTime, @LatestComplete
                                )";

                            SQLiteParameter[] detailParams = new SQLiteParameter[]
                            {
                                new SQLiteParameter("@PatchDetailId", patchDetail.PatchDetailId),
                                new SQLiteParameter("@PatchId", patchDetail.PatchId),
                                new SQLiteParameter("@CreateTime", patchDetail.CreateTime),
                                new SQLiteParameter("@StandDetailId", patchDetail.StandDetailId),
                                new SQLiteParameter("@StandValue", patchDetail.StandValue),
                                new SQLiteParameter("@SrcImage", patchDetail.SrcImage),
                                new SQLiteParameter("@SampleTime", patchDetail.SampleTime),
                                new SQLiteParameter("@StationKey", patchDetail.StationKey),
                                new SQLiteParameter("@StationId", patchDetail.StationId),
                                new SQLiteParameter("@MeterType", patchDetail.MeterType),
                                new SQLiteParameter("@BrightVal", patchDetail.BrightVal),
                                new SQLiteParameter("@FlowRate", patchDetail.FlowRate),
                                new SQLiteParameter("@DigitCount", patchDetail.DigitCount),
                                new SQLiteParameter("@PointerCount", patchDetail.PointerCount),
                                new SQLiteParameter("@LastUnit", patchDetail.LastUnit),
                                new SQLiteParameter("@MeterRegion", patchDetail.MeterRegion),
                                new SQLiteParameter("@FeatureRegion", patchDetail.FeatureRegion),
                                new SQLiteParameter("@LastCompress", patchDetail.LastCompress),
                                new SQLiteParameter("@LatestValue", patchDetail.LatestValue),
                                new SQLiteParameter("@LatestTime", patchDetail.LatestTime),
                                new SQLiteParameter("@LatestComplete", patchDetail.LatestComplete),
                            };
                            SQLiteHelper.ExecuteNonQuery(detailSql, detailParams, transaction);
                        }

                        // 提交事务
                        transaction.Commit();
                        return true;
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"插入数据失败：{ex.Message}");
                return false;
            }
        }


        public static bool UpdatePatchDetails_Latest(List<TPatchDetail> patchDetails)
        {
            try
            {
                using (SQLiteConnection connection = SQLiteHelper.GetConnection())
                {
                    connection.Open();
                    using (SQLiteTransaction transaction = connection.BeginTransaction())
                    {
                        // 插入 t_patch_detail 表
                        foreach (var patchDetail in patchDetails)
                        {
                            // 更新 t_patch_detail 表（仅涉及的字段）
                            string detailSql = @"
                                UPDATE t_patch_detail                 
                                SET latest_value = @LatestValue,
                                    latest_time = @LatestTime,
                                    latest_complete = @LatestComplete,
                                    last_compress = @LastCompress
                                WHERE patch_detail_id = @PatchDetailId;";

                            SQLiteParameter[] detailParams = new SQLiteParameter[]
                            {
                                new SQLiteParameter("@PatchDetailId", patchDetail.PatchDetailId),                                
                                new SQLiteParameter("@LastCompress", patchDetail.LastCompress),
                                new SQLiteParameter("@LatestValue", patchDetail.LatestValue),
                                new SQLiteParameter("@LatestTime", patchDetail.LatestTime),
                                new SQLiteParameter("@LatestComplete", patchDetail.LatestComplete),
                            };
                            SQLiteHelper.ExecuteNonQuery(detailSql, detailParams, transaction);
                        }

                        // 提交事务
                        transaction.Commit();
                        return true;
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"插入数据失败：{ex.Message}");
                return false;
            }
        }

        public static bool UpdatePatchDetails_Latest(TPatchDetail patchDetail)
        {
            try
            {
                using (SQLiteConnection connection = SQLiteHelper.GetConnection())
                {
                    connection.Open();

                    // 更新 t_patch_detail 表（仅涉及的字段）
                    string detailSql = @"
                        UPDATE t_patch_detail                 
                        SET latest_value = @LatestValue,
                            latest_time = @LatestTime,
                            latest_complete = @LatestComplete,
                            last_compress = @LastCompress
                        WHERE patch_detail_id = @PatchDetailId;";

                    SQLiteParameter[] detailParams = new SQLiteParameter[]
                    {
                        new SQLiteParameter("@PatchDetailId", patchDetail.PatchDetailId),
                        new SQLiteParameter("@LastCompress", patchDetail.LastCompress),
                        new SQLiteParameter("@LatestValue", patchDetail.LatestValue),
                        new SQLiteParameter("@LatestTime", patchDetail.LatestTime),
                        new SQLiteParameter("@LatestComplete", patchDetail.LatestComplete),
                    };
                    SQLiteHelper.ExecuteNonQuery(detailSql, detailParams);

                    return true;
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"插入数据失败：{ex.Message}");
                return false;
            }
        }


        public static Tuple<int, int, List<TPatchDetail>> GetPagedPatchDetails(int pageNumber, int pageSize, string patchId)
        {
            // 计算偏移量
            int offset = (pageNumber - 1) * pageSize;

            // 分页查询SQL
            //string sql = @"
            //    SELECT patch_detail_id, patch_id, create_time, stand_detail_id, stand_value, src_image, 
            //           run_flag, run_time, dst_image, meter_type, digit_count, pointer_count, 
            //           last_unit, result_type, raw_value, final_value, equal_flag, ai_ver, debug_info, log_path
            //    FROM t_patch_detail WHERE patch_id = @PatchId
            //    ORDER BY create_time DESC  -- 根据需要修改排序字段
            //    LIMIT @PageSize OFFSET @Offset;";
            string sql = @"
                SELECT patch_detail_id, patch_id, create_time, stand_detail_id, stand_value, src_image, 
                       run_flag, run_time, dst_image, meter_type, digit_count, pointer_count, 
                       last_unit, result_type, raw_value, final_value, equal_flag, ai_ver, debug_info, log_path, memo
                FROM t_patch_detail WHERE patch_id = @PatchId
                ORDER BY create_time ASC  -- 根据需要修改排序字段
                LIMIT @PageSize OFFSET @Offset;";

            // 定义参数
            SQLiteParameter[] parameters = new SQLiteParameter[]
            {
                new SQLiteParameter("@PatchId", patchId),
                new SQLiteParameter("@PageSize", pageSize),
                new SQLiteParameter("@Offset", offset)
            };

            // 执行查询并读取数据
            List<TPatchDetail> patchDetails = new List<TPatchDetail>();
            using (SQLiteDataReader reader = SQLiteHelper.ExecuteReader(sql, parameters))
            {
                while (reader.Read())
                {
                    // 将查询结果映射到 TPatchDetail 对象
                    TPatchDetail patchDetail = new TPatchDetail
                    {
                        PatchDetailId = reader.GetString(reader.GetOrdinal("patch_detail_id")),
                        PatchId = reader.GetString(reader.GetOrdinal("patch_id")),
                        CreateTime = reader.GetString(reader.GetOrdinal("create_time")),
                        StandDetailId = reader.GetString(reader.GetOrdinal("stand_detail_id")),
                        StandValue = reader.GetString(reader.GetOrdinal("stand_value")),
                        SrcImage = reader.GetString(reader.GetOrdinal("src_image")),
                        RunFlag = reader.GetInt32(reader.GetOrdinal("run_flag")),
                        RunTime = reader.GetString(reader.GetOrdinal("run_time")),
                        DstImage = reader.GetString(reader.GetOrdinal("dst_image")),
                        MeterType = reader.GetInt32(reader.GetOrdinal("meter_type")),
                        DigitCount = reader.GetInt32(reader.GetOrdinal("digit_count")),
                        PointerCount = reader.GetInt32(reader.GetOrdinal("pointer_count")),
                        LastUnit = reader.GetDouble(reader.GetOrdinal("last_unit")),
                        ResultType = reader.GetInt32(reader.GetOrdinal("result_type")),
                        RawValue = reader.GetInt64(reader.GetOrdinal("raw_value")),
                        FinalValue = reader.GetInt64(reader.GetOrdinal("final_value")),
                        EqualFlag = reader.GetInt32(reader.GetOrdinal("equal_flag")),
                        AiVer = reader.GetString(reader.GetOrdinal("ai_ver")),
                        DebugInfo = reader.GetString(reader.GetOrdinal("debug_info")),
                        LogPath = reader.GetString(reader.GetOrdinal("log_path")),
                        Memo = reader.GetString(reader.GetOrdinal("memo"))
                    };
                    patchDetails.Add(patchDetail);
                }
            }

            // 获取总记录数，用于计算总页数
            string countSql = "SELECT COUNT(*) FROM t_patch_detail WHERE patch_id = @PatchId";
            int totalRecords = Convert.ToInt32(SQLiteHelper.GetSingle(countSql, new SQLiteParameter("@PatchId", patchId)));

            // 计算总页数
            int totalPages = (int)Math.Ceiling((double)totalRecords / pageSize);

            // 返回总记录数、总页数和分页结果
            return Tuple.Create(totalRecords, totalPages, patchDetails);
        }


        public static Tuple<int, int, List<TPatchDetail>> GetPagedPatchDetails(PatchFindModel findModel)
        {
            // 计算偏移量
            int offset = (findModel.PageNumber - 1) * findModel.PageSize;

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

            List<SQLiteParameter> parameters = new List<SQLiteParameter>
            {
                new SQLiteParameter("@PatchId", findModel.PatchId),
                new SQLiteParameter("@PageSize", findModel.PageSize),
                new SQLiteParameter("@Offset", offset)
            };

            if (findModel.RunFlag >= 0)
            {
                whereClause.Append(" AND run_flag = @RunFlag");
                parameters.Add(new SQLiteParameter("@RunFlag", findModel.RunFlag));
            }

            if (findModel.EqualFlag >= 0)
            {
                whereClause.Append(" AND equal_flag = @EqualFlag");
                parameters.Add(new SQLiteParameter("@EqualFlag", findModel.EqualFlag));
            }

            if(findModel.PatchFindType == Patch.PatchFindType.HAVE_MEMO)
            {
                whereClause.Append(" AND memo != ''");
                //parameters.Add(new SQLiteParameter("@EqualFlag", findModel.EqualFlag));
            }

            //if (findModel.EqualFlag == 2) // 针对无标准值的查询条件
            //{
            //    whereClause.Append(" AND stand_value = ''");
            //}

            // 分页查询SQL
            //string sql = $@"
            //    SELECT patch_detail_id, patch_id, create_time, stand_detail_id, stand_value, src_image, 
            //           run_flag, run_time, dst_image, meter_type, digit_count, pointer_count, 
            //           last_unit, result_type, raw_value, final_value, equal_flag, ai_ver, debug_info, log_path, memo
            //    FROM t_patch_detail 
            //    {whereClause}
            //    ORDER BY create_time ASC  -- 根据需要修改排序字段
            //    LIMIT @PageSize OFFSET @Offset;";

            string sql = $@"
                SELECT * FROM t_patch_detail
                {whereClause}
                ORDER BY station_id,sample_time ASC
                LIMIT @PageSize OFFSET @Offset;";

            // 执行查询并读取数据
            List<TPatchDetail> patchDetails = new List<TPatchDetail>();
            using (SQLiteDataReader reader = SQLiteHelper.ExecuteReader(sql, parameters.ToArray()))
            {
                while (reader.Read())
                {
                    TPatchDetail patchDetail = new TPatchDetail
                    {
                        PatchDetailId = reader.GetString(reader.GetOrdinal("patch_detail_id")),
                        PatchId = reader.GetString(reader.GetOrdinal("patch_id")),
                        CreateTime = reader.GetString(reader.GetOrdinal("create_time")),
                        StandDetailId = reader.GetString(reader.GetOrdinal("stand_detail_id")),
                        StandValue = reader.GetString(reader.GetOrdinal("stand_value")),
                        SrcImage = reader.GetString(reader.GetOrdinal("src_image")),
                        SampleTime = reader.GetString(reader.GetOrdinal("sample_time")),
                        RunFlag = reader.GetInt32(reader.GetOrdinal("run_flag")),
                        RunTime = reader.GetString(reader.GetOrdinal("run_time")),
                        DstImage = reader.GetString(reader.GetOrdinal("dst_image")),
                        ResultType = reader.GetInt32(reader.GetOrdinal("result_type")),
                        RawValue = reader.GetInt64(reader.GetOrdinal("raw_value")),
                        FinalValue = reader.GetInt64(reader.GetOrdinal("final_value")),
                        CompleteValue = reader.GetInt64(reader.GetOrdinal("complete_value")),
                        ValueChanged = reader.GetInt32(reader.GetOrdinal("value_changed")),
                        ResultMeter = reader.GetInt32(reader.GetOrdinal("result_meter")),
                        EqualFlag = reader.GetInt32(reader.GetOrdinal("equal_flag")),
                        AiVer = reader.GetString(reader.GetOrdinal("ai_ver")),
                        DebugInfo = reader.GetString(reader.GetOrdinal("debug_info")),
                        LogPath = reader.GetString(reader.GetOrdinal("log_path")),
                        Memo = reader.GetString(reader.GetOrdinal("memo")),
                        StationKey = reader.GetString(reader.GetOrdinal("station_key")),
                        StationId = reader.GetString(reader.GetOrdinal("station_id")),
                        MeterType = reader.GetInt32(reader.GetOrdinal("meter_type")),
                        BrightVal = reader.GetDouble(reader.GetOrdinal("bright_val")),
                        FlowRate = reader.GetInt32(reader.GetOrdinal("flow_rate")),
                        DigitCount = reader.GetInt32(reader.GetOrdinal("digit_count")),
                        PointerCount = reader.GetInt32(reader.GetOrdinal("pointer_count")),
                        LastUnit = reader.GetDouble(reader.GetOrdinal("last_unit")),
                        MeterRegion = reader.GetString(reader.GetOrdinal("meter_region")),
                        FeatureRegion = reader.GetString(reader.GetOrdinal("feature_region")),
                        LastCompress = reader.GetInt32(reader.GetOrdinal("last_compress")),
                        LatestValue = reader.GetInt64(reader.GetOrdinal("latest_value")),
                        LatestTime = reader.GetString(reader.GetOrdinal("latest_time")),
                        LatestComplete = reader.GetInt64(reader.GetOrdinal("latest_complete"))
                    };
                    patchDetails.Add(patchDetail);
                }
            }

            // 获取总记录数，用于计算总页数
            string countSql = "SELECT COUNT(*) FROM t_patch_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, patchDetails);
        }


        public static VPatch GetVPatchById(string patchId)
        {
            VPatch vPatch = null;

            // 查询 SQL，从 v_patch 视图中获取数据
            string query = @"
            SELECT 
                patch_id, 
                create_time, 
                patch_name, 
                stand_id,
                stand_name,
                detail_count, 
                run_count, 
                equal_count,
                invalid_count,
                error_count
            FROM v_patch
            WHERE patch_id = @PatchId;";

            // 构造参数
            SQLiteParameter[] parameters = new SQLiteParameter[]
            {
                new SQLiteParameter("@PatchId", patchId)
            };

            // 执行查询
            using (SQLiteDataReader reader = SQLiteHelper.ExecuteReader(query, parameters))
            {
                if (reader.Read())
                {
                    vPatch = new VPatch
                    {
                        PatchId = reader["patch_id"].ToString(),
                        CreateTime = reader["create_time"].ToString(),
                        PatchName = reader["patch_name"].ToString(),
                        StandId = reader["stand_id"].ToString(),
                        StandName = reader["stand_name"].ToString(),
                        DetailCount = reader["detail_count"] == DBNull.Value ? 0 : Convert.ToInt32(reader["detail_count"]),
                        RunCount = reader["run_count"] == DBNull.Value ? 0 : Convert.ToInt32(reader["run_count"]),
                        EqualCount = reader["equal_count"] == DBNull.Value ? 0 : Convert.ToInt32(reader["equal_count"]),
                        InvalidCount = reader["invalid_count"] == DBNull.Value ? 0 : Convert.ToInt32(reader["invalid_count"]),
                        ErrorCount = reader["error_count"] == DBNull.Value ? 0 : Convert.ToInt32(reader["error_count"]),
                    };
                }
            }

            return vPatch;
        }

        public static Tuple<int, int, List<VPatchStation>> GetPagedPatchStations(int pageNumber, int pageSize,string patchId)
        {
            // 计算偏移量
            int offset = (pageNumber - 1) * pageSize;

            string sql = @"
                SELECT patch_id, station_id, total_count, error_count,equal_count, invalid_count
                FROM v_patch_detail_station 
                WHERE patch_id = @PatchId OR patch_id = ''
                ORDER BY station_id ASC  -- 根据需要修改排序字段
                LIMIT @PageSize OFFSET @Offset;";

            // 定义参数
            SQLiteParameter[] parameters = new SQLiteParameter[]
            {
                new SQLiteParameter("@PatchId", patchId),
                new SQLiteParameter("@PageSize", pageSize),
                new SQLiteParameter("@Offset", offset)
            };

            // 执行查询并读取数据
            List<VPatchStation> vpsDetails = new List<VPatchStation>();
            using (SQLiteDataReader reader = SQLiteHelper.ExecuteReader(sql, parameters.ToArray()))
            {
                while (reader.Read())
                {
                    VPatchStation patchDetail = new VPatchStation
                    {
                        PatchId = reader.GetString(reader.GetOrdinal("patch_id")),                     
                        StationId = reader.GetString(reader.GetOrdinal("station_id")),
                        TotalCount = reader.GetInt32(reader.GetOrdinal("total_count")),
                        ErrorCount = reader.GetInt32(reader.GetOrdinal("error_count")),
                        EqualCount = reader.GetInt32(reader.GetOrdinal("equal_count")),
                        InvalidCount = reader.GetInt32(reader.GetOrdinal("invalid_count"))
                    };
                    vpsDetails.Add(patchDetail);
                }
            }

            // 获取总记录数，用于计算总页数
            string countSql = "SELECT COUNT(*) FROM v_patch_detail_station WHERE patch_id = @PatchId OR patch_id = ''";
            int totalRecords = Convert.ToInt32(SQLiteHelper.GetSingle(countSql, parameters.ToArray()));

            // 计算总页数
            int totalPages = (int)Math.Ceiling((double)totalRecords / pageSize);

            // 返回总记录数、总页数和分页结果
            return Tuple.Create(totalRecords, totalPages, vpsDetails);
        }

        public static VPatchStation GetPatchStation(string patchId,string stationId)
        {
            VPatchStation vps = null;

            // 查询 SQL，从 v_patch 视图中获取数据
            string query = @"
            SELECT patch_id, station_id, total_count, error_count,equal_count, invalid_count
            FROM v_patch_detail_station
            WHERE patch_id = @PatchId AND station_id=@StationId;";

            // 构造参数
            SQLiteParameter[] parameters = new SQLiteParameter[]
            {
                new SQLiteParameter("@PatchId", patchId),
                new SQLiteParameter("@StationId", stationId),
            };

            // 执行查询
            using (SQLiteDataReader reader = SQLiteHelper.ExecuteReader(query, parameters))
            {
                if (reader.Read())
                {
                    vps = new VPatchStation
                    {
                        PatchId = reader["patch_id"].ToString(),
                        StationId = reader["station_id"].ToString(),
                        TotalCount = reader["total_count"] == DBNull.Value ? 0 : Convert.ToInt32(reader["total_count"]),
                        EqualCount = reader["equal_count"] == DBNull.Value ? 0 : Convert.ToInt32(reader["equal_count"]),
                        ErrorCount = reader["error_count"] == DBNull.Value ? 0 : Convert.ToInt32(reader["error_count"]),
                        InvalidCount = reader["invalid_count"] == DBNull.Value ? 0 : Convert.ToInt32(reader["invalid_count"])
                    };
                }
            }

            return vps;
        }




        //获取所有的识别任务
        public static List<VPatch> GetAllVPatchs()
        {
            // 定义 SQL 查询语句，带有分页
            string sql = @"
            SELECT 
                patch_id, 
                create_time, 
                patch_name, 
                stand_id,
                stand_name,
                detail_count, 
                run_count, 
                equal_count
            FROM v_patch WHERE detail_count  = run_count
            ORDER BY create_time DESC";

            // 执行查询并获取结果
            List<VPatch> patchs = new List<VPatch>();
            using (SQLiteDataReader reader = SQLiteHelper.ExecuteReader(sql))
            {
                while (reader.Read())
                {
                    // 将查询结果映射到 TStand 对象
                    VPatch patch = new VPatch
                    {
                        PatchId = reader["patch_id"].ToString(),
                        CreateTime = reader["create_time"].ToString(),
                        PatchName = reader["patch_name"].ToString(),
                        StandId = reader["stand_id"].ToString(),
                        StandName = reader["stand_name"].ToString(),
                        DetailCount = reader["detail_count"] == DBNull.Value ? 0 : Convert.ToInt32(reader["detail_count"]),
                        RunCount = reader["run_count"] == DBNull.Value ? 0 : Convert.ToInt32(reader["run_count"]),
                        EqualCount = reader["equal_count"] == DBNull.Value ? 0 : Convert.ToInt32(reader["equal_count"])
                    };
                    patchs.Add(patch);
                }
            }

            return patchs;

        }



        public static List<TPatchDetail> GetPatchDetailsWithRunFlagZero(string patchId)
        {
            // 查询 SQL
            //string sql = @"
            //    SELECT patch_detail_id, patch_id, create_time, stand_detail_id, stand_value, src_image, 
            //           run_flag, run_time, dst_image, meter_type, digit_count, pointer_count, 
            //           last_unit, result_type, raw_value, final_value, equal_flag, ai_ver, debug_info, log_path
            //    FROM t_patch_detail 
            //    WHERE patch_id = @PatchId AND run_flag = 0
            //    ORDER BY create_time AESC;"; // 可以根据需要修改排序字段

            //string sql = @"
            //    SELECT patch_detail_id, patch_id, create_time, stand_detail_id, stand_value, src_image 
            //    FROM t_patch_detail 
            //    WHERE patch_id = @PatchId AND run_flag = 0
            //    ORDER BY create_time ASC;"; // 可以根据需要修改排序字段

            string sql = $@"
                SELECT * FROM t_patch_detail
                WHERE patch_id = @PatchId AND run_flag = 0
                ORDER BY station_id,sample_time ASC";

            // 定义参数
            SQLiteParameter[] parameters = new SQLiteParameter[]
            {
                new SQLiteParameter("@PatchId", patchId)
            };

            // 执行查询并读取数据
            List<TPatchDetail> patchDetails = new List<TPatchDetail>();
            using (SQLiteDataReader reader = SQLiteHelper.ExecuteReader(sql, parameters))
            {
                while (reader.Read())
                {
                    // 将查询结果映射到 TPatchDetail 对象
                    TPatchDetail patchDetail = new TPatchDetail
                    {
                        PatchDetailId = reader.GetString(reader.GetOrdinal("patch_detail_id")),
                        PatchId = reader.GetString(reader.GetOrdinal("patch_id")),
                        CreateTime = reader.GetString(reader.GetOrdinal("create_time")),
                        StandDetailId = reader.GetString(reader.GetOrdinal("stand_detail_id")),
                        StandValue = reader.GetString(reader.GetOrdinal("stand_value")),
                        SrcImage = reader.GetString(reader.GetOrdinal("src_image")),
                        SampleTime = reader.GetString(reader.GetOrdinal("sample_time")),
                        RunFlag = reader.GetInt32(reader.GetOrdinal("run_flag")),
                        RunTime = reader.GetString(reader.GetOrdinal("run_time")),
                        DstImage = reader.GetString(reader.GetOrdinal("dst_image")),
                        ResultType = reader.GetInt32(reader.GetOrdinal("result_type")),
                        RawValue = reader.GetInt64(reader.GetOrdinal("raw_value")),
                        FinalValue = reader.GetInt64(reader.GetOrdinal("final_value")),
                        CompleteValue = reader.GetInt64(reader.GetOrdinal("complete_value")),
                        ValueChanged = reader.GetInt32(reader.GetOrdinal("value_changed")),
                        ResultMeter = reader.GetInt32(reader.GetOrdinal("result_meter")),
                        EqualFlag = reader.GetInt32(reader.GetOrdinal("equal_flag")),
                        AiVer = reader.GetString(reader.GetOrdinal("ai_ver")),
                        DebugInfo = reader.GetString(reader.GetOrdinal("debug_info")),
                        LogPath = reader.GetString(reader.GetOrdinal("log_path")),
                        Memo = reader.GetString(reader.GetOrdinal("memo")),
                        StationKey = reader.GetString(reader.GetOrdinal("station_key")),
                        StationId = reader.GetString(reader.GetOrdinal("station_id")),
                        MeterType = reader.GetInt32(reader.GetOrdinal("meter_type")),
                        BrightVal = reader.GetDouble(reader.GetOrdinal("bright_val")),
                        FlowRate = reader.GetInt32(reader.GetOrdinal("flow_rate")),
                        DigitCount = reader.GetInt32(reader.GetOrdinal("digit_count")),
                        PointerCount = reader.GetInt32(reader.GetOrdinal("pointer_count")),
                        LastUnit = reader.GetDouble(reader.GetOrdinal("last_unit")),
                        MeterRegion = reader.GetString(reader.GetOrdinal("meter_region")),
                        FeatureRegion = reader.GetString(reader.GetOrdinal("feature_region")),
                        LastCompress = reader.GetInt32(reader.GetOrdinal("last_compress")),
                        LatestValue = reader.GetInt64(reader.GetOrdinal("latest_value")),
                        LatestTime = reader.GetString(reader.GetOrdinal("latest_time")),
                        LatestComplete = reader.GetInt64(reader.GetOrdinal("latest_complete"))
                    };
                    patchDetails.Add(patchDetail);
                }
            }

            return patchDetails;
        }


        public static int GetPatchDetailsCountWithRunFlagZero(string patchId)
        {
            // 查询数据数量的 SQL
            string sql = "SELECT COUNT(*) FROM t_patch_detail WHERE patch_id = @PatchId AND run_flag = 0";

            // 定义参数
            SQLiteParameter[] parameters = new SQLiteParameter[]
            {
                new SQLiteParameter("@PatchId", patchId)
            };

            // 执行查询并返回数据数量
            return Convert.ToInt32(SQLiteHelper.GetSingle(sql, parameters));
        }


        /// <summary>
        /// 根据 patch_detail_id 删除指定的记录
        /// </summary>
        /// <param name="patchDetailId">要删除的记录的 ID</param>
        /// <returns>删除成功返回 true，否则返回 false</returns>
        public static bool DeleteTPatchDetailById(string patchDetailId)
        {
            // 构建删除的 SQL 语句
            string sql = "DELETE FROM t_patch_detail WHERE patch_detail_id = @PatchDetailId";

            // 创建参数数组
            SQLiteParameter[] parameters = new SQLiteParameter[]
            {
                new SQLiteParameter("@PatchDetailId", patchDetailId)
            };

            try
            {
                // 调用 SQLiteHelper 执行删除操作
                int rowsAffected = SQLiteHelper.ExecuteSql(sql, parameters);

                // 如果删除成功，返回 true，否则返回 false
                return rowsAffected > 0;
            }
            catch (Exception ex)
            {
                // 处理异常（如果有的话）
                Console.WriteLine("Error deleting data: " + ex.Message);
                return false;
            }
        }

        /// <summary>
        /// 根据 standDetailId 删除t_patch_detail与t_stand_detail中相关的数据
        /// </summary>
        /// <param name="standDetailId">要删除的记录的 模板详情ID</param>
        /// <returns>删除成功返回 true，否则返回 false</returns>
        public static bool Delete_TPatchDetail_TStandDetail_ByStand(string standDetailId)
        {
            // 构建删除的 SQL 语句
            //string sql = "DELETE FROM t_patch_detail WHERE patch_detail_id = @PatchDetailId";

            // 定义删除的 SQL 语句，使用事务来保证一致性
            string sql = @"
                BEGIN TRANSACTION;

                -- 删除 t_patch_detail 表中与 stand_id 相关的数据
                DELETE FROM t_patch_detail WHERE stand_detail_id = @StandDetailId;

                -- 删除 t_patch 表中与 patch_id 相关的数据
                DELETE FROM t_stand_detail WHERE stand_detail_id = @StandDetailId;

                COMMIT;";

            // 创建参数数组
            SQLiteParameter[] parameters = new SQLiteParameter[]
            {
                new SQLiteParameter("@StandDetailId", standDetailId)
            };

            try
            {
                // 调用 SQLiteHelper 执行删除操作
                int rowsAffected = SQLiteHelper.ExecuteSql(sql, parameters);

                // 如果删除成功，返回 true，否则返回 false
                return rowsAffected > 0;
            }
            catch (Exception ex)
            {
                // 处理异常（如果有的话）
                Console.WriteLine("Error deleting data: " + ex.Message);
                return false;
            }
        }

        public static bool UpdatePatchName(string patchId, string patchName)
        {
            string sql = @"
                UPDATE t_patch                 
                SET patch_name = @PatchName
                WHERE patch_id = @PatchId;";

            // 创建 SQL 参数
            SQLiteParameter[] parameters = new SQLiteParameter[]
            {
                new SQLiteParameter("@PatchName", patchName),
                new SQLiteParameter("@PatchId", patchId)
            };

            // 执行更新操作并返回受影响的行数
            return SQLiteHelper.ExecuteNonQuery(sql, parameters) > 0;
        }

        public static bool UpdatePatchDetailMemo(string detailId,string memo)
        {
            // 定义 SQL 更新语句
            string sql = @"
                UPDATE t_patch_detail                 
                SET memo = @Memo
                WHERE patch_detail_id = @PatchDetailId;";

            // 创建 SQL 参数
            SQLiteParameter[] parameters = new SQLiteParameter[]
            {
                new SQLiteParameter("@PatchDetailId", detailId),
                new SQLiteParameter("@Memo", memo)
            };

            // 执行更新操作并返回受影响的行数
            return SQLiteHelper.ExecuteNonQuery(sql, parameters) > 0;
        }


        //升级数据库(t_patch_detail添加memo字段)
        public static void UpdateTPatchDetailSchema()
        {
            string sql = "PRAGMA table_info(t_patch_detail)";
            // 查询表结构，判断是否存在memo字段
            using (SQLiteDataReader reader = SQLiteHelper.ExecuteReader(sql))
            {
                bool memoExists = false;
                while (reader.Read())
                {
                    if(reader["name"].ToString().ToLower() == "memo")
                    {
                        memoExists = true;
                        break;
                    }
                }
                reader.Close();

                //如果不存在memo字段,则添加该字段
                if (!memoExists)
                {
                    sql = "ALTER TABLE t_patch_detail ADD COLUMN memo TEXT NOT NULL DEFAULT ''";
                    SQLiteHelper.ExecuteNonQuery(sql, null);
                }
            }//using
        }
       
        //////////////////////////////////////////////////////////
    }
}