/// <summary>
/// 通过类型名称获得字典
/// </summary>
/// <param name="typeName"></param>
/// <returns></returns>
public DataTable GetDictByTypeName(string typeName)
{
string strSQL = "Select i.Name,i.Code From DictDir d,DictItem i Where i.DirID =d.ID And d.Name='" + typeName + "' Order By Sequence";
DataTable dt = DBHelperProxy.GetDataTable(strSQL);
return(dt);
}
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 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);
}
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);
}
/// <summary>
/// 获得类型
/// </summary>
/// <returns></returns>
public DataTable GetDictType()
{
string strSQL = "select * from DictType";
DataTable dt = DBHelperProxy.GetDataTable(strSQL);
return(dt);
}
public DataTable GetOperateLogs(string tablename, string oType, string year, string month)
{
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;
}
if (!string.IsNullOrEmpty(year))
{
sql += " and " + DBHelperProxy.FormatFunction("getyear", "OperateTime") + " =" + year;
}
if (!string.IsNullOrEmpty(month))
{
sql += " and " + DBHelperProxy.FormatFunction("getmonth", "OperateTime") + " =" + month;
}
sql += " Order By ID Desc";
return(DBHelperProxy.GetDataTable(sql));
}
public static bool IsRegiste()
{
bool val = false;
string serial = Convert.ToString(DBHelperProxy.ExecuteScalar("Select Code From Dept Where ID=-1"));
if (string.IsNullOrEmpty(serial))
{
val = false;
}
else
{
string unitname = Convert.ToString(DBHelperProxy.ExecuteScalar("Select Name From Dept Where ID=1"));
if (ALEncrypt.Md5hash(unitname + "sudenggang") == serial)
{
val = true;
}
else
{
val = false;
}
}
return(val);
}
/// <summary>
///
/// </summary>
/// <param name="bindTableID"></param>
/// <param name="bindTableName"></param>
/// <param name="customType"></param>
public void DeleteAttach(int?bindTableID, string bindTableName, int?customType)
{
if (string.IsNullOrEmpty(bindTableName))
{
throw new Exception("BindTableName不允许为空");
}
List <SysAttachInfo> list = BiAttachHelper.GetSysAttachList(bindTableName, bindTableID, customType);
foreach (SysAttachInfo attach in list)
{
if (attach.Mode == 1)
{
string filepath = this.MapPath(attach.FilePath);
if (File.Exists(filepath))
{
File.Delete(filepath);
}
}
}
string sql = "DELETE FROM SysAttach WHERE BindTableName = '" + bindTableName + "'";
if (bindTableID.HasValue)
{
sql += " AND BindTableID = " + bindTableID;
}
if (customType.HasValue)
{
sql += " AND CustomType = " + customType;
}
DBHelperProxy.ExecuteNonQuery(sql);
}
public static long Save(int unitid, OperateEnum.OperateType operateType, int userID, string userName, string tableBind, string operateContent)
{
long id = DBHelperProxy.GetMaxID("OperateLog");
DBHelperProxy.ExecuteNonQuery(string.Format("INSERT INTO OperateLog(ID,UnitID,OperateType,UserID,UserName,TableBind,OperateTime,OperateContent) VALUES({0},{1},{2},{3},'{4}','{5}','{6}','{7}') ", id, unitid, Convert.ToInt32(operateType), userID, userName, tableBind, DateTime.Now, operateContent));
return(id);
}
public void AddUserToAdminRole(long userID, long unitID)
{
RoleBO role = new RoleBO();
long roleID = Convert.ToInt32(role.GetRoleIDByCode("Admin"));
string strSQL = "Insert Into RoleUser(RoleID, UserID, UnitID) Values(" + roleID + "," + userID + "," + unitID + ")";
DBHelperProxy.ExecuteNonQuery(strSQL);
}
/// <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>
/// 通过uid获取担任部门负责人的部门名称
/// </summary>
/// <param name="leaderid"></param>
/// <returns></returns>
public string GetDeptName(int leaderid)
{
string strSQL = "select top 1 Name from Dept where LeaderID=" + leaderid.ToString();
object obj = DBHelperProxy.ExecuteScalar(strSQL);
if (obj != null && obj != DBNull.Value)
{
return(obj.ToString());
}
return(string.Empty);
}
/// <summary>
/// 保存角色模块集合
/// </summary>
/// <param name="iRoleID"></param>
/// <param name="lstModuleIDs"></param>
public void SaveModuleIDs(int iRoleID, List <int> lstModuleIDs, int sUnitID)
{
StringBuilder sbSql = new StringBuilder("delete from ModuleAccess where flag=1 and UserRoleID =" + iRoleID + " And UnitID=" + sUnitID + "; ");
foreach (int id in lstModuleIDs)
{
sbSql.Append("insert into ModuleAccess(UserRoleID,ModuleID,flag, UnitID) values(" + iRoleID + "," + id + ",1, " + sUnitID + "); ");
}
DBHelperProxy.ExecuteNonQuery(sbSql.ToString());
}
public void SaveAccess(List <int> rids, int role, int unitid)
{
StringBuilder sbSql = new StringBuilder("delete from ActionSourceAccess where RoleID =" + role + " And UnitID=" + unitid + "; ");
foreach (int id in rids)
{
sbSql.Append("insert into ActionSourceAccess(RoleID,SourceID, UnitID) values(" + role + "," + id + "," + unitid + "); ");
}
DBHelperProxy.ExecuteNonQuery(sbSql.ToString());
}
/// <summary>
/// 保存排序
/// </summary>
/// <param name="IDs"></param>
/// <param name="values"></param>
public void SaveSequence(List <int> ids, List <int> values)
{
StringBuilder sbSql = new StringBuilder();
for (int i = 0; i < ids.Count; i++)
{
sbSql.Append("update Dept set Sequence=" + values[i] + " where ID=" + ids[i] + ";");
}
DBHelperProxy.ExecuteNonQuery(sbSql.ToString());
}
public DataTable GetList(long typeid)
{
string strSql = "SELECT ID,Name,Code,Sequence,TypeID,Flag, UnitID,Remark FROM Role Where Code<>'Developor' And Code<>'System' And Code<>'Audit' And Code<>'Admin' And (UnitID =0 Or UnitID=" + this.unitID + ")";
if (typeid != 0)
{
strSql += " And TypeID=" + typeid;
}
strSql += " order by sequence";
return(DBHelperProxy.GetDataSet(strSql).Tables[0]);
}
/// <summary>
/// 保存角色用户
/// </summary>
/// <param name="iRoleID"></param>
/// <param name="lstUserIDs"></param>
public void SaveUserIDs(int iRoleID, List <int> lstUserIDs, int sUnitID)
{
StringBuilder sbSql = new StringBuilder();
sbSql.Append("delete from RoleUser where RoleID=" + iRoleID + " And UnitID=" + sUnitID);
foreach (int iUserID in lstUserIDs)
{
sbSql.Append(";insert into RoleUser(RoleID,UserID, UnitID) values(" + iRoleID + "," + iUserID + ", " + sUnitID + ") ");
}
DBHelperProxy.ExecuteNonQuery(sbSql.ToString());
}
public List <int> GetUserIDs(string roleCode)
{
string strSql = "select U.UserID from Role R, RoleUser U where R.ID=U.RoleID And R.Code='" + roleCode + "' And (R.UnitID =0 Or R.UnitID=" + this.unitID + ")";
DataTable dt = DBHelperProxy.GetDataSet(strSql).Tables[0];
List <int> lstUserIDs = new List <int>();
foreach (DataRow dr in dt.Rows)
{
lstUserIDs.Add((int)dr["UserID"]);
}
return(lstUserIDs);
}
/// <summary>
/// 删除角色
/// </summary>
/// <param name="lstSelectIDs"></param>
public void Delete(List <int> lstSelectIDs)
{
string strIDs = "0";
foreach (int id in lstSelectIDs)
{
strIDs += "," + id;
}
string strSql = "delete from Role where id IN(" + strIDs + ");delete from RoleUser where RoleID IN(" + strIDs + ");";
DBHelperProxy.ExecuteNonQuery(strSql);
}
/// <summary>
/// 获得某个用户的所有角色
/// </summary>
/// <returns></returns>
public List <int> GetUserRoles(int userID, int UnitID)
{
string strSql = "select RoleID from RoleUser where UserID=" + userID;
List <int> roleIDs = new List <int>();
DataTable dt = DBHelperProxy.GetDataSet(strSql).Tables[0];
foreach (DataRow dr in dt.Rows)
{
roleIDs.Add((int)dr["RoleID"]);
}
return(roleIDs);
}
/// <summary>
/// 将用户从角色用户表中剔除
/// </summary>
/// <param name="DelUserIDS"></param>
/// <param name="UnitID"></param>
public void DelUserFromRole(List <int> delUserIDS, int unitID)
{
string strIDS = "0";
foreach (int id in delUserIDS)
{
strIDS += "," + id;
}
string strSQL = "Delete From RoleUser Where UserID In (" + strIDS + ") And UnitID=" + unitID;
DBHelperProxy.ExecuteNonQuery(strSQL);
}
/// <summary>
/// 获得角色模块ID集合
/// </summary>
/// <param name="iRoleID"></param>
/// <returns></returns>
public List <int> GetModuleIDs(int iRoleID, int sUnitID)
{
string strSql = "select ModuleID from ModuleAccess where flag=1 and UserRoleID=" + iRoleID + " And UnitID=" + sUnitID;
DataTable dt = DBHelperProxy.GetDataSet(strSql).Tables[0];
List <int> lstModuleIDs = new List <int>();
foreach (DataRow dr in dt.Rows)
{
lstModuleIDs.Add((int)dr["ModuleID"]);
}
return(lstModuleIDs);
}
/// <summary>
/// 获得角色用户
/// </summary>
/// <param name="iRoleID"></param>
/// <returns></returns>
public List <int> GetUserIDs(int iRoleID)
{
string strSql = "select UserID from RoleUser where RoleID=" + iRoleID + " And (UnitID =0 Or UnitID=" + this.unitID + ")";
DataTable dt = DBHelperProxy.GetDataSet(strSql).Tables[0];
List <int> lstUserIDs = new List <int>();
foreach (DataRow dr in dt.Rows)
{
lstUserIDs.Add((int)dr["UserID"]);
}
return(lstUserIDs);
}
public static bool Registe(string serial, string unitName)
{
if (ALEncrypt.Md5hash(unitName + "sudenggang") != serial)
{
return(false);
}
else
{
DBHelperProxy.ExecuteNonQuery(string.Format("Update Dept set Code='{0}',Name='{1}' Where ID=-1;Update Dept set Name='{1}' Where ID=1;", serial, unitName));
// DBHelperProxy.ExecuteNonQuery(string.Format("Update ContactDir set Name='{0}' Where DeptID=1;",unitName));
return(true);
}
}
public List <int> GetRoleAccess(int role, int unitid)
{
List <int> rids = new List <int>();
string sql = "select SourceID from ActionSourceAccess where RoleID =" + role + " And UnitID=" + unitid + "; ";
DataTable dt = DBHelperProxy.GetDataTable(sql);
foreach (DataRow dr in dt.Rows)
{
rids.Add(int.Parse(dr[0].ToString()));
}
return(rids);
}
private DbParameter getSqlParameter(string name, XmlAttribute value)
{
DbParameter par;
if (value == null)
{
par = DBHelperProxy.CreateParameter(name, DBNull.Value);
}
else
{
par = DBHelperProxy.CreateParameter(name, value.Value);
}
return(par);
}
/// <summary>
/// 根据字典名称和字典项的代码取字典项的名称
/// </summary>
/// <param name="dictName"></param>
/// <param name="itemCode"></param>
/// <returns></returns>
public string GetDictItemName(string dictName, string itemCode)
{
string strSQL = "Select i.Name From DictDir d,DictItem i Where i.DirID =d.ID and i.code ='" + itemCode + "' And d.Name='" + dictName + "'";
DataTable dt = DBHelperProxy.GetDataTable(strSQL);
if (dt.Rows.Count > 0)
{
return(dt.Rows[0]["name"].ToString());
}
else
{
return("");
}
}
/// <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取部门名称
/// </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>
/// 根据部门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);
}
}
