OA2023/OASystem/OASystem.Api/Controllers/FinancialController.cs

using Aspose.Cells;
using Aspose.Words;
using Aspose.Words.Drawing;
using EyeSoft.Extensions;
using EyeSoft.IO;
using EyeSoft.Runtime.InteropServices;
using FluentValidation;
using MathNet.Numerics.Distributions;
using Microsoft.EntityFrameworkCore.Query.Internal;
using NPOI.HPSF;
using NPOI.HSSF.UserModel;
using NPOI.SS.Formula.PTG;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using OASystem.API.OAMethodLib;
using OASystem.API.OAMethodLib.File;
using OASystem.API.OAMethodLib.QiYeWeChatAPI.AppNotice;
using OASystem.Domain.Dtos.Financial;
using OASystem.Domain.Dtos.Groups;
using OASystem.Domain.Entities.Customer;
using OASystem.Domain.Entities.Financial;
using OASystem.Domain.Entities.Groups;
using OASystem.Domain.ViewModels.Financial;
using OASystem.Domain.ViewModels.Groups;
using OASystem.Infrastructure.Repositories.Financial;
using OASystem.Infrastructure.Repositories.Groups;
using OfficeOpenXml;
using SqlSugar.Extensions;
using System.Collections;
using System.Data;
using System.Data.OleDb;
using System.Diagnostics;
using System.Globalization;
using System.IO.Compression;
namespace OASystem.API.Controllers
{
    /// <summary>
    /// 财务模块
    /// </summary>
    [Route("api/[controller]/[action]")]
    [ApiController]
    public class FinancialController : ControllerBase
    {
        private readonly IMapper _mapper;
        private readonly IConfiguration _config;
        private readonly SqlSugarClient _sqlSugar;
        private readonly HttpClient _httpClient;
        private readonly SetDataTypeRepository _setDataTypeRep;
        private readonly SetDataRepository _setDataRep;
        private readonly DailyFeePaymentRepository _daiRep;    //日付申请仓库
        private readonly TeamRateRepository _teamRateRep;      //团组汇率仓库
        private readonly ForeignReceivablesRepository _ForForeignReceivablesRep;  //对外收款账单仓库
        private readonly ProceedsReceivedRepository _proceedsReceivedRep;  //已收款项仓库
        private readonly PaymentRefundAndOtherMoneyRepository _paymentRefundAndOtherMoneyRep; //收款退还与其他款项 仓库
        private readonly DelegationInfoRepository _delegationInfoRep; //团组信息 仓库

        /// <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));
            }

            #region 应用推送
            try
            {
                int dailyId = result.Data.GetType().GetProperty("dailyId").GetValue(result.Data, null);
                int sign = result.Data.GetType().GetProperty("sign").GetValue(result.Data, null);

                await AppNoticeLibrary.DailyPayReminders_Create_ToCaiwuChat(dailyId, sign, QiyeWeChatEnum.CaiWuChat);
            }
            catch (Exception ex)
            {
            }
            #endregion


