NewClientDataRepository.cs 40 KB

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