/// <summary>
/// Create log entry in the log of updated observations.
/// </summary>
/// <param name="dataProvider">Log for which data provider.</param>
/// <param name="type">What type of log entry.</param>
/// <param name="description">Description of the log entry.</param>
/// <param name="observationId">What observation id is related to the log entry.</param>
public void UpdateLog(String dataProvider,
LogType type,
String description,
String observationId)
{
SqlCommandBuilder commandBuilder = new SqlCommandBuilder("UpdateUpdateLog");
if (dataProvider.IsNotEmpty())
{
commandBuilder.AddParameter(HarvestLog.DATAPROVIDER, dataProvider);
}
commandBuilder.AddParameter(HarvestLog.TYPE, type.ToString());
if (description.IsNotEmpty())
{
commandBuilder.AddParameter(HarvestLog.DESCRIPTION, description);
}
if (description.IsNotEmpty())
{
commandBuilder.AddParameter(HarvestLog.OBSERVATIONID, observationId);
}
ExecuteCommand(commandBuilder);
}
/// <summary>
/// Get DataReader with all observation ids that has been deleted during the period changedFrom - changedTo.
/// </summary>
/// <param name="changedFrom">Get observations ids that was deleted at or later than this date.</param>
/// <param name="changedTo">Get observations ids that was deleted at or before date.</param>
/// <returns>An open DataReader for reading.</returns>
public DataReader GetDeletedObservations(DateTime changedFrom, DateTime changedTo)
{
ArtDatabanken.Database.SqlCommandBuilder commandBuilder = new ArtDatabanken.Database.SqlCommandBuilder("GetDeletedObservations");
commandBuilder.AddParameter(Observationsdatabasen.CHANGED_FROM, changedFrom);
commandBuilder.AddParameter(Observationsdatabasen.CHANGED_TO, changedTo);
return(GetReader(commandBuilder));
}
/// <summary>
/// Get next higher species observation change id that is larger than current change id.
/// If no higher change id exists then currentChangeId is returned.
/// </summary>
/// <param name="currentChangeId">Current change id.</param>
/// <param name="nextIndexHarvestStart">
/// Start date of harvest.
/// This value is used to avoid handling old delete of species observations.
/// </param>
/// <returns>Next higher species observation change id that is larger than current change id.</returns>
public Int64 GetNextChangeId(Int64 currentChangeId,
DateTime?nextIndexHarvestStart)
{
Int64 nextChangeId;
SqlCommandBuilder commandBuilder = new SqlCommandBuilder("GetNextChangeId");
commandBuilder.AddParameter(SpeciesObservationChangeData.CURRENT_CHANGE_ID, currentChangeId);
if (nextIndexHarvestStart.HasValue)
{
commandBuilder.AddParameter(SpeciesObservationElasticsearchData.NEXT_INDEX_HARVEST_START, nextIndexHarvestStart.Value);
}
using (DataReader dataReader = GetReader(commandBuilder))
{
if (dataReader.Read())
{
nextChangeId = dataReader.GetInt64(SpeciesObservationChangeData.NEXT_CHANGE_ID);
}
else
{
throw new ApplicationException("Next species observation change id was not found.");
}
}
return(nextChangeId);
}
/// <summary>
/// Get length of a column in a database.
/// This method is mainly used to retrive information about
/// text columns but is works on any data type.
/// </summary>
/// <param name="tableName">Name of table to get information from.</param>
/// <param name="columnName">Name of column to get length of.</param>
/// <returns> The column length.</returns>
public Int32 GetColumnLength(String tableName,
String columnName)
{
Int32 columnLength;
SqlCommandBuilder commandBuilder;
String hashKey;
// Get length of specified column.
hashKey = "Table:" + tableName + "Column:" + columnName;
if (_columnLenghtTable.Contains(hashKey))
{
// Get cached value.
columnLength = (Int32)(_columnLenghtTable[hashKey]);
}
else
{
// Get value from database.
commandBuilder = new SqlCommandBuilder("GetColumnLength");
commandBuilder.AddParameter(ColumnLenghtData.TABLE_NAME, tableName);
commandBuilder.AddParameter(ColumnLenghtData.COLUMN_NAME, columnName);
columnLength = ExecuteScalar(commandBuilder);
// Add value to cache.
lock (_columnLenghtTable)
{
if (!(_columnLenghtTable.Contains(hashKey)))
{
_columnLenghtTable.Add(hashKey, columnLength);
}
}
}
return(columnLength);
}
/// <summary>
/// Set harvest job's data provider metadata.
/// Used by HarvestManager (UpdateSpeciesObservations).
/// </summary>
/// <param name="harvestJob">
/// The harvest Job.
/// </param>
/// <returns>
/// The result of the query.
/// </returns>
public int SetHarvestJobDataProvider(HarvestJob harvestJob)
{
if (harvestJob.IsNotNull())
{
int cumulativeResult = 1;
if (harvestJob.DataProviders.Any())
{
Debug.WriteLine(DateTime.Now.ToShortDateString() + " " + DateTime.Now.ToLongTimeString() + ": harvestJob(dataproviders), ");
foreach (var harvestJobDataProvider in harvestJob.DataProviders)
{
var commandBuilder = new SqlCommandBuilder("HarvestJobSetDataProvider");
commandBuilder.AddParameter(HarvestJobTableData.DATAPROVIDERID, harvestJobDataProvider.DataProviderId);
commandBuilder.AddParameter(HarvestJobTableData.CHANGEID, harvestJobDataProvider.ChangeId);
int result = ExecuteCommand(commandBuilder);
if (result < 1)
{
cumulativeResult = result;
}
}
}
return(cumulativeResult);
}
return(-1);
}
/// <summary>
/// Get regions that matches the search criteria.
/// </summary>
/// <param name="nameSearchString">Name search string.</param>
/// <param name="typeId">Id of type to search for.</param>
/// <param name="categoryIds">Category ids.</param>
/// <param name="countryIsoCodes">The country iso codes.</param>
/// <returns>
/// Returns an open DataReader. Remember to close the
/// DataReader after reading has been finished.
/// </returns>
public DataReader GetRegionsBySearchCriteria(String nameSearchString,
Int32?typeId,
List <Int32> categoryIds,
List <Int32> countryIsoCodes)
{
SqlCommandBuilder commandBuilder;
// Use SqlCommandBuilder with SqlParameter
commandBuilder = new SqlCommandBuilder("GetRegionsBySearchCriteria", true);
if (nameSearchString.IsNotEmpty())
{
commandBuilder.AddParameter(RegionData.NAME, nameSearchString, 1);
}
if (typeId.HasValue)
{
commandBuilder.AddParameter(RegionData.TYPE_ID, typeId.Value);
}
if (categoryIds.IsNotEmpty())
{
commandBuilder.AddParameter(RegionData.CATEGORY_IDS, categoryIds);
}
if (countryIsoCodes.IsNotEmpty())
{
commandBuilder.AddParameter(RegionData.COUNTRY_ISO_CODES, countryIsoCodes);
}
return(GetReader(commandBuilder));
}
/// <summary>
/// Get DataReader with all observation ids that has been deleted during the period changedFrom - changedTo.
/// </summary>
/// <param name="changedFrom">Get observations ids that was deleted at or later than this date.</param>
/// <param name="changedTo">Get observations ids that was deleted at or before date.</param>
/// <returns>An open DataReader for reading.</returns>
public DataReader GetDeletedObservations(DateTime changedFrom, DateTime changedTo)
{
SqlCommandBuilder commandBuilder = new SqlCommandBuilder("GetDeletedObservations");
commandBuilder.AddParameter(Artportalen.CHANGED_FROM, changedFrom);
commandBuilder.AddParameter(Artportalen.CHANGED_TO, changedTo);
return(GetReader(commandBuilder));
}
/// <summary>
/// Get DataReader with all taxa that matches the search criteria.
/// </summary>
/// <param name="changedFrom">Get observations registered at or later than this date.</param>
/// <param name="changedTo">Get observations registered before this date.</param>
/// <returns>DataReader after reading has been finished.</returns>
public DataReader GetSpeciesObservations(DateTime changedFrom, DateTime changedTo)
{
ArtDatabanken.Database.SqlCommandBuilder commandBuilder = new ArtDatabanken.Database.SqlCommandBuilder("GetSpeciesObservations");
commandBuilder.AddParameter(Observationsdatabasen.CHANGED_FROM, changedFrom);
commandBuilder.AddParameter(Observationsdatabasen.CHANGED_TO, changedTo);
commandBuilder.AddParameter(Observationsdatabasen.IS_PRODUCTION, (Configuration.InstallationType == InstallationType.Production));
return(GetReader(commandBuilder));
}
/// <summary>
/// Get DataReader with all observations that matches the list of id's (used for sync purposes and not normal harvest), including any project parameters as a second dataset
/// </summary>
/// <param name="sightingIds">Get observations that has it's id in this list</param>
/// <returns>An open DataReader for reading.</returns>
public DataReader GetSpeciesObservationsByIds(List <Int64> sightingIds)
{
CommandTimeout = 600;
var commandBuilder = new SqlCommandBuilder("GetSpeciesObservationsByIds", true);
commandBuilder.AddParameter(Artportalen.SIGHTING_IDS, sightingIds);
commandBuilder.AddParameter(Artportalen.IS_PRODUCTION, (Configuration.InstallationType == InstallationType.Production));
return(GetReader(commandBuilder, CommandBehavior.Default));
}
public string GetCountyFromCoordinates(double x, double y)
{
var command = new SqlCommandBuilder("GetCountyFromCoordinates", false);
command.AddParameter("X", x);
command.AddParameter("Y", y);
return(ExecuteScalarString(command).Trim());
}
/// <summary>
/// Get species observations by ids in a format that
/// is adjusted to the needs of Elasticsearch.
/// </summary>
/// <param name="speciesObservationIds">Species observation ids.</param>
/// <returns>Information about species observations.</returns>
public DataReader GetSpeciesObservationsByIdElasticsearch(List <Int64> speciesObservationIds)
{
SqlCommandBuilder commandBuilder;
commandBuilder = new SqlCommandBuilder("GetSpeciesObservationsByIdElasticsearch", true);
commandBuilder.AddParameter("SpeciesObservationIdTable", speciesObservationIds);
commandBuilder.AddParameter(SpeciesObservationElasticsearchData.IS_DEBUG, Configuration.Debug);
return(GetReader(commandBuilder));
}
/// <summary>
/// Log when latest harvest was made for this data provider.
/// </summary>
/// <param name="dataProviderId">Data provider.</param>
/// <param name="harvestDate">Latest date when harvest was made.</param>
/// <returns>If database operation was successful.</returns>
public Int32 SetDataProviderLatestHarvestDate(Int32 dataProviderId, DateTime harvestDate)
{
SqlCommandBuilder commandBuilder = new SqlCommandBuilder("SetDataProviderLatestHarvestDate", false);
commandBuilder.AddParameter("dataProviderId", dataProviderId);
commandBuilder.AddParameter("harvestDate", harvestDate);
return(ExecuteCommand(commandBuilder));
}
/// <summary>
/// Get DataReader with all observations that matches the search criteria, including any project parameters as a second dataset.
/// </summary>
/// <param name="changedFrom">Get observations registered at or later than this date.</param>
/// <param name="changedTo">Get observations registered before this date.</param>
/// <returns>DataReader after reading has been finished.</returns>
public DataReader GetSpeciesObservations(DateTime changedFrom, DateTime changedTo)
{
SqlCommandBuilder commandBuilder;
CommandTimeout = 1200;
commandBuilder = new SqlCommandBuilder("GetSpeciesObservations");
commandBuilder.AddParameter(Artportalen.CHANGED_FROM, changedFrom);
commandBuilder.AddParameter(Artportalen.CHANGED_TO, changedTo);
commandBuilder.AddParameter(Artportalen.IS_PRODUCTION, (Configuration.InstallationType == InstallationType.Production));
return(GetReader(commandBuilder, CommandBehavior.Default));
}
/// <summary>
/// Get species observations with specified ids.
/// </summary>
/// <param name="speciesObservationIds">Ids for species observations to get.</param>
/// <param name="maxProtectionLevel">Max protection level.</param>
/// <returns>
/// Returns an open DataReader. Remember to close the
/// DataReader after reading has been finished.
/// </returns>
public DataReader GetDarwinCoreByIds(List <Int64> speciesObservationIds,
Int32 maxProtectionLevel)
{
SqlCommandBuilder commandBuilder;
// Use SqlCommandBuilder with SqlParameter, name the stored procedure
CommandTimeout = 600;
commandBuilder = new SqlCommandBuilder("GetDarwinCoreById", true);
commandBuilder.AddParameter("SpeciesObservationIdTable", speciesObservationIds);
commandBuilder.AddParameter(SpeciesObservationData.MAX_PROTECTION_LEVEL, maxProtectionLevel);
return(GetReader(commandBuilder));
}
/// <summary>
/// Get all factor tree nodes that match search criteria.
/// </summary>
/// <param name="factorIds">Factors to filter on.</param>
/// <returns>Factor tree nodes.</returns>
public DataReader GetFactorTreesBySearchCriteria(List <Int32> factorIds)
{
SqlCommandBuilder commandBuilder;
commandBuilder = new SqlCommandBuilder("GetFactorTreesByIdsAndSearchCriteria", true);
if (factorIds.IsNotEmpty())
{
commandBuilder.AddParameter(FactorTreeData.FACTOR_IDS, factorIds);
}
commandBuilder.AddParameter(FactorTreeData.IS_FACTOR_IDS_SPECIFIED, factorIds.IsNotEmpty());
return(GetReader(commandBuilder, CommandBehavior.Default));
}
/// <summary>
/// Write to log of statistics.
/// </summary>
/// <param name="action">What type of action should be logged.</param>
/// <param name="processtime">How long time did the process take.</param>
/// <param name="result">How did the action went.</param>
public void WriteToLogStatistics(string action,
long processtime,
string result)
{
SqlCommandBuilder commandBuilder = new SqlCommandBuilder("WriteToLogStatistics");
commandBuilder.AddParameter(HarvestLog.ACTION, action);
commandBuilder.AddParameter(HarvestLog.RESULT, result);
commandBuilder.AddParameter(HarvestLog.PROCESSTIME, processtime);
Debug.WriteLine(DateTime.Now.ToShortDateString() + " " + DateTime.Now.ToLongTimeString() + ": " + action + ", " + result + ", " + processtime);
ExecuteCommand(commandBuilder);
}
/// <summary>
/// Adds or updates the SpeciesObservationFieldMapping tabel
/// </summary>
/// <param name="table">The added or updated rows</param>
public void UpdateSpeciesObservationFieldMapping(DataTable table)
{
var commandBuilder = new SqlCommandBuilder("UpdateSpeciesObservationFieldMapping", true);
commandBuilder.AddParameter("Values", table);
ExecuteCommand(commandBuilder);
}
/// <summary>
///
/// </summary>
/// <param name="queryString"></param>
/// <returns></returns>
public DataReader GetDyntaxaTaxonIdByQueryString(String queryString)
{
SqlCommandBuilder commandBuilder = new SqlCommandBuilder("GetTaxonIdByQueryString");
commandBuilder.AddParameter("QueryString", queryString);
return(GetReader(commandBuilder));
}
/// <summary>
/// Creates a reference relation.
/// </summary>
/// <param name="relatedObjectGuid">
/// Object GUID representing an object
/// (taxon , taxon name, revision etc.).
/// </param>
/// <param name="referenceId">Id of the reference.</param>
/// <param name="typeId">Reference type.</param>
/// <returns>Id for the created reference relation.</returns>
public Int32 CreateReferenceRelation(String relatedObjectGuid,
Int32 referenceId,
Int32 typeId)
{
SqlCommandBuilder commandBuilder;
commandBuilder = new SqlCommandBuilder("CreateReferenceRelation");
commandBuilder.AddParameter(ReferenceRelationData.OBJECT_GUID, relatedObjectGuid);
commandBuilder.AddParameter(ReferenceRelationData.REFERENCEID, referenceId);
commandBuilder.AddParameter(ReferenceRelationData.TYPE, typeId);
lock (this)
{
CheckTransaction();
return(ExecuteScalar(commandBuilder));
}
}
/// <summary>
/// Get information about species observations that has
/// changed.
///
/// Scope is restricted to those observations that the
/// user has access rights to. There is no access right
/// check on deleted species observations. This means
/// that a client can obtain information about deleted
/// species observations that the client has not
/// received any create or update information about.
/// </summary>
/// <param name="changeId">
/// Start id for changes that should be returned.
/// The species observation that is changed in the
/// specified change id may be included in returned
/// information.
/// </param>
/// <param name="maxReturnedChanges">Max number of changes to return.</param>
/// <param name="nextIndexHarvestStart">
/// Start date of harvest.
/// This value is used to avoid handling old delete of species observations.
/// </param>
/// <returns>Information about changed species observations.</returns>
public DataReader GetSpeciesObservationChangesElasticsearch(long changeId,
long maxReturnedChanges,
DateTime?nextIndexHarvestStart)
{
SqlCommandBuilder commandBuilder = new SqlCommandBuilder("GetSpeciesObservationChangesElasticsearch");
commandBuilder.AddParameter(SpeciesObservationElasticsearchData.CHANGE_ID, changeId);
commandBuilder.AddParameter(SpeciesObservationElasticsearchData.IS_DEBUG, Configuration.Debug);
commandBuilder.AddParameter(SpeciesObservationElasticsearchData.MAX_RETURNED_CHANGES, maxReturnedChanges);
if (nextIndexHarvestStart.HasValue)
{
commandBuilder.AddParameter(SpeciesObservation.Database.SpeciesObservationElasticsearchData.NEXT_INDEX_HARVEST_START, nextIndexHarvestStart.Value);
}
return(GetReader(commandBuilder, CommandBehavior.Default));
}
/// <summary>
/// Get DataReader with all observations that has been deleted and also matches the list of id's (used for sync purposes and not normal harvest)
/// </summary>
/// <param name="sightingIds">Get observations that has it's id in this list</param>
/// <returns>An open DataReader for reading.</returns>
public DataReader GetDeletedObservationsByIds(List <string> sightingIds)
{
CommandTimeout = 600;
var commandBuilder = new SqlCommandBuilder("GetDeletedObservationsByIds", true);
commandBuilder.AddParameter(Artportalen.SIGHTING_IDS, sightingIds);
return(GetReader(commandBuilder));
}
/// <summary>
/// Get DataReader with information about reference relation types.
/// </summary>
/// <param name="localeId">Language id.</param>
/// <returns>
/// Returns an open DataReader. Remember to close the
/// DataReader after reading has been finished.
/// </returns>
public DataReader GetReferenceRelationTypes(Int32 localeId)
{
SqlCommandBuilder commandBuilder;
commandBuilder = new SqlCommandBuilder("GetReferenceRelationTypes");
commandBuilder.AddParameter(LocaleData.LOCALE_ID, localeId);
return(GetReader(commandBuilder));
}
/// <summary>
/// Get DataReader with information about a reference relation.
/// </summary>
/// <param name="guid">Reference relation GUID.</param>
/// <returns>
/// Returns an open DataReader. Remember to close the
/// DataReader after reading has been finished.
/// </returns>
public DataReader GetReferenceRelationsByGuid(String guid)
{
SqlCommandBuilder commandBuilder;
commandBuilder = new SqlCommandBuilder("GetReferenceRelationsByGuid");
commandBuilder.AddParameter(ReferenceRelationData.GUID, guid);
return(GetReader(commandBuilder));
}
/// <summary>
/// Get DataReader with information about references
/// that matches search criteria.
/// </summary>
/// <param name="whereCondition">Where condition.</param>
/// <returns>
/// Returns an open DataReader. Remember to close the
/// DataReader after reading has been finished.
/// </returns>
public DataReader GetReferencesBySearchCriteria(String whereCondition)
{
SqlCommandBuilder commandBuilder;
commandBuilder = new SqlCommandBuilder("GetReferencesBySearchCriteria");
commandBuilder.AddParameter(ReferenceData.WHERE_CONDITION, whereCondition);
return(GetReader(commandBuilder));
}
/// <summary>
/// Get DataReader with information about specified references.
/// </summary>
/// <param name="referenceIds">Reference ids.</param>
/// <returns>
/// Returns an open DataReader. Remember to close the
/// DataReader after reading has been finished.
/// </returns>
public DataReader GetReferencesByIds(List <Int32> referenceIds)
{
SqlCommandBuilder commandBuilder;
commandBuilder = new SqlCommandBuilder("GetReferencesByIds", true);
commandBuilder.AddParameter(ReferenceData.IDS, referenceIds);
return(GetReader(commandBuilder));
}
/// <summary>
/// Get entries from the LogUpdateError log.
/// </summary>
/// <param name="rowCount">Maximum number of log entries to get.</param>
/// <returns>
/// Returns an open DataReader. Remember to close the
/// DataReader after reading has been finished.
/// </returns>
public DataReader GetLogUpdateError(Int32 rowCount)
{
SqlCommandBuilder commandBuilder = new SqlCommandBuilder("GetLogUpdateError");
commandBuilder.AddParameter(WebServiceLogData.ROW_COUNT, rowCount);
return(GetReader(commandBuilder));
}
/// <summary>
/// Write the data provider's change id to database.
/// </summary>
/// <param name="dataProviderId">
/// The data Provider Id.
/// </param>
/// <param name="changeId">
/// The data provider's Change Id.
/// </param>
/// <returns>
/// The status of the query.
/// </returns>
public Int32 SetMaxChangeId(Int32 dataProviderId, Int64?changeId)
{
SqlCommandBuilder commandBuilder = new SqlCommandBuilder("SetDataProviderMaxChangeId", false);
commandBuilder.AddParameter("dataProviderId", dataProviderId);
if (changeId.HasValue)
{
commandBuilder.AddParameter("maxChangeId", changeId.Value);
}
else
{
commandBuilder.AddParameter("maxChangeId", (Int64?)null);
}
return(ExecuteCommand(commandBuilder));
}
public DataReader GetWeekOfYear(DateTime date)
{
SqlCommandBuilder commandBuilder;
commandBuilder = new SqlCommandBuilder("GetWeekOfYear");
commandBuilder.AddParameter("Date", date);
return(GetReader(commandBuilder));
}
/// <summary>
/// Log the latest date when information about retrieved observations have been changed at provider.
/// </summary>
/// <param name="dataProviderId">Data provider.</param>
/// <returns>If database operation was successful.</returns>
public Int32 SetDataProviderLatestChangedDate(Int32 dataProviderId)
{
SqlCommandBuilder commandBuilder = new SqlCommandBuilder("SetDataProviderLatestChangedDate", false);
commandBuilder.AddParameter("dataProviderId", dataProviderId);
return(ExecuteCommand(commandBuilder));
}
/// <summary>
/// Returns a list of id's with all differences between the tables DarwinCoreObservation and AllSourceSpeciesObservationIds that has a suggestedAction of INSERT
/// </summary>
/// <param name="dataProviderId"></param>
/// <returns></returns>
public List <string> GetAllSourceSpeciesObservationIdsForInsert(int dataProviderId)
{
var list = new List <string>();
var commandBuilder = new SqlCommandBuilder("GetAllSourceSpeciesObservationIds");
commandBuilder.AddParameter("dataProviderId", dataProviderId);
commandBuilder.AddParameter("suggestedAction", "INSERT");
using (var reader = GetReader(commandBuilder))
{
while (reader.Read())
{
list.Add(reader.GetString(0));
}
}
return(list);
}