OA2023/OASystem/OASystem.Api/Controllers/GroupsController.cs

using Aspose.Cells;
using Aspose.Cells.Drawing.Texts;
using Aspose.Words;
using Aspose.Words.Tables;
using NPOI.Util;
using OASystem.API.OAMethodLib;
using OASystem.API.OAMethodLib.File;
using OASystem.Domain.Dtos.Groups;
using OASystem.Domain.Entities.Groups;
using OASystem.Domain.ViewModels.Groups;
using OASystem.Infrastructure.Repositories.Groups;
using TencentCloud.Ocr.V20181119.Models;
using OASystem.Infrastructure.Tools;
using System.Web;
using System.Data;
using static OASystem.Infrastructure.Repositories.Groups.AirTicketResRepository;
using static OpenAI.GPT3.ObjectModels.SharedModels.IOpenAiModels;
using Cell = Aspose.Words.Tables.Cell;
using Row = Aspose.Words.Tables.Row;
using System.Runtime.Intrinsics.Arm;
using Microsoft.AspNetCore.Mvc.Filters;
using OASystem.Domain.Entities.Customer;
using NPOI.SS.Formula.Functions;
using OASystem.Domain.Dtos.CRM;
using System.Diagnostics;

namespace OASystem.API.Controllers
{
    /// <summary>
    /// 团组相关
    /// </summary>
    //[Authorize]
    [Route("api/[controller]/[action]")]
    public class GroupsController : ControllerBase
    {
        private readonly GrpScheduleRepository _grpScheduleRep;
        private readonly IMapper _mapper;
        private readonly DelegationInfoRepository _groupRepository;
        private readonly TaskAssignmentRepository _taskAssignmentRep;
        private readonly AirTicketResRepository _airTicketResRep;
        private readonly DecreasePaymentsRepository _decreasePaymentsRep;
        private readonly InvitationOfficialActivitiesRepository _InvitationOfficialActivitiesRep;
        private readonly DelegationEnDataRepository _delegationEnDataRep;
        private readonly SqlSugarClient _sqlSugar;
        private string url;
        private string path;

        private readonly EnterExitCostRepository _enterExitCostRep;

        public GroupsController(IMapper mapper, SqlSugarClient sqlSugar, GrpScheduleRepository grpScheduleRep, DelegationInfoRepository groupRepository,
            TaskAssignmentRepository taskAssignmentRep, AirTicketResRepository airTicketResRep, DecreasePaymentsRepository decreasePaymentsRep,
            InvitationOfficialActivitiesRepository InvitationOfficialActivitiesRep, DelegationEnDataRepository delegationEnDataRep, EnterExitCostRepository enterExitCostRep)
        {
            _mapper = mapper;
            _grpScheduleRep = grpScheduleRep;
            _groupRepository = groupRepository;
            _taskAssignmentRep = taskAssignmentRep;
            _airTicketResRep = airTicketResRep;
            _sqlSugar = sqlSugar;
            url = AppSettingsHelper.Get("ExcelBaseUrl");
            path = AppSettingsHelper.Get("ExcelBasePath");
            if (!System.IO.Directory.Exists(path))
            {
                System.IO.Directory.CreateDirectory(path);//不存在就创建文件夹
            }
            _decreasePaymentsRep = decreasePaymentsRep;
            _InvitationOfficialActivitiesRep = InvitationOfficialActivitiesRep;
            _delegationEnDataRep = delegationEnDataRep;
            _enterExitCostRep = enterExitCostRep;
        }

        #region 流程管控

        /// <summary>
        /// 获取团组流程管控信息
        /// </summary>
        /// <param name="paras">参数Json字符串</param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> PostSearchGrpSchedule(JsonDtoBase _jsonDto)
        {
            if (string.IsNullOrEmpty(_jsonDto.Paras))
            {
                return Ok(JsonView(false, "参数为空"));
            }

            Grp_ScheduleDto _ScheduleDto = JsonConvert.DeserializeObject<Grp_ScheduleDto>(_jsonDto.Paras);
            if (_ScheduleDto != null)
            {
                if (_ScheduleDto.SearchType == 2)//获取列表
                {
                    List<Grp_ScheduleView> _grpScheduleViewList = await _grpScheduleRep.GetViewList_GrpSchedule(_ScheduleDto);
                    return Ok(JsonView(_grpScheduleViewList));
                }
                else//获取对象
                {
                    Grp_ScheduleCombinView _grpScheduleView = await _grpScheduleRep.GetView_GrpSchedule(_ScheduleDto);
                    if (_grpScheduleView != null)
                    {
                        return Ok(JsonView(_grpScheduleView));
                    }
                }
            }
            else
            {
                return Ok(JsonView(false, "参数反序列化失败"));
            }

            return Ok(JsonView(false, "暂无数据！"));
        }

        /// <summary>
        /// 修改团组流程管控详细表数据
        /// </summary>
        /// <param name="paras"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> PostUpdateGrpScheduleDetail(Grp_ScheduleDetailUpdDto dto)
        {
            Grp_ScheduleDetailInfo _detail = _mapper.Map<Grp_ScheduleDetailInfo>(dto);
            var result = await _grpScheduleRep._sqlSugar.Updateable<Grp_ScheduleDetailInfo>()
                .SetColumns(it => it.Duty == _detail.Duty)
                .SetColumns(it => it.ExpectBeginDt == _detail.ExpectBeginDt)
                .SetColumns(it => it.ExpectEndDt == _detail.ExpectEndDt)
                .SetColumns(it => it.JobContent == _detail.JobContent)
                .SetColumns(it => it.Remark == _detail.Remark)
                .SetColumns(it => it.StepStatus == _detail.StepStatus)
                .Where(s => s.Id == dto.Id)

                //.UpdateColumns(s => new { s.Duty, s.ExpectBeginDt, s.ExpectEndDt, s.JobContent, s.Remark, s.StepStatus })
                .ExecuteCommandAsync();
            if (result > 0)
            {
                return Ok(JsonView(true, "保存成功！"));
            }

            return Ok(JsonView(false, "保存失败！"));
        }

        /// <summary>
        /// 删除团组流程管控详细表数据，删除人Id请放在Duty
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<ActionResult> PostDeleteGrpScheduleDetail(Grp_ScheduleDetailUpdDto dto)
        {
            Grp_ScheduleDetailInfo _detail = _mapper.Map<Grp_ScheduleDetailInfo>(dto);
            _detail.IsDel = 1;
            _detail.DeleteUserId = dto.Duty;
            _detail.DeleteTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
            var result = await _grpScheduleRep._sqlSugar.Updateable<Grp_ScheduleDetailInfo>()
               .SetColumns(it => it.IsDel == _detail.IsDel)
               .SetColumns(it => it.DeleteUserId == _detail.DeleteUserId)
               .SetColumns(it => it.DeleteTime == _detail.DeleteTime)
               .Where(it => it.Id == dto.Id)

               //.UpdateColumns(s => new { s.IsDel, s.DeleteUserId, s.DeleteTime })
               //.WhereColumns(s => s.Id == dto.Id)
               .ExecuteCommandAsync();
            if (result > 0)
            {
                return Ok(JsonView(true, "删除成功！"));
            }

            return Ok(JsonView(false, "删除失败！"));
        }

        /// <summary>
        /// 增加团组流程管控详细表数据
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<ActionResult> PostInsertGrpScheduleDetail(Grp_ScheduleDetailInsertDto dto)
        {
            Grp_ScheduleDetailInfo _detail = _mapper.Map<Grp_ScheduleDetailInfo>(dto);
            if (DateTime.Now < _detail.ExpectBeginDt)
            {
                _detail.StepStatus = 0;
            }
            else
            {//若大于设置时间，不考虑设置的预计结束日期，统一视为进行中
                _detail.StepStatus = 1;
            }
            var result = await _grpScheduleRep._sqlSugar.Insertable(_detail).ExecuteReturnIdentityAsync();
            if (result > 0)
            {
                Grp_ScheduleDetailView _result = await _grpScheduleRep.GetInsertBackData(result);

                return Ok(JsonView(true, "添加成功！", _result));
            }

            return Ok(JsonView(false, "添加失败！"));
        }

        #endregion

        #region 团组基本信息

