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;

namespace OASystem.API.Controllers
{
    /// <summary>
    /// 统计模块
    /// </summary>
    [Route("api/[controller]")]
    [ApiController]
    public class StatisticsController : ControllerBase
    {

        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")]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> PostGroupStatementDetails(GroupStatementDetailsDto _dto)
        {
            #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
										        From Fin_ForeignReceivables fr
										        Left Join Sys_SetData sd On fr.Currency = sd.Id
										        Where fr.IsDel = 0 And fr.Diid = {0}",_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,pr.Price,pr.Currency,
										        sd1.Name As CurrencyCode,sd1.Remark As CurrencyName,pr.Client,
										        pr.ReceivablesType,sd2.Name As ReceivablesTypeName,pr.Remark
										        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}", _dto.DiId);
                List<Gsd_ProceedsReceivedView> _prViews = await _sqlSugar.SqlQueryable<Gsd_ProceedsReceivedView>(_prSql).ToListAsync();
                prTotalAmount = _prViews.Sum(it => it.Price);
                _giView.ProceedsReceiveds = _prViews;
                _giView.ProceedsReceivedStr = string.Format(@"已收款合计：{0} CNY(人名币)", prTotalAmount.ConvertToDecimal1().ToString("#0.00"));


                /*
                 * 超支费用 
                 * 未挪表
                 */

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

                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.*
										         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 prom.PriceType = 1 And ccp.CTable = 285
										         And ccp.IsAuditGM = 1 And ccp.IsPay = 1
										         And prom.DiId =   {0}", _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,
                        FeeType = orbitalPrivateTransferStr,
                        PayType = payStr,
                        PayStatus = ropItem.IsPay == 0 ? "未付款" : "已付款",
                        Applicant = ropItem.Appliction

                    };

                    _promView.Add(gsd_PaymentRefund);
                }

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

                decimal BalancePayment = frTotalAmount - prTotalAmount + promTotalAmount;
                _view.GroupIncome = _giView;
                _view.GroupIncomeStr = string.Format(@"剩余尾款：{0} (包含了收款退还费用数据)", 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 OpName
                                                     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}", _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;

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

                    if (!string.IsNullOrEmpty(item.BreakfastCurrency)) currencys.Add(item.BreakfastCurrency);

                    if (!string.IsNullOrEmpty(item.GovernmentRentCurrency)) currencys.Add(item.GovernmentRentCurrency);

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

                #endregion

                #region 地接费用

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

                string CTGGRFeeSql = string.Format(@"Select ctggr.Id As CTGGRId,ctggr.DiId As CTGGRDiId,ctggr.Area,ctggrc.*,ccp.PayMoney,
                                                     sd2.name As PaymentCurrency,ccp.PayPercentage,(ccp.PayMoney * ccp.PayPercentage / 100) As AmountPaid,
                                                     (ccp.PayMoney -(ccp.PayMoney * ccp.PayPercentage / 100)) As BalancePayment,ccp.DayRate,
                                                     ccp.RMBPrice As CNYPrice,ccp.Payee,ccp.AuditGMDate,
                                                     ccp.OrbitalPrivateTransfer,sd1.Name As PayWay,ccp.IsPay,u.CnName As OpName
                                                     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} ",_dto.DiId);

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

                string CTGGRFeeStr = "";
                decimal CTGGRCNYTotalPrice = 0.00M;
                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(), item.PaymentCurrency, item.CNYPrice, item.DayRate);
                    CTGGRCNYTotalPrice += item.CNYPrice;
                }
                _geView.GroupCTGGRFeeViews = groupCTGGRFeeViews;

                _geView.GroupCTGGRFeeStr = string.Format(@"{0}人名币总费用：{1} CNY", CTGGRFeeStr,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 OpName
                                                        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} ", _dto.DiId);
                groupAirFeeViews = await _sqlSugar.SqlQueryable<GroupAirFeeView>(groupAirFeeSql).ToListAsync();

                string str = "";
                decimal AirCNYTotalPrice = 0.00M;
                decimal JJCCNYTotalPrice = 0.00M, JJCPeopleNum = 0.00M, JJCAveragePrice = 0.00M;
                decimal GWCCNYTotalPrice = 0.00M, GWCPeopleNum = 0.00M, GWCAveragePrice = 0.00M;

                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)
                {
                    string itemClientName = "";
                    if (!string.IsNullOrEmpty(item.ClientName))
                    {
                        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); });
                            var clients = _clientDatas.Where(it => output.Contains(it.Id)).ToList();
                            string clientPinYinName = "";
                            decimal unitCost = 0.00M;
                            unitCost = (item.PayMoney / item.ClientNum).ConvertToDecimal1();
                            foreach (var client in clients)
                            {
                                Index += 1;
                                int clienIndex = 1;
                                itemClientName += string.Format(@"{0}.{1} ;", clienIndex, client);
                                clientPinYinName += string.Format(@"{0}.{1}出票价为：{2} CNY;", Index,client.Pinyin, unitCost);
                                clienIndex++;
                            }
                        }
                    }
                    item.ClientName = itemClientName;

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

                _geView.GroupAirFeeViews = groupAirFeeViews;
                if (!string.IsNullOrEmpty(str))
                {
                    str = string.Format(@"其中：{0}", str);
                }

                _geView.GroupAirFeeStr = string.Format(@"人名币总费用：{0} CNY\r\n{1}\r\n经济舱均价为：{2}CNY/人;公务舱均价为：{3}CNY/人;", AirCNYTotalPrice, str, JJCAveragePrice, GWCAveragePrice);
                #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 OpName
                                                         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}",_dto.DiId);

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

                decimal VisaCNYTotalPirce = 0.00M;

                foreach (var item in groupVisaFeeViews)
                {
                    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 邀请/公务活动费用 InvitationalService

                #endregion

                #region 保险费用 insurance
                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 OpName
                                                              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}", _dto.DiId);
                groupInsuranceFeeViews = await _sqlSugar.SqlQueryable<GroupInsuranceFeeView>(groupInsuranceFeeSql).ToListAsync();

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

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

                #region 其他款项费用 OtherMoney

                #endregion

                _view.GroupExpenditure = _geView;
                #endregion


                return Ok(JsonView(true, "查询成功!", _view));
            }
            else
            {
                return Ok(JsonView(false, "查询成功"));
            }
        }

        #endregion

    }
}