//private System.Data.IDbConnection db = HdbApi.App_Code.DbConnect.Connect();
public SeriesModel.TimeSeries GetSeries(IDbConnection db, string id, string tstep, DateTime startDate, DateTime endDate, string sourceTable = "R", int mrid = 0, bool useRbase = false, string instantMinutes = "60")
{
if (db != null)
{
// RESOLVE HDB CONNECTION
Regex regex = new Regex(@"Data Source=([^;]*);");
Match match = regex.Match(db.ConnectionString);
// GET QUERY VARS
var tsQuery = new Models.SeriesModel.TimeSeriesQuery
{
hdb = match.Groups[1].Value.ToString().ToUpper(),
sdi = id,
t1 = startDate,
t2 = endDate,
interval = tstep.ToUpper(),
table = sourceTable.ToUpper(),
mrid = mrid,
retrieved = DateTime.Now,
rbase = useRbase
};
string queryTable = "";
if (sourceTable == "R" && useRbase)
{
queryTable = string.Format("(SELECT SITE_DATATYPE_ID, START_DATE_TIME, VALUE FROM R_BASE WHERE START_DATE_TIME BETWEEN " +
"to_date('{0}','dd-mon-yyyy hh24:mi') AND to_date('{1}','dd-mon-yyyy hh24:mi') AND SITE_DATATYPE_ID " +
"IN ({2}) AND INTERVAL = '{3}')",
startDate.ToString("dd-MMM-yyyy HH:mm"), endDate.ToString("dd-MMM-yyyy HH:mm"), id, tstep.ToLower());
}
else
{
queryTable = string.Format("{0}_{1}", sourceTable.ToUpper(), tstep.ToUpper());
}
// GET TS DATA
//string sqlString = string.Format("SELECT START_DATE_TIME AS DATETIME, VALUE AS VALUE FROM {0}_{1} WHERE " +
// "SITE_DATATYPE_ID IN ({2}) AND START_DATE_TIME >= to_date('{3}','dd-mon-yyyy hh24:mi') AND " +
// "START_DATE_TIME <= to_date('{4}','dd-mon-yyyy hh24:mi')", sourceTable.ToUpper(), tstep.ToUpper(),
// id.ToString("F0"), startDate.ToString("dd-MMM-yyyy HH:mm"), endDate.ToString("dd-MMM-yyyy HH:mm"));
// Fills missing with NULL
string dateArrayFunction = "DATES_BETWEEN";
string dateArrayIdentifier = "'" + tstep.ToLower() + "'";
if (tstep.ToUpper() == "INSTANT")
{
dateArrayFunction = "INSTANTS_BETWEEN";
dateArrayIdentifier = instantMinutes;
}
string sqlString = string.Format("SELECT t.DATE_TIME AS DATETIME, CAST(NVL(VALUE,NULL) AS VARCHAR(10)) " +
"AS VALUE FROM " + queryTable + " v RIGHT OUTER JOIN TABLE(" + dateArrayFunction + "(to_date('{0}','dd-mon-yyyy hh24:mi'), " +
"to_date('{1}','dd-mon-yyyy hh24:mi')," + dateArrayIdentifier + ")) t ON v.START_DATE_TIME = t.DATE_TIME AND " +
"v.SITE_DATATYPE_ID IN ({2})", startDate.ToString("dd-MMM-yyyy HH:mm"), endDate.ToString("dd-MMM-yyyy HH:mm"), id);
if (sourceTable.ToUpper() == "M")
{
sqlString += string.Format(" AND MODEL_RUN_ID = {0}", mrid);
}
sqlString += " ORDER BY t.DATE_TIME";
List <SeriesModel.TimeSeriesPoint> tsData = (List <SeriesModel.TimeSeriesPoint>)db.Query <SeriesModel.TimeSeriesPoint>(sqlString);
// [JR] GET TS METADATA
var seriesMetaProcessor = new HdbApi.DataAccessLayer.SiteDataTypeRepository();
var tsMeta = seriesMetaProcessor.GetSiteDataTypeForSeries(db, id);
// BUILD OUTPUT
var ts = new Models.SeriesModel.TimeSeries
{
query = tsQuery,
metadata = tsMeta,
data = tsData
};
return(ts);
}
else
{
// GET QUERY VARS
var tsQuery = new Models.SeriesModel.TimeSeriesQuery
{
hdb = "hydromet",
sdi = id,
t1 = startDate,
t2 = endDate,
interval = tstep.ToUpper(),
table = "NA",
mrid = -1,
retrieved = DateTime.Now,
rbase = useRbase
};
// GET TS DATA
var tsTable = App_Code.HydrometCommands.GetTsDataTable(tstep, id, startDate, endDate);
List <SeriesModel.TimeSeriesPoint> tsData = new List <SeriesModel.TimeSeriesPoint>();
foreach (DataRow row in tsTable.Rows)
{
tsData.Add(new SeriesModel.TimeSeriesPoint()
{
datetime = Convert.ToDateTime(row[0]), value = row[1].ToString()
});
}
// [JR] GET TS METADATA
var seriesMetaProcessor = new HdbApi.DataAccessLayer.SiteDataTypeRepository();
var tsMeta = seriesMetaProcessor.GetSiteDataTypeForSeries(null, id);
// BUILD OUTPUT
var ts = new Models.SeriesModel.TimeSeries
{
query = tsQuery,
metadata = tsMeta,
data = tsData
};
return(ts);
}
}
public object GetExamples()
{
var tsQuery = new Models.SeriesModel.TimeSeriesQuery
{
hdb = "lchdb2",
sdi = "1980",
t1 = new DateTime(2000, 1, 1, 0, 0, 0),
t2 = new DateTime(2000, 1, 2, 0, 0, 0),
interval = "day",
table = "r",
mrid = 0
};
var tsMeta = new Models.SiteDatatypeModel.SiteDataTypeMetadata
{
site_metadata = new Models.SiteModel.HdbSite
{
site_id = "919",
site_name = "LAKE POWELL",
site_common_name = "LAKE POWELL",
description = DBNull.Value.ToString(),
elevation = "3700",
lat = "37.05778",
longi = "-111.30332",
db_site_code = "UC",
objecttype_id = 7,
objecttype_name = "reservoir",
basin_id = 2029,
hydrologic_unit = null,
river_mile = float.NaN,
segment_no = 0,
state_id = 3,
state_code = "UT",
usgs_id = DBNull.Value.ToString(),
nws_code = DBNull.Value.ToString(),
shef_code = DBNull.Value.ToString(),
scs_id = DBNull.Value.ToString(),
parent_objecttype_id = 0,
parent_site_id = 0
},
datatype_metadata = new Models.DatatypeModel.HdbDatatype
{
datatype_id = "1393",
datatype_name = "average reservoir elevation",
datatype_common_name = "ave reservoir elevation",
physical_quantity_name = "water surface elevation",
unit_id = 4,
unit_name = "feet",
unit_common_name = "feet",
allowable_intervals = "non-instant",
agen_id = 0,
cmmnt = ""
}
};
var tsPoint1 = new Models.SeriesModel.TimeSeriesPoint
{
datetime = new DateTime(2000, 1, 1, 0, 0, 0),
value = 3.1416.ToString(),
flag = ""
};
var tsPoint2 = new Models.SeriesModel.TimeSeriesPoint
{
datetime = new DateTime(2000, 1, 2, 0, 0, 0),
value = 2.7183.ToString(),
flag = ""
};
var tsData = new List <Models.SeriesModel.TimeSeriesPoint>
{
tsPoint1, tsPoint2
};
var ts = new Models.SeriesModel.TimeSeries
{
query = tsQuery,
metadata = tsMeta,
data = tsData
};
return(ts);
}
