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