//===========================考勤管理==========================================
//考勤管理====查询考勤信息
public static DataTable SearchAttendInfo(int deptID)
{
//sql语句
string strSql = string.Format("select * from UserInfo inner join Department on UserInfo.DeptID=Department.DeptID where UserInfo.DeptID='{0}'", deptID);
return(DBHelper.ExecuteSelect(strSql));
}
/// <summary>
/// 根据日期得到该日期对应的考勤设置情况
/// </summary>
/// <param name="date"></param>
/// <returns></returns>
public static Model.AttendanceSetting GetAttendanceSettingByDate(DateTime date)
{
string sql = "select * from AttendanceSetting where Date=@Date";
SqlParameter[] para =
{
new SqlParameter("Date", date)
};
DataTable dt = DBHelper.ExecuteSelect(sql, para);
Model.AttendanceSetting a;
if (dt.Rows.Count > 0)
{
//表示有考勤设置
DataRow dr = dt.Rows[0];//得到第一行数据
a = new Model.AttendanceSetting();
a.Date = (DateTime)dr["Date"];
a.SettingID = (int)dr["SettingID"];
a.Status = (byte)dr["Status"];
}
else
{
//表示没有考勤设置
a = null;
}
return(a);
}
/// <summary>
/// 登录验证
/// </summary>
/// <param name="UserID">被验证的用户名</param>
/// <param name="Password">被验证的密码</param>
/// <returns></returns>
public static Model.UserInfo UserLogin(string UserID, string Password)
{
string sql = "select * from UserInfo where UserID=@UserID and Password=@Password";
SqlParameter[] para =
{
new SqlParameter("UserID", UserID),
new SqlParameter("Password", Password)
};
DataTable dt = DBHelper.ExecuteSelect(sql, para);
Model.UserInfo u;
if (dt.Rows.Count > 0)
{
u = new Model.UserInfo(); //表示用户名和密码正确
DataRow dr = dt.Rows[0];
u = new Model.UserInfo();
u.Cellphone = (string)dr["Cellphone"];
if (dr["DeptID"] != DBNull.Value)
{
u.DeptID = (int)dr["DeptID"];
}
u.Password = (string)dr["Password"];
u.UserID = (string)dr["UserID"];
u.UserName = (string)dr["UserName"];
u.UserType = (byte)dr["UserType"];
}
else
{
u = null;
}
return(u);
}
/// <summary>
/// 验证用户名和密码
/// </summary>
/// <param name="userID">用户名</param>
/// <param name="password">密码</param>
/// <returns></returns>
public static Model.UserInfo ValidateUserAndPwd(string userID, string password)
{
//string sql = string.Format("select * from UserInfo where UserID='{99}' and Password='******'",userID,password,,,,,);
//参数化sql语句 防止sql注入式攻击
string strSql = "select * from UserInfo where UserID=@UserID and Password=@Password";
SqlParameter[] para =
{
new SqlParameter("UserID", userID),
new SqlParameter("Password", password)
};
DataTable dt = DBHelper.ExecuteSelect(strSql, para);
Model.UserInfo u;
if (dt.Rows.Count > 0)//用户名和密码正确
{
u = new Model.UserInfo();
DataRow dr = dt.Rows[0]; //得到DataTable里面的第一行
u.Cellphone = (string)dr["Cellphone"];
if (dr["DeptID"] != DBNull.Value) //表示在表里面不是null值
{
u.DeptID = (int)dr["DeptID"];
}
u.Password = (string)dr["Password"];
u.UserID = (string)dr["UserID"];
u.UserName = (string)dr["UserName"];
u.UserType = (byte)dr["UserType"];
}
else
{
u = null;
}
return(u);
}
/// <summary>
/// 查询用户信息
/// </summary>
/// <param name="where"></param>
/// <returns></returns>
public static DataTable GetUserInfo(string where)
{
string sql = "select * from UserInfo left join Department on UserInfo.DeptID=Department.DeptID where 1=1" + where;
DataTable dt = DBHelper.ExecuteSelect(sql);
return(dt);
}
/// <summary>
/// 查询申请信息
/// </summary>
/// <param name="where"></param>
/// <returns></returns>
public static DataTable GetApproveInfo(string where)
{
string sql = "select b.UserName as ApplyName,c.UserName as ApproveName,*,(case Status when 0 then '待审批' when 1 then '归档' end ) NewStatus from Approve a " +
" left join UserInfo b on b.UserID = a.ApplyUser " +
" left join UserInfo c on c.UserID = a.ApproveUser where 1=1 " + where + " order by ApproveID desc ";
DataTable dt = DBHelper.ExecuteSelect(sql);
return(dt);
}
/// <summary>
/// 判断部门员工===》是否显示删除按钮
/// </summary>
/// <param name="u"></param>
/// <returns></returns>
public static DataTable JudgeDeptEmp(Model.DepartmentInfo u)
{
string sql = "select * from UserInfo inner join Department on UserInfo.DeptID=Department.DeptID where UserInfo.UserType=0 and Department.DeptName=@DeptName";
SqlParameter[] para =
{
new SqlParameter("DeptName", u.DeptName)
};
return(DBHelper.ExecuteSelect(sql, para));
}
/// <summary>
/// 获取请假单信息
/// </summary>
/// <param name="u"></param>
/// <returns></returns>
public static DataTable GetApproveInfo(Model.Approve u)
{
string strSql = "select * from Approve left join UserInfo on Approve.ApplyUser=UserInfo.UserID where ApproveID=@ApproveID";
SqlParameter[] para =
{
new SqlParameter("ApproveID", u.ApproveID),
};
return(DBHelper.ExecuteSelect(strSql, para));
}
public static DataTable GetMyAttendanceInfo1(string userID)
{
string sql = "select * from AttendanceInfo where UserID=@UserID ";
SqlParameter[] para =
{
new SqlParameter("UserID", userID),
};
return(DBHelper.ExecuteSelect(sql, para));
}
//查询部门
public static DataTable SearchDeptInfo(string deptName, int deptID)
{
string strSql = "select * from Department left join UserInfo on userinfo.userid=department.ManagerID where DeptName like '%" + deptName + "%'";
if (deptID != 0)
{
strSql += " and UserInfo.DeptID= " + deptID + "";
}
return(DBHelper.ExecuteSelect(strSql));
}
/// <summary>
/// 根据UserID、当天日期来得到打卡信息
/// </summary>
/// <param name="userID"></param>
/// <param name="date"></param>
/// <returns></returns>
public static DataTable GetMyAttendanceInfo(string userID, DateTime date)
{
string sql = "select min(FaceTime) FirstTime,max(FaceTime) LastTime from AttendanceInfo where UserID=@UserID and convert(varchar(10),FaceTime,112)=@Date";
SqlParameter[] para =
{
new SqlParameter("UserID", userID),
new SqlParameter("Date", date)
};
return(DBHelper.ExecuteSelect(sql, para));
}
//查询用户
public static DataTable SearchUserInFo(string userID, string userName, int deptID)
{
string strSql = " select * from UserInfo left join Department on UserInfo.DeptID=Department.DeptID where usertype not in (2) and UserID like '%" + userID + "%' and UserName like '%" + userName + "%'";
if (deptID != 0)
{
//进行字符串的追加
strSql += " and UserInfo.DeptID=" + deptID + "";
}
return(DBHelper.ExecuteSelect(strSql));
}
/// <summary>
/// 判断部门下有无员工信息
/// </summary>
/// <param name="where"></param>
/// <returns></returns>
public static bool HaveUserInfo(int deptID)
{
string sql = "select * from UserInfo where DeptID='" + deptID + "'";
DataTable dt = DBHelper.ExecuteSelect(sql);
bool b;
if (dt.Rows.Count > 0)
{
//表示部门有用户
b = true;
}
else
{
//表示部门没有用户
b = false;
}
return(b);
}
/// <summary>
/// 根据部门id获取单个对象
/// </summary>
/// <param name="managerID">主管ID</param>
/// <returns></returns>
public static Model.DepartmentInfo GetSingleDeptInfo(int deptID)
{
string strSql = "select * from Department where DeptID=@DeptID";
SqlParameter[] para =
{
new SqlParameter("DeptID", deptID)
};
DataTable dt = DBHelper.ExecuteSelect(strSql, para);
DataRow dr = dt.Rows[0];
Model.DepartmentInfo u = new Model.DepartmentInfo();
u.DeptName = (string)dr["DeptName"];
u.DeptInfo = (string)dr["DeptInfo"];
u.DeptID = (int)dr["DeptID"];
u.ManagerID = (string)dr["ManagerID"];
return(u);
}
/// <summary>
/// 判断请假状态（修改和删除）
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static bool SelectApproveState(int id)
{
string sql = " select * from Approve where ApproveID='" + id + "'";
DataTable dt = DBHelper.ExecuteSelect(sql);
bool b;
if (dt.Rows[0]["Status"].ToString() == "0")
{
//显示
b = true;
}
else
{
//隐藏
b = false;
}
return(b);
}
/// <summary>
/// 根据userID获取单个用户对象
/// </summary>
/// <param name="userID">用户ID</param>
/// <returns></returns>
public static Model.UserInfo GetSingleUserInfo(string userID)
{
string strSql = "select * from userInfo where UserID=@userID";
SqlParameter[] para =
{
new SqlParameter("UserID", userID)
};
DataTable dt = DBHelper.ExecuteSelect(strSql, para);
DataRow dr = dt.Rows[0]; //得到内存表的第一行数据
Model.UserInfo u = new Model.UserInfo();
u.Cellphone = (string)dr["Cellphone"];
u.DeptID = (int)dr["DeptID"];
u.UserID = (string)dr["UserID"];;
u.UserName = (string)dr["UserName"];;
u.UserType = (byte)dr["UserType"];;
return(u);
}
//查询=====>请假申请
//第一种 [DIY]
//public static DataTable SearchAskForLeave(Model.Approve a, string applyUser)
// {
//string strSql = string.Format("select CASE WHEN status = 0 THEN '待审批' WHEN status = 1 THEN '归档' ELSE '' END AS status, * from Approve inner join UserInfo on UserInfo.UserID=Approve.ApplyUser and ApplyUser='******'",applyUser);
//if (a.Title != "") //如果标题不为空
//{
// strSql += " and title like '%" + a.Title + "%'";
// // sb.AppendFormat(" and title like '%" + a.Title + "%'");
//}
//if (a.BeginDate.ToString() != "" || a.EndDate.ToString() != "") //判断申请时间是否为空
//{
// strSql += " and ApplyDate >'" + a.BeginDate + "' and ApplyDate < '" + a.EndDate + "'";
//}
//if (a.Status != 2) //请假状态 0待办 1归档 2全部 当前状态为全部
//{
// strSql += " and Status=" + a.Status;
//}
//if (a.ApplyUser != null)//申请人ID
//{
// strSql += " and ApplyUser='******'";
//}
//return DBHelper.ExecuteSelect(strSql);
// }
//第二种【Teacher】
public static DataTable SearchAskForLeave(string userID, string title, string beginDate, string endDate, byte status)
{
StringBuilder sb = new StringBuilder();
sb.AppendFormat("select * from Approve inner join UserInfo on Approve.ApplyUser=UserInfo.UserID where UserID='{0}' and Title like '%{1}%'", userID, title);
if (!string.IsNullOrEmpty(beginDate))
{
//表示beginDate不为空值
sb.AppendFormat(" and ApplyDate>='{0}'", beginDate);
}
if (!string.IsNullOrEmpty(endDate))
{
//表示endDate不为空值
sb.AppendFormat(" and ApplyDate<='{0}'", endDate);
}
if (status != 2)
{
sb.AppendFormat(" and Status={0}", status);
}
return(DBHelper.ExecuteSelect(sb.ToString()));
}
//查询=====>请假审批
public static DataTable SearchLeaveApprove(Model.Approve u, string userName)
{
string strSql = "select CASE WHEN status = 0 THEN '待审批' WHEN status = 1 THEN '归档' ELSE '' END AS status, * from Approve inner join UserInfo on UserInfo.UserID=Approve.ApplyUser";
if (u.Title != "") //如果标题不为空
{
strSql += " and title like '%" + u.Title + "%'";
}
if (u.BeginDate.ToString() != "" || u.EndDate.ToString() != "") //判断申请时间是否为空
{
// strSql += " and ApplyDate between '" + u.BeginDate + "' and '" + u.EndDate + "'";
strSql += " and ApplyDate >'" + u.BeginDate + "' and ApplyDate < '" + u.EndDate + "'";
}
if (u.Status != 2) //请假状态 0待办 1归档 2全部 当前状态为全部
{
strSql += " and Status=" + u.Status;
}
//如果申请人不为空
if (u.ApplyUser != "")
{
strSql += " and UserInfo.UserName like '%" + userName + "%'";
}
return(DBHelper.ExecuteSelect(strSql));
}
/// <summary>
/// 查询考勤设置信息
/// </summary>
/// <param name="where"></param>
/// <returns></returns>
public static DataTable GetAttendanceSettingInfo(string where)
{
string sql = " select SettingID,CONVERT(varchar(10),Date,120) as NewDate,DATENAME(WEEKDAY,Date) as WeekDays,Status from AttendanceSetting where 1=1" + where;
return(DBHelper.ExecuteSelect(sql));
}
//=======================UserManage=====================
//获取所有部门名字
public static DataTable GetAllDeptName()
{
string strSql = "select * from Department";
return(DBHelper.ExecuteSelect(strSql));
}
/// <summary>
/// 查询部门主管对应信息
/// </summary>
/// <param name="where"></param>
/// <returns></returns>
public static DataTable GetDepartmentInfo(string where)
{
string sql = " select * from Department left join UserInfo on Department.ManagerID=UserInfo.UserID where 1=1" + where;
return(DBHelper.ExecuteSelect(sql));;
}
/// <summary>
/// 获取主管信息
/// </summary>
/// <returns></returns>
public static DataTable GetAllManager()
{
string sql = "select * from dbo.UserInfo where UserType=1";
return(DBHelper.ExecuteSelect(sql));
}
//============================DepartmentManage==========================================
//获取部门主管名字
public static DataTable GetDeptChargeName()
{
string strSql = "select * from UserInfo inner join Department on UserInfo.DeptID=Department.DeptID and UserType=1";
return(DBHelper.ExecuteSelect(strSql));
}