/// <summary> /// Gets the metric values. /// </summary> /// <param name="isPrimary">if set to <c>true</c> [is primary].</param> /// <returns></returns> protected List <MetricValue> GetMetricValues(bool isPrimary) { var rockContext = new RockContext(); var metricService = new MetricService(rockContext); List <Guid> sourceGuids = null; var preKey = isPrimary ? string.Empty : "Comparison"; IQueryable <MetricValue> metricValues = null; var attributeValue = GetAttributeValue(preKey + "MetricSource"); if (string.IsNullOrWhiteSpace(attributeValue)) { attributeValue = string.Empty; } var pairs = MetricCategoriesFieldAttribute.GetValueAsGuidPairs(attributeValue); sourceGuids = pairs.Select(p => p.MetricGuid).ToList(); if (sourceGuids.Any()) { metricValues = metricService.GetByGuids(sourceGuids).SelectMany(m => m.MetricValues); } else { nbMetricWarning.Visible = true; pnlMetricDisplay.Visible = false; return(null); } if (GetAttributeValue(preKey + "RespectCampusContext").AsBoolean()) { var campusContext = RockPage.GetCurrentContext(EntityTypeCache.Get(typeof(Campus))); if (campusContext != null) { metricValues = FilterMetricValuesByPartition(metricValues, "Campus", campusContext.Id); } } if (GetAttributeValue(preKey + "RespectGroupContext").AsBoolean()) { var groupTypeContext = RockPage.GetCurrentContext(EntityTypeCache.Get(typeof(GroupType))); var groupContext = RockPage.GetCurrentContext(EntityTypeCache.Get(typeof(Group))); if (groupContext != null) { metricValues = FilterMetricValuesByPartition(metricValues, "Group", groupContext.Id); } else if (groupTypeContext != null) { var groupTypeIds = new GroupTypeService(rockContext).GetAllAssociatedDescendents(groupTypeContext.Id).Select(gt => gt.Id); var groupIds = new GroupService(rockContext).Queryable().Where(g => groupTypeIds.Contains(g.GroupTypeId)).Select(g => g.Id); metricValues = metricValues.Where(a => a.MetricValuePartitions.Any(mvp => mvp.MetricPartition.Label == "Group" && groupIds.Any(i => i == mvp.EntityId))); } } if (GetAttributeValue(preKey + "RespectDateContext").AsBoolean()) { var dateRangeString = RockPage.GetUserPreference(ContextPreferenceName); if (!string.IsNullOrWhiteSpace(dateRangeString)) { var dateRange = SlidingDateRangePicker.CalculateDateRangeFromDelimitedValues(dateRangeString); metricValues = metricValues.Where(v => v.MetricValueDateTime >= dateRange.Start && v.MetricValueDateTime <= dateRange.End); } } if (GetAttributeValue(preKey + "RespectScheduleContext").AsBoolean()) { var scheduleContext = RockPage.GetCurrentContext(EntityTypeCache.Get(typeof(Schedule))); if (scheduleContext != null) { metricValues = FilterMetricValuesByPartition(metricValues, "Schedule", scheduleContext.Id); } } return(metricValues.ToList()); }
// <summary> /// Raises the <see cref="E:System.Web.UI.Control.Init" /> event. /// </summary> /// <param name="e">An <see cref="T:System.EventArgs" /> object that contains the event data.</param> protected override void OnLoad(EventArgs e) { base.OnLoad(e); if (!Page.IsPostBack) { // check the page context bool pageScope = GetAttributeValue("ContextScope") == "Page"; // If the blocks respect page context let's set those vars if (pageScope) { // Get Current Campus Context CampusContext = RockPage.GetCurrentContext(EntityTypeCache.Read(typeof(Campus))); // Get Current Schedule Context ScheduleContext = RockPage.GetCurrentContext(EntityTypeCache.Read(typeof(Schedule))); // Get Current Group Context GroupContext = RockPage.GetCurrentContext(EntityTypeCache.Read(typeof(Group))); // Get Current GroupType Context GroupTypeContext = RockPage.GetCurrentContext(EntityTypeCache.Read(typeof(GroupType))); } // Output variables direct to the ascx metricBlockNumber.Value = BlockId.ToString(); metricBlockId.Value = BlockName.Replace(" ", "").ToString(); metricTitle.Value = BlockName; metricDisplay.Value = GetAttributeValue("MetricDisplayType"); metricWidth.Value = GetAttributeValue("NumberofColumns"); PrimaryMetricKey = GetAttributeValue("PrimaryMetricKey"); ComparisonMetricKey = GetAttributeValue("ComparisonMetricKey"); var churchMetricPeriod = GetAttributeValue("MetricPeriod"); var metricComparison = GetAttributeValue("MetricComparison"); var metricDisplayType = GetAttributeValue("MetricDisplayType"); var rockContext = new RockContext(); var metricService = new MetricService(rockContext); var primarySourceGuids = GetAttributeValue("PrimaryMetricSource") .SplitDelimitedValues() .AsGuidList(); var comparisonSourceGuids = GetAttributeValue("ComparisonMetricSource") .SplitDelimitedValues() .AsGuidList(); // lookup the metric sources List <int> primaryMetricSource = metricService.GetByGuids(primarySourceGuids) .Select(a => a.Id).ToList(); List <int> comparisonMetricSource = metricService.GetByGuids(comparisonSourceGuids) .Select(a => a.Id).ToList(); DateRange dateRange = new DateRange(DateTime.Now.AddMonths(-6), DateTime.Now); // Show data if metric source is selected if (primaryMetricSource.Any() || !string.IsNullOrEmpty(PrimaryMetricKey)) { if (metricDisplayType.Equals("Text")) { DisplayTextValue(dateRange, primaryMetricSource, comparisonMetricSource); } else if (metricDisplayType.Equals("Line")) { DisplayLineValue(dateRange, primaryMetricSource); } else if (metricDisplayType.Equals("Donut")) { DisplayDonutValue(dateRange, primaryMetricSource); } } else { // nothing selected, display an error message churchMetricWarning.Visible = true; } } // unused variables // var metricCustomDates = GetAttributeValue( "CustomDates" ); // MetricCompareLastYear = GetAttributeValue( "CompareAgainstLastYear" ).ToString(); // var dateRange = SlidingDateRangePicker.CalculateDateRangeFromDelimitedValues( this.GetAttributeValue( "SlidingDateRange" ) ?? string.Empty ); }
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)); }
private List <StatisticRow> GetDataForDateRange(DateTime startDate, DateTime endDate) { var entityTypeGroupGuid = Rock.SystemGuid.EntityType.GROUP.AsGuid(); var groupEntityType = EntityTypeCache.Read(entityTypeGroupGuid); int entityTypeScheduleEntityId = EntityTypeCache.Read(Rock.SystemGuid.EntityType.SCHEDULE.AsGuid()).Id; var rockContext = new RockContext(); rockContext.Database.CommandTimeout = 2600; var metricService = new MetricService(rockContext); var metricValueService = new MetricValueService(rockContext); var scheduleService = new ScheduleService(rockContext); var groupService = new GroupService(rockContext); var attendanceService = new AttendanceService(rockContext); var attributeService = new AttributeService(rockContext); var attributeValueService = new AttributeValueService(rockContext); var metricCategoryGuidList = GetAttributeValue("Metrics").SplitDelimitedValues().AsGuidList(); var attendanceGroupGuidList = GetAttributeValue("AttendanceGroups").SplitDelimitedValues().AsGuidList(); var parentMetricVolunteerGroupGuids = GetAttributeValue("ServiceVolunteerGroups").SplitDelimitedValues().AsGuidList(); var attendanceGroups = groupService.GetByGuids(attendanceGroupGuidList); var parentMetricVolunteerGroups = groupService.GetByGuids(parentMetricVolunteerGroupGuids); var metrics = metricService.GetByGuids(metricCategoryGuidList).Distinct().ToList(); var datasource = new List <StatisticRow>(); var metricVolunteerGroups = new List <Group>(); foreach (var parentMetricVolunteerGroup in parentMetricVolunteerGroups) { metricVolunteerGroups.Add(parentMetricVolunteerGroup); metricVolunteerGroups.AddRange(groupService.GetAllDescendents(parentMetricVolunteerGroup.Id)); } var metricVolunteerGroupIds = metricVolunteerGroups.Select(g => g.Id).ToList(); var metricVolunteerAttendanceData = attendanceService.Queryable().Where(a => a.GroupId.HasValue && metricVolunteerGroupIds.Contains(a.GroupId.Value) && a.StartDateTime >= startDate && a.StartDateTime <= endDate); foreach (var metric in metrics) { var metricData = metricValueService.Queryable("MetricValuePartitions").Where(mv => mv.MetricValueDateTime >= startDate && mv.MetricValueDateTime <= endDate && mv.MetricId == metric.Id && mv.MetricValuePartitions.FirstOrDefault( mvp => mvp.MetricPartition.EntityTypeId == entityTypeScheduleEntityId).EntityId.HasValue ) .GroupBy( mv => mv.MetricValuePartitions.FirstOrDefault( mvp => mvp.MetricPartition.EntityTypeId == entityTypeScheduleEntityId).EntityId.Value) .ToList() .Select(mv => { var service = scheduleService.Get(mv.Key); return(new StatisticRow { ScheduleDateRanges = GetScheduleDateRanges(service, startDate, endDate), RowId = metric.Id + "-" + mv.Key, SortValue = 0, IsTotal = false, Area = metric.Title, Subarea = "Head Count", StartTime = service.WeeklyTimeOfDay ?? service.StartTimeOfDay, DayOfWeek = service.WeeklyDayOfWeek ?? GetLastDayOfWeek(service, startDate, endDate), Service = service.Name, Count = mv.Sum(a => a.YValue).HasValue ? decimal.ToInt32(mv.Sum(a => a.YValue).Value) : 0, MetricNote = mv.Max(a => a.Note), Value = mv }); }) .ToList(); foreach (var row in metricData) { int volunteers = 0; int total = row.Value.Sum(a => a.YValue).HasValue ? decimal.ToInt32(row.Value.Sum(a => a.YValue).Value) : 0; if (metricVolunteerAttendanceData.Any()) { volunteers += row.ScheduleDateRanges.Sum(dateRange => metricVolunteerAttendanceData.Count(a => (a.DidAttend == null || a.DidAttend.Value) && a.StartDateTime >= dateRange.Start && a.StartDateTime <= dateRange.End)); row.Total = total + volunteers; row.Volunteers = volunteers; } } datasource.AddRange(metricData); if (metricData.Count > 1) { var subTotalRow = new StatisticRow { RowId = metric.Id.ToString(), SortValue = 0, IsTotal = true, Area = metric.Title, Subarea = "Head Count", Service = "Sub-Total", Count = metricData.Sum(mv => mv.Count), Volunteers = metricData.Sum(mv => mv.Volunteers), Total = metricData.Sum(mv => mv.Total) }; datasource.Add(subTotalRow); } } var totalRow = new StatisticRow { RowId = "HeadcountTotal", SortValue = 1, IsTotal = true, Area = "Head Count Total", Subarea = "Head Count", Service = "Total", Count = datasource.Where(row => !row.IsTotal).Sum(row => row.Count), Volunteers = datasource.Where(row => !row.IsTotal).Sum(mv => mv.Volunteers), Total = datasource.Where(row => !row.IsTotal).Sum(mv => mv.Total) }; datasource.Add(totalRow); string attributeKeyString = GetAttributeValue("VolunteerGroupAttributeKey"); var volunteerGroupAttributeIdList = attributeService.Queryable() .Where(a => a.Key == attributeKeyString && a.EntityTypeQualifierColumn == "GroupTypeId" && a.EntityTypeId == groupEntityType.Id).Select(a => a.Id); if (volunteerGroupAttributeIdList.Any()) { // Find the groups that attribute values that have the maaping between group (the entityId) and the place they should be grouped with attending (value) var volunteerGroupMappingList = attributeValueService.Queryable().Where(av => volunteerGroupAttributeIdList.Contains(av.AttributeId) && av.Value != null) .ToList() .Select(av => new { VolunteerAttendanceGroupGuid = av.Value.AsGuid(), VolunteerGroupId = av.EntityId }).ToList(); foreach (var attendanceGroup in attendanceGroups) { foreach (var attendanceChildGroup in attendanceGroup.Groups) { var attendanceChildDescendantGroups = groupService.GetAllDescendents(attendanceChildGroup.Id).ToList(); // Include child group in for cases where attendance needs to be mapped to an area not a specific group (production team isn't for a specific children's group -- it's associated with TC kids as a whole) attendanceChildDescendantGroups.Add(attendanceChildGroup); var attendanceChildDescendantGroupIds = attendanceChildDescendantGroups.Select(g => g.Id); var volunteerGroupIds = volunteerGroupMappingList .Where(vgm => attendanceChildDescendantGroups.Any(g => g.Guid == vgm.VolunteerAttendanceGroupGuid)) .Select(vgm => vgm.VolunteerGroupId).ToList(); var volunteerGroupAttendance = attendanceService.Queryable() .Where(a => volunteerGroupIds.Any(id => id != null && id == a.Group.Id) && a.StartDateTime >= startDate && a.StartDateTime <= endDate) .ToList(); var acg = attendanceChildGroup; var childGroupAttendance = attendanceService.Queryable().Where(a => a.GroupId != null && a.StartDateTime >= startDate && a.StartDateTime <= endDate && (a.GroupId == acg.Id || attendanceChildDescendantGroupIds.Any(id => id == a.GroupId)) && (a.DidAttend == null || a.DidAttend.Value)) .GroupBy(a => a.ScheduleId) .ToList(); // ag is created to prevent a warn "Access to foreach variable in closure." var ag = attendanceGroup; var statisticRows = childGroupAttendance.Select(a => { var attendance = a.FirstOrDefault(); var scheduleDateRanges = GetScheduleDateRanges(attendance.Schedule, startDate, endDate); var row = new StatisticRow(); row.RowId = acg.Id + "-" + a.Key; row.SortValue = 2; row.IsTotal = false; row.Area = ag.Name; row.Subarea = acg.Name; row.Service = attendance.Schedule.Name; row.StartTime = attendance.Schedule.WeeklyTimeOfDay ?? attendance.Schedule.StartTimeOfDay; row.DayOfWeek = attendance.Schedule.WeeklyDayOfWeek ?? GetLastDayOfWeek(attendance.Schedule, startDate, endDate); row.Count = a.Count(); row.Volunteers = volunteerGroupAttendance.Count(b => scheduleDateRanges.Any(s => b.StartDateTime >= s.Start && b.StartDateTime <= s.End)); row.Total = a.Count() + volunteerGroupAttendance.Count(b => scheduleDateRanges.Any(s => b.StartDateTime >= s.Start && b.StartDateTime <= s.End)); return(row); }).ToList(); datasource.AddRange(statisticRows); if (statisticRows.Count > 1) { var subTotalRow = new StatisticRow { RowId = attendanceChildGroup.Id.ToString(), SortValue = 2, IsTotal = true, Area = attendanceGroup.Name, Subarea = attendanceChildGroup.Name, Service = "Sub-Total", Count = statisticRows.Sum(cg => cg.Count), Volunteers = statisticRows.Sum(cg => cg.Volunteers), Total = statisticRows.Sum(cg => cg.Total) }; datasource.Add(subTotalRow); } } } } datasource.Add(new StatisticRow { RowId = "Total", SortValue = 3, IsTotal = true, Area = "Grand Total", Subarea = "Total", Service = "Total", Count = datasource.Where(ds => ds.IsTotal).Sum(cg => cg.Count), Volunteers = datasource.Where(ds => ds.IsTotal).Sum(cg => cg.Volunteers), Total = datasource.Where(ds => ds.IsTotal).Sum(cg => cg.Total) }); return(datasource); }