public async Task <IActionResult> SetTimeTaken([FromRoute] int userDeviceId, [FromQuery] TimeSpan timeSpan, [FromQuery] DurationType type)
{
var device = _userDeviceRepository.GetAllUserDevices()
.FirstOrDefault(d => d.Id == userDeviceId);
if (device == null)
{
return(NotFound(new { message = "Device unknown" }));
}
if (timeSpan <= TimeSpan.Zero)
{
return(BadRequest(new { message = "TimeSpan must be set to a positive value" }));
}
var duration = new WorkDuration
{
Device = device,
UserDeviceId = userDeviceId,
TimeTaken = timeSpan,
Type = type,
};
duration = await _userDeviceRepository.AddOrUpdateWorkDurationAsync(duration);
return(Ok(device));
}
public void SettingValue()
{
WorkDuration workDuration = new WorkDuration();
workDuration.StartDateTimeInstant = DateTime.Now.AddDays(-1);
workDuration.EndDateTimeInstant = DateTime.Now;
}
/// *******************************************************
/// <summary>
/// 获取当前的工作时间设置
/// </summary>
/// <param name="context"></param>
/// *******************************************************
private void GetInfo(HttpContext context)
{
int code = 0;
string msg = "";
string json = "";
#region 验证管理员权限
if (!UsrAuth.IsAdminister(context.Session))
{
code = 0;
msg = "没有管理员权限";
json = "{\"Code\":\"" + code + "\",\"Msg\":\"" + msg + "\"}";
context.Response.Write(json);
return;
}
#endregion
WorkDuration workDuration = WorkDurationService.Instance.GetWorkDuration();
FeedBackMsg <WorkDuration> feedBack = new FeedBackMsg <WorkDuration>();
if (WorkDurationService.Instance != null)
{
feedBack.Code = 1;
feedBack.Msg = "当前的工作时间设置";
feedBack.Obj = workDuration;
}
json = ObjToJson.ToJson(feedBack);
context.Response.Write(json);
}
public void DefaultStartAndEndAreNull()
{
WorkDuration workDuration = new WorkDuration();
Assert.False(workDuration.StartDateTimeInstant.HasValue);
Assert.False(workDuration.EndDateTimeInstant.HasValue);
}
private string makeSql(DateTime signOutTime, DateTime signInTime, WorkDuration workDuration)
{
bool isLate = false;
bool isAbsent = false;
bool isLeaveEarly = false;
bool isNormal = false;
int late = 0, leaveEarly = 0, absent = 0, normal = 0;
string sql = "";
string asMonth = signInTime.ToString("yyyy-MM-dd");
//计算一天的工作时长
TimeSpan timeSpan = signOutTime - signInTime;
int workTime = timeSpan.Hours;
//出现旷工情况
if (signInTime > DateTime.Parse(asMonth + " " + workDuration.AbsentSignInTime) || signOutTime < DateTime.Parse(asMonth + " " + workDuration.AbsentSignOutTime))
{
isAbsent = true;
}
//迟到
if ((signInTime > DateTime.Parse(asMonth + " " + workDuration.NormalTime) && signInTime <= DateTime.Parse(asMonth + " " + workDuration.AbsentSignInTime) && workTime < 9) ||
(signInTime > DateTime.Parse(asMonth + " " + workDuration.NormalTime).AddMinutes(Double.Parse(workDuration.WDFloatTime)) && signInTime < DateTime.Parse(asMonth + " " + workDuration.AbsentSignInTime)))
{
isLate = true;
}
//早退
if (signOutTime < DateTime.Parse(asMonth + " " + workDuration.LeaveEarlyTime) && signOutTime >= DateTime.Parse(asMonth + " " + workDuration.AbsentSignInTime))
{
isLeaveEarly = true;
}
//正常
if (!isLate && !isAbsent && !isLeaveEarly)
{
isNormal = true;
}
if (isAbsent)
{
absent = 1;
}
if (isLate)
{
late = 1;
}
if (isLeaveEarly)
{
leaveEarly = 1;
}
if (isNormal)
{
normal = 1;
}
sql = string.Format(@"'{0}','{1}','{2}','{3}'", late, leaveEarly, absent, normal);
return(sql);
}
/**********************************************************
* 更新上班时间
* ********************************************************/
private void Update(HttpContext context)
{
int code = 0;
string msg = "";
string json = "";
WorkDuration wd = new WorkDuration();
#region 参数值赋给wd对象
wd.NormalTime = context.Request["normalTime"];
wd.AbsentSignInTime = context.Request["absentTimeSignIn"];
wd.AbsentSignOutTime = context.Request["absenTimeSignOut"];
wd.LeaveEarlyTime = context.Request["leaveEarlyTime"];
wd.WDFloatTime = context.Request["floatTime"];
wd.WDMonthDuration = int.Parse(context.Request["monthWorkDuration"]);
wd.WDSignInMonth = context.Request["month"];
#endregion
#region 验证操作权限
if (!UsrAuth.IsAdminister(context.Session))
{
code = 0;
msg = "无‘管理员’权限";
json = "{\"Code\":\"" + code + "\",\"Msg\":\"" + msg + "\"}";
context.Response.Write(json);
return;
}
#endregion
int res = WorkDurationService.Instance.UpdateWorkDuration(wd);
if (res == 0)
{
code = 0;
msg = "更新失败";
json = "{\"Code\":\"" + code + "\",\"Msg\":\"" + msg + "\"}";
}
else
{
code = 1;
msg = "更新成功";
json = "{\"Code\":\"" + code + "\",\"Msg\":\"" + msg + "\"}";
}
context.Response.Write(json);
}
/*********************************************************************
* 将Row转化为WorkDuration
* ******************************************************************/
private WorkDuration getWorkDuration(DataRowCollection rows)
{
if (rows != null && rows.Count > 0)
{
WorkDuration workDuration = new WorkDuration()
{
WDID = int.Parse(rows[0]["F_WDID"].ToString()),
WDMonthDuration = int.Parse(rows[0]["F_WDMonthDuration"].ToString()),
WDSignInMonth = rows[0]["F_WDSignInMonth"].ToString(),
NormalTime = rows[0]["F_NormalTime"].ToString(),
AbsentSignInTime = rows[0]["F_AbsentSignInTime"].ToString(),
AbsentSignOutTime = rows[0]["F_AbsentSignOutTime"].ToString(),
LeaveEarlyTime = rows[0]["F_LeaveEarlyTime"].ToString(),
WDFloatTime = rows[0]["F_WDFloatTime"].ToString(),
WDDayDuration = int.Parse(rows[0]["F_WDDayDurattion"].ToString())
};
return(workDuration);
}
return(null);
}
public void ThrowExcepionWhenStartDateTimeIsAfterEndDateTime()
{
var start = DateTime.Now;
var end = start.AddDays(-1);
Assert.Throws <StartDateTimeAfterEndDateTimeException>(() =>
{
WorkDuration workDuration = new WorkDuration
{
StartDateTimeInstant = start,
EndDateTimeInstant = end
};
});
Assert.Throws <StartDateTimeAfterEndDateTimeException>(() =>
{
WorkDuration workDuration = new WorkDuration();
workDuration.EndDateTimeInstant = end;
workDuration.StartDateTimeInstant = start;
});
}
/// ********************************************************************
/// <summary>
/// 更新工作时长表
/// </summary>
/// <param name="workDuration">WorkDuration对象</param>
/// <returns>更新成功“1”，否则“0”</returns>
/// ********************************************************************
public int UpdateWorkDuration(WorkDuration wd)
{
//更新WorkDuration数据表中的数据
WorkDuration workDuration = GetWorkDuration();
int oldWorkDayDuration = workDuration.WDDayDuration;
TimeSpan timeSpan = DateTime.Parse(wd.LeaveEarlyTime) - DateTime.Parse(wd.NormalTime);
int newWorkDayDuration = timeSpan.Hours;
string sql = string.Format(@"UPDATE T_WorkDuration SET F_WDMonthDuration={0}, F_WDSignInMonth='{1}', F_NormalTime='{2}', F_AbsentSignInTime='{3}',
F_AbsentSignOutTime='{4}', F_LeaveEarlyTime='{5}', F_WDFloatTime={6}, F_WDDayDurattion={7}",
wd.WDMonthDuration, wd.WDSignInMonth.Replace("'", "''"), wd.NormalTime.Replace("'", "''"), wd.AbsentSignInTime.Replace("'", "''"), wd.AbsentSignOutTime.Replace("'", "''"), wd.LeaveEarlyTime.Replace("'", "''"), wd.WDFloatTime.Replace("'", "''"), newWorkDayDuration);
int res = MrDBAccess.ExecuteNonQuery(sql);
if (oldWorkDayDuration != newWorkDayDuration || wd.WDMonthDuration != workDuration.WDMonthDuration)
{
MonthAttendanceService.Instance.UpdateWhenDurationChange(newWorkDayDuration - oldWorkDayDuration, wd.WDMonthDuration - workDuration.WDMonthDuration,
DataBase.SetInt(DateTime.Now.ToString()));
}
if (res == 0)
{
return(0);
}
return(1);
}
/// *********************************************************************
/// <summary>
/// 描述：当系统管理员修改了某个月份的工作时间时，将当月统计的所有信息
/// </summary>
/// *********************************************************************
public void UpdateWhenDurationChange(int changeTime, int changeDay, int asMonth)
{
//MODIFY
string sql = "";
WorkDuration workDuration = WorkDurationService.Instance.GetWorkDuration();
sql = string.Format(@"UPDATE T_AttendanceStatistics SET");
if (changeTime != 0)
{
sql += string.Format(@" F_ASStandardDuration=F_ASStandardDuration+({0})", workDuration.WDMonthDuration * changeTime);
}
if (changeDay != 0)
{
sql += string.Format(@" F_ASStandardDuration=F_ASStandardDuration+({0})", workDuration.WDDayDuration * changeDay);
}
sql += string.Format(@" WHERE F_ASMonth={0}", asMonth);
try
{
MrDBAccess.ExecuteNonQuery(sql);
}
catch (Exception e)
{ /*TODO:*/ }
}
private string makeInsertSql(CheckInInfo checkInInfo, WorkDuration workDuration, int asMonth)
{
string sql = "";
int normal = 0, late = 0, leaveEarly = 0, absent = 0;
if (checkInInfo.CIIsNormal)//正常
{
normal = 1;
}
if (checkInInfo.CIIsAbsenteeism)//缺席
{
absent = 1;
}
if (checkInInfo.CIIsLate)//迟到
{
late = 1;
}
if (checkInInfo.CIIsLeaveEavly)//早退
{
leaveEarly = 1;
}
sql = string.Format(@"VALUES({0},'{1}',{2},'{3}',{4},{5},{6},{7},{8},{9},{10},'{11}');",
checkInInfo.EmpID,
checkInInfo.EmpName,
checkInInfo.DepID,
checkInInfo.DepName,
asMonth,
workDuration.WDMonthDuration * workDuration.WDDayDuration,
checkInInfo.CIRealityWorkDuration,
late, //迟到次数
leaveEarly, //早退次数
absent, //缺席次数
normal, //正常次数
DateTime.Now.ToString("yyyy-MM-dd"));
return(sql);
}
/// **************************************************************
/// <summary>
/// 描述：为所有人补签到签退
/// </summary>
/// <param name="date">签到或者签退的日期</param>
/// <param name="signInTime">签到时间</param>
/// <param name="signOutTime">签退时间</param>
/// <param name="appendForEmpID">被补签到人的ID</param>
/// <param name="note">说明</param>
/// <returns></returns>
/// **************************************************************
public int UpdateCheckInInfoBySystem(string date, string signInTime, string signOutTime, string note, int appendEmpID, string appendEmpName, int appendForEmpID)
{
string sql = "";
DateTime signInDate = DateTime.Parse(date + " " + signInTime);
DateTime signOutDate = DateTime.Parse(date + " " + signOutTime);
TimeSpan timeSpan = signOutDate - signInDate;
WorkDuration workDuration = WorkDurationService.Instance.GetWorkDuration();
List <CheckInInfo> modifyedEmpList = new List <CheckInInfo>();
int workTime = timeSpan.Hours;
if (workTime > workDuration.WDDayDuration)
{
workTime = workDuration.WDDayDuration;
}
if (appendForEmpID == 0)
{
List <EmployeeInfo> empList = EmployeeService.Instance.GetList();
if (empList != null && empList.Count > 0)
{
for (int i = 0; i < empList.Count; i++)
{
//为所有人进行签到签退
//判断是否已经签过到/退
//MODIFY
CheckInInfo checkInfo = isCheckInInfoExisted(date, int.Parse(empList[i].EmpID));
if (checkInfo != null)
{
return(0);
}
#region ------------------1. 插入语句
//MODIFY
//SQL语句过长问题
sql = string.Format(@"Insert into T_CheckingInInfo(
F_EmpID,
F_EmpName,
F_DepID,
F_DepName,
F_CISignInDate,
F_CISignOutDate,
F_CIRealityWorkDuration,
F_AppendSignInPersonID,
F_AppendSignInPersonName,
F_AppendSignInPersonNote,
F_CIIsLate,
F_CIIsLeaveEarvly,
F_CIIsAbsenteeism,
F_CIIsNormal,
F_CICreateDate,
F_CIIsSignIn,
F_CIIsCalculate,
F_Date
)
values({0},'{1}',{2},'{3}','{4}','{5}',{6},{7},'{8}','{9}',{10}, '{11}',{12},{13},'{14}');",
empList[i].EmpID,
empList[i].EmpName,
empList[i].DepID,
empList[i].DepName,
signInDate,
signOutDate,
workTime,
appendEmpID,
appendEmpName,
note.Replace("'", "''"),
makeSql(signOutDate, signInDate, workDuration),
DateTime.Now.ToString("yyyy-MM-dd"),
"0",
"0",
date);
#endregion
int res = MrDBAccess.ExecuteNonQuery(sql);
if (res == 0)
{
return(0);
}
modifyedEmpList.Add(GetCheckInfo(int.Parse(empList[i].EmpID), date));
}
}
}
else
{
//为某个员工进行签到签退
#region ---------------------------sql语句
EmployeeInfo employeeInfo = EmployeeService.Instance.GetEmployee(appendForEmpID);
CheckInInfo checkInfo = isCheckInInfoExisted(date, int.Parse(employeeInfo.EmpID));
if (checkInfo != null)
{
return(0);
}
sql = string.Format(@"INSERT INTO T_CheckingInInfo(F_EmpID,
F_EmpName,
F_DepID,
F_DepName,
F_CISignInDate,
F_CISignOutDate,
F_CIRealityWorkDuration,
F_AppendSignInPersonID,
F_AppendSignInPersonName,
F_AppendSignInPersonNote,
F_CIIsLate,
F_CIIsLeaveEarvly,
F_CIIsAbsenteeism,
F_CIIsNormal,
F_CICreateDate,
F_CIIsSignIn,
F_CIIsCalculate,
F_Date)
VALUES({0},'{1}',{2},'{3}','{4}','{5}',{6},{7},'{8}','{9}',{10}, '{11}',{12},{13},'{14}')",
employeeInfo.EmpID,
employeeInfo.EmpName,
employeeInfo.DepID,
employeeInfo.DepName,
signInDate,
signOutDate,
workTime,
appendEmpID,
appendEmpName,
note.Replace("'", "''"),
makeSql(signOutDate, signInDate, workDuration),
DateTime.Now.ToString("yyyy-MM-dd"),
"0",
"0",
date);
#endregion
int res = MrDBAccess.ExecuteNonQuery(sql);
if (res == 0)
{
return(0);
}
modifyedEmpList.Add(GetCheckInfo(int.Parse(employeeInfo.EmpID), date));
}
//List<CheckInInfo> modifyedEmpList = GetCheckInInfoListNoCalculate();
MonthAttendanceService.Instance.UpdateForAppendSignEmp(modifyedEmpList, true);
return(1);
}
private string makeUpdateSql(DateTime signInTime, DateTime signOutTime, WorkDuration workDuration, string currentDate)
{
bool isLate = false;
bool isAbsent = false;
bool isLeaveEarly = false;
bool isNormal = false;
string returnSql = "";
//正常签到时间
DateTime normalSignInTime = DateTime.Parse(currentDate + " " + workDuration.NormalTime);
//缺席时间
DateTime absentTimeSignIn = DateTime.Parse(currentDate + " " + workDuration.AbsentSignInTime);
DateTime absentTimeSignOut = DateTime.Parse(currentDate + " " + workDuration.AbsentSignOutTime);
//正常下班时间
DateTime normalLeaveTime = DateTime.Parse(currentDate + " " + workDuration.LeaveEarlyTime);
//浮动时间，单位：分钟
int floatTime = int.Parse(workDuration.WDFloatTime);
//一天工作时长计算
TimeSpan ts = signOutTime - signInTime;
int workTime = 0;
if (ts.Minutes > 40)
{
workTime = ts.Hours + 1;
}
workTime = ts.Hours;
//出现旷工情况
if (signInTime > absentTimeSignIn || signOutTime < absentTimeSignOut)
{
isAbsent = true;
}
//迟到
if ((signInTime > normalSignInTime && signInTime <= absentTimeSignIn && workTime < 9) ||
(signInTime > normalSignInTime.AddMinutes(floatTime) && signInTime < absentTimeSignIn))
{
isLate = true;
}
//早退
if (signOutTime < normalLeaveTime && signOutTime >= absentTimeSignOut)
{
isLeaveEarly = true;
}
//正常
if (!isLate && !isAbsent && !isLeaveEarly)
{
isNormal = true;
}
int late = 0, leaveEarly = 0, absent = 0, normal = 0;
if (isNormal)
{
normal = 1;
}
if (isAbsent)
{
absent = 1;
}
if (isLate)
{
late = 1;
}
if (isLeaveEarly)
{
leaveEarly = 1;
}
returnSql = string.Format(@",F_CIIsLate='{0}', F_CIIsLeaveEarvly='{1}', F_CIIsAbsenteeism='{2}', F_CIIsNormal='{3}'", late, leaveEarly, absent, normal);
return(returnSql);
}
/*****************************************************************
* 功能：正常情况下
* 签到
* 签退
* 参数：checkInInfoID 被操作的考勤信息的ID（可选参数）
* empID 员工ID
* empName 员工姓名
* depID 员工所属部门
* depName 员工所属部门名称
* date 签到签退时间
* 返回：成功“1”，失败“0”
* **************************************************************/
public int UpdateCheckInInfo(int empID, string empName, int depID, string depName, string date, int checkInInfoID = 0)
{
//MODIFY
string sql = "";
/*是否有签到记录*/
string checkSql = string.Format(@"select * from T_CheckingInInfo where F_EmpID={0} and (F_CISignInDate>'{1}' and F_CISignInDate<'{2}') ",
empID, DateTime.Now.ToString("yyyy-MM-dd"), DateTime.Now.AddDays(1).ToString("yyyy-MM-dd"));
DataRowCollection rows = MrDBAccess.ExecuteDataSet(checkSql).Tables[0].Rows;
WorkDuration workDuration = WorkDurationService.Instance.GetWorkDuration();
/*1. checkInInfoID为0时，添加（签到）*/
if (checkInInfoID == 0)
{
//已经签过到，退出，返回0
if (rows != null && rows.Count > 0)
{
return(0);
}
//签到时间
DateTime signInTime = DateTime.Parse(date);
sql = string.Format(@"Insert into T_CheckingInInfo(
F_EmpID,
F_EmpName,
F_DepID,
F_DepName,
F_CISignInDate,
F_CIRealityWorkDuration,
F_AppendSignInPersonID,
F_AppendSignInPersonName,
F_AppendSignInPersonNote,
F_CIIsLate,
F_CIIsLeaveEarvly,
F_CIIsAbsenteeism,
F_CIIsNormal,
F_CICreateDate,
F_CIIsSignIn,
F_CIIsSignOut,
F_CIIsCalculate,
F_Date
)
values({0},'{1}',{2},'{3}','{4}',{5},{6},'{7}','{8}',{9},{10},{11},{12},'{13}',{14},{15},{16}, '{17}')",
empID,
empName,
depID,
depName,
signInTime.ToString("yyyy-MM-dd HH:mm:ss"),
0,
0,
"",
"",
"0 ", //F_CIIsLate
"0", //F_CIIsLeaveEavly
"0", //F_CIIsAbsenteeism
"0", //F_CIIsNormal
DateTime.Now.ToString("yyyy-MM-dd"),
"1",
"0",
"0",
signInTime.ToString("yyyy-MM-dd"));
}
/*2. checkInInfoID不为0时，修改（签退）*/
else
{
bool isLate = false;
bool isAbsent = false;
bool isLeaveEarly = false;
bool isNormal = false;
//还没签到，不能进行签退，正常情况下是不会执行句代码
if (rows == null && rows.Count == 0)
{
return(0);
}
//用户点击签退时时间
DateTime signOutTime = DateTime.Parse(date);
//签到时间
DateTime signInTime = DateTime.Parse(rows[0]["F_CISignInDate"].ToString());
//正常签到时间
DateTime normalSignInTime = DateTime.Parse(workDuration.NormalTime);
//缺席时间
DateTime absentTimeSignIn = DateTime.Parse(workDuration.AbsentSignInTime);
DateTime absentTimeSignOut = DateTime.Parse(workDuration.AbsentSignOutTime);
//正常下班时间
DateTime normalLeaveTime = DateTime.Parse(workDuration.LeaveEarlyTime);
//浮动时间，单位：分钟
int floatTime = int.Parse(workDuration.WDFloatTime);
//一天工作时长计算
TimeSpan ts = signOutTime - signInTime;
int workTime = 0;
if (ts.Minutes > 40)
{
workTime = ts.Hours + 1;
}
workTime = ts.Hours;
if (workTime > workDuration.WDDayDuration)
{
workTime = workDuration.WDDayDuration;
}
//出现旷工情况
if (signInTime > absentTimeSignIn || signOutTime < absentTimeSignOut)
{
isAbsent = true;
}
//迟到
if ((signInTime > normalSignInTime && signInTime < absentTimeSignIn && workTime < 9) ||
(signInTime > normalSignInTime.AddMinutes(floatTime) && signInTime < absentTimeSignIn))
{
isLate = true;
}
//早退
if (signOutTime < normalLeaveTime && signOutTime > absentTimeSignOut)
{
isLeaveEarly = true;
}
//正常
if (!isLate && !isAbsent && !isLeaveEarly)
{
isNormal = true;
}
int normalCount = 0, lateCount = 0, absentCount = 0, leaveEarlyCount = 0;
if (isNormal)
{
normalCount = 1;
}
if (isAbsent)
{
absentCount = 1;
}
if (isLate)
{
lateCount = 1;
}
if (isLeaveEarly)
{
leaveEarlyCount = 1;
}
sql = string.Format(@"UPDATE T_CheckingInInfo SET F_CISignOutDate='{0}',
F_CIRealityWorkDuration={1},F_CIIsLate='{2}', F_CIIsLeaveEarvly='{3}', F_CIIsAbsenteeism='{4}', F_CIIsNormal='{5}' WHERE F_CIID={6}",
signOutTime, workTime, lateCount, leaveEarlyCount, absentCount, normalCount, checkInInfoID);
}
int result = MrDBAccess.ExecuteNonQuery(sql);
//更新失败
if (result == 0)
{
return(0);
}
return(1);
}
/*****************************************************************
* 功能：补签到补签退
* 补签到
* 补签退
* 参数：checkInInfoID 被操作的考勤信息的ID（可选参数)
* empID 补签到（补签退）员工ID
* empName 补签到（补签退）员工姓名
* note 补签到（补签退）说明
* 返回：成功“1”，失败“0”
* **************************************************************/
public int UpdateCheckInInfoBySystem(string empID, string empName, int depID, string depName, string date, string note, string appendEmpID = null, string appendEmpName = null, int checkInInfoID = 0)
{
//MODIFY
string sql = "";
if (checkInInfoID == 0)
{
//补签到
//1. 如果已经被补签到过，更新操作
CheckInInfo oldCheckInfo = isCheckInInfoExisted(DateTime.Parse(date).ToString("yyyy-MM-dd"), int.Parse(empID));
WorkDuration workDuration = WorkDurationService.Instance.GetWorkDuration();
if (oldCheckInfo != null)
{
DateTime signInTime = DateTime.Parse(date);
DateTime signOutTime = DateTime.Parse(oldCheckInfo.CISignOutDate);
TimeSpan ts = signOutTime - signInTime;
int workTime = 0;
if (ts.Minutes > 40)
{
workTime = ts.Hours + 1;
}
workTime = ts.Hours;
if (workTime > workDuration.WDDayDuration)
{
workTime = workDuration.WDDayDuration;
}
sql = string.Format(@"UPDATE T_CheckingInInfo SET F_CISignInDate='{0}', F_AppendSignOutPersonID={1}, F_AppendSignOutPersonName='{2}',
F_AppendSignOutPersonNote='{3}', F_CIRealityWorkDuration={4}
{5} WHERE F_CIID={6}",
date, appendEmpID, appendEmpName, note.Replace("'", "''"), workTime,
makeUpdateSql(signInTime, signOutTime, workDuration, DateTime.Parse(date).ToString("yyyy-MM-dd")), oldCheckInfo.CIID);
int res = MrDBAccess.ExecuteNonQuery(sql);
if (res == 0)
{
return(0);
}
CheckInInfo newCheckInfo = GetCheckInInfo(oldCheckInfo.CIID);
doCalculate(newCheckInfo, oldCheckInfo);
return(1);
}
#region --------------------------------------------插入Sql语句
else
{
//2. 没有被补签到过，进行插入操作
sql = string.Format(@"Insert into T_CheckingInInfo(
F_EmpID,
F_EmpName,
F_DepID,
F_DepName,
F_CISignInDate,
F_CIRealityWorkDuration,
F_AppendSignInPersonID,
F_AppendSignInPersonName,
F_AppendSignInPersonNote,
F_CIIsLate,
F_CIIsLeaveEarvly,
F_CIIsAbsenteeism,
F_CIIsNormal,
F_CICreateDate,
F_CIIsSignIn,
F_CIIsSignOut,
F_CIIsCalculate,
F_Date
)
values({0},'{1}',{2},'{3}','{4}',{5},{6},'{7}','{8}',{9},{10},{11},{12},'{13}',{14},{15},{16}, '{17}')",
empID,
empName,
depID,
depName,
date,
0,
int.Parse(appendEmpID),
appendEmpName,
note.Replace("'", "''"),
"0 ", //F_CIIsLate
"0", //F_CIIsLeaveEavly
"0", //F_CIIsAbsenteeism
"0", //F_CIIsNormal
DateTime.Now.ToString("yyyy-MM-dd"),
"0",
"0",
"0",
DateTime.Parse(date).ToString("yyyy-MM-dd")); //设置补签为true
int res = MrDBAccess.ExecuteNonQuery(sql);
if (res == 0)
{
return(0);
}
return(1);
}
#endregion
}
else
{
#region ------------------------------------------补签退
CheckInInfo oldCheckInInfo = GetCheckInInfo(checkInInfoID);
DateTime signInTime = DateTime.Parse(oldCheckInInfo.CISignInDate);
DateTime signOutTime = DateTime.Parse(date);
//MODIFY 补签退时进行这一天的工作装填统计
TimeSpan ts = signOutTime - signInTime;
int workTime = 0;
if (ts.Minutes > 40)
{
workTime = ts.Hours + 1;
}
workTime = ts.Hours;
sql = string.Format(@"UPDATE T_CheckingInInfo SET F_CISignOutDate='{0}', F_AppendSignOutPersonID={1}, F_AppendSignOutPersonName='{2}',
F_AppendSignOutPersonNote='{3}', F_CIRealityWorkDuration={4}
{5} WHERE F_CIID={6}",
signOutTime, appendEmpID, appendEmpName, note, workTime,
makeUpdateSql(signInTime, signOutTime, WorkDurationService.Instance.GetWorkDuration(), DateTime.Parse(date).ToString("yyyy-MM-dd")), checkInInfoID);
int res = MrDBAccess.ExecuteNonQuery(sql);
#endregion
#region ---------------------------------------补签退后面的统计处理
//补签退失败
if (res == 0)
{
return(0);
}
else
{
//补签退成功
//对被补签的员工进行统计
CheckInInfo newCheckInfo = GetCheckInInfo(checkInInfoID);
doCalculate(newCheckInfo, oldCheckInInfo);
return(1);
}
#endregion
}
}
/// <summary>
/// 更新考勤统计数据表
/// </summary>
/// <returns>成功“1”，否则“0”</returns>
public void UpdateForAll()
{
string sql = "";
//所有没有统计的签到信息
List <CheckInInfo> checkInInfoListWithNoCal = CheckInInfoService.Instance.GetCheckInInfoListNoCalculate();
if (checkInInfoListWithNoCal != null && checkInInfoListWithNoCal.Count > 0)
{
for (int i = 0; i < checkInInfoListWithNoCal.Count; i++)
{
//统计月份
int asMonth = DataBase.SetInt(checkInInfoListWithNoCal[i].CISignInDate);
int empID = checkInInfoListWithNoCal[i].EmpID;
//1. 判断当月该员工是否已经存在统计记录
if (!isMonthAttendanceExist(asMonth, empID))
{
#region -----------------------------------1. 封装插入语句
WorkDuration workDuration = WorkDurationService.Instance.GetWorkDuration();
sql += string.Format(@"INSERT INTO T_AttendanceStatistics(F_EmpID,
F_EmpName,
F_DepID,
F_DepName,
F_ASMonth,
F_ASStandardDuration,
F_ASRealityDuration,
F_ASLateNumber,
F_ASLeaveEavlyNumber,
F_ASAbsenteeismNumber,
F_ASNormalNumber,
F_ASCreateDate) {0}", makeInsertSql(checkInInfoListWithNoCal[i], workDuration, asMonth));
#endregion
}
else
{
#region -----------------------------------2. 更新语句
int normalCount = 0, lateCount = 0, absentCount = 0, leaveEarlyCount = 0;
if (checkInInfoListWithNoCal[i].CIIsNormal)
{
normalCount = 1;
}
if (checkInInfoListWithNoCal[i].CIIsLeaveEavly)
{
leaveEarlyCount = 1;
}
if (checkInInfoListWithNoCal[i].CIIsLate)
{
lateCount = 1;
}
if (checkInInfoListWithNoCal[i].CIIsAbsenteeism)
{
absentCount = 1;
}
sql += string.Format(@"UPDATE T_AttendanceStatistics {0}", makeUpdateSql(checkInInfoListWithNoCal[i], normalCount, lateCount, absentCount, leaveEarlyCount));
#endregion
}
}
try
{
MrDBAccess.BeginTransaction();
MrDBAccess.ExecuteNonQuery(sql);
MrDBAccess.CommitTransaction();
//将统计过的签到信息设置为已经被统计过
CheckInInfoService.Instance.SetCheckInInfoIsCalculate(checkInInfoListWithNoCal);
}
catch (Exception e)
{
//事务回滚
MrDBAccess.RollbackTransaction();
//将事务异常信息存日志
//TODO
}
}
}
/// *********************************************************************
/// <summary>
/// 描述：为补签的人实时更新统计信息
/// </summary>
/// <param name="asMonth">补签月份</param>
/// <param name="empID">被补签员工的ID</param>
/// *********************************************************************
public void UpdateForAppendSignEmp(List <CheckInInfo> list, bool check, int normalCount = 0, int lateCoutn = 0, int absentCount = 0, int leaveEarlyCount = 0)
{
//MODIFY
string sql = "";
WorkDuration workDuration = WorkDurationService.Instance.GetWorkDuration();
//判断被补签人是否已经被统计过
//统计表中
int iLengthList = list.Count;
for (int i = 0; i < iLengthList; i++)
{
if (!isMonthAttendanceExist(DataBase.SetInt(list[i].CISignInDate), list[i].EmpID))
{
sql += string.Format(@"INSERT INTO T_AttendanceStatistics(F_EmpID,
F_EmpName,
F_DepID,
F_DepName,
F_ASMonth,
F_ASStandardDuration,
F_ASRealityDuration,
F_ASLateNumber,
F_ASLeaveEavlyNumber,
F_ASAbsenteeismNumber,
F_ASNormalNumber,
F_ASCreateDate) {0}", makeInsertSql(list[i], workDuration, DataBase.SetInt(list[i].CISignInDate)));
}
else
{
if (check)
{
if (list[i].CIIsLeaveEavly)
{
leaveEarlyCount = 1;
}
if (list[i].CIIsNormal)
{
normalCount = 1;
}
if (list[i].CIIsLate)
{
lateCoutn = 1;
}
if (list[i].CIIsAbsenteeism)
{
absentCount = 1;
}
}
sql += string.Format(@"UPDATE T_AttendanceStatistics {0}", makeUpdateSql(list[i], normalCount, lateCoutn, absentCount, leaveEarlyCount));
}
}
try
{
MrDBAccess.BeginTransaction();
MrDBAccess.ExecuteNonQuery(sql);
MrDBAccess.CommitTransaction();
//将统计过的签到信息设置为已经被统计
CheckInInfoService.Instance.SetCheckInInfoIsCalculate(list);
}
catch (Exception e)
{
MrDBAccess.RollbackTransaction();
}
}
