MeterVision_http_v3/MeterVision/db/DBSingle.cs

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 DBSingle
    {
        public static bool InsertTSingleDetail0(TSingleDetail singlDetail)
        {
            // 构建插入的 SQL 语句
            string sql = "INSERT INTO t_single_detail (single_detail_id, create_time, src_image, device_sn, sample_time) " +
                         "VALUES (@SingleDetailId, @CreateTime, @SrcImage, @DeviceSn, @SampleTime)";

            // 创建参数数组
            SQLiteParameter[] parameters = new SQLiteParameter[]
            {
                new SQLiteParameter("@SingleDetailId", singlDetail.SingleDetailId),
                new SQLiteParameter("@CreateTime", singlDetail.CreateTime),
                new SQLiteParameter("@SrcImage", singlDetail.SrcImage),
                new SQLiteParameter("@DeviceSn", singlDetail.DeviceSn),
                new SQLiteParameter("@SampleTime", singlDetail.SampleTime)
            };

            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 InsertSingleDetail(TSingleDetail detail)
        {
            // 定义 SQL 插入语句
            string sql = @"
                INSERT INTO t_single_detail (
                    single_detail_id, create_time, src_image, run_flag, run_time,
                    dst_image, meter_type, digit_count, pointer_count, last_unit,
                    result_type, raw_value, final_value, ai_ver, debug_info, log_path, memo
                )
                VALUES (
                    @SingleDetailId, @CreateTime, @SrcImage, @RunFlag, @RunTime,
                    @DstImage, @MeterType, @DigitCount, @PointerCount, @LastUnit,
                    @ResultType, @RawValue, @FinalValue, @AiVer, @DebugInfo, @LogPath, @Memo
                );
            ";

            // 创建 SQL 参数
            SQLiteParameter[] parameters = new SQLiteParameter[]
            {
                new SQLiteParameter("@SingleDetailId", detail.SingleDetailId),
                new SQLiteParameter("@CreateTime", detail.CreateTime),
                new SQLiteParameter("@SrcImage", detail.SrcImage),
                new SQLiteParameter("@RunFlag", detail.RunFlag),
                new SQLiteParameter("@RunTime", detail.RunTime),
                new SQLiteParameter("@DstImage", detail.DstImage),
                new SQLiteParameter("@MeterType", detail.MeterType),
                new SQLiteParameter("@DigitCount", detail.DigitCount),
                new SQLiteParameter("@PointerCount", detail.PointerCount),
                new SQLiteParameter("@LastUnit", detail.LastUnit),
                new SQLiteParameter("@ResultType", detail.ResultType),
                new SQLiteParameter("@RawValue", detail.RawValue),
                new SQLiteParameter("@FinalValue", detail.FinalValue),
                new SQLiteParameter("@AiVer", detail.AiVer),
                new SQLiteParameter("@DebugInfo", detail.DebugInfo),
                new SQLiteParameter("@LogPath", detail.LogPath),
                new SQLiteParameter("@Memo",detail.Memo)
            };

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

        public static bool UpdateSingleDetailByConfig(TSingleDetail detail)
        {
            // 定义 SQL 更新语句
            string sql = @"
                UPDATE t_single_detail                 
                SET meter_type = @MeterType,
                    digit_count = @DigitCount,
                    pointer_count = @PointerCount,
                    last_unit = @LastUnit,
                    num_in_upper = @NumInUpper,
                    bright_val = @BrightVal,
                    flow_rate = @FlowRate,
                    meter_region = @MeterRegion,
                    feature_region = @FeatureRegion,
                    last_value = @LastValue,
                    last_time = @LastTime
                WHERE single_detail_id = @SingleDetailId;";

            // 创建 SQL 参数
            SQLiteParameter[] parameters = new SQLiteParameter[]
            {
                new SQLiteParameter("@SingleDetailId", detail.SingleDetailId),
                new SQLiteParameter("@MeterType", detail.MeterType),
                new SQLiteParameter("@DigitCount", detail.DigitCount),
                new SQLiteParameter("@PointerCount", detail.PointerCount),
                new SQLiteParameter("@LastUnit", detail.LastUnit),
                new SQLiteParameter("@NumInUpper",detail.NumInUpper),
                new SQLiteParameter("@BrightVal", detail.BrightVal),
                new SQLiteParameter("@FlowRate", detail.FlowRate),
                new SQLiteParameter("@MeterRegion", detail.MeterRegion),
                new SQLiteParameter("@FeatureRegion", detail.FeatureRegion),
                new SQLiteParameter("@LastValue", detail.LastValue),
                new SQLiteParameter("@LastTime", detail.LastTime)
            };

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


        //识别结果引发的更改
        public static bool UpdateSingleDetailByResult(TSingleDetail detail)
        {
            // 定义 SQL 更新语句
            string sql = @"
                UPDATE t_single_detail                 
                SET run_flag = @RunFlag,
                    run_time = @RunTime,
                    dst_image = @DstImage,
                    result_type = @ResultType,
                    raw_value = @RawValue,
                    final_value = @FinalValue,
                    complete_value = @CompleteValue,
                    result_meter = @ResultMeter,
                    ai_ver = @AiVer,
                    debug_info = @DebugInfo,
                    log_path = @LogPath,
                    memo = @Memo,
                    meter_type = @MeterType,
                    last_unit = @LastUnit
                WHERE single_detail_id = @SingleDetailId;";

            // 创建 SQL 参数
            SQLiteParameter[] parameters = new SQLiteParameter[]
            {
                new SQLiteParameter("@SingleDetailId", detail.SingleDetailId),
                new SQLiteParameter("@RunFlag", detail.RunFlag),
                new SQLiteParameter("@RunTime", detail.RunTime),
                new SQLiteParameter("@DstImage", detail.DstImage),
                new SQLiteParameter("@LastUnit", detail.LastUnit),
                new SQLiteParameter("@ResultType", detail.ResultType),
                new SQLiteParameter("@RawValue", detail.RawValue),
                new SQLiteParameter("@FinalValue", detail.FinalValue),
                new SQLiteParameter("@CompleteValue",detail.CompleteValue),
                new SQLiteParameter("@ResultMeter", detail.ResultMeter),
                new SQLiteParameter("@AiVer", detail.AiVer),
                new SQLiteParameter("@DebugInfo", detail.DebugInfo),
                new SQLiteParameter("@LogPath", detail.LogPath),
                new SQLiteParameter("@Memo",detail.Memo),
                new SQLiteParameter("@MeterType",detail.MeterType),
                new SQLiteParameter("@LastUnit",detail.LastUnit)
            };

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

        //更改备注信息
        public static bool UpdateSingleDetailMemo(string detailId,string memo)
        {
            // 定义 SQL 更新语句
            string sql = @"
                UPDATE t_single_detail                 
                SET memo = @Memo
                WHERE single_detail_id = @SingleDetailId;";

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

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


        public static Tuple<int,int, List<TSingleDetail>> GetPagedSingleDetails(int pageNumber, int pageSize)
        {
            // 计算 OFFSET 值
            int offset = (pageNumber - 1) * pageSize;

            // 定义 SQL 查询语句，带有分页
            string sql = @"
                SELECT single_detail_id, create_time, src_image, run_flag, run_time,
                       dst_image, meter_type, digit_count, pointer_count, last_unit,num_in_upper,
                       result_type, raw_value, final_value, complete_value, result_meter,ai_ver, 
                       debug_info, log_path, memo,device_sn, sample_time,
                       bright_val,flow_rate,meter_region,feature_region,last_value,last_time
                FROM t_single_detail
                ORDER BY create_time DESC
                LIMIT @PageSize OFFSET @Offset;";

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

            // 执行查询并获取结果
            List<TSingleDetail> details = new List<TSingleDetail>();
            using (SQLiteDataReader reader = SQLiteHelper.ExecuteReader(sql, parameters))
            {
                while (reader.Read())
                {
                    // 将查询结果映射到 TSingleDetail 对象
                    TSingleDetail detail = new TSingleDetail
                    {
                        SingleDetailId = reader.GetString(reader.GetOrdinal("single_detail_id")),
                        CreateTime = reader.GetString(reader.GetOrdinal("create_time")),
                        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")),
                        NumInUpper = reader.GetInt32(reader.GetOrdinal("num_in_upper")),
                        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")),
                        ResultMeter = reader.GetInt32(reader.GetOrdinal("result_meter")),
                        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")),
                        DeviceSn = reader.GetString(reader.GetOrdinal("device_sn")),
                        SampleTime = reader.GetString(reader.GetOrdinal("sample_time")),
                        BrightVal = reader.GetDouble(reader.GetOrdinal("bright_val")),
                        FlowRate = reader.GetInt32(reader.GetOrdinal("flow_rate")),
                        MeterRegion = reader.GetString(reader.GetOrdinal("meter_region")),
                        FeatureRegion = reader.GetString(reader.GetOrdinal("feature_region")),
                        LastValue = reader.GetDouble(reader.GetOrdinal("last_value")),
                        LastTime = reader.GetString(reader.GetOrdinal("last_time"))
                    };
                    details.Add(detail);
                }
            }

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

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

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


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

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

            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>
        /// 清空 t_single_detail 表中的所有记录
        /// </summary>
        /// <returns>操作成功返回 true，否则返回 false</returns>
        public static bool ClearTSingleDetailTable()
        {
            // 构建清空表的 SQL 语句
            string sql = "DELETE  FROM t_single_detail";

            try
            {
                // 调用 SQLiteHelper 执行清空操作
                SQLiteHelper.ExecuteSql(sql, null);

                // 返回 true 表示操作成功
                return true;
            }
            catch (Exception ex)
            {
                // 处理异常（如果有的话）
                Console.WriteLine("Error clearing table: " + ex.Message);
                return false;
            }
        }


        //升级数据库(t_patch_detail添加memo字段)
        public static void UpdateTSingleDetailSchema()
        {
            string sql = "PRAGMA table_info(t_single_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_single_detail ADD COLUMN memo TEXT NOT NULL DEFAULT ''";
                    SQLiteHelper.ExecuteNonQuery(sql, null);
                }
            }//using
        }


        //-----------------------------------------------------------------------------------------
    }
}