/// <summary>
/// (分页查询)从数据库中获取角色列表
/// </summary>
/// <param name="queryModel">查询条件</param>
/// <returns>返回角色列表</returns>
public DataTable GetRoleList(Entity.System.view.QueryModel queryModel)
{
try
{
var roleName = queryModel.roleName ?? "";
var pageNumber = queryModel.offset / queryModel.limit + 1;
var pageSize = queryModel.limit;
var strSql = new StringBuilder();
strSql.Append("select t.*,COUNT(*) OVER () RESULT_COUNT from ROLEINFO t where t.f_name like '%'||:roleName||'%'");
OracleParameter[] parameters =
{
new OracleParameter(":roleName", roleName)
};
var dbapi = new DbAPI();
dbapi.OpenConn("");
var rst = dbapi.GetDataTable(DbAPI.GeneratePagingSql(strSql.ToString(), pageNumber, pageSize), parameters);
dbapi.CloseConn();
return(rst);
}
catch (Exception ex)
{
LogBN.WriteLog(typeof(USERROLE_BN), "(分页查询)从数据库中获取角色列表GetRoleList 程序段的异常" + ex);
return(null);
}
}
/// <summary>
/// (分页查询)从数据库中获取某个时间段的监控历史记录列表
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="beginTime">开始时间</param>
/// <param name="endTime">结束时间</param>
/// <param name="strWhere">其他查询条件</param>
/// <param name="pageNumber">页码</param>
/// <param name="pageSize">每页记录数</param>
/// <returns></returns>
private DataTable GetList(string tableName, string beginTime, string endTime, string strWhere, int pageNumber, int pageSize)
{
var strSql = new StringBuilder();
strSql.Append("select d.devicename, t.* ");
strSql.AppendFormat("from {0} t ", tableName);
strSql.Append("left join deviceinfo d ");
strSql.Append("on t.devicecode = d.devicecode ");
strSql.Append("where t.DATETIME between ");
strSql.Append("to_date(:beginTime, 'YYYY-MM-DD HH24:MI:SS') and ");
strSql.Append("to_date(:endTime, 'YYYY-MM-DD HH24:MI:SS') ");
if (!string.IsNullOrWhiteSpace(strWhere))
{
strSql.AppendFormat("and {0} ", strWhere);
}
strSql.Append("order by t.DATETIME desc");
OracleParameter[] parameters =
{
new OracleParameter(":beginTime", beginTime),
new OracleParameter(":endTime", endTime)
};
var dbapi = new DbAPI();
dbapi.OpenConn("");
var rst = dbapi.GetDataTable(DbAPI.GeneratePagingSql(strSql.ToString(), pageNumber, pageSize), parameters);
dbapi.CloseConn();
return(rst);
}
/// <summary>
/// 获得数据列表
/// </summary>
public DataTablesResult GetList(string logType, string startDate, string endDate, int pageNumber, int pageSize)
{
StringBuilder strSql = new StringBuilder();
DbAPI dbHelper = new DbAPI();
List <OracleParameter> listParams = new List <OracleParameter>();
strSql.Append(" SELECT LOG_ID,LOGNAME,\"TYPE\",\"CONTENT\",REMARK,DATETIME,US.F_NAME FROM SYSTEMLOG S ");
strSql.Append(" LEFT JOIN USERINFO US ");
strSql.Append(" ON S.F_ACCOUNT=US.F_ACCOUNT ");
strSql.Append(" WHERE 1=1 ");
if (!string.IsNullOrEmpty(logType))
{
strSql.Append(" AND \"TYPE\" LIKE '%").Append(logType).Append("%' ");
//listParams.Add(new OracleParameter(":logType", logType));
}
if (!string.IsNullOrEmpty(startDate) && !string.IsNullOrEmpty(endDate))
{
strSql.Append(" AND DATETIME BETWEEN TO_DATE(:startDate, 'YYYY-MM-DD HH24:MI:SS') AND TO_DATE(:endDate, 'YYYY-MM-DD HH24:MI:SS') ");
listParams.Add(new OracleParameter("startDate", startDate));
listParams.Add(new OracleParameter("endDate", endDate + " 23:59:59"));
}
strSql.Append(" ORDER BY DATETIME DESC");
try
{
dbHelper.OpenConn("");
DataTable dt = dbHelper.GetDataTable(DbAPI.GeneratePagingSql(strSql.ToString(), pageNumber, pageSize), listParams.ToArray());
dbHelper.CloseConn();
DataTablesResult result = new DataTablesResult();
result.aaData = dt;
result.sEcho = Convert.ToInt16(pageSize);
result.iTotalDisplayRecords = GetCount(logType, startDate, endDate, pageNumber, pageSize);
result.iTotalRecords = GetCount(logType, startDate, endDate, pageNumber, pageSize).ToString();
return(result);
}
catch (Exception ex)
{
LogBN.WriteLog(typeof(SYSTEMLOG_BN), "GetList 程序段的异常" + ex);
return(null);
}
}
/// <summary>
/// (分页查询)从数据库中获取某个时间段的监控历史记录列表
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="devicecode">设备编号</param>
/// <param name="beginTime">开始时间</param>
/// <param name="endTime">结束时间</param>
/// <param name="strWhere">其他查询条件</param>
/// <param name="pageNumber">页码</param>
/// <param name="pageSize">每页记录数</param>
/// <returns></returns>
private DataTable GetList(string tableName, string devicecode, string beginTime, string endTime, string strWhere, int pageNumber, int pageSize)
{
try
{
var strSql = new StringBuilder();
strSql.Append("select d.devicename, t.*,COUNT(*) OVER () RESULT_COUNT ");
strSql.AppendFormat("from {0} t ", tableName);
strSql.Append("left join deviceinfo d ");
strSql.Append("on t.devicecode = d.devicecode ");
strSql.Append("where t.devicecode=:devicecode and t.DATETIME between ");
strSql.Append("to_date(:beginTime, 'YYYY-MM-DD HH24:MI:SS') and ");
strSql.Append("to_date(:endTime, 'YYYY-MM-DD HH24:MI:SS') ");
if (!string.IsNullOrWhiteSpace(strWhere))
{
strSql.AppendFormat("and {0} ", strWhere);
}
strSql.Append("order by t.DATETIME desc");
OracleParameter[] parameters =
{
new OracleParameter(":devicecode", devicecode),
new OracleParameter(":beginTime", beginTime),
new OracleParameter(":endTime", endTime)
};
var dbapi = new DbAPI();
dbapi.OpenConn("");
var rst = dbapi.GetDataTable(DbAPI.GeneratePagingSql(strSql.ToString(), pageNumber, pageSize), parameters);
dbapi.CloseConn();
return(rst);
}
catch (Exception ex)
{
LogBN.WriteLog(typeof(MonitorLog), "(分页查询)从数据库中获取某个时间段的监控历史记录列表GetList 程序段的异常" + ex);
return(null);
}
}
