/// <summary> /// To import metadata from RTF file for all indicator classifications except source /// </summary> /// /// <param name="metadataInfo"></param> /// <param name="metaDataType"></param> /// <param name="elementNid"></param> public void ImportMetadataFromRTFFile(string metadataInfo, MetaDataType metadataType, int elementNId) { IndicatorClassificationBuilder ICBuilder = new IndicatorClassificationBuilder(this.DBConnection, this.DBQueries); try { ICBuilder.UpdateICInfo(elementNId, metadataInfo); } catch (Exception ex) { ExceptionFacade.ThrowException(ex); } }
/// <summary> /// Deletes sources from Indicator_Classification table and associated records from IC_IUS table /// </summary> /// <param name="nids">Comma separated nids which may be blank</param> public void DeleteSources(string nids) { DITables TableNames; IndicatorClassificationBuilder ICBuilder; MetaDataBuilder MetadataBuilderObject; if (!string.IsNullOrEmpty(nids)) { try { // Step1: Delete records from IndicatorClassification table foreach (DataRow Row in this.DBConnection.DILanguages(this.DBQueries.DataPrefix).Rows) { TableNames = new DITables(this.DBQueries.DataPrefix, Row[Language.LanguageCode].ToString()); this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.IndicatorClassification.Delete.DeleteSources(TableNames.IndicatorClassifications, nids)); } if (!string.IsNullOrEmpty(nids)) { // Step2: Delete records from IC_IUS table ICBuilder = new IndicatorClassificationBuilder(this.DBConnection, this.DBQueries); ICBuilder.DeleteClassificationIUSRelation(nids, string.Empty); // delete records data table //this.DBConnection.ExecuteNonQuery(this.DBQueries.Delete.Data.DeleteRecordsBySourceNIds(nids)); new DIDatabase(this.DBConnection, this.DBQueries).DeleteDataValue(string.Empty, string.Empty, string.Empty, nids); // delete metadata MetadataBuilderObject = new MetaDataBuilder(this.DBConnection, this.DBQueries); MetadataBuilderObject.DeleteMetadata(nids, MetadataElementType.Source); } } catch (Exception ex) { throw new ApplicationException(ex.ToString()); } } }
internal int CreateClassificationChainFromExtDB(int srcICNId, int srcParentNId, string srcICGid, string srcICName, ICType srcICType, string srcICInfo, bool isGlobal, DIQueries srcQueries, DIConnection srcDBConnection, DIQueries targetDBQueries, DIConnection targetDBConnection) { int RetVal; //int TrgParentNId; //string TrgParentName; int NewParentNId; DataTable TempTable; IndicatorClassificationInfo ICInfo; IndicatorClassificationBuilder ClassificationBuilder = new IndicatorClassificationBuilder(targetDBConnection, targetDBQueries); // -- STEP 1: If the Parent NID is -1 then create the Classification at the root if (srcParentNId == -1) { // -- Create the Classification // -------------------------------------------------------------- // While importing the Classifications, if the NId of the Source Classification is _ // the same as that of the one created, then the Duplicate check fails and a duplicate // classification getscreated. PASS -99 as the first parameter to the calling function // -------------------------------------------------------------- ICInfo = new IndicatorClassificationInfo(); ICInfo.Parent = new IndicatorClassificationInfo(); ICInfo.Parent.Nid = srcParentNId; ICInfo.Nid = srcICNId; ICInfo.Name = srcICName; ICInfo.ClassificationInfo = srcICInfo; ICInfo.GID = srcICGid; ICInfo.IsGlobal = isGlobal; ICInfo.Type = srcICType; RetVal = ClassificationBuilder.ImportIndicatorClassification(ICInfo, srcICNId, srcQueries, srcDBConnection); } else { // -- STEP 2: If the Parent is not -1 then check for the existence of the Parent and then create the Classification // Classification can only be created if the parent exists // -- STEP 2.1: If the Parent Exists then create the Classification under that parent // -- STEP 2.2: If the Parent does not Exist then create the Parent first and then the Classification under that parent // -- STEP 2: Check the existence of the Parent in the Target Database // -- get the parent from the source database TempTable = srcDBConnection.ExecuteDataTable(srcQueries.IndicatorClassification.GetIC(FilterFieldType.NId, srcParentNId.ToString(), srcICType, FieldSelection.Heavy)); { // -------------------------------------------------------------- // While importing the Classifications, if the NId of the Source Classification is _ // the same as that of the one created, then the Duplicate check fails and a duplicate // classification getscreated. PASS -99 as the first parameter to the calling function // -------------------------------------------------------------- DataRow Row; string ClassificationInfo = string.Empty; Row = TempTable.Rows[0]; ClassificationInfo = Convert.ToString(Row[IndicatorClassifications.ICInfo]); NewParentNId = CreateClassificationChainFromExtDB( Convert.ToInt32(Row[IndicatorClassifications.ICNId]), Convert.ToInt32(Row[IndicatorClassifications.ICParent_NId]), Row[IndicatorClassifications.ICGId].ToString(), Row[IndicatorClassifications.ICName].ToString(), srcICType, ClassificationInfo, Convert.ToBoolean(Row[IndicatorClassifications.ICGlobal]), srcQueries, srcDBConnection, targetDBQueries, targetDBConnection); ; } // -- Create the Child Now ICInfo = new IndicatorClassificationInfo(); ICInfo.Parent = new IndicatorClassificationInfo(); ICInfo.Parent.Nid = NewParentNId; // set new parent nid ICInfo.Nid = srcICNId; ICInfo.Name = srcICName; ICInfo.ClassificationInfo = srcICInfo; ICInfo.GID = srcICGid; ICInfo.IsGlobal = isGlobal; ICInfo.Type = srcICType; RetVal = ClassificationBuilder.ImportIndicatorClassification(ICInfo, srcICNId, srcQueries, srcDBConnection); } //import ic and ius relationship into indicator_classification_IUS table ClassificationBuilder.ImportICAndIUSRelations(srcICNId, RetVal, ICInfo.Type, srcQueries, srcDBConnection); return RetVal; }
/// <summary> /// Process Mapped IC Values /// </summary> /// <param name="importOnlyICIUS"></param> public void ProcessMappedIC(bool importOnlyICIUS) { DIConnection SrcDBConnection = null; DIQueries SrcDBQueries = null; IndicatorClassificationBuilder TrgICBuilder = null; IndicatorClassificationInfo SrcICInfo = null; DataTable Table = null; string SourceFileWPath = string.Empty; int TrgICNid = 0; if (this.MappedTables.ContainsKey(TemplateMergeControlType.IndicatorClassification)) { TrgICBuilder = new IndicatorClassificationBuilder(this.DBConnection, this.DBQueries); foreach (MappedRowInfo RowInfo in this.MappedTables[TemplateMergeControlType.IndicatorClassification].MappedTable.MappedRows.Values) { TrgICNid = Convert.ToInt32(RowInfo.AvailableRow[ IndicatorClassifications.ICNId]); SrcICInfo = this.GetIndicatorClassificationInfo(RowInfo.UnmatchedRow); SourceFileWPath = Convert.ToString(RowInfo.UnmatchedRow[MergetTemplateConstants.Columns.COLUMN_SOURCEFILENAME]); try { SrcDBConnection = new DIConnection(DIServerType.MsAccess, string.Empty, string.Empty, SourceFileWPath, string.Empty, string.Empty); SrcDBQueries = DataExchange.GetDBQueries(SrcDBConnection); // update IC in target only if importOnlyICIUS = false if (importOnlyICIUS) { TrgICBuilder.ImportICFrmMappedIC(SrcICInfo, SrcICInfo.Nid, TrgICNid, SrcDBQueries, SrcDBConnection); } // update IC IUS relation in target file TrgICBuilder.ImportICAndIUSRelations(SrcICInfo.Nid, TrgICNid, SrcICInfo.Type, SrcDBQueries, SrcDBConnection); } finally { if (SrcDBConnection != null) { SrcDBConnection.Dispose(); SrcDBQueries.Dispose(); } } } } }
/// <summary> /// Deletes sources from Indicator_Classification table and associated records from IC_IUS table /// </summary> /// <param name="nids">Comma separated nids which may be blank</param> public void DeleteSources(string nids) { DITables TableNames; IndicatorClassificationBuilder ICBuilder; MetaDataBuilder MetadataBuilderObject; if (!string.IsNullOrEmpty(nids)) { try { // Step1: Delete records from IndicatorClassification table foreach (DataRow Row in this.DBConnection.DILanguages(this.DBQueries.DataPrefix).Rows) { TableNames = new DITables(this.DBQueries.DataPrefix, Row[Language.LanguageCode].ToString()); this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.IndicatorClassification.Delete.DeleteSources(TableNames.IndicatorClassifications, nids)); } if (!string.IsNullOrEmpty(nids)) { // Step2: Delete records from IC_IUS table ICBuilder = new IndicatorClassificationBuilder(this.DBConnection, this.DBQueries); ICBuilder.DeleteClassificationIUSRelation(nids, string.Empty); // delete records data table //this.DBConnection.ExecuteNonQuery(this.DBQueries.Delete.Data.DeleteRecordsBySourceNIds(nids)); new DIDatabase(this.DBConnection, this.DBQueries).DeleteDataValue(string.Empty, string.Empty, string.Empty, nids); // delete metadata MetadataBuilderObject = new MetaDataBuilder(this.DBConnection, this.DBQueries); MetadataBuilderObject.DeleteMetadata(nids, MetadataElementType.Source); } } catch (Exception ex) { throw new ApplicationException(ex.ToString()); } } }
/// <summary> /// To Import metadata information /// </summary> /// <param name="dataPrefix"></param> /// <param name="sourceDBConnection"></param> /// <param name="sourceDBQueries"></param> /// <param name="selectedNIDs"></param> /// <param name="selectionCount"></param> /// <param name="metadataType"> </param> public void ImportICMetadata(string dataPrefix, DIConnection sourceDBConnection, DIQueries sourceDBQueries, string selectedNIDs, int selectionCount, MetaDataType metadataType, ICType classificationType) { string MetadataInfo = string.Empty; DataTable TempDataTable; DataTable TempTargetIdTable = new DataTable(); IndicatorClassificationBuilder ICBuilder; int TargetICNid = 0; int CurrentRecordIndex = 0; this.RaiseStartProcessEvent(); try { ICBuilder = new IndicatorClassificationBuilder(this.DBConnection, this.DBQueries); if (selectionCount == -1) { // -- GET ALL TempDataTable = sourceDBConnection.ExecuteDataTable(sourceDBQueries.IndicatorClassification.GetIC(FilterFieldType.None, string.Empty, classificationType, FieldSelection.Heavy)); } else { // -- GET SELECTED TempDataTable = sourceDBConnection.ExecuteDataTable(sourceDBQueries.IndicatorClassification.GetIC(FilterFieldType.NId, selectedNIDs, classificationType, FieldSelection.Heavy)); } // -- Initialize Progress Bar this.RaiseBeforeProcessEvent(TempDataTable.Rows.Count); CurrentRecordIndex = 0; for (int Index = 0; Index <= TempDataTable.Rows.Count - 1; Index++) { CurrentRecordIndex++; if (!Information.IsDBNull(TempDataTable.Rows[Index][IndicatorClassifications.ICInfo])) { if (!(TempDataTable.Rows[Index][IndicatorClassifications.ICInfo].ToString().Length == 0)) { //-- Retrieve Metadata Information MetadataInfo = TempDataTable.Rows[Index][IndicatorClassifications.ICInfo].ToString(); // Get Target IC NID by Source IC GID TargetICNid = ICBuilder.GetNidByGID(Convert.ToString(TempDataTable.Rows[Index][IndicatorClassifications.ICGId]), classificationType); if (TargetICNid > 0) { //-- Update Metadata ICBuilder.UpdateICInfo(TargetICNid, MetadataInfo); } } } // -- Increemnt the Progress Bar Value this.RaiseProcessInfoEvent(CurrentRecordIndex); } // -- Dispose the Data Table object TempDataTable.Dispose(); } catch (Exception ex) { throw new ApplicationException(ex.ToString()); } }
/// <summary> /// Generate SDMX Files from DES /// </summary> /// <param name="DESFileNameWithPath"></param> /// <param name="DSDFileNameWPath"></param> /// <param name="outputFolder"></param> /// <returns></returns> public static bool Generate_SDMXML_Data(string DESFileNameWithPath, string DSDFileNameWPath, string outputFolder) { bool RetVal = false; DIConnection DBConnection = null; DIQueries DBQueries = null; int count = 0; // Utility.TemporaryFileNamePath DataTable Table = DevInfo.Lib.DI_LibBAL.Import.DAImport.DES.DataEntrySpreadsheets.GetDataTableForAllDESSheets(DESFileNameWithPath); string TempDatabaseName = string.Empty; string TempDatabaseNameCompact = string.Empty; TempDatabaseName = Path.Combine(Path.GetDirectoryName(DESFileNameWithPath), DICommon.GetValidFileName(DateTime.Now.Ticks.ToString() + DICommon.FileExtension.Template)); Dictionary <string, int> IndicatorList = new Dictionary <string, int>(); Dictionary <string, int> UnitList = new Dictionary <string, int>(); Dictionary <string, int> AreaList = new Dictionary <string, int>(); Dictionary <string, int> SgValList = new Dictionary <string, int>(); Dictionary <string, int> TimepeirodList = new Dictionary <string, int>(); Dictionary <string, int> SourceList = new Dictionary <string, int>(); try { DevInfo.Lib.DI_LibDAL.Resources.Resource.GetBlankDevInfoDBFile(TempDatabaseName); DBConnection = new DIConnection(DIServerType.MsAccess, string.Empty, string.Empty, TempDatabaseName, string.Empty, string.Empty); DBQueries = new DIQueries(DBConnection.DIDataSetDefault(), DBConnection.DILanguageCodeDefault(DBConnection.DIDataSetDefault())); DIDataValueHelper.MergeTextualandNumericDataValueColumn(DBConnection, DBQueries); IndicatorBuilder IndBuilder = new IndicatorBuilder(DBConnection, DBQueries); UnitBuilder UBuilder = new UnitBuilder(DBConnection, DBQueries); DI6SubgroupValBuilder SGBuilder = new DI6SubgroupValBuilder(DBConnection, DBQueries); AreaBuilder AreaBuilderObj = new AreaBuilder(DBConnection, DBQueries); IndicatorClassificationBuilder ICBuilder = new IndicatorClassificationBuilder(DBConnection, DBQueries); TimeperiodBuilder TBuilder = new TimeperiodBuilder(DBConnection, DBQueries); SourceBuilder SrcBuilder = new SourceBuilder(DBConnection, DBQueries); DIDatabase DatabaseObj = new DIDatabase(DBConnection, DBQueries); IUSBuilder IUSBuilderObj = new IUSBuilder(DBConnection, DBQueries); foreach (DataRow Row in Table.Rows) { int IndicatorNId = 0; int UnitNId = 0; int SGValNId = 0; int AreaNId = 0; int TimeperiodNId = 0; int SourceNid = 0; int IUSNID = 0; //-- Import Indicator if (IndicatorList.ContainsKey(Convert.ToString(Row[Indicator.IndicatorGId]))) { IndicatorNId = IndicatorList[Convert.ToString(Row[Indicator.IndicatorGId])]; } else { IndicatorNId = IndBuilder.ImportIndicator(Convert.ToString(Row[Indicator.IndicatorName]), Convert.ToString(Row[Indicator.IndicatorGId]), false); IndicatorList.Add(Convert.ToString(Row[Indicator.IndicatorGId]), IndicatorNId); } //-- Import Unit if (UnitList.ContainsKey(Convert.ToString(Row[Unit.UnitGId]))) { UnitNId = UnitList[Convert.ToString(Row[Unit.UnitGId])]; } else { UnitNId = UBuilder.ImportUnit(Convert.ToString(Row[Unit.UnitGId]), Convert.ToString(Row[Unit.UnitName]).Trim(), false); UnitList.Add(Convert.ToString(Row[Unit.UnitGId]), UnitNId); } //-- Import SubgroupVal if (!string.IsNullOrEmpty(Convert.ToString(Row[SubgroupVals.SubgroupVal]))) { if (SgValList.ContainsKey(Convert.ToString(Row[SubgroupVals.SubgroupValGId]))) { SGValNId = SgValList[Convert.ToString(Row[SubgroupVals.SubgroupValGId])]; } else { SGValNId = SGBuilder.ImportSubgroupVal(Convert.ToString(Row[SubgroupVals.SubgroupVal]), Convert.ToString(Row[SubgroupVals.SubgroupValGId]), false); SgValList.Add(Convert.ToString(Row[SubgroupVals.SubgroupValGId]), SGValNId); } } //-- Import Area if (AreaList.ContainsKey(Convert.ToString(Row[Area.AreaID]))) { AreaNId = AreaList[Convert.ToString(Row[Area.AreaID])]; } else { AreaNId = AreaBuilderObj.ImportArea(Convert.ToString(Row[Area.AreaName]), Convert.ToString(Row[Area.AreaID]), String.Empty, false); AreaList.Add(Convert.ToString(Row[Area.AreaID]), AreaNId); } //-- Import Timeperiod if (TimepeirodList.ContainsKey(Convert.ToString(Row[Timeperiods.TimePeriod]))) { TimeperiodNId = TimepeirodList[Convert.ToString(Row[Timeperiods.TimePeriod])]; } else { TimeperiodNId = TBuilder.CheckNCreateTimeperiod(Convert.ToString(Row[Timeperiods.TimePeriod])); TimepeirodList.Add(Convert.ToString(Row[Timeperiods.TimePeriod]), TimeperiodNId); } //-- Import Source if (SourceList.ContainsKey(Convert.ToString(Row[IndicatorClassifications.ICName]))) { SourceNid = SourceList[Convert.ToString(Row[IndicatorClassifications.ICName])]; } else { SourceNid = SrcBuilder.CheckNCreateSource(Convert.ToString(Row[IndicatorClassifications.ICName])); SourceList.Add(Convert.ToString(Row[IndicatorClassifications.ICName]), SourceNid); } IUSNID = IUSBuilderObj.ImportIUS(IndicatorNId, UnitNId, SGValNId, 0, 0, DBQueries, DBConnection); if (IUSNID > 0 && AreaNId > 0 && TimeperiodNId > 0 && !string.IsNullOrEmpty(Convert.ToString(Row[Data.DataValue]))) { DatabaseObj.CheckNCreateData(AreaNId, IUSNID, SourceNid, TimeperiodNId, Convert.ToString(Row[Data.DataValue])); } } DatabaseObj.UpdateIndicatorUnitSubgroupNIDsInData(); //-- Compact Database into TempFile TempDatabaseNameCompact = Path.Combine(Path.GetDirectoryName(DESFileNameWithPath), DICommon.GetValidFileName(DateTime.Now.Ticks.ToString() + DICommon.FileExtension.Template)); DIDatabase.CompactDataBase(ref DBConnection, DBQueries, TempDatabaseNameCompact, true); DBConnection = new DIConnection(DIServerType.MsAccess, string.Empty, string.Empty, TempDatabaseNameCompact, string.Empty, string.Empty); DBQueries = new DIQueries(DBConnection.DIDataSetDefault(), DBConnection.DILanguageCodeDefault(DBConnection.DIDataSetDefault())); if (!Directory.Exists(Path.Combine(outputFolder, DBQueries.LanguageCode.Trim('_')))) { Directory.CreateDirectory(Path.Combine(outputFolder, DBQueries.LanguageCode.Trim('_'))); } System.Xml.XmlDocument XmlDoc = SDMXUtility.Get_Query(SDMXSchemaType.Two_One, null, QueryFormats.StructureSpecificTS, DataReturnDetailTypes.Full, Guid.NewGuid().ToString().Replace("-", "").Replace("_", ""), DBConnection, DBQueries); RetVal = SDMXUtility.Generate_Data(SDMXSchemaType.Two_One, XmlDoc, DevInfo.Lib.DI_LibSDMX.DataFormats.StructureSpecificTS, DBConnection, DBQueries, outputFolder); } catch (Exception ex) { ExceptionFacade.ThrowException(ex); } return(RetVal); }
/// <summary> /// Convets Database into template /// </summary> public void ConvertDatabaseToTemplate() { SourceBuilder SrcBuilder; FootnoteBuilder FoonotesBuilder; IndicatorClassificationBuilder ICBuilder; RecommendedSourcesBuilder RecommendedSrcBuilder; DataTable ICTable; string ICNIds = string.Empty; try { // STEP 1: Remove records from DATA table this.DBConnection.ExecuteNonQuery(this.DBQueries.Delete.Data.DeleteRecords(string.Empty)); // STEP 2: Remove records from TIME table this.DBConnection.ExecuteNonQuery(this.DBQueries.Delete.Timeperiod.DeleteRecords(string.Empty)); // STEP 3: Remove records from SOURCE table SrcBuilder = new SourceBuilder(this.DBConnection, this.DBQueries); SrcBuilder.DeleteSources(string.Empty); // STEP 4: Remove source records from Indicator_classification tables ICBuilder = new IndicatorClassificationBuilder(this.DBConnection, this.DBQueries); ICTable = this.DBConnection.ExecuteDataTable(this.DBQueries.IndicatorClassification.GetIC(FilterFieldType.Type, "'SR'", FieldSelection.NId)); if (ICTable != null && ICTable.Rows.Count > 0) { ICNIds = DIConnection.GetDelimitedValuesFromDataTable(ICTable, IndicatorClassifications.ICNId); } ICBuilder.DeleteClassification(ICNIds); // STEP 5: Remove records from Footnotes table FoonotesBuilder = new FootnoteBuilder(this.DBConnection, this.DBQueries); FoonotesBuilder.DeleteFootnote(string.Empty); // STEP 6: Creates DevInfoSP2 tables if missing. DICommon.CheckNCreateDevInfoSP2Database(this.DBConnection, this.DBQueries, false); // STEP 7: remove records from notes table this.ClearNotesTables(); // STEP 8: remove records from RecommendedSources table RecommendedSrcBuilder = new RecommendedSourcesBuilder(this.DBConnection, this.DBQueries); RecommendedSrcBuilder.DeleteRecommendedSources(string.Empty); // STEP 9:Remove Source metadata reports new DI7MetaDataBuilder(this.DBConnection, this.DBQueries).DeleteMetadataReports(MetadataElementType.Source); } catch (Exception ex) { throw new ApplicationException(ex.ToString()); } }
/// <summary> /// Update auto calculated columns ( IC table - Publisher, Year & title ,Indicator table- Data_Exists, area table - data_exist, IUS table - subgroup_nids & data_exist) into the database/template /// </summary> /// <returns></returns> public bool UpdateAutoCalculatedFieldsInTables() { bool RetVal = false; IndicatorClassificationBuilder ICBuilder; IndicatorBuilder IndBuilder; IUSBuilder IUSBuilderObj; AreaBuilder AreaBuilderObj; try { // 1. Indicator classification table - Publisher, year & title columns ICBuilder = new IndicatorClassificationBuilder(this.DBConnection, this.DBQueries); ICBuilder.UpdatePublisherTitleYear(); // 2. Indicator table - Data_exist column IndBuilder = new IndicatorBuilder(this.DBConnection, this.DBQueries); IndBuilder.UpdateDataExistValues(); // 3. IUS table- Subgroup_Nids & Data_Exist columns IUSBuilderObj = new IUSBuilder(this.DBConnection, this.DBQueries); IUSBuilderObj.UpdateSubgroupNids(); IUSBuilderObj.UpdateDataExistValues(); // 4. Area table- Data_Exist column AreaBuilderObj = new AreaBuilder(this.DBConnection, this.DBQueries); AreaBuilderObj.UpdateDataExistValues(); RetVal = true; } catch (Exception ex) { ExceptionFacade.ThrowException(ex); } return RetVal; }
/// <summary> /// To import metadata from RTF file for all indicator classifications except source /// </summary> /// /// <param name="metadataInfo"></param> /// <param name="metaDataType"></param> /// <param name="elementNid"></param> public void ImportMetadataFromRTFFile(string metadataInfo, MetaDataType metadataType, int elementNId) { IndicatorClassificationBuilder ICBuilder = new IndicatorClassificationBuilder(this.DBConnection, this.DBQueries); try { ICBuilder.UpdateICInfo(elementNId, metadataInfo); } catch (Exception ex) { ExceptionFacade.ThrowException(ex); } }
/// <summary> /// To Import metadata information /// </summary> /// <param name="dataPrefix"></param> /// <param name="sourceDBConnection"></param> /// <param name="sourceDBQueries"></param> /// <param name="selectedNIDs"></param> /// <param name="selectionCount"></param> /// <param name="metadataType"> </param> public void ImportICMetadata(string dataPrefix, DIConnection sourceDBConnection, DIQueries sourceDBQueries, string selectedNIDs, int selectionCount, MetaDataType metadataType, ICType classificationType) { string MetadataInfo = string.Empty; DataTable TempDataTable; DataTable TempTargetIdTable = new DataTable(); IndicatorClassificationBuilder ICBuilder; int TargetICNid = 0; int CurrentRecordIndex = 0; this.RaiseStartProcessEvent(); try { ICBuilder = new IndicatorClassificationBuilder(this.DBConnection, this.DBQueries); if (selectionCount == -1) { // -- GET ALL TempDataTable = sourceDBConnection.ExecuteDataTable(sourceDBQueries.IndicatorClassification.GetIC(FilterFieldType.None, string.Empty, classificationType, FieldSelection.Heavy)); } else { // -- GET SELECTED TempDataTable = sourceDBConnection.ExecuteDataTable(sourceDBQueries.IndicatorClassification.GetIC(FilterFieldType.NId, selectedNIDs, classificationType, FieldSelection.Heavy)); } // -- Initialize Progress Bar this.RaiseBeforeProcessEvent(TempDataTable.Rows.Count); CurrentRecordIndex = 0; for (int Index = 0; Index <= TempDataTable.Rows.Count - 1; Index++) { CurrentRecordIndex++; if (!Information.IsDBNull(TempDataTable.Rows[Index][IndicatorClassifications.ICInfo])) { if (!(TempDataTable.Rows[Index][IndicatorClassifications.ICInfo].ToString().Length == 0)) { //-- Retrieve Metadata Information MetadataInfo = TempDataTable.Rows[Index][IndicatorClassifications.ICInfo].ToString(); // Get Target IC NID by Source IC GID TargetICNid = ICBuilder.GetNidByGID(Convert.ToString(TempDataTable.Rows[Index][IndicatorClassifications.ICGId]), classificationType); if (TargetICNid > 0) { //-- Update Metadata ICBuilder.UpdateICInfo(TargetICNid, MetadataInfo); } } } // -- Increemnt the Progress Bar Value this.RaiseProcessInfoEvent(CurrentRecordIndex); } // -- Dispose the Data Table object TempDataTable.Dispose(); } catch (Exception ex) { throw new ApplicationException(ex.ToString()); } }
/// <summary> /// Generate SDMX Files from DES /// </summary> /// <param name="DESFileNameWithPath"></param> /// <param name="DSDFileNameWPath"></param> /// <param name="outputFolder"></param> /// <returns></returns> public static bool Generate_SDMXML_Data(string DESFileNameWithPath, string DSDFileNameWPath, string outputFolder) { bool RetVal = false; DIConnection DBConnection = null; DIQueries DBQueries = null; int count = 0; // Utility.TemporaryFileNamePath DataTable Table = DevInfo.Lib.DI_LibBAL.Import.DAImport.DES.DataEntrySpreadsheets.GetDataTableForAllDESSheets(DESFileNameWithPath); string TempDatabaseName = string.Empty; string TempDatabaseNameCompact = string.Empty; TempDatabaseName = Path.Combine(Path.GetDirectoryName(DESFileNameWithPath), DICommon.GetValidFileName(DateTime.Now.Ticks.ToString() + DICommon.FileExtension.Template)); Dictionary<string, int> IndicatorList = new Dictionary<string, int>(); Dictionary<string, int> UnitList = new Dictionary<string, int>(); Dictionary<string, int> AreaList = new Dictionary<string, int>(); Dictionary<string, int> SgValList = new Dictionary<string, int>(); Dictionary<string, int> TimepeirodList = new Dictionary<string, int>(); Dictionary<string, int> SourceList = new Dictionary<string, int>(); try { DevInfo.Lib.DI_LibDAL.Resources.Resource.GetBlankDevInfoDBFile(TempDatabaseName); DBConnection = new DIConnection(DIServerType.MsAccess, string.Empty, string.Empty, TempDatabaseName, string.Empty, string.Empty); DBQueries = new DIQueries(DBConnection.DIDataSetDefault(), DBConnection.DILanguageCodeDefault(DBConnection.DIDataSetDefault())); DIDataValueHelper.MergeTextualandNumericDataValueColumn(DBConnection, DBQueries); IndicatorBuilder IndBuilder = new IndicatorBuilder(DBConnection, DBQueries); UnitBuilder UBuilder = new UnitBuilder(DBConnection, DBQueries); DI6SubgroupValBuilder SGBuilder = new DI6SubgroupValBuilder(DBConnection, DBQueries); AreaBuilder AreaBuilderObj = new AreaBuilder(DBConnection, DBQueries); IndicatorClassificationBuilder ICBuilder = new IndicatorClassificationBuilder(DBConnection, DBQueries); TimeperiodBuilder TBuilder = new TimeperiodBuilder(DBConnection, DBQueries); SourceBuilder SrcBuilder = new SourceBuilder(DBConnection, DBQueries); DIDatabase DatabaseObj = new DIDatabase(DBConnection, DBQueries); IUSBuilder IUSBuilderObj = new IUSBuilder(DBConnection, DBQueries); foreach (DataRow Row in Table.Rows) { int IndicatorNId = 0; int UnitNId = 0; int SGValNId = 0; int AreaNId = 0; int TimeperiodNId = 0; int SourceNid = 0; int IUSNID = 0; //-- Import Indicator if (IndicatorList.ContainsKey(Convert.ToString(Row[Indicator.IndicatorGId]))) { IndicatorNId = IndicatorList[Convert.ToString(Row[Indicator.IndicatorGId])]; } else { IndicatorNId = IndBuilder.ImportIndicator(Convert.ToString(Row[Indicator.IndicatorName]), Convert.ToString(Row[Indicator.IndicatorGId]), false); IndicatorList.Add(Convert.ToString(Row[Indicator.IndicatorGId]), IndicatorNId); } //-- Import Unit if (UnitList.ContainsKey(Convert.ToString(Row[Unit.UnitGId]))) { UnitNId = UnitList[Convert.ToString(Row[Unit.UnitGId])]; } else { UnitNId = UBuilder.ImportUnit(Convert.ToString(Row[Unit.UnitGId]), Convert.ToString(Row[Unit.UnitName]).Trim(), false); UnitList.Add(Convert.ToString(Row[Unit.UnitGId]), UnitNId); } //-- Import SubgroupVal if (!string.IsNullOrEmpty(Convert.ToString(Row[SubgroupVals.SubgroupVal]))) { if (SgValList.ContainsKey(Convert.ToString(Row[SubgroupVals.SubgroupValGId]))) { SGValNId = SgValList[Convert.ToString(Row[SubgroupVals.SubgroupValGId])]; } else { SGValNId = SGBuilder.ImportSubgroupVal(Convert.ToString(Row[SubgroupVals.SubgroupVal]), Convert.ToString(Row[SubgroupVals.SubgroupValGId]), false); SgValList.Add(Convert.ToString(Row[SubgroupVals.SubgroupValGId]), SGValNId); } } //-- Import Area if (AreaList.ContainsKey(Convert.ToString(Row[Area.AreaID]))) { AreaNId = AreaList[Convert.ToString(Row[Area.AreaID])]; } else { AreaNId = AreaBuilderObj.ImportArea(Convert.ToString(Row[Area.AreaName]), Convert.ToString(Row[Area.AreaID]), String.Empty, false); AreaList.Add(Convert.ToString(Row[Area.AreaID]), AreaNId); } //-- Import Timeperiod if (TimepeirodList.ContainsKey(Convert.ToString(Row[Timeperiods.TimePeriod]))) { TimeperiodNId = TimepeirodList[Convert.ToString(Row[Timeperiods.TimePeriod])]; } else { TimeperiodNId = TBuilder.CheckNCreateTimeperiod(Convert.ToString(Row[Timeperiods.TimePeriod])); TimepeirodList.Add(Convert.ToString(Row[Timeperiods.TimePeriod]), TimeperiodNId); } //-- Import Source if (SourceList.ContainsKey(Convert.ToString(Row[IndicatorClassifications.ICName]))) { SourceNid = SourceList[Convert.ToString(Row[IndicatorClassifications.ICName])]; } else { SourceNid = SrcBuilder.CheckNCreateSource(Convert.ToString(Row[IndicatorClassifications.ICName])); SourceList.Add(Convert.ToString(Row[IndicatorClassifications.ICName]), SourceNid); } IUSNID = IUSBuilderObj.ImportIUS(IndicatorNId, UnitNId, SGValNId, 0, 0, DBQueries, DBConnection); if (IUSNID > 0 && AreaNId > 0 && TimeperiodNId > 0 && !string.IsNullOrEmpty(Convert.ToString(Row[Data.DataValue]))) { DatabaseObj.CheckNCreateData(AreaNId, IUSNID, SourceNid, TimeperiodNId, Convert.ToString(Row[Data.DataValue])); } } DatabaseObj.UpdateIndicatorUnitSubgroupNIDsInData(); //-- Compact Database into TempFile TempDatabaseNameCompact = Path.Combine(Path.GetDirectoryName(DESFileNameWithPath), DICommon.GetValidFileName(DateTime.Now.Ticks.ToString() + DICommon.FileExtension.Template)); DIDatabase.CompactDataBase(ref DBConnection, DBQueries, TempDatabaseNameCompact, true); DBConnection = new DIConnection(DIServerType.MsAccess, string.Empty, string.Empty, TempDatabaseNameCompact, string.Empty, string.Empty); DBQueries = new DIQueries(DBConnection.DIDataSetDefault(), DBConnection.DILanguageCodeDefault(DBConnection.DIDataSetDefault())); if (!Directory.Exists(Path.Combine(outputFolder, DBQueries.LanguageCode.Trim('_')))) { Directory.CreateDirectory(Path.Combine(outputFolder, DBQueries.LanguageCode.Trim('_'))); } System.Xml.XmlDocument XmlDoc = SDMXUtility.Get_Query(SDMXSchemaType.Two_One, null, QueryFormats.StructureSpecificTS, DataReturnDetailTypes.Full, Guid.NewGuid().ToString().Replace("-", "").Replace("_", ""), DBConnection, DBQueries); RetVal = SDMXUtility.Generate_Data(SDMXSchemaType.Two_One, XmlDoc, DevInfo.Lib.DI_LibSDMX.DataFormats.StructureSpecificTS, DBConnection, DBQueries, outputFolder); } catch (Exception ex) { ExceptionFacade.ThrowException(ex); } return RetVal; }
private void InsertNewSources() { String SqlQuery = string.Empty; DataTable NewSourcesTable = new DataTable(); DataTable SourceTempTable; DataTable TempTable; string SourceNID = string.Empty; string IUSNID = string.Empty; IndicatorClassificationBuilder ICBuilder; //DIDatabase Database = new DIDatabase(); SourceBuilder DISource = new SourceBuilder(this.DBConnection, this.DBQueries); try { // 1. Update special quotes in sources // as replace function does not work with ADO.net so first get the special foonote with quotes and then update source text one by one. if (this.DBConnection.ConnectionStringParameters.ServerType == DIServerType.MsAccess) { SourceTempTable = this.DBConnection.ExecuteDataTable(this.DAQuery.GetSourcesWSpecialQuotesFrmTempDataTbl()); //update source text foreach (DataRow Row in SourceTempTable.Rows) { this.DBConnection.ExecuteNonQuery(this.DAQuery.UpdateSpecialQuotesInSourceText(Convert.ToString(Row[DevInfo.Lib.DI_LibBAL.Import.DAImport.Common.Constants.SourceColumnName]), Convert.ToString(Row[Data.DataNId]))); } } //// 2. Get Distinct Sources from Temp_Data which are not in UT_Indicator_Classification //NewSourcesTable = this.DBConnection.ExecuteDataTable(this.DAQuery.GetDistinctSourcesFromTempData()); // 1. Get Distinct Sources from Temp_Data NewSourcesTable = this.DBConnection.ExecuteDataTable(this.DAQuery.GetAllSourceFrmTempDataTable()).DefaultView.ToTable(true, DAImportCommon.Constants.SourceColumnName); // 2. Insert Sources into UT_Indicator_Classification. foreach (DataRow SourceRow in NewSourcesTable.Rows) { DISource.CheckNCreateSource(DICommon.RemoveQuotes(SourceRow[DAImportCommon.Constants.SourceColumnName].ToString())); } // 3. Update Source_Nid again in Temp_Data. this.DBConnection.ExecuteNonQuery(this.DAQuery.UpdateSourceNidInTempData()); // 4. Update IC_Global in target database this.UpdateICGlobal(); // Insert IC_Nid & IUSNid in "Indicator_Classification_IUS" Table //this.DBConnection.ExecuteNonQuery(this.DAQuery.InsertRecordInIndicator_Classification_IUS()); // 5. insert IC_NID and IUSNID relationship in Indicator_Classification_IUS" table TempTable = this.DBConnection.ExecuteDataTable(this.DAQuery.GetNewSourcesNIUSNID()); if (TempTable != null) { ICBuilder = new IndicatorClassificationBuilder(this.DBConnection, this.DBQueries); foreach (DataRow Row in TempTable.Rows) { SourceNID = Convert.ToString(Row[Data.SourceNId]); IUSNID = Convert.ToString(Row[DevInfo.Lib.DI_LibBAL.Import.DAImport.Common.Constants.NewIUSColumnName]); if (!string.IsNullOrEmpty(SourceNID) && !string.IsNullOrEmpty(IUSNID)) { ICBuilder.AddNUpdateICIUSRelationUptoRootlevel(Convert.ToInt32(SourceNID), Convert.ToInt32(IUSNID), false); } } } } catch (Exception ex) { ExceptionFacade.ThrowException(ex); } }