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;

namespace OASystem.API.Controllers
{
    /// <summary>
    /// 财务模块
    /// </summary>
    [Route("api/[controller]/[action]")]
    [ApiController]
    public class FinancialController : ControllerBase
    {
        private readonly IMapper _mapper;
        private readonly IConfiguration _config;
        private readonly SqlSugarClient _sqlSugar;
        private readonly HttpClient _httpClient;
        private readonly SetDataTypeRepository _setDataTypeRep;
        private readonly DailyFeePaymentRepository _daiRep;    //日付申请仓库
        private readonly TeamRateRepository _teamRateRep;      //团组汇率仓库
        private readonly ForeignReceivablesRepository _ForForeignReceivablesRep;  //对外收款账单仓库
        private readonly ProceedsReceivedRepository _proceedsReceivedRep;  //已收款项仓库
        private readonly PaymentRefundAndOtherMoneyRepository _paymentRefundAndOtherMoneyRep; //收款退还与其他款项 仓库

        /// <summary>
        /// 初始化
        /// </summary>
        public FinancialController(IMapper mapper, IConfiguration configuration, DailyFeePaymentRepository daiRep, SqlSugarClient sqlSugar, SetDataTypeRepository setDataTypeRep,
            TeamRateRepository teamRateRep, ForeignReceivablesRepository ForForeignReceivablesRep, ProceedsReceivedRepository proceedsReceivedRep,
            PaymentRefundAndOtherMoneyRepository paymentRefundAndOtherMoneyRep, HttpClient httpClient)
        {
            _mapper = mapper;
            _config = configuration;
            _daiRep = daiRep;
            _sqlSugar = sqlSugar;
            _setDataTypeRep = setDataTypeRep;
            _teamRateRep = teamRateRep;
            _ForForeignReceivablesRep = ForForeignReceivablesRep;
            _proceedsReceivedRep = proceedsReceivedRep;
            _paymentRefundAndOtherMoneyRep = paymentRefundAndOtherMoneyRep;
            _httpClient = httpClient;
        }

        #region 日付申请

        /// <summary>
        /// 获取日付申请 基础数据源
        /// </summary>
        /// <param name="dto"> 日付申请 分页 dto</param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> PostPageSearchDailyPaymentPriceTypeData(PortDtoBase dto)
        {
            var result = await _daiRep.GetPagePriceTypeData(dto);

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

            return Ok(JsonView(data));
        }


        /// <summary>
        /// 获取日付申请 基础数据源 - 转账表识 
        /// </summary>
        /// <param name="dto"> 日付申请 分页 dto</param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> GetSearchDailyPaymentPriceTypeAddData(PortDtoBase dto)
        {
            var result = await _daiRep.GetPriceTypeAddData(dto);

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

            return Ok(JsonView(data));
        }

        /// <summary>
        /// 日付申请 Page Search
        /// </summary>
        /// <param name="dto"> 日付申请 分页 dto</param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> PostPageSearchDailyPaymentList(PageDailyFeePaymentDto dto)
        {
            var result = await _daiRep.GetPageSearchAll(dto);

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

            return Ok(JsonView(data));
        }

        /// <summary>
        /// 日付申请 Single Search By Id
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> PostSearchDailyPaymentInfo(SearchDailyFeePaymentDto dto)
        {
            var result = await _daiRep.GetSearchById(dto);

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

            return Ok(JsonView(result.Data));
        }

