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; } }