/// <summary>
/// Renders the specified context.
/// </summary>
/// <param name="context">The context.</param>
/// <param name="result">The result.</param>
public override void OnRender(ILavaRenderContext context, TextWriter result)
{
// first ensure that sql commands are allowed in the context
if (!this.IsAuthorized(context))
{
result.Write(string.Format(LavaBlockBase.NotAuthorizedMessage, this.SourceElementName));
return;
}
using (TextWriter sql = new StringWriter())
{
base.OnRender(context, sql);
var parms = ParseMarkup(_markup, context);
var sqlTimeout = (int?)null;
if (parms.ContainsKey("timeout"))
{
sqlTimeout = parms["timeout"].AsIntegerOrNull();
}
switch (parms["statement"])
{
case "select":
var stopWatch = new Stopwatch();
stopWatch.Start();
var results = DbService.GetDataSet(sql.ToString(), CommandType.Text, parms.ToDictionary(i => i.Key, i => (object)i.Value), sqlTimeout);
stopWatch.Stop();
context.SetMergeField(parms["return"], results.Tables[0].ToDynamic());
// Manually add query timings
var rockMockContext = LavaHelper.GetRockContextFromLavaContext(context);
rockMockContext.QueryCount++;
if (rockMockContext.QueryMetricDetailLevel == QueryMetricDetailLevel.Full)
{
rockMockContext.QueryMetricDetails.Add(new QueryMetricDetail
{
Sql = sql.ToString(),
Duration = stopWatch.ElapsedTicks,
Database = rockMockContext.Database.Connection.Database,
Server = rockMockContext.Database.Connection.DataSource
});
}
break;
case "command":
var sqlParameters = new List <System.Data.SqlClient.SqlParameter>();
foreach (var p in parms)
{
sqlParameters.Add(new System.Data.SqlClient.SqlParameter(p.Key, p.Value));
}
using (var rockContext = LavaHelper.GetRockContextFromLavaContext(context))
{
if (sqlTimeout != null)
{
rockContext.Database.CommandTimeout = sqlTimeout;
}
int numOfRowsAffected = rockContext.Database.ExecuteSqlCommand(sql.ToString(), sqlParameters.ToArray());
context.SetMergeField(parms["return"], numOfRowsAffected);
}
break;
default:
break;
}
}
}
/// <summary>
/// Renders the specified context.
/// </summary>
/// <param name="context">The context.</param>
/// <param name="result">The result.</param>
public override void OnRender(ILavaRenderContext context, TextWriter result)
{
// first ensure that search commands are allowed in the context
if (!this.IsAuthorized(context))
{
result.Write(string.Format(LavaBlockBase.NotAuthorizedMessage, this.SourceElementName));
base.OnRender(context, result);
return;
}
var parms = ParseMarkup(_markup, context);
SearchFieldCriteria fieldCriteria = new SearchFieldCriteria();
SearchType searchType = SearchType.Wildcard;
List <int> entityIds = new List <int>();
string query = string.Empty;
int limit = 50;
int offset = 0;
if (parms.Any(p => p.Key == "query"))
{
query = parms["query"];
}
if (parms.Any(p => p.Key == "limit"))
{
Int32.TryParse(parms["limit"], out limit);
}
if (parms.Any(p => p.Key == "offset"))
{
Int32.TryParse(parms["offset"], out offset);
}
if (parms.Any(p => p.Key == "fieldcriteria"))
{
foreach (var queryString in parms["fieldcriteria"].ToKeyValuePairList())
{
// check that multiple values were not passed
var values = queryString.Value.ToString().Split(',');
foreach (var value in values)
{
// the first letter of the field name should be lowercase
string fieldName = Char.ToLowerInvariant(queryString.Key[0]) + queryString.Key.Substring(1);
fieldCriteria.FieldValues.Add(new FieldValue {
Field = fieldName, Value = value
});
}
}
}
if (parms.Any(p => p.Key == "searchtype"))
{
switch (parms["searchtype"])
{
case "exactmatch":
{
searchType = SearchType.ExactMatch;
break;
}
case "fuzzy":
{
searchType = SearchType.Fuzzy;
break;
}
case "wildcard":
{
searchType = SearchType.Wildcard;
break;
}
}
}
if (parms.Any(p => p.Key == "criteriasearchtype"))
{
if (parms["criteriasearchtype"].ToLower() == "and")
{
fieldCriteria.SearchType = CriteriaSearchType.And;
}
}
if (parms.Any(p => p.Key == "entities"))
{
var entities = parms["entities"].Split(',');
foreach (var entity in entities)
{
foreach (var entityType in EntityTypeCache.All())
{
if (entityType.FriendlyName?.ToLower() == entity)
{
entityIds.Add(entityType.Id);
}
}
}
}
var client = IndexContainer.GetActiveComponent();
if (client == null)
{
throw new Exception("Search results not available. Universal search is not enabled for this Rock instance.");
}
var results = client.Search(query, searchType, entityIds, fieldCriteria, limit, offset);
context.SetMergeField(parms["iterator"], results, LavaContextRelativeScopeSpecifier.Root);
base.OnRender(context, result);
}
/// <summary>
/// Renders the specified context.
/// </summary>
/// <param name="context">The context.</param>
/// <param name="result">The result.</param>
/// <exception cref="System.Exception">Your Lava command must contain at least one valid filter. If you configured a filter it's possible that the property or attribute you provided does not exist.</exception>
public override void OnRender(ILavaRenderContext context, TextWriter result)
{
// first ensure that entity commands are allowed in the context
if (!this.IsAuthorized(context, "rockentity"))
{
result.Write(string.Format(LavaBlockBase.NotAuthorizedMessage, "rockentity"));
base.OnRender(context, result);
return;
}
bool hasFilter = false;
var modelName = string.Empty;
// get a service for the entity based off it's friendly name
if (EntityName == "business")
{
modelName = "Rock.Model.Person";
}
else
{
modelName = "Rock.Model." + EntityName;
}
// Check first to see if this is a core model. use the createIfNotFound = false option
var entityTypeCache = EntityTypeCache.Get(modelName, false);
if (entityTypeCache == null)
{
var entityTypes = EntityTypeCache.All();
// If not, look for first plug-in model that has same friendly name
entityTypeCache = entityTypes
.Where(e =>
e.IsEntity &&
!e.Name.StartsWith("Rock.Model") &&
e.FriendlyName != null &&
e.FriendlyName.RemoveSpaces().ToLower() == EntityName)
.OrderBy(e => e.Id)
.FirstOrDefault();
// If still null check to see if this was a duplicate class and full class name was used as entity name
if (entityTypeCache == null)
{
modelName = EntityName.Replace('_', '.');
entityTypeCache = entityTypes.Where(e => String.Equals(e.Name, modelName, StringComparison.OrdinalIgnoreCase)).FirstOrDefault();
}
}
if (entityTypeCache != null)
{
Type entityType = entityTypeCache.GetEntityType();
if (entityType != null)
{
// Get the appropriate database context for this entity type.
// Note that this may be different from the standard RockContext if the entity is sourced from a plug-in.
var dbContext = Reflection.GetDbContextForEntityType(entityType);
// Check if there is a RockContext in the Lava context, if so (and the entity is using a RockContext) we should use that one
if (dbContext is RockContext)
{
dbContext = LavaHelper.GetRockContextFromLavaContext(context);
}
// Disable change-tracking for this data context to improve performance - objects supplied to a Lava context are read-only.
dbContext.Configuration.AutoDetectChangesEnabled = false;
// Create an instance of the entity's service
IService serviceInstance = Reflection.GetServiceForEntityType(entityType, dbContext);
ParameterExpression paramExpression = Expression.Parameter(entityType, "x");
Expression queryExpression = null; // the base expression we'll use to build our query from
// Parse markup
var parms = ParseMarkup(_markup, context);
if (parms.Any(p => p.Key == "id"))
{
string propertyName = "Id";
List <string> selectionParms = new List <string>();
selectionParms.Add(PropertyComparisonConversion("==").ToString());
selectionParms.Add(parms["id"].ToString());
selectionParms.Add(propertyName);
var entityProperty = entityType.GetProperty(propertyName);
queryExpression = ExpressionHelper.PropertyFilterExpression(selectionParms, paramExpression, propertyName, entityProperty.PropertyType);
hasFilter = true;
}
else
{
// where clause expression
if (parms.Any(p => p.Key == "where"))
{
queryExpression = ParseWhere(parms["where"], entityType, serviceInstance, paramExpression, entityType, entityTypeCache);
if (queryExpression != null)
{
hasFilter = true;
}
}
// DataView expression
if (parms.Any(p => p.Key == "dataview"))
{
var dataViewId = parms["dataview"].AsIntegerOrNull();
if (dataViewId.HasValue)
{
var dataViewExpression = GetDataViewExpression(dataViewId.Value, serviceInstance, paramExpression, entityTypeCache);
if (queryExpression == null)
{
queryExpression = dataViewExpression;
hasFilter = true;
}
else
{
queryExpression = Expression.AndAlso(queryExpression, dataViewExpression);
}
}
}
// process dynamic filter expressions (from the query string)
if (parms.Any(p => p.Key == "dynamicparameters"))
{
var dynamicFilters = parms["dynamicparameters"].Split(',')
.Select(x => x.Trim())
.Where(x => !string.IsNullOrWhiteSpace(x))
.ToList();
foreach (var dynamicFilter in dynamicFilters)
{
var dynamicFilterValue = HttpContext.Current.Request[dynamicFilter];
var dynamicFilterExpression = GetDynamicFilterExpression(dynamicFilter, dynamicFilterValue, entityType, serviceInstance, paramExpression);
if (dynamicFilterExpression != null)
{
if (queryExpression == null)
{
queryExpression = dynamicFilterExpression;
hasFilter = true;
}
else
{
queryExpression = Expression.AndAlso(queryExpression, dynamicFilterExpression);
}
}
}
}
}
// Make the query from the expression.
/* [2020-10-08] DL
* "Get" is intentionally used here rather than "GetNoTracking" to allow lazy-loading of navigation properties from the Lava context.
* (Refer https://github.com/SparkDevNetwork/Rock/issues/4293)
*/
MethodInfo getMethod = serviceInstance.GetType().GetMethod("Get", new Type[] { typeof(ParameterExpression), typeof(Expression), typeof(Rock.Web.UI.Controls.SortProperty), typeof(int?) });
if (getMethod != null)
{
// get a listing of ids and build it into the query expression
if (parms.Any(p => p.Key == "ids"))
{
List <int> value = parms["ids"].ToString().Split(',').Select(int.Parse).ToList();
MemberExpression propertyExpression = Expression.Property(paramExpression, "Id");
ConstantExpression constantExpression = Expression.Constant(value, typeof(List <int>));
Expression containsExpression = Expression.Call(constantExpression, typeof(List <int>).GetMethod("Contains", new Type[] { typeof(int) }), propertyExpression);
if (queryExpression != null)
{
queryExpression = Expression.AndAlso(queryExpression, containsExpression);
}
else
{
queryExpression = containsExpression;
}
hasFilter = true;
}
var getResult = getMethod.Invoke(serviceInstance, new object[] { paramExpression, queryExpression, null, null });
var queryResult = getResult as IQueryable <IEntity>;
// process entity specific filters
switch (EntityName)
{
case "person":
{
queryResult = PersonFilters((IQueryable <Person>)queryResult, parms);
break;
}
case "business":
{
queryResult = BusinessFilters((IQueryable <Person>)queryResult, parms);
break;
}
}
// if there was a dynamic expression add it now
if (parms.Any(p => p.Key == "expression"))
{
queryResult = queryResult.Where(parms["expression"]);
hasFilter = true;
}
var queryResultExpression = queryResult.Expression;
// add sort expressions
if (parms.Any(p => p.Key == "sort"))
{
string orderByMethod = "OrderBy";
foreach (var column in parms["sort"].Split(',').Select(x => x.Trim()).Where(x => !string.IsNullOrWhiteSpace(x)).ToList())
{
string propertyName;
var direction = SortDirection.Ascending;
if (column.EndsWith(" desc", StringComparison.OrdinalIgnoreCase))
{
direction = SortDirection.Descending;
propertyName = column.Left(column.Length - 5);
}
else
{
propertyName = column;
}
string methodName = direction == SortDirection.Descending ? orderByMethod + "Descending" : orderByMethod;
if (entityType.GetProperty(propertyName) != null)
{
// sorting a entity property
var memberExpression = Expression.Property(paramExpression, propertyName);
LambdaExpression sortSelector = Expression.Lambda(memberExpression, paramExpression);
queryResultExpression = Expression.Call(typeof(Queryable), methodName, new Type[] { queryResult.ElementType, sortSelector.ReturnType }, queryResultExpression, sortSelector);
}
else
{
// sorting on an attribute
// get attribute id
int?attributeId = null;
foreach (var attribute in AttributeCache.GetByEntityType(entityTypeCache.Id))
{
if (attribute.Key == propertyName)
{
attributeId = attribute.Id;
break;
}
}
if (attributeId.HasValue)
{
// get AttributeValue queryable and parameter
if (dbContext is RockContext)
{
var attributeValues = new AttributeValueService(dbContext as RockContext).Queryable();
ParameterExpression attributeValueParameter = Expression.Parameter(typeof(AttributeValue), "v");
MemberExpression idExpression = Expression.Property(paramExpression, "Id");
var attributeExpression = Attribute.Helper.GetAttributeValueExpression(attributeValues, attributeValueParameter, idExpression, attributeId.Value);
LambdaExpression sortSelector = Expression.Lambda(attributeExpression, paramExpression);
queryResultExpression = Expression.Call(typeof(Queryable), methodName, new Type[] { queryResult.ElementType, sortSelector.ReturnType }, queryResultExpression, sortSelector);
}
else
{
throw new Exception(string.Format("The database context for type {0} does not support RockContext attribute value queries.", entityTypeCache.FriendlyName));
}
}
}
orderByMethod = "ThenBy";
}
}
// check to ensure we had some form of filter (otherwise we'll return all results in the table)
if (!hasFilter)
{
throw new Exception("Your Lava command must contain at least one valid filter. If you configured a filter it's possible that the property or attribute you provided does not exist.");
}
// Disable lazy loading if requested
if (parms.Any(p => p.Key == "lazyloadenabled"))
{
if (!parms["lazyloadenabled"].AsBoolean())
{
dbContext.Configuration.LazyLoadingEnabled = false;
}
}
// Reassemble the queryable with the sort expressions
queryResult = queryResult.Provider.CreateQuery(queryResultExpression) as IQueryable <IEntity>;
// Add included entities
if (parms.Any(p => p.Key == "include"))
{
var includeList = parms["include"].Split(',')
.Select(x => x.Trim())
.Where(x => !string.IsNullOrWhiteSpace(x));
foreach (var includeItem in includeList)
{
queryResult = queryResult.Include(includeItem);
}
}
if (parms.GetValueOrNull("count").AsBoolean())
{
int countResult = queryResult.Count();
context.SetMergeField("count", countResult, LavaContextRelativeScopeSpecifier.Root);
}
else
{
// Run security check on each result if enabled and entity is not a person (we do not check security on people)
if (parms["securityenabled"].AsBoolean() && EntityName != "person")
{
var items = queryResult.ToList();
var itemsSecured = new List <IEntity>();
Person person = GetCurrentPerson(context);
foreach (IEntity item in items)
{
ISecured itemSecured = item as ISecured;
if (itemSecured == null || itemSecured.IsAuthorized(Authorization.VIEW, person))
{
itemsSecured.Add(item);
/*
* 8/13/2020 - JME
* It might seem logical to break out of the loop if there is limit parameter provided once the
* limit is reached. This though has two issues.
*
* FIRST
* Depending how it was implemented it can have the effect of breaking when an offset is
* provided.
* {% contentchannelitem where:'ContentChannelId == 1' limit:'3' %}
* {% for item in contentchannelitemItems %}
* {{ item.Id }} - {{ item.Title }}<br>
* {% endfor %}
* {% endcontentchannelitem %}
* Returns 3 items (correct)
*
* {% contentchannelitem where:'ContentChannelId == 1' limit:'3' offset:'1' %}
* {% for item in contentchannelitemItems %}
* {{ item.Id }} - {{ item.Title }}<br>
* {% endfor %}
* {% endcontentchannelitem %}
* Returns only 2 items (incorrect) - because of the offset
*
* SECOND
* If the limit is moved before the security check it's possible that the security checks
* will remove items and will therefore not give you the amount of items that you asked for.
*
* Unfortunately this has to be an inefficent process to ensure pagination works. I will also
* add a detailed note to the documentation to encourage people to disable security checks,
* especially when used with pagination, in the Lava docs.
*/
}
}
queryResult = itemsSecured.AsQueryable();
}
// offset
if (parms.Any(p => p.Key == "offset"))
{
queryResult = queryResult.Skip(parms["offset"].AsInteger());
}
// limit, default to 1000
if (parms.Any(p => p.Key == "limit"))
{
queryResult = queryResult.Take(parms["limit"].AsInteger());
}
else
{
queryResult = queryResult.Take(1000);
}
// Process logic to be able to return an anonymous types and group bys. We have to abstract the return types as
// the select returns a type of List<dynamic> while the normal entity command returns List<IEntity>.
// Using a type of List<object> for both did not work (that would have eliminated the need for the
// firstItem and returnCount.
object returnValues = null;
object firstItem = null;
int returnCount = 0;
if (parms.ContainsKey("groupby") || parms.ContainsKey("select") || parms.ContainsKey("selectmany"))
{
/*
* 3/1/2021 - JME
* Ensure that lazy loading is enabled. If this is false it throws a null reference exception.
* I confirmed that the anonymous type is getting it's data from the single source SQL (no lazy loading).
* Not sure why this exception is happening. It looks to be within the ZZZ Project System.Linq.Dynamic.Core
* package. The important part is that the data is coming back in a single query.
*/
dbContext.Configuration.LazyLoadingEnabled = true;
List <dynamic> results = null;
// Logic here is a groupby has to have a select, but a select doesn't need a groupby.
if (parms.ContainsKey("groupby") && parms.ContainsKey("select"))
{
results = queryResult.Cast(entityType)
.GroupBy(parms["groupby"])
.Select(parms["select"])
.ToDynamicList();
}
else
{
if (parms.ContainsKey("select"))
{
results = queryResult.Cast(entityType)
.Select(parms["select"])
.ToDynamicList();
}
else // selectmany
{
results = queryResult.Cast(entityType)
.SelectMany(parms["selectmany"])
.ToDynamicList();
}
}
returnValues = results;
firstItem = results.FirstOrDefault();
returnCount = results.Count();
}
else
{
var results = queryResult.ToList();
returnValues = results;
firstItem = results.FirstOrDefault();
returnCount = results.Count();
}
// Add the result to the current context.
context.SetMergeField(parms["iterator"], returnValues, LavaContextRelativeScopeSpecifier.Current);
if (returnCount == 1)
{
// If there is only one item, set a singleton variable in addition to the result list.
context.SetMergeField(EntityName, firstItem, LavaContextRelativeScopeSpecifier.Current);
}
}
}
}
}
else
{
result.Write(string.Format("Could not find a model for {0}.", EntityName));
base.OnRender(context, result);
}
base.OnRender(context, result);
}
/// <summary>
/// Renders the specified context.
/// </summary>
/// <param name="context">The context.</param>
/// <param name="result">The result.</param>
public override void OnRender(ILavaRenderContext context, TextWriter result)
{
// first ensure that entity commands are allowed in the context
if (!this.IsAuthorized(context))
{
result.Write(string.Format(LavaBlockBase.NotAuthorizedMessage, this.SourceElementName));
base.OnRender(context, result);
return;
}
var parms = ParseMarkup(_markup, context);
if (!string.IsNullOrWhiteSpace(parms["url"]))
{
dynamic responseData = null;
try {
var client = new RestClient(parms["url"].ToString());
var request = new RestRequest(parms["method"].ToUpper().ConvertToEnum <Method>(Method.GET));
client.Timeout = parms["timeout"].AsInteger();
// handle basic auth
if (!string.IsNullOrWhiteSpace(parms["basicauth"]))
{
string[] authParts = parms["basicauth"].Split(',');
if (authParts.Length == 2)
{
client.Authenticator = new HttpBasicAuthenticator(authParts[0], authParts[1]);
}
}
// add query string parms
if (!string.IsNullOrWhiteSpace(parms["parameters"]))
{
foreach (var queryString in parms["parameters"].ToKeyValuePairList())
{
request.AddParameter(queryString.Key, queryString.Value);
}
}
// add headers
if (!string.IsNullOrWhiteSpace(parms["headers"]))
{
foreach (var header in parms["headers"].ToKeyValuePairList())
{
request.AddHeader(header.Key, header.Value.ToString());
}
}
// add body, this will be ignored if other parameters exist
if (!string.IsNullOrWhiteSpace(parms["body"]))
{
if (parms.ContainsKey("requestcontenttype"))
{
request.AddParameter(parms["requestcontenttype"], parms["body"], ParameterType.RequestBody);
}
else
{
result.Write("When using the 'body' parameter you must also provide a 'requestcontenttype' also.");
base.OnRender(context, result);
return;
}
}
System.Net.ServicePointManager.SecurityProtocol = System.Net.SecurityProtocolType.Tls12;
IRestResponse response = client.Execute(request);
if (response.StatusCode == System.Net.HttpStatusCode.OK)
{
var content = response.Content;
var contentType = parms["responsecontenttype"].ToLower();
if (contentType == "xml")
{
responseData = new ExpandoObject();
var doc = XDocument.Parse(response.Content);
ExpandoObjectHelper.Parse(responseData, doc.Root);
}
else if (contentType == "json")
{
var converter = new ExpandoObjectConverter();
// determine if the return type is an array or not
if (content.Trim().Substring(0, 1) == "[")
{
responseData = JsonConvert.DeserializeObject <List <ExpandoObject> >(content, converter); // array
}
else
{
responseData = JsonConvert.DeserializeObject <ExpandoObject>(content, converter); // not an array
}
}
else // otherwise assume html and just throw the contents out to the screen
{
responseData = content;
}
}
else if (response.ErrorException != null)
{
responseData = $"Error: {response.ErrorMessage}";
}
else
{
responseData = $"{response.StatusCode}: {response.Content}";
}
} catch
{
throw;
}
context.SetMergeField(parms["return"], responseData);
}
else
{
result.Write("No url parameter was found.");
}
base.OnRender(context, result);
}
