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); } } } } }