NewClientDataRepository.cs 41 KB

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