/// <summary>
/// Area Autoselection: From the TreeView by Selected Area, Target Area Level
/// </summary>
/// <param name="userPreference"></param>
/// <param name="dIConnection"></param>
/// <param name="dIQueries"></param>
/// <param name="parentNId"></param>
/// <param name="currentLevel"></param>
/// <param name="requiredLevel"></param>
public AutoSelectArea(UserPreference.UserPreference userPreference, DI_LibDAL.Connection.DIConnection dIConnection, DI_LibDAL.Queries.DIQueries dIQueries, int parentNId, int currentLevel, int requiredLevel)
{
// -- Connection details preserved
this.mdIConnection = dIConnection;
this.mdIQueries = dIQueries;
this.mUserPreference = userPreference;
// Autoselect Parameters
this.mparentNId = parentNId;
this.mcurrentLevel = currentLevel;
this.mrequiredLevel = requiredLevel;
this.mAreaNIDs = string.Empty;
}
/// <summary>
/// Area Autoselection: From the Available list by a list of available Area NIDs
/// </summary>
/// <param name="userPreference"></param>
/// <param name="dIConnection"></param>
/// <param name="dIQueries"></param>
/// <param name="sAvlAreaNIDs"></param>
public AutoSelectArea(UserPreference.UserPreference userPreference, DI_LibDAL.Connection.DIConnection dIConnection, DI_LibDAL.Queries.DIQueries dIQueries, string avlAreaNIDs)
{
// -- Connection details preserved
this.mdIConnection = dIConnection;
this.mdIQueries = dIQueries;
this.mUserPreference = userPreference;
// Autoselect Parameters
this.mparentNId = -1;
this.mcurrentLevel = 0;
this.mrequiredLevel = 0;
this.mAreaNIDs = avlAreaNIDs;
}
/// <summary>
/// Set metadata master tables for Indicator / Area / Source
/// </summary>
/// <param name="metadataElementType">Indicator / Area / Source</param>
/// <param name="userPreference"></param>
/// <param name="dIConnection"></param>
/// <param name="dIQueries"></param>
/// <returns></returns>
private bool SetMetedataTables(MetadataElementType metadataElementType, UserPreference.UserPreference userPreference, DI_LibDAL.Connection.DIConnection dIConnection, DI_LibDAL.Queries.DIQueries dIQueries)
{
bool RetVal = false;
string MaskFilePath = string.Empty;
XmlDocument MaskFileDocument = new XmlDocument();
XmlNodeList MaskNodeList;
string MaskPathText = string.Empty;
string MaskPositionText = string.Empty;
string[] MaskPositionArray;
XmlDocument MetadataDocument = new XmlDocument();
string MetadataXml = string.Empty;
XmlNodeList MetadataFld_ValNodeList;
string[] arrSelectedMetadataFields = null;
string JoinColumnName = string.Empty;
string MetadataColumnName = string.Empty;
string _Query = string.Empty;
DataTable DistinctElementTable;
string[] DistinctNIds = null;
DataView dvMetadataInfo = null;
DataTable ElementMetadataTable = null;
string CategoryType = string.Empty; // I / A / S
string MetadataPrefix = string.Empty; //MD_IND_, MD_SRC_
switch (metadataElementType)
{
case MetadataElementType.Indicator:
// Set Category Type
CategoryType = DIQueries.MetadataElementTypeText[MetadataElementType.Indicator];
MetadataPrefix = UserPreference.UserPreference.DataviewPreference.MetadataIndicator;
// Get Metadata fields from userPreference
arrSelectedMetadataFields = userPreference.DataView.MetadataIndicatorField.Split(",".ToCharArray(), System.StringSplitOptions.RemoveEmptyEntries);
// Set Mask file path
MaskFilePath = Path.Combine(this.msMaskFolder, MASK_FILE_INDICATOR);
//Set Join column and Metadata Column name
JoinColumnName = Indicator.IndicatorNId;
MetadataColumnName = Indicator.IndicatorInfo;
// Get distinct indicator nids
DistinctElementTable = this._IUSIndicator.DefaultView.ToTable(true, JoinColumnName);
DistinctNIds = new string[DistinctElementTable.Rows.Count];
for (int i = 0; i < DistinctElementTable.Rows.Count; i++)
{
DistinctNIds[i] = DistinctElementTable.Rows[i][JoinColumnName].ToString();
}
// Get Metadata Info for all Indicators
_Query = this.mdIQueries.Indicators.GetIndicator(FilterFieldType.NId, string.Join(",", DistinctNIds), FieldSelection.Heavy);
dvMetadataInfo = this.ExecuteInvariantDataTable(_Query).DefaultView;
ElementMetadataTable = this._MetadataIndicator;
break;
case MetadataElementType.Area:
// Set Category Type
CategoryType = DIQueries.MetadataElementTypeText[MetadataElementType.Area];
MetadataPrefix = UserPreference.UserPreference.DataviewPreference.MetadataArea;
// Get Metadata fields from userPreference
arrSelectedMetadataFields = userPreference.DataView.MetadataAreaField.Split(",".ToCharArray(), System.StringSplitOptions.RemoveEmptyEntries);
// Set Mask file path
MaskFilePath = Path.Combine(this.msMaskFolder, MASK_FILE_AREA);
//Get LayerNids for these areas
_Query = this.mdIQueries.Area.GetAreaMapByAreaNIds(this._AreaNIds, false);
DataTable dtAreaMap = this.ExecuteInvariantDataTable(_Query);
//Get Distinct LayerNIds
string[] ColumnArray = new string[1];
ColumnArray[0] = Area_Map.LayerNId;
DistinctElementTable = dtAreaMap.DefaultView.ToTable(true, ColumnArray);
DistinctNIds = new string[DistinctElementTable.Rows.Count];
for (int i = 0; i < DistinctElementTable.Rows.Count; i++)
{
DistinctNIds[i] = DistinctElementTable.Rows[i][ColumnArray[0]].ToString();
}
//Get Metadata for LayerNIds along with associated AreaNIds
DataTable dtMetadata = GetInvariantDataTable();
if (DistinctNIds.Length > 0)
{
//Join to Associate AreaNId and LayerNId
DataSet dsParentChild = GetInvariantDataSet();
// Add AreaMap Table
dtMetadata = dtAreaMap.Copy(); // Since it might be part if a relationship, therefore adding the COPY
dtMetadata.TableName = "Parent";
dtMetadata.Columns.Add(Area_Map_Metadata.MetadataText, typeof(string));
dsParentChild.Tables.Add(dtMetadata);
// Add AreaMapMetadata Table
_Query = this.mdIQueries.Area.GetAreaMapMetadata(string.Join(",", DistinctNIds));
DataTable dtAreaMapMetadata = this.ExecuteInvariantDataTable(_Query);
dtAreaMapMetadata.TableName = "Child";
// Set Primaty Key in the AreaMapMetadata
DataColumn[] PK = new DataColumn[1];
PK[0] = dtAreaMapMetadata.Columns[Area_Map_Metadata.LayerNId];
dtAreaMapMetadata.PrimaryKey = PK;
dsParentChild.Tables.Add(dtAreaMapMetadata);
// Define relationship
dsParentChild.Relations.Add("RelAreaMap", dsParentChild.Tables[1].Columns[Area_Map.LayerNId], dsParentChild.Tables[0].Columns[Area_Map_Metadata.LayerNId], false);
dsParentChild.Tables[0].Columns[Area_Map_Metadata.MetadataText].Expression = "Parent(RelAreaMap)." + Area_Map_Metadata.MetadataText;
dsParentChild.AcceptChanges();
}
dvMetadataInfo = dtMetadata.DefaultView; //(AreaNId-LayerNId-MetadataText)
//Set Join column and Metadata Column name
JoinColumnName = Area.AreaNId;
MetadataColumnName = Area_Map_Metadata.MetadataText;
// Get distinct area nids
DistinctNIds = this._AreaNIds.Split(",".ToCharArray(), System.StringSplitOptions.RemoveEmptyEntries);
// Set metadata table
ElementMetadataTable = this._MetadataArea;
break;
case MetadataElementType.Source:
// Set Category Type
CategoryType = DIQueries.MetadataElementTypeText[MetadataElementType.Source];
MetadataPrefix = UserPreference.UserPreference.DataviewPreference.MetadataSource;
// Get Metadata fields from userPreference
arrSelectedMetadataFields = userPreference.DataView.MetadataSourceField.Split(",".ToCharArray(), System.StringSplitOptions.RemoveEmptyEntries);
// Set Mask file path
MaskFilePath = Path.Combine(this.msMaskFolder, MASK_FILE_SOURCE);
//Set Join column and Metadata Column name
JoinColumnName = IndicatorClassifications.ICNId;
MetadataColumnName = IndicatorClassifications.ICInfo;
// Get distinct source nids
DistinctNIds = new string[this._Sources.Rows.Count];
for (int i = 0; i < this._Sources.Rows.Count; i++)
{
DistinctNIds[i] = this._Sources.Rows[i][JoinColumnName].ToString();
}
// Get Metadata Info for all sources
_Query = this.mdIQueries.IndicatorClassification.GetIC(FilterFieldType.NId, string.Join(",", DistinctNIds), ICType.Source, FieldSelection.Heavy);
dvMetadataInfo = this.ExecuteInvariantDataTable(_Query).DefaultView;
// Set metadata table
ElementMetadataTable = this._MetadataSource;
break;
}
// Check for existence of indicator mask file
if (File.Exists(MaskFilePath))
{
// Try loading indicator mask file
try
{
MaskFileDocument.Load(MaskFilePath);
}
catch (Exception)
{
//TODO do not proceed further
}
}
// Add Join columns to Metadata table
ElementMetadataTable.Columns.Clear();
ElementMetadataTable.Columns.Add(JoinColumnName, typeof(int));
if (this._CensusInfoFeature)
{
_Query = this.mdIQueries.Metadata_Category.GetMetadataCategories(FilterFieldType.Type, CategoryType);
DataView dvMetadataCategory = this.ExecuteInvariantDataTable(_Query).DefaultView;
for (int j = 0; j < arrSelectedMetadataFields.Length; j++)
{
// Add Metadata columns to Metadata table
ElementMetadataTable.Columns.Add(arrSelectedMetadataFields[j], typeof(string));
ElementMetadataTable.Columns[arrSelectedMetadataFields[j]].DefaultValue = string.Empty;
// For each distinct Element
for (int i = 0; i < DistinctNIds.Length; i++)
{
DataRow drMetadata;
if (j == 0)
{
// Create data row for each Element (Indicator / Area / Source) only once
drMetadata = ElementMetadataTable.NewRow();
drMetadata[JoinColumnName] = DistinctNIds[i];
}
else
{
// If data row alreday exists simply update the desired field
drMetadata = ElementMetadataTable.Select(JoinColumnName + " = " + DistinctNIds[i])[0];
}
if (dvMetadataInfo != null && dvMetadataInfo.Table.Columns.Contains(JoinColumnName))
{
dvMetadataInfo.RowFilter = JoinColumnName + " = " + DistinctNIds[i];
// Check for valid xml content
if (dvMetadataInfo.Count > 0)
{
MetadataXml = dvMetadataInfo[0][MetadataColumnName].ToString();
if (MetadataXml != null && MetadataXml.Trim().Length > 0)
{
try // Try loading metadata xml
{
//Get all FLD_VAL contents into a Nodelist
XmlDocument xmlDoc = new XmlDocument();
xmlDoc.LoadXml(dvMetadataInfo[0][MetadataColumnName].ToString());
dvMetadataCategory.RowFilter = Metadata_Category.CategoryNId + " = " + arrSelectedMetadataFields[j].Replace(MetadataPrefix, "");
if (dvMetadataCategory != null && dvMetadataCategory.Count > 0)
{
XmlNode CategoryXmlNode = xmlDoc.SelectSingleNode("//Category[@name='" + dvMetadataCategory[0][Metadata_Category.CategoryName].ToString() + "']");
string ParaNodeText = string.Empty;
if (!String.IsNullOrEmpty(CategoryXmlNode.InnerText))
{
// Replace Para defining delimiter by new line character
ParaNodeText = CategoryXmlNode.InnerText.Replace(DevInfo.Lib.DI_LibBAL.DA.DML.MetadataManagerConstants.ReplaceableSymbol, Environment.NewLine);
}
// get category value from xml (from <para> tags)
//foreach (XmlNode ParaNode in CategoryXmlNode.ChildNodes)
//{
// // get para node text
// // add para node into concatenated category value
// if (!string.IsNullOrEmpty(ParaNodeText))
// {
// ParaNodeText += Microsoft.VisualBasic.ControlChars.NewLine ;
// }
// ParaNodeText += ParaNode.InnerText.Trim();
//}
// Based on position information (index) get Metadata content
drMetadata[arrSelectedMetadataFields[j]] = ParaNodeText;
}
}
catch (Exception)
{
//If unable to load metadata text then fields shall remain empty
}
}
}
dvMetadataInfo.RowFilter = string.Empty;
}
// Add element row only while looping for first field
if (j == 0)
{
ElementMetadataTable.Rows.Add(drMetadata);
}
}
}
}
else
{
// For each selected field for indicator / area (map) / source metadata
for (int j = 0; j < arrSelectedMetadataFields.Length; j++)
{
// Add Metadata columns to Metadata table
ElementMetadataTable.Columns.Add(arrSelectedMetadataFields[j], typeof(string));
ElementMetadataTable.Columns[arrSelectedMetadataFields[j]].DefaultValue = string.Empty;
// Get position information from mask file
MaskNodeList = MaskFileDocument.SelectNodes("*/*[ID='" + arrSelectedMetadataFields[j] + "']"); //root/Input1/ID
if (MaskNodeList.Count > 0)
{
if (MaskNodeList[0].SelectNodes("Position")[0] != null) //TODO Remove Hardcoding "Position"
{
MaskPositionText = MaskNodeList[0].SelectNodes("Position")[0].InnerText; //TODO Remove Hardcoding "Position"
MaskPositionArray = MaskPositionText.Split("/".ToCharArray(), System.StringSplitOptions.RemoveEmptyEntries);
if (MaskPositionArray.Length == 5) // 1/1/1/n/1 1/2/1/n/1
{
// For each distinct Element
for (int i = 0; i < DistinctNIds.Length; i++)
{
DataRow drMetadata;
if (j == 0)
{
// Create data row for each Element (Indicator / Area / Source) only once
drMetadata = ElementMetadataTable.NewRow();
drMetadata[JoinColumnName] = DistinctNIds[i];
}
else
{
// If data row alreday exists simply update the desired field
drMetadata = ElementMetadataTable.Select(JoinColumnName + " = " + DistinctNIds[i])[0];
}
if (dvMetadataInfo != null && dvMetadataInfo.Table.Columns.Contains(JoinColumnName))
{
dvMetadataInfo.RowFilter = JoinColumnName + " = " + DistinctNIds[i];
// Check for valid xml content
if (dvMetadataInfo.Count > 0)
{
MetadataXml = dvMetadataInfo[0][MetadataColumnName].ToString();
if (MetadataXml.Trim().Length > 0)
{
try // Try loading metadata xml
{
MetadataDocument.LoadXml(MetadataXml);
//Get all FLD_VAL contents into a Nodelist
MetadataFld_ValNodeList = MetadataDocument.SelectNodes("Indicator_Info/Row1/FLD_VAL"); //TODO Remove Hardcoding
// Based on position information (index) get Metadata content
//drMetadata[arrSelectedMetadataFields[j]] = "abc";
if (MetadataFld_ValNodeList.Count >= int.Parse(MaskPositionArray[1]))
{
drMetadata[arrSelectedMetadataFields[j]] = MetadataFld_ValNodeList[(int.Parse(MaskPositionArray[1]) - 1)].InnerText.Trim();
}
}
catch (Exception)
{
//If unable to load metadata text then fields shall remain empty
}
}
}
}
// Add element row only while looping for first field
if (j == 0)
{
ElementMetadataTable.Rows.Add(drMetadata);
}
}
}
}
}
}
}
return RetVal;
}
/// <summary>
/// Get concatinated path for desired area starting from root area
/// </summary>
/// <param name="areaNId">Area NId</param>
/// <param name="areaLevel">Area Level</param>
/// <param name="dIServerType">Server Type</param>
/// <returns></returns>
public string GetAreaChain(int areaNId, int areaLevel, DI_LibDAL.Connection.DIServerType dIServerType)
{
string RetVal = string.Empty;
string FromClause = string.Empty;
string WhereClause = string.Empty;
string SelfJoinTablePrefix = string.Empty;
int RequiredLevel = 1;
int LevelDifference = LevelDifference = Math.Abs(RequiredLevel - areaLevel);
if (LevelDifference > 0)
{
for (int i = 0; i <= LevelDifference; i++)
{
if (i == 0)
{
SelfJoinTablePrefix = "A" + i + "." + DIColumns.Area.AreaName;
FromClause = " FROM " + this.TablesName.Area + " AS A" + i;
WhereClause = " WHERE A" + LevelDifference + "." + DIColumns.Area.AreaNId + " = " + areaNId.ToString();
}
else
{
SelfJoinTablePrefix += Delimiter.TEXT_DELIMITER + "A" + i + "." + DIColumns.Area.AreaName;
FromClause += "," + this.TablesName.Area + " AS A" + i;
WhereClause += " AND A" + i + "." + DIColumns.Area.AreaParentNId + " = A" + (i - 1) + "." + DIColumns.Area.AreaNId;
}
}
RetVal = "SELECT DISTINCT ";
RetVal += DIQueries.SQL_GetConcatenatedValues(SelfJoinTablePrefix, Delimiter.TEXT_DELIMITER, " - ", dIServerType);
RetVal += " AS AreaChain ";
RetVal = RetVal + FromClause + WhereClause;
}
else
{
RetVal = "SELECT " + DIColumns.Area.AreaName + " FROM " + this.TablesName.Area + " WHERE " + DIColumns.Area.AreaNId + " = " + areaNId;
}
return RetVal;
}
/// <summary>
/// Build data table storing IUSNId and Source fields
/// </summary>
/// <param name="DIConnection"></param>
/// <param name="DIQueries"></param>
/// <returns></returns>
private bool BuildSource(DI_LibDAL.Connection.DIConnection DIConnection, DI_LibDAL.Queries.DIQueries DIQueries)
{
bool RetVal = true;
// If the Source Master Table has not been built then build it
if (this._Sources.Rows.Count == 0)
{
try
{
string _Query = string.Empty;
if (this.mdtIUSSourceNIDs.Rows.Count > 0)
{
string[] SourceDistinctColumns = new string[1];
SourceDistinctColumns[0] = DevInfo.Lib.DI_LibDAL.Queries.DIColumns.Data.SourceNId;
DataTable tmpSourceTable = this.mdtIUSSourceNIDs.DefaultView.ToTable(true, SourceDistinctColumns);
string sSources = string.Empty;
StringBuilder sbSources = new StringBuilder();
for (int i = 0; i < tmpSourceTable.Rows.Count; i++)
{
sbSources.Append("," + tmpSourceTable.Rows[i][Data.SourceNId]);
}
sSources = sbSources.ToString().Substring(1);
_Query = DIQueries.Source.GetSource(DevInfo.Lib.DI_LibDAL.Queries.FilterFieldType.NId, sSources, DevInfo.Lib.DI_LibDAL.Queries.FieldSelection.Light, false);
this._Sources = this.ExecuteInvariantDataTable(_Query);
this._Sources.TableName = "DataView_UserSelection_Sources";
// Add Selected column
this._Sources.Columns.Add(DataExpressionColumns.Selected, typeof(bool));
this.ClearSourceFilter(); // Set selected column value to true
}
RetVal = true;
}
catch (Exception)
{
RetVal = false;
throw;
}
}
else
{
RetVal = true;
}
return RetVal;
}
/// <summary>
/// Get dataview containing all columns related to Source, IUS, Indicator, Unit, SubgroupVal and Recommended source information
/// </summary>
/// <param name="DIConnection"></param>
/// <param name="DIQueries"></param>
/// <returns></returns>
private DataView GetIUSSource(DI_LibDAL.Connection.DIConnection DIConnection, DI_LibDAL.Queries.DIQueries DIQueries)
{
// If _IUSSource is precomputed, need not to recompute
if (this._IUSSource.Rows.Count > 0)
{
}
else
{
string IUSSourceFilterString = string.Empty;
string _Query = string.Empty;
// Indicator Unit Subgroup Source RecSrc Selected
// ----------------------------------------------------------------------------------------------------------------------
// Annual GDP growth rate Percent Total World Bank_WDI 2006 True True
// Average annual rate of inflation Percent Total World Bank_WDI 2006 False True
// We Have the following
// 1. _Indicators Data Table (All fields of Indicator by IUS)
// 2. _Units Data Table (All fields of Unit by IUS)
// 3. _Subgroups Data Table (All fields of Subgroups by IUS)
// 4. mdtIUSSourceNIDs Data Table (IUSNId and Source NId)
// STEP 1: Start building dtIUSSource on the basis of the dtIUSSourceNIDs data table
DataTable dtIUSSource = null;
dtIUSSource = this.mdtIUSSourceNIDs.Copy();
// Set column name to ICNId instead of SourceNId
dtIUSSource.Columns[Data.SourceNId].ColumnName = IndicatorClassifications.ICNId;
// Add RecommendedSource column
dtIUSSource.Columns.Add(IndicatorClassificationsIUS.RecommendedSource, typeof(bool));
dtIUSSource.Columns.Add(IndicatorClassificationsIUS.ICIUSOrder, typeof(int));
dtIUSSource.Columns.Add(IndicatorClassificationsIUS.ICIUSLabel, typeof(string));
// Add Selected column
dtIUSSource.Columns.Add(DataExpressionColumns.Selected, typeof(bool));
//---------------------------New Code Starts ------------------------------------------------
// Check for existanace of recommended source
// Fetch all records from IndicatorClassificationsIUS table where RecommendedSource = true
StringBuilder sbIUSSourceFilter = new StringBuilder();
foreach (DataRow dr in dtIUSSource.Rows)
{
sbIUSSourceFilter.Append(",'" + dr[Indicator_Unit_Subgroup.IUSNId] + "_" + dr[IndicatorClassifications.ICNId] + "'");
}
if (sbIUSSourceFilter.Length > 0)
{
_Query = DIQueries.Source.GetSource_Rec(sbIUSSourceFilter.ToString().Substring(1), mdIConnection.ConnectionStringParameters.ServerType);
DataView dtRecSource = this.ExecuteInvariantDataTable(_Query).DefaultView;
//DataTable
// Set recommended source value
if (dtRecSource != null && dtRecSource.Count > 0)
{
// If recommended source exists then set RecommendedSource value based on dtRecSource
for (int i = 0; i < dtIUSSource.Rows.Count; i++)
{
dtRecSource.RowFilter = IndicatorClassificationsIUS.ICNId + "=" + dtIUSSource.Rows[i][IndicatorClassificationsIUS.ICNId] + " AND " + IndicatorClassificationsIUS.IUSNId + "=" + dtIUSSource.Rows[i][IndicatorClassificationsIUS.IUSNId];
if (dtRecSource.Count > 0)
{
dtIUSSource.Rows[i][IndicatorClassificationsIUS.RecommendedSource] = dtRecSource[0][IndicatorClassificationsIUS.RecommendedSource];
dtIUSSource.Rows[i][IndicatorClassificationsIUS.ICIUSOrder] = dtRecSource[0][IndicatorClassificationsIUS.ICIUSOrder];
dtIUSSource.Rows[i][IndicatorClassificationsIUS.ICIUSLabel] = dtRecSource[0][IndicatorClassificationsIUS.ICIUSLabel];
}
else
{
dtIUSSource.Rows[i][IndicatorClassificationsIUS.RecommendedSource] = false;
}
dtIUSSource.Rows[i][DataExpressionColumns.Selected] = true;
}
}
else
{
// If no recommended source exists then set RecommendedSource value to false for all rows
for (int i = 0; i < dtIUSSource.Rows.Count; i++)
{
dtIUSSource.Rows[i][IndicatorClassificationsIUS.RecommendedSource] = false;
dtIUSSource.Rows[i][DataExpressionColumns.Selected] = true;
}
}
}
else
{
// If no recommended source exists then set RecommendedSource value to false for all rows
for (int i = 0; i < dtIUSSource.Rows.Count; i++)
{
dtIUSSource.Rows[i][IndicatorClassificationsIUS.RecommendedSource] = false;
dtIUSSource.Rows[i][DataExpressionColumns.Selected] = true;
}
}
if (dtIUSSource.Rows.Count > 0)
{
try
{
// STEP 2: We need to now build up the following IUS_Source Data Table IUS_Source with following columns
// ICNId (SourceNId) : IUSNId : Indicator Name : Indicator Global
// Unit Name : Unit Global : Subgroup Name : Subgroup Global
// Source Name : Source Global : Recommended Source
dtIUSSource.Columns.Add(Indicator.IndicatorName, typeof(string));
dtIUSSource.Columns.Add(Indicator.IndicatorGlobal, typeof(bool));
dtIUSSource.Columns.Add(Unit.UnitName, typeof(string));
dtIUSSource.Columns.Add(Unit.UnitGlobal, typeof(bool));
dtIUSSource.Columns.Add(DI_LibDAL.Queries.DIColumns.SubgroupVals.SubgroupVal, typeof(string));
dtIUSSource.Columns.Add(DI_LibDAL.Queries.DIColumns.SubgroupVals.SubgroupValGlobal, typeof(bool));
dtIUSSource.Columns.Add(IndicatorClassifications.ICName, typeof(string));
dtIUSSource.Columns.Add(IndicatorClassifications.ICGlobal, typeof(bool));
// STEP 3: Use ADO.NET Relations to build up the Data Table for dtIUSSource
DataSet _Base = GetInvariantDataSet();
// Add dtIUSSource Datatable into the base dataset
_Base.Tables.Add(dtIUSSource);
// STEP 3.1 - Add Indicator Name into dtIUSSource table using
// 1. dtIUSSource
// 2. _Indicators
// Add _Indicators Datatable into the base dataset
_Base.Tables.Add(this._IUSIndicator);
_Base.Relations.Add("RelIUS_For_Indicator", this._IUSIndicator.Columns[Indicator_Unit_Subgroup.IUSNId], dtIUSSource.Columns[Indicator_Unit_Subgroup.IUSNId], false);
// add expression on Indicator Column
dtIUSSource.Columns[Indicator.IndicatorName].Expression = "parent(RelIUS_For_Indicator)." + Indicator.IndicatorName;
dtIUSSource.Columns[Indicator.IndicatorGlobal].Expression = "parent(RelIUS_For_Indicator)." + Indicator.IndicatorGlobal;
_Base.AcceptChanges();
// STEP 3.2 - Add Unit Name into dtIUSSource table using
// 1. dtIUSSource
// 2. _Units
// Add _Units Datatable into the base dataset
_Base.Tables.Add(this._IUSUnit);
_Base.Relations.Add("RelIUS_For_Unit", this._IUSUnit.Columns[Indicator_Unit_Subgroup.IUSNId], dtIUSSource.Columns[Indicator_Unit_Subgroup.IUSNId], false);
// add expression on Unit Column
dtIUSSource.Columns[Unit.UnitName].Expression = "parent(RelIUS_For_Unit)." + Unit.UnitName;
dtIUSSource.Columns[Unit.UnitGlobal].Expression = "parent(RelIUS_For_Unit)." + Unit.UnitGlobal;
_Base.AcceptChanges();
// STEP 3.3 - Add Subgroup Name into dtIUSSource table using
// 1. dtIUSSource
// 2. _Subgroups
// Add _Subgroups Datatable into the base dataset
_Base.Tables.Add(this._IUSSubgroupVal);
_Base.Relations.Add("RelIUS_For_Subgroup", this._IUSSubgroupVal.Columns[Indicator_Unit_Subgroup.IUSNId], dtIUSSource.Columns[Indicator_Unit_Subgroup.IUSNId], false);
// add expression on Unit Column
dtIUSSource.Columns[DI_LibDAL.Queries.DIColumns.SubgroupVals.SubgroupVal].Expression = "parent(RelIUS_For_Subgroup)." + DI_LibDAL.Queries.DIColumns.SubgroupVals.SubgroupVal;
dtIUSSource.Columns[DI_LibDAL.Queries.DIColumns.SubgroupVals.SubgroupValGlobal].Expression = "parent(RelIUS_For_Subgroup)." + DI_LibDAL.Queries.DIColumns.SubgroupVals.SubgroupValGlobal;
_Base.AcceptChanges();
// STEP 3.4 - Add Source Name into dtIUSSource table using
// 1. dtIUSSource
// 2. _Sources
// Add _Sources Datatable into the base dataset
_Base.Tables.Add(this._Sources);
_Base.Relations.Add("RelIUS_For_Source", this._Sources.Columns[IndicatorClassificationsIUS.ICNId], dtIUSSource.Columns[IndicatorClassificationsIUS.ICNId], false);
// add expression on Unit Column
dtIUSSource.Columns[IndicatorClassifications.ICName].Expression = "parent(RelIUS_For_Source)." + IndicatorClassifications.ICName;
dtIUSSource.Columns[IndicatorClassifications.ICGlobal].Expression = "parent(RelIUS_For_Source)." + IndicatorClassifications.ICGlobal;
_Base.AcceptChanges();
// Set Return DataView
this._IUSSource = dtIUSSource;
}
catch (Exception)
{
this._IUSSource = dtIUSSource;
throw;
}
}
else
{
// -- Return empty DataView
this._IUSSource = dtIUSSource;
}
}
return this._IUSSource.DefaultView;
}
/// <summary>
/// Build data table storing IUSNId and Unit fields
/// </summary>
/// <param name="DIConnection"></param>
/// <param name="DIQueries"></param>
/// <returns></returns>
private bool BuildIUSUnit(DI_LibDAL.Connection.DIConnection DIConnection, DI_LibDAL.Queries.DIQueries DIQueries)
{
bool RetVal = true;
// If the Unit Master Table has not been built then build it
if (this._IUSUnit.Rows.Count == 0)
{
string _Query = string.Empty;
if (this._IUSNIds.Length > 0)
{
_Query = DIQueries.Unit.GetUnits(this._IUSNIds);
this._IUSUnit = this.ExecuteInvariantDataTable(_Query);
// ExecuteInvariantDataTable sets table name to "". Set Table name which is used while defining relationship
this._IUSUnit.TableName = "DataView_UserSelection_IUSUnit";
}
RetVal = true;
}
else
{
RetVal = true;
}
return RetVal;
}
/// <summary>
/// Build Metadata tables for Indicator, Area and Source
/// </summary>
/// <param name="userPreference"></param>
/// <param name="dIConnection"></param>
/// <param name="dIQueries"></param>
/// <returns></returns>
private void BuildMetadataTables(UserPreference.UserPreference userPreference, DI_LibDAL.Connection.DIConnection dIConnection, DI_LibDAL.Queries.DIQueries dIQueries)
{
if (userPreference.DataView.MetadataIndicatorField.Length > 0)
{
SetMetedataTables(MetadataElementType.Indicator, userPreference, dIConnection, dIQueries);
}
if (userPreference.DataView.MetadataAreaField.Length > 0)
{
SetMetedataTables(MetadataElementType.Area, userPreference, dIConnection, dIQueries);
}
if (userPreference.DataView.MetadataSourceField.Length > 0)
{
SetMetedataTables(MetadataElementType.Source, userPreference, dIConnection, dIQueries);
}
}
/// <summary>
/// Build data table storing IUSNId and Indicator fields
/// </summary>
/// <param name="DIConnection"></param>
/// <param name="DIQueries"></param>
/// <returns></returns>
private bool BuildIUSIndicator(DI_LibDAL.Connection.DIConnection DIConnection, DI_LibDAL.Queries.DIQueries DIQueries)
{
bool RetVal = true;
// If the Indicator Master Table has not been built then build it
if (this._IUSIndicator.Rows.Count == 0)
{
string _Query = string.Empty;
if (this._IUSNIds.Length > 0)
{
_Query = DIQueries.Indicators.GetIndicators(this._IUSNIds, false);
this._IUSIndicator = this.ExecuteInvariantDataTable(_Query);
this._IUSIndicator.TableName = "DataView_UserSelection_IUSIndicator";
}
RetVal = true;
}
else
{
RetVal = true;
}
return RetVal;
}
/// <summary>
/// Build data table storing IUSNId and SubgroupVal fields
/// </summary>
/// <param name="DIConnection"></param>
/// <param name="DIQueries"></param>
/// <returns></returns>
private bool BuildIUSSubgroup(DI_LibDAL.Connection.DIConnection DIConnection, DI_LibDAL.Queries.DIQueries DIQueries)
{
bool RetVal = true;
// If the Unit Master Table has not been built then build it
if (this._IUSSubgroupVal.Rows.Count == 0)
{
string _Query = string.Empty;
if (this._IUSNIds.Length > 0)
{
_Query = DIQueries.Subgroup.GetSubgroups(this._IUSNIds);
this._IUSSubgroupVal = this.ExecuteInvariantDataTable(_Query);
this._IUSSubgroupVal.TableName = "DataView_UserSelection_IUSSubgroupVal";
}
RetVal = true;
}
else
{
RetVal = true;
}
return RetVal;
}
/// <summary>
/// Set IC Field Information for columns selected by user
/// </summary>
/// <param name="userPreference"></param>
/// <param name="dIConnection"></param>
/// <param name="dIQueries"></param>
/// <param name="dtIUSNIDs">Datatable containing distinct IUSNIds for full dataview</param>
private void BuildIUSICInfoTable(UserPreference.UserPreference userPreference, DI_LibDAL.Connection.DIConnection dIConnection, DI_LibDAL.Queries.DIQueries dIQueries, DataTable dtIUSNIDs)
{
string sIUSICInfoTableName = this._IUSICInfo.TableName; //Preserve Table name
string sIUSICNIdInfoTableName = this._IUSICNIdInfo.TableName; //Preserve Table name
// Get Table with IUSNId column. Later add additional columns based on IC fields selected
this._IUSICInfo = dtIUSNIDs.Copy();
this._IUSICNIdInfo = dtIUSNIDs.Copy();
this._IUSICInfo.TableName = sIUSICInfoTableName; //Restore Table Name
this._IUSICNIdInfo.TableName = sIUSICNIdInfoTableName; //Restore Table Name
string[] arrSelectedICFields = null;
// This dictionary will store all distinct ICType(KEY) for which fields are selected and Maximum child level (Value) selected
System.Collections.Generic.Dictionary<ICType, int> ICMaxLevelCol = new Dictionary<ICType, int>();
System.Collections.Generic.Dictionary<ICType, string> ICGIdsCol = new Dictionary<ICType, string>();
string sICType = string.Empty;
int ICIndex = -1;
ICType ICType = ICType.Sector;
string sLevel = string.Empty;
string sCaption = string.Empty;
// Add selected IC columns to _IUSICInfo table
// Set language based caption
// Preserve information of distinct IC selected and max child level to traverse in ICMaxLevelCol
if (userPreference.DataView.ICFields.Length > 0)
{
arrSelectedICFields = userPreference.DataView.ICFields.Split(",".ToCharArray(), System.StringSplitOptions.RemoveEmptyEntries);
for (int i = 0; i < arrSelectedICFields.Length; i++)
{
sICType = arrSelectedICFields[i].Substring(4, 2);
ICIndex = DIQueries.ICTypeText.Values.IndexOf("'" + sICType + "'");
if (ICIndex > -1)
{
ICType = DIQueries.ICTypeText.Keys[ICIndex];
}
sLevel = arrSelectedICFields[i].Substring(7);
this._IUSICInfo.Columns.Add(arrSelectedICFields[i], typeof(string));
this._IUSICNIdInfo.Columns.Add(arrSelectedICFields[i], typeof(int));
this._IUSICInfo.Columns[arrSelectedICFields[i]].DefaultValue = string.Empty;
this._IUSICNIdInfo.Columns[arrSelectedICFields[i]].DefaultValue = -1;
if (ICMaxLevelCol.ContainsKey(ICType))
{
ICMaxLevelCol[ICType] = Math.Max(ICMaxLevelCol[ICType], int.Parse(sLevel));
}
else
{
ICMaxLevelCol.Add(ICType, int.Parse(sLevel));
}
if (ICGIdsCol.ContainsKey(ICType) == false)
{
string[] sICGIds = null;
switch (ICType)
{
case ICType.Sector:
sICGIds = userPreference.DataView.ICSectorGIds.Split(",".ToCharArray(), StringSplitOptions.RemoveEmptyEntries);
break;
case ICType.Goal:
sICGIds = userPreference.DataView.ICGoalGIds.Split(",".ToCharArray(), StringSplitOptions.RemoveEmptyEntries);
break;
case ICType.CF:
sICGIds = "-1".Split(",".ToCharArray(), StringSplitOptions.RemoveEmptyEntries);
break;
case ICType.Theme:
sICGIds = userPreference.DataView.ICThemeGIds.Split(",".ToCharArray(), StringSplitOptions.RemoveEmptyEntries);
break;
case ICType.Source:
sICGIds = userPreference.DataView.ICSourceGIds.Split(",".ToCharArray(), StringSplitOptions.RemoveEmptyEntries);
break;
case ICType.Institution:
sICGIds = userPreference.DataView.ICInstitutionalGIds.Split(",".ToCharArray(), StringSplitOptions.RemoveEmptyEntries);
break;
case ICType.Convention:
sICGIds = userPreference.DataView.ICConventionGIds.Split(",".ToCharArray(), StringSplitOptions.RemoveEmptyEntries);
break;
}
for (int j = 0; j < sICGIds.Length; j++)
{
sICGIds[j] = "'" + sICGIds[j] + "'";
}
ICGIdsCol.Add(ICType, string.Join(",", sICGIds));
}
}
}
string sCommaDelimitedICTypes = string.Empty;
string _Query = string.Empty;
// Get distinct comma delimited IC types for query
foreach (ICType sKey in ICMaxLevelCol.Keys)
{
sCommaDelimitedICTypes += "," + DIQueries.ICTypeText[sKey];
}
// Fetch records for IC based on IUSNIds and ICTypes
if (sCommaDelimitedICTypes.Length > 0)
{
sCommaDelimitedICTypes = sCommaDelimitedICTypes.Substring(1); //Remove extra ","
_Query = dIQueries.IndicatorClassification.GetICForIUSNId(FilterFieldType.Type, sCommaDelimitedICTypes, this._IUSNIds, FieldSelection.Light);
}
else
{
_Query = dIQueries.IndicatorClassification.GetICForIUSNId(FilterFieldType.None, "", this._IUSNIds, FieldSelection.Light);
}
DataView dvIC = this.ExecuteInvariantDataTable(_Query).DefaultView;
int iMaxLevel;
string sParentNIds = string.Empty;
string sColumnName = string.Empty;
//for each classification type (for which fields has been selected)
foreach (ICType sKey in ICMaxLevelCol.Keys)
{
ICType = sKey;
iMaxLevel = ICMaxLevelCol[sKey];
sParentNIds = "-1";
// for each level of current classification type
for (int i = 1; i <= iMaxLevel; i++)
{
if (!string.IsNullOrEmpty(sParentNIds))
{
// Build the column name based on field naming convention "CLS_SC_1", "CLS_GL_3"
sColumnName = "CLS_" + DIQueries.ICTypeText[ICType].Replace("'", "") + "_" + i.ToString();
// Check whether this column is part of _IUSICInfo table
if (this._IUSICInfo.Columns.Contains(sColumnName))
{
// for each IUS set _ICInfo[sColumnName] if its associated to Current IC record
for (int k = 0; k < this._IUSICInfo.Rows.Count; k++)
{
dvIC.RowFilter = IndicatorClassificationsIUS.IUSNId + " = " + this._IUSICInfo.Rows[k][IndicatorClassificationsIUS.IUSNId].ToString() + " AND " + IndicatorClassifications.ICParent_NId + " IN (" + sParentNIds + ") AND " + IndicatorClassifications.ICGId + " IN (" + ICGIdsCol[ICType] + ") AND " + IndicatorClassifications.ICType + " = " + DIQueries.ICTypeText[ICType];
if (dvIC.Count > 0)
{
this._IUSICInfo.Rows[k][sColumnName] = dvIC[0][IndicatorClassifications.ICName];
this._IUSICNIdInfo.Rows[k][sColumnName] = dvIC[0][IndicatorClassifications.ICNId];
}
else
{
this._IUSICInfo.Rows[k][sColumnName] = string.Empty;
}
}
}
// Set ParentNIds for next level
dvIC.RowFilter = IndicatorClassifications.ICParent_NId + " IN (" + sParentNIds + ") AND " + IndicatorClassifications.ICType + " = " + DIQueries.ICTypeText[ICType];
sParentNIds = string.Empty;
for (int j = 0; j < dvIC.Count; j++)
{
sParentNIds += "," + dvIC[j][IndicatorClassifications.ICNId];
}
// remove extra ","
if (sParentNIds.Length > 0)
{
sParentNIds = sParentNIds.Substring(1);
}
}
}
}
}
/// <summary>
/// Constructor for DIDataView builds up the primary information for getting paged data
/// </summary>
/// <param name="userPreference"></param>
/// <param name="dIConnection"></param>
/// <param name="dIQueries"></param>
/// <param name="maskFolder">Path of Mask Folder where mask files for Indicator, Area and Source metadata resides. Constants for mask file name are available within this class</param>
/// <param name="languageFilePath">Path of language file, used to set language based column captions</param>
public DIDataView(UserPreference.UserPreference userPreference, DI_LibDAL.Connection.DIConnection dIConnection, DI_LibDAL.Queries.DIQueries dIQueries, string maskFolder, string commentsDataNIds)
{
string _Query = string.Empty;
string[] DistinctColumns = new string[1];
// -- Connection details preserved for getting IUSSource (recommended source) later on demand
this.mdIConnection = dIConnection;
this.mdIQueries = dIQueries;
this.mUserPreference = userPreference;
this.mUserPreference.UserSelection.UserSelectionChangeEvent += UserSelectionChanged;
// Mask folder for Metadata
this.msMaskFolder = maskFolder;
this._PagingCompleted = false;
#region STEP 1: Get DataNIDs for the selected Indicator, Time and Area and Build the Master Tables
// -- STEP 1: Get DataNIDs for the selected Indicator, Time and Area
// -- STEP 1.1 - Build Query for the Data View - This query will return only the DataNIds on the basis of the selected I, A and T
// Query for Data NIDs
//We have to get data for records which are marked as deleted and still to be displayed at Data page
if (string.IsNullOrEmpty(commentsDataNIds))
{
_Query = dIQueries.Data.GetDataNIDByIUSTimePeriodAreaSource(userPreference.UserSelection, dIConnection.ConnectionStringParameters.ServerType, "");
}
else
{
_Query = dIQueries.Data.GetDataNIDByIUSTimePeriodAreaSource(userPreference.UserSelection, dIConnection.ConnectionStringParameters.ServerType, commentsDataNIds);
}
// -- STEP 1.2: Execute the Query
// -- This Data Table will have all the DataNIDs and wil be used in other operations to fetch data
DataTable dtData = this.ExecuteInvariantDataTable(_Query);
// -- STEP 1.3: Inititalize Data Tables
InitializeVariables();
// -- STEP 1.4: Creating the Data Tables for
// DataNID, IUSNId and IUS_SourceNId (These Datatables will be used in the DataView Page to fill the Data, Source, Unit and Subgroup Lists)
// Indicator (master table based on IUSNId)
// Unit (master table based on IUSNId)
// Subgroup (master table based on IUSNId)
// Source (master table based on IUS_SourceNId)
if (dtData != null && dtData.Rows.Count > 0)
{
// Distinct DataNId
DistinctColumns[0] = DevInfo.Lib.DI_LibDAL.Queries.DIColumns.Data.DataNId;
this._AllDataNIDs = dtData.DefaultView.ToTable(false, DistinctColumns);
this._FilteredDataNIds = this._AllDataNIDs.Copy();
StringBuilder sbDataNIDs = new StringBuilder();
for (int i = 0; i < this._AllDataNIDs.Rows.Count; i++)
{
sbDataNIDs.Append("," + this._AllDataNIDs.Rows[i][Data.DataNId]);
}
if (sbDataNIDs.Length > 0)
{
msAllDataNIDs = sbDataNIDs.ToString().Substring(1);
}
// Distinct IUSNID
DistinctColumns[0] = DevInfo.Lib.DI_LibDAL.Queries.DIColumns.Data.IUSNId;
DataTable dtIUSNIDs = GetInvariantDataTable();
dtIUSNIDs = dtData.DefaultView.ToTable(true, DistinctColumns);
StringBuilder sbIUSNIDs = new StringBuilder();
this._IUSNIds = string.Empty;
for (int i = 0; i < dtIUSNIDs.Rows.Count; i++)
{
sbIUSNIDs.Append("," + dtIUSNIDs.Rows[i][Data.IUSNId]);
}
if (sbIUSNIDs.Length > 0)
{
this._IUSNIds = sbIUSNIDs.ToString().Substring(1);
}
// Distinct AreaNIds
DistinctColumns[0] = Data.AreaNId;
DataTable dtAreaNIds = GetInvariantDataTable();
dtAreaNIds = dtData.DefaultView.ToTable(true, DistinctColumns);
StringBuilder sbAreaNIds = new StringBuilder();
this._AreaNIds = string.Empty;
for (int i = 0; i < dtAreaNIds.Rows.Count; i++)
{
sbAreaNIds.Append("," + dtAreaNIds.Rows[i][Data.AreaNId]);
}
if (sbAreaNIds.Length > 0)
{
this._AreaNIds = sbAreaNIds.ToString().Substring(1);
}
// Distinct TimePeriodNIds
DistinctColumns[0] = Data.TimePeriodNId;
DataTable dtTimePeriodNIds = GetInvariantDataTable();
dtTimePeriodNIds = dtData.DefaultView.ToTable(true, DistinctColumns);
StringBuilder sbTimePeriodNIds = new StringBuilder();
this._TimePeriodNIds = string.Empty;
for (int i = 0; i < dtTimePeriodNIds.Rows.Count; i++)
{
sbTimePeriodNIds.Append("," + dtTimePeriodNIds.Rows[i][Data.TimePeriodNId]);
}
if (sbTimePeriodNIds.Length > 0)
{
this._TimePeriodNIds = sbTimePeriodNIds.ToString().Substring(1);
}
// Distinct SourceNIDs
string[] SourceDistinctColumns = new string[2];
SourceDistinctColumns[0] = DevInfo.Lib.DI_LibDAL.Queries.DIColumns.Data.IUSNId;
SourceDistinctColumns[1] = DevInfo.Lib.DI_LibDAL.Queries.DIColumns.Data.SourceNId;
this.mdtIUSSourceNIDs = dtData.DefaultView.ToTable(true, SourceDistinctColumns);
// Build IUSIndicator table
this.BuildIUSIndicator(dIConnection, dIQueries);
// Build IUSUnit table
this.BuildIUSUnit(dIConnection, dIQueries);
// Build IUSSubgroup
this.BuildIUSSubgroup(dIConnection, dIQueries);
// Build Source
this.BuildSource(dIConnection, dIQueries);
//Build IUSSource
this.GetIUSSource(dIConnection, dIQueries);
// BuildMetadataTables
this.BuildMetadataTables(userPreference, dIConnection, dIQueries);
// BuildIUSICInfoTables
this.BuildIUSICInfoTable(userPreference, dIConnection, dIQueries, dtIUSNIDs);
// Set Paging Info
SetPagingInfo(this._AllDataNIDs.Rows.Count);
}
#endregion
}
public string GetPresentationData(UserSelection.UserSelection UserSelection, DI_LibDAL.Connection.DIServerType DIServerType)
{
string RetVal = string.Empty;
StringBuilder sbQuery = new StringBuilder();
string sDataViewBasicQuery = string.Empty;
// Get DataView Basic Query
sDataViewBasicQuery = GetGenericSelectFromWhereClause();
// Indicator Filters
if (UserSelection.IndicatorNIds.Length > 0)
{
if (UserSelection.ShowIUS == true)
{
sbQuery.Append(" AND IUS." + DIColumns.Indicator_Unit_Subgroup.IUSNId + " IN (" + UserSelection.IndicatorNIds + ")");
}
else
{
sbQuery.Append(" AND IUS." + DIColumns.Indicator_Unit_Subgroup.IndicatorNId + " IN (" + UserSelection.IndicatorNIds + ") ");
}
}
// Time Period Filters
if (UserSelection.TimePeriodNIds.Length > 0)
{
sbQuery.Append(" AND T." + DIColumns.Timeperiods.TimePeriodNId + " IN (" + UserSelection.TimePeriodNIds + ")");
}
// Area Filters
if (UserSelection.AreaNIds.Length > 0)
{
sbQuery.Append(" AND A." + DIColumns.Area.AreaNId + " IN (" + UserSelection.AreaNIds + ")");
}
// Dataview Filters
sbQuery.Append(UserSelection.DataViewFilters.SQL_GetDataViewFilters(DIServerType, false));
RetVal = sDataViewBasicQuery + sbQuery.ToString();
return RetVal;
}
/// <summary>
/// Gets records form data table based on User selection
/// </summary>
/// <param name="UserSelection">an instance of User selection object</param>
/// <param name="DIServerType">Server type required for database specific syntax like concat etc.</param>
/// <param name="dataNIds">
/// Comma delimited datanids which may be blank.
/// If datanid is provided then filter is applied on the basis datanids instead of indicatornid + timeperiodnids + areanids
/// </param>
/// <returns>Sql query text</returns>
public string GetDataNIDByIUSTimePeriodAreaSource(UserSelection.UserSelection UserSelection, DI_LibDAL.Connection.DIServerType DIServerType, string dataNIds)
{
string RetVal = string.Empty;
RetVal = GetDataIUSSourceNIDByUserSelection(UserSelection, FieldSelection.NId, DIServerType, dataNIds);
return RetVal;
}
/// <summary>
/// Gets records form data table based on User selection
/// </summary>
///<param name="UserSelection">an instance of User selection object</param>
/// <param name="fieldSelection">NId, Name, Light, Heavy</param>
/// <param name="DIServerType">Server type required for database specific syntax like concat etc.</param>
/// <param name="dataNIds">
/// Comma delimited datanids which may be blank.
/// If datanid is provided then filter is applied on the basis datanids instead of indicatornid + timeperiodnids + areanids
/// </param>
/// <returns></returns>
private string GetDataIUSSourceNIDByUserSelection(UserSelection.UserSelection UserSelection, FieldSelection fieldSelection, DI_LibDAL.Connection.DIServerType DIServerType, string dataNIds)
{
//
// --------- sDataNIDs (Length > 0 then apply Filters) ---------
//
// -- sDataNIDs will have comma seperated Data NIDs only when this function is being used to Apply Filters on the already created Data View
// -- In such a case sDataNIDs will hold all the DataNIDs of the created DataView
//
// -- 1. Will be used with Filters only
// -- 2. If sDataNIDs is not blank then it means that this function is being used to set Filters
// -- 3. TIME PERIOD Table - Will require a Check for MRD filter also - In this Case Time Period Table will be used
// -- 4. In this Case no Filters on Area, Time and Indicator
//
// -- SPECIAL CASE
// -- With too many DataNIDs, the Query might fail - Maximum limit set to 60,000 length
// -- In such a scenario, sDataNIDs filter will not be used and the Filters for Area, Time and Indicator will be used
// -- Also MRD will be applied if applicable
//
string RetVal = string.Empty;
string indicatorNIds = UserSelection.IndicatorNIds;
string timePeriodNIds = UserSelection.TimePeriodNIds;
string areaNIds = UserSelection.AreaNIds;
string sourceNIds = UserSelection.SourceNIds;
bool showIUS = UserSelection.ShowIUS;
bool UseIUSTable = false;
int iDataNIDLen = dataNIds.Length;
int iMaxDataNIdLen = 60000;
StringBuilder sbQuery = new StringBuilder();
// SELECT Clause
switch (fieldSelection)
{
case FieldSelection.NId:
sbQuery.Append("SELECT D." + DIColumns.Data.DataNId + ", D." + DIColumns.Data.IUSNId + ", D." + DIColumns.Data.TimePeriodNId + ", D." + DIColumns.Data.AreaNId + ", D." + DIColumns.Data.SourceNId);
// Use TimePeriod Table (Check comments on top - MRD case)
if (iDataNIDLen > 0 && UserSelection.DataViewFilters.MostRecentData)
{
// Check comments on top - MRD case
sbQuery.Append(", T." + DIColumns.Timeperiods.TimePeriod);
}
break;
case FieldSelection.Name:
sbQuery.Append("SELECT I." + DIColumns.Indicator.IndicatorName + ",U." + DIColumns.Unit.UnitName + ",SGV." + DIColumns.SubgroupVals.SubgroupVal + ",T." + DIColumns.Timeperiods.TimePeriod + ",A." + DIColumns.Area.AreaName + ",D." + DIColumns.Data.DataValue + ",IC." + DIColumns.IndicatorClassifications.ICName);
break;
case FieldSelection.Light:
sbQuery.Append("SELECT IUS." + DIColumns.Data.IUSNId + ",I." + DIColumns.Indicator.IndicatorNId + ",I." + DIColumns.Indicator.IndicatorName + ",U." + DIColumns.Unit.UnitNId + ",U." + DIColumns.Unit.UnitName + ",SGV." + DIColumns.SubgroupVals.SubgroupValNId + ",SGV." + DIColumns.SubgroupVals.SubgroupVal + ",T." + DIColumns.Timeperiods.TimePeriodNId + ",T." + DIColumns.Timeperiods.TimePeriod + ",A." + DIColumns.Area.AreaNId + ",A." + DIColumns.Area.AreaName + ",D." + DIColumns.Data.DataValue + ",IC." + DIColumns.IndicatorClassifications.ICNId + ",IC." + DIColumns.IndicatorClassifications.ICName);
break;
case FieldSelection.Heavy:
sbQuery.Append(this.GetGenericSelectClause());
break;
default:
break;
}
// FROM Clause
switch (fieldSelection)
{
case FieldSelection.NId:
sbQuery.Append(" FROM " + this.TablesName.Data + " AS D");
// Filters on - Unit and Subgroup
if (UserSelection.DataViewFilters.DeletedSubgroupNIds.Length > 0 || UserSelection.DataViewFilters.DeletedUnitNIds.Length > 0)
{
// -- DataView - If Filtered by Unit or Subgroup
// -- Use IUS Table in this case
UseIUSTable = true;
}
// Use IUS Table
if (UseIUSTable)
{
sbQuery.Append(", " + this.TablesName.IndicatorUnitSubgroup + " AS IUS ");
}
// Use Time Period Table
if (iDataNIDLen > 0 && UserSelection.DataViewFilters.MostRecentData)
{
// Check comments on top
sbQuery.Append(", " + this.TablesName.TimePeriod + " AS T ");
}
break;
case FieldSelection.Name:
case FieldSelection.Light:
case FieldSelection.Heavy:
sbQuery.Append(this.GetGenericFromClause());
break;
default:
break;
}
// WHERE Clause
switch (fieldSelection)
{
case FieldSelection.NId:
sbQuery.Append(" WHERE 1=1 ");
if (UseIUSTable)
{
// Join between IUS and Data Table
sbQuery.Append(" AND D." + DIColumns.Data.IUSNId + "=IUS." + DIColumns.Indicator_Unit_Subgroup.IUSNId);
}
// Use Time Period Table - MRD Filter
if (iDataNIDLen > 0 && UserSelection.DataViewFilters.MostRecentData)
{
// Check comments on top - MRD Filter
sbQuery.Append(" AND D." + DIColumns.Data.TimePeriodNId + "=T." + DIColumns.Timeperiods.TimePeriodNId);
}
break;
case FieldSelection.Name:
case FieldSelection.Light:
case FieldSelection.Heavy:
sbQuery.Append(this.GetGenericWhereClause());
break;
default:
break;
}
// Filters on Area, Indicator and Time Period
if (iDataNIDLen == 0 || iDataNIDLen > iMaxDataNIdLen)
{
// Area
if (areaNIds.Trim().Length > 0)
{
sbQuery.Append(" AND D." + DIColumns.Data.AreaNId + " IN (" + areaNIds + ")");
}
// Indicator
if (indicatorNIds.Trim().Length > 0)
{
if (showIUS == false)
{
sbQuery.Append(" AND D." + DIColumns.Data.IndicatorNId + " IN (" + indicatorNIds + ") ");
}
else
{
sbQuery.Append(" AND D." + DIColumns.Data.IUSNId + " IN (" + indicatorNIds + ")");
}
}
// Time Period
if (timePeriodNIds.Trim().Length > 0)
{
sbQuery.Append(" AND D." + DIColumns.Data.TimePeriodNId + " IN (" + timePeriodNIds + ")");
}
// Source
if (sourceNIds.Trim().Length > 0)
{
sbQuery.Append(" AND D." + DIColumns.Data.SourceNId + " IN (" + sourceNIds + ")");
}
}
else
{
// IF the length of sDataNIDs > 0 then apply filter on DataNIDs
sbQuery.Append(" AND D." + DIColumns.Data.DataNId + " IN (" + dataNIds + ")");
}
// ---------------------------------------------
// --------- Filters - DataView --------- Exclude Deleted Data Point filters as they will be part of main dataview
// ---------------------------------------------
if (UserSelection.DataViewFilters.MostRecentData == true)
{
// For MRD case Deleted records should be discarded ie filter clause for deleted record should be considered
sbQuery.Append(UserSelection.DataViewFilters.SQL_GetDataViewFilters(DIServerType, false));
}
else
{
// For other case Deleted records should be part of dataview ie filter clause for deleted record should be excluded
sbQuery.Append(UserSelection.DataViewFilters.SQL_GetDataViewFilters(DIServerType, true));
}
RetVal = sbQuery.ToString();
return RetVal;
}
