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 AlreadyImported
private void WriteData(DMDataType type, List <string> rows, DateTime date)
{
DroughtMonitorWeek dmWeek = new DroughtMonitorWeek();
dmWeek.Type = type;
dmWeek.Week = date;
dmWeek.County = new USCounty();
dmWeek.County.State = new USState();
dmWeek.State = new USState();
bool wroteUS = false;
foreach (string line in rows)
{
if (line.Equals("") || wroteUS)
{
continue;
}
// remove the \" from the line
string newLine = line.Replace("\"", "");
// split out each column
string[] cols = newLine.Split(',');
int offset = 2; // Offset is 2 for the State and US data sets, but is 4 for the County data
switch (type)
{
case DMDataType.COUNTY:
dmWeek.County.ID = -1;
dmWeek.County.Name = cols[2];
dmWeek.County.Fips = int.Parse(cols[1]);
dmWeek.County.State.Name = cols[3];
dmWeek.State.Name = cols[3];
offset = 4;
break;
case DMDataType.STATE:
dmWeek.State.ID = -1;
dmWeek.State.Name = cols[1];
break;
case DMDataType.US:
//only write first line of data for US data
wroteUS = true;
break;
}
//Add DM values for all six columns
for (int i = 0; i < 6; i++)
{
// Set value for col[i+offset] with category i
dmWeek[i] = float.Parse(cols[i + offset]);
}
_repo.Add(dmWeek);
} //End foreach line in rows
} //End WriteData
} //End Load DM Data
private DroughtMonitorWeek LoadDMDataValues(DMDataType type, DateTime date, int CountyFIPS)
{
DroughtMonitorWeek week = null;
switch (type)
{
case DMDataType.COUNTY:
week = DmRepository.FindBy(DmRepository.GetCountyForFips(CountyFIPS), date).FirstOrDefault();
break;
case DMDataType.STATE:
week = DmRepository.FindBy(DmRepository.GetCountyForFips(CountyFIPS).State, date).FirstOrDefault();
break;
case DMDataType.US:
week = DmRepository.FindUS(date).FirstOrDefault();
break;
}
if (week == null)
{
USCounty county = DmRepository.GetCountyForFips(CountyFIPS);
week = new DroughtMonitorWeek()
{
D0 = 0,
D1 = 0,
D2 = 0,
D3 = 0,
D4 = 0,
NonDrought = 0,
Week = date,
Type = type,
County = county,
State = county.State
};
}
else
{
week.Type = type;
// Normalize data to be out of 100%
week.D0 = (float)Math.Round((week.D0 - week.D1), 2);
week.D1 = (float)Math.Round((week.D1 - week.D2), 2);
week.D2 = (float)Math.Round((week.D2 - week.D3), 2);
week.D3 = (float)Math.Round((week.D3 - week.D4), 2);
}
return(week);
} //End LoadDMDataValues
public void Add(DroughtMonitorWeek week)
{
using (SqlConnection conn = new SqlConnection(_connectionString))
{
conn.Open();
using (SqlCommand command = new SqlCommand(null, conn))
{
switch (week.Type)
{
case DMDataType.COUNTY:
if (week.County.ID == -1)
{
week.County.ID = this.GetCountyID(conn, week.County.Name, week.County.Fips, week.State.Name);
}
command.CommandText = "insert into CountyDMValues (PublishedDate, County_ID, DroughtCategory, DroughtValue) values (@pdate, @county, @category, @dval)";
command.Parameters.AddWithValue("@county", week.County.ID);
break;
case DMDataType.STATE:
if (week.State.ID == -1)
{
week.State.ID = this.GetStateID(conn, week.State.Name);
}
command.CommandText = "insert into StateDMValues (PublishedDate, State_ID, DroughtCategory, DroughtValue) values (@pdate, @state, @category, @dval)";
command.Parameters.AddWithValue("@state", week.State.ID);
break;
case DMDataType.US:
command.CommandText = "insert into USDMValues (PublishedDate, DroughtCategory, DroughtValue) values (@pdate, @category, @dval)";
break;
}
command.Parameters.AddWithValue("@pdate", week.Week);
command.Parameters.Add("@category", SqlDbType.Int);
command.Parameters.Add("@dval", SqlDbType.Float);
for (int i = 0; i < 6; i++)
{
command.Parameters["@category"].Value = i;
command.Parameters["@dval"].Value = week[i];
command.ExecuteNonQuery();
}
}
}
}
} //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));
}
}
}
private ICollection <DroughtMonitorWeek> ProcessQuery(SqlCommand command, DMDataType type)
{
List <DroughtMonitorWeek> weeks = new List <DroughtMonitorWeek>();
using (SqlDataReader reader = command.ExecuteReader())
{
DroughtMonitorWeek currentWeek = null;
while (reader.Read())
{
switch (type)
{
case DMDataType.COUNTY:
if (currentWeek == null || (reader.GetDateTime(2) != currentWeek.Week || reader.GetInt64(3) != currentWeek.County.ID))
{
if (currentWeek != null)
{
weeks.Add(currentWeek);
}
currentWeek = new DroughtMonitorWeek();
currentWeek.Week = reader.GetDateTime(2);
currentWeek.County = GetCounty(reader.GetInt64(3));
currentWeek.State = currentWeek.County.State;
}
break;
case DMDataType.STATE:
if (currentWeek == null || (reader.GetDateTime(2) != currentWeek.Week || reader.GetInt64(3) != currentWeek.State.ID))
{
if (currentWeek != null)
{
weeks.Add(currentWeek);
}
currentWeek = new DroughtMonitorWeek();
currentWeek.Week = reader.GetDateTime(2);
currentWeek.State = GetState(reader.GetInt64(3));
}
break;
case DMDataType.US:
if (currentWeek == null || reader.GetDateTime(2) != currentWeek.Week)
{
if (currentWeek != null)
{
weeks.Add(currentWeek);
}
currentWeek = new DroughtMonitorWeek();
currentWeek.Week = reader.GetDateTime(2);
}
break;
}
currentWeek[reader.GetInt32(0)] = reader.GetDouble(1);
}
if (currentWeek != null)
{
weeks.Add(currentWeek);
}
reader.Close();
}
return(weeks);
}
