public static async Task<Decimal> SumHoursAsync(this DbSet<Timecard> dbSet, TimecardFilter filter)
{
const String query = @"SELECT SUM(Hours) FROM t.Timecard WHERE {0}";
String where = CreateWhere(filter);
String curQuery = String.Format(query, where);
ICollection<SqlParameter> parameters = CreateParameters(filter);
return await GitTimeContext.GetContext(dbSet).Database.SqlQuery<Decimal?>(curQuery, parameters.ToArray()).FirstOrDefaultAsync() ?? 0;
}
public static async Task<Int32> CountAsync(this DbSet<Timecard> dbSet, TimecardFilter filter)
{
const String query = @"SELECT CAST(COUNT(*) AS INT) FROM t.Timecard WHERE {0}";
String where = CreateWhere(filter);
String curQuery = String.Format(query, where);
ICollection<SqlParameter> parameters = CreateParameters(filter);
return await GitTimeContext.GetContext(dbSet).Database.SqlQuery<Int32>(curQuery, parameters.ToArray()).FirstOrDefaultAsync();
}
public static async Task<ICollection<TimecardFinderRow>> SelectFinderRowsAsync(this DbSet<Timecard> dbSet, Int32 startRow, Int32 endRow, TimecardFilter filter, String sortExpression)
{
const String query = @"
WITH Timecard AS
(
SELECT
Timecard.pk_ID AS TimecardID
,Timecard.EntryDate
,Person.FirstName AS PersonName
,Project.Repository
,Company.Name AS CompanyName
,Timecard.IssueDescription
,Timecard.[Hours]
,ROW_NUMBER() OVER (ORDER BY {1}) AS RowNumber
FROM
t.Timecard
INNER JOIN c.Contact AS Person ON Person.pk_ID = Timecard.fk_PersonContactID
INNER JOIN p.Project ON Project.pk_ID = Timecard.fk_ProjectID
INNER JOIN c.Contact AS Company ON Company.pk_ID = Project.fk_CompanyContactID
WHERE
{0}
)
SELECT * FROM Timecard
WHERE RowNumber BETWEEN @StartRow AND @EndRow
ORDER BY RowNumber
";
if (String.IsNullOrEmpty(sortExpression))
sortExpression = "EntryDate DESC, TimecardID DESC";
String rowNumberOrderBy = sortExpression
.Replace("TimecardID", "Timecard.pk_ID")
;
String where = CreateWhere(filter);
String curQuery = String.Format(query, where, rowNumberOrderBy);
ICollection<SqlParameter> parameters = CreateParameters(filter);
GitTimeContext.AddParameter("@StartRow", SqlDbType.Int, startRow, parameters);
GitTimeContext.AddParameter("@EndRow", SqlDbType.Int, endRow, parameters);
return await GitTimeContext.GetContext(dbSet).Database.SqlQuery<TimecardFinderRow>(curQuery, parameters.ToArray()).ToListAsync();
}
private static String CreateWhere(TimecardFilter filter)
{
var where = new StringBuilder("1 = 1");
if (filter.ProjectID.HasValue)
where.Append(" AND Timecard.fk_ProjectID = @ProjectID");
if (filter.PersonContactID.HasValue)
where.Append(" AND Timecard.fk_PersonContactID = @PersonContactID");
if (filter.EntryDateFrom.HasValue)
where.Append(" AND Timecard.EntryDate >= @EntryDateFrom");
if (filter.EntryDateThru.HasValue)
where.Append(" AND Timecard.EntryDate < @EntryDateThru");
return where.ToString();
}
private static ICollection<SqlParameter> CreateParameters(TimecardFilter filter)
{
var parameters = new List<SqlParameter>();
if (filter.ProjectID.HasValue)
GitTimeContext.AddParameter("@ProjectID", SqlDbType.Int, filter.ProjectID, parameters);
if (filter.PersonContactID.HasValue)
GitTimeContext.AddParameter("@PersonContactID", SqlDbType.Int, filter.PersonContactID, parameters);
if (filter.EntryDateFrom.HasValue)
GitTimeContext.AddParameter("@EntryDateFrom", SqlDbType.DateTime, filter.EntryDateFrom.Value.Date, parameters);
if (filter.EntryDateThru.HasValue)
GitTimeContext.AddParameter("@EntryDateThru", SqlDbType.DateTime, filter.EntryDateThru.Value.Date.AddDays(1), parameters);
return parameters;
}