///// <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();
}