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