public IList <UserInfo> SearchManage()
{
IList <UserInfo> usersJionLog = new List <UserInfo>();
DataTable dt = DBhelper.Select("select * from UserInfo where UserType ='1'");
return(usersJionLog = ConvertHelper.convertToList <UserInfo>(dt));
}
public static bool UserIDPadding(string id)
{
string sql = "select * from UserInfo where UserID='" + id + "'";
DataTable dt = DBhelper.Select(sql);
return(dt.Rows.Count > 0);
}
public DataTable GetAllDepart()
{
DataTable dt = new DataTable();
dt = DBhelper.Select("select *,(select UserName from UserInfo where UserID=ManagerID) as UserName from Department");
return(dt);
}
public DataTable SearchManage(string name, string manage, int PageSize, int pageindex)
{
DataTable dt = new DataTable();
string sql = "select ROW_NUMBER() over (order by Department.DeptID) RowNumb,DeptName,ManagerID, UserName from Department left join UserInfo on Department.ManagerID=UserInfo.UserID where DeptName like '%" + name + "%' ";
if (manage != "")
{
sql += "and UserName like '%" + manage + "%'";
}
int StarNum = (pageindex - 1) * PageSize + 1;
int EndNum = PageSize * pageindex;
string sqlComb = "select * from (" + sql + ") A where RowNumb between " + StarNum + " and " + EndNum;
if (pageindex != 0)
{
dt = DBhelper.Select(sqlComb);
}
else
{
dt = DBhelper.Select(sql);
}
return(dt);
}
public static bool DepartPadding(string id)
{
string sql = "select * from Department where DeptName='" + id + "'";
DataTable dt = DBhelper.Select(sql);
return(dt.Rows.Count > 0);
}
public String GetDateStatus(string date)
{
string sql = "select * from AttendanceSetting where date = '" + date + "'";
DataTable dt = DBhelper.Select(sql);
return(dt.Rows.Count > 0 ? dt.Rows[0][2].ToString() : "0");
}
public bool DepartIsNull(string id)
{
string sql = "select Count(*) from UserInfo where DeptID=(select DeptID from Department where DeptName='" + id.Trim() + "')";
DataTable dt = DBhelper.Select(sql);
return(dt.Rows[0][0].ToString() == "0");
}
public DataTable getCardInfo(int year, int month, string id)
{
Dictionary <DateTime, int> HoliDic = new Dictionary <DateTime, int>();
string sql = "select MIN(FaceTime) Frist,Max(FaceTime) Last,COUNT(FaceTime) Count,convert(varchar(10),FaceTime,111) Date from AttendanceInfo where UserID = '" + id + "' and YEAR(FaceTime) = " + year + " and MONTH(FaceTime) = " + month + " group by UserID, convert(varchar(10), FaceTime, 111)";
DataTable dt = DBhelper.Select(sql);
return(dt);
}
public static string UserInfos(string id)
{
string sql = "select * from UserInfo where UserID='" + id + "'";
DataTable dt = DBhelper.Select(sql);
string user = dt.Rows[0]["UserType"].ToString() + "," + dt.Rows[0]["Cellphone"].ToString();
return(user);
}
public static string DepartInfos(string id)
{
string sql = "select * from Department where DeptName='" + id + "'";
DataTable dt = DBhelper.Select(sql);
string Info = dt.Rows[0]["DeptInfo"].ToString();
return(Info);
}
public static string GetApply(string str)
{
string sql = "select * from Approve left join UserInfo on UserInfo.UserID=Approve.ApproveUser where ApproveID = '" + str + "'";
DataTable dt = DBhelper.Select(sql);
string res = dt.Rows[0]["Result"].ToString() + "," + dt.Rows[0]["UserName"].ToString() + "," + dt.Rows[0]["ApproveDate"].ToString() + "," + dt.Rows[0]["Remark"].ToString();
return(res);
}
public DataTable SearchPeopleInManage(string manage, int pagesize, int pageIndex)
{
string sql = "select ROW_NUMBER() over (order by UserInfo.UserID) RowNumb,Department.*,UserID,UserName from Department right join UserInfo on UserInfo.DeptID = Department.DeptID where ManagerID = '" + manage + "'";
int StarNum = (pageIndex - 1) * pagesize + 1;
int EndNum = pagesize * pageIndex;
string sqlComb = "select * from (" + sql + ") A where RowNumb between " + StarNum + " and " + EndNum;
DataTable dt = DBhelper.Select(sqlComb);
return(dt);
}
public static string DateIsFull(string star, string end, string id, string appid)
{
string sql = string.Format("select * from Approve where ApplyUser = '******' and(BeginDate between '{1}' and '{2}' or EndDate between '{1}' and '{2}')",
id, star, end);
if (appid != "")
{
sql += " and ApproveID!='" + appid + "'";
}
return(DBhelper.Select(sql).Rows.Count > 0 ? "true" : "false");
}
public Dictionary <DateTime, int> DateInfo(int year, int month)
{
Dictionary <DateTime, int> dic = new Dictionary <DateTime, int>();
string sql = "select * from AttendanceSetting where year(Date)=" + year + " and MONTH(Date)=" + month;
DataTable dt = DBhelper.Select(sql);
foreach (DataRow dr in dt.Rows)
{
dic.Add(Convert.ToDateTime(dr["Date"]), Convert.ToInt32(dr["Status"]));
}
return(dic);
}
public int SearchPeopleCount(string id, string name, string dept)
{
string sql = string.Format("select * from Department right join UserInfo on UserInfo.DeptID=Department.DeptID where UserID !='admin' and UserID like '%{0}%' and UserName like '%{1}%' "
, id, name);
if (dept != "")
{
sql += "and DeptName in (" + dept + ")";
}
DataTable dt = DBhelper.Select(sql);
return(dt.Rows.Count);
}
public Dictionary <DateTime, int> getDaySet(int year, int month)
{
Dictionary <DateTime, int> asetDic = new Dictionary <DateTime, int>();
string sql = " select * from AttendanceSetting where YEAR(Date) =" + year + " and MONTH(Date) =" + month;
DataTable dt = DBhelper.Select(sql);
foreach (DataRow dr in dt.Rows)
{
DateTime now = Convert.ToDateTime(dr["Date"]);
int statu = Convert.ToInt32(dr["Status"]);
asetDic[now] = statu;
}
return(asetDic);
}
public DataTable SearchPeople(string id, string name, string dept, int pagesize, int pageIndex)
{
string sql = string.Format("select ROW_NUMBER() over (order by Department.DeptID) RowNumb,Department.*,UserID,UserName from Department right join UserInfo on UserInfo.DeptID=Department.DeptID where UserID !='admin' and UserID like '%{0}%' and UserName like '%{1}%' "
, id, name);
if (dept != "")
{
sql += "and DeptName in (" + dept + ")";
}
int StarNum = (pageIndex - 1) * pagesize + 1;
int EndNum = pagesize * pageIndex;
string sqlComb = "select * from (" + sql + ") A where RowNumb between " + StarNum + " and " + EndNum;
DataTable dt = DBhelper.Select(sqlComb);
return(dt);
}
public bool Login(ref UserInfo us)
{
string sql = "select * from UserInfo where UserID='" + us.UserID + "' and Password='******'";
if (DBhelper.Select(sql).Rows.Count == 0)
{
return(false);
}
us.UserType = Convert.ToByte(DBhelper.Select(sql).Rows[0]["UserType"]);
us.UserName = DBhelper.Select(sql).Rows[0]["UserName"].ToString();
if (us.UserType != 2)
{
us.DeptID = Convert.ToInt32(DBhelper.Select(sql).Rows[0]["DeptID"]);
}
return(true);
}
public Dictionary <DateTime, int> getHoliday(int year, int month, string id)
{
Dictionary <DateTime, int> HoliDic = new Dictionary <DateTime, int>();
string sql = "select* from Approve where ApplyUser = '******' and YEAR(BeginDate) = " + year + " and MONTH(BeginDate) = " + month + " and Result=1";
DataTable dt = DBhelper.Select(sql);
foreach (DataRow dr in dt.Rows)
{
DateTime star = Convert.ToDateTime(dr["BeginDate"]);
DateTime end = Convert.ToDateTime(dr["EndDate"]);
if (star.Hour == 8)
{
HoliDic[Convert.ToDateTime(star.ToShortDateString())] = 3;
}
else
{
HoliDic[Convert.ToDateTime(star.ToShortDateString())] = 301;
}
for (star = star.AddDays(1); star < end; star = star.AddDays(1))
{
HoliDic[Convert.ToDateTime(star.ToShortDateString())] = 3;
HoliDic[Convert.ToDateTime(star.ToShortDateString())] = 3;
}
if (end.Hour == 17)
{
if (star.AddDays(-1).ToShortDateString() == end.ToShortDateString())
{
HoliDic[Convert.ToDateTime(end.ToShortDateString())] = 301;
}
else
{
HoliDic[Convert.ToDateTime(end.ToShortDateString())] = 3;
}
}
else
{
HoliDic[Convert.ToDateTime(end.ToShortDateString())] = 310;
}
}
return(HoliDic);
}
public int SearchApproveCountByUser(string User, string title, string start, string end, string Status)
{
string sql = "select ROW_NUMBER() over (order by Approve.ApproveID) RowNumb,*,statusname= case Status when '1' then '归档' else '待审核' end from Approve left join UserInfo on UserInfo.UserID=Approve.ApplyUser where ApplyUser='******' and Title like '%" + title + "%'";
if (start != "")
{
sql += " and ApplyDate >= '" + start + "'";
}
if (end != "")
{
sql += " and ApplyDate <= '" + end + "' ";
}
if (Status != "")
{
sql += " and Status = '" + Status + "'";
}
DataTable dt = DBhelper.Select(sql);
return(dt.Rows.Count);
}
public List <ApproveJoinUserInfo> SearchApproveByManage(string User, string Usname, string title, string start, string end, string Status, int pagesize, int pageIndex, string sortExpression, string sortDirection)
{
string sql = "select ROW_NUMBER() over (order by Approve.ApproveID) RowNumb,*,statusname= case Status when '1' then '归档' else '待审核' end from Approve left join UserInfo on UserInfo.UserID=Approve.ApplyUser where DeptID='" + User + "' and Title like '%" + title + "%' and UserName like '%" + Usname + "%'";
if (start != "")
{
sql += " and ApplyDate >= '" + start + "'";
}
if (end != "")
{
sql += " and ApplyDate <= '" + end + "' ";
}
if (Status != "")
{
sql += " and Status = '" + Status + "'";
}
int StarNum = (pageIndex - 1) * pagesize + 1;
int EndNum = pagesize * pageIndex;
string sqlComb = "select * from (" + sql + ") A where RowNumb between " + StarNum + " and " + EndNum + " order by " + sortExpression + " " + sortDirection;;
DataTable dt = DBhelper.Select(sqlComb);
return(ConvertHelper.convertToList <ApproveJoinUserInfo>(dt).ToList());
}
public static string GetRes(string date)
{
string sql = "select Reason from Approve where ApproveID = '" + date + "'";
return(DBhelper.Select(sql).Rows[0][0].ToString());
}