/// <summary>
/// 得到一个对象实体
/// </summary>
public Entity.ROLEINFO GetModel(string F_ROLECODE)
{
StringBuilder strSql = new StringBuilder();
DbAPI dbHelper = new DbAPI();
strSql.Append("select F_ROLECODE, F_NAME, F_DESCRIPTION ");
strSql.Append(" from ROLEINFO ");
strSql.Append(" where F_ROLECODE=:F_ROLECODE ");
OracleParameter[] parameters =
{
new OracleParameter(":F_ROLECODE", OracleType.VarChar, 36)
};
parameters[0].Value = F_ROLECODE;
Entity.ROLEINFO model = new Entity.ROLEINFO();
DataTable ds = dbHelper.GetDataTable(strSql.ToString(), parameters);
if (ds.Rows.Count > 0)
{
model.F_ROLECODE = ds.Rows[0]["F_ROLECODE"].ToString();
model.F_NAME = ds.Rows[0]["F_NAME"].ToString();
model.F_DESCRIPTION = ds.Rows[0]["F_DESCRIPTION"].ToString();
return(model);
}
else
{
return(null);
}
}
public string GetMapEqipment(string strWhere)
{
StringBuilder strSql = new StringBuilder();
DbAPI dbHelper = new DbAPI();
OracleParameter[] parameters = null;
strSql.Append("select * ");
strSql.Append(" FROM DEVICEFULL ");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
try
{
dbHelper.OpenConn("");
DataTable dt = new DataTable();
dt = dbHelper.GetDataTable(strSql.ToString(), parameters);
dbHelper.CloseConn();
string result = JsonConvert.SerializeObject(dt, new DataTableConverter());
return(result);
}
catch (Exception ex)
{
LogBN.WriteLog(typeof(DEVICEATTACH_BN), "GetMapEqipment 程序段的异常" + ex);
return("");
}
}
/// <summary>
/// 获取全部角色(绑定下拉框)
/// </summary>
public DataTable GetAllRoles(string userAccount)
{
try
{
if (string.IsNullOrWhiteSpace(userAccount))
{
return(GetList(""));
}
var strSql = "select A.F_ROLECODE, A.F_NAME, DECODE(B.F_ID, NULL, 0, 1) AS \"ISUSERHAS\"" +
" from ROLEINFO A" +
" LEFT JOIN (SELECT * FROM USERROLE WHERE F_ACCOUNT = :F_ACCOUNT) B" +
" ON A.F_ROLECODE = B.F_ROLECODE";
OracleParameter[] parms =
{
new OracleParameter("F_ACCOUNT", userAccount)
};
var dbapi = new DbAPI();
dbapi.OpenConn("");
var rst = dbapi.GetDataTable(strSql, parms);
dbapi.CloseConn();
return(rst);
}
catch (Exception ex)
{
LogBN.WriteLog(typeof(MonitorLog), "获取全部角色(绑定下拉框)方法GetAllRoles 程序段的异常" + 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>
/// <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>
public Entity.TABDICTIONARY GetModel()
{
StringBuilder strSql = new StringBuilder();
DbAPI dbHelper = new DbAPI();
strSql.Append("select DICTIONARYID, CONTENT, BEIZHU ");
strSql.Append(" from TABDICTIONARY ");
strSql.Append(" where ");
OracleParameter[] parameters =
{
};
Entity.TABDICTIONARY model = new Entity.TABDICTIONARY();
DataTable ds = dbHelper.GetDataTable(strSql.ToString(), parameters);
if (ds.Rows.Count > 0)
{
model.DICTIONARYID = ds.Rows[0]["DICTIONARYID"].ToString();
model.CONTENT = ds.Rows[0]["CONTENT"].ToString();
model.BEIZHU = ds.Rows[0]["BEIZHU"].ToString();
return(model);
}
else
{
return(null);
}
}
/// <summary>
/// 根据部门ID获取所有的用户名
/// </summary>
/// <param name="code"></param>
/// <returns></returns>
public DataTable GetNameList(string code)
{
StringBuilder strSql = new StringBuilder();
DbAPI dbHelper = new DbAPI();
OracleParameter[] parameters = null;
strSql.Append(" SELECT U.F_ACCOUNT,U.F_NAME FROM DEPARTMENTUSER D ");
strSql.Append(" LEFT JOIN USERINFO U ON D.F_ACCOUNT=U.F_ACCOUNT ");
strSql.Append(" WHERE 1=1 ");
if (!string.IsNullOrEmpty(code))
{
strSql.Append(" AND F_DEPARTMENTCODE= " + code);
}
try
{
dbHelper.OpenConn("");
DataTable dt = dbHelper.GetDataTable(strSql.ToString(), parameters);
dbHelper.CloseConn();
return(dt);
}
catch (Exception ex)
{
LogBN.WriteLog(typeof(DEPARTMENTUSER_BN), "GetNameList 程序段的异常" + ex);
return(null);
}
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public Entity.DEVICEATTACH GetModel()
{
StringBuilder strSql = new StringBuilder();
DbAPI dbHelper = new DbAPI();
strSql.Append("select DEVICECODE, SEAAREA, PROVINCE, BAY, BUREAUDEVICE, LOCALDEVICE, SERVICE ");
strSql.Append(" from DEVICEATTACH ");
strSql.Append(" where ");
OracleParameter[] parameters =
{
};
Entity.DEVICEATTACH model = new Entity.DEVICEATTACH();
DataTable ds = dbHelper.GetDataTable(strSql.ToString(), parameters);
if (ds.Rows.Count > 0)
{
model.DEVICECODE = ds.Rows[0]["DEVICECODE"].ToString();
model.SEAAREA = ds.Rows[0]["SEAAREA"].ToString();
model.PROVINCE = ds.Rows[0]["PROVINCE"].ToString();
model.BAY = ds.Rows[0]["BAY"].ToString();
model.BUREAUDEVICE = ds.Rows[0]["BUREAUDEVICE"].ToString();
model.LOCALDEVICE = ds.Rows[0]["LOCALDEVICE"].ToString();
model.SERVICE = ds.Rows[0]["SERVICE"].ToString();
return(model);
}
else
{
return(null);
}
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public USERROLE GetModel(string F_ID)
{
StringBuilder strSql = new StringBuilder();
DbAPI dbHelper = new DbAPI();
strSql.Append("select F_ID, F_ACCOUNT, F_ROLECODE ");
strSql.Append(" from USERROLE ");
strSql.Append(" where F_ID=:F_ID ");
OracleParameter[] parameters =
{
new OracleParameter(":F_ID", OracleType.VarChar, 36)
};
parameters[0].Value = F_ID;
USERROLE model = new USERROLE();
DataTable ds = dbHelper.GetDataTable(strSql.ToString(), parameters);
if (ds.Rows.Count > 0)
{
model.F_ID = ds.Rows[0]["F_ID"].ToString();
model.F_ACCOUNT = ds.Rows[0]["F_ACCOUNT"].ToString();
model.F_ROLECODE = ds.Rows[0]["F_ROLECODE"].ToString();
return(model);
}
else
{
return(null);
}
}
/// <summary>
/// 根据user account得到一个对象实体
/// </summary>
public Entity.USERINFO GetUserinforModel(string account)
{
var strSql = new StringBuilder();
var dbHelper = new DbAPI();
strSql.Append("select a.F_ACCOUNT,a.F_NAME,a.F_PASSWORD,a.F_EMAIL,a.F_PHONE,a.F_TEL,a.F_DESCRIPTION,a.F_ADDRESS,a.F_REALNAME,d.f_name as F_DEPARTMENTCODE,C.Roleinfos");
strSql.Append(" from USERINFO a");
strSql.Append(" left join departmentuser b");
strSql.Append(" on a.f_account = b.f_account");
strSql.Append(" left join departmentinfo d on b.f_departmentcode = d.f_departmentcode");
strSql.Append(" LEFT JOIN (SELECT A.F_ACCOUNT,");
strSql.Append(" LISTAGG(TO_CHAR(B.F_NAME), ',') WITHIN GROUP(ORDER BY A.F_ACCOUNT) AS Roleinfos");
strSql.Append(" FROM USERROLE A");
strSql.Append(" LEFT JOIN ROLEINFO B");
strSql.Append(" ON A.F_ROLECODE = B.F_ROLECODE");
strSql.Append(" GROUP BY A.F_ACCOUNT) C");
strSql.Append(" ON A.F_ACCOUNT = C.F_ACCOUNT");
strSql.Append(" where a.F_ACCOUNT = :F_ACCOUNT and rownum = 1");
OracleParameter[] parameters =
{
new OracleParameter(":F_ACCOUNT", account)
};
dbHelper.OpenConn("");
var ds = dbHelper.GetDataTable(strSql.ToString(), parameters);
if (ds == null || ds.Rows.Count <= 0)
{
return(null);
}
var row0 = ds.Rows[0];
var model = new Entity.USERINFO
{
F_ACCOUNT = row0["F_ACCOUNT"].ToString(),
F_NAME = row0["F_NAME"].ToString(),
F_PASSWORD = row0["F_PASSWORD"].ToString(),
F_EMAIL = row0["F_EMAIL"].ToString(),
F_PHONE = row0["F_PHONE"].ToString(),
F_TEL = row0["F_TEL"].ToString(),
F_DESCRIPTION = row0["F_DESCRIPTION"].ToString(),
F_ADDRESS = row0["F_ADDRESS"].ToString(),
F_REALNAME = row0["F_REALNAME"].ToString(),
DepartmentNames = row0["F_DEPARTMENTCODE"].ToString(),
RoleNames = row0["Roleinfos"].ToString()
};
dbHelper.CloseConn();
return(model);
}
/// <summary>
/// 获得数据列表
/// </summary>
public DataTable GetList(string strWhere)
{
StringBuilder strSql = new StringBuilder();
DbAPI dbHelper = new DbAPI();
OracleParameter[] parameters = null;
strSql.Append("select * ");
strSql.Append(" FROM DEVICEATTACH ");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
return(dbHelper.GetDataTable(strSql.ToString(), parameters));
}
/// <summary>
/// 获取所有的数据
/// </summary>
/// <returns></returns>
public DataTable GetAll()
{
StringBuilder strSql = new StringBuilder();
DbAPI dbHelper = new DbAPI();
strSql.Append("select * ");
strSql.Append(" FROM DEPARTMENTINFO");
dbHelper.OpenConn("");
DataTable bt = dbHelper.GetDataTable(strSql.ToString(), null);
dbHelper.CloseConn();
return(bt);
}
public string SearchEqipment(DEVICEATTACH model)
{
StringBuilder strSql = new StringBuilder();
DbAPI dbHelper = new DbAPI();
OracleParameter[] parameters = null;
strSql.Append("select * ");
strSql.Append(" FROM DEVICEFULL WHERE 1=1 ");
if (model.SEAAREA != "全部")
{
strSql.Append(" AND SEAAREA = '" + model.SEAAREA + "'");
}
if (model.PROVINCE != "全部")
{
strSql.Append(" AND PROVINCE LIKE '%" + dbHelper.CheckDBNull(model.PROVINCE).ToString() + "%'");
}
if (model.BAY != "全部")
{
strSql.Append(" AND BAY = '" + model.BAY + "'");
}
if (model.BUREAUDEVICE != "全部")
{
strSql.Append(" AND BUREAUDEVICE ='" + model.BUREAUDEVICE + "'");
}
if (model.LOCALDEVICE != "全部")
{
strSql.Append(" AND LOCALDEVICE = '" + model.LOCALDEVICE + "'");
}
if (model.SERVICE != "全部")
{
strSql.Append(" AND SERVICE = '" + model.SERVICE + "'");
}
try
{
dbHelper.OpenConn("");
DataTable dt = new DataTable();
dt = dbHelper.GetDataTable(strSql.ToString(), parameters);
dbHelper.CloseConn();
string result = JsonConvert.SerializeObject(dt, new DataTableConverter());
return(result);
}
catch (Exception ex)
{
LogBN.WriteLog(typeof(DEVICEATTACH_BN), "SearchEqipment 程序段的异常" + ex);
return("");
}
}
/// <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>
/// <returns></returns>
public DataTable GetJcysList()
{
try
{
var strSql = new StringBuilder();
strSql.Append("select * from TABSTD where std_type is not null order by std_id asc");
var dbapi = new DbAPI();
dbapi.OpenConn("");
var rst = dbapi.GetDataTable(strSql.ToString(), null);
dbapi.CloseConn();
return(rst);
}
catch (Exception ex)
{
LogBN.WriteLog(typeof(MonitorLog), "获取监测要素列表GetJcysList 程序段的异常" + ex);
return(null);
}
}
/// <summary>
/// 获得数据列表
/// </summary>
public DataTable GetList(string strWhere)
{
StringBuilder strSql = new StringBuilder();
OracleParameter[] parameters = null;
strSql.Append("select * ");
strSql.Append(" FROM ROLEINFO ");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
var dbapi = new DbAPI();
dbapi.OpenConn("");
var rst = dbapi.GetDataTable(strSql.ToString(), parameters);
dbapi.CloseConn();
return(rst);
}
/// <summary>
/// 根据用户名、密码查询用户是否存在
/// </summary>
/// <param name="user"></param>
/// <returns></returns>
public DataTable GetUserList(Entity.USERINFO user)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(" SELECT F_ACCOUNT,F_NAME,F_REALNAME FROM USERINFO WHERE 1=1 AND F_NAME=:F_NAME AND F_PASSWORD=:F_PASSWORD ");
DbAPI dbHelper = new DbAPI();
List <OracleParameter> list = new List <OracleParameter>();
list.Add(new OracleParameter(":F_NAME", user.F_NAME));
list.Add(new OracleParameter(":F_PASSWORD", user.F_PASSWORD));
dbHelper.OpenConn("");
DataTable dt = dbHelper.GetDataTable(strSql.ToString(), list.ToArray());
dbHelper.CloseConn();
return(dt);
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public Entity.USERINFO GetModel(string F_ACCOUNT)
{
StringBuilder strSql = new StringBuilder();
DbAPI dbHelper = new DbAPI();
strSql.Append("select F_ACCOUNT, F_NAME, F_PASSWORD, F_EMAIL, F_PHONE, F_TEL, F_DESCRIPTION, F_PHOTO, F_ADDRESS, F_REALNAME ");
strSql.Append(" from USERINFO ");
strSql.Append(" where F_ACCOUNT=:F_ACCOUNT ");
OracleParameter[] parameters =
{
new OracleParameter(":F_ACCOUNT", OracleType.VarChar, 36)
};
parameters[0].Value = F_ACCOUNT;
dbHelper.OpenConn("");
Entity.USERINFO model = new Entity.USERINFO();
DataTable ds = dbHelper.GetDataTable(strSql.ToString(), parameters);
if (ds.Rows.Count > 0)
{
model.F_ACCOUNT = ds.Rows[0]["F_ACCOUNT"].ToString();
model.F_NAME = ds.Rows[0]["F_NAME"].ToString();
model.F_PASSWORD = ds.Rows[0]["F_PASSWORD"].ToString();
model.F_EMAIL = ds.Rows[0]["F_EMAIL"].ToString();
model.F_PHONE = ds.Rows[0]["F_PHONE"].ToString();
model.F_TEL = ds.Rows[0]["F_TEL"].ToString();
model.F_DESCRIPTION = ds.Rows[0]["F_DESCRIPTION"].ToString();
if (ds.Rows[0]["F_PHOTO"].ToString() != "")
{
model.F_PHOTO = (byte[])ds.Rows[0]["F_PHOTO"];
}
model.F_ADDRESS = ds.Rows[0]["F_ADDRESS"].ToString();
model.F_REALNAME = ds.Rows[0]["F_REALNAME"].ToString();
dbHelper.CloseConn();
return(model);
}
else
{
return(null);
}
}
/// <summary>
/// 获得数据列表
/// </summary>
public DataTable GetList(string strWhere)
{
StringBuilder strSql = new StringBuilder();
DbAPI dbHelper = new DbAPI();
OracleParameter[] parameters = null;
strSql.Append("select t.*,0 AS \"ISUSERIN\" ");
strSql.Append(" FROM DEPARTMENTINFO t");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
strSql.Append(" ORDER BY t.f_departmentcode ASC");
dbHelper.OpenConn("");
var rst = dbHelper.GetDataTable(strSql.ToString(), parameters);
dbHelper.CloseConn();
return(rst);
}
/// <summary>
/// 获得前几行数据
/// </summary>
public DataTable GetList(int Top, string strWhere, string filedOrder)
{
StringBuilder strSql = new StringBuilder();
DbAPI dbHelper = new DbAPI();
OracleParameter[] parameters = null;
strSql.Append("select ");
if (Top > 0)
{
strSql.Append(" top " + Top.ToString());
}
strSql.Append(" * ");
strSql.Append(" FROM DEVICEATTACH ");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
strSql.Append(" order by " + filedOrder);
return(dbHelper.GetDataTable(strSql.ToString(), parameters));
}
/// <summary>
/// 获取所有的数据
/// </summary>
/// <returns></returns>
public DataTable GetAll()
{
StringBuilder strSql = new StringBuilder();
DbAPI dbHelper = new DbAPI();
strSql.Append("select * ");
strSql.Append(" FROM DEPARTMENTINFO");
dbHelper.OpenConn("");
try
{
DataTable bt = dbHelper.GetDataTable(strSql.ToString(), null);
dbHelper.CloseConn();
return(bt);
}
catch (Exception ex)
{
LogBN.WriteLog(typeof(DEPARTMENTINFO_BN), "GetAll 程序段的异常" + ex);
return(null);
}
}
/// <summary>
/// 获取当前设备信息
/// </summary>
/// <param name="device"></param>
/// <returns></returns>
public DataTablesResult getDeviceRow(string deviceCode)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(" SELECT * ");
strSql.Append(" FROM DEVICEINFO ");
strSql.Append(" WHERE 1=1 ");
List <OracleParameter> list = new List <OracleParameter>();
// 编号
if (!string.IsNullOrEmpty(deviceCode))
{
strSql.Append(" AND DEVICECODE=:DEVICECODE ");
list.Add(new OracleParameter(":DEVICECODE", deviceCode));
}
// strSql.Append(" )INFO ");
try
{
DbAPI dbHelper = new DbAPI();
DataTablesResult result = new DataTablesResult();
dbHelper.OpenConn("");
DataTable bt = dbHelper.GetDataTable(strSql.ToString(), list.ToArray());
result.aaData = bt;
dbHelper.CloseConn();
return(result);
}
catch (Exception ex)
{
LogBN.WriteLog(typeof(DEVICEINFO_BN), "GetDeviceInfo 程序段的异常" + ex);
return(null);
}
}
/// <summary>
/// 获得数据列表
/// </summary>
public DataTable GetList()
{
StringBuilder strSql = new StringBuilder();
DbAPI dbHelper = new DbAPI();
OracleParameter[] parameters = null;
strSql.Append(" SELECT * FROM TABDICTIONARY WHERE DICTIONARYID LIKE '9%' ");
try
{
dbHelper.OpenConn("");
DataTable dt = dbHelper.GetDataTable(strSql.ToString(), parameters);
dbHelper.CloseConn();
return(dt);
}
catch (Exception ex)
{
LogBN.WriteLog(typeof(DICTIONARY_BN), "GetList 程序段的异常" + ex);
return(null);
}
}
/// <summary>
/// 获得数据列表(with user)
/// </summary>
public DataTable GetAllDeptsByUser(string userAccount)
{
var strSql = new StringBuilder();
var dbHelper = new DbAPI();
OracleParameter[] parms =
{
new OracleParameter("F_ACCOUNT", userAccount)
};
strSql.Append("SELECT A.*, DECODE(B.F_ID, NULL, 0, 1) AS \"ISUSERIN\"");
strSql.Append(" FROM DEPARTMENTINFO A");
strSql.Append(" LEFT JOIN (SELECT * FROM DEPARTMENTUSER WHERE F_ACCOUNT = :F_ACCOUNT) B");
strSql.Append(" ON A.F_DEPARTMENTCODE = B.F_DEPARTMENTCODE");
strSql.Append(" ORDER BY A.f_departmentcode ASC");
dbHelper.OpenConn("");
var rst = dbHelper.GetDataTable(strSql.ToString(), parms);
dbHelper.CloseConn();
return(rst);
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public Entity.FUNCTIONINFO GetModel(string F_FUNCTIONCODE)
{
StringBuilder strSql = new StringBuilder();
DbAPI dbHelper = new DbAPI();
strSql.Append("select F_FUNCTIONCODE, F_NAME, F_DESCRIPTION, F_CAPTION, F_PRIORITY, F_PARENTID, URL ");
strSql.Append(" from FUNCTIONINFO ");
strSql.Append(" where F_FUNCTIONCODE=:F_FUNCTIONCODE ");
OracleParameter[] parameters =
{
new OracleParameter(":F_FUNCTIONCODE", OracleType.VarChar, 36)
};
parameters[0].Value = F_FUNCTIONCODE;
Entity.FUNCTIONINFO model = new Entity.FUNCTIONINFO();
DataTable ds = dbHelper.GetDataTable(strSql.ToString(), parameters);
if (ds.Rows.Count > 0)
{
model.F_FUNCTIONCODE = ds.Rows[0]["F_FUNCTIONCODE"].ToString();
model.F_NAME = ds.Rows[0]["F_NAME"].ToString();
model.F_DESCRIPTION = ds.Rows[0]["F_DESCRIPTION"].ToString();
model.F_CAPTION = ds.Rows[0]["F_CAPTION"].ToString();
if (ds.Rows[0]["F_PRIORITY"].ToString() != "")
{
model.F_PRIORITY = decimal.Parse(ds.Rows[0]["F_PRIORITY"].ToString());
}
model.F_PARENTID = ds.Rows[0]["F_PARENTID"].ToString();
model.URL = ds.Rows[0]["URL"].ToString();
return(model);
}
else
{
return(null);
}
}
/// <summary>
/// 导出用户列表到Excel
/// </summary>
/// <param name="queryModel">查询参数</param>
public DataTable GetExcelListUsers(Entity.System.view.QueryModelUser queryModel)
{
try
{
var userName = string.IsNullOrWhiteSpace(queryModel.userName) ? "" : queryModel.userName;
var strSql = "SELECT f_name \"用户名\", f_realname \"真实姓名\", f_email \"邮箱\", f_phone \"固话\", f_tel \"手机\", f_description \"说明\", f_address \"住址\" FROM userinfo where f_name like '%' || :userName || '%'";
OracleParameter[] parameters =
{
new OracleParameter(":userName", userName)
};
var dbapi = new DbAPI();
dbapi.OpenConn("");
var rst = dbapi.GetDataTable(strSql, parameters);
dbapi.CloseConn();
return(rst);
}
catch (Exception ex)
{
LogBN.WriteLog(typeof(MonitorLog), "导出用户列表到Excel方法GetExcelList 程序段的异常" + ex);
return(null);
}
}
/// <summary>
/// 导出角色列表到Excel
/// </summary>
/// <param name="queryModel">查询参数</param>
public DataTable GetExcelList(Entity.System.view.QueryModel queryModel)
{
try
{
var roleName = string.IsNullOrWhiteSpace(queryModel.roleName) ? "" : queryModel.roleName;
var strSql = "select f_rolecode \"编号\",f_name \"名称\",f_description \"备注\" from ROLEINFO where f_name like '%' || :roleName || '%'";
OracleParameter[] parameters =
{
new OracleParameter(":roleName", roleName)
};
var dbapi = new DbAPI();
dbapi.OpenConn("");
var rst = dbapi.GetDataTable(strSql, parameters);
dbapi.CloseConn();
return(rst);
}
catch (Exception ex)
{
LogBN.WriteLog(typeof(MonitorLog), "导出角色列表到Excel方法GetExcelList 程序段的异常" + ex);
return(null);
}
}
/// <summary>
/// 根据设备类型获取设备列表
/// </summary>
/// <param name="devicecode">设备编号</param>
/// <returns></returns>
public DataTable GetDeviceList(string devicecode)
{
try
{
var strSql = new StringBuilder();
strSql.Append("select devicecode,devicename,devicetype from DEVICEINFO where devicetype = (select DEVICETYPE from deviceinfo where devicecode=:devicecode) ");
strSql.Append("order by laytime desc,devicecode asc ");
OracleParameter[] parameters =
{
new OracleParameter(":devicecode", devicecode)
};
var dbapi = new DbAPI();
dbapi.OpenConn("");
var rst = dbapi.GetDataTable(strSql.ToString(), parameters);
dbapi.CloseConn();
return(rst);
}
catch (Exception ex)
{
LogBN.WriteLog(typeof(MonitorLog), "根据设备类型获取设备列表GetDeviceList 程序段的异常" + ex);
return(null);
}
}
/// <summary>
/// 获得数据列表
/// </summary>
public DataTable GetList(string strWhere)
{
StringBuilder strSql = new StringBuilder();
DbAPI dbHelper = new DbAPI();
OracleParameter[] parameters = null;
strSql.Append("select * ");
strSql.Append(" FROM FUNCTIONINFO ");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
try {
dbHelper.OpenConn("");
DataTable dt = new DataTable();
dt = dbHelper.GetDataTable(strSql.ToString(), parameters);
dbHelper.CloseConn();
return(dt);
}catch (Exception ex) {
LogBN.WriteLog(typeof(FUNCTIONINFO_BN), "GetDataTable 程序段的异常" + ex);
return(null);
}
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public Entity.SYSTEMLOG GetModel()
{
StringBuilder strSql = new StringBuilder();
DbAPI dbHelper = new DbAPI();
strSql.Append("select LOG_ID, LOGNAME, TYPE, CONTENT, F_ACCOUNT, REMARK, DATETIME ");
strSql.Append(" from SYSTEMLOG ");
strSql.Append(" where ");
OracleParameter[] parameters =
{
};
Entity.SYSTEMLOG model = new Entity.SYSTEMLOG();
DataTable ds = dbHelper.GetDataTable(strSql.ToString(), parameters);
if (ds.Rows.Count > 0)
{
model.LOG_ID = ds.Rows[0]["LOG_ID"].ToString();
model.LOGNAME = ds.Rows[0]["LOGNAME"].ToString();
model.TYPE = ds.Rows[0]["TYPE"].ToString();
model.CONTENT = ds.Rows[0]["CONTENT"].ToString();
model.F_ACCOUNT = ds.Rows[0]["F_ACCOUNT"].ToString();
model.REMARK = ds.Rows[0]["REMARK"].ToString();
if (ds.Rows[0]["DATETIME"].ToString() != "")
{
model.DATETIME = DateTime.Parse(ds.Rows[0]["DATETIME"].ToString());
}
return(model);
}
else
{
return(null);
}
}
