using BBPA.KitChen.GroupMeal.Application.Service.OneCard.ReportStatistics.Reservation.Dto;
using BPA.Kitchen.Core.Models.SYS;
using BPA.KitChen.GroupMeal.Application.BaseDto;
using BPA.KitChen.GroupMeal.Application.Service.OneCard.ReportStatistics.Reservation.Dto;
using BPA.KitChen.GroupMeal.Core.Common;
using BPA.KitChen.GroupMeal.Core.Common.Const;
using BPA.KitChen.GroupMeal.Core.Entity;
using BPA.KitChen.GroupMeal.SqlSugar;
using Furion;
using Furion.DependencyInjection;
using Furion.DynamicApiController;
using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Hosting;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Newtonsoft.Json;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace BPA.KitChen.GroupMeal.Application.Service.OneCard.ReportStatistics.Reservation
{

    [ApiDescriptionSettings("一卡通", Tag = "报表统计")]
    public class Reservation:SupperRepository, IDynamicApiController, IReservation, ITransient
    {
        private readonly SqlSugarScope db;
        IWebHostEnvironment _hostingEnvironment;

        private readonly IHttpContextAccessor _httpContextAccessor;

        public Reservation(IWebHostEnvironment hostingEnvironment, IHttpContextAccessor httpContextAccessor)
        {
            db = SqlSugarDb.Db;
            _hostingEnvironment= hostingEnvironment;
            _httpContextAccessor = httpContextAccessor;
        }

        /// <summary>
        /// 获取预定商品统计
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost("/api/Reservation/GetReservationGoods")]
        public PageUtil GetReservationGoods(ReservationQueryInput dto)
        {

            List<IConditionalModel> conModels = new List<IConditionalModel>();
            string groupId = App.User.FindFirst(ClaimConst.GroupId)?.Value;

            //#region 条件查询
            //if (!string.IsNullOrWhiteSpace(dto.OrgName))
            //{
            //    conModels.Add(new ConditionalModel() { FieldName = "a.OrgName", ConditionalType = ConditionalType.Like, FieldValue = dto.OrgName });
            //}
            //if (!string.IsNullOrWhiteSpace(dto.MealType))
            //{
            //    conModels.Add(new ConditionalModel() { FieldName = "a.MealType", ConditionalType = ConditionalType.Equal, FieldValue = dto.MealType });
            //}
            //#endregion

            int total = new RefAsync<int>();

            var res = db.Queryable<BPA_ReservationOrder>()
                //.Where((a) => a.IsDeleted == 0 )
                //.Where((a) => a.ReservationTime >= dto.beginTime && a.ReservationTime <= dto.endTime)
                //.Where(conModels)
                //.OrderBy(a => new { MealType = SqlFunc.Desc(a.MealType), a.CreateAt })
                //.GroupBy((a) =>a.MealType)
                .Select((a) => new
                {
                   // Qty = SqlFunc.AggregateSum(a.Qty),
                    Id = SqlFunc.AggregateMax(a.GoodsId),
                    //MealType = a.MealType,
                    Name = a.GoodsId,
                    CreateAt = a.ReservationTime.ToString(),
                }).ToPageList(dto.Current, dto.PageSize, ref total);


            //var res = db.Queryable<BPA_ReservationOrder, BPA_GoodsInfo>((a, b) => new JoinQueryInfos(
            //        JoinType.Left, a.GoodsId == b.Id
            //    ))
            //    .Where((a, b) => a.IsDeleted == 0 && b.IsDeleted == 0)
            //    .Where((a, b) => a.ReservationTime >= dto.beginTime && a.ReservationTime <= dto.endTime)
            //    .Where(conModels)
            //    .OrderBy(a => new { MealType = SqlFunc.Desc(a.MealType), a.CreateAt })
            //    .GroupBy((a, b) => new { b.Id, b.Goods_Name, a.MealType })
            //    .Select((a, b) => new
            //    {
            //        Id = b.Id,
            //        MealType = a.MealType,
            //        Name = b.Goods_Name,
            //        Qty = SqlFunc.AggregateSum(a.Qty),
            //        CreateAt = a.ReservationTime.ToString(),
            //    }).ToPageList(dto.Current, dto.PageSize, ref total);


            PageUtil util = new PageUtil()
            {
                Total = total,
                Data = res
            };
            return util;

        }

        /// <summary>
        /// 预定商品信息
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost("/api/Reservation/GetReservationGoodsExcel")]
        public ActionResult GetReservationGoodsExcel(ReservationQueryInput dto)
        {
            List<IConditionalModel> conModels = new List<IConditionalModel>();
            string groupId = App.User.FindFirst(ClaimConst.GroupId)?.Value;

            #region 条件查询
            if (!string.IsNullOrWhiteSpace(dto.OrgName))
            {
                conModels.Add(new ConditionalModel() { FieldName = "a.OrgName", ConditionalType = ConditionalType.Like, FieldValue = dto.OrgName });
            }
            if (!string.IsNullOrWhiteSpace(dto.MealType))
            {
                conModels.Add(new ConditionalModel() { FieldName = "a.MealType", ConditionalType = ConditionalType.Equal, FieldValue = dto.MealType });
            }
            #endregion

            int total = new RefAsync<int>();


            var res = db.Queryable<BPA_ReservationOrder>()
                .Where((a) => a.IsDeleted == 0)
               .Where((a) => a.ReservationTime >= dto.beginTime && a.ReservationTime <= dto.endTime)
               .Where(conModels)
                .OrderBy(a => a.CreateAt, OrderByType.Desc)
                .GroupBy((a) => new { a.GoodsId, Goods_Name = a.GoodsId, a.MealType })
                    .Select((a) => new
                    {
                        Id = a.GoodsId,
                        MealType = a.MealType,
                        Name = a.GoodsId,
                        Qty = SqlFunc.AggregateSum(a.Qty),
                        CreateAt = a.CreateAt.ToString(),
                    }).ToList();
            string fielName = "预定商品信息";
            var rootPath = _hostingEnvironment.ContentRootPath + "orderExcels\\";
            if (System.IO.Directory.Exists(rootPath) == false)
                System.IO.Directory.CreateDirectory(rootPath);

            var newFile = rootPath + "\\" + fielName + ".xlsx";
            if (System.IO.File.Exists(newFile))
            {
                System.IO.File.Delete(newFile);
            }
            using (var fs = new FileStream(newFile, FileMode.Create, FileAccess.Write))
            {

                IWorkbook workbook = new XSSFWorkbook();
                var sheet = workbook.CreateSheet("goods");
                var header = sheet.CreateRow(0);
                header.CreateCell(0).SetCellValue("商品");
                header.CreateCell(1).SetCellValue("用餐时段");
                header.CreateCell(2).SetCellValue("预定数量");
                header.CreateCell(3).SetCellValue("预定时间");
                var rowIndex = 1;
                foreach (var item in res)
                {
                    string MealType = "";
                    switch (item.MealType)
                    {
                        case "1":
                            MealType = "早餐";
                            break;
                        case "2":
                            MealType = "午餐";
                            break;
                        case "3":
                            MealType = "晚餐";
                            break;
                        default:
                            break;
                    }
                    var datarow = sheet.CreateRow(rowIndex);
                    datarow.CreateCell(0).SetCellValue(item.Name);
                    datarow.CreateCell(1).SetCellValue(MealType);
                    datarow.CreateCell(2).SetCellValue(item.Qty);
                    datarow.CreateCell(3).SetCellValue(item.CreateAt);
                    rowIndex++;
                }
                workbook.Write(fs);
            }
            var memory = new MemoryStream();
            using (var stream = new FileStream(newFile, FileMode.Open))
            {
                stream.CopyTo(memory);
            }
            memory.Position = 0;
            var result = new FileStreamResult(memory, "application/octet-stream")
            {
                FileDownloadName = fielName + ".xlsx"
            };
            return result;

        }


        /// <summary>
        /// 预定部门汇总
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost("/api/Reservation/GetReservationOrg")]
        public PageUtil GetReservationOrg(ReservationQueryInput dto)
        {
            List<IConditionalModel> conModels = new List<IConditionalModel>();
            string groupId = App.User.FindFirst(ClaimConst.GroupId)?.Value;

            #region 条件查询
            if (!string.IsNullOrWhiteSpace(dto.OrgName))
            {
                conModels.Add(new ConditionalModel() { FieldName = "a.OrgName", ConditionalType = ConditionalType.Like, FieldValue = dto.OrgName });
            }
            if (!string.IsNullOrWhiteSpace(dto.MealType))
            {
                conModels.Add(new ConditionalModel() { FieldName = "a.MealType", ConditionalType = ConditionalType.Equal, FieldValue = dto.MealType });
            }
            #endregion

            int total = new RefAsync<int>();


            var res = db.Queryable<BPA_ReservationOrder>()
                .Where(a => a.IsDeleted == 0)
                .Where(a => a.ReservationTime >= dto.beginTime && a.ReservationTime <= dto.endTime)
                .Where(conModels)
                .OrderBy(a => new { MealType = SqlFunc.Desc(a.MealType), a.CreateAt })
                .GroupBy(a => new { a.OrgName, a.MealType })
                .Select(a => new
                {
                    MealType = a.MealType,
                    OrgName = a.OrgName,
                    Qty = SqlFunc.AggregateSum(a.Qty),
                }).ToPageList(dto.Current, dto.PageSize, ref total);
            PageUtil util = new PageUtil()
            {
                Total = total,
                Data = res
            };
            return util;

        }

        /// <summary>
        /// 获取预定人员信息
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost("/api/Reservation/GetReservationUser")]
        public PageUtil GetReservationUser(ReservationQueryInput dto)
        {

            List<IConditionalModel> conModels = new List<IConditionalModel>();
            string groupId = App.User.FindFirst(ClaimConst.GroupId)?.Value;
            #region 条件查询
            if (!string.IsNullOrWhiteSpace(dto.OrgName))
            {
                conModels.Add(new ConditionalModel() { FieldName = "a.OrgName", ConditionalType = ConditionalType.Like, FieldValue = dto.OrgName });
            }
            if (!string.IsNullOrWhiteSpace(dto.MealType))
            {
                conModels.Add(new ConditionalModel() { FieldName = "a.MealType", ConditionalType = ConditionalType.Equal, FieldValue = dto.MealType });
            }
            #endregion

            int total = new RefAsync<int>();
            var res = db.Queryable<BPA_ReservationOrder, BPA_MemberInfo>((a, b) => new JoinQueryInfos(
                    JoinType.Left, a.UserId == b.Id
                ))
                .Where((a, b) => a.IsDeleted == 0 && b.IsDeleted == 0)
                .Where(conModels)
                .Where((a, b) => a.ReservationTime >= dto.beginTime && a.ReservationTime <= dto.endTime)
                .OrderBy(a => new { MealType = SqlFunc.Desc(a.MealType), a.CreateAt })
                .GroupBy((a, b) => new { b.Id, b.RealName, a.OrgName })
                .Select((a, b) => new
                {
                    Id = b.Id,
                    Name = b.RealName,
                    Qty = SqlFunc.AggregateSum(a.Qty),
                    OrgName = a.OrgName
                }).ToPageList(dto.Current, dto.PageSize, ref total);
            PageUtil util = new PageUtil()
            {
                Total = total,
                Data = res
            };
            return util;
        }

        /// <summary>
        /// 获取预定商品明细
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost("/api/Reservation/GetReservationGoodsDetailed")]
        public PageUtil GetReservationGoodsDetailed(ReservationQueryInput dto)
        {

            List<IConditionalModel> conModels = new List<IConditionalModel>();
            string groupId = App.User.FindFirst(ClaimConst.GroupId)?.Value;

            #region 条件查询
            if (!string.IsNullOrWhiteSpace(dto.OrgName))
            {
                conModels.Add(new ConditionalModel() { FieldName = "a.OrgName", ConditionalType = ConditionalType.Like, FieldValue = dto.OrgName });
            }
            if (!string.IsNullOrWhiteSpace(dto.MealType))
            {
                conModels.Add(new ConditionalModel() { FieldName = "a.MealType", ConditionalType = ConditionalType.Equal, FieldValue = dto.MealType });
            }
            #endregion

            int total = new RefAsync<int>();


            var res = db.Queryable<BPA_ReservationOrder, BPA_MemberInfo>((a, c) => new JoinQueryInfos(
                    JoinType.Left, a.UserId == c.Id
                ))
                .Where((a, c) => a.IsDeleted == 0)
                .Where((a, c) => a.ReservationTime >= dto.beginTime && a.ReservationTime <= dto.endTime)
                .Where(conModels)
                .OrderBy(a => new { MealType = SqlFunc.Desc(a.MealType), a.CreateAt })
                .GroupBy((a,  c) => new { a.GoodsId, Goods_Name=a.GoodsId, a.MealType, a.OrgName, a.Qty, c.RealName })
                .Select((a,  c) => new
                {
                    Id = a.GoodsId,
                    MealType = a.MealType,
                    Name =a.GoodsId,
                    Qty = SqlFunc.AggregateSum(a.Qty),
                    OrgName = a.OrgName,
                    CreateAt = a.ReservationTime.ToString(),
                    RealName = c.RealName,
                }).ToPageList(dto.Current, dto.PageSize, ref total);
            PageUtil util = new PageUtil()
            {
                Total = total,
                Data = res
            };
            return util;

        }



        /// <summary>
        /// 预定部门汇总
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost("/api/Reservation/GetReservationOrgExcel")]
        public ActionResult GetReservationOrgExcel(ReservationQueryInput dto)
        {
            List<IConditionalModel> conModels = new List<IConditionalModel>();
            string groupId = App.User.FindFirst(ClaimConst.GroupId)?.Value;

            #region 条件查询
            if (!string.IsNullOrWhiteSpace(dto.OrgName))
            {
                conModels.Add(new ConditionalModel() { FieldName = "a.OrgName", ConditionalType = ConditionalType.Like, FieldValue = dto.OrgName });
            }
            if (!string.IsNullOrWhiteSpace(dto.MealType))
            {
                conModels.Add(new ConditionalModel() { FieldName = "a.MealType", ConditionalType = ConditionalType.Equal, FieldValue = dto.MealType });
            }
            #endregion

            int total = new RefAsync<int>();


            var res = db.Queryable<BPA_ReservationOrder>()
                .Where(a => a.IsDeleted == 0)
               .Where(a => a.ReservationTime >= dto.beginTime && a.ReservationTime <= dto.endTime)
               .Where(conModels)
                .OrderBy(a => a.CreateAt, OrderByType.Desc)
                .GroupBy(a => new { a.OrgName, a.MealType })
                    .Select(a => new
                    {
                        MealType = a.MealType,
                        OrgName = a.OrgName,
                        Qty = SqlFunc.AggregateSum(a.Qty),
                    }).ToList();
            string fielName = "预定部门汇总";
            var rootPath = _hostingEnvironment.ContentRootPath + "orderExcels\\";
            if (System.IO.Directory.Exists(rootPath) == false)
                System.IO.Directory.CreateDirectory(rootPath);

            var newFile = rootPath + "\\" + fielName + ".xlsx";
            if (System.IO.File.Exists(newFile))
            {
                System.IO.File.Delete(newFile);
            }
            using (var fs = new FileStream(newFile, FileMode.Create, FileAccess.Write))
            {

                IWorkbook workbook = new XSSFWorkbook();
                var sheet = workbook.CreateSheet("org");
                var header = sheet.CreateRow(0);
                header.CreateCell(0).SetCellValue("部门名称");
                header.CreateCell(1).SetCellValue("用餐时段");
                header.CreateCell(2).SetCellValue("预定数量");
                var rowIndex = 1;
                foreach (var item in res)
                {
                    string MealType = "";
                    switch (item.MealType)
                    {
                        case "1":
                            MealType = "早餐";
                            break;
                        case "2":
                            MealType = "午餐";
                            break;
                        case "3":
                            MealType = "晚餐";
                            break;
                        default:
                            break;
                    }
                    var datarow = sheet.CreateRow(rowIndex);
                    datarow.CreateCell(0).SetCellValue(item.OrgName);
                    datarow.CreateCell(1).SetCellValue(MealType);
                    datarow.CreateCell(2).SetCellValue(item.Qty);
                    rowIndex++;
                }
                workbook.Write(fs);
            }
            var memory = new MemoryStream();
            using (var stream = new FileStream(newFile, FileMode.Open))
            {
                stream.CopyTo(memory);
            }
            memory.Position = 0;
            var result = new FileStreamResult(memory, "application/octet-stream")
            {
                FileDownloadName = fielName + ".xlsx"
            };
            return result;

        }

        /// <summary>
        /// 获取预定人员信息
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost("/api/Reservation/GetReservationUserExcel")]
        public ActionResult GetReservationUserExcel(ReservationQueryInput dto)
        {

            List<IConditionalModel> conModels = new List<IConditionalModel>();
            string groupId = App.User.FindFirst(ClaimConst.GroupId)?.Value;
            #region 条件查询
            if (!string.IsNullOrWhiteSpace(dto.OrgName))
            {
                conModels.Add(new ConditionalModel() { FieldName = "a.OrgName", ConditionalType = ConditionalType.Like, FieldValue = dto.OrgName });
            }
            if (!string.IsNullOrWhiteSpace(dto.MealType))
            {
                conModels.Add(new ConditionalModel() { FieldName = "a.MealType", ConditionalType = ConditionalType.Equal, FieldValue = dto.MealType });
            }
            #endregion

            int total = new RefAsync<int>();
            var res = db.Queryable<BPA_ReservationOrder, BPA_MemberInfo>((a, b) => new JoinQueryInfos(
                     JoinType.Left, a.UserId == b.Id
                 ))
                .Where((a, b) => a.IsDeleted == 0 && b.IsDeleted == 0)
                .Where(conModels)
                 .Where((a, b) => a.ReservationTime >= dto.beginTime && a.ReservationTime <= dto.endTime)
                .OrderBy(a => a.CreateAt, OrderByType.Desc)
                 .GroupBy((a, b) => new { b.Id, b.RealName, a.OrgName })
                    .Select((a, b) => new
                    {
                        Id = b.Id,
                        Name = b.RealName,
                        Qty = SqlFunc.AggregateSum(a.Qty),
                        OrgName = a.OrgName
                    }).ToList();
            string fielName = "获取预定人员信息";
            var rootPath = _hostingEnvironment.ContentRootPath + "orderExcels\\";
            if (System.IO.Directory.Exists(rootPath) == false)
                System.IO.Directory.CreateDirectory(rootPath);

            var newFile = rootPath + "\\" + fielName + ".xlsx";
            if (System.IO.File.Exists(newFile))
            {
                System.IO.File.Delete(newFile);
            }
            using (var fs = new FileStream(newFile, FileMode.Create, FileAccess.Write))
            {

                IWorkbook workbook = new XSSFWorkbook();
                var sheet = workbook.CreateSheet("user");
                var header = sheet.CreateRow(0);
                header.CreateCell(0).SetCellValue("姓名");
                header.CreateCell(1).SetCellValue("部门");
                header.CreateCell(2).SetCellValue("预定数量");
                var rowIndex = 1;
                foreach (var item in res)
                {
                    var datarow = sheet.CreateRow(rowIndex);
                    datarow.CreateCell(0).SetCellValue(item.Name);
                    datarow.CreateCell(1).SetCellValue(item.OrgName);
                    datarow.CreateCell(2).SetCellValue(item.Qty);
                    rowIndex++;
                }
                workbook.Write(fs);
            }
            var memory = new MemoryStream();
            using (var stream = new FileStream(newFile, FileMode.Open))
            {
                stream.CopyTo(memory);
            }
            memory.Position = 0;
            var result = new FileStreamResult(memory, "application/octet-stream")
            {
                FileDownloadName = fielName + ".xlsx"
            };
            return result;
        }

        /// <summary>
        /// 获取预定商品明细
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost("/api/Reservation/GetReservationGoodsDetailedExcel")]
        public ActionResult GetReservationGoodsDetailedExcel(ReservationQueryInput dto)
        {

            List<IConditionalModel> conModels = new List<IConditionalModel>();
            string groupId = App.User.FindFirst(ClaimConst.GroupId)?.Value;

            #region 条件查询
            if (!string.IsNullOrWhiteSpace(dto.OrgName))
            {
                conModels.Add(new ConditionalModel() { FieldName = "a.OrgName", ConditionalType = ConditionalType.Like, FieldValue = dto.OrgName });
            }
            if (!string.IsNullOrWhiteSpace(dto.MealType))
            {
                conModels.Add(new ConditionalModel() { FieldName = "a.MealType", ConditionalType = ConditionalType.Equal, FieldValue = dto.MealType });
            }
            #endregion

            int total = new RefAsync<int>();


            var res = db.Queryable<BPA_ReservationOrder, BPA_MemberInfo>((a,  c) => new JoinQueryInfos(
                      JoinType.Left, a.UserId == c.Id
                 ))
                .Where((a, c) => a.IsDeleted == 0 )
               .Where((a, c) => a.ReservationTime >= dto.beginTime && a.ReservationTime <= dto.endTime)
               .Where(conModels)
                .OrderBy(a => a.MealType, OrderByType.Desc)
                .GroupBy((a, c) => new { a.GoodsId, Goods_Name=a.GoodsId, a.MealType, a.OrgName, a.Qty, c.RealName })
                    .Select((a, c) => new
                    {
                        Id = a.GoodsId,
                        MealType = a.MealType,
                        Name = a.GoodsId,
                        Qty = SqlFunc.AggregateSum(a.Qty),
                        OrgName = a.OrgName,
                        CreateAt = a.ReservationTime.ToString(),
                        RealName = c.RealName,
                    }).ToList();
            string fielName = "预定商品明细";
            var rootPath = _hostingEnvironment.ContentRootPath + "orderExcels\\";
            if (System.IO.Directory.Exists(rootPath) == false)
                System.IO.Directory.CreateDirectory(rootPath);

            var newFile = rootPath + "\\" + fielName + ".xlsx";
            if (System.IO.File.Exists(newFile))
            {
                System.IO.File.Delete(newFile);
            }
            using (var fs = new FileStream(newFile, FileMode.Create, FileAccess.Write))
            {

                IWorkbook workbook = new XSSFWorkbook();
                var sheet = workbook.CreateSheet("Detailed");
                var header = sheet.CreateRow(0);
                header.CreateCell(0).SetCellValue("商品");
                header.CreateCell(1).SetCellValue("预定部门");
                header.CreateCell(2).SetCellValue("预定人姓名");
                header.CreateCell(3).SetCellValue("用餐时段");
                header.CreateCell(4).SetCellValue("预定数量");
                header.CreateCell(5).SetCellValue("是否统一预定");
                header.CreateCell(6).SetCellValue("预定时间");
                var rowIndex = 1;
                foreach (var item in res)
                {
                    string MealType = "";
                    switch (item.MealType)
                    {
                        case "1":
                            MealType = "早餐";
                            break;
                        case "2":
                            MealType = "午餐";
                            break;
                        case "3":
                            MealType = "晚餐";
                            break;
                        default:
                            break;
                    }
                    string IsAll = "否";
                    if (item.Qty > 2)
                    {
                        IsAll = "是";
                    }
                    var datarow = sheet.CreateRow(rowIndex);
                    datarow.CreateCell(0).SetCellValue(item.Name);
                    datarow.CreateCell(1).SetCellValue(item.OrgName);
                    datarow.CreateCell(2).SetCellValue(item.RealName);
                    datarow.CreateCell(3).SetCellValue(MealType);
                    datarow.CreateCell(4).SetCellValue(item.Qty);
                    datarow.CreateCell(5).SetCellValue(IsAll);
                    datarow.CreateCell(6).SetCellValue(item.CreateAt.ToString());
                    rowIndex++;
                }
                workbook.Write(fs);
            }
            var memory = new MemoryStream();
            using (var stream = new FileStream(newFile, FileMode.Open))
            {
                stream.CopyTo(memory);
            }
            memory.Position = 0;
            var result = new FileStreamResult(memory, "application/octet-stream")
            {
                FileDownloadName = fielName + ".xlsx"
            };
            return result;

        }




        /// <summary>
        /// 获取日消费信息（每人每天的的消费情况）
        /// </summary>
        /// <param name="inputDto"></param>
        [HttpPost("/api/Reservation/GetDayConsumeInfo")]
        public async Task<ResultEntity> GetDayConsumeInfo(ReportFormInputDto inputDto)
        {
            var result = new ResultEntity() { IsSuccess = true };

            var eatTimeInterval = await EatTimeInterval();

            var sql = @$"SELECT MemberInfoId,RealName,Phone,CreateAt,TimeInterval,count(TimeInterval) as TimeIntervalCount  from (
		                    SELECT 
			                    date(a.CreateAt) as CreateAt,
                            a.MemberInfoId,
		                    b.RealName,
		                    b.Phone,
			                    (case when  DATE_FORMAT(a.CreateAt,'%T')> DATE_FORMAT('0001-01-01 {eatTimeInterval.AMStartTime}','%T') and DATE_FORMAT(a.CreateAt,'%T')<DATE_FORMAT('0001-01-01 {eatTimeInterval.AMEndTime}','%T')	then 'AM'
						                    when  DATE_FORMAT(a.CreateAt,'%T')>DATE_FORMAT('0001-01-01 {eatTimeInterval.PMStartTime}','%T') and DATE_FORMAT(a.CreateAt,'%T')<DATE_FORMAT('0001-01-01 {eatTimeInterval.PMEndTime}','%T')	then 'PM'
						                    else 'AT' end ) as  TimeInterval
		                    from  bpa_memberaccountrecord as a
		                    LEFT JOIN bpa_memberinfo as b  on a.MemberInfoId=b.Id
                            WHERE a.CreateAt>='{inputDto.StartTime.ToString("yyyy-MM-dd HH:mm:ss")}' 
                                  and a.CreateAt<='{inputDto.EndTime.Date.AddHours(23).AddMinutes(59).AddSeconds(59).ToString("yyyy-MM-dd HH:mm:ss")}'
                                and a.GroupId='{CurrentUser.TenantId}' 
                    )  as a
                    GROUP BY MemberInfoId,RealName,Phone,CreateAt,TimeInterval";

            var table = db.SqlQueryable<ReportFormInfo>(sql).ToList();

            var res = table.GroupBy(x => new
            {
                x.MemberInfoId,
                x.CreateAt,
                x.Phone,
                x.RealName
            }).Select(x => new ReportFormOutDto()
            {
                MemberInfoId = x.Key.MemberInfoId,
                Name = x.Key.RealName,
                Date = x.Key.CreateAt.ToString("yyyy-MM-dd"),
                Phone = x.Key.Phone,
                AMCount = (int)x.Where(a => a.TimeInterval == "AM" && a.Phone == x.Key.Phone && a.RealName == x.Key.RealName && a.CreateAt == x.Key.CreateAt).Sum(a => a.TimeIntervalCount),
                PMCount = (int)x.Where(a => a.TimeInterval == "PM" && a.Phone == x.Key.Phone && a.RealName == x.Key.RealName && a.CreateAt == x.Key.CreateAt).Sum(a => a.TimeIntervalCount),
                ATCount = (int)x.Where(a => a.TimeInterval == "AT" && a.Phone == x.Key.Phone && a.RealName == x.Key.RealName && a.CreateAt == x.Key.CreateAt).Sum(a => a.TimeIntervalCount),

            }).ToList();

            //查询出所有会员的会员标签
            var memtag = await db.Queryable<BPA_MemberTagInfo, BPA_MemberTag>((a, b) =>
                    new JoinQueryInfos(
                        JoinType.Left, a.MemberTag_Id == b.Id))
                .Where(a => a.IsDeleted == 0)
                .Select((a, b) => new
                {
                    MemberInfoId = a.MemberInfo_Id,
                    TagId = b.Id,
                    TagName = b.Name

                }).ToListAsync();

            foreach (var item in res)
            {
                var info = memtag.Where(x => x.MemberInfoId == item.MemberInfoId)
                    .FirstOrDefault();
                item.Tag = info == null ? "" : info.TagName;
            }

            result.Data = res;
            return result;

        }

        /// <summary>
        /// 获取时间段消费信息（每天的消费情况）
        /// </summary>
        /// <param name="inputDto"></param>
        /// <returns></returns>
        [HttpPost("/api/Reservation/GetConsumeInfo")]
        public async Task<ResultEntity> GetConsumeInfo(ReportFormInputDto inputDto)
        {
            var result = new ResultEntity() { IsSuccess = true };
            var eatTimeInterval = await EatTimeInterval();

            var sql = @$"SELECT CreateAt,TimeInterval,count(TimeInterval) as TimeIntervalCount from (
		                    SELECT 
			                    date(a.CreateAt) as CreateAt,
		                    b.RealName,
		                    b.Phone,
			                      (case when  DATE_FORMAT(a.CreateAt,'%T')> DATE_FORMAT('0001-01-01 {eatTimeInterval.AMStartTime}','%T') and DATE_FORMAT(a.CreateAt,'%T')<DATE_FORMAT('0001-01-01 {eatTimeInterval.AMEndTime}','%T')	then 'AM'
						                    when  DATE_FORMAT(a.CreateAt,'%T')>DATE_FORMAT('0001-01-01 {eatTimeInterval.PMStartTime}','%T') and DATE_FORMAT(a.CreateAt,'%T')<DATE_FORMAT('0001-01-01 {eatTimeInterval.PMEndTime}','%T')	then 'PM'
						                    else 'AT' end ) as  TimeInterval
		                    from  bpa_memberaccountrecord as a
		                    LEFT JOIN bpa_memberinfo as b  on a.MemberInfoId=b.Id
                            WHERE a.CreateAt>='{inputDto.StartTime.ToString("yyyy-MM-dd HH:mm:ss")}' 
                                  and a.CreateAt<='{inputDto.EndTime.Date.AddHours(23).AddMinutes(59).AddSeconds(59).ToString("yyyy-MM-dd HH:mm:ss")}'
                                   and a.GroupId='{CurrentUser.TenantId}'
                    )  as a
                    GROUP BY CreateAt,TimeInterval";

            var table = db.SqlQueryable<ReportFormInfo>(sql).ToList();

            result.Data = table.GroupBy(x => x.CreateAt)
                .Select(x => new ReportFormOutDto()
                {
                    Date = x.Key.ToString("yyyy-MM-dd"),
                    AMCount = (int)x.Where(a => a.TimeInterval == "AM" && a.CreateAt == x.Key).Sum(a => a.TimeIntervalCount),
                    PMCount = (int)x.Where(a => a.TimeInterval == "PM" && a.CreateAt == x.Key).Sum(a => a.TimeIntervalCount),
                    ATCount = (int)x.Where(a => a.TimeInterval == "AT" && a.CreateAt == x.Key).Sum(a => a.TimeIntervalCount),

                }).ToList();


            return result;

        }

        /// <summary>
        /// 获取时间段消费信息汇总（时间段内每个人的消费情况）
        /// </summary>
        /// <param name="inputDto"></param>
        /// <returns></returns>
        [HttpPost("/api/Reservation/GetTimeIntervalConsumeInfo")]
        public async Task<ResultEntity> GetTimeIntervalConsumeInfo(ReportFormInputDto inputDto)
        {

            var result = new ResultEntity() { IsSuccess = true };
            var eatTimeInterval = await EatTimeInterval();

            var sql = @$"SELECT MemberInfoId,RealName,Phone,TimeInterval,count(TimeInterval) as TimeIntervalCount from (
		                    SELECT 
			                    date(a.CreateAt) as CreateAt,
		                    b.RealName,
                            a.MemberInfoId,
		                    b.Phone,
			                     (case when  DATE_FORMAT(a.CreateAt,'%T')> DATE_FORMAT('0001-01-01 {eatTimeInterval.AMStartTime}','%T') and DATE_FORMAT(a.CreateAt,'%T')<DATE_FORMAT('0001-01-01 {eatTimeInterval.AMEndTime}','%T')	then 'AM'
						                    when  DATE_FORMAT(a.CreateAt,'%T')>DATE_FORMAT('0001-01-01 {eatTimeInterval.PMStartTime}','%T') and DATE_FORMAT(a.CreateAt,'%T')<DATE_FORMAT('0001-01-01 {eatTimeInterval.PMEndTime}','%T')	then 'PM'
						                    else 'AT' end ) as  TimeInterval
		                    from  bpa_memberaccountrecord as a
		                    LEFT JOIN bpa_memberinfo as b  on a.MemberInfoId=b.Id
                            WHERE a.CreateAt>='{inputDto.StartTime.ToString("yyyy-MM-dd HH:mm:ss")}' 
                                  and a.CreateAt<='{inputDto.EndTime.Date.AddHours(23).AddMinutes(59).AddSeconds(59).ToString("yyyy-MM-dd HH:mm:ss")}'
                                  and a.GroupId='{CurrentUser.TenantId}' 
                    )  as a
                    GROUP BY MemberInfoId,RealName,Phone,TimeInterval";

            var table = db.SqlQueryable<ReportFormInfo>(sql).ToList();

            var res = table.GroupBy(x => new
            {
                x.MemberInfoId,
                x.Phone,
                x.RealName
            }).Select(x => new ReportFormOutDto()
            {
                MemberInfoId = x.Key.MemberInfoId,
                Name = x.Key.RealName,
                Phone = x.Key.Phone,
                AMCount = (int)x.Where(a => a.TimeInterval == "AM" && a.Phone == x.Key.Phone && a.RealName == x.Key.RealName).Sum(a => a.TimeIntervalCount),
                PMCount = (int)x.Where(a => a.TimeInterval == "PM" && a.Phone == x.Key.Phone && a.RealName == x.Key.RealName).Sum(a => a.TimeIntervalCount),
                ATCount = (int)x.Where(a => a.TimeInterval == "AT" && a.Phone == x.Key.Phone && a.RealName == x.Key.RealName).Sum(a => a.TimeIntervalCount),

            }).ToList();

            //查询出所有会员的会员标签
            var memtag = await db.Queryable<BPA_MemberTagInfo, BPA_MemberTag>((a, b) =>
                    new JoinQueryInfos(
                        JoinType.Left, a.MemberTag_Id == b.Id))
                .Where(a => a.IsDeleted == 0)
                .Select((a, b) => new
                {
                    MemberInfoId = a.MemberInfo_Id,
                    TagId = b.Id,
                    TagName = b.Name

                }).ToListAsync();

            foreach (var item in res)
            {
                var info = memtag.Where(x => x.MemberInfoId == item.MemberInfoId)
                    .FirstOrDefault();
                item.Tag = info == null ? "" : info.TagName;
            }

            result.Data = res;
            return result;

        }

        private async Task<EatTimeIntervalDto> EatTimeInterval()
        {
           

            Dictionary<string, string> dic = new Dictionary<string, string>();
            var url= App.Configuration["SAAS_Manage"]+ "/api/dictdata/getdiclist?typeCode=OneCardTimeInterval";
            var json= HttpHelper.HttpGet(url,"",dic,"");

            var data = JsonConvert.DeserializeObject<ResultEntity<List<DictDataDto>>>(json);
            var result = new EatTimeIntervalDto();

            foreach (var item in data.Data)
            {
                item.Value = item.Code switch
                {
                    "AMStartTime" => result.AMStartTime = item.Value,
                    "AMEndTime" => result.AMEndTime = item.Value,
                    "PMStartTime" => result.PMStartTime = item.Value,
                    "PMEndTime" => result.PMEndTime = item.Value,
                    "ATStartTime" => result.ATStartTime = item.Value,
                    "ATEndTime" => result.ATEndTime = item.Value,
                    _ => item.Value
                };
            }
            return result;
        }
    }
}
