public List <CTLocation> getLcationListByLocate(CTLocation loc) { List <CTLocation> list = new List <CTLocation>(); string sql = "select " + GlobalVar.User.SCHOOLCODE + " from " + GlobalVar.User.TABLE_USER + " where " + GlobalVar.User.UID + "='" + loc.Uid + "'"; DataTable dt_schoolcode = ctSqlHelper.getInstance().Query(sql); if (dt_schoolcode.Rows.Count > 0) { string schoolcode = dt_schoolcode.Rows[0][0].ToString(); string sex = loc.Uid.Substring(loc.Uid.Length - 1); sex = sex == GlobalVar.SEX_MALE ? GlobalVar.SEX_FEMALE : GlobalVar.SEX_MALE; DateTime startTime = DateTime.Parse(loc.Datetime).AddSeconds(-30); DateTime endTime = startTime.AddSeconds(60); string sqlUser = "******" + GlobalVar.Location.TABLE_LOCATION + " a left JOIN " + GlobalVar.User.TABLE_USER + " b on a." + GlobalVar.Location.UID + "=b." + GlobalVar.Location.UID + " where SUBSTRING(a." + GlobalVar.Location.UID + ",LEN(a." + GlobalVar.Location.UID + "),1)='" + sex + "' and b." + GlobalVar.User.SCHOOLCODE + "='" + schoolcode + "' and [" + GlobalVar.Location.TIME + "] BETWEEN '" + startTime.ToString("yyyy-MM-dd HH:mm:ss") + "' and '" + endTime.ToString("yyyy-MM-dd HH:mm:ss") + "'"; DataTable dt_list = ctSqlHelper.getInstance().Query(sqlUser); if (dt_list.Rows.Count > 0) { for (int i = 0; i < dt_list.Rows.Count; i++) { CTLocation tmp = new CTLocation(); tmp.Uid = dt_list.Rows[i][GlobalVar.Location.UID].ToString(); tmp.Latitude = dt_list.Rows[i][GlobalVar.Location.LATITUDE].ToString(); tmp.Longitude = dt_list.Rows[i][GlobalVar.Location.LONGITUDE].ToString(); tmp.Datetime = dt_list.Rows[i][GlobalVar.Location.TIME].ToString(); list.Add(tmp); } } } return(list); }
/// <summary> /// 插入GPS信息 /// </summary> /// <param name="list"></param> /// <returns></returns> public int AddGpsInfo(List <CTLocation> list) { if (list.Count <= 0) { return(0); } try { string sql = "insert into " + GlobalVar.Location.TABLE_LOCATION + " (" + GlobalVar.Location.UID + "," + GlobalVar.Location.LATITUDE + "," + GlobalVar.Location.LONGITUDE + "," + GlobalVar.Location.TIME + ") values"; for (int i = 0; i < list.Count; i++) { CTLocation loc = list[i]; sql += "('" + loc.Uid + "','" + loc.Latitude + "','" + loc.Longitude + "','" + loc.Datetime + "'),"; } sql = sql.Substring(0, sql.Length - 1); return(ctSqlHelper.getInstance().executeSql(sql)); } catch (Exception e) { Console.WriteLine(e.Message); } return(0); }
/// <summary> /// /// 获取对应时间段内所有坐标 /// </summary> /// <param name="uid"></param> /// <param name="startTime"></param> /// <param name="endTime"></param> /// <returns></returns> public List <CTLocation> getLocationByTime(string uid, string startTime, string endTime) { List <CTLocation> list = new List <CTLocation>(); string sql = "select * from " + GlobalVar.Location.TABLE_LOCATION + " where " + GlobalVar.Location.TIME + " between '" + startTime + "' and '" + endTime + "' and " + GlobalVar.Location.UID + "='" + uid + "'"; DataTable dt_loc = ctSqlHelper.getInstance().Query(sql); if (dt_loc.Rows.Count > 0) { for (int i = 0; i < dt_loc.Rows.Count; i++) { CTLocation ctloc = new CTLocation(); ctloc.Datetime = dt_loc.Rows[i][GlobalVar.Location.TIME].ToString(); ctloc.Latitude = dt_loc.Rows[i][GlobalVar.Location.LATITUDE].ToString(); ctloc.Longitude = dt_loc.Rows[i][GlobalVar.Location.LONGITUDE].ToString(); list.Add(ctloc); } } return(list); }