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;
using System.ComponentModel.Design;
using NetTaste;
using OASystem.Domain.ViewModels.QiYeWeChat;
using NPOI.POIFS.Crypt.Dsig;
using EyeSoft.SequentialIdentity;
using Microsoft.Extensions.Configuration;
using System.Text.Json;
using OASystem.RedisRepository;
using OASystem.RedisRepository.RedisAsyncHelper;
using System.Data;
using OfficeOpenXml.Export.ToDataTable;
using Aspose.Cells;
namespace OASystem.API.Controllers
{
/// <summary>
/// 统计模块
/// </summary>
[Route("api/[controller]")]
[ApiController]
public class StatisticsController : ControllerBase
{
private readonly int _decimalPlaces;
private readonly IConfiguration _config;
private readonly IMapper _mapper;
private readonly SqlSugarClient _sqlSugar;
private readonly DelegationInfoRepository _groupRep;
private readonly SetDataRepository _setDataRep;
private readonly TeamRateRepository _teamRateRep;
private readonly VisitingClientsRepository _visitingClientsRep;
/// <summary>
/// Init
/// </summary>
/// <param name="mapper"></param>
/// <param name="sqlSugar"></param>
/// <param name="groupRep"></param>
/// <param name="setDataRep"></param>
public StatisticsController(IMapper mapper, IConfiguration config, SqlSugarClient sqlSugar, DelegationInfoRepository groupRep, SetDataRepository setDataRep, TeamRateRepository teamRate, VisitingClientsRepository visitingClientsRep)
{
_mapper = mapper;
_config = config;
_groupRep = groupRep;
_setDataRep = setDataRep;
_sqlSugar = sqlSugar;
_teamRateRep = teamRate;
_visitingClientsRep = visitingClientsRep;
}
#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.AddingWay,fr.Status,u.CnName As Auditor,fr.AuditTime,fr.Diid,fr.PriceName,fr.Price,fr.Count,fr.Unit,fr.Currency,
sd.Name As CurrencyCode,sd.Remark As CurrencyName,fr.Rate,fr.ItemSumPrice,fr.CreateTime
From Fin_ForeignReceivables fr
Left Join Sys_SetData sd On fr.Currency = sd.Id
Left Join Sys_Users u On fr.Auditor = u.Id
Where fr.IsDel = 0 And fr.Diid = {0} Order By CreateTime", _dto.DiId);
List<Gsd_ForeignReceivablesView> _frViews = await _sqlSugar.SqlQueryable<Gsd_ForeignReceivablesView>(_frSql).ToListAsync();
//List<Gsd_ForeignReceivablesView> _frViews = new List<Gsd_ForeignReceivablesView>();
//if (_frViews1.Count > 0)
//{
// _frViews.AddRange(_frViews1.Where(x => x.AddingWay != 2).ToList());
// _frViews.AddRange(_frViews1.Where(x => x.AddingWay == 2 && x.Status == 1).ToList());
//}
_frViews.ForEach(x =>
{
string namePrefix = string.Empty;
if (x.AddingWay == 0) namePrefix = $"账单模块-";
else if (x.AddingWay == 1) namePrefix = $"成本预算模块-";
else if (x.AddingWay == 2) namePrefix = $"实际报价-";
x.PriceName = $"{namePrefix}{x.PriceName}";
});
frTotalAmount = _frViews.Sum(it => it.ItemSumPrice);
_giView.Receivables = _frViews;
_giView.ReceivableStr = string.Format(@"应收款合计：{0} CNY(人民币)", frTotalAmount.ConvertToDecimal1().ToString("#0.00"));
/*
* 已收报表
*/
decimal prTotalAmount = 0.00M;//已收总金额
string _prSql = string.Format(@"Select pr.Id,pr.Diid,pr.SectionTime As SectionTimeDt,pr.Price,pr.Currency,
sd1.Name As CurrencyCode,sd1.Remark As CurrencyName,pr.Client,
pr.ReceivablesType,sd2.Name As ReceivablesTypeName,pr.Remark,pr.CreateTime
From Fin_ProceedsReceived pr
Left Join Sys_SetData sd1 On pr.Currency = sd1.Id
Left Join Sys_SetData sd2 On pr.ReceivablesType = sd2.Id
Where pr.IsDel = 0 and pr.Diid = {0} Order By CreateTime", _dto.DiId);
List<Gsd_ProceedsReceivedView> _prViews = await _sqlSugar.SqlQueryable<Gsd_ProceedsReceivedView>(_prSql).ToListAsync();
prTotalAmount = _prViews.Sum(it => it.Price);
_giView.ProceedsReceivedViews = _prViews;
_giView.ProceedsReceivedStr = string.Format(@$"应收合计：{frTotalAmount.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,(gec.PriceSum * gec.Coefficient) As PayMoney,sd1.Name As PaymentCurrency,
(gec.PriceSum * gec.Coefficient * ccp.DayRate) As CNYPrice,ccp.DayRate,ccp.Payee,ccp.OrbitalPrivateTransfer,sd2.Name As PayWay,
sd3.Name As CardType,ccp.IsPay,u.CnName As Applicant,gec.CreateTime
From OA2023DB.dbo.Fin_GroupExtraCost gec
Left Join Grp_CreditCardPayment ccp On gec.Id = ccp.CId
Left Join Sys_SetData sd1 On ccp.PaymentCurrency = sd1.Id
Left Join Sys_SetData sd2 On ccp.PayDId = sd2.Id
Left Join Sys_SetData sd3 On ccp.CTDId = sd3.Id
Left Join Sys_Users u On ccp.CreateUserId = u.Id
Where ccp.IsDel = 0 And ccp.CTable = 1015 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>();
//ccp.RMBPrice As CNYPrice
//(((ccp.PayMoney * ccp.DayRate) / ccp.PayPercentage) * 100) As CNYPrice
string hotelFeeSql = string.Format(@"Select hr.Id As HrId,hr.DiId As HrDiId,hr.City,hr.HotelName,hr.CheckInDate,hr.CheckOutDate,hr.CardPrice As RoomPrice,
sd1.Name As PaymentCurrency,hr.SingleRoomPrice,hr.SingleRoomCount,hr.DoubleRoomPrice,
hr.DoubleRoomCount,hr.SuiteRoomPrice,hr.SuiteRoomCount,hr.OtherRoomPrice,hr.OtherRoomCount,
hr.BreakfastPrice,sd4.Name As BreakfastCurrency,hr.Isoppay,hr.GovernmentRent,
sd5.Name As GovernmentRentCurrency,hr.CityTax,sd6.Name As CityTaxCurrency,
ccp.PayMoney,(((ccp.PayMoney * ccp.DayRate) / ccp.PayPercentage) * 100) As CNYPrice,ccp.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();
List<int> hotelSubIds = groupHotelFeeViews.Select(it => it.HrId).ToList();
List<Grp_HotelReservationsContent> groupHotelContentFeeViews = new List<Grp_HotelReservationsContent>();
groupHotelContentFeeViews = await _sqlSugar.Queryable<Grp_HotelReservationsContent>().Where(it => hotelSubIds.Contains(it.HrId)).ToListAsync();
decimal HotelCNYTotalPrice = 0.00M;
var teamRateData = await _teamRateRep.PostGroupRateInfoByDiId(_dto.DiId);
foreach (var item in groupHotelFeeViews)
{
if (groupHotelContentFeeViews.Count > 0)
{
string paymentStr = string.Empty;
var roomData = groupHotelContentFeeViews.Find(it => it.HrId == item.HrId && it.PriceType == 1); //房费
item.RoomPrice = roomData?.Price ?? 0.00M;
if (item.RoomPrice != 0)
{
if (roomData.IsPay == 0) paymentStr += $"房费：未付款<br/>";
item.RoomPriceCurrency = _setDatas.Find(it => it.Id == roomData?.Currency)?.Name;
string feeMark1 = roomData?.OrbitalPrivateTransfer == 0 ? "公转" : "私转";
string isFeeMark1 = roomData?.IsOppay == 1 ? "是" : "否";
item.RoomInfoTips = @$"当时汇率：{roomData?.Rate.ToString("#0.0000")} <br/>
收款方：{roomData?.Payee}<br/>
费用标识：{feeMark1} <br/>
支付方式：{_setDatas.Find(it => it.Id == roomData?.PayDId)?.Name} <br/>
卡类型：{_setDatas.Find(it => it.Id == roomData?.CTDId)?.Name}<br/>
是否由地接支付：{isFeeMark1} <br/>";
}
var breakfastData = groupHotelContentFeeViews.Find(it => it.HrId == item.HrId && it.PriceType == 2); //早餐
item.BreakfastPrice = breakfastData?.Price ?? 0.00M;
if (item.BreakfastPrice != 0)
{
if (breakfastData.IsPay == 0) paymentStr += $"早餐：未付款<br/>";
item.BreakfastCurrency = _setDatas.Find(it => it.Id == breakfastData?.Currency)?.Name;
string feeMark2 = breakfastData?.OrbitalPrivateTransfer == 0 ? "公转" : "私转";
string isFeeMark2 = breakfastData?.IsOppay == 1 ? "是" : "否";
item.BreakfastInfoTips = @$"当时汇率：{breakfastData?.Rate.ToString("#0.0000")} <br/>
收款方：{breakfastData?.Payee}<br/>
费用标识：{feeMark2} <br/>
支付方式：{_setDatas.Find(it => it.Id == roomData?.PayDId)?.Name} <br/>
卡类型：{_setDatas.Find(it => it.Id == roomData?.CTDId)?.Name} <br/>
是否由地接支付：{isFeeMark2} <br/>";
}
var landTaxData = groupHotelContentFeeViews.Find(it => it.HrId == item.HrId && it.PriceType == 3); //地税
item.GovernmentRent = landTaxData?.Price ?? 0.00M;
if (item.GovernmentRent != 0)
{
if (landTaxData.IsPay == 0) paymentStr += $"地税：未付款<br/>";
item.GovernmentRentCurrency = _setDatas.Find(it => it.Id == landTaxData?.Currency)?.Name;
string feeMark3 = landTaxData?.OrbitalPrivateTransfer == 0 ? "公转" : "私转";
string isFeeMark3 = landTaxData?.IsOppay == 1 ? "是" : "否";
item.GovernmentRentTips = @$"当时汇率：{landTaxData?.Rate.ToString("#0.0000")} <br/>
收款方：{landTaxData?.Payee}<br/>
费用标识：{feeMark3} <br/>
支付方式：{_setDatas.Find(it => it.Id == landTaxData?.PayDId)?.Name} <br/>
卡类型：{_setDatas.Find(it => it.Id == landTaxData?.CTDId)?.Name} <br/>
是否由地接支付：{isFeeMark3} <br/>";
}
var cityTaxData = groupHotelContentFeeViews.Find(it => it.HrId == item.HrId && it.PriceType == 4); //城市税
item.CityTax = cityTaxData?.Price ?? 0.00M;
if (item.CityTax != 0)
{
if (cityTaxData.IsPay == 0) paymentStr += $"城市税：未付款<br/>";
item.CityTaxCurrency = _setDatas.Find(it => it.Id == cityTaxData?.Currency)?.Name;
string feeMark4 = cityTaxData?.OrbitalPrivateTransfer == 0 ? "公转" : "私转";
string isFeeMark4 = landTaxData?.IsOppay == 1 ? "是" : "否";
item.CityTaxTips = @$"当时汇率：{cityTaxData?.Rate.ToString("#0.0000")} <br/>
收款方：{cityTaxData?.Payee}<br/>
费用标识：{feeMark4} <br/>
支付方式：{_setDatas.Find(it => it.Id == cityTaxData?.PayDId)?.Name} <br/>
卡类型：{_setDatas.Find(it => it.Id == cityTaxData?.CTDId)?.Name} <br/>
是否由地接支付：{isFeeMark4} <br/>";
}
if (!string.IsNullOrEmpty(paymentStr))
{
item.IsPay = 2;
item.PayTips = paymentStr;
}
}
else
{
decimal roomPrice = (item.SingleRoomCount * item.SingleRoomPrice) +
(item.DoubleRoomCount * item.DoubleRoomPrice) +
(item.SuiteRoomCount * item.SuiteRoomPrice) +
(item.OtherRoomCount * item.OtherRoomPrice);
//item.RoomPrice = item.CardPrice;
item.RoomPriceCurrency = item.PaymentCurrency;
}
HotelCNYTotalPrice += item.CNYPrice;
item.PayMoney = item.PayMoney.ConvertToDecimal1();
item.CNYPrice = item.CNYPrice.ConvertToDecimal1();
}
_geView.GroupHotelFeeViews = groupHotelFeeViews;
_geView.GroupHotelFeeStr = string.Format(@"人民币总费用：{0} CNY", HotelCNYTotalPrice.ToString("#0.00"));
#endregion
#region 地接费用
List<GroupCTGGRFeeView> groupCTGGRFeeViews = new List<GroupCTGGRFeeView>();
//string CTGGRFeeSql = string.Format(@"Select ctggr.Id As CTGGRId,ctggr.DiId As CTGGRDiId,ctggr.PriceName As Area,ctggrc.*,ctggrc.Price As PayMoney,
// sd2.name As PaymentCurrency,ccp.PayPercentage,
// (ctggrc.Price / (ccp.PayPercentage / 100)) As AmountPaid,
// (ctggrc.Price / (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.DatePrice,cggrc.PriceContent
// From Grp_CarTouristGuideGroundReservationsContent cggrc
// Left Join Sys_SetData sd1 On cggrc.SId = sd1.Id
// Left Join Sys_SetData sd2 On cggrc.Currency = sd2.Id
// Where cggrc.ISdel = 0 And cggrc.Price != 0.00
// ) ctggrc On ctggr.Id = ctggrc.CTGGRId
// Left Join Grp_CreditCardPayment ccp On ccp.IsDel = 0 And ccp.CTable = 79 And ctggr.Id = ccp.CId
// Left Join Sys_SetData sd1 On ccp.PayDId = sd1.Id
// Left Join Sys_SetData sd2 On ccp.PaymentCurrency = sd2.Id
// Left Join Sys_Users u On ccp.CreateUserId = u.Id
// Where ctggr.IsDel = 0 And ccp.IsAuditGM = 1 And ctggr.DiId = {0}
// Order By CreateTime", _dto.DiId);
string CTGGRFeeSql = string.Format(@"Select ctggr.Id As CTGGRId,ctggr.DiId As CTGGRDiId,ctggr.PriceName As Area,ctggrc.*,ctggrc.Price As PayMoney,
sd2.name As PaymentCurrency,ccp.PayPercentage,
(ctggrc.Price * (ccp.PayPercentage / 100)) As AmountPaid,
(ctggrc.Price - ctggrc.Price * (ccp.PayPercentage / 100)) As BalancePayment,
ccp.DayRate,(ctggrc.Price * (ccp.PayPercentage / 100) * ccp.DayRate) As CNYPrice,ccp.Payee,ccp.AuditGMDate,
ccp.OrbitalPrivateTransfer,sd1.Name As PayWay,ccp.IsPay,u.CnName As Applicant,ctggr.CreateTime
From Grp_CarTouristGuideGroundReservations ctggr
Left Join ( Select cggrc.CTGGRId,sd1.Name As PriceName,cggrc.Price*cggrc.Count As Price,sd2.Name As PriceCurrency,
cggrc.DatePrice,cggrc.PriceContent
From Grp_CarTouristGuideGroundReservationsContent cggrc
Left Join Sys_SetData sd1 On cggrc.SId = sd1.Id
Left Join Sys_SetData sd2 On cggrc.Currency = sd2.Id
Where cggrc.ISdel = 0 And cggrc.Price != 0.00
) ctggrc On ctggr.Id = ctggrc.CTGGRId
Left Join Grp_CreditCardPayment ccp On ccp.IsDel = 0 And ccp.CTable = 79 And ctggr.Id = ccp.CId
Left Join Sys_SetData sd1 On ccp.PayDId = sd1.Id
Left Join Sys_SetData sd2 On ccp.PaymentCurrency = sd2.Id
Left Join Sys_Users u On ccp.CreateUserId = u.Id
Where ctggr.IsDel = 0 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");
}
if (item.DatePrice != null)
{
item.PriceName = $"{item.PriceName}({Convert.ToDateTime(item.DatePrice).ToString("yyyy-MM-dd")})";
}
//CTGGRFeeStr += string.Format(@"{0} 总费用：{1} {2}(人民币：{3} CNY 当时支付汇率：{4})\r\n",
// item.Area, item.AmountPaid.ConvertToDecimal1().ToString("#0.00"), item.PaymentCurrency, item.CNYPrice.ToString("#0.0000"), item.DayRate.ToString("#0.0000"));
//CTGGRCNYTotalPrice += item.CNYPrice;
}
_geView.GroupCTGGRFeeViews = groupCTGGRFeeViews;
_geView.GroupCTGGRFeeStr = string.Format(@"{0}人民币总费用：{1} CNY", CTGGRFeeStr, CTGGRCNYTotalPrice.ToString("#0.00"));
#endregion
#region 机票预订费用
//(((ccp.PayMoney * ccp.DayRate) / ccp.PayPercentage) * 100) As CNYPrice
//ccp.RMBPrice As CNYPrice
List<GroupAirFeeView> groupAirFeeViews = new List<GroupAirFeeView>();
string groupAirFeeSql = string.Format(@"Select atr.Id As AirId,atr.DIId As AirDiId,atr.FlightsCode,atr.FlightsCity,sd4.Name As AirTypeName,
atr.FlightsDate,atr.FlightsTime,atr.ClientName,atr.ClientNum,ccp.PayMoney,
sd1.Name As PayMoneyCurrency,(((ccp.PayMoney * ccp.DayRate) / ccp.PayPercentage) * 100) As CNYPrice,ccp.DayRate,ccp.Payee,ccp.AuditGMDate,
ccp.OrbitalPrivateTransfer,sd2.Name As PayWay,sd3.Name As CardType,ccp.IsPay,u.CnName As Applicant,atr.CreateTime,
atr.CType
From Grp_AirTicketReservations atr
Left Join Grp_CreditCardPayment ccp On ccp.isdel = 0 And ccp.CTable = 85 And atr.Id = ccp.CId
Left Join Sys_SetData sd1 On ccp.PaymentCurrency = sd1.Id
Left Join Sys_SetData sd2 On ccp.PayDId = sd2.Id
Left Join Sys_SetData sd3 On ccp.CTDId = sd3.Id
Left Join Sys_SetData sd4 On atr.CType = sd4.Id
Left Join Sys_Users u On ccp.CreateUserId = u.Id
Where atr.IsDel = 0 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;
decimal unitCost = 0.00M;
AirCNYTotalPrice += item.CNYPrice;
continue;
}
string[] clientIds = new string[] { };
if (item.ClientName.Contains(','))
{
clientIds = item.ClientName.Split(',');
}
else
{
clientIds = new string[] { item.ClientName };
}
if (clientIds.Length > 0)
{
int[] output = Array.ConvertAll<string, int>(clientIds, delegate (string s) { return int.Parse(s); });
if (output.Contains(-1))
{
itemClientName += $@"行程单";
output = output.Where(val => val != -1).ToArray();
}
var clients = _clientDatas.Where(it => output.Contains(it.Id)).ToList();
decimal unitCost = 0.00M;
unitCost = (item.PayMoney / item.ClientNum).ConvertToDecimal1();
int clienIndex = 1;
foreach (var client in clients)
{
airClientPris.Add(new
{
CnName = client.LastName + client.FirstName,
EnName = client.Pinyin,
Price = unitCost,
AirType = item.AirTypeName
});
string six = "";
if (client.Sex == 0) six = "Mr";
else if (client.Sex == 1) six = "Ms";
itemClientName += string.Format(@"{0}.{1} {2};", clienIndex, client.LastName + client.FirstName, six);
clienIndex++;
}
}
}
item.ClientName = itemClientName;
}
else
{
string clientPinYinName = "";
decimal unitCost = 0.00M;
int cNum = item.ClientNum == 0 ? 1 : item.ClientNum;
unitCost = (item.PayMoney / cNum).ConvertToDecimal1();
Regex r = new Regex("[0-9]");
string name1 = item.ClientName;
name1 = r.Replace(name1, "");
string[] clientNames = name1.Split('.');
for (int i = 0; i < item.ClientNum; i++)
{
string name = "";
if (clientNames.Length > 0)
{
int index = i + 1;
if (index < clientNames.Length)
{
name = clientNames[index].Replace("MR", "").Replace("MS", "").Trim();
if (!string.IsNullOrEmpty(name))
{
airClientPris.Add(new
{
CnName = name,
EnName = name,
Price = unitCost,
AirType = item.AirTypeName
});
}
//if (name.Length > 0)
//{
// string nameLastStr = name[name.Length - 1].ToString();
// if (nameLastStr.IsNumeric())
// {
// name = name.Substring(0, name.Length - 1).Trim();
// }
//}
}
}
clientPinYinName += string.Format(@"{0}.{1}出票价为：{2} CNY;", Index + 1, name, unitCost.ToString("#0.00"));
}
}
if (!string.IsNullOrEmpty(item.AuditGMDate))
{
item.AuditGMDate = Convert.ToDateTime(item.AuditGMDate).ToString("yyyy-MM-dd HH:mm:ss");
}
AirCNYTotalPrice += item.CNYPrice;
}
_geView.GroupAirFeeViews = groupAirFeeViews;
if (airClientPris.Count > 0)
{
var peoplePriStr = "";
var airClientPris1 = airClientPris.GroupBy(item => item.CnName)
.Select(group => group.First())
.ToList();
int airClientPrisIndex = 1;
foreach (var item in airClientPris1)
{
decimal price = 0.00M;
var prices = airClientPris.Where(it => it.CnName == item.CnName).ToList();
foreach (var pri in prices)
{
price += pri.Price;
}
peoplePriStr += $@"{airClientPrisIndex}.{item.EnName}出票价为: {price.ToString("#0.00")} CNY；";
airClientPrisIndex++;
}
if (!string.IsNullOrEmpty(peoplePriStr))
{
str = $@"其中：{peoplePriStr}";
}
//经济舱均价
var airJJCPris = airClientPris.Where(it => it.AirType == "经济舱").ToList();
if (airJJCPris.Count > 0)
{
decimal jjcTotalPrice = 0.00M;
foreach (var item in airJJCPris)
{
jjcTotalPrice += item.Price;
}
decimal jjcPeopleNum = airJJCPris.GroupBy(item => item.CnName)
.Select(group => group.First())
.ToList().Count(); ;
JJCAveragePrice = jjcTotalPrice / jjcPeopleNum;
}
//公务舱均价
var airGWCPris = airClientPris.Where(it => it.AirType == "公务舱").ToList();
if (airGWCPris.Count > 0)
{
decimal gwcTotalPrice = 0.00M;
foreach (var item in airGWCPris)
{
gwcTotalPrice += item.Price;
}
decimal gwcPeopleNum = airGWCPris.GroupBy(item => item.CnName)
.Select(group => group.First())
.ToList().Count();
GWCAveragePrice = gwcTotalPrice / gwcPeopleNum;
}
}
_geView.GroupAirFeeStr = $@"人民币总费用：{AirCNYTotalPrice.ToString("#0.00")} CNY\r\n{str}\r\n经济舱均价为：{JJCAveragePrice.ToString("#0.00")}CNY/人;公务舱均价为：{GWCAveragePrice.ToString("#0.00")}CNY/人;";
#endregion
#region 签证费用
List<GroupVisaFeeView> groupVisaFeeViews = new List<GroupVisaFeeView>();
string groupVisaFeeSql = string.Format(@"Select vi.Id As VisaId,vi.DIId As VisaDiId,vi.VisaClient,ccp.PayMoney,sd1.Name As PayMoneyCurrency,
ccp.DayRate,ccp.Payee,ccp.AuditGMDate,ccp.OrbitalPrivateTransfer,sd2.Name As PayWay,
sd3.Name As CardTypeName,ccp.IsPay,u.CnName As Applicant,vi.CreateTime,
(((ccp.PayMoney * ccp.DayRate) / ccp.PayPercentage) * 100) As CNYPrice
From Grp_VisaInfo vi
Left Join Grp_CreditCardPayment ccp On ccp.isdel = 0 And ccp.CTable = 80 And vi.Id = ccp.CId
Left Join Sys_SetData sd1 On ccp.PaymentCurrency = sd1.Id
Left Join Sys_SetData sd2 On ccp.PayDId = sd2.Id
Left Join Sys_SetData sd3 On ccp.CTDId = sd3.Id
Left Join Sys_Users u On ccp.CreateUserId = u.Id
Where vi.IsDel = 0 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))
{
string[] clientIds = new string[] { };
if (visaClients.Contains(','))
{
clientIds = visaClients.Split(',');
}
else
{
clientIds = new string[] { visaClients };
}
if (clientIds.Length > 0)
{
List<int> clientIds1 = new List<int>() { };
foreach (var clientIdStr in clientIds)
{
if (clientIdStr.IsNumeric())
{
clientIds1.Add(int.Parse(clientIdStr));
}
}
if (clientIds1.Count > 0)
{
var clients = _clientDatas.Where(it => clientIds1.Contains(it.Id)).ToList();
foreach (var client in clients)
{
itemClientName += $"{client.LastName + client.FirstName},";
}
}
else
{
itemClientName = visaClients;
}
}
}
if (itemClientName.Length > 0)
{
itemClientName = itemClientName.Substring(0, itemClientName.Length - 1);
}
item.VisaClient = itemClientName;
VisaCNYTotalPirce += item.CNYPrice;
if (!string.IsNullOrEmpty(item.AuditGMDate))
{
item.AuditGMDate = Convert.ToDateTime(item.AuditGMDate).ToString("yyyy-MM-dd HH:mm:ss");
}
}
_geView.GroupVisaFeeViews = groupVisaFeeViews;
_geView.GroupVisaFeeStr = string.Format(@"人民币总费用：{0} CNY", VisaCNYTotalPirce.ConvertToDecimal1().ToString("#.00"));
#endregion
#region 邀请/公务活动 CTable = 81
List<GroupInvitationalFeeView> groupInvitationalFeeViews = new List<GroupInvitationalFeeView>();
string groupInvitationalFeeSql = string.Format(@"Select ioa.Id As IOAId,ioa.DiId As IOADiId,ioa.InviterArea,ioa.Inviter,ioa.InviteTime,
ioa.InviteCost,sd3.Name As InviteCurrency,ioa.SendCost,sd4.Name As SendCurrency,ioa.EventsCost,
sd5.Name As EventsCurrency,ioa.TranslateCost,sd6.Name As TranslateCurrency,ccp.PayMoney,
sd7.Name As PaymentCurrency,ccp.RMBPrice As CNYPrice,(((ccp.PayMoney * ccp.DayRate) / ccp.PayPercentage) * 100) As CNYPrice2,ccp.Payee,ccp.AuditGMDate,
ccp.OrbitalPrivateTransfer,sd2.Name As PayWay,ccp.IsPay,u.CnName As Applicant,ioa.CreateTime
From Grp_InvitationOfficialActivities ioa
Left Join Grp_CreditCardPayment ccp On ccp.isdel = 0 And ccp.CTable = 81 And ioa.Id = ccp.CId
Left Join Sys_SetData sd1 On ccp.PaymentCurrency = sd1.Id
Left Join Sys_SetData sd2 On ccp.PayDId = sd2.Id
Left Join Sys_SetData sd3 On ioa.InviteCurrency = sd3.Id
Left Join Sys_SetData sd4 On ioa.SendCurrency = sd4.Id
Left Join Sys_SetData sd5 On ioa.EventsCurrency = sd5.Id
Left Join Sys_SetData sd6 On ioa.TranslateCurrency = sd6.Id
Left Join Sys_SetData sd7 On ccp.PaymentCurrency = sd7.Id
Left Join Sys_Users u On ccp.CreateUserId = u.Id
Where ioa.IsDel = 0 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.CNYPrice2;
if (!string.IsNullOrEmpty(item.AuditGMDate))
{
item.AuditGMDate = Convert.ToDateTime(item.AuditGMDate).ToString("yyyy-MM-dd HH:mm:ss");
}
string currencyRateStr = "";
List<string> currencys = new List<string>();
if (!string.IsNullOrEmpty(item.InviteCurrency)) currencys.Add(item.InviteCurrency);
if (!string.IsNullOrEmpty(item.SendCurrency)) currencys.Add(item.SendCurrency);
if (!string.IsNullOrEmpty(item.EventsCurrency)) currencys.Add(item.EventsCurrency);
if (!string.IsNullOrEmpty(item.TranslateCurrency)) currencys.Add(item.TranslateCurrency);
if (!string.IsNullOrEmpty(item.PaymentCurrency)) currencys.Add(item.PaymentCurrency);
currencyRateStr = await GeneralMethod.PostGroupRateByCTableAndCurrency(teamRateData, 81, currencys);
item.CurrencyRateStr = currencyRateStr;
}
_geView.GroupInvitationalFeeViews = groupInvitationalFeeViews;
_geView.GroupInvitationalFeeStr = string.Format(@"人民币总费用：{0} CNY", InvitationalCNYTotalPrice.ToString("#.00"));
#endregion
#region 保险费用
List<GroupInsuranceFeeView> groupInsuranceFeeViews = new List<GroupInsuranceFeeView>();
string groupInsuranceFeeSql = string.Format(@"Select ic.Id As InsuranceId,ic.Diid As InsuranceDiId,ClientName,ccp.PayMoney,ccp.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
#region 市场部销售额
/// <summary>
/// 市场部销售额
/// Init 基础数据(公司/人员/年份/季度/月份)
/// </summary>
/// <param name="_dto">市场部销售额请求dto</param>
/// <returns></returns>
[HttpPost("PostMarketingSalesInitData")]
[ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
public async Task<IActionResult> PostMarketingSalesInitData(MarketingSalesInitDataDto _dto)
{
#region 参数验证
MarketingSalesInitDataDtoFoalidator validationRules = new MarketingSalesInitDataDtoFoalidator();
var validResult = await validationRules.ValidateAsync(_dto);
if (!validResult.IsValid)
{
var errors = new StringBuilder();
foreach (var error in validResult.Errors) errors.AppendLine(error.ErrorMessage);
return Ok(JsonView(false, errors.ToString()));
}
PageFunAuthViewBase pageFunAuthView = new PageFunAuthViewBase();
#region 页面操作权限验证
pageFunAuthView = await GeneralMethod.PostUserPageFuncDatas(_dto.UserId, _dto.PageId);
if (pageFunAuthView.CheckAuth == 0) return Ok(JsonView(false, MsgTips.CheckAuth));
#endregion
var companyData = _sqlSugar.Queryable<Sys_Company>().Where(it => it.IsDel == 0)
.Select(it => new { id = it.Id, name = it.CompanyName })
.ToList();
List<int> companyIds = companyData.Select(it => it.id).ToList();
List<int> pickGroupUserIds = _sqlSugar.Queryable<Grp_DelegationInfo>().Where(it => it.IsDel == 0)
.Select(it => it.JietuanOperator )
.ToList();
//var jobData = _sqlSugar.Queryable<Sys_JobPost>().Where(it => it.IsDel == 0 && companyIds.Contains(it.CompanyId) && (it.JobName.Contains("经理") || it.JobName.Contains("主管")))
// .Select(it => new { it.Id, it.CompanyId, it.DepId, it.JobName })
// .ToList();
//List<int> jobIds = jobData.Select(it => it.Id).ToList();
//(depIds.Contains(it.DepId) || jobIds.Contains(it.JobPostId)
//userId = 21
var userData = _sqlSugar.Queryable<Sys_Users>().Where(it => it.IsDel == 0 && (pickGroupUserIds.Contains(it.Id) || it.Id == 21))
.Select(it => new { id = it.Id, companyId = it.CompanyId, name = it.CnName })
.ToList();
companyData.Insert(0, new { id = -1, name = "全部" });
userData.Insert(0, new { id = -1, companyId = -1, name = "全部" });
#region 年份
var dtData = new List<dynamic>();
int dt = DateTime.Now.Year;
for (int y = dt; y >= dt-4; y--)
{
//季度
var quarterDatas = new List<dynamic>();
quarterDatas.Add(new { name = "全部", beginDt = $"{y}-01-01", endDt = $"{y}-12-31" });
for (int q = 0; q < 4; q++)
{
dynamic quarterData = null;
if (q == 0)
{
var monthDatas = new List<dynamic>();
monthDatas.Add(new { name = $"全部", beginDt = $"{y}-01-01", endDt = $" {y}-03-31" });
for (int m = 1; m < 4; m++)
{
MonthlyTimeSegment timeSegment = new MonthlyTimeSegment(y, m);
monthDatas.Add(new { name = $"{ConvertToChinese(m)}", beginDt = timeSegment.Start.ToString("yyyy-MM-dd"), endDt = timeSegment.End.ToString("yyyy-MM-dd") });
}
//quarterData = new { name = "第一季度", beginDt = $"{y}-01-01", endDt = $" {y}-03-31", monthData = monthDatas };
quarterData = new { name = "第一季度", monthData = monthDatas };
}
else if (q == 1)
{
var monthDatas = new List<dynamic>();
monthDatas.Add(new { name = $"全部", beginDt = $"{y}-04-01", endDt = $"{y}-06-30" });
for (int m = 4; m < 7; m++)
{
MonthlyTimeSegment timeSegment = new MonthlyTimeSegment(y, m);
monthDatas.Add(new { name = $"{ConvertToChinese(m)}", beginDt = timeSegment.Start.ToString("yyyy-MM-dd"), endDt = timeSegment.End.ToString("yyyy-MM-dd") });
}
//quarterData = new { name = "第二季度", beginDt = $"{y}-04-01", endDt = $"{y}-06-30", monthData = monthDatas };
quarterData = new { name = "第二季度", monthData = monthDatas };
}
else if (q == 2)
{
var monthDatas = new List<dynamic>();
monthDatas.Add(new { name = $"全部", beginDt = $"{y}-07-01", endDt = $"{y}-09-30" });
for (int m = 7; m < 10; m++)
{
MonthlyTimeSegment timeSegment = new MonthlyTimeSegment(y, m);
monthDatas.Add(new { name = $"{ConvertToChinese(m)}", beginDt = timeSegment.Start.ToString("yyyy-MM-dd"), endDt = timeSegment.End.ToString("yyyy-MM-dd") });
}
//quarterData = new { name = "第三季度", beginDt = $"{y}-07-01", endDt = $"{y}-09-30", monthData = monthDatas };
quarterData = new { name = "第三季度", monthData = monthDatas };
}
else if (q == 3)
{
var monthDatas = new List<dynamic>();
monthDatas.Add(new { name = $"全部", beginDt = $"{y}-10-01", endDt = $"{y}-12-31" });
for (int m = 10; m < 13; m++)
{
MonthlyTimeSegment timeSegment = new MonthlyTimeSegment(y, m);
monthDatas.Add(new { name = $"{ConvertToChinese(m)}", beginDt = timeSegment.Start.ToString("yyyy-MM-dd"), endDt = timeSegment.End.ToString("yyyy-MM-dd") });
}
//quarterData = new { name = "第四季度", beginDt = $"{y}-10-01", endDt = $"{y}-12-31", monthData = monthDatas };
quarterData = new { name = "第四季度", monthData = monthDatas };
}
quarterDatas.Add(quarterData);
}
dtData.Add(new
{
year = y,
//yearData = new { beginDt = $"{y}-01-01", endDt = $"{y}-12-31" },
quarterData = quarterDatas,
});
}
#endregion
return Ok(JsonView(true, "操作成功!", new { companyData = companyData, userData = userData, dtData = dtData }));
#endregion
}
private static string ConvertToChinese(int month)
{
if (month < 1 || month > 12)
throw new ArgumentOutOfRangeException(nameof(month), "月份必须在1到12之间。");
var cultureInfo = new System.Globalization.CultureInfo("zh-CN");
var dateTimeFormat = cultureInfo.DateTimeFormat;
return dateTimeFormat.GetMonthName(month);
}
/// <summary>
/// 市场部销售额
/// 年度/季度/月度 报表(同比)
/// （增加团组查询条件 IsSure=1）
/// </summary>
/// <param name="_dto">市场部销售额请求dto</param>
/// <returns></returns>
[HttpPost("PostMarketingSalesStatistics")]
[ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
public async Task<IActionResult> PostMarketingSalesStatistics_Year(MarketingSalesStatisticsDto _dto)
{
#region 参数验证
MarketingSalesStatisticsDtoFoalidator validationRules = new MarketingSalesStatisticsDtoFoalidator();
var validResult = await validationRules.ValidateAsync(_dto);
if (!validResult.IsValid)
{
var errors = new StringBuilder();
foreach (var error in validResult.Errors) errors.AppendLine(error.ErrorMessage);
return Ok(JsonView(false, errors.ToString()));
}
PageFunAuthViewBase pageFunAuthView = new PageFunAuthViewBase();
#region 页面操作权限验证
pageFunAuthView = await GeneralMethod.PostUserPageFuncDatas(_dto.UserId, _dto.PageId);
if (pageFunAuthView.CheckAuth == 0) return Ok(JsonView(false, MsgTips.CheckAuth));
#endregion
#endregion
string beginDt = $"{_dto.BeginDt} 00:00:00",endDt = $"{_dto.EndDt} 23:59:59";
return Ok(JsonView(true, "操作成功!", await GroupSales(_dto.CompanyId, _dto.GroupPickupUserId, beginDt, endDt)));
}
private async Task<List<int>> GetUserIds(int companyId, int groupPickupUserId)
{
//全部人员Id
//查询所有公司的市场部
List<int> pickGroupIds = _sqlSugar.Queryable<Grp_DelegationInfo>().Where(it => it.IsDel == 0).Select(it => it.JietuanOperator).Distinct().ToList();
List<int> userIds = new List<int>();
var userDatas = await _sqlSugar.Queryable<Sys_Users>()
.InnerJoin<Sys_Company>((u, c) => u.CompanyId == c.Id)
.Where((u, c) => u.IsDel == 0 && pickGroupIds.Contains( u.Id))
.Select((u, c) => new { u.Id, u.CompanyId, u.CnName })
.ToListAsync();
userIds = userDatas.Select(it => it.Id).ToList();
if (companyId > 0)
{
userIds = userDatas.Where(it => it.CompanyId == companyId).Select(it => it.Id).ToList();
}
if (groupPickupUserId > 0)
{
userIds = userDatas.Where(it => it.Id == groupPickupUserId).Select(it => it.Id).ToList();
}
return userIds;
}
/// <summary>
/// 计算团组销售额
/// </summary>
/// <param name="companyId"></param>
/// <param name="groupPickupUserId"></param>
/// <param name="beginDt"></param>
/// <param name="endDt"></param>
/// <returns></returns>
private async Task<SalesYOYView> GroupSales(int companyId, int groupPickupUserId,string beginDt, string endDt)
{
decimal thisSales = 0.00M, lastSales = 0.00M, yoy = 1.00M;
var _view = new SalesYOYView();
List<int> userIds = new List<int>();
userIds = await GetUserIds(companyId, groupPickupUserId);
string userSqlWhere = "";
if (userIds.Count > 0)
{
userSqlWhere = string.Format($" And Id IN ({string.Join(',', userIds)})");
}
else
{
_view = new SalesYOYView
{
thisYearSales = "0.00",
lastYearSales = "0.00",
};
return _view;
}
string lastBeginDt = Convert.ToDateTime(beginDt).AddYears(-1).ToString("yyyy-MM-dd HH:mm:ss"),
lastEndDt = Convert.ToDateTime(endDt).AddYears(-1).ToString("yyyy-MM-dd HH:mm:ss");
string salesSql = string.Format(@"
SELECT
'ThisSales' As [Name],
CAST(SUM(Sales) AS decimal(12,2)) As Sales
FROM
(
SELECT di.VisitDate,
(SELECT CAST(SUM(ItemSumPrice * Rate) AS decimal(12,2)) FROM Fin_ForeignReceivables
WHERE IsDel = 0 AND AddingWay IN (0, 1, 2)AND di.Id = Diid
) AS Sales
FROM
Grp_DelegationInfo di
WHERE di.IsDel = 0 AND di.IsSure = 1
AND di.JietuanOperator IN (SELECT Id FROM Sys_Users WITH (NoLock) WHERE IsDel = 0 {0})
AND di.VisitDate BETWEEN '{1}' AND '{2}'
) temp
Union ALL
SELECT
'LastSales' As [Name],
CAST(SUM(Sales) AS decimal(12,2)) As Sales
FROM
(
SELECT di.VisitDate,
(SELECT CAST(SUM(ItemSumPrice * Rate) AS decimal(12,2)) FROM Fin_ForeignReceivables
WHERE IsDel = 0 AND AddingWay IN (0, 1, 2)AND di.Id = Diid
) AS Sales
FROM
Grp_DelegationInfo di
WHERE di.IsDel = 0 AND di.IsSure = 1
AND di.JietuanOperator IN (SELECT Id FROM Sys_Users WITH (NoLock) WHERE IsDel = 0 {3})
AND di.VisitDate BETWEEN '{4}' AND '{5}'
) temp", userSqlWhere, beginDt, endDt, userSqlWhere, lastBeginDt, lastEndDt);
var salesData = await _sqlSugar.SqlQueryable<SalesView>(salesSql).ToListAsync();
thisSales = salesData.Where(x => x.Name.Equals("ThisSales")).First()?.Sales ?? 0;
lastSales = salesData.Where(x => x.Name.Equals("LastSales")).First()?.Sales ?? 0;
if (lastSales != 0 && thisSales != 0) yoy = (thisSales - lastSales) / lastSales;
return new SalesYOYView() {
thisYearSales = thisSales.ToString("#0.00"),
lastYearSales = lastSales.ToString("#0.00"),
yoy = yoy.ToString("#0.00")
};
}
private class SalesView
{
public string Name { get; set; }
public decimal Sales { get; set; }
}
private class SalesYOYView
{
public string thisYearSales { get; set; }
public string lastYearSales { get; set; }
public string yoy { get; set; } = "1.00";
}
/// <summary>
/// 市场部销售额
/// 团组列表
/// （增加团组查询条件 IsSure=1）
/// </summary>
/// <param name="_dto">市场部销售额请求dto</param>
/// <returns></returns>
[HttpPost("PostMarketingSalesGroupList")]
[ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
public async Task<IActionResult> PostMarketingSalesGroupList(MarketingSalesGroupListDto _dto)
{
#region 参数验证
MarketingSalesStatisticsDtoFoalidator validationRules = new MarketingSalesStatisticsDtoFoalidator();
var validResult = await validationRules.ValidateAsync(_dto);
if (!validResult.IsValid)
{
var errors = new StringBuilder();
foreach (var error in validResult.Errors) errors.AppendLine(error.ErrorMessage);
return Ok(JsonView(false, errors.ToString()));
}
PageFunAuthViewBase pageFunAuthView = new PageFunAuthViewBase();
#region 页面操作权限验证
pageFunAuthView = await GeneralMethod.PostUserPageFuncDatas(_dto.UserId, _dto.PageId);
if (pageFunAuthView.CheckAuth == 0) return Ok(JsonView(false, MsgTips.CheckAuth));
#endregion
#endregion
string userSql = "";
List<int> userIds = new List<int>();
userIds = await GetUserIds(_dto.CompanyId, _dto.GroupPickupUserId);
if (userIds.Count <= 0)
{
return Ok(JsonView(true, "操作成功!", new List<object> { }, 0));
}
else userSql = @$" And di.JietuanOperator In ({string.Join(",", userIds)})";
if (!string.IsNullOrEmpty(_dto.SearchCriteria))
{
userSql += string.Format(@$" AND di.TeamName Like '%{_dto.SearchCriteria}%'");
}
string beginDt = $"{_dto.BeginDt} 00:00:00", endDt = $"{_dto.EndDt} 23:59:59";
string sql = string.Format(@$"SELECT
ROW_NUMBER() OVER (
ORDER BY
CollectionDays
) AS RowNumber,
*
FROM
(
SELECT
di.Id,
di.TeamName,
di.ClientUnit,
di.ClientName,
di.VisitDate,
di.VisitPNumber,
di.JietuanOperator,
di.VisitEndDate,
(
SELECT
CAST(
COALESCE(SUM(ItemSumPrice * Rate), 0) AS DECIMAL(12, 2)
) AS GroupSales
FROM
Fin_ForeignReceivables
WHERE
IsDel = 0
AND di.Id = Diid
AND AddingWay IN (0, 1, 2)
) AS GroupSales,
u.CnName AS GroupPickupUser,
DATEADD(DAY, 7, di.VisitEndDate) AS CollectionDays
FROM
Grp_DelegationInfo di
WITH
(NoLock)
LEFT JOIN Sys_Users u ON di.JietuanOperator = u.Id
WHERE
di.Isdel = 0
AND di.IsSure = 1 {userSql}
AND VisitDate Between '{beginDt}' And '{endDt}'
) Temp ");
RefAsync<int> total = 0;
var groupData = await _sqlSugar.SqlQueryable<MarketingSalesGroupList>(sql).ToPageListAsync(_dto.PageIndex, _dto.PageSize, total);
return Ok(JsonView(true, "操作成功!", groupData, total));
}
/// <summary>
/// 市场部销售额
/// 客户类型、客户等级 统计
/// </summary>
/// <param name="_dto">市场部销售额请求dto</param>
/// <returns></returns>
[HttpPost("PostMarketingSalesGroupStatistics")]
[ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
public async Task<IActionResult> PostMarketingSalesGroupStatistics(MarketingSalesGroupStatisticsDto _dto)
{
#region 参数验证
MarketingSalesStatisticsDtoFoalidator validationRules = new MarketingSalesStatisticsDtoFoalidator();
var validResult = await validationRules.ValidateAsync(_dto);
if (!validResult.IsValid)
{
var errors = new StringBuilder();
foreach (var error in validResult.Errors) errors.AppendLine(error.ErrorMessage);
return Ok(JsonView(false, errors.ToString()));
}
PageFunAuthViewBase pageFunAuthView = new PageFunAuthViewBase();
#region 页面操作权限验证
pageFunAuthView = await GeneralMethod.PostUserPageFuncDatas(_dto.UserId, _dto.PageId);
if (pageFunAuthView.CheckAuth == 0) return Ok(JsonView(false, MsgTips.CheckAuth));
#endregion
#endregion
string beginDt = $"{_dto.BeginDt} 00:00:00", endDt = $"{_dto.EndDt} 23:59:59";
List<int> userIds = await GetUserIds(_dto.CompanyId, _dto.GroupPickupUserId);
string userSql = "";
if (userIds.Count > 0)
{
userSql = string.Format(@$" AND JietuanOperator IN ({string.Join(",",userIds)})");
}
else
{
if (_dto.PortType == 2 || _dto.PortType == 3)
{
return Ok(JsonView(true, "操作成功!", new List<object> { }));
}
else
{
return Ok(JsonView(true, "操作成功!", new
{
customerTypeData = new List<object> { },
clientGradeData = new List<object> { }
}));
}
}
string sql = "";
if (_dto.StatisticsType == 1 )
{
sql = string.Format(@$"Select
sd.[Name],
Count(*) As [Count]
From Grp_DelegationInfo di
Left Join Sys_SetData sd On di.TeamDid = sd.Id
Where di.Isdel = 0
And IsSure = 1 {userSql}
And VisitDate Between '{beginDt}' And '{endDt}'
Group By [Name]
Order By Count Desc");
}
else if (_dto.StatisticsType == 2)
{
sql = string.Format(@$"Select
sd.[Name],
Count(*) As [Count]
From Grp_DelegationInfo di
Left Join Sys_SetData sd On di.TeamLevSId = sd.Id
Where di.Isdel = 0
And IsSure = 1 {userSql}
And VisitDate Between '{beginDt}' And '{endDt}'
Group By [Name]
Order By Count Desc");
}
else return Ok(JsonView(false, "StatisticsType不在可取范围!"));
if (_dto.PortType == 1)
{
string sql1 = string.Format(@$"Select
sd.[Name],
Count(*) As [Count]
From Grp_DelegationInfo di
Left Join Sys_SetData sd On di.TeamDid = sd.Id
Where di.Isdel = 0
And IsSure = 1 {userSql}
And VisitDate Between '{beginDt}' And '{endDt}'
Group By [Name]
Order By Count Desc");
var customerTypeData = await _sqlSugar.SqlQueryable<MarketingSalesGroupStatisticsView>(sql1).ToListAsync();
string sql2 = string.Format(@$"Select
sd.[Name],
Count(*) As [Count]
From Grp_DelegationInfo di
Left Join Sys_SetData sd On di.TeamLevSId = sd.Id
Where di.Isdel = 0
And IsSure = 1 {userSql}
And VisitDate Between '{beginDt}' And '{endDt}'
Group By [Name]
Order By Count Desc");
var clientGradeData = await _sqlSugar.SqlQueryable<MarketingSalesGroupStatisticsView>(sql2).ToListAsync();
return Ok(JsonView(true, "操作成功!", new {
customerTypeData= customerTypeData,
clientGradeData = clientGradeData
}));
}
else if (_dto.PortType == 2 || _dto.PortType == 3)
{
var data = await _sqlSugar.SqlQueryable<MarketingSalesGroupStatisticsView>(sql).ToListAsync();
return Ok(JsonView(true, "操作成功!", data, data.Count));
}
else return Ok(JsonView(false, MsgTips.Port));
}
/// <summary>
/// 市场部销售额
/// 接单排名
/// </summary>
/// <param name="_dto">市场部销售额请求dto</param>
/// <returns></returns>
[HttpPost("PostMarketingSalesOrderRanking")]
[ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
public async Task<IActionResult> PostMarketingSalesOrderRanking(MarketingSalesOrderRankingDto _dto)
{
#region 参数验证
MarketingSalesStatisticsDtoFoalidator validationRules = new MarketingSalesStatisticsDtoFoalidator();
var validResult = await validationRules.ValidateAsync(_dto);
if (!validResult.IsValid)
{
var errors = new StringBuilder();
foreach (var error in validResult.Errors) errors.AppendLine(error.ErrorMessage);
return Ok(JsonView(false, errors.ToString()));
}
PageFunAuthViewBase pageFunAuthView = new PageFunAuthViewBase();
#region 页面操作权限验证
pageFunAuthView = await GeneralMethod.PostUserPageFuncDatas(_dto.UserId, _dto.PageId);
if (pageFunAuthView.CheckAuth == 0) return Ok(JsonView(false, MsgTips.CheckAuth));
#endregion
#endregion
string beginDt = $"{_dto.BeginDt} 00:00:00", endDt = $"{_dto.EndDt} 23:59:59";
List<int> userIds = await GetUserIds(_dto.CompanyId, _dto.GroupPickupUserId);
string userSql = "";
if (userIds.Count > 0)
{
userSql = string.Format(@$" AND JietuanOperator IN ({string.Join(",", userIds)})");
}else return Ok(JsonView(true, "操作成功!", new List<object> { }, 0));
string sql = string.Format(@$"Select
ROW_NUMBER() Over(Order By Count(*) Desc) As RowNumber,
u.CnName As UserName,
Count(*) As [Count]
From Grp_DelegationInfo di
Left Join Sys_Users u On di.JietuanOperator = u.Id
Where di.Isdel = 0
AND IsSure = 1 {userSql}
And VisitDate Between '{beginDt}' And '{endDt}'
Group By CnName");
RefAsync<int> total = 0;
var rankingData = await _sqlSugar.SqlQueryable<MarketingSalesOrderRankingView>(sql).ToPageListAsync(_dto.PageIndex, _dto.PageSize, total);
return Ok(JsonView(true, "操作成功!", rankingData, total));
}
/// <summary>
/// 市场部销售额
/// 团组列表、客户类型、客户等级、接单排名
/// （增加团组查询条件 IsSure=1）
/// </summary>
/// <param name="_dto">市场部销售额请求dto</param>
/// <returns></returns>
[HttpPost("PostMarketingSalesGroupItem")]
[ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
public async Task<IActionResult> PostMarketingSalesGroupItem(MarketingSalesGroupListDto _dto)
{
#region 参数验证
MarketingSalesStatisticsDtoFoalidator validationRules = new MarketingSalesStatisticsDtoFoalidator();
var validResult = await validationRules.ValidateAsync(_dto);
if (!validResult.IsValid)
{
var errors = new StringBuilder();
foreach (var error in validResult.Errors) errors.AppendLine(error.ErrorMessage);
return Ok(JsonView(false, errors.ToString()));
}
PageFunAuthViewBase pageFunAuthView = new PageFunAuthViewBase();
#region 页面操作权限验证
pageFunAuthView = await GeneralMethod.PostUserPageFuncDatas(_dto.UserId, _dto.PageId);
if (pageFunAuthView.CheckAuth == 0) return Ok(JsonView(false, MsgTips.CheckAuth));
#endregion
#endregion
string userSql = "";
List<int> userIds = new List<int>();
userIds = await GetUserIds(_dto.CompanyId, _dto.GroupPickupUserId);
if (userIds.Count <= 0)
{
return Ok(JsonView(true, "操作成功!", new List<object> { }, 0));
}
else userSql = @$" And di.JietuanOperator In ({string.Join(",", userIds)})";
if (!string.IsNullOrEmpty(_dto.SearchCriteria))
{
userSql += string.Format(@$" AND di.TeamName Like '%{_dto.SearchCriteria}%'");
}
string beginDt = $"{_dto.BeginDt} 00:00:00", endDt = $"{_dto.EndDt} 23:59:59";
#region 团组List
string sql = string.Format(@$"SELECT
ROW_NUMBER() OVER (
ORDER BY
CollectionDays
) AS RowNumber,
*
FROM
(
SELECT
di.Id,
di.TeamName,
di.ClientUnit,
di.ClientName,
di.VisitDate,
di.VisitPNumber,
di.JietuanOperator,
di.VisitEndDate,
(
SELECT
CAST(
COALESCE(SUM(ItemSumPrice * Rate), 0) AS DECIMAL(12, 2)
) AS GroupSales
FROM
Fin_ForeignReceivables
WHERE
IsDel = 0
AND di.Id = Diid
AND AddingWay IN (0, 1, 2)
) AS GroupSales,
u.CnName AS GroupPickupUser,
DATEADD(DAY, 7, di.VisitEndDate) AS CollectionDays
FROM
Grp_DelegationInfo di
WITH
(NoLock)
LEFT JOIN Sys_Users u ON di.JietuanOperator = u.Id
WHERE
di.Isdel = 0
AND di.IsSure = 1 {userSql}
AND VisitDate Between '{beginDt}' And '{endDt}'
) Temp ");
RefAsync<int> total = 0;
var groupData = await _sqlSugar.SqlQueryable<MarketingSalesGroupList>(sql).ToPageListAsync(_dto.PageIndex, _dto.PageSize, total);
#endregion
#region 客户类型、客户等级 统计
string sql1 = string.Format(@$"Select
sd.[Name],
Count(*) As [Count]
From Grp_DelegationInfo di
Left Join Sys_SetData sd On di.TeamDid = sd.Id
Where di.Isdel = 0
And IsSure = 1 {userSql}
And VisitDate Between '{beginDt}' And '{endDt}'
Group By [Name]
Order By Count Desc");
var customerTypeData = await _sqlSugar.SqlQueryable<MarketingSalesGroupStatisticsView>(sql1).ToListAsync();
string sql2 = string.Format(@$"Select
sd.[Name],
Count(*) As [Count]
From Grp_DelegationInfo di
Left Join Sys_SetData sd On di.TeamLevSId = sd.Id
Where di.Isdel = 0
And IsSure = 1 {userSql}
And VisitDate Between '{beginDt}' And '{endDt}'
Group By [Name]
Order By Count Desc");
var clientGradeData = await _sqlSugar.SqlQueryable<MarketingSalesGroupStatisticsView>(sql2).ToListAsync();
#endregion
#region 接单排名
string sql4 = string.Format(@$"Select
ROW_NUMBER() Over(Order By Count(*) Desc) As RowNumber,
u.CnName As UserName,
Count(*) As [Count]
From Grp_DelegationInfo di
Left Join Sys_Users u On di.JietuanOperator = u.Id
Where di.Isdel = 0
AND IsSure = 1 {userSql}
And VisitDate Between '{beginDt}' And '{endDt}'
Group By CnName");
var rankingData = await _sqlSugar.SqlQueryable<MarketingSalesOrderRankingView>(sql4).ToListAsync();
#endregion
var viewData = new {
groupData = groupData,
groupTotal = total,
customerTypeData = customerTypeData,
clientGradeData = clientGradeData,
rankingData = rankingData
};
return Ok(JsonView(true, "操作成功!", viewData, total));
}
/// <summary>
/// 市场部销售额
/// 客户拜访列表
/// </summary>
/// <param name="_dto">市场部销售额请求dto</param>
/// <returns></returns>
[HttpPost("PostMarketingSalesVCList")]
[ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
public async Task<IActionResult> PostMarketingSalesVCList(MarketingSalesVCListDto _dto)
{
#region 参数验证
MarketingSalesUserPageFuncDtoBaseFoalidator validationRules = new MarketingSalesUserPageFuncDtoBaseFoalidator();
var validResult = await validationRules.ValidateAsync(_dto);
if (!validResult.IsValid)
{
var errors = new StringBuilder();
foreach (var error in validResult.Errors) errors.AppendLine(error.ErrorMessage);
return Ok(JsonView(false, errors.ToString()));
}
PageFunAuthViewBase pageFunAuthView = new PageFunAuthViewBase();
#region 页面操作权限验证
pageFunAuthView = await GeneralMethod.PostUserPageFuncDatas(_dto.UserId, _dto.PageId);
if (pageFunAuthView.CheckAuth == 0) return Ok(JsonView(false, MsgTips.CheckAuth));
#endregion
#endregion
return Ok(await _visitingClientsRep._List(_dto.PortType,_dto.PageIndex,_dto.PageSize,_dto.DiId,_dto.Search));
}
/// <summary>
/// 市场部销售额
/// 客户拜访 操作(添加 Or 编辑)
/// </summary>
/// <param name="_dto">市场部销售额请求dto</param>
/// <returns></returns>
[HttpPost("PostMarketingSalesVCOperate")]
[ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
public async Task<IActionResult> PostMarketingSalesVCOperate(MarketingSalesVCOperrateDto _dto)
{
#region 参数验证
MarketingSalesUserPageFuncDtoBaseFoalidator validationRules = new MarketingSalesUserPageFuncDtoBaseFoalidator();
var validResult = await validationRules.ValidateAsync(_dto);
if (!validResult.IsValid)
{
var errors = new StringBuilder();
foreach (var error in validResult.Errors) errors.AppendLine(error.ErrorMessage);
return Ok(JsonView(false, errors.ToString()));
}
PageFunAuthViewBase pageFunAuthView = new PageFunAuthViewBase();
#region 页面操作权限验证
pageFunAuthView = await GeneralMethod.PostUserPageFuncDatas(_dto.UserId, _dto.PageId);
if (pageFunAuthView.CheckAuth == 0) return Ok(JsonView(false, MsgTips.CheckAuth));
#endregion
#endregion
return Ok(await _visitingClientsRep._AddOrEdit(_dto));
}
/// <summary>
/// 市场部销售额
/// 客户拜访 Del
/// </summary>
/// <param name="_dto">市场部销售额请求dto</param>
/// <returns></returns>
[HttpPost("PostMarketingSalesVCDel")]
[ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
public async Task<IActionResult> PostMarketingSalesVCDel(MarketingSalesVCDelDto _dto)
{
return Ok(await _visitingClientsRep._Del(_dto.Id,_dto.UserId));
}
/// <summary>
/// 市场部销售额
/// 客户拜访 操作 save
/// </summary>
/// <param name="_dto">市场部销售额请求dto</param>
/// <returns></returns>
[HttpPost("PostMarketingSalesVCSave")]
[ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
public async Task<IActionResult> PostMarketingSalesVCSave(MarketingSalesVCSaveDto _dto)
{
#region 参数验证
MarketingSalesUserPageFuncDtoBaseFoalidator validationRules = new MarketingSalesUserPageFuncDtoBaseFoalidator();
var validResult = await validationRules.ValidateAsync(_dto);
if (!validResult.IsValid)
{
var errors = new StringBuilder();
foreach (var error in validResult.Errors) errors.AppendLine(error.ErrorMessage);
return Ok(JsonView(false, errors.ToString()));
}
PageFunAuthViewBase pageFunAuthView = new PageFunAuthViewBase();
#region 页面操作权限验证
pageFunAuthView = await GeneralMethod.PostUserPageFuncDatas(_dto.UserId, _dto.PageId);
if (pageFunAuthView.CheckAuth == 0) return Ok(JsonView(false, MsgTips.CheckAuth));
#endregion
#endregion
return Ok(await _visitingClientsRep._Save(_dto));
}
#endregion
#region 日付报表
/// <summary>
/// 日付类型数据
/// </summary>
/// <param name="_dto"></param>
/// <returns></returns>
[HttpPost("DailypaymentTypeInit")]
[ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
public async Task<IActionResult> DailypaymentTypeInit(DailypaymentTypeInitDto _dto)
{
#region 参数验证
if (_dto.PortType < 1 || _dto.PortType > 3) return Ok(JsonView(false, msg:MsgTips.Port));
PageFunAuthViewBase pageFunAuthView = new PageFunAuthViewBase();
#region 页面操作权限验证
//pageFunAuthView = await GeneralMethod.PostUserPageFuncDatas(_dto.UserId, _dto.PageId);
//if (pageFunAuthView.CheckAuth == 0) return Ok(JsonView(false, "您没有查看权限"));
#endregion
#endregion
if (_dto.PortType == 1 || _dto.PortType == 2 || _dto.PortType == 3) // web/Android/IOS
{
var defaultParentIds = new List<int>() {
48,// 人员费用
49,// 办公费用
50,// 销售费用
51,// 其他费用
55,// 大运会
};
var dailypaymentTypeData = await RedisRepository.RedisFactory
.CreateRedisRepository()
.StringGetAsync<List<int>>("DailypaymentTypeData") ?? new List<int>();
var dailyTypeData = await _sqlSugar.Queryable<DailypaymentParentTypeView>()
.Includes(x => x.SubData)
.Where(x => defaultParentIds.Contains(x.Id))
.ToListAsync();
dailyTypeData.ForEach(x =>
{
x.SubData.ForEach(y =>
{
int currId = dailypaymentTypeData.Find(z => z == y.Id);
y.IsChecked = currId == 0 ? false : true;
});
});
var companyData = await _sqlSugar.Queryable<Sys_Company>()
.Where(x => x.IsDel == 0)
.Select(x => new { id = x.Id, name = x.CompanyName })
.ToListAsync();
return Ok(JsonView(true, "查询成功!", new { dailyTypeData = dailyTypeData, companyData = companyData }));
}
else
{
return Ok(JsonView(false, "查询失败"));
}
}
/// <summary>
/// 日付类型数据 Save
/// </summary>
/// <param name="_dto"></param>
/// <returns></returns>
[HttpPost("DailypaymentTypeDataSave")]
[ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
public async Task<IActionResult> DailypaymentTypeDataSave(DailypaymentTypeDataSaveDto _dto)
{
#region 参数验证
if (_dto.PortType < 1 || _dto.PortType > 3) return Ok(JsonView(false, msg: MsgTips.Port));
PageFunAuthViewBase pageFunAuthView = new PageFunAuthViewBase();
#region 页面操作权限验证
//pageFunAuthView = await GeneralMethod.PostUserPageFuncDatas(_dto.UserId, _dto.PageId);
//if (pageFunAuthView.CheckAuth == 0) return Ok(JsonView(false, "您没有查看权限"));
#endregion
#endregion
if (_dto.PortType == 1 || _dto.PortType == 2 || _dto.PortType == 3) // web/Android/IOS
{
if (_dto.TypeIds.Count < 1) return Ok(JsonView(false, "请传入需要保存的TypeIds"));
var res = await RedisRepository.RedisFactory
.CreateRedisRepository()
.StringSetAsync(
key:"DailypaymentTypeData",
_dto.TypeIds,
timeout:null );
if (!res) return Ok(JsonView(false, "操作失败"));
return Ok(JsonView(true, "操作成功!"));
}
else return Ok(JsonView(false, "操作失败"));
}
/// <summary>
/// 日付数据列表
/// </summary>
/// <param name="_dto"></param>
/// <returns></returns>
[HttpPost("DailypaymentRange")]
[ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
public async Task<IActionResult> DailypaymentRange(DailypaymentRangeDto _dto)
{
#region 参数验证
var validator = new DailypaymentRangeDtoValidator();
var validationRes = validator.Validate(_dto);
if (!validationRes.IsValid)
{
StringBuilder sb = new StringBuilder();
foreach (var item in validationRes.Errors)
{
sb.AppendLine(item.ErrorMessage);
}
return Ok(JsonView(false, sb.ToString()));
}
PageFunAuthViewBase pageFunAuthView = new PageFunAuthViewBase();
#region 页面操作权限验证
//pageFunAuthView = await GeneralMethod.PostUserPageFuncDatas(_dto.UserId, _dto.PageId);
//if (pageFunAuthView.CheckAuth == 0) return Ok(JsonView(false, "您没有查看权限"));
#endregion
#endregion
if (_dto.PortType == 1 || _dto.PortType == 2 || _dto.PortType == 3) // web/Android/IOS
{
DateTime _beginDt = Convert.ToDateTime($"{_dto.BeginDt} 00:00:00"),
_endDt = Convert.ToDateTime($"{_dto.EndDt} 23:59:59");
string sqlWhere = string.Format(@" And dfp.CreateTime Between '{0}' And '{1}'",_beginDt,_endDt);
var dailypaymentTypeData = await RedisRepository.RedisFactory
.CreateRedisRepository()
.StringGetAsync<List<int>>("DailypaymentTypeData") ?? new List<int>();
string sql = string.Format(@"
Select
dfp.Id,
dfp.Instructions,
dfp.PriceTypeId,
sd1.Name As FeeType,
dfp.TransferTypeId,
sd2.Name As TransferType,
dfp.SumPrice,
dfp.FAuditDate,
dfp.MAuditDate,
dfp.CompanyId,
c.CompanyName,
dfp.CreateUserId As ApplicantId,
u.CnName As Applicant,
dfp.CreateTime As ApplicantDt,
dfp.Instructions+u.CnName As ViewStr
From Fin_DailyFeePayment dfp
Left Join Sys_Users u On dfp.CreateUserId = u.Id
Left Join Sys_SetData sd1 On dfp.TransferTypeId = sd1.Id
Left Join Sys_Company c On dfp.CompanyId = c.Id
Left Join Sys_SetData sd2 On dfp.PriceTypeId = sd2.Id
Where dfp.IsDel = 0 And dfp.IsPay = 1
", sqlWhere);//--Order By dfp.CreateTime Desc
if (_dto.Type == 1) //data
{
RefAsync<int> total = 0;
var _view = await _sqlSugar.SqlQueryable<DailyFeePaymentRangeView>(sql)
.Where(x => x.ApplicantDt >= _beginDt && x.ApplicantDt <= _endDt)
.WhereIF(dailypaymentTypeData.Count > 0,
x => dailypaymentTypeData.Contains(x.PriceTypeId))
.WhereIF(_dto.CompanyIds.Count > 0, x => _dto.CompanyIds.Contains(x.CompanyId))
.WhereIF(!string.IsNullOrEmpty(_dto.Filter), x => x.ViewStr.Contains(_dto.Filter))
.OrderByDescending(x => x.ApplicantDt)
.ToPageListAsync(
pageNumber: _dto.PageIndex,
pageSize: _dto.PageSize,
totalNumber: total
);
_sqlSugar.ThenMapper(_view, x =>
{
x.Contents = _sqlSugar.Queryable<DailyFeePaymentContentView>()
.SetContext(x1 => x1.DFPId, () => x.Id, x)
.ToList();
});
//单独处理
return Ok(JsonView(true, "操作成功!", new { data = _view, total = _view.Sum(x => x.SumPrice) }, total));
}
else if (_dto.Type == 2) //view
{
var _view = await _sqlSugar.SqlQueryable<ExcelView>(sql)
.Where(x => x.ApplicantDt >= _beginDt && x.ApplicantDt <= _endDt)
.WhereIF(dailypaymentTypeData.Count > 0,
x => dailypaymentTypeData.Contains(x.PriceTypeId))
.WhereIF(_dto.CompanyIds.Count > 0, x => _dto.CompanyIds.Contains(x.CompanyId))
.WhereIF(!string.IsNullOrEmpty(_dto.Filter), x => x.ViewStr.Contains(_dto.Filter))
.OrderByDescending(x => x.ApplicantDt)
.ToPageListAsync(
pageNumber: 1,
pageSize: 99999
) ;
_sqlSugar.ThenMapper(_view, x =>
{
x.Contents = _sqlSugar.Queryable<DailyFeePaymentContentView>()
.SetContext(x1 => x1.DFPId, () => x.Id, x)
.ToList();
string str = "";
int index = 1;
foreach (var item in x.Contents)
{
string str1 = $"{index}、费用名称：[{item.PriceName}] 单价:[{item.Price.ToString("#0.00")}] 数量:[{item.Quantity.ToString("#0.00")}] 小计:[{item.ItemTotal.ToString("#0.00")}] 备注:[{item.Remark}]";
if (index == x.Contents.Count) str += str1;
else str += str1 + "\r\n";
index++;
}
x.ContentStr = str;
});
if (_view.Count > 0 )
{
//DataTable dt = GeneralMethod.
DataTable dt = CommonFun.GetDataTableFromIList(_view );
dt.TableName = $"_view";
decimal total = _view.Sum(x => x.SumPrice);
WorkbookDesigner designer = new WorkbookDesigner();
designer.Workbook = new Workbook(AppSettingsHelper.Get("ExcelBasePath") + "Template/日付报表模板.xls");
designer.SetDataSource(dt);
designer.SetDataSource("Total", total);
designer.Workbook.Worksheets[0].Name = "日付报表";
designer.Process();
string fileName = $"DailyPayment/日付报表{ DateTime.Now.ToString("yyyyMMddHHmmss")}.xlsx";
designer.Workbook.Save(AppSettingsHelper.Get("ExcelBasePath") + fileName);
string rst = AppSettingsHelper.Get("ExcelBaseUrl") + AppSettingsHelper.Get("ExcelFtpPath") + fileName;
return Ok(JsonView(true, "操作成功", new { url = rst })) ;
}
return Ok(JsonView(true, ",暂无数据"));
}
return Ok(JsonView(false, "操作失败"));
}
else return Ok(JsonView(false, "操作失败"));
}
#endregion
#region 统计模块
/// <summary>
/// 同比
/// 营业额（今年和去年的）、成本支出（今年和去年的）、毛利润（今年和去年的）
/// </summary>
/// <param name="_dto">市场部销售额请求dto</param>
/// <returns></returns>
[HttpPost("StatisticsYOY")]
[ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
public async Task<IActionResult> StatisticsYOY(YOYDto _dto)
{
if (_dto.Year < 1) return Ok(JsonView(false,"操作失败"));
string beginDt = $"{_dto.Year-1}-01-01 00:00:00",
endDt = $"{_dto.Year}-12-31 23:59:59";
string sql = string.Format(@"
SELECT
di.Id,
di.TeamName,
di.VisitDate,
YEAR(di.VisitDate) AS YEAR,
MONTH(di.VisitDate) AS MONTH,
(
SELECT
CAST(SUM(ItemSumPrice * Rate) AS DECIMAL(12, 2))
FROM
Fin_ForeignReceivables
WHERE
IsDel = 0
AND AddingWay IN (0, 1, 2)
AND di.Id = Diid
) AS SaleAmount,
(
SELECT
CAST(
SUM(
(
((ccp.PayMoney * ccp.DayRate) / ccp.PayPercentage) * 100
)
) AS DECIMAL(12, 2)
)
FROM
Grp_AirTicketReservations atr
INNER JOIN Grp_CreditCardPayment ccp ON ccp.isdel = 0
AND ccp.CTable = 85
AND atr.Id = ccp.CId
WHERE
atr.IsDel = 0
AND ccp.IsAuditGM = 1
AND ccp.IsPay = 1
AND di.Id = atr.DiId
) AirTicketAmount,
(
SELECT
CAST(
SUM(
(
ctggr.ServiceQuotedPrice * (ccp.PayPercentage / 100) * ccp.DayRate
)
) AS DECIMAL(12, 2)
)
FROM
Grp_CarTouristGuideGroundReservations ctggr
INNER JOIN Grp_CreditCardPayment ccp ON ccp.IsDel = 0
AND ccp.CTable = 79
AND ctggr.Id = ccp.CId
WHERE
ctggr.IsDel = 0
AND ccp.IsAuditGM = 1
AND ccp.IsPay = 1
AND di.Id = ctggr.DiId
) AS OPAmount,
(
SELECT
CAST(SUM((ccp.PayMoney * ccp.DayRate)) AS DECIMAL(12, 2))
FROM
Grp_DecreasePayments dp
INNER JOIN Grp_CreditCardPayment ccp ON ccp.isdel = 0
AND ccp.CTable = 98
AND dp.Id = ccp.CId
WHERE
dp.IsDel = 0
AND ccp.Ctable = 98
AND ccp.IsAuditGM = 1
AND ccp.IsPay = 1
AND di.Id = dp.Diid
) AS OtherCostAmount,
(
SELECT
CAST(
SUM(
(
((ccp.PayMoney * ccp.DayRate) / ccp.PayPercentage) * 100
)
) AS DECIMAL(12, 2)
)
FROM
Grp_VisaInfo vi
INNER JOIN Grp_CreditCardPayment ccp ON ccp.isdel = 0
AND ccp.CTable = 80
AND vi.Id = ccp.CId
WHERE
vi.IsDel = 0
AND ccp.IsAuditGM = 1
AND ccp.IsPay = 1
AND di.Id = vi.DIId
) AS VisaAmount,
(
SELECT
CAST(
SUM(
((ccp.PayMoney * ccp.DayRate) / ccp.PayPercentage) * 100
) AS DECIMAL(12, 2)
)
FROM
Grp_InvitationOfficialActivities ioa
INNER JOIN Grp_CreditCardPayment ccp ON ccp.isdel = 0
AND ccp.CTable = 81
AND ioa.Id = ccp.CId
WHERE
ioa.IsDel = 0
AND ccp.IsAuditGM = 1
AND ccp.IsPay = 1
AND di.Id = ioa.Diid
) AS OAAmount,
(
SELECT
CAST(
SUM(
((ccp.PayMoney * ccp.DayRate) / ccp.PayPercentage) * 100
) AS DECIMAL(12, 2)
)
FROM
Grp_Customers ic
INNER JOIN Grp_CreditCardPayment ccp ON ccp.isdel = 0
AND ccp.CTable = 82
AND ic.Id = ccp.CId
WHERE
ic.IsDel = 0
AND ccp.IsAuditGM = 1And ccp.IsPay = 1
AND di.Id = ic.DiId
) AS InsureAmount,
(
SELECT
CAST(
SUM(
((ccp.PayMoney * ccp.DayRate) / ccp.PayPercentage) * 100
) AS DECIMAL(12, 2)
)
FROM
Grp_HotelReservations hr
INNER JOIN Grp_CreditCardPayment ccp ON hr.Id = ccp.CId
WHERE
hr.IsDel = 0
AND ccp.IsDel = 0
AND ccp.CTable = 76
AND ccp.IsAuditGM = 1
AND ccp.PayMoney <> 0
AND ccp.IsPay = 0
AND di.Id = hr.DiId
) AS HotelAmount,
(
SELECT
CAST(
SUM(
((ccp.PayMoney * ccp.DayRate) / ccp.PayPercentage) * 100
) AS DECIMAL(12, 2)
)
FROM
Grp_HotelReservations hr
INNER JOIN Grp_CreditCardPayment ccp ON hr.Id = ccp.CId
WHERE
hr.IsDel = 0
AND ccp.IsDel = 0
AND ccp.CTable = 76
AND ccp.IsAuditGM = 1
AND ccp.PayMoney <> 0
AND ccp.IsPay = 0
AND di.Id = hr.DiId
) AS HotelAmount,
(
SELECT
CAST(
SUM((gec.PriceSum * gec.Coefficient * ccp.DayRate)) AS DECIMAL(12, 2)
)
FROM
OA2023DB.dbo.Fin_GroupExtraCost gec
INNER JOIN Grp_CreditCardPayment ccp ON gec.Id = ccp.CId
WHERE
ccp.IsDel = 0
AND ccp.CTable = 1015
AND ccp.IsAuditGM = 1
AND ccp.IsPay = 1
AND di.Id = gec.DiId
) AS ExcessAmount
FROM
Grp_DelegationInfo di
WHERE
di.IsDel = 0
AND di.IsSure = 1
AND di.VisitDate BETWEEN '{0}' AND '{1}'
ORDER BY
di.VisitDate
",beginDt,endDt);
var data = await _sqlSugar.SqlQueryable<StatisticsYOYView>(sql).ToListAsync();
var thisYearData = data.Where(x => x.Year == _dto.Year).ToList();
var lastYearData = data.Where(x => x.Year == (_dto.Year - 1)).ToList();
List<YOYReturnView> SalesYOYData = new List<YOYReturnView>();
int monthIndex = 1;
while (monthIndex < 13)
{
monthIndex++;
}
return Ok(JsonView(true,"操作成功!", new { data = data, SalesData = new { },CostData = new { } }));
}
#endregion
}
}