Sleep-Multi-functionality/ReportWindow2.xaml.cs

using Microsoft.Win32;
using OfficeOpenXml;
using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Configuration;
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 AirRecordsRecordWithSelection : FlowPressureRecord
    {
        public bool IsSelected { get; set; }
    }

    public partial class ReportWindow2 : Window
    {
        private readonly string _lang = ConfigurationManager.AppSettings["Language"] ?? "zh-CN";

        private const int PageSize = 10;
        private int currentPage = 1;
        private int totalRecords = 0;

        // 新增：保存当前查询条件，用于删除后刷新
        private string _currentStartDate = null;
        private string _currentEndDate = null;
        // 新增：使用ObservableCollection支持数据绑定刷新
        private ObservableCollection<AirRecordsRecordWithSelection> _records = new ObservableCollection<AirRecordsRecordWithSelection>();

        public ReportWindow2()
        {
            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, RecordTime FROM AirRecords";
                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);

                    //List<AirRecordsRecordWithSelection> records = new List<AirRecordsRecordWithSelection>();
                    _records.Clear(); // 清空现有数据
                    using (SQLiteDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            _records.Add(new AirRecordsRecordWithSelection
                            {
                                Id = reader.GetInt32(0),
                                Flow = reader.GetDouble(1),
                                Pressure = reader.GetDouble(2),
                                RecordTime = reader.GetDateTime(3),
                                IsSelected = false // 默认未选中
                            });
                        }
                    }

                    DataGridReport.ItemsSource = _records;
                }

                // 获取总记录数，考虑当前查询条件
                string countQuery = "SELECT COUNT(*) FROM AirRecords";
                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()
        {
            if (_lang == "en-US")
                PageInfo.Text = $"Page {currentPage} / {Math.Ceiling((double)totalRecords / PageSize)} | Total: {totalRecords} records";
            else
                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)
            {
                MessageBoxResult result;

                if (_lang == "en-US")
                    result = MessageBox.Show($"Delete record ID: {id} ?", "Confirm Delete", MessageBoxButton.YesNo, MessageBoxImage.Warning);
                else
                    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())
            {
                if (_lang == "en-US")
                    MessageBox.Show("Please select records to delete", "Tip", MessageBoxButton.OK, MessageBoxImage.Information);
                else
                    MessageBox.Show("请先选择要删除的记录", "提示", MessageBoxButton.OK, MessageBoxImage.Information);
                return;
            }

            MessageBoxResult result;
            if (_lang == "en-US")
                result = MessageBox.Show($"Delete {selectedIds.Count} selected records?", "Confirm Batch Delete", MessageBoxButton.YesNo, MessageBoxImage.Warning);
            else
                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 AirRecords 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 AirRecords 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);
            }

            List<CO2Record> records = ReadCO2RecordsFromDatabase(startDate, endDate);
            if (records == null || !records.Any())
            {
                if (_lang == "en-US")
                    MessageBox.Show("No data to export", "Tip", MessageBoxButton.OK, MessageBoxImage.Warning);
                else
                    MessageBox.Show("气阻表中无数据，无法导出", "提示", MessageBoxButton.OK, MessageBoxImage.Warning);
                return;
            }

            bool success = ExportCO2RecordsToExcel(records);
            if (success)
            {
                if (_lang == "en-US")
                    MessageBox.Show("Export successful!", "Success", MessageBoxButton.OK, MessageBoxImage.Information);
                else
                    MessageBox.Show("数据已成功导出到Excel", "成功", MessageBoxButton.OK, MessageBoxImage.Information);
            }
            else
            {
                if (_lang == "en-US")
                    MessageBox.Show("Export failed! File may be in use.", "Error", MessageBoxButton.OK, MessageBoxImage.Error);
                else
                    MessageBox.Show("Excel导出失败，请检查文件是否被占用", "错误", MessageBoxButton.OK, MessageBoxImage.Error);
            }
        }


        private bool ExportCO2RecordsToExcel(List<CO2Record> records)
        {
            try
            {
                SaveFileDialog saveDialog = new SaveFileDialog
                {
                    Filter = _lang == "en-US" ? "Excel File (*.xlsx)|*.xlsx" : "Excel文件 (*.xlsx)|*.xlsx",
                    FileName = $"AirResistance_{DateTime.Now:yyyyMMddHHmmss}.xlsx",
                    Title = _lang == "en-US" ? "Save Air Resistance Records" : "保存气阻记录"
                };

                bool? result = saveDialog.ShowDialog();
                if (!(result ?? false)) return false;

                ExcelPackage.LicenseContext = LicenseContext.NonCommercial;

                using (ExcelPackage package = new ExcelPackage(new FileInfo(saveDialog.FileName)))
                {
                    ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Air Resistance");

                    // 双语表头
                    if (_lang == "en-US")
                    {
                        worksheet.Cells[1, 1].Value = "Air Resistance (hPa)";
                        worksheet.Cells[1, 2].Value = "Flow (L/min)";
                        worksheet.Cells[1, 3].Value = "Time";
                    }
                    else
                    {
                        worksheet.Cells[1, 1].Value = "气阻hpa";
                        worksheet.Cells[1, 2].Value = "流量L/min";
                        worksheet.Cells[1, 3].Value = "时间";
                    }

                    using (var header = worksheet.Cells[1, 1, 1, 3])
                    {
                        header.Style.Font.Bold = true;
                        header.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
                    }

                    for (int i = 0; i < records.Count; i++)
                    {
                        int row = i + 2;
                        worksheet.Cells[row, 1].Value = records[i].Flow;
                        worksheet.Cells[row, 2].Value = records[i].Pressure;
                        worksheet.Cells[row, 3].Value = records[i].RecordTime.ToString("yyyy-MM-dd HH:mm:ss");
                    }

                    worksheet.Cells.AutoFitColumns();
                    package.Save();
                }
                return true;
            }
            catch (Exception ex)
            {
                if (_lang == "en-US")
                    MessageBox.Show($"Export failed: {ex.Message}", "Error", MessageBoxButton.OK, MessageBoxImage.Error);
                else
                    MessageBox.Show($"导出失败：{ex.Message}", "错误", MessageBoxButton.OK, MessageBoxImage.Error);
                return false;
            }
        }


        /// <summary>
        /// 
        /// </summary>
        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, RecordTime FROM AirRecords 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）                                 
                                    RecordTime = reader.GetDateTime(2)       // 时间
                                });
                            }
                        }
                    }
                }
                return records;
            }
            catch (Exception ex)
            {
                MessageBox.Show($"读取气阻表失败：{ex.Message}", "错误", MessageBoxButton.OK, MessageBoxImage.Error);
                return null;
            }
        }



        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();
        }
    }
}