NewClientDataRepository.cs 25 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575
  1. using AutoMapper;
  2. using AutoMapper.Execution;
  3. using MySqlX.XDevAPI.Relational;
  4. using OASystem.Domain;
  5. using OASystem.Domain.Dtos.CRM;
  6. using OASystem.Domain.Entities.Customer;
  7. using OASystem.Domain.Entities.Resource;
  8. using OASystem.Domain.ViewModels.CRM;
  9. using SqlSugar;
  10. using System;
  11. using System.Collections;
  12. using System.Collections.Generic;
  13. using System.Linq;
  14. using System.Text;
  15. using System.Threading.Tasks;
  16. using System.Xml.Linq;
  17. using static OASystem.Domain.Dtos.CRM.NewClientDataQueryDto;
  18. namespace OASystem.Infrastructure.Repositories.CRM
  19. {
  20. public class NewClientDataRepository : BaseRepository<Crm_NewClientData, NewClientDataView>
  21. {
  22. private readonly IMapper _mapper;
  23. public NewClientDataRepository(SqlSugarClient sqlSugar, IMapper mapper) :
  24. base(sqlSugar)
  25. {
  26. _mapper = mapper;
  27. }
  28. /// <summary>
  29. /// 客户资料初识初始化
  30. /// </summary>
  31. /// <param name="dto"></param>
  32. /// <returns></returns>
  33. public async Task<Result> QueryNewClientData(NewClientDataQueryDto dto)
  34. {
  35. Result result = new Result() { Code = -2, Msg = "未知错误" };
  36. try
  37. {
  38. #region 交集
  39. List<int> NewClientDataId1 = new List<int>();
  40. List<int> NewClientDataId2 = new List<int>();
  41. string NewClientDataId = "";
  42. int state = 0;
  43. #region 负责人
  44. if (!string.IsNullOrWhiteSpace(dto.Userid))
  45. {
  46. 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);
  47. List<AscribedUser> ascribedUsers = await _sqlSugar.SqlQueryable<AscribedUser>(sql).ToListAsync();
  48. if (ascribedUsers.Count != 0)
  49. {
  50. foreach (var ascribedUser in ascribedUsers)
  51. {
  52. if (ascribedUser.NewClientDataId != 0)
  53. {
  54. NewClientDataId1.Add(ascribedUser.NewClientDataId);
  55. }
  56. }
  57. }
  58. else
  59. {
  60. result = new Result() { Code = -1, Msg = "暂无数据" };
  61. }
  62. state = -1;
  63. }
  64. #endregion
  65. #region 业务归属
  66. if (!string.IsNullOrWhiteSpace(dto.Business))
  67. {
  68. 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);
  69. List<AscribedDepartment> AscribedDepartment = await _sqlSugar.SqlQueryable<AscribedDepartment>(sql).ToListAsync();
  70. if (AscribedDepartment.Count != 0)
  71. {
  72. foreach (var item in AscribedDepartment)
  73. {
  74. if (item.NewClientDataId != 0)
  75. {
  76. NewClientDataId2.Add(item.NewClientDataId);
  77. }
  78. }
  79. }
  80. else
  81. {
  82. result = new Result() { Code = -1, Msg = "暂无数据" };
  83. }
  84. state = -1;
  85. }
  86. #endregion
  87. List<int> intList = new List<int>();
  88. if (NewClientDataId1.Count != 0 && NewClientDataId2.Count != 0)
  89. {
  90. intList = NewClientDataId1.Intersect(NewClientDataId2).ToList();
  91. }
  92. else if (NewClientDataId1.Count != 0)
  93. {
  94. intList = NewClientDataId1;
  95. }
  96. else if (NewClientDataId2.Count != 0)
  97. {
  98. intList = NewClientDataId2;
  99. }
  100. #endregion
  101. foreach (var item in intList)
  102. {
  103. NewClientDataId += item + ",";
  104. }
  105. if (!string.IsNullOrWhiteSpace(NewClientDataId))
  106. {
  107. NewClientDataId = NewClientDataId.Substring(0, NewClientDataId.Length - 1);
  108. }
  109. string sqlWhere = string.Empty;
  110. #region 联系人条件
  111. if (!string.IsNullOrWhiteSpace(dto.Contact))
  112. {
  113. sqlWhere += string.Format(@" And s.Contact like '%{0}%'", dto.Contact);
  114. }
  115. #endregion
  116. #region 地区条件
  117. if (!string.IsNullOrWhiteSpace(dto.Location))
  118. {
  119. sqlWhere += string.Format(@" And s.Location like '%{0}%'", dto.Location);
  120. }
  121. #endregion
  122. #region 单位条件
  123. if (!string.IsNullOrWhiteSpace(dto.Client))
  124. {
  125. sqlWhere += string.Format(@" And s.Client like '%{0}%'", dto.Client);
  126. }
  127. #endregion
  128. if (state == -1)
  129. {
  130. if (string.IsNullOrWhiteSpace(NewClientDataId))
  131. {
  132. NewClientDataId = "0";
  133. }
  134. sqlWhere += string.Format(@" And s.Id in({0})", NewClientDataId);
  135. }
  136. #region 地市州条件
  137. if (dto.Lvlid != 0)
  138. {
  139. sqlWhere += string.Format(@" And s.Lvlid={0}", dto.Lvlid);
  140. }
  141. #endregion
  142. #region 省域条件
  143. if (dto.Range != 0)
  144. {
  145. string setDataSql = "select * from Sys_SetData where STid = 33 and isdel = 0 ";
  146. switch (dto.Range)
  147. {
  148. case 419:
  149. setDataSql += " and (Name like '%四%川%' or Name like '%成%都%')";
  150. break;
  151. case 421:
  152. setDataSql += " and (Name like '%贵%州%' or Name like '%贵%阳%')";
  153. break;
  154. case 420:
  155. setDataSql += " and (Name like '%云%南%' or Name like '%昆%明%')";
  156. break;
  157. case 423:
  158. setDataSql += " and (Name like '%重庆%')";
  159. break;
  160. case 422:
  161. setDataSql += " and (Name like '%西%藏%' or Name like '%拉%萨%')";
  162. break;
  163. case 578:
  164. setDataSql += " and (Name like '%青%海%' or Name like '%西%宁%')";
  165. break;
  166. case 605:
  167. setDataSql += " and (Name like '%陕%西%' or Name like '%西%安%')";
  168. break;
  169. case 606:
  170. setDataSql += " and (Name like '%宁%夏%' or Name like '%银%川%')";
  171. break;
  172. case 625:
  173. setDataSql += " and (Name like '%甘%肃%' or Name like '%兰%州%')";
  174. break;
  175. case 634:
  176. setDataSql += " and (Name like '%新%疆%' or Name like '%乌%鲁%木%齐%')";
  177. break;
  178. }
  179. var RangeSetDataList = _sqlSugar.SqlQueryable<Sys_SetData>(setDataSql).Select(x => x.Id).ToList();
  180. string lvlds = string.Join(',', RangeSetDataList).TrimEnd(',');
  181. if (!string.IsNullOrEmpty(lvlds))
  182. {
  183. sqlWhere += string.Format(@" And s.Lvlid in ({0}) ", lvlds);
  184. }
  185. }
  186. #endregion
  187. #region 客户类别
  188. if (dto.Category != 0)
  189. {
  190. sqlWhere += string.Format(@" And s.Category = {0}", dto.Category);
  191. }
  192. #endregion
  193. sqlWhere += string.Format(@" And s.Lvlid=s1.Id And s.IsDel={0} ", 0);
  194. if (!string.IsNullOrEmpty(sqlWhere.Trim()))
  195. {
  196. Regex r = new Regex("And");
  197. sqlWhere = r.Replace(sqlWhere, "Where", 1);
  198. }
  199. int pIndex = dto.PageIndex * dto.PageSize - dto.PageSize + 1;
  200. int pSize = dto.PageIndex * dto.PageSize;
  201. 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()
  202. 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
  203. RowNumber between {1} and {2} ", sqlWhere, pIndex, pSize);
  204. List<NewClientDataView> NewClientDataView = await _sqlSugar.SqlQueryable<NewClientDataView>(sqlNew).ToListAsync();
  205. foreach (var item in NewClientDataView)
  206. {
  207. Sys_SetData CategoryStr = _sqlSugar.Queryable<Sys_SetData>().Single(it => it.Id == item.Category);
  208. item.CategoryStr = CategoryStr != null ? CategoryStr.Name : null;
  209. Sys_SetData lvlStr = _sqlSugar.Queryable<Sys_SetData>().Single(it => it.Id == item.Lvlid);
  210. item.LvlidStr = lvlStr != null ? lvlStr.Name : null;
  211. }
  212. if (NewClientDataView.Count > 0)
  213. {
  214. int count = NewClientDataView[0].countPage;
  215. float totalPage = (float)count / dto.PageSize;//总页数
  216. if (totalPage == 0) totalPage = 1;
  217. else totalPage = (int)Math.Ceiling((double)totalPage);
  218. if (dto.PortType == 1)
  219. {
  220. #region 下拉框初始化数据
  221. //负责人下拉框
  222. List<dynamic> _Users = new List<dynamic>();
  223. List<Sys_Users> users = _sqlSugar.Queryable<Sys_Users>()
  224. .Where(u => (u.CnName == "张海麟" || u.CnName == "安宁" || u.CnName == "李彩娟" || u.CnName == "舒庆" || u.CnName == "李媛媛") && u.IsDel == 0).ToList();
  225. foreach (Sys_Users user in users)
  226. {
  227. var data = new
  228. {
  229. Id = user.Id,
  230. Name = user.CnName
  231. };
  232. _Users.Add(data);
  233. };
  234. //省域数据
  235. List<dynamic> _Province = new List<dynamic>();
  236. List<Sys_SetData> province = _sqlSugar.Queryable<Sys_SetData>()
  237. .Where(u => u.STid == 42 && u.IsDel == 0).ToList();
  238. foreach (Sys_SetData item in province)
  239. {
  240. var data = new
  241. {
  242. Id = item.Id,
  243. Name = item.Name
  244. };
  245. _Province.Add(data);
  246. };
  247. //客户级别数据
  248. List<dynamic> _level = new List<dynamic>();
  249. List<Sys_SetData> level = _sqlSugar.Queryable<Sys_SetData>()
  250. .Where(u => u.STid == 33 && u.IsDel == 0).ToList();
  251. foreach (Sys_SetData item in level)
  252. {
  253. var data = new
  254. {
  255. Id = item.Id,
  256. Name = item.Name
  257. };
  258. _level.Add(data);
  259. };
  260. //客户类别
  261. List<dynamic> _CustomerClass = new List<dynamic>();
  262. List<Sys_SetData> CustomerClass = _sqlSugar.Queryable<Sys_SetData>()
  263. .Where(u => u.STid == 37 && u.IsDel == 0).ToList();
  264. foreach (Sys_SetData item in CustomerClass)
  265. {
  266. var data = new
  267. {
  268. Id = item.Id,
  269. Name = item.Name
  270. };
  271. _CustomerClass.Add(data);
  272. };
  273. //业务分类
  274. List<dynamic> _ServiceClass = new List<dynamic>();
  275. List<Sys_SetData> ServiceClass = _sqlSugar.Queryable<Sys_SetData>()
  276. .Where(u => u.STid == 36 && u.IsDel == 0).ToList();
  277. foreach (Sys_SetData item in ServiceClass)
  278. {
  279. var data = new
  280. {
  281. Id = item.Id,
  282. Name = item.Name
  283. };
  284. _ServiceClass.Add(data);
  285. };
  286. #endregion
  287. foreach (var item in NewClientDataView)
  288. {
  289. List<AscribedUser> AscribedUser = await _sqlSugar.SqlQueryable<AscribedUser>
  290. ("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();
  291. item.AscribedUser = AscribedUser;
  292. List<AscribedDepartment> AscribedDepartment = await _sqlSugar.SqlQueryable<AscribedDepartment>
  293. ("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();
  294. item.AscribedDepartment = AscribedDepartment;
  295. }
  296. var groupNumber = await QueryNumberGroups();
  297. var Data = new
  298. {
  299. ClientTableData = new { pageCount = count, totalPage = (int)totalPage, pageIndex = dto.PageIndex, pageSize = dto.PageSize, pageSource = NewClientDataView },
  300. Users = _Users,
  301. Province = _Province,
  302. level = _level,
  303. CustomerClass = _CustomerClass,
  304. ServiceClass = _ServiceClass,
  305. groupNumber = groupNumber.Data,
  306. };
  307. return result = new Result()
  308. {
  309. Code = 0,
  310. Msg = "查询成功",
  311. Data = Data
  312. };
  313. }
  314. else if (dto.PortType == 2)
  315. {
  316. result = new Result()
  317. {
  318. Code = 0,
  319. Msg = "查询成功",
  320. Data = new { pageCount = count, totalPage = (int)totalPage, pageIndex = dto.PageIndex, pageSize = dto.PageSize, pageSource = NewClientDataView },
  321. };
  322. }
  323. }
  324. else
  325. {
  326. result = new Result() { Code = -1, Msg = "暂无数据!" };
  327. }
  328. }
  329. catch (Exception ex)
  330. {
  331. result = new Result() { Code = -2, Msg = "未知错误" };
  332. }
  333. return result;
  334. }
  335. public async Task<Result> QueryUserSelect()
  336. {
  337. Result result = new Result() { Code = -2, Msg = "未知错误" };
  338. try
  339. {
  340. //负责人下拉框
  341. List<dynamic> _Users = new List<dynamic>();
  342. List<Sys_Users> users = _sqlSugar.Queryable<Sys_Users>()
  343. .Where(u => (u.CnName == "张海麟" || u.CnName == "安宁" || u.CnName == "李彩娟" || u.CnName == "舒庆" || u.CnName == "李媛媛") && u.IsDel == 0).ToList();
  344. foreach (Sys_Users user in users)
  345. {
  346. var data = new
  347. {
  348. Id = user.Id,
  349. Name = user.CnName
  350. };
  351. _Users.Add(data);
  352. };
  353. if (_Users.Count == 0)
  354. {
  355. result = new Result() { Code = -1, Msg = "暂无数据" };
  356. }
  357. result = new Result() { Code = 0, Msg = "查询成功!", Data = _Users };
  358. }
  359. catch (Exception)
  360. {
  361. result = new Result() { Code = -2, Msg = "未知错误" };
  362. }
  363. return result;
  364. }
  365. public async Task<Result> NewClientOp(NewClientOpDto dto)
  366. {
  367. Result result = new Result() { Code = -2, Msg = "未知错误" };
  368. try
  369. {
  370. if (dto.Status == 1)//添加
  371. {
  372. string selectSql = string.Format(@"select * from Crm_NewClientData where Client='{0}' Contact='{1}' And IsDel={2}"
  373. , dto.Client, dto.Contact, 0);
  374. var NewClientData = await _sqlSugar.SqlQueryable<Crm_NewClientData>(selectSql).FirstAsync();//查询是否存在
  375. if (NewClientData != null)
  376. {
  377. return result = new Result() { Code = -1, Msg = "该信息已存在,请勿重复添加!" };
  378. }
  379. else//不存在,可添加
  380. {
  381. Crm_NewClientData _NewClientData = _mapper.Map<Crm_NewClientData>(dto);
  382. int id = await AddAsyncReturnId(_NewClientData);
  383. if (id == 0)
  384. {
  385. return result = new Result() { Code = -1, Msg = "添加失败!" };
  386. }
  387. return result = new Result() { Code = 0, Msg = "添加成功!", Data = new { Id = id } };
  388. }
  389. }
  390. else if (dto.Status == 2)//修改
  391. {
  392. bool res = await UpdateAsync(a => a.Id == dto.Id, a => new Crm_NewClientData
  393. {
  394. Number = dto.Number,
  395. Lvlid = dto.Lvlid,
  396. Client = dto.Client,
  397. Weight = dto.Weight,
  398. ClientShort = dto.Clientshort,
  399. Contact = dto.Contact,
  400. Gender = dto.Gender,
  401. Passport = dto.Passport,
  402. PassportDate = dto.PassportDate,
  403. Job = dto.Job,
  404. Telephone = dto.Telephone,
  405. Phone = dto.Phone,
  406. Email = dto.Email,
  407. Location = dto.Location,
  408. Address = dto.Address,
  409. Birthday = dto.Birthday,
  410. OtherInfo = dto.Otherinfo,
  411. Wechat = dto.Wechat,
  412. Category = dto.Category,
  413. PreDele = dto.Predele,
  414. FinlishedDele = dto.FinlishedDele,
  415. Remark = dto.Remark,
  416. });
  417. if (!res)
  418. {
  419. return result = new Result() { Code = -1, Msg = "修改失败!" };
  420. }
  421. return result = new Result() { Code = 0, Msg = "修改成功!" };
  422. }
  423. else
  424. {
  425. return result = new Result() { Code = -1, Msg = "请传入Status参数,1添加 2修改!" };
  426. }
  427. }
  428. catch (Exception)
  429. {
  430. result = new Result() { Code = -2, Msg = "未知错误" };
  431. }
  432. return result;
  433. }
  434. public async Task<Result> QueryNumberGroups()
  435. {
  436. Result result = new Result();
  437. //preDeleAll 预计总量
  438. //finlishedDeleAll 已出总量
  439. DataTable preDeleAndfinlishedDeleAll = await GetDataTableAsync("select SUM(PreDele) as PreDeleAll ,SUM(FinlishedDele) as FinlishedDeleAll from Crm_NewClientData");
  440. var preDeleAll = preDeleAndfinlishedDeleAll.Rows[0]["PreDeleAll"].ToString();
  441. var finlishedDeleAll = preDeleAndfinlishedDeleAll.Rows[0]["finlishedDeleAll"].ToString();
  442. result.Code = 0;
  443. result.Msg = "成功!";
  444. result.Data = new
  445. {
  446. preDeleAll,
  447. finlishedDeleAll
  448. };
  449. return result;
  450. }
  451. /// <summary>
  452. /// 获取下拉列表数据和单条数据信息
  453. /// </summary>
  454. /// <param name="dto"></param>
  455. public async Task<Result> QuerySelectAndSingleData(QuerySingleDto dto)
  456. {
  457. Result rest = new Result();
  458. var QueryData = await GetAsync<Crm_NewClientData>(x => x.Id == dto.Id);
  459. NewClientDataView MapQueryData = null;
  460. if (QueryData != null)
  461. {
  462. MapQueryData = _mapper.Map<NewClientDataView>(QueryData);
  463. MapQueryData.AscribedUser = await _sqlSugar.SqlQueryable<AscribedUser>
  464. ("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();
  465. MapQueryData.AscribedDepartment = await _sqlSugar.SqlQueryable<AscribedDepartment>
  466. ("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();
  467. }
  468. #region 下拉框初始化数据
  469. //负责人下拉框
  470. List<dynamic> _Users = new List<dynamic>();
  471. List<Sys_Users> users = _sqlSugar.Queryable<Sys_Users>()
  472. .Where(u => (u.CnName == "张海麟" || u.CnName == "安宁" || u.CnName == "李彩娟" || u.CnName == "舒庆" || u.CnName == "李媛媛") && u.IsDel == 0).ToList();
  473. foreach (Sys_Users user in users)
  474. {
  475. var data = new
  476. {
  477. Id = user.Id,
  478. Name = user.CnName
  479. };
  480. _Users.Add(data);
  481. };
  482. //客户级别数据
  483. List<dynamic> _level = new List<dynamic>();
  484. List<Sys_SetData> level = _sqlSugar.Queryable<Sys_SetData>()
  485. .Where(u => u.STid == 33 && u.IsDel == 0).ToList();
  486. foreach (Sys_SetData item in level)
  487. {
  488. var data = new
  489. {
  490. Id = item.Id,
  491. Name = item.Name
  492. };
  493. _level.Add(data);
  494. };
  495. //客户类别
  496. List<dynamic> _CustomerClass = new List<dynamic>();
  497. List<Sys_SetData> CustomerClass = _sqlSugar.Queryable<Sys_SetData>()
  498. .Where(u => u.STid == 37 && u.IsDel == 0).ToList();
  499. foreach (Sys_SetData item in CustomerClass)
  500. {
  501. var data = new
  502. {
  503. Id = item.Id,
  504. Name = item.Name
  505. };
  506. _CustomerClass.Add(data);
  507. };
  508. //业务分类
  509. List<dynamic> _ServiceClass = new List<dynamic>();
  510. List<Sys_SetData> ServiceClass = _sqlSugar.Queryable<Sys_SetData>()
  511. .Where(u => u.STid == 36 && u.IsDel == 0).ToList();
  512. foreach (Sys_SetData item in ServiceClass)
  513. {
  514. var data = new
  515. {
  516. Id = item.Id,
  517. Name = item.Name
  518. };
  519. _ServiceClass.Add(data);
  520. };
  521. #endregion
  522. rest.Code = 0;
  523. rest.Data = new
  524. {
  525. data = MapQueryData,
  526. Users = _Users,
  527. level = _level,
  528. CustomerClass = _CustomerClass,
  529. ServiceClass = _ServiceClass,
  530. };
  531. rest.Msg = "获取成功!";
  532. return rest;
  533. }
  534. }
  535. }