NewClientDataRepository.cs 36 KB

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