/// <summary>
/// Getter for time dimension custom codelist. The codelist will contain two codes, one with the start period and the other with end period
/// </summary>
/// <returns>
/// The time dimension custom codelist
/// </returns>
private CodeListBean GetTimeDimensionStartEnd()
{
ICollection<DataSetColumnEntity> dataSetColumns = this._timeMapping.Columns;
var codelist = new CodeListBean();
if (dataSetColumns != null && dataSetColumns.Count > 0)
{
this._logger.LogInformation("|-- Generating SQL WHERE for dissemination database...");
string sqlWhere = this.GenerateWhere(this._criteria);
this._logger.LogInformation("|-- Generating SQL for dissemination database...");
string sql = this.GenerateSql(this._timeMapping, sqlWhere);
this._logger.LogInformation("|-- SQL for dissemination database generated:\n" + sql);
this.ExecuteSql(this._timeTranscoder, codelist, sql);
}
else
{
var min = new CodeBean();
min.Id = this._timeMapping.Constant;
SetupTimeCodelist(min, null, codelist);
}
return codelist;
}
/// <summary>
/// Execute the query generated from <see cref="GenerateSql"/> against the DDB and populate the given set of available codes.
/// </summary>
/// <param name="timeTranscoding">
/// The ITimeDimension implementation to use for mapping and transcoding
/// </param>
/// <param name="timeCodeList">
/// The Codelist to populate with the min and max values
/// </param>
/// <param name="sqlQuery">
/// The SQL Query to execute
/// </param>
/// <exception cref="DbException">
/// DDB communication error
/// </exception>
private void ExecuteSql(ITimeDimension timeTranscoding, CodeListBean timeCodeList, string sqlQuery)
{
var max = new CodeBean();
var min = new CodeBean();
max.Id = CustomCodelistConstants.TimePeriodMin;
min.Id = CustomCodelistConstants.TimePeriodMax;
using (DbConnection ddbConnection = this.CreateDdbConnection())
{
using (DbCommand cmd = ddbConnection.CreateCommand())
{
SetupUpCmd(cmd);
cmd.CommandText = sqlQuery;
using (IDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
string period = timeTranscoding.MapComponent(reader);
if (!string.IsNullOrEmpty(period))
{
if (string.CompareOrdinal(period, min.Id) < 0)
{
min.Id = period;
}
if (string.CompareOrdinal(period, max.Id) > 0)
{
max.Id = period;
}
}
}
}
}
}
if (min.Id.Equals(max.Id))
{
max = null;
}
SetupTimeCodelist(min, max, timeCodeList);
// TODO discuss this
// if (min.Id.Equals("9999")) {
// timeCodeList.Items.Clear();
// }
}
/// <summary>
/// Getter for the special data count request
/// </summary>
/// <returns>
/// A codelist with one code, the number of data
/// </returns>
private CodeListBean GetCountCodelist()
{
var codelist = new CodeListBean();
var name = new TextTypeBean(CustomCodelistConstants.CountCodeListName, CustomCodelistConstants.Lang);
codelist.Names.Add(name);
codelist.Id = CustomCodelistConstants.CountCodeList;
codelist.AgencyId = CustomCodelistConstants.Agency;
codelist.Version = CustomCodelistConstants.Version;
this._logger.LogInformation("|-- Generating SQL WHERE for dissemination database...");
string sqlWhere = this.GenerateWhere(this._criteria);
this._logger.LogInformation("|-- Generating SQL for dissemination database...");
string sql = this.GenerateCountSql(sqlWhere);
this._logger.LogInformation("|-- SQL for dissemination database generated:\n" + sql);
int xsMeasureMult = 1;
if (this._measureComponent != null)
{
this._logger.LogInformation("|-- Get XS Measure count");
xsMeasureMult = this.GetXsMeasureCount(this._criteria);
}
object value = this.ExecuteSql(sql);
// setup count codelist
var countCode = new CodeBean();
var text = new TextTypeBean(CustomCodelistConstants.CountCodeDescription, CustomCodelistConstants.Lang);
countCode.Descriptions.Add(text);
// normally count(*) should always return a number. Checking just in case I missed something.
if (value != null && !Convert.IsDBNull(value))
{
long count = Convert.ToInt64(value, CultureInfo.InvariantCulture);
// in .net, oracle will return 128bit decimal, sql server 32bit int, while mysql & sqlite 64bit long.
// check if there are XS measure mappings. In this case there could be multiple measures/obs per row.
// even if they are not, then will be static mappings
count *= xsMeasureMult;
countCode.Id = count.ToString(CultureInfo.InvariantCulture);
codelist.AddItem(countCode);
}
else
{
countCode.Id = CustomCodelistConstants.CountCodeDefault;
}
return codelist;
}
/// <summary>
/// Setup a CodelistBean object as a TimeDimension codelist containing two codes, startTime and endTime and using as codelist id the <see cref="CustomCodelistConstants.TimePeriodCodeList"/>.
/// </summary>
/// <param name="startTime">
/// The code that will contain the start Time period
/// </param>
/// <param name="endTime">
/// The code that will contain the end Time period
/// </param>
/// <param name="timeCodeList">
/// The codelist to setup
/// </param>
private static void SetupTimeCodelist(CodeBean startTime, CodeBean endTime, CodeListBean timeCodeList)
{
timeCodeList.Id = CustomCodelistConstants.TimePeriodCodeList;
timeCodeList.AgencyId = CustomCodelistConstants.Agency;
timeCodeList.Version = CustomCodelistConstants.Version;
timeCodeList.Names.Add(
new TextTypeBean(CustomCodelistConstants.TimePeriodCodeListName, CustomCodelistConstants.Lang));
startTime.Descriptions.Add(new TextTypeBean(CustomCodelistConstants.TimePeriodStartDescription, CustomCodelistConstants.Lang));
timeCodeList.AddItem(startTime);
if (endTime != null)
{
endTime.Descriptions.Add(new TextTypeBean(CustomCodelistConstants.TimePeriodEndDescription, CustomCodelistConstants.Lang));
timeCodeList.AddItem(endTime);
}
}