OA2023/OASystem/OASystem.Api/Controllers/StatisticsController.cs

using Aspose.Cells;
using Aspose.Words.Tables;
using EyeSoft.Collections.Generic;
using Google.Protobuf.WellKnownTypes;
using Microsoft.AspNetCore.Mvc.ViewEngines;
using Microsoft.EntityFrameworkCore.Metadata.Internal;
using MySqlX.XDevAPI.Relational;
using NetUV.Core.Handles;
using NodaTime;
using NPOI.POIFS.Properties;
using NPOI.SS.Formula.Functions;
using NPOI.SS.Formula.PTG;
using OASystem.API.OAMethodLib;
using OASystem.API.OAMethodLib.JuHeAPI;
using OASystem.Domain.AesEncryption;
using OASystem.Domain.Dtos.Statistics;
using OASystem.Domain.Entities.Customer;
using OASystem.Domain.Entities.Groups;
using OASystem.Domain.ViewModels.Financial;
using OASystem.Domain.ViewModels.JuHeExchangeRate;
using OASystem.Domain.ViewModels.QiYeWeChat;
using OASystem.Domain.ViewModels.Statistics;
using OASystem.Infrastructure.Repositories.Groups;
using System;
using System.Data;
using static OASystem.API.OAMethodLib.GeneralMethod;
using TypeInfo = OASystem.Domain.ViewModels.Statistics.TypeInfo;

namespace OASystem.API.Controllers
{
    /// <summary>
    /// 统计模块
    /// </summary>
    [Route("api/[controller]")]
    [ApiController]
    public class StatisticsController : ControllerBase
    {
        private readonly int _decimalPlaces;
        private readonly IConfiguration _config;
        private readonly IMapper _mapper;
        private readonly SqlSugarClient _sqlSugar;
        private readonly DelegationInfoRepository _groupRep;
        private readonly SetDataRepository _setDataRep;
        private readonly TeamRateRepository _teamRateRep;
        private readonly VisitingClientsRepository _visitingClientsRep;
        private readonly IJuHeApiService _juHeApiService;

        /// <summary>
        /// Init
        /// </summary>
        /// <param name="mapper"></param>
        /// <param name="config"></param>
        /// <param name="sqlSugar"></param>
        /// <param name="groupRep"></param>
        /// <param name="setDataRep"></param>
        /// <param name="teamRate"></param>
        /// <param name="visitingClientsRep"></param>
        /// <param name="juHeApiService"></param>
        public StatisticsController(
            IMapper mapper,
            IConfiguration config,
            SqlSugarClient sqlSugar,
            DelegationInfoRepository groupRep,
            SetDataRepository setDataRep,
            TeamRateRepository teamRate,
            VisitingClientsRepository visitingClientsRep,
            IJuHeApiService juHeApiService
            )
        {
            _mapper = mapper;
            _config = config;
            _groupRep = groupRep;
            _setDataRep = setDataRep;
            _sqlSugar = sqlSugar;
            _teamRateRep = teamRate;
            _visitingClientsRep = visitingClientsRep;
            _juHeApiService = juHeApiService;
        }

        #region 团组报表

