private void loadData(SWATUnitType type)
{
if (!_exist) return;
DataTable dt = GetDataTable("select name from sqlite_master where type = 'table'");
if (dt.Rows.Count > 0)
{
foreach (DataRow r in dt.Rows)
{
//get table name
string tableName = r[0].ToString();
if(!tableName.Contains(type.ToString())) continue;
//see if there are some data
//don't consider empty tables
dt = GetDataTable(
string.Format("select count(*) from [{0}]", tableName));
RowItem item = new RowItem(dt.Rows[0]);
if (item.getColumnValue_Int(0) == 0) continue;
//extract the id from table name
string[] all_in_tableName = tableName.Split(OBSERVATION_TABLE_NAME_DELIMITER);
if (all_in_tableName.Length < 3) continue;
int id = -1;
if (int.TryParse(all_in_tableName[1], out id))
{
string col = all_in_tableName[2];
string dataUniqueId = getUniqueId(type, id, col);
if (!_allData.ContainsKey(dataUniqueId))
{
SWATUnitObservationData data =
new SWATUnitObservationData(
id, type, col,
-1, -1,
this);
//add to all data
_allData.Add(dataUniqueId,data);
//add to display data
string display_id = string.Format("{0}_{1}",type,id);
if (!_allData_display.ContainsKey(display_id))
_allData_display.Add(display_id, new List<SWATUnitObservationData>());
_allData_display[display_id].Add(data);
}
}
}
}
}
private void loadData(SWATUnitType type)
{
if (!_exist)
{
return;
}
DataTable dt = GetDataTable("select name from sqlite_master where type = 'table'");
if (dt.Rows.Count > 0)
{
foreach (DataRow r in dt.Rows)
{
//get table name
string tableName = r[0].ToString();
if (!tableName.Contains(type.ToString()))
{
continue;
}
//see if there are some data
//don't consider empty tables
dt = GetDataTable(
string.Format("select count(*) from [{0}]", tableName));
RowItem item = new RowItem(dt.Rows[0]);
if (item.getColumnValue_Int(0) == 0)
{
continue;
}
//extract the id from table name
string[] all_in_tableName = tableName.Split(OBSERVATION_TABLE_NAME_DELIMITER);
if (all_in_tableName.Length < 3)
{
continue;
}
int id = -1;
if (int.TryParse(all_in_tableName[1], out id))
{
string col = all_in_tableName[2];
string dataUniqueId = getUniqueId(type, id, col);
if (!_allData.ContainsKey(dataUniqueId))
{
SWATUnitObservationData data =
new SWATUnitObservationData(
id, type, col,
-1, -1,
this);
//add to all data
_allData.Add(dataUniqueId, data);
//add to display data
string display_id = string.Format("{0}_{1}", type, id);
if (!_allData_display.ContainsKey(display_id))
{
_allData_display.Add(display_id, new List <SWATUnitObservationData>());
}
_allData_display[display_id].Add(data);
}
}
}
}
}
/// <summary>
/// Calculate (average) annual results
/// </summary>
/// <param name="type"></param>
/// <param name="col"></param>
/// <param name="year">-1 means all years, the average annual will be calculated</param>
/// <returns></returns>
/// <remarks>It works best for daily output. No calculation is required for yearly output.</remarks>
public Dictionary <int, double> getAverageAnnualResults(SWATUnitType type, string col, int year)
{
//look in the cache first
string result_id = string.Format("averageannual_{0}_{1}_{2}", type, col, year);
if (!_avgAnnualResults.ContainsKey(result_id))
{
//get outputs for all subbasins/reaches with given column
string sql = string.Format("select {0},{1},{2} from [{3}]",
ScenarioResultStructure.COLUMN_NAME_YEAR, type, col, type);
if (type == SWATUnitType.SUB)
{
if (col.Equals(ScenarioResultStructure.SUBBAIN_TOTAL_NITROGEN_COLUMN))
{
sql = string.Format("select {0},{1},{2} as {3} from [{4}]",
ScenarioResultStructure.COLUMN_NAME_YEAR, type, ScenarioResultStructure.SUBBAIN_NITROGEN_COLUMNS_SQL, col, type);
}
else if (col.Equals(ScenarioResultStructure.SUBBAIN_TOTAL_PHOSPHORUS_COLUMN))
{
sql = string.Format("select {0},{1},{2} as {3} from [{4}]",
ScenarioResultStructure.COLUMN_NAME_YEAR, type, ScenarioResultStructure.SUBBAIN_PHOSPHORUS_COLUMNS_SQL, col, type);
}
}
//years
if (year != -1)
{
sql += string.Format(" where {0} = {1}",
ScenarioResultStructure.COLUMN_NAME_YEAR, year);
}
DataTable dt = GetDataTable(sql);
//summary the result by year and id
//for reach flow, average operation should be used
var query = from oneresult in dt.AsEnumerable()
group oneresult by new {
Year = oneresult.Field <int>(ScenarioResultStructure.COLUMN_NAME_YEAR), //group by year
ID = oneresult.Field <int>(type.ToString()) //group by id
} into g
select new
{
ID = g.Key.ID,
Year = g.Key.Year,
Total = g.Sum(oneresult => oneresult.Field <double>(col)),
};
Dictionary <int, double> results = new Dictionary <int, double>();
//calculate the average annual
int numofyear = 1;
if (year == -1)
{
numofyear = EndYear - StartYear;
}
foreach (var oneyearid in query)
{
//Debug.WriteLine(String.Format("{0}:{1}", oneyearid.ID, oneyearid.Year));
if (!results.ContainsKey(oneyearid.ID))
{
results.Add(oneyearid.ID, 0.0);
}
results[oneyearid.ID] += oneyearid.Total / numofyear; //get the average
}
_avgAnnualResults.Add(result_id, results);
}
return(_avgAnnualResults[result_id]);
}
/// <summary>
/// Calculate (average) annual results
/// </summary>
/// <param name="type"></param>
/// <param name="col"></param>
/// <param name="year">-1 means all years, the average annual will be calculated</param>
/// <returns></returns>
/// <remarks>It works best for daily output. No calculation is required for yearly output.</remarks>
public Dictionary<int, double> getAverageAnnualResults(SWATUnitType type, string col,int year)
{
//look in the cache first
string result_id = string.Format("averageannual_{0}_{1}_{2}", type, col, year);
if (!_avgAnnualResults.ContainsKey(result_id))
{
//get outputs for all subbasins/reaches with given column
string sql = string.Format("select {0},{1},{2} from [{3}]",
ScenarioResultStructure.COLUMN_NAME_YEAR, type, col,type);
if (type == SWATUnitType.SUB)
{
if(col.Equals(ScenarioResultStructure.SUBBAIN_TOTAL_NITROGEN_COLUMN))
sql = string.Format("select {0},{1},{2} as {3} from [{4}]",
ScenarioResultStructure.COLUMN_NAME_YEAR, type, ScenarioResultStructure.SUBBAIN_NITROGEN_COLUMNS_SQL, col, type);
else if (col.Equals(ScenarioResultStructure.SUBBAIN_TOTAL_PHOSPHORUS_COLUMN))
sql = string.Format("select {0},{1},{2} as {3} from [{4}]",
ScenarioResultStructure.COLUMN_NAME_YEAR, type, ScenarioResultStructure.SUBBAIN_PHOSPHORUS_COLUMNS_SQL, col, type);
}
//years
if (year != -1)
sql += string.Format(" where {0} = {1}",
ScenarioResultStructure.COLUMN_NAME_YEAR, year);
DataTable dt = GetDataTable(sql);
//summary the result by year and id
//for reach flow, average operation should be used
var query = from oneresult in dt.AsEnumerable()
group oneresult by new {
Year = oneresult.Field<int>(ScenarioResultStructure.COLUMN_NAME_YEAR), //group by year
ID = oneresult.Field<int>(type.ToString()) //group by id
} into g
select new
{
ID = g.Key.ID,
Year = g.Key.Year,
Total = g.Sum(oneresult => oneresult.Field<double>(col)),
};
Dictionary<int, double> results = new Dictionary<int, double>();
//calculate the average annual
int numofyear = 1;
if (year == -1) numofyear = EndYear - StartYear;
foreach (var oneyearid in query)
{
//Debug.WriteLine(String.Format("{0}:{1}", oneyearid.ID, oneyearid.Year));
if (!results.ContainsKey(oneyearid.ID))
results.Add(oneyearid.ID, 0.0);
results[oneyearid.ID] += oneyearid.Total / numofyear; //get the average
}
_avgAnnualResults.Add(result_id, results);
}
return _avgAnnualResults[result_id];
}