考勤报表生成

需要：根据原始打卡记录生成考勤报表

配置：

       班次-> 由开始时间点，结束时间点， 开始时间区间，结束时间区间组成

       考勤检查方案->由多个班次组成,（属于这个考勤检查方案的人员使用这些班次规则进行统计）

业务:一天多个班次的需要考虑班次之间的连续打卡情况（允许），两次打卡时间间隔需要大于3分钟

代码:

      1.加载前一天全部打卡记录,按时间排序后，过滤掉短时间内的重复打卡记录

      2.遍历每个考勤检测方案。

      2.1.加载该方案下的全部人员，加载员工的当天的全部打卡记录，对方案内的班次配置与员工的打卡记录进行匹配

      2.2.班次间的连续打卡问题需要前溯或后溯进行处理

      2.3.根据匹配情况形成报表

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
using System.Collections;
using F.Studio.Util;
namespace Checking_In.UI
{
    public partial class frmMain : Form
    {

        #region Excel相关操作
        const string C_ExcelConnTmp = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= {0};Extended Properties='Excel 8.0;HDR=YES;IMEX=1';";
        public string ExcelFile
        {
            get { return @"E:NetHR_KQ9月质量部.xls"; }
        }
        public string Sheet
        {
            get
            {
                return "9月质量部$";
            }
        }
        public string ExcelConnectString
        {
            get
            {
                return string.Format(C_ExcelConnTmp, ExcelFile);
            }
        }

