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 void ImportDecodesAndProcessWithFlagLimits()
{
Logger.EnableLogger();
FileUtility.CleanTempPath();
var fn1 = FileUtility.GetTempFileName(".pdb");
Console.WriteLine(fn1);
var svr = new SQLiteServer(fn1);
var db = new TimeSeriesDatabase(svr, Reclamation.TimeSeries.Parser.LookupOption.TableName,false);
var tmpDir = CopyTestDecodesFileToTempDirectory("decodes_lapo.txt");
var rtlapo = CreateTempRatingTable("lapo.csv", new double[] {3.50,3.54,3.55,5.54 },
new double[] {1,2,3,10 });
// set limits gh: low=3.53, high 3.6, rate of change/hour 1
Quality q = new Quality(db);
q.SaveLimits("instant_lapo_gh", 3.6, 3.53, 1.0);
q.SaveLimits("instant_lapo_q", 5, 1.1, 0);
var site = db.GetSiteCatalog();
site.AddsitecatalogRow("lapo", "", "OR");
db.Server.SaveTable(site);
var c = new CalculationSeries("instant_lapo_q");
c.SiteID = "lapo";
c.Expression = "FileRatingTable(%site%_gh,\""+rtlapo+"\")";
db.AddSeries(c);
//SeriesExpressionParser.Debug = true;
FileImporter import = new FileImporter(db);
import.Import(tmpDir,RouteOptions.None,computeDependencies:true,searchPattern:"*.txt");
db.Inventory();
var s = db.GetSeriesFromTableName("instant_lapo_gh");
var expectedFlags = new string[] { "", "", "", "+", "", "", "", "-" };
for (int i = 0; i < s.Count; i++)
{
Assert.AreEqual(expectedFlags[i], s[i].Flag, " flag not expected ");
}
s = db.GetSeriesFromTableName("instant_lapo_q");
s.Read();
Assert.IsTrue(s.Count > 0, "No flow data computed lapo");
s.WriteToConsole(true);
// computed flows should be: 2 2 2 10 2 2 1
expectedFlags = new string[]{"","","","+","","","","-"}; //q>=1 and q<= 5
for (int i = 0; i < s.Count; i++)
{
Assert.AreEqual(expectedFlags[i], s[i].Flag.Trim()," Flag check on Flow (Q) ");
}
SeriesExpressionParser.Debug = false;
}
public TimeSeriesDatabaseUtility(TimeSeriesDatabase db)
{
m_db = db;
m_seriesCatalog = m_db.GetSeriesCatalog();
m_siteCatalog = m_db.GetSiteCatalog();
}
/// <summary>
/// Insert the contents of another time series database
/// at the specified folder.
/// </summary>
/// <param name="folder"></param>
/// <param name="db"></param>
public void InsertDatabase(PiscesFolder folder, TimeSeriesDatabase db)
{
var sc = GetSeriesCatalog();
var new_sc = db.GetSeriesCatalog();
for (int i = 0; i < new_sc.Rows.Count; i++)
{
var row = new_sc[i];
var path = new_sc.GetPath(row.id);
var folderID = sc.GetOrCreateFolder(path);
if (row.IsFolder == 0)
{// add series
var s = db.Factory.GetSeries(row.id);
var sr = sc.AddSeriesCatalogRow(s, sc.NextID(), folderID, s.Table.TableName);
// get time series data
s.Read();
ImportTimeSeriesTable(s.Table,sr, DatabaseSaveOptions.Insert);
}
}
Server.SaveTable(sc);
// Add Sites.
var sites = GetSiteCatalog();
var new_sites = db.GetSiteCatalog();
for (int i = 0; i < new_sites.Count; i++)
{
if( !sites.Exists(new_sites[i].siteid))
{
var newRow =sites.NewsitecatalogRow();
newRow.ItemArray = new_sites[i].ItemArray;
sites.AddsitecatalogRow(newRow);
}
}
Server.SaveTable(sites);
// add properties. TO DO.
}
/// <summary>
/// update or insert new records in sitecatlog
/// </summary>
/// <param name="db"></param>
/// <param name="csvFileName">input csv sitecatalog</param>
private static void UpdateSiteCatalog(TimeSeriesDatabase db, string csvFileName)
{
var scRWIS = db.GetSiteCatalog();
var scLocal = new CsvFile(csvFileName, CsvFile.FieldTypes.AllText);
for (int i = 0; i < scLocal.Rows.Count; i++)
{
var localRow = scLocal.Rows[i];
var siteID = localRow["siteid"].ToString();
//check for existing record
var rwisRows = scRWIS.Select("siteid='" + siteID + "'");
if( rwisRows.Length == 1)
{// update
Console.WriteLine("updating record: " + siteID);
var rwisRow = rwisRows[0];
for (int c = 0; c < scRWIS.Columns.Count; c++)
{
var cn = scRWIS.Columns[c].ColumnName;
if (cn == "siteid")
continue;
if ( cn == "vertical_accuracy") // float
{
double x = 0;
double.TryParse(localRow[cn].ToString(), out x);
rwisRow[cn] = x;
continue;
}
rwisRow[cn] = localRow[cn];
}
}
else
{// insert
Console.WriteLine("new record: "+siteID);
double vertical_accuracy = 0;
double latitude = 0;
double longitude = 0;
double elevation = 0;
double.TryParse(localRow["vertical_accuracy"].ToString(),out vertical_accuracy);
double.TryParse(localRow["latitude"].ToString(), out latitude);
double.TryParse(localRow["longitude"].ToString(), out longitude);
double.TryParse(localRow["elevation"].ToString(), out elevation);
scRWIS.AddsitecatalogRow(localRow["siteid"].ToString(), localRow["description"].ToString(), localRow["state"].ToString(),
latitude, longitude, elevation, localRow["timezone"].ToString(),
localRow["install"].ToString(), localRow["horizontal_datum"].ToString(), localRow["vertical_datum"].ToString(),
vertical_accuracy, localRow["elevation_method"].ToString(), localRow["tz_offset"].ToString(),
localRow["active_flag"].ToString(), localRow["type"].ToString(), localRow["responsibility"].ToString(), localRow["agency_region"].ToString());
}
}
db.Server.SaveTable(scRWIS);
}
static void SiteInventory(Arguments args, TimeSeriesDatabase db)
{
var site = args["siteinventory"].ToString().ToLower();
var siteCatalog = db.GetSiteCatalog("lower(description) LIKE '%" + site + "%'");
if (siteCatalog.Rows.Count < 1)
{
Console.WriteLine("Site " + site + " not found... ");
return;
}
else
{
foreach (var siteRow in siteCatalog)
{
var seriesCatalog = db.GetSeriesCatalog("isfolder = 0 AND siteid = '" + siteRow["siteid"] + "'");
Console.WriteLine("---------------------------------------------------------------------");
Console.WriteLine(siteRow["siteid"] + " | " + siteRow["description"] + " | " + siteRow["state"] +
" | " + siteRow["agency_region"]);
foreach (var row in seriesCatalog)
{ Console.WriteLine(" -- " + row["id"] + " | " + row["name"]); }
}
Console.WriteLine("---------------------------------------------------------------------");
}
}
/// <summary>
/// Main entry form
/// </summary>
/// <param name="args"></param>
public static void startRwisUiMain(string[] args)
{
if (jrDebug)
{
// Connect to DB Server
string dbname = ConfigurationManager.AppSettings["MySqlDatabase"];
string server = ConfigurationManager.AppSettings["MySqlServer"];
string user = ConfigurationManager.AppSettings["MySqlUser"];
BasicDBServer svr = MySqlServer.GetMySqlServer(server, dbname, user);
db = new TimeSeriesDatabase(svr);
siteCat = db.GetSiteCatalog();
parCat = db.GetParameterCatalog();
}
// Starts the application.
Application.Run(new rwisForm());
}
private void ProcessFile(string fileName)
{
Logger.WriteLine("ProcessFile('" + fileName + "')");
string importTag = "import"; // used to make friendly export filename
try
{
TextFile tf = new TextFile(fileName);
SeriesList sl = new SeriesList();
if (HydrometInstantSeries.IsValidDMS3(tf))
{
importTag = "decodes";
sl = HydrometInstantSeries.HydrometDMS3DataToSeriesList(tf);
}
else if (HydrometDailySeries.IsValidArchiveFile(tf))
{
importTag = "htools";
sl = HydrometDailySeries.HydrometDailyDataToSeriesList(tf);
}
else if (LoggerNetFile.IsValidFile(tf))
{
LoggerNetFile lf = new LoggerNetFile(tf);
if (lf.IsValid && Array.IndexOf(validSites, lf.SiteName) >= 0)
{
importTag = lf.SiteName;
sl = lf.ToSeries(validPcodes);
}
}
else if (CsvScadaFile.IsValidFile(tf))
{
importTag = "scada";
m_db.GetSiteCatalog();
var scada = new CsvScadaFile(fileName);
sl = scada.ToSeries();
}
else if (XConnectTextFile.IsValidFile(tf))
{
importTag = "xc";
var xc = new XConnectTextFile(tf);
sl = xc.ToSeries();
}
else if (HydrometMonthlySeries.IsValidFile(tf))
{
sl = HydrometMonthlySeries.FileToSeriesList(tf);
}
else
{
Logger.WriteLine("skipped Unknown File Format: " + fileName);
return;
}
if (m_importer == null)
{
m_importer = new TimeSeriesImporter(m_db, m_saveOption);
}
Console.WriteLine("Found " + sl.Count + " series in " + fileName);
foreach (var item in sl)
{
Logger.WriteLine(item.Table.TableName);
}
if (sl.Count > 0)
{
m_importer.Import(sl, m_computeDependencies, m_computeDailyDependencies, importTag);
FileUtility.MoveToSubDirectory(Path.GetDirectoryName(fileName), "attic", fileName);
}
}
catch (Exception ex)
{
Logger.WriteLine("Error:" + ex.Message);
Console.WriteLine("Error: skipping file, will move to error subdirectory " + fileName);
FileUtility.MoveToSubDirectory(Path.GetDirectoryName(fileName), "error", fileName);
}
}
