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

using Microsoft.AspNetCore.Mvc;
using NPOI.POIFS.Properties;
using NPOI.SS.Formula.Functions;
using OASystem.API.OAMethodLib;
using OASystem.Domain.Dtos.Groups;
using OASystem.Domain.Dtos.Statistics;
using OASystem.Domain.Entities.Financial;
using OASystem.Domain.ViewModels.Financial;
using OASystem.Domain.ViewModels.Groups;
using OASystem.Domain.ViewModels.Statistics;
using OASystem.Infrastructure.Repositories.Groups;
using static OpenAI.GPT3.ObjectModels.SharedModels.IOpenAiModels;
using System;
using OASystem.Domain.Entities.Customer;
using System.Collections.Generic;
using Microsoft.AspNetCore.Mvc.RazorPages;
using Microsoft.VisualBasic;
using OASystem.Domain.Entities.Groups;
using Microsoft.Extensions.DependencyInjection;
using Aspose.Words.Lists;
using static OASystem.API.OAMethodLib.GeneralMethod;
using TypeInfo = OASystem.Domain.ViewModels.Statistics.TypeInfo;
using System.Net.NetworkInformation;

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

        /// <summary>
        /// Init
        /// </summary>
        /// <param name="mapper"></param>
        /// <param name="sqlSugar"></param>
        /// <param name="groupRep"></param>
        /// <param name="setDataRep"></param>
        public StatisticsController(IMapper mapper, SqlSugarClient sqlSugar, DelegationInfoRepository groupRep, SetDataRepository setDataRep, TeamRateRepository teamRate)
        {
            _mapper = mapper;
            _groupRep = groupRep;
            _setDataRep = setDataRep;
            _sqlSugar = sqlSugar;
            _teamRateRep = teamRate;
        }


        #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).ToListAsync();

                #endregion

                #region 团组收入

                GroupIncomeView _giView = new GroupIncomeView();

                /*
                 * 应收报表
                 */
                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", _dto.DiId);
                List<Gsd_ForeignReceivablesView> _frViews = await _sqlSugar.SqlQueryable<Gsd_ForeignReceivablesView>(_frSql).ToListAsync();
                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.ToString("#0.00")} CNY  已收款合计：{prTotalAmount.ConvertToDecimal1().ToString("#0.00")} CNY");


                /*
                 * 超支费用 
                 */
                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", _dto.DiId);

                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.*,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", _dto.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);
                }

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

                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.PayMoney<> 0 And hr.DiId = {0} 
                                                     Order By CheckInDate Asc", _dto.DiId);

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

                decimal HotelCNYTotalPrice = 0.00M;

                var teamRateData = await _teamRateRep.PostGroupRateInfoByDiId(_dto.DiId);
                foreach (var item in groupHotelFeeViews)
                {
                    HotelCNYTotalPrice += item.CNYPrice;

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

                    string currencyRateStr = "";
                    List<string> currencys = new List<string>();
                    if (!string.IsNullOrEmpty(item.BreakfastCurrency)) currencys.Add(item.BreakfastCurrency);
                    if (!string.IsNullOrEmpty(item.GovernmentRentCurrency)) currencys.Add(item.GovernmentRentCurrency);
                    if (!string.IsNullOrEmpty(item.CityTaxCurrency)) currencys.Add(item.CityTaxCurrency);
                    if (!string.IsNullOrEmpty(item.PaymentCurrency)) currencys.Add(item.PaymentCurrency);

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

                    item.CurrencyRateStr = currencyRateStr;
                }

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

                #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 ctggr.DiId = {0}
                                                     Order By CreateTime", _dto.DiId);

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

                    //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 机票预订费用

                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,
                                                        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 And ccp.IsAuditGM = 1 And atr.DiId = {0} Order By CreateTime", _dto.DiId);
                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;
                                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;
                        unitCost = (item.PayMoney / item.ClientNum).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
                                                         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 vi.DIId = {0} Order By CreateTime", _dto.DiId);

                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))
                    {
                        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 = visaClients;
                            continue;
                        }

                        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.PayMoney;
                    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());
                #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  ioa.Diid = {0} Order By CreateTime", _dto.DiId);

                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.CNYPrice;

                    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);
                #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 ic.DiId = {0} Order By CreateTime", _dto.DiId);
                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 And ccp.IsAuditGM = 1 And dp.Diid = {0} 
                                                             Order By CreateTime", _dto.DiId);
                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, "查询成功"));
            }
        }

        #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(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

    }
}