OA2023/OASystem/OASystem.Infrastructure/Repositories/Groups/DelegationInfoRepository.cs

using AutoMapper;
using Newtonsoft.Json;
using NPOI.OpenXmlFormats.Wordprocessing;
using OASystem.Domain;
using OASystem.Domain.Dtos;
using OASystem.Domain.Dtos.Financial;
using OASystem.Domain.Dtos.Groups;
using OASystem.Domain.Dtos.UserDto;
using OASystem.Domain.Entities.Customer;
using OASystem.Domain.Entities.Groups;
using OASystem.Domain.ViewModels.CRM;
using OASystem.Domain.ViewModels.Financial;
using OASystem.Domain.ViewModels.Groups;
using OASystem.Infrastructure.Repositories.System;
using Org.BouncyCastle.Asn1.Cms;
using Org.BouncyCastle.Utilities.Collections;
using Serilog;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace OASystem.Infrastructure.Repositories.Groups
{
    /// <summary>
    /// 接团信息
    /// </summary>
    public class DelegationInfoRepository : BaseRepository<Grp_DelegationInfo, DelegationInfoView>
    {
        private readonly SetDataRepository _setDataRepository;
        private readonly UsersRepository _usersRepository;
        private readonly TaskAssignmentRepository _taskAssignmentRep;
        private readonly IMapper _mapper;
        //private readonly TeamRateRepository _teamRateRep;
        //public readonly Logs _logs;

        public DelegationInfoRepository(SqlSugarClient sqlSugar, SetDataRepository setDataRepository, UsersRepository usersRepository, 
            TaskAssignmentRepository taskAssignmentRep, IMapper mapper)
            : base(sqlSugar)
        {
            _setDataRepository = setDataRepository;
            _usersRepository = usersRepository;
            _taskAssignmentRep = taskAssignmentRep;
            _mapper = mapper;
            //_teamRateRep = teamRateRep;
        }

        #region 团组信息 团组详情共享Api

        /// <summary>
        /// 团组信息 团组预览详情共享Api
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public async Task<Result> PostShareGroupInfo(ShareGroupInfoDto dto)
        {
            Result result = new Result() { Code = -2, Msg = "未知错误" };

            string sql = string.Format(@"Select Id,TeamName,TourCode,ClientName,VisitCountry,VisitStartDate,VisitEndDate,VisitDays,VisitPNumber 
                                             From Grp_DelegationInfo Where Id = {0} And IsDel = 0", dto.Id);

            if (dto.PortType == 1) //Web
            {
                var _DelegationInfo = await _sqlSugar.SqlQueryable<Web_ShareGroupInfoView>(sql).FirstAsync();
                if (_DelegationInfo != null)
                {
                    _DelegationInfo.VisitCountry = FormartTeamName(_DelegationInfo.VisitCountry);
                }
                else result.Msg = "暂无该团组信息";

                result.Data = _DelegationInfo;
            }
            else if (dto.PortType == 2 || dto.PortType == 3) //IOS Or Android
            {
                var _DelegationInfo = await _sqlSugar.SqlQueryable<IOSOrAndroid_ShareGroupInfoView>(sql).FirstAsync();
                if (_DelegationInfo != null)
                {
                    _DelegationInfo.VisitCountry = FormartTeamName(_DelegationInfo.VisitCountry);

                    result.Data = _DelegationInfo;
                }
                else result.Msg = "暂无该团组信息";

                result.Data = _DelegationInfo;

            }
            else result.Msg = "请输入正确的端口号。1 Web 2 Android 3 IOS";

            result.Code = 0;
            result.Msg = "成功!";

            return result;
        }


        /// <summary>
        /// 团组信息List 团组预览详情共享Api
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public async Task<Result> PostShareGroupInfos(int portType)
        {
            Result result = new Result() { Code = -2, Msg = "未知错误", Data = new List<object>() { } };

            string sql = string.Format(@"Select Id,TeamName,TourCode,ClientName,VisitCountry,VisitStartDate,VisitEndDate,VisitDays,VisitPNumber 
                                             From Grp_DelegationInfo Where IsDel = 0 Order By Id Desc");


            var _DelegationInfo = await _sqlSugar.SqlQueryable<Web_ShareGroupInfoView>(sql).ToListAsync();
            if (_DelegationInfo.Count > 0)
            {
                foreach (var item in _DelegationInfo)
                {
                    item.VisitCountry = FormartTeamName(item.VisitCountry);
                }
            }
            else { result.Msg = "暂无该团组信息"; return result; }

            if (portType == 1) //Web
            {

                result.Data = _DelegationInfo;
            }
            else if (portType == 2 || portType == 3) //IOS Or Android
            {

                result.Data = _DelegationInfo;
            }
            else { result.Msg = "请输入正确的端口号。1 Web 2 Android 3 IOS"; return result; }

            result.Code = 0;
            result.Msg = "成功!";

            return result;
        }

        #endregion
        /// <summary>
        /// 获取接团信息Info
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public async Task<Result> PostGroupInfo(GroupInfoDto dto)
        {
            Result result = new Result() { Code = -2, Msg = "未知错误" };

            if (dto.PortType == 1 || dto.PortType == 2 || dto.PortType == 3) //Web Or Android
            {
                string sql = string.Format(@"Select Id,SalesQuoteNo,TourCode,JietuanOperator,TeamLevSId,TeamDid,TeamName,ClientName,
	                                                ClientUnit,VisitCountry,VisitDate,VisitDays,VisitPNumber,TontractTime,
	                                                PayDay,PaymentMoney,VisitPurpose,SpecialNeeds,OtherNeeds,CGRWSPBMMC,CGRWSPWH,
	                                                ZZSCBMMC,ZZSCSPWH,Remark,TellPhone,Officialneeds
                                             From Grp_DelegationInfo Where Id = {0} And IsDel = 0", dto.Id);

                var _DelegationInfo = await _sqlSugar.SqlQueryable<DelegationInfoWebView>(sql).FirstAsync();
                if (_DelegationInfo != null)
                {
                    _DelegationInfo.TeamName = FormartTeamName(_DelegationInfo.TeamName);
                    result.Code = 0;
                    result.Msg = "成功!";
                    result.Data = _DelegationInfo;
                }
                else result.Msg = "暂无该团组信息";
            }

            return result;

        }
        #region 团组


        /// <summary>
        /// 统一团组国家分割字符
        /// </summary>
        /// <param name="teamName"></param>
        /// <returns></returns>
        public string FormartTeamName(string teamName) 
        {
            string str = "";
            if (!string.IsNullOrEmpty(teamName))
            {
                str = teamName;
                List<string> searchreplaces = new List<string>() { "|", " ", "/", ",", "，" };
                foreach (var item in searchreplaces)
                {
                    str = str.Replace(item, "、");
                }
            }
            return str.Trim();
        }

        /// <summary>
        /// 团组国家分割字符
        /// 返回 国家数组
        /// </summary>
        /// <param name="teamName"></param>
        /// <returns></returns>
        public List<string> GroupSplitCountry (string visitCountry)
        {
            List<string> countrys = new List<string>();

            if (!string.IsNullOrEmpty(visitCountry))
            {
                if (visitCountry.Contains("、")) countrys = visitCountry.Split("、").ToList();
                else if (visitCountry.Contains("|")) countrys = visitCountry.Split("|").ToList();
                else countrys.Add(visitCountry);
            }
            return countrys;
        }


        /// <summary>
        /// 获取接团信息 Page List
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public async Task<Result> GetGroupPageList(GroupCommissionDto dto)
        {
            Result result = new Result() { Code = -2, Msg = "未知错误" };

            ListViewBase<DelegationPageListView> groupsInfoPageList = new ListViewBase<DelegationPageListView>()
            {
                ReceiveDt = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
            };

            if (dto.PortType == 1 || dto.PortType == 2 || dto.PortType == 3) //web
            {
                int startIndex = (dto.PageIndex - 1) * dto.PageSize + 1;
                int endIndex = startIndex + dto.PageSize - 1;

                int taskUserId = -1;
                string taskGroupIds = string.Empty;
                #region 处理用户团组操作权限
                if (dto.UserId != -1)
                {

                }


                #endregion

                string sql = string.Format(@"Select * From(Select row_number() over (order by gdi.Id Desc) as RowNumber,
		                                        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
	                                        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.CreateUserId = su.Id
	                                        Where gdi.IsDel = 0 
                                        )temp Where RowNumber Between {0} and {1}", startIndex, endIndex);

                string sqlCount = string.Format(@"Select Count(1) as Count 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.CreateUserId = su.Id
	                                        Where gdi.IsDel = 0  ");

                List<DelegationPageListView> _DeleInfoList = await _sqlSugar.SqlQueryable<DelegationPageListView>(sql).ToListAsync();

                DelegationPageCountView _DeleCount = await _sqlSugar.SqlQueryable<DelegationPageCountView>(sqlCount).FirstAsync();

                int count = _DeleCount.Count;

                groupsInfoPageList = new ListViewBase<DelegationPageListView>
                {
                    CurrPageIndex = dto.PageIndex,
                    CurrPageSize = dto.PageSize,
                    DataCount = count,
                    DataList = _DeleInfoList
                };

                result.Code = 0;
                result.Msg = "成功!";
                result.Data = groupsInfoPageList;
            }


            return result;

        }


        /// <summary>
        /// 获取接团信息List
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public async Task<Result> GetGroupList(GroupListDto dto)
        {
            Result result = new Result() { Code = -2, Msg = "未知错误" };

            if (dto.PortType == 1) {
                string sql = string.Format(@"Select 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
                  ,gs.Id as GrpScheduleId
				  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.CreateUserId = su.Id
				  left Join Grp_Schedule gs On gdi.Id = gs.DiId
                  Where gdi.IsDel = 0 
                  Order By gdi.CreateTime Desc");

                var _DelegationList = await _sqlSugar.SqlQueryable<DelegationListView>(sql).ToListAsync();

                if (_DelegationList.Count > 0)
                {
                    result.Code = 0;
                    result.Msg = "成功!";
                    result.Data = _DelegationList;
                }
                else
                {
                    result.Msg = "暂无该团组信息";
                }
            }
            else if (dto.PortType == 2 || dto.PortType == 3) //web
            {
                string sql = string.Format(@"Select 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
                  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.CreateUserId = su.Id
                  Where gdi.IsDel = 0 
                  Order By gdi.CreateTime Desc");

                var _DelegationList = await _sqlSugar.SqlQueryable<DelegationListView>(sql).ToListAsync();

                if (_DelegationList.Count > 0)
                {
                    result.Code = 0;
                    result.Msg = "成功!";
                    result.Data = _DelegationList;
                }
                else
                {
                    result.Msg = "暂无该团组信息";
                }
            }


            return result;

        }

        /// <summary>
        /// 获取接团信息Info
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public async Task<Result> GetGroupInfo(GroupInfoDto dto)
        {
            Result result = new Result() { Code = -2, Msg = "未知错误" };

            if (dto.PortType == 1 || dto.PortType == 2 || dto.PortType == 3) //Web Or Android
            {
                string sql = string.Format(@"Select Id,SalesQuoteNo,TourCode,JietuanOperator,TeamLevSId,TeamDid,TeamName,ClientName,
	                                                ClientUnit,VisitCountry,VisitDate,VisitDays,VisitPNumber,TontractTime,
	                                                PayDay,PaymentMoney,VisitPurpose,SpecialNeeds,OtherNeeds,CGRWSPBMMC,CGRWSPWH,
	                                                ZZSCBMMC,ZZSCSPWH,Remark,TellPhone,WeChatNo,OpRoyaltyLv,OpRoyaltyRemark,Officialneeds
                                             From Grp_DelegationInfo Where Id = {0} And IsDel = 0", dto.Id);

                var _DelegationInfo = await _sqlSugar.SqlQueryable<DelegationInfoWebView>(sql).FirstAsync();
                if (_DelegationInfo != null)
                {
                    result.Code = 0;
                    result.Msg = "成功!";
                    result.Data = _DelegationInfo;

                    if (dto.PortType == 2)
                    {
                        var GroupProcessOperationDtoParam = new GroupProcessOperationDto();
                        GroupProcessOperationDtoParam = _mapper.Map<DelegationInfoWebView, GroupProcessOperationDto>(_DelegationInfo);
                        List<Crm_DeleClient> DeleClientArr = await _sqlSugar.Queryable<Crm_DeleClient>().Where(it => it.IsDel == 0).ToListAsync(); //客户信息
                        List<Grp_TourClientList> _DeleClients = await _sqlSugar.Queryable<Grp_TourClientList>().Where(it => it.IsDel == 0 && it.DiId == dto.Id).ToListAsync(); // 接团客户名单
                        List<Crm_CustomerCert> _CustomerCerts = await _sqlSugar.Queryable<Crm_CustomerCert>().Where(it => it.IsDel == 0 && it.SdId == 773).ToListAsync(); // 身份证类型证件信息
                        List<Crm_CustomerCompany> CompanyArr = await _sqlSugar.Queryable<Crm_CustomerCompany>().Where(it => it.IsDel == 0).ToListAsync();
                        var TourClientListInfoArr = new List<TourClientListProcessInfo>();
                        var timeParam = new DateTime();
                        foreach (var item in _DeleClients)
                        {
                            var param = new TourClientListProcessInfo();
                            var clientInfo = DeleClientArr.FirstOrDefault(x => x.Id == item.ClientId);
                            if (clientInfo == null) clientInfo = new Crm_DeleClient(); 
                            var clientIDInfo = _CustomerCerts.FirstOrDefault(x=>x.DcId == item.ClientId); //身份证信息
                            if (clientIDInfo == null) clientIDInfo = new Crm_CustomerCert();
                            var Company = CompanyArr.FirstOrDefault(x => x.Id == clientInfo.CrmCompanyId);
                            if(Company == null) Company = new Crm_CustomerCompany();

                            param.IDCardNo = clientIDInfo.CertNo;
                            param.Remark = item.Remark;
                            param.BirthDay = DateTime.TryParse(clientInfo.BirthDay, out timeParam) ? timeParam.ToString("yyyy-MM-dd") : "";
                            param.FirstName = clientInfo.FirstName;
                            param.LastName = clientInfo.LastName;
                            param.CompanyFullName = Company.CompanyFullName;
                            param.Job = clientInfo.Job;
                            param.Sex = clientInfo.Sex;
                            param.Phone = clientInfo.Phone;
                            param.Pinyin = clientInfo.Pinyin;
                            param.HotelSpecialNeeds = item.HotelSpecialNeeds;
                            param.MealSpecialNeeds = item.MealSpecialNeeds;
                            param.ShippingSpaceSpecialNeeds = item.ShippingSpaceSpecialNeeds;
                            param.ShippingSpaceTypeId = item.ShippingSpaceTypeId;
                            param.Id = clientInfo.Id;

                            TourClientListInfoArr.Add(param);
                        }

                        GroupProcessOperationDtoParam.PortType = dto.PortType;
                        GroupProcessOperationDtoParam.TourClientListInfos = TourClientListInfoArr;
                        GroupProcessOperationDtoParam.VisitDate = DateTime.TryParse(GroupProcessOperationDtoParam.VisitDate, out timeParam) ? timeParam.ToString("yyyy-MM-dd") : "";
                        GroupProcessOperationDtoParam.TontractTime = DateTime.TryParse(GroupProcessOperationDtoParam.TontractTime, out timeParam) ? timeParam.ToString("yyyy-MM-dd") : "";

                        result.Data = GroupProcessOperationDtoParam;
                    }
                    else if (dto.PortType == 3)
                    {
                        var GroupProcessOperationDtoParam = new GroupProcessOperationDto();
                        GroupProcessOperationDtoParam = _mapper.Map<DelegationInfoWebView, GroupProcessOperationDto>(_DelegationInfo);
                        List<Crm_DeleClient> DeleClientArr = await _sqlSugar.Queryable<Crm_DeleClient>().Where(it => it.IsDel == 0).ToListAsync(); //客户信息
                        List<Grp_TourClientList> _DeleClients = await _sqlSugar.Queryable<Grp_TourClientList>().Where(it => it.IsDel == 0 && it.DiId == dto.Id).ToListAsync(); // 接团客户名单
                        List<Crm_CustomerCert> _CustomerCerts = await _sqlSugar.Queryable<Crm_CustomerCert>().Where(it => it.IsDel == 0 && it.SdId == 773).ToListAsync(); // 身份证类型证件信息
                        List<Crm_CustomerCompany> CompanyArr = await _sqlSugar.Queryable<Crm_CustomerCompany>().Where(it => it.IsDel == 0).ToListAsync();
                        var TourClientListInfoArr = new List<TourClientListProcessInfo>();
                        var timeParam = new DateTime();
                        foreach (var item in _DeleClients)
                        {
                            var param = new TourClientListProcessInfo();
                            var clientInfo = DeleClientArr.FirstOrDefault(x => x.Id == item.ClientId);
                            if (clientInfo == null) clientInfo = new Crm_DeleClient();
                            var clientIDInfo = _CustomerCerts.FirstOrDefault(x => x.DcId == item.ClientId); //身份证信息
                            if (clientIDInfo == null) clientIDInfo = new Crm_CustomerCert();
                            var Company = CompanyArr.FirstOrDefault(x => x.Id == clientInfo.CrmCompanyId);
                            if (Company == null) Company = new Crm_CustomerCompany();

                            param.IDCardNo = clientIDInfo.CertNo;
                            param.Remark = item.Remark;
                            param.BirthDay = DateTime.TryParse(clientInfo.BirthDay, out timeParam) ? timeParam.ToString("yyyy-MM-dd") : "";
                            param.FirstName = clientInfo.FirstName;
                            param.LastName = clientInfo.LastName;
                            param.CompanyFullName = Company.CompanyFullName;
                            param.Job = clientInfo.Job;
                            param.Sex = clientInfo.Sex;
                            param.Phone = clientInfo.Phone;
                            param.Pinyin = clientInfo.Pinyin;
                            param.HotelSpecialNeeds = item.HotelSpecialNeeds;
                            param.MealSpecialNeeds = item.MealSpecialNeeds;
                            param.ShippingSpaceSpecialNeeds = item.ShippingSpaceSpecialNeeds;
                            param.ShippingSpaceTypeId = item.ShippingSpaceTypeId;
                            param.Id = item.Id;

                            TourClientListInfoArr.Add(param);
                        }

                        GroupProcessOperationDtoParam.PortType = dto.PortType;
                        GroupProcessOperationDtoParam.TourClientListInfos = TourClientListInfoArr;
                        GroupProcessOperationDtoParam.VisitDate = DateTime.TryParse(GroupProcessOperationDtoParam.VisitDate, out timeParam) ? timeParam.ToString("yyyy-MM-dd") : "";
                        GroupProcessOperationDtoParam.TontractTime = DateTime.TryParse(GroupProcessOperationDtoParam.TontractTime, out timeParam) ? timeParam.ToString("yyyy-MM-dd") : "";

                        result.Data = GroupProcessOperationDtoParam;
                    }

                }
                else result.Msg = "暂无该团组信息";
            }

            return result;
        }

        /// <summary>
        /// 获取接团信息 编辑 
        /// 基础 数据源
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public async Task<Result> GroupEditBasicSource(GroupListDto dto)
        {
            Result result = new Result() { Code = -2, Msg = "未知错误" };

            try
            {
                if (dto.PortType == 1 || dto.PortType == 2 || dto.PortType == 3) //web
                {
                    dynamic? teamTypeData1 = null;
                    string teamTypeSql = string.Format(@"Select Id,Name From  Sys_SetData Where STid = {0} And IsDel = {1}", 10, 0);
                    var teamTypeData = await _sqlSugar.SqlQueryable<SetDataInfoView>(teamTypeSql).ToListAsync(); ;  //团组分类 10
                    if (teamTypeData.Count > 0)
                    {
                        teamTypeData1 = teamTypeData;
                    }

                    dynamic? teamLevData1 = null;
                    string teamLevSql = string.Format(@"Select Id,Name From  Sys_SetData Where STid = {0} And IsDel = {1}", 56, 0);
                    var teamLevData = await _sqlSugar.SqlQueryable<SetDataInfoView>(teamLevSql).ToListAsync(); ;  //团组等级 56
                    if (teamLevData.Count > 0)
                    {
                        teamLevData1 = teamLevData;
                    }

                    dynamic? userData1 = null;
                    string userSql = string.Format(@"Select Id,CnName From  Sys_Users Where IsDel = {0}", 0);
                    var userData = await _sqlSugar.SqlQueryable<UserNameView>(userSql).ToListAsync(); 
                    if (userData.Count > 0)
                    {
                        userData1 = userData;
                    }

                    //客户单位数据源 来源市场客户资料
                    dynamic? clientData1 = null;
                    string clientSql = $@"Select Client,Contact,Telephone,WeChat From Crm_NewClientData Where IsDel = 0";
                    var clientData = await _sqlSugar.SqlQueryable<Crm_NewClientData>(clientSql).ToListAsync();
                    if (clientData.Count > 0)
                    {
                        clientData1 = clientData.Select(it => new { it.Client,it.Contact,it.Telephone,it.Wechat }).ToList();
                    }

                    result.Code = 0;
                    result.Msg = "成功!";
                    result.Data = new
                    {
                        teamTypeData = teamTypeData1,
                        teamLevData = teamLevData1,
                        userData = userData1,
                        clientData = clientData1
                    };
                }
            }
            catch (Exception ex)
            {

                result.Msg = ex.Message;
            }


            return result;
        }

        /// <summary>
        /// 团组信息操作
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public async Task<Result>GroupOperation(GroupOperationDto dto)
        {
            Result result = new Result() { Code = -2, Msg = "未知错误" };

            if (dto.PortType == 1 || dto.PortType == 2 || dto.PortType == 3) //web
            {
                #region 添加出访起止时间
                var startTime = new DateTime();
                var endTime = new DateTime();
                if (DateTime.TryParse(dto.VisitDate, out startTime))
                {
                    endTime = startTime.AddDays(dto.VisitDays);
                }
                #endregion

                if (dto.Status == 1) //添加
                {
                    string selectSql = string.Format(@"Select * From Grp_DelegationInfo 
                                                       Where IsDel = 0 
                                                       And TeamName = '{0}'
                                                       And ClientName = '{1}'
                                                       And ClientUnit ='{2}'
                                                       And VisitDate ='{3}'",dto.TeamName, dto.ClientName, dto.ClientUnit, dto.VisitDate);
                    var selectGroupInfo =  _sqlSugar.SqlQueryable<Grp_DelegationInfo>(selectSql).First();
                    if (selectGroupInfo != null)
                    {
                        result.Msg = "数据已存在,请勿重复添加!";
                        return result;
                    }

                    //string teamCodeSql = string.Format("Select SalesQuoteNo From Grp_DelegationInfo");
                    //var teamCodeItem = await _sqlSugar.SqlQueryable<TeamCodeView>(teamCodeSql).ToListAsync();
                    //string SalesQuoteNo = "";
                    //while (true)
                    //{
                    //    SalesQuoteNo = Tools.CommonFun.GetRandomStr(6);
                    //    if (!teamCodeItem.Equals(SalesQuoteNo))
                    //    {
                    //        break;
                    //    }
                    //}

                    string countrys = string.Empty;
                    string countryReq = dto.VisitCountry;
                    if (!string.IsNullOrEmpty(countryReq)) 
                    {
                        if (countryReq.Contains("，")) countrys = countryReq.Replace('，','|');
                        else if (countryReq.Contains(",")) countrys = countryReq.Replace(',', '|');
                        else if (countryReq.Contains(" ")) countrys = countryReq.Replace(' ', '|');
                        else if (countryReq.Contains("、")) countrys = countryReq.Replace('、', '|');
                        else if (countryReq.Contains(".")) countrys = countryReq.Replace('.', '|');
                        else countrys = countryReq;
                    }

                    Grp_DelegationInfo delegationInfo = new Grp_DelegationInfo()
                    {
                        SalesQuoteNo = dto.SalesQuoteNo,
                        TeamLevSId = dto.TeamLevSId,
                        ClientName = dto.ClientName,
                        ClientUnit = dto.ClientUnit,
                        TeamName = dto.TeamName,
                        TeamDid = dto.TeamDid,
                        VisitDate = Convert.ToDateTime(dto.VisitDate),
                        VisitCountry = countrys,
                        VisitPurpose = dto.VisitPurpose,
                        VisitDays = dto.VisitDays,
                        VisitPNumber = dto.VisitPNumber,
                        SpecialNeeds = dto.SpecialNeeds,
                        OtherNeeds = dto.OtherNeeds,
                        Remark = dto.Remark,
                        JietuanOperator = dto.JietuanOperator,
                        TellPhone = dto.TellPhone,
                        WeChatNo = dto.WeChatNo,
                        CGRWSPBMMC = dto.CGRWSPBMMC,
                        CGRWSPWH = dto.CGRWSPWH,
                        ZZSCBMMC = dto.ZZSCBMMC,
                        ZZSCSPWH = dto.ZZSCSPWH,
                        TontractTime = Convert.ToDateTime(dto.TontractTime),
                        PaymentMoney = dto.PaymentMoney,
                        PayDay = dto.PayDay,
                        TourCode = "",
                        SalesDate = DateTime.Now,
                        IsState = 0, //默认团组未完成 0
                        JietuanTime = DateTime.Now,
                        IsDel = 0,
                        BudgetCost = 0.00M,
                        HotelComplain = 0,
                        OPComplain = 0,
                        OAComplain = 0,
                        AirComplain = 0,
                        VisaComplain = 0,
                        CreateUserId = dto.UserId,
                        CreateTime = DateTime.Now,
                        DeleteUserId = null,
                        DeleteTime = new DateTime(1990, 1, 1).ToString("yyyy-MM-dd HH:mm:ss"),
                        OpRoyaltyLv = dto.OpRoyaltyLv,
                        OpRoyaltyRemark=dto.opRoyaltyRemark,
                        Officialneeds = dto.Officialneeds,
                        VisitStartDate = startTime, 
                        VisitEndDate = endTime,
                    };

                    var addId = _sqlSugar.Insertable(delegationInfo).ExecuteReturnIdentity();
                    if (addId > 0)
                    {
                        result.Code = 0;
                        result.Msg = "添加成功!";
                        result.Data = addId;
                    }
                    else
                    {
                        result.Msg = "添加失败!";
                    }


                }  
                else if (dto.Status == 2)     //修改
                {
                    var updateStatus = await UpdateAsync(a => a.Id == dto.Id, a => new Grp_DelegationInfo
                    {
                        SalesQuoteNo = dto.SalesQuoteNo,
                        TeamLevSId = dto.TeamLevSId,
                        ClientName = dto.ClientName,
                        ClientUnit = dto.ClientUnit,
                        TeamName = dto.TeamName,
                        TeamDid = dto.TeamDid,
                        VisitDate = Convert.ToDateTime(dto.VisitDate),
                        VisitCountry = dto.VisitCountry,
                        VisitPurpose = dto.VisitPurpose,
                        VisitDays = dto.VisitDays,
                        VisitPNumber = dto.VisitPNumber,
                        SpecialNeeds = dto.SpecialNeeds,
                        OtherNeeds = dto.OtherNeeds,
                        Remark = dto.Remark,
                        JietuanOperator = dto.JietuanOperator,
                        TellPhone = dto.TellPhone,
                        WeChatNo = dto.WeChatNo,
                        CGRWSPBMMC = dto.CGRWSPBMMC,
                        CGRWSPWH = dto.CGRWSPWH,
                        ZZSCBMMC = dto.ZZSCBMMC,
                        ZZSCSPWH = dto.ZZSCSPWH,
                        TontractTime = Convert.ToDateTime(dto.TontractTime),
                        PaymentMoney = dto.PaymentMoney,
                        PayDay = dto.PayDay,
                        OpRoyaltyLv = dto.OpRoyaltyLv,
                        OpRoyaltyRemark = dto.opRoyaltyRemark,
                        Officialneeds = dto.Officialneeds,
                        VisitStartDate = startTime,
                        VisitEndDate = endTime,
                    });

                    if (updateStatus)
                    {
                        result.Code = 0;
                        result.Msg = "修改成功!";
                    }
                    else
                    {
                        result.Msg = "修改失败!";
                    }
                }
                else if (dto.Status == 3)  //删除
                {
                    var deleteStatus = await UpdateAsync(a => a.Id == dto.Id, a => new Grp_DelegationInfo
                    {
                        DeleteUserId = dto.UserId,
                        DeleteTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"),
                        IsDel = 1
                    });

                    if (deleteStatus)
                    {
                        result.Code = 0;
                        result.Msg = "删除成功!";
                    }
                    else
                    {
                        result.Msg = "删除失败!";
                    }
                }
            }

            return result;
        }

        /// <summary>
        /// 团组信息操作 - 删除
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public async Task<Result> GroupDel(GroupDelDto dto)
        {
            Result result = new Result() { Code = -2, Msg = "未知错误" };

            if (dto.PortType == 1 || dto.PortType == 2 || dto.PortType == 3) //web
            {
                var deleteStatus = await UpdateAsync(a => a.Id == dto.Id, a => new Grp_DelegationInfo
                {
                    DeleteUserId = dto.UserId,
                    DeleteTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"),
                    IsDel = 1
                });

                if (deleteStatus)
                {
                    result.Code = 0;
                    result.Msg = "删除成功!";
                }
                else
                {
                    result.Msg = "删除失败!";
                }

            }

            return result;
        }

        /// <summary>
        /// 获取销售报价号
        /// </summary>
        /// <returns></returns>
        public async Task<Result> GetGroupSalesQuoteNo()
        {
            Result result = new Result() { Code = -2, Msg = "未知错误" };

            string teamCodeSql = string.Format("Select SalesQuoteNo From Grp_DelegationInfo");
            var teamCodeItem = await _sqlSugar.SqlQueryable<SalesQuoteNoView>(teamCodeSql).ToListAsync();

            string teamCode = "";
            while (true)
            {
                teamCode = Tools.CommonFun.GetRandomAllStr(6);

                if (!teamCodeItem.Equals(teamCode))
                {
                    break;
                }
            }

            result.Code = 0;
            result.Msg = "成功!";
            result.Data = teamCode;
            return result;
        }

        /// <summary>
        /// 团组删除
        /// </summary>
        /// <returns></returns>
        public async Task<Result> GroupDeleteById()
        {
            Result result = new Result() { Code = -2, Msg = "未知错误" };

            string teamCodeSql = string.Format("Select TourCode From Grp_DelegationInfo");
            var teamCodeItem = await _sqlSugar.SqlQueryable<TeamCodeView>(teamCodeSql).ToListAsync();

            string teamCode = "";
            while (true)
            {
                teamCode = Tools.CommonFun.GetRandomAllStr(6);

                if (!teamCodeItem.Equals(teamCode))
                {
                    break;
                }
            }

            result.Code = 0;
            result.Msg = "成功!";
            result.Data = teamCode;
            return result;
        }

        /// <summary>
        /// 团组确认出团
        /// </summary>
        /// <returns></returns>
        public async Task<Result> ConfirmationGroup(ConfirmationGroupDto dto)
        {
            Result result = new Result() { Code = -2, Msg = "未知错误" };


            string teamCode = "";
            if (dto.PortType == 1 || dto.PortType == 2 || dto.PortType == 3) //web
            {

                string teamCodeSql = string.Format("Select TourCode From Grp_DelegationInfo");
                var teamCodeItem = await _sqlSugar.SqlQueryable<TeamCodeView>(teamCodeSql).ToListAsync();

                while (true)
                {
                    teamCode = Tools.CommonFun.GetRandomAllStr(6);

                    if (!teamCodeItem.Equals(teamCode))
                    {
                        break;
                    }
                }


                var deleteStatus = await UpdateAsync(a => a.Id == dto.GroupId, a => new Grp_DelegationInfo
                {
                    TourCode = teamCode,
                    GroupsOperator = dto.GroupsOperator,
                    GroupsTime = DateTime.Now
                });

                if (deleteStatus)
                {
                    result.Code = 0;
                    result.Msg = "确认出团设置成功!";
                }
                else
                {
                    result.Msg = "确认出团设置失败!";
                }

            }

            result.Code = 0;
            result.Msg = "成功!";
            result.Data = teamCode;
            return result;
        }

        /// <summary>
        /// 获取接团名称List
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public async Task<Result> GetGroupNameList(GroupNameDto dto)
        {
            Result result = new Result() { Code = -2, Msg = "未知错误" };

            if (dto.PortType == 1 || dto.PortType == 2 || dto.PortType == 3) //web
            {
                string sql = string.Format(@"Select Id,TeamName GroupName From  Grp_DelegationInfo 
                                             Where TeamName != '' And IsDel = 0
                                             Order By Id Desc");

                var _groupNameList = await _sqlSugar.SqlQueryable<GroupNameView>(sql).ToListAsync();
                if (_groupNameList.Count > 0)
                {
                    result.Code = 0;
                    result.Msg = "成功!";
                    result.Data = _groupNameList;
                }
                else
                {
                    result.Msg = "暂无团组信息";
                }
            }
            return result;
        }

        /// <summary>
        /// 获取接团名称List And 签证国别
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public async Task<Result> GetGroupNameAndVisaNationality(GroupNameDto dto)
        {
            Result result = new Result() { Code = -2, Msg = "未知错误" };

            if (dto.PortType == 1 || dto.PortType == 2 || dto.PortType == 3) //web
            {
                string sql = string.Format(@"Select Id,TeamName GroupName From  Grp_DelegationInfo 
                                             Where TeamName != '' And IsDel = 0
                                             Order By Id Desc");
                var _groupNameList = await _sqlSugar.SqlQueryable<GroupNameView>(sql).ToListAsync();
                
                string visaSql = string.Format(@"Select Id,Name From Sys_SetData Where STid = 41 And IsDel = 0");
                var _setDataList = await _sqlSugar.SqlQueryable<SetDataInfoView>(visaSql).ToListAsync();

                var data = new {
                    groupNameData = _groupNameList,
                    visaNationalityData = _setDataList
                };

                result.Data = data;
                result.Code = 0;

            }
            return result;
        }

        #endregion

        #region 团组&签证

        /// <summary>
        /// 根据团组Id查询客户
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public async Task<Result> GetCrmByGroupId(ClientByGroupIdDto dto)
        {
            Result result = new Result() { Code = -2 };
            if (dto.PortType == 1 || dto.PortType == 2 || dto.PortType == 3)
            {
                string sql = string.Format(@"Select gdjc.GrpDCId grpId,cdc.Id,cdc.LastName+cdc.FirstName clientName,cdc.Tel,ccc.CertNo CerdNo 
                                             From  Grp_DelegationJoinCustomer gdjc
                                             Inner join Crm_DeleClient cdc On gdjc.CrmDCId =  cdc.Id
                                             Left Join Crm_CustomerCert ccc On ccc.SdId = 773 And  cdc.Id = ccc.DcId 
                                             Where gdjc.GrpDCId = {0}", dto.GroupId);


                var clientList = await _sqlSugar.SqlQueryable<CrmByGroupIdView>(sql).ToListAsync();

                if (clientList.Count > 0)
                {
                    result.Code = 0;
                    result.Msg = "成功!";
                    result.Data = clientList;
                }
                else
                {
                    result.Msg = "暂无数据!";
                }
            }

            return result;
        }


        #endregion
    }
}