示例#1
0
        /// <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);
            }
        }
示例#3
0
        /// <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);
            }
        }
示例#4
0
        /// <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);
        }
示例#6
0
        /// <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);
            }
        }
示例#12
0
        /// <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);
            }
        }
示例#14
0
        /// <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);
        }
示例#19
0
        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("");
            }
        }
示例#20
0
        /// <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);
            }
        }
示例#22
0
        /// <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()));
        }
示例#23
0
        /// <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);
            }
        }
示例#29
0
        /// <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);
        }
示例#30
0
        /// <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));
        }