            return Ok(JsonView(true));
        }

        /// <summary>
        /// 日付申请 Update
        /// </summary>
        /// <param name="dto"> 日付申请 修改 dto</param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> PostEditDailyPayment(EditDailyFeePaymentDto dto)
        {
            var result = await _daiRep.Edit(dto);

            if (result == null || result.Code != 0)
            {
                return Ok(JsonView(false, result.Msg));
            }

            #region 应用推送
            try
            {
                int dailyId = result.Data.GetType().GetProperty("dailyId").GetValue(result.Data, null);
                int sign = result.Data.GetType().GetProperty("sign").GetValue(result.Data, null);

                await AppNoticeLibrary.DailyPayReminders_Create_ToCaiwuChat(dailyId, sign, QiyeWeChatEnum.CaiWuChat);
            }
            catch (Exception ex)
            {
            }
            #endregion

            return Ok(JsonView(true));
        }

        /// <summary>
        /// 日付申请 Del
        /// </summary>
        /// <param name="dto"> 日付申请 删除 dto</param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> PostDelDailyPayment(DelDailyFeePaymentDto dto)
        {
            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.DailyPaymentAudit(dto);
            if (result == null || result.Code != 0)
            {
                return Ok(JsonView(false, result.Msg));
            }
            return Ok(JsonView(true));
        }

        /// <summary>
        /// 日付申请 Single Excel Download
        /// </summary>
        /// <param name="dto"> dto </param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> PostExcelDailyPaymentDownload(SearchDailyFeePaymentDto dto)
        {
            if (dto.PortType == 1 || dto.PortType == 2)
            {
                Fin_DailyFeePaymentInfolView feeData = new Fin_DailyFeePaymentInfolView();

                string feeSql = string.Format(@"Select * From Fin_DailyFeePayment 
                                                Where IsDel=0 And Id = {0} ", dto.Id);
                feeData = await _sqlSugar.SqlQueryable<Fin_DailyFeePaymentInfolView>(feeSql).FirstAsync();
                if (feeData == null)
                {
                    return Ok(JsonView(false, "暂无数据!"));
                }

                string feeContentSql = string.Format(@"Select * From Fin_DailyFeePaymentContent 
                                                        Where IsDel=0 And DFPId = {0} ", dto.Id);
                feeData.FeeContents = await _sqlSugar.SqlQueryable<Fin_DailyFeePaymentContentInfolView>(feeContentSql).ToListAsync();

                if (feeData != null)
                {

                    string userName = string.Empty;
                    string userSql = string.Format("Select * From  Sys_Users Where Id={0} And Isdel = {1}", feeData.CreateUserId, 0);
                    Sys_Users user = await _sqlSugar.SqlQueryable<Sys_Users>(userSql).FirstAsync();
                    if (user != null) { userName = user.CnName; }

                    var setData = _setDataTypeRep.QueryDto<Sys_SetData, Fin_DailyFeePaymentPagePriceSubTypeView>().ToList();
                    //48人员费用  49办公费用 50 销售费用 51 其他费用 55 大运会
                    var priceSubTypeData = setData.Where(s => s.STid == 55).ToList();

                    Dictionary<string, object> pairs = new Dictionary<string, object>();
                    List<DataTable> datas = new List<DataTable>();

                    //if (priceSubTypeData.Where(s => s.Id == feeData.PriceTypeId).ToList().Count() > 0)//大运会专属模板
                    //{

                    //    //AsposeHelper.ExpertExcelToModel("日常费用付款申请模板-大运会数据.xls", "DailyPayment", "大运会所有日常费用付款申请.xls",
                    //    //    pairs, datas);
                    //}
                    //else  //日付常规模板
                    //{
                    pairs.Clear();
                    pairs.Add("Opertor", userName);
                    pairs.Add("DateTime", feeData.CreateTime.ToString("yyyy-MM-dd HH:mm:ss"));
                    pairs.Add("FAuditStatus", feeData.FAuditDesc);
                    pairs.Add("MAuditStatus", feeData.MAuditDesc);
                    pairs.Add("SumPrice", feeData.SumPrice);

                    DataTable data = AsposeHelper.ListToDataTable("DailyFeePayment", feeData.FeeContents);
                    datas.Clear();
                    datas.Add(data);
                    string fileName = string.Format("{0}-日常费用付款申请.xlsx", feeData.Instructions);
                    string msg = AsposeHelper.ExpertExcelToModel("日常费用付款申请模板.xlsx", "DailyPayment", fileName, pairs, datas);

                    return Ok(JsonView(true, msg));
                    //}

                }
                else
                {
                    return Ok(JsonView(false, "暂无数据!"));
                }
            }


            return Ok(JsonView(true));
        }

        /// <summary>
        /// 日付申请 
        /// 总经理未审核 日付信息
        /// </summary>
        /// <param name="dto"> dto </param>
        /// <returns></returns>
        [HttpGet]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> DailyPaymentGMUnAudited()
        {
            var dailyInfos = await _sqlSugar.Queryable<Fin_DailyFeePayment>()
                                             .LeftJoin<Sys_Users>((dfp, u) => dfp.CreateUserId == u.Id)
                                             .Where(dfp => dfp.IsDel == 0 &&
                                                           //dfp.FAudit == 1 &&
                                                           //dfp.IsPay == 0 && 
                                                           dfp.MAudit == 0
                                                   )
                                             .OrderBy(dfp => dfp.CreateTime, OrderByType.Desc)
                                             //.OrderBy(dfp => dfp.FAudit, OrderByType.Desc)
                                             .Select((dfp, u) => new
                                             {
                                                 id = dfp.Id,
                                                 amountName = dfp.Instructions,
                                                 amount = dfp.SumPrice,
                                                 fAuditStatus = dfp.FAudit == 1 ? "审核通过" : 
                                                                dfp.FAudit == 2 ? "审核未通过" : "未审核",
                                                 fAuditDate = dfp.FAuditDate,
                                                 applicant = u.CnName,
                                                 applicantDate = dfp.CreateTime
                                             })
                                             //.ToListAsync();
                                             .CountAsync();
            //int count = dailyInfos.Count;
            return Ok(JsonView(true,"查询成功", dailyInfos));
        }

        #endregion

        #region 团组提成
        /// <summary>
        /// 提成 Page Search
        /// </summary>
        /// <param name="dto"> 提成 分页 dto</param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> PostPageSearchCommissionList(GroupCommissionDto dto)
        {
            var data = await GroupCommission.GetCommissionPageList(dto);

            return Ok(JsonView(data.Data));
        }
        #endregion

        #region 团组汇率

        /// <summary>
        /// 团组汇率 Select数据源(团组列,汇率列)
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> GetGroupRateDataSources(TeamRateDto dto)
        {
            try
            {
                //迁移数据更新团组汇率
                //Result teamRateData1 = await _teamRateRep.GetGroupRateChangeData();

                //var data = await _teamRateRep.PostGroupTeamRateHot();


                Stopwatch stopwatch = Stopwatch.StartNew();

                GroupNameDto groupNameDto = new GroupNameDto() { PortType = dto.PortType };
                var groups = await _delegationInfoRep.GetGroupNameList(groupNameDto);

                List<CurrencyHot> _currData = new List<CurrencyHot>();

                string currData = await RedisRepository.RedisFactory.CreateRedisRepository().StringGetAsync<string>("GroupTeamCurrencyData");//string 取

                if (!string.IsNullOrEmpty(currData))
                {
                    _currData = JsonConvert.DeserializeObject<List<CurrencyHot>>(currData);
                }
                else
                {
                    _currData = await _teamRateRep.PostGroupTeamRateHot();

                    //过期时间 25 Hours
                    TimeSpan ts = DateTime.Now.AddHours(25).TimeOfDay;
                    await RedisRepository.RedisFactory.CreateRedisRepository().StringSetAsync("GroupTeamCurrencyData", JsonConvert.SerializeObject(_currData), ts);
                }

                var _data = new { GroupData = groups.Data, TeamRateData = _currData };

                stopwatch.Stop();

                return Ok(JsonView(true, $"查询成功!耗时：{stopwatch.ElapsedMilliseconds / 1000}s", _data));

            }
            catch (Exception ex)
            {
                return Ok(JsonView(false, ex.Message));
            }
        }

        ///// <summary>
        ///// 团组汇率 changge
        ///// </summary>
        ///// <returns></returns>
        //[HttpPost]
        //[ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        //public async Task<IActionResult> ChangeGroupRateInfo()
        //{
        //    try
        //    {
        //        Result teamRateData = await _teamRateRep.GetGroupRateChangeData();
        //        if (teamRateData.Code != 0)
        //        {
        //            return Ok(JsonView(false, teamRateData.Msg));
        //        }
        //        return Ok(JsonView(true, teamRateData.Msg, teamRateData.Data));
        //    }
        //    catch (Exception ex)
        //    {
        //        return Ok(JsonView(false, ex.Message));
        //        throw;
        //    }
        //}

        /// <summary>
        /// 团组汇率 Select汇率详情
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> GetGroupRateInfo(TeamRateInfoDto dto)
        {
            try
            {
                Result teamRateData = await _teamRateRep.GetGroupRateInfoByDiid(dto);
                if (teamRateData.Code != 0)
                {
                    return Ok(JsonView(false, teamRateData.Msg));
                }
                return Ok(JsonView(true, teamRateData.Msg, teamRateData.Data));
            }
            catch (Exception ex)
            {
                return Ok(JsonView(false, ex.Message));
                throw;
            }
        }


        /// <summary>
        /// 团组汇率 添加 or 更新
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> PostGroupRateUpdate(TeamRateUpdateDto dto)
        {
            try
            {
                Result teamRateData = await _teamRateRep.PostGroupRateUpdate(dto);
                if (teamRateData.Code != 0)
                {
                    return Ok(JsonView(false, teamRateData.Msg));
                }
                return Ok(JsonView(true, teamRateData.Msg, teamRateData.Data));
            }
            catch (Exception ex)
            {
                return Ok(JsonView(false, ex.Message));
                throw;
            }
        }

        #endregion

        #region 对外收款账单 关联已收款项

        /// <summary>
        /// 对外收款账单 Select数据源(团组名,币种,汇款方式)
        /// 关联已收款项
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> GetForeignReceivablesDataSources(ForeignReceivablesDataSourcesDto dto)
        {
            return Ok(await _ForForeignReceivablesRep.GetDataSource(dto));
        }

        /// <summary>
        /// 对外收款账单 
        /// 账单详情
        /// 关联已收款项
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> GetGroupReceivablesInfoByDiId(ForForeignReceivablesInfoDto dto)
        {
            try
            {
                Result ffrData = await _ForForeignReceivablesRep.GetGroupReceivablesInfoByDiId(dto);
                if (ffrData.Code != 0)
                {
                    return Ok(JsonView(false, ffrData.Msg));
                }
                return Ok(JsonView(true, ffrData.Msg, ffrData.Data));
            }
            catch (Exception ex)
            {
                return Ok(JsonView(false, ex.Message));
                throw;
            }
        }

        /// <summary>
        /// 对外收款账单 
        /// 账单 删除
        /// 关联已收款项
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> PostGroupReceivablesDel(DelForForeignReceivablesInfoDto dto)
        {
            try
            {
                Result ffrData = await _ForForeignReceivablesRep._Del(dto);
                if (ffrData.Code != 0)
                {
                    return Ok(JsonView(false, ffrData.Msg));
                }
                return Ok(JsonView(true, ffrData.Msg, ffrData.Data));
            }
            catch (Exception ex)
            {
                return Ok(JsonView(false, ex.Message));
                throw;
            }
        }

        /// <summary>
        /// 对外收款账单 
        /// 添加 And 更新
        /// 关联已收款项
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> PostReceivablesOperate(ForeignReceivablesAddAndUpdateDto dto)
        {
            try
            {
                Result ffrData = await _ForForeignReceivablesRep.PostReceivablesOperate(dto);
                if (ffrData.Code != 0)
                {
                    return Ok(JsonView(false, ffrData.Msg));
                }
                return Ok(JsonView(true, ffrData.Msg, ffrData.Data));
            }
            catch (Exception ex)
            {
                return Ok(JsonView(false, ex.Message));
                throw;
            }
        }

        /// <summary>
        /// 已收款项 
        /// 账单 删除
        /// 关联已收款项
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> PostAmountReceivedDel(ProceedsReceivedDelDto dto)
        {
            try
            {
                Result ffrData = await _proceedsReceivedRep._Del(dto);
                if (ffrData.Code != 0)
                {
                    return Ok(JsonView(false, ffrData.Msg));
                }
                return Ok(JsonView(true, ffrData.Msg, ffrData.Data));
            }
            catch (Exception ex)
            {
                return Ok(JsonView(false, ex.Message));
                throw;
            }
        }

        /// <summary>
        /// 已收款项 
        /// 添加 And 更新
        /// 关联已收款项
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> PostAmountReceivedOperate(ProceedsReceivedDto dto)
        {
            try
            {
                Result ffrData = await _proceedsReceivedRep.PostAmountReceivedOperate(dto);
                if (ffrData.Code != 0)
                {
                    return Ok(JsonView(false, ffrData.Msg));
                }
                return Ok(JsonView(true, ffrData.Msg, ffrData.Data));
            }
            catch (Exception ex)
            {
                return Ok(JsonView(false, ex.Message));
                throw;
            }
        }

        /// <summary>
        /// 财务 已收款项
        /// 分配已收款项至 应收项下
        /// 关联已收款项
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> PostAllocateAmountReceived(AllocateAmountReceivedDto dto)
        {
            try
            {
                Result ffrData = await _proceedsReceivedRep.PostAllocateAmountReceived(dto);
                if (ffrData.Code != 0)
                {
                    return Ok(JsonView(false, ffrData.Msg));
                }
                return Ok(JsonView(true));
            }
            catch (Exception ex)
            {
                return Ok(JsonView(false, ex.Message));
                throw;
            }
        }

        ///// <summary>
        ///// 财务 收款账单
        ///// 导出Word(北京,四川)
        ///// </summary>
        ///// <param name="dto"></param>
        ///// <returns></returns>
        //[HttpPost]
        //[ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        //public async Task<IActionResult> PostAccountReceivableWordExport(AccountReceivableWordExportDto dto)
        //{
        //    try
        //    {
        //        //模板处理
        //        string typeName = string.Empty;
        //        if (dto.TemplateType == 1) //四川
        //        {
        //            typeName = "四川";
        //        }
        //        else if (dto.TemplateType == 2) //北京 
        //        {
        //            typeName = "北京";
        //        }
        //        else return Ok(JsonView(false,"请选择正确的模板类型!"));

        //        string wordTempName = string.Format("收款账单({0})模板.doc", typeName);


        //    }
        //    catch (Exception ex)
        //    {
        //        return Ok(JsonView(false, ex.Message));
        //    }
        //}
        #endregion

        #region 对外收款账单

        /// <summary>
        /// 对外收款账单 
        /// 数据源
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> PostGroupReceivablesDataSource(ForeignReceivablesDataSourcesDto dto)
        {
            return Ok(await _ForForeignReceivablesRep.PostDataSource(dto));
        }

        /// <summary>
        /// 对外收款账单 
        /// 账单详情
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> PostGroupReceivablesInfoByDiId(ForForeignReceivablesNewDto dto)
        {
            return Ok(await _ForForeignReceivablesRep.PostGroupReceivablesInfoByDiId(dto));
        }

        /// <summary>
        /// 对外收款账单 
        /// 添加 And 更新
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> PostReceivablesSave(ForeignReceivablesSaveDto dto)
        {
           
            return Ok(await _ForForeignReceivablesRep.PostReceivablesSave(dto));
           
        }

        /// <summary>
        /// 对外收款账单 
        /// 审核
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> PostReceivablesAudit(FeeAuditDto dto)
        {
            return Ok(await _ForForeignReceivablesRep.FeeAudit(dto));
        }

        /// <summary>
        /// 已收账单 
        /// 删除
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> PostReceivablesDel(ForeignReceivablesDelDto dto)
        {
            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
        /// Init 
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> PostReceivablesFeilDownloadInit()
        {
            return Ok(JsonView(true, "操作成功！", new List<dynamic> {
                new { Id = 1, Name = "生成收款单(四川)" },
                new { Id = 2, Name = "生成收款单(北京)" },
                new { Id = 3, Name = "汇款账单" }
            }
            ));
        }

        /// <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.Replace("|", "、").ToString();
                    }

                    //付款日期
                    if (doc.Range.Bookmarks["PayDate"] != null)
                    {
                        Bookmark mark = doc.Range.Bookmarks["PayDate"];
                        mark.Text = _ForeignReceivables[0].PayDate == null ? "" : Convert.ToDateTime(_ForeignReceivables[0].PayDate).ToString("yyyy年MM月dd日");
                    }

                    decimal sumPrice = 0;
                    //各项费用
                    if (doc.Range.Bookmarks["PayItemContent"] != null)
                    {
                        string items = "";
                        foreach (var fr in _ForeignReceivables)
                        {
                            var currInfo = _sqlSugar.Queryable<Sys_SetData>().Where(it => it.Id == fr.Currency).First();

                            items += $"{fr.PriceName}   {currInfo?.Name} {fr.Price.ToString("#0.00")}  *  {fr.Count} {fr.Unit} * {fr.Rate}.................. RMB {fr.ItemSumPrice.ToString("#0.00")}\n";
                            sumPrice += fr.ItemSumPrice;
                        }
                        Bookmark mark = doc.Range.Bookmarks["PayItemContent"];
                        mark.Text = items;
                    }
                    //合计
                    if (doc.Range.Bookmarks["Total"] != null)
                    {
                        Bookmark mark = doc.Range.Bookmarks["Total"];
                        mark.Text = sumPrice.ToString("#0.00");
                    }

                    #endregion

                    //文件名
                    string strFileName = _DelegationInfo.TeamName + "-收款账单.docx";
                    //去水印
                    new Aspose.Words.License().SetLicense(new MemoryStream(Convert.FromBase64String(AsposeHelper.asposeKey)));
                    doc.Save(AppSettingsHelper.Get("WordBasePath") + "ForeignReceivables/File/" + strFileName);
                    string url = AppSettingsHelper.Get("WordBaseUrl") + "Office/Word/ForeignReceivables/File/" + strFileName;

                    return Ok(JsonView(true, "成功", new { Url = url }));
                }
                else if (dto.FileType == 3) //汇款通知
                {
                    var _EnterExitCosts = _sqlSugar.Queryable<Grp_EnterExitCost>().Where(it => it.IsDel == 0 && it.DiId == dto.DiId).First();
                    var _DayAndCosts = _sqlSugar.Queryable<Grp_DayAndCost>().Where(it => it.IsDel == 0 && it.DiId == dto.DiId && it.NationalTravelFeeId > 0).ToList();
                    if (_EnterExitCosts == null)
                    {
                        return Ok(JsonView(false, "该团组未填写出入境费用；"));
                    }

                    var _cityFee = _sqlSugar.Queryable<Grp_NationalTravelFee>().Where(it => it.IsDel == 0).ToList();

                    foreach (var item in _DayAndCosts)
                    {
                        var cityInfo = _cityFee.Where(it => it.Id == item.NationalTravelFeeId).FirstOrDefault();
                        if (cityInfo != null)
                        {
                            if (cityInfo.City.Contains("全部城市") || cityInfo.City.Contains("其他城市") || cityInfo.City.Contains("所有城市"))
                            {
                                item.Place = cityInfo.Country;
                            }
                            else item.Place = cityInfo.City;
                        }
                    }

                    //数据源
                    List<Grp_DayAndCost> dac1 = _DayAndCosts.Where(it => it.Type == 1).ToList(); //住宿费
                    List<Grp_DayAndCost> dac2 = _DayAndCosts.Where(it => it.Type == 2).ToList(); //伙食费
                    List<Grp_DayAndCost> dac3 = _DayAndCosts.Where(it => it.Type == 3).ToList(); //公杂费
                    List<Grp_DayAndCost> dac4 = _DayAndCosts.Where(it => it.Type == 4).ToList(); //培训费

                    //币种Data
                    var currData = _sqlSugar.Queryable<Sys_SetData>().Where(it => it.IsDel == 0 && it.STid == 66).ToList();

                    var DeleClientList = _sqlSugar.Queryable<Grp_TourClientList>()
                                                  .LeftJoin<Crm_DeleClient>((tcl, dc) => tcl.ClientId == dc.Id && dc.IsDel == 0)
                                                  .LeftJoin<Crm_CustomerCompany>((tcl, dc, cc) => dc.CrmCompanyId == cc.Id && dc.IsDel == 0)
                                                  .Where((tcl, dc, cc) => tcl.IsDel == 0 && tcl.DiId == dto.DiId)
                                                  .Select((tcl, dc, cc) => new
                                                  {
                                                      Name = dc.LastName + dc.FirstName,
                                                      Sex = dc.Sex,
                                                      Birthday = dc.BirthDay,
                                                      Company = cc.CompanyFullName.Replace("\n",""),
                                                      Job = dc.Job,
                                                      AirType = tcl.ShippingSpaceTypeId
                                                  })
                                                  .ToList();
                    if (DeleClientList.Count < 1)
                    {
                        return Ok(JsonView(false, "暂无团组成员,请先填写团组成员！！！"));
                    }
                    var _ClientNames = DeleClientList.Select(x => x.Name).ToList();
                    var _GroupClient = DeleClientList.GroupBy(x => x.Company).ToList();


                    Dictionary<string, string> bookmarkArr = null;

                    string tempPath = AppSettingsHelper.Get("WordBasePath") + $"ForeignReceivables/Temp/汇款通知.docx";
                    //载入模板
                    Document doc = new Document(tempPath);
                    DocumentBuilder builder = null;


                    List<string> filesToZip = new List<string>();

                    foreach (var ClientItem in _GroupClient) //遍历单位
                    {
                        doc = new Document(tempPath);
                        builder = new DocumentBuilder(doc);
                        Paragraph paragraph = new Paragraph(doc);

                        bookmarkArr = new Dictionary<string, string>();
                        bookmarkArr.Add("titleClientUnit", ClientItem.Key); //title单位
                        bookmarkArr.Add("ClientUnit", ClientItem.Key); //单位
                        bookmarkArr.Add("VisitStartDate", _DelegationInfo.VisitStartDate.ToString("yyyy年MM月dd日"));//出发日期
                        bookmarkArr.Add("name", string.Join(",", ClientItem.Select(x => x.Name).ToArray()).TrimEnd(','));//全部人员信息
                        bookmarkArr.Add("VisitCountry", _DelegationInfo.VisitCountry.Replace("|", "、"));//出访国家
                        bookmarkArr.Add("dayTime", DateTime.Now.ToString("yyyy年MM月dd日"));//今天日期

                        var ClientItemList = ClientItem.ToList();
                        string UsersTop = string.Empty;//word中人员以及金额
                        decimal WordAllPrice = 0.00M;

                        //int[] infoColumn = new int[5] {dac1.Select(x=>x.Place.Length).ToList().Max(),2, dac1.Select(x => x.Cost.Length).Max() + dac1.Select(x => x.Currency.Length).Max(),
                        //    6, 3 + dac1.Where(x=>!string.IsNullOrWhiteSpace(x.Place)).Select(x=>(Convert.ToDecimal(x.SubTotal) / Convert.ToDecimal(x.Cost)).ToString().Length).Max() };

                        //遍历人员
                        for (int i = 0; i < ClientItemList.Count(); i++)
                        {
                            Dictionary<string, string> TeableBookmarkArr = new Dictionary<string, string>();
                            var client = ClientItemList[i]; //每个人员
                            var firstName = ClientItemList[i].Name;

                            //计算费用总和
                            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.YiMiao + _EnterExitCosts.YiMiao + _EnterExitCosts.Ticket + _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 + "晚");

                                    string currencyCode = currData.Find(it => it.Id == item.Currency)?.Remark ?? "Unknown";
                                    SetCells(ChildTable, doc, rowIndex, 2, item.Cost.ToString("#0.00") + currencyCode + "/晚");
                                    SetCells(ChildTable, doc, rowIndex, 3, " 汇率" + (item.SubTotal / item.Cost).ToString("#0.00"));
                                    SetCells(ChildTable, doc, rowIndex, 4, "CNY" + item.SubTotal + "\r\n");
                                    rowIndex++;
                                    zsinfo += item.Place + "  " + days + "晚 " + item.Cost.ToString("#0.00") + currencyCode + "/晚" + " 汇率" + (item.SubTotal / item.Cost).ToString("#0.0000") + "   CNY" + item.SubTotal * days + "\r\n";
                                }

                            }
                            placeArr.Clear();
                            rowIndex = 0;
                            foreach (var item in dac2)
                            {
                                if (string.IsNullOrWhiteSpace(item.Place))
                                {
                                    continue;
                                }

                                if (placeArr.Contains(item.Place))
                                {
                                    continue;
                                }
                                else
                                {
                                    placeArr.Add(item.Place);
                                }
                                if (!string.IsNullOrWhiteSpace(item.Place))
                                {
                                    if (rowIndex > ChildTable1.Rows.Count - 1)
                                    {
                                        var chitableRow = ChildTable1.LastRow.Clone(true);
                                        ChildTable1.AppendChild(chitableRow);
                                    }
                                    int days = dac2.FindAll(x => x.Place == item.Place).Count();
                                    SetCells(ChildTable1, doc, rowIndex, 0, item.Place);
                                    SetCells(ChildTable1, doc, rowIndex, 1, days + "天");

                                    string currencyCode = currData.Find(it => it.Id == item.Currency)?.Remark ?? "Unknown";
                                    SetCells(ChildTable1, doc, rowIndex, 2, item.Cost.ToString("#0.00") + currencyCode + "/天");
                                    SetCells(ChildTable1, doc, rowIndex, 3, " 汇率" + (item.SubTotal / item.Cost).ToString("#0.00"));
                                    SetCells(ChildTable1, doc, rowIndex, 4, "CNY" + item.SubTotal);
                                    rowIndex++;
                                    hsinfo += item.Place + "  " + days + "天 " + item.Cost.ToString("#0.00") + currencyCode + "/天" + " 汇率" + (item.SubTotal / item.Cost).ToString("#0.0000") + "   CNY" + item.SubTotal * days + "\r\n";
                                }

                            }
                            placeArr.Clear();
                            rowIndex = 0;
                            foreach (var item in dac3)
                            {
                                if (string.IsNullOrWhiteSpace(item.Place))
                                {
                                    continue;
                                }

                                if (placeArr.Contains(item.Place))
                                {
                                    continue;
                                }
                                else
                                {
                                    placeArr.Add(item.Place);
                                }
                                if (!string.IsNullOrWhiteSpace(item.Place))
                                {
                                    if (rowIndex > ChildTable2.Rows.Count - 1)
                                    {
                                        var chitableRow = ChildTable2.LastRow.Clone(true);
                                        ChildTable2.AppendChild(chitableRow);
                                    }
                                    int days = dac3.FindAll(x => x.Place == item.Place).Count();
                                    SetCells(ChildTable2, doc, rowIndex, 0, item.Place);
                                    SetCells(ChildTable2, doc, rowIndex, 1, days + "天");

                                    string currencyCode = currData.Find(it => it.Id == item.Currency)?.Remark ?? "Unknown";
                                    SetCells(ChildTable2, doc, rowIndex, 2, item.Cost.ToString("#0.00") + currencyCode + "/天");
                                    SetCells(ChildTable2, doc, rowIndex, 3, " 汇率" + (item.SubTotal / item.Cost).ToString("#0.00"));
                                    SetCells(ChildTable2, doc, rowIndex, 4, "CNY" + item.SubTotal + "\r\n");
                                    rowIndex++;
                                    gzinfo += item.Place + "  " + days + "天 " + item.Cost.ToString("#0.00") + currencyCode + "/天" + " 汇率" + (item.SubTotal / item.Cost).ToString("#0.00") + "   CNY" + item.SubTotal * days + "\r\n";
                                }

                            }
                            placeArr.Clear();
                            TeableBookmarkArr.Add("zsinfo", zsinfo); //住宿费详情
                            TeableBookmarkArr.Add("hs", dac2.Sum(x => Convert.ToDecimal(x.SubTotal)).ToString("#0.00") + " 元"); //伙食费
                            TeableBookmarkArr.Add("hsinfo", hsinfo); //伙食费详情
                            TeableBookmarkArr.Add("gz", dac3.Sum(x => Convert.ToDecimal(x.SubTotal)).ToString("#0.00") + " 元"); //公杂费
                            TeableBookmarkArr.Add("gzinfo", gzinfo); //公杂费详情

                            string otherFeestr = "";
                            decimal otherFee = 0.00M;
                            if (_EnterExitCosts.Visa > 0.00M)
                            {
                                otherFee += _EnterExitCosts.Visa;
                                otherFeestr += $"签证费{_EnterExitCosts.Visa.ToString("#0.00")}元、";
                            }
                            if (_EnterExitCosts.YiMiao > 0.00M)
                            {
                                otherFee += _EnterExitCosts.YiMiao;
                                otherFeestr += $"疫苗费{_EnterExitCosts.YiMiao.ToString("#0.00")}元、";
                            }
                            if (_EnterExitCosts.HeSuan > 0.00M)
                            {
                                otherFee += _EnterExitCosts.HeSuan;
                                otherFeestr += $"核酸检测费{_EnterExitCosts.HeSuan.ToString("#0.00")}元、";
                            }
                            if (_EnterExitCosts.Safe > 0.00M)
                            {
                                otherFee += _EnterExitCosts.Safe;
                                otherFeestr += $"保险费{_EnterExitCosts.Safe.ToString("#0.00")}元、";
                            }
                            if (_EnterExitCosts.Ticket > 0.00M)
                            {
                                otherFee += _EnterExitCosts.Ticket;
                                otherFeestr += $"参展门票费{_EnterExitCosts.Ticket.ToString("#0.00")}元、";
                            }
                            if (otherFeestr.Length > 0)
                            {
                                otherFeestr = otherFeestr.Substring(0, otherFeestr.Length - 1);
                                otherFeestr += "等费用";
                            }

                            TeableBookmarkArr.Add("qt", otherFee.ToString("#0.00") + " 元");//其他费用
                            TeableBookmarkArr.Add("qtinfo", otherFeestr);//其他费用第二列

                            TeableBookmarkArr.Add("fw", _EnterExitCosts.Service.ToString("#0.00") + "元/人");//服务费
                            TeableBookmarkArr.Add("AllPrice", AllPrice.ToString("#0.00") + "元/人");//表格合计费用
                            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.Replace("\n", "")}.docx";
                        //去水印
                        new Aspose.Words.License().SetLicense(new MemoryStream(Convert.FromBase64String(AsposeHelper.asposeKey)));
                        doc.Save(filsPath);
                        filesToZip.Add(filsPath);
                        //streams.Add(ClientItem.Key + ".docx", outSteam.ToArray());
                    }

                    //文件名
                    string zipFileName = _DelegationInfo.TeamName + "-收款账单.zip";
                    string zipPath = $"ForeignReceivables/File/{_DelegationInfo.TeamName}-收款账单{DateTime.Now.ToString("yyyyMMddHHmmss")}.zip";
                    try
                    {
                        using (var zip = ZipFile.Open(AppSettingsHelper.Get("WordBasePath") + zipPath, ZipArchiveMode.Create))
                        {
                            foreach (var file in filesToZip)
                            {
                                zip.CreateEntryFromFile(file, Path.GetFileName(file));
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        return Ok(JsonView(false, ex.Message));
                    }

                    string url = AppSettingsHelper.Get("WordBaseUrl") + $"Office/Word/{zipPath}";

                    return Ok(JsonView(true, "成功", new { Url = url }));
                }

                return Ok(JsonView(false, "操作失败！"));
            }
            catch (Exception ex)
            {
                return Ok(JsonView(false, ex.Message));
            }
        }

        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 userSqlWhere = "";
            var userInfos = await _sqlSugar.Queryable<Sys_Users>()
                                           .InnerJoin<Sys_Department>((u, d) => u.DepId == d.Id)
                                           .Where((u, d) => u.IsDel == 0 && d.DepName.Contains("市场部") && u.Id == dto.CurrUserId)
                                           .ToListAsync();

           
            if (userInfos.Count > 0) userSqlWhere = string.Format(@$" And JietuanOperator={dto.CurrUserId} ");


            //排序倒序
            string sql = string.Format(@$"select distinct fr.diid,di.TeamName,di.ClientUnit,di.VisitDate,di.CreateTime from Fin_ForeignReceivables fr join Grp_DelegationInfo di on fr.DIID = di.id {sqlWhere}  {userSqlWhere}  Order By di.VisitDate Desc");

            List<PostSyntheticalReceivableByDateRangeView> list_rst = _sqlSugar.SqlQueryable<PostSyntheticalReceivableByDateRangeView>(sql).ToList();

            var setData = _sqlSugar.Queryable<Sys_SetData>().Where(it => it.IsDel == 0).ToList();

            decimal sumAll_fr = 0M; //应收
            decimal sumAll_pr = 0M; //已收
            decimal sumAll_balance = 0M; //尾款

            if (list_rst.Count > 0)
            {
                int rowNumber = 1;
                foreach (var item_rst in list_rst)
                {
                    DateTime dtTemp;
                    bool b = DateTime.TryParse(item_rst.visitDate, out dtTemp);
                    if (b)
                    {
                        item_rst.visitDate = dtTemp.ToString("yyyy-MM-dd");
                    }


                    item_rst.No = rowNumber;
                    rowNumber++;

                    int diId = item_rst.diid;
                    decimal sum_fr = 0M;
                    decimal sum_pr = 0M;
                    string str_client = string.Empty;
                    decimal sum_refund = 0M; //收款退还
                    decimal sum_extra = 0M; //超支费用
                    decimal balance = 0M;
                    string str_schedule = string.Empty;

                    //1.应收
                    string sql_fr = string.Format(@" Select * From Fin_ForeignReceivables Where IsDel=0 And Diid={0} ", diId);
                    List<Fin_ForeignReceivables> list_fr = _sqlSugar.SqlQueryable<Fin_ForeignReceivables>(sql_fr).ToList();
                    sum_fr = list_fr.Sum(s => s.ItemSumPrice);

                    //2.已收
                    string sql_pr = string.Format(@" Select * From Fin_ProceedsReceived Where IsDel=0 And Diid={0} ", diId);
                    List<Fin_ProceedsReceived> list_pr = _sqlSugar.SqlQueryable<Fin_ProceedsReceived>(sql_pr).ToList();
                    foreach (var item_pr in list_pr)
                    {
                        sum_pr += item_pr.Price;
                        str_client += string.Format(@"{0};", item_pr.Client);
                        str_schedule += string.Format(@"{0};", item_pr.Remark);

                    }
                    if (str_schedule.Length > 0)
                    {
                        str_schedule = str_schedule.TrimEnd(';');
                    }
                    if (str_client.Length > 0)
                    {
                        str_client = str_client.TrimEnd(';');
                    }

                    //3.收款退还
                    string sql_other = string.Format(@"Select * From  Fin_PaymentRefundAndOtherMoney prao
					  Inner Join Grp_CreditCardPayment ccp On prao.Id = ccp.CId
					  Where ccp.CTable = 285 And ccp.IsPay = 1 And prao.IsDel = 0 And ccp.IsDel = 0 And prao.DiId = {0}", diId);
                    List<Grp_CreditCardPayment> list_other = _sqlSugar.SqlQueryable<Grp_CreditCardPayment>(sql_other).ToList();
                    sum_refund = list_other.Sum(s => s.RMBPrice);

                    //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.ToString("#0.00");
                    item_rst.extraPrice = sum_extra.ToString("#0.00");
                    item_rst.receivableTotal = (sum_fr + sum_extra).ToString("#0.00");
                    item_rst.prPrice = sum_pr.ToString("#0.00");
                    item_rst.refundAmount = sum_refund.ToString("#0.00");
                    item_rst.receivedTotal = (sum_pr - sum_refund).ToString("#0.00");
                    item_rst.balPrice = ((sum_fr + sum_extra) - (sum_pr - sum_refund)).ToString("#0.00");
                    item_rst.prClient = str_client;
                    item_rst.schedule = str_schedule;

                    string tempVisitDate = Convert.ToDateTime(item_rst.visitDate).ToString("yyyy-MM-dd");

                    sumAll_fr += (sum_fr + sum_extra);
                    sumAll_pr += (sum_pr - sum_refund);
                    sumAll_balance += ((sum_fr + sum_extra) - (sum_pr - sum_refund));

                    #region 单位应收已收细项（以应收费用名称为主去(已收费用)匹配） 新增 雷怡 2024-5-08 16:35:28

                    List<ClientFeeInfoView> feeDatas = new List<ClientFeeInfoView>();

                    //匹配上的数据
                    foreach (var item in list_fr)
                    {
                        var prInfo = list_pr.Find(it => item.PriceName.Contains(it.Client) || item.PriceName.Equals(it.Client));

                        decimal _balancePayment = 0.00M;
                        if (item.Currency == prInfo?.Currency)
                        {
                            _balancePayment = item.ItemSumPrice - prInfo?.Price ?? 0.00M;
                        }

                        feeDatas.Add(new ClientFeeInfoView
                        {
                            client = item.PriceName,
                            frMoney = item.ItemSumPrice.ToString("#0.00"),
                            frCurrency = setData.Find(it => it.Id == item.Currency)?.Name ?? "-",
                            frRate = item.Rate.ToString("#0.0000"),
                            prReceivablesType = setData.Find(it => it.Id == prInfo?.ReceivablesType)?.Name ?? "",
                            prTime = prInfo?.SectionTime ?? "-",
                            prMoney = prInfo?.Price.ToString("#0.00") ?? "-",
                            prCurrency = setData.Find(it => it.Id == prInfo?.Currency)?.Name ?? "",
                            balPayment = _balancePayment.ToString("#0.00")
                        });
                    }
                    //未匹配上的数据
                    foreach (var item in list_pr)
                    {
                        var frInfo = list_fr.Find(it => it.PriceName.Contains(item.Client) || it.PriceName.Equals(item.Client));
                        if (frInfo == null)
                        {
                            feeDatas.Add(new ClientFeeInfoView
                            {
                                client = item.Client + "[未匹配上的已收数据(应收已收公司名称不一致)]",
                                frMoney = "0.00",
                                frCurrency = "-",
                                frRate = "0.0000",
                                prReceivablesType = setData.Find(it => it.Id == item?.ReceivablesType)?.Name ?? "",
                                prTime = item?.SectionTime ?? "-",
                                prMoney = item?.Price.ToString("#0.00") ?? "0.00",
                                prCurrency = setData.Find(it => it.Id == item?.Currency)?.Name ?? "",
                                balPayment = "0.00"
                            });
                        }
                    }


                    item_rst.feeItem = feeDatas;
                    #endregion
                }

                PostSyntheticalReceivableByDateRangeResultView result = new PostSyntheticalReceivableByDateRangeResultView();
                result.total_fr = sumAll_fr.ToString("#0.00");
                result.total_pr = sumAll_pr.ToString("#0.00");
                result.total_balance = sumAll_balance.ToString("#0.00");
                result.dataList = new List<PostSyntheticalReceivableByDateRangeView>(list_rst);

                if (dto.requestType == 1)
                {
                    return Ok(JsonView(true, "请求成功", result, list_rst.Count));
                }
                else
                {
                    //----------------------------
                    List<Excel_SyntheticalReceivableByDateRange> list_Ex = new List<Excel_SyntheticalReceivableByDateRange>();
                    WorkbookDesigner designer = new WorkbookDesigner();
                    designer.Workbook = new Workbook(AppSettingsHelper.Get("ExcelBasePath") + "Template/应收款项模板 - 副本.xls");

                    int excNo = 1;
                    foreach (var item in list_rst)
                    {
                        Excel_SyntheticalReceivableByDateRange exc = new Excel_SyntheticalReceivableByDateRange();
                        exc.No = excNo.ToString();
                        excNo++;
                        exc.TeamName = item.teamName;
                        exc.ClientUnit = item.clientUnit;
                        exc.VisitDate = item.visitDate;
                        exc.Accounts = item.frPrice;
                        exc.Extra = item.extraPrice;
                        exc.ReceivableTotal = item.receivableTotal;
                        exc.Received = item.prPrice;
                        exc.RefundAmount = item.refundAmount;
                        exc.ReceivedTotal = item.receivedTotal;
                        exc.Balance = item.balPrice;
                        exc.Collection = item.schedule;
                        DateTime time = Convert.ToDateTime(item.visitDate);
                        TimeSpan ts = DateTime.Now - time;
                        float SY = float.Parse(item.balPrice);
                        if (ts.Days >= 365 && SY > 0)
                        {
                            exc.Sign = "需收款";
                        }
                        else
                        {
                            exc.Sign = "";
                        }
                        list_Ex.Add(exc);
                    }

                    var dt = CommonFun.GetDataTableFromIList(list_Ex);
                    dt.TableName = "Excel_SyntheticalReceivableByDateRange";
                    if (dt != null)
                    {
                        designer.SetDataSource("SumPrice", "应收合计：" + result.total_fr + "RMB  已收合计：" + result.total_pr + "RMB  余款合计：" + result.total_balance + "RMB");

                        //数据源
                        designer.SetDataSource(dt);

                        //根据数据源处理生成报表内容
                        designer.Process();

                        string fileName = ("Receivable/应收款项(" + dto.beginDt + "～" + dto.endDt + ").xlsx");
                        designer.Workbook.Save(AppSettingsHelper.Get("ExcelBasePath") + fileName);
                        string rst = AppSettingsHelper.Get("ExcelBaseUrl") + AppSettingsHelper.Get("ExcelFtpPath") + fileName;
                        return Ok(JsonView(true, "成功", new { url = rst }));
                    }
                }
            }

            return Ok(JsonView(true, "获取成功", "", list_rst.Count));
        }

        #endregion

        #region 付款申请

        /// <summary>
        /// 付款申请
        /// 基础数据
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> PostPayRequestInit()
        {
            try
            {
                var conpanyDatas = _sqlSugar.Queryable<Sys_Company>()
                                            .Where(it => it.IsDel == 0)
                                            .Select(it => new
                                            {
                                                Id = it.Id,
                                                ConpamyName = it.CompanyName

                                            }).ToList();


                return Ok(JsonView(true, "操作成功!", new { ConpanyData = conpanyDatas }));
            }
            catch (Exception ex)
            {
                return Ok(JsonView(false, ex.Message));
            }
        }


        /// <summary>
        /// 付款申请 (PageId=51)
        /// 查询 根据日期范围
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> PostPayRequest_Center(PostPayRequestByDateRangeDto dto)
        {
            Stopwatch stopwatch = Stopwatch.StartNew();
            #region 验证
            DateTime beginDt, endDt;
            string format = "yyyy-MM-dd";
            if (!DateTime.TryParseExact(dto.beginDt, format, CultureInfo.InvariantCulture, DateTimeStyles.None, out beginDt))
            {
                return Ok(JsonView(false, "开始日期格式不正确！正确格式：yyyy-MM-dd"));
            }
            if (!DateTime.TryParseExact(dto.endDt, format, CultureInfo.InvariantCulture, DateTimeStyles.None, out endDt))
            {
                return Ok(JsonView(false, "结束日期格式不正确！正确格式：yyyy-MM-dd"));
            }

            #region 页面操作权限验证

            PageFunAuthViewBase pageFunAuthView = new PageFunAuthViewBase();
            pageFunAuthView = await GeneralMethod.PostUserPageFuncDatas(dto.UserId, dto.PageId);

            if (pageFunAuthView.CheckAuth == 0) return Ok(JsonView(false, "您没有查看权限!"));
            #endregion

            #endregion
            try
            {
                PaymentRequestCheckedView checkedView = new PaymentRequestCheckedView();
                var checkedStr = await RedisRepository.RedisFactory.CreateRedisRepository().StringGetAsync<string>("paymentRequestCheckedData");
                if (checkedStr != null)
                {
                    checkedView = JsonConvert.DeserializeObject<PaymentRequestCheckedView>(checkedStr.ToString());
                    if (checkedView.GroupIds.Count > 0)
                    {
                        checkedView.GroupIds = checkedView.GroupIds.OrderBy(x => x).ToList();
                    }
                }

                tree_Fin_DailyFeePaymentResult dailyResult = PayRequest_DailyByDateRange(dto.Status, checkedView.DailyPaymentIds, dto.beginDt, dto.endDt);
                tree_Group_DailyFeePaymentResult groupResult = PayRequest_GroupPaymentByDateRange(dto.Status, checkedView.GroupIds, checkedView.HotelSubIds, dto.beginDt, dto.endDt);

                stopwatch.Stop();
                return Ok(JsonView(true, $"查询成功！耗时{stopwatch.ElapsedMilliseconds / 1000}s", new { daily = dailyResult, group = groupResult }));
            }
            catch (Exception ex)
            {
                return Ok(JsonView(false, ex.Message));
            }

        }

        /// <summary>
        /// 根据团组类型类型处理团组费用所属公司
        /// </summary>
        /// <param name="teamId"></param>
        /// <returns></returns>
        private CompanyInfo ExpenseCompanyByTeamId(int teamId)
        {
            CompanyInfo _companyInfo = new CompanyInfo();

            List<int> _SiChuan = new List<int>() {
                38 , // 政府团
                39 , // 企业团
                40 , // 散客团
                102, // 未知
                248, // 非团组
                691, // 四川-会务活动
                762, // 四川-赛事项目收入
                1048,//高校团
            };
            List<int> _ChengDu = new List<int>() {
                302,  // 成都-会务活动
                1047, // 成都-赛事项目收入
            };

            if (_SiChuan.Contains(teamId))
            {
                _companyInfo.Id = 2;
                _companyInfo.ConpanyName = "四川泛美交流有限公司";
            }

            if (_ChengDu.Contains(teamId))
            {
                _companyInfo.Id = 1;
                _companyInfo.ConpanyName = "成都泛美商务有限公司";
            }

            return _companyInfo;
        }

        /// <summary>
        /// 付款申请(团组费用申请相关) 
        /// 查询 根据日期范围
        /// </summary>
        /// <param name="_groupIds"></param>
        /// <param name="beginDt"></param>
        /// <param name="endDt"></param>
        /// <returns></returns>
        private tree_Group_DailyFeePaymentResult PayRequest_GroupPaymentByDateRange(int status, List<int> _groupIds,List<int> _hotelSubIds, string beginDt, string endDt)
        {
            tree_Group_DailyFeePaymentResult _DailyFeePaymentResult = new tree_Group_DailyFeePaymentResult();
            List<tree_Group_DailyFeePaymentPageListView> dataList = new List<tree_Group_DailyFeePaymentPageListView>();
            #region sql条件处理
            string sqlWhere = string.Format(@" And (AuditGMDate Between '{0} 00:00:00' And '{1} 23:59:59') ", beginDt, endDt);

            if (status == 2)
            {
                if (_hotelSubIds.Count > 1)
                {
                    var hrIds = _sqlSugar.Queryable<Grp_HotelReservationsContent>().Where(it => _hotelSubIds.Contains(it.Id) && it.Price != 0).Select(it => it.HrId).Distinct().ToList();


                    var hrIds1 = _sqlSugar.Queryable<Grp_CreditCardPayment>().Where(it => it.CTable == 76 && hrIds.Contains(it.CId)).Select(it => it.Id).ToList();
                    if (hrIds1.Count > 1)
                    {
                        _groupIds.AddRange(hrIds1);
                    }
                }

                if (_groupIds.Count < 1)
                {
                    _DailyFeePaymentResult.dataList = new List<tree_Group_DailyFeePaymentPageListView>();
                    return _DailyFeePaymentResult;
                }
                sqlWhere += string.Format(@" And Id In ({0})", string.Join(",", _groupIds));
            }

            string sql_1 = string.Format(@"Select * From Grp_CreditCardPayment Where IsDel = 0 And IsPay = 0 And (IsAuditGM = 1 Or IsAuditGM = 3 ){0}", sqlWhere);
            #endregion

            var _paymentDatas = _sqlSugar.SqlQueryable<Grp_CreditCardPayment>(sql_1).ToList();//付款信息

            _DailyFeePaymentResult.gz = _paymentDatas.Where(it => it.OrbitalPrivateTransfer == 0).Sum(it => ((it.PayMoney * it.DayRate) / 100) * it.PayPercentage); //公转
            _DailyFeePaymentResult.sz = _paymentDatas.Where(it => it.OrbitalPrivateTransfer == 1).Sum(it => ((it.PayMoney * it.DayRate) / 100) * it.PayPercentage); ; //私转
            List<int> groupIds = _paymentDatas.Select(it => it.DIId).Distinct().ToList();

            var _groupDatas = _sqlSugar.Queryable<Grp_DelegationInfo>().Where(it => it.IsDel == 0 && groupIds.Contains(it.Id)).ToList();
            //_groupDatas = (List<Grp_DelegationInfo>)_groupDatas.GroupBy(it => it.TeamDid);

            #region 相关基础数据源
            var userDatas = _sqlSugar.Queryable<Sys_Users>().ToList();
            var setDatas = _sqlSugar.Queryable<Sys_SetData>().Where(it => it.IsDel == 0).ToList();
            var countryFeeDatas = _sqlSugar.Queryable<Grp_NationalTravelFee>().Where(it => it.IsDel == 0).ToList();

            var hotelDatas = _sqlSugar.Queryable<Grp_HotelReservations>().Where(it => it.IsDel == 0 && groupIds.Contains(it.DiId)).ToList();
            var hotelContentDatas = _sqlSugar.Queryable<Grp_HotelReservationsContent>().Where(it => it.IsDel == 0 && it.IsPay == 0 && it.Price != 0 && groupIds.Contains(it.DiId)).ToList();
            var opDatas = _sqlSugar.Queryable<Grp_CarTouristGuideGroundReservations>().Where(it => it.IsDel == 0 && groupIds.Contains(it.DiId)).ToList();
            var visaDatas = _sqlSugar.Queryable<Grp_VisaInfo>().Where(it => it.IsDel == 0 && groupIds.Contains(it.DIId)).ToList();
            var ioaDatas = _sqlSugar.Queryable<Grp_InvitationOfficialActivities>().Where(it => it.IsDel == 0 && groupIds.Contains(it.DiId)).ToList();
            var insureDatas = _sqlSugar.Queryable<Grp_Customers>().Where(it => it.IsDel == 0 && groupIds.Contains(it.DiId)).ToList();
            var airDatas = _sqlSugar.Queryable<Grp_AirTicketReservations>().Where(it => it.IsDel == 0 && groupIds.Contains(it.DIId)).ToList();
            //var otherMoneyDatas = _sqlSugar.Queryable<Fin_OtherPrice>().Where(it => it.IsDel == 0 && groupIds.Contains(it.Diid)).ToList();
            var otherMoneyDatas = _sqlSugar.Queryable<Grp_DecreasePayments>().Where(it => it.IsDel == 0 && groupIds.Contains(it.DiId)).ToList();
            var refundPaymentDatas = _sqlSugar.Queryable<Fin_PaymentRefundAndOtherMoney>().Where(it => it.IsDel == 0 && groupIds.Contains(it.DiId)).ToList();
            var ExtraCostDatas = _sqlSugar.Queryable<Fin_GroupExtraCost>().Where(it => it.IsDel == 0 && groupIds.Contains(it.DiId)).ToList();
            #endregion
            //Expense company
            foreach (var groupInfo in _groupDatas)
            {

                List<Group_DailyFeePaymentContentInfolView> childList = new List<Group_DailyFeePaymentContentInfolView>();

                var groupPaymentDatas = _paymentDatas.Where(it => groupInfo.Id == it.DIId).ToList();
                int rouNumber = 1;
                foreach (var payInfo in groupPaymentDatas)
                {
                    string priName = "-";

                    string orbitalPrivateTransfer = payInfo.OrbitalPrivateTransfer == 0 ? "公转" : payInfo.OrbitalPrivateTransfer == 1 ? "私转" : "-";
                    switch (payInfo.CTable)
                    {
                        case 76:  //76	酒店预订
                            priName = $"[费用名称：{hotelDatas.Find(it => payInfo.DIId == it.DiId && payInfo.CId == it.Id)?.HotelName ?? ""}]";
                            break;
                        case 79:  //79	车/导游地接
                            var opData = opDatas.Find(it => payInfo.DIId == it.DiId && payInfo.CId == it.Id);
                            if (opData != null)
                            {
                                string area = "";
                                bool b = int.TryParse(opData.Area, out int areaId);
                                if (b)
                                {
                                    string area1 = countryFeeDatas.Find(it => it.Id == areaId)?.Country ?? "-";
                                    area = $"{area1}({setDatas.Find(it => it.Id == opData.PriceType)?.Name ?? "-"})";
                                }
                                else area = opData.Area;

                                string opPriName = "-";
                                if (!string.IsNullOrEmpty(opData.PriceName)) opPriName = opData.PriceName;

                                area += $"({opPriName})";

                                if (payInfo.OrbitalPrivateTransfer == 0) //公转
                                {
                                    priName = $"【{orbitalPrivateTransfer}】【导游： {opData.ServiceGuide} 】[费用名称：{area}]";
                                }
                                else if (payInfo.OrbitalPrivateTransfer == 1) //私转
                                {
                                    priName = $"【{orbitalPrivateTransfer}】【导游：{opData.ServiceGuide}】[费用名称：{area}]";
                                }
                            }
                            break;
                        case 80:  // 80   签证

                            string sql = string.Format("select b.Id,b.Pinyin,b.lastName,b.firstName,b.phone from  Grp_TourClientList a, Crm_DeleClient b where a.clientid = b.id and a.isdel = 0 and a.diid = {0}", groupInfo.Id);
                            List<SimplClientInfo> arr = _sqlSugar.SqlQueryable<SimplClientInfo>(sql).ToList();

                            string visaClientName = visaDatas.Find(it => payInfo.DIId == it.DIId && payInfo.CId == it.Id)?.VisaClient ?? "";
                            string clientName = "-";
                            if (Regex.Match(visaClientName, @"\d+,?").Value.Length > 0)
                            {
                                string[] temparr = visaClientName.Split(',');
                                string fistrStr = temparr[0];
                                int count = temparr.Count();

                                int tempId;
                                bool success = int.TryParse(fistrStr, out tempId);
                                if (success)
                                {
                                    SimplClientInfo tempInfo = arr.FirstOrDefault(s => s.Id == tempId);
                                    if (tempInfo != null)
                                    {
                                        if (count > 1)
                                        {
                                            clientName = string.Format(@"{0}{1}等{2}人", tempInfo.LastName, tempInfo.FirstName, count);
                                        }
                                        else
                                        {
                                            clientName = string.Format(@"{0}{1}", tempInfo.LastName, tempInfo.FirstName);
                                        }
                                    }
                                }
                                else
                                {
                                    clientName = fistrStr;
                                }
                            }

                            priName = $"[费用名称：{clientName}]";
                            break;
                        case 81:  // 81	邀请/公务活动
                            priName = $"[费用名称：{ioaDatas.Find(it => payInfo.DIId == it.DiId && payInfo.CId == it.Id)?.Inviter ?? " -"}]";
                            break;
                        case 82:  // 82	团组客户保险
                            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);
                    }

                    if (payInfo.CTable == 76) //酒店单独处理
                    {
                        var hotelContents = hotelContentDatas.Where(it => it.HrId == payInfo.CId);
                        List<Group_DailyFeePaymentContentInfolView> childInfos = new List<Group_DailyFeePaymentContentInfolView>();
                        foreach (var hotelContent in hotelContents)
                        {
                            string subPriceName = "";
                            if (hotelContent.PriceType == 1) subPriceName = "房费";
                            else if (hotelContent.PriceType == 2) subPriceName = "早餐";
                            else if (hotelContent.PriceType == 3) subPriceName = "地税";
                            else if (hotelContent.PriceType == 4) subPriceName = "城市税";

                            if (string.IsNullOrEmpty(subPriceName)) subPriceName = priName;
                            else subPriceName = $"{priName.Replace("]","")}-{subPriceName}]";

                            string payeeStr1 = string.Format(@" {0},开户行：{1},银行卡号：{2} ",
                            string.IsNullOrEmpty(hotelContent.Payee) ? "-" : hotelContent.Payee,
                            string.IsNullOrEmpty(hotelContent.OtherBankName) ? "-" : hotelContent.OtherBankName,
                            string.IsNullOrEmpty(hotelContent.OtherSideNo) ? "-" : hotelContent.OtherSideNo);

                            decimal _PaymentAmount1 = hotelContent.Price;//此次付款金额
                            decimal _CNYSubTotalAmount1 = _PaymentAmount1 * hotelContent.Rate;//此次付款金额
                            _CNYSubTotalAmount1 = Convert.ToDecimal(_CNYSubTotalAmount1.ToString("#0.00"));

                            //酒店子项Id选中状态更改
                            if (_hotelSubIds != null) status1 = _hotelSubIds.Contains(hotelContent.Id);

                            var childInfo1 = new Group_DailyFeePaymentContentInfolView()
                            {
                                IsChecked = status1,
                                Id = payInfo.Id,
                                HotelSubId = hotelContent.Id,
                                Payee = payeeStr1,
                                RowNumber = rouNumber,
                                Applicant = userDatas.Find(it => it.Id == payInfo.CreateUserId)?.CnName ?? "",
                                ApplicantDt = payInfo.CreateTime.ToString("yyyy-MM-dd HH:mm:ss"),
                                PayType = setDatas.Find(it => it.Id == payInfo.PayDId)?.Name ?? "",
                                TransferMark = orbitalPrivateTransfer,
                                PriceName = subPriceName,
                                ModuleName = setDatas.Find(it => it.Id == payInfo.CTable)?.Name ?? "",
                                PayCurrCode = setDatas.Find(it => it.Id == hotelContent.Currency)?.Name ?? "",
                                PaymentAmount = _PaymentAmount1,
                                PayRate = hotelContent.Rate,
                                CNYSubTotalAmount = _CNYSubTotalAmount1,
                                AuditStatus = payInfo.IsAuditGM

                            };
                            string remaksDescription1 = $"【{childInfo1.PayType}】【{childInfo1.ModuleName}】{rouNumber}、[申请人：{childInfo1.Applicant}]{subPriceName}[收款方：{childInfo1.Payee}] {childInfo1.PayCurrCode} {_PaymentAmount1.ToString("#0.00")}、CNY：{childInfo1.CNYSubTotalAmount.ToString("#0.00")}(团组：{groupInfo.TeamName})";

                            childInfo1.RemaksDescription = remaksDescription1;

                            if (status == 2)
                            { 
                                if (status1) 
                                {
                                    childInfos.Add(childInfo1);
                                }
                            }
                            else childInfos.Add(childInfo1);

                            rouNumber++;
                        }

                        childList.AddRange(childInfos);

                    }
                    else
                    {
                        string payeeStr = string.Format(@" {0},开户行：{1},银行卡号：{2} ",
                            string.IsNullOrEmpty(payInfo.Payee) ? "-" : payInfo.Payee,
                            string.IsNullOrEmpty(payInfo.OtherBankName) ? "-" : payInfo.OtherBankName,
                            string.IsNullOrEmpty(payInfo.OtherSideNo) ? "-" : payInfo.OtherSideNo);


                        decimal _PaymentAmount = (payInfo.PayMoney / 100) * payInfo.PayPercentage;//此次付款金额
                        decimal _CNYSubTotalAmount = _PaymentAmount * payInfo.DayRate;//此次付款金额
                        _CNYSubTotalAmount = Convert.ToDecimal(_CNYSubTotalAmount.ToString("#0.00"));
                        var childInfo = new Group_DailyFeePaymentContentInfolView()
                        {
                            IsChecked = status1,
                            Id = payInfo.Id,
                            Payee = payeeStr,
                            RowNumber = rouNumber,
                            Applicant = userDatas.Find(it => it.Id == payInfo.CreateUserId)?.CnName ?? "",
                            ApplicantDt = payInfo.CreateTime.ToString("yyyy-MM-dd HH:mm:ss"),
                            PayType = setDatas.Find(it => it.Id == payInfo.PayDId)?.Name ?? "",
                            TransferMark = orbitalPrivateTransfer,
                            PriceName = priName,
                            ModuleName = setDatas.Find(it => it.Id == payInfo.CTable)?.Name ?? "",
                            PayCurrCode = setDatas.Find(it => it.Id == payInfo.PaymentCurrency)?.Name ?? "",
                            PaymentAmount = _PaymentAmount,
                            PayRate = payInfo.DayRate,
                            CNYSubTotalAmount = _CNYSubTotalAmount,
                            AuditStatus = payInfo.IsAuditGM
                        };

                        string remaksDescription = $"【{childInfo.PayType}】【{childInfo.ModuleName}】{rouNumber}、[申请人：{childInfo.Applicant}]{priName}[收款方：{childInfo.Payee}] {childInfo.PayCurrCode} {_PaymentAmount.ToString("#0.00")}、CNY：{childInfo.CNYSubTotalAmount.ToString("#0.00")}(团组：{groupInfo.TeamName})";

                        childInfo.RemaksDescription = remaksDescription;
                        childList.Add(childInfo);
                        rouNumber++;
                    }
                    
                }

                CompanyInfo companyInfo = new CompanyInfo();
                companyInfo = ExpenseCompanyByTeamId(groupInfo.TeamDid);
                dataList.Add(new tree_Group_DailyFeePaymentPageListView()
                {
                    Id = Guid.NewGuid().ToString("N"),
                    GroupName = groupInfo.TeamName,
                    CompanyId = companyInfo.Id,
                    ConpanyName = companyInfo.ConpanyName,
                    CNYTotalAmount = childList.Sum(it => it.CNYSubTotalAmount),
                    ChildList = childList,
                });
            }

            _DailyFeePaymentResult.dataList = dataList;
            return _DailyFeePaymentResult;
        }

        /// <summary>
        /// 付款申请(日付申请相关)
        /// 查询 根据日期范围
        /// </summary>
        /// <param name="_dailyIds"></param>
        /// <param name="beginDt"></param>
        /// <param name="endDt"></param>
        /// <returns></returns>
        private tree_Fin_DailyFeePaymentResult PayRequest_DailyByDateRange(int status, List<int> _dailyIds, string beginDt, string endDt)
        {

            #region sql条件处理
            string sqlWhere = string.Format(@" And dfp.CreateTime between '{0} 00:00:00' And '{1} 23:59:59' ", beginDt, endDt);

            if (status == 2)
            {
                if (_dailyIds.Count < 1)
                {
                    return new tree_Fin_DailyFeePaymentResult() { childList = new List<Fin_DailyFeePaymentContentInfolView>() };
                }

                sqlWhere += string.Format(@" And dfp.Id  In({0}) ", string.Join(",", _dailyIds));
            }

            string sql_1 = string.Format(@"Select * From (	
                                                Select row_number() over (order by dfp.Id Desc) as RowNumber,
                                                    dfp.Id,dfp.CompanyId,c.CompanyName,dfp.Instructions,dfp.SumPrice,
                                                    dfp.CreateUserId,u.CnName CreateUser,dfp.CreateTime,dfp.FAudit,dfp.MAudit,
                                                    dfp.PriceTypeId,dfp.TransferTypeId 
                                                From Fin_DailyFeePayment dfp
                                                Inner Join Sys_Company c On dfp.CompanyId = c.Id
                                                Left Join Sys_Users u On dfp.CreateUserId = u.Id
                                                Where dfp.IsDel=0 {0} And dfp.FAudit = 1 And dfp.MAudit = 1 And dfp.IsPay = 0
                                                ) temp ", sqlWhere);
            #endregion

            List<tree_Fin_DailyFeePaymentPageListView> DailyFeePaymentData = _sqlSugar.SqlQueryable<tree_Fin_DailyFeePaymentPageListView>(sql_1).ToList();

            Dictionary<int, string> dic_setData = new Dictionary<int, string>();

            Sys_SetDataType stGZ = _daiRep.Query<Sys_SetDataType>(s => s.Name == "公转").First();
            Sys_SetDataType stSZ = _daiRep.Query<Sys_SetDataType>(s => s.Name == "私转").First();

            foreach (var item in DailyFeePaymentData)
            {
                if (_dailyIds != null)
                {
                    item.IsChecked = _dailyIds.Contains(item.Id);
                }

                if (dic_setData.ContainsKey(item.PriceTypeId))
                {
                    item.priceTypeStr = dic_setData[item.PriceTypeId];
                }
                else
                {
                    Sys_SetData sd_priceType = _daiRep.Query<Sys_SetData>(s => s.Id == item.PriceTypeId).First();
                    if (sd_priceType != null)
                    {
                        item.priceTypeStr = sd_priceType.Name;
                        dic_setData.Add(item.PriceTypeId, sd_priceType.Name);
                    }
                }

                if (dic_setData.ContainsKey(item.transferTypeId))
                {
                    item.transferTypeIdStr = dic_setData[item.transferTypeId];
                    Sys_SetData sd_transfer = _daiRep.Query<Sys_SetData>(s => s.Id == item.transferTypeId).First();
                    if (sd_transfer != null)
                    {
                        item.transferParentId = sd_transfer.STid;
                        item.transferParentIdStr = sd_transfer.STid == stGZ.Id ? "公转" : sd_transfer.STid == stSZ.Id ? "私转" : "";
                    }
                }
                else
                {
                    Sys_SetData sd_transfer = _daiRep.Query<Sys_SetData>(s => s.Id == item.transferTypeId).First();
                    if (sd_transfer != null)
                    {
                        item.transferTypeIdStr = sd_transfer.Name;
                        item.transferParentId = sd_transfer.STid;
                        item.transferParentIdStr = sd_transfer.STid == stGZ.Id ? "公转" : sd_transfer.STid == stSZ.Id ? "私转" : "";
                        dic_setData.Add(item.transferTypeId, sd_transfer.Name);
                    }
                }

                string feeContentSql = string.Format(@"Select * From Fin_DailyFeePaymentContent 
                                                        Where IsDel=0 And DFPId = {0} ", item.Id);
                item.childList = _sqlSugar.SqlQueryable<Fin_DailyFeePaymentContentInfolView>(feeContentSql).ToList();
                int rowNumber = 1;
                foreach (var subItem in item.childList)
                {
                    string remaksDescription = $"{rowNumber}、【{item.priceTypeStr}】{item.Instructions}({subItem.PriceName}) CNY：{subItem.ItemTotal.ToString("#0.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 == stGZ.Id).Sum(d => d.SumPrice ?? 0M);
            decimal total_sz = DailyFeePaymentData.Where(s => s.transferParentId == stSZ.Id).Sum(d => d.SumPrice ?? 0M);

            var result = new tree_Fin_DailyFeePaymentResult() { gz = total_gz, sz = total_sz, dataList = DailyFeePaymentData };

            return result;
        }

        /// <summary> 
        /// 付款申请 (PageId=51)
        /// 团组,日付相关费用 选中状态变更
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> PostPayRequestCheckedChange(PayRequestCheckedChangeDto dto)
        {
            #region 验证

            if (dto.Type < 1 || dto.Type > 2)
            {
                return Ok(JsonView(false, "请传入有效的Type参数! 1 checked 2 清除上次勾选"));
            }

            #endregion

            PaymentRequestCheckedView requestCheckedView = new PaymentRequestCheckedView();
            List<int> groupIds = new List<int>();
            List<int> dailyPaymentIds = new List<int>();
            List<int> hotelIds = new List<int>();

            #region 参数处理
            if (!string.IsNullOrEmpty(dto.GroupIds))
            {
                if (dto.GroupIds.Contains(","))
                {
                    groupIds = dto.GroupIds.Split(',').Select(int.Parse).ToList();
                }
                else
                {
                    groupIds.Add(int.Parse(dto.GroupIds));
                }
            }

            if (!string.IsNullOrEmpty(dto.HotelSubIds))
            {
                if (dto.HotelSubIds.Contains(","))
                {
                    hotelIds = dto.HotelSubIds.Split(',').Select(int.Parse).ToList();
                }
                else
                {
                    hotelIds.Add(int.Parse(dto.HotelSubIds));
                }
            }

            if (!string.IsNullOrEmpty(dto.DailyPaymentIds))
            {
                if (dto.DailyPaymentIds.Contains(","))
                {
                    dailyPaymentIds = dto.DailyPaymentIds.Split(',').Select(int.Parse).ToList();
                }
                else
                {
                    dailyPaymentIds.Add(int.Parse(dto.DailyPaymentIds));
                }
            }
            #endregion
            requestCheckedView.GroupIds = groupIds;
            requestCheckedView.HotelSubIds = hotelIds;
            requestCheckedView.DailyPaymentIds = dailyPaymentIds;
            if (dto.Type == 1)
            {
                TimeSpan ts = DateTime.Now.AddDays(180) - DateTime.Now; //设置redis 过期时间 半年(180)
                var status = await RedisRepository.RedisFactory.CreateRedisRepository().StringSetAsync<string>("paymentRequestCheckedData", JsonConvert.SerializeObject(requestCheckedView), ts);
                if (status)
                {
                    return Ok(JsonView(true, "操作成功!"));
                }
            }
            else if (dto.Type == 2)
            {
                var status = await RedisRepository.RedisFactory.CreateRedisRepository().KeyDeleteAsync("paymentRequestCheckedData");
                if (status)
                {
                    return Ok(JsonView(true, "操作成功!"));
                }
            }
            return Ok(JsonView(false, "操作失败!"));

        }

        /// <summary>
        /// 付款申请 (PageId=51)
        /// 团组,日付相关费用 汇率变更
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> PostPayRequestRateChange(PayRequestRateChangeDto dto)
        {
            #region 验证
            DateTime beginDt, endDt;
            string format = "yyyy-MM-dd";
            if (!DateTime.TryParseExact(dto.beginDt, format, CultureInfo.InvariantCulture, DateTimeStyles.None, out beginDt))
            {
                return Ok(JsonView(false, "开始日期格式不正确！正确格式：yyyy-MM-dd"));
            }
            if (!DateTime.TryParseExact(dto.endDt, format, CultureInfo.InvariantCulture, DateTimeStyles.None, out endDt))
            {
                return Ok(JsonView(false, "结束日期格式不正确！正确格式：yyyy-MM-dd"));
            }

            if (dto.UserId < 1) return Ok(JsonView(false, "请传入有效的UserId参数!"));
            if (dto.Id < 1) return Ok(JsonView(false, "请传入有效的Id参数!"));
            if (dto.Rate <= 0) return Ok(JsonView(false, "请传入有效的Rate参数!"));
            #endregion

            int hotelSubStatus = 0,status = 0;
            int diid = 0, cTable = 0, currId = 0;

            if (dto.HotelSubId > 0)
            {
                //更改酒店子表汇率
                hotelSubStatus = _sqlSugar.Updateable<Grp_HotelReservationsContent>()
                                     .SetColumns(it => it.Rate == dto.Rate)
                                     .Where(it => it.Id == dto.HotelSubId)
                                     .ExecuteCommand();

                if (hotelSubStatus > 0)
                {
                    var hotelSubInfo = _sqlSugar.Queryable<Grp_HotelReservationsContent>().Where(it => it.Id == dto.HotelSubId).First();
                    if (hotelSubInfo != null)
                    {
                        diid = hotelSubInfo.DiId;
                        currId = hotelSubInfo.Currency;
                    }

                    //付款申请汇率更改成功；更改团组汇率对应币种
                    string currCode = _sqlSugar.Queryable<Sys_SetData>().Where(it => it.Id == currId).First()?.Name ?? "";
                    await _teamRateRep.UpdateGroupRateByDiIdAndCTableId(diid, 76, currCode, dto.Rate);
                }
            }

            if (dto.Id > 0)
            {
                var ccpInfo = _sqlSugar.Queryable<Grp_CreditCardPayment>().Where(it => it.Id == dto.Id).First();
                decimal cnyMoney = 0.00M;
                if (ccpInfo != null) {
                    cnyMoney = ccpInfo.PayMoney * dto.Rate;
                    diid = ccpInfo.DIId;
                    cTable = ccpInfo.CTable; 
                    currId = ccpInfo.PaymentCurrency;
                }

                status = _sqlSugar.Updateable<Grp_CreditCardPayment>()
                                  .SetColumns(it => it.DayRate == dto.Rate)
                                  .SetColumns(it => it.RMBPrice == cnyMoney)
                                  .Where(it => it.Id == dto.Id)
                                  .ExecuteCommand();
                if (status > 0)
                {
                    //付款申请汇率更改成功；更改团组汇率对应币种
                    if (cTable != 76)
                    {
                        string currCode = _sqlSugar.Queryable<Sys_SetData>().Where(it => it.Id == currId).First()?.Name ?? "";
                        await _teamRateRep.UpdateGroupRateByDiIdAndCTableId(diid, cTable, currCode, dto.Rate);
                    }
                }
            }

            if (hotelSubStatus > 0 || status > 0)
            {
                PaymentRequestCheckedView checkedView = new PaymentRequestCheckedView();
                var checkedStr = await RedisRepository.RedisFactory.CreateRedisRepository().StringGetAsync<string>("paymentRequestCheckedData");
                if (checkedStr != null)
                {
                    checkedView = JsonConvert.DeserializeObject<PaymentRequestCheckedView>(checkedStr.ToString());
                }

                tree_Fin_DailyFeePaymentResult dailyResult = PayRequest_DailyByDateRange(1, checkedView.DailyPaymentIds, dto.beginDt, dto.endDt);
                tree_Group_DailyFeePaymentResult groupResult = PayRequest_GroupPaymentByDateRange(1, checkedView.GroupIds, checkedView.HotelSubIds, dto.beginDt, dto.endDt);

                decimal _gz = dailyResult.gz + groupResult.gz;
                decimal _sz = dailyResult.sz + groupResult.sz;

                return Ok(JsonView(true, "操作成功!", new { gz = dailyResult, sz = groupResult }));
            }

            return Ok(JsonView(false, "该项汇率修改失败!"));
        }

        /// <summary>
        /// 付款申请 (PageId=51)
        /// 团组,日付相关费用 付款状态变更
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> PostPayRequestPayChange(PayRequestPayChangeDto dto)
        {
            if (dto.UserId < 1) return Ok(JsonView(false, "请传入有效的UserId参数!"));

            //if (string.IsNullOrEmpty(dto.GroupIds))
            //{
            //    return Ok(JsonView(false, "请传入有效的GroupIds参数!"));
            //}

            //if (string.IsNullOrEmpty(dto.DailyPaymentIds))
            //{
            //    return Ok(JsonView(false, "请传入有效的DailyPaymentIds参数!"));
            //}


            List<int> groupIds = new List<int>();
            List<int> dailyPaymentIds = new List<int>();
            List<int> hotelSubIds = new List<int>();

            #region 参数处理
            if (!string.IsNullOrEmpty(dto.GroupIds))
            {
                if (dto.GroupIds.Contains(",")) groupIds = dto.GroupIds.Split(',').Select(int.Parse).ToList();
                else groupIds.Add(int.Parse(dto.GroupIds));
            }

            if (!string.IsNullOrEmpty(dto.HotelSubIds))
            {
                if (dto.HotelSubIds.Contains(",")) hotelSubIds = dto.HotelSubIds.Split(',').Select(int.Parse).ToList();
                else hotelSubIds.Add(int.Parse(dto.HotelSubIds));
                if (hotelSubIds.Count > 0)
                {
                    foreach (var item in hotelSubIds)
                    {
                        if (item < 1)
                        {
                            hotelSubIds.Remove(item);
                        }
                    }
                }
            }

            if (!string.IsNullOrEmpty(dto.DailyPaymentIds))
            {
                if (dto.DailyPaymentIds.Contains(",")) dailyPaymentIds = dto.DailyPaymentIds.Split(',').Select(int.Parse).ToList();
                else dailyPaymentIds.Add(int.Parse(dto.DailyPaymentIds));
            }
            #endregion

            bool changeStatus = false;
            _sqlSugar.BeginTran();

            if (groupIds.Count > 0)
            {

                var ccpInfos = _sqlSugar.Queryable<Grp_CreditCardPayment>().Where(it => it.IsDel == 0 && groupIds.Contains(it.Id)).ToList();
                var otherTypeCcpIds = ccpInfos.Where(it => it.CTable != 76).Select(it => it.Id).ToList();
                var hotelTyprCcpIds = ccpInfos.Where(it => it.CTable == 76).Select(it => it.Id).ToList();
                int groupStatus = 0;
                if (otherTypeCcpIds.Count > 0) //其他费用类型 正常付款
                {
                    groupStatus = _sqlSugar.Updateable<Grp_CreditCardPayment>()
                                           .SetColumns(it => it.IsPay == 1)
                                           .Where(it => otherTypeCcpIds.Contains(it.Id))
                                           .ExecuteCommand();
                    changeStatus = true;
                }

                if (hotelTyprCcpIds.Count > 0) //酒店费用子项逻辑付款，酒店子项费用全部付完款，c表ispay=1
                {
                    if (hotelSubIds.Count > 0)
                    {
                        List<int> hrPayIds = new List<int>();
                        var hrIspayStatus = _sqlSugar.Updateable<Grp_HotelReservationsContent>()
                                                   .SetColumns(it => it.IsPay == 1)
                                                   .Where(it => hotelSubIds.Contains(it.Id))
                                                   .ExecuteCommand();
                        changeStatus = true;
                        //酒店子项是否全部付完款
                        List<int> hrIds = _sqlSugar.Queryable<Grp_HotelReservationsContent>().Where(it => it.IsDel == 0 && hotelSubIds.Contains(it.Id)).Select(it => it.HrId).Distinct().ToList();
                        if (hrIds.Count > 0 )
                        {
                            var hotelSubFeeData = _sqlSugar.Queryable<Grp_HotelReservationsContent>().Where(it => it.IsDel == 0 && hrIds.Contains(it.HrId)).ToList();
                            var hotelSubFeeGroupData = hotelSubFeeData.GroupBy(it => it.HrId).ToList();
                            foreach (var item in hotelSubFeeGroupData)
                            {
                                var allTotal = item.Where(it => it.Price > 0).ToList().Count;
                                var paymentTotal = item.Where(it => it.Price > 0 && it.IsPay == 1).ToList().Count;
                                if (allTotal == paymentTotal)
                                {
                                    hrPayIds.Add(item.Key);
                                }
                            }
                        }

                        if (hrPayIds.Count > 0)
                        {
                            //c表更改全部付款的酒店费用
                            groupStatus = _sqlSugar.Updateable<Grp_CreditCardPayment>()
                                               .SetColumns(it => it.IsPay == 1)
                                               .Where(it => it.CTable == 76 && hrPayIds.Contains(it.CId))
                                               .ExecuteCommand();
                        }
                    }
                }
            }

            //if (hotelSubIds.Count > 0)
            //{
            //    var groupStatus = _sqlSugar.Updateable<Grp_HotelReservationsContent>()
            //                               .SetColumns(it => it.IsPay == 1)
            //                               .Where(it => hotelSubIds.Contains(it.Id))
            //                               .ExecuteCommand();
            //    if (groupStatus > 0)
            //    {
            //        changeStatus = true;
            //    }
            //}

            if (dailyPaymentIds.Count > 0)
            {
                var dailyPaymentStatus = _sqlSugar.Updateable<Fin_DailyFeePayment>()
                                           .SetColumns(it => it.IsPay == 1)
                                           .Where(it => dailyPaymentIds.Contains(it.Id))
                                           .ExecuteCommand();
                if (dailyPaymentStatus > 0)
                {
                    changeStatus = true;
                }
            }

            if (changeStatus)
            {
                _sqlSugar.CommitTran();

                #region 应用推送
                try
                {
                    foreach (int ccpId in groupIds)
                    {
                        List<string> tempList = new List<string>() { ccpId.ToString() };
                        await AppNoticeLibrary.SendUserMsg_GroupStatus_PayResult(ccpId, tempList);
                    }

                    foreach (int dailyId in dailyPaymentIds)
                    {
                        List<string> tempList = new List<string>() { dailyId.ToString() };
                        await AppNoticeLibrary.DailyPayReminder_Pay_ToUser(dailyId, tempList);
                    }
                }
                catch (Exception ex)
                {
                }
                #endregion

                return Ok(JsonView(true, "操作成功!"));
            }
            _sqlSugar.RollbackTran();
            return Ok(JsonView(false, "付款状态修改失败!"));

        }

        /// <summary>
        /// 付款申请 (PageId=51)
        /// File Download
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> PostPayRequestFileDownload(PayRequestFileDownloadDto dto)
        {
            Stopwatch stopwatch = Stopwatch.StartNew();

            #region 参数,权限 验证
            if (dto.PortType < 1)
            {
                return Ok(JsonView(false, "请传入有效的PortType参数!"));
            }

            if (dto.UserId < 1)
            {
                return Ok(JsonView(false, "请传入有效的UserId参数!"));
            }
            if (dto.PageId < 1)
            {
                dto.PageId = 51;
                return Ok(JsonView(false, "请传入有效的PageId参数!"));
            }
            if (dto.ConpanyId < 1 || dto.ConpanyId > 4)
            {
                return Ok(JsonView(false, "请传入有效的ConpanyId参数!"));
            }

            PageFunAuthViewBase pageFunAuthView = new PageFunAuthViewBase();

            #region 页面操作权限验证
            pageFunAuthView = await GeneralMethod.PostUserPageFuncDatas(dto.UserId, dto.PageId);

            if (pageFunAuthView.FilesDownloadAuth == 0) return Ok(JsonView(false, "您没有文件下载权限!"));
            #endregion

            #endregion

            try
            {
                PaymentRequestCheckedView checkedView = new PaymentRequestCheckedView();
                var checkedStr = await RedisRepository.RedisFactory.CreateRedisRepository().StringGetAsync<string>("paymentRequestCheckedData");
                if (checkedStr != null)
                {
                    checkedView = JsonConvert.DeserializeObject<PaymentRequestCheckedView>(checkedStr.ToString());
                }

                if (checkedView == null)
                {
                    return Ok(JsonView(false, "没有选中的数据!"));
                }

                if (checkedView.GroupIds == null && checkedView.DailyPaymentIds == null)
                {
                    return Ok(JsonView(false, "没有选中的数据!"));
                }

                tree_Fin_DailyFeePaymentResult dailyResult = PayRequest_DailyByDateRange(2, checkedView.DailyPaymentIds, dto.beginDt, dto.endDt);
                tree_Group_DailyFeePaymentResult groupResult = PayRequest_GroupPaymentByDateRange(2, checkedView.GroupIds, checkedView.HotelSubIds, dto.beginDt, dto.endDt);

                if (dailyResult.childList == null)
                {
                    dailyResult.childList = new List<Fin_DailyFeePaymentContentInfolView>();
                }

                List<tree_Group_DailyFeePaymentPageListView> _GroupData = new List<tree_Group_DailyFeePaymentPageListView>();
                List<tree_Fin_DailyFeePaymentPageListView> _DailyData = new List<tree_Fin_DailyFeePaymentPageListView>();

                //1	成都泛美商务有限公司
                if (dto.ConpanyId == 1)
                {
                    if (groupResult.dataList != null && groupResult.dataList.Count > 0) _GroupData = groupResult.dataList.Where(it => it.CompanyId == 1).ToList();
                    if (dailyResult.dataList != null && dailyResult.dataList.Count > 0) _DailyData = dailyResult.dataList.Where(it => it.CompanyId == 1).ToList();

                }
                //2	四川泛美交流有限公司
                else if (dto.ConpanyId == 2)
                {
                    if (groupResult.dataList != null && groupResult.dataList.Count > 0) _GroupData = groupResult.dataList.Where(it => it.CompanyId == 2).ToList();
                    if (dailyResult.dataList != null && dailyResult.dataList.Count > 0) _DailyData = dailyResult.dataList.Where(it => it.CompanyId == 2).ToList();

                }
                //3 成都纽茵教育科技有限公司
                else if (dto.ConpanyId == 3)
                {
                    if (groupResult.dataList != null && groupResult.dataList.Count > 0) _GroupData = groupResult.dataList.Where(it => it.CompanyId == 3).ToList();
                    if (dailyResult.dataList != null && dailyResult.dataList.Count > 0) _DailyData = dailyResult.dataList.Where(it => it.CompanyId == 3).ToList();

                }
                //4 成都鸿企中元科技有限公司
                else if (dto.ConpanyId == 4)
                {
                    return Ok(JsonView(false, "暂未开放该类型!"));
                }
                else
                {
                    return Ok(JsonView(false, "参数ConpanyId不可使用!"));
                }

                string _requestPaymentDt = DateTime.Now.ToString("yyyy-MM-dd"),//申请付款日期
                       _appliedAmount = "", //申请付款金额
                       _GZStr = "",    //公转价格描述
                       _SZStr = "";    //私转价格描述

                decimal groupGZAmout = 0.00M, groupSZAmout = 0.00M;
                decimal dailyGZAmout = 0.00M, dailySZAmout = 0.00M;
                string dailyGZStr = "", dailySZStr = "", groupGZStr = "", groupSZStr = "";

                #region 数据处理

                //团组费用相关
                foreach (var item in _GroupData)
                {
                    string groupGZSubStr = "";
                    string groupSZSubStr = "";

                    foreach (var subItem in item.ChildList)
                    {
                        if (subItem.TransferMark.Equals("公转"))
                        {
                            groupGZAmout += subItem.CNYSubTotalAmount;
                            groupGZSubStr += $"{subItem.RemaksDescription}\r\n";
                        }
                        else if (subItem.TransferMark.Equals("私转"))
                        {
                            groupSZAmout += subItem.CNYSubTotalAmount;
                            groupSZSubStr += $"{subItem.RemaksDescription}\r\n";
                        }

                        //groupGZSubStr += $"\t";
                    }

                    //if (!string.IsNullOrEmpty(groupGZSubStr)) groupGZStr += $"团组：{item.GroupName}\r\n{groupGZSubStr}\r\n";
                    //if (!string.IsNullOrEmpty(groupSZSubStr)) groupSZStr += $"团组：{item.GroupName}\r\n{groupSZSubStr}\r\n";
                    if (!string.IsNullOrEmpty(groupGZSubStr)) groupGZStr += $"{groupGZSubStr}\r\n";
                    if (!string.IsNullOrEmpty(groupSZSubStr)) groupSZStr += $"{groupSZSubStr}\r\n";

                }

                //日常费用相关
                foreach (var item in _DailyData)
                {
                    foreach (var subItem in item.childList)
                    {
                        if (item.transferParentId == 62) //公转
                        {
                            dailyGZAmout += item.SumPrice ?? 0.00M;
                            dailyGZStr += $"{item.RowNumber}、【{item.CompanyName}】{subItem.ExcelRemaksDescription}\r\n";

                        }
                        else if (item.transferParentId == 63) //私转
                        {
                            dailySZAmout += item.SumPrice ?? 0.00M;
                            dailySZStr += $"{item.RowNumber}、【{item.CompanyName}】{subItem.ExcelRemaksDescription}\r\n";
                        }
                    }
                }

                _GZStr = $"【公转】团组相关费用（合计：CNY {groupGZAmout.ToString("#0.00")}）：\r\n{groupGZStr}【公转】日常付款费用（合计：CNY {dailyGZAmout.ToString("#0.00")}）：\r\n{dailyGZStr}";
                _SZStr = $"【私转】团组相关费用（合计：CNY {groupSZAmout.ToString("#0.00")}）：\r\n{groupSZStr}【私转】日常付款费用（合计：CNY {dailySZAmout.ToString("#0.00")}）：\r\n{dailySZStr}";
                _appliedAmount = $"公转：CNY {(groupGZAmout + dailyGZAmout).ToString("#0.00")}\r\n私转：CNY {(groupSZAmout + dailySZAmout).ToString("#0.00")}";
                #endregion


                WorkbookDesigner designer = new WorkbookDesigner();
                designer.Workbook = new Workbook(AppSettingsHelper.Get("ExcelBasePath") + "Template/付款申请书.xls");
                designer.SetDataSource("Date", _requestPaymentDt);
                designer.SetDataSource("Price", _appliedAmount);
                designer.SetDataSource("Content", _GZStr);
                designer.SetDataSource("Content1", _SZStr);

                //根据数据源处理生成报表内容
                designer.Process();

                string fileName = ("PayRequest/付款申请(" + dto.beginDt + "～" + dto.endDt + ").xlsx");
                designer.Workbook.Save(AppSettingsHelper.Get("ExcelBasePath") + fileName);
                string rst = AppSettingsHelper.Get("ExcelBaseUrl") + AppSettingsHelper.Get("ExcelFtpPath") + fileName;
                stopwatch.Stop();

                return Ok(JsonView(true, $"操作成功!{stopwatch.ElapsedMilliseconds / 1000}s", new { url = rst }));

            }
            catch (Exception ex)
            {
                return Ok(JsonView(false, ex.Message));
            }
        }

        #endregion

        #region 超支费用

        /// <summary>
        /// 超支费用
        /// 1增、2改、3删
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> PostGroupExtraCost_Operator(Fin_GroupExtraCostDto_OP dto)
        {
            #region 验证

            #endregion

            Fin_GroupExtraCost _entity = new Fin_GroupExtraCost();
            _entity.DiId = dto.diId;
            _entity.PriceName = dto.priceName;
            _entity.Price = dto.price;
            _entity.PriceCurrency = dto.currency;
            _entity.PriceType = dto.priceType;
            _entity.Coefficient = dto.coefficient;
            _entity.PriceDetailType = dto.priceDetailType;
            _entity.FilePath = dto.filePath;
            _entity.Remark = dto.remark;
            _entity.PriceCount = dto.PriceCount;
            _entity.Area = dto.Area;

            DateTime dt_PriceDt;
            bool b_PriceDt = DateTime.TryParse(dto.PriceDt, out dt_PriceDt);
            if (b_PriceDt)
            {
                _entity.PriceDt = dt_PriceDt;
            }
            else
            {
                _entity.PriceDt = DateTime.MinValue;
            }
            _entity.PriceSum = dto.price * dto.PriceCount;

            _daiRep.BeginTran();
            if (dto.editType == 1)
            {
                _entity.CreateUserId = dto.createUser;
                _entity.CreateTime = DateTime.Now;
                _entity.IsDel = 0;

                int returnId = await _daiRep.AddAsyncReturnId<Fin_GroupExtraCost>(_entity);
                if (returnId > 0)
                {
                    dto.Id = returnId;
                }
            }
            else if (dto.editType == 2)
            {
                bool res = await _daiRep.UpdateAsync<Fin_GroupExtraCost>(s => s.Id == dto.Id, s => new Fin_GroupExtraCost
                {
                    PriceName = dto.priceName,
                    Price = dto.price,
                    PriceCurrency = dto.currency,
                    PriceType = dto.priceType,
                    PriceDetailType = dto.priceDetailType,
                    Coefficient = dto.coefficient,
                    FilePath = dto.filePath,
                    Remark = dto.remark,
                    PriceCount = dto.PriceCount,
                    PriceDt = _entity.PriceDt,
                    PriceSum = _entity.PriceSum,
                    Area = _entity.Area,
                });
                if (!res)
                {
                    _daiRep.RollbackTran();
                    return Ok(JsonView(false, "2操作失败！"));
                }
            }
            else if (dto.editType == 3)
            {
                string delTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm");
                bool res = await _daiRep.UpdateAsync<Fin_GroupExtraCost>(s => s.Id == dto.Id, s => new Fin_GroupExtraCost
                {
                    IsDel = 1,
                    DeleteTime = delTime,
                    DeleteUserId = dto.createUser
                });
                if (!res)
                {
                    _daiRep.RollbackTran();
                    return Ok(JsonView(false, "3操作失败！"));
                }
            }
            else
            {
                _daiRep.RollbackTran();
                return Ok(JsonView(false, "未知的editType"));
            }

            if (!extraCost_editCreditCardPayment(dto))
            {
                return Ok(JsonView(false, "ccp操作失败"));
            }


            _daiRep.CommitTran();
            return Ok(JsonView(true, "操作成功"));
        }

        private Result PostCurrencyByDiid_Sync(int diId, int CId, int currencyId)
        {
            if (diId == 0)
            {
                return new Result() { Code = -1, Msg = "请传入团组Id！" };
            }
            if (CId == 0)
            {
                return new Result() { Code = -1, Msg = "请传入类型Id！" };
            }
            if (currencyId == 0)
            {
                return new Result() { Code = -1, Msg = "请传入币种Id！" };
            }
            Grp_TeamRate _TeamRate = _sqlSugar.Queryable<Grp_TeamRate>().First(a => a.DiId == diId && a.IsDel == 0 && a.CTable == CId);
            List<CurrencyInfo> currencyInfos = new List<CurrencyInfo>();
            CurrencyInfo CurrencyRate = new CurrencyInfo();
            try
            {
                if (_TeamRate != null)
                {
                    Sys_SetData _SetData = _sqlSugar.Queryable<Sys_SetData>().First(a => a.IsDel == 0 && a.Id == currencyId);
                    if (_SetData != null)
                    {
                        currencyInfos = CommonFun.GetCurrencyChinaToList(_TeamRate.Remark);
                        CurrencyRate = currencyInfos.FirstOrDefault(a => a.CurrencyCode == _SetData.Name);
                        if (CurrencyRate != null)
                        {
                            return new Result() { Code = 0, Msg = "查询成功！", Data = CurrencyRate };
                        }
                        else
                        {
                            return new Result() { Code = -1, Msg = "暂无团组汇率，请前往设置！", Data = CurrencyRate };
                        }
                    }
                    else
                    {
                        return new Result() { Code = -1, Msg = "暂无团组汇率，请前往设置！", Data = CurrencyRate };
                    }

                }
                else
                {
                    return new Result() { Code = -1, Msg = "暂无团组汇率，请前往设置！", Data = CurrencyRate };
                }
            }
            catch (Exception)
            {
                return new Result() { Code = -1, Msg = "查询异常！", Data = CurrencyRate };
            }

        }

        private bool extraCost_editCreditCardPayment(Fin_GroupExtraCostDto_OP costDto)
        {
            //设置团组汇率
            decimal dcm_dayrate = 1M;
            decimal dcm_rmbPrice = costDto.price;
            int ispay = costDto.payType == 72 ? 1 : 0;

            if (costDto.costSign != 3)
            {
                //获取新汇率  int diId,int CId, int currencyId
                Result rate = this.PostCurrencyByDiid_Sync(costDto.diId, 1015, costDto.currency);

                if (rate.Code == 0)
                {
                    var rateInfo = (rate.Data as CurrencyInfo);
                    if (rateInfo is not null)
                    {

                        dcm_dayrate = rateInfo.Rate;
                        dcm_rmbPrice = rateInfo.Rate * dcm_rmbPrice;
                    }
                    else
                    {
                        dcm_dayrate = 1;
                    }
                }
            }

            Grp_CreditCardPayment ccp = _daiRep.Query<Grp_CreditCardPayment>(s => s.CId == costDto.Id && s.CTable == 1015).First();
            if (ccp == null)
            {
                ccp = new Grp_CreditCardPayment();

                ccp.PayDId = costDto.payType;// dto
                ccp.ConsumptionPatterns = "";
                ccp.ConsumptionDate = "";
                ccp.CTDId = costDto.payCardId;// dto
                ccp.BankNo = "";

                ccp.CardholderName = "";
                ccp.PayMoney = costDto.price;// dto
                ccp.PaymentCurrency = costDto.currency;// dto
                ccp.CompanyBankNo = "";
                ccp.OtherBankName = "";

                ccp.OtherSideNo = "";
                ccp.OtherSideName = "";
                ccp.Remark = "";
                ccp.CreateUserId = costDto.createUser;
                ccp.CreateTime = DateTime.Now;

                ccp.MFOperator = 0;
                ccp.MFOperatorDate = "";
                ccp.IsAuditDM = 0;
                ccp.AuditDMOperate = 0;
                ccp.AuditDMDate = "";

                ccp.IsAuditMF = 0;
                ccp.AuditMFOperate = 0;
                ccp.AuditMFDate = "";
                ccp.IsAuditGM = 0;
                ccp.AuditGMOperate = 0;

                ccp.AuditGMDate = "";
                ccp.IsPay = ispay; // upd
                ccp.DIId = costDto.diId;// dto
                ccp.CId = costDto.Id;// dto
                ccp.CTable = 1015; //超支费用指向id

                ccp.IsDel = 0;
                ccp.PayPercentage = 100M;
                ccp.PayThenMoney = 0M;
                ccp.PayPercentageOld = 100M;
                ccp.PayThenMoneyOld = 0M;

                ccp.UpdateDate = "";
                ccp.Payee = costDto.payee;// dto
                ccp.OrbitalPrivateTransfer = costDto.costSign;// dto
                ccp.ExceedBudget = 0;
                ccp.DayRate = dcm_dayrate; //upd

                ccp.RMBPrice = dcm_rmbPrice; //upd

                int ccpInsertId = _daiRep.AddReturnId<Grp_CreditCardPayment>(ccp);
                if (ccpInsertId > 0)
                {
                    return true;
                }
            }
            else
            {
                if (costDto.editType == 2)
                {
                    bool res = _daiRep.Update<Grp_CreditCardPayment>(s => s.Id == ccp.Id, s => new Grp_CreditCardPayment
                    {
                        PayDId = costDto.payType,
                        CTDId = costDto.payCardId,
                        PayMoney = costDto.price,
                        PaymentCurrency = costDto.currency,
                        IsPay = ispay,

                        Payee = costDto.payee,
                        OrbitalPrivateTransfer = costDto.costSign,
                        DayRate = dcm_dayrate,
                        RMBPrice = dcm_rmbPrice
                    });
                    return res;
                }
                else if (costDto.editType == 3)
                {
                    string delTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm");
                    bool res2 = _daiRep.Update<Grp_CreditCardPayment>(s => s.Id == ccp.Id, s => new Grp_CreditCardPayment
                    {
                        IsDel = 1,
                        DeleteTime = delTime,
                        DeleteUserId = costDto.createUser
                    });
                    return res2;
                }
            }


            return false;
        }

        /// <summary>
        /// 超支费用
        /// 详情查询
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> PostGroupExtraCost_Detail(Fin_GroupExtraCostDto_Detail dto)
        {
            if (dto.Id < 1)
            {
                return Ok(JsonView(false, "查询失败"));
            }

            string sql = string.Format(@" Select 
                                                f.Id,f.DiId,
												f.PriceName,f.Price,f.PriceCurrency,c.Payee,c.OrbitalPrivateTransfer,c.PayDId,f.area, 
												c.CTDId,f.PriceType,f.PriceDetailType,f.Coefficient,f.Remark,f.PriceCount,f.PriceDt													
                                                From Fin_GroupExtraCost f
                                                Inner Join Grp_CreditCardPayment c On f.Id = c.CId
                                                Left Join Sys_Users u On f.CreateUserId = u.Id
                                                Where f.IsDel=0 And c.CTable = 1015 
												And f.Id = {0} ", dto.Id);
            Fin_GroupExtraCostDetailView detailView = await _sqlSugar.SqlQueryable<Fin_GroupExtraCostDetailView>(sql).FirstAsync();
            if (detailView == null)
            {
                return Ok(JsonView(false, "查询失败"));
            }

            return Ok(JsonView(true, "查询成功", detailView));
        }

        /// <summary>
        /// 超支费用
        /// 列表查询
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> PostGroupExtraCost_Search(Fin_GroupExtraCostDto_Search dto)
        {
            string sqlWhere = string.Format(@" And f.DiId = {0} ", dto.diId);
            int startIndex = (dto.PageIndex - 1) * dto.PageSize + 1;
            int endIndex = startIndex + dto.PageSize - 1;
            string sql_data = string.Format(@"Select * From (	
                                                Select row_number() over (order by f.Id Desc) as RowNumber,f.Id,f.DiId,
												f.PriceName,f.PriceType,f.PriceDetailType,CAST(f.Price as varchar)+' '+s.[Name] as PriceStr,f.PriceCount,CAST(f.PriceSum as varchar)+' '+s.[Name] as PriceSumStr,
                                                f.CreateUserId,f.PriceDt,c.IsAuditGM												
                                                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 }));
        }

        /// <summary>
        /// 超支费用Excel导出
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        public IActionResult ExportOverspendExcel(Fin_GroupExtraCostExcelDto dto)
        {
            var jw = JsonView(false);
            var grp_DelegationInfo = _sqlSugar.Queryable<Grp_DelegationInfo>().First(x => x.Id == dto.diId && x.IsDel == 0);
            if (grp_DelegationInfo == null)
            {
                return Ok(JsonView(false, "团组信息有误！"));
            }

            var joinTable = _sqlSugar.Queryable<Fin_GroupExtraCost, Grp_CreditCardPayment,Sys_SetData , Grp_NationalTravelFee>((f, c, s, g) => new JoinQueryInfos(
                    JoinType.Left,f.Id == c.CId && c.CTable == 1015 && c.IsDel == 0 ,
                    JoinType.Left,c.PaymentCurrency == s.Id && s.IsDel == 0,
                    JoinType.Left,g.IsDel == 0 && g.Id == f.Area
                ))
                .Where((f,c,s, g) => f.IsDel == 0 && f.DiId == dto.diId)
                .Select((f,c,s,g)=> new
                {
                    c.PaymentCurrency,
                    c.DayRate,
                    PaymentCurrencyStr = s.Name,
                    f.PriceName,
                    f.Remark,
                    f.PriceCount,
                    f.Price,
                    c.PayMoney,
                    RMBPrice = c.RMBPrice * f.PriceCount,
                    Offer = c.RMBPrice * f.PriceCount * f.Coefficient,
                    c.CTable,
                    f.PriceDetailType,
                    Area = g.Country ?? "未选择地区！"
                }).ToList();

            var PriceDetailTypeArr = new int[] { 1088 , 1074, 1075, 1076 };
            var whereArr = joinTable.Where(x => PriceDetailTypeArr.Contains(x.PriceDetailType)).ToList();
            _ =  joinTable.RemoveAll(x => PriceDetailTypeArr.Contains(x.PriceDetailType));
            foreach (var item in whereArr)
            {
                joinTable.Insert(0, item);
            }

            var dicClounm = new Dictionary<int, string>()
            {
                { 0,"Area" },
                { 1,"PriceName" },
                { 2,"Remark" },
                { 3,"PriceCount" },
                { 4,"Price" },
                { 5,"RMBPrice" },
                { 6,"DayRate" },
                { 7,"Offer" },
            };

            var rowStartIndex = 2;
            string filePath = AppSettingsHelper.Get("ExcelBasePath") + "\\Template\\超支费用表.xlsx";
            IWorkbook workbook = new XSSFWorkbook(new FileStream(filePath, FileMode.Open, FileAccess.Read));
            ISheet sheet = workbook.GetSheetAt(0);
            var initStyleRow = sheet.GetRow(2);
            var clounmCount = dicClounm.Count;

            Action cloneRowFn = () =>
            {
                rowStartIndex++;

                var cloneRow = sheet.CreateRow(rowStartIndex);

                // 复制样式
                for (int i = initStyleRow.FirstCellNum; i < initStyleRow.LastCellNum; i++)
                {
                    ICell sourceCell = initStyleRow.GetCell(i);
                    ICell targetCell = cloneRow.GetCell(i) ?? cloneRow.CreateCell(i);

                    // 确保单元格存在样式
                    if (sourceCell.CellStyle != null)
                    {
                        targetCell.CellStyle = sourceCell.CellStyle;
                    }
                }
            };

            sheet.GetRow(0).GetCell(0).SetCellValue($"{grp_DelegationInfo.TeamName}—超支费用");

            foreach (var item in joinTable)
            {
                for (int i = 0; i < clounmCount; i++)
                {
                    string property = dicClounm[i];
                    string value =  item.GetType()!.GetProperty(property)!.GetValue(item)?.ToString() ?? "";
                    sheet.GetRow(rowStartIndex).GetCell(i).SetCellValue(value);
                }

                cloneRowFn();
            }

            sheet.GetRow(rowStartIndex).GetCell(2).SetCellValue($"小计:");
            sheet.GetRow(rowStartIndex).GetCell(3).SetCellValue($"{joinTable.Sum(x=>x.RMBPrice)}元");
            var path = $"GroupExtraCost/{grp_DelegationInfo.TeamName}_超支费用.xlsx";
            using (var stream = new MemoryStream())
            {
                workbook.Write(stream, true);
                stream.Flush();
                stream.Seek(0, SeekOrigin.Begin);
                stream.SaveToFile(AppSettingsHelper.Get("ExcelBasePath") + path);
            }

            string rst = AppSettingsHelper.Get("ExcelBaseUrl") + AppSettingsHelper.Get("ExcelFtpPath") + path;
            jw = JsonView(true, "成功", new { url = rst });
            return Ok(jw);
        }

        /// <summary>
        /// 获取超支系数配置 
        /// </summary>
        /// <returns></returns>
        [HttpPost]
        public IActionResult QueryCoefficientConfig()
        {
            var arr =  _sqlSugar.Queryable<Sys_SetData>().Where(x => x.STid == 80 && x.IsDel == 0).ToList();

            var carCoefficient = new int[] { 1050 }; //2.4

            var menpiaoCoefficient = new int[] { 1086 }; // 1.2

            var airCoefficient = new int[] { 1035, 1036 }; // 2.0

            var HotelCoefficient = new int[] { 1044, 1045, 1046, 1041, 1042, 1043, 1038 }; // 1.5

            var fanyiCoefficient = new int[] { 1087 }; // 1.5

            var resultArr = new
                 ArrayList();

            foreach (var item in arr)
            {
                var a = new { coefficient = 1M, id = 0 };
                if (carCoefficient.Contains(item.Id))
                {
                    a = a with
                    {
                        coefficient = 2.4M,
                        id = item.Id
                    };
                }
                else if (menpiaoCoefficient.Contains(item.Id))
                {
                    a = a with
                    {
                        coefficient = 1.2M,
                        id = item.Id
                    };
                }
                else if (airCoefficient.Contains(item.Id))
                {
                    a = a with
                    {
                        coefficient = 2.0M,
                        id = item.Id
                    };
                }
                else if (HotelCoefficient.Contains(item.Id))
                {
                    a = a with
                    {
                        coefficient = 1.5M,
                        id = item.Id
                    };
                }
                else if (fanyiCoefficient.Contains(item.Id))
                {
                    a = a with
                    {
                        coefficient = 1.5M,
                        id = item.Id
                    };
                }
                else
                {
                    a = a with
                    {
                        coefficient = 1M,
                        id = item.Id
                    };
                }

                resultArr.Add(a);
            }


            return Ok(JsonView(true, "获取成功！", resultArr));
        }

        #endregion

        #region 信用卡对账

        /// <summary>
        ///将指定的Excel的文件转换成DataTable （Excel的第一个sheet）
        /// </summary>
        /// <param name="fullFielPath">文件的绝对路径</param>
        /// <returns></returns>
        private DataTable WorksheetToTable(string fullFielPath, string sheetName = null)
        {
            //如果是“EPPlus”，需要指定LicenseContext。
            //EPPlus.Core 不需要指定。
            ExcelPackage.LicenseContext = LicenseContext.NonCommercial;

            FileInfo existingFile = new FileInfo(fullFielPath);

            ExcelPackage package = new ExcelPackage(existingFile);
            ExcelWorksheet worksheet = null;

            if (string.IsNullOrEmpty(sheetName))
            {
                //不传入 sheetName 默认取第1个sheet。
                //EPPlus 索引是0
                //EPPlus.Core 索引是1
                worksheet = package.Workbook.Worksheets[0];
            }
            else
            {
                worksheet = package.Workbook.Worksheets[sheetName];
            }

            if (worksheet == null)
                throw new Exception("指定的sheetName不存在");

            return WorksheetToTable(worksheet);
        }

        /// <summary>
        /// 将worksheet转成datatable
        /// </summary>
        /// <param name="worksheet">待处理的worksheet</param>
        /// <returns>返回处理后的datatable</returns>
        private DataTable WorksheetToTable(ExcelWorksheet worksheet)
        {
            //获取worksheet的行数
            int rows = worksheet.Dimension.End.Row;
            //获取worksheet的列数
            int cols = worksheet.Dimension.End.Column;

            DataTable dt = new DataTable(worksheet.Name);
            DataRow dr = null;
            for (int i = 1; i <= rows; i++)
            {
                if (i > 1)
                    dr = dt.Rows.Add();

                for (int j = 1; j <= cols; j++)
                {
                    //默认将第一行设置为datatable的标题
                    if (i == 1)
                        dt.Columns.Add(GetString(worksheet.Cells[i, j].Value));
                    //剩下的写入datatable
                    else
                        dr[j - 1] = GetString(worksheet.Cells[i, j].Value);
                }
            }
            return dt;
        }

        private string GetString(object obj)
        {

            if (obj == null)
                return "";

            return obj.ToString();

        }

        private DataTable ExcelFileToDataTable(IFormFile file)
        {
            DataTable dtTest = null;
            using (var stream = new MemoryStream())
            {
                file.CopyTo(stream);
                using (var package = new ExcelPackage(stream))
                {
                    ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
                    ExcelWorksheet worksheet = package.Workbook.Worksheets[0];

                    dtTest = WorksheetToTable(worksheet);
                }
            }
            return dtTest;
        }

        /// <summary>
        /// 信用卡对账
        /// </summary>
        /// <param name="file"></param>
        /// <param name="cardType"></param>
        /// <param name="beginDt"></param>
        /// <param name="endDt"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> PostCreditCardBill(IFormFile file, int cardType, string beginDt, string endDt)
        {
            if (file == null || file.Length < 1)
            {
                return Ok(JsonView(false, "请上传文件!"));
            }
            string fileName = file.FileName;
            if (!fileName.EndsWith(".xlsx") && !fileName.EndsWith(".xls"))
            {
                return Ok(JsonView(false, "请上传Excel文件!"));
            }

            if (cardType < 1) return Ok(JsonView(false, "请传入有效的卡类型!"));


            if (string.IsNullOrEmpty(beginDt) || string.IsNullOrEmpty(endDt)) return Ok(JsonView(false, "请输入开始/结束日期!"));

            var beginValid = DateTime.TryParse(beginDt, out _);
            var endValid = DateTime.TryParse(endDt, out _);
            if (!beginValid || !endValid) return Ok(JsonView(false, "请输入正确的日期格式"));

            //读取ExcelFile
            DataTable dt = ExcelFileToDataTable(file);
            dt.TableName = "TB";
            if (dt == null)
            {
                return Ok(JsonView(false, $"您上传的Excel工作表没有内容，请检查！！！"));
            }
            //信用卡信息
            string sql = string.Format($"Select * From Grp_CreditCardPayment Where Isdel = 0 And CTDId = {cardType} And ConsumptionDate between '{beginDt}' and '{endDt}' ");
            var List_ccp = await _sqlSugar.SqlQueryable<Grp_CreditCardPayment>(sql).ToListAsync();

            if (List_ccp.Count < 1)
            {
                return Ok(JsonView(false, $"未查询到 {beginDt} 至 {endDt} 信用卡账单信息！！！!"));
            }

            //资源信息
            var delegationInfos = _sqlSugar.Queryable<Grp_DelegationInfo>().Where(it => it.IsDel == 0).ToList();
            var users = _sqlSugar.Queryable<Sys_Users>().Where(it => it.IsDel == 0).ToList();
            string url = string.Empty;
            try
            {
                /*
                 * 卡类型
                 * 74	美元卡 1
                 * 75	欧元卡 1
                 * 86	招行卡 1
                 * 346	中信卡 1
                 * 363	交行卡 1
                 * 
                 */

                var ids = new List<int>();
                if (cardType == 74) //美元卡
                {
                    var AirGroupReuslt = List_ccp.Where(x => x.CTable == 85).GroupBy(x => x.DIId).ToList();
                    if (AirGroupReuslt != null && AirGroupReuslt.Count > 0)
                    {
                        foreach (var item in AirGroupReuslt)
                        {
                            if (item.Count() > 1)
                            {
                                var obj = item.FirstOrDefault();
                                obj.RMBPrice = item.Sum(x => x.PayMoney);//合计币种金额
                                obj.Remark = "团组机票总价(虚拟)";
                                List_ccp.Add(obj);
                            }
                        }
                    }

                    //dt.AcceptChanges(); //提交

                    //修改table列名
                    dt.Columns[0].ColumnName = "accountType";       // 账户类型
                    dt.Columns[1].ColumnName = "tradeDate";         //交易日期
                    dt.Columns[2].ColumnName = "BillingDate";   // 记账日期
                    dt.Columns[3].ColumnName = "CardNo";           // 卡号
                    dt.Columns[4].ColumnName = "deposit";           // 存入金额
                    dt.Columns[5].ColumnName = "SpendingAmount";   // 支出金额
                    dt.Columns[6].ColumnName = "TransactionDescription";   // 交易描述
                    dt.Columns[7].ColumnName = "TeamRemark";   // 团组备注描述
                    dt.Columns[8].ColumnName = "Handlers";   // 经手人
                    dt.Columns[9].ColumnName = "State";   // 状态

                    //删除第一行数据
                    if (dt.Rows.Count > 0)
                    {
                        string accountType = dt.Rows[0]["accountType"].ToString();
                        if (dt.Rows[0]["accountType"].ToString().Equals("账户类型"))
                        {
                            dt.Rows[0].Delete();
                            dt.AcceptChanges(); //提交
                        }
                    }


                    foreach (DataRow item in dt.Rows)
                    {
                        #region 匹配的金额
                        decimal ExcelAmount = 0.00M;
                        decimal deposit = 0.00M;
                        if (!string.IsNullOrEmpty(item["SpendingAmount"].ToString()))
                        {
                            var isParase = decimal.TryParse(item["SpendingAmount"].ToString(), out ExcelAmount);
                            if (isParase)
                            {
                                var CList = List_ccp.FindAll(x => x.PayMoney == ExcelAmount);
                                if (CList != null && CList.Count > 0)
                                {
                                    item["TeamRemark"] = delegationInfos.Find(it => it.Id == CList.First().DIId)?.TeamName;
                                    item["Handlers"] = users.Find(it => it.Id == CList.First().CreateUserId)?.CnName;
                                    item["State"] = 1;
                                }
                            }
                        }
                        if (!string.IsNullOrEmpty(item["deposit"].ToString()))
                        {
                            var isParse = decimal.TryParse(item["deposit"].ToString(), out deposit);
                            if (isParse)
                            {
                                var CList = List_ccp.FindAll(x => x.PayMoney == deposit);
                                if (CList != null && CList.Count > 0)
                                {
                                    item["TeamRemark"] = delegationInfos.Find(it => it.Id == CList.First().DIId)?.TeamName;
                                    item["Handlers"] = users.Find(it => it.Id == CList.First().CreateUserId)?.CnName;
                                    item["State"] = 1;
                                }
                            }
                        }
                        #endregion

                        //交易描述
                        if (!string.IsNullOrEmpty(item["TransactionDescription"].ToString()))
                        {
                            string TransactionDescription = item["TransactionDescription"].ToString();
                            var startIndex = TransactionDescription.LastIndexOf("[");
                            var endIndex = TransactionDescription.LastIndexOf("]");
                            if (startIndex != -1 && endIndex != -1)
                            {
                                var moenyList = TransactionDescription.Substring(startIndex + 1, endIndex - startIndex - 1).Split(' ').
                                    Where(x => !string.IsNullOrEmpty(x)).ToList();
                                decimal money = 0.00M;
                                foreach (var itemMoeny in moenyList)
                                {
                                    if (itemMoeny.Contains('.'))
                                    {
                                        string itemMoenyStr = itemMoeny.Replace(",", string.Empty);
                                        bool istrue = decimal.TryParse(itemMoenyStr, out money);
                                        if (istrue)
                                        {
                                            var CList = List_ccp.FindAll(x => x.PayMoney == money);
                                            if (CList != null && CList.Count > 0)
                                            {
                                                item["TeamRemark"] = delegationInfos.Find(it => it.Id == CList.First().DIId)?.TeamName;
                                                item["Handlers"] = users.Find(it => it.Id == CList.First().CreateUserId)?.CnName;
                                                item["State"] = 1;
                                            }
                                        }
                                    }
                                }
                            }
                        }
                    }

                    string fileName1 = $"信用卡账单（美元卡）{DateTime.Now.ToString("yyyyMMddHHmmss")}.xls";
                    Dictionary<string, object> pairs = new Dictionary<string, object>();
                    List<DataTable> datas = new List<DataTable>();
                    datas.Add(dt);
                    url = AsposeHelper.ExpertExcelToModel("信用卡对账模板-美元卡.xls", $"CreditCardBill", fileName1, pairs, datas);

                }
                else if (cardType == 75) //欧元卡
                {
                    if (dt.Rows.Count > 0)
                    {
                        string accountType = dt.Rows[0][0].ToString();
                        if (accountType.Equals("账户类型"))
                        {
                            dt.Rows[0].Delete();
                            dt.AcceptChanges(); //提交
                        }
                    }

                    //dt.Columns[0].ColumnName = "All";

                    DataTable dt1 = new DataTable();
                    dt1.TableName = "TB";
                    dt1.Columns.Add("TradingDate", Type.GetType("System.String"));//交易日期
                    dt1.Columns.Add("TallyDate", Type.GetType("System.String"));  //记账日期
                    dt1.Columns.Add("TradindDesc", Type.GetType("System.String"));//交易描述
                    dt1.Columns.Add("TradindMoney", Type.GetType("System.String"));//交易币种/金额
                    dt1.Columns.Add("ClearindMoney", Type.GetType("System.String"));//  清算币种/金额
                    dt1.Columns.Add("TeamRemark", Type.GetType("System.String"));  //团组备注描述
                    dt1.Columns.Add("Handlers", Type.GetType("System.String"));    //经手人
                    dt1.Columns.Add("State", Type.GetType("System.String"));       //状态

                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        string[] str = dt.Rows[i]["All"].ToString().Split(' ');
                        //string[] str = dt.Rows[i];
                        DataRow dr = dt1.NewRow();
                        dr["TradingDate"] = str[0].ToString();
                        dr["TallyDate"] = str[1].ToString();
                        dr["TradindDesc"] = str[2].ToString() + " " + str[3].ToString();
                        dr["TradindMoney"] = str[4].ToString() + " " + str[5].ToString();
                        dr["ClearindMoney"] = str[6].ToString() + " " + str[7].ToString();
                        dt1.Rows.Add(dr);
                    }

                    foreach (DataRow item in dt1.Rows)
                    {
                        for (int i = 0; i < List_ccp.Count; i++)
                        {
                            DateTime dtTime1 = Convert.ToDateTime(item["TradingDate"].ToString() == "" ? DateTime.Now.ToString("MM/dd") : item["TradingDate"].ToString());  //交易日期
                            string USDPrice = item["TradindMoney"].ToString();          //支出金额
                            USDPrice = USDPrice.Split(' ')[1].Trim(); //替换掉非数字

                            //USDPrice = USDPrice.Split('/')[1];
                            // 经手人 and excel行状态
                            if (!string.IsNullOrEmpty(List_ccp[i].ConsumptionDate) && !string.IsNullOrEmpty(USDPrice))
                            {
                                DateTime dtTime2 = Convert.ToDateTime(List_ccp[i].ConsumptionDate.ToString() == "" ? DateTime.Now.ToString("MM/dd") : List_ccp[i].ConsumptionDate.ToString());
                                float price1 = float.Parse(USDPrice);
                                float price2 = float.Parse(List_ccp[i].PayMoney.ToString("F2"));
                                if (dtTime1 == dtTime2 && price1 == price2)
                                {
                                    item["TeamRemark"] = delegationInfos.Find(it => it.Id == List_ccp[i].DIId)?.TeamName;
                                    item["Handlers"] = users.Find(it => it.Id == List_ccp[i].CreateUserId)?.CnName;
                                    item["State"] = "1";
                                    i = List_ccp.Count - 1;
                                }
                                else item["State"] = "0";
                            }
                            else item["State"] = "0";
                        }

                        if (item["TradindDesc"].ToString().Contains("财付通"))
                            item["State"] = "2";
                        else if (item["TradindDesc"].ToString().Contains("京东"))
                            item["State"] = "2";
                        else if (item["TradindDesc"].ToString().Contains("微信支付"))
                            item["State"] = "2";
                        else if (item["TradindDesc"].ToString().Contains("淘宝"))
                            item["State"] = "2";
                    }

                    string fileName1 = $"信用卡账单（欧元卡）{DateTime.Now.ToString("yyyy.MM.dd")}.xls";
                    Dictionary<string, object> pairs = new Dictionary<string, object>();
                    List<DataTable> datas = new List<DataTable>();
                    datas.Add(dt);
                    url = AsposeHelper.ExpertExcelToModel("信用卡对账模板-欧元卡.xls", "TB", fileName1, pairs, datas);


                }
                else if (cardType == 86) //招行卡
                {
                    var AirGroupReuslt1 = List_ccp.Where(x => x.CTable == 85).GroupBy(x => x.DIId).ToList();
                    if (AirGroupReuslt1 != null && AirGroupReuslt1.Count > 0)
                    {
                        foreach (var item in AirGroupReuslt1)
                        {
                            if (item.Count() > 1)
                            {
                                var di = delegationInfos.Find(it => it.Id == item.Key);
                                var obj = item.FirstOrDefault();
                                obj.Remark = "团组机票总价";
                                if (di != null)
                                {
                                    obj.Remark = "团组机票总价(" + di.TeamName + ")";
                                }
                                obj.RMBPrice = item.Sum(x => x.RMBPrice);//合计人民币
                                List_ccp.Add(obj);
                            }
                        }
                    }

                    //查询所有刷卡的团组
                    //将刷卡团组内刷卡金额的金额存入ccp_list
                    //将excel作比较

                    //机票刷卡记录
                    var AirListAndPayID = List_ccp.Where(x => x.CTable == 85 && x.PayDId == 72).Select(x => x.DIId).Distinct().ToList();
                    var AirData = _sqlSugar.Queryable<Grp_AirTicketReservations>().Where(it => AirListAndPayID.Contains(it.DIId) && it.PrePrice != it.Price).ToList();
                    foreach (var item in AirData)
                    {
                        var DBwhere = List_ccp.Find(x => x.PayMoney == item.PrePrice);
                        if (DBwhere == null)
                        {
                            List_ccp.Add(new Grp_CreditCardPayment
                            {
                                PayMoney = item.PrePrice,
                                DIId = item.DIId,
                                CreateUserId = item.CreateUserId,
                                Remark = "(机票单价价格)",
                            });
                        }
                    }

                    var HotelGroupReuslt = List_ccp.Where(x => x.CTable == 76).GroupBy(x => x.DIId).ToList();
                    if (HotelGroupReuslt != null && HotelGroupReuslt.Count > 0)
                    {
                        foreach (var item in HotelGroupReuslt)
                        {
                            if (item.Count() > 1)
                            {
                                var di = delegationInfos.Find(it => it.Id == item.Key);
                                var obj = item.FirstOrDefault();
                                obj.Remark = "团组酒店总价";
                                if (di != null)
                                {
                                    obj.Remark = "团组酒店总价(" + di.TeamName + ")";
                                }
                                obj.RMBPrice = item.Sum(x => x.RMBPrice);//合计人民币
                                List_ccp.Add(obj);
                            }
                        }
                    }

                    #region
                    dt.Rows[0].Delete();//删除列名行
                    dt.AcceptChanges();

                    DataTable dt1 = new DataTable();
                    dt1.TableName = "TB";
                    dt1.Columns.Add("TradingDay", Type.GetType("System.String"));           // 交易日  
                    dt1.Columns.Add("TallyDay", Type.GetType("System.String"));               // 记账日
                    dt1.Columns.Add("TransactionDesc", Type.GetType("System.String")); // 交易描述
                    dt1.Columns.Add("RMBMoney", Type.GetType("System.String"));               // 人名币金额
                    dt1.Columns.Add("CardNo", Type.GetType("System.String"));               // 卡号    
                    dt1.Columns.Add("TradingMoney", Type.GetType("System.String"));   // 交易地金额    
                    dt1.Columns.Add("Currency", Type.GetType("System.String"));   // 币种 
                    dt1.Columns.Add("TeamRemark", Type.GetType("System.String"));  //团组备注描述
                    dt1.Columns.Add("Handlers", Type.GetType("System.String"));    //经手人
                    dt1.Columns.Add("State", Type.GetType("System.String"));       //状态

                    for (int j = 0; j < dt.Rows.Count; j++)//遍历行
                    {
                        DataRow dr = dt1.NewRow(); //获取每一行的数据
                        dr["TradingDay"] = dt.Rows[j][0].ToString();
                        dr["TallyDay"] = dt.Rows[j][1].ToString();
                        dr["TransactionDesc"] = dt.Rows[j][2].ToString();
                        dr["RMBMoney"] = dt.Rows[j][3].ToString();
                        dr["CardNo"] = dt.Rows[j][4].ToString();
                        string currency = "CN";
                        string money = dt.Rows[j][5].ToString();
                        if (money.Contains('('))
                        {
                            string[] strs = money.Split('(');
                            money = strs[0];
                            currency = strs[1].Replace(")", "");
                        }
                        dr["TradingMoney"] = money;
                        dr["Currency"] = currency;
                        if (string.IsNullOrEmpty(money))
                        {
                            continue;
                        }
                        dt1.Rows.Add(dr);
                    }

                    //datatable 排序
                    dt1.DefaultView.Sort = "TradingDay asc";
                    dt1 = dt1.DefaultView.ToTable();

                    foreach (DataRow item in dt1.Rows)
                    {
                        if (item["TradingDay"] == "" && item["RMBMoney"] == "")
                        {
                            continue;
                        }

                        //excel获取交易日期
                        string ExcelDt = string.Empty;
                        //C表交易日期
                        string DBDt = string.Empty;
                        //excel交易的金额
                        decimal TradingMoney = decimal.Parse(item["TradingMoney"].ToString());

                        if (item["TradingDay"] != null)
                        {
                            ExcelDt = DateTime.Parse(item["TradingDay"].ToString()).ToString("MMdd");
                        }

                        //加一获取金额区间
                        decimal MaxTradingMoney = TradingMoney + 1;
                        decimal MinTradingMoney = TradingMoney - 1;

                        //匹配C表金额相等的值
                        var CList = List_ccp.FindAll(x => x.PayMoney < MaxTradingMoney && x.PayMoney > MinTradingMoney);

                        if (CList != null && CList.Count > 0)
                        {
                            #region 日期匹配
                            foreach (var Citem in CList)
                            {
                                if (!string.IsNullOrWhiteSpace(Citem.ConsumptionDate))
                                {
                                    DBDt = DateTime.Parse(Citem.ConsumptionDate).ToString("MMdd");

                                    if (DBDt == ExcelDt)
                                    {
                                        item["TeamRemark"] = delegationInfos.Find(it => it.Id == Citem.DIId)?.TeamName;
                                        item["Handlers"] = users.Find(it => it.Id == Citem.CreateUserId)?.CnName;

                                        ids.Add(Citem.Id);
                                    }
                                }
                            }
                            #endregion
                        }

                        if (item["TransactionDesc"].ToString().Contains("财付通"))
                            item["State"] = "2";
                        else if (item["TransactionDesc"].ToString().Contains("京东支付"))
                            item["State"] = "2";
                        else if (item["TransactionDesc"].ToString().Contains("微信支付"))
                            item["State"] = "2";
                        else if (item["TransactionDesc"].ToString().Contains("淘宝"))
                            item["State"] = "2";
                        else if (item["TransactionDesc"].ToString().Contains("支付宝"))
                            item["State"] = "2";
                        else if (item["TransactionDesc"].ToString().Contains("拼多多"))
                            item["State"] = "2";
                        else
                            item["State"] = "0";

                        if (!string.IsNullOrEmpty(item["TeamRemark"].ToString()) && !string.IsNullOrEmpty(item["Handlers"].ToString()))
                            item["State"] = "1";
                    }

                    //更改匹配项状态
                    if (ids.Count > 0)
                    {
                        var _CreditCardPayments = new List<Grp_CreditCardPayment>();
                        foreach (var item in ids)
                        {
                            _CreditCardPayments.Add(new Grp_CreditCardPayment() { Id = item, IsMatchCreditCard = 1.00M });
                        }
                        var updateStatus = _sqlSugar.Updateable<Grp_CreditCardPayment>(_CreditCardPayments)
                                                    .UpdateColumns(it => new { it.IsMatchCreditCard })
                                                    .Where(it => ids.Contains(it.Id))
                                                    .ExecuteCommand();
                    }

                    string fileName1 = $"信用卡账单（招行卡）{DateTime.Now.ToString("yyyyMMddHHmmss")}.xls";
                    Dictionary<string, object> pairs = new Dictionary<string, object>();
                    List<DataTable> datas = new List<DataTable>();
                    datas.Add(dt1);
                    url = AsposeHelper.ExpertExcelToModel("信用卡对账模板-招行卡.xls", "CreditCardBill", fileName1, pairs, datas);


                    #endregion

                }
                else if (cardType == 346) //中信卡
                {
                    dt.Columns.Add("TeamRemark", Type.GetType("System.String"));  //团组备注描述
                    dt.Columns.Add("Handlers", Type.GetType("System.String"));    //经手人
                    dt.Columns.Add("State", Type.GetType("System.String"));       //状态

                    //修改table列名
                    dt.Columns[0].ColumnName = "TransactionDesc";           // 交易描述
                    dt.Columns[1].ColumnName = "TradingCurrencyAndMoney";   // 交易币种和金额
                    dt.Columns[2].ColumnName = "TransactionDate";           // 交易日期
                    dt.Columns[3].ColumnName = "SettlementCurrencyAndMoney";// 结算币种和金额
                    dt.Columns[4].ColumnName = "BookedDate";                // 入账日期

                    foreach (DataRow item in dt.Rows)
                    {
                        for (int i = 0; i < List_ccp.Count; i++)
                        {
                            if (string.IsNullOrEmpty(item["TransactionDate"].ToString()))
                                break;

                            DateTime dtTime1 = Convert.ToDateTime(item["TransactionDate"].ToString());  //交易日期
                            string USDPrice = item["TradingCurrencyAndMoney"].ToString();          //支出金额
                            USDPrice = USDPrice.Split('/')[1];
                            //string USDPrice1 = USDPrice.ToString("0.00");
                            // 经手人 and excel行状态
                            if (!string.IsNullOrEmpty(List_ccp[i].ConsumptionDate) && !string.IsNullOrEmpty(USDPrice))
                            {
                                DateTime dtTime2 = Convert.ToDateTime(List_ccp[i].ConsumptionDate);
                                float price1 = float.Parse(USDPrice);
                                float price2 = float.Parse(List_ccp[i].PayMoney.ToString("F2"));
                                if (dtTime1 == dtTime2 && price1 == price2)
                                {
                                    item["TeamRemark"] = delegationInfos.Find(it => it.Id == List_ccp[i].DIId)?.TeamName;
                                    item["Handlers"] = users.Find(it => it.Id == List_ccp[i].CreateUserId)?.CnName;
                                    item["State"] = "1";
                                    i = List_ccp.Count - 1;
                                }
                                else
                                    item["State"] = "0";
                            }
                            else
                                item["State"] = "0";

                        }
                        //判断是否是数字
                        //if (IsNumber(item["TradingCurrencyAndMoney"].ToString().Split('/')[1]) == false)
                        //    item["State"] = "0";

                        if (item["TransactionDesc"].ToString().Contains("财付通"))
                            item["State"] = "2";
                        else if (item["TransactionDesc"].ToString().Contains("京东"))
                            item["State"] = "2";
                        else if (item["TransactionDesc"].ToString().Contains("微信支付"))
                            item["State"] = "2";
                        else if (item["TransactionDesc"].ToString().Contains("淘宝"))
                            item["State"] = "2";
                    }

                    #region DownExcel

                    string fileName1 = $"信用卡账单（中信卡）{DateTime.Now.ToString("yyyy.MM.dd")}.xls";
                    Dictionary<string, object> pairs = new Dictionary<string, object>();
                    List<DataTable> datas = new List<DataTable>();
                    datas.Add(dt);
                    url = AsposeHelper.ExpertExcelToModel("信用卡对账模板-中信卡.xls", "TB", fileName1, pairs, datas);


                    #endregion

                }
                else if (cardType == 363) //交行卡
                {
                    dt.Columns[0].ColumnName = "All";

                    DataTable dt1 = new DataTable();
                    dt1.Columns.Add("TradingDate", Type.GetType("System.String"));//交易日期
                    dt1.Columns.Add("TallyDate", Type.GetType("System.String"));  //记账日期
                    dt1.Columns.Add("TradindDesc", Type.GetType("System.String"));//交易描述
                    dt1.Columns.Add("TradindMoney", Type.GetType("System.String"));//交易币种/金额
                    dt1.Columns.Add("ClearindMoney", Type.GetType("System.String"));//  清算币种/金额
                    dt1.Columns.Add("TeamRemark", Type.GetType("System.String"));  //团组备注描述
                    dt1.Columns.Add("Handlers", Type.GetType("System.String"));    //经手人
                    dt1.Columns.Add("State", Type.GetType("System.String"));       //状态

                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        string[] str = dt.Rows[i]["All"].ToString().Split(' ');
                        DataRow dr = dt1.NewRow();
                        dr["TradingDate"] = str[0].ToString();
                        dr["TallyDate"] = str[1].ToString();
                        dr["TradindDesc"] = str[2].ToString() + " " + str[3].ToString();
                        dr["TradindMoney"] = str[4].ToString() + " " + str[5].ToString();
                        dr["ClearindMoney"] = str[6].ToString() + " " + str[7].ToString();
                        dt1.Rows.Add(dr);
                    }

                    foreach (DataRow item in dt1.Rows)
                    {
                        for (int i = 0; i < List_ccp.Count; i++)
                        {
                            DateTime dtTime1 = Convert.ToDateTime(item["TradingDate"].ToString() == "" ? DateTime.Now.ToString("MM/dd") : item["TradingDate"].ToString());  //交易日期
                            string USDPrice = item["TradindMoney"].ToString();          //支出金额
                            USDPrice = USDPrice.Split(' ')[1].Trim(); //替换掉非数字

                            //USDPrice = USDPrice.Split('/')[1];
                            // 经手人 and excel行状态
                            if (!string.IsNullOrEmpty(List_ccp[i].ConsumptionDate) && !string.IsNullOrEmpty(USDPrice))
                            {
                                DateTime dtTime2 = Convert.ToDateTime(List_ccp[i].ConsumptionDate.ToString() == "" ? DateTime.Now.ToString("MM/dd") : List_ccp[i].ConsumptionDate.ToString());
                                float price1 = float.Parse(USDPrice);
                                float price2 = float.Parse(List_ccp[i].PayMoney.ToString("F2"));
                                if (dtTime1 == dtTime2 && price1 == price2)
                                {
                                    item["TeamRemark"] = delegationInfos.Find(it => it.Id == List_ccp[i].DIId)?.TeamName;

                                    item["Handlers"] = users.Find(it => it.Id == List_ccp[i].CreateUserId)?.CnName;
                                    item["State"] = "1";
                                    i = List_ccp.Count - 1;
                                }
                                else
                                    item["State"] = "0";
                            }
                            else
                                item["State"] = "0";
                        }

                        if (item["TradindDesc"].ToString().Contains("财付通"))
                            item["State"] = "2";
                        else if (item["TradindDesc"].ToString().Contains("京东"))
                            item["State"] = "2";
                        else if (item["TradindDesc"].ToString().Contains("微信支付"))
                            item["State"] = "2";
                        else if (item["TradindDesc"].ToString().Contains("淘宝"))
                            item["State"] = "2";
                    }

                    string fileName1 = $"信用卡账单（交行卡）{DateTime.Now.ToString("yyyy.MM.dd")}.xls";
                    Dictionary<string, object> pairs = new Dictionary<string, object>();
                    List<DataTable> datas = new List<DataTable>();
                    datas.Add(dt);
                    url = AsposeHelper.ExpertExcelToModel("信用卡对账模板-交行卡.xls", "TB", fileName1, pairs, datas);



                }
            }
            catch (Exception ex)
            {
                return Ok(JsonView(false, $"匹配失败，{ex.Message}!"));
            }

            return Ok(JsonView(true, "操作成功", new { url = url }));
        }


        /// <summary>
        /// 把数据从Excel装载到DataTable
        /// </summary>
        /// <param name="pathName">带路径的Excel文件名</param>
        /// <param name="sheetName">工作表名</param>
        /// <param name="tbContainer">将数据存入的DataTable</param>
        /// <returns></returns>
        public static DataTable ExcelToDataTable(string pathName, string sheetName)
        {
            DataTable tbContainer = new DataTable();
            string strConn = string.Empty;
            if (string.IsNullOrEmpty(sheetName)) { sheetName = "Sheet1"; }
            FileInfo file = new FileInfo(pathName);
            if (!file.Exists) { throw new Exception("文件不存在"); }
            string extension = file.Extension;
            switch (extension)
            {
                case ".xls":
                    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathName + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";
                    break;
                case ".xlsx":
                    strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + pathName + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1;'";
                    break;
                default:
                    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathName + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";
                    break;
            }
            //链接Excel
            OleDbConnection cnnxls = new OleDbConnection(strConn);
            //读取Excel里面有 表Sheet1
            System.Data.OleDb.OleDbDataAdapter oda = new System.Data.OleDb.OleDbDataAdapter(string.Format("select * from [{0}$]", sheetName), cnnxls);
            DataSet ds = new DataSet();
            //将Excel里面有表内容装载到内存表中！
            oda.Fill(tbContainer);
            return tbContainer;
        }


        #endregion

        #region 提成计算
        /// <summary>
        /// 提成计算
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        public IActionResult ComputeRoyalties(ComputeRoyaltiesDto dto)
        {
            var jw = JsonView(false);
            var userId = dto.UserId;
            var user =  _sqlSugar.Queryable<Sys_Users>().First(x=>x.Id == userId);
            //jw.Data = System.Array.Empty<string>();

            if (user == null)
            {
                jw.Msg = "暂无该用户！";
                return Ok(jw);
            }
            if (!DateTime.TryParse(dto.StartDate,out DateTime StartDateTime))
            {
                jw.Msg = "开始时间格式有误！";
                return Ok(jw);
            }
            if (!DateTime.TryParse(dto.EndDate, out DateTime EndDateTime))
            {
                jw.Msg = "结束时间格式有误！";
                return Ok(jw);
            }
            if (DateTime.Compare(StartDateTime,EndDateTime) > 0)
            {
                jw.Msg = "开始时间大于结束时间！";
                return Ok(jw);
            }

            //机票 职位表id 24
            int[] AirTicket = _sqlSugar.Queryable<Sys_Users>().Where(x => x.IsDel == 0 && x.JobPostId == 24).Select(x => x.Id).ToArray();

            //酒店 职位表id 25
            int[] Hotel = _sqlSugar.Queryable<Sys_Users>().Where(x => x.IsDel == 0 && x.JobPostId == 25).Select(x => x.Id).ToArray();

            //OP 职位表id 28 
            int [] OP = _sqlSugar.Queryable<Sys_Users>().Where(x=>x.IsDel == 0 && x.JobPostId == 28).Select(x=>x.Id).ToArray();

            //商邀 职位表id 27
            int [] Invitation = _sqlSugar.Queryable<Sys_Users>().Where(x => x.IsDel == 0 && x.JobPostId == 27).Select(x => x.Id).ToArray();

            //签证 职位表id 26
            int[] Visa = _sqlSugar.Queryable<Sys_Users>().Where(x => x.IsDel == 0 && x.JobPostId == 26 ).Select(x => x.Id).ToArray();

            //主管职位
            //string[] Manager = { "149", "283" };

            List<ComputeRoyaltiesView> resultArr = new List<ComputeRoyaltiesView>();

            var teamLv = _sqlSugar.Queryable<Sys_SetData>().Where(x => x.STid == 56 && x.IsDel == 0).ToList();

            var auditState = new int[] { 1 , 3 };

            if (AirTicket.Contains(userId))
            {
                //提成方式：￥10/人×人数×段数（每个组团涉及的换机次数）
                var listAir = _sqlSugar.Queryable<Grp_AirTicketReservations, Grp_DelegationInfo, Grp_CreditCardPayment>((x, a, b) => new
                JoinQueryInfos(
                    JoinType.Left, a.Id == x.DIId ,
                    JoinType.Left, b.CTable == 85 && b.CId == x.Id )
                ).Where((x, a, b) => x.IsDel == 0 && x.CreateUserId == userId  && (b.IsAuditGM == 1 || b.IsAuditGM == 3)
                    && a.IsDel == 0 && a.VisitDate >= StartDateTime && a.VisitDate <= EndDateTime && b.IsDel == 0 && a.IsSure == 1 && auditState.Contains(b.IsAuditGM) )
                    .Select((x, a, b) => new
                    {
                        x.FlightsDescription,
                        a.Id,
                        a.TeamName,
                        a.VisitDate,
                        a.TeamLevSId,
                        a.VisitPNumber,
                        x.Price,
                        x.ClientName,
                        x.ClientNum,
                        x.FlightsCode,
                    }).ToList();

                var groupByid = listAir.GroupBy(x => x.Id);
                foreach (var airArr in groupByid)
                {
                    var groupAirCount = 0; //团组航段数量
                    var infos = new List<string>();
                    foreach (var air in airArr)
                    {
                        var airCount = 0; // 单条航段数量

                        if (air.FlightsDescription.IsNullOrWhiteSpace())
                        {
                            continue;
                        }

                        if (air.ClientName.Contains("-1") || air.ClientName.Contains("行程单") || air.FlightsCode.Contains("行程单"))
                        {
                            continue;
                        }

                        string errMsg = string.Empty;

                        if (air.FlightsDescription.Contains("\r\n"))
                        {
                            var rowCode = Regex.Split(air.FlightsDescription, "\\d+\\.", RegexOptions.IgnoreCase).Where(x=>!string.IsNullOrWhiteSpace(x)).ToArray();
                            for (int i = 0; i < rowCode.Length; i++)
                            {
                                if (i != 0)
                                {
                                    try
                                    {
                                        var columnValue = rowCode[i].Split(' ').Where(x => !string.IsNullOrEmpty(x)).ToList();
                                        string day = columnValue[2].Substring(2, 2);//日
                                        string monthAbbreviations = columnValue[2].Substring(4, 3).ToUpper();//月份
                                        monthAbbreviations = GeneralMethod.GetLonger(monthAbbreviations);
                                        string EndHHmm = columnValue[5].Substring(0, 2) + ":" + columnValue[5].Substring(2, 2);
                                        DateTime EndData = DateTime.Parse($"{DateTime.Now.Year}-{monthAbbreviations}-{day} {EndHHmm}");//当前班次

                                        var spDotandEmpty1 = rowCode[i - 1].Split(' ').Where(x => !string.IsNullOrEmpty(x)).ToList();
                                        string day1 = spDotandEmpty1[2].Substring(2, 2);//日
                                        string monthAbbreviations1 = spDotandEmpty1[2].Substring(4, 3).ToUpper();//月份
                                        monthAbbreviations1 = GeneralMethod.GetLonger(monthAbbreviations1);
                                        string HHmm = spDotandEmpty1[6].Substring(0, 2) + ":" + spDotandEmpty1[6].Substring(2, 2);
                                        DateTime data = DateTime.Parse($"{DateTime.Now.Year}-{monthAbbreviations1}-{day1} {HHmm}");//上个班次

                                        TimeSpan t3 = EndData - data;  //两个时间相减 。默认得到的是 两个时间之间的天数   得到：365.00:00:00  
                                        double getHour = t3.TotalHours;

                                        if (getHour >= 6)
                                        {
                                            if (air.Price > 0)
                                            {
                                                airCount += 1;
                                            }
                                            else
                                            {
                                                airCount -= 1;
                                            }
                                        }
                                    }
                                    catch (Exception ex)
                                    {
                                        errMsg  = "机票行程代码格式有误！解析失败!" + $"({ex.Message})";
                                        break;
                                    }
                                }
                                else
                                {
                                    if (air.Price > 0)
                                    {
                                        airCount += 1;
                                    }
                                    else
                                    {
                                        airCount -= 1;
                                    }
                                }
                            }
                        }
                        else
                        {
                            if (air.Price > 0)
                            {
                                airCount++;
                            }
                            else
                            {
                                airCount--;
                            }
                        }

                        string info = $"航班{air.FlightsCode} {(air.Price > 0 ? "" : "此笔为退费!")} 人数为{air.ClientNum} 有效段数为{airCount} 总段数为{airCount * air.ClientNum}";
                        if (!string.IsNullOrWhiteSpace(errMsg))
                        {
                            info = $"航班{air.FlightsCode} " + errMsg ;
                        }
                        infos.Add(info); 
                        groupAirCount += airCount * air.ClientNum;
                    }
                    var groupinfo = listAir.First(x => x.Id == airArr.Key);
                    string temp = "本团人数" + Convert.ToInt32(groupinfo.VisitPNumber) + "，该人员" + user.CnName + "共订航段数" + groupAirCount.ToString() + "，每程航班提成为10元，" + user.CnName + "提成共" + (groupAirCount * 10).ToString("#0.00");
                    resultArr.Add(new ComputeRoyaltiesView
                    {
                        TeamName = groupinfo.TeamName,
                        TeamId = groupinfo.Id,
                        ChiArr = infos,
                        TeamLvStr = teamLv.Find(x=>x.Id == groupinfo.TeamLevSId)?.Name,
                        VisitDate = groupinfo.VisitDate,
                        Temp = temp,
                        Price = (groupAirCount * 10),
                    }) ;
                }
            }
            else if (Hotel.Contains(userId))
            {
                //标准团组：￥10/间/晚 * 实际订房间数 * 实际订房晚数； setdata id 1001 客人房
                var listHotel = _sqlSugar.Queryable<Grp_HotelReservations, Grp_DelegationInfo, Grp_CreditCardPayment>((x, a, b) => new JoinQueryInfos(
                     JoinType.Left,x.DiId == a.Id,
                     JoinType.Left,b.CTable == 76 && b.CId == x.Id  ))
                    .Where((x, a, b) => x.IsDel == 0 && x.CreateUserId == userId && a.VisitDate >= StartDateTime 
                    && a.VisitDate <= EndDateTime && a.IsDel == 0 && b.IsDel == 0 && x.CardPrice > 0 && a.IsSure == 1 && auditState.Contains(b.IsAuditGM) && x.CheckType == 1001).Select((x,a,b)=> new
                    {
                        a.Id,
                        a.TeamName,
                        a.VisitDate,
                        a.TeamLevSId,
                        a.VisitPNumber,
                        x.SingleRoomCount,
                        x.SuiteRoomCount,
                        x.DoubleRoomCount,
                        x.OtherRoomCount, 
                        x.CheckInDate,
                        x.CheckOutDate,
                        x.HotelName
                    }).ToList();

                var groupByid = listHotel.GroupBy(x => x.Id);
                var propertys = new string[] { "SingleRoomCount", "SuiteRoomCount", "DoubleRoomCount", "OtherRoomCount" };

                foreach (var group in groupByid)
                {
                    var infos = new List<string>();
                    var totalPrice = 0.00M;

                    foreach (var item in group)
                    {
                        var hotelCount = 0;
                        foreach (var property in propertys)
                        {
                            var value =  item.GetType().GetProperty(property).GetValue(item).ObjToInt();
                            if (value > 0)
                            {
                                hotelCount += value;
                            }
                        }

                        if (hotelCount > 0)
                        {
                            if (DateTime.TryParse(item.CheckInDate, out DateTime checkin) && DateTime.TryParse(item.CheckOutDate, out DateTime checkout))
                            {
                                var day = checkout - checkin;
                                infos.Add($"{item.HotelName} 共有 {hotelCount} 间房 ， 入住时间{item.CheckInDate} - {item.CheckOutDate} 共 { day.TotalDays }晚 提成数量:{hotelCount * day.TotalDays}  提成金额:{hotelCount * day.TotalDays * 10}");
                                totalPrice += (decimal)(hotelCount * day.TotalDays * 10);
                            }
                            else
                            {
                                infos.Add(item.HotelName + " 时间有误！请检查 。");
                            }
                        }
                    }

                    var groupinfo = listHotel.First(x => x.Id == group.Key);
                    var temp = "本团人数" + groupinfo.VisitPNumber + "，该人员" + user.CnName + "共操作酒店晚数" + totalPrice / 10 + "，每间每晚提成10元，" + user.CnName + "提成共" + totalPrice.ToString("#0.00") + "元";
                    resultArr.Add(new ComputeRoyaltiesView
                    {
                        TeamName = groupinfo.TeamName,
                        TeamId =  groupinfo.Id,
                        ChiArr = infos,
                        TeamLvStr = teamLv.Find(x => x.Id == groupinfo.TeamLevSId)?.Name,
                        VisitDate =  groupinfo.VisitDate,
                        Temp = temp,
                        Price = totalPrice,
                    });
                }

            }
            else if (OP.Contains(userId))
            {
                var OPList = _sqlSugar.Queryable<Grp_DelegationInfo>().Where((a) => a.IsDel == 0
                     && a.VisitDate >= StartDateTime && a.VisitDate <= EndDateTime && a.IsSure == 1
                 ).ToList();

                var LvArr = _sqlSugar.Queryable<Sys_SetData>().Where(x => x.STid == 70 && x.IsDel == 0).ToList();

                foreach (var groupinfo in OPList)
                {
                    var price = LvArr.Find(x => x.Id == groupinfo.OpRoyaltyLv)?.Name.ObjToInt() ?? 0;
                    string temp = $"本团等级为{teamLv.Find(x => x.Id == groupinfo.TeamLevSId)?.Name} 提成金额为{price} {(string.IsNullOrWhiteSpace(groupinfo.OpRoyaltyRemark) ? "" : "提成说明:" + groupinfo.OpRoyaltyRemark )}";
                    if (price == 0)
                    {
                        temp = $"本团等级为{teamLv.Find(x => x.Id == groupinfo.TeamLevSId)?.Name} op提成选项未选择！" ;
                    }
                    resultArr.Add(new ComputeRoyaltiesView
                    {
                        TeamName = groupinfo.TeamName,
                        TeamId = groupinfo.Id,
                        ChiArr = new List<string>() { temp },
                        TeamLvStr = teamLv.Find(x => x.Id == groupinfo.TeamLevSId)?.Name,
                        VisitDate = groupinfo.VisitDate,
                        Temp = temp,
                        Price = price
                    });
                }

            }
            else if (Invitation.Contains(userId))
            {
                //2)付费公务活动部分：￥50 / 团；
                //4)不付费公务活动部分（自己联络）额外奖励：￥200 / 团（若公务活动方与邀请方为同一机构，奖励金额为￥100 / 团）；
                //5)只发邀请的团组不付费邀请额外奖励：￥100 / 团；
                //邀请方已授权我司签发的邀请仍按照：￥100 / 团，无额外奖励。 
                var officialList = _sqlSugar.Queryable<Res_OfficialActivities, Grp_DelegationInfo>((x,a)=> 
                    new JoinQueryInfos(JoinType.Left,a.Id == x.DiId)
                ).Where((x,a) => x.IsSubmitApproval == 0 && x.IsDel == 0 && a.IsDel == 0
                    && a.VisitDate >= StartDateTime && a.VisitDate <= EndDateTime && a.IsSure == 1
                )
                .Select((x, a) => new
                {
                    a.Id,
                    a.TeamName,
                    a.VisitDate,
                    x.CreateUserId,
                    x.Type,
                    a.TeamLevSId,
                    x.Country,
                    x.Area,
                    x.Date,
                    x.Client
                })
                .ToList();

                var groups = officialList.GroupBy(x => x.Id);

                foreach (var group in groups)
                {
                    var groupinfo = officialList.First(x => x.Id == group.Key);
                    var officialGroupList = group.GroupBy(d => d.CreateUserId);
                    int operUserMax = 0, operMaxCount = 0;

                    foreach (var item in officialGroupList)
                    {
                        if (item.Count() > operMaxCount)
                        {
                            operUserMax = item.Key;
                            operMaxCount = item.Count();
                        }
                    }

                    string temp = string.Empty;
                    bool isBasics = operUserMax == userId;
                    var money = 0;

                    var infos = new List<string>();
                    foreach (var item in group)
                    {
                        if (item.CreateUserId != user.Id)
                        {
                            continue;
                        }

                        string info = string.Empty;
                        if (item.Type == 0)
                        {
                            money += (100 + 300);
                            info = $"{item.Country} - {item.Area} - {item.Client} - {item.Date} 提成金额为 400";
                        }
                        else if (item.Type == 1)
                        {
                            money += 300;
                            info = $"{item.Country} - {item.Area} - {item.Client} - {item.Date} 提成金额为 300";
                        }
                        else if (item.Type == 2)
                        {
                            money += 200;
                            info = $"{item.Country} - {item.Area} - {item.Client} - {item.Date} 提成金额为 200";
                        }
                        infos.Add(info);
                    }

                    if (isBasics)
                    {
                        infos.Add($"{groupinfo.TeamName} 该人员{user.CnName}操作场数最多，基本提成为{150}");
                    }
                    else
                    {
                        infos.Add($"{groupinfo.TeamName} 操作场数不是最多人员，所以无基本提成");
                    }

                    resultArr.Add(new ComputeRoyaltiesView
                    {
                        TeamName = groupinfo.TeamName,
                        TeamId =  groupinfo.Id,
                        ChiArr = infos,
                        TeamLvStr = teamLv.Find(x => x.Id == groupinfo.TeamLevSId)?.Name,
                        VisitDate = groupinfo.VisitDate,
                        Temp =  temp,
                        Price = money,
                    });
                }
            }
            else if (Visa.Contains(userId)) //签证
            {
                // 1）办理英国、加拿大、美国签证￥50元 / 人 / 团；
                // 申根国家及其他所有国家￥30元 / 人 / 团；
                // setdata id 974 客人
                var existsCountry = new string[] { "英国", "加拿大", "美国" };
                var whereExpression = Expressionable.Create<Grp_VisaInfo, Grp_DelegationInfo, Grp_CreditCardPayment>()
                    .And(
                        (x, a, b) => x.IsDel == 0 && x.CreateUserId == userId && (b.IsAuditGM == 1 || b.IsAuditGM == 3)
                        && a.IsDel == 0 && a.VisitDate >= StartDateTime && a.VisitDate <= EndDateTime && b.IsDel == 0 && x.IsThird == 0 && x.PassengerType == 974
                        && a.IsSure == 1 && auditState.Contains(b.IsAuditGM) && x.PassengerType == 974
                    );
                var listVisa = _sqlSugar.Queryable<Grp_VisaInfo, Grp_DelegationInfo, Grp_CreditCardPayment>((x, a, b) => new
                    JoinQueryInfos(
                        JoinType.Left, a.Id == x.DIId,
                        JoinType.Left, b.CTable == 80 && b.CId == x.Id)
                    )
                .Where(whereExpression.ToExpression())
                .Select((x, a, b) => new
                    {
                        a.Id,
                        a.TeamName,
                        a.VisitDate,
                        a.TeamLevSId,
                        a.VisitPNumber,
                        a.VisitCountry,
                        x.VisaDescription,
                        x.VisaFreeNumber,
                        x.VisaNumber,
                        x.VisaPrice
                })
                .ToList();

                var groupByid = listVisa.GroupBy(x => x.Id);
                foreach (var visaArr in groupByid)
                {
                    var groupinfo = listVisa.First(x => x.Id == visaArr.Key);
                    var singlePrice = existsCountry.Where(x => groupinfo.VisitCountry.Contains(x)).Count() > 0 ? 50 : 30;
                    int count = 0, money = 0;
                    var arr = new List<string>();
                    foreach (var visa in visaArr)
                    {
                        if (visa.VisaPrice > 0)
                        {
                            count += visa.VisaFreeNumber + visa.VisaNumber;
                            money += singlePrice * (visa.VisaFreeNumber + visa.VisaNumber);
                        }
                        else
                        {
                            count -= visa.VisaFreeNumber + visa.VisaNumber;
                            money -= singlePrice * (visa.VisaFreeNumber + visa.VisaNumber);
                        }
                        string info = $"签证 {visa.VisaDescription}  {(visa.VisaPrice > 0 ? "" : "此笔为退费!")} 人数为{visa.VisaFreeNumber + visa.VisaNumber} 单价为{singlePrice} 金额为{singlePrice * (visa.VisaFreeNumber + visa.VisaNumber)}";
                        arr.Add(info);
                    }
                   
                    string temp  = "本团人数" + groupinfo.VisitPNumber + "，该人员" + user.CnName + "共办理英国、加拿大、美国签证" + (singlePrice == 50 ? count : 0).ToString() + "本护照（包含免签人员），申根国家及其他所有国家" + (singlePrice == 30 ? count : 0).ToString() + "本。" + user.CnName + "提成共" + money.ToString("#0.00") + "元";

                    resultArr.Add(new ComputeRoyaltiesView
                    {
                        TeamName =  groupinfo.TeamName,
                        TeamId = groupinfo.Id,
                        ChiArr = arr,
                        TeamLvStr = teamLv.Find(x => x.Id == groupinfo.TeamLevSId)?.Name,
                        VisitDate =  groupinfo.VisitDate,
                        Temp =  temp,
                        Price = money,
                    });
                }
                    
            }

            if (resultArr.Count > 0)
            {
                jw.Code = 200;
                jw.Msg = "success!";
                decimal total = 0;
                foreach (var item in resultArr)
                {
                    //var number = item.GetType().GetProperty("price")?.GetValue(item)?.ToString();
                    //total += decimal.TryParse(number,out decimal numberInt) ? numberInt : 0.00M;
                    total += item.Price;
                }

                jw.Data = new
                {
                    resultArr,
                    totalPrice = total,
                };
            }
            else
            {
                jw.Msg = "暂无数据!";
            }

            return Ok(jw);
        
        }

        /// <summary>
        /// 导出提成表格
        /// </summary>
        /// <returns></returns>
        [HttpPost]
        public IActionResult ExportRoyaltyExcel(ExportRoyaltyExcelDto dto)
        {
            var jw = JsonView(false);

            var apiResult = ComputeRoyalties(new
                 ComputeRoyaltiesDto
            {
                EndDate = dto.EndDate,
                StartDate = dto.StartDate,
                UserId = dto.UserId,
            });

            var parseApiResult =  ((apiResult as OkObjectResult)?.Value) as OASystem.Domain.ViewModels.JsonView;

            if (parseApiResult != null && parseApiResult.Code == 200)
            {
                var apiArr = (parseApiResult.Data?.GetType().GetProperty("resultArr")?.GetValue(parseApiResult.Data)) as List<ComputeRoyaltiesView>;

                var user = _sqlSugar.Queryable<Sys_Users>().First(x => x.Id == dto.UserId);

                if (apiArr != null && apiArr.Count > 0)
                {
                    Dictionary<string, string> dic = new Dictionary<string, string>();
                    //dic.Add("Name", "人员：" + user.CnName);
                    dic.Add("startTime", dto.StartDate);
                    dic.Add("endTime", dto.EndDate);
                    dic.Add("operName", user.CnName);
                    dic.Add("timeNow", DateTime.Now.ToString("yyyy年MM月dd日"));
                    //dic.Add("DowntimeNow", DateTime.Now.ToString("yyyy年MM月dd日"));
                    dic.Add("totalPrice",( "总金额：" +  parseApiResult.Data?.GetType().GetProperty("totalPrice")?.GetValue(parseApiResult.Data).ObjToDecimal().ToString() ?? "0") +" 元");

                    ArrayList ToTableArr = new ArrayList();
                    int No = 1;
                    //打开excel模板
                    foreach (var item in apiArr)
                    {
                        //载入数据
                        ToTableArr.Add(new
                        {
                            No,
                            item.TeamName,
                            item.VisitDate,
                            item.TeamLvStr,
                            item.Temp,
                            infos =  string.Join("\r\n",item.ChiArr),
                            price = item.Price + "元",
                        });

                        No++;
                    }

                    DataTable TbData =CommonFun.ToDataTableArray(ToTableArr);
                    TbData.TableName = "TbData";

                    WorkbookDesigner designer = new WorkbookDesigner();
                    designer.Workbook = new Workbook(AppSettingsHelper.Get("ExcelBasePath") + "Template/提成结算template.xlsx");

                    foreach (var key in dic.Keys)
                    {
                        designer.SetDataSource(key, dic[key]);
                    }

                    //数据源 
                    designer.SetDataSource(TbData);
                    designer.Process();

                    designer.Workbook.Save(AppSettingsHelper.Get("ExcelBasePath") + "RoyaltyExcel/" + user.CnName + "_提成结算.xlsx" );

                    jw = JsonView(true, "success!", new
                    {
                        Url = AppSettingsHelper.Get("ExcelBaseUrl") + AppSettingsHelper.Get("ExcelFtpPath")+ "RoyaltyExcel/" + user.CnName + "_提成结算.xlsx",
                    });
                }
            }
            else
            {
                jw.Msg = "获取提成信息error " + jw.Msg;
            }

            return Ok(jw);
        }

        /// <summary>
        /// 提成财务确认通知
        /// </summary>
        /// <returns></returns>
        [HttpPost]

        public async Task<IActionResult> RoyaltyConfirmByFin(RoyaltyConfirmDto dto)
        {
            var jw = JsonView(false);

            if (!DateTime.TryParse(dto.StartData, out DateTime StartDateTime))
            {
                jw.Msg = "开始时间格式有误！";
                return Ok(jw);
            }
            if (!DateTime.TryParse(dto.EndData, out DateTime EndDateTime))
            {
                jw.Msg = "结束时间格式有误！";
                return Ok(jw);
            }
            if (DateTime.Compare(StartDateTime, EndDateTime) > 0)
            {
                jw.Msg = "开始时间大于结束时间！";
                return Ok(jw);
            }

            if (dto.ComputeRoyaltiesList.Count != 0)
            {
                var user = _sqlSugar.Queryable<Sys_Users>().First(x => x.Id == dto.UserId);
                if (user == null) { return Ok(JsonView(false, "通知失败! 人员信息有误！")); }

                //保存提成信息   
                //消息通知对应人员(公司 部门 人员 时间段 团组id 是否确定 ) 
                List<Fin_RoyaltyConfirm> entitys = dto.ComputeRoyaltiesList.Select(x => new Fin_RoyaltyConfirm
                {
                    UserId = dto.UserId,
                    ChiArr = JsonConvert.SerializeObject(x.ChiArr),
                    CreateTime = DateTime.Now,
                    CreateUserId = dto.UserId,
                    IsConfirm = 0,
                    TeamId = x.TeamId,
                    TeamLvStr = x.TeamLvStr,
                    Price = x.Price,
                    VisitDate = x.VisitDate,
                    TeamName = x.TeamName,
                    Temp = x.Temp,
                    IsSeed = 0
                }).ToList();

                var expressionSoftDeleteWhere = Expressionable.Create<Fin_RoyaltyConfirm>()
                    .And(it => it.VisitDate >= StartDateTime)
                    .And(it => it.VisitDate <= EndDateTime)
                    .And(it => it.IsDel == 0)
                    .And(it => it.UserId == dto.UserId)
                    .And(it => it.IsSeed == 0)
                    .ToExpression();

                var expressionSelectWhere = Expressionable.Create<Fin_RoyaltyConfirm>()
                    .And(it => it.VisitDate >= StartDateTime)
                    .And(it => it.VisitDate <= EndDateTime)
                    .And(it => it.IsDel == 0)
                    .And(it => it.UserId == dto.UserId)
                    .And(it => it.IsSeed == 1)
                    .ToExpression();

                _sqlSugar.BeginTran();

                try
                {
                    _ = _sqlSugar.Updateable<Fin_RoyaltyConfirm>().SetColumns(it => new Fin_RoyaltyConfirm() { IsDel = 1, DeleteTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm"), DeleteUserId = dto.UserId })
                                                            .Where(expressionSoftDeleteWhere)
                                                            .ExecuteCommand();

                    var confirmYes = _sqlSugar.Queryable<Fin_RoyaltyConfirm>().Where(expressionSelectWhere).Select(x => x.TeamId).ToList();

                    entitys = entitys.Where(x => !confirmYes.Contains(x.TeamId)).ToList();

                    _ = _sqlSugar.Insertable<Fin_RoyaltyConfirm>(entitys).ExecuteCommand();

                    _sqlSugar.CommitTran();

                    //调用通知
                    var title = "提成确认";
                    var center = $@"{dto.StartData}-{dto.EndData}提成财务已确认，请尽快查看!
【{dto.StartData}-{dto.EndData}-{dto.CompanyId}-{dto.DeparId}-{dto.UserId}】";
                    var ids = new List<int> { 235 }; //dto.UserId
                    await GeneralMethod.MessageIssueAndNotification(MessageTypeEnum.GroupBusinessOperations,title, center, ids);

                    jw = JsonView(true, $"人员:{user.CnName} {dto.StartData}-{dto.EndData} 通知成功!");
                }
                catch (Exception ex)
                {
                    _sqlSugar.RollbackTran();
                    jw = JsonView(false, "保存失败!" + ex.Message);
                }
            }
            else
            {
                jw = JsonView(false,"保存失败! 无提成信息");
            }

            return Ok(jw);
        }

        /// <summary>
        /// 查询提成信息
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        public IActionResult QueryRoyaltyInfo(QueryRoyaltyInfoDto dto)
        {
            var jw = JsonView(false);

            if (!DateTime.TryParse(dto.StartDate, out DateTime StartDateTime))
            {
                jw.Msg = "开始时间格式有误！";
                return Ok(jw);
            }
            if (!DateTime.TryParse(dto.EndDate, out DateTime EndDateTime))
            {
                jw.Msg = "结束时间格式有误！";
                return Ok(jw);
            }
            if (DateTime.Compare(StartDateTime, EndDateTime) > 0)
            {
                jw.Msg = "开始时间大于结束时间！";
                return Ok(jw);
            }

            var expressionSelectWhere = Expressionable.Create<Fin_RoyaltyConfirm>()
                    .And(it => it.VisitDate >= StartDateTime)
                    .And(it => it.VisitDate <= EndDateTime)
                    .And(it => it.IsDel == 0)
                    .And(it => it.UserId == dto.UserId)
                    .ToExpression();

            try
            {
                var query = _sqlSugar.Queryable<Fin_RoyaltyConfirm>().Where(expressionSelectWhere).ToList().Select(x => new
                {
                    x.Price,
                    ChiArr = JsonConvert.DeserializeObject<List<string>>(x.ChiArr),
                    x.IsConfirm,
                    x.Id,
                    x.TeamId,
                    x.TeamName,
                    x.TeamLvStr,
                    x.Temp,
                    x.VisitDate,
                    x.IsSeed
                });

                jw = JsonView(true, "获取成功！", query);
            }
            catch (Exception ex)
            {
                jw = JsonView(true, "获取失败！" + ex.Message);
            }

            return Ok(jw);
        }

        /// <summary>
        /// 个人提成确认
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        public IActionResult RoyaltyConfirmByUser(RoyaltyConfirmByUserDto dto)
        {
            var jw = JsonView(false);

            var stateArr = new int[] {0 , 1}; 

            if (!stateArr.Contains(dto.State))
            {
                return Ok(JsonView(false,"状态标识有误!"));
            }

            if (dto.Data != null && dto.Data.Count != 0)
            {
                var expressionUpdateWhere = Expressionable.Create<Fin_RoyaltyConfirm>()
                   .And(it => dto.Data.Contains (it.Id))
                   .And(it => it.IsDel == 0)
                   .ToExpression();

                var count  =  _sqlSugar.Updateable<Fin_RoyaltyConfirm>().SetColumns(x => new Fin_RoyaltyConfirm
                {
                    IsConfirm = dto.State,
                })
                .Where(expressionUpdateWhere).ExecuteCommand();

                jw.Msg = $"{count}个团组确认成功！";
                jw.Code = 200;
            }
            else
            {
                jw.Msg = "Data为空！";
            }
            return Ok(jw);
        }

        /// <summary>
        /// 提成发放
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        public IActionResult RoyaltySeed(RoyaltySeedDto dto)
        {
            var jw = JsonView(false);
            if (dto.Data != null && dto.Data.Count > 0)
            {
                var updateCount = _sqlSugar.Updateable<Fin_RoyaltyConfirm>().SetColumns(x => new Fin_RoyaltyConfirm
                { IsSeed = 1 }).Where(x => dto.Data.Contains(x.Id) && x.IsDel == 0 && x.IsConfirm == 1).ExecuteCommand();
                var info = $"{updateCount}个团组提成发放成功！";
                if (dto.Data.Count > updateCount)
                {
                    info += $"{dto.Data.Count - updateCount}个团组未确认！";
                }
                jw = JsonView(true, info);
            }
            else
            {
                jw.Msg = "发放的数据为空！";
            }
            return Ok(jw);
        }
        #endregion
    }
}