MV485_v125/MV485/db/DBSlaveDetail.cs

using MV485.helper;
using MV485.model;
using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace MV485.db
{
    public class DBSlaveDetail
    {
        public static bool InsertTSlaveDetail(TSlaveDetail detail)
        {
            string sql = @"
                INSERT OR IGNORE INTO t_slave_detail (
                    detail_id, slave_id, device_sn, read_time,
                    sample_result, sample_time, meter_type, image_file,read_memo
                )
                VALUES (
                    @DetailId, @SlaveId, @DeviceSn, @ReadTime,
                    @SampleResult, @SampleTime, @MeterType, @ImageFile, @ReadMemo
                )";

            SQLiteParameter[] parameters = new SQLiteParameter[]
            {
                new SQLiteParameter("@DetailId", detail.DetailId),
                new SQLiteParameter("@SlaveId", detail.SlaveId),
                new SQLiteParameter("@DeviceSn", detail.DeviceSn),
                new SQLiteParameter("@ReadTime", detail.ReadTime),
                new SQLiteParameter("@SampleResult", detail.SampleResult),
                new SQLiteParameter("@SampleTime", detail.SampleTime),
                new SQLiteParameter("@MeterType", detail.MeterType),
                new SQLiteParameter("@ImageFile", detail.ImageFile),
                new SQLiteParameter("@ReadMemo", detail.ReadMemo),
            };

            try
            {
                int rowsAffected = SQLiteHelper.ExecuteSql(sql, parameters);
                return rowsAffected > 0;
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error inserting slave detail: " + ex.Message);
                return false;
            }
        }

        public static List<TSlaveDetail> GetSlaveDetailBySlaveId(string slaveId)
        {
            string sql = @"
                SELECT detail_id, slave_id, device_sn, read_time,
                       sample_result, sample_time, meter_type, image_file
                FROM t_slave_detail
                WHERE slave_id = @SlaveId ORDER BY sample_time DESC";

            List<TSlaveDetail> details = new List<TSlaveDetail>();

            SQLiteParameter[] parameters = new SQLiteParameter[]
            {
                new SQLiteParameter("@SlaveId", slaveId)
            };

            using (SQLiteDataReader reader = SQLiteHelper.ExecuteReader(sql, parameters))
            {
                while (reader.Read())
                {
                    TSlaveDetail detail = new TSlaveDetail
                    {
                        DetailId = reader["detail_id"].ToString(),
                        SlaveId = reader["slave_id"].ToString(),
                        DeviceSn = reader["device_sn"].ToString(),
                        ReadTime = reader["read_time"].ToString(),
                        SampleResult = Convert.ToUInt64(reader["sample_result"]),
                        SampleTime = reader["sample_time"].ToString(),
                        MeterType = Convert.ToInt32(reader["meter_type"]),
                        ImageFile = reader["image_file"].ToString()
                    };
                    details.Add(detail);
                }
            }

            return details;
        }

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

            // 定义 SQL 查询语句，带有分页
            string sql = @"
                SELECT detail_id, slave_id, device_sn, read_time, sample_result, sample_time, meter_type,image_file,read_memo
                FROM t_slave_detail
                WHERE slave_id = @SlaveId ";

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

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

            // 执行查询并获取结果
            List<TSlaveDetail> slaveDetails = new List<TSlaveDetail>();
            using (SQLiteDataReader reader = SQLiteHelper.ExecuteReader(sql, parameters.ToArray()))
            {
                while (reader.Read())
                {
                    // 将查询结果映射到 TSlaveDetail 对象
                    TSlaveDetail slaveDetail = new TSlaveDetail
                    {
                        DetailId = reader.GetString(reader.GetOrdinal("detail_id")),
                        SlaveId = reader.GetString(reader.GetOrdinal("slave_id")),
                        DeviceSn = reader.GetString(reader.GetOrdinal("device_sn")),
                        ReadTime = reader.GetString(reader.GetOrdinal("read_time")),
                        //SampleResult = reader.get(reader.GetOrdinal("sample_result")),
                        SampleResult = Convert.ToUInt64(reader["sample_result"]),
                        SampleTime = reader.GetString(reader.GetOrdinal("sample_time")),
                        MeterType = reader.GetInt32(reader.GetOrdinal("meter_type")),
                        ImageFile = reader["image_file"].ToString(),
                        ReadMemo = reader["read_memo"].ToString(),
                    };
                    slaveDetails.Add(slaveDetail);
                }
            }

            // 获取总记录数，用于计算总页数
            string countSql = "SELECT COUNT(*) FROM t_slave_detail WHERE slave_id = @SlaveId";
            int totalRecords = Convert.ToInt32(SQLiteHelper.GetSingle(countSql, new SQLiteParameter("@SlaveId", slaveId)));

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

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



        public static bool DeleteSlaveDetailBySlaveId(string slaveId)
        {
            string sql = "DELETE FROM t_slave_detail WHERE slave_id = @SlaveId";

            SQLiteParameter[] parameters = new SQLiteParameter[]
            {
                new SQLiteParameter("@SlaveId", slaveId)
            };

            try
            {
                int rowsAffected = SQLiteHelper.ExecuteSql(sql, parameters);
                return rowsAffected > 0;
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error deleting slave detail: " + ex.Message);
                return false;
            }
        }

        public static bool DeleteSlaveDetailByDetailId(string detailId)
        {
            string sql = "DELETE FROM t_slave_detail WHERE detail_id = @DetailId";

            SQLiteParameter[] parameters = new SQLiteParameter[]
            {
                new SQLiteParameter("@DetailId", detailId)
            };

            try
            {
                int rowsAffected = SQLiteHelper.ExecuteSql(sql, parameters);
                return rowsAffected > 0;
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error deleting slave detail: " + ex.Message);
                return false;
            }
        }
        //----------------------------------------------------------------------
    }
}