using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using Parking_space_WebAPI.Models;
using Parking_space_WebAPI.Services;
using Parking_space_WebAPI.ViewModel;
using Parking_space_WebAPI.Authorization;
using OfficeOpenXml;


namespace Parking_space_WebAPI.Controllers
{

    [Route("api/[controller]")]
    [ApiController]
    [Authorize]
    [ApiExplorerSettings(GroupName = "校園大內網")]
    public class Yuntech_in_car_tableController : ControllerBase
    {
        private readonly SqlContext _context;

        public Yuntech_in_car_tableController(SqlContext context)
        {
            _context = context;
        }
        #region 獲取進入雲科的所有資料
        /// <summary>
        /// 獲取進入雲科的所有資料
        /// </summary>
        // GET: api/Yuntech_in_car_table
        [HttpGet]
        public async Task<ActionResult<IEnumerable<Yuntech_in_car_table>>> Getyuntech_in_car_table()
        {
            var in_car_table = await (from c in _context.yuntech_in_car_table
                                      orderby c.in_time descending
                                      select new Yuntech_in_car_table
                                      {
                                          license_plate_number = c.license_plate_number,
                                          in_time = c.in_time,
                                          out_time = c.out_time,
                                          location = c.location,
                                          out_location=c.out_location,
                                      }
                                      ).ToListAsync();


            return in_car_table;
        }
        #endregion


        #region 獲取進入雲科的100筆資料
        /// <summary>
        /// 獲取進入雲科的100筆資料
        /// </summary>
        // GET: api/Yuntech_in_car_table
        [HttpGet("Amount-{num}")]
        public async Task<ActionResult<IEnumerable<Yuntech_in_car_table>>> Getyuntech_in_car_100_table(int num)
        {
            var in_car_table = await (from c in _context.yuntech_in_car_table
                                      orderby c.in_time descending
                                      select new Yuntech_in_car_table
                                      {
                                          license_plate_number = c.license_plate_number,
                                          in_time = c.in_time,
                                          out_time = c.out_time,
                                          location = c.location,
                                      }
                                      ).Skip(num).Take(100).ToListAsync();


            return in_car_table;
        }
        #endregion

        #region 獲取進入的單獨地區的所有資料
        /// <summary>
        /// 獲取進入的單獨地區的所有資料
        /// </summary>
        /// <param name="id"> 哪個門口</param>
        /// <returns></returns>
        // GET: api/Yuntech_in_car_table/5
        [HttpGet("location-{id}")]
        public async Task<IEnumerable<Yuntech_in_car_table>> GetYuntech_in_car_table(string id)
        {
            var in_car_table = await (from c in _context.yuntech_in_car_table
                                      where c.location == id
                                      orderby c.in_time descending
                                      select new Yuntech_in_car_table
                                      {
                                          license_plate_number = c.license_plate_number,
                                          in_time = c.in_time,
                                          out_time = c.out_time,
                                          location = c.location,
                                      }).Skip(0).Take(500).ToListAsync();


            return in_car_table;
        }
        #endregion

        #region 獲取時間內的車輛
        /// <summary>
        /// 獲取時間內的車輛
        /// </summary>
        /// <param name="id">位置名稱</param>
        /// <param name="start_time">起始時間</param>
        /// <param name="end_time">結束時間</param>
        /// <returns></returns>
        
        [HttpGet("location_name_1_-{id}-start_time-{start_time}-end_time-{end_time}")]
        public async Task<IEnumerable<Yuntech_in_car_table>> GetYuntech_in_car_table_date(string id, DateTime start_time, DateTime end_time)
        {

            var in_car_table = await (from c in _context.yuntech_in_car_table
                                      where c.location == id
                                      where c.in_time >= start_time
                                      where c.in_time <= end_time
                                      orderby c.in_time descending // 按進入時間降序排列
                                      select new Yuntech_in_car_table
                                      {
                                          license_plate_number = c.license_plate_number,
                                          in_time = c.in_time,
                                          out_time = c.out_time,
                                          location = c.location,
                                      }).ToListAsync();


            return in_car_table;
        }
        #endregion

