Esempio n. 1
0
 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\"=@WorkerContactId 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) });
 }
Esempio n. 2
0
        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\"=@MatterId)) 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\"=@MatterId)) 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\"=@MatterId)) 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\"=@MatterId)) AND \"utc_disabled\" is null ORDER BY \"start\" ASC",
                    new { MatterId = matterId });
        }
Esempio n. 3
0
        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\"=@ContactId) " +
                    "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\"=@ContactId) " +
                    "AND \"utc_disabled\" is null AND \"start\">=@Start ORDER BY \"start\" ASC",
                    new { ContactId = contactId, Start = start });

            return events;
        }
Esempio n. 4
0
 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\"=@WorkerContactId) OR " + // 1 and 2
         "(@Stop > \"start\" AND @Stop < \"stop\" AND \"worker_contact_id\"=@WorkerContactId) OR " + // 1 and 2
         "(@Start <= \"start\" AND @Stop >= \"stop\" AND \"worker_contact_id\"=@WorkerContactId)",
         new { Start = start, Stop = stop, WorkerContactId = workerContactId });
 }