public List <PiscesObject> GetRootObjects()
{
var tbl = new TimeSeriesDatabaseDataSet.SeriesCatalogDataTable();
string sql = "select * from seriescatalog where id = parentid ";
m_server.FillTable(tbl, sql);
if (tbl.Rows.Count == 0)
{
Reclamation.Core.Logger.WriteLine("Tree Requires at least one root Node");
Logger.WriteLine("AutoCreation of New Root Folder");
CreateRootFolder();
m_server.FillTable(tbl, sql);
}
List <PiscesObject> rval = new List <PiscesObject>();
for (int i = 0; i < tbl.Rows.Count; i++)
{
rval.Add(Factory.CreateObject(tbl[i]));
}
if (rval.Count > 0)
{
m_root = rval[0];
}
return(rval);
}
private TimeSeriesDatabaseDataSet.SeriesCatalogDataTable GetChildrenRows(PiscesObject parent)
{
if (Filter.Trim() == "")
{
string sql = "select * from seriescatalog where parentid = " + parent.ID + " and id <> " + parent.ID + " order by sortorder";
var tbl = new TimeSeriesDatabaseDataSet.SeriesCatalogDataTable();
m_server.FillTable(tbl, sql);
return(tbl);
}
else
{// filtered catalog
var sc = GetFilteredCatalog();
var tbl = new TimeSeriesDatabaseDataSet.SeriesCatalogDataTable();
var rows = sc.Select("parentid=" + parent.ID + " and id <> parentid", "sortorder");
foreach (var item in rows)
{
var newRow = tbl.NewSeriesCatalogRow();
newRow.ItemArray = item.ItemArray;
tbl.Rows.Add(newRow);
}
tbl.AcceptChanges();
return(tbl);
}
}
protected PiscesObject()
{
catalog = new TimeSeriesDatabaseDataSet.SeriesCatalogDataTable();
row = catalog.NewSeriesCatalogRow();
row.id = -1;
// Catalog.Rows.Add(row);
// Console.WriteLine("PiscesObject()");
}
public TimeSeriesDatabaseDelete(TimeSeriesDatabase db, PiscesObject o)
{
this.m_db = db;
sdi = o.ID;
catalog = m_db.GetFilteredCatalog();
m_seriesProperties = m_db.GetSeriesProperties();
//catalog.PrimaryKey = new DataColumn[] { catalog.Columns["id"]};
}
/// <summary>
/// Get a filtered view of the SeriesCatalog.
/// Include all parent folders with children that match the filter.
/// </summary>
/// <param name="filter"></param>
/// <returns></returns>
private TimeSeriesDatabaseDataSet.SeriesCatalogDataTable GetFilteredCatalog( )
{
if (m_prevFilter == Filter && m_SeriesCatalog != null)
{
return(m_SeriesCatalog);
}
m_SeriesCatalog = new TimeSeriesDatabaseDataSet.SeriesCatalogDataTable();
m_server.FillTable(m_SeriesCatalog); // get all rows.
var newColumn = new DataColumn("keep", typeof(System.Boolean));
newColumn.DefaultValue = false;
m_SeriesCatalog.Columns.Add(newColumn);
List <int> parentsIncluded = new List <int>();
var filters = Filter.Split(',');
var sql = "";
for (int i = 0; i < filters.Length; i++)
{
if (i > 0)
{
sql += " or ";
}
sql += "name like '%" + BasicDBServer.SafeSqlLikeClauseLiteral(filters[i].Trim()) + "%'";
}
var rows = m_SeriesCatalog.Select(sql);
foreach (var item in rows)
{
item["keep"] = true;
int parentID = Convert.ToInt32(item["parentid"]);
if (!parentsIncluded.Contains(parentID))
{
MarkParentsAsPartOfFilter(parentID, m_SeriesCatalog);
parentsIncluded.Add(parentID);
}
}
var rowsToDelete = m_SeriesCatalog.Select("keep = false");
for (int i = 0; i < rowsToDelete.Length; i++)
{
m_SeriesCatalog.Rows.Remove(rowsToDelete[i]);
}
m_SeriesCatalog.Columns.Remove("keep");
m_SeriesCatalog.AcceptChanges();
m_prevFilter = Filter;
return(m_SeriesCatalog);
}
private void MarkParentsAsPartOfFilter(int id, TimeSeriesDatabaseDataSet.SeriesCatalogDataTable tbl)
{
var row = tbl.FindByid(id);
row["keep"] = true;
if (row.ParentID != row.id) // skip root level folders where parentid=id
{
MarkParentsAsPartOfFilter(row.ParentID, tbl);
}
}
protected PiscesObject(TimeSeriesDatabaseDataSet.SeriesCatalogRow row)
{
if (row.RowState == System.Data.DataRowState.Detached)
{
catalog = new TimeSeriesDatabaseDataSet.SeriesCatalogDataTable();
}
else
{
catalog = (TimeSeriesDatabaseDataSet.SeriesCatalogDataTable)row.Table;//
}
this.row = row;
}
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 void MarkParentsAsPartOfFilter(int id, TimeSeriesDatabaseDataSet.SeriesCatalogDataTable tbl)
{
var row = tbl.FindByid(id);
if (row == null)
{
Logger.WriteLine("Row does not exist. It may have been deleted.");
return;
}
row["keep"] = true;
if (row.ParentID != row.id) // skip root level folders where parentid=id
{
MarkParentsAsPartOfFilter(row.ParentID, tbl);
}
}
public TimeSeriesDatabaseUtility(TimeSeriesDatabase db)
{
m_db = db;
m_seriesCatalog = m_db.GetSeriesCatalog();
m_siteCatalog = m_db.GetSiteCatalog();
}
/// <summary>
/// Lookup Series based on the internal database TableName
/// </summary>
/// <param name="tableName"></param>
/// <param name="defaultInterval">default tableName prefix interval</param>
/// <param name="prefix">table prefix usually 'instant' , 'daily' or monthly </param>
/// <param name="createMissing"></param>
/// <returns></returns>
public Series GetSeriesFromTableName(string tableName, string prefix="",bool createMissing=false)
{
Logger.WriteLine("GetSeriesFromTableName(" + tableName + ", '" + prefix+"')");
if (prefix.ToLower() == "irregular")
prefix = "instant"; // inconsistency..
if (Regex.IsMatch(tableName, "^[0-9]")) // table name starting with number is not allowed
{
tableName = "_" + tableName; // append with underscore
}
TimeSeriesName tn = new TimeSeriesName(tableName);
if ( tn.Valid && !tn.HasInterval && prefix != "")
{
tableName = prefix + "_" + tableName; // add prefix before searching
}
string sql = "Select * from seriescatalog where tablename = '" + tableName.ToLower() + "' and isfolder = 0 ";
var tbl = new TimeSeriesDatabaseDataSet.SeriesCatalogDataTable();
m_server.FillTable(tbl, sql);
if (tbl.Rows.Count == 0)
{
if (createMissing)
{
Logger.WriteLine("Creating series "+tableName);
if (m_missingFolder == null )
m_missingFolder = GetOrCreateFolder("missing");
Series ns = new Series();
ns.Name = tableName;
ns.Table.TableName = tableName;
ns.Parameter = tn.pcode;
var id = AddSeries(ns,m_missingFolder);
return factory.GetSeries(id);
}
else
{
Logger.WriteLine("Error: This table name was not found '" + tableName + "'");
return null;
}
}
var s =factory.GetSeries(tbl[0]);
Logger.WriteLine(s.Name + ": " + s.Table.TableName);
return s;
}
//public Series GetSeries(string siteID, string parameterCode, TimeInterval timeInterval)
//{
// return GetSeriesFromTableName(siteID + "_" + parameterCode,timeInterval.ToString().ToLower());
//}
/// <summary>
/// Lookup Series based on the display Name
/// </summary>
/// <param name="name"></param>
/// <returns></returns>
public Series GetSeriesFromName(string name, string timeInterval="", bool exactMatch=false)
{
//string sql = " select * from seriesCatalog where name = '" + name + "'";
//string sql = "Select * from SeriesCatalog where name = '"+name +"' union select * from SeriesCatalog where name like 'one:%'";
string sql = "Select * from seriescatalog where name = '" + name + "' and isfolder = 0 ";
if (!exactMatch)
{
sql += " UNION "
+ " select * from seriescatalog where name like '" + name + ":%' and isfolder = 0";
}
if (timeInterval != "")
{
sql = sql.Replace("where name", "where lower(timeinterval) ='"+timeInterval.ToLower()+"' and name");
}
var tbl = new TimeSeriesDatabaseDataSet.SeriesCatalogDataTable();
m_server.FillTable(tbl, sql);
if (tbl.Rows.Count == 0)
{
Logger.WriteLine("Error: GetSeriesFromName: This name was not found '" + name + "'");
return null;
}
if (tbl.Rows.Count > 1)
{
Logger.WriteLine("Error: GetSeriesFromName Series '" + name + "' was found " + tbl.Rows.Count + " times");
return null;
}
return factory.GetSeries(tbl[0]);
}
/// <summary>
/// Returns a filtered list from the Series Catalog
/// </summary>
/// <param name="filter"></param>
/// <param name="propertyFilter"></param>
/// <returns></returns>
public TimeSeriesDatabaseDataSet.SeriesCatalogDataTable GetSeriesCatalog(string filter,string propertyFilter="",string sort="order by siteid")
{
var tbl = new TimeSeriesDatabaseDataSet.SeriesCatalogDataTable();
string sql = "select * from seriescatalog where "+filter;
sql += GetSeriesPropertySQL(propertyFilter);
sql += " "+sort;
m_server.FillTable(tbl,sql );
return tbl;
}
public TimeSeriesDatabaseDataSet.SeriesCatalogDataTable GetSeriesCatalog()
{
var tbl = new TimeSeriesDatabaseDataSet.SeriesCatalogDataTable();
tbl.Server = m_server;
m_server.FillTable(tbl);
return tbl;
}
/// <summary>
/// Creates a new SeriesRow and Assigns SiteDataTypeID
/// </summary>
/// <returns></returns>
public SeriesCatalogRow GetNewSeriesRow(bool assignID = true)
{
var tbl = new TimeSeriesDatabaseDataSet.SeriesCatalogDataTable();
var rval = tbl.NewSeriesCatalogRow();
rval.id = NextSDI();
rval.enabled = 1;
tbl.Rows.Add(rval);
return rval;
}
private void CreateRootFolder()
{
TimeSeriesDatabaseDataSet.SeriesCatalogDataTable tbl= new TimeSeriesDatabaseDataSet.SeriesCatalogDataTable();
m_server.FillTable(tbl, "Select * from seriescatalog where id = parentid ");
//DataTable tbl = m_server.Table("seriescatalog", "Select * from seriesCatalog where id = parentid ");
if (tbl.Rows.Count == 0)
{
Logger.WriteLine("Creating Root Folder in database '"+m_server.Name+"'");
int id = NextSDI();
Logger.WriteLine("id = "+id);
// DataRow row = tbl.NewRow();
// row["id"] = id;
// row["ParentID"] = id;
// row["IsFolder"] = true;
//// row["sortorder"] = 0;
// row["Name"] = m_server.Name;
tbl.AddFolder(m_server.Name,id, id);
// tbl.Rows.Add(row);
Logger.WriteLine("before save: tbl.rows[0][id]= "+tbl.Rows[0]["id"].ToString());
m_server.SaveTable(tbl);
Logger.WriteLine("Root folder created");
}
}
/// <summary>
/// Gets a Series Catalog using SQL.
/// </summary>
/// <param name="filter">Select * from seriesCatalog where [filter] </param>
/// <returns></returns>
internal SeriesCatalogRow GetSeriesRow(string filter)
{
string sql = "Select * from seriescatalog where " + filter;
var tbl = new TimeSeriesDatabaseDataSet.SeriesCatalogDataTable();
m_server.FillTable(tbl, sql);
if (tbl.Rows.Count == 0)
return null;
return tbl[0];
}
internal SeriesCatalogRow GetSeriesRow(int sdi)
{
string sql = "Select * from seriescatalog where id = " + sdi;
var tbl = new TimeSeriesDatabaseDataSet.SeriesCatalogDataTable();
m_server.FillTable(tbl, sql);
if (tbl.Rows.Count == 0)
throw new InvalidOperationException("this SiteDataTypeID does not exist "+sdi);
return tbl[0];
}
/// <summary>
/// Get a filtered view of the SeriesCatalog.
/// Include all parent folders with children that match the filter.
/// </summary>
/// <param name="filter"></param>
/// <returns></returns>
internal TimeSeriesDatabaseDataSet.SeriesCatalogDataTable GetFilteredCatalog( )
{
if (m_prevFilter == Filter && m_SeriesCatalog != null)
return m_SeriesCatalog;
m_SeriesCatalog = new TimeSeriesDatabaseDataSet.SeriesCatalogDataTable();
m_server.FillTable(m_SeriesCatalog); // get all rows.
if( Filter.Trim() == "")
{
m_prevFilter = Filter;
return m_SeriesCatalog;
}
var newColumn = new DataColumn("keep", typeof(System.Boolean));
newColumn.DefaultValue = false;
m_SeriesCatalog.Columns.Add(newColumn);
List<int> parentsIncluded = new List<int>();
var sql = "";
// Search with an AND filter
if (Filter.Contains('&'))
{
var andFilters = Filter.Split('&');
for (int i = 0; i < andFilters.Length; i++)
{
if (i > 0)
sql += " and ";
sql += "name like '%" + BasicDBServer.SafeSqlLikeClauseLiteral(andFilters[i].Trim()) + "%'";
}
}
else
{
// Search with an OR filter
var filters = Filter.Split(',');
for (int i = 0; i < filters.Length; i++)
{
if (i > 0)
sql += " or ";
sql += "name like '%" + BasicDBServer.SafeSqlLikeClauseLiteral(filters[i].Trim()) + "%'";
}
}
var rows = m_SeriesCatalog.Select(sql);
foreach (var item in rows)
{
item["keep"] = true;
int parentID = Convert.ToInt32(item["parentid"]);
if (!parentsIncluded.Contains(parentID))
{
MarkParentsAsPartOfFilter(parentID, m_SeriesCatalog);
parentsIncluded.Add(parentID);
}
}
var rowsToDelete = m_SeriesCatalog.Select("keep = false");
for (int i = 0; i < rowsToDelete.Length; i++)
{
m_SeriesCatalog.Rows.Remove(rowsToDelete[i]);
}
m_SeriesCatalog.Columns.Remove("keep");
m_SeriesCatalog.AcceptChanges();
m_prevFilter = Filter;
return m_SeriesCatalog;
}
private TimeSeriesDatabaseDataSet.SeriesCatalogDataTable GetChildrenRows(PiscesObject parent)
{
if (Filter.Trim() == "")
{
string sql = "select * from seriescatalog where parentid = " + parent.ID + " and id <> " + parent.ID + " order by sortorder";
var tbl = new TimeSeriesDatabaseDataSet.SeriesCatalogDataTable();
m_server.FillTable(tbl, sql);
return tbl;
}
else
{// filtered catalog
var sc = GetFilteredCatalog();
var tbl = new TimeSeriesDatabaseDataSet.SeriesCatalogDataTable();
var rows = sc.Select("parentid=" + parent.ID+ " and id <> parentid","sortorder");
foreach (var item in rows)
{
var newRow = tbl.NewSeriesCatalogRow();
newRow.ItemArray = item.ItemArray;
tbl.Rows.Add(newRow);
}
tbl.AcceptChanges();
return tbl;
}
}
public List<PiscesObject> GetRootObjects()
{
var tbl = new TimeSeriesDatabaseDataSet.SeriesCatalogDataTable();
string sql = "select * from seriescatalog where id = parentid ";
m_server.FillTable(tbl, sql);
if (tbl.Rows.Count == 0)
{
Reclamation.Core.Logger.WriteLine("Tree Requires at least one root Node");
Logger.WriteLine("AutoCreation of New Root Folder");
CreateRootFolder();
m_server.FillTable(tbl, sql);
}
List<PiscesObject> rval = new List<PiscesObject>();
for (int i = 0; i < tbl.Rows.Count; i++)
{
rval.Add(Factory.CreateObject(tbl[i]));
}
if (rval.Count > 0)
m_root = rval[0];
return rval;
}
