c# - 多次办理入住/退房/午餐时间并获得总时数

我有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子句。但是我需要一种无需执行大量代码即可更轻松地计算出这一点的方法。

最佳答案

这不是特别健壮，可能可以进行一些重构，但是可能为您扩展自己的逻辑提供了一个起点。

您的状态为枚举：

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