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;
using MathNet.Numerics.Statistics.Mcmc;
using AlibabaCloud.OpenApiClient.Models;
using System;
using NPOI.HPSF;
using SqlSugar;
using System.Collections;
using Org.BouncyCastle.Ocsp;
using System.Globalization;
using static QRCoder.PayloadGenerator;
using Bookmark = Aspose.Words.Bookmark;
using Aspose.Words.Fields;
using NPOI.POIFS.FileSystem;
using Microsoft.AspNetCore.Mvc.ViewEngines;
using OASystem.Domain.ViewModels.QiYeWeChat;

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 DelegationVisaRepository _delegationVisaRep;
        private readonly VisaPriceRepository _visaPriceRep;
        private readonly CarTouristGuideGroundRepository _carTouristGuideGroundRep;
        private readonly HotelPriceRepository _hotelPriceRep;
        private readonly CustomersRepository _customersRep;
        private readonly MessageRepository _message;
        private readonly SqlSugarClient _sqlSugar;
        #region 成本相关
        private readonly CheckBoxsRepository _checkBoxs;
        private readonly GroupCostRepository _GroupCostRepository;
        private readonly CostTypeHotelNumberRepository _CostTypeHotelNumberRepository;
        private readonly GroupCostParameterRepository _GroupCostParameterRepository;
        #endregion

        private readonly SetDataRepository _setDataRep;
        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
            , DelegationVisaRepository delegationVisaRep, MessageRepository message, VisaPriceRepository visaPriceRep, CarTouristGuideGroundRepository carTouristGuideGroundRep,
            CheckBoxsRepository checkBoxs, GroupCostRepository GroupCostRepository, CostTypeHotelNumberRepository CostTypeHotelNumberRepository,
            GroupCostParameterRepository GroupCostParameterRepository, HotelPriceRepository hotelPriceRep, CustomersRepository customersRep, SetDataRepository setDataRep)
        {
            _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;
            _delegationVisaRep = delegationVisaRep;
            _message = message;
            _visaPriceRep = visaPriceRep;
            _carTouristGuideGroundRep = carTouristGuideGroundRep;
            _checkBoxs = checkBoxs;
            _GroupCostRepository = GroupCostRepository;
            _CostTypeHotelNumberRepository = CostTypeHotelNumberRepository;
            _GroupCostParameterRepository = GroupCostParameterRepository;
            _hotelPriceRep = hotelPriceRep;
            _customersRep = customersRep;
            _setDataRep = setDataRep;
        }

        #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>
        ///  接团信息列表 Page
        /// </summary>
        /// <param name="dto">团组列表请求dto</param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> PostGroupPageList(GroupPageListDto dto)
        {
            #region  参数验证
            if (dto.UserId < 1) return Ok(JsonView(false, "员工Id为空"));
            if (dto.PageId < 1) return Ok(JsonView(false, "页面Id为空"));

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

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

            #endregion

            if (dto.PortType == 1 || dto.PortType == 2) // web/Android
            {
                string sqlWhere = string.Empty;
                if (dto.IsSure == 0) //未完成
                {
                    sqlWhere += string.Format(@" And IsSure = 0");
                }
                else if (dto.IsSure == 1) //已完成
                {
                    sqlWhere += string.Format(@" And IsSure = 1");
                }

                if (!string.IsNullOrEmpty(dto.SearchCriteria))
                {
                    string tj = dto.SearchCriteria;
                    sqlWhere += string.Format(@"And (ssd.Name Like '%{0}%' Or TeamName Like '%{1}%' Or ClientName Like '%{2}%' Or  ClientName Like '%{3}%' Or su.CnName  Like '%{4}%')",
                       tj, tj, tj, tj, tj);
                }

                string sql = string.Format(@"Select Row_Number,Id,SalesQuoteNo,TourCode,TeamTypeId, TeamType,
					                              TeamLevId,TeamLev,TeamName,ClientName,ClientUnit,
					                              VisitDate,VisitDays,VisitPNumber,JietuanOperator,IsSure,CreateTime
					                              From (
					                              Select row_number() over(order by gdi.CreateTime Desc) as Row_Number,
					                              gdi.Id,SalesQuoteNo,TourCode,ssd.Id TeamTypeId, ssd.Name TeamType,
					                              ssd1.Id TeamLevId,ssd1.Name TeamLev,TeamName,ClientName,ClientUnit,
					                              VisitDate,VisitDays,VisitPNumber,su.CnName JietuanOperator,IsSure,gdi.CreateTime
					                              From  Grp_DelegationInfo gdi
					                              Inner Join Sys_SetData ssd On gdi.TeamDid = ssd.Id 
					                              Inner Join Sys_SetData ssd1 On gdi.TeamLevSId = ssd1.Id
					                              Left Join Sys_Users su On gdi.JietuanOperator = su.Id
					                              Where gdi.IsDel = 0  {0}
				                              ) temp  ", sqlWhere);

                RefAsync<int> total = 0;//REF和OUT不支持异步,想要真的异步这是最优解
                var _DelegationList = await _sqlSugar.SqlQueryable<DelegationListView>(sql).ToPageListAsync(dto.PageIndex, dto.PageSize, total);//ToPageAsync

                var _view = new
                {
                    PageFuncAuth = pageFunAuthView,
                    Data = _DelegationList
                };
                return Ok(JsonView(true, "查询成功!", _view, total));


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

        /// <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(true, "操作成功!", groupData.Data));
        }



        /// <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));
        }
        /// <summary>
        /// 根据CTable类型返回对应的团组名称及简单数据（APP端）
        /// </summary>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> PostGroupNameAndEasy(DecreasePaymentsDto dto)
        {
            try
            {
                Result groupData = await _decreasePaymentsRep.PostGroupNameAndEasy(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>
        ///  根据团组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));
        }


        /// <summary>
        ///  IOS获取团组签证拍照上传进度01（团组列表）
        /// </summary>
        /// <param name="dto">请求dto</param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]

        public async Task<IActionResult> PostIOSVisaProgress(IOS_VisaDto dto)
        {
            if (dto == null)
            {
                return Ok(JsonView(false, "参数为空"));
            }

            DelegationVisaViewList visaList = _delegationVisaRep.GetDelegationList(dto);

            return Ok(JsonView(visaList));
        }

        /// <summary>
        ///  IOS获取团组签证拍照上传进度02（团组签证详情\人员列表\国家）
        /// </summary>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<ActionResult> PostIOSVisaProgressContent(IOS_VisaCustomerListDto dto)
        {
            if (dto == null)
            {
                return Ok(JsonView(false, "请求错误："));
            }

            List<DelegationVisaProgressView> list = _delegationVisaRep.GetDelegationProgressList(dto.diId);

            return Ok(JsonView(list));
        }


        /// <summary>
        ///  IOS获取团组签证拍照上传进度03（相册）
        /// </summary>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<ActionResult> PostIOSVisaProgressImageList(IOS_VisaImageListDto dto)
        {
            if (dto == null)
            {
                return Ok(JsonView(false, "请求错误："));
            }

            List<VisaProgressImageView> list = _delegationVisaRep.GetVisaProgressImageList(dto.visaProgressCustomerId, dto.picType);

            string url = AppSettingsHelper.Get("VisaProgressImageBaseUrl") + AppSettingsHelper.Get("VisaProgressImageFtpPath");
            list.ForEach(s => s.url = url);

            return Ok(JsonView(list));
        }


        /// <summary>
        /// IOS获取团组签证拍照上传进度04（图片上传）
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<ActionResult> PostIOSVisaProgressUploadImage(IOS_VisaUploadImageDto dto)
        {

            //string result = decodeBase64ToImage(dto.base64DataURL, dto.imageName);

            //if (!string.IsNullOrEmpty(result))
            //{

            //}
            //else {
            //    return Ok(JsonView(false, "上传失败"));
            //}

            DateTime dt1970 = new DateTime(1970, 1, 1, 0, 0, 0, 0);
            int sucNum = 0;
            try
            {
                foreach (var item in dto.base64DataList)
                {
                    string imageName = dto.imageName + ((DateTime.Now.Ticks - dt1970.Ticks) / 10000).ToString();
                    string result = decodeBase64ToImage(item, imageName);

                    if (!string.IsNullOrEmpty(result))
                    {
                        Grp_VisaProgressCustomerPicture pic = new Grp_VisaProgressCustomerPicture();
                        pic.CreateUserId = dto.CreateUserId;
                        pic.PicName = imageName;
                        pic.PicPath = result;
                        pic.VisaProgressCustomerId = dto.visaProgressCustomerId;

                        int insertResult = await _delegationVisaRep.AddAsync<Grp_VisaProgressCustomerPicture>(pic);
                        if (insertResult > 0)
                        {
                            sucNum++;
                        }
                    }
                }

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

            string msg = string.Format(@"成功上传{0}张", sucNum);

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

        private string decodeBase64ToImage(string base64DataURL, string imgName)
        {
            string filename = "";//声明一个string类型的相对路径

            String base64 = base64DataURL.Substring(base64DataURL.IndexOf(",") + 1);      //将‘，’以前的多余字符串删除
            System.Drawing.Bitmap bitmap = null;//定义一个Bitmap对象，接收转换完成的图片

            try//会有异常抛出，try，catch一下
            {

                byte[] arr = Convert.FromBase64String(base64);//将纯净资源Base64转换成等效的8位无符号整形数组

                System.IO.MemoryStream ms = new System.IO.MemoryStream(arr);//转换成无法调整大小的MemoryStream对象
                bitmap = new System.Drawing.Bitmap(ms);//将MemoryStream对象转换成Bitmap对象

                var fileDir = AppSettingsHelper.Get("VisaProgressImageBasePath");
                //文件名称
                filename = "VisaProgress_" + DateTime.Now.ToString("yyyyMMddHHmmss") + "_" + imgName + ".jpeg";//所要保存的相对路径及名字
                //上传的文件的路径
                string filePath = "";
                if (!Directory.Exists(fileDir))
                {
                    Directory.CreateDirectory(fileDir);
                }
                //上传的文件的路径
                filePath = fileDir + filename;

                //string url = HttpRuntime.AppDomainAppPath.ToString();
                //string tmpRootDir = System.Web.HttpContext.Current.Server.MapPath(System.Web.HttpContext.Current.Request.ApplicationPath.ToString()); //获取程序根目录 
                //string imagesurl2 = tmpRootDir + filename; //转换成绝对路径 
                bitmap.Save(filePath, System.Drawing.Imaging.ImageFormat.Jpeg);//保存到服务器路径
                //bitmap.Save(filePath + ".bmp", System.Drawing.Imaging.ImageFormat.Bmp);
                //bitmap.Save(filePath + ".gif", System.Drawing.Imaging.ImageFormat.Gif);
                //bitmap.Save(filePath, System.Drawing.Imaging.ImageFormat.Png);
                ms.Close();//关闭当前流，并释放所有与之关联的资源
                bitmap.Dispose();
            }
            catch (Exception e)
            {
                string massage = e.Message;
                Logs("IOS图片上传Error:" + massage);
                //filename = e.Message;
            }
            return filename;//返回相对路径
        }


        /// <summary>
        /// IOS获取团组签证拍照上传进度05(修改签证状态/通知)
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<ActionResult> PostIOSVisaProgressChangeStatus(IOS_VisaChangeStatusDto dto)
        {
            if (dto == null)
            {
                return Ok(JsonView(false, "请求错误："));
            }

            string msg = "参数错误";
            if (dto.diId > 0 && dto.visaStatus > 0 && dto.visaStatus < 4)
            {
                try
                {
                    //_delegationVisaRep.BeginTran();

                    var updCount = await _delegationVisaRep._sqlSugar.Updateable<Grp_VisaProgressCustomer>()
                        .SetColumns(it => it.WorkStatus == dto.visaStatus)
                        .Where(s => s.Id == dto.visaProgressCustomerId)
                        .ExecuteCommandAsync();
                    if (updCount > 0 && dto.publishCode == 1)
                    {

                        _delegationVisaRep.ChangeDataBase(DBEnum.OA2014DB); //切换到新OA后删除

                        string sqlDelegation = string.Format(@" Select * From DelegationInfo With(Nolock) Where Id = {0} ", dto.diId);
                        OA2021_DelegationInfo groupData = _sqlSugar.SqlQueryable<OA2021_DelegationInfo>(sqlDelegation).First();
                        //GroupInfoDto grpDto = new GroupInfoDto() { Id = dto.diId };
                        //var groupData = await _groupRepository.GetGroupInfo(grpDto);
                        _delegationVisaRep.ChangeDataBase(DBEnum.OA2023DB); //切换到新OA后删除
                        if (groupData == null)
                        {
                            _delegationVisaRep.RollbackTran();
                        }

                        string title = string.Format(@"[签证进度更新]");
                        string content = string.Format(@"测试文本");

                        bool rst = await _message.AddMsg(new MessageDto()
                        {
                            Type = 5,
                            IssuerId = dto.publisher,
                            Title = title,
                            Content = content,
                            ReleaseTime = DateTime.Now,
                            UIdList = new List<int> {
                                234
                            }
                        });

                        if (rst)
                        {
                            return Ok(JsonView(true, "发送通知成功"));
                        }
                    }

                    //_delegationVisaRep.CommitTran();
                }
                catch (Exception)
                {
                    //_delegationVisaRep.RollbackTran();

                }
            }


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

        #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>
        ///  费用审核
        ///  团组列表 Page
        /// </summary>
        /// <param name="_dto">团组列表请求dto</param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> PostExpenseAuditGroupPageItems(ExpenseAuditGroupPageItemsDto _dto)
        {
            #region  参数验证
            if (_dto.UserId < 1) return Ok(JsonView(false, "员工Id为空"));
            if (_dto.PageId < 1) return Ok(JsonView(false, "页面Id为空"));

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

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

            #endregion
            if (_dto.PortType == 1 || _dto.PortType == 2 || _dto.PortType == 2) // web/Android/IOS
            {
                string sqlWhere = string.Empty;
                if (_dto.IsSure == 0) //未完成
                {
                    sqlWhere += string.Format(@" And IsSure = 0");
                }
                else if (_dto.IsSure == 1) //已完成
                {
                    sqlWhere += string.Format(@" And IsSure = 1");
                }

                if (!string.IsNullOrEmpty(_dto.SearchCriteria))
                {
                    string tj = _dto.SearchCriteria;
                    sqlWhere += string.Format(@"And (ssd.Name Like '%{0}%' Or TeamName Like '%{1}%' Or ClientName Like '%{2}%' Or  ClientName Like '%{3}%' Or su.CnName  Like '%{4}%')",
                       tj, tj, tj, tj, tj);
                }

                string sql = string.Format(@"Select Row_Number,Id,SalesQuoteNo,TourCode,TeamTypeId, TeamType,
					                       TeamName,ClientName,ClientUnit, TeamLevId,TeamLev,VisitDate,
										   VisitDays,VisitPNumber,JietuanOperator,IsSure,CreateTime
					                From (
					                    Select row_number() over(order by gdi.VisitDate Desc) as Row_Number,
					                    gdi.Id,SalesQuoteNo,TourCode,ssd1.Id TeamLevId,ssd1.Name TeamLev,TeamName,
										ClientName,ClientUnit,ssd.Id TeamTypeId, ssd.Name TeamType,VisitDate,
										VisitDays,VisitPNumber,su.CnName JietuanOperator,IsSure,gdi.CreateTime
					                    From  Grp_DelegationInfo gdi
					                    Inner Join Sys_SetData ssd On gdi.TeamDid = ssd.Id 
					                    Inner Join Sys_SetData ssd1 On gdi.TeamLevSId = ssd1.Id
					                    Left Join Sys_Users su On gdi.JietuanOperator = su.Id
					                    Where gdi.IsDel = 0 {0}
				                    ) temp  ", sqlWhere);

                RefAsync<int> total = 0;//REF和OUT不支持异步,想要真的异步这是最优解
                var _DelegationList = await _sqlSugar.SqlQueryable<DelegationListView>(sql).ToPageListAsync(_dto.PageIndex, _dto.PageSize, total);//ToPageAsync

                var _view = new
                {
                    PageFuncAuth = pageFunAuthView,
                    Data = _DelegationList
                };
                return Ok(JsonView(true, "查询成功!", _view, total));

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

        /// <summary>
        /// 获取团组费用审核
        /// </summary>
        /// <param name="paras">参数Json字符串</param>
        /// <returns></returns>

        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> PostSearchGrpCreditCardPayment(Search_GrpCreditCardPaymentDto _dto)
        {
            try
            {
                #region  参数验证
                if (_dto.UserId < 1) return Ok(JsonView(false, "员工Id为空"));
                if (_dto.PageId < 1) return Ok(JsonView(false, "页面Id为空"));
                if (_dto.DiId < 1) return Ok(JsonView(false, "团组Id为空"));

                PageFunAuthViewBase pageFunAuthView = new PageFunAuthViewBase();

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

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

                #endregion

                Grp_CreditCardPaymentView _view = new Grp_CreditCardPaymentView();

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

                List<CreditCardPaymentCurrencyPriceItem> ccpCurrencyPrices = new List<CreditCardPaymentCurrencyPriceItem>();

                /*
                 *  Label = 98 其他款项
                 */

                List<Grp_DecreasePayments> _DecreasePayments = await _groupRepository
                                                                    .Query<Grp_DecreasePayments>(s => s.DiId == _dto.DiId && s.IsDel == 0)
                                                                    .ToListAsync();
                /*
                 *  Lable = 85 机票预订
                 */

                List<Grp_AirTicketReservations> p_AirTicketReservations = await _groupRepository
                                                                    .Query<Grp_AirTicketReservations>(s => s.DIId == _dto.DiId && s.IsDel == 0)
                                                                    .ToListAsync();

                /*
                 * 币种信息
                 */

                var currencyItems = await _groupRepository.Query<Sys_SetData>(s => s.STid == 66 && s.IsDel == 0).ToListAsync();

                /*
                 * 用户信息
                 */

                var userItems = await _groupRepository.Query<Sys_Users>(s => s.IsDel == 0).ToListAsync();

                /*
                    * 费用模块
                    */
                Sys_SetData sdPriceName = _groupRepository.Query<Sys_SetData>(s => s.Id == _dto.Label).First();
                string priceModule = string.Empty;
                if (sdPriceName != null)
                {
                    priceModule = sdPriceName.Name;
                }

                /*
                 * 处理详情数据 
                 */
                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 = _DecreasePayments.Where(s => s.Id == entity.CId).FirstOrDefault();
                            if (gdpRes != null)
                            {
                                _detail.PriceMsgContent = "备注：" + gdpRes.Remark;
                                _detail.PriceNameContent = gdpRes.PriceName;
                            }
                            break;
                        default:
                            break;
                    }

                    _detail.PriceName = priceModule;

                    /*
                     * 应付款金额	
                     */
                    Sys_SetData sdPaymentCurrency_WaitPay = currencyItems.Where(s => s.Id == entity.PaymentCurrency).FirstOrDefault();
                    string PaymentCurrency_WaitPay = "Unknown";
                    if (sdPaymentCurrency_WaitPay != null)
                    {
                        PaymentCurrency_WaitPay = sdPaymentCurrency_WaitPay.Name;
                    }
                    _detail.WaitPay = entity.PayMoney.ConvertToDecimal1() + " " + PaymentCurrency_WaitPay;

                    /*
                     * 此次付款金额
                     */
                    decimal CurrPayStr = 0;
                    if (entity.PayPercentage == 0)
                    {
                        if (entity.PayThenMoney != 0)
                            CurrPayStr = (entity.PayThenMoney * entity.DayRate).ConvertToDecimal1();
                    }
                    else
                    {
                        CurrPayStr = (entity.PayMoney * (decimal.Parse(entity.PayPercentage.ToString("#0.00")) / 100 * entity.DayRate)).ConvertToDecimal1();
                    }
                    _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).ConvertToDecimal1();

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

                    /*
                     * 申请人
                     */
                    string operatorName = " - ";
                    Sys_Users _opUser = userItems.Where(s => s.Id == entity.CreateUserId).FirstOrDefault();
                    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 = userItems.Where(s => s.Id == entity.AuditGMOperate).FirstOrDefault();
                        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;


                    /*
                     * 费用总计
                     */
                    ccpCurrencyPrices.Add(new CreditCardPaymentCurrencyPriceItem()
                    {
                        CurrencyId = entity.PaymentCurrency,
                        CurrencyName = PaymentCurrency_WaitPay,
                        AmountPayable = entity.PayMoney,
                        ThisPayment = CurrPayStr,
                        BalancePayment = BalanceStr,
                        AuditedFunds = CurrPayStr
                    });

                    _detail.IsAuditGM = entity.IsAuditGM;

                    detailList.Add(_detail);
                }

                #endregion

                _view.DetailList = new List<Grp_CreditCardPaymentDetailView>(detailList);


                /*
                 * 下方描述处理
                 */

                List<CreditCardPaymentCurrencyPriceItem> nonDuplicat = ccpCurrencyPrices.Where((x, i) => ccpCurrencyPrices.FindIndex(z => z.CurrencyId == x.CurrencyId) == i).ToList();//Lambda表达式去重 

                CreditCardPaymentCurrencyPriceItem ccpCurrencyPrice = nonDuplicat.Where(it => it.CurrencyId == 836).FirstOrDefault();
                if (ccpCurrencyPrice != null)
                {
                    int CNYIndex = nonDuplicat.IndexOf(ccpCurrencyPrice);
                    nonDuplicat.MoveItemAtIndexToFront(CNYIndex);

                }
                else
                {
                    nonDuplicat.OrderBy(it => it.CurrencyId).ToList();
                }
                string amountPayableStr = string.Format(@"应付款总金额： ");
                string thisPaymentStr = string.Format(@"此次付款总金额： ");
                string balancePaymentStr = string.Format(@"目前剩余尾款总金额： ");
                string auditedFundsStr = string.Format(@"已审费用总额： ");

                foreach (var item in nonDuplicat)
                {
                    var strs = ccpCurrencyPrices.Where(it => it.CurrencyId == item.CurrencyId).ToList();
                    if (strs.Count > 0)
                    {
                        decimal amountPayable = strs.Sum(it => it.AmountPayable);
                        decimal thisPayment = strs.Sum(it => it.ThisPayment);
                        decimal balancePayment = strs.Sum(it => it.BalancePayment);
                        decimal auditedFunds = strs.Sum(it => it.AuditedFunds);
                        amountPayableStr += string.Format(@"{0}{1}&nbsp;|", amountPayable.ToString("#0.00"), item.CurrencyName);
                        thisPaymentStr += string.Format(@"{0}{1}&nbsp;|", thisPayment.ToString("#0.00"), item.CurrencyName);
                        balancePaymentStr += string.Format(@"{0}{1}&nbsp;|", balancePayment.ToString("#0.00"), item.CurrencyName);
                        auditedFundsStr += string.Format(@"{0}{1}&nbsp;|", auditedFunds.ToString("#0.00"), item.CurrencyName);
                    }
                }

                _view.TotalStr1 = amountPayableStr.Substring(0, amountPayableStr.Length - 1);
                _view.TotalStr2 = thisPaymentStr.Substring(0, thisPaymentStr.Length - 1);
                _view.TotalStr3 = balancePaymentStr.Substring(0, balancePaymentStr.Length - 1);
                _view.TotalStr4 = auditedFundsStr.Substring(0, auditedFundsStr.Length - 1);


                var _view1 = new
                {
                    PageFuncAuth = pageFunAuthView,
                    Data = _view
                };

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

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


        /// <summary>
        /// 费用审核 
        /// 修改团组费用审核状态
        /// </summary>
        /// <param name="_dto">参数Json字符串</param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> PostAuditGrpCreditCardPayment(Edit_GrpCreditCardPaymentDto _dto)
        {
            #region  参数验证
            if (_dto.UserId < 1) return Ok(JsonView(false, "员工Id为空"));
            if (_dto.PageId < 1) return Ok(JsonView(false, "页面Id为空"));
            #endregion


            #region 页面操作权限验证
            PageFunAuthViewBase pageFunAuthView = new PageFunAuthViewBase();

            pageFunAuthView = await GeneralMethod.PostUserPageFuncDatas(_dto.UserId, _dto.PageId);

            if (pageFunAuthView.AuditAuth == 0) return Ok(JsonView(false, "您没有审核权限"));

            #endregion

            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.UserId)
                .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)
                {
                    var result = await _sqlSugar.Updateable<Grp_CreditCardPayment>().Where(a => a.CId == dto.Id && a.CTable == 85).SetColumns(a => new Grp_CreditCardPayment()
                    {
                        IsDel = 1,
                        DeleteUserId = dto.DeleteUserId,
                        DeleteTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
                    }).ExecuteCommandAsync();
                    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)
                {
                    var result = await _sqlSugar.Updateable<Grp_CreditCardPayment>().Where(a => a.CId == dto.Id && a.CTable == 98).SetColumns(a => new Grp_CreditCardPayment()
                    {
                        IsDel = 1,
                        DeleteUserId = dto.DeleteUserId,
                        DeleteTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
                    }).ExecuteCommandAsync();
                    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)
                {
                    var result = await _sqlSugar.Updateable<Grp_CreditCardPayment>().Where(a => a.CId == dto.Id && a.CTable == 81).SetColumns(a => new Grp_CreditCardPayment()
                    {
                        IsDel = 1,
                        DeleteUserId = dto.DeleteUserId,
                        DeleteTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
                    }).ExecuteCommandAsync();
                    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> PostEnterExitCostExportDetailsList(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", nationalTravelFeeData));
            }
            catch (Exception ex)
            {
                return Ok(JsonView(false, ex.Message));
                throw;
            }
        }

        /// <summary>
        /// 团组模块 - 出入境国家费用标准 Page List Data Source
        /// </summary>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> GetNationalTravelFeePageDataSource()
        {

            string sql = string.Format(@"Select * From Grp_NationalTravelFee Where Isdel = 0  ");

            var nationalTravelFeeData = await _groupRepository._sqlSugar.SqlQueryable<Grp_NationalTravelFee>(sql).ToListAsync();

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

            countryData.AddRange(nationalTravelFeeData.Select(it => it.Country).ToList());
            countryData = countryData.Distinct().ToList();

            List<dynamic> dataSource = new List<dynamic>();
            foreach (var item in countryData)
            {
                List<string> cityData1 = new List<string>();
                cityData1 = nationalTravelFeeData.Where(it => it.Country == item).Select(it => it.City).ToList();
                var countryData2 = new
                {
                    CountryName = item,
                    CityData = cityData1
                };

                dataSource.Add(countryData2);
            }

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

        /// <summary>
        /// 团组模块 - 出入境国家费用标准 Page List
        /// </summary>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> PostNationalTravelFeePage(NationalTravelFeePageDto dto)
        {
            int portId = dto.PortType;
            if (portId != 1 && portId != 2 && portId != 3) return Ok(JsonView(false, "请输入正确的端口号!\r\n请求端口分类1 Web 2 Android 3 IOS"));

            if (dto.PageIndex == 0) return Ok(JsonView(false, "当前页码数不能为0!"));
            if (dto.PageSize == 0) return Ok(JsonView(false, "每页条数不能0!"));

            string whereSql = string.Empty;
            if (!string.IsNullOrEmpty(dto.Country))
            {
                whereSql += string.Format(@" And gntf.Country ='{0}'", dto.Country);
            }

            if (!string.IsNullOrEmpty(dto.City))
            {
                whereSql += string.Format(@" And gntf.City='{0}'", dto.City);
            }

            string pageSql = string.Format(@"Select * From (
                                             Select row_number() over(order by gntf.LastUpdateTime Desc) as RowNumber,
                                             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 {0} ) temp ", whereSql);
            RefAsync<int> total = 0;
            var nationalTravelFeeData = await _groupRepository._sqlSugar.SqlQueryable<NationalTravelFeePageInfoView>(pageSql).ToPageListAsync(dto.PageIndex, dto.PageSize, total);



            return Ok(JsonView(true, "查询成功！", nationalTravelFeeData, (int)total));

        }

        /// <summary>
        /// 团组模块 - 出入境国家费用标准 根据城市查询
        /// </summary>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> PostNationalTravelFeeInfoByCountryAndCity(NationalTravelFeeByCountryAndCityDto dto)
        {
            if (dto == null) return Ok(JsonView(false, "请传入参数!"));

            int portId = dto.PortType;
            if (portId != 1 && portId != 2 && portId != 3) return Ok(JsonView(false, "请输入正确的端口号!\r\n请求端口分类1 Web 2 Android 3 IOS"));

            string whereSql = string.Empty;
            if (!string.IsNullOrEmpty(dto.Country))
            {
                whereSql = string.Format(@"And Country = '{0}' ", dto.Country);
            }

            if (!string.IsNullOrEmpty(dto.City))
            {
                whereSql = string.Format(@"And City = '{0}' ", dto.City);
            }


            string sql = string.Format(@"Select gntf.Country,gntf.City,gntf.Currency,ssd.Name as CurrencyCode, 
	                                                ssd.Remark as CurrencyName,gntf.RoomCost,gntf.FoodCost,gntf.PublicCost,
	                                                gntf.LastUpdateUserId,su.CnName as LastUpdateUserName,gntf.LastUpdateTime 
                                             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 {0} ", whereSql);

            var nationalTravelFeeData = await _groupRepository._sqlSugar.SqlQueryable<NationalTravelFeeInfoByCountryAndCityView>(sql).FirstAsync();



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

        }

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

        /// <summary>
        /// 团组模块 - 出入境国家费用标准 - Del
        /// </summary>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> PostNationalTravelFeeDel(NationalTravelFeeDelDto dto)
        {
            try
            {
                Grp_NationalTravelFee _nationalTravelFee = new Grp_NationalTravelFee()
                {
                    Id = dto.Id,
                    DeleteUserId = dto.DeleteUserId,
                    DeleteTime = DateTime.Now.ToString("yyyy-MM-dd"),
                    IsDel = 1
                };

                var delStatus = await _enterExitCostRep._sqlSugar.Updateable<Grp_NationalTravelFee>(_nationalTravelFee)
                                               .UpdateColumns(it => new { it.DeleteTime, it.DeleteUserId, it.IsDel })
                                               .WhereColumns(it => new { it.Id })
                                               .ExecuteCommandAsync();

                if (delStatus <= 0)
                {
                    return Ok(JsonView(false, "删除失败!"));
                }

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

        #endregion

        #region 签证费用录入

        /// <summary>
        /// 根据diid查询签证费用列表
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> QueryVisaByDiId(VisaPriceDto dto)
        {
            try
            {
                Result groupData = await _visaPriceRep.PostVisaByDiId(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, ex.Message));
            }
        }
        /// <summary>
        /// 根据签证费用Id查询单条数据及c表数据
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> QueryVisaById(PostVisaByIdDto dto)
        {
            try
            {
                Result groupData = await _visaPriceRep.PostVisaById(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, ex.Message));
            }
        }
        /// <summary>
        /// 签证费用删除
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> DelVisaPrice(DelBaseDto dto)
        {
            try
            {
                var res = await _visaPriceRep.SoftDeleteByIdAsync<Grp_VisaInfo>(dto.Id.ToString(), dto.DeleteUserId);
                if (!res)
                {
                    return Ok(JsonView(false, "删除失败"));
                }
                var resultC = await _sqlSugar.Updateable<Grp_CreditCardPayment>().Where(a => a.CId == dto.Id && a.IsDel == 0 && a.CTable == 80).SetColumns(a => new Grp_CreditCardPayment()
                {
                    IsDel = 1,
                    DeleteUserId = dto.DeleteUserId,
                    DeleteTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
                }).ExecuteCommandAsync();
                return Ok(JsonView(true, "删除成功！"));
            }
            catch (Exception ex)
            {
                return Ok(JsonView(false, "程序错误！"));
                throw;
            }
        }
        /// <summary>
        /// 签证费用录入下拉框初始化
        /// </summary>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> VisaPriceAddSelect()
        {
            try
            {
                //支付方式
                List<Sys_SetData> Payment = _sqlSugar.Queryable<Sys_SetData>().Where(a => a.STid == 14 && a.IsDel == 0).ToList();
                List<SetDataInfoView> _Payment = _mapper.Map<List<SetDataInfoView>>(Payment);

                //币种
                List<Sys_SetData> CurrencyList = _sqlSugar.Queryable<Sys_SetData>().Where(a => a.STid == 66 && a.IsDel == 0).ToList();
                List<SetDataInfoView> _CurrencyList = _mapper.Map<List<SetDataInfoView>>(CurrencyList);

                //乘客类型
                List<Sys_SetData> PassengerType = _sqlSugar.Queryable<Sys_SetData>().Where(a => a.STid == 69 && a.IsDel == 0).ToList();
                List<SetDataInfoView> _PassengerType = _mapper.Map<List<SetDataInfoView>>(PassengerType);

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

                var data = new
                {
                    Payment = _Payment,
                    CurrencyList = _CurrencyList,
                    PassengerType = _PassengerType,
                    BankCard = _BankCard
                };
                return Ok(JsonView(true, "查询成功！", 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> OpVisaPrice(OpVisaPriceDto dto)
        {
            try
            {
                Result groupData = await _visaPriceRep.OpVisaPrice(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, ex.Message));
            }
        }
        #endregion

        #region op费用录入
        /// <summary>
        /// 根据diid查询op费用列表
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> QueryCarTouristGuideGroundByDiId(CarTouristGuideGroundDto dto)
        {
            try
            {
                Result groupData = await _carTouristGuideGroundRep.QueryCarTouristGuideGroundByDiId(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, ex.Message));
            }
        }
        /// <summary>
        /// 根据op费用Id查询单条数据及c表数据
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> QueryCarTouristGuideGroundById(CarTouristGuideGroundIdDto dto)
        {
            try
            {
                Grp_CarTouristGuideGroundReservations _groupData = await _sqlSugar.Queryable<Grp_CarTouristGuideGroundReservations>().FirstAsync(a => a.Id == dto.Id && a.IsDel == 0);
                Grp_CreditCardPayment _creditCardPayment = await _sqlSugar.Queryable<Grp_CreditCardPayment>().FirstAsync(a => a.CId == dto.Id && a.CTable == 79 && a.IsDel == 0);
                var data = new
                {
                    CarTouristGuideGround = _groupData,
                    CreditCardPayment = _creditCardPayment
                };
                return Ok(JsonView(true, "查询成功！", data));
            }
            catch (Exception ex)
            {
                return Ok(JsonView(false, "程序错误！"));
            }
        }

        /// <summary>
        /// op费用删除
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> DelCarTouristGuideGround(DelBaseDto dto)
        {
            try
            {
                var res = await _carTouristGuideGroundRep.SoftDeleteByIdAsync<Grp_CarTouristGuideGroundReservations>(dto.Id.ToString(), dto.DeleteUserId);
                if (!res)
                {
                    return Ok(JsonView(false, "删除失败"));
                }
                var result = await _sqlSugar.Updateable<Grp_CarTouristGuideGroundReservationsContent>().Where(a => a.CTGGRId == dto.Id && a.IsDel == 0).SetColumns(a => new Grp_CarTouristGuideGroundReservationsContent()
                {
                    IsDel = 1,
                    DeleteUserId = dto.DeleteUserId,
                    DeleteTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
                }).ExecuteCommandAsync();
                var resultC = await _sqlSugar.Updateable<Grp_CreditCardPayment>().Where(a => a.CId == dto.Id && a.IsDel == 0 && a.CTable == 79).SetColumns(a => new Grp_CreditCardPayment()
                {
                    IsDel = 1,
                    DeleteUserId = dto.DeleteUserId,
                    DeleteTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
                }).ExecuteCommandAsync();
                return Ok(JsonView(true, "删除成功！"));
            }
            catch (Exception ex)
            {
                return Ok(JsonView(false, "程序错误！"));
                throw;
            }
        }

        /// <summary>
        /// op费用录入操作（Status:1.新增,2.修改）
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> OpCarTouristGuideGround(OpCarTouristGuideGroundDto dto)
        {
            try
            {
                Result groupData = await _carTouristGuideGroundRep.OpCarTouristGuideGround(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, ex.Message));
            }
        }
        /// <summary>
        /// 填写费用详细页面初始化绑定
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> CarTouristGuideGroundContentInitialize(CarTouristGuideGroundContentDto dto)
        {
            try
            {
                Result groupData = await _carTouristGuideGroundRep.CarTouristGuideGroundContent(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, ex.Message));
            }
        }
        /// <summary>
        /// 根据op费用Id查询详细数据
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> OpCarTouristGuideGroundContentById(CarTouristGuideGroundIdDto dto)
        {
            try
            {
                Result groupData = await _carTouristGuideGroundRep.OpCarTouristGuideGroundContentById(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, ex.Message));
            }
        }
        /// <summary>
        /// OP费用录入填写详情
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> OpCarTouristGuideGroundContent(OpCarTouristGuideGroundContentDto dto)
        {
            try
            {
                Result groupData = await _carTouristGuideGroundRep.OpCarTouristGuideGroundContent(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, ex.Message));
            }
        }
        #endregion

        #region 团组成本

        /// <summary>
        /// 团组成本数据初始化
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        public async Task<IActionResult> GroupCostInit(GroupCostInItDto dto)
        {
            var groupList = _sqlSugar.SqlQueryable<DelegationInfoAndIsTrueView>($@"
                Select  a.Id,TeamName GroupName,b.isTrue From  Grp_DelegationInfo  a left join  (select top 100 percent Diid, CASE 
                WHEN COUNT(*) >= 0 THEN 'True' 
                ELSE 'False' END as isTrue  from Grp_GroupCost where Isdel = 0 and date != '' group by Diid) b on a.Id = b.Diid
                 Where TeamName != '' And IsDel = 0  Order By a.Id Desc
            ").ToList(); //团组列表
            int diid = dto.Diid == -1 ? groupList.First().Id : dto.Diid;
            var groupInfo = await _groupRepository.PostShareGroupInfo(new ShareGroupInfoDto { PortType = 1, Id = diid }); //团组信息
            var groupChecks = _checkBoxs.GetCheckBoxsByDiid(diid);  //团组选中信息  可枚举
            var groupCost = _GroupCostRepository.GetAllByDiid(diid);  //团组列表信息  
            var groupCostMap = _mapper.Map<List<Grp_GroupCostDto>>(groupCost);
            var hotelNumber = _CostTypeHotelNumberRepository.GetCostTypeHotelNumberByDiid(diid); //酒店数量 可枚举
            var GroupCostParameter = _GroupCostParameterRepository.GetGroupCostParameterListByDiid(diid); //成本系数 可枚举

            //GroupCostParameter.Add(new
            //     Grp_GroupCostParameter());
            var GroupCostParameterMap = _mapper.Map<List<Grp_GroupCostParameterDto>>(GroupCostParameter);

            return Ok(JsonView(new
            {
                groupList,
                groupInfo,
                groupChecks,
                groupCost = groupCostMap,
                hotelNumber,
                GroupCostParameter = GroupCostParameterMap,
            }));
        }

        /// <summary>
        /// 团组成本信息保存
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        public async Task<IActionResult> SaveGroupCost(GroupCostSavaDto dto)
        {
            JsonView jw = null;
            bool isTrue = false;
            var Grp_groups = _mapper.Map<List<Grp_GroupCost>>(dto.GroupCosts);
            Grp_groups.ForEach(x => { x.CreateUserId = dto.Userid; x.CreateTime = DateTime.Now; }); //.ToString("yyyy-MM-dd HH:mm:ss")
            var Grp_CheckBoxs = _mapper.Map<List<Grp_CheckBoxs>>(dto.CheckBoxs);
            Grp_CheckBoxs.ForEach(x => { x.CreateUserId = dto.Userid; x.CreateTime = DateTime.Now; });
            var Grp_HotelNumber = _mapper.Map<List<Grp_CostTypeHotelNumber>>(dto.CostTypeHotelNumbers);
            var Grp_CostParameters = _mapper.Map<List<Grp_GroupCostParameter>>(dto.GroupCostParameters);

            try
            {
                _sqlSugar.BeginTran();
                isTrue = await _GroupCostRepository.
                    SaveGroupCostList(Grp_groups, dto.Diid); //列表
                isTrue = await _checkBoxs.SaveCheckBoxs(Grp_CheckBoxs, dto.Diid); //选中项
                isTrue = await _CostTypeHotelNumberRepository.SaveHotelNumber(Grp_HotelNumber, dto.Userid, dto.Diid); //酒店房间数量
                isTrue = await _GroupCostParameterRepository.SaveAsync(Grp_CostParameters, dto.Userid, dto.Diid); //系数

                _sqlSugar.CommitTran();
                jw = JsonView(true, "保存成功！", isTrue);
            }
            catch (Exception)
            {
                _sqlSugar.RollbackTran();
                jw = JsonView(false);
            }

            return Ok(jw);
        }

        /// <summary>
        /// 司兼导数据
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        public IActionResult GetCarGuides(CarGuidesDto dto)
        {
            JsonView jw = null;
            var Data = _sqlSugar.SqlQueryable<Grp_CarGuides>($@" select * from Grp_CarGuides where isdel = 0 ").ToList();
            jw = JsonView(true, "获取成功！", Data);
            return Ok(jw);
        }


        /// <summary>
        /// 导游数据
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        public IActionResult GetGuidesInfo(CarGuidesDto dto)
        {
            JsonView jw = null;

            //var Data = _sqlSugar.SqlQueryable<Grp_GuidesInfo>($@"		SELECT* FROM (
            //                                      SELECT*,ROW_NUMBER() OVER (ORDER BY Id) AS RowNumber FROM Grp_GuidesInfo where (city like '%{dto.City}%' or country like '%{dto.City}%' )  and isdel = 0 ) as b
            //                                      where RowNumber BETWEEN {(dto.Page - 1) * dto.Size + 1} and {(dto.Page) * dto.Size}").ToList();

            var Data = _sqlSugar.SqlQueryable<Grp_GuidesInfo>($@" select * from Grp_GuidesInfo where isdel = 0 ").ToList();

            jw = JsonView(true, "获取成功！", Data);
            return Ok(jw);
        }

        /// <summary>
        /// 成本车数据
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        public IActionResult GetCarInfo(CarGuidesDto dto)
        {
            JsonView jw = null;
            var Data = _sqlSugar.SqlQueryable<Grp_CarInfo>($@" select * from Grp_CarInfo where isdel = 0 ").ToList();
            jw = JsonView(true, "获取成功！", Data);
            return Ok(jw);
        }


        /// <summary>
        /// 景点数据
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        public IActionResult GetScenicSpotInfo(CarGuidesDto dto)
        {
            var Data = _sqlSugar.SqlQueryable<Grp_ScenicSpotInfo>($@" select * from Grp_ScenicSpotInfo where isdel = 0 ").ToList();
            return Ok(JsonView(true, "获取成功！", Data));
        }

        #endregion

        #region 酒店预定
        /// <summary>
        /// 酒店预订页面初始化绑定
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> HotelReservationsInitialize(HotelReservationsDto dto)
        {
            try
            {
                Result groupData = await _hotelPriceRep.HotelReservationsInitialize(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, ex.Message));
            }
        }
        /// <summary>
        /// 根据团组Id查询酒店费用列表
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> HotelReservationsByDiId(HotelReservationsByDiIdDto dto)
        {
            try
            {
                Result groupData = await _hotelPriceRep.HotelReservationsByDiId(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, ex.Message));
            }
        }
        /// <summary>
        /// 根据酒店费用Id查询酒店费用详细
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> HotelReservationsById(HotelReservationsByIdDto dto)
        {
            try
            {
                Result groupData = await _hotelPriceRep.HotelReservationsById(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, ex.Message));
            }
        }
        /// <summary>
        /// 计算酒店付款总金额
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> HotelConversionAmounts(HotelReservationsCNYDto dto)
        {
            try
            {
                Result groupData = await _hotelPriceRep.HotelConversionAmounts(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, ex.Message));
            }
        }

        /// <summary>
        /// 酒店费用操作（Status:1.新增,2.修改）
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> OpHotelReservations(OpHotelReservationsData dto)
        {
            try
            {
                Result groupData = await _hotelPriceRep.OpHotelReservations(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, ex.Message));
            }
        }

        /// <summary>
        ///  文件上传
        /// </summary>
        /// <param name="file"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> UploadHotel(IFormFile file)
        {
            try
            {
                if (file != null)
                {
                    var fileDir = AppSettingsHelper.Get("GrpFileBasePath");
                    //文件名称
                    string projectFileName = file.FileName;

                    //上传的文件的路径
                    string filePath = "";


                    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> DelFileHotel(DelFileDto dto)
        {
            try
            {
                string filePath = "";
                var fileDir = AppSettingsHelper.Get("GrpFileBasePath");
                // 返回与指定虚拟路径相对应的物理路径即绝对路径
                //int id = 0;
                filePath = fileDir + "/酒店费用录入相关文件/" + dto.fileName;
                // 删除该文件
                try
                {
                    System.IO.File.Delete(filePath);
                    var result = await _sqlSugar.Updateable<Grp_HotelReservations>().Where(a => a.Id == dto.Id && a.IsDel == 0).SetColumns(a => new Grp_HotelReservations()
                    {
                        Attachment = "",
                    }).ExecuteCommandAsync();
                    if (result != 0)
                    {
                        return Ok(JsonView(true, "成功！"));
                    }
                    else
                    {
                        return Ok(JsonView(false, "失败！"));
                    }
                }
                catch (Exception)
                {
                    var result = await _sqlSugar.Updateable<Grp_HotelReservations>().Where(a => a.Id == dto.Id && a.IsDel == 0).SetColumns(a => new Grp_HotelReservations()
                    {
                        Attachment = "",
                    }).ExecuteCommandAsync();
                    if (result != 0)
                    {
                        return Ok(JsonView(true, "成功！"));
                    }
                    else
                    {
                        return Ok(JsonView(false, "失败！"));
                    }
                    throw;
                }


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

        }
        /// <summary>
        /// 生成VOUCHER
        /// </summary>
        /// <param name=""></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> HotelGenerate(HotelReservationsByIdDto dto)
        {

            Grp_HotelReservations h = _sqlSugar.Queryable<Grp_HotelReservations>().First(a => a.Id == dto.Id && a.IsDel == 0);

            //判断数据是否完整
            if (h != null)
            {
                if (!string.IsNullOrEmpty(h.DetermineNo))
                {
                    string strFileName = "HotelStatement/";
                    Grp_DelegationInfo dele = _sqlSugar.Queryable<Grp_DelegationInfo>().First(a => a.Id == h.DiId && a.IsDel == 0);
                    if (dele != null)
                        strFileName += dele.TourCode;


                    //载入模板
                    string sss = AppSettingsHelper.Get("WordBasePath") + "Template/酒店预订模板.doc";

                    Document doc = new Document(sss);
                    DocumentBuilder builder = new DocumentBuilder(doc);
                    try
                    {
                        #region 替换Word模板书签内容

                        //这里可以创建个DataTable循环添加书签的值，这里提示一下就不多做修改了
                        //入住卷预定号码

                        if (doc.Range.Bookmarks["VNO"] != null)
                        {
                            Bookmark mark = doc.Range.Bookmarks["VNO"];
                            mark.Text = h.CheckNumber;
                        }
                        //酒店时间
                        if (doc.Range.Bookmarks["Date"] != null)
                        {
                            Bookmark mark = doc.Range.Bookmarks["Date"];
                            mark.Text = Convert.ToDateTime(h.CreateTime).ToString("yyyy-MM-dd");
                        }
                        //团号
                        if (doc.Range.Bookmarks["TNo"] != null)
                        {
                            Bookmark mark = doc.Range.Bookmarks["TNo"];
                            mark.Text = dele.TourCode;
                        }
                        //预定号码
                        if (doc.Range.Bookmarks["BookingId"] != null)
                        {
                            Bookmark mark = doc.Range.Bookmarks["BookingId"];
                            mark.Text = h.ReservationsNo;
                        }

                        if (doc.Range.Bookmarks["HotelConfirmNo"] != null)
                        {
                            Bookmark mark = doc.Range.Bookmarks["HotelConfirmNo"];
                            mark.Text = h.DetermineNo;
                        }

                        //酒店城市
                        if (doc.Range.Bookmarks["City"] != null)
                        {
                            Bookmark mark = doc.Range.Bookmarks["City"];
                            mark.Text = h.City;
                        }
                        //酒店名称
                        if (doc.Range.Bookmarks["HName"] != null)
                        {
                            Bookmark mark = doc.Range.Bookmarks["HName"];
                            mark.Text = h.HotelName;
                        }
                        //酒店地址
                        if (doc.Range.Bookmarks["Address"] != null)
                        {
                            Bookmark mark = doc.Range.Bookmarks["Address"];
                            mark.Text = h.HotelAddress;
                        }
                        //酒店电话
                        if (doc.Range.Bookmarks["Tel"] != null)
                        {
                            Bookmark mark = doc.Range.Bookmarks["Tel"];
                            mark.Text = h.HotelTel;
                        }
                        //酒店传真
                        if (doc.Range.Bookmarks["Fax"] != null)
                        {
                            Bookmark mark = doc.Range.Bookmarks["Fax"];
                            if (!string.IsNullOrWhiteSpace(h.HotelFax))
                            {
                                mark.Text = h.HotelFax;
                            }

                        }
                        //入住时间
                        if (doc.Range.Bookmarks["CIn"] != null)
                        {
                            DateTime dtCheckIn = Convert.ToDateTime(h.CheckInDate);
                            Bookmark mark = doc.Range.Bookmarks["CIn"];
                            mark.Text = dtCheckIn.Day + " " + dtCheckIn.ToString("MMMM", CultureInfo.GetCultureInfo("en-US")) + " " + dtCheckIn.Year;
                        }
                        //退房时间
                        if (doc.Range.Bookmarks["COut"] != null)
                        {
                            DateTime dtCheckOut = Convert.ToDateTime(h.CheckOutDate);
                            Bookmark mark = doc.Range.Bookmarks["COut"];
                            mark.Text = dtCheckOut.Day + " " + dtCheckOut.ToString("MMMM", CultureInfo.GetCultureInfo("en-US")) + " " + dtCheckOut.Year;
                        }
                        //客户名称
                        if (doc.Range.Bookmarks["GName"] != null)
                        {
                            Bookmark mark = doc.Range.Bookmarks["GName"];
                            mark.Text = h.GuestName;
                        }

                        //房间介绍
                        if (doc.Range.Bookmarks["ROOM"] != null)
                        {
                            Bookmark mark = doc.Range.Bookmarks["ROOM"];
                            mark.Text = h.RoomExplanation;
                        }

                        //报价描述
                        if (doc.Range.Bookmarks["NOTE"] != null)
                        {
                            Bookmark mark = doc.Range.Bookmarks["NOTE"];
                            Sys_SetData ss = _sqlSugar.Queryable<Sys_SetData>().First(a => a.Id == h.ReservationsWebsite);
                            if (ss != null)
                                mark.Text = ss.Name;
                        }
                        //入住时间
                        if (doc.Range.Bookmarks["CheckIn"] != null)
                        {
                            DateTime dtCheckIn = Convert.ToDateTime(h.CheckInDate);
                            Bookmark mark = doc.Range.Bookmarks["CheckIn"];
                            mark.Text = dtCheckIn.Day + " " + dtCheckIn.ToString("MMMM", CultureInfo.GetCultureInfo("en-US")) + " " + dtCheckIn.Year + " ";
                        }
                        //退房时间
                        if (doc.Range.Bookmarks["CheckOut"] != null)
                        {
                            DateTime dtCheckOut = Convert.ToDateTime(h.CheckOutDate);
                            Bookmark mark = doc.Range.Bookmarks["CheckOut"];
                            mark.Text = " " + dtCheckOut.Day + " " + dtCheckOut.ToString("MMMM", CultureInfo.GetCultureInfo("en-US")) + " " + dtCheckOut.Year;
                        }
                        //日期
                        if (doc.Range.Bookmarks["DT"] != null)
                        {
                            Bookmark mark = doc.Range.Bookmarks["DT"];
                            mark.Text = Convert.ToDateTime(h.CreateTime).ToString("yyyy-MM-dd");
                        }
                        //名称
                        if (doc.Range.Bookmarks["VName"] != null)
                        {
                            Bookmark mark = doc.Range.Bookmarks["VName"];
                            mark.Text = h.HotelName;
                        }
                        //号码
                        if (doc.Range.Bookmarks["VOUCHERNO"] != null)
                        {
                            Bookmark mark = doc.Range.Bookmarks["VOUCHERNO"];
                            mark.Text = h.CheckNumber;
                        }

                        #endregion
                        //string fileName = "HotelStatement/" + diName + "机票费用报表" + "_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx";

                        strFileName += "VOUCHER.doc";

                        var fileDir = AppSettingsHelper.Get("WordBasePath") + strFileName;
                        doc.Save(fileDir);
                        string Url = AppSettingsHelper.Get("WordBaseUrl") + "Office/Word/" + strFileName;
                        return Ok(JsonView(true, "成功！", Url));

                        //保存为doc，并打开 

                    }
                    catch (Exception ex)
                    {

                        throw;
                    }

                }
                else
                {
                    return Ok(JsonView(false, "酒店确定号码未填写,无法生成Voucher！"));
                }
            }
            else
            {
                return Ok(JsonView(false, "酒店确定号码未填写,无法生成Voucher！"));
            }

        }


        /// <summary>
        /// 导出确认单
        /// </summary>
        /// <param name=""></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> ConfirmWord(HotelReservationsByIdDto dto)
        {
            //团组信息

            Grp_DelegationInfo di = _sqlSugar.Queryable<Grp_DelegationInfo>().First(a => a.Id == dto.Id);

            //酒店数据
            List<Grp_HotelReservations> listhoteldata = _sqlSugar.Queryable<Grp_HotelReservations>().Where(a => a.DiId == dto.Id && a.IsDel == 0).ToList();

            //利datatable存储
            DataTable dt = new DataTable();
            dt.Columns.Add("CheckInDate", typeof(string));
            dt.Columns.Add("City", typeof(string));
            dt.Columns.Add("Hotel", typeof(string));
            dt.Columns.Add("Room", typeof(string));

            for (int i = 0; i < listhoteldata.Count; i++)
            {
                DateTime dayStart = Convert.ToDateTime(listhoteldata[i].CheckInDate);
                DateTime dayEnd = Convert.ToDateTime(listhoteldata[i].CheckOutDate);
                while (dayStart < dayEnd)
                {
                    string temp = "";
                    DataRow row = dt.NewRow();
                    row["CheckInDate"] = dayStart.ToString("yyyy-MM-dd");
                    row["City"] = listhoteldata[i].City;
                    row["Hotel"] = listhoteldata[i].HotelName;
                    if (listhoteldata[i].SingleRoomCount > 0)
                    {
                        temp = listhoteldata[i].SingleRoomCount + "个单间" + "\r\n";
                    }
                    if (listhoteldata[i].DoubleRoomCount > 0)
                    {
                        temp = temp + listhoteldata[i].DoubleRoomCount + "个表间" + "\r\n";
                    }
                    if (listhoteldata[i].SuiteRoomCount > 0)
                    {
                        temp = temp + listhoteldata[i].SuiteRoomCount + "个套房" + "\r\n";
                    }
                    if (listhoteldata[i].OtherRoomCount > 0)
                    {
                        temp = temp + listhoteldata[i].OtherRoomCount + "个其他房型" + "\r\n";
                    }
                    row["Room"] = temp;
                    dt.Rows.Add(row);
                    dayStart = dayStart.AddDays(1);
                }
            }


            Dictionary<string, string> dic = new Dictionary<string, string>();
            dic.Add("Dele", di.TeamName);
            dic.Add("City", di.VisitCountry);


            //模板路径
            //string sss = AppSettingsHelper.Get("WordBasePath") + "Template/酒店预订模板.doc";
            string tempPath = AppSettingsHelper.Get("WordBasePath") + "Template/酒店用房确认单-模板.doc";
            //载入模板
            Aspose.Words.Document doc = new Aspose.Words.Document(tempPath);
            DocumentBuilder builder = new DocumentBuilder(doc);

            foreach (var key in dic.Keys)
            {
                builder.MoveToBookmark(key);
                builder.Write(dic[key]);
            }

            //获取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行
            try
            {
                //循环赋值
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    builder.MoveToCell(0, i + 1, 0, 0);
                    builder.Write(dt.Rows[i]["CheckInDate"].ToString());

                    builder.MoveToCell(0, i + 1, 1, 0);
                    builder.Write(dt.Rows[i]["City"].ToString());

                    builder.MoveToCell(0, i + 1, 2, 0);
                    builder.Write(dt.Rows[i]["Hotel"].ToString());

                    builder.MoveToCell(0, i + 1, 3, 0);
                    builder.Write(dt.Rows[i]["Room"].ToString());
                }
            }
            catch
            {
            }
            //删除多余行

            while (tableOne.Rows.Count > dt.Rows.Count + 1)
            {
                tableOne.Rows.RemoveAt(dt.Rows.Count + 1);
            }
            string strFileName = di.TeamName + "酒店确认单.doc";
            try
            {
                doc.Save(AppSettingsHelper.Get("WordBasePath") + "HotelStatement/" + strFileName);
                string Url = AppSettingsHelper.Get("WordBaseUrl") + "Office/Word/HotelStatement/" + strFileName;
                return Ok(JsonView(true, "成功", Url));
            }
            catch (Exception)
            {
                return Ok(JsonView(false, "当前文档已打开，请先关闭！"));
                throw;
            }

            //doc.Save(HttpUtility.UrlEncode(strFileName, Encoding.UTF8), Aspose.Words.SaveFormat.Doc, Aspose.Words.SaveType.OpenInWord, Response);

        }

        /// <summary>
        /// 酒店费用删除
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> DelHotelPrice(DelBaseDto dto)
        {
            try
            {
                var res = await _hotelPriceRep.SoftDeleteByIdAsync<Grp_HotelReservations>(dto.Id.ToString(), dto.DeleteUserId);
                if (!res)
                {
                    return Ok(JsonView(false, "删除失败"));
                }
                var resultC = await _sqlSugar.Updateable<Grp_CreditCardPayment>().Where(a => a.CId == dto.Id && a.IsDel == 0 && a.CTable == 76).SetColumns(a => new Grp_CreditCardPayment()
                {
                    IsDel = 1,
                    DeleteUserId = dto.DeleteUserId,
                    DeleteTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
                }).ExecuteCommandAsync();
                return Ok(JsonView(true, "删除成功！"));
            }
            catch (Exception ex)
            {
                return Ok(JsonView(false, "程序错误！"));
                throw;
            }
        }
        #endregion

        #region 团组状态

        /// <summary>
        ///  团组状态列表 Page
        /// </summary>
        /// <param name="dto">团组列表请求dto</param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> PostGroupStatusPageList(GroupStatusListDto dto)
        {
            if (dto == null) return Ok(JsonView(false, "请输入搜索条件!"));

            if (dto.PortType == 1 || dto.PortType == 2) // web/Android
            {
                string sqlWhere = string.Empty;
                if (dto.IsSure == 0) //未完成
                {
                    sqlWhere += string.Format(@" And IsSure = 0");
                }
                else if (dto.IsSure == 1) //已完成
                {
                    sqlWhere += string.Format(@" And IsSure = 1");
                }

                if (!string.IsNullOrEmpty(dto.SearchCriteria))
                {
                    string tj = dto.SearchCriteria;
                    sqlWhere += string.Format(@"And (ssd.Name Like '%{0}%' Or TeamName Like '%{1}%' Or ClientName Like '%{2}%' Or  ClientName Like '%{3}%' Or su.CnName  Like '%{4}%')",
                       tj, tj, tj, tj, tj);
                }

                string sql = string.Format(@"Select Row_Number,Id,SalesQuoteNo,TourCode,TeamTypeId, TeamType,
					                              TeamLevId,TeamLev,TeamName,ClientName,ClientUnit,
					                              VisitDate,VisitDays,VisitPNumber,JietuanOperator,IsSure,CreateTime
					                              From (
					                              Select row_number() over(order by gdi.CreateTime Desc) as Row_Number,
					                              gdi.Id,SalesQuoteNo,TourCode,ssd.Id TeamTypeId, ssd.Name TeamType,
					                              ssd1.Id TeamLevId,ssd1.Name TeamLev,TeamName,ClientName,ClientUnit,
					                              VisitDate,VisitDays,VisitPNumber,su.CnName JietuanOperator,IsSure,gdi.CreateTime
					                              From  Grp_DelegationInfo gdi
					                              Inner Join Sys_SetData ssd On gdi.TeamDid = ssd.Id 
					                              Inner Join Sys_SetData ssd1 On gdi.TeamLevSId = ssd1.Id
					                              Left Join Sys_Users su On gdi.JietuanOperator = su.Id
					                              Where gdi.IsDel = 0  {0}
				                              ) temp  ", sqlWhere);

                RefAsync<int> total = 0;//REF和OUT不支持异步,想要真的异步这是最优解
                var _DelegationList = await _sqlSugar.SqlQueryable<GroupStatusView>(sql).ToPageListAsync(dto.PageIndex, dto.PageSize, total);//ToPageAsync

                return Ok(JsonView(true, "查询成功!", _DelegationList, total));

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

        /// <summary>
        ///  团组状态
        ///  设置操作完成
        /// </summary>
        /// <param name="dto">团组列表请求dto</param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> PostGroupStatusSetOperationComplete(GroupStatusSetOperationCompleteDto dto)
        {
            if (dto == null) return Ok(JsonView(false, "请输入搜索条件!"));


            Grp_DelegationInfo _DelegationInfo = new Grp_DelegationInfo()
            {
                Id = dto.Id,
                IsSure = 1
            };


            var result = await _sqlSugar.Updateable(_DelegationInfo)
                .UpdateColumns(it => new { it.IsSure })
                .WhereColumns(it => new { it.Id })
                .ExecuteCommandAsync();

            if (result > 0)
            {
                return Ok(JsonView(true, "操作完成!"));
            }

            return Ok(JsonView(false, "操作失败!"));


        }


        #endregion

        #region 保险费用录入
        /// <summary>
        /// 根据团组Id查询保险费用列表
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> CustomersByDiId(CustomersByDiIdDto dto)
        {
            try
            {
                Result groupData = await _customersRep.CustomersByDiId(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, ex.Message));
            }
        }

        /// <summary>
        /// 根据保险费用Id查询保险费用详细
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> CustomersById(CustomersByIdDto dto)
        {
            try
            {
                Result groupData = await _customersRep.CustomersById(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, ex.Message));
            }
        }
        /// <summary>
        /// 酒店预订页面初始化绑定
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> CustomersInitialize(CustomersInitializeDto dto)
        {
            try
            {
                Result groupData = await _customersRep.CustomersInitialize(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, ex.Message));
            }
        }

        /// <summary>
        /// 保险费用操作（Status:1.新增,2.修改）
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> OpCustomers(OpCustomersDto dto)
        {
            try
            {
                Result groupData = await _customersRep.OpCustomers(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, ex.Message));
            }
        }

        /// <summary>
        ///  文件上传
        /// </summary>
        /// <param name="file"></param>
        /// <returns></returns>
        [HttpPost]
        [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
        public async Task<IActionResult> UploadCus(IFormFile file)
        {
            try
            {
                if (file != null)
                {
                    var fileDir = AppSettingsHelper.Get("GrpFileBasePath");
                    //文件名称
                    string projectFileName = file.FileName;

                    //上传的文件的路径
                    string filePath = "";


                    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> DelFileCus(DelFileDto dto)
        {
            try
            {
                string filePath = "";
                var fileDir = AppSettingsHelper.Get("GrpFileBasePath");
                // 返回与指定虚拟路径相对应的物理路径即绝对路径
                //int id = 0;
                filePath = fileDir + "/保险费用文件上传/" + dto.fileName;
                // 删除该文件
                try
                {
                    System.IO.File.Delete(filePath);
                    var result = await _sqlSugar.Updateable<Grp_Customers>().Where(a => a.Id == dto.Id && a.IsDel == 0 && a.Attachment == dto.fileName).SetColumns(a => new Grp_Customers()
                    {
                        Attachment = "",
                    }).ExecuteCommandAsync();
                    if (result != 0)
                    {
                        return Ok(JsonView(true, "成功！"));
                    }
                    else
                    {
                        return Ok(JsonView(false, "失败！"));
                    }
                }
                catch (Exception)
                {
                    var result = await _sqlSugar.Updateable<Grp_Customers>().Where(a => a.Id == dto.Id && a.IsDel == 0 && a.Attachment == dto.fileName).SetColumns(a => new Grp_Customers()
                    {
                        Attachment = "",
                    }).ExecuteCommandAsync();
                    if (result != 0)
                    {
                        return Ok(JsonView(true, "成功！"));
                    }
                    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> DelCustomers(DelBaseDto dto)
        {
            try
            {
                var res = await _customersRep.SoftDeleteByIdAsync<Grp_Customers>(dto.Id.ToString(), dto.DeleteUserId);
                if (!res)
                {
                    return Ok(JsonView(false, "删除失败"));
                }
                var resultC = await _sqlSugar.Updateable<Grp_CreditCardPayment>().Where(a => a.CId == dto.Id && a.IsDel == 0 && a.CTable == 82).SetColumns(a => new Grp_CreditCardPayment()
                {
                    IsDel = 1,
                    DeleteUserId = dto.DeleteUserId,
                    DeleteTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
                }).ExecuteCommandAsync();
                return Ok(JsonView(true, "删除成功！"));
            }
            catch (Exception ex)
            {
                return Ok(JsonView(false, "程序错误！"));
                throw;
            }
        }
        #endregion
    }
}