/// <summary>
/// 更新一条数据
/// </summary>
public bool Update(Entity.ROLEINFO model)
{
StringBuilder strSql = new StringBuilder();
DbAPI dbHelper = new DbAPI();
strSql.Append("update ROLEINFO set ");
strSql.Append(" F_ROLECODE = :F_ROLECODE , ");
strSql.Append(" F_NAME = :F_NAME , ");
strSql.Append(" F_DESCRIPTION = :F_DESCRIPTION ");
strSql.Append(" where F_ROLECODE=:F_ROLECODE ");
OracleParameter[] parameters =
{
new OracleParameter(":F_ROLECODE", OracleType.VarChar, 36),
new OracleParameter(":F_NAME", OracleType.NVarChar),
new OracleParameter(":F_DESCRIPTION", OracleType.NVarChar)
};
parameters[0].Value = model.F_ROLECODE;
parameters[1].Value = model.F_NAME;
parameters[2].Value = model.F_DESCRIPTION;
int rows = dbHelper.ExecuteNonQuery(strSql.ToString(), parameters);
if (rows > 0)
{
return(true);
}
else
{
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="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 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="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>
public void Add(Entity.DEPARTMENTINFO model)
{
StringBuilder strSql = new StringBuilder();
DbAPI dbHelper = new DbAPI();
strSql.Append("insert into DEPARTMENTINFO(");
strSql.Append("F_DEPARTMENTCODE,F_NAME,F_DESCRIPTION,F_PARENT");
strSql.Append(") values (");
strSql.Append(":F_DEPARTMENTCODE,:F_NAME,:F_DESCRIPTION,:F_PARENT");
strSql.Append(") ");
OracleParameter[] parameters =
{
new OracleParameter(":F_DEPARTMENTCODE", OracleType.VarChar, 36),
new OracleParameter(":F_NAME", OracleType.NVarChar),
new OracleParameter(":F_DESCRIPTION", OracleType.NVarChar),
new OracleParameter(":F_PARENT", OracleType.VarChar, 36)
};
parameters[0].Value = model.F_DEPARTMENTCODE;
parameters[1].Value = model.F_NAME;
parameters[2].Value = model.F_DESCRIPTION;
parameters[3].Value = model.F_PARENT;
dbHelper.ExecuteNonQuery(strSql.ToString(), parameters);
}
/// <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>
public bool Update(Entity.TABDICTIONARY model)
{
StringBuilder strSql = new StringBuilder();
DbAPI dbHelper = new DbAPI();
strSql.Append("update TABDICTIONARY set ");
strSql.Append(" DICTIONARYID = :DICTIONARYID , ");
strSql.Append(" CONTENT = :CONTENT , ");
strSql.Append(" BEIZHU = :BEIZHU ");
strSql.Append(" where ");
OracleParameter[] parameters =
{
new OracleParameter(":DICTIONARYID", OracleType.NVarChar),
new OracleParameter(":CONTENT", OracleType.NVarChar),
new OracleParameter(":BEIZHU", OracleType.NVarChar)
};
parameters[0].Value = model.DICTIONARYID;
parameters[1].Value = model.CONTENT;
parameters[2].Value = model.BEIZHU;
int rows = dbHelper.ExecuteNonQuery(strSql.ToString(), parameters);
if (rows > 0)
{
return(true);
}
else
{
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();
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);
}
}
/// <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 void Add(Entity.DEVICEATTACH model)
{
StringBuilder strSql = new StringBuilder();
DbAPI dbHelper = new DbAPI();
strSql.Append("insert into DEVICEATTACH(");
strSql.Append("DEVICECODE,SEAAREA,PROVINCE,BAY,BUREAUDEVICE,LOCALDEVICE,SERVICE");
strSql.Append(") values (");
strSql.Append(":DEVICECODE,:SEAAREA,:PROVINCE,:BAY,:BUREAUDEVICE,:LOCALDEVICE,:SERVICE");
strSql.Append(") ");
OracleParameter[] parameters =
{
new OracleParameter(":DEVICECODE", OracleType.Char, 6),
new OracleParameter(":SEAAREA", OracleType.VarChar, 50),
new OracleParameter(":PROVINCE", OracleType.VarChar, 50),
new OracleParameter(":BAY", OracleType.VarChar, 50),
new OracleParameter(":BUREAUDEVICE", OracleType.VarChar, 50),
new OracleParameter(":LOCALDEVICE", OracleType.VarChar, 50),
new OracleParameter(":SERVICE", OracleType.VarChar, 50)
};
parameters[0].Value = model.DEVICECODE;
parameters[1].Value = model.SEAAREA;
parameters[2].Value = model.PROVINCE;
parameters[3].Value = model.BAY;
parameters[4].Value = model.BUREAUDEVICE;
parameters[5].Value = model.LOCALDEVICE;
parameters[6].Value = model.SERVICE;
dbHelper.ExecuteNonQuery(strSql.ToString(), parameters);
}
/// <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>
/// 更新一条数据
/// </summary>
public bool Update(USERROLE model)
{
StringBuilder strSql = new StringBuilder();
DbAPI dbHelper = new DbAPI();
strSql.Append("update USERROLE set ");
strSql.Append(" F_ID = :F_ID , ");
strSql.Append(" F_ACCOUNT = :F_ACCOUNT , ");
strSql.Append(" F_ROLECODE = :F_ROLECODE ");
strSql.Append(" where F_ID=:F_ID ");
OracleParameter[] parameters =
{
new OracleParameter(":F_ID", OracleType.VarChar, 36),
new OracleParameter(":F_ACCOUNT", OracleType.VarChar, 36),
new OracleParameter(":F_ROLECODE", OracleType.VarChar, 36)
};
parameters[0].Value = model.F_ID;
parameters[1].Value = model.F_ACCOUNT;
parameters[2].Value = model.F_ROLECODE;
int rows = dbHelper.ExecuteNonQuery(strSql.ToString(), parameters);
if (rows > 0)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// 删除一条数据
/// </summary>
public bool Delete(string F_ID)
{
StringBuilder strSql = new StringBuilder();
DbAPI dbHelper = new DbAPI();
strSql.Append("delete from USERROLE ");
strSql.Append(" where F_ID=:F_ID ");
OracleParameter[] parameters =
{
new OracleParameter(":F_ID", OracleType.VarChar, 36)
};
parameters[0].Value = F_ID;
int rows = dbHelper.ExecuteNonQuery(strSql.ToString(), parameters);
if (rows > 0)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// 增加一条数据
/// </summary>
public void Add(Entity.FUNCTIONINFO model)
{
StringBuilder strSql = new StringBuilder();
DbAPI dbHelper = new DbAPI();
strSql.Append("insert into FUNCTIONINFO(");
strSql.Append("F_FUNCTIONCODE,F_NAME,F_DESCRIPTION,F_CAPTION,F_PRIORITY,F_PARENTID,URL");
strSql.Append(") values (");
strSql.Append(":F_FUNCTIONCODE,:F_NAME,:F_DESCRIPTION,:F_CAPTION,:F_PRIORITY,:F_PARENTID,:URL");
strSql.Append(") ");
OracleParameter[] parameters =
{
new OracleParameter(":F_FUNCTIONCODE", OracleType.VarChar, 36),
new OracleParameter(":F_NAME", OracleType.NVarChar),
new OracleParameter(":F_DESCRIPTION", OracleType.NVarChar),
new OracleParameter(":F_CAPTION", OracleType.NVarChar),
new OracleParameter(":F_PRIORITY", OracleType.Number, 22),
new OracleParameter(":F_PARENTID", OracleType.VarChar, 36),
new OracleParameter(":URL", OracleType.NVarChar)
};
parameters[0].Value = model.F_FUNCTIONCODE;
parameters[1].Value = model.F_NAME;
parameters[2].Value = model.F_DESCRIPTION;
parameters[3].Value = model.F_CAPTION;
parameters[4].Value = model.F_PRIORITY;
parameters[5].Value = model.F_PARENTID;
parameters[6].Value = model.URL;
dbHelper.ExecuteNonQuery(strSql.ToString(), parameters);
}
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 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 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>
/// 获取实时数据列表
/// </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>
/// 更新一条数据
/// </summary>
public bool Update(Entity.USERINFO model)
{
StringBuilder strSql = new StringBuilder();
DbAPI dbHelper = new DbAPI();
strSql.Append("update USERINFO set ");
strSql.Append(" F_ACCOUNT = :F_ACCOUNT , ");
strSql.Append(" F_NAME = :F_NAME , ");
strSql.Append(" F_PASSWORD = :F_PASSWORD , ");
strSql.Append(" F_EMAIL = :F_EMAIL , ");
strSql.Append(" F_PHONE = :F_PHONE , ");
strSql.Append(" F_TEL = :F_TEL , ");
strSql.Append(" F_DESCRIPTION = :F_DESCRIPTION , ");
strSql.Append(" F_PHOTO = :F_PHOTO , ");
strSql.Append(" F_ADDRESS = :F_ADDRESS , ");
strSql.Append(" F_REALNAME = :F_REALNAME ");
strSql.Append(" where F_ACCOUNT=:F_ACCOUNT ");
OracleParameter[] parameters =
{
new OracleParameter(":F_ACCOUNT", OracleType.VarChar, 36),
new OracleParameter(":F_NAME", OracleType.VarChar, 64),
new OracleParameter(":F_PASSWORD", OracleType.VarChar, 128),
new OracleParameter(":F_EMAIL", OracleType.VarChar, 128),
new OracleParameter(":F_PHONE", OracleType.VarChar, 16),
new OracleParameter(":F_TEL", OracleType.VarChar, 16),
new OracleParameter(":F_DESCRIPTION", OracleType.NVarChar),
new OracleParameter(":F_PHOTO", OracleType.Blob),
new OracleParameter(":F_ADDRESS", OracleType.NVarChar),
new OracleParameter(":F_REALNAME", OracleType.NVarChar)
};
parameters[0].Value = model.F_ACCOUNT;
parameters[1].Value = model.F_NAME;
parameters[2].Value = model.F_PASSWORD;
parameters[3].Value = model.F_EMAIL;
parameters[4].Value = model.F_PHONE;
parameters[5].Value = model.F_TEL;
parameters[6].Value = model.F_DESCRIPTION;
parameters[7].Value = model.F_PHOTO;
parameters[8].Value = model.F_ADDRESS;
parameters[9].Value = model.F_REALNAME;
int rows = dbHelper.ExecuteNonQuery(strSql.ToString(), parameters);
if (rows > 0)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// 增加一条数据
/// </summary>
public void Add(Entity.TABBUOYSTATUS model)
{
StringBuilder strSql = new StringBuilder();
DbAPI dbHelper = new DbAPI();
strSql.Append("insert into TABBUOYSTATUS(");
strSql.Append("DEVICECODE,DATETIME,SENDNUM,RECVNUM,LON,LAT,AZIMUTH,VOLTAGE,ANCHOR,WATERALARM,DOORALARM,GPSALARM,FREEMEMO,SENSERSTATUS,RESERV0,RESERV1,RESERV2,BUOYSTATUS");
strSql.Append(") values (");
strSql.Append(":DEVICECODE,:DATETIME,:SENDNUM,:RECVNUM,:LON,:LAT,:AZIMUTH,:VOLTAGE,:ANCHOR,:WATERALARM,:DOORALARM,:GPSALARM,:FREEMEMO,:SENSERSTATUS,:RESERV0,:RESERV1,:RESERV2,:BUOYSTATUS");
strSql.Append(") ");
OracleParameter[] parameters =
{
new OracleParameter(":DEVICECODE", OracleType.Char, 6),
new OracleParameter(":DATETIME", OracleType.DateTime),
new OracleParameter(":SENDNUM", OracleType.Number, 22),
new OracleParameter(":RECVNUM", OracleType.Number, 22),
new OracleParameter(":LON", OracleType.Number, 16),
new OracleParameter(":LAT", OracleType.Number, 16),
new OracleParameter(":AZIMUTH", OracleType.Number, 22),
new OracleParameter(":VOLTAGE", OracleType.Number, 16),
new OracleParameter(":ANCHOR", OracleType.Number, 22),
new OracleParameter(":WATERALARM", OracleType.Number, 22),
new OracleParameter(":DOORALARM", OracleType.Number, 22),
new OracleParameter(":GPSALARM", OracleType.Number, 22),
new OracleParameter(":FREEMEMO", OracleType.Number, 22),
new OracleParameter(":SENSERSTATUS", OracleType.Number, 22),
new OracleParameter(":RESERV0", OracleType.Number, 16),
new OracleParameter(":RESERV1", OracleType.Number, 16),
new OracleParameter(":RESERV2", OracleType.Number, 16),
new OracleParameter(":BUOYSTATUS", OracleType.Number, 22)
};
parameters[0].Value = model.DEVICECODE;
parameters[1].Value = model.DATETIME;
parameters[2].Value = model.SENDNUM;
parameters[3].Value = model.RECVNUM;
parameters[4].Value = model.LON;
parameters[5].Value = model.LAT;
parameters[6].Value = model.AZIMUTH;
parameters[7].Value = model.VOLTAGE;
parameters[8].Value = model.ANCHOR;
parameters[9].Value = model.WATERALARM;
parameters[10].Value = model.DOORALARM;
parameters[11].Value = model.GPSALARM;
parameters[12].Value = model.FREEMEMO;
parameters[13].Value = model.SENSERSTATUS;
parameters[14].Value = model.RESERV0;
parameters[15].Value = model.RESERV1;
parameters[16].Value = model.RESERV2;
parameters[17].Value = model.BUOYSTATUS;
dbHelper.ExecuteNonQuery(strSql.ToString(), parameters);
}
/// <summary>
/// 增加一条数据
/// </summary>
public void Add(Entity.DEVICEINFO model)
{
StringBuilder strSql = new StringBuilder();
DbAPI dbHelper = new DbAPI();
strSql.Append("insert into DEVICEINFO(");
strSql.Append("DEVICECODE,DEVICENAME,DEVICEUSER,DEVICETYPE,LAYTIME,ELON,ELAT,COMTYPE,STRUCTTYPE,COMARRAY,PACKNUM,RUNNINGSTATUS,POSITION,PRODUCER,MANAGER,RESERV0,RESERV1,RESERV2");
strSql.Append(") values (");
strSql.Append(":DEVICECODE,:DEVICENAME,:DEVICEUSER,:DEVICETYPE,:LAYTIME,:ELON,:ELAT,:COMTYPE,:STRUCTTYPE,:COMARRAY,:PACKNUM,:RUNNINGSTATUS,:POSITION,:PRODUCER,:MANAGER,:RESERV0,:RESERV1,:RESERV2");
strSql.Append(") ");
OracleParameter[] parameters =
{
new OracleParameter(":DEVICECODE", OracleType.Char, 6),
new OracleParameter(":DEVICENAME", OracleType.VarChar, 20),
new OracleParameter(":DEVICEUSER", OracleType.VarChar, 30),
new OracleParameter(":DEVICETYPE", OracleType.Number, 22),
new OracleParameter(":LAYTIME", OracleType.DateTime),
new OracleParameter(":ELON", OracleType.Number, 16),
new OracleParameter(":ELAT", OracleType.Number, 16),
new OracleParameter(":COMTYPE", OracleType.VarChar, 50),
new OracleParameter(":STRUCTTYPE", OracleType.Number, 22),
new OracleParameter(":COMARRAY", OracleType.Blob),
new OracleParameter(":PACKNUM", OracleType.Number, 22),
new OracleParameter(":RUNNINGSTATUS", OracleType.Number, 22),
new OracleParameter(":POSITION", OracleType.VarChar, 20),
new OracleParameter(":PRODUCER", OracleType.VarChar, 30),
new OracleParameter(":MANAGER", OracleType.VarChar, 30),
new OracleParameter(":RESERV0", OracleType.VarChar, 50),
new OracleParameter(":RESERV1", OracleType.VarChar, 50),
new OracleParameter(":RESERV2", OracleType.VarChar, 50)
};
parameters[0].Value = model.DEVICECODE;
parameters[1].Value = model.DEVICENAME;
parameters[2].Value = model.DEVICEUSER;
parameters[3].Value = model.DEVICETYPE;
parameters[4].Value = model.LAYTIME;
parameters[5].Value = model.ELON;
parameters[6].Value = model.ELAT;
parameters[7].Value = model.COMTYPE;
parameters[8].Value = model.STRUCTTYPE;
parameters[9].Value = model.COMARRAY;
parameters[10].Value = model.PACKNUM;
parameters[11].Value = model.RUNNINGSTATUS;
parameters[12].Value = model.POSITION;
parameters[13].Value = model.PRODUCER;
parameters[14].Value = model.MANAGER;
parameters[15].Value = model.RESERV0;
parameters[16].Value = model.RESERV1;
parameters[17].Value = model.RESERV2;
dbHelper.ExecuteNonQuery(strSql.ToString(), parameters);
}
/// <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);
}
/// <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));
}