RoomSettingDAL.cs 11 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187
  1. using System;
  2. using System.Collections.Generic;
  3. using System.Linq;
  4. using System.Text;
  5. using System.Linq.Expressions;
  6. using Bowin.Common.Linq;
  7. using EMIS.DataLogic.Repositories;
  8. using EMIS.ViewModel.UniversityManage.ClassroomManage;
  9. using EMIS.Entities;
  10. using EMIS.ViewModel.ExaminationManage;
  11. using EMIS.ViewModel.ExaminationApply;
  12. using EMIS.ViewModel;
  13. using EMIS.DataLogic.UniversityManage.ClassroomManage;
  14. namespace EMIS.DataLogic.ExaminationManage
  15. {
  16. public class RoomSettingDAL
  17. {
  18. public ExaminationRoomSettingRepository ExaminationRoomSettingRepository { get; set; }
  19. public ExaminationRoomLayoutRepository ExaminationRoomLayoutRepository { get; set; }
  20. public ExaminationPlanRepository ExaminationPlanRepository { get; set; }
  21. public ClassroomRepository ClassroomRepository { get; set; }
  22. public DictionaryItemRepository DictionaryItemRepository { get; set; }
  23. public Lazy<ClassroomDAL> ClassroomDAL { get; set; }
  24. public virtual IQueryable<ExaminationClassroomView> GetAvailableClassroomView(Expression<Func<EX_ExaminationPlan, bool>> examinationPlanExp,
  25. Expression<Func<EX_ExaminationRoomLayout, bool>> examinationRoomLayoutExp,
  26. Expression<Func<CF_Classroom, bool>> classroomExp)
  27. {
  28. var q = (from c in ClassroomRepository.GetList(classroomExp)
  29. join b in DictionaryItemRepository.Entities on new { c.RoomUseID, DictionaryCode = typeof(CF_RoomUse).Name }
  30. equals new { RoomUseID = b.Value, b.DictionaryCode } into gru
  31. from giru in gru.DefaultIfEmpty()
  32. join cid in
  33. (
  34. (from ers in ExaminationRoomSettingRepository.Entities
  35. join erl in ExaminationRoomLayoutRepository.GetList(examinationRoomLayoutExp) on ers.ClassroomID equals erl.ClassroomID into derl
  36. from eerl in derl.DefaultIfEmpty()
  37. join nas in
  38. (
  39. from ep in ExaminationPlanRepository.GetList(examinationPlanExp)
  40. join erl in ExaminationRoomLayoutRepository.Entities on true equals true
  41. where erl.EX_ExaminationPlan.ExaminationDate == ep.ExaminationDate
  42. && erl.EX_ExaminationPlan.EndTime >= ep.StartTime && erl.EX_ExaminationPlan.StartTime <= ep.EndTime
  43. group erl by erl.ClassroomID into g
  44. select new { ClassroomID = g.Key, StudentCount = (int?)g.Sum(x => x.EX_ExaminationRoomStudent.Count()) }
  45. ) on ers.ClassroomID equals nas.ClassroomID into dnas
  46. from enas in dnas.DefaultIfEmpty()
  47. where (enas.ClassroomID == null || ers.CF_Classroom.Examinationseating > (enas.StudentCount ?? 0)) && eerl.ExaminationRoomLayoutID == null
  48. select new { ers.ClassroomID, RemainSeatCount = ers.CF_Classroom.Examinationseating - (enas.StudentCount ?? 0) }
  49. ).Concat(
  50. from erl in ExaminationRoomLayoutRepository.GetList(examinationRoomLayoutExp)
  51. from oerl in ExaminationRoomLayoutRepository.Entities.Where(x => x.ExaminationRoomLayoutID != erl.ExaminationRoomLayoutID
  52. && x.ClassroomID == erl.ClassroomID && erl.EX_ExaminationPlan.ExaminationDate == x.EX_ExaminationPlan.ExaminationDate
  53. && x.EX_ExaminationPlan.EndTime >= erl.EX_ExaminationPlan.StartTime
  54. && x.EX_ExaminationPlan.StartTime <= erl.EX_ExaminationPlan.EndTime).DefaultIfEmpty()
  55. group oerl by new { erl.ClassroomID, erl.CF_Classroom.Examinationseating } into g
  56. select new { g.Key.ClassroomID, RemainSeatCount = g.Key.Examinationseating - g.Sum(x => (x.ExaminationRoomLayoutID == null) ? 0 : x.EX_ExaminationRoomStudent.Count()) }
  57. )
  58. ) on c.ClassroomID equals cid.ClassroomID
  59. select new ExaminationClassroomView
  60. {
  61. ClassroomID = c.ClassroomID,
  62. CampusID = c.CF_BuildingsInfo.CampusID,
  63. CollegeID = c.CollegeID,
  64. CollegeCode = c.CF_College.No,
  65. CollegeName = c.CF_College.Name,
  66. Code = c.Code,
  67. Name = c.Name,
  68. BuildingsInfoID = c.BuildingsInfoID,
  69. BuildingsInfoCode = c.CF_BuildingsInfo.Code,
  70. BuildingsInfoName = c.CF_BuildingsInfo.Name,
  71. RoomUseID = c.RoomUseID,
  72. FloorLevel = c.FloorLevel,
  73. IsAvailable = c.IsAvailable ?? false,
  74. IsConcurrentUse = c.IsConcurrentUse ?? false,
  75. Acreage = c.Acreage,
  76. RowCout = c.RowCout,
  77. ColumnCount = c.ColumnCount,
  78. Totalseating = c.Totalseating,
  79. Effectiveseating = c.Effectiveseating,
  80. Examinationseating = c.Examinationseating,
  81. RemainSeatCount = cid.RemainSeatCount,
  82. LayoutTypeID = c.LayoutTypeID,
  83. IsWrittenExam = c.IsWrittenExam ?? false,
  84. IsMachinetest = c.IsMachinetest ?? false,
  85. CreateUserID = c.CreateUserID,
  86. CreateTime = c.CreateTime,
  87. Remark = c.Remark,
  88. CF_ClassroomType = c.CF_ClassroomType
  89. });
  90. return q;
  91. }
  92. public IQueryable<ExaminationClassroomView> GetAvailableClassroomViewGdss(Expression<Func<EX_ExaminationPlan, bool>> otherExaminationPlanExp,
  93. Expression<Func<CF_Classroom, bool>> classroomExp, int? studentCount)
  94. {
  95. var q = (from c in ClassroomRepository.GetList(classroomExp)
  96. join b in DictionaryItemRepository.Entities on new { c.RoomUseID, DictionaryCode = typeof(CF_RoomUse).Name }
  97. equals new { RoomUseID = b.Value, b.DictionaryCode } into gru
  98. from giru in gru.DefaultIfEmpty()
  99. join cid in
  100. (from ers in ExaminationRoomSettingRepository.Entities
  101. join nas in
  102. (
  103. from otherPlan in ExaminationPlanRepository.Entities.Where(otherExaminationPlanExp)
  104. from otherLayout in otherPlan.EX_ExaminationRoomLayout
  105. group otherLayout by otherLayout.ClassroomID into g
  106. select new { ClassroomID = g.Key, StudentCount = (int?)g.Sum(x => x.EX_ExaminationRoomStudent.Count()) }
  107. ) on ers.ClassroomID equals nas.ClassroomID into dnas
  108. from enas in dnas.DefaultIfEmpty()
  109. where (enas.ClassroomID == null || ers.CF_Classroom.Examinationseating > (enas.StudentCount ?? 0))
  110. select new { ers.ClassroomID, RemainSeatCount = ers.CF_Classroom.Examinationseating - (enas.StudentCount ?? 0) }
  111. ) on c.ClassroomID equals cid.ClassroomID
  112. where (studentCount == null || cid.RemainSeatCount >= studentCount)
  113. select new ExaminationClassroomView
  114. {
  115. ClassroomID = c.ClassroomID,
  116. CampusID = c.CF_BuildingsInfo.CampusID,
  117. CollegeID = c.CollegeID,
  118. CollegeCode = c.CF_College.No,
  119. CollegeName = c.CF_College.Name,
  120. Code = c.Code,
  121. Name = c.Name,
  122. BuildingsInfoID = c.BuildingsInfoID,
  123. BuildingsInfoCode = c.CF_BuildingsInfo.Code,
  124. BuildingsInfoName = c.CF_BuildingsInfo.Name,
  125. RoomUseID = c.RoomUseID,
  126. FloorLevel = c.FloorLevel,
  127. IsAvailable = c.IsAvailable ?? false,
  128. IsConcurrentUse = c.IsConcurrentUse ?? false,
  129. Acreage = c.Acreage,
  130. RowCout = c.RowCout,
  131. ColumnCount = c.ColumnCount,
  132. Totalseating = c.Totalseating,
  133. Effectiveseating = c.Effectiveseating,
  134. Examinationseating = c.Examinationseating,
  135. RemainSeatCount = cid.RemainSeatCount,
  136. LayoutTypeID = c.LayoutTypeID,
  137. IsWrittenExam = c.IsWrittenExam ?? false,
  138. IsMachinetest = c.IsMachinetest ?? false,
  139. CreateUserID = c.CreateUserID,
  140. CreateTime = c.CreateTime,
  141. Remark = c.Remark,
  142. CF_ClassroomType = c.CF_ClassroomType
  143. });
  144. return q;
  145. }
  146. public IQueryable<ClassroomConflictView> GetConflictClassroomView(Expression<Func<EX_ExaminationPlan, bool>> examinationPlanExp,
  147. DateTime? date, TimeSpan? startTime, TimeSpan? endTime)
  148. {
  149. var q = (
  150. from ep in ExaminationPlanRepository.GetList(examinationPlanExp)
  151. from erl in ep.EX_ExaminationRoomLayout
  152. join erlt in
  153. (
  154. from erlt1 in ExaminationRoomLayoutRepository.Entities
  155. select new
  156. {
  157. ClassroomID = erlt1.ClassroomID,
  158. ExaminationPlanID = erlt1.EX_ExaminationPlan.ExaminationPlanID,
  159. ExaminationPlanName = erlt1.EX_ExaminationPlan.ClassName,
  160. erlt1.EX_ExaminationPlan.ExaminationDate,
  161. erlt1.EX_ExaminationPlan.StartTime,
  162. erlt1.EX_ExaminationPlan.EndTime
  163. }
  164. ) on erl.ClassroomID equals erlt.ClassroomID
  165. where ep.ExaminationPlanID != erlt.ExaminationPlanID && erlt.ExaminationDate == date
  166. && erlt.EndTime >= startTime && erlt.StartTime <= endTime
  167. select new ClassroomConflictView
  168. {
  169. ClassroomID = erl.CF_Classroom.ClassroomID,
  170. Name = erl.CF_Classroom.Name,
  171. ExaminationPlanID = erlt.ExaminationPlanID,
  172. ExaminationPlanName = erlt.ExaminationPlanName,
  173. StartTime = erlt.StartTime,
  174. EndTime = erlt.EndTime
  175. }
  176. );
  177. return q;
  178. }
  179. }
  180. }