/// <summary>
/// Parsing a Selections query
/// </summary>
/// <param name="querys">Selection</param>
/// <param name="_freqWhere">return a string that representing a TimeWhere to pass a database parameter</param>
/// <returns>string representing the selection formatted</returns>
private string parseWhereSelection(IDataWhereStatmentSelection querys, out string _freqWhere)
{
try
{
_freqWhere = null;
List <string> SingleValues = new List <string>();
IConceptObjectImpl realConceptFound = Concepts.Find(c => (c.Id.Trim().ToUpper() == querys.ComponentId.Trim().ToUpper()) || CheckRenamedFrequency(c, querys.ComponentId));
if (realConceptFound == null)
{
throw new SdmxException(this, FlyExceptionObject.FlyExceptionTypeEnum.DimensionNotFound, new Exception(querys.ComponentId));
}
IConceptObjectImpl realConceptTime = Concepts.Find(c => c.ConceptType == ConceptTypeEnum.Dimension && ((IDimensionConcept)c).DimensionType == DimensionTypeEnum.Time);
string timedim = string.Format(FormatWhereValue, ((IDimensionConcept)realConceptTime).GetColumTimeName());
if (realConceptFound is IDimensionConcept && ((IDimensionConcept)realConceptFound).DimensionType == DimensionTypeEnum.Frequency)
{
IDimensionConcept FreqConcept = (IDimensionConcept)realConceptFound;
if (!FreqConcept.IsFakeFrequency)
{
string dim = string.Format(FormatWhereValue, FreqConcept.RealNameFreq);
foreach (string val in querys.Values)
{
SingleValues.Add(string.Format("{0}='{1}'", dim, val));
}
}
else
{
_freqWhere = TimePeriodDBFormat.GetFreqValueWhereStatment(querys.Values, timedim);
}
}
else if (realConceptFound is IDimensionConcept)
{
string dim = string.Format(FormatWhereValue, realConceptFound.ConceptObjectCode);
foreach (string val in querys.Values)
{
SingleValues.Add(string.Format("{0}='{1}'", dim, val));
}
}
else if (realConceptFound is IAttributeConcept)
{//Gestione degli attributi
if (realConceptFound.Id == FlyConfiguration.Time_Format_Id)
{
_freqWhere = TimePeriodDBFormat.GetTimeFormatValueWhereStatment(querys.Values, timedim);
}
}
if (SingleValues.Count == 0)
{
return(null);
}
return(string.Format("({0})", string.Join(" OR ", SingleValues)));
}
catch (SdmxException) { throw; }
catch (Exception ex)
{
throw new SdmxException(this, FlyExceptionObject.FlyExceptionTypeEnum.InternalError, ex);
}
}
/// <summary>
/// Parse a time where condition
/// </summary>
/// <param name="DateFrom">Date From in Sdmx Format</param>
/// <param name="DateTo">Date To in Sdmx Format</param>
/// <returns>string representing the date where formatted</returns>
private string ParseTimeValueWhereStatment(ISdmxDate DateFrom, ISdmxDate DateTo)
{
try
{
IConceptObjectImpl realConceptTime = Concepts.Find(c => c.ConceptType == ConceptTypeEnum.Dimension && ((IDimensionConcept)c).DimensionType == DimensionTypeEnum.Time);
string timedim = string.Format(FormatWhereValue, ((IDimensionConcept)realConceptTime).GetColumTimeName());
List <string> SingleValues = new List <string>();
//dò per scontato che i timeFormat delle 2 date siano Uguali
TimeFormatEnumType tf = TimeFormatEnumType.Null;
if (DateFrom != null && DateFrom.Date.HasValue)
{
tf = DateFrom.TimeFormatOfDate.EnumType;
string wherestr = TimePeriodDBFormat.GetTimeWhereStatment(timedim, TimePeriodDBFormat.TypeDateOperation.Major, tf, DateFrom);
if (!string.IsNullOrEmpty(wherestr))
{
SingleValues.Add(wherestr);
}
}
if (DateTo != null && DateTo.Date.HasValue)
{
if (tf == TimeFormatEnumType.Null)
{
tf = DateTo.TimeFormatOfDate.EnumType;
}
string wherestr = TimePeriodDBFormat.GetTimeWhereStatment(timedim, FlyController.Model.TimePeriodDBFormat.TypeDateOperation.Minor, tf, DateTo);
if (!string.IsNullOrEmpty(wherestr))
{
SingleValues.Add(wherestr);
}
}
if (SingleValues.Count > 0)
{
return(string.Format("({0})", string.Join(") AND (", SingleValues)));
}
return(null);
}
catch (SdmxException) { throw; }
catch (Exception ex)
{
throw new SdmxException(this, FlyExceptionObject.FlyExceptionTypeEnum.InternalError, ex);
}
}
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);
}
//public void GetCL_TIME_PERIOD(List<IParameterValue> parameter, out string FirstTime, out string EndTime)
//{
// try
// {
// _conn = new SqlConnection();
// _conn.ConnectionString = this.ConnectionString;
// _conn.Open();
// SqlCommand cmd = new SqlCommand(GetStoreRef(DBOperationEnum.GetData), _conn);
// cmd.CommandType = CommandType.StoredProcedure;
// if (parameter != null)
// parameter.ForEach(p => cmd.Parameters.Add(((ParameterValue)p).CreateParameter()));
// cmd.CommandTimeout = int.MaxValue;
// FirstTime = null;
// EndTime = null;
// string TimeColum = null;
// using (SqlDataReader rea = cmd.ExecuteReader())
// {
// if (string.IsNullOrEmpty(TimeColum))
// {
// DataTable schemaTable = rea.GetSchemaTable();
// foreach (DataRow item in schemaTable.Rows)
// {
// string itemColumnName = item["ColumnName"].ToString();
// if (itemColumnName.Trim().ToUpper() == SpacialColumsNamesEnum._TIME.ToString() ||
// itemColumnName.Trim().ToUpper() == SpacialColumsNamesEnum._TIME_.ToString())
// {
// TimeColum = itemColumnName;
// break;
// }
// }
// if (string.IsNullOrEmpty(TimeColum))
// throw new SdmxException(this, FlyExceptionObject.FlyExceptionTypeEnum.DBErrorResponse, new Exception("Not Found Time Colums in DB"));
// }
// FlyLog.WriteLog(this, FlyLog.LogTypeEnum.All, @"Execution Store procedure {0} for Get CL_COUNT request Successfully. Reading results", cmd.CommandText);
// while (rea.Read())
// {
// string ActDate = rea[TimeColum].ToString();
// if (ActDate.Trim().Length < 4)
// continue;
// if (string.IsNullOrEmpty(FirstTime))
// {
// FirstTime = ActDate.Trim().Substring(0, 4);
// EndTime = ActDate.Trim().Substring(0, 4);
// continue;
// }
// if (ActDate.Trim().Substring(0, 4).CompareTo(FirstTime) < 0)
// FirstTime = ActDate.Trim().Substring(0, 4);
// if (ActDate.Trim().Substring(0, 4).CompareTo(EndTime) > 0)
// EndTime = ActDate.Trim().Substring(0, 4);
// }
// }
// }
// catch (SdmxException) { throw; }
// catch (Exception e)
// {
// throw new SdmxException(this, FlyExceptionObject.FlyExceptionTypeEnum.DBErrorResponse, e);
// }
//}
//public int GetCL_COUNT(List<IParameterValue> parameter)
//{
// try
// {
// _conn = new SqlConnection();
// _conn.ConnectionString = this.ConnectionString;
// _conn.Open();
// SqlCommand cmd = new SqlCommand(GetStoreRef(DBOperationEnum.GetData), _conn);
// cmd.CommandType = CommandType.StoredProcedure;
// if (parameter != null)
// parameter.ForEach(p => cmd.Parameters.Add(((ParameterValue)p).CreateParameter()));
// cmd.CommandTimeout = int.MaxValue;
// int Counter = 0;
// using (SqlDataReader rea = cmd.ExecuteReader())
// {
// FlyLog.WriteLog(this, FlyLog.LogTypeEnum.All, @"Execution Store procedure {0} for Get CL_COUNT request Successfully. Reading results", cmd.CommandText);
// while (rea.Read())
// Counter++;
// }
// return Counter;
// }
// catch (SdmxException) { throw; }
// catch (Exception e)
// {
// throw new SdmxException(this, FlyExceptionObject.FlyExceptionTypeEnum.DBErrorResponse, e);
// }
//}
//public List<string> GetCL_CONTRAINED(List<IParameterValue> parameter, string ConceptColum)
//{
// try
// {
// _conn = new SqlConnection();
// _conn.ConnectionString = this.ConnectionString;
// _conn.Open();
// SqlCommand cmd = new SqlCommand(GetStoreRef(DBOperationEnum.GetData), _conn);
// cmd.CommandType = CommandType.StoredProcedure;
// if (parameter != null)
// parameter.ForEach(p => cmd.Parameters.Add(((ParameterValue)p).CreateParameter()));
// cmd.CommandTimeout = int.MaxValue;
// List<string> CodeObject = new List<string>();
// using (SqlDataReader rea = cmd.ExecuteReader())
// {
// bool ExistColum = false;
// DataTable schemaTable = rea.GetSchemaTable();
// string TimeCol = "";
// foreach (DataRow item in schemaTable.Rows)
// {
// string itemColumnName = item["ColumnName"].ToString();
// if (itemColumnName.Trim().ToUpper() == ConceptColum.Trim().ToUpper())
// {
// ConceptColum = itemColumnName;
// ExistColum = true;
// }
// else if (itemColumnName.Trim().ToUpper() == SpacialColumsNamesEnum._TIME.ToString() ||
// itemColumnName.Trim().ToUpper() == SpacialColumsNamesEnum._TIME_.ToString())
// {
// TimeCol = itemColumnName;
// }
// }
// StringBuilder Par = new StringBuilder();
// foreach (var item in parameter)
// Par.AppendLine(String.Format("{0} \t\t-> {1}", item.Item, item.Value));
// FlyLog.WriteLog(this, FlyLog.LogTypeEnum.All, @"Execution Store procedure {0} for Get CL_COUNT request Successfully. Reading results {1}", cmd.CommandText, Par);
// while (rea.Read())
// {
// if (!ExistColum)
// {
// if (ConceptColum.Trim().ToUpper() == "FREQ")
// {
// string TVal = (TimePeriodDBFormat.GetFrequencyValueFromTime(rea[TimeCol].ToString()));
// if (!CodeObject.Contains(TVal))
// CodeObject.Add(TVal);
// }
// else
// break;
// }
// else
// {
// string Val = rea[ConceptColum].ToString();
// if (!CodeObject.Contains(Val))
// CodeObject.Add(Val);
// }
// }
// }
// return CodeObject;
// }
// catch (SdmxException) { throw; }
// catch (Exception e)
// {
// throw new SdmxException(this, FlyExceptionObject.FlyExceptionTypeEnum.DBErrorResponse, e);
// }
//}
#endregion
/// <summary>
/// Return a CodelistConstrained with other Dimension in Join
/// </summary>
/// <param name="parameter">Parameters to be passed to the store procedure</param>
/// <param name="ConceptColum">List of Other Dimension separated by Comma</param>
/// <returns></returns>
public List <string> GetCL_CONTRAINED(List <IParameterValue> parameter, string ConceptColum)
{
try
{
_conn = new SqlConnection();
_conn.ConnectionString = this.ConnectionString;
_conn.Open();
string datasetCode = parameter.Find(p => p.Item == "DataSetCode").Value.ToString();
string WhereStatement = parameter.Find(p => p.Item == "WhereStatement").Value.ToString().Replace("'", "''");
string TimePar = parameter.Find(p => p.Item == "Time").Value.ToString().Replace("'", "''");
string SqlQuery = string.Format(@"
declare @DataSetCode varchar(100) = '{0}'
declare @ColumDistinct varchar(100) = '{1}'
declare @WhereStatement varchar(max)='{2}'
declare @Time varchar(max) = '{3}'
declare @TableID nvarchar(255);
declare @TableName nvarchar(255);
declare @req_WherePart as varchar(max);
SET @TableID =
(
SELECT CatSet.[IDSet] FROM CatSet WHERE CatSet.Code=@DataSetCode
)
SET @TableName = 'Dataset_'+@TableID+'_ViewAllData';
SET @req_WherePart=@WhereStatement
if(RTRIM(@Time) <> '')
SET @req_WherePart = @req_WherePart +' AND '+ @Time
Set @req_WherePart=@WhereStatement
if(RTRIM(@Time) <> '')
SET @req_WherePart = @req_WherePart +' AND '+ @Time
SELECT
@req_WherePart=
(CASE WHEN CD.IsTimeSeriesDim=1 THEN replace(@req_WherePart,'[$TIME_PERIOD$].Code', '[_'+CD.CODE+']')
ELSE replace(@req_WherePart,'[$'+CD.CODE+'$].Code', '[_'+CD.CODE+']')
END
)
from CatDim as CD
where CD.IDSet=@TableID
declare @TimeCol as varchar(100)
select TOP 1 @TimeCol= Code from CatDim where IDSet=@TableID AND IsTimeSeriesDim=1
SET @ColumDistinct=REPLACE(@ColumDistinct,'TIME_PERIOD',@TimeCol);
exec('select DISTINCT _'+@ColumDistinct+' from '+@TableName + ' where ' + @req_WherePart)",
datasetCode, (ConceptColum.Trim().ToUpper() == "FREQ" ? "TIME_PERIOD" : ConceptColum), WhereStatement, TimePar);
SqlCommand cmd = new SqlCommand(SqlQuery, _conn);
cmd.CommandTimeout = int.MaxValue;
cmd.CommandType = CommandType.Text;
List <string> CodeObject = new List <string>();
using (SqlDataReader rea = cmd.ExecuteReader())
{
while (rea.Read())
{
if (ConceptColum.Trim().ToUpper() == "FREQ")
{
string TVal = (TimePeriodDBFormat.GetFrequencyValueFromTime(rea[0].ToString()));
if (!CodeObject.Contains(TVal))
{
CodeObject.Add(TVal);
}
}
else
{
string Val = rea[0].ToString();
if (!CodeObject.Contains(Val))
{
CodeObject.Add(Val);
}
}
}
}
_conn.Close();
return(CodeObject);
}
catch (SdmxException) { throw; }
catch (Exception e)
{
throw new SdmxException(this, FlyExceptionObject.FlyExceptionTypeEnum.DBErrorResponse, e);
}
}