private void AddHydrossClick(object sender, EventArgs e)
{
Hydross.HydrossScenarioPicker dlg = new Reclamation.TimeSeries.Hydross.HydrossScenarioPicker();
try
{
DB.SuspendTreeUpdates();
if (dlg.ShowDialog() == DialogResult.OK)
{
ShowAsBusy("Reading Hydross data");
var tbl = new TimeSeriesDatabaseDataSet.SeriesCatalogDataTable();
Hydross.HydrossTree.Generate(tbl, DB.DataSource, dlg.ScenarioFiles, DB.NextSDI(), this.CurrentFolder.ID);
DB.Server.SaveTable(tbl);
// add scenarios..
var tblScen = DB.GetScenarios();
foreach (var item in dlg.ScenarioFiles)
{
string scenarioPath = ConnectionStringUtility.MakeFileNameRelative("FileName=" + item, DB.DataSource);
tblScen.AddScenarioRow(Path.GetFileNameWithoutExtension(item), true, scenarioPath, 0);
}
DB.Server.SaveTable(tblScen);
DatabaseChanged();
}
}
finally
{
ShowAsReady("Done with Hydross import");
DB.ResumeTreeUpdates();
}
}
private static bool AddRow(DataTable seriesCatalog, int ID, int parentID, string scenName, string HydrossName, string Description, string HydrossStation, string HydrossVarcode, string HydrossUnits, string HydrossExtra1, string HydrossExtra2)
{
DataRow dr = seriesCatalog.NewRow();
dr["id"] = ID;
dr["ParentID"] = parentID;
dr["IsFolder"] = false;
dr["SortOrder"] = 1;
dr["Source"] = "Hydross";
dr["Name"] = HydrossName + ": " + Description;
dr["SiteName"] = HydrossStation;
dr["Units"] = HydrossUnits;
dr["TimeInterval"] = "Monthly";
dr["Parameter"] = "Flow";
dr["FileIndex"] = 0;
dr["Provider"] = "HydrossSeries";
string cstr = string.Format("FileName={0};stationID={1};varcode={2};extra1={3};extra2={4};",
new object[] { scenName, HydrossStation, HydrossVarcode, HydrossExtra1, HydrossExtra2 });
dr["ConnectionString"] = ConnectionStringUtility.MakeFileNameRelative(cstr, s_databasePath);
seriesCatalog.Rows.Add(dr);
return(true);
}
/// <summary>
/// Scenario is defined by the workbook and worksheet names
/// </summary>
public void LoadScenarios()
{
m_db.ClearScenarios();
var table = m_db.GetScenarios();
int i = 0;
foreach (var pct in UrgwomForecastLevels)
{
foreach (var month in UrgwomMonths)
{
int year = UrgwonStartYear;
int count = UrgwomEndYear - UrgwonStartYear + 1;
for (int run = 0; run < count; run++)
{
string fn = CreateFileName(excelFilename, month, pct);
var connectionString = "Year=" + year + ";Month=" + month + ";Percent=" + pct + ";FileName=" + fn + ";SheetName=" + "Run" + run;
connectionString = ConnectionStringUtility.MakeFileNameRelative(connectionString, m_db.DataSource);
table.AddScenarioRow(month + " " + pct + "% " + year, i < 2, connectionString, 0);
i++;
year++;
}
}
}
m_db.Server.SaveTable(table);
}
/// <summary>
/// insert new tree row
/// </summary>
static void AddNewRow(int sdi, int parentID, bool isFolder, string modsimName, string displayName, string timeSeriesName)
{
var row = seriesCatalog.NewSeriesCatalogRow();
row.id = sdi;
seriesCatalog.Rows.Add(row);
row.ParentID = parentID;
if (isFolder)
{
row.IsFolder = 1;
row.Name = displayName;
row.TableName = "";
}
else
{
ModsimSeries s = new ModsimSeries(mi, modsimName, timeSeriesName);
row.IsFolder = 0;
row.ConnectionString = ConnectionStringUtility.MakeFileNameRelative(s.ConnectionString, s_db.DataSource);
row.iconname = "Modsim";
row.Provider = s.Provider;
row.Name = displayName;
row.TimeInterval = s.TimeInterval.ToString();
//row.TableName = "empty" + Guid.NewGuid().ToString();
row.siteid = s.SiteID;
row.Parameter = s.Parameter;
row.Units = s.Units;
//row.DisplayUnits = s.DisplayUnits;
row.Expression = s.Expression;
row.Notes = s.Notes;
}
}
/// <summary>
/// Updates database if the original source text file still exists and has
/// been modified. All series data is replaced with the source file contents
/// </summary>
private void Update()
{
if (m_db == null)
{
return;
}
Logger.WriteLine("Checking series " + Name + " (" + ID + ") for updates");
string dir = Path.GetDirectoryName(m_db.DataSource);
bool canUpdate = CanUpdateFromFile(ConnectionString, dir);
if (canUpdate)
{
string fileName = ConnectionStringUtility.GetToken(ConnectionString, "FileName", "");
if (!Path.IsPathRooted(fileName))
{
fileName = Path.Combine(dir, fileName);
}
Logger.WriteLine("Updating: File has changed " + fileName);
TextSeries ts = TextSeries.ReadFromFile(fileName);
ConnectionString = ts.ConnectionString;
ConnectionString = ConnectionStringUtility.MakeFileNameRelative(ConnectionString, m_db.DataSource);
m_db.SaveProperties(this); // last write time needs to be updated.
m_db.SaveTimeSeriesTable(ID, ts, DatabaseSaveOptions.DeleteAllExisting);
}
}
private void CreateSeriesAF(AccessDB mdb, string plantName, string dataType, int parentID)
{
string units = "acre-feet";
string name = plantName + " " + dataType + " " + units;
string sheetName = plantName + " " + dataType;
string cs = "FileName=" + m_mdbFileName + ";PlantName=" + plantName + ";DataType=" + dataType; // connection string
cs = ConnectionStringUtility.MakeFileNameRelative(cs, m_databasePath);
seriesCatalog.AddSeriesCatalogRow(ID++, parentID, false, ID, "BpaHydsimSeriesAccess", name, sheetName, units,
TimeInterval.Monthly.ToString(), "Parameter", "", "BpaHydsimSeriesAccess", cs, "", "", true);
}
private void AddRiverWareSingleRdf_Click(object sender, EventArgs e)
{
var dlg = new ScenarioPicker.ScenarioPicker();
dlg.Text = "Select RiverWare Output Files";
dlg.Dialog.DefaultExt = ".rdf";
dlg.Dialog.Filter = "RiverWare Data File (*.rdf)|*.rdf|All Files|*.*";
dlg.Dialog.Title = "Open RiverWare RDF Files";
try
{
DB.SuspendTreeUpdates();
var result = dlg.ShowDialog();
if (result == DialogResult.OK)
{
if (dlg.ScenariosChecked && dlg.ScenarioFiles.Count > 0)
{
//create scenarios
ShowAsBusy("Reading RiverWare data");
var tblScen = DB.GetScenarios();
foreach (var item in dlg.ScenarioFiles)
{
string scenarioPath = ConnectionStringUtility.MakeFileNameRelative("FileName=" + item, DB.DataSource);
tblScen.AddScenarioRow(Path.GetFileNameWithoutExtension(item), true, scenarioPath, 0);
}
//add first file in the list to the tree
if (dlg.AddToTreeChecked)
{
RiverWare.RiverWareTree.AddRiverWareFileToDatabase(dlg.ScenarioFiles[0], CurrentFolder, DB);
}
DB.Server.SaveTable(tblScen);
DatabaseChanged();
}
else
if (dlg.AddToTreeChecked)
{
//add to tree, but not to scenairo list
ShowAsBusy("Reading RiverWare data");
for (int i = 0; i < dlg.ScenarioFiles.Count; i++)
{
string fn = dlg.ScenarioFiles[i].ToString();
RiverWare.RiverWareTree.AddRiverWareFileToDatabase(fn, CurrentFolder, DB);
}
DatabaseChanged();
}
}
}
finally
{
ShowAsReady("Done with RiverWare import");
DB.ResumeTreeUpdates();
}
}
/// <summary>
/// SQLite convered from DSS
/// https://github.com/usbr/convertdss
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void addSqLiteModel_Click(object sender, EventArgs e)
{
var dlg = new Reclamation.TimeSeries.ScenarioPicker.ScenarioPicker();
dlg.Text = "Select SqLite files";
dlg.Dialog.DefaultExt = ".db";
dlg.Dialog.Filter = "SQLite *.db|*.db|All Files|*.*";
dlg.Dialog.Title = "Open SQLite File (from DSS)";
try
{
DB.SuspendTreeUpdates();
var result = dlg.ShowDialog();
if (result == DialogResult.OK)
{
if (dlg.ScenariosChecked && dlg.ScenarioFiles.Count > 0)
{
//create scenarios
ShowAsBusy("Reading data");
var tblScen = DB.GetScenarios();
foreach (var item in dlg.ScenarioFiles)
{
string scenarioPath = ConnectionStringUtility.MakeFileNameRelative("FileName=" + item, DB.DataSource);
tblScen.AddScenarioRow(Path.GetFileNameWithoutExtension(item), true, scenarioPath, 0);
}
//add first file in the list to the tree
if (dlg.AddToTreeChecked)
{
SQLiteSeries.CreatePiscesTree(dlg.ScenarioFiles[0], CurrentFolder, DB);
}
DB.Server.SaveTable(tblScen);
DatabaseChanged();
}
else
if (dlg.AddToTreeChecked)
{
//add to tree, but not to scenairo list
ShowAsBusy("Reading data");
for (int i = 0; i < dlg.ScenarioFiles.Count; i++)
{
string fn = dlg.ScenarioFiles[i].ToString();
SQLiteSeries.CreatePiscesTree(fn, CurrentFolder, DB);
}
DatabaseChanged();
}
}
}
finally
{
ShowAsReady("Done with Modsim import");
DB.ResumeTreeUpdates();
}
}
public static void CreatePiscesTree(string filename, PiscesFolder CurrentFolder, TimeSeries.TimeSeriesDatabase db)
{
var sqlite = new SQLiteServer(filename);
/*insert into seriescatalog (id,parentid,name,isfolder) values (1,1,"database name",1);
* insert into seriescatalog (id,parentid,name,units,tablename)
*
*/
Logger.WriteLine("reading " + filename, "ui");
var variables = sqlite.Table("variables", "select a.var_name,b.value AS units from variables a "
+ "left join attributes b on a.var_name = b.var_name and b.attribute_name = 'units' order by a.var_name DESC");
Logger.WriteLine("variable count = " + variables.Rows.Count, "ui");
var sc = db.GetSeriesCatalog();
string rootFolderName = Path.GetFileNameWithoutExtension(filename);
var rootID = sc.AddFolder(rootFolderName);
var prev_FolderName = rootFolderName;
int folderID = rootID;
int id = sc.NextID();
var cs = ConnectionStringUtility.MakeFileNameRelative("FileName=" + filename + ";", db.DataSource);
foreach (DataRow row in variables.Rows)
{
var var_name = row["var_name"].ToString();
if (var_name.IndexOf("_") > 0)
{
var tokens = var_name.Split('_');
if (tokens[0] != prev_FolderName)
{
folderID = sc.AddFolder(tokens[0], rootID);
id++;
prev_FolderName = tokens[0];
}
}
var newRow = sc.NewSeriesCatalogRow();
newRow.id = id++;
newRow.ParentID = folderID;
newRow.Provider = "SQLiteSeries";
newRow.ConnectionString = cs;
newRow.IsFolder = false;
newRow.TableName = var_name;
newRow.Units = row["units"].ToString();
newRow.Name = var_name;
newRow.Parameter = row["var_name"].ToString();
sc.AddSeriesCatalogRow(newRow);
}
db.Server.SaveTable(sc);
}
/// <summary>
/// Updates database if the original source file still exists and has
/// been modified.
/// </summary>
protected override void UpdateCore(DateTime t1, DateTime t2, bool minimal)
{
Logger.WriteLine("Checking Excel series " + Name + " (" + ID + ") for updates");
string dir = Path.GetDirectoryName(m_db.DataSource);
if (TextSeries.CanUpdateFromFile(ConnectionString, dir))
{
Logger.WriteLine("Update: File has changed");
SpreadsheetGearSeries g = SpreadsheetGearSeries.CreateFromConnectionString(ConnectionString, dir);
g.Read();
//m_db.Truncate(ID);
ConnectionString = g.ConnectionString;
ConnectionString = ConnectionStringUtility.MakeFileNameRelative(ConnectionString, m_db.DataSource);
m_db.SaveProperties(this);// LastWriteTime proabably changed
m_db.SaveTimeSeriesTable(ID, g, DatabaseSaveOptions.DeleteAllExisting);
}
}
private void CreateSeries(AccessDB mdb, string plantName, string dataType, int parentID)
{
string[] dTcfs = { "QBPF", "QOUT", "FRCSPILL", "BYPSPILL" };
string[] dTMW = { "AVGEN", "SYSGEN", "SYSSURP", "SYSDP" };
string[] dTksfd = { "ENDSTO", "ECC", "URC" };
string[] dTfeet = { "ENDELEV", "ECCFT", "URCFT" };
string units = "";
if (dTcfs.Contains(dataType) == true)
{
units = "cfs";
}
else
if (dTMW.Contains(dataType) == true)
{
units = "aMW";
}
else
if (dTksfd.Contains(dataType) == true)
{
units = "ksfd";
}
else
if (dTfeet.Contains(dataType) == true)
{
units = "feet";
}
else
{
Logger.WriteLine("Error: unsupported data type '" + dataType + "'");
}
string name = plantName + " " + dataType + " " + units;
string sheetName = plantName + " " + dataType;
string cs = "FileName=" + m_mdbFileName + ";PlantName=" + plantName + ";DataType=" + dataType; // connection string
cs = ConnectionStringUtility.MakeFileNameRelative(cs, m_databasePath);
seriesCatalog.AddSeriesCatalogRow(ID++, parentID, false, ID, "BpaHydsimSeriesAccess", name, sheetName, units,
TimeInterval.Monthly.ToString(), "Parameter", "", "BpaHydsimSeriesAccess", cs, "", "", true);
}
public void LoadTree()
{
m_db.SuspendTreeUpdates();
var colNames = xls.ColumnNames("Run0");
var sc = m_db.GetSeriesCatalog();
var root = m_db.AddFolder("URGWOM");
var sr = m_db.GetNewSeriesRow();
int id = sr.id;
foreach (var item in colNames)
{
Series s = new UrgwomSeries(excelFilename, "Run0", item);
s.ConnectionString = ConnectionStringUtility.MakeFileNameRelative(s.ConnectionString, m_db.DataSource);
s.Name = item;
s.Parameter = item;
sc.AddSeriesCatalogRow(s, id, root.ID, "");
id++;
}
m_db.Server.SaveTable(sc);
m_db.ResumeTreeUpdates();
}
/**********************************
*
* Create a tree file that is usable by pisces
*
* input : riverware rdf file
* output : comma seperated tree file.
*
*
* // example portion of input file.
* (this input file is using snapshots in riverware)
* -----------------------------------
* object_type: SnapShotObj
* object_name: Most Likely 2
* slot_name: Andrews Gage 12447390 at RM 3_5_Gage Outflow
* END_SLOT_PREAMBLE
* units: cfs
*
* // example output. There is no nesting of tree levels for now.
* RiverwareName,Description,RiverwareDataType,Level,Units
* Riverware Results,,,0,
* Yakima River at Parker PARW,Yakima River at Parker PARW,Gage Outflow,1,cfs
* Yakima River at Grandview,Yakima River at Grandview,Gage Inflow,1,cfs
* ...
*
**********************************/
public static void AddRiverWareFileToDatabase(string rdfFilename, PiscesFolder parent,
TimeSeriesDatabase db)
{
Reclamation.Core.TextFile tf = new Reclamation.Core.TextFile(rdfFilename);
#region notes
/*
* SnapShotStyle...
* -------------------------------------
* 2001-9-29 24:00
* 2001-9-30 24:00
* object_type: SnapShotObj
* object_name: Most Likely 2 ### scernario name
* slot_name: Andrews Gage 12447390 at RM 3_5_Gage Outflow # object_name slotName are combined.
* END_SLOT_PREAMBLE
* units: cfs
*
* Regular Style ...
* ---------------------------------------
* END_COLUMN
* END_SLOT
* object_type: StreamGage
* object_name: Yakima 202_0 at Easton EASW
* slot_name: Gage Outflow
* END_SLOT_PREAMBLE
* units: cfs
* scale: 1
*
*/
#endregion
int number_of_runs = LookupNumberOfRuns(tf);
PiscesFolder folder = parent;
if (number_of_runs == 1)
{
folder = db.AddFolder(parent, Path.GetFileNameWithoutExtension(rdfFilename));
}
int sz = tf.Length;
// object_type and object_name should occur on consecutive lines.
int index = tf.IndexOfBoth("object_name:", "slot_name:", 0);
var objectList = new List <string>(); //list to avoid duplicates in tree
Performance p1 = new Performance();
Performance p2 = new Performance();
p2.Pause();
int counter = 0;
db.SuspendTreeUpdates();
var sc = db.GetSeriesCatalog();
Dictionary <string, int> objTypeID = new Dictionary <string, int>();
Dictionary <string, int> objNameID = new Dictionary <string, int>();
while (index < sz && index > 0)
{
//slot_name: Andrews Gage 12447390 at RM 3_5_Gage Outflow
string slot_name = tf[index + 1].Substring(11); //Andrews Gage 12447390 at RM 3_5_Gage Outflow
string object_type = tf[index - 1].Substring(13);
string object_name = tf[index].Substring(13);
string units = tf[index + 3].Substring(6).Trim();
string tag = object_name + ":" + slot_name;
if (!objectList.Contains(tag))
{
int scenarioNumber = -1;
if (number_of_runs > 1)
{
scenarioNumber = 1;
}
RiverWareSeries s;
if (object_type == "SnapShotObj")
{
s = new RiverWareSeries(rdfFilename, "", slot_name, scenarioNumber, true, units);
}
else
{
s = new RiverWareSeries(rdfFilename, object_name, slot_name, scenarioNumber, false, units);
}
s.Units = units;
s.ConnectionString = ConnectionStringUtility.MakeFileNameRelative(s.ConnectionString, db.DataSource);
p2.Continue();
if (object_type.Contains("Reservoir"))
{
object_type = "Reservoir";
}
else if (object_type.Contains("Reach"))
{
object_type = "Reach";
}
else if (object_type.Contains("Diversion"))
{
object_type = "Diversion";
}
else if (object_type.Contains("Canal"))
{
object_type = "Canal";
}
int id = sc.NextID();
if (!sc.FolderExists(object_type, folder.ID))
{
objTypeID.Add(object_type, id);
sc.AddFolder(object_type, id, folder.ID);
id++;
}
if (!sc.FolderExists(object_name, objTypeID[object_type]))
{
objNameID.Add(object_name, id);
sc.AddFolder(object_name, id, objTypeID[object_type]);
id++;
}
sc.AddSeriesCatalogRow(s, id, objNameID[object_name], "");
objectList.Add(tag);
}
index = tf.IndexOfBoth("object_name:", "slot_name:", index + 2);
counter++;
}
p1.Report("total");
p2.Report("db.add()");
//398.7732813 seconds elapsed. total
//384.6792607 seconds elapsed. db.add()
// disable tree refresh (doubles perf)
// 255.9736646 seconds elapsed. total
// 241.7702669 seconds elapsed. db.add()
// implemented member ExternalDataSource
//34.8756696 seconds elapsed. total
//20.3753912 seconds elapsed. db.add()
var convention = Reclamation.TimeSeries.RiverWare.ImportRiverWare.ScenarioConvention.Default;
if (number_of_runs > 1) // Multiple runs.
{ // show dialog to allow water year naming or traces
var dlg = new Reclamation.TimeSeries.RiverWare.ImportRiverWare();
if (dlg.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
convention = dlg.NamingConvention;
}
// Add Scenarios.
var tblScen = db.GetScenarios();
for (int i = 0; i < number_of_runs; i++)
{
string name = "Run" + i;
if (convention == RiverWare.ImportRiverWare.ScenarioConvention.ByYear)
{
name = (dlg.FirstYear + i).ToString();
}
//string scenarioPath = ConnectionStringUtility.MakeFileNameRelative("FileName=" + item, DB.Filename);
tblScen.AddScenarioRow(name, true, "ScenarioNumber=" + (i + 1).ToString());
}
db.Server.SaveTable(tblScen);
}
db.Server.SaveTable(sc);
db.ResumeTreeUpdates();
db.RefreshFolder(parent);
}
private void AddBpaHydsimClick(object sender, EventArgs e)
{
var dlg = new Reclamation.TimeSeries.ScenarioPicker.ScenarioPicker();
dlg.Text = "Select Hydsim Output Files";
dlg.Dialog.DefaultExt = ".mdb";
dlg.Dialog.Filter = "BPA Hydsim Access (*.mdb, *.accdb)|*.mdb;*.accdb|All Files|*.*";
dlg.Dialog.Title = "Open BPA Hydsim Access File";
try
{
DB.SuspendTreeUpdates();
var result = dlg.ShowDialog();
if (result == DialogResult.OK)
{
if (dlg.ScenariosChecked && dlg.ScenarioFiles.Count > 0)
{
//create scenarios
ShowAsBusy("Reading Hydsim data");
var tblScen = DB.GetScenarios();
foreach (var item in dlg.ScenarioFiles)
{
string scenarioPath = ConnectionStringUtility.MakeFileNameRelative("FileName=" + item, DB.DataSource);
tblScen.AddScenarioRow(Path.GetFileNameWithoutExtension(item), true, scenarioPath, 0);
}
//add first file in the list to the tree
if (dlg.AddToTreeChecked)
{
var bpa = new BpaHydsim.BpaHydsimTreeAccess(dlg.ScenarioFiles[0], DB.DataSource, DB.NextSDI(), this.CurrentFolder.ID);
var tbl = bpa.CreateTree();
DB.Server.SaveTable(tbl);
}
DB.Server.SaveTable(tblScen);
DatabaseChanged();
}
else
if (dlg.AddToTreeChecked)
{
//add to tree, but not to scenairo list
ShowAsBusy("Reading Hydsim data");
for (int i = 0; i < dlg.ScenarioFiles.Count; i++)
{
string fn = dlg.ScenarioFiles[i].ToString();
var bpa = new BpaHydsim.BpaHydsimTreeAccess(fn, DB.DataSource, DB.NextSDI(), this.CurrentFolder.ID);
var tbl = bpa.CreateTree();
DB.Server.SaveTable(tbl);
}
DatabaseChanged();
}
}
}
finally
{
ShowAsReady("Done with Hydsim import");
DB.ResumeTreeUpdates();
}
}