public DataTable GetRoleUsers(string roleCode) { string strSql = "select Us.ID,Us.UserName from Role R, RoleUser U," + DBHelperProxy.FormatTable("User") + " Us where R.ID=U.RoleID and U.UserID=Us.ID And R.Code='" + roleCode + "' And (R.UnitID =0 Or R.UnitID=" + this.unitID + ")"; DataTable dt = DBHelperProxy.GetDataSet(strSql).Tables[0]; return(dt); }
/// <summary> /// 检测该部门是否有用户 /// </summary> /// <param name="deptID"></param> /// <returns></returns> public bool IsHavUserInDept(int deptID) { int sCount = 0; string strSQL = "Select Count(*) From " + DBHelperProxy.FormatTable("User") + " Where DeptID=" + deptID; sCount = Convert.ToInt32(DBHelperProxy.ExecuteScalar(strSQL)); return(sCount > 0); }
/// <summary> /// 根据id取部门名称 /// </summary> /// <param name="deptID"></param> /// <returns></returns> public string GetDeptNameByID(int deptID) { string strSQL = "Select Name from " + DBHelperProxy.FormatTable("Dept") + " where ID=" + deptID.ToString(); object name = DBHelperProxy.ExecuteScalar(strSQL); if (name != null && name != DBNull.Value) { return(name.ToString()); } else { return(string.Empty); } }
/// <summary> /// 新建单位时自动产生一个单位管理员 /// </summary> /// <param name="deptID">部门ID</param> /// <param name="unitID">单位ID</param> public void NewAdminOfUnit(long deptID, long unitID, string unitName) { long newUserID; string userName = "******" + unitID; string password = ALEncrypt.Md5hash(AppConfig.GetConfig("InitPassword")); newUserID = DBHelperProxy.GetMaxID("User"); string strSQL = "Insert Into " + DBHelperProxy.FormatTable("User") + "(ID,UserName,LoginName," + DBHelperProxy.FormatField("Password") + ",DeptID,DeptName,UnitID,IsStop," + DBHelperProxy.FormatField("Sequence") + ") Values(" + newUserID.ToString() + ", '管理员[" + unitID + "]','" + userName + "','" + password + "'," + deptID + ",'" + unitName + "'," + unitID + ",1,100);"; DBHelperProxy.ExecuteScalar(strSQL); //添加到管理员角色中 AddUserToAdminRole(newUserID, unitID); }
/// <summary> /// 根据部门id取部门领导id /// </summary> /// <param name="deptID"></param> /// <returns></returns> public void GetLeaderIDByDeptID(int deptID, out int leaderID, out string leaderName) { leaderID = 0; leaderName = ""; string strSQL = "select LeaderID from " + DBHelperProxy.FormatTable("Dept") + " where ID =" + deptID; object leader = DBHelperProxy.ExecuteScalar(strSQL); if (leader != null && leader != DBNull.Value) { leaderID = (int)leader; IUser user = new UserBO(); leaderName = user.GetUserNameByID(leaderID); } }
/// <summary> /// 根据ids取所有的部门名称,参数格式"1,2,3",返回"一部,二部,三部" /// </summary> /// <param name="deptIDs"></param> /// <returns></returns> public string GetDeptNamesByIDs(string deptIDs) { string strSql = "select Name from " + DBHelperProxy.FormatTable("Dept") + " where id in (" + deptIDs + ")"; DataTable dt = DBHelperProxy.GetDataTable(strSql); StringBuilder names = new StringBuilder(); for (int i = 0; i < dt.Rows.Count; i++) { if (i == 0) { names.Append(dt.Rows[i]["Name"].ToString()); } else { names.Append("," + dt.Rows[i]["Name"].ToString()); } } return(names.ToString()); }
/// <summary> /// 插入和更新 /// </summary> /// <param name="deptInfo"></param> /// <returns></returns> public long Save(SYSDeptInfo deptInfo) { long id = deptInfo.ID.Value; base.Save(deptInfo); if (deptInfo.IsUnit == 1) { deptInfo.UnitID = deptInfo.ID; deptInfo.ParentID = 0; deptInfo.Layer = 0; deptInfo.Path = ",-1," + deptInfo.ID + ","; } else { DeptInfo tempInfo = GetInfo <DeptInfo>(deptInfo.ParentID.Value); deptInfo.Layer = tempInfo.Layer + 1; deptInfo.Path = tempInfo.Path + deptInfo.ID + ","; } base.Save(deptInfo); if (id != 0) { //更新user表 string strSql = "UPDATE " + DBHelperProxy.FormatTable("User") + " SET DeptName = " + DBHelperProxy.FormatParameter("Name") + " WHERE DeptID=" + DBHelperProxy.FormatParameter("ID") + ";UPDATE " + DBHelperProxy.FormatTable("User") + " SET SLDeptName = " + DBHelperProxy.FormatParameter("Name") + " WHERE SLDeptID=" + DBHelperProxy.FormatParameter("ID") + ";"; DBHelperProxy.ExecuteScalar(strSql, DBHelperProxy.CreateParameter("Name", deptInfo.Name), DBHelperProxy.CreateParameter("ID", deptInfo.ID.Value)); } if (id == 0 && deptInfo.IsUnit == 1) { //新增单位时自动增加一个单位管理员 NewAdminOfUnit(deptInfo.ID.Value, deptInfo.ID.Value, deptInfo.Name); } return(deptInfo.ID.Value); }
public DataTable GetLoginStat(string year, string month, string deptid) { string sql = "Select Count(*) as Total,U.LoginName,U.UserName,U.DeptName from LoginLog L Inner Join " + DBHelperProxy.FormatTable("User") + " U On L.UserID=U.ID Where 1=1"; if (!string.IsNullOrEmpty(year)) { sql += " and " + DBHelperProxy.FormatFunction("getyear", "LoginTime") + " =" + year; } if (!string.IsNullOrEmpty(month)) { sql += " and " + DBHelperProxy.FormatFunction("getmonth", "LoginTime") + " =" + month; } if (!string.IsNullOrEmpty(deptid)) { sql += " and U.DeptID=" + deptid; } sql += " Group by L.UserID,U.LoginName,U.Username,U.DeptName Order by Total desc"; return(DBHelperProxy.GetDataTable(sql)); }
public DataTable GetLoginLogs(string year, string month, string deptid, string username) { string sql = "Select LoginTime,UserID,U.LoginName,L.IP,U.UserName,U.DeptName From LoginLog L Inner Join " + DBHelperProxy.FormatTable("User") + " U On L.UserID=U.ID Where 1=1"; if (!string.IsNullOrEmpty(year)) { sql += " and " + DBHelperProxy.FormatFunction("getyear", "LoginTime") + " =" + year; } if (!string.IsNullOrEmpty(month)) { sql += " and " + DBHelperProxy.FormatFunction("getmonth", "LoginTime") + " =" + month; } if (!string.IsNullOrEmpty(deptid)) { sql += " and U.DeptID=" + deptid; } if (!string.IsNullOrEmpty(username)) { sql += " and U.UserName='******'"; } sql += " Order By L.ID Desc"; return(DBHelperProxy.GetDataTable(sql)); }
public DataTable GetLoginLogs() { return(DBHelperProxy.GetDataTable("Select LoginTime,UserID,L.UserName as LoginName,L.IP,U.UserName,U.DeptName From LoginLog L Inner Join " + DBHelperProxy.FormatTable("User") + " U On L.UserID=U.ID Order By L.ID Desc")); }
public DataTable GetLogStat() { DataTable dtTotal = new DataTable("LogTotal"); DataColumn dc = new DataColumn("Desc"); dtTotal.Columns.Add(dc); dc = new DataColumn("Total"); dtTotal.Columns.Add(dc); string sql = "select * from " + DBHelperProxy.FormatFunction("gettables"); DataTable dt = DBHelperProxy.GetDataTable(sql); DataRow drr; foreach (DataRow dr in dt.Rows) { drr = dtTotal.NewRow(); drr["Desc"] = dr["table_name"]; drr["Total"] = DBHelperProxy.ExecuteScalar(string.Format("select count(*) as total from " + DBHelperProxy.FormatTable("{0}"), dr["Name"].ToString())); dtTotal.Rows.Add(drr); } return(dtTotal); }
public DataTable GetOperateLogs(string tablename, string oType) { string sql = "Select ID,UnitID,OperateType,UserID,UserName,TableBind,OperateTime,OperateContent From " + DBHelperProxy.FormatTable("OperateLog") + " Where 1=1 "; if (!string.IsNullOrEmpty(tablename)) { sql += " and TableBind='" + tablename + "'"; } if (!string.IsNullOrEmpty(oType)) { sql += " and OperateType=" + oType; } sql += " Order By ID Desc"; return(DBHelperProxy.GetDataTable(sql)); }
public DataTable GetOperateLogs() { return(DBHelperProxy.GetDataTable("Select ID,UnitID,OperateType,UserID,UserName,TableBind,OperateTime,OperateContent From " + DBHelperProxy.FormatTable("OperateLog") + " Order By ID Desc")); }
/// <summary> /// 根据部门id取所有下级部门,不含下级的子部门 /// </summary> /// <param name="parentID"></param> /// <returns></returns> public DataTable GetChildDeptsByID(int parentID) { string strSql = "select * from " + DBHelperProxy.FormatTable("Dept") + " where ParentID =" + parentID + " order by Sequence "; return(DBHelperProxy.GetDataTable(strSql)); }