private DbQuery BuildConditionQuery(DbQuery dbQuery, DateQuery query) { var condition = new AndQueryCondition(); if (query.SchoolYearId.HasValue) { condition.Add(Date.SCHOOL_YEAR_REF, query.SchoolYearId, ConditionRelation.Equal); } if (query.DayType.HasValue) { condition.Add(Date.DATE_TYPE_REF_FIELD, query.DayType, ConditionRelation.Equal); } if (query.FromDate.HasValue) { condition.Add(Date.DATE_TIME_FIELD, "fromDate", query.FromDate, ConditionRelation.GreaterEqual); } if (query.ToDate.HasValue) { condition.Add(Date.DATE_TIME_FIELD, "toDate", query.ToDate, ConditionRelation.LessEqual); } if (query.SchoolDaysOnly) { condition.Add(Date.IS_SCHOOL_DAY_FIELD, true, ConditionRelation.Equal); } condition.BuildSqlWhere(dbQuery, "Date"); if (query.MarkingPeriodId.HasValue) { dbQuery.Parameters.Add("@markingPeriodId", query.MarkingPeriodId); dbQuery.Sql.AppendFormat(@" and exists(select * from MarkingPeriod where [MarkingPeriod].[{0}] = @markingPeriodId and [MarkingPeriod].[{1}] <= [Date].[{3}] and [MarkingPeriod].[{2}] >= [Date].[{3}])" , MarkingPeriod.ID_FIELD, MarkingPeriod.START_DATE_FIELD, MarkingPeriod.END_DATE_FIELD, Date.DATE_TIME_FIELD); } return(dbQuery); }
public IList <Date> GetDates(DateQuery query) { var q = new DbQuery(); q.Sql.Append("select * from [Date]"); q = BuildConditionQuery(q, query); q.Sql.Append($" order by {Date.DATE_TIME_FIELD} desc OFFSET 0 ROWS FETCH NEXT {query.Count} ROWS ONLY"); q = new DbQuery($"select * from ({q.Sql}) x order by x.{Date.DATE_TIME_FIELD}", q.Parameters); return(ReadMany <Date>(q)); }