///// <summary> ///// Print DataTable composed of tablename,datetime,value[,flag] ///// with columns for each tablename ///// </summary> ///// <param name="list"></param> ///// <param name="table"></param> //private static void PrintDataTable(SeriesList list, DataTable table, // Formatter fmt, TimeInterval interval) //{ // var t0 = ""; // if (table.Rows.Count > 0) // t0 = fmt.FormatDate(table.Rows[0][1]); // var vals = new string[list.Count]; // var flags = new string[list.Count]; // var dict = new Dictionary<string, int>(); // for (int i = 0; i < list.Count; i++) // { // dict.Add(list[i].Table.TableName, i); // } // string t = ""; // bool printThisRow = false; // for (int i = 0; i < table.Rows.Count; i++) // { // var row = table.Rows[i]; // t = fmt.FormatDate(row[1]); // if (t != t0) // { // if (printThisRow) // fmt.PrintRow(t0, vals, flags); // vals = new string[list.Count]; // flags = new string[list.Count]; // t0 = t; // } // vals[dict[row[0].ToString()]] = fmt.FormatNumber(row[2]); // flags[dict[row[0].ToString()]] = fmt.FormatFlag(row[3]); // DateTime date = Convert.ToDateTime(row[1]); // bool topOfHour = date.Minute == 0; // printThisRow = fmt.HourlyOnly == false || (fmt.HourlyOnly && topOfHour); // } // if (printThisRow) // fmt.PrintRow(t, vals, flags); //} private static TimeSeriesName[] GetTimeSeriesName(NameValueCollection query, TimeInterval interval) { List <TimeSeriesName> rval = new List <TimeSeriesName>(); //add support for the auto generated parameter list //if the sites list is just the site return a list of all parameters in the table var sites = HydrometWebUtility.GetParameter(query, "list"); Logger.WriteLine("GetTimeSeriesName()"); Logger.WriteLine(query.ToString()); var siteCodePairs = sites.Split(','); foreach (var item in siteCodePairs) { var tokens = item.Split(new char[] { ' ' }, StringSplitOptions.RemoveEmptyEntries); if (tokens.Length == 2) { TimeSeriesName tn = new TimeSeriesName(tokens[0] + "_" + tokens[1], interval); rval.Add(tn); } } return(rval.ToArray()); }
/// <summary> /// Lookup hydromet Series. /// name is interval_cbtt_pcode /// </summary> /// <param name="name"></param> /// <returns></returns> public override ParserResult Lookup(string name, TimeInterval defaultInterval) { var interval = defaultInterval; TimeSeriesName tn = new TimeSeriesName(name); if( tn.HasInterval) { interval = tn.GetTimeInterval(); } if (tn.Valid) { Logger.WriteLine("Hydromet Lookup " + tn.siteid + "," + tn.pcode); var s = new Series(); if (interval == TimeInterval.Monthly) { s = new HydrometMonthlySeries(tn.siteid, tn.pcode,svr); } else if (interval == TimeInterval.Irregular) { s = new HydrometInstantSeries(tn.siteid, tn.pcode,svr); } else if (interval == TimeInterval.Daily) { s = new HydrometDailySeries(tn.siteid, tn.pcode,svr); } return new ParserResult(s); } else { return base.Lookup(name,interval); } }
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); } }
public override void WriteSeriesHeader(SeriesList list) { WriteLine(HydrometWebUtility.HydrometHeader()); WriteLine("BEGIN DATA"); string headLine = "DATE "; if (m_interval == TimeInterval.Irregular || m_interval == TimeInterval.Hourly) { headLine = "DATE TIME "; } foreach (var item in list) { TimeSeriesName tn = new TimeSeriesName(item.Table.TableName); if (m_interval == TimeInterval.Daily) { //fprintf(stdout,", %4.8s %-4.8s",params[i].station,params[i].pcode); headLine += delim + " " + tn.siteid.PadRight(4) + " " + tn.pcode.PadRight(4); } else { headLine += delim + " " + tn.siteid.PadRight(8) + "" + tn.pcode.PadRight(8); } } headLine = headLine.ToUpper(); WriteLine(headLine); }
/// <summary> /// Returns a list of IDWR series that have set /// in the seriesproperties. /// </summary> /// <param name="svr"></param> /// <returns></returns> internal static List <TimeSeriesName> GetIDWRDailyList(BasicDBServer svr, string customList) { string[] allowed = { "wd01", "wd63", "wd65" }; customList = customList.ToLower(); if (Array.IndexOf(allowed, customList) < 0) { Logger.WriteLine("invalid water district:" + customList); return(new List <TimeSeriesName>()); } List <TimeSeriesName> rval = new List <TimeSeriesName>(); var sql = @" select s.tablename, p.value siteid ,s.parameter from seriescatalog s join seriesproperties p on s.id = p.seriesid join sitecatalog c on c.siteid = s.siteid where timeinterval = 'Daily' "; sql += " and p.name ='" + customList + "' order by tablename"; var tbl = svr.Table("idwr", sql); IdwrAccountingFormatter.CustomNames = tbl; for (int i = 0; i < tbl.Rows.Count; i++) { var tn = new TimeSeriesName(tbl.Rows[i]["tablename"].ToString()); rval.Add(tn); } return(rval); }
/// <summary> /// Finds a matching row /// TableMask can be exact or with wildcards. /// For example *_ob will apply to all table names that end with ‘_ob’. /// Flags will not be applied to tables that are not defined by a mask. /// Also nulls in the columns (High, Low, Change) will cause no flagging to occur. /// </summary> /// <param name="tableName"></param> /// <returns></returns> public TimeSeriesDatabaseDataSet.quality_limitRow GetRow(string tableName) { // try exact match first. instant_odsw_ob var rows = m_limit.Select("TableMask = '" + tableName + "'"); if (rows.Length == 1) return rows[0] as TimeSeriesDatabaseDataSet.quality_limitRow; TimeSeriesName tn = new TimeSeriesName(tableName); // try site specific next: odsw_ob var mask = tn.siteid + "_" + tn.pcode; rows = m_limit.Select("TableMask = '" + mask + "'"); if (rows.Length == 1) return rows[0] as TimeSeriesDatabaseDataSet.quality_limitRow; // try parameter alone next mask = tn.pcode; rows = m_limit.Select("TableMask like '[*]_" + mask + "'"); if (rows.Length == 1) return rows[0] as TimeSeriesDatabaseDataSet.quality_limitRow; return null; }
/// <summary> /// returns series that have idwr_cbtt set in the siteproperites /// and have a idwr_shef set in the seriesproperties. /// Also sets the CustomNames property in the ShefFormatter /// example url : instant?custom_list=idwr&format=shefa /// </summary> /// <param name="svr"></param> /// <returns></returns> internal static List <TimeSeriesName> GetIDWRInstantList(BasicDBServer svr) { List <TimeSeriesName> rval = new List <TimeSeriesName>(); var sql = @" select s.tablename,p.value parameter,sitep.value siteid , case(c.timezone) when 'US/Mountain' then 'M' when 'US/Pacific' then 'P' end as timezone from seriescatalog s join seriesproperties p on s.id = p.seriesid join siteproperties sitep on sitep.siteid =s.siteid join sitecatalog c on c.siteid = s.siteid where p.name ='idwr_shef' and sitep.name='idwr_cbtt' and timeinterval = 'Irregular' "; var tbl = svr.Table("idwr", sql); ShefAFormatter.CustomNames = tbl; for (int i = 0; i < tbl.Rows.Count; i++) { var tn = new TimeSeriesName(tbl.Rows[i]["tablename"].ToString()); rval.Add(tn); } return(rval); }
/// <summary> /// Finds the object or value assoicated with the named variable. /// The search matches either (TableName or SeriesName) based on the LookupOption /// /// SeriesName searches that include a name with a prefix such as daily_ override the /// defaultInterval in the search. /// /// </summary> /// <param name="name">variable name</param> /// <param name="defaultInterval">interval used when looking up variable name</param> /// <returns></returns> public virtual ParserResult Lookup(string name, TimeInterval defaultInterval) { if (name == "true") // This is not a variable... but a boolean... move this code to SeriesExpressionParser token type.? { return(new ParserResult(true)); } if (name == "false") { return(new ParserResult(false)); } if (vars.ContainsKey(name)) { return(vars[name]); } if (db != null) { TimeSeriesName tn = new TimeSeriesName(name); string lookupInterval = ""; if (tn.HasInterval) { lookupInterval = tn.interval; } else { lookupInterval = defaultInterval.ToString().ToLower(); } Series s2 = null; if (m_lookup == LookupOption.SeriesName) { // drop the prefix (such as daily_) if it exists var search = Regex.Replace(name, "^(daily_)|(monthly_)|(instant_)", ""); s2 = db.GetSeriesFromName(search, ""); // ignore default interval when using nam.. if (s2 == null) { s2 = db.GetSeriesFromName(search, lookupInterval); } } else { if (m_lookup == LookupOption.TableName) { s2 = db.GetSeriesFromTableName(name, lookupInterval); } } if (s2 != null) { return(new ParserResult(s2)); } } // throw an error.... throw new Exception("Error finding variable '" + name + "', this could indicate there are two series with the same name"); //return new ParserResult(name); // returns a string result. ... }
private void buttonOK_Click(object sender, EventArgs e) { m_series.Expression = basicEquation1.SeriesExpression; var a = this.basicEquation1.SiteID; if (a != "") { if (string.IsNullOrEmpty(basicEquation1.Parameter)) { m_series.Name = a; a += "_c" + DateTime.Now.ToString("yyyyMMMddHHmmssfff").ToLower(); } else { a += "_" + basicEquation1.Parameter; m_series.Name = a; } string tn = basicEquation1.TimeInterval.ToString().ToLower() + "_" + TimeSeriesDatabase.SafeTableName(a); tn = tn.Replace("irregular", "instant"); m_series.Table.TableName = tn; TimeSeriesName x = new TimeSeriesName(a, basicEquation1.TimeInterval); m_series.Parameter = x.pcode; m_series.SiteID = x.siteid; } string errorMessage = ""; m_series.TimeInterval = basicEquation1.TimeInterval; var xcs = m_db.GetCalculationSeries(m_series.SiteID, m_series.Parameter, m_series.TimeInterval); if (xcs != null) { errorMessage = "This calculation already exists."; MessageBox.Show("Error: " + errorMessage); DialogResult = DialogResult.None; } else if ( m_series.TimeSeriesDatabase.Parser.VariableResolver is Parser.HydrometVariableResolver || m_series.IsValidExpression(basicEquation1.SeriesExpression, out errorMessage) ) { DialogResult = DialogResult.OK; } else { var result = MessageBox.Show("Your equation may have an error. Click OK to proceed.\n" + errorMessage, "Use this Equation?", MessageBoxButtons.OKCancel); if (result == DialogResult.Cancel) { DialogResult = DialogResult.None; } } }
private void AddDailyCalculation(string name, string exp) { var c = new CalculationSeries(name); TimeSeriesName tn = new TimeSeriesName(name); c.Expression = exp; c.SiteID = tn.siteid; c.TimeInterval = TimeInterval.Daily; db.AddSeries(c); }
private string[] GetTableNames(string[] tokens) { var rval = new List <String>(); for (int j = 1; j < tokens.Length; j++) { var n = new TimeSeriesName(tokens[j].Replace(".", "_"), TimeInterval.Irregular); rval.Add(n.GetTableName()); } return(rval.ToArray()); }
private void SaveShift_Click(object sender, System.EventArgs e) { float?num = null; if (this.textBoxDischarge.Text.Trim() != "") { num = new float?(System.Convert.ToSingle(this.textBoxDischarge.Text)); } float?num2 = null; if (this.textBoxGageHeight.Text.Trim() != "") { num2 = new float?(System.Convert.ToSingle(this.textBoxGageHeight.Text)); } HydrometTools.Login login = new HydrometTools.Login(); if (login.ShowDialog() == System.Windows.Forms.DialogResult.OK) { try { string text = HydrometEditsVMS.UpdateShift(login.Username, login.Password, this.cbtt.ToUpper().Trim(), this.textBoxPcode.Text.ToUpper().Trim(), (double)System.Convert.ToSingle(this.textBoxNewShift.Text)); if (text.ToLower().Contains("error")) { FormStatus.ShowStatus(text, true); } else { FormStatus.ShowStatus(text, false); System.Console.WriteLine(text); double shift = System.Convert.ToDouble(this.textBoxNewShift.Text); string arg_1D9_1 = this.cbtt.ToUpper().Trim(); string arg_1D9_2 = this.textBoxPcode.Text.ToUpper().Trim(); System.DateTime arg_1D9_3 = System.Convert.ToDateTime(this.DateMeasured.Text); float? num3 = num; double? arg_1D9_4 = num3.HasValue ? new double?((double)num3.GetValueOrDefault()) : null; num3 = num2; Database.InsertShift(arg_1D9_1, arg_1D9_2, arg_1D9_3, arg_1D9_4, num3.HasValue ? new double?((double)num3.GetValueOrDefault()) : null, shift, this.textBoxComments.Text, System.DateTime.Now); Reclamation.Core.BasicDBServer postgresServer = Reclamation.Core.PostgreSQL.GetPostgresServer("", "", ""); TimeSeriesName tn = new TimeSeriesName(this.cbtt.ToLower() + "_" + this.textBoxPcode.Text.ToLower(), "instant"); TimeSeriesDatabaseDataSet.seriespropertiesDataTable.Set("shift", shift.ToString(), tn, postgresServer); this.ReadShiftTable(); this.textBoxPcode.Text = ""; this.textBoxCbtt.Text = ""; this.textBoxDischarge.Text = ""; this.textBoxComments.Text = ""; this.textBoxNewShift.Text = ""; this.textBoxGageHeight.Text = ""; } } catch (System.Exception ex) { System.Windows.Forms.MessageBox.Show(ex.Message); } } }
public void AddEquationSeries(string name, string equation, string interval = "instant") { var f = db.GetOrCreateFolder("hydromet", "aga", interval); TimeSeriesName tn = new TimeSeriesName(name, interval); var c = new CalculationSeries(name); c.Table.TableName = tn.GetTableName(); c.Expression = equation; c.TimeInterval = tn.GetTimeInterval(); db.AddSeries(c, f); }
public override int GetHashCode() { unchecked { var hashCode = (ServerUrl != null ? ServerUrl.GetHashCode() : 0); hashCode = (hashCode * 397) ^ (ApiKey != null ? ApiKey.GetHashCode() : 0); hashCode = (hashCode * 397) ^ (Domain != null ? Domain.GetHashCode() : 0); hashCode = (hashCode * 397) ^ (Password != null ? Password.GetHashCode() : 0); hashCode = (hashCode * 397) ^ (Username != null ? Username.GetHashCode() : 0); hashCode = (hashCode * 397) ^ (TimeSeriesName != null ? TimeSeriesName.GetHashCode() : 0); return(hashCode); } }
private void WriteSeriesHeader(SeriesList list, TimeInterval interval) { //string headLine = "DATE, "; var headLine = "DATE TIME "; foreach (var item in list) { TimeSeriesName tn = new TimeSeriesName(item.Table.TableName); headLine += ", " + tn.siteid.PadRight(8) + "" + tn.pcode.PadRight(8); } headLine = headLine.ToUpper(); Console.WriteLine(headLine); }
public override void WriteSeriesHeader(SeriesList list) { //WriteLine("<PRE>"); string headLine = "DateTime"; foreach (var item in list) { TimeSeriesName tn = new TimeSeriesName(item.Table.TableName); headLine += "," + tn.siteid + "_" + tn.pcode; } WriteLine(headLine); }
public override void WriteSeriesHeader(SeriesList list) { WriteLine("<PRE>"); string headLine = "DateTime"; foreach (var item in list) { TimeSeriesName tn = new TimeSeriesName(item.Table.TableName); headLine += "," + tn.siteid + "_" + tn.pcode; } WriteLine(headLine); }
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); }
/// <summary> /// Prints in format used as input to IDWR Planning model. /// for example: ///13010500R201711 6AF 642871.00 ///13010500R201711 6FB 6760.77 ///13010500R201711 7FB 6760.77 ///13010500R201711 7AF 642871.00 ///13010500R201711 9FB 6760.77 ///13010500R20171110FB 6760.78 /// </summary> /// <param name="list"></param> /// <param name="table"></param> public override void PrintDataTable(SeriesList list, DataTable table) { Point.MissingValueFlag = 998877; var sortedRows = table.Select("", "tablename"); for (int i = 0; i < sortedRows.Length; i++) { var r = sortedRows[i]; var tn = new TimeSeriesName(r["tablename"].ToString()); Point p; if (r["value"] == DBNull.Value) { p = new Point((DateTime)r["datetime"], Point.MissingValueFlag, r["flag"].ToString()); } else { p = new Point((DateTime)r["datetime"], (double)r["value"], r["flag"].ToString()); } //if (p.IsMissing || p.FlaggedBad) //{ // continue; //} var pcode = tn.pcode.ToUpper(); var siteid = "";// tn.siteid; if (CustomNames != null) { var rows = CustomNames.Select("tablename = '" + tn.GetTableName() + "'"); if (rows.Length > 0) { r = rows[0]; siteid = r["siteid"].ToString(); } } if (siteid == "") { continue; } WriteLine(siteid + p.DateTime.ToString("yyyyMM") + p.DateTime.Day.ToString().PadLeft(2) + pcode + " " + p.Value.ToString("F2").PadLeft(11)); } }
private void RemoveProgramProperty(TimeSeriesName tn) { var x = m_db.GetSeriesCatalog("tablename ='" + tn.GetTableName() + "'"); if (x.Count == 0) { return; } var row = x[0]; var props = m_db.GetSeriesProperties(true); props.Delete("program", row.id); props.Save(); }
private static string[] GetParameters(string cbtt, TimeInterval interval) { var rval = new List <string>(); var svr = PostgreSQL.GetPostgresServer(); TimeSeriesDatabase p = new TimeSeriesDatabase(svr); var sql = " lower(siteid) = '" + svr.SafeSqlLiteral(cbtt.ToLower()) + "' and TimeInterval = '" + interval.ToString() + "'"; var sc = p.GetSeriesCatalog(sql); foreach (var item in sc) { TimeSeriesName tn = new TimeSeriesName(item.TableName); rval.Add(tn.pcode); } return(rval.ToArray()); }
static void Main(string[] args) { Logger.EnableLogger(); var svr = PostgreSQL.GetPostgresServer("timeseries", "lrgs1"); var db = new TimeSeriesDatabase(svr, Reclamation.TimeSeries.Parser.LookupOption.TableName); var sc = db.GetSeriesCatalog(); var prop = db.GetSeriesProperties(true); var mcf = McfUtility.GetDataSetFromCsvFiles(Globals.LocalConfigurationDataPath); Console.WriteLine("processing "); for (int i = 0; i < idwrSiteList.Length; i++) { var cbtt = idwrSiteList[i]; Console.WriteLine(cbtt); var rows = mcf.pcodemcf.Select("PCODE='" + cbtt.PadRight(8) + "CH'"); if (rows.Length == 1) { var shift = Convert.ToDouble(rows[0]["SHIFT"]); Console.WriteLine(shift); TimeSeriesName tn = new TimeSeriesName(cbtt.ToLower() + "_" + "ch", "instant"); Reclamation.TimeSeries.TimeSeriesDatabaseDataSet.seriespropertiesDataTable.Set("shift", shift.ToString("F4"), tn, svr); } } return; foreach (var pcode in mcf.pcodemcf) { if (pcode.IsRTCPROCNull()) { continue; } Console.WriteLine(pcode.PCODE); string cbtt = pcode.PCODE.Substring(0, 8).Trim().ToLower(); string pc = pcode.PCODE.Substring(8).Trim().ToLower(); var width_factor = pcode.SCALE.ToString("F4"); var exponent = pcode.BASE.ToString("F4"); var offset = pcode.OFFSET.ToString("F4"); var shift = pcode.SHIFT.ToString("F4"); } Console.WriteLine(); }
private static void RenameUntitled(BasicDBServer svr, TimeSeriesDatabase db) { var untitled = db.GetSeriesCatalog("name = 'untitled'"); for (int i = 0; i < untitled.Count; i++) { var item = untitled[i]; TimeSeriesName tn = new TimeSeriesName(item.TableName); Console.WriteLine("Renaming '" + item.Name + "' to " + tn.siteid + "_" + tn.pcode); item.Name = tn.siteid + "_" + tn.pcode; Console.WriteLine(item.Name + " " + item.TableName + " " + item.TimeInterval); } svr.SaveTable(untitled); }
public static IEnumerable <String> GetBlockOfQueries(TimeSeriesDatabase db, TimeInterval interval, string filter, string propertyFilter = "", int blockSize = 75) { var rval = new List <string>(); foreach (Series s in db.GetSeries(interval, filter, propertyFilter).ToArray()) { TimeSeriesName tn = new TimeSeriesName(s.Table.TableName); //rval.Add(s.SiteID + " " + s.Parameter); rval.Add(tn.siteid + " " + tn.pcode); if (rval.Count >= blockSize) { yield return(String.Join(",", rval.ToArray())); rval.Clear(); } } yield return(String.Join(",", rval.ToArray())); }
/// <summary> /// get most recent value for this table, or 'missing' /// </summary> /// <param name="table"></param> /// <param name="tn"></param> /// <returns></returns> private string GetLast(DataTable table, string tablename) { TimeSeriesName tn = new TimeSeriesName(tablename); var x = table.Select("tablename ='" + tablename + "'", "datetime"); if (x.Length > 0) { var r = x[x.Length - 1]; var t = DateTime.Parse(r["datetime"].ToString()); var val = Double.Parse(r["value"].ToString()); Point p = new Point(t, val, r["flag"].ToString()); string rval = p.DateTime.ToString("MMM dd HH:mm") + " " + tn.pcode.ToUpper() + " " + p.Value.ToString("F2").PadLeft(11) + p.Flag; return(rval); } return(" ".PadRight(14) + tn.pcode.ToUpper().PadRight(7) + "Missing"); }
public override void PrintDataTable(SeriesList list, DataTable table) { var sortedRows = table.Select("", "tablename"); for (int i = 0; i < sortedRows.Length; i++) { var r = sortedRows[i]; var tn = new TimeSeriesName(r["tablename"].ToString()); if (r["value"] == DBNull.Value) { continue; } Point p = new Point((DateTime)r["datetime"], (double)r["value"], r["flag"].ToString()); if (p.IsMissing || p.FlaggedBad) { continue; } var siteid = tn.siteid; var pcode = tn.pcode; var timeZone = "M"; if (CustomNames != null) { var rows = CustomNames.Select("tablename = '" + tn.GetTableName() + "'"); if (rows.Length > 0) { r = rows[0]; timeZone = r["timezone"].ToString(); siteid = r["siteid"].ToString(); pcode = r["parameter"].ToString(); } } WriteLine(".A " + siteid + " " + p.DateTime.ToString("yyMMdd") + " " + timeZone + " " + "DH" + p.DateTime.ToString("HHmm") + "/" + pcode + " " + p.Value.ToString("F2")); } }
//private static void EnterShiftToPisces(string cbtt,string pcode,double shift) //{ // var svr = PostgreSQL.GetPostgresServer(); // TimeSeriesName tn = new TimeSeriesName(cbtt.ToLower()+"_"+pcode.ToLower(),"instant"); // TimeSeriesDatabaseDataSet.seriespropertiesDataTable.Set("shift", shift.ToString(), tn, svr); // return; //} private static void InsertShiftToPostgres(string cbtt, string pcode, double shift, string dateMeasured, double?discharge, double?gh) { var svr = PostgreSQL.GetPostgresServer("timeseries"); //enter shift to shift db //string sql = "insert into "; //ds.insertshift(cbtt.ToUpper().Trim(), pcode.ToUpper().Trim(), // Convert.ToDateTime(dateMeasured), discharge, // gh, shift, "Shift Entered by USFOShifts Program", DateTime.Now); //enter shift to pisces db TimeSeriesName tn = new TimeSeriesName(cbtt.ToLower() + "_" + pcode.ToLower(), "instant"); TimeSeriesDatabaseDataSet.seriespropertiesDataTable.Set("shift", shift.ToString(), tn, svr); return; }
/// <summary> /// convert queries such as: list=boii ob, bigi gh /// into an array of time series names. /// /// </summary> /// <param name="query"></param> /// <param name="interval"></param> /// <param name="db"></param> /// <returns></returns> private static TimeSeriesName[] GetTimeSeriesName(NameValueCollection query, TimeInterval interval, TimeSeriesDatabase db) { var custom_list = HydrometWebUtility.GetParameter(query, "custom_list"); if (custom_list == "idwr" && interval == TimeInterval.Irregular) { return(IdwrCustom.GetIDWRInstantList(db.Server).ToArray()); } else if (custom_list.ToLower().IndexOf("wd") == 0 && interval == TimeInterval.Daily) { return(IdwrCustom.GetIDWRDailyList(db.Server, custom_list).ToArray()); } List <TimeSeriesName> rval = new List <TimeSeriesName>(); var listParameter = HydrometWebUtility.GetParameter(query, "list"); Logger.WriteLine("GetTimeSeriesName()"); Logger.WriteLine(query.ToString()); var siteCodePairs = listParameter.ToLower().Split(','); foreach (var item in siteCodePairs) { var tokens = item.Split(new char[] { ' ' }, StringSplitOptions.RemoveEmptyEntries); if (tokens.Length == 2) { TimeSeriesName tn = new TimeSeriesName(tokens[0] + "_" + tokens[1], interval); rval.Add(tn); } else if (tokens.Length == 1) {//just the site return a list of all parameters var parms = db.GetParameters(tokens[0].Trim(), interval, false); for (int i = 0; i < parms.Length; i++) { TimeSeriesName tn = new TimeSeriesName(tokens[0] + "_" + parms[i], interval); rval.Add(tn); } } } return(rval.ToArray()); }
private void Init(string cbtt, string pcode, HydrometHost server) { this.server = server; this.cbtt = cbtt; this.pcode = pcode; this.Table.TableName = TimeSeriesName.GetTableName(server.ToString(), TimeSeries.TimeInterval.Irregular, cbtt, pcode); Parameter = pcode; Units = HydrometInfoUtility.LookupDayfileUnits(pcode); Name = HydrometInfoUtility.LookupSiteDescription(cbtt) + " " + Units; Source = "Hydromet"; Provider = "HydrometInstantSeries"; this.SiteID = HydrometInfoUtility.LookupSiteDescription(cbtt); this.TimeInterval = TimeSeries.TimeInterval.Irregular; ReadOnly = true; this.Appearance.LegendText = cbtt + " " + pcode; ConnectionString = "server=" + server + ";cbtt=" + cbtt + ";pcode=" + pcode;// +";LastUpdate=" + DateTime.Now.ToString(DateTimeFormat_Instantaneous); }
private static TimeSeriesName[] GetTimeSeriesName(NameValueCollection query) { List <TimeSeriesName> rval = new List <TimeSeriesName>(); var sites = WebUtility.GetParameter(query, "parameter"); var siteCodePairs = sites.Split(','); foreach (var item in siteCodePairs) { var tokens = item.Split(' '); if (tokens.Length == 2) { TimeSeriesName tn = new TimeSeriesName(tokens[0] + "_" + tokens[1], TimeInterval.Irregular); rval.Add(tn); } } return(rval.ToArray()); }
public static IEnumerable<String> GetBlockOfQueries(TimeSeriesDatabase db, TimeInterval interval, string filter,string propertyFilter="", int blockSize=75, bool ignoreQuality=true) { var rval = new List<string>(); foreach (Series s in db.GetSeries(interval, filter,propertyFilter).ToArray()) { TimeSeriesName tn = new TimeSeriesName(s.Table.TableName); //rval.Add(s.SiteID + " " + s.Parameter); if (Array.IndexOf(s_quality_parameters, tn.pcode.ToLower() ) >=0 ) continue; // skip quality parameters rval.Add(tn.siteid + " " + tn.pcode); if (rval.Count >= blockSize) { yield return String.Join(",",rval.ToArray()); rval.Clear(); } } yield return String.Join(",", rval.ToArray()); }
public override void WriteSeriesHeader(SeriesList list) { WriteLine("<table border=1>"); if (m_printHeader) { WriteLine("<tr>"); WriteLine("<th>DateTime</th>"); for (int i = 0; i < list.Count; i++) { TimeSeriesName tn = new TimeSeriesName(list[i].Table.TableName); WriteLine("<th>" + tn.siteid + "_" + tn.pcode + "</th>"); if (PrintFlags) { WriteLine("<th>flag</th>"); } } WriteLine("</tr>"); } }
public HydrometMonthlySeries(string cbtt, string pcode, HydrometHost server = HydrometHost.PNLinux) { this.TimeInterval = TimeSeries.TimeInterval.Monthly; this.Units = ""; ReadOnly = true; this.m_cbtt = cbtt; this.m_pcode = pcode; this.Table.TableName = TimeSeriesName.GetTableName(server.ToString(), TimeSeries.TimeInterval.Monthly, cbtt, pcode); this.Name = FindName(cbtt); this.Parameter = pcode; this.HasFlags = true; this.Units = LookupUnits(pcode); this.Appearance.LegendText = cbtt + " " + pcode; this.server = server; Provider = "HydrometMonthlySeries"; ConnectionString = "server=" + server.ToString() + ";cbtt=" + cbtt + ";pcode=" + pcode + ";LastUpdate=" + DateTime.Now.ToString(DateTimeFormatInstantaneous); this.Table.TableName = "monthly_" + cbtt + "_" + pcode; }
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 static void Set(string name, string value, TimeSeriesName tn, BasicDBServer svr) { var tableName = tn.GetTableName(); var sc = svr.Table("seriescatalog", "select * from seriescatalog where tablename = '" + tableName + "'"); if (sc.Rows.Count == 1) { int id = Convert.ToInt32(sc.Rows[0]["id"]); Set(name, value, id, svr); } else { var msg = "Error: tablename:" + tableName + "not found (or duplicated) in the seriescatalog"; Logger.WriteLine(msg); throw new KeyNotFoundException(msg); } }
public override void WriteSeriesHeader(SeriesList list) { WriteLine("<table border=1>"); if (m_printHeader) { WriteLine("<tr>"); WriteLine("<th>DateTime</th>"); for (int i = 0; i < list.Count; i++) { TimeSeriesName tn = new TimeSeriesName(list[i].Table.TableName); WriteLine("<th>" + tn.siteid + "_" + tn.pcode + "</th>"); if( PrintFlags ) WriteLine("<th>flag</th>"); } WriteLine("</tr>"); } }
/// <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 event EventHandler<SeriesEventArgs> AfterSave; //private void OnAfterSave(SeriesEventArgs e) //{ // EventHandler<SeriesEventArgs> handler = AfterSave; // if (handler != null) // handler(this, e); //} /// <summary> /// Import Series s into the database. /// Series s is saved to the tablename defined in the Series s.DataTable.TableName /// the table will be created if necessary. /// </summary> public void ImportSeriesUsingTableName(Series s, string[] folderNames ,DatabaseSaveOptions saveOption = DatabaseSaveOptions.UpdateExisting) { Logger.WriteLine("ImportSeriesUsingTableName: '" + s.Table.TableName+"'"); FixInvalidTableName(s); var sr = GetSeriesRow("tablename ='" + s.Table.TableName.ToLower() +"'"); if (sr == null) {// create new series. //sr = GetNewSeriesRow(); Logger.WriteLine("table: " + s.Table.TableName + " does not exist in the catalog"); TimeSeriesName tn = new TimeSeriesName(s.Table.TableName); PiscesFolder folder = RootFolder; if (folderNames.Length == 0 && tn.interval != "") { folder = GetOrCreateFolder(new string[] { tn.interval }); } else { folder = GetOrCreateFolder(RootFolder,folderNames); } sr = GetNewSeriesRow(); sr.TimeInterval = tn.GetTimeInterval().ToString(); sr.siteid = tn.siteid; sr.Name = s.Name; sr.Parameter = s.Parameter; sr.ParentID = folder.ID; Logger.WriteLine("Info: ImportSeriesUsingTableName() series: " + s.Name + " tablename=" + s.Table.TableName); sr.TableName = s.Table.TableName; m_server.SaveTable(sr.Table); } ImportTimeSeriesTable(s.Table, sr, saveOption); // OnAfterSave(new SeriesEventArgs(s)); }
private static string[] GetParametersFromPostgres(string cbtt, TimeInterval interval) { var rval = new List<string>(); var svr = PostgreSQL.GetPostgresServer(); TimeSeriesDatabase p = new TimeSeriesDatabase(svr,false); var sql = " lower(siteid) = '"+ svr.SafeSqlLiteral(cbtt.ToLower())+"' and TimeInterval = '"+interval.ToString()+"'"; var sc = p.GetSeriesCatalog(sql); foreach (var item in sc) { TimeSeriesName tn = new TimeSeriesName(item.TableName); rval.Add(tn.pcode); } return rval.ToArray(); }
/// <summary> /// Create a Series List /// </summary> /// <param name="input"></param> input from the command line /// <param name="interval"></param> time interval /// <returns></returns> private SeriesList CreateSeriesList(CommandLineInput input, TimeInterval interval) { List<TimeSeriesName> names = new List<TimeSeriesName>(); foreach (var cbtt in input.SiteList) { foreach (var pcode in input.Parameters) { string sInterval = TimeSeriesName.GetTimeIntervalForTableName(interval); TimeSeriesName tn = new TimeSeriesName(cbtt + "_" + pcode,sInterval); names.Add(tn); } } var tableNames = (from n in names select n.GetTableName()).ToArray(); var sc = m_db.GetSeriesCatalog("tablename in ('" + String.Join("','", tableNames) + "')"); SeriesList sList = new SeriesList(); foreach (var tn in names) { Series s = new Series(); s.TimeInterval = interval; if (sc.Select("tablename = '" + tn.GetTableName() + "'").Length == 1) { s = m_db.GetSeriesFromTableName(tn.GetTableName()); } s.Table.TableName = tn.GetTableName(); sList.Add(s); } return sList; }
private static void SaveTableToSeries(TimeSeriesDatabase db, DataTable table, TimeInterval interval) { int i = 1; string tablePrefix="daily"; if (interval == TimeInterval.Irregular) tablePrefix = "instant"; if (interval == TimeInterval.Monthly) tablePrefix = "monthly"; while( i < table.Columns.Count ) { string tn = table.Columns[i].ColumnName.Trim().ToLower(); tn = tn.Replace(" ", "_"); TimeSeriesName tsn = new TimeSeriesName(tn,interval.ToString().ToLower()); var series1 = db.GetSeriesFromTableName(tn, tablePrefix); Console.Write(tn+ " "); for (int r = 0; r < table.Rows.Count; r++) { var row = table.Rows[r]; object o = row[i]; double val = Point.MissingValueFlag; if (o != DBNull.Value) val = Convert.ToDouble(row[i]); else { continue; // mixing 5 and 15-minute data can cause gaps } string flag = "hmet-import"; if (interval == TimeInterval.Irregular|| interval == TimeInterval.Monthly) flag = row[i + 1].ToString(); DateTime t = Convert.ToDateTime(row[0]); if (interval == TimeInterval.Monthly) { //if( tsn.pcode.ToLower() == "fc" || tsn.pcode.ToLower() == "se" || tsn.pcode.ToLower() == "fcm") t = t.FirstOfMonth(); //if (val != Point.MissingValueFlag && HydrometMonthlySeries.LookupUnits(tsn.pcode) == "1000 acre-feet") // val = val * 1000; } var pt = new Point(t, val, flag); series1.Add(pt); } if (interval == TimeInterval.Irregular || interval == TimeInterval.Monthly) { i += 2;// flag column } else { i++; } int rc = series1.Count; if( rc>0) rc = db.SaveTimeSeriesTable(series1.ID, series1, DatabaseSaveOptions.UpdateExisting); Console.WriteLine(rc + " records saved "+POR(series1.Table)); } }
//private static void EnterShiftToPisces(string cbtt,string pcode,double shift) //{ // var svr = PostgreSQL.GetPostgresServer(); // TimeSeriesName tn = new TimeSeriesName(cbtt.ToLower()+"_"+pcode.ToLower(),"instant"); // TimeSeriesDatabaseDataSet.seriespropertiesDataTable.Set("shift", shift.ToString(), tn, svr); // return; //} private static void InsertShiftToPostgres(string cbtt, string pcode, double shift, string dateMeasured, double? discharge, double? gh) { //enter shift to shift db var ds = new HydrometDataSet(); ds.DataSetName = "hydromet"; ds.insertshift(cbtt.ToUpper().Trim(), pcode.ToUpper().Trim(), Convert.ToDateTime(dateMeasured), discharge, gh, shift, "Shift Entered by USFOShifts Program", DateTime.Now); //enter shift to pisces db var svr = PostgreSQL.GetPostgresServer("timeseries"); TimeSeriesName tn = new TimeSeriesName(cbtt.ToLower() + "_" + pcode.ToLower(), "instant"); TimeSeriesDatabaseDataSet.seriespropertiesDataTable.Set("shift", shift.ToString(), tn, svr); return; }
///// <summary> ///// Print DataTable composed of tablename,datetime,value[,flag] ///// with columns for each tablename ///// </summary> ///// <param name="list"></param> ///// <param name="table"></param> //private static void PrintDataTable(SeriesList list, DataTable table, // Formatter fmt, TimeInterval interval) //{ // var t0 = ""; // if (table.Rows.Count > 0) // t0 = fmt.FormatDate(table.Rows[0][1]); // var vals = new string[list.Count]; // var flags = new string[list.Count]; // var dict = new Dictionary<string, int>(); // for (int i = 0; i < list.Count; i++) // { // dict.Add(list[i].Table.TableName, i); // } // string t = ""; // bool printThisRow = false; // for (int i = 0; i < table.Rows.Count; i++) // { // var row = table.Rows[i]; // t = fmt.FormatDate(row[1]); // if (t != t0) // { // if (printThisRow) // fmt.PrintRow(t0, vals, flags); // vals = new string[list.Count]; // flags = new string[list.Count]; // t0 = t; // } // vals[dict[row[0].ToString()]] = fmt.FormatNumber(row[2]); // flags[dict[row[0].ToString()]] = fmt.FormatFlag(row[3]); // DateTime date = Convert.ToDateTime(row[1]); // bool topOfHour = date.Minute == 0; // printThisRow = fmt.HourlyOnly == false || (fmt.HourlyOnly && topOfHour); // } // if (printThisRow) // fmt.PrintRow(t, vals, flags); //} private static TimeSeriesName[] GetTimeSeriesName(NameValueCollection query, TimeInterval interval) { List<TimeSeriesName> rval = new List<TimeSeriesName>(); //add support for the auto generated parameter list //if the sites list is just the site return a list of all parameters in the table var sites = HydrometWebUtility.GetParameter(query, "list"); Logger.WriteLine("GetTimeSeriesName()"); Logger.WriteLine(query.ToString()); var siteCodePairs = sites.Split(','); foreach (var item in siteCodePairs) { var tokens = item.Split(new char[] { ' ' }, StringSplitOptions.RemoveEmptyEntries); if (tokens.Length == 2) { TimeSeriesName tn = new TimeSeriesName(tokens[0] + "_" + tokens[1], interval); rval.Add(tn); } } return rval.ToArray(); }