/*******************************************************************
* 功能：获取个人的出勤信息，普通员工
* 可用于查询
* 参数：empID 员工ID
* empName 员工姓名
* date 查询时间，nullable
* rate 出勤率
* 1全勤，0非全勤，null所有
* ****************************************************************/
public List <CheckInInfo> GetCheckInInfoList(string empID, string startDate, string endDate, string rate)
{
string sql = string.Format(@"SELECT * FROM T_CheckingInInfo WHERE F_EmpID={0} ", empID);
if (!string.IsNullOrEmpty(startDate) && !string.IsNullOrEmpty(endDate))
{
sql += string.Format(@" AND (F_Date>='{0}' AND F_Date<='{1}') ", startDate, endDate);
}
if (!string.IsNullOrEmpty(rate))
{
//MODIFYED
if (rate.Equals("1"))
{
sql += " AND F_CIIsNormal='1'";
}
else
{
sql += " AND F_CIIsNormal!='1'";
}
}
sql += string.Format(@" order by F_Date desc");
DataRowCollection rows = MrDBAccess.ExecuteDataSet(sql).Tables[0].Rows;
if (rows != null && rows.Count > 0)
{
return(getCheckInInfo(rows));
}
return(null);
}
/// ******************************************************************
/// <summary>
/// 获取本人信息， 普通员工
/// </summary>
/// <param name="empID">员工ID</param>
/// <returns>
/// 返回一个EmployeeInfo对象
/// </returns>
/// ******************************************************************
public EmployeeInfo GetEmployee(int empID)
{
string sql = string.Format(@"SELECT * FROM T_EmployeeInfo WHERE F_EmpID={0}", empID);
DataRowCollection row = MrDBAccess.ExecuteDataSet(sql).Tables[0].Rows;
return(getEmployeeInfo(row)[0]);
}
///*******************************************************************************
/// <summary>
/// 获取部门列表
/// </summary>
/// <param name="depName">部门名称</param>
/// <returns></returns>
///*******************************************************************************
private List <Department> getList(string strWhere)
{
string sql = string.Format(@"SELECT * FROM T_Department {0}", strWhere);
DataRowCollection rows = MrDBAccess.ExecuteDataSet(sql).Tables[0].Rows;
return(getDepartment(rows));
}
/// ******************************************************************
/// <summary>
/// 获取所有员工列表, 管理员、总经理
/// </summary>
/// <returns>
/// 返回所有员工的数据集合
/// </returns>
/// ******************************************************************
private List <EmployeeInfo> getList(string strWhere)
{
string sql = string.Format(@"SELECT * FROM T_EmployeeInfo {0}", strWhere);
DataRowCollection rows = MrDBAccess.ExecuteDataSet(sql).Tables[0].Rows;
return(getEmployeeInfo(rows));
}
/*************************************************************************
* 描述：获取月考勤信息
* 参数：empID 员工ID
* empName 员工姓名
* date 查询日期
* rate 出勤率，全勤1，非全勤0，所有null
* depID 部门ID
* **********************************************************************/
/*获取部门员工考勤月统计数据*/
public List <MonthAttendance> GetAttendanceStatistics(string date, string depID = null, string empName = null, string rate = null)
{
string sql = string.Format(@"SELECT * FROM T_AttendanceStatistics WHERE F_ASMonth={0} ", date);
if (!string.IsNullOrEmpty(depID))
{
sql += string.Format(@"AND F_DepID={0} ", depID);
}
if (!string.IsNullOrEmpty(empName))
{
sql += string.Format(@"AND F_EmpName LIKE '%{0}%' ", empName);
}
if (!string.IsNullOrEmpty(rate) && rate.Equals("1"))
{
sql += string.Format(@"AND F_ASLateNumber=0 AND F_ASLeaveEavlyNumber=0 AND F_ASAbsenteeismNumber=0 ");
}
else if (!string.IsNullOrEmpty(rate) && rate.Equals("0"))
{
sql += string.Format(@"AND F_ASLateNumber!=0 OR F_ASLeaveEavlyNumber!=0 OR F_ASAbsenteeismNumber!=0 ");
}
sql += string.Format(@"ORDER BY F_ASMonth");
DataRowCollection rows = MrDBAccess.ExecuteDataSet(sql).Tables[0].Rows;
if (rows != null && rows.Count > 0)
{
return(getAttendanceStatistics(rows));
}
return(null);
}
/*******************************************************************
*获取员工的出勤信息，部门经理、总经理和管理与昂
* 参数：depID 部门ID
* empName 员工姓名，nullable
* date 查询时间，nullable
* rate 出勤率 枚举类型
******************************************************************/
public List <CheckInInfo> GetCheckInInfoList(int depID = 0, string empNameLike = null, string startDate = null, string endDate = null, string rate = null)
{
string sql = "SELECT * FROM T_CheckingInInfo WHERE 1=1 ";
if (!string.IsNullOrEmpty(empNameLike))
{
sql += string.Format(@"AND F_EmpName LIKE '%{0}%' ", empNameLike);
}
if (!string.IsNullOrEmpty(startDate) && !string.IsNullOrEmpty(endDate))
{
sql += string.Format(@"AND (F_Date>='{0}' AND F_Date<='{1}') ", startDate, endDate);
}
if (!string.IsNullOrEmpty(rate) && rate == "1")
{
sql += string.Format(@"AND F_CIIsNormal='1' ");
}
if (!string.IsNullOrEmpty(rate) && rate == "0")
{
sql += string.Format(@"AND F_CIIsNormal!='1'");
}
if (depID != 0)
{
sql += string.Format(@"AND F_DepID={0} ", depID);
}
sql += string.Format(@" order by F_Date desc");
DataRowCollection rows = MrDBAccess.ExecuteDataSet(sql).Tables[0].Rows;
if (rows != null && rows.Count > 0)
{
return(getCheckInInfo(rows));
}
return(null);
}
/// <summary>
/// 根据depID取部门单条信息
/// </summary>
/// <param name="depID">部门id</param>
/// <returns></returns>
public Department GetInfo(int id)
{
string sql = string.Format(@"select * from T_Department where F_DepID={0}", id);
DataRowCollection rows = MrDBAccess.ExecuteDataSet(sql).Tables[0].Rows;
List <Department> deplist = getDepartment(rows);
return(deplist != null ? deplist[0] : null);
}
private CheckInInfo isCheckInInfoExisted(string date /*2015-11-23*/, int empID)
{
string sql = string.Format(@"SELECT * FROM T_CheckingInInfo WHERE F_Date='{0}' AND F_EmpID={1}", date, empID);
DataRowCollection rows = MrDBAccess.ExecuteDataSet(sql).Tables[0].Rows;
if (rows.Count == 0)
{
return(null);
}
return(getCheckInInfo(rows)[0]);
}
public bool hasSingOut(int empID, string appendDate)
{
string sql = string.Format(@"SELECT F_CIID FROM T_CheckingInInfo WHERE F_EmpID={0} AND F_Date='{1}' AND F_CIIsSignOut='1'", empID, appendDate);
int count = MrDBAccess.ExecuteDataSet(sql).Tables[0].Rows.Count;
if (count == 0)
{
return(false);
}
return(true);
}
public CheckInInfo GetCheckInInfo(int checkInInfoID)
{
string sql = string.Format(@"SELECT * FROM T_CheckingInInfo WHERE F_CIID={0}", checkInInfoID);
DataRowCollection row = MrDBAccess.ExecuteDataSet(sql).Tables[0].Rows;
if (row != null && row.Count > 0)
{
return(getCheckInInfo(row)[0]);
}
return(null);
}
/**********************************************************************
* 获取WorkDuration数据
* *******************************************************************/
public WorkDuration GetWorkDuration()
{
string sql = string.Format(@"select * from T_WorkDuration");
DataRowCollection rows = MrDBAccess.ExecuteDataSet(sql).Tables[0].Rows;
if (rows != null && rows.Count > 0)
{
return(getWorkDuration(rows));
}
return(null);
}
/// *********************************************************************
/// <summary>
/// 描述：判断该员工当月是否已经统计过
/// </summary>
/// <param name="asMonth">员工签到的月份</param>
/// <param name="empID">员工ID</param>
/// <returns>
/// 返回：存在;true
/// 不存在:false
/// </returns>
/// *********************************************************************
private bool isMonthAttendanceExist(int asMonth, int empID)
{
string sql = string.Format(@"SELECT * FROM T_AttendanceStatistics WHERE F_ASMonth={0} AND F_EmpID={1}", asMonth, empID);
DataRowCollection rows = MrDBAccess.ExecuteDataSet(sql).Tables[0].Rows;
if (rows.Count > 0)
{
return(true);
}
return(false);
}
/// *************************************************************
/// <summary>
/// 查询没有统计的所有签到信息
/// </summary>
/// <returns></returns>
/// *************************************************************
public List <CheckInInfo> GetCheckInInfoListNoCalculate()
{
string sql = string.Format(@"SELECT * FROM T_CheckingInInfo WHERE F_CIIsCalculate='0' AND F_CISignOutDate >= '1900-01-01'");
DataRowCollection rows = MrDBAccess.ExecuteDataSet(sql).Tables[0].Rows;
List <CheckInInfo> list = new List <CheckInInfo>();
if (rows != null && rows.Count > 0)
{
list = getCheckInInfo(rows);
return(list);
}
return(null);
}
public CheckInInfo GetCheckInfo(int empID, string date)
{
string sql = string.Format(@"SELECT * FROM T_CheckingInInfo WHERE F_EmpID={0} AND F_Date='{1}'",
empID, date);
DataRowCollection rows = MrDBAccess.ExecuteDataSet(sql).Tables[0].Rows;
List <CheckInInfo> list = getCheckInInfo(rows);
if (list != null && list.Count > 0)
{
return(list[0]);
}
return(null);
}
/// *************************************************************
/// <summary>
/// 描述：根据员工的ID和姓名获取员工信息
/// </summary>
/// <param name="empName">员工姓名</param>
/// <param name="empID">员工ID</param>
/// <returns>
/// 返回：存在，返回员工对象
/// 不存在，返回NULL
/// </returns>
/// *************************************************************
public CheckInInfo GetCurrentCheckInInfo(int empID)
{
//MODIFY
string sql = string.Format(@"SELECT * FROM T_CheckingInInfo WHERE F_EmpID={0} AND F_Date='{1}'",
empID, DateTime.Now.ToString("yyyy-MM-dd"));
DataRowCollection rows = MrDBAccess.ExecuteDataSet(sql).Tables[0].Rows;
List <CheckInInfo> list = getCheckInInfo(rows);
if (list != null && list.Count > 0)
{
return(list[0]);
}
return(null);
}
/*******************************************************************************
* 功能：删除部门
* 参数：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);
}
/*当前员工的考勤信息*/
public List <MonthAttendance> GetAttendanceStatistics(string empID, string date, string rate)
{
string sql = string.Format(@"SELECT * FROM T_AttendanceStatistics WHERE F_ASMonth={0} AND F_EmpID={1} ", date, empID);
if (!string.IsNullOrEmpty(rate) && rate.Equals("0"))
{
sql += "AND F_ASStandardDuration > F_ASRealityDuration";
}
else if (!string.IsNullOrEmpty(rate) && rate.Equals("1"))
{
sql += "AND F_ASStandardDuration = F_ASRealityDuration";
}
sql += string.Format(@"ORDER BY F_ASMonth");
DataRowCollection rows = MrDBAccess.ExecuteDataSet(sql).Tables[0].Rows;
if (rows != null && rows.Count > 0)
{
return(getAttendanceStatistics(rows));
}
return(null);
}
/*****************************************************************
* 功能：正常情况下
* 签到
* 签退
* 参数：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);
}