private void Update_chart_col_Click(object sender, RoutedEventArgs e)
{
string timestart = StartTimePickerCol.Text;
string timestop = StopTimePickerCol.Text;
long granularity = Convert.ToInt64(GranularityPickerStack.Text);
int devices_num = Convert.ToInt32(DevNumPickerCol.Text);
MySqlCommand cmm = null;
try
{
// Initialize the graph structure and clear existing data
cmm = new MySqlCommand("SELECT mac FROM devices"
+ " WHERE timestamp BETWEEN '" + timestart + "' AND '" + timestop + "'"
+ " GROUP BY mac"
+ " ORDER BY count(*) DESC, mac LIMIT " + devices_num, DBconnection);
MySqlDataReader r = cmm.ExecuteReader();
List <string> labs = new List <string>();
Dictionary <string, int> map = new Dictionary <string, int>();
// Clear old data
if (ColumnCollection.Count != 0)
{
ColumnCollection.Clear();
}
// For each mac read, store the address in the map and add a series to the chart
int count = 0;
while (r.Read())
{
if (!map.ContainsKey(r[0].ToString()))
{
map.Add(r[0].ToString(), count);
count++;
ColumnCollection.Add(new StackedColumnSeries
{
Title = r[0].ToString(),
Values = new ChartValues <double> {
},
StackMode = StackMode.Values
});
}
}
r.Close();
// Now that the graph is correctly initialized, read the actual data about the most frequent devices
cmm = new MySqlCommand("SELECT (unix_timestamp(timestamp) - unix_timestamp(timestamp)%" + granularity + ") groupTime, d.mac, count(*) "
+ " FROM devices d JOIN(SELECT mac FROM devices"
+ " WHERE timestamp BETWEEN '" + timestart + "' AND '" + timestop + "'"
+ " GROUP BY mac"
+ " ORDER BY count(*) DESC, mac LIMIT " + map.Count
+ ") x ON(x.mac = d.mac)"
+ " WHERE timestamp BETWEEN '" + timestart + "' AND '" + timestop + "'"
+ " GROUP BY groupTime, d.mac", DBconnection);
r = cmm.ExecuteReader();
bool first = true;
int actualDevicesNum = map.Count;
Ts_structure TS = new Ts_structure(0, actualDevicesNum);
while (r.Read())
{
// The first time we iterate through the loop we need to properly initialize the ts_structure
if (first)
{
TS = new Ts_structure(Convert.ToInt64(r[0]), actualDevicesNum);
first = false;
}
// If the timestamp is different from the previous one, it means we completed the analisys of the previous time segment,
// hence its time to update the graph with the macs collected during that timeframe.
if (TS.Timestamp != Convert.ToInt64(r[0]))
{
// Even if some MAC wasn't read during a specific time segment, we still need to keep track of its absence in the graph
if (TS.MACMap.Count != actualDevicesNum)
{
TS.AddMAC(map, 0);
}
// Add the data to the graph
foreach (KeyValuePair <string, int> mac in TS.MACMap)
{
ColumnCollection[map[mac.Key]].Values.Add(Convert.ToDouble(mac.Value));
}
DateTime dIn = TimeStampToDateTime(TS.Timestamp);
labs.Add(dIn.ToShortDateString() + "\n " + dIn.ToString("HH:mm:ss"));
TS = new Ts_structure(Convert.ToInt64(r[0]), actualDevicesNum);
}
// Add the mac address and its occurrence within the time segment in the TS_structure
TS.AddMAC(r[1].ToString(), Convert.ToInt32(r[2]));
}
// When we go out of the r.Read() while loop, we still need to add the last data to the graph.
// If any data was read from the database, then finish the graph update with the last data.
if (!first)
{
// Even if some MAC wasn't read during a specific time segment, we still need to keep track of its absence in the graph
if (TS.MACMap.Count != actualDevicesNum)
{
TS.AddMAC(map, 0);
}
// Add the data to the graph
foreach (KeyValuePair <string, int> mac in TS.MACMap)
{
ColumnCollection[map[mac.Key]].Values.Add(Convert.ToDouble(mac.Value));
}
DateTime d = TimeStampToDateTime(TS.Timestamp);
labs.Add(d.ToShortDateString() + "\n " + d.ToString("HH:mm:ss"));
}
//Prepare labels
string[] ls = new string[labs.Count];
for (int i = 0; i < labs.Count; i++)
{
ls[i] = labs[i];
}
ColumnLabels = ls;
ColumnFormatter = value => value.ToString();
//Send data to the graph
DataContext = this;
cmm.Dispose();
}
catch (Exception ex)
{
if (cmm != null)
{
cmm.Dispose();
}
output_box.AppendText("" + ex.Message);
Console.WriteLine(ex.StackTrace);
}
}