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