private List <DroughtMonitorWeek> GetValuesFor(DMDataType type)
{
List <DroughtMonitorWeek> weeks = new List <DroughtMonitorWeek>();
using (SqlConnection conn = new SqlConnection(_connectionString))
{
conn.Open();
using (SqlCommand command = new SqlCommand(null, conn))
{
switch (type)
{
case DMDataType.COUNTY:
command.CommandText = string.Format("select DroughtCategory, DroughtValue, PublishedDate, County_ID from CountyDMValues order by PublishedDate, County_ID");
break;
case DMDataType.STATE:
command.CommandText = string.Format("select DroughtCategory, DroughtValue, PublishedDate, State_ID from StateDMValues order by PublishedDate, State_ID");
break;
case DMDataType.US:
command.CommandText = string.Format("select DroughtCategory, DroughtValue, PublishedDate from USDMValues order by PublishedDate");
break;
}
weeks.AddRange(ProcessQuery(command, type));
}
}
return(weeks);
}
public ActionResult DroughtMonitorData(string type, long photoID)
{
DMDataType dmDataType = DMDataType.COUNTY;
switch (type)
{
case "County":
dmDataType = DMDataType.COUNTY;
break;
case "State":
dmDataType = DMDataType.STATE;
break;
case "US":
dmDataType = DMDataType.US;
break;
}
Photo photo = PhotoRepository.Single(p => p.ID == photoID);
if (photo == null)
{
return(new HttpNotFoundResult(string.Format("Photo {0} was not found", photoID)));
}
DmData model = LoadDMData(dmDataType, photo.Captured, photo.Site.CountyFips);
model.PhotoID = photoID;
return(PartialView("_DmPartial", model));
} //End DroughtMonitorData
} //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
private DmData LoadDMData(DMDataType type, DateTime date, int CountyFIPS)
{
DmData data = new DmData();
data.DMValues = LoadDMDataValues(type, date, CountyFIPS);
data.PreviousWeekValues = LoadDMDataValues(type, date.AddDays(-7), CountyFIPS);
data.PreviousMonthValues = LoadDMDataValues(type, date.AddDays(-30), CountyFIPS);
//data.DMValues.Type = type.ToString().ToLower();
data.DisplayDate = data.DMValues.Week.ToString("MM-dd-yyyy");
data.DataWeek = data.DMValues.Week.ToString("yyMMdd");
return(data);
} //End Load DM Data
} //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 ICollection<DroughtMonitorWeek> Fetch(DMDataType type)
{
if (type == DMDataType.ALL)
{
List<DroughtMonitorWeek> weeks = new List<DroughtMonitorWeek>();
weeks.AddRange(GetValuesFor(DMDataType.COUNTY));
weeks.AddRange(GetValuesFor(DMDataType.STATE));
weeks.AddRange(GetValuesFor(DMDataType.US));
return weeks;
}
else
{
return GetValuesFor(type);
}
}
public ICollection <DroughtMonitorWeek> Fetch(DMDataType type)
{
if (type == DMDataType.ALL)
{
List <DroughtMonitorWeek> weeks = new List <DroughtMonitorWeek>();
weeks.AddRange(GetValuesFor(DMDataType.COUNTY));
weeks.AddRange(GetValuesFor(DMDataType.STATE));
weeks.AddRange(GetValuesFor(DMDataType.US));
return(weeks);
}
else
{
return(GetValuesFor(type));
}
}
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 List<DroughtMonitorWeek> GetValuesFor(DMDataType type)
{
List<DroughtMonitorWeek> weeks = new List<DroughtMonitorWeek>();
using (SqlConnection conn = new SqlConnection(_connectionString))
{
conn.Open();
using (SqlCommand command = new SqlCommand(null, conn))
{
switch (type)
{
case DMDataType.COUNTY:
command.CommandText = string.Format("select DroughtCategory, DroughtValue, PublishedDate, County_ID from CountyDMValues order by PublishedDate, County_ID");
break;
case DMDataType.STATE:
command.CommandText = string.Format("select DroughtCategory, DroughtValue, PublishedDate, State_ID from StateDMValues order by PublishedDate, State_ID");
break;
case DMDataType.US:
command.CommandText = string.Format("select DroughtCategory, DroughtValue, PublishedDate from USDMValues order by PublishedDate");
break;
}
weeks.AddRange(ProcessQuery(command, type));
}
}
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 DmData LoadDMData(DMDataType type, DateTime date, int CountyFIPS)
{
DmData data = new DmData();
data.DMValues = LoadDMDataValues(type, date, CountyFIPS);
data.PreviousWeekValues = LoadDMDataValues(type, date.AddDays(-7), CountyFIPS);
data.PreviousMonthValues = LoadDMDataValues(type, date.AddDays(-30), CountyFIPS);
//data.DMValues.Type = type.ToString().ToLower();
data.DisplayDate = data.DMValues.Week.ToString("MM-dd-yyyy");
data.DataWeek = data.DMValues.Week.ToString("yyMMdd");
return data;
}
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 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
}
