private IQueryable <MetricValue> GetMetricData(int metricId)
{
var rockContext = new RockContext();
var metricValueService = new MetricValueService(rockContext);
var qry = metricValueService.Queryable("Metric").AsNoTracking();
qry = qry.Where(a => a.MetricId == metricId);
var drp = new DateRangePicker();
drp.DelimitedValues = gfMetricValues.GetUserPreference("Date Range");
if (drp.LowerValue.HasValue)
{
qry = qry.Where(a => a.MetricValueDateTime >= drp.LowerValue.Value);
}
if (drp.UpperValue.HasValue)
{
var upperDate = drp.UpperValue.Value.Date.AddDays(1);
qry = qry.Where(a => a.MetricValueDateTime < upperDate);
}
var campusId = ddlCampuses.SelectedValueAsId();
if (ddlCampuses.SelectedValue.HasValue())
{
qry = qry.Where(mv => mv.MetricValuePartitions.Any(mvp => mvp.EntityId == campusId || mvp.EntityId == null));
}
return(qry);
}
/// <summary>
/// Handles the Delete event of the gMetricValues control.
/// </summary>
/// <param name="sender">The source of the event.</param>
/// <param name="e">The <see cref="RowEventArgs" /> instance containing the event data.</param>
protected void gMetricValues_Delete(object sender, RowEventArgs e)
{
var rockContext = new RockContext();
var metricValueService = new MetricValueService(rockContext);
var metricValuePartitionService = new MetricValuePartitionService(rockContext);
var metricValue = metricValueService.Get(e.RowKeyId);
if (metricValue != null)
{
string errorMessage;
if (!metricValueService.CanDelete(metricValue, out errorMessage))
{
mdGridWarning.Show(errorMessage, ModalAlertType.Information);
return;
}
rockContext.WrapTransaction(() =>
{
metricValuePartitionService.DeleteRange(metricValue.MetricValuePartitions);
metricValueService.Delete(metricValue);
rockContext.SaveChanges();
});
}
BindGrid();
}
/// <summary>
/// Handles the Click event of the btnSaveValue control.
/// </summary>
/// <param name="sender">The source of the event.</param>
/// <param name="e">The <see cref="EventArgs"/> instance containing the event data.</param>
protected void btnSaveValue_Click(object sender, EventArgs e)
{
using (new Rock.Data.UnitOfWorkScope())
{
int metricValueId = hfMetricValueId.ValueAsInt();
var metricValueService = new MetricValueService();
MetricValue metricValue;
if (metricValueId == 0)
{
metricValue = new MetricValue();
metricValue.IsSystem = false;
metricValue.MetricId = hfMetricId.ValueAsInt();
metricValueService.Add(metricValue, CurrentPersonId);
}
else
{
metricValue = metricValueService.Get(metricValueId);
}
metricValue.Value = tbValue.Text;
metricValue.Description = tbValueDescription.Text;
metricValue.xValue = tbXValue.Text;
metricValue.Label = tbLabel.Text;
metricValue.isDateBased = cbIsDateBased.Checked;
metricValue.MetricId = Int32.Parse(ddlMetricFilter.SelectedValue);
metricValueService.Save(metricValue, CurrentPersonId);
}
BindGrid();
modalValue.Hide();
}
public void MetricValueDateKeyJoinsCorrectly()
{
var expectedRecordCount = 15;
var year = 2015;
var rockContext = new RockContext();
var metricValueService = new MetricValueService(rockContext);
var minDateValue = TestDataHelper.GetAnalyticsSourceMinDateForYear(rockContext, year);
var maxDateValue = TestDataHelper.GetAnalyticsSourceMaxDateForYear(rockContext, year);
for (var i = 0; i < 15; i++)
{
var metricValue = BuildMetricValue(rockContext,
TestDataHelper.GetRandomDateInRange(minDateValue, maxDateValue));
metricValueService.Add(metricValue);
}
rockContext.SaveChanges();
var metricValues = metricValueService.
Queryable("AnalyticsSourceDate").
Where(i => i.ForeignKey == metricValueForeignKey).
Where(i => i.MetricValueSourceDate.CalendarYear == year);
Assert.AreEqual(expectedRecordCount, metricValues.Count());
}
/// <summary>
/// Raises the <see cref="E:System.Web.UI.Control.Load" /> event.
/// </summary>
/// <param name="e">The <see cref="T:System.EventArgs" /> object that contains the event data.</param>
protected override void OnLoad(EventArgs e)
{
base.OnLoad(e);
if (Page.IsPostBack)
{
// create dynamic controls
var rockContext = new RockContext();
var metricValue = new MetricValueService(rockContext).Get(hfMetricValueId.Value.AsInteger());
if (metricValue == null)
{
metricValue = new MetricValue {
MetricId = hfMetricId.Value.AsInteger()
};
metricValue.Metric = new MetricService(rockContext).Get(metricValue.MetricId);
}
CreateDynamicControls(metricValue, false, false);
}
if (!Page.IsPostBack)
{
int?metricValueId = PageParameter("MetricValueId").AsIntegerOrNull();
// in case called with MetricId as the parent id parameter
int?metricId = PageParameter("MetricId").AsIntegerOrNull();
// in case called with MetricCategoryId as the parent id parameter
int? metricCategoryId = PageParameter("MetricCategoryId").AsIntegerOrNull();
MetricCategory metricCategory = null;
if (metricCategoryId.HasValue)
{
if (metricCategoryId.Value > 0)
{
// editing a metric, but get the metricId from the metricCategory
metricCategory = new MetricCategoryService(new RockContext()).Get(metricCategoryId.Value);
if (metricCategory != null)
{
metricId = metricCategory.MetricId;
}
}
else
{
// adding a new metric. Block will (hopefully) not be shown
metricId = 0;
}
}
hfMetricCategoryId.Value = metricCategoryId.ToString();
if (metricValueId.HasValue)
{
ShowDetail(metricValueId.Value, metricId);
}
else
{
pnlDetails.Visible = false;
}
}
}
protected void bbAdd_OnClick(object sender, EventArgs e)
{
int?metricId = hfMetricId.ValueAsInt();
var dateTimeArray = ddlInstanceTwo.SelectedValue.Split(',');
var dateTime = dateTimeArray[0].AsDateTime();
var value = nbValue.Text.AsIntegerOrNull();
if (metricId == 0 || !dateTime.HasValue || !value.HasValue)
{
nbWarning.Text = "Unable to save. Please check you have selected an instance and input a value. Also ask your administrator to double check your Headcount Metric Sync Groups job is running.";
nbWarning.Visible = true;
return;
}
var rockContext = new RockContext();
var metricValueService = new MetricValueService(rockContext);
var existingMetricValue = metricValueService.Queryable().FirstOrDefault(v => v.MetricValueDateTime.HasValue && v.MetricValueDateTime.Value == dateTime.Value && v.MetricId == metricId);
if (existingMetricValue != null && !string.IsNullOrWhiteSpace(existingMetricValue.MetricValuePartitionEntityIds) && existingMetricValue.MetricValuePartitionEntityIds.Split(',').Any(partition => partition.Split('|')[0].AsInteger() == EntityTypeCache.Get(typeof(Campus)).Id&& partition.Split('|')[1].AsInteger() == ddlCampuses.SelectedValueAsId()))
{
nbWarning.Text =
String.Format(
"A metric value already existed for the {0}, the old value of {1} has been changed to {2}",
dateTime.Value, Decimal.ToInt32(existingMetricValue.YValue.Value), value);
nbWarning.Visible = true;
existingMetricValue.YValue = value;
}
else
{
var metric = new MetricService(rockContext).Get(metricId.Value);
var metricValue = new MetricValue();
metricValue.MetricValueDateTime = dateTime;
metricValue.YValue = value;
metricValue.MetricValuePartitions = new List <MetricValuePartition>();
var metricPartitionsByPosition = metric.MetricPartitions.OrderBy(a => a.Order).ToList();
foreach (var metricPartition in metricPartitionsByPosition)
{
var metricValuePartition = new MetricValuePartition();
metricValuePartition.MetricPartition = metricPartition;
metricValuePartition.MetricPartitionId = metricPartition.Id;
metricValuePartition.MetricValue = metricValue;
metricValuePartition.EntityId = ddlCampuses.SelectedValueAsId();
metricValue.MetricValuePartitions.Add(metricValuePartition);
}
metricValue.MetricId = metricId.Value;
metricValue.Note = "Input as a headcount metric value";
metricValueService.Add(metricValue);
nbWarning.Text = "";
nbWarning.Visible = false;
}
nbValue.Text = "";
rockContext.SaveChanges();
BindGrid();
}
/// <summary>
/// Handles the Click event of the btnSave control.
/// </summary>
/// <param name="sender">The source of the event.</param>
/// <param name="e">The <see cref="EventArgs" /> instance containing the event data.</param>
protected void btnSave_Click(object sender, EventArgs e)
{
MetricValue metricValue;
var rockContext = new RockContext();
MetricValueService metricValueService = new MetricValueService(rockContext);
int metricValueId = int.Parse(hfMetricValueId.Value);
if (metricValueId == 0)
{
metricValue = new MetricValue();
metricValueService.Add(metricValue);
metricValue.MetricId = hfMetricId.ValueAsInt();
metricValue.Metric = metricValue.Metric ?? new MetricService(rockContext).Get(metricValue.MetricId);
}
else
{
metricValue = metricValueService.Get(metricValueId);
}
metricValue.MetricValueType = ddlMetricValueType.SelectedValueAsEnum <MetricValueType>();
metricValue.XValue = tbXValue.Text;
metricValue.YValue = tbYValue.Text.AsDecimalOrNull();
metricValue.Note = tbNote.Text;
metricValue.MetricValueDateTime = dpMetricValueDateTime.SelectedDate;
// Get EntityId from EntityType UI controls
var metricEntityType = EntityTypeCache.Read(metricValue.Metric.EntityTypeId ?? 0);
Control entityTypeEditControl = phEntityTypeEntityIdValue.FindControl("entityTypeEditControl");
if (metricEntityType != null && metricEntityType.SingleValueFieldType != null && metricEntityType.SingleValueFieldType.Field is IEntityFieldType)
{
metricValue.EntityId = (metricEntityType.SingleValueFieldType.Field as IEntityFieldType).GetEditValueAsEntityId(entityTypeEditControl, new Dictionary <string, ConfigurationValue>());
}
else
{
metricValue.EntityId = null;
}
if (!metricValue.IsValid)
{
// Controls will render the error messages
return;
}
rockContext.SaveChanges();
var qryParams = new Dictionary <string, string>();
qryParams.Add("MetricId", hfMetricId.Value);
qryParams.Add("MetricCategoryId", hfMetricCategoryId.Value);
qryParams.Add("ExpandedIds", PageParameter("ExpandedIds"));
NavigateToParentPage(qryParams);
}
/// <summary>
/// Handles the Delete event of the gMetricValues control.
/// </summary>
/// <param name="sender">The source of the event.</param>
/// <param name="e">The <see cref="RowEventArgs"/> instance containing the event data.</param>
protected void gMetricValues_Delete(object sender, RowEventArgs e)
{
var metricValueService = new MetricValueService();
MetricValue metricValue = metricValueService.Get((int)e.RowKeyValue);
if (metricValue != null)
{
metricValueService.Delete(metricValue, CurrentPersonId);
metricValueService.Save(metricValue, CurrentPersonId);
}
BindGrid();
}
/// <summary>
/// Binds the grid.
/// </summary>
private void BindGrid()
{
MetricValueService metricValueService = new MetricValueService(new RockContext());
SortProperty sortProperty = gMetricValues.SortProperty;
var qry = metricValueService.Queryable();
// in case called normally
int?metricId = PageParameter("MetricId").AsInteger(false);
// in case called from CategoryTreeView
int? metricCategoryId = PageParameter("MetricCategoryId").AsInteger(false);
MetricCategory metricCategory = null;
if (metricCategoryId.HasValue)
{
if (metricCategoryId.Value > 0)
{
// editing a metric, but get the metricId from the metricCategory
metricCategory = new MetricCategoryService(new RockContext()).Get(metricCategoryId.Value);
if (metricCategory != null)
{
metricId = metricCategory.MetricId;
}
}
else
{
// adding a new metric. Block will (hopefully) not be shown
metricId = 0;
}
}
hfMetricId.Value = metricId.ToString();
hfMetricCategoryId.Value = metricCategoryId.ToString();
this.Visible = metricId.HasValue;
qry = qry.Where(a => a.MetricId == metricId);
if (sortProperty != null)
{
gMetricValues.DataSource = qry.Sort(sortProperty).ToList();
}
else
{
gMetricValues.DataSource = qry.OrderBy(s => s.Order).ThenBy(s => s.XValue).ThenBy(s => s.MetricValueDateTime).ThenBy(s => s.YValue).ToList();
}
gMetricValues.DataBind();
}
public void MetricValueDateKeySavesCorrectly()
{
var rockContext = new RockContext();
var metricValueService = new MetricValueService(rockContext);
var metricValue = BuildMetricValue(rockContext, Convert.ToDateTime("3/15/2010"));
metricValueService.Add(metricValue);
rockContext.SaveChanges();
var metricValueId = metricValue.Id;
// We're bypassing the model because the model doesn't user the MetricValueDateKey from the database,
// but it still needs to be correct for inner joins to work correctly.
var result = rockContext.Database.
SqlQuery <int>($"SELECT MetricValueDateKey FROM MetricValue WHERE Id = {metricValueId}").First();
Assert.AreEqual(20100315, result);
}
/// <summary>
/// Binds the grid.
/// </summary>
private void BindGrid()
{
int metricId = hfMetricId.ValueAsInt();
var queryable = new MetricValueService().Queryable()
.Where(a => a.MetricId == metricId);
SortProperty sortProperty = gMetricValues.SortProperty;
if (sortProperty != null)
{
queryable = queryable.Sort(sortProperty);
}
else
{
queryable = queryable.OrderBy(a => a.Id).ThenBy(a => a.Value);
}
gMetricValues.DataSource = queryable.ToList();
gMetricValues.DataBind();
}
/// <summary>
/// Shows the edit modal.
/// </summary>
/// <param name="valueId">The value unique identifier.</param>
protected void ShowEdit(int valueId)
{
var metricId = hfMetricId.ValueAsInt();
var metric = new MetricService().Get(metricId);
MetricValue metricValue = null;
if (!valueId.Equals(0))
{
metricValue = new MetricValueService().Get(valueId);
if (metric != null)
{
lActionTitle.Text = ActionTitle.Edit("metric value for " + metric.Title);
}
}
else
{
metricValue = new MetricValue {
Id = 0
};
metricValue.MetricId = metricId;
if (metric != null)
{
lActionTitle.Text = ActionTitle.Add("metric value for " + metric.Title);
}
}
hfMetricValueId.SetValue(metricValue.Id);
ddlMetricFilter.SelectedValue = hfMetricId.Value;
tbValue.Text = metricValue.Value;
tbValueDescription.Text = metricValue.Description;
tbXValue.Text = metricValue.xValue;
tbLabel.Text = metricValue.Label;
cbIsDateBased.Checked = metricValue.isDateBased;
modalValue.Show();
}
/// <summary>
/// Handles the Click event of the btnSave control.
/// </summary>
/// <param name="sender">The source of the event.</param>
/// <param name="e">The <see cref="EventArgs"/> instance containing the event data.</param>
protected void btnSave_Click(object sender, EventArgs e)
{
int campusEntityTypeId = EntityTypeCache.Get(typeof(Rock.Model.Campus)).Id;
int scheduleEntityTypeId = EntityTypeCache.Get(typeof(Rock.Model.Schedule)).Id;
int? campusId = bddlCampus.SelectedValueAsInt();
int? scheduleId = bddlService.SelectedValueAsInt();
DateTime?weekend = bddlWeekend.SelectedValue.AsDateTime();
if (campusId.HasValue && scheduleId.HasValue && weekend.HasValue)
{
using (var rockContext = new RockContext())
{
var metricService = new MetricService(rockContext);
var metricValueService = new MetricValueService(rockContext);
weekend = GetWeekendDate(scheduleId, weekend, rockContext);
foreach (RepeaterItem item in rptrMetric.Items)
{
var hfMetricIId = item.FindControl("hfMetricId") as HiddenField;
var nbMetricValue = item.FindControl("nbMetricValue") as NumberBox;
if (hfMetricIId != null && nbMetricValue != null)
{
var metricYValue = nbMetricValue.Text.AsDecimalOrNull();
// If no value was provided and the block is not configured to default to "0" then just skip this metric.
if (metricYValue == null && !GetAttributeValue(AttributeKey.DefaultToZero).AsBoolean())
{
continue;
}
int metricId = hfMetricIId.ValueAsInt();
var metric = new MetricService(rockContext).Get(metricId);
if (metric != null)
{
int campusPartitionId = metric.MetricPartitions.Where(p => p.EntityTypeId.HasValue && p.EntityTypeId.Value == campusEntityTypeId).Select(p => p.Id).FirstOrDefault();
int schedulePartitionId = metric.MetricPartitions.Where(p => p.EntityTypeId.HasValue && p.EntityTypeId.Value == scheduleEntityTypeId).Select(p => p.Id).FirstOrDefault();
var metricValue = metricValueService
.Queryable()
.Where(v =>
v.MetricId == metric.Id &&
v.MetricValueDateTime.HasValue && v.MetricValueDateTime.Value == weekend.Value &&
v.MetricValuePartitions.Count == 2 &&
v.MetricValuePartitions.Any(p => p.MetricPartitionId == campusPartitionId && p.EntityId.HasValue && p.EntityId.Value == campusId.Value) &&
v.MetricValuePartitions.Any(p => p.MetricPartitionId == schedulePartitionId && p.EntityId.HasValue && p.EntityId.Value == scheduleId.Value))
.FirstOrDefault();
if (metricValue == null)
{
metricValue = new MetricValue();
metricValue.MetricValueType = MetricValueType.Measure;
metricValue.MetricId = metric.Id;
metricValue.MetricValueDateTime = weekend.Value;
metricValueService.Add(metricValue);
var campusValuePartition = new MetricValuePartition();
campusValuePartition.MetricPartitionId = campusPartitionId;
campusValuePartition.EntityId = campusId.Value;
metricValue.MetricValuePartitions.Add(campusValuePartition);
var scheduleValuePartition = new MetricValuePartition();
scheduleValuePartition.MetricPartitionId = schedulePartitionId;
scheduleValuePartition.EntityId = scheduleId.Value;
metricValue.MetricValuePartitions.Add(scheduleValuePartition);
}
if (metricYValue == null)
{
metricValue.YValue = 0;
}
else
{
metricValue.YValue = metricYValue;
}
metricValue.Note = tbNote.Text;
}
}
}
rockContext.SaveChanges();
}
nbMetricsSaved.Text = string.Format("Your metrics for the {0} service on {1} at the {2} Campus have been saved.",
bddlService.SelectedItem.Text, bddlWeekend.SelectedItem.Text, bddlCampus.SelectedItem.Text);
nbMetricsSaved.Visible = true;
BindMetrics();
}
}
/// <summary>
/// Executes the specified context.
/// </summary>
/// <param name="context">The context.</param>
public void Execute(IJobExecutionContext context)
{
var metricSourceValueTypeDataviewGuid = Rock.SystemGuid.DefinedValue.METRIC_SOURCE_VALUE_TYPE_DATAVIEW.AsGuid();
var metricSourceValueTypeSqlGuid = Rock.SystemGuid.DefinedValue.METRIC_SOURCE_VALUE_TYPE_SQL.AsGuid();
var metricsQry = new MetricService(new RockContext()).Queryable().AsNoTracking().Where(
a => a.ScheduleId.HasValue &&
a.SourceValueTypeId.HasValue &&
(a.SourceValueType.Guid == metricSourceValueTypeDataviewGuid || a.SourceValueType.Guid == metricSourceValueTypeSqlGuid));
var metricIdList = metricsQry.OrderBy(a => a.Title).ThenBy(a => a.Subtitle).Select(a => a.Id).ToList();
var metricExceptions = new List <Exception>();
int metricsCalculated = 0;
int metricValuesCalculated = 0;
Metric metric = null;
foreach (var metricId in metricIdList)
{
try
{
using (var rockContextForMetricEntity = new RockContext())
{
var metricService = new MetricService(rockContextForMetricEntity);
metric = metricService.Get(metricId);
var lastRunDateTime = metric.LastRunDateTime ?? metric.CreatedDateTime ?? metric.ModifiedDateTime;
if (lastRunDateTime.HasValue)
{
var currentDateTime = RockDateTime.Now;
// get all the schedule times that were supposed to run since that last time it was scheduled to run
var scheduledDateTimesToProcess = metric.Schedule.GetScheduledStartTimes(lastRunDateTime.Value, currentDateTime).Where(a => a > lastRunDateTime.Value).ToList();
foreach (var scheduleDateTime in scheduledDateTimesToProcess)
{
using (var rockContextForMetricValues = new RockContext())
{
var metricPartitions = new MetricPartitionService(rockContextForMetricValues).Queryable().Where(a => a.MetricId == metric.Id).ToList();
var metricValueService = new MetricValueService(rockContextForMetricValues);
List <ResultValue> resultValues = new List <ResultValue>();
bool getMetricValueDateTimeFromResultSet = false;
if (metric.SourceValueType.Guid == metricSourceValueTypeDataviewGuid)
{
// get the metric value from the DataView
if (metric.DataView != null)
{
var errorMessages = new List <string>();
var qry = metric.DataView.GetQuery(null, null, out errorMessages);
if (metricPartitions.Count > 1 || metricPartitions.First().EntityTypeId.HasValue)
{
throw new NotImplementedException("Partitioned Metrics using DataViews is not supported.");
}
else
{
var resultValue = new ResultValue();
resultValue.Value = Convert.ToDecimal(qry.Count());
resultValue.Partitions = new List <ResultValuePartition>();
resultValue.MetricValueDateTime = scheduleDateTime;
resultValues.Add(resultValue);
}
}
}
else if (metric.SourceValueType.Guid == metricSourceValueTypeSqlGuid)
{
//// calculate the metricValue using the results from the SQL
//// assume SQL is in one of the following forms:
//// -- "SELECT Count(*), Partion0EntityId, Partion1EntityId, Partion2EntityId,.. FROM ..."
//// -- "SELECT Count(*), [MetricValueDateTime], Partion0EntityId, Partion1EntityId, Partion2EntityId,.. FROM ..."
if (!string.IsNullOrWhiteSpace(metric.SourceSql))
{
string formattedSql = metric.SourceSql.ResolveMergeFields(metric.GetMergeObjects(scheduleDateTime));
var tableResult = DbService.GetDataTable(formattedSql, System.Data.CommandType.Text, null);
if (tableResult.Columns.Count >= 2 && tableResult.Columns[1].ColumnName == "MetricValueDateTime")
{
getMetricValueDateTimeFromResultSet = true;
}
foreach (var row in tableResult.Rows.OfType <System.Data.DataRow>())
{
var resultValue = new ResultValue();
resultValue.Value = Convert.ToDecimal(row[0]);
if (getMetricValueDateTimeFromResultSet)
{
resultValue.MetricValueDateTime = Convert.ToDateTime(row[1]);
}
else
{
resultValue.MetricValueDateTime = scheduleDateTime;
}
resultValue.Partitions = new List <ResultValuePartition>();
int partitionPosition = 0;
int partitionFieldIndex = getMetricValueDateTimeFromResultSet ? 2 : 1;
int partitionColumnCount = tableResult.Columns.Count - 1;
while (partitionFieldIndex <= partitionColumnCount)
{
resultValue.Partitions.Add(new ResultValuePartition
{
PartitionPosition = partitionPosition,
EntityId = row[partitionFieldIndex] as int?
});
partitionPosition++;
partitionFieldIndex++;
}
resultValues.Add(resultValue);
}
}
}
metric.LastRunDateTime = scheduleDateTime;
metricsCalculated++;
metricValuesCalculated += resultValues.Count();
if (resultValues.Any())
{
List <MetricValue> metricValuesToAdd = new List <MetricValue>();
foreach (var resultValue in resultValues)
{
var metricValue = new MetricValue();
metricValue.MetricId = metric.Id;
metricValue.MetricValueDateTime = resultValue.MetricValueDateTime;
metricValue.MetricValueType = MetricValueType.Measure;
metricValue.YValue = resultValue.Value;
metricValue.CreatedDateTime = RockDateTime.Now;
metricValue.ModifiedDateTime = RockDateTime.Now;
metricValue.MetricValuePartitions = new List <MetricValuePartition>();
var metricPartitionsByPosition = metricPartitions.OrderBy(a => a.Order).ToList();
if (!resultValue.Partitions.Any() && metricPartitionsByPosition.Count() == 1 && !metricPartitionsByPosition[0].EntityTypeId.HasValue)
{
// a metric with just the default partition (not partitioned by Entity)
var metricPartition = metricPartitionsByPosition[0];
var metricValuePartition = new MetricValuePartition();
metricValuePartition.MetricPartition = metricPartition;
metricValuePartition.MetricPartitionId = metricPartition.Id;
metricValuePartition.MetricValue = metricValue;
metricValuePartition.EntityId = null;
metricValue.MetricValuePartitions.Add(metricValuePartition);
}
else
{
foreach (var partitionResult in resultValue.Partitions)
{
if (metricPartitionsByPosition.Count > partitionResult.PartitionPosition)
{
var metricPartition = metricPartitionsByPosition[partitionResult.PartitionPosition];
var metricValuePartition = new MetricValuePartition();
metricValuePartition.MetricPartition = metricPartition;
metricValuePartition.MetricPartitionId = metricPartition.Id;
metricValuePartition.MetricValue = metricValue;
metricValuePartition.EntityId = partitionResult.EntityId;
metricValue.MetricValuePartitions.Add(metricValuePartition);
}
}
}
if (metricValue.MetricValuePartitions == null || !metricValue.MetricValuePartitions.Any())
{
// shouldn't happen, but just in case
throw new Exception("MetricValue requires at least one Partition Value");
}
else
{
metricValuesToAdd.Add(metricValue);
}
}
// if a single metricValueDateTime was specified, delete any existing metric values for this date and refresh with the current results
var dbTransaction = rockContextForMetricValues.Database.BeginTransaction();
if (getMetricValueDateTimeFromResultSet)
{
var metricValueDateTimes = metricValuesToAdd.Select(a => a.MetricValueDateTime).Distinct().ToList();
foreach (var metricValueDateTime in metricValueDateTimes)
{
bool alreadyHasMetricValues = metricValueService.Queryable().Where(a => a.MetricId == metric.Id && a.MetricValueDateTime == metricValueDateTime).Any();
if (alreadyHasMetricValues)
{
// use direct SQL to clean up any existing metric values
rockContextForMetricValues.Database.ExecuteSqlCommand(@"
DELETE
FROM MetricValuePartition
WHERE MetricValueId IN (
SELECT Id
FROM MetricValue
WHERE MetricId = @metricId
AND MetricValueDateTime = @metricValueDateTime
)
", new SqlParameter("@metricId", metric.Id), new SqlParameter("@metricValueDateTime", metricValueDateTime));
rockContextForMetricValues.Database.ExecuteSqlCommand(@"
DELETE
FROM MetricValue
WHERE MetricId = @metricId
AND MetricValueDateTime = @metricValueDateTime
", new SqlParameter("@metricId", metric.Id), new SqlParameter("@metricValueDateTime", metricValueDateTime));
}
}
}
metricValueService.AddRange(metricValuesToAdd);
// disable savechanges PrePostProcessing since there could be hundreds or thousands of metric values getting inserted/updated
rockContextForMetricValues.SaveChanges(true);
dbTransaction.Commit();
}
rockContextForMetricEntity.SaveChanges();
}
}
}
}
}
catch (Exception ex)
{
metricExceptions.Add(new Exception(string.Format("Exception when calculating metric for {0} ", metric), ex));
}
}
context.Result = string.Format("Calculated a total of {0} metric values for {1} metrics", metricValuesCalculated, metricsCalculated);
if (metricExceptions.Any())
{
throw new AggregateException("One or more metric calculations failed ", metricExceptions);
}
}
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));
}
/// <summary>
/// Binds the grid.
/// </summary>
private void BindGrid()
{
int?metricId = hfMetricId.Value.AsIntegerOrNull();
if (!metricId.HasValue)
{
return;
}
var rockContext = new RockContext();
SortProperty sortProperty = gMetricValues.SortProperty;
MetricValueService metricValueService = new MetricValueService(rockContext);
var qry = metricValueService.Queryable().Include(a => a.MetricValuePartitions);
qry = qry.Where(a => a.MetricId == metricId);
var metricValuePartitionsColumn = gMetricValues.Columns.OfType <RockLiteralField>().FirstOrDefault(a => a.ID == "lMetricValuePartitions");
var metric = new MetricService(rockContext).Get(metricId ?? 0);
metricValuePartitionsColumn.Visible = metric != null && metric.MetricPartitions.Any(a => a.EntityTypeId.HasValue);
var drp = new DateRangePicker();
drp.DelimitedValues = gfMetricValues.GetUserPreference("Date Range");
if (drp.LowerValue.HasValue)
{
qry = qry.Where(a => a.MetricValueDateTime >= drp.LowerValue.Value);
}
if (drp.UpperValue.HasValue)
{
DateTime upperDate = drp.UpperValue.Value.Date.AddDays(1);
qry = qry.Where(a => a.MetricValueDateTime < upperDate);
}
var metricValueType = gfMetricValues.GetUserPreference("Goal/Measure").ConvertToEnumOrNull <MetricValueType>();
if (metricValueType.HasValue)
{
qry = qry.Where(a => a.MetricValueType == metricValueType.Value);
}
var entityTypeEntityUserPreference = gfMetricValues.GetUserPreference(this.EntityTypeEntityPreferenceKey) ?? string.Empty;
var entityTypeEntityList = entityTypeEntityUserPreference.Split(',').Select(a => a.Split('|')).Where(a => a.Length == 2).Select(a =>
new
{
EntityTypeId = a[0].AsIntegerOrNull(),
EntityId = a[1].AsIntegerOrNull()
});
foreach (var entityTypeEntity in entityTypeEntityList)
{
if (entityTypeEntity.EntityTypeId.HasValue && entityTypeEntity.EntityId.HasValue)
{
qry = qry.Where(a => a.MetricValuePartitions.Any(x => x.MetricPartition.EntityTypeId == entityTypeEntity.EntityTypeId && x.EntityId == entityTypeEntity.EntityId));
}
}
if (sortProperty != null)
{
qry = qry.Sort(sortProperty);
}
else
{
qry = qry.OrderByDescending(s => s.MetricValueDateTime).ThenBy(s => s.YValue).ThenBy(s => s.XValue).ThenByDescending(s => s.ModifiedDateTime);
}
gMetricValues.SetLinqDataSource(qry);
gMetricValues.DataBind();
}
/// <summary>
/// Binds the metrics.
/// </summary>
private void BindMetrics()
{
var serviceMetricValues = new List <ServiceMetric>();
int campusEntityTypeId = EntityTypeCache.Get(typeof(Rock.Model.Campus)).Id;
int scheduleEntityTypeId = EntityTypeCache.Get(typeof(Rock.Model.Schedule)).Id;
int? campusId = bddlCampus.SelectedValueAsInt();
int? scheduleId = bddlService.SelectedValueAsInt();
DateTime?weekend = bddlWeekend.SelectedValue.AsDateTime();
var notes = new List <string>();
if (campusId.HasValue && scheduleId.HasValue && weekend.HasValue)
{
SetBlockUserPreference("CampusId", campusId.HasValue ? campusId.Value.ToString() : "");
SetBlockUserPreference("ScheduleId", scheduleId.HasValue ? scheduleId.Value.ToString() : "");
var metricCategories = MetricCategoriesFieldAttribute.GetValueAsGuidPairs(GetAttributeValue("MetricCategories"));
var metricGuids = metricCategories.Select(a => a.MetricGuid).ToList();
using (var rockContext = new RockContext())
{
var metricValueService = new MetricValueService(rockContext);
foreach (var metric in new MetricService(rockContext)
.GetByGuids(metricGuids)
.Where(m =>
m.MetricPartitions.Count == 2 &&
m.MetricPartitions.Any(p => p.EntityTypeId.HasValue && p.EntityTypeId.Value == campusEntityTypeId) &&
m.MetricPartitions.Any(p => p.EntityTypeId.HasValue && p.EntityTypeId.Value == scheduleEntityTypeId))
.OrderBy(m => m.Title)
.Select(m => new
{
m.Id,
m.Title,
CampusPartitionId = m.MetricPartitions.Where(p => p.EntityTypeId.HasValue && p.EntityTypeId.Value == campusEntityTypeId).Select(p => p.Id).FirstOrDefault(),
SchedulePartitionId = m.MetricPartitions.Where(p => p.EntityTypeId.HasValue && p.EntityTypeId.Value == scheduleEntityTypeId).Select(p => p.Id).FirstOrDefault(),
}))
{
var serviceMetric = new ServiceMetric(metric.Id, metric.Title);
if (campusId.HasValue && weekend.HasValue && scheduleId.HasValue)
{
var metricValue = metricValueService
.Queryable().AsNoTracking()
.Where(v =>
v.MetricId == metric.Id &&
v.MetricValueDateTime.HasValue && v.MetricValueDateTime.Value == weekend.Value &&
v.MetricValuePartitions.Count == 2 &&
v.MetricValuePartitions.Any(p => p.MetricPartitionId == metric.CampusPartitionId && p.EntityId.HasValue && p.EntityId.Value == campusId.Value) &&
v.MetricValuePartitions.Any(p => p.MetricPartitionId == metric.SchedulePartitionId && p.EntityId.HasValue && p.EntityId.Value == scheduleId.Value))
.FirstOrDefault();
if (metricValue != null)
{
serviceMetric.Value = metricValue.YValue;
if (!string.IsNullOrWhiteSpace(metricValue.Note) &&
!notes.Contains(metricValue.Note))
{
notes.Add(metricValue.Note);
}
}
}
serviceMetricValues.Add(serviceMetric);
}
}
}
rptrMetric.DataSource = serviceMetricValues;
rptrMetric.DataBind();
tbNote.Text = notes.AsDelimited(Environment.NewLine + Environment.NewLine);
}
/// <summary>
/// Handles the Click event of the btnSave control.
/// </summary>
/// <param name="sender">The source of the event.</param>
/// <param name="e">The <see cref="EventArgs"/> instance containing the event data.</param>
protected void btnSave_Click(object sender, EventArgs e)
{
Metric metric;
var rockContext = new RockContext();
MetricService metricService = new MetricService(rockContext);
MetricCategoryService metricCategoryService = new MetricCategoryService(rockContext);
MetricValueService metricValueService = new MetricValueService(rockContext);
bool deleteValuesOnSave = sender == btnDeleteValuesAndSave;
int metricId = hfMetricId.Value.AsInteger();
if (metricId == 0)
{
metric = new Metric();
}
else
{
metric = metricService.Get(metricId);
}
metric.Title = tbTitle.Text;
metric.Subtitle = tbSubtitle.Text;
metric.Description = tbDescription.Text;
metric.IconCssClass = tbIconCssClass.Text;
metric.SourceValueTypeId = ddlSourceType.SelectedValueAsId();
metric.XAxisLabel = tbXAxisLabel.Text;
metric.YAxisLabel = tbYAxisLabel.Text;
metric.IsCumulative = cbIsCumulative.Checked;
var origEntityType = metric.EntityTypeId.HasValue ? EntityTypeCache.Read(metric.EntityTypeId.Value) : null;
var newEntityType = etpEntityType.SelectedEntityTypeId.HasValue ? EntityTypeCache.Read(etpEntityType.SelectedEntityTypeId.Value) : null;
if (origEntityType != null && !deleteValuesOnSave)
{
if (newEntityType == null || newEntityType.Id != origEntityType.Id)
{
// if the EntityTypeId of this metric has changed to NULL or to another EntityType, warn about the EntityId values being wrong
bool hasEntityValues = metricValueService.Queryable().Any(a => a.MetricId == metric.Id && a.EntityId.HasValue);
if (hasEntityValues)
{
nbEntityTypeChanged.Text = string.Format(
"Warning: You can't change the series partition to {0} when there are values associated with {1}. Do you want to delete existing values?",
newEntityType != null ? newEntityType.FriendlyName : "<none>",
origEntityType.FriendlyName);
mdEntityTypeChanged.Show();
nbEntityTypeChanged.Visible = true;
return;
}
}
}
metric.EntityTypeId = etpEntityType.SelectedEntityTypeId;
int sourceTypeDataView = DefinedValueCache.Read(Rock.SystemGuid.DefinedValue.METRIC_SOURCE_VALUE_TYPE_DATAVIEW.AsGuid()).Id;
int sourceTypeSQL = DefinedValueCache.Read(Rock.SystemGuid.DefinedValue.METRIC_SOURCE_VALUE_TYPE_SQL.AsGuid()).Id;
var personService = new PersonService(rockContext);
var metricChampionPerson = personService.Get(ppMetricChampionPerson.SelectedValue ?? 0);
metric.MetricChampionPersonAliasId = metricChampionPerson != null ? metricChampionPerson.PrimaryAliasId : null;
var adminPerson = personService.Get(ppAdminPerson.SelectedValue ?? 0);
metric.AdminPersonAliasId = adminPerson != null ? adminPerson.PrimaryAliasId : null;
if (metric.SourceValueTypeId == sourceTypeSQL)
{
metric.SourceSql = ceSourceSql.Text;
}
else
{
metric.SourceSql = string.Empty;
}
if (metric.SourceValueTypeId == sourceTypeDataView)
{
metric.DataViewId = ddlDataView.SelectedValueAsId();
}
else
{
metric.DataViewId = null;
}
var scheduleSelectionType = rblScheduleSelect.SelectedValueAsEnum <ScheduleSelectionType>();
if (scheduleSelectionType == ScheduleSelectionType.NamedSchedule)
{
metric.ScheduleId = ddlSchedule.SelectedValueAsId();
}
else
{
metric.ScheduleId = hfUniqueScheduleId.ValueAsInt();
}
if (!Page.IsValid)
{
return;
}
if (!metric.IsValid)
{
// Controls will render the error messages
return;
}
if (!cpMetricCategories.SelectedValuesAsInt().Any())
{
cpMetricCategories.ShowErrorMessage("Must select at least one category");
return;
}
// do a WrapTransaction since we are doing multiple SaveChanges()
rockContext.WrapTransaction(() =>
{
var scheduleService = new ScheduleService(rockContext);
var schedule = scheduleService.Get(metric.ScheduleId ?? 0);
int metricScheduleCategoryId = new CategoryService(rockContext).Get(Rock.SystemGuid.Category.SCHEDULE_METRICS.AsGuid()).Id;
if (schedule == null)
{
schedule = new Schedule();
// make it an "Unnamed" metrics schedule
schedule.Name = string.Empty;
schedule.CategoryId = metricScheduleCategoryId;
}
// if the schedule was a unique schedule (configured in the Metric UI, set the schedule's ical content to the schedule builder UI's value
if (scheduleSelectionType == ScheduleSelectionType.Unique)
{
schedule.iCalendarContent = sbSchedule.iCalendarContent;
}
if (!schedule.HasSchedule() && scheduleSelectionType == ScheduleSelectionType.Unique)
{
// don't save as a unique schedule if the schedule doesn't do anything
schedule = null;
}
else
{
if (schedule.Id == 0)
{
scheduleService.Add(schedule);
// save to make sure we have a scheduleId
rockContext.SaveChanges();
}
}
if (schedule != null)
{
metric.ScheduleId = schedule.Id;
}
else
{
metric.ScheduleId = null;
}
if (metric.Id == 0)
{
metricService.Add(metric);
// save to make sure we have a metricId
rockContext.SaveChanges();
}
// update MetricCategories for Metric
metric.MetricCategories = metric.MetricCategories ?? new List <MetricCategory>();
var selectedCategoryIds = cpMetricCategories.SelectedValuesAsInt();
// delete any categories that were removed
foreach (var metricCategory in metric.MetricCategories.ToList())
{
if (!selectedCategoryIds.Contains(metricCategory.CategoryId))
{
metricCategoryService.Delete(metricCategory);
}
}
// add any categories that were added
foreach (int categoryId in selectedCategoryIds)
{
if (!metric.MetricCategories.Any(a => a.CategoryId == categoryId))
{
metricCategoryService.Add(new MetricCategory {
CategoryId = categoryId, MetricId = metric.Id
});
}
}
rockContext.SaveChanges();
// delete MetricValues associated with the old entityType if they confirmed the EntityType change
if (deleteValuesOnSave)
{
metricValueService.DeleteRange(metricValueService.Queryable().Where(a => a.MetricId == metric.Id && a.EntityId.HasValue));
// since there could be 1000s of values that got deleted, do a SaveChanges that skips PrePostProcessing
rockContext.SaveChanges(true);
}
// delete any orphaned Unnamed metric schedules
var metricIdSchedulesQry = metricService.Queryable().Select(a => a.ScheduleId);
int?metricScheduleId = schedule != null ? schedule.Id : (int?)null;
var orphanedSchedules = scheduleService.Queryable()
.Where(a => a.CategoryId == metricScheduleCategoryId && a.Name == string.Empty && a.Id != (metricScheduleId ?? 0))
.Where(s => !metricIdSchedulesQry.Any(m => m == s.Id));
foreach (var item in orphanedSchedules)
{
scheduleService.Delete(item);
}
if (orphanedSchedules.Any())
{
rockContext.SaveChanges();
}
});
var qryParams = new Dictionary <string, string>();
qryParams["MetricId"] = metric.Id.ToString();
if (hfMetricCategoryId.ValueAsInt() == 0)
{
int?parentCategoryId = PageParameter("ParentCategoryId").AsIntegerOrNull();
int?metricCategoryId = new MetricCategoryService(new RockContext()).Queryable().Where(a => a.MetricId == metric.Id && a.CategoryId == parentCategoryId).Select(a => a.Id).FirstOrDefault();
hfMetricCategoryId.Value = metricCategoryId.ToString();
}
qryParams["MetricCategoryId"] = hfMetricCategoryId.Value;
qryParams["ExpandedIds"] = PageParameter("ExpandedIds");
NavigateToPage(RockPage.Guid, qryParams);
}
/// <summary>
/// Binds the metrics.
/// </summary>
private void BindMetrics()
{
var groupMetricValues = new List <GroupMetric>();
int groupEntityTypeId = EntityTypeCache.Get(typeof(Rock.Model.Group)).Id;
int campusEntityTypeId = EntityTypeCache.Get(typeof(Rock.Model.Campus)).Id;
int? groupId = gpSelectGroup.SelectedValueAsInt();
DateTime?weekend = dpMetricValueDateTime.SelectedDate;
var notes = new List <string>();
if (groupId.HasValue && weekend.HasValue)
{
SetBlockUserPreference("GroupId", groupId.HasValue ? groupId.Value.ToString() : "");
var metricCategories = MetricCategoriesFieldAttribute.GetValueAsGuidPairs(GetAttributeValue("MetricCategories"));
var metricGuids = metricCategories.Select(a => a.MetricGuid).ToList();
using (var rockContext = new RockContext())
{
var group = new GroupService(rockContext).Queryable().FirstOrDefault(g => g.Id == groupId);
int?campusId = group.CampusId;
var metricValueService = new MetricValueService(rockContext);
foreach (var metric in new MetricService(rockContext)
.GetByGuids(metricGuids)
.OrderBy(m => m.Title)
.Select(m => new
{
m.Id,
m.Title,
GroupPartitionId = m.MetricPartitions.Where(p => p.EntityTypeId.HasValue && p.EntityTypeId.Value == groupEntityTypeId).Select(p => p.Id).FirstOrDefault(),
CampusPartitionId = m.MetricPartitions.Where(p => p.EntityTypeId.HasValue && p.EntityTypeId.Value == campusEntityTypeId).Select(p => p.Id).FirstOrDefault()
}))
{
var groupMetric = new GroupMetric(metric.Id, metric.Title);
if (groupId.HasValue && weekend.HasValue)
{
var metricValue = metricValueService
.Queryable().AsNoTracking()
.Where(v =>
v.MetricId == metric.Id &&
v.MetricValueDateTime.HasValue && v.MetricValueDateTime.Value == weekend.Value &&
(
(
v.MetricValuePartitions.Count == 2 &&
v.MetricValuePartitions.Any(p => p.MetricPartitionId == metric.CampusPartitionId && p.EntityId.HasValue && p.EntityId.Value == campusId.Value) &&
v.MetricValuePartitions.Any(p => p.MetricPartitionId == metric.GroupPartitionId && p.EntityId.HasValue && p.EntityId.Value == groupId.Value)
) ||
(
v.MetricValuePartitions.Count == 1 &&
(
v.MetricValuePartitions.Any(p => p.MetricPartitionId == metric.GroupPartitionId && p.EntityId.HasValue && p.EntityId.Value == groupId.Value)
)
)
)
)
.FirstOrDefault();
if (metricValue != null)
{
groupMetric.Value = metricValue.YValue;
if (!string.IsNullOrWhiteSpace(metricValue.Note) &&
!notes.Contains(metricValue.Note))
{
notes.Add(metricValue.Note);
}
}
}
groupMetricValues.Add(groupMetric);
}
}
}
rptrMetric.DataSource = groupMetricValues;
rptrMetric.DataBind();
tbNote.Text = notes.AsDelimited(Environment.NewLine + Environment.NewLine);
}
/// <summary>
/// Shows the detail.
/// </summary>
/// <param name="metricValueId">The metric value identifier.</param>
/// <param name="metricId">The metric identifier.</param>
public void ShowDetail(int metricValueId, int?metricId)
{
pnlDetails.Visible = true;
// Load depending on Add(0) or Edit
MetricValue metricValue = null;
if (!metricValueId.Equals(0))
{
metricValue = new MetricValueService(new RockContext()).Get(metricValueId);
lActionTitle.Text = ActionTitle.Edit(MetricValue.FriendlyTypeName).FormatAsHtmlTitle();
pdAuditDetails.SetEntity(metricValue, ResolveRockUrl("~"));
}
if (metricValue == null && metricId.HasValue)
{
metricValue = new MetricValue {
Id = 0, MetricId = metricId.Value
};
metricValue.Metric = metricValue.Metric ?? new MetricService(new RockContext()).Get(metricValue.MetricId);
lActionTitle.Text = ActionTitle.Add(MetricValue.FriendlyTypeName).FormatAsHtmlTitle();
// hide the panel drawer that show created and last modified dates
pdAuditDetails.Visible = false;
}
hfMetricValueId.Value = metricValue.Id.ToString();
LoadDropDowns();
ddlMetricValueType.SelectedValue = metricValue.MetricValueType.ConvertToInt().ToString();
tbXValue.Text = metricValue.XValue;
tbYValue.Text = metricValue.YValue.ToString();
hfMetricId.Value = metricValue.MetricId.ToString();
tbNote.Text = metricValue.Note;
dpMetricValueDateTime.SelectedDate = metricValue.MetricValueDateTime;
// render UI based on Authorized and IsSystem
bool readOnly = false;
nbEditModeMessage.Text = string.Empty;
bool canEdit = UserCanEdit;
if (!canEdit && metricId.HasValue && metricId.Value > 0)
{
var metric = new MetricService(new RockContext()).Get(metricId.Value);
if (metric != null && metric.IsAuthorized(Authorization.EDIT, CurrentPerson))
{
canEdit = true;
}
}
if (!canEdit)
{
readOnly = true;
nbEditModeMessage.Text = EditModeMessage.ReadOnlyEditActionNotAllowed(MetricValue.FriendlyTypeName);
}
if (readOnly)
{
lActionTitle.Text = ActionTitle.View(MetricValue.FriendlyTypeName);
btnCancel.Text = "Close";
}
CreateDynamicControls(metricValue, true, readOnly);
ddlMetricValueType.Enabled = !readOnly;
tbXValue.ReadOnly = readOnly;
tbYValue.ReadOnly = readOnly;
tbNote.ReadOnly = readOnly;
dpMetricValueDateTime.Enabled = !readOnly;
btnSave.Visible = !readOnly;
}
protected void CalulateMetrics()
{
DateTime start2020 = new DateTime(2020, 1, 1);
DateTime end2020 = new DateTime(2020, 12, 31);
var _now = DateTime.Now.SundayDate().AddDays(-7 * 3);
var CurrentMonthInFiscalYear = ((_now.Month - 8) % 12) + 1;
var GoalOffsetMultiplier = (1.0 / 12) * CurrentMonthInFiscalYear;
var SecondaryGoalOffsetMultiplier = 0.88 + (CurrentMonthInFiscalYear * 0.01);
var _6wkEnd = _now;
var _6wkStart = _6wkEnd.AddDays(-7 * 6);
var _6wkLYEnd = _6wkEnd.AddYears(-1);
var _6wkLYStart = _6wkLYEnd.AddDays(-7 * 6);
var _8wkEnd = _now;
var _8wkStart = _6wkEnd.AddDays(-7 * 8);
var _8wkLYEnd = _6wkEnd.AddYears(-1);
var _8wkLYStart = _6wkLYEnd.AddDays(-7 * 8);
DateTime fiscalYearStartDate;
var fiscalYearStartDateFromAttribute = GetAttributeValue("FiscalYearStartDate").AsDateTime();
if (fiscalYearStartDateFromAttribute.HasValue)
{
fiscalYearStartDate = new DateTime(_now.Year, fiscalYearStartDateFromAttribute.Value.Month, fiscalYearStartDateFromAttribute.Value.Day);
}
else
{
fiscalYearStartDate = new DateTime(_now.Year, 1, 1);
}
if (fiscalYearStartDate > _now)
{
fiscalYearStartDate.AddYears(-1);
}
var _fyEnd = _now;
var _fyStart = fiscalYearStartDate;
List <MetricRow> metricList = new List <MetricRow>();
MetricValueService mvServ = new MetricValueService(rockContext);
var metric = mvServ.Queryable().Where(mv => mv.MetricValuePartitions.Where(mvp => mvp.MetricPartition.EntityTypeId == 67).Select(mvp => mvp.EntityId).Contains(SelectedCampusId));
var metric_Goal = metric.Where(mv => mv.MetricValueType == MetricValueType.Goal);
var metric_Measure = metric.Where(mv => mv.MetricValueType == MetricValueType.Measure);
var metric_Att_Adult = new MetricRow();
metric_Att_Adult.SecondaryGoalOffsetMultiplier = SecondaryGoalOffsetMultiplier;
metric_Att_Adult.Name = "6wk Average Adult Attendance";
metric_Att_Adult.YearToDateValue = ( int? )metric_Measure.Where(mv => mv.MetricId == 2 && mv.MetricValueDateTime >= _6wkStart && mv.MetricValueDateTime < _6wkEnd).ToList().GroupBy(mv => mv.MetricValueDateTime.Value.SundayDate()).Select(mv => mv.Sum(m => m.YValue)).Average() ?? 0;
metric_Att_Adult.LastYearToDateValue = ( int? )metric_Measure.Where(mv => mv.MetricId == 2 && mv.MetricValueDateTime >= _6wkLYStart && mv.MetricValueDateTime < _6wkLYEnd).ToList().GroupBy(mv => mv.MetricValueDateTime.Value.SundayDate()).Select(mv => mv.Sum(m => m.YValue)).Average() ?? 0;
metric_Att_Adult.Goal = ( int? )metric_Goal.Where(mv => mv.MetricId == 2 && mv.MetricValueDateTime >= start2020 && mv.MetricValueDateTime <= end2020).Sum(mv => mv.YValue) ?? 0;
metricList.Add(metric_Att_Adult);
var metric_Att_Kid = new MetricRow();
metric_Att_Kid.SecondaryGoalOffsetMultiplier = SecondaryGoalOffsetMultiplier;
metric_Att_Kid.Name = "6wk Average Kids Attendance";
metric_Att_Kid.YearToDateValue = ( int? )metric_Measure.Where(mv => new int[] { 3, 4 }.Contains(mv.MetricId) && mv.MetricValueDateTime >= _6wkStart && mv.MetricValueDateTime < _6wkEnd).ToList().GroupBy(mv => mv.MetricValueDateTime.Value.SundayDate()).Select(mv => mv.Sum(m => m.YValue)).Average() ?? 0;
metric_Att_Kid.LastYearToDateValue = ( int? )metric_Measure.Where(mv => new int[] { 3, 4 }.Contains(mv.MetricId) && mv.MetricValueDateTime >= _6wkLYStart && mv.MetricValueDateTime < _6wkLYEnd).ToList().GroupBy(mv => mv.MetricValueDateTime.Value.SundayDate()).Select(mv => mv.Sum(m => m.YValue)).Average() ?? 0;
metric_Att_Kid.Goal = ( int? )metric_Goal.Where(mv => new int[] { 3, 4 }.Contains(mv.MetricId) && mv.MetricValueDateTime >= start2020 && mv.MetricValueDateTime <= end2020).Sum(mv => mv.YValue) ?? 0;
metricList.Add(metric_Att_Kid);
var metric_Att_Stud = new MetricRow();
metric_Att_Stud.SecondaryGoalOffsetMultiplier = SecondaryGoalOffsetMultiplier;
metric_Att_Stud.Name = "6wk Average Students Attendance";
metric_Att_Stud.YearToDateValue = ( int? )metric_Measure.Where(mv => new int[] { 5 }.Contains(mv.MetricId) && mv.MetricValueDateTime >= _6wkStart && mv.MetricValueDateTime < _6wkEnd).ToList().GroupBy(mv => mv.MetricValueDateTime.Value.SundayDate()).Select(mv => mv.Sum(m => m.YValue)).Average() ?? 0;
metric_Att_Stud.LastYearToDateValue = ( int? )metric_Measure.Where(mv => new int[] { 5 }.Contains(mv.MetricId) && mv.MetricValueDateTime >= _6wkLYStart && mv.MetricValueDateTime < _6wkLYEnd).ToList().GroupBy(mv => mv.MetricValueDateTime.Value.SundayDate()).Select(mv => mv.Sum(m => m.YValue)).Average() ?? 0;
metric_Att_Stud.Goal = ( int? )metric_Goal.Where(mv => new int[] { 5 }.Contains(mv.MetricId) && mv.MetricValueDateTime >= start2020 && mv.MetricValueDateTime <= end2020).Sum(mv => mv.YValue) ?? 0;
metricList.Add(metric_Att_Stud);
var metric_Att_HS = new MetricRow();
metric_Att_HS.SecondaryGoalOffsetMultiplier = SecondaryGoalOffsetMultiplier;
metric_Att_HS.Name = "6wk Average High School Attendance";
metric_Att_HS.YearToDateValue = ( int? )metric_Measure.Where(mv => new int[] { 23 }.Contains(mv.MetricId) && mv.MetricValueDateTime >= _6wkStart && mv.MetricValueDateTime < _6wkEnd).ToList().GroupBy(mv => mv.MetricValueDateTime.Value.SundayDate()).Select(mv => mv.Sum(m => m.YValue)).Average() ?? 0;
metric_Att_HS.LastYearToDateValue = ( int? )metric_Measure.Where(mv => new int[] { 23 }.Contains(mv.MetricId) && mv.MetricValueDateTime >= _6wkLYStart && mv.MetricValueDateTime < _6wkLYEnd).ToList().GroupBy(mv => mv.MetricValueDateTime.Value.SundayDate()).Select(mv => mv.Sum(m => m.YValue)).Average() ?? 0;
metric_Att_HS.Goal = ( int? )metric_Goal.Where(mv => new int[] { 23 }.Contains(mv.MetricId) && mv.MetricValueDateTime >= start2020 && mv.MetricValueDateTime <= end2020).Sum(mv => mv.YValue) ?? 0;
metricList.Add(metric_Att_HS);
gMetricsChart.DataSource = metricList;
gMetricsChart.DataBind();
}
/// <summary>
/// Binds the grid.
/// </summary>
private void BindGrid()
{
int? metricId = hfMetricId.Value.AsIntegerOrNull();
if ( !metricId.HasValue )
{
return;
}
var rockContext = new RockContext();
SortProperty sortProperty = gMetricValues.SortProperty;
MetricValueService metricValueService = new MetricValueService( rockContext );
var qry = metricValueService.Queryable();
qry = qry.Where( a => a.MetricId == metricId );
var entityTypeNameColumn = gMetricValues.Columns.OfType<RockLiteralField>().FirstOrDefault( a => a.ID == "lEntityTypeName" );
var metric = new MetricService( rockContext ).Get( metricId ?? 0 );
entityTypeNameColumn.Visible = metric != null && metric.EntityTypeId.HasValue;
if ( metric != null )
{
_entityNameLookup = new Dictionary<int, string>();
var entityTypeCache = EntityTypeCache.Read( metric.EntityTypeId ?? 0 );
if ( entityTypeCache != null )
{
entityTypeNameColumn.HeaderText = entityTypeCache.FriendlyName;
IQueryable<IEntity> entityQry = null;
if ( entityTypeCache.GetEntityType() == typeof( Rock.Model.Group ) )
{
// special case for Group since there could be a very large number (especially if you include families), so limit to GroupType.ShowInGroupList
entityQry = new GroupService( rockContext ).Queryable().Where( a => a.GroupType.ShowInGroupList );
}
else
{
Type[] modelType = { entityTypeCache.GetEntityType() };
Type genericServiceType = typeof( Rock.Data.Service<> );
Type modelServiceType = genericServiceType.MakeGenericType( modelType );
var serviceInstance = Activator.CreateInstance( modelServiceType, new object[] { rockContext } ) as IService;
MethodInfo qryMethod = serviceInstance.GetType().GetMethod( "Queryable", new Type[] { } );
entityQry = qryMethod.Invoke( serviceInstance, new object[] { } ) as IQueryable<IEntity>;
}
if ( entityQry != null )
{
var entityList = entityQry.ToList();
foreach ( var e in entityList )
{
_entityNameLookup.AddOrReplace( e.Id, e.ToString() );
}
}
}
}
var drp = new DateRangePicker();
drp.DelimitedValues = gfMetricValues.GetUserPreference( "Date Range" );
if ( drp.LowerValue.HasValue )
{
qry = qry.Where( a => a.MetricValueDateTime >= drp.LowerValue.Value );
}
if ( drp.UpperValue.HasValue )
{
DateTime upperDate = drp.UpperValue.Value.Date.AddDays( 1 );
qry = qry.Where( a => a.MetricValueDateTime < upperDate );
}
var metricValueType = gfMetricValues.GetUserPreference( "Goal/Measure" ).ConvertToEnumOrNull<MetricValueType>();
if ( metricValueType.HasValue )
{
qry = qry.Where( a => a.MetricValueType == metricValueType.Value );
}
var entityParts = gfMetricValues.GetUserPreference( this.EntityPreferenceKey ).Split( '|' );
if ( entityParts.Length == 2 )
{
if ( entityParts[0].AsInteger() == metric.EntityTypeId )
{
var entityId = entityParts[1].AsIntegerOrNull();
if ( entityId.HasValue )
{
qry = qry.Where( a => a.EntityId == entityId );
}
}
}
if ( sortProperty != null )
{
qry = qry.Sort( sortProperty );
}
else
{
qry = qry.OrderBy( s => s.Order ).ThenByDescending( s => s.MetricValueDateTime ).ThenBy( s => s.YValue ).ThenBy( s => s.XValue ).ThenByDescending( s => s.ModifiedDateTime );
}
gMetricValues.SetLinqDataSource( qry );
gMetricValues.DataBind();
}
protected void DoSQL()
{
int SelectedCampusId = cpCampus.SelectedValue.AsInteger();
CampusCache SelectedCampus = CampusCache.Read(SelectedCampusId);
string SelectedCampusName = SelectedCampus != null ? SelectedCampus.Name : "";
var sunday = DateTime.Today.SundayDate().AddDays(-6);
var lastSunday = sunday.AddDays(-7);
SundayDate = sunday.ToShortDateString();
MetricValueService mvServ = new MetricValueService(rockContext);
var metricValues = SelectedCampusId == 0 ? mvServ.Queryable() : mvServ.Queryable().Where(mv => mv.MetricValuePartitions.Where(mvp => mvp.MetricPartition.EntityTypeId == 67).Select(mvp => mvp.EntityId).Contains(SelectedCampusId));
var metric_Goal = metricValues.Where(mv => mv.MetricValueType == MetricValueType.Goal);
var metric_Measure = metricValues.Where(mv => mv.MetricValueType == MetricValueType.Measure);
var metric_LastWeek = metric_Measure.Where(mv => mv.MetricValueDateTime > lastSunday && mv.MetricValueDateTime <= sunday);
//Attendance Last Week - All Environments
int iAttendanceGoalCurrent = decimal.ToInt32(metric_Goal.Where(mv => new int[] { 2, 3, 4, 5 }.Contains(mv.MetricId) && mv.MetricValueDateTime.Value.Year == sunday.Year).Sum(mv => mv.YValue) ?? 0);
int iAttendanceLastWeekAll = decimal.ToInt32(metric_LastWeek.Where(mv => new int[] { 2, 3, 4, 5 }.Contains(mv.MetricId)).Sum(mv => mv.YValue) ?? 0);
int iAttendancePercent = iAttendanceGoalCurrent != 0 ? ( int )((( double )iAttendanceLastWeekAll / iAttendanceGoalCurrent) * 100) : 0;
AttendanceLastWeekAll = iAttendanceLastWeekAll.ToString();
AttendancePercentage = iAttendancePercent.ToString();
AttendanceColor = GetColorForPercent(iAttendancePercent);
//Attendance Last Week - Auditorium
AttendanceLastWeekAud = decimal.ToInt32(metric_LastWeek.Where(mv => new int[] { 2 }.Contains(mv.MetricId)).Sum(mv => mv.YValue) ?? 0).ToString();
//Attendance Last Week - Rainforest + Velocity
AttendanceLastWeekChild = decimal.ToInt32(metric_LastWeek.Where(mv => new int[] { 3, 4 }.Contains(mv.MetricId)).Sum(mv => mv.YValue) ?? 0).ToString();
//Attendance Last Week - The Collective
AttendanceLastWeekStudent = decimal.ToInt32(metric_LastWeek.Where(mv => new int[] { 5 }.Contains(mv.MetricId)).Sum(mv => mv.YValue) ?? 0).ToString();
//Comitments
var commitStartDate = DateTime.Parse("2015-09-01");
//First Time Commitments
int iCommitments = decimal.ToInt32(metric_Measure.Where(mv => mv.MetricId == 12 && mv.MetricValueDateTime >= commitStartDate).Sum(mv => mv.YValue) ?? 0);
Commitments = iCommitments.ToString();
//Re-commitments
int iRecommitments = decimal.ToInt32(metric_Measure.Where(mv => mv.MetricId == 13 && mv.MetricValueDateTime >= commitStartDate).Sum(mv => mv.YValue) ?? 0);
Recommitments = iRecommitments.ToString();
//TotalCommitments
int iTotalCommitments = decimal.ToInt32(metric_Measure.Where(mv => new int[] { 12, 13 }.Contains(mv.MetricId) && mv.MetricValueDateTime >= commitStartDate).Sum(mv => mv.YValue) ?? 0);
int iTotalCommitmentGoal = decimal.ToInt32(metric_Goal.Where(mv => new int[] { 12, 13 }.Contains(mv.MetricId) && mv.MetricValueDateTime.Value.Year == sunday.Year).Sum(mv => mv.YValue) ?? 0);
int iTotalCommitmentPercent = iTotalCommitmentGoal != 0 ? ( int )((( double )iTotalCommitments / iTotalCommitmentGoal) * 100) : 0;
AllCommitments = iTotalCommitments.ToString();
TotalCommitmentPercentage = iTotalCommitmentPercent.ToString();
TotalCommitmentColor = GetColorForPercent(iTotalCommitmentPercent);
//Involvement
var groupMembers = new GroupMemberService(rockContext).Queryable().Where(gm => gm.Group.IsActive && gm.GroupMemberStatus == GroupMemberStatus.Active);
if (SelectedCampusId != 0)
{
groupMembers = groupMembers.Where(gm => gm.Group.CampusId == SelectedCampusId);
}
//Small Group Leaders
SmallGroupLeaders = groupMembers.Where(gm => gm.Group.GroupTypeId == 25 && gm.GroupRoleId == 24).DistinctBy(gm => gm.PersonId).Count().ToString();
//Volunteers
Volunteers = groupMembers.Where(gm => gm.Group.GroupTypeId == 42).DistinctBy(gm => gm.PersonId).Count().ToString();
//Total Involvement
Involvement = groupMembers.Where(gm => (gm.Group.GroupTypeId == 25 && gm.GroupRoleId == 24) || gm.Group.GroupTypeId == 42).DistinctBy(gm => gm.PersonId).Count().ToString();
WorkflowService workServ = new WorkflowService(rockContext);
var today = DateTime.Now;
int iInactiveFollowupComplete;
int iInactiveFollowupIncomplete;
//Inactive Follow-up
if (SelectedCampusId == 0)
{
iInactiveFollowupComplete = workServ.Queryable().Where(w => w.WorkflowTypeId == 120 && w.Status == "Completed" && w.ActivatedDateTime.Value.Year == today.Year && w.ActivatedDateTime.Value.Month == today.Month).Count();
}
else
{
iInactiveFollowupComplete = workServ.Queryable().Where(w => w.WorkflowTypeId == 120 && w.Status == "Completed" && w.ActivatedDateTime.Value.Year == today.Year && w.ActivatedDateTime.Value.Month == today.Month).WhereAttributeValue(rockContext, "Campus", SelectedCampusName).Count();
}
if (SelectedCampusId == 0)
{
iInactiveFollowupIncomplete = workServ.Queryable().Where(w => w.WorkflowTypeId == 120 && w.Status != "Completed" && w.ActivatedDateTime.Value.Year == today.Year && w.ActivatedDateTime.Value.Month == today.Month).Count();
}
else
{
iInactiveFollowupIncomplete = workServ.Queryable().Where(w => w.WorkflowTypeId == 120 && w.Status != "Completed" && w.ActivatedDateTime.Value.Year == today.Year && w.ActivatedDateTime.Value.Month == today.Month).WhereAttributeValue(rockContext, "Campus", SelectedCampusName).Count();
}
int iInactiveFollowup = iInactiveFollowupComplete + iInactiveFollowupIncomplete;
int iInactiveFollowupPercentage = iInactiveFollowup != 0 ? ( int )((( double )iInactiveFollowupComplete / iInactiveFollowup) * 100) : 0;
InactiveFollowupComplete = iInactiveFollowupComplete.ToString();
InactiveFollowupIncomplete = iInactiveFollowupIncomplete.ToString();
InactiveFollowup = iInactiveFollowup.ToString();
InactiveFollowupPercentage = iInactiveFollowupPercentage.ToString();
InactiveFollowupColor = GetColorForPercent(iInactiveFollowupPercentage);
}
/// <summary>
/// Shows the detail.
/// </summary>
/// <param name="metricValueId">The metric value identifier.</param>
/// <param name="metricId">The metric identifier.</param>
public void ShowDetail(int metricValueId, int?metricId)
{
pnlDetails.Visible = true;
// Load depending on Add(0) or Edit
MetricValue metricValue = null;
if (!metricValueId.Equals(0))
{
metricValue = new MetricValueService(new RockContext()).Get(metricValueId);
lActionTitle.Text = ActionTitle.Edit(MetricValue.FriendlyTypeName).FormatAsHtmlTitle();
}
if (metricValue == null && metricId.HasValue)
{
metricValue = new MetricValue {
Id = 0, MetricId = metricId.Value
};
metricValue.Metric = metricValue.Metric ?? new MetricService(new RockContext()).Get(metricValue.MetricId);
lActionTitle.Text = ActionTitle.Add(MetricValue.FriendlyTypeName).FormatAsHtmlTitle();
}
hfMetricValueId.Value = metricValue.Id.ToString();
LoadDropDowns();
ddlMetricValueType.SelectedValue = metricValue.MetricValueType.ConvertToInt().ToString();
tbXValue.Text = metricValue.XValue;
tbYValue.Text = metricValue.YValue.ToString();
hfMetricId.Value = metricValue.MetricId.ToString();
tbNote.Text = metricValue.Note;
dpMetricValueDateTime.SelectedDate = metricValue.MetricValueDateTime;
var metricEntityType = EntityTypeCache.Read(metricValue.Metric.EntityTypeId ?? 0);
// Setup EntityType UI controls
Control entityTypeEditControl = null;
if (metricEntityType != null && metricEntityType.SingleValueFieldType != null)
{
hfSingleValueFieldTypeId.Value = metricEntityType.SingleValueFieldType.Id.ToString();
FieldTypeCache fieldType = FieldTypeCache.Read(hfSingleValueFieldTypeId.Value.AsInteger());
entityTypeEditControl = fieldType.Field.EditControl(new Dictionary <string, Rock.Field.ConfigurationValue>(), "entityTypeEditControl");
if (entityTypeEditControl is IRockControl)
{
(entityTypeEditControl as IRockControl).Label = fieldType.Name;
}
phEntityTypeEntityIdValue.Controls.Add(entityTypeEditControl);
IEntityFieldType entityFieldType = metricEntityType.SingleValueFieldType.Field as IEntityFieldType;
if (entityFieldType != null)
{
entityFieldType.SetEditValueFromEntityId(entityTypeEditControl, new Dictionary <string, ConfigurationValue>(), metricValue.EntityId);
}
}
// render UI based on Authorized and IsSystem
bool readOnly = false;
nbEditModeMessage.Text = string.Empty;
bool canEdit = UserCanEdit;
if (!canEdit && metricId.HasValue && metricId.Value > 0)
{
var metric = new MetricService(new RockContext()).Get(metricId.Value);
if (metric != null && metric.IsAuthorized(Authorization.EDIT, CurrentPerson))
{
canEdit = true;
}
}
if (!canEdit)
{
readOnly = true;
nbEditModeMessage.Text = EditModeMessage.ReadOnlyEditActionNotAllowed(MetricValue.FriendlyTypeName);
}
if (readOnly)
{
lActionTitle.Text = ActionTitle.View(MetricValue.FriendlyTypeName);
btnCancel.Text = "Close";
}
ddlMetricValueType.Enabled = !readOnly;
tbXValue.ReadOnly = readOnly;
tbYValue.ReadOnly = readOnly;
tbNote.ReadOnly = readOnly;
dpMetricValueDateTime.Enabled = !readOnly;
if (entityTypeEditControl is WebControl)
{
(entityTypeEditControl as WebControl).Enabled = !readOnly;
}
btnSave.Visible = !readOnly;
}
/// <summary>
/// Handles the Click event of the btnSave control.
/// </summary>
/// <param name="sender">The source of the event.</param>
/// <param name="e">The <see cref="EventArgs"/> instance containing the event data.</param>
protected void btnSave_Click(object sender, EventArgs e)
{
int campusEntityTypeId = EntityTypeCache.Read(typeof(Rock.Model.Campus)).Id;
int scheduleEntityTypeId = EntityTypeCache.Read(typeof(Rock.Model.Schedule)).Id;
int?campusId = bddlCampus.SelectedValueAsInt();
//int? scheduleId = bddlService.SelectedValueAsInt();
DateTime?weekend = bddlWeekend.SelectedValue.AsDateTime();
if (campusId.HasValue && weekend.HasValue)
{
using (var rockContext = new RockContext())
{
var metricService = new MetricService(rockContext);
var metricValueService = new MetricValueService(rockContext);
foreach (RepeaterItem category in rptrMetricCategory.Items)
{
var rptrMetric = category.FindControl("rptrMetric") as Repeater;
foreach (RepeaterItem item in rptrMetric.Items)
{
var hfMetricIId = item.FindControl("hfMetricId") as HiddenField;
var nbMetricValue = item.FindControl("nbMetricValue") as NumberBox;
if (hfMetricIId != null && nbMetricValue != null && !string.IsNullOrEmpty(nbMetricValue.Text))
{
int metricId = hfMetricIId.ValueAsInt();
var metric = new MetricService(rockContext).Get(metricId);
if (metric != null)
{
int campusPartitionId = metric.MetricPartitions.Where(p => p.EntityTypeId.HasValue && p.EntityTypeId.Value == campusEntityTypeId).Select(p => p.Id).FirstOrDefault();
var metricValue = metricValueService
.Queryable()
.Where(v =>
v.MetricId == metric.Id &&
v.MetricValueDateTime.HasValue && v.MetricValueDateTime.Value == weekend.Value &&
v.MetricValuePartitions.Count == 1 &&
v.MetricValuePartitions.Any(p => p.MetricPartitionId == campusPartitionId && p.EntityId.HasValue && p.EntityId.Value == campusId.Value))
.FirstOrDefault();
if (metricValue == null)
{
metricValue = new MetricValue();
metricValue.MetricValueType = MetricValueType.Measure;
metricValue.MetricId = metric.Id;
metricValue.MetricValueDateTime = weekend.Value;
metricValueService.Add(metricValue);
var campusValuePartition = new MetricValuePartition();
campusValuePartition.MetricPartitionId = campusPartitionId;
campusValuePartition.EntityId = campusId.Value;
metricValue.MetricValuePartitions.Add(campusValuePartition);
}
metricValue.YValue = nbMetricValue.Text.AsDecimalOrNull();
metricValue.Note = tbNote.Text;
}
}
}
}
foreach (RepeaterItem service in rptrService.Items)
{
var hfScheduleId = service.FindControl("hfScheduleId") as HiddenField;
int scheduleId = hfScheduleId.ValueAsInt();
var rptrServiceMetric = service.FindControl("pnlwService").FindControl("rptrServiceMetric") as Repeater;
foreach (RepeaterItem item in rptrServiceMetric.Items)
{
var hfMetricIId = item.FindControl("hfMetricId") as HiddenField;
var nbMetricValue = item.FindControl("nbMetricValue") as NumberBox;
if (hfMetricIId != null && nbMetricValue != null && !string.IsNullOrEmpty(nbMetricValue.Text))
{
int metricId = hfMetricIId.ValueAsInt();
var metric = new MetricService(rockContext).Get(metricId);
if (metric != null)
{
int campusPartitionId = metric.MetricPartitions.Where(p => p.EntityTypeId.HasValue && p.EntityTypeId.Value == campusEntityTypeId).Select(p => p.Id).FirstOrDefault();
int schedulePartitionId = metric.MetricPartitions.Where(p => p.EntityTypeId.HasValue && p.EntityTypeId.Value == scheduleEntityTypeId).Select(p => p.Id).FirstOrDefault();
var metricValue = metricValueService
.Queryable()
.Where(v =>
v.MetricId == metric.Id &&
v.MetricValueDateTime.HasValue && v.MetricValueDateTime.Value == weekend.Value &&
v.MetricValuePartitions.Count == 2 &&
v.MetricValuePartitions.Any(p => p.MetricPartitionId == campusPartitionId && p.EntityId.HasValue && p.EntityId.Value == campusId.Value) &&
v.MetricValuePartitions.Any(p => p.MetricPartitionId == schedulePartitionId && p.EntityId.HasValue && p.EntityId.Value == scheduleId))
.FirstOrDefault();
if (metricValue == null)
{
metricValue = new MetricValue();
metricValue.MetricValueType = MetricValueType.Measure;
metricValue.MetricId = metric.Id;
metricValue.MetricValueDateTime = weekend.Value;
metricValueService.Add(metricValue);
var campusValuePartition = new MetricValuePartition();
campusValuePartition.MetricPartitionId = campusPartitionId;
campusValuePartition.EntityId = campusId.Value;
metricValue.MetricValuePartitions.Add(campusValuePartition);
var scheduleValuePartition = new MetricValuePartition();
scheduleValuePartition.MetricPartitionId = schedulePartitionId;
scheduleValuePartition.EntityId = scheduleId;
metricValue.MetricValuePartitions.Add(scheduleValuePartition);
}
metricValue.YValue = nbMetricValue.Text.AsDecimalOrNull();
metricValue.Note = tbNote.Text;
}
}
}
}
rockContext.SaveChanges();
}
nbMetricsSaved.Text = string.Format("Your metrics for {0} at the {1} Campus have been saved.",
bddlWeekend.SelectedItem.Text, bddlCampus.SelectedItem.Text);
nbMetricsSaved.Visible = true;
BindMetrics();
}
}
/// <summary>
/// Executes the specified context.
/// </summary>
/// <param name="context">The context.</param>
public void Execute(IJobExecutionContext context)
{
var dataMap = context.JobDetail.JobDataMap;
var commandTimeout = dataMap.GetString(AttributeKey.CommandTimeout).AsIntegerOrNull() ?? 300;
var metricSourceValueTypeDataviewGuid = Rock.SystemGuid.DefinedValue.METRIC_SOURCE_VALUE_TYPE_DATAVIEW.AsGuid();
var metricSourceValueTypeSqlGuid = Rock.SystemGuid.DefinedValue.METRIC_SOURCE_VALUE_TYPE_SQL.AsGuid();
var metricSourceValueTypeLavaGuid = Rock.SystemGuid.DefinedValue.METRIC_SOURCE_VALUE_TYPE_LAVA.AsGuid();
Guid[] calculatedSourceTypes = new Guid[] {
metricSourceValueTypeDataviewGuid,
metricSourceValueTypeSqlGuid,
metricSourceValueTypeLavaGuid
};
var metricsQry = new MetricService(new RockContext()).Queryable().AsNoTracking().Where(
a => a.ScheduleId.HasValue &&
a.SourceValueTypeId.HasValue &&
calculatedSourceTypes.Contains(a.SourceValueType.Guid));
var metricIdList = metricsQry.OrderBy(a => a.Title).ThenBy(a => a.Subtitle).Select(a => a.Id).ToList();
var metricExceptions = new List <Exception>();
int metricsCalculated = 0;
int metricValuesCalculated = 0;
Metric metric = null;
foreach (var metricId in metricIdList)
{
try
{
using (var rockContextForMetricEntity = new RockContext())
{
rockContextForMetricEntity.Database.CommandTimeout = commandTimeout;
var metricService = new MetricService(rockContextForMetricEntity);
metric = metricService.Get(metricId);
/* Get the last time the metric has run, if it has never run then set it as the first day of the current week to give it a chance to run now.
* NOTE: This is being used instead of Min Date to prevent a schedule with a start date of months or years back from running for each period since then.
* This would be the case for the "out-of-the-box" Rock daily metrics "Active Records", "Active Families", and "Active Connection Requests" if they
* have never run before. Or if a new user created metric uses a schedule that has an older start date.
*/
DateTime currentDateTime = RockDateTime.Now;
DateTime?startOfWeek = currentDateTime.StartOfWeek(RockDateTime.FirstDayOfWeek);
DateTime?lastRunDateTime = metric.LastRunDateTime ?? startOfWeek;
// get all the schedule times that were supposed to run since that last time it was scheduled to run
var scheduledDateTimesToProcess = metric.Schedule.GetScheduledStartTimes(lastRunDateTime.Value, currentDateTime).Where(a => a > lastRunDateTime.Value).ToList();
foreach (var scheduleDateTime in scheduledDateTimesToProcess)
{
using (var rockContextForMetricValues = new RockContext())
{
rockContextForMetricValues.Database.CommandTimeout = commandTimeout;
var metricPartitions = new MetricPartitionService(rockContextForMetricValues).Queryable().Where(a => a.MetricId == metric.Id).ToList();
var metricValueService = new MetricValueService(rockContextForMetricValues);
List <ResultValue> resultValues = new List <ResultValue>();
bool getMetricValueDateTimeFromResultSet = false;
if (metric.SourceValueType.Guid == metricSourceValueTypeDataviewGuid)
{
// get the metric value from the DataView
if (metric.DataView != null)
{
bool parseCampusPartition = metricPartitions.Count == 1 &&
metric.AutoPartitionOnPrimaryCampus &&
metric.DataView.EntityTypeId == Web.Cache.EntityTypeCache.GetId(SystemGuid.EntityType.PERSON) &&
metricPartitions[0].EntityTypeId == Web.Cache.EntityTypeCache.GetId(SystemGuid.EntityType.CAMPUS);
// Dataview metrics can be partitioned by campus only and AutoPartitionOnPrimaryCampus must be selected.
if (metricPartitions.Count > 1 ||
(metricPartitions[0].EntityTypeId.HasValue && parseCampusPartition == false))
{
throw new NotImplementedException("Partitioned Metrics using DataViews is only supported for Person data views using a single partition of type 'Campus' with 'Auto Partition on Primary Campus' checked. Any other dataview partition configuration is not supported.");
}
Stopwatch stopwatch = Stopwatch.StartNew();
var qry = metric.DataView.GetQuery(new DataViewGetQueryArgs());
if (parseCampusPartition)
{
// Create a dictionary of campus IDs with a person count
var campusPartitionValues = new Dictionary <int, int>();
foreach (var person in qry.ToList())
{
var iPerson = ( Person )person;
var campusId = iPerson.GetCampus()?.Id ?? -1;
campusPartitionValues.TryGetValue(campusId, out var currentCount);
campusPartitionValues[campusId] = currentCount + 1;
}
// Use the dictionary to create the ResultValues for each campus (partition)
foreach (var campusPartitionValue in campusPartitionValues)
{
var resultValue = new ResultValue
{
MetricValueDateTime = scheduleDateTime,
Partitions = new List <ResultValuePartition>(),
Value = campusPartitionValue.Value
};
int?entityId = campusPartitionValue.Key;
if (entityId == -1)
{
entityId = null;
}
resultValue.Partitions.Add(new ResultValuePartition
{
PartitionPosition = 0,
EntityId = entityId
});
resultValues.Add(resultValue);
}
}
else
{
// Put the entire set in one result since there is no partition
resultValues.Add(new ResultValue
{
Value = Convert.ToDecimal(qry.Count()),
MetricValueDateTime = scheduleDateTime,
Partitions = new List <ResultValuePartition>()
});
}
stopwatch.Stop();
DataViewService.AddRunDataViewTransaction(metric.DataView.Id, Convert.ToInt32(stopwatch.Elapsed.TotalMilliseconds));
}
}
else if (metric.SourceValueType.Guid == metricSourceValueTypeSqlGuid)
{
//// calculate the metricValue using the results from the SQL
//// assume SQL is in one of the following forms:
//// -- "SELECT Count(*) FROM ..."
//// -- "SELECT Count(*), [MetricValueDateTime] FROM ..."
//// -- "SELECT Count(*), Partition0EntityId, Partition1EntityId, Partition2EntityId,.. FROM ..."
//// -- "SELECT Count(*), [MetricValueDateTime], Partition0EntityId, Partition1EntityId, Partition2EntityId,.. FROM ..."
if (!string.IsNullOrWhiteSpace(metric.SourceSql))
{
string formattedSql = metric.SourceSql.ResolveMergeFields(metric.GetMergeObjects(scheduleDateTime));
var tableResult = DbService.GetDataTable(formattedSql, System.Data.CommandType.Text, null);
if (tableResult.Columns.Count >= 2 && tableResult.Columns[1].ColumnName == "MetricValueDateTime")
{
getMetricValueDateTimeFromResultSet = true;
}
foreach (var row in tableResult.Rows.OfType <System.Data.DataRow>())
{
var resultValue = new ResultValue();
resultValue.Value = Convert.ToDecimal(row[0] == DBNull.Value ? 0 : row[0]);
if (getMetricValueDateTimeFromResultSet)
{
resultValue.MetricValueDateTime = Convert.ToDateTime(row[1]);
}
else
{
resultValue.MetricValueDateTime = scheduleDateTime;
}
resultValue.Partitions = new List <ResultValuePartition>();
int partitionPosition = 0;
int partitionFieldIndex = getMetricValueDateTimeFromResultSet ? 2 : 1;
int partitionColumnCount = tableResult.Columns.Count - 1;
while (partitionFieldIndex <= partitionColumnCount)
{
resultValue.Partitions.Add(new ResultValuePartition
{
PartitionPosition = partitionPosition,
EntityId = row[partitionFieldIndex] as int?
});
partitionPosition++;
partitionFieldIndex++;
}
resultValues.Add(resultValue);
}
}
}
else if (metric.SourceValueType.Guid == metricSourceValueTypeLavaGuid)
{
//// calculate the metricValue using the results from Lava
//// assume Lava Output is in one of the following forms:
//// A single Count
//// 42
//// A List of Count, MetricValueDateTime
//// 42, 1/1/2017
//// 40, 1/2/2017
//// 49, 1/3/2017
//// A List of Count, Partition0EntityId, Partition1EntityId, Partition2EntityId
//// 42, 201, 450, 654
//// 42, 202, 450, 654
//// 42, 203, 450, 654
//// A List of Count, MetricValueDateTime, Partition0EntityId, Partition1EntityId, Partition2EntityId
//// 42, 1/1/2017, 201, 450, 654
//// 42, 1/2/2017, 202, 450, 654
//// 42, 1/3/2017, 203, 450, 654
if (!string.IsNullOrWhiteSpace(metric.SourceLava))
{
var mergeObjects = metric.GetMergeObjects(scheduleDateTime);
string lavaResult = metric.SourceLava.ResolveMergeFields(mergeObjects, enabledLavaCommands: "All", throwExceptionOnErrors: true);
List <string> resultLines = lavaResult.Split(new string[] { "\n", "\r\n" }, StringSplitOptions.RemoveEmptyEntries)
.Select(a => a.Trim()).Where(a => !string.IsNullOrEmpty(a)).ToList();
List <string[]> resultList = resultLines.Select(a => a.SplitDelimitedValues()).ToList();
if (resultList.Any())
{
if (resultList[0].Length >= 2)
{
// if the value of the data in the 2nd column is a Date, assume that is is the MetricValueDateTime
if (resultList[0][1].AsDateTime().HasValue)
{
getMetricValueDateTimeFromResultSet = true;
}
}
}
foreach (var row in resultList)
{
var resultValue = new ResultValue();
resultValue.Value = row[0].AsDecimal();
if (getMetricValueDateTimeFromResultSet)
{
resultValue.MetricValueDateTime = row[1].AsDateTime() ?? scheduleDateTime;
}
else
{
resultValue.MetricValueDateTime = scheduleDateTime;
}
resultValue.Partitions = new List <ResultValuePartition>();
int partitionPosition = 0;
int partitionFieldIndex = getMetricValueDateTimeFromResultSet ? 2 : 1;
int partitionColumnCount = row.Length - 1;
while (partitionFieldIndex <= partitionColumnCount)
{
resultValue.Partitions.Add(new ResultValuePartition
{
PartitionPosition = partitionPosition,
EntityId = row[partitionFieldIndex].AsIntegerOrNull()
});
partitionPosition++;
partitionFieldIndex++;
}
resultValues.Add(resultValue);
}
}
}
metric.LastRunDateTime = scheduleDateTime;
metricsCalculated++;
metricValuesCalculated += resultValues.Count();
if (resultValues.Any())
{
List <MetricValue> metricValuesToAdd = new List <MetricValue>();
foreach (var resultValue in resultValues)
{
var metricValue = new MetricValue();
metricValue.MetricId = metric.Id;
metricValue.MetricValueDateTime = resultValue.MetricValueDateTime;
metricValue.MetricValueType = MetricValueType.Measure;
metricValue.YValue = resultValue.Value;
metricValue.CreatedDateTime = RockDateTime.Now;
metricValue.ModifiedDateTime = RockDateTime.Now;
metricValue.MetricValuePartitions = new List <MetricValuePartition>();
var metricPartitionsByPosition = metricPartitions.OrderBy(a => a.Order).ToList();
if (!resultValue.Partitions.Any() && metricPartitionsByPosition.Count() == 1 && !metricPartitionsByPosition[0].EntityTypeId.HasValue)
{
// a metric with just the default partition (not partitioned by Entity)
var metricPartition = metricPartitionsByPosition[0];
var metricValuePartition = new MetricValuePartition();
metricValuePartition.MetricPartition = metricPartition;
metricValuePartition.MetricPartitionId = metricPartition.Id;
metricValuePartition.MetricValue = metricValue;
metricValuePartition.EntityId = null;
metricValue.MetricValuePartitions.Add(metricValuePartition);
}
else
{
foreach (var partitionResult in resultValue.Partitions)
{
if (metricPartitionsByPosition.Count > partitionResult.PartitionPosition)
{
var metricPartition = metricPartitionsByPosition[partitionResult.PartitionPosition];
var metricValuePartition = new MetricValuePartition();
metricValuePartition.MetricPartition = metricPartition;
metricValuePartition.MetricPartitionId = metricPartition.Id;
metricValuePartition.MetricValue = metricValue;
metricValuePartition.EntityId = partitionResult.EntityId;
metricValue.MetricValuePartitions.Add(metricValuePartition);
}
}
}
if (metricValue.MetricValuePartitions == null || !metricValue.MetricValuePartitions.Any())
{
// shouldn't happen, but just in case
throw new Exception("MetricValue requires at least one Partition Value");
}
else
{
metricValuesToAdd.Add(metricValue);
}
}
// if a single metricValueDateTime was specified, delete any existing metric values for this date and refresh with the current results
var dbTransaction = rockContextForMetricValues.Database.BeginTransaction();
if (getMetricValueDateTimeFromResultSet)
{
var metricValueDateTimes = metricValuesToAdd.Select(a => a.MetricValueDateTime).Distinct().ToList();
foreach (var metricValueDateTime in metricValueDateTimes)
{
bool alreadyHasMetricValues = metricValueService.Queryable().Where(a => a.MetricId == metric.Id && a.MetricValueDateTime == metricValueDateTime).Any();
if (alreadyHasMetricValues)
{
// use direct SQL to clean up any existing metric values
rockContextForMetricValues.Database.ExecuteSqlCommand(@"
DELETE
FROM MetricValuePartition
WHERE MetricValueId IN (
SELECT Id
FROM MetricValue
WHERE MetricId = @metricId
AND MetricValueDateTime = @metricValueDateTime
)
", new SqlParameter("@metricId", metric.Id), new SqlParameter("@metricValueDateTime", metricValueDateTime));
rockContextForMetricValues.Database.ExecuteSqlCommand(@"
DELETE
FROM MetricValue
WHERE MetricId = @metricId
AND MetricValueDateTime = @metricValueDateTime
", new SqlParameter("@metricId", metric.Id), new SqlParameter("@metricValueDateTime", metricValueDateTime));
}
}
}
metricValueService.AddRange(metricValuesToAdd);
// disable savechanges PrePostProcessing since there could be hundreds or thousands of metric values getting inserted/updated
rockContextForMetricValues.SaveChanges(true);
dbTransaction.Commit();
}
rockContextForMetricEntity.SaveChanges();
}
}
}
}
catch (Exception ex)
{
metricExceptions.Add(new Exception($"Exception when calculating metric for {metric}: {ex.Message}", ex));
}
}
context.Result = string.Format("Calculated a total of {0} metric values for {1} metrics", metricValuesCalculated, metricsCalculated);
if (metricExceptions.Any())
{
throw new AggregateException("One or more metric calculations failed ", metricExceptions);
}
}
/// <summary>
/// Handles the Click event of the btnSave control.
/// </summary>
/// <param name="sender">The source of the event.</param>
/// <param name="e">The <see cref="EventArgs" /> instance containing the event data.</param>
protected void btnSave_Click(object sender, EventArgs e)
{
MetricValue metricValue;
var rockContext = new RockContext();
MetricValueService metricValueService = new MetricValueService(rockContext);
int metricValueId = int.Parse(hfMetricValueId.Value);
if (metricValueId == 0)
{
metricValue = new MetricValue();
metricValueService.Add(metricValue);
metricValue.MetricId = hfMetricId.ValueAsInt();
metricValue.Metric = metricValue.Metric ?? new MetricService(rockContext).Get(metricValue.MetricId);
metricValue.MetricValuePartitions = new List <MetricValuePartition>();
}
else
{
metricValue = metricValueService.Get(metricValueId);
}
metricValue.MetricValueType = ddlMetricValueType.SelectedValueAsEnum <MetricValueType>();
metricValue.XValue = tbXValue.Text;
metricValue.YValue = tbYValue.Text.AsDecimalOrNull();
metricValue.Note = tbNote.Text;
metricValue.MetricValueDateTime = dpMetricValueDateTime.SelectedDate;
// Get EntityId from EntityType UI controls
foreach (var metricPartition in metricValue.Metric.MetricPartitions)
{
var metricPartitionEntityType = EntityTypeCache.Get(metricPartition.EntityTypeId ?? 0);
var controlId = string.Format("metricPartition{0}_entityTypeEditControl", metricPartition.Id);
Control entityTypeEditControl = phMetricValuePartitions.FindControl(controlId);
var metricValuePartition = metricValue.MetricValuePartitions.FirstOrDefault(a => a.MetricPartitionId == metricPartition.Id);
if (metricValuePartition == null)
{
metricValuePartition = new MetricValuePartition();
metricValuePartition.MetricPartitionId = metricPartition.Id;
metricValue.MetricValuePartitions.Add(metricValuePartition);
}
if (metricPartitionEntityType != null && metricPartitionEntityType.SingleValueFieldType != null && metricPartitionEntityType.SingleValueFieldType.Field is IEntityFieldType)
{
metricValuePartition.EntityId = (metricPartitionEntityType.SingleValueFieldType.Field as IEntityFieldType).GetEditValueAsEntityId(entityTypeEditControl, new Dictionary <string, ConfigurationValue>());
}
else
{
metricValuePartition.EntityId = null;
}
if (metricPartition.IsRequired && metricPartitionEntityType != null && !metricValuePartition.EntityId.HasValue)
{
nbValueRequired.Text = string.Format("A value for {0} is required", metricPartition.Label ?? metricPartitionEntityType.FriendlyName);
nbValueRequired.Dismissable = true;
nbValueRequired.Visible = true;
return;
}
}
if (!metricValue.IsValid)
{
// Controls will render the error messages
return;
}
rockContext.SaveChanges();
var qryParams = new Dictionary <string, string>();
qryParams.Add("MetricId", hfMetricId.Value);
qryParams.Add("MetricCategoryId", hfMetricCategoryId.Value);
qryParams.Add("ExpandedIds", PageParameter("ExpandedIds"));
NavigateToParentPage(qryParams);
}
/// <summary>
/// Binds the metrics.
/// </summary>
private void BindMetrics()
{
var metricCategoryList = new List <MetricCategory>();
int campusEntityTypeId = EntityTypeCache.Read(typeof(Rock.Model.Campus)).Id;
int scheduleEntityTypeId = EntityTypeCache.Read(typeof(Rock.Model.Schedule)).Id;
int?campusId = bddlCampus.SelectedValueAsInt();
//int? scheduleId = bddlService.SelectedValueAsInt();
DateTime?weekend = bddlWeekend.SelectedValue.AsDateTime();
// If we changed the campus, make sure we reload the services
if (campusId != _selectedCampusId)
{
_selectedCampusId = GetBlockUserPreference("CampusId").AsIntegerOrNull();
SaveViewState();
//bddlService.Items.Clear();
// Load service times
foreach (var service in GetServices(CampusCache.Read(campusId.Value)))
{
// bddlService.Items.Add( new ListItem( service.Name, service.Id.ToString() ) );
}
//bddlService.SetValue( _selectedServiceId.Value );
}
var notes = new List <string>();
if (campusId.HasValue && weekend.HasValue)
{
SetBlockUserPreference("CampusId", campusId.HasValue ? campusId.Value.ToString() : "");
var metricCategories = MetricCategoriesFieldAttribute.GetValueAsGuidPairs(GetAttributeValue("MetricCategories"));
var metricGuids = metricCategories.Select(a => a.MetricGuid).ToList();
using (var rockContext = new RockContext())
{
var metricValueService = new MetricValueService(rockContext);
var metrics = new MetricService(rockContext)
.GetByGuids(metricGuids)
.Where(m =>
m.MetricPartitions.Count == 1 &&
m.MetricPartitions.Any(p => p.EntityTypeId.HasValue && p.EntityTypeId.Value == campusEntityTypeId))
.OrderBy(m => m.Title)
.Select(m => new
{
m.Id,
m.Title,
CampusPartitionId = m.MetricPartitions.Where(p => p.EntityTypeId.HasValue && p.EntityTypeId.Value == campusEntityTypeId).Select(p => p.Id).FirstOrDefault(),
SchedulePartitionId = m.MetricPartitions.Where(p => p.EntityTypeId.HasValue && p.EntityTypeId.Value == scheduleEntityTypeId).Select(p => p.Id).FirstOrDefault(),
CategoryName = m.MetricCategories.Select(c => c.Category.Name).FirstOrDefault()
});
foreach (var metric in metrics.OrderBy(m => m.CategoryName).ThenBy(m => m.Title))
{
MetricCategory category = null;
if (metricCategoryList.Any(mct => mct.CategoryName == metric.CategoryName))
{
category = metricCategoryList.Where(mct => mct.CategoryName == metric.CategoryName).FirstOrDefault();
}
else
{
category = new MetricCategory()
{
CategoryName = metric.CategoryName, Metrics = new List <Metric>()
};
metricCategoryList.Add(category);
}
var metricObj = new Metric(metric.Id, metric.Title);
if (campusId.HasValue && weekend.HasValue)
{
var metricValue = metricValueService
.Queryable().AsNoTracking()
.Where(v =>
v.MetricId == metric.Id &&
v.MetricValueDateTime.HasValue && v.MetricValueDateTime.Value == weekend.Value &&
v.MetricValuePartitions.Count == 1 &&
v.MetricValuePartitions.Any(p => p.MetricPartitionId == metric.CampusPartitionId && p.EntityId.HasValue && p.EntityId.Value == campusId.Value))
.FirstOrDefault();
if (metricValue != null)
{
metricObj.Value = metricValue.YValue;
if (!string.IsNullOrWhiteSpace(metricValue.Note) &&
!notes.Contains(metricValue.Note))
{
notes.Add(metricValue.Note);
}
}
}
category.Metrics.Add(metricObj);
}
}
}
rptrMetricCategory.DataSource = metricCategoryList;
rptrMetricCategory.DataBind();
if (campusId.HasValue)
{
rptrService.DataSource = GetServices(CampusCache.Read(campusId.Value));
rptrService.DataBind();
rptrService.Visible = rptrService.Items.Count > 0;
}
tbNote.Text = notes.AsDelimited(Environment.NewLine + Environment.NewLine);
}
/// <summary>
/// Handles the ItemDataBound event of the rptrService control.
/// </summary>
/// <param name="sender">The source of the event.</param>
/// <param name="e">The <see cref="RepeaterItemEventArgs"/> instance containing the event data.</param>
protected void rptrService_ItemDataBound(object sender, RepeaterItemEventArgs e)
{
if (e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem)
{
Schedule schedule = e.Item.DataItem as Schedule;
int campusEntityTypeId = EntityTypeCache.Read(typeof(Rock.Model.Campus)).Id;
int scheduleEntityTypeId = EntityTypeCache.Read(typeof(Rock.Model.Schedule)).Id;
int? campusId = bddlCampus.SelectedValueAsInt();
int? scheduleId = schedule.Id;
DateTime?weekend = bddlWeekend.SelectedValue.AsDateTime();
var notes = new List <string>();
var serviceMetricValues = new List <Metric>();
var rptrServiceMetric = e.Item.FindControl("pnlwService").FindControl("rptrServiceMetric") as Repeater;
var metricCategories = MetricCategoriesFieldAttribute.GetValueAsGuidPairs(GetAttributeValue("MetricCategories"));
var metricGuids = metricCategories.Select(a => a.MetricGuid).ToList();
using (var rockContext = new RockContext())
{
var metricValueService = new MetricValueService(rockContext);
foreach (var metric in new MetricService(rockContext)
.GetByGuids(metricGuids)
.Where(m =>
m.MetricPartitions.Count == 2 &&
m.MetricPartitions.Any(p => p.EntityTypeId.HasValue && p.EntityTypeId.Value == campusEntityTypeId) &&
m.MetricPartitions.Any(p => p.EntityTypeId.HasValue && p.EntityTypeId.Value == scheduleEntityTypeId))
.OrderBy(m => m.Title)
.Select(m => new
{
m.Id,
m.Title,
CampusPartitionId = m.MetricPartitions.Where(p => p.EntityTypeId.HasValue && p.EntityTypeId.Value == campusEntityTypeId).Select(p => p.Id).FirstOrDefault(),
SchedulePartitionId = m.MetricPartitions.Where(p => p.EntityTypeId.HasValue && p.EntityTypeId.Value == scheduleEntityTypeId).Select(p => p.Id).FirstOrDefault(),
}))
{
var serviceMetric = new Metric(metric.Id, metric.Title);
if (campusId.HasValue && weekend.HasValue && scheduleId.HasValue)
{
var metricValue = metricValueService
.Queryable().AsNoTracking()
.Where(v =>
v.MetricId == metric.Id &&
v.MetricValueDateTime.HasValue && v.MetricValueDateTime.Value == weekend.Value &&
v.MetricValuePartitions.Count == 2 &&
v.MetricValuePartitions.Any(p => p.MetricPartitionId == metric.CampusPartitionId && p.EntityId.HasValue && p.EntityId.Value == campusId.Value) &&
v.MetricValuePartitions.Any(p => p.MetricPartitionId == metric.SchedulePartitionId && p.EntityId.HasValue && p.EntityId.Value == scheduleId.Value))
.FirstOrDefault();
if (metricValue != null)
{
serviceMetric.Value = metricValue.YValue;
if (!string.IsNullOrWhiteSpace(metricValue.Note) &&
!notes.Contains(metricValue.Note))
{
notes.Add(metricValue.Note);
}
}
}
serviceMetricValues.Add(serviceMetric);
}
}
rptrServiceMetric.DataSource = serviceMetricValues;
rptrServiceMetric.DataBind();
}
}
/// <summary>
/// Handles the Click event of the btnSave control.
/// </summary>
/// <param name="sender">The source of the event.</param>
/// <param name="e">The <see cref="EventArgs"/> instance containing the event data.</param>
protected void btnSave_Click(object sender, EventArgs e)
{
int groupEntityTypeId = EntityTypeCache.Get(typeof(Rock.Model.Group)).Id;
int campusEntityTypeId = EntityTypeCache.Get(typeof(Rock.Model.Campus)).Id;
int? groupId = gpSelectGroup.SelectedValueAsInt();
DateTime?dateVal = dpMetricValueDateTime.SelectedDate;
if (groupId.HasValue && dateVal.HasValue)
{
using (var rockContext = new RockContext())
{
var metricService = new MetricService(rockContext);
var metricValueService = new MetricValueService(rockContext);
var group = new GroupService(rockContext).Queryable().FirstOrDefault(g => g.Id == groupId);
int?campusId = group.CampusId;
foreach (RepeaterItem item in rptrMetric.Items)
{
var hfMetricIId = item.FindControl("hfMetricId") as HiddenField;
var nbMetricValue = item.FindControl("nbMetricValue") as NumberBox;
if (hfMetricIId != null && nbMetricValue != null)
{
int metricId = hfMetricIId.ValueAsInt();
var metric = new MetricService(rockContext).Get(metricId);
if (metric != null)
{
int groupPartitionId = metric.MetricPartitions.Where(p => p.EntityTypeId.HasValue && p.EntityTypeId.Value == groupEntityTypeId).Select(p => p.Id).FirstOrDefault();
int campusPartitionId = metric.MetricPartitions.Where(p => p.EntityTypeId.HasValue && p.EntityTypeId.Value == campusEntityTypeId).Select(p => p.Id).FirstOrDefault();
var metricValue = metricValueService
.Queryable()
.Where(v =>
v.MetricId == metric.Id &&
v.MetricValueDateTime.HasValue && v.MetricValueDateTime.Value == dateVal.Value &&
(
(
v.MetricValuePartitions.Count == 2 &&
v.MetricValuePartitions.Any(p => p.MetricPartitionId == campusPartitionId && p.EntityId.HasValue && p.EntityId.Value == campusId.Value) &&
v.MetricValuePartitions.Any(p => p.MetricPartitionId == groupPartitionId && p.EntityId.HasValue && p.EntityId.Value == groupId.Value)
) ||
(
v.MetricValuePartitions.Count == 1 &&
(
v.MetricValuePartitions.Any(p => p.MetricPartitionId == groupPartitionId && p.EntityId.HasValue && p.EntityId.Value == groupId.Value)
)
)
)
)
.FirstOrDefault();
if (metricValue == null)
{
metricValue = new MetricValue();
metricValue.MetricValueType = MetricValueType.Measure;
metricValue.MetricId = metric.Id;
metricValue.MetricValueDateTime = dateVal.Value;
metricValueService.Add(metricValue);
if (groupPartitionId > 0)
{
var groupValuePartition = new MetricValuePartition();
groupValuePartition.MetricPartitionId = groupPartitionId;
groupValuePartition.EntityId = groupId.Value;
metricValue.MetricValuePartitions.Add(groupValuePartition);
}
if (campusPartitionId > 0 && campusId.HasValue)
{
var campusValuePartition = new MetricValuePartition();
campusValuePartition.MetricPartitionId = campusPartitionId;
campusValuePartition.EntityId = campusId.Value;
metricValue.MetricValuePartitions.Add(campusValuePartition);
}
}
metricValue.YValue = nbMetricValue.Text.AsDecimalOrNull();
metricValue.Note = tbNote.Text;
}
}
}
rockContext.SaveChanges();
}
nbMetricsSaved.Text = string.Format("The metrics for '{0}' on {1} have been saved.", gpSelectGroup.ItemName, dpMetricValueDateTime.SelectedDate.ToString());
nbMetricsSaved.Visible = true;
BindMetrics();
}
}
