DelegationVisaRepository.cs 9.2 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223
  1. using Aspose.Words.Lists;
  2. using OASystem.Domain.Dtos.Groups;
  3. using OASystem.Domain.Entities.Groups;
  4. using OASystem.Domain.ViewModels.Groups;
  5. using OASystem.Domain.ViewModels.Resource;
  6. using SqlSugar;
  7. using System;
  8. using System.Collections.Generic;
  9. using System.Linq;
  10. using System.Text;
  11. using System.Threading.Tasks;
  12. namespace OASystem.Infrastructure.Repositories.Groups
  13. {
  14. public class DelegationVisaRepository : BaseRepository<Grp_VisaProgress, Grp_DelegationVisaView>
  15. {
  16. public DelegationVisaRepository(SqlSugarClient sqlSugar) : base(sqlSugar)
  17. {
  18. }
  19. public DelegationVisaViewList GetDelegationList(IOS_VisaDto _dto)
  20. {
  21. string sqlWhere = string.Format(" Where IsDel=0 ");
  22. int currPIndex = (((_dto.PageIndex > 0) ? (_dto.PageIndex - 1) : 0) * _dto.PageSize) + 1;
  23. int currPSize = (((_dto.PageIndex > 0) ? (_dto.PageIndex - 1) : 0) + 1) * _dto.PageSize;
  24. ChangeDataBase(DBEnum.OA2014DB);
  25. string sql = string.Format(@" Select * From(Select ROW_NUMBER() Over(order By Id desc) as RowNumber, Id as DiId,
  26. TeamName,ClientUnit,ClientName,TeamLev,VisitDate,VisitDays,VisitPNumber
  27. From DelegationInfo With(Nolock) {2}
  28. ) as tb Where tb.RowNumber Between {0} And {1} ", currPIndex, currPSize, sqlWhere);
  29. DelegationVisaViewList rst = new DelegationVisaViewList();
  30. rst.CurrPageIndex = currPIndex;
  31. rst.CurrPageSize = currPSize;
  32. List<Grp_DelegationVisaView> dataSource = _sqlSugar.SqlQueryable<Grp_DelegationVisaView>(sql).ToList();
  33. foreach (var item in dataSource)
  34. {
  35. //string sql2 = string.Format(@" Select * From Grp_VisaProgressCustomer With(Nolock) Where DiId={0} And IsDel=0 ", item.DiId);
  36. //List<Grp_VisaProgressCustomer> listComplete = _sqlSugar.SqlQueryable<Grp_VisaProgressCustomer>(sql2).ToList();
  37. string sql2 = string.Format(@" Select WorkStatus as code,COUNT(1) as nums From Grp_VisaProgressCustomer With(Nolock) Where DiId={0} And IsDel=0 Group by WorkStatus ", item.DiId);
  38. List<dynamic> listComplete = _sqlSugar.SqlQueryable<dynamic>(sql2).ToList();
  39. int totalCount = listComplete.Count;
  40. //未作详细描述文本
  41. int count_0 = 0;
  42. int count_1 = 0;
  43. int count_2 = 0;
  44. int count_3 = 0;
  45. foreach (var comCount in listComplete)
  46. {
  47. switch (comCount.code)
  48. {
  49. case 0: count_0 = comCount.nums; break;
  50. case 1: count_1 = comCount.nums; break;
  51. case 2: count_2 = comCount.nums; break;
  52. case 3: count_3 = comCount.nums; break;
  53. default:
  54. break;
  55. }
  56. }
  57. int comNums = totalCount - count_0;
  58. item.CompletePNumber = string.Format(@"{0} / {1}", comNums, totalCount);
  59. }
  60. rst.DataList = new List<Grp_DelegationVisaView>(dataSource);
  61. if (rst.DataList.Count > 0)
  62. {
  63. string sqlCount = string.Format(@" Select Id as DiId From DelegationInfo With(Nolock) {0} ", sqlWhere);
  64. int dataCount = _sqlSugar.SqlQueryable<Grp_DelegationVisaView>(sqlCount).Count();
  65. rst.DataCount = dataCount;
  66. }
  67. return rst;
  68. }
  69. public Grp_DelegationVisaView GetDelegationInfo(int diId)
  70. {
  71. ChangeDataBase(DBEnum.OA2014DB);
  72. string sql = string.Format(@" Select * From(Select ROW_NUMBER() Over(order By Id desc) as RowNumber, Id as DiId,
  73. TeamName,ClientUnit,ClientName,TeamLev,VisitDate,VisitDays,VisitPNumber
  74. From DelegationInfo With(Nolock) Where Id = {0}
  75. ) as tb ", diId);
  76. Grp_DelegationVisaView rst = _sqlSugar.SqlQueryable<Grp_DelegationVisaView>(sql).First();
  77. string sql2 = string.Format(@" Select * From Grp_VisaProgressCustomer With(Nolock) Where DiId={0} And IsDel=0 ", diId);
  78. List<Grp_VisaProgressCustomer> listComplete = _sqlSugar.SqlQueryable<Grp_VisaProgressCustomer>(sql2).ToList();
  79. rst.CompletePNumber = string.Format(@"已完成{0}人", listComplete.Count);
  80. return rst;
  81. }
  82. public List<DelegationVisaProgressView> GetDelegationProgressList(int diid)
  83. {
  84. ChangeDataBase(DBEnum.OA2014DB);
  85. string sqlDeleClient = string.Format(@" Select
  86. Id as 'PersonViewId',
  87. Diid,
  88. LastName,
  89. [Name] as 'FirstName'
  90. From DeleClient With(Nolock) Where Diid = {0} And IsDel = 0 ", diid);
  91. List<DelegationVisaProgressView> clientList = _sqlSugar.SqlQueryable<DelegationVisaProgressView>(sqlDeleClient).ToList();
  92. string sqlDelegation = string.Format(@" Select * From DelegationInfo With(Nolock) Where Id = {0} ", diid);
  93. OA2021_DelegationInfo delegationInfo = _sqlSugar.SqlQueryable<OA2021_DelegationInfo>(sqlDelegation).First();
  94. //string countryName = delegationInfo.VisitCountry;
  95. #region 检查与配置
  96. ChangeDataBase(DBEnum.OA2023DB);
  97. string sqlCheck = string.Format(@" Select * From Grp_VisaProgressCustomer With(Nolock) Where DiId = {0} ", diid);
  98. List<Grp_VisaProgressCustomer> listVisaPro = _sqlSugar.SqlQueryable<Grp_VisaProgressCustomer>(sqlCheck).ToList();
  99. if (listVisaPro.Count < 1)
  100. {
  101. //listVisaPro = new List<Grp_VisaProgressCustomer>(initVisitCountry(diid, delegationInfo));
  102. initVisitCountry(diid, delegationInfo);
  103. }
  104. #endregion
  105. string sqlView1 = string.Format(@" Select
  106. Id as 'ProgressViewId',
  107. DiId,
  108. CustomerId,
  109. LastName,
  110. FirstName,
  111. Country,
  112. WorkStatus as 'StatusSign'
  113. From Grp_VisaProgressCustomer With(Nolock) Where DiId = {0} And IsDel = 0 ", diid);
  114. List<DelegationVisaProgressView> result = _sqlSugar.SqlQueryable<DelegationVisaProgressView>(sqlView1).ToList();
  115. //查询各子项行数
  116. foreach (var pro in result)
  117. {
  118. string sqlCheckCount = string.Format(@" Select PicType,Count(PicType) PicTypeCount
  119. From Grp_VisaProgressCustomerPicture Where VisaProgressCustomerId = {0}
  120. Group by PicType Order By PicType ", pro.ProgressViewId);
  121. List<dynamic> tempList = _sqlSugar.SqlQueryable<dynamic>(sqlCheckCount).ToList();
  122. DelegationVisaPicCountList countList = new DelegationVisaPicCountList();
  123. foreach (var item in tempList)
  124. {
  125. int tempCount = item.PicTypeCount;
  126. switch (item.PicType)
  127. {
  128. case 0: countList.FirstPageCount = tempCount; break;
  129. case 1: countList.DocumentsCount = tempCount; break;
  130. case 2: countList.ExemptionCount = tempCount; break;
  131. case 3: countList.DispatchCount = tempCount; break;
  132. case 4: countList.VisaCount = tempCount; break;
  133. case 5: countList.InvoiceCount = tempCount; break;
  134. default: break;
  135. }
  136. }
  137. countList.totalCount();
  138. pro.PicCountList = countList;
  139. }
  140. return result;
  141. }
  142. public void initVisitCountry(int diid, OA2021_DelegationInfo delegationInfo)
  143. {
  144. ChangeDataBase(DBEnum.OA2014DB);
  145. string sqlDeleClient = String.Format(@" Select * From DeleClient With(Nolock) Where diid = {0} ", diid);
  146. List<OA2021_DeleClient> listDeleClient = _sqlSugar.SqlQueryable<OA2021_DeleClient>(sqlDeleClient).ToList();
  147. ChangeDataBase(DBEnum.OA2023DB);
  148. List<Grp_VisaProgressCustomer> listVisaCustomer = new List<Grp_VisaProgressCustomer>();
  149. string countryName = delegationInfo.VisitCountry;
  150. string[] countrys = Regex.Replace(countryName, @"[,,|-、\s丶]", ",", RegexOptions.IgnoreCase).Split(',');
  151. foreach (var client in listDeleClient)
  152. {
  153. foreach (string cname in countrys)
  154. {
  155. Grp_VisaProgressCustomer item = new Grp_VisaProgressCustomer();
  156. item.Country = cname;
  157. item.CreateUserId = -3;
  158. item.CustomerId = 0;
  159. item.DiId = diid;
  160. item.FirstName = client.Name;
  161. item.LastName = client.LastName;
  162. item.WorkStatus = 0;
  163. listVisaCustomer.Add(item);
  164. }
  165. }
  166. _sqlSugar.Insertable(listVisaCustomer).UseParameter().ExecuteCommand();
  167. //return listVisaCustomer;
  168. }
  169. public List<VisaProgressImageView> GetVisaProgressImageList(int visaProgressCustomerId, int picType)
  170. {
  171. string sql1 = string.Format(@" Select
  172. Id as imageId,
  173. PicPath as 'path',
  174. PicName as picName
  175. From Grp_VisaProgressCustomerPicture
  176. Where VisaProgressCustomerId = {0} And PicType = {1} ", visaProgressCustomerId, picType);
  177. List<VisaProgressImageView> imageList = _sqlSugar.SqlQueryable<VisaProgressImageView>(sql1).ToList();
  178. return imageList;
  179. }
  180. }
  181. }