        #region 透過車牌號碼搜尋所有資料
        /// <summary>
        /// 透過車牌號碼搜尋所有資料
        /// </summary>
        /// <param name="id">車牌號碼 ex:ABC4321</param>
        /// <returns></returns>
        [HttpGet("license_plate_number-{id}")]
        public async Task<IEnumerable<Yuntech_in_car_table>> Getlicense_plate_number(string id)
        {

            var in_car_table = await (from c in _context.yuntech_in_car_table
                                      where c.license_plate_number == id
                                      orderby c.in_time descending
                                      select new Yuntech_in_car_table
                                      {
                                          license_plate_number = c.license_plate_number,
                                          in_time = c.in_time,
                                          out_time = c.out_time,
                                          location = c.location,
                                      }).ToListAsync();


            return in_car_table;
        }
        #endregion

        #region 獲取進入的單獨地區的單筆資料
        /// <summary>
        /// 獲取進入的單獨地區的單筆資料
        /// </summary>
        /// <param name="id">地區</param>
        /// <param name="time">進入時間 ex:2024-02-05T12:21:48.395Z</param>
        /// <returns></returns>
        // GET: api/Yuntech_in_car_table/5
        [HttpGet("location_name-{id}-time-{time}")]
        public async Task<ActionResult<Yuntech_in_car_table>> One_data(string id, DateTime time)
        {
            var in_car_table = await (from c in _context.yuntech_in_car_table
                                      where c.location == id
                                      where c.in_time == time
                                      select new Yuntech_in_car_table
                                      {
                                          license_plate_number = c.license_plate_number,
                                          in_time = c.in_time,
                                          out_time = c.out_time,
                                          car_img = c.car_img,
                                          location = c.location,
                                          out_car_img = c.out_car_img,
                                          out_location= c.out_location,
                                      }).FirstAsync();


            return in_car_table;
        }
        #endregion

        #region 尋找規定時間內每日進入人數
        /// <summary>
        /// 尋找規定時間內每日進入人數
        /// </summary>
        /// <returns></returns>
        [HttpGet("get_day_in_car-{day}")]
        public async Task<IEnumerable<object>> Getdayvalue(int day)
        {
            // 取得今日日期
            DateTime today = DateTime.Today;

            // 取得 30 天前的日期
            DateTime DaysAgo = today.AddDays(-day);
            var dateValue = new List<object>();
            // 使用 for 迴圈逐日計算次數
            for (int i = 1; i <= day; i++)
            {
                DateTime date_1 = DaysAgo;
                date_1 = date_1.AddDays(i);
                DateTime date_2 = DaysAgo;
                date_2 = date_2.AddDays(i + 1);
                var dailyCount = await _context.yuntech_in_car_table
                    .Where(c => c.in_time >= date_1.Date) // 只選擇指定日期的資料
                    .Where(c => c.in_time <= date_2.Date) // 只選擇指定日期的資料
                    .CountAsync(); // 計算該日期的次數

                dateValue.Add(new { Date = date_1, Occurrences = dailyCount });
            }




            return dateValue;
        }
        #endregion

        #region 尋找所有進出校園的地點
        ///<summary>
        ///尋找所有進出校園的地點
        /// </summary>
        /// <returns></returns>
        [HttpGet("location_name")]
        public async Task<IEnumerable<object>> Get_in_Yuntech_location_name()
        {
            var location_name_table = await _context.yuntech_in_car_table
                                        .GroupBy(c=>c.location)
                                        .Select(group => new
                                        {
                                            location_name = group.Key,
                                        })
                                        .ToListAsync();


            return location_name_table;
        }
        #endregion
       

