TourClientListRepository.cs 51 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852853854855856857858859860861862863864865866867868869870871872873874875876877878879880881882883884885886887888889890891892893894895896897898899900901902903904905906907908909910911912913914915916917918919920921922923924925926927928929930931932933934935936937938939940941942943944945946947948949950951952953954955956957958959960961962963964965966967968969970971972973974975976977978979980981982983984985986987988989990991992993994995996997998999100010011002100310041005100610071008100910101011101210131014101510161017101810191020102110221023102410251026102710281029103010311032103310341035103610371038103910401041104210431044104510461047104810491050105110521053105410551056105710581059106010611062106310641065106610671068
  1. using AutoMapper;
  2. using MathNet.Numerics.Distributions;
  3. using NPOI.SS.Formula.PTG;
  4. using NPOI.Util;
  5. using OASystem.Domain;
  6. using OASystem.Domain.Dtos.CRM;
  7. using OASystem.Domain.Dtos.Groups;
  8. using OASystem.Domain.Entities.Customer;
  9. using OASystem.Domain.Entities.Groups;
  10. using OASystem.Domain.ViewModels.Groups;
  11. using OASystem.Infrastructure.Tools;
  12. using Org.BouncyCastle.Utilities.Encoders;
  13. using StackExchange.Redis;
  14. using System;
  15. using System.Collections;
  16. using System.Collections.Generic;
  17. using System.Globalization;
  18. using System.Linq;
  19. using System.Text;
  20. using System.Threading.Tasks;
  21. namespace OASystem.Infrastructure.Repositories.Groups
  22. {
  23. /// <summary>
  24. /// 接团客户名单
  25. /// 仓库
  26. /// </summary>
  27. public class TourClientListRepository :BaseRepository<Grp_TourClientList,TourClientListView>
  28. {
  29. private readonly Result _result;
  30. private readonly IMapper _mapper;
  31. public TourClientListRepository(SqlSugarClient sqlSugar, IMapper mapper)
  32. : base(sqlSugar)
  33. {
  34. _result = new Result() { Code = -1, Msg = "操作失败!" };
  35. _mapper = mapper;
  36. }
  37. /// <summary>
  38. /// 根据团组Id客人名单List
  39. /// </summary>
  40. /// <param name="portId"></param>
  41. /// <param name="diId"></param>
  42. /// <returns></returns>
  43. public async Task<List<SimplClientInfo>> _GuestNameItemByDiId(int portId, int diId)
  44. {
  45. List<SimplClientInfo> simplClients = new List<SimplClientInfo>();
  46. if (portId == 1 || portId == 2 || portId == 3) // 1 web 2 Android 3 ios
  47. {
  48. string sql = $@"Select b.Id,b.Pinyin,b.lastName,b.firstName,b.phone From Grp_TourClientList a, Crm_DeleClient b
  49. Where a.clientid = b.id and a.isdel = 0 and a.diId = {diId}";
  50. simplClients = await _sqlSugar.SqlQueryable<SimplClientInfo>(sql).ToListAsync();
  51. }
  52. else
  53. {
  54. _result.Msg = "请传入有效的PortType参数,1 Web 2 Android 3 IOS!";
  55. }
  56. return simplClients;
  57. }
  58. /// <summary>
  59. /// 根据团组Id查询List
  60. /// </summary>
  61. /// <param name="portId"></param>
  62. /// <param name="diId"></param>
  63. /// <returns></returns>
  64. public async Task<Result> _ItemByDiId(int portId, int diId)
  65. {
  66. if (portId == 1 || portId == 2 || portId == 3 ) // 1 web 2 Android 3 ios
  67. {
  68. string sql = string.Format(@"Select tcl.Id,tcl.DiId,temp.* From Grp_TourClientList tcl
  69. Left Join
  70. (Select dc.Id As DcId,dc.LastName,dc.FirstName,ccom.CompanyFullName,dc.Job,
  71. cc.CertNo As IDCardNo,dc.Sex,dc.BirthDay
  72. From Crm_DeleClient dc
  73. Left Join Crm_CustomerCompany ccom On dc.CrmCompanyId = ccom.Id And ccom.IsDel = 0
  74. Left Join Crm_CustomerCert cc On dc.Id = cc.DcId And cc.SdId = 773 And cc.IsDel = 0
  75. Where dc.IsDel = 0) temp
  76. On temp.DcId =tcl.ClientId
  77. Where tcl.IsDel = 0 And tcl.DiId = {0}", diId);
  78. var data = await _sqlSugar.SqlQueryable<TourClientListByDiIdView>(sql).ToListAsync();
  79. _result.Code = 0;
  80. _result.Data = data;
  81. }
  82. else
  83. {
  84. _result.Msg = "请传入有效的PortType参数,1 Web 2 Android 3 IOS!";
  85. }
  86. return _result;
  87. }
  88. /// <summary>
  89. /// 基础数据 Init
  90. /// </summary>
  91. /// <param name="portId"></param>
  92. /// <returns></returns>
  93. public async Task<Result> _BasicDataInit(int portId)
  94. {
  95. if (portId == 1 || portId == 2 || portId == 3) // 1 web 2 Android 3 ios
  96. {
  97. var setData = await _sqlSugar.Queryable<Sys_SetData>().Where(it => it.IsDel == 0) .ToListAsync();
  98. var shippingSpaceTypeData = setData.Where(it => it.STid == 44).ToList(); //舱位类型
  99. List<SetDataInfoView> _ShippingSpaceTypeData = _mapper.Map<List<SetDataInfoView>>(shippingSpaceTypeData);
  100. //var passportTypeData = setData.Where(it => it.STid == 74).ToList(); //护照类型
  101. //List<SetDataInfoView> _PassportTypeData = _mapper.Map<List<SetDataInfoView>>(passportTypeData);
  102. //客户信息资料
  103. var clientInfoSql = string.Format(@"Select dc.Id As DcId,dc.LastName,dc.FirstName,dc.Pinyin,dc.Sex,ccom.CompanyFullName,dc.Job,
  104. cc1.CertNo As IDCardNo,dc.Phone,dc.BirthDay
  105. From Crm_DeleClient dc
  106. Left Join Crm_CustomerCompany ccom On dc.CrmCompanyId = ccom.Id And ccom.IsDel = 0
  107. Left Join Crm_CustomerCert cc1 On dc.Id = cc1.DcId And cc1.SdId = 773 And cc1.IsDel = 0
  108. Where dc.IsDel = 0");
  109. var clientData = await _sqlSugar.SqlQueryable<TourClientListDetailsView>(clientInfoSql).ToListAsync();
  110. //公司信息
  111. var clientCompanySql = string.Format(@"Select Id,CompanyFullName From Crm_CustomerCompany Where IsDel = 0");
  112. var clientCompanyData = await _sqlSugar.SqlQueryable<CustomerCompanyCiew>(clientCompanySql).ToListAsync();
  113. clientCompanyData = clientCompanyData.DistinctBy(it => it.CompanyFullName).ToList();
  114. var _view = new {
  115. ShippingSpaceTypeData = _ShippingSpaceTypeData,
  116. ClientData = clientData,
  117. ClientCompanyData = clientCompanyData
  118. //PassportTypeData = _PassportTypeData
  119. };
  120. _result.Code = 0;
  121. _result.Data = _view;
  122. }
  123. else
  124. {
  125. _result.Msg = "请传入有效的PortType参数,1 Web 2 Android 3 IOS!";
  126. }
  127. return _result;
  128. }
  129. /// <summary>
  130. /// 根据Id查询Details
  131. /// </summary>
  132. /// <param name="portId"></param>
  133. /// <param name="id"></param>
  134. /// <returns></returns>
  135. public async Task<Result> _Details(int portId, int id)
  136. {
  137. if (portId == 1 || portId == 2 || portId == 3) // 1 web 2 Android 3 ios
  138. {
  139. string sql = string.Format(@"Select tcl.Id,tcl.DiId,temp.*,tcl.ShippingSpaceTypeId,tcl.ShippingSpaceSpecialNeeds,
  140. tcl.HotelSpecialNeeds,tcl.MealSpecialNeeds,tcl.Remark
  141. From Grp_TourClientList tcl
  142. Left Join
  143. (Select dc.Id As DcId,dc.LastName,dc.FirstName,dc.Pinyin,dc.Sex,ccom.CompanyFullName,dc.Job,
  144. cc1.CertNo As IDCardNo,dc.Phone,dc.BirthDay,cc2.PassportType,cc2.CertNo As PassportNo,cc2.Country,
  145. cc2.Area,cc2.IssueDt,cc2.ExpiryDt
  146. From Crm_DeleClient dc
  147. Left Join Crm_CustomerCompany ccom On dc.CrmCompanyId = ccom.Id And ccom.IsDel = 0
  148. Left Join Crm_CustomerCert cc1 On dc.Id = cc1.DcId And cc1.SdId = 773 And cc1.IsDel = 0
  149. Left Join Crm_CustomerCert cc2 On dc.Id = cc2.DcId And cc2.SdId = 774 And cc2.IsDel = 0
  150. Where dc.IsDel = 0) temp
  151. On temp.DcId =tcl.ClientId
  152. Where tcl.IsDel = 0 And tcl.Id = {0}", id);
  153. var data = await _sqlSugar.SqlQueryable<TourClientListDetailsView>(sql).FirstAsync();
  154. if (data != null)
  155. {
  156. data.BirthDay = data.BirthDay.DateFormat("yyyy-MM-dd");
  157. //data.IssueDt = data.IssueDt.DateFormat("yyyy-MM-dd");
  158. //data.ExpiryDt = data.ExpiryDt.DateFormat("yyyy-MM-dd");
  159. _result.Code = 0;
  160. _result.Data = data;
  161. }
  162. }
  163. else
  164. {
  165. _result.Msg = "请传入有效的PortType参数,1 Web 2 Android 3 IOS!";
  166. }
  167. return _result;
  168. }
  169. /// <summary>
  170. /// Add Or Edit
  171. /// </summary>
  172. /// <param name="portId"></param>
  173. /// <param name="id"></param>
  174. /// <returns></returns>
  175. public async Task<Result> _AddOrEdit(TourClientListAddOrEditDto dto)
  176. {
  177. if (string.IsNullOrEmpty(dto.CompanyFullName))
  178. {
  179. _result.Msg = "客户单位名称为空!";
  180. return _result;
  181. }
  182. //if (string.IsNullOrEmpty(dto.IDCardNo))
  183. //{
  184. // _result.Msg = "客户身份证No为空!";
  185. // return _result;
  186. //}
  187. if (dto.ShippingSpaceTypeId < 0)
  188. {
  189. _result.Msg = "舱位类型为空!";
  190. return _result;
  191. }
  192. if (dto.Id >= 0)
  193. {
  194. #region 参数处理
  195. _sqlSugar.BeginTran();
  196. int clientId = -1;
  197. int crmCompanyId = -1;
  198. var clientInfo = await _sqlSugar.Queryable<Crm_DeleClient>().Where(it => it.IsDel == 0
  199. && it.LastName.Equals(dto.LastName)
  200. && it.FirstName.Equals(dto.FirstName)
  201. //&& it.Phone.Equals(dto.Phone)
  202. ).FirstAsync();
  203. Crm_CustomerCompany _CustomerCompany = new Crm_CustomerCompany()
  204. {
  205. CompanyFullName = dto.CompanyFullName,
  206. LastedOpUserId = dto.UserId,
  207. CreateUserId = dto.UserId
  208. };
  209. string idNo = "", birthDay = "";
  210. int sex = -1;
  211. #region 身份证验证
  212. if (!string.IsNullOrEmpty(dto.IDCardNo))
  213. {
  214. var idBool = dto.IDCardNo.IsValidChineseId();
  215. if (idBool)
  216. {
  217. idNo = dto.IDCardNo;
  218. DateTime? birthDayDt = CommonFun.GetBirthDateFromIdentityCard(dto.IDCardNo);
  219. if (birthDayDt != null)
  220. {
  221. birthDay = birthDayDt?.ToString("yyyy-MM-dd") ?? "";
  222. }
  223. sex = CommonFun.GetGenderFromIdentityCard(dto.IDCardNo);
  224. }
  225. #region dto重新赋值
  226. if (string.IsNullOrEmpty(dto.BirthDay)) dto.BirthDay = birthDay;
  227. if (dto.Sex < 0) dto.Sex = sex;
  228. #endregion
  229. }
  230. #endregion
  231. Crm_CustomerCert _CustomerCert = new Crm_CustomerCert()
  232. {
  233. SdId = 773,
  234. CertNo = dto.IDCardNo,
  235. CreateUserId = dto.UserId
  236. };
  237. Crm_DeleClient _DeleClient = new Crm_DeleClient()
  238. {
  239. LastName = dto.LastName,
  240. FirstName = dto.FirstName,
  241. Pinyin = dto.Pinyin,
  242. Phone = dto.Phone,
  243. Sex = dto.Sex,
  244. BirthDay = dto.BirthDay == "" ? null : Convert.ToDateTime(dto.BirthDay),
  245. Job = dto.Job,
  246. CreateUserId = dto.UserId
  247. };
  248. //客户单位/公司 操作
  249. //1.添加 or 修改 公司基本信息
  250. if (clientInfo == null)
  251. {
  252. var companyInfo = await _sqlSugar.Queryable<Crm_CustomerCompany>().Where(it => it.IsDel == 0 &&
  253. it.CompanyFullName.Equals(dto.CompanyFullName)
  254. ).FirstAsync();
  255. if (companyInfo != null) crmCompanyId = companyInfo.Id;
  256. else
  257. {
  258. var companyAdd = await _sqlSugar.Insertable(_CustomerCompany).ExecuteReturnIdentityAsync();
  259. if (companyAdd < 0)
  260. {
  261. _result.Msg = "客户公司信息添加失败!";
  262. _sqlSugar.RollbackTran();
  263. return _result;
  264. }
  265. crmCompanyId = companyAdd;
  266. }
  267. }
  268. else
  269. {
  270. var companyInfo = await _sqlSugar.Queryable<Crm_CustomerCompany>().Where(it => it.IsDel == 0 &&
  271. it.CompanyFullName.Equals(dto.CompanyFullName)
  272. ).FirstAsync();
  273. if (companyInfo != null)
  274. {
  275. crmCompanyId = companyInfo.Id;
  276. if (companyInfo.CompanyFullName.Equals(dto.CompanyFullName))
  277. {
  278. companyInfo.CompanyFullName = dto.CompanyFullName;
  279. var companyEdit = await _sqlSugar.Updateable(companyInfo).UpdateColumns(it =>
  280. new
  281. {
  282. it.CompanyFullName,
  283. }
  284. )
  285. .Where(it => it.Id == companyInfo.Id)
  286. .ExecuteCommandAsync();
  287. if (companyEdit < 0)
  288. {
  289. _result.Msg = "客户公司信息修改失败!";
  290. _sqlSugar.RollbackTran();
  291. return _result;
  292. }
  293. }
  294. }
  295. else
  296. {
  297. var companyAdd = await _sqlSugar.Insertable(_CustomerCompany).ExecuteReturnIdentityAsync();
  298. if (companyAdd < 0)
  299. {
  300. _result.Msg = "客户公司信息添加失败!";
  301. _sqlSugar.RollbackTran();
  302. return _result;
  303. }
  304. crmCompanyId = companyAdd;
  305. }
  306. }
  307. _DeleClient.CrmCompanyId = crmCompanyId;
  308. //客户人员 操作
  309. //2.添加 or 修改 客户基本信息
  310. if (clientInfo != null) //该客户存在 修改信息
  311. {
  312. clientId = clientInfo.Id;
  313. var clientEdit = await _sqlSugar.Updateable(_DeleClient).UpdateColumns(it =>
  314. new
  315. {
  316. it.CrmCompanyId,
  317. it.LastName,
  318. it.FirstName,
  319. it.Pinyin,
  320. it.Job,
  321. it.Sex,
  322. it.Phone,
  323. it.BirthDay
  324. }
  325. )
  326. .Where(it => it.Id == clientId)
  327. .ExecuteCommandAsync();
  328. if (clientEdit < 0)
  329. {
  330. _result.Msg = "客户基础信息修改失败!";
  331. _sqlSugar.RollbackTran();
  332. return _result;
  333. }
  334. }
  335. else //不存在添加 客户信息
  336. {
  337. _DeleClient.CrmCompanyId = crmCompanyId;
  338. var clientAdd = await _sqlSugar.Insertable(_DeleClient).ExecuteReturnIdentityAsync();
  339. if (clientAdd < 0)
  340. {
  341. _result.Msg = "客户基本信息添加失败!";
  342. _sqlSugar.RollbackTran();
  343. return _result;
  344. }
  345. clientId = clientAdd;
  346. }
  347. //客户身份证操作
  348. //3.添加 or 修改 身份证信息
  349. _CustomerCert.DcId = clientId;
  350. if (clientInfo == null)
  351. {
  352. var certInfo = await _sqlSugar.Queryable<Crm_CustomerCert>().Where(it => it.IsDel == 0 &&
  353. it.SdId == 773 && //卡类型 身份证
  354. it.CertNo == dto.IDCardNo //人员Id
  355. ).FirstAsync();
  356. if (certInfo == null)
  357. {
  358. var certAdd = await _sqlSugar.Insertable(_CustomerCert).ExecuteReturnIdentityAsync();
  359. if (certAdd < 0)
  360. {
  361. _result.Msg = "客户身份证添加失败!";
  362. _sqlSugar.RollbackTran();
  363. return _result;
  364. }
  365. }
  366. }
  367. else
  368. {
  369. var certInfo = await _sqlSugar.Queryable<Crm_CustomerCert>().Where(it => it.IsDel == 0 &&
  370. it.SdId == 773 && //卡类型 身份证
  371. it.DcId == clientInfo.Id //人员Id
  372. ).FirstAsync();
  373. if (certInfo == null)
  374. {
  375. var certAdd = await _sqlSugar.Insertable(_CustomerCert).ExecuteReturnIdentityAsync();
  376. if (certAdd < 0)
  377. {
  378. _result.Msg = "客户身份证添加失败!";
  379. _sqlSugar.RollbackTran();
  380. return _result;
  381. }
  382. }
  383. else
  384. {
  385. var certEdit = await _sqlSugar.Updateable(_CustomerCert).UpdateColumns(it =>
  386. new
  387. {
  388. it.CertNo,
  389. }
  390. )
  391. .Where(it => it.Id == certInfo.Id)
  392. .ExecuteCommandAsync();
  393. if (certEdit < 0)
  394. {
  395. _result.Msg = "客户身份证修改失败!";
  396. _sqlSugar.RollbackTran();
  397. return _result;
  398. }
  399. }
  400. }
  401. //团组客户信息名单操作
  402. Grp_TourClientList _TourClientList = new Grp_TourClientList()
  403. {
  404. DiId = dto.DiId,
  405. ClientId = clientId,
  406. ShippingSpaceTypeId = dto.ShippingSpaceTypeId,
  407. ShippingSpaceSpecialNeeds = dto.ShippingSpaceSpecialNeeds,
  408. HotelSpecialNeeds = dto.HotelSpecialNeeds,
  409. MealSpecialNeeds = dto.MealSpecialNeeds,
  410. Remark = dto.Remark,
  411. CreateUserId = dto.UserId
  412. };
  413. #endregion
  414. if (dto.Id == 0) // 添加
  415. {
  416. var tourClientAdd = await _sqlSugar.Insertable(_TourClientList).ExecuteReturnIdentityAsync();
  417. if (tourClientAdd < 0)
  418. {
  419. _result.Msg = "接团客户名单添加失败!";
  420. _sqlSugar.RollbackTran();
  421. return _result;
  422. }
  423. }
  424. else if (dto.Id > 0) //修改
  425. {
  426. var certEdit = await _sqlSugar.Updateable(_TourClientList).UpdateColumns(it =>
  427. new
  428. {
  429. it.ClientId,
  430. it.ShippingSpaceTypeId,
  431. it.ShippingSpaceSpecialNeeds,
  432. it.HotelSpecialNeeds,
  433. it.MealSpecialNeeds,
  434. it.Remark,
  435. }
  436. )
  437. .Where(it => it.Id == dto.Id)
  438. .ExecuteCommandAsync();
  439. if (certEdit < 0)
  440. {
  441. _result.Msg = "接团客户名单失败修改!";
  442. _sqlSugar.RollbackTran();
  443. return _result;
  444. }
  445. }
  446. _result.Code = 0;
  447. _sqlSugar.CommitTran();
  448. }
  449. else
  450. {
  451. _result.Msg = "请传入有效的id参数!";
  452. }
  453. return _result;
  454. }
  455. /// <summary>
  456. /// AddMultiple
  457. /// 添加多个
  458. /// </summary>
  459. /// <param name="portId"></param>
  460. /// <param name="id"></param>
  461. /// <returns></returns>
  462. public async Task<Result> _AddMultiple(TourClientListAddMultipleDto dto)
  463. {
  464. if (dto.DiId < 0)
  465. {
  466. _result.Msg = string.Format(@"请传入有效的团组Id参数!");
  467. return _result;
  468. }
  469. List<TourClientListInfo> _TourClientListInfos = dto.TourClientListInfos;
  470. if (_TourClientListInfos == null || _TourClientListInfos.Count < 0)
  471. {
  472. _result.Msg = string.Format(@"接团客户名单信息集合为空,不执行批量添加!");
  473. return _result;
  474. }
  475. List<Crm_DeleClient> _DeleClients = await _sqlSugar.Queryable<Crm_DeleClient>().Where(it => it.IsDel == 0).ToListAsync();
  476. List<Crm_CustomerCompany> _CustomerCompanies = await _sqlSugar.Queryable<Crm_CustomerCompany>().Where(it => it.IsDel == 0).ToListAsync();
  477. List<Crm_CustomerCert> _CustomerCerts = await _sqlSugar.Queryable<Crm_CustomerCert>().Where(it => it.IsDel == 0 && it.SdId == 773).ToListAsync(); // 身份证类型证件信息
  478. string _Msg = "";
  479. int _DiId = dto.DiId;
  480. int _UserId = dto.UserId;
  481. foreach (var item in _TourClientListInfos)
  482. {
  483. _sqlSugar.BeginTran();
  484. int companyId = -1;
  485. int clientId = -1;
  486. string clientName = string.Format(@"{0}{1}", item.LastName, item.FirstName);
  487. Crm_DeleClient _DeleClientInfo = _DeleClients.Where(it => it.LastName.Equals(item.LastName) &&
  488. it.FirstName.Equals(item.FirstName) &&
  489. it.Phone.Equals(item.Phone)).FirstOrDefault();
  490. if (_DeleClientInfo == null) //添加
  491. {
  492. if (!string.IsNullOrEmpty(item.CompanyFullName))
  493. {
  494. var companyInfo = _CustomerCompanies.Where(it => it.CompanyFullName.Equals(item.CompanyFullName)).FirstOrDefault();
  495. if (companyInfo != null) companyId = companyInfo.Id;
  496. else
  497. {
  498. Crm_CustomerCompany _CustomerCompany = new Crm_CustomerCompany()
  499. {
  500. CompanyFullName = item.CompanyFullName,
  501. LastedOpUserId = _UserId,
  502. CreateUserId = _UserId
  503. };
  504. var companyAdd = await _sqlSugar.Insertable(_CustomerCompany).ExecuteReturnIdentityAsync();
  505. if (companyAdd < 0)
  506. {
  507. _Msg += string.Format("{0} 公司信息添加失败!请前往客户信息确认!\r\n", clientName);
  508. }
  509. companyId = companyAdd;
  510. }
  511. }
  512. _DeleClientInfo = new Crm_DeleClient()
  513. {
  514. LastName = item.LastName,
  515. FirstName = item.FirstName,
  516. Pinyin = item.Pinyin,
  517. CrmCompanyId = companyId,
  518. Job = item.Job,
  519. Sex = item.Sex,
  520. Phone = item.Phone
  521. };
  522. string temp_birthDay = "";
  523. DateTime tempDt_Birthday;
  524. bool b_birth = DateTime.TryParse(item.BirthDay, out tempDt_Birthday);
  525. _DeleClientInfo.BirthDay = b_birth ? tempDt_Birthday : null;
  526. var clientAdd = await _sqlSugar.Insertable(_DeleClientInfo).ExecuteReturnIdentityAsync();
  527. if (clientAdd < 0)
  528. {
  529. _Msg += string.Format("{0} 基本信息添加失败,本条客户名单不添加!请重新添加!\r\n", clientName);
  530. _sqlSugar.RollbackTran();
  531. continue;
  532. }
  533. clientId = clientAdd;
  534. if (!string.IsNullOrEmpty(item.IDCardNo))
  535. {
  536. Crm_CustomerCert _CustomerCert = _CustomerCerts.Where(it => it.DcId == clientId && it.CertNo.Equals(item.IDCardNo)).FirstOrDefault();
  537. if (_CustomerCert == null)
  538. {
  539. _CustomerCert = new Crm_CustomerCert()
  540. {
  541. DcId = clientId,
  542. SdId = 773,
  543. CertNo = item.IDCardNo,
  544. CreateUserId = _UserId,
  545. };
  546. var customerCertAdd = await _sqlSugar.Insertable(_CustomerCert).ExecuteCommandAsync();
  547. if (customerCertAdd < 0)
  548. {
  549. _Msg += string.Format("{0} 身份证信息添加失败!请前往客户信息确认!\r\n", clientName);
  550. }
  551. }
  552. }
  553. }
  554. else //修改客户基本信息
  555. {
  556. clientId = _DeleClientInfo.Id;
  557. if (!string.IsNullOrEmpty(item.CompanyFullName))
  558. {
  559. var companyInfo = _CustomerCompanies.Where(it => it.CompanyFullName.Equals(item.CompanyFullName)).FirstOrDefault();
  560. if (companyInfo != null) companyId = companyInfo.Id;
  561. else
  562. {
  563. Crm_CustomerCompany _CustomerCompany = new Crm_CustomerCompany()
  564. {
  565. CompanyFullName = item.CompanyFullName,
  566. LastedOpUserId = _UserId,
  567. CreateUserId = _UserId
  568. };
  569. var companyAdd = await _sqlSugar.Insertable(_CustomerCompany).ExecuteReturnEntityAsync();
  570. if (companyAdd == null)
  571. {
  572. _Msg += string.Format("{0} 公司信息添加失败!请前往客户信息确认!\r\n", clientName);
  573. }
  574. _CustomerCompanies.Add(companyAdd);
  575. companyId = companyAdd.Id;
  576. }
  577. }
  578. Crm_DeleClient _DeleClient = new Crm_DeleClient() {
  579. CrmCompanyId = companyId,
  580. LastName = item.LastName,
  581. FirstName = item.FirstName,
  582. Pinyin = item.Pinyin,
  583. Sex = item.Sex,
  584. Phone = item.Phone
  585. };
  586. string temp_birthDay = "";
  587. DateTime tempDt_Birthday;
  588. bool b_birth = DateTime.TryParse(item.BirthDay, out tempDt_Birthday);
  589. _DeleClient.BirthDay = b_birth ? tempDt_Birthday : null;
  590. var clientEdit = await _sqlSugar.Updateable(_DeleClient).UpdateColumns(it =>
  591. new
  592. {
  593. it.CrmCompanyId,
  594. it.LastName,
  595. it.FirstName,
  596. it.Sex,
  597. it.Phone,
  598. it.BirthDay
  599. }
  600. )
  601. .Where(it => it.Id == clientId)
  602. .ExecuteCommandAsync();
  603. if (clientEdit < 0)
  604. {
  605. _Msg += string.Format("{0} 基本信息修改失败,!请前往客户信息修改!\r\n", clientName);
  606. }
  607. }
  608. //团组客户信息名单操作
  609. Grp_TourClientList _TourClientList = new Grp_TourClientList()
  610. {
  611. DiId = _DiId,
  612. ClientId = clientId,
  613. ShippingSpaceTypeId = item.ShippingSpaceTypeId,
  614. CreateUserId = _UserId,
  615. ShippingSpaceSpecialNeeds = item.ShippingSpaceSpecialNeeds,
  616. HotelSpecialNeeds = item.HotelSpecialNeeds,
  617. MealSpecialNeeds = item.MealSpecialNeeds,
  618. Remark = item.Remark
  619. };
  620. //判断现有团组客户名单是否存在该客户
  621. var QuerFirstClient = _sqlSugar.Queryable<Grp_TourClientList>().First(x => x.ClientId == _TourClientList.ClientId && x.DiId == _TourClientList.DiId && x.IsDel == 0);
  622. if (QuerFirstClient != null)
  623. {
  624. QuerFirstClient.IsDel = 1;
  625. QuerFirstClient.DeleteUserId = dto.UserId;
  626. QuerFirstClient.DeleteTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
  627. var UpdateTrue = _sqlSugar.Updateable(QuerFirstClient).UpdateColumns(x => new { x.IsDel,x.DeleteUserId,x.DeleteTime}).ExecuteCommand() > 0;
  628. }
  629. var tourClientAdd = await _sqlSugar.Insertable(_TourClientList).ExecuteReturnIdentityAsync();
  630. if (tourClientAdd < 0)
  631. {
  632. _Msg += string.Format("{0} 接团客户名单添加失败!请检查数据完整性后重新添加!\r\n", clientName);
  633. _sqlSugar.RollbackTran();
  634. continue;
  635. }
  636. _sqlSugar.CommitTran();
  637. }
  638. if (!string.IsNullOrEmpty(_Msg)) _result.Msg = _Msg;
  639. else _result.Msg = string.Format("操作成功!");
  640. _result.Code = 0;
  641. return _result;
  642. }
  643. public async Task<Result> OperMultiple(List<TourClientListProcessInfo> _TourClientListInfos , int diid,int userId)
  644. {
  645. if (diid < 0)
  646. {
  647. _result.Msg = string.Format(@"请传入有效的团组Id参数!");
  648. return _result;
  649. }
  650. if (_TourClientListInfos == null || _TourClientListInfos.Count < 0)
  651. {
  652. _result.Msg = string.Format(@"接团客户名单信息集合为空,不执行批量添加!");
  653. return _result;
  654. }
  655. _sqlSugar.BeginTran();
  656. try
  657. {
  658. _sqlSugar.Updateable<Grp_TourClientList>().SetColumns(x=> new Grp_TourClientList { IsDel = 1 } ).Where(x=>x.DiId == diid).ExecuteCommand();
  659. foreach (var item in _TourClientListInfos)
  660. {
  661. //客户信息
  662. var clientInfo = await _sqlSugar.Queryable<Crm_DeleClient>().Where(it => it.IsDel == 0 &&
  663. it.LastName.Equals(item.LastName) &&
  664. it.FirstName.Equals(item.FirstName) &&
  665. it.Phone.Equals(item.Phone)
  666. ).FirstAsync();
  667. Crm_CustomerCompany _CustomerCompany = new Crm_CustomerCompany()
  668. {
  669. CompanyFullName = item.CompanyFullName,
  670. LastedOpUserId = userId,
  671. CreateUserId = userId
  672. };
  673. Crm_CustomerCert _CustomerCert = new Crm_CustomerCert()
  674. {
  675. SdId = 773,
  676. CertNo = item.IDCardNo,
  677. CreateUserId = userId
  678. };
  679. Crm_DeleClient _DeleClient = new Crm_DeleClient()
  680. {
  681. LastName = item.LastName,
  682. FirstName = item.FirstName,
  683. Pinyin = item.Pinyin,
  684. Phone = item.Phone,
  685. Sex = item.Sex,
  686. BirthDay = string.IsNullOrEmpty(item.BirthDay) ? null : Convert.ToDateTime(item.BirthDay),
  687. Job = item.Job,
  688. CreateUserId = userId
  689. };
  690. int clientId = -1;
  691. int crmCompanyId = -1;
  692. //客户单位/公司 操作
  693. //1.添加 or 修改 公司基本信息
  694. if (clientInfo == null)
  695. {
  696. var companyInfo = await _sqlSugar.Queryable<Crm_CustomerCompany>().Where(it => it.IsDel == 0 &&
  697. it.CompanyFullName.Equals(item.CompanyFullName)
  698. ).FirstAsync();
  699. if (companyInfo != null) crmCompanyId = companyInfo.Id;
  700. else
  701. {
  702. var companyAdd = await _sqlSugar.Insertable(_CustomerCompany).ExecuteReturnIdentityAsync();
  703. if (companyAdd < 0)
  704. {
  705. _result.Msg = "客户公司信息添加失败!";
  706. _sqlSugar.RollbackTran();
  707. return _result;
  708. }
  709. crmCompanyId = companyAdd;
  710. }
  711. }
  712. else
  713. {
  714. var companyInfo = await _sqlSugar.Queryable<Crm_CustomerCompany>().Where(it => it.IsDel == 0 &&
  715. it.Id == clientInfo.CrmCompanyId
  716. ).FirstAsync();
  717. if (companyInfo != null)
  718. {
  719. crmCompanyId = companyInfo.Id;
  720. if (!companyInfo.CompanyFullName.Equals(item.CompanyFullName))
  721. {
  722. companyInfo.CompanyFullName = item.CompanyFullName;
  723. var companyEdit = await _sqlSugar.Updateable(companyInfo).UpdateColumns(it =>
  724. new
  725. {
  726. it.CompanyFullName,
  727. }
  728. )
  729. .Where(it => it.Id == companyInfo.Id)
  730. .ExecuteCommandAsync();
  731. if (companyEdit < 0)
  732. {
  733. _result.Msg = "客户公司信息修改失败!";
  734. _sqlSugar.RollbackTran();
  735. return _result;
  736. }
  737. }
  738. }
  739. else
  740. {
  741. var companyAdd = await _sqlSugar.Insertable(_CustomerCompany).ExecuteReturnIdentityAsync();
  742. if (companyAdd < 0)
  743. {
  744. _result.Msg = "客户公司信息添加失败!";
  745. _sqlSugar.RollbackTran();
  746. return _result;
  747. }
  748. crmCompanyId = companyAdd;
  749. }
  750. }
  751. //客户人员 操作
  752. //2.添加 or 修改 客户基本信息
  753. if (clientInfo != null) //该客户存在 修改信息
  754. {
  755. clientId = clientInfo.Id;
  756. _DeleClient.CrmCompanyId = crmCompanyId;
  757. var clientEdit = await _sqlSugar.Updateable(_DeleClient).UpdateColumns(it =>
  758. new
  759. {
  760. it.LastName,
  761. it.FirstName,
  762. it.Pinyin,
  763. it.Sex,
  764. it.Phone,
  765. it.BirthDay
  766. }
  767. )
  768. .Where(it => it.Id == clientId)
  769. .ExecuteCommandAsync();
  770. if (clientEdit < 0)
  771. {
  772. _result.Msg = "客户基础信息修改失败!";
  773. _sqlSugar.RollbackTran();
  774. return _result;
  775. }
  776. }
  777. else //不存在添加 客户信息
  778. {
  779. _DeleClient.CrmCompanyId = crmCompanyId;
  780. var clientAdd = await _sqlSugar.Insertable(_DeleClient).ExecuteReturnIdentityAsync();
  781. if (clientAdd < 0)
  782. {
  783. _result.Msg = "客户基本信息添加失败!";
  784. _sqlSugar.RollbackTran();
  785. return _result;
  786. }
  787. clientId = clientAdd;
  788. }
  789. //客户身份证操作
  790. //3.添加 or 修改 身份证信息
  791. _CustomerCert.DcId = clientId;
  792. if (clientInfo == null)
  793. {
  794. var certInfo = await _sqlSugar.Queryable<Crm_CustomerCert>().Where(it => it.IsDel == 0 &&
  795. it.SdId == 773 && //卡类型 身份证
  796. it.CertNo == item.IDCardNo //人员Id
  797. ).FirstAsync();
  798. if (certInfo == null)
  799. {
  800. var certAdd = await _sqlSugar.Insertable(_CustomerCert).ExecuteReturnIdentityAsync();
  801. if (certAdd < 0)
  802. {
  803. _result.Msg = "客户身份证添加失败!";
  804. _sqlSugar.RollbackTran();
  805. return _result;
  806. }
  807. }
  808. }
  809. else
  810. {
  811. var certInfo = await _sqlSugar.Queryable<Crm_CustomerCert>().Where(it => it.IsDel == 0 &&
  812. it.SdId == 773 && //卡类型 身份证
  813. it.DcId == clientInfo.Id //人员Id
  814. ).FirstAsync();
  815. if (certInfo == null)
  816. {
  817. var certAdd = await _sqlSugar.Insertable(_CustomerCert).ExecuteReturnIdentityAsync();
  818. if (certAdd < 0)
  819. {
  820. _result.Msg = "客户身份证添加失败!";
  821. _sqlSugar.RollbackTran();
  822. return _result;
  823. }
  824. }
  825. else
  826. {
  827. var certEdit = await _sqlSugar.Updateable(_CustomerCert).UpdateColumns(it =>
  828. new
  829. {
  830. it.CertNo,
  831. }
  832. )
  833. .Where(it => it.Id == certInfo.Id)
  834. .ExecuteCommandAsync();
  835. if (certEdit < 0)
  836. {
  837. _result.Msg = "客户身份证修改失败!";
  838. _sqlSugar.RollbackTran();
  839. return _result;
  840. }
  841. }
  842. }
  843. //团组客户信息名单操作
  844. Grp_TourClientList _TourClientList = new Grp_TourClientList()
  845. {
  846. DiId = diid,
  847. ClientId = clientId,
  848. ShippingSpaceTypeId = item.ShippingSpaceTypeId,
  849. ShippingSpaceSpecialNeeds = item.ShippingSpaceSpecialNeeds,
  850. HotelSpecialNeeds = item.HotelSpecialNeeds,
  851. MealSpecialNeeds = item.MealSpecialNeeds,
  852. Remark = item.Remark,
  853. CreateUserId = userId,
  854. };
  855. var tourClientAdd = await _sqlSugar.Insertable(_TourClientList).ExecuteReturnIdentityAsync();
  856. if (tourClientAdd < 0)
  857. {
  858. _result.Msg = "接团客户名单添加失败!";
  859. _sqlSugar.RollbackTran();
  860. return _result;
  861. }
  862. }
  863. _result.Code = 0;
  864. _sqlSugar.CommitTran();
  865. }
  866. catch (Exception ex)
  867. {
  868. _sqlSugar.RollbackTran();
  869. _result.Msg = "程序异常 -- " + ex.Message;
  870. }
  871. return _result;
  872. }
  873. /// <summary>
  874. /// Del
  875. /// </summary>
  876. /// <param name="portId"></param>
  877. /// <param name="id"></param>
  878. /// <returns></returns>
  879. public async Task<Result> _Del(int id, int userId)
  880. {
  881. if (id > 0)
  882. {
  883. Grp_TourClientList _TourClientList = new Grp_TourClientList()
  884. {
  885. Id = id,
  886. IsDel = 1,
  887. DeleteUserId = userId,
  888. DeleteTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
  889. };
  890. var certEdit = await _sqlSugar.Updateable(_TourClientList).UpdateColumns(it =>
  891. new
  892. {
  893. it.IsDel,
  894. it.DeleteUserId,
  895. it.DeleteTime,
  896. }
  897. )
  898. .Where(it => it.Id == id)
  899. .ExecuteCommandAsync();
  900. if (certEdit < 0)
  901. {
  902. _result.Msg = string.Format(@"接团客户名单删除修改!");
  903. return _result;
  904. }
  905. _result.Code = 0;
  906. }
  907. else
  908. {
  909. _result.Msg = string.Format(@"请传入有效的Id参数");
  910. }
  911. return _result;
  912. }
  913. /// <summary>
  914. /// 分割客户名称
  915. /// </summary>
  916. /// <param name="ids"></param>
  917. /// <returns></returns>
  918. public string _ResolveCustomerName(string ids)
  919. {
  920. string clientStr = "";
  921. if (string.IsNullOrEmpty(ids)) return clientStr;
  922. List<int> intList = new List<int>();
  923. //if (ids.Contains(","))
  924. //{
  925. // string[] numbers = ids.Split(',');
  926. // foreach (string numberStr in numbers)
  927. // {
  928. // if (int.TryParse(numberStr.Trim(), out int number))
  929. // {
  930. // intList.Add(number);
  931. // }
  932. // }
  933. //}
  934. //else
  935. //{
  936. // if (int.TryParse(ids.Trim(), out int number))
  937. // {
  938. // intList.Add(number);
  939. // }
  940. //}
  941. string sql = string.Format(@$"Select * From Crm_DeleClient Where IsDel = 0 And Id In ({ids})");
  942. var infos = _sqlSugar.SqlQueryable<Crm_DeleClient>(sql).ToList();
  943. if (infos.Count > 0)
  944. {
  945. var infos1 = infos.Select(it => it.LastName + it.FirstName).ToList();
  946. clientStr = string.Join(",", infos1);
  947. }
  948. return clientStr;
  949. }
  950. private class CustomerNameInfo
  951. {
  952. public int Id { get; set; }
  953. public string Name { get; set; }
  954. }
  955. }
  956. }