/// <summary>
/// Read all items with parent item
/// </summary>
/// <param name="allItems">
/// The all items.
/// </param>
/// <param name="orderedItems">
/// The ordered items.
/// </param>
/// <param name="command">
/// The command.
/// </param>
/// <param name="childItems">
/// The child items.
/// </param>
protected void ReadItems(IDictionary <long, TItem> allItems, ICollection <KeyValuePair <long, TItem> > orderedItems, DbCommand command, IDictionary <long, long> childItems)
{
using (IDataReader dataReader = this.MappingStoreDb.ExecuteReader(command))
{
int sysIdIdx = dataReader.GetOrdinal("SYSID");
int idIdx = dataReader.GetOrdinal("ID");
int parentIdx = dataReader.GetOrdinal("PARENT");
int txtIdx = dataReader.GetOrdinal("TEXT");
int langIdx = dataReader.GetOrdinal("LANGUAGE");
int typeIdx = dataReader.GetOrdinal("TYPE");
while (dataReader.Read())
{
long sysId = dataReader.GetInt64(sysIdIdx); // not a null.
TItem item;
if (!allItems.TryGetValue(sysId, out item))
{
item = this.CreateItem();
item.Id = DataReaderHelper.GetString(dataReader, idIdx); // "ID"
this.HandleItemExtraFields(item, dataReader);
orderedItems.Add(new KeyValuePair <long, TItem>(sysId, item));
allItems.Add(sysId, item);
long parentItemId = DataReaderHelper.GetInt64(dataReader, parentIdx);
if (parentItemId > long.MinValue)
{
childItems.Add(sysId, parentItemId);
}
}
ReadLocalisedString(item, typeIdx, txtIdx, langIdx, dataReader);
}
this.MappingStoreDb.CancelSafe(command);
}
}
/// <summary>
/// The retrieve category reference.
/// </summary>
/// <param name="categoryCache">
/// The category cache.
/// </param>
private void RetrieveCategoryReference(IDictionary <long, IStructureReference> categoryCache)
{
using (var command = this.MappingStoreDb.GetSqlStringCommand(CategorisationConstant.CategoryRefQueryFormat))
{
_log.InfoFormat(CultureInfo.InvariantCulture, "Executing Query: '{0}'", command.CommandText);
using (IDataReader dataReader = this.MappingStoreDb.ExecuteReader(command))
{
//// C.CATN_ID, A.ID, A.VERSION, A.AGENCY, I.ID as CATID
int catnIdx = dataReader.GetOrdinal("CATN_ID");
int idIdx = dataReader.GetOrdinal("ID");
int versionIdx = dataReader.GetOrdinal("VERSION");
int agencyIdx = dataReader.GetOrdinal("AGENCY");
int catidIdx = dataReader.GetOrdinal("CATID");
while (dataReader.Read())
{
long catn = DataReaderHelper.GetInt64(dataReader, catnIdx);
string id = DataReaderHelper.GetString(dataReader, idIdx);
string agency = DataReaderHelper.GetString(dataReader, agencyIdx);
string version = DataReaderHelper.GetString(dataReader, versionIdx);
string catid = DataReaderHelper.GetString(dataReader, catidIdx);
var structureReference = new StructureReferenceImpl(agency, id, version, SdmxStructureEnumType.Category, catid);
categoryCache.Add(catn, structureReference);
}
}
}
}
/// <summary>
/// This method populates the Contacts.
/// </summary>
/// <param name="mappingStoreDb">
/// The <see cref="Database"/> instance for Mapping Store database
/// </param>
/// <param name="partySysId">
/// The party system identifier. In the database the column PARTY.PARTY_ID
/// </param>
/// <param name="contacts">
/// The list of contacts to be populated i
/// </param>
private static void PopulatePartyContacts(Database mappingStoreDb, long partySysId, ICollection <IContact> contacts)
{
string paramId = mappingStoreDb.BuildParameterName(ParameterNameConstants.IdParameter);
var sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT CONTACT.CONTACT_ID, CONTACT.PARTY_ID ");
sqlCommand.Append("FROM CONTACT ");
sqlCommand.AppendFormat("WHERE CONTACT.PARTY_ID = {0} ", paramId);
using (DbCommand command = mappingStoreDb.GetSqlStringCommand(sqlCommand.ToString()))
{
mappingStoreDb.AddInParameter(command, ParameterNameConstants.IdParameter, DbType.Int64, partySysId);
using (IDataReader dataReader = mappingStoreDb.ExecuteReader(command))
{
while (dataReader.Read())
{
var contact = new ContactMutableObjectCore();
long contactSysId = DataReaderHelper.GetInt64(dataReader, "CONTACT_ID");
PopulateContactLocalisedStrings(mappingStoreDb, contactSysId, contact);
PopulateContactDetails(mappingStoreDb, contactSysId, contact);
contacts.Add(new ContactCore(contact));
}
}
}
}
/// <summary>
/// When this method is overridden it is used to retrieve Items of a ItemScheme and populate the output List
/// </summary>
/// <param name="itemScheme">
/// The <see cref="IItemSchemeMutableObject{T}"/> to fill with <see cref="IItemMutableObject"/>
/// </param>
/// <param name="parentSysId">
/// The primary key of the Item Scheme from Mapping Store table ARTEFACT.ART_ID field
/// </param>
protected virtual void FillItems(TMaintaible itemScheme, long parentSysId)
{
var itemQuery = new ItemSqlQuery(this._itemSqlQueryInfo, parentSysId);
var itemMap = new Dictionary <long, TItem>();
using (DbCommand command = this.ItemCommandBuilder.Build(itemQuery))
{
using (IDataReader dataReader = this.MappingStoreDb.ExecuteReader(command))
{
int sysIdIdx = dataReader.GetOrdinal("SYSID");
int idIdx = dataReader.GetOrdinal("ID");
int txtIdx = dataReader.GetOrdinal("TEXT");
int langIdx = dataReader.GetOrdinal("LANGUAGE");
int typeIdx = dataReader.GetOrdinal("TYPE");
while (dataReader.Read())
{
long sysId = DataReaderHelper.GetInt64(dataReader, sysIdIdx);
TItem item;
if (!itemMap.TryGetValue(sysId, out item))
{
item = this.CreateItem(); // we set them below.
item.Id = DataReaderHelper.GetString(dataReader, idIdx); // "ID"
itemScheme.AddItem(item);
itemMap.Add(sysId, item);
}
ReadLocalisedString(item, typeIdx, txtIdx, langIdx, dataReader);
}
}
}
this.IdentifiableAnnotationRetrieverEngine.RetrieveAnnotations(parentSysId, itemMap);
}
/// <summary>
/// Retrieve the Groups from mapping store and populate <paramref name="artefact"/>
/// </summary>
/// <param name="parentSysId">
/// The DSD primary key in the Mapping Store Database
/// </param>
/// <param name="artefact">
/// The <see cref="IDataStructureMutableObject"/> to add the groups
/// </param>
private void PopulateGroups(long parentSysId, IDataStructureMutableObject artefact)
{
var groupMap = new Dictionary <long, IGroupMutableObject>();
using (DbCommand command = this._commandBuilder.Build(new ItemSqlQuery(this._groupQueryInfo, parentSysId)))
{
using (IDataReader dataReader = this.MappingStoreDb.ExecuteReader(command))
{
while (dataReader.Read())
{
long currGroupId = DataReaderHelper.GetInt64(dataReader, "GR_ID");
IGroupMutableObject group;
if (!groupMap.TryGetValue(currGroupId, out group))
{
group = new GroupMutableCore {
Id = DataReaderHelper.GetString(dataReader, "GROUP_ID")
};
artefact.AddGroup(group);
groupMap.Add(currGroupId, group);
}
group.DimensionRef.Add(DataReaderHelper.GetString(dataReader, "DIMENSION_REF"));
}
}
}
this._groupAnnotationRetrieverEngine.RetrieveAnnotations(parentSysId, groupMap);
}
/// <summary>
/// Retrieves the list of <see cref="LocalisedStringVO"/> for the specified nameable primary key value.
/// </summary>
/// <param name="nameablePrimaryKeyValue">
/// The nameable primary key value.
/// </param>
/// <param name="localisedType">
/// Type of the localized.
/// </param>
/// <param name="isArtefact">
/// Set to <c>true</c> if the foreign key is <c>ART_ID</c>.
/// </param>
/// <returns>
/// The list of <see cref="LocalisedStringVO"/>
/// </returns>
private IEnumerable <LocalisedStringVO> Retrieve(long nameablePrimaryKeyValue, string localisedType, bool isArtefact)
{
var typeParameter = this._mappingStoreDatabase.CreateInParameter("p_type", DbType.AnsiString, localisedType);
DbParameter artIdParameter;
DbParameter itemIdParameter;
if (isArtefact)
{
artIdParameter = this._mappingStoreDatabase.CreateInParameter("p_artid", DbType.Int64, nameablePrimaryKeyValue);
itemIdParameter = this._mappingStoreDatabase.CreateInParameter("p_itemId", DbType.Int64, DBNull.Value);
}
else
{
artIdParameter = this._mappingStoreDatabase.CreateInParameter("p_artid", DbType.Int64, DBNull.Value);
itemIdParameter = this._mappingStoreDatabase.CreateInParameter("p_itemId", DbType.Int64, nameablePrimaryKeyValue);
}
using (var command = this._mappingStoreDatabase.GetSqlStringCommandFormat(SelectLocalisedStringByForeignKey, typeParameter, artIdParameter, itemIdParameter))
using (var reader = command.ExecuteReader())
{
int lsdIdIdx = reader.GetOrdinal("LS_ID");
int textIdx = reader.GetOrdinal("TEXT");
int langIdx = reader.GetOrdinal("LANGUAGE");
while (reader.Read())
{
var lsdId = DataReaderHelper.GetInt64(reader, lsdIdIdx);
var text = DataReaderHelper.GetString(reader, textIdx);
var lang = DataReaderHelper.GetString(reader, langIdx);
yield return(new LocalisedStringVO {
PrimaryKeyValue = lsdId, TextTypeWrapper = new TextTypeWrapperMutableCore(lang, text)
});
}
}
}
/// <summary>
/// Get the Codes
/// </summary>
/// <param name="itemSchemeBean">
/// The parent <see cref="ICodelistMutableObject"/>
/// </param>
/// <param name="parentSysId">
/// The parent ItemScheme primary key in Mapping Store
/// </param>
/// <param name="subset">
/// The list of items to retrieve
/// </param>
/// <returns>
/// The <see cref="ICodelistMutableObject"/>.
/// </returns>
private ICodelistMutableObject FillCodesHash(ICodelistMutableObject itemSchemeBean, long parentSysId, IList <string> subset)
{
var allItems = new Dictionary <long, ICodeMutableObject>();
var orderedItems = new List <KeyValuePair <long, ICodeMutableObject> >();
var childItems = new Dictionary <long, long>();
// TODO convert to Set<> in .NET 3.5
var subsetSet = new HashSet <string>(StringComparer.Ordinal);
for (int i = 0; i < subset.Count; i++)
{
subsetSet.Add(subset[i]);
}
using (DbCommand command = this.ItemCommandBuilder.Build(new ItemSqlQuery(this.ItemSqlQueryInfo, parentSysId)))
{
using (IDataReader dataReader = this.MappingStoreDb.ExecuteReader(command))
{
int sysIdIdx = dataReader.GetOrdinal("SYSID");
int idIdx = dataReader.GetOrdinal("ID");
int parentIdx = dataReader.GetOrdinal("PARENT");
int txtIdx = dataReader.GetOrdinal("TEXT");
int langIdx = dataReader.GetOrdinal("LANGUAGE");
int typeIdx = dataReader.GetOrdinal("TYPE");
while (dataReader.Read())
{
string id = DataReaderHelper.GetString(dataReader, idIdx);
if (subsetSet.Contains(id))
{
long sysId = DataReaderHelper.GetInt64(dataReader, sysIdIdx);
ICodeMutableObject item;
if (!allItems.TryGetValue(sysId, out item))
{
item = new CodeMutableCore {
Id = id
};
orderedItems.Add(new KeyValuePair <long, ICodeMutableObject>(sysId, item));
allItems.Add(sysId, item);
long parentItemId = DataReaderHelper.GetInt64(dataReader, parentIdx);
if (parentItemId > long.MinValue)
{
childItems.Add(sysId, parentItemId);
}
}
ReadLocalisedString(item, typeIdx, txtIdx, langIdx, dataReader);
}
}
}
}
this.FillParentItems(itemSchemeBean, childItems, allItems, orderedItems);
this.IdentifiableAnnotationRetrieverEngine.RetrieveAnnotations(parentSysId, allItems);
return(itemSchemeBean);
}
/// <summary>
/// Returns the final status (id and is final value) of the specified <paramref name="reference"/>; otherwise it returns null
/// </summary>
/// <param name="state">
/// The state.
/// </param>
/// <param name="reference">
/// The structure reference.
/// </param>
/// <exception cref="ArgumentException">
/// <paramref name="reference"/> unsupported structure
/// </exception>
/// <returns>
/// The <see cref="ArtefactFinalStatus"/> of the specified <paramref name="reference"/>; otherwise it returns null.
/// </returns>
public static ArtefactFinalStatus GetFinalStatus(DbTransactionState state, IStructureReference reference)
{
if (state == null)
{
throw new ArgumentNullException("state");
}
if (reference == null)
{
throw new ArgumentNullException("reference");
}
var tableInfo = _tableInfoBuilder.Build(reference.MaintainableStructureEnumType.EnumType);
if (tableInfo == null)
{
_log.WarnFormat("Unsupported structure type {0}", reference.MaintainableStructureEnumType.EnumType);
return(ArtefactFinalStatus.Empty);
////throw new ArgumentException(string.Format(CultureInfo.InvariantCulture, Resources.ExceptionUnsupportedStructureReferenceFormat1, reference), "reference");
}
var finalQueryBuilder = new IsFinalQueryBuilder(state.Database);
var query = finalQueryBuilder.Build(tableInfo);
var maintainableRefObject = reference.MaintainableReference;
var version = maintainableRefObject.SplitVersion(3);
ArtefactFinalStatus artefactFinalStatus = ArtefactFinalStatus.Empty;
state.ExecuteReaderFormat(
query,
reader =>
{
if (reader.Read())
{
var primaryKey = DataReaderHelper.GetInt64(reader, "primaryKey");
var finalStatus = DataReaderHelper.GetBoolean(reader, "isFinal");
artefactFinalStatus = new ArtefactFinalStatus(primaryKey, finalStatus);
}
},
state.Database.CreateInParameter("id", DbType.AnsiString, maintainableRefObject.MaintainableId),
state.Database.CreateInParameter("agency", DbType.AnsiString, maintainableRefObject.AgencyId),
state.Database.CreateInParameter("version1", DbType.AnsiString, version[0].ToDbValue(0)),
state.Database.CreateInParameter("version2", DbType.AnsiString, version[1].ToDbValue(0)),
state.Database.CreateInParameter("version3", DbType.AnsiString, version[2].ToDbValue()));
return(artefactFinalStatus);
}
/// <summary>
/// Populates the concept scheme per measure dimension.
/// </summary>
/// <param name="reader">
/// The reader.
/// </param>
/// <param name="conceptSchemesPerMeasureDimension">
/// The concept schemes per measure dimension.
/// </param>
private static void PopulateConceptSchemePerMeasureDimension(IDataReader reader, IDictionary <long, IMaintainableRefObject> conceptSchemesPerMeasureDimension)
{
int compIdIdx = reader.GetOrdinal("COMP_ID");
int agencyIdx = reader.GetOrdinal("CONCEPTSCHEME_AGENCY");
int idIdx = reader.GetOrdinal("CONCEPTSCHEME_ID");
int versionIdx = reader.GetOrdinal("CONCEPTSCHEME_VERSION");
while (reader.Read())
{
var compId = DataReaderHelper.GetInt64(reader, compIdIdx);
var agencyId = DataReaderHelper.GetString(reader, agencyIdx);
var id = DataReaderHelper.GetString(reader, idIdx);
var version = DataReaderHelper.GetString(reader, versionIdx);
var conceptSchemeReference = new MaintainableRefObjectImpl(agencyId, id, version);
conceptSchemesPerMeasureDimension.Add(compId, conceptSchemeReference);
}
}
/// <summary>
/// Retrieve and populate the <see cref="IHierarchicalCodelistMutableObject.Hierarchies"/> for the HCL with specified Primary KEY
/// </summary>
/// <param name="hierarchicalCodelistBean">
/// The <see cref="IHierarchicalCodelistMutableObject"/> to populate
/// </param>
/// <param name="sysId">
/// The HCL Mapping store Primary KEY
/// </param>
private void FillHierarchy(IHierarchicalCodelistMutableObject hierarchicalCodelistBean, long sysId)
{
// ReSharper restore SuggestBaseTypeForParameter
var sysIdArtefacts = new Dictionary <long, IHierarchyMutableObject>();
var itemSqlQuery = new ItemSqlQuery(this._hierarchyQueryInfo, sysId);
using (DbCommand command = this._itemCommandBuilder.Build(itemSqlQuery))
{
using (IDataReader dataReader = this.MappingStoreDb.ExecuteReader(command))
{
int sysIdIdx = dataReader.GetOrdinal("SYSID");
int idIdx = dataReader.GetOrdinal("ID");
int txtIdx = dataReader.GetOrdinal("TEXT");
int langIdx = dataReader.GetOrdinal("LANGUAGE");
int typeIdx = dataReader.GetOrdinal("TYPE");
while (dataReader.Read())
{
long hierarchySysId = DataReaderHelper.GetInt64(dataReader, sysIdIdx);
IHierarchyMutableObject artefact;
if (!sysIdArtefacts.TryGetValue(hierarchySysId, out artefact))
{
artefact = new HierarchyMutableCore {
Id = DataReaderHelper.GetString(dataReader, idIdx)
};
sysIdArtefacts.Add(hierarchySysId, artefact);
}
ReadLocalisedString(artefact, typeIdx, txtIdx, langIdx, dataReader);
}
}
}
this._hierarchyAnnotationRetrieverEngine.RetrieveAnnotations(sysId, sysIdArtefacts);
foreach (KeyValuePair <long, IHierarchyMutableObject> sysIdArtefact in sysIdArtefacts)
{
long hid = sysIdArtefact.Key;
IHierarchyMutableObject artefact = sysIdArtefact.Value;
this.FillLevel(artefact, hid);
this.FillCodeRef(artefact, hid);
hierarchicalCodelistBean.AddHierarchies(artefact);
}
}
/// <summary>
/// This method populates the Senders and Receivers of a <see cref="IHeader"/>
/// </summary>
/// <param name="mappingStoreDb">
/// The <see cref="Database"/> instance for Mapping Store database
/// </param>
/// <param name="headerSysId">
/// The header system identifier. In the database the column HEADER.HEADER_ID
/// </param>
/// <param name="header">
/// The <see cref="IHeader"/> to be populated in terms of Senders and Receivers
/// </param>
private static void PoulateHeaderSendersAndReceivers(Database mappingStoreDb, long headerSysId, IHeader header)
{
string paramId = mappingStoreDb.BuildParameterName(ParameterNameConstants.IdParameter);
var sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT PARTY.PARTY_ID, PARTY.ID, PARTY.HEADER_ID, PARTY.TYPE ");
sqlCommand.Append("FROM PARTY ");
sqlCommand.AppendFormat("WHERE PARTY.HEADER_ID = {0} ", paramId);
using (DbCommand command = mappingStoreDb.GetSqlStringCommand(sqlCommand.ToString()))
{
mappingStoreDb.AddInParameter(command, ParameterNameConstants.IdParameter, DbType.Int64, headerSysId);
using (IDataReader dataReader = mappingStoreDb.ExecuteReader(command))
{
while (dataReader.Read())
{
var id = DataReaderHelper.GetString(dataReader, "ID");
long partySysId = DataReaderHelper.GetInt64(dataReader, "PARTY_ID");
string partyType = DataReaderHelper.GetString(dataReader, "TYPE");
var names = new List <ITextTypeWrapper>();
PopulatePartyLocalisedStrings(mappingStoreDb, partySysId, names);
var contacts = new List <IContact>();
PopulatePartyContacts(mappingStoreDb, partySysId, contacts);
var party = new PartyCore(names, id, contacts, null);
// is it a sender or a receiver?
if (partyType.Equals(SenderText, StringComparison.OrdinalIgnoreCase))
{
header.Sender = party;
}
else if (partyType.Equals(ReceiverText, StringComparison.OrdinalIgnoreCase))
{
header.AddReciever(party);
}
}
}
}
}
/// <summary>
/// The retrieve artefact reference.
/// </summary>
/// <param name="artefactCache">
/// The artefact cache.
/// </param>
/// <param name="allowedDataflows">
/// The allowed dataflows.
/// </param>
/// <exception cref="InvalidOperationException">
/// There is an error in the SQL Query in <see cref="CategorisationConstant.ArtefactRefQueryFormat"/>
/// </exception>
private void RetrieveArtefactReference(IDictionary <long, IStructureReference> artefactCache, IList <IMaintainableRefObject> allowedDataflows)
{
using (var command = this._authReferenceCommandBuilder.Build(new ReferenceSqlQuery(CategorisationConstant.ArtefactReference), allowedDataflows))
{
_log.InfoFormat(CultureInfo.InvariantCulture, "Executing Query: '{0}'", command.CommandText);
using (IDataReader dataReader = this.MappingStoreDb.ExecuteReader(command))
{
//// A.ID, A.VERSION, A.AGENCY, T.STYPE
int catnIdx = dataReader.GetOrdinal("CATN_ID");
int idIdx = dataReader.GetOrdinal("ID");
int versionIdx = dataReader.GetOrdinal("VERSION");
int agencyIdx = dataReader.GetOrdinal("AGENCY");
int stypeIdx = dataReader.GetOrdinal("STYPE");
while (dataReader.Read())
{
long catn = DataReaderHelper.GetInt64(dataReader, catnIdx);
string id = DataReaderHelper.GetString(dataReader, idIdx);
string agency = DataReaderHelper.GetString(dataReader, agencyIdx);
string version = DataReaderHelper.GetString(dataReader, versionIdx);
string stype = DataReaderHelper.GetString(dataReader, stypeIdx);
SdmxStructureEnumType structureType;
if (Enum.TryParse(stype, true, out structureType))
{
var structureReference = new StructureReferenceImpl(agency, id, version, structureType);
artefactCache.Add(catn, structureReference);
}
else
{
var message = string.Format(CultureInfo.InvariantCulture, "Error could not convert {0} to SdmxStructureEnumType", stype);
_log.Error(message);
throw new InvalidOperationException(message);
}
}
}
}
}
/// <summary>
/// This method queries the mapping store for header information for a specific dataflow
/// </summary>
/// <param name="beginDate">For ReportingBegin element</param>
/// <param name="endDate">For ReportingEnd element</param>
/// <param name="dataflowReference">The dataflow reference.</param>
/// <returns>
/// A <see cref="IHeader" /> object. Otherwise null
/// </returns>
private IHeader GetHeader(DateTime?beginDate, DateTime?endDate, IMaintainableRefObject dataflowReference)
{
long headerSysId;
string paramId = this._mappingStoreDb.BuildParameterName(ParameterNameConstants.IdParameter);
string version1 = this._mappingStoreDb.BuildParameterName(ParameterNameConstants.VersionParameter1);
string version2 = this._mappingStoreDb.BuildParameterName(ParameterNameConstants.VersionParameter2);
string version3 = this._mappingStoreDb.BuildParameterName(ParameterNameConstants.VersionParameter3);
string agency = this._mappingStoreDb.BuildParameterName(ParameterNameConstants.AgencyParameter);
var sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT HD.HEADER_ID, HD.TEST, HD.DATASET_AGENCY, HD.DF_ID ");
sqlCommand.Append("FROM HEADER HD, DATAFLOW DF, ARTEFACT ART ");
sqlCommand.Append("WHERE HD.DF_ID = DF.DF_ID ");
sqlCommand.Append("AND DF.DF_ID = ART.ART_ID ");
sqlCommand.AppendFormat("AND ART.ID = {0} ", paramId);
sqlCommand.AppendFormat("AND dbo.isEqualVersion(ART.VERSION1,ART.VERSION2,ART.VERSION3,{0},{1},{2})=1 ", version1, version2, version3);
sqlCommand.AppendFormat("AND ART.AGENCY = {0} ", agency);
IDictionary <string, string> additionalAttributes = new Dictionary <string, string>(StringComparer.Ordinal);
bool test;
DateTime currentDate = DateTime.Now;
var dataflowId = dataflowReference.MaintainableId;
var dataflowAgency = dataflowReference.AgencyId;
var version = dataflowReference.SplitVersion(3);
using (DbCommand command = this._mappingStoreDb.GetSqlStringCommand(sqlCommand.ToString()))
{
this._mappingStoreDb.AddInParameter(command, ParameterNameConstants.IdParameter, DbType.String, dataflowId);
this._mappingStoreDb.AddInParameter(command, ParameterNameConstants.VersionParameter1, DbType.Int64, version[0].HasValue ? version[0].Value : 0);
this._mappingStoreDb.AddInParameter(command, ParameterNameConstants.VersionParameter2, DbType.Int64, version[1].HasValue ? version[1].Value : 0);
this._mappingStoreDb.AddInParameter(command, ParameterNameConstants.VersionParameter3, DbType.Int64, version[2].HasValue ? (object)version[2].Value : null);
this._mappingStoreDb.AddInParameter(command, ParameterNameConstants.AgencyParameter, DbType.String, dataflowAgency);
using (IDataReader dataReader = this._mappingStoreDb.ExecuteReader(command))
{
// we expect only 1 record here
if (dataReader.Read())
{
headerSysId = DataReaderHelper.GetInt64(dataReader, "HEADER_ID");
test = DataReaderHelper.GetBoolean(dataReader, "TEST");
additionalAttributes.Add("DataSetAgency", DataReaderHelper.GetString(dataReader, "DATASET_AGENCY"));
_log.DebugFormat(CultureInfo.InvariantCulture, "Found header information in mapping store for Dataflow {0}", dataflowId);
}
else
{
_log.DebugFormat(CultureInfo.InvariantCulture, "No header information found in mapping store for Dataflow {0}", dataflowId);
return(null);
}
}
}
string datasetId = BuildDatasetId(dataflowId, currentDate);
// DatasetAction: Information (case that is response to a query)
DatasetAction datasetAction = DatasetAction.GetAction("Information");
IHeader ret = new HeaderImpl(additionalAttributes, null, null, datasetAction, dataflowId, datasetId, null, currentDate, currentDate, beginDate, endDate, null, null, null, null, test);
PopulateHeaderLocalisedStrings(this._mappingStoreDb, headerSysId, ret);
PoulateHeaderSendersAndReceivers(this._mappingStoreDb, headerSysId, ret);
if (ret.Sender != null)
{
DateTime extracted = ret.Extracted.HasValue ? ret.Extracted.Value : currentDate;
ret.DatasetId = BuildDatasetId(ret.Id, extracted, ret.Sender.Id);
}
return(ret);
}
/// <summary>
/// Retrieve the common <see cref="IMaintainableMutableObject"/> information from Mapping Store ARTEFACT table. This method does not retrieve the Names and Description
/// </summary>
/// <param name="sqlQuery">
/// The SQL Query for artefacts
/// </param>
/// <param name="detail">
/// The structure query detail
/// </param>
/// <param name="commandBuilder">
/// The command Builder.
/// </param>
/// <param name="retrieveDetails">
/// The method to retrieve details of the artefacts
/// </param>
/// <param name="extraFields">
/// The extra Fields.
/// </param>
/// <returns>
/// A dictionary with key the primary key in Mapping Store
/// </returns>
protected ISet <T> RetrieveArtefacts(ArtefactSqlQuery sqlQuery, ComplexStructureQueryDetailEnumType detail, Func <ArtefactSqlQuery, DbCommand> commandBuilder = null, Func <T, long, T> retrieveDetails = null, Action <T, IDataReader> extraFields = null)
{
commandBuilder = commandBuilder ?? this.CommandBuilder.Build;
retrieveDetails = retrieveDetails ?? this.RetrieveDetails;
var artefactPkPairs = new List <KeyValuePair <T, long> >();
using (DbCommand command = commandBuilder(sqlQuery))
{
_log.InfoFormat(CultureInfo.InvariantCulture, "Executing Query: '{0}' with '{1}'", command.CommandText, sqlQuery.MaintainableRef);
using (IDataReader dataReader = this._mappingStoreDb.ExecuteReader(command))
{
int sysIdIdx = dataReader.GetOrdinal("SYSID");
int idIdx = dataReader.GetOrdinal("ID");
int version1Idx = dataReader.GetOrdinal("VERSION");
int agencyIdx = dataReader.GetOrdinal("AGENCY");
int validFromIdx = dataReader.GetOrdinal("VALID_FROM");
int validToIdx = dataReader.GetOrdinal("VALID_TO");
int isFinalIdx = dataReader.GetOrdinal("IS_FINAL");
int txtIdx = dataReader.GetOrdinal("TEXT");
int langIdx = dataReader.GetOrdinal("LANGUAGE");
int typeIdx = dataReader.GetOrdinal("TYPE");
var artefactMap = new Dictionary <long, T>();
while (dataReader.Read())
{
long sysId = DataReaderHelper.GetInt64(dataReader, sysIdIdx);
T artefact;
if (!artefactMap.TryGetValue(sysId, out artefact))
{
artefact = this.CreateArtefact();
artefact.FinalStructure = DataReaderHelper.GetTristate(dataReader, isFinalIdx);
artefact.EndDate = DataReaderHelper.GetStringDate(dataReader, validToIdx);
artefact.StartDate = DataReaderHelper.GetStringDate(dataReader, validFromIdx);
artefact.Version = DataReaderHelper.GetString(dataReader, version1Idx);
artefact.AgencyId = DataReaderHelper.GetString(dataReader, agencyIdx);
artefact.Id = DataReaderHelper.GetString(dataReader, idIdx);
this.HandleArtefactExtraFields(artefact, dataReader);
if (extraFields != null)
{
extraFields(artefact, dataReader);
}
artefactPkPairs.Add(new KeyValuePair <T, long>(artefact, sysId));
artefactMap.Add(sysId, artefact);
}
if (!artefact.IsDefault())
{
ReadLocalisedString(artefact, typeIdx, txtIdx, langIdx, dataReader, detail);
}
}
}
}
if (artefactPkPairs.Count < 1)
{
_log.InfoFormat(CultureInfo.InvariantCulture, "No artefacts retrieved for : '{0}'", sqlQuery.MaintainableRef);
return(new HashSet <T>());
}
return(HandleDetailLevel(detail, retrieveDetails, artefactPkPairs));
}
/// <summary>
/// Retrieve and populate the <see cref="IHierarchyMutableObject.ChildLevel"/> for the HIERARCHY with specified Primary KEY
/// </summary>
/// <param name="hierarchy">
/// the <see cref="IHierarchyMutableObject"/> to populate
/// </param>
/// <param name="sysId">
/// The HIERARCHY Mapping store Primary KEY
/// </param>
private void FillLevel(IHierarchyMutableObject hierarchy, long sysId)
{
var sysIdArtefacts = new Dictionary <long, ILevelMutableObject>();
// holds the next level id to level id map. It contains all but the first level (with order == 1)
var orderChain = new Dictionary <long, long>();
using (DbCommand command = this._itemCommandBuilder.Build(new ItemSqlQuery(this._levelQueryInfo, sysId)))
{
using (IDataReader dataReader = this.MappingStoreDb.ExecuteReader(command))
{
int sysIdIdx = dataReader.GetOrdinal("SYSID");
int idIdx = dataReader.GetOrdinal("ID");
int parentIdx = dataReader.GetOrdinal("PARENT");
int txtIdx = dataReader.GetOrdinal("TEXT");
int langIdx = dataReader.GetOrdinal("LANGUAGE");
int typeIdx = dataReader.GetOrdinal("TYPE");
while (dataReader.Read())
{
long levelId = DataReaderHelper.GetInt64(dataReader, sysIdIdx);
ILevelMutableObject artefact;
if (!sysIdArtefacts.TryGetValue(levelId, out artefact))
{
artefact = new LevelMutableCore {
Id = DataReaderHelper.GetString(dataReader, idIdx)
};
long nextLevelId = DataReaderHelper.GetInt64(dataReader, parentIdx);
// TODO CodingType when MSDB supports it
if (nextLevelId > long.MinValue)
{
orderChain[levelId] = nextLevelId;
}
sysIdArtefacts.Add(levelId, artefact);
}
ReadLocalisedString(artefact, typeIdx, txtIdx, langIdx, dataReader);
}
}
}
this._levelAnnotationRetrieverEngine.RetrieveAnnotations(sysId, sysIdArtefacts);
foreach (KeyValuePair <long, ILevelMutableObject> level in sysIdArtefacts)
{
long nextLevelId;
if (orderChain.TryGetValue(level.Key, out nextLevelId))
{
ILevelMutableObject nextLevel;
if (sysIdArtefacts.TryGetValue(nextLevelId, out nextLevel))
{
nextLevel.ChildLevel = level.Value;
}
}
else
{
hierarchy.ChildLevel = level.Value;
//// TODO FIXME common api sets this to true for SDMX v2.0 input if levels exist.
hierarchy.FormalLevels = true;
}
}
}
/// <summary>
/// Retrieve and populate the <see cref="IHierarchyMutableObject.HierarchicalCodeObjects"/> for the HIERARCHY with specified Primary KEY
/// </summary>
/// <param name="artefact">
/// The <see cref="IHierarchyMutableObject"/> instance to populate
/// </param>
/// <param name="hid">
/// The Hierarchy Mapping store Primary KEY
/// </param>
private void FillCodeRef(IHierarchyMutableObject artefact, long hid)
{
IDictionary <long, ICodeRefMutableObject> allItems = new ListDictionary <long, ICodeRefMutableObject>();
var childItems = new Dictionary <long, long>();
using (DbCommand command = this._itemCommandBuilder.Build(new ItemSqlQuery(this._codeRefQueryInfo, hid)))
{
using (IDataReader dataReader = this.MappingStoreDb.ExecuteReader(command))
{
int sysIdIdx = dataReader.GetOrdinal("SYSID");
int idIdx = dataReader.GetOrdinal("CODE_ID");
int parentIdx = dataReader.GetOrdinal("PARENT");
int nodeAliasIdIdx = dataReader.GetOrdinal("NodeAliasID");
//// TODO check how to set the version (it seems supported in v2.1)
int levelRefIdx = dataReader.GetOrdinal("LEVEL_REF");
int clidIdx = dataReader.GetOrdinal("CLID");
int clversionIdx = dataReader.GetOrdinal("CLVERSION");
int clagencyIdx = dataReader.GetOrdinal("CLAGENCY");
int validFromIdx = dataReader.GetOrdinal("VALID_FROM");
int validToIdx = dataReader.GetOrdinal("VALID_TO");
while (dataReader.Read())
{
var codeRef = new CodeRefMutableCore
{
CodelistAliasRef =
BuildCodelistRefAlias(
DataReaderHelper.GetString(dataReader, clidIdx),
DataReaderHelper.GetString(dataReader, clagencyIdx),
DataReaderHelper.GetString(dataReader, clversionIdx)),
Id = DataReaderHelper.GetString(dataReader, nodeAliasIdIdx),
CodeId = DataReaderHelper.GetString(dataReader, idIdx)
};
if (artefact.ChildLevel != null)
{
var levelRef = DataReaderHelper.GetString(dataReader, levelRefIdx);
ILevelMutableObject currentLevel = artefact.ChildLevel;
var normalizedLevel = GetNormalizedLevel(currentLevel, levelRef);
codeRef.LevelReference = normalizedLevel;
}
if (string.IsNullOrWhiteSpace(codeRef.Id))
{
codeRef.Id = string.Format(CultureInfo.InvariantCulture, "{0}{1}{2}", codeRef.CodelistAliasRef.Replace('@', '_'), '_', codeRef.CodeId);
}
DateTime?validFrom = DataReaderHelper.GetStringDate(dataReader, validFromIdx);
if (validFrom != null)
{
codeRef.ValidFrom = validFrom.Value;
}
DateTime?validTo = DataReaderHelper.GetStringDate(dataReader, validToIdx);
if (validTo != null)
{
codeRef.ValidTo = validTo.Value;
}
long sysId = DataReaderHelper.GetInt64(dataReader, sysIdIdx);
long parentItemId = DataReaderHelper.GetInt64(dataReader, parentIdx);
if (parentItemId > long.MinValue)
{
childItems.Add(sysId, parentItemId);
}
allItems.Add(sysId, codeRef);
}
}
}
this._hierarchalCodeAnnotationRetrieverEngine.RetrieveAnnotations(hid, allItems);
ICollection <KeyValuePair <long, ICodeRefMutableObject> > collection = allItems;
foreach (KeyValuePair <long, ICodeRefMutableObject> item in collection)
{
long sysId = item.Key;
ICodeRefMutableObject codeRef = item.Value;
long parentItemId;
if (childItems.TryGetValue(sysId, out parentItemId))
{
// has parent
ICodeRefMutableObject parent = allItems[parentItemId];
parent.AddCodeRef(codeRef);
}
else
{
// add only root elements
artefact.AddHierarchicalCode(codeRef);
}
}
}
