//function for search date and retrieve sensor data from server
private void findsqlDate(DateTime date)
{
start = 0;
end = 59;
object lastrow = 0;
int counter = 0;
string retrieve = String.Format("SET ROWCOUNT 60; select * from(select Row_Number() over (order by TIMESUBMIT) as RowIndex, * from LORA_TABLE) " +
"as Sub where TimeSubmit >= '{0}';", date.ToString("MM-dd-yyyy HH:mm:ss"));
Debug.WriteLine("interesting", retrieve);
var loradata = LoraSQLConnect.GetLoraDatabaseData(retrieve, true, date, counter, lastrow);
var hankrecords = loradata.Item1;
var lorarecords = loradata.Item2;
CurrentDate.Text = loradata.Item5;
counter = loradata.Item3;
lastrow = loradata.Item4;
start += Convert.ToInt32(lastrow)-counter+1;
end += Convert.ToInt32(lastrow)-counter+1;
(dustChart.Series[0] as LineSeries).ItemsSource = hankrecords.SelectMany(i => i.dust).ToList();
(uvChart.Series[0] as LineSeries).ItemsSource = hankrecords.SelectMany(i => i.uv).ToList();
(temperatureChart.Series[0] as LineSeries).ItemsSource = hankrecords.SelectMany(i => i.temperature).ToList();
(pressureChart.Series[0] as LineSeries).ItemsSource = hankrecords.SelectMany(i => i.pressure).ToList();
(humidityChart.Series[0] as LineSeries).ItemsSource = hankrecords.SelectMany(i => i.humidity).ToList();
(RSSIChart.Series[0] as LineSeries).ItemsSource = hankrecords.SelectMany(i => i.rssi).ToList();
(dustChart.Series[1] as LineSeries).ItemsSource = lorarecords.SelectMany(i => i.dust).ToList();
(uvChart.Series[1] as LineSeries).ItemsSource = lorarecords.SelectMany(i => i.uv).ToList();
(temperatureChart.Series[1] as LineSeries).ItemsSource = lorarecords.SelectMany(i => i.temperature).ToList();
(pressureChart.Series[1] as LineSeries).ItemsSource = lorarecords.SelectMany(i => i.pressure).ToList();
(humidityChart.Series[1] as LineSeries).ItemsSource = lorarecords.SelectMany(i => i.humidity).ToList();
(RSSIChart.Series[1] as LineSeries).ItemsSource = lorarecords.SelectMany(i => i.rssi).ToList();
}
//load the graphs of sensor data collected
public void LoadChartContents()
{
string retrieve = string.Format("select * from (select Row_Number() over (order by TIMESUBMIT) as RowIndex, * from LORA_TABLE) as Sub Where Sub.RowIndex >= {0} and Sub.RowIndex <= {1};",start,end);
var loradata = LoraSQLConnect.GetLoraDatabaseData(retrieve,false,empty,0,0);
var hankrecords = loradata.Item1;
var lorarecords = loradata.Item2;
CurrentDate.Text = loradata.Item5;
(dustChart.Series[0] as LineSeries).ItemsSource = hankrecords.SelectMany(i => i.dust).ToList();
(uvChart.Series[0] as LineSeries).ItemsSource = hankrecords.SelectMany(i => i.uv).ToList();
(temperatureChart.Series[0] as LineSeries).ItemsSource = hankrecords.SelectMany(i => i.temperature).ToList();
(pressureChart.Series[0] as LineSeries).ItemsSource = hankrecords.SelectMany(i => i.pressure).ToList();
(humidityChart.Series[0] as LineSeries).ItemsSource = hankrecords.SelectMany(i => i.humidity).ToList();
(RSSIChart.Series[0] as LineSeries).ItemsSource = hankrecords.SelectMany(i => i.rssi).ToList();
(dustChart.Series[1] as LineSeries).ItemsSource = lorarecords.SelectMany(i => i.dust).ToList();
(uvChart.Series[1] as LineSeries).ItemsSource = lorarecords.SelectMany(i => i.uv).ToList();
(temperatureChart.Series[1] as LineSeries).ItemsSource = lorarecords.SelectMany(i => i.temperature).ToList();
(pressureChart.Series[1] as LineSeries).ItemsSource = lorarecords.SelectMany(i => i.pressure).ToList();
(humidityChart.Series[1] as LineSeries).ItemsSource = lorarecords.SelectMany(i => i.humidity).ToList();
(RSSIChart.Series[1] as LineSeries).ItemsSource = lorarecords.SelectMany(i => i.rssi).ToList();
}