/// <summary>
/// Rename Data_value and TextualValue column into OrgData_value and OrgTextualValue
/// and merge both column value into new column Data_Value
/// </summary>
/// <param name="dbConnection"></param>
/// <param name="dbQueries"></param>
public static void MergeTextualandNumericDataValueColumn(DIConnection dbConnection, DIQueries dbQueries)
{
try
{
if (!ISColumnExists(dbConnection, Constants.Data.Orginal_Data_valueColumn, dbQueries.TablesName.Data))
{
//1. Rename Textual_Data_Value & Data_value to orgTextual_Data_value & orgData_Value respectively
RenameOriganalDataTableColumnInDatabase(dbConnection, dbQueries, Data.TextualDataValue, Constants.Data.Orginal_Textual_Data_valueColumn, " Memo");
RenameOriganalDataTableColumnInDatabase(dbConnection, dbQueries, Data.DataValue, Constants.Data.Orginal_Data_valueColumn, " Double ");
System.Threading.Thread.Sleep(100);
//2. Create new column Data_Value of memo data type
string SqlQuery = "ALTER Table " + dbQueries.TablesName.Data + " Add Column " + Data.DataValue + " Memo NULL";
dbConnection.ExecuteNonQuery(SqlQuery);
System.Threading.Thread.Sleep(10);
//3. Merge all data values into Data_Value column
SqlQuery = "UPDATE " + dbQueries.TablesName.Data + " SET " + Data.DataValue + "=" + Constants.Data.Orginal_Data_valueColumn;
dbConnection.ExecuteNonQuery(SqlQuery);
SqlQuery = "UPDATE " + dbQueries.TablesName.Data + " SET " + Data.DataValue + "=" + Constants.Data.Orginal_Textual_Data_valueColumn + " WHERE " + Data.IsTextualData + "=" + true;
dbConnection.ExecuteNonQuery(SqlQuery);
}
}
catch (Exception ex)
{
throw ex;
}
}
internal CategorySchemeUtility(CategorySchemeTypes categorySchemeType, bool completeOrSummaryFlag, string agencyId, string language, Header header, string outputFolder, Dictionary<string, string> DictIndicator, Dictionary<string, string> DictIndicatorMapping, DIConnection DIConnection, DIQueries DIQueries)
: base(agencyId, language, header, outputFolder)
{
this._categorySchemeType = categorySchemeType;
this._completeOrSummaryFlag = completeOrSummaryFlag;
this._diConnection = DIConnection;
this._diQueries = DIQueries;
if (string.IsNullOrEmpty(language))
{
this.Language = this._diQueries.LanguageCode.Substring(1);
this._multiLanguageHandlingRequired = true;
}
else
{
if (this._diConnection.IsValidDILanguage(this._diQueries.DataPrefix, language))
{
this.Language = language;
this._multiLanguageHandlingRequired = false;
}
else
{
this.Language = this._diQueries.LanguageCode.Substring(1);
this._multiLanguageHandlingRequired = false;
}
}
this._dictIndicator = DictIndicator;
this._dictIndicatorMapping = DictIndicatorMapping;
}
/// <summary>
/// Constructor
/// </summary>
/// <param name="fileNameWPath"></param>
public DI6ToDI5Converter(string fileNameWPath)
{
this.DBConnection = new DIConnection(DIServerType.MsAccess, string.Empty, string.Empty, fileNameWPath, string.Empty, string.Empty);
this.DBQueries = new DIQueries(this.DBConnection.DIDataSetDefault(), this.DBConnection.DILanguageCodeDefault("UT_"));
this.FilenameWPath = fileNameWPath;
}
public ElementMapper(DIConnection dbConnection, DIQueries dbQueries, Dictionary<TemplateMergeControlType, MergeTableInfo> mappedTables)
{
this.DBConnection = dbConnection;
this.DBQueries = dbQueries;
this.TemplateQueries = new MergeTemplateQueries(this.DBQueries.DataPrefix, this.DBQueries.LanguageCode);
this.MappedTables = mappedTables;
}
public static DIData CreateInstance(DIImportFileType importFileType, List<string> sourceFileNamesWPath,
string tempFolderPath, DIConnection dbConnection, DIQueries dbQueries)
{
DIData RetVal = null;
switch (importFileType)
{
case DIImportFileType.DataEntrySpreasheet:
break;
case DIImportFileType.Database:
break;
case DIImportFileType.Template:
break;
case DIImportFileType.SDMXXml:
RetVal = new SDMX.SDMXXml(sourceFileNamesWPath, tempFolderPath, dbConnection, dbQueries);
break;
default:
break;
}
if (RetVal != null)
{
RetVal.ImportFileType = importFileType;
}
return RetVal;
}
internal MetadataReportUtility(string agencyId, string language, Header header, DIConnection DIConnection, DIQueries DIQueries)
{
this._agencyId = agencyId;
this._header = header;
this._diConnection = DIConnection;
this._diQueries = DIQueries;
if (string.IsNullOrEmpty(language))
{
this._language = this._diQueries.LanguageCode.Substring(1);
this._multiLanguageHandlingRequired = true;
}
else
{
if (this._diConnection.IsValidDILanguage(this._diQueries.DataPrefix, language))
{
this._language = language;
this._multiLanguageHandlingRequired = false;
}
else
{
this._language = this._diQueries.LanguageCode.Substring(1);
this._multiLanguageHandlingRequired = false;
}
}
}
internal CodelistUtility(CodelistTypes codelistType, bool completeOrSummaryFlag, string agencyId, string language, Header header, string outputFolder, DIConnection DIConnection, DIQueries DIQueries)
: base(agencyId, language, header, outputFolder)
{
this._codelistType = codelistType;
this._completeOrSummaryFlag = completeOrSummaryFlag;
this._diConnection = DIConnection;
this._diQueries = DIQueries;
if (string.IsNullOrEmpty(language))
{
this.Language = this._diQueries.LanguageCode.Substring(1);
this._multiLanguageHandlingRequired = true;
}
else
{
if (this._diConnection.IsValidDILanguage(this._diQueries.DataPrefix, language))
{
this.Language = language;
this._multiLanguageHandlingRequired = false;
}
else
{
this.Language = this._diQueries.LanguageCode.Substring(1);
this._multiLanguageHandlingRequired = false;
}
}
}
internal ImportQueries(DIQueries DBQueries)
{
this.DBQueries = DBQueries;
// Set IUpdateQuery object, (By Default: Traditional)
this.UpdateQueries = new TraditionalUpdateQuery(this.DBQueries);
}
public static List<ArtefactInfo> Generate_Categorization(SDMXSchemaType schemaType, string agencyId, string language, Header header, string outputFolder, DIConnection DIConnection, DIQueries DIQueries,string IcType)
{
List<ArtefactInfo> RetVal;
CategorizationUtility CategorizationUtility;
RetVal = null;
CategorizationUtility = null;
try
{
CategorizationUtility = new CategorizationUtility(agencyId, language, header, outputFolder, DIConnection, DIQueries);
// RetVal = CategorizationUtility.Generate_Artefact();
RetVal = CategorizationUtility.Generate_CompleteArtefact(IcType);
CategorizationUtility.Save_Artefacts(RetVal);
}
catch (Exception ex)
{
RetVal = null;
throw ex;
}
finally
{
}
return RetVal;
}
public string GetDataQueryResponseJSON(string dbNId, string language, string key)
{
string ResponseString;
XmlDocument QueryDocument;
ResponseString = string.Empty;
QueryDocument = null;
try
{
this.DIConnection = Global.GetDbConnection(Convert.ToInt32(dbNId));
this.DIQueries = new DIQueries(this.DIConnection.DIDataSetDefault(), this.DIConnection.DILanguageCodeDefault(this.DIConnection.DIDataSetDefault()));
QueryDocument = this.Get_Query(key, Constants.WSQueryStrings.ResponseFormatTypes.JSON, Constants.WSQueryStrings.SDMXFormatTypes.StructureSpecificTS, language);
ResponseString = DATAUtility.Get_Data(QueryDocument, DataTypes.JSON, DIConnection, DIQueries);
}
catch (Exception ex)
{
Global.CreateExceptionString(ex, null);
throw ex;
}
finally
{
}
return ResponseString;
}
internal CategorizationUtility(string agencyId, string language, Header header, string outputFolder, DIConnection DIConnection, DIQueries DIQueries)
: base(agencyId, language, header, outputFolder)
{
this._diConnection = DIConnection;
this._diQueries = DIQueries;
if (string.IsNullOrEmpty(language))
{
this.Language = this._diQueries.LanguageCode.Substring(1);
this._multiLanguageHandlingRequired = true;
}
else
{
if (this._diConnection.IsValidDILanguage(this._diQueries.DataPrefix, language))
{
this.Language = language;
this._multiLanguageHandlingRequired = false;
}
else
{
this.Language = this._diQueries.LanguageCode.Substring(1);
this._multiLanguageHandlingRequired = false;
}
}
}
internal ExportOnlineDatabase(UserSelection userSelection, DIConnection sourceDBConnection, DIQueries sourceDBQueries)
{
if (sourceDBConnection != null && userSelection != null)
{
this.UserSelection = userSelection;
//-- Assign NIDs in userSelection.
if (userSelection.ShowIUS)
{
this.IUSNIDs = userSelection.IndicatorNIds;
}
else
{
this.IndicatorNIDs = userSelection.IndicatorNIds;
}
this.AreaNIDs = userSelection.AreaNIds;
this.TimePeriodNIDs = userSelection.TimePeriodNIds;
this.SourceNIDs = userSelection.SourceNIds;
this.ApplyMRD = userSelection.DataViewFilters.MostRecentData;
this.SourceDBConnection = sourceDBConnection;
this.SourceDBQueries = sourceDBQueries; //new DIQueries(this.SourceDBConnection.DIDataSetDefault(), this.SourceDBConnection.DILanguageCodeDefault(this.SourceDBConnection.DIDataSetDefault()));
}
}
//-- Check orgTextual_Data_value exists or not. If column exists then move textual & numeric values into their respective column.
public static void SeparateTextualandNemericData(DIConnection dbConnection, DIQueries dbQueries)
{
string SqlQuery = string.Empty;
try
{
if (ISColumnExists(dbConnection, Constants.Data.Orginal_Data_valueColumn, dbQueries.TablesName.Data))
{
//--Update Textual value true if data value is not Numeric
SqlQuery = "UPDATE " + dbQueries.TablesName.Data + " SET " + Data.IsTextualData + " = 1 WHERE NOT ISNUMERIC(" + Data.DataValue + ")";
dbConnection.ExecuteNonQuery(SqlQuery);
//-- move orgData_Value values into Data_Value
SqlQuery = "UPDATE " + dbQueries.TablesName.Data + " Set " + Constants.Data.Orginal_Data_valueColumn + "=" + Data.DataValue + " WHERE " + Data.IsTextualData + " = 0";
dbConnection.ExecuteNonQuery(SqlQuery);
//--move orgTextual_Data_value into Textual_Data_value if ISTextual is true
SqlQuery = "UPDATE " + dbQueries.TablesName.Data + " Set " + Constants.Data.Orginal_Textual_Data_valueColumn + " = " + Data.DataValue + " WHERE " + Data.IsTextualData + "<>0";
dbConnection.ExecuteNonQuery(SqlQuery);
//2. Remove Data_Value column and rename orgTextual_Data_Value and orgData_Value to Textual_Data_Value and Data_value"
DIDataValueHelper.RemoveDataValueAndRenameOrgColumns(dbConnection, dbQueries);
}
}
catch (Exception ex)
{
throw ex;
}
}
public RegistryService()
{
int DBNIdOut;
if (this.Context.Request.QueryString["p"] != null && !string.IsNullOrEmpty(this.Context.Request.QueryString["p"]) &&
int.TryParse(this.Context.Request.QueryString["p"], out DBNIdOut))
{
DBNId = DBNIdOut;
}
else
{
Global.GetAppSetting();
DBNId = Convert.ToInt32(Global.GetDefaultDbNId());
}
if (Global.IsDSDUploadedFromAdmin(DBNId))
{
this.DIConnection = Global.GetDbConnection(this.Get_AssociatedDB_NId(DBNId.ToString()));
}
else
{
this.DIConnection = Global.GetDbConnection(DBNId);
}
this.DIQueries = new DIQueries(this.DIConnection.DIDataSetDefault(), this.DIConnection.DILanguageCodeDefault(this.DIConnection.DIDataSetDefault()));
}
internal BaseQueryUtility(Dictionary<string, string> dictUserSelections, DataReturnDetailTypes dataReturnDetailType, string agencyId, DIConnection DIConnection, DIQueries DIQueries)
{
this._dictUserSelections = dictUserSelections;
this._dataReturnDetailType = dataReturnDetailType;
this._agencyId = agencyId;
this._diConnection = DIConnection;
this._diQueries = DIQueries;
}
private void ConnectToDatabaseNCreateConverter(string fileNameWPath)
{
DIConnection DBConnection = new DIConnection(DIServerType.MsAccess, string.Empty, string.Empty, fileNameWPath, string.Empty, string.Empty);
DIQueries DBQueries = new DIQueries(DBConnection.DIDataSetDefault(), DBConnection.DILanguageCodeDefault(DBConnection.DIDataSetDefault()));
this.CreateDBConverter(DBConnection, DBQueries);
}
/// <summary>
/// Constructor
/// </summary>
/// <param name="fileNameWPath"></param>
public DI6ToDI5Converter(string fileNameWPath)
{
this.DBConnection = new DIConnection(DIServerType.MsAccess, string.Empty, string.Empty, fileNameWPath, string.Empty, string.Empty);
this.DBQueries = new DIQueries(this.DBConnection.DIDataSetDefault(), this.DBConnection.DILanguageCodeDefault("UT_"));
this.FilenameWPath = fileNameWPath;
}
/// <summary>
/// Returns the instance of BaseListViewSource. Based on Factory method.
/// </summary>
/// <param name="listType"></param>
/// <param name="showCheckBoxColumn"></param>
/// <param name="dbConnection"></param>
/// <param name="dbQueries"></param>
/// <returns></returns>
public static BaseListViewSource CreateListViewSource(ListViewType listType, DIConnection dbConnection, DIQueries dbQueries)
{
BaseListViewSource RetVal = null;
UserPreference DIUserPreferencce = new UserPreference();
RetVal = ListViewSourceFactory.CreateListViewSource(listType, dbConnection, dbQueries, DIUserPreferencce);
return RetVal;
}
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 IndicatorClassificationSelections(DIConnection dIConnection,DIQueries dIQueries)
{
// -- DAL connection object
this.DbConnection = dIConnection;
// -- DAL query object
this.DbQueries = dIQueries;
// -- Intialize the string builder object
this._XMLString = new StringBuilder();
}
/// <summary>
/// Returns the instance of BaseListViewSource. Based on Factory method.
/// </summary>
/// <param name="listType"></param>
/// <param name="dbConnection"></param>
/// <param name="dbQueries"></param>
/// <param name="userprefenece"></param>
/// <returns></returns>
public static BaseListViewSource CreateListViewSource(ListViewType listType, DIConnection dbConnection, DIQueries dbQueries, UserPreference userprefenece)
{
BaseListViewSource RetVal = null;
switch (listType)
{
case ListViewType.Indicator:
RetVal = new IndicatorListViewSource();
RetVal.RaiseInitializedIndicatorListViewEvent();
break;
case ListViewType.Timeperiod:
RetVal = new TimeperiodListViewSource();
RetVal.RaiseInitializedTimperiodListViewEvent();
break;
case ListViewType.Area:
RetVal = new AreaListViewSource();
RetVal.RaiseInitializedAreaListViewEvent();
break;
case ListViewType.Source:
RetVal = new DISourceListViewSource();
RetVal.RaiseInitializedDISourceListViewEvent();
break;
case ListViewType.Unit:
RetVal = new UnitListViewSource();
RetVal.RaiseInitializedUnitListViewEvent();
break;
case ListViewType.SubgroupVal:
RetVal = new SubgroupValListViewSource();
RetVal.RaiseInitializedUnitListViewEvent();
break;
case ListViewType.IC:
RetVal = new ICListViewSource();
break;
default:
break;
}
if (RetVal != null)
{
//set variables
RetVal.DBConnection = dbConnection;
RetVal.DBQueries = dbQueries;
RetVal.DIUserPreference = userprefenece;
//set columns
RetVal.SetColumnInfo();
}
return RetVal;
}
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 override void Import(string selectedNids)
{
DataTable Table = null;
int ProgressCounter = 0;
UnitBuilder TrgUnitBuilderObj = null;
UnitBuilder SourceUnitBuilderObj = null;
UnitInfo SrcUnitInfoObj = null;
DIConnection SourceDBConnection = null;
DIQueries SourceDBQueries = null;
//-- Step 1: Get TempTable with Sorted SourceFileName
Table = this._TargetDBConnection.ExecuteDataTable(this.ImportQueries.GetImportUnits(selectedNids));
//-- Step 2:Initialise Indicator Builder with Target DBConnection
TrgUnitBuilderObj = new UnitBuilder(this.TargetDBConnection, this.TargetDBQueries);
// Initialize progress bar
this.RaiseProgressBarInitialize(selectedNids.Split(',').GetUpperBound(0) + 1);
//-- 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);
SourceUnitBuilderObj = new UnitBuilder(SourceDBConnection, SourceDBQueries);
SrcUnitInfoObj = SourceUnitBuilderObj.GetUnitInfo(FilterFieldType.NId, Convert.ToString(Row[MergetTemplateConstants.Columns.COLUMN_SRCNID]));
// Import Unit from Source
TrgUnitBuilderObj.ImportUnit(SrcUnitInfoObj, 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 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();
}
private bool UpdateSortOrderIntoIC(int icParentNId, ICType icType)
{
bool RetVal = true;
DataView Table = null;
DIQueries TempQueries;
int ParentNId = 0;
int ICOrder = 1;
string DataPrefix = string.Empty;
string LanguageCode = string.Empty;
try
{
// step1: create table for all dataset
foreach (DataRow DataPrefixRow in this._DBConnection.DIDataSets().Rows)
{
DataPrefix = DataPrefixRow[DBAvailableDatabases.AvlDBPrefix].ToString() + "_";
// get language for all available langauges
foreach (DataRow LanguageRow in this._DBConnection.DILanguages(DataPrefix).Rows)
{
LanguageCode = "_" + LanguageRow[Language.LanguageCode].ToString();
// check table already exists or not
TempQueries = new DIQueries(DataPrefix, LanguageCode);
Table = this.DBConnection.ExecuteDataTable(TempQueries.IndicatorClassification.GetIC(FilterFieldType.ParentNId, icParentNId.ToString(), icType, FieldSelection.Light)).DefaultView;
if (Table.Count > 0)
{
Table.Sort = IndicatorClassifications.ICName + " Asc";
// Get each Child ICNId
foreach (DataRowView ICRow in Table)
{
ParentNId = Convert.ToInt32(ICRow[IndicatorClassifications.ICNId]);
RetVal = this.UpdateSortOrderIntoIC(ParentNId, icType);
// Set Sort Order
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.IndicatorClassification.Update.UpdateICOrder(TempQueries.DataPrefix, TempQueries.LanguageCode, ICOrder++, ParentNId));
// Update ICIUS_IUSNId Order
this.UpdateSortOrderIntoICIUS(ParentNId, icType);
}
}
else
{
RetVal = false;
}
}
}
}
catch (Exception)
{
RetVal = false;
}
return(RetVal);
}
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);
}
private void CreateDBConverter(DIConnection dbConnection, DIQueries dbQueries)
{
#region "-- change here --"
this.DatabaseConverter = new DI7_0_0_1DBConverter(dbConnection, dbQueries);
#endregion
// add event handlers
this.DatabaseConverter.ProcessInfo += new DevInfo.Lib.DI_LibBAL.DA.DML.ProcessInfoDelegate(DatabaseConverter_ProcessInfo);
this.DatabaseConverter.ProcessStarted += new DevInfo.Lib.DI_LibBAL.DA.DML.ProcessInfoDelegate(DatabaseConverter_ProcessStarted);
}
//Returns the requires area levels
public static DataTable GetAreaLevels(DIConnection dBConnection, DIQueries dBQueries)
{
DataTable dtAreaLevel = null;
try
{
dtAreaLevel = dBConnection.ExecuteDataTable(dBQueries.Area.GetAreaLevel(FilterFieldType.None, string.Empty));
}
catch (Exception)
{
}
return dtAreaLevel;
}
private void RemoveExtraColumnsNTable()
{
DIQueries queries;
foreach (string LanguageCode in AllLanguageCodes)
{
queries = new DIQueries(this.DBQueries.DataPrefix, LanguageCode);
//Remove IC Columns
this.DBConnection.DropIndividualColumnOfTable(queries.TablesName.IndicatorClassifications, IndicatorClassifications.ISBN);
this.DBConnection.DropIndividualColumnOfTable(queries.TablesName.IndicatorClassifications, IndicatorClassifications.Nature);
//Remove area extra columns
this.DBConnection.DropIndividualColumnOfTable(queries.TablesName.Area, Area.AreaShortName);
//Remove Metadata Category extra columns
this.DBConnection.DropIndividualColumnOfTable(queries.TablesName.MetadataCategory, Metadata_Category.ParentCategoryNId);
this.DBConnection.DropIndividualColumnOfTable(queries.TablesName.MetadataCategory, Metadata_Category.CategoryGId);
this.DBConnection.DropIndividualColumnOfTable(queries.TablesName.MetadataCategory, Metadata_Category.CategoryDescription);
this.DBConnection.DropIndividualColumnOfTable(queries.TablesName.MetadataCategory, Metadata_Category.IsPresentational);
this.DBConnection.DropIndividualColumnOfTable(queries.TablesName.MetadataCategory, Metadata_Category.IsMandatory);
//Drop Metadata Report Table
this._DBConnection.DropTable(queries.TablesName.MetadataReport);
//Drop sdmx user table
this._DBConnection.DropTable(this.DBQueries.TablesName.SDMXUser);
}
//Remove Timeperiod Columns
this.DBConnection.DropIndividualColumnOfTable(this.DBQueries.TablesName.TimePeriod, Timeperiods.StartDate);
this.DBConnection.DropIndividualColumnOfTable(this.DBQueries.TablesName.TimePeriod, Timeperiods.EndDate);
this.DBConnection.DropIndividualColumnOfTable(this.DBQueries.TablesName.TimePeriod, Timeperiods.Periodicity);
//Remove IUS columns
this.DBConnection.DropIndividualColumnOfTable(this.DBQueries.TablesName.IndicatorUnitSubgroup, Indicator_Unit_Subgroup.IsDefaultSubgroup);
this.DBConnection.DropIndividualColumnOfTable(this.DBQueries.TablesName.IndicatorUnitSubgroup, Indicator_Unit_Subgroup.AvlMinDataValue);
this.DBConnection.DropIndividualColumnOfTable(this.DBQueries.TablesName.IndicatorUnitSubgroup, Indicator_Unit_Subgroup.AvlMaxDataValue);
this.DBConnection.DropIndividualColumnOfTable(this.DBQueries.TablesName.IndicatorUnitSubgroup, Indicator_Unit_Subgroup.AvlMinTimePeriod);
this.DBConnection.DropIndividualColumnOfTable(this.DBQueries.TablesName.IndicatorUnitSubgroup, Indicator_Unit_Subgroup.AvlMaxTimePeriod);
//Remove Data columns
this.DBConnection.DropIndividualColumnOfTable(this.DBQueries.TablesName.Data, DevInfo.Lib.DI_LibBAL.DA.DML.Constants.Data.Orginal_Textual_Data_valueColumn);
this.DBConnection.DropIndividualColumnOfTable(this.DBQueries.TablesName.Data, DevInfo.Lib.DI_LibBAL.DA.DML.Constants.Data.Orginal_Data_valueColumn);
this.DBConnection.DropIndividualColumnOfTable(this.DBQueries.TablesName.Data, Data.IsTextualData);
this.DBConnection.DropIndividualColumnOfTable(this.DBQueries.TablesName.Data, Data.IsMRD);
this.DBConnection.DropIndividualColumnOfTable(this.DBQueries.TablesName.Data, Data.IsPlannedValue);
this.DBConnection.DropIndividualColumnOfTable(this.DBQueries.TablesName.Data, Data.IUNId);
this.DBConnection.DropIndividualColumnOfTable(this.DBQueries.TablesName.Data, Data.ConfidenceIntervalLower);
this.DBConnection.DropIndividualColumnOfTable(this.DBQueries.TablesName.Data, Data.ConfidenceIntervalUpper);
this.DBConnection.DropIndividualColumnOfTable(this.DBQueries.TablesName.Data, Data.MultipleSource);
}
public SearchIndicators(DIConnection dbConnection,DIQueries dbQueries)
{
this._GIdColumnName = Indicator.IndicatorNId;
this._NameColumnName = Indicator.IndicatorName;
this._GlobalValueColumnName = Indicator.IndicatorGlobal;
this._NameColumnHeader = DILanguage.GetLanguageString("INDICATOR");
this.DBConnection = dbConnection;
this.DBQueries = dbQueries;
FreeTextObj = new FreeText(dbConnection, dbQueries);
}
public SearchIndicators(DIConnection dbConnection, DIQueries dbQueries)
{
this._GIdColumnName = Indicator.IndicatorNId;
this._NameColumnName = Indicator.IndicatorName;
this._GlobalValueColumnName = Indicator.IndicatorGlobal;
this._NameColumnHeader = DILanguage.GetLanguageString("INDICATOR");
this.DBConnection = dbConnection;
this.DBQueries = dbQueries;
FreeTextObj = new FreeText(dbConnection, dbQueries);
}
/// <summary>
/// Area Autoselection: From the Available list by a list of available Area NIDs
/// </summary>
/// <param name="userPreference"></param>
/// <param name="dIConnection"></param>
/// <param name="dIQueries"></param>
/// <param name="sAvlAreaNIDs"></param>
public AutoSelectArea(UserPreference.UserPreference userPreference, DI_LibDAL.Connection.DIConnection dIConnection, DI_LibDAL.Queries.DIQueries dIQueries, string avlAreaNIDs)
{
// -- Connection details preserved
this.mdIConnection = dIConnection;
this.mdIQueries = dIQueries;
this.mUserPreference = userPreference;
// Autoselect Parameters
this.mparentNId = -1;
this.mcurrentLevel = 0;
this.mrequiredLevel = 0;
this.mAreaNIDs = avlAreaNIDs;
}
/// <summary>
/// Area Autoselection: From the TreeView by Selected Area, Target Area Level
/// </summary>
/// <param name="userPreference"></param>
/// <param name="dIConnection"></param>
/// <param name="dIQueries"></param>
/// <param name="parentNId"></param>
/// <param name="currentLevel"></param>
/// <param name="requiredLevel"></param>
public AutoSelectArea(UserPreference.UserPreference userPreference, DI_LibDAL.Connection.DIConnection dIConnection, DI_LibDAL.Queries.DIQueries dIQueries, int parentNId, int currentLevel, int requiredLevel)
{
// -- Connection details preserved
this.mdIConnection = dIConnection;
this.mdIQueries = dIQueries;
this.mUserPreference = userPreference;
// Autoselect Parameters
this.mparentNId = parentNId;
this.mcurrentLevel = currentLevel;
this.mrequiredLevel = requiredLevel;
this.mAreaNIDs = string.Empty;
}
//Returns the all time periods
public static DataTable GetAllTimePeriods(DIConnection dBConnection, DIQueries dBQueries)
{
DataTable dtTimePeriod=null;
try
{
dtTimePeriod = dBConnection.ExecuteDataTable(dBQueries.Timeperiod.GetTimePeriod(FilterFieldType.None, string.Empty, Timeperiods.TimePeriod + " DESC"));
}
catch (Exception)
{
}
return dtTimePeriod;
}
/// <summary>
/// Get the base layer (most recent) associated with an area
/// </summary>
/// <param name="AreaNId"></param>
/// <param name="DIConnection"></param>
/// <param name="DIQueries"></param>
/// <returns>Layer NId</returns>
private static int GetLayerNId(string AreaNId, DIConnection DIConnection, DIQueries DIQueries)
{
int RetVal = -1;
string sSql = DIQueries.Area.GetAreaMapByAreaNIds(AreaNId, false); //Consider base layers only
DataTable dtAreaMap = DIConnection.ExecuteDataTable(sSql);
if (dtAreaMap != null && dtAreaMap.Rows.Count > 0)
{
//OPT If multiple base layer are associated with an area select latest map available
RetVal = (int)dtAreaMap.Rows[0][Area_Map.LayerNId];
}
dtAreaMap.Dispose();
return(RetVal);
}
public string GetDataQueryResponseSDMXML(string dbNId, string language, string flowRef, string key)
{
string AgencyId, ResponseString;
XmlDocument QueryDocument;
AgencyId = string.Empty;
ResponseString = string.Empty;
QueryDocument = null;
try
{
this.DIConnection = Global.GetDbConnection(Convert.ToInt32(dbNId));
this.DIQueries = new DIQueries(this.DIConnection.DIDataSetDefault(), this.DIConnection.DILanguageCodeDefault(this.DIConnection.DIDataSetDefault()));
AgencyId = DevInfo.Lib.DI_LibSDMX.Constants.MaintenanceAgencyScheme.Prefix + dbNId;
switch (WebOperationContext.Current.IncomingRequest.ContentType)
{
case Constants.WSQueryStrings.SDMXContentTypes.Generic:
QueryDocument = this.Get_Query(key, Constants.WSQueryStrings.ResponseFormatTypes.SDMX, Constants.WSQueryStrings.SDMXFormatTypes.Generic, language);
ResponseString = SDMXUtility.Get_Data(SDMXSchemaType.Two_One, QueryDocument, DataFormats.Generic, DIConnection, DIQueries).OuterXml;
break;
case Constants.WSQueryStrings.SDMXContentTypes.GenericTS:
QueryDocument = this.Get_Query(key, Constants.WSQueryStrings.ResponseFormatTypes.SDMX, Constants.WSQueryStrings.SDMXFormatTypes.GenericTS, language);
ResponseString = SDMXUtility.Get_Data(SDMXSchemaType.Two_One, QueryDocument, DataFormats.GenericTS, DIConnection, DIQueries).OuterXml;
break;
case Constants.WSQueryStrings.SDMXContentTypes.StructureSpecific:
QueryDocument = this.Get_Query(key, Constants.WSQueryStrings.ResponseFormatTypes.SDMX, Constants.WSQueryStrings.SDMXFormatTypes.StructureSpecific, language);
ResponseString = SDMXUtility.Get_Data(SDMXSchemaType.Two_One, QueryDocument, DataFormats.StructureSpecific, DIConnection, DIQueries).OuterXml;
break;
case Constants.WSQueryStrings.SDMXContentTypes.StructureSpecificTS:
QueryDocument = this.Get_Query(key, Constants.WSQueryStrings.ResponseFormatTypes.SDMX, Constants.WSQueryStrings.SDMXFormatTypes.StructureSpecificTS, language);
ResponseString = SDMXUtility.Get_Data(SDMXSchemaType.Two_One, QueryDocument, DataFormats.StructureSpecificTS, DIConnection, DIQueries).OuterXml;
break;
default:
QueryDocument = this.Get_Query(key, Constants.WSQueryStrings.ResponseFormatTypes.SDMX, Constants.WSQueryStrings.SDMXFormatTypes.Generic, language);
ResponseString = SDMXUtility.Get_Data(SDMXSchemaType.Two_One, QueryDocument, DataFormats.Generic, DIConnection, DIQueries).OuterXml;
break;
}
}
catch (Exception ex)
{
Global.CreateExceptionString(ex, null);
throw ex;
}
finally
{
}
return ResponseString;
}
private void UpdateSortOrderIntoSubgroup()
{
DataView Table = null;
DIQueries TempQueries;
int SubgroupType = 0;
int Order = 1;
string DataPrefix = string.Empty;
string LanguageCode = string.Empty;
try
{
// step1: create table for all dataset
foreach (DataRow DataPrefixRow in this._DBConnection.DIDataSets().Rows)
{
DataPrefix = DataPrefixRow[DBAvailableDatabases.AvlDBPrefix].ToString() + "_";
// get language for all available langauges
foreach (DataRow LanguageRow in this._DBConnection.DILanguages(DataPrefix).Rows)
{
LanguageCode = "_" + LanguageRow[Language.LanguageCode].ToString();
// check table already exists or not
TempQueries = new DIQueries(DataPrefix, LanguageCode);
// Get Subgroup Dimension Values
Table = this._DBConnection.ExecuteDataTable(TempQueries.Subgroup.GetSubgroup(FilterFieldType.None, string.Empty)).DefaultView;
// Sort By SubgroupType and Subgroup
Table.Sort = Subgroup.SubgroupType + " Asc," + Subgroup.SubgroupName + " Asc";
// Update SubgroupVal Order Column
foreach (DataRowView Row in Table)
{
// Reset Subgroup Order id SubgroupType changes
if (SubgroupType != Convert.ToInt32(Row[Subgroup.SubgroupType]))
{
Order = 1;
SubgroupType = Convert.ToInt32(Row[Subgroup.SubgroupType]);
}
this._DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.Subgroup.Update.UpdateSubgroupOrderByNId(TempQueries.DataPrefix, TempQueries.LanguageCode, Convert.ToInt32(Row[Subgroup.SubgroupNId]), Order++));
}
}
}
}
catch (Exception)
{
}
}
/// <summary>
/// Get Image for the selected area
/// </summary>
/// <param name="AreaNId"> Area Nid of selected area</param>
/// <param name="OutPath"> Location where metadata related files are created</param>
/// <param name="DI_QueryBase"> This contain Conncetion Details</param>
/// <param name="PicHeight"> Height of the map </param>
/// <param name="PicWidth">width of the map</param>
/// <returns></returns>
public static Image GetAreaMapImage(DIConnection DIConnection, DIQueries DIQueries, int AreaNId, string TempFolder, int PicHeight, int PicWidth)
{
Image RetVal = null;
int LayerNId = -1;
try
{
//-- If any specific layer has not been defined then get the first base layer associated with the area
if (_LayerNId == -1)
{
LayerNId = GetLayerNId(AreaNId.ToString(), DIConnection, DIQueries);
}
else
{
LayerNId = _LayerNId;
}
if (LayerNId != -1)
{
//Get Layer Name
string LayerName = GetLayerName(LayerNId.ToString(), DIConnection, DIQueries);
//Extarct shape file for associated layer
Map.ExtractShapeFileByLayerId(LayerNId.ToString(), TempFolder, DIConnection, DIQueries);
Map map = new Map();
map.CanvasColor = Color.White;
// Add layer to map layer collection
map.Layers.Clear();
map.Width = PicWidth;
map.Height = PicHeight;
map.Layers.AddShapeFile(TempFolder, LayerName);
map.Layers[0].FillColor = System.Drawing.Color.White;
map.SetFullExtent();
//Extract map image
RetVal = Image.FromStream(map.GetMapStream());
map.Dispose();
map = null;
}
}
catch (Exception ex)
{
Console.Write(ex.Message);
}
return(RetVal);
}
/// <summary>
/// Creates Icon tables for all available language
/// </summary>
/// <param name="dbConnection"></param>
/// <param name="dbQueries"></param>
/// <param name="forOnlineDB"></param>
public static void CreateIconsTblsForAllLngs(DIConnection dbConnection, DIQueries dbQueries, bool forOnlineDB)
{
if (DIIcons.IsIconsTblExists(dbQueries.TablesName.Icons, dbConnection) == false)
{
try
{
//-- create Icon table
dbConnection.ExecuteNonQuery(DIIcons.CreateIconsTbl(dbQueries.TablesName.Icons, forOnlineDB));
}
catch (Exception ex)
{
throw new ApplicationException(ex.Message);
}
}
}
/// <summary>
/// Creates Icon tables for all available language
/// </summary>
/// <param name="dbConnection"></param>
/// <param name="dbQueries"></param>
/// <param name="forOnlineDB"></param>
public static void CreateIconsTblsForAllLngs(DIConnection dbConnection, DIQueries dbQueries, bool forOnlineDB)
{
if (DIIcons.IsIconsTblExists(dbQueries.TablesName.Icons, dbConnection) == false)
{
try
{
//-- create Icon table
dbConnection.ExecuteNonQuery(DIIcons.CreateIconsTbl(dbQueries.TablesName.Icons, forOnlineDB));
}
catch (Exception ex)
{
throw new ApplicationException(ex.Message);
}
}
}
/// <summary>
/// Retruns nid only if name exists in the database
/// </summary>
/// <returns></returns>
private int GetNidByName(string name, DIQueries dbQueries, string parentNId)
{
int RetVal = 0;
string SqlQuery = string.Empty;
try
{
SqlQuery = dbQueries.Metadata_Category.GetMetadataCategories(FilterFieldType.Name, "'" + DIQueries.RemoveQuotesForSqlQuery(name) + "'", parentNId);
RetVal = Convert.ToInt32(this.DBConnection.ExecuteScalarSqlQuery(SqlQuery));
}
catch (Exception)
{
RetVal = 0;
}
return(RetVal);
}
/// <summary>
/// Rename Data_value and TextualValue column into OrgData_value and OrgTextualValue
/// and merge both column value into new column Data_Value
/// </summary>
/// <param name="dbConnection"></param>
/// <param name="dbQueries"></param>
public static void MergeTextualandNumericDataValueColumn(string databaseFileNameWPath)
{
try
{
using (DIConnection DBConnection = new DIConnection(DIServerType.MsAccess, string.Empty, string.Empty, databaseFileNameWPath, string.Empty, string.Empty))
{
DIQueries DBQueries = new DIQueries(DBConnection.DIDataSetDefault(), DBConnection.DILanguageCodeDefault(DBConnection.DIDataSetDefault()));
MergeTextualandNumericDataValueColumn(DBConnection, DBQueries);
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// Rename Data_value and TextualValue column into OrgData_value and OrgTextualValue
/// and merge both column value into new column Data_Value
/// </summary>
/// <param name="dbConnection"></param>
/// <param name="dbQueries"></param>
public static void MergeTextualandNumericDataValueColumn(string databaseFileNameWPath)
{
try
{
using (DIConnection DBConnection = new DIConnection(DIServerType.MsAccess, string.Empty, string.Empty, databaseFileNameWPath, string.Empty, string.Empty))
{
DIQueries DBQueries = new DIQueries(DBConnection.DIDataSetDefault(), DBConnection.DILanguageCodeDefault(DBConnection.DIDataSetDefault()));
MergeTextualandNumericDataValueColumn(DBConnection, DBQueries);
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// Generate Sheet for Comparison Report
/// </summary>
/// <param name="excelFile">Excel File</param>
/// <param name="dbConnection">Database Connection </param>
/// <param name="dbQueries">DIQueries</param>
/// <param name="sheetType">Sheet Type for Comparison Reports</param>
internal void GenerateSheet(ref DIExcel excelFile, DIConnection dbConnection, DIQueries dbQueries, SheetType sheetType)
{
int SheetNo = 0;
int CurrentRowIndex = 0;
DataTable MissingRecords = null;
DataTable AdditionalRecords = null;
SheetSource SheetGenerator = null;
// -- Get Sheet Class Instance
SheetGenerator = SheetSourceFactory.CreateInstance(sheetType, dbConnection, dbQueries);
this.NameColIndex = SheetGenerator.NameColumnIndex;
this.LastColIndex = SheetGenerator.LastColumnIndex;
this.LanguageName = SheetGenerator.GetLanguageName();
// -- Get Missing Records
MissingRecords = SheetGenerator.GetMissingRecordsTable();
//-- Get Additional Records
AdditionalRecords = SheetGenerator.GetAdditionalRecordsTable();
// if records is morethan 50000 then create multiple sheets
if (MissingRecords.Rows.Count > Constants.SheetsLayout.MAXEXCELROWS || (MissingRecords.Rows.Count + AdditionalRecords.Rows.Count) > Constants.SheetsLayout.MAXEXCELROWS)
{
// -- Create Multiple Sheet
this.GenerateSheets(ref excelFile, SheetGenerator.SheetName, MissingRecords, AdditionalRecords);
}
else
{
// -- Create Worksheet
SheetNo = this.CreateSheet(ref excelFile, SheetGenerator.SheetName, 0);
// -- Set Initial Sheet Value
this.SetSheetHeading(ref excelFile, SheetNo, SheetGenerator.SheetName);
this.SetSheetLanguageValue(ref excelFile, SheetNo);
this.SetMissingText(ref excelFile, SheetNo);
// -- Load Missing Records Into Sheet
excelFile.LoadDataTableIntoSheet(Constants.Sheet.Indicator.DetailsRowIndex, Constants.HeaderColIndex, MissingRecords, SheetNo, false);
CurrentRowIndex = Constants.Sheet.Indicator.DetailsRowIndex + Constants.RecordsGapCount + MissingRecords.Rows.Count;
excelFile.SetCellValue(SheetNo, CurrentRowIndex, Constants.HeaderColIndex, DILanguage.GetLanguageString(Constants.SheetHeader.ADDITIONAL) + " : " + DBNameForAdditionalRecords);
CurrentRowIndex += 1;
// -- Load Additional Records Into Sheet
excelFile.LoadDataTableIntoSheet(CurrentRowIndex, Constants.HeaderColIndex, AdditionalRecords, SheetNo, false);
// -- Apply Font Settings
this.ApplyFontSetting(ref excelFile, SheetNo, MissingRecords.Rows.Count);
}
}
/// <summary>
/// Import ISDefaultSubgroup from database
/// </summary>
/// <param name="sourceDatabase"></param>
/// <param name="sourceDBQueries"></param>
public void ImportDefaultSubgroups(DIConnection sourceDBConnection, DIQueries sourceDBQueries)
{
string IUSNids = string.Empty;
DataTable IUSTable = null;
DataTable IUTable = null;
IUSInfo IUSInfoObj = new IUSInfo();
//-- Get IUS from Source Database
IUSTable = sourceDBConnection.ExecuteDataTable(sourceDBQueries.IUS.GetIUS(FilterFieldType.None, string.Empty, FieldSelection.Light, true));
//-- Get Default IUS Values
DataRow[] IUSRows = IUSTable.Select(Indicator_Unit_Subgroup.IsDefaultSubgroup + "=1");
foreach (DataRow Row in IUSRows)
{
IUSInfoObj = new IUSInfo();
IUSInfoObj.IndicatorInfo = new IndicatorInfo();
IUSInfoObj.UnitInfo = new UnitInfo();
IUSInfoObj.SubgroupValInfo = new DI6SubgroupValInfo();
IUSInfoObj.IndicatorInfo.Name = Convert.ToString(Row[Indicator.IndicatorName]);
IUSInfoObj.IndicatorInfo.GID = Convert.ToString(Row[Indicator.IndicatorGId]);
IUSInfoObj.UnitInfo.Name = Convert.ToString(Row[Unit.UnitName]);
IUSInfoObj.UnitInfo.GID = Convert.ToString(Row[Unit.UnitGId]);
IUSInfoObj.SubgroupValInfo.Name = Convert.ToString(Row[SubgroupVals.SubgroupVal]);
IUSInfoObj.SubgroupValInfo.GID = Convert.ToString(Row[SubgroupVals.SubgroupValGId]);
int IUSNid = this.GetIUSNid(IUSInfoObj);
//-- Set Default Subgroup if IUS exists
if (IUSNid > 0)
{
IUTable = this.DBConnection.ExecuteDataTable(this.DBQueries.IUS.GetIUSByI_U_S(IUSInfoObj.IndicatorInfo.Nid.ToString(), IUSInfoObj.UnitInfo.Nid.ToString(), string.Empty));
IUSNids = DIConnection.GetDelimitedValuesFromDataTable(IUTable, Indicator_Unit_Subgroup.IUSNId);
//-- Make ISDefault false for other subgroupval for current I-U
this.UpdateIUSISDefaultSubgroup(IUSNids, false);
//-- Set DefaultSubgroupVal for IUSNid
this.UpdateIUSISDefaultSubgroup(IUSNid.ToString(), true);
}
}
}
/// <summary>
/// Get subgroup Type Nid by Name
/// </summary>
/// <returns> Nid</returns>
private int GetSubgroupTypeByName(string name)
{
int RetVal = 0;
string SqlQuery = string.Empty;
try
{
SqlQuery = this.DBQueries.SubgroupTypes.GetSubgroupTypes(FilterFieldType.Name, "'" +
DIQueries.RemoveQuotesForSqlQuery(name) + "'");
RetVal = Convert.ToInt32(this.DBConnection.ExecuteScalarSqlQuery(SqlQuery));
}
catch (Exception)
{
RetVal = 0;
}
return(RetVal);
}
public DatabaseIncrementBuilder(string sourceDBLocation, string TargetDBLocation)
{
string DataPrefix, LangCode = string.Empty;
SourceConnection = new DIConnection(DIServerType.MsAccess, string.Empty, string.Empty, sourceDBLocation, string.Empty, "unitednations2000");
TargetConnection = new DIConnection(DIServerType.MsAccess, string.Empty, string.Empty, TargetDBLocation, string.Empty, "unitednations2000");
DataPrefix = SourceConnection.DIDataSetDefault();
LangCode = SourceConnection.DILanguageCodeDefault(DataPrefix);
SourceQueries = new DIQueries(DataPrefix, LangCode);
DataPrefix = TargetConnection.DIDataSetDefault();
LangCode = TargetConnection.DILanguageCodeDefault(DataPrefix);
TargetQueries = new DIQueries(DataPrefix, LangCode);
this.SourceDbFile = sourceDBLocation;
this.TargetDbFile = TargetDBLocation;
}
/// <summary>
/// Imports metadata from the source database into current database
/// </summary>
/// <param name="srcConnection"></param>
/// <param name="srcQueries"></param>
/// <param name="srcElementNid"></param>
/// <param name="trgElementNid"></param>
/// <param name="categoryType"></param>
/// <param name="metadataType"></param>
/// <param name="iconType"></param>
public void ImportMetadata(DIConnection srcConnection, DIQueries srcQueries, int srcElementNid, int trgElementNid, MetadataElementType categoryType, MetaDataType metadataType, IconElementType iconType)
{
string SrcCategoryGID = string.Empty;
string MetadataText = string.Empty;
int TrgCategoryNid = 0;
DataTable TrgCategoryTable = null;
Dictionary <String, String> OldIconNIdnNewIconNId = new Dictionary <string, string>();
try
{
// Get target Category table
TrgCategoryTable = this.DBConnection.ExecuteDataTable(this.DBQueries.Metadata_Category.GetMetadataCategories(FilterFieldType.Type, DIQueries.MetadataElementTypeText[categoryType]));
// update metadata icon in DICon table
OldIconNIdnNewIconNId = DIIcons.ImportElement(srcElementNid, trgElementNid, iconType, srcQueries, srcConnection, this.DBQueries, this.DBConnection);
//get source metadata reports
foreach (DataRow SrcRow in srcConnection.ExecuteDataTable(srcQueries.MetadataReport.GetMetadataReportsByTargetNid(srcElementNid.ToString(), categoryType)).Rows)
{
SrcCategoryGID = Convert.ToString(SrcRow[Metadata_Category.CategoryGId]);
// check source category GID exists in current database
// Import metadta report only if category exists in current database
foreach (DataRow TrgCategoryRow in TrgCategoryTable.Select(Metadata_Category.CategoryGId + "='" + DIQueries.RemoveQuotesForSqlQuery(SrcCategoryGID) + "' "))
{
MetadataText = Convert.ToString(SrcRow[MetadataReport.Metadata]);
// Update IconNids in metadata if exists
foreach (string OldIconName in OldIconNIdnNewIconNId.Keys)
{
MetadataText = MetadataText.Replace(OldIconName, OldIconNIdnNewIconNId[OldIconName].ToString());
}
TrgCategoryNid = Convert.ToInt32(TrgCategoryRow[Metadata_Category.CategoryNId]);
this.InsertORUpdateMetadataInfo(metadataType, TrgCategoryNid.ToString(), trgElementNid, MetadataText);
}
}
}
catch (Exception ex)
{
ExceptionHandler.ExceptionFacade.ThrowException(ex);
}
}
/// <summary>
/// Import indicator, unit and subgroupval.
/// </summary>
/// <param name="iusInfo"></param>
/// <param name="sourceQurey"></param>
/// <param name="sourceDBConnection"></param>
/// <returns></returns>
public int ImportIUS(IUSInfo iusInfo, DIQueries sourceQurey, DIConnection sourceDBConnection)
{
int RetVal = 0;
int NewIndicatorNid = 0;
int NewUnitNid = 0;
int NewSubgroupValNid = 0;
try
{
//import indicator
NewIndicatorNid = this.DIIndicator.ImportIndicator(iusInfo.IndicatorInfo, iusInfo.IndicatorInfo.Nid, sourceQurey, sourceDBConnection);
//import unit
NewUnitNid = this.DIUnit.ImportUnit(iusInfo.UnitInfo, iusInfo.UnitInfo.Nid, sourceQurey, sourceDBConnection);
//import subgroupval
NewSubgroupValNid = this.DISubgroupVal.ImportSubgroupVal(iusInfo.SubgroupValInfo.Nid, sourceQurey, sourceDBConnection);
//check ius exists or not
if (NewIndicatorNid > 0 & NewUnitNid > 0 & NewSubgroupValNid > 0)
{
RetVal = this.GetIUSNid(NewIndicatorNid, NewUnitNid, NewSubgroupValNid);
//update nids
iusInfo.IndicatorInfo.Nid = NewIndicatorNid;
iusInfo.UnitInfo.Nid = NewUnitNid;
iusInfo.SubgroupValInfo.Nid = NewSubgroupValNid;
if (RetVal <= 0)
{
//insert ius combination
this.InsertIUS(iusInfo);
RetVal = this.GetIUSNid(NewIndicatorNid, NewUnitNid, NewSubgroupValNid);
}
}
}
catch (Exception ex)
{
RetVal = 0;
throw new ApplicationException(ex.ToString());
}
return(RetVal);
}
private void ConvertDI7MetadataIntoDI6Metadata()
{
MetadataCategoryBuilder metadataCategoryBuilder;
DIQueries queries;
foreach (string LanguageCode in AllLanguageCodes)
{
queries = new DIQueries(this.DBQueries.DataPrefix, LanguageCode);
metadataCategoryBuilder = new MetadataCategoryBuilder(this._DBConnection, queries);
//for Indicator, Area, Source
this.UpdateMetadataInfoByCategoryType(MetadataElementType.Indicator, metadataCategoryBuilder, queries);
this.UpdateMetadataInfoByCategoryType(MetadataElementType.Area, metadataCategoryBuilder, queries);
this.UpdateMetadataInfoByCategoryType(MetadataElementType.Source, metadataCategoryBuilder, queries);
}
}
private void CreateDBMetaTable(bool forOnlineDB)
{
string DataPrefix = string.Empty;
string LanguageCode = string.Empty;
DITables TableNames;
DBMetadataTableBuilder DBMetadataTblBuilder;
DIQueries TempQueries;
try
{
// create table for all dataset
foreach (DataRow DataPrefixRow in this._DBConnection.DIDataSets().Rows)
{
DataPrefix = DataPrefixRow[DBAvailableDatabases.AvlDBPrefix].ToString() + "_";
// create table for all available langauges
foreach (DataRow LanguageRow in this._DBConnection.DILanguages(DataPrefix).Rows)
{
LanguageCode = "_" + LanguageRow[Language.LanguageCode].ToString();
// check table already exists or not
TempQueries = new DIQueries(DataPrefix, LanguageCode);
DBMetadataTblBuilder = new DBMetadataTableBuilder(this._DBConnection, TempQueries);
if (DBMetadataTblBuilder.IsDBMetadataTableExists() == false)
{
TableNames = new DITables(DataPrefix, LanguageCode);
this._DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.DBMetadata.Insert.CreateTable(TableNames.DBMetadata,
forOnlineDB, this._DBConnection.ConnectionStringParameters.ServerType));
}
}
// insert blank records with counts
// reset DBMetadata builder with main DIQuerie's object
DBMetadataTblBuilder = new DBMetadataTableBuilder(this._DBConnection, this._DBQueries);
DBMetadataTblBuilder.InsertRecord(string.Empty, string.Empty, string.Empty, string.Empty,
string.Empty, string.Empty, string.Empty, string.Empty, string.Empty, string.Empty, string.Empty, string.Empty);
DBMetadataTblBuilder.GetNUpdateCounts();
}
}
catch (Exception ex)
{
throw new ApplicationException(ex.ToString());
}
}
/// <summary>
/// Gets the count from database/template and update into DBMetadata Table
/// </summary>
public void GetNUpdateCounts()
{
string AreaCount = string.Empty;
string IndicatorCount = string.Empty;
string IUSCount = string.Empty;
string TimeperiodCount = string.Empty;
string SourceCount = string.Empty;
string DataCount = string.Empty;
string SearchString = string.Empty;
DataTable SourceTable;
// Get NId from DBMetadata Table
// Get area count
AreaCount = this.DBConnection.ExecuteScalarSqlQuery(DIQueries.GetTableRecordsCount(this.DBQueries.TablesName.Area, string.Empty)).ToString();
// Get Indicator count
IndicatorCount = this.DBConnection.ExecuteScalarSqlQuery(DIQueries.GetTableRecordsCount(this.DBQueries.TablesName.Indicator, string.Empty)).ToString();
// Get IUS count
IUSCount = this.DBConnection.ExecuteScalarSqlQuery(DIQueries.GetTableRecordsCount(this.DBQueries.TablesName.IndicatorUnitSubgroup, string.Empty)).ToString();
// Get timeperiod count
TimeperiodCount = this.DBConnection.ExecuteScalarSqlQuery(DIQueries.GetTableRecordsCount(this.DBQueries.TablesName.TimePeriod, string.Empty)).ToString();
// Get source count
SearchString = IndicatorClassifications.ICParent_NId + ">0";
SourceTable = this.DBConnection.ExecuteDataTable(this.DBQueries.Source.GetSource(FilterFieldType.Search, SearchString, FieldSelection.NId, false));
if (SourceTable.Rows.Count > 0)
{
SourceCount = Convert.ToString(SourceTable.Select().Length);
}
else
{
SourceCount = "0";
}
// Get data count
DataCount = this.DBConnection.ExecuteScalarSqlQuery(DIQueries.GetTableRecordsCount(this.DBQueries.TablesName.Data, string.Empty)).ToString();
this.UpdateCounts(-1, AreaCount, IndicatorCount, IUSCount, TimeperiodCount, SourceCount, DataCount);
}
private void UpdateMetadataInfo(DIQueries queries, string targetNid, MetadataElementType categoryType, String metadataCategoryInfo)
{
switch (categoryType)
{
case MetadataElementType.Indicator:
this._DBConnection.ExecuteNonQuery("update " + queries.TablesName.Indicator + " set " + Indicator.IndicatorInfo + "='" + DIQueries.RemoveQuotesForSqlQuery(metadataCategoryInfo) + "'where " + Indicator.IndicatorNId + "=" + targetNid);
break;
case MetadataElementType.Area:
this._DBConnection.ExecuteNonQuery("update " + queries.TablesName.AreaMapMetadata + " set " + Area_Map_Metadata.MetadataText + "='" + DIQueries.RemoveQuotesForSqlQuery(metadataCategoryInfo) + "' where " + Area_Map_Layer.LayerNId + "=" + targetNid);
break;
case MetadataElementType.Source:
this._DBConnection.ExecuteNonQuery("update " + queries.TablesName.IndicatorClassifications + " set " + IndicatorClassifications.ICInfo + "='" + DIQueries.RemoveQuotesForSqlQuery(metadataCategoryInfo) + "'where " + IndicatorClassifications.ICNId + "=" + targetNid);
break;
default:
break;
}
}
/// <summary>
/// Updates count information
/// </summary>
/// <param name="NID"></param>
/// <param name="areaCount"></param>
/// <param name="indicatorCount"></param>
/// <param name="IUSCount"></param>
/// <param name="timeperiodCount"></param>
/// <param name="sourceCount"></param>
/// <param name="dataCount"></param>
public void UpdateCounts(int NID,
string areaCount, string indicatorCount, string IUSCount, string timeperiodCount, string sourceCount, string dataCount)
{
string PublishedOn = System.DateTime.Now.ToString("yyyy-MM-dd");
string SqlQuery = string.Empty;
DITables TablesName;
string DataPrefix = this.DBConnection.DIDataSetDefault();
bool UseUpdateQuery = true;
try
{
// check record exists in DB_Metadata table or not
if (Convert.ToInt32(this.DBConnection.ExecuteScalarSqlQuery(DIQueries.GetTableRecordsCount(this.DBQueries.TablesName.DBMetadata, string.Empty))) == 0)
{
UseUpdateQuery = false;
}
foreach (DataRow Row in this.DBConnection.DILanguages(DataPrefix).Rows)
{
TablesName = new DITables(DataPrefix, "_" + Row[Language.LanguageCode].ToString());
if (UseUpdateQuery)
{
// update query
SqlQuery = DevInfo.Lib.DI_LibDAL.Queries.DBMetadata.Update.UpdateCounts(TablesName.DBMetadata, NID,
areaCount, indicatorCount, IUSCount, timeperiodCount, sourceCount, dataCount);
}
else
{
//insert query
SqlQuery = DevInfo.Lib.DI_LibDAL.Queries.DBMetadata.Insert.InsertRecord(TablesName.DBMetadata, string.Empty, string.Empty, PublishedOn, string.Empty, string.Empty, string.Empty,
areaCount, indicatorCount, IUSCount, timeperiodCount, sourceCount, dataCount);
}
this.DBConnection.ExecuteNonQuery(SqlQuery);
}
}
catch (Exception ex)
{
ExceptionFacade.ThrowException(ex);
}
}
private void CreateNUpdateRecommendedSourcesTable(bool forOnlineDB)
{
string DataPrefix = string.Empty;
string LanguageCode = string.Empty;
DITables TableNames;
RecommendedSourcesBuilder RecommendedSourcesTblBuilder;
DIQueries TempQueries;
try
{
// step1: create table for all dataset
foreach (DataRow DataPrefixRow in this._DBConnection.DIDataSets().Rows)
{
DataPrefix = DataPrefixRow[DBAvailableDatabases.AvlDBPrefix].ToString() + "_";
// create table for all available langauges
foreach (DataRow LanguageRow in this._DBConnection.DILanguages(DataPrefix).Rows)
{
LanguageCode = "_" + LanguageRow[Language.LanguageCode].ToString();
// check table already exists or not
TempQueries = new DIQueries(DataPrefix, LanguageCode);
RecommendedSourcesTblBuilder = new RecommendedSourcesBuilder(this._DBConnection, TempQueries);
if (RecommendedSourcesTblBuilder.IsRecommendedSourcesTableExists() == false)
{
TableNames = new DITables(DataPrefix, LanguageCode);
this._DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.RecommendedSources.Insert.CreateTable(TableNames.RecommendedSources,
forOnlineDB, this._DBConnection.ConnectionStringParameters.ServerType));
// step2: insert IC_IUS_Label into recommendedsource table from IC_IUS table
this.DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.RecommendedSources.Insert.InsertAllLabelFrmICIUS(TableNames));
}
}
}
}
catch (Exception ex)
{
throw new ApplicationException(ex.ToString());
}
}
/// <summary>
/// Get path for selected area starting from root area
/// </summary>
/// <param name="ElementNid">ID of selected Data</param>
/// <param name="DI_QueryBase">This contains Connection Details</param>
/// <returns>String containg the path of area node starting from root node</returns>
public static string GetAreaChain(int AreaNId, DIConnection DIConnection, DIQueries DIQueries)
{
string RetVal = string.Empty;
string sSql = string.Empty;
int AreaLevel = 1;
// Get Current Area Level
sSql = DIQueries.Area.GetArea(FilterFieldType.NId, AreaNId.ToString());
DataTable dtArea = DIConnection.ExecuteDataTable(sSql);
if (dtArea != null & dtArea.Rows.Count > 0)
{
AreaLevel = (int)dtArea.Rows[0][Area.AreaLevel];
}
//Get concatinated Area names
sSql = DIQueries.Area.GetAreaChain(AreaNId, AreaLevel, DIConnection.ConnectionStringParameters.ServerType);
RetVal = DIConnection.ExecuteScalarSqlQuery(sSql).ToString();
return(RetVal);
}
private void CreateMetadatCategoryTable(bool forOnlineDB)
{
string DataPrefix = string.Empty;
string LanguageCode = string.Empty;
DITables TableNames;
MetadataCategoryBuilder MetadataCategoryBuilderObj = null;
DIQueries TempQueries;
try
{
// step1: create table for all dataset
foreach (DataRow DataPrefixRow in this._DBConnection.DIDataSets().Rows)
{
DataPrefix = DataPrefixRow[DBAvailableDatabases.AvlDBPrefix].ToString() + "_";
// create table for all available langauges
foreach (DataRow LanguageRow in this._DBConnection.DILanguages(DataPrefix).Rows)
{
LanguageCode = "_" + LanguageRow[Language.LanguageCode].ToString();
// check table already exists or not
TempQueries = new DIQueries(DataPrefix, LanguageCode);
MetadataCategoryBuilderObj = new MetadataCategoryBuilder(this._DBConnection, TempQueries);
if (MetadataCategoryBuilderObj.IsMetadataCategoryTableExists() == false)
{
TableNames = new DITables(DataPrefix, LanguageCode);
this._DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.MetadataCategory.Insert.CreateTable(TableNames.MetadataCategory,
forOnlineDB, this._DBConnection.ConnectionStringParameters.ServerType));
}
}
}
}
catch (Exception ex)
{
throw new ApplicationException(ex.ToString());
}
}
private void UpdateSortOrderIntoSubgroupVal()
{
DataView Table = null;
int Order = 1;
DIQueries TempQueries;
string DataPrefix = string.Empty;
string LanguageCode = string.Empty;
try
{
// step1: create table for all dataset
foreach (DataRow DataPrefixRow in this._DBConnection.DIDataSets().Rows)
{
DataPrefix = DataPrefixRow[DBAvailableDatabases.AvlDBPrefix].ToString() + "_";
// get language for all available langauges
foreach (DataRow LanguageRow in this._DBConnection.DILanguages(DataPrefix).Rows)
{
LanguageCode = "_" + LanguageRow[Language.LanguageCode].ToString();
// check table already exists or not
TempQueries = new DIQueries(DataPrefix, LanguageCode);
// Get SubgroupVal
Table = this._DBConnection.ExecuteDataTable(TempQueries.SubgroupVals.GetSubgroupVals()).DefaultView;
Table.Sort = SubgroupVals.SubgroupVal + " Asc";
// Update SUbgroupVal Order Column
foreach (DataRowView Row in Table)
{
this._DBConnection.ExecuteNonQuery(DevInfo.Lib.DI_LibDAL.Queries.SubgroupVal.Update.UpdateSubgroupValOrder(TempQueries.DataPrefix, TempQueries.LanguageCode, Convert.ToInt32(Row[SubgroupVals.SubgroupValNId]), Order++));
}
}
}
}
catch (Exception)
{
}
}
/// <summary>
/// Returns the instance of SubgroupInfo on the basis of Subgroup Nid
/// </summary>
/// <param name="sourceQuery"></param>
/// <param name="sourceDBConnection"></param>
/// <param name="subgroupNid"></param>
/// <returns></returns>
public static DI6SubgroupInfo GetSubgroupInfo(DIQueries queries, DIConnection dbConnection, FilterFieldType filterClause, string filterText)
{
string Query = string.Empty;
DI6SubgroupInfo RetVal = new DI6SubgroupInfo();
DataTable SubgroupTable;
DI6SubgroupTypeBuilder SGTypeBuilder;
try
{
Query = queries.Subgroup.GetSubgroup(filterClause, filterText);
SubgroupTable = dbConnection.ExecuteDataTable(Query);
//set Subgroup info
if (SubgroupTable != null)
{
if (SubgroupTable.Rows.Count > 0)
{
RetVal.GID = SubgroupTable.Rows[0][Subgroup.SubgroupGId].ToString();
RetVal.Global = Convert.ToBoolean(SubgroupTable.Rows[0][Subgroup.SubgroupGlobal]);
RetVal.Name = SubgroupTable.Rows[0][Subgroup.SubgroupName].ToString();
RetVal.Nid = Convert.ToInt32(SubgroupTable.Rows[0][Subgroup.SubgroupNId].ToString());
RetVal.Type = Convert.ToInt32(SubgroupTable.Rows[0][Subgroup.SubgroupType].ToString());
// Get subgrouptype info
if (RetVal.Type > 0)
{
SGTypeBuilder = new DI6SubgroupTypeBuilder(dbConnection, queries);
RetVal.DISubgroupType = SGTypeBuilder.GetSubgroupTypeInfoByNid(RetVal.Type);
}
}
}
}
catch (Exception)
{
RetVal = null;
}
return(RetVal);
}
public void ReadXmlAndUpdateCategoryTable(string metadataXmlInfo, string metadataTypeText)
{
int CategoryOrder = 0;
XmlDocument XmlDoc = new XmlDocument();
XmlNodeList RootNodeList;
string DefaultCategoryLanguageValue = string.Empty;
MetadataCategoryInfo MetadataInfo;
string CategoryForDatabase = string.Empty;
DITables Tables = null;
DIQueries TempDBQueries = null;
if (!string.IsNullOrEmpty(metadataXmlInfo.Trim()))
{
XmlDoc.LoadXml(metadataXmlInfo);
// Get "Metadata" Root Element NodeList
RootNodeList = XmlDoc.SelectNodes(Constants.MetadataCategory.MetadataCategoryNodePath);
// Get first recrod from "metadata/Category" Node and insert into all available Metadata_Category language table
// Check Category In each "metadata/Category" Node
for (int Index = 0; Index < RootNodeList.Count; Index++)//reach (XmlElement InputNodeList in RootNodeList.Count)
{
MetadataInfo = new MetadataCategoryInfo();
// Set MetadataCategoryInfo Value
MetadataInfo.CategoryName = DICommon.RemoveQuotes(Convert.ToString(RootNodeList[Index].Attributes["name"].Value));
//-- Get Max Category Order
CategoryOrder = Convert.ToInt32(this.DBConnection.ExecuteScalarSqlQuery(this.DBQueries.Metadata_Category.GetMaxMetadataCategoryOrder(metadataTypeText)));
MetadataInfo.CategoryOrder = CategoryOrder;
MetadataInfo.CategoryType = metadataTypeText;
// Add MetadataCategory Into all metdata category language tables
this.CheckNCreateMetadataCategory(MetadataInfo);
}
}
}
