/// <summary> /// Update / Save NIds on the basis of GIds /// </summary> /// <param name="DIConnection"></param> /// <param name="DIQueries"></param> public void UpdateNIdsFromGIds(DIConnection DIConnection, DIQueries DIQueries) { IDataReader IDataReader; StringBuilder IDValues = new StringBuilder(); string sSql = string.Empty; string[] ColumnField = new string[0]; StringBuilder Values = new StringBuilder(); #region Indicator if (this._IndicatorGIds.Length > 0) { string[] IndicatorValues = DIQueries.GetSplittedValues(this._IndicatorGIds, Delimiter.TEXT_DELIMITER); StringBuilder IndicatorText = new StringBuilder(); for (int i = 0; i < IndicatorValues.Length; i++) { if (i > 0) { IndicatorText.Append(Delimiter.NUMERIC_DELIMITER); } IndicatorText.Append("'" + IndicatorValues[i] + "'"); } if (this._ShowIUS) { sSql = DIQueries.IUS.GetIUSNIds(IndicatorText.ToString(), DIConnection.ConnectionStringParameters.ServerType); } else { sSql = DIQueries.Indicators.GetIndicator(FilterFieldType.GId, IndicatorText.ToString(), FieldSelection.NId); } IDataReader = DIConnection.ExecuteReader(sSql); while (IDataReader.Read()) { if (IDValues.Length > 0) { IDValues.Append(Delimiter.NUMERIC_DELIMITER); } if (this._ShowIUS == true) { IDValues.Append(IDataReader[Indicator_Unit_Subgroup.IUSNId]); } else { IDValues.Append(IDataReader[Indicator.IndicatorNId]); } } IDataReader.Close(); this._IndicatorNIds = IDValues.ToString(); } else { //-- Clear the NId, if GId is blank this._IndicatorNIds = string.Empty; } #endregion #region Unit if (this._UnitGIds.Length > 0) { sSql = DIQueries.Unit.GetUnit(FilterFieldType.GId, this._UnitGIds); IDataReader = DIConnection.ExecuteReader(sSql); IDValues.Length = 0; while (IDataReader.Read()) { if (IDValues.Length > 0) { IDValues.Append(Delimiter.NUMERIC_DELIMITER); } IDValues.Append(IDataReader[Unit.UnitNId]); } IDataReader.Close(); this._UnitNIds = IDValues.ToString(); } else { //-- Clear the NId, if GId is blank this._UnitNIds = string.Empty; } #endregion #region SubgroupVal if (this._SubgroupValGIds.Length > 0) { sSql = DIQueries.Subgroup.GetSubgroupVals(FilterFieldType.GId, this._SubgroupValGIds); IDataReader = DIConnection.ExecuteReader(sSql); IDValues.Length = 0; while (IDataReader.Read()) { if (IDValues.Length > 0) { IDValues.Append(Delimiter.NUMERIC_DELIMITER); } IDValues.Append(IDataReader[SubgroupVals.SubgroupValNId]); } IDataReader.Close(); this._SubgroupValNIds = IDValues.ToString(); } else { //-- Clear the NId, if GId is blank this._SubgroupValNIds = string.Empty; } #endregion #region Source if (this._SourceNames.Length > 0) { Values.Length = 0; //Clear Stringbuilder ColumnField = DIQueries.GetSplittedValues(this._SourceNames, Delimiter.TEXT_DELIMITER); for (int i = 0; i < ColumnField.Length; i++) { if (i > 0) { Values.Append(Delimiter.NUMERIC_DELIMITER); } Values.Append("'" + ColumnField[i].Replace("'","''") + "'"); } sSql = DIQueries.Source.GetSource(FilterFieldType.Name, Values.ToString(), FieldSelection.Light, false); IDataReader = DIConnection.ExecuteReader(sSql); IDValues.Length = 0; while (IDataReader.Read()) { if (IDValues.Length > 0) { IDValues.Append(Delimiter.NUMERIC_DELIMITER); } IDValues.Append(IDataReader[IndicatorClassifications.ICNId]); } IDataReader.Close(); this._SourceNIds = IDValues.ToString(); } else { //-- Clear the NId, if GId is blank this._SourceNIds = string.Empty; } #endregion #region TimePeriod if (this._TimePeriods.Length > 0) { Values.Length = 0; //Clear Stringbuilder ColumnField = DIQueries.GetSplittedValues(this._TimePeriods, Delimiter.TEXT_DELIMITER); for (int i = 0; i < ColumnField.Length; i++) { if (i > 0) { Values.Append(Delimiter.NUMERIC_DELIMITER); } Values.Append("'" + ColumnField[i].Replace("'", "''") + "'"); } sSql = DIQueries.Timeperiod.GetTimePeriod(FilterFieldType.Name, Values.ToString()); IDataReader = DIConnection.ExecuteReader(sSql); IDValues.Length = 0; while (IDataReader.Read()) { if (IDValues.Length > 0) { IDValues.Append(Delimiter.NUMERIC_DELIMITER); } IDValues.Append(IDataReader[Timeperiods.TimePeriodNId]); } IDataReader.Close(); this._TimePeriodNIds = IDValues.ToString(); } else { //-- Clear the NId, if GId is blank this._TimePeriodNIds = string.Empty; } #endregion #region Area NId if (this._AreaIds.Length > 0) { Values.Length = 0; ColumnField = DIQueries.GetSplittedValues(this._AreaIds, Delimiter.TEXT_DELIMITER); for (int i = 0; i < ColumnField.Length; i++) { if (i > 0) { Values.Append(Delimiter.NUMERIC_DELIMITER); } Values.Append("'" + ColumnField[i].Replace("'", "''") + "'"); } sSql = DIQueries.Area.GetArea(FilterFieldType.ID, Values.ToString()); IDataReader = DIConnection.ExecuteReader(sSql); IDValues.Length = 0; while (IDataReader.Read()) { if (IDValues.Length > 0) { IDValues.Append(Delimiter.NUMERIC_DELIMITER); } IDValues.Append(IDataReader[Area.AreaNId]); } this._AreaNIds = IDValues.ToString(); IDataReader.Close(); } else { //-- Clear the NId, if GId is blank this._AreaNIds = string.Empty; } #endregion #region Source Filter if (this._DataViewFilters.DeletedSourceNames.Length > 0 && this.DataViewFilters.ShowSourceByIUS) { string[] SourceGIds = DIQueries.GetSplittedValues(this.DataViewFilters.DeletedSourceNames, Delimiter.TEXT_DELIMITER); StringBuilder SourceText = new StringBuilder(); for (int i = 0; i < SourceGIds.Length; i++) { if (i > 0) { SourceText.Append(Delimiter.NUMERIC_DELIMITER); } SourceText.Append("'" + SourceGIds[i].Replace("'", "''") + "'"); } //if (this.DataViewFilters.ShowSourceByIUS) //{ sSql = DIQueries.Source.GetIUSNId_SourceNIds(SourceText.ToString(), DIConnection.ConnectionStringParameters.ServerType); //} //else //{ // sSql = DIQueries.IndicatorClassification.GetIC(FilterFieldType.Name, SourceText.ToString(), ICType.Source, FieldSelection.NId); //} IDataReader = DIConnection.ExecuteReader(sSql); IDValues.Length = 0; while (IDataReader.Read()) { if (IDValues.Length > 0) { IDValues.Append(Delimiter.NUMERIC_DELIMITER); } if (this.DataViewFilters.ShowSourceByIUS) { IDValues.Append("'" + IDataReader[0] + "'"); // Expression column with concatinated IUSNId_ICNId } else { IDValues.Append(IDataReader[IndicatorClassifications.ICNId]); } } this.DataViewFilters.DeletedSourceNIds = IDValues.ToString(); IDataReader.Close(); } else if (this._DataViewFilters.DeletedSourceNIds.Length == 0) { //-- Clear the NId, if GId is blank this._DataViewFilters.DeletedSourceNIds = string.Empty; } #endregion #region Unit Filter if (this._DataViewFilters.DeletedUnitGIds.Length > 0) { string[] UnitGIds = DIQueries.GetSplittedValues(this.DataViewFilters.DeletedUnitGIds, Delimiter.TEXT_DELIMITER); StringBuilder UnitText = new StringBuilder(); for (int i = 0; i < UnitGIds.Length; i++) { if (i > 0) { UnitText.Append(Delimiter.NUMERIC_DELIMITER); } UnitText.Append("'" + UnitGIds[i] + "'"); } if (this.DataViewFilters.ShowUnitByIndicator) { sSql = DIQueries.Unit.GetIndicatorNId_UnitNIds(UnitText.ToString(), DIConnection.ConnectionStringParameters.ServerType); } else { sSql = DIQueries.Unit.GetUnit(FilterFieldType.GId, UnitText.ToString()); } IDValues.Length = 0; IDataReader = DIConnection.ExecuteReader(sSql); while (IDataReader.Read()) { if (IDValues.Length > 0) { IDValues.Append(Delimiter.NUMERIC_DELIMITER); } if (this.DataViewFilters.ShowUnitByIndicator) { IDValues.Append("'" + IDataReader[0] + "'"); // Expression column with concatinated IndicatorNId_UnitNId } else { IDValues.Append(IDataReader[Unit.UnitNId]); } } IDataReader.Close(); this.DataViewFilters.DeletedUnitNIds = IDValues.ToString(); } else { //-- Clear the NId, if GId is blank this._DataViewFilters.DeletedUnitNIds = string.Empty; } #endregion #region Subgroup Filter if (this.DataViewFilters.DeletedSubgroupGIds.Length > 0) { string[] SubgroupGIds = DIQueries.GetSplittedValues(this.DataViewFilters.DeletedSubgroupGIds, Delimiter.TEXT_DELIMITER); StringBuilder SubgroupText = new StringBuilder(); for (int i = 0; i < SubgroupGIds.Length; i++) { if (i > 0) { SubgroupText.Append(Delimiter.NUMERIC_DELIMITER); } SubgroupText.Append("'" + SubgroupGIds[i] + "'"); } if (this.DataViewFilters.ShowSubgroupByIndicator) { sSql = DIQueries.Subgroup.GetIndicatorNId_SubgroupNIds(SubgroupText.ToString(), DIConnection.ConnectionStringParameters.ServerType); } else { sSql = DIQueries.Subgroup.GetSubgroupVals(FilterFieldType.GId, SubgroupText.ToString()); } IDValues.Length = 0; IDataReader = DIConnection.ExecuteReader(sSql); while (IDataReader.Read()) { if (IDValues.Length > 0) { IDValues.Append(Delimiter.NUMERIC_DELIMITER); } if (this.DataViewFilters.ShowSubgroupByIndicator) { IDValues.Append("'" + IDataReader[0] + "'"); // Expression column with concatinated IndicatorNId_SubgroupvalNId } else { IDValues.Append(IDataReader[SubgroupVals.SubgroupValNId]); } } IDataReader.Close(); this.DataViewFilters.DeletedSubgroupNIds = IDValues.ToString(); } else { //-- Clear the NId, if GId is blank this._DataViewFilters.DeletedSubgroupNIds = string.Empty; } #endregion #region DataPoint Filter if (this.DataViewFilters.DeletedDataGIds.Length > 0) { string[] DeletedDataGIds = DIQueries.GetSplittedValues(this.DataViewFilters.DeletedDataGIds, Delimiter.TEXT_DELIMITER); StringBuilder DataGIds = new StringBuilder(); for (int i = 0; i < DeletedDataGIds.Length; i++) { if (i > 0) { DataGIds.Append(Delimiter.NUMERIC_DELIMITER); } DataGIds.Append("'" + DeletedDataGIds[i] + "'"); } sSql = DIQueries.Data.GetDataPointNIds(DataGIds.ToString(), DIConnection.ConnectionStringParameters.ServerType); IDValues.Length = 0; IDataReader = DIConnection.ExecuteReader(sSql); while (IDataReader.Read()) { if (IDValues.Length > 0) { IDValues.Append(Delimiter.NUMERIC_DELIMITER); } IDValues.Append("" + IDataReader[Data.DataNId] + ""); } IDataReader.Close(); this.DataViewFilters.DeletedDataNIds = IDValues.ToString(); } else { //-- Clear the NId, if GId is blank this._DataViewFilters.DeletedDataNIds = string.Empty; } #endregion #region IUS Filter if (this.DataViewFilters.IndicatorDataValueFilters.Count > 0) { string InvalidRow = string.Empty; int InvalidRowIndex = 0; for (int i = 0; i < this.DataViewFilters.IndicatorDataValueFilters.Count; i++) { if (this.DataViewFilters.IndicatorDataValueFilters.ShowIUS) { sSql = DIQueries.IUS.GetIUSNIds("'" + this.DataViewFilters.IndicatorDataValueFilters[i].IndicatorGId + "'", DIConnection.ConnectionStringParameters.ServerType); } else { sSql = DIQueries.Indicators.GetIndicator(FilterFieldType.GId, "'" + this.DataViewFilters.IndicatorDataValueFilters[i].IndicatorGId + "'", FieldSelection.Light); } IDataReader = DIConnection.ExecuteReader(sSql.ToString()); if (IDataReader.Read()) { if (this.DataViewFilters.IndicatorDataValueFilters.ShowIUS) { this.DataViewFilters.IndicatorDataValueFilters[i].IndicatorNId = (int)IDataReader[Indicator_Unit_Subgroup.IUSNId]; } else { this.DataViewFilters.IndicatorDataValueFilters[i].IndicatorNId = (int)IDataReader[Indicator.IndicatorNId]; } } else { if (InvalidRow.Length > 0) { InvalidRow += ","; } InvalidRowIndex += 1; //-- Insert the index of invalid rows. InvalidRow += i.ToString(); } IDataReader.Close(); } //-- delete the Invalid rows from the collection. string[] Row = new string[0]; Row = DIQueries.GetSplittedValues(InvalidRow, ","); for (int i = 0; i < InvalidRowIndex; i++) { Row[i] = (Convert.ToInt32(Row[i]) - i).ToString(); this.DataViewFilters.IndicatorDataValueFilters.RemoveAt(Convert.ToInt32(Row[i])); } } #endregion }
private string[] AutoSelectedTimePeriods(DIConnection dbConnection, DIQueries dbQueries, UserSelection dbUserSelection) { string[] Retval = new string[0]; try { string TimePeriods = string.Empty; IDataReader TimePeriodReader; if (string.IsNullOrEmpty(this._FromTimePeriod)) { TimePeriodReader = dbConnection.ExecuteReader(dbQueries.Timeperiod.GetAutoSelectTimeperiod(dbUserSelection.IndicatorNIds, dbUserSelection.ShowIUS, dbUserSelection.AreaNIds, dbUserSelection.SourceNIds)); } else { if (this._IsToMRD) { TimePeriodReader = dbConnection.ExecuteReader(dbQueries.Timeperiod.GetAutoSelectedTimePeriodsRange(this._FromTimePeriod, string.Empty, dbUserSelection)); } else { TimePeriodReader = dbConnection.ExecuteReader(dbQueries.Timeperiod.GetAutoSelectedTimePeriodsRange(this._FromTimePeriod, this._ToTimePeriod, dbUserSelection)); } } while (TimePeriodReader.Read()) { TimePeriods += "," + TimePeriodReader[Timeperiods.TimePeriod].ToString(); } TimePeriodReader.Close(); if (!string.IsNullOrEmpty(TimePeriods)) { TimePeriods = TimePeriods.Substring(1); } Retval = DICommon.SplitString(TimePeriods, ","); } catch (Exception) { } return Retval; }
/// <summary> /// Update / Save GIds on the basis of NIds /// </summary> /// <param name="DIConnection"></param> /// <param name="DIQueries"></param> public void UpdateGIdsFromNIds(DIConnection DIConnection, DIQueries DIQueries) { IDataReader IDataReader; string IdValue = string.Empty; string sSql = string.Empty; #region Indicator GId if (this._IndicatorNIds.Length > 0) { if (this._ShowIUS == true) { sSql = DIQueries.IUS.GetIUSGIds(this._IndicatorNIds, DIConnection.ConnectionStringParameters.ServerType); } else { sSql = DIQueries.Indicators.GetIndicator(FilterFieldType.NId, this._IndicatorNIds, FieldSelection.Light); } IDataReader = DIConnection.ExecuteReader(sSql); while (IDataReader.Read()) { if (IdValue.Length > 0) { IdValue += Delimiter.TEXT_DELIMITER; } if (this._ShowIUS == true) { IdValue += IDataReader[0].ToString(); // Expression column with concatinated I_U_S Gids } else { IdValue += IDataReader[Indicator.IndicatorGId].ToString(); } } IDataReader.Close(); this._IndicatorGIds = IdValue; } else { //-- Clear the GID, if NID is blank this._IndicatorGIds = string.Empty; } #endregion #region Unit GId if (this._UnitNIds.Length > 0) { sSql = DIQueries.Unit.GetUnit(FilterFieldType.NId, this._UnitNIds); IDataReader = DIConnection.ExecuteReader(sSql); IdValue = string.Empty; while (IDataReader.Read()) { if (IdValue.Length > 0) { IdValue += Delimiter.NUMERIC_DELIMITER; } IdValue += IDataReader[Unit.UnitGId].ToString(); } IDataReader.Close(); this._UnitGIds = IdValue; } else { //-- Clear the GID, if NID is blank this._UnitGIds = string.Empty; } #endregion #region SubgroupVal GId if (this._SubgroupValNIds.Length > 0) { sSql = DIQueries.Subgroup.GetSubgroupVals(FilterFieldType.NId, this._SubgroupValNIds); IDataReader = DIConnection.ExecuteReader(sSql); IdValue = string.Empty; while (IDataReader.Read()) { if (IdValue.Length > 0) { IdValue += Delimiter.NUMERIC_DELIMITER; } IdValue += IDataReader[SubgroupVals.SubgroupValGId].ToString(); } IDataReader.Close(); this._SubgroupValGIds = IdValue; } else { //-- Clear the GID, if NID is blank this._SubgroupValGIds = string.Empty; } #endregion #region Source Name if (this._SourceNIds.Length > 0) { sSql = DIQueries.Source.GetSource(FilterFieldType.NId, this._SourceNIds, FieldSelection.Light, false); IDataReader = DIConnection.ExecuteReader(sSql); IdValue = string.Empty; while (IDataReader.Read()) { if (IdValue.Length > 0) { IdValue += Delimiter.TEXT_DELIMITER; } IdValue += IDataReader[IndicatorClassifications.ICName].ToString(); } IDataReader.Close(); this._SourceNames = IdValue; } else { //-- Clear the GID, if NID is blank this._SourceNames = string.Empty; } #endregion #region TimePeriod if (this._TimePeriodNIds.Length > 0) { sSql = DIQueries.Timeperiod.GetTimePeriod(FilterFieldType.NId, this.TimePeriodNIds); IDataReader = DIConnection.ExecuteReader(sSql); IdValue = string.Empty; while (IDataReader.Read()) { if (IdValue.Length > 0) { IdValue += Delimiter.TEXT_DELIMITER; } IdValue += IDataReader[Timeperiods.TimePeriod].ToString(); } IDataReader.Close(); this._TimePeriods = IdValue; } else { //-- Clear the GID, if NID is blank this._TimePeriods = string.Empty; } #endregion #region AreaId if (this._AreaNIds.Length > 0) { sSql = DIQueries.Area.GetArea(FilterFieldType.NId, this._AreaNIds); IDataReader = DIConnection.ExecuteReader(sSql); IdValue = string.Empty; while (IDataReader.Read()) { if (IdValue.Length > 0) { IdValue += Delimiter.TEXT_DELIMITER; } IdValue += IDataReader[Area.AreaID].ToString(); } this._AreaIds = IdValue; IDataReader.Close(); } else { //-- Clear the GID, if NID is blank this._AreaIds = string.Empty; } #endregion #region Source Filter if (this._DataViewFilters.DeletedSourceNIds.Length > 0 && this.DataViewFilters.ShowSourceByIUS) { //if (this.DataViewFilters.ShowSourceByIUS) //{ sSql = DIQueries.Source.GetIndGId_UnitGId_SubgroupGID_SoureNames(this.DataViewFilters.DeletedSourceNIds, DIConnection.ConnectionStringParameters.ServerType); //} //else //{ // sSql = DIQueries.IndicatorClassification.GetIC(FilterFieldType.NId, this.DataViewFilters.DeletedSourceNIds, ICType.Source, FieldSelection.Name); //} IDataReader = DIConnection.ExecuteReader(sSql); IdValue = string.Empty; while (IDataReader.Read()) { if (IdValue.Length > 0) { IdValue += Delimiter.TEXT_DELIMITER; } if (this.DataViewFilters.ShowSourceByIUS) { IdValue += IDataReader[0].ToString(); // Expression column with concatinated IndGId_UnitGId_SubgroupGId_SourceName } else { IdValue += IDataReader[IndicatorClassifications.ICName].ToString(); } } this.DataViewFilters.DeletedSourceNames = IdValue; IDataReader.Close(); } else if (this._DataViewFilters.DeletedSourceNames.Length == 0) { //-- Clear the GID, if NID is blank this._DataViewFilters.DeletedSourceNames = string.Empty; } #endregion #region Unit Filter if (this._DataViewFilters.DeletedUnitNIds.Length > 0) { if (this.DataViewFilters.ShowUnitByIndicator) { sSql = DIQueries.Unit.GetIndGId_UnitGIds(this.DataViewFilters.DeletedUnitNIds, DIConnection.ConnectionStringParameters.ServerType); } else { sSql = DIQueries.Unit.GetUnit(FilterFieldType.NId, this.DataViewFilters.DeletedUnitNIds); } IdValue = string.Empty; IDataReader = DIConnection.ExecuteReader(sSql); while (IDataReader.Read()) { if (IdValue.Length > 0) { IdValue += Delimiter.TEXT_DELIMITER; } if (this.DataViewFilters.ShowUnitByIndicator) { IdValue += IDataReader[0].ToString(); } else { IdValue += IDataReader[Unit.UnitGId].ToString(); } } IDataReader.Close(); this.DataViewFilters.DeletedUnitGIds = IdValue; } else { //-- Clear the GID, if NID is blank this._DataViewFilters.DeletedUnitGIds = string.Empty; } #endregion #region Subgroup Filter if (this.DataViewFilters.DeletedSubgroupNIds.Length > 0) { if (this.DataViewFilters.ShowSubgroupByIndicator) { sSql = DIQueries.Subgroup.GetIndGId_SubgroupGIds(this.DataViewFilters.DeletedSubgroupNIds, DIConnection.ConnectionStringParameters.ServerType); } else { sSql = DIQueries.Subgroup.GetSubgroupVals(FilterFieldType.NId, this.DataViewFilters.DeletedSubgroupNIds); } IdValue = string.Empty; IDataReader = DIConnection.ExecuteReader(sSql); while (IDataReader.Read()) { if (IdValue.Length > 0) { IdValue += Delimiter.TEXT_DELIMITER; } if (this.DataViewFilters.ShowSubgroupByIndicator) { IdValue += IDataReader[0].ToString(); } else { IdValue += IDataReader[SubgroupVals.SubgroupValGId].ToString(); } } IDataReader.Close(); this.DataViewFilters.DeletedSubgroupGIds = IdValue; } else { //-- Clear the GID, if NID is blank this._DataViewFilters.DeletedSubgroupGIds = string.Empty; } #endregion #region DataPoint Filter if (this.DataViewFilters.DeletedDataNIds.Trim().Length > 0) { StringBuilder DataPointGid = new StringBuilder(); sSql = DIQueries.Data.GetDataViewDataByDataNIDs(this.DataViewFilters.DeletedDataNIds); IDataReader = DIConnection.ExecuteReader(sSql); while (IDataReader.Read()) { if (DataPointGid.Length > 0) { DataPointGid.Append(Delimiter.TEXT_DELIMITER); } DataPointGid.Append(IDataReader[Indicator.IndicatorGId] + Delimiter.TEXT_SEPARATOR + IDataReader[Unit.UnitGId] + Delimiter.TEXT_SEPARATOR); DataPointGid.Append(IDataReader[SubgroupVals.SubgroupValGId] + Delimiter.TEXT_SEPARATOR + IDataReader[Timeperiods.TimePeriod] + Delimiter.TEXT_SEPARATOR); DataPointGid.Append(IDataReader[Area.AreaID] + Delimiter.TEXT_SEPARATOR + IDataReader[IndicatorClassifications.ICName]); } this.DataViewFilters.DeletedDataGIds = DataPointGid.ToString(); IDataReader.Close(); } else { //-- Clear the GID, if NID is blank this._DataViewFilters.DeletedDataGIds = string.Empty; } #endregion #region IUS Filter if (this.DataViewFilters.IndicatorDataValueFilters.Count > 0) { for (int i = 0; i < this.DataViewFilters.IndicatorDataValueFilters.Count; i++) { if (this.DataViewFilters.IndicatorDataValueFilters.ShowIUS) { sSql = DIQueries.IUS.GetIUSGIds(this.DataViewFilters.IndicatorDataValueFilters[i].IndicatorNId.ToString(), DIConnection.ConnectionStringParameters.ServerType); } else { sSql = DIQueries.Indicators.GetIndicator(FilterFieldType.NId, this.DataViewFilters.IndicatorDataValueFilters[i].IndicatorNId.ToString(), FieldSelection.Light); } IDataReader = DIConnection.ExecuteReader(sSql.ToString()); if (IDataReader.Read()) { if (this.DataViewFilters.IndicatorDataValueFilters.ShowIUS) { this.DataViewFilters.IndicatorDataValueFilters[i].IndicatorGId = IDataReader[0].ToString(); } else { this.DataViewFilters.IndicatorDataValueFilters[i].IndicatorGId = IDataReader[Indicator.IndicatorGId].ToString(); } } IDataReader.Close(); } } #endregion }
public string UpdateTimePeriods(DIConnection dbConnection, DIQueries dbQueries, UserSelection dbUserSelection) { string RetVal = string.Empty; try { IDataReader TimeReader = null; //'-- Set the MRD filter to true if (this._IsToMRD | this._MRD) { dbUserSelection.DataViewFilters.MostRecentData = true; } else { dbUserSelection.DataViewFilters.MostRecentData = false; } if (this._IsToMRD || !string.IsNullOrEmpty(this._ToTimePeriod) || string.IsNullOrEmpty(dbUserSelection.TimePeriodNIds.Trim())) { if (this._IsToMRD) { //'-- Get all the time period greater then TO time period TimeReader = dbConnection.ExecuteReader(dbQueries.Timeperiod.GetAutoSelectedTimePeriodsRange(this._FromTimePeriod, "", dbUserSelection)); } else if (!string.IsNullOrEmpty(this._ToTimePeriod)) { //'-- Get auto selected time period between from and two TimeReader = dbConnection.ExecuteReader(dbQueries.Timeperiod.GetAutoSelectedTimePeriodsRange(this._FromTimePeriod, this._ToTimePeriod, dbUserSelection)); } else if (string.IsNullOrEmpty(dbUserSelection.TimePeriodNIds.Trim())) { //-- Get auto selected time periods TimeReader = dbConnection.ExecuteReader(dbQueries.Timeperiod.GetAutoSelectByIndicatorAreaSource(string.Empty, dbUserSelection.AreaNIds, dbUserSelection.SourceNIds, dbUserSelection.IndicatorNIds)); } while (TimeReader.Read()) { RetVal += "," + TimeReader[Timeperiods.TimePeriodNId]; } if (!string.IsNullOrEmpty(RetVal)) { RetVal = RetVal.Substring(1); } else { RetVal = "-1"; } TimeReader.Close(); } //-- Exclude the TimePeriod from the selections. //this.ExcludeTimePeriodsFromUserSelection(dbConnection, dbQueries, dbUserSelection); } catch (Exception) { } return RetVal; }
/// <summary> /// Get the parent levels of selected level /// </summary> /// <param name="levels"></param> /// <param name="dbConnection"></param> /// <param name="dbQueries"></param> /// <returns></returns> private string GetParentLevel(string levels,DIConnection dbConnection, DIQueries dbQueries) { string RetVal = string.Empty; try { int MaxLevel = -1; IDataReader LevelReader; string[] Levels = new string[0]; Levels = DICommon.SplitString(levels, ","); //-- Get the maximum level from the selected level. MaxLevel = Convert.ToInt32(Levels[0]); foreach (string SecondaryLevel in Levels) { if (Convert.ToInt32(MaxLevel) < Convert.ToInt32(SecondaryLevel)) { MaxLevel = Convert.ToInt32(SecondaryLevel); } } RetVal=MaxLevel.ToString(); //-- Get the parent levels. LevelReader = dbConnection.ExecuteReader(dbQueries.Area.GetAreaLevel()); while (LevelReader.Read()) { if (MaxLevel > Convert.ToInt32(LevelReader[Area_Level.AreaLevel])) { RetVal += "," + LevelReader[Area_Level.AreaLevel].ToString(); } } LevelReader.Close(); } catch (Exception) { } return RetVal; }
/// <summary> /// Get all the area levels /// </summary> /// <returns></returns> /// <remarks></remarks> private List<Int32> GetAreaLevel(DIConnection dbConnection, DIQueries dbQueries) { List<Int32> Retval = new List<Int32>(); IDataReader LevelReader = null; try { LevelReader = dbConnection.ExecuteReader(dbQueries.Area.GetAreaLevel(Lib.DI_LibDAL.Queries.FilterFieldType.None, "")); while (LevelReader.Read()) { Retval.Add(Convert.ToInt32(LevelReader[Area_Level.AreaLevel])); } } catch (Exception ex) { } finally { LevelReader.Close(); } return Retval; }
/// <summary> /// Get all the AreaNIds of the selected level and update the user selection. /// </summary> /// <param name="selectedAreaNIds"></param> /// <param name="levels"></param> /// <remarks></remarks> public void GetSubNationals(DIConnection dbConnection, DIQueries dbQueries, UserSelection dbUserSelection, string selectedAreaNIds, string levels) { try { if (!string.IsNullOrEmpty(this._SecondryAreaLevels)) { StringBuilder sbArea = new StringBuilder(); string[] SelectedLevel = new string[0]; string sLevels = string.Empty; DataTable Areadt = new DataTable(); DataRow[] Rows = null; string SelectedAreaNId = string.Empty; Areas = new Dictionary<int, string>(); foreach (Int32 Level in GetAreaLevel(dbConnection, dbQueries)) { IDataReader AreaReader; //'-- Get Area NIDs of the levles AreaReader = dbConnection.ExecuteReader(dbQueries.Area.GetAreaNIdByAreaLevel(dbUserSelection.AreaNIds, Level)); while (AreaReader.Read()) { sbArea.Append("," + AreaReader[Area.AreaNId].ToString()); } //'-- Add the selected AreaNIDs according to their level if (sbArea.Length > 0) { Areas.Add(Level, sbArea.ToString().Substring(1)); } AreaReader.Close(); sbArea.Length = 0; } //'-- Get the Levels. sLevels = this.GetParentLevel(this._SecondryAreaLevels, dbConnection, dbQueries); this._AreaLevels = sLevels; SelectedLevel = DICommon.SplitString(sLevels, ","); //'-- Insert all the required level to build the comma seprated AreaNIds of selected levels for (int Index = GetMaxKey(Areas); Index <= GetMinSelectedLevel(SelectedLevel) - 1; Index++) { Array.Resize(ref SelectedLevel, SelectedLevel.Length + 1); SelectedLevel[SelectedLevel.Length - 1] = Index.ToString(); } //'-- Sort on the all the required levels SelectedLevel = Sort(SelectedLevel); //'-- Comma seprated required levels. sLevels = string.Empty; foreach (string NewLevel in SelectedLevel) { sLevels += "," + NewLevel; } if (!string.IsNullOrEmpty(sLevels)) { sLevels = sLevels.Substring(1); } if (!string.IsNullOrEmpty(this._SecondryAreaLevels)) { //'-- Get all the areas of the required levels Areadt = dbConnection.ExecuteDataTable(dbQueries.Area.GetAreasByAreaLevels(sLevels)); foreach (string sLevel in SelectedLevel) { sbArea.Length = 0; if (string.IsNullOrEmpty(GetSelectedAreaNId(Areas, Convert.ToInt32(sLevel)))) { //'-- Get the saved AreaNIds if (Convert.ToInt32(sLevel) > 1) { SelectedAreaNId = GetSelectedAreaNId(Areas, Convert.ToInt32(sLevel) - 1); } //'-- Get the AreaNIds on the basis of selected areas and level if (!string.IsNullOrEmpty(SelectedAreaNId)) { Rows = Areadt.Select(Area.AreaLevel + " = " + Convert.ToInt32(sLevel) + " AND " + Area.AreaParentNId + " IN (" + SelectedAreaNId + ")"); } else { Rows = Areadt.Select(Area.AreaLevel + " = " + Convert.ToInt32(sLevel)); } //'-- Add the AreaNIds in the collection. foreach (DataRow Row in Rows) { sbArea.Append("," + Row[Area.AreaNId].ToString()); } if (sbArea.Length > 0) { Areas.Add(Convert.ToInt32(sLevel), sbArea.ToString().Substring(1)); } } } SelectedLevel = DICommon.SplitString(this._SecondryAreaLevels, ","); //'-- Update user selection with AreaNIds dbUserSelection.AreaNIds = string.Empty; dbUserSelection.AreaIds = string.Empty; foreach (string selLevel in SelectedLevel) { SelectedAreaNId = GetSelectedAreaNId(Areas, Convert.ToInt32(selLevel)); if (!string.IsNullOrEmpty(SelectedAreaNId)) { dbUserSelection.AreaNIds += "," + SelectedAreaNId; } } if (!string.IsNullOrEmpty(dbUserSelection.AreaNIds)) { dbUserSelection.AreaNIds = dbUserSelection.AreaNIds.Substring(1); } Areadt = dbConnection.ExecuteDataTable(dbQueries.Area.GetArea(FilterFieldType.NId, dbUserSelection.AreaNIds, Lib.DI_LibDAL.Queries.Area.Select.OrderBy.AreaName)); foreach (DataRow Row in Areadt.Rows) { if (string.IsNullOrEmpty(dbUserSelection.AreaIds)) { dbUserSelection.AreaIds = Row[Area.AreaID].ToString(); } else { dbUserSelection.AreaIds += Delimiter.TEXT_DELIMITER + Row[Area.AreaID].ToString(); } } } } } catch (Exception ex) { } }
/// <summary> /// Transforms deleted source nids from IUS_SourceNIDs format into SourceNIDs format and vice a versa /// Also updates selected field value in Source / IUSSource table /// After transformation, calling routine should explicitly toggle the status of ShowSourceByIUS property /// </summary> /// <param name="userSelection">UserSelection</param> /// <returns>transformed deleted source nids</returns> public static string GetTransformedDeletedSourceNIDs(UserSelection userSelection, DataTable dtIUSSource, DIConnection dbConnection, DIQueries dbQueries) { // -- moDIDataView.IUSSource - Available IUS _ Source string sNewDelSourceNIDs = string.Empty; DataRow[] Rows = new DataRow[0]; string DelSourceNIDs = string.Empty; System.Text.StringBuilder sbNewDelSourceNIDs = new System.Text.StringBuilder(); string retVal = string.Empty; string[] DistinctColumns = new string[1]; string[] IUSDistinctColumns = new string[2]; if (userSelection.DataViewFilters.ShowSourceByIUS || string.IsNullOrEmpty(userSelection.DataViewFilters.DeletedSourceNIds)) { // -- Do Nothing retVal = sNewDelSourceNIDs; } else { try { IDataReader IUSReader; IUSReader = dbConnection.ExecuteReader(dbQueries.Data.GetDataViewDataByDataNIDs(userSelection.DataViewFilters.DeletedSourceNIds)); while (IUSReader.Read()) { sNewDelSourceNIDs = "'" + IUSReader[Data.IUSNId].ToString() + "_" + IUSReader[IndicatorClassifications.ICNId].ToString() + "'"; retVal += "," + sNewDelSourceNIDs; Rows = dtIUSSource.Select(Indicator_Unit_Subgroup.IUSNId + " + '_' + " + IndicatorClassifications.ICNId + " IN (" + sNewDelSourceNIDs + ")"); foreach (DataRow Row in Rows) { Row[DataExpressionColumns.Selected] = false; } } IUSReader.Close(); if (!string.IsNullOrEmpty(retVal)) { retVal = retVal.Substring(1); } } catch (Exception) { } } return retVal; }