        #region 新增資料
        /// <summary>
        /// 新增進入雲科的車輛資料
        /// </summary>
        /// <param name="yuntech_in_car_table"></param>
        /// <returns></returns>
        // POST: api/Yuntech_in_car_table
        // To protect from overposting attacks, see https://go.microsoft.com/fwlink/?linkid=2123754
        [HttpPost]
        public async Task<ActionResult<Yuntech_in_car_table>> PostYuntech_in_car_table(Yuntech_in_car_table yuntech_in_car_table)
        {
          if (_context.yuntech_in_car_table == null)
          {
              return Problem("Entity set 'SqlContext.yuntech_in_car_table'  is null.");
          }
            
            try
            {
                //比對1分鐘內有無重複記錄到
                DateTime now_time = DateTime.Now;
                // 取得 59秒前的時間
                DateTime time_59s_ago = now_time.AddSeconds(-59);
                var in_car_data = await (from c in _context.yuntech_in_car_table
                                         where c.license_plate_number == yuntech_in_car_table.license_plate_number
                                         where c.out_time == null
                                         orderby c.in_time descending
                                         select c).FirstOrDefaultAsync();
                if (in_car_data != null && in_car_data.in_time > time_59s_ago)
                {
                    return Ok();
                }

                //比對30秒內出去與進入的時間對比
                //取得30秒前的時間
                DateTime time_30s_ago = now_time.AddSeconds(-30);
                var out_car_data = await (from c in _context.yuntech_in_car_table
                                         where c.license_plate_number == yuntech_in_car_table.license_plate_number
                                         orderby c.out_time descending
                                         select c).FirstOrDefaultAsync();
                if (out_car_data != null && out_car_data.out_time > time_30s_ago)
                {
                    return Ok(out_car_data);
                }

                // 新增資料
                yuntech_in_car_table.in_time = DateTime.Now;
                yuntech_in_car_table.out_time = null;
                _context.yuntech_in_car_table.Add(yuntech_in_car_table);
                await _context.SaveChangesAsync();

                // 車位數量-1
                var yuntech_parking = await _context.yuntech_parking.FirstOrDefaultAsync();
                if (yuntech_parking != null)
                {
                    string now_num_str = yuntech_parking.now_num;
                    int now_num_int;
                    Int32.TryParse(now_num_str, out now_num_int);
                    now_num_int = now_num_int - 1;
                    now_num_str=now_num_int.ToString();
                    yuntech_parking.now_num = now_num_str;
                    // 保存更改
                    await _context.SaveChangesAsync();
                }
                //判斷月租臨停車輛 +1
                // 與名單比對車牌號碼
                var in_list_data = await _context.yuntech_parking_user_list
                    .FirstOrDefaultAsync(user => user.user_license_plate_number == yuntech_in_car_table.license_plate_number);
                // 車牌號碼相同，更新月租車位數量
                if (in_list_data != null)
                {
                    var monthly_rent_data_1 = await _context.yuntech_monthly_rent_number
                        .FirstOrDefaultAsync(rent => rent.category == "月租");
                    if (monthly_rent_data_1 != null)
                    {
                        int now_number;
                        Int32.TryParse(monthly_rent_data_1.number, out now_number);
                        now_number = now_number + 1;
                        monthly_rent_data_1.number = now_number.ToString();
                        // 保存更改
                        await _context.SaveChangesAsync();
                    }
                }
                else {
                    var monthly_rent_data_2 = await _context.yuntech_monthly_rent_number
                        .FirstOrDefaultAsync(rent => rent.category == "臨停");
                    if (monthly_rent_data_2 != null)
                    {
                        int now_number;
                        Int32.TryParse(monthly_rent_data_2.number, out now_number);
                        now_number = now_number + 1;
                        monthly_rent_data_2.number = now_number.ToString();
                        // 保存更改
                        await _context.SaveChangesAsync();
                    }
                }
                
                

            }
            catch (DbUpdateException)
            {
                if (Yuntech_in_car_tableExists(yuntech_in_car_table.in_time))
                {
                    return Conflict();
                }
                else
                {
                    throw;
                }
            }

            return CreatedAtAction("GetYuntech_in_car_table", new { id = yuntech_in_car_table.in_time }, yuntech_in_car_table);
        }
        #endregion