        /// <summary>
        ///  团组报表
        ///  Items 
        /// </summary>
        /// <param name="_dto">团组列表请求dto</param>
        /// <returns></returns>
        [HttpPost("PostGroupStatementItems")]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> PostGroupStatementItems(GroupStatementItemsDto _dto)
        {
            #region  参数验证
            if (_dto.UserId < 1) return Ok(JsonView(false, "员工Id为空"));
            if (_dto.PageId < 1) return Ok(JsonView(false, "页面Id为空"));

            PageFunAuthViewBase pageFunAuthView = new PageFunAuthViewBase();
            #region 页面操作权限验证
            pageFunAuthView = await GeneralMethod.PostUserPageFuncDatas(_dto.UserId, _dto.PageId);

            if (pageFunAuthView.CheckAuth == 0) return Ok(JsonView(false, "您没有查看权限"));
            #endregion

            #endregion
            if (_dto.PortType == 1 || _dto.PortType == 2 || _dto.PortType == 3) // web/Android/IOS
            {
                string sqlWhere = string.Empty;
                if (_dto.IsSure == 0) //未完成
                {
                    sqlWhere += string.Format(@" And IsSure = 0");
                }
                else if (_dto.IsSure == 1) //已完成
                {
                    sqlWhere += string.Format(@" And IsSure = 1");
                }

                if (!string.IsNullOrEmpty(_dto.SearchCriteria))
                {
                    string tj = _dto.SearchCriteria;
                    sqlWhere += string.Format(@"And (ssd.Name Like '%{0}%' Or TeamName Like '%{1}%' Or ClientName Like '%{2}%' Or  ClientName Like '%{3}%' Or su.CnName  Like '%{4}%')",
                       tj, tj, tj, tj, tj);
                }

                string sql = string.Format(@"Select row_number() over(order by gdi.VisitDate Desc) as Row_Number, 
											    gdi.Id,TourCode,ssd1.Id TeamLevId,ssd1.Name TeamLev,TeamName,
											    ClientName,ClientUnit,VisitDate,ssd.Id TeamTypeId, ssd.Name TeamType,
											    VisitDays,VisitPNumber,su.CnName JietuanOperator,IsSure,gdi.CreateTime,
											    pr.LastCollectionTime
											    From  Grp_DelegationInfo gdi
											    Left Join Sys_SetData ssd On gdi.TeamDid = ssd.Id 
											    Left Join Sys_SetData ssd1 On gdi.TeamLevSId = ssd1.Id
											    Left Join Sys_Users su On gdi.JietuanOperator = su.Id
											    Left Join (
												    SELECT Diid, MAX(CreateTime) LastCollectionTime
												    FROM Fin_ProceedsReceived
												    Where IsDel = 0
												    GROUP BY Diid
											    ) pr On gdi.Id = pr.Diid
										    Where gdi.IsDel = 0 {0} ", sqlWhere);

                RefAsync<int> total = 0;//REF和OUT不支持异步,想要真的异步这是最优解
                var _DelegationList = await _sqlSugar.SqlQueryable<GroupStatementItemView>(sql).ToPageListAsync(_dto.PageIndex, _dto.PageSize, total);//ToPageAsync

                var _view = new
                {
                    PageFuncAuth = pageFunAuthView,
                    Data = _DelegationList
                };
                return Ok(JsonView(true, "查询成功!", _view, total));

            }
            else
            {
                return Ok(JsonView(false, "查询失败"));
            }
        }

        /// <summary>
        ///  团组报表
        ///  Details 
        /// </summary>
        /// <param name="_dto">团组列表请求dto</param>
        /// <returns></returns>
        [HttpPost("PostGroupStatementDetails")]
        //[JsonConverter(typeof(DecimalConverter), 2)]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> PostGroupStatementDetails(GroupStatementDetailsDto _dto)
        {
            /* 
             * 团组报表计算方式
             * 当前总支出 = 团组支出.Sum() + 超支费用.Sum()
             * 应收金额 = 应收表.Sum()
             * 已收金额 = 已收表.Sum()
             * 应收利润(应收-支出) = 应收金额 - 收款退还 - 当前总支出
             * 已收利润(已收-支出) = 已收金额 - 收款退还 - 当前总支出
             * 
             */

            #region  参数验证
            if (_dto.UserId < 1) return Ok(JsonView(false, "员工Id为空"));
            if (_dto.PageId < 1) _dto.PageId = 38; //团组报表页面Id
            if (_dto.DiId < 1) return Ok(JsonView(false, "团组Id为空"));

            PageFunAuthViewBase pageFunAuthView = new PageFunAuthViewBase();
            #region 页面操作权限验证
            pageFunAuthView = await GeneralMethod.PostUserPageFuncDatas(_dto.UserId, _dto.PageId);

            if (pageFunAuthView.CheckAuth == 0) return Ok(JsonView(false, "您没有查看权限"));
            #endregion


            #endregion
            if (_dto.PortType == 1 || _dto.PortType == 2 || _dto.PortType == 3) // web/Android/IOS
            {
                GroupStatementDetailsView _view = new GroupStatementDetailsView();

                #region 费用类型 币种，转账,客户信息

                List<Sys_SetData> _setDatas = await _sqlSugar.Queryable<Sys_SetData>().Where(it => it.IsDel == 0).ToListAsync();
                var _clientDatas = await _sqlSugar.Queryable<Crm_DeleClient>()
                    .Where(it => it.IsDel == 0)
                    .Select(x => new Crm_DeleClient { Id = x.Id, FirstName = x.FirstName, LastName = x.LastName, Sex = x.Sex })
                    .ToListAsync();
                foreach (var item in _clientDatas) EncryptionProcessor.DecryptProperties(item);

                var groupInfo = await _sqlSugar.Queryable<Grp_DelegationInfo>().Where(x => x.Id == _dto.DiId).FirstAsync();
                var visitDate = groupInfo.VisitDate;

                #endregion

                #region 团组收入

                GroupIncomeView _giView = new GroupIncomeView();

                /*
                 * 应收报表
                 * 增加方式=实际报价时 费用必须审核才能进入团组报表
                 */
                decimal frTotalAmount = 0.00M;//应收总金额
                string _frSql = string.Format(@"  Select fr.Id,fr.AddingWay,fr.Status,u.CnName As Auditor,fr.AuditTime,fr.Diid,fr.PriceName,fr.Price,fr.Count,fr.Unit,fr.Currency,
										        sd.Name As CurrencyCode,sd.Remark As CurrencyName,fr.Rate,fr.ItemSumPrice,fr.CreateTime
										        From Fin_ForeignReceivables fr
										        Left Join Sys_SetData sd On fr.Currency = sd.Id
												Left Join Sys_Users u On fr.Auditor = u.Id
										        Where fr.IsDel = 0 And fr.Diid = {0} Order By CreateTime", _dto.DiId);
                List<Gsd_ForeignReceivablesView> _frViews = await _sqlSugar.SqlQueryable<Gsd_ForeignReceivablesView>(_frSql).ToListAsync();


                //List<Gsd_ForeignReceivablesView> _frViews = new List<Gsd_ForeignReceivablesView>();
                //if (_frViews1.Count > 0)
                //{
                //    _frViews.AddRange(_frViews1.Where(x => x.AddingWay != 2).ToList());
                //    _frViews.AddRange(_frViews1.Where(x => x.AddingWay == 2 && x.Status == 1).ToList());
                //}

                _frViews.ForEach(x =>
                {
                    string namePrefix = string.Empty;
                    if (x.AddingWay == 0) namePrefix = $"账单模块-";
                    else if (x.AddingWay == 1) namePrefix = $"成本预算模块-";
                    else if (x.AddingWay == 2) namePrefix = $"实际报价-";
                    x.PriceName = $"{namePrefix}{x.PriceName}";
                });

                frTotalAmount = _frViews.Sum(it => it.ItemSumPrice);
                _giView.Receivables = _frViews;
                _giView.ReceivableStr = string.Format(@"应收款合计：{0} CNY(人民币)", frTotalAmount.ConvertToDecimal1().ToString("#0.00"));

                /*
                 * 已收报表
                 */
                decimal prTotalAmount = 0.00M;//已收总金额
                string _prSql = string.Format(@"Select pr.Id,pr.Diid,pr.SectionTime As SectionTimeDt,pr.Price,pr.Currency,
										        sd1.Name As CurrencyCode,sd1.Remark As CurrencyName,pr.Client,
										        pr.ReceivablesType,sd2.Name As ReceivablesTypeName,pr.Remark,pr.CreateTime
										        From Fin_ProceedsReceived  pr
										        Left Join Sys_SetData sd1 On pr.Currency = sd1.Id
										        Left Join Sys_SetData sd2 On pr.ReceivablesType = sd2.Id
										        Where pr.IsDel = 0 and pr.Diid = {0} Order By CreateTime", _dto.DiId);
                List<Gsd_ProceedsReceivedView> _prViews = await _sqlSugar.SqlQueryable<Gsd_ProceedsReceivedView>(_prSql).ToListAsync();
                prTotalAmount = _prViews.Sum(it => it.Price);
                _giView.ProceedsReceivedViews = _prViews;
                _giView.ProceedsReceivedStr = string.Format(@$"应收合计：{frTotalAmount:#0.00} CNY  已收款合计：{prTotalAmount.ConvertToDecimal1():#0.00} CNY");


                /*
                 * 超支费用 
                 */
                decimal exTotalAmount = 0.00M;
                //    string ecSql = string.Format(@"Select gec.Id As GECId,gec.DiId As GECDiId,gec.PriceName,(gec.PriceSum * gec.Coefficient) As PayMoney,sd1.Name As PaymentCurrency,
                //	   (gec.PriceSum * gec.Coefficient * ccp.DayRate) As CNYPrice,ccp.DayRate,ccp.Payee,ccp.OrbitalPrivateTransfer,sd2.Name As PayWay,
                //	   sd3.Name As CardType,ccp.IsPay,u.CnName As Applicant,gec.CreateTime
                //From OA2023DB.dbo.Fin_GroupExtraCost gec
                //Left Join Grp_CreditCardPayment ccp On gec.Id = ccp.CId
                //                                    Left Join Sys_SetData sd1 On ccp.PaymentCurrency = sd1.Id
                //                                    Left Join Sys_SetData sd2 On ccp.PayDId = sd2.Id 
                //                                    Left Join Sys_SetData sd3 On ccp.CTDId = sd3.Id
                //                                    Left Join Sys_Users u On ccp.CreateUserId = u.Id
                //Where ccp.IsDel = 0 And ccp.CTable = 1015 {1} And ccp.DiId = {0} Order By CreateTime", _dto.DiId, _dto.isAudit ? "And ccp.IsAuditGM = 1 And ccp.IsPay = 1 " : " ");

                //    List<Gsd_ExtraCostsView> _ExtraCostsViews = await _sqlSugar.SqlQueryable<Gsd_ExtraCostsView>(ecSql).ToListAsync();

                //    #region 超支费用 - 模拟数据

                //    //if (_ExtraCostsViews.Count < 1)
                //    //{
                //    //    _ExtraCostsViews.Add(new Gsd_ExtraCostsView()
                //    //    {
                //    //        GECId = 0,
                //    //        GECDiId = 2334,
                //    //        PriceName = "模拟数据-超支费用名称",
                //    //        PayMoney = 1000.00M,
                //    //        PaymentCurrency = "CNY",
                //    //        DayRate = 1.0000M,
                //    //        CNYPrice = 1000.00M,
                //    //        Payee = "模拟数据-超支费用收款方",
                //    //        OrbitalPrivateTransfer = 1,
                //    //        PayWay = "刷卡",
                //    //        CardType = "招行卡",
                //    //        IsPay = 1,
                //    //        Applicant = "刘华举"
                //    //    });
                //    //    _ExtraCostsViews.Add(new Gsd_ExtraCostsView()
                //    //    {
                //    //        GECId = 0,
                //    //        GECDiId = 2334,
                //    //        PriceName = "模拟数据-超支费用名称",
                //    //        PayMoney = 1000.00M,
                //    //        PaymentCurrency = "CNY",
                //    //        DayRate = 1.0000M,
                //    //        CNYPrice = 1000.00M,
                //    //        Payee = "模拟数据-超支费用收款方",
                //    //        OrbitalPrivateTransfer = 1,
                //    //        PayWay = "刷卡",
                //    //        CardType = "招行卡",
                //    //        IsPay = 1,
                //    //        Applicant = "刘华举"
                //    //    });
                //    //}
                //    #endregion


                //    exTotalAmount = _ExtraCostsViews.Sum(it => it.CNYPrice);

                //    _giView.ExtraCostsViews = _ExtraCostsViews;
                //    _giView.ExtraCostsStr = string.Format(@"人民币总费用：{0} CNY", exTotalAmount.ConvertToDecimal1().ToString("#0.00"));

                /*
                 * 收款退还
                 */
                decimal promTotalAmount = 0.00M;// 收款退还总金额
                List<Gsd_PaymentRefundAndOtherMoneyView> _promView = new List<Gsd_PaymentRefundAndOtherMoneyView>();

                //删除了  And prom.PriceType = 1
                string _ropSql = string.Format(@"Select u.CnName As Appliction,prom.Id As PrId,prom.DiId As PrDiId,prom.Price As PrPrice,
										         prom.PriceName AS PrPriceName,prom.CurrencyId As PrCurrencyId,
										         prom.PayType As PrPayType,prom.PriceType As PrPriceType,
												 ccp.RMBPrice * ccp.DayRate As RMBPrice,ccp.*,prom.CreateTime As PrCreateTime
										         From Fin_PaymentRefundAndOtherMoney prom
										         Left Join Grp_CreditCardPayment ccp On prom.DiId = ccp.DIId And prom.Id = ccp.CId
										         Left Join Sys_Users u On ccp.CreateUserId = u.Id
										         Where prom.IsDel = 0  
                                                 And prom.PayType = 1 
                                                 And ccp.CTable = 285
										         {1} 
										         And prom.DiId = {0} Order By PrCreateTime", _dto.DiId, _dto.isAudit ? "And ccp.IsAuditGM = 1" : " ");
                var _promDatas = await _sqlSugar.SqlQueryable<Gsd_PaymentRefundAndOtherMoneyDataSource1View>(_ropSql).ToListAsync();

                foreach (var ropItem in _promDatas)
                {
                    string thisCueencyCode = "Unknown";
                    string thisCueencyName = "Unknown";
                    var currency = _setDatas.Where(it => it.Id == ropItem.PaymentCurrency).FirstOrDefault();
                    if (currency != null)
                    {
                        thisCueencyCode = currency.Name;
                        thisCueencyName = currency.Remark;
                    }

                    string orbitalPrivateTransferStr = "Unknown";
                    var orbitalPrivateTransfer = _setDatas.Where(it => it.Id == ropItem.OrbitalPrivateTransfer).FirstOrDefault();
                    if (orbitalPrivateTransfer != null)
                    {
                        orbitalPrivateTransferStr = orbitalPrivateTransfer.Name;
                    }

                    string payStr = "Unknown";
                    var pay = _setDatas.Where(it => it.Id == ropItem.PayDId).FirstOrDefault();
                    if (pay != null)
                    {
                        payStr = pay.Name;
                    }

                    Gsd_PaymentRefundAndOtherMoneyView gsd_PaymentRefund = new Gsd_PaymentRefundAndOtherMoneyView()
                    {
                        Id = ropItem.Id,
                        DiId = ropItem.DIId,
                        PriceName = ropItem.PrPriceName,
                        PayCurrencyCode = thisCueencyCode,
                        PayCurrencyName = thisCueencyName,
                        Price = ropItem.PrPrice,
                        CNYPrice = ropItem.PayMoney * ropItem.DayRate,
                        ThisRate = ropItem.DayRate,
                        Payee = ropItem.Payee,
                        PayTime = ropItem.AuditGMDate,
                        OrbitalPrivateTransfer = ropItem.OrbitalPrivateTransfer,
                        PayType = payStr,
                        IsPay = ropItem.IsPay,
                        Applicant = ropItem.Appliction

                    };

                    _promView.Add(gsd_PaymentRefund);
                }

                #region 收款退还 - 模拟数据

                //if (_promView.Count < 1)
                //{
                //    _promView.Add(new Gsd_PaymentRefundAndOtherMoneyView()
                //    {
                //        Id = 0,
                //        DiId = 2334,
                //        PriceName = "模拟数据-费用名称",
                //        PayCurrencyCode = "CNY",
                //        PayCurrencyName = "人民币",
                //        Price = 1000.00M,
                //        CNYPrice = 1000.00M,
                //        ThisRate = 1.00M,
                //        Payee = "模拟数据-收款方",
                //        PayTime = "2023-01-01 15:20:01",
                //        OrbitalPrivateTransfer = 1,
                //        PayType = "刷卡",
                //        IsPay = 1,
                //        Applicant = "刘华举"
                //    });
                //    _promView.Add(new Gsd_PaymentRefundAndOtherMoneyView()
                //    {
                //        Id = 0,
                //        DiId = 2334,
                //        PriceName = "模拟数据-费用名称",
                //        PayCurrencyCode = "CNY",
                //        PayCurrencyName = "人民币",
                //        Price = 1000.00M,
                //        CNYPrice = 1000.00M,
                //        ThisRate = 1.00M,
                //        Payee = "模拟数据-收款方",
                //        PayTime = "2023-01-01 15:20:01",
                //        OrbitalPrivateTransfer = 1,
                //        PayType = "刷卡",
                //        IsPay = 1,
                //        Applicant = "刘华举"
                //    });
                //}
                #endregion

                promTotalAmount = _promView.Sum(it => it.CNYPrice);
                _giView.PaymentRefundAndOtherMoneyViews = _promView;
                _giView.PaymentRefundAndOtherMoneyStr = string.Format(@"人民币总费用：{0} CNY", promTotalAmount.ConvertToDecimal1().ToString("#0.00"));

                decimal BalancePayment = frTotalAmount - prTotalAmount + promTotalAmount;
                _view.GroupIncome = _giView;
                _view.GroupIncomeStr = string.Format(@"<span style='color:red;'>剩余尾款：{0} CNY(包含了收款退还费用数据)</span>", BalancePayment.ConvertToDecimal1().ToString("#0.00"));
                #endregion

                #region 团组支出
                GroupExpenditureView _geView = new GroupExpenditureView();

                #region 酒店预定费用
                List<GroupHotelFeeView> groupHotelFeeViews = new List<GroupHotelFeeView>();

                //ccp.RMBPrice As CNYPrice
                //(((ccp.PayMoney * ccp.DayRate) / ccp.PayPercentage) * 100) As CNYPrice
                string hotelFeeSql = string.Format(@"
SELECT
  hr.Id AS HrId,
  hr.DiId AS HrDiId,
  hr.City,
  hr.HotelName,
  hr.CheckInDate,
  hr.CheckOutDate,
  hr.CardPrice AS RoomPrice,
  sd1.Name AS PaymentCurrency,
  hr.SingleRoomPrice,
  hr.SingleRoomCount,
  hr.DoubleRoomPrice,
  hr.DoubleRoomCount,
  hr.SuiteRoomPrice,
  hr.SuiteRoomCount,
  hr.OtherRoomPrice,
  hr.OtherRoomCount,
  hr.BreakfastPrice,
  sd4.Name AS BreakfastCurrency,
  hr.Isoppay,
  hr.GovernmentRent,
  sd5.Name AS GovernmentRentCurrency,
  hr.CityTax,
  sd6.Name AS CityTaxCurrency,
  ccp.PayMoney,
  (
    ((ccp.PayMoney * ccp.DayRate) / ccp.PayPercentage) * 100
  ) AS CNYPrice,
  ccp.PayPercentage,
  ccp.DayRate,
  ccp.Payee,
  ccp.OrbitalPrivateTransfer,
  sd2.Name AS PayWay,
  sd3.Name AS CardType,
  ccp.IsPay,
  u.CnName AS Applicant
FROM
  Grp_HotelReservations hr
  INNER JOIN Grp_CreditCardPayment ccp ON hr.Id = ccp.CId
  LEFT JOIN Sys_SetData sd1 ON ccp.PaymentCurrency = sd1.Id
  LEFT JOIN Sys_SetData sd2 ON ccp.PayDId = sd2.Id
  LEFT JOIN Sys_SetData sd3 ON ccp.CTDId = sd3.Id
  LEFT JOIN Sys_Users u ON ccp.CreateUserId = u.Id
  LEFT JOIN Sys_SetData sd4 ON hr.BreakfastCurrency = sd4.Id
  LEFT JOIN Sys_SetData sd5 ON hr.GovernmentRentCurrency = sd5.Id
  LEFT JOIN Sys_SetData sd6 ON hr.CityTaxCurrency = sd6.Id
WHERE
  hr.IsDel = 0
  AND ccp.IsDel = 0
  AND ccp.CTable = 76 {1}
  AND ccp.PayMoney <> 0
  AND hr.DiId = {0}
ORDER BY
  CheckInDate Asc", _dto.DiId, _dto.isAudit ? "AND (ccp.IsAuditGM = 1 Or ccp.IsAuditGM = 3)" : " ");

                groupHotelFeeViews = await _sqlSugar.SqlQueryable<GroupHotelFeeView>(hotelFeeSql).ToListAsync();

                List<int> hotelSubIds = groupHotelFeeViews.Select(it => it.HrId).ToList();

                var groupHotelContentFeeViews = await _sqlSugar.Queryable<Grp_HotelReservationsContent>().Where(it => hotelSubIds.Contains(it.HrId)).ToListAsync();

                decimal HotelCNYTotalPrice = 0.00M;

                var teamRateData = await _teamRateRep.PostGroupRateInfoByDiId(_dto.DiId);
                foreach (var item in groupHotelFeeViews)
                {
                    if (groupHotelContentFeeViews.Count > 0)
                    {

                        string paymentStr = string.Empty;

                        var roomData = groupHotelContentFeeViews.Find(it => it.HrId == item.HrId && it.PriceType == 1); //房费 


                        item.RoomPrice = roomData?.Price ?? 0.00M;
                        if (item.RoomPrice != 0)
                        {
                            if (roomData.IsPay == 0) paymentStr += $"房费：未付款<br/>";

                            item.RoomPriceCurrency = _setDatas.Find(it => it.Id == roomData?.Currency)?.Name;
                            string feeMark1 = roomData?.OrbitalPrivateTransfer == 0 ? "公转" : "私转";
                            string isFeeMark1 = roomData?.IsOppay == 1 ? "是" : "否";
                            item.RoomInfoTips = @$"当时汇率：{roomData?.Rate.ToString("#0.0000")} <br/>
                                           收款方：{roomData?.Payee}<br/>
                                           费用标识：{feeMark1} <br/>
                                           支付方式：{_setDatas.Find(it => it.Id == roomData?.PayDId)?.Name} <br/>
                                           卡类型：{_setDatas.Find(it => it.Id == roomData?.CTDId)?.Name}<br/>
                                           是否由地接支付：{isFeeMark1} <br/>";
                        }

                        var breakfastData = groupHotelContentFeeViews.Find(it => it.HrId == item.HrId && it.PriceType == 2); //早餐 

                        item.BreakfastPrice = breakfastData?.Price ?? 0.00M;

                        if (item.BreakfastPrice != 0)
                        {
                            if (breakfastData.IsPay == 0) paymentStr += $"早餐：未付款<br/>";

                            item.BreakfastCurrency = _setDatas.Find(it => it.Id == breakfastData?.Currency)?.Name;
                            string feeMark2 = breakfastData?.OrbitalPrivateTransfer == 0 ? "公转" : "私转";
                            string isFeeMark2 = breakfastData?.IsOppay == 1 ? "是" : "否";
                            item.BreakfastInfoTips = @$"当时汇率：{breakfastData?.Rate.ToString("#0.0000")} <br/>
                                                收款方：{breakfastData?.Payee}<br/>
                                                费用标识：{feeMark2} <br/>
                                                支付方式：{_setDatas.Find(it => it.Id == roomData?.PayDId)?.Name} <br/>
                                                卡类型：{_setDatas.Find(it => it.Id == roomData?.CTDId)?.Name} <br/>
                                                是否由地接支付：{isFeeMark2} <br/>";
                        }

                        var landTaxData = groupHotelContentFeeViews.Find(it => it.HrId == item.HrId && it.PriceType == 3); //地税

                        item.GovernmentRent = landTaxData?.Price ?? 0.00M;

                        if (item.GovernmentRent != 0)
                        {
                            if (landTaxData.IsPay == 0) paymentStr += $"地税：未付款<br/>";

                            item.GovernmentRentCurrency = _setDatas.Find(it => it.Id == landTaxData?.Currency)?.Name;
                            string feeMark3 = landTaxData?.OrbitalPrivateTransfer == 0 ? "公转" : "私转";
                            string isFeeMark3 = landTaxData?.IsOppay == 1 ? "是" : "否";
                            item.GovernmentRentTips = @$"当时汇率：{landTaxData?.Rate.ToString("#0.0000")} <br/>
                                                 收款方：{landTaxData?.Payee}<br/>
                                                 费用标识：{feeMark3} <br/>
                                                 支付方式：{_setDatas.Find(it => it.Id == landTaxData?.PayDId)?.Name} <br/>
                                                 卡类型：{_setDatas.Find(it => it.Id == landTaxData?.CTDId)?.Name} <br/>
                                                 是否由地接支付：{isFeeMark3} <br/>";
                        }

                        var cityTaxData = groupHotelContentFeeViews.Find(it => it.HrId == item.HrId && it.PriceType == 4); //城市税 

                        item.CityTax = cityTaxData?.Price ?? 0.00M;

                        if (item.CityTax != 0)
                        {
                            if (cityTaxData.IsPay == 0) paymentStr += $"城市税：未付款<br/>";
                            item.CityTaxCurrency = _setDatas.Find(it => it.Id == cityTaxData?.Currency)?.Name;
                            string feeMark4 = cityTaxData?.OrbitalPrivateTransfer == 0 ? "公转" : "私转";
                            string isFeeMark4 = landTaxData?.IsOppay == 1 ? "是" : "否";
                            item.CityTaxTips = @$"当时汇率：{cityTaxData?.Rate.ToString("#0.0000")} <br/>
                                          收款方：{cityTaxData?.Payee}<br/>
                                          费用标识：{feeMark4} <br/>
                                          支付方式：{_setDatas.Find(it => it.Id == cityTaxData?.PayDId)?.Name} <br/>
                                          卡类型：{_setDatas.Find(it => it.Id == cityTaxData?.CTDId)?.Name} <br/>
                                          是否由地接支付：{isFeeMark4} <br/>";
                        }

                        if (!string.IsNullOrEmpty(paymentStr))
                        {
                            item.IsPay = 2;
                            item.PayTips = paymentStr;
                        }
                        //item.CNYPrice = roomData?.Price ?? 0.00M * roomData?.Rate ?? 0.00M +
                        //                breakfastData?.Price ?? 0.00M * breakfastData?.Rate ?? 0.00M +
                        //                landTaxData?.Price ?? 0.00M * landTaxData?.Rate ?? 0.00M +
                        //                cityTaxData?.Price ?? 0.00M * cityTaxData?.Rate ?? 0.00M;
                    }
                    else
                    {
                        decimal roomPrice = (item.SingleRoomCount * item.SingleRoomPrice) +
                                            (item.DoubleRoomCount * item.DoubleRoomPrice) +
                                            (item.SuiteRoomCount * item.SuiteRoomPrice) +
                                            (item.OtherRoomCount * item.OtherRoomPrice);
                        //item.RoomPrice = item.CardPrice;
                        item.RoomPriceCurrency = item.PaymentCurrency;
                    }

                    HotelCNYTotalPrice += item.CNYPrice;
                    item.PayMoney = item.PayMoney.ConvertToDecimal1();
                    item.CNYPrice = item.CNYPrice.ConvertToDecimal1();
                }

                _geView.GroupHotelFeeViews = groupHotelFeeViews;
                _geView.GroupHotelFeeStr = string.Format(@"人民币总费用：{0} CNY", HotelCNYTotalPrice.ToString("#0.00"));

                #endregion

                #region 地接费用

                List<GroupCTGGRFeeView> groupCTGGRFeeViews = new List<GroupCTGGRFeeView>();
                string CTGGRFeeSql = string.Empty;
                if (visitDate > Convert.ToDateTime("2024-04-17"))
                {
                    CTGGRFeeSql = string.Format(@"Select ctggr.Id As CTGGRId,ctggr.DiId As CTGGRDiId,ctggr.PriceName As Area,ctggrc.*,ctggrc.Price As PayMoney,
                                                     sd2.name As PaymentCurrency,ccp.PayPercentage,
                                                     (ctggrc.Price * (ccp.PayPercentage / 100)) As AmountPaid,
                                                     (ctggrc.Price - ctggrc.Price * (ccp.PayPercentage / 100)) As BalancePayment,
                                                     ccp.DayRate,(ctggrc.Price * (ccp.PayPercentage / 100) * ccp.DayRate) As CNYPrice,ccp.Payee,ccp.AuditGMDate,
                                                     ccp.OrbitalPrivateTransfer,sd1.Name As PayWay,ccp.IsPay,u.CnName As Applicant,ctggr.CreateTime
                                                     From Grp_CarTouristGuideGroundReservations ctggr
                                                     Left Join ( Select cggrc.CTGGRId,sd1.Name As PriceName,cggrc.Price*cggrc.Count As Price,sd2.Name As PriceCurrency,
                                                                        cggrc.DatePrice,cggrc.PriceContent
                                                             From Grp_CarTouristGuideGroundReservationsContent cggrc
                                                             Left Join Sys_SetData sd1 On cggrc.SId = sd1.Id
                                                             Left Join Sys_SetData sd2 On cggrc.Currency = sd2.Id
                                                             Where cggrc.ISdel = 0 And cggrc.Price != 0.00 
                                                             ) ctggrc On ctggr.Id = ctggrc.CTGGRId 
                                                     Left Join Grp_CreditCardPayment ccp On ccp.IsDel = 0  And ccp.CTable = 79 And ctggr.Id = ccp.CId
                                                     Left Join Sys_SetData sd1 On ccp.PayDId = sd1.Id
                                                     Left Join Sys_SetData sd2 On ccp.PaymentCurrency = sd2.Id
                                                     Left Join Sys_Users u On ccp.CreateUserId = u.Id
                                                     Where ctggr.IsDel = 0 {1} And ctggr.DiId = {0}
                                                     Order By CreateTime", _dto.DiId, _dto.isAudit ? "And (ccp.IsAuditGM = 1 OR ccp.IsAuditGM = 3)" : " ");
                }
                else
                {
                    CTGGRFeeSql = string.Format(@"Select ctggr.Id As CTGGRId,ctggr.DiId As CTGGRDiId,ctggr.PriceName As Area,
													 ccp.PayMoney,sd2.name As PaymentCurrency,ccp.PayPercentage,
													 (ccp.PayMoney * (ccp.PayPercentage / 100)) As AmountPaid,
													 (ccp.PayMoney -ccp.PayMoney * (ccp.PayPercentage / 100)) As BalancePayment,
                                                     ccp.DayRate,(ccp.PayMoney * (ccp.PayPercentage / 100) * ccp.DayRate) As CNYPrice,
													 ccp.Payee,ccp.AuditGMDate,
                                                     ccp.OrbitalPrivateTransfer,sd1.Name As PayWay,ccp.IsPay,u.CnName As Applicant,ctggr.CreateTime
                                                     From Grp_CarTouristGuideGroundReservations ctggr
                                                     Left Join Grp_CreditCardPayment ccp On ccp.IsDel = 0  And ccp.CTable = 79 And ctggr.Id = ccp.CId
                                                     Left Join Sys_SetData sd1 On ccp.PayDId = sd1.Id
                                                     Left Join Sys_SetData sd2 On ccp.PaymentCurrency = sd2.Id
                                                     Left Join Sys_Users u On ccp.CreateUserId = u.Id
                                                     Where ctggr.IsDel = 0 {1} And ctggr.DiId = {0}
                                                     Order By CreateTime", _dto.DiId, _dto.isAudit ? "And (ccp.IsAuditGM = 1 OR ccp.IsAuditGM = 3)" : " ");
                }

                groupCTGGRFeeViews = await _sqlSugar.SqlQueryable<GroupCTGGRFeeView>(CTGGRFeeSql).ToListAsync();

                string CTGGRFeeStr = "";
                decimal CTGGRCNYTotalPrice = 0.00M;

                //按1 地区，2 币种，3 汇率 分组计算
                var groupCTGGRFeeDatas = groupCTGGRFeeViews.GroupBy(it => it.Area);

                foreach (var ctggfr in groupCTGGRFeeDatas)
                {
                    var ctggfr_curr = ctggfr.GroupBy(it => it.PaymentCurrency);

                    if (ctggfr_curr.Count() > 0)
                    {
                        foreach (var curr in ctggfr_curr)
                        {

                            var ctggfr_rate = curr.GroupBy(it => it.DayRate);

                            if (ctggfr_rate.Count() > 0)
                            {
                                foreach (var rate in ctggfr_rate)
                                {
                                    CTGGRFeeStr += string.Format(@$"{ctggfr.Key} 总费用：{rate.Sum(it => it.AmountPaid).ToString("#0.00")} 
                                                        {rate.FirstOrDefault()?.PaymentCurrency}(人民币：
                                                        {rate.Sum(it => Convert.ToDecimal(it.CNYPrice.ToString("#0.00"))).ToString("#0.00")} CNY 当时支付汇率：
                                                        {rate.FirstOrDefault()?.DayRate.ToString("#0.0000")})\r\n");
                                    CTGGRCNYTotalPrice += rate.Sum(it => Convert.ToDecimal(it.CNYPrice.ToString("#0.00")));
                                }
                            }
                            else
                            {
                                CTGGRFeeStr += string.Format(@$"{ctggfr.Key} 总费用：{curr.Sum(it => it.AmountPaid).ToString("#0.00")} 
                                                        {curr.FirstOrDefault()?.PaymentCurrency}(人民币：
                                                        {curr.Sum(it => Convert.ToDecimal(it.CNYPrice.ToString("#0.00"))).ToString("#0.00")} CNY 当时支付汇率：
                                                        {curr.FirstOrDefault()?.DayRate.ToString("#0.0000")})\r\n");
                                CTGGRCNYTotalPrice += curr.Sum(it => Convert.ToDecimal(it.CNYPrice.ToString("#0.00")));
                            }
                        }
                    }
                    else
                    {
                        CTGGRFeeStr += string.Format(@$"{ctggfr.Key} 总费用：{ctggfr.Sum(it => it.AmountPaid).ToString("#0.00")} 
                                                        {ctggfr.FirstOrDefault()?.PaymentCurrency}(人民币：
                                                        {ctggfr.Sum(it => Convert.ToDecimal(it.CNYPrice.ToString("#0.00"))).ToString("#0.00")} CNY 当时支付汇率：
                                                        {ctggfr.FirstOrDefault()?.DayRate.ToString("#0.0000")})\r\n");
                        CTGGRCNYTotalPrice += ctggfr.Sum(it => Convert.ToDecimal(it.CNYPrice.ToString("#0.00")));
                    }

                }

                foreach (var item in groupCTGGRFeeViews)
                {
                    if (!string.IsNullOrEmpty(item.AuditGMDate))
                    {
                        item.AuditGMDate = Convert.ToDateTime(item.AuditGMDate).ToString("yyyy-MM-dd HH:mm:ss");
                    }

                    if (item.DatePrice != null)
                    {
                        item.PriceName = $"{item.PriceName}({Convert.ToDateTime(item.DatePrice).ToString("yyyy-MM-dd")})";
                    }

                    //CTGGRFeeStr += string.Format(@"{0} 总费用：{1} {2}(人民币：{3} CNY 当时支付汇率：{4})\r\n", 
                    //    item.Area, item.AmountPaid.ConvertToDecimal1().ToString("#0.00"), item.PaymentCurrency, item.CNYPrice.ToString("#0.0000"), item.DayRate.ToString("#0.0000"));
                    //CTGGRCNYTotalPrice += item.CNYPrice;
                }
                _geView.GroupCTGGRFeeViews = groupCTGGRFeeViews;

                _geView.GroupCTGGRFeeStr = string.Format(@"{0}人民币总费用：{1} CNY", CTGGRFeeStr, CTGGRCNYTotalPrice.ToString("#0.00"));
                #endregion

                #region 机票预订费用
                //(((ccp.PayMoney * ccp.DayRate) / ccp.PayPercentage) * 100) As CNYPrice
                //ccp.RMBPrice As CNYPrice
                List<GroupAirFeeView> groupAirFeeViews = new List<GroupAirFeeView>();
                string groupAirFeeSql = string.Format(@"Select atr.Id As AirId,atr.DIId As AirDiId,atr.FlightsCode,atr.FlightsCity,sd4.Name As AirTypeName,
                                                        atr.FlightsDate,atr.FlightsTime,atr.ClientName,atr.ClientNum,ccp.PayMoney,
                                                        sd1.Name As PayMoneyCurrency,(((ccp.PayMoney * ccp.DayRate) / ccp.PayPercentage) * 100) As CNYPrice,ccp.DayRate,ccp.Payee,ccp.AuditGMDate,
                                                        ccp.OrbitalPrivateTransfer,sd2.Name As PayWay,sd3.Name As CardType,ccp.IsPay,u.CnName As Applicant,atr.CreateTime,
                                                        atr.CType
                                                        From Grp_AirTicketReservations atr
                                                        Left Join Grp_CreditCardPayment ccp On ccp.isdel = 0 And ccp.CTable = 85 And atr.Id = ccp.CId
                                                        Left Join Sys_SetData sd1 On ccp.PaymentCurrency = sd1.Id
                                                        Left Join Sys_SetData sd2 On ccp.PayDId = sd2.Id
                                                        Left Join Sys_SetData sd3 On ccp.CTDId = sd3.Id
                                                        Left Join Sys_SetData sd4 On atr.CType = sd4.Id
                                                        Left Join Sys_Users u On ccp.CreateUserId = u.Id
                                                        Where atr.IsDel = 0 {1} And atr.DiId = {0} Order By CreateTime", _dto.DiId, _dto.isAudit ? "And ccp.IsAuditGM = 1" : " ");
                groupAirFeeViews = await _sqlSugar.SqlQueryable<GroupAirFeeView>(groupAirFeeSql).ToListAsync();

                string str = "";
                List<dynamic> airClientPris = new List<dynamic>();
                decimal AirCNYTotalPrice = 0.00M;
                decimal JJCCNYTotalPrice = 0.00M, JJCPeopleNum = 0.00M, JJCAveragePrice = 0.00M;
                decimal GWCCNYTotalPrice = 0.00M, GWCPeopleNum = 0.00M, GWCAveragePrice = 0.00M;

                //if (groupAirFeeViews.Count > 0)
                //{
                //    JJCCNYTotalPrice = groupAirFeeViews.Where(it => it.AirTypeName.Equals("经济舱")).Sum(it => it.CNYPrice);
                //    JJCPeopleNum = groupAirFeeViews.Where(it => it.AirTypeName.Equals("经济舱")).Sum(it => it.ClientNum);
                //    JJCAveragePrice = (JJCCNYTotalPrice / JJCPeopleNum).ConvertToDecimal1();
                //    GWCCNYTotalPrice = groupAirFeeViews.Where(it => it.AirTypeName.Equals("公务舱")).Sum(it => it.CNYPrice);
                //    GWCPeopleNum = groupAirFeeViews.Where(it => it.AirTypeName.Equals("公务舱")).Sum(it => it.ClientNum);
                //    GWCAveragePrice = (GWCCNYTotalPrice / GWCPeopleNum).ConvertToDecimal1();
                //}

                int Index = 0;
                foreach (var item in groupAirFeeViews)
                {
                    if (item.AirId > 2924)
                    {
                        string itemClientName = "";
                        if (!string.IsNullOrEmpty(item.ClientName))
                        {
                            System.Text.RegularExpressions.Match m_EnName = Regex.Match(item.ClientName, @"[A-Za-z]+");
                            System.Text.RegularExpressions.Match m_ZHName = Regex.Match(item.ClientName, @"[\u4e00-\u9fa5]");

                            if (m_EnName.Success || m_ZHName.Success)
                            {
                                itemClientName = item.ClientName;

                                decimal unitCost = 0.00M;
                                AirCNYTotalPrice += item.CNYPrice;
                                continue;
                            }

                            string[] clientIds = new string[] { };
                            if (item.ClientName.Contains(','))
                            {
                                clientIds = item.ClientName.Split(',');
                            }
                            else
                            {
                                clientIds = new string[] { item.ClientName };
                            }
                            if (clientIds.Length > 0)
                            {
                                int[] output = Array.ConvertAll<string, int>(clientIds, delegate (string s) { return int.Parse(s); });

                                if (output.Contains(-1))
                                {
                                    itemClientName += $@"行程单";
                                    output = output.Where(val => val != -1).ToArray();
                                }

                                var clients = _clientDatas.Where(it => output.Contains(it.Id)).ToList();
                                decimal unitCost = 0.00M;
                                unitCost = (item.PayMoney / item.ClientNum).ConvertToDecimal1();

                                int clienIndex = 1;
                                foreach (var client in clients)
                                {

                                    airClientPris.Add(new
                                    {
                                        CnName = client.LastName + client.FirstName,
                                        EnName = client.Pinyin,
                                        Price = unitCost,
                                        AirType = item.AirTypeName
                                    });

                                    string six = "";
                                    if (client.Sex == 0) six = "Mr";
                                    else if (client.Sex == 1) six = "Ms";

                                    itemClientName += string.Format(@"{0}.{1} {2};", clienIndex, client.LastName + client.FirstName, six);
                                    clienIndex++;
                                }
                            }
                        }
                        item.ClientName = itemClientName;
                    }
                    else
                    {
                        string clientPinYinName = "";
                        decimal unitCost = 0.00M;
                        int cNum = item.ClientNum == 0 ? 1 : item.ClientNum;
                        unitCost = (item.PayMoney / cNum).ConvertToDecimal1();

                        Regex r = new Regex("[0-9]");
                        string name1 = item.ClientName;
                        name1 = r.Replace(name1, "");
                        string[] clientNames = name1.Split('.');
                        for (int i = 0; i < item.ClientNum; i++)
                        {
                            string name = "";
                            if (clientNames.Length > 0)
                            {
                                int index = i + 1;
                                if (index < clientNames.Length)
                                {
                                    name = clientNames[index].Replace("MR", "").Replace("MS", "").Trim();
                                    if (!string.IsNullOrEmpty(name))
                                    {
                                        airClientPris.Add(new
                                        {
                                            CnName = name,
                                            EnName = name,
                                            Price = unitCost,
                                            AirType = item.AirTypeName
                                        });
                                    }

                                    //if (name.Length > 0)
                                    //{
                                    //    string nameLastStr = name[name.Length - 1].ToString();
                                    //    if (nameLastStr.IsNumeric())
                                    //    {
                                    //        name = name.Substring(0, name.Length - 1).Trim();
                                    //    }
                                    //}
                                }
                            }

                            clientPinYinName += string.Format(@"{0}.{1}出票价为：{2} CNY;", Index + 1, name, unitCost.ToString("#0.00"));
                        }
                    }
                    if (!string.IsNullOrEmpty(item.AuditGMDate))
                    {
                        item.AuditGMDate = Convert.ToDateTime(item.AuditGMDate).ToString("yyyy-MM-dd HH:mm:ss");
                    }
                    AirCNYTotalPrice += item.CNYPrice;
                }

                _geView.GroupAirFeeViews = groupAirFeeViews;
                if (airClientPris.Count > 0)
                {
                    var peoplePriStr = "";
                    var airClientPris1 = airClientPris.GroupBy(item => item.CnName)
                                                      .Select(group => group.First())
                                                      .ToList();
                    int airClientPrisIndex = 1;
                    foreach (var item in airClientPris1)
                    {
                        decimal price = 0.00M;
                        var prices = airClientPris.Where(it => it.CnName == item.CnName).ToList();
                        foreach (var pri in prices)
                        {
                            price += pri.Price;
                        }
                        peoplePriStr += $@"{airClientPrisIndex}.{item.EnName}出票价为: {price.ToString("#0.00")} CNY；";

                        airClientPrisIndex++;
                    }

                    if (!string.IsNullOrEmpty(peoplePriStr))
                    {
                        str = $@"其中：{peoplePriStr}";
                    }

                    //经济舱均价
                    var airJJCPris = airClientPris.Where(it => it.AirType == "经济舱").ToList();
                    if (airJJCPris.Count > 0)
                    {
                        decimal jjcTotalPrice = 0.00M;
                        foreach (var item in airJJCPris)
                        {
                            jjcTotalPrice += item.Price;
                        }
                        decimal jjcPeopleNum = airJJCPris.GroupBy(item => item.CnName)
                                                      .Select(group => group.First())
                                                      .ToList().Count;
                        JJCAveragePrice = jjcTotalPrice / jjcPeopleNum;
                    }

                    //公务舱均价
                    var airGWCPris = airClientPris.Where(it => it.AirType == "公务舱").ToList();
                    if (airGWCPris.Count > 0)
                    {
                        decimal gwcTotalPrice = 0.00M;
                        foreach (var item in airGWCPris)
                        {
                            gwcTotalPrice += item.Price;
                        }
                        decimal gwcPeopleNum = airGWCPris.GroupBy(item => item.CnName)
                                                      .Select(group => group.First())
                                                      .ToList().Count;
                        GWCAveragePrice = gwcTotalPrice / gwcPeopleNum;
                    }
                }



                _geView.GroupAirFeeStr = $@"人民币总费用：{AirCNYTotalPrice.ToString("#0.00")} CNY\r\n{str}\r\n经济舱均价为：{JJCAveragePrice.ToString("#0.00")}CNY/人;公务舱均价为：{GWCAveragePrice.ToString("#0.00")}CNY/人;";
                #endregion

                #region 签证费用

                List<GroupVisaFeeView> groupVisaFeeViews = new List<GroupVisaFeeView>();
                string groupVisaFeeSql = string.Format(@"Select vi.Id As VisaId,vi.DIId As VisaDiId,vi.VisaClient,ccp.PayMoney,sd1.Name As PayMoneyCurrency,
                                                         ccp.DayRate,ccp.Payee,ccp.AuditGMDate,ccp.OrbitalPrivateTransfer,sd2.Name As PayWay,
                                                         sd3.Name As CardTypeName,ccp.IsPay,u.CnName As Applicant,vi.CreateTime,
(((ccp.PayMoney * ccp.DayRate) / ccp.PayPercentage) * 100) As CNYPrice
                                                         From Grp_VisaInfo vi
                                                         Left Join Grp_CreditCardPayment ccp On ccp.isdel = 0 And ccp.CTable = 80 And vi.Id = ccp.CId
                                                         Left Join Sys_SetData sd1 On ccp.PaymentCurrency = sd1.Id
                                                         Left Join Sys_SetData sd2 On ccp.PayDId = sd2.Id
                                                         Left Join Sys_SetData sd3 On ccp.CTDId = sd3.Id
                                                         Left Join Sys_Users u On ccp.CreateUserId = u.Id
                                                         Where vi.IsDel = 0 {1} And vi.DIId = {0} Order By CreateTime", _dto.DiId, _dto.isAudit ? "And ccp.IsAuditGM = 1" : " ");

                groupVisaFeeViews = await _sqlSugar.SqlQueryable<GroupVisaFeeView>(groupVisaFeeSql).ToListAsync();

                decimal VisaCNYTotalPirce = 0.00M;

                foreach (var item in groupVisaFeeViews)
                {
                    string itemClientName = "";
                    string visaClients = item.VisaClient;
                    if (!string.IsNullOrEmpty(visaClients))
                    {
                        string[] clientIds = new string[] { };
                        if (visaClients.Contains(','))
                        {
                            clientIds = visaClients.Split(',');
                        }
                        else
                        {
                            clientIds = new string[] { visaClients };
                        }

                        if (clientIds.Length > 0)
                        {
                            List<int> clientIds1 = new List<int>() { };
                            foreach (var clientIdStr in clientIds)
                            {
                                if (clientIdStr.IsNumeric())
                                {
                                    clientIds1.Add(int.Parse(clientIdStr));
                                }
                            }
                            if (clientIds1.Count > 0)
                            {
                                var clients = _clientDatas.Where(it => clientIds1.Contains(it.Id)).ToList();
                                foreach (var client in clients)
                                {
                                    itemClientName += $"{client.LastName + client.FirstName},";
                                }
                            }
                            else
                            {
                                itemClientName = visaClients;
                            }
                        }
                    }
                    if (itemClientName.Length > 0)
                    {
                        itemClientName = itemClientName.Substring(0, itemClientName.Length - 1);
                    }
                    item.VisaClient = itemClientName;
                    VisaCNYTotalPirce += item.CNYPrice;
                    if (!string.IsNullOrEmpty(item.AuditGMDate))
                    {
                        item.AuditGMDate = Convert.ToDateTime(item.AuditGMDate).ToString("yyyy-MM-dd HH:mm:ss");
                    }
                }

                _geView.GroupVisaFeeViews = groupVisaFeeViews;
                _geView.GroupVisaFeeStr = string.Format(@"人民币总费用：{0} CNY", VisaCNYTotalPirce.ConvertToDecimal1().ToString("#.00"));
                #endregion

                #region 邀请/公务活动  CTable = 81

                List<GroupInvitationalFeeView> groupInvitationalFeeViews = new List<GroupInvitationalFeeView>();
                string groupInvitationalFeeSql = string.Format(@"Select ioa.Id As IOAId,ioa.DiId As IOADiId,ioa.InviterArea,ioa.Inviter,ioa.InviteTime,
                                                                 ioa.InviteCost,sd3.Name As InviteCurrency,ioa.SendCost,sd4.Name As SendCurrency,ioa.EventsCost,
                                                                 sd5.Name As EventsCurrency,ioa.TranslateCost,sd6.Name As TranslateCurrency,ccp.PayMoney,
                                                                 sd7.Name As PaymentCurrency,ccp.RMBPrice As CNYPrice,
                                                                 (((ccp.PayMoney * ccp.DayRate) / ccp.PayPercentage) * 100) As CNYPrice2,ccp.Payee,ccp.AuditGMDate,
                                                                 ccp.OrbitalPrivateTransfer,sd2.Name As PayWay,ccp.IsPay,u.CnName As Applicant,ioa.CreateTime
                                                                 From  Grp_InvitationOfficialActivities ioa
                                                                 Left Join Grp_CreditCardPayment ccp On ccp.isdel = 0 And ccp.CTable = 81 And ioa.Id = ccp.CId
                                                                 Left Join Sys_SetData sd1 On ccp.PaymentCurrency = sd1.Id
                                                                 Left Join Sys_SetData sd2 On ccp.PayDId = sd2.Id
                                                                 Left Join Sys_SetData sd3 On ioa.InviteCurrency = sd3.Id
                                                                 Left Join Sys_SetData sd4 On ioa.SendCurrency = sd4.Id
                                                                 Left Join Sys_SetData sd5 On ioa.EventsCurrency = sd5.Id
                                                                 Left Join Sys_SetData sd6 On ioa.TranslateCurrency = sd6.Id
                                                                 Left Join Sys_SetData sd7 On ccp.PaymentCurrency = sd7.Id
                                                                 Left Join Sys_Users u On ccp.CreateUserId = u.Id
                                                                 Where ioa.IsDel = 0 {1} And  ioa.Diid = {0} Order By CreateTime", _dto.DiId, _dto.isAudit ? "And ccp.IsAuditGM = 1" : " ");

                groupInvitationalFeeViews = await _sqlSugar.SqlQueryable<GroupInvitationalFeeView>(groupInvitationalFeeSql).ToListAsync();

                #region 邀请/公务活动 - 模拟数据
                //if (groupInvitationalFeeViews.Count < 1)
                //{
                //    groupInvitationalFeeViews.Add(new GroupInvitationalFeeView()
                //    {
                //        IOAId = 0,
                //        IOADiId = 2334,
                //        InviterArea = "模拟数据-邀请方地区",
                //        Inviter = "模拟数据-邀请方",
                //        InviteTime = "2023-10-10",
                //        InviteCost = 100.00M,
                //        InviteCurrency = "EUR",
                //        SendCost = 100.00M,
                //        SendCurrency = "EUR",
                //        EventsCost = 10000.00M,
                //        EventsCurrency = "EUR",
                //        TranslateCost = 300.00M,
                //        TranslateCurrency = "EUR",
                //        PayMoney = 10500.00M,
                //        PaymentCurrency = "EUR",
                //        CNYPrice = 76765.50M,
                //        Payee = "模拟数据-收款方",
                //        AuditGMDate = "2023-12-05",
                //        OrbitalPrivateTransfer = 1,
                //        PayWay = "刷卡",
                //        IsPay = 1,
                //        Applicant = "刘华举"
                //    });
                //    groupInvitationalFeeViews.Add(new GroupInvitationalFeeView()
                //    {
                //        IOAId = 0,
                //        IOADiId = 2334,
                //        InviterArea = "模拟数据-邀请方地区",
                //        Inviter = "模拟数据-邀请方",
                //        InviteTime = "2023-10-10",
                //        InviteCost = 100.00M,
                //        InviteCurrency = "EUR",
                //        SendCost = 100.00M,
                //        SendCurrency = "EUR",
                //        EventsCost = 10000.00M,
                //        EventsCurrency = "EUR",
                //        TranslateCost = 300.00M,
                //        TranslateCurrency = "EUR",
                //        PayMoney = 10500.00M,
                //        PaymentCurrency = "EUR",
                //        CNYPrice = 76765.50M,
                //        Payee = "模拟数据-收款方",
                //        AuditGMDate = "2023-12-05",
                //        OrbitalPrivateTransfer = 1,
                //        PayWay = "刷卡",
                //        IsPay = 1,
                //        Applicant = "刘华举"
                //    });
                //}
                #endregion

                decimal InvitationalCNYTotalPrice = 0.00M;

                foreach (var item in groupInvitationalFeeViews)
                {
                    InvitationalCNYTotalPrice += item.CNYPrice2;

                    if (!string.IsNullOrEmpty(item.AuditGMDate))
                    {
                        item.AuditGMDate = Convert.ToDateTime(item.AuditGMDate).ToString("yyyy-MM-dd HH:mm:ss");
                    }

                    string currencyRateStr = "";
                    List<string> currencys = new List<string>();
                    if (!string.IsNullOrEmpty(item.InviteCurrency)) currencys.Add(item.InviteCurrency);
                    if (!string.IsNullOrEmpty(item.SendCurrency)) currencys.Add(item.SendCurrency);
                    if (!string.IsNullOrEmpty(item.EventsCurrency)) currencys.Add(item.EventsCurrency);
                    if (!string.IsNullOrEmpty(item.TranslateCurrency)) currencys.Add(item.TranslateCurrency);
                    if (!string.IsNullOrEmpty(item.PaymentCurrency)) currencys.Add(item.PaymentCurrency);

                    currencyRateStr = await GeneralMethod.PostGroupRateByCTableAndCurrency(teamRateData, 81, currencys);

                    item.CurrencyRateStr = currencyRateStr;
                }

                _geView.GroupInvitationalFeeViews = groupInvitationalFeeViews;
                _geView.GroupInvitationalFeeStr = string.Format(@"人民币总费用：{0} CNY", InvitationalCNYTotalPrice.ToString("#.00"));
                #endregion

                #region 保险费用
                List<GroupInsuranceFeeView> groupInsuranceFeeViews = new List<GroupInsuranceFeeView>();
                string groupInsuranceFeeSql = string.Format(@"Select ic.Id As InsuranceId,ic.Diid As InsuranceDiId,ClientName,ccp.PayMoney,ccp.PayMoney * ccp.DayRate As CNYPrice,
                                                              sd1.Name As PayMoneyCurrency,ccp.Payee,ccp.AuditGMDate,ccp.OrbitalPrivateTransfer,
                                                              sd2.Name As PayWay,ccp.IsPay,u.CnName As Applicant,ic.CreateTime
                                                              From Grp_Customers ic
                                                              Left Join Grp_CreditCardPayment ccp On ccp.isdel = 0 And ccp.CTable = 82 And ic.Id = ccp.CId
                                                              Left Join Sys_SetData sd1 On ccp.PaymentCurrency = sd1.Id
                                                              Left Join Sys_SetData sd2 On ccp.PayDId = sd2.Id
                                                              Left Join Sys_Users u On ccp.CreateUserId = u.Id
                                                              Where ic.IsDel = 0 {1} And ic.DiId = {0} Order By CreateTime", _dto.DiId, _dto.isAudit ? "And ccp.IsAuditGM = 1" : " ");
                groupInsuranceFeeViews = await _sqlSugar.SqlQueryable<GroupInsuranceFeeView>(groupInsuranceFeeSql).ToListAsync();

                decimal InsuranceCNYTotalPrice = 0.00M;
                foreach (var item in groupInsuranceFeeViews)
                {
                    InsuranceCNYTotalPrice += item.CNYPrice;

                    string itemClientName = "";
                    string insClients = item.ClientName;
                    if (!string.IsNullOrEmpty(insClients))
                    {
                        //System.Text.RegularExpressions.Match m_EnName = Regex.Match(item.ClientName, @"[A-Za-z]+");
                        //System.Text.RegularExpressions.Match m_ZHName = Regex.Match(item.ClientName, @"[\u4e00-\u9fa5]");

                        //if (m_EnName.Success || m_ZHName.Success)
                        //{
                        //    itemClientName = insClients;
                        //    continue;
                        //}

                        string[] clientIds = new string[] { };
                        if (insClients.Contains(','))
                        {
                            clientIds = insClients.Split(',');
                        }
                        else
                        {
                            clientIds = new string[] { insClients };
                        }

                        if (clientIds.Length > 0)
                        {
                            List<int> output = new List<int>();
                            foreach (var clientId in clientIds)
                            {
                                if (clientId.IsNumeric())
                                {
                                    output.Add(int.Parse(clientId));
                                }

                            }

                            if (output.Count > 0)
                            {
                                var clients = _clientDatas.Where(it => output.Contains(it.Id)).ToList();
                                foreach (var client in clients)
                                {
                                    itemClientName += $"{client.LastName + client.FirstName},";
                                }
                                if (itemClientName.Length > 0)
                                {
                                    itemClientName = itemClientName.Substring(0, itemClientName.Length - 1);
                                }
                            }
                            else
                            {
                                itemClientName = insClients;
                            }
                        }
                    }

                    item.ClientName = itemClientName;

                    if (!string.IsNullOrEmpty(item.AuditGMDate))
                    {
                        item.AuditGMDate = Convert.ToDateTime(item.AuditGMDate).ToString("yyyy-MM-dd HH:mm:ss");
                    }
                }
                _geView.GroupInsuranceFeeViews = groupInsuranceFeeViews;
                _geView.GroupInsuranceFeeStr = string.Format(@"人民币总费用：{0} CNY", InsuranceCNYTotalPrice.ToString("#0.00"));
                #endregion

                #region 其他款项费用  98
                List<GroupDecreaseFeeView> groupDecreaseFeeViews = new List<GroupDecreaseFeeView>();
                string groupDecreaseFeeSql = string.Format(@"Select dp.Id As DPId,dp.DiId As DPDiId,dp.PriceName,ccp.PayMoney,sd1.Name As PayMoneyCurrency,
                                                              (((ccp.PayMoney * ccp.DayRate) / ccp.PayPercentage) * 100) As CNYPrice,   
                                                             ccp.DayRate,ccp.Payee,ccp.AuditGMDate,ccp.OrbitalPrivateTransfer,
                                                             sd2.Name As PayWay,ccp.IsPay,u.CnName As Applicant,dp.CreateTime
                                                             From Grp_DecreasePayments dp
                                                             Left Join Grp_CreditCardPayment ccp On ccp.isdel = 0 And ccp.CTable = 98 And dp.Id = ccp.CId
                                                             Left Join Sys_SetData sd1 On ccp.PaymentCurrency = sd1.Id
                                                             Left Join Sys_SetData sd2 On ccp.PayDId = sd2.Id
                                                             Left Join Sys_Users u On ccp.CreateUserId = u.Id
                                                             Where dp.IsDel = 0 And ccp.Ctable = 98 {1} And dp.Diid = {0} 
                                                             Order By CreateTime", _dto.DiId, _dto.isAudit ? "And ccp.IsAuditGM = 1" : " ");
                groupDecreaseFeeViews = await _sqlSugar.SqlQueryable<GroupDecreaseFeeView>(groupDecreaseFeeSql).ToListAsync();

                #region 保险费用 - 模拟数据

                //if (groupDecreaseFeeViews.Count < 1)
                //{
                //    groupDecreaseFeeViews.Add(new GroupDecreaseFeeView()
                //    {
                //        DPId = 0,
                //        DPDiId = 2334,
                //        PriceName = "模拟数据-费用名称",
                //        PayMoney = 1000.00M,
                //        PayMoneyCurrency = "CNY",
                //        DayRate = 1.0000M,
                //        CNYPrice = 1.0000M,
                //        AuditGMDate = "2023-12-10 12:13:00",
                //        Payee = "模拟数据-付款方",
                //        OrbitalPrivateTransfer = 1,
                //        PayWay = "现金",
                //        IsPay = 1,
                //        Applicant = "刘华举"
                //    });
                //    groupDecreaseFeeViews.Add(new GroupDecreaseFeeView()
                //    {
                //        DPId = 0,
                //        DPDiId = 2334,
                //        PriceName = "模拟数据-费用名称",
                //        PayMoney = 1000.00M,
                //        PayMoneyCurrency = "CNY",
                //        DayRate = 1.0000M,
                //        CNYPrice = 1.0000M,
                //        AuditGMDate = "2023-12-10 12:13:00",
                //        Payee = "模拟数据-付款方",
                //        OrbitalPrivateTransfer = 1,
                //        PayWay = "现金",
                //        IsPay = 1,
                //        Applicant = "刘华举"
                //    });
                //}

                #endregion

                decimal DecreaseCNYTotalPrice = 0.00M;
                foreach (var item in groupDecreaseFeeViews)
                {
                    item.CNYPrice = Convert.ToDecimal(item.CNYPrice.ToString("#0.00"));
                    DecreaseCNYTotalPrice += item.CNYPrice;


                    if (!string.IsNullOrEmpty(item.AuditGMDate))
                    {
                        item.AuditGMDate = Convert.ToDateTime(item.AuditGMDate).ToString("yyyy-MM-dd HH:mm:ss");
                    }
                }

                _geView.GroupDecreaseFeeViews = groupDecreaseFeeViews;
                _geView.GroupDecreaseFeeStr = string.Format(@"人民币总费用：{0} CNY", DecreaseCNYTotalPrice.ToString("#0.00"));
                #endregion

                _view.GroupExpenditure = _geView;
                #endregion

                /* 
                * 团组报表计算方式
                * 当前总支出 = 团组支出.Sum() + 超支费用.Sum()
                * 应收金额 = 应收表.Sum()
                * 已收金额 = 已收表.Sum()
                * 应收利润(应收-支出) = 应收金额 - 收款退还 - 当前总支出
                * 已收利润(已收-支出) = 已收金额 - 收款退还 - 当前总支出
                * 
                */
                decimal _totalExpenditure = 0.00M; //总支出
                decimal _amountReceivable = 0.00M; //应收金额
                decimal _amountReceived = 0.00M;   //已收金额
                decimal _receivableProfit = 0.00M; //应收利润
                decimal _receivedProfit = 0.00M;   //已收利润
                _totalExpenditure = HotelCNYTotalPrice + CTGGRCNYTotalPrice + AirCNYTotalPrice + VisaCNYTotalPirce + InvitationalCNYTotalPrice +
                                    InsuranceCNYTotalPrice + DecreaseCNYTotalPrice + exTotalAmount;
                _amountReceivable = frTotalAmount;
                _amountReceived = prTotalAmount;
                _receivableProfit = _amountReceivable - promTotalAmount - _totalExpenditure;
                _receivedProfit = _amountReceived - promTotalAmount - _totalExpenditure;
                _view.FeeTotalStr = string.Format(@$"<span>
                                                    <span>当前总支出：{_totalExpenditure.ToString("#0.00")} CNY</span> 
                                                    <span style='padding-left:10px;color: Green;'>应收金额：{_amountReceivable.ToString("#0.00")} CNY</span> 
                                                    <span style='padding-left:10px;color: Green;'>已收金额：{_amountReceived.ToString("#0.00")} CNY</span>
                                                    <span style='padding-left:10px;color: Green;'>应收利润(应收-支出)：{_receivableProfit.ToString("#0.00")} CNY</span>
                                                    <span style='padding-left:10px;color: Green;'>已收利润(已收-支出)：{_receivedProfit.ToString("#0.00")} CNY</span>
                                                    </span>");
                return Ok(JsonView(true, "查询成功!", _view));
            }
            else
            {
                return Ok(JsonView(false, "查询成功"));
            }
        }

        /// <summary>
        ///  团组报表
        ///  Excel 下载 
        /// </summary>
        /// <param name="_dto">团组列表请求dto</param>
        /// <returns></returns>
        [HttpPost("PostGroupStatementExportExcel")]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> PostGroupStatementExportExcel(GroupStatementDetailsDto _dto)
        {
            //pageId 38
            int currUserId = _dto.UserId, pageId = _dto.PageId, diId = _dto.DiId;
            bool isAudit = _dto.isAudit;

            #region  参数验证
            if (currUserId < 1) return Ok(JsonView(false, "员工Id为空"));
            if (pageId < 1) return Ok(JsonView(false, "页面Id为空"));
            if (diId < 1) return Ok(JsonView(false, "数据Id为空"));

            #region 页面操作权限验证
            var pageFunAuthView = await GeneralMethod.PostUserPageFuncDatas(currUserId, pageId);

            if (pageFunAuthView.FilesDownloadAuth == 0) return Ok(JsonView(false, "您没有文件下载权!"));
            #endregion

            #endregion

            var groupInfo = _sqlSugar.Queryable<Grp_DelegationInfo>().Where(x => x.IsDel == 0 && x.Id == diId).First();
            if (groupInfo == null) return Ok(JsonView(false, "暂无该条团组信息!"));
            var userInfo = _sqlSugar.Queryable<Sys_Users>().Where(x => x.IsDel == 0 && x.Id == currUserId).First();

            string groupNo = groupInfo.TourCode,
                   groupName = groupInfo.TeamName.Replace("|", "、"),
                   lister = userInfo?.CnName ?? "-";

            var _clientDatas = _sqlSugar.Queryable<Crm_DeleClient>()
                    .Where(it => it.IsDel == 0)
                    .Select(x => new Crm_DeleClient { Id = x.Id, FirstName = x.FirstName, LastName = x.LastName, Sex = x.Sex })
                    .ToList();

            //获取模板
            string tempPath = (AppSettingsHelper.Get("ExcelBasePath") + "Template/团组费用统计模板.xls");
            var designer = new WorkbookDesigner();
            designer.Workbook = new Workbook(tempPath);
            // 获取工作簿中的工作表集合
            var worksheets = designer.Workbook.Worksheets;

            #region 获取sheet,定义datatable,局部变量
            //酒店费用相关 
            var hotelFeeSheet = worksheets["酒店费用"];
            var hotelFeeDt = new DataTable($"HotelFeeView");
            decimal hotelCNYTotalCost = 0.00M;

            //地接费用相关 
            var OPFeeSheet = worksheets["地接费用"];
            var OPFeeDt = new DataTable($"OPFeeView");
            var OPFeeLabel = string.Empty;
            decimal OPCNYTotalCost = 0.00M;

            //签证费用相关 
            var visaFeeSheet = worksheets["签证费用"];
            var visaFeeDt = new DataTable($"VisaFeeView");
            decimal visaCNYTotalCost = 0.00M;

            //邀请费用相关 
            var OAFeeSheet = worksheets["邀请费用"];
            var OAFeeDt = new DataTable($"OAFeeView");
            decimal OACNYTotalCost = 0.00M;

            //机票费用相关 
            var airTicketFeeSheet = worksheets["机票费用"];
            var airTicketFeeDt = new DataTable($"AirTicketFeeView");
            decimal airTicketCNYTotalCost = 0.00M;

            //保险费用相关 
            var insureFeeSheet = worksheets["保险费用"];
            var insureFeeDt = new DataTable($"InsureFeeView");
            decimal insureCNYTotalCost = 0.00M;

            //其他费用相关 
            var otherFeeSheet = worksheets["其他费用"];
            var otherFeeDt = new DataTable($"OtherFeeView");
            decimal otherCNYTotalCost = 0.00M;

            //收款退还相关 
            var SKTHFeeSheet = worksheets["收款退还"];
            var SKTHFeeDt = new DataTable($"SKTHFeeView");
            decimal SKTHCNYTotalCost = 0.00M;

            var totalAmount = 0.00M;     //支出成本合计
            var accountsAmount = 0.00M;  //应收金额合计
            var receivedAmount = 0.00M;  //已收金额合计
            var accountsProfit = 0.00M;  //应收利润合计
            var receiveProfit = 0.00M;   //已收利润合计
            var profitMargin = 0.00M;    //利润差合计

            //应收合计相关
            var receivableFeeSheet = worksheets["团组收入(未收)"];
            var receivableFeeDt = new DataTable($"ReceivableFeeView");

            //已收合计相关
            var receivedFeeSheet = worksheets["团组收入(已收)"];
            var receivedFeeDt = new DataTable($"ReceivedFeeView");

            //团组收入支出利润
            var groupIEProfitDt = new DataTable($"IEProfitFeeView");

            #endregion

            #region 酒店费用

            if (hotelFeeSheet != null)
            {
                string hotelFeeSql = string.Format(@"
SELECT
  hr.Id AS HrId,
  hr.DiId AS HrDiId,
  hr.City,
  hr.HotelName,
  hr.CheckInDate,
  hr.CheckOutDate,
  hr.CardPrice AS RoomPrice,
  sd1.Name AS PaymentCurrency,
  hr.SingleRoomPrice,
  hr.SingleRoomCount,
  hr.DoubleRoomPrice,
  hr.DoubleRoomCount,
  hr.SuiteRoomPrice,
  hr.SuiteRoomCount,
  hr.OtherRoomPrice,
  hr.OtherRoomCount,
  hr.BreakfastPrice,
  sd4.Name AS BreakfastCurrency,
  hr.Isoppay,
  hr.GovernmentRent,
  sd5.Name AS GovernmentRentCurrency,
  hr.CityTax,
  sd6.Name AS CityTaxCurrency,
  ccp.PayMoney,
  (
    ((ccp.PayMoney * ccp.DayRate) / ccp.PayPercentage) * 100
  ) AS CNYPrice,
  ccp.PayPercentage,
  ccp.DayRate,
  ccp.Payee,
  ccp.OrbitalPrivateTransfer,
  sd2.Name AS PayWay,
  sd3.Name AS CardType,
  ccp.IsPay,
  u.CnName AS Applicant,
  hr.Remark
FROM
  Grp_HotelReservations hr
  INNER JOIN Grp_CreditCardPayment ccp ON hr.Id = ccp.CId
  LEFT JOIN Sys_SetData sd1 ON ccp.PaymentCurrency = sd1.Id
  LEFT JOIN Sys_SetData sd2 ON ccp.PayDId = sd2.Id
  LEFT JOIN Sys_SetData sd3 ON ccp.CTDId = sd3.Id
  LEFT JOIN Sys_Users u ON ccp.CreateUserId = u.Id
  LEFT JOIN Sys_SetData sd4 ON hr.BreakfastCurrency = sd4.Id
  LEFT JOIN Sys_SetData sd5 ON hr.GovernmentRentCurrency = sd5.Id
  LEFT JOIN Sys_SetData sd6 ON hr.CityTaxCurrency = sd6.Id
WHERE
  hr.IsDel = 0
  AND ccp.IsDel = 0
  AND ccp.CTable = 76 {1}
  AND ccp.PayMoney <> 0
  AND hr.DiId = {0}
ORDER BY
  CheckInDate Asc", _dto.DiId, _dto.isAudit ? "AND (ccp.IsAuditGM = 1 Or ccp.IsAuditGM = 3)" : " ");

                var groupHotelFeeViews = await _sqlSugar.SqlQueryable<GroupHotelFeeView>(hotelFeeSql).ToListAsync();

                if (groupHotelFeeViews.Any())
                {
                    var _setDatas = await _sqlSugar.Queryable<Sys_SetData>().Where(it => it.IsDel == 0).ToListAsync();

                    var hotelSubIds = groupHotelFeeViews.Select(it => it.HrId).ToList();

                    var groupHotelContentFeeViews = await _sqlSugar.Queryable<Grp_HotelReservationsContent>().Where(it => hotelSubIds.Contains(it.HrId)).ToListAsync();

                    var teamRateData = await _teamRateRep.PostGroupRateInfoByDiId(_dto.DiId);
                    foreach (var item in groupHotelFeeViews)
                    {
                        if (groupHotelContentFeeViews.Count > 0)
                        {

                            string paymentStr = string.Empty;

                            var roomData = groupHotelContentFeeViews.Find(it => it.HrId == item.HrId && it.PriceType == 1); //房费 

                            item.RoomPrice = roomData?.Price ?? 0.00M;
                            if (item.RoomPrice != 0)
                            {
                                if (roomData.IsPay == 0) paymentStr += $"房费：未付款<br/>";

                                item.RoomPriceCurrency = _setDatas.Find(it => it.Id == roomData?.Currency)?.Name;
                                string feeMark1 = roomData?.OrbitalPrivateTransfer == 0 ? "公转" : "私转";
                                string isFeeMark1 = roomData?.IsOppay == 1 ? "是" : "否";
                                item.RoomInfoTips = @$"当时汇率：{roomData?.Rate.ToString("#0.0000")} <br/>
                                           收款方：{roomData?.Payee}<br/>
                                           费用标识：{feeMark1} <br/>
                                           支付方式：{_setDatas.Find(it => it.Id == roomData?.PayDId)?.Name} <br/>
                                           卡类型：{_setDatas.Find(it => it.Id == roomData?.CTDId)?.Name}<br/>
                                           是否由地接支付：{isFeeMark1} <br/>";
                            }

                            var breakfastData = groupHotelContentFeeViews.Find(it => it.HrId == item.HrId && it.PriceType == 2); //早餐 

                            item.BreakfastPrice = breakfastData?.Price ?? 0.00M;

                            if (item.BreakfastPrice != 0)
                            {
                                if (breakfastData.IsPay == 0) paymentStr += $"早餐：未付款<br/>";

                                item.BreakfastCurrency = _setDatas.Find(it => it.Id == breakfastData?.Currency)?.Name;
                                string feeMark2 = breakfastData?.OrbitalPrivateTransfer == 0 ? "公转" : "私转";
                                string isFeeMark2 = breakfastData?.IsOppay == 1 ? "是" : "否";
                                item.BreakfastInfoTips = @$"当时汇率：{breakfastData?.Rate.ToString("#0.0000")} <br/>
                                                收款方：{breakfastData?.Payee}<br/>
                                                费用标识：{feeMark2} <br/>
                                                支付方式：{_setDatas.Find(it => it.Id == roomData?.PayDId)?.Name} <br/>
                                                卡类型：{_setDatas.Find(it => it.Id == roomData?.CTDId)?.Name} <br/>
                                                是否由地接支付：{isFeeMark2} <br/>";
                            }

                            var landTaxData = groupHotelContentFeeViews.Find(it => it.HrId == item.HrId && it.PriceType == 3); //地税

                            item.GovernmentRent = landTaxData?.Price ?? 0.00M;

                            if (item.GovernmentRent != 0)
                            {
                                if (landTaxData.IsPay == 0) paymentStr += $"地税：未付款<br/>";

                                item.GovernmentRentCurrency = _setDatas.Find(it => it.Id == landTaxData?.Currency)?.Name;
                                string feeMark3 = landTaxData?.OrbitalPrivateTransfer == 0 ? "公转" : "私转";
                                string isFeeMark3 = landTaxData?.IsOppay == 1 ? "是" : "否";
                                item.GovernmentRentTips = @$"当时汇率：{landTaxData?.Rate.ToString("#0.0000")} <br/>
                                                 收款方：{landTaxData?.Payee}<br/>
                                                 费用标识：{feeMark3} <br/>
                                                 支付方式：{_setDatas.Find(it => it.Id == landTaxData?.PayDId)?.Name} <br/>
                                                 卡类型：{_setDatas.Find(it => it.Id == landTaxData?.CTDId)?.Name} <br/>
                                                 是否由地接支付：{isFeeMark3} <br/>";
                            }

                            var cityTaxData = groupHotelContentFeeViews.Find(it => it.HrId == item.HrId && it.PriceType == 4); //城市税 

                            item.CityTax = cityTaxData?.Price ?? 0.00M;

                            if (item.CityTax != 0)
                            {
                                if (cityTaxData.IsPay == 0) paymentStr += $"城市税：未付款<br/>";
                                item.CityTaxCurrency = _setDatas.Find(it => it.Id == cityTaxData?.Currency)?.Name;
                                string feeMark4 = cityTaxData?.OrbitalPrivateTransfer == 0 ? "公转" : "私转";
                                string isFeeMark4 = landTaxData?.IsOppay == 1 ? "是" : "否";
                                item.CityTaxTips = @$"当时汇率：{cityTaxData?.Rate.ToString("#0.0000")} <br/>
                                          收款方：{cityTaxData?.Payee}<br/>
                                          费用标识：{feeMark4} <br/>
                                          支付方式：{_setDatas.Find(it => it.Id == cityTaxData?.PayDId)?.Name} <br/>
                                          卡类型：{_setDatas.Find(it => it.Id == cityTaxData?.CTDId)?.Name} <br/>
                                          是否由地接支付：{isFeeMark4} <br/>";
                            }

                            if (!string.IsNullOrEmpty(paymentStr))
                            {
                                item.IsPay = 2;
                                item.PayTips = paymentStr;
                            }
                            //item.CNYPrice = roomData?.Price ?? 0.00M * roomData?.Rate ?? 0.00M +
                            //                breakfastData?.Price ?? 0.00M * breakfastData?.Rate ?? 0.00M +
                            //                landTaxData?.Price ?? 0.00M * landTaxData?.Rate ?? 0.00M +
                            //                cityTaxData?.Price ?? 0.00M * cityTaxData?.Rate ?? 0.00M;
                        }
                        else
                        {
                            decimal roomPrice = (item.SingleRoomCount * item.SingleRoomPrice) +
                                                (item.DoubleRoomCount * item.DoubleRoomPrice) +
                                                (item.SuiteRoomCount * item.SuiteRoomPrice) +
                                                (item.OtherRoomCount * item.OtherRoomPrice);
                            //item.RoomPrice = item.CardPrice;
                            item.RoomPriceCurrency = item.PaymentCurrency;
                        }

                        item.PayMoney = item.PayMoney.ConvertToDecimal1();
                        item.CNYPrice = item.CNYPrice.ConvertToDecimal1();
                    }

                }

                hotelCNYTotalCost = groupHotelFeeViews.Sum(x => x.CNYPrice);
                hotelFeeDt = CommonFun.ToDataTableArray(groupHotelFeeViews);
                hotelFeeDt.TableName = $"HotelFeeView";
            }

            #endregion

            #region 地接费用
            if (OPFeeSheet != null)
            {
                string CTGGRFeeSql = string.Empty;
                if (groupInfo.VisitDate > Convert.ToDateTime("2024-04-17"))
                {
                    CTGGRFeeSql = string.Format(@"Select ctggr.Id As CTGGRId,ctggr.DiId As CTGGRDiId,ctggr.PriceName As Area,ctggrc.*,ctggrc.Price As PayMoney,
                                                     sd2.name As PaymentCurrency,ccp.PayPercentage,
                                                     (ctggrc.Price * (ccp.PayPercentage / 100)) As AmountPaid,
                                                     (ctggrc.Price - ctggrc.Price * (ccp.PayPercentage / 100)) As BalancePayment,
                                                     ccp.DayRate,(ctggrc.Price * (ccp.PayPercentage / 100) * ccp.DayRate) As CNYPrice,ccp.Payee,ccp.AuditGMDate,
                                                     ccp.OrbitalPrivateTransfer,sd1.Name As PayWay,ccp.IsPay,u.CnName As Applicant,ctggr.CreateTime
                                                     From Grp_CarTouristGuideGroundReservations ctggr
                                                     Left Join ( Select cggrc.CTGGRId,sd1.Name As PriceName,cggrc.Price*cggrc.Count As Price,sd2.Name As PriceCurrency,
                                                                        cggrc.DatePrice,cggrc.PriceContent
                                                             From Grp_CarTouristGuideGroundReservationsContent cggrc
                                                             Left Join Sys_SetData sd1 On cggrc.SId = sd1.Id
                                                             Left Join Sys_SetData sd2 On cggrc.Currency = sd2.Id
                                                             Where cggrc.ISdel = 0 And cggrc.Price != 0.00 
                                                             ) ctggrc On ctggr.Id = ctggrc.CTGGRId 
                                                     Left Join Grp_CreditCardPayment ccp On ccp.IsDel = 0  And ccp.CTable = 79 And ctggr.Id = ccp.CId
                                                     Left Join Sys_SetData sd1 On ccp.PayDId = sd1.Id
                                                     Left Join Sys_SetData sd2 On ccp.PaymentCurrency = sd2.Id
                                                     Left Join Sys_Users u On ccp.CreateUserId = u.Id
                                                     Where ctggr.IsDel = 0 {1} And ctggr.DiId = {0}
                                                     Order By CreateTime", _dto.DiId, _dto.isAudit ? "And (ccp.IsAuditGM = 1 OR ccp.IsAuditGM = 3)" : " ");
                }
                else
                {
                    CTGGRFeeSql = string.Format(@"Select ctggr.Id As CTGGRId,ctggr.DiId As CTGGRDiId,ctggr.PriceName As Area,
													 ccp.PayMoney,sd2.name As PaymentCurrency,ccp.PayPercentage,
													 (ccp.PayMoney * (ccp.PayPercentage / 100)) As AmountPaid,
													 (ccp.PayMoney -ccp.PayMoney * (ccp.PayPercentage / 100)) As BalancePayment,
                                                     ccp.DayRate,(ccp.PayMoney * (ccp.PayPercentage / 100) * ccp.DayRate) As CNYPrice,
													 ccp.Payee,ccp.AuditGMDate,
                                                     ccp.OrbitalPrivateTransfer,sd1.Name As PayWay,ccp.IsPay,u.CnName As Applicant,ctggr.CreateTime
                                                     From Grp_CarTouristGuideGroundReservations ctggr
                                                     Left Join Grp_CreditCardPayment ccp On ccp.IsDel = 0  And ccp.CTable = 79 And ctggr.Id = ccp.CId
                                                     Left Join Sys_SetData sd1 On ccp.PayDId = sd1.Id
                                                     Left Join Sys_SetData sd2 On ccp.PaymentCurrency = sd2.Id
                                                     Left Join Sys_Users u On ccp.CreateUserId = u.Id
                                                     Where ctggr.IsDel = 0 {1} And ctggr.DiId = {0}
                                                     Order By CreateTime", _dto.DiId, _dto.isAudit ? "And (ccp.IsAuditGM = 1 OR ccp.IsAuditGM = 3)" : " ");
                }

                var groupOPFeeViews = await _sqlSugar.SqlQueryable<GroupCTGGRFeeView>(CTGGRFeeSql).ToListAsync();
                string CTGGRFeeStr = "";
                var OPToDataTableViews = groupOPFeeViews;

                decimal CTGGRCNYTotalPrice = 0.00M;

                //按1 地区，2 币种，3 汇率 分组计算
                var groupCTGGRFeeDatas = groupOPFeeViews.GroupBy(it => it.Area);

                foreach (var ctggfr in groupCTGGRFeeDatas)
                {
                    var ctggfr_curr = ctggfr.GroupBy(it => it.PaymentCurrency);

                    if (ctggfr_curr.Count() > 0)
                    {
                        foreach (var curr in ctggfr_curr)
                        {

                            var ctggfr_rate = curr.GroupBy(it => it.DayRate);

                            if (ctggfr_rate.Count() > 0)
                            {
                                foreach (var rate in ctggfr_rate)
                                {
                                    CTGGRFeeStr += string.Format($"{ctggfr.Key} 总费用：{rate.Sum(it => it.AmountPaid).ToString("#0.00")} {rate.FirstOrDefault()?.PaymentCurrency}(人民币：{rate.Sum(it => Convert.ToDecimal(it.CNYPrice.ToString("#0.00"))).ToString("#0.00")} CNY 当时支付汇率：{rate.FirstOrDefault()?.DayRate.ToString("#0.0000")})\n");
                                    CTGGRCNYTotalPrice += rate.Sum(it => Convert.ToDecimal(it.CNYPrice.ToString("#0.00")));
                                }
                            }
                            else
                            {
                                CTGGRFeeStr += string.Format($"{ctggfr.Key} 总费用：{curr.Sum(it => it.AmountPaid).ToString("#0.00")} {curr.FirstOrDefault()?.PaymentCurrency}(人民币：{curr.Sum(it => Convert.ToDecimal(it.CNYPrice.ToString("#0.00"))).ToString("#0.00")} CNY 当时支付汇率：{curr.FirstOrDefault()?.DayRate.ToString("#0.0000")})\n");
                                CTGGRCNYTotalPrice += curr.Sum(it => Convert.ToDecimal(it.CNYPrice.ToString("#0.00")));
                            }
                        }
                    }
                    else
                    {
                        CTGGRFeeStr += string.Format($"{ctggfr.Key} 总费用：{ctggfr.Sum(it => it.AmountPaid).ToString("#0.00")} {ctggfr.FirstOrDefault()?.PaymentCurrency}(人民币：{ctggfr.Sum(it => Convert.ToDecimal(it.CNYPrice.ToString("#0.00"))).ToString("#0.00")} CNY 当时支付汇率：{ctggfr.FirstOrDefault()?.DayRate.ToString("#0.0000")})\n");
                        CTGGRCNYTotalPrice += ctggfr.Sum(it => Convert.ToDecimal(it.CNYPrice.ToString("#0.00")));
                    }
                }

                foreach (var item in groupOPFeeViews)
                {
                    if (!string.IsNullOrEmpty(item.AuditGMDate))
                    {
                        item.AuditGMDate = Convert.ToDateTime(item.AuditGMDate).ToString("yyyy-MM-dd HH:mm:ss");
                    }

                    if (item.DatePrice != null)
                    {
                        item.PriceName = $"{item.PriceName}({Convert.ToDateTime(item.DatePrice).ToString("yyyy-MM-dd")})";
                    }
                }

                OPCNYTotalCost = CTGGRCNYTotalPrice;
                OPFeeDt = CommonFun.ToDataTableArray(OPToDataTableViews);
                OPFeeLabel = CTGGRFeeStr;
                OPFeeDt.TableName = $"OPFeeView";
            }

            #endregion

            #region 签证费用
            if (visaFeeSheet != null)
            {
                string groupVisaFeeSql = string.Format(@"Select vi.Id As VisaId,vi.DIId As VisaDiId,vi.VisaClient,ccp.PayMoney,sd1.Name As PayMoneyCurrency,
                                                         ccp.DayRate,ccp.Payee,ccp.AuditGMDate,sd2.Name As PaymentModes,sd3.Name As CardTypeName,u.CnName As Applicant,vi.CreateTime,
                                                         (((ccp.PayMoney * ccp.DayRate) / ccp.PayPercentage) * 100) As CNYPrice,ccp.ConsumptionDate,
														 Case When ccp.OrbitalPrivateTransfer = 0 Then '公转' Else '私转' End As CostMark,
														 Case When ccp.IsPay = 0 Then '未付款' Else '已付款' End As IsPay,vi.Remark
                                                         From Grp_VisaInfo vi
                                                         Left Join Grp_CreditCardPayment ccp On ccp.isdel = 0 And ccp.CTable = 80 And vi.Id = ccp.CId
                                                         Left Join Sys_SetData sd1 On ccp.PaymentCurrency = sd1.Id
                                                         Left Join Sys_SetData sd2 On ccp.PayDId = sd2.Id
                                                         Left Join Sys_SetData sd3 On ccp.CTDId = sd3.Id
                                                         Left Join Sys_Users u On ccp.CreateUserId = u.Id
                                                         Where vi.IsDel = 0 {1} And vi.DIId = {0} Order By CreateTime", diId, isAudit ? "And ccp.IsAuditGM = 1" : " ");

                var groupVisaFeeViews = _sqlSugar.SqlQueryable<GroupVisaFeeExcelView>(groupVisaFeeSql).ToList();

                foreach (var item in groupVisaFeeViews)
                {
                    string names = string.Empty;
                    string visaClients = item.VisaClient;
                    if (!string.IsNullOrEmpty(visaClients))
                    {
                        var clientIds = new string[] { };
                        if (visaClients.Contains(',')) clientIds = visaClients.Split(',');
                        else clientIds = new string[] { visaClients };

                        if (clientIds.Length > 0)
                        {
                            var clientIds1 = new List<int>() { };
                            foreach (var clientIdStr in clientIds)
                            {
                                var isInt = int.TryParse(clientIdStr, out int id);
                                if (isInt) clientIds1.Add(id);
                            }
                            if (clientIds1.Count > 0)
                            {
                                var clients = _clientDatas.Where(it => clientIds1.Contains(it.Id)).ToList();

                                foreach (var client in clients)
                                {
                                    EncryptionProcessor.DecryptProperties(client);
                                    names += $"{client.LastName + client.FirstName},";
                                }
                            }
                            else names = visaClients;
                        }
                        if (names.Length > 0) names = names.Substring(0, names.Length - 1);
                        item.VisaClient = names;
                    }
                }

                visaCNYTotalCost = groupVisaFeeViews.Sum(x => x.CNYPrice);
                visaFeeDt = CommonFun.ToDataTableArray(groupVisaFeeViews);
                visaFeeDt.TableName = $"VisaFeeView";

            }
            #endregion

            #region 邀请费用

            if (OAFeeSheet != null)
            {
                string feeSql = string.Format(@"Select ioa.Id As IOAId,ioa.DiId As IOADiId,ioa.InviterArea,ioa.Inviter,CONVERT(DATE, ioa.InviteTime) AS InviteTime,
                                                ioa.InviteCost,sd3.Name As InviteCurrency,ioa.SendCost,sd4.Name As SendCurrency,ioa.EventsCost,
                                                sd5.Name As EventsCurrency,ioa.TranslateCost,sd6.Name As TranslateCurrency,ccp.PayMoney,
                                                sd7.Name As PaymentCurrency,ccp.RMBPrice As CNYPrice,ccp.ConsumptionDate,
                                                Case When ccp.OrbitalPrivateTransfer = 0 Then '公转' Else '私转' End As CostMark,
                                                Case When ccp.IsPay = 0 Then '未付款' Else '已付款' End As IsPay,sd2.Name As PaymentModes,
                                                ccp.Payee,ioa.Remark,ccp.AuditGMDate,u.CnName As Applicant,ioa.CreateTime
                                                From  Grp_InvitationOfficialActivities ioa
                                                Left Join Grp_CreditCardPayment ccp On ccp.isdel = 0 And ccp.CTable = 81 And ioa.Id = ccp.CId
                                                Left Join Sys_SetData sd1 On ccp.PaymentCurrency = sd1.Id
                                                Left Join Sys_SetData sd2 On ccp.PayDId = sd2.Id
                                                Left Join Sys_SetData sd3 On ioa.InviteCurrency = sd3.Id
                                                Left Join Sys_SetData sd4 On ioa.SendCurrency = sd4.Id
                                                Left Join Sys_SetData sd5 On ioa.EventsCurrency = sd5.Id
                                                Left Join Sys_SetData sd6 On ioa.TranslateCurrency = sd6.Id
                                                Left Join Sys_SetData sd7 On ccp.PaymentCurrency = sd7.Id
                                                Left Join Sys_Users u On ccp.CreateUserId = u.Id
                                                Where ioa.IsDel = 0 {1} And  ioa.Diid = {0} Order By CreateTime", diId, isAudit ? "And ccp.IsAuditGM = 1" : " ");

                var feeViews = _sqlSugar.SqlQueryable<GroupOAFeeExcelView>(feeSql).ToList();

                OACNYTotalCost = feeViews.Sum(x => x.CNYPrice);
                OAFeeDt = CommonFun.ToDataTableArray(feeViews);
                OAFeeDt.TableName = $"OAFeeView";
            }

            #endregion

            #region 机票费用

            if (airTicketFeeSheet != null)
            {
                string feeSql = string.Format(@"Select atr.Id As AirId,atr.DIId As AirDiId,atr.FlightsCode,atr.FlightsCity,
                                                sd4.Name As AirTypeName,atr.FlightsDate,atr.FlightsTime,atr.ClientName,atr.ClientNum,
                                                atr.PriceDescription,ccp.PayMoney,ccp.DayRate,sd1.Name As PayMoneyCurrency,
                                                (((ccp.PayMoney * ccp.DayRate) / ccp.PayPercentage) * 100) As CNYPrice,ccp.ConsumptionDate,
                                                ccp.Payee,Case When ccp.OrbitalPrivateTransfer = 0 Then '公转' Else '私转' End As CostMark,
                                                Case When ccp.IsPay = 0 Then '未付款' Else '已付款' End As IsPay,sd2.Name As PaymentModes,
                                                sd3.Name As CardType,ccp.AuditGMDate,u.CnName As Applicant,atr.CreateTime,atr.Remark
                                                From Grp_AirTicketReservations atr
                                                Left Join Grp_CreditCardPayment ccp On ccp.isdel = 0 And ccp.CTable = 85 And atr.Id = ccp.CId
                                                Left Join Sys_SetData sd1 On ccp.PaymentCurrency = sd1.Id
                                                Left Join Sys_SetData sd2 On ccp.PayDId = sd2.Id
                                                Left Join Sys_SetData sd3 On ccp.CTDId = sd3.Id
                                                Left Join Sys_SetData sd4 On atr.CType = sd4.Id
                                                Left Join Sys_Users u On ccp.CreateUserId = u.Id
                                                Where atr.IsDel = 0 {1} And atr.DiId = {0} Order By CreateTime", diId, isAudit ? "And ccp.IsAuditGM = 1" : " ");

                var feeViews = _sqlSugar.SqlQueryable<GroupAirTicketExcelView>(feeSql).ToList();

                airTicketCNYTotalCost = feeViews.Sum(x => x.CNYPrice);
                airTicketFeeDt = CommonFun.ToDataTableArray(feeViews);
                airTicketFeeDt.TableName = $"AirTicketFeeView";
            }

            #endregion

            #region 保险费用

            if (insureFeeSheet != null)
            {
                string feeSql = string.Format(@"Select ic.Id As InsuranceId,ic.Diid As InsuranceDiId,ClientName,ccp.PayMoney,
                                                sd1.Name As PayMoneyCurrency,ccp.DayRate,ccp.PayMoney * ccp.DayRate As CNYPrice,ccp.ConsumptionDate,
                                                Case When ccp.OrbitalPrivateTransfer = 0 Then '公转' Else '私转' End As CostMark,
                                                Case When ccp.IsPay = 0 Then '未付款' Else '已付款' End As IsPay,sd2.Name As PaymentModes,
                                                ccp.Payee,ccp.AuditGMDate,u.CnName As Applicant,ic.CreateTime,ic.Remark
                                                From Grp_Customers ic
                                                Left Join Grp_CreditCardPayment ccp On ccp.isdel = 0 And ccp.CTable = 82 And ic.Id = ccp.CId
                                                Left Join Sys_SetData sd1 On ccp.PaymentCurrency = sd1.Id
                                                Left Join Sys_SetData sd2 On ccp.PayDId = sd2.Id
                                                Left Join Sys_Users u On ccp.CreateUserId = u.Id
                                                Where ic.IsDel = 0 {1} And ic.DiId = {0} Order By CreateTime", diId, isAudit ? "And ccp.IsAuditGM = 1" : " ");

                var feeViews = _sqlSugar.SqlQueryable<GroupInsureExcelView>(feeSql).ToList();

                foreach (var item in feeViews)
                {
                    string itemClientName = "";
                    string insClients = item.ClientName;
                    if (!string.IsNullOrEmpty(insClients))
                    {
                        string[] clientIds = new string[] { };
                        if (insClients.Contains(',')) clientIds = insClients.Split(',');
                        else clientIds = new string[] { insClients };

                        if (clientIds.Length > 0)
                        {
                            List<int> output = new List<int>();
                            foreach (var clientId in clientIds)
                                if (int.TryParse(clientId, out int id))
                                    output.Add(id);

                            if (output.Count > 0)
                            {
                                var clients = _clientDatas.Where(it => output.Contains(it.Id)).ToList();

                                foreach (var client in clients)
                                {
                                    EncryptionProcessor.DecryptProperties(client);
                                    itemClientName += $"{client.LastName + client.FirstName},";
                                }
                                if (itemClientName.Length > 0)
                                {
                                    itemClientName = itemClientName.Substring(0, itemClientName.Length - 1);
                                }
                            }
                            else itemClientName = insClients;
                        }
                    }

                    item.ClientName = itemClientName;
                }

                insureCNYTotalCost = feeViews.Sum(x => x.CNYPrice);
                insureFeeDt = CommonFun.ToDataTableArray(feeViews);
                insureFeeDt.TableName = $"InsureFeeView";
            }

            #endregion

            #region 其他费用 

            if (insureFeeSheet != null)
            {
                string feeSql = string.Format(@"Select dp.Id As DPId,dp.DiId As DPDiId,dp.PriceName,ccp.PayMoney,sd1.Name As PayMoneyCurrency,
                                                ccp.DayRate,(((ccp.PayMoney * ccp.DayRate) / ccp.PayPercentage) * 100) As CNYPrice,ccp.ConsumptionDate,
                                                Case When ccp.OrbitalPrivateTransfer = 0 Then '公转' Else '私转' End As CostMark,
                                                Case When ccp.IsPay = 0 Then '未付款' Else '已付款' End As IsPay,sd2.Name As PaymentModes,  
                                                ccp.Payee,ccp.AuditGMDate,u.CnName As Applicant,dp.CreateTime,dp.Remark
                                                From Grp_DecreasePayments dp
                                                Left Join Grp_CreditCardPayment ccp On ccp.isdel = 0 And ccp.CTable = 98 And dp.Id = ccp.CId
                                                Left Join Sys_SetData sd1 On ccp.PaymentCurrency = sd1.Id
                                                Left Join Sys_SetData sd2 On ccp.PayDId = sd2.Id
                                                Left Join Sys_Users u On ccp.CreateUserId = u.Id
                                                Where dp.IsDel = 0 And ccp.Ctable = 98 {1} And dp.Diid = {0} 
                                                Order By CreateTime", diId, isAudit ? "And ccp.IsAuditGM = 1" : " ");

                var feeViews = _sqlSugar.SqlQueryable<GroupOtherFeeExcelView>(feeSql).ToList();

                otherCNYTotalCost = feeViews.Sum(x => x.CNYPrice);
                otherFeeDt = CommonFun.ToDataTableArray(feeViews);
                otherFeeDt.TableName = $"OtherFeeView";
            }

            #endregion

            #region 收款退还

            if (SKTHFeeSheet != null)
            {
                //删除了  And prom.PriceType = 1
                string _ropSql = string.Format(@"Select u.CnName As Appliction,prom.Id As PrId,prom.DiId As PrDiId,prom.Price As PrPrice,
										         prom.PriceName AS PrPriceName,prom.CurrencyId As PrCurrencyId,
										         prom.PayType As PrPayType,prom.PriceType As PrPriceType,
												 ccp.RMBPrice * ccp.DayRate As RMBPrice,ccp.*,prom.CreateTime As PrCreateTime,prom.Remark AS SKTHRemark
										         From Fin_PaymentRefundAndOtherMoney prom
										         Left Join Grp_CreditCardPayment ccp On prom.DiId = ccp.DIId And prom.Id = ccp.CId
										         Left Join Sys_Users u On ccp.CreateUserId = u.Id
										         Where prom.IsDel = 0  
                                                 And prom.PayType = 1 
                                                 And ccp.CTable = 285
										         {1} 
										         And prom.DiId = {0} Order By PrCreateTime", _dto.DiId, _dto.isAudit ? "And ccp.IsAuditGM = 1" : " ");
                var _promDatas = await _sqlSugar.SqlQueryable<Gsd_PaymentRefundAndOtherMoneyDataSource1View>(_ropSql).ToListAsync();

                if (_promDatas.Any())
                {
                    var _promView = new List<Gsd_PaymentRefundAndOtherMoneyView>();
                    var _setDatas = await _sqlSugar.Queryable<Sys_SetData>().Where(it => it.IsDel == 0).ToListAsync();

                    foreach (var ropItem in _promDatas)
                    {
                        string thisCueencyCode = "Unknown";
                        string thisCueencyName = "Unknown";
                        var currency = _setDatas.Where(it => it.Id == ropItem.PaymentCurrency).FirstOrDefault();
                        if (currency != null)
                        {
                            thisCueencyCode = currency.Name;
                            thisCueencyName = currency.Remark;
                        }

                        string orbitalPrivateTransferStr = "Unknown";
                        var orbitalPrivateTransfer = _setDatas.Where(it => it.Id == ropItem.OrbitalPrivateTransfer).FirstOrDefault();
                        if (orbitalPrivateTransfer != null)
                        {
                            orbitalPrivateTransferStr = orbitalPrivateTransfer.Name;
                        }

                        string payStr = "Unknown";
                        var pay = _setDatas.Where(it => it.Id == ropItem.PayDId).FirstOrDefault();
                        if (pay != null)
                        {
                            payStr = pay.Name;
                        }

                        var gsd_PaymentRefund = new Gsd_PaymentRefundAndOtherMoneyView()
                        {
                            Id = ropItem.Id,
                            DiId = ropItem.DIId,
                            PriceName = ropItem.PrPriceName,
                            PayCurrencyCode = thisCueencyCode,
                            PayCurrencyName = thisCueencyName,
                            Price = ropItem.PrPrice,
                            CNYPrice = ropItem.PayMoney * ropItem.DayRate,
                            ThisRate = ropItem.DayRate,
                            Payee = ropItem.Payee,
                            PayTime = ropItem.AuditGMDate,
                            OrbitalPrivateTransfer = ropItem.OrbitalPrivateTransfer,
                            PayType = payStr,
                            IsPay = ropItem.IsPay,
                            Applicant = ropItem.Appliction,
                            Remark = ropItem.SKTHRemark
                        };

                        _promView.Add(gsd_PaymentRefund);
                    }

                    SKTHCNYTotalCost = _promView.Sum(x => x.CNYPrice);
                    SKTHFeeDt = CommonFun.ToDataTableArray(_promView);
                    SKTHFeeDt.TableName = $"SKTHFeeView";
                }

            }

            #endregion

            #region 应收金额(增加方式=实际报价时 费用必须审核才能进入团组报表)
            decimal frTotalAmount = 0.00M;//应收总金额
            string _frSql = string.Format(@"  Select fr.Id,fr.AddingWay,fr.Status,u.CnName As Auditor,fr.AuditTime,fr.Diid,fr.PriceName,fr.Price,fr.Count,fr.Unit,fr.Currency,
										        sd.Name As CurrencyCode,sd.Remark As CurrencyName,fr.Rate,fr.ItemSumPrice,fr.CreateTime,fr.Remark
										        From Fin_ForeignReceivables fr
										        Left Join Sys_SetData sd On fr.Currency = sd.Id
												Left Join Sys_Users u On fr.Auditor = u.Id
										        Where fr.IsDel = 0 And fr.Diid = {0} Order By CreateTime", _dto.DiId);
            var _frViews = await _sqlSugar.SqlQueryable<Gsd_ForeignReceivablesView>(_frSql).ToListAsync();

            _frViews.ForEach(x =>
            {
                string namePrefix = string.Empty;
                if (x.AddingWay == 0) namePrefix = $"账单模块-";
                else if (x.AddingWay == 1) namePrefix = $"成本预算模块-";
                else if (x.AddingWay == 2) namePrefix = $"实际报价-";
                x.PriceName = $"{namePrefix}{x.PriceName}";
            });

            if (_frViews.Any())
            {
                accountsAmount = _frViews.Sum(it => it.ItemSumPrice);
                receivableFeeDt = CommonFun.ToDataTableArray(_frViews);
                receivableFeeDt.TableName = $"ReceivedFeeView";
            }

            #endregion

            #region 已收金额
            decimal prTotalAmount = 0.00M;//已收总金额
            string _prSql = string.Format(@"Select pr.Id,pr.Diid,pr.SectionTime As SectionTimeDt,pr.Price,pr.Currency,
										        sd1.Name As CurrencyCode,sd1.Remark As CurrencyName,pr.Client,
										        pr.ReceivablesType,sd2.Name As ReceivablesTypeName,pr.Remark,pr.CreateTime
										        From Fin_ProceedsReceived  pr
										        Left Join Sys_SetData sd1 On pr.Currency = sd1.Id
										        Left Join Sys_SetData sd2 On pr.ReceivablesType = sd2.Id
										        Where pr.IsDel = 0 and pr.Diid = {0} Order By CreateTime", _dto.DiId);
            var _prViews = await _sqlSugar.SqlQueryable<Gsd_ProceedsReceivedView>(_prSql).ToListAsync();

            if (_prViews.Any())
            {
                receivedAmount = _prViews.Sum(it => it.Price);
                receivedFeeDt = CommonFun.ToDataTableArray(_prViews);
                receivedFeeDt.TableName = $"ReceivableFeeView";
            }

            #endregion

            #region 团组收入支出利润
            groupIEProfitDt.Columns.AddRange(new DataColumn[] {
                            new DataColumn(){ ColumnName = "ModuleName"},
                            new DataColumn(){ ColumnName = "FeeTotal",DataType = typeof(decimal)},
                            new DataColumn(){ ColumnName = "FeeCurrency"},
                        });

            var groupIEProfitDt_rows = new[] {
                new object[] { $"签证费用", visaCNYTotalCost, $"CNY" },
                new object[] { $"酒店费用", hotelCNYTotalCost, $"CNY" },
                new object[] { $"地接费用", OPCNYTotalCost, $"CNY" },
                new object[] { $"商邀费用", OACNYTotalCost, $"CNY" },
                new object[] { $"机票费用", airTicketCNYTotalCost, $"CNY" },
                new object[] { $"保险费用", insureCNYTotalCost, $"CNY" },
                new object[] { $"其他费用", otherCNYTotalCost, $"CNY" },
                new object[] { $"收款退还费用", SKTHCNYTotalCost, $"CNY" },
            };

            // 批量添加
            foreach (var rowData in groupIEProfitDt_rows)
            {
                groupIEProfitDt.Rows.Add(rowData);
            }

            #endregion

            #region 统一填充数据源
            designer.SetDataSource("GroupNo", groupNo);
            designer.SetDataSource("GroupName", groupName);
            designer.SetDataSource("Lister", lister);

            //酒店
            designer.SetDataSource(hotelFeeDt);
            designer.SetDataSource("HotelCNYTotalCost", $"{hotelCNYTotalCost.ToString("#0.00")} CNY");

            //地接
            designer.SetDataSource(OPFeeDt);
            designer.SetDataSource("OPFeeLabel", OPFeeLabel);
            designer.SetDataSource("OPCNYTotalCost", $"{OPCNYTotalCost.ToString("#0.00")} CNY");

            //签证
            designer.SetDataSource(visaFeeDt);
            designer.SetDataSource("VisaCNYTotalCost", $"{visaCNYTotalCost.ToString("#0.00")} CNY");

            //商邀
            designer.SetDataSource(OAFeeDt);
            designer.SetDataSource("OACNYTotalCost", $"{OACNYTotalCost.ToString("#0.00")} CNY");

            //机票
            designer.SetDataSource(airTicketFeeDt);
            designer.SetDataSource("AirTicketCNYTotalCost", $"{airTicketCNYTotalCost.ToString("#0.00")} CNY");

            //保险
            designer.SetDataSource(insureFeeDt);
            designer.SetDataSource("InsureCNYTotalCost", $"{insureCNYTotalCost.ToString("#0.00")} CNY");

            //其他费用
            designer.SetDataSource(otherFeeDt);
            designer.SetDataSource("OtherCNYTotalCost", $"{otherCNYTotalCost.ToString("#0.00")} CNY");

            //收款退还
            designer.SetDataSource(SKTHFeeDt);
            designer.SetDataSource("SKTHCNYTotalCost", $"{SKTHCNYTotalCost.ToString("#0.00")}");

            //已收金额
            designer.SetDataSource(receivedFeeDt);
            designer.SetDataSource("ReceivedAmount", $"{receivedAmount.ToString("#0.00")}");

            //应收金额
            designer.SetDataSource(receivableFeeDt);
            designer.SetDataSource("AccountsAmount", $"{accountsAmount.ToString("#0.00")}");

            //应收已收 -- 尾款
            var balancePayment = accountsAmount - SKTHCNYTotalCost - receivedAmount;
            designer.SetDataSource("BalancePayment", $"{balancePayment.ToString("#0.00")}");

            //团组收入支出利润
            designer.SetDataSource(groupIEProfitDt);
            #region 各项费用计算
            /* 
             * 团组报表计算方式
             * 当前总支出 = 团组支出.Sum() + 超支费用.Sum()
             * 应收金额 = 应收表.Sum() - 收款退还
             * 已收金额 = 已收表.Sum() - 收款退还
             * 应收利润(应收-支出) = 应收金额 - 当前总支出
             * 已收利润(已收-支出) = 已收金额 - 当前总支出
             * 利润差 = 应收利润 - 已收利润
             */

            totalAmount = visaCNYTotalCost + hotelCNYTotalCost + OPCNYTotalCost + OACNYTotalCost + airTicketCNYTotalCost + insureCNYTotalCost + otherCNYTotalCost;//合计金额
            accountsProfit = accountsAmount - totalAmount - SKTHCNYTotalCost;   //应收利润
            receiveProfit = receivedAmount - totalAmount - SKTHCNYTotalCost; //已收利润
            profitMargin = accountsProfit - receiveProfit;   //利润差

            designer.SetDataSource("TotalAmount", $"{totalAmount.ToString("#0.00")}");
            designer.SetDataSource("AccountsProfit", $"{accountsProfit.ToString("#0.00")}");
            designer.SetDataSource("ReceiveProfit", $"{receiveProfit.ToString("#0.00")}");
            designer.SetDataSource("ProfitMargin", $"{profitMargin.ToString("#0.00")}");
            #endregion

            designer.Process();

            #endregion

            #region 单元格样式设置  未付款设置为红色

            /*
             * 设置单元格样式
             */
            //背景颜色
            Style style = designer.Workbook.CreateStyle();
            style.ForegroundColor = Color.FromArgb(254, 242, 203);
            style.Pattern = BackgroundType.Solid;
            //字体
            style.Font.Name = "微软雅黑";                   // 字体名称
            style.Font.Size = 10;                           // 字体大小
            style.Font.Color = System.Drawing.Color.Black;  // 字体颜色



            StatementExportExcelSetCell(designer, hotelFeeSheet, hotelFeeDt, style, "IsPayLable", 8, "A", "V");    //酒店
            StatementExportExcelSetCell(designer, OPFeeSheet, OPFeeDt, style, "IsPayLabel", 6, "A", "M");          //OP
            StatementExportExcelSetCell(designer, visaFeeSheet, visaFeeDt, style, "IsPay", 6, "A", "I");           //签证
            StatementExportExcelSetCell(designer, OAFeeSheet, OAFeeDt, style, "IsPay", 6, "A", "R");               //商邀
            StatementExportExcelSetCell(designer, airTicketFeeSheet, airTicketFeeDt, style, "IsPay", 6, "A", "M"); //机票
            StatementExportExcelSetCell(designer, insureFeeSheet, insureFeeDt, style, "IsPay", 6, "A", "I");       //保险
            StatementExportExcelSetCell(designer, otherFeeSheet, otherFeeDt, style, "IsPay", 6, "A", "J");         //其他
            StatementExportExcelSetCell(designer, SKTHFeeSheet, SKTHFeeDt, style, "IsPayLable", 6, "A", "K");      //首款退还
            #endregion

            //文件名
            string fileName = $"{groupName}[{groupNo}]_团组费用清单{DateTime.Now.ToString("yyyyMMddHHmmss")}.xls";
            designer.Workbook.Save(AppSettingsHelper.Get("ExcelBasePath") + "GroupStatement/" + fileName);
            string url = AppSettingsHelper.Get("ExcelBaseUrl") + "Office/Excel/GroupStatement/" + fileName;
            return Ok(JsonView(true, "成功", url));
        }

        private void StatementExportExcelSetCell(WorkbookDesigner designer, Worksheet sheet, DataTable dt, Style style, string judgeLable, int startIndex, string startRange, string endRange)
        {
            if (designer == null) return;
            if (sheet == null) return;
            if (style == null) return;
            if (dt == null) return;
            if (string.IsNullOrEmpty(startRange)) return;
            if (string.IsNullOrEmpty(endRange)) return;

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                var isPayStr = dt.Rows[i][$"{judgeLable}"].ToString();
                if (string.IsNullOrEmpty(isPayStr)) continue;

                if (isPayStr.Contains("未付款"))
                {
                    var excelIndex = startIndex + i;
                    Aspose.Cells.Range range = sheet.Cells.CreateRange($"{startRange}{excelIndex}", $"{endRange}{excelIndex}");
                    range.ApplyStyle(style, new StyleFlag() { CellShading = true, Font = true });
                }
            }

        }

        #endregion

        #region 报表/折线图统计
        //企业利润-团组利润
        //企业利润-会务利润

        /// <summary>
        ///  企业利润
        ///  Details 
        ///  待添加权限验证
        /// </summary>
        /// <param name="_dto">团组列表请求dto</param>
        /// <returns></returns>
        [HttpPost("PostCorporateProfit")]
        //[JsonConverter(typeof(DecimalConverter), 2)]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> PostCorporateProfit(PostCorporateProfitDto _dto)
        {
            #region  参数验证
            if (_dto.UserId < 1) return Ok(JsonView(false, "员工Id为空"));
            if (_dto.PageId < 1) _dto.PageId = 38; //团组报表页面Id
            if (_dto.Year < 1) return Ok(JsonView(false, "请输入有效的Year参数!"));
            if (_dto.StatisticsType > 2 && _dto.StatisticsType < 1) return Ok(JsonView(false, "请输入有效的StatisticsType参数，1 月份 2 季度"));
            if (_dto.BusinessType > 3 && _dto.BusinessType < 1) return Ok(JsonView(false, "请输入有效的BusinessType参数，1 所有 2 团组 3 会务"));

            PageFunAuthViewBase pageFunAuthView = new PageFunAuthViewBase();

            #region 页面操作权限验证
            //pageFunAuthView = await GeneralMethod.PostUserPageFuncDatas(_dto.UserId, _dto.PageId);

            //if (pageFunAuthView.CheckAuth == 0) return Ok(JsonView(false, "您没有查看权限"));
            #endregion

            string sqlWhere = string.Empty;

            //起止时间
            DateTime beginDt = Convert.ToDateTime($"{_dto.Year}-01-01 00:00:00");
            DateTime endDt = Convert.ToDateTime($"{_dto.Year}-12-31 23:59:59");

            sqlWhere = string.Format(@$" Where Isdel = 0 ");

            //业务类型
            List<int> groupTypeId = new List<int>();
            if (_dto.BusinessType == 2) //团组
            {
                groupTypeId.AddRange(new List<int>() {
                    38,   //  政府团
                    39,   //  企业团
                    40,   //  散客团
                    1048  // 高校团
                });
            }
            else if (_dto.BusinessType == 3) //会务
            {
                groupTypeId.AddRange(new List<int>() {
                   102,   // 未知
                   248,   // 非团组
                   302,   // 成都-会务活动
                   691,   // 四川-会务活动
                   762,   // 四川-赛事项目收入
                   1047   // 成都-赛事项目收入
                });
            }

            if (groupTypeId.Count > 0)
            {
                sqlWhere += string.Format(@$" And TeamDid In ({string.Join(',', groupTypeId)})");
            }

            string sql = string.Format(@$"Select * From Grp_DelegationInfo {sqlWhere}");

            var groupInfos = await _sqlSugar.SqlQueryable<Grp_DelegationInfo>(sql).Where(it => it.CreateTime >= beginDt && it.CreateTime <= endDt).ToListAsync();

            if (groupInfos.Count < 1) return Ok(JsonView(false, "暂无相关团组!"));

            List<int> diIds = groupInfos.Select(it => it.Id).ToList();

            List<CorporateProfit> corporateProfits = await CorporateProfit(diIds);

            List<MonthInfo> months = new List<MonthInfo>();
            if (_dto.StatisticsType == 1) //月份
            {
                months = GeneralMethod.GetMonthInfos(Convert.ToInt32(_dto.Year));

            }
            else if (_dto.StatisticsType == 1) //季度
            {
                months = GeneralMethod.GetQuarter(Convert.ToInt32(_dto.Year));
            }

            List<CorporateProfitMonthView> _view = new List<CorporateProfitMonthView>();
            foreach (var item in months)
            {
                DateTime monthBeginDt = Convert.ToDateTime($"{_dto.Year}-{item.Month}-{item.Days.BeginDays} 00:00:00");
                DateTime monthEndDt = Convert.ToDateTime($"{_dto.Year}-{item.Month}-{item.Days.EndDays} 23:59:59");

                var corporateProfit = corporateProfits.Where(it => it.CreateDt >= monthBeginDt && it.CreateDt <= monthEndDt).ToList();

                _view.Add(new CorporateProfitMonthView()
                {
                    Month = item.Month,
                    Profit = corporateProfit.Sum(it => it.ReceivedProfit),
                    GroupInfos = corporateProfit.OrderBy(it => it.CreateDt).ToList()
                });
            }

            return Ok(JsonView(true, "操作成功!", _view));
            #endregion

        }

        /// <summary>
        /// 计算团组利润
        /// </summary>
        /// <param name="diIds"></param>
        /// <returns></returns>
        private async Task<List<CorporateProfit>> CorporateProfit(List<int> diIds)
        {
            List<CorporateProfit> corporateProfits = new List<CorporateProfit>();
            if (diIds.Count < 1)
            {
                return corporateProfits;
            }

            #region 计算团组利润

            /* 
            * 团组报表计算方式
            * 当前总支出 = 团组支出.Sum() + 超支费用.Sum()
            * 应收金额 = 应收表.Sum()
            * 已收金额 = 已收表.Sum()
            * 应收利润(应收-支出) = 应收金额 - 收款退还 - 当前总支出
            * 已收利润(已收-支出) = 已收金额 - 收款退还 - 当前总支出
            * 
            */


            string diIdStr = string.Join(",", diIds);

            string sql = string.Format(@$"Select * From Grp_DelegationInfo Where Isdel = 0 And Id In ({diIdStr})");
            var groupInfos = await _sqlSugar.SqlQueryable<Grp_DelegationInfo>(sql).ToListAsync();

            #region 费用类型 币种，转账,客户信息

            List<Sys_SetData> _setDatas = await _sqlSugar.Queryable<Sys_SetData>().Where(it => it.IsDel == 0).ToListAsync();
            var _clientDatas = await _sqlSugar.Queryable<Crm_DeleClient>().Where(it => it.IsDel == 0).ToListAsync();

            #endregion

            foreach (var _diId in diIds)
            {

                List<ExpenditureInfo> expenditureInfos = new List<ExpenditureInfo>();

                #region 团组收入

                /*
                 * 应收报表
                 */
                decimal frTotalAmount = 0.00M;//应收总金额
                string _frSql = string.Format(@"Select fr.Id,fr.Diid,fr.PriceName,fr.Price,fr.Count,fr.Unit,fr.Currency,
										        sd.Name As CurrencyCode,sd.Remark As CurrencyName,fr.Rate,fr.ItemSumPrice,fr.CreateTime
										        From Fin_ForeignReceivables fr
										        Left Join Sys_SetData sd On fr.Currency = sd.Id
										        Where fr.IsDel = 0 And fr.Diid = {0} Order By CreateTime", _diId);
                List<Gsd_ForeignReceivablesView> _frViews = await _sqlSugar.SqlQueryable<Gsd_ForeignReceivablesView>(_frSql).ToListAsync();
                frTotalAmount = _frViews.Sum(it => it.ItemSumPrice);

                expenditureInfos.Add(new ExpenditureInfo() { ItemName = "应收项", Amount = frTotalAmount });

                /*
                 * 已收报表
                 */
                decimal prTotalAmount = 0.00M;//已收总金额
                string _prSql = string.Format(@"Select pr.Id,pr.Diid,pr.SectionTime As SectionTimeDt,pr.Price,pr.Currency,
										        sd1.Name As CurrencyCode,sd1.Remark As CurrencyName,pr.Client,
										        pr.ReceivablesType,sd2.Name As ReceivablesTypeName,pr.Remark,pr.CreateTime
										        From Fin_ProceedsReceived  pr
										        Left Join Sys_SetData sd1 On pr.Currency = sd1.Id
										        Left Join Sys_SetData sd2 On pr.ReceivablesType = sd2.Id
										        Where pr.IsDel = 0 and pr.Diid = {0} Order By CreateTime", _diId);
                List<Gsd_ProceedsReceivedView> _prViews = await _sqlSugar.SqlQueryable<Gsd_ProceedsReceivedView>(_prSql).ToListAsync();
                prTotalAmount = _prViews.Sum(it => it.Price);

                expenditureInfos.Add(new ExpenditureInfo() { ItemName = "已收项", Amount = prTotalAmount });

                /*
                 * 超支费用 
                 */
                decimal exTotalAmount = 0.00M;
                string _ecSql = string.Format(@"Select gec.Id As GECId,gec.DiId As GECDiId,gec.PriceName,ccp.PayMoney,sd1.Name As PaymentCurrency,
													   ccp.RMBPrice As CNYPrice,ccp.DayRate,ccp.Payee,ccp.OrbitalPrivateTransfer,sd2.Name As PayWay,
													   sd3.Name As CardType,ccp.IsPay,u.CnName As Applicant,gec.CreateTime
												From OA2023DB.dbo.Fin_GroupExtraCost gec
												Left Join Grp_CreditCardPayment ccp On gec.Id = ccp.CId
                                                Left Join Sys_SetData sd1 On ccp.PaymentCurrency = sd1.Id
                                                Left Join Sys_SetData sd2 On ccp.PayDId = sd2.Id 
                                                Left Join Sys_SetData sd3 On ccp.CTDId = sd3.Id
                                                Left Join Sys_Users u On ccp.CreateUserId = u.Id
												Where ccp.IsDel = 0 And ccp.CTable = 1015 And ccp.IsAuditGM = 1 And ccp.IsPay = 1 And ccp.DiId = {0} Order By CreateTime", _diId);

                List<Gsd_ExtraCostsView> _ExtraCostsViews = await _sqlSugar.SqlQueryable<Gsd_ExtraCostsView>(_ecSql).ToListAsync();
                exTotalAmount = _ExtraCostsViews.Sum(it => it.CNYPrice);

                expenditureInfos.Add(new ExpenditureInfo() { ItemName = "超支费用", Amount = exTotalAmount });

                /*
                 * 收款退还
                 */
                decimal promTotalAmount = 0.00M;// 收款退还总金额
                List<Gsd_PaymentRefundAndOtherMoneyView> _promView = new List<Gsd_PaymentRefundAndOtherMoneyView>();

                //删除了  And prom.PriceType = 1
                string _ropSql = string.Format(@"Select u.CnName As Appliction,prom.Id As PrId,prom.DiId As PrDiId,prom.Price As PrPrice,
										         prom.PriceName AS PrPriceName,prom.CurrencyId As PrCurrencyId,
										         prom.PayType As PrPayType,prom.PriceType As PrPriceType,ccp.*,prom.CreateTime As PrCreateTime
										         From Fin_PaymentRefundAndOtherMoney prom
										         Left Join Grp_CreditCardPayment ccp On prom.DiId = ccp.DIId And prom.Id = ccp.CId
										         Left Join Sys_Users u On ccp.CreateUserId = u.Id
										         Where prom.IsDel = 0  And prom.PayType = 1 And ccp.CTable = 285
										         And ccp.IsAuditGM = 1 And ccp.IsPay = 1
										         And prom.DiId = {0} Order By PrCreateTime", _diId);
                var _promDatas = await _sqlSugar.SqlQueryable<Gsd_PaymentRefundAndOtherMoneyDataSource1View>(_ropSql).ToListAsync();

                foreach (var ropItem in _promDatas)
                {
                    string thisCueencyCode = "Unknown";
                    string thisCueencyName = "Unknown";
                    var currency = _setDatas.Where(it => it.Id == ropItem.PaymentCurrency).FirstOrDefault();
                    if (currency != null)
                    {
                        thisCueencyCode = currency.Name;
                        thisCueencyName = currency.Remark;
                    }

                    string orbitalPrivateTransferStr = "Unknown";
                    var orbitalPrivateTransfer = _setDatas.Where(it => it.Id == ropItem.OrbitalPrivateTransfer).FirstOrDefault();
                    if (orbitalPrivateTransfer != null)
                    {
                        orbitalPrivateTransferStr = orbitalPrivateTransfer.Name;
                    }

                    string payStr = "Unknown";
                    var pay = _setDatas.Where(it => it.Id == ropItem.PayDId).FirstOrDefault();
                    if (pay != null)
                    {
                        payStr = pay.Name;
                    }

                    Gsd_PaymentRefundAndOtherMoneyView gsd_PaymentRefund = new Gsd_PaymentRefundAndOtherMoneyView()
                    {
                        Id = ropItem.Id,
                        DiId = ropItem.DIId,
                        PriceName = ropItem.PrPriceName,
                        PayCurrencyCode = thisCueencyCode,
                        PayCurrencyName = thisCueencyName,
                        Price = ropItem.PrPrice,
                        CNYPrice = ropItem.RMBPrice,
                        ThisRate = ropItem.DayRate,
                        Payee = ropItem.Payee,
                        PayTime = ropItem.AuditGMDate,
                        OrbitalPrivateTransfer = ropItem.OrbitalPrivateTransfer,
                        PayType = payStr,
                        IsPay = ropItem.IsPay,
                        Applicant = ropItem.Appliction

                    };

                    _promView.Add(gsd_PaymentRefund);
                }

                promTotalAmount = _promView.Sum(it => it.CNYPrice);

                expenditureInfos.Add(new ExpenditureInfo() { ItemName = "收款退还", Amount = promTotalAmount });
                #endregion

                #region 团组支出
                GroupExpenditureView _geView = new GroupExpenditureView();

                #region 酒店预定费用
                List<GroupHotelFeeView> groupHotelFeeViews = new List<GroupHotelFeeView>();

                string hotelFeeSql = string.Format(@"Select hr.Id As HrId,hr.DiId As HrDiId,hr.City,hr.HotelName,hr.CheckInDate,hr.CheckOutDate,
                                                     sd1.Name As PaymentCurrency,hr.SingleRoomPrice,hr.SingleRoomCount,hr.DoubleRoomPrice,
                                                     hr.DoubleRoomCount,hr.SuiteRoomPrice,hr.SuiteRoomCount,hr.OtherRoomPrice,hr.OtherRoomCount,
                                                     hr.BreakfastPrice,sd4.Name As BreakfastCurrency,hr.Isoppay,hr.GovernmentRent,
                                                     sd5.Name As GovernmentRentCurrency,hr.CityTax,sd6.Name As CityTaxCurrency,
                                                     ccp.PayMoney,ccp.RMBPrice As CNYPrice,ccp.DayRate,ccp.Payee,ccp.OrbitalPrivateTransfer,
                                                     sd2.Name As PayWay,sd3.Name As CardType,ccp.IsPay,u.CnName As Applicant
                                                     From Grp_HotelReservations  hr
                                                     Left Join Grp_CreditCardPayment ccp On hr.Id = ccp.CId
                                                     Left Join Sys_SetData sd1 On ccp.PaymentCurrency = sd1.Id
                                                     Left Join Sys_SetData sd2 On ccp.PayDId = sd2.Id
                                                     Left Join Sys_SetData sd3 On ccp.CTDId = sd3.Id
                                                     Left Join Sys_Users u On ccp.CreateUserId = u.Id
                                                     Left Join Sys_SetData sd4 On hr.BreakfastCurrency = sd4.Id
                                                     Left Join Sys_SetData sd5 On hr.GovernmentRentCurrency = sd5.Id
                                                     Left Join Sys_SetData sd6 On hr.CityTaxCurrency = sd6.Id
                                                     Where hr.IsDel = 0 And ccp.IsDel = 0 And ccp.CTable = 76 And ccp.IsAuditGM = 1 And ccp.IsPay = 1 And hr.DiId = {0} 
                                                     Order By CheckInDate Asc", _diId);

                groupHotelFeeViews = await _sqlSugar.SqlQueryable<GroupHotelFeeView>(hotelFeeSql).ToListAsync();

                decimal HotelCNYTotalPrice = groupHotelFeeViews.Sum(it => it.CNYPrice);

                expenditureInfos.Add(new ExpenditureInfo() { ItemName = "酒店预定", Amount = HotelCNYTotalPrice });
                #endregion

                #region 地接费用

                List<GroupCTGGRFeeView> groupCTGGRFeeViews = new List<GroupCTGGRFeeView>();

                string CTGGRFeeSql = string.Format(@"Select ctggr.Id As CTGGRId,ctggr.DiId As CTGGRDiId,ctggr.Area,ctggrc.*,ctggrc.Price As PayMoney,
                                                     sd2.name As PaymentCurrency,ccp.PayPercentage,
                                                     (ctggrc.Price / (ccp.PayPercentage / 100)) As AmountPaid,
                                                     (ctggrc.Price / (ccp.PayPercentage / 100) - ctggrc.Price) As BalancePayment,
                                                     ccp.DayRate,(ctggrc.Price * ccp.DayRate)  As CNYPrice,ccp.Payee,ccp.AuditGMDate,
                                                     ccp.OrbitalPrivateTransfer,sd1.Name As PayWay,ccp.IsPay,u.CnName As Applicant,ctggr.CreateTime
                                                     From Grp_CarTouristGuideGroundReservations ctggr
                                                     Left Join ( Select cggrc.CTGGRId,sd1.Name As PriceName,cggrc.Price,sd2.Name As PriceCurrency,
                                                                     cggrc.PriceContent
                                                             From Grp_CarTouristGuideGroundReservationsContent cggrc
                                                             Left Join Sys_SetData sd1 On cggrc.SId = sd1.Id
                                                             Left Join Sys_SetData sd2 On cggrc.Currency = sd2.Id
                                                             Where cggrc.ISdel = 0 And cggrc.Price != 0.00 
                                                             ) ctggrc On ctggr.Id = ctggrc.CTGGRId 
                                                     Left Join Grp_CreditCardPayment ccp On ccp.IsDel = 0  And ccp.CTable = 79 And ctggr.Id = ccp.CId
                                                     Left Join Sys_SetData sd1 On ccp.PayDId = sd1.Id
                                                     Left Join Sys_SetData sd2 On ccp.PaymentCurrency = sd2.Id
                                                     Left Join Sys_Users u On ccp.CreateUserId = u.Id
                                                     Where ctggr.IsDel = 0 And ccp.IsAuditGM = 1 And ccp.IsPay = 1 And ctggr.DiId = {0}
                                                     Order By CreateTime", _diId);

                groupCTGGRFeeViews = await _sqlSugar.SqlQueryable<GroupCTGGRFeeView>(CTGGRFeeSql).ToListAsync();

                decimal CTGGRCNYTotalPrice = groupCTGGRFeeViews.Sum(it => Convert.ToDecimal(it.CNYPrice.ToString("#0.00")));

                expenditureInfos.Add(new ExpenditureInfo() { ItemName = "地接", Amount = CTGGRCNYTotalPrice });
                #endregion

                #region 机票预订费用

                List<GroupAirFeeView> groupAirFeeViews = new List<GroupAirFeeView>();
                string groupAirFeeSql = string.Format(@"Select atr.Id As AirId,atr.DIId As AirDiId,atr.FlightsCode,atr.FlightsCity,sd4.Name As AirTypeName,
                                                        atr.FlightsDate,atr.FlightsTime,atr.ClientName,atr.ClientNum,ccp.PayMoney,
                                                        sd1.Name As PayMoneyCurrency,ccp.RMBPrice As CNYPrice,ccp.DayRate,ccp.Payee,ccp.AuditGMDate,
                                                        ccp.OrbitalPrivateTransfer,sd2.Name As PayWay,sd3.Name As CardType,ccp.IsPay,u.CnName As Applicant,atr.CreateTime
                                                        From Grp_AirTicketReservations atr
                                                        Left Join Grp_CreditCardPayment ccp On ccp.isdel = 0 And ccp.CTable = 85 And atr.Id = ccp.CId
                                                        Left Join Sys_SetData sd1 On ccp.PaymentCurrency = sd1.Id
                                                        Left Join Sys_SetData sd2 On ccp.PayDId = sd2.Id
                                                        Left Join Sys_SetData sd3 On ccp.CTDId = sd3.Id
                                                        Left Join Sys_SetData sd4 On atr.CType = sd4.Id
                                                        Left Join Sys_Users u On ccp.CreateUserId = u.Id
                                                        Where atr.IsDel = 0 And ccp.IsAuditGM = 1 And ccp.IsPay = 1 And atr.DiId = {0} Order By CreateTime", _diId);
                groupAirFeeViews = await _sqlSugar.SqlQueryable<GroupAirFeeView>(groupAirFeeSql).ToListAsync();

                decimal AirCNYTotalPrice = groupAirFeeViews.Sum(it => it.CNYPrice);

                expenditureInfos.Add(new ExpenditureInfo() { ItemName = "机票预订", Amount = AirCNYTotalPrice });
                #endregion

                #region 签证费用

                List<GroupVisaFeeView> groupVisaFeeViews = new List<GroupVisaFeeView>();
                string groupVisaFeeSql = string.Format(@"Select vi.Id As VisaId,vi.DIId As VisaDiId,vi.VisaClient,ccp.PayMoney,sd1.Name As PayMoneyCurrency,
                                                         ccp.DayRate,ccp.Payee,ccp.AuditGMDate,ccp.OrbitalPrivateTransfer,sd2.Name As PayWay,
                                                         sd3.Name As CardTypeName,ccp.IsPay,u.CnName As Applicant,vi.CreateTime
                                                         From Grp_VisaInfo vi
                                                         Left Join Grp_CreditCardPayment ccp On ccp.isdel = 0 And ccp.CTable = 80 And vi.Id = ccp.CId
                                                         Left Join Sys_SetData sd1 On ccp.PaymentCurrency = sd1.Id
                                                         Left Join Sys_SetData sd2 On ccp.PayDId = sd2.Id
                                                         Left Join Sys_SetData sd3 On ccp.CTDId = sd3.Id
                                                         Left Join Sys_Users u On ccp.CreateUserId = u.Id
                                                         Where vi.IsDel = 0 And ccp.IsAuditGM = 1 And ccp.IsPay = 1 And vi.DIId = {0} Order By CreateTime", _diId);

                groupVisaFeeViews = await _sqlSugar.SqlQueryable<GroupVisaFeeView>(groupVisaFeeSql).ToListAsync();

                decimal VisaCNYTotalPirce = groupVisaFeeViews.Sum(it => it.PayMoney);

                expenditureInfos.Add(new ExpenditureInfo() { ItemName = "签证", Amount = VisaCNYTotalPirce });
                #endregion

                #region 邀请/公务活动  CTable = 81

                List<GroupInvitationalFeeView> groupInvitationalFeeViews = new List<GroupInvitationalFeeView>();
                string groupInvitationalFeeSql = string.Format(@"Select ioa.Id As IOAId,ioa.DiId As IOADiId,ioa.InviterArea,ioa.Inviter,ioa.InviteTime,
                                                                 ioa.InviteCost,sd3.Name As InviteCurrency,ioa.SendCost,sd4.Name As SendCurrency,ioa.EventsCost,
                                                                 sd5.Name As EventsCurrency,ioa.TranslateCost,sd6.Name As TranslateCurrency,ccp.PayMoney,
                                                                 sd7.Name As PaymentCurrency,ccp.RMBPrice As CNYPrice,ccp.Payee,ccp.AuditGMDate,
                                                                 ccp.OrbitalPrivateTransfer,sd2.Name As PayWay,ccp.IsPay,u.CnName As Applicant,ioa.CreateTime
                                                                 From  Grp_InvitationOfficialActivities ioa
                                                                 Left Join Grp_CreditCardPayment ccp On ccp.isdel = 0 And ccp.CTable = 81 And ioa.Id = ccp.CId
                                                                 Left Join Sys_SetData sd1 On ccp.PaymentCurrency = sd1.Id
                                                                 Left Join Sys_SetData sd2 On ccp.PayDId = sd2.Id
                                                                 Left Join Sys_SetData sd3 On ioa.InviteCurrency = sd3.Id
                                                                 Left Join Sys_SetData sd4 On ioa.SendCurrency = sd4.Id
                                                                 Left Join Sys_SetData sd5 On ioa.EventsCurrency = sd5.Id
                                                                 Left Join Sys_SetData sd6 On ioa.TranslateCurrency = sd6.Id
                                                                 Left Join Sys_SetData sd7 On ccp.PaymentCurrency = sd7.Id
                                                                 Left Join Sys_Users u On ccp.CreateUserId = u.Id
                                                                 Where ioa.IsDel = 0 And ccp.IsAuditGM = 1 And ccp.IsPay = 1 And  ioa.Diid = {0} Order By CreateTime", _diId);

                groupInvitationalFeeViews = await _sqlSugar.SqlQueryable<GroupInvitationalFeeView>(groupInvitationalFeeSql).ToListAsync();

                decimal InvitationalCNYTotalPrice = groupInvitationalFeeViews.Sum(it => it.CNYPrice);

                expenditureInfos.Add(new ExpenditureInfo() { ItemName = "邀请/公务活动", Amount = InvitationalCNYTotalPrice });
                #endregion

                #region 保险费用
                List<GroupInsuranceFeeView> groupInsuranceFeeViews = new List<GroupInsuranceFeeView>();
                string groupInsuranceFeeSql = string.Format(@"Select ic.Id As InsuranceId,ic.Diid As InsuranceDiId,ClientName,ccp.PayMoney,ccp.RMBPrice As CNYPrice,
                                                              sd1.Name As PayMoneyCurrency,ccp.Payee,ccp.AuditGMDate,ccp.OrbitalPrivateTransfer,
                                                              sd2.Name As PayWay,ccp.IsPay,u.CnName As Applicant,ic.CreateTime
                                                              From Grp_Customers ic
                                                              Left Join Grp_CreditCardPayment ccp On ccp.isdel = 0 And ccp.CTable = 82 And ic.Id = ccp.CId
                                                              Left Join Sys_SetData sd1 On ccp.PaymentCurrency = sd1.Id
                                                              Left Join Sys_SetData sd2 On ccp.PayDId = sd2.Id
                                                              Left Join Sys_Users u On ccp.CreateUserId = u.Id
                                                              Where ic.IsDel = 0 And ccp.IsAuditGM = 1 And ccp.IsPay = 1 And ic.DiId = {0} Order By CreateTime", _diId);
                groupInsuranceFeeViews = await _sqlSugar.SqlQueryable<GroupInsuranceFeeView>(groupInsuranceFeeSql).ToListAsync();

                decimal InsuranceCNYTotalPrice = groupInsuranceFeeViews.Sum(it => it.CNYPrice);

                expenditureInfos.Add(new ExpenditureInfo() { ItemName = "保险费用", Amount = InsuranceCNYTotalPrice });
                #endregion

                #region 其他款项费用  98
                List<GroupDecreaseFeeView> groupDecreaseFeeViews = new List<GroupDecreaseFeeView>();
                string groupDecreaseFeeSql = string.Format(@"Select dp.Id As DPId,dp.DiId As DPDiId,dp.PriceName,ccp.PayMoney,sd1.Name As PayMoneyCurrency,
                                                              (((ccp.PayMoney * ccp.DayRate) / ccp.PayPercentage) * 100) As CNYPrice,   
                                                             ccp.DayRate,ccp.Payee,ccp.AuditGMDate,ccp.OrbitalPrivateTransfer,
                                                             sd2.Name As PayWay,ccp.IsPay,u.CnName As Applicant,dp.CreateTime
                                                             From Grp_DecreasePayments dp
                                                             Left Join Grp_CreditCardPayment ccp On ccp.isdel = 0 And ccp.CTable = 98 And dp.Id = ccp.CId
                                                             Left Join Sys_SetData sd1 On ccp.PaymentCurrency = sd1.Id
                                                             Left Join Sys_SetData sd2 On ccp.PayDId = sd2.Id
                                                             Left Join Sys_Users u On ccp.CreateUserId = u.Id
                                                             Where dp.IsDel = 0 And ccp.Ctable = 98 And ccp.IsAuditGM = 1 And ccp.IsPay = 1 And dp.Diid = {0} 
                                                             Order By CreateTime", _diId);
                groupDecreaseFeeViews = await _sqlSugar.SqlQueryable<GroupDecreaseFeeView>(groupDecreaseFeeSql).ToListAsync();

                decimal DecreaseCNYTotalPrice = groupDecreaseFeeViews.Sum(it => Convert.ToDecimal(it.CNYPrice.ToString("#0.00")));

                expenditureInfos.Add(new ExpenditureInfo() { ItemName = "其他款项", Amount = DecreaseCNYTotalPrice });
                #endregion

                #endregion

                /* 
                 * 团组报表计算方式
                 * 当前总支出 = 团组支出.Sum() + 超支费用.Sum()
                 * 应收金额 = 应收表.Sum()
                 * 已收金额 = 已收表.Sum()
                 * 应收利润(应收-支出) = 应收金额 - 收款退还 - 当前总支出
                 * 已收利润(已收-支出) = 已收金额 - 收款退还 - 当前总支出
                 * 
                 */
                decimal _totalExpenditure = 0.00M; //总支出
                decimal _amountReceivable = 0.00M; //应收金额
                decimal _amountReceived = 0.00M;   //已收金额
                decimal _receivableProfit = 0.00M; //应收利润
                decimal _receivedProfit = 0.00M;   //已收利润
                _totalExpenditure = HotelCNYTotalPrice + CTGGRCNYTotalPrice + AirCNYTotalPrice + VisaCNYTotalPirce + InvitationalCNYTotalPrice +
                                    InsuranceCNYTotalPrice + DecreaseCNYTotalPrice + exTotalAmount;
                _amountReceivable = frTotalAmount;
                _amountReceived = prTotalAmount;
                _receivableProfit = _amountReceivable - promTotalAmount - _totalExpenditure;
                _receivedProfit = _amountReceived - promTotalAmount - _totalExpenditure;

                var groupInfo = groupInfos.Find(it => it.Id == _diId);
                corporateProfits.Add(new CorporateProfit()
                {
                    DiId = _diId,
                    TeamName = groupInfo?.TeamName ?? "Unkwnon",
                    CreateDt = Convert.ToDateTime(groupInfo?.CreateTime),
                    TotalExpenditure = _totalExpenditure,
                    ExpenditureItem = expenditureInfos,
                    AmountReceivable = _amountReceivable,
                    AmountReceived = _amountReceived,
                    ReceivableProfit = _receivableProfit,
                    ReceivedProfit = _receivedProfit,
                });

            }

            #endregion

            return corporateProfits;
        }

        //未来预测-地区接团/出团量
        //未来预测-地区酒店预订量
        //未来预测-地区机票预订量
        //未来预测-地区车辆预订量

        /// <summary>
        ///  (国家/城市)地区预订数量(团，酒店，机票，车辆)
        ///  Details
        ///  待添加权限验证 
        /// </summary>
        /// <param name="_dto">团组列表请求dto</param>
        /// <returns></returns>
        [HttpPost("PostRegionalBookingsNumber")]
        //[JsonConverter(typeof(DecimalConverter), 2)]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> PostRegionalBookingsNumber(PostRegionalBookingsNumberDto _dto)
        {
            #region  参数验证
            if (_dto.UserId < 1) return Ok(JsonView(false, "员工Id为空"));
            //if (_dto.PageId < 1) _dto.PageId = 38; //团组报表页面Id
            if (_dto.Type > 1 && _dto.Type > 5) return Ok(JsonView(false, "请输入有效的Type参数，1 团 2 酒店 3 机票 4 车辆"));
            if (_dto.Year < 1) return Ok(JsonView(false, "请输入有效的Year参数!"));

            PageFunAuthViewBase pageFunAuthView = new PageFunAuthViewBase();

            #region 页面操作权限验证
            //pageFunAuthView = await GeneralMethod.PostUserPageFuncDatas(_dto.UserId, _dto.PageId);

            //if (pageFunAuthView.CheckAuth == 0) return Ok(JsonView(false, "您没有查看权限"));
            #endregion

            string sqlWhere = string.Empty;

            //起止时间
            DateTime beginDt = Convert.ToDateTime($"{_dto.Year}-01-01 00:00:00");
            DateTime endDt = Convert.ToDateTime($"{_dto.Year}-12-31 23:59:59");

            sqlWhere = string.Format(@$" Where Isdel = 0 ");



            string sql = string.Format(@$"Select * From Grp_DelegationInfo {sqlWhere}");

            var groupInfos = await _sqlSugar.SqlQueryable<Grp_DelegationInfo>(sql).Where(it => it.CreateTime >= beginDt && it.CreateTime <= endDt).ToListAsync();

            if (groupInfos.Count < 1) return Ok(JsonView(false, "暂无相关团组!"));

            List<GroupTypeNumberInfo> datas = new List<GroupTypeNumberInfo>();
            foreach (var item in groupInfos)
            {
                var data = await GroupBookingsNumber(_dto.Type, item);

                if (data.TypeItem.Count > 0)
                {
                    datas.Add(data);
                }

            }

            //类型处理
            if (_dto.Type == 1)//接团
            {
                List<GroupBookingNumberView> views = new List<GroupBookingNumberView>();
                dynamic groupData = null;
                foreach (var item in datas)
                {
                    if (item.TypeItem.Count > 0)
                    {
                        foreach (var item1 in item.TypeItem)
                        {
                            if (item1.RegionItem.Count > 0)
                            {
                                GroupInfo groupInfo = new GroupInfo()
                                {
                                    DiId = item.DiId,
                                    TeamName = item.GroupName,
                                    CreateTime = groupInfos.Find(it => it.Id == item.DiId)?.CreateTime.ToString("yyyy-MM-dd HH:mm:ss") ?? "Unknown",
                                    ClientUnit = groupInfos.Find(it => it.Id == item.DiId)?.ClientUnit ?? "Unknown",
                                    Principal = groupInfos.Find(it => it.Id == item.DiId)?.ClientName ?? "Unknown",
                                };
                                views.Add(new GroupBookingNumberView() { Name = item1.RegionItem[0].Name, Number = item1.RegionItem[0].Number, GroupItem = new List<GroupInfo>() { groupInfo } });
                            }
                        }
                    }
                }

                var viewsGroup = views.GroupBy(it => it.Name);
                List<GroupBookingNumberView> _view = new List<GroupBookingNumberView>();
                foreach (var item in viewsGroup)
                {
                    List<GroupInfo> infos = new List<GroupInfo>();
                    foreach (var item1 in item)
                    {
                        infos.AddRange(item1.GroupItem);
                    }
                    infos = infos.OrderByDescending(it => it.CreateTime).ToList(); //

                    _view.Add(new GroupBookingNumberView() { Name = item.Key, Number = item.Count(), GroupItem = infos });
                }

                _view = _view.OrderByDescending(it => it.Number).Take(10).ToList();

                return Ok(JsonView(true, "操作成功!", _view, _view.Count));

            }
            else if (_dto.Type == 2)
            {
                List<HotelBookingNumberView> views = new List<HotelBookingNumberView>();

                foreach (var item in datas)
                {
                    if (item.TypeItem.Count > 0)
                    {
                        foreach (var item1 in item.TypeItem)
                        {
                            if (item1.RegionItem.Count > 0)
                            {
                                foreach (var item2 in item1.RegionItem)
                                {
                                    GroupInfo groupInfo = new GroupInfo()
                                    {
                                        DiId = item.DiId,
                                        TeamName = item.GroupName,
                                        CreateTime = groupInfos.Find(it => it.Id == item.DiId)?.CreateTime.ToString("yyyy-MM-dd HH:mm:ss") ?? "Unknown",
                                        ClientUnit = groupInfos.Find(it => it.Id == item.DiId)?.ClientUnit ?? "Unknown",
                                        Principal = groupInfos.Find(it => it.Id == item.DiId)?.ClientName ?? "Unknown",
                                    };

                                    List<HotelInfo> hotels = new List<HotelInfo>();
                                    foreach (var item3 in item2.Data)
                                    {

                                        StatisticsHotelInfo statisticsHotelInfos = JsonConvert.DeserializeObject<StatisticsHotelInfo>(JsonConvert.SerializeObject(item3));

                                        HotelInfo hotelInfo = new HotelInfo()
                                        {
                                            HotelName = statisticsHotelInfos.HotelName,
                                            SingleRoomNum = statisticsHotelInfos.SingleRoomNum,
                                            DoubleRoomNum = statisticsHotelInfos.DoubleRoomNum,
                                            SuiteRoomNum = statisticsHotelInfos.SuiteRoomNum,
                                            OtherRoomNum = statisticsHotelInfos.OtherRoomNum,
                                            GroupInfo = groupInfo
                                        };
                                        hotels.Add(hotelInfo);
                                    }

                                    views.Add(new HotelBookingNumberView() { Name = item1.RegionItem[0].Name, Number = item1.RegionItem[0].Number, HotelItem = hotels });
                                }
                            }
                        }
                    }
                }

                var viewsGroup = views.GroupBy(it => it.Name);
                List<HotelBookingNumberView> _view = new List<HotelBookingNumberView>();

                foreach (var item in viewsGroup)
                {
                    List<HotelInfo> infos = new List<HotelInfo>();
                    foreach (var item1 in item)
                    {
                        infos.AddRange(item1.HotelItem);
                    }
                    _view.Add(new HotelBookingNumberView() { Name = item.Key, Number = item.Count(), HotelItem = infos });
                }

                _view = _view.OrderByDescending(it => it.Number).Take(10).ToList();

                return Ok(JsonView(true, "操作成功!", views, views.Count));
            }

            return Ok(JsonView(false, "操作失败!"));
            #endregion

        }

        /// <summary>
        /// 计算团组ALLType预订数量
        /// </summary>
        /// <param name="diIds"></param>
        /// <returns></returns>
        private async Task<GroupTypeNumberInfo> GroupBookingsNumber(int type, Grp_DelegationInfo info)
        {
            GroupTypeNumberInfo _view = new GroupTypeNumberInfo();
            if (info == null)
            {
                return _view;
            }

            _view.DiId = info.Id;
            _view.GroupName = info.TeamName;
            List<TypeInfo> _types = new List<TypeInfo>();
            #region 计算团组ALLType预订数量

            if (type == 1)
            {
                //接团 客户集团所在地区
                string group_region = string.Empty;
                int group_number = 0;
                if (!string.IsNullOrEmpty(info.ClientUnit))
                {
                    var _NewClientData = await _sqlSugar.Queryable<Crm_NewClientData>()
                        .Where(it => it.IsDel == 0 && it.Client.Equals(AesEncryptionHelper.Encrypt(info.ClientUnit)))
                        .FirstAsync();
                    if (_NewClientData != null)
                    {
                        var regionInfo = await _sqlSugar.Queryable<Sys_SetData>().Where(it => it.Id == _NewClientData.Lvlid).FirstAsync();
                        if (regionInfo != null)
                        {
                            group_region = regionInfo.Name.Replace("级", "");
                            group_number++;
                        }
                    }
                }
                if (group_number > 0)
                {
                    _types.Add(new TypeInfo() { Id = 1, RegionItem = new List<RegionInfo>() { new RegionInfo() { Name = group_region, Number = group_number } } });
                }
            }
            else if (type == 2)
            {
                //酒店
                var hotelInfos = await _sqlSugar.Queryable<Grp_HotelReservations>().Where(it => it.IsDel == 0 && it.DiId == info.Id).ToListAsync();

                if (hotelInfos.Count > 0)
                {
                    List<RegionInfo> hotelRegions = new List<RegionInfo>();
                    foreach (var item in hotelInfos)
                    {
                        var hotelNumberInfo = new StatisticsHotelInfo()
                        {
                            HotelName = item.HotelName,
                            SingleRoomNum = item.SingleRoomCount,
                            DoubleRoomNum = item.DoubleRoomCount,
                            SuiteRoomNum = item.SuiteRoomCount,
                            OtherRoomNum = item.OtherRoomCount,
                        };
                        int hotelRoomTotal = item.SingleRoomCount + item.DoubleRoomCount + item.SuiteRoomCount + item.OtherRoomCount;
                        if (hotelRegions.Select(it => it.Name).ToList().Contains(item.City))
                        {
                            RegionInfo hotelRegion = hotelRegions.Find(it => it.Name.Equals(item.City));
                            if (hotelRegion != null)
                            {
                                hotelRegions.Remove(hotelRegion);
                                if (hotelRegion.Data.Count > 0)
                                {
                                    hotelRegion.Data.Add(hotelNumberInfo);
                                }

                                hotelRegion.Number += hotelRoomTotal;
                                hotelRegions.Add(hotelRegion);
                            }
                        }
                        else
                        {
                            hotelRegions.Add(new RegionInfo() { Name = item.City, Number = hotelRoomTotal, Data = new List<dynamic>() { hotelNumberInfo } });
                        }
                    }
                    _types.Add(new TypeInfo() { Id = 2, RegionItem = hotelRegions });
                }
            }
            else if (type == 3)
            {
                //机票
                var airTicketInfos = await _sqlSugar.Queryable<Grp_AirTicketReservations>()
                                                    .LeftJoin<Grp_CreditCardPayment>((atr, ccp) => atr.Id == ccp.CId && ccp.IsPay == 1)
                                                    .LeftJoin<Sys_SetData>((atr, ccp, sd) => atr.CType == sd.Id)
                                                    .Where((atr, ccp, sd) => atr.IsDel == 0 && atr.DIId == info.Id)
                                                    .Select((atr, ccp, sd) => new { atr.ClientNum, atr.CType, ccp.Payee, AirType = sd.Name })
                                                    .ToListAsync();

                if (airTicketInfos.Count > 0)
                {
                    List<RegionInfo> airTicketRegions = new List<RegionInfo>();
                    foreach (var item in airTicketInfos)
                    {
                        var ticketClass = new
                        {
                            TiketClass = item.AirType,
                            Number = item.ClientNum
                        };

                        if (airTicketRegions.Select(it => it.Name).ToList().Contains(item.Payee))
                        {
                            RegionInfo airTicketRegion = airTicketRegions.Find(it => it.Name.Equals(item.Payee));
                            if (airTicketRegion != null)
                            {
                                airTicketRegions.Remove(airTicketRegion);
                                if (airTicketRegion.Data.Count > 0)
                                {
                                    airTicketRegion.Data.Add(ticketClass);
                                }

                                airTicketRegion.Number += item.ClientNum;
                                airTicketRegions.Add(airTicketRegion);
                            }
                        }
                        else
                        {
                            airTicketRegions.Add(new RegionInfo() { Name = item.Payee, Number = item.ClientNum, Data = new List<dynamic>() { ticketClass } });
                        }
                    }

                    _types.Add(new TypeInfo() { Id = 3, RegionItem = airTicketRegions });
                }
            }
            else if (type == 4)
            {
                //车辆
                var opInfos = await _sqlSugar.Queryable<Grp_CarTouristGuideGroundReservations>().Where(it => it.IsDel == 0 && it.DiId == info.Id).ToListAsync();
                var opContentInfos = await _sqlSugar.Queryable<Grp_CarTouristGuideGroundReservationsContent>().Where(it => it.IsDel == 0 && it.DiId == info.Id).ToListAsync();
                if (opInfos.Count > 0)
                {
                    List<RegionInfo> opRegions = new List<RegionInfo>();

                    foreach (var item in opInfos)
                    {
                        int carNum = 0;
                        var opContentInfo = opContentInfos.Where(it => it.CTGGRId == item.Id && it.SId == 91).ToList();

                        if (opContentInfo.Count > 0)
                        {
                            foreach (var item1 in opContentInfo)
                            {
                                if (item1.Price > 0 && item1.Count > 0)
                                {
                                    carNum += item1.Count;
                                }
                            }
                        }
                        if (carNum > 0)
                        {
                            var opData = new
                            {
                                ServiceCompany = item.ServiceCompany,
                                BusName = item.BusName,
                                Numbuer = carNum
                            };
                            if (opRegions.Select(it => it.Name).ToList().Contains(item.Area))
                            {
                                RegionInfo opRegion = opRegions.Find(it => it.Name.Equals(item.Area));
                                if (opRegion != null)
                                {
                                    opRegions.Remove(opRegion);
                                    if (opRegion.Data.Count > 0)
                                    {
                                        opRegion.Data.Add(opData);
                                    }

                                    opRegion.Number += carNum;
                                    opRegions.Add(opRegion);
                                }

                            }
                            else
                            {
                                opRegions.Add(new RegionInfo() { Name = item.Area, Number = carNum, Data = new List<dynamic>() { opData } });
                            }
                        }

                    }
                    _types.Add(new TypeInfo() { Id = 4, RegionItem = opRegions });
                }
            }

            #endregion

            _view.TypeItem = _types;

            return _view;
        }

        #endregion

        #region 市场部销售额

        /// <summary>
        ///  市场部销售额
        /// Init 基础数据(公司/人员/年份/季度/月份)
        /// </summary>
        /// <param name="_dto">市场部销售额请求dto</param>
        /// <returns></returns>
        [HttpPost("PostMarketingSalesInitData")]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> PostMarketingSalesInitData(MarketingSalesInitDataDto _dto)
        {
            #region  参数验证
            MarketingSalesInitDataDtoFoalidator validationRules = new MarketingSalesInitDataDtoFoalidator();
            var validResult = await validationRules.ValidateAsync(_dto);
            if (!validResult.IsValid)
            {
                var errors = new StringBuilder();
                foreach (var error in validResult.Errors) errors.AppendLine(error.ErrorMessage);
                return Ok(JsonView(false, errors.ToString()));
            }


            PageFunAuthViewBase pageFunAuthView = new PageFunAuthViewBase();

            #region 页面操作权限验证
            pageFunAuthView = await GeneralMethod.PostUserPageFuncDatas(_dto.UserId, _dto.PageId);

            if (pageFunAuthView.CheckAuth == 0) return Ok(JsonView(false, MsgTips.CheckAuth));
            #endregion


            var companyData = _sqlSugar.Queryable<Sys_Company>().Where(it => it.IsDel == 0)
                                       .Select(it => new { id = it.Id, name = it.CompanyName })
                                       .ToList();
            List<int> companyIds = companyData.Select(it => it.id).ToList();

            List<int> pickGroupUserIds = _sqlSugar.Queryable<Grp_DelegationInfo>().Where(it => it.IsDel == 0)
                                                  .Select(it => it.JietuanOperator)
                                                  .ToList();

            var userData = _sqlSugar.Queryable<Sys_Users>()
                                    .Where(it => it.IsDel == 0 && (pickGroupUserIds.Contains(it.Id) || it.Id == 21))
                                    .Select(it => new { id = it.Id, companyId = it.CompanyId, name = it.CnName })
                                    .ToList();

            companyData.Insert(0, new { id = -1, name = "全部" });
            userData.Insert(0, new { id = -1, companyId = -1, name = "全部" });

            #region 年份

            var dtData = new List<dynamic>();
            int dt = DateTime.Now.Year;
            for (int y = dt; y >= dt - 4; y--)
            {
                //季度
                var quarterDatas = new List<dynamic>();
                quarterDatas.Add(new { name = "全部", beginDt = $"{y}-01-01", endDt = $"{y}-12-31" });
                for (int q = 0; q < 4; q++)
                {
                    dynamic quarterData = null;
                    if (q == 0)
                    {
                        var monthDatas = new List<dynamic>();
                        monthDatas.Add(new { name = $"全部", beginDt = $"{y}-01-01", endDt = $" {y}-03-31" });
                        for (int m = 1; m < 4; m++)
                        {
                            MonthlyTimeSegment timeSegment = new MonthlyTimeSegment(y, m);
                            monthDatas.Add(new { name = $"{ConvertToChinese(m)}", beginDt = timeSegment.Start.ToString("yyyy-MM-dd"), endDt = timeSegment.End.ToString("yyyy-MM-dd") });
                        }
                        //quarterData = new { name = "第一季度", beginDt = $"{y}-01-01", endDt = $" {y}-03-31", monthData = monthDatas };
                        quarterData = new { name = "第一季度", monthData = monthDatas };
                    }
                    else if (q == 1)
                    {
                        var monthDatas = new List<dynamic>();
                        monthDatas.Add(new { name = $"全部", beginDt = $"{y}-04-01", endDt = $"{y}-06-30" });
                        for (int m = 4; m < 7; m++)
                        {
                            MonthlyTimeSegment timeSegment = new MonthlyTimeSegment(y, m);
                            monthDatas.Add(new { name = $"{ConvertToChinese(m)}", beginDt = timeSegment.Start.ToString("yyyy-MM-dd"), endDt = timeSegment.End.ToString("yyyy-MM-dd") });
                        }

                        //quarterData = new { name = "第二季度", beginDt = $"{y}-04-01", endDt = $"{y}-06-30", monthData = monthDatas };
                        quarterData = new { name = "第二季度", monthData = monthDatas };
                    }
                    else if (q == 2)
                    {
                        var monthDatas = new List<dynamic>();
                        monthDatas.Add(new { name = $"全部", beginDt = $"{y}-07-01", endDt = $"{y}-09-30" });
                        for (int m = 7; m < 10; m++)
                        {
                            MonthlyTimeSegment timeSegment = new MonthlyTimeSegment(y, m);
                            monthDatas.Add(new { name = $"{ConvertToChinese(m)}", beginDt = timeSegment.Start.ToString("yyyy-MM-dd"), endDt = timeSegment.End.ToString("yyyy-MM-dd") });
                        }
                        //quarterData = new { name = "第三季度", beginDt = $"{y}-07-01", endDt = $"{y}-09-30", monthData = monthDatas };
                        quarterData = new { name = "第三季度", monthData = monthDatas };
                    }
                    else if (q == 3)
                    {
                        var monthDatas = new List<dynamic>();
                        monthDatas.Add(new { name = $"全部", beginDt = $"{y}-10-01", endDt = $"{y}-12-31" });
                        for (int m = 10; m < 13; m++)
                        {
                            MonthlyTimeSegment timeSegment = new MonthlyTimeSegment(y, m);
                            monthDatas.Add(new { name = $"{ConvertToChinese(m)}", beginDt = timeSegment.Start.ToString("yyyy-MM-dd"), endDt = timeSegment.End.ToString("yyyy-MM-dd") });
                        }
                        //quarterData = new { name = "第四季度", beginDt = $"{y}-10-01", endDt = $"{y}-12-31", monthData = monthDatas }; 
                        quarterData = new { name = "第四季度", monthData = monthDatas };
                    }
                    quarterDatas.Add(quarterData);
                }

                dtData.Add(new
                {
                    year = y,
                    //yearData = new { beginDt = $"{y}-01-01", endDt = $"{y}-12-31" },
                    quarterData = quarterDatas,
                });
            }

            #endregion

            return Ok(JsonView(true, "操作成功!", new { companyData = companyData, userData = userData, dtData = dtData }));
            #endregion

        }

        private static string ConvertToChinese(int month)
        {
            if (month < 1 || month > 12)
                throw new ArgumentOutOfRangeException(nameof(month), "月份必须在1到12之间。");

            var cultureInfo = new System.Globalization.CultureInfo("zh-CN");
            var dateTimeFormat = cultureInfo.DateTimeFormat;
            return dateTimeFormat.GetMonthName(month);
        }

        /// <summary>
        ///  市场部销售额
        ///  年度/季度/月度 报表(同比)
        /// </summary>
        /// <param name="_dto">市场部销售额请求dto</param>
        /// <returns></returns>
        [HttpPost("PostMarketingSalesStatistics")]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> PostMarketingSalesStatistics_Year(MarketingSalesStatisticsDto _dto)
        {
            #region  参数验证

            MarketingSalesStatisticsDtoFoalidator validationRules = new MarketingSalesStatisticsDtoFoalidator();
            var validResult = await validationRules.ValidateAsync(_dto);
            if (!validResult.IsValid)
            {
                var errors = new StringBuilder();
                foreach (var error in validResult.Errors) errors.AppendLine(error.ErrorMessage);
                return Ok(JsonView(false, errors.ToString()));
            }

            PageFunAuthViewBase pageFunAuthView = new PageFunAuthViewBase();

            #region 页面操作权限验证
            pageFunAuthView = await GeneralMethod.PostUserPageFuncDatas(_dto.UserId, _dto.PageId);

            if (pageFunAuthView.CheckAuth == 0) return Ok(JsonView(false, MsgTips.CheckAuth));
            #endregion

            #endregion

            string beginDt = $"{_dto.BeginDt} 00:00:00", endDt = $"{_dto.EndDt} 23:59:59";

            return Ok(JsonView(true, "操作成功!", await GroupSales(_dto.CompanyId, _dto.GroupPickupUserId, beginDt, endDt)));
        }

        private async Task<List<int>> GetUserIds(int companyId, int groupPickupUserId)
        {
            //全部人员Id
            //查询所有公司的市场部
            var pickGroupIds = _sqlSugar.Queryable<Grp_DelegationInfo>().Where(it => it.IsDel == 0).Select(it => it.JietuanOperator).Distinct().ToList();
            var userIds = new List<int>();
            var userDatas = await _sqlSugar.Queryable<Sys_Users>()
                                           .InnerJoin<Sys_Company>((u, c) => u.CompanyId == c.Id)
                                           .Where((u, c) => u.IsDel == 0 && pickGroupIds.Contains(u.Id))
                                           .Select((u, c) => new { u.Id, u.CompanyId, u.CnName })
                                           .ToListAsync();

            userIds = userDatas.Select(it => it.Id).ToList();
            if (companyId > 0)
            {
                userIds = userDatas.Where(it => it.CompanyId == companyId).Select(it => it.Id).ToList();
            }
            if (groupPickupUserId > 0)
            {
                userIds = userDatas.Where(it => it.Id == groupPickupUserId).Select(it => it.Id).ToList();
            }

            return userIds;
        }

        /// <summary>
        /// 计算团组销售额
        /// </summary>
        /// <param name="companyId"></param>
        /// <param name="groupPickupUserId"></param>
        /// <param name="beginDt"></param>
        /// <param name="endDt"></param>
        /// <returns></returns>
        private async Task<SalesYOYView> GroupSales(int companyId, int groupPickupUserId, string beginDt, string endDt)
        {
            decimal thisSales = 0.00M, lastSales = 0.00M, yoy = 1.00M;

            var _view = new SalesYOYView();
            List<int> userIds = new List<int>();
            userIds = await GetUserIds(companyId, groupPickupUserId);
            string userSqlWhere = "";
            if (userIds.Count > 0)
            {
                userSqlWhere = string.Format($" And Id IN ({string.Join(',', userIds)})");
            }
            else
            {
                _view = new SalesYOYView
                {
                    thisYearSales = "0.00",
                    lastYearSales = "0.00",
                };
                return _view;
            }

            string lastBeginDt = Convert.ToDateTime(beginDt).AddYears(-1).ToString("yyyy-MM-dd HH:mm:ss"),
                   lastEndDt = Convert.ToDateTime(endDt).AddYears(-1).ToString("yyyy-MM-dd HH:mm:ss");
            //            string salesSql = string.Format(@"
            //SELECT
            //	'ThisSales' As [Name],
            //	CAST(SUM(Sales) AS decimal(12,2)) As Sales
            //FROM
            //	(
            //	SELECT di.VisitDate,
            //		(SELECT  CAST(SUM(ItemSumPrice * Rate) AS decimal(12,2)) FROM Fin_ForeignReceivables
            //		WHERE IsDel = 0 AND AddingWay IN (0, 1, 2)AND di.Id = Diid
            //		) AS Sales
            //	FROM
            //		Grp_DelegationInfo di
            //	WHERE di.IsDel = 0 AND di.IsSure = 1
            //		AND di.JietuanOperator IN (SELECT Id FROM Sys_Users WITH (NoLock) WHERE IsDel = 0  {0})
            //		AND di.VisitDate BETWEEN '{1}' AND '{2}'
            //	) temp
            //Union ALL
            //SELECT
            //	'LastSales' As [Name],
            //	CAST(SUM(Sales) AS decimal(12,2)) As Sales
            //FROM
            //	(
            //	SELECT di.VisitDate,
            //		(SELECT  CAST(SUM(ItemSumPrice * Rate) AS decimal(12,2)) FROM Fin_ForeignReceivables
            //		WHERE IsDel = 0 AND AddingWay IN (0, 1, 2)AND di.Id = Diid
            //		) AS Sales
            //	FROM
            //		Grp_DelegationInfo di
            //	WHERE di.IsDel = 0 AND di.IsSure = 1
            //		AND di.JietuanOperator IN (SELECT Id FROM Sys_Users WITH (NoLock) WHERE IsDel = 0 {3})
            //		AND di.VisitDate BETWEEN '{4}' AND '{5}'
            //	) temp", userSqlWhere, beginDt, endDt, userSqlWhere, lastBeginDt, lastEndDt);

            string salesSql = string.Format(@"
SELECT
	'ThisSales' As [Name],
	CAST(SUM(Sales) AS decimal(12,2)) As Sales
FROM
	(
	SELECT di.VisitDate,
		(SELECT  CAST(SUM(ItemSumPrice * Rate) AS decimal(12,2)) FROM Fin_ForeignReceivables
		WHERE IsDel = 0 AND AddingWay IN (0, 1, 2)AND di.Id = Diid
		) AS Sales
	FROM
		Grp_DelegationInfo di
	WHERE di.IsDel = 0 
		AND di.JietuanOperator IN (SELECT Id FROM Sys_Users WITH (NoLock) WHERE IsDel = 0  {0})
		AND di.VisitDate BETWEEN '{1}' AND '{2}'
	) temp
Union ALL
SELECT
	'LastSales' As [Name],
	CAST(SUM(Sales) AS decimal(12,2)) As Sales
FROM
	(
	SELECT di.VisitDate,
		(SELECT  CAST(SUM(ItemSumPrice * Rate) AS decimal(12,2)) FROM Fin_ForeignReceivables
		WHERE IsDel = 0 AND AddingWay IN (0, 1, 2)AND di.Id = Diid
		) AS Sales
	FROM
		Grp_DelegationInfo di
	WHERE di.IsDel = 0 
		AND di.JietuanOperator IN (SELECT Id FROM Sys_Users WITH (NoLock) WHERE IsDel = 0 {3})
        AND di.IsBid = 0 
        AND di.TeamName Not Like '%投标%'
		AND di.VisitDate BETWEEN '{4}' AND '{5}'
	) temp", userSqlWhere, beginDt, endDt, userSqlWhere, lastBeginDt, lastEndDt);
            var salesData = await _sqlSugar.SqlQueryable<SalesView>(salesSql).ToListAsync();

            thisSales = salesData.Where(x => x.Name.Equals("ThisSales")).First()?.Sales ?? 0;
            lastSales = salesData.Where(x => x.Name.Equals("LastSales")).First()?.Sales ?? 0;

            if (lastSales != 0 && thisSales != 0) yoy = (thisSales - lastSales) / lastSales;

            return new SalesYOYView()
            {
                thisYearSales = thisSales.ToString("#0.00"),
                lastYearSales = lastSales.ToString("#0.00"),
                yoy = yoy.ToString("#0.00")
            };
        }

        private class SalesView
        {
            public string Name { get; set; }
            public decimal Sales { get; set; }
        }

        private class SalesYOYView
        {
            public string thisYearSales { get; set; }
            public string lastYearSales { get; set; }
            public string yoy { get; set; } = "1.00";
        }

        /// <summary>
        ///  市场部销售额
        ///  团组列表
        /// </summary>
        /// <param name="_dto">市场部销售额请求dto</param>
        /// <returns></returns>
        [HttpPost("PostMarketingSalesGroupList")]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> PostMarketingSalesGroupList(MarketingSalesGroupListDto _dto)
        {
            #region  参数验证

            MarketingSalesStatisticsDtoFoalidator validationRules = new MarketingSalesStatisticsDtoFoalidator();
            var validResult = await validationRules.ValidateAsync(_dto);
            if (!validResult.IsValid)
            {
                var errors = new StringBuilder();
                foreach (var error in validResult.Errors) errors.AppendLine(error.ErrorMessage);
                return Ok(JsonView(false, errors.ToString()));
            }

            PageFunAuthViewBase pageFunAuthView = new PageFunAuthViewBase();

            #region 页面操作权限验证
            pageFunAuthView = await GeneralMethod.PostUserPageFuncDatas(_dto.UserId, _dto.PageId);

            if (pageFunAuthView.CheckAuth == 0) return Ok(JsonView(false, MsgTips.CheckAuth));
            #endregion

            #endregion

            string userSql = "";
            List<int> userIds = new List<int>();
            userIds = await GetUserIds(_dto.CompanyId, _dto.GroupPickupUserId);

            if (userIds.Count <= 0)
            {
                return Ok(JsonView(true, "操作成功!", new List<object> { }, 0));
            }
            else userSql = @$" And di.JietuanOperator In ({string.Join(",", userIds)})";


            if (!string.IsNullOrEmpty(_dto.SearchCriteria))
            {
                userSql += string.Format(@$" AND di.TeamName Like '%{_dto.SearchCriteria}%'");
            }


            string beginDt = $"{_dto.BeginDt} 00:00:00", endDt = $"{_dto.EndDt} 23:59:59";

            //            string sql = string.Format(@$"SELECT
            //  ROW_NUMBER() OVER (
            //    ORDER BY
            //      CollectionDays
            //  ) AS RowNumber,
            //  *
            //FROM
            //  (
            //    SELECT
            //      di.Id,
            //      di.TeamName,
            //      di.ClientUnit,
            //      di.ClientName,
            //      di.VisitDate,
            //      di.VisitPNumber,
            //      di.JietuanOperator,
            //      di.VisitEndDate,
            //      (
            //        SELECT
            //          CAST(
            //            COALESCE(SUM(ItemSumPrice * Rate), 0) AS DECIMAL(12, 2)
            //          ) AS GroupSales
            //        FROM
            //          Fin_ForeignReceivables
            //        WHERE
            //          IsDel = 0
            //          AND di.Id = Diid
            //          AND AddingWay IN (0, 1, 2)
            //      ) AS GroupSales,
            //      u.CnName AS GroupPickupUser,
            //      DATEADD(DAY, 7, di.VisitEndDate) AS CollectionDays
            //    FROM
            //      Grp_DelegationInfo di
            //    WITH
            //      (NoLock)
            //      LEFT JOIN Sys_Users u ON di.JietuanOperator = u.Id
            //    WHERE
            //      di.Isdel = 0
            //	  AND di.IsSure = 1 {userSql} 
            //      AND VisitDate Between '{beginDt}' And  '{endDt}'
            //  ) Temp ");

            string sql = string.Format(@$"SELECT
  ROW_NUMBER() OVER (
    ORDER BY
      CollectionDays
  ) AS RowNumber,
  *
FROM
  (
    SELECT
      di.Id,
      di.TeamName,
      di.ClientUnit,
      di.ClientName,
      di.VisitDate,
      di.VisitPNumber,
      di.JietuanOperator,
      di.VisitEndDate,
      (
        SELECT
          CAST(
            COALESCE(SUM(ItemSumPrice * Rate), 0) AS DECIMAL(12, 2)
          ) AS GroupSales
        FROM
          Fin_ForeignReceivables
        WHERE
          IsDel = 0
          AND di.Id = Diid
          AND AddingWay IN (0, 1, 2)
      ) AS GroupSales,
      u.CnName AS GroupPickupUser,
      DATEADD(DAY, 7, di.VisitEndDate) AS CollectionDays
    FROM
      Grp_DelegationInfo di
    WITH
      (NoLock)
      LEFT JOIN Sys_Users u ON di.JietuanOperator = u.Id
    WHERE
      di.Isdel = 0
      AND  (
        SELECT
          CAST(
            COALESCE(SUM(ItemSumPrice * Rate), 0) AS DECIMAL(12, 2)
          ) AS GroupSales
        FROM
          Fin_ForeignReceivables
        WHERE
          IsDel = 0
          AND di.Id = Diid
          AND AddingWay IN (0, 1, 2)
      ) > 0
	   {userSql}  
      AND di.IsBid = 0 
      AND di.TeamName Not Like '%投标%'
      AND VisitDate Between '{beginDt}' And  '{endDt}'
  ) Temp ");

            RefAsync<int> total = 0;
            var groupData = await _sqlSugar.SqlQueryable<MarketingSalesGroupList>(sql).ToPageListAsync(_dto.PageIndex, _dto.PageSize, total);

            return Ok(JsonView(true, "操作成功!", groupData, total));
        }

        /// <summary>
        ///  市场部销售额
        ///  团组列表Excel下载
        /// </summary>
        /// <param name="_dto">市场部销售额请求dto</param>
        /// <returns></returns>
        [HttpPost("PostMarketingSalesGroupExcel")]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> PostMarketingSalesGroupExcel(MarketingSalesGroupListDto _dto)
        {
            #region  参数验证

            MarketingSalesStatisticsDtoFoalidator validationRules = new MarketingSalesStatisticsDtoFoalidator();
            var validResult = await validationRules.ValidateAsync(_dto);
            if (!validResult.IsValid)
            {
                var errors = new StringBuilder();
                foreach (var error in validResult.Errors) errors.AppendLine(error.ErrorMessage);
                return Ok(JsonView(false, errors.ToString()));
            }

            PageFunAuthViewBase pageFunAuthView = new PageFunAuthViewBase();

            #region 页面操作权限验证
            pageFunAuthView = await GeneralMethod.PostUserPageFuncDatas(_dto.UserId, _dto.PageId);

            if (pageFunAuthView.CheckAuth == 0) return Ok(JsonView(false, MsgTips.CheckAuth));
            #endregion

            #endregion

            string userSql = "";
            var userIds = new List<int>();
            userIds = await GetUserIds(_dto.CompanyId, _dto.GroupPickupUserId);

            if (userIds.Count <= 0)
            {
                return Ok(JsonView(false));
            }
            else userSql = @$" And di.JietuanOperator In ({string.Join(",", userIds)})";


            if (!string.IsNullOrEmpty(_dto.SearchCriteria))
            {
                userSql += string.Format(@$" AND di.TeamName Like '%{_dto.SearchCriteria}%'");
            }

            string beginDt = $"{_dto.BeginDt} 00:00:00",
                   endDt = $"{_dto.EndDt} 23:59:59";

            string sql = string.Format(@$"SELECT
  ROW_NUMBER() OVER (
    ORDER BY
      CollectionDays
  ) AS RowNumber,
  *
FROM
  (
    SELECT
      di.Id,
      di.TeamName,
      di.ClientUnit,
      di.ClientName,
      di.VisitDate,
      di.VisitPNumber,
      di.JietuanOperator,
      di.VisitEndDate,
      (
        SELECT
          CAST(
            COALESCE(SUM(ItemSumPrice * Rate), 0) AS DECIMAL(12, 2)
          ) AS GroupSales
        FROM
          Fin_ForeignReceivables
        WHERE
          IsDel = 0
          AND di.Id = Diid
          AND AddingWay IN (0, 1, 2)
      ) AS GroupSales,
      u.CnName AS GroupPickupUser,
      DATEADD(DAY, 7, di.VisitEndDate) AS CollectionDays
    FROM
      Grp_DelegationInfo di
    WITH
      (NoLock)
      LEFT JOIN Sys_Users u ON di.JietuanOperator = u.Id
    WHERE
      di.Isdel = 0
      AND  (
        SELECT
          CAST(
            COALESCE(SUM(ItemSumPrice * Rate), 0) AS DECIMAL(12, 2)
          ) AS GroupSales
        FROM
          Fin_ForeignReceivables
        WHERE
          IsDel = 0
          AND di.Id = Diid
          AND AddingWay IN (0, 1, 2)
      ) > 0
	   {userSql}  
      AND di.IsBid = 0 
      AND di.TeamName Not Like '%投标%'
      AND VisitDate Between '{beginDt}' And  '{endDt}'
  ) Temp ");

            var view = await _sqlSugar.SqlQueryable<MarketingSalesGroupList>(sql).ToListAsync();


            if (view.Count < 1) return Ok(JsonView(false, "暂无数据!"));

            //DataTable dt = GeneralMethod.
            DataTable dt = CommonFun.GetDataTableFromIList(view);
            dt.TableName = $"_view";
            WorkbookDesigner designer = new WorkbookDesigner();
            designer.Workbook = new Workbook(AppSettingsHelper.Get("ExcelBasePath") + "Template/营业额团组报表模板.xls");
            designer.SetDataSource(dt);
            designer.SetDataSource("Total", view.Sum(x => x.GroupSales));
            designer.Workbook.Worksheets[0].Name = "营业额团组报表";

            designer.Process();

            string fileName = $"MarketingSales/{_dto.BeginDt}～{_dto.EndDt}营业额团组报表{DateTime.Now.ToString("yyyyMMddHHmmss")}.xlsx";
            designer.Workbook.Save(AppSettingsHelper.Get("ExcelBasePath") + fileName);
            string rst = AppSettingsHelper.Get("ExcelBaseUrl") + AppSettingsHelper.Get("ExcelFtpPath") + fileName;
            return Ok(JsonView(true, "操作成功", new { url = rst }));
        }

        /// <summary>
        ///  市场部销售额
        ///  客户类型、客户等级 统计
        /// </summary>
        /// <param name="_dto">市场部销售额请求dto</param>
        /// <returns></returns>
        [HttpPost("PostMarketingSalesGroupStatistics")]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> PostMarketingSalesGroupStatistics(MarketingSalesGroupStatisticsDto _dto)
        {
            #region  参数验证

            MarketingSalesStatisticsDtoFoalidator validationRules = new MarketingSalesStatisticsDtoFoalidator();
            var validResult = await validationRules.ValidateAsync(_dto);
            if (!validResult.IsValid)
            {
                var errors = new StringBuilder();
                foreach (var error in validResult.Errors) errors.AppendLine(error.ErrorMessage);
                return Ok(JsonView(false, errors.ToString()));
            }

            PageFunAuthViewBase pageFunAuthView = new PageFunAuthViewBase();

            #region 页面操作权限验证
            pageFunAuthView = await GeneralMethod.PostUserPageFuncDatas(_dto.UserId, _dto.PageId);

            if (pageFunAuthView.CheckAuth == 0) return Ok(JsonView(false, MsgTips.CheckAuth));
            #endregion

            #endregion

            string beginDt = $"{_dto.BeginDt} 00:00:00", endDt = $"{_dto.EndDt} 23:59:59";
            List<int> userIds = await GetUserIds(_dto.CompanyId, _dto.GroupPickupUserId);

            string userSql = "";
            if (userIds.Count > 0)
            {
                userSql = string.Format(@$" AND JietuanOperator IN ({string.Join(",", userIds)})");
            }
            else
            {
                if (_dto.PortType == 2 || _dto.PortType == 3)
                {
                    return Ok(JsonView(true, "操作成功!", new List<object> { }));
                }
                else
                {
                    return Ok(JsonView(true, "操作成功!", new
                    {
                        customerTypeData = new List<object> { },
                        clientGradeData = new List<object> { }
                    }));
                }
            }

            string sql = "";

            if (_dto.StatisticsType == 1)
            {
                sql = string.Format(@$"Select 
	sd.[Name],
	Count(*) As [Count]
From Grp_DelegationInfo di
Left Join Sys_SetData sd On di.TeamDid = sd.Id
Where di.Isdel = 0 
And (
  SELECT
    CAST(
      COALESCE(SUM(ItemSumPrice * Rate), 0) AS DECIMAL(12, 2)
    ) AS GroupSales
  FROM
    Fin_ForeignReceivables fr
  WHERE
    fr.IsDel = 0
    AND di.Id = fr.Diid
    AND AddingWay IN (0, 1, 2)
) > 0
 {userSql} 
    AND di.IsBid = 0 
    AND di.TeamName Not Like '%投标%'
And VisitDate Between '{beginDt}' And  '{endDt}'
Group By [Name]
Order By Count Desc");
            }
            else if (_dto.StatisticsType == 2)
            {
                sql = string.Format(@$"Select 
	sd.[Name],
	Count(*) As [Count]
From Grp_DelegationInfo di
Left Join Sys_SetData sd On di.TeamLevSId = sd.Id
Where di.Isdel = 0 
And (
  SELECT
    CAST(
      COALESCE(SUM(ItemSumPrice * Rate), 0) AS DECIMAL(12, 2)
    ) AS GroupSales
  FROM
    Fin_ForeignReceivables fr
  WHERE
    fr.IsDel = 0
    AND di.Id = fr.Diid
    AND AddingWay IN (0, 1, 2)
) > 0
 {userSql} 
    AND di.IsBid = 0 
    AND di.TeamName Not Like '%投标%'
And VisitDate Between '{beginDt}' And  '{endDt}'
Group By [Name]
Order By Count Desc");
            }
            else return Ok(JsonView(false, "StatisticsType不在可取范围!"));

            if (_dto.PortType == 1)
            {
                string sql1 = string.Format(@$"Select 
	sd.[Name],
	Count(*) As [Count]
From Grp_DelegationInfo di
Left Join Sys_SetData sd On di.TeamDid = sd.Id
Where di.Isdel = 0 
And (
  SELECT
    CAST(
      COALESCE(SUM(ItemSumPrice * Rate), 0) AS DECIMAL(12, 2)
    ) AS GroupSales
  FROM
    Fin_ForeignReceivables fr
  WHERE
    fr.IsDel = 0
    AND di.Id = fr.Diid
    AND AddingWay IN (0, 1, 2)
) > 0
 {userSql} 
AND di.IsBid = 0 
AND di.TeamName Not Like '%投标%'
And VisitDate Between '{beginDt}' And  '{endDt}'
Group By [Name]
Order By Count Desc");

                var customerTypeData = await _sqlSugar.SqlQueryable<MarketingSalesGroupStatisticsView>(sql1).ToListAsync();

                string sql2 = string.Format(@$"Select 
	sd.[Name],
	Count(*) As [Count]
From Grp_DelegationInfo di
Left Join Sys_SetData sd On di.TeamLevSId = sd.Id
Where di.Isdel = 0 
And (
  SELECT
    CAST(
      COALESCE(SUM(ItemSumPrice * Rate), 0) AS DECIMAL(12, 2)
    ) AS GroupSales
  FROM
    Fin_ForeignReceivables fr
  WHERE
    fr.IsDel = 0
    AND di.Id = fr.Diid
    AND AddingWay IN (0, 1, 2)
) > 0
 {userSql} 
AND di.IsBid = 0 
AND di.TeamName Not Like '%投标%'
And VisitDate Between '{beginDt}' And  '{endDt}'
Group By [Name]
Order By Count Desc");
                var clientGradeData = await _sqlSugar.SqlQueryable<MarketingSalesGroupStatisticsView>(sql2).ToListAsync();
                return Ok(JsonView(true, "操作成功!", new
                {
                    customerTypeData = customerTypeData,
                    clientGradeData = clientGradeData
                }));

            }
            else if (_dto.PortType == 2 || _dto.PortType == 3)
            {
                var data = await _sqlSugar.SqlQueryable<MarketingSalesGroupStatisticsView>(sql).ToListAsync();
                return Ok(JsonView(true, "操作成功!", data, data.Count));
            }
            else return Ok(JsonView(false, MsgTips.Port));
        }

        /// <summary>
        ///  市场部销售额
        ///  接单排名
        /// </summary>
        /// <param name="_dto">市场部销售额请求dto</param>
        /// <returns></returns>
        [HttpPost("PostMarketingSalesOrderRanking")]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> PostMarketingSalesOrderRanking(MarketingSalesOrderRankingDto _dto)
        {
            #region  参数验证

            MarketingSalesStatisticsDtoFoalidator validationRules = new MarketingSalesStatisticsDtoFoalidator();
            var validResult = await validationRules.ValidateAsync(_dto);
            if (!validResult.IsValid)
            {
                var errors = new StringBuilder();
                foreach (var error in validResult.Errors) errors.AppendLine(error.ErrorMessage);
                return Ok(JsonView(false, errors.ToString()));
            }

            PageFunAuthViewBase pageFunAuthView = new PageFunAuthViewBase();

            #region 页面操作权限验证
            pageFunAuthView = await GeneralMethod.PostUserPageFuncDatas(_dto.UserId, _dto.PageId);

            if (pageFunAuthView.CheckAuth == 0) return Ok(JsonView(false, MsgTips.CheckAuth));
            #endregion

            #endregion

            string beginDt = $"{_dto.BeginDt} 00:00:00", endDt = $"{_dto.EndDt} 23:59:59";

            List<int> userIds = await GetUserIds(_dto.CompanyId, _dto.GroupPickupUserId);

            string userSql = "";
            if (userIds.Count > 0)
            {
                userSql = string.Format(@$" AND JietuanOperator IN ({string.Join(",", userIds)})");
            }
            else return Ok(JsonView(true, "操作成功!", new List<object> { }, 0));

            string sql = string.Format(@$"Select 
	ROW_NUMBER() Over(Order By Count(*) Desc) As RowNumber,
	u.CnName As UserName,
	Count(*) As [Count]
	From Grp_DelegationInfo di
	Left Join Sys_Users u On di.JietuanOperator = u.Id
	Where di.Isdel = 0 
	AND (
      SELECT
        CAST(
          COALESCE(SUM(ItemSumPrice * Rate), 0) AS DECIMAL(12, 2)
        ) AS GroupSales
      FROM
        Fin_ForeignReceivables fr
      WHERE
        fr.IsDel = 0
        AND di.Id = fr.Diid
        AND AddingWay IN (0, 1, 2)
    ) > 0
     {userSql} 
    AND di.IsBid = 0 
    AND di.TeamName Not Like '%投标%'
	AND VisitDate Between '{beginDt}' AND  '{endDt}'
	Group By CnName");

            RefAsync<int> total = 0;

            var rankingData = await _sqlSugar.SqlQueryable<MarketingSalesOrderRankingView>(sql).ToPageListAsync(_dto.PageIndex, _dto.PageSize, total);

            return Ok(JsonView(true, "操作成功!", rankingData, total));
        }

        /// <summary>
        ///  市场部销售额
        ///  团组列表、客户类型、客户等级、接单排名
        /// </summary>
        /// <param name="_dto">市场部销售额请求dto</param>
        /// <returns></returns>
        [HttpPost("PostMarketingSalesGroupItem")]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> PostMarketingSalesGroupItem(MarketingSalesGroupListDto _dto)
        {
            #region  参数验证

            MarketingSalesStatisticsDtoFoalidator validationRules = new MarketingSalesStatisticsDtoFoalidator();
            var validResult = await validationRules.ValidateAsync(_dto);
            if (!validResult.IsValid)
            {
                var errors = new StringBuilder();
                foreach (var error in validResult.Errors) errors.AppendLine(error.ErrorMessage);
                return Ok(JsonView(false, errors.ToString()));
            }

            PageFunAuthViewBase pageFunAuthView = new PageFunAuthViewBase();

            #region 页面操作权限验证
            pageFunAuthView = await GeneralMethod.PostUserPageFuncDatas(_dto.UserId, _dto.PageId);

            if (pageFunAuthView.CheckAuth == 0) return Ok(JsonView(false, MsgTips.CheckAuth));
            #endregion

            #endregion

            string userSql = "";
            List<int> userIds = new List<int>();
            userIds = await GetUserIds(_dto.CompanyId, _dto.GroupPickupUserId);

            if (userIds.Count <= 0)
            {
                return Ok(JsonView(true, "操作成功!", new List<object> { }, 0));
            }
            else userSql = @$" And di.JietuanOperator In ({string.Join(",", userIds)})";


            if (!string.IsNullOrEmpty(_dto.SearchCriteria))
            {
                userSql += string.Format(@$" AND di.TeamName Like '%{_dto.SearchCriteria}%'");
            }

            string beginDt = $"{_dto.BeginDt} 00:00:00", endDt = $"{_dto.EndDt} 23:59:59";


            #region 团组List

            string sql = string.Format(@$"SELECT
  ROW_NUMBER() OVER (
    ORDER BY
      CollectionDays
  ) AS RowNumber,
  *
FROM
  (
    SELECT
      di.Id,
      di.TeamName,
      di.ClientUnit,
      di.ClientName,
      di.VisitDate,
      di.VisitPNumber,
      di.JietuanOperator,
      di.VisitEndDate,
      (
        SELECT
          CAST(
            COALESCE(SUM(ItemSumPrice * Rate), 0) AS DECIMAL(12, 2)
          ) AS GroupSales
        FROM
          Fin_ForeignReceivables
        WHERE
          IsDel = 0
          AND di.Id = Diid
          AND AddingWay IN (0, 1, 2)
      ) AS GroupSales,
      u.CnName AS GroupPickupUser,
      DATEADD(DAY, 7, di.VisitEndDate) AS CollectionDays
    FROM
      Grp_DelegationInfo di
    WITH
      (NoLock)
      LEFT JOIN Sys_Users u ON di.JietuanOperator = u.Id
    WHERE
      di.Isdel = 0
    AND (
          SELECT
            CAST(
              COALESCE(SUM(ItemSumPrice * Rate), 0) AS DECIMAL(12, 2)
            ) AS GroupSales
          FROM
            Fin_ForeignReceivables fr
          WHERE
            fr.IsDel = 0
            AND di.Id = fr.Diid
            AND AddingWay IN (0, 1, 2)
        ) > 0 
	   {userSql} 
      AND di.IsBid = 0 
      AND di.TeamName Not Like '%投标%'
      AND VisitDate Between '{beginDt}' And  '{endDt}'
  ) Temp ");
            RefAsync<int> total = 0;
            var groupData = await _sqlSugar.SqlQueryable<MarketingSalesGroupList>(sql).ToPageListAsync(_dto.PageIndex, _dto.PageSize, total);
            #endregion

            #region 客户类型、客户等级 统计

            string sql1 = string.Format(@$"Select 
	sd.[Name],
	Count(*) As [Count]
From Grp_DelegationInfo di
Left Join Sys_SetData sd On di.TeamDid = sd.Id
Where di.Isdel = 0 
And (
  SELECT
    CAST(
      COALESCE(SUM(ItemSumPrice * Rate), 0) AS DECIMAL(12, 2)
    ) AS GroupSales
  FROM
    Fin_ForeignReceivables fr
  WHERE
    fr.IsDel = 0
    AND di.Id = fr.Diid
    AND AddingWay IN (0, 1, 2)
) > 0 
 {userSql} 
      AND di.IsBid = 0 
      AND di.TeamName Not Like '%投标%'
And VisitDate Between '{beginDt}' And  '{endDt}'
Group By [Name]
Order By Count Desc");

            var customerTypeData = await _sqlSugar.SqlQueryable<MarketingSalesGroupStatisticsView>(sql1).ToListAsync();

            string sql2 = string.Format(@$"Select 
	sd.[Name],
	Count(*) As [Count]
From Grp_DelegationInfo di
Left Join Sys_SetData sd On di.TeamLevSId = sd.Id
Where di.Isdel = 0 
And (
  SELECT
    CAST(
      COALESCE(SUM(ItemSumPrice * Rate), 0) AS DECIMAL(12, 2)
    ) AS GroupSales
  FROM
    Fin_ForeignReceivables fr
  WHERE
    fr.IsDel = 0
    AND di.Id = fr.Diid
    AND AddingWay IN (0, 1, 2)
) > 0 
 {userSql} 
      AND di.IsBid = 0 
      AND di.TeamName Not Like '%投标%'
And VisitDate Between '{beginDt}' And  '{endDt}'
Group By [Name]
Order By Count Desc");
            var clientGradeData = await _sqlSugar.SqlQueryable<MarketingSalesGroupStatisticsView>(sql2).ToListAsync();
            #endregion

            #region 接单排名

            string sql4 = string.Format(@$"Select 
ROW_NUMBER() Over(Order By Count(*) Desc) As RowNumber,
u.CnName As UserName,
Count(*) As [Count]
From Grp_DelegationInfo di
Left Join Sys_Users u On di.JietuanOperator = u.Id
Where di.Isdel = 0 
And (
  SELECT
    CAST(
      COALESCE(SUM(ItemSumPrice * Rate), 0) AS DECIMAL(12, 2)
    ) AS GroupSales
  FROM
    Fin_ForeignReceivables fr
  WHERE
    fr.IsDel = 0
    AND di.Id = fr.Diid
    AND AddingWay IN (0, 1, 2)
) > 0
    {userSql} 
      AND di.IsBid = 0 
      AND di.TeamName Not Like '%投标%'
And VisitDate Between '{beginDt}' And  '{endDt}'
Group By CnName");

            var rankingData = await _sqlSugar.SqlQueryable<MarketingSalesOrderRankingView>(sql4).ToListAsync();

            #endregion

            var viewData = new
            {
                groupData = groupData,
                groupTotal = total,
                customerTypeData = customerTypeData,
                clientGradeData = clientGradeData,
                rankingData = rankingData
            };


            return Ok(JsonView(true, "操作成功!", viewData, total));
        }

        /// <summary>
        ///  市场部销售额
        ///  客户拜访列表
        /// </summary>
        /// <param name="_dto">市场部销售额请求dto</param>
        /// <returns></returns>
        [HttpPost("PostMarketingSalesVCList")]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> PostMarketingSalesVCList(MarketingSalesVCListDto _dto)
        {
            #region  参数验证
            MarketingSalesUserPageFuncDtoBaseFoalidator validationRules = new MarketingSalesUserPageFuncDtoBaseFoalidator();
            var validResult = await validationRules.ValidateAsync(_dto);
            if (!validResult.IsValid)
            {
                var errors = new StringBuilder();
                foreach (var error in validResult.Errors) errors.AppendLine(error.ErrorMessage);
                return Ok(JsonView(false, errors.ToString()));
            }

            PageFunAuthViewBase pageFunAuthView = new PageFunAuthViewBase();

            #region 页面操作权限验证
            pageFunAuthView = await GeneralMethod.PostUserPageFuncDatas(_dto.UserId, _dto.PageId);

            if (pageFunAuthView.CheckAuth == 0) return Ok(JsonView(false, MsgTips.CheckAuth));
            #endregion
            #endregion
            return Ok(await _visitingClientsRep._List(_dto.PortType, _dto.PageIndex, _dto.PageSize, _dto.DiId, _dto.Search));
        }

        /// <summary>
        ///  市场部销售额
        ///  客户拜访 操作(添加 Or 编辑)
        /// </summary>
        /// <param name="_dto">市场部销售额请求dto</param>
        /// <returns></returns>
        [HttpPost("PostMarketingSalesVCOperate")]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> PostMarketingSalesVCOperate(MarketingSalesVCOperrateDto _dto)
        {
            #region  参数验证
            MarketingSalesUserPageFuncDtoBaseFoalidator validationRules = new MarketingSalesUserPageFuncDtoBaseFoalidator();
            var validResult = await validationRules.ValidateAsync(_dto);
            if (!validResult.IsValid)
            {
                var errors = new StringBuilder();
                foreach (var error in validResult.Errors) errors.AppendLine(error.ErrorMessage);
                return Ok(JsonView(false, errors.ToString()));
            }

            PageFunAuthViewBase pageFunAuthView = new PageFunAuthViewBase();

            #region 页面操作权限验证
            pageFunAuthView = await GeneralMethod.PostUserPageFuncDatas(_dto.UserId, _dto.PageId);

            if (pageFunAuthView.CheckAuth == 0) return Ok(JsonView(false, MsgTips.CheckAuth));
            #endregion
            #endregion
            return Ok(await _visitingClientsRep._AddOrEdit(_dto));
        }

        /// <summary>
        ///  市场部销售额
        ///  客户拜访 Del
        /// </summary>
        /// <param name="_dto">市场部销售额请求dto</param>
        /// <returns></returns>
        [HttpPost("PostMarketingSalesVCDel")]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> PostMarketingSalesVCDel(MarketingSalesVCDelDto _dto)
        {

            return Ok(await _visitingClientsRep._Del(_dto.Id, _dto.UserId));
        }

        /// <summary>
        ///  市场部销售额
        ///  客户拜访 操作 save
        /// </summary>
        /// <param name="_dto">市场部销售额请求dto</param>
        /// <returns></returns>
        [HttpPost("PostMarketingSalesVCSave")]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> PostMarketingSalesVCSave(MarketingSalesVCSaveDto _dto)
        {
            #region  参数验证
            MarketingSalesUserPageFuncDtoBaseFoalidator validationRules = new MarketingSalesUserPageFuncDtoBaseFoalidator();
            var validResult = await validationRules.ValidateAsync(_dto);
            if (!validResult.IsValid)
            {
                var errors = new StringBuilder();
                foreach (var error in validResult.Errors) errors.AppendLine(error.ErrorMessage);
                return Ok(JsonView(false, errors.ToString()));
            }

            PageFunAuthViewBase pageFunAuthView = new PageFunAuthViewBase();

            #region 页面操作权限验证
            pageFunAuthView = await GeneralMethod.PostUserPageFuncDatas(_dto.UserId, _dto.PageId);

            if (pageFunAuthView.CheckAuth == 0) return Ok(JsonView(false, MsgTips.CheckAuth));
            #endregion
            #endregion
            return Ok(await _visitingClientsRep._Save(_dto));
        }

        #endregion

        #region 日付报表

        /// <summary>
        /// 日付类型数据
        /// </summary>
        /// <param name="_dto"></param>
        /// <returns></returns>
        [HttpPost("DailypaymentTypeInit")]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> DailypaymentTypeInit(DailypaymentTypeInitDto _dto)
        {
            #region  参数验证
            if (_dto.PortType < 1 || _dto.PortType > 3) return Ok(JsonView(false, msg: MsgTips.Port));

            PageFunAuthViewBase pageFunAuthView = new PageFunAuthViewBase();
            #region 页面操作权限验证
            //pageFunAuthView = await GeneralMethod.PostUserPageFuncDatas(_dto.UserId, _dto.PageId);

            //if (pageFunAuthView.CheckAuth == 0) return Ok(JsonView(false, "您没有查看权限"));
            #endregion

            #endregion
            if (_dto.PortType == 1 || _dto.PortType == 2 || _dto.PortType == 3) // web/Android/IOS
            {
                var defaultParentIds = new List<int>() {
                                                        48,//  人员费用
                                                        49,//  办公费用
                                                        50,//  销售费用
                                                        51,//  其他费用
                                                        55,//  大运会
                                                         };

                var dailypaymentTypeData = await RedisRepository.RedisFactory
                                                                .CreateRedisRepository()
                                                                .StringGetAsync<List<int>>("DailypaymentTypeData") ?? new List<int>();

                var dailyTypeData = await _sqlSugar.Queryable<DailypaymentParentTypeView>()
                                                   .Includes(x => x.SubData)
                                                   .Where(x => defaultParentIds.Contains(x.Id))
                                                   .ToListAsync();
                dailyTypeData.ForEach(x =>
                {
                    x.SubData.ForEach(y =>
                    {
                        int currId = dailypaymentTypeData.Find(z => z == y.Id);
                        y.IsChecked = currId == 0 ? false : true;
                    });
                });


                var companyData = await _sqlSugar.Queryable<Sys_Company>()
                                                 .Where(x => x.IsDel == 0)
                                                 .Select(x => new { id = x.Id, name = x.CompanyName })
                                                 .ToListAsync();

                return Ok(JsonView(true, "查询成功!", new { dailyTypeData = dailyTypeData, companyData = companyData }));
            }
            else
            {
                return Ok(JsonView(false, "查询失败"));
            }
        }

        /// <summary>
        /// 日付类型数据 Save
        /// </summary>
        /// <param name="_dto"></param>
        /// <returns></returns>
        [HttpPost("DailypaymentTypeDataSave")]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> DailypaymentTypeDataSave(DailypaymentTypeDataSaveDto _dto)
        {
            #region  参数验证
            if (_dto.PortType < 1 || _dto.PortType > 3) return Ok(JsonView(false, msg: MsgTips.Port));

            PageFunAuthViewBase pageFunAuthView = new PageFunAuthViewBase();
            #region 页面操作权限验证
            //pageFunAuthView = await GeneralMethod.PostUserPageFuncDatas(_dto.UserId, _dto.PageId);

            //if (pageFunAuthView.CheckAuth == 0) return Ok(JsonView(false, "您没有查看权限"));
            #endregion

            #endregion
            if (_dto.PortType == 1 || _dto.PortType == 2 || _dto.PortType == 3) // web/Android/IOS
            {
                if (_dto.TypeIds.Count < 1) return Ok(JsonView(false, "请传入需要保存的TypeIds"));

                var res = await RedisRepository.RedisFactory
                                               .CreateRedisRepository()
                                               .StringSetAsync(
                                                    key: "DailypaymentTypeData",
                                                    _dto.TypeIds,
                                                    timeout: null);

                if (!res) return Ok(JsonView(false, "操作失败"));


                return Ok(JsonView(true, "操作成功!"));
            }
            else return Ok(JsonView(false, "操作失败"));
        }

        /// <summary>
        /// 日付数据列表
        /// </summary>
        /// <param name="_dto"></param>
        /// <returns></returns>
        [HttpPost("DailypaymentRange")]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> DailypaymentRange(DailypaymentRangeDto _dto)
        {
            #region  参数验证

            var validator = new DailypaymentRangeDtoValidator();
            var validationRes = validator.Validate(_dto);

            if (!validationRes.IsValid)
            {
                StringBuilder sb = new StringBuilder();
                foreach (var item in validationRes.Errors)
                {
                    sb.AppendLine(item.ErrorMessage);
                }
                return Ok(JsonView(false, sb.ToString()));
            }


            PageFunAuthViewBase pageFunAuthView = new PageFunAuthViewBase();
            #region 页面操作权限验证
            //pageFunAuthView = await GeneralMethod.PostUserPageFuncDatas(_dto.UserId, _dto.PageId);

            //if (pageFunAuthView.CheckAuth == 0) return Ok(JsonView(false, "您没有查看权限"));
            #endregion

            #endregion
            if (_dto.PortType == 1 || _dto.PortType == 2 || _dto.PortType == 3) // web/Android/IOS
            {
                DateTime _beginDt = Convert.ToDateTime($"{_dto.BeginDt} 00:00:00"),
                         _endDt = Convert.ToDateTime($"{_dto.EndDt} 23:59:59");

                var sqlWhere = string.Format(@" And dfp.CreateTime Between '{0}' And '{1}'", _beginDt, _endDt);

                var dailypaymentTypeData = await RedisRepository.RedisFactory
                                                                .CreateRedisRepository()
                                                                .StringGetAsync<List<int>>("DailypaymentTypeData") ?? new List<int>();

                var sql = string.Format(@"
Select 
	dfp.Id,
	dfp.Instructions,
	dfp.PriceTypeId,
	sd1.Name As FeeType,
	dfp.TransferTypeId,
	sd2.Name As TransferType,
	dfp.SumPrice,
	dfp.FAuditDate,
	dfp.MAuditDate,
	dfp.CompanyId,
	c.CompanyName,
	dfp.CreateUserId As ApplicantId,
	u.CnName As Applicant,
	dfp.CreateTime As ApplicantDt,
	dfp.Instructions+u.CnName As ViewStr
From Fin_DailyFeePayment dfp  
Left Join Sys_Users u On dfp.CreateUserId = u.Id 
Left Join Sys_SetData sd1 On dfp.TransferTypeId = sd1.Id
Left Join Sys_Company c On dfp.CompanyId = c.Id
Left Join Sys_SetData sd2 On dfp.PriceTypeId = sd2.Id
Where dfp.IsDel = 0 And dfp.IsPay = 1
");//--Order By dfp.CreateTime Desc


                if (_dto.Type == 1) //data
                {
                    RefAsync<int> total = 0;
                    var _view = await _sqlSugar.SqlQueryable<DailyFeePaymentRangeView>(sql)
                                               .Where(x => x.ApplicantDt >= _beginDt && x.ApplicantDt <= _endDt)
                                               .WhereIF(dailypaymentTypeData.Count > 0,
                                                        x => dailypaymentTypeData.Contains(x.PriceTypeId))
                                               .WhereIF(_dto.CompanyIds.Count > 0, x => _dto.CompanyIds.Contains(x.CompanyId))
                                               .WhereIF(!string.IsNullOrEmpty(_dto.Filter), x => x.ViewStr.Contains(_dto.Filter))
                                               .OrderByDescending(x => x.ApplicantDt)
                                               .ToPageListAsync(
                                                    pageNumber: _dto.PageIndex,
                                                    pageSize: _dto.PageSize,
                                                    totalNumber: total
                                                    );

                    _sqlSugar.ThenMapper(_view, x =>
                    {
                        x.Contents = _sqlSugar.Queryable<DailyFeePaymentContentView>()
                                              .SetContext(x1 => x1.DFPId, () => x.Id, x)
                                              .ToList();
                    });

                    //单独处理

                    return Ok(JsonView(true, "操作成功!", new { data = _view, total = _view.Sum(x => x.SumPrice) }, total));
                }
                else if (_dto.Type == 2) //view
                {
                    var _view = await _sqlSugar.SqlQueryable<ExcelView>(sql)
                                               .Where(x => x.ApplicantDt >= _beginDt && x.ApplicantDt <= _endDt)
                                               .WhereIF(dailypaymentTypeData.Count > 0,
                                                        x => dailypaymentTypeData.Contains(x.PriceTypeId))
                                               .WhereIF(_dto.CompanyIds.Count > 0, x => _dto.CompanyIds.Contains(x.CompanyId))
                                               .WhereIF(!string.IsNullOrEmpty(_dto.Filter), x => x.ViewStr.Contains(_dto.Filter))
                                               .OrderByDescending(x => x.ApplicantDt)
                                               .ToPageListAsync(
                                                    pageNumber: 1,
                                                    pageSize: 99999
                                                    );

                    _sqlSugar.ThenMapper(_view, x =>
                    {
                        x.Contents = _sqlSugar.Queryable<DailyFeePaymentContentView>()
                                              .SetContext(x1 => x1.DFPId, () => x.Id, x)
                                              .ToList();

                        string str = "";
                        int index = 1;
                        foreach (var item in x.Contents)
                        {
                            string str1 = $"{index}、费用名称：[{item.PriceName}]   单价:[{item.Price:#0.00}]   数量:[{item.Quantity:#0.00}]   小计:[{item.ItemTotal:#0.00}]   备注:[{item.Remark}]";

                            if (index == x.Contents.Count) str += str1;
                            else str += str1 + "\r\n";
                            index++;
                        }
                        x.ContentStr = str;
                    });

                    if (_view.Count > 0)
                    {
                        //DataTable dt = GeneralMethod.
                        DataTable dt = CommonFun.GetDataTableFromIList(_view);
                        dt.TableName = $"_view";
                        decimal total = _view.Sum(x => x.SumPrice);
                        WorkbookDesigner designer = new WorkbookDesigner();
                        designer.Workbook = new Workbook(AppSettingsHelper.Get("ExcelBasePath") + "Template/日付报表模板.xls");
                        designer.SetDataSource(dt);
                        designer.SetDataSource("Total", total);
                        designer.Workbook.Worksheets[0].Name = "日付报表";

                        designer.Process();

                        string fileName = $"DailyPayment/日付报表{DateTime.Now.ToString("yyyyMMddHHmmss")}.xlsx";
                        designer.Workbook.Save(AppSettingsHelper.Get("ExcelBasePath") + fileName);
                        string rst = AppSettingsHelper.Get("ExcelBaseUrl") + AppSettingsHelper.Get("ExcelFtpPath") + fileName;
                        return Ok(JsonView(true, "操作成功", new { url = rst }));
                    }

                    return Ok(JsonView(true, ",暂无数据"));
                }

                return Ok(JsonView(false, "操作失败"));

            }
            else return Ok(JsonView(false, "操作失败"));
        }


        #endregion

        #region 统计模块

        /// <summary>
        ///  同比
        ///  营业额（今年和去年的）、成本支出（今年和去年的）、毛利润（今年和去年的）
        /// </summary>
        /// <param name="_dto">市场部销售额请求dto</param>
        /// <returns></returns>
        [HttpPost("StatisticsYOY")]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> StatisticsYOY(YOYDto _dto)
        {
            //同比增长率 = (本期 - 同期) / 同期 * 100%;
            if (_dto.Year < 1) return Ok(JsonView(false, "操作失败"));


            string beginDt = $"{_dto.Year - 1}-01-01 00:00:00",
                   endDt = $"{_dto.Year}-12-31 23:59:59";

            string sql = string.Format(@"
SELECT
  di.Id,
  di.TeamName,
  di.VisitDate,
  YEAR(di.VisitDate) AS YEAR,
  MONTH(di.VisitDate) AS MONTH,
  (
    SELECT
      CAST(SUM(ItemSumPrice * Rate) AS DECIMAL(12, 2))
    FROM
      Fin_ForeignReceivables
    WHERE
      IsDel = 0
      AND AddingWay IN (0, 1, 2)
      AND di.Id = Diid
  ) AS SaleAmount,
  (
    SELECT
      CAST(
        SUM(
          (
            ((ccp.PayMoney * ccp.DayRate) / ccp.PayPercentage) * 100
          )
        ) AS DECIMAL(12, 2)
      )
    FROM
      Grp_AirTicketReservations atr
      INNER JOIN Grp_CreditCardPayment ccp ON ccp.isdel = 0
      AND ccp.CTable = 85
      AND atr.Id = ccp.CId
    WHERE
      atr.IsDel = 0
      AND ccp.IsAuditGM = 1
      AND ccp.IsPay = 1
      AND di.Id = atr.DiId
  ) AirTicketAmount,
  (
    SELECT
      CAST(
        SUM(
          (
            ctggr.ServiceQuotedPrice * (ccp.PayPercentage / 100) * ccp.DayRate
          )
        ) AS DECIMAL(12, 2)
      )
    FROM
      Grp_CarTouristGuideGroundReservations ctggr
      INNER JOIN Grp_CreditCardPayment ccp ON ccp.IsDel = 0
      AND ccp.CTable = 79
      AND ctggr.Id = ccp.CId
    WHERE
      ctggr.IsDel = 0
      AND ccp.IsAuditGM = 1
      AND ccp.IsPay = 1
      AND di.Id = ctggr.DiId
  ) AS OPAmount,
  (
    SELECT
      CAST(SUM((ccp.PayMoney * ccp.DayRate)) AS DECIMAL(12, 2))
    FROM
      Grp_DecreasePayments dp
      INNER JOIN Grp_CreditCardPayment ccp ON ccp.isdel = 0
      AND ccp.CTable = 98
      AND dp.Id = ccp.CId
    WHERE
      dp.IsDel = 0
      AND ccp.Ctable = 98
      AND ccp.IsAuditGM = 1
      AND ccp.IsPay = 1
      AND di.Id = dp.Diid
  ) AS OtherCostAmount,
  (
    SELECT
      CAST(
        SUM(
          (
            (ccp.PayMoney * ccp.DayRate) 
          )
        ) AS DECIMAL(12, 2)
      )
    FROM
      Grp_VisaInfo vi
      INNER JOIN Grp_CreditCardPayment ccp ON ccp.isdel = 0
      AND ccp.CTable = 80
      AND vi.Id = ccp.CId
    WHERE
      vi.IsDel = 0
      AND ccp.IsAuditGM = 1
      AND ccp.IsPay = 1
      AND di.Id = vi.DIId
  ) AS VisaAmount,
  (
    SELECT
      CAST(
        SUM(
          (ccp.PayMoney * ccp.DayRate) 
        ) AS DECIMAL(12, 2)
      )
    FROM
      Grp_InvitationOfficialActivities ioa
      INNER JOIN Grp_CreditCardPayment ccp ON ccp.isdel = 0
      AND ccp.CTable = 81
      AND ioa.Id = ccp.CId
    WHERE
      ioa.IsDel = 0
      AND ccp.IsAuditGM = 1
      AND ccp.IsPay = 1
      AND di.Id = ioa.Diid
  ) AS OAAmount,
  (
    SELECT
      CAST(
        SUM(
          (ccp.PayMoney * ccp.DayRate)
        ) AS DECIMAL(12, 2)
      )
    FROM
      Grp_Customers ic
      INNER JOIN Grp_CreditCardPayment ccp ON ccp.isdel = 0
      AND ccp.CTable = 82
      AND ic.Id = ccp.CId
    WHERE
      ic.IsDel = 0
      AND ccp.IsAuditGM = 1 
      AND ccp.IsPay = 1
      AND di.Id = ic.DiId
  ) AS InsureAmount,
  (
    SELECT
      CAST(
        SUM(
          (ccp.PayMoney * ccp.DayRate)
        ) AS DECIMAL(12, 2)
      )
    FROM
      Grp_HotelReservations hr
      INNER JOIN Grp_CreditCardPayment ccp ON hr.Id = ccp.CId
    WHERE
      hr.IsDel = 0
      AND ccp.IsDel = 0
      AND ccp.CTable = 76
      AND ccp.IsAuditGM = 1
      AND ccp.PayMoney <> 0
      AND ccp.IsPay = 1
      AND di.Id = hr.DiId
  ) AS HotelAmount,
  (
    SELECT
      CAST(
        SUM((gec.PriceSum * gec.Coefficient * ccp.DayRate)) AS DECIMAL(12, 2)
      )
    FROM
      OA2023DB.dbo.Fin_GroupExtraCost gec
      INNER JOIN Grp_CreditCardPayment ccp ON gec.Id = ccp.CId
    WHERE
      ccp.IsDel = 0
      AND ccp.CTable = 1015
      AND ccp.IsAuditGM = 1
      AND ccp.IsPay = 1
      AND di.Id = gec.DiId
  ) AS ExcessAmount,
  (
    SELECT
      CAST(
        SUM(
          (ccp.PayMoney * ccp.DayRate) 
        ) AS DECIMAL(12, 2)
      )
    FROM
      Fin_PaymentRefundAndOtherMoney prom
      INNER JOIN Grp_CreditCardPayment ccp ON prom.DiId = ccp.DIId
      AND prom.Id = ccp.CId
    WHERE
      prom.IsDel = 0
      AND prom.PayType = 1
      AND ccp.CTable = 285
      AND ccp.IsAuditGM = 1
      AND ccp.IsPay = 1
      AND di.Id = prom.DiId
  ) AS SKTHAmount
FROM
  Grp_DelegationInfo di
WHERE
  di.IsDel = 0
  AND di.TeamDid IN (38,39,40,1048)
  AND di.VisitDate BETWEEN '{0}' AND '{1}'
ORDER BY
  di.VisitDate
", beginDt, endDt);

            var data = await _sqlSugar.SqlQueryable<StatisticsYOYView>(sql).ToListAsync();

            var thisYearData = data.Where(x => x.Year == _dto.Year).ToList();
            var lastYearData = data.Where(x => x.Year == (_dto.Year - 1)).ToList();

            List<YOYReturnView> salesYOYData = new List<YOYReturnView>();
            List<YOYReturnView> costYOYData = new List<YOYReturnView>();
            List<YOYReturnView> grossProfitYOYData = new List<YOYReturnView>();
            int monthIndex = 1;
            while (monthIndex < 13)
            {
                var thisMonthData = thisYearData.Where(x => x.Month == monthIndex).ToList();
                var lastMonthData = lastYearData.Where(x => x.Month == monthIndex).ToList();
                var thisGroupIds = thisMonthData.Select(x => x.Id).ToList();
                var lastGroupIds = lastMonthData.Select(x => x.Id).ToList();

                decimal thisSalesAmount = thisMonthData.Sum(x => x?.SaleAmount ?? 0.00M),
                        lastSalesAmount = lastMonthData.Sum(x => x?.SaleAmount ?? 0.00M),
                        thisCostAmount = thisMonthData.Sum(x => x?.CostAmount ?? 0.00M),
                        lastCostAmount = lastMonthData.Sum(x => x?.CostAmount ?? 0.00M),
                        thisgrossProfitAmount = thisMonthData.Sum(x => x?.GrossProfitAmount ?? 0.00M),
                        lastgrossProfitAmount = lastMonthData.Sum(x => x?.GrossProfitAmount ?? 0.00M);

                salesYOYData.Add(new YOYReturnView(_dto.Year, monthIndex, thisSalesAmount, lastSalesAmount, thisGroupIds, lastGroupIds));
                costYOYData.Add(new YOYReturnView(_dto.Year, monthIndex, thisCostAmount, lastCostAmount, thisGroupIds, lastGroupIds));
                grossProfitYOYData.Add(new YOYReturnView(_dto.Year, monthIndex, thisgrossProfitAmount, lastgrossProfitAmount, thisGroupIds, lastGroupIds));

                monthIndex++;
            }
            salesYOYData = salesYOYData.OrderBy(x => x.Month).ToList();
            costYOYData = costYOYData.OrderBy(x => x.Month).ToList();
            grossProfitYOYData = grossProfitYOYData.OrderBy(x => x.Month).ToList();


            return Ok(JsonView(true, "操作成功!", new { salesYOYData = salesYOYData, costYOYData = costYOYData, grossProfitYOYData = grossProfitYOYData }));
        }

        /// <summary>
        ///  国交数据统计-机票相关
        ///  机票票数、机票到达地、预订平台
        /// </summary>
        /// <param name="_dto"></param>
        /// <returns></returns>
        [HttpPost("StatisticsAirTicket")]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> StatisticsAirTicket(YOYDto _dto)
        {
            if (_dto.Year < 1) return Ok(JsonView(false, "操作失败"));


            string beginDt = $"{_dto.Year}-01-01 00:00:00",
                   endDt = $"{_dto.Year}-12-31 23:59:59";

            string sql = string.Format(@"
SELECT
  atr.DIId,
  atr.FlightsDate,
  YEAR(atr.FlightsDate) AS YEAR,
  MONTH(atr.FlightsDate) AS MONTH,
  atr.CType,
  sd.Name As CTypeName,
  atr.FlightsCity,
  atr.ClientNum,
  atr.ClientName,
  di.TeamName As 'GroupName',
  CASE 
    WHEN ccp.Payee = '' OR ccp.Payee IS NULL THEN '未知'
    ELSE ccp.Payee
  END AS 'Platform'
FROM
  Grp_AirTicketReservations atr
  INNER JOIN Grp_CreditCardPayment ccp ON atr.Id = ccp.CId
  AND ccp.IsDel = 0
  AND CTable = 85
  INNER JOIN Grp_DelegationInfo di ON atr.DIId = di.Id
  LEFT JOIN Sys_SetData sd On atr.CType = sd.Id
WHERE
  atr.IsDel = 0
  AND di.TeamDid IN (38,39,40,1048)
  AND atr.CType IN(457,458,459,460,574,575)
  AND CHARINDEX('行程单',atr.ClientName) = 0
  AND CHARINDEX('返点',atr.ClientName) = 0
  AND CHARINDEX('-1',atr.ClientName) = 0
  AND ccp.IsPay = 1
  AND atr.FlightsDate BETWEEN '{0}' AND '{1}'
  ORDER BY atr.FlightsDate
", beginDt, endDt);

            var data = await _sqlSugar.SqlQueryable<StatisticsAirTicketView>(sql).ToListAsync();

            var groupIds = data.Select(x => x.DIId).ToList();
            //处理城市数据
            var threeCodeData = await _sqlSugar.Queryable<Res_ThreeCode>().Where(x => x.IsDel == 0).ToListAsync();
            //处理团组名称
            var groupNames = await _sqlSugar.Queryable<Grp_DelegationInfo>().Where(x => groupIds.Contains(x.Id)).Select(x => new { Id = x.Id, GroupName = x.TeamName }).ToListAsync();


            data.ForEach(x =>
            {
                string cityName = "";
                if (!string.IsNullOrEmpty(x.FlightsCity))
                {
                    string cityCode = x.FlightsCity.Replace("-", "").Replace("/", "");

                    int cityNum = cityCode.Length / 3;
                    if (cityCode.Length % 3 == 0)
                    {
                        for (int i = 0; i < cityNum; i++)
                        {
                            string code = "";
                            if (i == 0) code = cityCode.Substring(0, 3);
                            else code = cityCode.Substring(i * 3, 3);

                            if (!string.IsNullOrEmpty(code))
                            {
                                if (i == cityNum - 1)
                                {
                                    cityName += threeCodeData.Find(x1 => x1.Three.Equals(code))?.City ?? "";
                                }
                                else
                                {
                                    var str1 = threeCodeData.Find(x1 => x1.Three.Equals(code))?.City ?? "";
                                    cityName += str1 + "、";
                                }
                            }
                        }
                    }
                }
                x.FlightsCityName = cityName;
            });

            var airTicketNumData = new List<AirTicketReturnView>();
            var airTicketAreaData = new List<AirTicketCityReturnView>();

            int monthIndex = 1;
            while (monthIndex < 13)
            {
                var monthData = data.Where(x => x.Month == monthIndex).ToList();

                //机票票数处理
                int airticket_quantity = monthData.Sum(x => x.ClientNum);
                var linkGroupIds = monthData.Select(x => x.DIId).ToList();
                List<AitTicketInfo> aitTicketInfos = new List<AitTicketInfo>();

                foreach (var item in monthData)
                {
                    string groupName = groupNames.Find(x => item.DIId == x.Id)?.GroupName ?? "";
                    aitTicketInfos.Add(new AitTicketInfo(item.DIId, groupName, item.ClientNum));
                }
                var aitTicketInfosGroupBy = aitTicketInfos.GroupBy(x => x.Id);

                List<AitTicketInfo> aitTicketInfos1 = new List<AitTicketInfo>();
                foreach (var item in aitTicketInfosGroupBy)
                {

                    aitTicketInfos1.Add(new AitTicketInfo(item.Key, item.First().GroupName, item.Sum(x => x.Quantity)));
                }

                aitTicketInfos1 = aitTicketInfos1.OrderBy(x => x.Quantity).ToList();


                linkGroupIds = linkGroupIds.Distinct().ToList();

                airTicketNumData.Add(new AirTicketReturnView(_dto.Year, monthIndex, airticket_quantity, aitTicketInfos1, linkGroupIds));

                //机票城市处理
                //城市处理
                var airTicketCityInfos = new List<string>();
                foreach (var item in monthData)
                {
                    if (!string.IsNullOrEmpty(item.FlightsCityName))
                    {
                        if (item.FlightsCityName.Contains("、"))
                        {
                            var cityArray = item.FlightsCityName.Split("、").ToList();
                            foreach (var item1 in cityArray)
                            {
                                if (!string.IsNullOrEmpty(item1))
                                {
                                    airTicketCityInfos.Add(item1);
                                }
                            }
                        }
                        else
                        {
                            airTicketCityInfos.Add(item.FlightsCityName);
                        }
                    }
                }

                //int quantity1 = airTicketCityInfos.Count;
                int city_totalquantity = 0;

                var airTicketCityInfosGroupby = airTicketCityInfos.GroupBy(x => x);
                var airTicketCityInfos1 = new List<AirTicketCityInfo>();
                foreach (var item in airTicketCityInfosGroupby)
                {
                    var city_quantity = item.ToList().Count;
                    city_totalquantity += city_quantity;
                    airTicketCityInfos1.Add(new AirTicketCityInfo(item.Key, city_quantity));
                }

                airTicketCityInfos1 = airTicketCityInfos1.OrderByDescending(x => x.Quantity).ToList();

                airTicketAreaData.Add(new AirTicketCityReturnView(_dto.Year, monthIndex, city_totalquantity, airTicketCityInfos1, linkGroupIds));

                monthIndex++;
            }

            var airTicketPlatformData = data.GroupBy(g => g.Platform)
                .Select(g => new
                {
                    platform = g.Key,
                    total = g.Sum(x => x.ClientNum),
                    children = g.GroupBy(g1 => g1.DIId)
                                .Select(g1 => new
                                {
                                    diId = g1.Key,
                                    groupName = g1.FirstOrDefault()?.GroupName ?? "-",
                                    total = g1.Sum(x1 => x1.ClientNum),
                                    children = g1.Select(x1 => new
                                    {
                                        //diId = x1.DIId,
                                        //groupName = x1.GroupName,
                                        flightsCityName = x1.FlightsCityName,
                                        total = x1.ClientNum
                                    })
                                                 .ToList()
                                })
                                .OrderByDescending(x1 => x1.total)
                                .ToList()
                })
                .OrderByDescending(x => x.total)
                .ToList();

            return Ok(JsonView(true, "操作成功!", new
            {
                airTicketNumData = airTicketNumData,
                airTicketAreaData = airTicketAreaData,
                airTicketPlatformData = airTicketPlatformData
            }));
        }

        /// <summary>
        ///  国交数据统计-酒店
        ///  国家TOP10、地区TOP10、预订平台
        /// </summary>
        /// <param name="_dto"></param>
        /// <returns></returns>
        [HttpPost("StatisticsHotel")]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> StatisticsHotel(YOYDto _dto)
        {

            if (_dto.Year < 1) return Ok(JsonView(false, "操作失败"));


            string beginDt = $"{_dto.Year}-01-01 00:00:00",
                   endDt = $"{_dto.Year}-12-31 23:59:59";

            string sql = string.Format(@"
SELECT
  hr.DIId,
  CASE
    WHEN ntf.Country IS NULL THEN '未选择'
    ELSE ntf.Country
  END AS 'Country',
  hr.City,
  hr.HotelName,
  sd.Name AS BookinSite,
  hr.CheckInDate,
  YEAR(hr.CheckInDate) AS YEAR,
  MONTH(hr.CheckInDate) AS MONTH,
  hr.CheckOutDate,
  hr.SingleRoomCount,
  hr.DoubleRoomCount,
  hr.SuiteRoomCount,
  hr.OtherRoomCount
FROM
  Grp_HotelReservations hr
  INNER JOIN Grp_CreditCardPayment ccp ON hr.Id = ccp.CId
  AND ccp.IsDel = 0
  AND CTable = 76
  INNER JOIN Grp_DelegationInfo di ON hr.DiId = di.Id
  LEFT JOIN Sys_SetData sd ON hr.ReservationsWebsite = sd.Id
  LEFT JOIN Grp_NationalTravelFee ntf ON hr.AreaId = ntf.Id 
WHERE
  hr.IsDel = 0
  AND di.TeamDid IN (38,39,40,1048)
  AND ccp.IsPay = 1
  AND (
    hr.SingleRoomCount > 0
    OR hr.DoubleRoomCount > 0
    OR hr.SuiteRoomCount > 0
    OR hr.OtherRoomCount > 0
  )
  AND CHARINDEX('早餐',hr.HotelName) = 0
  AND CHARINDEX('晚餐',hr.HotelName) = 0
  AND CHARINDEX('升级',hr.HotelName) = 0
  AND CHARINDEX('饮料茶水费',hr.HotelName) = 0
  AND CHARINDEX('城市税',hr.HotelName) = 0
  AND CHARINDEX('退款',hr.HotelName) = 0
  AND CHARINDEX('返现',hr.HotelName) = 0
  AND CHARINDEX('会议室',hr.HotelName) = 0
  AND CHARINDEX('迷你吧消费',hr.HotelName) = 0
  AND CHARINDEX('运费',hr.HotelName) = 0
  AND CHARINDEX('地接',hr.HotelName) = 0
  AND CHARINDEX('损失',hr.HotelName) = 0
  AND CHARINDEX('补差',hr.HotelName) = 0
  AND hr.CheckInDate BETWEEN CONVERT(datetime,'{0}') AND CONVERT(datetime,'{1}')
ORDER BY
  hr.CheckInDate
", beginDt, endDt);
            var data = await _sqlSugar.SqlQueryable<StatisticsHotelView>(sql).ToListAsync();

            var hotelCountryGroupByData = data.GroupBy(x => x.Country)
                                            .Select(g => new
                                            {
                                                Country = g.Key,
                                                BookingRoomNum = g.Sum(x => x.RoomTotal),
                                                hotelData = g.GroupBy(x => x.HotelName)
                                                             .Select(g1 => new
                                                             {
                                                                 hotelName = g1.Key,
                                                                 roomNights = g1.Sum(x => x.RoomNights),
                                                                 roomTotal = g1.Sum(x => x.RoomTotal),
                                                                 singleRoomCount = g1.Sum(x => x.SingleRoomCount),
                                                                 doubleRoomCount = g1.Sum(x => x.DoubleRoomCount),
                                                                 suiteRoomCount = g1.Sum(x => x.SuiteRoomCount),
                                                                 otherRoomCount = g1.Sum(x => x.OtherRoomCount),
                                                             })
                                                             .ToList(),
                                                linkGroupIds = g.Select(x => x.DIId).Distinct().ToList()

                                            })
                                             .OrderByDescending(x => x.BookingRoomNum)
                                             .Take(10)
                                             .ToList();

            var hotelData = data.OrderBy(x => x.CityStr).ToList();

            var hotelCityGroupByData = data.GroupBy(x => x.CityStr)
                                            .Select(g => new
                                            {
                                                city = g.Key,
                                                BookingRoomNum = g.Sum(x => x.RoomTotal),
                                                hotelData = g.GroupBy(x => x.HotelName)
                                                             .Select(g1 => new
                                                             {
                                                                 hotelName = g1.Key,
                                                                 roomNights = g1.Sum(x => x.RoomNights),
                                                                 roomTotal = g1.Sum(x => x.RoomTotal),
                                                                 singleRoomCount = g1.Sum(x => x.SingleRoomCount),
                                                                 doubleRoomCount = g1.Sum(x => x.DoubleRoomCount),
                                                                 suiteRoomCount = g1.Sum(x => x.SuiteRoomCount),
                                                                 otherRoomCount = g1.Sum(x => x.OtherRoomCount),
                                                             })
                                                             .ToList(),
                                                linkGroupIds = g.Select(x => x.DIId).Distinct().ToList()

                                            })
                                             .OrderByDescending(x => x.BookingRoomNum)
                                             .Take(10)
                                             .ToList();

            var hotelPlatformGroupByData = data.GroupBy(x => x.BookinSite)
                                               .Select(g => new
                                               {
                                                   Platform = g.Key,
                                                   BookingRoomNum = g.Sum(x => x.RoomTotal),
                                                   hotelData = g.GroupBy(x => x.HotelName)
                                                                .Select(g1 => new
                                                                {
                                                                    hotelName = g1.Key,
                                                                    roomNights = g1.Sum(x => x.RoomNights),
                                                                    roomTotal = g1.Sum(x => x.RoomTotal),
                                                                    singleRoomCount = g1.Sum(x => x.SingleRoomCount),
                                                                    doubleRoomCount = g1.Sum(x => x.DoubleRoomCount),
                                                                    suiteRoomCount = g1.Sum(x => x.SuiteRoomCount),
                                                                    otherRoomCount = g1.Sum(x => x.OtherRoomCount),
                                                                })
                                                                .ToList(),
                                                   linkGroupIds = g.Select(x => x.DIId).Distinct().ToList()

                                               })
                                               .OrderByDescending(x => x.BookingRoomNum)
                                               .ToList();

            return Ok(JsonView(true, "操作成功!", new { hotelCountryGroupByData = hotelCountryGroupByData, hotelCityData = hotelCityGroupByData, hotelPlatformData = hotelPlatformGroupByData }));
        }

        /// <summary>
        ///  国交数据统计
        ///  商邀邀请国家数量TOP10
        /// </summary>
        /// <param name="_dto"></param>
        /// <returns></returns>
        [HttpPost("StatisticsInvitation")]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> StatisticsInvitation(YOYDto _dto)
        {
            if (_dto.Year < 1) return Ok(JsonView(false, "操作失败"));

            string beginDt = $"{_dto.Year}-01-01 00:00:00",
                   endDt = $"{_dto.Year}-12-31 23:59:59";

            string sql = string.Format(@"
SELECT
  oa.DiId,
  di.TeamName AS GroupName,
  oa.Country,
  oa.Area AS City,
  oa.Client,
  oa.Date
FROM
  Res_OfficialActivities oa
  LEFT JOIN Grp_DelegationInfo di ON oa.DiId = di.Id
WHERE
  oa.IsDel = 0
  AND oa.Date BETWEEN '{0}' AND '{1}'
  ", beginDt, endDt);

            var data = await _sqlSugar.SqlQueryable<StatisticsInvitation_HW>(sql).ToListAsync();

            var groupByCountry = data.GroupBy(x => x.Country)
                                  .Select(g => new
                                  {
                                      Country = g.Key,
                                      TimeNum = g.Count(),
                                      LinkGroupIds = g.Select(x => x.GroupName).Distinct().ToList()

                                  })
                                  .OrderByDescending(x => x.TimeNum)
                                  .Take(10)
                                  .ToList();
            var groupByClient = data.GroupBy(x => x.Client)
                                  .Select(g => new
                                  {
                                      Country = g.Key,
                                      TimeNum = g.Count(),
                                      LinkGroupIds = g.Select(x => x.GroupName).Distinct().ToList()

                                  })
                                  .OrderByDescending(x => x.TimeNum)
                                  .Take(10)
                                  .ToList();


            return Ok(JsonView(true, "操作成功!", new { groupByCountry = groupByCountry, groupByClient = groupByClient }));
        }

        /// <summary>
        ///  会务数据统计
        ///  会务城市TOP10排序
        /// </summary>
        /// <param name="_dto"></param>
        /// <returns></returns>
        [HttpPost("StatisticsConferenceCity")]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> StatisticsConferenceCity(YOYDto _dto)
        {

            if (_dto.Year < 1) return Ok(JsonView(false, "操作失败"));


            string beginDt = $"{_dto.Year}-01-01 00:00:00",
                   endDt = $"{_dto.Year}-12-31 23:59:59";

            string sql = string.Format(@"
SELECT
  ioa.DiId,
  ioa.InviterArea AS Country
FROM
  Grp_InvitationOfficialActivities ioa
  INNER JOIN Grp_CreditCardPayment ccp ON ioa.Id = ccp.CId
  AND ccp.IsDel = 0
  AND ccp.CTable = 81
  INNER JOIN Grp_DelegationInfo di ON ioa.DiId = di.Id
WHERE
  ioa.IsDel = 0
  AND di.TeamDid IN (102,248,302,691,762,1047)
  AND ccp.IsPay = 1
  AND ioa.CreateTime BETWEEN '{0}' AND '{1}'
", beginDt, endDt);

            var data = await _sqlSugar.SqlQueryable<StatisticsInvitation>(sql).ToListAsync();

            var groupByData = data.GroupBy(x => x.Country)
                                  .Select(g => new
                                  {
                                      Country = g.Key,
                                      TimeNum = g.Count(),
                                      LinkGroupIds = g.Select(x => x.DIId).ToList()

                                  })
                                  .OrderByDescending(x => x.TimeNum)
                                  .Take(10)
                                  .ToList();

            return Ok(JsonView(true, "操作成功!", groupByData));
        }

        /// <summary>
        ///  团组数据统计
        ///  团组合作前十的客户TOP10排序
        /// </summary>
        /// <param name="_dto"></param>
        /// <returns></returns>
        [HttpPost("StatisticsCooperativeCustomer")]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> StatisticsCooperativeCustomer(YOYDto _dto)
        {

            if (_dto.Year < 1) return Ok(JsonView(false, "操作失败"));


            string beginDt = $"{_dto.Year}-01-01 00:00:00",
                   endDt = $"{_dto.Year}-12-31 23:59:59";
            DateTime _beginDt = Convert.ToDateTime(beginDt),
                     _endDt = Convert.ToDateTime(endDt);

            var groupInfos = await _sqlSugar.Queryable<Grp_DelegationInfo>()
                                            .Where(x => x.IsDel == 0)
                                            //.Where(x => x.IsSure == 1)
                                            .Where(x => x.VisitDate >= _beginDt && x.VisitDate <= _endDt)
                                            .ToListAsync();

            var groupInfos1 = groupInfos.GroupBy(x => x.ClientName);

            var view = groupInfos1.Select(x =>
                                            new
                                            {
                                                clienName = x.Key,
                                                clientUnit = x.FirstOrDefault()?.ClientUnit ?? "",
                                                visitsNum = x.Count()
                                            }
                                         )
                                  .OrderByDescending(x => x.visitsNum)
                                  .Take(10)
                                  .ToList();
            return Ok(JsonView(true, "操作成功!", view));
        }

        /// <summary>
        ///  团组数据统计
        ///  已出团客户单位的类型比例图（饼状图-政府团、企业团等）
        /// </summary>
        /// <param name="_dto"></param>
        /// <returns></returns>
        [HttpPost("StatisticsCooperativeCustomerType")]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> StatisticsCooperativeCustomerType(YOYDto _dto)
        {

            if (_dto.Year < 1) return Ok(JsonView(false, "操作失败"));


            string beginDt = $"{_dto.Year}-01-01 00:00:00",
                   endDt = $"{_dto.Year}-12-31 23:59:59";
            DateTime _beginDt = Convert.ToDateTime(beginDt),
                     _endDt = Convert.ToDateTime(endDt);

            var groupInfos = await _sqlSugar.Queryable<Grp_DelegationInfo>()
                                            .LeftJoin<Sys_SetData>((di, sd) => di.TeamDid == sd.Id)
                                            .Where((di, sd) => di.IsDel == 0)
                                            .Where((di, sd) => di.VisitDate >= _beginDt && di.VisitDate <= _endDt)
                                            .Select((di, sd) => new
                                            {
                                                Id = di.Id,
                                                GroupTypeId = di.TeamDid,
                                                GroupTypeName = sd.Name,
                                            })
                                            .ToListAsync();


            var groupInfos1 = groupInfos.GroupBy(x => x.GroupTypeName);

            var view = groupInfos1.Select(x =>
                                            new
                                            {
                                                groupTypeName = x.Key,
                                                groupNum = x.Count(),
                                                linkGroupId = x.Select(x1 => x1.Id).ToList()
                                            }
                                         )
                                  .OrderByDescending(x => x.groupNum)
                                  .ToList();
            return Ok(JsonView(true, "操作成功!", view));
        }

        /// <summary>
        ///  团组数据统计
        ///  团组数量
        /// </summary>
        /// <param name="_dto"></param>
        /// <returns></returns>
        [HttpPost("StatisticsGroupNum")]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> StatisticsGroupNum(YOYDto _dto)
        {
            if (_dto.Year < 1) return Ok(JsonView(false, "操作失败"));

            string beginDt = $"{_dto.Year}-01-01 00:00:00",
                   endDt = $"{_dto.Year}-12-31 23:59:59";
            DateTime _beginDt = Convert.ToDateTime(beginDt),
                     _endDt = Convert.ToDateTime(endDt);

            var groupInfos = await _sqlSugar.Queryable<Grp_DelegationInfo>()
                                            .Where(x => x.IsDel == 0)
                                            .Where(x => x.VisitDate >= _beginDt && x.VisitDate <= _endDt)
                                            .Select(x => new StatisticsGroupInfoEntity
                                            {
                                                Id = x.Id,
                                                VisitDate = x.VisitDate,
                                                Month = x.VisitDate.Month,
                                                //MonthName = x.VisitDate.ToString("MMM", CultureInfo.GetCultureInfo("zh-CN")),
                                                VisitPNumber = x.VisitPNumber
                                            })
                                            .MergeTable()
                                            .OrderBy(x => new { x.Month })
                                            .ToListAsync();

            string groupNumStr = $"年度出访量：{groupInfos.Count}\r\n";

            var groupNumYearData = new
            {
                year = _dto.Year,
                yearName = _dto.Year + "年",
                num = groupInfos.Count,
                linkGroupIds = groupInfos.Select(x => x.Id).ToList()
            };
            var groupNumData = groupInfos.GroupBy(x => x.Quarter)
                                         .Select(x => new
                                         {
                                             Quarter = x.Key,
                                             QuarterName = x.FirstOrDefault()?.QuarterName ?? "-",
                                             Num = x.Count(),
                                             LinkGroupIds = x.Select(x1 => x1.Id).ToList()
                                         })
                                         .OrderBy(x => x.Quarter)
                                         .ToList();
            groupNumData.ForEach(x =>
            {
                groupNumStr += $"{x.QuarterName}出访量：{x.Num}；";
            });

            var groupNumMonthData = groupInfos.GroupBy(x => x.Month)
                                         .Select(x => new
                                         {
                                             Month = x.Key,
                                             MonthName = x.FirstOrDefault()?.MonthName ?? "-",
                                             Num = x.Count(),
                                             LinkGroupIds = x.Select(x1 => x1.Id).ToList()
                                         })
                                         .OrderBy(x => x.Month)
                                         .ToList();

            return Ok(JsonView(true, "操作成功!", new
            {
                yearData = groupNumYearData,
                quarterData = groupNumData,
                monthData = groupNumMonthData,
                remark = groupNumStr
            }));
        }

        /// <summary>
        ///  团组数据统计
        ///  团组人数
        /// </summary>
        /// <param name="_dto"></param>
        /// <returns></returns>
        [HttpPost("StatisticsGroupPeopleNum")]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> StatisticsGroupPeopleNum(YOYDto _dto)
        {
            if (_dto.Year < 1) return Ok(JsonView(false, "操作失败"));

            string beginDt = $"{_dto.Year}-01-01 00:00:00",
                   endDt = $"{_dto.Year}-12-31 23:59:59";
            DateTime _beginDt = Convert.ToDateTime(beginDt),
                     _endDt = Convert.ToDateTime(endDt);

            var groupInfos = await _sqlSugar.Queryable<Grp_DelegationInfo>()
                                            .Where(x => x.IsDel == 0)
                                            .Where(x => x.VisitDate >= _beginDt && x.VisitDate <= _endDt)
                                            .Select(x => new StatisticsGroupInfoEntity
                                            {
                                                Id = x.Id,
                                                VisitDate = x.VisitDate,
                                                Month = x.VisitDate.Month,
                                                VisitPNumber = x.VisitPNumber
                                            })
                                            .MergeTable()
                                            .OrderBy(x => new { x.Month })
                                            .ToListAsync();

            string groupPeopleNumStr = $"年度出访人数：{groupInfos.Sum(x => x.VisitPNumber)}\r\n";

            var groupPeopleNumYearData = new
            {
                year = _dto.Year,
                yearName = _dto.Year + "年",
                num = groupInfos.Sum(x => x.VisitPNumber),
                linkGroupIds = groupInfos.Select(x => x.Id).ToList()
            };
            var groupPeopleNumData = groupInfos.GroupBy(x => x.Quarter)
                                               .Select(x => new
                                               {
                                                   Quarter = x.Key,
                                                   QuarterName = x.FirstOrDefault()?.QuarterName ?? "-",
                                                   Num = x.Sum(x1 => x1.VisitPNumber),
                                                   LinkGroupIds = x.Select(x1 => x1.Id).ToList()
                                               })
                                               .OrderBy(x => x.Quarter)
                                               .ToList();
            groupPeopleNumData.ForEach(x =>
            {
                groupPeopleNumStr += $"{x.QuarterName}出访人数：{x.Num}；";
            });

            var groupPeopleNumMonthData = groupInfos.GroupBy(x => x.Month)
                                                    .Select(x => new
                                                    {
                                                        Month = x.Key,
                                                        MonthName = x.FirstOrDefault()?.MonthName ?? "-",
                                                        Num = x.Sum(x1 => x1.VisitPNumber),
                                                        LinkGroupIds = x.Select(x1 => x1.Id).ToList()
                                                    })
                                                    .OrderBy(x => x.Month)
                                                    .ToList();

            return Ok(JsonView(true, "操作成功!", new
            {
                yearData = groupPeopleNumYearData,
                quarterData = groupPeopleNumData,
                monthData = groupPeopleNumMonthData,
                remark = groupPeopleNumStr
            }));
        }

        /// <summary>
        ///  团组数据统计
        ///  OP 成本   
        /// </summary>
        /// <param name="_dto"></param>
        /// <returns></returns>
        [HttpPost("StatisticsOP")]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> StatisticsOP(StatisticsOPDto _dto)
        {
            if (_dto.Year < 1) return Ok(JsonView(false, "Year参数错误!"));
            if (_dto.Type < 1 || _dto.Type > 2) return Ok(JsonView(false, "Type参数错误!"));


            int thisYear = _dto.Year, lastYear = _dto.Year - 1;
            string beginDt = $"{lastYear}-01-01 00:00:00",
                   endDt = $"{thisYear}-12-31 23:59:59";



            string sql = string.Format(@"
SELECT
  temp.Id,
  DiId,
  PaymentTime,
  [Year],
  [Quarter],
  [Month],
  Area,
  FeeType,
  sd1.Name AS FeeTypeName,
  FeeSubType,
  sd2.Name AS FeeSubTypeName,
  CONVERT(DECIMAL(12, 2), Price * Qauntity * ExchangeRate) AS Price
FROM
  (
    SELECT
      ctggr.Id,
      ctggr.DiId,
      ctggr.Area,
      CASE
        WHEN ctggr.PriceType IS NULL THEN 1069
        ELSE ctggr.PriceType
      END AS FeeType,
      ctggrc.SId AS FeeSubType,
      ctggrc.Price,
      CASE
        WHEN ctggrc.Count IS NULL THEN 1
        ELSE ctggrc.Count
      END AS Qauntity,
      ccp.DayRate AS ExchangeRate,
      ctggrc.CreateTime AS PaymentTime,
      YEAR(ctggrc.CreateTime) AS [Year],
      CONVERT(INT,DATENAME(QUARTER, ctggrc.CreateTime)) AS [Quarter],
      MONTH(ctggrc.CreateTime) AS [Month]
    FROM
      Grp_CarTouristGuideGroundReservations ctggr
      INNER JOIN Grp_CarTouristGuideGroundReservationsContent ctggrc ON ctggr.Id = ctggrc.CTGGRId
      AND ctggrc.IsDel = 0
      INNER JOIN Grp_CreditCardPayment ccp ON ctggr.Id = ccp.CId
      AND ccp.CTable = 79
      AND ccp.IsDel = 0
    WHERE
      ctggr.IsDel = 0
      AND ctggrc.SId != 1070 --费用子项筛掉尾款
      AND ctggrc.Price > 0 --筛选真实存在的数据
      AND ccp.IsPay = 1
  ) temp
  LEFT JOIN Sys_SetData sd1 ON FeeType = sd1.Id
  LEFT JOIN Sys_SetData sd2 ON FeeSubType = sd2.Id
WHERE
  temp.Area NOT LIKE '%尾款%'  -- 筛选掉尾款相关信息
  AND temp.FeeType != 1062 --费用类型筛选掉全款和首付款数据
  AND temp.PaymentTime BETWEEN CONVERT(datetime,'{0}') AND CONVERT(datetime,'{1}')
ORDER BY
  temp.[Year],
  temp.[Quarter],
  temp.[Month]
", beginDt, endDt);

            var opInfos = await _sqlSugar.SqlQueryable<StatisticsOP>(sql).ToListAsync();

            //处理地区相关信息
            var countrys = await _sqlSugar.Queryable<Grp_NationalTravelFee>().Where(x => x.IsDel == 0).ToListAsync();

            for (int i = 0; i < opInfos.Count; i++)
            {
                string country = string.Empty, city = string.Empty;

                int dataId = 0;
                bool isCountryId = int.TryParse(opInfos[i].Area, out dataId);
                string[] citySelecter = new string[] { "其他城市", "所有城市" };
                if (isCountryId)
                {
                    var countryInfo = countrys.Find(x => x.Id == dataId);
                    country = countryInfo?.Country ?? "";
                    if (citySelecter.Contains(countryInfo?.City)) city = country;
                    else city = countryInfo?.City ?? "";

                }
                else
                {
                    var countryInfo1 = countrys.Find(x => opInfos[i].Area.Contains(x.Country));
                    if (countryInfo1 != null)
                    {
                        country = countryInfo1?.Country ?? "";
                        city = countryInfo1?.City ?? country;
                        var countryInfo2 = countrys.Find(x => opInfos[i].Area.Contains(x.City));

                        if (citySelecter.Contains(countryInfo2?.City)) city = country;
                        else city = countryInfo2?.City ?? country;

                    }
                    else
                    {
                        var countryInfo2 = countrys.Find(x => opInfos[i].Area.Contains(x.City));

                        if (countryInfo2 != null)
                        {
                            country = countryInfo2?.Country ?? "";
                            city = countryInfo2?.City ?? "";

                            if (citySelecter.Contains(countryInfo2?.City)) city = country;

                        }
                    }
                }
                opInfos[i].Country = country;
                opInfos[i].City = city;
            }

            var opParentFeeData = new StatisticsOP()._OPParentFeeInfo;

            if (_dto.Type == 1)
            {
                //同比增长率＝（本期数－同期数）/ 同期数
                var yearData = opInfos.GroupBy(x => x.ParentFeeName)
                                      .Select(x => new StatisticsOPYOY(
                                                   feeId: x.FirstOrDefault()?.ParentFeeId ?? 0,
                                                   feeName: x.Key,
                                                   currPeriodFee: x.Where(x => x.Year == thisYear).Sum(x => x.Price),
                                                   samePeriodFee: x.Where(x => x.Year == lastYear).Sum(x => x.Price),
                                                   currPeriodGroupTotal: x.Where(x => x.Year == thisYear).Select(x => x.DiId).Distinct().Count(),
                                                   samePeriodGroupTotal: x.Where(x => x.Year == lastYear).Select(x => x.DiId).Distinct().Count(),
                                                   subFeeData: x.GroupBy(x1 => x1.FeeSubType)
                                                                .Select(x1 => new StatisticsOPSubFeeYOY(
                                                                              feeId: x1.Key,
                                                                              feeName: x1.FirstOrDefault()?.FeeSubTypeName ?? "",
                                                                              currPeriodFee: x1.Where(x1 => x1.Year == thisYear).Sum(x1 => x1.Price),
                                                                              samePeriodFee: x1.Where(x1 => x1.Year == lastYear).Sum(x1 => x1.Price),
                                                                              currPeriodGroupTotal: x1.Where(x1 => x1.Year == thisYear).Select(x1 => x1.DiId).Distinct().Count(),
                                                                              samePeriodGroupTotal: x1.Where(x1 => x1.Year == lastYear).Select(x1 => x1.DiId).Distinct().Count(),
                                                                              cityData: x1.GroupBy(x2 => x2.City)
                                                                                          .Select(x2 => new StatisticsOPCityYOY(
                                                                                                        cityName: x2.Key,
                                                                                                        currPeriodGroupTotal: x2.Where(x2 => x2.Year == thisYear).Select(x2 => x2.DiId).Distinct().Count(),
                                                                                                        samePeriodGroupTotal: x2.Where(x2 => x2.Year == lastYear).Select(x2 => x2.DiId).Distinct().Count(),
                                                                                                        currPeriodFee: x2.Where(x2 => x2.Year == thisYear).Sum(x2 => x2.Price),
                                                                                                        samePeriodFee: x2.Where(x2 => x2.Year == lastYear).Sum(x2 => x2.Price)
                                                                                              ))
                                                                                          .ToArray()
                                                                    ))
                                                                .ToArray()
                                             ))
                                      .OrderBy(x => x.FeeId)
                                      .ToList();


                var opParentYearIds = yearData.Select(x => x.FeeId).ToList();
                var opParentYearDataSelector = opParentFeeData.Where(x => !opParentYearIds.Contains(x.Id)).ToList();

                if (opParentYearDataSelector.Count > 0)
                {
                    foreach (var item in opParentYearDataSelector)
                    {
                        yearData.Add(
                            new StatisticsOPYOY(feeId: item.Id,
                                                feeName: item.Name,
                                                currPeriodFee: 0.00M,
                                                samePeriodFee: 0.00M,
                                                currPeriodGroupTotal: 0,
                                                samePeriodGroupTotal: 0,
                                                subFeeData: new StatisticsOPSubFeeYOY[] { }
                                                ));
                    }
                    yearData = yearData.OrderBy(x => x.FeeId).ToList();
                }
                return Ok(JsonView(true, "操作成功!", yearData));
            }
            else if (_dto.Type == 2)
            {
                var monthData = opInfos.GroupBy(x => x.Month)
                                       .Select(x => new StatisticsOPMonthYOY(
                                               monthId: x.Key,
                                               monthName: System.Enum.IsDefined(typeof(MonthEnum), (int)x.Key) ? ((MonthEnum)(int)x.Key).GetEnumDescription() : "",
                                               feeDatas: x.GroupBy(x1 => x1.ParentFeeId)
                                                         .Select(x1 => new StatisticsOPYOY(
                                                                       feeId: x1.Key,
                                                                       feeName: x1.FirstOrDefault()?.ParentFeeName ?? "",
                                                                       currPeriodFee: x1.Where(x1 => x1.Year == thisYear).Sum(x1 => x1.Price),
                                                                       samePeriodFee: x1.Where(x1 => x1.Year == lastYear).Sum(x1 => x1.Price),
                                                                       currPeriodGroupTotal: x1.Where(x1 => x1.Year == thisYear).Select(x1 => x1.DiId).Distinct().Count(),
                                                                       samePeriodGroupTotal: x1.Where(x1 => x1.Year == lastYear).Select(x1 => x1.DiId).Distinct().Count(),
                                                                       subFeeData: x1.GroupBy(x2 => x2.FeeSubType)
                                                                                    .Select(x2 => new StatisticsOPSubFeeYOY(
                                                                                                  feeId: x2.Key,
                                                                                                  feeName: x2.FirstOrDefault()?.FeeSubTypeName ?? "",
                                                                                                  currPeriodFee: x2.Where(x2 => x2.Year == thisYear).Sum(x2 => x2.Price),
                                                                                                  samePeriodFee: x2.Where(x2 => x2.Year == lastYear).Sum(x2 => x2.Price),
                                                                                                  currPeriodGroupTotal: x2.Where(x2 => x2.Year == thisYear).Select(x2 => x2.DiId).Distinct().Count(),
                                                                                                  samePeriodGroupTotal: x2.Where(x2 => x2.Year == lastYear).Select(x2 => x2.DiId).Distinct().Count(),
                                                                                                  cityData: x2.GroupBy(x3 => x3.City)
                                                                                                              .Select(x3 => new StatisticsOPCityYOY(
                                                                                                                            cityName: x3.Key,
                                                                                                                            currPeriodFee: x3.Where(x3 => x3.Year == thisYear).Sum(x3 => x3.Price),
                                                                                                                            samePeriodFee: x3.Where(x3 => x3.Year == lastYear).Sum(x3 => x3.Price),
                                                                                                                            currPeriodGroupTotal: x3.Where(x3 => x3.Year == thisYear).Select(x3 => x3.DiId).Distinct().Count(),
                                                                                                                            samePeriodGroupTotal: x3.Where(x3 => x3.Year == lastYear).Select(x3 => x3.DiId).Distinct().Count()
                                                                                                                  ))
                                                                                                              .ToArray()
                                                                                        ))
                                                                                    .ToArray()

                                                             ))
                                                         .ToArray()
                                               ))
                                       //.OrderBy(x => x.MonthId)
                                       .ToList();
                //1、月份是否足够十二月
                var monthIds = new int[] { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12 };
                var monthDataIds = monthData.Select(x => x.MonthId).ToArray();
                var newMonthIds = monthIds.Where(x => !monthDataIds.Contains(x)).ToList();
                if (newMonthIds.Count > 0)
                {
                    foreach (var item in newMonthIds)
                    {
                        monthData.Add(new StatisticsOPMonthYOY(
                                          monthId: item,
                                          monthName: System.Enum.IsDefined(typeof(MonthEnum), (int)item) ? ((MonthEnum)(int)item).GetEnumDescription() : "",
                                          feeDatas: Array.Empty<StatisticsOPYOY>()
                                          ));
                    }
                    monthData = monthData.OrderBy(x => x.MonthId).ToList();
                }
                //2、费用父类是否足够
                for (int i = 0; i < monthData.Count; i++)
                {
                    var month = monthData[i];
                    var opParentFeeIds = month.FeeDatas.Select(x => x.FeeId).ToList();
                    var opParentFeeDataSelector = opParentFeeData.Where(x => !opParentFeeIds.Contains(x.Id)).ToList();
                    if (opParentFeeDataSelector.Count > 0)
                    {
                        var feeDatas = month.FeeDatas.ToList();
                        foreach (var item in opParentFeeDataSelector)
                        {
                            feeDatas.Add(new StatisticsOPYOY(
                                             feeId: item.Id,
                                             feeName: item.Name,
                                             currPeriodFee: 0.00M,
                                             samePeriodFee: 0.00M,
                                             currPeriodGroupTotal: 0,
                                             samePeriodGroupTotal: 0,
                                             subFeeData: Array.Empty<StatisticsOPSubFeeYOY>()
                                ));
                        }
                        feeDatas = feeDatas.OrderBy(x => x.FeeId).ToList();
                        monthData[i].FeeDatas = feeDatas.ToArray();
                    }
                }
                //3、费用子类是否足够

                return Ok(JsonView(true, "操作成功!", monthData));
            }
            return Ok(JsonView(false, "操作失败!"));
        }

        /// <summary>
        ///  团组数据统计
        ///  团组预算/实际成本   
        /// </summary>
        /// <param name="_dto"></param>
        /// <returns></returns>
        [HttpPost("StatisticsGroupCost")]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> StatisticsBRCost(StatisticsBRCostDto _dto)
        {
            if (_dto.Year < 1) return Ok(JsonView(false, "Year参数错误!"));

            string beginDt = $"{_dto.Year}-01-01 00:00:00",
                  endDt = $"{_dto.Year}-12-31 23:59:59";

            var sql = string.Format(@"
SELECT
  ROW_NUMBER() OVER(
    ORDER BY
      di.VisitDate DESC
  ) AS 'RowNumber',
  di.Id,
  di.TeamName AS 'GroupName',
  di.ClientUnit,
  di.VisitDate,
  di.VisitDays,
  di.VisitPNumber,
  (
    SELECT
      TOP 1 CASE
        ISNUMERIC (gcp.Currency)
        WHEN 1 THEN (
          SELECT
            Name
          FROM
            Sys_SetData sd
          WHERE
            CAST(gcp.Currency AS INT) = sd.Id
        )
        ELSE gcp.Currency
      END AS 'CurrencyCode'
    FROM
      Grp_GroupCostParameter gcp
    WHERE
      gcp.IsDel = 0
      AND gcp.DiId = di.Id
  ) Budget_CurrencyCode,
  --预算币种
  (
    SELECT
      TOP 1 CAST(COALESCE(gcp.Rate, 0.0000) AS DECIMAL(12, 4))
    FROM
      Grp_GroupCostParameter gcp
    WHERE
      gcp.IsDel = 0
      AND gcp.DiId = di.Id
  ) Budget_Rate,
  --预算汇率
  (
    SELECT
      CAST(
        SUM(COALESCE(gcp.VisaCB, 0) * COALESCE(gcp.VisaRS, 0)) AS DECIMAL(12, 2)
      )
    FROM
      Grp_GroupCostParameter gcp
    WHERE
      gcp.IsDel = 0
      AND gcp.DiId = di.id
  ) Budget_VisaAmount,
  -- 预算签证成本
  (
    SELECT
      CAST(
        SUM(
          (
            (ccp.PayMoney * ccp.DayRate)
          )
        ) AS DECIMAL(12, 2)
      )
    FROM
      Grp_VisaInfo vi
      INNER JOIN Grp_CreditCardPayment ccp ON ccp.isdel = 0
      AND ccp.CTable = 80
      AND vi.Id = ccp.CId
    WHERE
      vi.IsDel = 0
      AND ccp.IsAuditGM = 1
      AND ccp.IsPay = 1
      AND di.Id = vi.DIId
  ) AS Reality_VisaAmount,
  -- 实际签证成本
  (
    SELECT
      CAST(
        SUM(
          COALESCE(gcp.JJCCB, 0) * COALESCE(gcp.JJCRS, 0) + COALESCE(gcp.GWCCB, 0) * COALESCE(gcp.GWCRS, 0) + COALESCE(gcp.TDCCB, 0) * COALESCE(gcp.TDCRS, 0)
        ) AS DECIMAL(12, 2)
      )
    FROM
      Grp_GroupCostParameter gcp
    WHERE
      gcp.IsDel = 0
      AND gcp.DiId = di.id
  ) Budget_AirTicketAmount,
  -- 预算机票成本
  (
    SELECT
      CAST(
        SUM(
          (
            (ccp.PayMoney * ccp.DayRate)
          )
        ) AS DECIMAL(12, 2)
      )
    FROM
      Grp_AirTicketReservations atr
      INNER JOIN Grp_CreditCardPayment ccp ON ccp.isdel = 0
      AND ccp.CTable = 85
      AND atr.Id = ccp.CId
    WHERE
      atr.IsDel = 0
      AND ccp.IsAuditGM = 1
      AND ccp.IsPay = 1
      AND di.Id = atr.DiId
  ) Reality_AirTicketAmount,
  --实际机票
  (
    SELECT
      CAST(
        SUM(
          COALESCE(gcp.SGRCB, 0) * COALESCE(gcp.SGRNumber, 0) + COALESCE(gcp.TBRCB, 0) * COALESCE(gcp.TBRNumber, 0) + COALESCE(gcp.SUITECB, 0) * COALESCE(gcp.SUITENumber, 0) + COALESCE(gcp.SUITENumber, 0) * COALESCE(gcp.JSESXS, 0)
        ) AS DECIMAL(12, 2)
      )
    FROM
      Grp_GroupCostParameter gcp
    WHERE
      gcp.IsDel = 0
      AND gcp.DiId = di.id
  ) Budget_HotelAmount,
  -- 预算酒店成本
  (
    SELECT
      CAST(
        SUM(
          (ccp.PayMoney * ccp.DayRate)
        ) AS DECIMAL(12, 2)
      )
    FROM
      Grp_HotelReservations hr
      INNER JOIN Grp_CreditCardPayment ccp ON hr.Id = ccp.CId
    WHERE
      hr.IsDel = 0
      AND ccp.IsDel = 0
      AND ccp.CTable = 76
      AND ccp.IsAuditGM = 1
      AND ccp.PayMoney <> 0
      AND ccp.IsPay = 1
      AND di.Id = hr.DiId
  ) AS Reality_HotelAmount,
  -- 实际酒店成本
  (
    SELECT
      CAST(
        SUM(COALESCE(gcp.DJCB, 0) * COALESCE(gcp.DJRS, 0)) AS DECIMAL(12, 2)
      )
    FROM
      Grp_GroupCostParameter gcp
    WHERE
      gcp.IsDel = 0
      AND gcp.DiId = di.id
  ) Budget_OPAmount,
  -- 预算 OP
  (
    SELECT
      CAST(
        SUM(
          (
            ctggr.ServiceQuotedPrice * (ccp.PayPercentage / 100) * ccp.DayRate
          )
        ) AS DECIMAL(12, 2)
      )
    FROM
      Grp_CarTouristGuideGroundReservations ctggr
      INNER JOIN Grp_CreditCardPayment ccp ON ccp.IsDel = 0
      AND ccp.CTable = 79
      AND ctggr.Id = ccp.CId
    WHERE
      ctggr.IsDel = 0
      AND ccp.IsAuditGM = 1
      AND ccp.IsPay = 1
      AND di.Id = ctggr.DiId
  ) AS Reality_OPAmount,
  -- 实际 OP
  (
    SELECT
      CAST(
        SUM(COALESCE(gcp.GWCB, 0) * COALESCE(gcp.GWRS, 0)) AS DECIMAL(12, 2)
      )
    FROM
      Grp_GroupCostParameter gcp
    WHERE
      gcp.IsDel = 0
      AND gcp.DiId = di.id
  ) Budget_OAAmount,
  -- 预算 商邀
  (
    SELECT
      CAST(
        SUM(
          (ccp.PayMoney * ccp.DayRate)
        ) AS DECIMAL(12, 2)
      )
    FROM
      Grp_InvitationOfficialActivities ioa
      INNER JOIN Grp_CreditCardPayment ccp ON ccp.isdel = 0
      AND ccp.CTable = 81
      AND ioa.Id = ccp.CId
    WHERE
      ioa.IsDel = 0
      AND ccp.IsAuditGM = 1
      AND ccp.IsPay = 1
      AND di.Id = ioa.Diid
  ) AS Reality_OAAmount,
  -- 实际 商邀
  (
    SELECT
      CAST(
        SUM(
          COALESCE(gcp.BXCB, 0) * COALESCE(gcp.BXRS, 0) + COALESCE(gcp.HCPCB, 0) * COALESCE(gcp.HCPRS, 0) + COALESCE(gcp.CPCB, 0) * COALESCE(gcp.CPRS, 0) + COALESCE(gcp.HSCB, 0) * COALESCE(gcp.HSRS, 0) + COALESCE(gcp.LYJCB, 0) * COALESCE(gcp.LYJRS, 0)
        ) AS DECIMAL(12, 2)
      )
    FROM
      Grp_GroupCostParameter gcp
    WHERE
      gcp.IsDel = 0
      AND gcp.DiId = di.id
  ) Budget_OtherAmount,
  -- 预算其他成本
  (
    SELECT
      CAST(
        SUM(
          (ccp.PayMoney * ccp.DayRate) 
        ) AS DECIMAL(12, 2)
      )
    FROM
      Grp_Customers ic
      INNER JOIN Grp_CreditCardPayment ccp ON ccp.isdel = 0
      AND ccp.CTable = 82
      AND ic.Id = ccp.CId
    WHERE
      ic.IsDel = 0
      AND ccp.IsAuditGM = 1And ccp.IsPay = 1
      AND di.Id = ic.DiId
  ) AS Reality_InsureAmount,
  -- 实际保险成本
  (
    SELECT
      CAST(SUM((ccp.PayMoney * ccp.DayRate)) AS DECIMAL(12, 2))
    FROM
      Grp_DecreasePayments dp
      INNER JOIN Grp_CreditCardPayment ccp ON ccp.isdel = 0
      AND ccp.CTable = 98
      AND dp.Id = ccp.CId
    WHERE
      dp.IsDel = 0
      AND ccp.Ctable = 98
      AND ccp.IsAuditGM = 1
      AND ccp.IsPay = 1
      AND di.Id = dp.Diid
  ) AS Reality_OtherCostAmount -- 实际其他款项
FROM
  Grp_DelegationInfo di
WHERE
  di.IsDel = 0
  AND di.IsBid = 0 
  --AND di.TeamDid IN (38, 39, 40, 1048)
  AND di.VisitDate BETWEEN '{0}' AND '{1}'
", beginDt, endDt);

            if (!string.IsNullOrEmpty(_dto.Search))
            {
                sql += $"AND di.TeamName Like '%{_dto.Search}%'";
            }

            var view = await _sqlSugar.SqlQueryable<StatisticsBRCostView>(sql).ToListAsync();
            view = view.Where(x => !x.GroupName.Contains("投标") && x.RealityAmount > 0).OrderByDescending(x => x.Id).ToList();
            var total = view.Count;
            var toSkip = (_dto.PageIndex - 1) * _dto.PageSize;
            var data = view.Skip(toSkip).Take(_dto.PageSize).ToList();
            if (data.Count > 0)
            {
                var diIds = data.Select(x => x.Id).ToList();
                var currencyData = await _sqlSugar.Queryable<Sys_SetData>().Where(x => x.IsDel == 0 && x.STid == 66).ToListAsync();
                var ccpData = await _sqlSugar.Queryable<Grp_CreditCardPayment>()
                    .Where(x => x.IsDel == 0 &&
                                diIds.Contains(x.DIId) &&
                                x.IsPay == 1 &&
                                x.IsAuditGM == 1
                    )
                    .ToListAsync();

                for (int i = 0; i < data.Count; i++)
                {
                    var info = data[i];

                    info.RowNumber = i + 1;

                    #region 1：签证 80 
                    var visaDatas = ccpData.Where(x => x.DIId == info.Id && x.CTable == 80).ToList();
                    var visaDesc = "-";
                    if (visaDatas.Count > 0)
                    {
                        visaDesc = $"实际币种金额:";
                        foreach (var visa in visaDatas)
                        {
                            var currencyCode = currencyData.Find(x => x.Id == visa.PaymentCurrency)?.Name;
                            visaDesc += $"{visa.PayMoney} {currencyCode} 汇率:{visa.DayRate.ToString("#0.0000")};";
                        }
                    }
                    info.Reality_VisaAmountDesc = visaDesc;
                    #endregion

                    #region 2：机票 85
                    var airDatas = ccpData.Where(x => x.DIId == info.Id && x.CTable == 85).ToList();
                    var airDesc = "-";
                    if (airDatas.Count > 0)
                    {
                        airDesc = $"实际币种金额:";
                        foreach (var air in airDatas)
                        {
                            var currencyCode = currencyData.Find(x => x.Id == air.PaymentCurrency)?.Name;
                            airDesc += $"{air.PayMoney} {currencyCode} 汇率:{air.DayRate.ToString("#0.0000")};";
                        }
                    }
                    info.Reality_AirTicketAmountDesc = airDesc;
                    #endregion

                    #region 3：酒店 76
                    var hotelDatas = ccpData.Where(x => x.DIId == info.Id && x.CTable == 76 && x.PayMoney != 0).ToList();
                    var hotelDesc = "-";
                    if (hotelDatas.Count > 0)
                    {
                        hotelDesc = $"实际币种金额:";
                        foreach (var hotel in hotelDatas)
                        {
                            var currencyCode = currencyData.Find(x => x.Id == hotel.PaymentCurrency)?.Name;
                            hotelDesc += $"{hotel.PayMoney} {currencyCode} 汇率:{hotel.DayRate.ToString("#0.0000")};";
                        }
                    }
                    info.Reality_HotelAmountDesc = hotelDesc;
                    #endregion

                    #region 4：OP 79
                    var opDatas = ccpData.Where(x => x.DIId == info.Id && x.CTable == 79 && x.PayMoney != 0).ToList();
                    var opDesc = "-";
                    if (opDatas.Count > 0)
                    {
                        opDesc = $"实际币种金额;";
                        foreach (var op in opDatas)
                        {
                            var currencyCode = currencyData.Find(x => x.Id == op.PaymentCurrency)?.Name;
                            opDesc += $"{op.PayMoney} {currencyCode} 汇率:{op.DayRate.ToString("#0.0000")};";
                        }
                    }
                    info.Reality_OPAmountDesc = opDesc;
                    #endregion

                    #region 5：商邀 81 
                    var oaDatas = ccpData.Where(x => x.DIId == info.Id && x.CTable == 81 && x.PayMoney != 0).ToList();
                    var oaDesc = "-";
                    if (oaDatas.Count > 0)
                    {
                        oaDesc = $"实际币种金额:";
                        foreach (var oa in oaDatas)
                        {
                            var currencyCode = currencyData.Find(x => x.Id == oa.PaymentCurrency)?.Name;
                            oaDesc += $"{oa.PayMoney} {currencyCode} 汇率:{oa.DayRate.ToString("#0.0000")};";
                        }
                    }
                    info.Reality_OAAmountDesc = oaDesc;
                    #endregion

                    #region 6：其他款项(保险 82、其他款项 98)
                    var cTables = new List<int>() { 82, 98 };
                    var otherDatas = ccpData.Where(x => x.DIId == info.Id && cTables.Contains(x.CTable) && x.PayMoney != 0).ToList();
                    var otherDesc = "-";
                    if (otherDatas.Count > 0)
                    {
                        otherDesc = $"实际币种金额:";
                        foreach (var other in otherDatas)
                        {
                            var currencyCode = currencyData.Find(x => x.Id == other.PaymentCurrency)?.Name;
                            otherDesc += $"{other.PayMoney} {currencyCode} 汇率:{other.DayRate.ToString("#0.0000")};";
                        }
                    }
                    info.Reality_OtherCostAmountDesc = otherDesc;
                    #endregion
                }
            }

            var briefData = _mapper.Map<List<StatisticsBRCostBriefView>>(data);
            return Ok(JsonView(briefData, total));
        }

        /// <summary>
        ///  团组数据统计
        ///  当前时间团组所在城市   
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost("StatisticsNowCityOfGroup")]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> StatisticsNowCityOfGroup(StatisticsNowCityOfGroupDto dto)
        {

            var dt1 = GetCountryOrCityCurrentDateTime("美国", "纽约");
            var dt2 = GetCountryOrCityCurrentDateTime("日本", "东京");
            var dt3 = GetCountryOrCityCurrentDateTime("加拿大", "多伦多");
            var dt4 = GetCountryOrCityCurrentDateTime("韩国", "首尔");
            var dt5 = GetCountryOrCityCurrentDateTime("美国", "华盛顿");
            var dt6 = GetCountryOrCityCurrentDateTime("印度", "孟买");



            if (!SharingStaticData.PortTypes.Contains(dto.PortType)) return Ok(JsonView(false, MsgTips.Port));

            var nowLable = DateTime.Now.ToString("yyyy-MM-dd");

            var sql = string.Format(@"SELECT * FROM Grp_DelegationInfo WHERE ISdel = 0  AND '{0}' BETWEEN VisitStartDate AND VisitEndDate", nowLable);
            var groupInfos = await _sqlSugar.SqlQueryable<Grp_DelegationInfo>(sql)
                .Select(x => new { x.Id, x.VisitCountry, x.TeamName,x.ClientUnit, x.VisitStartDate, x.VisitEndDate }).
                ToListAsync();

            if (!groupInfos.Any()) return Ok(JsonView(false, "今日暂无团组出行!"));

            var groupIds = groupInfos.Select(x => x.Id).ToList();

            //三字码
            var threeDatas = await _sqlSugar.Queryable<Res_ThreeCode>().Where(x => x.IsDel == 0).ToListAsync();

            //国家
            var countryDatas = await _sqlSugar.Queryable<Grp_NationalTravelFee>().Where(x => x.IsDel == 0 && !string.IsNullOrEmpty(x.Country) && !string.IsNullOrEmpty(x.City)).ToListAsync();

            //公务
            var OADatas = await _sqlSugar
                .Queryable<Res_OfficialActivities>().Where(x => x.IsDel == 0 && groupIds.Contains(x.DiId) && !string.IsNullOrEmpty(x.Country) && !string.IsNullOrEmpty(x.Area))
                .ToListAsync();

            //酒店
            var hotelDatas = await _sqlSugar.Queryable<Grp_HotelReservations>()
                .LeftJoin<Grp_NationalTravelFee>((hr, ntf) => hr.AreaId == ntf.Id)
                .Where((hr, ntf) => hr.IsDel == 0 && groupIds.Contains(hr.DiId))
                .Select((hr, ntf) => new { 
                    hr.DiId,
                    hr.AreaId,
                    hr.HotelName,
                    hr.CheckInDate,
                    hr.CheckOutDate,
                    hr.HotelAddress,
                    ntf.Country,
                    ntf.City
                })
                .ToListAsync();

            //机票
            /*
            *舱位类型
            *457   头等舱
            *458   公务舱
            *459   超经舱
            *460   经济舱
            *461   退票
            *565   其他
            *574   长段公务舱
            *575   短途经济舱
            *1023  行程单打印费
            *1024  选座费
            */
            var classTypes = new int[] { 457, 458, 459, 460, 574, 575 };

            var airDatas = await _sqlSugar.Queryable<Grp_AirTicketReservations>()
                .Where(atr => atr.IsDel == 0 && groupIds.Contains(atr.DIId) && classTypes.Contains(atr.CType))
                .ToListAsync();

            string[] characters = { "|", "、" };

            var result = new List<StatisticsNowCityOfGroupResultView>();

            foreach (var item in groupInfos)
            {
                string statusText = string.Empty,
                       address = string.Empty;
                var countrys = item.VisitCountry;
                if (string.IsNullOrEmpty(countrys)) continue;

                var currCountrys = item.VisitCountry.Split(characters, StringSplitOptions.RemoveEmptyEntries);

                var stutus = true;

                #region 公务
                var currOADatas = OADatas.Where(x => x.DiId == item.Id && currCountrys.Any(c => x.Country.Contains(c))).ToList();
                if (currOADatas.Any())
                {
                    foreach (var curr_oa in currOADatas)
                    {
                        var localTime = GetCountryOrCityCurrentDateTime(curr_oa.Country, curr_oa.Area);
                        if (localTime == null) continue;

                        var currOaDtStart = Convert.ToDateTime($"{Convert.ToDateTime(curr_oa.Date).ToString("yyyy-MM-dd")} {curr_oa.Time}");
                        var currOaDtEnd = currOaDtStart.AddHours(2);

                        var currTimeZoneStatus = localTime >= currOaDtStart && localTime <= currOaDtEnd;
                        if (!currTimeZoneStatus) continue;

                        stutus = false;
                        statusText = "公务活动中";
                        address = curr_oa.Address;
                        break;
                    }
                }
                #endregion

                #region 酒店
                var currHotelDatas = hotelDatas.Where(x => x.DiId == item.Id && !string.IsNullOrEmpty(x.Country) && currCountrys.Any(c => x.Country.Contains(c))).ToList();
                if (stutus && hotelDatas.Any())
                {
                    foreach (var curr_hotel in currHotelDatas)
                    {
                        var checkInDt = Convert.ToDateTime(curr_hotel.CheckInDate);
                        List<(DateTime start, DateTime end)> timeRanges = new List<(DateTime, DateTime)>();
                        while (checkInDt <= Convert.ToDateTime(curr_hotel.CheckOutDate))
                        {
                            int year = checkInDt.Year,
                                month = checkInDt.Month,
                                day = checkInDt.Day;

                            timeRanges.Add((new DateTime(year, month, day, 0, 0, 0), new DateTime(year, month, day, 9, 0, 0)));
                            timeRanges.Add((new DateTime(year, month, day, 18, 0, 0), new DateTime(year, month, day, 23, 59, 59)));

                            checkInDt = checkInDt.AddDays(1);
                        }
                        if (!timeRanges.Any()) continue;

                        if (string.IsNullOrEmpty(curr_hotel.Country) && string.IsNullOrEmpty(curr_hotel.City)) continue;

                        var countryInfo = countryDatas.Where(x => x.Id == curr_hotel.AreaId).FirstOrDefault();
                        if (countryInfo == null) continue;

                        var localTime = GetCountryOrCityCurrentDateTime(countryInfo.Country, countryInfo.City);

                        var currTimeZoneStatus = timeRanges.Any(range => localTime >= range.start && localTime <= range.end);
                        if (!currTimeZoneStatus) continue;
                        stutus = false;
                        statusText = "入住酒店中";
                        address = curr_hotel.HotelAddress;
                        break;
                    }
                }
                #endregion

                #region 机票--通过三字码解析处理

                var airTicketDatas = GetTableByBlackCode(item.Id);

                if (stutus && airTicketDatas != null)
                {

                    foreach (DataRow curr_airTicket in airTicketDatas.Rows)
                    {

                        string departDate = curr_airTicket["Date"].ToString(),       //出发日期
                               departThree = curr_airTicket["Three"].ToString(),     //城市三字码
                               departTime = curr_airTicket["StartTime"].ToString(),  //出发时刻
                               departTerminal = curr_airTicket["StartBuilding"].ToString(), //出发航站楼
                               arriveTerminal = curr_airTicket["EndBuilding"].ToString(),   //抵达航站楼
                               arrivedDate = curr_airTicket["ArrivedDate"].ToString(),   //抵达日期
                               arrivedTime = curr_airTicket["EndTime"].ToString(),   //抵达日期
                               flightTime = curr_airTicket["FlightTime"].ToString();        //飞行时间

                        if (string.IsNullOrEmpty(departThree)) continue;

                        string departAirThree = departThree.Substring(0, 3),
                               arriveAirThree = departThree.Substring(3, 3);
                        string departCountry = string.Empty,
                               departCity = string.Empty,
                               departAirport = string.Empty,
                               arriveCountry = string.Empty,
                               arriveCity = string.Empty,
                               arriveAirport = string.Empty;

                        var departCountryInfo = threeDatas.Find(x => x.Three.Equals(departAirThree));
                        var arriveCountryInfo = threeDatas.Find(x => x.Three.Equals(arriveAirThree));
                        if (departCountryInfo == null || arriveCountryInfo == null) continue;

                        departCountry = departCountryInfo.Country;
                        departCity = departCountryInfo.City;
                        departAirport = departCountryInfo.AirPort;
                        arriveCountry = arriveCountryInfo.Country;
                        arriveCity = arriveCountryInfo.City;
                        arriveAirport = arriveCountryInfo.AirPort;

                        var arriveDateTimeBool = DateTime.TryParse(arrivedDate + " " + arrivedTime.Insert(2, ":"),out DateTime arriveDateTime);

                        if (!arriveDateTimeBool) continue;

                        var filghtHoursBool = int.TryParse(flightTime.Split("H")[0].ToString(), out int filghtHours);
                        var filghtMinutesBool= int.TryParse(flightTime.Split("H")[1].Remove(2).ToString(), out int filghtMinutes);
                        if (!filghtHoursBool || !filghtHoursBool) continue;

                        var departDateTime = arriveDateTime.AddHours(-filghtHours).AddMinutes(-filghtMinutes);

                        var localTime = GetCountryOrCityCurrentDateTime(arriveCountry,arriveCity);

                        var currTimeZoneStatus = localTime >= departDateTime && localTime <= arriveDateTime;
                        if (!currTimeZoneStatus) continue;
                        stutus = false;
                        statusText = $"正飞往{arriveCity}城市中";
                        address = $"{arriveCountry}{arriveAirport}{arriveTerminal}";
                        break;
                    }
                }

                if (stutus) statusText = $"其他";

                #endregion
                result.Add(new StatisticsNowCityOfGroupResultView()
                {
                    GroupName = item.TeamName,
                    Client = item.ClientUnit,
                    StatusText = statusText,
                    Address = address
                });
            }

            result.AddRange(new List<StatisticsNowCityOfGroupResultView>() {
                new StatisticsNowCityOfGroupResultView(){ GroupName = "模拟数据团组1",Client = "模拟数据单位1",StatusText = "正在公务中",Address = "日本〒104-0032 Tokyo, Chuo City, Hatchobori, 1 Chome−12−8 ＥＡＳＴ２階" },
                new StatisticsNowCityOfGroupResultView(){ GroupName = "模拟数据团组2",Client = "模拟数据单位2",StatusText = "入住酒店中",Address = "日本〒104-0032 Tokyo, Chuo City, Hatchobori, 1 Chome−12−8 ＥＡＳＴ２階" },
                new StatisticsNowCityOfGroupResultView(){ GroupName = "模拟数据团组3",Client = "模拟数据单位3",StatusText = "其他",Address = "日本〒104-0032 Tokyo, Chuo City, Hatchobori, 1 Chome−12−8 ＥＡＳＴ２階" },
            });


            var result1 = result.GroupBy(x => x.Address).Select(g => new
            {
                address = g.Key,
                Items = g.ToList().Select(gg => new { gg.GroupName, gg.Client, gg.StatusText }).ToList()
            });


            return Ok(JsonView(result1));
        }

        private StatisticsNowCityOfGroupView CitySplit(string cityLable, List<Res_ThreeCode> cities)
        {
            var info = new StatisticsNowCityOfGroupView();
            if (string.IsNullOrEmpty(cityLable)) return info;

            var cityArray = cityLable.Split("-");
            var departCityCode = cityArray[0];
            var arriveCityCode = cityArray[1];

            var departAirportInfo = cities.Find(x => x.Three.Equals(departCityCode.ToUpper()));
            info.DepartAirportCode = departCityCode;
            info.DepartCountryName = departAirportInfo?.Country ?? "-";
            info.DepartCityName = departAirportInfo?.City ?? "-";

            var arriveAirportInfo = cities.Find(x => x.Three.Equals(arriveCityCode.ToUpper()));
            info.ArriveAirportCode = arriveCityCode;
            info.ArriveCountryName = arriveAirportInfo?.Country ?? "-";
            info.ArriveCityName = arriveAirportInfo?.City ?? "-";

            return info;
        }

        #endregion
    }
}