public override IFieldType CreateInstance(IFieldDefinition fieldDefinition)
{
var item = new FilterFieldType();
item.Init(fieldDefinition);
return(item);
}
/// <summary>
/// Returns instance of IndicatorInfo.
/// </summary>
/// <param name="filterClause"></param>
/// <param name="filterText"></param>
/// <param name="selectionType"></param>
/// <returns></returns>
public IndicatorInfo GetIndicatorInfo(FilterFieldType filterClause, string filterText, FieldSelection selectionType)
{
string Query = string.Empty;
IndicatorInfo RetVal = new IndicatorInfo();
DataTable IndicatorTable;
try
{
//get indicator information
Query = this.DBQueries.Indicators.GetIndicator(filterClause, filterText, selectionType);
IndicatorTable = this.DBConnection.ExecuteDataTable(Query);
//set indicator info
if (IndicatorTable != null)
{
if (IndicatorTable.Rows.Count > 0)
{
RetVal.GID = IndicatorTable.Rows[0][Indicator.IndicatorGId].ToString();
RetVal.Global = Convert.ToBoolean(IndicatorTable.Rows[0][Indicator.IndicatorGlobal]);
RetVal.Name = IndicatorTable.Rows[0][Indicator.IndicatorName].ToString();
RetVal.Nid = Convert.ToInt32(IndicatorTable.Rows[0][Indicator.IndicatorNId]);
if (selectionType == FieldSelection.Heavy)
{
RetVal.Info = IndicatorTable.Rows[0][Indicator.IndicatorInfo].ToString();
}
}
}
}
catch (Exception)
{
RetVal = null;
}
return(RetVal);
}
/// <summary>
/// Returns instance of MetadataCategoryInfo details for filerText.
/// </summary>
/// <param name="filterClause"></param>
/// <param name="filterText"></param>
/// <returns></returns>
public MetadataCategoryInfo GetMetadataCategoryInfo(FilterFieldType filterClause, string filterText)
{
string Query = string.Empty;
MetadataCategoryInfo RetVal = new MetadataCategoryInfo();
DataTable MetadataCategoryTable;
try
{
//get Metadata_Category information
Query = this.DBQueries.Metadata_Category.GetMetadataCategories(filterClause, filterText);
MetadataCategoryTable = this.DBConnection.ExecuteDataTable(Query);
//set Metadata Category info
if (MetadataCategoryTable != null)
{
if (MetadataCategoryTable.Rows.Count > 0)
{
RetVal.CategoryName = MetadataCategoryTable.Rows[0][Metadata_Category.CategoryName].ToString();
RetVal.CategoryType = Convert.ToString(MetadataCategoryTable.Rows[0][Metadata_Category.CategoryType]);
RetVal.CategoryOrder = Convert.ToInt32(MetadataCategoryTable.Rows[0][Metadata_Category.CategoryOrder]);
RetVal.CategoryNId = Convert.ToInt32(MetadataCategoryTable.Rows[0][Metadata_Category.CategoryNId]);
}
}
}
catch (Exception)
{
RetVal = null;
}
return(RetVal);
}
public string CheckDIUserExistence(FilterFieldType filterFieldType, string filterText, FieldSelection fieldSelection, bool editMode, int userNId, DIServerType serverType)
{
StringBuilder sbQuery = new StringBuilder();
string RetVal = string.Empty;
sbQuery.Append("SELECT " + DIUser.UserNId+ "," + DIUser.UserName + "," + DIUser.UserPWD + "," + DIUser.IsAdmin + "," + DIUser.ISloggedIn);
//- Get User table Name for Servertype
if (serverType == DIServerType.MySql)
sbQuery.Append(" FROM " + TablesName.DIUser + " ");
else
sbQuery.Append(" FROM [" + TablesName.DIUser + "] ");
// WHERE Clause
if (filterFieldType != FilterFieldType.None && filterText.Length > 0)
sbQuery.Append(" WHERE ");
if (filterText.Length > 0)
{
switch (filterFieldType)
{
case FilterFieldType.None:
break;
case FilterFieldType.NId:
sbQuery.Append(DIUser.UserNId + " IN (" + filterText + ")");
break;
case FilterFieldType.ParentNId:
break;
case FilterFieldType.Name:
sbQuery.Append(DIUser.UserName + " = ('" + filterText + "')");
break;
case FilterFieldType.Search:
sbQuery.Append(filterText);
break;
case FilterFieldType.NIdNotIn:
sbQuery.Append(DIUser.UserNId + " NOT IN (" + filterText + ")");
break;
case FilterFieldType.NameNotIn:
sbQuery.Append(DIUser.UserName + " <> ('" + filterText + "')");
break;
default:
break;
}
if (editMode)
sbQuery.Append(" AND User_NId <> " + userNId);
}
RetVal = sbQuery.ToString();
//--dispose
sbQuery = null;
return RetVal;
}
/// <summary>
/// Get NId, Name, GID, Global from Unit table for a given Filter Criteria
/// </summary>
/// <param name="filterFieldType">Applicable for NId,GId,Name,Search,NIdNotIn,NameNotIn</param>
/// <param name="filterText">For FilterFieldType "Search" FilterText should be FootNotes.FootNote LIKE '%Sample FootNote%'</param>
/// <returns></returns>
public string GetFootnote(FilterFieldType filterFieldType, string filterText)
{
string RetVal = string.Empty;
StringBuilder sbQuery = new StringBuilder();
sbQuery.Append("SELECT " + DIColumns.FootNotes.FootNoteNId + "," + DIColumns.FootNotes.FootNote + "," + DIColumns.FootNotes.FootNoteGId);
sbQuery.Append(" FROM " + this.TablesName.FootNote);
if (filterFieldType != FilterFieldType.None)
sbQuery.Append(" WHERE ");
if (filterText.Length > 0)
{
switch (filterFieldType)
{
case FilterFieldType.None:
break;
case FilterFieldType.NId:
sbQuery.Append(DIColumns.FootNotes.FootNoteNId + " IN (" + filterText + ")");
break;
case FilterFieldType.ParentNId:
break;
case FilterFieldType.ID:
break;
case FilterFieldType.GId:
sbQuery.Append(DIColumns.FootNotes.FootNoteGId + " IN (" + filterText + ")");
break;
case FilterFieldType.Name:
sbQuery.Append(DIColumns.FootNotes.FootNote + " IN (" + filterText + ")");
break;
case FilterFieldType.Type:
break;
case FilterFieldType.Search:
sbQuery.Append(filterText);
break;
case FilterFieldType.Global:
break;
case FilterFieldType.NIdNotIn:
sbQuery.Append(DIColumns.FootNotes.FootNoteNId + " NOT IN (" + filterText + ")");
break;
case FilterFieldType.NameNotIn:
sbQuery.Append(DIColumns.FootNotes.FootNote + " NOT IN (" + filterText + ")");
break;
default:
break;
}
}
RetVal = sbQuery.ToString();
return RetVal;
}
/// <summary>
/// return records form Metadata_Category table
/// </summary>
/// <param name="categoryNIds"></param>
/// <returns></returns>
public string GetMetadataCategories(FilterFieldType filterFieldType, string filterText)
{
string RetVal = string.Empty;
StringBuilder sbQuery = new StringBuilder();
//sbQuery.Append("SELECT " + DIColumns.Metadata_Category.CategoryNId + "," + DIColumns.Metadata_Category.CategoryName + "," + DIColumns.Metadata_Category.CategoryType + "," + DIColumns.Metadata_Category.CategoryOrder + " From " + this.TablesName.MetadataCategory);
sbQuery.Append("SELECT * From " + this.TablesName.MetadataCategory);
if (filterText.Length > 0)
{
sbQuery.Append(" WHERE 1=1 ");
switch (filterFieldType)
{
case FilterFieldType.None:
break;
case FilterFieldType.NId:
sbQuery.Append(" AND " + DIColumns.Metadata_Category.CategoryNId + " IN (" + filterText + ")");
break;
case FilterFieldType.Name:
sbQuery.Append(" AND " + DIColumns.Metadata_Category.CategoryName + " IN (" + DIQueries.RemoveQuotesForSqlQuery( filterText) + ")");
break;
case FilterFieldType.Type:
sbQuery.Append(" AND " + DIColumns.Metadata_Category.CategoryType + " IN (" + filterText + ")");
break;
case FilterFieldType.GId:
sbQuery.Append(" AND " + DIColumns.Metadata_Category.CategoryGId + "='" + DIQueries.RemoveQuotesForSqlQuery(filterText) + "'");
break;
case FilterFieldType.Search:
sbQuery.Append( " AND " + filterText);
break;
default:
break;
}
}
sbQuery.Append(" order by " + DIColumns.Metadata_Category.CategoryOrder);
RetVal = sbQuery.ToString();
return RetVal;
}
/// <summary>
/// Returns the instance of SubgroupInfo on the basis of Subgroup Nid
/// </summary>
/// <param name="sourceQuery"></param>
/// <param name="sourceDBConnection"></param>
/// <param name="subgroupNid"></param>
/// <returns></returns>
public static DI6SubgroupInfo GetSubgroupInfo(DIQueries queries, DIConnection dbConnection, FilterFieldType filterClause, string filterText)
{
string Query = string.Empty;
DI6SubgroupInfo RetVal = new DI6SubgroupInfo();
DataTable SubgroupTable;
DI6SubgroupTypeBuilder SGTypeBuilder;
try
{
Query = queries.Subgroup.GetSubgroup(filterClause, filterText);
SubgroupTable = dbConnection.ExecuteDataTable(Query);
//set Subgroup info
if (SubgroupTable != null)
{
if (SubgroupTable.Rows.Count > 0)
{
RetVal.GID = SubgroupTable.Rows[0][Subgroup.SubgroupGId].ToString();
RetVal.Global = Convert.ToBoolean(SubgroupTable.Rows[0][Subgroup.SubgroupGlobal]);
RetVal.Name = SubgroupTable.Rows[0][Subgroup.SubgroupName].ToString();
RetVal.Nid = Convert.ToInt32(SubgroupTable.Rows[0][Subgroup.SubgroupNId].ToString());
RetVal.Type = Convert.ToInt32(SubgroupTable.Rows[0][Subgroup.SubgroupType].ToString());
// Get subgrouptype info
if (RetVal.Type > 0)
{
SGTypeBuilder = new DI6SubgroupTypeBuilder(dbConnection, queries);
RetVal.DISubgroupType = SGTypeBuilder.GetSubgroupTypeInfoByNid(RetVal.Type);
}
}
}
}
catch (Exception)
{
RetVal = null;
}
return RetVal;
}
/// <summary>
/// Get the auto selected NId, Name, GID, Global from IUS - Indicator - Unit - SubgroupVal table for a given Filter Criteria
/// </summary>
/// <param name="filterFieldType">Applicable for NId</param>
/// <param name="filterText"></param>
/// <param name="fieldSelection">Use Light for NId + Name + GId + Global fields use heavy to include IndicatorInfo field</param>
/// <returns>Sql query string</returns>
public string GetAutoSelectIUS(FilterFieldType filterFieldType, string filterText, FieldSelection fieldSelection)
{
string RetVal = string.Empty;
StringBuilder sbQuery = new StringBuilder();
// SELECT Clause
sbQuery.Append("SELECT IUS." + DIColumns.Indicator_Unit_Subgroup.IUSNId + ",IUS." + DIColumns.Indicator_Unit_Subgroup.IndicatorNId + ",IUS." + DIColumns.Indicator_Unit_Subgroup.UnitNId + ",IUS." + DIColumns.Indicator_Unit_Subgroup.SubgroupValNId);
if (fieldSelection == FieldSelection.Light || fieldSelection == FieldSelection.Heavy)
{
sbQuery.Append(",I." + DIColumns.Indicator.IndicatorName + ",I." + DIColumns.Indicator.IndicatorGId + ",I." + DIColumns.Indicator.IndicatorGlobal);
sbQuery.Append(",U." + DIColumns.Unit.UnitName + ",U." + DIColumns.Unit.UnitGId + ",U." + DIColumns.Unit.UnitGlobal);
sbQuery.Append(",SGV." + DIColumns.SubgroupVals.SubgroupVal + ",SGV." + DIColumns.SubgroupVals.SubgroupValGId + ",SGV." + DIColumns.SubgroupVals.SubgroupValGlobal);
sbQuery.Append(",IUS." + DIColumns.Indicator_Unit_Subgroup.MinValue + ",IUS." + DIColumns.Indicator_Unit_Subgroup.MaxValue + ",SGV." + DIColumns.SubgroupVals.SubgroupValOrder);
}
if (fieldSelection == FieldSelection.Heavy)
{
sbQuery.Append(",I." + DIColumns.Indicator.IndicatorInfo);
}
// FROM Clause
sbQuery.Append(" FROM " + this.TablesName.IndicatorUnitSubgroup + " AS IUS");
if (fieldSelection == FieldSelection.Light || fieldSelection == FieldSelection.Heavy)
{
sbQuery.Append("," + this.TablesName.Indicator + " AS I");
sbQuery.Append("," + this.TablesName.Unit + " AS U");
sbQuery.Append("," + this.TablesName.SubgroupVals + " AS SGV");
}
// WHERE Clause
sbQuery.Append(" WHERE IUS." + DIColumns.Indicator_Unit_Subgroup.DataExist + " <> 0 ");
if (fieldSelection == FieldSelection.Light || fieldSelection == FieldSelection.Heavy)
{
sbQuery.Append(" AND IUS." + DIColumns.Indicator_Unit_Subgroup.IndicatorNId + " = I." + DIColumns.Indicator.IndicatorNId);
sbQuery.Append(" AND IUS." + DIColumns.Indicator_Unit_Subgroup.UnitNId + " = U." + DIColumns.Unit.UnitNId);
sbQuery.Append(" AND IUS." + DIColumns.Indicator_Unit_Subgroup.SubgroupValNId + " = SGV." + DIColumns.SubgroupVals.SubgroupValNId);
}
if (filterFieldType != FilterFieldType.None && filterText.Length > 0)
{
switch (filterFieldType)
{
case FilterFieldType.None:
break;
case FilterFieldType.NId:
sbQuery.Append(" AND " + DIColumns.Indicator_Unit_Subgroup.IUSNId + " IN (" + filterText + ")");
break;
case FilterFieldType.ParentNId:
break;
case FilterFieldType.ID:
break;
case FilterFieldType.GId:
break;
case FilterFieldType.Name:
break;
case FilterFieldType.Search:
sbQuery.Append(filterText);
break;
case FilterFieldType.Global:
break;
case FilterFieldType.NIdNotIn:
break;
case FilterFieldType.NameNotIn:
break;
default:
break;
}
}
// ORDER BY Clause
//if (fieldSelection == FieldSelection.Light || fieldSelection == FieldSelection.Heavy)
//{
// sbQuery.Append(" ORDER BY I." + DIColumns.Indicator.IndicatorName);
//}
RetVal = sbQuery.ToString();
return RetVal;
}
/// <summary>
/// Returns instance of IUSInfo.
/// </summary>
/// <param name="filterClause"></param>
/// <param name="filterText"></param>
/// <param name="selectionType"></param>
/// <returns></returns>
public IUSInfo GetIUSInfo(FilterFieldType filterClause, string filterText, FieldSelection selectionType)
{
string Query = string.Empty;
IUSInfo RetVal = new IUSInfo();
IndicatorInfo IndicatorObject = new IndicatorInfo();
UnitInfo UnitObject = new UnitInfo();
IndicatorBuilder IndicatorBuilderObj = null;
UnitBuilder UnitBuilderObj = null;
DI6SubgroupValBuilder SubgroupValBuilderObj = null;
DI6SubgroupValInfo SubgroupValObject = new DI6SubgroupValInfo();
int IndicatorNid = 0;
int UnitNid = 0;
int SGNid = 0;
int MinVal = 0;
int MaxVal = 0;
DataTable Table = null;
try
{
//get IUS information
Query = this.DBQueries.IUS.GetIUS(filterClause, filterText, selectionType);
Table = this.DBConnection.ExecuteDataTable(Query);
//set IUS info
if (Table != null)
{
if (Table.Rows.Count > 0)
{
MinVal = 0;
MaxVal = 0;
// initialize builder objects
IndicatorBuilderObj = new IndicatorBuilder(this.DBConnection, this.DBQueries);
UnitBuilderObj = new UnitBuilder(this.DBConnection, this.DBQueries);
SubgroupValBuilderObj = new DI6SubgroupValBuilder(this.DBConnection, this.DBQueries);
// set IUS properties
//-- set maximum value
if (!string.IsNullOrEmpty(Convert.ToString(Table.Rows[0][Indicator_Unit_Subgroup.MaxValue])))
{
MaxVal = Convert.ToInt32(Table.Rows[0][Indicator_Unit_Subgroup.MaxValue]);
}
//-- Set Minmum Value
if (!string.IsNullOrEmpty(Convert.ToString(Table.Rows[0][Indicator_Unit_Subgroup.MinValue])))
{
MinVal = Convert.ToInt32(Table.Rows[0][Indicator_Unit_Subgroup.MinValue]);
}
RetVal.Maximum = MaxVal; // Convert.ToInt32(Table.Rows[0][Indicator_Unit_Subgroup.MaxValue]);
RetVal.Minimum = MinVal; // Convert.ToInt32(Table.Rows[0][Indicator_Unit_Subgroup.MinValue]);
RetVal.Nid = Convert.ToInt32(Table.Rows[0][Indicator_Unit_Subgroup.IUSNId]);
// set indicator, unit and subgroup info
IndicatorNid = Convert.ToInt32(Table.Rows[0][Indicator_Unit_Subgroup.IndicatorNId]);
UnitNid = Convert.ToInt32(Table.Rows[0][Indicator_Unit_Subgroup.UnitNId]);
SGNid = Convert.ToInt32(Table.Rows[0][Indicator_Unit_Subgroup.SubgroupValNId]);
RetVal.IndicatorInfo = IndicatorBuilderObj.GetIndicatorInfo(FilterFieldType.NId, IndicatorNid.ToString(), FieldSelection.Light);
RetVal.UnitInfo = UnitBuilderObj.GetUnitInfo(FilterFieldType.NId, UnitNid.ToString());
RetVal.SubgroupValInfo = SubgroupValBuilderObj.GetSubgroupValInfo(FilterFieldType.NId, SGNid.ToString());
}
}
}
catch (Exception)
{
RetVal = null;
}
return RetVal;
}
/// <summary>
/// Get NId, Name from Area for a given Source and Filter Criteria
/// </summary>
/// <param name="filterFieldType"></param>
/// <param name="filterText"></param>
/// <returns></returns>
/// <remarks>Used in CCC Dashboard</remarks>
public string GetAreaBySource(FilterFieldType filterFieldType, string filterText)
{
string RetVal = string.Empty;
RetVal = "SELECT DISTINCT A." + DIColumns.Area.AreaNId + ", A." + DIColumns.Area.AreaName;
RetVal += " FROM " + this.TablesName.Area + " A, " + this.TablesName.Data + " D, " + this.TablesName.IndicatorClassifications + " IC";
RetVal += " WHERE A." + DIColumns.Area.AreaNId + " = D." + DIColumns.Data.AreaNId + " AND D." + DIColumns.Data.SourceNId + " = IC." + DIColumns.IndicatorClassifications.ICNId + " AND IC." + DIColumns.IndicatorClassifications.ICType + " = " + DIQueries.ICTypeText[ICType.Source];
if (filterText.Length > 0)
{
switch (filterFieldType)
{
case FilterFieldType.NId:
break;
case FilterFieldType.ParentNId:
break;
case FilterFieldType.ID:
break;
case FilterFieldType.GId:
break;
case FilterFieldType.Name:
break;
case FilterFieldType.Search:
RetVal += " AND IC." + DIColumns.IndicatorClassifications.ICName + " LIKE " + filterText;
break;
case FilterFieldType.Global:
break;
case FilterFieldType.NIdNotIn:
break;
case FilterFieldType.NameNotIn:
break;
default:
break;
}
}
return RetVal;
}
/// <summary>
/// Get NId, Name, GID, Global from IUS - Indicator - Unit - SubgroupVal table for a given Filter Criteria
/// </summary>
/// <param name="filterFieldType">Applicable for NId</param>
/// <param name="filterText"></param>
/// <param name="fieldSelection">Use Light for NId + Name + GId + Global fields use heavy to include IndicatorInfo field</param>
/// <returns>Sql query string</returns>
public string GetIUSByParentOrder(FilterFieldType filterFieldType, string filterText, ICType ICType, FieldSelection fieldSelection, string icNIds)
{
string RetVal = string.Empty;
StringBuilder sbQuery = new StringBuilder();
// SELECT Clause
sbQuery.Append("SELECT DISTINCT IUS." + DIColumns.Indicator_Unit_Subgroup.IUSNId + ",IUS." + DIColumns.Indicator_Unit_Subgroup.IndicatorNId + ",IUS." + DIColumns.Indicator_Unit_Subgroup.UnitNId + ",IUS." + DIColumns.Indicator_Unit_Subgroup.SubgroupValNId);
if (fieldSelection == FieldSelection.Light || fieldSelection == FieldSelection.Heavy)
{
sbQuery.Append(",I." + DIColumns.Indicator.IndicatorName + ",I." + DIColumns.Indicator.IndicatorGId + ",I." + DIColumns.Indicator.IndicatorGlobal);
sbQuery.Append(",U." + DIColumns.Unit.UnitName + ",U." + DIColumns.Unit.UnitGId + ",U." + DIColumns.Unit.UnitGlobal);
sbQuery.Append(",SGV." + DIColumns.SubgroupVals.SubgroupVal + ",SGV." + DIColumns.SubgroupVals.SubgroupValGId + ",SGV." + DIColumns.SubgroupVals.SubgroupValGlobal);
sbQuery.Append(",IUS." + DIColumns.Indicator_Unit_Subgroup.MinValue + ",IUS." + DIColumns.Indicator_Unit_Subgroup.MaxValue);
sbQuery.Append(",ICIUS." + DIColumns.IndicatorClassificationsIUS.ICIUSOrder);
}
if (fieldSelection == FieldSelection.Heavy)
{
sbQuery.Append(",I." + DIColumns.Indicator.IndicatorInfo);
}
// FROM Clause
sbQuery.Append(" FROM " + this.TablesName.IndicatorUnitSubgroup + " AS IUS");
if (fieldSelection == FieldSelection.Light || fieldSelection == FieldSelection.Heavy)
{
sbQuery.Append("," + this.TablesName.Indicator + " AS I");
sbQuery.Append("," + this.TablesName.Unit + " AS U");
sbQuery.Append("," + this.TablesName.SubgroupVals + " AS SGV");
sbQuery.Append("," + this.TablesName.IndicatorClassifications + " AS IC");
sbQuery.Append("," + this.TablesName.IndicatorClassificationsIUS + " AS ICIUS");
}
// WHERE Clause
sbQuery.Append(" WHERE 1=1 ");
if (fieldSelection == FieldSelection.Light || fieldSelection == FieldSelection.Heavy)
{
sbQuery.Append(" AND IUS." + DIColumns.Indicator_Unit_Subgroup.IndicatorNId + " = I." + DIColumns.Indicator.IndicatorNId);
sbQuery.Append(" AND IUS." + DIColumns.Indicator_Unit_Subgroup.UnitNId + " = U." + DIColumns.Unit.UnitNId);
sbQuery.Append(" AND IUS." + DIColumns.Indicator_Unit_Subgroup.SubgroupValNId + " = SGV." + DIColumns.SubgroupVals.SubgroupValNId);
sbQuery.Append(" AND IUS." + DIColumns.Indicator_Unit_Subgroup.IUSNId + " = ICIUS." + DIColumns.IndicatorClassificationsIUS.IUSNId);
sbQuery.Append(" AND IC." + DIColumns.IndicatorClassifications.ICNId + " = ICIUS." + DIColumns.IndicatorClassificationsIUS.ICNId);
sbQuery.Append(" AND IC." + DIColumns.IndicatorClassifications.ICType + " = " + DIQueries.ICTypeText[ICType]);
if (!string.IsNullOrEmpty(icNIds))
{
sbQuery.Append(" AND IC." + DIColumns.IndicatorClassifications.ICParent_NId + " IN (" + icNIds + ")");
}
}
if (filterFieldType != FilterFieldType.None && filterText.Length > 0)
{
switch (filterFieldType)
{
case FilterFieldType.None:
break;
case FilterFieldType.NId:
sbQuery.Append(" AND IUS." + DIColumns.Indicator_Unit_Subgroup.IUSNId + " IN (" + filterText + ")");
break;
case FilterFieldType.ParentNId:
break;
case FilterFieldType.ID:
break;
case FilterFieldType.GId:
break;
case FilterFieldType.Name:
break;
case FilterFieldType.Search:
sbQuery.Append(filterText);
break;
case FilterFieldType.Global:
break;
case FilterFieldType.NIdNotIn:
break;
case FilterFieldType.NameNotIn:
break;
default:
break;
}
}
// ORDER BY Clause
if (fieldSelection == FieldSelection.Light || fieldSelection == FieldSelection.Heavy)
{
sbQuery.Append(" ORDER BY ICIUS." + DIColumns.IndicatorClassificationsIUS.ICIUSOrder);
}
RetVal = sbQuery.ToString();
return RetVal;
}
/// <summary>
/// Get IC Records for root parents (-1) for a given IC Type and Filter Criteria
/// </summary>
/// <param name="filterFieldType">None = get all top level Classifications with ParentNId=-1.
/// <para>Applicable for NId, Search, NameNotIn</para>
/// </param>
/// <param name="filterText"><para>blank will Filter only for ICParentNId=-1</para>
/// <para>For FilterFieldType "Search" include wild characters. e.g. '%UNSD%' or '%UNSD'</para>
/// </param>
/// <param name="classificationType"></param>
/// <returns>
/// <para>NId, Name, GID, Global</para>
/// </returns>
public string GetICTopParents(FilterFieldType filterFieldType, string filterText, ICType classificationType)
{
string RetVal = string.Empty;
StringBuilder sbQuery = new StringBuilder();
// SELECT Clause
sbQuery.Append("SELECT " + DIColumns.IndicatorClassifications.ICNId + "," + DIColumns.IndicatorClassifications.ICName + "," + DIColumns.IndicatorClassifications.ICGId + "," + DIColumns.IndicatorClassifications.ICGlobal);
// FROM Clause
sbQuery.Append(" FROM " + this.TablesName.IndicatorClassifications);
// WHERE Clause
sbQuery.Append(" WHERE " + DIColumns.IndicatorClassifications.ICParent_NId + " = -1 ");
sbQuery.Append(" AND " + DIColumns.IndicatorClassifications.ICType + "=" + DIQueries.ICTypeText[classificationType]);
if (filterText.Length > 0)
{
switch (filterFieldType)
{
case FilterFieldType.NId:
// Filter on NID
sbQuery.Append(" AND " + DIColumns.IndicatorClassifications.ICNId + " IN(" + filterText + ")");
break;
case FilterFieldType.GId:
break;
case FilterFieldType.Name:
sbQuery.Append(" AND " + DIColumns.IndicatorClassifications.ICName + " IN (" + filterText + ")");
break;
case FilterFieldType.Search:
sbQuery.Append(" AND " + DIColumns.IndicatorClassifications.ICName + " LIKE " + filterText);
break;
case FilterFieldType.Global:
break;
case FilterFieldType.NIdNotIn:
break;
case FilterFieldType.NameNotIn:
sbQuery.Append(" AND " + DIColumns.IndicatorClassifications.ICName + " NOT IN (" + filterText + ")");
break;
default:
break;
}
}
// ORDER BY Clause
sbQuery.Append(" ORDER BY " + DIColumns.IndicatorClassifications.ICName);
RetVal = sbQuery.ToString();
return RetVal;
}
/// <summary>
/// Returns the instance of SubgroupInfo on the basis of Subgroup Nid
/// </summary>
/// <param name="sourceQuery"></param>
/// <param name="sourceDBConnection"></param>
/// <param name="subgroupNid"></param>
/// <returns></returns>
public static DI6SubgroupInfo GetSubgroupInfo(DIQueries queries, DIConnection dbConnection, FilterFieldType filterClause, string filterText)
{
string Query = string.Empty;
DI6SubgroupInfo RetVal = new DI6SubgroupInfo();
DataTable SubgroupTable;
DI6SubgroupTypeBuilder SGTypeBuilder;
try
{
Query = queries.Subgroup.GetSubgroup(filterClause, filterText);
SubgroupTable = dbConnection.ExecuteDataTable(Query);
//set Subgroup info
if (SubgroupTable != null)
{
if (SubgroupTable.Rows.Count > 0)
{
RetVal.GID = SubgroupTable.Rows[0][Subgroup.SubgroupGId].ToString();
RetVal.Global = Convert.ToBoolean(SubgroupTable.Rows[0][Subgroup.SubgroupGlobal]);
RetVal.Name = SubgroupTable.Rows[0][Subgroup.SubgroupName].ToString();
RetVal.Nid = Convert.ToInt32(SubgroupTable.Rows[0][Subgroup.SubgroupNId].ToString());
RetVal.Type = Convert.ToInt32(SubgroupTable.Rows[0][Subgroup.SubgroupType].ToString());
// Get subgrouptype info
if (RetVal.Type > 0)
{
SGTypeBuilder = new DI6SubgroupTypeBuilder(dbConnection, queries);
RetVal.DISubgroupType = SGTypeBuilder.GetSubgroupTypeInfoByNid(RetVal.Type);
}
}
}
}
catch (Exception)
{
RetVal = null;
}
return(RetVal);
}
/// <summary>
/// Returns instance of SubgrouopInfo
/// </summary>
/// <param name="filterClause"></param>
/// <param name="filterText"></param>
/// <param name="selectionType"></param>
/// <returns></returns>
public DI6SubgroupInfo GetSubgroupInfo(FilterFieldType filterClause, string filterText)
{
return DI6SubgroupBuilder.GetSubgroupInfo(this.DBQueries, this.DBConnection, filterClause, filterText);
}
/// <summary>
/// Get Indicator Classification records for a given IC Type and IUSNId
/// </summary>
/// <param name="filterFieldType">Applicable for NId, GId, Name, Search, ParentNId, NIdNotIn, NameNotIn, Type</param>
/// <param name="filterText">
/// <para>For FilterFieldType "Search" include wild characters. e.g. '%UNSD%' or '%UNSD'</para>
/// </param>
/// <param name="IUSNIds">Comma delimited IUSNIds </param>
/// <param name="classificationType">ICType enum value</param>
/// <param name="fieldSelection">LIGHT: Returns - ICNId, ICParent_NId, ICName, ICGId, ICGlobal, ICType, IUSNId
/// <para>HEAVY: Returns - ICNId, ICParent_NId, ICName, ICGId, ICGlobal, ICType, ICInfo, IUSNId</para>
/// </param>
/// <returns>
/// </returns>
public string GetICForIUSNId(FilterFieldType filterFieldType, string filterText, string IUSNIds, ICType classificationType, FieldSelection fieldSelection)
{
string RetVal = string.Empty;
StringBuilder sbQuery = new StringBuilder();
sbQuery.Append(this.GetICIUSSelectFromClause(fieldSelection));
// WHERE Clause
sbQuery.Append(" WHERE ");
sbQuery.Append(" IC." + DIColumns.IndicatorClassifications.ICNId + " = ICIUS." + DIColumns.IndicatorClassificationsIUS.ICNId);
sbQuery.Append(" AND IC." + DIColumns.IndicatorClassifications.ICType + "=" + DIQueries.ICTypeText[classificationType]);
if (IUSNIds.Trim().Length > 0)
{
sbQuery.Append(" AND ICIUS." + DIColumns.IndicatorClassificationsIUS.IUSNId + " IN (" + IUSNIds + ")");
}
string WhereClause = this.GetICWhereClause(filterFieldType, filterText);
if (WhereClause.Length > 0)
{
sbQuery.Append(" AND " + WhereClause);
}
RetVal = sbQuery.ToString();
return RetVal;
}
/// <summary>
/// Get NId, ParentNId, Name, GID, Global,ID from Area for a given Filter Criteria
/// </summary>
/// <param name="filterFieldType">None = get all top level Classifications with ParentNId=-1.
/// <para>Applicable for NId, ParentNId, ID, GId, Search, NIdNotIn, NameNotIn, Level,Dataexist</para>
/// </param>
/// <param name="filterText"><para>blank will Filter only for ICParentNId=-1</para>
/// <para>For FilterFieldType "Search" include wild characters. e.g. '%Ind%' or '%Africa%'</para>
/// </param>
/// <returns>
/// </returns>
public string GetAreasWithDataExist(FilterFieldType filterFieldType, string filterText)
{
string RetVal = string.Empty;
StringBuilder sbQuery = new StringBuilder();
sbQuery.Append("SELECT " + DIColumns.Area.AreaNId + "," + DIColumns.Area.AreaParentNId + "," + DIColumns.Area.AreaName + "," + DIColumns.Area.AreaGId + "," + DIColumns.Area.AreaGlobal + "," + DIColumns.Area.AreaID + "," + DIColumns.Area.AreaBlock + "," + DIColumns.Area.AreaMap + "," + DIColumns.Area.AreaLevel + "," + DIColumns.Area.DataExist);
sbQuery.Append(" FROM " + this.TablesName.Area);
if (filterFieldType != FilterFieldType.None && filterText.Length > 0)
sbQuery.Append(" WHERE ");
if (!string.IsNullOrEmpty(filterText))
{
switch (filterFieldType)
{
case FilterFieldType.None:
break;
case FilterFieldType.NId:
sbQuery.Append(DIColumns.Area.AreaNId + " IN (" + filterText + ")");
break;
case FilterFieldType.ParentNId:
sbQuery.Append(DIColumns.Area.AreaParentNId + " IN (" + filterText + ")");
break;
case FilterFieldType.ID:
sbQuery.Append(DIColumns.Area.AreaID + " IN (" + filterText + ")");
break;
case FilterFieldType.GId:
sbQuery.Append(DIColumns.Area.AreaGId + " IN (" + filterText + ")");
break;
case FilterFieldType.Name:
sbQuery.Append(DIColumns.Area.AreaName + " IN (" + filterText + ")");
break;
case FilterFieldType.Search:
//sbQuery.Append( DIColumns.Area.AreaName + " LIKE " + FilterText );
sbQuery.Append(filterText);
break;
case FilterFieldType.Global:
break;
case FilterFieldType.NIdNotIn:
sbQuery.Append(DIColumns.Area.AreaNId + " NOT IN (" + filterText + ")");
break;
case FilterFieldType.NameNotIn:
sbQuery.Append(DIColumns.Area.AreaName + " NOT IN (" + filterText + ")");
break;
case FilterFieldType.Level:
sbQuery.Append(DIColumns.Area.AreaLevel + "=" + filterText);
break;
default:
break;
}
}
RetVal = sbQuery.ToString();
return RetVal;
}
/// <summary>
/// Update indicator info on the basis of Nid or GId.
/// </summary>
/// <param name="dataPrefix"></param>
/// <param name="languageCode"></param>
/// <param name="Info"></param>
/// <param name="filterType">only NId or GId</param>
/// <param name="filterString"></param>
/// <returns></returns>
public static string UpdateIndicatorInfo(string dataPrefix, string languageCode, string Info, FilterFieldType filterType, string filterString)
{
string RetVal = string.Empty;
RetVal = "UPDATE " + dataPrefix + Update.TableName + languageCode + " SET " + DIColumns.Indicator.IndicatorInfo + "='" + Info + "' " + " WHERE ";
switch (filterType)
{
case FilterFieldType.GId:
RetVal += DIColumns.Indicator.IndicatorGId + "='" + DIQueries.RemoveQuotesForSqlQuery(filterString) + "' ";
break;
case FilterFieldType.NId:
RetVal += DIColumns.Indicator.IndicatorNId + "=" + filterString;
break;
}
return RetVal;
}
/// <summary>
/// Get NId, ParentNId, Name, GID, Global,ID from Area for a given Filter Criteria
/// </summary>
/// <param name="filterFieldType">None - gets all records
/// <para>Applicable for NId, Name, Search, NIdNotIn, NameNotIn</para>
/// </param>
/// <param name="filterString">blank - gets all records
/// <para>For FilterFieldType "Search" include wild characters. e.g. '%Ind%' or '%Africa%'</para>
/// </param>
/// <param name="fieldSelection">Heavy will include metadata text</param>
/// <returns></returns>
public string GetAreaMapMetadata(FilterFieldType filterFieldType, string filterString, FieldSelection fieldSelection)
{
string RetVal = string.Empty;
StringBuilder sbQuery = new StringBuilder();
sbQuery.Append("SELECT " + DIColumns.Area_Map_Metadata.MetadataNId + "," + DIColumns.Area_Map_Metadata.LayerNId + "," + DIColumns.Area_Map_Metadata.LayerName);
//-- Include Metadata text only for heavy case
if (fieldSelection == FieldSelection.Heavy)
{
sbQuery.Append("," + DIColumns.Area_Map_Metadata.MetadataText);
}
sbQuery.Append(" FROM " + this.TablesName.AreaMapMetadata);
if (filterString.Length > 0 && filterFieldType != FilterFieldType.None)
{
sbQuery.Append(" WHERE ");
}
if (filterString.Length > 0)
{
switch (filterFieldType)
{
case FilterFieldType.None:
break;
case FilterFieldType.NId:
sbQuery.Append(DIColumns.Area_Map_Metadata.LayerNId + " IN (" + filterString + ")");
break;
case FilterFieldType.Name:
sbQuery.Append(DIColumns.Area_Map_Metadata.LayerName + " IN (" + filterString + ")");
break;
case FilterFieldType.Search:
sbQuery.Append(filterString);
break;
case FilterFieldType.NIdNotIn:
sbQuery.Append(DIColumns.Area_Map_Metadata.LayerNId + " NOT IN (" + filterString + ")");
break;
case FilterFieldType.NameNotIn:
sbQuery.Append(DIColumns.Area_Map_Metadata.LayerName + " NOT IN (" + filterString + ")");
break;
}
}
RetVal = sbQuery.ToString();
return RetVal;
}
/// <summary>
/// Returns sql query to get area map layer information with area
/// </summary>
/// <param name="filterType"></param>
/// <param name="filterString"></param>
/// <param name="fieldSelection"></param>
/// <returns></returns>
public string GetAreaMapLayerInfo(FilterFieldType filterType, string filterString, FieldSelection fieldSelection)
{
string RetVal = string.Empty;
RetVal = "SELECT A." + DIColumns.Area.AreaGId + " ";
if (fieldSelection == FieldSelection.Heavy)
{
RetVal += ", A." + DIColumns.Area.AreaID + ", A." + DIColumns.Area.AreaName + ", AM." + DIColumns.Area_Map_Layer.LayerNId
+ ", AM." + DIColumns.Area_Map.AreaMapNId + ", AM." + DIColumns.Area_Map.AreaNId + ", AM." + DIColumns.Area_Map.FeatureLayer + ", AM." + DIColumns.Area_Map.FeatureTypeNId + " ";
}
RetVal += ", MET." + DIColumns.Area_Map_Metadata.LayerName + " ";
if (fieldSelection == FieldSelection.Heavy)
{
RetVal += ", AML." + DIColumns.Area_Map_Layer.MetadataNId + ", AML." + DIColumns.Area_Map_Layer.UpdateTimestamp + ", MET." + DIColumns.Area_Map_Metadata.MetadataText + " ";
}
RetVal += " FROM ((" + this.TablesName.AreaMap + " AS AM INNER JOIN " + this.TablesName.AreaMapLayer + " AS AML "
+ " ON AM." + DIColumns.Area_Map.LayerNId + " = AML." + DIColumns.Area_Map_Layer.LayerNId + ") " +
" LEFT JOIN " + this.TablesName.AreaMapMetadata + " AS MET ON AML." + DIColumns.Area_Map_Layer.LayerNId + " = MET." + DIColumns.Area_Map_Metadata.LayerNId + ") "
+ " INNER JOIN " + this.TablesName.Area + " AS A ON AM." + DIColumns.Area_Map.AreaNId + "= A." + DIColumns.Area.AreaNId + " ";
// Where clause
RetVal += " WHERE 1=1 ";
if (filterType == FilterFieldType.NId)
{
RetVal += " AND A." + DIColumns.Area.AreaNId + " in(" + filterString + ")";
}
else if (filterType == FilterFieldType.LayerNid)
{
RetVal += " AND AM." + DIColumns.Area_Map.LayerNId + " in(" + filterString + ")";
}
else if (filterType == FilterFieldType.Level)
{
RetVal += " AND A." + DIColumns.Area.AreaLevel + " =" + filterString + " ";
}
else if (filterType == FilterFieldType.Search)
{
RetVal += " AND " + filterString + " ";
}
// Order By
RetVal += " ORDER BY AM.Layer_NId ";
return (RetVal);
}
/// <summary>
/// Get LevelNId, AreaLevel, AreaLevelName from AreaLevel for a given Filter Criteria
/// </summary>
/// <param name="filterFieldType">Use None for all levels. Use ID filter for AreaLevel
/// <para>Applicable for NId,ID(AreaLevel),Name,Search,NIdNotIn,NameNotIn</para>
/// </param>
/// <param name="filterText"><para>blank will get all AreaLevel</para>
/// <para>For FilterFieldType "Search" include wild characters. e.g. '%Ind%' or '%Africa%'</para>
/// </param>
/// <returns>
/// </returns>
public string GetAreaLevel(FilterFieldType filterFieldType, string filterText)
{
string RetVal = string.Empty;
StringBuilder sbQuery = new StringBuilder();
sbQuery.Append("SELECT " + DIColumns.Area_Level.LevelNId + "," + DIColumns.Area_Level.AreaLevel + "," + DIColumns.Area_Level.AreaLevelName);
sbQuery.Append(" FROM " + this.TablesName.AreaLevel);
if (filterFieldType != FilterFieldType.None && filterText.Length > 0)
{
sbQuery.Append(" WHERE ");
switch (filterFieldType)
{
case FilterFieldType.None:
break;
case FilterFieldType.NId:
sbQuery.Append(DIColumns.Area_Level.LevelNId + " IN (" + filterText + ")");
break;
case FilterFieldType.ParentNId:
break;
case FilterFieldType.ID:
sbQuery.Append(DIColumns.Area_Level.AreaLevel + " IN (" + filterText + ")");
break;
case FilterFieldType.GId:
break;
case FilterFieldType.Name:
sbQuery.Append(DIColumns.Area_Level.AreaLevelName + " IN (" + filterText + ")");
break;
case FilterFieldType.Search:
//sbQuery.Append( DIColumns.Area.AreaName + " LIKE " + FilterText );
sbQuery.Append(filterText);
break;
case FilterFieldType.Global:
break;
case FilterFieldType.NIdNotIn:
sbQuery.Append(DIColumns.Area_Level.LevelNId + " NOT IN (" + filterText + ")");
break;
case FilterFieldType.NameNotIn:
sbQuery.Append(DIColumns.Area_Level.AreaLevelName + " NOT IN (" + filterText + ")");
break;
case FilterFieldType.Level:
sbQuery.Append(DIColumns.Area_Level.AreaLevel + " IN (" + filterText + ")");
break;
default:
break;
}
}
RetVal = sbQuery.ToString();
return RetVal;
}
/// <summary>
/// Get IC Records for a given Filter Criteria and FieldSelection
/// </summary>
/// <param name="filterFieldType">
/// <para>Applicable for NId, GId, Name, Search, ParentNId, NIdNotIn, NameNotIn, Type</para>
/// </param>
/// <param name="filterText">
/// <para>For FilterFieldType "Search" include wild characters. e.g. '%UNSD%' or '%UNSD'</para>
/// </param>
/// <param name="fieldSelection">
/// <para>LIGHT: Returns - NId, ParentNId, Name, GID, Global</para>
/// <para>HEAVY: Returns - NId, ParentNId, Name, GID, Global, Info</para>
/// </param>
/// <returns></returns>
public string GetIC(FilterFieldType filterFieldType, string filterText, FieldSelection fieldSelection)
{
string RetVal = string.Empty;
StringBuilder sbQuery = new StringBuilder();
sbQuery.Append(this.GetICSelectFromClause(fieldSelection));
string WhereClause = this.GetICWhereClause(filterFieldType, filterText);
if (WhereClause.Length > 0)
{
sbQuery.Append(" WHERE " + WhereClause);
}
sbQuery.Append(" ORDER BY IC." + DIColumns.IndicatorClassifications.ICName);
RetVal = sbQuery.ToString();
return RetVal;
}
public string GetSource(FilterFieldType filterFieldType, string filterText, FieldSelection fieldSelection, bool onlyParent)
{
string RetVal = string.Empty;
StringBuilder sbQuery = new StringBuilder();
// SELECT clause
sbQuery.Append("SELECT " + DIColumns.IndicatorClassifications.ICNId); //FieldSelection.NId
if (fieldSelection == FieldSelection.Light || fieldSelection == FieldSelection.Heavy)
{
sbQuery.Append("," + DIColumns.IndicatorClassifications.ICName + "," + DIColumns.IndicatorClassifications.ICGId + "," + DIColumns.IndicatorClassifications.ICGlobal+","+ DIColumns.IndicatorClassifications.ICParent_NId );
}
if (fieldSelection == FieldSelection.Heavy)
{
sbQuery.Append("," + DIColumns.IndicatorClassifications.ICInfo);
}
// FROM Clause
sbQuery.Append(" FROM " + this.TablesName.IndicatorClassifications);
// WHERE Clause
sbQuery.Append(" WHERE " + DIColumns.IndicatorClassifications.ICType + " = " + DIQueries.ICTypeText[ICType.Source]);
if (filterFieldType != FilterFieldType.None && filterText.Length > 0)
{
sbQuery.Append(" AND ");
switch (filterFieldType)
{
case FilterFieldType.None:
break;
case FilterFieldType.NId:
sbQuery.Append(DIColumns.IndicatorClassifications.ICNId + " IN (" + filterText + ")");
break;
case FilterFieldType.ParentNId:
break;
case FilterFieldType.ID:
break;
case FilterFieldType.GId:
sbQuery.Append(DIColumns.IndicatorClassifications.ICGId + " IN (" + filterText + ")");
break;
case FilterFieldType.Name:
sbQuery.Append(DIColumns.IndicatorClassifications.ICName + " IN (" + filterText + ")");
break;
case FilterFieldType.Search:
sbQuery.Append(filterText);
break;
case FilterFieldType.Global:
break;
case FilterFieldType.NIdNotIn:
sbQuery.Append(DIColumns.IndicatorClassifications.ICNId + " NOT IN (" + filterText + ")");
break;
case FilterFieldType.NameNotIn:
sbQuery.Append(DIColumns.IndicatorClassifications.ICName + " NOT IN (" + filterText + ")");
break;
default:
break;
}
}
if (onlyParent)
{
sbQuery.Append(" AND " + DIColumns.IndicatorClassifications.ICParent_NId + " =-1 ");
}
// ORDER BY Clause
sbQuery.Append(" ORDER BY " + DIColumns.IndicatorClassifications.ICName);
RetVal = sbQuery.ToString();
return RetVal;
}
/// <summary>
/// Get IC Records for a given IC Type and Filter Criteria
/// </summary>
/// <param name="filterFieldType">
/// <para>Applicable for NId, GId, Name, Search, ParentNId, NIdNotIn, NameNotIn, Type</para>
/// </param>
/// <param name="filterText">
/// <para>For FilterFieldType "Search" include wild characters. e.g. '%UNSD%' or '%UNSD'</para>
/// </param>
/// <param name="classificationType"></param>
/// <param name="fieldSelection">LIGHT: Returns - NId, PArentNId, Name, GID, Global
/// <para>HEAVY: Returns - NId, PArentNId, Name, GID, Global, Info</para>
/// </param>
/// <returns>
/// </returns>
public string GetIC(FilterFieldType filterFieldType, string filterText, ICType classificationType, FieldSelection fieldSelection, bool includeISBNNature)
{
string RetVal = string.Empty;
StringBuilder sbQuery = new StringBuilder();
string WhereClause = string.Empty;
sbQuery.Append(this.GetICSelectFromClause(fieldSelection, includeISBNNature));
sbQuery.Append(" WHERE ");
sbQuery.Append("IC." + DIColumns.IndicatorClassifications.ICType + "=" + DIQueries.ICTypeText[classificationType]);
WhereClause = this.GetICWhereClause(filterFieldType, filterText);
if (WhereClause.Length > 0)
{
sbQuery.Append(" AND " + WhereClause);
}
if (classificationType == ICType.Source)
{
sbQuery.Append(" ORDER BY IC." + DIColumns.IndicatorClassifications.ICName);
}
else
{
sbQuery.Append(" ORDER BY IC." + DIColumns.IndicatorClassifications.ICOrder);
}
RetVal = sbQuery.ToString();
return RetVal;
}
/// <summary>
/// Returns sql query to get subgroup types
/// </summary>
/// <param name="filterType"></param>
/// <param name="filterString"></param>
/// <returns></returns>
public string GetSubgroupTypes(FilterFieldType filterType, string filterString)
{
StringBuilder RetVal = new StringBuilder();
RetVal.Append(this.GetSelectClause(false));
RetVal.Append(" FROM " + this.TablesName.SubgroupType);
if (!string.IsNullOrEmpty(filterString))
{
RetVal.Append(" WHERE 1=1 ");
switch (filterType)
{
case FilterFieldType.None:
break;
case FilterFieldType.NId:
RetVal.Append(" AND " + DIColumns.SubgroupTypes.SubgroupTypeNId + " IN (" + filterString + ")");
break;
case FilterFieldType.ParentNId:
break;
case FilterFieldType.ID:
break;
case FilterFieldType.GId:
RetVal.Append(" AND " + DIColumns.SubgroupTypes.SubgroupTypeGID + " IN (" + filterString + ")");
break;
case FilterFieldType.Name:
RetVal.Append(" AND " + DIColumns.SubgroupTypes.SubgroupTypeName + " IN (" + filterString + ")");
break;
case FilterFieldType.Type:
break;
case FilterFieldType.Search:
RetVal.Append(filterString);
break;
case FilterFieldType.Global:
break;
case FilterFieldType.NIdNotIn:
RetVal.Append(" AND " + DIColumns.SubgroupTypes.SubgroupTypeNId + " NOT IN (" + filterString + ")");
break;
case FilterFieldType.NameNotIn:
break;
case FilterFieldType.Level:
break;
case FilterFieldType.LayerNid:
break;
default:
break;
}
}
RetVal.Append(" ORDER BY " + DIColumns.SubgroupTypes.SubgroupTypeOrder + " ");
return RetVal.ToString();
}
/// <summary>
/// Get IC Records for a given Filter Criteria and FieldSelection
/// </summary>
/// <param name="filterFieldType">
/// <para>Applicable for NId, GId, Name, Search, ParentNId, NIdNotIn, NameNotIn, Type</para>
/// </param>
/// <param name="searchClause">
/// <para>For FilterFieldType "Search" include wild characters. e.g. '%UNSD%' or '%UNSD'</para>
/// </param>
/// <param name="fieldSelection">
/// <para>LIGHT: Returns - NId, ParentNId, Name, GID, Global</para>
/// <para>HEAVY: Returns - NId, ParentNId, Name, GID, Global, Info</para>
/// </param>
/// <returns></returns>
public string GetICForSearch(FilterFieldType filterFieldType, string searchClause, FieldSelection fieldSelection, bool includeISBNNature)
{
string RetVal = string.Empty;
StringBuilder sbQuery = new StringBuilder();
sbQuery.Append(this.GetICSelectFromClause(fieldSelection, includeISBNNature));
string WhereClause = searchClause;
if (WhereClause.Length > 0)
{
sbQuery.Append(" WHERE " + WhereClause);
}
sbQuery.Append(" ORDER BY IC." + DIColumns.IndicatorClassifications.ICName);
RetVal = sbQuery.ToString();
return RetVal;
}
/// <summary>
/// Get NId, Name, GID, Global from SubgroupVals table for a given Filter Criteria
/// </summary>
/// <param name="filterFieldType">Applicable for NId, Name, Search, NIdNotIn, NameNotIn</param>
/// <param name="filterText">For FilterFieldType "Search" FilterText should be SubgroupVals.SubgroupVal LIKE '%Rural%' or SubgroupVals.SubgroupVal LIKE '%Total 0-14 yr%'</param>
/// <returns></returns>
public string GetSubgroupVals(FilterFieldType filterFieldType, string filterText)
{
string RetVal = string.Empty;
StringBuilder sbQuery = new StringBuilder();
sbQuery.Append("SELECT " + DIColumns.SubgroupVals.SubgroupValNId + "," + DIColumns.SubgroupVals.SubgroupVal + "," + DIColumns.SubgroupVals.SubgroupValGId + "," + DIColumns.SubgroupVals.SubgroupValGlobal + "," + DIColumns.SubgroupVals.SubgroupValOrder);
sbQuery.Append(" FROM " + this.TablesName.SubgroupVals);
if (filterFieldType != FilterFieldType.None)
sbQuery.Append(" WHERE ");
if (filterText.Length > 0)
{
switch (filterFieldType)
{
case FilterFieldType.None:
break;
case FilterFieldType.NId:
sbQuery.Append(DIColumns.SubgroupVals.SubgroupValNId + " IN (" + filterText + ")");
break;
case FilterFieldType.ParentNId:
break;
case FilterFieldType.ID:
break;
case FilterFieldType.GId:
sbQuery.Append(DIColumns.SubgroupVals.SubgroupValGId + " IN (" + filterText + ")");
break;
case FilterFieldType.Name:
sbQuery.Append(DIColumns.SubgroupVals.SubgroupVal + " IN (" + filterText + ")");
break;
case FilterFieldType.Search:
sbQuery.Append(filterText);
break;
case FilterFieldType.Global:
break;
case FilterFieldType.NIdNotIn:
sbQuery.Append(DIColumns.SubgroupVals.SubgroupValNId + " NOT IN (" + filterText + ")");
break;
case FilterFieldType.NameNotIn:
sbQuery.Append(DIColumns.SubgroupVals.SubgroupVal + " NOT IN (" + filterText + ")");
break;
default:
break;
}
}
sbQuery.Append(" Order by " + DIColumns.SubgroupVals.SubgroupValOrder + " ");
RetVal = sbQuery.ToString();
return RetVal;
}
/// <summary>
/// Returns Where clause based on FilterFieldType and FilterText
/// </summary>
/// <param name="filterFieldType">
/// <para>Applicable for NId, GId, Name, Search, ParentNId, NIdNotIn, NameNotIn, Type</para>
/// </param>
/// <param name="filterText">
/// <para>For FilterFieldType "Search" include wild characters. e.g. '%UNSD%' or '%UNSD'</para>
/// </param>
/// <returns></returns>
private string GetICWhereClause(FilterFieldType filterFieldType, string filterText)
{
string RetVal = string.Empty;
if (filterText.Length > 0)
{
switch (filterFieldType)
{
case FilterFieldType.None:
break;
case FilterFieldType.NId:
RetVal = "IC." + DIColumns.IndicatorClassifications.ICNId + " IN (" + filterText + ")";
break;
case FilterFieldType.GId:
RetVal = "IC." + DIColumns.IndicatorClassifications.ICGId + " IN (" + filterText + ")";
break;
case FilterFieldType.Name:
RetVal = "IC." + DIColumns.IndicatorClassifications.ICName + " IN (" + filterText + ")";
break;
case FilterFieldType.Search:
RetVal = "IC." + DIColumns.IndicatorClassifications.ICName + " LIKE " + filterText;
break;
case FilterFieldType.ParentNId:
RetVal = "IC." + DIColumns.IndicatorClassifications.ICParent_NId + " IN (" + filterText + ")";
break;
case FilterFieldType.NIdNotIn:
RetVal = "IC." + DIColumns.IndicatorClassifications.ICNId + " NOT IN (" + filterText + ")";
break;
case FilterFieldType.NameNotIn:
RetVal = "IC." + DIColumns.IndicatorClassifications.ICName + " NOT IN (" + filterText + ")";
break;
case FilterFieldType.Type:
RetVal = "IC." + DIColumns.IndicatorClassifications.ICType + " IN (" + filterText + ")";
break;
default:
break;
}
}
return RetVal;
}
/// <summary>
/// Returns instance of SubgrouopInfo
/// </summary>
/// <param name="filterClause"></param>
/// <param name="filterText"></param>
/// <param name="selectionType"></param>
/// <returns></returns>
public DI6SubgroupInfo GetSubgroupInfo(FilterFieldType filterClause, string filterText)
{
return(DI6SubgroupBuilder.GetSubgroupInfo(this.DBQueries, this.DBConnection, filterClause, filterText));
}
public static string ConvertEnumToString(FilterFieldType fieldType)
{
FieldInfo fi = fieldType.GetType().GetField(fieldType.ToString());
if (fi != null)
{
var attributes = (DescriptionAttribute[])fi.GetCustomAttributes(typeof(DescriptionAttribute), false);
return ((attributes.Length > 0) && (!String.IsNullOrEmpty(attributes[0].Description))) ?
attributes[0].Description : fieldType.ToString();
}
return null;
}
public string GetArea(FilterFieldType filterFieldType, string filterText, OrderBy OrderBy)
{
string RetVal = string.Empty;
RetVal = this.GetArea(filterFieldType, filterText);
RetVal += " ";
switch (OrderBy)
{
case OrderBy.AreaId:
RetVal += "ORDER BY " + DIColumns.Area.AreaID;
break;
case OrderBy.AreaName:
RetVal += "ORDER BY " + DIColumns.Area.AreaName;
break;
case OrderBy.AreaNId:
RetVal += "ORDER BY " + DIColumns.Area.AreaNId;
break;
case OrderBy.AreaParentNId:
RetVal += "ORDER BY " + DIColumns.Area.AreaParentNId;
break;
default:
break;
}
return RetVal;
}
private string Get_Language_Specific_Query(CodelistTypes codelistType, FilterFieldType filterField, string filterText, string language)
{
string RetVal;
string[] IUSSplits;
DIQueries DIQueriesLanguage;
RetVal = string.Empty;
if ((filterField == FilterFieldType.GId || filterField == FilterFieldType.ID) && codelistType != CodelistTypes.IUS)
{
filterText = Constants.Apostophe + filterText + Constants.Apostophe;
}
// 1. Creating language specific DIQueries object.
DIQueriesLanguage = new DIQueries(this.DIQueries.DataPrefix, language);
switch (codelistType)
{
case CodelistTypes.Area:
RetVal = DIQueriesLanguage.Area.GetArea(filterField, filterText);
break;
case CodelistTypes.Indicator:
RetVal = DIQueriesLanguage.Indicators.GetIndicator(filterField, filterText, FieldSelection.Heavy);
break;
case CodelistTypes.Subgroups:
RetVal = DIQueriesLanguage.Subgroup.GetSubgroup(filterField, filterText);
break;
case CodelistTypes.SubgroupType:
RetVal = DIQueriesLanguage.SubgroupTypes.GetSubgroupTypes(filterField, filterText);
break;
case CodelistTypes.SubgroupVal:
RetVal = DIQueriesLanguage.SubgroupVals.GetSubgroupVals(filterField, filterText);
break;
case CodelistTypes.Unit:
RetVal = DIQueriesLanguage.Unit.GetUnit(filterField, filterText);
break;
case CodelistTypes.IUS:
IUSSplits = this.SplitString(filterText, Constants.AtTheRate);
RetVal = DIQueriesLanguage.IUS.GetIUSNIdsByGID(IUSSplits[0], IUSSplits[1], IUSSplits[2]);
break;
default:
break;
}
return RetVal;
}
private void Handle_All_Languages(CodeType Code, CodelistTypes codelistType, FilterFieldType filterField, string filterText, string nameColumn)
{
DataTable DtTable;
string Query, Language;
Query = string.Empty;
if (this.MultiLanguageHandlingRequired)
{
foreach (DataRow LanguageRow in this.DIConnection.DILanguages(this.DIQueries.DataPrefix).Rows)
{
Language = LanguageRow[DevInfo.Lib.DI_LibDAL.Queries.DIColumns.Language.LanguageCode].ToString();
if (Language != this.DIQueries.LanguageCode.Substring(1))
{
Query = this.Get_Language_Specific_Query(codelistType, filterField, filterText, Language);
DtTable = this.DIConnection.ExecuteDataTable(Query);
Add_Language_Specific_Details(Code, codelistType, DtTable, nameColumn, Language);
}
}
}
}
/// <summary>
/// Returns instance of UnitInfo.
/// </summary>
/// <param name="filterClause"></param>
/// <param name="filterText"></param>
/// <param name="selectionType"></param>
/// <returns></returns>
public UnitInfo GetUnitInfo(FilterFieldType filterClause, string filterText)
{
string Query = string.Empty;
UnitInfo RetVal = new UnitInfo();
DataTable UnitTable;
try
{
Query = this.DBQueries.Unit.GetUnit(filterClause, filterText);
UnitTable = this.DBConnection.ExecuteDataTable(Query);
//set unit info
if (UnitTable != null)
{
if (UnitTable.Rows.Count > 0)
{
RetVal.GID = UnitTable.Rows[0][Unit.UnitGId].ToString();
RetVal.Name = UnitTable.Rows[0][Unit.UnitName].ToString();
RetVal.Nid = Convert.ToInt32(UnitTable.Rows[0][Unit.UnitNId].ToString());
RetVal.Global = Convert.ToBoolean(UnitTable.Rows[0][Unit.UnitGlobal]);
}
}
}
catch (Exception)
{
RetVal = null;
}
return RetVal;
}
/// <summary>
/// Returns instance of IndicatorInfo.
/// </summary>
/// <param name="filterClause"></param>
/// <param name="filterText"></param>
/// <param name="selectionType"></param>
/// <returns></returns>
public IndicatorInfo GetIndicatorInfo(FilterFieldType filterClause, string filterText, FieldSelection selectionType)
{
string Query = string.Empty;
IndicatorInfo RetVal = new IndicatorInfo();
DataTable IndicatorTable;
try
{
//get indicator information
Query = this.DBQueries.Indicators.GetIndicator(filterClause, filterText, selectionType);
IndicatorTable = this.DBConnection.ExecuteDataTable(Query);
//set indicator info
if (IndicatorTable != null)
{
if (IndicatorTable.Rows.Count > 0)
{
RetVal.GID = IndicatorTable.Rows[0][Indicator.IndicatorGId].ToString();
RetVal.Global = Convert.ToBoolean(IndicatorTable.Rows[0][Indicator.IndicatorGlobal]);
RetVal.Name = IndicatorTable.Rows[0][Indicator.IndicatorName].ToString();
RetVal.Nid = Convert.ToInt32(IndicatorTable.Rows[0][Indicator.IndicatorNId]);
if (selectionType == FieldSelection.Heavy)
{
RetVal.Info = IndicatorTable.Rows[0][Indicator.IndicatorInfo].ToString();
}
}
}
}
catch (Exception)
{
RetVal = null;
}
return RetVal;
}
/// <summary>
/// Get NId, Name, GID, Global, Type from Subgroup table for a given Filter Criteria
/// </summary>
/// <param name="filterFieldType">Applicable for NId, Name, Type, Search, NIdNotIn, NameNotIn</param>
/// <param name="filterText">For FilterFieldType "Search" FilterText should be Subgroup.SubgroupName LIKE '%Rural%' or SubgroupVals.SubgroupVal LIKE '%Total%'</param>
/// <returns></returns>
public string GetSubgroup(FilterFieldType filterFieldType, string filterText)
{
string RetVal = string.Empty;
StringBuilder sbQuery = new StringBuilder();
sbQuery.Append("SELECT " + DIColumns.Subgroup.SubgroupNId + "," + DIColumns.Subgroup.SubgroupName + "," + DIColumns.Subgroup.SubgroupGId + "," + DIColumns.Subgroup.SubgroupGlobal + "," + DIColumns.Subgroup.SubgroupType + "," + DIColumns.Subgroup.SubgroupOrder);
sbQuery.Append(" FROM " + this.TablesName.Subgroup);
if (!string.IsNullOrEmpty(filterText) && filterFieldType != FilterFieldType.None)
sbQuery.Append(" WHERE ");
if (!string.IsNullOrEmpty(filterText))
{
switch (filterFieldType)
{
case FilterFieldType.None:
break;
case FilterFieldType.NId:
sbQuery.Append(DIColumns.Subgroup.SubgroupNId + " IN (" + filterText + ")");
break;
case FilterFieldType.ParentNId:
break;
case FilterFieldType.ID:
break;
case FilterFieldType.GId:
sbQuery.Append(DIColumns.Subgroup.SubgroupGId + " IN (" + filterText + ")");
break;
case FilterFieldType.Name:
sbQuery.Append(DIColumns.Subgroup.SubgroupName + " IN (" + filterText + ")");
break;
case FilterFieldType.Type:
sbQuery.Append(DIColumns.Subgroup.SubgroupType + " IN (" + filterText + ")");
break;
case FilterFieldType.Search:
sbQuery.Append(filterText);
break;
case FilterFieldType.Global:
break;
case FilterFieldType.NIdNotIn:
sbQuery.Append(DIColumns.Subgroup.SubgroupNId + " NOT IN (" + filterText + ")");
break;
case FilterFieldType.NameNotIn:
sbQuery.Append(DIColumns.Subgroup.SubgroupName + " NOT IN (" + filterText + ")");
break;
default:
break;
}
}
RetVal = sbQuery.ToString();
return RetVal;
}
/// <summary>
/// Returns instance of IUSInfo.
/// </summary>
/// <param name="filterClause"></param>
/// <param name="filterText"></param>
/// <param name="selectionType"></param>
/// <returns></returns>
public IUSInfo GetIUSInfo(FilterFieldType filterClause, string filterText, FieldSelection selectionType)
{
string Query = string.Empty;
IUSInfo RetVal = new IUSInfo();
IndicatorInfo IndicatorObject = new IndicatorInfo();
UnitInfo UnitObject = new UnitInfo();
IndicatorBuilder IndicatorBuilderObj = null;
UnitBuilder UnitBuilderObj = null;
DI6SubgroupValBuilder SubgroupValBuilderObj = null;
DI6SubgroupValInfo SubgroupValObject = new DI6SubgroupValInfo();
int IndicatorNid = 0;
int UnitNid = 0;
int SGNid = 0;
int MinVal = 0;
int MaxVal = 0;
DataTable Table = null;
try
{
//get IUS information
Query = this.DBQueries.IUS.GetIUS(filterClause, filterText, selectionType);
Table = this.DBConnection.ExecuteDataTable(Query);
//set IUS info
if (Table != null)
{
if (Table.Rows.Count > 0)
{
MinVal = 0;
MaxVal = 0;
// initialize builder objects
IndicatorBuilderObj = new IndicatorBuilder(this.DBConnection, this.DBQueries);
UnitBuilderObj = new UnitBuilder(this.DBConnection, this.DBQueries);
SubgroupValBuilderObj = new DI6SubgroupValBuilder(this.DBConnection, this.DBQueries);
// set IUS properties
//-- set maximum value
if (!string.IsNullOrEmpty(Convert.ToString(Table.Rows[0][Indicator_Unit_Subgroup.MaxValue])))
{
MaxVal = Convert.ToInt32(Table.Rows[0][Indicator_Unit_Subgroup.MaxValue]);
}
//-- Set Minmum Value
if (!string.IsNullOrEmpty(Convert.ToString(Table.Rows[0][Indicator_Unit_Subgroup.MinValue])))
{
MinVal = Convert.ToInt32(Table.Rows[0][Indicator_Unit_Subgroup.MinValue]);
}
RetVal.Maximum = MaxVal; // Convert.ToInt32(Table.Rows[0][Indicator_Unit_Subgroup.MaxValue]);
RetVal.Minimum = MinVal; // Convert.ToInt32(Table.Rows[0][Indicator_Unit_Subgroup.MinValue]);
RetVal.Nid = Convert.ToInt32(Table.Rows[0][Indicator_Unit_Subgroup.IUSNId]);
// set indicator, unit and subgroup info
IndicatorNid = Convert.ToInt32(Table.Rows[0][Indicator_Unit_Subgroup.IndicatorNId]);
UnitNid = Convert.ToInt32(Table.Rows[0][Indicator_Unit_Subgroup.UnitNId]);
SGNid = Convert.ToInt32(Table.Rows[0][Indicator_Unit_Subgroup.SubgroupValNId]);
RetVal.IndicatorInfo = IndicatorBuilderObj.GetIndicatorInfo(FilterFieldType.NId, IndicatorNid.ToString(), FieldSelection.Light);
RetVal.UnitInfo = UnitBuilderObj.GetUnitInfo(FilterFieldType.NId, UnitNid.ToString());
RetVal.SubgroupValInfo = SubgroupValBuilderObj.GetSubgroupValInfo(FilterFieldType.NId, SGNid.ToString());
}
}
}
catch (Exception)
{
RetVal = null;
}
return(RetVal);
}
