NewClientDataRepository.cs 40 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852853854855856857858859860861862863864865866867868869870871872873874875876877878879880881882883884885886887888889890891892893894895896897898899900901902903904905906907908909910911912913914915916917918919920921922923924925926927928929930931932933934935936937938939940941942943944945946947948949950951952953954955956957958959960961
  1. using AutoMapper;
  2. using AutoMapper.Execution;
  3. using MySqlX.XDevAPI.Relational;
  4. using NPOI.OpenXmlFormats.Dml.Diagram;
  5. using OASystem.Domain;
  6. using OASystem.Domain.Dtos;
  7. using OASystem.Domain.Dtos.CRM;
  8. using OASystem.Domain.Entities.Customer;
  9. using OASystem.Domain.Entities.Resource;
  10. using OASystem.Domain.Enums;
  11. using OASystem.Domain.ViewModels.CRM;
  12. using SqlSugar;
  13. using System;
  14. using System.Collections;
  15. using System.Collections.Generic;
  16. using System.ComponentModel.Design;
  17. using System.Linq;
  18. using System.Text;
  19. using System.Threading.Tasks;
  20. using System.Xml.Linq;
  21. using static Google.Protobuf.Reflection.SourceCodeInfo.Types;
  22. using static OASystem.Domain.Dtos.CRM.NewClientDataQueryDto;
  23. namespace OASystem.Infrastructure.Repositories.CRM
  24. {
  25. public class NewClientDataRepository : BaseRepository<Crm_NewClientData, NewClientDataView>
  26. {
  27. private readonly IMapper _mapper;
  28. public NewClientDataRepository(SqlSugarClient sqlSugar, IMapper mapper) :
  29. base(sqlSugar)
  30. {
  31. _mapper = mapper;
  32. }
  33. /// <summary>
  34. /// 客户资料
  35. /// 基础数据源
  36. /// </summary>
  37. /// <param name="dto"></param>
  38. /// <returns></returns>
  39. public async Task<Result> _Init(int portType)
  40. {
  41. Result result = new Result() { Code = -2, Msg = "未知错误" };
  42. if (portType == 1 || portType == 2 || portType == 3)
  43. {
  44. #region 下拉框初始化数据
  45. //负责人下拉框
  46. List<dynamic> _Users = new List<dynamic>();
  47. List<Sys_Users> users = _sqlSugar.Queryable<Sys_Users>().Where(u => u.IsDel == 0 && u.CompanyId == 2).ToList();
  48. foreach (Sys_Users user in users)
  49. {
  50. var data = new
  51. {
  52. Id = user.Id,
  53. Name = user.CnName
  54. };
  55. _Users.Add(data);
  56. };
  57. List<Sys_SetData> initData = _sqlSugar.Queryable<Sys_SetData>().Where(it => it.IsDel == 0).ToList();
  58. //客户级别数据
  59. List<dynamic> _level = new List<dynamic>();
  60. List<Sys_SetData> level = initData.Where(u => u.STid == 33 && u.IsDel == 0).ToList();
  61. foreach (Sys_SetData item in level)
  62. {
  63. var data = new
  64. {
  65. Id = item.Id,
  66. Name = item.Name
  67. };
  68. _level.Add(data);
  69. };
  70. //客户类别
  71. List<dynamic> _CustomerClass = new List<dynamic>();
  72. List<Sys_SetData> CustomerClass = initData.Where(u => u.STid == 37 && u.IsDel == 0).ToList();
  73. foreach (Sys_SetData item in CustomerClass)
  74. {
  75. var data = new
  76. {
  77. Id = item.Id,
  78. Name = item.Name
  79. };
  80. _CustomerClass.Add(data);
  81. };
  82. //业务分类
  83. List<dynamic> _ServiceClass = new List<dynamic>();
  84. List<Sys_SetData> ServiceClass = initData.Where(u => u.STid == 36 && u.IsDel == 0).ToList();
  85. foreach (Sys_SetData item in ServiceClass)
  86. {
  87. var data = new
  88. {
  89. Id = item.Id,
  90. Name = item.Name
  91. };
  92. _ServiceClass.Add(data);
  93. };
  94. //身份分类
  95. List<dynamic> _ProvinceClass = new List<dynamic>();
  96. List<Sys_SetData> ProvinceClass = initData.Where(u => u.STid == 42 && u.IsDel == 0).ToList();
  97. foreach (Sys_SetData item in ProvinceClass)
  98. {
  99. var data = new
  100. {
  101. Id = item.Id,
  102. Name = item.Name
  103. };
  104. _ProvinceClass.Add(data);
  105. };
  106. #endregion
  107. var data1 = new {
  108. Users = _Users,
  109. Level = _level,
  110. CustomerClass = _CustomerClass,
  111. ServiceClass= _ServiceClass,
  112. ProvinceClass = _ProvinceClass
  113. };
  114. return result = new Result()
  115. {
  116. Code = 0,
  117. Msg = "查询成功",
  118. Data = data1
  119. };
  120. }
  121. else
  122. {
  123. result.Msg = string.Format("请传入有效的PortType参数!");
  124. }
  125. return result;
  126. }
  127. /// <summary>
  128. /// 市场客户资料数据
  129. /// 详情
  130. /// </summary>
  131. /// <param name="dto"></param>
  132. /// <returns></returns>
  133. public async Task<Result> _Details(int portType,int id)
  134. {
  135. Result result = new Result() { Code = -2, Msg = "未知错误" };
  136. if (portType == 1 || portType == 2 || portType == 3)
  137. {
  138. if (id < 0)
  139. {
  140. result.Msg = string.Format("请传入有效的Id参数!");
  141. return result;
  142. }
  143. string infoSql = string.Format(@" Select * From Crm_NewClientData Where Isdel = 0 And Id = {0}", id);
  144. var info = await _sqlSugar.SqlQueryable<DetailsView>(infoSql).FirstAsync();
  145. if (info != null )
  146. {
  147. List<AscribedUser> AscribedUser = await _sqlSugar.SqlQueryable<AscribedUser>
  148. ("select u1.UsersId as UserId ,u2.CnName,u1.NewClientDataId from Crm_ClientDataAndUser u1,Sys_Users u2 where u1.UsersId=u2.Id and NewClientDataId=" + info.Id + " AND u1.ISDEL = 0").ToListAsync();
  149. info.AscribedUser = AscribedUser.Select(it => it.UserId).ToList();
  150. List<AscribedDepartment> AscribedDepartment = await _sqlSugar.SqlQueryable<AscribedDepartment>
  151. ("select d2.Id,d2.Name,d1.NewClientDataId from Crm_ClientDataAndBusiness d1,Sys_SetData d2 where d1.SetDataId=d2.Id and NewClientDataId=" + info.Id + " AND d1.ISDEL = 0").ToListAsync();
  152. info.AscribedDepartment = AscribedDepartment.Select(it => it.Id).ToList();
  153. result.Code = 0;
  154. result.Data = info;
  155. }
  156. }
  157. else
  158. {
  159. result.Msg = string.Format("请传入有效的PortType参数!");
  160. }
  161. return result;
  162. }
  163. /// <summary>
  164. /// 客户资料初识初始化
  165. /// </summary>
  166. /// <param name="dto"></param>
  167. /// <returns></returns>
  168. public async Task<Result> QueryNewClientData(NewClientDataQueryDto dto)
  169. {
  170. Result result = new Result() { Code = -2, Msg = "未知错误" };
  171. try
  172. {
  173. #region 交集
  174. List<int> NewClientDataId1 = new List<int>();
  175. List<int> NewClientDataId2 = new List<int>();
  176. string NewClientDataId = "";
  177. int state = 0;
  178. #region 负责人
  179. if (!string.IsNullOrWhiteSpace(dto.Userid))
  180. {
  181. string sql = string.Format(@"select u1.UsersId as UserId,u2.CnName,u1.NewClientDataId from Crm_ClientDataAndUser u1,Sys_Users u2 where u1.UsersId=u2.Id and u1.UsersId in ({0}) and u1.IsDel = 0", dto.Userid);
  182. List<AscribedUser> ascribedUsers = await _sqlSugar.SqlQueryable<AscribedUser>(sql).ToListAsync();
  183. if (ascribedUsers.Count != 0)
  184. {
  185. foreach (var ascribedUser in ascribedUsers)
  186. {
  187. if (ascribedUser.NewClientDataId != 0)
  188. {
  189. NewClientDataId1.Add(ascribedUser.NewClientDataId);
  190. }
  191. }
  192. }
  193. else
  194. {
  195. result = new Result() { Code = -1, Msg = "暂无数据" };
  196. }
  197. state = -1;
  198. }
  199. #endregion
  200. #region 业务归属
  201. if (!string.IsNullOrWhiteSpace(dto.Business))
  202. {
  203. string sql = string.Format(@"select d2.Id,d2.Name,d1.NewClientDataId from Crm_ClientDataAndBusiness d1,Sys_SetData d2 where d1.SetDataId=d2.Id and d1.SetDataId in ({0}) and d1.isdel = 0", dto.Business);
  204. List<AscribedDepartment> AscribedDepartment = await _sqlSugar.SqlQueryable<AscribedDepartment>(sql).ToListAsync();
  205. if (AscribedDepartment.Count != 0)
  206. {
  207. foreach (var item in AscribedDepartment)
  208. {
  209. if (item.NewClientDataId != 0)
  210. {
  211. NewClientDataId2.Add(item.NewClientDataId);
  212. }
  213. }
  214. }
  215. else
  216. {
  217. result = new Result() { Code = -1, Msg = "暂无数据" };
  218. }
  219. state = -1;
  220. }
  221. #endregion
  222. List<int> intList = new List<int>();
  223. if (NewClientDataId1.Count != 0 && NewClientDataId2.Count != 0)
  224. {
  225. intList = NewClientDataId1.Intersect(NewClientDataId2).ToList();
  226. }
  227. else if (NewClientDataId1.Count != 0)
  228. {
  229. intList = NewClientDataId1;
  230. }
  231. else if (NewClientDataId2.Count != 0)
  232. {
  233. intList = NewClientDataId2;
  234. }
  235. #endregion
  236. foreach (var item in intList)
  237. {
  238. NewClientDataId += item + ",";
  239. }
  240. if (!string.IsNullOrWhiteSpace(NewClientDataId))
  241. {
  242. NewClientDataId = NewClientDataId.Substring(0, NewClientDataId.Length - 1);
  243. }
  244. string sqlWhere = string.Empty;
  245. if (dto.PortType == 1)
  246. {
  247. #region 联系人条件
  248. if (!string.IsNullOrWhiteSpace(dto.Contact))
  249. {
  250. sqlWhere += string.Format(@" And s.Contact like '%{0}%'", dto.Contact);
  251. }
  252. #endregion
  253. #region 地区条件
  254. if (!string.IsNullOrWhiteSpace(dto.Location))
  255. {
  256. sqlWhere += string.Format(@" And s.Location like '%{0}%'", dto.Location);
  257. }
  258. #endregion
  259. #region 单位条件
  260. if (!string.IsNullOrWhiteSpace(dto.Client))
  261. {
  262. sqlWhere += string.Format(@" And s.Client like '%{0}%'", dto.Client);
  263. }
  264. #endregion
  265. }
  266. else if (dto.PortType == 2 || dto.PortType == 3)
  267. {
  268. sqlWhere += string.Format("And (Contact like '%{0}%' or Location like '%{0}%' or Client like '%{0}%' )", dto.Client);
  269. }
  270. if (state == -1)
  271. {
  272. if (string.IsNullOrWhiteSpace(NewClientDataId))
  273. {
  274. NewClientDataId = "0";
  275. }
  276. sqlWhere += string.Format(@" And s.Id in({0})", NewClientDataId);
  277. }
  278. #region 地市州条件
  279. if (dto.Lvlid != 0)
  280. {
  281. sqlWhere += string.Format(@" And s.Lvlid={0}", dto.Lvlid);
  282. }
  283. #endregion
  284. #region 省域条件
  285. if (dto.Range != 0)
  286. {
  287. string setDataSql = "select * from Sys_SetData where STid = 33 and isdel = 0 ";
  288. switch (dto.Range)
  289. {
  290. case 419:
  291. setDataSql += " and (Name like '%四%川%' or Name like '%成%都%')";
  292. break;
  293. case 421:
  294. setDataSql += " and (Name like '%贵%州%' or Name like '%贵%阳%')";
  295. break;
  296. case 420:
  297. setDataSql += " and (Name like '%云%南%' or Name like '%昆%明%')";
  298. break;
  299. case 423:
  300. setDataSql += " and (Name like '%重庆%')";
  301. break;
  302. case 422:
  303. setDataSql += " and (Name like '%西%藏%' or Name like '%拉%萨%')";
  304. break;
  305. case 578:
  306. setDataSql += " and (Name like '%青%海%' or Name like '%西%宁%')";
  307. break;
  308. case 605:
  309. setDataSql += " and (Name like '%陕%西%' or Name like '%西%安%')";
  310. break;
  311. case 606:
  312. setDataSql += " and (Name like '%宁%夏%' or Name like '%银%川%')";
  313. break;
  314. case 625:
  315. setDataSql += " and (Name like '%甘%肃%' or Name like '%兰%州%')";
  316. break;
  317. case 634:
  318. setDataSql += " and (Name like '%新%疆%' or Name like '%乌%鲁%木%齐%')";
  319. break;
  320. }
  321. var RangeSetDataList = _sqlSugar.SqlQueryable<Sys_SetData>(setDataSql).Select(x => x.Id).ToList();
  322. string lvlds = string.Join(',', RangeSetDataList).TrimEnd(',');
  323. if (!string.IsNullOrEmpty(lvlds))
  324. {
  325. sqlWhere += string.Format(@" And s.Lvlid in ({0}) ", lvlds);
  326. }
  327. }
  328. #endregion
  329. #region 客户类别
  330. if (dto.Category != 0)
  331. {
  332. sqlWhere += string.Format(@" And s.Category = {0}", dto.Category);
  333. }
  334. #endregion
  335. sqlWhere += string.Format(@" And s.Lvlid=s1.Id And s.IsDel={0} ", 0);
  336. if (!string.IsNullOrEmpty(sqlWhere.Trim()))
  337. {
  338. Regex r = new Regex("And");
  339. sqlWhere = r.Replace(sqlWhere, "Where", 1);
  340. }
  341. int pIndex = dto.PageIndex * dto.PageSize - dto.PageSize + 1;
  342. int pSize = dto.PageIndex * dto.PageSize;
  343. string sqlNew = string.Format(@"select (SELECT COUNT(1) FROM Crm_NewClientData s left Join Sys_SetData s1 On s.Lvlid=s1.Id {0}) AS countPage,* from (select ROW_NUMBER()
  344. over(order by s.CreateTime desc) RowNumber,s.*,s1.Name as 'LvlName' from Crm_NewClientData s left Join Sys_SetData s1 On s.Lvlid=s1.Id {0}) as co where
  345. RowNumber between {1} and {2} ", sqlWhere, pIndex, pSize);
  346. List<NewClientDataView> NewClientDataView = await _sqlSugar.SqlQueryable<NewClientDataView>(sqlNew).ToListAsync();
  347. foreach (var item in NewClientDataView)
  348. {
  349. Sys_SetData CategoryStr = _sqlSugar.Queryable<Sys_SetData>().Single(it => it.Id == item.Category);
  350. if (CategoryStr != null)
  351. {
  352. item.CategoryStr = CategoryStr != null ? CategoryStr.Name : null;
  353. }
  354. Sys_SetData lvlStr = _sqlSugar.Queryable<Sys_SetData>().Single(it => it.Id == item.Lvlid);
  355. if (lvlStr != null)
  356. {
  357. item.LvlidStr = lvlStr != null ? lvlStr.Name : null;
  358. }
  359. }
  360. if (NewClientDataView.Count > 0)
  361. {
  362. int count = NewClientDataView[0].countPage;
  363. float totalPage = (float)count / dto.PageSize;//总页数
  364. if (totalPage == 0) totalPage = 1;
  365. else totalPage = (int)Math.Ceiling((double)totalPage);
  366. if (dto.PortType == 1)
  367. {
  368. #region 下拉框初始化数据
  369. //负责人下拉框
  370. List<dynamic> _Users = new List<dynamic>();
  371. List<Sys_Users> users = _sqlSugar.Queryable<Sys_Users>()
  372. .Where(u => u.IsDel == 0).ToList();
  373. foreach (Sys_Users user in users)
  374. {
  375. var data = new
  376. {
  377. Id = user.Id,
  378. Name = user.CnName
  379. };
  380. _Users.Add(data);
  381. };
  382. //省域数据
  383. List<dynamic> _Province = new List<dynamic>();
  384. List<Sys_SetData> province = _sqlSugar.Queryable<Sys_SetData>()
  385. .Where(u => u.STid == 42 && u.IsDel == 0).ToList();
  386. foreach (Sys_SetData item in province)
  387. {
  388. var data = new
  389. {
  390. Id = item.Id,
  391. Name = item.Name
  392. };
  393. _Province.Add(data);
  394. };
  395. //客户级别数据
  396. List<dynamic> _level = new List<dynamic>();
  397. List<Sys_SetData> level = _sqlSugar.Queryable<Sys_SetData>()
  398. .Where(u => u.STid == 33 && u.IsDel == 0).ToList();
  399. foreach (Sys_SetData item in level)
  400. {
  401. var data = new
  402. {
  403. Id = item.Id,
  404. Name = item.Name
  405. };
  406. _level.Add(data);
  407. };
  408. //客户类别
  409. List<dynamic> _CustomerClass = new List<dynamic>();
  410. List<Sys_SetData> CustomerClass = _sqlSugar.Queryable<Sys_SetData>()
  411. .Where(u => u.STid == 37 && u.IsDel == 0).ToList();
  412. foreach (Sys_SetData item in CustomerClass)
  413. {
  414. var data = new
  415. {
  416. Id = item.Id,
  417. Name = item.Name
  418. };
  419. _CustomerClass.Add(data);
  420. };
  421. //业务分类
  422. List<dynamic> _ServiceClass = new List<dynamic>();
  423. List<Sys_SetData> ServiceClass = _sqlSugar.Queryable<Sys_SetData>()
  424. .Where(u => u.STid == 36 && u.IsDel == 0).ToList();
  425. foreach (Sys_SetData item in ServiceClass)
  426. {
  427. var data = new
  428. {
  429. Id = item.Id,
  430. Name = item.Name
  431. };
  432. _ServiceClass.Add(data);
  433. };
  434. #endregion
  435. foreach (var item in NewClientDataView)
  436. {
  437. List<AscribedUser> AscribedUser = await _sqlSugar.SqlQueryable<AscribedUser>
  438. ("select u1.UsersId as UserId ,u2.CnName,u1.NewClientDataId from Crm_ClientDataAndUser u1,Sys_Users u2 where u1.UsersId=u2.Id and NewClientDataId=" + item.Id + " AND u1.ISDEL = 0").ToListAsync();
  439. item.AscribedUser = AscribedUser;
  440. List<AscribedDepartment> AscribedDepartment = await _sqlSugar.SqlQueryable<AscribedDepartment>
  441. ("select d2.Id,d2.Name,d1.NewClientDataId from Crm_ClientDataAndBusiness d1,Sys_SetData d2 where d1.SetDataId=d2.Id and NewClientDataId=" + item.Id + " AND d1.ISDEL = 0").ToListAsync();
  442. item.AscribedDepartment = AscribedDepartment;
  443. }
  444. var groupNumber = await QueryNumberGroups();
  445. var Data = new
  446. {
  447. ClientTableData = new { pageCount = count, totalPage = (int)totalPage, pageIndex = dto.PageIndex, pageSize = dto.PageSize, pageSource = NewClientDataView },
  448. Users = _Users,
  449. Province = _Province,
  450. level = _level,
  451. CustomerClass = _CustomerClass,
  452. ServiceClass = _ServiceClass,
  453. groupNumber = groupNumber.Data,
  454. };
  455. return result = new Result()
  456. {
  457. Code = 0,
  458. Msg = "查询成功",
  459. Data = Data
  460. };
  461. }
  462. else if (dto.PortType == 2 || dto.PortType == 3)
  463. {
  464. List<NewClientDataAndroidIOSView> newClientDataIOSViews = new List<NewClientDataAndroidIOSView>();
  465. foreach (var item in NewClientDataView)
  466. {
  467. newClientDataIOSViews.Add(new NewClientDataAndroidIOSView()
  468. {
  469. RowNumber = item.RowNumber,
  470. Id = item.Id,
  471. Client = item.Client,
  472. Contact = item.Contact,
  473. Job = item.Job,
  474. Telephone = item.Telephone,
  475. Location = item.Location,
  476. });
  477. }
  478. result = new Result()
  479. {
  480. Code = 0,
  481. Msg = "查询成功",
  482. Data = new { pageCount = count, totalPage = (int)totalPage, pageIndex = dto.PageIndex, pageSize = dto.PageSize, pageSource = newClientDataIOSViews },
  483. };
  484. }
  485. }
  486. else
  487. {
  488. result = new Result() { Code = -1, Msg = "暂无数据!" };
  489. }
  490. }
  491. catch (Exception ex)
  492. {
  493. result = new Result() { Code = -2, Msg = "未知错误" };
  494. }
  495. return result;
  496. }
  497. public Result QueryUserSelect()
  498. {
  499. Result result = new Result() { Code = -2, Msg = "未知错误" };
  500. try
  501. {
  502. //负责人下拉框
  503. List<dynamic> _Users = new List<dynamic>();
  504. List<Sys_Users> users = _sqlSugar.Queryable<Sys_Users>()
  505. .Where(u => u.IsDel == 0).ToList();
  506. foreach (Sys_Users user in users)
  507. {
  508. var data = new
  509. {
  510. Id = user.Id,
  511. Name = user.CnName
  512. };
  513. _Users.Add(data);
  514. };
  515. if (_Users.Count == 0)
  516. {
  517. result = new Result() { Code = -1, Msg = "暂无数据" };
  518. }
  519. result = new Result() { Code = 0, Msg = "查询成功!", Data = _Users };
  520. }
  521. catch (Exception)
  522. {
  523. result = new Result() { Code = -2, Msg = "未知错误" };
  524. }
  525. return result;
  526. }
  527. public async Task<Result> NewClientOp(NewClientOpDto dto)
  528. {
  529. Result result = new Result() { Code = -2, Msg = "未知错误" };
  530. bool istrue = false;
  531. int AddReturnId = -1;
  532. string BirthdayStr = string.Empty;
  533. if (!string.IsNullOrWhiteSpace(dto.Birthday))
  534. {
  535. DateTime Birthday = new DateTime();
  536. var isParse = DateTime.TryParse(dto.Birthday, out Birthday);
  537. BirthdayStr = isParse ? Birthday.ToString("yyyy-MM-dd") : "";
  538. }
  539. try
  540. {
  541. BeginTran();
  542. if (dto.Status == 1)//添加
  543. {
  544. string selectSql = string.Format(@"select * from Crm_NewClientData where Client='{0}' And Contact='{1}' And IsDel={2}"
  545. , dto.Client, dto.Contact, 0);
  546. var NewClientData = await _sqlSugar.SqlQueryable<Crm_NewClientData>(selectSql).FirstAsync();//查询是否存在
  547. if (NewClientData == null)
  548. {
  549. if (string.IsNullOrWhiteSpace(dto.PassportDate))
  550. {
  551. dto.PassportDate = null;
  552. }
  553. Crm_NewClientData _NewClientData = _mapper.Map<Crm_NewClientData>(dto);
  554. _NewClientData.Birthday = BirthdayStr;
  555. int id = await AddAsyncReturnId(_NewClientData); //添加市场客户资料表数据
  556. if (id == 0)
  557. {
  558. result = new Result() { Code = -1, Msg = "添加失败!" };
  559. }
  560. else
  561. {
  562. result = new Result() { Code = 0, Msg = "添加成功!",Data = id };
  563. istrue = true;
  564. AddReturnId = id;
  565. CommitTran();
  566. }
  567. }
  568. else
  569. {
  570. result = new Result() { Code = -1, Msg = "该信息已存在,请勿重复添加!" };
  571. }
  572. }
  573. else if (dto.Status == 2)//修改
  574. {
  575. DateTime? PassportDate = null;
  576. try
  577. {
  578. PassportDate = DateTime.Parse(dto.PassportDate);
  579. }
  580. catch (Exception)
  581. {
  582. PassportDate = null;
  583. }
  584. bool res = await UpdateAsync(a => a.Id == dto.Id, a => new Crm_NewClientData
  585. {
  586. Number = dto.Number,
  587. Lvlid = dto.Lvlid,
  588. Client = dto.Client,
  589. Weight = dto.Weight,
  590. ClientShort = dto.Clientshort,
  591. Contact = dto.Contact,
  592. Gender = dto.Gender,
  593. Passport = dto.Passport,
  594. PassportDate = PassportDate,
  595. Job = dto.Job,
  596. Telephone = dto.Telephone,
  597. Phone = dto.Phone,
  598. Email = dto.Email,
  599. Location = dto.Location,
  600. Address = dto.Address,
  601. Birthday = BirthdayStr,
  602. OtherInfo = dto.Otherinfo,
  603. Wechat = dto.Wechat,
  604. Category = dto.Category,
  605. PreDele = dto.Predele,
  606. FinlishedDele = dto.FinlishedDele,
  607. Remark = dto.Remark,
  608. });
  609. if (res)
  610. {
  611. istrue = true;
  612. AddReturnId = dto.Id == 0 ? -1 : dto.Id;
  613. if (AddReturnId != -1)
  614. {
  615. await _sqlSugar.Updateable<Crm_ClientDataAndUser>().Where(x=>x.NewClientDataId == AddReturnId).SetColumns(a => new Crm_ClientDataAndUser()
  616. {
  617. IsDel = 1,
  618. DeleteTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
  619. }).ExecuteCommandAsync();
  620. await _sqlSugar.Updateable<Crm_ClientDataAndBusiness>().Where(x => x.NewClientDataId == AddReturnId).SetColumns(a => new Crm_ClientDataAndBusiness()
  621. {
  622. IsDel = 1,
  623. DeleteTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
  624. }).ExecuteCommandAsync();
  625. }
  626. result = new Result() { Code = 0, Msg = "修改成功!" };
  627. }
  628. else
  629. {
  630. result = new Result() { Code = -1, Msg = "修改失败!" };
  631. }
  632. }
  633. else
  634. {
  635. result = new Result() { Code = -1, Msg = "请传入Status参数,1添加 2修改!" };
  636. }
  637. if (istrue)
  638. {
  639. Adds<Crm_ClientDataAndUser>(dto.AscribedUser.Select(x => new Crm_ClientDataAndUser
  640. {
  641. CreateTime = DateTime.Now,
  642. CreateUserId = dto.CreateUserId,
  643. IsDel = 0,
  644. NewClientDataId = AddReturnId,
  645. usersId = x
  646. }).ToList());
  647. Adds<Crm_ClientDataAndBusiness>(dto.AscribedDepartment.Select(x => new Crm_ClientDataAndBusiness
  648. {
  649. CreateUserId = dto.CreateUserId,
  650. IsDel = 0,
  651. CreateTime = DateTime.Now,
  652. NewClientDataId = AddReturnId,
  653. SetDataId = x,
  654. }).ToList());
  655. CommitTran();
  656. result.Data = AddReturnId;
  657. }
  658. else
  659. {
  660. RollbackTran();
  661. }
  662. }
  663. catch (Exception)
  664. {
  665. RollbackTran();
  666. result = new Result() { Code = -2, Msg = "未知错误" };
  667. }
  668. return result;
  669. }
  670. public async Task<Result> QueryNumberGroups()
  671. {
  672. Result result = new Result();
  673. //preDeleAll 预计总量
  674. //finlishedDeleAll 已出总量
  675. DataTable preDeleAndfinlishedDeleAll = await GetDataTableAsync("select SUM(PreDele) as PreDeleAll ,SUM(FinlishedDele) as FinlishedDeleAll from Crm_NewClientData");
  676. var preDeleAll = preDeleAndfinlishedDeleAll.Rows[0]["PreDeleAll"].ToString();
  677. var finlishedDeleAll = preDeleAndfinlishedDeleAll.Rows[0]["finlishedDeleAll"].ToString();
  678. result.Code = 0;
  679. result.Msg = "成功!";
  680. result.Data = new
  681. {
  682. preDeleAll,
  683. finlishedDeleAll
  684. };
  685. return result;
  686. }
  687. /// <summary>
  688. /// 获取下拉列表数据和单条数据信息
  689. /// </summary>
  690. /// <param name="dto"></param>
  691. public async Task<Result> QuerySelectAndSingleData(QuerySingleDto dto)
  692. {
  693. Result rest = new Result();
  694. var QueryData = await GetAsync<Crm_NewClientData>(x => x.Id == dto.Id);
  695. NewClientDataView MapQueryData = null;
  696. if (QueryData != null)
  697. {
  698. MapQueryData = _mapper.Map<NewClientDataView>(QueryData);
  699. MapQueryData.AscribedUser = await _sqlSugar.SqlQueryable<AscribedUser>
  700. ("select u1.UsersId as UserId,u2.CnName,u1.NewClientDataId from Crm_ClientDataAndUser u1,Sys_Users u2 where u1.UsersId=u2.Id and NewClientDataId=" + dto.Id + " and u1.isdel = 0").ToListAsync();
  701. MapQueryData.AscribedDepartment = await _sqlSugar.SqlQueryable<AscribedDepartment>
  702. ("select d2.Id,d2.Name,d1.NewClientDataId from Crm_ClientDataAndBusiness d1,Sys_SetData d2 where d1.SetDataId=d2.Id and NewClientDataId=" + dto.Id + " and d1.isdel = 0").ToListAsync();
  703. }
  704. #region 下拉框初始化数据
  705. //负责人下拉框
  706. List<dynamic> _Users = new List<dynamic>();
  707. List<Sys_Users> users = _sqlSugar.Queryable<Sys_Users>()
  708. .Where(u => u.IsDel == 0).ToList();
  709. foreach (Sys_Users user in users)
  710. {
  711. var data = new
  712. {
  713. Id = user.Id,
  714. Name = user.CnName
  715. };
  716. _Users.Add(data);
  717. };
  718. //客户级别数据
  719. List<dynamic> _level = new List<dynamic>();
  720. List<Sys_SetData> level = _sqlSugar.Queryable<Sys_SetData>()
  721. .Where(u => u.STid == 33 && u.IsDel == 0).ToList();
  722. foreach (Sys_SetData item in level)
  723. {
  724. var data = new
  725. {
  726. Id = item.Id,
  727. Name = item.Name
  728. };
  729. _level.Add(data);
  730. };
  731. //客户类别
  732. List<dynamic> _CustomerClass = new List<dynamic>();
  733. List<Sys_SetData> CustomerClass = _sqlSugar.Queryable<Sys_SetData>()
  734. .Where(u => u.STid == 37 && u.IsDel == 0).ToList();
  735. foreach (Sys_SetData item in CustomerClass)
  736. {
  737. var data = new
  738. {
  739. Id = item.Id,
  740. Name = item.Name
  741. };
  742. _CustomerClass.Add(data);
  743. };
  744. //业务分类
  745. List<dynamic> _ServiceClass = new List<dynamic>();
  746. List<Sys_SetData> ServiceClass = _sqlSugar.Queryable<Sys_SetData>()
  747. .Where(u => u.STid == 36 && u.IsDel == 0).ToList();
  748. foreach (Sys_SetData item in ServiceClass)
  749. {
  750. var data = new
  751. {
  752. Id = item.Id,
  753. Name = item.Name
  754. };
  755. _ServiceClass.Add(data);
  756. };
  757. #endregion
  758. rest.Code = 0;
  759. rest.Data = new
  760. {
  761. data = MapQueryData,
  762. Users = _Users,
  763. level = _level,
  764. CustomerClass = _CustomerClass,
  765. ServiceClass = _ServiceClass,
  766. };
  767. rest.Msg = "获取成功!";
  768. return rest;
  769. }
  770. /// <summary>
  771. /// 删除市场客户资料数据
  772. /// </summary>
  773. /// <param name="dto"></param>
  774. /// <returns></returns>
  775. public async Task<Result> DelNewClientData(DelBaseDto dto)
  776. {
  777. Result AcrionResult = new Result();
  778. BeginTran();
  779. var DBresult = await SoftDeleteByIdAsync<Crm_NewClientData>(dto.Id.ToString(), dto.DeleteUserId);
  780. try
  781. {
  782. if (DBresult)
  783. {
  784. AcrionResult.Code = 0;
  785. string sqlSet = $"isdel = 1, DeleteUserId = {dto.DeleteUserId} ,DeleteTime = '{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}'";
  786. string sql = $" update Crm_ClientDataAndUser set {sqlSet} where NewClientDataId = {dto.Id} ";
  787. await ExecuteCommandAsync(sql);
  788. sql = $" update Crm_ClientDataAndBusiness set {sqlSet} where NewClientDataId = {dto.Id} ";
  789. await ExecuteCommandAsync(sql);
  790. CommitTran();
  791. AcrionResult.Code = 0;
  792. }
  793. }
  794. catch (Exception ex)
  795. {
  796. RollbackTran();
  797. AcrionResult.Msg = ex.Message;
  798. AcrionResult.Code = -1;
  799. }
  800. return AcrionResult;
  801. }
  802. /// <summary>
  803. /// 市场客户资料数据
  804. /// 批量指派
  805. /// </summary>
  806. /// <param name="dto"></param>
  807. /// <returns></returns>
  808. public async Task<Result> _BatchAssignment(BatchAssignmentDto dto)
  809. {
  810. Result AcrionResult = new Result() { Code = -1,Msg="操作失败"};
  811. if (dto.UserIdItem == null || dto.UserIdItem.Count < 1)
  812. {
  813. AcrionResult.Msg = "用户ID集合不能为空!";
  814. return AcrionResult;
  815. }
  816. if (dto.ClientDataIdItem == null || dto.ClientDataIdItem.Count < 1)
  817. {
  818. AcrionResult.Msg = "客户资料ID集合不能为空!";
  819. return AcrionResult;
  820. }
  821. List<Crm_ClientDataAndUser> _ClientDataAndUsers = new List<Crm_ClientDataAndUser>();
  822. List<Crm_ClientDataAndUser> _ClientDataAndUsers1 = await _sqlSugar.Queryable<Crm_ClientDataAndUser>()
  823. .Where(it =>
  824. it.IsDel == 0 &&
  825. dto.ClientDataIdItem.Contains(it.NewClientDataId) &&
  826. dto.UserIdItem.Contains(it.usersId)
  827. )
  828. .ToListAsync();
  829. foreach (var clientDataId in dto.ClientDataIdItem)
  830. {
  831. foreach (var userId1 in dto.UserIdItem)
  832. {
  833. Crm_ClientDataAndUser _ClientDataAndUsers2 = _ClientDataAndUsers1.Where(it => it.NewClientDataId == clientDataId &&
  834. it.usersId == userId1
  835. ).FirstOrDefault();
  836. if (_ClientDataAndUsers2 == null)
  837. {
  838. _ClientDataAndUsers.Add(new Crm_ClientDataAndUser()
  839. {
  840. CreateUserId = dto.UserId,
  841. NewClientDataId = clientDataId,
  842. usersId = userId1
  843. });
  844. }
  845. }
  846. }
  847. if (_ClientDataAndUsers.Count > 0)
  848. {
  849. var adds = await _sqlSugar.Insertable(_ClientDataAndUsers).ExecuteCommandAsync();
  850. if (adds > 0)
  851. {
  852. #region 客户资料表操作记录 批量添加
  853. List<Crm_TableOperationRecord> _TableOperationRecords = new List<Crm_TableOperationRecord>();
  854. foreach (var item in _ClientDataAndUsers)
  855. {
  856. _TableOperationRecords.Add(
  857. new Crm_TableOperationRecord() {
  858. TableName = "Crm_TableOperationRecord",
  859. PortType = dto.PortType,
  860. OperationItem = OperationEnum.BatchAssignment,
  861. DataId = item.NewClientDataId,
  862. CreateUserId = dto.UserId,
  863. CreateTime = DateTime.Now,
  864. Remark = "",
  865. IsDel = 0
  866. });
  867. }
  868. if (_TableOperationRecords.Count > 0)
  869. {
  870. await _sqlSugar.Insertable(_TableOperationRecords).ExecuteCommandAsync();
  871. }
  872. #endregion
  873. AcrionResult.Code = 0;
  874. return AcrionResult;
  875. }
  876. }
  877. return AcrionResult;
  878. }
  879. }
  880. }