public string GetHtmlForSqlBlock(Guid blockGuid) { RockContext rockContext = new RockContext(); Block block = new BlockService(rockContext).Get(blockGuid); if (block != null) { block.LoadAttributes(); string lavaTemplate = block.GetAttributeValue("LavaTemplate"); string sql = block.GetAttributeValue("Sql"); var results = DbService.GetDataSet(sql.ToString(), CommandType.Text, null, null); var dropRows = new List <DataRowDrop>(); if (results.Tables.Count == 1) { foreach (DataRow row in results.Tables[0].Rows) { dropRows.Add(new DataRowDrop(row)); } } Dictionary <string, object> mergeValues = new Dictionary <string, object>(); mergeValues.Add("Rows", dropRows); return(lavaTemplate.ResolveMergeFields(mergeValues)); } return(string.Format( @"<div class='alert alert-danger'> unable to find block_guid: {0} </div>", blockGuid)); }
public IEnumerable <Dictionary <string, object> > GetJavascriptForSqlBlock(Guid blockGuid) { RockContext rockContext = new RockContext(); Block block = new BlockService(rockContext).Get(blockGuid); if (block != null) { block.LoadAttributes(); string sql = block.GetAttributeValue("Sql"); var parameters = ActionContext.Request.GetQueryStrings() .AsQueryable() .ToDictionary(x => x.Key, x => ( object )x.Value); var results = DbService.GetDataSet(sql.ToString(), CommandType.Text, parameters, null); var rows = new List <Dictionary <string, object> >(); foreach (DataRow row in results.Tables[0].Rows) { var r = new Dictionary <string, object>(); foreach (DataColumn column in results.Tables[0].Columns) { r.Add(column.ColumnName, row[column]); } rows.Add(r); } return(rows); } return(null); }
public string GetHtmlForBlock(int blockId, int?entityTypeId = null, int?entityId = null) { RockContext rockContext = this.Service.Context as RockContext ?? new RockContext(); Block block = new BlockService(rockContext).Get(blockId); if (block != null) { block.LoadAttributes(); string liquidTemplate = block.GetAttributeValue("LiquidTemplate"); var metricCategoryPairList = Rock.Attribute.MetricCategoriesFieldAttribute.GetValueAsGuidPairs(block.GetAttributeValue("MetricCategories")); var metricGuids = metricCategoryPairList.Select(a => a.MetricGuid).ToList(); bool roundYValues = block.GetAttributeValue("RoundValues").AsBooleanOrNull() ?? true; MetricService metricService = new MetricService(rockContext); var metrics = metricService.GetByGuids(metricGuids).Include(a => a.MetricPartitions); List <object> metricsData = new List <object>(); if (metrics.Count() == 0) { return(@"<div class='alert alert-warning'> Please select a metric in the block settings. </div>" ); } MetricValueService metricValueService = new MetricValueService(rockContext); DateTime firstDayOfYear = new DateTime(RockDateTime.Now.Year, 1, 1); DateTime currentDateTime = RockDateTime.Now; DateTime firstDayOfNextYear = new DateTime(RockDateTime.Now.Year + 1, 1, 1); foreach (var metric in metrics) { var metricYTDData = JsonConvert.DeserializeObject(metric.ToJson(), typeof(MetricYTDData)) as MetricYTDData; var qryMeasureValues = metricValueService.Queryable() .Where(a => a.MetricId == metricYTDData.Id) .Where(a => a.MetricValueDateTime >= firstDayOfYear && a.MetricValueDateTime < currentDateTime) .Where(a => a.MetricValueType == MetricValueType.Measure); //// if an entityTypeId/EntityId filter was specified, and the entityTypeId is the same as the metric's partitions' EntityTypeId, filter the values to the specified entityId //// Note: if a Metric or it's Metric Value doesn't have a context, include it regardless of Context setting if (entityTypeId.HasValue && entityId.HasValue) { if (metric.MetricPartitions.Any(a => a.EntityTypeId == entityTypeId.Value)) { qryMeasureValues = qryMeasureValues.Where(a => a.MetricValuePartitions.Any(p => p.EntityId == entityId.Value && p.MetricPartition.EntityTypeId == entityTypeId)); } } var lastMetricValue = qryMeasureValues.OrderByDescending(a => a.MetricValueDateTime).FirstOrDefault(); if (lastMetricValue != null) { metricYTDData.LastValueDate = lastMetricValue.MetricValueDateTime.HasValue ? lastMetricValue.MetricValueDateTime.Value.Date : DateTime.MinValue; // get a sum of the values that for whole 24 hour day of the last Date DateTime lastValueDateEnd = metricYTDData.LastValueDate.AddDays(1); var lastMetricCumulativeValues = qryMeasureValues.Where(a => a.MetricValueDateTime.HasValue && a.MetricValueDateTime.Value >= metricYTDData.LastValueDate && a.MetricValueDateTime.Value < lastValueDateEnd); metricYTDData.LastValue = lastMetricCumulativeValues.Sum(a => a.YValue).HasValue ? Math.Round(lastMetricCumulativeValues.Sum(a => a.YValue).Value, roundYValues ? 0 : 2) : (decimal?)null; } decimal?sum = qryMeasureValues.Sum(a => a.YValue); metricYTDData.CumulativeValue = sum.HasValue ? Math.Round(sum.Value, roundYValues ? 0 : 2) : (decimal?)null; // figure out goal as of current date time by figuring out the slope of the goal var qryGoalValuesCurrentYear = metricValueService.Queryable() .Where(a => a.MetricId == metricYTDData.Id) .Where(a => a.MetricValueDateTime >= firstDayOfYear && a.MetricValueDateTime < firstDayOfNextYear) .Where(a => a.MetricValueType == MetricValueType.Goal); // if an entityTypeId/EntityId filter was specified, and the entityTypeId is the same as the metric's partitions' EntityTypeId, filter the values to the specified entityId if (entityTypeId.HasValue && entityId.HasValue) { if (metric.MetricPartitions.Any(a => a.EntityTypeId == entityTypeId.Value)) { qryGoalValuesCurrentYear = qryGoalValuesCurrentYear.Where(a => a.MetricValuePartitions.Any(p => p.EntityId == entityId.Value && p.MetricPartition.EntityTypeId == entityTypeId)); } } MetricValue goalLineStartPoint = qryGoalValuesCurrentYear.Where(a => a.MetricValueDateTime <= currentDateTime).OrderByDescending(a => a.MetricValueDateTime).FirstOrDefault(); MetricValue goalLineEndPoint = qryGoalValuesCurrentYear.Where(a => a.MetricValueDateTime >= currentDateTime).FirstOrDefault(); if (goalLineStartPoint != null && goalLineEndPoint != null) { var changeInX = goalLineEndPoint.DateTimeStamp - goalLineStartPoint.DateTimeStamp; var changeInY = goalLineEndPoint.YValue - goalLineStartPoint.YValue; if (changeInX != 0) { decimal?slope = changeInY / changeInX; decimal goalValue = ((slope * (currentDateTime.ToJavascriptMilliseconds() - goalLineStartPoint.DateTimeStamp)) + goalLineStartPoint.YValue).Value; metricYTDData.GoalValue = Math.Round(goalValue, roundYValues ? 0 : 2); } } else { // if there isn't a both a start goal and end goal within the date range, there wouldn't be a goal line shown in a line chart, so don't display a goal in liquid either metricYTDData.GoalValue = null; } metricsData.Add(metricYTDData.ToLiquid()); } Dictionary <string, object> mergeValues = new Dictionary <string, object>(); mergeValues.Add("Metrics", metricsData); string resultHtml = liquidTemplate.ResolveMergeFields(mergeValues); // show liquid help for debug if (block.GetAttributeValue("EnableDebug").AsBoolean()) { resultHtml += mergeValues.lavaDebugInfo(); } return(resultHtml); } return(string.Format( @"<div class='alert alert-danger'> unable to find block_id: {0} </div>", blockId)); }