public static DIQueries GetMapServerQueries(string DataPrefix_UI, string LanguageCode_UI, DIConnection connection)
{
//TO Do GetConnection from web service
DIQueries RetVal = null;
try
{
if (connection.DIDataSets().Select(DBAvailableDatabases.AvlDBPrefix + "='" + DataPrefix_UI.Trim('_') + "'").Length == 0)
{
DataPrefix_UI = connection.DIDataSetDefault();
}
if (connection.DILanguages(DataPrefix_UI).Select(Language.LanguageCode + "='" + LanguageCode_UI + "'").Length == 0)
{
LanguageCode_UI = connection.DILanguageCodeDefault(DataPrefix_UI);
}
RetVal = new DIQueries(DataPrefix_UI, LanguageCode_UI);
}
catch (Exception ex)
{
throw ex;
}
return RetVal;
}
public static DataTable GetAllDBLangaugeCodesByDbNid(int DBNid, DIConnection diConnection)
{
DataTable RetVal = null;
//DIConnection DIConnection;
if (DBNid != 0)
{
//DIConnection = Global.GetDbConnection(DBNid);
RetVal = diConnection.DILanguages(diConnection.DIDataSetDefault());
}
return RetVal;
}
/// <summary>
/// Get all available language code from database
/// </summary>
/// <param name="diConnection"></param>
/// <returns></returns>
public static string[] GetAllAvailableLanguageCode(DIConnection diConnection)
{
string[] RetVal;
DataTable DTAvailableLanguages = null;
List<string> AvlLanguages = new List<string>();
try
{
DTAvailableLanguages = diConnection.DILanguages(diConnection.DIDataSetDefault());
foreach (DataRow Row in DTAvailableLanguages.Rows)
{
AvlLanguages.Add(Row["Language_Code"].ToString());
}
}
catch (Exception ex)
{
Global.CreateExceptionString(ex, null);
}
RetVal = AvlLanguages.ToArray();
return RetVal;
}
/// <summary>
/// Generate_Data for Country DSD SDMX-ML v2.0
/// </summary>
/// <param name="DictMapping">Key - DevInfo GId; Value - UNSD GID</param>
/// <param name="OutputFolder">SDMX-ML folder path</param>
/// <param name="DIConnection"></param>
/// <param name="DIQueries"></param>
/// <param name="fileCount">Return Count of SDMX-ML files generated for all language</param>
/// <param name="AreaId"></param>
/// <param name="DBOrDSDDBId">dbnid of devinfo database / country dsd</param>
/// <param name="GeneratedFiles">list unique SDMX-ML file generated (single file name for multiple language)</param>
/// <param name="HeaderfilePath"></param>
/// <param name="xml">In case of optimize database - user selection through databuplish xml</param>
/// <param name="DuplicateKey">Return knowldege of Duplicate key for indicator and time</param>
/// <param name="dtSelections">In case of Register - user selection in form of datatable [ind, Area]</param>
/// <param name="ErrorLogs">Return indicator@@Timeperiod}} </param>
/// <param name="GeneratedIndicatorCountryGIDS">Return list of Country GUID for which files have been generated</param>
/// <returns></returns>
internal static bool Generate_Data(Dictionary<string, string> DictMapping, string OutputFolder, DIConnection DIConnection, DIQueries DIQueries, string AreaId, string DBOrDSDDBId, string HeaderfilePath, string xml, DataTable dtSelections, out int fileCount, out List<string> GeneratedFiles, out string ErrorLogs, out string DuplicateKey)
{
bool RetVal;
string Language;
string IndicatorGId, UnitGId, SGVGId, AreaID;
string AreaNIds, TPNIds, SourceNIds;
string SeriesValue, Unit, Age, Sex, Location, Frequency, SourceType, Nature, UnitMult, TimePeriod, ObsVal, TimeDetail, SourceDetail, Footnotes, IndicatorName;
ErrorLogs = string.Empty;
List<string> ProcessedIUSA;
DataTable DtData;
DataRow[] IUSARows;
DataRow[] CheckDuplicateKeys;
XmlDocument AttributesDoc;
System.Xml.XmlAttribute Attribute;
CompactDataType CompactData;
SDMXApi_2_0.CompactData.SeriesType Series;
SDMXApi_2_0.CompactData.ObsType Obs;
Dictionary<string, string> DictAreaMapping;
DateTime CurrentTime;
RetVal = true;
fileCount = 0;
Language = string.Empty;
IndicatorGId = string.Empty;
UnitGId = string.Empty;
SGVGId = string.Empty;
AreaID = string.Empty;
SeriesValue = string.Empty;
Unit = string.Empty;
Age = string.Empty;
Sex = string.Empty;
Location = string.Empty;
Frequency = string.Empty;
SourceType = string.Empty;
Nature = string.Empty;
UnitMult = string.Empty;
TimePeriod = string.Empty;
ObsVal = string.Empty;
TimeDetail = string.Empty;
SourceDetail = string.Empty;
Footnotes = string.Empty;
int RowCount = 0;
ProcessedIUSA = new List<string>();
DtData = null;
IUSARows = null;
CheckDuplicateKeys = null;
AttributesDoc = null;
Attribute = null;
CompactData = null;
Series = null;
Obs = null;
DictAreaMapping = null;
CurrentTime = DateTime.Now;
IndicatorName = string.Empty;
XmlDocument UploadedHeaderXml = new XmlDocument();
DataSet ds = new DataSet();
SDMXApi_2_0.Message.StructureType UploadedDSDStructure = new SDMXApi_2_0.Message.StructureType();
SDMXApi_2_0.Message.HeaderType Header = new SDMXApi_2_0.Message.HeaderType();
DuplicateKey = string.Empty;
DataTable dtIndicator = new DataTable();
if (File.Exists(HeaderfilePath))
{
UploadedHeaderXml.Load(HeaderfilePath);
UploadedDSDStructure = (SDMXApi_2_0.Message.StructureType)SDMXApi_2_0.Deserializer.LoadFromXmlDocument(typeof(SDMXApi_2_0.Message.StructureType), UploadedHeaderXml);
Header = UploadedDSDStructure.Header;
}
GeneratedFiles = new List<string>();
if (dtSelections != null && dtSelections.Rows.Count > 0)
{
// register tab
ds.Tables.Add(dtSelections);
}
else
{
// Admin
if (File.Exists(xml))
{
ds.ReadXml(xml);
}
}
string IndicatorNId = string.Empty;
// for each language in database
foreach (DataRow LanguageRow in DIConnection.DILanguages(DIQueries.DataPrefix).Rows)
{
//
Language = LanguageRow[DevInfo.Lib.DI_LibDAL.Queries.DIColumns.Language.LanguageCode].ToString();
int dsRowCount = ds.Tables["Data"].Rows.Count;
RowCount = 0;
// consider only those indiactors which are selected
foreach (DataRow DSRow in ds.Tables["Data"].Select("selectedState=true"))
{
if (RowCount > dsRowCount)
{
break;
}
IndicatorNId = DSRow["Ind"].ToString();
AreaNIds = DSRow["areas"].ToString();
TPNIds = DSRow["timeperiods"].ToString();
SourceNIds = DSRow["source"].ToString();
// Get data based on Indiactor and associated fileters
DtData = Get_Language_Specific_Data_Table(Language, IndicatorNId, AreaNIds, TPNIds, SourceNIds, DIConnection);
dtIndicator = DIConnection.ExecuteDataTable(DIQueries.Indicators.GetIndicator(FilterFieldType.NId, IndicatorNId, FieldSelection.Heavy));
foreach (DataRow drIndicator in dtIndicator.Rows)
{
IndicatorName = drIndicator[Indicator.IndicatorName].ToString();
}
ProcessedIUSA = new List<string>();
DictAreaMapping = RegTwoZeroFunctionality.Get_Area_Mapping_Dict(Convert.ToInt32(DBOrDSDDBId));
if ((DtData != null) && (DtData.Rows.Count > 0))
{
fileCount += 1;
CompactData = new CompactDataType();
if (!File.Exists(HeaderfilePath))
{
CompactData.Header = RegTwoZeroFunctionality.Get_Appropriate_Header();
}
else
{
CompactData.Header = Header;
}
CompactData.DataSet = new SDMXApi_2_0.CompactData.DataSetType();
CompactData.DataSet.ListSeries = new List<SDMXApi_2_0.CompactData.SeriesType>();
// for each data value for indicator
foreach (DataRow DrData in DtData.Rows)
{
IndicatorGId = DrData[Indicator.IndicatorGId].ToString();
UnitGId = DrData[DevInfo.Lib.DI_LibDAL.Queries.DIColumns.Unit.UnitGId].ToString();
SGVGId = DrData[SubgroupVals.SubgroupValGId].ToString();
if (DictAreaMapping.ContainsKey(DrData[Area.AreaID].ToString()))
{
AreaId = DictAreaMapping[DrData[DevInfo.Lib.DI_LibDAL.Queries.DIColumns.Area.AreaID].ToString()];
}
else
{
AreaId = DrData[DevInfo.Lib.DI_LibDAL.Queries.DIColumns.Area.AreaID].ToString();
}
// AreaID = DrData[Area.AreaID].ToString();
AreaID = AreaId;
if (string.IsNullOrEmpty(AreaID) == true)
{
AreaID = Global.registryMSDAreaId;
}
if (!DictMapping.ContainsKey(IndicatorGId + "@__@" + UnitGId + "@__@" + SGVGId))
{
continue;
}
// Replace DI GUIDs with Coutry data GIDs based on mapping
SeriesValue = DictMapping[IndicatorGId + "@__@" + UnitGId + "@__@" + SGVGId].Split(new string[] { "@__@" }, StringSplitOptions.None)[0];
Unit = DictMapping[IndicatorGId + "@__@" + UnitGId + "@__@" + SGVGId].Split(new string[] { "@__@" }, StringSplitOptions.None)[1];
Age = DictMapping[IndicatorGId + "@__@" + UnitGId + "@__@" + SGVGId].Split(new string[] { "@__@" }, StringSplitOptions.None)[2];
Sex = DictMapping[IndicatorGId + "@__@" + UnitGId + "@__@" + SGVGId].Split(new string[] { "@__@" }, StringSplitOptions.None)[3];
Location = DictMapping[IndicatorGId + "@__@" + UnitGId + "@__@" + SGVGId].Split(new string[] { "@__@" }, StringSplitOptions.None)[4];
Frequency = DictMapping[IndicatorGId + "@__@" + UnitGId + "@__@" + SGVGId].Split(new string[] { "@__@" }, StringSplitOptions.None)[5];
SourceType = DictMapping[IndicatorGId + "@__@" + UnitGId + "@__@" + SGVGId].Split(new string[] { "@__@" }, StringSplitOptions.None)[6];
Nature = DictMapping[IndicatorGId + "@__@" + UnitGId + "@__@" + SGVGId].Split(new string[] { "@__@" }, StringSplitOptions.None)[7];
UnitMult = DictMapping[IndicatorGId + "@__@" + UnitGId + "@__@" + SGVGId].Split(new string[] { "@__@" }, StringSplitOptions.None)[8];
// If series item already exists
if (ProcessedIUSA.Contains(IndicatorGId + "@__@" + UnitGId + "@__@" + SGVGId + "@__@" + AreaID))
{
continue;
}
else
{
ProcessedIUSA.Add(IndicatorGId + "@__@" + UnitGId + "@__@" + SGVGId + "@__@" + AreaID);
Series = new SDMXApi_2_0.CompactData.SeriesType();
Series.AnyAttr = new List<XmlAttribute>();
AttributesDoc = new XmlDocument();
Attribute = AttributesDoc.CreateAttribute(Constants.UNSD.Concept.Indicator.Id);
Attribute.Value = SeriesValue;
Series.AnyAttr.Add(Attribute);
Attribute = AttributesDoc.CreateAttribute(Constants.UNSD.Concept.Unit.Id);
Attribute.Value = Unit;
Series.AnyAttr.Add(Attribute);
Attribute = AttributesDoc.CreateAttribute(Constants.UNSD.Concept.Age.Id);
Attribute.Value = Age;
Series.AnyAttr.Add(Attribute);
Attribute = AttributesDoc.CreateAttribute(Constants.UNSD.Concept.Sex.Id);
Attribute.Value = Sex;
Series.AnyAttr.Add(Attribute);
Attribute = AttributesDoc.CreateAttribute(Constants.UNSD.Concept.Location.Id);
Attribute.Value = Location;
Series.AnyAttr.Add(Attribute);
Attribute = AttributesDoc.CreateAttribute(Constants.UNSD.Concept.Frequency.Id);
Attribute.Value = Frequency;
Series.AnyAttr.Add(Attribute);
Attribute = AttributesDoc.CreateAttribute(Constants.UNSD.Concept.SourceType.Id);
Attribute.Value = SourceType;
Series.AnyAttr.Add(Attribute);
Attribute = AttributesDoc.CreateAttribute(Constants.UNSD.Concept.Area.Id);
Attribute.Value = AreaID;
Series.AnyAttr.Add(Attribute);
IUSARows = DtData.Select(Indicator.IndicatorGId + SDMXLibrary.Constants.EqualsTo + SDMXLibrary.Constants.Apostophe + IndicatorGId + SDMXLibrary.Constants.Apostophe + SDMXLibrary.Constants.AND + DevInfo.Lib.DI_LibDAL.Queries.DIColumns.Unit.UnitGId + SDMXLibrary.Constants.EqualsTo + SDMXLibrary.Constants.Apostophe + UnitGId + SDMXLibrary.Constants.Apostophe + SDMXLibrary.Constants.AND + SubgroupVals.SubgroupValGId + SDMXLibrary.Constants.EqualsTo + SDMXLibrary.Constants.Apostophe + SGVGId + SDMXLibrary.Constants.Apostophe + SDMXLibrary.Constants.AND + Area.AreaID + SDMXLibrary.Constants.EqualsTo + SDMXLibrary.Constants.Apostophe + DrData[Area.AreaID].ToString() + SDMXLibrary.Constants.Apostophe);
Series.ListObs = new List<SDMXApi_2_0.CompactData.ObsType>();
foreach (DataRow IUSARow in IUSARows)
{
TimePeriod = Get_Time_Period_Start_Year(Convert.ToString(IUSARow[Timeperiods.TimePeriod]));
CheckDuplicateKeys = DtData.Select(Indicator.IndicatorGId + SDMXLibrary.Constants.EqualsTo + SDMXLibrary.Constants.Apostophe + IndicatorGId + SDMXLibrary.Constants.Apostophe + SDMXLibrary.Constants.AND + DevInfo.Lib.DI_LibDAL.Queries.DIColumns.Unit.UnitGId + SDMXLibrary.Constants.EqualsTo + SDMXLibrary.Constants.Apostophe + UnitGId + SDMXLibrary.Constants.Apostophe + SDMXLibrary.Constants.AND + SubgroupVals.SubgroupValGId + SDMXLibrary.Constants.EqualsTo + SDMXLibrary.Constants.Apostophe + SGVGId + SDMXLibrary.Constants.Apostophe + SDMXLibrary.Constants.AND + Area.AreaID + SDMXLibrary.Constants.EqualsTo + SDMXLibrary.Constants.Apostophe + DrData[Area.AreaID].ToString() + SDMXLibrary.Constants.Apostophe + SDMXLibrary.Constants.AND + Timeperiods.TimePeriod + SDMXLibrary.Constants.EqualsTo + SDMXLibrary.Constants.Apostophe + TimePeriod + SDMXLibrary.Constants.Apostophe);
ObsVal = IUSARow[Data.DataValue].ToString();
TimeDetail = IUSARow[Timeperiods.TimePeriod].ToString();
SourceDetail = IUSARow[IndicatorClassifications.ICName].ToString();
Footnotes = IUSARow[FootNotes.FootNote].ToString();
Obs = new SDMXApi_2_0.CompactData.ObsType();
Obs.AnyAttr = new List<XmlAttribute>();
AttributesDoc = new XmlDocument();
Attribute = AttributesDoc.CreateAttribute(Constants.UNSD.Concept.Nature.Id);
Attribute.Value = Nature;
Obs.AnyAttr.Add(Attribute);
Attribute = AttributesDoc.CreateAttribute(Constants.UNSD.Concept.UnitMult.Id);
Attribute.Value = UnitMult;
Obs.AnyAttr.Add(Attribute);
Attribute = AttributesDoc.CreateAttribute(Constants.UNSD.Concept.TimePeriod.Id);
Attribute.Value = TimePeriod;
Obs.AnyAttr.Add(Attribute);
Attribute = AttributesDoc.CreateAttribute(Constants.UNSD.Concept.ObsVal.Id);
Attribute.Value = ObsVal;
Obs.AnyAttr.Add(Attribute);
Attribute = AttributesDoc.CreateAttribute(Constants.UNSD.Concept.TimeDetail.Id);
Attribute.Value = TimeDetail;
Obs.AnyAttr.Add(Attribute);
Attribute = AttributesDoc.CreateAttribute(Constants.UNSD.Concept.SourceDetail.Id);
Attribute.Value = SourceDetail;
Obs.AnyAttr.Add(Attribute);
Attribute = AttributesDoc.CreateAttribute(Constants.UNSD.Concept.Footnotes.Id);
Attribute.Value = Footnotes;
Obs.AnyAttr.Add(Attribute);
Series.ListObs.Add(Obs);
}
CompactData.DataSet.ListSeries.Add(Series);
}
}
if (CheckDuplicateKeys.Count() <= 1)
{
SDMXApi_2_0.Serializer.SerializeToFile(typeof(CompactDataType), CompactData, Path.Combine(Path.Combine(OutputFolder, Language), Convert.ToString(SeriesValue + "_DI_" + IndicatorGId) + DevInfo.Lib.DI_LibSDMX.Constants.XmlExtension));
GeneratedFiles.Add(Convert.ToString(SeriesValue + "_DI_" + IndicatorGId));
}
else
{
fileCount -= 1;
if (string.IsNullOrEmpty(DuplicateKey))
{
DuplicateKey += "DK" + Constants.Delimiters.ParamDelimiter + "INDICATOR: " + IndicatorName + "__@@@@__" + "TIMEPERIOD: " + TimePeriod + Constants.Delimiters.ParamDelimiter;
}
else
{
DuplicateKey += "DK" + Constants.Delimiters.ParamDelimiter + "INDICATOR: " + IndicatorName + "__@@@@__" + "TIMEPERIOD: " + TimePeriod + Constants.Delimiters.ParamDelimiter;
}
XLSLogGenerator.WriteCSVLogForMailStatus("Error found while publishing data", IndicatorName, "File not published for the given IndicatorGId and Timeperiod", DuplicateKey);
}
}
else
{
// to find indicator gid
DtData = Get_Language_Specific_Data_Table(Language, IndicatorNId, string.Empty, string.Empty, string.Empty, DIConnection);
if (string.IsNullOrEmpty(IndicatorName))
{
ErrorLogs += "NDF" + Constants.Delimiters.ParamDelimiter + IndicatorName + Constants.Delimiters.ParamDelimiter;
XLSLogGenerator.WriteCSVLogForMailStatus("No data found", IndicatorName, "File not published", IndicatorName);
}
else
{
if (ErrorLogs.Contains(IndicatorName))
{
continue;
}
else
{
if (string.IsNullOrEmpty(ErrorLogs))
{
ErrorLogs += "NDF" + Constants.Delimiters.ParamDelimiter + IndicatorName + Constants.Delimiters.ParamDelimiter;
}
else
{
ErrorLogs += "NDF" + Constants.Delimiters.ParamDelimiter + IndicatorName + Constants.Delimiters.ParamDelimiter;
}
XLSLogGenerator.WriteCSVLogForMailStatus("No data found for ", IndicatorName, "File not published for the given IndicatorGId", IndicatorName);
}
}
}
RowCount += 1;
}
}
RetVal = true;
return RetVal;
}
/// <summary>
/// Set the selected connection details on the basis of DBinfo
/// </summary>
/// <param name="dbInfo"></param>
/// <remarks> In case of online connection, Dbinfo contains : ServerType{[]}ConnectionIndex{[]}DatasetPrefix
/// In case of offline connection, ServerType{[]}AcessdbPath (Applicable for Dekstop application) </remarks>
public void SetSelectedConnectionDetail(string dbInfo)
{
DIConnection DIConnection;
string[] Values = new string[0];
Values = Utility.DICommon.SplitString(dbInfo, DELIMITER);
if (Values.Length == 3)
{
//-- In case of Web, it return the connection for all the servert type.
this.SelectedConnectionDetail = this.OnlineDatabaseDetails[Convert.ToInt32(Values[1])].DIConnectionDetails;
this.SelectedConnectionName = this.OnlineDatabaseDetails[Convert.ToInt32(Values[1])].Connection;
for (int i = 0; i <= this.OnlineDatabaseDetails[Convert.ToInt32(Values[1])].OnlineDatasetInfo.Count - 1; i++)
{
if (this.OnlineDatabaseDetails[Convert.ToInt32(Values[1])].OnlineDatasetInfo[i].Prefix.ToLower() == Values[2].ToLower())
{
//-- set the selected dataset name and its prefix.
this.SelectedDatasetName = this.OnlineDatabaseDetails[Convert.ToInt32(Values[1])].OnlineDatasetInfo[i].DatasetName;
this.SelectedDatasetPrefix = this.OnlineDatabaseDetails[Convert.ToInt32(Values[1])].OnlineDatasetInfo[i].Prefix + "_";
break;
}
}
this.SelectedDbInfo = dbInfo;
DIConnection = new DIConnection(this.SelectedConnectionDetail);
//-- Seleted dataset language
if (DIConnection.DILanguages(this.SelectedDatasetPrefix).Select(DevInfo.Lib.DI_LibDAL.Queries.DIColumns.Language.LanguageCode + " = '" + this._DatabaseLanguage.Substring(1) + "'").Length > 0)
{
//-- If the database language exists in the database
this.DatabaseLanguage = this._DatabaseLanguage;
}
else
{
//-- Set the default language as databaseLanguage, if the last selected language does not exists in the database.
this.DatabaseLanguage = DIConnection.DILanguageCodeDefault(this.SelectedDatasetPrefix);
}
}
else if (Values.Length == 2)
{
//-- Set the connection detail of access db (only for dekstop)
this.SelectedConnectionDetail = new DIConnectionDetails(DIServerType.MsAccess, "", "", Values[1], USERNAME, PASSWORD);
this.SelectedConnectionName = string.Empty;
this.SelectedDatasetName = System.IO.Path.GetFileName(Values[1]);
DIConnection = new DIConnection(this.SelectedConnectionDetail);
this.SelectedDatasetPrefix = DIConnection.DIDataSetDefault();
this.SelectedDbInfo = dbInfo;
//-- Seleted dataset language
this.DatabaseLanguage = DIConnection.DILanguageCodeDefault(this.SelectedDatasetPrefix);
}
}
private static void Fill_Reported_Attribute_Value(ReportedAttributeType ReportedAttribute, string IndicatorNId, Dictionary<string, string> DictMetadataMapping, DIConnection DIConnection, DIQueries DIQueries)
{
string language, Query;
string CategoryNId;
DataTable DtTable;
SDMXApi_2_0.Common.TextType LanguageSpecificValue;
if (DictMetadataMapping.ContainsKey(ReportedAttribute.conceptID))
{
ReportedAttribute.Value = new List<SDMXApi_2_0.Common.TextType>();
foreach (DataRow LanguageRow in DIConnection.DILanguages(DIQueries.DataPrefix).Rows)
{
language = LanguageRow[DevInfo.Lib.DI_LibDAL.Queries.DIColumns.Language.LanguageCode].ToString();
Query = "SELECT CategoryNId FROM UT_Metadata_Category_" + language + " WHERE CategoryGId = '" + DictMetadataMapping[ReportedAttribute.conceptID].ToString() + "' AND CategoryType = 'I'";
DtTable = DIConnection.ExecuteDataTable(Regex.Replace(Query, "UT_", DIConnection.DIDataSetDefault(), RegexOptions.IgnoreCase));
if (DtTable != null && DtTable.Rows.Count > 0)
{
CategoryNId = DtTable.Rows[0]["CategoryNId"].ToString();
Query = "SELECT Metadata FROM UT_MetadataReport_" + language + " WHERE Target_NId = " + IndicatorNId + " AND Category_NId = " + CategoryNId;
DtTable = DIConnection.ExecuteDataTable(Regex.Replace(Query, "UT_", DIConnection.DIDataSetDefault(), RegexOptions.IgnoreCase));
if (DtTable != null && DtTable.Rows.Count > 0)
{
LanguageSpecificValue = new SDMXApi_2_0.Common.TextType();
LanguageSpecificValue.lang = language;
LanguageSpecificValue.Value = DtTable.Rows[0]["Metadata"].ToString();
ReportedAttribute.Value.Add(LanguageSpecificValue);
}
}
}
}
}
