Ejemplo n.º 1
0
        public DataTable GetTableForExport(long seriesID, double?noDataValue = null, string dateColumn = null, DateTime?firstDate = null, DateTime?lastDate = null)
        {
            var sql =
                "SELECT ds.SeriesID, s.SiteName, v.VariableName, dv.LocalDateTime, dv.DataValue, U1.UnitsName As VarUnits, v.DataType, s.SiteID, s.SiteCode, v.VariableID, v.VariableCode, " +
                "S.Organization, S.SourceDescription, S.SourceLink, S.Citation, v.ValueType, v.TimeSupport, U2.UnitsName As TimeUnits, v.IsRegular, v.NoDataValue, " +
                "dv.UTCOffset, dv.DateTimeUTC, s.Latitude, s.Longitude, dv.ValueAccuracy, dv.CensorCode, m.MethodDescription, q.QualityControlLevelCode, v.SampleMedium, v.GeneralCategory, " +
                "OffsetValue, OT.OffsetDescription, Un.UnitsAbbreviation as OffsetUnits, QL.QualifierCode " +
                "FROM DataSeries ds, Sites s, Variables v, DataValues dv, Units U1, Units U2, Methods m, QualityControlLevels q, Sources S " +
                "LEFT JOIN OffsetTypes OT on DV.OffsetTypeId = OT.OffsetTypeId " +
                "LEFT JOIN Units Un on Un.UnitsID = OT.OffsetUnitsId " +
                "LEFT JOIN Qualifiers QL on QL.QualifierID = dv.QualifierID " +
                "WHERE v.VariableID = ds.VariableID " +
                "AND s.SiteID = ds.SiteID " +
                "AND m.MethodID = ds.MethodID " +
                "AND q.QualityControlLevelID = ds.QualityControlLevelID " +
                "AND S.SourceID = ds.SourceID " +
                "AND dv.SeriesID = ds.SeriesID " +
                "AND U1.UnitsID = v.VariableUnitsID " +
                "AND U2.UnitsID = v.TimeUnitsID " +
                "AND ds.SeriesID = " + seriesID;

            if (noDataValue.HasValue)
            {
                sql += " AND dv.DataValue != " + noDataValue;
            }

            var cmd = DbOperations.CreateCommand(sql);

            // Append date range filter
            if (!string.IsNullOrEmpty(dateColumn) &&
                firstDate.HasValue && lastDate.HasValue)
            {
                cmd.CommandText += string.Format(" AND ({0} >=  @p1 and {0} <=  @p2)", dateColumn);
                var startDateParameter = DbOperations.AddParameter(cmd, "@p1", DbType.DateTime);
                var endDateParemater   = DbOperations.AddParameter(cmd, "@p2", DbType.DateTime);

                startDateParameter.Value = firstDate.Value;
                endDateParemater.Value   = lastDate.Value;
            }

            var tbl = DbOperations.LoadTable("values", cmd);

            return(tbl);
        }