}// 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