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