c# - 有多个入住/退房/午餐时间并获取总时间

Question

我有 2 个日期时间选择器。假设我的数据库中已经保存了所需的数据

//Status      //Date      //Time      //name
----------------------------------------------
Check In     1/8/2016    12:30:36pm     ali
  Lunch      1/8/2016     2:40:36pm     ali
Check In     1/8/2016     3:40:36pm     ali
Check Out    1/8/2016     6:40:36pm     ali

因为我不想计算午餐时间。因为我想计算我的员工当天的工作内容

6:40:36 PM - 12:30:36pm = 6 //total hours worked include lunch

所以我必须减去午餐 - Checkk，其中需要 1 小时

6 - (3:40:36 PM - 2:40:36 PM) = 5 hours //total hours that worked

我应该用什么样的逻辑来做这件事？我已经知道可以从数据库中选择的所有类型的 SQL 子句。但我需要一种可以更轻松地计算此值而无需实现大量代码的方法。

Answer 1

这不是特别健壮，可能需要进行一些重构，但可能会给您一个扩展自己逻辑的起点。

您的状态作为枚举:

public enum Status
    {
        CheckIn,
        CheckOut,
        Lunch
    }

将您的数据转换为以下列表:

public class EmployeeStatusChange
    {
        public int EmployeeId { get; set; }

        public DateTime DateTime { get; set; }

        public Status Status { get; set; }
    }

然后使用这个扩展方法:

public static double CalculateHours(this List<EmployeeStatusChange> changes)
    {
        changes = changes.OrderBy(x => x.DateTime).ToList();

        double hours = 0;
        var start = DateTime.MinValue;
        var lunch = DateTime.MinValue;
        var checkedOut = false;

        foreach (var change in changes)
        {
            // exclude anything before the first check in, or if we have already checked out
            if ((start == DateTime.MinValue && change.Status != Status.CheckIn) || checkedOut)
            {
                continue;
            }

            // Set the start time
            if (start == DateTime.MinValue && change.Status == Status.CheckIn)
            {
                start = change.DateTime;
                continue;
            }

            switch (change.Status)
            {
                case Status.CheckIn:
                    if (lunch == DateTime.MinValue)
                    {
                        continue;
                    }

                    start = change.DateTime;
                    continue;

                case Status.Lunch:
                    lunch = change.DateTime;
                    hours += (change.DateTime - start).TotalHours;
                    break;

                case Status.CheckOut:
                    checkedOut = true;
                    hours += (change.DateTime - start).TotalHours;
                    break;
            }
        }

        return hours;
    }

这将返回 6.5:

var items = new List<EmployeeStatusChange>();
items.Add(new EmployeeStatusChange { EmployeeId = 1, Status = Status.CheckIn, DateTime = new DateTime(2015, 1, 1, 9, 0, 0) });
items.Add(new EmployeeStatusChange { EmployeeId = 1, Status = Status.Lunch, DateTime = new DateTime(2015, 1, 1, 10, 30, 0) });
items.Add(new EmployeeStatusChange { EmployeeId = 1, Status = Status.CheckIn, DateTime = new DateTime(2015, 1, 1, 11, 0, 0) });
items.Add(new EmployeeStatusChange { EmployeeId = 1, Status = Status.Lunch, DateTime = new DateTime(2015, 1, 1, 12, 0, 0) });
items.Add(new EmployeeStatusChange { EmployeeId = 1, Status = Status.CheckIn, DateTime = new DateTime(2015, 1, 1, 13, 0, 0) });
items.Add(new EmployeeStatusChange { EmployeeId = 1, Status = Status.CheckOut, DateTime = new DateTime(2015, 1, 1, 17, 0, 0) });
items.CalculateHours();