/// <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(); } }