private Dictionary <int, SWATUnit> readUnitBasicInfo(SWATUnitType type)
{
Dictionary <int, SWATUnit> units = new Dictionary <int, SWATUnit>();
List <int> ids = new List <int>();
DataTable dt = GetDataTable("select * from " + getInfoTableFromType(type));
foreach (DataRow r in dt.Rows)
{
SWATUnit unit = null;
switch (type)
{
case SWATUnitType.HRU: unit = new HRU(r, this); break;
case SWATUnitType.SUB: unit = new Subbasin(r, this); break;
case SWATUnitType.RCH: unit = new Reach(r, this); break;
case SWATUnitType.RES: unit = new Reservoir(r, this); break;
}
if (unit != null && unit.ID != ScenarioResultStructure.UNKONWN_ID && !units.ContainsKey(unit.ID))
{
units.Add(unit.ID, unit);
ids.Add(unit.ID);
}
}
_units.Add(type, units);
_unitIds.Add(type, ids);
return(units);
}
/// <summary>
/// Load given csv file into the database
/// </summary>
/// <param name="csvFile"></param>
/// <param name="unitType"></param>
/// <param name="id"></param>
/// <param name="col"></param>
public bool loadCSV(string csvFile, SWATUnitType unitType, int id, string col)
{
if (!System.IO.File.Exists(csvFile))
{
throw new Exception(csvFile + " doesn't exist.");
}
if (getObservedData(unitType, id, col) != null)
{
if (System.Windows.Forms.MessageBox.Show(
string.Format("Observed data for {0},{1},{2} already exist. Do you want to overwrite?", unitType, id, col),
SWAT_SQLite.NAME, System.Windows.Forms.MessageBoxButtons.YesNo)
== System.Windows.Forms.DialogResult.No)
{
return(false);
}
}
//save data
using (CsvReader csv = new CsvReader(new StreamReader(csvFile), true))
{
if (csv.FieldCount < 2)
{
throw new Exception("There should be at least two data columns in the csv file!");
}
//create the table
//this would also delete existing data
string tableName = "[" + getTableName(unitType, id, col) + "]";
SQLite.PrepareTable(_databasePath, tableName,
OBSERVED_TABLE_DATE_COLUMN, OBSERVED_TABLE_VALUE_COLUMN);
StringBuilder sb = new StringBuilder();
DateTime date = DateTime.Now;
double value = -99.0;
while (csv.ReadNextRecord())
{
if (DateTime.TryParse(csv[0], out date) &&
double.TryParse(csv[1], out value) && value >= 0)
{
sb.Append(string.Format(INSERT_SQL_FORMAT, tableName, date, value));
}
}
if (sb.Length == 0)
{
throw new Exception("The given file is empty!");
}
SQLite.insert(_databasePath, sb.ToString());
//update the file status. It may not exist before the data is loaded.
_exist = System.IO.File.Exists(_databasePath);
//reload the data
loadData(unitType, id, col);
return(true);
}
}
public SWATUnitObservationData getObservedData(SWATUnitType unitType, int id, string col)
{
string uniqueID = getUniqueId(unitType, id, col);
if (_allData.ContainsKey(uniqueID))
{
return(_allData[uniqueID]);
}
return(null);
}
public SWATUnitObservationData(SWATUnitColumnYearResult result, ObservationData parent)
{
_parentData = parent;
_id = result.UnitResult.Unit.ID;
_unitType = result.UnitResult.Unit.Type;
_col = result.Column;
_startYear = result.UnitResult.Unit.Scenario.StartYear;
_endYear = result.UnitResult.Unit.Scenario.EndYear;
}
private string getUniqueId(SWATUnitColumnYearResult result)
{
int startYear = result.UnitResult.Unit.Scenario.StartYear;
int endYear = result.UnitResult.Unit.Scenario.EndYear;
SWATUnitType unitType = result.UnitResult.Unit.Type;
int id = result.UnitResult.Unit.ID;
string col = result.Column;
return(getUniqueId(unitType, id, col, startYear, endYear));
}
/// <summary>
/// Used to get observed data for display
/// </summary>
/// <param name="unitType"></param>
/// <param name="id"></param>
/// <returns></returns>
public List <SWATUnitObservationData> getObservedData(SWATUnitType unitType, int id)
{
string display_id = string.Format("{0}_{1}", unitType, id);
if (_allData_display.ContainsKey(display_id))
{
return(_allData_display[display_id]);
}
return(new List <SWATUnitObservationData>());
}
public SWATUnitObservationData(SWATUnitColumnYearResult result, ObservationData parent)
{
_parentData = parent;
_id = result.UnitResult.Unit.ID;
_unitType = result.UnitResult.Unit.Type;
_col = result.Column;
_startYear = result.UnitResult.Unit.Scenario.StartYear;
_endYear = result.UnitResult.Unit.Scenario.EndYear;
}
public SWATUnitObservationData(int id, SWATUnitType unitType,
string dataType,
int startYear, int endYear,
ObservationData parent)
{
_parentData = parent;
_id = id;
_unitType = unitType;
_col = dataType;
_startYear = startYear;
_endYear = endYear;
}
private void loadData(SWATUnitType type, int id, string col)
{
if (!_exist)
{
return;
}
string tableName = getTableName(type, id, col);
DataTable dt = GetDataTable(
string.Format("select name from sqlite_master where type = 'table' and name = '{0}'", tableName));
if (dt.Rows.Count > 0)
{
//see if there are some data
//don't consider empty tables
dt = GetDataTable(
string.Format("select count(*) from [{0}]", tableName));
if (dt.Rows.Count == 0)
{
return;
}
RowItem item = new RowItem(dt.Rows[0]);
if (item.getColumnValue_Int(0) == 0)
{
return;
}
//read data
string dataUniqueId = getUniqueId(type, id, col);
//remove previous one
_allData.Remove(dataUniqueId);
_allData_display.Remove(dataUniqueId);
//add new one
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);
}
}
public static string[] getObservationDataColumns(SWATUnitType unitType)
{
if (unitType == SWATUnitType.RCH)
{
return(OBSERVATION_REACH_COLUMNS);
}
if (unitType == SWATUnitType.RES)
{
return(OBSERVATION_RESERVOIR_COLUMNS);
}
return(null);
}
public SWATUnitObservationData(int id, SWATUnitType unitType,
string dataType,
int startYear, int endYear,
ObservationData parent)
{
_parentData = parent;
_id = id;
_unitType = unitType;
_col = dataType;
_startYear = startYear;
_endYear = endYear;
}
/// <summary>
/// Get tables with data in it
/// </summary>
/// <param name="type"></param>
/// <returns></returns>
public StringCollection getResultTablesWithData(SWATUnitType type)
{
string[] tbls_fromType = getResultTableNames(type);
StringCollection tbls = new StringCollection();
foreach (string tbl in tbls_fromType)
{
if (isTableHasData(tbl))
{
tbls.Add(tbl);
}
}
return(tbls);
}
public SWATUnit getSWATUnit(SWATUnitType type, int id)
{
if (type == SWATUnitType.WSHD)
{
return(_watershed);
}
Dictionary <int, SWATUnit> units = _units[type];
if (units.ContainsKey(id))
{
return(units[id]);
}
return(null);
}
public static string[] getSWATFileExtentions(SWATUnitType type)
{
switch (type)
{
case SWATUnitType.HRU: return(SWAT_UNIT_EXTENSIONS_HRU);
case SWATUnitType.SUB: return(SWAT_UNIT_EXTENSIONS_SUB);
case SWATUnitType.RCH: return(SWAT_UNIT_EXTENSIONS_RCH);
case SWATUnitType.RES: return(SWAT_UNIT_EXTENSIONS_RES);
default: return(new string[] {});
}
}
private string getInfoTableFromType(SWATUnitType type)
{
switch (type)
{
case SWATUnitType.HRU: return(ScenarioResultStructure.INFO_TABLE_NAME_HRU);
case SWATUnitType.SUB: return(ScenarioResultStructure.INFO_TABLE_NAME_SUB);
case SWATUnitType.RCH: return(ScenarioResultStructure.INFO_TABLE_NAME_RCH);
case SWATUnitType.RES: return(ScenarioResultStructure.INFO_TABLE_NAME_RSV);
default: return("");
}
}
public List <int> getSWATUnitIDs(SWATUnitType type)
{
List <int> ids = new List <int>();
if (type == SWATUnitType.UNKNOWN || type == SWATUnitType.WSHD)
{
return(ids);
}
if (_unitIds == null || !_unitIds.ContainsKey(type))
{
return(ids);
}
return(_unitIds[type]);
}
public static string[] getResultTableNames(SWATUnitType type)
{
switch (type)
{
case SWATUnitType.HRU: return(HRU_TABLES);
case SWATUnitType.RCH: return(REACH_TABLES);
case SWATUnitType.SUB: return(SUBBASIN_TABLES);
case SWATUnitType.RES: return(RESERVOIR_TABLES);
case SWATUnitType.WSHD: return(WATERSHED_TABLES);
default: return(new string[] { });
}
}
public bool delete(SWATUnitType unitType, int id, string col)
{
if (getObservedData(unitType, id, col) == null)
{
return(true);
}
//empty the table, but not drop the table
string tableName = "[" + getTableName(unitType, id, col) + "]";
SQLite.PrepareTable(_databasePath, tableName,
OBSERVED_TABLE_DATE_COLUMN, OBSERVED_TABLE_VALUE_COLUMN);
//reload all observed data
loadData();
return(true);
}
/// <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];
}
private string getUniqueId(SWATUnitType type, int id, string col, int startYear, int endYear)
{
return(string.Format("{0}_{1}_{2}_{3}_{4}",
type, id, col, startYear, endYear));
}
public static string getTableName(SWATUnitType type, int id, string col)
{
return string.Format("{0}{3}{1}{3}{2}", type, id, col, OBSERVATION_TABLE_NAME_DELIMITER);
}
public SWATUnit getSWATUnit(SWATUnitType type, int id)
{
if (type == SWATUnitType.WSHD) return _watershed;
Dictionary<int, SWATUnit> units = _units[type];
if (units.ContainsKey(id)) return units[id];
return null;
}
private void getPerformanceTableForType(int splitYear, bool withSplitYear, DataTable dt, SWATUnitType type, StatisticCompareType statisticType)
{
Dictionary<int, SWATUnit> units = null;
if (type == SWATUnitType.RCH)
units = _reaches;
else if (type == SWATUnitType.RES)
units = _reservoirs;
if (units == null) return;
StringCollection ids = new StringCollection();
foreach (SWATUnit oneUnit in units.Values)
{
foreach (SWATUnitResult unitResult in oneUnit.Results.Values)
{
foreach (string col in unitResult.Columns)
{
SWATUnitColumnYearResult oneResult = unitResult.getResult(col, -1);
if (oneResult.CompareWithObserved == null) continue;
//avoid repeat records, like TSS
string id = string.Format("{0}_{1}_{2}", unitResult.Unit.Type, unitResult.Unit.ID, col);
if (ids.Contains(id)) continue;
ids.Add(id);
//create a new row
DataRow r = dt.NewRow();
r[0] = unitResult.Unit.Type.ToString();
r[1] = unitResult.Unit.ID;
r[2] = ObservationData.getObservationColumnFromSWAT(col);
double total = oneResult.CompareWithObserved.Statistics.Statistic("",statisticType);
r[3] = Math.Round(total, 4);
if (withSplitYear)
{
double before = ScenarioResultStructure.EMPTY_VALUE;
double after = ScenarioResultStructure.EMPTY_VALUE;
oneResult.CompareWithObserved.Statistics.Statistic(splitYear,statisticType, out before, out after);
r[4] = Math.Round(before, 4);
r[5] = Math.Round(after, 4);
}
dt.Rows.Add(r);
}
}
}
}
private string getUniqueId(SWATUnitType type, int id, string col)
{
return(getUniqueId(type, id, col, -1, -1));
}
public static string getTableName(SWATUnitType type, int id, string col)
{
return(string.Format("{0}{3}{1}{3}{2}", type, id, col, OBSERVATION_TABLE_NAME_DELIMITER));
}
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>
/// Used to get observed data for display
/// </summary>
/// <param name="unitType"></param>
/// <param name="id"></param>
/// <returns></returns>
public List<SWATUnitObservationData> getObservedData(SWATUnitType unitType, int id)
{
string display_id = string.Format("{0}_{1}", unitType, id);
if (_allData_display.ContainsKey(display_id)) return _allData_display[display_id];
return new List<SWATUnitObservationData>();
}
private void getPerformanceTableForType(int splitYear, bool withSplitYear, DataTable dt, SWATUnitType type, StatisticCompareType statisticType)
{
Dictionary <int, SWATUnit> units = null;
if (type == SWATUnitType.RCH)
{
units = _reaches;
}
else if (type == SWATUnitType.RES)
{
units = _reservoirs;
}
if (units == null)
{
return;
}
StringCollection ids = new StringCollection();
foreach (SWATUnit oneUnit in units.Values)
{
foreach (SWATUnitResult unitResult in oneUnit.Results.Values)
{
foreach (string col in unitResult.Columns)
{
SWATUnitColumnYearResult oneResult = unitResult.getResult(col, -1);
if (oneResult.CompareWithObserved == null)
{
continue;
}
//avoid repeat records, like TSS
string id = string.Format("{0}_{1}_{2}", unitResult.Unit.Type, unitResult.Unit.ID, col);
if (ids.Contains(id))
{
continue;
}
ids.Add(id);
//create a new row
DataRow r = dt.NewRow();
r[0] = unitResult.Unit.Type.ToString();
r[1] = unitResult.Unit.ID;
r[2] = ObservationData.getObservationColumnFromSWAT(col);
double total = oneResult.CompareWithObserved.Statistics.Statistic("", statisticType);
r[3] = Math.Round(total, 4);
if (withSplitYear)
{
double before = ScenarioResultStructure.EMPTY_VALUE;
double after = ScenarioResultStructure.EMPTY_VALUE;
oneResult.CompareWithObserved.Statistics.Statistic(splitYear, statisticType, out before, out after);
r[4] = Math.Round(before, 4);
r[5] = Math.Round(after, 4);
}
dt.Rows.Add(r);
}
}
}
}
/// <summary>
/// Load given csv file into the database
/// </summary>
/// <param name="csvFile"></param>
/// <param name="unitType"></param>
/// <param name="id"></param>
/// <param name="col"></param>
public bool loadCSV(string csvFile, SWATUnitType unitType, int id, string col)
{
if(!System.IO.File.Exists(csvFile))
throw new Exception(csvFile + " doesn't exist.");
if(getObservedData(unitType,id,col) != null)
if(System.Windows.Forms.MessageBox.Show(
string.Format("Observed data for {0},{1},{2} already exist. Do you want to overwrite?",unitType,id,col),
SWAT_SQLite.NAME, System.Windows.Forms.MessageBoxButtons.YesNo)
== System.Windows.Forms.DialogResult.No)
return false;
//save data
using (CsvReader csv = new CsvReader(new StreamReader(csvFile),true))
{
if (csv.FieldCount < 2)
throw new Exception("There should be at least two data columns in the csv file!");
//create the table
//this would also delete existing data
string tableName = "[" + getTableName(unitType, id, col) + "]";
SQLite.PrepareTable(_databasePath, tableName,
OBSERVED_TABLE_DATE_COLUMN, OBSERVED_TABLE_VALUE_COLUMN);
StringBuilder sb = new StringBuilder();
DateTime date = DateTime.Now;
double value = -99.0;
while (csv.ReadNextRecord())
{
if (DateTime.TryParse(csv[0], out date) &&
double.TryParse(csv[1],out value) && value >= 0)
sb.Append(string.Format(INSERT_SQL_FORMAT, tableName, date, value));
}
if (sb.Length == 0)
throw new Exception("The given file is empty!");
SQLite.insert(_databasePath,sb.ToString());
//update the file status. It may not exist before the data is loaded.
_exist = System.IO.File.Exists(_databasePath);
//reload the data
loadData(unitType, id, col);
return true;
}
}
public static string[] getObservationDataColumns(SWATUnitType unitType)
{
if (unitType == SWATUnitType.RCH) return OBSERVATION_REACH_COLUMNS;
if (unitType == SWATUnitType.RES) return OBSERVATION_RESERVOIR_COLUMNS;
return null;
}
private string getUniqueId(SWATUnitType type, int id, string col, int startYear, int endYear)
{
return string.Format("{0}_{1}_{2}_{3}_{4}",
type, id, col, startYear, endYear);
}
public bool delete(SWATUnitType unitType, int id, string col)
{
if (getObservedData(unitType, id, col) == null) return true;
//empty the table, but not drop the table
string tableName = "[" + getTableName(unitType, id, col) + "]";
SQLite.PrepareTable(_databasePath, tableName,
OBSERVED_TABLE_DATE_COLUMN, OBSERVED_TABLE_VALUE_COLUMN);
//reload all observed data
loadData();
return true;
}
private Dictionary<int, SWATUnit> readUnitBasicInfo(SWATUnitType type)
{
Dictionary<int, SWATUnit> units = new Dictionary<int, SWATUnit>();
List<int> ids = new List<int>();
DataTable dt = GetDataTable("select * from " + getInfoTableFromType(type));
foreach (DataRow r in dt.Rows)
{
SWATUnit unit = null;
switch (type)
{
case SWATUnitType.HRU: unit = new HRU(r, this); break;
case SWATUnitType.SUB: unit = new Subbasin(r, this); break;
case SWATUnitType.RCH: unit = new Reach(r, this); break;
case SWATUnitType.RES: unit = new Reservoir(r, this); break;
}
if (unit != null && unit.ID != ScenarioResultStructure.UNKONWN_ID && !units.ContainsKey(unit.ID))
{
units.Add(unit.ID, unit);
ids.Add(unit.ID);
}
}
_units.Add(type,units);
_unitIds.Add(type, ids);
return units;
}
public SWATUnitObservationData getObservedData(SWATUnitType unitType, int id, string col)
{
string uniqueID = getUniqueId(unitType, id, col);
if (_allData.ContainsKey(uniqueID)) return _allData[uniqueID];
return null;
}
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 string getUniqueId(SWATUnitType type, int id, string col)
{
return getUniqueId(type, id, col, -1, -1);
}
private string getInfoTableFromType(SWATUnitType type)
{
switch (type)
{
case SWATUnitType.HRU: return ScenarioResultStructure.INFO_TABLE_NAME_HRU;
case SWATUnitType.SUB: return ScenarioResultStructure.INFO_TABLE_NAME_SUB;
case SWATUnitType.RCH: return ScenarioResultStructure.INFO_TABLE_NAME_RCH;
case SWATUnitType.RES: return ScenarioResultStructure.INFO_TABLE_NAME_RSV;
default: return "";
}
}
private void loadData(SWATUnitType type, int id, string col)
{
if (!_exist) return;
string tableName = getTableName(type, id, col);
DataTable dt = GetDataTable(
string.Format("select name from sqlite_master where type = 'table' and name = '{0}'", tableName));
if (dt.Rows.Count > 0)
{
//see if there are some data
//don't consider empty tables
dt = GetDataTable(
string.Format("select count(*) from [{0}]", tableName));
if (dt.Rows.Count == 0) return;
RowItem item = new RowItem(dt.Rows[0]);
if (item.getColumnValue_Int(0) == 0) return;
//read data
string dataUniqueId = getUniqueId(type, id, col);
//remove previous one
_allData.Remove(dataUniqueId);
_allData_display.Remove(dataUniqueId);
//add new one
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]);
}
public static string[] getSWATFileExtentions(SWATUnitType type)
{
switch (type)
{
case SWATUnitType.HRU: return SWAT_UNIT_EXTENSIONS_HRU;
case SWATUnitType.SUB: return SWAT_UNIT_EXTENSIONS_SUB;
case SWATUnitType.RCH: return SWAT_UNIT_EXTENSIONS_RCH;
case SWATUnitType.RES: return SWAT_UNIT_EXTENSIONS_RES;
default: return new string[]{};
}
}
public List<int> getSWATUnitIDs(SWATUnitType type)
{
List<int> ids = new List<int>();
if (type == SWATUnitType.UNKNOWN || type == SWATUnitType.WSHD) return ids;
if (_unitIds == null || !_unitIds.ContainsKey(type)) return ids;
return _unitIds[type];
}
