public string GetMinitorFunctionTree()
{
try
{
DataTable dataTable = GetList("F_PART = '1'");
DataRow[] rows = dataTable.Select("F_PARENTID='#'");
StringBuilder funTree = new StringBuilder();
funTree.Append("[");
for (int i = 0; i < rows.Length; i++)
{
if (i == rows.Length - 1)
{
CreatFunctionTree(dataTable, rows[i], ref funTree, false);
}
else
{
CreatFunctionTree(dataTable, rows[i], ref funTree, true);
}
}
funTree.Append("]");
return(funTree.ToString());
}
catch (Exception ex)
{
LogBN.WriteLog(typeof(DbAPI), "GetFunctionTree 程序段的异常" + ex);
return(null);
}
}
/// <summary>
/// 新增角色
/// </summary>
/// <param name="roleinfo">角色实体类</param>
/// <returns>是否成功</returns>
public bool AddRoleMain(ROLEINFO roleinfo)
{
var strSql = new StringBuilder();
var dbHelper = new DbAPI();
strSql.Append("INSERT INTO ROLEINFO(F_ROLECODE, F_NAME, F_DESCRIPTION)VALUES(SYS_GUID(),:F_NAME,:F_DESCRIPTION)");
OracleParameter[] parms =
{
new OracleParameter("F_NAME", roleinfo.F_NAME),
new OracleParameter("F_DESCRIPTION", roleinfo.F_DESCRIPTION ?? "")
};
try
{
dbHelper.OpenConn("");
var rstInt = dbHelper.ExecuteNonQuery(strSql.ToString(), parms);
dbHelper.CloseConn();
return(rstInt > 0);
}
catch (Exception ex)
{
LogBN.WriteLog(typeof(ROLEINFO_BN), "新增角色AddRoleMain 程序段的异常" + ex);
return(false);
}
}
/// <summary>
/// 验证用户名是否存在
/// </summary>
/// <param name="userName">用户名</param>
/// <returns>存在返回false,不存在返回true</returns>
public bool ValidateUsername(string userName)
{
var strSql = new StringBuilder();
strSql.Append("SELECT COUNT(1) FROM USERINFO WHERE UPPER(F_NAME) = UPPER(:F_NAME)");
OracleParameter[] parms =
{
new OracleParameter("F_NAME", userName)
};
var dbHelper = new DbAPI();
try
{
dbHelper.OpenConn("");
var rstInt = dbHelper.ExecuteOracleScalar(strSql.ToString(), parms);
dbHelper.CloseConn();
return(int.Parse(rstInt.ToString()) <= 0);
}
catch (Exception ex)
{
LogBN.WriteLog(typeof(ROLEINFO_BN), "验证用户名是否存在ValidateUsername 程序段的异常" + ex);
return(false);
}
}
/// <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 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>
/// 根据部门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>
/// <param name="deptName">部门名称</param>
/// <param name="id">部门ID</param>
/// <returns>是否成功的JSON结果</returns>
public bool UpdateDepartment(string deptName, string id)
{
StringBuilder strSql = new StringBuilder();
DbAPI dbHelper = new DbAPI();
strSql.Append("update departmentinfo set f_name=:deptName where f_departmentcode=:id");
OracleParameter[] parms =
{
new OracleParameter("deptName", deptName),
new OracleParameter("id", id)
};
try
{
dbHelper.OpenConn("");
var rstInt = dbHelper.ExecuteNonQuery(strSql.ToString(), parms);
dbHelper.CloseConn();
return(rstInt > 0);
}
catch (Exception ex)
{
LogBN.WriteLog(typeof(DEPARTMENTINFO_BN), "修改部门AddDepartment 程序段的异常" + ex);
return(false);
}
}
/// <summary>
/// 新增部门
/// </summary>
/// <param name="deptName">部门名称</param>
/// <param name="pid">父节点ID</param>
public bool AddDepartment(string deptName, string pid)
{
StringBuilder strSql = new StringBuilder();
DbAPI dbHelper = new DbAPI();
strSql.Append("insert into departmentinfo(f_departmentcode, f_name, f_description, f_parent)values((SELECT TO_CHAR(MAX(TO_NUMBER(f_departmentcode))+1) FROM departmentinfo),:deptName,'',:pid)");
OracleParameter[] parms =
{
new OracleParameter("deptName", deptName),
new OracleParameter("pid", pid)
};
try
{
dbHelper.OpenConn("");
var rstInt = dbHelper.ExecuteNonQuery(strSql.ToString(), parms);
dbHelper.CloseConn();
return(rstInt > 0);
}
catch (Exception ex)
{
LogBN.WriteLog(typeof(DEPARTMENTINFO_BN), "新增部门AddDepartment 程序段的异常" + ex);
return(false);
}
}
/// <summary>
/// 修改角色
/// </summary>
/// <param name="roleinfo">角色实体类</param>
/// <returns>是否成功</returns>
public bool EditRoleMain(ROLEINFO roleinfo)
{
StringBuilder strSql = new StringBuilder();
DbAPI dbHelper = new DbAPI();
strSql.Append("UPDATE ROLEINFO SET F_NAME=:F_NAME,F_DESCRIPTION=:F_DESCRIPTION WHERE F_ROLECODE=:F_ROLECODE");
OracleParameter[] parms =
{
new OracleParameter("F_NAME", roleinfo.F_NAME),
new OracleParameter("F_DESCRIPTION", roleinfo.F_DESCRIPTION),
new OracleParameter("F_ROLECODE", roleinfo.F_ROLECODE)
};
try
{
dbHelper.OpenConn("");
var rstInt = dbHelper.ExecuteNonQuery(strSql.ToString(), parms);
dbHelper.CloseConn();
return(rstInt > 0);
}
catch (Exception ex)
{
LogBN.WriteLog(typeof(ROLEINFO_BN), "修改角色EditRoleMain 程序段的异常" + ex);
return(false);
}
}
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 ReturnResult DeleteRole(string F_ROLECODE)
{
ReturnResult result = new ReturnResult();
StringBuilder strSql = new StringBuilder();
DbAPI dbHelper = new DbAPI();
try
{
dbHelper.OpenConn("");
dbHelper.OpenTrans();
strSql.Append("delete from ROLEINFO ");
strSql.Append(" where F_ROLECODE=:F_ROLECODE ");
OracleParameter[] parameters =
{
new OracleParameter(":F_ROLECODE", dbHelper.CheckDBNull(F_ROLECODE))
};
int rows = dbHelper.ExecuteNonQuery(strSql.ToString(), parameters);
if (rows > 0)
{
strSql.Clear();
strSql.Append("delete from ROLEFUNCTION ");
strSql.Append(" where F_ROLECODE=:F_ROLECODE ");
OracleParameter[] parameters1 =
{
new OracleParameter(":F_ROLECODE", dbHelper.CheckDBNull(F_ROLECODE))
};
int rows1 = dbHelper.ExecuteNonQuery(strSql.ToString(), parameters1);
if (rows1 <= 0)
{
goto Over;
}
result.success = true;
result.Message = "操作成功";
dbHelper.CommitTrans();
dbHelper.CloseConn();
return(result);
}
else
{
goto Over;
}
}
catch (Exception ex)
{
LogBN.WriteLog(typeof(ROLEINFO_BN), "DeleteRole 程序段的异常" + ex);
goto Over;
}
Over:
result.success = false;
result.Message = "操作失败";
dbHelper.RollBack();
dbHelper.CloseConn();
return(result);
}
/// <summary>
/// 增加一条数据
/// </summary>
public bool Add(Entity.SYSTEMLOG model)
{
StringBuilder strSql = new StringBuilder();
DbAPI dbHelper = new DbAPI();
strSql.Append("insert into SYSTEMLOG(");
strSql.Append("LOG_ID,LOGNAME,TYPE,CONTENT,F_ACCOUNT,REMARK,DATETIME");
strSql.Append(") values (");
strSql.Append(":LOG_ID,:LOGNAME,:TYPE,:CONTENT,:F_ACCOUNT,:REMARK,:DATETIME");
strSql.Append(") ");
OracleParameter[] parameters =
{
new OracleParameter(":LOG_ID", OracleType.VarChar, 36),
new OracleParameter(":LOGNAME", OracleType.VarChar, 256),
new OracleParameter(":TYPE", OracleType.VarChar, 36),
new OracleParameter(":CONTENT", OracleType.VarChar, 1000),
new OracleParameter(":F_ACCOUNT", OracleType.VarChar, 36),
new OracleParameter(":REMARK", OracleType.VarChar, 128),
new OracleParameter(":DATETIME", OracleType.DateTime)
};
parameters[0].Value = dbHelper.GUID();
parameters[1].Value = model.LOGNAME;
parameters[2].Value = model.TYPE;
parameters[3].Value = model.CONTENT;
parameters[4].Value = model.F_ACCOUNT;
parameters[5].Value = model.REMARK;
parameters[6].Value = model.DATETIME;
try
{
dbHelper.OpenConn("");
int result = dbHelper.ExecuteNonQuery(strSql.ToString(), parameters);
dbHelper.CloseConn();
if (result > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (Exception ex)
{
LogBN.WriteLog(typeof(SYSTEMLOG_BN), "Add 程序段的异常" + ex);
return(false);
}
}
/// <summary>
/// 递归获取树节点(with user)
/// </summary>
/// <param name="userAccount">用户名</param>
public List <TreeEntity> GetDepartmentTreeByUser(string userAccount)
{
var dt = string.IsNullOrWhiteSpace(userAccount) ? GetList("") : GetAllDeptsByUser(userAccount);
try
{
var nodes = GetTreeChildrenByUser(0.ToString(), dt);
return(nodes);
}
catch (Exception ex)
{
LogBN.WriteLog(typeof(DEPARTMENTINFO_BN), "递归获取树节点GetDepartmentTreeByUser 程序段的异常" + ex);
return(null);
}
}
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>
/// <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>
/// 删除角色
/// </summary>
/// <param name="id">角色ID</param>
/// <returns>是否成功的JSON结果</returns>
public bool DeleteRole(string id)
{
try
{
OracleParameter[] parameters =
{
new OracleParameter("p_roleCode", id)
};
var dbapi = new DbAPI();
dbapi.OpenConn("");
var rst = dbapi.ExecuteNonQueryByProcedure("DeleteRole", parameters);
dbapi.CloseConn();
return(rst > 0);
}
catch (Exception ex)
{
LogBN.WriteLog(typeof(MonitorLog), "删除部门AddDepartment 程序段的异常" + ex);
return(false);
}
}
/// <summary>
/// 获取部门树
/// </summary>
/// <returns></returns>
public List <TreeEntity> GetDepartmentTree()
{
var dt = GetList("");
try
{
var list = new List <TreeEntity>
{
new TreeEntity
{
id = 0.ToString(),
text = "北海分局",
nodes = GetTreeChildren(0.ToString(), dt)
}
};
return(list);
}
catch (Exception ex)
{
LogBN.WriteLog(typeof(DEPARTMENTINFO_BN), "GetTree 程序段的异常" + 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>
/// 导出用户列表到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>
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>
/// <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);
}
}
/// <summary>
/// 从数据库中获取某个时间段的监控历史记录列表
/// </summary>
public DataTable GetExcelList(QueryModel model)
{
try
{
var strSql = ExcelColumns.SqlStatement[model.order];
OracleParameter[] parameters =
{
new OracleParameter(":devicecode", model.devicecode),
new OracleParameter(":beginTime", model.beginTime),
new OracleParameter(":endTime", model.endTime)
};
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>
public int GetCount(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 COUNT(1) FROM SYSTEMLOG ");
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"));
}
try
{
dbHelper.OpenConn("");
object result = dbHelper.ExecuteOracleScalar(strSql.ToString(), listParams.ToArray());
dbHelper.CloseConn();
return(Convert.ToInt32(result.ToString()));
}
catch (Exception ex)
{
LogBN.WriteLog(typeof(SYSTEMLOG_BN), "GetCount 程序段的异常" + ex);
return(0);
}
}
public string GetMonitorTimeList()
{
try
{
using (DataBaseLayer dataBaseLayer = new DataBaseLayer())
{
DataTable dt = new DataTable();
StringBuilder strBuilder = new StringBuilder("select distinct DT from ZT");
strBuilder.Append("").Append(" t where t.dt like to_date('");
strBuilder.Append("2016-4-23");
strBuilder.Append("','yyyy-mm-dd')and t.id=").Append("0136").Append(" order by DT desc");
//'2016/4/2 1:00:00','yyyy-mm-dd hh24:mi:ss') and t.id=:buoyId
dt = dataBaseLayer.ExecuteQuery(strBuilder.ToString());
if (dt.Rows.Count > 0)
{
List <string> strList = new List <string>();
for (int i = 0; i < dt.Rows.Count; i++)
{
strList.Add(Convert.ToDateTime(dt.Rows[i][0].ToString()).ToLongTimeString());
}
return(JsonConvert.SerializeObject(strList));
}
}
}
catch (Exception ex)
{
LogBN.WriteLog(typeof(USER_BN), "GetMonitorTimeList()当天获得实时数据的时间序列 程序段的异常" + ex);
return("");
}
finally
{
}
return("");
}
/// <summary>
/// 获取生态数据的标准：水质监测参数
/// </summary>
public Entity.BUOYECOLOGY GetModel(string deviceCode)
{
string sql = " SELECT STDTYPE FROM DEVICEATTACH WHERE DEVICECODE=:DEVICECODE ";
DbAPI dbHelper = new DbAPI();
OracleParameter[] parameters =
{
new OracleParameter(":DEVICECODE", deviceCode)
};
try
{
dbHelper.OpenConn("");
DataTable ds = dbHelper.GetDataTable(sql, parameters);
dbHelper.CloseConn();
if (ds.Rows.Count > 0)
{
Entity.BUOYECOLOGY model = new Entity.BUOYECOLOGY();
if (ds.Rows[0]["STDTYPE"].ToString() != "")
{
string stdType = ds.Rows[0]["STDTYPE"].ToString();
StringBuilder strSql = new StringBuilder();
if (stdType == "1")
{
strSql.Append(" SELECT DATETIME,CURRENTTEM, WATERTEM, CTDTEM, SALINITY, CONDUCTIVITY, OXYGEN, PH, TURBIDITY, YLS, YLSTEM, UNDERWATERCO2, OXYGENCHEMICAL, MNO4, CR, NH3N, NO3, NO2, PO4, SIO3, PAHS, \"P\", N, C, S, ALKALINITY, SUSPENSION, CYANIDE, HG, CD, CR6, PB, \"AS\", YLSA, PHYCOCY, PHYCOER, COLIFORM, POTENTIAL, TOTALY, K40, CS134, CS137, CO60, CU, ZN, PHENOL, BOD5, ORGANIC, DETERGENTS, OIL, VIRUS, PETRO, DDT, PCBS, BENZOL, CHLO, ANTIBIOTIC, POISONA, POISONB ");
strSql.Append(" FROM BUOYECOLOGY_LV1 ");
strSql.Append(" WHERE DEVICECODE=:DEVICECODE ");
strSql.Append(" AND DATETIME=(SELECT MAX(DATETIME) DATETIME FROM BUOYECOLOGY_LV1 WHERE DEVICECODE=:DEVICECODE) ");
}
else if (stdType == "2")
{
strSql.Append(" SELECT DATETIME,CURRENTTEM, WATERTEM, CTDTEM, SALINITY, CONDUCTIVITY, OXYGEN, PH, TURBIDITY, YLS, YLSTEM, UNDERWATERCO2, OXYGENCHEMICAL, MNO4, CR, NH3N, NO3, NO2, PO4, SIO3, PAHS, \"P\", N, C, S, ALKALINITY, SUSPENSION, CYANIDE, HG, CD, CR6, PB, \"AS\", YLSA, PHYCOCY, PHYCOER, COLIFORM, POTENTIAL, TOTALY, K40, CS134, CS137, CO60, CU, ZN, PHENOL, BOD5, ORGANIC, DETERGENTS, OIL, VIRUS, PETRO, DDT, PCBS, BENZOL, CHLO, ANTIBIOTIC, POISONA, POISONB ");
strSql.Append(" FROM BUOYECOLOGY_LV2 ");
strSql.Append(" WHERE DEVICECODE=:DEVICECODE ");
strSql.Append(" AND DATETIME=(SELECT MAX(DATETIME) DATETIME FROM BUOYECOLOGY_LV2 WHERE DEVICECODE=:DEVICECODE) ");
}
else if (stdType == "3")
{
strSql.Append(" SELECT DATETIME,CURRENTTEM, WATERTEM, CTDTEM, SALINITY, CONDUCTIVITY, OXYGEN, PH, TURBIDITY, YLS, YLSTEM, UNDERWATERCO2, OXYGENCHEMICAL, MNO4, CR, NH3N, NO3, NO2, PO4, SIO3, PAHS, \"P\", N, C, S, ALKALINITY, SUSPENSION, CYANIDE, HG, CD, CR6, PB, \"AS\", YLSA, PHYCOCY, PHYCOER, COLIFORM, POTENTIAL, TOTALY, K40, CS134, CS137, CO60, CU, ZN, PHENOL, BOD5, ORGANIC, DETERGENTS, OIL, VIRUS, PETRO, DDT, PCBS, BENZOL, CHLO, ANTIBIOTIC, POISONA, POISONB ");
strSql.Append(" FROM BUOYECOLOGY_LV3 ");
strSql.Append(" WHERE DEVICECODE=:DEVICECODE ");
strSql.Append(" AND DATETIME=(SELECT MAX(DATETIME) DATETIME FROM BUOYECOLOGY_LV3 WHERE DEVICECODE=:DEVICECODE) ");
}
else if (stdType == "4")
{
strSql.Append(" SELECT DATETIME,CURRENTTEM, WATERTEM, CTDTEM, SALINITY, CONDUCTIVITY, OXYGEN, PH, TURBIDITY, YLS, YLSTEM, UNDERWATERCO2, OXYGENCHEMICAL, MNO4, CR, NH3N, NO3, NO2, PO4, SIO3, PAHS, \"P\", N, C, S, ALKALINITY, SUSPENSION, CYANIDE, HG, CD, CR6, PB, \"AS\", YLSA, PHYCOCY, PHYCOER, COLIFORM, POTENTIAL, TOTALY, K40, CS134, CS137, CO60, CU, ZN, PHENOL, BOD5, ORGANIC, DETERGENTS, OIL, VIRUS, PETRO, DDT, PCBS, BENZOL, CHLO, ANTIBIOTIC, POISONA, POISONB ");
strSql.Append(" FROM BUOYECOLOGY_LV4 ");
strSql.Append(" WHERE DEVICECODE=:DEVICECODE ");
strSql.Append(" AND DATETIME=(SELECT MAX(DATETIME) DATETIME FROM BUOYECOLOGY_LV4 WHERE DEVICECODE=:DEVICECODE) ");
}
OracleParameter[] parameters2 =
{
new OracleParameter(":DEVICECODE", deviceCode)
};
dbHelper.OpenConn("");
DataTable dt = dbHelper.GetDataTable(strSql.ToString(), parameters2);
dbHelper.CloseConn();
if (dt.Rows.Count > 0)
{
#region 获取字段值
//model.DEVICECODE = ds.Rows[0]["DEVICECODE"].ToString();
if (dt.Rows[0]["DATETIME"].ToString() != "")
{
model.DATETIME = DateTime.Parse(dt.Rows[0]["DATETIME"].ToString());
}
if (dt.Rows[0]["CURRENTTEM"].ToString() != "")
{
model.CURRENTTEM = decimal.Parse(dt.Rows[0]["CURRENTTEM"].ToString());
}
if (dt.Rows[0]["WATERTEM"].ToString() != "")
{
model.WATERTEM = decimal.Parse(dt.Rows[0]["WATERTEM"].ToString());
}
if (dt.Rows[0]["CTDTEM"].ToString() != "")
{
model.CTDTEM = decimal.Parse(dt.Rows[0]["CTDTEM"].ToString());
}
if (dt.Rows[0]["SALINITY"].ToString() != "")
{
model.SALINITY = decimal.Parse(dt.Rows[0]["SALINITY"].ToString());
}
if (dt.Rows[0]["CONDUCTIVITY"].ToString() != "")
{
model.CONDUCTIVITY = decimal.Parse(dt.Rows[0]["CONDUCTIVITY"].ToString());
}
if (dt.Rows[0]["OXYGEN"].ToString() != "")
{
model.OXYGEN = decimal.Parse(dt.Rows[0]["OXYGEN"].ToString());
}
if (dt.Rows[0]["PH"].ToString() != "")
{
model.PH = decimal.Parse(dt.Rows[0]["PH"].ToString());
}
if (dt.Rows[0]["TURBIDITY"].ToString() != "")
{
model.TURBIDITY = decimal.Parse(dt.Rows[0]["TURBIDITY"].ToString());
}
if (dt.Rows[0]["YLS"].ToString() != "")
{
model.YLS = decimal.Parse(dt.Rows[0]["YLS"].ToString());
}
if (dt.Rows[0]["YLSTEM"].ToString() != "")
{
model.YLSTEM = decimal.Parse(dt.Rows[0]["YLSTEM"].ToString());
}
if (dt.Rows[0]["UNDERWATERCO2"].ToString() != "")
{
model.UNDERWATERCO2 = decimal.Parse(dt.Rows[0]["UNDERWATERCO2"].ToString());
}
if (dt.Rows[0]["OXYGENCHEMICAL"].ToString() != "")
{
model.OXYGENCHEMICAL = decimal.Parse(dt.Rows[0]["OXYGENCHEMICAL"].ToString());
}
if (dt.Rows[0]["MNO4"].ToString() != "")
{
model.MNO4 = decimal.Parse(dt.Rows[0]["MNO4"].ToString());
}
if (dt.Rows[0]["CR"].ToString() != "")
{
model.CR = decimal.Parse(dt.Rows[0]["CR"].ToString());
}
if (dt.Rows[0]["NH3N"].ToString() != "")
{
model.NH3N = decimal.Parse(dt.Rows[0]["NH3N"].ToString());
}
if (dt.Rows[0]["NO3"].ToString() != "")
{
model.NO3 = decimal.Parse(dt.Rows[0]["NO3"].ToString());
}
if (dt.Rows[0]["NO2"].ToString() != "")
{
model.NO2 = decimal.Parse(dt.Rows[0]["NO2"].ToString());
}
if (dt.Rows[0]["PO4"].ToString() != "")
{
model.PO4 = decimal.Parse(dt.Rows[0]["PO4"].ToString());
}
if (dt.Rows[0]["SIO3"].ToString() != "")
{
model.SIO3 = decimal.Parse(dt.Rows[0]["SIO3"].ToString());
}
if (dt.Rows[0]["PAHS"].ToString() != "")
{
model.PAHS = decimal.Parse(dt.Rows[0]["PAHS"].ToString());
}
if (dt.Rows[0]["P"].ToString() != "")
{
model.P = decimal.Parse(dt.Rows[0]["P"].ToString());
}
if (dt.Rows[0]["N"].ToString() != "")
{
model.N = decimal.Parse(dt.Rows[0]["N"].ToString());
}
if (dt.Rows[0]["C"].ToString() != "")
{
model.C = decimal.Parse(dt.Rows[0]["C"].ToString());
}
if (dt.Rows[0]["S"].ToString() != "")
{
model.S = decimal.Parse(dt.Rows[0]["S"].ToString());
}
if (dt.Rows[0]["ALKALINITY"].ToString() != "")
{
model.ALKALINITY = decimal.Parse(dt.Rows[0]["ALKALINITY"].ToString());
}
if (dt.Rows[0]["SUSPENSION"].ToString() != "")
{
model.SUSPENSION = decimal.Parse(dt.Rows[0]["SUSPENSION"].ToString());
}
if (dt.Rows[0]["CYANIDE"].ToString() != "")
{
model.CYANIDE = decimal.Parse(dt.Rows[0]["CYANIDE"].ToString());
}
if (dt.Rows[0]["HG"].ToString() != "")
{
model.HG = decimal.Parse(dt.Rows[0]["HG"].ToString());
}
if (dt.Rows[0]["CD"].ToString() != "")
{
model.CD = decimal.Parse(dt.Rows[0]["CD"].ToString());
}
if (dt.Rows[0]["CR6"].ToString() != "")
{
model.CR6 = decimal.Parse(dt.Rows[0]["CR6"].ToString());
}
if (dt.Rows[0]["PB"].ToString() != "")
{
model.PB = decimal.Parse(dt.Rows[0]["PB"].ToString());
}
if (dt.Rows[0]["AS"].ToString() != "")
{
model.AS = decimal.Parse(dt.Rows[0]["AS"].ToString());
}
if (dt.Rows[0]["YLSA"].ToString() != "")
{
model.YLSA = decimal.Parse(dt.Rows[0]["YLSA"].ToString());
}
if (dt.Rows[0]["PHYCOCY"].ToString() != "")
{
model.PHYCOCY = decimal.Parse(dt.Rows[0]["PHYCOCY"].ToString());
}
if (dt.Rows[0]["PHYCOER"].ToString() != "")
{
model.PHYCOER = decimal.Parse(dt.Rows[0]["PHYCOER"].ToString());
}
if (dt.Rows[0]["COLIFORM"].ToString() != "")
{
model.COLIFORM = decimal.Parse(dt.Rows[0]["COLIFORM"].ToString());
}
if (dt.Rows[0]["POTENTIAL"].ToString() != "")
{
model.POTENTIAL = decimal.Parse(dt.Rows[0]["POTENTIAL"].ToString());
}
if (dt.Rows[0]["TOTALY"].ToString() != "")
{
model.TOTALY = decimal.Parse(dt.Rows[0]["TOTALY"].ToString());
}
if (dt.Rows[0]["K40"].ToString() != "")
{
model.K40 = decimal.Parse(dt.Rows[0]["K40"].ToString());
}
if (dt.Rows[0]["CS134"].ToString() != "")
{
model.CS134 = decimal.Parse(dt.Rows[0]["CS134"].ToString());
}
if (dt.Rows[0]["CS137"].ToString() != "")
{
model.CS137 = decimal.Parse(dt.Rows[0]["CS137"].ToString());
}
if (dt.Rows[0]["CO60"].ToString() != "")
{
model.CO60 = decimal.Parse(dt.Rows[0]["CO60"].ToString());
}
if (dt.Rows[0]["CU"].ToString() != "")
{
model.CU = decimal.Parse(dt.Rows[0]["CU"].ToString());
}
if (dt.Rows[0]["ZN"].ToString() != "")
{
model.ZN = decimal.Parse(dt.Rows[0]["ZN"].ToString());
}
if (dt.Rows[0]["PHENOL"].ToString() != "")
{
model.PHENOL = decimal.Parse(dt.Rows[0]["PHENOL"].ToString());
}
if (dt.Rows[0]["BOD5"].ToString() != "")
{
model.BOD5 = decimal.Parse(dt.Rows[0]["BOD5"].ToString());
}
if (dt.Rows[0]["ORGANIC"].ToString() != "")
{
model.ORGANIC = decimal.Parse(dt.Rows[0]["ORGANIC"].ToString());
}
if (dt.Rows[0]["DETERGENTS"].ToString() != "")
{
model.DETERGENTS = decimal.Parse(dt.Rows[0]["DETERGENTS"].ToString());
}
if (dt.Rows[0]["OIL"].ToString() != "")
{
model.OIL = decimal.Parse(dt.Rows[0]["OIL"].ToString());
}
if (dt.Rows[0]["VIRUS"].ToString() != "")
{
model.VIRUS = decimal.Parse(dt.Rows[0]["VIRUS"].ToString());
}
if (dt.Rows[0]["PETRO"].ToString() != "")
{
model.PETRO = decimal.Parse(dt.Rows[0]["PETRO"].ToString());
}
if (dt.Rows[0]["DDT"].ToString() != "")
{
model.DDT = decimal.Parse(dt.Rows[0]["DDT"].ToString());
}
if (dt.Rows[0]["PCBS"].ToString() != "")
{
model.PCBS = decimal.Parse(dt.Rows[0]["PCBS"].ToString());
}
if (dt.Rows[0]["BENZOL"].ToString() != "")
{
model.BENZOL = decimal.Parse(dt.Rows[0]["BENZOL"].ToString());
}
if (dt.Rows[0]["CHLO"].ToString() != "")
{
model.CHLO = decimal.Parse(dt.Rows[0]["CHLO"].ToString());
}
if (dt.Rows[0]["ANTIBIOTIC"].ToString() != "")
{
model.ANTIBIOTIC = decimal.Parse(dt.Rows[0]["ANTIBIOTIC"].ToString());
}
if (dt.Rows[0]["POISONA"].ToString() != "")
{
model.POISONA = decimal.Parse(dt.Rows[0]["POISONA"].ToString());
}
if (dt.Rows[0]["POISONB"].ToString() != "")
{
model.POISONB = decimal.Parse(dt.Rows[0]["POISONB"].ToString());
}
#endregion
//return model;
}
else
{
return(null);
}
}
return(model);
}
else
{
return(null);
}
}
catch (Exception ex)
{
LogBN.WriteLog(typeof(BUOYECOLOGY_BN), "GetModel 程序段的异常" + ex);
return(null);
}
}