        public new List<TableInfo> Tables
        {
            get;
            private set;
        }
        public ArrayList ExcelSheetName()
        {
            ArrayList al = new ArrayList();
            string strConn;
            strConn = ExcelConnectString;
            OleDbConnection conn = new OleDbConnection(strConn);
            conn.Open();
            DataTable sheetNames = conn.GetOleDbSchemaTable
            (System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
            conn.Close();
            foreach (DataRow dr in sheetNames.Rows)
            {
                al.Add(dr[2]);
            }
            return al;
        }  
        private void LoadExcelTableInfo()
        {

            Tables = new List<TableInfo>();

            var ds = LoadExcelData();

            TableInfo curTable = null;
            for (int i = 1; i < ds.Tables[0].Rows.Count; i++)
            {
                DataRow dr = ds.Tables[0].Rows[i];
                if (string.IsNullOrWhiteSpace(dr["新加"].ToString()))//空表示表信息
                {
                    curTable = new TableInfo();
                    curTable.Name = dr["F2"].ToString().Trim();
                    curTable.Code = dr["p_bs"].ToString().Trim();

                    Tables.Add(curTable);
                    continue;

                }
                var column = new ColumnInfo();
                column.Code = dr["新加"].ToString().Trim();
                string tmp = dr["删除"].ToString();
                if (!string.IsNullOrWhiteSpace(tmp))
                {
                    column.Name = tmp.Trim().Split(" ".ToArray(), StringSplitOptions.RemoveEmptyEntries)[0];
                    column.Comment = tmp.Trim();
                    if (column.Name.Length >= 10)
                    {
                        column.Name = column.Name.Substring(0, 10);
                    }
                    column.NeedSync = true;
                }
                curTable.Columns.Add(column);
            }

        }
        private DataSet LoadExcelData()
        {
            DataSet dsExcelSheet = new DataSet();

            //打开并读取Excel表信息
            OleDbConnection connExecel = new OleDbConnection(ExcelConnectString);
            OleDbDataAdapter dpQualityLevel = new OleDbDataAdapter("SELECT * FROM [" + Sheet + "]", connExecel);

            connExecel.Open();

            dpQualityLevel.Fill(dsExcelSheet);
            connExecel.Close();

            return dsExcelSheet;
        }
        #region Class
        public class TableInfo
        {
            public TableInfo()
            {
                Columns = new List<ColumnInfo>();
            }
            public string Name { get; set; }
            public string Code { get; set; }
            public List<ColumnInfo> Columns { get; set; }

        }
        public class ColumnInfo
        {
            public string Name { get; set; }
            public string Code { get; set; }
            public string Comment { get; set; }
            /// <summary>
            /// 是否需要同步
            /// </summary>
            public bool NeedSync { get; set; }
        }
        #endregion
        #endregion

        private List<CheckingRule> CheckingRules = new List<CheckingRule>();
        private List<ShiftInfo> ShiftList = new List<ShiftInfo>();
        private List< CheckingInfo> CheckingRecords=null;
        private List<EmpInfo> Emps = new List<EmpInfo>();
        private List<DateTime> Days = new List<DateTime>();
        private List<CheckingRstInfo> Rpt = new List<CheckingRstInfo>();
        public frmMain()
        {
            InitializeComponent();

            #region 上班时间配置配置
            ShiftList.Add(
                new ShiftInfo(){
                    InRule=new CheckingRule() { CheckPoint = "07:00", CheckType = 1, RangBegin = "00:00", RangEnd = "09:00",Title="质量上午进" },
                    OutRule=new CheckingRule() { CheckPoint = "11:00", CheckType = 2, RangBegin = "09:00", RangEnd = "12:00", Title = "质量上午出" },
                    Name="质量早班"
                });
            ShiftList.Add(
                new ShiftInfo(){
                    InRule=new CheckingRule() { CheckPoint = "13:00", CheckType = 1, RangBegin = "12:00", RangEnd = "15:30", Title = "质量下午进" },
                    OutRule=new CheckingRule() { CheckPoint = "17:00", CheckType = 2, RangBegin = "15:30", RangEnd = "17:30", Title = "质量下午出" },
                    Name="质量中班"
                });
            ShiftList.Add(
               new ShiftInfo()
               {
                   InRule = new CheckingRule() { CheckPoint = "18:00", CheckType = 1, RangBegin = "17:30", RangEnd = "19:30", Title = "质量晚上进" },
                   OutRule = new CheckingRule() { CheckPoint = "21:00", CheckType = 2, RangBegin = "19:30", RangEnd = "23:59", Title = "质量晚上出" },
                   Name = "质量晚班"
               });

            #endregion
        }

        private void  LoadData()
        {
            var ds = LoadExcelData();
            var list=new List< CheckingInfo>();
            var index = 0;
            foreach (DataRow item in ds.Tables[0].Rows)
            {
                index++;
                DateTime addtime;
                var dtStr = item["日期时间"].ToString();
                if (!DateTime.TryParse(dtStr, out addtime))
                {
                    Console.WriteLine("异常记录!"+index);
                    continue;
                }

                var it=new CheckingInfo();
                it.Dept = item["部门"].ToString();
                it.EmpNo = item["编号"].ToString();
                it.Name = item["姓名"].ToString();
                it.AddTime = addtime;
                it.RawAddTime = addtime;
                it.MachineNo = item["机器号"].ToString();
                it.CheckType = item["比对方式"].ToString();
                list.Add(it);
            }

            #region 排除间隔过短的记录
            //时间 2分钟
            int count = 0;
            int intervalSeconds = 1800;
            list = list.OrderBy(ent => ent.AddTime).ToList();
            for (int i = 0; i < list.Count;i++ )
            {
                var it = list[i];
                if (it.DelFlag) continue;
                for (var j = i + 1; j < list.Count; j++)
                {
                    
                    var next = list[j];
                    if (next.DelFlag)continue;
                    if( (next.AddTime - it.AddTime).TotalSeconds >intervalSeconds)break;
                    if (next.EmpNo==it.EmpNo)
                    {
                        next.DelFlag = true;
                        Console.WriteLine(next.Name + "," + next.AddTime + "," + next.EmpNo);
                        count++;
                    }
                }
            }
            Console.WriteLine(count);
            
            #endregion
            CheckingRecords =list.Where(ent=>ent.DelFlag==false).OrderBy(ent=>ent.AddTime).ToList();
        }
        private void ExtractEmpAndDate()
        {

            var list = CheckingRecords.GroupBy(ent => ent.EmpNo).ToList();
            foreach (var it in list)
            {
                var emp=new EmpInfo();
                emp.EmpNo = it.Key;
                emp.Name = it.FirstOrDefault().Name;
                emp.Dept = it.FirstOrDefault().Dept;
                Emps.Add(emp);
            }
            var list2 = CheckingRecords.GroupBy(ent => ent.AddTime.Date);
            foreach(var it in list2)
            {
                Days.Add(it.Key);
            }
            Days=  Days.OrderBy(ent => ent.Date).ToList();

        }

        private void Analyze()
        {
            var rpts = new List<CheckingRstInfo>();

            foreach (var day in Days)
            {
                foreach (var emp in Emps)
                {
                   var checklist=  CheckingRecords.Where(ent => ent.AddTime >= day && ent.AddTime <= day.AddHours(23.999) && ent.EmpNo == emp.EmpNo).OrderBy(ent => ent.AddTime).ToList();

                    for(int index=0;index< ShiftList.Count;index++)
                    {
                        var shift=ShiftList[index];
                      #region 按配置进行分析
                
                       var rpt = new CheckingRstInfo() {
                           Date=day,Dept=emp.Dept,EmpNo=emp.EmpNo,
                           ShowInReport=false,Name=emp.Name,
                           IsAbsenteeism=false,InTime="",OutTime="" };
                       rpts.Add(rpt);

                  
                       rpt.RuleTitle = shift.Name;
                      

                       foreach (var rule in new List<CheckingRule>() { shift.InRule, shift.OutRule })
                       {
                           #region 上下班检测
                           DateTime eventTime;
                           var btime = rule.GetTime(day, rule.RangBegin);
                           var etime = rule.GetTime(day, rule.RangEnd);
                           var ptime = rule.GetTime(day, rule.CheckPoint);
                           if (rule.CheckType == 1)//上班
                           {
                               var inTimeList = checklist.Where(ent => ent.AddTime > btime && ent.AddTime <= etime).ToList();
                               if (inTimeList.Count <= 0)
                               {
                                   #region 向前寻找
                                   if (index == 1)
                                   {
                                       var prevTime = rule.GetTime(day, ShiftList[0].OutRule.CheckPoint);
                                       var lista = checklist.Where(ent => ent.AddTime > prevTime && ent.AddTime <= etime).ToList();
                                       if (lista.Count >= 2)
                                       {
                                           var item = lista.OrderBy(ent => ent.AddTime).ToList().LastOrDefault();
                                           inTimeList.Add(item);
                                           checklist.Remove(item);
                                       }
                                   }
                                   else if (index == 2)
                                   {
                                       var prevTime = rule.GetTime(day, ShiftList[1].OutRule.CheckPoint);
                                       var lista = checklist.Where(ent => ent.AddTime > prevTime && ent.AddTime <= etime).ToList();
                                       if (lista.Count >= 2)
                                       {
                                           var item = lista.OrderBy(ent => ent.AddTime).ToList().LastOrDefault();
                                           inTimeList.Add(item);
                                           checklist.Remove(item);
                                       }
                                   }
                                   #endregion
                                   if (inTimeList.Count <= 0)
                                   {
                                       rpt.ShowInReport = true;
                                       rpt.IsAbsenteeism = true;
                                       continue;
                                   }
                               }
                               eventTime = inTimeList.Min(ent => ent.AddTime);
                               rpt.InTime = eventTime.ToString("yyyy-MM-dd HH:mm:ss");
                               var lateMinute = (eventTime - ptime).TotalMinutes;
                               if (lateMinute >= 1)
                               {
                                   rpt.LateMinute = (int)lateMinute;
                                   rpt.ShowInReport = true;
                               }
                           }
                           else if (rule.CheckType == 2)//下班
                           {
                               var outTimeList = checklist.Where(ent => ent.AddTime > btime && ent.AddTime <= etime).ToList();
                               if (outTimeList.Count() <= 0)
                               {
                                   #region 向后寻找
                                   if (index == 0 && ShiftList.Count >= 2)
                                   {
                                       var nextTime = rule.GetTime(day, ShiftList[1].InRule.CheckPoint);
                                       var lista = checklist.Where(ent => ent.AddTime > btime && ent.AddTime <= nextTime).ToList();
                                       if (lista.Count >= 2)
                                       {
                                           var item = lista.OrderBy(ent => ent.AddTime).ToList().FirstOrDefault();
                                           outTimeList.Add(item);
                                           checklist.Remove(item);
                                       }
                                   }
                                   else if (index == 1 && ShiftList.Count >= 3)
                                   {
                                       var nextTime = rule.GetTime(day, ShiftList[2].InRule.CheckPoint);
                                       var lista = checklist.Where(ent => ent.AddTime > btime && ent.AddTime <= nextTime).ToList();
                                       if (lista.Count >= 2)
                                       {
                                           var item = lista.OrderBy(ent => ent.AddTime).ToList().FirstOrDefault();
                                           outTimeList.Add(item);
                                           checklist.Remove(item);
                                       }
                                   }
                                   #endregion
                                   if (outTimeList.Count <= 0)
                                   {
                                       rpt.ShowInReport = true;
                                       rpt.IsAbsenteeism = true;
                                       continue;
                                   }
                               }
                               eventTime = outTimeList.Min(ent => ent.AddTime);
                               rpt.OutTime = eventTime.ToString("yyyy-MM-dd HH:mm:ss");
                               var leaveEarlyMinute = (ptime - eventTime).TotalMinutes;
                               if (leaveEarlyMinute >= 1)
                               {
                                   rpt.LeaveEarlyMinute = (int)leaveEarlyMinute;
                                   rpt.ShowInReport = true;
                               }
                           }
                           #endregion

                       }
                    #endregion

                    }
                }
            }
            var list = rpts.Where(ent => ent.ShowInReport == true).ToList();
            dataGridView1.DataSource = list;
            Rpt = list;
        }
        private void button1_Click(object sender, EventArgs e)
        {
            LoadData();
            ExtractEmpAndDate();
            Analyze();
            button1.Enabled = false;
            //saveFileDialog1.FileName = DateTime.Now.ToString("考勤分析yyMMddHHmmss") + ".xls";
            //if (saveFileDialog1.ShowDialog() == System.Windows.Forms.DialogResult.Cancel)
            //{
            //    return;
            //}
            //if (string.IsNullOrWhiteSpace(saveFileDialog1.FileName)) return;
            //var filename = saveFileDialog1.FileName;
            //NPOIUtil.Export(Rpt.ToDataTable(), "打卡记录分析", filename);
            
        }
    }
}

数据结构

