首页 > 代码库 > 考勤报表生成

考勤报表生成

需要:根据原始打开记录生成考勤报表

配置:班次, 由开始时间,结束时间点, 开始时间区间,结束时间区间组成

一天多个班次的需要考虑班次之间的连续打卡情况(允许),两次打卡时间间隔需要大于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:\Net\HR_KQ\9月质量部.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);                    }    }}
View Code

数据结构

技术分享
   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]));        }    }
View Code
技术分享
    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; }    }
View Code
技术分享
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;        }    }}
View Code
技术分享
    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; }    }
View Code

 

考勤报表生成