private void comboBoxFilter_SelectedIndexChanged(object sender, EventArgs e)
{
try
{
Cursor = Cursors.WaitCursor;
Application.DoEvents();
this.checkedListBoxSites.Items.Clear();
var tbl = db.GetDataTable(SheetName, SpreadsheetGear.Data.GetDataFlags.FormattedText);
var table = DataTableUtility.SelectDistinct(tbl, SiteColumn);
var sites = DataTableUtility.StringList(table, "", SiteColumn);
this.checkedListBoxSites.Items.AddRange(sites.ToArray());
}
finally
{
Cursor = Cursors.Default;
}
}
public static string[] LookupMonthlyInventory(string cbtt)
{
List <string> rval = new List <string>();
var tbl = DataTableUtility.Select(MonthlyInventory, "Cbtt='" + cbtt + "'", "cbtt,pcode");
var pcodeList = DataTableUtility.StringList(DataTableUtility.SelectDistinct(tbl, "pcode"), "", "pcode");
foreach (var pc in pcodeList)
{
string line = cbtt.PadRight(12) + " " + pc.PadRight(9);
var rows = tbl.Select("Cbtt='" + cbtt + "' and pcode='" + pc + "'");
for (int i = 0; i < rows.Length; i++)
{
line += rows[i]["years"].ToString() + " ";
}
rval.Add(line);
}
return(rval.ToArray());
}
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>
/// Goal: High performance import of Dayfiles to TimeSeriesDatabase
/// </summary>
/// <param name="fileName"></param>
private static void ImportVaxFile(TimeSeriesDatabase db, string fileNamePattern)
{
// we have 12,000 dayfiles to import. (avg 3.0 MB each)
// if it takes 4 seconds to read one file and sort
// that is allready 13 hours just to read the files.
// initial results 72 seconds to import 2013sep21.day (agrimet/hydromet subset)
// that would take 10 days
Console.WriteLine("Reading file(s): " + fileNamePattern);
Performance p = new Performance();
string path = Path.GetDirectoryName(fileNamePattern);
string searchPattern = Path.GetFileName(fileNamePattern);
var files = Directory.GetFiles(path, searchPattern, SearchOption.TopDirectoryOnly);
DataTable tbl = null;
foreach (var fileName in files)
{
if (db.Server.SqlCommands.Count > 5000)
{
db.Server.SqlCommands.Clear(); // we can run out out of memory if we save all commands.
}
Console.Write("Reading " + fileName + " ");
string interval = "instant";
if (Path.GetExtension(fileName).ToLower() == ".day")
{
var df = new DayFile(fileName, false, false);
tbl = df.GetTable();
}
if (Path.GetExtension(fileName).ToLower() == ".acf")
{
var acf = new ArchiveFile(fileName);
interval = "daily";
tbl = acf.GetTable();
}
if (Path.GetExtension(fileName).ToLower() == ".ind")
{
var acf = new MpollFile(fileName);
interval = "daily";
tbl = acf.GetTable();
}
// sw.WriteLine("DateTime, site,pcode,value,flag");
// tbl = DataTableUtility.Select(tbl, "", "site,pcode");
//p.Report("sorted"); // 4.3 sec
var distinct = DataTableUtility.SelectDistinct(tbl, "site", "pcode");
//p.Report("distinct list " + distinct.Rows.Count + " items");
foreach (DataRow row in distinct.Rows)
{
string cbtt = row["site"].ToString();
string pcode = row["pcode"].ToString();
TimeSeriesName tn = new TimeSeriesName(cbtt + "_" + pcode, interval);
if (!tn.Valid)
{
Console.WriteLine("skipping Invalid cbtt/pcode " + cbtt + "/" + pcode);
continue;
}
string filter = "site = '" + cbtt + "' and pcode = '" + pcode + "'";
var filterRows = tbl.Select(filter, "");
var s = db.GetSeriesFromTableName(tn.GetTableName(), "", true);
if (s == null)
{
//Console.WriteLine("Skipping : " + tn.GetTableName() + " not found in database ");
continue;
}
else
{
for (int i = 0; i < filterRows.Length; i++)
{
DateTime t = Convert.ToDateTime(filterRows[i]["DateTime"]);
if (s.IndexOf(t) >= 0)
{
Console.WriteLine("Warning: skipping duplicate date " + t.ToString());
continue;
}
s.Add(t, Convert.ToDouble(filterRows[i]["value"]),
filterRows[i]["flag"].ToString());
}
int count = db.SaveTimeSeriesTable(s.ID, s, DatabaseSaveOptions.Insert);
Console.WriteLine(tn.GetTableName() + ": Saved " + count + " rows");
}
}
}
p.Report("Done. importing " + searchPattern);
}
public static string[] GetSnowggGroupList()
{
var tbl = DataTableUtility.SelectDistinct(SnowggGroups, "Group");
return(DataTableUtility.Strings(tbl, "", "Group"));
}
