InvitationOfficialActivitiesRepository.cs 20 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376
  1. using AutoMapper;
  2. using MathNet.Numerics.Statistics.Mcmc;
  3. using NPOI.SS.Formula.Functions;
  4. using OASystem.Domain;
  5. using OASystem.Domain.Dtos.Groups;
  6. using OASystem.Domain.Entities.Groups;
  7. using OASystem.Domain.Entities.Resource;
  8. using OASystem.Domain.ViewModels.Groups;
  9. using System;
  10. using System.Collections.Generic;
  11. using System.Linq;
  12. using System.Text;
  13. using System.Threading.Tasks;
  14. namespace OASystem.Infrastructure.Repositories.Groups
  15. {
  16. public class InvitationOfficialActivitiesRepository : BaseRepository<Grp_InvitationOfficialActivities, InvitationOfficialActivitiesListDto>
  17. {
  18. private readonly IMapper _mapper;
  19. public InvitationOfficialActivitiesRepository(SqlSugarClient sqlSugar, IMapper mapper) :
  20. base(sqlSugar)
  21. {
  22. this._mapper = mapper;
  23. }
  24. /// <summary>
  25. /// 根据商邀费用Id查询C表和商邀资料及费用表
  26. /// </summary>
  27. /// <param name="dto"></param>
  28. /// <returns></returns>
  29. public async Task<Result> InvitationOfficialActivitiesById(InvitationOfficialActivitiesByIdDto dto)
  30. {
  31. Result result = new Result() { Code = -2, Msg = "程序错误" };
  32. try
  33. {
  34. Grp_InvitationOfficialActivities grp_Invitation = _sqlSugar.Queryable<Grp_InvitationOfficialActivities>().First(a => a.Id == dto.Id && a.IsDel == 0);
  35. Grp_CreditCardPayment grp_CreditCard = _sqlSugar.Queryable<Grp_CreditCardPayment>().First(a => a.CId == dto.Id && a.IsDel == 0 && a.CTable==81);
  36. Res_InvitationOfficialActivityData res_InvitationOfficialActivityData = new Res_InvitationOfficialActivityData();
  37. if (grp_Invitation!=null)
  38. {
  39. res_InvitationOfficialActivityData = _sqlSugar.Queryable<Res_InvitationOfficialActivityData>().First(a =>a.Country==grp_Invitation.InviterArea && a.UnitName==grp_Invitation.Inviter && a.IsDel == 0);
  40. }
  41. var data = new
  42. {
  43. _Invitation = grp_Invitation,
  44. _InvitationData = res_InvitationOfficialActivityData,
  45. _CreditCard = grp_CreditCard,
  46. };
  47. result = new Result() { Code = 0, Msg = "查询成功!", Data = data };
  48. }
  49. catch (Exception)
  50. {
  51. result = new Result() { Code = -2, Msg = "程序错误" };
  52. throw;
  53. }
  54. return result;
  55. }
  56. /// <summary>
  57. /// 根据团组id查询商邀费用数据
  58. /// </summary>
  59. /// <param name="dto"></param>
  60. /// <returns></returns>
  61. public async Task<Result> InvitationOfficialActivitiesList(InvitationOfficialActivitiesListDto dto)
  62. {
  63. Result result = new Result() { Code = -2, Msg = "未知错误" };
  64. try
  65. {
  66. string sqlWhere = string.Empty;
  67. if (!string.IsNullOrWhiteSpace(dto.Inviter))
  68. {
  69. sqlWhere += string.Format(@" And Inviter like '%{0}%'", dto.Inviter);
  70. }
  71. if (!string.IsNullOrWhiteSpace(dto.StartInviteTime) && !string.IsNullOrWhiteSpace(dto.EndInviteTime))
  72. {
  73. sqlWhere += string.Format(@" And i.InviteTime between '{0}' and '{1}'", dto.StartInviteTime, dto.EndInviteTime);
  74. }
  75. sqlWhere += string.Format(@" And i.DiId={0} And i.IsDel={1}", dto.DiId, 0);
  76. if (!string.IsNullOrEmpty(sqlWhere.Trim()))
  77. {
  78. Regex r = new Regex("And");
  79. sqlWhere = r.Replace(sqlWhere, "Where", 1);
  80. }
  81. string sql = string.Format(@"select Id,DiId,InviterArea,Inviter,InviteTime,InviteCosts,(select name from Sys_SetData where id=i.Currency and IsDel={0})
  82. as Currency,IsGoOfficaiaBussiness,(select IsAuditGM from Grp_CreditCardPayment where CTable=81 and CId=i.Id
  83. and IsDel={0}) as 'isAudit',Attachment from Grp_InvitationOfficialActivities i {1}", 0, sqlWhere);
  84. List<InvitationOfficialActivitiesView> _DecreasePayments = await _sqlSugar.SqlQueryable<InvitationOfficialActivitiesView>(sql).ToListAsync();
  85. if (_DecreasePayments.Count != 0)
  86. {
  87. result = new Result() { Code = 0, Msg = "查询成功!", Data = _DecreasePayments };
  88. }
  89. else
  90. {
  91. result = new Result() { Code = 0, Msg = "暂无数据!", Data = _DecreasePayments };
  92. }
  93. }
  94. catch (Exception ex)
  95. {
  96. result = new Result() { Code = -2, Msg = "未知错误" };
  97. }
  98. return result;
  99. }
  100. /// <summary>
  101. /// 商邀费用操作(Status:1.新增,2.修改)
  102. /// </summary>
  103. /// <param name="dto"></param>
  104. /// <returns></returns>
  105. public async Task<Result> OpInvitationOfficialActivities(OpInvitationOfficialActivitiesDto dto)
  106. {
  107. Result result = new Result() { Code = -2, Msg = "程序错误!" };
  108. BeginTran();
  109. try
  110. {
  111. int id = 0;
  112. Grp_InvitationOfficialActivities grp_Invitation = _mapper.Map<Grp_InvitationOfficialActivities>(dto);
  113. if (dto.Status == 1)//添加
  114. {
  115. string selectSql = string.Format(@"select * from Grp_InvitationOfficialActivities where InviterArea='{0}' and Inviter='{1}' and DiId={2} and IsDel={3}"
  116. , dto.InviterArea, dto.Inviter, dto.DiId, 0);
  117. var _InvitationOfficialActivities = await _sqlSugar.SqlQueryable<Grp_InvitationOfficialActivities>(selectSql).FirstAsync();//查询是否存在
  118. if (_InvitationOfficialActivities != null)
  119. {
  120. return result = new Result() { Code = -1, Msg = "该数据已存在,请勿重复添加!" };
  121. }
  122. else
  123. {
  124. id = await AddAsyncReturnId(grp_Invitation);
  125. if (id != 0)//修改或添加商邀资料
  126. {
  127. Res_InvitationOfficialActivityData res_InvitationData = _mapper.Map<Res_InvitationOfficialActivityData>(dto);
  128. res_InvitationData.Country = dto.InviterArea;
  129. res_InvitationData.UnitName = dto.Inviter;
  130. Res_InvitationOfficialActivityData ifNullUp = await _sqlSugar.Queryable<Res_InvitationOfficialActivityData>().FirstAsync
  131. (a => a.Country == res_InvitationData.Country && a.City == res_InvitationData.City && a.UnitName == res_InvitationData.UnitName);
  132. if (ifNullUp == null)///添加或修改商邀资料
  133. {
  134. res_InvitationData.Remark = dto.OtherInformation;
  135. Grp_DelegationInfo delegationInfo = await _sqlSugar.Queryable<Grp_DelegationInfo>().FirstAsync(a => a.Id == dto.DiId && a.IsDel == 0);
  136. res_InvitationData.Delegation = delegationInfo.TeamName;
  137. int DataID = await _sqlSugar.Insertable(res_InvitationData).ExecuteReturnIdentityAsync();
  138. if (DataID != 0)
  139. {
  140. result = new Result() { Code = 0, Msg = "添加成功!" };
  141. }
  142. else
  143. {
  144. RollbackTran();
  145. result = new Result() { Code = -1, Msg = "添加失败!" };
  146. }
  147. }
  148. else
  149. {
  150. int CTable = await _sqlSugar.Updateable<Res_InvitationOfficialActivityData>().Where(a => a.Id == ifNullUp.Id).SetColumns(a => new Res_InvitationOfficialActivityData
  151. {
  152. Contact=dto.Contact,
  153. Tel=dto.Tel,
  154. Email=dto.Email,
  155. Fax=dto.Fax,
  156. Address=dto.Address,
  157. Remark=dto.OtherInformation,
  158. }).ExecuteCommandAsync();
  159. }
  160. Grp_CreditCardPayment C = new Grp_CreditCardPayment();
  161. C.PayDId = dto.PayDId;
  162. C.ConsumptionPatterns = "";
  163. C.ConsumptionDate = "";
  164. C.CTDId = 0;
  165. C.BankNo = "";
  166. C.CardholderName = "";
  167. C.PayMoney = dto.InviteCosts + dto.SendCost;
  168. C.PaymentCurrency = dto.Currency;
  169. //当天汇率
  170. //if (!string.IsNullOrEmpty(hfRate.Value))
  171. // C.DayRate = hfRate.Value;
  172. //else
  173. //C.DayRate = "";
  174. C.CompanyBankNo = "";
  175. C.OtherBankName = "";
  176. C.OtherSideNo = "";
  177. C.OtherSideName = "";
  178. C.Remark = "";
  179. C.CreateUserId = dto.CreateUserId;
  180. C.MFOperator = "";
  181. C.MFOperatorDate = "";
  182. C.IsAuditDM = 0;
  183. C.AuditDMOperate = 0;
  184. C.AuditDMDate = "";
  185. C.IsAuditMF = 0;
  186. C.AuditMFOperate = 0;
  187. C.AuditMFDate = "";
  188. C.IsAuditGM = 0;
  189. C.AuditGMOperate = 21;
  190. C.AuditGMDate = "";
  191. C.IsPay = 0;
  192. C.DIId = dto.DiId;
  193. C.CId = id;
  194. C.CTable = 81;
  195. C.PayPercentage = 0;
  196. C.PayThenMoney = 0;
  197. C.PayPercentageOld = 0;
  198. C.PayThenMoneyOld = 0;
  199. C.UpdateDate = "";
  200. C.Payee = dto.Payee;
  201. C.OrbitalPrivateTransfer = dto.OrbitalPrivateTransfer;
  202. C.ExceedBudget = 0;
  203. //C.RMBPrice = 0.00f;
  204. //设置该团组的汇率
  205. Grp_TeamRate _TeamRate = _sqlSugar.Queryable<Grp_TeamRate>().First(a => a.DiId == dto.DiId && a.IsDel == 0 && a.CTable == 81);
  206. if (_TeamRate != null)
  207. {
  208. if (dto.Currency == 49)
  209. {
  210. C.DayRate = _TeamRate.RateU;
  211. C.RMBPrice = C.PayMoney * Convert.ToDecimal(_TeamRate.RateU);
  212. //ccp.PayMoney = ccp.PayMoney * float.Parse(tr.RateU);
  213. }
  214. else if (dto.Currency == 51)
  215. {
  216. C.DayRate = _TeamRate.RateE;
  217. C.RMBPrice = C.PayMoney * Convert.ToDecimal(_TeamRate.RateE);
  218. //ccp.PayMoney = ccp.PayMoney * float.Parse(tr.RateE);
  219. }
  220. else
  221. {
  222. C.DayRate = 1M;
  223. C.RMBPrice = C.PayMoney;
  224. }
  225. }
  226. else
  227. {
  228. C.DayRate = 1M;
  229. C.RMBPrice = C.PayMoney;
  230. }
  231. int cId = await _sqlSugar.Insertable(C).ExecuteReturnIdentityAsync();
  232. if (cId != 0)
  233. {
  234. result = new Result() { Code = 0, Msg = "添加成功!" };
  235. }
  236. else
  237. {
  238. RollbackTran();
  239. result = new Result() { Code = -1, Msg = "添加失败!" };
  240. }
  241. }
  242. }
  243. }
  244. else//修改
  245. {
  246. bool res = await UpdateAsync(a => a.Id == grp_Invitation.Id, a => new Grp_InvitationOfficialActivities
  247. {
  248. DiId = dto.DiId,
  249. InviterArea = dto.InviterArea,
  250. Inviter = dto.Inviter,
  251. InviteTime = dto.InviteTime,
  252. Attachment = dto.Attachment,
  253. InviteCosts = dto.InviteCosts,
  254. Currency = dto.Currency,
  255. SendCost = dto.SendCost,
  256. IsGoOfficaiaBussiness = dto.IsGoOfficaiaBussiness,
  257. Remark = dto.Remark,
  258. });
  259. if (res)
  260. {
  261. Res_InvitationOfficialActivityData res_InvitationData = _mapper.Map<Res_InvitationOfficialActivityData>(dto);
  262. res_InvitationData.Country = dto.InviterArea;
  263. res_InvitationData.UnitName = dto.Inviter;
  264. Res_InvitationOfficialActivityData ifNullUp = await _sqlSugar.Queryable<Res_InvitationOfficialActivityData>().FirstAsync
  265. (a => a.Country == res_InvitationData.Country && a.City == res_InvitationData.City && a.UnitName == res_InvitationData.UnitName);
  266. if (ifNullUp == null)///添加或修改商邀资料
  267. {
  268. res_InvitationData.Remark =dto.OtherInformation;
  269. Grp_DelegationInfo delegationInfo = await _sqlSugar.Queryable<Grp_DelegationInfo>().FirstAsync(a => a.Id == dto.DiId && a.IsDel == 0);
  270. res_InvitationData.Delegation = delegationInfo.TeamName;
  271. int DataID = await _sqlSugar.Insertable(res_InvitationData).ExecuteReturnIdentityAsync();
  272. if (DataID != 0)
  273. {
  274. result = new Result() { Code = 0, Msg = "添加成功!" };
  275. }
  276. else
  277. {
  278. RollbackTran();
  279. result = new Result() { Code = -1, Msg = "添加失败!" };
  280. }
  281. } else
  282. {
  283. int CTable = await _sqlSugar.Updateable<Res_InvitationOfficialActivityData>().Where(a => a.Id == ifNullUp.Id).SetColumns(a => new Res_InvitationOfficialActivityData
  284. {
  285. Contact=dto.Contact,
  286. Tel=dto.Tel,
  287. Email=dto.Email,
  288. Fax=dto.Fax,
  289. Address=dto.Address,
  290. Remark=dto.OtherInformation,
  291. }).ExecuteCommandAsync();
  292. }
  293. Grp_CreditCardPayment grp_CreditCardPayment = _sqlSugar.Queryable<Grp_CreditCardPayment>().First(a => a.CId == grp_Invitation.Id && a.CTable==81 && a.IsDel == 0);
  294. if (grp_CreditCardPayment != null)
  295. {
  296. grp_CreditCardPayment.PayMoney = dto.InviteCosts + dto.SendCost;
  297. Grp_TeamRate _TeamRate = _sqlSugar.Queryable<Grp_TeamRate>().First(a => a.DiId == grp_Invitation.DiId && a.IsDel == 0 && a.IsDel==0 && a.CTable == 81);
  298. if (_TeamRate != null)
  299. {
  300. if (grp_Invitation.Currency == 49)
  301. {
  302. grp_CreditCardPayment.DayRate = _TeamRate.RateU;
  303. grp_CreditCardPayment.RMBPrice = grp_CreditCardPayment.PayMoney * Convert.ToDecimal(_TeamRate.RateU);
  304. //ccp.PayMoney = ccp.PayMoney * float.Parse(tr.RateU);
  305. }
  306. else if (grp_Invitation.Currency == 51)
  307. {
  308. grp_CreditCardPayment.DayRate = _TeamRate.RateE;
  309. grp_CreditCardPayment.RMBPrice = grp_CreditCardPayment.PayMoney * Convert.ToDecimal(_TeamRate.RateE);
  310. //ccp.PayMoney = ccp.PayMoney * float.Parse(tr.RateE);
  311. }
  312. else
  313. {
  314. grp_CreditCardPayment.DayRate = 1M;
  315. grp_CreditCardPayment.RMBPrice = grp_CreditCardPayment.PayMoney;
  316. }
  317. }
  318. else
  319. {
  320. grp_CreditCardPayment.DayRate = 1M;
  321. grp_CreditCardPayment.RMBPrice = grp_CreditCardPayment.PayMoney;
  322. }
  323. int CTable = await _sqlSugar.Updateable<Grp_CreditCardPayment>().Where(a => a.Id == grp_CreditCardPayment.Id).SetColumns(a => new Grp_CreditCardPayment
  324. {
  325. PayDId = dto.PayDId,
  326. PayMoney = grp_CreditCardPayment.PayMoney,
  327. PaymentCurrency = grp_Invitation.Currency,
  328. Payee = dto.Payee,
  329. OrbitalPrivateTransfer = dto.OrbitalPrivateTransfer,
  330. DayRate = grp_CreditCardPayment.DayRate,
  331. RMBPrice = grp_CreditCardPayment.RMBPrice,
  332. }).ExecuteCommandAsync();
  333. if (CTable != 0)
  334. {
  335. result = new Result() { Code = 0, Msg = "修改成功!" };
  336. }
  337. else
  338. {
  339. result = new Result() { Code = -1, Msg = "修改失败!" };
  340. RollbackTran();
  341. }
  342. }
  343. else
  344. {
  345. RollbackTran();
  346. result = new Result() { Code = -1, Msg = "修改失败!" };
  347. }
  348. }
  349. else
  350. {
  351. RollbackTran();
  352. result = new Result() { Code = -1, Msg = "修改失败!" };
  353. }
  354. }
  355. CommitTran();
  356. }
  357. catch (Exception ex)
  358. {
  359. result = new Result() { Code = -2, Msg = "程序错误!" };
  360. throw;
  361. }
  362. return result;
  363. }
  364. }
  365. }