Example #1
0
        /// <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;
            }
Example #3
0
        /// <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)
                {
                }
            }
Example #8
0
        /// <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;
        }