Sleep-Multi-functionality/ReportWindow6.xaml.cs

using Microsoft.Win32;
using OfficeOpenXml;
using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data.SQLite;
using System.IO;
using System.Windows;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using static ShanghaiEnvironmentalTechnology.Window5;

namespace ShanghaiEnvironmentalTechnology
{

    public class CO2ProcessRecordWithSelection : FlowPressureRecord
    {
        public bool IsSelected { get; set; }
    }
    public partial class ReportWindow6 : Window
    {
        private const int PageSize = 10;
        private int currentPage = 1;
        private int totalRecords = 0;
        // 新增：保存当前查询条件，用于删除后刷新
        private string _currentStartDate = null;
        private string _currentEndDate = null;
        // 新增：使用ObservableCollection支持数据绑定刷新
        private ObservableCollection<CO2ProcessRecordWithSelection> _records = new ObservableCollection<CO2ProcessRecordWithSelection>();

        public ReportWindow6()
        {
            InitializeComponent();
            LoadData();
        }

        private void LoadData(string startDate = null, string endDate = null)
        {
            using (SQLiteConnection conn = new SQLiteConnection(CSConstant.DbConnectionString))
            {
                conn.Open();
                string query = "SELECT Id, Flow, Pressure, BeginCO2, EndCO2, CO2Added, RecordTime FROM CO2";
                if (!string.IsNullOrEmpty(startDate) && !string.IsNullOrEmpty(endDate))
                {
                    query += " WHERE RecordTime BETWEEN @startDate AND @endDate";
                }
                query += " LIMIT @offset, @limit";

                using (SQLiteCommand cmd = new SQLiteCommand(query, conn))
                {
                    if (!string.IsNullOrEmpty(startDate) && !string.IsNullOrEmpty(endDate))
                    {
                        cmd.Parameters.AddWithValue("@startDate", startDate);
                        cmd.Parameters.AddWithValue("@endDate", endDate);
                    }
                    cmd.Parameters.AddWithValue("@offset", (currentPage - 1) * PageSize);
                    cmd.Parameters.AddWithValue("@limit", PageSize);

                    _records.Clear(); // 清空现有数据
                    using (SQLiteDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            _records.Add(new CO2ProcessRecordWithSelection
                            {
                                Id = reader.GetInt32(0),
                                Flow = reader.GetDouble(1),
                                Pressure = reader.GetDouble(2),
                                BeginCO2 = reader.GetDouble(3),
                                EndCO2 = reader.GetDouble(4),
                                CO2Added = reader.GetDouble(5),
                                RecordTime = reader.GetDateTime(6)
                            });
                        }
                    }

                    DataGridReport.ItemsSource = _records;
                }

                // 获取总记录数，考虑当前查询条件
                string countQuery = "SELECT COUNT(*) FROM CO2";
                if (!string.IsNullOrEmpty(startDate) && !string.IsNullOrEmpty(endDate))
                {
                    countQuery += " WHERE RecordTime BETWEEN @startDate AND @endDate";
                }

                using (SQLiteCommand countCmd = new SQLiteCommand(countQuery, conn))
                {
                    if (!string.IsNullOrEmpty(startDate) && !string.IsNullOrEmpty(endDate))
                    {
                        countCmd.Parameters.AddWithValue("@startDate", startDate);
                        countCmd.Parameters.AddWithValue("@endDate", endDate);
                    }

                    totalRecords = Convert.ToInt32(countCmd.ExecuteScalar());
                }

                UpdatePageInfo();
            }
        }


        private void QueryButton_Click(object sender, RoutedEventArgs e)
        {
            string? startDate = StartDatePicker.SelectedDate?.ToString("yyyy-MM-dd") ?? null;
            DateTime? endDate = EndDatePicker.SelectedDate;

            if (endDate.HasValue)
            {
                endDate = endDate.Value.Date.AddDays(1).AddTicks(-1); // 设置为23:59:59.9999999
            }

            string? endDateString = endDate?.ToString("yyyy-MM-dd HH:mm:ss") ?? null;

            currentPage = 1;
            LoadData(startDate, endDateString);
        }

        private void UpdatePageInfo()
        {
            PageInfo.Text = $"第 {currentPage} 页 / 共 {Math.Ceiling((double)totalRecords / PageSize)} 页 {totalRecords}条记录";
        }

        private void PreviousPage_Click(object sender, RoutedEventArgs e)
        {
            if (currentPage > 1)
            {
                currentPage--;
                LoadData();
            }
        }

        private void NextPage_Click(object sender, RoutedEventArgs e)
        {
            if (currentPage * PageSize < totalRecords)
            {
                currentPage++;
                LoadData();
            }
        }

