parking-webapi/WebApi_data_value/Controllers/ParkingLogsController.cs

using System;
using System.Collections.Generic;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Threading.Tasks;
using ClosedXML.Excel;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using Parking_space_WebAPI.Services;
using WebApi_data_value.Models;

namespace WebApi_data_value.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class ParkingLogsController : ControllerBase
    {
        private readonly SqlContext _context;

        public ParkingLogsController(SqlContext context)
        {
            _context = context;
        }

        // 新增停車紀錄
        [HttpPost]
        public async Task<IActionResult> AddParkingLog([FromBody] ParkingLog parkingLog)
        {
            // 確認模型有效
            if (!ModelState.IsValid)
            {
                return BadRequest(new { message = "請求的模型無效。" });
            }


            _context.ParkingLogs.Add(parkingLog);

            // 儲存變更到資料庫
            await _context.SaveChangesAsync();


            return CreatedAtAction(nameof(GetParkingLogById), new { id = parkingLog.Id }, parkingLog);
        }

        // 根據 ID 獲取停車紀錄
        [HttpGet("{id}")]
        public async Task<IActionResult> GetParkingLogById(int id)
        {
            var parkingLog = await _context.ParkingLogs.FindAsync(id);
            if (parkingLog == null)
            {
                return NotFound(new { message = "找不到該停車紀錄。" });
            }

            return Ok(parkingLog);
        }

        #region  下載即時車位 Excel 檔案
        [HttpGet("DownloadCurrentStatusExcel")]
        public async Task<IActionResult> DownloadCurrentStatusExcel()
        {

            DateTime currentTime = DateTime.Now;


            var parkingLog = await _context.ParkingLogs
                .OrderByDescending(p => p.Timestamp)
                .FirstOrDefaultAsync(p => p.Timestamp <= currentTime);

            if (parkingLog == null)
            {
                return NotFound(new { message = "找不到即時車位資料。" });
            }

            using (var workbook = new XLWorkbook())
            {
                var worksheet = workbook.Worksheets.Add("即時車位資訊");
                worksheet.Cell(1, 1).Value = "時間";
                worksheet.Cell(1, 2).Value = "星期";
                worksheet.Cell(1, 3).Value = "總車位";
                worksheet.Cell(1, 4).Value = "剩餘車位";
                worksheet.Cell(1, 5).Value = "月租車位";
                worksheet.Cell(1, 6).Value = "臨停車位";
                worksheet.Cell(1, 7).Value = "下載日期";


                worksheet.Cell(2, 1).Value = parkingLog.Timestamp.ToString("yyyy/MM/dd HH:mm:ss");
                worksheet.Cell(2, 2).Value = DateTime.Now.ToString("dddd", new CultureInfo("zh-TW"));
                worksheet.Cell(2, 3).Value = parkingLog.TotalParkingSpaces;
                worksheet.Cell(2, 4).Value = parkingLog.RemainingSpaces;
                worksheet.Cell(2, 5).Value = parkingLog.MonthlyRentSpaces;
                worksheet.Cell(2, 6).Value = parkingLog.TemporaryRentSpaces;
                worksheet.Cell(2, 7).Value = DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss dddd");

                // 設定欄位寬度
                worksheet.Column(1).Width = 25;
                worksheet.Column(2).Width = 10;
                worksheet.Column(3).Width = 15;
                worksheet.Column(4).Width = 15;
                worksheet.Column(5).Width = 15;
                worksheet.Column(6).Width = 15;
                worksheet.Column(7).Width = 25;

                using (var stream = new MemoryStream())
                {
                    workbook.SaveAs(stream);
                    var content = stream.ToArray();

                    // 生成檔案名稱
                    string currentDate = DateTime.Now.ToString("yyyy-MM-dd");
                    string weekDayDownload = DateTime.Now.ToString("dddd", new CultureInfo("zh-TW")); // 獲取當前星期幾

                    string fileName = $"即時車位-{currentDate}-{weekDayDownload}.xlsx";

                    return File(content, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", fileName);
                }
            }
        }
        #endregion

        #region  下載 Excel 檔案
        [HttpGet("DownloadExcel")]
        public async Task<IActionResult> DownloadExcel(DateTime startDate, DateTime endDate, string interval)
        {
            
            if (startDate == default || endDate == default)
            {
                return BadRequest(new { message = "日期無效。" });
            }

            
            if (startDate >= endDate)
            {
                return BadRequest(new { message = "結束日期必須晚於開始日期。" });
            }

            // 調用 GetHourlyParkingLogs 方法以獲取所有停車紀錄
            var parkingLogsResult = await GetHourlyParkingLogs(startDate, endDate, interval, 1, int.MaxValue); 

            if (parkingLogsResult is NotFoundResult)
            {
                return NotFound(new { message = "找不到該日期範圍的停車紀錄。" });
            }

            var logs = (parkingLogsResult as OkObjectResult).Value as dynamic;
            var records = logs.logs; // 獲取停車紀錄

            using (var workbook = new XLWorkbook())
            {
                var worksheet = workbook.Worksheets.Add("停車紀錄");
                worksheet.Cell(1, 1).Value = "時間";
                worksheet.Cell(1, 2).Value = "星期";
                worksheet.Cell(1, 3).Value = "總車位";
                worksheet.Cell(1, 4).Value = "剩餘車位";
                worksheet.Cell(1, 5).Value = "月租車位";
                worksheet.Cell(1, 6).Value = "臨停車位";
                worksheet.Cell(1, 7).Value = "下載日期";

               
                for (int i = 0; i < records.Count; i++)
                {
                    var log = records[i];
                    worksheet.Cell(i + 2, 1).Value = log.Timestamp.ToString("yyyy/MM/dd HH:mm:ss");
                    worksheet.Cell(i + 2, 2).Value = log.DayOfWeek;
                    worksheet.Cell(i + 2, 3).Value = log.TotalParkingSpaces;
                    worksheet.Cell(i + 2, 4).Value = log.RemainingSpaces;
                    worksheet.Cell(i + 2, 5).Value = log.MonthlyRentSpaces;
                    worksheet.Cell(i + 2, 6).Value = log.TemporaryRentSpaces;
                   
                }
                worksheet.Cell( 2, 7).Value = DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss dddd");
                worksheet.Column(1).Width = 25;
                worksheet.Column(2).Width = 10;
                worksheet.Column(3).Width = 15;
                worksheet.Column(4).Width = 15;
                worksheet.Column(5).Width = 15;
                worksheet.Column(6).Width = 15;
                worksheet.Column(7).Width = 25;

                using (var stream = new MemoryStream())
                {
                    workbook.SaveAs(stream);
                    var content = stream.ToArray();

                    string currentDate = DateTime.Now.ToString("yyyy-MM-dd");
                    string weekDay = DateTime.Now.ToString("dddd", new CultureInfo("zh-TW"));

                    string fileName = $"停車紀錄-{currentDate}-{weekDay}.xlsx";

                    return File(content, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", fileName);
                }
            }
            
        }
        #endregion


        #region  自動下載 Excel 檔案
        [HttpGet("AutoDownloadExcel")]
        public async Task<IActionResult> AutoDownloadExcel()
        {
            string string_startDate = "2024-10-01";
            DateTime startDate = DateTime.ParseExact(string_startDate, "yyyy-MM-dd", System.Globalization.CultureInfo.InvariantCulture);

            var endDate = DateTime.Now;
            if (startDate == default || endDate == default)
            {
                return BadRequest(new { message = "日期無效。" });
            }

            // 調用 GetHourlyParkingLogs 方法以獲取所有停車紀錄
            var parkingLogsResult = await GetHourlyParkingLogs(startDate, endDate, "1", 1, int.MaxValue);

            if (parkingLogsResult is NotFoundResult)
            {
                return NotFound(new { message = "找不到該日期範圍的停車紀錄。" });
            }

            var logs = (parkingLogsResult as OkObjectResult).Value as dynamic;
            var records = logs.logs; // 獲取停車紀錄

            using (var workbook = new XLWorkbook())
            {
                var worksheet = workbook.Worksheets.Add("停車紀錄");
                worksheet.Cell(1, 1).Value = "時間";
                worksheet.Cell(1, 2).Value = "星期";
                worksheet.Cell(1, 3).Value = "總車位";
                worksheet.Cell(1, 4).Value = "剩餘車位";
                worksheet.Cell(1, 5).Value = "月租車位";
                worksheet.Cell(1, 6).Value = "臨停車位";
                worksheet.Cell(1, 7).Value = "下載日期";


                for (int i = 0; i < records.Count; i++)
                {
                    var log = records[i];
                    worksheet.Cell(i + 2, 1).Value = log.Timestamp.ToString("yyyy/MM/dd HH:mm:ss");
                    worksheet.Cell(i + 2, 2).Value = log.DayOfWeek;
                    worksheet.Cell(i + 2, 3).Value = log.TotalParkingSpaces;
                    worksheet.Cell(i + 2, 4).Value = log.RemainingSpaces;
                    worksheet.Cell(i + 2, 5).Value = log.MonthlyRentSpaces;
                    worksheet.Cell(i + 2, 6).Value = log.TemporaryRentSpaces;
                    worksheet.Cell(i + 2, 7).Value = DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss dddd");
                }


                worksheet.Column(1).Width = 25;
                worksheet.Column(2).Width = 10;
                worksheet.Column(3).Width = 15;
                worksheet.Column(4).Width = 15;
                worksheet.Column(5).Width = 15;
                worksheet.Column(6).Width = 15;
                worksheet.Column(7).Width = 25;

                using (var stream = new MemoryStream())
                {
                    workbook.SaveAs(stream);
                    var content = stream.ToArray();

                    string currentDate = DateTime.Now.ToString("yyyy-MM-dd");
                    string weekDay = DateTime.Now.ToString("dddd", new CultureInfo("zh-TW"));

                    string fileName = $"停車紀錄-{currentDate}-{weekDay}.xlsx";

                    return File(content, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", fileName);
                  }
             }
         }
        #endregion

        #region  獲取停車紀錄
        private async Task<IActionResult> GetParkingLogs(DateTime startDate, DateTime endDate, int pageNumber, int pageSize)
        {

            if (startDate == default || endDate == default)
            {
                return BadRequest(new { message = "日期無效。" });
            }


            if (startDate >= endDate)
            {
                return BadRequest(new { message = "結束日期必須晚於開始日期。" });
            }


            var parkingLogs = await _context.ParkingLogs
                .AsNoTracking()
                .Where(p => p.Timestamp >= startDate && p.Timestamp <= endDate)
                .OrderByDescending(p => p.Timestamp)
                .Skip((pageNumber - 1) * pageSize)
                .Take(pageSize)
                .ToListAsync();

            if (!parkingLogs.Any())
            {
                return NotFound(new { message = "找不到該日期範圍的停車紀錄。" });
            }

            return Ok(new
            {
                totalRecords = await _context.ParkingLogs.CountAsync(p => p.Timestamp >= startDate && p.Timestamp <= endDate), // 總紀錄數
                records = parkingLogs
            });
        }
        
        [HttpGet("GetHourlyParkingLogs")]
        public async Task<IActionResult> GetHourlyParkingLogs(DateTime startDate, DateTime endDate, string interval, int page = 1, int recordsPerPage = 20)
        {
            if (startDate == default || endDate == default)
            {
                return BadRequest(new { message = "日期無效。" });
            }

            if (startDate >= endDate)
            {
                return BadRequest(new { message = "結束日期必須晚於開始日期。" });
            }

            // 查詢指定時間範圍的停車紀錄
            var parkingLogs = await _context.ParkingLogs
                .AsNoTracking()
                .Where(p => p.Timestamp >= startDate && p.Timestamp <= endDate)
                .ToListAsync();

            if (!parkingLogs.Any())
            {
                return NotFound(new { message = "找不到該日期範圍的停車紀錄。" });
            }

            // 設定時間間隔
            if (interval == "none")
            {
                var formattedLogs = parkingLogs.Select(log => new
                {
                    log.Timestamp,
                    DayOfWeek = log.Timestamp.ToString("dddd", new CultureInfo("zh-TW")),
                    log.TotalParkingSpaces,
                    log.RemainingSpaces,
                    log.MonthlyRentSpaces,
                    log.TemporaryRentSpaces
                }).ToList();

                var totalRecords = formattedLogs.Count;
                var pagedLogs = formattedLogs.Skip((page - 1) * recordsPerPage).Take(recordsPerPage).ToList();

                return Ok(new
                {
                    totalRecords = totalRecords,
                    logs = pagedLogs
                });
            }
            else
            {
                // 將 interval 轉換為整數
                int hourInterval = int.Parse(interval);
                var formattedLogs = new List<object>();
                for (DateTime currentTime = startDate; currentTime <= endDate; currentTime = currentTime.AddHours(hourInterval))
                {
                    // 查找該時間的停車紀錄
                    var log = parkingLogs.FirstOrDefault(p => p.Timestamp.Year == currentTime.Year &&
                                                               p.Timestamp.Month == currentTime.Month &&
                                                               p.Timestamp.Day == currentTime.Day &&
                                                               p.Timestamp.Hour == currentTime.Hour);

                    if (log != null)
                    {
                        formattedLogs.Add(new
                        {
                            Timestamp = log.Timestamp,
                            DayOfWeek = log.Timestamp.ToString("dddd", new CultureInfo("zh-TW")),
                            log.TotalParkingSpaces,
                            log.RemainingSpaces,
                            log.MonthlyRentSpaces,
                            log.TemporaryRentSpaces
                        });
                    }
                }

                if (!formattedLogs.Any())
                {
                    return NotFound(new { message = "找不到該日期範圍的整點停車紀錄。" });
                }

                // 分頁處理
                var totalRecords = formattedLogs.Count;
                var pagedLogs = formattedLogs.Skip((page - 1) * recordsPerPage).Take(recordsPerPage).ToList();

                return Ok(new
                {
                    totalRecords = totalRecords,
                    logs = pagedLogs
                });
            }
        }
        #endregion
    }

}