public static object filterCloudTag(string dat, string mood,int hour)
{
// create a list, which can hold linechart instances
List<CloudTag> ctlist = new List<CloudTag>();
//convert to correct date format
//DateTime date = DateTime.Parse(str);
DateTime date = DateTime.ParseExact(dat, "MM-dd-yyyy", null);
string str1 = date.ToString("yyyy'-'MM'-'dd");
// create a linechart class instance so that i
// can talk to database
CloudTag ct = new CloudTag();
ctlist = ct.filterCloudTag(str1,mood.Trim(),hour);
// assign the linechartList to the response object
object response = ctlist;
return response;
}
public List<CloudTag> filterCloudTag(string date, string mood, int hour)
{
List<CloudTag> ctlist = new List<CloudTag>();
using (MySqlConnection cn = new MySqlConnection())
{
using (MySqlCommand cmd = new MySqlCommand())
{
string str = "";
//split mood
string[] moods = mood.Split(new[] { " " }, StringSplitOptions.None);
//rearrange string containing mood for sql query
string moo = moods[0];
for (int i = 1; i < moods.Length; i++)
{
moo += "' AND tr.hour="+hour+" OR tr.date = '"+date +"'AND cr.mood = '" + moods[i];
}
// obtain connection string information from app.config
cn.ConnectionString = "server=localhost; userid=root; password=; database=twitter_stream;";
// tell the cmd to use the cn
cmd.Connection = cn;
// supply the cmd with the necessary SQL Y-M-D FULL
str = "SELECT tag, tr.tagid, count( * ) AS frequency FROM `tagsretrievedtemp` tr INNER JOIN `contentsretrievedtemp` cr ON tr.tagId = cr.tagId WHERE tr.date = '" + date + "' AND cr.mood = '" + moo + "' AND tr.hour =" + hour + " GROUP BY tag";
System.Diagnostics.Debug.WriteLine(str);
cmd.CommandText = str;
//testing/print out sqlquery
System.Diagnostics.Debug.WriteLine(cmd.CommandText);
cn.Open();
// returns the results
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
CloudTag ct = new CloudTag(reader.GetString("tagId"), reader.GetString("tag"), reader.GetInt16("frequency"));
ctlist.Add(ct);
}
}
}
}
return ctlist;
}
public List<CloudTag> getCloudTag(string date,int hour)
{
List<CloudTag> ctlist = new List<CloudTag>();
using (MySqlConnection cn = new MySqlConnection())
{
using (MySqlCommand cmd = new MySqlCommand())
{
// obtain connection string information from app.config
cn.ConnectionString = "server=localhost; userid=root; password=; database=twitter_stream;";
// tell the cmd to use the cn
cmd.Connection = cn;
// supply the cmd with the necessary SQL Y-M-D FULL
cmd.CommandText = "SELECT * FROM tagsretrievedtemp WHERE date = '" + date + "' AND hour ="+hour+"";
//testing/print out sqlquery
System.Diagnostics.Debug.WriteLine(cmd.CommandText);
cn.Open();
// returns the results
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
CloudTag ct = new CloudTag(reader.GetString("tagId"), reader.GetString("tag"), reader.GetInt16("frequency"));
ctlist.Add(ct);
}
}
}
}
return ctlist;
}
