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

using Aspose.Cells;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using OASystem.API.OAMethodLib;
using OASystem.API.OAMethodLib.File;
using OASystem.Domain;
using OASystem.Domain.Dtos.Financial;
using OASystem.Domain.Dtos.Groups;
using OASystem.Domain.Entities.Financial;
using OASystem.Domain.Entities.Groups;
using OASystem.Domain.ViewModels.Financial;
using OASystem.Domain.ViewModels.Groups;
using OASystem.Domain.ViewModels.SmallFun;
using OASystem.Infrastructure.Repositories.Financial;
using OASystem.Infrastructure.Repositories.Groups;
using SqlSugar;
using StackExchange.Redis;
using System.Data;
using static OpenAI.GPT3.ObjectModels.SharedModels.IOpenAiModels;
using System.Web;
using Aspose.Words;
using NPOI.HSSF.Util;
using OASystem.Domain.Entities.Customer;
using System.IO.Compression;
using NPOI.SS.UserModel;
using System.Net.Http;
using Google.Protobuf;
using NPOI.SS.Formula.Functions;
using System.Globalization;
using NPOI.POIFS.Properties;
using SixLabors.ImageSharp.ColorSpaces;
using OASystem.Domain.ViewModels.QiYeWeChat;
using System.Diagnostics;
using System.Security.Cryptography;

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));
            }
            return Ok(JsonView(true));
        }

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

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

        /// <summary>
        /// 日付申请 Del
        /// </summary>
        /// <param name="dto"> 日付申请 删除 dto</param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> PostDelDailyPayment(DelDailyFeePaymentDto dto)
        {
            var result = await _daiRep.Del(dto);

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

        /// <summary>
        /// 日付申请 财务审核
        /// </summary>
        /// <param name="dto"> dto </param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> PostDelDailyPaymentAudit(DP_AuditStatusDto dto)
        {
            var result = await _daiRep.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));
        }
        #endregion

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

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

        #region 团组汇率

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

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


                Stopwatch stopwatch = Stopwatch.StartNew();

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

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

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

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

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

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

                stopwatch.Stop();

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

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

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

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


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

        #endregion

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

        /// <summary>
        /// 对外收款账单 Select数据源(团组名,币种,汇款方式)
        /// 关联已收款项
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> GetForeignReceivablesDataSources()
        {
            try
            {
                Result ffrData = await _ForForeignReceivablesRep.GetDataSource();
                if (ffrData.Code != 0)
                {
                    return Ok(JsonView(false, ffrData.Msg));
                }
                return Ok(JsonView(true, ffrData.Msg, ffrData.Data));
            }
            catch (Exception ex)
            {
                return Ok(JsonView(false, ex.Message));
                throw;
            }
        }

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

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

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

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

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

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

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

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


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

        #region 对外收款账单

        /// <summary>
        /// 对外收款账单 
        /// 数据源
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> PostGroupReceivablesDataSource()
        {
            try
            {
                Result ffrData = await _ForForeignReceivablesRep.PostDataSource();
                if (ffrData.Code != 0)
                {
                    return Ok(JsonView(false, ffrData.Msg));
                }
                return Ok(JsonView(true, ffrData.Msg, ffrData.Data));
            }
            catch (Exception ex)
            {
                return Ok(JsonView(false, ex.Message));
            }
        }

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

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

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


        /// <summary>
        /// 已收账单 
        /// File Downloasd
        /// 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.ToString();
                    }

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

                    decimal sumPrice = 0;
                    //各项费用
                    if (doc.Range.Bookmarks["PayItemContent"] != null)
                    {
                        string items = "";
                        foreach (var fr in _ForeignReceivables)
                        {
                            items += fr.PriceName + "   " + fr.Currency + " " + fr.Price.ToString("#0.00") + " * " + fr.Count + " " + fr.Unit + " * " + fr.Rate + ".................. RMB " + fr.ItemSumPrice.ToString("#0.00") + "\n";
                            sumPrice += fr.ItemSumPrice;
                        }
                        Bookmark mark = doc.Range.Bookmarks["PayItemContent"];
                        mark.Text = items;
                    }
                    //合计
                    if (doc.Range.Bookmarks["Total"] != null)
                    {
                        Bookmark mark = doc.Range.Bookmarks["Total"];
                        mark.Text = sumPrice.ToString("#0.00");
                    }

                    #endregion

                    //文件名
                    string strFileName = _DelegationInfo.TeamName + "-收款账单.docx";

                    doc.Save(AppSettingsHelper.Get("WordBasePath") + "ForeignReceivables/File/" + strFileName);
                    string url = AppSettingsHelper.Get("WordBaseUrl") + "Office/Word/ForeignReceivables/File/" + strFileName;

                    return Ok(JsonView(true, "成功", new { Url = url }));
                }
                else if (dto.FileType == 3) //汇款通知
                {
                    var _EnterExitCosts = _sqlSugar.Queryable<Grp_EnterExitCost>().Where(it => it.IsDel == 0 && it.DiId == dto.DiId).First();
                    var _DayAndCosts = _sqlSugar.Queryable<Grp_DayAndCost>().Where(it => it.IsDel == 0 && it.DiId == dto.DiId && 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("其他城市"))
                            {
                                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,
                                                      Job = dc.Job,
                                                      AirType = tcl.ShippingSpaceTypeId
                                                  })
                                                  .ToList();
                    if (DeleClientList.Count < 1)
                    {
                        return Ok(JsonView(false, "暂无团组成员,请先填写团组成员！！！"));
                    }
                    var _ClientNames = DeleClientList.Select(x => x.Name).ToList();
                    var _GroupClient = DeleClientList.GroupBy(x => x.Company).ToList();


                    Dictionary<string, string> bookmarkArr = null;

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


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

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

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

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

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

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

                            //计算费用总和
                            decimal airPrice = client.AirType == 460 ? _EnterExitCosts.AirJJ : _EnterExitCosts.AirGW;
                            decimal AllPrice = airPrice + _EnterExitCosts.CityTranffic + dac1.Sum(x => x.SubTotal) + dac2.Sum(x => x.SubTotal) +
                                               dac3.Sum(x => x.SubTotal) + dac4.Sum(x => x.SubTotal) + _EnterExitCosts.Visa + _EnterExitCosts.Safe +
                                               _EnterExitCosts.Service;
                            WordAllPrice += AllPrice;

                            UsersTop += firstName + "出访费用为￥" + AllPrice.ToString("#0.00") + "元、";

                            TeableBookmarkArr.Add("jp", (client.AirType == 460 ? _EnterExitCosts.AirJJ : _EnterExitCosts.AirGW).ToString("#0.00") + " 元"); //机票金额
                            TeableBookmarkArr.Add("cs", _EnterExitCosts.CityTranffic.ToString("#0.00") + " 元"); //城市交通费用
                            TeableBookmarkArr.Add("zs", dac1.Sum(x => Convert.ToDecimal(x.SubTotal)).ToString("#0.00") + " 元"); //住宿费
                            string zsinfo = string.Empty;
                            string hsinfo = string.Empty;
                            string gzinfo = string.Empty;
                            List<string> placeArr = new List<string>();

                            Aspose.Words.Tables.Table FirstTable = (Aspose.Words.Tables.Table)doc.GetChild(NodeType.Table, 0, true);
                            Aspose.Words.Tables.Table ChildTable = (Aspose.Words.Tables.Table)FirstTable.GetChild(NodeType.Table, 0, true);
                            Aspose.Words.Tables.Table ChildTable1 = (Aspose.Words.Tables.Table)FirstTable.GetChild(NodeType.Table, 1, true);
                            Aspose.Words.Tables.Table ChildTable2 = (Aspose.Words.Tables.Table)FirstTable.GetChild(NodeType.Table, 2, true);

                            int rowIndex = 0;
                            foreach (var item in dac1)
                            {
                                if (string.IsNullOrWhiteSpace(item.Place))
                                {
                                    continue;
                                }

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

                                    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); //公杂费详情
                            TeableBookmarkArr.Add("qt", (_EnterExitCosts.Visa + _EnterExitCosts.Safe).ToString("#0.00") + " 元");//其他费用
                            TeableBookmarkArr.Add("qtinfo", $"（签证费{_EnterExitCosts.Visa.ToString("#0.00")}元、保险{_EnterExitCosts.Safe.ToString("#0.00")}元等费用）");//其他费用第二列
                            TeableBookmarkArr.Add("fw", _EnterExitCosts.Service.ToString("#0.00") + "元/人");//服务费
                            TeableBookmarkArr.Add("AllPrice", AllPrice.ToString("#0.00") + "元/人");//表格合计费用
                            string airStr = client.AirType == 460 ? "经济舱" : client.AirType == 458 ? "公务舱" : "";
                            TeableBookmarkArr.Add("title", $"费用清单-{airStr}（{firstName}）");
                            foreach (var book in TeableBookmarkArr.Keys)
                            {

                                if (doc.Range.Bookmarks[book] != null)
                                {
                                    Bookmark mark = doc.Range.Bookmarks[book];
                                    mark.Text = TeableBookmarkArr[book];
                                }
                            }
                            if (i != ClientItemList.Count - 1)
                            {
                                builder.PageSetup.Orientation = Aspose.Words.Orientation.Portrait;
                                Aspose.Words.Tables.Table table = (Aspose.Words.Tables.Table)doc.GetChild(NodeType.Table, 0, true);
                                table.ParentNode.InsertAfter(paragraph, table);
                                var CloneTable = (Aspose.Words.Tables.Table)table.Clone(true);
                                table.ParentNode.InsertAfter(CloneTable, paragraph);
                            }

                            TeableBookmarkArr.Clear();
                        }


                        bookmarkArr.Add("VisitPrice", WordAllPrice.ToString());//出访费用总额
                        bookmarkArr.Add("CnAllPrice", WordAllPrice.ConvertCNYUpper());//出访费用总额中文
                        bookmarkArr.Add("namesPrice", UsersTop.TrimEnd('、'));//各人员出访费用  付辰同志出访费用为￥73,604.8元

                        foreach (var book in bookmarkArr.Keys)
                        {

                            if (doc.Range.Bookmarks[book] != null)
                            {
                                Bookmark mark = doc.Range.Bookmarks[book];
                                mark.Text = bookmarkArr[book];
                            }
                        }
                        //MemoryStream outSteam = new MemoryStream();
                        string filsPath = AppSettingsHelper.Get("WordBasePath") + $"ForeignReceivables/File/{ClientItem.Key.Replace("\n","")}.docx";
                        doc.Save(filsPath);
                        filesToZip.Add(filsPath);
                        //streams.Add(ClientItem.Key + ".docx", outSteam.ToArray());
                    }

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

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

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

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

        private void SetCells(Aspose.Words.Tables.Table table, Document doc, int rows, int cells, string val)
        {
            //获取table中的某个单元格，从0开始
            Aspose.Words.Tables.Cell lshCell = table.Rows[rows].Cells[cells];
            //将单元格中的第一个段落移除
            lshCell.FirstParagraph.Remove();
            //新建一个段落
            Paragraph p = new Paragraph(doc);
            var r = new Run(doc, val);
            r.Font.Size = 8;
            //把设置的值赋给之前新建的段落
            p.AppendChild(r);

            //将此段落加到单元格内
            lshCell.AppendChild(p);
        }
        #endregion

        #region 已收款项

        /// <summary>
        /// 已收款项
        /// 查询
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> PostAmountReceived(AmountReceivedDto dto)
        {
            try
            {
                if (dto == null)
                {
                    return Ok(JsonView(false, "参数不能为空!"));
                }

                Result ffrData = await _proceedsReceivedRep.PostAmountReceived(dto.DiId);
                dynamic data = null;
                if (dto.PortType == 1)
                {
                    if (ffrData.Code != 0)
                    {
                        return Ok(JsonView(false, ffrData.Msg));
                    }
                    data = ffrData.Data;
                }
                else if (dto.PortType == 2)
                {
                    if (ffrData.Code != 0)
                    {
                        return Ok(JsonView(false, ffrData.Msg));
                    }
                    data = ffrData.Data;
                }
                else if (dto.PortType == 2)
                {
                    if (ffrData.Code != 0)
                    {
                        return Ok(JsonView(false, ffrData.Msg));
                    }
                    data = ffrData.Data;

                }
                else
                {
                    return Ok(JsonView(false, "请选择正确的端口号!"));
                }
                return Ok(JsonView(true, "操作成功!", data));
            }
            catch (Exception ex)
            {
                return Ok(JsonView(false, ex.Message));
            }
        }

        /// <summary>
        /// 已收款项
        /// Add Or Edit
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> PostAmountReceivedAddOrEdit(AmountReceivedAddOrEditDto dto)
        {
            try
            {
                if (dto == null)
                {
                    return Ok(JsonView(false, "参数不能为空!"));
                }

                Result ffrData = await _proceedsReceivedRep.PostAmountReceivedAddOrEditDto(dto);

                if (dto.PortType == 1)
                {
                    if (ffrData.Code != 0)
                    {
                        return Ok(JsonView(false, ffrData.Msg));
                    }

                }
                else if (dto.PortType == 2)
                {
                    if (ffrData.Code != 0)
                    {
                        return Ok(JsonView(false, ffrData.Msg));
                    }

                }
                else if (dto.PortType == 2)
                {
                    if (ffrData.Code != 0)
                    {
                        return Ok(JsonView(false, ffrData.Msg));
                    }
                }
                else
                {
                    return Ok(JsonView(false, "请选择正确的端口号!"));
                }
                return Ok(JsonView(true, "操作成功!"));
            }
            catch (Exception ex)
            {
                return Ok(JsonView(false, ex.Message));
            }
        }

        /// <summary>
        /// 已收款项
        /// Del
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> PostAmountReceived_Del(AmountReceivedDelDto dto)
        {
            try
            {
                if (dto == null)
                {
                    return Ok(JsonView(false, "参数不能为空!"));
                }

                Result ffrData = await _proceedsReceivedRep.PostAmountReceivedDel(dto);

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

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

        #endregion

        #region 收款退还与其他款项 --> 收款退还

        /// <summary>
        /// 收款退还与其他款项
        /// 查询 根据团组Id
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> PostPaymentRefundAndOtherMoneyItemByDiId(PaymentRefundAndOtherMoneyItemByDiIdDto dto)
        {
            try
            {
                if (dto == null) return Ok(JsonView(false, "参数不能为空!"));

                if (dto.PageId <= 0) return Ok(JsonView(false, "请传入正确的的页面Id!"));

                if (dto.UserId <= 0) return Ok(JsonView(false, "请传入正确的的员工Id!"));

                #region 页面功能权限处理
                PageFunAuthViewBase pageFunAuth = new PageFunAuthViewBase();
                pageFunAuth = await GeneralMethod.PostUserPageFuncDatas(dto.UserId, dto.PageId);

                if (pageFunAuth.CheckAuth == 0)
                {
                    return Ok(JsonView(false, "您没有当前页面查询权限!"));
                }

                #endregion

                Result _result = await _paymentRefundAndOtherMoneyRep._ItemByDiId(dto.DiId);

                if (dto.PortType == 1 || dto.PortType == 2 || dto.PortType == 3)  //1 Web 2 Android 3 Ios
                {
                    if (_result.Code != 0)
                    {
                        return Ok(JsonView(false, _result.Msg));
                    }

                    var data = new
                    {
                        PageFuncAuth = pageFunAuth,
                        Data = _result.Data
                    };

                    return Ok(JsonView(true, "操作成功!", data));
                }
                else
                {
                    return Ok(JsonView(false, "请输入正确的端口号! 1 Web 2 Android 3 Ios;"));
                }
            }
            catch (Exception ex)
            {
                return Ok(JsonView(false, ex.Message));
            }
        }

        /// <summary>
        /// 收款退还与其他款项
        /// 删除 
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> PostPaymentRefundAndOtherMoneyDel(PaymentRefundAndOtherMoneyDelDto dto)
        {
            try
            {
                if (dto == null) return Ok(JsonView(false, "参数不能为空!"));
                if (dto.PageId <= 0) return Ok(JsonView(false, "请传入正确的的页面Id!"));
                if (dto.UserId <= 0) return Ok(JsonView(false, "请传入正确的的员工Id!"));

                PageFunAuthViewBase pageFunAuth = new PageFunAuthViewBase();

                #region 页面功能权限处理

                pageFunAuth = await GeneralMethod.PostUserPageFuncDatas(dto.UserId, dto.PageId);

                #endregion

                if (pageFunAuth.DeleteAuth == 0)
                {
                    return Ok(JsonView(false, "您没有当前页面删除权限!"));
                }


                Result _result = await _paymentRefundAndOtherMoneyRep._Del(dto);

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

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

        /// <summary>
        /// 收款退还与其他款项
        /// Info Data Source
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> PostPaymentRefundAndOtherMoneyInfoDataSource(PortDtoBase dto)
        {
            try
            {
                Result _result = await _paymentRefundAndOtherMoneyRep._InfoDataSource(dto);

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

                return Ok(JsonView(true, "查询成功!", _result.Data));
            }
            catch (Exception ex)
            {
                return Ok(JsonView(false, ex.Message));
            }
        }

        /// <summary>
        /// 收款退还与其他款项
        /// Info
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> PostPaymentRefundAndOtherMoneyInfo(PaymentRefundAndOtherMoneyInfoDto dto)
        {
            try
            {
                if (dto == null)
                {
                    return Ok(JsonView(false, "参数不能为空!"));
                }

                Result _result = await _paymentRefundAndOtherMoneyRep._Info(dto);

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

                return Ok(JsonView(true, "查询成功!", _result.Data));
            }
            catch (Exception ex)
            {
                return Ok(JsonView(false, ex.Message));
            }
        }

        /// <summary>
        /// 收款退还与其他款项 --> 收款退还(只保留人名币)
        /// 操作(Add Or Edit)
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> PostPaymentRefundAndOtherMoneyAddOrEdit(PaymentRefundAndOtherMoneyAddOrEditDto dto)
        {
            try
            {
                if (dto == null) return Ok(JsonView(false, "参数不能为空!"));
                if (dto.PageId <= 0) return Ok(JsonView(false, "请传入正确的的页面Id!"));
                if (dto.UserId <= 0) return Ok(JsonView(false, "请传入正确的的员工Id!"));

                #region 页面功能权限处理
                PageFunAuthViewBase pageFunAuth = new PageFunAuthViewBase();
                pageFunAuth = await GeneralMethod.PostUserPageFuncDatas(dto.UserId, dto.PageId);

                #endregion

                if (dto.Status == 1) //add
                {
                    if (pageFunAuth.AddAuth == 0)
                    {
                        return Ok(JsonView(false, "您没有当前页面添加权限!"));
                    }
                }
                else if (dto.Status == 2) //edit
                {
                    if (pageFunAuth.EditAuth == 0)
                    {
                        return Ok(JsonView(false, "您没有当前页面编辑权限!"));
                    }
                }
                else
                {
                    return Ok(JsonView(false, "请输入正确的操作状态! 1 添加 2 修改!"));
                }

                Result _result = await _paymentRefundAndOtherMoneyRep._AddOrEdit(dto);

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

                return Ok(JsonView(true, "操作成功!"));
            }
            catch (Exception ex)
            {
                return Ok(JsonView(false, ex.Message));
            }
        }
        #endregion

        #region 应收报表

        /// <summary>
        /// 应收报表
        /// 查询 根据日期范围
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> PostSyntheticalReceivableByDateRange(PostSyntheticalReceivableByDateRangeDto dto)
        {

            string sqlWhere = " Where di.IsDel=0 ";

            #region 验证
            if (string.IsNullOrEmpty(dto.beginDt) && string.IsNullOrEmpty(dto.endDt))
            {
                return Ok(JsonView(false, "日期参数至少填写一个!"));
            }

            if (!string.IsNullOrEmpty(dto.beginDt))
            {
                if (Regex.Match(dto.beginDt, @"^\d{4}-\d{2}-\d{2}$").Value.Length < 1)
                {
                    return Ok(JsonView(false, "日期参数格式错误，应为yyyy-MM-dd!"));
                }
                else
                {
                    sqlWhere += string.Format(@" And di.VisitDate >= '{0} 00:00:00' ", dto.beginDt);
                }
            }

            if (!string.IsNullOrEmpty(dto.endDt))
            {
                if (Regex.Match(dto.endDt, @"^\d{4}-\d{2}-\d{2}$").Value.Length < 1)
                {
                    return Ok(JsonView(false, "日期参数格式错误，应为yyyy-MM-dd!"));
                }
                else
                {
                    sqlWhere += string.Format(@" And di.VisitDate <= '{0} 23:59:59' ", dto.endDt);
                }
            }


            #endregion

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

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


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

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


                    item_rst.No = rowNumber;
                    rowNumber++;

                    int diId = item_rst.diid;
                    decimal sum_fr = 0M;
                    decimal sum_pr = 0M;
                    string str_client = string.Empty;
                    decimal sum_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));

                }

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

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

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

        }

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

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

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

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

            return _companyInfo;
        }

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

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

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

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

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

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

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

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

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

                    string orbitalPrivateTransfer = payInfo.OrbitalPrivateTransfer == 0 ? "公转" : payInfo.OrbitalPrivateTransfer == 1 ? "私转" : "-";
                    switch (payInfo.CTable)
                    {
                        case 76:  //76	酒店预订
                            priName = hotelDatas.Find(it => payInfo.DIId == it.DiId && payInfo.CId == it.Id)?.HotelName ?? "";
                            break;
                        case 79:  //79	车/导游地接
                            var opData = opDatas.Find(it => payInfo.DIId == it.DiId && payInfo.CId == it.Id);
                            if (opData != null)
                            {
                                if (payInfo.OrbitalPrivateTransfer == 0) //公转
                                {
                                    priName = $"【{orbitalPrivateTransfer}】【导游： {opData.ServiceGuide} 】{opData.Area}";
                                }
                                else if (payInfo.OrbitalPrivateTransfer == 1) //私转
                                {
                                    priName = $"【{orbitalPrivateTransfer}】【导游：{opData.ServiceGuide}】{opData.Area}";
                                }
                            }
                            break;
                        case 80:  // 80   签证
                            priName = visaDatas.Find(it => payInfo.DIId == it.DIId && payInfo.CId == it.Id)?.VisaClient ?? "-";
                            break;
                        case 81:  // 81	邀请/公务活动
                            priName = ioaDatas.Find(it => payInfo.DIId == it.DiId && payInfo.CId == it.Id)?.Inviter ?? "-";
                            break;
                        case 82:  // 82	团组客户保险
                            priName = insureDatas.Find(it => payInfo.DIId == it.DiId && payInfo.CId == it.Id)?.ClientName ?? "-";
                            break;
                        case 85:  // 85	机票预订
                            string flightsCode = airDatas.Find(it => payInfo.DIId == it.DIId && payInfo.CId == it.Id)?.FlightsCode ?? "-";
                            string airPayType = setDatas.Find(it => it.Id == payInfo.PayDId)?.Name ?? "-";
                            priName = $"{flightsCode}【{airPayType}】";
                            break;
                        case 98:  //  98	其他款项
                            priName = otherMoneyDatas.Find(it => payInfo.DIId == it.Diid && payInfo.CId == it.Id)?.PriceName ?? "-";
                            break;
                        case 285:  //  285 收款退还
                            priName = refundPaymentDatas.Find(it => payInfo.DIId == it.DiId && payInfo.CId == it.Id)?.PriceName ?? "-";
                            break;
                        case 1015:  //  1015	超支费用
                            priName = ExtraCostDatas.Find(it => payInfo.DIId == it.DiId && payInfo.CId == it.Id)?.PriceName ?? "-";
                            break;
                        default:
                            priName = "";
                            break;
                    }

                    bool status1 = false;
                    if (_groupIds != null)
                    {
                        status1 = _groupIds.Contains(payInfo.Id);
                    }


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

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

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

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

            _DailyFeePaymentResult.dataList = dataList;
            return _DailyFeePaymentResult;
        }

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

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

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

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

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

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

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

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

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

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

                string feeContentSql = string.Format(@"Select * From Fin_DailyFeePaymentContent 
                                                        Where IsDel=0 And DFPId = {0} ", item.Id);
                item.childList = _sqlSugar.SqlQueryable<Fin_DailyFeePaymentContentInfolView>(feeContentSql).ToList();
                int rowNumber = 1;
                foreach (var subItem in item.childList)
                {
                    string remaksDescription = $"{rowNumber}、【{item.priceTypeStr}】{item.Instructions}({subItem.PriceName}) CNY：{subItem.ItemTotal.ToString("#0.00")}(单价：{subItem.Price.ToString("#0.00")} * {subItem.Quantity})";
                    subItem.RemaksDescription = remaksDescription;
                    string excelRemaksDescription = $"【{item.priceTypeStr}】{item.Instructions}({subItem.PriceName}) CNY：{subItem.ItemTotal.ToString("#0.00")}(单价：{subItem.Price.ToString("#0.00")} * {subItem.Quantity})【申请人：{item.CreateUser}  申请时间：{item.CreateTime.ToString("yyyy-MM-dd HH:mm:ss")}】";
                    subItem.ExcelRemaksDescription = excelRemaksDescription;
                    rowNumber++;
                }
            }

            decimal total_gz = DailyFeePaymentData.Where(s => s.transferParentId == 62).Sum(d => d.SumPrice ?? 0M);
            decimal total_sz = DailyFeePaymentData.Where(s => s.transferParentId == 63).Sum(d => d.SumPrice ?? 0M);

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

            return result;
        }

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

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

            #endregion

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

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

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


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

            if (dto.UserId < 1)
            {
                return Ok(JsonView(false, "请传入有效的UserId参数!"));
            }

            if (dto.Id < 1)
            {
                return Ok(JsonView(false, "请传入有效的Id参数!"));
            }

            if (dto.Rate <= 0)
            {
                return Ok(JsonView(false, "请传入有效的Rate参数!"));
            }
            #endregion
            try
            {
                var status = _sqlSugar.Updateable<Grp_CreditCardPayment>()
                                      .SetColumns(it => it.DayRate == dto.Rate)
                                      .Where(it => it.Id == dto.Id)
                                      .ExecuteCommand();
                if (status > 0)
                {
                    PaymentRequestCheckedView checkedView = new PaymentRequestCheckedView();
                    var checkedStr = await RedisRepository.RedisFactory.CreateRedisRepository().StringGetAsync<string>("paymentRequestCheckedData");
                    if (checkedStr != null)
                    {
                        checkedView = JsonConvert.DeserializeObject<PaymentRequestCheckedView>(checkedStr.ToString());
                    }

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

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

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

                return Ok(JsonView(false, "该项汇率修改失败!"));
            }
            catch (Exception ex)
            {
                return Ok(JsonView(false, ex.Message));
            }
        }

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

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

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

            try
            {
                List<int> groupIds = new List<int>();
                List<int> dailyPaymentIds = new List<int>();

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

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

                bool changeStatus = false;
                _sqlSugar.BeginTran();

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

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

                if (changeStatus)
                {
                    _sqlSugar.CommitTran();
                    return Ok(JsonView(true, "操作成功!"));
                }
                _sqlSugar.RollbackTran();
                return Ok(JsonView(false, "付款状态修改失败!"));
            }
            catch (Exception ex)
            {
                _sqlSugar.RollbackTran();
                return Ok(JsonView(false, ex.Message));
            }
        }

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

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

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

            PageFunAuthViewBase pageFunAuthView = new PageFunAuthViewBase();

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

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

            #endregion

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

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

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

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

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

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

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

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

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

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

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

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

                #region 数据处理

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

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

                        groupGZSubStr += $"\t";
                    }

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

                }

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

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

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


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

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

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

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

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

        #endregion

        #region 超支费用

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

            #endregion

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

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

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

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


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

        private 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,
												c.CTDId,f.PriceType,f.PriceDetailType,f.Coefficient,f.Remark,f.PriceCount,f.PriceDt													
                                                From Fin_GroupExtraCost f
                                                Inner Join Grp_CreditCardPayment c On f.Id = c.CId
                                                Left Join Sys_Users u On f.CreateUserId = u.Id
                                                Where f.IsDel=0 And c.CTable = 1015 
												And f.Id = {0} ", dto.Id);
            Fin_GroupExtraCostDetailView detailView = await _sqlSugar.SqlQueryable<Fin_GroupExtraCostDetailView>(sql).FirstAsync();
            if (detailView == null)
            {
                return Ok(JsonView(false, "查询失败"));
            }

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

        /// <summary>
        /// 超支费用
        /// 列表查询
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> PostGroupExtraCost_Search(Fin_GroupExtraCostDto_Search dto)
        {
            string sqlWhere = string.Format(@" And f.DiId = {0} ", dto.diId);
            int startIndex = (dto.PageIndex - 1) * dto.PageSize + 1;
            int endIndex = startIndex + dto.PageSize - 1;
            string sql_data = string.Format(@"Select * From (	
                                                Select row_number() over (order by f.Id Desc) as RowNumber,f.Id,f.DiId,
												f.PriceName,f.PriceType,f.PriceDetailType,CAST(f.Price as varchar)+' '+s.[Name] as PriceStr,f.PriceCount,CAST(f.PriceSum as varchar)+' '+s.[Name] as PriceSumStr,
                                                f.CreateUserId,f.PriceDt,c.IsAuditGM												
                                                From Fin_GroupExtraCost f
                                                Inner Join Grp_CreditCardPayment c On f.Id = c.CId
                                                Inner Join Sys_SetData s On f.PriceCurrency = s.Id
                                                Left Join Sys_Users u On f.CreateUserId = u.Id
                                                Where f.IsDel=0 And c.CTable = 1015 {0}
                                                ) temp Where RowNumber Between {1} and {2}", sqlWhere, startIndex, endIndex);
            string sql_count = string.Format(@"Select Count(1) as DataCount From (	
                                                Select row_number() over (order by f.Id Desc) as RowNumber,f.Id,f.DiId,
												f.PriceName,f.PriceType,f.Price,f.FilePath,f.CreateUserId,f.CreateTime,c.IsAuditGM
                                                From Fin_GroupExtraCost f
                                                Inner Join Grp_CreditCardPayment c On f.Id = c.CId
                                                Inner Join Sys_SetData s On f.PriceCurrency = s.Id
                                                Left Join Sys_Users u On f.CreateUserId = u.Id
                                                Where f.IsDel=0 And c.CTable = 1015 {0}
                                                ) temp ", sqlWhere);
            if (dto.PortType == 1 || dto.PortType == 2 || dto.PortType == 3) //web
            {
                //Fin_DailyFeePaymentPageCount
                var count = await _sqlSugar.SqlQueryable<Fin_GroupExtraCostViewDataCount>(sql_count).FirstAsync();
                List<Fin_GroupExtraCostView> dataList = await _sqlSugar.SqlQueryable<Fin_GroupExtraCostView>(sql_data).ToListAsync();

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

                foreach (var item in dataList)
                {
                    DateTime dtTemp_PriceDt;
                    bool b_ct = DateTime.TryParse(item.PriceDt, out dtTemp_PriceDt);
                    if (b_ct)
                    {
                        item.PriceDt = dtTemp_PriceDt.ToString("yyyy-MM-dd");
                    }



                    //费用类型
                    if (dic_setData.ContainsKey(item.PriceType))
                    {
                        item.PriceTypeStr = dic_setData[item.PriceType];
                    }
                    else
                    {
                        Sys_SetData sd_priceTypeDetail = _daiRep.Query<Sys_SetData>(s => s.Id == item.PriceType).First();
                        if (sd_priceTypeDetail != null)
                        {
                            string tempName = sd_priceTypeDetail.Name.Replace("n", "");
                            item.PriceTypeStr = tempName;
                            dic_setData.Add(item.PriceType, tempName);
                        }
                    }

                    if (item.PriceDetailType > 0)
                    {
                        if (dic_setData.ContainsKey(item.PriceDetailType))
                        {
                            item.PriceTypeStr = item.PriceTypeStr + " - " + dic_setData[item.PriceDetailType];
                        }
                        else
                        {
                            Sys_SetData sd_priceTypeDetail = _daiRep.Query<Sys_SetData>(s => s.Id == item.PriceDetailType).First();
                            if (sd_priceTypeDetail != null)
                            {
                                string tempName = sd_priceTypeDetail.Name.Replace("n", "");
                                item.PriceTypeStr = item.PriceTypeStr + " - " + tempName;
                                dic_setData.Add(item.PriceDetailType, tempName);
                            }
                        }
                    }

                    //系统用户
                    if (dic_user.ContainsKey(item.CreateUserId))
                    {
                        item.CreateUserIdStr = dic_user[item.CreateUserId];
                    }
                    else
                    {
                        Sys_Users users = _daiRep.Query<Sys_Users>(s => s.Id == item.CreateUserId).First();
                        if (users != null)
                        {
                            item.CreateUserIdStr = users.CnName;
                            dic_user.Add(item.CreateUserId, users.CnName);
                        }
                    }

                    switch (item.IsAuditGM)
                    {
                        case 0: item.IsAuditGMStr = "未审核"; break;
                        case 1: item.IsAuditGMStr = "已通过"; break;
                        case 2: item.IsAuditGMStr = "未通过"; break;
                        default: item.IsAuditGMStr = "未知状态"; break;
                    }
                }


                var result = new ListViewBase<Fin_GroupExtraCostView>
                {
                    CurrPageIndex = dto.PageIndex,
                    CurrPageSize = dto.PageSize,
                    DataCount = count.DataCount,
                    DataList = dataList
                };

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

            }


            return Ok(JsonView(false, "查询失败"));
        }

        /// <summary>
        /// 超支费用
        /// 数据集合配置
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> PostGroupExtraCost_ListDataInit(Fin_GroupExtraCostDto_DataListInit dto)
        {
            //支付方式
            List<Sys_SetData> Payment = _sqlSugar.Queryable<Sys_SetData>().Where(a => a.STid == 14 && a.IsDel == 0).ToList();
            List<SetDataInfoView> _Payment = _mapper.Map<List<SetDataInfoView>>(Payment);

            //信用卡类型
            List<Sys_SetData> Card = _sqlSugar.Queryable<Sys_SetData>().Where(a => a.STid == 15 && a.IsDel == 0).ToList();
            List<SetDataInfoView> _Card = _mapper.Map<List<SetDataInfoView>>(Card);

            //超支费用类型
            List<Sys_SetData> PriceType = _sqlSugar.Queryable<Sys_SetData>().Where(a => a.STid == 79 && a.IsDel == 0).ToList();
            List<SetDataInfoView> _PriceType = _mapper.Map<List<SetDataInfoView>>(PriceType);

            //超支费用详细类型
            List<Sys_SetData> PriceDetailType = _sqlSugar.Queryable<Sys_SetData>().Where(a => a.STid == 80 && a.IsDel == 0).ToList();
            PriceDetailType.ForEach(a => { a.Name = a.Name.Replace("n", ""); });
            List<SetDataInfoView> _PriceDetailType = _mapper.Map<List<SetDataInfoView>>(PriceDetailType);

            var data = new
            {
                Payment = _Payment,
                Card = _Card,
                PriceType = _PriceType,
                PriceDetailType = _PriceDetailType
            };


            return Ok(JsonView(true, "", data));
        }

        /// <summary>
        /// 超支费用
        /// 导出团组超支费用Excel
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> PostGroupExtraCost_OutputExcel(Fin_GroupExtraCostExcelDto dto)
        {
            string sqlGroup = string.Format(@" Select * From Grp_DelegationInfo WIth(Nolock) where Id = {0} ", dto.diId);
            Grp_DelegationInfo grp_DelegationInfo = await _sqlSugar.SqlQueryable<Grp_DelegationInfo>(sqlGroup).FirstAsync();
            if (grp_DelegationInfo == null)
            {
                return Ok(JsonView(false, "导出失败，未查询到团组"));
            }

            Workbook workbook = new Workbook();
            Worksheet sheet = workbook.Worksheets[0];
            Cells cells = sheet.Cells;

            //sheet.Protect(Aspose.Cells.ProtectionType.All, "123123", "");//保护工作表
            //sheet.Protection.IsSelectingLockedCellsAllowed = true;//设置只能选择解锁单元格
            //sheet.Protection.IsFormattingColumnsAllowed = true;//设置可以调整列
            //sheet.Protection.IsFormattingRowsAllowed = true;//设置可以调整行

            #region 数据源

            string sqlData = string.Format(@" Select f.PriceType,REPLACE(s2.[Name],'超支费用','') as PriceTypeStr,'('+REPLACE(s3.[Name],'n','')+')' as PriceDetailTypeStr, PriceDt,PriceName,
Price,PriceCount,s.[Name] as Currency,PriceSum,f.Remark
From Fin_GroupExtraCost as f With(Nolock)
Inner Join Grp_CreditCardPayment as c With(Nolock) On f.Id = c.CId
Inner Join Sys_SetData as s With(Nolock) On f.PriceCurrency = s.Id
Inner Join Sys_SetData as s2 With(Nolock) On f.PriceType = s2.Id
Inner Join Sys_SetData as s3 With(Nolock) On f.PriceDetailType = s3.Id
Where f.DiId = {0} And f.IsDel=0 And c.CTable=1015
Order by PriceType ASC,PriceDt ASC ", dto.diId);

            string sqlDataCount = string.Format(@" Select f.PriceType,COUNT(f.PriceType) as DataCount
From Fin_GroupExtraCost as f With(Nolock)
Inner Join Grp_CreditCardPayment as c With(Nolock) On f.Id = c.CId
Where f.DiId = {0} And f.IsDel=0 And c.CTable=1015
Group by PriceType ", dto.diId);



            List<Fin_GroupExtraCost_ExcelView> dataList = await _sqlSugar.SqlQueryable<Fin_GroupExtraCost_ExcelView>(sqlData).ToListAsync();
            List<Fin_GroupExtraCost_ExcelCountView> countList = await _sqlSugar.SqlQueryable<Fin_GroupExtraCost_ExcelCountView>(sqlDataCount).ToListAsync();

            if (dataList.Count < 1 || countList.Count < 1)
            {
                return Ok(JsonView(false, "导出失败，未查询到数据"));
            }

            #endregion

            #region 标题


            string cellValue_Header = grp_DelegationInfo.TeamName;

            //Aspose.Cells.Style style1 = workbook.Styles[workbook.Styles.Add()];//新增样式
            Aspose.Cells.Style style_Header = workbook.CreateStyle();
            style_Header.HorizontalAlignment = TextAlignmentType.Center;//文字居中
            style_Header.VerticalAlignment = TextAlignmentType.Center;
            style_Header.Font.Name = "微软雅黑";//文字字体
            style_Header.Font.Size = 18;//文字大小
            style_Header.IsLocked = false;//单元格解锁
            style_Header.Font.IsBold = false;//粗体
            style_Header.Font.Color = Color.FromArgb(255, 0, 0);
            //style1.ForegroundColor = Color.FromArgb(0x99, 0xcc, 0xff);//设置背景色
            //style1.Pattern = BackgroundType.Solid; //设置背景样式
            //style1.IsTextWrapped = true;//单元格内容自动换行
            style_Header.Borders[Aspose.Cells.BorderType.LeftBorder].LineStyle = CellBorderType.Thin; //应用边界线 左边界线
            style_Header.Borders[Aspose.Cells.BorderType.RightBorder].LineStyle = CellBorderType.Thin; //应用边界线 右边界线
            style_Header.Borders[Aspose.Cells.BorderType.TopBorder].LineStyle = CellBorderType.Thin; //应用边界线 上边界线
            style_Header.Borders[Aspose.Cells.BorderType.BottomBorder].LineStyle = CellBorderType.Thin; //应用边界线 下边界线

            cells.Merge(1, 1, 1, 10);
            Aspose.Cells.Range range_header = cells.CreateRange(1, 1, 1, 10);
            range_header.PutValue(cellValue_Header, false, false);
            range_header.SetStyle(style_Header);
            cells.SetRowHeight(1, 35);
            #endregion

            #region 列名

            Aspose.Cells.Style style_colName = workbook.CreateStyle();
            style_colName.Name = "colName";
            style_colName.HorizontalAlignment = TextAlignmentType.Center;//文字居中
            style_colName.VerticalAlignment = TextAlignmentType.Center;
            style_colName.Font.Name = "微软雅黑";//文字字体
            style_colName.Font.Size = 12;//文字大小
            style_colName.IsLocked = false;//单元格解锁
            style_colName.Font.IsBold = true;//粗体
            style_colName.Font.Color = Color.FromArgb(0, 0, 0);
            style_colName.Borders[Aspose.Cells.BorderType.LeftBorder].LineStyle = CellBorderType.Thin; //应用边界线 左边界线
            style_colName.Borders[Aspose.Cells.BorderType.RightBorder].LineStyle = CellBorderType.Thin; //应用边界线 右边界线
            style_colName.Borders[Aspose.Cells.BorderType.TopBorder].LineStyle = CellBorderType.Thin; //应用边界线 上边界线
            style_colName.Borders[Aspose.Cells.BorderType.BottomBorder].LineStyle = CellBorderType.Thin; //应用边界线 下边界线

            List<Fin_GroupExtraCostExcelColumnSetting> colNameSettingList = new List<Fin_GroupExtraCostExcelColumnSetting>()
            {
                new Fin_GroupExtraCostExcelColumnSetting(){ columnIndex = 1, columnName="类型", columnWidth= 25},
                new Fin_GroupExtraCostExcelColumnSetting(){ columnIndex = 2, columnName="时间", columnWidth= 16},
                new Fin_GroupExtraCostExcelColumnSetting(){ columnIndex = 3, columnName="内容", columnWidth= 35},
                new Fin_GroupExtraCostExcelColumnSetting(){ columnIndex = 4, columnName="单价", columnWidth= 12},
                new Fin_GroupExtraCostExcelColumnSetting(){ columnIndex = 5, columnName="数量", columnWidth= 12},
                new Fin_GroupExtraCostExcelColumnSetting(){ columnIndex = 6, columnName="货币", columnWidth= 12},
                new Fin_GroupExtraCostExcelColumnSetting(){ columnIndex = 7, columnName="费用", columnWidth= 12},
                new Fin_GroupExtraCostExcelColumnSetting(){ columnIndex = 8, columnName="汇率", columnWidth= 12},
                new Fin_GroupExtraCostExcelColumnSetting(){ columnIndex = 9, columnName="人民币", columnWidth= 12},
                new Fin_GroupExtraCostExcelColumnSetting(){ columnIndex = 10, columnName="备注信息", columnWidth= 24}
            };

            foreach (var col in colNameSettingList)
            {
                cells[2, col.columnIndex].PutValue(col.columnName);
                cells[2, col.columnIndex].SetStyle(style_colName);
                cells.SetColumnWidth(col.columnIndex, col.columnWidth);
            }
            cells.SetRowHeight(2, 25);
            #endregion

            #region 数据填充
            Aspose.Cells.Style style_dataCol = workbook.GetNamedStyle("colName");
            style_dataCol.Font.IsBold = false;
            style_dataCol.Name = "dataCol";

            Aspose.Cells.Style style_dataBlue = workbook.CreateStyle();
            style_dataBlue.HorizontalAlignment = TextAlignmentType.Center;//文字居中
            style_dataBlue.VerticalAlignment = TextAlignmentType.Center;
            style_dataBlue.Font.Name = "微软雅黑";//文字字体
            style_dataBlue.Font.Size = 12;//文字大小
            style_dataBlue.IsLocked = false;//单元格解锁
            style_dataBlue.Font.IsBold = false;//粗体
            style_dataBlue.ForegroundColor = Color.FromArgb(189, 215, 238);
            style_dataBlue.Pattern = BackgroundType.Solid;
            style_dataBlue.Font.Color = Color.FromArgb(0, 0, 0);
            style_dataBlue.Borders[Aspose.Cells.BorderType.LeftBorder].LineStyle = CellBorderType.Thin; //应用边界线 左边界线
            style_dataBlue.Borders[Aspose.Cells.BorderType.RightBorder].LineStyle = CellBorderType.Thin; //应用边界线 右边界线
            style_dataBlue.Borders[Aspose.Cells.BorderType.TopBorder].LineStyle = CellBorderType.Thin; //应用边界线 上边界线
            style_dataBlue.Borders[Aspose.Cells.BorderType.BottomBorder].LineStyle = CellBorderType.Thin; //应用边界线 下边界线

            Aspose.Cells.Style style_dataYellow = workbook.CreateStyle();
            style_dataYellow.HorizontalAlignment = TextAlignmentType.Center;//文字居中
            style_dataYellow.VerticalAlignment = TextAlignmentType.Center;
            style_dataYellow.Font.Name = "微软雅黑";//文字字体
            style_dataYellow.Font.Size = 12;//文字大小
            style_dataYellow.IsLocked = false;//单元格解锁
            style_dataYellow.Font.IsBold = false;//粗体
            style_dataYellow.ForegroundColor = Color.FromArgb(255, 242, 204);
            style_dataYellow.Pattern = BackgroundType.Solid;
            style_dataYellow.Font.Color = Color.FromArgb(0, 0, 0);
            style_dataYellow.Borders[Aspose.Cells.BorderType.LeftBorder].LineStyle = CellBorderType.Thin; //应用边界线 左边界线
            style_dataYellow.Borders[Aspose.Cells.BorderType.RightBorder].LineStyle = CellBorderType.Thin; //应用边界线 右边界线
            style_dataYellow.Borders[Aspose.Cells.BorderType.TopBorder].LineStyle = CellBorderType.Thin; //应用边界线 上边界线
            style_dataYellow.Borders[Aspose.Cells.BorderType.BottomBorder].LineStyle = CellBorderType.Thin; //应用边界线 下边界线




            int rowIndex = 4;
            foreach (var d in dataList)
            {


                //内容
                string typeStr = d.PriceDetailTypeStr + d.PriceName;
                cells["B" + rowIndex.ToString()].PutValue(d.PriceTypeStr);
                cells["C" + rowIndex.ToString()].PutValue(d.PriceDtStr);
                cells["D" + rowIndex.ToString()].PutValue(typeStr);
                cells["E" + rowIndex.ToString()].PutValue(d.Price.ToString("#0.00"));
                cells["F" + rowIndex.ToString()].PutValue(d.PriceCount);
                cells["G" + rowIndex.ToString()].PutValue(d.Currency);
                cells["H" + rowIndex.ToString()].PutValue(d.PriceSum.ToString("#0.00"));
                cells["K" + rowIndex.ToString()].PutValue(d.Remark);

                //样式
                cells["B" + rowIndex.ToString()].SetStyle(style_dataCol);
                cells["C" + rowIndex.ToString()].SetStyle(style_dataCol);
                cells["D" + rowIndex.ToString()].SetStyle(style_dataCol);
                cells["E" + rowIndex.ToString()].SetStyle(style_dataBlue);
                cells["F" + rowIndex.ToString()].SetStyle(style_dataBlue);
                cells["G" + rowIndex.ToString()].SetStyle(style_dataBlue);
                cells["H" + rowIndex.ToString()].SetStyle(style_dataBlue);
                cells["I" + rowIndex.ToString()].SetStyle(style_dataBlue);
                cells["J" + rowIndex.ToString()].SetStyle(style_dataBlue);
                cells["K" + rowIndex.ToString()].SetStyle(style_dataYellow);

                //公式
                cells["H" + rowIndex.ToString()].Formula = string.Format(@"E{0}*F{0}", rowIndex);
                cells["J" + rowIndex.ToString()].Formula = string.Format(@"H{0}*I{0}", rowIndex);

                cells.SetRowHeight(rowIndex - 1, 25);
                rowIndex++;
            }

            cells["B" + rowIndex.ToString()].SetStyle(style_dataCol);
            cells["C" + rowIndex.ToString()].SetStyle(style_dataCol);
            cells["D" + rowIndex.ToString()].SetStyle(style_dataCol);
            cells["E" + rowIndex.ToString()].SetStyle(style_dataBlue);
            cells["F" + rowIndex.ToString()].SetStyle(style_dataBlue);
            cells["G" + rowIndex.ToString()].SetStyle(style_dataBlue);
            cells["H" + rowIndex.ToString()].SetStyle(style_dataBlue);
            cells["I" + rowIndex.ToString()].SetStyle(style_dataBlue);
            cells["J" + rowIndex.ToString()].SetStyle(style_dataBlue);
            cells["K" + rowIndex.ToString()].SetStyle(style_dataYellow);
            rowIndex++;
            cells["B" + rowIndex.ToString()].SetStyle(style_dataCol);
            cells["C" + rowIndex.ToString()].SetStyle(style_dataCol);
            cells["D" + rowIndex.ToString()].SetStyle(style_dataCol);
            cells["E" + rowIndex.ToString()].SetStyle(style_dataBlue);
            cells["F" + rowIndex.ToString()].SetStyle(style_dataBlue);
            cells["G" + rowIndex.ToString()].SetStyle(style_dataBlue);
            cells["H" + rowIndex.ToString()].SetStyle(style_dataBlue);
            cells["I" + rowIndex.ToString()].SetStyle(style_dataBlue);
            cells["J" + rowIndex.ToString()].SetStyle(style_dataBlue);
            cells["K" + rowIndex.ToString()].SetStyle(style_dataYellow);

            rowIndex = 3;
            int tempPriceType = 0;
            foreach (var c in dataList)
            {
                if (tempPriceType == c.PriceType)
                {
                    continue;
                }
                tempPriceType = c.PriceType;
                int _rowCount = countList.First(s => s.PriceType == tempPriceType).DataCount;
                cells.Merge(rowIndex, 1, _rowCount, 1);
                Aspose.Cells.Range tempRange = cells.CreateRange(rowIndex, 1, _rowCount, 1);
                rowIndex += _rowCount;
            }

            rowIndex = 4 + dataList.Count;
            cells["D" + rowIndex.ToString()].PutValue("合计");
            cells["J" + rowIndex.ToString()].Formula = string.Format(@"SUM(J4,J{0})", rowIndex - 1);
            cells.SetRowHeight(rowIndex - 1, 25);

            rowIndex++;
            cells["D" + rowIndex.ToString()].PutValue("服务费10%开票税金8%");
            cells["J" + rowIndex.ToString()].Formula = string.Format(@"J{0}*1.1*1.08", rowIndex - 1);
            cells.SetRowHeight(rowIndex - 1, 25);
            #endregion

            #region IO

            System.IO.MemoryStream ms = workbook.SaveToStream();//生成数据流
            string fileName = ("GroupExtraCost/超支费用(" + cellValue_Header + ").xlsx");
            byte[] bt = ms.ToArray();
            workbook.Save(AppSettingsHelper.Get("ExcelBasePath") + fileName);
            #endregion

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


        #endregion
    }
}