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="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); }