        #region City_parking 新增進入資料
        /// <summary>
        /// City_parking 新增進入資料
        /// </summary>
        /// <param name="yuntech_in_car_table"></param>
        /// <returns></returns>
        // POST: api/Yuntech_in_car_table
        // To protect from overposting attacks, see https://go.microsoft.com/fwlink/?linkid=2123754
        [HttpPost("city_parking_in_car_data")]
        public async Task<ActionResult<Yuntech_in_car_table>> PostYuntech_in_car_table_for__city_parking(Yuntech_in_car_table yuntech_in_car_table)
        {
            if (_context.yuntech_in_car_table == null)
            {
                return Problem("Entity set 'SqlContext.yuntech_in_car_table'  is null.");
            }

            try
            {
                //比對1分鐘內有無重複記錄到
                DateTime now_time = DateTime.Now;
                // 取得 59秒前的時間
                DateTime time_59s_ago = now_time.AddSeconds(-59);
                var in_car_data = await (from c in _context.yuntech_in_car_table
                                         where c.license_plate_number == yuntech_in_car_table.license_plate_number
                                         where c.out_time == null
                                         orderby c.in_time descending
                                         select c).FirstOrDefaultAsync();
                if (in_car_data != null && in_car_data.in_time > time_59s_ago)
                {
                    return Ok();
                }

                //比對30秒內出去與進入的時間對比
                //取得30秒前的時間
                DateTime time_30s_ago = now_time.AddSeconds(-30);
                var out_car_data = await (from c in _context.yuntech_in_car_table
                                          where c.license_plate_number == yuntech_in_car_table.license_plate_number
                                          orderby c.out_time descending
                                          select c).FirstOrDefaultAsync();
                if (out_car_data != null && out_car_data.out_time > time_30s_ago)
                {
                    return Ok(out_car_data);
                }

                // 新增資料
                yuntech_in_car_table.in_time = DateTime.Now;
                yuntech_in_car_table.out_time = null;
                _context.yuntech_in_car_table.Add(yuntech_in_car_table);
                await _context.SaveChangesAsync();

                // 車位數量-1
                var yuntech_parking = await _context.yuntech_parking.FirstOrDefaultAsync();
                if (yuntech_parking != null)
                {
                    string now_num_str = yuntech_parking.now_num;
                    int now_num_int;
                    Int32.TryParse(now_num_str, out now_num_int);
                    now_num_int = now_num_int - 1;
                    now_num_str = now_num_int.ToString();
                    yuntech_parking.now_num = now_num_str;
                    // 保存更改
                    await _context.SaveChangesAsync();
                }

                //判斷月租臨停車輛 +1
                // 與名單比對車牌號碼
                var in_list_data = await _context.yuntech_parking_user_list
                    .FirstOrDefaultAsync(user => user.user_license_plate_number == yuntech_in_car_table.license_plate_number);
                // 車牌號碼相同，更新月租車位數量
                if (in_list_data != null)
                {
                    var monthly_rent_data_1 = await _context.yuntech_monthly_rent_number
                        .FirstOrDefaultAsync(rent => rent.category == "月租");
                    if (monthly_rent_data_1 != null)
                    {
                        int now_number;
                        Int32.TryParse(monthly_rent_data_1.number, out now_number);
                        now_number = now_number + 1;
                        monthly_rent_data_1.number = now_number.ToString();
                        // 保存更改
                        await _context.SaveChangesAsync();
                    }
                }
                else
                {
                    var monthly_rent_data_2 = await _context.yuntech_monthly_rent_number
                        .FirstOrDefaultAsync(rent => rent.category == "臨停");
                    if (monthly_rent_data_2 != null)
                    {
                        int now_number;
                        Int32.TryParse(monthly_rent_data_2.number, out now_number);
                        now_number = now_number + 1;
                        monthly_rent_data_2.number = now_number.ToString();
                        // 保存更改
                        await _context.SaveChangesAsync();
                    }
                }

            }
            catch (DbUpdateException)
            {
                if (Yuntech_in_car_tableExists(yuntech_in_car_table.in_time))
                {
                    return Conflict();
                }
                else
                {
                    throw;
                }
            }

            return CreatedAtAction("GetYuntech_in_car_table", new { id = yuntech_in_car_table.in_time }, yuntech_in_car_table);
        }
        #endregion


