/// <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); }