/// <summary>
/// check if all the links exist in Modsim output mdb file
/// </summary>
/// <param name="links"></param>
/// <returns></returns>
private static bool LinksExist(string[] links)
{
string sql = "Select LName from LinksInfo where "
+ " LName in ('" + String.Join("','", links) + "')";
DataTable tbl = m_db.Table("LinksInfo", sql);
bool rval = tbl.Rows.Count == links.Length;
if (!rval)
{
Logger.WriteLine("WARNING: some Modsim Link Names do not exist in the output");
Logger.WriteLine("-- from output -- " + tbl.Rows.Count + " links");
for (int i = 0; i < tbl.Rows.Count; i++)
{
Logger.WriteLine(tbl.Rows[i][0].ToString());
}
Logger.WriteLine("-- from ModsimTotals.txt --- " + links.Length + " links ");
for (int i = 0; i < links.Length; i++)
{
Logger.WriteLine(links[i]);
}
}
return(rval);
}
private string[] GetDistinct(string colName)
{
string sql = "Select Distinct [" + colName + "] from [" + TableName + "]";
DataTable tbl = db.Table(TableName, sql);
return(DataTableUtility.Strings(tbl, "", colName));
}
/// <summary>
/// returns MODSIM Link output
/// </summary>
private void LinkOutput(string modsimName, string columnName, DateTime t1, DateTime t2)
{
string sql = " SELECT TimeSteps.TsDate, LinksOutput." + columnName //+ SQLMultiplyByScale(columnName)
+ " FROM (LinksOutput INNER JOIN TimeSteps ON LinksOutput.TSIndex = TimeSteps.TSIndex) "
+ " INNER JOIN LinksInfo ON LinksOutput.LNumber = LinksInfo.LNumber "
+ " where LinksInfo.LName = '" + modsimName + "'";
if (!FullPeriod(t1, t2)) // performance improvement when using full period of record
{
sql += " AND " + GetDateClause(t1, t2);
}
if (modsimName.IndexOf(",") >= 0)
{// sum link group together.
string where = "( '" + modsimName.Replace(",", "','") + "') ";
sql = "SELECT TimeSteps.TSDate, Sum(LinksOutput." + columnName + " ) AS " + columnName
+ " FROM TimeSteps INNER JOIN (LinksOutput INNER JOIN LinksInfo ON LinksOutput.LNumber = LinksInfo.LNumber) "
+ " ON TimeSteps.TSIndex = LinksOutput.TSIndex "
+ " WHERE " + GetDateClause(t1, t2)
+ " AND (LinksInfo.LName in " + where + " ) "
+ " GROUP BY TimeSteps.TSDate ";
}
Performance perf = new Performance();
DataTable tbl = AccessDB.Table(m_databaseName, "LinksOutput", sql);
perf.Report();
InitTimeSeries(tbl, Units, TimeInterval, true);
}
/// <summary>
/// <summary>
/// returns MODSIM Demand output
/// </summary>
private void DemandOutput(string modsimName, string columnName, DateTime t1, DateTime t2)
{
if (columnName.Contains("Hydro_State"))
{
columnName = "Hydro_State";
}
string sql = "SELECT TimeSteps.TsDate, DEMOutput." + columnName //+ SQLMultiplyByScale(columnName)
+ " FROM (TimeSteps INNER JOIN DEMOutput ON TimeSteps.TSIndex = DEMOutput.TSIndex) "
+ " INNER JOIN NodesInfo ON DEMOutput.NNo = NodesInfo.NNumber "
+ " where NodesInfo.NName = '" + modsimName + "' AND "
+ GetDateClause(t1, t2);
if (modsimName.IndexOf(",") >= 0)
{// sum demand group together.
string where = "( '" + modsimName.Replace(",", "','") + "') ";
sql = "SELECT TimeSteps.TSDate, Sum(DEMOutput." + columnName + " ) AS " + columnName
+ " FROM TimeSteps INNER JOIN (DEMOutput INNER JOIN NodesInfo ON DEMOutput.NNo = NodesInfo.NNumber) "
+ " ON TimeSteps.TSIndex = DEMOutput.TSIndex "
+ " WHERE " + GetDateClause(t1, t2)
+ " AND (NodesInfo.NName in " + where + " ) "
+ " GROUP BY TimeSteps.TSDate ";
}
DataTable tbl = AccessDB.Table(m_databaseName, "DEMOutput", sql);
InitTimeSeries(tbl, Units, TimeInterval, true);
}
/// <summary>
/// returns MODSIM Reservoir Storage output
/// </summary>
private void ReservoirStorageOutput(string modsimName, string columnName, DateTime t1, DateTime t2)
{
// using EndDate -- karl
string sql = "SELECT TimeSteps.TSDate, RES_STOROutput." + columnName //+ SQLMultiplyByScale(columnName)
+ " FROM (TimeSteps INNER JOIN RES_STOROutput ON TimeSteps.TSIndex = RES_STOROutput.TSIndex) "
+ " INNER JOIN NodesInfo ON RES_STOROutput.NNo = NodesInfo.NNumber "
+ " where NodesInfo.NName = '" + modsimName + "' AND"
+ GetDateClause(t1, t2);
if (modsimName.IndexOf(",") >= 0)
{// sum reservoir group together.
string where = "( '" + modsimName.Replace(",", "','") + "') ";
sql = "SELECT TimeSteps.TSDate, Sum(RES_STOROutput." + columnName + " ) AS " + columnName
+ " FROM TimeSteps INNER JOIN (RES_STOROutput INNER JOIN NodesInfo ON RES_STOROutput.NNo = NodesInfo.NNumber) "
+ " ON TimeSteps.TSIndex = RES_STOROutput.TSIndex "
+ " WHERE " + GetDateClause(t1, t2)
+ " AND (NodesInfo.NName in " + where + " ) "
+ " GROUP BY TimeSteps.TSDate ";
}
DataTable tbl = AccessDB.Table(m_databaseName, "RES_STOROutput", sql);
InitTimeSeries(tbl, Units, this.TimeInterval, true);
}
public void TestTable()
{
string mdbFilename = Path.Combine(Globals.TestDataPath, "Water Quality Testing.mdb");
AccessDB a = new AccessDB(mdbFilename);
DataTable tbl = a.Table("Water");
Assert.AreEqual(5880, tbl.Rows.Count);
// Console.WriteLine(tbl.Rows.Count);
}
private void ReadFromMdb(DateTime t1, DateTime t2)
{
string dateFormat = "yyyy-MM-dd HH:mm:ss";
string sql = "SELECT [" + m_dateColumn + "] as [DateTime], [" + m_valueColumn + "]"
+ " FROM [" + m_tableName + "] "
+ " WHERE [" + m_dateColumn + "] >= #" + t1.ToString(dateFormat) + "#"
+ " AND [" + m_dateColumn + "] <= #" + t2.ToString(dateFormat) + "#";
string query = "Select * from [" + m_tableName + "] Where 1 = 0";
DataTable schema = AccessDB.Table(m_filename, m_tableName, query);
if (m_filterValue != "" && m_filterColumn != "")
{
if (NeedQuotes(schema.Columns[m_filterColumn]))
{
sql += " AND [" + m_filterColumn + "] = '" + m_filterValue + "'";
}
else
{
sql += " AND [" + m_filterColumn + "] = " + m_filterValue;
}
}
// get columns to define table
DataTable tbl = AccessDB.Table(m_filename, m_tableName, sql + " AND 1 = 0");
InitTimeSeries(tbl, this.Units, this.TimeInterval, false);
tbl = AccessDB.Table(m_filename, m_tableName, sql);
// CsvFile.WriteToCSV(tbl, @"c:\temp\a.csv",true);
foreach (DataRow r in tbl.Rows)
{
double v = Point.MissingValueFlag;
if (r[1] != DBNull.Value)
{
v = Convert.ToDouble(r[1]);
}
DateTime t = Convert.ToDateTime(r[0]);
if (this.IndexOf(t) >= 0)
{
Logger.WriteLine("Skipping duplicate value " + t + ", " + v);
}
else
{
Add(t, v);
}
}
}
/// returns MODSIM Reservoir (other than storage) output
/// </summary>
private void ReservoirOutput(string modsimName, string columnName, DateTime t1, DateTime t2)
{
if (columnName.Contains("Hydro_State"))
{
columnName = "Hydro_State";
}
// assumes date should be MidDate --Leslie
/* BLounsbury - Why MidDate? - Changing to tsdate to match flow/demand/etc */
string sql = "SELECT TimeSteps.TsDate, RESOutput." + columnName //+ SQLMultiplyByScale(columnName)
+ " FROM (TimeSteps INNER JOIN RESOutput ON TimeSteps.TSIndex = RESOutput.TSIndex) "
+ " INNER JOIN NodesInfo ON RESOutput.NNo = NodesInfo.NNumber "
+ " where NodesInfo.NName = '" + modsimName + "' AND "
+ GetDateClause(t1, t2);
DataTable tbl = AccessDB.Table(m_databaseName, "RESOutput", sql);
InitTimeSeries(tbl, Units, TimeInterval, true);
}
private Series ReadWithAverage(Series s, AccessDB mdb, DateTime t1, DateTime t2)
{
// gets 15th of month
//string sqlAVG = "SELECT DateValue(Month(Working_Set.PeriodStart) & " + "\"" + "/15/" + "\"" + " &"
// + " IIf(Month(Working_Set.PeriodStart)>9,Left(Working_Set.WySeq,4),Left(Working_Set.WySeq,4)+1))"
// + " AS mmddyyyy, Avg(Working_Set.Data) AS AvgOfData"
// + " FROM Working_Set"
// + " GROUP BY DateValue(Month(Working_Set.PeriodStart) & " + "\"" + "/15/" + "\"" + " &"
// + " IIf(Month(Working_Set.PeriodStart)>9,Left(Working_Set.WySeq,4),Left(Working_Set.WySeq,4)+1)),"
// + " Working_Set.PlantName, Working_Set.DataType"
// + " HAVING (((DateValue(Month(Working_Set.PeriodStart) & " + "\"" + "/15/" + "\"" + " &"
// + " IIf(Month(Working_Set.PeriodStart)>9,Left(Working_Set.WySeq,4),Left(Working_Set.WySeq,4)+1)))"
// + ">=#" + t1.Date + "# And (DateValue(Month(Working_Set.PeriodStart) & " + "\"" + "/15/" + "\"" + " &"
// + " IIf(Month(Working_Set.PeriodStart)>9,Left(Working_Set.WySeq,4),Left(Working_Set.WySeq,4)+1)))"
// + "<=#" + t2.Date + "#) AND ((Working_Set.PlantName)=" + "\"" + m_plantName + "\"" + ") AND"
// + " ((Working_Set.DataType)=" + "\"" + m_dataType + "\"" + "))";
// gets end of month
string sqlAVG = "SELECT DateValue(Month(Working_Set.PeriodStart) & " + "\"" + "/" + "\"" + " & Day(DateAdd('d', -1,"
+ " DateSerial(IIf(Month(Working_Set.PeriodStart)>9,Left(Working_Set.WySeq,4),Left(Working_Set.WySeq,4)+1),"
+ " Month(Working_Set.PeriodStart)+1,1))) & " + "\"" + "/" + "\"" + " & IIf(Month(Working_Set.PeriodStart)>9,"
+ " Left(Working_Set.WySeq,4),Left(Working_Set.WySeq,4)+1)) AS mmddyyyy, Avg(Working_Set.Data) AS AvgOfData"
+ " FROM Working_Set"
+ " GROUP BY DateValue(Month(Working_Set.PeriodStart) & " + "\"" + "/" + "\"" + " & Day(DateAdd('d', -1,"
+ " DateSerial(IIf(Month(Working_Set.PeriodStart)>9,Left(Working_Set.WySeq,4),Left(Working_Set.WySeq,4)+1),"
+ " Month(Working_Set.PeriodStart)+1,1))) & " + "\"" + "/" + "\"" + " & IIf(Month(Working_Set.PeriodStart)>9,"
+ " Left(Working_Set.WySeq,4),Left(Working_Set.WySeq,4)+1)), Working_Set.PlantName, Working_Set.DataType"
+ " HAVING (((Working_Set.PlantName)='" + m_plantName + "') AND ((Working_Set.DataType)='" + m_dataType + "') AND"
+ " ((DateValue(Month(Working_Set.PeriodStart) & " + "\"" + "/" + "\"" + " & Day(DateAdd('d', -1,"
+ " DateSerial(IIf(Month(Working_Set.PeriodStart)>9,Left(Working_Set.WySeq,4),Left(Working_Set.WySeq,4)+1),"
+ " Month(Working_Set.PeriodStart)+1,1))) & " + "\"" + "/" + "\"" + " & IIf(Month(Working_Set.PeriodStart)>9,"
+ " Left(Working_Set.WySeq,4),Left(Working_Set.WySeq,4)+1)))>=#" + t1.Date + "# And"
+ " (DateValue(Month([Working_Set].[PeriodStart]) & " + "\"" + "/" + "\"" + " & Day(DateAdd('d', -1,"
+ " DateSerial(IIf(Month(Working_Set.PeriodStart)>9,Left(Working_Set.WySeq,4),Left(Working_Set.WySeq,4)+1),"
+ " Month(Working_Set.PeriodStart)+1,1))) & " + "\"" + "/" + "\"" + " & IIf(Month(Working_Set.PeriodStart)>9,"
+ " Left(Working_Set.WySeq,4),Left(Working_Set.WySeq,4)+1)))<=#" + t2.Date + "#))";
DataTable tbl = mdb.Table("Working_Set", sqlAVG);
s = SeriesFromTable(tbl, 0, 1);
return(s);
}
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);
}
private Series ReadEndOfMonth(Series s, AccessDB mdb, DateTime t1, DateTime t2)
{
//string sqlEOM = "SELECT DateValue(Month(Working_Set.PeriodEnd) & "+"\""+"/"+"\""+" &"
// + " Day(Working_Set.PeriodEnd) & "+"\""+"/"+"\""+" & IIf(Month(Working_Set.PeriodEnd)>9,"
// + " Left(Working_Set.WySeq,4),Left(Working_Set.WySeq,4)+1)) AS mmddyyyy, Working_Set.Data"
// + " FROM Working_Set"
// + " WHERE (((Working_Set.PlantName)=" + "\"" + m_plantName + "\"" + ") AND"
// + " ((Working_Set.DataType)=" + "\"" + m_dataType + "\"" + ") AND (Day(Working_Set.PeriodEnd)>27)"
// + " AND (DateValue(Month([Working_Set].[PeriodEnd]) & "+"\""+"/"+"\""+" & Day([Working_Set].[PeriodEnd])"
// + " & "+"\""+"/"+"\""+" & IIf(Month([Working_Set].[PeriodEnd])>9,Left([Working_Set].[WySeq],4),"
// + " Left([Working_Set].[WySeq],4)+1)))>=#"+t1.Date+"#"
// + " AND (DateValue(Month([Working_Set].[PeriodEnd]) & "+"\""+"/"+"\"" +" & Day([Working_Set].[PeriodEnd])"
// + " & "+"\""+"/"+"\""+" & IIf(Month([Working_Set].[PeriodEnd])>9,Left([Working_Set].[WySeq],4),"
// + " Left([Working_Set].[WySeq],4)+1)))<=#"+t2.Date+"#)";
// Fixed to calculate end of month from PeriodStart rather than relying on the database (one database used
// the 29th as the last day of February for every year, which created an error)
string sqlEOM = "SELECT DateValue(Month(Working_Set.PeriodStart) & " + "\"" + "/" + "\"" + " &"
+ " Day(DateAdd('d', -1, DateSerial(IIf(Month(Working_Set.PeriodStart)>9,Left(Working_Set.WySeq,4),"
+ " Left(Working_Set.WySeq,4)+1), Month(Working_Set.PeriodStart)+1,1))) & " + "\"" + "/" + "\"" + " &"
+ " IIf(Month(Working_Set.PeriodStart)>9,Left(Working_Set.WySeq,4),Left(Working_Set.WySeq,4)+1)) AS mmddyyyy,"
+ " Working_Set.Data"
+ " FROM Working_Set"
+ " WHERE (((Working_Set.PlantName)='" + m_plantName + "') AND ((Working_Set.DataType)='" + m_dataType + "')"
+ " AND (Day(Working_Set.PeriodEnd)>27)"
+ " AND DateValue(Month(Working_Set.PeriodStart) & " + "\"" + "/" + "\"" + " &"
+ " Day(DateAdd('d', -1, DateSerial(IIf(Month(Working_Set.PeriodStart)>9,Left(Working_Set.WySeq,4),"
+ " Left(Working_Set.WySeq,4)+1), Month(Working_Set.PeriodStart)+1,1))) & " + "\"" + "/" + "\"" + " &"
+ " IIf(Month(Working_Set.PeriodStart)>9,Left(Working_Set.WySeq,4),Left(Working_Set.WySeq,4)+1))>=#" + t1.Date + "#"
+ " AND DateValue(Month(Working_Set.PeriodStart) & " + "\"" + "/" + "\"" + " &"
+ " Day(DateAdd('d', -1, DateSerial(IIf(Month(Working_Set.PeriodStart)>9,Left(Working_Set.WySeq,4),"
+ " Left(Working_Set.WySeq,4)+1), Month(Working_Set.PeriodStart)+1,1))) & " + "\"" + "/" + "\"" + " &"
+ " IIf(Month(Working_Set.PeriodStart)>9,Left(Working_Set.WySeq,4),Left(Working_Set.WySeq,4)+1))<=#" + t2.Date + "#)";
DataTable tbl = mdb.Table("Working_Set", sqlEOM);
s = SeriesFromTable(tbl, 0, 1);
return(s);
}
public override PeriodOfRecord GetPeriodOfRecord()
{
string sql = "select count(*), min(" + m_dateColumn + "),max(" + m_dateColumn + ") from [" + m_tableName + "]";
string query = "Select * from [" + m_tableName + "] Where 1 = 0";
DataTable schema = AccessDB.Table(m_filename, m_tableName, query);
if (m_filterValue != "" && m_filterColumn != "")
{
if (NeedQuotes(schema.Columns[m_filterColumn]))
{
sql += " WHERE [" + m_filterColumn + "] = '" + m_filterValue + "'";
}
else
{
sql += " WHERE [" + m_filterColumn + "] = " + m_filterValue;
}
}
DateTime t1 = TimeSeriesDatabase.MinDateTime; //.. DateTime.MinValue;
DateTime t2 = TimeSeriesDatabase.MaxDateTime; // DateTime.MinValue;
int count = 0;
DataTable por = AccessDB.Table(m_filename, "por", sql);
count = Convert.ToInt32(por.Rows[0][0]);
if (count > 0)
{
t1 = Convert.ToDateTime(por.Rows[0][1]);
t2 = Convert.ToDateTime(por.Rows[0][2]);
}
PeriodOfRecord rval = new PeriodOfRecord(t1, t2, count);
return(rval);
}