using AutoMapper;
using MathNet.Numerics.Distributions;
using NPOI.SS.Formula.PTG;
using NPOI.Util;
using OASystem.Domain;
using OASystem.Domain.AesEncryption;
using OASystem.Domain.Dtos.CRM;
using OASystem.Domain.Dtos.Groups;
using OASystem.Domain.Entities.Customer;
using OASystem.Domain.Entities.Groups;
using OASystem.Domain.ViewModels.Financial;
using OASystem.Domain.ViewModels.Groups;
using OASystem.Infrastructure.Tools;
using Org.BouncyCastle.Utilities.Encoders;
using StackExchange.Redis;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Diagnostics;
using System.Globalization;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace OASystem.Infrastructure.Repositories.Groups
{
/// <summary>
/// 接团客户名单
/// 仓库
/// </summary>
public class TourClientListRepository :BaseRepository<Grp_TourClientList,TourClientListView>
{
private readonly Result _result;
private readonly IMapper _mapper;
public TourClientListRepository(SqlSugarClient sqlSugar, IMapper mapper)
: base(sqlSugar)
{
_result = new Result() { Code = -1, Msg = "操作失败!" };
_mapper = mapper;
}
/// <summary>
/// 根据团组Id客人名单List
/// </summary>
/// <param name="portId"></param>
/// <param name="diId"></param>
/// <returns></returns>
public async Task<List<SimplClientInfo>> _GuestNameItemByDiId(int portId, int diId)
{
List<SimplClientInfo> simplClients = new List<SimplClientInfo>();
if (portId == 1 || portId == 2 || portId == 3) // 1 web 2 Android 3 ios
{
string sql = $@"Select b.Id,b.Pinyin,b.lastName,b.firstName,b.phone From Grp_TourClientList a, Crm_DeleClient b
Where a.clientid = b.id and a.isdel = 0 and a.diId = {diId}";
simplClients = await _sqlSugar.SqlQueryable<SimplClientInfo>(sql).ToListAsync();
foreach (var item in simplClients)
{
EncryptionProcessor.DecryptProperties(item);
}
}
else
{
_result.Msg = "请传入有效的PortType参数,1 Web 2 Android 3 IOS!";
}
return simplClients;
}
/// <summary>
/// 根据团组Id查询List
/// </summary>
/// <param name="portId"></param>
/// <param name="diId"></param>
/// <returns></returns>
public async Task<Result> _ItemByDiId(int portId, int diId)
{
if (portId == 1 || portId == 2 || portId == 3 ) // 1 web 2 Android 3 ios
{
string sql = string.Format(@"
SELECT
tcl.Id,
tcl.DiId,
tcl.IsAccompany,
temp.*,
u.CnName AS Operator,
tcl.CreateTime AS OperatingTime
FROM
Grp_TourClientList tcl
LEFT JOIN (
SELECT
dc.Id AS DcId,
dc.LastName,
dc.FirstName,
ccom.CompanyFullName,
dc.Job,
cc.CertNo AS IDCardNo,
dc.Sex,
dc.BirthDay
FROM
Crm_DeleClient dc
LEFT JOIN Crm_CustomerCompany ccom ON dc.CrmCompanyId = ccom.Id
AND ccom.IsDel = 0
LEFT JOIN Crm_CustomerCert cc ON dc.Id = cc.DcId
AND cc.SdId = 773
AND cc.IsDel = 0
WHERE
dc.IsDel = 0
) temp ON temp.DcId = tcl.ClientId
LEFT JOIN Sys_Users u ON tcl.CreateUserId = u.Id
WHERE
tcl.IsDel = 0
AND tcl.DiId = {0}", diId);
var data = await _sqlSugar.SqlQueryable<TourClientListByDiIdView>(sql).ToListAsync();
foreach (var item in data)
{
EncryptionProcessor.DecryptProperties(item);
}
_result.Code = 0;
_result.Data = data;
}
else
{
_result.Msg = "请传入有效的PortType参数,1 Web 2 Android 3 IOS!";
}
return _result;
}
/// <summary>
/// 基础数据 Init
/// </summary>
/// <param name="portId"></param>
/// <returns></returns>
public async Task<JsonView> _BasicDataInit(int portId)
{
var view = new JsonView() { Code = StatusCodes.Status400BadRequest, Msg = "操作失败!" };
if (portId == 1 || portId == 2 || portId == 3) // 1 web 2 Android 3 ios
{
var setData = await _sqlSugar.Queryable<Sys_SetData>().Where(it => it.IsDel == 0) .ToListAsync();
var shippingSpaceTypeData = setData.Where(it => it.STid == 44).ToList(); //舱位类型
var shippingSpaceTypeData1 = _mapper.Map<List<SetDataInfoView>>(shippingSpaceTypeData);
//var passportTypeData = setData.Where(it => it.STid == 74).ToList(); //护照类型
//List<SetDataInfoView> _PassportTypeData = _mapper.Map<List<SetDataInfoView>>(passportTypeData);
//客户信息资料
var clientInfoSql = string.Format(@"Select dc.Id As DcId,dc.LastName,dc.FirstName,dc.Pinyin,dc.Sex,ccom.CompanyFullName,dc.Job,
cc1.CertNo As IDCardNo,dc.Phone,dc.BirthDay
From Crm_DeleClient dc
Left Join Crm_CustomerCompany ccom On dc.CrmCompanyId = ccom.Id And ccom.IsDel = 0
Left Join Crm_CustomerCert cc1 On dc.Id = cc1.DcId And cc1.SdId = 773 And cc1.IsDel = 0
Where dc.IsDel = 0");
var clientData = _sqlSugar.SqlQueryable<TourClientListDetailsView>(clientInfoSql).ToList();
//公司信息
var clientCompanySql = string.Format(@"Select Id,CompanyFullName From Crm_CustomerCompany Where IsDel = 0");
var clientCompanyData = _sqlSugar.SqlQueryable<CustomerCompanyCiew>(clientCompanySql).ToList();
clientCompanyData = clientCompanyData.DistinctBy(it => it.CompanyFullName).ToList();
foreach ( var item in clientData)
{
if (item.FirstName.Length > 5 && item.LastName.Length > 5)
{
EncryptionProcessor.DecryptProperties(item);
}
}
foreach (var item in clientCompanyData)
{
if (item.CompanyFullName.Length > 5)
{
EncryptionProcessor.DecryptProperties(item);
}
}
var _view = new {
ShippingSpaceTypeData = shippingSpaceTypeData1,
ClientData = clientData,
ClientCompanyData = clientCompanyData
//PassportTypeData = _PassportTypeData
};
view.Code = StatusCodes.Status200OK;
view.Data = _view;
}
else
{
view.Msg = "请传入有效的PortType参数,1 Web 2 Android 3 IOS!";
}
return view;
}
/// <summary>
/// 根据Id查询Details
/// </summary>
/// <param name="portId"></param>
/// <param name="id"></param>
/// <returns></returns>
public async Task<Result> _Details(int portId, int id)
{
if (portId == 1 || portId == 2 || portId == 3) // 1 web 2 Android 3 ios
{
string sql = string.Format(@"
SELECT
tcl.Id,
tcl.DiId,
tcl.IsAccompany,
temp.*,
tcl.ShippingSpaceTypeId,
tcl.ShippingSpaceSpecialNeeds,
tcl.HotelSpecialNeeds,
tcl.MealSpecialNeeds,
tcl.Remark
FROM
Grp_TourClientList tcl
LEFT JOIN (
SELECT
dc.Id AS DcId,
dc.LastName,
dc.FirstName,
dc.Pinyin,
dc.Sex,
ccom.CompanyFullName,
dc.Job,
cc1.CertNo AS IDCardNo,
dc.Phone,
dc.BirthDay,
cc2.PassportType,
cc2.CertNo AS PassportNo,
cc2.Country,
cc2.Area,
cc2.IssueDt,
cc2.ExpiryDt
FROM
Crm_DeleClient dc
LEFT JOIN Crm_CustomerCompany ccom ON dc.CrmCompanyId = ccom.Id
AND ccom.IsDel = 0
LEFT JOIN Crm_CustomerCert cc1 ON dc.Id = cc1.DcId
AND cc1.SdId = 773
AND cc1.IsDel = 0
LEFT JOIN Crm_CustomerCert cc2 ON dc.Id = cc2.DcId
AND cc2.SdId = 774
AND cc2.IsDel = 0
WHERE
dc.IsDel = 0
) temp ON temp.DcId = tcl.ClientId
WHERE
tcl.IsDel = 0
AND tcl.Id = {0}", id);
var data = await _sqlSugar.SqlQueryable<TourClientListDetailsView>(sql).FirstAsync();
if (data != null)
{
EncryptionProcessor.DecryptProperties(data);
data.BirthDay = data.BirthDay?.DateFormat("yyyy-MM-dd") ?? "";
_result.Code = 0;
_result.Data = data;
}
}
else
{
_result.Msg = "请传入有效的PortType参数,1 Web 2 Android 3 IOS!";
}
return _result;
}
/// <summary>
/// Add Or Edit
/// </summary>
/// <param name="portId"></param>
/// <param name="id"></param>
/// <returns></returns>
public async Task<Result> _AddOrEdit(TourClientListAddOrEditDto dto)
{
if (string.IsNullOrEmpty(dto.CompanyFullName))
{
_result.Msg = "客户单位名称为空!";
return _result;
}
//if (string.IsNullOrEmpty(dto.IDCardNo))
//{
// _result.Msg = "客户身份证No为空!";
// return _result;
//}
if (dto.ShippingSpaceTypeId < 0)
{
_result.Msg = "舱位类型为空!";
return _result;
}
if (dto.Id >= 0)
{
#region 参数处理
_sqlSugar.BeginTran();
int clientId = -1;
int crmCompanyId = -1;
string idNo = "", birthDay = "";
int sex = -1;
#region 身份证验证
if (!string.IsNullOrEmpty(dto.IDCardNo))
{
var idBool = dto.IDCardNo.IsValidChineseId();
if (idBool)
{
idNo = dto.IDCardNo;
DateTime? birthDayDt = CommonFun.GetBirthDateFromIdentityCard(dto.IDCardNo);
if (birthDayDt != null)
{
birthDay = birthDayDt?.ToString("yyyy-MM-dd") ?? "";
}
sex = CommonFun.GetGenderFromIdentityCard(dto.IDCardNo);
}
#region dto重新赋值
if (string.IsNullOrEmpty(dto.BirthDay)) dto.BirthDay = birthDay;
if (dto.Sex < 0) dto.Sex = sex;
#endregion
}
#endregion
EncryptionProcessor.EncryptProperties(dto);
var clientInfo = await _sqlSugar.Queryable<Crm_DeleClient>().Where(it => it.IsDel == 0
&& it.LastName.Equals(dto.LastName)
&& it.FirstName.Equals(dto.FirstName)
//&& it.Phone.Equals(dto.Phone)
).FirstAsync();
Crm_CustomerCompany _CustomerCompany = new Crm_CustomerCompany()
{
CompanyFullName = dto.CompanyFullName,
LastedOpUserId = dto.UserId,
CreateUserId = dto.UserId
};
Crm_CustomerCert _CustomerCert = new Crm_CustomerCert()
{
SdId = 773,
CertNo = dto.IDCardNo,
CreateUserId = dto.UserId
};
Crm_DeleClient _DeleClient = new Crm_DeleClient()
{
LastName = dto.LastName,
FirstName = dto.FirstName,
Pinyin = dto.Pinyin,
Phone = dto.Phone,
Sex = dto.Sex,
BirthDay = dto.BirthDay == "" ? null : Convert.ToDateTime(dto.BirthDay),
Job = dto.Job,
CreateUserId = dto.UserId
};
//客户单位/公司 操作
//1.添加 or 修改 公司基本信息
if (clientInfo == null)
{
var companyInfo = await _sqlSugar.Queryable<Crm_CustomerCompany>().Where(it => it.IsDel == 0 &&
it.CompanyFullName.Equals(dto.CompanyFullName)
).FirstAsync();
if (companyInfo != null) crmCompanyId = companyInfo.Id;
else
{
var companyAdd = await _sqlSugar.Insertable(_CustomerCompany).ExecuteReturnIdentityAsync();
if (companyAdd < 0)
{
_result.Msg = "客户公司信息添加失败!";
_sqlSugar.RollbackTran();
return _result;
}
crmCompanyId = companyAdd;
}
}
else
{
var companyInfo = await _sqlSugar.Queryable<Crm_CustomerCompany>().Where(it => it.IsDel == 0 &&
it.CompanyFullName.Equals(dto.CompanyFullName)
).FirstAsync();
if (companyInfo != null)
{
crmCompanyId = companyInfo.Id;
if (companyInfo.CompanyFullName.Equals(dto.CompanyFullName))
{
companyInfo.CompanyFullName = _CustomerCompany.CompanyFullName;
var companyEdit = await _sqlSugar.Updateable(companyInfo).UpdateColumns(it =>
new
{
it.CompanyFullName,
}
)
.Where(it => it.Id == companyInfo.Id)
.ExecuteCommandAsync();
if (companyEdit < 0)
{
_result.Msg = "客户公司信息修改失败!";
_sqlSugar.RollbackTran();
return _result;
}
}
}
else
{
var companyAdd = await _sqlSugar.Insertable(_CustomerCompany).ExecuteReturnIdentityAsync();
if (companyAdd < 0)
{
_result.Msg = "客户公司信息添加失败!";
_sqlSugar.RollbackTran();
return _result;
}
crmCompanyId = companyAdd;
}
}
_DeleClient.CrmCompanyId = crmCompanyId;
//客户人员 操作
//2.添加 or 修改 客户基本信息
if (clientInfo != null) //该客户存在 修改信息
{
clientId = clientInfo.Id;
var clientEdit = await _sqlSugar.Updateable(_DeleClient).UpdateColumns(it =>
new
{
it.CrmCompanyId,
it.LastName,
it.FirstName,
it.Pinyin,
it.Job,
it.Sex,
it.Phone,
it.BirthDay
}
)
.Where(it => it.Id == clientId)
.ExecuteCommandAsync();
if (clientEdit < 0)
{
_result.Msg = "客户基础信息修改失败!";
_sqlSugar.RollbackTran();
return _result;
}
}
else //不存在添加 客户信息
{
_DeleClient.CrmCompanyId = crmCompanyId;
var clientAdd = await _sqlSugar.Insertable(_DeleClient).ExecuteReturnIdentityAsync();
if (clientAdd < 0)
{
_result.Msg = "客户基本信息添加失败!";
_sqlSugar.RollbackTran();
return _result;
}
clientId = clientAdd;
}
//客户身份证操作
//3.添加 or 修改 身份证信息
_CustomerCert.DcId = clientId;
if (clientInfo == null)
{
var certInfo = await _sqlSugar.Queryable<Crm_CustomerCert>().Where(it => it.IsDel == 0 &&
it.SdId == 773 && //卡类型 身份证
it.CertNo == dto.IDCardNo //人员Id
).FirstAsync();
if (certInfo == null)
{
var certAdd = await _sqlSugar.Insertable(_CustomerCert).ExecuteReturnIdentityAsync();
if (certAdd < 0)
{
_result.Msg = "客户身份证添加失败!";
_sqlSugar.RollbackTran();
return _result;
}
}
}
else
{
var certInfo = await _sqlSugar.Queryable<Crm_CustomerCert>().Where(it => it.IsDel == 0 &&
it.SdId == 773 && //卡类型 身份证
it.DcId == clientInfo.Id //人员Id
).FirstAsync();
if (certInfo == null)
{
var certAdd = await _sqlSugar.Insertable(_CustomerCert).ExecuteReturnIdentityAsync();
if (certAdd < 0)
{
_result.Msg = "客户身份证添加失败!";
_sqlSugar.RollbackTran();
return _result;
}
}
else
{
var certEdit = await _sqlSugar.Updateable(_CustomerCert).UpdateColumns(it =>
new
{
it.CertNo,
}
)
.Where(it => it.Id == certInfo.Id)
.ExecuteCommandAsync();
if (certEdit < 0)
{
_result.Msg = "客户身份证修改失败!";
_sqlSugar.RollbackTran();
return _result;
}
}
}
//团组客户信息名单操作
Grp_TourClientList _TourClientList = new Grp_TourClientList()
{
IsAccompany = dto.IsAccompany,
DiId = dto.DiId,
ClientId = clientId,
ShippingSpaceTypeId = dto.ShippingSpaceTypeId,
ShippingSpaceSpecialNeeds = dto.ShippingSpaceSpecialNeeds,
HotelSpecialNeeds = dto.HotelSpecialNeeds,
MealSpecialNeeds = dto.MealSpecialNeeds,
Remark = dto.Remark ?? "",
CreateUserId = dto.UserId
};
#endregion
if (dto.Id == 0) // 添加
{
var tourClientAdd = await _sqlSugar.Insertable(_TourClientList).ExecuteReturnIdentityAsync();
if (tourClientAdd < 0)
{
_result.Msg = "接团客户名单添加失败!";
_sqlSugar.RollbackTran();
return _result;
}
}
else if (dto.Id > 0) //修改
{
var certEdit = await _sqlSugar.Updateable(_TourClientList).UpdateColumns(it =>
new
{
it.IsAccompany,
it.ClientId,
it.ShippingSpaceTypeId,
it.ShippingSpaceSpecialNeeds,
it.HotelSpecialNeeds,
it.MealSpecialNeeds,
it.Remark,
}
)
.Where(it => it.Id == dto.Id)
.ExecuteCommandAsync();
if (certEdit < 0)
{
_result.Msg = "接团客户名单失败修改!";
_sqlSugar.RollbackTran();
return _result;
}
}
_result.Code = 0;
_sqlSugar.CommitTran();
}
else
{
_result.Msg = "请传入有效的id参数!";
}
return _result;
}
/// <summary>
/// AddMultiple
/// 添加多个
/// </summary>
/// <param name="portId"></param>
/// <param name="id"></param>
/// <returns></returns>
public async Task<Result> _AddMultiple(TourClientListAddMultipleDto dto)
{
if (dto.DiId < 0)
{
_result.Msg = string.Format(@"请传入有效的团组Id参数!");
return _result;
}
List<TourClientListInfo> _TourClientListInfos = dto.TourClientListInfos;
if (_TourClientListInfos == null || _TourClientListInfos.Count < 0)
{
_result.Msg = string.Format(@"接团客户名单信息集合为空，不执行批量添加!");
return _result;
}
List<Crm_DeleClient> _DeleClients = await _sqlSugar.Queryable<Crm_DeleClient>().Where(it => it.IsDel == 0).ToListAsync();
List<Crm_CustomerCompany> _CustomerCompanies = await _sqlSugar.Queryable<Crm_CustomerCompany>().Where(it => it.IsDel == 0).ToListAsync();
List<Crm_CustomerCert> _CustomerCerts = await _sqlSugar.Queryable<Crm_CustomerCert>().Where(it => it.IsDel == 0 && it.SdId == 773).ToListAsync(); // 身份证类型证件信息
string _Msg = "";
int _DiId = dto.DiId;
int _UserId = dto.UserId;
foreach (var item in _TourClientListInfos)
{
EncryptionProcessor.EncryptProperties(item);
_sqlSugar.BeginTran();
int companyId = -1;
int clientId = -1;
string clientName = string.Format(@"{0}{1}", item.LastName, item.FirstName);
Crm_DeleClient _DeleClientInfo = _DeleClients.Where(it => it.LastName.Equals(item.LastName) &&
it.FirstName.Equals(item.FirstName) &&
it.Phone.Equals(item.Phone)).FirstOrDefault();
if (_DeleClientInfo == null) //添加
{
if (!string.IsNullOrEmpty(item.CompanyFullName))
{
var companyInfo = _CustomerCompanies.Where(it => it.CompanyFullName.Equals(item.CompanyFullName)).FirstOrDefault();
if (companyInfo != null) companyId = companyInfo.Id;
else
{
Crm_CustomerCompany _CustomerCompany = new Crm_CustomerCompany()
{
CompanyFullName = item.CompanyFullName,
LastedOpUserId = _UserId,
CreateUserId = _UserId
};
var companyAdd = await _sqlSugar.Insertable(_CustomerCompany).ExecuteReturnIdentityAsync();
if (companyAdd < 0)
{
_Msg += string.Format("{0} 公司信息添加失败!请前往客户信息确认!\r\n", clientName);
}
companyId = companyAdd;
}
}
_DeleClientInfo = new Crm_DeleClient()
{
LastName = item.LastName,
FirstName = item.FirstName,
Pinyin = item.Pinyin,
CrmCompanyId = companyId,
Job = item.Job,
Sex = item.Sex,
Phone = item.Phone
};
//string temp_birthDay = "";
DateTime tempDt_Birthday;
bool b_birth = DateTime.TryParse(item.BirthDay, out tempDt_Birthday);
_DeleClientInfo.BirthDay = b_birth ? tempDt_Birthday : null;
var clientAdd = await _sqlSugar.Insertable(_DeleClientInfo).ExecuteReturnIdentityAsync();
if (clientAdd < 0)
{
_Msg += string.Format("{0} 基本信息添加失败,本条客户名单不添加!请重新添加!\r\n", clientName);
_sqlSugar.RollbackTran();
continue;
}
clientId = clientAdd;
if (!string.IsNullOrEmpty(item.IDCardNo))
{
Crm_CustomerCert _CustomerCert = _CustomerCerts.Where(it => it.DcId == clientId && it.CertNo.Equals(item.IDCardNo)).FirstOrDefault();
if (_CustomerCert == null)
{
_CustomerCert = new Crm_CustomerCert()
{
DcId = clientId,
SdId = 773,
CertNo = item.IDCardNo,
CreateUserId = _UserId,
};
var customerCertAdd = await _sqlSugar.Insertable(_CustomerCert).ExecuteCommandAsync();
if (customerCertAdd < 0)
{
_Msg += string.Format("{0} 身份证信息添加失败!请前往客户信息确认!\r\n", clientName);
}
}
}
}
else //修改客户基本信息
{
clientId = _DeleClientInfo.Id;
if (!string.IsNullOrEmpty(item.CompanyFullName))
{
var companyInfo = _CustomerCompanies.Where(it => it.CompanyFullName.Equals(item.CompanyFullName)).FirstOrDefault();
if (companyInfo != null) companyId = companyInfo.Id;
else
{
Crm_CustomerCompany _CustomerCompany = new Crm_CustomerCompany()
{
CompanyFullName = item.CompanyFullName,
LastedOpUserId = _UserId,
CreateUserId = _UserId
};
var companyAdd = await _sqlSugar.Insertable(_CustomerCompany).ExecuteReturnEntityAsync();
if (companyAdd == null)
{
_Msg += string.Format("{0} 公司信息添加失败!请前往客户信息确认!\r\n", clientName);
}
_CustomerCompanies.Add(companyAdd);
companyId = companyAdd.Id;
}
}
Crm_DeleClient _DeleClient = new Crm_DeleClient() {
CrmCompanyId = companyId,
LastName = item.LastName,
FirstName = item.FirstName,
Pinyin = item.Pinyin,
Sex = item.Sex,
Phone = item.Phone
};
//string temp_birthDay = "";
DateTime tempDt_Birthday;
bool b_birth = DateTime.TryParse(item.BirthDay, out tempDt_Birthday);
_DeleClient.BirthDay = b_birth ? tempDt_Birthday : null;
var clientEdit = await _sqlSugar.Updateable(_DeleClient).UpdateColumns(it =>
new
{
it.CrmCompanyId,
it.LastName,
it.FirstName,
it.Sex,
it.Phone,
it.BirthDay
}
)
.Where(it => it.Id == clientId)
.ExecuteCommandAsync();
if (clientEdit < 0)
{
_Msg += string.Format("{0} 基本信息修改失败,!请前往客户信息修改!\r\n", clientName);
}
}
//团组客户信息名单操作
Grp_TourClientList _TourClientList = new Grp_TourClientList()
{
DiId = _DiId,
IsAccompany = item.IsAccompany,
ClientId = clientId,
ShippingSpaceTypeId = item.ShippingSpaceTypeId,
CreateUserId = _UserId,
ShippingSpaceSpecialNeeds = item.ShippingSpaceSpecialNeeds,
HotelSpecialNeeds = item.HotelSpecialNeeds,
MealSpecialNeeds = item.MealSpecialNeeds,
Remark = item.Remark
};
//判断现有团组客户名单是否存在该客户
var QuerFirstClient = _sqlSugar.Queryable<Grp_TourClientList>().First(x => x.ClientId == _TourClientList.ClientId && x.DiId == _TourClientList.DiId && x.IsDel == 0);
if (QuerFirstClient != null)
{
QuerFirstClient.IsDel = 1;
QuerFirstClient.DeleteUserId = dto.UserId;
QuerFirstClient.DeleteTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
var UpdateTrue = _sqlSugar.Updateable(QuerFirstClient).UpdateColumns(x => new { x.IsDel,x.DeleteUserId,x.DeleteTime}).ExecuteCommand() > 0;
}
var tourClientAdd = await _sqlSugar.Insertable(_TourClientList).ExecuteReturnIdentityAsync();
if (tourClientAdd < 0)
{
_Msg += string.Format("{0} 接团客户名单添加失败!请检查数据完整性后重新添加!\r\n", clientName);
_sqlSugar.RollbackTran();
continue;
}
_sqlSugar.CommitTran();
}
if (!string.IsNullOrEmpty(_Msg)) _result.Msg = _Msg;
else _result.Msg = string.Format("操作成功!");
_result.Code = 0;
return _result;
}
public async Task<Result> OperMultiple(List<TourClientListProcessInfo> _TourClientListInfos , int diid,int userId)
{
if (diid < 0)
{
_result.Msg = string.Format(@"请传入有效的团组Id参数!");
return _result;
}
if (_TourClientListInfos == null || _TourClientListInfos.Count < 0)
{
_result.Msg = string.Format(@"接团客户名单信息集合为空，不执行批量添加!");
return _result;
}
_sqlSugar.BeginTran();
try
{
_sqlSugar.Updateable<Grp_TourClientList>().SetColumns(x=> new Grp_TourClientList { IsDel = 1 } ).Where(x=>x.DiId == diid).ExecuteCommand();
foreach (var item in _TourClientListInfos)
{
EncryptionProcessor.EncryptProperties(item);
//客户信息
var clientInfo = await _sqlSugar.Queryable<Crm_DeleClient>().Where(it => it.IsDel == 0 &&
it.LastName.Equals(item.LastName) &&
it.FirstName.Equals(item.FirstName) &&
it.Phone.Equals(item.Phone)
).FirstAsync();
Crm_CustomerCompany _CustomerCompany = new Crm_CustomerCompany()
{
CompanyFullName = item.CompanyFullName,
LastedOpUserId = userId,
CreateUserId = userId
};
Crm_CustomerCert _CustomerCert = new Crm_CustomerCert()
{
SdId = 773,
CertNo = item.IDCardNo,
CreateUserId = userId
};
Crm_DeleClient _DeleClient = new Crm_DeleClient()
{
LastName = item.LastName,
FirstName = item.FirstName,
Pinyin = item.Pinyin,
Phone = item.Phone,
Sex = item.Sex,
BirthDay = string.IsNullOrEmpty(item.BirthDay) ? null : Convert.ToDateTime(item.BirthDay),
Job = item.Job,
CreateUserId = userId
};
int clientId = -1;
int crmCompanyId = -1;
//客户单位/公司 操作
//1.添加 or 修改 公司基本信息
if (clientInfo == null)
{
var companyInfo = await _sqlSugar.Queryable<Crm_CustomerCompany>().Where(it => it.IsDel == 0 &&
it.CompanyFullName.Equals(item.CompanyFullName)
).FirstAsync();
if (companyInfo != null) crmCompanyId = companyInfo.Id;
else
{
var companyAdd = await _sqlSugar.Insertable(_CustomerCompany).ExecuteReturnIdentityAsync();
if (companyAdd < 0)
{
_result.Msg = "客户公司信息添加失败!";
_sqlSugar.RollbackTran();
return _result;
}
crmCompanyId = companyAdd;
}
}
else
{
var companyInfo = await _sqlSugar.Queryable<Crm_CustomerCompany>().Where(it => it.IsDel == 0 &&
it.Id == clientInfo.CrmCompanyId
).FirstAsync();
if (companyInfo != null)
{
crmCompanyId = companyInfo.Id;
if (!companyInfo.CompanyFullName.Equals(item.CompanyFullName))
{
companyInfo.CompanyFullName = item.CompanyFullName;
var companyEdit = await _sqlSugar.Updateable(companyInfo).UpdateColumns(it =>
new
{
it.CompanyFullName,
}
)
.Where(it => it.Id == companyInfo.Id)
.ExecuteCommandAsync();
if (companyEdit < 0)
{
_result.Msg = "客户公司信息修改失败!";
_sqlSugar.RollbackTran();
return _result;
}
}
}
else
{
var companyAdd = await _sqlSugar.Insertable(_CustomerCompany).ExecuteReturnIdentityAsync();
if (companyAdd < 0)
{
_result.Msg = "客户公司信息添加失败!";
_sqlSugar.RollbackTran();
return _result;
}
crmCompanyId = companyAdd;
}
}
//客户人员 操作
//2.添加 or 修改 客户基本信息
if (clientInfo != null) //该客户存在 修改信息
{
clientId = clientInfo.Id;
_DeleClient.CrmCompanyId = crmCompanyId;
var clientEdit = await _sqlSugar.Updateable(_DeleClient).UpdateColumns(it =>
new
{
it.LastName,
it.FirstName,
it.Pinyin,
it.Sex,
it.Phone,
it.BirthDay
}
)
.Where(it => it.Id == clientId)
.ExecuteCommandAsync();
if (clientEdit < 0)
{
_result.Msg = "客户基础信息修改失败!";
_sqlSugar.RollbackTran();
return _result;
}
}
else //不存在添加 客户信息
{
_DeleClient.CrmCompanyId = crmCompanyId;
var clientAdd = await _sqlSugar.Insertable(_DeleClient).ExecuteReturnIdentityAsync();
if (clientAdd < 0)
{
_result.Msg = "客户基本信息添加失败!";
_sqlSugar.RollbackTran();
return _result;
}
clientId = clientAdd;
}
//客户身份证操作
//3.添加 or 修改 身份证信息
_CustomerCert.DcId = clientId;
if (clientInfo == null)
{
var certInfo = await _sqlSugar.Queryable<Crm_CustomerCert>().Where(it => it.IsDel == 0 &&
it.SdId == 773 && //卡类型 身份证
it.CertNo == item.IDCardNo //人员Id
).FirstAsync();
if (certInfo == null)
{
var certAdd = await _sqlSugar.Insertable(_CustomerCert).ExecuteReturnIdentityAsync();
if (certAdd < 0)
{
_result.Msg = "客户身份证添加失败!";
_sqlSugar.RollbackTran();
return _result;
}
}
}
else
{
var certInfo = await _sqlSugar.Queryable<Crm_CustomerCert>().Where(it => it.IsDel == 0 &&
it.SdId == 773 && //卡类型 身份证
it.DcId == clientInfo.Id //人员Id
).FirstAsync();
if (certInfo == null)
{
var certAdd = await _sqlSugar.Insertable(_CustomerCert).ExecuteReturnIdentityAsync();
if (certAdd < 0)
{
_result.Msg = "客户身份证添加失败!";
_sqlSugar.RollbackTran();
return _result;
}
}
else
{
var certEdit = await _sqlSugar.Updateable(_CustomerCert).UpdateColumns(it =>
new
{
it.CertNo,
}
)
.Where(it => it.Id == certInfo.Id)
.ExecuteCommandAsync();
if (certEdit < 0)
{
_result.Msg = "客户身份证修改失败!";
_sqlSugar.RollbackTran();
return _result;
}
}
}
//团组客户信息名单操作
Grp_TourClientList _TourClientList = new Grp_TourClientList()
{
DiId = diid,
ClientId = clientId,
ShippingSpaceTypeId = item.ShippingSpaceTypeId,
ShippingSpaceSpecialNeeds = item.ShippingSpaceSpecialNeeds,
HotelSpecialNeeds = item.HotelSpecialNeeds,
MealSpecialNeeds = item.MealSpecialNeeds,
Remark = item.Remark,
CreateUserId = userId,
IsAccompany = item.IsAccompany,
};
var tourClientAdd = await _sqlSugar.Insertable(_TourClientList).ExecuteReturnIdentityAsync();
if (tourClientAdd < 0)
{
_result.Msg = "接团客户名单添加失败!";
_sqlSugar.RollbackTran();
return _result;
}
}
_result.Code = 0;
_sqlSugar.CommitTran();
}
catch (Exception ex)
{
_sqlSugar.RollbackTran();
_result.Msg = "程序异常 -- " + ex.Message;
}
return _result;
}
/// <summary>
/// Del
/// </summary>
/// <param name="portId"></param>
/// <param name="id"></param>
/// <returns></returns>
public async Task<Result> _Del(int id, int userId)
{
if (id > 0)
{
Grp_TourClientList _TourClientList = new Grp_TourClientList()
{
Id = id,
IsDel = 1,
DeleteUserId = userId,
DeleteTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
};
var certEdit = await _sqlSugar.Updateable(_TourClientList).UpdateColumns(it =>
new
{
it.IsDel,
it.DeleteUserId,
it.DeleteTime,
}
)
.Where(it => it.Id == id)
.ExecuteCommandAsync();
if (certEdit < 0)
{
_result.Msg = string.Format(@"接团客户名单删除修改!");
return _result;
}
_result.Code = 0;
}
else
{
_result.Msg = string.Format(@"请传入有效的Id参数");
}
return _result;
}
/// <summary>
/// 分割客户名称
/// </summary>
/// <param name="ids"></param>
/// <returns></returns>
public string _ResolveCustomerName(string ids)
{
string clientStr = "";
if (string.IsNullOrEmpty(ids)) return clientStr;
List<int> intList = new List<int>();
//if (ids.Contains(","))
//{
// string[] numbers = ids.Split(',');
// foreach (string numberStr in numbers)
// {
// if (int.TryParse(numberStr.Trim(), out int number))
// {
// intList.Add(number);
// }
// }
//}
//else
//{
// if (int.TryParse(ids.Trim(), out int number))
// {
// intList.Add(number);
// }
//}
string sql = string.Format(@$"Select * From Crm_DeleClient Where IsDel = 0 And Id In ({ids})");
var infos = _sqlSugar.SqlQueryable<Crm_DeleClient>(sql).ToList();
if (infos.Count > 0)
{
var infos1 = infos.Select(it => it.LastName + it.FirstName).ToList();
clientStr = string.Join(",", infos1);
}
return clientStr;
}
private class CustomerNameInfo
{
public int Id { get; set; }
public string Name { get; set; }
}
}
}