using Aspose.Cells;
using Aspose.Words;
using EyeSoft.Extensions;
using EyeSoft.IO;
using FluentValidation;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using OASystem.API.OAMethodLib;
using OASystem.API.OAMethodLib.File;
using OASystem.API.OAMethodLib.QiYeWeChatAPI.AppNotice;
using OASystem.Domain.Dtos.Financial;
using OASystem.Domain.Dtos.Groups;
using OASystem.Domain.Entities.Customer;
using OASystem.Domain.Entities.Financial;
using OASystem.Domain.Entities.Groups;
using OASystem.Domain.ViewModels.Financial;
using OASystem.Domain.ViewModels.Groups;
using OASystem.Infrastructure.Repositories.Financial;
using OASystem.Infrastructure.Repositories.Groups;
using OfficeOpenXml;
using SqlSugar.Extensions;
using System.Collections;
using System.Data;
using System.Data.OleDb;
using System.Diagnostics;
using System.Globalization;
using System.IO.Compression;
using NPOI.SS.Formula.Functions;
using Org.BouncyCastle.Asn1.X509.Qualified;
using MathNet.Numerics;
using static OASystem.API.OAMethodLib.JWTHelper;
using Org.BouncyCastle.Asn1.Ocsp;
namespace OASystem.API.Controllers
{
/// <summary>
/// 财务模块
/// </summary>
[Route("api/[controller]/[action]")]
[ApiController]
public class FinancialController : ControllerBase
{
private readonly IMapper _mapper;
private readonly IConfiguration _config;
private readonly SqlSugarClient _sqlSugar;
private readonly HttpClient _httpClient;
private readonly SetDataTypeRepository _setDataTypeRep;
private readonly SetDataRepository _setDataRep;
private readonly DailyFeePaymentRepository _daiRep; //日付申请仓库
private readonly TeamRateRepository _teamRateRep; //团组汇率仓库
private readonly ForeignReceivablesRepository _ForForeignReceivablesRep; //对外收款账单仓库
private readonly ProceedsReceivedRepository _proceedsReceivedRep; //已收款项仓库
private readonly PaymentRefundAndOtherMoneyRepository _paymentRefundAndOtherMoneyRep; //收款退还与其他款项 仓库
private readonly DelegationInfoRepository _delegationInfoRep; //团组信息 仓库
private readonly ForeignReceivablesRepository _foreignReceivablesRepository;
/// <summary>
/// 初始化
/// </summary>
public FinancialController(IMapper mapper, IConfiguration configuration, DailyFeePaymentRepository daiRep, SqlSugarClient sqlSugar, SetDataTypeRepository setDataTypeRep,
TeamRateRepository teamRateRep, ForeignReceivablesRepository ForForeignReceivablesRep, ProceedsReceivedRepository proceedsReceivedRep,
PaymentRefundAndOtherMoneyRepository paymentRefundAndOtherMoneyRep, HttpClient httpClient, DelegationInfoRepository delegationInfoRep, SetDataRepository setDataRep,
ForeignReceivablesRepository foreignReceivablesRepository)
{
_mapper = mapper;
_config = configuration;
_daiRep = daiRep;
_sqlSugar = sqlSugar;
_setDataTypeRep = setDataTypeRep;
_teamRateRep = teamRateRep;
_ForForeignReceivablesRep = ForForeignReceivablesRep;
_proceedsReceivedRep = proceedsReceivedRep;
_paymentRefundAndOtherMoneyRep = paymentRefundAndOtherMoneyRep;
_httpClient = httpClient;
_delegationInfoRep = delegationInfoRep;
_setDataRep = setDataRep;
_foreignReceivablesRepository = foreignReceivablesRepository;
}
#region 日付申请
/// <summary>
/// 获取日付申请 基础数据源
/// </summary>
/// <param name="dto"> 日付申请 分页 dto</param>
/// <returns></returns>
[HttpPost]
[ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
public async Task<IActionResult> PostPageSearchDailyPaymentPriceTypeData(PortDtoBase dto)
{
var currUserInfo = JwtHelper.SerializeJwt(HttpContext.Request.Headers.Authorization);
if (currUserInfo == null) return Ok(JsonView(false, "请传入token!"));
var result = await _daiRep.GetPagePriceTypeData(dto,currUserInfo.UserId);
if (result == null || result.Code != 0)
{
return Ok(JsonView(false, result.Msg));
}
var data = result.Data;
return Ok(JsonView(data));
}
/// <summary>
/// 获取日付申请 基础数据源 - 转账表识
/// </summary>
/// <param name="dto"> 日付申请 分页 dto</param>
/// <returns></returns>
[HttpPost]
[ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
public async Task<IActionResult> GetSearchDailyPaymentPriceTypeAddData(PortDtoBase dto)
{
var result = await _daiRep.GetPriceTypeAddData(dto);
if (result == null || result.Code != 0)
{
return Ok(JsonView(false, result.Msg));
}
var data = result.Data;
return Ok(JsonView(data));
}
/// <summary>
/// 日付申请 Page Search
/// </summary>
/// <param name="dto"> 日付申请 分页 dto</param>
/// <returns></returns>
[HttpPost]
[ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
public async Task<IActionResult> PostPageSearchDailyPaymentList(PageDailyFeePaymentDto dto)
{
var result = await _daiRep.GetPageSearchAll(dto);
if (result == null || result.Code != 0)
{
return Ok(JsonView(false, result.Msg));
}
var data = result.Data;
if (data == null)
{
return Ok(JsonView(false, result.Msg));
}
return Ok(JsonView(data));
}
/// <summary>
/// 日付申请 Single Search By Id
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
[HttpPost]
[ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
public async Task<IActionResult> PostSearchDailyPaymentInfo(SearchDailyFeePaymentDto dto)
{
var result = await _daiRep.GetSearchById(dto);
if (result == null || result.Code != 0)
{
return Ok(JsonView(false, result.Msg));
}
return Ok(JsonView(result.Data));
}
/// <summary>
/// 日付申请 添加
/// </summary>
/// <param name="dto"> 日付申请 添加 dto</param>
/// <returns></returns>
[HttpPost]
[ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
public async Task<IActionResult> PostAddDailyPayment(AddDailyFeePaymentDto dto)
{
var result = await _daiRep.Add(dto);
if (result == null || result.Code != 0)
{
return Ok(JsonView(false, result.Msg));
}
#region 应用推送
try
{
int dailyId = result.Data.GetType().GetProperty("dailyId").GetValue(result.Data, null);
int sign = result.Data.GetType().GetProperty("sign").GetValue(result.Data, null);
await AppNoticeLibrary.DailyPayReminders_Create_ToCaiwuChat(dailyId, sign, QiyeWeChatEnum.CaiWuChat);
//2024-10-21 新增LZ UID
var userIds = new List<int>() { 21 };
string title = $"系统通知";
var dailyInfo = await _sqlSugar.Queryable<Fin_DailyFeePayment>().Where(x => x.Id == dailyId).FirstAsync();
string content = $"[新增-日付申请]一项费用：[费用说明：{dailyInfo.Instructions}]{dailyInfo.SumPrice.ToString("#0.00")} CNY;";
await GeneralMethod.MessageIssueAndNotification(MessageTypeEnum.DailyPayment, title, content, userIds, 0);
}
catch (Exception ex)
{
}
#endregion
return Ok(JsonView(true));
}
/// <summary>
/// 日付申请 Update
/// </summary>
/// <param name="dto"> 日付申请 修改 dto</param>
/// <returns></returns>
[HttpPost]
[ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
public async Task<IActionResult> PostEditDailyPayment(EditDailyFeePaymentDto dto)
{
var result = await _daiRep.Edit(dto);
if (result == null || result.Code != 0)
{
return Ok(JsonView(false, result.Msg));
}
#region 应用推送
try
{
int dailyId = result.Data.GetType().GetProperty("dailyId").GetValue(result.Data, null);
int sign = result.Data.GetType().GetProperty("sign").GetValue(result.Data, null);
await AppNoticeLibrary.DailyPayReminders_Create_ToCaiwuChat(dailyId, sign, QiyeWeChatEnum.CaiWuChat);
//2024-10-21 新增LZ UID
var userIds = new List<int>() { 21 };
string title = $"系统通知";
var dailyInfo = await _sqlSugar.Queryable<Fin_DailyFeePayment>().Where(x => x.Id == dailyId).FirstAsync();
string content = $"[更新-日付申请]一项费用：[费用说明：{dailyInfo.Instructions}]{dailyInfo.SumPrice.ToString("#0.00")} CNY;";
await GeneralMethod.MessageIssueAndNotification(MessageTypeEnum.DailyPayment, title, content, userIds, 0);
}
catch (Exception ex)
{
}
#endregion
return Ok(JsonView(true));
}
/// <summary>
/// 日付申请 Del
/// </summary>
/// <param name="dto"> 日付申请 删除 dto</param>
/// <returns></returns>
[HttpPost]
[ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
public async Task<IActionResult> PostDelDailyPayment(DelDailyFeePaymentDto dto)
{
_sqlSugar.BeginTran();
var result = await _daiRep.Del(dto);
//删除日付关联表
var delCount = _sqlSugar.Updateable<Fin_RoyaltyForm>()
.Where(x => x.IsDel == 0 && x.DayOverhead == dto.Id)
.SetColumns(x => new Fin_RoyaltyForm
{
DeleteTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"),
DeleteUserId = dto.UserId,
IsDel = 1
}).ExecuteCommand();
_sqlSugar.CommitTran();
if (result == null || result.Code != 0)
{
return Ok(JsonView(false, result.Msg));
}
return Ok(JsonView(true));
}
/// <summary>
/// 日付申请 财务审核
/// </summary>
/// <param name="dto"> dto </param>
/// <returns></returns>
[HttpPost]
[ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
public async Task<IActionResult> PostDelDailyPaymentAudit(DP_AuditStatusDto dto)
{
var result = await _daiRep.DailyPaymentAudit(dto);
if (result == null || result.Code != 0)
{
return Ok(JsonView(false, result.Msg));
}
return Ok(JsonView(true));
}
/// <summary>
/// 日付申请 Single Excel Download
/// </summary>
/// <param name="dto"> dto </param>
/// <returns></returns>
[HttpPost]
[ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
public async Task<IActionResult> PostExcelDailyPaymentDownload(SearchDailyFeePaymentDto dto)
{
if (dto.PortType == 1 || dto.PortType == 2)
{
Fin_DailyFeePaymentInfolView feeData = new Fin_DailyFeePaymentInfolView();
string feeSql = string.Format(@"Select * From Fin_DailyFeePayment
Where IsDel=0 And Id = {0} ", dto.Id);
feeData = await _sqlSugar.SqlQueryable<Fin_DailyFeePaymentInfolView>(feeSql).FirstAsync();
if (feeData == null)
{
return Ok(JsonView(false, "暂无数据!"));
}
string feeContentSql = string.Format(@"Select * From Fin_DailyFeePaymentContent
Where IsDel=0 And DFPId = {0} ", dto.Id);
feeData.FeeContents = await _sqlSugar.SqlQueryable<Fin_DailyFeePaymentContentInfolView>(feeContentSql).ToListAsync();
if (feeData != null)
{
string userName = string.Empty;
string userSql = string.Format("Select * From Sys_Users Where Id={0} And Isdel = {1}", feeData.CreateUserId, 0);
Sys_Users user = await _sqlSugar.SqlQueryable<Sys_Users>(userSql).FirstAsync();
if (user != null) { userName = user.CnName; }
var setData = _setDataTypeRep.QueryDto<Sys_SetData, Fin_DailyFeePaymentPagePriceSubTypeView>().ToList();
//48人员费用 49办公费用 50 销售费用 51 其他费用 55 大运会
var priceSubTypeData = setData.Where(s => s.STid == 55).ToList();
Dictionary<string, object> pairs = new Dictionary<string, object>();
List<DataTable> datas = new List<DataTable>();
//if (priceSubTypeData.Where(s => s.Id == feeData.PriceTypeId).ToList().Count() > 0)//大运会专属模板
//{
// //AsposeHelper.ExpertExcelToModel("日常费用付款申请模板-大运会数据.xls", "DailyPayment", "大运会所有日常费用付款申请.xls",
// // pairs, datas);
//}
//else //日付常规模板
//{
pairs.Clear();
pairs.Add("Opertor", userName);
pairs.Add("DateTime", feeData.CreateTime.ToString("yyyy-MM-dd HH:mm:ss"));
pairs.Add("FAuditStatus", feeData.FAuditDesc);
pairs.Add("MAuditStatus", feeData.MAuditDesc);
pairs.Add("SumPrice", feeData.SumPrice);
DataTable data = AsposeHelper.ListToDataTable("DailyFeePayment", feeData.FeeContents);
datas.Clear();
datas.Add(data);
string fileName = string.Format("{0}-日常费用付款申请.xlsx", feeData.Instructions);
string msg = AsposeHelper.ExpertExcelToModel("日常费用付款申请模板.xlsx", "DailyPayment", fileName, pairs, datas);
return Ok(JsonView(true, msg));
//}
}
else
{
return Ok(JsonView(false, "暂无数据!"));
}
}
return Ok(JsonView(true));
}
/// <summary>
/// 日付申请
/// 总经理未审核 日付信息
/// </summary>
/// <param name="dto"> dto </param>
/// <returns></returns>
[HttpGet]
[ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
public async Task<IActionResult> DailyPaymentGMUnAudited()
{
var dailyInfos = await _sqlSugar.Queryable<Fin_DailyFeePayment>()
.LeftJoin<Sys_Users>((dfp, u) => dfp.CreateUserId == u.Id)
.Where(dfp => dfp.IsDel == 0 &&
//dfp.FAudit == 1 &&
//dfp.IsPay == 0 &&
dfp.MAudit == 0
)
.OrderBy(dfp => dfp.CreateTime, OrderByType.Desc)
//.OrderBy(dfp => dfp.FAudit, OrderByType.Desc)
.Select((dfp, u) => new
{
id = dfp.Id,
amountName = dfp.Instructions,
amount = dfp.SumPrice,
fAuditStatus = dfp.FAudit == 1 ? "审核通过" :
dfp.FAudit == 2 ? "审核未通过" : "未审核",
fAuditDate = dfp.FAuditDate,
applicant = u.CnName,
applicantDate = dfp.CreateTime
})
//.ToListAsync();
.CountAsync();
//int count = dailyInfos.Count;
return Ok(JsonView(true, "查询成功", dailyInfos));
}
#endregion
#region 团组提成
/// <summary>
/// 提成 Page Search
/// </summary>
/// <param name="dto"> 提成 分页 dto</param>
/// <returns></returns>
[HttpPost]
[ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
public async Task<IActionResult> PostPageSearchCommissionList(GroupCommissionDto dto)
{
var data = await GroupCommission.GetCommissionPageList(dto);
return Ok(JsonView(data.Data));
}
#endregion
#region 团组汇率
/// <summary>
/// 团组汇率 Select数据源(团组列,汇率列)
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
[HttpPost]
[ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
public async Task<IActionResult> GetGroupRateDataSources(TeamRateDto dto)
{
try
{
//迁移数据更新团组汇率
//Result teamRateData1 = await _teamRateRep.GetGroupRateChangeData();
//var data = await _teamRateRep.PostGroupTeamRateHot();
Stopwatch stopwatch = Stopwatch.StartNew();
GroupNameDto groupNameDto = new GroupNameDto() { PortType = dto.PortType };
var groups = await _delegationInfoRep.GetGroupNameList(groupNameDto);
List<CurrencyHot> _currData = new List<CurrencyHot>();
string currData = await RedisRepository.RedisFactory.CreateRedisRepository().StringGetAsync<string>("GroupTeamCurrencyData");//string 取
if (!string.IsNullOrEmpty(currData))
{
_currData = JsonConvert.DeserializeObject<List<CurrencyHot>>(currData);
}
else
{
_currData = await _teamRateRep.PostGroupTeamRateHot();
//过期时间 25 Hours
TimeSpan ts = DateTime.Now.AddHours(25).TimeOfDay;
await RedisRepository.RedisFactory.CreateRedisRepository().StringSetAsync("GroupTeamCurrencyData", JsonConvert.SerializeObject(_currData), ts);
}
var _data = new { GroupData = groups.Data, TeamRateData = _currData };
stopwatch.Stop();
return Ok(JsonView(true, $"查询成功!耗时：{stopwatch.ElapsedMilliseconds / 1000}s", _data));
}
catch (Exception ex)
{
return Ok(JsonView(false, ex.Message));
}
}
///// <summary>
///// 团组汇率 changge
///// </summary>
///// <returns></returns>
//[HttpPost]
//[ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
//public async Task<IActionResult> ChangeGroupRateInfo()
//{
// try
// {
// Result teamRateData = await _teamRateRep.GetGroupRateChangeData();
// if (teamRateData.Code != 0)
// {
// return Ok(JsonView(false, teamRateData.Msg));
// }
// return Ok(JsonView(true, teamRateData.Msg, teamRateData.Data));
// }
// catch (Exception ex)
// {
// return Ok(JsonView(false, ex.Message));
// throw;
// }
//}
/// <summary>
/// 团组汇率 Select汇率详情
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
[HttpPost]
[ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
public async Task<IActionResult> GetGroupRateInfo(TeamRateInfoDto dto)
{
try
{
Result teamRateData = await _teamRateRep.GetGroupRateInfoByDiid(dto);
if (teamRateData.Code != 0)
{
return Ok(JsonView(false, teamRateData.Msg));
}
return Ok(JsonView(true, teamRateData.Msg, teamRateData.Data));
}
catch (Exception ex)
{
return Ok(JsonView(false, ex.Message));
throw;
}
}
/// <summary>
/// 团组汇率 添加 or 更新
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
[HttpPost]
[ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
public async Task<IActionResult> PostGroupRateUpdate(TeamRateUpdateDto dto)
{
try
{
Result teamRateData = await _teamRateRep.PostGroupRateUpdate(dto);
if (teamRateData.Code != 0)
{
return Ok(JsonView(false, teamRateData.Msg));
}
return Ok(JsonView(true, teamRateData.Msg, teamRateData.Data));
}
catch (Exception ex)
{
return Ok(JsonView(false, ex.Message));
throw;
}
}
#endregion
#region 对外收款账单 关联已收款项
/// <summary>
/// 对外收款账单 Select数据源(团组名,币种,汇款方式)
/// 关联已收款项
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
[HttpPost]
[ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
public async Task<IActionResult> GetForeignReceivablesDataSources(ForeignReceivablesDataSourcesDto dto)
{
return Ok(await _ForForeignReceivablesRep.GetDataSource(dto));
}
/// <summary>
/// 对外收款账单
/// 账单详情
/// 关联已收款项
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
[HttpPost]
[ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
public async Task<IActionResult> GetGroupReceivablesInfoByDiId(ForForeignReceivablesInfoDto dto)
{
try
{
Result ffrData = await _ForForeignReceivablesRep.GetGroupReceivablesInfoByDiId(dto);
if (ffrData.Code != 0)
{
return Ok(JsonView(false, ffrData.Msg));
}
return Ok(JsonView(true, ffrData.Msg, ffrData.Data));
}
catch (Exception ex)
{
return Ok(JsonView(false, ex.Message));
throw;
}
}
/// <summary>
/// 对外收款账单
/// 账单 删除
/// 关联已收款项
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
[HttpPost]
[ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
public async Task<IActionResult> PostGroupReceivablesDel(DelForForeignReceivablesInfoDto dto)
{
try
{
Result ffrData = await _ForForeignReceivablesRep._Del(dto);
if (ffrData.Code != 0)
{
return Ok(JsonView(false, ffrData.Msg));
}
return Ok(JsonView(true, ffrData.Msg, ffrData.Data));
}
catch (Exception ex)
{
return Ok(JsonView(false, ex.Message));
throw;
}
}
/// <summary>
/// 对外收款账单
/// 添加 And 更新
/// 关联已收款项
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
[HttpPost]
[ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
public async Task<IActionResult> PostReceivablesOperate(ForeignReceivablesAddAndUpdateDto dto)
{
try
{
Result ffrData = await _ForForeignReceivablesRep.PostReceivablesOperate(dto);
if (ffrData.Code != 0)
{
return Ok(JsonView(false, ffrData.Msg));
}
return Ok(JsonView(true, ffrData.Msg, ffrData.Data));
}
catch (Exception ex)
{
return Ok(JsonView(false, ex.Message));
throw;
}
}
/// <summary>
/// 已收款项
/// 账单 删除
/// 关联已收款项
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
[HttpPost]
[ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
public async Task<IActionResult> PostAmountReceivedDel(ProceedsReceivedDelDto dto)
{
try
{
Result ffrData = await _proceedsReceivedRep._Del(dto);
if (ffrData.Code != 0)
{
return Ok(JsonView(false, ffrData.Msg));
}
return Ok(JsonView(true, ffrData.Msg, ffrData.Data));
}
catch (Exception ex)
{
return Ok(JsonView(false, ex.Message));
throw;
}
}
/// <summary>
/// 已收款项
/// 添加 And 更新
/// 关联已收款项
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
[HttpPost]
[ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
public async Task<IActionResult> PostAmountReceivedOperate(ProceedsReceivedDto dto)
{
try
{
Result ffrData = await _proceedsReceivedRep.PostAmountReceivedOperate(dto);
if (ffrData.Code != 0)
{
return Ok(JsonView(false, ffrData.Msg));
}
return Ok(JsonView(true, ffrData.Msg, ffrData.Data));
}
catch (Exception ex)
{
return Ok(JsonView(false, ex.Message));
throw;
}
}
/// <summary>
/// 财务 已收款项
/// 分配已收款项至 应收项下
/// 关联已收款项
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
[HttpPost]
[ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
public async Task<IActionResult> PostAllocateAmountReceived(AllocateAmountReceivedDto dto)
{
try
{
Result ffrData = await _proceedsReceivedRep.PostAllocateAmountReceived(dto);
if (ffrData.Code != 0)
{
return Ok(JsonView(false, ffrData.Msg));
}
return Ok(JsonView(true));
}
catch (Exception ex)
{
return Ok(JsonView(false, ex.Message));
throw;
}
}
///// <summary>
///// 财务 收款账单
///// 导出Word(北京,四川)
///// </summary>
///// <param name="dto"></param>
///// <returns></returns>
//[HttpPost]
//[ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
//public async Task<IActionResult> PostAccountReceivableWordExport(AccountReceivableWordExportDto dto)
//{
// try
// {
// //模板处理
// string typeName = string.Empty;
// if (dto.TemplateType == 1) //四川
// {
// typeName = "四川";
// }
// else if (dto.TemplateType == 2) //北京
// {
// typeName = "北京";
// }
// else return Ok(JsonView(false,"请选择正确的模板类型!"));
// string wordTempName = string.Format("收款账单({0})模板.doc", typeName);
// }
// catch (Exception ex)
// {
// return Ok(JsonView(false, ex.Message));
// }
//}
#endregion
#region 对外收款账单
/// <summary>
/// 对外收款账单
/// 数据源
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
[HttpPost]
[ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
public async Task<IActionResult> PostGroupReceivablesDataSource(ForeignReceivablesDataSourcesDto dto)
{
return Ok(await _ForForeignReceivablesRep.PostDataSource(dto));
}
/// <summary>
/// 对外收款账单
/// 账单详情
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
[HttpPost]
[ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
public async Task<IActionResult> PostGroupReceivablesInfoByDiId(ForForeignReceivablesNewDto dto)
{
return Ok(await _ForForeignReceivablesRep.PostGroupReceivablesInfoByDiId(dto));
}
/// <summary>
/// 对外收款账单
/// 添加 And 更新
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
[HttpPost]
[ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
public async Task<IActionResult> PostReceivablesSave(ForeignReceivablesSaveDto dto)
{
return Ok(await _ForForeignReceivablesRep.PostReceivablesSave(dto));
}
/// <summary>
/// 对外收款账单
/// 审核
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
[HttpPost]
[ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
public async Task<IActionResult> PostReceivablesAudit(FeeAuditDto dto)
{
return Ok(await _ForForeignReceivablesRep.FeeAudit(dto));
}
/// <summary>
/// 已收账单
/// 删除
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
[HttpPost]
[ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
public async Task<IActionResult> PostReceivablesDel(ForeignReceivablesDelDto dto)
{
Result ffrData = await _ForForeignReceivablesRep.PostReceivablesDel(dto);
if (ffrData.Code != 0)
{
return Ok(JsonView(false, ffrData.Msg));
}
return Ok(JsonView(true, ffrData.Msg, ffrData.Data));
}
/// <summary>
/// 已收账单
/// File Download
/// Init
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
[HttpPost]
[ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
public async Task<IActionResult> PostReceivablesFeilDownloadInit()
{
return Ok(JsonView(true, "操作成功！", new List<dynamic> {
new { Id = 1, Name = "生成收款单(四川)" },
new { Id = 2, Name = "生成收款单(北京)" },
new { Id = 3, Name = "汇款账单" }
}
));
}
private class EnterExitCostCurrency
{
public string Name { get; set; }
public string Code { get; set; }
public decimal Rate { get; set; }
}
/// <summary>
/// 已收账单
/// File Downloasd
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
[HttpPost]
[ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
public async Task<IActionResult> PostReceivablesFeilDownload(ForeignReceivablesFeilDownloadDto dto)
{
try
{
if (dto.DiId < 1)
{
return Ok(JsonView(false, "请传入有效DiId参数！"));
}
if (dto.FileType < 1 || dto.FileType > 3)
{
return Ok(JsonView(false, "请传入有效FileType参数！ 1 生成收款单(四川) 2 生成收款单(北京) 3 汇款账单"));
}
var _currencyDatas = _sqlSugar.Queryable<Sys_SetData>().Where(x => x.IsDel == 0 && x.STid == 66).ToList();
var _DelegationInfo = _sqlSugar.Queryable<Grp_DelegationInfo>().Where(it => it.IsDel == 0 && it.Id == dto.DiId).First();
if (_DelegationInfo == null)
{
return Ok(JsonView(false, "暂无团组信息！！"));
}
if (dto.FileType == 1 || dto.FileType == 2)
{
var _ForeignReceivables = _sqlSugar.Queryable<Fin_ForeignReceivables>().Where(it => it.IsDel == 0 && it.Diid == dto.DiId).ToList();
if (_ForeignReceivables.Count < 1)
{
return Ok(JsonView(false, "暂无收款信息！！"));
}
string tempName = "";
if (dto.FileType == 1) tempName = $"收款账单(四川)模板.docx";
else if (dto.FileType == 2) tempName = $"收款账单(北京)模板.docx";
string tempPath = AppSettingsHelper.Get("WordBasePath") + $"ForeignReceivables/Temp/{tempName}";
//载入模板
Document doc = new Document(tempPath);
DocumentBuilder builder = new DocumentBuilder(doc);
#region 替换Word模板书签内容
//这里可以创建个DataTable循环添加书签的值，这里提示一下就不多做修改了
//付款方
if (doc.Range.Bookmarks["To"] != null)
{
Bookmark mark = doc.Range.Bookmarks["To"];
mark.Text = _ForeignReceivables[0].To == null ? "" : _ForeignReceivables[0].To.ToString();
}
//付款方电话
if (doc.Range.Bookmarks["ToTel"] != null)
{
Bookmark mark = doc.Range.Bookmarks["ToTel"];
mark.Text = _ForeignReceivables[0].ToTel == null ? "" : _ForeignReceivables[0].ToTel.ToString();
}
//导出时间
if (doc.Range.Bookmarks["Date"] != null)
{
Bookmark mark = doc.Range.Bookmarks["Date"];
mark.Text = DateTime.Now.ToString("yyyy-MM-dd");
}
//注
if (doc.Range.Bookmarks["Attention"] != null)
{
Bookmark mark = doc.Range.Bookmarks["Attention"];
mark.Text = _ForeignReceivables[0].Attention == null ? "" : _ForeignReceivables[0].Attention.ToString();
}
//团队名称
if (doc.Range.Bookmarks["Team"] != null)
{
Bookmark mark = doc.Range.Bookmarks["Team"];
mark.Text = _DelegationInfo.VisitCountry == null ? "" : _DelegationInfo.VisitCountry.Replace("|", "、").ToString();
}
//付款日期
if (doc.Range.Bookmarks["PayDate"] != null)
{
Bookmark mark = doc.Range.Bookmarks["PayDate"];
mark.Text = _ForeignReceivables[0].PayDate == null ? "" : Convert.ToDateTime(_ForeignReceivables[0].PayDate).ToString("yyyy年MM月dd日");
}
decimal sumPrice = 0;
//各项费用
if (doc.Range.Bookmarks["PayItemContent"] != null)
{
string items = "";
foreach (var fr in _ForeignReceivables)
{
var currInfo = _sqlSugar.Queryable<Sys_SetData>().Where(it => it.Id == fr.Currency).First();
items += $"{fr.PriceName} {currInfo?.Name} {fr.Price.ToString("#0.00")} * {fr.Count} {fr.Unit} * {fr.Rate}.................. RMB {fr.ItemSumPrice.ToString("#0.00")}\n";
sumPrice += fr.ItemSumPrice;
}
Bookmark mark = doc.Range.Bookmarks["PayItemContent"];
mark.Text = items;
}
//合计
if (doc.Range.Bookmarks["Total"] != null)
{
Bookmark mark = doc.Range.Bookmarks["Total"];
mark.Text = sumPrice.ToString("#0.00");
}
#endregion
//文件名
string strFileName = _DelegationInfo.TeamName + "-收款账单.docx";
//去水印
new Aspose.Words.License().SetLicense(new MemoryStream(Convert.FromBase64String(AsposeHelper.asposeKey)));
doc.Save(AppSettingsHelper.Get("WordBasePath") + "ForeignReceivables/File/" + strFileName);
string url = AppSettingsHelper.Get("WordBaseUrl") + "Office/Word/ForeignReceivables/File/" + strFileName;
return Ok(JsonView(true, "成功", new { Url = url }));
}
else if (dto.FileType == 3) //汇款通知
{
var _EnterExitCosts = _sqlSugar.Queryable<Grp_EnterExitCost>().Where(it => it.IsDel == 0 && it.DiId == dto.DiId).First();
var _DayAndCosts = _sqlSugar.Queryable<Grp_DayAndCost>().Where(it => it.IsDel == 0 && it.DiId == dto.DiId && it.NationalTravelFeeId > 0).ToList();
if (_EnterExitCosts == null)
{
return Ok(JsonView(false, "该团组未填写出入境费用；"));
}
var _EnterExitCostCurrencys = new List<EnterExitCostCurrency>();
if (!string.IsNullOrEmpty(_EnterExitCosts.CurrencyRemark))
{
var currency1 = _EnterExitCosts.CurrencyRemark.Split("|");
foreach (var item in currency1)
{
var currency2 = item.Split(":");
var currency3 = currency2[0].Split("(");
var currencyName = currency3[0].ToString();
var currencyCode = currency3[1].Split(")")[0].ToString();
_EnterExitCostCurrencys.Add(new EnterExitCostCurrency
{
Name = currencyName,
Code = currencyCode,
Rate = Convert.ToDecimal(currency2[1] ?? "0")
});
}
}
var _cityFee = _sqlSugar.Queryable<Grp_NationalTravelFee>().Where(it => it.IsDel == 0).ToList();
foreach (var item in _DayAndCosts)
{
var cityInfo = _cityFee.Where(it => it.Id == item.NationalTravelFeeId).FirstOrDefault();
if (cityInfo != null)
{
if (cityInfo.City.Contains("全部城市") || cityInfo.City.Contains("其他城市") || cityInfo.City.Contains("所有城市"))
{
item.Place = cityInfo.Country;
}
else item.Place = cityInfo.City;
}
}
//数据源
List<Grp_DayAndCost> dac1 = _DayAndCosts.Where(it => it.Type == 1).ToList(); //住宿费
List<Grp_DayAndCost> dac2 = _DayAndCosts.Where(it => it.Type == 2).ToList(); //伙食费
List<Grp_DayAndCost> dac3 = _DayAndCosts.Where(it => it.Type == 3).ToList(); //公杂费
List<Grp_DayAndCost> dac4 = _DayAndCosts.Where(it => it.Type == 4).ToList(); //培训费
//币种Data
var currData = _sqlSugar.Queryable<Sys_SetData>().Where(it => it.IsDel == 0 && it.STid == 66).ToList();
var DeleClientList = _sqlSugar.Queryable<Grp_TourClientList>()
.LeftJoin<Crm_DeleClient>((tcl, dc) => tcl.ClientId == dc.Id && dc.IsDel == 0)
.LeftJoin<Crm_CustomerCompany>((tcl, dc, cc) => dc.CrmCompanyId == cc.Id && dc.IsDel == 0)
.Where((tcl, dc, cc) => tcl.IsDel == 0 && tcl.DiId == dto.DiId)
.Select((tcl, dc, cc) => new
{
Name = dc.LastName + dc.FirstName,
Sex = dc.Sex,
Birthday = dc.BirthDay,
Company = cc.CompanyFullName.Replace("\n", ""),
Job = dc.Job,
AirType = tcl.ShippingSpaceTypeId
})
.ToList();
if (DeleClientList.Count < 1)
{
return Ok(JsonView(false, "暂无团组成员,请先填写团组成员！！！"));
}
var _ClientNames = DeleClientList.Select(x => x.Name).ToList();
var _GroupClient = DeleClientList.GroupBy(x => x.Company).ToList();
Dictionary<string, string> bookmarkArr = null;
string tempPath = AppSettingsHelper.Get("WordBasePath") + $"ForeignReceivables/Temp/汇款通知.docx";
//载入模板
Document doc = new Document(tempPath);
DocumentBuilder builder = null;
List<string> filesToZip = new List<string>();
foreach (var ClientItem in _GroupClient) //遍历单位
{
doc = new Document(tempPath);
builder = new DocumentBuilder(doc);
Paragraph paragraph = new Paragraph(doc);
bookmarkArr = new Dictionary<string, string>();
bookmarkArr.Add("titleClientUnit", ClientItem.Key); //title单位
bookmarkArr.Add("ClientUnit", ClientItem.Key); //单位
bookmarkArr.Add("VisitStartDate", _DelegationInfo.VisitStartDate.ToString("yyyy年MM月dd日"));//出发日期
bookmarkArr.Add("name", string.Join(",", ClientItem.Select(x => x.Name).ToArray()).TrimEnd(','));//全部人员信息
bookmarkArr.Add("VisitCountry", _DelegationInfo.VisitCountry.Replace("|", "、"));//出访国家
bookmarkArr.Add("dayTime", DateTime.Now.ToString("yyyy年MM月dd日"));//今天日期
var ClientItemList = ClientItem.ToList();
string UsersTop = string.Empty;//word中人员以及金额
decimal WordAllPrice = 0.00M;
//int[] infoColumn = new int[5] {dac1.Select(x=>x.Place.Length).ToList().Max(),2, dac1.Select(x => x.Cost.Length).Max() + dac1.Select(x => x.Currency.Length).Max(),
// 6, 3 + dac1.Where(x=>!string.IsNullOrWhiteSpace(x.Place)).Select(x=>(Convert.ToDecimal(x.SubTotal) / Convert.ToDecimal(x.Cost)).ToString().Length).Max() };
//遍历人员
for (int i = 0; i < ClientItemList.Count(); i++)
{
Dictionary<string, string> TeableBookmarkArr = new Dictionary<string, string>();
var client = ClientItemList[i]; //每个人员
var firstName = ClientItemList[i].Name;
/*
* 457 头等舱
* 458 公务舱
* 460 经济舱
*/
decimal airPrice = 0.00M;
string airName = string.Empty;
if (client.AirType == 457)
{
airName = $"头等舱";
airPrice = _EnterExitCosts.AirTD;
}
else if (client.AirType == 458)
{
airName = $"公务舱";
airPrice = _EnterExitCosts.AirGW;
}
else if (client.AirType == 460)
{
airName = $"经济舱";
airPrice = _EnterExitCosts.AirJJ;
}
//计算费用总和
decimal AllPrice = airPrice + _EnterExitCosts.CityTranffic + dac1.Sum(x => x.SubTotal) + dac2.Sum(x => x.SubTotal) +
dac3.Sum(x => x.SubTotal) + dac4.Sum(x => x.SubTotal) + _EnterExitCosts.Visa + _EnterExitCosts.Safe +
+_EnterExitCosts.YiMiao + _EnterExitCosts.YiMiao + _EnterExitCosts.Ticket + _EnterExitCosts.Service;
WordAllPrice += AllPrice;
UsersTop += firstName + "出访费用为￥" + AllPrice.ToString("#0.00") + "元、";
TeableBookmarkArr.Add("jp", airPrice.ToString("#0.00") + " 元"); //机票金额
TeableBookmarkArr.Add("cs", _EnterExitCosts.CityTranffic.ToString("#0.00") + " 元"); //城市交通费用
TeableBookmarkArr.Add("zs", dac1.Sum(x => Convert.ToDecimal(x.SubTotal)).ToString("#0.00") + " 元"); //住宿费
string zsinfo = string.Empty;
string hsinfo = string.Empty;
string gzinfo = string.Empty;
List<string> placeArr = new List<string>();
Aspose.Words.Tables.Table FirstTable = (Aspose.Words.Tables.Table)doc.GetChild(NodeType.Table, 0, true);
Aspose.Words.Tables.Table ChildTable = (Aspose.Words.Tables.Table)FirstTable.GetChild(NodeType.Table, 0, true);
Aspose.Words.Tables.Table ChildTable1 = (Aspose.Words.Tables.Table)FirstTable.GetChild(NodeType.Table, 1, true);
Aspose.Words.Tables.Table ChildTable2 = (Aspose.Words.Tables.Table)FirstTable.GetChild(NodeType.Table, 2, true);
int rowIndex = 0;
foreach (var item in dac1)
{
if (string.IsNullOrWhiteSpace(item.Place))
{
continue;
}
if (placeArr.Contains(item.Place))
{
continue;
}
else
{
placeArr.Add(item.Place);
}
if (!string.IsNullOrWhiteSpace(item.Place))
{
if (rowIndex > ChildTable.Rows.Count - 1)
{
var chitableRow = ChildTable.LastRow.Clone(true);
ChildTable.AppendChild(chitableRow);
}
int days = dac1.FindAll(x => x.Place == item.Place).Count();
SetCells(ChildTable, doc, rowIndex, 0, item.Place);
SetCells(ChildTable, doc, rowIndex, 1, days + "晚");
string currencyCode = currData.Find(it => it.Id == item.Currency)?.Remark ?? "Unknown";
SetCells(ChildTable, doc, rowIndex, 2, item.Cost.ToString("#0.00") + currencyCode + "/晚");
var currencyRate = DecimalToString((item.SubTotal / item.Cost), 4);
var sys_currencyInfo = _currencyDatas.Find(x => item.Currency == x.Id);
if (sys_currencyInfo != null)
{
var sys_currencyCode = sys_currencyInfo.Name;
var eec_currencyInfo = _EnterExitCostCurrencys.Find(x => x.Code.Equals(sys_currencyCode));
if (eec_currencyInfo != null)
{
currencyRate = DecimalToString(eec_currencyInfo.Rate, 4);
}
}
SetCells(ChildTable, doc, rowIndex, 3, "汇率" + currencyRate);
SetCells(ChildTable, doc, rowIndex, 4, "CNY " + item.SubTotal + "\r\n");
rowIndex++;
zsinfo += item.Place + " " + days + "晚 " + item.Cost.ToString("#0.00") + currencyCode + "/晚" + " 汇率" + (item.SubTotal / item.Cost).ToString("#0.0000") + " CNY " + item.SubTotal * days + "\r\n";
}
}
placeArr.Clear();
rowIndex = 0;
foreach (var item in dac2)
{
if (string.IsNullOrWhiteSpace(item.Place))
{
continue;
}
if (placeArr.Contains(item.Place))
{
continue;
}
else
{
placeArr.Add(item.Place);
}
if (!string.IsNullOrWhiteSpace(item.Place))
{
if (rowIndex > ChildTable1.Rows.Count - 1)
{
var chitableRow = ChildTable1.LastRow.Clone(true);
ChildTable1.AppendChild(chitableRow);
}
int days = dac2.FindAll(x => x.Place == item.Place).Count();
SetCells(ChildTable1, doc, rowIndex, 0, item.Place);
SetCells(ChildTable1, doc, rowIndex, 1, days + "天");
string currencyCode = currData.Find(it => it.Id == item.Currency)?.Remark ?? "Unknown";
SetCells(ChildTable1, doc, rowIndex, 2, item.Cost.ToString("#0.00") + currencyCode + "/天");
var currencyRate = DecimalToString((item.SubTotal / item.Cost), 4);
var sys_currencyInfo = _currencyDatas.Find(x => item.Currency == x.Id);
if (sys_currencyInfo != null)
{
var sys_currencyCode = sys_currencyInfo.Name;
var eec_currencyInfo = _EnterExitCostCurrencys.Find(x => x.Code.Equals(sys_currencyCode));
if (eec_currencyInfo != null)
{
currencyRate = DecimalToString(eec_currencyInfo.Rate, 4);
}
}
SetCells(ChildTable1, doc, rowIndex, 3, "汇率" + currencyRate);
SetCells(ChildTable1, doc, rowIndex, 4, "CNY " + item.SubTotal);
rowIndex++;
hsinfo += item.Place + " " + days + "天 " + item.Cost.ToString("#0.00") + currencyCode + "/天" + " 汇率" + (item.SubTotal / item.Cost).ToString("#0.0000") + " CNY " + item.SubTotal * days + "\r\n";
}
}
placeArr.Clear();
rowIndex = 0;
foreach (var item in dac3)
{
if (string.IsNullOrWhiteSpace(item.Place))
{
continue;
}
if (placeArr.Contains(item.Place))
{
continue;
}
else
{
placeArr.Add(item.Place);
}
if (!string.IsNullOrWhiteSpace(item.Place))
{
if (rowIndex > ChildTable2.Rows.Count - 1)
{
var chitableRow = ChildTable2.LastRow.Clone(true);
ChildTable2.AppendChild(chitableRow);
}
int days = dac3.FindAll(x => x.Place == item.Place).Count();
SetCells(ChildTable2, doc, rowIndex, 0, item.Place);
SetCells(ChildTable2, doc, rowIndex, 1, days + "天");
string currencyCode = currData.Find(it => it.Id == item.Currency)?.Remark ?? "Unknown";
SetCells(ChildTable2, doc, rowIndex, 2, item.Cost.ToString("#0.00") + currencyCode + "/天");
var currencyRate = DecimalToString((item.SubTotal / item.Cost), 4);
var sys_currencyInfo = _currencyDatas.Find(x => item.Currency == x.Id);
if (sys_currencyInfo != null)
{
var sys_currencyCode = sys_currencyInfo.Name;
var eec_currencyInfo = _EnterExitCostCurrencys.Find(x => x.Code.Equals(sys_currencyCode));
if (eec_currencyInfo != null)
{
currencyRate = DecimalToString(eec_currencyInfo.Rate, 4);
}
}
SetCells(ChildTable2, doc, rowIndex, 3, "汇率" + currencyRate);
SetCells(ChildTable2, doc, rowIndex, 4, "CNY " + item.SubTotal + "\r\n");
rowIndex++;
gzinfo += item.Place + " " + days + "天 " + item.Cost.ToString("#0.00") + currencyCode + "/天" + " 汇率" + (item.SubTotal / item.Cost).ToString("#0.00") + " CNY " + item.SubTotal * days + "\r\n";
}
}
placeArr.Clear();
TeableBookmarkArr.Add("zsinfo", zsinfo); //住宿费详情
TeableBookmarkArr.Add("hs", dac2.Sum(x => Convert.ToDecimal(x.SubTotal)).ToString("#0.00") + " 元"); //伙食费
TeableBookmarkArr.Add("hsinfo", hsinfo); //伙食费详情
TeableBookmarkArr.Add("gz", dac3.Sum(x => Convert.ToDecimal(x.SubTotal)).ToString("#0.00") + " 元"); //公杂费
TeableBookmarkArr.Add("gzinfo", gzinfo); //公杂费详情
string otherFeestr = "";
decimal otherFee = 0.00M;
if (_EnterExitCosts.Visa > 0.00M)
{
otherFee += _EnterExitCosts.Visa;
otherFeestr += $"签证费{_EnterExitCosts.Visa.ToString("#0.00")}元、";
}
if (_EnterExitCosts.YiMiao > 0.00M)
{
otherFee += _EnterExitCosts.YiMiao;
otherFeestr += $"疫苗费{_EnterExitCosts.YiMiao.ToString("#0.00")}元、";
}
if (_EnterExitCosts.HeSuan > 0.00M)
{
otherFee += _EnterExitCosts.HeSuan;
otherFeestr += $"核酸检测费{_EnterExitCosts.HeSuan.ToString("#0.00")}元、";
}
if (_EnterExitCosts.Safe > 0.00M)
{
otherFee += _EnterExitCosts.Safe;
otherFeestr += $"保险费{_EnterExitCosts.Safe.ToString("#0.00")}元、";
}
if (_EnterExitCosts.Ticket > 0.00M)
{
otherFee += _EnterExitCosts.Ticket;
otherFeestr += $"参展门票费{_EnterExitCosts.Ticket.ToString("#0.00")}元、";
}
if (otherFeestr.Length > 0)
{
otherFeestr = otherFeestr.Substring(0, otherFeestr.Length - 1);
otherFeestr += "等费用";
}
TeableBookmarkArr.Add("qt", otherFee.ToString("#0.00") + " 元");//其他费用
TeableBookmarkArr.Add("qtinfo", otherFeestr);//其他费用第二列
TeableBookmarkArr.Add("fw", _EnterExitCosts.Service.ToString("#0.00") + "元/人");//服务费
TeableBookmarkArr.Add("AllPrice", AllPrice.ToString("#0.00") + "元/人");//表格合计费用
TeableBookmarkArr.Add("title", $"费用清单-{airName}（{firstName}）");
foreach (var book in TeableBookmarkArr.Keys)
{
if (doc.Range.Bookmarks[book] != null)
{
Bookmark mark = doc.Range.Bookmarks[book];
mark.Text = TeableBookmarkArr[book];
}
}
if (i != ClientItemList.Count - 1)
{
builder.PageSetup.Orientation = Aspose.Words.Orientation.Portrait;
Aspose.Words.Tables.Table table = (Aspose.Words.Tables.Table)doc.GetChild(NodeType.Table, 0, true);
table.ParentNode.InsertAfter(paragraph, table);
var CloneTable = (Aspose.Words.Tables.Table)table.Clone(true);
table.ParentNode.InsertAfter(CloneTable, paragraph);
}
TeableBookmarkArr.Clear();
}
bookmarkArr.Add("VisitPrice", WordAllPrice.ToString());//出访费用总额
bookmarkArr.Add("CnAllPrice", WordAllPrice.ConvertCNYUpper());//出访费用总额中文
bookmarkArr.Add("namesPrice", UsersTop.TrimEnd('、'));//各人员出访费用 付辰同志出访费用为￥73,604.8元
foreach (var book in bookmarkArr.Keys)
{
if (doc.Range.Bookmarks[book] != null)
{
Bookmark mark = doc.Range.Bookmarks[book];
mark.Text = bookmarkArr[book];
}
}
//MemoryStream outSteam = new MemoryStream();
string filsPath = AppSettingsHelper.Get("WordBasePath") + $"ForeignReceivables/File/{ClientItem.Key.Replace("\n", "")}_{_DelegationInfo.VisitCountry.Replace("|","、")}.docx";
//去水印
new Aspose.Words.License().SetLicense(new MemoryStream(Convert.FromBase64String(AsposeHelper.asposeKey)));
doc.Save(filsPath);
filesToZip.Add(filsPath);
//streams.Add(ClientItem.Key + ".docx", outSteam.ToArray());
}
//文件名
string zipFileName = _DelegationInfo.TeamName + "-收款账单.zip";
string zipPath = $"ForeignReceivables/File/{_DelegationInfo.TeamName}-收款账单{DateTime.Now.ToString("yyyyMMddHHmmss")}.zip";
try
{
using (var zip = ZipFile.Open(AppSettingsHelper.Get("WordBasePath") + zipPath, ZipArchiveMode.Create))
{
foreach (var file in filesToZip)
{
zip.CreateEntryFromFile(file, Path.GetFileName(file));
}
}
}
catch (Exception ex)
{
return Ok(JsonView(false, ex.Message));
}
string url = AppSettingsHelper.Get("WordBaseUrl") + $"Office/Word/{zipPath}";
return Ok(JsonView(true, "成功", new { Url = url }));
}
return Ok(JsonView(false, "操作失败！"));
}
catch (Exception ex)
{
return Ok(JsonView(false, ex.Message));
}
}
/// <summary>
/// decimal保留指定位数小数
/// </summary>
/// <param name="num">原始数量</param>
/// <param name="scale">保留小数位数</param>
/// <returns>截取指定小数位数后的数量字符串</returns>
private static string DecimalToString(decimal num, int scale)
{
string numToString = num.ToString();
int index = numToString.IndexOf(".");
int length = numToString.Length;
if (index != -1)
{
return string.Format("{0}.{1}",
numToString.Substring(0, index),
numToString.Substring(index + 1, Math.Min(length - index - 1, scale)));
}
else
{
return num.ToString();
}
}
/// <summary>
/// 保留小数位数
/// </summary>
/// <param name="n">待处理的值</param>
/// <param name="d">保留位数</param>
/// <param name="isEnter">是否四舍五入</param>
/// <returns></returns>
private static decimal Round(decimal n, int d, bool isEnter = false)
{
if (isEnter)
return decimal.Round(n, d, MidpointRounding.AwayFromZero);
return Math.Truncate(n * (decimal)Math.Pow(10, d)) / (decimal)Math.Pow(10, d);
}
private void SetCells(Aspose.Words.Tables.Table table, Document doc, int rows, int cells, string val)
{
//获取table中的某个单元格，从0开始
Aspose.Words.Tables.Cell lshCell = table.Rows[rows].Cells[cells];
//将单元格中的第一个段落移除
lshCell.FirstParagraph.Remove();
//if (cells == 0) lshCell.CellFormat.Width = 120;
//else if (cells == 1) lshCell.CellFormat.Width = 50;
//else if (cells == 2) lshCell.CellFormat.Width = 120;
//else if (cells == 3) lshCell.CellFormat.Width = 100;
//else if (cells == 4) lshCell.CellFormat.Width = 120;
//新建一个段落
Paragraph p = new Paragraph(doc);
var r = new Run(doc, val);
r.Font.Size = 8;
//把设置的值赋给之前新建的段落
p.AppendChild(r);
//将此段落加到单元格内
lshCell.AppendChild(p);
}
/// <summary>
/// 已收账单
/// 提示导入出入境报价费用
/// </summary>
/// <param name="groupId"></param>
/// <returns></returns>
[HttpPost]
[ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
public async Task<IActionResult> PostReceivablesImportFee(int groupId)
{
if (groupId < 1) return Ok(JsonView(false, "请传入有效的GroupId参数!"));
var data = await GeneralMethod.ReceivablesImportFeeAsync(groupId);
var view = _mapper.Map<List<ProceedsReceivedNewView>>(data);
return Ok(JsonView(true, "操作成功", view));
}
#endregion
#region 已收款项
/// <summary>
/// 已收款项
/// 查询
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
[HttpPost]
[ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
public async Task<IActionResult> PostAmountReceived(AmountReceivedDto dto)
{
try
{
if (dto == null)
{
return Ok(JsonView(false, "参数不能为空!"));
}
Result ffrData = await _proceedsReceivedRep.PostAmountReceived(dto.DiId);
dynamic data = null;
if (dto.PortType == 1)
{
if (ffrData.Code != 0)
{
return Ok(JsonView(false, ffrData.Msg));
}
data = ffrData.Data;
}
else if (dto.PortType == 2)
{
if (ffrData.Code != 0)
{
return Ok(JsonView(false, ffrData.Msg));
}
data = ffrData.Data;
}
else if (dto.PortType == 2)
{
if (ffrData.Code != 0)
{
return Ok(JsonView(false, ffrData.Msg));
}
data = ffrData.Data;
}
else
{
return Ok(JsonView(false, "请选择正确的端口号!"));
}
return Ok(JsonView(true, "操作成功!", data));
}
catch (Exception ex)
{
return Ok(JsonView(false, ex.Message));
}
}
/// <summary>
/// 已收款项
/// Add Or Edit
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
[HttpPost]
[ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
public async Task<IActionResult> PostAmountReceivedAddOrEdit(AmountReceivedAddOrEditDto dto)
{
try
{
if (dto == null)
{
return Ok(JsonView(false, "参数不能为空!"));
}
Result ffrData = await _proceedsReceivedRep.PostAmountReceivedAddOrEditDto(dto);
if (dto.PortType == 1)
{
if (ffrData.Code != 0)
{
return Ok(JsonView(false, ffrData.Msg));
}
}
else if (dto.PortType == 2)
{
if (ffrData.Code != 0)
{
return Ok(JsonView(false, ffrData.Msg));
}
}
else if (dto.PortType == 2)
{
if (ffrData.Code != 0)
{
return Ok(JsonView(false, ffrData.Msg));
}
}
else
{
return Ok(JsonView(false, "请选择正确的端口号!"));
}
return Ok(JsonView(true, "操作成功!"));
}
catch (Exception ex)
{
return Ok(JsonView(false, ex.Message));
}
}
/// <summary>
/// 已收款项
/// Del
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
[HttpPost]
[ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
public async Task<IActionResult> PostAmountReceived_Del(AmountReceivedDelDto dto)
{
try
{
if (dto == null)
{
return Ok(JsonView(false, "参数不能为空!"));
}
Result ffrData = await _proceedsReceivedRep.PostAmountReceivedDel(dto);
if (ffrData.Code != 0)
{
return Ok(JsonView(false, ffrData.Msg));
}
return Ok(JsonView(true, "操作成功!"));
}
catch (Exception ex)
{
return Ok(JsonView(false, ex.Message));
}
}
#endregion
#region 收款退还与其他款项 --> 收款退还
/// <summary>
/// 收款退还与其他款项
/// 查询 根据团组Id
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
[HttpPost]
[ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
public async Task<IActionResult> PostPaymentRefundAndOtherMoneyItemByDiId(PaymentRefundAndOtherMoneyItemByDiIdDto dto)
{
if (dto == null) return Ok(JsonView(false, "参数不能为空!"));
if (dto.PageId <= 0) return Ok(JsonView(false, "请传入正确的的页面Id!"));
if (dto.UserId <= 0) return Ok(JsonView(false, "请传入正确的的员工Id!"));
#region 页面功能权限处理
PageFunAuthViewBase pageFunAuth = new PageFunAuthViewBase();
pageFunAuth = await GeneralMethod.PostUserPageFuncDatas(dto.UserId, dto.PageId);
if (pageFunAuth.CheckAuth == 0)
{
return Ok(JsonView(false, "您没有当前页面查询权限!"));
}
#endregion
Result _result = await _paymentRefundAndOtherMoneyRep._ItemByDiId(dto.DiId);
if (dto.PortType == 1 || dto.PortType == 2 || dto.PortType == 3) //1 Web 2 Android 3 Ios
{
if (_result.Code != 0)
{
return Ok(JsonView(false, _result.Msg));
}
var data = new
{
PageFuncAuth = pageFunAuth,
Data = _result.Data
};
return Ok(JsonView(true, "操作成功!", data));
}
else
{
return Ok(JsonView(false, "请输入正确的端口号! 1 Web 2 Android 3 Ios;"));
}
}
/// <summary>
/// 收款退还与其他款项
/// 删除
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
[HttpPost]
[ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
public async Task<IActionResult> PostPaymentRefundAndOtherMoneyDel(PaymentRefundAndOtherMoneyDelDto dto)
{
if (dto == null) return Ok(JsonView(false, "参数不能为空!"));
if (dto.PageId <= 0) return Ok(JsonView(false, "请传入正确的的页面Id!"));
if (dto.UserId <= 0) return Ok(JsonView(false, "请传入正确的的员工Id!"));
PageFunAuthViewBase pageFunAuth = new PageFunAuthViewBase();
#region 页面功能权限处理
pageFunAuth = await GeneralMethod.PostUserPageFuncDatas(dto.UserId, dto.PageId);
#endregion
if (pageFunAuth.DeleteAuth == 0)
{
return Ok(JsonView(false, "您没有当前页面删除权限!"));
}
Result _result = await _paymentRefundAndOtherMoneyRep._Del(dto);
if (_result.Code != 0)
{
return Ok(JsonView(false, _result.Msg));
}
return Ok(JsonView(true, "操作成功!"));
}
/// <summary>
/// 收款退还与其他款项
/// Info Data Source
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
[HttpPost]
[ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
public async Task<IActionResult> PostPaymentRefundAndOtherMoneyInfoDataSource(PortDtoBase dto)
{
Result _result = await _paymentRefundAndOtherMoneyRep._InfoDataSource(dto);
if (_result.Code != 0)
{
return Ok(JsonView(false, _result.Msg));
}
return Ok(JsonView(true, "查询成功!", _result.Data));
}
/// <summary>
/// 收款退还与其他款项
/// Info
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
[HttpPost]
[ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
public async Task<IActionResult> PostPaymentRefundAndOtherMoneyInfo(PaymentRefundAndOtherMoneyInfoDto dto)
{
if (dto == null) return Ok(JsonView(false, "参数不能为空!"));
var view = await _paymentRefundAndOtherMoneyRep._Info(dto);
return Ok(view);
}
/// <summary>
/// 收款退还与其他款项 --> 收款退还(只保留人名币)
/// 操作(Add Or Edit)
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
[HttpPost]
[ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
public async Task<IActionResult> PostPaymentRefundAndOtherMoneyAddOrEdit(PaymentRefundAndOtherMoneyAddOrEditDto dto)
{
if (dto == null) return Ok(JsonView(false, "参数不能为空!"));
if (dto.PageId <= 0) return Ok(JsonView(false, "请传入正确的的页面Id!"));
if (dto.UserId <= 0) return Ok(JsonView(false, "请传入正确的的员工Id!"));
#region 页面功能权限处理
PageFunAuthViewBase pageFunAuth = new PageFunAuthViewBase();
pageFunAuth = await GeneralMethod.PostUserPageFuncDatas(dto.UserId, dto.PageId);
#endregion
if (dto.Status == 1) //add
{
if (pageFunAuth.AddAuth == 0) return Ok(JsonView(false, "您没有当前页面添加权限!"));
}
else if (dto.Status == 2) //edit
{
if (pageFunAuth.EditAuth == 0) return Ok(JsonView(false, "您没有当前页面编辑权限!"));
}
else return Ok(JsonView(false, "请输入正确的操作状态! 1 添加 2 修改!"));
return Ok(await _paymentRefundAndOtherMoneyRep._AddOrEdit(dto));
}
#endregion
#region 应收报表
/// <summary>
/// 应收报表
/// 查询 根据日期范围
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
[HttpPost]
[ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
public async Task<IActionResult> PostSyntheticalReceivableByDateRange(PostSyntheticalReceivableByDateRangeDto dto)
{
string sqlWhere = " Where di.IsDel=0 ";
#region 验证
if (string.IsNullOrEmpty(dto.beginDt) && string.IsNullOrEmpty(dto.endDt))
{
return Ok(JsonView(false, "日期参数至少填写一个!"));
}
if (!string.IsNullOrEmpty(dto.beginDt))
{
if (Regex.Match(dto.beginDt, @"^\d{4}-\d{2}-\d{2}$").Value.Length < 1)
{
return Ok(JsonView(false, "日期参数格式错误，应为yyyy-MM-dd!"));
}
else
{
sqlWhere += string.Format(@" And di.VisitDate >= '{0} 00:00:00' ", dto.beginDt);
}
}
if (!string.IsNullOrEmpty(dto.endDt))
{
if (Regex.Match(dto.endDt, @"^\d{4}-\d{2}-\d{2}$").Value.Length < 1)
{
return Ok(JsonView(false, "日期参数格式错误，应为yyyy-MM-dd!"));
}
else
{
sqlWhere += string.Format(@" And di.VisitDate <= '{0} 23:59:59' ", dto.endDt);
}
}
if (!string.IsNullOrEmpty(dto.groupName))
{
sqlWhere += string.Format(@" And di.TeamName Like '%{0}%' ", dto.groupName);
}
#endregion
//已收款项 判断如果是市场部的人员进来的话 只显示自己的 其他的都显示全部的
string userSqlWhere = "";
var userInfos = await _sqlSugar.Queryable<Sys_Users>()
.InnerJoin<Sys_Department>((u, d) => u.DepId == d.Id)
.Where((u, d) => u.IsDel == 0 && d.DepName.Contains("市场部") && u.Id == dto.CurrUserId)
.ToListAsync();
if (userInfos.Count > 0) userSqlWhere = string.Format(@$" And JietuanOperator={dto.CurrUserId} ");
//排序倒序
string sql = string.Format(@$"select distinct fr.diid,di.TeamName,di.ClientUnit,di.VisitDate,di.CreateTime from Fin_ForeignReceivables fr join Grp_DelegationInfo di on fr.DIID = di.id {sqlWhere} {userSqlWhere} Order By di.VisitDate Desc");
var list_rst = _sqlSugar.SqlQueryable<PostSyntheticalReceivableByDateRangeView>(sql).ToList();
var setData = _sqlSugar.Queryable<Sys_SetData>().Where(it => it.IsDel == 0).ToList();
decimal sumAll_fr = 0M; //应收
decimal sumAll_pr = 0M; //已收
decimal sumAll_balance = 0M; //尾款
if (list_rst.Count > 0)
{
int rowNumber = 1;
foreach (var item_rst in list_rst)
{
DateTime dtTemp;
bool b = DateTime.TryParse(item_rst.visitDate, out dtTemp);
if (b)
{
item_rst.visitDate = dtTemp.ToString("yyyy-MM-dd");
}
item_rst.No = rowNumber;
rowNumber++;
int diId = item_rst.diid;
decimal sum_fr = 0M;
decimal sum_pr = 0M;
string str_client = string.Empty;
decimal sum_refund = 0M; //收款退还
decimal sum_extra = 0M; //超支费用
decimal balance = 0M;
string str_schedule = string.Empty;
//1.应收
string sql_fr = string.Format(@" Select * From Fin_ForeignReceivables Where IsDel=0 And Diid={0} ", diId);
List<Fin_ForeignReceivables> list_fr = _sqlSugar.SqlQueryable<Fin_ForeignReceivables>(sql_fr).ToList();
sum_fr = list_fr.Sum(s => s.ItemSumPrice);
//2.已收
string sql_pr = string.Format(@" Select * From Fin_ProceedsReceived Where IsDel=0 And Diid={0} ", diId);
List<Fin_ProceedsReceived> list_pr = _sqlSugar.SqlQueryable<Fin_ProceedsReceived>(sql_pr).ToList();
foreach (var item_pr in list_pr)
{
sum_pr += item_pr.Price;
str_client += string.Format(@"{0};", item_pr.Client);
str_schedule += string.Format(@"{0};", item_pr.Remark);
}
if (str_schedule.Length > 0)
{
str_schedule = str_schedule.TrimEnd(';');
}
if (str_client.Length > 0)
{
str_client = str_client.TrimEnd(';');
}
//3.收款退还
string sql_other = string.Format(@"Select * From Fin_PaymentRefundAndOtherMoney prao
Inner Join Grp_CreditCardPayment ccp On prao.Id = ccp.CId
Where ccp.CTable = 285 And ccp.IsPay = 1 And prao.IsDel = 0 And ccp.IsDel = 0 And prao.DiId = {0}", diId);
List<Grp_CreditCardPayment> list_other = _sqlSugar.SqlQueryable<Grp_CreditCardPayment>(sql_other).ToList();
sum_refund = list_other.Sum(s => s.PayMoney * s.DayRate);
//4.超支
//string sql_extra = string.Format(@" Select c.* From Fin_GroupExtraCost f
//Inner join Grp_CreditCardPayment c On f.Id = c.CId
//Where c.CTable = 1015 And c.IsPay = 1 And f.IsDel = 0 And c.IsDel = 0 And f.DiId = {0} ", diId);
//List<Grp_CreditCardPayment> list_extra = _sqlSugar.SqlQueryable<Grp_CreditCardPayment>(sql_extra).ToList();
//sum_extra = list_extra.Sum(s => s.PayMoney * s.DayRate);
item_rst.frPrice = sum_fr.ToString("#0.00");
item_rst.extraPrice = sum_extra.ToString("#0.00");
item_rst.receivableTotal = (sum_fr + sum_extra).ToString("#0.00");
item_rst.prPrice = sum_pr.ToString("#0.00");
item_rst.refundAmount = sum_refund.ToString("#0.00");
item_rst.receivedTotal = (sum_pr - sum_refund).ToString("#0.00");
item_rst.balPrice = ((sum_fr + sum_extra) - (sum_pr - sum_refund)).ToString("#0.00");
item_rst.prClient = str_client;
item_rst.schedule = str_schedule;
string tempVisitDate = Convert.ToDateTime(item_rst.visitDate).ToString("yyyy-MM-dd");
sumAll_fr += (sum_fr + sum_extra);
sumAll_pr += (sum_pr - sum_refund);
sumAll_balance += ((sum_fr + sum_extra) - (sum_pr - sum_refund));
#region 单位应收已收细项（以应收费用名称为主去(已收费用)匹配） 新增 雷怡 2024-5-08 16:35:28
List<ClientFeeInfoView> feeDatas = new List<ClientFeeInfoView>();
//匹配上的数据
foreach (var item in list_fr)
{
var prInfo = list_pr.Find(it => item.PriceName.Contains(it.Client) || item.PriceName.Equals(it.Client));
decimal _balancePayment = 0.00M;
if (item.Currency == prInfo?.Currency)
{
_balancePayment = item.ItemSumPrice - prInfo?.Price ?? 0.00M;
}
feeDatas.Add(new ClientFeeInfoView
{
client = item.PriceName,
frMoney = item.ItemSumPrice.ToString("#0.00"),
frCurrency = setData.Find(it => it.Id == item.Currency)?.Name ?? "-",
frRate = item.Rate.ToString("#0.0000"),
prReceivablesType = setData.Find(it => it.Id == prInfo?.ReceivablesType)?.Name ?? "",
prTime = prInfo?.SectionTime ?? "-",
prMoney = prInfo?.Price.ToString("#0.00") ?? "-",
prCurrency = setData.Find(it => it.Id == prInfo?.Currency)?.Name ?? "",
balPayment = _balancePayment.ToString("#0.00")
});
}
//未匹配上的数据
foreach (var item in list_pr)
{
var frInfo = list_fr.Find(it => it.PriceName.Contains(item.Client) || it.PriceName.Equals(item.Client));
if (frInfo == null)
{
feeDatas.Add(new ClientFeeInfoView
{
client = item.Client + "[未匹配上的已收数据(应收已收公司名称不一致)]",
frMoney = "0.00",
frCurrency = "-",
frRate = "0.0000",
prReceivablesType = setData.Find(it => it.Id == item?.ReceivablesType)?.Name ?? "",
prTime = item?.SectionTime ?? "-",
prMoney = item?.Price.ToString("#0.00") ?? "0.00",
prCurrency = setData.Find(it => it.Id == item?.Currency)?.Name ?? "",
balPayment = "0.00"
});
}
}
item_rst.feeItem = feeDatas;
#endregion
}
PostSyntheticalReceivableByDateRangeResultView result = new PostSyntheticalReceivableByDateRangeResultView();
result.total_fr = sumAll_fr.ToString("#0.00");
result.total_pr = sumAll_pr.ToString("#0.00");
result.total_balance = sumAll_balance.ToString("#0.00");
result.dataList = new List<PostSyntheticalReceivableByDateRangeView>(list_rst);
if (dto.requestType == 1)
{
return Ok(JsonView(true, "请求成功", result, list_rst.Count));
}
else
{
//----------------------------
List<Excel_SyntheticalReceivableByDateRange> list_Ex = new List<Excel_SyntheticalReceivableByDateRange>();
WorkbookDesigner designer = new WorkbookDesigner();
designer.Workbook = new Workbook(AppSettingsHelper.Get("ExcelBasePath") + "Template/应收款项模板 - 副本.xls");
int excNo = 1;
foreach (var item in list_rst)
{
Excel_SyntheticalReceivableByDateRange exc = new Excel_SyntheticalReceivableByDateRange();
exc.No = excNo.ToString();
excNo++;
exc.TeamName = item.teamName;
exc.ClientUnit = item.clientUnit;
exc.VisitDate = item.visitDate;
exc.Accounts = item.frPrice;
exc.Extra = item.extraPrice;
exc.ReceivableTotal = item.receivableTotal;
exc.Received = item.prPrice;
exc.RefundAmount = item.refundAmount;
exc.ReceivedTotal = item.receivedTotal;
exc.Balance = item.balPrice;
exc.Collection = item.schedule;
DateTime time = Convert.ToDateTime(item.visitDate);
TimeSpan ts = DateTime.Now - time;
float SY = float.Parse(item.balPrice);
if (ts.Days >= 365 && SY > 0)
{
exc.Sign = "需收款";
}
else
{
exc.Sign = "";
}
list_Ex.Add(exc);
}
var dt = CommonFun.GetDataTableFromIList(list_Ex);
dt.TableName = "Excel_SyntheticalReceivableByDateRange";
if (dt != null)
{
designer.SetDataSource("SumPrice", "应收合计：" + result.total_fr + "RMB 已收合计：" + result.total_pr + "RMB 余款合计：" + result.total_balance + "RMB");
//数据源
designer.SetDataSource(dt);
//根据数据源处理生成报表内容
designer.Process();
string fileName = ("Receivable/应收款项(" + dto.beginDt + "～" + dto.endDt + ").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, "获取成功", "", list_rst.Count));
}
#endregion
#region 付款申请
/// <summary>
/// 付款申请
/// 基础数据
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
[HttpPost]
[ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
public async Task<IActionResult> PostPayRequestInit()
{
try
{
var conpanyDatas = _sqlSugar.Queryable<Sys_Company>()
.Where(it => it.IsDel == 0)
.Select(it => new
{
Id = it.Id,
ConpamyName = it.CompanyName
}).ToList();
return Ok(JsonView(true, "操作成功!", new { ConpanyData = conpanyDatas }));
}
catch (Exception ex)
{
return Ok(JsonView(false, ex.Message));
}
}
/// <summary>
/// 付款申请 (PageId=51)
/// 查询 根据日期范围
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
[HttpPost]
[ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
public async Task<IActionResult> PostPayRequest_Center(PostPayRequestByDateRangeDto dto)
{
Stopwatch stopwatch = Stopwatch.StartNew();
#region 验证
DateTime beginDt, endDt;
string format = "yyyy-MM-dd";
if (!DateTime.TryParseExact(dto.beginDt, format, CultureInfo.InvariantCulture, DateTimeStyles.None, out beginDt))
{
return Ok(JsonView(false, "开始日期格式不正确！正确格式：yyyy-MM-dd"));
}
if (!DateTime.TryParseExact(dto.endDt, format, CultureInfo.InvariantCulture, DateTimeStyles.None, out endDt))
{
return Ok(JsonView(false, "结束日期格式不正确！正确格式：yyyy-MM-dd"));
}
#region 页面操作权限验证
PageFunAuthViewBase pageFunAuthView = new PageFunAuthViewBase();
pageFunAuthView = await GeneralMethod.PostUserPageFuncDatas(dto.UserId, dto.PageId);
if (pageFunAuthView.CheckAuth == 0) return Ok(JsonView(false, "您没有查看权限!"));
#endregion
#endregion
try
{
PaymentRequestCheckedView checkedView = new PaymentRequestCheckedView();
var checkedStr = await RedisRepository.RedisFactory.CreateRedisRepository().StringGetAsync<string>("paymentRequestCheckedData");
if (checkedStr != null)
{
checkedView = JsonConvert.DeserializeObject<PaymentRequestCheckedView>(checkedStr.ToString());
if (checkedView.GroupIds.Count > 0)
{
checkedView.GroupIds = checkedView.GroupIds.OrderBy(x => x).ToList();
}
}
tree_Fin_DailyFeePaymentResult dailyResult = PayRequest_DailyByDateRange(dto.Status, checkedView.DailyPaymentIds, dto.beginDt, dto.endDt);
tree_Group_DailyFeePaymentResult groupResult = PayRequest_GroupPaymentByDateRange(dto.Status, checkedView.GroupIds, checkedView.HotelSubIds, dto.beginDt, dto.endDt);
stopwatch.Stop();
return Ok(JsonView(true, $"查询成功！耗时{stopwatch.ElapsedMilliseconds / 1000}s", new { daily = dailyResult, group = groupResult }));
}
catch (Exception ex)
{
return Ok(JsonView(false, ex.Message));
}
}
/// <summary>
/// 根据团组类型类型处理团组费用所属公司
/// </summary>
/// <param name="teamId"></param>
/// <returns></returns>
private CompanyInfo ExpenseCompanyByTeamId(int teamId)
{
CompanyInfo _companyInfo = new CompanyInfo();
List<int> _SiChuan = new List<int>() {
38 , // 政府团
39 , // 企业团
40 , // 散客团
102, // 未知
248, // 非团组
691, // 四川-会务活动
762, // 四川-赛事项目收入
1048,//高校团
};
List<int> _ChengDu = new List<int>() {
302, // 成都-会务活动
1047, // 成都-赛事项目收入
};
if (_SiChuan.Contains(teamId))
{
_companyInfo.Id = 2;
_companyInfo.ConpanyName = "四川泛美交流有限公司";
}
if (_ChengDu.Contains(teamId))
{
_companyInfo.Id = 1;
_companyInfo.ConpanyName = "成都泛美商务有限公司";
}
return _companyInfo;
}
/// <summary>
/// 付款申请(团组费用申请相关)
/// 查询 根据日期范围
/// </summary>
/// <param name="_groupIds"></param>
/// <param name="beginDt"></param>
/// <param name="endDt"></param>
/// <returns></returns>
private tree_Group_DailyFeePaymentResult PayRequest_GroupPaymentByDateRange(int status, List<int> _groupIds, List<int> _hotelSubIds, string beginDt, string endDt)
{
tree_Group_DailyFeePaymentResult _DailyFeePaymentResult = new tree_Group_DailyFeePaymentResult();
List<tree_Group_DailyFeePaymentPageListView> dataList = new List<tree_Group_DailyFeePaymentPageListView>();
#region sql条件处理
string sqlWhere = string.Format(@" And (AuditGMDate Between '{0} 00:00:00' And '{1} 23:59:59') ", beginDt, endDt);
if (status == 2)
{
if (_hotelSubIds.Count > 1)
{
var hrIds = _sqlSugar.Queryable<Grp_HotelReservationsContent>().Where(it => _hotelSubIds.Contains(it.Id) && it.Price != 0).Select(it => it.HrId).Distinct().ToList();
var hrIds1 = _sqlSugar.Queryable<Grp_CreditCardPayment>().Where(it => it.CTable == 76 && hrIds.Contains(it.CId)).Select(it => it.Id).ToList();
if (hrIds1.Count > 1)
{
_groupIds.AddRange(hrIds1);
}
}
if (_groupIds.Count < 1)
{
_DailyFeePaymentResult.dataList = new List<tree_Group_DailyFeePaymentPageListView>();
return _DailyFeePaymentResult;
}
sqlWhere += string.Format(@" And Id In ({0})", string.Join(",", _groupIds));
}
string sql_1 = string.Format(@"Select * From Grp_CreditCardPayment Where IsDel = 0 And IsPay = 0 And (IsAuditGM = 1 Or IsAuditGM = 3 ){0}", sqlWhere);
#endregion
var _paymentDatas = _sqlSugar.SqlQueryable<Grp_CreditCardPayment>(sql_1).ToList();//付款信息
_DailyFeePaymentResult.gz = _paymentDatas.Where(it => it.OrbitalPrivateTransfer == 0).Sum(it => ((it.PayMoney * it.DayRate) / 100) * it.PayPercentage); //公转
_DailyFeePaymentResult.sz = _paymentDatas.Where(it => it.OrbitalPrivateTransfer == 1).Sum(it => ((it.PayMoney * it.DayRate) / 100) * it.PayPercentage); ; //私转
List<int> groupIds = _paymentDatas.Select(it => it.DIId).Distinct().ToList();
var _groupDatas = _sqlSugar.Queryable<Grp_DelegationInfo>().Where(it => it.IsDel == 0 && groupIds.Contains(it.Id)).ToList();
//_groupDatas = (List<Grp_DelegationInfo>)_groupDatas.GroupBy(it => it.TeamDid);
#region 相关基础数据源
var userDatas = _sqlSugar.Queryable<Sys_Users>().ToList();
var setDatas = _sqlSugar.Queryable<Sys_SetData>().Where(it => it.IsDel == 0).ToList();
var countryFeeDatas = _sqlSugar.Queryable<Grp_NationalTravelFee>().Where(it => it.IsDel == 0).ToList();
var hotelDatas = _sqlSugar.Queryable<Grp_HotelReservations>().Where(it => it.IsDel == 0 && groupIds.Contains(it.DiId)).ToList();
var hotelContentDatas = _sqlSugar.Queryable<Grp_HotelReservationsContent>().Where(it => it.IsDel == 0 && it.IsPay == 0 && it.Price != 0 && groupIds.Contains(it.DiId)).ToList();
var opDatas = _sqlSugar.Queryable<Grp_CarTouristGuideGroundReservations>().Where(it => it.IsDel == 0 && groupIds.Contains(it.DiId)).ToList();
var visaDatas = _sqlSugar.Queryable<Grp_VisaInfo>().Where(it => it.IsDel == 0 && groupIds.Contains(it.DIId)).ToList();
var ioaDatas = _sqlSugar.Queryable<Grp_InvitationOfficialActivities>().Where(it => it.IsDel == 0 && groupIds.Contains(it.DiId)).ToList();
var insureDatas = _sqlSugar.Queryable<Grp_Customers>().Where(it => it.IsDel == 0 && groupIds.Contains(it.DiId)).ToList();
var airDatas = _sqlSugar.Queryable<Grp_AirTicketReservations>().Where(it => it.IsDel == 0 && groupIds.Contains(it.DIId)).ToList();
//var otherMoneyDatas = _sqlSugar.Queryable<Fin_OtherPrice>().Where(it => it.IsDel == 0 && groupIds.Contains(it.Diid)).ToList();
var otherMoneyDatas = _sqlSugar.Queryable<Grp_DecreasePayments>().Where(it => it.IsDel == 0 && groupIds.Contains(it.DiId)).ToList();
var refundPaymentDatas = _sqlSugar.Queryable<Fin_PaymentRefundAndOtherMoney>().Where(it => it.IsDel == 0 && groupIds.Contains(it.DiId)).ToList();
var ExtraCostDatas = _sqlSugar.Queryable<Fin_GroupExtraCost>().Where(it => it.IsDel == 0 && groupIds.Contains(it.DiId)).ToList();
#endregion
//Expense company
foreach (var groupInfo in _groupDatas)
{
List<Group_DailyFeePaymentContentInfolView> childList = new List<Group_DailyFeePaymentContentInfolView>();
var groupPaymentDatas = _paymentDatas.Where(it => groupInfo.Id == it.DIId).ToList();
int rouNumber = 1;
foreach (var payInfo in groupPaymentDatas)
{
string priName = "-";
string orbitalPrivateTransfer = payInfo.OrbitalPrivateTransfer == 0 ? "公转" : payInfo.OrbitalPrivateTransfer == 1 ? "私转" : "-";
switch (payInfo.CTable)
{
case 76: //76 酒店预订
priName = $"[费用名称：{hotelDatas.Find(it => payInfo.DIId == it.DiId && payInfo.CId == it.Id)?.HotelName ?? ""}]";
break;
case 79: //79 车/导游地接
var opData = opDatas.Find(it => payInfo.DIId == it.DiId && payInfo.CId == it.Id);
if (opData != null)
{
string area = "";
bool b = int.TryParse(opData.Area, out int areaId);
if (b)
{
string area1 = countryFeeDatas.Find(it => it.Id == areaId)?.Country ?? "-";
area = $"{area1}({setDatas.Find(it => it.Id == opData.PriceType)?.Name ?? "-"})";
}
else area = opData.Area;
string opPriName = "-";
if (!string.IsNullOrEmpty(opData.PriceName)) opPriName = opData.PriceName;
area += $"({opPriName})";
if (payInfo.OrbitalPrivateTransfer == 0) //公转
{
priName = $"【{orbitalPrivateTransfer}】【导游： {opData.ServiceGuide} 】[费用名称：{area}]";
}
else if (payInfo.OrbitalPrivateTransfer == 1) //私转
{
priName = $"【{orbitalPrivateTransfer}】【导游：{opData.ServiceGuide}】[费用名称：{area}]";
}
}
break;
case 80: // 80 签证
string sql = string.Format("select b.Id,b.Pinyin,b.lastName,b.firstName,b.phone from Grp_TourClientList a, Crm_DeleClient b where a.clientid = b.id and a.isdel = 0 and a.diid = {0}", groupInfo.Id);
List<SimplClientInfo> arr = _sqlSugar.SqlQueryable<SimplClientInfo>(sql).ToList();
string visaClientName = visaDatas.Find(it => payInfo.DIId == it.DIId && payInfo.CId == it.Id)?.VisaClient ?? "";
string clientName = "-";
if (Regex.Match(visaClientName, @"\d+,?").Value.Length > 0)
{
string[] temparr = visaClientName.Split(',');
string fistrStr = temparr[0];
int count = temparr.Count();
int tempId;
bool success = int.TryParse(fistrStr, out tempId);
if (success)
{
SimplClientInfo tempInfo = arr.FirstOrDefault(s => s.Id == tempId);
if (tempInfo != null)
{
if (count > 1)
{
clientName = string.Format(@"{0}{1}等{2}人", tempInfo.LastName, tempInfo.FirstName, count);
}
else
{
clientName = string.Format(@"{0}{1}", tempInfo.LastName, tempInfo.FirstName);
}
}
}
else
{
clientName = fistrStr;
}
}
priName = $"[费用名称：{clientName}]";
break;
case 81: // 81 邀请/公务活动
priName = $"[费用名称：{ioaDatas.Find(it => payInfo.DIId == it.DiId && payInfo.CId == it.Id)?.Inviter ?? " -"}]";
break;
case 82: // 82 团组客户保险
string bx_sql = string.Format("select b.Id,b.Pinyin,b.lastName,b.firstName,b.phone from Grp_TourClientList a, Crm_DeleClient b where a.clientid = b.id and a.isdel = 0 and a.diid = {0}", groupInfo.Id);
List<SimplClientInfo> bx_arr = _sqlSugar.SqlQueryable<SimplClientInfo>(bx_sql).ToList();
string bx_ClientName = insureDatas.Find(it => payInfo.DIId == it.DiId && payInfo.CId == it.Id)?.ClientName ?? "";
string bx_clientName = "-";
if (Regex.Match(bx_ClientName, @"\d+,?").Value.Length > 0)
{
string[] temparr = bx_ClientName.Split(',');
string fistrStr = temparr[0];
int count = temparr.Count();
int tempId;
bool success = int.TryParse(fistrStr, out tempId);
if (success)
{
SimplClientInfo tempInfo = bx_arr.FirstOrDefault(s => s.Id == tempId);
if (tempInfo != null)
{
if (count > 1)
{
bx_clientName = string.Format(@"{0}{1}等{2}人", tempInfo.LastName, tempInfo.FirstName, count);
}
else
{
bx_clientName = string.Format(@"{0}{1}", tempInfo.LastName, tempInfo.FirstName);
}
}
}
else
{
bx_clientName = fistrStr;
}
}
//priName = $"[费用名称：{insureDatas.Find(it => payInfo.DIId == it.DiId && payInfo.CId == it.Id)?.ClientName ?? " -"}]";
priName = $"[费用名称：{bx_clientName}]";
break;
case 85: // 85 机票预订
string flightsCode = airDatas.Find(it => payInfo.DIId == it.DIId && payInfo.CId == it.Id)?.FlightsCode ?? "-";
string airPayType = setDatas.Find(it => it.Id == payInfo.PayDId)?.Name ?? "-";
priName = $"{flightsCode}【{airPayType}】";
break;
case 98: // 98 其他款项
priName = $"[费用名称：{otherMoneyDatas.Find(it => payInfo.DIId == it.DiId && payInfo.CId == it.Id)?.PriceName ?? " -"}]";
break;
case 285: // 285 收款退还
priName = $"[费用名称：{refundPaymentDatas.Find(it => payInfo.DIId == it.DiId && payInfo.CId == it.Id)?.PriceName ?? " -"}]";
break;
case 1015: // 1015 超支费用
priName = $"[费用名称：{ExtraCostDatas.Find(it => payInfo.DIId == it.DiId && payInfo.CId == it.Id)?.PriceName ?? " -"}]";
break;
default:
priName = "";
break;
}
bool status1 = false;
if (_groupIds != null)
{
status1 = _groupIds.Contains(payInfo.Id);
}
if (payInfo.CTable == 76) //酒店单独处理
{
var hotelContents = hotelContentDatas.Where(it => it.HrId == payInfo.CId);
List<Group_DailyFeePaymentContentInfolView> childInfos = new List<Group_DailyFeePaymentContentInfolView>();
foreach (var hotelContent in hotelContents)
{
string subPriceName = "";
if (hotelContent.PriceType == 1) subPriceName = "房费";
else if (hotelContent.PriceType == 2) subPriceName = "早餐";
else if (hotelContent.PriceType == 3) subPriceName = "地税";
else if (hotelContent.PriceType == 4) subPriceName = "城市税";
if (string.IsNullOrEmpty(subPriceName)) subPriceName = priName;
else subPriceName = $"{priName.Replace("]", "")}-{subPriceName}]";
string payeeStr1 = string.Format(@" {0},开户行：{1},银行卡号：{2} ",
string.IsNullOrEmpty(hotelContent.Payee) ? "-" : hotelContent.Payee,
string.IsNullOrEmpty(hotelContent.OtherBankName) ? "-" : hotelContent.OtherBankName,
string.IsNullOrEmpty(hotelContent.OtherSideNo) ? "-" : hotelContent.OtherSideNo);
decimal _PaymentAmount1 = hotelContent.Price;//此次付款金额
decimal _CNYSubTotalAmount1 = _PaymentAmount1 * hotelContent.Rate;//此次付款金额
_CNYSubTotalAmount1 = Convert.ToDecimal(_CNYSubTotalAmount1.ToString("#0.00"));
//酒店子项Id选中状态更改
if (_hotelSubIds != null) status1 = _hotelSubIds.Contains(hotelContent.Id);
var childInfo1 = new Group_DailyFeePaymentContentInfolView()
{
IsChecked = status1,
Id = payInfo.Id,
HotelSubId = hotelContent.Id,
Payee = payeeStr1,
RowNumber = rouNumber,
Applicant = userDatas.Find(it => it.Id == payInfo.CreateUserId)?.CnName ?? "",
ApplicantDt = payInfo.CreateTime.ToString("yyyy-MM-dd HH:mm:ss"),
PayType = setDatas.Find(it => it.Id == payInfo.PayDId)?.Name ?? "",
TransferMark = orbitalPrivateTransfer,
PriceName = subPriceName,
ModuleName = setDatas.Find(it => it.Id == payInfo.CTable)?.Name ?? "",
PayCurrCode = setDatas.Find(it => it.Id == hotelContent.Currency)?.Name ?? "",
PaymentAmount = _PaymentAmount1,
PayRate = hotelContent.Rate,
CNYSubTotalAmount = _CNYSubTotalAmount1,
AuditStatus = payInfo.IsAuditGM
};
string remaksDescription1 = $"【{childInfo1.PayType}】【{childInfo1.ModuleName}】{rouNumber}、[申请人：{childInfo1.Applicant}]{subPriceName}[收款方：{childInfo1.Payee}] {childInfo1.PayCurrCode} {_PaymentAmount1.ToString("#0.00")}、CNY：{childInfo1.CNYSubTotalAmount.ToString("#0.00")}(团组：{groupInfo.TeamName})";
childInfo1.RemaksDescription = remaksDescription1;
if (status == 2)
{
if (status1)
{
childInfos.Add(childInfo1);
}
}
else childInfos.Add(childInfo1);
rouNumber++;
}
childList.AddRange(childInfos);
}
else
{
string payeeStr = string.Format(@" {0},开户行：{1},银行卡号：{2} ",
string.IsNullOrEmpty(payInfo.Payee) ? "-" : payInfo.Payee,
string.IsNullOrEmpty(payInfo.OtherBankName) ? "-" : payInfo.OtherBankName,
string.IsNullOrEmpty(payInfo.OtherSideNo) ? "-" : payInfo.OtherSideNo);
decimal _PaymentAmount = (payInfo.PayMoney / 100) * payInfo.PayPercentage;//此次付款金额
decimal _CNYSubTotalAmount = _PaymentAmount * payInfo.DayRate;//此次付款金额
_CNYSubTotalAmount = Convert.ToDecimal(_CNYSubTotalAmount.ToString("#0.00"));
var childInfo = new Group_DailyFeePaymentContentInfolView()
{
IsChecked = status1,
Id = payInfo.Id,
Payee = payeeStr,
RowNumber = rouNumber,
Applicant = userDatas.Find(it => it.Id == payInfo.CreateUserId)?.CnName ?? "",
ApplicantDt = payInfo.CreateTime.ToString("yyyy-MM-dd HH:mm:ss"),
PayType = setDatas.Find(it => it.Id == payInfo.PayDId)?.Name ?? "",
TransferMark = orbitalPrivateTransfer,
PriceName = priName,
ModuleName = setDatas.Find(it => it.Id == payInfo.CTable)?.Name ?? "",
PayCurrCode = setDatas.Find(it => it.Id == payInfo.PaymentCurrency)?.Name ?? "",
PaymentAmount = _PaymentAmount,
PayRate = payInfo.DayRate,
CNYSubTotalAmount = _CNYSubTotalAmount,
AuditStatus = payInfo.IsAuditGM
};
string remaksDescription = $"【{childInfo.PayType}】【{childInfo.ModuleName}】{rouNumber}、[申请人：{childInfo.Applicant}]{priName}[收款方：{childInfo.Payee}] {childInfo.PayCurrCode} {_PaymentAmount.ToString("#0.00")}、CNY：{childInfo.CNYSubTotalAmount.ToString("#0.00")}(团组：{groupInfo.TeamName})";
childInfo.RemaksDescription = remaksDescription;
childList.Add(childInfo);
rouNumber++;
}
}
CompanyInfo companyInfo = new CompanyInfo();
companyInfo = ExpenseCompanyByTeamId(groupInfo.TeamDid);
dataList.Add(new tree_Group_DailyFeePaymentPageListView()
{
Id = Guid.NewGuid().ToString("N"),
GroupName = groupInfo.TeamName,
CompanyId = companyInfo.Id,
ConpanyName = companyInfo.ConpanyName,
CNYTotalAmount = childList.Sum(it => it.CNYSubTotalAmount),
ChildList = childList,
});
}
_DailyFeePaymentResult.dataList = dataList;
return _DailyFeePaymentResult;
}
/// <summary>
/// 付款申请(日付申请相关)
/// 查询 根据日期范围
/// </summary>
/// <param name="_dailyIds"></param>
/// <param name="beginDt"></param>
/// <param name="endDt"></param>
/// <returns></returns>
private tree_Fin_DailyFeePaymentResult PayRequest_DailyByDateRange(int status, List<int> _dailyIds, string beginDt, string endDt)
{
#region sql条件处理
string sqlWhere = string.Format(@" And dfp.CreateTime between '{0} 00:00:00' And '{1} 23:59:59' ", beginDt, endDt);
if (status == 2)
{
if (_dailyIds.Count < 1)
{
return new tree_Fin_DailyFeePaymentResult() { childList = new List<Fin_DailyFeePaymentContentInfolView>() };
}
sqlWhere += string.Format(@" And dfp.Id In({0}) ", string.Join(",", _dailyIds));
}
string sql_1 = string.Format(@"Select * From (
Select row_number() over (order by dfp.Id Desc) as RowNumber,
dfp.Id,dfp.CompanyId,c.CompanyName,dfp.Instructions,dfp.SumPrice,
dfp.CreateUserId,u.CnName CreateUser,dfp.CreateTime,dfp.FAudit,dfp.MAudit,
dfp.PriceTypeId,dfp.TransferTypeId
From Fin_DailyFeePayment dfp
Inner Join Sys_Company c On dfp.CompanyId = c.Id
Left Join Sys_Users u On dfp.CreateUserId = u.Id
Where dfp.IsDel=0 {0} And dfp.FAudit = 1 And dfp.MAudit = 1 And dfp.IsPay = 0
) temp ", sqlWhere);
#endregion
List<tree_Fin_DailyFeePaymentPageListView> DailyFeePaymentData = _sqlSugar.SqlQueryable<tree_Fin_DailyFeePaymentPageListView>(sql_1).ToList();
Dictionary<int, string> dic_setData = new Dictionary<int, string>();
Sys_SetDataType stGZ = _daiRep.Query<Sys_SetDataType>(s => s.Name == "公转").First();
Sys_SetDataType stSZ = _daiRep.Query<Sys_SetDataType>(s => s.Name == "私转").First();
foreach (var item in DailyFeePaymentData)
{
if (_dailyIds != null)
{
item.IsChecked = _dailyIds.Contains(item.Id);
}
if (dic_setData.ContainsKey(item.PriceTypeId))
{
item.priceTypeStr = dic_setData[item.PriceTypeId];
}
else
{
Sys_SetData sd_priceType = _daiRep.Query<Sys_SetData>(s => s.Id == item.PriceTypeId).First();
if (sd_priceType != null)
{
item.priceTypeStr = sd_priceType.Name;
dic_setData.Add(item.PriceTypeId, sd_priceType.Name);
}
}
if (dic_setData.ContainsKey(item.transferTypeId))
{
item.transferTypeIdStr = dic_setData[item.transferTypeId];
Sys_SetData sd_transfer = _daiRep.Query<Sys_SetData>(s => s.Id == item.transferTypeId).First();
if (sd_transfer != null)
{
item.transferParentId = sd_transfer.STid;
item.transferParentIdStr = sd_transfer.STid == stGZ.Id ? "公转" : sd_transfer.STid == stSZ.Id ? "私转" : "";
}
}
else
{
Sys_SetData sd_transfer = _daiRep.Query<Sys_SetData>(s => s.Id == item.transferTypeId).First();
if (sd_transfer != null)
{
item.transferTypeIdStr = sd_transfer.Name;
item.transferParentId = sd_transfer.STid;
item.transferParentIdStr = sd_transfer.STid == stGZ.Id ? "公转" : sd_transfer.STid == stSZ.Id ? "私转" : "";
dic_setData.Add(item.transferTypeId, sd_transfer.Name);
}
}
string feeContentSql = string.Format(@"Select * From Fin_DailyFeePaymentContent
Where IsDel=0 And DFPId = {0} ", item.Id);
item.childList = _sqlSugar.SqlQueryable<Fin_DailyFeePaymentContentInfolView>(feeContentSql).ToList();
int rowNumber = 1;
foreach (var subItem in item.childList)
{
string remaksDescription = $"{rowNumber}、【{item.priceTypeStr}】{item.Instructions}({subItem.PriceName}) CNY：{subItem.ItemTotal.ToString("#0.0000")}(单价：{subItem.Price.ToString("#0.0000")} * {subItem.Quantity.ToString("#0.0000")})";
subItem.RemaksDescription = remaksDescription;
string excelRemaksDescription = $"【{item.priceTypeStr}】{item.Instructions}({subItem.PriceName}) CNY：{subItem.ItemTotal.ToString("#0.0000")}(单价：{subItem.Price.ToString("#0.0000")} * {subItem.Quantity.ToString("#0.0000")})【申请人：{item.CreateUser} 申请时间：{item.CreateTime.ToString("yyyy-MM-dd HH:mm:ss")}】";
subItem.ExcelRemaksDescription = excelRemaksDescription;
rowNumber++;
}
}
decimal total_gz = DailyFeePaymentData.Where(s => s.transferParentId == stGZ.Id).Sum(d => d.SumPrice ?? 0M);
decimal total_sz = DailyFeePaymentData.Where(s => s.transferParentId == stSZ.Id).Sum(d => d.SumPrice ?? 0M);
var result = new tree_Fin_DailyFeePaymentResult() { gz = total_gz, sz = total_sz, dataList = DailyFeePaymentData };
return result;
}
/// <summary>
/// 付款申请 (PageId=51)
/// 团组,日付相关费用 选中状态变更
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
[HttpPost]
[ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
public async Task<IActionResult> PostPayRequestCheckedChange(PayRequestCheckedChangeDto dto)
{
#region 验证
if (dto.Type < 1 || dto.Type > 2)
{
return Ok(JsonView(false, "请传入有效的Type参数! 1 checked 2 清除上次勾选"));
}
#endregion
PaymentRequestCheckedView requestCheckedView = new PaymentRequestCheckedView();
List<int> groupIds = new List<int>();
List<int> dailyPaymentIds = new List<int>();
List<int> hotelIds = new List<int>();
#region 参数处理
if (!string.IsNullOrEmpty(dto.GroupIds))
{
if (dto.GroupIds.Contains(","))
{
groupIds = dto.GroupIds.Split(',').Select(int.Parse).ToList();
}
else
{
groupIds.Add(int.Parse(dto.GroupIds));
}
}
if (!string.IsNullOrEmpty(dto.HotelSubIds))
{
if (dto.HotelSubIds.Contains(","))
{
hotelIds = dto.HotelSubIds.Split(',').Select(int.Parse).ToList();
}
else
{
hotelIds.Add(int.Parse(dto.HotelSubIds));
}
}
if (!string.IsNullOrEmpty(dto.DailyPaymentIds))
{
if (dto.DailyPaymentIds.Contains(","))
{
dailyPaymentIds = dto.DailyPaymentIds.Split(',').Select(int.Parse).ToList();
}
else
{
dailyPaymentIds.Add(int.Parse(dto.DailyPaymentIds));
}
}
#endregion
requestCheckedView.GroupIds = groupIds;
requestCheckedView.HotelSubIds = hotelIds;
requestCheckedView.DailyPaymentIds = dailyPaymentIds;
if (dto.Type == 1)
{
TimeSpan ts = DateTime.Now.AddDays(180) - DateTime.Now; //设置redis 过期时间 半年(180)
var status = await RedisRepository.RedisFactory.CreateRedisRepository().StringSetAsync<string>("paymentRequestCheckedData", JsonConvert.SerializeObject(requestCheckedView), ts);
if (status)
{
return Ok(JsonView(true, "操作成功!"));
}
}
else if (dto.Type == 2)
{
var status = await RedisRepository.RedisFactory.CreateRedisRepository().KeyDeleteAsync("paymentRequestCheckedData");
if (status)
{
return Ok(JsonView(true, "操作成功!"));
}
}
return Ok(JsonView(false, "操作失败!"));
}
/// <summary>
/// 付款申请 (PageId=51)
/// 团组,日付相关费用 汇率变更
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
[HttpPost]
[ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
public async Task<IActionResult> PostPayRequestRateChange(PayRequestRateChangeDto dto)
{
#region 验证
DateTime beginDt, endDt;
string format = "yyyy-MM-dd";
if (!DateTime.TryParseExact(dto.beginDt, format, CultureInfo.InvariantCulture, DateTimeStyles.None, out beginDt))
{
return Ok(JsonView(false, "开始日期格式不正确！正确格式：yyyy-MM-dd"));
}
if (!DateTime.TryParseExact(dto.endDt, format, CultureInfo.InvariantCulture, DateTimeStyles.None, out endDt))
{
return Ok(JsonView(false, "结束日期格式不正确！正确格式：yyyy-MM-dd"));
}
if (dto.UserId < 1) return Ok(JsonView(false, "请传入有效的UserId参数!"));
if (dto.Id < 1) return Ok(JsonView(false, "请传入有效的Id参数!"));
if (dto.Rate <= 0) return Ok(JsonView(false, "请传入有效的Rate参数!"));
#endregion
int hotelSubStatus = 0, status = 0;
int diid = 0, cTable = 0, currId = 0;
if (dto.HotelSubId > 0)
{
//更改酒店子表汇率
hotelSubStatus = _sqlSugar.Updateable<Grp_HotelReservationsContent>()
.SetColumns(it => it.Rate == dto.Rate)
.Where(it => it.Id == dto.HotelSubId)
.ExecuteCommand();
if (hotelSubStatus > 0)
{
var hotelSubInfo = _sqlSugar.Queryable<Grp_HotelReservationsContent>().Where(it => it.Id == dto.HotelSubId).First();
if (hotelSubInfo != null)
{
diid = hotelSubInfo.DiId;
currId = hotelSubInfo.Currency;
}
//付款申请汇率更改成功；更改团组汇率对应币种
string currCode = _sqlSugar.Queryable<Sys_SetData>().Where(it => it.Id == currId).First()?.Name ?? "";
await _teamRateRep.UpdateGroupRateByDiIdAndCTableId(diid, 76, currCode, dto.Rate);
}
}
if (dto.Id > 0)
{
var ccpInfo = _sqlSugar.Queryable<Grp_CreditCardPayment>().Where(it => it.Id == dto.Id).First();
decimal cnyMoney = 0.00M;
if (ccpInfo != null)
{
cnyMoney = ccpInfo.PayMoney * dto.Rate;
diid = ccpInfo.DIId;
cTable = ccpInfo.CTable;
currId = ccpInfo.PaymentCurrency;
}
status = _sqlSugar.Updateable<Grp_CreditCardPayment>()
.SetColumns(it => it.DayRate == dto.Rate)
.SetColumns(it => it.RMBPrice == cnyMoney)
.Where(it => it.Id == dto.Id)
.ExecuteCommand();
if (status > 0)
{
//付款申请汇率更改成功；更改团组汇率对应币种
if (cTable != 76)
{
string currCode = _sqlSugar.Queryable<Sys_SetData>().Where(it => it.Id == currId).First()?.Name ?? "";
await _teamRateRep.UpdateGroupRateByDiIdAndCTableId(diid, cTable, currCode, dto.Rate);
}
}
}
if (hotelSubStatus > 0 || status > 0)
{
PaymentRequestCheckedView checkedView = new PaymentRequestCheckedView();
var checkedStr = await RedisRepository.RedisFactory.CreateRedisRepository().StringGetAsync<string>("paymentRequestCheckedData");
if (checkedStr != null)
{
checkedView = JsonConvert.DeserializeObject<PaymentRequestCheckedView>(checkedStr.ToString());
}
tree_Fin_DailyFeePaymentResult dailyResult = PayRequest_DailyByDateRange(1, checkedView.DailyPaymentIds, dto.beginDt, dto.endDt);
tree_Group_DailyFeePaymentResult groupResult = PayRequest_GroupPaymentByDateRange(1, checkedView.GroupIds, checkedView.HotelSubIds, dto.beginDt, dto.endDt);
decimal _gz = dailyResult.gz + groupResult.gz;
decimal _sz = dailyResult.sz + groupResult.sz;
return Ok(JsonView(true, "操作成功!", new { gz = dailyResult, sz = groupResult }));
}
return Ok(JsonView(false, "该项汇率修改失败!"));
}
/// <summary>
/// 付款申请 (PageId=51)
/// 团组,日付相关费用 付款状态变更
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
[HttpPost]
[ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
public async Task<IActionResult> PostPayRequestPayChange(PayRequestPayChangeDto dto)
{
if (dto.UserId < 1) return Ok(JsonView(false, "请传入有效的UserId参数!"));
//if (string.IsNullOrEmpty(dto.GroupIds))
//{
// return Ok(JsonView(false, "请传入有效的GroupIds参数!"));
//}
//if (string.IsNullOrEmpty(dto.DailyPaymentIds))
//{
// return Ok(JsonView(false, "请传入有效的DailyPaymentIds参数!"));
//}
List<int> groupIds = new List<int>();
List<int> dailyPaymentIds = new List<int>();
List<int> hotelSubIds = new List<int>();
#region 参数处理
if (!string.IsNullOrEmpty(dto.GroupIds))
{
if (dto.GroupIds.Contains(",")) groupIds = dto.GroupIds.Split(',').Select(int.Parse).ToList();
else groupIds.Add(int.Parse(dto.GroupIds));
}
if (!string.IsNullOrEmpty(dto.HotelSubIds))
{
if (dto.HotelSubIds.Contains(",")) hotelSubIds = dto.HotelSubIds.Split(',').Select(int.Parse).ToList();
else hotelSubIds.Add(int.Parse(dto.HotelSubIds));
if (hotelSubIds.Count > 0)
{
foreach (var item in hotelSubIds)
{
if (item < 1)
{
hotelSubIds.Remove(item);
}
}
}
}
if (!string.IsNullOrEmpty(dto.DailyPaymentIds))
{
if (dto.DailyPaymentIds.Contains(",")) dailyPaymentIds = dto.DailyPaymentIds.Split(',').Select(int.Parse).ToList();
else dailyPaymentIds.Add(int.Parse(dto.DailyPaymentIds));
}
#endregion
bool changeStatus = false;
_sqlSugar.BeginTran();
if (groupIds.Count > 0)
{
var ccpInfos = _sqlSugar.Queryable<Grp_CreditCardPayment>().Where(it => it.IsDel == 0 && groupIds.Contains(it.Id)).ToList();
var otherTypeCcpIds = ccpInfos.Where(it => it.CTable != 76).Select(it => it.Id).ToList();
var hotelTyprCcpIds = ccpInfos.Where(it => it.CTable == 76).Select(it => it.Id).ToList();
int groupStatus = 0;
if (otherTypeCcpIds.Count > 0) //其他费用类型 正常付款
{
groupStatus = _sqlSugar.Updateable<Grp_CreditCardPayment>()
.SetColumns(it => it.IsPay == 1)
.Where(it => otherTypeCcpIds.Contains(it.Id))
.ExecuteCommand();
changeStatus = true;
}
if (hotelTyprCcpIds.Count > 0) //酒店费用子项逻辑付款，酒店子项费用全部付完款，c表ispay=1
{
if (hotelSubIds.Count > 0)
{
List<int> hrPayIds = new List<int>();
var hrIspayStatus = _sqlSugar.Updateable<Grp_HotelReservationsContent>()
.SetColumns(it => it.IsPay == 1)
.Where(it => hotelSubIds.Contains(it.Id))
.ExecuteCommand();
changeStatus = true;
//酒店子项是否全部付完款
List<int> hrIds = _sqlSugar.Queryable<Grp_HotelReservationsContent>().Where(it => it.IsDel == 0 && hotelSubIds.Contains(it.Id)).Select(it => it.HrId).Distinct().ToList();
if (hrIds.Count > 0)
{
var hotelSubFeeData = _sqlSugar.Queryable<Grp_HotelReservationsContent>().Where(it => it.IsDel == 0 && hrIds.Contains(it.HrId)).ToList();
var hotelSubFeeGroupData = hotelSubFeeData.GroupBy(it => it.HrId).ToList();
foreach (var item in hotelSubFeeGroupData)
{
var allTotal = item.Where(it => it.Price > 0).ToList().Count;
var paymentTotal = item.Where(it => it.Price > 0 && it.IsPay == 1).ToList().Count;
if (allTotal == paymentTotal)
{
hrPayIds.Add(item.Key);
}
}
}
if (hrPayIds.Count > 0)
{
//c表更改全部付款的酒店费用
groupStatus = _sqlSugar.Updateable<Grp_CreditCardPayment>()
.SetColumns(it => it.IsPay == 1)
.Where(it => it.CTable == 76 && hrPayIds.Contains(it.CId))
.ExecuteCommand();
}
}
}
}
//if (hotelSubIds.Count > 0)
//{
// var groupStatus = _sqlSugar.Updateable<Grp_HotelReservationsContent>()
// .SetColumns(it => it.IsPay == 1)
// .Where(it => hotelSubIds.Contains(it.Id))
// .ExecuteCommand();
// if (groupStatus > 0)
// {
// changeStatus = true;
// }
//}
if (dailyPaymentIds.Count > 0)
{
var dailyPaymentStatus = _sqlSugar.Updateable<Fin_DailyFeePayment>()
.SetColumns(it => it.IsPay == 1)
.Where(it => dailyPaymentIds.Contains(it.Id))
.ExecuteCommand();
//修改提成关联表
var ids = _sqlSugar.Queryable<Fin_DailyFeePayment>()
.LeftJoin<Fin_RoyaltyForm>((dfp, rf) => dfp.Id == rf.DayOverhead && rf.IsDel == 0)
.Where(dfp => dailyPaymentIds.Contains(dfp.Id) && dfp.IsDel == 0)
.Select((dfp, rf) => rf.ConfirmIdArr).ToList();
var idsList = string.Join(',', ids).Split(',')
.Select(x =>
{
if (!string.IsNullOrWhiteSpace(x) && int.TryParse(x, out int intx))
{
return intx;
}
return 0;
})
.Where(x => x != 0)
.Distinct()
.ToList();
_sqlSugar.Updateable<Fin_RoyaltyConfirm>()
.SetColumns(it => it.IsSeed == 1)
.Where(it => idsList.Contains(it.Id) && it.IsSeed == 0)
.ExecuteCommand();
if (dailyPaymentStatus > 0)
{
changeStatus = true;
}
}
if (changeStatus)
{
_sqlSugar.CommitTran();
#region 应用推送
try
{
foreach (int ccpId in groupIds)
{
List<string> tempList = new List<string>() { ccpId.ToString() };
await AppNoticeLibrary.SendUserMsg_GroupStatus_PayResult(ccpId, tempList);
}
foreach (int dailyId in dailyPaymentIds)
{
List<string> tempList = new List<string>() { dailyId.ToString() };
await AppNoticeLibrary.DailyPayReminder_Pay_ToUser(dailyId, tempList);
}
}
catch (Exception ex)
{
}
#endregion
return Ok(JsonView(true, "操作成功!"));
}
_sqlSugar.RollbackTran();
return Ok(JsonView(false, "付款状态修改失败!"));
}
/// <summary>
/// 付款申请 (PageId=51)
/// File Download
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
[HttpPost]
[ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
public async Task<IActionResult> PostPayRequestFileDownload(PayRequestFileDownloadDto dto)
{
Stopwatch stopwatch = Stopwatch.StartNew();
#region 参数,权限 验证
if (dto.PortType < 1)
{
return Ok(JsonView(false, "请传入有效的PortType参数!"));
}
if (dto.UserId < 1)
{
return Ok(JsonView(false, "请传入有效的UserId参数!"));
}
if (dto.PageId < 1)
{
dto.PageId = 51;
return Ok(JsonView(false, "请传入有效的PageId参数!"));
}
if (dto.ConpanyId < 1 || dto.ConpanyId > 4)
{
return Ok(JsonView(false, "请传入有效的ConpanyId参数!"));
}
PageFunAuthViewBase pageFunAuthView = new PageFunAuthViewBase();
#region 页面操作权限验证
pageFunAuthView = await GeneralMethod.PostUserPageFuncDatas(dto.UserId, dto.PageId);
if (pageFunAuthView.FilesDownloadAuth == 0) return Ok(JsonView(false, "您没有文件下载权限!"));
#endregion
#endregion
try
{
PaymentRequestCheckedView checkedView = new PaymentRequestCheckedView();
var checkedStr = await RedisRepository.RedisFactory.CreateRedisRepository().StringGetAsync<string>("paymentRequestCheckedData");
if (checkedStr != null)
{
checkedView = JsonConvert.DeserializeObject<PaymentRequestCheckedView>(checkedStr.ToString());
}
if (checkedView == null)
{
return Ok(JsonView(false, "没有选中的数据!"));
}
if (checkedView.GroupIds == null && checkedView.DailyPaymentIds == null)
{
return Ok(JsonView(false, "没有选中的数据!"));
}
tree_Fin_DailyFeePaymentResult dailyResult = PayRequest_DailyByDateRange(2, checkedView.DailyPaymentIds, dto.beginDt, dto.endDt);
tree_Group_DailyFeePaymentResult groupResult = PayRequest_GroupPaymentByDateRange(2, checkedView.GroupIds, checkedView.HotelSubIds, dto.beginDt, dto.endDt);
if (dailyResult.childList == null)
{
dailyResult.childList = new List<Fin_DailyFeePaymentContentInfolView>();
}
List<tree_Group_DailyFeePaymentPageListView> _GroupData = new List<tree_Group_DailyFeePaymentPageListView>();
List<tree_Fin_DailyFeePaymentPageListView> _DailyData = new List<tree_Fin_DailyFeePaymentPageListView>();
//1 成都泛美商务有限公司
if (dto.ConpanyId == 1)
{
if (groupResult.dataList != null && groupResult.dataList.Count > 0) _GroupData = groupResult.dataList.Where(it => it.CompanyId == 1).ToList();
if (dailyResult.dataList != null && dailyResult.dataList.Count > 0) _DailyData = dailyResult.dataList.Where(it => it.CompanyId == 1).ToList();
}
//2 四川泛美交流有限公司
else if (dto.ConpanyId == 2)
{
if (groupResult.dataList != null && groupResult.dataList.Count > 0) _GroupData = groupResult.dataList.Where(it => it.CompanyId == 2).ToList();
if (dailyResult.dataList != null && dailyResult.dataList.Count > 0) _DailyData = dailyResult.dataList.Where(it => it.CompanyId == 2).ToList();
}
//3 成都纽茵教育科技有限公司
else if (dto.ConpanyId == 3)
{
if (groupResult.dataList != null && groupResult.dataList.Count > 0) _GroupData = groupResult.dataList.Where(it => it.CompanyId == 3).ToList();
if (dailyResult.dataList != null && dailyResult.dataList.Count > 0) _DailyData = dailyResult.dataList.Where(it => it.CompanyId == 3).ToList();
}
//4 成都鸿企中元科技有限公司
else if (dto.ConpanyId == 4)
{
return Ok(JsonView(false, "暂未开放该类型!"));
}
else
{
return Ok(JsonView(false, "参数ConpanyId不可使用!"));
}
string _requestPaymentDt = DateTime.Now.ToString("yyyy-MM-dd"),//申请付款日期
_appliedAmount = "", //申请付款金额
_GZStr = "", //公转价格描述
_SZStr = ""; //私转价格描述
decimal groupGZAmout = 0.00M, groupSZAmout = 0.00M;
decimal dailyGZAmout = 0.00M, dailySZAmout = 0.00M;
string dailyGZStr = "", dailySZStr = "", groupGZStr = "", groupSZStr = "";
#region 数据处理
//团组费用相关
foreach (var item in _GroupData)
{
string groupGZSubStr = "";
string groupSZSubStr = "";
foreach (var subItem in item.ChildList)
{
if (subItem.TransferMark.Equals("公转"))
{
groupGZAmout += subItem.CNYSubTotalAmount;
groupGZSubStr += $"{subItem.RemaksDescription}\r\n";
}
else if (subItem.TransferMark.Equals("私转"))
{
groupSZAmout += subItem.CNYSubTotalAmount;
groupSZSubStr += $"{subItem.RemaksDescription}\r\n";
}
//groupGZSubStr += $"\t";
}
//if (!string.IsNullOrEmpty(groupGZSubStr)) groupGZStr += $"团组：{item.GroupName}\r\n{groupGZSubStr}\r\n";
//if (!string.IsNullOrEmpty(groupSZSubStr)) groupSZStr += $"团组：{item.GroupName}\r\n{groupSZSubStr}\r\n";
if (!string.IsNullOrEmpty(groupGZSubStr)) groupGZStr += $"{groupGZSubStr}\r\n";
if (!string.IsNullOrEmpty(groupSZSubStr)) groupSZStr += $"{groupSZSubStr}\r\n";
}
//日常费用相关
foreach (var item in _DailyData)
{
foreach (var subItem in item.childList)
{
if (item.transferParentId == 62) //公转
{
dailyGZAmout += item.SumPrice ?? 0.00M;
dailyGZStr += $"{item.RowNumber}、【{item.CompanyName}】{subItem.ExcelRemaksDescription}\r\n";
}
else if (item.transferParentId == 63) //私转
{
dailySZAmout += item.SumPrice ?? 0.00M;
dailySZStr += $"{item.RowNumber}、【{item.CompanyName}】{subItem.ExcelRemaksDescription}\r\n";
}
}
}
_GZStr = $"【公转】团组相关费用（合计：CNY {groupGZAmout.ToString("#0.00")}）：\r\n{groupGZStr}【公转】日常付款费用（合计：CNY {dailyGZAmout.ToString("#0.00")}）：\r\n{dailyGZStr}";
_SZStr = $"【私转】团组相关费用（合计：CNY {groupSZAmout.ToString("#0.00")}）：\r\n{groupSZStr}【私转】日常付款费用（合计：CNY {dailySZAmout.ToString("#0.00")}）：\r\n{dailySZStr}";
_appliedAmount = $"公转：CNY {(groupGZAmout + dailyGZAmout).ToString("#0.00")}\r\n私转：CNY {(groupSZAmout + dailySZAmout).ToString("#0.00")}";
#endregion
WorkbookDesigner designer = new WorkbookDesigner();
designer.Workbook = new Workbook(AppSettingsHelper.Get("ExcelBasePath") + "Template/付款申请书.xls");
designer.SetDataSource("Date", _requestPaymentDt);
designer.SetDataSource("Price", _appliedAmount);
designer.SetDataSource("Content", _GZStr);
designer.SetDataSource("Content1", _SZStr);
//根据数据源处理生成报表内容
designer.Process();
string fileName = ("PayRequest/付款申请(" + dto.beginDt + "～" + dto.endDt + ").xlsx");
designer.Workbook.Save(AppSettingsHelper.Get("ExcelBasePath") + fileName);
string rst = AppSettingsHelper.Get("ExcelBaseUrl") + AppSettingsHelper.Get("ExcelFtpPath") + fileName;
stopwatch.Stop();
return Ok(JsonView(true, $"操作成功!{stopwatch.ElapsedMilliseconds / 1000}s", new { url = rst }));
}
catch (Exception ex)
{
return Ok(JsonView(false, ex.Message));
}
}
#endregion
#region 超支费用
/// <summary>
/// 超支费用
/// 1增、2改、3删
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
[HttpPost]
[ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
public async Task<IActionResult> PostGroupExtraCost_Operator(Fin_GroupExtraCostDto_OP dto)
{
#region 验证
#endregion
Fin_GroupExtraCost _entity = new Fin_GroupExtraCost();
_entity.DiId = dto.diId;
_entity.PriceName = dto.priceName;
_entity.Price = dto.price;
_entity.PriceCurrency = dto.currency;
_entity.PriceType = dto.priceType;
_entity.Coefficient = dto.coefficient;
_entity.PriceDetailType = dto.priceDetailType;
_entity.FilePath = dto.filePath;
_entity.Remark = dto.remark;
_entity.PriceCount = dto.PriceCount;
_entity.Area = dto.Area;
_entity.SupervisorConfirm = dto.SupervisorConfirm;
_entity.ManagerConfirm = dto.ManagerConfirm;
_entity.SYsupervisorConfirm = dto.SYsupervisorConfirm;
DateTime dt_PriceDt;
bool b_PriceDt = DateTime.TryParse(dto.PriceDt, out dt_PriceDt);
if (b_PriceDt)
{
_entity.PriceDt = dt_PriceDt;
}
else
{
_entity.PriceDt = DateTime.MinValue;
}
_entity.PriceSum = dto.price * dto.PriceCount;
_daiRep.BeginTran();
if (dto.editType == 1)
{
_entity.CreateUserId = dto.createUser;
_entity.CreateTime = DateTime.Now;
_entity.IsDel = 0;
int returnId = await _daiRep.AddAsyncReturnId<Fin_GroupExtraCost>(_entity);
if (returnId > 0)
{
dto.Id = returnId;
}
}
else if (dto.editType == 2)
{
bool res = await _daiRep.UpdateAsync<Fin_GroupExtraCost>(s => s.Id == dto.Id, s => new Fin_GroupExtraCost
{
PriceName = dto.priceName,
Price = dto.price,
PriceCurrency = dto.currency,
PriceType = dto.priceType,
PriceDetailType = dto.priceDetailType,
Coefficient = dto.coefficient,
FilePath = dto.filePath,
Remark = dto.remark,
PriceCount = dto.PriceCount,
PriceDt = _entity.PriceDt,
PriceSum = _entity.PriceSum,
Area = _entity.Area,
});
if (!res)
{
_daiRep.RollbackTran();
return Ok(JsonView(false, "2操作失败！"));
}
}
else if (dto.editType == 3)
{
string delTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm");
bool res = await _daiRep.UpdateAsync<Fin_GroupExtraCost>(s => s.Id == dto.Id, s => new Fin_GroupExtraCost
{
IsDel = 1,
DeleteTime = delTime,
DeleteUserId = dto.createUser
});
if (!res)
{
_daiRep.RollbackTran();
return Ok(JsonView(false, "3操作失败！"));
}
}
else
{
_daiRep.RollbackTran();
return Ok(JsonView(false, "未知的editType"));
}
if (!extraCost_editCreditCardPayment(dto))
{
return Ok(JsonView(false, "ccp操作失败"));
}
//同步修改收款账单
_foreignReceivablesRepository.OverSpSeteceivables(new OverSpSeteceivablesDto
{
CreateUserId = dto.createUser,
DiId = dto.diId,
});
_daiRep.CommitTran();
return Ok(JsonView(true, "操作成功"));
}
private Result PostCurrencyByDiid_Sync(int diId, int CId, int currencyId)
{
if (diId == 0)
{
return new Result() { Code = -1, Msg = "请传入团组Id！" };
}
if (CId == 0)
{
return new Result() { Code = -1, Msg = "请传入类型Id！" };
}
if (currencyId == 0)
{
return new Result() { Code = -1, Msg = "请传入币种Id！" };
}
Grp_TeamRate _TeamRate = _sqlSugar.Queryable<Grp_TeamRate>().First(a => a.DiId == diId && a.IsDel == 0 && a.CTable == CId);
List<CurrencyInfo> currencyInfos = new List<CurrencyInfo>();
CurrencyInfo CurrencyRate = new CurrencyInfo();
try
{
if (_TeamRate != null)
{
Sys_SetData _SetData = _sqlSugar.Queryable<Sys_SetData>().First(a => a.IsDel == 0 && a.Id == currencyId);
if (_SetData != null)
{
currencyInfos = CommonFun.GetCurrencyChinaToList(_TeamRate.Remark);
CurrencyRate = currencyInfos.FirstOrDefault(a => a.CurrencyCode == _SetData.Name);
if (CurrencyRate != null)
{
return new Result() { Code = 0, Msg = "查询成功！", Data = CurrencyRate };
}
else
{
return new Result() { Code = -1, Msg = "暂无团组汇率，请前往设置！", Data = CurrencyRate };
}
}
else
{
return new Result() { Code = -1, Msg = "暂无团组汇率，请前往设置！", Data = CurrencyRate };
}
}
else
{
return new Result() { Code = -1, Msg = "暂无团组汇率，请前往设置！", Data = CurrencyRate };
}
}
catch (Exception)
{
return new Result() { Code = -1, Msg = "查询异常！", Data = CurrencyRate };
}
}
private bool extraCost_editCreditCardPayment(Fin_GroupExtraCostDto_OP costDto)
{
//设置团组汇率
decimal dcm_dayrate = 1M;
decimal dcm_rmbPrice = costDto.price;
int ispay = costDto.payType == 72 ? 1 : 0;
if (costDto.costSign != 3)
{
//获取新汇率 int diId,int CId, int currencyId
Result rate = this.PostCurrencyByDiid_Sync(costDto.diId, 1015, costDto.currency);
if (rate.Code == 0)
{
var rateInfo = (rate.Data as CurrencyInfo);
if (rateInfo is not null)
{
dcm_dayrate = rateInfo.Rate;
dcm_rmbPrice = rateInfo.Rate * dcm_rmbPrice;
}
else
{
dcm_dayrate = 1;
}
}
}
Grp_CreditCardPayment ccp = _daiRep.Query<Grp_CreditCardPayment>(s => s.CId == costDto.Id && s.CTable == 1015).First();
if (ccp == null)
{
ccp = new Grp_CreditCardPayment();
ccp.PayDId = costDto.payType;// dto
ccp.ConsumptionPatterns = "";
ccp.ConsumptionDate = "";
ccp.CTDId = costDto.payCardId;// dto
ccp.BankNo = "";
ccp.CardholderName = "";
ccp.PayMoney = costDto.price;// dto
ccp.PaymentCurrency = costDto.currency;// dto
ccp.CompanyBankNo = "";
ccp.OtherBankName = "";
ccp.OtherSideNo = "";
ccp.OtherSideName = "";
ccp.Remark = "";
ccp.CreateUserId = costDto.createUser;
ccp.CreateTime = DateTime.Now;
ccp.MFOperator = 0;
ccp.MFOperatorDate = "";
ccp.IsAuditDM = 0;
ccp.AuditDMOperate = 0;
ccp.AuditDMDate = "";
ccp.IsAuditMF = 0;
ccp.AuditMFOperate = 0;
ccp.AuditMFDate = "";
ccp.IsAuditGM = 0;
ccp.AuditGMOperate = 0;
ccp.AuditGMDate = "";
ccp.IsPay = ispay; // upd
ccp.DIId = costDto.diId;// dto
ccp.CId = costDto.Id;// dto
ccp.CTable = 1015; //超支费用指向id
ccp.IsDel = 0;
ccp.PayPercentage = 100M;
ccp.PayThenMoney = 0M;
ccp.PayPercentageOld = 100M;
ccp.PayThenMoneyOld = 0M;
ccp.UpdateDate = "";
ccp.Payee = costDto.payee;// dto
ccp.OrbitalPrivateTransfer = costDto.costSign;// dto
ccp.ExceedBudget = 0;
ccp.DayRate = dcm_dayrate; //upd
ccp.RMBPrice = dcm_rmbPrice; //upd
int ccpInsertId = _daiRep.AddReturnId<Grp_CreditCardPayment>(ccp);
if (ccpInsertId > 0)
{
return true;
}
}
else
{
if (costDto.editType == 2)
{
bool res = _daiRep.Update<Grp_CreditCardPayment>(s => s.Id == ccp.Id, s => new Grp_CreditCardPayment
{
PayDId = costDto.payType,
CTDId = costDto.payCardId,
PayMoney = costDto.price,
PaymentCurrency = costDto.currency,
IsPay = ispay,
Payee = costDto.payee,
OrbitalPrivateTransfer = costDto.costSign,
DayRate = dcm_dayrate,
RMBPrice = dcm_rmbPrice
});
return res;
}
else if (costDto.editType == 3)
{
string delTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm");
bool res2 = _daiRep.Update<Grp_CreditCardPayment>(s => s.Id == ccp.Id, s => new Grp_CreditCardPayment
{
IsDel = 1,
DeleteTime = delTime,
DeleteUserId = costDto.createUser
});
return res2;
}
}
return false;
}
/// <summary>
/// 超支费用
/// 详情查询
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
[HttpPost]
[ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
public async Task<IActionResult> PostGroupExtraCost_Detail(Fin_GroupExtraCostDto_Detail dto)
{
if (dto.Id < 1)
{
return Ok(JsonView(false, "查询失败"));
}
string sql = string.Format(@" Select
f.Id,f.DiId,
f.PriceName,f.Price,f.PriceCurrency,c.Payee,c.OrbitalPrivateTransfer,c.PayDId,f.area,
c.CTDId,f.PriceType,f.PriceDetailType,f.Coefficient,f.Remark,f.PriceCount,f.PriceDt
From Fin_GroupExtraCost f
Inner Join Grp_CreditCardPayment c On f.Id = c.CId
Left Join Sys_Users u On f.CreateUserId = u.Id
Where f.IsDel=0 And c.CTable = 1015
And f.Id = {0} ", dto.Id);
Fin_GroupExtraCostDetailView detailView = await _sqlSugar.SqlQueryable<Fin_GroupExtraCostDetailView>(sql).FirstAsync();
if (detailView == null)
{
return Ok(JsonView(false, "查询失败"));
}
return Ok(JsonView(true, "查询成功", detailView));
}
/// <summary>
/// 超支费用
/// 列表查询
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
[HttpPost]
[ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
public async Task<IActionResult> PostGroupExtraCost_Search(Fin_GroupExtraCostDto_Search dto)
{
string sqlWhere = string.Format(@" And f.DiId = {0} ", dto.diId);
int startIndex = (dto.PageIndex - 1) * dto.PageSize + 1;
int endIndex = startIndex + dto.PageSize - 1;
string sql_data = string.Format(@"Select * From (
Select row_number() over (order by f.Id Desc) as RowNumber,f.Id,f.DiId,
f.PriceName,f.PriceType,f.PriceDetailType,CAST(f.Price as varchar)+' '+s.[Name] as PriceStr,f.PriceCount,CAST(f.PriceSum as varchar)+' '+s.[Name] as PriceSumStr,
f.CreateUserId,f.PriceDt,c.IsAuditGM,f.ManagerConfirm,f.SupervisorConfirm,f.Remark,
CASE ManagerConfirm WHEN 1 THEN '已确认' ELSE '未确认' END as 'ManagerConfirmStr' , CASE SupervisorConfirm WHEN 1 THEN '已确认' ELSE '未确认' END as 'SupervisorConfirmStr',
CASE SYsupervisorConfirm WHEN 1 THEN '已确认' ELSE '未确认' END as 'SYsupervisorConfirmStr'
From Fin_GroupExtraCost f
Inner Join Grp_CreditCardPayment c On f.Id = c.CId
Inner Join Sys_SetData s On f.PriceCurrency = s.Id
Left Join Sys_Users u On f.CreateUserId = u.Id
Where f.IsDel=0 And c.CTable = 1015 {0}
) temp Where RowNumber Between {1} and {2}", sqlWhere, startIndex, endIndex);
string sql_count = string.Format(@"Select Count(1) as DataCount From (
Select row_number() over (order by f.Id Desc) as RowNumber,f.Id,f.DiId,
f.PriceName,f.PriceType,f.Price,f.FilePath,f.CreateUserId,f.CreateTime,c.IsAuditGM,f.ManagerConfirm,f.SupervisorConfirm,f.Remark,
CASE ManagerConfirm WHEN 1 THEN '已确认' ELSE '未确认' END as 'ManagerConfirmStr' , CASE SupervisorConfirm WHEN 1 THEN '已确认' ELSE '未确认' END as 'SupervisorConfirmStr',
CASE SYsupervisorConfirm WHEN 1 THEN '已确认' ELSE '未确认' END as 'SYsupervisorConfirmStr'
From Fin_GroupExtraCost f
Inner Join Grp_CreditCardPayment c On f.Id = c.CId
Inner Join Sys_SetData s On f.PriceCurrency = s.Id
Left Join Sys_Users u On f.CreateUserId = u.Id
Where f.IsDel=0 And c.CTable = 1015 {0}
) temp ", sqlWhere);
if (dto.PortType == 1 || dto.PortType == 2 || dto.PortType == 3) //web
{
//Fin_DailyFeePaymentPageCount
var count = await _sqlSugar.SqlQueryable<Fin_GroupExtraCostViewDataCount>(sql_count).FirstAsync();
List<Fin_GroupExtraCostView> dataList = await _sqlSugar.SqlQueryable<Fin_GroupExtraCostView>(sql_data).ToListAsync();
Dictionary<int, string> dic_setData = new Dictionary<int, string>();
Dictionary<int, string> dic_user = new Dictionary<int, string>();
foreach (var item in dataList)
{
DateTime dtTemp_PriceDt;
bool b_ct = DateTime.TryParse(item.PriceDt, out dtTemp_PriceDt);
if (b_ct)
{
item.PriceDt = dtTemp_PriceDt.ToString("yyyy-MM-dd");
}
//费用类型
if (dic_setData.ContainsKey(item.PriceType))
{
item.PriceTypeStr = dic_setData[item.PriceType];
}
else
{
Sys_SetData sd_priceTypeDetail = _daiRep.Query<Sys_SetData>(s => s.Id == item.PriceType).First();
if (sd_priceTypeDetail != null)
{
string tempName = sd_priceTypeDetail.Name.Replace("n", "");
item.PriceTypeStr = tempName;
dic_setData.Add(item.PriceType, tempName);
}
}
if (item.PriceDetailType > 0)
{
if (dic_setData.ContainsKey(item.PriceDetailType))
{
item.PriceTypeStr = item.PriceTypeStr + " - " + dic_setData[item.PriceDetailType];
}
else
{
Sys_SetData sd_priceTypeDetail = _daiRep.Query<Sys_SetData>(s => s.Id == item.PriceDetailType).First();
if (sd_priceTypeDetail != null)
{
string tempName = sd_priceTypeDetail.Name.Replace("n", "");
item.PriceTypeStr = item.PriceTypeStr + " - " + tempName;
dic_setData.Add(item.PriceDetailType, tempName);
}
}
}
//系统用户
if (dic_user.ContainsKey(item.CreateUserId))
{
item.CreateUserIdStr = dic_user[item.CreateUserId];
}
else
{
Sys_Users users = _daiRep.Query<Sys_Users>(s => s.Id == item.CreateUserId).First();
if (users != null)
{
item.CreateUserIdStr = users.CnName;
dic_user.Add(item.CreateUserId, users.CnName);
}
}
switch (item.IsAuditGM)
{
case 0: item.IsAuditGMStr = "未审核"; break;
case 1: item.IsAuditGMStr = "已通过"; break;
case 2: item.IsAuditGMStr = "未通过"; break;
default: item.IsAuditGMStr = "未知状态"; break;
}
}
var result = new ListViewBase<Fin_GroupExtraCostView>
{
CurrPageIndex = dto.PageIndex,
CurrPageSize = dto.PageSize,
DataCount = count.DataCount,
DataList = dataList
};
return Ok(JsonView(true, "查询成功", result));
}
return Ok(JsonView(false, "查询失败"));
}
/// <summary>
/// 超支费用
/// 数据集合配置
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
[HttpPost]
[ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
public async Task<IActionResult> PostGroupExtraCost_ListDataInit(Fin_GroupExtraCostDto_DataListInit dto)
{
//支付方式
List<Sys_SetData> Payment = _sqlSugar.Queryable<Sys_SetData>().Where(a => a.STid == 14 && a.IsDel == 0).ToList();
List<SetDataInfoView> _Payment = _mapper.Map<List<SetDataInfoView>>(Payment);
//信用卡类型
List<Sys_SetData> Card = _sqlSugar.Queryable<Sys_SetData>().Where(a => a.STid == 15 && a.IsDel == 0).ToList();
List<SetDataInfoView> _Card = _mapper.Map<List<SetDataInfoView>>(Card);
//超支费用类型
List<Sys_SetData> PriceType = _sqlSugar.Queryable<Sys_SetData>().Where(a => a.STid == 79 && a.IsDel == 0).ToList();
List<SetDataInfoView> _PriceType = _mapper.Map<List<SetDataInfoView>>(PriceType);
//超支费用详细类型
List<Sys_SetData> PriceDetailType = _sqlSugar.Queryable<Sys_SetData>().Where(a => a.STid == 80 && a.IsDel == 0).ToList();
PriceDetailType.ForEach(a => { a.Name = a.Name.Replace("n", ""); });
List<SetDataInfoView> _PriceDetailType = _mapper.Map<List<SetDataInfoView>>(PriceDetailType);
var data = new
{
Payment = _Payment,
Card = _Card,
PriceType = _PriceType,
PriceDetailType = _PriceDetailType
};
return Ok(JsonView(true, "", data));
}
/// <summary>
/// 超支费用
/// 导出团组超支费用Excel
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
[HttpPost]
[ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
public async Task<IActionResult> PostGroupExtraCost_OutputExcel(Fin_GroupExtraCostExcelDto dto)
{
string sqlGroup = string.Format(@" Select * From Grp_DelegationInfo WIth(Nolock) where Id = {0} ", dto.diId);
Grp_DelegationInfo grp_DelegationInfo = await _sqlSugar.SqlQueryable<Grp_DelegationInfo>(sqlGroup).FirstAsync();
if (grp_DelegationInfo == null)
{
return Ok(JsonView(false, "导出失败，未查询到团组"));
}
Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];
Cells cells = sheet.Cells;
//sheet.Protect(Aspose.Cells.ProtectionType.All, "123123", "");//保护工作表
//sheet.Protection.IsSelectingLockedCellsAllowed = true;//设置只能选择解锁单元格
//sheet.Protection.IsFormattingColumnsAllowed = true;//设置可以调整列
//sheet.Protection.IsFormattingRowsAllowed = true;//设置可以调整行
#region 数据源
string sqlData = string.Format(@" Select f.PriceType,REPLACE(s2.[Name],'超支费用','') as PriceTypeStr,'('+REPLACE(s3.[Name],'n','')+')' as PriceDetailTypeStr, PriceDt,PriceName,
Price,PriceCount,s.[Name] as Currency,PriceSum,f.Remark
From Fin_GroupExtraCost as f With(Nolock)
Inner Join Grp_CreditCardPayment as c With(Nolock) On f.Id = c.CId
Inner Join Sys_SetData as s With(Nolock) On f.PriceCurrency = s.Id
Inner Join Sys_SetData as s2 With(Nolock) On f.PriceType = s2.Id
Inner Join Sys_SetData as s3 With(Nolock) On f.PriceDetailType = s3.Id
Where f.DiId = {0} And f.IsDel=0 And c.CTable=1015
Order by PriceType ASC,PriceDt ASC ", dto.diId);
string sqlDataCount = string.Format(@" Select f.PriceType,COUNT(f.PriceType) as DataCount
From Fin_GroupExtraCost as f With(Nolock)
Inner Join Grp_CreditCardPayment as c With(Nolock) On f.Id = c.CId
Where f.DiId = {0} And f.IsDel=0 And c.CTable=1015
Group by PriceType ", dto.diId);
List<Fin_GroupExtraCost_ExcelView> dataList = await _sqlSugar.SqlQueryable<Fin_GroupExtraCost_ExcelView>(sqlData).ToListAsync();
List<Fin_GroupExtraCost_ExcelCountView> countList = await _sqlSugar.SqlQueryable<Fin_GroupExtraCost_ExcelCountView>(sqlDataCount).ToListAsync();
if (dataList.Count < 1 || countList.Count < 1)
{
return Ok(JsonView(false, "导出失败，未查询到数据"));
}
#endregion
#region 标题
string cellValue_Header = grp_DelegationInfo.TeamName;
//Aspose.Cells.Style style1 = workbook.Styles[workbook.Styles.Add()];//新增样式
Aspose.Cells.Style style_Header = workbook.CreateStyle();
style_Header.HorizontalAlignment = TextAlignmentType.Center;//文字居中
style_Header.VerticalAlignment = TextAlignmentType.Center;
style_Header.Font.Name = "微软雅黑";//文字字体
style_Header.Font.Size = 18;//文字大小
style_Header.IsLocked = false;//单元格解锁
style_Header.Font.IsBold = false;//粗体
style_Header.Font.Color = Color.FromArgb(255, 0, 0);
//style1.ForegroundColor = Color.FromArgb(0x99, 0xcc, 0xff);//设置背景色
//style1.Pattern = BackgroundType.Solid; //设置背景样式
//style1.IsTextWrapped = true;//单元格内容自动换行
style_Header.Borders[Aspose.Cells.BorderType.LeftBorder].LineStyle = CellBorderType.Thin; //应用边界线 左边界线
style_Header.Borders[Aspose.Cells.BorderType.RightBorder].LineStyle = CellBorderType.Thin; //应用边界线 右边界线
style_Header.Borders[Aspose.Cells.BorderType.TopBorder].LineStyle = CellBorderType.Thin; //应用边界线 上边界线
style_Header.Borders[Aspose.Cells.BorderType.BottomBorder].LineStyle = CellBorderType.Thin; //应用边界线 下边界线
cells.Merge(1, 1, 1, 10);
Aspose.Cells.Range range_header = cells.CreateRange(1, 1, 1, 10);
range_header.PutValue(cellValue_Header, false, false);
range_header.SetStyle(style_Header);
cells.SetRowHeight(1, 35);
#endregion
#region 列名
Aspose.Cells.Style style_colName = workbook.CreateStyle();
style_colName.Name = "colName";
style_colName.HorizontalAlignment = TextAlignmentType.Center;//文字居中
style_colName.VerticalAlignment = TextAlignmentType.Center;
style_colName.Font.Name = "微软雅黑";//文字字体
style_colName.Font.Size = 12;//文字大小
style_colName.IsLocked = false;//单元格解锁
style_colName.Font.IsBold = true;//粗体
style_colName.Font.Color = Color.FromArgb(0, 0, 0);
style_colName.Borders[Aspose.Cells.BorderType.LeftBorder].LineStyle = CellBorderType.Thin; //应用边界线 左边界线
style_colName.Borders[Aspose.Cells.BorderType.RightBorder].LineStyle = CellBorderType.Thin; //应用边界线 右边界线
style_colName.Borders[Aspose.Cells.BorderType.TopBorder].LineStyle = CellBorderType.Thin; //应用边界线 上边界线
style_colName.Borders[Aspose.Cells.BorderType.BottomBorder].LineStyle = CellBorderType.Thin; //应用边界线 下边界线
List<Fin_GroupExtraCostExcelColumnSetting> colNameSettingList = new List<Fin_GroupExtraCostExcelColumnSetting>()
{
new Fin_GroupExtraCostExcelColumnSetting(){ columnIndex = 1, columnName="类型", columnWidth= 25},
new Fin_GroupExtraCostExcelColumnSetting(){ columnIndex = 2, columnName="时间", columnWidth= 16},
new Fin_GroupExtraCostExcelColumnSetting(){ columnIndex = 3, columnName="内容", columnWidth= 35},
new Fin_GroupExtraCostExcelColumnSetting(){ columnIndex = 4, columnName="单价", columnWidth= 12},
new Fin_GroupExtraCostExcelColumnSetting(){ columnIndex = 5, columnName="数量", columnWidth= 12},
new Fin_GroupExtraCostExcelColumnSetting(){ columnIndex = 6, columnName="货币", columnWidth= 12},
new Fin_GroupExtraCostExcelColumnSetting(){ columnIndex = 7, columnName="费用", columnWidth= 12},
new Fin_GroupExtraCostExcelColumnSetting(){ columnIndex = 8, columnName="汇率", columnWidth= 12},
new Fin_GroupExtraCostExcelColumnSetting(){ columnIndex = 9, columnName="人民币", columnWidth= 12},
new Fin_GroupExtraCostExcelColumnSetting(){ columnIndex = 10, columnName="备注信息", columnWidth= 24}
};
foreach (var col in colNameSettingList)
{
cells[2, col.columnIndex].PutValue(col.columnName);
cells[2, col.columnIndex].SetStyle(style_colName);
cells.SetColumnWidth(col.columnIndex, col.columnWidth);
}
cells.SetRowHeight(2, 25);
#endregion
#region 数据填充
Aspose.Cells.Style style_dataCol = workbook.GetNamedStyle("colName");
style_dataCol.Font.IsBold = false;
style_dataCol.Name = "dataCol";
Aspose.Cells.Style style_dataBlue = workbook.CreateStyle();
style_dataBlue.HorizontalAlignment = TextAlignmentType.Center;//文字居中
style_dataBlue.VerticalAlignment = TextAlignmentType.Center;
style_dataBlue.Font.Name = "微软雅黑";//文字字体
style_dataBlue.Font.Size = 12;//文字大小
style_dataBlue.IsLocked = false;//单元格解锁
style_dataBlue.Font.IsBold = false;//粗体
style_dataBlue.ForegroundColor = Color.FromArgb(189, 215, 238);
style_dataBlue.Pattern = BackgroundType.Solid;
style_dataBlue.Font.Color = Color.FromArgb(0, 0, 0);
style_dataBlue.Borders[Aspose.Cells.BorderType.LeftBorder].LineStyle = CellBorderType.Thin; //应用边界线 左边界线
style_dataBlue.Borders[Aspose.Cells.BorderType.RightBorder].LineStyle = CellBorderType.Thin; //应用边界线 右边界线
style_dataBlue.Borders[Aspose.Cells.BorderType.TopBorder].LineStyle = CellBorderType.Thin; //应用边界线 上边界线
style_dataBlue.Borders[Aspose.Cells.BorderType.BottomBorder].LineStyle = CellBorderType.Thin; //应用边界线 下边界线
Aspose.Cells.Style style_dataYellow = workbook.CreateStyle();
style_dataYellow.HorizontalAlignment = TextAlignmentType.Center;//文字居中
style_dataYellow.VerticalAlignment = TextAlignmentType.Center;
style_dataYellow.Font.Name = "微软雅黑";//文字字体
style_dataYellow.Font.Size = 12;//文字大小
style_dataYellow.IsLocked = false;//单元格解锁
style_dataYellow.Font.IsBold = false;//粗体
style_dataYellow.ForegroundColor = Color.FromArgb(255, 242, 204);
style_dataYellow.Pattern = BackgroundType.Solid;
style_dataYellow.Font.Color = Color.FromArgb(0, 0, 0);
style_dataYellow.Borders[Aspose.Cells.BorderType.LeftBorder].LineStyle = CellBorderType.Thin; //应用边界线 左边界线
style_dataYellow.Borders[Aspose.Cells.BorderType.RightBorder].LineStyle = CellBorderType.Thin; //应用边界线 右边界线
style_dataYellow.Borders[Aspose.Cells.BorderType.TopBorder].LineStyle = CellBorderType.Thin; //应用边界线 上边界线
style_dataYellow.Borders[Aspose.Cells.BorderType.BottomBorder].LineStyle = CellBorderType.Thin; //应用边界线 下边界线
int rowIndex = 4;
foreach (var d in dataList)
{
//内容
string typeStr = d.PriceDetailTypeStr + d.PriceName;
cells["B" + rowIndex.ToString()].PutValue(d.PriceTypeStr);
cells["C" + rowIndex.ToString()].PutValue(d.PriceDtStr);
cells["D" + rowIndex.ToString()].PutValue(typeStr);
cells["E" + rowIndex.ToString()].PutValue(d.Price.ToString("#0.00"));
cells["F" + rowIndex.ToString()].PutValue(d.PriceCount);
cells["G" + rowIndex.ToString()].PutValue(d.Currency);
cells["H" + rowIndex.ToString()].PutValue(d.PriceSum.ToString("#0.00"));
cells["K" + rowIndex.ToString()].PutValue(d.Remark);
//样式
cells["B" + rowIndex.ToString()].SetStyle(style_dataCol);
cells["C" + rowIndex.ToString()].SetStyle(style_dataCol);
cells["D" + rowIndex.ToString()].SetStyle(style_dataCol);
cells["E" + rowIndex.ToString()].SetStyle(style_dataBlue);
cells["F" + rowIndex.ToString()].SetStyle(style_dataBlue);
cells["G" + rowIndex.ToString()].SetStyle(style_dataBlue);
cells["H" + rowIndex.ToString()].SetStyle(style_dataBlue);
cells["I" + rowIndex.ToString()].SetStyle(style_dataBlue);
cells["J" + rowIndex.ToString()].SetStyle(style_dataBlue);
cells["K" + rowIndex.ToString()].SetStyle(style_dataYellow);
//公式
cells["H" + rowIndex.ToString()].Formula = string.Format(@"E{0}*F{0}", rowIndex);
cells["J" + rowIndex.ToString()].Formula = string.Format(@"H{0}*I{0}", rowIndex);
cells.SetRowHeight(rowIndex - 1, 25);
rowIndex++;
}
cells["B" + rowIndex.ToString()].SetStyle(style_dataCol);
cells["C" + rowIndex.ToString()].SetStyle(style_dataCol);
cells["D" + rowIndex.ToString()].SetStyle(style_dataCol);
cells["E" + rowIndex.ToString()].SetStyle(style_dataBlue);
cells["F" + rowIndex.ToString()].SetStyle(style_dataBlue);
cells["G" + rowIndex.ToString()].SetStyle(style_dataBlue);
cells["H" + rowIndex.ToString()].SetStyle(style_dataBlue);
cells["I" + rowIndex.ToString()].SetStyle(style_dataBlue);
cells["J" + rowIndex.ToString()].SetStyle(style_dataBlue);
cells["K" + rowIndex.ToString()].SetStyle(style_dataYellow);
rowIndex++;
cells["B" + rowIndex.ToString()].SetStyle(style_dataCol);
cells["C" + rowIndex.ToString()].SetStyle(style_dataCol);
cells["D" + rowIndex.ToString()].SetStyle(style_dataCol);
cells["E" + rowIndex.ToString()].SetStyle(style_dataBlue);
cells["F" + rowIndex.ToString()].SetStyle(style_dataBlue);
cells["G" + rowIndex.ToString()].SetStyle(style_dataBlue);
cells["H" + rowIndex.ToString()].SetStyle(style_dataBlue);
cells["I" + rowIndex.ToString()].SetStyle(style_dataBlue);
cells["J" + rowIndex.ToString()].SetStyle(style_dataBlue);
cells["K" + rowIndex.ToString()].SetStyle(style_dataYellow);
rowIndex = 3;
int tempPriceType = 0;
foreach (var c in dataList)
{
if (tempPriceType == c.PriceType)
{
continue;
}
tempPriceType = c.PriceType;
int _rowCount = countList.First(s => s.PriceType == tempPriceType).DataCount;
cells.Merge(rowIndex, 1, _rowCount, 1);
Aspose.Cells.Range tempRange = cells.CreateRange(rowIndex, 1, _rowCount, 1);
rowIndex += _rowCount;
}
rowIndex = 4 + dataList.Count;
cells["D" + rowIndex.ToString()].PutValue("合计");
cells["J" + rowIndex.ToString()].Formula = string.Format(@"SUM(J4,J{0})", rowIndex - 1);
cells.SetRowHeight(rowIndex - 1, 25);
rowIndex++;
cells["D" + rowIndex.ToString()].PutValue("服务费10%开票税金8%");
cells["J" + rowIndex.ToString()].Formula = string.Format(@"J{0}*1.1*1.08", rowIndex - 1);
cells.SetRowHeight(rowIndex - 1, 25);
#endregion
#region IO
System.IO.MemoryStream ms = workbook.SaveToStream();//生成数据流
string fileName = ("GroupExtraCost/超支费用(" + cellValue_Header + ").xlsx");
byte[] bt = ms.ToArray();
workbook.Save(AppSettingsHelper.Get("ExcelBasePath") + fileName);
#endregion
string rst = AppSettingsHelper.Get("ExcelBaseUrl") + AppSettingsHelper.Get("ExcelFtpPath") + fileName;
return Ok(JsonView(true, "成功", new { url = rst }));
}
/// <summary>
/// 超支费用Excel导出
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
[HttpPost]
public IActionResult ExportOverspendExcel(Fin_GroupExtraCostExcelDto dto)
{
var jw = JsonView(false);
var grp_DelegationInfo = _sqlSugar.Queryable<Grp_DelegationInfo>().First(x => x.Id == dto.diId && x.IsDel == 0);
if (grp_DelegationInfo == null)
{
return Ok(JsonView(false, "团组信息有误！"));
}
Dictionary<int, int> dicSoure = new Dictionary<int, int>()
{
{1077,1074 },
{1078,1075 },
{1079,1076 }
};
Dictionary<int, int> mealsDic = new Dictionary<int, int>()
{
{988,1074},
{93,1075},
{989,1076},
};
var checkBoxDataArr = _sqlSugar.Queryable<Sys_SetData>().Where(x => x.IsDel == 0 && x.STid == 17).ToList();
var CurrDataArr = _sqlSugar.Queryable<Sys_SetData>().Where(x => x.IsDel == 0 && x.STid == 66).ToList();
var joinTable = _sqlSugar.Queryable<Fin_GroupExtraCost, Grp_CreditCardPayment, Sys_SetData, Grp_NationalTravelFee,
Fin_LocalGuideRelevancyOverspend, Grp_CarTouristGuideGroundReservations, Sys_SetData, Grp_DelegationInfo>(
(f, c, s, g, r, l, s1, i) => new JoinQueryInfos(
JoinType.Left, f.Id == c.CId && c.CTable == 1015 && c.IsDel == 0,
JoinType.Left, c.PaymentCurrency == s.Id && s.IsDel == 0,
JoinType.Left, g.IsDel == 0 && g.Id == f.Area,
JoinType.Left, f.Id == r.OverspendId && r.IsDel == 0,
JoinType.Left, l.Id == r.LocalGuideId && l.IsDel == 0,
JoinType.Left, s1.Id == l.toCurr && s1.IsDel == 0,
JoinType.Left, i.Id == l.DiId && i.IsDel == 0
))
.Where((f, c, s, g) => f.IsDel == 0 && f.DiId == dto.diId)
.Select((f, c, s, g, r, l, s1, i) => new
{
c.PaymentCurrency,
c.DayRate,
PaymentCurrencyStr = s.Name,
f.PriceName,
f.Remark,
f.PriceCount,
Price = f.Price * f.Coefficient,
c.PayMoney,
RMBPrice = c.RMBPrice * f.Coefficient,
Offer = c.RMBPrice * f.PriceCount * f.Coefficient,
c.CTable,
f.PriceDetailType,
Area = g.Country ?? "未选择地区！",
l.toCurr,
l.Rate,
toCurrStr = s1.Name,
g.FoodCost,
CarParentId = l.Id,
f.PriceDt,
i.VisitPNumber,
f.DiId,
f.PriceCurrency,
f.SupervisorConfirm,
f.ManagerConfirm,
f.SYsupervisorConfirm,
})
.ToList()
.Where(x=> {
var count = 0;
var stringArr = new string[] { "SYsupervisorConfirm", "SupervisorConfirm", "ManagerConfirm" };
foreach (var item in stringArr)
{
var number = x.GetType()?.GetProperty(item)?.GetValue(x).ObjToInt();
if (number > 0)
{
count++;
}
}
return count > 1;
})
.Select(x =>
{
var costPirce = x.Price;
decimal costRMBPrice = x.RMBPrice;
decimal costDayRate = x.DayRate;
string remake = x.Remark;
if (x.VisitPNumber > 0 && x.PriceDetailType != 1088)
{
decimal.TryParse(x.FoodCost, out decimal cost);
cost /= 2;
decimal oldPrice = costPirce;
costPirce *= x.Rate; //转换币种
if (x.toCurr != x.PaymentCurrency)
{
var rate = _setDataRep.PostCurrencyByDiid(x.DiId, 1015, x.toCurr).Result;
if (rate.Code == 0)
{
var rateData = rate.Data as CurrencyInfo;
if (rateData != null)
{
costDayRate = rateData.Rate;
}
}
}
if (dicSoure.Keys.Contains(x.PriceDetailType))
{
var carCenterList = _sqlSugar.Queryable<Grp_CarTouristGuideGroundReservationsContent>().Where(x1 => x1.CTGGRId == x.CarParentId && x1.IsDel == 0
&& x1.SId == mealsDic.First(x2 => x2.Value == dicSoure[x.PriceDetailType]).Key).ToList();
var CarCenter = carCenterList.FirstOrDefault(x1 => (DateTime.Compare(x1.DatePrice.ObjToDate(), x.PriceDt) == 0)) ?? new
Grp_CarTouristGuideGroundReservationsContent();
costPirce += (CarCenter.Price * CarCenter.Count * x.Rate);
oldPrice += (CarCenter.Price * CarCenter.Count);
var costResultPirce = costPirce - x.VisitPNumber * cost;
costResultPirce = Convert.ToInt32(Math.Round(costResultPirce));
var findCheck = checkBoxDataArr.Find(s => s.Id == CarCenter.SId);
if (x.toCurr != x.PaymentCurrency)
{
remake += $" {x.PriceDt.ToString("M/d")} {findCheck?.Name} {x.PaymentCurrencyStr} {(int)Math.Round(oldPrice)}（ 折算{x.toCurrStr} {(int)Math.Round(costPirce)}） - 财政{cost} * {x.VisitPNumber} = {x.toCurrStr}{costResultPirce}";
}
else
{
remake += $" {x.PriceDt.ToString("M/d")} {findCheck?.Name}{x.PaymentCurrencyStr} {(int)Math.Round(costPirce)} - 财政{cost} * {x.VisitPNumber} = {x.PaymentCurrencyStr}{costResultPirce}";
}
costPirce = costResultPirce;
}
else
{
costPirce = Convert.ToInt32(Math.Round(costPirce));
}
costRMBPrice = Convert.ToInt32(Math.Round(costPirce * costDayRate));
}
return new
{
x.PaymentCurrency,
DayRate = costDayRate,
x.PaymentCurrencyStr,
x.PriceName,
Remark = remake,
x.PriceCount,
Price = costPirce,
x.PayMoney,
RMBPrice = costRMBPrice,
Offer = costRMBPrice * x.PriceCount,
x.CTable,
x.PriceDetailType,
x.Area,
x.toCurr,
x.toCurrStr,
ItemPrice = costPirce * x.PriceCount,
};
})
.ToList();
var PriceDetailTypeArr = new int[] { 1088, 1074, 1075, 1076 };
var whereArr = joinTable.Where(x => PriceDetailTypeArr.Contains(x.PriceDetailType)).ToList();
_ = joinTable.RemoveAll(x => PriceDetailTypeArr.Contains(x.PriceDetailType));
foreach (var item in whereArr)
{
joinTable.Insert(0, item);
}
var dicClounm = new Dictionary<int, string>()
{
{ 0,"Area" },
{ 1,"PriceName" },
{ 2,"Remark" },
{ 3,"PriceCount" },
{ 4,"Price" },
{ 5,"ItemPrice" },
{ 6,"DayRate" },
{ 7,"Offer" },
};
var rowStartIndex = 2;
string filePath = AppSettingsHelper.Get("ExcelBasePath") + "\\Template\\超支费用表.xlsx";
IWorkbook workbook = new XSSFWorkbook(new FileStream(filePath, FileMode.Open, FileAccess.Read));
ISheet sheet = workbook.GetSheetAt(0);
var initStyleRow = sheet.GetRow(2);
var clounmCount = dicClounm.Count;
Action cloneRowFn = () =>
{
rowStartIndex++;
var cloneRow = sheet.CreateRow(rowStartIndex);
// 复制样式
for (int i = initStyleRow.FirstCellNum; i < initStyleRow.LastCellNum; i++)
{
ICell sourceCell = initStyleRow.GetCell(i);
ICell targetCell = cloneRow.GetCell(i) ?? cloneRow.CreateCell(i);
// 确保单元格存在样式
if (sourceCell.CellStyle != null)
{
targetCell.CellStyle = sourceCell.CellStyle;
}
}
};
sheet.GetRow(0).GetCell(0).SetCellValue($"{grp_DelegationInfo.TeamName}—超支费用");
foreach (var item in joinTable)
{
for (int i = 0; i < clounmCount; i++)
{
string property = dicClounm[i];
string value = item.GetType()!.GetProperty(property)!.GetValue(item)?.ToString() ?? "";
sheet.GetRow(rowStartIndex).GetCell(i).SetCellValue(value);
}
cloneRowFn();
}
sheet.GetRow(rowStartIndex).GetCell(2).SetCellValue($"小计:");
sheet.GetRow(rowStartIndex).GetCell(3).SetCellValue($"{joinTable.Sum(x => x.RMBPrice)}元");
var path = $"GroupExtraCost/{grp_DelegationInfo.TeamName}_超支费用.xlsx";
using (var stream = new MemoryStream())
{
workbook.Write(stream, true);
stream.Flush();
stream.Seek(0, SeekOrigin.Begin);
var pathinfo = AppSettingsHelper.Get("ExcelBasePath") + path;
if (System.IO.File.Exists(pathinfo))
{
System.IO.File.Delete(pathinfo);
}
stream.SaveToFile(AppSettingsHelper.Get("ExcelBasePath") + path);
}
string rst = AppSettingsHelper.Get("ExcelBaseUrl") + AppSettingsHelper.Get("ExcelFtpPath") + path;
jw = JsonView(true, "成功", new { url = rst });
return Ok(jw);
}
/// <summary>
/// 获取超支系数配置
/// </summary>
/// <returns></returns>
[HttpPost]
public IActionResult QueryCoefficientConfig()
{
var arr = _sqlSugar.Queryable<Sys_SetData>().Where(x => x.STid == 80 && x.IsDel == 0).ToList();
var carCoefficient = new int[] { 1050 }; //2.4
var menpiaoCoefficient = new int[] { 1086 }; // 1.2
var airCoefficient = new int[] { 1035, 1036 }; // 2.0
var HotelCoefficient = new int[] { 1044, 1045, 1046, 1041, 1042, 1043, 1038 }; // 1.5
var fanyiCoefficient = new int[] { 1087 }; // 1.5
var resultArr = new
ArrayList();
foreach (var item in arr)
{
var a = new { coefficient = 1M, id = 0 };
if (carCoefficient.Contains(item.Id))
{
a = a with
{
coefficient = 2.4M,
id = item.Id
};
}
else if (menpiaoCoefficient.Contains(item.Id))
{
a = a with
{
coefficient = 1.2M,
id = item.Id
};
}
else if (airCoefficient.Contains(item.Id))
{
a = a with
{
coefficient = 2.0M,
id = item.Id
};
}
else if (HotelCoefficient.Contains(item.Id))
{
a = a with
{
coefficient = 1.5M,
id = item.Id
};
}
else if (fanyiCoefficient.Contains(item.Id))
{
a = a with
{
coefficient = 1.5M,
id = item.Id
};
}
else
{
a = a with
{
coefficient = 1M,
id = item.Id
};
}
resultArr.Add(a);
}
return Ok(JsonView(true, "获取成功！", resultArr));
}
/// <summary>
/// 退费确认
/// </summary>
/// <returns></returns>
[HttpPost]
public IActionResult ReturnPremiumConfirm(ReturnPremiumConfirmDto dto)
{
var jw = JsonView(false);
var confirmStatusArr = new Dictionary<int, string>()
{
{1 ,"ManagerConfirm" },
{2 ,"SupervisorConfirm" },
{3 ,"SYsupervisorConfirm" },
};
if (dto.DataId < 1 || confirmStatusArr.Keys.Contains(dto.ConfirmId) == false)
{
jw.Msg = "参数有误！";
return Ok(jw);
}
Fin_GroupExtraCost fge = _sqlSugar.Queryable<Fin_GroupExtraCost>().First(it => it.Id == dto.DataId && it.IsDel == 0);
try
{
if (fge == null)
throw new Exception("数据不存在！");
fge.GetType().GetProperty(confirmStatusArr[dto.ConfirmId]).SetValue(fge, dto.status);
var isSaveCollectionStatement = true;
//确认后，同步修改收款账单
foreach (var item in confirmStatusArr.Keys)
{
var value = fge.GetType().GetProperty(confirmStatusArr[item]).GetValue(fge).ObjToInt();
//if (value == 0)
//{
// isSaveCollectionStatement = false;
// break;
//}
}
_sqlSugar.BeginTran();
_sqlSugar.Updateable<Fin_GroupExtraCost>(fge).ExecuteCommand();
if (isSaveCollectionStatement)
{
//同步修改收款账单
_foreignReceivablesRepository.OverSpSeteceivables(new OverSpSeteceivablesDto
{
CreateUserId = fge.CreateUserId,
DiId = fge.DiId,
});
}
}
catch (Exception ex)
{
_sqlSugar.RollbackTran();
jw.Msg = "Error！ " + ex.Message;
return Ok(jw);
}
_sqlSugar.CommitTran();
jw = JsonView(true, "操作成功！");
return Ok(jw);
}
#endregion
#region 信用卡对账
/// <summary>
///将指定的Excel的文件转换成DataTable（Excel的第一个sheet）
/// </summary>
/// <param name="fullFielPath">文件的绝对路径</param>
/// <returns></returns>
private DataTable WorksheetToTable(string fullFielPath, string sheetName = null)
{
//如果是“EPPlus”，需要指定LicenseContext。
//EPPlus.Core 不需要指定。
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
FileInfo existingFile = new FileInfo(fullFielPath);
ExcelPackage package = new ExcelPackage(existingFile);
ExcelWorksheet worksheet = null;
if (string.IsNullOrEmpty(sheetName))
{
//不传入 sheetName 默认取第1个sheet。
//EPPlus 索引是0
//EPPlus.Core 索引是1
worksheet = package.Workbook.Worksheets[0];
}
else
{
worksheet = package.Workbook.Worksheets[sheetName];
}
if (worksheet == null)
throw new Exception("指定的sheetName不存在");
return WorksheetToTable(worksheet);
}
/// <summary>
/// 将worksheet转成datatable
/// </summary>
/// <param name="worksheet">待处理的worksheet</param>
/// <returns>返回处理后的datatable</returns>
private DataTable WorksheetToTable(ExcelWorksheet worksheet)
{
//获取worksheet的行数
int rows = worksheet.Dimension.End.Row;
//获取worksheet的列数
int cols = worksheet.Dimension.End.Column;
DataTable dt = new DataTable(worksheet.Name);
DataRow dr = null;
for (int i = 1; i <= rows; i++)
{
if (i > 1)
dr = dt.Rows.Add();
for (int j = 1; j <= cols; j++)
{
//默认将第一行设置为datatable的标题
if (i == 1)
dt.Columns.Add(GetString(worksheet.Cells[i, j].Value));
//剩下的写入datatable
else
{
var cell = worksheet.Cells[i, j];
if (j == 1 || j == 2)
{
if (cell.Value is double)
{
dr[j - 1] = EPPlusConvertToDateTime((double)cell.Value).ToString("MM/dd");
}
}
else
{
dr[j - 1] = GetString(cell.Value);
}
}
}
}
return dt;
}
private string GetString(object obj)
{
if (obj == null)
return "";
return obj.ToString();
}
public static DateTime EPPlusConvertToDateTime(double numericDate)
{
// Excel起始日期
DateTime startDate = new DateTime(1899, 12, 30);
// 将数字日期转换为时间间隔，并加上起始日期
TimeSpan duration = new TimeSpan((long)(numericDate * TimeSpan.TicksPerDay));
return startDate.Add(duration);
}
private DataTable ExcelFileToDataTable(IFormFile file)
{
DataTable dtTest = null;
using (var stream = new MemoryStream())
{
file.CopyTo(stream);
using (var package = new ExcelPackage(stream))
{
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
ExcelWorksheet worksheet = package.Workbook.Worksheets[0];
dtTest = WorksheetToTable(worksheet);
}
}
return dtTest;
}
/// <summary>
/// 信用卡对账--基础数据
/// </summary>
/// <param name="file"></param>
/// <param name="cardType"></param>
/// <param name="beginDt"></param>
/// <param name="endDt"></param>
/// <returns></returns>
[HttpPost]
[ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
public async Task<IActionResult> PostCreditCardBillInit()
{
var data = await _sqlSugar.Queryable<Sys_SetData>().Where(x => x.IsDel == 0 && x.STid == 15).Select(x => new { x.Id,x.Name}).ToListAsync();
return Ok(JsonView(true, "操作成功!", data));
}
/// <summary>
/// 信用卡对账
/// </summary>
/// <param name="file"></param>
/// <param name="cardType"></param>
/// <param name="beginDt"></param>
/// <param name="endDt"></param>
/// <returns></returns>
[HttpPost]
[ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
public async Task<IActionResult> PostCreditCardBill(IFormFile file, int cardType, string beginDt, string endDt)
{
if (file == null || file.Length < 1)
{
return Ok(JsonView(false, "请上传文件!"));
}
string fileName = file.FileName;
if (!fileName.EndsWith(".xlsx") && !fileName.EndsWith(".xls"))
{
return Ok(JsonView(false, "请上传Excel文件!"));
}
if (cardType < 1) return Ok(JsonView(false, "请传入有效的卡类型!"));
if (string.IsNullOrEmpty(beginDt) || string.IsNullOrEmpty(endDt)) return Ok(JsonView(false, "请输入开始/结束日期!"));
var beginValid = DateTime.TryParse(beginDt, out DateTime _beginDt);
var endValid = DateTime.TryParse(endDt, out DateTime _endDt);
if (!beginValid || !endValid) return Ok(JsonView(false, "请输入正确的日期格式"));
//读取ExcelFile
DataTable dt = ExcelFileToDataTable(file);
if (dt == null)
{
return Ok(JsonView(false, $"您上传的Excel工作表没有内容，请检查！！！"));
}
dt.TableName = "TB";
//信用卡信息
string sql = string.Format($"Select * From Grp_CreditCardPayment Where Isdel = 0 And CTDId = {cardType} And ConsumptionDate between '{beginDt}' and '{endDt}' ");
var List_ccp = await _sqlSugar.SqlQueryable<Grp_CreditCardPayment>(sql).ToListAsync();
if (List_ccp.Count < 1)
{
return Ok(JsonView(false, $"未查询到 {beginDt} 至 {endDt} 信用卡账单信息！！！!"));
}
//酒店刷卡信息
var hotelData = await _sqlSugar.Queryable<Grp_HotelReservations>()
.LeftJoin<Grp_HotelReservationsContent>((hr, hrc) => hr.Id == hrc.HrId)
.Where((hr, hrc) => hr.IsDel == 0 && hrc.Price > 0 && hrc.IsPay == 1)
.Select((hr, hrc) => new
{
hrc.DiId,
hrc.HrId,
hrc.Id,
hrc.PriceType,
hrc.Price,
hrc.Currency,
hrc.Rate,
hrc.IsOppay,
hrc.PayDId,
hrc.ConsumptionPatterns,
hrc.ConsumptionDate,
ConsumptionDt = string.IsNullOrEmpty(hrc.ConsumptionDate) ? new DateTime(1990, 12, 30) : Convert.ToDateTime(hrc.ConsumptionDate),
hrc.CTDId,
hrc.BankNo,
hrc.CardholderName,
hrc.CompanyBankNo,
hrc.OtherBankName,
hrc.OtherSideNo,
hrc.IsPay,
hrc.Payee,
hrc.OrbitalPrivateTransfer
})
.ToListAsync();
if (hotelData.Count > 0)
{
hotelData = hotelData.Where(x => x.ConsumptionDt >= _beginDt && x.ConsumptionDt <= _endDt).ToList();
}
//资源信息
var delegationInfos = _sqlSugar.Queryable<Grp_DelegationInfo>().Where(it => it.IsDel == 0).ToList();
var users = _sqlSugar.Queryable<Sys_Users>().Where(it => it.IsDel == 0).ToList();
string url = string.Empty;
try
{
/*
* 卡类型
* 74 美元卡 1
* 75 欧元卡 1
* 86 招行卡 1
* 346 中信卡 1
* 363 交行卡 1
*
*/
var ids = new List<int>();
if (cardType == 74) //美元卡
{
var AirGroupReuslt = List_ccp.Where(x => x.CTable == 85).GroupBy(x => x.DIId).ToList();
if (AirGroupReuslt != null && AirGroupReuslt.Count > 0)
{
foreach (var item in AirGroupReuslt)
{
if (item.Count() > 1)
{
var obj = item.FirstOrDefault();
obj.RMBPrice = item.Sum(x => x.PayMoney);//合计币种金额
obj.Remark = "团组机票总价(虚拟)";
List_ccp.Add(obj);
}
}
}
//dt.AcceptChanges(); //提交
//修改table列名
dt.Columns[0].ColumnName = "accountType"; // 账户类型
dt.Columns[1].ColumnName = "tradeDate"; //交易日期
dt.Columns[2].ColumnName = "BillingDate"; // 记账日期
dt.Columns[3].ColumnName = "CardNo"; // 卡号
dt.Columns[4].ColumnName = "deposit"; // 存入金额
dt.Columns[5].ColumnName = "SpendingAmount"; // 支出金额
dt.Columns[6].ColumnName = "TransactionDescription"; // 交易描述
dt.Columns[7].ColumnName = "TeamRemark"; // 团组备注描述
dt.Columns[8].ColumnName = "Handlers"; // 经手人
dt.Columns[9].ColumnName = "State"; // 状态
//删除第一行数据
if (dt.Rows.Count > 0)
{
string accountType = dt.Rows[0]["accountType"].ToString();
if (dt.Rows[0]["accountType"].ToString().Equals("账户类型"))
{
dt.Rows[0].Delete();
dt.AcceptChanges(); //提交
}
}
foreach (DataRow item in dt.Rows)
{
#region 匹配的金额
decimal ExcelAmount = 0.00M;
decimal deposit = 0.00M;
if (!string.IsNullOrEmpty(item["SpendingAmount"].ToString()))
{
var isParase = decimal.TryParse(item["SpendingAmount"].ToString(), out ExcelAmount);
if (isParase)
{
var CList = List_ccp.FindAll(x => x.PayMoney == ExcelAmount);
if (CList != null && CList.Count > 0)
{
item["TeamRemark"] = delegationInfos.Find(it => it.Id == CList.First().DIId)?.TeamName;
item["Handlers"] = users.Find(it => it.Id == CList.First().CreateUserId)?.CnName;
item["State"] = 1;
}
}
}
if (!string.IsNullOrEmpty(item["deposit"].ToString()))
{
var isParse = decimal.TryParse(item["deposit"].ToString(), out deposit);
if (isParse)
{
var CList = List_ccp.FindAll(x => x.PayMoney == deposit);
if (CList != null && CList.Count > 0)
{
item["TeamRemark"] = delegationInfos.Find(it => it.Id == CList.First().DIId)?.TeamName;
item["Handlers"] = users.Find(it => it.Id == CList.First().CreateUserId)?.CnName;
item["State"] = 1;
}
}
}
#endregion
//交易描述
if (!string.IsNullOrEmpty(item["TransactionDescription"].ToString()))
{
string TransactionDescription = item["TransactionDescription"].ToString();
var startIndex = TransactionDescription.LastIndexOf("[");
var endIndex = TransactionDescription.LastIndexOf("]");
if (startIndex != -1 && endIndex != -1)
{
var moenyList = TransactionDescription.Substring(startIndex + 1, endIndex - startIndex - 1).Split(' ').
Where(x => !string.IsNullOrEmpty(x)).ToList();
decimal money = 0.00M;
foreach (var itemMoeny in moenyList)
{
if (itemMoeny.Contains('.'))
{
string itemMoenyStr = itemMoeny.Replace(",", string.Empty);
bool istrue = decimal.TryParse(itemMoenyStr, out money);
if (istrue)
{
var CList = List_ccp.FindAll(x => x.PayMoney == money);
if (CList != null && CList.Count > 0)
{
item["TeamRemark"] = delegationInfos.Find(it => it.Id == CList.First().DIId)?.TeamName;
item["Handlers"] = users.Find(it => it.Id == CList.First().CreateUserId)?.CnName;
item["State"] = 1;
}
}
}
}
}
}
}
string fileName1 = $"信用卡账单（美元卡）{DateTime.Now.ToString("yyyyMMddHHmmss")}.xls";
Dictionary<string, object> pairs = new Dictionary<string, object>();
List<DataTable> datas = new List<DataTable>();
datas.Add(dt);
url = AsposeHelper.ExpertExcelToModel("信用卡对账模板-美元卡.xls", $"CreditCardBill", fileName1, pairs, datas);
}
else if (cardType == 75) //欧元卡
{
if (dt.Rows.Count > 0)
{
string accountType = dt.Rows[0][0].ToString();
if (accountType.Equals("账户类型"))
{
dt.Rows[0].Delete();
dt.AcceptChanges(); //提交
}
}
//dt.Columns[0].ColumnName = "All";
DataTable dt1 = new DataTable();
dt1.TableName = "TB";
dt1.Columns.Add("TradingDate", Type.GetType("System.String"));//交易日期
dt1.Columns.Add("TallyDate", Type.GetType("System.String")); //记账日期
dt1.Columns.Add("TradindDesc", Type.GetType("System.String"));//交易描述
dt1.Columns.Add("TradindMoney", Type.GetType("System.String"));//交易币种/金额
dt1.Columns.Add("ClearindMoney", Type.GetType("System.String"));// 清算币种/金额
dt1.Columns.Add("TeamRemark", Type.GetType("System.String")); //团组备注描述
dt1.Columns.Add("Handlers", Type.GetType("System.String")); //经手人
dt1.Columns.Add("State", Type.GetType("System.String")); //状态
for (int i = 0; i < dt.Rows.Count; i++)
{
string[] str = dt.Rows[i]["All"].ToString().Split(' ');
//string[] str = dt.Rows[i];
DataRow dr = dt1.NewRow();
dr["TradingDate"] = str[0].ToString();
dr["TallyDate"] = str[1].ToString();
dr["TradindDesc"] = str[2].ToString() + " " + str[3].ToString();
dr["TradindMoney"] = str[4].ToString() + " " + str[5].ToString();
dr["ClearindMoney"] = str[6].ToString() + " " + str[7].ToString();
dt1.Rows.Add(dr);
}
foreach (DataRow item in dt1.Rows)
{
for (int i = 0; i < List_ccp.Count; i++)
{
DateTime dtTime1 = Convert.ToDateTime(item["TradingDate"].ToString() == "" ? DateTime.Now.ToString("MM/dd") : item["TradingDate"].ToString()); //交易日期
string USDPrice = item["TradindMoney"].ToString(); //支出金额
USDPrice = USDPrice.Split(' ')[1].Trim(); //替换掉非数字
//USDPrice = USDPrice.Split('/')[1];
// 经手人 and excel行状态
if (!string.IsNullOrEmpty(List_ccp[i].ConsumptionDate) && !string.IsNullOrEmpty(USDPrice))
{
DateTime dtTime2 = Convert.ToDateTime(List_ccp[i].ConsumptionDate.ToString() == "" ? DateTime.Now.ToString("MM/dd") : List_ccp[i].ConsumptionDate.ToString());
float price1 = float.Parse(USDPrice);
float price2 = float.Parse(List_ccp[i].PayMoney.ToString("F2"));
if (dtTime1 == dtTime2 && price1 == price2)
{
item["TeamRemark"] = delegationInfos.Find(it => it.Id == List_ccp[i].DIId)?.TeamName;
item["Handlers"] = users.Find(it => it.Id == List_ccp[i].CreateUserId)?.CnName;
item["State"] = "1";
i = List_ccp.Count - 1;
}
else item["State"] = "0";
}
else item["State"] = "0";
}
if (item["TradindDesc"].ToString().Contains("财付通"))
item["State"] = "2";
else if (item["TradindDesc"].ToString().Contains("京东"))
item["State"] = "2";
else if (item["TradindDesc"].ToString().Contains("微信支付"))
item["State"] = "2";
else if (item["TradindDesc"].ToString().Contains("淘宝"))
item["State"] = "2";
}
string fileName1 = $"信用卡账单（欧元卡）{DateTime.Now.ToString("yyyy.MM.dd")}.xls";
Dictionary<string, object> pairs = new Dictionary<string, object>();
List<DataTable> datas = new List<DataTable>();
datas.Add(dt);
url = AsposeHelper.ExpertExcelToModel("信用卡对账模板-欧元卡.xls", "TB", fileName1, pairs, datas);
}
else if (cardType == 86) //招行卡
{
var AirGroupReuslt1 = List_ccp.Where(x => x.CTable == 85).GroupBy(x => x.DIId).ToList();
if (AirGroupReuslt1 != null && AirGroupReuslt1.Count > 0)
{
foreach (var item in AirGroupReuslt1)
{
if (item.Count() > 1)
{
var di = delegationInfos.Find(it => it.Id == item.Key);
var obj = item.FirstOrDefault();
obj.Remark = "团组机票总价";
if (di != null)
{
obj.Remark = "团组机票总价(" + di.TeamName + ")";
}
obj.RMBPrice = item.Sum(x => x.RMBPrice);//合计人民币
List_ccp.Add(obj);
}
}
}
//查询所有刷卡的团组
//将刷卡团组内刷卡金额的金额存入ccp_list
//将excel作比较
//机票刷卡记录
var AirListAndPayID = List_ccp.Where(x => x.CTable == 85 && x.PayDId == 72).Select(x => x.DIId).Distinct().ToList();
var AirData = _sqlSugar.Queryable<Grp_AirTicketReservations>().Where(it => AirListAndPayID.Contains(it.DIId) && it.PrePrice != it.Price).ToList();
foreach (var item in AirData)
{
var DBwhere = List_ccp.Find(x => x.PayMoney == item.PrePrice);
if (DBwhere == null)
{
List_ccp.Add(new Grp_CreditCardPayment
{
PayMoney = item.PrePrice,
DIId = item.DIId,
CreateUserId = item.CreateUserId,
Remark = "(机票单价价格)",
});
}
}
//机票刷卡记录
var hotelGroupReuslt = hotelData.Where(x => x.CTDId == 86).GroupBy(x => x.DiId).ToList();
if (hotelGroupReuslt != null && hotelGroupReuslt.Count > 0)
{
foreach (var item in hotelGroupReuslt)
{
if (item.Count() > 0)
{
var hotelInfo = new Grp_CreditCardPayment()
{
Remark = $"团组酒店总价(" + delegationInfos.Find(it => it.Id == item.Key)?.TeamName ?? "" + ")",
RMBPrice = item.Sum(x => x.Price * x.Rate),
ConsumptionDate = item.FirstOrDefault()?.ConsumptionDate ?? ""
};
List_ccp.Add(hotelInfo);
}
}
}
#region
dt.Rows[0].Delete();//删除列名行
dt.AcceptChanges();
DataTable dt1 = new DataTable();
dt1.TableName = "TB";
dt1.Columns.Add("TradingDay", Type.GetType("System.String")); // 交易日
dt1.Columns.Add("TallyDay", Type.GetType("System.String")); // 记账日
dt1.Columns.Add("TransactionDesc", Type.GetType("System.String")); // 交易描述
dt1.Columns.Add("RMBMoney", Type.GetType("System.String")); // 人名币金额
dt1.Columns.Add("CardNo", Type.GetType("System.String")); // 卡号
dt1.Columns.Add("TradingMoney", Type.GetType("System.String")); // 交易地金额
dt1.Columns.Add("Currency", Type.GetType("System.String")); // 币种
dt1.Columns.Add("TeamRemark", Type.GetType("System.String")); //团组备注描述
dt1.Columns.Add("Handlers", Type.GetType("System.String")); //经手人
dt1.Columns.Add("State", Type.GetType("System.String")); //状态
for (int j = 0; j < dt.Rows.Count; j++)//遍历行
{
DataRow dr = dt1.NewRow(); //获取每一行的数据
var tradingDay = dt.Rows[j][0].ToString();
dr["TradingDay"] = tradingDay;
var tallyDay = dt.Rows[j][1].ToString();
dr["TallyDay"] = tallyDay;
dr["TransactionDesc"] = dt.Rows[j][2].ToString();
dr["RMBMoney"] = dt.Rows[j][3].ToString();
dr["CardNo"] = dt.Rows[j][4].ToString();
string currency = "CN";
string money = dt.Rows[j][5].ToString();
if (money.Contains('('))
{
string[] strs = money.Split('(');
money = strs[0];
currency = strs[1].Replace(")", "");
}
dr["TradingMoney"] = money;
dr["Currency"] = currency;
if (string.IsNullOrEmpty(money))
{
continue;
}
dt1.Rows.Add(dr);
}
//datatable 排序
dt1.DefaultView.Sort = "TradingDay asc";
dt1 = dt1.DefaultView.ToTable();
foreach (DataRow item in dt1.Rows)
{
if (item["TradingDay"] == "" && item["RMBMoney"] == "")
{
continue;
}
//excel获取交易日期
string ExcelDt = string.Empty;
//C表交易日期
string DBDt = string.Empty;
//excel交易的金额
decimal TradingMoney = decimal.Parse(item["TradingMoney"].ToString());
if (item["TradingDay"] != null)
{
ExcelDt = item["TradingDay"].ToString();
}
//加一获取金额区间
decimal MaxTradingMoney = TradingMoney + 1;
decimal MinTradingMoney = TradingMoney - 1;
//匹配C表金额相等的值
var CList = List_ccp.FindAll(x => x.PayMoney <= MaxTradingMoney && x.PayMoney >= MinTradingMoney);
if (CList != null && CList.Count > 0)
{
#region 日期匹配
foreach (var Citem in CList)
{
if (!string.IsNullOrWhiteSpace(Citem.ConsumptionDate))
{
DBDt = DateTime.Parse(Citem.ConsumptionDate).ToString("MM/dd");
if (DBDt.Equals(ExcelDt))
{
item["TeamRemark"] = delegationInfos.Find(it => it.Id == Citem.DIId)?.TeamName;
item["Handlers"] = users.Find(it => it.Id == Citem.CreateUserId)?.CnName;
ids.Add(Citem.Id);
}
}
}
#endregion
}
var whereRange = new List<string>() { "财付通", "京东", "微信", "淘宝", "支付宝", "拼多多" };
foreach (var where in whereRange)
{
if (item["TransactionDesc"].ToString().Contains(where)) item["State"] = "2";
else item["State"] = "0";
}
if (!string.IsNullOrEmpty(item["TeamRemark"].ToString()) && !string.IsNullOrEmpty(item["Handlers"].ToString()))
item["State"] = "1";
}
//更改匹配项状态
if (ids.Count > 0)
{
var _CreditCardPayments = new List<Grp_CreditCardPayment>();
foreach (var item in ids)
{
_CreditCardPayments.Add(new Grp_CreditCardPayment() { Id = item, IsMatchCreditCard = 1.00M });
}
var updateStatus = _sqlSugar.Updateable<Grp_CreditCardPayment>(_CreditCardPayments)
.UpdateColumns(it => new { it.IsMatchCreditCard })
.Where(it => ids.Contains(it.Id))
.ExecuteCommand();
}
string fileName1 = $"信用卡账单（招行卡）{DateTime.Now.ToString("yyyyMMddHHmmss")}.xls";
Dictionary<string, object> pairs = new Dictionary<string, object>();
List<DataTable> datas = new List<DataTable>();
datas.Add(dt1);
url = AsposeHelper.ExpertExcelToModel("信用卡对账模板-招行卡.xls", "CreditCardBill", fileName1, pairs, datas);
#endregion
}
else if (cardType == 346) //中信卡
{
dt.Columns.Add("TeamRemark", Type.GetType("System.String")); //团组备注描述
dt.Columns.Add("Handlers", Type.GetType("System.String")); //经手人
dt.Columns.Add("State", Type.GetType("System.String")); //状态
//修改table列名
dt.Columns[0].ColumnName = "TransactionDesc"; // 交易描述
dt.Columns[1].ColumnName = "TradingCurrencyAndMoney"; // 交易币种和金额
dt.Columns[2].ColumnName = "TransactionDate"; // 交易日期
dt.Columns[3].ColumnName = "SettlementCurrencyAndMoney";// 结算币种和金额
dt.Columns[4].ColumnName = "BookedDate"; // 入账日期
foreach (DataRow item in dt.Rows)
{
for (int i = 0; i < List_ccp.Count; i++)
{
if (string.IsNullOrEmpty(item["TransactionDate"].ToString()))
break;
DateTime dtTime1 = Convert.ToDateTime(item["TransactionDate"].ToString()); //交易日期
string USDPrice = item["TradingCurrencyAndMoney"].ToString(); //支出金额
USDPrice = USDPrice.Split('/')[1];
//string USDPrice1 = USDPrice.ToString("0.00");
// 经手人 and excel行状态
if (!string.IsNullOrEmpty(List_ccp[i].ConsumptionDate) && !string.IsNullOrEmpty(USDPrice))
{
DateTime dtTime2 = Convert.ToDateTime(List_ccp[i].ConsumptionDate);
float price1 = float.Parse(USDPrice);
float price2 = float.Parse(List_ccp[i].PayMoney.ToString("F2"));
if (dtTime1 == dtTime2 && price1 == price2)
{
item["TeamRemark"] = delegationInfos.Find(it => it.Id == List_ccp[i].DIId)?.TeamName;
item["Handlers"] = users.Find(it => it.Id == List_ccp[i].CreateUserId)?.CnName;
item["State"] = "1";
i = List_ccp.Count - 1;
}
else
item["State"] = "0";
}
else
item["State"] = "0";
}
//判断是否是数字
//if (IsNumber(item["TradingCurrencyAndMoney"].ToString().Split('/')[1]) == false)
// item["State"] = "0";
if (item["TransactionDesc"].ToString().Contains("财付通"))
item["State"] = "2";
else if (item["TransactionDesc"].ToString().Contains("京东"))
item["State"] = "2";
else if (item["TransactionDesc"].ToString().Contains("微信支付"))
item["State"] = "2";
else if (item["TransactionDesc"].ToString().Contains("淘宝"))
item["State"] = "2";
}
#region DownExcel
string fileName1 = $"信用卡账单（中信卡）{DateTime.Now.ToString("yyyy.MM.dd")}.xls";
Dictionary<string, object> pairs = new Dictionary<string, object>();
List<DataTable> datas = new List<DataTable>();
datas.Add(dt);
url = AsposeHelper.ExpertExcelToModel("信用卡对账模板-中信卡.xls", "TB", fileName1, pairs, datas);
#endregion
}
else if (cardType == 363) //交行卡
{
dt.Columns[0].ColumnName = "All";
DataTable dt1 = new DataTable();
dt1.Columns.Add("TradingDate", Type.GetType("System.String"));//交易日期
dt1.Columns.Add("TallyDate", Type.GetType("System.String")); //记账日期
dt1.Columns.Add("TradindDesc", Type.GetType("System.String"));//交易描述
dt1.Columns.Add("TradindMoney", Type.GetType("System.String"));//交易币种/金额
dt1.Columns.Add("ClearindMoney", Type.GetType("System.String"));// 清算币种/金额
dt1.Columns.Add("TeamRemark", Type.GetType("System.String")); //团组备注描述
dt1.Columns.Add("Handlers", Type.GetType("System.String")); //经手人
dt1.Columns.Add("State", Type.GetType("System.String")); //状态
for (int i = 0; i < dt.Rows.Count; i++)
{
string[] str = dt.Rows[i]["All"].ToString().Split(' ');
DataRow dr = dt1.NewRow();
dr["TradingDate"] = str[0].ToString();
dr["TallyDate"] = str[1].ToString();
dr["TradindDesc"] = str[2].ToString() + " " + str[3].ToString();
dr["TradindMoney"] = str[4].ToString() + " " + str[5].ToString();
dr["ClearindMoney"] = str[6].ToString() + " " + str[7].ToString();
dt1.Rows.Add(dr);
}
foreach (DataRow item in dt1.Rows)
{
for (int i = 0; i < List_ccp.Count; i++)
{
DateTime dtTime1 = Convert.ToDateTime(item["TradingDate"].ToString() == "" ? DateTime.Now.ToString("MM/dd") : item["TradingDate"].ToString()); //交易日期
string USDPrice = item["TradindMoney"].ToString(); //支出金额
USDPrice = USDPrice.Split(' ')[1].Trim(); //替换掉非数字
//USDPrice = USDPrice.Split('/')[1];
// 经手人 and excel行状态
if (!string.IsNullOrEmpty(List_ccp[i].ConsumptionDate) && !string.IsNullOrEmpty(USDPrice))
{
DateTime dtTime2 = Convert.ToDateTime(List_ccp[i].ConsumptionDate.ToString() == "" ? DateTime.Now.ToString("MM/dd") : List_ccp[i].ConsumptionDate.ToString());
float price1 = float.Parse(USDPrice);
float price2 = float.Parse(List_ccp[i].PayMoney.ToString("F2"));
if (dtTime1 == dtTime2 && price1 == price2)
{
item["TeamRemark"] = delegationInfos.Find(it => it.Id == List_ccp[i].DIId)?.TeamName;
item["Handlers"] = users.Find(it => it.Id == List_ccp[i].CreateUserId)?.CnName;
item["State"] = "1";
i = List_ccp.Count - 1;
}
else
item["State"] = "0";
}
else
item["State"] = "0";
}
if (item["TradindDesc"].ToString().Contains("财付通"))
item["State"] = "2";
else if (item["TradindDesc"].ToString().Contains("京东"))
item["State"] = "2";
else if (item["TradindDesc"].ToString().Contains("微信支付"))
item["State"] = "2";
else if (item["TradindDesc"].ToString().Contains("淘宝"))
item["State"] = "2";
}
string fileName1 = $"信用卡账单（交行卡）{DateTime.Now.ToString("yyyy.MM.dd")}.xls";
Dictionary<string, object> pairs = new Dictionary<string, object>();
List<DataTable> datas = new List<DataTable>();
datas.Add(dt);
url = AsposeHelper.ExpertExcelToModel("信用卡对账模板-交行卡.xls", "TB", fileName1, pairs, datas);
}
}
catch (Exception ex)
{
return Ok(JsonView(false, $"匹配失败，{ex.Message}!"));
}
return Ok(JsonView(true, "操作成功", new { url = url }));
}
/// <summary>
/// 把数据从Excel装载到DataTable
/// </summary>
/// <param name="pathName">带路径的Excel文件名</param>
/// <param name="sheetName">工作表名</param>
/// <param name="tbContainer">将数据存入的DataTable</param>
/// <returns></returns>
public static DataTable ExcelToDataTable(string pathName, string sheetName="")
{
DataTable tbContainer = new DataTable();
string strConn = string.Empty;
if (string.IsNullOrEmpty(sheetName)) { sheetName = "Sheet1"; }
FileInfo file = new FileInfo(pathName);
if (!file.Exists) { throw new Exception("文件不存在"); }
string extension = file.Extension;
switch (extension)
{
case ".xls":
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathName + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";
break;
case ".xlsx":
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + pathName + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1;'";
break;
default:
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathName + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";
break;
}
//链接Excel
OleDbConnection cnnxls = new OleDbConnection(strConn);
//读取Excel里面有 表Sheet1
System.Data.OleDb.OleDbDataAdapter oda = new System.Data.OleDb.OleDbDataAdapter(string.Format("select * from [{0}$]", sheetName), cnnxls);
DataSet ds = new DataSet();
//将Excel里面有表内容装载到内存表中！
oda.Fill(tbContainer);
return tbContainer;
}
#endregion
#region 提成计算
/// <summary>
/// 提成计算
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
[HttpPost]
public IActionResult ComputeRoyalties(ComputeRoyaltiesDto dto)
{
var jw = JsonView(false);
var userId = dto.UserId;
var user = _sqlSugar.Queryable<Sys_Users>().First(x => x.Id == userId);
//jw.Data = System.Array.Empty<string>();
if (user == null)
{
jw.Msg = "暂无该用户！";
return Ok(jw);
}
if (!DateTime.TryParse(dto.StartDate, out DateTime StartDateTime))
{
jw.Msg = "开始时间格式有误！";
return Ok(jw);
}
if (!DateTime.TryParse(dto.EndDate, out DateTime EndDateTime))
{
jw.Msg = "结束时间格式有误！";
return Ok(jw);
}
if (DateTime.Compare(StartDateTime, EndDateTime) > 0)
{
jw.Msg = "开始时间大于结束时间！";
return Ok(jw);
}
int[] jobPostIds = { 24, 25, 26, 27, 28 };
var userGroups = _sqlSugar.Queryable<Sys_Users>()
.Where(x => x.IsDel == 0 && jobPostIds.Contains(x.JobPostId))
.Select(x => new { x.Id, x.JobPostId })
.ToArray()
.GroupBy(x => x.JobPostId)
.ToDictionary(g => g.Key, g => g.Select(x => x.Id).ToArray());
int[] AirTicket = userGroups.ContainsKey(24) ? userGroups[24] : Array.Empty<int>();
int[] Hotel = userGroups.ContainsKey(25) ? userGroups[25] : Array.Empty<int>();
int[] OP = userGroups.ContainsKey(28) ? userGroups[28] : Array.Empty<int>();
int[] Invitation = userGroups.ContainsKey(27) ? userGroups[27] : Array.Empty<int>();
int[] Visa = userGroups.ContainsKey(26) ? userGroups[26] : Array.Empty<int>();
//主管职位
//string[] Manager = { "149", "283" };
List<ComputeRoyaltiesView> resultArr = new List<ComputeRoyaltiesView>();
var teamLv = _sqlSugar.Queryable<Sys_SetData>().Where(x => x.STid == 56 && x.IsDel == 0).ToList();
var auditState = new int[] { 1, 3 };
if (AirTicket.Contains(userId))
{
//提成方式：￥10/人×人数×段数（每个组团涉及的换机次数）
var listAir = _sqlSugar.Queryable<Grp_AirTicketReservations, Grp_DelegationInfo, Grp_CreditCardPayment>((x, a, b) => new
JoinQueryInfos(
JoinType.Left, a.Id == x.DIId,
JoinType.Left, b.CTable == 85 && b.CId == x.Id)
).Where((x, a, b) => x.IsDel == 0 && x.CreateUserId == userId && (b.IsAuditGM == 1 || b.IsAuditGM == 3)
&& a.IsDel == 0 && a.VisitDate >= StartDateTime && a.VisitDate <= EndDateTime && b.IsDel == 0 && a.IsSure == 1 && auditState.Contains(b.IsAuditGM))
.Select((x, a, b) => new
{
x.FlightsDescription,
a.Id,
a.TeamName,
a.VisitDate,
a.TeamLevSId,
a.VisitPNumber,
x.Price,
x.ClientName,
x.ClientNum,
x.FlightsCode,
}).ToList();
var groupByid = listAir.GroupBy(x => x.Id);
foreach (var airArr in groupByid)
{
var groupAirCount = 0; //团组航段数量
var infos = new List<string>();
foreach (var air in airArr)
{
var airCount = 0; // 单条航段数量
if (air.FlightsDescription.IsNullOrWhiteSpace())
{
continue;
}
if (air.ClientName.Contains("-1") || air.ClientName.Contains("行程单") || air.FlightsCode.Contains("行程单"))
{
continue;
}
string errMsg = string.Empty;
if (air.FlightsDescription.Contains("\r\n") || Regex.Split(air.FlightsDescription, "\\d+\\.", RegexOptions.IgnoreCase).Count() > 1)
{
var rowCode = Regex.Split(air.FlightsDescription, "\\d+\\.", RegexOptions.IgnoreCase).Where(x => !string.IsNullOrWhiteSpace(x)).ToArray();
for (int i = 0; i < rowCode.Length; i++)
{
if (i != 0)
{
try
{
var columnValue = rowCode[i].Split(' ').Where(x => !string.IsNullOrEmpty(x)).ToList();
string day = columnValue[2].Substring(2, 2);//日
string monthAbbreviations = columnValue[2].Substring(4, 3).ToUpper();//月份
monthAbbreviations = GeneralMethod.GetLonger(monthAbbreviations);
string EndHHmm = columnValue[5].Substring(0, 2) + ":" + columnValue[5].Substring(2, 2);
DateTime EndData = DateTime.Parse($"{DateTime.Now.Year}-{monthAbbreviations}-{day} {EndHHmm}");//当前班次
var spDotandEmpty1 = rowCode[i - 1].Split(' ').Where(x => !string.IsNullOrEmpty(x)).ToList();
string day1 = spDotandEmpty1[2].Substring(2, 2);//日
string monthAbbreviations1 = spDotandEmpty1[2].Substring(4, 3).ToUpper();//月份
monthAbbreviations1 = GeneralMethod.GetLonger(monthAbbreviations1);
string HHmm = spDotandEmpty1[6].Substring(0, 2) + ":" + spDotandEmpty1[6].Substring(2, 2);
DateTime data = DateTime.Parse($"{DateTime.Now.Year}-{monthAbbreviations1}-{day1} {HHmm}");//上个班次
TimeSpan t3 = EndData - data; //两个时间相减 。默认得到的是 两个时间之间的天数 得到：365.00:00:00
double getHour = t3.TotalHours;
if (getHour >= 6)
{
if (air.Price > 0)
{
airCount += 1;
}
else
{
airCount -= 1;
}
}
}
catch (Exception ex)
{
errMsg = "机票行程代码格式有误！解析失败!" + $"({ex.Message})";
break;
}
}
else
{
if (air.Price > 0)
{
airCount += 1;
}
else
{
airCount -= 1;
}
}
}
}
else
{
if (air.Price > 0)
{
airCount++;
}
else
{
airCount--;
}
}
string info = $"航班{air.FlightsCode} {(air.Price > 0 ? "" : "此笔为退费!")} 人数为{air.ClientNum} 有效段数为{airCount} 总段数为{airCount * air.ClientNum}";
if (!string.IsNullOrWhiteSpace(errMsg))
{
info = $"航班{air.FlightsCode} " + errMsg;
}
infos.Add(info);
groupAirCount += airCount * air.ClientNum;
}
var groupinfo = listAir.First(x => x.Id == airArr.Key);
string temp = "本团人数" + Convert.ToInt32(groupinfo.VisitPNumber) + "，该人员" + user.CnName + "共订航段数" + groupAirCount.ToString() + "，每程航班提成为10元，" + user.CnName + "提成共" + (groupAirCount * 10).ToString("#0.00");
resultArr.Add(new ComputeRoyaltiesView
{
TeamName = groupinfo.TeamName,
TeamId = groupinfo.Id,
ChiArr = infos,
TeamLvStr = teamLv.Find(x => x.Id == groupinfo.TeamLevSId)?.Name,
VisitDate = groupinfo.VisitDate,
Temp = temp,
Price = (groupAirCount * 10),
});
}
}
else if (Hotel.Contains(userId))
{
//标准团组：￥10/间/晚 * 实际订房间数 * 实际订房晚数； setdata id 1001 客人房
var listHotel = _sqlSugar.Queryable<Grp_HotelReservations, Grp_DelegationInfo, Grp_CreditCardPayment>((x, a, b) => new JoinQueryInfos(
JoinType.Left, x.DiId == a.Id,
JoinType.Left, b.CTable == 76 && b.CId == x.Id))
.Where((x, a, b) => x.IsDel == 0 && x.CreateUserId == userId && a.VisitDate >= StartDateTime
&& a.VisitDate <= EndDateTime && a.IsDel == 0 && b.IsDel == 0 && x.CardPrice != 0 && a.IsSure == 1 && auditState.Contains(b.IsAuditGM) && x.CheckType == 1001).Select((x, a, b) => new
{
a.Id,
a.TeamName,
a.VisitDate,
a.TeamLevSId,
a.VisitPNumber,
x.SingleRoomCount,
x.SuiteRoomCount,
x.DoubleRoomCount,
x.OtherRoomCount,
x.CheckInDate,
x.CheckOutDate,
x.HotelName
}).ToList();
var groupByid = listHotel.GroupBy(x => x.Id);
var propertys = new string[] { "SingleRoomCount", "SuiteRoomCount", "DoubleRoomCount", "OtherRoomCount" };
foreach (var group in groupByid)
{
var infos = new List<string>();
var totalPrice = 0.00M;
foreach (var item in group)
{
var hotelCount = 0;
foreach (var property in propertys)
{
var value = item.GetType().GetProperty(property).GetValue(item).ObjToInt();
if (value > 0)
{
hotelCount += value;
}
}
if (hotelCount > 0)
{
if (DateTime.TryParse(item.CheckInDate, out DateTime checkin) && DateTime.TryParse(item.CheckOutDate, out DateTime checkout))
{
var day = checkout - checkin;
infos.Add($"{item.HotelName} 共有 {hotelCount} 间房 ， 入住时间{item.CheckInDate} - {item.CheckOutDate} 共 {day.TotalDays}晚 提成数量:{hotelCount * day.TotalDays} 提成金额:{hotelCount * day.TotalDays * 10}");
totalPrice += (decimal)(hotelCount * day.TotalDays * 10);
}
else
{
infos.Add(item.HotelName + " 时间有误！请检查 。");
}
}
}
var groupinfo = listHotel.First(x => x.Id == group.Key);
var temp = "本团人数" + groupinfo.VisitPNumber + "，该人员" + user.CnName + "共操作酒店晚数" + totalPrice / 10 + "，每间每晚提成10元，" + user.CnName + "提成共" + totalPrice.ToString("#0.00") + "元";
resultArr.Add(new ComputeRoyaltiesView
{
TeamName = groupinfo.TeamName,
TeamId = groupinfo.Id,
ChiArr = infos,
TeamLvStr = teamLv.Find(x => x.Id == groupinfo.TeamLevSId)?.Name,
VisitDate = groupinfo.VisitDate,
Temp = temp,
Price = totalPrice,
});
}
}
else if (OP.Contains(userId))
{
var OPList = _sqlSugar.Queryable<Grp_DelegationInfo>().Where((a) => a.IsDel == 0
&& a.VisitDate >= StartDateTime && a.VisitDate <= EndDateTime && a.IsSure == 1
).ToList();
var LvArr = _sqlSugar.Queryable<Sys_SetData>().Where(x => x.STid == 70 && x.IsDel == 0).ToList();
foreach (var groupinfo in OPList)
{
var price = LvArr.Find(x => x.Id == groupinfo.OpRoyaltyLv)?.Name.ObjToInt() ?? 0;
string temp = $"本团等级为{teamLv.Find(x => x.Id == groupinfo.TeamLevSId)?.Name} 提成金额为{price} {(string.IsNullOrWhiteSpace(groupinfo.OpRoyaltyRemark) ? "" : "提成说明:" + groupinfo.OpRoyaltyRemark)}";
if (price == 0)
{
temp = $"本团等级为{teamLv.Find(x => x.Id == groupinfo.TeamLevSId)?.Name} op提成选项未选择！";
}
resultArr.Add(new ComputeRoyaltiesView
{
TeamName = groupinfo.TeamName,
TeamId = groupinfo.Id,
ChiArr = new List<string>() { temp },
TeamLvStr = teamLv.Find(x => x.Id == groupinfo.TeamLevSId)?.Name,
VisitDate = groupinfo.VisitDate,
Temp = temp,
Price = price
});
}
}
else if (Invitation.Contains(userId))
{
//2)付费公务活动部分：￥50 / 团；
//4)不付费公务活动部分（自己联络）额外奖励：￥200 / 团（若公务活动方与邀请方为同一机构，奖励金额为￥100 / 团）；
//5)只发邀请的团组不付费邀请额外奖励：￥100 / 团；
//邀请方已授权我司签发的邀请仍按照：￥100 / 团，无额外奖励。
var officialList = _sqlSugar.Queryable<Res_OfficialActivities, Grp_DelegationInfo>((x, a) =>
new JoinQueryInfos(JoinType.Left, a.Id == x.DiId)
).Where((x, a) => x.IsSubmitApproval == 0 && x.IsDel == 0 && a.IsDel == 0
&& a.VisitDate >= StartDateTime && a.VisitDate <= EndDateTime && a.IsSure == 1
)
.Select((x, a) => new
{
a.Id,
a.TeamName,
a.VisitDate,
x.CreateUserId,
x.Type,
a.TeamLevSId,
x.Country,
x.Area,
x.Date,
x.Client
})
.ToList();
var groups = officialList.GroupBy(x => x.Id);
foreach (var group in groups)
{
var groupinfo = officialList.First(x => x.Id == group.Key);
var officialGroupList = group.GroupBy(d => d.CreateUserId);
int operUserMax = 0, operMaxCount = 0;
foreach (var item in officialGroupList)
{
if (item.Count() > operMaxCount)
{
operUserMax = item.Key;
operMaxCount = item.Count();
}
}
string temp = string.Empty;
bool isBasics = operUserMax == userId;
var money = 0;
var infos = new List<string>();
foreach (var item in group)
{
if (item.CreateUserId != user.Id)
{
continue;
}
string info = string.Empty;
if (item.Type == 0)
{
money += (100 + 300);
info = $"{item.Country} - {item.Area} - {item.Client} - {item.Date} 提成金额为 400";
}
else if (item.Type == 1)
{
money += 300;
info = $"{item.Country} - {item.Area} - {item.Client} - {item.Date} 提成金额为 300";
}
else if (item.Type == 2)
{
money += 200;
info = $"{item.Country} - {item.Area} - {item.Client} - {item.Date} 提成金额为 200";
}
infos.Add(info);
}
if (isBasics)
{
infos.Add($"{groupinfo.TeamName} 该人员{user.CnName}操作场数最多，基本提成为{150}");
}
else
{
infos.Add($"{groupinfo.TeamName} 操作场数不是最多人员，所以无基本提成");
}
resultArr.Add(new ComputeRoyaltiesView
{
TeamName = groupinfo.TeamName,
TeamId = groupinfo.Id,
ChiArr = infos,
TeamLvStr = teamLv.Find(x => x.Id == groupinfo.TeamLevSId)?.Name,
VisitDate = groupinfo.VisitDate,
Temp = temp,
Price = money,
});
}
}
else if (Visa.Contains(userId)) //签证
{
// 1）办理英国、加拿大、美国签证￥50元 / 人 / 团；
// 申根国家及其他所有国家￥30元 / 人 / 团；
// setdata id 974 客人
var existsCountry = new string[] { "英国", "加拿大", "美国" };
var whereExpression = Expressionable.Create<Grp_VisaInfo, Grp_DelegationInfo, Grp_CreditCardPayment>()
.And(
(x, a, b) => x.IsDel == 0 && x.CreateUserId == userId && (b.IsAuditGM == 1 || b.IsAuditGM == 3)
&& a.IsDel == 0 && a.VisitDate >= StartDateTime && a.VisitDate <= EndDateTime && b.IsDel == 0 && x.IsThird == 0 && x.PassengerType == 974
&& a.IsSure == 1 && auditState.Contains(b.IsAuditGM)
);
var listVisa = _sqlSugar.Queryable<Grp_VisaInfo, Grp_DelegationInfo, Grp_CreditCardPayment>((x, a, b) => new
JoinQueryInfos(
JoinType.Left, a.Id == x.DIId,
JoinType.Left, b.CTable == 80 && b.CId == x.Id)
)
.Where(whereExpression.ToExpression())
.Select((x, a, b) => new
{
a.Id,
a.TeamName,
a.VisitDate,
a.TeamLevSId,
a.VisitPNumber,
a.VisitCountry,
x.VisaDescription,
x.VisaFreeNumber,
x.VisaNumber,
x.VisaPrice
})
.ToList();
var groupByid = listVisa.GroupBy(x => x.Id);
foreach (var visaArr in groupByid)
{
var groupinfo = listVisa.First(x => x.Id == visaArr.Key);
var singlePrice = existsCountry.Where(x => groupinfo.VisitCountry.Contains(x)).Count() > 0 ? 50 : 30;
int count = 0, money = 0;
var arr = new List<string>();
foreach (var visa in visaArr)
{
if (visa.VisaPrice > 0)
{
count += visa.VisaFreeNumber + visa.VisaNumber;
money += singlePrice * (visa.VisaFreeNumber + visa.VisaNumber);
}
else
{
count -= visa.VisaFreeNumber + visa.VisaNumber;
money -= singlePrice * (visa.VisaFreeNumber + visa.VisaNumber);
}
string info = $"签证 {visa.VisaDescription} {(visa.VisaPrice > 0 ? "" : "此笔为退费!")} 人数为{visa.VisaFreeNumber + visa.VisaNumber} 单价为{singlePrice} 金额为{singlePrice * (visa.VisaFreeNumber + visa.VisaNumber)}";
arr.Add(info);
}
string temp = "本团人数" + groupinfo.VisitPNumber + "，该人员" + user.CnName + "共办理英国、加拿大、美国签证" + (singlePrice == 50 ? count : 0).ToString() + "本护照（包含免签人员），申根国家及其他所有国家" + (singlePrice == 30 ? count : 0).ToString() + "本。" + user.CnName + "提成共" + money.ToString("#0.00") + "元";
resultArr.Add(new ComputeRoyaltiesView
{
TeamName = groupinfo.TeamName,
TeamId = groupinfo.Id,
ChiArr = arr,
TeamLvStr = teamLv.Find(x => x.Id == groupinfo.TeamLevSId)?.Name,
VisitDate = groupinfo.VisitDate,
Temp = temp,
Price = money,
});
}
}
if (resultArr.Count > 0)
{
jw.Code = 200;
jw.Msg = "success!";
decimal total = 0;
foreach (var item in resultArr)
{
//var number = item.GetType().GetProperty("price")?.GetValue(item)?.ToString();
//total += decimal.TryParse(number,out decimal numberInt) ? numberInt : 0.00M;
total += item.Price;
}
jw.Data = new
{
resultArr,
totalPrice = total,
};
}
else
{
jw.Msg = "暂无数据!";
}
return Ok(jw);
}
/// <summary>
/// 导出提成表格
/// </summary>
/// <returns></returns>
[HttpPost]
public IActionResult ExportRoyaltyExcel(ExportRoyaltyExcelDto dto)
{
var jw = JsonView(false);
var apiResult = ComputeRoyalties(new
ComputeRoyaltiesDto
{
EndDate = dto.EndDate,
StartDate = dto.StartDate,
UserId = dto.UserId,
});
var parseApiResult = ((apiResult as OkObjectResult)?.Value) as OASystem.Domain.ViewModels.JsonView;
if (parseApiResult != null && parseApiResult.Code == 200)
{
var apiArr = (parseApiResult.Data?.GetType().GetProperty("resultArr")?.GetValue(parseApiResult.Data)) as List<ComputeRoyaltiesView>;
var user = _sqlSugar.Queryable<Sys_Users>().First(x => x.Id == dto.UserId);
if (apiArr != null && apiArr.Count > 0)
{
Dictionary<string, string> dic = new Dictionary<string, string>();
//dic.Add("Name", "人员：" + user.CnName);
dic.Add("startTime", dto.StartDate);
dic.Add("endTime", dto.EndDate);
dic.Add("operName", user.CnName);
dic.Add("timeNow", DateTime.Now.ToString("yyyy年MM月dd日"));
//dic.Add("DowntimeNow", DateTime.Now.ToString("yyyy年MM月dd日"));
dic.Add("totalPrice", ("总金额：" + parseApiResult.Data?.GetType().GetProperty("totalPrice")?.GetValue(parseApiResult.Data).ObjToDecimal().ToString() ?? "0") + " 元");
ArrayList ToTableArr = new ArrayList();
int No = 1;
//打开excel模板
foreach (var item in apiArr)
{
//载入数据
ToTableArr.Add(new
{
No,
item.TeamName,
item.VisitDate,
item.TeamLvStr,
item.Temp,
infos = string.Join("\r\n", item.ChiArr),
price = item.Price + "元",
});
No++;
}
DataTable TbData = CommonFun.ToDataTableArray(ToTableArr);
TbData.TableName = "TbData";
WorkbookDesigner designer = new WorkbookDesigner();
designer.Workbook = new Workbook(AppSettingsHelper.Get("ExcelBasePath") + "Template/提成结算template.xlsx");
foreach (var key in dic.Keys)
{
designer.SetDataSource(key, dic[key]);
}
//数据源
designer.SetDataSource(TbData);
designer.Process();
designer.Workbook.Save(AppSettingsHelper.Get("ExcelBasePath") + "RoyaltyExcel/" + user.CnName + "_提成结算.xlsx");
jw = JsonView(true, "success!", new
{
Url = AppSettingsHelper.Get("ExcelBaseUrl") + AppSettingsHelper.Get("ExcelFtpPath") + "RoyaltyExcel/" + user.CnName + "_提成结算.xlsx",
});
}
}
else
{
jw.Msg = "获取提成信息error " + jw.Msg;
}
return Ok(jw);
}
/// <summary>
/// 提成财务确认通知
/// </summary>
/// <returns></returns>
[HttpPost]
public async Task<IActionResult> RoyaltyConfirmByFin(RoyaltyConfirmDto dto)
{
var jw = JsonView(false);
if (!DateTime.TryParse(dto.StartData, out DateTime StartDateTime))
{
jw.Msg = "开始时间格式有误！";
return Ok(jw);
}
if (!DateTime.TryParse(dto.EndData, out DateTime EndDateTime))
{
jw.Msg = "结束时间格式有误！";
return Ok(jw);
}
if (DateTime.Compare(StartDateTime, EndDateTime) > 0)
{
jw.Msg = "开始时间大于结束时间！";
return Ok(jw);
}
if (dto.ComputeRoyaltiesList.Count != 0)
{
var user = _sqlSugar.Queryable<Sys_Users>().First(x => x.Id == dto.UserId);
if (user == null) { return Ok(JsonView(false, "通知失败! 人员信息有误！")); }
//保存提成信息
//消息通知对应人员(公司 部门 人员 时间段 团组id 是否确定 )
List<Fin_RoyaltyConfirm> entitys = dto.ComputeRoyaltiesList.Select(x => new Fin_RoyaltyConfirm
{
UserId = dto.UserId,
ChiArr = JsonConvert.SerializeObject(x.ChiArr),
CreateTime = DateTime.Now,
CreateUserId = dto.UserId,
IsConfirm = 0,
TeamId = x.TeamId,
TeamLvStr = x.TeamLvStr,
Price = x.Price,
VisitDate = x.VisitDate,
TeamName = x.TeamName,
Temp = x.Temp,
IsSeed = 0
}).ToList();
var expressionSoftDeleteWhere = Expressionable.Create<Fin_RoyaltyConfirm>()
.And(it => it.VisitDate >= StartDateTime)
.And(it => it.VisitDate <= EndDateTime)
.And(it => it.IsDel == 0)
.And(it => it.UserId == dto.UserId)
.And(it => it.IsSeed == 0)
.ToExpression();
var expressionSelectWhere = Expressionable.Create<Fin_RoyaltyConfirm>()
.And(it => it.VisitDate >= StartDateTime)
.And(it => it.VisitDate <= EndDateTime)
.And(it => it.IsDel == 0)
.And(it => it.UserId == dto.UserId)
.And(it => it.IsSeed == 1)
.ToExpression();
_sqlSugar.BeginTran();
try
{
_ = _sqlSugar.Updateable<Fin_RoyaltyConfirm>().SetColumns(it => new Fin_RoyaltyConfirm() { IsDel = 1, DeleteTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm"), DeleteUserId = dto.UserId })
.Where(expressionSoftDeleteWhere)
.ExecuteCommand();
var confirmYes = _sqlSugar.Queryable<Fin_RoyaltyConfirm>().Where(expressionSelectWhere).Select(x => x.TeamId).ToList();
entitys = entitys.Where(x => !confirmYes.Contains(x.TeamId)).ToList();
_ = _sqlSugar.Insertable<Fin_RoyaltyConfirm>(entitys).ExecuteCommand();
_sqlSugar.CommitTran();
//调用通知
var title = "提成确认";
var center = $"【{dto.StartData}】 - 【{dto.EndData}】提成财务已确认，请尽快查看!";
//【{dto.StartData}-{dto.EndData}-{dto.CompanyId}-{dto.DeparId}-{dto.UserId}】";
var ids = new List<int> { 235, 233 }; //dto.UserId
await GeneralMethod.MessageIssueAndNotification(MessageTypeEnum.GroupBusinessOperations, title, center, ids);
jw = JsonView(true, $"人员:{user.CnName} {dto.StartData}-{dto.EndData} 通知成功!");
}
catch (Exception ex)
{
_sqlSugar.RollbackTran();
jw = JsonView(false, "保存失败!" + ex.Message);
}
}
else
{
jw = JsonView(false, "保存失败! 无提成信息");
}
return Ok(jw);
}
/// <summary>
/// 查询提成信息
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
[HttpPost]
public IActionResult QueryRoyaltyInfo(QueryRoyaltyInfoDto dto)
{
var jw = JsonView(false);
var stateArr = new int[] { -1, 0, 1 };
if (!DateTime.TryParse(dto.StartDate, out DateTime StartDateTime))
{
jw.Msg = "开始时间格式有误！";
return Ok(jw);
}
if (!DateTime.TryParse(dto.EndDate, out DateTime EndDateTime))
{
jw.Msg = "结束时间格式有误！";
return Ok(jw);
}
if (DateTime.Compare(StartDateTime, EndDateTime) > 0)
{
jw.Msg = "开始时间大于结束时间！";
return Ok(jw);
}
if (!stateArr.Contains(dto.Send) || !stateArr.Contains(dto.Confirm))
{
jw.Msg = "状态标识有误！";
return Ok(jw);
}
var expressionSelectWhere = Expressionable.Create<Fin_RoyaltyConfirm>()
.And(it => it.VisitDate >= StartDateTime)
.And(it => it.VisitDate <= EndDateTime)
.And(it => it.IsDel == 0)
.And(it => it.UserId == dto.UserId)
.AndIF(dto.Confirm != -1, it => it.IsConfirm == dto.Confirm)
.AndIF(dto.Send != -1, it => it.IsSeed == dto.Send)
.ToExpression();
try
{
var query = _sqlSugar.Queryable<Fin_RoyaltyConfirm>().Where(expressionSelectWhere).ToList().Select(x => new
{
x.Price,
ChiArr = JsonConvert.DeserializeObject<List<string>>(x.ChiArr),
x.IsConfirm,
x.Id,
x.TeamId,
x.TeamName,
x.TeamLvStr,
x.Temp,
x.VisitDate,
x.IsSeed
});
jw = JsonView(true, "获取成功！", new
{
List = query,
CostPrice = query.Sum(x => x.Price),
ConfirmPrice = query.Where(x => x.IsConfirm == 1).Sum(x => x.Price),
NoConfirmPrice = query.Where(x => x.IsConfirm == 0).Sum(x => x.Price),
SeedPrice = query.Where(x => x.IsSeed == 1).Sum(x => x.Price),
NoSeedPrice = query.Where(x => x.IsSeed == 0).Sum(x => x.Price),
});
}
catch (Exception ex)
{
jw = JsonView(true, "获取失败！" + ex.Message);
}
return Ok(jw);
}
/// <summary>
/// 个人提成确认
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
[HttpPost]
public IActionResult RoyaltyConfirmByUser(RoyaltyConfirmByUserDto dto)
{
var jw = JsonView(false);
var stateArr = new int[] { 0, 1 };
if (!stateArr.Contains(dto.State))
{
return Ok(JsonView(false, "状态标识有误!"));
}
if (dto.Data != null && dto.Data.Count != 0)
{
var expressionUpdateWhere = Expressionable.Create<Fin_RoyaltyConfirm>()
.And(it => dto.Data.Contains(it.Id))
.And(it => it.IsDel == 0)
.ToExpression();
var count = _sqlSugar.Updateable<Fin_RoyaltyConfirm>().SetColumns(x => new Fin_RoyaltyConfirm
{
IsConfirm = dto.State,
})
.Where(expressionUpdateWhere).ExecuteCommand();
jw.Msg = $"{count}个团组确认成功！";
jw.Code = 200;
}
else
{
jw.Msg = "Data为空！";
}
return Ok(jw);
}
/// <summary>
/// 提成发放
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
[HttpPost]
public IActionResult RoyaltySeed(RoyaltySeedDto dto)
{
var jw = JsonView(false);
if (dto.Data != null && dto.Data.Count > 0)
{
var updateCount = _sqlSugar.Updateable<Fin_RoyaltyConfirm>().SetColumns(x => new Fin_RoyaltyConfirm
{ IsSeed = 1 }).Where(x => dto.Data.Contains(x.Id) && x.IsDel == 0 && x.IsConfirm == 1).ExecuteCommand();
var info = $"{updateCount}个团组提成发放成功！";
if (dto.Data.Count > updateCount)
{
info += $"/r/n{dto.Data.Count - updateCount}个团组未确认！";
}
jw = JsonView(true, info);
}
else
{
jw.Msg = "发放的数据为空！";
}
return Ok(jw);
}
/// <summary>
/// 提成确认信息
/// </summary>
/// <returns></returns>
[HttpPost]
public IActionResult RoyaltyConfirmInfo()
{
var jw = JsonView(false);
var queryExpression = Expressionable.Create<Fin_RoyaltyConfirm>()
.And(r => r.IsDel == 0)
.And(r => r.IsConfirm == 1)
.And(r => r.IsSeed == 0)
.ToExpression();
var DbQueryConfirmInfo = _sqlSugar.Queryable<Fin_RoyaltyConfirm>()
.LeftJoin<Sys_Users>((r, s) => s.IsDel == 0 && s.Id == r.UserId)
.LeftJoin<Sys_JobPost>((r, s, p) => p.IsDel == 0 && p.Id == s.JobPostId)
.LeftJoin<Sys_Department>((r, s, p, d) => d.IsDel == 0 && d.Id == p.DepId)
.LeftJoin<Sys_Company>((r, s, p, d, c) => c.IsDel == 0 && c.Id == d.CompanyId)
.Where(queryExpression)
.Select((r, s, p, d, c) => new
{
r.UserId,
r.TeamId,
r.Id,
r.TeamName,
r.Price,
s.CnName,
p.JobName,
d.DepName,
c.CompanyName
})
.ToList();
var groupConfirmInfoByUser = DbQueryConfirmInfo.GroupBy(x => x.UserId);
ArrayList returnData = new ArrayList();
foreach (var item in groupConfirmInfoByUser)
{
var first = item.First();
returnData.Add(new
{
UserId = item.Key,
Count = item.Count(),
first.CompanyName,
first.DepName,
first.JobName,
first.CnName,
SumPrice = item.Sum(x => x.Price),
RoyaltyIdArr = item.Select(x => x.Id)
});
}
jw = JsonView(true, "获取成功!", returnData);
return Ok(jw);
}
/// <summary>
/// 提交到日付申请
/// </summary>
/// <returns></returns>
[HttpPost]
public async Task<IActionResult> RoyaltyFormPrice(RoyaltyFromPriceDto dto)
{
var jw = JsonView(false, "用户ID为空！");
var User = _sqlSugar.Queryable<Sys_Users>().First(x => x.Id
== dto.CreateId && x.IsDel == 0);
if (User == null)
{
JsonView(false, "createid 不存在！");
}
if (dto.Items.Count > 0)
{
var queryExpression = Expressionable.Create<Fin_RoyaltyConfirm>()
.And(r => r.IsDel == 0)
.And(r => r.IsConfirm == 1)
.And(r => dto.Items.Select(x => x.UserId).Contains(r.UserId))
.ToExpression();
var DbQueryConfirmInfo = _sqlSugar.Queryable<Fin_RoyaltyConfirm>()
.LeftJoin<Sys_Users>((r, s) => s.IsDel == 0 && s.Id == r.UserId)
.LeftJoin<Sys_JobPost>((r, s, p) => p.IsDel == 0 && p.Id == s.JobPostId)
.LeftJoin<Sys_Department>((r, s, p, d) => d.IsDel == 0 && d.Id == p.DepId)
.LeftJoin<Sys_Company>((r, s, p, d, c) => c.IsDel == 0 && c.Id == d.CompanyId)
.Where(queryExpression)
.Select((r, s, p, d, c) => new
{
r.UserId,
r.TeamId,
r.Id,
r.TeamName,
r.Price,
s.CnName,
p.JobName,
d.DepName,
c.CompanyName,
CompanyId = c.Id,
UserName = s.CnName,
})
.ToList();
//已经提交到日付的用户提成信息
var existsDb = _sqlSugar.Queryable<Fin_RoyaltyForm>()
.Where(x => dto.Items
.Select(x => x.UserId).Contains(x.UserId) && x.IsDel == 0)
.ToList();
var GroupByConfirmInfo = DbQueryConfirmInfo.GroupBy(x => x.UserId).ToList();
//关联信息
List<Fin_RoyaltyForm> relevanceSoure = new List<Fin_RoyaltyForm>();
AddDailyFeePaymentDto dayPrice = new AddDailyFeePaymentDto()
{
UserId = dto.CreateId,
Instructions = DateTime.Now.ToString("yyyy-MM-dd HH:mm") + " 提成导入日付！",
PortType = 1,
CompanyId = 2,
PriceTypeId = 668,
TransferTypeId = 798,
};
List<AddDailyFeePaymentContentDto> ChiDayPrice = new List<AddDailyFeePaymentContentDto>();
_sqlSugar.BeginTran();
try
{
string message = string.Empty;
foreach (var item in GroupByConfirmInfo)
{
var first = item.First();
var reqBody = dto.Items.Find(x => x.UserId == item.Key);
var exists = item.Where(x => reqBody!.ChiArr.Contains(x.Id));
var existsDbByUser = existsDb.Where(x => x.UserId == item.Key);
var idsByUser = string.Join(',', existsDbByUser.Select(x => x.ConfirmIdArr));
var idsByUserList = idsByUser.Split(',')
.Select(x =>
{
if (!string.IsNullOrWhiteSpace(x) && int.TryParse(x, out int intx))
{
return intx;
}
return 0;
})
.Where(x => x != 0)
.ToList();
var saveArr = exists.Where(x => !idsByUserList.Contains(x.Id));
var existsYes = exists.Where(x => idsByUserList.Contains(x.Id));
var yesPrice = existsYes.Sum(x => x.Price);
var savePrice = saveArr.Sum(x => x.Price);
if (saveArr.Count() > 0)
{
ChiDayPrice.Add(new
AddDailyFeePaymentContentDto
{
ItemTotal = savePrice,
Price = savePrice,
Quantity = 1,
PriceName = first.CnName + "提成金额!",
});
relevanceSoure.Add(new
Fin_RoyaltyForm
{
CreateUserId = dto.CreateId,
ConfirmIdArr = string.Join(',', saveArr.Select(x => x.Id)),
CreateTime = DateTime.Now,
IsDel = 0,
UserId = item.Key,
});
}
if (yesPrice > 0)
{
message += $"{first.CnName} 提成 {savePrice}元已导入日付申请 , {yesPrice} 元已经存在 ! \r\n";
}
else
{
message += $"{first.CnName} 提成 {savePrice}元已导入日付申请 ! \r\n";
}
}
dayPrice.FeeContents = ChiDayPrice;
dayPrice.SumPrice = ChiDayPrice.Sum(x => x.ItemTotal);
if (dayPrice.SumPrice != 0)
{
var ApiResult = await _daiRep.Add(dayPrice);
int dailyId = Convert.ToInt32(ApiResult?.Data?.GetType().GetProperty("dailyId").GetValue(ApiResult.Data));
int sign = Convert.ToInt32(ApiResult?.Data?.GetType().GetProperty("sign").GetValue(ApiResult.Data));
relevanceSoure.ForEach(x => x.DayOverhead = dailyId);
var addCount = _sqlSugar.Insertable<Fin_RoyaltyForm>(relevanceSoure).ExecuteCommand();
}
_sqlSugar.CommitTran();
//消息通知
//await AppNoticeLibrary.DailyPayReminders_Create_ToCaiwuChat(dailyId, sign, QiyeWeChatEnum.CaiWuChat);
jw = JsonView(true, message);
}
catch (Exception ex)
{
_sqlSugar.RollbackTran();
jw = JsonView(false, $" Api Error ({ex.Message})");
}
}
return Ok(jw);
}
#endregion
#region 团组未审核数据
/// <summary>
/// 团组未审核费用数据
/// </summary>
/// <param name="beginDt"></param>
/// <param name="endDt"></param>
/// <returns></returns>
[HttpGet]
public async Task<IActionResult> GroupUnAuditFeeList(string beginDt, string endDt, string teamName)
{
var jw = JsonView(false);
bool beginDtBool = DateTime.TryParse(beginDt, out DateTime _beginDt),
endDtBool = DateTime.TryParse(endDt, out DateTime _endDt);
if (!beginDtBool && !endDtBool)
{
jw.Msg = $"开始或者结束时间格式不正确!";
return Ok(jw);
}
var _groupDatas = _sqlSugar.Queryable<Grp_DelegationInfo>()
.Where(x => x.IsDel == 0 &&
x.VisitDate >= _beginDt &&
x.VisitDate <= _endDt
)
.WhereIF(!string.IsNullOrEmpty(teamName), x => x.TeamName.Contains(teamName))
.ToList();
var _groupIds = _groupDatas.Select(x => x.Id).ToList();
if (_groupIds.Count < 1)
{
jw.Msg = $"该时间段暂无出访的团组!";
return Ok(jw);
}
var _DailyFeePaymentResult = new tree_Group_DailyFeePaymentResult();
var dataList = new List<tree_Group_DailyFeePaymentPageListView>();
var _paymentDatas = _sqlSugar.Queryable<Grp_CreditCardPayment>()
.Where(x => x.IsDel == 0 &&
_groupIds.Contains(x.DIId) &&
x.IsAuditGM == 0
)
.ToList();
_DailyFeePaymentResult.gz = _paymentDatas.Where(it => it.OrbitalPrivateTransfer == 0).Sum(it => ((it.PayMoney * it.DayRate) / 100) * it.PayPercentage); //公转
_DailyFeePaymentResult.sz = _paymentDatas.Where(it => it.OrbitalPrivateTransfer == 1).Sum(it => ((it.PayMoney * it.DayRate) / 100) * it.PayPercentage); ; //私转
#region 相关基础数据源
var userDatas = _sqlSugar.Queryable<Sys_Users>().ToList();
var setDatas = _sqlSugar.Queryable<Sys_SetData>().Where(it => it.IsDel == 0).ToList();
var countryFeeDatas = _sqlSugar.Queryable<Grp_NationalTravelFee>().Where(it => it.IsDel == 0).ToList();
//76 酒店预订
var hotelDatas = _sqlSugar.Queryable<Grp_HotelReservations>()
.InnerJoin<Grp_CreditCardPayment>((hr, ccp) => hr.Id == ccp.CId &&
ccp.CTable == 76 &&
ccp.IsDel == 0
)
.Where((hr, ccp) => hr.IsDel == 0 && _groupIds.Contains(hr.DiId) && ccp.IsAuditGM == 0)
.ToList();
var hotelContentDatas = _sqlSugar.Queryable<Grp_HotelReservationsContent>()
.Where(it => it.IsDel == 0 &&
it.IsPay == 0 &&
it.Price != 0 &&
_groupIds.Contains(it.DiId)
)
.ToList();
//79 车/导游地接
var opDatas = _sqlSugar.Queryable<Grp_CarTouristGuideGroundReservations>()
.InnerJoin<Grp_CreditCardPayment>((ctggr, ccp) => ctggr.Id == ccp.CId &&
ccp.CTable == 79 &&
ccp.IsDel == 0
)
.Where((ctggr, ccp) => ctggr.IsDel == 0 && _groupIds.Contains(ctggr.DiId) && ccp.IsAuditGM == 0)
.ToList();
// 80 签证
var visaDatas = _sqlSugar.Queryable<Grp_VisaInfo>()
.InnerJoin<Grp_CreditCardPayment>((vi, ccp) => vi.Id == ccp.CId &&
ccp.CTable == 80 &&
ccp.IsDel == 0
)
.Where((vi, ccp) => vi.IsDel == 0 && _groupIds.Contains(vi.DIId) && ccp.IsAuditGM == 0)
.ToList();
// 81 邀请/公务活动
var ioaDatas = _sqlSugar.Queryable<Grp_InvitationOfficialActivities>()
.InnerJoin<Grp_CreditCardPayment>((ioa, ccp) => ioa.Id == ccp.CId &&
ccp.CTable == 81 &&
ccp.IsDel == 0
)
.Where((ioa, ccp) => ioa.IsDel == 0 && _groupIds.Contains(ioa.DiId) && ccp.IsAuditGM == 0)
.ToList();
// 82 团组客户保险
var insureDatas = _sqlSugar.Queryable<Grp_Customers>()
.InnerJoin<Grp_CreditCardPayment>((c, ccp) => c.Id == ccp.CId &&
ccp.CTable == 82 &&
ccp.IsDel == 0
)
.Where((c, ccp) => c.IsDel == 0 && _groupIds.Contains(c.DiId) && ccp.IsAuditGM == 0)
.ToList();
// 85 机票预订
var airDatas = _sqlSugar.Queryable<Grp_AirTicketReservations>()
.InnerJoin<Grp_CreditCardPayment>((atr, ccp) => atr.Id == ccp.CId &&
ccp.CTable == 85 &&
ccp.IsDel == 0
)
.Where((atr, ccp) => atr.IsDel == 0 && _groupIds.Contains(atr.DIId) && ccp.IsAuditGM == 0)
.ToList();
// 98 其他款项
var otherMoneyDatas = _sqlSugar.Queryable<Grp_DecreasePayments>()
.InnerJoin<Grp_CreditCardPayment>((dp, ccp) => dp.Id == ccp.CId &&
ccp.CTable == 98 &&
ccp.IsDel == 0
)
.Where((dp, ccp) => dp.IsDel == 0 && _groupIds.Contains(dp.DiId) && ccp.IsAuditGM == 0)
.ToList();
// 285 收款退还
var refundPaymentDatas = _sqlSugar.Queryable<Fin_PaymentRefundAndOtherMoney>()
.InnerJoin<Grp_CreditCardPayment>((prom, ccp) => prom.Id == ccp.CId &&
ccp.CTable == 285 &&
ccp.IsDel == 0
)
.Where((prom, ccp) => prom.IsDel == 0 && _groupIds.Contains(prom.DiId) && ccp.IsAuditGM == 0)
.ToList();
// 1015 超支费用
var extraCostDatas = _sqlSugar.Queryable<Fin_GroupExtraCost>()
.InnerJoin<Grp_CreditCardPayment>((gec, ccp) => gec.Id == ccp.CId &&
ccp.CTable == 1015 &&
ccp.IsDel == 0
)
.Where((gec, ccp) => gec.IsDel == 0 && _groupIds.Contains(gec.DiId) && ccp.IsAuditGM == 0)
.ToList();
#endregion
//Expense company
foreach (var groupInfo in _groupDatas)
{
var childList = new List<Group_DailyFeePaymentContentInfolView>();
var groupPaymentDatas = _paymentDatas.Where(it => groupInfo.Id == it.DIId).ToList();
if (groupPaymentDatas.Count > 0)
{
int rouNumber = 1;
foreach (var payInfo in groupPaymentDatas)
{
string priName = "-";
string orbitalPrivateTransfer = payInfo.OrbitalPrivateTransfer == 0 ? "公转" : payInfo.OrbitalPrivateTransfer == 1 ? "私转" : "-";
switch (payInfo.CTable)
{
case 76: //76 酒店预订
priName = $"[费用名称：{hotelDatas.Find(it => payInfo.DIId == it.DiId && payInfo.CId == it.Id)?.HotelName ?? ""}]";
break;
case 79: //79 车/导游地接
var opData = opDatas.Find(it => payInfo.DIId == it.DiId && payInfo.CId == it.Id);
if (opData != null)
{
string area = "";
bool b = int.TryParse(opData.Area, out int areaId);
if (b)
{
string area1 = countryFeeDatas.Find(it => it.Id == areaId)?.Country ?? "-";
area = $"{area1}({setDatas.Find(it => it.Id == opData.PriceType)?.Name ?? "-"})";
}
else area = opData.Area;
string opPriName = "-";
if (!string.IsNullOrEmpty(opData.PriceName)) opPriName = opData.PriceName;
area += $"({opPriName})";
if (payInfo.OrbitalPrivateTransfer == 0) //公转
{
priName = $"【{orbitalPrivateTransfer}】【导游： {opData.ServiceGuide} 】[费用名称：{area}]";
}
else if (payInfo.OrbitalPrivateTransfer == 1) //私转
{
priName = $"【{orbitalPrivateTransfer}】【导游：{opData.ServiceGuide}】[费用名称：{area}]";
}
}
break;
case 80: // 80 签证
string sql = string.Format("select b.Id,b.Pinyin,b.lastName,b.firstName,b.phone from Grp_TourClientList a, Crm_DeleClient b where a.clientid = b.id and a.isdel = 0 and a.diid = {0}", groupInfo.Id);
List<SimplClientInfo> arr = _sqlSugar.SqlQueryable<SimplClientInfo>(sql).ToList();
string visaClientName = visaDatas.Find(it => payInfo.DIId == it.DIId && payInfo.CId == it.Id)?.VisaClient ?? "";
string clientName = "-";
if (Regex.Match(visaClientName, @"\d+,?").Value.Length > 0)
{
string[] temparr = visaClientName.Split(',');
string fistrStr = temparr[0];
int count = temparr.Count();
int tempId;
bool success = int.TryParse(fistrStr, out tempId);
if (success)
{
SimplClientInfo tempInfo = arr.FirstOrDefault(s => s.Id == tempId);
if (tempInfo != null)
{
if (count > 1)
{
clientName = string.Format(@"{0}{1}等{2}人", tempInfo.LastName, tempInfo.FirstName, count);
}
else
{
clientName = string.Format(@"{0}{1}", tempInfo.LastName, tempInfo.FirstName);
}
}
}
else
{
clientName = fistrStr;
}
}
priName = $"[费用名称：{clientName}]";
break;
case 81: // 81 邀请/公务活动
priName = $"[费用名称：{ioaDatas.Find(it => payInfo.DIId == it.DiId && payInfo.CId == it.Id)?.Inviter ?? " -"}]";
break;
case 82: // 82 团组客户保险
string bx_sql = string.Format("select b.Id,b.Pinyin,b.lastName,b.firstName,b.phone from Grp_TourClientList a, Crm_DeleClient b where a.clientid = b.id and a.isdel = 0 and a.diid = {0}", groupInfo.Id);
List<SimplClientInfo> bx_arr = _sqlSugar.SqlQueryable<SimplClientInfo>(bx_sql).ToList();
string bx_ClientName = insureDatas.Find(it => payInfo.DIId == it.DiId && payInfo.CId == it.Id)?.ClientName ?? "";
string bx_clientName = "-";
if (Regex.Match(bx_ClientName, @"\d+,?").Value.Length > 0)
{
string[] temparr = bx_ClientName.Split(',');
string fistrStr = temparr[0];
int count = temparr.Count();
int tempId;
bool success = int.TryParse(fistrStr, out tempId);
if (success)
{
SimplClientInfo tempInfo = bx_arr.FirstOrDefault(s => s.Id == tempId);
if (tempInfo != null)
{
if (count > 1)
{
bx_clientName = string.Format(@"{0}{1}等{2}人", tempInfo.LastName, tempInfo.FirstName, count);
}
else
{
bx_clientName = string.Format(@"{0}{1}", tempInfo.LastName, tempInfo.FirstName);
}
}
}
else
{
bx_clientName = fistrStr;
}
}
//priName = $"[费用名称：{insureDatas.Find(it => payInfo.DIId == it.DiId && payInfo.CId == it.Id)?.ClientName ?? " -"}]";
priName = $"[费用名称：{bx_clientName}]";
break;
case 85: // 85 机票预订
string flightsCode = airDatas.Find(it => payInfo.DIId == it.DIId && payInfo.CId == it.Id)?.FlightsCode ?? "-";
string airPayType = setDatas.Find(it => it.Id == payInfo.PayDId)?.Name ?? "-";
priName = $"{flightsCode}【{airPayType}】";
break;
case 98: // 98 其他款项
priName = $"[费用名称：{otherMoneyDatas.Find(it => payInfo.DIId == it.DiId && payInfo.CId == it.Id)?.PriceName ?? " -"}]";
break;
case 285: // 285 收款退还
priName = $"[费用名称：{refundPaymentDatas.Find(it => payInfo.DIId == it.DiId && payInfo.CId == it.Id)?.PriceName ?? " -"}]";
break;
case 1015: // 1015 超支费用
priName = $"[费用名称：{extraCostDatas.Find(it => payInfo.DIId == it.DiId && payInfo.CId == it.Id)?.PriceName ?? " -"}]";
break;
default:
priName = "";
break;
}
if (payInfo.CTable == 76) //酒店单独处理
{
var hotelContents = hotelContentDatas.Where(it => it.HrId == payInfo.CId);
List<Group_DailyFeePaymentContentInfolView> childInfos = new List<Group_DailyFeePaymentContentInfolView>();
foreach (var hotelContent in hotelContents)
{
string subPriceName = "";
if (hotelContent.PriceType == 1) subPriceName = "房费";
else if (hotelContent.PriceType == 2) subPriceName = "早餐";
else if (hotelContent.PriceType == 3) subPriceName = "地税";
else if (hotelContent.PriceType == 4) subPriceName = "城市税";
if (string.IsNullOrEmpty(subPriceName)) subPriceName = priName;
else subPriceName = $"{priName.Replace("]", "")}-{subPriceName}]";
string payeeStr1 = string.Format(@" {0},开户行：{1},银行卡号：{2} ",
string.IsNullOrEmpty(hotelContent.Payee) ? "-" : hotelContent.Payee,
string.IsNullOrEmpty(hotelContent.OtherBankName) ? "-" : hotelContent.OtherBankName,
string.IsNullOrEmpty(hotelContent.OtherSideNo) ? "-" : hotelContent.OtherSideNo);
decimal _PaymentAmount1 = hotelContent.Price;//此次付款金额
decimal _CNYSubTotalAmount1 = _PaymentAmount1 * hotelContent.Rate;//此次付款金额
_CNYSubTotalAmount1 = Convert.ToDecimal(_CNYSubTotalAmount1.ToString("#0.00"));
var childInfo1 = new Group_DailyFeePaymentContentInfolView()
{
Id = payInfo.Id,
HotelSubId = hotelContent.Id,
Payee = payeeStr1,
RowNumber = rouNumber,
Applicant = userDatas.Find(it => it.Id == payInfo.CreateUserId)?.CnName ?? "",
ApplicantDt = payInfo.CreateTime.ToString("yyyy-MM-dd HH:mm:ss"),
PayType = setDatas.Find(it => it.Id == hotelContent.PayDId)?.Name ?? "",
TransferMark = orbitalPrivateTransfer,
PriceName = subPriceName,
ModuleName = setDatas.Find(it => it.Id == payInfo.CTable)?.Name ?? "",
PayCurrCode = setDatas.Find(it => it.Id == hotelContent.Currency)?.Name ?? "",
PaymentAmount = _PaymentAmount1,
PayRate = hotelContent.Rate,
CNYSubTotalAmount = _CNYSubTotalAmount1,
AuditStatus = payInfo.IsAuditGM
};
string remaksDescription1 = $"【{childInfo1.PayType}】【{childInfo1.ModuleName}】{rouNumber}、[申请人：{childInfo1.Applicant}]{subPriceName}[收款方：{childInfo1.Payee}] {childInfo1.PayCurrCode} {_PaymentAmount1.ToString("#0.00")}、CNY：{childInfo1.CNYSubTotalAmount.ToString("#0.00")}(团组：{groupInfo.TeamName})";
childInfo1.RemaksDescription = remaksDescription1;
childInfos.Add(childInfo1);
rouNumber++;
}
childList.AddRange(childInfos);
}
else
{
string payeeStr = string.Format(@" {0},开户行：{1},银行卡号：{2} ",
string.IsNullOrEmpty(payInfo.Payee) ? "-" : payInfo.Payee,
string.IsNullOrEmpty(payInfo.OtherBankName) ? "-" : payInfo.OtherBankName,
string.IsNullOrEmpty(payInfo.OtherSideNo) ? "-" : payInfo.OtherSideNo);
decimal _PaymentAmount = (payInfo.PayMoney / 100) * payInfo.PayPercentage;//此次付款金额
decimal _CNYSubTotalAmount = _PaymentAmount * payInfo.DayRate;//此次付款金额
_CNYSubTotalAmount = Convert.ToDecimal(_CNYSubTotalAmount.ToString("#0.00"));
var childInfo = new Group_DailyFeePaymentContentInfolView()
{
Id = payInfo.Id,
Payee = payeeStr,
RowNumber = rouNumber,
Applicant = userDatas.Find(it => it.Id == payInfo.CreateUserId)?.CnName ?? "",
ApplicantDt = payInfo.CreateTime.ToString("yyyy-MM-dd HH:mm:ss"),
PayType = setDatas.Find(it => it.Id == payInfo.PayDId)?.Name ?? "",
TransferMark = orbitalPrivateTransfer,
PriceName = priName,
ModuleName = setDatas.Find(it => it.Id == payInfo.CTable)?.Name ?? "",
PayCurrCode = setDatas.Find(it => it.Id == payInfo.PaymentCurrency)?.Name ?? "",
PaymentAmount = _PaymentAmount,
PayRate = payInfo.DayRate,
CNYSubTotalAmount = _CNYSubTotalAmount,
AuditStatus = payInfo.IsAuditGM
};
string remaksDescription = $"【{childInfo.PayType}】【{childInfo.ModuleName}】{rouNumber}、[申请人：{childInfo.Applicant}]{priName}[收款方：{childInfo.Payee}] {childInfo.PayCurrCode} {_PaymentAmount.ToString("#0.00")}、CNY：{childInfo.CNYSubTotalAmount.ToString("#0.00")}(团组：{groupInfo.TeamName})";
childInfo.RemaksDescription = remaksDescription;
childList.Add(childInfo);
rouNumber++;
}
}
CompanyInfo companyInfo = new CompanyInfo();
companyInfo = ExpenseCompanyByTeamId(groupInfo.TeamDid);
dataList.Add(new tree_Group_DailyFeePaymentPageListView()
{
Id = Guid.NewGuid().ToString("N"),
GroupName = groupInfo.TeamName,
CompanyId = companyInfo.Id,
ConpanyName = companyInfo.ConpanyName,
CNYTotalAmount = childList.Sum(it => it.CNYSubTotalAmount),
ChildList = childList,
});
}
}
var dataList1 = new List<tree_Group_DailyFeePaymentPageListView>();
//移除没有数据的团
foreach (var item in dataList)
{
if (item.CNYTotalAmount > 0 && item.ChildList.Count > 0)
{
dataList1.Add(item);
}
}
_DailyFeePaymentResult.dataList = dataList1;
jw.Code = StatusCodes.Status200OK;
jw.Msg = $"操作成功!";
jw.Data = _DailyFeePaymentResult;
return Ok(jw);
}
#endregion
}
}