        /// <summary>
        ///  接团信息列表
        /// </summary>
        /// <param name="dto">团组列表请求dto</param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> GetGroupList(GroupListDto dto)
        {
            var groupData = await _groupRepository.GetGroupList(dto);
            if (groupData.Code != 0)
            {
                return Ok(JsonView(false, groupData.Msg));
            }

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

        /// <summary>
        ///  接团信息详情
        /// </summary>
        /// <param name="dto">团组info请求dto</param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> GetGroupInfo(GroupInfoDto dto)
        {
            var groupData = await _groupRepository.GetGroupInfo(dto);
            if (groupData.Code != 0)
            {
                return Ok(JsonView(false, groupData.Msg));
            }

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

        /// <summary>
        /// 接团信息 编辑添加
        /// 基础信息数据源
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> GroupEditBasicSource(GroupListDto dto)
        {

            var groupData = await _groupRepository.GroupEditBasicSource(dto);
            if (groupData.Code != 0)
            {
                return Ok(JsonView(false, groupData.Msg));
            }
            return Ok(JsonView(groupData.Data));

        }

        /// <summary>
        /// 接团信息 操作(增改)
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> GroupOperation(GroupOperationDto dto)
        {
            try
            {
                var groupData = await _groupRepository.GroupOperation(dto);
                if (groupData.Code != 0)
                {
                    return Ok(JsonView(false, groupData.Msg));
                }

                return Ok(JsonView(true));
            }
            catch (Exception ex)
            {

                Logs("[response]" + JsonConvert.SerializeObject(dto));
                Logs(ex.Message);
                return Ok(JsonView(false, ex.Message));
            }
        }

        /// <summary>
        /// 接团信息 操作(删除)
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> GroupDel(GroupDelDto dto)
        {
            try
            {
                var groupData = await _groupRepository.GroupDel(dto);
                if (groupData.Code != 0)
                {
                    return Ok(JsonView(false, groupData.Msg));
                }

                return Ok(JsonView(true));
            }
            catch (Exception ex)
            {

                Logs("[response]" + JsonConvert.SerializeObject(dto));
                Logs(ex.Message);
                return Ok(JsonView(false, ex.Message));
            }
        }

        /// <summary>
        /// 获取团组销售报价号
        /// 团组添加时 使用
        /// </summary>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> GetGroupSalesQuoteNo()
        {
            var groupData = await _groupRepository.GetGroupSalesQuoteNo();
            if (groupData.Code != 0)
            {
                return Ok(JsonView(false, groupData.Msg));
            }
            object salesQuoteNo = new
            {
                SalesQuoteNo = groupData.Data
            };

            return Ok(JsonView(salesQuoteNo));
        }

        /// <summary>
        /// 设置确认出团
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> SetConfirmationGroup(ConfirmationGroupDto dto)
        {
            var groupData = await _groupRepository.ConfirmationGroup(dto);
            if (groupData.Code != 0)
            {
                return Ok(JsonView(false, groupData.Msg));
            }

            GroupStepForDelegation.CreateWorkStep(dto.GroupId); //创建管控流程

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

        /// <summary>
        /// 获取团组名称 List
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> GetGroupNameList(GroupNameDto dto)
        {
            var groupData = await _groupRepository.GetGroupNameList(dto);
            if (groupData.Code != 0)
            {
                return Ok(JsonView(false, groupData.Msg));
            }

            return Ok(JsonView(groupData.Data, groupData.Data.Count));
        }

        /// <summary>
        /// 获取团组名称data  And 签证国别Data
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> GetGroupNameAndVisaNationality(GroupNameDto dto)
        {
            var groupData = await _groupRepository.GetGroupNameAndVisaNationality(dto);
            if (groupData.Code != 0)
            {
                return Ok(JsonView(false, groupData.Msg));
            }


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

        #endregion

        #region 团组&签证

        /// <summary>
        ///  根据团组Id获取签证客户信息List
        /// </summary>
        /// <param name="dto">请求dto</param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> GetCrmByGroupId(ClientByGroupIdDto dto)
        {
            var groupData = await _groupRepository.GetCrmByGroupId(dto);
            if (groupData.Code != 0)
            {
                return Ok(JsonView(false, groupData.Msg));
            }

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



        #endregion

        #region 团组任务分配
        /// <summary>
        /// 团组任务分配初始化
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> GetTaskAssignmen()
        {
            var groupData = await _taskAssignmentRep.GetTaskAssignmen();
            if (groupData.Code != 0)
            {
                return Ok(JsonView(false, groupData.Msg));
            }
            return Ok(JsonView(true, groupData.Msg, groupData.Data));
        }

        /// <summary>
        /// 团组任务分配查询
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> TaskAssignmenQuery(TaskAssignmenQueryDto dto)
        {
            var groupData = await _taskAssignmentRep.TaskAssignmenQuery(dto);
            if (groupData.Code != 0)
            {
                return Ok(JsonView(false, groupData.Msg));
            }
            return Ok(JsonView(true, groupData.Msg, groupData.Data));
        }


        /// <summary>
        /// 团组任务分配操作
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> GetTaskAssignmenOp(TaskAssignmenDto dto)
        {
            Result groupData = await _taskAssignmentRep.GetTaskAssignmenOp(dto);
            if (groupData.Code != 0)
            {
                return Ok(JsonView(false, groupData.Msg));
            }
            return Ok(JsonView(true, groupData.Msg, groupData.Data));
        }
        #endregion

        #region 团组费用审核

        /// <summary>
        /// 获取团组费用审核
        /// </summary>
        /// <param name="paras">参数Json字符串</param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> PostSearchGrpCreditCardPayment(Search_GrpCreditCardPaymentDto _dto)
        {
            if (_dto.DiId < 1)
            {
                return Ok(JsonView(false, "团组Id为空"));
            }
            Grp_CreditCardPaymentView _view = new Grp_CreditCardPaymentView();

            #region 团组基本信息

            Grp_DelegationInfo _delegation = _groupRepository.Query<Grp_DelegationInfo>(s => s.Id == _dto.DiId).First();
            if (_delegation != null)
            {
                _view.ClientName = _delegation.ClientName;
                _view.DiId = _dto.DiId;
                _view.TeamName = _delegation.TeamName;
                _view.VisitCountry = _delegation.VisitCountry;
                _view.VisitDate = _delegation.VisitStartDate.ToString("yyyy-MM-dd") + " ~ " + _delegation.VisitEndDate.ToString("yyyy-MM-dd");
                _view.VisitDays = _delegation.VisitDays;
                _view.VisitPNumber = _delegation.VisitPNumber;
            }
            else
            {
                return Ok(JsonView(false, "团组信息为空"));
            }

            #endregion

            #region 费用清单
            var exp = Expressionable.Create<Grp_CreditCardPayment>();
            exp.AndIF(_dto.AuditStatus != -1, it => it.IsAuditGM == _dto.AuditStatus);
            exp.AndIF(_dto.Label != -1, it => it.CTable == _dto.Label);

            List<Grp_CreditCardPayment> entityList = _groupRepository
                .Query<Grp_CreditCardPayment>(s => s.DIId == _dto.DiId && s.IsDel == 0 && s.CreateUserId > 0)
                .Where(exp.ToExpression())
                .ToList();

            List<Grp_CreditCardPaymentDetailView> detailList = new List<Grp_CreditCardPaymentDetailView>();
            decimal CNY = 0;
            decimal PayCNY = 0;
            decimal BalanceCNY = 0;
            decimal YSFYCNY = 0;

            decimal USD = 0;
            decimal PayUSD = 0;
            decimal BalanceUSD = 0;
            decimal YSFYUSD = 0;

            decimal EUR = 0;
            decimal PayEUR = 0;
            decimal BalanceEUR = 0;
            decimal YSFYEUR = 0;

            foreach (var entity in entityList)
            {
                Grp_CreditCardPaymentDetailView _detail = new Grp_CreditCardPaymentDetailView();

                _detail.Id = entity.Id;

                /*
                 * Bus名称
                 */
                _detail.BusName = "";

                /*
                 *费用所属
                 */
                switch (entity.CTable)
                {
                    case 85:
                        Grp_AirTicketReservations jpRes = _groupRepository.Query<Grp_AirTicketReservations>(s => s.Id == entity.CId).First();
                        if (jpRes != null)
                        {
                            string FlightsDescription = jpRes.FlightsDescription;
                            string PriceDescription = jpRes.PriceDescription;
                            _detail.PriceMsgContent = "航班号：" + jpRes.FlightsCode + "<br/>城市A-B：" + jpRes.FlightsCity + "<br/>航班描述：" + FlightsDescription.Replace("\r\n", "<br />") + "<br/>" + "价格描述：" + PriceDescription;
                            _detail.PriceNameContent = "(" + jpRes.FlightsCode + ")";
                        }
                        break;
                    case 79:
                        _detail.BusName = "待增加";
                        break;
                    case 98:
                        Grp_DecreasePayments gdpRes = _groupRepository.Query<Grp_DecreasePayments>(s => s.Id == entity.CId).First();
                        if (gdpRes != null)
                        {
                            _detail.PriceMsgContent = "备注：" + gdpRes.Remark;
                            _detail.PriceNameContent = gdpRes.PriceName;
                        }
                        break;
                    default:
                        break;
                }

                /*
                 * 费用模块
                 */
                Sys_SetData sdPriceName = _groupRepository.Query<Sys_SetData>(s => s.Id == entity.CTable).First();
                if (sdPriceName != null)
                {
                    _detail.PriceName = sdPriceName.Name;
                }

                /*
                 * 应付款金额	
                 */
                Sys_SetData sdPaymentCurrency_WaitPay = _groupRepository.Query<Sys_SetData>(s => s.Id == entity.PaymentCurrency).First();
                string PaymentCurrency_WaitPay = "Unknown";
                if (sdPaymentCurrency_WaitPay != null)
                {
                    PaymentCurrency_WaitPay = sdPaymentCurrency_WaitPay.Name;
                }
                _detail.WaitPay = entity.PayMoney.ToString("#0.00") + " " + PaymentCurrency_WaitPay;

                /*
                 * 此次付款金额
                 */
                decimal CurrPayStr = 0;
                if (entity.PayPercentage == 0)
                {
                    if (entity.PayThenMoney != 0)
                        CurrPayStr = entity.PayThenMoney * entity.DayRate;
                }
                else
                {
                    CurrPayStr = entity.PayMoney * (decimal.Parse(entity.PayPercentage.ToString("#0.00")) / 100 * entity.DayRate);
                }
                _detail.CurrPay = CurrPayStr.ToString("#0.00") + " CNY";

                /*
                 * 剩余尾款
                 */
                decimal BalanceStr = 0;
                if (entity.PayMoney - (CurrPayStr / entity.DayRate) < 0.01M)
                    BalanceStr = 0;
                else
                    BalanceStr = (entity.PayMoney - CurrPayStr / entity.DayRate);

                _detail.Balance = BalanceStr.ToString("#0.00") + " " + PaymentCurrency_WaitPay;

                /*
                 * 申请人
                 */
                string operatorName = "无";
                Sys_Users _opUser = _groupRepository.Query<Sys_Users>(s => s.Id == entity.CreateUserId).First();
                if (_opUser != null)
                {
                    operatorName = _opUser.CnName;
                }
                _detail.OperatorName = operatorName;

                /*
                * 审核人
                */
                string auditOperatorName = "Unknown";
                if (entity.AuditGMOperate == 0)
                    auditOperatorName = "无";
                else if (entity.AuditGMOperate == 4)
                    auditOperatorName = "自动审核";
                else
                {
                    Sys_Users _adUser = _groupRepository.Query<Sys_Users>(s => s.Id == entity.AuditGMOperate).First();
                    if (_adUser != null)
                    {
                        auditOperatorName = _adUser.CnName;
                    }
                }
                _detail.AuditOperatorName = auditOperatorName;

                /*
                 * 
                 * *超预算比例
                 */
                string overBudgetStr = "";

                if (entity.ExceedBudget == -1)
                    overBudgetStr = sdPriceName.Name + "尚无预算";
                else if (entity.ExceedBudget == 0)
                    overBudgetStr = "未超预算";
                else
                    overBudgetStr = entity.ExceedBudget.ToString("P");
                _detail.OverBudget = overBudgetStr;


                /*
                 * 费用总计
                 */


                if (entity.PaymentCurrency == 48)
                {
                    CNY += entity.PayMoney;
                    PayCNY += CurrPayStr;
                    BalanceCNY += BalanceStr;
                    YSFYCNY += CurrPayStr;
                }
                if (entity.PaymentCurrency == 49)
                {
                    USD += entity.PayMoney;
                    PayUSD += CurrPayStr;
                    BalanceUSD += BalanceStr;
                    YSFYUSD += CurrPayStr;
                }
                if (entity.PaymentCurrency == 51)
                {
                    EUR += entity.PayMoney;
                    PayEUR += CurrPayStr;
                    BalanceEUR += BalanceStr;
                    YSFYEUR += CurrPayStr;
                }

                _detail.IsAuditGM = entity.IsAuditGM;

                detailList.Add(_detail);

            }

            #endregion
            _view.DetailList = new List<Grp_CreditCardPaymentDetailView>(detailList);
            _view.TotalStr1 = string.Format(@"应付款总金额：{0}CNY&nbsp;|&nbsp;{1}USD&nbsp;|&nbsp;{2}EUR", CNY, USD, EUR);
            _view.TotalStr2 = string.Format(@"此次付款总金额：{0}CNY&nbsp;|&nbsp;{1}USD&nbsp;|&nbsp;{2}EUR", PayCNY, PayUSD, PayEUR);
            _view.TotalStr3 = string.Format(@"目前剩余尾款总金额：{0}CNY&nbsp;|&nbsp;{1}USD&nbsp;|&nbsp;{2}EUR", BalanceCNY, BalanceUSD, BalanceEUR);
            _view.TotalStr4 = string.Format(@"已审费用总额：{0}CNY&nbsp;|&nbsp;{1}USD&nbsp;|&nbsp;{2}EUR", YSFYCNY, YSFYUSD, YSFYEUR);


            return Ok(JsonView(_view));
        }


        /// <summary>
        /// 修改团组费用审核状态
        /// </summary>
        /// <param name="paras">参数Json字符串</param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> PostAuditGrpCreditCardPayment(Edit_GrpCreditCardPaymentDto _dto)
        {
            List<string> idList = _dto.CreditIdStr.Split(',').ToList();
            Grp_CreditCardPayment _detail = _mapper.Map<Grp_CreditCardPayment>(_dto);
            DateTime dtNow = DateTime.Now;
            _groupRepository.BeginTran();
            int rst = 0;
            foreach (var item in idList)
            {
                int CreditId = int.Parse(item);
                var result = await _grpScheduleRep._sqlSugar.Updateable<Grp_CreditCardPayment>()
                .SetColumns(it => it.IsAuditGM == _dto.AuditCode)
                .SetColumns(it => it.AuditGMOperate == _dto.User)
                .SetColumns(it => it.AuditGMDate == dtNow.ToString())
                .Where(s => s.Id == CreditId)
                .ExecuteCommandAsync();
                if (result < 1)
                {
                    rst = -1;
                }
                else
                {
                    _groupRepository.RollbackTran();
                    return Ok(JsonView(false, "保存失败并回滚！"));
                }
            }
            _groupRepository.CommitTran();
            if (rst == 0)
            {
                return Ok(JsonView(true, "保存成功！"));
            }

            return Ok(JsonView(false, "保存失败！"));
        }

        #endregion

        #region 机票费用录入
        /// <summary>
        /// 机票录入当前登录人可操作团组
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> AirTicketResSelect(AirTicketResDto dto)
        {
            try
            {
                Result groupData = await _airTicketResRep.AirTicketResSelect(dto);
                if (groupData.Code != 0)
                {
                    return Ok(JsonView(false, groupData.Msg));
                }
                return Ok(JsonView(true, groupData.Msg, groupData.Data));
            }
            catch (Exception ex)
            {
                return Ok(JsonView(false, "程序错误！"));
                throw;
            }
        }
        /// <summary>
        /// 机票费用录入列表
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> AirTicketResList(AirTicketResDto dto)
        {
            try
            {
                Result groupData = await _airTicketResRep.AirTicketResList(dto);
                if (groupData.Code != 0)
                {
                    return Ok(JsonView(false, groupData.Msg));
                }
                return Ok(JsonView(true, groupData.Msg, groupData.Data));
            }
            catch (Exception ex)
            {
                return Ok(JsonView(false, "程序错误！"));
                throw;
            }
        }
        /// <summary>
        /// 根据id查询费用录入信息
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> AirTicketResById(AirTicketResByIdDto dto)
        {
            try
            {
                Result groupData = await _airTicketResRep.AirTicketResById(dto);
                if (groupData.Code != 0)
                {
                    return Ok(JsonView(false, groupData.Msg));
                }
                return Ok(JsonView(true, groupData.Msg, groupData.Data));
            }
            catch (Exception ex)
            {
                return Ok(JsonView(false, "程序错误！"));
                throw;
            }
        }
        /// <summary>
        /// 机票费用录入操作（Status:1.新增,2.修改）
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> OpAirTicketRes(AirTicketResOpDto dto)
        {
            try
            {
                Result groupData = await _airTicketResRep.OpAirTicketRes(dto);
                if (groupData.Code != 0)
                {
                    return Ok(JsonView(false, groupData.Msg));
                }
                return Ok(JsonView(true, groupData.Msg, groupData.Data));
            }
            catch (Exception ex)
            {
                return Ok(JsonView(false, "程序错误！"));
                throw;
            }
        }
        /// <summary>
        /// 根据舱位类型查询接团客户名单信息
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> tankType(AirTicketResByIdDto dto)
        {
            try
            {
                List<Crm_GroupCustomer> crm_Groups = _sqlSugar.Queryable<Crm_GroupCustomer>().Where(a=>a.IsDel==0 && a.AirType==dto.Id).ToList();
                if (crm_Groups.Count!=0)
                {
                    List<dynamic> Customer = new List<dynamic>();
                    foreach (var item in crm_Groups)
                    {
                        var data = new
                        {
                            Id = item.Id,
                            Pinyin = item.Pinyin,
                            Name = item.LastName+item.FirstName
                        };
                        Customer.Add(data);
                    }
                    return Ok(JsonView(true, "查询成功！", Customer));
                }
                return Ok(JsonView(true, "暂无数据", crm_Groups));
            }
            catch (Exception ex)
            {
                return Ok(JsonView(false, "程序错误！"));
                throw;
            }
        }

        /// <summary>
        /// 机票费用录入，删除
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> DelAirTicketRes(DelBaseDto dto)
        {
            try
            {
                var res = await _airTicketResRep.SoftDeleteByIdAsync<Grp_AirTicketReservations>(dto.Id.ToString(), dto.DeleteUserId);
                if (!res)
                {
                    return Ok(JsonView(false, "删除失败"));
                }
                return Ok(JsonView(true, "删除成功！"));
            }
            catch (Exception ex)
            {
                return Ok(JsonView(false, "程序错误！"));
                throw;
            }
        }
        /// <summary>
        /// 导出机票录入报表
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> DeriveAirTicketRes(AirTicketResDto dto)
        {
            try
            {
                Result groupData = await _airTicketResRep.DeriveAirTicketRes(dto);
                if (groupData.Code != 0)
                {
                    return Ok(JsonView(false, groupData.Msg));
                }
                else
                {
                    List<AirTicketReservationsPayView> AirTicketReservations = groupData.Data.GetType().GetProperty("AirTicketRes").GetValue(groupData.Data);
                    if (AirTicketReservations.Count != 0)
                    {
                        Grp_DelegationInfo DelegationInfo = groupData.Data.GetType().GetProperty("Delegation").GetValue(groupData.Data);
                        Sys_Users _Users = groupData.Data.GetType().GetProperty("Users").GetValue(groupData.Data);
                        string diCode = DelegationInfo != null ? DelegationInfo.TourCode : "XXX";
                        string diName = DelegationInfo != null ? DelegationInfo.TeamName : "XXX";
                        WorkbookDesigner designer = new WorkbookDesigner();
                        designer.Workbook = new Workbook(AppSettingsHelper.Get("ExcelBasePath") + "Template/机票预订费用报表模板.xlsx");
                        decimal countCost = 0;
                        foreach (var item in AirTicketReservations)
                        {
                            if (item.BankType == "其他")
                            {
                                item.BankNo = "--";
                            }
                            else
                            {
                                item.BankNo = item.BankType + ":" + item.BankNo.Substring(0, 3);
                            }
                            item.PrePrice = System.Decimal.Round(item.PrePrice, 2);
                            item.Price = System.Decimal.Round(item.Price, 2);
                            countCost += Convert.ToDecimal(item.Price);
                        }
                        designer.SetDataSource("Export", AirTicketReservations);
                        designer.SetDataSource("ExportDiCode", diCode);
                        designer.SetDataSource("ExportDiName", diName);
                        designer.SetDataSource("ExportOpUserName", _Users.CnName);

                        designer.SetDataSource("ExportCountCost", countCost + "(" + AirTicketReservations[0].CurrencyStr);
                        designer.Process();
                        string fileName = "AirfareStatement/" + diName + "机票费用报表" + "_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx";
                        designer.Workbook.Save(AppSettingsHelper.Get("ExcelBasePath") + fileName);
                        string rst = AppSettingsHelper.Get("ExcelBaseUrl") + AppSettingsHelper.Get("ExcelFtpPath") + fileName;
                        return Ok(JsonView(true, "成功", url = rst));
                    }
                    else
                    {
                        return Ok(JsonView(false, "暂无数据！"));
                    }
                }

            }
            catch (Exception ex)
            {
                return Ok(JsonView(false, "程序错误！"));
                throw;
            }
        }
        Dictionary<string, string> transDic = new Dictionary<string, string>();
        /// <summary>
        /// 行程单导出
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> ItineraryAirTicketRes(ItineraryAirTicketResDto dto)
        {
            try
            {
                Result groupData = await _airTicketResRep.ItineraryAirTicketRes(dto);
                if (groupData.Code != 0)
                {
                    return Ok(JsonView(false, groupData.Msg));
                }
                else
                {
                    List<AirTicketReservationsView> _AirTicketReservations = groupData.Data;
                    if (dto.Language == "CN")
                    {
                        Document doc = new Document(AppSettingsHelper.Get("WordBasePath") + "Template/电子客票行程单模板_CN.docx");
                        DocumentBuilder builder = new DocumentBuilder(doc);
                        int tableIndex = 0;//表格索引
                                           //得到文档中的第一个表格
                        Table table = (Table)doc.GetChild(NodeType.Table, tableIndex, true);

                        foreach (var item in _AirTicketReservations)
                        {
                            #region 处理固定数据
                            string[] FlightsCode = item.FlightsCode.Split('/');
                            if (FlightsCode.Length != 0)
                            {
                                Res_AirCompany _AirCompany = _sqlSugar.Queryable<Res_AirCompany>().First(a => a.ShortCode == FlightsCode[0].Substring(0, 2));
                                if (_AirCompany != null)
                                {
                                    table.Range.Bookmarks["AirlineCompany"].Text = _AirCompany.CnName;
                                }
                                else
                                {
                                    table.Range.Bookmarks["AirlineCompany"].Text = "--";
                                }
                            }
                            table.Range.Bookmarks["AirlineRecordCode"].Text = "--";
                            table.Range.Bookmarks["ReservationRecordCode"].Text = "--";
                            string[] nameArray = Regex.Split(item.ClientName, "\\d+\\.", RegexOptions.IgnoreCase);
                            nameArray = nameArray.Where(str => str != "" && str != " " && !string.IsNullOrEmpty(str)).ToArray();
                            string name = "";
                            foreach (string clientName in nameArray)
                            {
                                if (!name.Contains(clientName))
                                {
                                    name += clientName + ",";
                                }
                            }
                            if (!string.IsNullOrWhiteSpace(name))
                            {
                                table.Range.Bookmarks["ClientName"].Text = name.Substring(0, name.Length - 1);
                            }
                            else
                            {
                                table.Range.Bookmarks["ClientName"].Text = "--";
                            }
                            table.Range.Bookmarks["TicketNumber"].Text = "--";
                            table.Range.Bookmarks["IdentificationCode"].Text = "--";
                            table.Range.Bookmarks["JointTicket"].Text = "--";
                            table.Range.Bookmarks["TimeIssue"].Text = "--";
                            table.Range.Bookmarks["DrawingAgent"].Text = "--";
                            table.Range.Bookmarks["NavigationCode"].Text = "--";
                            table.Range.Bookmarks["AgentsAddress"].Text = "--";
                            table.Range.Bookmarks["AgentPhone"].Text = "--";
                            table.Range.Bookmarks["AgentFacsimile"].Text = "--";

                            #endregion
                            #region 循环数据处理
                            List<AirInfo> airs = new List<AirInfo>();
                            string[] DayArray = Regex.Split(item.FlightsDescription, "\\d+\\.", RegexOptions.IgnoreCase);
                            DayArray = DayArray.Where(s => s != " " && s != "" && !string.IsNullOrEmpty(s)).ToArray();
                            for (int i = 0; i < FlightsCode.Length; i++)
                            {
                                AirInfo air = new AirInfo();
                                string[] tempstr = DayArray[i]
                                               .Replace("\r\n", string.Empty)
                                               .Replace("\\r\\n", string.Empty)
                                               .TrimStart().TrimEnd()
                                               .Split(new char[] { ' ' }, StringSplitOptions.RemoveEmptyEntries);

                                Res_ThreeCode star_Three = _sqlSugar.Queryable<Res_ThreeCode>().First(a => a.Three == tempstr[3].Substring(0, 3));
                                string starCity = "";
                                if (star_Three != null)
                                {
                                    starCity = star_Three.AirPort;
                                }
                                Res_ThreeCode End_Three = _sqlSugar.Queryable<Res_ThreeCode>().First(a => a.Three == tempstr[3].Substring(3, 3));
                                string EndCity = "";
                                if (End_Three != null)
                                {
                                    EndCity = End_Three.AirPort;
                                }
                                air.Destination = starCity + "/" + EndCity;
                                air.Flight = FlightsCode[i];
                                air.SeatingClass = item.CTypeName;
                                string dateTime = tempstr[2];
                                string DateTemp = dateTime.Substring(2, 5).ToUpper();
                                air.FlightDate = DateTemp;
                                air.DepartureTime = tempstr[5];
                                air.LandingTime = tempstr[6];
                                air.ValidityPeriod = DateTemp + "/" + DateTemp;
                                air.TicketStatus = "--";
                                air.Luggage = "--";
                                air.DepartureTerminal = "--";
                                air.LandingTerminal = "--";
                                airs.Add(air);
                            }
                            int row = 13;
                            for (int i = 0; i < airs.Count; i++)
                            {
                                if (airs.Count > 2)
                                {
                                    for (int j = 0; j < airs.Count - 2; j++)
                                    {
                                        var CopyRow = table.Rows[12].Clone(true);
                                        table.Rows.Add(CopyRow);
                                    }
                                }
                                PropertyInfo[] properties = airs[i].GetType().GetProperties();
                                int index = 0;
                                foreach (PropertyInfo property in properties)
                                {
                                    string value = property.GetValue(airs[i]).ToString();
                                    Cell ishcel0 = table.Rows[row].Cells[index];
                                    Paragraph p = new Paragraph(doc);
                                    string s = value;
                                    p.AppendChild(new Run(doc, s));
                                    p.ParagraphFormat.Alignment = ParagraphAlignment.Center;//水平居中对齐
                                    ishcel0.AppendChild(p);
                                    ishcel0.CellFormat.VerticalAlignment = CellVerticalAlignment.Center;//垂直居中对齐
                                    index++;
                                }
                                row++;

                            }
                            #endregion

                            Paragraph lastParagraph = new Paragraph(doc);
                            //第一个表格末尾加段落
                            table.ParentNode.InsertAfter(lastParagraph, table);
                            //复制第一个表格
                            Table cloneTable = (Table)table.Clone(true);
                            //在文档末尾段落后面加入复制的表格
                            table.ParentNode.InsertAfter(cloneTable, lastParagraph);

                            if (item != _AirTicketReservations[_AirTicketReservations.Count - 1])
                            {
                                int rownewsIndex = 13;
                                for (int i = 0; i < 2; i++)
                                {
                                    var CopyRow = table.Rows[12].Clone(true);
                                    table.Rows.RemoveAt(13);
                                    table.Rows.Add(CopyRow);
                                    rownewsIndex++;
                                }
                            }
                            else
                            {
                                table.Rows.RemoveAt(12);
                            }
                            cloneTable.Rows.RemoveAt(12);
                        }
                        if (_AirTicketReservations.Count != 0)
                        {
                            string[] FlightsCode = _AirTicketReservations[0].FlightsCode.Split('/');
                            if (FlightsCode.Length != 0)
                            {
                                Res_AirCompany _AirCompany = _sqlSugar.Queryable<Res_AirCompany>().First(a => a.ShortCode == FlightsCode[0].Substring(0, 2));
                                if (_AirCompany != null)
                                {
                                    table.Range.Bookmarks["AirlineCompany"].Text = _AirCompany.CnName;
                                }
                                else
                                {
                                    table.Range.Bookmarks["AirlineCompany"].Text = "--";
                                }
                            }
                            table.Range.Bookmarks["AirlineRecordCode"].Text = "--";
                            table.Range.Bookmarks["ReservationRecordCode"].Text = "--";
                            string[] nameArray = Regex.Split(_AirTicketReservations[0].ClientName, "\\d+\\.", RegexOptions.IgnoreCase);
                            nameArray = nameArray.Where(str => str != "" && str != " " && !string.IsNullOrEmpty(str)).ToArray();
                            string name = "";
                            foreach (string clientName in nameArray)
                            {
                                if (!name.Contains(clientName))
                                {
                                    name += clientName + ",";
                                }
                            }
                            if (!string.IsNullOrWhiteSpace(name))
                            {
                                table.Range.Bookmarks["ClientName"].Text = name.Substring(0, name.Length - 1);
                            }
                            else
                            {
                                table.Range.Bookmarks["ClientName"].Text = "--";
                            }
                            table.Range.Bookmarks["TicketNumber"].Text = "--";
                            table.Range.Bookmarks["IdentificationCode"].Text = "--";
                            table.Range.Bookmarks["JointTicket"].Text = "--";
                            table.Range.Bookmarks["TimeIssue"].Text = "--";
                            table.Range.Bookmarks["DrawingAgent"].Text = "--";
                            table.Range.Bookmarks["NavigationCode"].Text = "--";
                            table.Range.Bookmarks["AgentsAddress"].Text = "--";
                            table.Range.Bookmarks["AgentPhone"].Text = "--";
                            table.Range.Bookmarks["AgentFacsimile"].Text = "--";
                        }


                        doc.MailMerge.Execute(new[] { "PageCount" }, new object[] { doc.PageCount });
                        //保存合并后的文档
                        string fileName = "AirItinerary/电子客票中文行程单_CN.docx";
                        string rst = AppSettingsHelper.Get("WordBaseUrl") + AppSettingsHelper.Get("WordFtpPath") + fileName;
                        doc.Save(AppSettingsHelper.Get("WordBasePath") + fileName);

                        return Ok(JsonView(true, "成功！", rst));
                    }
                    else
                    {
                        Document doc = new Document(AppSettingsHelper.Get("WordBasePath") + "Template/电子客票行程单模板_EN.docx");
                        DocumentBuilder builder = new DocumentBuilder(doc);
                        int tableIndex = 0;//表格索引
                                           //得到文档中的第一个表格
                        Table table = (Table)doc.GetChild(NodeType.Table, tableIndex, true);

                        List<string> texts = new List<string>();
                        foreach (var item in _AirTicketReservations)
                        {
                            string[] FlightsCode = item.FlightsCode.Split('/');
                            if (FlightsCode.Length != 0)
                            {
                                Res_AirCompany _AirCompany = _sqlSugar.Queryable<Res_AirCompany>().First(a => a.ShortCode == FlightsCode[0].Substring(0, 2));
                                if (_AirCompany != null)
                                {
                                    if (!transDic.ContainsKey(_AirCompany.CnName))
                                    {
                                        transDic.Add(_AirCompany.CnName, _AirCompany.EnName);
                                    }

                                }
                                else
                                {
                                    if (!transDic.ContainsKey("--"))
                                    {
                                        transDic.Add("--", "--");
                                    }

                                }
                            }
                            string[] nameArray = Regex.Split(item.ClientName, "\\d+\\.", RegexOptions.IgnoreCase);
                            nameArray = nameArray.Where(str => str != "" && str != " " && !string.IsNullOrEmpty(str)).ToArray();
                            string name = "";
                            foreach (string clientName in nameArray)
                            {
                                name += clientName + ",";
                            }
                            if (!texts.Contains(name))
                            {
                                texts.Add(name);
                            }
                            List<AirInfo> airs = new List<AirInfo>();
                            string[] DayArray = Regex.Split(item.FlightsDescription, "\\d+\\.", RegexOptions.IgnoreCase);
                            DayArray = DayArray.Where(s => s != " " && s != "" && !string.IsNullOrEmpty(s)).ToArray();
                            for (int i = 0; i < FlightsCode.Length; i++)
                            {
                                AirInfo air = new AirInfo();
                                string[] tempstr = DayArray[i]
                                               .Replace("\r\n", string.Empty)
                                               .Replace("\\r\\n", string.Empty)
                                               .TrimStart().TrimEnd()
                                               .Split(new char[] { ' ' }, StringSplitOptions.RemoveEmptyEntries);

                                Res_ThreeCode star_Three = _sqlSugar.Queryable<Res_ThreeCode>().First(a => a.Three == tempstr[3].Substring(0, 3));
                                if (star_Three != null)
                                {
                                    if (!transDic.ContainsKey(star_Three.AirPort))
                                    {
                                        transDic.Add(star_Three.AirPort, star_Three.AirPort_En);
                                    }
                                }
                                Res_ThreeCode End_Three = _sqlSugar.Queryable<Res_ThreeCode>().First(a => a.Three == tempstr[3].Substring(3, 3));

                                if (End_Three != null)
                                {
                                    if (!transDic.ContainsKey(End_Three.AirPort))
                                    {
                                        transDic.Add(End_Three.AirPort, End_Three.AirPort_En);
                                    }

                                }
                                if (!texts.Contains(item.CTypeName))
                                {
                                    texts.Add(item.CTypeName);
                                }
                            }
                        }
                        List<TranslateResult> transData = _airTicketResRep.ReTransBatch(texts, "en");
                        if (transData.Count > 0)
                        {
                            foreach (TranslateResult item in transData)
                            {
                                if (!transDic.ContainsKey(item.Query))
                                {

                                    transDic.Add(item.Query, item.Translation);
                                }
                            }
                        }
                        foreach (var item in _AirTicketReservations)
                        {
                            #region 处理固定数据
                            string[] FlightsCode = item.FlightsCode.Split('/');
                            if (FlightsCode.Length != 0)
                            {
                                Res_AirCompany _AirCompany = _sqlSugar.Queryable<Res_AirCompany>().First(a => a.ShortCode == FlightsCode[0].Substring(0, 2));
                                if (_AirCompany != null)
                                {
                                    string str = "--";
                                    string translateResult = transDic.Where(s => s.Key == _AirCompany.CnName).FirstOrDefault().Value;
                                    if (!string.IsNullOrEmpty(translateResult))
                                    {
                                        str = translateResult;
                                        str = _airTicketResRep.Processing(str);
                                    }

                                    table.Range.Bookmarks["AirlineCompany"].Text = str;
                                }
                                else
                                {
                                    table.Range.Bookmarks["AirlineCompany"].Text = "--";
                                }
                            }
                            table.Range.Bookmarks["AirlineRecordCode"].Text = "--";
                            table.Range.Bookmarks["ReservationRecordCode"].Text = "--";
                            string[] nameArray = Regex.Split(item.ClientName, "\\d+\\.", RegexOptions.IgnoreCase);
                            nameArray = nameArray.Where(str => str != "" && str != " " && !string.IsNullOrEmpty(str)).ToArray();
                            string names = "";
                            foreach (string clientName in nameArray)
                            {
                                names += clientName + ",";
                            }
                            if (!string.IsNullOrWhiteSpace(names))
                            {
                                string str = "--";
                                string translateResult = transDic.Where(s => s.Key == names).FirstOrDefault().Value;
                                if (!string.IsNullOrEmpty(translateResult))
                                {
                                    str = translateResult;
                                    str = _airTicketResRep.Processing(str);
                                }

                                table.Range.Bookmarks["ClientName"].Text = str;
                            }
                            else
                            {
                                table.Range.Bookmarks["ClientName"].Text = "--";
                            }
                            table.Range.Bookmarks["TicketNumber"].Text = "--";
                            table.Range.Bookmarks["IdentificationCode"].Text = "--";
                            table.Range.Bookmarks["JointTicket"].Text = "--";
                            table.Range.Bookmarks["TimeIssue"].Text = "--";
                            table.Range.Bookmarks["DrawingAgent"].Text = "--";
                            table.Range.Bookmarks["NavigationCode"].Text = "--";
                            table.Range.Bookmarks["AgentsAddress"].Text = "--";
                            table.Range.Bookmarks["AgentPhone"].Text = "--";
                            table.Range.Bookmarks["AgentFacsimile"].Text = "--";

                            #endregion
                            #region 循环数据处理
                            List<AirInfo> airs = new List<AirInfo>();
                            string[] DayArray = Regex.Split(item.FlightsDescription, "\\d+\\.", RegexOptions.IgnoreCase);
                            DayArray = DayArray.Where(s => s != " " && s != "" && !string.IsNullOrEmpty(s)).ToArray();
                            for (int i = 0; i < FlightsCode.Length; i++)
                            {
                                AirInfo air = new AirInfo();
                                string[] tempstr = DayArray[i]
                                               .Replace("\r\n", string.Empty)
                                               .Replace("\\r\\n", string.Empty)
                                               .TrimStart().TrimEnd()
                                               .Split(new char[] { ' ' }, StringSplitOptions.RemoveEmptyEntries);

                                Res_ThreeCode star_Three = _sqlSugar.Queryable<Res_ThreeCode>().First(a => a.Three == tempstr[3].Substring(0, 3));
                                string starCity = "";
                                if (star_Three != null)
                                {
                                    string str2 = "--";
                                    string translateResult2 = transDic.Where(s => s.Key == star_Three.AirPort).FirstOrDefault().Value;
                                    if (!string.IsNullOrEmpty(translateResult2))
                                    {
                                        str2 = translateResult2;
                                        str2 = _airTicketResRep.Processing(str2);
                                    }
                                    starCity = str2;
                                }
                                Res_ThreeCode End_Three = _sqlSugar.Queryable<Res_ThreeCode>().First(a => a.Three == tempstr[3].Substring(3, 3));
                                string EndCity = "";
                                if (End_Three != null)
                                {
                                    string str1 = "--";
                                    string translateResult1 = transDic.Where(s => s.Key == End_Three.AirPort).FirstOrDefault().Value;
                                    if (!string.IsNullOrEmpty(translateResult1))
                                    {
                                        str1 = translateResult1;
                                        str1 = _airTicketResRep.Processing(str1);
                                    }
                                    EndCity = str1;
                                }
                                air.Destination = starCity + "/" + EndCity;
                                air.Flight = FlightsCode[i];
                                string str = "--";
                                string translateResult = transDic.Where(s => s.Key == item.CTypeName).FirstOrDefault().Value;
                                if (!string.IsNullOrEmpty(translateResult))
                                {
                                    str = translateResult;
                                    str = _airTicketResRep.Processing(str);
                                }
                                air.SeatingClass = str;
                                string dateTime = tempstr[2];
                                string DateTemp = dateTime.Substring(2, 5).ToUpper();
                                air.FlightDate = DateTemp;
                                air.DepartureTime = tempstr[5];
                                air.LandingTime = tempstr[6];
                                air.ValidityPeriod = DateTemp + "/" + DateTemp;
                                air.TicketStatus = "--";
                                air.Luggage = "--";
                                air.DepartureTerminal = "--";
                                air.LandingTerminal = "--";
                                airs.Add(air);
                            }
                            int row = 13;
                            for (int i = 0; i < airs.Count; i++)
                            {
                                if (airs.Count > 2)
                                {
                                    for (int j = 0; j < airs.Count - 2; j++)
                                    {
                                        var CopyRow = table.Rows[12].Clone(true);
                                        table.Rows.Add(CopyRow);
                                    }
                                }
                                PropertyInfo[] properties = airs[i].GetType().GetProperties();
                                int index = 0;
                                foreach (PropertyInfo property in properties)
                                {
                                    string value = property.GetValue(airs[i]).ToString();
                                    Cell ishcel0 = table.Rows[row].Cells[index];
                                    Paragraph p = new Paragraph(doc);
                                    string s = value;
                                    p.AppendChild(new Run(doc, s));
                                    p.ParagraphFormat.Alignment = ParagraphAlignment.Center;//水平居中对齐
                                    ishcel0.AppendChild(p);
                                    ishcel0.CellFormat.VerticalAlignment = CellVerticalAlignment.Center;//垂直居中对齐
                                    //ishcel0.CellFormat.VerticalAlignment=
                                    index++;
                                }
                                row++;

                            }
                            #endregion

                            Paragraph lastParagraph = new Paragraph(doc);
                            //第一个表格末尾加段落
                            table.ParentNode.InsertAfter(lastParagraph, table);
                            //复制第一个表格
                            Table cloneTable = (Table)table.Clone(true);
                            //在文档末尾段落后面加入复制的表格
                            table.ParentNode.InsertAfter(cloneTable, lastParagraph);

                            if (item != _AirTicketReservations[_AirTicketReservations.Count - 1])
                            {
                                int rownewsIndex = 13;
                                for (int i = 0; i < 2; i++)
                                {
                                    var CopyRow = table.Rows[12].Clone(true);
                                    table.Rows.RemoveAt(13);
                                    table.Rows.Add(CopyRow);
                                    rownewsIndex++;
                                }
                            }
                            else
                            {
                                table.Rows.RemoveAt(12);
                            }
                            cloneTable.Rows.RemoveAt(12);
                        }
                        if (_AirTicketReservations.Count != 0)
                        {
                            string[] FlightsCode = _AirTicketReservations[0].FlightsCode.Split('/');
                            if (FlightsCode.Length != 0)
                            {
                                Res_AirCompany _AirCompany = _sqlSugar.Queryable<Res_AirCompany>().First(a => a.ShortCode == FlightsCode[0].Substring(0, 2));
                                if (_AirCompany != null)
                                {
                                    string str = "--";
                                    string translateResult = transDic.Where(s => s.Key == _AirCompany.CnName).FirstOrDefault().Value;
                                    if (!string.IsNullOrEmpty(translateResult))
                                    {
                                        str = translateResult;
                                        str = _airTicketResRep.Processing(str);
                                    }

                                    table.Range.Bookmarks["AirlineCompany"].Text = str;
                                }
                                else
                                {
                                    table.Range.Bookmarks["AirlineCompany"].Text = "--";
                                }
                            }
                            table.Range.Bookmarks["AirlineRecordCode"].Text = "--";
                            table.Range.Bookmarks["ReservationRecordCode"].Text = "--";
                            string[] nameArray = Regex.Split(_AirTicketReservations[0].ClientName, "\\d+\\.", RegexOptions.IgnoreCase);
                            nameArray = nameArray.Where(str => str != "" && str != " " && !string.IsNullOrEmpty(str)).ToArray();
                            string names = "";
                            foreach (string clientName in nameArray)
                            {
                                names += clientName + ",";
                            }
                            if (!string.IsNullOrWhiteSpace(names))
                            {
                                string str = "--";
                                string translateResult = transDic.Where(s => s.Key == names).FirstOrDefault().Value;
                                if (!string.IsNullOrEmpty(translateResult))
                                {
                                    str = translateResult;
                                    str = _airTicketResRep.Processing(str);
                                }

                                table.Range.Bookmarks["ClientName"].Text = str;
                            }
                            else
                            {
                                table.Range.Bookmarks["ClientName"].Text = "--";
                            }
                            table.Range.Bookmarks["TicketNumber"].Text = "--";
                            table.Range.Bookmarks["IdentificationCode"].Text = "--";
                            table.Range.Bookmarks["JointTicket"].Text = "--";
                            table.Range.Bookmarks["TimeIssue"].Text = "--";
                            table.Range.Bookmarks["DrawingAgent"].Text = "--";
                            table.Range.Bookmarks["NavigationCode"].Text = "--";
                            table.Range.Bookmarks["AgentsAddress"].Text = "--";
                            table.Range.Bookmarks["AgentPhone"].Text = "--";
                            table.Range.Bookmarks["AgentFacsimile"].Text = "--";
                        }


                        doc.MailMerge.Execute(new[] { "PageCount" }, new object[] { doc.PageCount });
                        //保存合并后的文档
                        string fileName = "AirItinerary/电子客票英文行程单_EN.docx";
                        string rst = AppSettingsHelper.Get("WordBaseUrl") + AppSettingsHelper.Get("WordFtpPath") + fileName;
                        doc.Save(AppSettingsHelper.Get("WordBasePath") + fileName);

                        return Ok(JsonView(true, "成功！", rst));
                    }
                }

            }
            catch (Exception ex)
            {
                return Ok(JsonView(false, "程序错误！"));
                throw;
            }
        }
        #endregion

        #region 团组增减款项
        /// <summary>
        /// 团组增减款项下拉框绑定
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> DecreasePaymentsSelect(DecreasePaymentsDto dto)
        {
            try
            {
                Result groupData = await _decreasePaymentsRep.DecreasePaymentsSelect(dto);
                if (groupData.Code != 0)
                {
                    return Ok(JsonView(false, groupData.Msg));
                }
                return Ok(JsonView(true, groupData.Msg, groupData.Data));
            }
            catch (Exception ex)
            {
                return Ok(JsonView(false, "程序错误！"));
                throw;
            }
        }
        /// <summary>
        /// 根据团组Id查询团组增减款项
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> DecreasePaymentsList(DecreasePaymentsListDto dto)
        {
            try
            {
                Result groupData = await _decreasePaymentsRep.DecreasePaymentsList(dto);
                if (groupData.Code != 0)
                {
                    return Ok(JsonView(false, groupData.Msg));
                }
                return Ok(JsonView(true, groupData.Msg, groupData.Data));
            }
            catch (Exception ex)
            {
                return Ok(JsonView(false, "程序错误！"));
                throw;
            }
        }
        /// <summary>
        /// 团组增减款项操作（Status:1.新增,2.修改）
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> OpDecreasePayments(DecreasePaymentsOpDto dto)
        {
            try
            {
                Result groupData = await _decreasePaymentsRep.OpDecreasePayments(dto);
                if (groupData.Code != 0)
                {
                    return Ok(JsonView(false, groupData.Msg));
                }
                return Ok(JsonView(true, groupData.Msg, groupData.Data));
            }
            catch (Exception ex)
            {
                return Ok(JsonView(false, "程序错误！"));
                throw;
            }
        }
        /// <summary>
        /// 团组增减款项操作 删除
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> DelDecreasePayments(DelBaseDto dto)
        {
            try
            {
                var res = await _decreasePaymentsRep.SoftDeleteByIdAsync<Grp_DecreasePayments>(dto.Id.ToString(), dto.DeleteUserId);
                if (!res)
                {
                    return Ok(JsonView(false, "删除失败"));
                }
                return Ok(JsonView(true, "删除成功！"));
            }
            catch (Exception ex)
            {
                return Ok(JsonView(false, "程序错误！"));
                throw;
            }
        }

        /// <summary>
        /// 根据团组增减款项Id查询
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> QueryDecreasePaymentsById(DecreasePaymentsByIdDto dto)
        {
            try
            {
                Result groupData = await _decreasePaymentsRep.QueryDecreasePaymentsById(dto);
                if (groupData.Code != 0)
                {
                    return Ok(JsonView(false, groupData.Msg));
                }
                return Ok(JsonView(true, groupData.Msg, groupData.Data));
            }
            catch (Exception ex)
            {
                return Ok(JsonView(false, "程序错误！"));
                throw;
            }
        }



        #endregion

        #region 文件上传、删除
        /// <summary>
        /// region 文件上传  可以带参数
        /// </summary>
        /// <param name="file"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> UploadProject(IFormFile file)
        {
            try
            {
                var TypeName = Request.Headers["TypeName"].ToString();
                if (file != null)
                {
                    var fileDir = AppSettingsHelper.Get("GrpFileBasePath");
                    //文件名称
                    string projectFileName = file.FileName;

                    //上传的文件的路径
                    string filePath = "";
                    if (TypeName == "A")//A代表团组增减款项
                    {

                        if (!Directory.Exists(fileDir))
                        {
                            Directory.CreateDirectory(fileDir);
                        }

                        //上传的文件的路径
                        filePath = fileDir + $@"\团组增减款项相关文件\{projectFileName}";

                    }
                    else if (TypeName == "B")//B代表商邀相关文件
                    {
                        if (!Directory.Exists(fileDir))
                        {
                            Directory.CreateDirectory(fileDir);
                        }

                        //上传的文件的路径
                        filePath = fileDir + $@"\商邀相关文件\{projectFileName}";
                    }
                    using (FileStream fs = System.IO.File.Create(filePath))
                    {
                        file.CopyTo(fs);
                        fs.Flush();
                    }
                    return Ok(JsonView(true, "上传成功！", projectFileName));
                }
                else
                {
                    return Ok(JsonView(false, "上传失败！"));
                }
            }
            catch (Exception ex)
            {
                return Ok(JsonView(false, "程序错误！"));
                throw;
            }

        }
        /// <summary>
        /// 删除指定文件
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> DelFile(DelFileDto dto)
        {
            try
            {
                var TypeName = Request.Headers["TypeName"].ToString();
                string filePath = "";
                var fileDir = AppSettingsHelper.Get("GrpFileBasePath");
                // 返回与指定虚拟路径相对应的物理路径即绝对路径
                int id = 0;
                if (TypeName == "A")
                {
                    filePath = fileDir + "/团组增减款项相关文件/" + dto.fileName;
                    // 删除该文件
                    System.IO.File.Delete(filePath);
                    id = await _sqlSugar.Updateable<Grp_DecreasePayments>().Where(a => a.Id == dto.Id).SetColumns(a => new Grp_DecreasePayments { FilePath = "" }).ExecuteCommandAsync();

                }
                else if (TypeName == "B")
                {
                    filePath = fileDir + "/商邀相关文件/" + dto.fileName;
                    // 删除该文件
                    System.IO.File.Delete(filePath);
                    id = await _sqlSugar.Updateable<Grp_InvitationOfficialActivities>().Where(a => a.Id == dto.Id).SetColumns(a => new Grp_InvitationOfficialActivities { Attachment = "" }).ExecuteCommandAsync();
                }
                if (id != 0)
                {
                    return Ok(JsonView(true, "成功！"));
                }
                else
                {
                    return Ok(JsonView(false, "失败！"));
                }
            }
            catch (Exception ex)
            {
                return Ok(JsonView(false, "程序错误！"));
                throw;
            }

        }
        #endregion

        #region 商邀费用录入
        /// <summary>
        /// 根据团组Id查询商邀费用列表
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> InvitationOfficialActivitiesList(InvitationOfficialActivitiesListDto dto)
        {
            try
            {
                Result groupData = await _InvitationOfficialActivitiesRep.InvitationOfficialActivitiesList(dto);
                if (groupData.Code != 0)
                {
                    return Ok(JsonView(false, groupData.Msg));
                }
                return Ok(JsonView(true, groupData.Msg, groupData.Data));
            }
            catch (Exception ex)
            {
                return Ok(JsonView(false, "程序错误！"));
                throw;
            }
        }
        /// <summary>
        /// 根据商邀费用ID查询C表和商邀费用数据
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)] 
        public async Task<IActionResult> InvitationOfficialActivitiesById(InvitationOfficialActivitiesByIdDto dto)
        {
            try
            {
                Result groupData = await _InvitationOfficialActivitiesRep.InvitationOfficialActivitiesById(dto);
                if (groupData.Code != 0)
                {
                    return Ok(JsonView(false, groupData.Msg));
                }
                return Ok(JsonView(true, groupData.Msg, groupData.Data));
            }
            catch (Exception ex)
            {
                return Ok(JsonView(false, "程序错误！"));
                throw;
            }
        }
        /// <summary>
        /// 商邀费用录入操作（Status:1.新增,2.修改）
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> OpInvitationOfficialActivities(OpInvitationOfficialActivitiesDto dto)
        {
            try
            {
                Result groupData = await _InvitationOfficialActivitiesRep.OpInvitationOfficialActivities(dto);
                if (groupData.Code != 0)
                { 
                    return Ok(JsonView(false, groupData.Msg));
                }
                return Ok(JsonView(true, groupData.Msg, groupData.Data));
            }
            catch (Exception ex)
            {
                return Ok(JsonView(false, "程序错误！"));
                throw;
            }
        }
        /// <summary>
        /// 商邀删除
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> DelInvitationOfficialActivities(DelBaseDto dto)
        {
            try
            {
                var res = await _InvitationOfficialActivitiesRep.SoftDeleteByIdAsync<Grp_InvitationOfficialActivities>(dto.Id.ToString(), dto.DeleteUserId);
                if (!res)
                {
                    return Ok(JsonView(false, "删除失败"));
                }
                return Ok(JsonView(true, "删除成功！"));
            }
            catch (Exception ex)
            {
                return Ok(JsonView(false, "程序错误！"));
                throw;
            }
        }

        #endregion

        #region 团组英文资料
        /// <summary>
        /// 查询团组英文所有资料
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> QueryDelegationEnData(QueryDelegationEnDataDto dto)
        {
            try
            {
                Result groupData = await _delegationEnDataRep.QueryDelegationEnData(dto);
                if (groupData.Code != 0)
                {
                    return Ok(JsonView(false, groupData.Msg));
                }
                return Ok(JsonView(true, groupData.Msg, groupData.Data));
            }
            catch (Exception ex)
            {
                return Ok(JsonView(false, "程序错误！"));
                throw;
            }
        }
        /// <summary>
        /// 团组英文资料操作（Status:1.新增,2.修改）
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> OpDelegationEnData(OpDelegationEnDataDto dto)
        {
            try
            {
                Result groupData = await _delegationEnDataRep.OpDelegationEnData(dto);
                if (groupData.Code != 0)
                {
                    return Ok(JsonView(false, groupData.Msg));
                }
                return Ok(JsonView(true, groupData.Msg, groupData.Data));
            }
            catch (Exception ex)
            {
                return Ok(JsonView(false, "程序错误！"));
                throw;
            }
        }
        /// <summary>
        /// 团组英文资料Id查询数据
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> QueryDelegationEnDataById(QueryDelegationEnDataByIdDto dto)
        {
            try
            {
                Grp_DelegationEnData _DelegationEnData=await _sqlSugar.Queryable<Grp_DelegationEnData>().FirstAsync(it => it.Id==dto.Id && it.IsDel==0);
                if (_DelegationEnData != null)
                {
                    return Ok(JsonView(true, "查询成功！", _DelegationEnData));
                }
                return Ok(JsonView(true, "暂无数据！",_DelegationEnData));
            }
            catch (Exception ex)
            {
                return Ok(JsonView(false, "程序错误！"));
                throw;
            }
        }
        /// <summary>
        /// 团组英文资料删除
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> DelDelegationEnData(DelBaseDto dto)
        {
            try
            {
                var res = await _delegationEnDataRep.SoftDeleteByIdAsync<Grp_DelegationEnData>(dto.Id.ToString(), dto.DeleteUserId);
                if (!res)
                {
                    return Ok(JsonView(false, "删除失败"));
                }
                return Ok(JsonView(true, "删除成功！"));
            }
            catch (Exception ex)
            {
                return Ok(JsonView(false, "程序错误！"));
                throw;
            }
        }
        #endregion

        #region 导出邀请函
        /// <summary>
        /// 导出邀请函页面初始化
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> QueryinvitationLetter(DecreasePaymentsListDto dto)
        {
            try
            {
                List<Grp_DelegationInfo> grp_Delegations = await _sqlSugar.Queryable<Grp_DelegationInfo>().Where(a => a.IsDel==0).OrderBy(a=>a.Id,OrderByType.Desc).ToListAsync();
                List<Crm_DeleClient> crm_Deles = new List<Crm_DeleClient>();
                if (dto.DiId==0)
                {
                   crm_Deles = await _sqlSugar.Queryable<Crm_DeleClient>().Where(a => a.DiId==grp_Delegations[0].Id && a.IsDel==0).ToListAsync();
                }
                else
                {
                   crm_Deles = await _sqlSugar.Queryable<Crm_DeleClient>().Where(a => a.DiId==dto.DiId && a.IsDel==0).ToListAsync();
                }
                
                return Ok(JsonView(true, "查询成功！",new
                {
                    deleClient = crm_Deles,
                    delegations= grp_Delegations
                }));
            }
            catch (Exception ex)
            {
                return Ok(JsonView(false, "程序错误！"));
                throw;
            }
        }
        /// <summary>
        /// 导出邀请函
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> invitationLetter(DecreasePaymentsListDto dto)
        {
            try
            {
                Dictionary<string, string> transDic = new Dictionary<string, string>();
                List<Crm_DeleClient> crm_DeleClients = await _sqlSugar.Queryable<Crm_DeleClient>().Where(a => a.DiId==dto.DiId && a.IsDel==0).ToListAsync();
                List<string> texts = new List<string>();
                if (crm_DeleClients.Count!=0)
                {

                foreach (Crm_DeleClient item in crm_DeleClients)
                {

                    if (!string.IsNullOrWhiteSpace(item.Pinyin))
                    {
                        transDic.Add(item.LastName+item.FirstName, item.Pinyin);
                    }
                    else
                    {
                        string name = item.LastName + item.FirstName;
                        texts.Add(name);
                    }

                    if (!string.IsNullOrEmpty(item.Job) && !texts.Contains(item.Job))
                    {
                        if (!transDic.ContainsKey(item.Job))
                        {
                            texts.Add(item.Job);
                        }
                    }
                    if (item.CrmCompanyId!=0)
                    {
                        Crm_CustomerCompany crm_Customer = await _sqlSugar.Queryable<Crm_CustomerCompany>().FirstAsync(a => a.Id==item.CrmCompanyId && a.IsDel==0);
                        if (!transDic.ContainsKey(crm_Customer.CompanyName))
                        {
                            texts.Add(crm_Customer.CompanyName);
                        }
                    }
                }
                List<TranslateResult> transData = _airTicketResRep.ReTransBatch(texts, "en");
                if (transData.Count > 0)
                {
                    foreach (TranslateResult item in transData)
                    {
                        if (!transDic.ContainsKey(item.Query))
                        {

                            transDic.Add(item.Query, item.Translation);
                        }
                    }
                }
                List<guestList> list = new List<guestList>();
                foreach (Crm_DeleClient dele in crm_DeleClients)
                {
                    guestList guestList = new guestList();

                    if (!string.IsNullOrWhiteSpace(dele.Pinyin))
                    {
                        guestList.Name = dele.Pinyin;
                    }
                    else
                    {
                        string Name = transDic.Where(s => s.Key == dele.LastName + dele.FirstName).FirstOrDefault().Value;
                        guestList.Name = Name;
                    }
                    if (dele.Sex ==0)
                    {
                        guestList.Sex = "Male";
                    }
                    else if (dele.Sex == 1)
                    {
                        guestList.Sex = "Female";
                    }
                    guestList.DOB = dele.BirthDay.Replace('-', '.');
                    Crm_CustomerCompany crm_Customer = await _sqlSugar.Queryable<Crm_CustomerCompany>().FirstAsync(a => a.Id==dele.CrmCompanyId && a.IsDel==0);
                    string jobName = transDic.Where(s => s.Key == crm_Customer.CompanyName).FirstOrDefault().Value + " "+transDic.Where(s => s.Key == dele.Job).FirstOrDefault().Value;

                    guestList.Job = jobName;
                    list.Add(guestList);
                }

                //载入模板
                Document doc = new Document(AppSettingsHelper.Get("WordBasePath") + "Template/邀请函模板0210.docx");
                DocumentBuilder builder = new DocumentBuilder(doc);

                //获取word里所有表格
                NodeCollection allTables = doc.GetChildNodes(NodeType.Table, true);

                //获取所填表格的序数
                Aspose.Words.Tables.Table tableOne = allTables[0] as Aspose.Words.Tables.Table;

                var rowStart = tableOne.Rows[0]; //获取第1行

                //循环赋值
                for (int i = 0; i < list.Count; i++)
                {
                    builder.MoveToCell(0, i + 1, 0, 0);
                    builder.Write(list[i].Name.ToString());

                    builder.MoveToCell(0, i + 1, 1, 0);
                    builder.Write(list[i].Sex.ToString());

                    builder.MoveToCell(0, i + 1, 2, 0);
                    builder.Write(list[i].DOB.ToString());

                    builder.MoveToCell(0, i + 1, 3, 0);
                    builder.Write(list[i].Job.ToString());
                }


                //删除多余行
                while (tableOne.Rows.Count > list.Count + 1)
                {
                    tableOne.Rows.RemoveAt(list.Count + 1);
                }
                var fileDir = AppSettingsHelper.Get("GrpFileBasePath");
                string fileName = "邀请函"+DateTime.Now.ToString("yyyy-MM-dd")+".docx";
                string filePath = fileDir + $@"商邀相关文件/{fileName}";
                doc.Save(filePath);
                string Url= AppSettingsHelper.Get("WordBaseUrl") + "Office/GrpFile/商邀相关文件"+fileName;
                return Ok(JsonView(true, "成功！", Url));

                }
                else
                {
                    return Ok(JsonView(false, "失败，该团组没有客户名单！"));
                }
            }
            catch (Exception ex)
            {
                return Ok(JsonView(false, "程序错误！"));
                throw;
            }
        }
        #endregion

        #region 团组经理模块 出入境费用
        ///// <summary>
        ///// 团组模块 - 出入境费用
        ///// </summary>
        ///// <returns></returns>
        //[HttpPost]
        //[ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        //public async Task<IActionResult> SetEnterExitCostCurrencyChange()
        //{
        //    try
        //    {
        //        var data = await _enterExitCostRep.SetEnterExitCostCurrencyChange();

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

        //        return Ok(JsonView(true, "查询成功！"));
        //    }
        //    catch (Exception ex)
        //    {
        //        return Ok(JsonView(false, ex.Message));
        //        throw;
        //    }
        //}

        /// <summary>
        /// 团组模块 - 出入境费用 - 子项 地区更改为 nationalTravelFee 的id
        /// </summary>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> SetDayAndCostAreaChange()
        {
            try
            {
                var nationalTravelFee = await _sqlSugar.Queryable<Grp_NationalTravelFee>().ToListAsync();
                var dayAndCost = await _sqlSugar.Queryable<Grp_DayAndCost>().Where(it => it.IsDel == 0).ToListAsync();

                var unite = dayAndCost.Where(a => nationalTravelFee.Any(b => a.Place.Trim() == b.City)).ToList(); //交集

                var merge = dayAndCost.Where(a =>! nationalTravelFee.Any(b => a.Place.Trim() == b.City)).ToList(); //差集

                foreach (var item in unite) //处理交集数据
                {
                    dayAndCost.Where(it => it.Id == item.Id).FirstOrDefault().NationalTravelFeeId = nationalTravelFee.Where(it => it.City.Trim() == item.Place.Trim()).FirstOrDefault().Id;
                }

                foreach (var item in merge) //处理差集数据
                {
                    int nationalTravelFeeId = 0;

                    var cityData = nationalTravelFee.Where(it => it.City.Trim() == item.Place.Trim()).FirstOrDefault();

                    if (cityData != null) nationalTravelFeeId = cityData.Id;
                    else
                    {
                        var countryData = nationalTravelFee.Where(it => it.Country.Trim() == item.Place.Trim()).FirstOrDefault();
                        if (countryData != null) nationalTravelFeeId = countryData.Id;
                    }

                    dayAndCost.Where(it => it.Id == item.Id).FirstOrDefault().NationalTravelFeeId = nationalTravelFeeId;
                }

                //只更新dayAndCost 的 nationalTravelFeeId;
                var result = _sqlSugar.Updateable(dayAndCost).UpdateColumns(it => new { it.NationalTravelFeeId }).ExecuteCommand();

                if (result > 0) return Ok(JsonView(true, "nationalTravelFeeId列更新成功!"));
                else return Ok(JsonView(false, "nationalTravelFeeId列更新失败!"));

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


        /// <summary>
        /// 团组模块 - 出入境费用 - 基础数据源(团组名称/币种类型)
        /// </summary>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> GetEnterExitCostDataSource(PortDtoBase dto)
        {
            try
            {
                var groupNameData = await _groupRepository.GetGroupNameList(new GroupNameDto { PortType = dto.PortType });

                string currencySql = string.Format("Select * From Sys_SetData Where STid = 66 And IsDel = 0");
                var currencyData = await _sqlSugar.SqlQueryable<SetDataInfoView>(currencySql).ToListAsync();


                //string nationalTravelFeeSql = string.Format(@"Select ssd.Name as CurrencyCode, ssd.Remark as CurrencyName,su.CnName as LastUpdateUserName,gntf.* From Grp_NationalTravelFee gntf
                //                                                Left Join Sys_SetData ssd On ssd.STid = 66 And gntf.Currency = ssd.Id
                //                                                Left Join Sys_Users su On gntf.LastUpdateUserId = su.Id 
                //                                                Where gntf.Isdel = 0");
                //var nationalTravelFeeData = await _sqlSugar.SqlQueryable<NationalTravelFeeInfoView>(nationalTravelFeeSql).ToListAsync();


                return Ok(JsonView(true, "查询成功！",new {
                    GroupNameData = groupNameData.Data,
                    CurrencyData = currencyData,
                    //NationalTravelFeeData = nationalTravelFeeData
                }));
            }
            catch (Exception ex)
            {
                return Ok(JsonView(false, ex.Message));
                throw;
            }
        }

        /// <summary>
        /// 团组模块 - 出入境费用 - Info
        /// </summary>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> GetEnterExitCostInfobyDiId(EnterExitCostInfobyDiIdDto dto)
        {
            try
            {
                var data = await _enterExitCostRep.GetEnterExitCostInfoByDiId(dto);

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

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

        /// <summary>
        /// 团组模块 - 出入境费用 - Add And Update
        /// </summary>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> PostEnterExitCostOperate(EnterExitCostOperateDto dto)
        {
            try
            {
                var data = await _enterExitCostRep.PostEnterExitCostOperate(dto);

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

                return Ok(JsonView(true, data.Msg, data.Data));
            }
            catch (Exception ex)
            {
                return Ok(JsonView(false, ex.Message));
            }
        }
        

        /// <summary>
        /// 团组模块 - 出入境费用 - 子项删除
        /// </summary>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> PostEnterExitCostSubItemDel(EnterExitCostSubItemDelDto dto)
        {
            try
            {
                var data = await _enterExitCostRep.PostEnterExitCostSubItemDel(dto);

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

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

        /// <summary>
        /// 团组模块 - 出入境国家费用标准 List
        /// </summary>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> GetNationalTravelFeeData(PortDtoBase dto)
        {
            try
            {
                Stopwatch sw = new Stopwatch();
                sw.Start();
                string nationalTravelFeeSql = string.Format(@"Select ssd.Name as CurrencyCode, ssd.Remark as CurrencyName,su.CnName as LastUpdateUserName,gntf.* From Grp_NationalTravelFee gntf
                                                                Left Join Sys_SetData ssd On ssd.STid = 66 And gntf.Currency = ssd.Id
                                                                Left Join Sys_Users su On gntf.LastUpdateUserId = su.Id 
                                                                Where gntf.Isdel = 0");
                var nationalTravelFeeData = await _sqlSugar.SqlQueryable<NationalTravelFeeInfoView>(nationalTravelFeeSql).ToListAsync();

                var nationalTravel = nationalTravelFeeData.GroupBy(it => it.Country).Select(it1 => it1.FirstOrDefault());

                List<dynamic> nationalTravelFeeData1 = new List<dynamic>();

                foreach (var item in nationalTravel)
                {
                    var cityData = nationalTravelFeeData.Where(it => it.Country == item.Country).ToList();
                    var otherData = cityData.Where(it => it.City.Contains("其他城市")).FirstOrDefault();
                    if (otherData != null)
                    {
                        cityData.Remove(otherData);
                        cityData.Add(otherData);
                    }
                    

                    nationalTravelFeeData1.Add(new
                    {
                        Country = item.Country,
                        CityData = cityData
                    });
                }

                sw.Stop();
                return Ok(JsonView(true, "查询成功！耗时:"+ sw.ElapsedMilliseconds+"ms", nationalTravelFeeData1));
            }
            catch (Exception ex)
            {
                return Ok(JsonView(false, ex.Message));
                throw;
            }
        }


        /// <summary>
        /// 团组模块 - 出入境国家费用标准 - Add Or Update
        /// </summary>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> PostNationalTravelFeeOperate(NationalTravelFeeOperateDto dto)
        {
            try
            {
                var data = await _enterExitCostRep.PostNationalTravelFeeOperate(dto);

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

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


        #endregion
    }
}