统计员工年度,月度的加班和调休统计
根据需求,写了一个统计部门员工年度的加班与调休统计报表。 实现结果如下:页面代码如下所示:@model IEnumerable<lvElecCenter.Areas.HR.Models.LeaveAndOverTimeCountVM>@using Webdiyer.WebControls.Mvc;@{ViewBag.Title = "考勤查询-加班调休统计.
·
根据需求,写了一个统计部门员工年度的加班与调休统计报表。 实现结果如下:
页面代码如下所示:
@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;">
员工姓名:@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" onclick="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)
}
为武汉地区的开发者提供学习、交流和合作的平台。社区聚集了众多技术爱好者和专业人士,涵盖了多个领域,包括人工智能、大数据、云计算、区块链等。社区定期举办技术分享、培训和活动,为开发者提供更多的学习和交流机会。
更多推荐
已为社区贡献1条内容
所有评论(0)