public LoaderProcedure()
{
_ds = null;
_mapping = null;
_fileCsvData = null;
_filesXmlData = null;
_transcodeTime = null;
}
public BuilderProcedure()
{
_ds = null;
_fileCsvData = null;
_filesXmlData = null;
_mapping = null;
_transcodeTime = null;
_useTranscodeTime = false;
}
public static Dictionary<string, List<string>> GetView(System.IO.Stream stream, TranscodeTime transcode_time = null, int maxResult = 0)
{
Dictionary<string, List<string>> view = new Dictionary<string, List<string>>();
using (System.IO.StreamReader _stream = new System.IO.StreamReader(stream))
{
ReadableDataLocationFactory fact = new ReadableDataLocationFactory();
IReadableDataLocation rdl = fact.GetReadableDataLocation(_stream.BaseStream);
try
{
ISdmxObjectRetrievalManager retrievalManager = DataSDMX.GetManagerImmutable();
Org.Sdmxsource.Sdmx.DataParser.Manager.DataReaderManager dataReader = new Org.Sdmxsource.Sdmx.DataParser.Manager.DataReaderManager();
Org.Sdmxsource.Sdmx.Api.Engine.IDataReaderEngine dataEngine = dataReader.GetDataReaderEngine(rdl, retrievalManager);
if (dataEngine != null)
{
dataEngine.Reset();
#region Data Parsing
while (dataEngine.MoveNextDataset())
{
IDataStructureObject dsd = dataEngine.DataStructure;
List<KeyValuePair<IList<IKeyValue>, IList<IKeyValue>>> groupAttributeDef = new List<KeyValuePair<IList<IKeyValue>, IList<IKeyValue>>>();
Dictionary<string, string> groupAttrTemplate = new Dictionary<string, string>();
while (dataEngine.MoveNextKeyable())
{
IKeyable currentKey = dataEngine.CurrentKey;
if (currentKey.GroupName != null)
{
KeyValuePair<IList<IKeyValue>, IList<IKeyValue>> groupAttrAssignment =
new KeyValuePair<IList<IKeyValue>, IList<IKeyValue>>(currentKey.Key, currentKey.Attributes);
groupAttributeDef.Add(groupAttrAssignment);
foreach (var attr in currentKey.Attributes)
{
string c = attr.Concept.Trim();
if (!view.Keys.Contains(c))
{
view.Add(c, new List<string>());
groupAttrTemplate[c] = null;
}
}
}
while (dataEngine.MoveNextObservation())
{
IObservation obs = dataEngine.CurrentObservation;
var freq = TranscodeTime.TypePeriod.A.ToString();
#region add column for key of attributes at level series
foreach (IKeyValue keyValue in currentKey.Attributes)
{
if (view.Keys.Contains<string>(keyValue.Concept))
{
((List<string>)view[keyValue.Concept]).Add(keyValue.Code.Trim());
}
else
{
view.Add(keyValue.Concept, new List<string>());
((List<string>)view[keyValue.Concept]).Add(keyValue.Code.Trim());
}
}
#endregion
#region add column for key of dim
foreach (IKeyValue keyValue in currentKey.Key)
{
if (keyValue.Concept == dsd.FrequencyDimension.Id)
freq = keyValue.Code.Trim();
if (view.Keys.Contains<string>(keyValue.Concept))
{
((List<string>)view[keyValue.Concept]).Add(keyValue.Code.Trim());
}
else
{
view.Add(keyValue.Concept, new List<string>());
((List<string>)view[keyValue.Concept]).Add(keyValue.Code.Trim());
}
}
#endregion
#region add column for key of attributes to level observation
foreach (IKeyValue keyValue in obs.Attributes)
{
if (view.Keys.Contains<string>(keyValue.Concept))
{
((List<string>)view[keyValue.Concept]).Add(keyValue.Code.Trim());
}
else
{
view.Add(keyValue.Concept, new List<string>());
((List<string>)view[keyValue.Concept]).Add(keyValue.Code.Trim());
}
}
#endregion
#region add columns for key of attributes at level group
Dictionary<string, string> groupAttrValues = new Dictionary<string, string>(groupAttrTemplate);
foreach (var attributeDef in groupAttributeDef)
{
bool setAttribute = true;
foreach (var condition in attributeDef.Key)
{
var viewItem = view[condition.Concept] as List<string>;
if (viewItem == null ||
viewItem.Count == 0 ||
viewItem.Last() != condition.Code)
{
setAttribute = false;
break;
}
}
if (setAttribute)
{
foreach (var attribute in attributeDef.Value)
{
groupAttrValues[attribute.Concept] = attribute.Code;
}
}
}
foreach (var item in groupAttrValues)
((List<string>)view[item.Key]).Add(item.Value);
#endregion
#region add column Cross Sectional Value
if (obs.CrossSection)
if (view.Keys.Contains<string>(obs.CrossSectionalValue.Concept))
{
((List<string>)view[obs.CrossSectionalValue.Concept]).Add(obs.CrossSectionalValue.Code.Trim());
}
else
{
view.Add(obs.CrossSectionalValue.Concept, new List<string>());
((List<string>)view[obs.CrossSectionalValue.Concept]).Add(obs.CrossSectionalValue.Code.Trim());
}
#endregion
#region Apply transcoding
string obsTime = obs.ObsTime;
bool useTranscode = true;
//if (view.Keys.Contains<string>("FREQ"))
if (freq == TranscodeTime.TypePeriod.A.ToString()) useTranscode = false;
if (useTranscode)
{
int sepIndex = obsTime.IndexOf('-');
string period = string.Empty;
string period_change = obsTime[sepIndex].ToString();
int nextChar;
if (!int.TryParse(obsTime[sepIndex + 1].ToString(), out nextChar))
{
period = obsTime[sepIndex + 1].ToString();
period_change += period;
}
else period = TranscodeTime.TypePeriod.M.ToString();
if (view.Keys.Contains<string>("FREQ")) period = freq;
TranscodeTime _transcode_time = new TranscodeTime();
_transcode_time.stopChar = period_change;
_transcode_time.periodChar = (TranscodeTime.TypePeriod)Enum.Parse(typeof(TranscodeTime.TypePeriod), period);
obsTime = _transcode_time.TransCodific(obsTime);
}
#endregion
#region add column TIME_PERIOD
if (view.Keys.Contains<string>("TIME_PERIOD"))
{
((List<string>)view["TIME_PERIOD"]).Add(obsTime);
}
else
{
view.Add("TIME_PERIOD", new List<string>());
((List<string>)view["TIME_PERIOD"]).Add(obsTime);
}
#endregion
#region add column OBS_VALUE
if (view.Keys.Contains<string>("OBS_VALUE"))
{
((List<string>)view["OBS_VALUE"]).Add(obs.ObservationValue.Trim());
}
else
{
view.Add("OBS_VALUE", new List<string>());
((List<string>)view["OBS_VALUE"]).Add(obs.ObservationValue.Trim());
}
#endregion
if (maxResult > 0
&& ((List<string>)view["OBS_VALUE"]).Count >= maxResult)
return view;
}
}
break;
}
#endregion
}
}
catch (Exception ex)
{
//Console.Write(ex.Message);
}
//_stream.Close();
rdl.Close();
}
//return first dataset
return (view.Count > 0) ? view : null;
}
public static List<string> WriteSDMXtoCSV(System.IO.Stream sdmxFile, System.IO.StreamWriter csvOutput, TranscodeTime transcode_time = null, int maxResult = 0)
{
//Dictionary<string, List<string>> view = new Dictionary<string, List<string>>();
char separator = ';';
List<string> currentRow;
List<string> header = new List<string>();
bool writeHeader = true;
using (System.IO.StreamReader _stream = new System.IO.StreamReader(sdmxFile))
{
using (csvOutput)
{
ReadableDataLocationFactory fact = new ReadableDataLocationFactory();
IReadableDataLocation rdl = fact.GetReadableDataLocation(_stream.BaseStream);
try
{
ISdmxObjectRetrievalManager retrievalManager = DataSDMX.GetManagerImmutable();
Org.Sdmxsource.Sdmx.DataParser.Manager.DataReaderManager dataReader = new Org.Sdmxsource.Sdmx.DataParser.Manager.DataReaderManager();
Org.Sdmxsource.Sdmx.Api.Engine.IDataReaderEngine dataEngine = dataReader.GetDataReaderEngine(rdl, retrievalManager);
if (dataEngine != null)
{
dataEngine.Reset();
#region Data Parsing
while (dataEngine.MoveNextDataset())
{
IDataStructureObject dsd = dataEngine.DataStructure;
List<KeyValuePair<IList<IKeyValue>, IList<IKeyValue>>> groupAttributeDef = new List<KeyValuePair<IList<IKeyValue>, IList<IKeyValue>>>();
Dictionary<string, int> headerIndx = new Dictionary<string, int>();
int i = 0;
foreach (var dimension in dsd.GetDimensions())
{
header.Add(dimension.Id);
headerIndx[dimension.Id] = i;
i++;
}
foreach (var attribute in dsd.Attributes)
{
header.Add(attribute.Id);
headerIndx[attribute.Id] = i;
i++;
}
header.Add("OBS_VALUE");
headerIndx["OBS_VALUE"] = i;
while (dataEngine.MoveNextKeyable())
{
IKeyable currentKey = dataEngine.CurrentKey;
if (currentKey.GroupName != null)
{
KeyValuePair<IList<IKeyValue>, IList<IKeyValue>> groupAttrAssignment =
new KeyValuePair<IList<IKeyValue>, IList<IKeyValue>>(currentKey.Key, currentKey.Attributes);
groupAttributeDef.Add(groupAttrAssignment);
}
while (dataEngine.MoveNextObservation())
{
currentRow = new List<string>();
var currentRowArray = new string[header.Count];
for (i = 0; i < currentRowArray.Length; i++) currentRowArray[i] = "";
IObservation obs = dataEngine.CurrentObservation;
var freq = TranscodeTime.TypePeriod.A.ToString();
#region add column for key of attributes at level series
foreach (IKeyValue keyValue in currentKey.Attributes)
{
currentRowArray[headerIndx[keyValue.Concept]] = keyValue.Code;
}
#endregion
#region add column for key of dim
foreach (IKeyValue keyValue in currentKey.Key)
{
currentRowArray[headerIndx[keyValue.Concept]] = keyValue.Code;
if (keyValue.Concept == dsd.FrequencyDimension.Id)
freq = keyValue.Code.Trim();
}
#endregion
#region add column for key of attributes to level observation
foreach (IKeyValue keyValue in obs.Attributes)
{
currentRowArray[headerIndx[keyValue.Concept]] = keyValue.Code;
}
#endregion
#region add columns for key of attributes at level group
foreach (var attributeDef in groupAttributeDef)
{
bool setAttribute = true;
foreach (var condition in attributeDef.Key)
{
if (currentRowArray[headerIndx[condition.Concept]] != condition.Code)
{
setAttribute = false;
break;
}
}
if (setAttribute)
{
foreach (var attribute in attributeDef.Value)
{
currentRowArray[headerIndx[attribute.Concept]] = attribute.Code;
}
}
}
#endregion
#region add column Cross Sectional Value
if (obs.CrossSection)
{
currentRowArray[headerIndx[obs.CrossSectionalValue.Concept]] = obs.CrossSectionalValue.Code.Trim();
}
#endregion
#region Apply transcoding
string obsTime = obs.ObsTime;
bool useTranscode = true;
if (obs.ObsTimeFormat.FrequencyCode == TranscodeTime.TypePeriod.A.ToString())
useTranscode = false;
if (useTranscode)
{
int sepIndex = obsTime.IndexOf('-');
string period = string.Empty;
string period_change = obsTime[sepIndex].ToString();
int nextChar;
if (!int.TryParse(obsTime[sepIndex + 1].ToString(), out nextChar))
{
period = obsTime[sepIndex + 1].ToString();
period_change += period;
}
else period = TranscodeTime.TypePeriod.M.ToString();
if (dsd.HasFrequencyDimension() && currentRowArray[headerIndx[dsd.FrequencyDimension.Id]] != null)
period = currentRowArray[headerIndx[dsd.FrequencyDimension.Id]];
TranscodeTime _transcode_time = new TranscodeTime();
_transcode_time.stopChar = period_change;
_transcode_time.periodChar = (TranscodeTime.TypePeriod)Enum.Parse(typeof(TranscodeTime.TypePeriod), period);
obsTime = _transcode_time.TransCodific(obsTime);
}
#endregion
#region add column TIME_PERIOD
currentRowArray[headerIndx["TIME_PERIOD"]] = obsTime;
#endregion
#region add column OBS_VALUE
if (obs.ObservationValue.Trim() != "NaN")
currentRowArray[headerIndx["OBS_VALUE"]] = obs.ObservationValue.Trim();
#endregion
/*
if (maxResult > 0
&& ((List<string>)view["OBS_VALUE"]).Count >= maxResult)
return view;
*/
if (writeHeader)
{
i = 0;
foreach (var concept in header)
{
if (i > 0) csvOutput.Write(separator);
csvOutput.Write(concept);
i++;
}
csvOutput.Write(Environment.NewLine);
writeHeader = false;
}
int j = 0;
foreach (var item in currentRowArray)
{
if (j > 0) csvOutput.Write(separator);
csvOutput.Write(item);
j++;
}
csvOutput.Write(Environment.NewLine);
}
}
break;
}
#endregion
}
}
catch (Exception ex)
{
//Console.Write(ex.Message);
throw ex;
}
finally
{
rdl.Close();
}
}
}
//returns the list of concepts
return header;
}
public Mapping Set_MAPPING(int IDMappingSchema)
{
Dictionary<string, object> mapping_set = ISTAT.DBDAL.DataAccess.Get_MappingSet(IDMappingSchema);
Dictionary<string, object> mapping_items = ISTAT.DBDAL.DataAccess.Get_MappingScheme(IDMappingSchema);
if (mapping_set != null)
{
_ds = new DataStructure(int.Parse(mapping_set["ID_SET"].ToString()));
_transcodeTime = new ISTAT.ENTITY.TranscodeTime();
_transcodeTime.periodChar = (TranscodeTime.TypePeriod)mapping_set["TRANSCODE_VALUE"];
_transcodeTime.stopChar = (mapping_set["TRANSCODE_CHAR"].ToString() != string.Empty) ? mapping_set["TRANSCODE_CHAR"].ToString() : string.Empty;
_mapping = new Mapping();
_mapping.IDSchema = IDMappingSchema;
_mapping.Name = mapping_set["NAME"].ToString();
_mapping.Time = ((DateTime)mapping_set["TIMESTAMP"]).TimeOfDay;
_mapping.Description = mapping_set["DESCRIPTION"].ToString();
_mapping.TranscodeUse = (bool)mapping_set["TRANSCODE_USE"];
_mapping.TranscodeTime = _transcodeTime;
_mapping.CSV_CHAR = (mapping_set["FILE_CSV_CHAR"].ToString() != string.Empty) ? char.Parse(mapping_set["FILE_CSV_CHAR"].ToString()) : ';';
_mapping.CSV_HASHEADER = (bool)mapping_set["FILE_CSV_HASHEADER"];
if (mapping_items != null)
{
_mapping.Items = new MappingItem[mapping_items.Count];
int i = 0;
foreach (object mapp in mapping_items.Keys)
{
_mapping.Items[i] = new MappingItem();
Dictionary<int, string> c = mapping_items[mapp.ToString()] as Dictionary<int, string>;
_mapping.Items[i]._a = c.Keys.First();
_mapping.Items[i]._b = mapp.ToString();
_mapping.Items[i]._c = c.Values.First();
i++;
}
}
}
return _mapping;
}
public void Set_DataStructure(int IDCube)
{
if (IDCube >= 0)
{
_ds = ISTAT.DBDAL.DataAccess.Get_DataStructure(IDCube);
_transcodeTime = null;
_mapping = null;
}
}
/// <summary>
/// Get_TrancodeTime
/// </summary>
/// <param name="idscheme">ID of mapping</param>
/// <returns>transcode Time</returns>
public static TranscodeTime Get_TranscodeTime(int idscheme)
{
DataWrapper dtw = new DataWrapper(DataWrapper.ECONNECTIONTYPE.SQL, DataAccess.SQLConnString_DB.ConnectionString);
if (dtw.TestConnection())
{
dtw.DBConnection.Open();
try
{
////////////////////////
System.Data.IDbCommand cmd_columns = dtw.DBConnection.CreateCommand();
cmd_columns.CommandType = System.Data.CommandType.StoredProcedure;
cmd_columns.CommandText = "dbo.proc_GET_TRANSCODETIME";
System.Data.IDbDataParameter _param_idschema = cmd_columns.CreateParameter();
_param_idschema.DbType = System.Data.DbType.Int32;
_param_idschema.ParameterName = "IDSchema";
_param_idschema.Value = idscheme;
cmd_columns.Parameters.Add(_param_idschema);
System.Data.IDbDataParameter _param_char = cmd_columns.CreateParameter();
_param_char.DbType = System.Data.DbType.String;
_param_char.ParameterName = "TransChar";
_param_char.Size = 1;
_param_char.Direction = ParameterDirection.Output;
cmd_columns.Parameters.Add(_param_char);
System.Data.IDbDataParameter _param_value = cmd_columns.CreateParameter();
_param_value.DbType = System.Data.DbType.Int32;
_param_value.ParameterName = "TransValue";
_param_value.Direction = ParameterDirection.Output;
cmd_columns.Parameters.Add(_param_value);
cmd_columns.ExecuteNonQuery();
TranscodeTime transcodeTime = new TranscodeTime();
transcodeTime.stopChar = _param_char.Value.ToString();
transcodeTime.periodChar = (TranscodeTime.TypePeriod)int.Parse(_param_value.Value.ToString());
////////////////////////
dtw.DBConnection.Close();
return transcodeTime;
}
catch
{
dtw.DBConnection.Close();
return null;
}
} return null;
}