public ICollection<DroughtMonitorWeek> FindBy(USCounty county, DateTime? week = null, int weeksPrevious = 0)
{
week = DroughtMonitorWeek.ConvertDateToTuesday(week.Value);
using (SqlConnection conn = new SqlConnection(_connectionString))
{
conn.Open();
using (SqlCommand command = new SqlCommand(null, conn))
{
if (week == null && weeksPrevious == 0)
{
command.CommandText = string.Format("select DroughtCategory, DroughtValue, PublishedDate, County_ID from CountyDMValues where County_ID = @county order by PublishedDate, County_ID");
command.Parameters.AddWithValue("@county", county.ID);
}
else if (week == null && weeksPrevious != 0)
{
command.CommandText = string.Format("select DroughtCategory, DroughtValue, PublishedDate, County_ID from CountyDMValues where County_ID = @county and PublishedDate >= @rangestart order by PublishedDate, County_ID");
command.Parameters.AddWithValue("@county", county.ID);
command.Parameters.AddWithValue("@rangestart", DateTime.Now.AddDays(7 * (0 - weeksPrevious)).ToString("yyyy/MM/dd"));
}
else if (week != null)
{
command.CommandText = string.Format("select DroughtCategory, DroughtValue, PublishedDate, County_ID from CountyDMValues where County_ID = @county and PublishedDate >= @rangestart and PublishedDate <= @rangeend order by PublishedDate, County_ID");
command.Parameters.AddWithValue("@county", county.ID);
command.Parameters.AddWithValue("@rangestart", week.Value.AddDays(7 * (0 - weeksPrevious)).ToString("yyyy/MM/dd"));
command.Parameters.AddWithValue("@rangeend", week.Value.ToString("yyyy/MM/dd"));
}
else
{
throw new Exception("No command was specified");
}
return ProcessQuery(command, DMDataType.COUNTY);
}
}
}
public USCounty GetCountyForFips(int fips)
{
using (SqlConnection conn = new SqlConnection(_connectionString))
{
conn.Open();
using (SqlCommand command = new SqlCommand("select * from Counties where FIPS = @Fips", conn))
{
command.Parameters.AddWithValue("@Fips", fips);
SqlDataReader reader = command.ExecuteReader();
if (reader.Read())
{
USCounty county = new USCounty();
county.ID = reader.GetInt64(0);
county.Fips = reader.GetInt32(1);
county.Name = reader.GetString(2);
county.State = GetState(reader.GetInt64(3));
return county;
}
}
}
throw new ArgumentException(string.Format("Fips code {0} is not recognized", fips));
}
