public IEnumerable <RecordAverageDTO> FilterByDate([FromRoute] int userId, double fromDate, double toDate) { DateHandler dateHandler = new DateHandler(); string dateFrom = dateHandler.ConvertToSqlStringDate(fromDate); string dateTo = dateHandler.ConvertToSqlStringDate(toDate); string _selectString = "SELECT Datepart(\"Day\", \"RecordTime\") as \"DayDate\", Datepart(\"Month\", \"RecordTime\") as \"MonthDate\", avg(\"BPSystolic\") as \"BpSystolicAvg\", avg(\"BPDiastolic\") as \"BpDiastolicAvg\", avg(\"BodyTemperature\") as \"BodyTemperature\", avg(\"HeartBeatPerSecond\") as \"HeartBeatPerSecond\", avg(\"Dust\") as \"Dust\", avg(\"Sulphur\") as \"Sulphur\", avg(\"Nitrogen\") as \"Nitrogen\", avg(\"Fluor\") as \"Fluor\", avg(\"CarbonMonoxide\") as \"CarbonMonoxide\", avg(\"Ozone\") as \"Ozone\", avg(\"RaspberryRecord\".\"Temperature\") as \"Temperature\", avg(\"RaspberryRecord\".\"Pressure\") as \"Pressure\", avg(\"RaspberryRecord\".\"Humudity\") as \"Humidity\" FROM \"Record\" INNER JOIN \"RaspberryRecord\" ON \"Record\".\"UserID\" = \"RaspberryRecord\".\"UserId\" WHERE \"Record\".\"UserID\" = " + userId + " AND \"RecordTime\" BETWEEN " + dateFrom + " AND " + dateTo + " AND \"DateTime\" BETWEEN " + dateFrom + " AND " + dateTo + " group by Datepart (\"Day\", \"RecordTime\"), Datepart (\"Month\", \"RecordTime\")"; using (var conn = new SqlConnection(connectionString)) { conn.Open(); using (var cmd = new SqlCommand(_selectString, conn)) { using (var reader = cmd.ExecuteReader()) { List <RecordAverageDTO> RecordList = new List <RecordAverageDTO>(); while (reader.Read()) { DataReader dataReader = new DataReader(); RecordAverageDTO mtc = dataReader.ReadRecordAverageDTO(reader); RecordList.Add(mtc); } //ObjectConverter objConverted = new ObjectConverter(); //JObject jObject = objConverted.dayJson(RecordList); return(RecordList); } } } }
public RecordAverageDTO ReadRecordAverageDTO(IDataRecord reader) { var obj = new JObject(); int dayDate = reader.GetInt32(0); int monthDate = reader.GetInt32(1); double bPSystolic = reader.GetDouble(2); double bPDiastolic = reader.GetDouble(3); double bodyTemperature = reader.GetDouble(4); double heartBeatPerSecond = reader.GetInt32(5); double dust = reader.GetDouble(6); double sulphur = reader.GetDouble(7); double nitrogen = reader.GetDouble(8); double fluor = reader.GetDouble(9); double carbonMonoxide = reader.GetDouble(10); double ozone = reader.GetDouble(11); double temperature = reader.GetDouble(12); double pressure = reader.GetDouble(13); double humidity = reader.GetDouble(14); RecordAverageDTO recordAvergageDTO = new RecordAverageDTO { DayDate = dayDate, MonthDate = monthDate, BPSystolic = bPSystolic, BPDiastolic = bPDiastolic, BodyTemperature = bodyTemperature, HeartBeatPerSecond = heartBeatPerSecond, Dust = dust, Sulphur = sulphur, Nitrogen = nitrogen, Fluor = fluor, CarbonMonoxide = carbonMonoxide, Ozone = ozone, Temperature = temperature, Pressure = pressure, Humidity = humidity }; return(recordAvergageDTO); }