NewClientDataRepository.cs 61 KB

12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879808182838485868788899091929394959697989910010110210310410510610710810911011111211311411511611711811912012112212312412512612712812913013113213313413513613713813914014114214314414514614714814915015115215315415515615715815916016116216316416516616716816917017117217317417517617717817918018118218318418518618718818919019119219319419519619719819920020120220320420520620720820921021121221321421521621721821922022122222322422522622722822923023123223323423523623723823924024124224324424524624724824925025125225325425525625725825926026126226326426526626726826927027127227327427527627727827928028128228328428528628728828929029129229329429529629729829930030130230330430530630730830931031131231331431531631731831932032132232332432532632732832933033133233333433533633733833934034134234334434534634734834935035135235335435535635735835936036136236336436536636736836937037137237337437537637737837938038138238338438538638738838939039139239339439539639739839940040140240340440540640740840941041141241341441541641741841942042142242342442542642742842943043143243343443543643743843944044144244344444544644744844945045145245345445545645745845946046146246346446546646746846947047147247347447547647747847948048148248348448548648748848949049149249349449549649749849950050150250350450550650750850951051151251351451551651751851952052152252352452552652752852953053153253353453553653753853954054154254354454554654754854955055155255355455555655755855956056156256356456556656756856957057157257357457557657757857958058158258358458558658758858959059159259359459559659759859960060160260360460560660760860961061161261361461561661761861962062162262362462562662762862963063163263363463563663763863964064164264364464564664764864965065165265365465565665765865966066166266366466566666766866967067167267367467567667767867968068168268368468568668768868969069169269369469569669769869970070170270370470570670770870971071171271371471571671771871972072172272372472572672772872973073173273373473573673773873974074174274374474574674774874975075175275375475575675775875976076176276376476576676776876977077177277377477577677777877978078178278378478578678778878979079179279379479579679779879980080180280380480580680780880981081181281381481581681781881982082182282382482582682782882983083183283383483583683783883984084184284384484584684784884985085185285385485585685785885986086186286386486586686786886987087187287387487587687787887988088188288388488588688788888989089189289389489589689789889990090190290390490590690790890991091191291391491591691791891992092192292392492592692792892993093193293393493593693793893994094194294394494594694794894995095195295395495595695795895996096196296396496596696796896997097197297397497597697797897998098198298398498598698798898999099199299399499599699799899910001001100210031004100510061007100810091010101110121013101410151016101710181019102010211022102310241025102610271028102910301031103210331034103510361037103810391040104110421043104410451046104710481049105010511052105310541055105610571058105910601061106210631064106510661067106810691070107110721073107410751076107710781079108010811082108310841085108610871088108910901091109210931094109510961097109810991100110111021103110411051106110711081109111011111112111311141115111611171118111911201121112211231124112511261127112811291130113111321133113411351136113711381139114011411142114311441145114611471148114911501151115211531154115511561157115811591160116111621163116411651166116711681169117011711172117311741175117611771178117911801181118211831184118511861187118811891190119111921193119411951196119711981199120012011202120312041205120612071208120912101211121212131214121512161217121812191220122112221223122412251226122712281229123012311232123312341235123612371238123912401241124212431244124512461247124812491250125112521253125412551256125712581259126012611262126312641265126612671268126912701271127212731274127512761277127812791280128112821283128412851286128712881289129012911292129312941295129612971298129913001301130213031304130513061307130813091310131113121313131413151316131713181319132013211322132313241325132613271328132913301331133213331334133513361337133813391340134113421343134413451346134713481349135013511352135313541355135613571358135913601361136213631364136513661367136813691370137113721373137413751376137713781379138013811382138313841385138613871388138913901391139213931394139513961397139813991400140114021403140414051406140714081409141014111412141314141415141614171418141914201421142214231424
  1. using AutoMapper;
  2. using AutoMapper.Execution;
  3. using EyeSoft.Extensions;
  4. using MySqlX.XDevAPI.Relational;
  5. using Newtonsoft.Json;
  6. using NPOI.OpenXmlFormats.Dml.Diagram;
  7. using NPOI.SS.Formula.Functions;
  8. using NPOI.SS.UserModel;
  9. using OASystem.Domain;
  10. using OASystem.Domain.AesEncryption;
  11. using OASystem.Domain.Dtos;
  12. using OASystem.Domain.Dtos.CRM;
  13. using OASystem.Domain.Entities.Customer;
  14. using OASystem.Domain.Entities.Resource;
  15. using OASystem.Domain.Enums;
  16. using OASystem.Domain.ViewModels.CRM;
  17. using OASystem.Domain.ViewModels.JuHeExchangeRate;
  18. using OASystem.Domain.ViewModels.QiYeWeChat;
  19. using SqlSugar;
  20. using System;
  21. using System.Collections;
  22. using System.Collections.Generic;
  23. using System.ComponentModel.Design;
  24. using System.Diagnostics;
  25. using System.Diagnostics.Contracts;
  26. using System.Linq;
  27. using System.Reflection.Metadata;
  28. using System.Runtime.Intrinsics.Arm;
  29. using System.Text;
  30. using System.Threading.Tasks;
  31. using System.Xml.Linq;
  32. using XAct;
  33. using static Google.Protobuf.Reflection.SourceCodeInfo.Types;
  34. using static OASystem.Domain.Dtos.CRM.NewClientDataQueryDto;
  35. namespace OASystem.Infrastructure.Repositories.CRM
  36. {
  37. public class NewClientDataRepository : BaseRepository<Crm_NewClientData, NewClientDataView>
  38. {
  39. private readonly IMapper _mapper;
  40. public NewClientDataRepository(SqlSugarClient sqlSugar, IMapper mapper) :
  41. base(sqlSugar)
  42. {
  43. _mapper = mapper;
  44. }
  45. /// <summary>
  46. /// 客户资料
  47. /// 基础数据源
  48. /// </summary>
  49. /// <param name="dto"></param>
  50. /// <returns></returns>
  51. public async Task<Result> _Init(MarketCustomerInitDto Dto)
  52. {
  53. Result result = new Result() { Code = -2, Msg = "未知错误" };
  54. var portType = Dto.PortType;
  55. if (portType == 1 || portType == 2 || portType == 3)
  56. {
  57. #region 下拉框初始化数据
  58. //负责人下拉框
  59. //List<dynamic> _Users = new List<dynamic>();
  60. //var _Users = _sqlSugar.Queryable<Sys_Users>().Where(x => x.IsDel == 0).Select(x => new
  61. //{
  62. // x.Id,
  63. // Name = x.CnName
  64. //}).ToList();
  65. var _Users = GetNewExistClient(Dto.UserId)
  66. .Select(x => new
  67. {
  68. x.Id,
  69. Name = x.CnName
  70. }).ToList();
  71. //List<Sys_Users> users = GetNewExistClient();
  72. //foreach (Sys_Users user in users)
  73. //{
  74. // var data = new
  75. // {
  76. // Id = user.Id,
  77. // Name = user.CnName
  78. // };
  79. // _Users.Add(data);
  80. //};
  81. List<Sys_SetData> initData = _sqlSugar.Queryable<Sys_SetData>().Where(it => it.IsDel == 0).ToList();
  82. //客户级别数据
  83. List<dynamic> _level = new List<dynamic>();
  84. List<Sys_SetData> level = initData.Where(u => u.STid == 33 && u.IsDel == 0).ToList();
  85. foreach (Sys_SetData item in level)
  86. {
  87. var data = new
  88. {
  89. Id = item.Id,
  90. Name = item.Name
  91. };
  92. _level.Add(data);
  93. };
  94. //客户类别
  95. List<dynamic> _CustomerClass = new List<dynamic>();
  96. List<Sys_SetData> CustomerClass = initData.Where(u => u.STid == 37 && u.IsDel == 0).ToList();
  97. foreach (Sys_SetData item in CustomerClass)
  98. {
  99. var data = new
  100. {
  101. Id = item.Id,
  102. Name = item.Name
  103. };
  104. _CustomerClass.Add(data);
  105. };
  106. //业务分类
  107. List<dynamic> _ServiceClass = new List<dynamic>();
  108. List<Sys_SetData> ServiceClass = initData.Where(u => u.STid == 36 && u.IsDel == 0).ToList();
  109. foreach (Sys_SetData item in ServiceClass)
  110. {
  111. var data = new
  112. {
  113. Id = item.Id,
  114. Name = item.Name
  115. };
  116. _ServiceClass.Add(data);
  117. };
  118. //身份分类
  119. List<dynamic> _ProvinceClass = new List<dynamic>();
  120. List<Sys_SetData> ProvinceClass = initData.Where(u => u.STid == 42 && u.IsDel == 0).ToList();
  121. foreach (Sys_SetData item in ProvinceClass)
  122. {
  123. var data = new
  124. {
  125. Id = item.Id,
  126. Name = item.Name
  127. };
  128. _ProvinceClass.Add(data);
  129. };
  130. #endregion
  131. var data1 = new {
  132. Users = _Users,
  133. Level = _level,
  134. CustomerClass = _CustomerClass,
  135. ServiceClass= _ServiceClass,
  136. ProvinceClass = _ProvinceClass
  137. };
  138. return result = new Result()
  139. {
  140. Code = 0,
  141. Msg = "查询成功",
  142. Data = data1
  143. };
  144. }
  145. else
  146. {
  147. result.Msg = string.Format("请传入有效的PortType参数!");
  148. }
  149. return result;
  150. }
  151. /// <summary>
  152. /// 市场客户资料数据
  153. /// 详情
  154. /// </summary>
  155. /// <param name="dto"></param>
  156. /// <returns></returns>
  157. public async Task<Result> _Details(int portType,int id)
  158. {
  159. Result result = new Result() { Code = -2, Msg = "未知错误" };
  160. if (portType == 1 || portType == 2 || portType == 3)
  161. {
  162. if (id < 0)
  163. {
  164. result.Msg = string.Format("请传入有效的Id参数!");
  165. return result;
  166. }
  167. string infoSql = string.Format(@" Select * From Crm_NewClientData Where Isdel = 0 And Id = {0}", id);
  168. var info = await _sqlSugar.SqlQueryable<DetailsView>(infoSql).FirstAsync();
  169. if (info != null)
  170. {
  171. EncryptionProcessor.DecryptProperties(info);
  172. List<AscribedUser> AscribedUser = await _sqlSugar.SqlQueryable<AscribedUser>
  173. ("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();
  174. info.AscribedUser = AscribedUser.Select(it => it.UserId).ToList();
  175. List<AscribedDepartment> AscribedDepartment = await _sqlSugar.SqlQueryable<AscribedDepartment>
  176. ("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();
  177. info.AscribedDepartment = AscribedDepartment.Select(it => it.Id).ToList();
  178. result.Code = 0;
  179. result.Data = info;
  180. }
  181. }
  182. else result.Msg = MsgTips.Port;
  183. return result;
  184. }
  185. /// <summary>
  186. /// 客户资料初识初始化
  187. /// </summary>
  188. /// <param name="dto"></param>
  189. /// <returns></returns>
  190. public async Task<Result> QueryNewClientData(NewClientDataQueryDto dto)
  191. {
  192. Stopwatch stopwatch = Stopwatch.StartNew();
  193. Result result = new Result() { Code = -2, Msg = "未知错误" };
  194. int pageIndex = dto.PageIndex, pageSize = dto.PageSize;
  195. try
  196. {
  197. #region 交集
  198. List<int> NewClientDataId1 = new List<int>();
  199. List<int> NewClientDataId2 = new List<int>();
  200. int state = 0;
  201. #region 负责人
  202. if (dto.OperationUserId != 21)
  203. {
  204. if (string.IsNullOrWhiteSpace(dto.Userid))
  205. {
  206. dto.Userid = dto.OperationUserId.ToString();
  207. }
  208. }
  209. if (!string.IsNullOrWhiteSpace(dto.Userid))
  210. {
  211. 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);
  212. List<AscribedUser> ascribedUsers = await _sqlSugar.SqlQueryable<AscribedUser>(sql).ToListAsync();
  213. if (ascribedUsers.Count != 0)
  214. {
  215. foreach (var ascribedUser in ascribedUsers)
  216. {
  217. if (ascribedUser.NewClientDataId != 0)
  218. {
  219. NewClientDataId1.Add(ascribedUser.NewClientDataId);
  220. }
  221. }
  222. }
  223. else
  224. {
  225. result = new Result() { Code = -1, Msg = "暂无数据" };
  226. }
  227. state = -1;
  228. }
  229. #endregion
  230. #region 业务归属
  231. if (!string.IsNullOrWhiteSpace(dto.Business))
  232. {
  233. 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);
  234. List<AscribedDepartment> AscribedDepartment = await _sqlSugar.SqlQueryable<AscribedDepartment>(sql).ToListAsync();
  235. if (AscribedDepartment.Count != 0)
  236. {
  237. foreach (var item in AscribedDepartment)
  238. {
  239. if (item.NewClientDataId != 0)
  240. {
  241. NewClientDataId2.Add(item.NewClientDataId);
  242. }
  243. }
  244. }
  245. else
  246. {
  247. result = new Result() { Code = -1, Msg = "暂无数据" };
  248. }
  249. state = -1;
  250. }
  251. #endregion
  252. List<int> intList = new List<int>();
  253. if (NewClientDataId1.Count != 0 && NewClientDataId2.Count != 0)
  254. {
  255. intList = NewClientDataId1.Intersect(NewClientDataId2).ToList();
  256. }
  257. else if (NewClientDataId1.Count != 0)
  258. {
  259. intList = NewClientDataId1;
  260. }
  261. else if (NewClientDataId2.Count != 0)
  262. {
  263. intList = NewClientDataId2;
  264. }
  265. if (state == -1)
  266. {
  267. if (intList.Count < 1) intList.Add(-1);
  268. }
  269. #endregion
  270. #region 省域条件
  271. var rangeSetDataList = new List<int>();
  272. if (dto.Range != 0)
  273. {
  274. string setDataSql = "select * from Sys_SetData where STid = 33 and isdel = 0 ";
  275. switch (dto.Range)
  276. {
  277. case 419:
  278. setDataSql += " and (Name like '%四%川%' or Name like '%成%都%')";
  279. break;
  280. case 421:
  281. setDataSql += " and (Name like '%贵%州%' or Name like '%贵%阳%')";
  282. break;
  283. case 420:
  284. setDataSql += " and (Name like '%云%南%' or Name like '%昆%明%')";
  285. break;
  286. case 423:
  287. setDataSql += " and (Name like '%重庆%')";
  288. break;
  289. case 422:
  290. setDataSql += " and (Name like '%西%藏%' or Name like '%拉%萨%')";
  291. break;
  292. case 578:
  293. setDataSql += " and (Name like '%青%海%' or Name like '%西%宁%')";
  294. break;
  295. case 605:
  296. setDataSql += " and (Name like '%陕%西%' or Name like '%西%安%')";
  297. break;
  298. case 606:
  299. setDataSql += " and (Name like '%宁%夏%' or Name like '%银%川%')";
  300. break;
  301. case 625:
  302. setDataSql += " and (Name like '%甘%肃%' or Name like '%兰%州%')";
  303. break;
  304. case 634:
  305. setDataSql += " and (Name like '%新%疆%' or Name like '%乌%鲁%木%齐%')";
  306. break;
  307. }
  308. rangeSetDataList = _sqlSugar.SqlQueryable<Sys_SetData>(setDataSql).Select(x => x.Id).ToList();
  309. }
  310. #endregion
  311. var NewClientDataView = new List<NewClientDataView>();
  312. var count = 0;
  313. string contact = dto.Contact, location = dto.Location, clientDto = dto.Client;
  314. var isSelectSearch = false;
  315. var searchDataIds = new List<int>();
  316. if (string.IsNullOrEmpty(contact) || string.IsNullOrEmpty(location) || string.IsNullOrEmpty(clientDto))
  317. {
  318. isSelectSearch = true;
  319. var searchClientDatas = await _sqlSugar.Queryable<Crm_NewClientData>()
  320. .Where(x => x.IsDel == 0)
  321. .Select(x => new Crm_NewClientData() { Id = x.Id, Client = x.Client, Location = x.Location, Contact = x.Contact })
  322. .ToListAsync();
  323. foreach (var item in searchClientDatas) EncryptionProcessor.DecryptProperties(item);
  324. if (dto.PortType == 1)
  325. {
  326. searchDataIds = searchClientDatas
  327. .WhereIF(!string.IsNullOrEmpty(contact), x => !string.IsNullOrEmpty(x.Contact) && x.Contact.Contains(contact)) //联系人条件
  328. .WhereIF(!string.IsNullOrEmpty(location), x => !string.IsNullOrEmpty(x.Location) && x.Location.Contains(location)) //地区条件
  329. .WhereIF(!string.IsNullOrEmpty(clientDto), x => !string.IsNullOrEmpty(x.Client) && x.Client.Contains(clientDto)) //单位条件
  330. .Select(x => x.Id)
  331. .ToList();
  332. }
  333. else if (dto.PortType == 2 || dto.PortType == 3)
  334. {
  335. searchDataIds = searchClientDatas
  336. .Where(x => (!string.IsNullOrEmpty(x.Contact) && x.Contact.Contains(clientDto)) ||
  337. (!string.IsNullOrEmpty(x.Location) && x.Location.Contains(clientDto)) ||
  338. (!string.IsNullOrEmpty(x.Client) && x.Client.Contains(clientDto)))
  339. .Select(x => x.Id)
  340. .ToList();
  341. }
  342. if (searchDataIds.Count < 1) searchDataIds.Add(0);
  343. }
  344. RefAsync<int> total = 0;
  345. var clientDatas = await _sqlSugar.Queryable<Crm_NewClientData>()
  346. .Where(x => x.IsDel == 0)
  347. .WhereIF(state == -1 && intList.Count > 0, x => intList.Contains(x.Id))
  348. .WhereIF(dto.Lvlid != 0, x => x.Lvlid == dto.Lvlid) //地市州条件
  349. .WhereIF(rangeSetDataList.Count > 0, x => rangeSetDataList.Contains(x.Lvlid)) //省域条件
  350. .WhereIF(dto.Category > 0, x => x.Category == dto.Category) //客户类别
  351. .WhereIF(isSelectSearch && searchDataIds.Count > 0 , x => searchDataIds.Contains(x.Id)) //条件模糊查询
  352. .OrderByDescending(x => x.CreateTime)
  353. .ToPageListAsync(pageIndex, pageSize, total);
  354. NewClientDataView = _mapper.Map<List<NewClientDataView>>(clientDatas);
  355. count = total;
  356. var setDatas = _sqlSugar.Queryable<Sys_SetData>().Where(x => x.IsDel == 0).ToList();
  357. #region 下拉框初始化数据
  358. //负责人下拉框
  359. var _Users = GetNewExistClient(dto.OperationUserId).Select(x => new { x.Id, Name = x.CnName }).ToList();
  360. //省域数据
  361. var _Province = setDatas.Where(u => u.STid == 42).Select(x => new { x.Id, x.Name }).ToList();
  362. //客户级别数据
  363. var _level = setDatas.Where(u => u.STid == 33).Select(x => new { x.Id, x.Name }).ToList();
  364. //客户类别
  365. var _CustomerClass = setDatas.Where(u => u.STid == 37).Select(x => new { x.Id, x.Name }).ToList();
  366. //业务分类
  367. var _ServiceClass = setDatas.Where(u => u.STid == 36).Select(x => new { x.Id, x.Name }).ToList();
  368. #endregion
  369. var groupNumber = await QueryNumberGroups();
  370. if (NewClientDataView.Count > 0)
  371. {
  372. float totalPage = (float)count / dto.PageSize;//总页数
  373. if (totalPage == 0) totalPage = 1;
  374. else totalPage = (int)Math.Ceiling((double)totalPage);
  375. if (dto.PortType == 1)
  376. {
  377. int index = 1;
  378. foreach (var item in NewClientDataView)
  379. {
  380. //EncryptionProcessor.DecryptProperties(item); //解密
  381. item.Weight = AesEncryptionHelper.Decrypt(item.Weight);
  382. item.Client = AesEncryptionHelper.Decrypt(item.Client);
  383. item.Contact = AesEncryptionHelper.Decrypt(item.Contact);
  384. item.Job = AesEncryptionHelper.Decrypt(item.Job);
  385. item.Telephone = AesEncryptionHelper.Decrypt(item.Telephone);
  386. item.Phone = AesEncryptionHelper.Decrypt(item.Phone);
  387. item.Location = AesEncryptionHelper.Decrypt(item.Location);
  388. item.Remark = AesEncryptionHelper.Decrypt(item.Remark);
  389. item.RowNumber = index;
  390. item.CategoryStr = setDatas.Find(x => x.Id == item.Category)?.Name ?? "-";
  391. item.LvlidStr = setDatas.Find(x => x.Id == item.Lvlid)?.Name ?? "-";
  392. List<AscribedUser> AscribedUser = await _sqlSugar.SqlQueryable<AscribedUser>
  393. ("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();
  394. item.AscribedUser = AscribedUser;
  395. List<AscribedDepartment> AscribedDepartment = await _sqlSugar.SqlQueryable<AscribedDepartment>
  396. ("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();
  397. item.AscribedDepartment = AscribedDepartment;
  398. index++;
  399. }
  400. var Data = new
  401. {
  402. ClientTableData = new { pageCount = count, totalPage = (int)totalPage, pageIndex = dto.PageIndex, pageSize = dto.PageSize, pageSource = NewClientDataView },
  403. Users = _Users,
  404. Province = _Province,
  405. level = _level,
  406. CustomerClass = _CustomerClass,
  407. ServiceClass = _ServiceClass,
  408. groupNumber = groupNumber.Data,
  409. };
  410. return result = new Result()
  411. {
  412. Code = 0,
  413. Msg = $"查询成功!解密耗时: {stopwatch.ElapsedMilliseconds} 毫秒",
  414. Data = Data
  415. };
  416. }
  417. else if (dto.PortType == 2 || dto.PortType == 3)
  418. {
  419. List<NewClientDataAndroidIOSView> newClientDataIOSViews = new List<NewClientDataAndroidIOSView>();
  420. int index = 1;
  421. foreach (var item in NewClientDataView)
  422. {
  423. newClientDataIOSViews.Add(new NewClientDataAndroidIOSView()
  424. {
  425. RowNumber = index,
  426. Id = item.Id,
  427. Client = AesEncryptionHelper.Decrypt(item.Client),
  428. Contact = AesEncryptionHelper.Decrypt(item.Contact),
  429. Job = AesEncryptionHelper.Decrypt(item.Job),
  430. Telephone = AesEncryptionHelper.Decrypt(item.Telephone),
  431. Location = AesEncryptionHelper.Decrypt(item.Location),
  432. });
  433. index++;
  434. }
  435. result = new Result()
  436. {
  437. Code = 0,
  438. Msg = $"查询成功!解密耗时: {stopwatch.ElapsedMilliseconds} 毫秒",
  439. Data = new { pageCount = count, totalPage = (int)totalPage, pageIndex = dto.PageIndex, pageSize = dto.PageSize, pageSource = newClientDataIOSViews },
  440. };
  441. }
  442. }
  443. else
  444. {
  445. if (dto.PortType == 2 || dto.PortType == 3)
  446. {
  447. var Data = new { pageCount = 0, totalPage = 0, pageIndex = dto.PageIndex, pageSize = dto.PageSize, pageSource = NewClientDataView };
  448. result = new Result() { Code = 0, Msg = "获取成功!", Data = Data };
  449. }
  450. else
  451. {
  452. var Data = new
  453. {
  454. ClientTableData = new { pageCount = 0, totalPage = 0, pageIndex = dto.PageIndex, pageSize = dto.PageSize, pageSource = NewClientDataView },
  455. Users = _Users,
  456. Province = _Province,
  457. level = _level,
  458. CustomerClass = _CustomerClass,
  459. ServiceClass = _ServiceClass,
  460. groupNumber = groupNumber.Data,
  461. };
  462. result = new Result() { Code = 0, Msg = "获取成功!", Data = Data };
  463. }
  464. }
  465. }
  466. catch (Exception ex)
  467. {
  468. result = new Result() { Code = -2, Msg = "未知错误" };
  469. }
  470. stopwatch.Stop();
  471. result.Msg = $"耗时: {stopwatch.ElapsedMilliseconds} 毫秒";
  472. return result;
  473. }
  474. /// <summary>
  475. /// 客户资料初识初始化
  476. /// </summary>
  477. /// <param name="dto"></param>
  478. /// <returns></returns>
  479. public async Task<Result> QueryNewClientData1(NewClientDataQueryDto dto)
  480. {
  481. Result result = new Result() { Code = -2, Msg = "未知错误" };
  482. try
  483. {
  484. #region 交集
  485. List<int> NewClientDataId1 = new List<int>();
  486. List<int> NewClientDataId2 = new List<int>();
  487. string NewClientDataId = "";
  488. int state = 0;
  489. #region 负责人
  490. if (dto.OperationUserId != 21)
  491. {
  492. if (string.IsNullOrWhiteSpace(dto.Userid))
  493. {
  494. dto.Userid = dto.OperationUserId.ToString();
  495. }
  496. }
  497. if (!string.IsNullOrWhiteSpace(dto.Userid))
  498. {
  499. 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);
  500. List<AscribedUser> ascribedUsers = await _sqlSugar.SqlQueryable<AscribedUser>(sql).ToListAsync();
  501. if (ascribedUsers.Count != 0)
  502. {
  503. foreach (var ascribedUser in ascribedUsers)
  504. {
  505. if (ascribedUser.NewClientDataId != 0)
  506. {
  507. NewClientDataId1.Add(ascribedUser.NewClientDataId);
  508. }
  509. }
  510. }
  511. else
  512. {
  513. result = new Result() { Code = -1, Msg = "暂无数据" };
  514. }
  515. state = -1;
  516. }
  517. #endregion
  518. #region 业务归属
  519. if (!string.IsNullOrWhiteSpace(dto.Business))
  520. {
  521. 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);
  522. List<AscribedDepartment> AscribedDepartment = await _sqlSugar.SqlQueryable<AscribedDepartment>(sql).ToListAsync();
  523. if (AscribedDepartment.Count != 0)
  524. {
  525. foreach (var item in AscribedDepartment)
  526. {
  527. if (item.NewClientDataId != 0)
  528. {
  529. NewClientDataId2.Add(item.NewClientDataId);
  530. }
  531. }
  532. }
  533. else
  534. {
  535. result = new Result() { Code = -1, Msg = "暂无数据" };
  536. }
  537. state = -1;
  538. }
  539. #endregion
  540. List<int> intList = new List<int>();
  541. if (NewClientDataId1.Count != 0 && NewClientDataId2.Count != 0)
  542. {
  543. intList = NewClientDataId1.Intersect(NewClientDataId2).ToList();
  544. }
  545. else if (NewClientDataId1.Count != 0)
  546. {
  547. intList = NewClientDataId1;
  548. }
  549. else if (NewClientDataId2.Count != 0)
  550. {
  551. intList = NewClientDataId2;
  552. }
  553. #endregion
  554. foreach (var item in intList)
  555. {
  556. NewClientDataId += item + ",";
  557. }
  558. if (!string.IsNullOrWhiteSpace(NewClientDataId))
  559. {
  560. NewClientDataId = NewClientDataId.Substring(0, NewClientDataId.Length - 1);
  561. }
  562. string sqlWhere = string.Empty;
  563. if (dto.PortType == 1)
  564. {
  565. #region 联系人条件
  566. if (!string.IsNullOrWhiteSpace(dto.Contact))
  567. {
  568. sqlWhere += string.Format(@" And s.Contact like '%{0}%'", dto.Contact);
  569. }
  570. #endregion
  571. #region 地区条件
  572. if (!string.IsNullOrWhiteSpace(dto.Location))
  573. {
  574. sqlWhere += string.Format(@" And s.Location like '%{0}%'", dto.Location);
  575. }
  576. #endregion
  577. #region 单位条件
  578. if (!string.IsNullOrWhiteSpace(dto.Client))
  579. {
  580. sqlWhere += string.Format(@" And s.Client like '%{0}%'", dto.Client);
  581. }
  582. #endregion
  583. }
  584. else if (dto.PortType == 2 || dto.PortType == 3)
  585. {
  586. sqlWhere += string.Format("And (Contact like '%{0}%' or Location like '%{0}%' or Client like '%{0}%' )", dto.Client);
  587. }
  588. if (state == -1)
  589. {
  590. if (string.IsNullOrWhiteSpace(NewClientDataId))
  591. {
  592. NewClientDataId = "0";
  593. }
  594. sqlWhere += string.Format(@" And s.Id in({0})", NewClientDataId);
  595. }
  596. #region 地市州条件
  597. if (dto.Lvlid != 0)
  598. {
  599. sqlWhere += string.Format(@" And s.Lvlid={0}", dto.Lvlid);
  600. }
  601. #endregion
  602. #region 省域条件
  603. if (dto.Range != 0)
  604. {
  605. string setDataSql = "select * from Sys_SetData where STid = 33 and isdel = 0 ";
  606. switch (dto.Range)
  607. {
  608. case 419:
  609. setDataSql += " and (Name like '%四%川%' or Name like '%成%都%')";
  610. break;
  611. case 421:
  612. setDataSql += " and (Name like '%贵%州%' or Name like '%贵%阳%')";
  613. break;
  614. case 420:
  615. setDataSql += " and (Name like '%云%南%' or Name like '%昆%明%')";
  616. break;
  617. case 423:
  618. setDataSql += " and (Name like '%重庆%')";
  619. break;
  620. case 422:
  621. setDataSql += " and (Name like '%西%藏%' or Name like '%拉%萨%')";
  622. break;
  623. case 578:
  624. setDataSql += " and (Name like '%青%海%' or Name like '%西%宁%')";
  625. break;
  626. case 605:
  627. setDataSql += " and (Name like '%陕%西%' or Name like '%西%安%')";
  628. break;
  629. case 606:
  630. setDataSql += " and (Name like '%宁%夏%' or Name like '%银%川%')";
  631. break;
  632. case 625:
  633. setDataSql += " and (Name like '%甘%肃%' or Name like '%兰%州%')";
  634. break;
  635. case 634:
  636. setDataSql += " and (Name like '%新%疆%' or Name like '%乌%鲁%木%齐%')";
  637. break;
  638. }
  639. var RangeSetDataList = _sqlSugar.SqlQueryable<Sys_SetData>(setDataSql).Select(x => x.Id).ToList();
  640. string lvlds = string.Join(',', RangeSetDataList).TrimEnd(',');
  641. if (!string.IsNullOrEmpty(lvlds))
  642. {
  643. sqlWhere += string.Format(@" And s.Lvlid in ({0}) ", lvlds);
  644. }
  645. }
  646. #endregion
  647. #region 客户类别
  648. if (dto.Category != 0)
  649. {
  650. sqlWhere += string.Format(@" And s.Category = {0}", dto.Category);
  651. }
  652. #endregion
  653. sqlWhere += string.Format(@" And s.Lvlid=s1.Id And s.IsDel={0} ", 0);
  654. if (!string.IsNullOrEmpty(sqlWhere.Trim()))
  655. {
  656. Regex r = new Regex("And");
  657. sqlWhere = r.Replace(sqlWhere, "Where", 1);
  658. }
  659. int pIndex = dto.PageIndex * dto.PageSize - dto.PageSize + 1;
  660. int pSize = dto.PageIndex * dto.PageSize;
  661. 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()
  662. 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
  663. RowNumber between {1} and {2} ", sqlWhere, pIndex, pSize);
  664. List<NewClientDataView> NewClientDataView = await _sqlSugar.SqlQueryable<NewClientDataView>(sqlNew).ToListAsync();
  665. foreach (var item in NewClientDataView)
  666. {
  667. Sys_SetData CategoryStr = _sqlSugar.Queryable<Sys_SetData>().Single(it => it.Id == item.Category);
  668. if (CategoryStr != null)
  669. {
  670. item.CategoryStr = CategoryStr != null ? CategoryStr.Name : null;
  671. }
  672. Sys_SetData lvlStr = _sqlSugar.Queryable<Sys_SetData>().Single(it => it.Id == item.Lvlid);
  673. if (lvlStr != null)
  674. {
  675. item.LvlidStr = lvlStr != null ? lvlStr.Name : null;
  676. }
  677. }
  678. #region 下拉框初始化数据
  679. //负责人下拉框
  680. List<dynamic> _Users = new List<dynamic>();
  681. List<Sys_Users> users = GetNewExistClient(dto.OperationUserId);
  682. foreach (Sys_Users user in users)
  683. {
  684. var data = new
  685. {
  686. Id = user.Id,
  687. Name = user.CnName
  688. };
  689. _Users.Add(data);
  690. };
  691. //省域数据
  692. List<dynamic> _Province = new List<dynamic>();
  693. List<Sys_SetData> province = _sqlSugar.Queryable<Sys_SetData>()
  694. .Where(u => u.STid == 42 && u.IsDel == 0).ToList();
  695. foreach (Sys_SetData item in province)
  696. {
  697. var data = new
  698. {
  699. Id = item.Id,
  700. Name = item.Name
  701. };
  702. _Province.Add(data);
  703. };
  704. //客户级别数据
  705. List<dynamic> _level = new List<dynamic>();
  706. List<Sys_SetData> level = _sqlSugar.Queryable<Sys_SetData>()
  707. .Where(u => u.STid == 33 && u.IsDel == 0).ToList();
  708. foreach (Sys_SetData item in level)
  709. {
  710. var data = new
  711. {
  712. Id = item.Id,
  713. Name = item.Name
  714. };
  715. _level.Add(data);
  716. };
  717. //客户类别
  718. List<dynamic> _CustomerClass = new List<dynamic>();
  719. List<Sys_SetData> CustomerClass = _sqlSugar.Queryable<Sys_SetData>()
  720. .Where(u => u.STid == 37 && u.IsDel == 0).ToList();
  721. foreach (Sys_SetData item in CustomerClass)
  722. {
  723. var data = new
  724. {
  725. Id = item.Id,
  726. Name = item.Name
  727. };
  728. _CustomerClass.Add(data);
  729. };
  730. //业务分类
  731. List<dynamic> _ServiceClass = new List<dynamic>();
  732. List<Sys_SetData> ServiceClass = _sqlSugar.Queryable<Sys_SetData>()
  733. .Where(u => u.STid == 36 && u.IsDel == 0).ToList();
  734. foreach (Sys_SetData item in ServiceClass)
  735. {
  736. var data = new
  737. {
  738. Id = item.Id,
  739. Name = item.Name
  740. };
  741. _ServiceClass.Add(data);
  742. };
  743. #endregion
  744. var groupNumber = await QueryNumberGroups();
  745. if (NewClientDataView.Count > 0)
  746. {
  747. int count = NewClientDataView[0].countPage;
  748. float totalPage = (float)count / dto.PageSize;//总页数
  749. if (totalPage == 0) totalPage = 1;
  750. else totalPage = (int)Math.Ceiling((double)totalPage);
  751. if (dto.PortType == 1)
  752. {
  753. foreach (var item in NewClientDataView)
  754. {
  755. List<AscribedUser> AscribedUser = await _sqlSugar.SqlQueryable<AscribedUser>
  756. ("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();
  757. item.AscribedUser = AscribedUser;
  758. List<AscribedDepartment> AscribedDepartment = await _sqlSugar.SqlQueryable<AscribedDepartment>
  759. ("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();
  760. item.AscribedDepartment = AscribedDepartment;
  761. }
  762. var Data = new
  763. {
  764. ClientTableData = new { pageCount = count, totalPage = (int)totalPage, pageIndex = dto.PageIndex, pageSize = dto.PageSize, pageSource = NewClientDataView },
  765. Users = _Users,
  766. Province = _Province,
  767. level = _level,
  768. CustomerClass = _CustomerClass,
  769. ServiceClass = _ServiceClass,
  770. groupNumber = groupNumber.Data,
  771. };
  772. return result = new Result()
  773. {
  774. Code = 0,
  775. Msg = "查询成功",
  776. Data = Data
  777. };
  778. }
  779. else if (dto.PortType == 2 || dto.PortType == 3)
  780. {
  781. List<NewClientDataAndroidIOSView> newClientDataIOSViews = new List<NewClientDataAndroidIOSView>();
  782. foreach (var item in NewClientDataView)
  783. {
  784. newClientDataIOSViews.Add(new NewClientDataAndroidIOSView()
  785. {
  786. RowNumber = item.RowNumber,
  787. Id = item.Id,
  788. Client = item.Client,
  789. Contact = item.Contact,
  790. Job = item.Job,
  791. Telephone = item.Telephone,
  792. Location = item.Location,
  793. });
  794. }
  795. result = new Result()
  796. {
  797. Code = 0,
  798. Msg = "查询成功",
  799. Data = new { pageCount = count, totalPage = (int)totalPage, pageIndex = dto.PageIndex, pageSize = dto.PageSize, pageSource = newClientDataIOSViews },
  800. };
  801. }
  802. }
  803. else
  804. {
  805. if (dto.PortType == 2 || dto.PortType == 3)
  806. {
  807. var Data = new { pageCount = 0, totalPage = 0, pageIndex = dto.PageIndex, pageSize = dto.PageSize, pageSource = NewClientDataView };
  808. result = new Result() { Code = 0, Msg = "获取成功!", Data = Data };
  809. }
  810. else
  811. {
  812. var Data = new
  813. {
  814. ClientTableData = new { pageCount = 0, totalPage = 0, pageIndex = dto.PageIndex, pageSize = dto.PageSize, pageSource = NewClientDataView },
  815. Users = _Users,
  816. Province = _Province,
  817. level = _level,
  818. CustomerClass = _CustomerClass,
  819. ServiceClass = _ServiceClass,
  820. groupNumber = groupNumber.Data,
  821. };
  822. result = new Result() { Code = 0, Msg = "获取成功!", Data = Data };
  823. }
  824. }
  825. }
  826. catch (Exception ex)
  827. {
  828. result = new Result() { Code = -2, Msg = "未知错误" };
  829. }
  830. return result;
  831. }
  832. public Result QueryUserSelect()
  833. {
  834. Result result = new Result() { Code = -2, Msg = "未知错误" };
  835. try
  836. {
  837. //负责人下拉框
  838. List<dynamic> _Users = new List<dynamic>();
  839. List<Sys_Users> users = _sqlSugar.Queryable<Sys_Users>()
  840. .Where(u => u.IsDel == 0).ToList();
  841. foreach (Sys_Users user in users)
  842. {
  843. var data = new
  844. {
  845. Id = user.Id,
  846. Name = user.CnName
  847. };
  848. _Users.Add(data);
  849. };
  850. if (_Users.Count == 0)
  851. {
  852. result = new Result() { Code = -1, Msg = "暂无数据" };
  853. }
  854. result = new Result() { Code = 0, Msg = "查询成功!", Data = _Users };
  855. }
  856. catch (Exception)
  857. {
  858. result = new Result() { Code = -2, Msg = "未知错误" };
  859. }
  860. return result;
  861. }
  862. public async Task<Result> NewClientOp(NewClientOpDto dto)
  863. {
  864. Result result = new Result() { Code = -2, Msg = "未知错误" };
  865. bool istrue = false;
  866. int AddReturnId = -1;
  867. string BirthdayStr = string.Empty;
  868. if (!string.IsNullOrWhiteSpace(dto.Birthday))
  869. {
  870. DateTime Birthday = new DateTime();
  871. var isParse = DateTime.TryParse(dto.Birthday, out Birthday);
  872. BirthdayStr = isParse ? Birthday.ToString("yyyy-MM-dd") : "";
  873. }
  874. if (string.IsNullOrWhiteSpace(dto.PassportDate))
  875. {
  876. dto.PassportDate = null;
  877. }
  878. var newClientDataUnEncrypted = _mapper.Map<Crm_NewClientData>(dto);
  879. newClientDataUnEncrypted.Birthday = BirthdayStr;
  880. var newClientDataEncrypted = newClientDataUnEncrypted;
  881. EncryptionProcessor.EncryptProperties(newClientDataEncrypted); //加密
  882. try
  883. {
  884. BeginTran();
  885. if (dto.Status == 1)//添加
  886. {
  887. string selectSql = string.Format(@"select * from Crm_NewClientData where Client='{0}' And Contact='{1}' And IsDel={2}"
  888. , newClientDataEncrypted.Client, newClientDataEncrypted.Contact, 0);
  889. var NewClientData = await _sqlSugar.SqlQueryable<Crm_NewClientData>(selectSql).FirstAsync();//查询是否存在
  890. if (NewClientData != null)
  891. {
  892. result = new Result() { Code = -1, Msg = "该信息已存在,请勿重复添加!" };
  893. }
  894. int id = await AddAsyncReturnId(newClientDataEncrypted); //添加市场客户资料表数据
  895. if (id == 0)
  896. {
  897. result = new Result() { Code = -1, Msg = "添加失败!" };
  898. }
  899. else
  900. {
  901. result = new Result() { Code = 0, Msg = "添加成功!", Data = id };
  902. istrue = true;
  903. AddReturnId = id;
  904. newClientDataUnEncrypted.Id = id;
  905. }
  906. }
  907. else if (dto.Status == 2)//修改
  908. {
  909. DateTime? PassportDate = null;
  910. try
  911. {
  912. PassportDate = DateTime.Parse(dto.PassportDate);
  913. }
  914. catch (Exception)
  915. {
  916. PassportDate = null;
  917. }
  918. var res = await _sqlSugar.Updateable(newClientDataEncrypted).IgnoreColumns(x => new { x.DeleteTime, x.DeleteUserId, x.CreateTime, x.CreateUserId }).ExecuteCommandAsync();
  919. if (res > 0)
  920. {
  921. istrue = true;
  922. AddReturnId = dto.Id == 0 ? -1 : dto.Id;
  923. if (AddReturnId != -1)
  924. {
  925. await _sqlSugar.Updateable<Crm_ClientDataAndUser>().Where(x=>x.NewClientDataId == AddReturnId).SetColumns(a => new Crm_ClientDataAndUser()
  926. {
  927. IsDel = 1,
  928. DeleteTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
  929. }).ExecuteCommandAsync();
  930. await _sqlSugar.Updateable<Crm_ClientDataAndBusiness>().Where(x => x.NewClientDataId == AddReturnId).SetColumns(a => new Crm_ClientDataAndBusiness()
  931. {
  932. IsDel = 1,
  933. DeleteTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
  934. }).ExecuteCommandAsync();
  935. }
  936. newClientDataUnEncrypted.Id = AddReturnId;
  937. result = new Result() { Code = 0, Msg = "修改成功!" };
  938. }
  939. else
  940. {
  941. result = new Result() { Code = -1, Msg = "修改失败!" };
  942. }
  943. }
  944. else
  945. {
  946. result = new Result() { Code = -1, Msg = "请传入Status参数,1添加 2修改!" };
  947. }
  948. if (istrue)
  949. {
  950. Adds<Crm_ClientDataAndUser>(dto.AscribedUser.Select(x => new Crm_ClientDataAndUser
  951. {
  952. CreateTime = DateTime.Now,
  953. CreateUserId = dto.CreateUserId,
  954. IsDel = 0,
  955. NewClientDataId = AddReturnId,
  956. usersId = x
  957. }).ToList());
  958. Adds<Crm_ClientDataAndBusiness>(dto.AscribedDepartment.Select(x => new Crm_ClientDataAndBusiness
  959. {
  960. CreateUserId = dto.CreateUserId,
  961. IsDel = 0,
  962. CreateTime = DateTime.Now,
  963. NewClientDataId = AddReturnId,
  964. SetDataId = x,
  965. }).ToList());
  966. CommitTran();
  967. ////缓存添加信息
  968. //var optype = 0;
  969. //if (dto.Status == 1) optype = 3;
  970. //else if (dto.Status == 2) optype = 4;
  971. ////操作缓存(未加密数据)
  972. //await NewClientDataRedis(optype, newClientDataUnEncrypted);
  973. result.Data = AddReturnId;
  974. }
  975. else
  976. {
  977. RollbackTran();
  978. }
  979. }
  980. catch (Exception)
  981. {
  982. RollbackTran();
  983. result = new Result() { Code = -2, Msg = "未知错误" };
  984. }
  985. return result;
  986. }
  987. public async Task<Result> QueryNumberGroups()
  988. {
  989. Result result = new Result();
  990. //preDeleAll 预计总量
  991. //finlishedDeleAll 已出总量
  992. DataTable preDeleAndfinlishedDeleAll = await GetDataTableAsync("select SUM(PreDele) as PreDeleAll ,SUM(FinlishedDele) as FinlishedDeleAll from Crm_NewClientData");
  993. var preDeleAll = preDeleAndfinlishedDeleAll.Rows[0]["PreDeleAll"].ToString();
  994. var finlishedDeleAll = preDeleAndfinlishedDeleAll.Rows[0]["finlishedDeleAll"].ToString();
  995. result.Code = 0;
  996. result.Msg = "成功!";
  997. result.Data = new
  998. {
  999. preDeleAll,
  1000. finlishedDeleAll
  1001. };
  1002. return result;
  1003. }
  1004. /// <summary>
  1005. /// 获取下拉列表数据和单条数据信息
  1006. /// </summary>
  1007. /// <param name="dto"></param>
  1008. public async Task<Result> QuerySelectAndSingleData(QuerySingleDto dto)
  1009. {
  1010. Result rest = new Result();
  1011. //var QueryData1 = await NewClientDataRedis(2, new Crm_NewClientData() { Id = dto.Id });
  1012. //var QueryData = QueryData1.FirstOrDefault();
  1013. var QueryData = await _sqlSugar.Queryable<Crm_NewClientData>().Where(x => x.IsDel == 0 && x.Id == dto.Id).FirstAsync();
  1014. NewClientDataView MapQueryData = null;
  1015. if (QueryData != null)
  1016. {
  1017. EncryptionProcessor.DecryptProperties(QueryData);
  1018. MapQueryData = _mapper.Map<NewClientDataView>(QueryData);
  1019. MapQueryData.AscribedUser = await _sqlSugar.SqlQueryable<AscribedUser>
  1020. ("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();
  1021. MapQueryData.AscribedDepartment = await _sqlSugar.SqlQueryable<AscribedDepartment>
  1022. ("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();
  1023. }
  1024. #region 下拉框初始化数据
  1025. //负责人下拉框
  1026. List<dynamic> _Users = new List<dynamic>();
  1027. List<Sys_Users> users = _sqlSugar.Queryable<Sys_Users>()
  1028. .Where(u => u.IsDel == 0).ToList();
  1029. foreach (Sys_Users user in users)
  1030. {
  1031. var data = new
  1032. {
  1033. Id = user.Id,
  1034. Name = user.CnName
  1035. };
  1036. _Users.Add(data);
  1037. };
  1038. //客户级别数据
  1039. List<dynamic> _level = new List<dynamic>();
  1040. List<Sys_SetData> level = _sqlSugar.Queryable<Sys_SetData>()
  1041. .Where(u => u.STid == 33 && u.IsDel == 0).ToList();
  1042. foreach (Sys_SetData item in level)
  1043. {
  1044. var data = new
  1045. {
  1046. Id = item.Id,
  1047. Name = item.Name
  1048. };
  1049. _level.Add(data);
  1050. };
  1051. //客户类别
  1052. List<dynamic> _CustomerClass = new List<dynamic>();
  1053. List<Sys_SetData> CustomerClass = _sqlSugar.Queryable<Sys_SetData>()
  1054. .Where(u => u.STid == 37 && u.IsDel == 0).ToList();
  1055. foreach (Sys_SetData item in CustomerClass)
  1056. {
  1057. var data = new
  1058. {
  1059. Id = item.Id,
  1060. Name = item.Name
  1061. };
  1062. _CustomerClass.Add(data);
  1063. };
  1064. //业务分类
  1065. List<dynamic> _ServiceClass = new List<dynamic>();
  1066. List<Sys_SetData> ServiceClass = _sqlSugar.Queryable<Sys_SetData>()
  1067. .Where(u => u.STid == 36 && u.IsDel == 0).ToList();
  1068. foreach (Sys_SetData item in ServiceClass)
  1069. {
  1070. var data = new
  1071. {
  1072. Id = item.Id,
  1073. Name = item.Name
  1074. };
  1075. _ServiceClass.Add(data);
  1076. };
  1077. #endregion
  1078. rest.Code = 0;
  1079. rest.Data = new
  1080. {
  1081. data = MapQueryData,
  1082. Users = _Users,
  1083. level = _level,
  1084. CustomerClass = _CustomerClass,
  1085. ServiceClass = _ServiceClass,
  1086. };
  1087. rest.Msg = "获取成功!";
  1088. return rest;
  1089. }
  1090. /// <summary>
  1091. /// 删除市场客户资料数据
  1092. /// </summary>
  1093. /// <param name="dto"></param>
  1094. /// <returns></returns>
  1095. public async Task<Result> DelNewClientData(DelBaseDto dto)
  1096. {
  1097. Result AcrionResult = new Result();
  1098. BeginTran();
  1099. var DBresult = await SoftDeleteByIdAsync<Crm_NewClientData>(dto.Id.ToString(), dto.DeleteUserId);
  1100. try
  1101. {
  1102. if (DBresult)
  1103. {
  1104. AcrionResult.Code = 0;
  1105. string sqlSet = $"isdel = 1, DeleteUserId = {dto.DeleteUserId} ,DeleteTime = '{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}'";
  1106. string sql = $" update Crm_ClientDataAndUser set {sqlSet} where NewClientDataId = {dto.Id} ";
  1107. await ExecuteCommandAsync(sql);
  1108. sql = $" update Crm_ClientDataAndBusiness set {sqlSet} where NewClientDataId = {dto.Id} ";
  1109. await ExecuteCommandAsync(sql);
  1110. CommitTran();
  1111. //await NewClientDataRedis(5, new Crm_NewClientData() { Id = dto.Id });
  1112. AcrionResult.Code = 0;
  1113. }
  1114. }
  1115. catch (Exception ex)
  1116. {
  1117. RollbackTran();
  1118. AcrionResult.Msg = ex.Message;
  1119. AcrionResult.Code = -1;
  1120. }
  1121. return AcrionResult;
  1122. }
  1123. /// <summary>
  1124. /// 市场客户资料数据
  1125. /// 批量指派
  1126. /// </summary>
  1127. /// <param name="dto"></param>
  1128. /// <returns></returns>
  1129. public async Task<Result> _BatchAssignment(BatchAssignmentDto dto)
  1130. {
  1131. Result AcrionResult = new Result() { Code = -1,Msg="操作失败"};
  1132. if (dto.UserIdItem == null || dto.UserIdItem.Count < 1)
  1133. {
  1134. AcrionResult.Msg = "用户ID集合不能为空!";
  1135. return AcrionResult;
  1136. }
  1137. if (dto.ClientDataIdItem == null || dto.ClientDataIdItem.Count < 1)
  1138. {
  1139. AcrionResult.Msg = "客户资料ID集合不能为空!";
  1140. return AcrionResult;
  1141. }
  1142. List<Crm_ClientDataAndUser> _ClientDataAndUsers = new List<Crm_ClientDataAndUser>();
  1143. List<Crm_ClientDataAndUser> _ClientDataAndUsers1 = await _sqlSugar.Queryable<Crm_ClientDataAndUser>()
  1144. .Where(it =>
  1145. it.IsDel == 0 &&
  1146. dto.ClientDataIdItem.Contains(it.NewClientDataId) &&
  1147. dto.UserIdItem.Contains(it.usersId)
  1148. )
  1149. .ToListAsync();
  1150. var existsCount = 0;
  1151. foreach (var clientDataId in dto.ClientDataIdItem)
  1152. {
  1153. foreach (var userId1 in dto.UserIdItem)
  1154. {
  1155. Crm_ClientDataAndUser _ClientDataAndUsers2 = _ClientDataAndUsers1.Where(it => it.NewClientDataId == clientDataId &&
  1156. it.usersId == userId1
  1157. ).FirstOrDefault();
  1158. if (_ClientDataAndUsers2 == null)
  1159. {
  1160. _ClientDataAndUsers.Add(new Crm_ClientDataAndUser()
  1161. {
  1162. CreateUserId = dto.UserId,
  1163. NewClientDataId = clientDataId,
  1164. usersId = userId1
  1165. });
  1166. }
  1167. else
  1168. {
  1169. existsCount++;
  1170. }
  1171. }
  1172. }
  1173. if (_ClientDataAndUsers.Count > 0)
  1174. {
  1175. var adds = await _sqlSugar.Insertable(_ClientDataAndUsers).ExecuteCommandAsync();
  1176. if (adds > 0)
  1177. {
  1178. #region 客户资料表操作记录 批量添加
  1179. List<Crm_TableOperationRecord> _TableOperationRecords = new List<Crm_TableOperationRecord>();
  1180. foreach (var item in _ClientDataAndUsers)
  1181. {
  1182. _TableOperationRecords.Add(
  1183. new Crm_TableOperationRecord() {
  1184. TableName = "Crm_TableOperationRecord",
  1185. PortType = dto.PortType,
  1186. OperationItem = OperationEnum.BatchAssignment,
  1187. DataId = item.NewClientDataId,
  1188. CreateUserId = dto.UserId,
  1189. CreateTime = DateTime.Now,
  1190. Remark = "",
  1191. IsDel = 0
  1192. });
  1193. }
  1194. if (_TableOperationRecords.Count > 0)
  1195. {
  1196. await _sqlSugar.Insertable(_TableOperationRecords).ExecuteCommandAsync();
  1197. }
  1198. #endregion
  1199. AcrionResult.Code = 0;
  1200. return AcrionResult;
  1201. }
  1202. }
  1203. else
  1204. {
  1205. if(existsCount > 0)
  1206. AcrionResult.Msg += $" {existsCount}条数据已被指派,无法重复指派!";
  1207. }
  1208. return AcrionResult;
  1209. }
  1210. public List<Sys_Users> GetNewExistClient(int userid)
  1211. {
  1212. 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";
  1213. var userArr = _sqlSugar.SqlQueryable<Sys_Users>(sql).ToList();
  1214. var dic = new Dictionary<int, int[]>()
  1215. {
  1216. { 95, new int []{ 95 , 337 , 302} }
  1217. };
  1218. if (dic.Keys.Contains(userid))
  1219. {
  1220. return userArr.Where(x=> dic[userid].Contains(x.Id)).ToList();
  1221. }
  1222. return userArr;
  1223. }
  1224. /// <summary>
  1225. /// 公司客户名单 缓存
  1226. /// </summary>
  1227. /// <param name="opType">操作类型 1 获取全部 2 单条详情 3 添加 4 修改 5 删除</param>
  1228. /// <param name="data"></param>
  1229. /// <returns></returns>
  1230. public async Task<List<Crm_NewClientData>> NewClientDataRedis(int opType, Crm_NewClientData data = null)
  1231. {
  1232. var datas = new List<Crm_NewClientData>();
  1233. var opTypes = new List<int>() { 1, 2, 3, 4 };
  1234. if (!opTypes.Any(x => x == opType)) return datas;
  1235. var keyName = $"newClientDataStr";
  1236. var newClientJson = await RedisRepository.RedisFactory.CreateRedisRepository().StringGetAsync<string>(keyName);//string 取
  1237. if (!string.IsNullOrEmpty(newClientJson)) datas = JsonConvert.DeserializeObject<List<Crm_NewClientData>>(newClientJson);
  1238. if (opType == 1) //查询全部
  1239. {
  1240. if (datas != null && datas.Count > 0) return datas;
  1241. datas = await _sqlSugar.Queryable<Crm_NewClientData>().Where(x => x.IsDel == 0).ToListAsync();
  1242. foreach (var item in datas) EncryptionProcessor.DecryptProperties(item);
  1243. }
  1244. else if (opType == 2) //单条数据
  1245. {
  1246. if (datas != null && datas.Count > 0) return datas.Where(x => x.Id == data.Id).ToList();
  1247. datas = await _sqlSugar.Queryable<Crm_NewClientData>().Where(x => x.IsDel == 0).ToListAsync();
  1248. foreach (var item in datas) EncryptionProcessor.DecryptProperties(item);
  1249. await RedisRepository.RedisFactory.CreateRedisRepository().StringSetAsync(keyName, JsonConvert.SerializeObject(datas));//string 存
  1250. return datas.Where(x => x.Id == data.Id).ToList();
  1251. }
  1252. else if (opType == 3) //添加
  1253. {
  1254. datas.Add(data);
  1255. }
  1256. else if (opType == 4) //修改
  1257. {
  1258. var updData = datas.Where(x => x.Id != data.Id).FirstOrDefault();
  1259. if (updData != null) datas.Remove(updData);
  1260. datas.Add(data);
  1261. }
  1262. else if (opType == 5) //删除
  1263. {
  1264. var delData = datas.Where(x => x.Id == data.Id).FirstOrDefault();
  1265. if (delData != null) datas.Remove(delData);
  1266. }
  1267. await RedisRepository.RedisFactory.CreateRedisRepository().StringSetAsync(keyName, JsonConvert.SerializeObject(datas));//string 存
  1268. return datas;
  1269. }
  1270. }
  1271. }