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();
}
}
public PiscesSeriesLoader(TimeSeriesDatabase db, string program = "")
{
this.db = db;
seriesCatalog = db.GetSeriesCatalog();
seriesProperties = db.GetSeriesProperties();
m_program = program;
}
public TimeSeriesDatabaseDataSet.SeriesCatalogDataTable SeriesList()
{
var rval = new TimeSeriesDatabaseDataSet.SeriesCatalogDataTable();
var sql = "select * from seriescatalog where siteid='" + m_siteID + "'";
m_db.Server.FillTable(rval, sql);
return rval;
}
private static void LoadDailyUsbrCatalog(TimeSeriesDatabaseDataSet.SeriesCatalogDataTable sc,
TimeSeriesDatabaseDataSet.SeriesCatalogDataTable sc_vm, string program)
{
for (int i = 0; i < sc.Rows.Count; i++)
{
if (i == 55)
{
return;
}
var row = sc[i];
string[] path = { "water.usbr.gov", "pn", program, row.siteid, "daily" };
var folderID = sc_vm.GetOrCreateFolder(path);
TimeSeriesName tn = new TimeSeriesName(row.TableName);
var newRow = sc_vm.NewSeriesCatalogRow();
newRow.ItemArray = row.ItemArray;
newRow.id = sc_vm.NextID();
newRow.iconname = "";
newRow.Provider = "HydrometDailySeries";
newRow.ConnectionString = "server=PN;cbtt=" + tn.siteid + ";pcode=" + tn.pcode + ";";
newRow.TableName = "pnhydromet_" + row.TableName;
newRow.Units = HydrometInfoUtility.LookupDailyUnits(tn.pcode);
newRow.Parameter = HydrometInfoUtility.LookupDailyParameterName(tn.pcode);
newRow.ParentID = folderID;
sc_vm.Rows.Add(newRow);
}
}
private static int AddPiscesSeries(string name, int scIndex,
TimeSeriesDatabaseDataSet.SeriesCatalogDataTable sc, Forecast f, int parentID, string connectionString)
{
string folderName = f.IssueDate.ToString("yyyy-MM-dd");
int folderID = ++scIndex;
sc.AddFolder(folderName, folderID, parentID);
int memberCount = f.Ensemble.GetLength(0);
for (int i = 0; i < memberCount; i++)
{
var scrow = sc.NewSeriesCatalogRow();
scrow.id = ++scIndex;
scrow.Provider = "EnsembleSeries";
scrow.ConnectionString = connectionString.Replace("{member_index}", (i + 1).ToString());
scrow.ParentID = folderID;
scrow.siteid = name;
scrow.Name = name + " member" + (i + 1);
scrow.TimeInterval = Reclamation.TimeSeries.TimeInterval.Hourly.ToString();
scrow.Units = "cfs";
scrow.Parameter = "flow";
sc.Rows.Add(scrow);
}
return(scIndex);
}
public void SiteInfoDesignTest()
{
var fn = FileUtility.GetTempFileName(".pdb");
SQLiteServer svr = new SQLiteServer(fn);
var db = new TimeSeriesDatabase(svr, false);
var siteCatalog = db.GetSiteCatalog();
siteCatalog.AddsitecatalogRow("BOII", "Boise station", "ID");
svr.SaveTable(siteCatalog);
var sc = db.GetSeriesCatalog();
var s = new Series();
s.SiteID = "BOII";
sc.AddSeriesCatalogRow(s, sc.NextID(), db.GetRootObjects()[0].ID);
sc.Save();
var si = db.SiteInfo("BOII");
bool idaho = si.state == "ID"; //idaho
var timezone = si.timezone;
TimeSeriesDatabaseDataSet.SeriesCatalogDataTable d = si.SeriesList();
TimeSeriesDatabaseDataSet.SeriesCatalogRow row = d[0];
row.Parameter = "Asce ET #5";
d.Save();
Console.WriteLine(si.SeriesList()[0].Parameter);
//var goodStats = (si.Parameters()[0].statistic == "Avg");
}
public TimeSeriesDatabaseDataSet.SeriesCatalogDataTable SeriesList()
{
var rval = new TimeSeriesDatabaseDataSet.SeriesCatalogDataTable();
var sql = "select * from seriescatalog where siteid='" + m_siteID + "'";
m_db.Server.FillTable(rval, sql);
return(rval);
}
private void AddRows(TimeSeriesDatabaseDataSet.SeriesCatalogDataTable tbl)
{
foreach (var item in tbl)
{
var r = m_seriesCatalog.NewSeriesCatalogRow();
r.ItemArray = item.ItemArray;
m_seriesCatalog.AddSeriesCatalogRow(r);
}
}
private void Setup()
{
m_sites = m_db.GetSiteCatalog();
this.comboBox1.DataSource = m_sites;
this.comboBox1.DisplayMember = "siteid";
this.comboBox1.ValueMember = "siteid";
comboBox1.SelectedValue = "boii";
m_seriesCatalog = new TimeSeriesDatabaseDataSet.SeriesCatalogDataTable();
}
public static void CreatePiscesTree(string fileName, PiscesFolder root,
TimeSeriesDatabase db)
{
mi = new Model();
s_db = db;
sdi = db.NextSDI();
studyFolderID = sdi;
int parentID = root.ID;
seriesCatalog = new TimeSeriesDatabaseDataSet.SeriesCatalogDataTable();
if (File.Exists(fileName))
{
XYFileReader.Read(mi, fileName);
m_xyFilename = Path.GetFileNameWithoutExtension(fileName);
}
else
{
throw new FileNotFoundException("Modsim xy file is not found " + fileName);
}
string mdbJetName = Path.Combine(Path.GetDirectoryName(fileName), m_xyFilename + "OUTPUT.mdb");
string mdbAceName = Path.Combine(Path.GetDirectoryName(fileName), m_xyFilename + "OUTPUT.accdb");
if (File.Exists(mdbAceName))
{
m_databaseName = mdbAceName;
}
else
{
m_databaseName = mdbJetName;
}
if (File.Exists(m_databaseName))
{
m_db = new AccessDB(m_databaseName);
dir = Path.GetDirectoryName(Path.GetFullPath(m_databaseName));
//AddNewRow(sdi,parentID,true, "", mi.name, "");
AddNewRow(sdi, parentID, true, "", Path.GetFileNameWithoutExtension(fileName), "");
ReservoirsTree();
DemandsTree();
RiverLinksTree();
TotalsTree();
}
else
{
throw new FileNotFoundException(" MODSIM output not found " + m_databaseName);
}
//DataTableOutput.Write(seriesCatalog, @"C:\temp\a.csv",false);
db.Server.SaveTable(seriesCatalog);
db.RefreshFolder(root);
}
private static void FixFolderStructure(TimeSeriesDatabase db, TimeSeriesDatabaseDataSet.SeriesCatalogDataTable sc)
{
var siteCatalog = db.GetSiteCatalog();
for (int i = 0; i < sc.Count; i++)
{
var row = sc[i];
if (row.IsFolder || row.TimeInterval != TimeInterval.Irregular.ToString() ||
row.Provider != "Series")
{
continue;
}
TimeSeriesName tn = new TimeSeriesName(row.TableName);
var s = db.GetSeries(row.id);
var program = EstimateProgramName(siteCatalog, s);
if (program == "" || (program != "hydromet" && program != "agrimet"))
{
Console.WriteLine("Error: will skip, no program defined in series or type in sitecatalog");
continue;
}
if (!IsQualityParameter(tn.pcode))
{
continue;
}
var myPath = sc.GetPath(row.id);
var myPathJoin = String.Join("/", myPath);
string[] path = { "timeseries", program, tn.siteid, "instant" };
if (IsQualityParameter(tn.pcode))
{
path = new string[] { "timeseries", program, tn.siteid, "quality" }
}
;
var expectedPath = String.Join("/", path);
if (myPathJoin != expectedPath)
{
Console.WriteLine(tn.pcode + ": " + myPathJoin + " --> " + expectedPath);
var id = sc.GetOrCreateFolder(path);
row.ParentID = id;
}
}
db.Server.SaveTable(sc);
}
private void Setup()
{
m_sites = m_db.GetSiteCatalog();
this.comboBox1.DataSource = m_sites;
this.comboBox1.DisplayMember = "siteid";
this.comboBox1.ValueMember = "siteid";
comboBox1.SelectedValue = "boii";
m_seriesCatalog = new TimeSeriesDatabaseDataSet.SeriesCatalogDataTable();
properties.Columns.Add("name");
properties.Columns.Add("value");
//properties.Rows.Add("program", "hydromet");
//properties.Rows.Add("basin", "yakima");
dataGridViewProperties.DataSource = properties;
}
private static void LoadUpperSnakeHydromet(TimeSeriesDatabaseDataSet.SeriesCatalogDataTable sc_vm)
{
// load hydromet/upper snake
var lines = upperSnakeDaily.Split('\n');
foreach (var item in lines)
{
var tokens = item.Trim().Split(' ', '\t');
var cbtt = tokens[0].Trim().ToLower();
var pcode = tokens[1].Trim().ToLower();
string[] path = { "water.usbr.gov", "pn", "hydromet", cbtt, "daily" };
var folderID = sc_vm.GetOrCreateFolder(path);
HydrometDailySeries s = new HydrometDailySeries(cbtt, pcode);
s.SiteID = cbtt;
s.Parameter = HydrometInfoUtility.LookupDailyParameterName(pcode);
sc_vm.AddSeriesCatalogRow(s, sc_vm.NextID(), folderID, "pnhydromet_" + s.Table.TableName);
}
}
private static void FixSiteID(BasicDBServer svr, TimeSeriesDatabaseDataSet.SeriesCatalogDataTable sc)
{
for (int i = 0; i < sc.Count; i++)
{
var row = sc[i];
if (row.IsFolder || row.TimeInterval != TimeInterval.Irregular.ToString() ||
row.Provider != "Series")
{
continue;
}
TimeSeriesName tn = new TimeSeriesName(row.TableName);
if (row.siteid == "" && tn.siteid != "")
{
Console.WriteLine("Site ID is blank. chaning to " + " " + tn.siteid);
row.siteid = tn.siteid;
}
}
svr.SaveTable(sc);
}
public TimeSeriesDatabaseDataSet.SeriesCatalogDataTable CreateTree()
{
seriesCatalog = new TimeSeriesDatabaseDataSet.SeriesCatalogDataTable();
int bpaRoot = AddFolder(ID++, m_parentID, Path.GetFileNameWithoutExtension(m_mdbFileName));
// read access file
mdb = new AccessDB(m_mdbFileName);
// get table of distinct Plant Names and Data Types to create Tree
string sql = "SELECT DISTINCT Working_Set.PlantName, Working_Set.DataType FROM Working_Set";
DataTable tbl = mdb.Table("Working_Set", sql);
// create folder for each Plant Name and a row for each Data Type
DataTable tblPlantNames = DataTableUtility.SelectDistinct(tbl, "PlantName");
for (int i = 0; i < tblPlantNames.Rows.Count; i++)
{
string plantName = tblPlantNames.Rows[i][0].ToString().Trim();
int siteID = AddFolder(ID++, bpaRoot, plantName);
string[] dataTypes = DataTableUtility.Strings(tbl, "[PlantName]='" + plantName + "'", "DataType");
for (int j = 0; j < dataTypes.Count(); j++)
{
string dataType = dataTypes[j].Trim();
string[] dTksfd = { "ENDSTO", "ECC", "URC" };
if (dTksfd.Contains(dataType) == true)
{
CreateSeries(mdb, plantName, dataType, siteID);
CreateSeriesAF(mdb, plantName, dataType, siteID);
}
else
{
CreateSeries(mdb, plantName, dataType, siteID);
}
}
}
return(seriesCatalog);
}
/// <summary>
/// Creates table that can be
/// appended to the SeriesCatalog in Pisces
/// </summary>
/// <returns></returns>
public static TimeSeriesDatabaseDataSet.SeriesCatalogDataTable PiscesSeriesCatalog(int model_id,
string m_table, // model table i.e. m_month, m_day,...
DateTime t, // consider model runs after this date
int nextPiscesID, // next avaliable id in Pisces
int parentID) // container for this model
{ // get unique list of site_datatype_id -- based on these parameters
var rval = new TimeSeriesDatabaseDataSet.SeriesCatalogDataTable();
DataTable sitesAndParameters = Hdb.Instance.ModelParameterList(t, model_id, m_table);
DataTable siteList = DataTableUtility.SelectDistinct(sitesAndParameters, "Site_ID");
int model_run_id = -1;
string model_run_name = "";
string run_date = "";
Hdb.Instance.FirstModelRunInfo(t, model_id, out model_run_id, out model_run_name, out run_date);
//string scenarioName = "";// "(" + model_run_id + ")";
for (int i = 0; i < siteList.Rows.Count; i++)
{
int site_id = Convert.ToInt32(siteList.Rows[i]["site_id"]);
DataRow[] rows = sitesAndParameters.Select("Site_ID=" + site_id);
int siteRowID = nextPiscesID++;
rval.AddSeriesCatalogRow(siteRowID, parentID, true, 1, "HdbModel",
rows[0]["site_name"].ToString(), "", "", "", "", "", "", "", "", "", true);
for (int j = 0; j < rows.Length; j++)
{
string cs = HdbModelSeries.BuildConnectionString(m_table, model_run_id.ToString(), model_run_name, run_date, rows[j]["site_datatype_id"].ToString());
rval.AddSeriesCatalogRow(nextPiscesID++, siteRowID, false, j,
"HdbModel", rows[j]["datatype_common_name"].ToString(),
rows[j]["site_name"].ToString(),
rows[j]["unit_common_name"].ToString(),
IntervalString(m_table),
rows[j]["datatype_common_name"].ToString(),
"", "HdbModelSeries", cs, "", "", true);
}
}
return(rval);
}
/// <summary>
/// Add the following
///
/// cbtt_ch|gh = ""
/// cbtt_hh|hj = ConstantShift(...)
/// cbtt_(q|qc) = GenericWeir(....)
///
/// </summary>
/// <param name="sc"></param>
/// <param name="prop"></param>
/// <param name="cbtt"></param>
/// <param name="pc"></param>
/// <param name="width_factor"></param>
/// <param name="exponent"></param>
/// <param name="offset"></param>
/// <param name="shift"></param>
private static void AddGenericWeir(TimeSeriesDatabaseDataSet.SeriesCatalogDataTable sc,
TimeSeriesDatabaseDataSet.seriespropertiesDataTable prop,
string cbtt, string pc,
string width_factor, string exponent, string offset, string shift)
{
string shiftCode = "";
string flowCode = "";
if (pc == "ch")
{
shiftCode = "hh";
flowCode = "qc";
}
else
{
shiftCode = "hj";
flowCode = "q";
}
int parentID = 0; // TO DO.
// afci_qc = GenericWeir(afci_ch,width_factor,exponent) // smart to look for shift... in afci_ch.Properties.shift
// afci_hh = ConstantShift(afci_ch); // lookup shift from properties...
// afci_ch.shift=-0.41
var id = sc.AddInstantRow(cbtt, parentID, "feet", pc, "");
prop.Set("shift", shift, id); // save current shift in properties.
prop.Set("program", "hydromet", id);
sc.AddInstantRow(cbtt, parentID, "feet", shiftCode, "ConstantShift(%site%_" + pc + ")");
prop.Set("program", "hydromet", id);
string expression = "GenericWeir(%site%_" + pc + "," + offset + "," + width_factor + "," + exponent + ")";
id = sc.AddInstantRow(cbtt, parentID, "cfs", flowCode, expression);
prop.Set("program", "hydromet", id);
}
private void Setup()
{
m_sites = m_db.GetSiteCatalog();
this.comboBox1.DataSource = m_sites;
this.comboBox1.DisplayMember = "siteid";
this.comboBox1.ValueMember = "siteid";
comboBox1.SelectedValue = "boii";
m_seriesCatalog = new TimeSeriesDatabaseDataSet.SeriesCatalogDataTable();
}
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();
}
}
/// <summary>
/// For each instant Series in the database create an appropriate
/// Dailycalculation.
/// </summary>
public void AddDailyCalculations(DataTable pcodeLookup, bool dryRun, string[] sites)
{
var codes = new List <string>();
for (int i = 0; i < pcodeLookup.Rows.Count; i++)
{
var pc = pcodeLookup.Rows[i]["instantpcode"].ToString();
codes.Add(pc.ToLower());
}
var filter = "select * from seriescatalog a join sitecatalog b on a.siteid=b.siteid "
+ "where timeinterval= 'Irregular' and b.type <> 'agrimet' ";
filter += "and parameter in ( '" + String.Join("','", codes.ToArray()) + "')";
var q = new TimeSeriesDatabaseDataSet.SeriesCatalogDataTable();
m_db.Server.FillTable(q, filter);
newCalcCount = 0;
changeEqCount = 0;
//foreach (var r in q)
for (int j = 0; j < q.Rows.Count; j++)
{
var r = q[j];
try
{
if (sites.Length > 0 && Array.IndexOf(sites, r.siteid) == -1)
{
continue;
}
if (!HasCurrentData(r.TableName)) // don't add equations for 'old' data
{
Console.WriteLine("no current data. skipping .." + r.TableName);
}
else
{
var calcs = pcodeLookup.Select("InstantPcode ='" + r.Parameter + "'");
for (int i = 0; i < calcs.Length; i++)
{
var dailyPcode = calcs[i]["DailyPcode"].ToString().ToLower();
var siteFilter = calcs[i]["siteFilter"].ToString();
var expression = calcs[i]["Equation"].ToString();
expression = expression.Replace("%site%", r.siteid);
var name = r.siteid + "_" + dailyPcode;
TimeSeriesName tn = new TimeSeriesName(name, "daily");
if (m_sites.Select("siteid='" + r.siteid + "'").Length == 0)
{
Console.WriteLine("Warning: skipping site not cataloged: " + r.siteid);
continue;
}
// remove property--> program:hydromet (prevents data flow from pnhyd0)
if (!dryRun)
{
RemoveProgramProperty(tn);
}
if (!FilterAllows(r.siteid, siteFilter)) // config spreasheet limits i.e. responsibility='IDWR'
{
continue;
}
if (m_db.TableNameInUse(tn.GetTableName()))
{ // already defined in some manner.
ConvertToDailyEquation(tn, expression, dryRun);
}
else
{
CalculationSeries s = CreateCalcSeries(r, name, tn, expression);
newCalcCount++;
if (!dryRun)
{
AddCalcSeries(s);
}
}
// check if this equation matches Hydromet Legacy.
if (!dryRun)
{
CompareWithVMS(tn);
}
}
}
}
catch (Exception exc)
{
sw.WriteLine("Error:" + exc.Message);
}
if (j % 100 == 0)
{
Console.WriteLine(j + " of " + q.Count);
}
}
sw.WriteLine("New equations: " + newCalcCount);
sw.WriteLine("modified to Equations :" + this.changeEqCount);
sw.Close();
}
