统计员工年度,月度的加班和调休统计

版权声明:本文为博主原创文章,遵循 CC 4.0 by-sa 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/shenjqiang/article/details/84830011

根据需求,写了一个统计部门员工年度的加班与调休统计报表。  实现结果如下:

页面代码如下所示:

@model IEnumerable<lvElecCenter.Areas.HR.Models.LeaveAndOverTimeCountVM>
@using Webdiyer.WebControls.Mvc;
@{
    ViewBag.Title = "考勤查询-加班调休统计";
    Layout = "~/Views/Shared/_Layout_Index_Query.cshtml";
    ViewBag.ControllerName = "/HR/LeaveAndOverTimeCount";
}
@section SectionSearch{
    @using (Html.BeginForm())
    {
        <tr>
            <td colspan='29' style="background-color: #EDF5FE; height: 28px;">
                &nbsp;&nbsp;员工姓名:@Html.TextBox("filter_LIKES_UserName", Request["filter_LIKES_UserName"], new { @style = "width:100px;" })
                <input type="submit" value="" class="searchSubmit" />
                <input type="button" value="" class="Snap" οnclick="selectExcel('@Request.ApplicationPath/HR/Attendance/toExcel')"/>
            </td>
        </tr>
    }
}
<tr>
    <th rowspan='2' colspan='1'>
        @Html.LabelFor(m => m.First().Name)
    </th>
    <th rowspan='2' colspan='1'>
        @DateTime.Now.AddYears(-1).Year@Html.LabelFor(m => m.First().SurPlus)
    </th>
    <th rowspan='1' colspan='13'>
        加班/h
    </th>
    <th rowspan='1' colspan='13'>
        调休/h
    </th>
    <th rowspan='2' colspan='1'>
        @Html.LabelFor(m => m.First().TotalHours)
    </th>
</tr>
<tr>
    <th>
        @Html.LabelFor(m => m.First().January)
    </th>
    <th>
        @Html.LabelFor(m => m.First().February)
    </th>
    <th>@Html.LabelFor(m => m.First().March)
    </th>
    <th>@Html.LabelFor(m => m.First().April)
    </th>
    <th>@Html.LabelFor(m => m.First().May)
    </th>
    <th>@Html.LabelFor(m => m.First().June)
    </th>
    <th>@Html.LabelFor(m => m.First().July)
    </th>
    <th>@Html.LabelFor(m => m.First().August)
    </th>
    <th>@Html.LabelFor(m => m.First().September)
    </th>
    <th>@Html.LabelFor(m => m.First().October)
    </th>
    <th>@Html.LabelFor(m => m.First().November)
    </th>
    <th>@Html.LabelFor(m => m.First().December)
    </th>
    <th>@Html.LabelFor(m => m.First().CountOvertime)
    </th>
    <th>@Html.LabelFor(m => m.First().January1)
    </th>
    <th>@Html.LabelFor(m => m.First().February1)
    </th>
    <th>@Html.LabelFor(m => m.First().March1)
    </th>
    <th>@Html.LabelFor(m => m.First().April1)
    </th>
    <th>@Html.LabelFor(m => m.First().May1)
    </th>
    <th>@Html.LabelFor(m => m.First().June1)
    </th>
    <th>@Html.LabelFor(m => m.First().July1)
    </th>
    <th>@Html.LabelFor(m => m.First().August1)
    </th>
    <th>@Html.LabelFor(m => m.First().September1)
    </th>
    <th>@Html.LabelFor(m => m.First().October1)
    </th>
    <th>@Html.LabelFor(m => m.First().November1)
    </th>
    <th>@Html.LabelFor(m => m.First().December1)
    </th>
    <th>@Html.LabelFor(m => m.First().CountLeave)
    </th>
