public QueryModel LoadFromStoreQuery(IQueryModel qm, StoreQuery q)
{
var result = new QueryModel();
// read parameters
result.StoreParameters.DataService = q.DataService;
result.StoreParameters.QueryName = q.Name;
result.StoreParameters.Namespace = q.Namespace;
result.StoreParameters.QueryReturnType = q.ReturnTypeName;
result.Schema.Name = q.SchemaName;
result.Schema.Version = q.SchemaVersion;
// from
foreach (var t in q.Query.Tables)
{
var schemaTable = qm.Schema.Definitions[t.Value.TableName];
var ft = new QueryFromTable(schemaTable);
ft.Alias = t.Value.ObjectAlias;
result.FromTables.Add(ft);
}
// qc.RegenerateTableLinks();
// fields
foreach (var f in q.Query.Fields.Values)
{
var table = result.FromTables.FirstOrDefault(t => t.Alias == f.Field.ObjectAlias);
if (table == null)
{
throw new Exception($"Table with alias '{f.Field.ObjectAlias}' not found");
}
var storeProperty = table.Properties.FirstOrDefault(p => p.StoreProperty.Name == f.Field.FieldName);
if (storeProperty == null)
{
throw new Exception($"Field '{f.Field.FieldName}' not found in table with alias '{f.Field.ObjectAlias}'");
}
var newSelectionProperty = new QuerySelectProperty(table, storeProperty.StoreProperty)
{
IsOutput = f.IsOutput,
GroupByFunction = f.GroupByFunction,
Alias = f.FieldAlias != f.Field.FieldName ? f.FieldAlias : ""
// ToDo: Filter is not stored and cannot be loaded
};
result.SelectionProperties.Add(newSelectionProperty);
}
// Where
result.WhereClause = QueryExpressionHelper.QueryExprToString(q.Query.Where.Expression);
return(result);
}
public void AddSelectionProperty(QueryFromTable table, QueryFromProperty property)
{
if (!SelectedQuery.SelectionProperties.Any(s => s.FromTable.Alias == table.Alias && s.StoreProperty.Name == property.Name))
{
var newSelectProperty = new QuerySelectProperty(table, property.OriginalStoreProperty)
{
IsOutput = true, Alias = property.Alias
};
SelectedQuery.SelectionProperties.Add(newSelectProperty);
}
}
public string ReviewSelectPropertyFilter(QuerySelectProperty property, string filterText)
{
var filter = filterText?.Trim();
if (string.IsNullOrWhiteSpace(filter))
{
return("");
}
else
{
// generate new condition
var filterOperator = "=";
var operators = _resolver.GetCompareOperators();
foreach (var o in operators)
{
if (filter.StartsWith(o))
{
filterOperator = o;
filter = filter.Substring(o.Count()).Trim();
break;
}
}
;
var filterValue = filter;
char quote = _resolver.GetStringLiteralSymbol();
if (filterValue.First() != '@' && (property.StoreProperty.Type == "String" || property.StoreProperty.Type == "DateTime"))
{
if (filterValue.First() != quote)
{
filterValue = quote + filterValue;
}
if (filterValue.Last() != quote)
{
filterValue = filterValue + quote;
}
}
//if (!string.IsNullOrWhiteSpace(SelectedQuery.WhereClause))
//{
// SelectedQuery.WhereClause += " AND ";
//}
//SelectedQuery.WhereClause += $"{property.FromTable.Alias}.{property.StoreProperty.Name} {filterOperator} {filterValue}";
var result = $"{filterOperator} {filterValue}";
return(result);
}
}
public void ApplySelectPropertyFilter(QuerySelectProperty property, string filterText)
{
var filter = filterText?.Trim();
if (string.IsNullOrWhiteSpace(filter))
{
// find and remove previous condition
}
else
{
// add new condition
var filterOperator = "=";
var operators = _resolver.GetCompareOperators();
foreach (var o in operators)
{
if (filter.StartsWith(o))
{
filterOperator = o;
filter = filter.Substring(o.Count()).Trim();
break;
}
}
;
var filterValue = filter;
char quote = _resolver.GetStringLiteralSymbol();
if (filterValue.First() != '@' && (property.StoreProperty.Type == "String" || property.StoreProperty.Type == "DateTime"))
{
if (filterValue.First() != quote)
{
filterValue = quote + filterValue;
}
if (filterValue.Last() != quote)
{
filterValue = filterValue + quote;
}
}
if (!string.IsNullOrWhiteSpace(SelectedQuery.WhereClause))
{
SelectedQuery.WhereClause += " AND ";
}
SelectedQuery.WhereClause += $"{property.FromTable.Alias}.{property.StoreProperty.Name} {filterOperator} {filterValue}";
}
}
public void SetGroupByFunction(QuerySelectProperty property, string func)
{
property.GroupByFunction = func;
if (func == "Group By All")
{
SelectionProperties.Where(p => p.IsOutput).ToList().ForEach(p => p.GroupByFunction = "Group By");
SelectionProperties.Where(p => !String.IsNullOrWhiteSpace(p.Filter)).ToList().ForEach(p => p.GroupByFunction = "Where");
}
if (func == "Group By None")
{
SelectionProperties.ToList().ForEach(p => p.GroupByFunction = "");
}
}
public SubQueryStoreProperty(QuerySelectProperty queryProperty)
{
QuerySelectProperty = queryProperty;
Source = queryProperty.StoreProperty;
}
private static IQueryModel ReadQueryTables(List <IQueryModel> loadedSubQueryList, string name, StoreSchema schema, StoreQueryDefinition queryDef, Dictionary <string, StoreQueryDefinition> subQueries)
{
var result = loadedSubQueryList.FirstOrDefault(l => l.Name == name);
if (result != null)
{
return(result);
}
queryDef = queryDef ?? subQueries[name];
result = new QueryModel()
{
Name = name
};
foreach (var t in queryDef.Tables)
{
if (schema.Definitions.ContainsKey(t.Value.TableName))
{
// not subquery table
var schemaTable = schema.Definitions[t.Value.TableName];
var ft = new QueryFromTable(schemaTable);
ft.Alias = t.Value.ObjectAlias;
result.FromTables.Add(ft);
}
else
{
if (!subQueries.ContainsKey(t.Value.TableName))
{
throw new Exception($"Table or SubQuery with name '{t.Value.TableName}' not found");
}
var subQuery = ReadQueryTables(loadedSubQueryList, t.Value.TableName, schema, null, subQueries);
var ft = new QueryFromTable(subQuery);
ft.Alias = t.Value.ObjectAlias;
result.FromTables.Add(ft);
}
}
loadedSubQueryList.Add(result);
// fields
foreach (var f in queryDef.Fields.Values)
{
var table = result.FromTables.FirstOrDefault(t => t.Alias == f.Field.ObjectAlias);
if (table == null)
{
throw new Exception($"Table with alias '{f.Field.ObjectAlias}' not found");
}
var storeProperty = table.Properties.FirstOrDefault(p => p.Name == f.Field.FieldName);
if (storeProperty == null)
{
throw new Exception($"Field '{f.Field.FieldName}' not found in table with alias '{f.Field.ObjectAlias}'");
}
var newSelectionProperty = new QuerySelectProperty(table, storeProperty.OriginalStoreProperty)
{
IsOutput = f.IsOutput,
GroupByFunction = f.GroupByFunction,
Having = f.GroupByFilter,
Alias = f.FieldAlias != f.Field.FieldName ? f.FieldAlias : ""
// ToDo: Filter is not stored and cannot be loaded
};
result.SelectionProperties.Add(newSelectionProperty);
}
// Joins
foreach (var j in queryDef.Joins)
{
}
result.FromTableJoins = queryDef.Joins.Select(j => new TableJoinModel()
{
IsDeleted = false,
JoinType = j.JoinType,
Source = j
}).ToList();
// Where
result.WhereClause = QueryExpressionHelper.QueryExprToString(queryDef.Where.Expression);
return(result);
}