   class CheckingRule
    {
        /// <summary>
        /// 格式00:00
        /// </summary>
        public String CheckPoint { get; set; }
        public String RangBegin { get; set; }
        public String RangEnd { get; set; }
        /// <summary>
        /// 上班:1
        /// 下班:2
        /// 
        /// </summary>
        public int CheckType { get; set; }
        public String Title { get; set; }
       
        public DateTime GetTime(DateTime day,string v)
        {
            var arr= v.Split(":".ToCharArray());
            return day.Date.AddHours(double.Parse(arr[0])).AddMinutes(double.Parse(arr[1]));
        }
    }

    class CheckingRstInfo
    {
        public String Name { get; set; }
        public String EmpNo { get; set; }
        public String Dept { get; set; }
        public DateTime Date { get; set; }
        public String InTime { get; set; }
        public String OutTime { get; set; }
        public bool? IsAbsenteeism { get; set; }
        public int LateMinute { get; set; }
        public int LeaveEarlyMinute { get; set; }
        public bool? ShowInReport { get; set; }
        public String RuleTitle { get; set; }
    }

using System.Data;
using System.Collections.Generic;
using System.Reflection;
using System;
using System.Collections;
namespace F.Studio.Util
{
    public static class DataTableExtensions
    {
        /// <summary> 
        /// 转化一个DataTable 
        /// </summary> 
        /// <typeparam name="T"></typeparam> 
        /// <param name="list"></param> 
        /// <returns></returns> 
        public static DataTable ToDataTable<T>(this IEnumerable<T> list, params string[] tableName)
        {
            //创建属性的集合 
            List<PropertyInfo> pList = new List<PropertyInfo>();
            //获得反射的入口 
            Type type = typeof(T);
            string tname = "Table1";
            if (tableName.Length >= 1)
            {
                tname = tableName[0];
            }
            DataTable dt = new DataTable(tname);
            //把所有的public属性加入到集合 并添加DataTable的列 
            Array.ForEach<PropertyInfo>(type.GetProperties(), p =>
            {
                pList.Add(p);
                var theType = p.PropertyType;
                //处理可空类型
                if (theType.IsGenericType && theType.GetGenericTypeDefinition().Equals(typeof(Nullable<>)))
                {
                    dt.Columns.Add(p.Name, Nullable.GetUnderlyingType(theType));
                }
                else
                {
                    dt.Columns.Add(p.Name, theType);
                }
            });
            foreach (var item in list)
            {
                //创建一个DataRow实例 
                DataRow row = dt.NewRow();
                //给row 赋值 
                pList.ForEach(p =>
                {
                    var v = p.GetValue(item, null);
                    row[p.Name] = v == null ? DBNull.Value : v;

                });
                //加入到DataTable 
                dt.Rows.Add(row);
            }
            return dt;
        }
    }
}

    public class CheckingInfo
    {
        public CheckingInfo()
        {
            DelFlag = false;
        }
        public String Dept { get; set; }
        public String Name { get; set; }
        public DateTime AddTime { get; set; }
        public DateTime RawAddTime { get; set; }
        public String MachineNo { get; set; }
        public String EmpNo { get; set; }
        public String CheckType { get; set; }
        public bool DelFlag { get; set; }
    }