public void UpdateMethod(int methodID, string methodDescription, string methodLink)
{
DbOperations.ExecuteNonQuery(
string.Format("UPDATE Methods SET MethodDescription='{0}', MethodLink='{1}' Where MethodID = {2}",
methodDescription, methodLink, methodID)
);
}
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);
}
/// <summary>
/// Deletes a theme and all its series as long as the series don't belong to any other theme.
/// </summary>
/// <param name="themeID">The Theme ID</param>
/// <returns>true if successful, false otherwise</returns>
public bool DeleteTheme(int themeID)
{
string sqlTheme = "SELECT SeriesID FROM DataThemes where ThemeID = " + themeID;
DataTable tblSeries = DbOperations.LoadTable("tblSeries", sqlTheme);
foreach (DataRow seriesRow in tblSeries.Rows)
{
int seriesID = Convert.ToInt32(seriesRow["SeriesID"]);
var seriesRepository = RepositoryFactory.Instance.Get <IDataSeriesRepository>();
seriesRepository.DeleteSeries(seriesID);
}
//delete the actual theme
string sqlDeleteTheme = "DELETE FROM DataThemeDescriptions WHERE ThemeID = " + themeID;
try
{
DbOperations.ExecuteNonQuery(sqlDeleteTheme);
}
catch { };
//re-check the number of series in the theme
return(true);
}
public void Update(Qualifier entity)
{
var query = "UPDATE Qualifiers SET QualifierCode = ?, QualifierDescription = ? WHERE QualifierID = " +
entity.Id;
DbOperations.ExecuteNonQuery(query, new object[] { entity.Code, entity.Description });
}
public void Update(QualityControlLevel entity)
{
const string query = "UPDATE QualityControlLevels SET QualityControlLevelCode=?, Definition=?, Explanation=? WHERE QualityControlLevelID = ?";
DbOperations.ExecuteNonQuery(query, new object[]
{
entity.Code, entity.Definition, entity.Explanation,
entity.Id
});
}
public long InsertMethod(string methodDescription, string methodLink)
{
var methodID = DbOperations.GetNextID(TableName, "MethodID");
DbOperations.ExecuteNonQuery(
string.Format(
"INSERT INTO Methods(MethodID, MethodDescription, MethodLink) VALUES ({0}, '{1}', '{2}')", methodID,
methodDescription, methodLink));
return(methodID);
}
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);
}
/// <summary>
/// Delete a theme - a background worker and progress bar is used
/// </summary>
/// <param name="themeID">The themeID (this needs to be a valid ID)</param>
/// <param name="worker">The background worker component</param>
/// <param name="e">The arguments for background worker</param>
/// <returns></returns>
public bool DeleteTheme(int themeID, BackgroundWorker worker, DoWorkEventArgs e)
{
string sqlTheme = "SELECT SeriesID FROM DataThemes where ThemeID = " + themeID;
DataTable tblSeries = DbOperations.LoadTable("tblSeries", sqlTheme);
int numSeries = tblSeries.Rows.Count;
int count = 0;
if (numSeries == 0)
{
return(false);
}
foreach (DataRow seriesRow in tblSeries.Rows)
{
if (worker != null)
{
//check cancellation
if (e != null && worker.CancellationPending)
{
e.Cancel = true;
return(false);
}
}
int seriesID = Convert.ToInt32(seriesRow["SeriesID"]);
var seriesRepository = RepositoryFactory.Instance.Get <IDataSeriesRepository>();
seriesRepository.DeleteSeries(seriesID);
//progress report
count++;
if (worker != null && worker.WorkerReportsProgress)
{
var percent = (int)(((float)count / (float)numSeries) * 100);
var userState = "Deleting series " + count + " of " + numSeries + "...";
worker.ReportProgress(percent, userState);
}
}
//delete the actual theme
string sqlDeleteTheme = "DELETE FROM DataThemeDescriptions WHERE ThemeID = " + themeID;
try
{
DbOperations.ExecuteNonQuery(sqlDeleteTheme);
e.Result = "Theme deleted successfully";
}
catch { };
return(true);
}
public void AddNew(SeriesProvenance entity)
{
entity.Id = DbOperations.GetNextID("SeriesProvenance", "ProvenanceID");
const string query = "INSERT INTO SeriesProvenance(ProvenanceID, ProvenanceDateTime, InputSeriesID, OutputSeriesID, MethodID, Comment) VALUES (?, ?,?,?,?,?)";
DbOperations.ExecuteNonQuery(query, new object[]
{
entity.Id,
entity.ProvenanceDateTime,
entity.InputSeries.Id,
entity.OutputSeries.Id,
entity.Method.Id,
entity.Comment
});
}
public int InsertNewSeries(long sourceSeriesID, long variableID, long methodID, long qualityControlLevelID)
{
var newSeriesID = DbOperations.GetNextID("DataSeries", "SeriesID");
var dt = DbOperations.LoadTable("DataSeries", "SELECT * FROM DataSeries WHERE SeriesID = " + sourceSeriesID);
var row = dt.Rows[0];
//Making the INSERT SQL string for the new data series
var sqlString = new StringBuilder();
sqlString.Append("INSERT INTO DataSeries(SeriesID, SiteID, VariableID, IsCategorical, MethodID, SourceID, ");
sqlString.Append("QualityControlLevelID, BeginDateTime, EndDateTime, BeginDateTimeUTC, EndDateTimeUTC, ");
sqlString.Append("ValueCount, CreationDateTime, Subscribed, UpdateDateTime, LastcheckedDateTime) Values (");
//SeriesID value
sqlString.Append(newSeriesID + ", ");
//SiteID value
sqlString.Append(Convert.ToInt64(row[1]) + ", ");
//VariableID values
sqlString.Append(variableID + ", ");
//IsCategorical value
sqlString.Append(row[3].ToString() == "True" ? "1, " : "0, ");
//MethodID value
sqlString.Append(methodID + ", ");
//SourceID value
sqlString.Append(Convert.ToInt64(row[5]) + ", ");
//QualityControlLevelID value
sqlString.Append(qualityControlLevelID + ", ");
//BeginDateTime, EndDateTime, BeginDateTimeUTC and EndDateTimeUTC values
for (int i = 7; i <= 10; i++)
{
var tempstring = Convert.ToDateTime(row[i]).ToString("yyyy-MM-dd HH:mm:ss");
sqlString.Append("'" + tempstring + "', ");
}
var todaystring = DateTime.Today.ToString("yyyy-MM-dd HH:mm:ss");
//ValueCount, CreationDateTime, Subscribed, UpdateDateTime and LastcheckedDateTime values
sqlString.Append(row[11] + ", '" + todaystring + "', 0, '" + todaystring + "','" + todaystring +
"')");
//Execute the SQL string
DbOperations.ExecuteNonQuery(sqlString.ToString());
return(newSeriesID);
}
public void Update(Variable variable)
{
var query =
string.Format(
@"UPDATE {0} SET VariableCode=?, VariableName=?, Speciation=?, SampleMedium=?, ValueType=?, IsRegular=?, IsCategorical=?, TimeSupport=?, DataType=?, GeneralCategory=?, NoDataValue=?, TimeUnitsID=?, VariableUnitsID=?
WHERE VariableID=?",
TableName);
DbOperations.ExecuteNonQuery(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.Id,
variable.VariableUnit.Id, variable.Id
});
}
/// <summary>
/// Delete a theme - a background worker and progress bar is used
/// </summary>
/// <param name="themeID">The themeID (this needs to be a valid ID)</param>
/// <param name="worker">The background worker component</param>
/// <returns>True - on success, otherwise false.</returns>
public bool DeleteTheme(long themeID, BackgroundWorker worker = null)
{
var sqlTheme = "SELECT SeriesID FROM DataThemes where ThemeID = " + themeID;
var sqlDeleteTheme = "DELETE FROM DataThemeDescriptions WHERE ThemeID = " + themeID;
var sqlDeleteTheme2 = "DELETE FROM DataThemes WHERE ThemeID = " + themeID;
var tblSeries = DbOperations.LoadTable("tblSeries", sqlTheme);
var seriesRepository = RepositoryFactory.Instance.Get <IDataSeriesRepository>();
for (var i = 0; i < tblSeries.Rows.Count; i++)
{
// Check cancellation
if (worker != null && worker.CancellationPending)
{
return(false);
}
var seriesRow = tblSeries.Rows[i];
var seriesID = Convert.ToInt32(seriesRow["SeriesID"]);
seriesRepository.DeleteSeries(seriesID, themeID);
// Progress report
if (worker != null && worker.WorkerReportsProgress)
{
var percent = (int)(((i + 1) / (float)tblSeries.Rows.Count) * 100);
var userState = "Deleting series " + (i + 1) + " of " + tblSeries.Rows.Count + "...";
worker.ReportProgress(percent, userState);
}
}
// Delete the actual theme
DbOperations.ExecuteNonQuery(sqlDeleteTheme2);
DbOperations.ExecuteNonQuery(sqlDeleteTheme);
return(true);
}
public void DeriveInsertDataValues(double A, double B, double C, double D, double E, double F,
DataTable dt,
long newSeriesID, long sourceSeriesID, bool isAlgebraic, IProgressHandler progressHandler)
{
const int chunkLength = 400;
var nodatavalue = GetNoDataValueForSeriesVariable(newSeriesID);
const string insertQuery =
"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 index = 0;
while (index != dt.Rows.Count - 1)
{
//Save values by chunks
var newValueID = DbOperations.GetNextID("DataValues", "ValueID");
var query = new StringBuilder("BEGIN TRANSACTION; ");
for (int i = 0; i < chunkLength; i++)
{
// Calculating value
double newvalue = 0.0;
if (isAlgebraic)
{
var currentvalue = Convert.ToDouble(dt.Rows[index]["DataValue"]);
if (currentvalue != nodatavalue)
{
//NOTE:Equation = Fx ^ 5 + Ex ^ 4 + Dx ^ 3 + Cx ^ 2 + Bx + A
newvalue = (F * (Math.Pow(currentvalue, 5))) + (E * (Math.Pow(currentvalue, 4))) +
(D * (Math.Pow(currentvalue, 3))) + (C * (Math.Pow(currentvalue, 2))) +
(B * currentvalue) +
A;
newvalue = Math.Round(newvalue, 5);
}
else
{
newvalue = nodatavalue;
}
}
else
{
newvalue = Convert.ToDouble(dt.Rows[index]["DataValue"]);
}
var row = dt.Rows[index];
query.AppendFormat(insertQuery,
newValueID + i,
newSeriesID,
newvalue,
row["ValueAccuracy"].ToString() == "" ? "NULL" : row["ValueAccuracy"].ToString(),
Convert.ToDateTime(row["LocalDateTime"]).ToString("yyyy-MM-dd HH:mm:ss"),
row["UTCOffset"].ToString(),
Convert.ToDateTime(row["DateTimeUTC"]).ToString("yyyy-MM-dd HH:mm:ss"),
row["OffsetValue"].ToString() == "" ? "NULL" : row["OffsetValue"].ToString(),
row["OffsetTypeID"].ToString() == "" ? "NULL" : row["OffsetTypeID"].ToString(),
row["CensorCode"].ToString(),
row["QualifierID"].ToString() == "" ? "NULL" : row["QualifierID"].ToString(),
row["SampleID"].ToString() == "" ? "NULL" : row["SampleID"].ToString(),
row["FileID"].ToString() == "" ? "NULL" : row["FileID"].ToString());
query.AppendLine();
if (index == dt.Rows.Count - 1)
{
break;
}
index = index + 1;
}
query.AppendLine("COMMIT;");
DbOperations.ExecuteNonQuery(query.ToString());
progressHandler.ReportProgress(index, null);
}
}
public void DeriveInsertAggregateDataValues(DataTable dt,
long newSeriesID,
DateTime currentdate, DateTime lastdate, DeriveAggregateMode mode,
DeriveComputeMode computeMode,
double nodatavalue, IProgressHandler progressHandler)
{
const string insertQuery =
"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});";
const int chunkLength = 400;
var index = 0;
while (currentdate <= lastdate)
{
// Save values by chunks
var newValueID = DbOperations.GetNextID("DataValues", "ValueID");
var query = new StringBuilder("BEGIN TRANSACTION; ");
for (int i = 0; i <= chunkLength - 1; i++)
{
double newvalue = 0.0;
string sqlString = string.Empty;
double UTC = 0.0;
switch (mode)
{
case DeriveAggregateMode.Daily:
sqlString = "LocalDateTime >= '" + currentdate.ToString(CultureInfo.InvariantCulture) + "' AND LocalDateTime <= '" +
currentdate.AddDays(1).AddMilliseconds(-1).ToString(CultureInfo.InvariantCulture) + "' AND DataValue <> " +
nodatavalue.ToString(CultureInfo.InvariantCulture);
break;
case DeriveAggregateMode.Monthly:
sqlString = "LocalDateTime >= '" + currentdate.ToString(CultureInfo.InvariantCulture) + "' AND LocalDateTime <= '" +
currentdate.AddMonths(1).AddMilliseconds(-1).ToString(CultureInfo.InvariantCulture) + "' AND DataValue <> " +
nodatavalue.ToString(CultureInfo.InvariantCulture);
break;
case DeriveAggregateMode.Quarterly:
sqlString = "LocalDateTime >= '" + currentdate.ToString(CultureInfo.InvariantCulture) +
"' AND LocalDateTime <= '" +
currentdate.AddMonths(3).AddMilliseconds(-1).ToString(
CultureInfo.InvariantCulture) + "' AND DataValue <> " +
nodatavalue.ToString(CultureInfo.InvariantCulture);
break;
}
try
{
switch (computeMode)
{
case DeriveComputeMode.Maximum:
newvalue = Convert.ToDouble(dt.Compute("Max(DataValue)", sqlString));
break;
case DeriveComputeMode.Minimum:
newvalue = Convert.ToDouble(dt.Compute("MIN(DataValue)", sqlString));
break;
case DeriveComputeMode.Average:
newvalue = Convert.ToDouble(dt.Compute("AVG(DataValue)", sqlString));
break;
case DeriveComputeMode.Sum:
newvalue = Convert.ToDouble(dt.Compute("Sum(DataValue)", sqlString));
break;
}
UTC = Convert.ToDouble(dt.Compute("AVG(UTCOffset)", sqlString));
}
catch (Exception)
{
newvalue = nodatavalue;
}
query.AppendFormat(insertQuery,
newValueID + i,
newSeriesID,
newvalue,
0,
Convert.ToDateTime(dt.Rows[index]["LocalDateTime"]).ToString("yyyy-MM-dd HH:mm:ss"),
UTC.ToString(CultureInfo.InvariantCulture),
currentdate.AddHours(UTC).ToString("yyyy-MM-dd HH:mm:ss"),
"NULL",
"NULL",
"nc",
"NULL",
"NULL",
"NULL");
query.AppendLine();
switch (mode)
{
case DeriveAggregateMode.Daily:
currentdate = currentdate.AddDays(1);
break;
case DeriveAggregateMode.Monthly:
currentdate = currentdate.AddMonths(1);
break;
case DeriveAggregateMode.Quarterly:
currentdate = currentdate.AddMonths(3);
break;
}
if (currentdate > lastdate)
{
break;
}
index = index + 1;
//Report progress
progressHandler.ReportProgress(index - 1, null);
}
query.AppendLine("COMMIT;");
DbOperations.ExecuteNonQuery(query.ToString());
progressHandler.ReportProgress(index - 1, null);
}
}
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 void DeleteById(long valueID)
{
DbOperations.ExecuteNonQuery("DELETE FROM DataValues WHERE ValueID =" + valueID);
}