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;
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)
{
#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.ProceedsReceivedViews = _prViews;
_giView.ProceedsReceivedStr = string.Format(@"已收款合计：{0} CNY(人名币)", prTotalAmount.ConvertToDecimal1().ToString("#0.00"));
/*
* 超支费用
*/
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
From Fin_GroupExtraCost gec
Left Join Grp_CreditCardPayment ccp On ccp.isdel = 0 And ccp.CTable = 81 And 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 gec.IsDel = 0 And ccp.IsAuditGM = 1 And ccp.IsPay = 1 And gec.DiId = {0}",_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>();
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,
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.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;
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.*,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 Applicant
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().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
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 = "";
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))
{
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();
string[] clientNames = item.ClientName.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","");
}
}
clientPinYinName += string.Format(@"{0}.{1}出票价为：{2} CNY;", Index, name, unitCost);
}
}
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
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)
{
string itemClientName = "";
string visaClients = item.VisaClient;
if (!string.IsNullOrEmpty(visaClients))
{
string[] clientIds = new string[] { };
if (visaClients.Contains(','))
{
clientIds = visaClients.Split(',');
}
else
{
clientIds = new string[] { visaClients };
}
if (clientIds.Length > 0)
{
List<int> clientIds1 = new List<int>() { };
foreach (var clientIdStr in clientIds)
{
if (clientIdStr.IsNumeric())
{
clientIds1.Add(int.Parse(clientIdStr));
}
}
if (clientIds1.Count > 0)
{
var clients = _clientDatas.Where(it => clientIds1.Contains(it.Id)).ToList();
foreach (var client in clients)
{
itemClientName += $"{client.LastName + client.FirstName},";
}
}
else {
itemClientName = visaClients;
}
}
}
if (itemClientName.Length > 0 )
{
itemClientName = itemClientName.Substring(0, itemClientName.Length - 1);
}
item.VisaClient = itemClientName;
VisaCNYTotalPirce += item.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
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}",_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
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;
string itemClientName = "";
string insClients = item.ClientName;
if (!string.IsNullOrEmpty(insClients))
{
string[] clientIds = new string[] { };
if (insClients.Contains(','))
{
clientIds = insClients.Split(',');
}
else
{
clientIds = new string[] { insClients };
}
if (clientIds.Length > 0)
{
List<int> output = new List<int>();
foreach (var clientId in clientIds)
{
if (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 其他款项费用
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.RMBPrice As CNYPrice,ccp.DayRate,ccp.Payee,ccp.AuditGMDate,ccp.OrbitalPrivateTransfer,
sd2.Name As PayWay,ccp.IsPay,u.CnName As Applicant
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.IsAuditGM = 1 And ccp.IsPay = 1 And dp.Diid = {0}",_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)
{
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
decimal _totalExpenditure = 0.00M; //总支出
decimal _amountReceivable = 0.00M; //应收金额
decimal _amountReceived = 0.00M; //已收金额
decimal _profit = 0.00M; //利润
_totalExpenditure = HotelCNYTotalPrice + CTGGRCNYTotalPrice + AirCNYTotalPrice + VisaCNYTotalPirce + InvitationalCNYTotalPrice +
InsuranceCNYTotalPrice + DecreaseCNYTotalPrice + exTotalAmount;
_amountReceivable = frTotalAmount;
_amountReceived = prTotalAmount;
_profit = _amountReceived - _totalExpenditure;
_view.FeeTotalStr = string.Format(@"<span>
<span>当前总支出：{0} CNY</span>
<span style='padding-left:10px;color: Green;'>应收金额：{1} CNY</span>
<span style='padding-left:10px;color: Green;'>已收金额：{2} CNY</span>
<span style='padding-left:10px;color: Green;'>利润(收入-支出)：{3} CNY</span>
</span>", _totalExpenditure, _amountReceivable, _amountReceived, _profit);
return Ok(JsonView(true, "查询成功!", _view));
}
else
{
return Ok(JsonView(false, "查询成功"));
}
}
#endregion
}
}