        #region 新增出去資料
        /// <summary>
        /// 新增出去雲科的車輛資料
        /// </summary>
        /// <param name="yuntech_in_car_table"></param>
        /// <returns></returns>
        // POST: api/Yuntech_in_car_table
        // To protect from overposting attacks, see https://go.microsoft.com/fwlink/?linkid=2123754
        [HttpPost("out_car")]
        public async Task<ActionResult<Yuntech_in_car_table>> PostYuntech_out_car_table(Yuntech_in_car_table yuntech_out_car_table)
        {
            if (_context.yuntech_in_car_table == null)
            {
                return Problem("Entity set 'SqlContext.yuntech_in_car_table'  is null.");
            }
            var license_plate_number = yuntech_out_car_table.license_plate_number;
            var out_car_img = yuntech_out_car_table.out_car_img;
            var out_location = yuntech_out_car_table.location;
            var in_car_data = await(from c in _context.yuntech_in_car_table
                                     where c.license_plate_number == license_plate_number
                                     where c.out_time ==null
                                     orderby c.in_time descending
                                     select c).FirstOrDefaultAsync();
            DateTime now_time = DateTime.Now;
            // 取得 30秒前的時間
            DateTime time_30s_ago = now_time.AddSeconds(-30);
            if (in_car_data != null && in_car_data.in_time<time_30s_ago)
            {

                // 处理找到记录的情况
                _context.yuntech_in_car_table.Remove(in_car_data);
                await _context.SaveChangesAsync();
                
                in_car_data.out_time = DateTime.Now;
                in_car_data.out_car_img = out_car_img;
                in_car_data.out_location = out_location;
                _context.yuntech_in_car_table.Add(in_car_data);
                await _context.SaveChangesAsync();

                // 車位數量+1
                var yuntech_parking = await _context.yuntech_parking.FirstOrDefaultAsync();

                if (yuntech_parking != null)
                {
                    string now_num_str = yuntech_parking.now_num;
                    int now_num_int;
                    Int32.TryParse(now_num_str, out now_num_int);
                    now_num_int = now_num_int + 1;
                    now_num_str = now_num_int.ToString();
                    yuntech_parking.now_num = now_num_str;
                    // 保存更改
                    await _context.SaveChangesAsync();
                }

                //判斷月租臨停車輛 -1
                // 與名單比對車牌號碼
                var in_list_data = await _context.yuntech_parking_user_list
                    .FirstOrDefaultAsync(user => user.user_license_plate_number == yuntech_out_car_table.license_plate_number);
                // 車牌號碼相同，更新月租車位數量
                if (in_list_data != null)
                {
                    var monthly_rent_data_1 = await _context.yuntech_monthly_rent_number
                        .FirstOrDefaultAsync(rent => rent.category == "月租");
                    if (monthly_rent_data_1 != null)
                    {
                        int now_number;
                        Int32.TryParse(monthly_rent_data_1.number, out now_number);
                        now_number = now_number - 1;
                        monthly_rent_data_1.number = now_number.ToString();
                        // 保存更改
                        await _context.SaveChangesAsync();
                    }
                }
                else
                {
                    var monthly_rent_data_2 = await _context.yuntech_monthly_rent_number
                        .FirstOrDefaultAsync(rent => rent.category == "臨停");
                    if (monthly_rent_data_2 != null)
                    {
                        int now_number;
                        Int32.TryParse(monthly_rent_data_2.number, out now_number);
                        now_number = now_number - 1;
                        monthly_rent_data_2.number = now_number.ToString();
                        // 保存更改
                        await _context.SaveChangesAsync();
                    }
                }
            }
            else
            {
                // 处理未找到记录的情况
                await _context.SaveChangesAsync();
            }




            return Ok();
        }
        #endregion

