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