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, num_region, ht_region, last_num_unit, last_ind_unit, " +
                         "last_value, last_time, create_time, stand_id) " +
                         "VALUES (@StationId, @StationName, @DeviceSn, @MeterType, @BrightVal, @FlowRate, " +
                         "@DialRegion, @NumCount, @IndCount, @NumRegion, @HtRegion, @LastNumUnit, @LastIndUnit, " +
                         "@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("@NumRegion", tStation.NumRegion),
                new SQLiteParameter("@HtRegion", tStation.HtRegion),
                new SQLiteParameter("@LastNumUnit", tStation.LastNumUnit),
                new SQLiteParameter("@LastIndUnit", tStation.LastIndUnit),
                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_num_unit)
                VALUES (@Id, @StandId, @CreateTime, @StationId, @DeviceSn, @NumCount, @LastNumUnit);";

            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("@LastNumUnit", 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, num_region, ht_region, last_num_unit, last_ind_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_name ASC LIMIT @PageSize OFFSET @Offset;";

            // 创建 SQL 参数
            //SQLiteParameter[] parameters = new SQLiteParameter[]
            //{
            //    new SQLiteParameter("@PageSize", pageSize),
            //    new SQLiteParameter("@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")),
                        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"))
                        //Id = reader.IsDBNull(reader.GetOrdinal("id")) ? null : reader.GetString(reader.GetOrdinal("id")),
                        //StationId = reader.IsDBNull(reader.GetOrdinal("station_id")) ? null : reader.GetString(reader.GetOrdinal("station_id")),
                        //StationName = reader.IsDBNull(reader.GetOrdinal("station_name")) ? null : reader.GetString(reader.GetOrdinal("station_name")),
                        //DeviceSn = reader.IsDBNull(reader.GetOrdinal("device_sn")) ? null : reader.GetString(reader.GetOrdinal("device_sn")),
                        //MeterType = reader.IsDBNull(reader.GetOrdinal("meter_type")) ? 0 : reader.GetInt32(reader.GetOrdinal("meter_type")),
                        //BrightVal = reader.IsDBNull(reader.GetOrdinal("bright_val")) ? 0.0 : reader.GetDouble(reader.GetOrdinal("bright_val")),
                        //FlowRate = reader.IsDBNull(reader.GetOrdinal("flow_rate")) ? 0 : reader.GetInt32(reader.GetOrdinal("flow_rate")),
                        //DialRegion = reader.IsDBNull(reader.GetOrdinal("dial_region")) ? null : reader.GetString(reader.GetOrdinal("dial_region")),
                        //NumCount = reader.IsDBNull(reader.GetOrdinal("num_count")) ? 0 : reader.GetInt32(reader.GetOrdinal("num_count")),
                        //IndCount = reader.IsDBNull(reader.GetOrdinal("ind_count")) ? 0 : reader.GetInt32(reader.GetOrdinal("ind_count")),
                        //NumRegion = reader.IsDBNull(reader.GetOrdinal("num_region")) ? null : reader.GetString(reader.GetOrdinal("num_region")),
                        //HtRegion = reader.IsDBNull(reader.GetOrdinal("ht_region")) ? null : reader.GetString(reader.GetOrdinal("ht_region")),
                        //LastNumUnit = reader.IsDBNull(reader.GetOrdinal("last_num_unit")) ? 0.0 : reader.GetDouble(reader.GetOrdinal("last_num_unit")),
                        //LastIndUnit = reader.IsDBNull(reader.GetOrdinal("last_ind_unit")) ? 0.0 : reader.GetDouble(reader.GetOrdinal("last_ind_unit")),
                        //LastValue = reader.IsDBNull(reader.GetOrdinal("last_value")) ? 0.0 : reader.GetDouble(reader.GetOrdinal("last_value")),
                        //LastTime = reader.IsDBNull(reader.GetOrdinal("last_time")) ? null : reader.GetString(reader.GetOrdinal("last_time")),
                        //CreateTime = reader.IsDBNull(reader.GetOrdinal("create_time")) ? null : reader.GetString(reader.GetOrdinal("create_time")),
                        //StandId = reader.IsDBNull(reader.GetOrdinal("stand_id")) ? null : 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 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,
                    num_region = @NumRegion,
                    ht_region = @HtRegion,
                    last_num_unit = @LastNumUnit,
                    last_ind_unit = @LastIndUnit,
                    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("@NumRegion", tStation.NumRegion),
                new SQLiteParameter("@HtRegion", tStation.HtRegion),
                new SQLiteParameter("@LastNumUnit", tStation.LastNumUnit),
                new SQLiteParameter("@LastIndUnit", tStation.LastIndUnit),
                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;
            }
        }



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

    }
}