        #region City_parking 新增出去資料
        /// <summary>
        /// City_parking 新增出去資料
        /// </summary>
        /// <param name="yuntech_in_car_table"></param>
        /// <returns></returns>
        // POST: api/Yuntech_in_car_table
        // To protect from overposting attacks, see https://go.microsoft.com/fwlink/?linkid=2123754
        [HttpPost("city_parking_out_car_data")]
        public async Task<ActionResult<Yuntech_in_car_table>> PostYuntech_out_car_table_for_city_parking(Yuntech_in_car_table yuntech_out_car_table)
        {
            if (_context.yuntech_in_car_table == null)
            {
                return Problem("Entity set 'SqlContext.yuntech_in_car_table'  is null.");
            }
            var license_plate_number = yuntech_out_car_table.license_plate_number;
            var out_car_img = yuntech_out_car_table.out_car_img;
            var out_location = yuntech_out_car_table.location;
            var in_car_data = await (from c in _context.yuntech_in_car_table
                                     where c.license_plate_number == license_plate_number
                                     where c.out_time == null
                                     orderby c.in_time descending
                                     select c).FirstOrDefaultAsync();
            DateTime now_time = DateTime.Now;
            // 取得 30秒前的時間
            DateTime time_30s_ago = now_time.AddSeconds(-30);
            if (in_car_data != null && in_car_data.in_time < time_30s_ago)
            {
                // 处理找到记录的情况

                _context.yuntech_in_car_table.Remove(in_car_data);
                await _context.SaveChangesAsync();
                in_car_data.out_time = DateTime.Now;
                in_car_data.out_car_img = out_car_img;
                in_car_data.out_location = out_location;
                _context.yuntech_in_car_table.Add(in_car_data);
                await _context.SaveChangesAsync();

                // 車位數量+1
                var yuntech_parking = await _context.yuntech_parking.FirstOrDefaultAsync();

                if (yuntech_parking != null)
                {
                    string now_num_str = yuntech_parking.now_num;
                    int now_num_int;
                    Int32.TryParse(now_num_str, out now_num_int);
                    now_num_int = now_num_int + 1;
                    now_num_str = now_num_int.ToString();
                    yuntech_parking.now_num = now_num_str;
                    // 保存更改
                    await _context.SaveChangesAsync();
                }
                //判斷月租臨停車輛 -1
                // 與名單比對車牌號碼
                var in_list_data = await _context.yuntech_parking_user_list
                    .FirstOrDefaultAsync(user => user.user_license_plate_number == yuntech_out_car_table.license_plate_number);
                // 車牌號碼相同，更新月租車位數量
                if (in_list_data != null)
                {
                    var monthly_rent_data_1 = await _context.yuntech_monthly_rent_number
                        .FirstOrDefaultAsync(rent => rent.category == "月租");
                    if (monthly_rent_data_1 != null)
                    {
                        int now_number;
                        Int32.TryParse(monthly_rent_data_1.number, out now_number);
                        now_number = now_number - 1;
                        monthly_rent_data_1.number = now_number.ToString();
                        // 保存更改
                        await _context.SaveChangesAsync();
                    }
                }
                else
                {
                    var monthly_rent_data_2 = await _context.yuntech_monthly_rent_number
                        .FirstOrDefaultAsync(rent => rent.category == "臨停");
                    if (monthly_rent_data_2 != null)
                    {
                        int now_number;
                        Int32.TryParse(monthly_rent_data_2.number, out now_number);
                        now_number = now_number - 1;
                        monthly_rent_data_2.number = now_number.ToString();
                        // 保存更改
                        await _context.SaveChangesAsync();
                    }
                }
            }
            else
            {
                // 处理未找到记录的情况
                await _context.SaveChangesAsync();
            }




            return Ok();
        }
        #endregion


