public ICollection <DroughtMonitorWeek> FindUS(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 from USDMValues where order by PublishedDate");
}
else if (week == null && weeksPrevious != 0)
{
command.CommandText = string.Format("select DroughtCategory, DroughtValue, PublishedDate from USDMValues where PublishedDate >= @rangestart order by PublishedDate");
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 from USDMValues where PublishedDate >= @rangestart and PublishedDate <= @rangeend order by PublishedDate");
command.Parameters.AddWithValue("@rangestart", week.Value.AddDays(7 * (0 - weeksPrevious)).ToString("yyyy/MM/dd"));
command.Parameters.AddWithValue("@rangeend", week.Value.ToString("yyyy/MM/dd"));
}
return(ProcessQuery(command, DMDataType.US));
}
}
}
} //End RunDMImport (two dates)
private void ImportDMData(DateTime startDate, DateTime endDate)
{
startDate = DroughtMonitorWeek.ConvertDateToTuesday(startDate);
endDate = DroughtMonitorWeek.ConvertDateToTuesday(endDate);
DateTime importWeek = startDate;
List <DateTime> importDates = new List <DateTime>();
while (importWeek <= endDate)
{
importDates.Add(importWeek);
importWeek = importWeek.AddDays(7);
}
foreach (DateTime week in importDates)
{
if (!AlreadyImported(week))
{
foreach (DMDataType type in Enum.GetValues(typeof(DMDataType)))
{
if (type != DMDataType.ALL)
{
//Get information
string url = String.Format(@"http://droughtmonitor.unl.edu/USDMStatistics.ashx/?mode=table&aoi={0}&date={1}", type.ToString().ToLower(), week.ToString("yyyyMMdd"));
WebClient client = new WebClient();
string response = client.DownloadString(url);
// split the response into rows based on the new line character
List <string> rows = response.Split('\n').ToList <string>();
rows.RemoveAt(0); // remove the header row
this.WriteData(type, rows, week);
}
}
}
} //End foreach week in importDates
} //End ImportDMData (two dates)
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));
}
}
}