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