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;
namespace OASystem.API.Controllers
{
/// <summary>
/// 统计模块
/// </summary>
[Route("api/[controller]")]
[ApiController]
public class StatisticsController : ControllerBase
{
private readonly IMapper _mapper;
private readonly SqlSugarClient _sqlSugar;
private readonly DelegationInfoRepository _groupRep;
private readonly SetDataRepository _setDataRep;
/// <summary>
/// Init
/// </summary>
/// <param name="mapper"></param>
/// <param name="sqlSugar"></param>
/// <param name="groupRep"></param>
/// <param name="setDataRep"></param>
public StatisticsController(IMapper mapper, SqlSugarClient sqlSugar, DelegationInfoRepository groupRep, SetDataRepository setDataRep)
{
_mapper = mapper;
_groupRep = groupRep;
_setDataRep = setDataRep;
_sqlSugar = sqlSugar;
}
#region 团组报表
/// <summary>
/// 团组报表
/// Items
/// </summary>
/// <param name="_dto">团组列表请求dto</param>
/// <returns></returns>
[HttpPost("PostGroupStatementItems")]
[ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
public async Task<IActionResult> PostGroupStatementItems(GroupStatementItemsDto _dto)
{
#region 参数验证
if (_dto.UserId < 1) return Ok(JsonView(false, "员工Id为空"));
if (_dto.PageId < 1) return Ok(JsonView(false, "页面Id为空"));
PageFunAuthViewBase pageFunAuthView = new PageFunAuthViewBase();
#region 页面操作权限验证
pageFunAuthView = await GeneralMethod.PostUserPageFuncDatas(_dto.UserId, _dto.PageId);
if (pageFunAuthView.CheckAuth == 0) return Ok(JsonView(false, "您没有查看权限"));
#endregion
#endregion
if (_dto.PortType == 1 || _dto.PortType == 2 || _dto.PortType == 3) // web/Android/IOS
{
string sqlWhere = string.Empty;
if (_dto.IsSure == 0) //未完成
{
sqlWhere += string.Format(@" And IsSure = 0");
}
else if (_dto.IsSure == 1) //已完成
{
sqlWhere += string.Format(@" And IsSure = 1");
}
if (!string.IsNullOrEmpty(_dto.SearchCriteria))
{
string tj = _dto.SearchCriteria;
sqlWhere += string.Format(@"And (ssd.Name Like '%{0}%' Or TeamName Like '%{1}%' Or ClientName Like '%{2}%' Or ClientName Like '%{3}%' Or su.CnName Like '%{4}%')",
tj, tj, tj, tj, tj);
}
string sql = string.Format(@"Select row_number() over(order by gdi.VisitDate Desc) as Row_Number,
gdi.Id,TourCode,ssd1.Id TeamLevId,ssd1.Name TeamLev,TeamName,
ClientName,ClientUnit,VisitDate,ssd.Id TeamTypeId, ssd.Name TeamType,
VisitDays,VisitPNumber,su.CnName JietuanOperator,IsSure,gdi.CreateTime,
pr.LastCollectionTime
From Grp_DelegationInfo gdi
Left Join Sys_SetData ssd On gdi.TeamDid = ssd.Id
Left Join Sys_SetData ssd1 On gdi.TeamLevSId = ssd1.Id
Left Join Sys_Users su On gdi.JietuanOperator = su.Id
Left Join (
SELECT Diid, MAX(CreateTime) LastCollectionTime
FROM Fin_ProceedsReceived
Where IsDel = 0
GROUP BY Diid
) pr On gdi.Id = pr.Diid
Where gdi.IsDel = 0 {0} ", sqlWhere);
RefAsync<int> total = 0;//REF和OUT不支持异步,想要真的异步这是最优解
var _DelegationList = await _sqlSugar.SqlQueryable<GroupStatementItemView>(sql).ToPageListAsync(_dto.PageIndex, _dto.PageSize, total);//ToPageAsync
var _view = new
{
PageFuncAuth = pageFunAuthView,
Data = _DelegationList
};
return Ok(JsonView(true, "查询成功!", _view, total));
}
else
{
return Ok(JsonView(false, "查询失败"));
}
}
/// <summary>
/// 团组报表
/// Details
/// </summary>
/// <param name="_dto">团组列表请求dto</param>
/// <returns></returns>
[HttpPost("PostGroupStatementDetails")]
[ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
public async Task<IActionResult> PostGroupStatementDetails(GroupStatementDetailsDto _dto)
{
#region 参数验证
if (_dto.UserId < 1) return Ok(JsonView(false, "员工Id为空"));
if (_dto.PageId < 1) return Ok(JsonView(false, "页面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();
#endregion
#region 团组收入
GroupIncomeView _giView = new GroupIncomeView();
/*
* 应收报表
*/
decimal frTotalAmount = 0.00M;//应收总金额
string _frSql = string.Format(@"Select fr.Id,fr.Diid,fr.PriceName,fr.Price,fr.Count,fr.Unit,fr.Currency,
sd.Name As CurrencyCode,sd.Remark As CurrencyName,fr.Rate,fr.ItemSumPrice
From Fin_ForeignReceivables fr
Left Join Sys_SetData sd On fr.Currency = sd.Id
Where fr.IsDel = 0 And fr.Diid = {0}",_dto.DiId);
List<Gsd_ForeignReceivablesView> _frViews = await _sqlSugar.SqlQueryable<Gsd_ForeignReceivablesView>(_frSql).ToListAsync();
frTotalAmount = _frViews.Sum(it => it.ItemSumPrice);
_giView.Receivables = _frViews;
_giView.ReceivableStr = string.Format(@"应收款合计：{0} CNY(人名币)", frTotalAmount.ConvertToDecimal1().ToString("#0.00"));
/*
* 已收报表
*/
decimal prTotalAmount = 0.00M;//已收总金额
string _prSql = string.Format(@"Select pr.Id,pr.Diid,pr.SectionTime,pr.Price,pr.Currency,
sd1.Name As CurrencyCode,sd1.Remark As CurrencyName,pr.Client,
pr.ReceivablesType,sd2.Name As ReceivablesTypeName,pr.Remark
From Fin_ProceedsReceived pr
Left Join Sys_SetData sd1 On pr.Currency = sd1.Id
Left Join Sys_SetData sd2 On pr.ReceivablesType = sd2.Id
Where pr.IsDel = 0 and pr.Diid = {0}", _dto.DiId);
List<Gsd_ProceedsReceivedView> _prViews = await _sqlSugar.SqlQueryable<Gsd_ProceedsReceivedView>(_prSql).ToListAsync();
prTotalAmount = _prViews.Sum(it => it.Price);
_giView.ProceedsReceiveds = _prViews;
_giView.ProceedsReceivedStr = string.Format(@"已收款合计：{0} CNY(人名币)", prTotalAmount.ConvertToDecimal1().ToString("#0.00"));
/*
* 超支费用
* 未挪表
*/
/*
* 收款退还
*/
decimal promTotalAmount = 0.00M;// 收款退还总金额
List<Gsd_PaymentRefundAndOtherMoneyView> _promView = new List<Gsd_PaymentRefundAndOtherMoneyView>();
string _ropSql = string.Format(@"Select u.CnName As Appliction,prom.Id As PrId,prom.DiId As PrDiId,prom.Price As PrPrice,
prom.PriceName AS PrPriceName,prom.CurrencyId As PrCurrencyId,
prom.PayType As PrPayType,prom.PriceType As PrPriceType,ccp.*
From Fin_PaymentRefundAndOtherMoney prom
Left Join Grp_CreditCardPayment ccp On prom.DiId = ccp.DIId And prom.Id = ccp.CId
Left Join Sys_Users u On ccp.CreateUserId = u.Id
Where prom.IsDel = 0 And prom.PayType = 1 And prom.PriceType = 1 And ccp.CTable = 285
And ccp.IsAuditGM = 1 And ccp.IsPay = 1
And prom.DiId = {0}", _dto.DiId);
var _promDatas = await _sqlSugar.SqlQueryable<Gsd_PaymentRefundAndOtherMoneyDataSource1View>(_ropSql).ToListAsync();
foreach (var ropItem in _promDatas)
{
string thisCueencyCode = "Unknown";
string thisCueencyName = "Unknown";
var currency = _setDatas.Where(it => it.Id == ropItem.PaymentCurrency).FirstOrDefault();
if (currency != null)
{
thisCueencyCode = currency.Name;
thisCueencyName = currency.Remark;
}
string orbitalPrivateTransferStr = "Unknown";
var orbitalPrivateTransfer = _setDatas.Where(it => it.Id == ropItem.OrbitalPrivateTransfer).FirstOrDefault();
if (orbitalPrivateTransfer != null)
{
orbitalPrivateTransferStr = orbitalPrivateTransfer.Name;
}
string payStr = "Unknown";
var pay = _setDatas.Where(it => it.Id == ropItem.PayDId).FirstOrDefault();
if (pay != null)
{
payStr = pay.Name;
}
Gsd_PaymentRefundAndOtherMoneyView gsd_PaymentRefund = new Gsd_PaymentRefundAndOtherMoneyView()
{
Id = ropItem.Id,
DiId = ropItem.DIId,
PriceName = ropItem.PrPriceName,
PayCurrencyCode = thisCueencyCode,
PayCurrencyName = thisCueencyName,
Price = ropItem.PrPrice,
CNYPrice = ropItem.RMBPrice,
ThisRate = ropItem.DayRate,
Payee = ropItem.Payee,
PayTime = ropItem.AuditGMDate,
FeeType = orbitalPrivateTransferStr,
PayType = payStr,
PayStatus = ropItem.IsPay == 0 ? "未付款" : "已付款",
Applicant = ropItem.Appliction
};
_promView.Add(gsd_PaymentRefund);
}
promTotalAmount = _promView.Sum(it => it.CNYPrice);
_giView.PaymentRefundAndOtherMoneys = _promView;
_giView.PaymentRefundAndOtherMoneyStr = string.Format(@"人名币总费用：{0} CNY", promTotalAmount.ConvertToDecimal1().ToString("#0.00"));
decimal BalancePayment = frTotalAmount - prTotalAmount + promTotalAmount;
_view.GroupIncome = _giView;
_view.GroupIncomeStr = string.Format(@"剩余尾款：{0} (包含了收款退还费用数据)", BalancePayment.ConvertToDecimal1().ToString("#0.00"));
#endregion
#region 团组支出
GroupExpenditureView _geView = new GroupExpenditureView();
#region 酒店预定费用
#endregion
// Select hr.Id As HrId,hr.DiId As HrDiId,hr.City,hr.HotelName,hr.CheckInDate,hr.CheckOutDate,
//ccp.PaymentCurrency,sd1.Name As PaymentCurrencyName,hr.SingleRoomPrice,hr.SingleRoomCount,
//hr.DoubleRoomPrice,hr.DoubleRoomCount,hr.SuiteRoomPrice,hr.SuiteRoomCount,hr.OtherRoomPrice,
//hr.OtherRoomCount,hr.GovernmentRent,hr.CityTax,hr.Commission,ccp.PayMoney,ccp.RMBPrice As CNYPrice,
//ccp.DayRate,ccp.Payee,
//From Grp_HotelReservations hr
// Inner Join Grp_CreditCardPayment ccp On hr.Id = ccp.CId
// Left Join Sys_SetData sd1 On ccp.PaymentCurrency = sd1.Id
// Where hr.IsDel = 0 And ccp.IsDel = 0 And ccp.CTable = 76 And ccp.IsAuditGM = 1 And ccp.IsPay = 1
// And hr.DiId = 2337
_view.GroupExpenditure = _geView;
#endregion
return Ok(JsonView(true, "查询成功!", _view));
}
else
{
return Ok(JsonView(false, "查询成功"));
}
}
#endregion
}
}