OfficialActivitiesRepository.cs 23 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547
  1. using AutoMapper;
  2. using MathNet.Numerics.Distributions;
  3. using MathNet.Numerics.Statistics.Mcmc;
  4. using NetTaste;
  5. using Newtonsoft.Json;
  6. using NPOI.SS.Formula.Functions;
  7. using OASystem.Domain;
  8. using OASystem.Domain.AesEncryption;
  9. using OASystem.Domain.Dtos.Resource;
  10. using OASystem.Domain.Entities.Groups;
  11. using OASystem.Domain.Entities.Resource;
  12. using OASystem.Domain.ViewModels.Resource;
  13. using OASystem.Infrastructure.Tools;
  14. using System;
  15. using System.Collections.Generic;
  16. using System.Linq;
  17. using System.Reflection;
  18. using System.Text;
  19. using System.Threading.Tasks;
  20. namespace OASystem.Infrastructure.Repositories.Resource
  21. {
  22. public class OfficialActivitiesRepository : BaseRepository<Res_OfficialActivities, OfficialActivitiesView>
  23. {
  24. private readonly IMapper _mapper;
  25. public OfficialActivitiesRepository(SqlSugarClient sqlSugar, IMapper mapper) : base(sqlSugar)
  26. {
  27. _mapper = mapper;
  28. }
  29. /// <summary>
  30. /// 根据Diid查询公务出访数据List
  31. /// </summary>
  32. /// <param name="dto"></param>
  33. /// <returns></returns>
  34. public async Task<JsonView> QueryOfficialActivitiesByDiId(OfficialActivitiesByDiIdDto dto)
  35. {
  36. JsonView result = new JsonView() { Code = StatusCodes.Status200OK, Msg = "暂无数据" };
  37. string sqlWhere = string.Empty;
  38. sqlWhere += string.Format(@"AND o.Isdel={0} AND o.DiId={1} ", 0, dto.DiId);
  39. if (!string.IsNullOrEmpty(sqlWhere.Trim()))
  40. {
  41. Regex r = new Regex("AND");
  42. sqlWhere = r.Replace(sqlWhere, "WHERE", 1);
  43. }
  44. string sql = string.Format(@"
  45. SELECT
  46. *,
  47. u.CnName AS CreateUserName,
  48. sd.Name AS OfficialFormName
  49. FROM
  50. Res_OfficialActivities o
  51. LEFT JOIN Sys_SetData sd ON o.OfficialForm = sd.Id
  52. LEFT JOIN Sys_Users u ON o.CreateUserId = u.Id
  53. {0}
  54. ORDER BY
  55. o.CreateTime desc
  56. ", sqlWhere);
  57. var OfficialActivities = await _sqlSugar.SqlQueryable<OfficialActivitiesView>(sql).ToListAsync();
  58. if (OfficialActivities.Count != 0)
  59. {
  60. if (dto.PageSize == 0 && dto.PageIndex == 0)
  61. {
  62. OfficialActivities.ForEach(x =>
  63. {
  64. //2024年4月1日 11:55:44 -蒋金辰 -日期处理
  65. bool b_dt = DateTime.TryParse(x.Date, out DateTime dt);
  66. if (b_dt)
  67. {
  68. if (!string.IsNullOrEmpty(x.Time)) x.Date = dt.ToString("yyyy-MM-dd") + " " + x.Time;
  69. else x.Date = dt.ToString("yyyy-MM-dd HH:mm:ss");
  70. }
  71. });
  72. result = new JsonView() { Code = 200, Msg = "查询成功!", Data = OfficialActivities };
  73. }
  74. else
  75. {
  76. int count = OfficialActivities.Count;
  77. float totalPage = (float)count / dto.PageSize;//总页数
  78. if (totalPage == 0) totalPage = 1;
  79. else totalPage = (int)Math.Ceiling((double)totalPage);
  80. List<OfficialActivitiesView> _OfficialActivities = new List<OfficialActivitiesView>();
  81. for (int i = 0; i < dto.PageSize; i++)
  82. {
  83. var RowIndex = i + (dto.PageIndex - 1) * dto.PageSize;
  84. if (RowIndex < OfficialActivities.Count)
  85. {
  86. //2024年4月1日 11:55:44 -蒋金辰 -日期处理
  87. DateTime dt;
  88. bool b_dt = DateTime.TryParse(OfficialActivities[RowIndex].Date, out dt);
  89. if (b_dt)
  90. {
  91. OfficialActivities[RowIndex].Date = dt.ToString("yyyy-MM-dd HH:mm");
  92. }
  93. _OfficialActivities.Add(OfficialActivities[RowIndex]);
  94. }
  95. else
  96. {
  97. break;
  98. }
  99. }
  100. ListViewBase<OfficialActivitiesView> rst = new ListViewBase<OfficialActivitiesView>();
  101. rst.DataList = _OfficialActivities;
  102. rst.DataCount = count;
  103. rst.CurrPageIndex = dto.PageIndex;
  104. rst.CurrPageSize = dto.PageSize;
  105. result = new JsonView() { Code = 200, Msg = "查询成功!", Data = rst };
  106. }
  107. }
  108. else
  109. {
  110. result = new JsonView() { Code = StatusCodes.Status200OK, Msg = "暂无数据!" };
  111. if (dto.PageSize == 0 && dto.PageIndex == 0) { result.Data = OfficialActivities; }
  112. else {
  113. ListViewBase<OfficialActivitiesView> rst = new ListViewBase<OfficialActivitiesView>();
  114. rst.DataList = OfficialActivities;
  115. rst.DataCount = 0;
  116. rst.CurrPageIndex = dto.PageIndex;
  117. rst.CurrPageSize = dto.PageSize;
  118. result.Data = rst;
  119. }
  120. }
  121. return result;
  122. }
  123. /// <summary>
  124. /// 根据公务出访Id查询单个数据
  125. /// </summary>
  126. /// <param name="dto"></param>
  127. /// <returns></returns>
  128. /// <exception cref="NotImplementedException"></exception>
  129. public async Task<Result> QueryOfficialActivitiesById(OfficialActivitiesDiIdDto dto)
  130. {
  131. Result result = new Result() { Code = -2, Msg = "未知错误" };
  132. try
  133. {
  134. string sqlWhere = string.Empty;
  135. sqlWhere += string.Format(@"AND oa.Isdel={0} AND oa.DiId={1} AND oa.Id={2}", 0, dto.DiId, dto.Id);
  136. if (!string.IsNullOrEmpty(sqlWhere.Trim()))
  137. {
  138. Regex r = new Regex("AND");
  139. sqlWhere = r.Replace(sqlWhere, "WHERE", 1);
  140. }
  141. string sql = string.Format(@"
  142. SELECT
  143. oa.*,
  144. u.CnName AS CreateUserName,
  145. sd.Name AS OfficialFormName
  146. FROM
  147. Res_OfficialActivities oa
  148. LEFT JOIN Sys_Users u ON oa.CreateUserId = u.Id
  149. LEFT JOIN Sys_SetData sd ON oa.OfficialForm = sd.Id
  150. {0}", sqlWhere);
  151. var oa = await _sqlSugar.SqlQueryable<OfficialActivitiesView>(sql).FirstAsync();
  152. var array1 = _sqlSugar.Queryable<Grp_OfficialDutyLinkTranslator>()
  153. .Where(x => x.IsDel == 0 && x.OfficialDutyId == dto.Id)
  154. .Select(x => x.TranslatorId)
  155. .ToArray();
  156. if (array1.Any())
  157. {
  158. oa.TranslatorIdItem = array1;
  159. int translatorId = array1[0];
  160. var translatorInfo = await _sqlSugar.Queryable<Res_TranslatorLibrary>()
  161. .Where(x => x.IsDel == 0 && x.Id == translatorId)
  162. .FirstAsync();
  163. EncryptionProcessor.DecryptProperties(translatorInfo);
  164. oa.TranslatorInfo = _mapper.Map<TranslatorView>(translatorInfo);
  165. if(oa.TranslatorInfo != null)
  166. oa.TranslatorInfo.CurrencyName = _sqlSugar.Queryable<Sys_SetData>().Where(x => x.Id == oa.TranslatorInfo.Currency).First()?.Name ?? "";
  167. }
  168. result = new Result() { Code = 0, Msg = "查询成功!", Data = oa };
  169. }
  170. catch (Exception ex)
  171. {
  172. result = new Result() { Code = -2, Msg = "未知错误" };
  173. }
  174. return result;
  175. }
  176. public async Task<Result> OpOfficialActivities(OpOfficialActivitiesDto dto)
  177. {
  178. var result = new Result() { Code = -2, Msg = "未知错误" };
  179. var language = dto?.TranslatorInfo?.Language;
  180. #region 特殊字符转码 037 - 4.28 15:17
  181. if (!string.IsNullOrEmpty(dto.Contact))
  182. {
  183. byte[] utf8Bytes = Encoding.UTF8.GetBytes(dto.Contact);
  184. byte[] utf16Bytes = Encoding.Convert(Encoding.UTF8, Encoding.Unicode, utf8Bytes);
  185. dto.Contact = Encoding.Unicode.GetString(utf16Bytes);
  186. }
  187. #endregion
  188. _sqlSugar.BeginTran();
  189. //添加到资料库
  190. var res_InvitationData = new Res_InvitationOfficialActivityData
  191. {
  192. Country = dto.Country,
  193. City = dto.Area,
  194. UnitName = dto.Client,
  195. Delegation = dto.DiId.ToString(),
  196. Address = dto.Address,
  197. CreateUserId = dto.CreateUserId,
  198. Contact = dto.Contact,
  199. Job = dto.Job,
  200. Tel = dto.Tel,
  201. Field = dto.Field
  202. };
  203. EncryptionProcessor.EncryptProperties(res_InvitationData);
  204. var isInserTranslator = true;
  205. /*
  206. * 2025-04-28
  207. * 翻译人员ID = 0 && 相关文本值 == “-” 不执行添加
  208. *
  209. */
  210. // 获取所有string类型的公共实例属性 排除币种名称
  211. var pTypes = new List<string>
  212. {
  213. "CurrencyName",
  214. };
  215. var stringProperties = dto.TranslatorInfo.GetType()
  216. .GetProperties(BindingFlags.Public | BindingFlags.Instance)
  217. .Where(p => p.PropertyType == typeof(string) && !pTypes.Contains(p.Name));
  218. // 检查是否有任何属性的值为 -
  219. int valCount = 0;
  220. foreach (var property in stringProperties)
  221. {
  222. var value = (string)property.GetValue(dto.TranslatorInfo) ?? "";
  223. if (value.Trim().Equals("-")) { valCount++; }
  224. }
  225. if (stringProperties.Count() == valCount) isInserTranslator = false;
  226. var transInfo = new Res_TranslatorLibrary();
  227. if (isInserTranslator)
  228. {
  229. //翻译人员资料
  230. transInfo = _mapper.Map<Res_TranslatorLibrary>(dto.TranslatorInfo);
  231. transInfo.LastUpdateUserId = dto.CreateUserId;
  232. transInfo.LastUpdateTime = DateTime.Now;
  233. transInfo.CreateUserId = dto.CreateUserId;
  234. if (dto.TranslatorIdItem.Any()) transInfo.Id = dto.TranslatorIdItem[0];
  235. EncryptionProcessor.EncryptProperties(transInfo);
  236. isInserTranslator = true;
  237. }
  238. int DataID = 0;
  239. if (dto.Status == 1)//添加
  240. {
  241. //添加资料
  242. DataID = await _sqlSugar.Insertable(res_InvitationData).ExecuteReturnIdentityAsync();
  243. var _InvitationOfficialActivityData = _mapper.Map<Res_OfficialActivities>(dto);
  244. _InvitationOfficialActivityData.DataId = DataID;
  245. _InvitationOfficialActivityData.Language = language;
  246. int id = await _sqlSugar.Insertable(_InvitationOfficialActivityData).ExecuteReturnIdentityAsync();
  247. if (id == 0)
  248. {
  249. _sqlSugar.RollbackTran();
  250. result = new Result() { Code = -1, Msg = "添加失败!" };
  251. }
  252. else
  253. {
  254. var translatorId = transInfo.Id;
  255. if (isInserTranslator)
  256. {
  257. if (translatorId > 0) // 翻译人员资料更新
  258. {
  259. var tiStatus = await _sqlSugar.Updateable<Res_TranslatorLibrary>(transInfo)
  260. .UpdateColumns(x => new
  261. {
  262. x.Area,
  263. x.Name,
  264. x.Sex,
  265. x.Tel,
  266. x.Email,
  267. x.WechatNo,
  268. x.OtherSocialAccounts,
  269. x.Language,
  270. x.Price,
  271. x.Currency,
  272. })
  273. .ExecuteCommandAsync();
  274. if (tiStatus < 1)
  275. {
  276. _sqlSugar.RollbackTran();
  277. return new Result() { Code = -1, Msg = "翻译人员资料更新失败!", Data = new { Id = id } };
  278. }
  279. }
  280. else //添加翻译人员资料
  281. {
  282. translatorId = await _sqlSugar.Insertable(transInfo).ExecuteReturnIdentityAsync();
  283. if (translatorId == 0)
  284. {
  285. _sqlSugar.RollbackTran();
  286. return new Result() { Code = -1, Msg = "翻译人员资料添加失败!", Data = new { Id = id } };
  287. }
  288. }
  289. #region 新增(公务信息关联翻译人员) 关联信息
  290. var linkStatus = await _sqlSugar
  291. .Insertable(new Grp_OfficialDutyLinkTranslator()
  292. {
  293. TranslatorId = translatorId,
  294. OfficialDutyId = id,
  295. CreateUserId = dto.CreateUserId,
  296. Remark = $"公务出访客户资料-->添加"
  297. }).ExecuteCommandAsync();
  298. if (linkStatus < 1)
  299. {
  300. _sqlSugar.RollbackTran();
  301. return new Result() { Code = -1, Msg = "公务出访关联翻译人员资料添加失败!", Data = new { Id = id } };
  302. }
  303. #endregion
  304. }
  305. _sqlSugar.CommitTran();
  306. result = new Result() { Code = 0, Msg = "添加成功!", Data = new { Id = id } };
  307. }
  308. }
  309. else if (dto.Status == 2)//修改
  310. {
  311. var officialActivities = _sqlSugar.Queryable<Res_OfficialActivities>().First(x => x.Id == dto.Id);
  312. if (officialActivities.DataId > 0)
  313. {
  314. res_InvitationData.Id = officialActivities.DataId;
  315. }
  316. else
  317. {
  318. var ifNullUp = await _sqlSugar.Queryable<Res_InvitationOfficialActivityData>()
  319. .FirstAsync(a => a.Country == res_InvitationData.Country
  320. && a.City == res_InvitationData.City
  321. && a.UnitName == res_InvitationData.UnitName
  322. && a.IsDel == 0
  323. && a.Address == res_InvitationData.Address);
  324. if (ifNullUp != null)
  325. {
  326. res_InvitationData.Id = ifNullUp.Id;
  327. }
  328. }
  329. if (res_InvitationData.Id == 0 )
  330. {
  331. DataID = await _sqlSugar.Insertable(res_InvitationData).ExecuteReturnIdentityAsync();
  332. }
  333. else
  334. {
  335. DataID = res_InvitationData.Id;
  336. //商邀资料
  337. await _sqlSugar.Updateable(res_InvitationData)
  338. .UpdateColumns(x => new
  339. {
  340. x.Country,
  341. x.City,
  342. x.UnitName,
  343. x.Delegation,
  344. x.Address,
  345. x.CreateUserId,
  346. x.Contact,
  347. x.Job,
  348. x.Tel,
  349. x.Field,
  350. })
  351. .ExecuteCommandAsync();
  352. }
  353. //公务出访
  354. bool res = await UpdateAsync(a => a.Id == dto.Id, a => new Res_OfficialActivities
  355. {
  356. DataSource = dto.DataSource,
  357. Country = dto.Country,
  358. Area = dto.Area,
  359. Type = dto.Type,
  360. Client = dto.Client,
  361. Date = dto.Date,
  362. Time = dto.Time,
  363. Address = dto.Address,
  364. Contact = dto.Contact,
  365. Job = dto.Job,
  366. Tel = dto.Tel,
  367. OfficialForm = dto.OfficialForm,
  368. Field = dto.Field,
  369. ReqSample = dto.ReqSample,
  370. Setting = dto.Setting,
  371. Dresscode = dto.Dresscode,
  372. Attendees = dto.Attendees,
  373. IsNeedTrans = dto.IsNeedTrans,
  374. //Translators = dto.Translators,
  375. Language = language,
  376. Trip = dto.Trip,
  377. CreateUserId = dto.CreateUserId,
  378. Remark = dto.Remark,
  379. IsPay = dto.IsPay,
  380. IsSubmitApproval = dto.IsSubmitApproval,
  381. EmailOrWeChat = dto.EmailOrWeChat,
  382. Website = dto.Website,
  383. Nature = dto.Nature,
  384. DataId = DataID,
  385. });
  386. if (res)
  387. {
  388. if (isInserTranslator)
  389. {
  390. #region 更新(公务信息关联翻译人员) 关联信息
  391. if (transInfo.Id > 0) //资料更新
  392. {
  393. var tiStatus = await _sqlSugar.Updateable<Res_TranslatorLibrary>(transInfo)
  394. .UpdateColumns(x => new
  395. {
  396. x.Area,
  397. x.Name,
  398. x.Sex,
  399. x.Tel,
  400. x.Email,
  401. x.WechatNo,
  402. x.OtherSocialAccounts,
  403. x.Language,
  404. x.Price,
  405. x.Currency,
  406. })
  407. .ExecuteCommandAsync();
  408. if (tiStatus < 1)
  409. {
  410. _sqlSugar.RollbackTran();
  411. return new Result() { Code = -1, Msg = "翻译人员资料更新失败!", Data = new { Id = dto.Id } };
  412. }
  413. var dutyLink_select = await _sqlSugar.Queryable<Grp_OfficialDutyLinkTranslator>()
  414. .Where(x => x.IsDel == 0 && x.OfficialDutyId == dto.Id && x.TranslatorId == transInfo.Id)
  415. .FirstAsync();
  416. if (dutyLink_select == null)
  417. {
  418. var odltStatus = await _sqlSugar.Insertable(new Grp_OfficialDutyLinkTranslator()
  419. {
  420. TranslatorId = transInfo.Id,
  421. OfficialDutyId = dto.Id,
  422. CreateUserId = dto.CreateUserId,
  423. Remark = $"公务出访客户资料-->添加"
  424. }).ExecuteCommandAsync();
  425. if (odltStatus < 1)
  426. {
  427. _sqlSugar.RollbackTran();
  428. result = new Result() { Code = -1, Msg = "公务出访关联翻译人员资料添加失败!" };
  429. }
  430. }
  431. }
  432. else // 添加
  433. {
  434. transInfo.Id = await _sqlSugar.Insertable(transInfo).ExecuteReturnIdentityAsync();
  435. if (transInfo.Id == 0)
  436. {
  437. _sqlSugar.RollbackTran();
  438. return new Result() { Code = -1, Msg = "翻译人员资料添加失败!", Data = new { Id = dto.Id } };
  439. }
  440. var odltStatus = await _sqlSugar.Insertable(new Grp_OfficialDutyLinkTranslator()
  441. {
  442. TranslatorId = transInfo.Id,
  443. OfficialDutyId = dto.Id,
  444. CreateUserId = dto.CreateUserId,
  445. Remark = $"公务出访客户资料-->添加"
  446. }).ExecuteCommandAsync();
  447. if (odltStatus < 1)
  448. {
  449. _sqlSugar.RollbackTran();
  450. result = new Result() { Code = -1, Msg = "公务出访关联翻译人员资料添加失败!" };
  451. }
  452. }
  453. #endregion
  454. }
  455. _sqlSugar.CommitTran();
  456. result = new Result() { Code = 0, Msg = "修改成功!", Data = new { Id = dto.Id } };
  457. }
  458. else
  459. {
  460. _sqlSugar.RollbackTran();
  461. result = new Result() { Code = -1, Msg = "公务出访修改失败!" };
  462. }
  463. }
  464. else
  465. {
  466. _sqlSugar.RollbackTran();
  467. result = new Result() { Code = -1, Msg = "请传入Status参数,1添加 2修改!" };
  468. }
  469. return result;
  470. }
  471. public async Task<Result> PostReqReqSampleTips(string country, string Area,string client)
  472. {
  473. if (string.IsNullOrEmpty(country)) return new Result() { Code = -1, Msg = "国家为空!" };
  474. string sqlWhere = string.Empty;
  475. if (!string.IsNullOrEmpty(Area)) sqlWhere = string.Format(@$" And oa.Area Like '%{Area}%'");
  476. if (!string.IsNullOrEmpty(client)) sqlWhere = string.Format(@$" And oa.Client Like '%{client}%'");
  477. string sql = string.Format(@$"Select di.TeamName,oa.Id,oa.Country,oa.Area,oa.Client,oa.ReqSample
  478. From Res_OfficialActivities oa With(NoLock)
  479. Left Join Grp_DelegationInfo di On oa.DiId = di.Id
  480. Where oa.IsDel = 0 And oa.Country='{country}' {sqlWhere}");
  481. //ReqReqSampleTipsView
  482. var _views = await _sqlSugar.SqlQueryable<ReqReqSampleTipsView>(sql).ToListAsync();
  483. if (_views.Count > 0 )
  484. {
  485. return new Result() { Code = 0, Msg = "操作成功!", Data = _views };
  486. }
  487. return new Result() { Code = -1, Msg = "暂无相关数据!" };
  488. }
  489. }
  490. }