}// db_get_tweet
public static tweets[] db_get_tweet_by_tag( double latitude,
double longitude,
int radius,
string tags)
{
int i = 0;
List<tweets> results = new List<tweets>();
if (radius == 0)
radius = m_cutoff;
using (SqlCommand command = m_conn.CreateCommand())
{
/*
* get tweet_values from tweet_tags: filtered by tags and time
*/
string[] tokens_tags = tags.Split(',');
command.CommandText = "SELECT * FROM tweets_tags WHERE (";
for (i = 0; i < tokens_tags.Length - 1; i++)
{
if (tokens_tags[i].CompareTo("") != 0)
command.CommandText = command.CommandText + "tags LIKE '%" + tokens_tags[i] + "%' OR ";
}
// calculate current_time
if (tokens_tags[i].CompareTo("") != 0)
command.CommandText = command.CommandText + "tags LIKE '%" + tokens_tags[i] + "%' ";
else
{
command.CommandText = command.CommandText.Remove(command.CommandText.Length - 3);
}
command.CommandText = command.CommandText + ") AND ttl+time_stamp > dbo.get_current_time()";
command.CommandText = command.CommandText + " AND dbo.distance(latitude, longitude, "
+ latitude + ", " + longitude + ") < " + radius;
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
tweets t = new tweets();
t.m_tags = reader["tags"].ToString().Trim();
t.m_tweet_value = reader["tweet_value"].ToString().Trim();
results.Add(t);
}// while
}
}
return results.ToArray();
}// db_get_tweet_by_tag
} // end of function
public static tweets[] db_get_tweet(long phone_number,
double latitude,
double longitude,
int radius)
{
int i = 0;
string interests = null;
List<tweets> results = new List<tweets>();
if (radius == 0)
radius = m_cutoff;
using (SqlCommand command = m_conn.CreateCommand())
{
/*
* get tweet_values from tweet_tags: filtered by tags and time
*/
/* get interests form user_profile */
command.CommandText = "SELECT interests FROM user_profile WHERE phone_number=" + phone_number;
using (SqlDataReader reader = command.ExecuteReader())
{
// Loop over the results
while (reader.Read())
{
interests = reader["interests"].ToString().Trim();
}
}
if (interests == null)
return null;
bool flag = false;
string[] tokens_tags = interests.Split(',');
command.CommandText = "SELECT * FROM tweets_tags WHERE (";
for (i = 0; i < tokens_tags.Length - 1; i++)
{
if (tokens_tags[i].CompareTo("") != 0)
{
command.CommandText = command.CommandText + "tags LIKE '%" + tokens_tags[i] + "%' OR ";
flag = true;
}
}
// calculate current_time
if (tokens_tags[i].CompareTo("") != 0)
{
command.CommandText = command.CommandText + "tags LIKE '%" + tokens_tags[i] + "%' OR ";
flag = true;
}
if (flag == true)
{
command.CommandText = command.CommandText.Remove(command.CommandText.Length - 3);
command.CommandText = command.CommandText + ") AND ";
}
else
{
return null;
//command.CommandText = command.CommandText.Remove(command.CommandText.Length - 1);
}
command.CommandText = command.CommandText + "ttl+time_stamp > dbo.get_current_time()";
command.CommandText = command.CommandText + " AND dbo.distance(latitude, longitude, "
+ latitude + ", " + longitude + ") < " + radius;
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
tweets t = new tweets();
t.m_tags = reader["tags"].ToString().Trim();
t.m_tweet_value = reader["tweet_value"].ToString().Trim();
results.Add(t);
}// while
}
}
return results.ToArray();
}// db_get_tweet
