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