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 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 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)); }
/// <summary> /// 获得类型 /// </summary> /// <returns></returns> public DataTable GetDictType() { string strSQL = "select * from DictType"; DataTable dt = DBHelperProxy.GetDataTable(strSQL); return(dt); }
/// <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 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); }
/// <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(""); } }
public string ExportDictXml(string ids) { StringBuilder strSql = new StringBuilder(); strSql.AppendFormat("select DictType.Name, dictDir.Name,UnitID,Remark,DictItem.Name,DictItem.Code,Sequence from DictType left join DictDir on DictType.ID=DictDir.TypeID left join DictItem on dictDir.ID=DictItem.DirID {0} order by TypeID,DirID,[Sequence] for xml auto;", string.IsNullOrEmpty(ids) ? "" : "where dictDir.ID in (" + ids + ")"); DataTable dt = DBHelperProxy.GetDataTable(strSql.ToString()); strSql = new StringBuilder(); foreach (DataRow dr in dt.Rows) { strSql.Append(dr[0].ToString()); } return(strSql.ToString()); }
/// <summary> /// /// </summary> /// <param name="bindTableName"></param> /// <param name="bindTalbeID"></param> /// <param name="customType"></param> /// <returns></returns> public static List <SysAttachInfo> GetSysAttachList(string bindTableName, int?bindTalbeID, int?customType) { string sql = "SELECT * FROM SysAttach WHERE 1=1"; if (!string.IsNullOrEmpty(bindTableName)) { sql += " AND BindTableName = '" + bindTableName + "'"; } if (bindTalbeID.HasValue) { sql += " AND BindTableID = " + bindTalbeID.Value; } if (customType.HasValue) { sql += " AND CustomType = " + customType.Value; } return(DBHelperProxy.GetDataTable(sql).ToList <SysAttachInfo>()); }
/// <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="name"></param> /// <returns></returns> public IDictionary <int, string> GetItemDic(string dictname) { string strSQL = "select ID,Name from DictItem where DirID=(select top 1 ID from DictDir where Name='" + dictname + "')"; DataTable dt = DBHelperProxy.GetDataTable(strSQL); Dictionary <int, string> aDic = new Dictionary <int, string>(); if (dt.Rows.Count > 0) { for (int i = 0; i < dt.Rows.Count; i++) { DataRow row = (DataRow)dt.Rows[i]; int id = Convert.ToInt32(row["ID"]); string name = row["Name"].ToString(); if (aDic.ContainsKey(id) == false) { aDic.Add(id, name); } } } return(aDic); }
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 GetActionSourceBelongs() { return(DBHelperProxy.GetDataTable("Select ID,Name,Code,UnitID From ActionSourceBelong")); }
/// <summary> /// 获得角色类型 /// </summary> /// <returns></returns> public DataTable GetRoleTypes() { string strSql = "select * from RoleType Order By id asc"; return(DBHelperProxy.GetDataTable(strSql)); }
public DataTable GetActionSources(int bid) { return(DBHelperProxy.GetDataTable("Select ID,BelongID,Name,Code,CodeExt,UnitID From ActionSource Where BelongID=" + bid)); }
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 GetOperateLogs() { return(DBHelperProxy.GetDataTable("Select ID,UnitID,OperateType,UserID,UserName,TableBind,OperateTime,OperateContent From " + DBHelperProxy.FormatTable("OperateLog") + " Order By ID Desc")); }
/// <summary> /// /// </summary> /// <param name="ids"></param> /// <returns></returns> public static List <SysAttachInfo> GetSysAttach(List <int> ids) { string sql = string.Format("SELECT * FROM SysAttach WHERE id in ({0})", ALConvert.ListToString <int>(ids, ',')); return(DBHelperProxy.GetDataTable(sql).ToList <SysAttachInfo>()); }
/// <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)); }
public DataTable GetDict(int type) { string strSQL = "SELECT d.*, t.Name AS TypeName FROM DictDir d LEFT JOIN DictType t ON d.TypeID = t.ID where d.TypeID=" + type + " and ( d.UnitID=0 OR d.UnitID=" + unitID.ToString() + ")"; return(DBHelperProxy.GetDataTable(strSQL)); }
public DataTable QueryDict(string str, long typeID) { string strSQL = " select id from DictDir where name='" + str + "' and TypeID =" + typeID; return(DBHelperProxy.GetDataTable(strSQL)); }
/// <summary> /// 根据用户ID和角色代码判断用户是否在这个角色中,苏登刚 /// </summary> /// <param name="roleCode"></param> /// <param name="userID"></param> public bool IsInRole(string roleCode, int userID) { string strSQl = "select Code FROM Role R LEFT OUTER JOIN RoleUser U ON R.ID = U.RoleID WHERE (R.Code = '" + roleCode + "') AND (U.UserID = " + userID + ")"; return(DBHelperProxy.GetDataTable(strSQl).Rows.Count > 0); }
/// <summary> /// 根据字典名获得项 /// </summary> /// <param name="dictName"></param> /// <returns></returns> private DataTable getDictItemByName(string dictName) { string strSQL = "Select i.* From DictDir d, DictItem i Where d.id =i.DirID And d.Name='" + dictName + "' Order By Sequence"; return(DBHelperProxy.GetDataTable(strSQL)); }