private void Serialize(FieldSelection field, StringBuilder builder)
{
if (field.Alias != null)
{
builder.Append(field.Alias);
builder.Append(": ");
}
builder.Append(field.Name);
if (field.Arguments?.Any() == true)
{
builder.Append('(');
var first = true;
foreach (var arg in field.Arguments)
{
if (!first)
{
builder.Append(comma);
}
builder.Append(arg.Name).Append(colon).Append(SerializeValue(arg.Value));
first = false;
}
builder.Append(')');
}
if (field.Selections.Any() == true)
{
SerializeSelections(field, builder);
}
}
public override FieldSelection BeginVisitFieldSelection(
FieldSelection selection)
{
Tracker.EnterFieldSelection?.Invoke(selection);
return(base.BeginVisitFieldSelection(selection));
}
public string Serialize(FieldSelection field)
{
StringBuilder builder = new StringBuilder();
Serialize(field, builder);
return(builder.ToString());
}
public static IReadOnlyDictionary <string, object?> CoerceArgumentValues(
ISchema schema,
ObjectType objectType,
FieldSelection field,
IReadOnlyDictionary <string, object> coercedVariableValues)
{
var coercedValues = new Dictionary <string, object?>();
var argumentValues = field.Arguments ?? new List <Language.Nodes.Argument>();
var fieldName = field.Name;
var argumentDefinitions = schema.GetField(objectType.Name, fieldName)
.Arguments;
if (argumentDefinitions == null)
{
return(coercedValues);
}
foreach (var argumentDefinitionPair in argumentDefinitions)
{
var argumentDefinition = argumentDefinitionPair.Value;
Name argumentName = argumentDefinitionPair.Key;
var argument = argumentValues.SingleOrDefault(a => a.Name == argumentName);
coercedValues[argumentName] = CoerceArgumentValue(
schema,
coercedVariableValues,
argumentName,
argumentDefinition,
argument);
}
return(coercedValues);
}
private FeatureData GetFeatureData(GetFeatures getFeatures, FieldSelection fieldSelection)
{
CheckIsFeatureLayer();
Features features = (Features)_service.Send(getFeatures);
FeatureData featureData = fieldSelection.CreateFeatureData();
featureData.LayerName = Name;
LoadFeatureData(features, featureData, fieldSelection);
if (features.FeatureCount.HasMore)
{
getFeatures.BeginRecord = 1;
do
{
getFeatures.BeginRecord += features.FeatureCount.Count;
features = (Features)_service.Send(getFeatures);
LoadFeatureData(features, featureData, fieldSelection);
}while (features.FeatureCount.HasMore);
}
return(featureData);
}
public static IReadOnlyDictionary <string, object?> CoerceArgumentValues(
ISchema schema,
ObjectDefinition objectDefinition,
FieldSelection field,
IReadOnlyDictionary <string, object?> coercedVariableValues)
{
var coercedValues = new Dictionary <string, object?>();
var argumentValues = field.Arguments ?? Arguments.None;
var fieldName = field.Name;
var argumentDefinitions = schema.GetRequiredField(objectDefinition.Name, fieldName)
.Arguments;
if (argumentDefinitions == null)
{
return(coercedValues);
}
foreach (var definition in argumentDefinitions)
{
var argumentDefinition = definition;
var argumentName = definition.Name;
var argument = argumentValues.SingleOrDefault(a => a.Name == argumentName);
coercedValues[argumentName] = CoerceArgumentValue(
schema,
coercedVariableValues,
argumentName,
argumentDefinition,
argument);
}
return(coercedValues);
}
public static object?Handle(
IExecutorContext context,
ObjectDefinition objectDefinition,
string fieldName,
TypeBase fieldType,
FieldSelection fieldSelection,
object?completedValue,
Exception error,
NodePath path)
{
if (error is not QueryExecutionException)
{
error = new QueryExecutionException(
"",
error,
path,
fieldSelection);
}
if (fieldType is NonNullType)
{
throw error;
}
context.AddError(error);
return(completedValue);
}
private static FieldSelection PageInfoSelection()
{
var result = new FieldSelection("pageInfo", null);
result.Selections.Add(new FieldSelection("hasNextPage", null));
result.Selections.Add(new FieldSelection("endCursor", null));
return(result);
}
public override FieldSelection EndVisitFieldSelection(
FieldSelection selection)
{
{
Tracker.LeaveFieldSelection?.Invoke(selection);
}
return(base.EndVisitFieldSelection(selection));
}
private void LoadFeatureTable(Features features, DataTable table, FieldSelection fieldSelection)
{
if (features.Count == 0)
{
return;
}
int[] fieldIndex = new int[fieldSelection.Fields.Count];
var select = features[0].Fields.Select((field, index) => new { field, index });
for (int i = 0; i < fieldSelection.Fields.Count; ++i)
{
var result = select.Where(x => String.Compare(x.field.Name, fieldSelection.Fields[i].Name, true) == 0).FirstOrDefault();
fieldIndex[i] = result == null ? -1 : result.index;
}
foreach (Feature feature in features)
{
DataRow row = table.NewRow();
for (int c = 0; c < fieldSelection.Fields.Count; ++c)
{
if (fieldIndex[c] >= 0)
{
string value = feature.Fields[fieldIndex[c]].Value;
if (!String.IsNullOrEmpty(value) || feature.Shape != null)
{
switch (fieldSelection.Fields[c].Type)
{
case CommonFieldType.Geometry:
if (feature.Shape.OgcGeometryType == OgcGeometryType.MultiPoint && fieldSelection.Fields[c].GeometryType == OgcGeometryType.Point)
{
row[c] = ((IMultiPoint)feature.Shape)[0];
}
else
{
row[c] = feature.Shape;
}
break;
case CommonFieldType.Date:
row[c] = new DateTime((Convert.ToInt64(value) * 10000) + 621355968000000000);
break;
default:
row[c] = Convert.ChangeType(value, table.Columns[c].DataType);
break;
}
}
}
}
table.Rows.Add(row);
}
}
public string GetAllIUSByIC(ICType ICType, string ICNId, 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(",ICIUS." + DIColumns.IndicatorClassifications.ICNId + ", ICIUS." + DIColumns.IndicatorClassificationsIUS.RecommendedSource);
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.IndicatorClassificationsIUS + " AS ICIUS");
sbQuery.Append("," + this.TablesName.IndicatorClassifications + " AS IC");
// 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 ICIUS." + DIColumns.IndicatorClassificationsIUS.ICNId + " = IC." + DIColumns.IndicatorClassifications.ICNId);
sbQuery.Append(" AND IC." + DIColumns.IndicatorClassifications.ICType + " = " + DIQueries.ICTypeText[ICType]);
if (ICNId.Length > 0)
{
sbQuery.Append(" AND IC." + DIColumns.IndicatorClassifications.ICNId + " IN (" + ICNId + ")");
}
// ORDER BY Clause
if (fieldSelection == FieldSelection.Light || fieldSelection == FieldSelection.Heavy)
{
sbQuery.Append(" ORDER BY I." + DIColumns.Indicator.IndicatorName);
}
RetVal = sbQuery.ToString();
return RetVal;
}
public ResolverContextFacts()
{
_objectType = "type Test";
_objectValue = null;
_field = "test: ID";
_selection = new FieldSelection(null, "test", null, null, null);
_schema = new SchemaBuilder()
.Add("type Query")
.Build(new SchemaBuildOptions()).Result;
}
public override DataTable GetFeatureTable(string fieldNames, string where)
{
CheckIsFeatureLayer();
FieldSelection fieldSelection = new FieldSelection(this, fieldNames);
QueryFilter queryFilter = new QueryFilter(fieldSelection.NamesToString(), where);
RecordSet recordSet = _service.MapServer.QueryFeatureData(DataFrame.Name, _mapLayerInfo.LayerID, queryFilter);
return(RecordSetToTable(recordSet, fieldSelection));
}
public ResolverContextFacts()
{
_objectType = new ObjectType("Test");
_objectValue = null;
_field = new Field(ScalarType.ID);
_selection = new FieldSelection(null, "test", null, null, null, null);
_schema = new SchemaBuilder()
.Query(out _)
.Build();
}
public void Generate()
{
_context.Register(_query);
GenerateEnumTypes();
var backlog = new Queue <FieldSelection>();
foreach (var operation in
_document.Definitions.OfType <OperationDefinitionNode>())
{
Path root = Path.New(operation.Name !.Value);
ObjectType operationType =
_schema.GetOperationType(operation.Operation);
ICodeDescriptor resultType =
GenerateOperationSelectionSet(
operationType, operation, root, backlog);
while (backlog.Any())
{
FieldSelection current = backlog.Dequeue();
Path path = current.Path.Append(current.ResponseName);
if (!current.Field.Type.NamedType().IsLeafType())
{
GenerateFieldSelectionSet(
operation, current.Field.Type,
current.Selection, path, backlog);
}
}
GenerateResultParserDescriptor(operation, resultType);
}
var clientDescriptor = new ClientDescriptor(
_context.ClientName,
_namespace,
_context.Descriptors.OfType <IOperationDescriptor>().ToList());
var servicesDescriptor = new ServicesDescriptor(
_context.ClientName + "ServiceCollectionExtensions",
_context.Namespace,
clientDescriptor,
_context.Descriptors.OfType <IInputClassDescriptor>().ToList(),
_context.Descriptors.OfType <IEnumDescriptor>().ToList(),
_context.Descriptors.OfType <IResultParserDescriptor>().ToList());
_context.Register(clientDescriptor);
_context.Register(servicesDescriptor);
FieldTypes = _context.FieldTypes;
Descriptors = _context.Descriptors;
}
private ICollection <ISelectionNode> ResolveSelections(IEnumerable <ISelection> selections, Dictionary <string, IFragment> fragements)
{
var list = new List <ISelectionNode>();
foreach (var selection in selections)
{
if (selection is InlineFragment inlineFragment)
{
var graphType = _graphTypeProvider.GetGraphType(inlineFragment.Type.Name);
var fragment = new Fragment(graphType);
foreach (var item in ResolveSelections(inlineFragment.SelectionSet.Selections, fragements))
{
fragment.AddSubSelection(item);
}
list.Add(fragment);
}
if (!(selection is Field field))
{
continue;
}
var fieldSelection = new FieldSelection(field.Name)
{
Alias = field.Alias
};
if (field.SelectionSet.Selections.FirstOrDefault() is FragmentSpread fragmentSpread)
{
var fragmentType = fragements[fragmentSpread.Name];
}
foreach (var directiveDefinition in field.Directives)
{
var directive = new Directive(directiveDefinition.Name);
foreach (var argument in directiveDefinition.Arguments)
{
directive.AddArgument(new NamedValueToken(argument.Name, argument.Value.Value, argument.Value is VariableReference));
}
fieldSelection.Directives.Add(directive);
}
foreach (var argument in field.Arguments)
{
fieldSelection.AddArgument(new NamedValueToken(argument.Name, argument.Value.Value, argument.Value is VariableReference));
}
var subSelections = ResolveSelections(field.SelectionSet.Selections, fragements);
foreach (var subSelection in subSelections)
{
fieldSelection.AddSubSelection(subSelection);
}
list.Add(fieldSelection);
}
return(list);
}
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;
}
public SelectionVisitorContext(
IResolverContext context,
ITypeConversion conversion,
FieldSelection fieldSelection,
SelectionMiddlewareContext selectionMiddlewareContext)
{
Conversion = conversion;
FieldSelection = fieldSelection;
SelectionContext = selectionMiddlewareContext;
_context = context;
_arguments = fieldSelection.CoerceArguments(context.Variables, conversion);
}
public override DataTable GetFeatureTable(string fieldNames, string where, IGeometry shape)
{
CheckIsFeatureLayer();
FieldSelection fieldSelection = new FieldSelection(this, fieldNames);
SpatialFilter spatialFilter = new SpatialFilter(shape, fieldSelection.NamesToString(), where);
spatialFilter.SpatialRel = esriSpatialRelEnum.esriSpatialRelIntersects;
RecordSet recordSet = _service.MapServer.QueryFeatureData(DataFrame.Name, _mapLayerInfo.LayerID, spatialFilter);
return(RecordSetToTable(recordSet, fieldSelection));
}
public override DataTable GetFeatureTable(string fieldNames, string where, IGeometry shape)
{
FieldSelection fieldSelection = new FieldSelection(this, fieldNames);
SpatialQuery query = new SpatialQuery(where, shape);
query.Subfields = fieldSelection.NamesToString(" ");
GetFeatures getFeatures = new GetFeatures(ID, query);
getFeatures.DataFrame = DataFrame.Name;
getFeatures.Geometry = fieldSelection.Fields.Any(lyr => lyr.Type == CommonFieldType.Geometry);
return(GetFeatureTable(getFeatures, fieldSelection));
}
private void VisitFieldSelection(FieldSelection node)
{
EnterNode(node);
Visit(node.Arguments);
Visit(node.Directives);
if (node.SelectionSet != null)
{
Visit(node.SelectionSet);
}
ExitNode(node);
}
private FeatureData RecordSetToFeatureData(RecordSet recordSet, FieldSelection fieldSelection)
{
int fieldCount = fieldSelection.Fields.Count;
int[] fieldIndex = new int[fieldCount];
var select = recordSet.Fields.FieldArray.Select((field, index) => new { field, index });
for (int i = 0; i < fieldCount; ++i)
{
var result = select.Where(x => String.Compare(x.field.Name, fieldSelection.Fields[i].Name, true) == 0).FirstOrDefault();
fieldIndex[i] = result == null ? -1 : result.index;
}
FeatureData featureData = fieldSelection.CreateFeatureData();
featureData.LayerName = Name;
featureData.Rows = new FeatureRow[recordSet.Records.Length];
for (int r = 0; r < recordSet.Records.Length; ++r)
{
FeatureRow featureRow = new FeatureRow();
featureRow.Values = new object[fieldCount];
for (int c = 0; c < fieldSelection.Fields.Count; ++c)
{
if (fieldIndex[c] >= 0)
{
object value = recordSet.Records[r].Values[fieldIndex[c]];
if (value != null)
{
if (fieldSelection.Fields[c].Type == CommonFieldType.Geometry)
{
featureRow.Values[c] = ((AppGeo.Clients.Ags.Proxy.Geometry)value).ToCommon();
}
else
{
featureRow.Values[c] = value;
}
}
}
}
featureData.Rows[r] = featureRow;
}
return(featureData);
}
private string PrintFieldSelection(FieldSelection node)
{
var alias = node.Alias != null?PrintName(node.Alias.Value): string.Empty;
var name = PrintName(node.Name);
var args = node.Arguments?.Select(PrintArgument) ?? Array.Empty <string>();
var directives = node.Directives?.Select(PrintDirective) ?? Array.Empty <string>();
var selectionSet = node.SelectionSet != null?PrintSelectionSet(node.SelectionSet): string.Empty;
return(Join(new[]
{
$"{Wrap(string.Empty, alias, ": ")}{name}{Wrap("(", Join(args, ", "), ")")}",
Join(directives, " "),
selectionSet
}));
}
public virtual FieldSelection BeginVisitFieldSelection(
FieldSelection selection)
{
if (selection.Arguments != null)
{
BeginVisitArguments(selection.Arguments);
}
if (selection.SelectionSet != null)
{
BeginVisitNode(selection.SelectionSet);
}
if (selection.Directives != null)
{
BeginVisitDirectives(selection.Directives);
}
return(EndVisitFieldSelection(selection));
}
private TypeReferenceModel GenerateTypeReference(FieldSelection field)
{
if (field.Selection == null) // this is a leaf thus can be a projection
{
return(GenerateTypeReference(field.Type.Type));
}
else
{
return(new TypeReferenceModel
{
CanCollectionBeNull = field.Type.Type.CanCollectionBeNull,
IsCollection = field.Type.Type.IsCollection,
CanValueBeNull = field.Type.Type.CanValueBeNull,
TypeName = GenerateType(field.Selection)
});
}
}
protected override void EnterFieldSelection(PrinterContext context, FieldSelection fieldSelection)
{
if (fieldSelection.Alias != default)
{
context.Append(fieldSelection.Alias);
context.Append(": ");
}
context.Append($"{fieldSelection.Name}");
if (context.Parent is SelectionSet)
{
if (context.CurrentArray?.Count > 0 && !context.CurrentArray.IsLast)
{
context.Append(' ');
}
}
}
public void Visit(FieldSelection selection)
{
// enter
foreach (var visitor in _options.FieldSelection)
{
visitor.Enter(selection);
}
// children
Visit(selection.Arguments);
Visit(selection.Directives);
Visit(selection.SelectionSet);
// leave
foreach (var visitor in _options.FieldSelection)
{
visitor.Leave(selection);
}
}
public ResolverContext(
ObjectType objectType,
object objectValue,
IField field,
FieldSelection selection,
IReadOnlyCollection <FieldSelection> fields,
IReadOnlyDictionary <string, object?> arguments,
NodePath path,
IExecutorContext executionContext)
{
ObjectType = objectType ?? throw new ArgumentNullException(nameof(objectType));
ObjectValue = objectValue;
Field = field ?? throw new ArgumentNullException(nameof(field));
Selection = selection ?? throw new ArgumentNullException(nameof(selection));
Fields = fields;
Arguments = arguments ?? throw new ArgumentNullException(nameof(arguments));
Path = path ?? throw new ArgumentNullException(nameof(path));
ExecutionContext = executionContext;
}
private static void CreateMultiHit <T>(MultiHitTuple tuple, JsonSerializer serializer, ICollection <IMultiGetHit <object> > collection) where T : class
{
var hit = new MultiGetHit <T>();
var reader = tuple.Hit.CreateReader();
serializer.Populate(reader, hit);
var f = new FieldSelection <T>();
var source = tuple.Hit["fields"];
if (source != null)
{
f.Document = serializer.Deserialize <T>(source.CreateReader());
f.FieldValues = serializer.Deserialize <Dictionary <string, object> >(source.CreateReader());
hit.FieldSelection = f;
}
collection.Add(hit);
}
public void Paint(HeightField field, int x, int y, float intensity, out FieldSelection brushArea)
{
brushArea = new FieldSelection(x - Width / 2 - Width % 2, y - Height / 2 - Height % 2, Width, Height);
if (sample != null && blend != null)
{
for (int _x = brushArea.Left; _x < brushArea.Right; _x++)
{
for (int _y = brushArea.Top; _y < brushArea.Bottom; _y++)
{
float data;
if (field.TryGetHeight(_x, _y, out data))
{
float shape = sample(_x, _y, intensity * Power, brushArea.Left, brushArea.Right, brushArea.Top, brushArea.Bottom);
field[_x, _y] = blend(data, shape);
}
}
}
}
}
public override GeoAPI.Geometries.Envelope GetFeatureExtent(string where)
{
FieldSelection fieldSelection = new FieldSelection(this, GeometryField.Name);
QueryFilter queryFilter = new QueryFilter(GeometryField.Name, where);
RecordSet recordSet = _service.MapServer.QueryFeatureData(DataFrame.Name, _mapLayerInfo.LayerID, queryFilter);
DataTable table = RecordSetToTable(recordSet, fieldSelection);
GeoAPI.Geometries.Envelope extent = new GeoAPI.Geometries.Envelope();
foreach (DataRow row in table.Rows)
{
if (!row.IsNull(0))
{
extent.ExpandToInclude(((IGeometry)row[0]).EnvelopeInternal);
}
}
return(extent);
}
private DataTable RecordSetToTable(RecordSet recordSet, FieldSelection fieldSelection)
{
int[] fieldIndex = new int[fieldSelection.Fields.Count];
var select = recordSet.Fields.FieldArray.Select((field, index) => new { field, index });
for (int i = 0; i < fieldSelection.Fields.Count; ++i)
{
var result = select.Where(x => String.Compare(x.field.Name, fieldSelection.Fields[i].Name, true) == 0).FirstOrDefault();
fieldIndex[i] = result == null ? -1 : result.index;
}
DataTable table = fieldSelection.CreateTable();
foreach (Record record in recordSet.Records)
{
DataRow row = table.NewRow();
for (int c = 0; c < fieldSelection.Fields.Count; ++c)
{
if (fieldIndex[c] >= 0)
{
object value = record.Values[fieldIndex[c]];
if (value != null)
{
if (fieldSelection.Fields[c].Type == CommonFieldType.Geometry)
{
row[c] = ((AppGeo.Clients.Ags.Proxy.Geometry)value).ToCommon();
}
else
{
row[c] = value;
}
}
}
}
table.Rows.Add(row);
}
return(table);
}
/// <summary>
/// Get NId, Name, GID, Global from Indicator table based on Indicator Classification Type
/// </summary>
/// <param name="ICType">Indicator Classification Type with which associated indicator are to be retrieved</param>
/// <param name="ICNId">ICNId which may be -1 if only ICType is to be considered</param>
/// <param name="fieldSelection">Use heavy for all fields or use light to exclude IndicatorInfo field</param>
/// <returns>Sql query string</returns>
public string GetAllIndicatorByIC(ICType ICType, string ICNId, FieldSelection fieldSelection)
{
string RetVal = string.Empty;
StringBuilder sbQuery = new StringBuilder();
// SELECT clause
sbQuery.Append("SELECT I." + DIColumns.Indicator.IndicatorNId); //FieldSelection.NId
if (fieldSelection == FieldSelection.Light || fieldSelection == FieldSelection.Heavy)
{
sbQuery.Append(",I." + DIColumns.Indicator.IndicatorName + ",I." + DIColumns.Indicator.IndicatorGId + ",I." + DIColumns.Indicator.IndicatorGlobal);
}
if (fieldSelection == FieldSelection.Heavy)
{
sbQuery.Append(",I." + DIColumns.Indicator.IndicatorInfo);
}
// FROM Clause
sbQuery.Append(" FROM " + this.TablesName.Indicator + " AS I");
sbQuery.Append("," + this.TablesName.IndicatorUnitSubgroup + " AS IUS");
sbQuery.Append("," + this.TablesName.IndicatorClassificationsIUS + " AS ICIUS");
sbQuery.Append("," + this.TablesName.IndicatorClassifications + " AS IC");
// WHERE Clause
sbQuery.Append(" WHERE I." + DIColumns.Indicator.IndicatorNId + " = IUS." + DIColumns.Indicator_Unit_Subgroup.IndicatorNId);
sbQuery.Append(" AND IUS." + DIColumns.Indicator_Unit_Subgroup.IUSNId + " = ICIUS." + DIColumns.IndicatorClassificationsIUS.IUSNId);
sbQuery.Append(" AND ICIUS." + DIColumns.IndicatorClassificationsIUS.ICNId + " = IC." + DIColumns.IndicatorClassifications.ICNId);
sbQuery.Append(" AND IC." + DIColumns.IndicatorClassifications.ICType + " = " + DIQueries.ICTypeText[ICType]);
if (!string.IsNullOrEmpty(ICNId) && ICNId != "-1")
{
sbQuery.Append(" AND IC." + DIColumns.IndicatorClassifications.ICNId + " IN (" + ICNId + ")");
}
// ORDER BY Clause
sbQuery.Append(" ORDER BY " + DIColumns.Indicator.IndicatorName);
RetVal = sbQuery.ToString();
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 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;
}
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;
}
private string GetGenericSelectClause(FieldSelection fieldSelection)
{
string RetVal = string.Empty;
try
{
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);
}
if (fieldSelection == FieldSelection.Heavy)
{
sbQuery.Append(",I." + DIColumns.Indicator.IndicatorInfo);
}
RetVal = sbQuery.ToString();
}
catch (Exception)
{
}
return RetVal;
}
/// <summary>
/// Get distinct NId, Name, GID, Global from IUS - Indicator - Unit - SubgroupVal table based on Indicator Classification Type and order on the basis of ICIUS order
/// </summary>
/// <param name="ICType">Indicator Classification Type</param>
/// <param name="ICNId">ICNId which may be blank if only ICType is to be considered</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 GetDistinctOrderedIUSByIC(ICType ICType, string ICNId, FieldSelection fieldSelection)
{
string sortOrderString = "ICIUS." + DIColumns.IndicatorClassificationsIUS.ICIUSOrder;
return this.GetDistinctIUSByIC(ICType, ICNId, fieldSelection, sortOrderString);
}
/// <summary>
/// AutoSelect Indiactor,unit,subgroup based on TimePeriod, Area and source selection and only for the given indicators nid
/// </summary>
/// <param name="timePeriodNIds">commma delimited Timeperiod NIds which may be blank</param>
/// <param name="areaNIds">commma delimited Area NIds which may be blank</param>
/// <param name="sourceNids">Source Nids which may be blank </param>
/// <param name="fieldSelection">NId, Light or Heavy</param>
/// <param name="indicatorsNId">Comma delimited Indicators NId</param>
/// <returns>Sql query string</returns>
public string GetAutoSelectIUSByIndicator(string timePeriodNIds, string areaNIds, string sourceNids, FieldSelection fieldSelection, string indicatorsNId)
{
string RetVal = string.Empty;
RetVal = "SELECT IUS." + DIColumns.Indicator_Unit_Subgroup.IUSNId + ",I." + DIColumns.Indicator.IndicatorNId + ", I." + DIColumns.Indicator.IndicatorName + ", I." + DIColumns.Indicator.IndicatorGId + ", I." + DIColumns.Indicator.IndicatorGlobal +
",U." + DIColumns.Unit.UnitNId + ", U." + DIColumns.Unit.UnitName + ", U." + DIColumns.Unit.UnitGId + ", U." + DIColumns.Unit.UnitGlobal +
",SG." + DIColumns.SubgroupVals.SubgroupVal + ", SG." + DIColumns.SubgroupVals.SubgroupValNId + ", SG." + DIColumns.SubgroupVals.SubgroupValGId + ", SG." + DIColumns.SubgroupVals.SubgroupValGlobal
+ " FROM " + this.TablesName.Indicator + " AS I, " + this.TablesName.Unit + " AS U," + this.TablesName.SubgroupVals + " AS SG , " + this.TablesName.IndicatorUnitSubgroup + " AS IUS "
+ " WHERE I." + DIColumns.Indicator.IndicatorNId + " IN(" + indicatorsNId + ") AND "
+ " I." + DIColumns.Indicator.IndicatorNId + "= IUS." + DIColumns.Indicator_Unit_Subgroup.IndicatorNId
+ " AND U." + DIColumns.Unit.UnitNId + "=IUS." + DIColumns.Indicator_Unit_Subgroup.UnitNId
+ " AND SG." + DIColumns.SubgroupVals.SubgroupValNId + "=IUS." + DIColumns.Indicator_Unit_Subgroup.SubgroupValNId
+ " AND EXISTS ( SELECT * FROM " + this.TablesName.Data + " as D WHERE IUS." + DIColumns.Indicator_Unit_Subgroup.IUSNId + "= D." + DIColumns.Data.IUSNId;
if (!string.IsNullOrEmpty(timePeriodNIds))
{
RetVal += " AND D." + DIColumns.Data.TimePeriodNId + " IN( " + timePeriodNIds + " ) ";
}
if (!string.IsNullOrEmpty(areaNIds))
{
RetVal += " AND D." + DIColumns.Data.AreaNId + " IN( " + areaNIds + " ) ";
}
if (!string.IsNullOrEmpty(sourceNids))
{
RetVal += " AND D." + DIColumns.Data.SourceNId + " IN( " + sourceNids + " ) ";
}
RetVal += " ) ORDER BY I." + DIColumns.Indicator.IndicatorName + "";
return RetVal;
}
/// <summary>
/// Get NId, Name, GID, Global from Indicator against which data is present.
/// </summary>
/// <param name="fieldSelection">Use heavy for all fields or use light to exclude IndicatorInfo field</param>
/// <returns>Sql query string</returns>
public string GetIndicatorUnitWithData(string IUSNIDs, FieldSelection fieldSelection)
{
string RetVal = string.Empty;
StringBuilder sbQuery = new StringBuilder();
// SELECT Clause
sbQuery.Append(" SELECT DISTINCT IUS." + DIColumns.Indicator_Unit_Subgroup.IndicatorNId + ",IUS." + DIColumns.Indicator_Unit_Subgroup.UnitNId);
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);
}
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," + this.TablesName.Unit + " AS U");
}
// 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 + " and ");
}
if (string.IsNullOrEmpty(IUSNIDs))
{
IUSNIDs = "0";
}
sbQuery.Append(" " + DIColumns.Indicator_Unit_Subgroup.IUSNId + " in( " + IUSNIDs + " )");
// ORDER BY Clause
if (fieldSelection == FieldSelection.Light || fieldSelection == FieldSelection.Heavy)
{
sbQuery.Append(" ORDER BY I." + DIColumns.Indicator.IndicatorName);
}
RetVal = sbQuery.ToString();
return RetVal;
}
/// <summary>
/// Get distinct NId, Name, GID, Global from IUS - Indicator - Unit - SubgroupVal against which data is present and order by ICIUSOrder.
/// </summary>
/// <param name="ICType">Indicator Classification Type</param>
/// <param name="ICNId">ICNId which may be blank if only ICType is to be considered</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 GetDistinctOrderedIUSWithData(int icNId, string IUSNIDs, FieldSelection fieldSelection)
{
string RetVal = string.Empty;
StringBuilder sbQuery = new StringBuilder();
sbQuery.Append(this.GetGenericSelectClause(fieldSelection));
sbQuery.Append(",ICIUS." + DIColumns.IndicatorClassificationsIUS.ICIUSOrder);
//// 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.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 + " AND IUS." + DIColumns.Indicator_Unit_Subgroup.UnitNId + " = U." + DIColumns.Unit.UnitNId + " AND IUS." + DIColumns.Indicator_Unit_Subgroup.SubgroupValNId + " = SGV." + DIColumns.SubgroupVals.SubgroupValNId + " ");
sbQuery.Append(" AND IUS." + DIColumns.Indicator_Unit_Subgroup.IUSNId + " = ICIUS." + DIColumns.IndicatorClassificationsIUS.IUSNId);
}
if (!string.IsNullOrEmpty(IUSNIDs))
{
sbQuery.Append(" and IUS." + DIColumns.Indicator_Unit_Subgroup.IUSNId + " in( " + IUSNIDs + " )");
}
else
{
sbQuery.Append(" and IUS." + DIColumns.Indicator_Unit_Subgroup.IUSNId + " in(0) ");
}
if (icNId > 0)
{
sbQuery.Append(" and ICIUS." + DIColumns.IndicatorClassificationsIUS.ICNId + " = " + icNId);
}
// ORDER BY Clause
if (fieldSelection == FieldSelection.Light || fieldSelection == FieldSelection.Heavy)
{
sbQuery.Append(" ORDER BY ICIUS." + DIColumns.IndicatorClassificationsIUS.ICIUSOrder);
}
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 NId, Name, GID, Global from IUS - Indicator - Unit - SubgroupVal table based on Indicator Classification Type
/// </summary>
/// <param name="ICType"></param>
/// <param name="ICNId"></param>
/// <param name="fieldSelection"></param>
/// <param name="includeDI7Columns"></param>
/// <returns></returns>
public string GetIUSByIC(ICType ICType, int ICNId, FieldSelection fieldSelection, bool includeDI7Columns)
{
if (ICNId == -1)
{
return GetIUSByIC(ICType, string.Empty, fieldSelection, includeDI7Columns);
}
else
{
return GetIUSByIC(ICType, ICNId.ToString(), fieldSelection, includeDI7Columns);
}
}
/// <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>
/// 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;
}
/// <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>
/// AutoSelect IUS based on TimePeriod and Area selection
/// </summary>
/// <param name="timePeriodNIds">commma delimited Timeperiod NIds which may be blank</param>
/// <param name="areaNIds">commma delimited Area NIds which may be blank</param>
/// <param name="ICType">Indicator Classification Type</param>
/// <param name="fieldSelection">NId or Light</param>
/// <returns>Sql query string</returns>
public string GetAutoSelectByTimePeriodArea(string timePeriodNIds, string areaNIds, ICType ICType, FieldSelection fieldSelection)
{
string RetVal = String.Empty;
// Get basic Sql clause from overloaded function
RetVal = GetAutoSelectByTimePeriodArea(timePeriodNIds, areaNIds, -1, fieldSelection);
// Append additional clause for ICType
RetVal += " AND IC." + DIColumns.IndicatorClassifications.ICType + " = " + DIQueries.ICTypeText[ICType];
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>
/// Get IUSNIds based on Indicator, Unit , SubgroupVal
/// </summary>
/// <param name="indicatorName">IndicatorName which may be blank</param>
/// <param name="unitName">UnitName which may be blank</param>
/// <param name="subgroupVal">SubgroupVal which may be blank</param>
/// <returns>If all three parameters are blank then resultent query will return all IUSNIds in IUS table</returns>
public string GetIUSByI_U_S_Name(string indicatorName, string unitName, string subgroupVal, FieldSelection fieldSelection)
{
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);
}
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 1=1 ");
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 (indicatorName.Trim().Length > 0)
sbQuery.Append(" AND " + DIColumns.Indicator.IndicatorName + " ='" + indicatorName + "'");
if (unitName.Trim().Length > 0)
sbQuery.Append(" AND " + DIColumns.Unit.UnitName + " ='" + unitName + "'");
if (subgroupVal.Trim().Length > 0)
sbQuery.Append(" AND " + DIColumns.SubgroupVals.SubgroupVal + " ='" + subgroupVal + "'");
}
RetVal = sbQuery.ToString();
return RetVal;
}
/// <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 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>
/// Returns SELECT and FROM clause for IC and ICIUSNId table based on FieldSelection
/// </summary>
/// <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>
private string GetICIUSSelectFromClause(FieldSelection fieldSelection)
{
string RetVal = string.Empty;
RetVal = "SELECT DISTINCT IC." + DIColumns.IndicatorClassifications.ICNId + ",IC." + DIColumns.IndicatorClassifications.ICParent_NId + ",IC." + DIColumns.IndicatorClassifications.ICName + ",IC." + DIColumns.IndicatorClassifications.ICGId + ",IC." + DIColumns.IndicatorClassifications.ICGlobal + ",IC." + DIColumns.IndicatorClassifications.ICType;
RetVal += ",ICIUS." + DIColumns.IndicatorClassificationsIUS.IUSNId;
if (fieldSelection == FieldSelection.Heavy)
{
RetVal += "," + DIColumns.IndicatorClassifications.ICInfo;
}
// FROM Clause
RetVal += " FROM " + this.TablesName.IndicatorClassifications + " AS IC," + this.TablesName.IndicatorClassificationsIUS + " AS ICIUS";
return RetVal;
}
/// <summary>
/// Returns query to get area map layer
/// </summary>
/// <param name="layerNid">Comma separated layer nids and to get all map layer can be blank</param>
/// <param name="fieldSelection"></param>
/// <returns></returns>
public string GetAreaMapLayerByNid(string layerNids, FieldSelection fieldSelection)
{
string RetVal = string.Empty;
// SELECT
RetVal = "Select AML." + DIColumns.Area_Map_Layer.LayerNId + ", AML." + DIColumns.Area_Map_Layer.LayerSize + ", AML." + DIColumns.Area_Map_Layer.LayerType + ", AML." + DIColumns.Area_Map_Layer.MinX + ", AML." + DIColumns.Area_Map_Layer.MinY + ", AML." + DIColumns.Area_Map_Layer.MaxX + ", AML." + DIColumns.Area_Map_Layer.MaxY + ", AML." + DIColumns.Area_Map_Layer.StartDate + ", AML." + DIColumns.Area_Map_Layer.EndDate + ", AML." + DIColumns.Area_Map_Layer.UpdateTimestamp + ", AMM." + DIColumns.Area_Map_Metadata.LayerName;
if (fieldSelection == FieldSelection.Heavy)
{
RetVal += ", AML." + DIColumns.Area_Map_Layer.LayerShp + ", AML." + DIColumns.Area_Map_Layer.LayerShx + ", AML." + DIColumns.Area_Map_Layer.Layerdbf;
}
// FROM
RetVal += " FROM " + this.TablesName.AreaMapLayer + " AML, " + this.TablesName.AreaMapMetadata + " AMM ";
// WHERE
RetVal += " WHERE AMM." + DIColumns.Area_Map_Layer.LayerNId + " = AML." + DIColumns.Area_Map_Layer.LayerNId;
if (!string.IsNullOrEmpty(layerNids))
RetVal += " AND AML.Layer_NId in(" + layerNids + ")";
// Return
return RetVal;
}
/// <summary>
/// Returns SELECT and FROM clause for IC table based on FieldSelection
/// </summary>
/// <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>
private string GetICSelectFromClause(FieldSelection fieldSelection,bool includeISBNNature)
{
string RetVal = string.Empty;
RetVal = "SELECT IC." + DIColumns.IndicatorClassifications.ICNId + ",IC." + DIColumns.IndicatorClassifications.ICParent_NId + ",IC." + DIColumns.IndicatorClassifications.ICName + ",IC." + DIColumns.IndicatorClassifications.ICGId + ",IC." + DIColumns.IndicatorClassifications.ICGlobal + ",IC." + DIColumns.IndicatorClassifications.ICType + ",IC." + DIColumns.IndicatorClassifications.ICOrder ;
if (includeISBNNature)
{
RetVal += ",IC." + DIColumns.IndicatorClassifications.ISBN + ",IC." + DIColumns.IndicatorClassifications.Nature;
}
if (fieldSelection == FieldSelection.Heavy)
{
RetVal += ",IC." + DIColumns.IndicatorClassifications.ICInfo;
}
RetVal += " FROM " + this.TablesName.IndicatorClassifications + " AS IC";
return RetVal;
}
/// <summary>
/// Get LayerName, LayerNId, LayerSize, LayerType, MinX, MinY, MaxX, MaxY,[LayerShp, LayerShx, LayerDbf] from AreaMapLayer for a given Filter Criteria
/// </summary>
/// <param name="MapFilterFieldType">None will get all layers</param>
/// <param name="filterText">blank will get all layers</param>
/// <param name="fieldSelection">Light or Heavy. Heavy will include shp, shx and dbf</param>
/// <returns></returns>
public string GetAreaMapLayer(MapFilterFieldType mapFilterFieldType, string filterText, FieldSelection fieldSelection)
{
string RetVal = string.Empty;
StringBuilder sbQuery = new StringBuilder();
//DISTINCT Clause not valid with memo or oledb (blob) fields
sbQuery.Append("SELECT AMM." + DIColumns.Area_Map_Metadata.LayerName + ",AML." + DIColumns.Area_Map_Layer.LayerNId + ",AML." + DIColumns.Area_Map_Layer.LayerSize + ",AML." + DIColumns.Area_Map_Layer.LayerType);
sbQuery.Append(",AML." + DIColumns.Area_Map_Layer.MinX + ",AML." + DIColumns.Area_Map_Layer.MinY + ",AML." + DIColumns.Area_Map_Layer.MaxX + ",AML." + DIColumns.Area_Map_Layer.MaxY);
sbQuery.Append(",AML." + DIColumns.Area_Map_Layer.StartDate + ",AML." + DIColumns.Area_Map_Layer.EndDate + ",AML." + DIColumns.Area_Map_Layer.UpdateTimestamp);
if (fieldSelection == FieldSelection.Heavy)
{
sbQuery.Append(",AML." + DIColumns.Area_Map_Layer.LayerShp + ",AML." + DIColumns.Area_Map_Layer.LayerShx + ",AML." + DIColumns.Area_Map_Layer.Layerdbf);
}
if (mapFilterFieldType == MapFilterFieldType.AreaNId || mapFilterFieldType == MapFilterFieldType.AreaLevel)
{
sbQuery.Append(",A." + DIColumns.Area.AreaLevel + ", A." + DIColumns.Area.AreaNId + ", A." + DIColumns.Area.AreaID + ", A." + DIColumns.Area.AreaBlock);
}
sbQuery.Append(" FROM " + this.TablesName.AreaMapLayer + " AS AML," + this.TablesName.AreaMapMetadata + " AS AMM");
if (mapFilterFieldType == MapFilterFieldType.AreaNId || mapFilterFieldType == MapFilterFieldType.AreaLevel)
{
sbQuery.Append("," + this.TablesName.AreaMap + " AS AM," + this.TablesName.Area + " AS A");
}
sbQuery.Append(" WHERE AML." + DIColumns.Area_Map_Layer.LayerNId + " = AMM." + DIColumns.Area_Map_Metadata.LayerNId);
if (mapFilterFieldType == MapFilterFieldType.AreaNId || mapFilterFieldType == MapFilterFieldType.AreaLevel)
{
sbQuery.Append(" AND AML." + DIColumns.Area_Map_Layer.LayerNId + " = AM." + DIColumns.Area_Map.LayerNId);
sbQuery.Append(" AND AM." + DIColumns.Area_Map.AreaNId + " = A." + DIColumns.Area.AreaNId);
}
if (filterText.Length > 0)
{
switch (mapFilterFieldType)
{
case MapFilterFieldType.None:
break;
case MapFilterFieldType.LayerNId:
sbQuery.Append(" AND AML." + DIColumns.Area_Map_Layer.LayerNId + " IN (" + filterText + ")");
break;
case MapFilterFieldType.LayerName:
sbQuery.Append(" AND AMM." + DIColumns.Area_Map_Metadata.LayerName + " IN (" + filterText + ")");
break;
case MapFilterFieldType.AreaNId:
sbQuery.Append(" AND A." + DIColumns.Area.AreaNId + " IN (" + filterText + ")");
break;
case MapFilterFieldType.AreaLevel:
sbQuery.Append(" AND A." + DIColumns.Area.AreaLevel + " IN (" + filterText + ")");
break;
}
}
//*** Polygon(5)->PolyLine(3)->Points(1) Country(1)->State(2)->District(3)
sbQuery.Append(" ORDER BY AML." + DIColumns.Area_Map_Layer.LayerType + " DESC");
if (mapFilterFieldType == MapFilterFieldType.AreaNId || mapFilterFieldType == MapFilterFieldType.AreaLevel)
{
sbQuery.Append(",A." + DIColumns.Area.AreaLevel);
}
RetVal = sbQuery.ToString();
return RetVal;
}
/// <summary>
/// AutoSelect IUS based on TimePeriod and Area selection
/// </summary>
/// <param name="timePeriodNIds">commma delimited Timeperiod NIds which may be blank</param>
/// <param name="areaNIds">commma delimited Area NIds which may be blank</param>
/// <param name="ICNId">Indicator Classification NId of selected item in IC tree which may be -1 for all </param>
/// <param name="fieldSelection">NId or Light</param>
/// <returns>Sql query string</returns>
public string GetAutoSelectByTimePeriodArea(string timePeriodNIds, string areaNIds, int ICNId, FieldSelection fieldSelection)
{
string RetVal = string.Empty;
StringBuilder sbQuery = new StringBuilder();
// Set SELECT clause based on fieldSelection type
sbQuery.Append("SELECT DISTINCT IUS." + DIColumns.Indicator_Unit_Subgroup.IUSNId + ",I." + DIColumns.Indicator.IndicatorNId + ",U." + DIColumns.Unit.UnitNId + ",SGV." + DIColumns.SubgroupVals.SubgroupValNId); //FieldSelection.NId
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);
}
//FROM UT_Data AS D,UT_Indicator_Unit_Subgroup AS IUS,UT_Indicator_en AS I,UT_Unit_en AS U,UT_Subgroup_Vals_en AS SGV,UT_Indicator_Classifications_en AS IC,UT_Indicator_Classifications_IUS AS ICIUS,UT_Area_en AS A ,UT_TimePeriod AS T
// Set FROM Clause
sbQuery.Append(" FROM " + this.TablesName.Data + " AS D," + this.TablesName.IndicatorUnitSubgroup + " AS IUS," + this.TablesName.Indicator + " AS I,");
sbQuery.Append(this.TablesName.Unit + " AS U," + this.TablesName.SubgroupVals + " AS SGV");
if (timePeriodNIds.Length > 0)
{
sbQuery.Append("," + this.TablesName.TimePeriod + " AS T");
}
if (areaNIds.Length > 0)
{
sbQuery.Append("," + this.TablesName.Area + " AS A");
}
sbQuery.Append("," + this.TablesName.IndicatorClassifications + " AS IC," + this.TablesName.IndicatorClassificationsIUS + " AS ICIUS");
// Set WHERE clause
sbQuery.Append(" WHERE I." + DIColumns.Indicator.IndicatorNId + " = IUS." + DIColumns.Indicator_Unit_Subgroup.IndicatorNId);
sbQuery.Append(" AND U." + DIColumns.Unit.UnitNId + " = IUS." + DIColumns.Indicator_Unit_Subgroup.UnitNId);
sbQuery.Append(" AND SGV." + DIColumns.SubgroupVals.SubgroupValNId + " = IUS." + DIColumns.Indicator_Unit_Subgroup.SubgroupValNId);
sbQuery.Append(" AND IUS." + DIColumns.Indicator_Unit_Subgroup.IUSNId + " = D." + DIColumns.Data.IUSNId);
if (timePeriodNIds.Length > 0)
{
sbQuery.Append(" AND T." + DIColumns.Timeperiods.TimePeriodNId + " = D." + DIColumns.Data.TimePeriodNId + " AND T." + DIColumns.Timeperiods.TimePeriodNId + " IN (" + timePeriodNIds + ")");
}
if (areaNIds.Length > 0)
{
sbQuery.Append(" AND A." + DIColumns.Area.AreaNId + " = D." + DIColumns.Data.AreaNId + " AND A." + DIColumns.Area.AreaNId + " IN (" + areaNIds + ")");
}
sbQuery.Append(" AND IUS." + DIColumns.Indicator_Unit_Subgroup.IUSNId + " = ICIUS." + DIColumns.IndicatorClassificationsIUS.IUSNId);
sbQuery.Append(" AND ICIUS." + DIColumns.IndicatorClassificationsIUS.ICNId + " = IC." + DIColumns.IndicatorClassifications.ICNId);
if (ICNId != -1)
{
sbQuery.Append(" AND IC." + DIColumns.IndicatorClassifications.ICNId + " = " + ICNId);
}
RetVal = sbQuery.ToString();
return RetVal;
}
/// <summary>
/// Get Indicator Classification records IndicatorNids
/// </summary>
/// <param name="IndicatorNIds">Comma delimited Indicator NIds</param>
/// <param name="fieldSelection">LIGHT: Returns - ICNId, ICParent_NId, ICName, ICGId, ICGlobal,IndicatorNId
/// <para>HEAVY: Returns - NId, PArentNId, Name, GID, Global, Info</para>
/// </param>
/// <returns>
/// </returns>
public string GetICForIndicators(string IndicatorNIds, FieldSelection fieldSelection)
{
string RetVal = string.Empty;
StringBuilder sbQuery = new StringBuilder();
// SELECT Clause
sbQuery.Append("SELECT DISTINCT IC." + DIColumns.IndicatorClassifications.ICNId + ",IC." + DIColumns.IndicatorClassifications.ICParent_NId + ",IC." + DIColumns.IndicatorClassifications.ICName + ",IC." + DIColumns.IndicatorClassifications.ICGId + ",IC." + DIColumns.IndicatorClassifications.ICGlobal + ",IC." + DIColumns.IndicatorClassifications.ICType);
sbQuery.Append(",IUS." + DIColumns.Indicator_Unit_Subgroup.IndicatorNId);
if (fieldSelection == FieldSelection.Heavy)
{
sbQuery.Append("," + DIColumns.IndicatorClassifications.ICInfo);
}
// FROM Clause
sbQuery.Append(" FROM " + this.TablesName.IndicatorClassifications + " AS IC," + this.TablesName.IndicatorClassificationsIUS + " AS ICIUS," + this.TablesName.IndicatorUnitSubgroup + " AS IUS");
// WHERE Clause
sbQuery.Append(" WHERE ");
sbQuery.Append(" IC." + DIColumns.IndicatorClassifications.ICNId + "= ICIUS." + DIColumns.IndicatorClassificationsIUS.ICNId + " AND ICIUS." + DIColumns.IndicatorClassificationsIUS.IUSNId + "= IUS." + DIColumns.Indicator_Unit_Subgroup.IUSNId);
if (IndicatorNIds.Trim().Length > 0)
{
sbQuery.Append(" AND IUS." + DIColumns.Indicator_Unit_Subgroup.IndicatorNId + " IN (" + IndicatorNIds + ")");
}
RetVal = sbQuery.ToString();
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>
/// Get distinct NId, Name, GID, Global from IUS - Indicator - Unit - SubgroupVal table based on Indicator Classification Type
/// </summary>
/// <param name="ICType">Indicator Classification Type</param>
/// <param name="ICNId">ICNId which may be -1 if only ICType is to be considered</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 GetDistinctIUSByIC(ICType ICType, int ICNId, FieldSelection fieldSelection)
{
if (ICNId == -1)
{
return GetDistinctIUSByIC(ICType, string.Empty, fieldSelection);
}
else
{
return GetDistinctIUSByIC(ICType, ICNId.ToString(), fieldSelection);
}
}
/// <summary>
/// Get distinct NId, Name, GID, Global from IUS - Indicator - Unit - SubgroupVal table based on Indicator Classification Type
/// </summary>
/// <param name="ICType">Indicator Classification Type</param>
/// <param name="ICNId">ICNId which may be blank if only ICType is to be considered</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 GetDistinctIUSByIC(ICType ICType, string ICNId, FieldSelection fieldSelection)
{
string sortOrderString = "I." + DIColumns.Indicator.IndicatorName + ", U." + DIColumns.Unit.UnitName + ", SGV." + DIColumns.SubgroupVals.SubgroupVal;
return this.GetDistinctIUSByIC(ICType, ICNId, fieldSelection, sortOrderString);
}
