/// <summary>
/// Translates the specified <paramref name="sdmxDate"/> to <see cref="SdmxQueryPeriod"/>
/// </summary>
/// <param name="sdmxDate">The SDMX date.</param>
/// <param name="periodicity">The periodicity.</param>
/// <returns>
/// The <see cref="SdmxQueryPeriod" />.
/// </returns>
public static SdmxQueryPeriod ToQueryPeriod(this ISdmxDate sdmxDate, IPeriodicity periodicity)
{
if (sdmxDate == null)
{
return(null);
}
if (periodicity.TimeFormat.EnumType != sdmxDate.TimeFormatOfDate)
{
sdmxDate = new SdmxDateCore(sdmxDate.Date, periodicity.TimeFormat);
}
var time = new SdmxQueryPeriod();
string[] startTime = sdmxDate.DateInSdmxFormat.Split('-');
var startYear = Convert.ToInt32(startTime[0].Substring(0, 4), _invariantCulture);
time.Year = startYear;
if (startTime.Length >= 2)
{
int startPeriod;
if (int.TryParse(startTime[1].Substring(periodicity.DigitStart), NumberStyles.None, _invariantCulture, out startPeriod))
{
time.HasPeriod = true;
time.Period = startPeriod;
}
}
return(time);
}
/// <summary>
/// Generates the SQL Query where condition from the SDMX Query Time
/// </summary>
/// <param name="dateFrom">
/// The start time
/// </param>
/// <param name="dateTo">
/// The end time
/// </param>
/// <param name="frequencyValue">
/// The frequency value
/// </param>
/// <returns>
/// The string containing SQL Query where condition
/// </returns>
public string GenerateWhere(ISdmxDate dateFrom, ISdmxDate dateTo, string frequencyValue)
{
if (!string.IsNullOrEmpty(frequencyValue))
{
TimeFormat format = TimeFormat.GetTimeFormatFromCodeId(frequencyValue);
if (dateFrom != null)
{
dateFrom = new SdmxDateCore(dateFrom.Date, format.EnumType);
}
if (dateTo != null)
{
dateTo = new SdmxDateCore(dateTo.Date, format.EnumType);
}
}
return(this._timeDimensionMapping.GenerateWhere(dateFrom, dateTo));
}
/// <summary>
/// Generates the SQL Query where condition from the SDMX Query TimeBean <see cref="ISdmxDate"/>
/// </summary>
/// <param name="dateFrom">The start time period</param>
/// <param name="dateTo">The end time period</param>
/// <returns>
/// The string containing SQL Query where condition
/// </returns>
public string GenerateWhere(ISdmxDate dateFrom, ISdmxDate dateTo)
{
if (dateFrom == null && dateTo == null)
{
return(string.Empty);
}
ISdmxDate constantDate = new SdmxDateCore(Mapping.Constant);
var ret = new StringBuilder("(");
bool timeWhereStarted = false;
string normDateFrom = dateFrom != null ? new SdmxDateCore(dateFrom.Date, constantDate.TimeFormatOfDate.EnumType).DateInSdmxFormat : null;
string normDateTo = dateTo != null ? new SdmxDateCore(dateTo.Date, constantDate.TimeFormatOfDate.EnumType).DateInSdmxFormat : null;
bool areEqual = Equals(normDateFrom, normDateTo);
if (normDateFrom != null)
{
var startTime = normDateFrom.Replace("'", "''");
if (areEqual)
{
ret.AppendFormat(this._equalsWhere, startTime);
}
else
{
ret.AppendFormat(this._fromWhere, startTime);
timeWhereStarted = true;
}
}
if (normDateTo != null && !areEqual)
{
if (timeWhereStarted)
{
ret.Append(" and ");
}
var endTime = normDateTo.Replace("'", "''");
ret.AppendFormat(this._toWhere, endTime);
}
ret.Append(") ");
return(ret.ToString());
}
private List <ICodeMutableObject> SpecialCodelist_CL_CONTRAINED(string _dataFlowCode, ISpecialConcept specialConcept)
{
string WhereStatement = "1=1";
string Time = "1=1";
List <string> _ConceptWhere = new List <string>();
if (specialConcept.ContrainConceptREF != null && specialConcept.ContrainConceptREF.Keys.Count > 0)
{
foreach (string otherconept in specialConcept.ContrainConceptREF.Keys.ToList())
{
if (specialConcept.ContrainConceptREF[otherconept] == null)
{
continue;
}
if (new List <string>()
{
"TIME", "TIME_PERIOD"
}.Contains(otherconept.Trim().ToUpper()))
{
if (specialConcept.ContrainConceptREF[otherconept].Count > 0 && specialConcept.TimeDimensionRef != null)
{
string timedim = string.Format(DataMessageManager.FormatWhereValue, ((IDimensionConcept)specialConcept.TimeDimensionRef).GetColumTimeName());
ISdmxDate StartDate = new SdmxDateCore(specialConcept.ContrainConceptREF[otherconept][0].Value);
string STime = TimePeriodDBFormat.GetTimeWhereStatment(timedim, TimePeriodDBFormat.TypeDateOperation.Major, StartDate.TimeFormatOfDate.EnumType, StartDate);
Time = String.Format("({0})", STime);
if (specialConcept.ContrainConceptREF[otherconept].Count == 2)
{
ISdmxDate EndDate = new SdmxDateCore(specialConcept.ContrainConceptREF[otherconept][1].Value);
string ETime = TimePeriodDBFormat.GetTimeWhereStatment(timedim, TimePeriodDBFormat.TypeDateOperation.Minor, EndDate.TimeFormatOfDate.EnumType, EndDate);
Time = String.Format("({0} AND {1})", STime, ETime);
}
}
continue;
}
if (otherconept.Trim().ToUpper() == "FREQ")
{
//Devo controllare se stò in modalità FakeFrequency se cosi
continue;
}
List <string> ConceptInternalWhere = new List <string>();
foreach (var item in specialConcept.ContrainConceptREF[otherconept])
{
ConceptInternalWhere.Add(string.Format("{0}='{1}'", string.Format(DataMessageManager.FormatWhereValue, otherconept), item.Value));
}
_ConceptWhere.Add(string.Format("({0})", string.Join(" OR ", ConceptInternalWhere)));
}
}
WhereStatement = string.Format("{0}", string.Join(" AND ", _ConceptWhere));
if (string.IsNullOrEmpty(WhereStatement))
{
WhereStatement = "1=1";
}
List <IParameterValue> parametri = new List <IParameterValue>()
{
new ParameterValue()
{
Item = "DataSetCode", Value = _dataFlowCode
},
new ParameterValue()
{
Item = "WhereStatement", Value = WhereStatement
},
new ParameterValue()
{
Item = "Time", Value = Time
},
new ParameterValue()
{
Item = "UserName", Value = FlyConfiguration.UserName
},
new ParameterValue()
{
Item = "Domain", Value = FlyConfiguration.Domain
},
};
List <ICodeMutableObject> codes = null;
if (specialConcept.Id.Trim().ToUpper() == "FREQ")
{
codes = GetFrequencyCodelist();
}
else
{
DimensionCodelistsManager dimcm = new DimensionCodelistsManager(this.parsingObject, this.versionTypeResp);
codes = dimcm.InternalGetDimensionCodelistConstrain(_dataFlowCode, specialConcept.Id);
}
List <string> existCode = ((DWHAccess)DbAccess).GetCL_CONTRAINED(parametri, specialConcept.Id);
List <ICodeMutableObject> Contrainedcodes = new List <ICodeMutableObject>();
for (int i = codes.Count - 1; i >= 0; i--)
{
if (existCode.Contains(codes[i].Id))
{
Contrainedcodes.Add(codes[i]);
}
}
for (int i = 0; i < Contrainedcodes.Count; i++)
{
ICodeMutableObject observeCode = Contrainedcodes[i];
while (true)
{
if (!string.IsNullOrEmpty(observeCode.ParentCode) && !Contrainedcodes.Exists(c => c.Id == observeCode.ParentCode))
{
ICodeMutableObject ParentCode = codes.Find(c => c.Id == observeCode.ParentCode);
if (ParentCode != null)
{
Contrainedcodes.Add(ParentCode);
observeCode = ParentCode;
continue;
}
}
break;
}
}
return(Contrainedcodes);
}
