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