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