/// <summary> /// Process Mapped Areas from MappedRows. /// </summary> public void ProcessMappedAreas() { DIConnection SrcDBConnection = null; DIQueries SrcDBQueries = null; AreaBuilder TrgAreaBuilder = null; AreaBuilder SourceAreaBuilder = null; AreaInfo SrcAreaInfo = null; DataTable Table = null; string SourceFileWPath = string.Empty; int TrgAreaNid = 0; if (this.MappedTables.ContainsKey(TemplateMergeControlType.Areas)) { TrgAreaBuilder = new AreaBuilder(this.DBConnection, this.DBQueries); foreach (DataRow Row in this.MappedTables[TemplateMergeControlType.Areas].MappedTable.MappedTable.Rows) { //todo: Table = this.DBConnection.ExecuteDataTable(TemplateQueries.GetImportAreas()); TrgAreaNid = Convert.ToInt32(Row[MergetTemplateConstants.Columns.AVAILABLE_COL_Prefix + Area.AreaNId]); if (Table != null && Table.Rows.Count > 0) { try { SourceFileWPath = Convert.ToString(Table.Rows[0][MergetTemplateConstants.Columns.COLUMN_SOURCEFILENAME]); SrcDBConnection = new DIConnection(DIServerType.MsAccess, string.Empty, string.Empty, SourceFileWPath, string.Empty, string.Empty); SrcDBQueries = DataExchange.GetDBQueries(SrcDBConnection); // Get Source Area Info SourceAreaBuilder = new AreaBuilder(SrcDBConnection, SrcDBQueries); SrcAreaInfo= SourceAreaBuilder.GetAreaInfo(FilterFieldType.NId, Convert.ToString(Table.Rows[0][MergetTemplateConstants.Columns.COLUMN_SRCNID])); // Import Mapped Area TrgAreaBuilder.ImportAreaFrmMappedArea(SrcAreaInfo, SrcAreaInfo.Nid, TrgAreaNid, SrcDBQueries, SrcDBConnection); } finally { if (SrcDBConnection != null) { SrcDBConnection.Dispose(); SrcDBQueries.Dispose(); } } } } } }
/// <summary> /// Imports records from source database to target database/template /// </summary> /// <param name="selectedNids"></param> /// <param name="allSelected">Set true to import all records</param> public override void ImportValues(List<string> SelectedNids, bool allSelected) { AreaBuilder AreaBuilderObj = new AreaBuilder(this._TargetDBConnection, this._TargetDBQueries); DI7MetadataCategoryBuilder AreaMetadataCategoryBuilder = null; int SelectedCount = SelectedNids.Count; try { // 1. import all area metadata categories if (this.IncludeMap) { AreaMetadataCategoryBuilder = new DI7MetadataCategoryBuilder(this._TargetDBConnection, this._TargetDBQueries); AreaMetadataCategoryBuilder.ImportAllMetadataCategories(this.SourceDBConnection, this.SourceDBQueries, MetadataElementType.Area); } // 2. add event handlers AreaBuilderObj.ProcessInfo += new ProcessInfoDelegate(AreaBuilderObj_ProcessInfo); AreaBuilderObj.BeforeProcess += new ProcessInfoDelegate(AreaBuilderObj_BeforeProcess); // 3. updated selected count variable if (allSelected) SelectedCount = -1; // 4. import selected areas if (this.IncludeArea & this.IncludeMap) { // import area and maps AreaBuilderObj.ImportArea(string.Join(",", SelectedNids.ToArray()), SelectedCount, this.SourceDBConnection, this.SourceDBQueries, this.SelectedAreaLevel, true); } else if (this.IncludeArea) { // import area AreaBuilderObj.ImportArea(string.Join(",", SelectedNids.ToArray()), SelectedCount, this.SourceDBConnection, this.SourceDBQueries, this.SelectedAreaLevel); } else if (this.IncludeMap) { // import area maps AreaBuilderObj.ImportAreaMaps(string.Join(",", SelectedNids.ToArray()), SelectedCount, this.SourceDBConnection, this.SourceDBQueries, this.SelectedAreaLevel); } } catch (Exception ex) { throw new ApplicationException(ex.ToString()); } }
/// <summary> /// Process Target Areas that matched with Source Areas /// </summary> public void ProcessMatchedAreas() { DataTable Table = null; AreaBuilder AreaBuilderObj = null; AreaInfo AreaInfoObj = null; Dictionary<string, DataRow> FileWithNids = new Dictionary<string, DataRow>(); DIConnection SourceDBConnection = null; DIQueries SourceDBQueries = null; //-- Step 1: Get TempTable with Sorted SourceFileName Table = this.DBConnection.ExecuteDataTable(this.TemplateQueries.GetMatchedAreas()); //-- Step 2:Initialise Indicator Builder with Target DBConnection AreaBuilderObj = new AreaBuilder(this.DBConnection, this.DBQueries); //-- Step 3: Import Nids for each SourceFile foreach (DataRow Row in Table.Copy().Rows) { try { SourceDBConnection = new DIConnection(DIServerType.MsAccess, String.Empty, String.Empty, Convert.ToString(Row[MergetTemplateConstants.Columns.COLUMN_SOURCEFILENAME]), String.Empty, MergetTemplateConstants.DBPassword); SourceDBQueries = DataExchange.GetDBQueries(SourceDBConnection); AreaBuilderObj.ImportArea(Convert.ToString(Row[Area.AreaNId]), 1, SourceDBConnection, SourceDBQueries); } catch (Exception ex) { ExceptionFacade.ThrowException(ex); } finally { if (SourceDBConnection != null) SourceDBConnection.Dispose(); if (SourceDBQueries != null) SourceDBQueries.Dispose(); } } }
/// <summary> /// DevInfo_5_0 Area Spreadsheet (should have 5 columns only staring the value from 6th Row - AreaID, AreaName, AreaLevel, AreaGID, ParentGID) /// </summary> /// <param name="templateFileName"></param> /// <param name="xlsFilenames"></param> /// <param name="tempFolderPath"></param> /// <param name="trgDBQueries">Instance of target queries object</param> /// <param name="languageName">languageName</param> /// <returns></returns> public bool CreateTemplateFrmAreaSpreadsheet(string templateFileName, List<string> xlsFilenames, string tempFolderPath, DIQueries trgDBQueries, string languageName) { bool RetVal = false; DIConnection DBConnection = null; DIQueries DBQueries; AreaBuilder AreaBuilderObj; DIDatabase TempTemplateFile; string DataPrefix = string.Empty; string LanguageCode = string.Empty; LanguageBuilder DILanguageBuilder; try { //create temp template file TempTemplateFile = new DIDatabase(templateFileName); TempTemplateFile.Dispose(); //create DIConnection, queries and area objects DBConnection = new DIConnection(DIServerType.MsAccess, string.Empty, string.Empty, templateFileName, string.Empty, string.Empty); if (trgDBQueries != null) { DBQueries = new DIQueries(DBConnection.DIDataSetDefault(), trgDBQueries.LanguageCode); DataPrefix = DBQueries.DataPrefix; LanguageCode = trgDBQueries.LanguageCode.Replace("_", ""); // create langauge dependent tables if not exists in the new template this.UpdateTablesForTargetLanguage(languageName, DBConnection, DBQueries, DataPrefix, LanguageCode); } else { DBQueries = new DIQueries(DBConnection.DIDataSetDefault(), DBConnection.DILanguageCodeDefault(DBConnection.DIDataSetDefault())); } AreaBuilderObj = new AreaBuilder(DBConnection, DBQueries); //get areas from excel files foreach (string XlsFileName in xlsFilenames) { try { // insert area into template DIExcel AreaXlsFile = new DIExcel(XlsFileName); AreaInfo NewArea; DataTable TempTable = AreaXlsFile.GetDataTableFromSheet(AreaXlsFile.GetSheetName(0)); //check it is a valid area excel file or not if (TempTable.Rows.Count > 5 & TempTable.Columns.Count > 1) { if (TempTable.Rows[2][0].ToString() == DITemplate.AreaIdString & TempTable.Rows[2][1].ToString() == DITemplate.AreaNameString & TempTable.Rows[2][2].ToString() == DITemplate.AreaLevelString & TempTable.Rows[2][3].ToString() == DITemplate.AreaGIdString & TempTable.Rows[2][4].ToString() == DITemplate.AreaParentGIdString) { // delete empty rows for (int i = 0; i < 4; i++) { TempTable.Rows[0].Delete(); } TempTable.AcceptChanges(); // sort the table by level TempTable.DefaultView.Sort = TempTable.Columns[this.AreaSheetColumns[AreaSheetColumnType.AreaLevel].ColumnIndex].ColumnName; foreach (DataRowView Row in TempTable.DefaultView) { try { // get area info from temp table NewArea = new AreaInfo(); NewArea.ID = DICommon.RemoveQuotes(Row[this.AreaSheetColumns[AreaSheetColumnType.AreaID].ColumnIndex].ToString()); NewArea.Name = DICommon.RemoveQuotes(Row[this.AreaSheetColumns[AreaSheetColumnType.AreaName].ColumnIndex].ToString()); NewArea.Level = Convert.ToInt32(Row[this.AreaSheetColumns[AreaSheetColumnType.AreaLevel].ColumnIndex]); NewArea.GID = DICommon.RemoveQuotes(Row[this.AreaSheetColumns[AreaSheetColumnType.AreaGID].ColumnIndex].ToString()); NewArea.Parent = new AreaInfo(); NewArea.Parent.ID = DICommon.RemoveQuotes(Row[this.AreaSheetColumns[AreaSheetColumnType.PareaGID].ColumnIndex].ToString()); //insert area into template // Step 1: If Area_Parent_ID is blank then set Area_Parent_NID to -1 and Area_Level=1 if (string.IsNullOrEmpty(NewArea.Parent.ID)) { NewArea.Parent.Nid = -1; NewArea.Level = 1; } else { // Step 2: If Area_Parent_ID is not blank then get Area_Parent_NID NewArea.Parent.Nid = AreaBuilderObj.GetAreaNidByAreaID(NewArea.Parent.ID); if (NewArea.Parent.Nid <= 0) { // Step 2.1: If Area_Parent_NID <=0 then set Area_Parent_NID to -1 and Area_Level=1 NewArea.Parent.Nid = -1; NewArea.Level = 1; } else { // Step 2.1: If Area_Parent_NID >0 then Area_Level=Area_Parent_Level+1 //get parent area level NewArea.Parent.Level = AreaBuilderObj.GetAreaLevelByAreaID(NewArea.Parent.ID); NewArea.Level = NewArea.Parent.Level + 1; } } // insert area into template AreaBuilderObj.InsertIntoDatabase(NewArea.Name, NewArea.ID, NewArea.GID, NewArea.Level, NewArea.Parent.Nid); } catch (Exception ex) { // do nothing } } } } } catch (Exception) { // } } RetVal = true; } catch (Exception ex) { RetVal = false; throw new ApplicationException(ex.ToString()); } finally { if (DBConnection != null) { DBConnection.Dispose(); } } return RetVal; }
/// <summary> /// To open or create DevInfo database /// </summary> /// <param name="fileNameWPath"></param> /// <param name="datasetPrefix"></param> /// <param name="langaugeCode"></param> public void OpenDatabase(string fileNameWPath, string datasetPrefix, string langaugeCode) { DBConverterDecorator DBConverter; //check file exists or not. If not exists then create it. if (!File.Exists(fileNameWPath)) { DIDatabase.CreateDevInfoDBFile(fileNameWPath); // update the db schema DBConverter = new DBConverterDecorator(fileNameWPath); DBConverter.DoConversion(false); DBConverter.Dispose(); } this.SetDatabaseObjects(fileNameWPath, datasetPrefix, langaugeCode); this.DIArea = new AreaBuilder(this.DBConnection, this.DBQueries); this.DIIUS = new IUSBuilder(this.DBConnection, this.DBQueries); this.DITimeperiod = new TimeperiodBuilder(this.DBConnection, this.DBQueries); this.DISource = new SourceBuilder(this.DBConnection, this.DBQueries); this.DIFootnote = new FootnoteBuilder(this.DBConnection, this.DBQueries); }
/// <summary> /// Returns instance of DIDatabase. /// </summary> /// <param name="connection">Instance of DIConnection which is already opened</param> /// <param name="queries">Instance of DIQueries which is already opened</param> public DIDatabase(DIConnection connection, DIQueries queries) { this.DBConnection = connection; this.DBQueries = queries; this.DIArea = new AreaBuilder(this.DBConnection, this.DBQueries); this.DIIUS = new IUSBuilder(this.DBConnection, this.DBQueries); this.DITimeperiod = new TimeperiodBuilder(this.DBConnection, this.DBQueries); this.DISource = new SourceBuilder(this.DBConnection, this.DBQueries); this.DIFootnote = new FootnoteBuilder(this.DBConnection, this.DBQueries); }
/// <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; }
public override void Import(string selectedNids) { DataTable Table = null; int ProgressCounter = 0; AreaBuilder AreaBuilderObj = null; AreaInfo AreaInfoObj = null; Dictionary<string, DataRow> FileWithNids = new Dictionary<string, DataRow>(); DIConnection SourceDBConnection = null; DIQueries SourceDBQueries = null; // Initialize progress bar this.RaiseProgressBarInitialize(selectedNids.Split(',').GetUpperBound(0) + 1); //////-- Step 1: Get TempTable with Sorted SourceFileName ////Table = this._TargetDBConnection.ExecuteDataTable(this.ImportQueries.GetImportAreas(this._CurrentTemplateFileNameWPath,selectedNids)); //-- Step 2:Initialise Indicator Builder with Target DBConnection AreaBuilderObj = new AreaBuilder(this.TargetDBConnection, this.TargetDBQueries); ////-- Step 3: Import Nids for each SourceFile //foreach (DataRow Row in Table.Copy().Rows) //{ try { SourceDBConnection = new DIConnection(DIServerType.MsAccess, String.Empty, String.Empty, this._CurrentTemplateFileNameWPath, String.Empty, MergetTemplateConstants.DBPassword); SourceDBQueries = DataExchange.GetDBQueries(SourceDBConnection); // AreaInfoObj = this.GetIndicatorInfo(Row); //AreaBuilderObj.ImportArea(selectedNids, 1, SourceDBConnection, SourceDBQueries); //AreaBuilderObj.ImportAreaMaps(selectedNids, 1, SourceDBConnection, SourceDBQueries); AreaBuilderObj.ImportArea(selectedNids, DICommon.SplitString(selectedNids, ",").Length, SourceDBConnection, SourceDBQueries, true); ProgressCounter += 1; this.RaiseProgressBarIncrement(ProgressCounter); } catch (Exception ex) { ExceptionFacade.ThrowException(ex); } finally { if (SourceDBConnection != null) SourceDBConnection.Dispose(); if (SourceDBQueries != null) SourceDBQueries.Dispose(); } //} this._UnmatchedTable = this.GetUnmatchedTable(); this._AvailableTable = this.GetAvailableTable(); // Close ProgressBar this.RaiseProgressBarClose(); }
/// <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; }