/// <summary> /// Imports mapped data values into database /// </summary> public void ImportTableValuesIntoDB(Database dbDatabase, CrossTabLogFile logFile) { Mapping MappedValues = null; string DataValue = String.Empty; string DenominatorValue = string.Empty; UnMatchedElementInfo UnmatchedElement; try { // import datavalues into target database for (int RowIndex = 0; RowIndex < this._DataValueTable.Rows.Count; RowIndex++) { for (int ColIndex = 0; ColIndex < this._DataValueTable.Columns.Count; ColIndex++) { // dont insert denominator column values if (this.DenominatorTable.Select(DenominatorColumns.DenominatorColumn + "='" + ColIndex +"'").Length == 0) { MappedValues = this.GetCellMapping(RowIndex, ColIndex, dbDatabase); DataValue = Convert.ToString(this._DataValueTable.Rows[RowIndex][ColIndex]); if (!string.IsNullOrEmpty(DataValue) && MappedValues != null && MappedValues.IsVaildMappedValues()) { // dont import missingvaluecharacter into data table if (!string.IsNullOrEmpty(this._MissingValueCharacter) && DataValue == this._MissingValueCharacter) { // dont import missingvaluecharacter into data table } else { // replace datavalue to zero if datavalue is equal to zero mask value if (!string.IsNullOrEmpty(this._ZeroMaskValue)) { if (DataValue == this._ZeroMaskValue) { DataValue = "0"; } } // round numeric datavalue if (DataValue != "-" && DataValue != "." && Microsoft.VisualBasic.Information.IsNumeric(DataValue)) { DataValue = Convert.ToString(Math.Round(Convert.ToDecimal(DataValue), this._DecimalValue)); } // get denominator value DenominatorValue = this.GetDenominatorValue(RowIndex, ColIndex); UnmatchedElement = MappedValues.ImportMappedValuesIntoDB(dbDatabase.DBConnection, dbDatabase.DBQueries, DataValue, DenominatorValue); this.WriteUnmatchedElementsIntoLogFile(logFile, UnmatchedElement); } } else { // WRITE unmapped information into log file this.WriteUnmappedInfoIntoLogFile(logFile, MappedValues, Convert.ToString(DataValue)); } } } } } catch (Exception ex) { throw new ApplicationException(ex.ToString()); } }
/// <summary> /// Imports mapped data values into data entry spreadsheet /// </summary> public DataEntrySpreadsheet GetTableValuesIntoDES(Database dbDatabase) { DataEntrySpreadsheet RetVal; Mapping MappedValues = null; string DataValue = String.Empty; string DenominatorValue = string.Empty; UnMatchedElementInfo UnmatchedElement; DESValue DESRow; try { // create instance of DES RetVal = new DataEntrySpreadsheet(); // import datavalues into target database for (int RowIndex = 0; RowIndex < this._DataValueTable.Rows.Count; RowIndex++) { for (int ColIndex = 0; ColIndex < this._DataValueTable.Columns.Count; ColIndex++) { // dont insert denominator column values if (this.DenominatorTable.Select(DenominatorColumns.DenominatorColumn + "='" + ColIndex+"'").Length == 0) { MappedValues = this.GetCellMapping(RowIndex, ColIndex, dbDatabase); DataValue = Convert.ToString(this._DataValueTable.Rows[RowIndex][ColIndex]); if (!string.IsNullOrEmpty(DataValue)) { if (DataValue != "-" && DataValue != ".") { // round datavalue if (Microsoft.VisualBasic.Information.IsNumeric(DataValue)) { DataValue = Convert.ToString(Math.Round(Convert.ToDecimal(DataValue), this._DecimalValue)); } } } // get denominator value DenominatorValue = this.GetDenominatorValue(RowIndex, ColIndex); // add values into DES if (MappedValues != null) { DESRow = new DESValue(); DESRow.IndicatorGID = MappedValues.IndicatorGID; DESRow.IndicatorName = MappedValues.IndicatorName; DESRow.UnitGID = MappedValues.UnitGID; DESRow.UnitName = MappedValues.UnitName; DESRow.Source = MappedValues.Source; DESRow.SubgroupVal = MappedValues.SubgroupVal; DESRow.SubgroupValGID = MappedValues.SubgroupValGID; DESRow.Timeperiod = MappedValues.Timeperiod; DESRow.AreaID = MappedValues.AreaID; DESRow.AreaName = MappedValues.Area; DESRow.Footnote = MappedValues.Footnote; DESRow.Denominator = DenominatorValue; DESRow.DataValue = DataValue; // add into DES Values RetVal.DESValues.Add(DESRow); } } } } } catch (Exception ex) { throw new ApplicationException(ex.ToString()); } return RetVal; }
/// <summary> /// Imports input files into data entry spreadsheet and returns log file name with path /// </summary> public void ImportFilesIntoDES(Database dbDatabase) { List<string> InputFileNames = new List<string>(); int TableIndex = 1; DESGenerator DESFile; DataEntrySpreadsheet DESSheet; string DESFileNameWPath = string.Empty; try { foreach (CrossTabInputFileInfo InputFileInfo in this._InputFiles) { TableIndex = 1; // create DES File instance DESFile = new DESGenerator(); foreach (CrossTabTableInfo TableInfo in InputFileInfo.Tables) { if (string.IsNullOrEmpty(TableInfo.Caption)) { TableInfo.Caption = TableIndex.ToString(); } // Get sheet and insert it into DES File TableInfo.ZeroMaskValue = this._ZeroMaskValue; TableInfo.MissingValueCharacter = this._MissingValueCharacter; DESSheet = TableInfo.GetTableValuesIntoDES(dbDatabase); if (DESSheet != null) { DESFile.DESSheets.Add(DESSheet); } TableIndex++; } // create DES File if (string.IsNullOrEmpty(InputFileInfo.ActucalFileNameWithoutExtension)) { DESFileNameWPath = DICommon.DefaultFolder.DefaultSpreadSheetsFolder + "DATA_" + Path.GetFileNameWithoutExtension(InputFileInfo.FileNameWExtension) + DICommon.FileExtension.Excel; } else { DESFileNameWPath = DICommon.DefaultFolder.DefaultSpreadSheetsFolder + "DATA_" + Path.GetFileNameWithoutExtension( InputFileInfo.ActucalFileNameWithoutExtension )+ DICommon.FileExtension.Excel; } DESFile.GenerateDESFile(DESFileNameWPath); } } catch (Exception ex) { throw new ApplicationException(ex.ToString()); } }
/// <summary> /// Returns cell mapping values /// </summary> /// <param name="selectedTableInfo"></param> /// <param name="rowIndex"></param> /// <param name="colIndex"></param> /// <returns></returns> public Mapping GetCellMapping(int rowIndex, int colIndex, Database dbDatabase) { Mapping RetVal = null; Mapping ColumnMapping; Mapping RowMapping; int SGValNID = -1; List<string> SGNIds = new List<string>(); DI6SubgroupBuilder DI6SGBuilder; try { // Get mapping information if (rowIndex >= 0 && colIndex >= 0) { ColumnMapping = this.ColumnsMapping[colIndex].Mappings.CellMap; RowMapping = this.RowsMapping[rowIndex].Mappings.CellMap; // Step1: copy column mapping RetVal = ColumnMapping.Copy(); // Step2: set the blank mapping value with the values available in row's mapping RetVal.ReplaceEmptyValues(RowMapping); // Step3: IF SubgoupVal is empty then get subgroupval on the basis of column's subgroups NID + row's subgroups NID if (string.IsNullOrEmpty(RetVal.SubgroupVal)) { DI6SGBuilder = new DI6SubgroupBuilder(dbDatabase.DBConnection, dbDatabase.DBQueries); // Step 3a: get subgroup nids(dimensionvalues nid) this.AddSubgroupNIds(DI6SGBuilder, RetVal.Subgroups, SGNIds); this.AddSubgroupNIds(DI6SGBuilder, RowMapping.Subgroups, SGNIds); if (SGNIds.Count > 0) { // Step 3b: get subgroupval for the selected dimensions DI6SubgroupValBuilder SGValBuilder = new DI6SubgroupValBuilder(dbDatabase.DBConnection, dbDatabase.DBQueries); DI6SubgroupValInfo SGValInfo; SGValNID = SGValBuilder.GetSubgroupValNIdBySugbroups(SGNIds); if (SGValNID > 0) { SGValInfo = SGValBuilder.GetSubgroupValInfo(FilterFieldType.NId, SGValNID.ToString()); RetVal.SubgroupVal = SGValInfo.Name; RetVal.SubgroupValGID = SGValInfo.GID; } } } // Step 4: set the blank mapping value with the values available in Default mapping RetVal.ReplaceEmptyValues(this._DefaultMapping); } } catch (Exception ex) { throw new ApplicationException(ex.ToString()); } return RetVal; }
/// <summary> /// Imports input files into database and returns log file name with path /// </summary> /// public string ImportFilesIntoDB(Database dbDatabase) { string RetVal = string.Empty; CrossTabLogFile LogFile = new CrossTabLogFile(); List<string> InputFileNames = new List<string>(); int TableIndex = 1; try { RetVal = DICommon.DefaultFolder.DefaultSpreadSheetsFolder + LogFile.GenerateLogFileName(this._DXApplicationName); // Get fileNames foreach (CrossTabInputFileInfo InputFile1 in this._InputFiles) { if (string.IsNullOrEmpty(InputFile1.ActucalFileNameWPath)) { InputFileNames.Add(InputFile1.FileNameWPath); } else { InputFileNames.Add(InputFile1.ActucalFileNameWPath); } } // Start log file LogFile.Start(RetVal, this._DXApplicationName, InputFileNames, this._OutputFileNameForLogFile, dbDatabase.DBConnection, dbDatabase.DBQueries); foreach (CrossTabInputFileInfo InputFileInfo in this._InputFiles) { TableIndex = 1; // Write file name in log file LogFile.StartInputFileLog(InputFileInfo.ActucalFileNameWithoutExtension); foreach (CrossTabTableInfo TableInfo in InputFileInfo.Tables) { if (string.IsNullOrEmpty(TableInfo.Caption)) { TableInfo.Caption = TableIndex.ToString(); } TableInfo.ZeroMaskValue = this._ZeroMaskValue; TableInfo.MissingValueCharacter = this._MissingValueCharacter; TableInfo.ImportTableValuesIntoDB(dbDatabase, LogFile); TableIndex++; } //end input file log LogFile.EndInputFileLog(); } // close log file LogFile.Close(); //update I,U & S NIDs in data table try { dbDatabase.DBConnection.ExecuteNonQuery(DI_LibDAL.Queries.Data.Update.UpdateIndicatorUnitSubgroupVAlNids(dbDatabase.DBQueries.TablesName)); } catch (Exception ex) { throw new ApplicationException(ex.ToString()); } } catch (Exception ex) { throw new ApplicationException(ex.ToString()); } return RetVal; }