public static StringCollection GetDataColumns(string path, string table)
{
DataTable dt = GetDataTable(string.Format("PRAGMA table_info({0})", table), path);
StringCollection cols = new StringCollection();
foreach (DataRow r in dt.Rows)
{
RowItem item = new RowItem(r);
if (item.getColumnValue_String("type").ToLower().Equals("float"))
{
cols.Add(item.getColumnValue_String("name"));
}
}
return(cols);
}
/// <summary>
/// Retrieve data columns from table name
/// </summary>
/// <param name="tableName"></param>
/// <returns></returns>
/// <remarks>Only float columns are return</remarks>
public StringCollection getDataColumns(string tableName)
{
StringCollection cols = new StringCollection();
if (tableName == null)
{
return(cols);
}
tableName = tableName.Trim();
if (tableName.Length == 0)
{
return(cols);
}
if (!_columns.ContainsKey(tableName))
{
if (_scenario == null)
{
return(cols);
}
DataTable dt = _scenario.GetDataTable(
string.Format("PRAGMA table_info({0})", tableName));
foreach (DataRow r in dt.Rows)
{
RowItem item = new RowItem(r);
if (item.getColumnValue_String("type").ToLower().Equals("float"))
{
cols.Add(item.getColumnValue_String("name"));
}
}
if (cols.Count > 0)
{
_columns.Add(tableName, cols);
}
else
{
return(new StringCollection());
}
}
return(_columns[tableName]);
}
private void checkStatus()
{
if (DatabasePath == null || !File.Exists(DatabasePath))
{
_status = ScenarioResultStatus.NO_EXIST; return;
}
DataTable dt = Query.GetDataTable("select * from " + ScenarioResultStructure.TABLE_NAME_WATERSHED_AVERAGE_ANNUAL, DatabasePath);
if (dt.Rows.Count == 0)
{
_status = ScenarioResultStatus.UNSUCCESS; return;
}
foreach (DataRow r in dt.Rows)
{
RowItem item = new RowItem(r);
string name = item.getColumnValue_String("NAME");
if (name.Equals(ScenarioResultStructure.NAME_STATUS_START_YEAR))
{
_startYear = item.getColumnValue_Int("VALUE");
}
else if (name.Equals("END_YEAR"))
{
_endYear = item.getColumnValue_Int("VALUE");
}
else if (name.Equals("OUTPUT_INTERVAL"))
{
_interval = (SWATResultIntervalType)(item.getColumnValue_Int("VALUE"));
}
else if (name.Equals("SUCCESS"))
{
_status = ScenarioResultStatus.NORMAL;
}
}
//some version don't have this output, need to upgrade
if (_startYear == ScenarioResultStructure.UNKONWN_ID)
{
throw new Exception("Missing output starting year. Please upgrade your SWAT executable.");
}
_generationTime = (new System.IO.FileInfo(DatabasePath)).LastWriteTime;
}
protected override void read()
{
if (_table != null)
{
return;
}
if (_parentData == null)
{
return;
}
if (_parentData.Observation == null)
{
return;
}
//get table name
//each observed data for one corresponding table in the database
string tableName =
ObservationData.getTableName(_parentData.UnitType, _parentData.ID, _col);
//get filter based on given year
string filter = "";
if (_year > 0) //specific year
{
filter += string.Format("{0} >= '{1}-01-01' and {0} <= '{2}-12-31'", ObservationData.OBSERVATION_COLUMN_DATE, _year - 1, _year); //read two years of data to consider hydrological year
}
else //all years in between start and end year
{
if (_parentData.StartYear > 0)
{
filter += string.Format("{0} >= '{1}-01-01'", ObservationData.OBSERVATION_COLUMN_DATE, _parentData.StartYear);
}
if (_parentData.EndYear > 0)
{
if (filter.Length > 0)
{
filter += " and ";
}
filter += string.Format("{0} <= '{1}-01-01'", ObservationData.OBSERVATION_COLUMN_DATE, _parentData.EndYear);
}
}
if (filter.Length > 0)
{
filter = " where " + filter;
}
DataTable dt = _parentData.Observation.GetDataTable(
string.Format("select * from [{0}] {1}", tableName, filter));
if (dt.Columns.Count == 2)
{
//add a date time column and convert time from text to date
dt.Columns.Add(SWATUnitResult.COLUMN_NAME_DATE, typeof(DateTime));
DateTime d = DateTime.Now;
foreach (DataRow r in dt.Rows)
{
RowItem item = new RowItem(r);
if (DateTime.TryParse(item.getColumnValue_String(0), out d))
{
r[2] = d;
}
}
dt.Columns[1].ColumnName = _col;
_table = dt;
}
}
/// <summary>
/// Retrieve data columns from table name
/// </summary>
/// <param name="tableName"></param>
/// <returns></returns>
/// <remarks>Only float columns are return</remarks>
public StringCollection getDataColumns(string tableName)
{
StringCollection cols = new StringCollection();
if (tableName == null)
{
return(cols);
}
tableName = tableName.Trim();
if (tableName.Length == 0)
{
return(cols);
}
if (!_columns.ContainsKey(tableName))
{
if (_scenario == null)
{
return(cols);
}
DataTable dt = _scenario.GetDataTable(
string.Format("PRAGMA table_info({0})", tableName));
foreach (DataRow r in dt.Rows)
{
RowItem item = new RowItem(r);
if (item.getColumnValue_String("type").ToLower().Equals("float"))
{
cols.Add(item.getColumnValue_String("name"));
}
}
if (cols.Count > 0)
{
//add TN and TP option for subbasin
//they will be calculated in the interface
//all the N/P columns should exist to calculte TN/TP
if (tableName.Equals(TABLE_NAME_SUB))
{
int i = 0;
for (; i < SUBBAIN_NITROGEN_COLUMNS.Length; i++)
{
if (!cols.Contains(SUBBAIN_NITROGEN_COLUMNS[i]))
{
break;
}
}
if (i == SUBBAIN_NITROGEN_COLUMNS.Length)
{
cols.Add(SUBBAIN_TOTAL_NITROGEN_COLUMN);
}
i = 0;
for (; i < SUBBAIN_PHOSPHORUS_COLUMNS.Length; i++)
{
if (!cols.Contains(SUBBAIN_PHOSPHORUS_COLUMNS[i]))
{
break;
}
}
if (i == SUBBAIN_PHOSPHORUS_COLUMNS.Length)
{
cols.Add(SUBBAIN_TOTAL_PHOSPHORUS_COLUMN);
}
}
_columns.Add(tableName, cols);
}
else
{
return(new StringCollection());
}
}
return(_columns[tableName]);
}
private IFeatureLayer addLayer(string path, string name, bool isForObserved, bool isWorkingLayer)
{
Debug.WriteLine(DateTime.Now);
Debug.WriteLine("Adding Layer..., " + name);
if (!System.IO.File.Exists(path))
{
Debug.WriteLine(path + " doesn't exist!");
return(null);
}
IFeatureLayer layer = this.Layers.Add(path) as IFeatureLayer;
layer.SelectionEnabled = isWorkingLayer;
layer.LegendText = name;
foreach (DataColumn col in layer.DataSet.DataTable.Columns)
{
col.ColumnName = col.ColumnName.ToLower();
}
//working layer and result display
if (isWorkingLayer && !isForObserved)
{
//add result column
DataTable dt = layer.DataSet.DataTable;
dt.Columns.Add(RESULT_COLUMN, typeof(double));
//create schema for result display
layer.Symbology.EditorSettings.ClassificationType = ClassificationType.Quantities;
layer.Symbology.EditorSettings.FieldName = RESULT_COLUMN;
layer.Symbology.EditorSettings.IntervalMethod = IntervalMethod.Quantile;
layer.Symbology.EditorSettings.IntervalSnapMethod = IntervalSnapMethod.SignificantFigures;
layer.Symbology.EditorSettings.IntervalRoundingDigits = 3; //3 significant number
layer.Symbology.EditorSettings.StartSize = 5;
layer.Symbology.EditorSettings.EndSize = 25;
layer.Symbology.EditorSettings.NumBreaks = 5;
layer.Symbology.EditorSettings.UseSizeRange = true;
//start and end color
layer.Symbology.EditorSettings.StartColor = Color.Green;
layer.Symbology.EditorSettings.EndColor = Color.Red;
}
//set normal symbol
//for result display, this is just the initial symbol. The symbol would be updated based on result
//after the result is retrieved.
if (layer.DataSet.FeatureType == DotSpatial.Topology.FeatureType.Polygon) //subbasin
{
layer.Symbolizer = new PolygonSymbolizer(System.Drawing.Color.LightGray, System.Drawing.Color.Black, 0.5);
//show label for subbasin
MapLabelLayer label = new MapLabelLayer();
label.Symbology.Categories[0].Expression = "[" + ID_COLUMN_NAME + "]";
layer.LabelLayer = label;
layer.ShowLabels = true;
}
else if (layer.DataSet.FeatureType == DotSpatial.Topology.FeatureType.Line) //reach
{
layer.Symbolizer = new LineSymbolizer(System.Drawing.Color.Blue, 3.0);
//set selection sysmbol for reach as wider red to make it more obvious
layer.SelectionSymbolizer = new LineSymbolizer(System.Drawing.Color.Red, 3.0);
}
else if (layer.DataSet.FeatureType == DotSpatial.Topology.FeatureType.Point) //reservoir
{
//set the symbol color,shape and size
layer.Symbolizer = new PointSymbolizer(Color.Green, DotSpatial.Symbology.PointShape.Hexagon, 20.0);
layer.SelectionSymbolizer = new PointSymbolizer(Color.Cyan, DotSpatial.Symbology.PointShape.Hexagon, 20.0);
//also set to just show reservoir
//first to see if there are some reservoir there Type = R
if (layer.DataSet.DataTable.Rows.Count == 0)
{
Layers.Remove(layer as IMapLayer); return(null);
}
int reservoirNum = int.Parse(layer.DataSet.DataTable.Compute("count(" + ID_COLUMN_NAME + ")", "type = 'R' or type = 'r'").ToString());
if (reservoirNum <= 0)
{
Layers.Remove(layer as IMapLayer); return(null);
}
//only show reservoir
List <int> hiddenMoniterPoints = new List <int>();
for (int i = 0; i < layer.DataSet.DataTable.Rows.Count; i++)
{
ArcSWAT.RowItem item = new ArcSWAT.RowItem(layer.DataSet.DataTable.Rows[i]);
string type = item.getColumnValue_String("type");
if (!type.Equals("R") && !type.Equals("r"))
{
hiddenMoniterPoints.Add(i);
}
}
layer.RemoveFeaturesAt(hiddenMoniterPoints);
}
//add a column to show if the feature has observed data
if (isForObserved)
{
//add observed column
DataTable dt = layer.DataSet.DataTable;
dt.Columns.Add(OBSERVED_COLUMN, typeof(int));
//create schema observed column to make feature with observed data more obvious
if (layer.DataSet.FeatureType != DotSpatial.Topology.FeatureType.Polygon)
{
layer.Symbology.ClearCategories();
//get the observed data status
ArcSWAT.SWATUnitType unitType = ArcSWAT.SWATUnitType.RCH;
if (layer.DataSet.FeatureType == DotSpatial.Topology.FeatureType.Point)
{
unitType = ArcSWAT.SWATUnitType.RES;
}
foreach (DataRow r in layer.DataSet.DataTable.Rows)
{
int id = getIDFromFeatureRow(r);
if (_project.Observation(Interval).getObservedData(unitType, id).Count > 0)
{
r[OBSERVED_COLUMN] = 1;
}
else
{
r[OBSERVED_COLUMN] = 0;
}
}
//set the category
IFeatureCategory cat_observed = layer.Symbology.CreateNewCategory(Color.Blue, 3.0) as IFeatureCategory;
cat_observed.FilterExpression = string.Format("[{0}]=0", OBSERVED_COLUMN.ToUpper());
IFeatureCategory cat_no_observed = layer.Symbology.CreateNewCategory(Color.Red, 3.0) as IFeatureCategory;
cat_no_observed.FilterExpression = string.Format("[{0}]=1", OBSERVED_COLUMN.ToUpper());
//for reservoir, change default size and shape
if (layer.DataSet.FeatureType == DotSpatial.Topology.FeatureType.Point)
{
cat_observed.SelectionSymbolizer = new PointSymbolizer(Color.Cyan, DotSpatial.Symbology.PointShape.Hexagon, 20.0);
cat_no_observed.SelectionSymbolizer = new PointSymbolizer(Color.Cyan, DotSpatial.Symbology.PointShape.Hexagon, 20.0);
((cat_observed.Symbolizer as PointSymbolizer).Symbols[0] as SimpleSymbol).Size = new Size2D(20.0, 20.0);
((cat_no_observed.Symbolizer as PointSymbolizer).Symbols[0] as SimpleSymbol).Size = new Size2D(20.0, 20.0);
((cat_observed.Symbolizer as PointSymbolizer).Symbols[0] as SimpleSymbol).PointShape = DotSpatial.Symbology.PointShape.Hexagon;
((cat_no_observed.Symbolizer as PointSymbolizer).Symbols[0] as SimpleSymbol).PointShape = DotSpatial.Symbology.PointShape.Hexagon;
}
layer.Symbology.AddCategory(cat_observed);
layer.Symbology.AddCategory(cat_no_observed);
layer.ApplyScheme(layer.Symbology);
}
}
if (isWorkingLayer)
{
layer.SelectionEnabled = true;
//for selection changed event
layer.SelectionChanged += (ss, _e) =>
{
if (onLayerSelectionChanged == null)
{
return;
}
ArcSWAT.SWATUnitType unitType = ArcSWAT.SWATUnitType.SUB;
if (layer.DataSet.FeatureType == DotSpatial.Topology.FeatureType.Point)
{
unitType = ArcSWAT.SWATUnitType.RES;
}
else if (layer.DataSet.FeatureType == DotSpatial.Topology.FeatureType.Line)
{
unitType = ArcSWAT.SWATUnitType.RCH;
}
int id = -1;
if (layer.Selection.NumRows() > 0)
{
IFeature fea = layer.Selection.ToFeatureList()[0];
id = getIDFromFeatureRow(fea.DataRow);
}
onLayerSelectionChanged(unitType, id);
_id = id;
};
}
return(layer);
}
private void checkStatus()
{
if (DatabasePath == null || !File.Exists(DatabasePath)) { _status = ScenarioResultStatus.NO_EXIST; return; }
DataTable dt = Query.GetDataTable("select * from " + ScenarioResultStructure.TABLE_NAME_WATERSHED_AVERAGE_ANNUAL, DatabasePath);
if (dt.Rows.Count == 0) { _status = ScenarioResultStatus.UNSUCCESS; return; }
foreach (DataRow r in dt.Rows)
{
RowItem item = new RowItem(r);
string name = item.getColumnValue_String("NAME");
if (name.Equals(ScenarioResultStructure.NAME_STATUS_START_YEAR))
_startYear = item.getColumnValue_Int("VALUE");
else if (name.Equals("END_YEAR"))
_endYear = item.getColumnValue_Int("VALUE");
else if (name.Equals("OUTPUT_INTERVAL"))
_interval = (SWATResultIntervalType)(item.getColumnValue_Int("VALUE"));
else if (name.Equals("SUCCESS"))
_status = ScenarioResultStatus.NORMAL;
}
//some version don't have this output, need to upgrade
if (_startYear == ScenarioResultStructure.UNKONWN_ID)
throw new Exception("Missing output starting year. Please upgrade your SWAT executable.");
_generationTime = (new System.IO.FileInfo(DatabasePath)).LastWriteTime;
}
protected override void read()
{
if (_table != null) return;
if(_parentData == null) return;
if(_parentData.Observation == null) return;
//get table name
//each observed data for one corresponding table in the database
string tableName =
ObservationData.getTableName(_parentData.UnitType,_parentData.ID,_col);
//get filter based on given year
string filter = "";
if (_year > 0) //specific year
{
filter += string.Format("{0} >= '{1}-01-01' and {0} <= '{2}-12-31'", ObservationData.OBSERVATION_COLUMN_DATE, _year-1,_year); //read two years of data to consider hydrological year
}
else //all years in between start and end year
{
if (_parentData.StartYear > 0)
{
filter += string.Format("{0} >= '{1}-01-01'", ObservationData.OBSERVATION_COLUMN_DATE, _parentData.StartYear);
}
if (_parentData.EndYear > 0)
{
if(filter.Length > 0) filter += " and ";
filter += string.Format("{0} <= '{1}-01-01'", ObservationData.OBSERVATION_COLUMN_DATE, _parentData.EndYear);
}
}
if (filter.Length > 0)
filter = " where " + filter;
DataTable dt = _parentData.Observation.GetDataTable(
string.Format("select * from [{0}] {1}",tableName,filter));
if (dt.Columns.Count == 2)
{
//add a date time column and convert time from text to date
dt.Columns.Add(SWATUnitResult.COLUMN_NAME_DATE, typeof(DateTime));
DateTime d = DateTime.Now;
foreach (DataRow r in dt.Rows)
{
RowItem item = new RowItem(r);
if (DateTime.TryParse(item.getColumnValue_String(0), out d))
r[2] = d;
}
dt.Columns[1].ColumnName = _col;
_table = dt;
}
}
public static StringCollection GetDataColumns(string path, string table)
{
DataTable dt = GetDataTable(string.Format("PRAGMA table_info({0})", table),path);
StringCollection cols = new StringCollection();
foreach (DataRow r in dt.Rows)
{
RowItem item = new RowItem(r);
if (item.getColumnValue_String("type").ToLower().Equals("float"))
cols.Add(item.getColumnValue_String("name"));
}
return cols;
}