public int GetVariableID(int seriesID)
{
var result =
DbOperations.ExecuteSingleOutput("SELECT VariableID FROM DataSeries WHERE SeriesID = " + seriesID);
return(Convert.ToInt32(result));
}
public double CalculatePercAvailable(long seriesID, DateTime minDate, DateTime maxDate)
{
var query =
string.Format(
"select count(DataValue) from {0} main " +
"LEFT JOIN DataSeries ds ON ds.SeriesID = main.SeriesID " +
"LEFT JOIN Variables v ON v.VariableID = ds.VariableID " +
"WHERE DateTimeUTC >= '{1}' and DateTimeUTC <= '{2}' and main.SeriesID = {3} and DataValue <> v.NoDataValue ",
TableName,
minDate.ToString("yyyy-MM-dd HH:mm:ss", CultureInfo.InvariantCulture), maxDate.ToString("yyyy-MM-dd HH:mm:ss", CultureInfo.InvariantCulture),
seriesID);
var hasValues = Convert.ToDouble(DbOperations.ExecuteSingleOutput(query), CultureInfo.InvariantCulture);
query =
string.Format(
"select count(DataValue) from {0} main " +
"WHERE DateTimeUTC >= '{1}' and DateTimeUTC <= '{2}' and main.SeriesID = {3} ",
TableName,
minDate.ToString("yyyy-MM-dd HH:mm:ss", CultureInfo.InvariantCulture), maxDate.ToString("yyyy-MM-dd HH:mm:ss", CultureInfo.InvariantCulture),
seriesID);
var totalValues = Convert.ToDouble(DbOperations.ExecuteSingleOutput(query), CultureInfo.InvariantCulture);
var value = hasValues * 100.0 / totalValues;
return(value);
}
public Tuple <DateTime, DateTime> GetDateTimes(long seriesID)
{
var begin = Convert.ToDateTime(DbOperations.ExecuteSingleOutput("Select BeginDateTime FROM DataSeries WHERE SeriesID = " + seriesID));
var end = Convert.ToDateTime(DbOperations.ExecuteSingleOutput("Select EndDateTime FROM DataSeries WHERE SeriesID = " + seriesID));
return(new Tuple <DateTime, DateTime>(begin, end));
}
public double GetMinValue(long seriesID)
{
var query = "SELECT MIN(DataValue) FROM DataValues WHERE SeriesID = " + seriesID;
var res = DbOperations.ExecuteSingleOutput(query);
return(Convert.ToDouble(res));
}
public void UpdateDataSeriesFromDataValues(long seriesID)
{
var SQLstring = "SELECT LocalDateTime FROM DataValues WHERE SeriesID = " + seriesID +
" ORDER BY LocalDateTime ASC";
var BeginDateTime = Convert.ToDateTime(DbOperations.ExecuteSingleOutput(SQLstring),
CultureInfo.InvariantCulture);
SQLstring = "SELECT LocalDateTime FROM DataValues WHERE SeriesID = " + seriesID +
" ORDER BY LocalDateTime DESC";
var EndDateTime = Convert.ToDateTime(DbOperations.ExecuteSingleOutput(SQLstring),
CultureInfo.InvariantCulture);
SQLstring = "SELECT DateTimeUTC FROM DataValues WHERE SeriesID = " + seriesID +
" ORDER BY LocalDateTime ASC";
var BeginDateTimeUTC = Convert.ToDateTime(DbOperations.ExecuteSingleOutput(SQLstring),
CultureInfo.InvariantCulture);
SQLstring = "SELECT DateTimeUTC FROM DataValues WHERE SeriesID = " + seriesID +
" ORDER BY LocalDateTime DESC";
var EndDateTimeUTC = Convert.ToDateTime(DbOperations.ExecuteSingleOutput(SQLstring),
CultureInfo.InvariantCulture);
SQLstring = "SELECT COUNT(*) FROM DataValues WHERE SeriesID = " + seriesID;
var ValueCount = DbOperations.ExecuteSingleOutput(SQLstring);
SQLstring = "UPDATE DataSeries SET BeginDateTime = '" + BeginDateTime.ToString("yyyy-MM-dd HH:mm:ss") + "', ";
SQLstring += "EndDateTime = '" + EndDateTime.ToString("yyyy-MM-dd HH:mm:ss") + "', ";
SQLstring += "BeginDateTimeUTC = '" + BeginDateTimeUTC.ToString("yyyy-MM-dd HH:mm:ss") + "', ";
SQLstring += "EndDateTimeUTC = '" + EndDateTimeUTC.ToString("yyyy-MM-dd HH:mm:ss") + "', ";
SQLstring += "ValueCount = " + ValueCount + " WHERE SeriesID = " + seriesID;
DbOperations.ExecuteNonQuery(SQLstring);
}
public long GetQualityControlLevelID(long seriesID)
{
var res =
DbOperations.ExecuteSingleOutput("SELECT QualityControlLevelID FROM DataSeries WHERE SeriesID = " +
seriesID);
return(Convert.ToInt64(res));
}
public bool ExistsSeries(Site site, Variable variable)
{
var query = string.Format("select count(*) from DataSeries where SiteID={0} and VariableID={1}", site.Id,
variable.Id);
var res = DbOperations.ExecuteSingleOutput(query);
return(Convert.ToInt64(res) > 0);
}
public bool Exists(object key)
{
var res =
DbOperations.ExecuteSingleOutput(
string.Format("select count(*) from {0} where {1}=?", TableName, PrimaryKeyName), key);
return(Convert.ToInt64(res) > 0);
}
public long GetCountForAllFieldsInSequence(IList <int> seriesIDs)
{
var whereClause = GetWhereClauseForIds(seriesIDs);
var countQuery = "select count(ValueID) from DataValues WHERE " + whereClause;
var res = DbOperations.ExecuteSingleOutput(countQuery);
return(Convert.ToInt64(res));
}
public double GetNoDataValueForSeriesVariable(long seriesID)
{
var query =
"SELECT NoDataValue FROM DataSeries LEFT JOIN Variables ON DataSeries.VariableID = Variables.VariableID WHERE SeriesID = " +
seriesID;
var result = DbOperations.ExecuteSingleOutput(query);
return(Convert.ToDouble(result));
}
public string GetQualityControlLevelCode(long seriesID)
{
var query =
"SELECT QualityControlLevelCode FROM DataSeries AS d LEFT JOIN QualityControlLevels AS q ON (d.QualityControlLevelID = q.QualityControlLevelID) WHERE SeriesID = " +
seriesID;
var res = DbOperations.ExecuteSingleOutput(query);
return(Convert.ToString(res));
}
public bool ExistsConversion(Unit unitA, Unit unitB)
{
var res = DbOperations.ExecuteSingleOutput
(
"select count(*) from UnitConversions where FromUnitsID = ? and ToUnitsID = ?",
new object[] { unitA.Id, unitB.Id }
);
return(Convert.ToInt64(res) > 0);
}
public int?GetMethodID(string methodDescription)
{
var res = DbOperations.ExecuteSingleOutput(string.Format("select MethodID from Methods where MethodDescription='{0}'", methodDescription));
if (res == null || res == DBNull.Value)
{
return(null);
}
return(Convert.ToInt32(res));
}
public long GetCountForJustValuesInParallel(IList <int> seriesIDs)
{
var whereClause = GetWhereClauseForIds(seriesIDs);
var countQuery =
string.Format("select count(*) from (select distinct LocalDateTime from DataValues where {0}) A",
whereClause);
var res = DbOperations.ExecuteSingleOutput(countQuery);
return(Convert.ToInt64(res));
}
public void InsertNewTheme(long seriesID, long newSeriesID)
{
var SQLstring = "SELECT ThemeID FROM DataThemes WHERE SeriesID = " + seriesID;
var ThemeID = Convert.ToInt64(DbOperations.ExecuteSingleOutput(SQLstring));
SQLstring = "INSERT INTO DataThemes(ThemeID, SeriesID) VALUES (";
SQLstring += ThemeID + "," + newSeriesID + ")";
DbOperations.ExecuteNonQuery(SQLstring);
}
public int?GetID(string themeName)
{
const string sql = "SELECT ThemeID from DataThemeDescriptions WHERE ThemeName =?";
var objThemeId = DbOperations.ExecuteSingleOutput(sql, themeName);
if (objThemeId == null || objThemeId == DBNull.Value)
{
return(null);
}
return(Convert.ToInt32(objThemeId));
}
public void AddQualifier(Qualifier entity)
{
var query = "INSERT INTO Qualifiers(QualifierCode, QualifierDescription) VALUES (?, ?)"
+ LastRowIDSelect;
var id = DbOperations.ExecuteSingleOutput(query, new object[]
{
entity.Code,
entity.Description
});
entity.Id = Convert.ToInt64(id);
}
public bool Exists(Site site)
{
if (site == null)
{
return(false);
}
const string query = "select count(*) from {0} where SiteID = {1} and SiteCode = '{2}'";
var result = DbOperations.ExecuteSingleOutput(string.Format(query, TableName, site.Id, site.Code));
return(Convert.ToInt32(result) > 0);
}
public DataTable GetThemesForAllSeries()
{
var dtThemes = DbOperations.LoadTable(TableName, "SELECT ThemeID, ThemeName from DataThemeDescriptions");
if (Int32.Parse(DbOperations.ExecuteSingleOutput("Select count(*) from DataSeries " +
"Where SeriesID not in (Select SeriesID from DataThemes)").
ToString()) > 0)
{
dtThemes.Rows.Add(DBNull.Value, OTHER_DATA_SERIES);
}
return(dtThemes);
}
public bool Exists(Variable entity)
{
if (entity == null)
{
return(false);
}
const string query = "select count(*) from {0} where VariableID = {1} and VariableCode = '{2}'";
var result = DbOperations.ExecuteSingleOutput(string.Format(query, TableName, entity.Id, entity.Code));
return(Convert.ToInt32(result) > 0);
}
public void AddNew(QualityControlLevel entity)
{
var query =
"INSERT INTO QualityControlLevels(QualityControlLevelCode, Definition, Explanation) VALUES (?, ?, ?)" +
LastRowIDSelect;
var id = DbOperations.ExecuteSingleOutput(query, new object[]
{
entity.Code, entity.Definition, entity.Explanation
});
entity.Id = Convert.ToInt64(id);
}
public void AddUnit(Unit unit)
{
var query = "INSERT INTO Units(UnitsAbbreviation, UnitsName, UnitsType)"
+ "VALUES (?, ?, ?)" + LastRowIDSelect;
var id = DbOperations.ExecuteSingleOutput(query,
new object[]
{
unit.Abbreviation,
unit.Name,
unit.UnitsType,
});
unit.Id = Convert.ToInt64(id);
}
public double?AggregateValues(long seriesID, string aggregateFunction, DateTime minDate, DateTime maxDate)
{
var query =
string.Format(
"select {0}(DataValue) from {1} main " +
"LEFT JOIN DataSeries ds ON ds.SeriesID = main.SeriesID " +
"LEFT JOIN Variables v ON v.VariableID = ds.VariableID " +
"WHERE DateTimeUTC >= '{2}' and DateTimeUTC <= '{3}' and main.SeriesID = {4} and DataValue <> v.NoDataValue ",
aggregateFunction, TableName,
minDate.ToString("yyyy-MM-dd HH:mm:ss"), maxDate.ToString("yyyy-MM-dd HH:mm:ss"),
seriesID);
var value = DbOperations.ExecuteSingleOutput(query);
return(value != DBNull.Value ? (double?)Convert.ToDouble(value, CultureInfo.InvariantCulture) : null);
}
public void AddSite(Site site)
{
var query = "INSERT INTO Sites(SiteCode, SiteName, Latitude, Longitude, Elevation_m, Comments, County, State, PosAccuracy_m, LocalX, LocalY, VerticalDatum, LatLongDatumID, LocalProjectionID)"
+ "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)" + LastRowIDSelect;
var id = DbOperations.ExecuteSingleOutput(query,
new object[]
{
site.Code, site.Name, site.Latitude, site.Longitude,
site.Elevation_m, site.Comments, site.County, site.State,
site.PosAccuracy_m, site.LocalX, site.LocalX,
site.VerticalDatum,
site.SpatialReference == null? 0 : site.SpatialReference.Id,
site.LocalProjection == null? 0 : site.LocalProjection.Id
});
site.Id = Convert.ToInt64(id);
}
public void AddVariable(Variable variable)
{
var query =
string.Format(
@"INSERT INTO {0}(VariableCode, VariableName, Speciation, SampleMedium, ValueType, IsRegular, IsCategorical, TimeSupport, DataType, GeneralCategory, NoDataValue, TimeUnitsID, VariableUnitsID)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
TableName) + LastRowIDSelect;
var res = DbOperations.ExecuteSingleOutput(query,
new object[]
{
variable.Code, variable.Name, variable.Speciation,
variable.SampleMedium,
variable.ValueType, Convert.ToInt32(variable.IsRegular),
Convert.ToInt32(variable.IsCategorical), variable.TimeSupport,
variable.DataType, variable.GeneralCategory, variable.NoDataValue,
variable.TimeUnit != null ? variable.TimeUnit.Id : 0,
variable.VariableUnit != null ? variable.VariableUnit.Id : 0
});
variable.Id = Convert.ToInt64(res);
}
public void UpdateValuesForEditView(DataTable table)
{
const string updateFormatString = "UPDATE DataValues SET DataValue = {0}, QualifierID = {1} WHERE ValueID = {2}; ";
const string insertFormatString = "INSERT INTO DataValues (ValueID,SeriesID,DataValue,ValueAccuracy,LocalDateTime,UTCOffset,DateTimeUTC, " +
"OffsetValue, OffsetTypeID, CensorCode, QualifierID, SampleID, FileID) VALUES (" +
"{0},{1},{2},'{3}','{4}',{5},'{6}',{7},{8},'{9}',{10},{11},{12}) ;";
var sqLstring2 = new StringBuilder();
sqLstring2.Append("BEGIN TRANSACTION; ");
var qualifierRepo = RepositoryFactory.Instance.Get <IQualifiersRepository>();
for (int i = 0; i < table.Rows.Count; i++)
{
var row = table.Rows[i];
var valueID = Convert.ToInt64(row["ValueID"]);
var other = Convert.ToInt64(row["Other"]);
if (other != 0)
{
//Deleteing point
if (other == -1)
{
sqLstring2.AppendFormat("DELETE FROM DataValues WHERE ValueID = {0}; ", valueID);
}
else if (other == 1)
{
if (
String.IsNullOrEmpty(
DbOperations.ExecuteSingleOutput(
"Select ValueID FROM DataValues WHERE ValueID = " + valueID).ToString()))
{
sqLstring2.AppendFormat(insertFormatString,
row[0],
row[1],
Convert.ToString(row[2], CultureInfo.InvariantCulture),
row[3] == DBNull.Value ? "NULL" : row[3],
Convert.ToDateTime(row[4]).ToString("yyyy-MM-dd HH:mm:ss",
CultureInfo.InvariantCulture),
row[5],
Convert.ToDateTime(row[6]).ToString("yyyy-MM-dd HH:mm:ss",
CultureInfo.InvariantCulture),
row[8] == DBNull.Value
? "NULL"
: Convert.ToString(row[8], CultureInfo.InvariantCulture),
row[9] == DBNull.Value ? "NULL" : row[9],
row[10] == DBNull.Value ? "NULL" : row[10],
row[7] == DBNull.Value
? "NULL"
: qualifierRepo.FindByCodeOrCreate(row[7].ToString()).Id.
ToString(CultureInfo.InvariantCulture),
row[11] == DBNull.Value ? "NULL" : row[11],
row[12] == DBNull.Value ? "NULL" : row[12]);
}
}
else if (other == 2)
{
sqLstring2.AppendFormat(updateFormatString,
Convert.ToString(row["DataValue"], CultureInfo.InvariantCulture),
qualifierRepo.FindByCodeOrCreate(row["QualifierCode"].ToString()).Id,
valueID);
}
}
}
if (!sqLstring2.ToString().TrimEnd().EndsWith(";"))
{
sqLstring2.Append(";");
}
sqLstring2.Append("COMMIT;");
DbOperations.ExecuteNonQuery(sqLstring2.ToString());
}
public bool Exists(string name)
{
var res = DbOperations.ExecuteSingleOutput("Select count(*) FROM Units where UnitsName=?", new object[] { name });
return(Convert.ToInt64(res) > 0);
}