MeterVision_v2/MeterVision/db/DBStation.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 DBStation
    {

        public static bool InsertTStation(TStation tStation)
        {
            // 构建插入的 SQL 语句
            string sql = "INSERT INTO t_station (id,station_id, station_name, device_sn,meter_type, bright_val, flow_rate, " +
                         "dial_region, num_count, ind_count, feature_region, last_unit, " +
                         "last_value, last_time, create_time, stand_id) " +
                         "VALUES (@StationId, @StationName, @DeviceSn, @MeterType, @BrightVal, @FlowRate, " +
                         "@DialRegion, @NumCount, @IndCount, @FeatureRegion, @LastUnit, " +
                         "@LastValue, @LastTime, @CreateTime, @StandId)";

            // 创建参数数组
            SQLiteParameter[] parameters = new SQLiteParameter[]
            {
                new SQLiteParameter("@Id", tStation.Id),
                new SQLiteParameter("@StationId", tStation.StationId),
                new SQLiteParameter("@StationName", tStation.StationName),
                new SQLiteParameter("@DeviceSn", tStation.DeviceSn),
                new SQLiteParameter("@MeterType", tStation.MeterType),
                new SQLiteParameter("@BrightVal", tStation.BrightVal),
                new SQLiteParameter("@FlowRate", tStation.FlowRate),
                new SQLiteParameter("@DialRegion", tStation.DialRegion),
                new SQLiteParameter("@NumCount", tStation.NumCount),
                new SQLiteParameter("@IndCount", tStation.IndCount),
                new SQLiteParameter("@FeatureRegion", tStation.FeatureRegion),               
                new SQLiteParameter("@LastUnit", tStation.LastUnit),
                new SQLiteParameter("@LastValue", tStation.LastValue),
                new SQLiteParameter("@LastTime", tStation.LastTime),
                new SQLiteParameter("@CreateTime", tStation.CreateTime),
                new SQLiteParameter("@StandId", tStation.StandId),
            };

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

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

        public static bool InsertTStations(List<StationInfo> stationInfos)
        {
            string sql = @"
                INSERT INTO t_station (id, stand_id,create_time, station_id, device_sn, num_count, last_unit)
                VALUES (@Id, @StandId, @CreateTime, @StationId, @DeviceSn, @NumCount, @LastUnit);";

            try
            {
                // 开始事务
                using (SQLiteConnection connection = SQLiteHelper.GetConnection())
                {
                    connection.Open();
                    using (SQLiteTransaction transaction = connection.BeginTransaction())
                    {
                        // 遍历列表插入每一条数据
                        foreach (var stationInfo in stationInfos)
                        {
                            SQLiteParameter[] parameters = new SQLiteParameter[]
                            {
                                new SQLiteParameter("@Id", Guid.NewGuid().ToString()),
                                new SQLiteParameter("@StandId", stationInfo.StandId),
                                new SQLiteParameter("@CreateTime", ThisApp.GetNowTime_yyyyMMddHHmmss()),
                                new SQLiteParameter("@StationId", stationInfo.StationId),
                                new SQLiteParameter("@DeviceSn", stationInfo.DeviceSn),
                                new SQLiteParameter("@NumCount", stationInfo.NumCount),
                                new SQLiteParameter("@LastUnit", stationInfo.LastUnit2)
                            };

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

                        // 提交事务
                        transaction.Commit();
                    }
                }
                return true;

            }
            catch(Exception ex)
            {
                Console.WriteLine(ex.Message);
                return false;
            }
        }

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

            // 定义 SQL 查询语句，带有分页
            // 定义 SQL 查询语句的基础部分
            string sql = @"
                SELECT id,station_id, station_name, device_sn,meter_type, bright_val, flow_rate, dial_region,
                       num_count, ind_count, feature_region, last_unit,
                       last_value, last_time, create_time,stand_id                
                FROM t_station ";

            // 如果传入的 stationId 不为空，增加过滤条件
            if (!string.IsNullOrEmpty(stationId))
            {
                sql += "WHERE station_id LIKE @StationId ";
            }

            // 添加排序和分页
            sql += "ORDER BY station_id ASC LIMIT @PageSize OFFSET @Offset;";

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

            // 如果传入了 stationId，添加到参数中
            if (!string.IsNullOrEmpty(stationId))
            {
                parameters.Add(new SQLiteParameter("@StationId", "%" + stationId + "%"));
            }

            // 执行查询并获取结果
            List<TStation> stations = new List<TStation>();
            using (SQLiteDataReader reader = SQLiteHelper.ExecuteReader(sql, parameters.ToArray()))
            {
                while (reader.Read())
                {
                    // 将查询结果映射到 TStation 对象
                    TStation station = new TStation
                    {
                        Id = reader.GetString(reader.GetOrdinal("id")),
                        StationId = reader.GetString(reader.GetOrdinal("station_id")),
                        StationName = reader.GetString(reader.GetOrdinal("station_name")),
                        DeviceSn = reader.GetString(reader.GetOrdinal("device_sn")),
                        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")),
                        CreateTime = reader.GetString(reader.GetOrdinal("create_time")),
                        StandId = reader.GetString(reader.GetOrdinal("stand_id"))
                    };
                    stations.Add(station);
                }
            }

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

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

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

        //public static Tuple<int, int, List<TStation>> GetPagedTStations(int pageNumber, int pageSize,string stationId,string standId)
        //{
        //    // 计算 OFFSET 值
        //    int offset = (pageNumber - 1) * pageSize;

        //    // 定义 SQL 查询语句，带有分页
        //    // 定义 SQL 查询语句的基础部分
        //    string sql = @"
        //        SELECT id,station_id, station_name, device_sn,meter_type, bright_val, flow_rate, dial_region,
        //               num_count, ind_count, num_region, ht_region, last_num_unit, last_ind_unit,
        //               last_value, last_time, create_time,stand_id                
        //        FROM t_station ";

        //    sql += "WHERE stand_id = @StandId";
        //    // 如果传入的 stationId 不为空，增加过滤条件
        //    if (!string.IsNullOrEmpty(stationId))
        //    {
        //        //sql += "WHERE station_id LIKE @StationId ";
        //        sql += " AND station_id LIKE @StationId ";
        //    }

        //    // 添加排序和分页
        //    sql += " ORDER BY station_id ASC LIMIT @PageSize OFFSET @Offset;";


        //    // 创建 SQL 参数
        //    List<SQLiteParameter> parameters = new List<SQLiteParameter>
        //    {
        //        new SQLiteParameter("@PageSize", pageSize),
        //        new SQLiteParameter("@Offset", offset),
        //        new SQLiteParameter("@StandId",standId)
        //    };

        //    // 如果传入了 stationId，添加到参数中
        //    if (!string.IsNullOrEmpty(stationId))
        //    {
        //        parameters.Add(new SQLiteParameter("@StationId", "%" + stationId + "%"));
        //    }

        //    // 执行查询并获取结果
        //    List<TStation> stations = new List<TStation>();
        //    using (SQLiteDataReader reader = SQLiteHelper.ExecuteReader(sql, parameters.ToArray()))
        //    {
        //        while (reader.Read())
        //        {
        //            // 将查询结果映射到 TStation 对象
        //            TStation station = new TStation
        //            {
        //                Id = reader.GetString(reader.GetOrdinal("id")),
        //                StationId = reader.GetString(reader.GetOrdinal("station_id")),
        //                StationName = reader.GetString(reader.GetOrdinal("station_name")),
        //                DeviceSn = reader.GetString(reader.GetOrdinal("device_sn")),
        //                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")),
        //                NumRegion = reader.GetString(reader.GetOrdinal("num_region")),
        //                HtRegion = reader.GetString(reader.GetOrdinal("ht_region")),
        //                LastNumUnit = reader.GetDouble(reader.GetOrdinal("last_num_unit")),
        //                LastIndUnit = reader.GetDouble(reader.GetOrdinal("last_ind_unit")),
        //                LastValue = reader.GetDouble(reader.GetOrdinal("last_value")),
        //                LastTime = reader.GetString(reader.GetOrdinal("last_time")),
        //                CreateTime = reader.GetString(reader.GetOrdinal("create_time")),
        //                StandId = reader.GetString(reader.GetOrdinal("stand_id"))
        //            };
        //            stations.Add(station);
        //        }
        //    }

        //    // 获取总记录数，用于计算总页数
        //    string countSql = "SELECT COUNT(*) FROM t_station ";

        //    int totalRecords = Convert.ToInt32(SQLiteHelper.GetSingle(countSql));

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

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

        public static Tuple<int, int, List<TStation>> GetPagedTStations(int pageNumber, int pageSize, string stationId, string standId)
        {
            // 计算 OFFSET 值
            int offset = (pageNumber - 1) * pageSize;

            // 定义 SQL 查询语句
            string baseSql = @"
                FROM t_station 
                WHERE stand_id = @StandId";

            // 如果传入的 stationId 不为空，增加过滤条件
            if (!string.IsNullOrEmpty(stationId))
            {
                baseSql += " AND station_id LIKE @StationId";
            }

            // 查询分页数据
            string sql = $@"
                SELECT id, station_id, station_name, device_sn, meter_type, bright_val, flow_rate, dial_region,
                       num_count, ind_count, feature_region, last_unit,
                       last_value, last_time, create_time, stand_id
                {baseSql}
                ORDER BY station_id ASC
                LIMIT @PageSize OFFSET @Offset;";

            // 查询总记录数
            string countSql = $"SELECT COUNT(*) {baseSql};";

            // 创建 SQL 参数
            List<SQLiteParameter> parameters = new List<SQLiteParameter>
            {
                new SQLiteParameter("@StandId", standId),
                new SQLiteParameter("@PageSize", pageSize),
                new SQLiteParameter("@Offset", offset)
            };

            // 如果传入了 stationId，添加到参数中
            if (!string.IsNullOrEmpty(stationId))
            {
                parameters.Add(new SQLiteParameter("@StationId", "%" + stationId + "%"));
            }

            // 获取数据列表
            List<TStation> stations = new List<TStation>();
            using (SQLiteDataReader reader = SQLiteHelper.ExecuteReader(sql, parameters.ToArray()))
            {
                while (reader.Read())
                {
                    TStation station = new TStation
                    {
                        Id = reader.GetString(reader.GetOrdinal("id")),
                        StationId = reader.GetString(reader.GetOrdinal("station_id")),
                        StationName = reader.GetString(reader.GetOrdinal("station_name")),
                        DeviceSn = reader.GetString(reader.GetOrdinal("device_sn")),
                        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")),
                        CreateTime = reader.GetString(reader.GetOrdinal("create_time")),
                        StandId = reader.GetString(reader.GetOrdinal("stand_id"))
                    };
                    stations.Add(station);
                }
            }

            // 获取符合条件的总记录数
            int totalRecords = Convert.ToInt32(SQLiteHelper.GetSingle(countSql, parameters.ToArray()));

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

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


        public static bool DeleteTStation(string id)
        {
            // 定义删除的 SQL 语句
            string sql = "DELETE FROM t_station WHERE id = @Id";

            // 创建 SQL 参数
            SQLiteParameter[] parameters = new SQLiteParameter[]
            {
                new SQLiteParameter("@Id", id)
            };

            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 UpdateTStation(TStation tStation)
        {
            // 定义更新的 SQL 语句
            string sql = @"
                UPDATE t_station 
                SET 
                    station_name = @StationName, 
                    meter_type = @MeterType,
                    bright_val = @BrightVal,
                    flow_rate = @FlowRate,
                    dial_region = @DialRegion,
                    num_count = @NumCount,
                    ind_count = @IndCount,
                    feature_region = @FeatureRegion,
                    last_unit = @LastUnit,
                    last_value = @LastValue,
                    last_time = @LastTime
                WHERE id = @Id";

            // 创建 SQL 参数
            SQLiteParameter[] parameters = new SQLiteParameter[]
            {
                //new SQLiteParameter("@StationId", tStation.StationId),
                new SQLiteParameter("@Id",tStation.Id),
                new SQLiteParameter("@StationName", tStation.StationName),
                new SQLiteParameter("@MeterType", tStation.MeterType),
                new SQLiteParameter("@BrightVal", tStation.BrightVal),
                new SQLiteParameter("@FlowRate", tStation.FlowRate),
                new SQLiteParameter("@DialRegion", tStation.DialRegion),
                new SQLiteParameter("@NumCount", tStation.NumCount),
                new SQLiteParameter("@IndCount", tStation.IndCount),
                new SQLiteParameter("@FeatureRegion", tStation.FeatureRegion),
                new SQLiteParameter("@LastUnit", tStation.LastUnit),
                new SQLiteParameter("@LastValue", tStation.LastValue),
                new SQLiteParameter("@LastTime", tStation.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 bool ClearTStationTable()
        {
            // 构建清空表的 SQL 语句
            string sql = "DELETE  FROM t_station";

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

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



        //-----------------------------------------------------

    }
}