/// <summary> /// 添加文件 /// </summary> /// <param name="info"></param> /// <returns></returns> public static bool Add(INFO_FILEINFO info) { bool isSuccess = true; StringBuilder pInsertText = new StringBuilder(); pInsertText.Append("INSERT INTO INFO_FILEINFO(FILENAME,FILEURL,UPLOADTIME,FILESIZE,UPLOADUSER) VALUES('"); pInsertText.Append(info.FILENAME + "','" + info.FILEURL + "',TO_DATE('" + info.UPLOADTIME.ToString("yyyy-MM-dd HH:mm:ss") + "','yyyy-mm-dd hh24:mi:ss'),'" + info.FILESIZE + "'," + info.UPLOADUSER + ")"); try { OracleOperateBLL.ExecuteSql(pInsertText.ToString()); } catch { isSuccess = false; } return(isSuccess); //DbBase<INFO_FILEINFO> db = new DbBase<INFO_FILEINFO>(); //db.Insert(info); //if (db.SaveChanges() >= 0) //{ // return true; //} //else //{ // return false; //} }
/// <summary> /// 删除文件 /// </summary> /// <param name="ids"></param> /// <returns></returns> public static bool Delete(int[] ids) { bool isSuccess = true; foreach (var id in ids) { StringBuilder pUpdateText = new StringBuilder(); pUpdateText.Append("DELETE FROM INFO_FILEINFO WHERE ID=" + id); try { OracleOperateBLL.ExecuteSql(pUpdateText.ToString()); } catch { isSuccess = false; } } return(isSuccess); //DbBase<INFO_FILEINFO> db = new DbBase<INFO_FILEINFO>(); //List<INFO_FILEINFO> infoList = new List<INFO_FILEINFO>(); //foreach (var id in ids) //{ // db.Delete(p => p.ID == id); //} //if (db.SaveChanges() >= 0) //{ // return true; //} //else //{ // return false; //} }
public static List <GpsData> GetList_User(string telephone, DateTime stTime, DateTime endTime) { StringBuilder pSBQueryText = new StringBuilder(); pSBQueryText.Append("SELECT LON,LAT,CREATE_TIME,ADDRESS FROM GPS_DATA WHERE CREATE_TIME BETWEEN TO_DATE('" + stTime.ToString("yyyy-MM-dd HH:mm:ss") + "','yyyy-mm-dd hh24:mi:ss') AND TO_DATE('" + endTime.ToString("yyyy-MM-dd HH:mm:ss") + "','yyyy-mm-dd hh24:mi:ss') "); if (string.IsNullOrWhiteSpace(telephone) == false) { pSBQueryText.Append("AND PHONE='" + telephone + "' "); } pSBQueryText.Append(" ORDER BY CREATE_TIME"); var data = OracleOperateBLL.FillDataTable(pSBQueryText.ToString()); List <GpsData> gpsDataList = new List <GpsData>(); foreach (DataRow dr in data.Rows) { GpsData d = new GpsData(); d.Address = dr["ADDRESS"].ToString(); d.Create_Time = DateTime.Parse(dr["CREATE_TIME"].ToString()).ToString("yyyy-MM-dd HH:mm:ss"); var lonAndLat = GpsTranslate.gcj2bd(Convert.ToDouble(dr["LAT"].ToString()), Convert.ToDouble(dr["LON"].ToString())); d.Lat = lonAndLat[0].ToString(); d.Lon = lonAndLat[1].ToString(); gpsDataList.Add(d); } return(gpsDataList); }
/// <summary> /// 添加车辆信息 /// </summary> /// <param name="info"></param> /// <returns></returns> public static bool Add(CFG_CARINFO info) { bool isSuccess = true; StringBuilder pInsertText = new StringBuilder(); pInsertText.Append("INSERT INTO CFG_CARINFO(VEHICLENO,VEHICLETYPE,VEHICLEBRAND,BELONGDEPTID,BELONGNETID,REMARK,ISDELETED,OWNER,OWNERTYPE,CLDWZDSBH,WLWKHM) VALUES('"); pInsertText.Append(info.VEHICLENO + "'," + info.VEHICLETYPE + ",'" + info.VEHICLEBRAND + "'," + info.BELONGDEPTID + "," + info.BELONGNETID + ",'" + info.REMARK + "'," + info.ISDELETED + ",'"); pInsertText.Append(info.OWNER + "'," + info.OWNERTYPE + ",'" + info.CLDWZDSBH + "','" + info.WLWKHM + "')"); try { OracleOperateBLL.ExecuteSql(pInsertText.ToString()); } catch { isSuccess = false; } return(isSuccess); //DbBase<CFG_CARINFO> db = new DbBase<CFG_CARINFO>(); //db.Insert(info); //if (db.SaveChanges() >= 0) //{ // return true; //} //else //{ // return false; //} }
/// <summary> /// 修改车辆信息 /// </summary> /// <param name="info"></param> /// <returns></returns> public static bool Update(CFG_CARINFO info) { bool isSuccess = true; StringBuilder pUpdateText = new StringBuilder(); pUpdateText.Append("UPDATE CFG_CARINFO SET VEHICLENO='" + info.VEHICLENO + "',VEHICLETYPE=" + info.VEHICLETYPE + ",VEHICLEBRAND='" + info.VEHICLEBRAND + "',BELONGDEPTID=" + info.BELONGDEPTID + ",BELONGNETID=" + info.BELONGNETID + ","); pUpdateText.Append("REMARK='" + info.REMARK + "',OWNER='" + info.OWNER + "',OWNERTYPE=" + info.OWNERTYPE + ",CLDWZDSBH='" + info.CLDWZDSBH + "',WLWKHM='" + info.WLWKHM + "' "); pUpdateText.Append("WHERE ID=" + info.ID); try { OracleOperateBLL.ExecuteSql(pUpdateText.ToString()); } catch { isSuccess = false; } return(isSuccess); //DbBase<CFG_CARINFO> db = new DbBase<CFG_CARINFO>(); //db.Update(info); //if (db.SaveChanges() >= 0) //{ // return true; //} //else //{ // return false; //} }
/// <summary> /// 删除用户信息 /// </summary> /// <param name="id"></param> /// <returns></returns> public static bool Delete(int id) { bool isSuccess = true; StringBuilder pUpdateText = new StringBuilder(); pUpdateText.Append("DELETE FROM CFG_USERINFO WHERE ID=" + id); try { OracleOperateBLL.ExecuteSql(pUpdateText.ToString()); } catch { isSuccess = false; } return(isSuccess); //DbBase<CFG_USERINFO> db = new DbBase<CFG_USERINFO>(); //db.Delete(p => p.ID == id); //if (db.SaveChanges() >= 0) //{ // return true; //} //else //{ // return false; //} }
public static List <GpsData> GetList_Car(string cldwzdsbh, DateTime stTime, DateTime endTime) { StringBuilder pSBQueryText = new StringBuilder(); pSBQueryText.Append("SELECT LONGITUDE_BAIDU,LATITUDE_BAIDU,PASSTIME FROM GPS_VEHICLEDATA A LEFT JOIN CFG_VEHICLEINFO B ON A.VEHID=B.ID WHERE A.PASSTIME BETWEEN TO_DATE('" + stTime.ToString("yyyy-MM-dd HH:mm:ss") + "','yyyy-mm-dd hh24:mi:ss') AND TO_DATE('" + endTime.ToString("yyyy-MM-dd HH:mm:ss") + "','yyyy-mm-dd hh24:mi:ss') "); if (string.IsNullOrWhiteSpace(cldwzdsbh) == false) { pSBQueryText.Append("AND B.CLDWZDSBH='" + cldwzdsbh + "' "); } pSBQueryText.Append(" ORDER BY A.PASSTIME DESC"); var data = OracleOperateBLL.FillDataTable(pSBQueryText.ToString()); List <GpsData> gpsDataList = new List <GpsData>(); foreach (DataRow dr in data.Rows) { GpsData d = new GpsData(); //d.Address = dr["ADDRESS"].ToString(); d.Create_Time = DateTime.Parse(dr["PASSTIME"].ToString()).ToString("yyyy-MM-dd HH:mm:ss"); d.Lat = Convert.ToDecimal(dr["LATITUDE_BAIDU"]).ToString("f6"); d.Lon = Convert.ToDecimal(dr["LONGITUDE_BAIDU"]).ToString("f6"); gpsDataList.Add(d); } return(gpsDataList); }
/// <summary> /// 添加用户信息 /// </summary> /// <param name="info"></param> /// <returns></returns> public static bool Add(CFG_USERINFO info) { bool isSuccess = true; StringBuilder pInsertText = new StringBuilder(); pInsertText.Append("INSERT INTO CFG_USERINFO(NAME,SEX,AGE,TELEPHONE,CERTIFICATETYPE,CERTIFICATENUM,DIRECTION,BELONGDEPTID,BELONGNETID,ADDRESS,REMARK,ISDELETED,USERTYPE,VIRTUALTRUMPET,USERNAME,USERPWD,LOGINTYPE) VALUES('"); pInsertText.Append(info.NAME + "'," + info.SEX + ",'" + info.AGE + "','" + info.TELEPHONE + "'," + info.CERTIFICATETYPE + ",'" + info.CERTIFICATENUM + "','" + info.DIRECTION + "',"); pInsertText.Append(info.BELONGDEPTID + "," + info.BELONGNETID + ",'" + info.ADDRESS + "','" + info.REMARK + "'," + info.ISDELETED + "," + info.USERTYPE + ",'"); pInsertText.Append(info.VIRTUALTRUMPET + "','" + info.USERNAME + "','" + info.USERPWD + "'," + info.LOGINTYPE + ")"); try { OracleOperateBLL.ExecuteSql(pInsertText.ToString()); } catch { isSuccess = false; } return(isSuccess); //DbBase<CFG_USERINFO> db = new DbBase<CFG_USERINFO>(); //db.Insert(info); //if (db.SaveChanges() >= 0) //{ // return true; //} //else //{ // return false; //} }
/// <summary> /// 修改用户信息 /// </summary> /// <param name="info"></param> /// <returns></returns> public static bool Update(CFG_USERINFO info) { bool isSuccess = true; StringBuilder pUpdateText = new StringBuilder(); pUpdateText.Append("UPDATE CFG_USERINFO SET NAME='" + info.NAME + "',SEX=" + info.SEX + ",AGE='" + info.AGE + "',TELEPHONE='" + info.TELEPHONE + "',CERTIFICATETYPE=" + info.CERTIFICATETYPE + ","); pUpdateText.Append("CERTIFICATENUM='" + info.CERTIFICATENUM + "',DIRECTION='" + info.DIRECTION + "',BELONGDEPTID=" + info.BELONGDEPTID + ",BELONGNETID=" + info.BELONGNETID + ",ADDRESS='" + info.ADDRESS + "', "); pUpdateText.Append("REMARK='" + info.REMARK + "',USERTYPE=" + info.USERTYPE + ",VIRTUALTRUMPET='" + info.VIRTUALTRUMPET + "',USERNAME='******',USERPWD='" + info.USERPWD + "',LOGINTYPE= " + info.LOGINTYPE + " "); pUpdateText.Append("WHERE ID=" + info.ID); try { OracleOperateBLL.ExecuteSql(pUpdateText.ToString()); } catch { isSuccess = false; } return(isSuccess); //DbBase<CFG_USERINFO> db = new DbBase<CFG_USERINFO>(); //db.Update(info); //if (db.SaveChanges() >= 0) //{ // return true; //} //else //{ // return false; //} }
/// <summary> /// 修改网格信息 /// </summary> /// <param name="info"></param> /// <returns></returns> public static bool Update(CFG_NETINFO info) { bool isSuccess = true; StringBuilder pUpdateText = new StringBuilder(); pUpdateText.Append("UPDATE CFG_NETINFO SET NAME='" + info.NAME + "',POPULATIONINFO='" + info.POPULATIONINFO + "',HOUSEINFO='" + info.HOUSEINFO + "',UNITSTOREINFO='" + info.UNITSTOREINFO + "',BELONGAREA='" + info.BELONGAREA + "',"); pUpdateText.Append("BELONGDEPTID=" + info.BELONGDEPTID + ",REMARK='" + info.REMARK + "',NETCOLOR='" + info.NETCOLOR + "',LONANDLAT='" + info.LONANDLAT + "' "); pUpdateText.Append("WHERE ID=" + info.ID); try { OracleOperateBLL.ExecuteSql(pUpdateText.ToString()); } catch { isSuccess = false; } return(isSuccess); //DbBase<CFG_NETINFO> db = new DbBase<CFG_NETINFO>(); //db.Update(info); //if (db.SaveChanges() >= 0) //{ // return true; //} //else //{ // return false; //} }
/// <summary> /// 获取所有 /// </summary> /// <returns></returns> public static string GetAllInfo() { var info = ""; var now = DateTime.Now; StringBuilder pSBQueryText = new StringBuilder(); pSBQueryText.Append("SELECT A.NAME,A.TELEPHONE AS OBJECTNAME,CASE WHEN B.PASSTIME IS NULL THEN '1753-01-01 00:00:00' ELSE TO_CHAR(B.PASSTIME, 'yyyy-mm-dd hh24:mi:ss') END AS PASSTIME FROM CFG_USERINFO A "); pSBQueryText.Append("LEFT JOIN GPS_REAL B ON A.TELEPHONE = B.OBJECTNAME WHERE A.ISDELETED = 0 AND A.LOGINTYPE=40 "); pSBQueryText.Append("UNION ALL "); pSBQueryText.Append("SELECT A.VEHICLENO AS NAME,TO_CHAR(D.ID) AS OBJECTNAME,CASE WHEN B.PASSTIME IS NULL THEN '1753-01-01 00:00:00' ELSE TO_CHAR(B.PASSTIME, 'yyyy-mm-dd hh24:mi:ss') END AS PASSTIME FROM CFG_CARINFO A "); pSBQueryText.Append("LEFT JOIN CFG_VEHICLEINFO D ON A.CLDWZDSBH = D.CLDWZDSBH LEFT JOIN GPS_REAL B ON A.VEHICLENO = B.OBJECTNAME AND B.OBJECTTYPE=1 WHERE A.ISDELETED = 0 "); var data = OracleOperateBLL.FillDataTable(pSBQueryText.ToString()); foreach (DataRow dr in data.Rows) { var objectName = dr["OBJECTNAME"].ToString(); var passTime = dr["PASSTIME"].ToString(); if (string.IsNullOrWhiteSpace(objectName) == false)//&& now.Subtract(DateTime.Parse(passTime)).TotalSeconds <= 300) { info += objectName + ";"; } } return(info); }
/// <summary> /// 添加网格信息 /// </summary> /// <param name="info"></param> /// <returns></returns> public static bool Add(CFG_NETINFO info) { bool isSuccess = true; StringBuilder pInsertText = new StringBuilder(); pInsertText.Append("INSERT INTO CFG_NETINFO(NAME,POPULATIONINFO,HOUSEINFO,UNITSTOREINFO,BELONGAREA,BELONGDEPTID,REMARK,ISDELETED,NETCOLOR,LONANDLAT) VALUES('"); pInsertText.Append(info.NAME + "','" + info.POPULATIONINFO + "','" + info.HOUSEINFO + "','" + info.UNITSTOREINFO + "','" + info.BELONGAREA + "'," + info.BELONGDEPTID + ",'" + info.REMARK + "',"); pInsertText.Append(info.ISDELETED + ",'" + info.NETCOLOR + "','" + info.LONANDLAT + "')"); try { OracleOperateBLL.ExecuteSql(pInsertText.ToString()); } catch { isSuccess = false; } return(isSuccess); //DbBase<CFG_NETINFO> db = new DbBase<CFG_NETINFO>(); //db.Insert(info); //if (db.SaveChanges() >= 0) //{ // return true; //} //else //{ // return false; //} }
/// <summary> /// 删除网格信息 /// </summary> /// <param name="id"></param> /// <returns></returns> public static bool Delete(int id) { bool isSuccess = true; StringBuilder pUpdateText = new StringBuilder(); pUpdateText.Append("UPDATE CFG_NETINFO SET ISDELETED=1 WHERE ID=" + id); try { OracleOperateBLL.ExecuteSql(pUpdateText.ToString()); } catch { isSuccess = false; } return(isSuccess); //DbBase<CFG_NETINFO> db = new DbBase<CFG_NETINFO>(); //var info = Get(id); //info.ISDELETED = 1; //db.Update(info); //if (db.SaveChanges() >= 0) //{ // return true; //} //else //{ // return false; //} }
/// <summary> /// 修改是否已读 /// </summary> /// <param name="info"></param> /// <returns></returns> public static bool Update(MESSAGE info) { bool isSuccess = true; StringBuilder pUpdateText = new StringBuilder(); pUpdateText.Append("UPDATE MESSAGE SET ISREAD_PLATFORM= " + info.ISREAD_PLATFORM + " "); pUpdateText.Append("WHERE ID=" + info.ID); try { OracleOperateBLL.ExecuteSql(pUpdateText.ToString()); } catch { isSuccess = false; } return(isSuccess); //DbBase<MESSAGE> db = new DbBase<MESSAGE>(); //db.Update(info); //if (db.SaveChanges() >= 0) //{ // return true; //} //else //{ // return false; //} }
/// <summary> /// 添加报警信息 /// </summary> /// <param name="info"></param> /// <returns></returns> public static bool Add(INFO_ALARMINFO info) { bool isSuccess = true; StringBuilder pInsertText = new StringBuilder(); pInsertText.Append("INSERT INTO INFO_ALARMINFO(ALARMINFO,ALARMTIME,ALARMADDRESS,ALARMTYPE,ALARMOBJECTNAME) VALUES('"); pInsertText.Append(info.ALARMINFO + "',TO_DATE('" + info.ALARMTIME.ToString("yyyy-MM-dd HH:mm:ss") + "','yyyy-mm-dd hh24:mi:ss'),'" + info.ALARMADDRESS + "','" + info.ALARMTYPE + "','" + info.ALARMOBJECTNAME + "')"); try { OracleOperateBLL.ExecuteSql(pInsertText.ToString()); } catch { isSuccess = false; } return(isSuccess); //DbBase<INFO_ALARMINFO> db = new DbBase<INFO_ALARMINFO>(); //db.Insert(info); //if (db.SaveChanges() >= 0) //{ // return true; //} //else //{ // return false; //} }
/// <summary> /// 获取报警列表 /// </summary> /// <param name="objectName">对象名称</param> /// <param name="alarmType">报警类型</param> /// <param name="stTime">开始时间</param> /// <param name="endTime">结束时间</param> /// <param name="start"></param> /// <param name="limit"></param> /// <param name="totalCount"></param> /// <returns></returns> public static List <AlarmInfo> GetList(string objectName, int alarmType, DateTime stTime, DateTime endTime, int start, int limit, out int totalCount) { StringBuilder pSBQueryCount = new StringBuilder(); pSBQueryCount.Append("SELECT COUNT(*) FROM INFO_ALARMINFO WHERE ALARMTIME BETWEEN TO_DATE('" + stTime.ToString("yyyy-MM-dd HH:mm:ss") + "','yyyy-mm-dd hh24:mi:ss') AND TO_DATE('" + endTime.ToString("yyyy-MM-dd HH:mm:ss") + "','yyyy-mm-dd hh24:mi:ss') "); if (string.IsNullOrWhiteSpace(objectName) == false) { pSBQueryCount.Append("AND ALARMOBJECTNAME LIKE '%" + objectName + "%'"); } if (alarmType != -1) { pSBQueryCount.Append("AND ALARMTYPE = " + alarmType); } totalCount = OracleOperateBLL.GetQueryCount(pSBQueryCount.ToString()); int pStartNum = limit * (start - 1) + 1; int pEndNum = limit * start; StringBuilder pSBQueryText = new StringBuilder(); pSBQueryText.Append("SELECT ID,ALARMINFO,TO_CHAR(ALARMTIME, 'yyyy-mm-dd hh24:mi:ss') AS ALARMTIME,ALARMADDRESS,ALARMTYPE,ALARMOBJECTNAME FROM (SELECT A.*, rownum r FROM("); pSBQueryText.Append("SELECT * FROM INFO_ALARMINFO WHERE ALARMTIME BETWEEN TO_DATE('" + stTime.ToString("yyyy-MM-dd HH:mm:ss") + "','yyyy-mm-dd hh24:mi:ss') AND TO_DATE('" + endTime.ToString("yyyy-MM-dd HH:mm:ss") + "','yyyy-mm-dd hh24:mi:ss') "); if (string.IsNullOrWhiteSpace(objectName) == false) { pSBQueryText.Append("AND ALARMOBJECTNAME LIKE '%" + objectName + "%'"); } if (alarmType != -1) { pSBQueryText.Append("AND ALARMTYPE = " + alarmType); } pSBQueryText.Append(" ORDER BY ALARMTIME DESC) A "); pSBQueryText.Append("WHERE rownum<=" + pEndNum + ") B WHERE r>=" + pStartNum); var data = OracleOperateBLL.FillDataTable(pSBQueryText.ToString()); List <AlarmInfo> alarmInfoList = new List <AlarmInfo>(); foreach (DataRow dr in data.Rows) { AlarmInfo a = new AlarmInfo(); a.Id = Convert.ToInt32(dr["ID"].ToString()); a.Info = dr["ALARMINFO"].ToString(); a.AlarmTimeStr = dr["ALARMTIME"].ToString(); var type = Convert.ToInt16(dr["ALARMTYPE"].ToString()); if (type == 1) { a.AlarmTypeName = "人员报警"; } else { a.AlarmTypeName = "车辆报警"; } a.AlarmAddress = dr["ALARMADDRESS"].ToString(); a.AlarmObjectName = dr["ALARMOBJECTNAME"].ToString(); alarmInfoList.Add(a); } return(alarmInfoList); }
/// <summary> /// 更新消息 /// </summary> /// <param name="ids"></param> public static void UpdateMessage(List <int> ids) { StringBuilder pSBQueryText = new StringBuilder(); pSBQueryText.Append("UPDATE MESSAGE SET ISSHOW_PLATFORM=1 WHERE ID IN(" + string.Join(",", ids) + ")"); try { OracleOperateBLL.ExecuteSql(pSBQueryText.ToString()); } catch { } }
public static void AddLog(LogInfo info) { StringBuilder pSBQueryText = new StringBuilder(); pSBQueryText.Append("INSERT INTO SYS_LOG(USER_ID,USERNAME,OPERATION,TIME,METHOD,PARAMS,IP,GMT_CREATE) VALUES('" + info.User_Id + "','" + info.UserName + "','" + info.Operation + "','" + 0 + "','" + info.Method + "','" + info.Params + "','" + info.Ip + "'," + "sysdate)"); try { OracleOperateBLL.ExecuteSql(pSBQueryText.ToString()); } catch { } }
/// <summary> /// 更新定位实时信息 /// </summary> /// <param name="objectName"></param> /// <returns></returns> public static void UpdateGpsInfo(string objectName) { StringBuilder pSBQueryText = new StringBuilder(); pSBQueryText.Append("UPDATE GPS_REAL SET LASTALARMTIME=TO_DATE('" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "','yyyy-mm-dd hh24:mi:ss') WHERE OBJECTNAME='" + objectName + "'"); try { OracleOperateBLL.ExecuteSql(pSBQueryText.ToString()); } catch { } }
public static List <NetInfo> GetAll() { StringBuilder pSBQueryText = new StringBuilder(); pSBQueryText.Append("SELECT A.ID,A.BELONGAREA,A.BELONGDEPTID,B.BMVALUE AS BELONGDEPTNAME,A.NAME,A.POPULATIONINFO,A.HOUSEINFO,A.UNITSTOREINFO,A.REMARK,A.NETCOLOR,A.LONANDLAT FROM CFG_NETINFO A "); pSBQueryText.Append("LEFT JOIN SYS_BM_CODE B ON A.BELONGDEPTID=B.BMKEY WHERE A.ISDELETED=0 "); pSBQueryText.Append(" ORDER BY A.NAME"); var data = OracleOperateBLL.FillDataTable(pSBQueryText.ToString()); List <NetInfo> netInfoList = new List <NetInfo>(); foreach (DataRow dr in data.Rows) { NetInfo d = new NetInfo(); d.Id = Convert.ToInt32(dr["ID"].ToString()); d.BelongArea = dr["BELONGAREA"].ToString(); d.BelongDeptId = Convert.ToInt32(dr["BELONGDEPTID"].ToString()); d.BelongDeptName = dr["BELONGDEPTNAME"].ToString(); d.Name = dr["NAME"].ToString(); d.PopulationInfo = dr["POPULATIONINFO"].ToString(); d.HouseInfo = dr["HOUSEINFO"].ToString(); d.UnitStoreInfo = dr["UNITSTOREINFO"].ToString(); d.Remark = dr["REMARK"].ToString(); d.NetColor = dr["NETCOLOR"].ToString(); d.LonAndLat = dr["LONANDLAT"].ToString(); netInfoList.Add(d); } return(netInfoList); //var q = (from n in SlaveDb.Set<CFG_NETINFO>() // join c in SlaveDb.Set<SYS_BM_CODE>() on n.BELONGDEPTID equals c.BMKEY into cc // where n.ISDELETED == 0 // from ccc in cc.DefaultIfEmpty() // select new NetInfo // { // Id = n.ID, // BelongArea = n.BELONGAREA, // BelongDeptName = ccc.BMVALUE, // HouseInfo = n.HOUSEINFO, // LonAndLat = n.LONANDLAT, // PopulationInfo = n.POPULATIONINFO, // Remark = n.REMARK, // UnitStoreInfo = n.UNITSTOREINFO, // NetColor=n.NETCOLOR, // Name = n.NAME // }); //return q.ToList(); }
/// <summary> /// 修改用户密码 /// </summary> /// <param name="userId"></param> /// <param name="password"></param> public static bool UpdateUserPwd(int userId, string password) { bool success = true; StringBuilder pSBQueryText = new StringBuilder(); pSBQueryText.Append("UPDATE CFG_USERINFO SET USERPWD='" + password + "' WHERE ID=" + userId + ""); try { OracleOperateBLL.ExecuteSql(pSBQueryText.ToString()); } catch { success = false; } return(success); }
/// <summary> /// 登录系统 /// </summary> /// <param name="userName">用户名</param> /// <param name="userPwd">密码</param> /// <returns></returns> public static int Login(string userName, string userPwd, out UserInfo userInfo) { userInfo = null; int pResult = -1; //-1:不存在该用户 0:密码不正确 1:登录成功 StringBuilder pSBQueryText = new StringBuilder(); pSBQueryText.Append("SELECT ID,USERNAME,USERPWD,NAME,LOGINTYPE FROM CFG_USERINFO WHERE USERNAME='******' AND ISDELETED=0"); try { var data = OracleOperateBLL.FillDataTable(pSBQueryText.ToString()); if (data.Rows.Count == 0) { pResult = -1; } else if (data.Rows[0]["USERPWD"].ToString() == userPwd) { //看是不是平台用户 if (Convert.ToInt32(data.Rows[0]["LOGINTYPE"]) == 39) { pResult = 1; userInfo = new UserInfo(); userInfo.Id = Convert.ToInt32(data.Rows[0]["ID"]); userInfo.UserName = data.Rows[0]["USERNAME"].ToString(); userInfo.UserPwd = data.Rows[0]["USERPWD"].ToString(); userInfo.Name = data.Rows[0]["NAME"].ToString(); } else { pResult = -2; } } else { pResult = 0; } } catch (Exception error) { } return(pResult); }
/// <summary> /// 获取人员轨迹列表 /// </summary> /// <param name="telephone">电话号码</param> /// <param name="stTime">开始时间</param> /// <param name="endTime">结束时间</param> /// <param name="start"></param> /// <param name="limit"></param> /// <param name="totalCount"></param> /// <returns></returns> public static List <GpsData> GetList_User(string telephone, DateTime stTime, DateTime endTime, int start, int limit, out int totalCount) { StringBuilder pSBQueryCount = new StringBuilder(); pSBQueryCount.Append("SELECT COUNT(*) FROM GPS_DATA WHERE CREATE_TIME BETWEEN TO_DATE('" + stTime.ToString("yyyy-MM-dd HH:mm:ss") + "','yyyy-mm-dd hh24:mi:ss') AND TO_DATE('" + endTime.ToString("yyyy-MM-dd HH:mm:ss") + "','yyyy-mm-dd hh24:mi:ss') "); if (string.IsNullOrWhiteSpace(telephone) == false) { pSBQueryCount.Append("AND PHONE='" + telephone + "'"); } totalCount = OracleOperateBLL.GetQueryCount(pSBQueryCount.ToString()); int pStartNum = limit * (start - 1) + 1; int pEndNum = limit * start; StringBuilder pSBQueryText = new StringBuilder(); pSBQueryText.Append("SELECT LON,LAT,CREATE_TIME,ADDRESS FROM (SELECT A.*, rownum r FROM("); pSBQueryText.Append("SELECT * FROM GPS_DATA WHERE CREATE_TIME BETWEEN TO_DATE('" + stTime.ToString("yyyy-MM-dd HH:mm:ss") + "','yyyy-mm-dd hh24:mi:ss') AND TO_DATE('" + endTime.ToString("yyyy-MM-dd HH:mm:ss") + "','yyyy-mm-dd hh24:mi:ss') "); if (string.IsNullOrWhiteSpace(telephone) == false) { pSBQueryText.Append("AND PHONE='" + telephone + "' "); } pSBQueryText.Append(" ORDER BY CREATE_TIME DESC) A "); pSBQueryText.Append("WHERE rownum<=" + pEndNum + ") B WHERE r>=" + pStartNum); var data = OracleOperateBLL.FillDataTable(pSBQueryText.ToString()); List <GpsData> gpsDataList = new List <GpsData>(); foreach (DataRow dr in data.Rows) { GpsData d = new GpsData(); d.Address = dr["ADDRESS"].ToString(); d.Create_Time = DateTime.Parse(dr["CREATE_TIME"].ToString()).ToString("yyyy-MM-dd HH:mm:ss"); d.Lat = dr["LAT"].ToString(); d.Lon = dr["LON"].ToString(); gpsDataList.Add(d); } return(gpsDataList); }
/// <summary> /// 获取所有人员 /// </summary> /// <returns></returns> public static List <CFG_USERINFO> GetAll() { StringBuilder pSBQueryText = new StringBuilder(); pSBQueryText.Append("SELECT ID,NAME,SEX,AGE,TELEPHONE,CERTIFICATETYPE,CERTIFICATENUM,DIRECTION,BELONGDEPTID,BELONGNETID,ADDRESS,REMARK,ISDELETED,USERTYPE,VIRTUALTRUMPET,USERNAME,USERPWD,LOGINTYPE FROM CFG_USERINFO WHERE ISDELETED=0"); var data = OracleOperateBLL.FillDataTable(pSBQueryText.ToString()); List <CFG_USERINFO> userInfoList = new List <CFG_USERINFO>(); foreach (DataRow dr in data.Rows) { CFG_USERINFO d = new CFG_USERINFO(); d.ID = Convert.ToInt32(dr["ID"]); d.NAME = Convert.ToString(dr["NAME"]); d.SEX = Convert.ToInt32(dr["SEX"]); d.AGE = Convert.ToString(dr["AGE"]); d.TELEPHONE = Convert.ToString(dr["TELEPHONE"]); d.CERTIFICATETYPE = Convert.ToInt32(dr["CERTIFICATETYPE"]); d.CERTIFICATENUM = Convert.ToString(dr["CERTIFICATENUM"]); d.DIRECTION = Convert.ToString(dr["DIRECTION"]); d.BELONGDEPTID = Convert.ToInt32(dr["BELONGDEPTID"]); d.BELONGNETID = Convert.ToInt32(dr["BELONGNETID"]); d.ADDRESS = Convert.ToString(dr["ADDRESS"]); d.REMARK = Convert.ToString(dr["REMARK"]); d.ISDELETED = Convert.ToInt32(dr["ISDELETED"]); d.USERTYPE = Convert.ToInt32(dr["USERTYPE"]); d.VIRTUALTRUMPET = Convert.ToString(dr["VIRTUALTRUMPET"]); d.USERNAME = Convert.ToString(dr["USERNAME"]); d.USERPWD = Convert.ToString(dr["USERPWD"]); d.LOGINTYPE = Convert.ToInt32(dr["LOGINTYPE"]); userInfoList.Add(d); } return(userInfoList); //DbBase<CFG_USERINFO> db = new DbBase<CFG_USERINFO>(); //return db.GetAll(p => p.ISDELETED == 0, "NAME"); }
/// <summary> /// 获取未显示过的消息 /// </summary> /// <param name="phone"></param> /// <returns></returns> public static List <MessageInfo> GetMessageList(string phone) { List <MessageInfo> infoList = new List <MessageInfo>(); List <int> ids = new List <int>(); StringBuilder pSBQueryText = new StringBuilder(); pSBQueryText.Append("SELECT ID,TITLE,ADDRESS,REMARKS FROM MESSAGE WHERE ISSHOW_PLATFORM=0 and PHONE='" + phone + "'"); var data = OracleOperateBLL.FillDataTable(pSBQueryText.ToString()); foreach (DataRow dr in data.Rows) { MessageInfo info = new MessageInfo(); info.Id = Convert.ToInt32(dr["ID"]); info.Title = dr["TITLE"].ToString(); info.Address = dr["ADDRESS"].ToString(); info.Remarks = dr["REMARKS"].ToString(); ids.Add(info.Id); infoList.Add(info); } UpdateMessage(ids); return(infoList); }
/// <summary> /// 获取用户信息列表 /// </summary> /// <param name="name"></param> /// <param name="belongDeptId"></param> /// <param name="start"></param> /// <param name="limit"></param> /// <param name="totalCount"></param> /// <returns></returns> public static List <UserInfo> GetList(string name, string telephone, int belongDeptId, int userType, int start, int limit, out int totalCount) { StringBuilder pSBQueryCount = new StringBuilder(); pSBQueryCount.Append("SELECT COUNT(*) FROM CFG_USERINFO WHERE ISDELETED=0 "); if (string.IsNullOrWhiteSpace(name) == false) { pSBQueryCount.Append("AND NAME like '%" + name + "%' "); } if (string.IsNullOrWhiteSpace(telephone) == false) { pSBQueryCount.Append("AND TELEPHONE like '%" + telephone + "%' "); } if (userType != -1) { pSBQueryCount.Append("AND USERTYPE =" + userType + " "); } if (belongDeptId != -1) { pSBQueryCount.Append("AND BELONGDEPTID =" + belongDeptId); } totalCount = OracleOperateBLL.GetQueryCount(pSBQueryCount.ToString()); int pStartNum = limit * (start - 1) + 1; int pEndNum = limit * start; StringBuilder pSBQueryText = new StringBuilder(); pSBQueryText.Append("SELECT ID,NAME,SEXNAME,AGE,TELEPHONE,CERTIFICATENUM,ADDRESS,BELONGDEPTID,BELONGDEPTNAME,BELONGNETID,BELONGNETNAME,USERTYPE,USERTYPENAME,VIRTUALTRUMPET FROM (SELECT F.*, rownum r FROM("); pSBQueryText.Append("SELECT A.ID,A.NAME,C.BMVALUE AS SEXNAME,A.AGE,A.TELEPHONE,A.CERTIFICATENUM,A.ADDRESS,A.BELONGDEPTID,D.BMVALUE AS BELONGDEPTNAME,A.BELONGNETID,B.NAME AS BELONGNETNAME,A.USERTYPE,E.BMVALUE AS USERTYPENAME,A.VIRTUALTRUMPET FROM CFG_USERINFO A "); pSBQueryText.Append("LEFT JOIN CFG_NETINFO B ON A.BELONGNETID=B.ID "); pSBQueryText.Append("LEFT JOIN SYS_BM_CODE C ON A.SEX=C.BMKEY "); pSBQueryText.Append("LEFT JOIN SYS_BM_CODE D ON A.BELONGDEPTID=D.BMKEY "); pSBQueryText.Append("LEFT JOIN SYS_BM_CODE E ON A.USERTYPE=E.BMKEY WHERE A.ISDELETED=0 "); if (string.IsNullOrWhiteSpace(name) == false) { pSBQueryText.Append("AND A.NAME like '%" + name + "%' "); } if (string.IsNullOrWhiteSpace(telephone) == false) { pSBQueryText.Append("AND A.TELEPHONE like '%" + telephone + "%' "); } if (userType != -1) { pSBQueryText.Append("AND A.USERTYPE =" + userType + " "); } if (belongDeptId != -1) { pSBQueryText.Append("AND A.BELONGDEPTID =" + belongDeptId); } pSBQueryText.Append(" ORDER BY A.USERTYPE DESC,A.BELONGDEPTID DESC,A.BELONGNETID DESC,A.NAME DESC) F "); pSBQueryText.Append("WHERE rownum<=" + pEndNum + ") G WHERE r>=" + pStartNum); var data = OracleOperateBLL.FillDataTable(pSBQueryText.ToString()); List <UserInfo> userInfoList = new List <UserInfo>(); foreach (DataRow dr in data.Rows) { UserInfo d = new UserInfo(); d.Id = Convert.ToInt32(dr["ID"].ToString()); d.Name = dr["NAME"].ToString(); d.SexName = dr["SEXNAME"].ToString(); d.Age = dr["AGE"].ToString(); d.Telephone = dr["TELEPHONE"].ToString(); d.CertificateNum = dr["CERTIFICATENUM"].ToString(); d.Address = dr["ADDRESS"].ToString(); d.BelongDeptId = Convert.ToInt32(dr["BELONGDEPTID"].ToString()); d.BelongDeptName = dr["BELONGDEPTNAME"].ToString(); d.BelongNetId = Convert.ToInt32(dr["BELONGNETID"].ToString()); d.BelongNetName = dr["BELONGNETNAME"].ToString(); d.UserType = Convert.ToInt32(dr["USERTYPE"].ToString()); d.UserTypeName = dr["USERTYPENAME"].ToString(); d.VirtualTrumpet = dr["VIRTUALTRUMPET"].ToString(); userInfoList.Add(d); } return(userInfoList); //var q = (from u in SlaveDb.Set<CFG_USERINFO>() // join n in SlaveDb.Set<CFG_NETINFO>() on u.BELONGNETID equals n.ID into nn // join s in SlaveDb.Set<SYS_BM_CODE>() on u.SEX equals s.BMKEY // join d in SlaveDb.Set<SYS_BM_CODE>() on u.BELONGDEPTID equals d.BMKEY // join t in SlaveDb.Set<SYS_BM_CODE>() on u.USERTYPE equals t.BMKEY // from nnn in nn.DefaultIfEmpty() // where ((name == "" || name == null) ? true : u.NAME.Contains(name)) // && ((telephone == "" || telephone == null) ? true : u.TELEPHONE.Contains(telephone)) // && (userType == -1 ? true : u.USERTYPE == userType) // && (belongDeptId == -1 ? true : u.BELONGDEPTID == belongDeptId) && u.ISDELETED == 0 // select new UserInfo // { // Id = u.ID, // Name = u.NAME, // SexName = s.BMVALUE, // Age = u.AGE, // Telephone = u.TELEPHONE, // CertificateNum = u.CERTIFICATENUM, // Address = u.ADDRESS, // BelongDeptId = u.BELONGDEPTID, // BelongDeptName = d.BMVALUE, // BelongNetId = u.BELONGNETID, // BelongNetName = nnn.NAME, // UserType = u.USERTYPE, // UserTypeName = t.BMVALUE, // VirtualTrumpet = u.VIRTUALTRUMPET // }); //totalCount = q.Count(); //return q.OrderByDescending(p => p.UserType).ThenByDescending(p => p.BelongDeptId).ThenByDescending(p => p.BelongNetId).ThenBy(p => p.Name).Skip((start - 1) * limit).Take(limit).ToList(); }
/// <summary> /// 获取最新定位信息 /// </summary> /// <param name="objectName"></param> /// <returns></returns> public static List <GpsRealData> GetNewGpsInfo(string[] objectNames) { var now = DateTime.Now; StringBuilder pSBQueryText = new StringBuilder(); pSBQueryText.Append("SELECT A.OBJECTNAME AS OBJECTID,LON,LAT,CASE WHEN B.BELONGNETID IS NULL THEN C.BELONGNETID ELSE B.BELONGNETID END AS BELONGNETID,"); pSBQueryText.Append("CASE WHEN B.NAME IS NULL THEN C.VEHICLENO ELSE B.NAME END AS NAME,CASE WHEN B.NAME IS NULL THEN C.OWNER ELSE B.NAME END AS OWNER,TO_CHAR(A.PASSTIME, 'yyyy-mm-dd hh24:mi:ss') AS PASSTIME,A.ADDRESS,CASE WHEN B.NAME IS NULL THEN 2 ELSE 1 END AS TYPE,CASE WHEN B.NAME IS NULL THEN C.VEHICLETYPE ELSE 0 END AS OBJECTTYPE,TO_CHAR(A.LASTALARMTIME,'yyyy-mm-dd hh24:mi:ss') AS LASTALARMTIME FROM GPS_REAL A "); pSBQueryText.Append("LEFT JOIN CFG_USERINFO B ON A.OBJECTNAME = B.TELEPHONE "); pSBQueryText.Append("LEFT JOIN CFG_VEHICLEINFO D ON A.OBJECTNAME = D.ID AND A.OBJECTTYPE = 1 LEFT JOIN CFG_CARINFO C ON D.CLDWZDSBH=C.CLDWZDSBH WHERE A.OBJECTNAME IN('" + string.Join("','", objectNames) + "')"); //获取所有需要提醒的用户手机号码 var phone_Show = OracleOperateBLL.FillDataTable("SELECT DISTINCT PHONE FROM MESSAGE WHERE ISSHOW_PLATFORM=0"); List <string> phone_ShowList = new List <string>(); foreach (DataRow dr in phone_Show.Rows) { phone_ShowList.Add(dr["PHONE"].ToString()); } var data = OracleOperateBLL.FillDataTable(pSBQueryText.ToString()); List <GpsRealData> dataList = new List <GpsRealData>(); foreach (DataRow dr in data.Rows) { var objectId = dr["OBJECTID"].ToString(); var objectName = dr["NAME"].ToString(); var lon = dr["LON"].ToString(); var lat = dr["LAT"].ToString(); var belongNetId = dr["BELONGNETID"].ToString(); var passTime = dr["PASSTIME"].ToString(); var address = dr["ADDRESS"].ToString(); var type = dr["TYPE"].ToString(); var owner = dr["OWNER"].ToString(); var objectType = dr["OBJECTTYPE"].ToString(); var lastAlarmTime = dr["LASTALARMTIME"].ToString(); GpsRealData d = new GpsRealData(); d.ObjectId = objectId; d.ObjectType = objectType; if (string.IsNullOrWhiteSpace(lastAlarmTime)) { d.IsNeedAlarm = true; } else //看最近一次报警时间与当前时间比较,小于一分钟不报警 { var alarmTime = DateTime.Parse(lastAlarmTime); if (now.Subtract(alarmTime).TotalSeconds >= 60) { d.IsNeedAlarm = true; } else { d.IsNeedAlarm = false; } } if (type == "1") //人员 { if (phone_ShowList.Contains(d.ObjectId)) { d.IsNeedShow = true; } else { d.IsNeedShow = false; } d.ObjectName = objectName; var lonAndLat = GpsTranslate.gcj2bd(Convert.ToDouble(lat), Convert.ToDouble(lon)); d.Lat = lonAndLat[0].ToString(); d.Lon = lonAndLat[1].ToString(); } else { d.ObjectName = objectName; d.Lon = lon; d.Lat = lat; } d.BelongNetId = belongNetId; d.Address = address; d.Type = type; //如果时间大于5min,表示离线 if (now.Subtract(DateTime.Parse(passTime)).TotalSeconds >= 300) { d.IsOffline = true; } else { d.IsOffline = false; } dataList.Add(d); } return(dataList); }
/// <summary> /// 获取网格信息列表 /// </summary> /// <param name="name"></param> /// <param name="belongDeptId"></param> /// <param name="start"></param> /// <param name="limit"></param> /// <param name="totalCount"></param> /// <returns></returns> public static List <NetInfo> GetList(string name, int belongDeptId, int start, int limit, out int totalCount) { StringBuilder pSBQueryCount = new StringBuilder(); pSBQueryCount.Append("SELECT COUNT(*) FROM CFG_NETINFO WHERE ISDELETED=0 "); if (string.IsNullOrWhiteSpace(name) == false) { pSBQueryCount.Append("AND NAME like '%" + name + "%' "); } if (belongDeptId != -1) { pSBQueryCount.Append("AND BELONGDEPTID =" + belongDeptId); } totalCount = OracleOperateBLL.GetQueryCount(pSBQueryCount.ToString()); int pStartNum = limit * (start - 1) + 1; int pEndNum = limit * start; StringBuilder pSBQueryText = new StringBuilder(); pSBQueryText.Append("SELECT ID,BELONGAREA,BELONGDEPTID,BELONGDEPTNAME,NAME,POPULATIONINFO,HOUSEINFO,UNITSTOREINFO,REMARK,NETCOLOR,LONANDLAT FROM (SELECT C.*, rownum r FROM("); pSBQueryText.Append("SELECT A.ID,A.BELONGAREA,A.BELONGDEPTID,B.BMVALUE AS BELONGDEPTNAME,A.NAME,A.POPULATIONINFO,A.HOUSEINFO,A.UNITSTOREINFO,A.REMARK,A.NETCOLOR,A.LONANDLAT FROM CFG_NETINFO A "); pSBQueryText.Append("LEFT JOIN SYS_BM_CODE B ON A.BELONGDEPTID=B.BMKEY WHERE A.ISDELETED=0 "); if (string.IsNullOrWhiteSpace(name) == false) { pSBQueryText.Append("AND A.NAME like '%" + name + "%' "); } if (belongDeptId != -1) { pSBQueryText.Append("AND A.BELONGDEPTID =" + belongDeptId); } pSBQueryText.Append(" ORDER BY A.NAME) C "); pSBQueryText.Append("WHERE rownum<=" + pEndNum + ") D WHERE r>=" + pStartNum); var data = OracleOperateBLL.FillDataTable(pSBQueryText.ToString()); List <NetInfo> netInfoList = new List <NetInfo>(); foreach (DataRow dr in data.Rows) { NetInfo d = new NetInfo(); d.Id = Convert.ToInt32(dr["ID"].ToString()); d.BelongArea = dr["BELONGAREA"].ToString(); d.BelongDeptId = Convert.ToInt32(dr["BELONGDEPTID"].ToString()); d.BelongDeptName = dr["BELONGDEPTNAME"].ToString(); d.Name = dr["NAME"].ToString(); d.PopulationInfo = dr["POPULATIONINFO"].ToString(); d.HouseInfo = dr["HOUSEINFO"].ToString(); d.UnitStoreInfo = dr["UNITSTOREINFO"].ToString(); d.Remark = dr["REMARK"].ToString(); d.NetColor = dr["NETCOLOR"].ToString(); d.LonAndLat = dr["LONANDLAT"].ToString(); netInfoList.Add(d); } return(netInfoList); //var q = (from n in SlaveDb.Set<CFG_NETINFO>() // join c in SlaveDb.Set<SYS_BM_CODE>() on n.BELONGDEPTID equals c.BMKEY into cc // where ((name == "" || name == null) ? true : n.NAME.Contains(name)) // && (belongDeptId == -1 ? true : n.BELONGDEPTID == belongDeptId) && n.ISDELETED == 0 // from ccc in cc.DefaultIfEmpty() // select new NetInfo // { // Id = n.ID, // BelongArea = n.BELONGAREA, // BelongDeptName = ccc.BMVALUE, // HouseInfo = n.HOUSEINFO, // LonAndLat = n.LONANDLAT, // PopulationInfo = n.POPULATIONINFO, // Remark = n.REMARK, // UnitStoreInfo = n.UNITSTOREINFO, // NetColor=n.NETCOLOR, // Name = n.NAME // }); //totalCount = q.Count(); //return q.OrderByDescending(p => p.Name).Skip((start - 1) * limit).Take(limit).ToList(); }
/// <summary> /// 获取树 /// </summary> /// <returns></returns> public static List <TreeNode> GetTree() { var now = DateTime.Now; StringBuilder pSBQueryText = new StringBuilder(); pSBQueryText.Append("SELECT * FROM (SELECT A.NAME,A.TELEPHONE AS OBJECTNAME,B.LON,B.LAT,TO_CHAR(B.PASSTIME, 'yyyy-mm-dd hh24:mi:ss') AS PASSTIME,1 AS TYPE,B.ADDRESS,C.BMVALUE AS BELONGDEPTNAME,'' AS OWNER FROM CFG_USERINFO A "); pSBQueryText.Append("LEFT JOIN GPS_REAL B ON A.TELEPHONE = B.OBJECTNAME LEFT JOIN SYS_BM_CODE C ON A.BELONGDEPTID = C.BMKEY WHERE A.ISDELETED = 0 AND A.LOGINTYPE=40 ORDER BY A.BELONGDEPTID,A.NAME)"); pSBQueryText.Append("UNION ALL "); pSBQueryText.Append("SELECT * FROM(SELECT A.VEHICLENO AS NAME,TO_CHAR(D.ID) AS OBJECTNAME,B.LON,B.LAT,TO_CHAR(B.PASSTIME, 'yyyy-mm-dd hh24:mi:ss') AS PASSTIME,2 AS TYPE,B.ADDRESS,C.BMVALUE AS BELONGDEPTNAME,A.OWNER FROM CFG_CARINFO A "); pSBQueryText.Append("LEFT JOIN CFG_VEHICLEINFO D ON A.CLDWZDSBH = D.CLDWZDSBH LEFT JOIN GPS_REAL B ON D.ID = B.OBJECTNAME AND B.OBJECTTYPE=1 LEFT JOIN SYS_BM_CODE C ON A.BELONGDEPTID = C.BMKEY WHERE A.ISDELETED = 0 ORDER BY A.BELONGDEPTID,A.VEHICLENO)"); var data = OracleOperateBLL.FillDataTable(pSBQueryText.ToString()); List <TreeInfo> infoList = new List <TreeInfo>(); foreach (DataRow dr in data.Rows) { var name = dr["NAME"].ToString(); var objectName = dr["OBJECTNAME"].ToString(); var lon = dr["LON"].ToString(); var lat = dr["LAT"].ToString(); var passTime = dr["PASSTIME"].ToString(); var type = dr["TYPE"].ToString(); var address = dr["ADDRESS"].ToString(); var belongDeptName = dr["BELONGDEPTNAME"].ToString(); var owner = dr["OWNER"].ToString(); TreeInfo info = new TreeInfo(); info.Name = name; info.ObjectName = objectName; info.Lon = lon; info.Lat = lat; info.PassTime = passTime; info.Type = type; info.Address = address; info.BelongDeptName = belongDeptName; info.Owner = owner; infoList.Add(info); } List <TreeNode> treeNodeList = new List <TreeNode>(); TreeNode userNode = new TreeNode(); userNode.name = "人员"; userNode.open = true; userNode.@checked = true; userNode.id = "1"; int userOffline = 0; int userCount = 0; //找到所有人员信息 var userInfoList = infoList.Where(p => p.Type == "1").ToList(); var belongDeptList_User = infoList.Where(p => p.Type == "1").Select(p => p.BelongDeptName).Distinct().OrderBy(p => p).ToList(); foreach (var dept in belongDeptList_User) { TreeNode deptNode = new TreeNode(); deptNode.open = false; deptNode.@checked = true; var id = "1_" + belongDeptList_User.IndexOf(dept); deptNode.id = id; var userList = infoList.Where(p => p.Type == "1" && p.BelongDeptName == dept).ToList(); var userOffline_Dept = 0; var userCount_Dept = 0; foreach (var u in userList) { var id_child = id + "_" + userList.IndexOf(u); TreeNode n = new TreeNode(); n.objectName = u.ObjectName; if (string.IsNullOrWhiteSpace(u.PassTime)) //实时表没有数据,表示离线 { n.name = u.Name + "(离线)"; n.id = id_child; n.iconSkin = "dark"; userOffline++; userOffline_Dept++; } else { //如果时间大于5min,表示离线 if (now.Subtract(DateTime.Parse(u.PassTime)).TotalSeconds >= 300) { n.name = u.Name + "(" + u.PassTime + "后离线)"; n.id = id_child; n.iconSkin = "dark"; userOffline++; userOffline_Dept++; } else { n.name = u.Name; n.id = id_child; n.iconSkin = "light"; } } n.@checked = true; deptNode.children.Add(n); userCount++; userCount_Dept++; } deptNode.name = dept + "(" + (userCount_Dept - userOffline_Dept) + "/" + userCount_Dept + ")"; userNode.children.Add(deptNode); } userNode.name = "人员(" + (userCount - userOffline) + "/" + userCount + ")"; TreeNode carNode = new TreeNode(); carNode.name = "车辆"; carNode.open = true; carNode.@checked = true; carNode.id = "2"; int carOffline = 0; int carCount = 0; //找到所有车辆信息 var carInfoList = infoList.Where(p => p.Type == "2").ToList(); var belongDeptList_Car = infoList.Where(p => p.Type == "2").Select(p => p.BelongDeptName).Distinct().OrderBy(p => p).ToList(); foreach (var dept in belongDeptList_Car) { TreeNode deptNode = new TreeNode(); deptNode.open = false; deptNode.@checked = true; var id = "1_" + belongDeptList_Car.IndexOf(dept); deptNode.id = id; var carList = infoList.Where(p => p.Type == "2" && p.BelongDeptName == dept).ToList(); int carOffline_Dept = 0; int carCount_Dept = 0; foreach (var c in carList) { var id_child = id + "_" + carList.IndexOf(c); TreeNode n = new TreeNode(); n.objectName = c.ObjectName; if (string.IsNullOrWhiteSpace(c.PassTime)) //实时表没有数据,表示离线 { n.name = c.Name + "(" + c.Owner + ")" + "(离线)"; n.id = id_child; n.iconSkin = "dark"; carOffline++; carOffline_Dept++; } else { //如果时间大于5min,表示离线 if (now.Subtract(DateTime.Parse(c.PassTime)).TotalSeconds >= 300) { n.name = c.Name + "(" + c.Owner + ")" + "(" + c.PassTime + "后离线)"; n.id = id_child; n.iconSkin = "dark"; carOffline++; carOffline_Dept++; } else { n.name = c.Name + "(" + c.Owner + ")"; n.id = id_child; n.iconSkin = "light"; } } n.@checked = true; deptNode.children.Add(n); carCount++; carCount_Dept++; } deptNode.name = dept + "(" + (carCount_Dept - carOffline_Dept) + "/" + carCount_Dept + ")"; carNode.children.Add(deptNode); } carNode.name = "车辆(" + (carCount - carOffline) + "/" + carCount + ")"; if (userNode.children.Count > 0) { treeNodeList.Add(userNode); } if (carNode.children.Count > 0) { treeNodeList.Add(carNode); } return(treeNodeList); }
/// <summary> /// 获取人员列表 /// </summary> /// <returns></returns> public static Dictionary <string, Dictionary <string, string> > GetUserList() { Dictionary <string, Dictionary <string, string> > m_dic = new Dictionary <string, Dictionary <string, string> >(); Dictionary <string, bool> m_userDic = new Dictionary <string, bool>(); StringBuilder pSBQueryText = new StringBuilder(); pSBQueryText.Append("SELECT A.TELEPHONE,A.ID,A.NAME,A.CERTIFICATENUM,B.BMVALUE FROM CFG_USERINFO A LEFT JOIN SYS_BM_CODE B ON A.USERTYPE=B.BMKEY WHERE A.ISDELETED = 0 ORDER BY USERTYPE, NAME"); try { var data = OracleOperateBLL.FillDataTable(pSBQueryText.ToString()); foreach (DataRow dr in data.Rows) { var userName = dr["NAME"].ToString(); if (m_userDic.ContainsKey(userName) == false) { m_userDic.Add(userName, false); } else { m_userDic[userName] = true; } } foreach (DataRow dr in data.Rows) { var userType = dr["BMVALUE"].ToString(); var userId = dr["ID"].ToString(); var userTelephone = dr["TELEPHONE"].ToString(); var userName = dr["NAME"].ToString(); var userCertificateNum = dr["CERTIFICATENUM"].ToString(); if (string.IsNullOrWhiteSpace(userType)) { userType = "未分类"; } //看是不是已经存在用户类型 if (m_dic.ContainsKey(userType)) { var value = m_dic[userType]; if (value.ContainsKey(userId + ";" + userTelephone) == false) { //看是不是同名 if (m_userDic.ContainsKey(userName)) { var isSame = m_userDic[userName]; if (isSame) { value.Add(userId + ";" + userTelephone, userName + "(" + userCertificateNum + ")"); } else { value.Add(userId + ";" + userTelephone, userName); } } else { value.Add(userId + ";" + userTelephone, userName); } } } else { m_dic.Add(userType, new Dictionary <string, string>()); var value = m_dic[userType]; if (value.ContainsKey(userId + ";" + userTelephone) == false) { //看是不是同名 if (m_userDic.ContainsKey(userName)) { var isSame = m_userDic[userName]; if (isSame) { value.Add(userId + ";" + userTelephone, userName + "(" + userCertificateNum + ")"); } else { value.Add(userId + ";" + userTelephone, userName); } } else { value.Add(userId + ";" + userTelephone, userName); } } } } } catch { } return(m_dic); }