</tr>
@foreach (var item in Model)
{
    <tr>
        <td>@item.Name
        </td>
        <td>
            @item.SurPlus
        </td>
        <td>@item.January
        </td>
        <td>@item.February
        </td>
        <td>@item.March
        </td>
        <td>@item.April
        </td>
        <td>@item.May
        </td>
        <td>@item.June
        </td>
        <td>@item.July
        </td>
        <td>@item.August
        </td>
        <td>@item.September
        </td>
        <td>
            @item.October
        </td>
        <td>@item.November
        </td>
        <td>@item.December
        </td>
        <td>@item.CountOvertime
        </td>
        <td>@item.January1
        </td>
        <td>@item.February1
        </td>
        <td>@item.March1
        </td>
        <td>@item.April1
        </td>
        <td>@item.May1
        </td>
        <td>@item.June1
        </td>
        <td>@item.July1
        </td>
        <td>@item.August1
        </td>
        <td>@item.September
        </td>
        <td>@item.October1
        </td>
        <td>@item.November1
        </td>
        <td>@item.December1
        </td>
        <td>@item.CountLeave
        </td>
        <td>@item.TotalHours
        </td>
    </tr>
}
<script type="text/javascript">
    function selectExcel(url) {
        if (confirm('你确定要导出吗?')) {
            $("form").attr("action", url);
            $("form").submit();
        }
    }
</script>