        private void Window_Loaded(object sender, RoutedEventArgs e)
        {
            string imagePath = System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Resources/sleep2.jpg");
            ImageBrush brush = new ImageBrush();
            brush.ImageSource = new BitmapImage(new Uri(imagePath, UriKind.Absolute));
            this.Background = brush;
        }

        private void DeleteButton_Click(object sender, RoutedEventArgs e)
        {
            if (sender is FrameworkElement element && element.Tag is int id)
            {
                var result = MessageBox.Show($"确定要删除ID为 {id} 的记录吗？", "确认删除",
                                           MessageBoxButton.YesNo, MessageBoxImage.Warning);
                if (result == MessageBoxResult.Yes)
                {
                    DeleteRecordFromDb(id);
                    // 刷新当前页数据
                    LoadData(_currentStartDate, _currentEndDate);
                }
            }
        }

        // 新增：批量删除功能
        private void BatchDeleteButton_Click(object sender, RoutedEventArgs e)
        {
            var selectedIds = _records.Where(r => r.IsSelected).Select(r => r.Id).ToList();
            if (!selectedIds.Any())
            {
                MessageBox.Show("请先选择要删除的记录", "提示", MessageBoxButton.OK, MessageBoxImage.Information);
                return;
            }

            var result = MessageBox.Show($"确定要删除选中的 {selectedIds.Count} 条记录吗？", "确认批量删除",
                                       MessageBoxButton.YesNo, MessageBoxImage.Warning);
            if (result == MessageBoxResult.Yes)
            {
                BatchDeleteFromDb(selectedIds);
                // 刷新当前页数据
                LoadData(_currentStartDate, _currentEndDate);
            }
        }

        // 新增：数据库单条删除操作
        private void DeleteRecordFromDb(int id)
        {
            using (var conn = new SQLiteConnection(CSConstant.DbConnectionString))
            {
                conn.Open();
                using (var cmd = new SQLiteCommand("DELETE FROM CO2 WHERE Id = @Id", conn))
                {
                    cmd.Parameters.AddWithValue("@Id", id);
                    cmd.ExecuteNonQuery();
                }
            }
        }

        // 新增：数据库批量删除操作
        private void BatchDeleteFromDb(List<int> ids)
        {
            if (!ids.Any()) return;

            using (var conn = new SQLiteConnection(CSConstant.DbConnectionString))
            {
                conn.Open();

                string idsParam = string.Join(",", ids.Select((_, i) => $"@Id{i}"));
                using (var cmd = new SQLiteCommand($"DELETE FROM CO2 WHERE Id IN ({idsParam})", conn))
                {
                    // 添加参数
                    for (int i = 0; i < ids.Count; i++)
                    {
                        cmd.Parameters.AddWithValue($"@Id{i}", ids[i]);
                    }
                    cmd.ExecuteNonQuery();
                }
            }
        }

        private void Export_Click(object sender, RoutedEventArgs e)
        {

            DateTime? startDate = null;
            if (DateTime.TryParse(StartDatePicker.SelectedDate?.ToString(), out DateTime tempStartDate))
            {
                startDate = tempStartDate;
            }

            DateTime? endDate = EndDatePicker.SelectedDate;

            if (endDate.HasValue)
            {
                endDate = endDate.Value.Date.AddDays(1).AddTicks(-1);
            }

            // 步骤2：读取CO2表数据
            List<CO2Record> co2Records = ReadCO2RecordsFromDatabase(startDate, endDate);
            if (co2Records == null || !co2Records.Any())
            {
                MessageBox.Show("CO2表中无数据，无法导出", "提示", MessageBoxButton.OK, MessageBoxImage.Warning);
                return;
            }

            // 步骤3：导出Excel
            bool exportSuccess = ExportCO2RecordsToExcel(co2Records);
            if (exportSuccess)
            {
                MessageBox.Show("数据已成功导出到Excel", "成功", MessageBoxButton.OK, MessageBoxImage.Information);
            }
            else
            {
                MessageBox.Show("Excel导出失败，请检查文件是否被占用", "错误", MessageBoxButton.OK, MessageBoxImage.Error);
            }
        }

