/// <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="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="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);
}
}
/// <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();
dbHelper.OpenConn("");
DataTable ds = dbHelper.GetDataTable(strSql.ToString(), parameters);
dbHelper.CloseConn();
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>
/// 根据部门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="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="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="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="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="device"></param>
/// <returns></returns>
public int GetDeviceInfoCount(DEVICEINFO device)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(" SELECT COUNT(1) ");
strSql.Append(" FROM DEVICEINFO DI ");
strSql.Append(" LEFT JOIN DEVICEATTACH DA ON DI.DEVICECODE=DA.DEVICECODE ");
strSql.Append(" WHERE 1=1 ");
List <OracleParameter> list = new List <OracleParameter>();
// 设备类型（岸基站、浮标）
if (device.DEVICETYPE > 0)
{
strSql.Append(" AND DEVICETYPE=:DEVICETYPE ");
list.Add(new OracleParameter(":DEVICETYPE", device.DEVICETYPE));
}
if (!string.IsNullOrEmpty(device.SEAAREA))
{
strSql.Append(" AND SEAAREA=:SEAAREA ");
list.Add(new OracleParameter(":SEAAREA", device.SEAAREA));
}
if (!string.IsNullOrEmpty(device.PROVINCE))
{
strSql.Append(" AND PROVINCE=:PROVINCE ");
list.Add(new OracleParameter(":PROVINCE", device.PROVINCE));
}
if (!string.IsNullOrEmpty(device.BAY))
{
strSql.Append(" AND BAY=:BAY ");
list.Add(new OracleParameter(":BAY", device.BAY));
}
if (!string.IsNullOrEmpty(device.BUREAUDEVICE))
{
strSql.Append(" AND BUREAUDEVICE=:BUREAUDEVICE ");
list.Add(new OracleParameter(":BUREAUDEVICE", device.BUREAUDEVICE));
}
if (!string.IsNullOrEmpty(device.LOCALDEVICE))
{
strSql.Append(" AND LOCALDEVICE=:LOCALDEVICE ");
list.Add(new OracleParameter(":LOCALDEVICE", device.LOCALDEVICE));
}
if (!string.IsNullOrEmpty(device.SERVICE))
{
strSql.Append(" AND SERVICE=:SERVICE ");
list.Add(new OracleParameter(":SERVICE", device.SERVICE));
}
DbAPI dbHelper = new DbAPI();
dbHelper.OpenConn("");
object result = dbHelper.ExecuteOracleScalar(strSql.ToString(), list.ToArray());
dbHelper.CloseConn();
return(Convert.ToInt32(result.ToString()));
}
/// <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>
/// 根据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 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>
/// 获取所有的数据
/// </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>
/// <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>
/// <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>
/// 获得数据列表(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 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>
/// 导出角色列表到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);
}
}