后台代码如下所示:

 public class LeaveAndOverTimeCountController : Controller
    {
        //
        // GET: /LeaveAndOverTimeCount/
        [ActionFillters]
        public ActionResult Index(int? id)
        {
            UserInfo userinfo = Chint.Oa.Library.Comm.UserInfo.User_Info();
            List<PropertyFilter> filters = LinqUtil.BuildFilters(Request);
            string Year = DateTime.Now.Year.ToString();
            string EndTime = DateTime.Now.ToString("yyyy-MM-dd");
            string Employee = "";
            for (int i = 0; i < filters.Count; i++)
            {

                if (filters[i].PropertyNames[0].ToString().Equals("UserName"))
                {
                    Employee = filters[i].Value.ToString();
                }
            }
            const int defaultPageSize = 20;
            List<LeaveAndOverTimeCountVM> Attendance = GetLeaveAndOverTimeCount(Year, Employee);
            IQueryable<LeaveAndOverTimeCountVM> result = null;
            if (Attendance == null)
            {
                return View("Index");
            }
            else
            {
                result = Attendance.AsQueryable<LeaveAndOverTimeCountVM>();
                result = result.OrderBy(c => c.Name);
                PagedList<LeaveAndOverTimeCountVM> page = result.ToPagedList(id ?? 1, defaultPageSize);
                if (page.CurrentPageIndex > page.TotalPageCount)
                {
                    page = result.ToPagedList(page.TotalPageCount, defaultPageSize);
                }
                return View(page);
            }
        }

        /// <summary>
        /// 获取加班调休统计信息
        /// </summary>
        /// <param name="Year"></param>
        /// <param name="Employee"></param>
        /// <returns></returns>
        public List<LeaveAndOverTimeCountVM> GetLeaveAndOverTimeCount(string Year, string Employee)
        {
            DataTable dtEmployee = CommUtils.GetUserList("", Employee);
            DataTable dtOverTime = this.GetOverTime(DateTime.Now.Year.ToString());
            DataTable dtLeave = this.GetLeave(DateTime.Now.Year.ToString());
            var list = (from m in dtEmployee.AsEnumerable()
                        join
                            n in dtOverTime.AsEnumerable() on m.Field<decimal>("UserID") equals n.Field<Int32>("ApplyUserID") into mn
                        from x in mn.DefaultIfEmpty()
                        join o in dtLeave.AsEnumerable() on m.Field<decimal>("UserID") equals o.Field<Int32>("ApplyUserID") into mo
                        from y in mo.DefaultIfEmpty()
                        select new LeaveAndOverTimeCountVM
                        {
                            Guid = new Guid(),
                            Name = m.Field<string>("UNAME"),
                            SurPlus = "0",
                            January = x == null ? 0 : x.Field<decimal>("Janurary"),
                            February = x == null ? 0 : x.Field<decimal>("February"),
                            March = x == null ? 0 : x.Field<decimal>("March"),
                            April = x == null ? 0 : x.Field<decimal>("April"),
                            May = x == null ? 0 : x.Field<decimal>("May"),
                            June = x == null ? 0 : x.Field<decimal>("June"),
                            July = x == null ? 0 : x.Field<decimal>("July"),
                            August = x == null ? 0 : x.Field<decimal>("August"),
                            September = x == null ? 0 : x.Field<decimal>("September"),
                            October = x == null ? 0 : x.Field<decimal>("October"),
                            November = x == null ? 0 : x.Field<decimal>("November"),
                            December = x == null ? 0 : x.Field<decimal>("December"),
                            CountOvertime = x == null ? 0 : x.Field<decimal>("Count"),
                            January1 = y == null ? 0 : y.Field<decimal>("Janurary"),
                            February1 = y == null ? 0 : y.Field<decimal>("February"),
                            March1 = y == null ? 0 : y.Field<decimal>("March"),
                            April1 = y == null ? 0 : y.Field<decimal>("April"),
                            May1 = y == null ? 0 : y.Field<decimal>("May"),
                            June1 = y == null ? 0 : y.Field<decimal>("June"),
                            July1 = y == null ? 0 : y.Field<decimal>("July"),
                            August1 = y == null ? 0 : y.Field<decimal>("August"),
                            September1 = y == null ? 0 : y.Field<decimal>("September"),
                            October1 = y == null ? 0 : y.Field<decimal>("October"),
                            November1 = y == null ? 0 : y.Field<decimal>("November"),
                            December1 = y == null ? 0 : y.Field<decimal>("December"),
                            CountLeave = y == null ? 0 : y.Field<decimal>("Count"),
                            TotalHours = (x == null ? 0 : x.Field<decimal>("Count")) - (y == null ? 0 : y.Field<decimal>("Count")),
                        }).ToList();
            return list;
        }

        /// <summary>
        /// 获取加班信息
        /// </summary>
        /// <param name="Year"></param>
        /// <param name="Employee"></param>
        /// <returns></returns>
        public DataTable GetOverTime(string Year)
        {
            string strSQL = string.Format(@"SELECT a.applyUserID,
 Sum(CASE
             WHEN ( Datepart(month, CONVERT(VARCHAR(10), StartTime, 120)) ) = 1 THEN duration
             ELSE 0
           END) AS Janurary,
       Sum(CASE
             WHEN ( Datepart(month, CONVERT(VARCHAR(10), StartTime, 120)) ) = 2 THEN duration
             ELSE 0
           END) AS February,
       Sum(CASE
             WHEN ( Datepart(month, CONVERT(VARCHAR(10), StartTime, 120)) ) = 3 THEN duration
             ELSE 0
           END) AS March,
       Sum(CASE
             WHEN ( Datepart(month, CONVERT(VARCHAR(10), StartTime, 120)) ) = 4 THEN duration
             ELSE 0
           END) AS April,
       Sum(CASE
             WHEN ( Datepart(month, CONVERT(VARCHAR(10), StartTime, 120)) ) = 5 THEN duration
             ELSE 0
           END) AS May,
       Sum(CASE
             WHEN ( Datepart(month, CONVERT(VARCHAR(10), StartTime, 120)) ) = 6 THEN duration
             ELSE 0
           END) AS June,
       Sum(CASE
             WHEN ( Datepart(month, CONVERT(VARCHAR(10), StartTime, 120)) ) = 7 THEN duration
             ELSE 0
           END) AS July,
       Sum(CASE
             WHEN ( Datepart(month, CONVERT(VARCHAR(10), StartTime, 120)) ) = 8 THEN duration
             ELSE 0
           END) AS August,
       Sum(CASE
             WHEN ( Datepart(month, CONVERT(VARCHAR(10), StartTime, 120)) ) = 9 THEN duration
             ELSE 0
           END) AS September,
       Sum(CASE
             WHEN ( Datepart(month, CONVERT(VARCHAR(10), StartTime, 120)) ) = 10 THEN duration
             ELSE 0
           END) AS October,
       Sum(CASE
             WHEN ( Datepart(month, CONVERT(VARCHAR(10), StartTime, 120)) ) = 11 THEN duration
             ELSE 0
           END) AS November,
       Sum(CASE
             WHEN ( Datepart(month, CONVERT(VARCHAR(10), StartTime, 120)) ) = 12 THEN duration
             ELSE 0
           END) AS December,
       Sum(duration) AS Count
FROM HR_Overtime  a
 where 1=1 and Datepart(YEAR, CONVERT(VARCHAR(10), StartTime, 120))='{0}'
group by a.ApplyUserId
order by a.ApplyUserId asc", Year);
            DataTable dt = DbOaSQL.Query(strSQL).Tables[0];
            return dt;
        }

        /// <summary>
        /// 获取调休信息
        /// </summary>
        /// <param name="Year"></param>
        /// <param name="Employee"></param>
        /// <returns></returns>
        public DataTable GetLeave(string Year)
        {
            string strSQL = string.Format(@"SELECT a.applyUserID,
 Sum(CASE
             WHEN ( Datepart(month, CONVERT(VARCHAR(10), start, 120)) ) = 1 THEN LeaveHours
             ELSE 0
           END) AS Janurary,
       Sum(CASE
             WHEN ( Datepart(month, CONVERT(VARCHAR(10), start, 120)) ) = 2 THEN LeaveHours
             ELSE 0
           END) AS February,
       Sum(CASE
             WHEN ( Datepart(month, CONVERT(VARCHAR(10), start, 120)) ) = 3 THEN LeaveHours
             ELSE 0
           END) AS March,
       Sum(CASE
             WHEN ( Datepart(month, CONVERT(VARCHAR(10), start, 120)) ) = 4 THEN LeaveHours
             ELSE 0
           END) AS April,
       Sum(CASE
             WHEN ( Datepart(month, CONVERT(VARCHAR(10), start, 120)) ) = 5 THEN LeaveHours
             ELSE 0
           END) AS May,
       Sum(CASE
             WHEN ( Datepart(month, CONVERT(VARCHAR(10), start, 120)) ) = 6 THEN LeaveHours
             ELSE 0
           END) AS June,
       Sum(CASE
             WHEN ( Datepart(month, CONVERT(VARCHAR(10), start, 120)) ) = 7 THEN LeaveHours
             ELSE 0
           END) AS July,
       Sum(CASE
             WHEN ( Datepart(month, CONVERT(VARCHAR(10), start, 120)) ) = 8 THEN LeaveHours
             ELSE 0
           END) AS August,
       Sum(CASE
             WHEN ( Datepart(month, CONVERT(VARCHAR(10), start, 120)) ) = 9 THEN LeaveHours
             ELSE 0
           END) AS September,
       Sum(CASE
             WHEN ( Datepart(month, CONVERT(VARCHAR(10), start, 120)) ) = 10 THEN LeaveHours
             ELSE 0
           END) AS October,
       Sum(CASE
             WHEN ( Datepart(month, CONVERT(VARCHAR(10), start, 120)) ) = 11 THEN LeaveHours
             ELSE 0
           END) AS November,
       Sum(CASE
             WHEN ( Datepart(month, CONVERT(VARCHAR(10), start, 120)) ) = 12 THEN LeaveHours
             ELSE 0
           END) AS December,
       Sum(LeaveHours) AS December
FROM HR_Leave  a
 where 1=1 and Datepart(YEAR, CONVERT(VARCHAR(10), start, 120))='{0}'
 and LeaveType='调休'
group by a.ApplyUserId
order by a.ApplyUserId asc
", Year);
            DataTable dt = DbOaSQL.Query(strSQL).Tables[0];
            return dt;
        }



    }

Model定义如下:

    /// <summary>
    /// 加班调休月份统计表
    /// </summary>
    public class LeaveAndOverTimeCountVM
    {
        [Key]
        public Guid Guid { get; set; }
        [Display(Name = "姓名")]
        public string Name { get; set; } //姓名
        [Display(Name = "年剩余/h")]
        public string SurPlus { get; set; } //部门
        [Display(Name = "1月")]
        public decimal January { get; set; } //迟到/早退大于30分钟/次
        [Display(Name = "2月")]
        public decimal February { get; set; } //迟到/早退小于30分钟/次
        [Display(Name = "3月")]
        public decimal March { get; set; } //单次打卡(上班未打卡)/次
        [Display(Name = "4月")]
        public decimal April { get; set; } //单次打卡(下班未打卡)/次
        [Display(Name = "5月")]
        public decimal May { get; set; } //未打卡/次
        [Display(Name = "6月")]
        public decimal June { get; set; } //实际应出勤(H)
        [Display(Name = "7月")]
        public decimal July { get; set; } //出差(H)
        [Display(Name = "8月")]
        public decimal August { get; set; } //事假(H)
        [Display(Name = "9月")]
        public decimal September { get; set; } //病假(H)
        [Display(Name = "10月")]
        public decimal October { get; set; } //产前病事假(H)
        [Display(Name = "11月")]
        public decimal November { get; set; } //产假或护理假(H)
        [Display(Name = "12月")]
        public decimal December { get; set; } //婚假(H)
        [Display(Name = "小计")]
        public decimal CountOvertime { get; set; } //丧假(H)
        [Display(Name = "1月")]
        public decimal January1 { get; set; } //年假(H)
        [Display(Name = "2月")]
        public decimal February1 { get; set; } //公假(H)
        [Display(Name = "3月")]
        public decimal March1 { get; set; } //其他(H)
        [Display(Name = "4月")]
        public decimal April1 { get; set; } //调休(H)
        [Display(Name = "5月")]
        public decimal May1 { get; set; } //平时延长(H)
        [Display(Name = "6月")]
        public decimal June1 { get; set; } //休息日(H)
        [Display(Name = "7月")]
        public decimal July1 { get; set; } //法定节假日(H)
        [Display(Name = "8月")]
        public decimal August1 { get; set; } //加班折合调休时间(H)
        [Display(Name = "9月")]
        public decimal September1 { get; set; } //加班折合调休时间(H)
        [Display(Name = "10月")]
        public decimal October1 { get; set; } //加班折合调休时间(H)
        [Display(Name = "11月")]
        public decimal November1 { get; set; } //加班折合调休时间(H)
        [Display(Name = "12月")]
        public decimal December1 { get; set; } //加班折合调休时间(H)
        [Display(Name = "小计")]
        public decimal CountLeave { get; set; } //加班折合调休时间(H)
        [Display(Name = "合计剩余调休/h")]
        public decimal TotalHours { get; set; } //加班折合调休时间(H)
    }

 

展开阅读全文

统计所有员工节假日加班时间

12-26

按月份统计节假日和周末加班rn方法:sum(每个员工实际提交的工时—理论工时)rnrnrn这是自己写的查询按月查询每个员工实际工时的语句rnselect rn rn rn sr.id,rn sr.full_name,rn sum(PE.PRACTSUM/3600/8) act_days,-------实际工时rn month(PP.PRSTART) startrn rn FROM rn srm_resources srrn inner join PRTIMESHEET pt on sr.id = pt.prresourceidrn inner join PRTIMEPERIOD PP on Pt.PRTIMEPERIODID = PP.PRIDrn inner join pac_mnt_resources pmr on sr.unique_name = pmr.RESOURCE_CODErn inner join prtimeentry pe on pt.prid = pe.prtimesheetidrn inner join prtypecode ptc on pe.prtypecodeid = ptc.pridrn rnwhere rn pp.prstart >= '2012-01-01'rn and pp.prfinish <= '2013-01-01'rn and pt.prstatus = 4rn and (pmr.LOCATIONID = 'C1_BJ' or pmr.LOCATIONID = 'C1_sh')rn and ptc.prid <>5000007rn group by sr.id,sr.full_name,month(pp.prstart)rnrnrnrnrn然后新建了一个日历表Corporate_Calendar标记所有的假期(holiday)和工作日(workday),用于统计每个月的工作日和节假日rnrn例如1月份rn实际工时的计算:先统计每个员工的工时,再将同一月份的工时汇总rnrnrn理论工时的计算:分3种情况rn1. 正常情况:员工的date_of_hire(入职日期)<2012-01-01 and date_of_termination(离职日期) = null or date_of_termination >2012-01-31,理论工时的计算: select count(*) days from Corporate_Calendar where holiday = 0 and month(date) = 1rn2. 1月份入职:date_of_hire(入职日期)>2012-01-01, 理论工时的计算:select count(*) days from Corporate_Calendar where holiday = 0 and date between date_of_hire and 该月最后一天rn3. 3.1月份离职:date_of_termination <2012-01-31, 理论工时的计算:select count(*) days from Corporate_Calendar where holiday = 0 and date between 该月第一天 and date_of_termination rnrn有没有人告诉我大概怎么写sql或者存储过程,思路明白,不知道怎么下手rn 论坛

没有更多推荐了,返回首页