MeterVision_v3/MeterVision/db/DBStand.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 DBStand
    {
        public static bool FreeDatabase()
        {
            // 构建删除的 SQL 语句
            string sql = "VACUUM;";

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

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


        public static bool InsertTStand(TStand tStand)
        {
            // 构建插入的 SQL 语句
            string sql = "INSERT INTO t_stand (stand_id, create_time, stand_name, stand_type, stand_file) " +
                         "VALUES (@StandId, @CreateTime, @StandName,@StandType,@StandFile)";

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

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

                -- 删除 t_stand_detail 表中与 stand_id 相关的数据
                DELETE FROM t_stand_detail WHERE stand_id = @StandId;

                -- 删除 t_stand 表中与 stand_id 相关的数据
                DELETE FROM t_stand WHERE stand_id = @StandId;

                -- 删除 t_station 表中与 stand_id 相关的数据
                DELETE FROM t_station WHERE stand_id = @StandId;

                COMMIT;";

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

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

            // 定义 SQL 查询语句，带有分页
            string sql = @"
                SELECT stand_id, create_time, stand_name,stand_type,stand_file,stand_count
                FROM v_stand ORDER BY create_time DESC
                LIMIT @PageSize OFFSET @Offset;";

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

            // 执行查询并获取结果
            List<VStand> stands = new List<VStand>();
            using (SQLiteDataReader reader = SQLiteHelper.ExecuteReader(sql, parameters))
            {
                while (reader.Read())
                {
                    // 将查询结果映射到 TStand 对象
                    VStand stand = new VStand
                    {
                        StandId = reader.GetString(reader.GetOrdinal("stand_id")),
                        CreateTime = reader.GetString(reader.GetOrdinal("create_time")),
                        StandName = reader.GetString(reader.GetOrdinal("stand_name")),
                        StandType = reader.GetInt32(reader.GetOrdinal("stand_type")),
                        StandFile = reader.GetString(reader.GetOrdinal("stand_file")),
                        StandCount = reader.GetInt32(reader.GetOrdinal("stand_count"))
                    };
                    stands.Add(stand);
                }
            }

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

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

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

        public static List<VStand> GetAllVStands()
        {
            // 定义 SQL 查询语句，带有分页
            string sql = @"
                SELECT stand_id, create_time, stand_name,stand_type,stand_file,stand_count
                FROM v_stand WHERE stand_count>0 ORDER BY create_time DESC";


            // 执行查询并获取结果
            List<VStand> stands = new List<VStand>();
            using (SQLiteDataReader reader = SQLiteHelper.ExecuteReader(sql))
            {
                while (reader.Read())
                {
                    // 将查询结果映射到 TStand 对象
                    VStand stand = new VStand
                    {
                        StandId = reader.GetString(reader.GetOrdinal("stand_id")),
                        CreateTime = reader.GetString(reader.GetOrdinal("create_time")),
                        StandName = reader.GetString(reader.GetOrdinal("stand_name")),
                        StandType = reader.GetInt32(reader.GetOrdinal("stand_type")),
                        StandFile = reader.GetString(reader.GetOrdinal("stand_file")),
                        StandCount = reader.GetInt32(reader.GetOrdinal("stand_count")),
                    };
                    stands.Add(stand);
                }
            }

            return stands;
        }


        public static bool InsertStandDetail(TStandDetail standDetail)
        {
            // 插入SQL语句
            string sql = @"
                INSERT OR IGNORE INTO t_stand_detail (stand_detail_id, create_time, stand_id, src_image, stand_value,
                station_id, device_sn,sample_time, num_count, last_unit)
                VALUES (@StandDetailId, @CreateTime, @StandId, @SrcImage, @StandValue, 
                @StationId, @DeviceSn, @SampleTime, @NumCount, @LastUnit);";

            // 定义参数
            SQLiteParameter[] parameters = new SQLiteParameter[]
            {
                new SQLiteParameter("@StandDetailId", standDetail.StandDetailId),
                new SQLiteParameter("@CreateTime", standDetail.CreateTime),
                new SQLiteParameter("@StandId", standDetail.StandId),
                new SQLiteParameter("@SrcImage", standDetail.SrcImage),
                new SQLiteParameter("@StandValue", standDetail.StandValue),
                new SQLiteParameter("@StationId", standDetail.StationId),
                new SQLiteParameter("@DeviceSn",standDetail.DeviceSn),
                new SQLiteParameter("@SampleTime",standDetail.SampleTime),
                new SQLiteParameter("@NumCount", standDetail.NumCount),
                new SQLiteParameter("@LastUnit", standDetail.LastUnit)
            };

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

        //public static bool InsertStandDetails(List<TStandDetail> standDetails)
        //{
        //    // 插入SQL语句
        //    string sql = @"
        //        INSERT OR IGNORE INTO t_stand_detail (stand_detail_id, create_time, stand_id, src_image, stand_value,
        //        station_id, device_sn, sample_time, num_count, last_unit)
        //        VALUES (@StandDetailId, @CreateTime, @StandId, @SrcImage, @StandValue, 
        //        @StationId, ,@DeviceSn, @SampleTime, @NumCount, @LastUnit);";


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

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

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

        //同时插入t_stand与t_stand_detail表(在一个事务中)
        public static bool InsertStandAndDetails(TStand stand, List<TStandDetail> standDetails)
        {
            try
            {
                using (SQLiteConnection connection = SQLiteHelper.GetConnection())
                {
                    connection.Open();
                    using (SQLiteTransaction transaction = connection.BeginTransaction())
                    {
                        // 构建插入的 SQL 语句
                        string standSql = "INSERT INTO t_stand (stand_id, create_time, stand_name, stand_type, stand_file) " +
                                     "VALUES (@StandId, @CreateTime, @StandName,@StandType,@StandFile)";

                        SQLiteParameter[] standParams = new SQLiteParameter[]
                        {
                            new SQLiteParameter("@StandId", stand.StandId),
                            new SQLiteParameter("@CreateTime", stand.CreateTime),
                            new SQLiteParameter("@StandName", stand.StandName),
                            new SQLiteParameter("@StandType", stand.StandType),
                            new SQLiteParameter("@StandFile", stand.StandFile)
                        };
                        SQLiteHelper.ExecuteNonQuery(standSql, standParams, transaction);

                        // 插入 t_patch_detail 表
                        foreach (var standDetail in standDetails)
                        {
                            string detailSql = @"
                                INSERT OR IGNORE INTO t_stand_detail (stand_detail_id, create_time, stand_id, src_image, stand_value, 
                                station_id, device_sn,sample_time, e_num_count, e_last_unit)
                                VALUES (@StandDetailId, @CreateTime, @StandId, @SrcImage, @StandValue, 
                                @StationId, @DeviceSn ,@SampleTime, @ENumCount, @ELastUnit);";

                            SQLiteParameter[] detailParams = new SQLiteParameter[]
                            {
                                new SQLiteParameter("@StandDetailId", standDetail.StandDetailId),
                                new SQLiteParameter("@CreateTime", standDetail.CreateTime),
                                new SQLiteParameter("@StandId", standDetail.StandId),
                                new SQLiteParameter("@SrcImage", standDetail.SrcImage),
                                new SQLiteParameter("@StandValue", standDetail.StandValue),
                                new SQLiteParameter("@StationId", standDetail.StationId),
                                new SQLiteParameter("@DeviceSn", standDetail.DeviceSn),
                                new SQLiteParameter("@SampleTime",standDetail.SampleTime),
                                new SQLiteParameter("@ENumCount", standDetail.ENumCount),
                                new SQLiteParameter("@ELastUnit", standDetail.ELastUnit),
                            };
                            SQLiteHelper.ExecuteNonQuery(detailSql, detailParams, transaction);
                        }

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

        public static bool InsertStandDetails(List<TStandDetail> standDetails)
        {
            if (standDetails == null || standDetails.Count == 0)
            {
                return false; // 如果列表为空，直接返回
            }

            try
            {
                using (SQLiteConnection connection = SQLiteHelper.GetConnection())
                {
                    connection.Open();
                    using (SQLiteTransaction transaction = connection.BeginTransaction())
                    {
                        string detailSql = @"
                            INSERT OR IGNORE INTO t_stand_detail 
                            (stand_detail_id, create_time, stand_id, src_image, stand_value, 
                             station_id, device_sn, sample_time, e_num_count, e_last_unit, 
                             meter_type, bright_val, flow_rate, dial_region, num_count, 
                             ind_count, feature_region, last_unit, num_in_upper,last_value, last_time) 
                            VALUES 
                            (@StandDetailId, @CreateTime, @StandId, @SrcImage, @StandValue, 
                             @StationId, @DeviceSn, @SampleTime, @ENumCount, @ELastUnit, 
                             @MeterType, @BrightVal, @FlowRate, @DialRegion, @NumCount, 
                             @IndCount, @FeatureRegion, @LastUnit, @NumInUpper,@LastValue, @LastTime);";

                        foreach (var standDetail in standDetails)
                        {
                            SQLiteParameter[] detailParams = new SQLiteParameter[]
                            {
                                new SQLiteParameter("@StandDetailId", standDetail.StandDetailId),
                                new SQLiteParameter("@CreateTime", standDetail.CreateTime),
                                new SQLiteParameter("@StandId", standDetail.StandId),
                                new SQLiteParameter("@SrcImage", standDetail.SrcImage),
                                new SQLiteParameter("@StandValue", standDetail.StandValue),
                                new SQLiteParameter("@StationId", standDetail.StationId),
                                new SQLiteParameter("@DeviceSn", standDetail.DeviceSn),
                                new SQLiteParameter("@SampleTime", standDetail.SampleTime),
                                new SQLiteParameter("@ENumCount", standDetail.ENumCount),
                                new SQLiteParameter("@ELastUnit", standDetail.ELastUnit),
                                new SQLiteParameter("@MeterType", standDetail.MeterType),
                                new SQLiteParameter("@BrightVal", standDetail.BrightVal),
                                new SQLiteParameter("@FlowRate", standDetail.FlowRate),
                                new SQLiteParameter("@DialRegion", standDetail.DialRegion),
                                new SQLiteParameter("@NumCount", standDetail.NumCount),
                                new SQLiteParameter("@IndCount", standDetail.IndCount),
                                new SQLiteParameter("@FeatureRegion", standDetail.FeatureRegion),
                                new SQLiteParameter("@LastUnit", standDetail.LastUnit),
                                new SQLiteParameter("@NumInUpper",standDetail.NumInUpper),
                                new SQLiteParameter("@LastValue", standDetail.LastValue),
                                new SQLiteParameter("@LastTime", standDetail.LastTime)
                            };

                            SQLiteHelper.ExecuteNonQuery(detailSql, detailParams, transaction);
                        }

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



        public static Tuple<int,int, List<TStandDetail>> GetPagedStandDetails(int pageNumber, int pageSize,StationItem stationItem)
        {
            // 计算偏移量
            int offset = (pageNumber - 1) * pageSize;

            // 分页查询SQL
            //string sql = @"
            //    SELECT stand_detail_id, create_time, stand_id, src_image, stand_value, station_id, device_sn, sample_time, num_count, last_unit
            //    FROM t_stand_detail WHERE stand_id=@StandId AND station_id=@StationId
            //    ORDER BY sample_time ASC  -- 根据需要修改排序字段
            //    LIMIT @PageSize OFFSET @Offset;";
            string sql = @"
                SELECT *
                FROM t_stand_detail WHERE stand_id=@StandId AND station_id=@StationId
                ORDER BY sample_time ASC  -- 根据需要修改排序字段
                LIMIT @PageSize OFFSET @Offset;";
            // 定义参数
            SQLiteParameter[] parameters = new SQLiteParameter[]
            {
                new SQLiteParameter("@StandId",stationItem.StandId),
                new SQLiteParameter("@StationId", stationItem.StationId),
                new SQLiteParameter("@PageSize", pageSize),
                new SQLiteParameter("@Offset", offset)
            };

            // 执行查询并读取数据
            List<TStandDetail> standDetails = new List<TStandDetail>();
            using (SQLiteDataReader reader = SQLiteHelper.ExecuteReader(sql, parameters))
            {
                while (reader.Read())
                {
                    // 将查询结果映射到 TStandDetail 对象
                    TStandDetail standDetail = new TStandDetail
                    {
                        StandDetailId = reader.GetString(reader.GetOrdinal("stand_detail_id")),
                        CreateTime = reader.GetString(reader.GetOrdinal("create_time")),
                        StandId = reader.GetString(reader.GetOrdinal("stand_id")),
                        SrcImage = reader.GetString(reader.GetOrdinal("src_image")),
                        StandValue = reader.GetString(reader.GetOrdinal("stand_value")),
                        StationId = reader.GetString(reader.GetOrdinal("station_id")),
                        DeviceSn = reader.GetString(reader.GetOrdinal("device_sn")),
                        SampleTime = reader.GetString(reader.GetOrdinal("sample_time")),
                        ENumCount = reader.GetInt32(reader.GetOrdinal("e_num_count")),
                        ELastUnit = reader.GetInt32(reader.GetOrdinal("e_last_unit")),
                        //新加的项目
                        MeterType = reader.GetInt32(reader.GetOrdinal("meter_type")),
                        BrightVal = reader.GetDouble(reader.GetOrdinal("bright_val")),
                        FlowRate = reader.GetInt32(reader.GetOrdinal("flow_rate")),
                        DialRegion = reader.GetString(reader.GetOrdinal("dial_region")),
                        NumCount = reader.GetInt32(reader.GetOrdinal("num_count")),
                        IndCount = reader.GetInt32(reader.GetOrdinal("ind_count")),
                        FeatureRegion = reader.GetString(reader.GetOrdinal("feature_region")),
                        LastUnit = reader.GetDouble(reader.GetOrdinal("last_unit")),
                        NumInUpper = reader.GetInt32(reader.GetOrdinal("num_in_upper")),
                        LastValue = reader.GetDouble(reader.GetOrdinal("last_value")),
                        LastTime = reader.GetString(reader.GetOrdinal("last_time"))
                    };
                    standDetails.Add(standDetail);
                }
            }

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

            string countSql = "SELECT COUNT(*) FROM t_stand_detail WHERE stand_id = @StandId AND station_id = @StationId";
            //int totalRecords = Convert.ToInt32(SQLiteHelper.GetSingle(countSql, new SQLiteParameter("@StandId", standId)));

            SQLiteParameter[] parameters2 = new SQLiteParameter[]{
                new SQLiteParameter("@StandId",stationItem.StandId),
                new SQLiteParameter("@StationId", stationItem.StationId),
            };
            int totalRecords = Convert.ToInt32(SQLiteHelper.GetSingle(countSql, parameters2));

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

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

        public static List<TStandDetail> GetAllStandDetails(string standId)
        {
            // 分页查询 SQL
            string sql = @"
                SELECT stand_detail_id, create_time, stand_id, src_image, stand_value, station_id, device_sn, sample_time, num_count, last_unit
                FROM t_stand_detail WHERE stand_id = @StandId
                ORDER BY create_time DESC";

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

            // 执行查询并读取数据
            List<TStandDetail> standDetails = new List<TStandDetail>();
            try
            {
                using (SQLiteDataReader reader = SQLiteHelper.ExecuteReader(sql, parameters))
                {
                    while (reader.Read())
                    {
                        // 将查询结果映射到 TStandDetail 对象
                        var standDetail = new TStandDetail
                        {
                            StandDetailId = reader.GetString(reader.GetOrdinal("stand_detail_id")),
                            CreateTime = reader.GetString(reader.GetOrdinal("create_time")),
                            StandId = reader.GetString(reader.GetOrdinal("stand_id")),
                            SrcImage = reader.GetString(reader.GetOrdinal("src_image")),
                            StandValue = reader.GetString(reader.GetOrdinal("stand_value")),
                            StationId = reader.GetString(reader.GetOrdinal("station_id")),
                            DeviceSn = reader.GetString(reader.GetOrdinal("device_sn")),
                            SampleTime = reader.GetString(reader.GetOrdinal("sample_time")),
                            ENumCount = reader.GetInt32(reader.GetOrdinal("e_num_count")),
                            ELastUnit = reader.GetInt32(reader.GetOrdinal("e_last_unit")),

                            //新加的项目
                            MeterType = reader.GetInt32(reader.GetOrdinal("meter_type")),
                            BrightVal = reader.GetDouble(reader.GetOrdinal("bright_val")),
                            FlowRate = reader.GetInt32(reader.GetOrdinal("flow_rate")),
                            DialRegion = reader.GetString(reader.GetOrdinal("dial_region")),
                            NumCount = reader.GetInt32(reader.GetOrdinal("num_count")),
                            IndCount = reader.GetInt32(reader.GetOrdinal("ind_count")),
                            FeatureRegion = reader.GetString(reader.GetOrdinal("feature_region")),
                            LastUnit = reader.GetDouble(reader.GetOrdinal("last_unit")),
                            LastValue = reader.GetDouble(reader.GetOrdinal("last_value")),
                            LastTime = reader.GetString(reader.GetOrdinal("last_time"))
                        };
                        standDetails.Add(standDetail);
                    }
                }
            }
            catch (Exception ex)
            {
                // 根据需要记录日志或抛出异常
                throw new Exception($"查询 StandDetails 时发生错误：{ex.Message}", ex);
            }

            return standDetails;
        }

        public static List<TStandDetail> GetAllStandDetails(string standId,string stationId)
        {
            // 分页查询 SQL
            //string sql = @"
            //    SELECT stand_detail_id, create_time, stand_id, src_image, stand_value, station_id, device_sn, sample_time, num_count, last_unit
            //    FROM t_stand_detail WHERE stand_id = @StandId AND station_id = @StationId AND sample_time <> '' 
            //    ORDER BY sample_time ASC";

            string sql = @"
                SELECT *
                FROM t_stand_detail WHERE stand_id = @StandId AND station_id = @StationId AND sample_time <> '' 
                ORDER BY sample_time ASC";

            // 定义参数
            SQLiteParameter[] parameters = new SQLiteParameter[]
            {
                new SQLiteParameter("@StandId", standId),
                new SQLiteParameter("@StationId",stationId)
            };

            // 执行查询并读取数据
            List<TStandDetail> standDetails = new List<TStandDetail>();
            try
            {
                using (SQLiteDataReader reader = SQLiteHelper.ExecuteReader(sql, parameters))
                {
                    while (reader.Read())
                    {
                        // 将查询结果映射到 TStandDetail 对象
                        var standDetail = new TStandDetail
                        {
                            StandDetailId = reader.GetString(reader.GetOrdinal("stand_detail_id")),
                            CreateTime = reader.GetString(reader.GetOrdinal("create_time")),
                            StandId = reader.GetString(reader.GetOrdinal("stand_id")),
                            SrcImage = reader.GetString(reader.GetOrdinal("src_image")),
                            StandValue = reader.GetString(reader.GetOrdinal("stand_value")),
                            StationId = reader.GetString(reader.GetOrdinal("station_id")),
                            DeviceSn = reader.GetString(reader.GetOrdinal("device_sn")),
                            SampleTime = reader.GetString(reader.GetOrdinal("sample_time")),
                            ENumCount = reader.GetInt32(reader.GetOrdinal("e_num_count")),
                            ELastUnit = reader.GetInt32(reader.GetOrdinal("e_last_unit")),

                            //新加的项目
                            MeterType = reader.GetInt32(reader.GetOrdinal("meter_type")),
                            BrightVal = reader.GetDouble(reader.GetOrdinal("bright_val")),
                            FlowRate = reader.GetInt32(reader.GetOrdinal("flow_rate")),
                            DialRegion = reader.GetString(reader.GetOrdinal("dial_region")),
                            NumCount = reader.GetInt32(reader.GetOrdinal("num_count")),
                            IndCount = reader.GetInt32(reader.GetOrdinal("ind_count")),
                            FeatureRegion = reader.GetString(reader.GetOrdinal("feature_region")),
                            LastUnit = reader.GetDouble(reader.GetOrdinal("last_unit")),
                            NumInUpper = reader.GetInt32(reader.GetOrdinal("num_in_upper")),
                            LastValue = reader.GetDouble(reader.GetOrdinal("last_value")),
                            LastTime = reader.GetString(reader.GetOrdinal("last_time"))
                        };
                        standDetails.Add(standDetail);
                    }
                }
            }
            catch (Exception ex)
            {
                // 根据需要记录日志或抛出异常
                throw new Exception($"查询 StandDetails 时发生错误：{ex.Message}", ex);
            }

            return standDetails;
        }

        public static TStandDetail GetMinSampleTimeDetail(string standId, string stationId)
        {
            // 查询 sample_time 最小的符合条件的记录
            //string sql = @"
            //    SELECT stand_detail_id, create_time, stand_id, src_image, stand_value, station_id, device_sn, sample_time, num_count, last_unit
            //    FROM t_stand_detail
            //    WHERE stand_id = @StandId AND station_id = @StationId
            //    ORDER BY sample_time ASC
            //    LIMIT 1";
            string sql = @"
                SELECT *
                FROM t_stand_detail
                WHERE stand_id = @StandId AND station_id = @StationId
                ORDER BY sample_time ASC
                LIMIT 1";
            // 定义参数
            SQLiteParameter[] parameters = new SQLiteParameter[]
            {
                new SQLiteParameter("@StandId", standId),
                new SQLiteParameter("@StationId", stationId)
            };

            // 执行查询并读取数据
            try
            {
                using (SQLiteDataReader reader = SQLiteHelper.ExecuteReader(sql, parameters))
                {
                    if (reader.Read())
                    {
                        return new TStandDetail
                        {
                            StandDetailId = reader.GetString(reader.GetOrdinal("stand_detail_id")),
                            CreateTime = reader.GetString(reader.GetOrdinal("create_time")),
                            StandId = reader.GetString(reader.GetOrdinal("stand_id")),
                            SrcImage = reader.GetString(reader.GetOrdinal("src_image")),
                            StandValue = reader.GetString(reader.GetOrdinal("stand_value")),
                            StationId = reader.GetString(reader.GetOrdinal("station_id")),
                            DeviceSn = reader.GetString(reader.GetOrdinal("device_sn")),
                            SampleTime = reader.GetString(reader.GetOrdinal("sample_time")),
                            ENumCount = reader.GetInt32(reader.GetOrdinal("e_num_count")),
                            ELastUnit = reader.GetInt32(reader.GetOrdinal("e_last_unit")),
                            //新加的项目
                            MeterType = reader.GetInt32(reader.GetOrdinal("meter_type")),
                            BrightVal = reader.GetDouble(reader.GetOrdinal("bright_val")),
                            FlowRate = reader.GetInt32(reader.GetOrdinal("flow_rate")),
                            DialRegion = reader.GetString(reader.GetOrdinal("dial_region")),
                            NumCount = reader.GetInt32(reader.GetOrdinal("num_count")),
                            IndCount = reader.GetInt32(reader.GetOrdinal("ind_count")),
                            FeatureRegion = reader.GetString(reader.GetOrdinal("feature_region")),
                            LastUnit = reader.GetDouble(reader.GetOrdinal("last_unit")),
                            NumInUpper = reader.GetInt32(reader.GetOrdinal("num_in_upper")),
                            LastValue = reader.GetDouble(reader.GetOrdinal("last_value")),
                            LastTime = reader.GetString(reader.GetOrdinal("last_time"))
                        };
                    }
                }
            }
            catch (Exception ex)
            {
                throw new Exception($"查询最小 SampleTime 记录时发生错误：{ex.Message}", ex);
            }

            return null; // 若未查询到结果，返回 null
        }

        public static TStandDetail GetPreConfigValidStandDetail(string standId, string stationId,string sampleTime)
        {
            string sql = @"
                SELECT *
                FROM t_stand_detail
                WHERE stand_id = @StandId AND station_id = @StationId AND sample_time < @SampleTime AND meter_type > 0
                ORDER BY sample_time DESC
                LIMIT 1";
            // 定义参数
            SQLiteParameter[] parameters = new SQLiteParameter[]
            {
                new SQLiteParameter("@StandId", standId),
                new SQLiteParameter("@StationId", stationId),
                new SQLiteParameter("@SampleTime", sampleTime)
            };

            // 执行查询并读取数据
            try
            {
                using (SQLiteDataReader reader = SQLiteHelper.ExecuteReader(sql, parameters))
                {
                    if (reader.Read())
                    {
                        return new TStandDetail
                        {
                            StandDetailId = reader.GetString(reader.GetOrdinal("stand_detail_id")),
                            CreateTime = reader.GetString(reader.GetOrdinal("create_time")),
                            StandId = reader.GetString(reader.GetOrdinal("stand_id")),
                            SrcImage = reader.GetString(reader.GetOrdinal("src_image")),
                            StandValue = reader.GetString(reader.GetOrdinal("stand_value")),
                            StationId = reader.GetString(reader.GetOrdinal("station_id")),
                            DeviceSn = reader.GetString(reader.GetOrdinal("device_sn")),
                            SampleTime = reader.GetString(reader.GetOrdinal("sample_time")),
                            ENumCount = reader.GetInt32(reader.GetOrdinal("e_num_count")),
                            ELastUnit = reader.GetInt32(reader.GetOrdinal("e_last_unit")),
                            //新加的项目
                            MeterType = reader.GetInt32(reader.GetOrdinal("meter_type")),
                            BrightVal = reader.GetDouble(reader.GetOrdinal("bright_val")),
                            FlowRate = reader.GetInt32(reader.GetOrdinal("flow_rate")),
                            DialRegion = reader.GetString(reader.GetOrdinal("dial_region")),
                            NumCount = reader.GetInt32(reader.GetOrdinal("num_count")),
                            IndCount = reader.GetInt32(reader.GetOrdinal("ind_count")),
                            FeatureRegion = reader.GetString(reader.GetOrdinal("feature_region")),
                            LastUnit = reader.GetDouble(reader.GetOrdinal("last_unit")),
                            NumInUpper = reader.GetInt32(reader.GetOrdinal("num_in_upper")),
                            LastValue = reader.GetDouble(reader.GetOrdinal("last_value")),
                            LastTime = reader.GetString(reader.GetOrdinal("last_time"))
                        };
                    }
                }
            }
            catch (Exception ex)
            {
                throw new Exception($"查询最小 SampleTime 记录时发生错误：{ex.Message}", ex);
            }

            return null; // 若未查询到结果，返回 null
        }


        //更新t_stand_detail中的参数
        public static bool UpdateTStandDetailByConfig(TStandDetail detail)
        {
            // 定义更新的 SQL 语句
            string sql = @"
                UPDATE t_stand_detail 
                SET                     
                    meter_type = @MeterType,
                    bright_val = @BrightVal,
                    flow_rate = @FlowRate,
                    dial_region = @DialRegion,
                    num_count = @NumCount,
                    ind_count = @IndCount,
                    feature_region = @FeatureRegion,
                    last_unit = @LastUnit,
                    num_in_upper = @NumInUpper,
                    last_value = @LastValue,
                    last_time = @LastTime
                 WHERE stand_detail_id = @StandDetailId;";

            // 创建 SQL 参数
            SQLiteParameter[] parameters = new SQLiteParameter[]
            {
                new SQLiteParameter("@StandDetailId",detail.StandDetailId),
                new SQLiteParameter("@MeterType", detail.MeterType),
                new SQLiteParameter("@BrightVal", detail.BrightVal),
                new SQLiteParameter("@FlowRate", detail.FlowRate),
                new SQLiteParameter("@DialRegion", detail.DialRegion),
                new SQLiteParameter("@NumCount", detail.NumCount),
                new SQLiteParameter("@IndCount", detail.IndCount),
                new SQLiteParameter("@FeatureRegion", detail.FeatureRegion),
                new SQLiteParameter("@LastUnit", detail.LastUnit),
                new SQLiteParameter("@NumInUpper",detail.NumInUpper),
                new SQLiteParameter("@LastValue", detail.LastValue),
                new SQLiteParameter("@LastTime", detail.LastTime)
            };

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

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




        public static async Task<List<TStandDetail>> GetAllStandDetailsAsync(string standId)
        {
            // 分页查询 SQL
            string sql = @"
                SELECT stand_detail_id, create_time, stand_id, src_image, stand_value, station_id, device_sn, sample_id, num_count, last_unit
                FROM t_stand_detail WHERE stand_id = @StandId
                ORDER BY create_time DESC";

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

            // 执行查询并读取数据
            List<TStandDetail> standDetails = new List<TStandDetail>();
            try
            {
                using (SQLiteDataReader reader = await SQLiteHelper.ExecuteReaderAsync(sql, parameters))
                {
                    while (await reader.ReadAsync())
                    {
                        // 将查询结果映射到 TStandDetail 对象
                        var standDetail = new TStandDetail
                        {
                            StandDetailId = reader.GetString(reader.GetOrdinal("stand_detail_id")),
                            CreateTime = reader.GetString(reader.GetOrdinal("create_time")),
                            StandId = reader.GetString(reader.GetOrdinal("stand_id")),
                            SrcImage = reader.GetString(reader.GetOrdinal("src_image")),
                            StandValue = reader.GetString(reader.GetOrdinal("stand_value")),
                            StationId = reader.GetString(reader.GetOrdinal("station_id")),
                            DeviceSn = reader.GetString(reader.GetOrdinal("device_sn")),
                            SampleTime = reader.GetString(reader.GetOrdinal("sample_time")),
                            NumCount = reader.GetInt32(reader.GetOrdinal("num_count")),
                            LastUnit = reader.GetInt32(reader.GetOrdinal("last_unit"))
                        };
                        standDetails.Add(standDetail);
                    }
                }
            }
            catch (Exception ex)
            {
                // 根据需要记录日志或抛出异常
                throw new Exception($"查询 StandDetails 时发生错误：{ex.Message}", ex);
            }

            return standDetails;
        }



        // 使用 SQLiteHelper 从 v_stand 中根据 stand_id 查询记录
        public static VStand GetVStandById(string standId)
        {
            VStand vStand = null;

            // 查询 SQL
            string query = @"
            SELECT 
                stand_id, 
                create_time, 
                stand_name, 
                stand_type, 
                stand_file, 
                stand_count 
            FROM v_stand
            WHERE stand_id = @StandId;";

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

            // 执行查询
            using (SQLiteDataReader reader = SQLiteHelper.ExecuteReader(query, parameters))
            {
                if (reader.Read())
                {
                    vStand = new VStand
                    {
                        StandId = reader["stand_id"].ToString(),
                        CreateTime = reader["create_time"].ToString(),
                        StandName = reader["stand_name"].ToString(),
                        StandType = reader["stand_type"] == DBNull.Value ? 0 : Convert.ToInt32(reader["stand_type"]),
                        StandFile = reader["stand_file"].ToString(),
                        StandCount = reader["stand_count"] == DBNull.Value ? 0 : Convert.ToInt32(reader["stand_count"])
                    };
                }
            }

            return vStand;
        }

        //t_stand_detail是否有重复数据
        public static bool IsSrcImageExitInStand(string filePath,string standId)
        {
            string query = "SELECT COUNT(*) FROM t_stand_detail WHERE src_image = @fileName";
            SQLiteParameter[] parameters = new SQLiteParameter[]
            {
                new SQLiteParameter("@fileName", filePath)
            };

            int result = Convert.ToInt32(SQLiteHelper.ExecuteSql(query, parameters));
            return result > 0;
        }

        public static bool UpdateStandDetailStandValue(string srcImage, string standValue)
        {
            string sql = @"
                UPDATE t_stand_detail                 
                SET stand_value = @StandValue
                WHERE src_image = @SrcImage;";

            // 创建 SQL 参数
            SQLiteParameter[] parameters = new SQLiteParameter[]
            {
                new SQLiteParameter("@SrcImage", srcImage),
                new SQLiteParameter("@StandValue", standValue)
            };

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

        public static bool UpdateStandName(string standId, string standName)
        {
            string sql = @"
                UPDATE t_stand                 
                SET stand_name = @StandName
                WHERE stand_id = @StandId;";

            // 创建 SQL 参数
            SQLiteParameter[] parameters = new SQLiteParameter[]
            {
                new SQLiteParameter("@StandName", standName),
                new SQLiteParameter("@StandId", standId)
            };

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

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

            // 创建参数数组
            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 List<StationInfo> GetUniqueStationInfo(string standId)
        {
            try
            {
                //string sql = @"
                //    SELECT
                //        stand_id,
                //        station_id,
                //        device_sn,
                //        MAX(e_num_count) AS num_count,
                //        MAX(e_last_unit) AS last_unit
                //    FROM
                //        t_stand_detail
                //    WHERE
                //        stand_id = @StandId
                //    GROUP BY
                //        station_id,device_sn
                //    ORDER BY
                //        station_id ASC;";

                string sql = @"
                    SELECT
                        stand_id,
                        station_id,
                        device_sn,
                        MAX(e_num_count) AS num_count,
                        MAX(e_last_unit) AS last_unit
                    FROM
                        t_stand_detail
                    WHERE
                        stand_id = @StandId
                    GROUP BY
                        station_id
                    ORDER BY
                        station_id ASC;";

                SQLiteParameter[] parameters = new SQLiteParameter[]
                {
                    new SQLiteParameter("@StandId", standId)
                };

                List<StationInfo> result = new List<StationInfo>();

                using (SQLiteDataReader reader = SQLiteHelper.ExecuteReader(sql, parameters))
                {
                    while (reader.Read())
                    {
                        StationInfo station = new StationInfo
                        {
                            StandId = reader.GetString(reader.GetOrdinal("stand_id")),
                            StationId = reader.GetString(reader.GetOrdinal("station_id")),
                            DeviceSn = reader.GetString(reader.GetOrdinal("device_sn")),
                            NumCount = reader.GetInt32(reader.GetOrdinal("num_count")),
                            LastUnit = reader.GetInt32(reader.GetOrdinal("last_unit"))
                        };
                        result.Add(station);
                    }
                }

                return result;
            }
            catch (Exception ex)
            {
                Console.WriteLine($"查询失败：{ex.Message}");
                return null;
            }
        }
        //----------------------------------------------------------------------
    }
}