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));
}