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 With(NoLock) 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)
{
if (str.Contains(item))
{
str = str.Replace(item, "、");
break;
}
}
}
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,IsBid,
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)
{
_DelegationInfo.TeamName = FormartTeamName(_DelegationInfo.TeamName);
_DelegationInfo.VisitCountry = FormartTeamName(_DelegationInfo.VisitCountry);
//if (_DelegationInfo.TontractTime)
//{
//}
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 = clientInfo.BirthDay == null ? "" : DateTime.TryParse(Convert.ToDateTime( clientInfo.BirthDay).ToString("yyyy-MM-dd"), 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.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 = clientInfo.BirthDay == null ? "" : DateTime.TryParse(Convert.ToDateTime(clientInfo.BirthDay).ToString("yyyy-MM-dd"), 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 - 1);//含当天
}
#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;
}
//建团时 默认出团 设置 团组code
string teamCode="";
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;
}
}
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 = string.IsNullOrEmpty(dto.TontractTime) ? null : Convert.ToDateTime(dto.TontractTime),
IsBid = dto.IsBid,
PaymentMoney = dto.PaymentMoney,
PayDay = dto.PayDay,
TourCode = teamCode,
GroupsOperator = dto.UserId,
GroupsTime = DateTime.Now,
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 = string.IsNullOrEmpty(dto.TontractTime) ? null : Convert.ToDateTime(dto.TontractTime),
IsBid = dto.IsBid,
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)
{
for (int i = 0; i < _groupNameList.Count; i++)
{
var info = _groupNameList[i];
_groupNameList[i].GroupName = FormartTeamName(info.GroupName);
}
result.Code = 0;
result.Msg = "成功!";
result.Data = _groupNameList;
}
else
{
result.Msg = "暂无团组信息";
}
}
return result;
}
//PostGroupNameScreen
/// <summary>
/// 获取接团名称 Page List
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
public async Task<JsonView> GetGroupNameList(int portType,int pageIndex,int pageSize, string groupName,int userId)
{
var _res = new JsonView() { Code = 400 , Msg = MsgTips.Fail };
string sql = "";
string sqlWhere = "";
string mobileSqlWhere = "";
if (!string.IsNullOrEmpty(groupName))
{
sqlWhere += string.Format($@"And TeamName Like '%{groupName}%'");
mobileSqlWhere += string.Format($@"GroupName Like '%{groupName}%'");
}
List<int> userIds = new List<int>() { -1, 21 };
if (!userIds.Contains(userId))
{
sqlWhere += string.Format($@"And JietuanOperator = {userId}");
}
sql = string.Format($@"Select ROW_NUMBER()Over(Order By Id Desc) as RowNumber,Id,TeamName GroupName From Grp_DelegationInfo
Where IsDel = 0 {sqlWhere}");
if (portType == 1) //web
{ }
else if (portType == 2 || portType == 3)
{
//国交部 7 (主管 22，经理 32)团操操作权限 市场部 6 接团人
var userInfo = await _sqlSugar.Queryable<Sys_Users>().Where(it => it.Id == userId && it.IsDel == 0).FirstAsync();
if (userInfo != null)
{
if (userInfo.DepId == 7)
{
if (userInfo.JobPostId != 22 || userInfo.JobPostId != 32)
{
if (!string.IsNullOrEmpty(mobileSqlWhere)) mobileSqlWhere = $" Where {mobileSqlWhere}";
sql = string.Format($@"Select
ROW_NUMBER()Over(Order By Id Desc) as RowNumber,
Id,
GroupName
From (
Select
distinct
di.Id,
di.TeamName GroupName
From Grp_GroupsTaskAssignment gta With(NoLock)
Inner Join Grp_DelegationInfo di With(NoLock) On gta.DIId = di.Id
Where gta.IsDel = 0 And di.IsDel = 0 And gta.UId = {userId}
) Temp {mobileSqlWhere}");
}
}
}
}
RefAsync<int> total = 0;
var groupNamePageData = await _sqlSugar.SqlQueryable<GroupNamePageView>(sql).ToPageListAsync(pageIndex, pageSize, total);
_res.Code = 200;
_res.Data = groupNamePageData;
_res.Msg = MsgTips.Succeed;
_res.Count = total;
return _res;
}
/// <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
}
}