/// <summary>
/// Inserts IUS into database
/// </summary>
/// <param name="indicatorGId"></param>
/// <param name="unitGId"></param>
/// <param name="subgroupGId"></param>
/// <returns></returns>
public int InsertIUSIntoDB(string indicatorGId, string unitGId, string subgroupGId)
{
int RetVal = -1;
IndicatorBuilder IndicatorBuilderObj = new IndicatorBuilder(this.DBConnection, this.DBQueries);
UnitBuilder UnitBuilderObj = new UnitBuilder(this.DBConnection, this.DBQueries);
DI6SubgroupValBuilder SGBuilder = new DI6SubgroupValBuilder(this.DBConnection, this.DBQueries);
IUSBuilder IUSObj = new IUSBuilder(this.DBConnection, this.DBQueries);
int IndicatorNID = -1;
int UnitNID = -1;
int SubgroupValNID = -1;
try
{
IndicatorNID = IndicatorBuilderObj.GetIndicatorNid(indicatorGId, string.Empty);
UnitNID = UnitBuilderObj.GetUnitNid(unitGId, string.Empty);
SubgroupValNID = SGBuilder.GetSubgroupValNid(subgroupGId, string.Empty);
if (IndicatorNID > 0 & UnitNID > 0 & SubgroupValNID > 0)
{
RetVal = IUSObj.InsertIUS(IndicatorNID, UnitNID, SubgroupValNID, 0, 0);
}
}
catch (Exception ex)
{
throw new ApplicationException(ex.ToString());
}
return(RetVal);
}
/// <summary>
/// Returns the metadata text from database
/// </summary>
/// <param name="elementNid"></param>
/// <returns></returns>
public override string GetMetadataTextFrmDB(int elementNid)
{
string RetVal = string.Empty;
string ElementGID = string.Empty;
IndicatorBuilder Indicators;
DI7MetaDataBuilder MDBuilder = null;
try
{
Indicators = new IndicatorBuilder(this.DBConnection, this.DBQueries);
ElementGID = Indicators.GetIndicatorInfo(FilterFieldType.NId, elementNid.ToString(), FieldSelection.Light).GID;
// get xml file from SDMX library
RetVal = DevInfo.Lib.DI_LibSDMX.SDMXUtility.Get_MetadataReport(DI_LibSDMX.SDMXSchemaType.Two_One, ElementGID, DevInfo.Lib.DI_LibSDMX.MetadataTypes.Indicator, "MDAgency", this.DBQueries.LanguageCode.Replace("_", ""), this.DBConnection, this.DBQueries).InnerXml;
MDBuilder = new DI7MetaDataBuilder(this.DBConnection, this.DBQueries);
RetVal = MDBuilder.GetMetadataReportWCategoryName(RetVal, MetadataElementType.Indicator).InnerXml;
}
catch (Exception)
{
RetVal = string.Empty;
}
return RetVal;
}
public override void Import(string selectedGIds)
{
int ProgressCounter = 0;
DataTable Table = null;
string selectedNids = string.Empty;
selectedNids = selectedGIds;
// Initialize progress bar
this.RaiseProgressBarInitialize( selectedGIds.Split(',').GetUpperBound(0) + 1);
IndicatorBuilder TrgIndicatorBuilderObj = null;
IndicatorBuilder SourceIndicatorBuilder = null;
IndicatorInfo IndicatorInfoObj = null;
Dictionary<string, DataRow> FileWithNids=new Dictionary<string,DataRow>();
DIConnection SourceDBConnection = null ;
DIQueries SourceDBQueries = null ;
//-- Step 1: Get TempTable with Sorted SourceFileName
Table = this._TargetDBConnection.ExecuteDataTable(this.ImportQueries.GetImportIndicator(selectedNids));
//-- Step 2:Initialise Indicator Builder with Target DBConnection
TrgIndicatorBuilderObj = new IndicatorBuilder(this.TargetDBConnection, this.TargetDBQueries);
//-- Step 3: Import Nids for each SourceFile
foreach (DataRow Row in Table.Copy().Rows )
{
try
{
string SourceFileWPath = Convert.ToString(Row[MergetTemplateConstants.Columns.COLUMN_SOURCEFILENAME]);
SourceDBConnection = new DIConnection(DIServerType.MsAccess, String.Empty, String.Empty, SourceFileWPath, String.Empty, MergetTemplateConstants.DBPassword);
SourceDBQueries = DataExchange.GetDBQueries(SourceDBConnection);
// Get Source Indicator Info
SourceIndicatorBuilder = new IndicatorBuilder(SourceDBConnection, SourceDBQueries);
IndicatorInfoObj = SourceIndicatorBuilder.GetIndicatorInfo(FilterFieldType.NId,Convert.ToString(Row[MergetTemplateConstants.Columns.COLUMN_SRCNID]),FieldSelection.Light);
// Import INdicator By Nid
TrgIndicatorBuilderObj.ImportIndicator(IndicatorInfoObj, Convert.ToInt32(Row[MergetTemplateConstants.Columns.COLUMN_SRCNID]), SourceDBQueries, SourceDBConnection);
ProgressCounter += 1;
this.RaiseProgressBarIncrement(ProgressCounter);
}
catch (Exception ex) { ExceptionFacade.ThrowException(ex); }
finally
{
if (SourceDBConnection != null)
SourceDBConnection.Dispose();
if (SourceDBQueries != null)
SourceDBQueries.Dispose();
}
}
this._AvailableTable = this.GetAvailableTable();
this._UnmatchedTable = this.GetUnmatchedTable();
// Close ProgressBar
this.RaiseProgressBarClose();
}
public IUSBuilder(DIConnection connection, DIQueries queries)
{
this.DBConnection = connection;
this.DBQueries = queries;
this.DIIndicator = new IndicatorBuilder(connection, queries);
this.DIUnit = new UnitBuilder(connection, queries);
this.DISubgroupVal = new DI6SubgroupValBuilder(connection, queries);
DIConnection.ConnectionType = this.DBConnection.ConnectionStringParameters.ServerType;
}
public IUSBuilder(DIConnection connection, DIQueries queries)
{
this.DBConnection = connection;
this.DBQueries = queries;
this.DIIndicator = new IndicatorBuilder(connection, queries);
this.DIUnit = new UnitBuilder(connection, queries);
this.DISubgroupVal = new DI6SubgroupValBuilder(connection, queries);
DIConnection.ConnectionType = this.DBConnection.ConnectionStringParameters.ServerType;
}
internal AssistantImporter(DIConnection targetConnection, DIQueries targetQueries, List<string> sourceDatabaseFileNamesWPath)
{
this._TargetDBConnection = targetConnection;
this._TargetDBQueries = targetQueries;
this.SourceDatabaseFileNamesWPath = sourceDatabaseFileNamesWPath;
this.AvailableIndicators = new IndicatorBuilder(this._TargetDBConnection, this._TargetDBQueries);
this.AvailableUnit= new UnitBuilder(this._TargetDBConnection, this._TargetDBQueries);
this.AvailableSubgroupVal= new DI6SubgroupValBuilder(this._TargetDBConnection, this._TargetDBQueries);
}
/// <summary>
/// Inserts or updates IUS into database
/// </summary>
/// <param name="indicatorGId"></param>
/// <param name="unitGId"></param>
/// <param name="subgroupGId"></param>
/// <param name="minValue"></param>
/// <param name="maxValue"></param>
/// <returns></returns>
public int InsertOrUpdateIUSIntoDBByName(string indicatorName, string unitName, string subgroupval, int minValue, int maxValue)
{
int RetVal = -1;
IndicatorBuilder IndicatorBuilderObj = new IndicatorBuilder(this.DBConnection, this.DBQueries);
UnitBuilder UnitBuilderObj = new UnitBuilder(this.DBConnection, this.DBQueries);
DI6SubgroupValBuilder SGBuilder = new DI6SubgroupValBuilder(this.DBConnection, this.DBQueries);
IUSBuilder IUSObj = new IUSBuilder(this.DBConnection, this.DBQueries);
int IndicatorNID = -1;
int UnitNID = -1;
int SubgroupValNID = -1;
try
{
IndicatorNID = IndicatorBuilderObj.GetIndicatorNid(string.Empty, indicatorName);
UnitNID = UnitBuilderObj.GetUnitNid(string.Empty, unitName);
SubgroupValNID = SGBuilder.GetSubgroupValNid(string.Empty, subgroupval);
if (IndicatorNID > 0 & UnitNID > 0 & SubgroupValNID > 0)
{
// check IUS combination already exists or not
RetVal = this.GetIUSNid(IndicatorNID, UnitNID, SubgroupValNID);
if (RetVal > 0)
{
//update record
this.UpdateIUS(IndicatorNID, UnitNID, SubgroupValNID, maxValue.ToString(), minValue.ToString(), RetVal);
}
else
{
// insert record
RetVal = IUSObj.InsertIUS(IndicatorNID, UnitNID, SubgroupValNID, minValue, maxValue);
}
}
}
catch (Exception ex)
{
throw new ApplicationException(ex.ToString());
}
return(RetVal);
}
/// <summary>
/// DevInfo_5_0 Indicator Spreadsheet (should have 2 columns only staring the value from 6th Row - Indicator,Indicator_GId)
/// </summary>
/// <param name="templateFileName"></param>
/// <param name="xlsFilenames"></param>
/// <param name="tempFolderPath"></param>
/// <returns></returns>
public bool CreateTemplateFrmIndicatorSpreadsheet(string templateFileName, List<string> xlsFilenames, string tempFolderPath, DIQueries trgQueries, string languageName)
{
bool RetVal = false;
DIConnection DBConnection = null;
DIQueries DBQueries;
IndicatorBuilder IndicatorBuilderObj;
DIDatabase TempTemplateFile;
string DataPrefix = string.Empty;
string LanguageCode = string.Empty;
try
{
//create temp template file
TempTemplateFile = new DIDatabase(templateFileName);
TempTemplateFile.Dispose();
//create DIConnection, queries and indicator objects
DBConnection = new DIConnection(DIServerType.MsAccess, string.Empty, string.Empty, templateFileName, string.Empty, string.Empty);
if (trgQueries == null)
{
DBQueries = new DIQueries(DBConnection.DIDataSetDefault(), DBConnection.DILanguageCodeDefault(DBConnection.DIDataSetDefault()));
}
else
{
DBQueries = new DIQueries(DBConnection.DIDataSetDefault(), trgQueries.LanguageCode);
DataPrefix = DBQueries.DataPrefix;
LanguageCode = trgQueries.LanguageCode.Replace("_", "");
this.UpdateTablesForTargetLanguage(languageName, DBConnection, DBQueries, DataPrefix, LanguageCode);
}
IndicatorBuilderObj = new IndicatorBuilder(DBConnection, DBQueries);
//get indicators from excel files
foreach (string XlsFileName in xlsFilenames)
{
try
{
// insert indicators into template
DIExcel IndicatorXlsFile = new DIExcel(XlsFileName);
IndicatorInfo NewIndicator;
DataTable TempTable = IndicatorXlsFile.GetDataTableFromSheet(IndicatorXlsFile.GetSheetName(0));
//check it is a valid indicator excel file or not
if (TempTable.Rows.Count > 5 & TempTable.Columns.Count > 1)
{
if (TempTable.Rows[2][0].ToString() == DITemplate.IndicatorString)
{
//starting index should be 5
for (int i = 5; i < TempTable.Rows.Count; i++)
{
NewIndicator = new IndicatorInfo();
//indicator Name
NewIndicator.Name = DICommon.RemoveQuotes(TempTable.Rows[i][0].ToString());
//indicator GId
NewIndicator.GID = DICommon.RemoveQuotes(TempTable.Rows[i][1].ToString());
//insert indicator into template
IndicatorBuilderObj.CheckNCreateIndicator(NewIndicator);
}
}
}
}
catch (Exception)
{
//
}
}
RetVal = true;
}
catch (Exception ex)
{
RetVal = false;
throw new ApplicationException(ex.ToString());
}
finally
{
if (DBConnection != null)
{
DBConnection.Dispose();
}
}
return RetVal;
}
/// <summary>
/// Imports indicator metadata from template/database
/// </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 ImportIndicatorMetadata(string dataPrefix, DIConnection sourceDBConnection, DIQueries sourceDBQueries, string selectedNIDs, int selectionCount, MetaDataType metadataType)
{
DataTable TempDataTable;
int CurrentRecordIndex = 0;
DI7MetadataCategoryBuilder MetadataCategoryBuilderObj;
IndicatorInfo SourceIndicatorInfo = new IndicatorInfo();
IndicatorBuilder IndBuilder = null;
try
{
this.RaiseStartProcessEvent();
IndBuilder = new IndicatorBuilder(this.DBConnection, this.DBQueries);
// 1. import metadta categories
// import indicator metadata categories
MetadataCategoryBuilderObj = new DI7MetadataCategoryBuilder(this.DBConnection, this.DBQueries);
MetadataCategoryBuilderObj.ImportAllMetadataCategories(sourceDBConnection, sourceDBQueries, MetadataElementType.Indicator);
// 2. Imort metadata reports
if (selectionCount == -1)
{
// -- GET ALL
TempDataTable = sourceDBConnection.ExecuteDataTable(sourceDBQueries.Indicators.GetIndicator(FilterFieldType.None, string.Empty, FieldSelection.Heavy));
}
else
{
// -- GET SELECTED
TempDataTable = sourceDBConnection.ExecuteDataTable(sourceDBQueries.Indicators.GetIndicator(FilterFieldType.NId, selectedNIDs, FieldSelection.Heavy));
}
//////// -- Initialize Progress Bar
this.RaiseBeforeProcessEvent(TempDataTable.Rows.Count);
foreach (DataRow Row in TempDataTable.Rows)
{
CurrentRecordIndex++;
// get source indicator info
SourceIndicatorInfo = new IndicatorInfo();
SourceIndicatorInfo.Nid = Convert.ToInt32(Row[Indicator.IndicatorNId]);
SourceIndicatorInfo.GID = Convert.ToString(Row[Indicator.IndicatorGId]);
SourceIndicatorInfo.Name = Convert.ToString(Row[Indicator.IndicatorName]);
SourceIndicatorInfo.Global = Convert.ToBoolean(Row[Indicator.IndicatorGlobal]);
//import metadata
IndBuilder.ImportIndicatorMetadata(SourceIndicatorInfo, SourceIndicatorInfo.Nid, sourceDBQueries, sourceDBConnection);
// -- Increemnt the Progress Bar Value
this.RaiseProcessInfoEvent(CurrentRecordIndex);
}
}
catch (Exception ex)
{
throw new ApplicationException(ex.ToString());
}
finally
{
this.RaiseEndProcessEvent();
}
}
private int GetElementNId(string excelFilePath, MetaDataType elementType, int elementNameRowIndex)
{
int RetVal = 0;
string ElementName = string.Empty;
string ElementGId = string.Empty;
SpreadsheetGear.IWorksheet MetadataSheet;
IndicatorBuilder IndBuilder = null;
IndicatorInfo IndInfo = new IndicatorInfo();
DataTable Table = null;
int GIdRowIndex = elementNameRowIndex + 1;
//-- Open excel File and get first worksheet
DIExcel DiExcel = new DIExcel(excelFilePath);
MetadataSheet = DiExcel.GetWorksheet(0);
//-- Get Element GId
ElementGId = DiExcel.GetCellValue(ElementSheetIndex, GIdRowIndex, ElementGIdColumnIndex, GIdRowIndex, ElementGIdColumnIndex);
//-- Get Element Name
ElementName = DiExcel.GetCellValue(ElementSheetIndex, elementNameRowIndex, ElementNameColumnIndex, elementNameRowIndex, ElementNameColumnIndex);
//-- Get GId By Name if GID is blank in Excel File
if (!string.IsNullOrEmpty(ElementGId) || !string.IsNullOrEmpty(ElementName))
{
switch (elementType)
{
case MetaDataType.Indicator:
IndBuilder = new IndicatorBuilder(this.DBConnection, this.DBQueries);
RetVal = IndBuilder.GetIndicatorNid(ElementGId, ElementName);
break;
case MetaDataType.Map:
Table = this.DBConnection.ExecuteDataTable(this.DBQueries.Area.GetAreaMapMetadataByName(string.IsNullOrEmpty(ElementGId) ? ElementName : ElementGId));
if (Table.Rows.Count > 0)
{
RetVal = Convert.ToInt32(Table.Rows[0][Area_Map_Metadata.LayerNId]);
}
break;
case MetaDataType.Source:
case MetaDataType.Sector:
case MetaDataType.Goal:
case MetaDataType.CF:
case MetaDataType.Theme:
case MetaDataType.Institution:
case MetaDataType.Convention:
case MetaDataType.IndicatorClassification:
Table = this.DBConnection.ExecuteDataTable(this.DBQueries.IndicatorClassification.GetIC(FilterFieldType.GId, "'" + ElementGId + "'", FieldSelection.Heavy));
if (Table.Rows.Count > 0)
{
RetVal = Convert.ToInt32(Table.Rows[0][IndicatorClassifications.ICNId]);
}
break;
default:
break;
}
}
return RetVal;
}
/// <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>
/// Inserts or updates IUS into database
/// </summary>
/// <param name="indicatorGId"></param>
/// <param name="unitGId"></param>
/// <param name="subgroupGId"></param>
/// <param name="minValue"></param>
/// <param name="maxValue"></param>
/// <returns></returns>
public int InsertOrUpdateIUSIntoDBByName(string indicatorName, string unitName, string subgroupval, int minValue, int maxValue)
{
int RetVal = -1;
IndicatorBuilder IndicatorBuilderObj = new IndicatorBuilder(this.DBConnection, this.DBQueries);
UnitBuilder UnitBuilderObj = new UnitBuilder(this.DBConnection, this.DBQueries);
DI6SubgroupValBuilder SGBuilder = new DI6SubgroupValBuilder(this.DBConnection, this.DBQueries);
IUSBuilder IUSObj = new IUSBuilder(this.DBConnection, this.DBQueries);
int IndicatorNID = -1;
int UnitNID = -1;
int SubgroupValNID = -1;
try
{
IndicatorNID = IndicatorBuilderObj.GetIndicatorNid(string.Empty, indicatorName);
UnitNID = UnitBuilderObj.GetUnitNid(string.Empty, unitName);
SubgroupValNID = SGBuilder.GetSubgroupValNid(string.Empty, subgroupval);
if (IndicatorNID > 0 & UnitNID > 0 & SubgroupValNID > 0)
{
// check IUS combination already exists or not
RetVal = this.GetIUSNid(IndicatorNID, UnitNID, SubgroupValNID);
if (RetVal > 0)
{
//update record
this.UpdateIUS(IndicatorNID, UnitNID, SubgroupValNID, maxValue.ToString(), minValue.ToString(), RetVal);
}
else
{
// insert record
RetVal = IUSObj.InsertIUS(IndicatorNID, UnitNID, SubgroupValNID, minValue, maxValue);
}
}
}
catch (Exception ex)
{
throw new ApplicationException(ex.ToString());
}
return RetVal;
}
/// <summary>
/// Returns instance of IUSInfo.
/// </summary>
/// <param name="filterClause"></param>
/// <param name="filterText"></param>
/// <param name="selectionType"></param>
/// <returns></returns>
public IUSInfo GetIUSInfo(FilterFieldType filterClause, string filterText, FieldSelection selectionType)
{
string Query = string.Empty;
IUSInfo RetVal = new IUSInfo();
IndicatorInfo IndicatorObject = new IndicatorInfo();
UnitInfo UnitObject = new UnitInfo();
IndicatorBuilder IndicatorBuilderObj = null;
UnitBuilder UnitBuilderObj = null;
DI6SubgroupValBuilder SubgroupValBuilderObj = null;
DI6SubgroupValInfo SubgroupValObject = new DI6SubgroupValInfo();
int IndicatorNid = 0;
int UnitNid = 0;
int SGNid = 0;
int MinVal = 0;
int MaxVal = 0;
DataTable Table = null;
try
{
//get IUS information
Query = this.DBQueries.IUS.GetIUS(filterClause, filterText, selectionType);
Table = this.DBConnection.ExecuteDataTable(Query);
//set IUS info
if (Table != null)
{
if (Table.Rows.Count > 0)
{
MinVal = 0;
MaxVal = 0;
// initialize builder objects
IndicatorBuilderObj = new IndicatorBuilder(this.DBConnection, this.DBQueries);
UnitBuilderObj = new UnitBuilder(this.DBConnection, this.DBQueries);
SubgroupValBuilderObj = new DI6SubgroupValBuilder(this.DBConnection, this.DBQueries);
// set IUS properties
//-- set maximum value
if (!string.IsNullOrEmpty(Convert.ToString(Table.Rows[0][Indicator_Unit_Subgroup.MaxValue])))
{
MaxVal = Convert.ToInt32(Table.Rows[0][Indicator_Unit_Subgroup.MaxValue]);
}
//-- Set Minmum Value
if (!string.IsNullOrEmpty(Convert.ToString(Table.Rows[0][Indicator_Unit_Subgroup.MinValue])))
{
MinVal = Convert.ToInt32(Table.Rows[0][Indicator_Unit_Subgroup.MinValue]);
}
RetVal.Maximum = MaxVal; // Convert.ToInt32(Table.Rows[0][Indicator_Unit_Subgroup.MaxValue]);
RetVal.Minimum = MinVal; // Convert.ToInt32(Table.Rows[0][Indicator_Unit_Subgroup.MinValue]);
RetVal.Nid = Convert.ToInt32(Table.Rows[0][Indicator_Unit_Subgroup.IUSNId]);
// set indicator, unit and subgroup info
IndicatorNid = Convert.ToInt32(Table.Rows[0][Indicator_Unit_Subgroup.IndicatorNId]);
UnitNid = Convert.ToInt32(Table.Rows[0][Indicator_Unit_Subgroup.UnitNId]);
SGNid = Convert.ToInt32(Table.Rows[0][Indicator_Unit_Subgroup.SubgroupValNId]);
RetVal.IndicatorInfo = IndicatorBuilderObj.GetIndicatorInfo(FilterFieldType.NId, IndicatorNid.ToString(), FieldSelection.Light);
RetVal.UnitInfo = UnitBuilderObj.GetUnitInfo(FilterFieldType.NId, UnitNid.ToString());
RetVal.SubgroupValInfo = SubgroupValBuilderObj.GetSubgroupValInfo(FilterFieldType.NId, SGNid.ToString());
}
}
}
catch (Exception)
{
RetVal = null;
}
return(RetVal);
}
/// <summary>
/// Process Matched Indicators
/// </summary>
public void ProcessMatchedIndicators()
{
DataTable Table = null;
string selectedNids = string.Empty;
IndicatorBuilder IndicatorBuilderObj = null;
IndicatorBuilder SrcIndicatorBuilderObj = null;
IndicatorInfo SrcIndicatorInfoObj = 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.GetMatchedIndicator());
//-- Step 2:Initialise Indicator Builder with Target DBConnection
IndicatorBuilderObj = new IndicatorBuilder(this.DBConnection, this.DBQueries);
//-- Step 3: Import Nids for each SourceFile
foreach (DataRow Row in Table.Copy().Rows)
{
try
{
string SourceFileWPath = Convert.ToString(Row[MergetTemplateConstants.Columns.COLUMN_SOURCEFILENAME]);
SourceDBConnection = new DIConnection(DIServerType.MsAccess, String.Empty, String.Empty, SourceFileWPath, String.Empty, MergetTemplateConstants.DBPassword);
SourceDBQueries = DataExchange.GetDBQueries(SourceDBConnection);
//get indicator info
SrcIndicatorBuilderObj = new IndicatorBuilder(SourceDBConnection, SourceDBQueries);
SrcIndicatorInfoObj = SrcIndicatorBuilderObj.GetIndicatorInfo(FilterFieldType.NId, Convert.ToString(Row[Indicator.IndicatorNId]), FieldSelection.Light);
//import indicator
IndicatorBuilderObj.ImportIndicator(SrcIndicatorInfoObj, Convert.ToInt32(Row[Indicator.IndicatorNId]), SourceDBQueries, SourceDBConnection);
}
catch (Exception ex) { ExceptionFacade.ThrowException(ex); }
finally
{
if (SourceDBConnection != null)
SourceDBConnection.Dispose();
if (SourceDBQueries != null)
SourceDBQueries.Dispose();
}
}
}
/// <summary>
/// Imports indicator metadata from template/database
/// </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 ImportIndicatorMetadata(string dataPrefix, DIConnection sourceDBConnection, DIQueries sourceDBQueries, string selectedNIDs, int selectionCount, MetaDataType metadataType)
{
DataTable TempDataTable;
int CurrentRecordIndex = 0;
DI7MetadataCategoryBuilder MetadataCategoryBuilderObj;
IndicatorInfo SourceIndicatorInfo = new IndicatorInfo();
IndicatorBuilder IndBuilder = null;
try
{
this.RaiseStartProcessEvent();
IndBuilder = new IndicatorBuilder(this.DBConnection, this.DBQueries);
// 1. import metadta categories
// import indicator metadata categories
MetadataCategoryBuilderObj = new DI7MetadataCategoryBuilder(this.DBConnection, this.DBQueries);
MetadataCategoryBuilderObj.ImportAllMetadataCategories(sourceDBConnection, sourceDBQueries, MetadataElementType.Indicator);
// 2. Imort metadata reports
if (selectionCount == -1)
{
// -- GET ALL
TempDataTable = sourceDBConnection.ExecuteDataTable(sourceDBQueries.Indicators.GetIndicator(FilterFieldType.None, string.Empty, FieldSelection.Heavy));
}
else
{
// -- GET SELECTED
TempDataTable = sourceDBConnection.ExecuteDataTable(sourceDBQueries.Indicators.GetIndicator(FilterFieldType.NId, selectedNIDs, FieldSelection.Heavy));
}
//////// -- Initialize Progress Bar
this.RaiseBeforeProcessEvent(TempDataTable.Rows.Count);
foreach (DataRow Row in TempDataTable.Rows)
{
CurrentRecordIndex++;
// get source indicator info
SourceIndicatorInfo = new IndicatorInfo();
SourceIndicatorInfo.Nid = Convert.ToInt32(Row[Indicator.IndicatorNId]);
SourceIndicatorInfo.GID = Convert.ToString(Row[Indicator.IndicatorGId]);
SourceIndicatorInfo.Name = Convert.ToString(Row[Indicator.IndicatorName]);
SourceIndicatorInfo.Global = Convert.ToBoolean(Row[Indicator.IndicatorGlobal]);
//import metadata
IndBuilder.ImportIndicatorMetadata(SourceIndicatorInfo, SourceIndicatorInfo.Nid, sourceDBQueries, sourceDBConnection);
// -- Increemnt the Progress Bar Value
this.RaiseProcessInfoEvent(CurrentRecordIndex);
}
}
catch (Exception ex)
{
throw new ApplicationException(ex.ToString());
}
finally
{
this.RaiseEndProcessEvent();
}
}
private void ProcessMappedIndicators()
{
DIConnection SrcDBConnection = null;
DIQueries SrcDBQueries = null;
IndicatorBuilder TrgIndicatorBilderObj = null;
IndicatorBuilder SourceIndicatorBilderObj = null;
IndicatorInfo SrcIndicatorInfoObj = null;
DataTable Table = null;
string SourceFileWPath = string.Empty;
int TrgIndicatorNid = 0;
try
{
if (this.MappedTables.ContainsKey(TemplateMergeControlType.Indicator))
{
TrgIndicatorBilderObj = new IndicatorBuilder(this.DBConnection, this.DBQueries);
foreach (DataRow Row in this.MappedTables[TemplateMergeControlType.Indicator].MappedTable.MappedTable.Rows)
{
Table = this.DBConnection.ExecuteDataTable(TemplateQueries.GetImportIndicator(Convert.ToString(Row[MergetTemplateConstants.Columns.UNMATCHED_COL_Prefix + Indicator.IndicatorNId])));
TrgIndicatorNid = Convert.ToInt32(Row[MergetTemplateConstants.Columns.AVAILABLE_COL_Prefix + Indicator.IndicatorNId]);
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 Indicator Info
SourceIndicatorBilderObj = new IndicatorBuilder(SrcDBConnection, SrcDBQueries);
SrcIndicatorInfoObj = SrcIndicatorInfoObj = SourceIndicatorBilderObj.GetIndicatorInfo(FilterFieldType.NId, Convert.ToString(Table.Rows[0][MergetTemplateConstants.Columns.COLUMN_SRCNID]), FieldSelection.Light);
// Import Mapped Indicator Values from Source
TrgIndicatorBilderObj.ImportIndicatorFrmMappedIndicator(SrcIndicatorInfoObj, SrcIndicatorInfoObj.Nid, TrgIndicatorNid, SrcDBQueries, SrcDBConnection);
}
finally
{
if (SrcDBConnection != null)
{
SrcDBConnection.Dispose();
SrcDBQueries.Dispose();
}
}
}
}
}
}
catch (Exception ex)
{
ExceptionFacade.ThrowException(ex);
}
}
//private int ImportIndicator(DataRow row)
//{
// int RetVal = -1;
// IndicatorInfo IndicatorRecord;
// IndicatorBuilder IndicatorBuilderObj;
// IndicatorRecord = this.GetIndicatorInfo(row);
// IndicatorBuilderObj = new IndicatorBuilder(this._TargetDBConnection, this._TargetDBQueries);
// RetVal = IndicatorBuilderObj.ImportIndicator(IndicatorRecord, IndicatorRecord.Nid, this.SourceDBQueries, this.SourceDBConnection);
// return RetVal;
//}
//private int ImportUnit(DataRow row)
//{
// int RetVal = -1;
// UnitInfo UnitRecord;
// UnitBuilder UnitBuilderObj;
// UnitRecord = this.GetUnitInfo(row);
// UnitBuilderObj = new UnitBuilder(this._TargetDBConnection, this._TargetDBQueries);
// RetVal = UnitBuilderObj.ImportUnit(UnitRecord, UnitRecord.Nid, this.SourceDBQueries, this.SourceDBConnection);
// return RetVal;
//}
//private int ImportSubgroupVal(DataRow row)
//{
// int RetVal = -1;
// DI6SubgroupValBuilder SubgroupValBuilderObj = new DI6SubgroupValBuilder(this._TargetDBConnection, this._TargetDBQueries);
// //import into target database
// RetVal = SubgroupValBuilderObj.ImportSubgroupVal(Convert.ToInt32(row[SubgroupVals.SubgroupValNId]), this.SourceDBQueries, this.SourceDBConnection);
// return RetVal;
//}
//private void ImportIUS(List<string> selectedNids, bool allSelected)
//{
// int ProgressBarValue = 1;
// int IndicatorNId = -1;
// int UnitNId = -1;
// int SGValNId = -1;
// int MinValue=0;
// int MaxValue=0;
// DataRow[] Rows;
// DataTable TempTable;
// List<string> ImportedIndicatorNIDs = new List<string>();
// List<string> ImportedUnitNIDs = new List<string>();
// List<string> ImportedSGNIDs = new List<string>();
// IUSBuilder IusBuilderObj ;
// if (this.ShowIUS)
// {
// // Get selected rows
// if (allSelected)
// {
// Rows = this.SourceTable.Select("1=1");
// }
// else
// {
// Rows = this.SourceTable.Select(this.TagValueColumnName + " IN ( " + string.Join(",", selectedNids.ToArray()) + ")");
// }
// foreach (DataRow Row in Rows)
// {
// try
// {
// //import Indicator
// IndicatorNId = Convert.ToInt32(Row[Indicator.IndicatorNId]);
// if (!ImportedIndicatorNIDs.Contains(IndicatorNId.ToString()))
// {
// ImportedIndicatorNIDs.Add(IndicatorNId.ToString());
// IndicatorNId = this.ImportIndicator(Row);
// }
// // import unit
// UnitNId = Convert.ToInt32(Row[Unit.UnitNId]);
// if (!ImportedIndicatorNIDs.Contains(UnitNId.ToString()))
// {
// ImportedUnitNIDs.Add(UnitNId.ToString());
// UnitNId = this.ImportUnit(Row);
// }
// // import subgroupval
// SGValNId=Convert.ToInt32(Row[SubgroupVals.SubgroupValNId]);
// if (!ImportedSGNIDs.Contains(SGValNId.ToString()))
// {
// ImportedSGNIDs.Add(SGValNId.ToString());
// SGValNId = this.ImportSubgroupVal(Row);
// }
// //import IUS
// if (IndicatorNId > 0 && UnitNId > 0 && SGValNId > 0)
// {
// //if(Convert.ToString(Row[Indicator_Unit_Subgroup.MaxValue]).Length>0)
// //{
// //MaxValue= Convert.ToInt32(Row[Indicator_Unit_Subgroup.MaxValue]);
// //}
// //else
// //{
// // MaxValue=0;
// //}
// //if(Convert.ToString(Row[Indicator_Unit_Subgroup.MinValue]).Length>0)
// //{
// // MinValue= Convert.ToInt32(Row[Indicator_Unit_Subgroup.MinValue]);
// //}
// //else
// //{
// // MinValue=0;
// //}
// IusBuilderObj = new IUSBuilder( this._TargetDBConnection, this._TargetDBQueries);
// IusBuilderObj.ImportIUS(IndicatorNId, UnitNId, SGValNId, MinValue, MaxValue , this.SourceDBQueries, this.SourceDBConnection);
// }
// if (this.ShowSector)
// {
// this.ImportSector(Row);
// }
// }
// catch (Exception)
// {
// throw;
// }
// this.RaiseIncrementProgessBarEvent(ProgressBarValue);
// ProgressBarValue++;
// }
// }
//}
/// <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)
{
DataRow Row;
IndicatorInfo IndicatorRecord = null;
IndicatorBuilder IndicatorBuilderObj = null;
DI7MetadataCategoryBuilder MetadataCategoryBuilderObj = null;
int ProgressBarValue = 1;
// import indicator metadata categories
MetadataCategoryBuilderObj = new DI7MetadataCategoryBuilder(this._TargetDBConnection, this._TargetDBQueries);
MetadataCategoryBuilderObj.ImportAllMetadataCategories(this.SourceDBConnection, this.SourceDBQueries, MetadataElementType.Indicator);
// import IUS or indicator
foreach (string Nid in selectedNids)
{
try
{
Row = this.SourceTable.Select(this.TagValueColumnName + "=" + Nid)[0];
if (this._ShowIUS)
{
this.ImportForIUS(Row);
}
else
{
//import indicator
IndicatorRecord = this.GetIndicatorInfo(Row);
IndicatorBuilderObj = new IndicatorBuilder(this._TargetDBConnection, this._TargetDBQueries);
IndicatorBuilderObj.ImportIndicator(IndicatorRecord, Convert.ToInt32(Nid), this.SourceDBQueries, this.SourceDBConnection);
if (this._ShowSector)
{
this.ImportSector(Row);
}
}
}
catch (Exception)
{
throw;
}
this.RaiseIncrementProgessBarEvent(ProgressBarValue);
ProgressBarValue++;
}
if (this._ShowIUS && this._ShowSector)
{
this.ImportSectors(selectedNids, allSelected);
}
}
/// <summary>
/// Returns instance of IUSInfo.
/// </summary>
/// <param name="filterClause"></param>
/// <param name="filterText"></param>
/// <param name="selectionType"></param>
/// <returns></returns>
public IUSInfo GetIUSInfo(FilterFieldType filterClause, string filterText, FieldSelection selectionType)
{
string Query = string.Empty;
IUSInfo RetVal = new IUSInfo();
IndicatorInfo IndicatorObject = new IndicatorInfo();
UnitInfo UnitObject = new UnitInfo();
IndicatorBuilder IndicatorBuilderObj = null;
UnitBuilder UnitBuilderObj = null;
DI6SubgroupValBuilder SubgroupValBuilderObj = null;
DI6SubgroupValInfo SubgroupValObject = new DI6SubgroupValInfo();
int IndicatorNid = 0;
int UnitNid = 0;
int SGNid = 0;
int MinVal = 0;
int MaxVal = 0;
DataTable Table = null;
try
{
//get IUS information
Query = this.DBQueries.IUS.GetIUS(filterClause, filterText, selectionType);
Table = this.DBConnection.ExecuteDataTable(Query);
//set IUS info
if (Table != null)
{
if (Table.Rows.Count > 0)
{
MinVal = 0;
MaxVal = 0;
// initialize builder objects
IndicatorBuilderObj = new IndicatorBuilder(this.DBConnection, this.DBQueries);
UnitBuilderObj = new UnitBuilder(this.DBConnection, this.DBQueries);
SubgroupValBuilderObj = new DI6SubgroupValBuilder(this.DBConnection, this.DBQueries);
// set IUS properties
//-- set maximum value
if (!string.IsNullOrEmpty(Convert.ToString(Table.Rows[0][Indicator_Unit_Subgroup.MaxValue])))
{
MaxVal = Convert.ToInt32(Table.Rows[0][Indicator_Unit_Subgroup.MaxValue]);
}
//-- Set Minmum Value
if (!string.IsNullOrEmpty(Convert.ToString(Table.Rows[0][Indicator_Unit_Subgroup.MinValue])))
{
MinVal = Convert.ToInt32(Table.Rows[0][Indicator_Unit_Subgroup.MinValue]);
}
RetVal.Maximum = MaxVal; // Convert.ToInt32(Table.Rows[0][Indicator_Unit_Subgroup.MaxValue]);
RetVal.Minimum = MinVal; // Convert.ToInt32(Table.Rows[0][Indicator_Unit_Subgroup.MinValue]);
RetVal.Nid = Convert.ToInt32(Table.Rows[0][Indicator_Unit_Subgroup.IUSNId]);
// set indicator, unit and subgroup info
IndicatorNid = Convert.ToInt32(Table.Rows[0][Indicator_Unit_Subgroup.IndicatorNId]);
UnitNid = Convert.ToInt32(Table.Rows[0][Indicator_Unit_Subgroup.UnitNId]);
SGNid = Convert.ToInt32(Table.Rows[0][Indicator_Unit_Subgroup.SubgroupValNId]);
RetVal.IndicatorInfo = IndicatorBuilderObj.GetIndicatorInfo(FilterFieldType.NId, IndicatorNid.ToString(), FieldSelection.Light);
RetVal.UnitInfo = UnitBuilderObj.GetUnitInfo(FilterFieldType.NId, UnitNid.ToString());
RetVal.SubgroupValInfo = SubgroupValBuilderObj.GetSubgroupValInfo(FilterFieldType.NId, SGNid.ToString());
}
}
}
catch (Exception)
{
RetVal = null;
}
return RetVal;
}
/// <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>
/// Inserts IUS into database
/// </summary>
/// <param name="indicatorGId"></param>
/// <param name="unitGId"></param>
/// <param name="subgroupGId"></param>
/// <returns></returns>
public int InsertIUSIntoDB(string indicatorGId, string unitGId, string subgroupGId)
{
int RetVal = -1;
IndicatorBuilder IndicatorBuilderObj = new IndicatorBuilder(this.DBConnection, this.DBQueries);
UnitBuilder UnitBuilderObj = new UnitBuilder(this.DBConnection, this.DBQueries);
DI6SubgroupValBuilder SGBuilder = new DI6SubgroupValBuilder(this.DBConnection, this.DBQueries);
IUSBuilder IUSObj = new IUSBuilder(this.DBConnection, this.DBQueries);
int IndicatorNID = -1;
int UnitNID = -1;
int SubgroupValNID = -1;
try
{
IndicatorNID = IndicatorBuilderObj.GetIndicatorNid(indicatorGId, string.Empty);
UnitNID = UnitBuilderObj.GetUnitNid(unitGId, string.Empty);
SubgroupValNID = SGBuilder.GetSubgroupValNid(subgroupGId, string.Empty);
if (IndicatorNID > 0 & UnitNID > 0 & SubgroupValNID > 0)
{
RetVal = IUSObj.InsertIUS(IndicatorNID, UnitNID, SubgroupValNID, 0, 0);
}
}
catch (Exception ex)
{
throw new ApplicationException(ex.ToString());
}
return RetVal;
}
/// <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>
/// Generates blank DES
/// </summary>
/// <param name="singleWorkbook"></param>
/// <param name="dBConnection"></param>
/// <param name="dBQueries"></param>
/// <param name="userSelection"></param>
/// <param name="sortedFields"></param>
/// <param name="xlsFileNameWPath"></param>
/// <param name="includeGUID"></param>
/// <param name="languageFileNameWPath"></param>
/// <returns></returns>
private DataView GetBlankDESDataView(DataView blankDESDataView, string indicatorNId, string unitNId, bool singleWorkbook, DIConnection dBConnection, DIQueries dBQueries, UserSelection userSelection, bool includeGUID)
{
DataView RetVal = null;
string SqlQuery = string.Empty;
string OriginalSubgroupNIds = string.Empty;
DataTable AutoSubgroupValsTable = null;
IndicatorInfo IndicatorInfoObj;
IndicatorBuilder IndicatorBuilderObj;
UnitInfo UnitInfoObj;
UnitBuilder UnitBuilderObj;
DI6SubgroupValBuilder SGValBuilder;
DI6SubgroupValInfo SGValINfo;
DataTable DT;
try
{
// 1. Get blank DES dataview only if it is null
if (blankDESDataView == null)
{
blankDESDataView = GetBlankDESDataViewWithoutIUS(userSelection, dBConnection, dBQueries);
}
// 2. get dAtaview for the given Indicator + Unit.
try
{
// 3. Preserve original SubgroupNIds into a temp
OriginalSubgroupNIds = userSelection.SubgroupValNIds;
// 4. If SubgroupVal is blank, then get all subgroups for which given IU are combined as IUS.
if (userSelection.SubgroupValNIds.Length == 0)
{
if (userSelection.ShowIUS & userSelection.IndicatorNIds.Length > 0)
{
// 4.1 Get SubgroupNIDs for given IUSNId as userSelection.IndicatorNId
AutoSubgroupValsTable = dBConnection.ExecuteDataTable(dBQueries.IUS.GetIUS(FilterFieldType.NId, userSelection.IndicatorNIds, FieldSelection.Light));
// 4.2 Set filter for given I, U
AutoSubgroupValsTable.DefaultView.RowFilter = Indicator.IndicatorNId + " = " + indicatorNId + " AND " + Unit.UnitNId + " = " + unitNId;
AutoSubgroupValsTable = AutoSubgroupValsTable.DefaultView.ToTable();
}
else
{
// 4.3 Get SubgroupNIDs for given Indicator + Unit
AutoSubgroupValsTable = dBConnection.ExecuteDataTable(dBQueries.IUS.GetIUSNIdByI_U_S(indicatorNId, unitNId, string.Empty));
}
// 4.4 Set those SubgroupNIds into userSelection
userSelection.SubgroupValNIds = DIExport.DataColumnValuesToString(AutoSubgroupValsTable, SubgroupVals.SubgroupValNId);
}
//////// get IUSNIds on the basis of IndicatorsNId,UnitsNId and subgroupValSNId
//////SqlQuery = dBQueries.IUS.GetIUSByI_U_S(indicatorNId, unitNId, userSelection.SubgroupValNIds);
//////IUSTable = dBConnection.ExecuteDataTable(SqlQuery);
//////IUSNIds = DIConnection.GetDelimitedValuesFromDataTable(IUSTable, Indicator_Unit_Subgroup.IUSNId);
// **************************************************************************
// get indicator,unit and subgroup info
IndicatorBuilderObj = new IndicatorBuilder(dBConnection, dBQueries);
IndicatorInfoObj = IndicatorBuilderObj.GetIndicatorInfo(FilterFieldType.NId, indicatorNId, FieldSelection.Light);
UnitBuilderObj = new UnitBuilder(dBConnection, dBQueries);
UnitInfoObj = UnitBuilderObj.GetUnitInfo(FilterFieldType.NId, unitNId);
SGValBuilder = new DI6SubgroupValBuilder(dBConnection, dBQueries);
// 5. process and update filteredDataview for all subgroup nids
if (RetVal == null)
{
RetVal = new DataView(blankDESDataView.Table.Copy());
RetVal.Table.Clear();
// add subgroup columns
if (includeGUID)
{
RetVal.Table.Columns.Add(SubgroupVals.SubgroupValGId);
}
RetVal.Table.Columns.Add(SubgroupVals.SubgroupVal);
// check and insert columns for indicator & unit
if (!(RetVal.Table.Columns.Contains(Indicator.IndicatorName)))
{
RetVal.Table.Columns.Add(Indicator.IndicatorName);
}
if (!(RetVal.Table.Columns.Contains(Indicator.IndicatorGId)))
{
RetVal.Table.Columns.Add(Indicator.IndicatorGId);
}
if (!(RetVal.Table.Columns.Contains(Unit.UnitName)))
{
RetVal.Table.Columns.Add(Unit.UnitName);
}
if (!(RetVal.Table.Columns.Contains(Unit.UnitGId)))
{
RetVal.Table.Columns.Add(Unit.UnitGId);
}
// set indicator and unit values
RetVal.Table.Columns[Indicator.IndicatorGId].DefaultValue = IndicatorInfoObj.GID;
RetVal.Table.Columns[Indicator.IndicatorName].DefaultValue = IndicatorInfoObj.Name;
RetVal.Table.Columns[Unit.UnitGId].DefaultValue = UnitInfoObj.GID;
RetVal.Table.Columns[Unit.UnitName].DefaultValue = UnitInfoObj.Name;
}
else
{
RetVal.Table.Clear();
}
foreach (string SGNid in DICommon.SplitString(userSelection.SubgroupValNIds, ","))
{
//////// 5.1 if rows are morethan 65500 then dont add more rows
//////if (RetVal.Table.Rows.Count > 65500)
//////{
////// break;
//////}
// 5.2 get subgroup val info
SGValINfo = SGValBuilder.GetSubgroupValInfo(FilterFieldType.NId, SGNid);
DT = blankDESDataView.Table.Copy();
DT.Clear();
// 5.3 add subgroup columns
if (includeGUID)
{
DT.Columns.Add(SubgroupVals.SubgroupValGId);
DT.Columns[SubgroupVals.SubgroupValGId].DefaultValue = SGValINfo.GID;
}
DT.Columns.Add(SubgroupVals.SubgroupVal);
DT.Columns[SubgroupVals.SubgroupVal].DefaultValue = SGValINfo.Name;
// 5.4 merge blank DES data view
DT.Merge(blankDESDataView.Table);
// 5.5 update Subgroup in BlankDESDataView
RetVal.Table.Merge(DT);
}
// **************************************************************************
//////// 5.6 Limit DataRows to 65,536 as Excels sheet has 65,536 rows limit.
//////if (RetVal.Table.Rows.Count > 65500)
//////{
////// for (int i = RetVal.Table.Rows.Count - 1; i > 65500; i--)
////// {
////// RetVal.Table.Rows[i].Delete();
////// }
//////}
//////RetVal.Table.AcceptChanges();
// 5.7 Set original SubgroupNIDs back into userSelection
userSelection.SubgroupValNIds = OriginalSubgroupNIds;
////////// 5.9 Rename GID columns if not required.
////////if (!(includeGUID))
////////{
//////// FilteredDataView.Table.Columns[Indicator.IndicatorGId].ColumnName = "I_GID";
//////// FilteredDataView.Table.Columns[Unit.UnitGId].ColumnName = "U_GID";
//////// if (FilteredDataView.Table.Columns.Contains(SubgroupVals.SubgroupValGId))
//////// {
//////// FilteredDataView.Table.Columns[SubgroupVals.SubgroupValGId].ColumnName = "S_GID";
//////// }
////////}
////////// 5.10 Update Sector , Class in DataView
////////DIExport.AddSectorClassInDataView(ref FilteredDataView, dBConnection, dBQueries, indicatorNId, unitNId);
////////FilteredDataView.Table.AcceptChanges();
}
catch (Exception ex)
{
}
finally
{
if (RetVal == null)
{
RetVal = (new DataTable()).DefaultView;
}
}
}
catch (Exception ex)
{
}
finally
{
if (RetVal == null)
{
RetVal = (new DataTable()).DefaultView;
}
}
return RetVal;
}
private string GetElementInfo(MetaDataType elementType, string elementGId)
{
string RetVal = string.Empty;
IndicatorBuilder IndBuilder = null;
IndicatorInfo IndInfo = new IndicatorInfo();
DataTable Table = null;
elementGId = DICommon.RemoveQuotes(elementGId);
try
{
switch (elementType)
{
case MetaDataType.Indicator:
IndBuilder = new IndicatorBuilder(this.DBConnection, this.DBQueries);
IndInfo = IndBuilder.GetIndicatorInfo(FilterFieldType.GId, "'" + elementGId + "'", FieldSelection.Heavy);
RetVal = IndInfo.Info;
break;
case MetaDataType.Map:
Table = this.DBConnection.ExecuteDataTable(this.DBQueries.Area.GetAreaMapMetadataByName(elementGId));
RetVal = Convert.ToString(Table.Rows[0][Area_Map_Metadata.MetadataText]);
break;
case MetaDataType.Source:
case MetaDataType.Sector:
case MetaDataType.Goal:
case MetaDataType.CF:
case MetaDataType.Theme:
case MetaDataType.Institution:
case MetaDataType.Convention:
case MetaDataType.IndicatorClassification:
Table = this.DBConnection.ExecuteDataTable(this.DBQueries.IndicatorClassification.GetIC(FilterFieldType.GId, "'" + elementGId + "'", FieldSelection.Heavy));
RetVal = Convert.ToString(Table.Rows[0][IndicatorClassifications.ICInfo]);
break;
default:
break;
}
}
catch (Exception)
{
}
return RetVal;
}