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