/// <summary>
/// This method retrieves a list of <see cref="MappingEntity"/> objects
/// by the mapping set system identifier
/// </summary>
/// <param name="mappingStoreDb">
/// The <see cref="Database"/> instance for Mapping Store database
/// </param>
/// <param name="mappingSet">
/// The mapping set
/// </param>
private static void GetMappingsByMappingSetSysId(Database mappingStoreDb, MappingSetEntity mappingSet)
{
long sysId = mappingSet.SysId;
var dictionary = new Dictionary<long, MappingEntity>();
var componentMap = new Dictionary<long, ComponentEntity>();
bool transcoded = false;
MappingEntity timeDimensionMapping = null;
AddToComponentMap(mappingSet.Dataflow.Dsd.Dimensions, componentMap);
if (mappingSet.Dataflow.Dsd.TimeDimension != null)
{
componentMap.Add(mappingSet.Dataflow.Dsd.TimeDimension.SysId, mappingSet.Dataflow.Dsd.TimeDimension);
}
componentMap.Add(mappingSet.Dataflow.Dsd.PrimaryMeasure.SysId, mappingSet.Dataflow.Dsd.PrimaryMeasure);
AddToComponentMap(mappingSet.Dataflow.Dsd.Attributes, componentMap);
AddToComponentMap(mappingSet.Dataflow.Dsd.CrossSectionalMeasures, componentMap);
string paramId = mappingStoreDb.BuildParameterName(ParameterNameConstants.IdParameter);
var sqlCommand = new StringBuilder();
// select
sqlCommand.Append("SELECT CMAP.MAP_ID, CMAP.TYPE, CMAP.CONSTANT, CMAPCOMP.COMP_ID, TR.TR_ID, TR.EXPRESSION ");
// from1
sqlCommand.Append("FROM COMPONENT_MAPPING CMAP INNER JOIN MAPPING_SET MSET ON MSET.MAP_SET_ID = CMAP.MAP_SET_ID INNER JOIN COM_COL_MAPPING_COMPONENT CMAPCOMP ON CMAPCOMP.MAP_ID = CMAP.MAP_ID ");
sqlCommand.Append(" LEFT OUTER JOIN TRANSCODING TR ON TR.MAP_ID = CMAP.MAP_ID ");
// where
sqlCommand.AppendFormat(" WHERE MSET.MAP_SET_ID = {0} ", paramId);
using (DbCommand command = mappingStoreDb.GetSqlStringCommand(sqlCommand.ToString()))
{
mappingStoreDb.AddInParameter(command, ParameterNameConstants.IdParameter, DbType.Int64, sysId);
using (IDataReader dataReader = mappingStoreDb.ExecuteReader(command))
{
int mapID = dataReader.GetOrdinal("MAP_ID");
int type = dataReader.GetOrdinal("TYPE");
int constant = dataReader.GetOrdinal("CONSTANT");
int compIDIdx = dataReader.GetOrdinal("COMP_ID");
int tridIdx = dataReader.GetOrdinal("TR_ID");
int exprIdx = dataReader.GetOrdinal("EXPRESSION");
while (dataReader.Read())
{
// get existing or create new mapping object
long mapId = DataReaderHelper.GetInt64(dataReader, mapID);
MappingEntity mapping;
if (!dictionary.TryGetValue(mapId, out mapping))
{
// create new mapping and append it to the mapping set
mapping = new MappingEntity(mapId) { MappingType = DataReaderHelper.GetString(dataReader, type), Constant = DataReaderHelper.GetString(dataReader, constant) };
long trid = DataReaderHelper.GetInt64(dataReader, tridIdx);
if (trid > 0)
{
mapping.Transcoding = new TranscodingEntity(trid) { Expression = DataReaderHelper.GetString(dataReader, exprIdx) };
transcoded = true;
}
dictionary.Add(mapId, mapping);
mappingSet.Mappings.Add(mapping);
}
long componentSysId = DataReaderHelper.GetInt64(dataReader, compIDIdx);
ComponentEntity component;
if (componentMap.TryGetValue(componentSysId, out component))
{
mapping.Components.Add(component);
if (component.Equals(mappingSet.Dataflow.Dsd.TimeDimension) && mapping.Transcoding != null)
{
timeDimensionMapping = mapping;
}
}
}
}
}
// second select
sqlCommand = new StringBuilder();
sqlCommand.Append(
"SELECT CMAP.MAP_ID, CMAP.TYPE, CMAP.CONSTANT, DSCOL.COL_ID, DSCOL.NAME, DSCOL.DS_ID ");
sqlCommand.Append(
"FROM COMPONENT_MAPPING CMAP, MAPPING_SET MSET, COM_COL_MAPPING_COLUMN CMAPCOL, DATASET_COLUMN DSCOL ");
sqlCommand.Append("WHERE MSET.MAP_SET_ID = CMAP.MAP_SET_ID ");
sqlCommand.Append("AND CMAP.MAP_ID = CMAPCOL.MAP_ID ");
sqlCommand.Append("AND CMAPCOL.COL_ID = DSCOL.COL_ID ");
sqlCommand.AppendFormat("AND MSET.MAP_SET_ID = {0} ", paramId);
using (DbCommand command = mappingStoreDb.GetSqlStringCommand(sqlCommand.ToString()))
{
mappingStoreDb.AddInParameter(command, ParameterNameConstants.IdParameter, DbType.Int64, sysId);
using (IDataReader dataReader = mappingStoreDb.ExecuteReader(command))
{
int mapID = dataReader.GetOrdinal("MAP_ID");
int type = dataReader.GetOrdinal("TYPE");
int constant = dataReader.GetOrdinal("CONSTANT");
int colIDIdx = dataReader.GetOrdinal("COL_ID");
int nameIdx = dataReader.GetOrdinal("NAME");
////int descIdx = dataReader.GetOrdinal("DESCRIPTION");
while (dataReader.Read())
{
// get existing or create new mapping object
long mapId = DataReaderHelper.GetInt64(dataReader, mapID);
MappingEntity mapping;
if (!dictionary.TryGetValue(mapId, out mapping))
{
// create new mapping and append it to the mapping set
mapping = new MappingEntity(mapId) { MappingType = DataReaderHelper.GetString(dataReader, type), Constant = DataReaderHelper.GetString(dataReader, constant) };
dictionary.Add(mapId, mapping);
mappingSet.Mappings.Add(mapping);
}
var column = new DataSetColumnEntity(DataReaderHelper.GetInt64(dataReader, colIDIdx)) { Name = DataReaderHelper.GetString(dataReader, nameIdx) };
// dsColumn.SysId = DataReaderHelper.GetInt64(dataReader, "DS_ID");
////column.Description = DataReaderHelper.GetString(dataReader, descIdx);
mapping.Columns.Add(column);
}
}
}
if (transcoded)
{
GetTranscodingRulesByMapSetId(mappingStoreDb, sysId, timeDimensionMapping, dictionary);
RetrieveTimeDimensionTranscoding(mappingStoreDb, timeDimensionMapping);
}
}
/// <summary>
/// The method retrieves the <see cref="TranscodingRulesEntity"/> object
/// by the transcoding system identifier
/// </summary>
/// <param name="mappingStoreDb">
/// The <see cref="Database"/> instance for Mapping Store database
/// </param>
/// <param name="sysId">
/// The mapping set with the specified system identifier
/// </param>
/// <param name="timeDimensionMapping">
/// The TRANSCODING.TR_ID value of the TimeDimension, if there is TimeDimension and is transcoded; otherwise set to null
/// </param>
/// <param name="mappingsMap">
/// The map between COMPONENT_MAPPING.MAP_ID value and MappingEntity
/// </param>
private static void GetTranscodingRulesByMapSetId(Database mappingStoreDb, long sysId, MappingEntity timeDimensionMapping, IDictionary<long, MappingEntity> mappingsMap)
{
string paramId = mappingStoreDb.BuildParameterName(ParameterNameConstants.IdParameter);
string timeDimensionTranscodingIdParam = mappingStoreDb.BuildParameterName(ParameterNameConstants.TranscodingId);
object timedimensionID = timeDimensionMapping == null ? (object)null : timeDimensionMapping.Transcoding.SysId;
// holds the dsd codes by rule
var componentsByRule = new Dictionary<long, CodeCollection>();
string sql = string.Format(
CultureInfo.InvariantCulture,
MappingStoreSqlStatements.TranscodingRulesDsdCodes,
paramId,
timeDimensionTranscodingIdParam);
using (DbCommand command = mappingStoreDb.GetSqlStringCommand(sql))
{
mappingStoreDb.AddInParameter(command, ParameterNameConstants.IdParameter, DbType.Int64, sysId);
mappingStoreDb.AddInParameter(command, ParameterNameConstants.TranscodingId, DbType.Int64, timedimensionID);
using (IDataReader dataReader = mappingStoreDb.ExecuteReader(command))
{
int pos = 0;
long prevMapId = -1;
TranscodingRulesEntity currentRules = null;
long firstTrRuleID = -1;
long curTrRuleID = -1;
long prevTrRuleID = -1;
var componentList = new CodeCollection();
int ruleIDIdx = dataReader.GetOrdinal("TR_RULE_ID");
int componentIdx = dataReader.GetOrdinal("COMPONENT");
int codeIdx = dataReader.GetOrdinal("CODE");
int mapIdIdx = dataReader.GetOrdinal("MAP_ID");
while (dataReader.Read())
{
long curMapId = DataReaderHelper.GetInt64(dataReader, mapIdIdx);
if (prevMapId != curMapId || currentRules == null)
{
prevMapId = curMapId;
if (curTrRuleID > -1)
{
componentsByRule.Add(curTrRuleID, componentList);
}
componentList = new CodeCollection();
firstTrRuleID = -1;
prevTrRuleID = -1;
pos = 0;
MappingEntity curretMapping = mappingsMap[curMapId];
currentRules = new TranscodingRulesEntity();
curretMapping.Transcoding.TranscodingRules = currentRules;
}
curTrRuleID = DataReaderHelper.GetInt64(dataReader, ruleIDIdx);
if (firstTrRuleID == -1 || firstTrRuleID == curTrRuleID)
{
firstTrRuleID = curTrRuleID;
prevTrRuleID = curTrRuleID;
currentRules.AddComponent(DataReaderHelper.GetInt64(dataReader, componentIdx), pos);
pos++;
}
else if (prevTrRuleID != curTrRuleID)
{
componentsByRule.Add(prevTrRuleID, componentList);
componentList = new CodeCollection();
prevTrRuleID = curTrRuleID;
}
componentList.Add(DataReaderHelper.GetString(dataReader, codeIdx));
}
if (curTrRuleID > -1)
{
componentsByRule.Add(curTrRuleID, componentList);
}
}
}
if (componentsByRule.Count > 0)
{
sql = string.Format(
CultureInfo.InvariantCulture,
MappingStoreSqlStatements.TranscodingRulesLocalCodes,
paramId,
timeDimensionTranscodingIdParam);
using (DbCommand command = mappingStoreDb.GetSqlStringCommand(sql))
{
mappingStoreDb.AddInParameter(command, ParameterNameConstants.IdParameter, DbType.Int64, sysId);
mappingStoreDb.AddInParameter(command, ParameterNameConstants.TranscodingId, DbType.Int64, timedimensionID);
using (IDataReader dataReader = mappingStoreDb.ExecuteReader(command))
{
long prevMapId = -1;
int pos = 0;
TranscodingRulesEntity currentRules = null;
long firstTrRuleID = -1;
long curTrRuleID = -1;
long prevTrRuleID = -1;
var columnList = new CodeCollection();
int ruleIDIdx = dataReader.GetOrdinal("TR_RULE_ID");
int columnIdx = dataReader.GetOrdinal("LOCAL_COLUMN");
int codeIdx = dataReader.GetOrdinal("CODE");
int mapIdIdx = dataReader.GetOrdinal("MAP_ID");
while (dataReader.Read())
{
long curMapId = DataReaderHelper.GetInt64(dataReader, mapIdIdx);
if (prevMapId != curMapId || currentRules == null)
{
prevMapId = curMapId;
if (curTrRuleID > -1 && currentRules != null)
{
currentRules.Add(columnList, componentsByRule[curTrRuleID]);
}
firstTrRuleID = -1;
prevTrRuleID = -1;
pos = 0;
columnList = new CodeCollection();
MappingEntity curretMapping = mappingsMap[curMapId];
currentRules = curretMapping.Transcoding.TranscodingRules;
}
curTrRuleID = DataReaderHelper.GetInt64(dataReader, ruleIDIdx);
if (firstTrRuleID == -1 || firstTrRuleID == curTrRuleID)
{
firstTrRuleID = curTrRuleID;
prevTrRuleID = curTrRuleID;
currentRules.AddColumn(DataReaderHelper.GetInt64(dataReader, columnIdx), pos);
pos++;
}
else if (prevTrRuleID != curTrRuleID)
{
currentRules.Add(columnList, componentsByRule[prevTrRuleID]);
columnList = new CodeCollection();
prevTrRuleID = curTrRuleID;
}
columnList.Add(DataReaderHelper.GetString(dataReader, codeIdx));
}
if (curTrRuleID > -1 && currentRules != null)
{
currentRules.Add(columnList, componentsByRule[curTrRuleID]);
}
}
}
}
}
/// <summary>
/// This method retrieves a <see cref="DataSetEntity"/> by its identifier
/// </summary>
/// <param name="mappingStoreDb">
/// The <see cref="Database"/> instance for Mapping Store database
/// </param>
/// <param name="datasetId">
/// The dataset identifier, by which the dataset will be retrieved
/// </param>
/// <returns>
/// The populated <see cref="DataSetEntity"/> object
/// </returns>
private static DataSetEntity GetDatasetById(Database mappingStoreDb, long datasetId)
{
DataSetEntity ret = null;
long connectionId = 0;
string paramId = mappingStoreDb.BuildParameterName(ParameterNameConstants.IdParameter);
var sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT DS.DS_ID, DS.NAME, DS.QUERY, DS.CONNECTION_ID ");
sqlCommand.Append("FROM DATASET DS ");
sqlCommand.AppendFormat("WHERE DS.DS_ID = {0} ", paramId);
DbCommand command = mappingStoreDb.GetSqlStringCommand(sqlCommand.ToString());
mappingStoreDb.AddInParameter(command, ParameterNameConstants.IdParameter, DbType.Int64, datasetId);
using (IDataReader dataReader = mappingStoreDb.ExecuteReader(command))
{
// we expect only 1 record here
while (dataReader.Read())
{
ret = new DataSetEntity(DataReaderHelper.GetInt64(dataReader, "DS_ID"))
{
Description = DataReaderHelper.GetString(dataReader, "NAME"),
Query = DataReaderHelper.GetString(dataReader, "QUERY")
};
connectionId = DataReaderHelper.GetInt64(dataReader, "CONNECTION_ID");
}
}
if (ret == null)
{
throw new MappingStoreException(
string.Format(CultureInfo.InvariantCulture, "There was no dataset for Id:{0}", datasetId));
}
ret.Connection = GetConnectionById(mappingStoreDb, connectionId);
return ret;
}
/// <summary>
/// The method retrieves the <see cref="DsdEntity"/> object
/// by the data flow system identifier
/// </summary>
/// <param name="mappingStoreDb">
/// The <see cref="Database"/> instance for Mapping Store database
/// </param>
/// <param name="sysId">
/// The data flow with the specified system identifier
/// </param>
/// <returns>
/// The populated <see cref="DsdEntity"/>object
/// </returns>
private static DsdEntity GetDsdByDataFlowSysId(Database mappingStoreDb, long sysId)
{
DsdEntity ret = null;
string paramId = mappingStoreDb.BuildParameterName(ParameterNameConstants.IdParameter);
var sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT DSD.DSD_ID, ART.ID, ART.VERSION, ART.AGENCY ");
sqlCommand.Append("FROM DSD, DATAFLOW DF, ARTEFACT_VIEW ART ");
sqlCommand.Append("WHERE DF.DSD_ID = DSD.DSD_ID ");
sqlCommand.Append("AND DSD.DSD_ID = ART.ART_ID ");
sqlCommand.AppendFormat("AND DF.DF_ID = {0} ", paramId);
using (DbCommand command = mappingStoreDb.GetSqlStringCommand(sqlCommand.ToString()))
{
mappingStoreDb.AddInParameter(command, ParameterNameConstants.IdParameter, DbType.Int64, sysId);
using (IDataReader dataReader = mappingStoreDb.ExecuteReader(command))
{
// we expect only 1 record here
if (dataReader.Read())
{
ret = new DsdEntity(DataReaderHelper.GetInt64(dataReader, "DSD_ID"))
{
Id = DataReaderHelper.GetString(dataReader, "ID"),
Version = DataReaderHelper.GetString(dataReader, "VERSION"),
Agency = DataReaderHelper.GetString(dataReader, "AGENCY")
};
}
}
}
if (ret == null)
{
throw new MappingStoreException(
string.Format(
CultureInfo.InvariantCulture,
ErrorMessages.MappingStoreNoEntityFormat2,
typeof(DsdEntity),
sysId));
}
// populate the Dsd fields by the correct components
PoulateDsdComponentsByDsdSysId(mappingStoreDb, ret.SysId, ref ret);
return ret;
}
/// <summary>
/// The method retrieves the <see cref="ConceptEntity"/> object
/// by the component system identifier
/// </summary>
/// <param name="mappingStoreDb">
/// The <see cref="Database"/> instance for Mapping Store database
/// </param>
/// <param name="sysId">
/// The component with the specified system identifier
/// </param>
/// <returns>
/// The populated <see cref="ConceptEntity"/>object
/// </returns>
private static ConceptEntity GetConceptByComponentSysId(Database mappingStoreDb, long sysId)
{
ConceptEntity ret = null;
string paramId = mappingStoreDb.BuildParameterName(ParameterNameConstants.IdParameter);
var sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT CON.CON_ID, IT.ID ");
sqlCommand.Append("FROM CONCEPT CON, COMPONENT COMP, ITEM IT ");
sqlCommand.Append("WHERE COMP.CON_ID = CON.CON_ID ");
sqlCommand.Append("AND CON.CON_ID = IT.ITEM_ID ");
sqlCommand.AppendFormat("AND COMP.COMP_ID = {0} ", paramId);
using (DbCommand command = mappingStoreDb.GetSqlStringCommand(sqlCommand.ToString()))
{
mappingStoreDb.AddInParameter(command, ParameterNameConstants.IdParameter, DbType.Int64, sysId);
using (IDataReader dataReader = mappingStoreDb.ExecuteReader(command))
{
// we expect only 1 record here
if (dataReader.Read())
{
ret = new ConceptEntity(DataReaderHelper.GetInt64(dataReader, "CON_ID")) { Id = DataReaderHelper.GetString(dataReader, "ID") };
}
}
}
return ret;
}
/// <summary>
/// This method retrieves a <see cref="ConnectionEntity"/> object
/// by its identifier
/// </summary>
/// <param name="mappingStoreDb">
/// The <see cref="Database"/> instance for Mapping Store database
/// </param>
/// <param name="connectionId">
/// The connection with the specified identifier
/// </param>
/// <returns>
/// The populated <see cref="ConnectionEntity"/>object
/// </returns>
private static ConnectionEntity GetConnectionById(Database mappingStoreDb, long connectionId)
{
ConnectionEntity ret = null;
string paramId = mappingStoreDb.BuildParameterName(ParameterNameConstants.IdParameter);
var sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT CON.CONNECTION_ID, CON.DB_NAME, CON.DB_TYPE, CON.NAME, ");
sqlCommand.Append("CON.DB_PASSWORD, CON.DB_PORT, CON.DB_SERVER, CON.DB_USER, ");
sqlCommand.Append("CON.ADO_CONNECTION_STRING, CON.JDBC_CONNECTION_STRING ");
sqlCommand.Append("FROM DB_CONNECTION CON ");
sqlCommand.AppendFormat("WHERE CON.CONNECTION_ID = {0} ", paramId);
DbCommand command = mappingStoreDb.GetSqlStringCommand(sqlCommand.ToString());
mappingStoreDb.AddInParameter(command, ParameterNameConstants.IdParameter, DbType.Int64, connectionId);
using (IDataReader dataReader = mappingStoreDb.ExecuteReader(command))
{
// we expect only 1 record here
while (dataReader.Read())
{
ret = new ConnectionEntity(DataReaderHelper.GetInt64(dataReader, "CONNECTION_ID"));
ret.DBName = DataReaderHelper.GetString(dataReader, "DB_NAME");
ret.DBType = DataReaderHelper.GetString(dataReader, "DB_TYPE");
ret.Name = DataReaderHelper.GetString(dataReader, "NAME");
// ret.Owner = DataReaderHelper.GetString(dataReader, "OWNER");
ret.DBPassword = DataReaderHelper.GetString(dataReader, "DB_PASSWORD");
//ret.DBPort = DataReaderHelper.GetInt32(dataReader, "DB_PORT");
ret.DBServer = DataReaderHelper.GetString(dataReader, "DB_SERVER");
ret.DBUser = DataReaderHelper.GetString(dataReader, "DB_USER");
ret.AdoConnectionString = DataReaderHelper.GetString(dataReader, "ADO_CONNECTION_STRING");
ret.JdbcConnectionString = DataReaderHelper.GetString(dataReader, "JDBC_CONNECTION_STRING");
}
}
if (ret == null)
{
throw new MappingStoreException(
string.Format(
CultureInfo.InvariantCulture,
ErrorMessages.MappingStoreNoEntityFormat2,
typeof(ConnectionEntity),
connectionId));
}
return ret;
}
/// <summary>
/// The method populates all the <see cref="ComponentEntity">DSD components</see>
/// of a <see cref="DsdEntity"/> object by the data structure definition identifier
/// </summary>
/// <param name="mappingStoreDb">
/// The <see cref="Database"/> instance for Mapping Store database
/// </param>
/// <param name="sysId">
/// The data flow with the specified system identifier
/// </param>
/// <param name="dsd">
/// The populated <see cref="DsdEntity"/>object containing also the
/// <see cref="ComponentEntity">DSD components</see>
/// </param>
private static void PoulateDsdComponentsByDsdSysId(Database mappingStoreDb, long sysId, ref DsdEntity dsd)
{
GroupEntity group;
string id;
string paramId = mappingStoreDb.BuildParameterName(ParameterNameConstants.IdParameter);
var sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT G.GR_ID AS GR_ID, G.ID AS ID, DG.COMP_ID AS COMP_ID ");
sqlCommand.Append("FROM DSD D, DSD_GROUP G, DIM_GROUP DG ");
sqlCommand.AppendFormat("WHERE D.DSD_ID = G.DSD_ID AND G.GR_ID = DG.GR_ID AND D.DSD_ID = {0} ", paramId);
sqlCommand.Append("ORDER BY G.GR_ID");
// used to store the groups given the dimension system id. Is used later to gather actual ComponentVO objects and set them to Group
var groupsFromDim = new Dictionary<long, List<GroupEntity>>();
// holds the groups created given their group id
var groupsFromId = new Dictionary<string, GroupEntity>();
using (DbCommand command = mappingStoreDb.GetSqlStringCommand(sqlCommand.ToString()))
{
mappingStoreDb.AddInParameter(command, ParameterNameConstants.IdParameter, DbType.Int64, sysId);
using (IDataReader dataReader = mappingStoreDb.ExecuteReader(command))
{
// we expect only 1 record here ?
while (dataReader.Read())
{
id = DataReaderHelper.GetString(dataReader, "ID");
// do we have a new group id
if (!groupsFromId.ContainsKey(id))
{
group = new GroupEntity(DataReaderHelper.GetInt64(dataReader, "GR_ID")) { Id = id };
dsd.AddGroup(group);
groupsFromId.Add(id, group);
}
else
{
group = groupsFromId[id];
}
long dimId = DataReaderHelper.GetInt64(dataReader, "COMP_ID");
List<GroupEntity> groupsList;
if (!groupsFromDim.TryGetValue(dimId, out groupsList))
{
groupsList = new List<GroupEntity>();
groupsFromDim.Add(dimId, groupsList);
}
groupsList.Add(group);
}
}
}
sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT COMP.COMP_ID, COMP.ID as CID, COMP.TYPE, COMP.DSD_ID, COMP.CON_ID, COMP.CL_ID, ");
sqlCommand.Append("COMP.IS_FREQ_DIM, COMP.IS_MEASURE_DIM, COMP.ATT_ASS_LEVEL, COMP.ATT_STATUS, ");
sqlCommand.Append("COMP.ATT_IS_TIME_FORMAT, COMP.XS_ATTLEVEL_DS, COMP.XS_ATTLEVEL_GROUP, ");
sqlCommand.Append("COMP.XS_ATTLEVEL_SECTION, COMP.XS_ATTLEVEL_OBS, COMP.XS_MEASURE_CODE, ");
sqlCommand.Append("AG.GR_ID, GR.ID AS GID, MC.ID as MID ");
sqlCommand.Append("FROM DSD INNER JOIN COMPONENT COMP ON DSD.DSD_ID = COMP.DSD_ID ");
sqlCommand.Append("LEFT OUTER JOIN ATT_GROUP AG ON COMP.COMP_ID = AG.COMP_ID ");
sqlCommand.Append("LEFT OUTER JOIN DSD_GROUP GR ON AG.GR_ID = GR.GR_ID ");
sqlCommand.Append("LEFT OUTER JOIN ATT_MEASURE AM ON AM.ATT_COMP_ID = COMP.COMP_ID ");
sqlCommand.Append("LEFT OUTER JOIN COMPONENT MC ON AM.MEASURE_COMP_ID = MC.COMP_ID ");
sqlCommand.AppendFormat("WHERE DSD.DSD_ID = {0} ", paramId);
// make sure that they are returned in the order they were inserted (proper order)
sqlCommand.Append("ORDER BY COMP.COMP_ID ");
// hashmap keeping components already met with key the sys id.
var componentsMap = new Dictionary<long, ComponentEntity>();
using (DbCommand command = mappingStoreDb.GetSqlStringCommand(sqlCommand.ToString()))
{
mappingStoreDb.AddInParameter(command, ParameterNameConstants.IdParameter, DbType.Int64, sysId);
using (IDataReader dataReader = mappingStoreDb.ExecuteReader(command))
{
// GetOrdinal positions
int compIdIdx = dataReader.GetOrdinal("COMP_ID");
int type = dataReader.GetOrdinal("TYPE");
int isFreqDim = dataReader.GetOrdinal("IS_FREQ_DIM");
int isMeasureDim = dataReader.GetOrdinal("IS_MEASURE_DIM");
int attAssLevel = dataReader.GetOrdinal("ATT_ASS_LEVEL");
int attStatus = dataReader.GetOrdinal("ATT_STATUS");
int attIsTimeFormat = dataReader.GetOrdinal("ATT_IS_TIME_FORMAT");
int crossSectionalAttachmentlevelDataSet = dataReader.GetOrdinal("XS_ATTLEVEL_DS");
int attlevelGroup = dataReader.GetOrdinal("XS_ATTLEVEL_GROUP");
int attlevelSection = dataReader.GetOrdinal("XS_ATTLEVEL_SECTION");
int attlevelObs = dataReader.GetOrdinal("XS_ATTLEVEL_OBS");
int measureCode = dataReader.GetOrdinal("XS_MEASURE_CODE");
int gid = dataReader.GetOrdinal("GID");
int cid = dataReader.GetOrdinal("CID");
int mid = dataReader.GetOrdinal("MID");
while (dataReader.Read())
{
long compSysId = DataReaderHelper.GetInt64(dataReader, compIdIdx);
// check if new populate
ComponentEntity component;
if (!componentsMap.TryGetValue(compSysId, out component))
{
component = new ComponentEntity(compSysId);
component.Id = DataReaderHelper.GetString(dataReader, cid);
component.SetType(DataReaderHelper.GetString(dataReader, type));
component.CrossSectionalLevelDataSet = DataReaderHelper.GetBoolean(
dataReader, crossSectionalAttachmentlevelDataSet);
component.CrossSectionalLevelGroup = DataReaderHelper.GetBoolean(dataReader, attlevelGroup);
component.CrossSectionalLevelSection = DataReaderHelper.GetBoolean(
dataReader, attlevelSection);
component.CrossSectionalLevelObs = DataReaderHelper.GetBoolean(dataReader, attlevelObs);
component.CrossSectionalMeasureCode = DataReaderHelper.GetString(dataReader, measureCode);
switch (component.ComponentType)
{
case SdmxComponentType.Dimension:
component.FrequencyDimension = DataReaderHelper.GetBoolean(dataReader, isFreqDim);
component.MeasureDimension = DataReaderHelper.GetBoolean(dataReader, isMeasureDim);
dsd.Dimensions.Add(component);
break;
case SdmxComponentType.Attribute:
component.SetAttachmentLevel(DataReaderHelper.GetString(dataReader, attAssLevel));
component.SetAssignmentStatus(DataReaderHelper.GetString(dataReader, attStatus));
component.AttTimeFormat = DataReaderHelper.GetBoolean(dataReader, attIsTimeFormat);
dsd.Attributes.Add(component);
break;
case SdmxComponentType.TimeDimension:
dsd.TimeDimension = component;
break;
case SdmxComponentType.PrimaryMeasure:
dsd.PrimaryMeasure = component;
break;
case SdmxComponentType.CrossSectionalMeasure:
dsd.CrossSectionalMeasures.Add(component);
break;
}
componentsMap.Add(compSysId, component);
}
// populate for current groups the related assigned groups if any
id = DataReaderHelper.GetString(dataReader, gid);
if (!string.IsNullOrEmpty(id))
{
group = groupsFromId[id];
component.AttAssignmentGroups.Add(group);
}
var measureId = DataReaderHelper.GetString(dataReader, mid);
if (!string.IsNullOrEmpty(measureId))
{
component.AttAttachmentMeasures.Add(measureId);
}
// if component is a dimension used in group definition, add it in their object
List<GroupEntity> groupFromDim;
if (groupsFromDim.TryGetValue(compSysId, out groupFromDim))
{
foreach (GroupEntity groupsItem in groupFromDim)
{
groupsItem.Dimensions.Add(component);
}
}
}
}
}
foreach (var kv in componentsMap)
{
kv.Value.Concept = GetConceptByComponentSysId(mappingStoreDb, kv.Key);
kv.Value.CodeList = GetCodeListByComponentSysId(mappingStoreDb, kv.Key);
}
}
/// <summary>
/// The method retrieves the <see cref="CodeListEntity"/> object
/// by the component system identifier
/// </summary>
/// <param name="mappingStoreDb">
/// The <see cref="Database"/> instance for Mapping Store database
/// </param>
/// <param name="sysId">
/// The component with the specified system identifier
/// </param>
/// <returns>
/// The populated <see cref="CodeListEntity"/>object
/// </returns>
private static CodeListEntity GetCodeListByComponentSysId(Database mappingStoreDb, long sysId)
{
CodeListEntity ret = null;
string paramId = mappingStoreDb.BuildParameterName(ParameterNameConstants.IdParameter);
var sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT CL.CL_ID, A.ID as CODELIST_ID, A.AGENCY, A.VERSION ");
sqlCommand.Append("FROM CODELIST CL, COMPONENT COMP, ARTEFACT_VIEW A ");
sqlCommand.Append("WHERE COMP.CL_ID = CL.CL_ID ");
sqlCommand.Append("AND CL.CL_ID = A.ART_ID ");
sqlCommand.AppendFormat("AND COMP.COMP_ID = {0} ", paramId);
using (DbCommand command = mappingStoreDb.GetSqlStringCommand(sqlCommand.ToString()))
{
mappingStoreDb.AddInParameter(command, ParameterNameConstants.IdParameter, DbType.Int64, sysId);
using (IDataReader dataReader = mappingStoreDb.ExecuteReader(command))
{
// we expect only 1 record here
if (dataReader.Read())
{
ret = new CodeListEntity(DataReaderHelper.GetInt64(dataReader, "CL_ID"))
{
Id = DataReaderHelper.GetString(dataReader, "CODELIST_ID"),
Agency = DataReaderHelper.GetString(dataReader, "AGENCY"),
Version = DataReaderHelper.GetString(dataReader, "VERSION")
};
// ret.CodeList.Add(DataReaderHelper.GetString(dataReader, "CODE_ID"));
// while (dataReader.Read())
// {
// ret.CodeList.Add(DataReaderHelper.GetString(dataReader, "CODE_ID"));
// }
}
}
}
return ret;
}
/// <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>
/// This method populates the international strings
/// </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="partyNames">
/// The <see cref="ITextTypeWrapper"/>lists to be populated in terms of Names
/// </param>
private static void PopulatePartyLocalisedStrings(Database mappingStoreDb, long partySysId, ICollection<ITextTypeWrapper> partyNames)
{
string paramId = mappingStoreDb.BuildParameterName(ParameterNameConstants.IdParameter);
var sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT HLS.HLS_ID, HLS.TYPE, HLS.HEADER_ID, HLS.PARTY_ID, HLS.CONTACT_ID, HLS.LANGUAGE, HLS.TEXT ");
sqlCommand.Append("FROM HEADER_LOCALISED_STRING HLS ");
sqlCommand.AppendFormat("WHERE HLS.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 text = new TextTypeWrapperMutableCore { Locale = DataReaderHelper.GetString(dataReader, "LANGUAGE"), Value = DataReaderHelper.GetString(dataReader, "TEXT") };
string textType = DataReaderHelper.GetString(dataReader, "TYPE");
// is it a sender or a receiver?
if (textType.Equals(NameText, StringComparison.OrdinalIgnoreCase))
{
partyNames.Add(new TextTypeWrapperImpl(text, null));
}
}
}
}
}
/// <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>
/// This method populates the Localized Strings (Names, Departments, Roles)
/// of a <see cref="IContactMutableObject"/>
/// </summary>
/// <param name="mappingStoreDb">
/// The <see cref="Database"/> instance for Mapping Store database
/// </param>
/// <param name="contactSysId">
/// The contact system identifier. In the database the column CONTACT.CONTACT_ID
/// </param>
/// <param name="contact">
/// The <see cref="IContactMutableObject"/> to be populated in terms of Names, Departments, Roles
/// </param>
private static void PopulateContactLocalisedStrings(Database mappingStoreDb, long contactSysId, IContactMutableObject contact)
{
string paramId = mappingStoreDb.BuildParameterName(ParameterNameConstants.IdParameter);
var sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT HLS.HLS_ID, HLS.TYPE, HLS.HEADER_ID, HLS.PARTY_ID, HLS.CONTACT_ID, HLS.LANGUAGE, HLS.TEXT ");
sqlCommand.Append("FROM HEADER_LOCALISED_STRING HLS ");
sqlCommand.AppendFormat("WHERE HLS.CONTACT_ID = {0} ", paramId);
using (DbCommand command = mappingStoreDb.GetSqlStringCommand(sqlCommand.ToString()))
{
mappingStoreDb.AddInParameter(command, ParameterNameConstants.IdParameter, DbType.Int64, contactSysId);
using (IDataReader dataReader = mappingStoreDb.ExecuteReader(command))
{
while (dataReader.Read())
{
var text = new TextTypeWrapperMutableCore { Locale = DataReaderHelper.GetString(dataReader, "LANGUAGE"), Value = DataReaderHelper.GetString(dataReader, "TEXT") };
if (!string.IsNullOrWhiteSpace(text.Value) && !string.IsNullOrWhiteSpace(text.Locale))
{
string textType = DataReaderHelper.GetString(dataReader, "TYPE");
if (textType.Equals(NameText, StringComparison.OrdinalIgnoreCase))
{
contact.Names.Add(text);
}
else if (textType.Equals(DepartmentText, StringComparison.OrdinalIgnoreCase))
{
contact.Departments.Add(text);
}
else if (textType.Equals(RoleText, StringComparison.OrdinalIgnoreCase))
{
contact.Roles.Add(text);
}
}
}
}
}
}
/// <summary>
/// This method populates the contact details of a <see cref="IContactMutableObject"/>
/// </summary>
/// <param name="mappingStoreDb">
/// The <see cref="Database"/> instance for Mapping Store database
/// </param>
/// <param name="contactSysId">
/// The contact system identifier. In the database the column CONTACT.CONTACT_ID
/// </param>
/// <param name="contact">
/// The <see cref="IContactMutableObject"/> for which the details will be populated
/// </param>
private static void PopulateContactDetails(Database mappingStoreDb, long contactSysId, IContactMutableObject contact)
{
string paramId = mappingStoreDb.BuildParameterName(ParameterNameConstants.IdParameter);
var sqlCommand = new StringBuilder();
sqlCommand.Append("SELECT CD.CD_ID, CD.CONTACT_ID, CD.TYPE, CD.VALUE ");
sqlCommand.Append("FROM CONTACT_DETAIL CD ");
sqlCommand.AppendFormat("WHERE CD.CONTACT_ID = {0} ", paramId);
using (DbCommand command = mappingStoreDb.GetSqlStringCommand(sqlCommand.ToString()))
{
mappingStoreDb.AddInParameter(command, ParameterNameConstants.IdParameter, DbType.Int64, contactSysId);
using (IDataReader dataReader = mappingStoreDb.ExecuteReader(command))
{
while (dataReader.Read())
{
string coordinateType = DataReaderHelper.GetString(dataReader, "TYPE");
string coordinateData = DataReaderHelper.GetString(dataReader, "VALUE");
contact.AddCoordinateType(coordinateType, coordinateData);
}
}
}
}
