VisaPriceRepository.cs 14 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300
  1. 
  2. using AutoMapper;
  3. using NPOI.SS.Formula.Functions;
  4. using OASystem.Domain;
  5. using OASystem.Domain.Dtos.Financial;
  6. using OASystem.Domain.Dtos.Groups;
  7. using OASystem.Domain.Entities.Financial;
  8. using OASystem.Domain.Entities.Groups;
  9. using OASystem.Domain.ViewModels.Financial;
  10. using OASystem.Domain.ViewModels.Groups;
  11. using OASystem.Infrastructure.Repositories.System;
  12. using OASystem.Infrastructure.Tools;
  13. using Org.BouncyCastle.Asn1.Cms;
  14. using System;
  15. using System.Collections.Generic;
  16. using System.Linq;
  17. using System.Security.Cryptography;
  18. using System.Text;
  19. using System.Threading.Tasks;
  20. namespace OASystem.Infrastructure.Repositories.Groups
  21. {
  22. public class VisaPriceRepository : BaseRepository<Grp_VisaInfo, VisaPriceDto>
  23. {
  24. private readonly IMapper _mapper;
  25. public VisaPriceRepository(SqlSugarClient sqlSugar,IMapper mapper)
  26. : base(sqlSugar)
  27. {
  28. _mapper=mapper;
  29. }
  30. /// <summary>
  31. /// 根据diid查询签证费用列表
  32. /// </summary>
  33. /// <param name="dto"></param>
  34. /// <returns></returns>
  35. public async Task<Result> PostVisaByDiId(VisaPriceDto dto)
  36. {
  37. Result result = new Result() { Code = -2, Msg = "未知错误" };
  38. if (dto.PortType==0 ||string.IsNullOrWhiteSpace(dto.PortType.ToString()))
  39. {
  40. return result = new Result() { Code = -1, Msg = "请传入PortType参数,请求端口分类 1 Web 2 Android 3 IOS" };
  41. }
  42. try
  43. {
  44. #region SQL条件拼接
  45. string sqlWhere = string.Empty;
  46. if (!string.IsNullOrWhiteSpace(dto.VisaClient))
  47. {
  48. sqlWhere += string.Format(@" And v.VisaClient like '%{0}%'", dto.VisaClient);
  49. }
  50. sqlWhere += string.Format(@"And v.DIId={0} and v.isdel={1}", dto.DiID, 0);
  51. string UserId = "";
  52. List<Grp_GroupsTaskAssignment> gtaUIdList = _sqlSugar.Queryable<Grp_GroupsTaskAssignment>().Where(a => a.DIId == dto.DiID && a.IsDel == 0 && a.CTId == 80).ToList();
  53. foreach (Grp_GroupsTaskAssignment gta in gtaUIdList)
  54. UserId += gta.UId + ",";
  55. if (!string.IsNullOrWhiteSpace(UserId))
  56. {
  57. UserId = UserId.Substring(0, UserId.Length - 1);
  58. }
  59. else
  60. {
  61. UserId = "0";
  62. }
  63. sqlWhere += string.Format(@" And v.CreateUserId in ({0})", UserId);
  64. if (!string.IsNullOrEmpty(sqlWhere.Trim()))
  65. {
  66. Regex r = new Regex("And");
  67. sqlWhere = r.Replace(sqlWhere, "Where", 1);
  68. }
  69. #endregion
  70. int startIndex = (dto.PageIndex - 1) * dto.PageSize + 1;
  71. int endIndex = startIndex + dto.PageSize - 1;
  72. if (dto.PortType==1)
  73. {
  74. string sql = string.Format(@"select *,(select IsAuditGM from Grp_CreditCardPayment where CTable=80 and CId=v.Id and IsDel=0) IsAuditGM,
  75. (select Name from Sys_SetData where isdel=0 and v.VisaCurrency=Id) VisaCurrencyStr from
  76. Grp_VisaInfo v {0} order by CreateTime desc",sqlWhere);
  77. List<VisaInfoView> infoViews = _sqlSugar.SqlQueryable<VisaInfoView>(sql).ToList();
  78. foreach (VisaInfoView view in infoViews)
  79. {
  80. if (view.IsAuditGM == 0) view.IsAuditGMStr = "未审核";
  81. else if (view.IsAuditGM == 1) view.IsAuditGMStr = "已通过";
  82. else if (view.IsAuditGM == 2) view.IsAuditGMStr = "未通过";
  83. else if (view.IsAuditGM == 3) view.IsAuditGMStr = "自动审核";
  84. }
  85. return result = new Result() { Code = 0, Msg = "查询成功!", Data = infoViews };
  86. }
  87. else if(dto.PortType==2 || dto.PortType==3)
  88. {
  89. string sql = string.Format(@"Select * From (
  90. Select row_number() over (order by v.Id Desc) as RowNumber,v.*,c.IsAuditGM,s.Name as VisaCurrencyStr
  91. From Grp_VisaInfo v
  92. left Join Grp_CreditCardPayment c On v.Id = c.CId and CTable=80 and c.isdel=0
  93. Left Join Sys_SetData s On v.VisaCurrency = s.Id {0}
  94. ) temp Where RowNumber Between {1} and {2}", sqlWhere, startIndex, endIndex);
  95. List<VisaInfoView> infoViews = _sqlSugar.SqlQueryable<VisaInfoView>(sql).ToList();
  96. foreach (VisaInfoView view in infoViews)
  97. {
  98. if (view.IsAuditGM == 0) view.IsAuditGMStr = "未审核";
  99. else if (view.IsAuditGM == 1) view.IsAuditGMStr = "已通过";
  100. else if (view.IsAuditGM == 2) view.IsAuditGMStr = "未通过";
  101. else if (view.IsAuditGM == 3) view.IsAuditGMStr = "自动审核";
  102. }
  103. string CountSql = string.Format(@"Select COUNT(1) as Count From (
  104. Select v.*,c.IsAuditGM,s.Name as VisaCurrencyStr
  105. From Grp_VisaInfo v
  106. left Join Grp_CreditCardPayment c On v.Id = c.CId and CTable=80 and c.isdel=0
  107. Left Join Sys_SetData s On v.VisaCurrency = s.Id {0}
  108. ) temp", sqlWhere);
  109. DataCount dataCount = _sqlSugar.SqlQueryable<DataCount>(CountSql).First();
  110. if (dataCount!=null)
  111. {
  112. int count = dataCount.Count;
  113. float totalPage = (float)count / dto.PageSize;//总页数
  114. if (totalPage == 0) totalPage = 1;
  115. else totalPage = (int)Math.Ceiling((double)totalPage);
  116. ListViewBase<VisaInfoView> rst = new ListViewBase<VisaInfoView>();
  117. rst.DataList = infoViews;
  118. rst.DataCount = count;
  119. rst.CurrPageIndex = dto.PageIndex;
  120. rst.CurrPageSize = dto.PageSize;
  121. return result = new Result() { Code = 0, Msg = "查询成功!", Data = rst };
  122. }
  123. }
  124. }
  125. catch (Exception)
  126. {
  127. return result = new Result() { Code = -2, Msg = "未知错误" };
  128. throw;
  129. }
  130. return result;
  131. }
  132. /// <summary>
  133. /// 根据签证费用Id查询单条数据及c表数据
  134. /// </summary>
  135. /// <param name="dto"></param>
  136. /// <returns></returns>
  137. public async Task<Result> PostVisaById(PostVisaByIdDto dto)
  138. {
  139. Result result = new Result() { Code = -2, Msg = "未知错误" };
  140. try
  141. {
  142. Grp_VisaInfo _VisaInfo = _sqlSugar.Queryable<Grp_VisaInfo>().First(a => a.Id == dto.Id && a.IsDel==0);
  143. Grp_CreditCardPayment _CreditCardPayment = _sqlSugar.Queryable<Grp_CreditCardPayment>().First(a=>a.CId==dto.Id && a.IsDel==0 && a.CTable==80);
  144. var data = new
  145. {
  146. VisaInfo = _VisaInfo,
  147. CreditCardPayment = _CreditCardPayment
  148. };
  149. return result = new Result() { Code = 0, Msg = "暂无数据!", Data = data };
  150. }
  151. catch (Exception ex)
  152. {
  153. return result = new Result() { Code = -2, Msg = "未知错误" };
  154. }
  155. }
  156. /// <summary>
  157. /// 签证费用录入操作(Status:1.新增,2.修改)
  158. /// </summary>
  159. /// <param name="dto"></param>
  160. /// <returns></returns>
  161. public async Task<Result> OpVisaPrice(OpVisaPriceDto dto)
  162. {
  163. Result result = new Result() { Code = -2, Msg = "未知错误" };
  164. BeginTran();
  165. try
  166. {
  167. int id = 0;
  168. Grp_VisaInfo grp_Visa1 = _mapper.Map<Grp_VisaInfo>(dto);
  169. Grp_CreditCardPayment c = _mapper.Map<Grp_CreditCardPayment>(dto);
  170. c.PayMoney = dto.VisaPrice;
  171. c.PaymentCurrency = dto.VisaCurrency;
  172. c.Remark = dto.CRemark;
  173. c.PayPercentage = 100;
  174. c.CTable = 80;
  175. c.CId = dto.Id;
  176. c.IsAuditGM = 0;
  177. if (c.PayDId == 72)
  178. {
  179. c.IsPay = 1;
  180. }
  181. c.RMBPrice = c.PayMoney;
  182. c.DayRate = 1;
  183. Grp_TeamRate _TeamRate = _sqlSugar.Queryable<Grp_TeamRate>().First(a => a.DiId == dto.DiId && a.IsDel == 0 && a.CTable == 80);
  184. List<CurrencyInfo> currencyInfos = new List<CurrencyInfo>();
  185. if (_TeamRate != null)
  186. {
  187. Sys_SetData _SetData = _sqlSugar.Queryable<Sys_SetData>().First(a => a.IsDel == 0 && a.Id == c.PaymentCurrency);
  188. if (_SetData!=null)
  189. {
  190. currencyInfos = CommonFun.GetCurrencyChinaToList(_TeamRate.Remark);
  191. CurrencyInfo CurrencyRate = currencyInfos.FirstOrDefault(a => a.CurrencyCode == _SetData.Name);
  192. if (CurrencyRate!=null)
  193. {
  194. c.RMBPrice = c.PayMoney * Convert.ToDecimal(CurrencyRate.Rate);
  195. c.DayRate = CurrencyRate.Rate;
  196. }
  197. }
  198. }
  199. if (dto.Status==1)//添加
  200. {
  201. Grp_VisaInfo grp_Visa =_sqlSugar.Queryable<Grp_VisaInfo>().First(a=>a.IsDel==0 && a.VisaCurrency==dto.VisaCurrency && a.VisaPrice==dto.VisaPrice && a.VisaClient==dto.VisaClient);
  202. if (grp_Visa!=null)
  203. {
  204. return result = new Result() { Code = -1, Msg = "该笔费用已存在,请勿重复添加!" };
  205. }
  206. else
  207. {
  208. id = await AddAsyncReturnId(grp_Visa1);
  209. if (id!=0)
  210. {
  211. c.CId = id;
  212. int cId = await _sqlSugar.Insertable(c).ExecuteReturnIdentityAsync();
  213. if (cId != 0)
  214. {
  215. result = new Result() { Code = 0, Msg = "添加成功!" };
  216. }
  217. else
  218. {
  219. RollbackTran();
  220. result = new Result() { Code = -1, Msg = "添加失败!" };
  221. }
  222. }
  223. else
  224. {
  225. RollbackTran();
  226. result = new Result() { Code = -1, Msg = "添加失败,请稍后重试!" };
  227. }
  228. }
  229. }
  230. else if (dto.Status==2)//修改
  231. {
  232. bool res = await UpdateAsync(a => a.Id == dto.Id, a => new Grp_VisaInfo
  233. {
  234. VisaClient = grp_Visa1.VisaClient,
  235. VisaPrice = grp_Visa1.VisaPrice,
  236. VisaCurrency = grp_Visa1.VisaCurrency,
  237. IsThird = grp_Visa1.IsThird,
  238. PassengerType = grp_Visa1.PassengerType,
  239. VisaNumber = grp_Visa1.VisaNumber,
  240. VisaFreeNumber = grp_Visa1.VisaFreeNumber,
  241. Remark = dto.Remark,
  242. });
  243. if (res)
  244. {
  245. int CTable = await _sqlSugar.Updateable<Grp_CreditCardPayment>().Where(a => a.CId == grp_Visa1.Id && a.CTable==80).SetColumns(a => new Grp_CreditCardPayment
  246. {
  247. PayDId = dto.PayDId,
  248. PayMoney = c.PayMoney,
  249. PaymentCurrency = c.PaymentCurrency,
  250. Payee = c.Payee,
  251. OrbitalPrivateTransfer = c.OrbitalPrivateTransfer,
  252. DayRate = c.DayRate,
  253. RMBPrice = c.RMBPrice,
  254. ConsumptionPatterns = c.ConsumptionPatterns,
  255. ConsumptionDate = c.ConsumptionDate,
  256. CTDId = c.CTDId,
  257. CompanyBankNo = c.CompanyBankNo,
  258. OtherBankName = c.OtherBankName,
  259. OtherSideNo = c.OtherSideNo,
  260. OtherSideName = c.OtherSideName,
  261. BankNo = c.BankNo,
  262. CardholderName = c.CardholderName,
  263. Remark = c.Remark,
  264. }).ExecuteCommandAsync();
  265. result = new Result() { Code = 0, Msg = "修改成功!" };
  266. }
  267. else
  268. {
  269. RollbackTran();
  270. result = new Result() { Code = -1, Msg = "修改失败,请稍后重试!" };
  271. }
  272. }
  273. CommitTran();
  274. }
  275. catch (Exception ex)
  276. {
  277. result = new Result() { Code = -2, Msg = "未知错误" };
  278. }
  279. return result;
  280. }
  281. }
  282. }