public static object getLineChart(string calendarClickDate)
{
// create a list, which can hold linechart instances
List<LineChart> linechartList = new List<LineChart>();
//convert to correct date format
string format = "MM-dd-yyyy";
string format2 = "MM/dd/yyyy";
DateTime dateTime;
string str1 = "";
if (DateTime.TryParseExact(calendarClickDate, format, CultureInfo.InvariantCulture, DateTimeStyles.None, out dateTime))
{
str1 = dateTime.ToString("yyyy'-'MM'-'dd");
}
else if (DateTime.TryParseExact(calendarClickDate, format2, CultureInfo.InvariantCulture, DateTimeStyles.None, out dateTime))
{
str1 = dateTime.ToString("yyyy'-'MM'-'dd");
}
// create a linechart class instance so that i
// can talk to database
LineChart linechart = new LineChart();
linechartList = linechart.getLineChart(str1);
// assign the linechartList to the response object
object response = linechartList;
return response;
}
public static int getLiveCount()
{
int liveCount = 0;
// create a linechart class instance so that i
// can talk to database
LineChart linechart = new LineChart();
liveCount = linechart.getLiveCount();
return liveCount;
}
//private static string GetTextFromDataTable(DataTable dataTable)
//{
// var stringBuilder = new StringBuilder();
// stringBuilder.AppendLine(string.Join("\t", dataTable.Columns.Cast<DataColumn>().Select(arg => arg.ColumnName)));
// foreach (DataRow dataRow in dataTable.Rows)
// stringBuilder.AppendLine(string.Join("\t", dataRow.ItemArray.Select(arg => arg.ToString())));
// return stringBuilder.ToString();
//}
//method for getting data and putting it inside tsv file
public void getDataForBubble(string date, string hour, string path)
{
LineChart lc = new LineChart();
StringBuilder sb = new StringBuilder();
//get all the keywords/tags from the clicked date and hour
//List<String> list = lc.getKeyword(date, hour);
//for every keyword, get frequency of mood and put inside stringbuilder
// foreach (string s in list)
// {
//sb.AppendLine(s + "\t" + lc.getFrequencyJoy(s) + "\t" + lc.getFrequencyAnger(s) + "\t" + lc.getFrequencySadness(s) + "\t" + lc.getFrequencySurprised(s) + "\t" + lc.getFrequencyDisgusted(s));
//lc.getFrequencyJoy(s);
//lc.getFrequencyAnger(s);
//lc.getFrequencySadness(s);
//lc.getFrequencySurprised(s);
//lc.getFrequencyDisgusted(s);
// }
//write the data into tsv file for graph display
File.WriteAllText(path, sb.ToString());
}
public List<LineChart> getLineChart(string str)
{
int minX = 0;
int maxX = 0;
ArrayList hours = new ArrayList();
DataSet ds = new DataSet();
DataSet ds1 = new DataSet();
DataSet ds2 = new DataSet();
// empty list
List<LineChart> linechartList = new List<LineChart>();
using (MySqlConnection cn = new MySqlConnection())
{
using (MySqlCommand cmd = new MySqlCommand())
{
using (MySqlDataAdapter da = new MySqlDataAdapter(), da1 = new MySqlDataAdapter(), da2 = new MySqlDataAdapter())
{
// 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 = '" + str + "' GROUP BY date,hour;";
//testing/print out sqlquery
System.Diagnostics.Debug.WriteLine(cmd.CommandText);
// tell the DataAdapter to use the cmd
da.SelectCommand = cmd;
// open an active connection
cn.Open();
// rrturns the results
da.Fill(ds, "Rate");
cmd.CommandText = "SELECT Min(CAST(`hour` AS UNSIGNED)) FROM tagsretrievedtemp WHERE date = '" + str + "';";
if (cmd.ExecuteScalar().ToString() != "")
{
minX = int.Parse(cmd.ExecuteScalar().ToString());
cmd.CommandText = "SELECT Max(CAST(`hour` AS UNSIGNED)) FROM tagsretrievedtemp WHERE date = '" + str + "';";
if (cmd.ExecuteScalar().ToString() != "")
{
maxX = int.Parse(cmd.ExecuteScalar().ToString());
}
}
if (maxX != 0)
{
// get the actually value of hour
cmd.CommandText = "SELECT hour FROM tagsretrievedtemp WHERE date = '" + str + "' GROUP BY date,hour;";
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
hours.Add(reader.GetString(0));
}
}
// reuse cmd variable
// count frequency of a individual mood for a given date and time
foreach (string hour in hours)
{
cmd.CommandText = "SELECT(SELECT count(*) FROM tagsretrievedtemp tr INNER JOIN contentsretrievedtemp ct ON tr.tagId = ct.tagId WHERE `date` = '" + str + "' and hour='" + hour + "' and mood = 'joy') AS joy,"
+ "(SELECT count(*) FROM tagsretrievedtemp tr INNER JOIN contentsretrievedtemp ct ON tr.tagId = ct.tagId WHERE `date` = '" + str + "' and hour='" + hour + "' and mood = 'sadness') AS sadness,"
+ "(SELECT count(*) FROM tagsretrievedtemp tr INNER JOIN contentsretrievedtemp ct ON tr.tagId = ct.tagId WHERE `date` = '" + str + "' and hour='" + hour + "' and mood = 'surprised') AS surprised,"
+ "(SELECT count(*) FROM tagsretrievedtemp tr INNER JOIN contentsretrievedtemp ct ON tr.tagId = ct.tagId WHERE `date` = '" + str + "' and hour='" + hour + "' and mood = 'anger') AS anger,"
+ "(SELECT count(*) FROM tagsretrievedtemp tr INNER JOIN contentsretrievedtemp ct ON tr.tagId = ct.tagId WHERE `date` = '" + str + "' and hour='" + hour + "' and mood = 'disgusted') AS disgusted";
// tell the DataAdapter to use the cmd
da1.SelectCommand = cmd;
// returns the results
da1.Fill(ds1, "mood");
}
// get the length, incase some number missing between minX and maxX
cmd.CommandText = "SELECT count(distinct(hour)) AS maxLength FROM tagsretrievedtemp WHERE date = '" + str + "';";
int length = int.Parse(cmd.ExecuteScalar().ToString());
// tell the DataAdapter to use the cmd
da2.SelectCommand = cmd;
// returns the results
da2.Fill(ds2, "maxLength");
}
// close the connection
cn.Close();
}
}
}
// loop throught the datarows in the DataTable
// fetch all the data and pump them into list
foreach (DataRow dr in ds.Tables["Rate"].Rows)
{
LineChart linechart = new LineChart();
linechart.Hour = dr["hour"].ToString();
linechart.Frequency = dr["frequency"].ToString();
linechartList.Add(linechart);
}
if (maxX != 0)
{
foreach (DataRow dr in ds1.Tables["mood"].Rows)
{
LineChart linechart = new LineChart();
linechart.Joy = dr["joy"].ToString();
linechart.Sadness = dr["sadness"].ToString();
linechart.Surprised = dr["surprised"].ToString();
linechart.Anger = dr["anger"].ToString();
linechart.Disgusted = dr["disgusted"].ToString();
linechartList.Add(linechart);
}
foreach (DataRow dr in ds2.Tables["maxLength"].Rows)
{
LineChart linechart = new LineChart();
linechart.MaxLength = dr["maxLength"].ToString();
linechartList.Add(linechart);
}
}
else if (maxX == 0)
{
LineChart linechart = new LineChart();
linechart.MaxLength = "0";
linechartList.Add(linechart);
}
return linechartList;
}
