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