/********************************************************************************
* 功能：更新部门表
* 参数：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>
/// <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();
}
}