/// <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); } } } }