private async void CreateExportQuery(SENSOR_DATA_TYPE type)
{
try
{
var credential = GoogleCredential.FromFile(" path to credentials");
BigQueryClient client = BigQueryClient.Create(_projectId, credential);
string table_name = "", destination_uri = " ";
switch (type)
{
case SENSOR_DATA_TYPE.SOIL_MOISTURE:
table_name = "`smartgarden-iot.sm_gardenplant_data.plant_Soil_Moisture`";
destination_uri = "gs://bq-query-export-data/sm-data-*.csv";
break;
case SENSOR_DATA_TYPE.LIGHT:
table_name = "`smartgarden-iot.sm_gardenplant_data.plant_light_resistance`";
destination_uri = "gs://bq-query-export-data/lr-data-*.csv";
break;
case SENSOR_DATA_TYPE.TEMPERATURE:
table_name = "`smartgarden-iot.sm_gardenplant_data.plant_temperature`";
destination_uri = "gs://bq-query-export-data/tmp-data-*.csv";
break;
case SENSOR_DATA_TYPE.HUMIDITY:
table_name = "`smartgarden-iot.sm_gardenplant_data.plant_humidity`";
destination_uri = "gs://bq-query-export-data/hm-data-*.csv";
break;
default:
break;
}
string sql = string.Format(@"EXPORT DATA OPTIONS(uri='{0}', format='CSV', overwrite=true)
AS SELECT * FROM {1} WHERE TIME_DIFF(time_received, CURRENT_TIME('UTC-5'),HOUR) < 12
LIMIT 70", destination_uri, table_name);
BigQueryParameter[] parameters = null;
BigQueryResults _ = await client.ExecuteQueryAsync(sql, parameters);
}
catch (Exception ex)
{
_logger.LogError(@" \n --------------\n
Somthing went wrong in {0}. \n Message: {1} \n \n Stack trace: {3} \n Inner Exception {4}", ex.Source, ex.Message, ex.StackTrace, ex.StackTrace);
}
}
private void UpdateModels(List <int> dataList, List <DateTime> dates, SENSOR_DATA_TYPE sensorType)
{
switch (sensorType)
{
case SENSOR_DATA_TYPE.SOIL_MOISTURE:
SoilMoistureChart = new SoilMoistureChartData {
Data_Axis_Y = dataList,
Data_Axis_X = dates,
};
break;
case SENSOR_DATA_TYPE.LIGHT:
LightResistanceChart = new LightResistanceChartData
{
Data_Axis_Y = dataList,
Data_Axis_X = dates,
};
break;
case SENSOR_DATA_TYPE.TEMPERATURE:
TemperatureChart = new TemperatureChartData
{
Data_Axis_Y = dataList,
Data_Axis_X = dates,
};
break;
case SENSOR_DATA_TYPE.HUMIDITY:
HumidityChart = new HumidityChartData
{
Data_Axis_Y = dataList,
Data_Axis_X = dates,
};
break;
default:
break;
}
}
/// <summary>
/// Takes a file name and Sensor data type and parses through the the csv file.
/// </summary>
/// <param name="filename"></param>
/// <param name="type"></param>
///
private void Parse(string filename, SENSOR_DATA_TYPE type)
{
List <int> soilMoisturesList = new List <int>();
List <int> temperaturesList = new List <int>();
List <int> lightResistancesList = new List <int>();
List <int> humidityList = new List <int>();
List <DateTime> dateTimes = new List <DateTime>();
try
{
using (TextFieldParser parser = new TextFieldParser(Path.Combine(WebHostEnvironment.WebRootPath, "data", filename)))
{
parser.TextFieldType = FieldType.Delimited;
parser.SetDelimiters(",");
while (!parser.EndOfData)
{
string[] fields = parser.ReadFields();
string dateString = fields[1] + "T" + fields[2] + "Z";
DateTime date;
if (type == SENSOR_DATA_TYPE.SOIL_MOISTURE)
{
dateString = fields[2] + "T" + fields[3] + "Z";
date = DateTime.Parse(dateString).ToUniversalTime();
soilMoisturesList.Add(int.Parse(fields[0]));
dateTimes.Add(date);
}
else if (type == SENSOR_DATA_TYPE.LIGHT)
{
date = DateTime.Parse(dateString).ToUniversalTime();
lightResistancesList.Add(int.Parse(fields[0]));
dateTimes.Add(date);
}
else if (type == SENSOR_DATA_TYPE.TEMPERATURE)
{
date = DateTime.Parse(dateString).ToUniversalTime();
temperaturesList.Add(int.Parse(fields[0]));
dateTimes.Add(date);
}
else
{
date = DateTime.Parse(dateString).ToUniversalTime();
humidityList.Add(int.Parse(fields[0]));
dateTimes.Add(date);
}
}
}
}
catch (Exception ex)
{
_logger.LogError($"Somthing went wrong in {ex.Source}. Message: {ex.Message}");
_logger.LogError($"Inner Exception : {ex.InnerException}");
_logger.LogError(ex.StackTrace);
}
switch (type)
{
case SENSOR_DATA_TYPE.SOIL_MOISTURE:
UpdateModels(soilMoisturesList, dateTimes, type);
break;
case SENSOR_DATA_TYPE.LIGHT:
UpdateModels(lightResistancesList, dateTimes, type);
break;
case SENSOR_DATA_TYPE.TEMPERATURE:
UpdateModels(temperaturesList, dateTimes, type);
break;
case SENSOR_DATA_TYPE.HUMIDITY:
UpdateModels(humidityList, dateTimes, type);
break;
default:
break;
}
}