/// <summary>
/// 重置密码
/// </summary>
/// <returns>成功“1”，失败“0”</returns>
public int ResetPwd(string empID)
{
/*1. 重置员工密码*/
string sql = string.Format(@"UPDATE T_EmployeeInfo SET F_EmpPwd='{0}' WHERE F_EmpID={1}", DataBase.StringToMD5Hash("123456"), empID);
int res = MrDBAccess.ExecuteNonQuery(sql);
if (res == 0)
{
return(0);
}
return(1);
}
/// ******************************************************************
/// <summary>
/// 功能：删除员工
/// 参数：员工ID
/// 返回：成功“1”，失败“0”
/// </summary>
/// <param name="empID">员工ID</param>
/// <returns>成功返回“1”，否则返回“0”</returns>
/// ******************************************************************
public int DeleteEmployee(string empID)
{
/*1. 执行删除操作*/
MonthAttendanceService.Instance.Delete(int.Parse(empID));
CheckInInfoService.Instance.DeleteCheckInInfo(int.Parse(empID));
string sql = string.Format(@"DELETE FROM T_EmployeeInfo WHERE F_EmpID={0}", empID);
int res = MrDBAccess.ExecuteNonQuery(sql);
if (res == 0)
{
return(0);
}
return(1);
}
/*******************************************************************************
* 功能：删除部门
* 参数：depID 部门ID
* 返回：1 成功
* 0 失败
* *****************************************************************************/
public int DeleteDepartment(string id)
{
/*1. 删除部门后对其部门下的员工的处理*/
string strSql = string.Format(@"SELECT COUNT(1) as count FROM T_EmployeeInfo WHERE F_DepID={0}", id);
int count = int.Parse(MrDBAccess.ExecuteDataSet(strSql).Tables[0].Rows[0]["count"].ToString());
if (count <= 0)
{
string sql = string.Format(@"delete from T_Department where F_DepID={0}", id);
int res = MrDBAccess.ExecuteNonQuery(sql);
if (res != 0)
{
return(1);
}
}
return(0);
}
/// ******************************************************************
/// <summary>
/// 功能：修改密码
/// 参数：empID 员工ID
/// newPwd 新密码
/// oldPwd旧密码
/// 返回：成功“1”，失败“0”
/// </summary>
/// <param name="newPwd">新密码</param>
/// <param name="empID">员工ID</param>
/// <param name="oldPwd">旧密码</param>
/// <returns>成功“1”，失败“0”</returns>
/// ******************************************************************
public int ModifyPwd(string id, string newPwd, string oldPwd)
{
/*1. 修改密码*/
string confirmSql = string.Format("SELECT COUNT(1) FROM T_EmployeeInfo WHERE F_EmpID={0} AND F_EmpPwd='{1}'", id, oldPwd);
int count = MrDBAccess.ExecuteNonQuery(confirmSql);
if (count == 0)
{
return(0);
}
string sql = string.Format(@"UPDATE T_EmployeeInfo SET F_EmpPwd='{0}' WHERE F_EmpID={1}", newPwd, id);
int res = MrDBAccess.ExecuteNonQuery(sql);
if (res == 0)
{
return(0);
}
return(1);
}
/// <summary>
/// 描述：将没有统计过的签到信息设置为已经被统计过
/// </summary>
public void SetCheckInInfoIsCalculate(List <CheckInInfo> checkInInfoList)
{
string sql = "";
if (checkInInfoList != null && checkInInfoList.Count > 0)
{
for (int i = 0; i < checkInInfoList.Count; i++)
{
sql += string.Format(@"UPDATE T_CheckingInInfo SET F_CIIsCalculate='1' WHERE F_CIID={0};", checkInInfoList[i].CIID);
}
try
{
MrDBAccess.ExecuteNonQuery(sql);
}
catch (Exception e)
{
//do something
//TODO
}
}
}
/********************************************************************************
* 功能：更新部门表
* 参数：depID 部门ID，可以为null
* depName 部门名称
* 返回：1 更新成功
* 0 更新失败
* *****************************************************************************/
public int UpdateDepartment(string name, int id = 0)
{
string sql = "";
//判断部门名称是否存在
if (id == 0)
{
//添加
if (isDepartmentExist(name))
{
return(0);
}
sql = string.Format(@"insert into T_Department(F_DepName) values('{0}')", name.Replace("'", "''"));
return(MrDBAccess.ExecuteNonQuery(sql));
}
else
{
//修改
if (isDepartmentExist(name))
{
return(0);
}
int result = 0;
sql = string.Format(@"update T_Department set F_DepName='{0}' where F_DepID={1}", name.Replace("'", "''"), id);
string strSql = string.Format(@"update T_EmployeeInfo set F_DepName='{0}' where F_DepID={1}", name.Replace("'", "''"), id);//修改员工信息中的部门名称
try
{
MrDBAccess.BeginTransaction();
result = MrDBAccess.ExecuteNonQuery(sql);
MrDBAccess.ExecuteNonQuery(strSql);
MrDBAccess.CommitTransaction();
return(result);
}
catch (Exception)
{
MrDBAccess.RollbackTransaction();
}
return(result);
}
}
/// ********************************************************************
/// <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:*/ }
}
/// ******************************************************************
/// <summary>
/// 更新Employee数据表数据
/// </summary>
/// <param name="employee">员工对象</param>
/// <returns>
/// 更新成功“1”
/// 否则返回“0”
/// </returns>
/// ******************************************************************
public int Update(EmployeeInfo employee)
{
string sql = "";
/*1. empID为null，执行添加操作*/
if (string.IsNullOrEmpty(employee.EmpID))
{
sql = string.Format(@"INSERT INTO T_EmployeeInfo(F_EmpName, F_EmpRole, F_DepID, F_DepName, F_EmpAccount, F_EmpPwd, F_EmpCreateDate) VALUES('{0}','{1}',{2},'{3}','{4}','{5}','{6}')",
employee.EmpName, employee.EmpRole, employee.DepID, employee.DepName, employee.EmpAccount, employee.EmpPwd, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
}
/*2. empID不为null，执行修改操作*/
else
{
sql = string.Format(@"UPDATE T_EmployeeInfo SET F_EmpName='{0}',F_EmpRole='{1}',F_EmpAccount='{2}',F_DepID={3},F_DepName='{4}' WHERE F_EmpID={5}",
employee.EmpName, employee.EmpRole, employee.EmpAccount, employee.DepID, employee.DepName, employee.EmpID);
}
int res = MrDBAccess.ExecuteNonQuery(sql);
if (res == 0)
{
return(0);
}
return(1);
}
public void DeleteCheckInInfo(int empID)
{
string sql = string.Format(@"DELETE FROM T_CheckingInInfo WHERE F_EmpID={0}", empID);
MrDBAccess.ExecuteNonQuery(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);
}
/*****************************************************************
* 功能：正常情况下
* 签到
* 签退
* 参数：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
}
}
public void Delete(int empID)
{
string sql = string.Format(@"DELETE FROM T_AttendanceStatistics WHERE F_EmpID={0}", empID);
MrDBAccess.ExecuteNonQuery(sql);
}
/// <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();
}
}