public TimelapseDataWeek GetTimelapseData(TimelapseDataRequest request) { USCounty county = DmRepo.GetCountyForFips(request.CountyFips); Dictionary <string, DroughtMonitorWeek> results = new Dictionary <string, DroughtMonitorWeek>(); DateTime date = Convert.ToDateTime(request.DmWeek); results.Add("COUNTY", DmRepo.FindBy(county, date).FirstOrDefault()); results.Add("STATE", DmRepo.FindBy(county.State, date).FirstOrDefault()); results.Add("US", DmRepo.FindUS(date).FirstOrDefault()); ICollection <AvailableWaterDataByStation> types = WaterRepo.FetchBestDataTypesForStationDate(WaterRepo.GetClosestStations(request.Latitude, request.Longitude, 1), date); AvailableWaterDataByStation discharge = types.Where(t => t.ParameterCode == "00060").FirstOrDefault(); double averageDischarge = 0; if (discharge != null) { double total = 0; ICollection <WaterDataValue> values = WaterRepo.FetchByDateRange(discharge.StationID, discharge.DataID, date, date.AddDays(7)); foreach (WaterDataValue wvalue in values) { if (wvalue.Value != -999999) { total += wvalue.Value; } } averageDischarge = total / (double)values.Count; } return(new TimelapseDataWeek { DMData = results, AverageDischarge = averageDischarge }); }
public ICollection <AvailableWaterDataByStation> FetchCurrentDataTypes() { List <AvailableWaterDataByStation> stationDataTypes = new List <AvailableWaterDataByStation>(); using (SqlConnection conn = new SqlConnection(_connectionString)) { conn.Open(); using (SqlCommand dataTypeLookup = new SqlCommand(string.Format("select * from AvailableWaterDataTypes"), conn)) using (SqlDataReader reader = dataTypeLookup.ExecuteReader()) { while (reader.Read()) { AvailableWaterDataByStation record = new AvailableWaterDataByStation(); record.DataID = reader.GetInt64(0); record.StationID = reader.GetInt64(1); long typeID = reader.GetInt64(2); record.ParameterCode = this.GetCode(typeID, WaterCodeType.PARAMETER); record.StatisticCode = this.GetCode(typeID, WaterCodeType.STATISTIC); record.CurrentLastDate = reader.GetDateTime(4); stationDataTypes.Add(record); } reader.Close(); } } return(stationDataTypes); }
public void UpdateAvailableDataTypes(AvailableWaterDataByStation dataType) { using (SqlConnection conn = new SqlConnection(_connectionString)) { conn.Open(); // Create and execute an SQL statement. SqlCommand command = new SqlCommand(null, conn); command.CommandText = "update AvailableWaterDataTypes set ToDate=@date where ID=@ID"; command.Parameters.AddWithValue("@ID", dataType.DataID); command.Parameters.Add("@date", SqlDbType.DateTime); command.Parameters["@date"].Value = dataType.CurrentLastDate; command.ExecuteNonQuery(); } }
} // End ValidRow private void ImportAndWriteDataValues(string stationCode, string parameterCode, string statisticCode, long stationID, long waterDataTypeID, DateTime startDate) { AvailableWaterDataByStation dataType = new AvailableWaterDataByStation(); dataType.CurrentLastDate = startDate; dataType.DataID = waterDataTypeID; dataType.ParameterCode = parameterCode; //dataType.StationCode = stationCode; dataType.StationID = stationID; dataType.StatisticCode = statisticCode; //Get information DateTime newLastDate = this.UpdateWaterValues(dataType, DateTime.Now); } //End ImportAndWriteDataValues
} //End UpdateWaterDataToCurrent private DateTime UpdateWaterValues(AvailableWaterDataByStation dataType, DateTime endDate) { DateTime resultDate = dataType.CurrentLastDate; //Get data from current last date (plus 1) to current date //Get information string url = String.Format(@"http://waterservices.usgs.gov/nwis/dv/?format=rdb&sites={0}¶meterCd={1}&statCd={2}&startDT={3}&endDT={4}", _repo.GetStationCode(dataType.StationID), dataType.ParameterCode, dataType.StatisticCode, dataType.CurrentLastDate.AddDays(1).ToString("yyyy-MM-dd"), endDate.ToString("yyyy-MM-dd")); WebClient client = new WebClient(); try { string response = client.DownloadString(url); // split the response into rows based on the new line character List <string> rows = response.Split('\n').ToList <string>(); while (rows[0].StartsWith("#")) { rows.RemoveAt(0); // remove the header comment rows } rows.RemoveAt(0); // remove the header row rows.RemoveAt(0); // remove the definition row //Write values and keep track of new greatest date if (rows.Count != 0) { resultDate = this.WriteDataValues(rows, dataType.StationID, dataType.DataID); if (resultDate.CompareTo(new DateTime(2009, 1, 1)) <= 0) { resultDate = dataType.CurrentLastDate; } } } catch (WebException we) { if (((HttpWebResponse)we.Response).StatusCode != HttpStatusCode.NotFound) { throw we; } // else do nothing as this means the site doesn't have data for the requested interval which is OK } return(resultDate); } //End UpdateWaterValues
public ICollection <AvailableWaterDataByStation> FetchBestDataTypesForStationDate(ICollection <WaterStation> stationIDs, DateTime imageDate) { List <AvailableWaterDataByStation> stationDataTypes = new List <AvailableWaterDataByStation>(); Boolean found = false; int stationIndex = 0; using (SqlConnection conn = new SqlConnection(_connectionString)) { conn.Open(); while (!found && stationIDs.Count > stationIndex) { using (SqlCommand dataTypeLookup = new SqlCommand(string.Format("select * from AvailableWaterDataTypes where Station_ID = {0}", stationIDs.ElementAt(stationIndex).ID), conn)) using (SqlDataReader reader = dataTypeLookup.ExecuteReader()) { while (reader.Read()) { if (imageDate.CompareTo(reader.GetDateTime(4)) <= 0) { AvailableWaterDataByStation record = new AvailableWaterDataByStation(); record.DataID = reader.GetInt64(0); record.StationID = reader.GetInt64(1); long typeID = reader.GetInt64(2); record.ParameterCode = this.GetCode(typeID, WaterCodeType.PARAMETER); record.StatisticCode = this.GetCode(typeID, WaterCodeType.STATISTIC); record.CurrentLastDate = reader.GetDateTime(4); stationDataTypes.Add(record); found = true; } } reader.Close(); } stationIndex++; } // End while not found } return(stationDataTypes); }