public static List <MODEL.PaySlip> selectPaySlip(string UserId)
{
int LateMoney = 0;
int AdvanceMoney = 0;
int Sa_TotalSalary = 5000;
DataTable dt = DBHelper.GetSelect("select * from AttendanceSheet where (AttendanceType = 1 or AttendanceType = 2 ) and UserId =" + UserId);
for (int i = 0; i < dt.Rows.Count; i++)
{
if (dt.Rows[i]["AttendanceType"].ToString() == "1")
{
LateMoney++;
Sa_TotalSalary = Sa_TotalSalary - 500;
}
else
{
AdvanceMoney++;
Sa_TotalSalary = Sa_TotalSalary - 500;
}
}
MODEL.PaySlip paySlip = new MODEL.PaySlip();
paySlip.LateMoney = LateMoney.ToString();
paySlip.AdvanceMoney = AdvanceMoney.ToString();
paySlip.TotalSalary = Sa_TotalSalary.ToString();
List <MODEL.PaySlip> list = new List <MODEL.PaySlip>();
list.Add(paySlip);
return(list);
}
/// <summary>
///查询字典表
/// </summary>
/// <param name="C_Category"></param>
/// <returns></returns>
public List <MODEL.CategoryItems> selectCategoryItems(string C_Category)
{
string sql = "select CI_ID,CI_Name from CategoryItems where C_Category = @C_Category";
SqlParameter[] sqlpar =
{
new SqlParameter("C_Category", C_Category)
};
DataTable dt = DBHelper.GetSelect(sql, sqlpar);
List <MODEL.CategoryItems> list = new List <MODEL.CategoryItems>();
if (dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
MODEL.CategoryItems categoryItems = new MODEL.CategoryItems();
categoryItems.CI_ID = dt.Rows[i]["CI_ID"].ToString();
categoryItems.CI_Name = dt.Rows[i]["CI_Name"].ToString();
list.Add(categoryItems);
}
}
return(list);
}
/// <summary>
/// 查询员工信息
/// </summary>
/// <returns></returns>
public DataTable selectUserInfo(string UserName, string DepartmentID, int offset, int pageSize, out string count)
{
string where = string.Empty;
if (!string.IsNullOrEmpty(UserName))
{
where += " and UserName like '%" + UserName + "%' ";
}
if (DepartmentID != "0")
{
where += "and UserInfo.DepartmentID = " + DepartmentID;
}
string sql = "select * from (select ROW_NUMBER() over(order by UserID) rowindex,UserInfo.* , Department.DepartmentName from UserInfo inner join Department on UserInfo.DepartmentID=Department.DepartmentID where 1 = 1 " + where + ") c where rowindex between " + (offset + 1) + " and " + (pageSize + offset);
DataTable dt = DBHelper.GetSelect(sql);
#region 查询条数
string sqlCount = "select count(UserID) from UserInfo where 1=1 " + where;
count = DBHelper.GetSelect(sqlCount).Rows[0][0].ToString();
#endregion
return(dt);
}
/// <summary>
/// 查询审批列表
/// </summary>
/// <param name="offset"></param>
/// <param name="pageSize"></param>
/// <param name="LeaveStartTime"></param>
/// <param name="LeaveEndTime"></param>
/// <param name="user"></param>
/// <param name="count"></param>
/// <returns></returns>
public DataTable selectAuditLeave(int offset, int pageSize, string LeaveStartTime, string LeaveEndTime, MODEL.UserInfo user, out string count)
{
string where = "";
if (user.RoleID == "2")
{
where += " and DepartmentalAudit = 3 and UserInfo.DepartmentID = " + user.DepartmentID;
}
else if (user.RoleID == "4")
{
where += " and (DepartmentalAudit = 1 or DepartmentalAudit = 0) and GeneralManagerAudit = 3 and RoleID != " + user.RoleID;
}
else if (user.RoleID == "5")
{
where += " and ManagerAudit =3 and (GeneralManagerAudit = 1 or GeneralManagerAudit =0) ";
}
string sql = "select * from (select ROW_NUMBER() over(order by Leave.LeaveTime desc) rowindex,Leave.*,UserInfo.UserName,UserInfo.UserTel,Department.DepartmentName from Leave inner join UserInfo on Leave.UserID = UserInfo.UserID inner join Department on UserInfo.DepartmentID=Department.DepartmentID inner join OvertineCheck on Leave.LeaveID = OvertineCheck.LeaveID where 1 = 1 " + where + ") a where rowindex between " + (offset + 1) + " and " + (pageSize + offset) + " order by LeaveTime desc";
#region 查询行数
string sql2 = " select COUNT(Leave.LeaveID) from Leave inner join UserInfo on Leave.UserID = UserInfo.UserID inner join OvertineCheck on Leave.LeaveID = OvertineCheck.LeaveID where 1=1 " + where;
count = (DBHelper.GetSelect(sql2)).Rows[0][0].ToString();
#endregion
return(DBHelper.GetSelect(sql));
}
/// <summary>
/// 登录
/// </summary>
/// <param name="userInfo"></param>
/// <returns></returns>
public DataTable Login(UserInfo userInfo)
{
String sql = "select * from UserInfo where LoginName=@LoginName and LoginPwd=@LoginPwd";
SqlParameter[] sqlPar =
{
new SqlParameter("LoginName", userInfo.LoginName),
new SqlParameter("LoginPwd", userInfo.LoginPwd)
};
return(DBHelper.GetSelect(sql, sqlPar));
}
/// <summary>
/// 修改审核状态
/// </summary>
/// <param name="LeaveID"></param>
/// <returns></returns>
public void updateAuditLeave(string LeaveID)
{
DataTable dt = DBHelper.GetSelect("select * from OvertineCheck inner join UserInfo on OvertineCheck.UserID = UserInfo.UserID where LeaveID =" + LeaveID);
string ManagerAudit = dt.Rows[0]["ManagerAudit"].ToString(); //总经理审批结果
string GeneralManagerAudit = dt.Rows[0]["GeneralManagerAudit"].ToString(); //人事经理审批结果
string DepartmentalAudit = dt.Rows[0]["DepartmentalAudit"].ToString(); //部门经理审批结果
string RoleID = dt.Rows[0]["RoleID"].ToString();
string DepartmentID = dt.Rows[0]["DepartmentID"].ToString();
string Audit = "3";
if (RoleID == "1" && DepartmentID != "10") //非人事部普通员工
{
if ((DepartmentalAudit == "1" && GeneralManagerAudit == "1" && ManagerAudit == "1") || (DepartmentalAudit == "1" && GeneralManagerAudit == "1" && ManagerAudit == "0") || (DepartmentalAudit == "1" && GeneralManagerAudit == "0" && ManagerAudit == "0"))
{
Audit = "1";
}
else if (DepartmentalAudit == "2" || GeneralManagerAudit == "2" || ManagerAudit == "2")
{
Audit = "2";
}
}
else if ((RoleID == "1" && DepartmentID == "10") || RoleID == "3" || RoleID == "2") //人事部普通员工部门经理
{
if ((DepartmentalAudit == "0" && GeneralManagerAudit == "1" && ManagerAudit == "1") || (DepartmentalAudit == "0" && GeneralManagerAudit == "1" && ManagerAudit == "0"))
{
Audit = "1";
}
else if (DepartmentalAudit == "2" || GeneralManagerAudit == "2" || ManagerAudit == "2")
{
Audit = "2";
}
}
else if (RoleID == "4") //人事经理
{
if (DepartmentalAudit == "0" && GeneralManagerAudit == "0" && ManagerAudit == "1")
{
Audit = "1";
}
else if (DepartmentalAudit == "2" || GeneralManagerAudit == "2" || ManagerAudit == "2")
{
Audit = "2";
}
}
if (Audit != "3")
{
updateAudit(LeaveID, Audit);
}
}
/// <summary>
/// 查询今天是否打卡
/// </summary>
/// <param name="AttendanceStartTime"></param>
/// <param name="UserID"></param>
/// <returns></returns>
public bool selectIs(string AttendanceStartTime, string UserID)
{
string sql = "select * from AttendanceSheet where AttendanceStartTime between '" + AttendanceStartTime + "' and '" + AttendanceStartTime + " 23:59:59' and UserID = '" + UserID + "'";
DataTable dt = DBHelper.GetSelect(sql);
if (dt.Rows.Count > 0)
{
return(false);
}
else
{
return(true);
}
}
/// <summary>
/// 下拉框数据
/// </summary>
/// <returns></returns>
public List <Department> selectDepartment()
{
string sql = "select distinct DepartmentID,DepartmentName from Department ";
DataTable dt = DBHelper.GetSelect(sql);
List <Department> list = new List <Department>();
for (int i = 0; i < dt.Rows.Count; i++)
{
Department department = new Department();
department.DepartmentID = dt.Rows[i]["DepartmentID"].ToString();
department.DepartmentName = dt.Rows[i]["DepartmentName"].ToString();
list.Add(department);
}
return(list);
}
/// <summary>
/// 查询部门
/// </summary>
/// <returns></returns>
public List <Department> selectDepartment(string DepartmentName, int pageSize, int offset, out string count)
{
string where = "";
if (!string.IsNullOrEmpty(DepartmentName))
{
where = " and DepartmentName like '%" + DepartmentName + "%' ";
}
string sql = "select * from (select ROW_NUMBER() over(order by DepartmentId) rowindex,* from Department where 1 = 1 and isDel != '0' " + where + " ) c where rowindex between " + (offset + 1) + " and " + (pageSize + offset) + " ";
DataTable dt = DBHelper.GetSelect(sql);
List <Department> list = new List <Department>();
if (dt.Rows.Count != 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
Department department = new Department();
department.DepartmentID = dt.Rows[i]["DepartmentID"].ToString();
department.DepartmentName = dt.Rows[i]["DepartmentName"].ToString();
department.DepartmentRemarks = dt.Rows[i]["DepartmentRemarks"].ToString();
list.Add(department);
}
}
#region 查询条数
string sqlCount = "select COUNT(DepartmentID) from Department where 1 = 1 and isDel != '0' ";
if (!string.IsNullOrEmpty(DepartmentName))
{
sqlCount = sqlCount + " and DepartmentName like '%" + DepartmentName + "%'";
}
DataTable dtcount = DBHelper.GetSelect(sqlCount);
count = dtcount.Rows[0][0].ToString();
#endregion
//Department department = new Department();
//department.DepartmentID = dt.Rows[0]["DepartmentID"].ToString();
//department.DepartmentName = dt.Rows[0]["DepartmentName"].ToString();
//department.DepartmentRemarks = dt.Rows[0]["DepartmentRemarks"].ToString();
return(list);
}
/// <summary>
/// 查询角色
/// </summary>
/// <returns></returns>
public List <Role> selectRole()
{
string sql = "select * from Role";
DataTable dt = DBHelper.GetSelect(sql);
List <Role> list = new List <Role>();
for (int i = 0; i < dt.Rows.Count; i++)
{
Role role = new Role();
role.RoleID = dt.Rows[i]["RoleID"].ToString();
role.RoleName = dt.Rows[i]["RoleName"].ToString();
list.Add(role);
}
return(list);
}
/// <summary>
/// 请假申请
/// </summary>
/// <param name="leave"></param>
/// <returns></returns>
public string ApplicationLeave(MODEL.Leave leave)
{
string sql = "insert into Leave(UserID,LeaveTime,LeaveStartTime,LeaveEndTime,LeaveHalfDay,LeaveDays,LeaveReason,LeaveState) values(@UserID,GETDATE(),@LeaveStartTime,@LeaveEndTime,@LeaveHalfDay,@LeaveDays,@LeaveReason,3);select @@identity;";
SqlParameter[] sqlpar =
{
new SqlParameter("UserID", leave.UserID),
new SqlParameter("LeaveStartTime", leave.LeaveStartTime),
new SqlParameter("LeaveEndTime", leave.LeaveEndTime),
new SqlParameter("LeaveHalfDay", leave.LeaveHalfDay),
new SqlParameter("LeaveDays", leave.LeaveDays),
new SqlParameter("LeaveReason", leave.LeaveReason),
};
DataTable dt = DBHelper.GetSelect(sql, sqlpar);
return(dt.Rows[0][0].ToString());
}
/// <summary>
/// 查询是否有重复数据
/// </summary>
/// <param name="DepartmentName"></param>
/// <returns></returns>
public bool selectRepeatDepartment(string DepartmentName)
{
string sql = "select * from Department where DepartmentName =@DepartmentName ";
SqlParameter[] sqlpar =
{
new SqlParameter("DepartmentName", DepartmentName)
};
DataTable dt = DBHelper.GetSelect(sql, sqlpar);
if (dt.Rows.Count > 0)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// 查询请假记录
/// </summary>
/// <returns></returns>
public DataTable selectLeaveLoc(int offset, int pageSize, string LeaveStartTime, string LeaveEndTime, MODEL.UserInfo user, out string count)
{
string where = "";
if (!string.IsNullOrEmpty(user.UserName))
{
where += " and UserName like '%" + user.UserName + "%' ";
}
if (!string.IsNullOrEmpty(user.DepartmentID))
{
where += " and UserInfo.DepartmentID = " + user.DepartmentID;
}
string sql = "select * from (select ROW_NUMBER() over(order by Leave.LeaveTime desc) rowindex,Leave.*,UserInfo.UserName,UserInfo.UserTel,Department.DepartmentName from Leave inner join UserInfo on Leave.UserID = UserInfo.UserID inner join Department on UserInfo.DepartmentID=Department.DepartmentID where 1 = 1 " + where + " ) a where rowindex between " + (offset + 1) + " and " + (pageSize + offset) + " order by LeaveID desc";
#region 查询行数
string sql2 = " select COUNT(LeaveID) from Leave inner join UserInfo on Leave.UserID = UserInfo.UserID where LeaveState = 1 " + where;
count = (DBHelper.GetSelect(sql2)).Rows[0][0].ToString();
#endregion
return(DBHelper.GetSelect(sql));
}
/// <summary>
/// 查询请假申请
/// </summary>
/// <returns></returns>
public DataTable selectLeave(int offset, int pageSize, string LeaveStartTime, string LeaveEndTime, MODEL.UserInfo user, out string count)
{
string where = "";
where += " and Leave.UserID = " + user.UserID;
//if (user.RoleID == "1" || user.RoleID == "3")
//{
//}
//else if(user.RoleID == "2")
//{
// where += " and DepartmentID = " + user.DepartmentID;
//}
string sql = "select * from ( select ROW_NUMBER() over(order by LeaveID desc) rowindex ,* from (select Leave.*,UserInfo.UserName,UserInfo.UserTel from Leave inner join UserInfo on Leave.UserID = UserInfo.UserID where 1=1 " + where + ") a inner join (select * from CategoryItems where C_Category ='LeaveState') b on a.LeaveState = b.CI_ID) c inner join OvertineCheck on c.LeaveID = OvertineCheck.LeaveID where rowindex between " + (offset + 1) + " and " + (pageSize + offset) + " order by c.LeaveID desc";
#region 查询行数
string sql2 = " select COUNT(LeaveID) from Leave inner join UserInfo on Leave.UserID = UserInfo.UserID where 1=1 " + where;
count = (DBHelper.GetSelect(sql2)).Rows[0][0].ToString();
#endregion
return(DBHelper.GetSelect(sql));
}
/// <summary>
/// 查询考勤记录
/// </summary>
/// <returns></returns>
public DataTable selectRecord(string UserID, string d1, string d2)
{
string sql = "select * from AttendanceSheet where AttendanceStartTime between '" + d1 + "' and '" + d2 + "' and UserID =" + UserID;
return(DBHelper.GetSelect(sql));
}
