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));
}
