using Aspose.Cells;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using OASystem.API.OAMethodLib;
using OASystem.API.OAMethodLib.File;
using OASystem.Domain;
using OASystem.Domain.Dtos.Financial;
using OASystem.Domain.Dtos.Groups;
using OASystem.Domain.Entities.Financial;
using OASystem.Domain.Entities.Groups;
using OASystem.Domain.ViewModels.Financial;
using OASystem.Domain.ViewModels.Groups;
using OASystem.Domain.ViewModels.SmallFun;
using OASystem.Infrastructure.Repositories.Financial;
using OASystem.Infrastructure.Repositories.Groups;
using SqlSugar;
using StackExchange.Redis;
using System.Data;
using static OpenAI.GPT3.ObjectModels.SharedModels.IOpenAiModels;
using System.Web;
using Aspose.Words;
using NPOI.HSSF.Util;
using OASystem.Domain.Entities.Customer;
using System.IO.Compression;
using NPOI.SS.UserModel;
using System.Net.Http;
using Google.Protobuf;
using NPOI.SS.Formula.Functions;
using System.Globalization;
using NPOI.POIFS.Properties;
using SixLabors.ImageSharp.ColorSpaces;
using OASystem.Domain.ViewModels.QiYeWeChat;
using System.Diagnostics;
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 DailyFeePaymentRepository _daiRep; //日付申请仓库
private readonly TeamRateRepository _teamRateRep; //团组汇率仓库
private readonly ForeignReceivablesRepository _ForForeignReceivablesRep; //对外收款账单仓库
private readonly ProceedsReceivedRepository _proceedsReceivedRep; //已收款项仓库
private readonly PaymentRefundAndOtherMoneyRepository _paymentRefundAndOtherMoneyRep; //收款退还与其他款项 仓库
private readonly DelegationInfoRepository _delegationInfoRep; //团组信息 仓库
/// <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)
{
_mapper = mapper;
_config = configuration;
_daiRep = daiRep;
_sqlSugar = sqlSugar;
_setDataTypeRep = setDataTypeRep;
_teamRateRep = teamRateRep;
_ForForeignReceivablesRep = ForForeignReceivablesRep;
_proceedsReceivedRep = proceedsReceivedRep;
_paymentRefundAndOtherMoneyRep = paymentRefundAndOtherMoneyRep;
_httpClient = httpClient;
_delegationInfoRep = delegationInfoRep;
}
#region 日付申请
/// <summary>
/// 获取日付申请 基础数据源
/// </summary>
/// <param name="dto"> 日付申请 分页 dto</param>
/// <returns></returns>
[HttpPost]
[ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
public async Task<IActionResult> PostPageSearchDailyPaymentPriceTypeData(PortDtoBase dto)
{
var result = await _daiRep.GetPagePriceTypeData(dto);
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));
}
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));
}
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)
{
var result = await _daiRep.Del(dto);
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.DelDailyPaymentAudit(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));
}
#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()
{
try
{
Result ffrData = await _ForForeignReceivablesRep.GetDataSource();
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> 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()
{
try
{
Result ffrData = await _ForForeignReceivablesRep.PostDataSource();
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));
}
}
/// <summary>
/// 对外收款账单
/// 账单详情
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
[HttpPost]
[ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
public async Task<IActionResult> PostGroupReceivablesInfoByDiId(ForForeignReceivablesNewDto dto)
{
try
{
Result ffrData = await _ForForeignReceivablesRep.PostGroupReceivablesInfoByDiId(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));
}
}
/// <summary>
/// 对外收款账单
/// 添加 And 更新
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
[HttpPost]
[ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
public async Task<IActionResult> PostReceivablesSave(ForeignReceivablesSaveDto dto)
{
try
{
Result ffrData = await _ForForeignReceivablesRep.PostReceivablesSave(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> PostReceivablesDel(ForeignReceivablesDelDto dto)
{
try
{
Result ffrData = await _ForForeignReceivablesRep.PostReceivablesDel(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>
/// 已收账单
/// 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 _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.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)
{
items += fr.PriceName + " " + fr.Currency + " " + 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";
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).ToList();
if (_EnterExitCosts == null)
{
return Ok(JsonView(false, "该团组未填写出入境费用；"));
}
//数据源
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(); //培训费
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,
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);//出访国家
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;
//计算费用总和
decimal airPrice = client.AirType == 460 ? _EnterExitCosts.AirJJ : _EnterExitCosts.AirGW;
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.Service;
WordAllPrice += AllPrice;
UsersTop += firstName + "出访费用为￥" + AllPrice.ToString("#0.00") + "元、";
TeableBookmarkArr.Add("jp", (client.AirType == 460 ? _EnterExitCosts.AirJJ : _EnterExitCosts.AirGW).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 + "晚");
SetCells(ChildTable, doc, rowIndex, 2, item.Cost + item.Currency + "/晚");
SetCells(ChildTable, doc, rowIndex, 3, " 汇率" + (item.SubTotal / item.Cost).ToString("#0.00"));
SetCells(ChildTable, doc, rowIndex, 4, "CNY" + item.SubTotal * days + "\r\n");
rowIndex++;
zsinfo += item.Place + " " + days + "晚 " + item.Cost + item.Currency + "/晚" + " 汇率" + (item.SubTotal / item.Cost).ToString("#0.00") + " 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 + "天");
SetCells(ChildTable1, doc, rowIndex, 2, item.Cost + item.Currency + "/天");
SetCells(ChildTable1, doc, rowIndex, 3, " 汇率" + (item.SubTotal / item.Cost).ToString("#0.00"));
SetCells(ChildTable1, doc, rowIndex, 4, "CNY" + item.SubTotal * days);
rowIndex++;
hsinfo += item.Place + " " + days + "天 " + item.Cost + item.Currency + "/天" + " 汇率" + (item.SubTotal / item.Cost).ToString("#0.00") + " 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 + "天");
SetCells(ChildTable2, doc, rowIndex, 2, item.Cost + item.Currency + "/天");
SetCells(ChildTable2, doc, rowIndex, 3, " 汇率" + (item.SubTotal / item.Cost).ToString("#0.00"));
SetCells(ChildTable2, doc, rowIndex, 4, "CNY" + item.SubTotal * days + "\r\n");
rowIndex++;
gzinfo += item.Place + " " + days + "天 " + item.Cost + item.Currency + "/天" + " 汇率" + (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); //公杂费详情
TeableBookmarkArr.Add("qt", (_EnterExitCosts.Visa + _EnterExitCosts.Safe).ToString("#0.00") + " 元");//其他费用
TeableBookmarkArr.Add("qtinfo", $"（签证费{_EnterExitCosts.Visa.ToString("#0.00")}元、保险{_EnterExitCosts.Safe.ToString("#0.00")}元等费用）");//其他费用第二列
TeableBookmarkArr.Add("fw", _EnterExitCosts.Service.ToString("#0.00") + "元/人");//服务费
TeableBookmarkArr.Add("AllPrice", AllPrice.ToString("#0.00") + "元/人");//表格合计费用
string airStr = client.AirType == 460 ? "经济舱" : client.AirType == 458 ? "公务舱" : "";
TeableBookmarkArr.Add("title", $"费用清单-{airStr}（{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}.docx";
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));
}
}
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();
//新建一个段落
Paragraph p = new Paragraph(doc);
var r = new Run(doc, val);
r.Font.Size = 8;
//把设置的值赋给之前新建的段落
p.AppendChild(r);
//将此段落加到单元格内
lshCell.AppendChild(p);
}
#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)
{
try
{
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;"));
}
}
catch (Exception ex)
{
return Ok(JsonView(false, ex.Message));
}
}
/// <summary>
/// 收款退还与其他款项
/// 删除
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
[HttpPost]
[ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
public async Task<IActionResult> PostPaymentRefundAndOtherMoneyDel(PaymentRefundAndOtherMoneyDelDto dto)
{
try
{
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, "操作成功!"));
}
catch (Exception ex)
{
return Ok(JsonView(false, ex.Message));
}
}
/// <summary>
/// 收款退还与其他款项
/// Info Data Source
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
[HttpPost]
[ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
public async Task<IActionResult> PostPaymentRefundAndOtherMoneyInfoDataSource(PortDtoBase dto)
{
try
{
Result _result = await _paymentRefundAndOtherMoneyRep._InfoDataSource(dto);
if (_result.Code != 0)
{
return Ok(JsonView(false, _result.Msg));
}
return Ok(JsonView(true, "查询成功!", _result.Data));
}
catch (Exception ex)
{
return Ok(JsonView(false, ex.Message));
}
}
/// <summary>
/// 收款退还与其他款项
/// Info
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
[HttpPost]
[ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
public async Task<IActionResult> PostPaymentRefundAndOtherMoneyInfo(PaymentRefundAndOtherMoneyInfoDto dto)
{
try
{
if (dto == null)
{
return Ok(JsonView(false, "参数不能为空!"));
}
Result _result = await _paymentRefundAndOtherMoneyRep._Info(dto);
if (_result.Code != 0)
{
return Ok(JsonView(false, _result.Msg));
}
return Ok(JsonView(true, "查询成功!", _result.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> PostPaymentRefundAndOtherMoneyAddOrEdit(PaymentRefundAndOtherMoneyAddOrEditDto dto)
{
try
{
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 修改!"));
}
Result _result = await _paymentRefundAndOtherMoneyRep._AddOrEdit(dto);
if (_result.Code != 0)
{
return Ok(JsonView(false, _result.Msg));
}
return Ok(JsonView(true, "操作成功!"));
}
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> 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);
}
}
#endregion
string sql = string.Format(@" select distinct fr.diid,di.TeamName,di.ClientUnit,di.VisitDate from Fin_ForeignReceivables fr join Grp_DelegationInfo di on fr.DIID = di.id {0} ", sqlWhere);
List<PostSyntheticalReceivableByDateRangeView> list_rst = _sqlSugar.SqlQueryable<PostSyntheticalReceivableByDateRangeView>(sql).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_other = 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_OtherPrice where diid = {0} and isdel = 0 and RefundType = 1 and PayType=1 ", diId);
List<Fin_OtherPrice> list_other = _sqlSugar.SqlQueryable<Fin_OtherPrice>(sql_other).ToList();
sum_other = list_other.Sum(s => s.Price);
//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.RMBPrice);
item_rst.frPrice = (sum_fr + sum_extra).ToString("#0.00");
item_rst.prPrice = (sum_pr - sum_other).ToString("#0.00");
item_rst.balPrice = ((sum_fr + sum_extra) - (sum_pr - sum_other)).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_other);
sumAll_balance += ((sum_fr + sum_extra) - (sum_pr - sum_other));
}
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.Received = item.prPrice;
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());
}
tree_Fin_DailyFeePaymentResult dailyResult = PayRequest_DailyByDateRange(dto.Status, checkedView.DailyPaymentIds, dto.beginDt, dto.endDt);
tree_Group_DailyFeePaymentResult groupResult = PayRequest_GroupPaymentByDateRange(dto.Status, checkedView.GroupIds, 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, // 四川-赛事项目收入
};
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, 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 (_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 {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>().ToList();
var hotelDatas = _sqlSugar.Queryable<Grp_HotelReservations>().Where(it => it.IsDel == 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 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)
{
if (payInfo.OrbitalPrivateTransfer == 0) //公转
{
priName = $"【{orbitalPrivateTransfer}】【导游： {opData.ServiceGuide} 】{opData.Area}";
}
else if (payInfo.OrbitalPrivateTransfer == 1) //私转
{
priName = $"【{orbitalPrivateTransfer}】【导游：{opData.ServiceGuide}】{opData.Area}";
}
}
break;
case 80: // 80 签证
priName = visaDatas.Find(it => payInfo.DIId == it.DIId && payInfo.CId == it.Id)?.VisaClient ?? "-";
break;
case 81: // 81 邀请/公务活动
priName = ioaDatas.Find(it => payInfo.DIId == it.DiId && payInfo.CId == it.Id)?.Inviter ?? "-";
break;
case 82: // 82 团组客户保险
priName = insureDatas.Find(it => payInfo.DIId == it.DiId && payInfo.CId == it.Id)?.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);
}
var childInfo = new Group_DailyFeePaymentContentInfolView()
{
IsChecked = status1,
Id = payInfo.Id,
Payee = payInfo.Payee,
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 = payInfo.PayMoney,
PayRate = payInfo.DayRate,
CNYSubTotalAmount = ((payInfo.DayRate * payInfo.PayMoney) / 100) * payInfo.PayPercentage //此次付款金额
};
string remaksDescription = $"【{childInfo.PayType}】【{childInfo.ModuleName}】{rouNumber}、[申请人：{childInfo.Applicant}][收款方：{childInfo.Payee}]{priName}[{payInfo.ConsumptionPatterns}] {childInfo.PayCurrCode} {payInfo.PayMoney.ToString("#0.00")}、CNY：{childInfo.CNYSubTotalAmount.ToString("#0.00")}";
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>();
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 == 62 ? "公转" : sd_transfer.STid == 63 ? "私转" : "";
}
}
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 == 62 ? "公转" : sd_transfer.STid == 63 ? "私转" : "";
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.00")}(单价：{subItem.Price.ToString("#0.00")} * {subItem.Quantity})";
subItem.RemaksDescription = remaksDescription;
string excelRemaksDescription = $"【{item.priceTypeStr}】{item.Instructions}({subItem.PriceName}) CNY：{subItem.ItemTotal.ToString("#0.00")}(单价：{subItem.Price.ToString("#0.00")} * {subItem.Quantity})【申请人：{item.CreateUser} 申请时间：{item.CreateTime.ToString("yyyy-MM-dd HH:mm:ss")}】";
subItem.ExcelRemaksDescription = excelRemaksDescription;
rowNumber++;
}
}
decimal total_gz = DailyFeePaymentData.Where(s => s.transferParentId == 62).Sum(d => d.SumPrice ?? 0M);
decimal total_sz = DailyFeePaymentData.Where(s => s.transferParentId == 63).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
try
{
PaymentRequestCheckedView requestCheckedView = new PaymentRequestCheckedView();
List<int> groupIds = new List<int>();
List<int> dailyPaymentIds = 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.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.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, "操作失败!"));
}
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> 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
try
{
var status = _sqlSugar.Updateable<Grp_CreditCardPayment>()
.SetColumns(it => it.DayRate == dto.Rate)
.Where(it => it.Id == dto.Id)
.ExecuteCommand();
if (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, 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, "该项汇率修改失败!"));
}
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> 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参数!"));
//}
try
{
List<int> groupIds = new List<int>();
List<int> dailyPaymentIds = 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.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 groupStatus = _sqlSugar.Updateable<Grp_CreditCardPayment>()
.SetColumns(it => it.IsPay == 1)
.Where(it => groupIds.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();
if (dailyPaymentStatus > 0)
{
changeStatus = true;
}
}
if (changeStatus)
{
_sqlSugar.CommitTran();
return Ok(JsonView(true, "操作成功!"));
}
_sqlSugar.RollbackTran();
return Ok(JsonView(false, "付款状态修改失败!"));
}
catch (Exception ex)
{
_sqlSugar.RollbackTran();
return Ok(JsonView(false, ex.Message));
}
}
/// <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, 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";
}
//日常费用相关
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.PriceDt = DateTime.Parse(dto.PriceDt);
_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
});
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操作失败"));
}
_daiRep.CommitTran();
return Ok(JsonView(true, "操作成功"));
}
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)
{
Grp_TeamRate tr = _daiRep.Query<Grp_TeamRate>(s => s.DiId == costDto.diId && s.CTable == 1015).First();
if (tr != null)
{
if (costDto.currency == 49)
{
dcm_dayrate = tr.RateU;
dcm_rmbPrice = dcm_rmbPrice * tr.RateU;
}
else if (costDto.currency == 51)
{
dcm_dayrate = tr.RateE;
dcm_rmbPrice = dcm_rmbPrice * tr.RateE;
}
}
}
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,
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
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
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 }));
}
#endregion
}
}