        /// <summary>
        /// 日付申请 添加
        /// </summary>
        /// <param name="dto"> 日付申请 添加 dto</param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> PostAddDailyPayment(AddDailyFeePaymentDto dto)
        {
            var result = await _daiRep.Add(dto);

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

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

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

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

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

        /// <summary>
        /// 日付申请 财务审核
        /// </summary>
        /// <param name="dto"> dto </param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> PostDelDailyPaymentAudit(DP_AuditStatusDto dto)
        {
            var result = await _daiRep.DelDailyPaymentAudit(dto);
            if (result == null || result.Code != 0)
            {
                return Ok(JsonView(false, result.Msg));
            }
            return Ok(JsonView(true));
        }

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

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

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

                if (feeData != null)
                {

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

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

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

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

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

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

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

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


            return Ok(JsonView(true));
        }
        #endregion

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

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

        #region 团组汇率

        /// <summary>
        /// 团组汇率 Select数据源(团组列,汇率列)
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> GetGroupRateDataSources(TeamRateDto dto)
        {
            try
            {
                Result teamRateData = await _teamRateRep.GetGroupRateDataSource(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>
        ///// 团组汇率 changge
        ///// </summary>
        ///// <returns></returns>
        //[HttpPost]
        //[ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        //public async Task<IActionResult> ChangeGroupRateInfo()
        //{
        //    try
        //    {
        //        Result teamRateData = await _teamRateRep.GetGroupRateChangeData();
        //        if (teamRateData.Code != 0)
        //        {
        //            return Ok(JsonView(false, teamRateData.Msg));
        //        }
        //        return Ok(JsonView(true, teamRateData.Msg, teamRateData.Data));
        //    }
        //    catch (Exception ex)
        //    {
        //        return Ok(JsonView(false, ex.Message));
        //        throw;
        //    }
        //}

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


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

        #endregion

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

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

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

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

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

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

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

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

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

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


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

        #region 对外收款账单

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

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

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

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

        /// <summary>
        /// 已收账单 
        /// File Downloasd
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> PostReceivablesFeilDownload(ForeignReceivablesFeilDownloadDto dto)
        {
            try
            {
                if (dto.DiId < 1)
                {
                    return Ok(JsonView(false, "请传入有效DiId参数！"));
                }
                if (dto.FileType < 1 || dto.FileType > 3)
                {
                    return Ok(JsonView(false, "请传入有效FileType参数！ 1 生成收款单(四川)  2 生成收款单(北京)  3 汇款账单"));
                }

                var _DelegationInfo = _sqlSugar.Queryable<Grp_DelegationInfo>().Where(it => it.IsDel == 0 && it.Id == dto.DiId).First();
                if (_DelegationInfo == null)
                {
                    return Ok(JsonView(false, "暂无团组信息！！"));
                }

                if (dto.FileType == 1 || dto.FileType == 2)
                {
                    var _ForeignReceivables = _sqlSugar.Queryable<Fin_ForeignReceivables>().Where(it => it.IsDel == 0 && it.Diid == dto.DiId).ToList();
                    if (_ForeignReceivables.Count < 1)
                    {
                        return Ok(JsonView(false, "暂无收款信息！！"));
                    }

                    string tempName = "";
                    if (dto.FileType == 1) tempName = $"收款账单(四川)模板.docx";
                    else if (dto.FileType == 2) tempName = $"收款账单(北京)模板.docx";



                    string tempPath = AppSettingsHelper.Get("WordBasePath") + $"ForeignReceivables/Temp/{tempName}";
                    //载入模板
                    Document doc = new Document(tempPath);
                    DocumentBuilder builder = new DocumentBuilder(doc);

                    #region 替换Word模板书签内容

                    //这里可以创建个DataTable循环添加书签的值，这里提示一下就不多做修改了
                    //付款方
                    if (doc.Range.Bookmarks["To"] != null)
                    {
                        Bookmark mark = doc.Range.Bookmarks["To"];
                        mark.Text = _ForeignReceivables[0].To == null ? "" : _ForeignReceivables[0].To.ToString();
                    }
                    //付款方电话
                    if (doc.Range.Bookmarks["ToTel"] != null)
                    {
                        Bookmark mark = doc.Range.Bookmarks["ToTel"];
                        mark.Text = _ForeignReceivables[0].ToTel == null ? "" : _ForeignReceivables[0].ToTel.ToString();
                    }
                    //导出时间
                    if (doc.Range.Bookmarks["Date"] != null)
                    {
                        Bookmark mark = doc.Range.Bookmarks["Date"];
                        mark.Text = DateTime.Now.ToString("yyyy-MM-dd");
                    }
                    //注
                    if (doc.Range.Bookmarks["Attention"] != null)
                    {
                        Bookmark mark = doc.Range.Bookmarks["Attention"];
                        mark.Text = _ForeignReceivables[0].Attention == null ? "" : _ForeignReceivables[0].Attention.ToString();
                    }

                    //团队名称
                    if (doc.Range.Bookmarks["Team"] != null)
                    {
                        Bookmark mark = doc.Range.Bookmarks["Team"];
                        mark.Text = _DelegationInfo.VisitCountry == null ? "" : _DelegationInfo.VisitCountry.ToString();
                    }

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

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

                    #endregion

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

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

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

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

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


                    Dictionary<string, string> bookmarkArr = null;

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


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

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

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

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

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

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

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

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

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

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

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

                                if (placeArr.Contains(item.Place))
                                {
                                    continue;
                                }
                                else
                                {
                                    placeArr.Add(item.Place);
                                }
                                if (!string.IsNullOrWhiteSpace(item.Place))
                                {
                                    if (rowIndex > ChildTable.Rows.Count - 1)
                                    {
                                        var chitableRow = ChildTable.LastRow.Clone(true);
                                        ChildTable.AppendChild(chitableRow);
                                    }
                                    int days = dac1.FindAll(x => x.Place == item.Place).Count();
                                    SetCells(ChildTable, doc, rowIndex, 0, item.Place);
                                    SetCells(ChildTable, doc, rowIndex, 1, days + "晚");
                                    SetCells(ChildTable, doc, rowIndex, 2, item.Cost + item.Currency + "/晚");
                                    SetCells(ChildTable, doc, rowIndex, 3, " 汇率" + (item.SubTotal / item.Cost).ToString("#0.00"));
                                    SetCells(ChildTable, doc, rowIndex, 4, "CNY" + item.SubTotal * days + "\r\n");
                                    rowIndex++;
                                    zsinfo += item.Place + "  " + days + "晚 " + item.Cost + item.Currency + "/晚" + " 汇率" + (item.SubTotal /item.Cost).ToString("#0.00") + "   CNY" + item.SubTotal * days + "\r\n";
                                }

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

                                if (placeArr.Contains(item.Place))
                                {
                                    continue;
                                }
                                else
                                {
                                    placeArr.Add(item.Place);
                                }
                                if (!string.IsNullOrWhiteSpace(item.Place))
                                {
                                    if (rowIndex > ChildTable1.Rows.Count - 1)
                                    {
                                        var chitableRow = ChildTable1.LastRow.Clone(true);
                                        ChildTable1.AppendChild(chitableRow);
                                    }
                                    int days = dac2.FindAll(x => x.Place == item.Place).Count();
                                    SetCells(ChildTable1, doc, rowIndex, 0, item.Place);
                                    SetCells(ChildTable1, doc, rowIndex, 1, days + "天");
                                    SetCells(ChildTable1, doc, rowIndex, 2, item.Cost + item.Currency + "/天");
                                    SetCells(ChildTable1, doc, rowIndex, 3, " 汇率" + (item.SubTotal / item.Cost).ToString("#0.00"));
                                    SetCells(ChildTable1, doc, rowIndex, 4, "CNY" +item.SubTotal * days);
                                    rowIndex++;
                                    hsinfo += item.Place + "  " + days + "天 " + item.Cost + item.Currency + "/天" + " 汇率" + (item.SubTotal / item.Cost).ToString("#0.00") + "   CNY" +item.SubTotal * days + "\r\n";
                                }

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

                                if (placeArr.Contains(item.Place))
                                {
                                    continue;
                                }
                                else
                                {
                                    placeArr.Add(item.Place);
                                }
                                if (!string.IsNullOrWhiteSpace(item.Place))
                                {
                                    if (rowIndex > ChildTable2.Rows.Count - 1)
                                    {
                                        var chitableRow = ChildTable2.LastRow.Clone(true);
                                        ChildTable2.AppendChild(chitableRow);
                                    }
                                    int days = dac3.FindAll(x => x.Place == item.Place).Count();
                                    SetCells(ChildTable2, doc, rowIndex, 0, item.Place);
                                    SetCells(ChildTable2, doc, rowIndex, 1, days + "天");
                                    SetCells(ChildTable2, doc, rowIndex, 2, item.Cost + item.Currency + "/天");
                                    SetCells(ChildTable2, doc, rowIndex, 3, " 汇率" + (item.SubTotal / item.Cost).ToString("#0.00"));
                                    SetCells(ChildTable2, doc, rowIndex, 4, "CNY" + item.SubTotal * days + "\r\n");
                                    rowIndex++;
                                    gzinfo += item.Place + "  " + days + "天 " + item.Cost + item.Currency + "/天" + " 汇率" + (item.SubTotal / item.Cost).ToString("#0.00") + "   CNY" + item.SubTotal * days + "\r\n";
                                }

                            }
                            placeArr.Clear();
                            TeableBookmarkArr.Add("zsinfo", zsinfo); //住宿费详情
                            TeableBookmarkArr.Add("hs", dac2.Sum(x => Convert.ToDecimal(x.SubTotal)).ToString("#0.00") + " 元"); //伙食费
                            TeableBookmarkArr.Add("hsinfo", hsinfo); //伙食费详情
                            TeableBookmarkArr.Add("gz", dac3.Sum(x => Convert.ToDecimal(x.SubTotal)).ToString("#0.00") + " 元"); //公杂费
                            TeableBookmarkArr.Add("gzinfo", gzinfo); //公杂费详情
                            TeableBookmarkArr.Add("qt", (_EnterExitCosts.Visa + _EnterExitCosts.Safe).ToString("#0.00") + " 元");//其他费用
                            TeableBookmarkArr.Add("qtinfo", $"（签证费{_EnterExitCosts.Visa.ToString("#0.00")}元、保险{_EnterExitCosts.Safe.ToString("#0.00")}元等费用）");//其他费用第二列
                            TeableBookmarkArr.Add("fw", _EnterExitCosts.Service.ToString("#0.00") + "元/人");//服务费
                            TeableBookmarkArr.Add("AllPrice", AllPrice.ToString("#0.00") + "元/人");//表格合计费用
                            string airStr = client.AirType == 460 ? "经济舱" : client.AirType == 458 ? "公务舱" : "";
                            TeableBookmarkArr.Add("title", $"费用清单-{airStr}（{firstName}）");
                            foreach (var book in TeableBookmarkArr.Keys)
                            {

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

                            TeableBookmarkArr.Clear();
                        }


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

                        foreach (var book in bookmarkArr.Keys)
                        {

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

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

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

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

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

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

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

        #region 已收款项

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

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

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

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

                Result ffrData = await _proceedsReceivedRep.PostAmountReceivedAddOrEditDto(dto);

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

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

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

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

                Result ffrData = await _proceedsReceivedRep.PostAmountReceivedDel(dto);

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

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

        #endregion

        #region 收款退还与其他款项 

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

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

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

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

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

                #endregion

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

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

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

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

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

                PageFunAuthViewBase pageFunAuth = new PageFunAuthViewBase();

                #region 页面功能权限处理

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

                #endregion

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


                Result _result = await _paymentRefundAndOtherMoneyRep._Del(dto);

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

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

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

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

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

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

                Result _result = await _paymentRefundAndOtherMoneyRep._Info(dto);

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

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

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

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

                #endregion

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

                Result _result = await _paymentRefundAndOtherMoneyRep._AddOrEdit(dto);

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

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

        #region 应收报表

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

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

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

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

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


            #endregion

            string sql = string.Format(@" select distinct fr.diid,di.TeamName,di.ClientUnit,di.VisitDate from Fin_ForeignReceivables fr join Grp_DelegationInfo di on fr.DIID = di.id  {0} ", sqlWhere);

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

            decimal sumAll_fr = 0M;
            decimal sumAll_pr = 0M;
            decimal sumAll_balance = 0M;

            if (list_rst.Count > 0)
            {
                int rowNumber = 1;
                foreach (var item_rst in list_rst)
                {
                    item_rst.No = rowNumber;
                    rowNumber++;

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

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

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

                    //3.
                    string sql_other = string.Format(@" Select * From Fin_OtherPrice where diid = {0} and isdel = 0 and RefundType = 1 and PayType=1 ", diId);
                    List<Fin_OtherPrice> list_other = _sqlSugar.SqlQueryable<Fin_OtherPrice>(sql_other).ToList();
                    sum_other = list_other.Sum(s => s.Price);

                    //4.
                    string sql_extra = string.Format(@" Select c.* From Fin_GroupExtraCost f 
Inner join Grp_CreditCardPayment c On f.Id = c.CId 
Where c.CTable = 1015 And c.IsPay = 1 And f.IsDel = 0 And c.IsDel = 0 And f.DiId = {0} ", diId);
                    List<Grp_CreditCardPayment> list_extra = _sqlSugar.SqlQueryable<Grp_CreditCardPayment>(sql_extra).ToList();
                    sum_extra = list_extra.Sum(s => s.RMBPrice);

                    item_rst.frPrice = (sum_fr + sum_extra).ToString("#0.00");
                    item_rst.prPrice = (sum_pr - sum_other).ToString("#0.00");
                    item_rst.balPrice = ((sum_fr + sum_extra) - (sum_pr - sum_other)).ToString("#0.00");
                    item_rst.prClient = str_client;
                    item_rst.schedule = str_schedule;

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

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

                }

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

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

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

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

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


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

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


                }

            }


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

        }

        #endregion

        #region 付款申请

        /// <summary>
        /// 付款申请
        /// 查询 根据日期范围
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> PostPayRequest_Center(PostPayRequestByDateRangeDto 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"));
            }

            #endregion
            try
            {
                tree_Fin_DailyFeePaymentResult dailyResult = PayRequest_DailyByDateRange(dto.beginDt, dto.endDt);
                tree_Group_DailyFeePaymentResult groupResult = PayRequest_PaymentByDateRange(dto.beginDt, dto.endDt);

                return Ok(JsonView(true, "获取成功", new { daily = dailyResult, group = groupResult }));
            }
            catch (Exception ex)
            {
                return Ok(JsonView(false, ex.Message));
            }

        }

        /// <summary>
        /// 付款申请(日付申请相关)
        /// 查询 根据日期范围
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        private tree_Group_DailyFeePaymentResult PayRequest_PaymentByDateRange(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);
            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();
            List<int> teamDids = new List<int> { 300, 302, 248 }; //团组类型 不含
            var _groupDatas = _sqlSugar.Queryable<Grp_DelegationInfo>().Where(it => it.IsDel == 0 && groupIds.Contains(it.Id) && !teamDids.Contains(it.TeamDid)).ToList();

            #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

            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 = "";

                    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 = "【四川泛美交流有限公司】" + "【公转】" + "【导游：" + opData.ServiceGuide + "】" + opData.Area;
                                }
                                else if (payInfo.OrbitalPrivateTransfer == 1) //私转
                                {
                                    priName = "【四川泛美交流有限公司】" + "【私转】" + "【导游：" + 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;
                    }


                    childList.Add(new Group_DailyFeePaymentContentInfolView()
                    {
                        Id = payInfo.Id,
                        Payee = payInfo.Payee,
                        RowNumber = rouNumber,
                        Applicant = userDatas.Find(it => it.Id == payInfo.CreateUserId)?.CnName ??  "",
                        PayType = setDatas.Find(it => it.Id == payInfo.PayDId)?.Name ?? "",
                        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 //此次付款金额
                    }) ;
                    rouNumber++;
                }

                dataList.Add(new tree_Group_DailyFeePaymentPageListView()
                {
                    GroupName = groupInfo.TeamName,
                    CNYTotalAmount = childList.Sum(it => it.CNYSubTotalAmount),
                    ChildList = childList,
                });
            }

            _DailyFeePaymentResult.dataList = dataList;
            return _DailyFeePaymentResult;
        }


        /// <summary>
        /// 付款申请(日付申请相关)
        /// 查询 根据日期范围
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        private tree_Fin_DailyFeePaymentResult PayRequest_DailyByDateRange(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);

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

            }

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

        #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;

            _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
                });
                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(false, "操作成功"));
        }

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

            if (costDto.costSign != 3)
            {
                Grp_TeamRate tr = _daiRep.Query<Grp_TeamRate>(s => s.DiId == costDto.diId && s.CTable == 1015).First();

                if (tr != null)
                {
                    if (costDto.currency == 49)
                    {
                        dcm_dayrate = tr.RateU;
                        dcm_rmbPrice = dcm_rmbPrice * tr.RateU;
                    }
                    else if (costDto.currency == 51)
                    {
                        dcm_dayrate = tr.RateE;
                        dcm_rmbPrice = dcm_rmbPrice * tr.RateE;
                    }
                }
            }

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

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

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

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

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

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

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

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

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

                ccp.RMBPrice = dcm_rmbPrice; //upd

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

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


            return false;
        }

        /// <summary>
        /// 超支费用
        /// 列表查询
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> PostGroupExtraCost_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.Price,f.FilePath,f.CreateUserId,f.CreateTime,c.IsAuditGM
                                                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 {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
                                                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)
                {
                    //费用详细类型
                    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 (dic_user.ContainsKey(item.CreateUserId))
                    {
                        item.CreateUserIdStr = dic_setData[item.CreateUserId];
                    }
                    else
                    {
                        Sys_Users users = _daiRep.Query<Sys_Users>(s => s.Id == item.CreateUserId).First();
                        if (users != null)
                        {
                            item.CreateUserIdStr = users.CnName;
                            dic_setData.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> 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,
                PriceType = _PriceType,
                PriceDetailType = _PriceDetailType
            };


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

        #endregion
    }
}