using MeterVision.model; using MeterVision.Stand; 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 OR IGNORE 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 stationInfos) { //INSERT OR IGNORE INTO string sql = @" INSERT OR IGNORE 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> 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 parameters = new List { new SQLiteParameter("@PageSize", pageSize), new SQLiteParameter("@Offset", offset), }; // 如果传入了 stationId，添加到参数中 if (!string.IsNullOrEmpty(stationId)) { parameters.Add(new SQLiteParameter("@StationId", "%" + stationId + "%")); } // 执行查询并获取结果 List stations = new List(); 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> 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 parameters = new List // { // new SQLiteParameter("@PageSize", pageSize), // new SQLiteParameter("@Offset", offset), // new SQLiteParameter("@StandId",standId) // }; // // 如果传入了 stationId，添加到参数中 // if (!string.IsNullOrEmpty(stationId)) // { // parameters.Add(new SQLiteParameter("@StationId", "%" + stationId + "%")); // } // // 执行查询并获取结果 // List stations = new List(); // 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> 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 parameters = new List { new SQLiteParameter("@StandId", standId), new SQLiteParameter("@PageSize", pageSize), new SQLiteParameter("@Offset", offset) }; // 如果传入了 stationId，添加到参数中 if (!string.IsNullOrEmpty(stationId)) { parameters.Add(new SQLiteParameter("@StationId", "%" + stationId + "%")); } // 获取数据列表 List stations = new List(); 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 Tuple> GetPagedVStations(int pageNumber, int pageSize, string stationId, MarkFindType markFindType,string standId) { // 计算 OFFSET 值 int offset = (pageNumber - 1) * pageSize; // 定义 SQL 查询语句 string baseSql = @" FROM v_station WHERE stand_id = @StandId"; // 如果传入的 stationId 不为空，增加过滤条件 if (!string.IsNullOrEmpty(stationId)) { baseSql += " AND station_id LIKE @StationId"; } if(markFindType == MarkFindType.ALL) { } else if(markFindType == MarkFindType.MARK_YES) { baseSql += " AND mark_count > 0"; } else if(markFindType == MarkFindType.MARK_NO) { baseSql += " And mark_count = 0"; } // 查询分页数据 string sql = $@" SELECT id, stand_id, station_id, create_time, mark_count {baseSql} ORDER BY station_id ASC LIMIT @PageSize OFFSET @Offset;"; // 查询总记录数 string countSql = $"SELECT COUNT(*) {baseSql};"; // 创建 SQL 参数 List parameters = new List { new SQLiteParameter("@StandId", standId), new SQLiteParameter("@PageSize", pageSize), new SQLiteParameter("@Offset", offset) }; // 如果传入了 stationId，添加到参数中 if (!string.IsNullOrEmpty(stationId)) { parameters.Add(new SQLiteParameter("@StationId", "%" + stationId + "%")); } // 获取数据列表 List stations = new List(); using (SQLiteDataReader reader = SQLiteHelper.ExecuteReader(sql, parameters.ToArray())) { while (reader.Read()) { TStation station = new TStation { Id = reader.GetString(reader.GetOrdinal("id")), StandId = reader.GetString(reader.GetOrdinal("stand_id")), StationId = reader.GetString(reader.GetOrdinal("station_id")), CreateTime = reader.GetString(reader.GetOrdinal("create_time")), MarkCount = reader.GetInt32(reader.GetOrdinal("mark_count")), }; 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 int GetMarkCount(string id) { int iMarkCount = 0; string sql = $@" SELECT mark_count FROM v_station WHERE id = @Id"; // 定义参数 SQLiteParameter[] parameters = new SQLiteParameter[] { new SQLiteParameter("@Id", id) }; // 执行查询并读取数据 try { using (SQLiteDataReader reader = SQLiteHelper.ExecuteReader(sql, parameters)) { if (reader.Read()) { return reader.GetInt32(reader.GetOrdinal("mark_count")); } } } catch (Exception ex) { throw new Exception($"查询最小 SampleTime 记录时发生错误：{ex.Message}", ex); } return iMarkCount; } public static List GetAllStation(string standId) { // 查询分页数据 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 WHERE stand_id = @StandId AND meter_type > 0 AND dial_region <> '' AND feature_region <> '' AND last_time <> '' ORDER BY station_id ASC"; // 定义参数 SQLiteParameter[] parameters = new SQLiteParameter[] { new SQLiteParameter("@StandId", standId) }; // 执行查询并读取数据 List stations = new List(); try { 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); } } } catch (Exception ex) { // 根据需要记录日志或抛出异常 throw new Exception($"查询 TStation表 时发生错误：{ex.Message}", ex); } return stations; } public static List GetAllVStation(string standId) { // 查询分页数据 string sql = $@" SELECT id, stand_id, station_id, create_time, mark_count FROM v_station WHERE stand_id = @StandId AND mark_count > 0 ORDER BY station_id ASC"; // 定义参数 SQLiteParameter[] parameters = new SQLiteParameter[] { new SQLiteParameter("@StandId", standId) }; // 执行查询并读取数据 List stations = new List(); try { using (SQLiteDataReader reader = SQLiteHelper.ExecuteReader(sql, parameters.ToArray())) { while (reader.Read()) { TStation station = new TStation { Id = reader.GetString(reader.GetOrdinal("id")), StandId = reader.GetString(reader.GetOrdinal("stand_id")), StationId = reader.GetString(reader.GetOrdinal("station_id")), CreateTime = reader.GetString(reader.GetOrdinal("create_time")), MarkCount = reader.GetInt32(reader.GetOrdinal("mark_count")) }; stations.Add(station); } } } catch (Exception ex) { // 根据需要记录日志或抛出异常 throw new Exception($"查询 TStation表 时发生错误：{ex.Message}", ex); } return stations; } public static List GetAllVStation2(string standId) { // 查询分页数据 string sql = $@" SELECT id, stand_id, station_id, create_time, mark_count FROM v_station WHERE stand_id = @StandId ORDER BY station_id ASC"; // 定义参数 SQLiteParameter[] parameters = new SQLiteParameter[] { new SQLiteParameter("@StandId", standId) }; // 执行查询并读取数据 List stations = new List(); try { using (SQLiteDataReader reader = SQLiteHelper.ExecuteReader(sql, parameters.ToArray())) { while (reader.Read()) { TStation station = new TStation { Id = reader.GetString(reader.GetOrdinal("id")), StandId = reader.GetString(reader.GetOrdinal("stand_id")), StationId = reader.GetString(reader.GetOrdinal("station_id")), CreateTime = reader.GetString(reader.GetOrdinal("create_time")), MarkCount = reader.GetInt32(reader.GetOrdinal("mark_count")) }; stations.Add(station); } } } catch (Exception ex) { // 根据需要记录日志或抛出异常 throw new Exception($"查询 TStation表 时发生错误：{ex.Message}", ex); } return 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 DeleteTStationByStandId(string standId) { // 定义删除的 SQL 语句 string sql = "DELETE FROM t_station WHERE stand_id = @StandId"; // 创建 SQL 参数 SQLiteParameter[] parameters = new SQLiteParameter[] { new SQLiteParameter("@StandId", standId) }; try { // 调用 SQLiteHelper 执行删除操作 int rowsAffected = SQLiteHelper.ExecuteSql(sql, parameters); // 如果删除成功，返回 true，否则返回 false //return rowsAffected > 0; return true; } catch (Exception ex) { // 处理异常（如果有的话） Console.WriteLine("Error deleting data: " + ex.Message); return false; } } public static bool DeleteStationAndStandDetails(string standId,string stationId) { // 定义删除的 SQL 语句，使用事务来保证一致性 string sql = @" BEGIN TRANSACTION; -- 删除 t_stand_detail 表中与 stand_id 相关的数据 DELETE FROM t_stand_detail WHERE station_id = @StationId AND stand_id = @StandId; -- 删除 t_station 表中与 stand_id 相关的数据 DELETE FROM t_station WHERE station_id = @StationId AND stand_id = @StandId; COMMIT;"; // 创建参数 SQLiteParameter[] parameters = new SQLiteParameter[] { new SQLiteParameter("@StandId", standId), new SQLiteParameter("@StationId",stationId) }; 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 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; } } //----------------------------------------------------- } }