MeterVision_v2/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 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, num_count, last_unit)
                                VALUES (@StandDetailId, @CreateTime, @StandId, @SrcImage, @StandValue, 
                                @StationId, @DeviceSn ,@SampleTime, @NumCount, @LastUnit);";

                            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("@NumCount", standDetail.NumCount),
                                new SQLiteParameter("@LastUnit", standDetail.LastUnit),
                            };
                            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 Tuple<int,int, List<TStandDetail>> GetPagedStandDetails(int pageNumber, int pageSize,string standId)
        {
            // 计算偏移量
            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
                ORDER BY create_time DESC  -- 根据需要修改排序字段
                LIMIT @PageSize OFFSET @Offset;";

            // 定义参数
            SQLiteParameter[] parameters = new SQLiteParameter[]
            {
                new SQLiteParameter("@StandId",standId),
                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")),
                        NumCount = reader.GetInt32(reader.GetOrdinal("num_count")),
                        LastUnit = reader.GetInt32(reader.GetOrdinal("last_unit"))
                    };
                    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";
            int totalRecords = Convert.ToInt32(SQLiteHelper.GetSingle(countSql, new SQLiteParameter("@StandId", standId)));

            // 计算总页数
            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")),
                            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;
        }

        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(num_count) AS num_count,
                        MAX(last_unit) AS last_unit
                    FROM
                        t_stand_detail
                    WHERE
                        stand_id = @StandId
                    GROUP BY
                        station_id,device_sn
                    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;
            }
        }
        //----------------------------------------------------------------------
    }
}