        private List<CO2Record> ReadCO2RecordsFromDatabase(DateTime? startDate, DateTime? endDate)
        {
            List<CO2Record> records = new List<CO2Record>();
            try
            {
                using (SQLiteConnection conn = new SQLiteConnection(CSConstant.DbConnectionString))
                {
                    conn.Open();
                    // 构建查询语句，根据开始时间和结束时间进行筛选
                    string query = "SELECT Flow, Pressure, BeginCO2, EndCO2, CO2Added, RecordTime FROM CO2 WHERE 1=1";

                    if (startDate != null)
                    {
                        query += " AND RecordTime >= @StartDate";
                    }

                    if (endDate != null)
                    {
                        query += " AND RecordTime <= @EndDate";
                    }

                    query += " ORDER BY RecordTime";

                    using (SQLiteCommand cmd = new SQLiteCommand(query, conn))
                    {
                        if (startDate != null)
                        {
                            cmd.Parameters.AddWithValue("@StartDate", startDate);
                        }

                        if (endDate != null)
                        {
                            cmd.Parameters.AddWithValue("@EndDate", endDate);
                        }

                        using (SQLiteDataReader reader = cmd.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                records.Add(new CO2Record
                                {
                                    Flow = reader.GetDouble(0),               // 二氧化碳浓度（%）
                                    Pressure = reader.GetDouble(1),          // 压力（pa）
                                    BeginCO2 = reader.GetDouble(2),          // 开始CO2浓度（%）
                                    EndCO2 = reader.GetDouble(3),            // 终CO2浓度（%）
                                    CO2Added = reader.GetDouble(4),          // CO2浓度相对增加（%）
                                    RecordTime = reader.GetDateTime(5)       // 时间
                                });
                            }
                        }
                    }
                }
                return records;
            }
            catch (Exception ex)
            {
                MessageBox.Show($"读取CO2表失败：{ex.Message}", "错误", MessageBoxButton.OK, MessageBoxImage.Error);
                return null;
            }
        }


        /// <summary>
        /// 将CO2数据导出到Excel（WPF .NET 8 适配版）
        /// </summary>
        private bool ExportCO2RecordsToExcel(List<CO2Record> records)
        {
            try
            {
                // WPF 原生保存对话框（替代 WinForms）

                SaveFileDialog saveDialog = new SaveFileDialog
                {
                    Filter = "Excel文件 (*.xlsx)|*.xlsx",
                    FileName = $"CO2过程记录导出_{DateTime.Now:yyyyMMddHHmmss}.xlsx",
                    Title = "保存CO2记录"
                };

                // 用户取消选择则返回
                bool? result = saveDialog.ShowDialog(); if (!(result ?? false)) return false;
                 

                // EPPlus 许可设置（.NET 8 必须显式设置）
                ExcelPackage.LicenseContext = LicenseContext.NonCommercial;

                // 创建并写入Excel
                using (ExcelPackage package = new ExcelPackage(new FileInfo(saveDialog.FileName)))
                {
                    ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("CO2记录");

                    // 表头（对应DataGrid列）
                    worksheet.Cells[1, 1].Value = "二氧化碳浓度（%）";
                    worksheet.Cells[1, 2].Value = "压力（pa）";

                    worksheet.Cells[1, 3].Value = "时间";

                    // 表头样式（加粗、居中）
                    using (var headerRange = worksheet.Cells[1, 1, 1, 6])
                    {
                        headerRange.Style.Font.Bold = true;
                        headerRange.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
                    }

                    // 填充数据（从第2行开始）
                    for (int i = 0; i < records.Count; i++)
                    {
                        int row = i + 2;
                        var record = records[i];
                        worksheet.Cells[row, 1].Value = record.Flow;
                        worksheet.Cells[row, 2].Value = record.Pressure;
                        //worksheet.Cells[row, 1].Value = record.BeginCO2;
                        //worksheet.Cells[row, 2].Value = record.EndCO2;
                        //worksheet.Cells[row, 3].Value = record.CO2Added;
                        worksheet.Cells[row, 3].Value = record.RecordTime.ToString("yyyy-MM-dd HH:mm:ss");
                    }

                    // 自动调整列宽
                    worksheet.Cells.AutoFitColumns();

                    // 保存文件
                    package.Save();
                }

                return true;
            }
            catch (Exception ex)
            {
                MessageBox.Show($"导出失败：{ex.Message}", "错误", MessageBoxButton.OK, MessageBoxImage.Error);
                return false;
            }
        }

        private void HomePage_Click(object sender, RoutedEventArgs e)
        {
            this.Close();
        }

        private void SelectAllButton_Click(object sender, RoutedEventArgs e)
        {
            // 检查是否有数据
            if (_records == null || !_records.Any())
                return;

            // 判断当前是否已经全选
            bool allSelected = _records.All(r => r.IsSelected);

            // 如果已经全选则取消全选，否则全选
            foreach (var record in _records)
            {
                record.IsSelected = !allSelected;
            }

            // 刷新DataGrid以显示更新后的选中状态
            DataGridReport.Items.Refresh();
        }
    }
}