using AutoMapper;
using Newtonsoft.Json;
using OASystem.Domain;
using OASystem.Domain.Dtos;
using OASystem.Domain.Dtos.CRM;
using OASystem.Domain.Dtos.UserDto;
using OASystem.Domain.Entities.Customer;
using OASystem.Domain.Entities.Groups;
using OASystem.Domain.Entities.Resource;
using OASystem.Domain.ViewModels.CRM;
using OASystem.Domain.ViewModels.Groups;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Intrinsics.X86;
using System.Text;
using System.Threading.Tasks;
namespace OASystem.Infrastructure.Repositories.CRM
{
/// <summary>
/// 签证客户 仓库
/// </summary>
public class VisaDeleClientRepository : BaseRepository<Crm_DeleClient, VisaDeleClientView>
{
private readonly IMapper _mapper;
public VisaDeleClientRepository(SqlSugarClient sqlSugar,IMapper mapper) :
base(sqlSugar)
{
this._mapper = mapper;
}
/// <summary>
/// 签证客户list
/// </summary>
/// <param name="loginDto"></param>
/// <returns></returns>
public async Task<Result> GetCrmList(DtoBase dto)
{
Result result = new Result() { Code = -2 };
if (dto.PortType == 1 || dto.PortType == 2)
{
string sql = string.Format(@"Select cdc.Id,LastName+FirstName ClientName,ccc.CompanyFullName,Sex,Marriage,
Phone LandlinePhone,Tel,crmCard1.CertNo IDNo,crmCard2.CertNo PassportNo
From Crm_DeleClient cdc
Left Join Crm_CustomerCompany ccc On cdc.crmCompanyId = ccc.Id
Left Join Crm_CustomerCert crmCard1 On crmCard1.SdId = 773 And cdc.Id = crmCard1.DcId And crmCard1.Isdel=0
Left Join Crm_CustomerCert crmCard2 On crmCard2.SdId = 774 And cdc.Id = crmCard2.DcId And crmCard2.IsDel=0 where cdc.IsDel='0' order By cdc.CreateTime desc");
var clientList = await _sqlSugar.SqlQueryable<VisaDeleClientListView>(sql).ToListAsync();
if (clientList.Count > 0)
{
result.Code = 0;
result.Msg = "成功!";
result.Data = clientList;
}
else
{
result.Msg = "暂无数据!";
}
}
return result;
}
/// <summary>
/// 签证客户操作
/// </summary>
/// <param name="loginDto"></param>
/// <returns></returns>
public async Task<Result> CrmClinetoperation(LoginDto loginDto)
{
Result result = new Result() { Code = -2 };
return result;
}
/// <summary>
/// 签证客户 新增
/// </summary>
/// <param name="loginDto"></param>
/// <returns></returns>
public async Task<int> CrmClinetAdd(Crm_DeleClient client)
{
int addId = -1;
return addId;
}
/// <summary>
/// 根据身份证识别修改ocr添加
/// </summary>
/// <param name="client"></param>
/// <returns></returns>
public async Task<bool> SetCrmUpdPassIdCardOCR(SetCrmUpdPassIdCardOCRDto client)
{
string clientSql = string.Format(@"Select * From Crm_DeleClient Where LastName+FirstName='{0}' And Sex = {1}",
client.ClientName, client.Sex);
var clientInfo = await _sqlSugar.SqlQueryable<VisaDeleClientListView>(clientSql).FirstAsync();
if(clientInfo == null) return false;
string cardSql = string.Format(@"Select * From Crm_CustomerCert Where SdId=773 And DcId={0}", clientInfo.Id);
var cardInfo = await _sqlSugar.SqlQueryable<CustomerCertView>(cardSql).FirstAsync();
if (cardInfo == null) //添加
{
int cerdAdd = await _sqlSugar.Insertable<Crm_CustomerCert>(new Crm_CustomerCert
{
DcId = clientInfo.Id,
SdId = 773,
Country = "中国",
CertNo = client.CerdNo,
TargetCountry = "",
IssueDt = new DateTime(1990, 1, 1),
ExpiryDt = new DateTime(1990, 1, 1),
CreateUserId = client.UserId,
CreateTime = DateTime.Now,
DeleteUserId = null,
DeleteTime = "1990-01-01 00:00:00.000",
Remark = "",
IsDel = 0
}).ExecuteReturnIdentityAsync();
if (cerdAdd > 0) return true;
}
else //修改
{
var cerdStatus = await _sqlSugar.Updateable<Crm_CustomerCert>()
.Where(c => c.Id == cardInfo.Id)
.SetColumns(c => new Crm_CustomerCert
{
CertNo = cardInfo.CertNo,
IDCardAddress = cardInfo.IDCardAddress
}).ExecuteCommandAsync();
if (cerdStatus > 0) return true;
}
return false;
}
public async Task<Result> OpCustomer(DeleClientOpDto dto)
{
Result result = new Result() { Code = -2, Msg = "未知错误" };
try
{
BeginTran();
int deleId = 0;
if (dto.Status==1)//添加
{
string selectSql = string.Format(@"select * from Crm_DeleClient where LastName+FirstName='{0}' and Phone='{1}' and IsDel='{2}'"
, dto.DeleClient.LastName+dto.DeleClient.FirstName, dto.DeleClient.Phone, 0);
var DeleClient = await _sqlSugar.SqlQueryable<Crm_DeleClient>(selectSql).FirstAsync();//查询是否存在
if (DeleClient != null)
{
result = new Result() { Code = -1, Msg = "该客户已存在，请勿重复添加！" };
}
else//不存在，可添加
{
Crm_DeleClient _CountryFeeCost = _mapper.Map<Crm_DeleClient>(dto.DeleClient);
int id = await AddAsyncReturnId(_CountryFeeCost);
if (id == 0)
{
result = new Result() { Code = -1, Msg = "添加失败！" };
}
result = new Result() { Code = 0, Msg = "添加成功！" };
deleId = id;
}
}
else if (dto.Status == 2)//修改
{
deleId=dto.DeleClient.Id;
bool res = await UpdateAsync(a => a.Id == dto.DeleClient.Id, a => new Crm_DeleClient
{
DiId = dto.DeleClient.DiId,
LastName = dto.DeleClient.LastName,
FirstName = dto.DeleClient.FirstName,
OldName = dto.DeleClient.OldName,
Pinyin = dto.DeleClient.Pinyin,
Sex = dto.DeleClient.Sex,
Marriage = dto.DeleClient.Marriage,
Phone = dto.DeleClient.Phone,
BirthProvince = dto.DeleClient.BirthProvince,
BirthCity = dto.DeleClient.BirthCity,
BirthDay = dto.DeleClient.BirthDay,
AirType = dto.DeleClient.AirType,
SeatPref = dto.DeleClient.SeatPref,
AirRemark = dto.DeleClient.AirRemark,
RoomType = dto.DeleClient.RoomType,
RoomPref = dto.DeleClient.RoomPref,
Tel = dto.DeleClient.Tel,
Email = dto.DeleClient.Email,
Address = dto.DeleClient.Address,
HighestEducation = dto.DeleClient.HighestEducation,
PostCodes = dto.DeleClient.PostCodes,
CrmCompanyId = dto.DeleClient.CrmCompanyId,
Job = dto.DeleClient.Job,
WorkState = dto.DeleClient.WorkState,
WorkDate = dto.DeleClient.WorkDate,
Wage = dto.DeleClient.Wage,
ClientPhone = dto.DeleClient.ClientPhone,
ClientFax = dto.DeleClient.ClientFax,
ClientEmail = dto.DeleClient.ClientEmail,
ClientLeader = dto.DeleClient.ClientLeader,
ClientLeaderJob = dto.DeleClient.ClientLeaderJob,
IsGetSchengen = dto.DeleClient.IsGetSchengen,
StartTime = dto.DeleClient.StartTime,
EndTime = dto.DeleClient.EndTime,
IsFinger = dto.DeleClient.IsFinger,
FingerDate = dto.DeleClient.FingerDate,
IsVisitUC = dto.DeleClient.IsVisitUC,
USADate = dto.DeleClient.USADate,
USADays = dto.DeleClient.USADays,
CanDate = dto.DeleClient.CanDate,
CanDays = dto.DeleClient.CanDays,
IsUSAVia = dto.DeleClient.IsUSAVia,
GetUSAVisaDate = dto.DeleClient.GetUSAVisaDate,
GetUPPlace = dto.DeleClient.GetUPPlace,
USAVisaCate = dto.DeleClient.USAVisaCate,
USAVisaCode = dto.DeleClient.USAVisaCode,
USAFinger = dto.DeleClient.USAFinger,
IsRejected = dto.DeleClient.IsRejected,
RejectedDate = dto.DeleClient.RejectedDate,
RejectedPlace = dto.DeleClient.RejectedPlace,
RejectedVisa = dto.DeleClient.RejectedVisa,
IsRevoke = dto.DeleClient.IsRevoke,
IsLose = dto.DeleClient.IsLose,
LoseDate = dto.DeleClient.LoseDate,
LoseCode = dto.DeleClient.LoseCode,
IsUSAVisa = dto.DeleClient.IsUSAVisa,
MateUSA = dto.DeleClient.MateUSA,
MateIden = dto.DeleClient.MateIden,
WHUSA = dto.DeleClient.WHUSA,
WHIden = dto.DeleClient.WHIden,
ParentUSA = dto.DeleClient.ParentUSA,
ParentIden = dto.DeleClient.ParentIden,
ChildUSA = dto.DeleClient.ChildUSA,
ChildIden = dto.DeleClient.ChildIden,
BroUSA = dto.DeleClient.BroUSA,
BroIden = dto.DeleClient.BroIden,
Social = dto.DeleClient.Social,
IsArmy = dto.DeleClient.IsArmy,
ArmyState = dto.DeleClient.ArmyState,
CostBearers = dto.DeleClient.CostBearers,
TableOpName = dto.DeleClient.TableOpName,
TableOpTel = dto.DeleClient.TableOpTel,
TableDate = dto.DeleClient.TableDate,
Party = dto.DeleClient.Party,
Nationality = dto.DeleClient.Nationality,
WeddingDate = dto.DeleClient.WeddingDate,
DivorceDate = dto.DeleClient.DivorceDate,
MateName = dto.DeleClient.MateName,
MateBirthDay = dto.DeleClient.MateBirthDay,
MateBirthCity = dto.DeleClient.MateBirthCity,
MateBirthCountry = dto.DeleClient.MateBirthCountry,
MateAddress = dto.DeleClient.MateAddress,
MateClient = dto.DeleClient.MateClient,
MateClientAddress = dto.DeleClient.MateClientAddress,
MateJob = dto.DeleClient.MateJob,
VisitCountry = dto.DeleClient.VisitCountry,
Paper = dto.DeleClient.Paper,
Papent = dto.DeleClient.Papent,
PhD = dto.DeleClient.PhD,
CreateUserId = dto.DeleClient.CreateUserId,
Remark = dto.DeleClient.Remark,
});
if (!res)
{
result = new Result() { Code = -1, Msg = "修改失败！" };
}
result = new Result() { Code = 0, Msg = "修改成功！" };
}
//进行其他表
if (result.Code == 0)
{//家庭成员信息
foreach (CustomerFamily item in dto.CustomerFamily)
{
Crm_VisaCustomerFamily Crm_VisaCustomerFamily = _mapper.Map<Crm_VisaCustomerFamily>(item);
Crm_VisaCustomerFamily.DcId = deleId;
if (item.Id != 0)//修改
{
int res = await _sqlSugar.Updateable<Crm_VisaCustomerFamily>().Where(a => a.Id == Crm_VisaCustomerFamily.Id).SetColumns(a => new Crm_VisaCustomerFamily
{
Appellation = Crm_VisaCustomerFamily.Appellation,
Name = Crm_VisaCustomerFamily.Name,
BirthDay = Crm_VisaCustomerFamily.BirthDay,
BirthPlace = Crm_VisaCustomerFamily.BirthPlace,
Politics = Crm_VisaCustomerFamily.Politics,
Client = Crm_VisaCustomerFamily.Client,
Address = Crm_VisaCustomerFamily.Address,
IsEu = Crm_VisaCustomerFamily.IsEu,
NameSnd = Crm_VisaCustomerFamily.NameSnd,
BirthDaySnd = Crm_VisaCustomerFamily.BirthDaySnd,
Nationality = Crm_VisaCustomerFamily.Nationality,
IDCard = Crm_VisaCustomerFamily.IDCard,
Reletionship = Crm_VisaCustomerFamily.Reletionship,
IsUSA = Crm_VisaCustomerFamily.IsUSA,
Remark = Crm_VisaCustomerFamily.Remark,
}).ExecuteCommandAsync();
if (res==0)
{
result = new Result() { Code = -1, Msg = "家庭成员信息保存失败！" };
RollbackTran();
}
}
else if (item.Id == 0)//添加
{
int sss= await _sqlSugar.Insertable(Crm_VisaCustomerFamily).ExecuteReturnIdentityAsync();
if (sss == 0)
{
result = new Result() { Code = -1, Msg = "家庭成员信息保存失败！" };
RollbackTran();
}
}
}
//证件表信息
foreach (CustomerCerts item in dto.CustomerCert)
{
Crm_CustomerCert CustomerCert = _mapper.Map<Crm_CustomerCert>(item);
CustomerCert.DcId = deleId;
if (item.Id != 0)//修改
{
int res = await _sqlSugar.Updateable<Crm_CustomerCert>().Where(a => a.Id == CustomerCert.Id).SetColumns(a => new Crm_CustomerCert
{
DcId=CustomerCert.DcId,
SdId = CustomerCert.SdId,
CertNo = CustomerCert.CertNo,
Country = CustomerCert.Country,
Area = CustomerCert.Area,
TargetCountry = CustomerCert.TargetCountry,
IssueDt = CustomerCert.IssueDt,
ExpiryDt = CustomerCert.ExpiryDt,
IDCardAddress = CustomerCert.IDCardAddress,
CreateUserId = CustomerCert.CreateUserId,
Remark = CustomerCert.Remark,
}).ExecuteCommandAsync();
if (res == 0)
{
result = new Result() { Code = -1, Msg = "证件信息保存失败！" };
RollbackTran();
}
}
else if (item.Id == 0)//添加
{
int sss = await _sqlSugar.Insertable(CustomerCert).ExecuteReturnIdentityAsync();
if (sss == 0)
{
result = new Result() { Code = -1, Msg = "证件信息保存失败！" };
RollbackTran();
}
}
}
//客户工作经历表
foreach (VisaCustomerCompany item in dto.WorkExperience)
{
Crm_VisaCustomerCompany VisaCustomerCompany = _mapper.Map<Crm_VisaCustomerCompany>(item);
VisaCustomerCompany.DcId = deleId;
if (item.Id != 0)//修改
{
int res = await _sqlSugar.Updateable<Crm_VisaCustomerCompany>().Where(a => a.Id == VisaCustomerCompany.Id).SetColumns(a => new Crm_VisaCustomerCompany
{
DcId=VisaCustomerCompany.DcId,
Company = VisaCustomerCompany.Company,
CompanyAddress = VisaCustomerCompany.CompanyAddress,
Phone = VisaCustomerCompany.Phone,
Job = VisaCustomerCompany.Job,
LeaderName = VisaCustomerCompany.LeaderName,
WorkStart = VisaCustomerCompany.WorkStart,
WorkEnd = VisaCustomerCompany.WorkEnd,
WorkState = VisaCustomerCompany.WorkState,
CreateUserId = VisaCustomerCompany.CreateUserId,
Remark = VisaCustomerCompany.Remark,
}).ExecuteCommandAsync();
if (res == 0)
{
result = new Result() { Code = -1, Msg = "客户工作经历保存失败！" };
RollbackTran();
}
}
else if (item.Id == 0)//添加
{
int sss = await _sqlSugar.Insertable(VisaCustomerCompany).ExecuteReturnIdentityAsync();
if (sss == 0)
{
result = new Result() { Code = -1, Msg = "客户工作经历保存失败！" };
RollbackTran();
}
}
}
//客户学历表
foreach (VisaCustomerSchool item in dto.CustomerSchool)
{
Crm_VisaCustomerSchool VisaCustomerSchool = _mapper.Map<Crm_VisaCustomerSchool>(item);
VisaCustomerSchool.DcId = deleId;
if (item.Id != 0)//修改
{
int res = await _sqlSugar.Updateable<Crm_VisaCustomerSchool>().Where(a => a.Id == VisaCustomerSchool.Id).SetColumns(a => new Crm_VisaCustomerSchool
{
DcId = VisaCustomerSchool.DcId,
School = VisaCustomerSchool.School,
Address = VisaCustomerSchool.Address,
Teacher = VisaCustomerSchool.Teacher,
Education = VisaCustomerSchool.Education,
Subject = VisaCustomerSchool.Subject,
StudyStart = VisaCustomerSchool.StudyStart,
StudyEnd = VisaCustomerSchool.StudyEnd,
CreateUserId = VisaCustomerSchool.CreateUserId,
Remark = VisaCustomerSchool.Remark,
}).ExecuteCommandAsync();
if (res == 0)
{
result = new Result() { Code = -1, Msg = "客户学历信息保存失败！" };
RollbackTran();
}
}
else if (item.Id == 0)//添加
{
int sss = await _sqlSugar.Insertable(VisaCustomerSchool).ExecuteReturnIdentityAsync();
if (sss == 0)
{
result = new Result() { Code = -1, Msg = "客户学历信息保存失败！" };
RollbackTran();
}
}
}
CommitTran();
}
}
catch (Exception ex)
{
return result = new Result() { Code = -2, Msg = "程序错误！" };
throw;
}
return result;
}
public async Task<Result> DelCustomer(DeleClientDelDto dto)
{
Result result = new Result() { Code = -2, Msg = "未知错误" };
try
{
bool isOk=await SoftDeleteByIdAsync<Crm_DeleClient>(dto.Id.ToString(),dto.DeleteUserId);
if (isOk)//删除其他表数据
{
//客户工作经历
await _sqlSugar.Updateable<Crm_VisaCustomerCompany>().Where(a => a.DcId==dto.Id).SetColumns(a => new Crm_VisaCustomerCompany()
{
IsDel = 1,
DeleteUserId = dto.DeleteUserId,
DeleteTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
}).ExecuteCommandAsync();
//客户学历
await _sqlSugar.Updateable<Crm_VisaCustomerSchool>().Where(a => a.DcId == dto.Id).SetColumns(a => new Crm_VisaCustomerSchool()
{
IsDel = 1,
DeleteUserId = dto.DeleteUserId,
DeleteTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
}).ExecuteCommandAsync();
//客户家庭成员信息
await _sqlSugar.Updateable<Crm_VisaCustomerFamily>().Where(a => a.DcId == dto.Id).SetColumns(a => new Crm_VisaCustomerFamily()
{
IsDel = 1,
DeleteUserId = dto.DeleteUserId,
DeleteTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
}).ExecuteCommandAsync();
//客户证件表
await _sqlSugar.Updateable<Crm_CustomerCert>().Where(a => a.DcId == dto.Id).SetColumns(a => new Crm_CustomerCert()
{
IsDel = 1,
DeleteUserId = dto.DeleteUserId,
DeleteTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
}).ExecuteCommandAsync();
return result = new Result() { Code = 0, Msg = "删除成功！" };
}
}
catch (Exception ex)
{
return result = new Result() { Code = -2, Msg = "程序错误！" };
throw;
}
return result;
}
}
}