/// <summary> /// Retrieve time transcoding and populate the <paramref name="timeDimensionMapping"/>. /// </summary> /// <param name="mappingStoreDb"> /// The mapping store DB. /// </param> /// <param name="timeDimensionMapping"> /// The time dimension mapping. /// </param> private static void RetrieveTimeTranscoding(Database mappingStoreDb, MappingEntity timeDimensionMapping) { if (timeDimensionMapping == null) { return; } DbParameter parameter = mappingStoreDb.CreateInParameter(ParameterNameConstants.TranscodingId, DbType.Int64, timeDimensionMapping.Transcoding.SysId); using (DbCommand command = mappingStoreDb.GetSqlStringCommandFormat(MappingStoreSqlStatements.TimeTranscoding, parameter)) { using (IDataReader reader = mappingStoreDb.ExecuteReader(command)) { int freqIdx = reader.GetOrdinal("FREQ"); int yearColIdIdx = reader.GetOrdinal("YEAR_COL_ID"); int periodColIdIdx = reader.GetOrdinal("PERIOD_COL_ID"); int dateColIdIdx = reader.GetOrdinal("DATE_COL_ID"); int expressionIdx = reader.GetOrdinal("EXPRESSION"); var timeTranscodingCollection = timeDimensionMapping.Transcoding.TimeTranscodingCollection; while (reader.Read()) { var timeTranscoding = timeTranscodingCollection.Add(DataReaderHelper.GetString(reader, freqIdx)); timeTranscoding.YearColumnId = DataReaderHelper.GetInt64(reader, yearColIdIdx); timeTranscoding.PeriodColumnId = DataReaderHelper.GetInt64(reader, periodColIdIdx); timeTranscoding.DateColumnId = DataReaderHelper.GetInt64(reader, dateColIdIdx); timeTranscoding.Expression = DataReaderHelper.GetString(reader, expressionIdx); } } } }
/// <summary> /// Retrieve TimeDimension transcoding, if <paramref name="timeDimensionMapping"/> is null /// </summary> /// <param name="mappingStoreDb"> /// The mapping store DB. /// </param> /// <param name="timeDimensionMapping"> /// The time dimension mapping. /// </param> private static void RetrieveTimeDimensionTranscoding( Database mappingStoreDb, MappingEntity timeDimensionMapping) { if (timeDimensionMapping == null) { return; } RetrieveTimeTranscoding(mappingStoreDb, timeDimensionMapping); TimeTranscodingCollection timeTranscodingCollection = timeDimensionMapping.Transcoding.TimeTranscodingCollection; if (timeTranscodingCollection.Count == 1 && TimeFormat.GetTimeFormatFromCodeId(timeTranscodingCollection[0].FrequencyValue).EnumType == TimeFormatEnumType.Year) { return; } var codelists = PeriodCodelist.PeriodCodelistIdMap; IDictionary<string, TimeTranscodingEntity> transcodingMap = new Dictionary<string, TimeTranscodingEntity>(StringComparer.Ordinal); foreach (var periodObject in codelists) { if (timeTranscodingCollection.Contains(periodObject.Key)) { var timeTranscodingEntity = timeTranscodingCollection[periodObject.Key]; if (timeTranscodingEntity.PeriodColumnId > 0) { var timeDimensionRules = new TranscodingRulesEntity(); timeDimensionRules.AddColumn(timeTranscodingEntity.PeriodColumnId, 0); timeDimensionRules.AddComponent(timeDimensionMapping.Components[0].SysId, 0); timeTranscodingEntity.TranscodingRules = timeDimensionRules; transcodingMap.Add(periodObject.Value.Id, timeTranscodingEntity); } } } DbParameter parameter = mappingStoreDb.CreateInParameter(ParameterNameConstants.TranscodingId, DbType.Int64, timeDimensionMapping.Transcoding.SysId); using (DbCommand command = mappingStoreDb.GetSqlStringCommandFormat(MappingStoreSqlStatements.TranscodingRulesTimeDimension, parameter)) using (IDataReader dataReader = mappingStoreDb.ExecuteReader(command)) { int dsdcodeIdx = dataReader.GetOrdinal("DSDCODE"); int localcodeIdx = dataReader.GetOrdinal("LOCALCODE"); int codelistId = dataReader.GetOrdinal("CODELIST_ID"); while (dataReader.Read()) { var periodCodelist = DataReaderHelper.GetString(dataReader, codelistId); TimeTranscodingEntity timeTranscodingEntity; if (transcodingMap.TryGetValue(periodCodelist, out timeTranscodingEntity)) { var timeDimensionRules = timeTranscodingEntity.TranscodingRules; var dsdperiod = new CodeCollection(); var localperiod = new CodeCollection(); dsdperiod.Add(DataReaderHelper.GetString(dataReader, dsdcodeIdx)); localperiod.Add(DataReaderHelper.GetString(dataReader, localcodeIdx)); timeDimensionRules.Add(localperiod, dsdperiod); } } } }
/// <summary> /// Add where clauses and populate <paramref name="parameters"/> /// </summary> /// <param name="maintainableRef"> /// The maintainable reference which may contain ID, AGENCY ID and/or VERSION. /// </param> /// <param name="database"> /// The database. /// </param> /// <param name="sqlCommand"> /// The SQL command. /// </param> /// <param name="parameters"> /// The parameters. /// </param> /// <param name="allowedDataflows"> /// The allowed dataflows. /// </param> /// <param name="whereState">the current state of the WHERE clause in <paramref name="sqlCommand"/></param> /// <returns> /// The <paramref name="parameters"/> /// </returns> public static IList<DbParameter> AddWhereClauses(IMaintainableRefObject maintainableRef, Database database, StringBuilder sqlCommand, IList<DbParameter> parameters, ICollection<IMaintainableRefObject> allowedDataflows, WhereState whereState) { if (allowedDataflows == null) { return parameters; } maintainableRef = maintainableRef ?? new MaintainableRefObjectImpl(); switch (whereState) { case WhereState.Nothing: sqlCommand.Append(" WHERE ("); break; case WhereState.Where: break; case WhereState.And: sqlCommand.Append(" AND ("); break; } int lastClause = sqlCommand.Length; int count = 0; bool addedClauses = false; foreach (IMaintainableRefObject allowedDataflow in allowedDataflows) { // TODO check if allowed dataflow id is mandatory. If not we need to change this. if (!maintainableRef.HasMaintainableId() || string.Equals(maintainableRef.MaintainableId, allowedDataflow.MaintainableId)) { string countString = count.ToString(CultureInfo.InvariantCulture); sqlCommand.Append("("); // id string idParam = ParameterNameConstants.IdParameter + countString; sqlCommand.AppendFormat(" A.ID = {0} ", database.BuildParameterName(idParam)); parameters.Add(database.CreateInParameter(idParam, DbType.String, allowedDataflow.MaintainableId)); // version var versionParameters = allowedDataflow.GenerateVersionParameters(database, parameters, "A.VERSION", versionNumber => string.Format(CultureInfo.InvariantCulture, "{0}{1}_{2}", ParameterNameConstants.VersionParameter, versionNumber, countString)); if (versionParameters.Length > 0) { sqlCommand.AppendFormat(CultureInfo.InvariantCulture, "AND {0}", versionParameters); } // agency if (allowedDataflow.HasAgencyId()) { string name = ParameterNameConstants.AgencyParameter + countString; sqlCommand.AppendFormat(" AND A.AGENCY = {0} ", database.BuildParameterName(name)); parameters.Add(database.CreateInParameter(name, DbType.String, allowedDataflow.AgencyId)); } sqlCommand.Append(" ) "); lastClause = sqlCommand.Length; sqlCommand.Append(" OR "); count++; addedClauses = true; } } sqlCommand.Length = lastClause; if (!addedClauses) { sqlCommand.Append("'ACCESS' = 'DENIED'"); } sqlCommand.Append(" ) "); return parameters; }