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