public static List<Common.Models.Timing.Time> ListForDay(int workerContactId, DateTime date)
{
if (date.Kind != DateTimeKind.Utc)
date = date.ToDbTime();
return DataHelper.List<Common.Models.Timing.Time, DBOs.Timing.Time>(
"SELECT * FROM \"time\" WHERE \"worker_contact_id\"[email protected] AND \"start\" BETWEEN @Start AND @Stop AND \"utc_disabled\" is null ORDER BY \"start\" ASC",
new { WorkerContactId = workerContactId, Start = date, Stop = date.AddDays(1).AddMilliseconds(-1) });
}
public static List<Common.Models.Timing.Time> ListForMatterWithinRange(Guid matterId, DateTime? from = null, DateTime? to = null)
{
if (from.HasValue && from.Value.Kind != DateTimeKind.Utc)
from = from.ToDbTime();
if (to.HasValue && to.Value.Kind != DateTimeKind.Utc)
to = to.ToDbTime();
if (from.HasValue && to.HasValue)
return DataHelper.List<Common.Models.Timing.Time, DBOs.Timing.Time>(
"SELECT * FROM \"time\" WHERE \"id\" IN (SELECT \"time_id\" FROM \"task_time\" WHERE \"task_id\" IN (SELECT \"task_id\" FROM \"task_matter\" WHERE \"matter_id\"[email protected])) AND \"start\" BETWEEN @Start AND @Stop AND \"utc_disabled\" is null ORDER BY \"start\" ASC",
new { MatterId = matterId, Start = from.Value, Stop = to.Value.AddDays(1).AddMilliseconds(-1) });
else if (from.HasValue && !to.HasValue)
return DataHelper.List<Common.Models.Timing.Time, DBOs.Timing.Time>(
"SELECT * FROM \"time\" WHERE \"id\" IN (SELECT \"time_id\" FROM \"task_time\" WHERE \"task_id\" IN (SELECT \"task_id\" FROM \"task_matter\" WHERE \"matter_id\"[email protected])) AND \"start\" >= @Start AND \"utc_disabled\" is null ORDER BY \"start\" ASC",
new { MatterId = matterId, Start = from.Value });
else if (!from.HasValue && to.HasValue)
return DataHelper.List<Common.Models.Timing.Time, DBOs.Timing.Time>(
"SELECT * FROM \"time\" WHERE \"id\" IN (SELECT \"time_id\" FROM \"task_time\" WHERE \"task_id\" IN (SELECT \"task_id\" FROM \"task_matter\" WHERE \"matter_id\"[email protected])) AND \"start\" <= @Stop AND \"utc_disabled\" is null ORDER BY \"start\" ASC",
new { MatterId = matterId, Stop = to.Value.AddDays(1).AddMilliseconds(-1) });
else // !from && !to
return DataHelper.List<Common.Models.Timing.Time, DBOs.Timing.Time>(
"SELECT * FROM \"time\" WHERE \"id\" IN (SELECT \"time_id\" FROM \"task_time\" WHERE \"task_id\" IN (SELECT \"task_id\" FROM \"task_matter\" WHERE \"matter_id\"[email protected])) AND \"utc_disabled\" is null ORDER BY \"start\" ASC",
new { MatterId = matterId });
}
public static List<Common.Models.Events.Event> ListForContact(int contactId, DateTime start, DateTime? stop)
{
List<Common.Models.Events.Event> events;
start = start.ToDbTime();
if (stop.HasValue)
{
stop = stop.Value.ToDbTime();
events = DataHelper.List<Common.Models.Events.Event, DBOs.Events.Event>(
"SELECT * FROM \"event\" WHERE \"id\" IN (SELECT \"event_id\" FROM \"event_assigned_contact\" WHERE \"contact_id\"[email protected]) " +
"AND \"utc_disabled\" is null AND \"start\" BETWEEN @Start AND @Stop ORDER BY \"start\" ASC",
new { ContactId = contactId, Start = start, Stop = stop });
}
else
events = DataHelper.List<Common.Models.Events.Event, DBOs.Events.Event>(
"SELECT * FROM \"event\" WHERE \"id\" IN (SELECT \"event_id\" FROM \"event_assigned_contact\" WHERE \"contact_id\"[email protected]) " +
"AND \"utc_disabled\" is null AND \"start\">[email protected] ORDER BY \"start\" ASC",
new { ContactId = contactId, Start = start });
return events;
}
public static List<Common.Models.Timing.Time> ListConflictingTimes(DateTime start, DateTime stop, int workerContactId)
{
// Check for overlap
// We work in time frames or windows
// The new time can either (1) be within an existing time, (2) overlap an existing time, (3) encumpas an existing time or (4) be exclusive in reference to other time
// The ONLY valid entry is #4
if (start.Kind != DateTimeKind.Utc)
start = start.ToDbTime();
if (stop.Kind != DateTimeKind.Utc)
stop = stop.ToDbTime();
return DataHelper.List<Common.Models.Timing.Time, DBOs.Timing.Time>(
"SELECT * FROM time WHERE (@Start > \"start\" AND @Start < \"stop\" AND \"worker_contact_id\"[email protected]) OR " + // 1 and 2
"(@Stop > \"start\" AND @Stop < \"stop\" AND \"worker_contact_id\"[email protected]) OR " + // 1 and 2
"(@Start <= \"start\" AND @Stop >= \"stop\" AND \"worker_contact_id\"[email protected])",
new { Start = start, Stop = stop, WorkerContactId = workerContactId });
}