        #region 刪除資料
        /// <summary>
        /// 刪除指定資料
        /// </summary>
        /// <param name="license_plate_number">車牌號碼</param>
        /// <param name="time">資料新增時間</param>
        /// <returns></returns>
        [HttpDelete("license_plate_number-{license_plate_number}-time-{time}")]
        public async Task<IActionResult> Deletein_car_table(string license_plate_number, DateTime time)
        {
            var in_car_table = await (from c in _context.yuntech_in_car_table
                                             where c.license_plate_number == license_plate_number
                                             where c.in_time == time

                                             select c).FirstAsync();


            _context.yuntech_in_car_table.Remove(in_car_table);
            await _context.SaveChangesAsync();

            return NoContent();
        }
        #endregion

        #region 生成時間段Excel文件
        /// <summary>
        /// 生成包含時間範圍內車輛資料的 Excel 文件
        /// </summary>
        /// <param name="id">位置名稱</param>
        /// <param name="start_time">起始時間</param>
        /// <param name="end_time">結束時間</param>
        /// <returns></returns>
        [HttpGet("location_name_2_-{id}-start_time-{start_time}-end_time-{end_time}-export_excel")]
        public async Task<IActionResult> ExportYuntech_in_car_table_date_to_excel(string id, DateTime start_time, DateTime end_time)
        {
            var in_car_table = await (from c in _context.yuntech_in_car_table
                                      where c.location == id
                                      where c.in_time >= start_time
                                      where c.in_time <= end_time
                                      orderby c.in_time descending // 按進入時間降序排列
                                      select new Yuntech_in_car_table
                                      {
                                          license_plate_number = c.license_plate_number,
                                          in_time = c.in_time,
                                          out_time = c.out_time,
                                          location = c.location,
                                      }).ToListAsync();

            using (ExcelPackage package = new ExcelPackage())
            {
                ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("YuntechInCarTable");

                // 添加標題行
                worksheet.Cells["A1"].Value = "進入位置";
                worksheet.Cells["B1"].Value = "車牌號碼";
                worksheet.Cells["C1"].Value = "進入時間";
                worksheet.Cells["D1"].Value = "出去時間";

                // 設置列寬
                worksheet.Column(1).Width = 20;  
                worksheet.Column(2).Width = 15;  
                worksheet.Column(3).Width = 25;  
                worksheet.Column(4).Width = 25;  

                // 添加資料
                int row = 2;
                foreach (var item in in_car_table)
                {
                    worksheet.Cells["A" + row].Value = item.location;
                    worksheet.Cells["B" + row].Value = item.license_plate_number;
                    worksheet.Cells["C" + row].Value = item.in_time?.ToString("yyyy-MM-dd HH:mm:ss") ?? ""; 
                    worksheet.Cells["D" + row].Value = item.out_time?.ToString("yyyy-MM-dd HH:mm:ss") ?? ""; 


                    row++;
                }

                // 將ExcelPackage保存到內存流中
                MemoryStream stream = new MemoryStream(package.GetAsByteArray());

                // 設置HttpResponseMessage的內容
                Response.Headers.Add("Content-Disposition", $"attachment; filename={id}_car_table_{start_time:yyyyMMddHHmmss}_{end_time:yyyyMMddHHmmss}.xlsx");
                return File(stream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            }
        }
        #endregion


        #region 自動生成時間段Excel文件
        /// <summary>
        /// 自動生成包含時間範圍內車輛資料的 Excel 文件
        /// </summary>
        /// <param name="id">位置名稱</param>
        /// <param name="start_time">起始時間</param>
        /// <param name="end_time">結束時間</param>
        /// <returns></returns>

        [HttpGet("auto_export_excel")]
        public async Task<IActionResult> ExportYuntech_in_car_table_date_to_excel_auto()
        {
            var start_time = DateTime.Now.AddHours(-24);//將資料調整成24小時內的車輛紀錄
            var end_time = DateTime.Now;
            var in_car_table = await (from c in _context.yuntech_in_car_table
                                      where c.in_time >= start_time
                                      where c.in_time <= end_time
                                      orderby c.in_time descending // 按進入時間降序排列
                                      select new Yuntech_in_car_table
                                      {
                                          license_plate_number = c.license_plate_number,
                                          in_time = c.in_time,
                                          out_time = c.out_time,
                                          location = c.location,
                                      }).ToListAsync();

            // 創建ExcelPackage對象
            using (ExcelPackage package = new ExcelPackage())
            {
                ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("YuntechInCarTable");

                // 添加標題行
                worksheet.Cells["A1"].Value = "進入位置";
                worksheet.Cells["B1"].Value = "車牌號碼";
                worksheet.Cells["C1"].Value = "進入時間";
                worksheet.Cells["D1"].Value = "出去時間";
                // 設置列寬
                worksheet.Column(1).Width = 20;
                worksheet.Column(2).Width = 15;
                worksheet.Column(3).Width = 25;
                worksheet.Column(4).Width = 25;
                // 添加資料
                int row = 2;
                foreach (var item in in_car_table)
                {
                    worksheet.Cells["A" + row].Value = item.location;
                    worksheet.Cells["B" + row].Value = item.license_plate_number;
                    worksheet.Cells["C" + row].Value = item.in_time?.ToString("yyyy-MM-dd HH:mm:ss") ?? "";
                    worksheet.Cells["D" + row].Value = item.out_time?.ToString("yyyy-MM-dd HH:mm:ss") ?? "";
                    row++;
                }

                // 將ExcelPackage保存到內存流中
                MemoryStream stream = new MemoryStream(package.GetAsByteArray());

                // 設置HttpResponseMessage的內容
                Response.Headers.Add("Content-Disposition", "attachment; filename=test.xlsx");
                return File(stream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            }
        }
        #endregion


        #region 生成全部進入車輛EXCEL
        /// <summary>
        /// 生成全部進入車輛EXCEL
        /// </summary>
        /// <returns></returns>
        [HttpGet("export-all_excel")]
        public async Task<IActionResult> GenerateExcel()
        {
            var yuntech_in_car_table = await (from c in _context.yuntech_in_car_table
                                             orderby c.in_time descending, c.out_time descending
                                              select new Yuntech_in_car_table
                                             {
                                                 license_plate_number = c.license_plate_number,
                                                 in_time = c.in_time,
                                                 out_time = c.out_time,
                                                 location = c.location,

                                             }).ToListAsync();

            // 創建ExcelPackage對象
            using (ExcelPackage package = new ExcelPackage())
            {
                ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("YuntechInCarTable");

                // 添加標題行
                worksheet.Cells["A1"].Value = "進入位置";
                worksheet.Cells["B1"].Value = "車牌號碼";
                worksheet.Cells["C1"].Value = "進入時間";
                worksheet.Cells["D1"].Value = "出去時間";
                // 設置列寬
                worksheet.Column(1).Width = 20;
                worksheet.Column(2).Width = 15;
                worksheet.Column(3).Width = 25;
                worksheet.Column(4).Width = 25;

                // 添加資料
                int row = 2;
                foreach (var item in yuntech_in_car_table)
                {
                    worksheet.Cells["A" + row].Value = item.location;
                    worksheet.Cells["B" + row].Value = item.license_plate_number;
                    worksheet.Cells["C" + row].Value = item.in_time?.ToString("yyyy-MM-dd HH:mm:ss") ?? "";
                    worksheet.Cells["D" + row].Value = item.out_time?.ToString("yyyy-MM-dd HH:mm:ss") ?? "";
                    row++;
                }

                // 將ExcelPackage保存到內存流中
                MemoryStream stream = new MemoryStream(package.GetAsByteArray());

                // 設置HttpResponseMessage的內容
                Response.Headers.Add("Content-Disposition", "attachment; filename=test.xlsx");
                return File(stream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            }
        }
        #endregion

        private bool Yuntech_in_car_tableExists(DateTime? id)
        {
            return (_context.yuntech_in_car_table?.Any(e => e.in_time == id)).GetValueOrDefault();
        }
    }
}