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>
/// 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();
}
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);
}
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>
/// 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>
/// 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>
/// 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>
/// 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();
}
}
/// <summary>
/// Executes the specified context.
/// </summary>
/// <param name="context">The context.</param>
public void Execute(IJobExecutionContext context)
{
var rockContext = new RockContext();
var metricService = new MetricService(rockContext);
var metricValueService = new MetricValueService(rockContext);
var metricSourceValueTypeDataviewGuid = Rock.SystemGuid.DefinedValue.METRIC_SOURCE_VALUE_TYPE_DATAVIEW.AsGuid();
var metricSourceValueTypeSqlGuid = Rock.SystemGuid.DefinedValue.METRIC_SOURCE_VALUE_TYPE_SQL.AsGuid();
var metricsQry = metricService.Queryable("Schedule").Where(
a => a.ScheduleId.HasValue &&
a.SourceValueTypeId.HasValue &&
(a.SourceValueType.Guid == metricSourceValueTypeDataviewGuid || a.SourceValueType.Guid == metricSourceValueTypeSqlGuid));
var metricsList = metricsQry.OrderBy(a => a.Title).ThenBy(a => a.Subtitle).ToList();
var metricExceptions = new List <Exception>();
foreach (var metric in metricsList)
{
try
{
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)
{
Dictionary <int, decimal> resultValues = new Dictionary <int, decimal>();
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 (metric.EntityTypeId.HasValue)
{
throw new NotImplementedException("Partitioned Metrics using DataViews is not supported.");
}
else
{
resultValues.Add(0, qry.Count());
}
}
}
else if (metric.SourceValueType.Guid == metricSourceValueTypeSqlGuid)
{
// calculate the metricValue assuming that the SQL returns one row with one numeric field
if (!string.IsNullOrWhiteSpace(metric.SourceSql))
{
string formattedSql = metric.SourceSql.ResolveMergeFields(metric.GetMergeObjects(scheduleDateTime));
var tableResult = DbService.GetDataTable(formattedSql, System.Data.CommandType.Text, null);
foreach (var row in tableResult.Rows.OfType <System.Data.DataRow>())
{
int entityId = 0;
decimal countValue;
if (tableResult.Columns.Count >= 2)
{
if (tableResult.Columns.Contains("EntityId"))
{
entityId = Convert.ToInt32(row["EntityId"]);
}
else
{
// assume SQL is in the form "SELECT Count(*), EntityId FROM ..."
entityId = Convert.ToInt32(row[1]);
}
}
if (tableResult.Columns.Contains("Value"))
{
countValue = Convert.ToDecimal(row["Value"]);
}
else
{
// assume SQL is in the form "SELECT Count(*), EntityId FROM ..."
countValue = Convert.ToDecimal(row[0]);
}
resultValues.Add(entityId, countValue);
}
}
}
metric.LastRunDateTime = scheduleDateTime;
if (resultValues.Any())
{
foreach (var resultValue in resultValues)
{
var metricValue = new MetricValue();
metricValue.MetricId = metric.Id;
metricValue.MetricValueDateTime = scheduleDateTime;
metricValue.MetricValueType = MetricValueType.Measure;
metricValue.YValue = resultValue.Value;
metricValue.EntityId = resultValue.Key > 0 ? resultValue.Key : (int?)null;
metricValueService.Add(metricValue);
}
}
rockContext.SaveChanges();
}
}
}
catch (Exception ex)
{
metricExceptions.Add(new Exception(string.Format("Exception when calculating metric for ", metric), ex));
}
}
if (metricExceptions.Any())
{
throw new AggregateException("One or more metric calculations failed ", metricExceptions);
}
}
/// <summary>
/// Saves the <see cref="MetricValue"/>s and updates the <see cref="IJobExecutionContext"/>'s Result property.
/// </summary>
/// <param name="context">The <see cref="IJobExecutionContext"/>.</param>
/// <exception cref="Exception">Unable to find the "Hosting Metrics" Category ID.</exception>
private void SaveMetricValues(IJobExecutionContext context)
{
var rockContext = new RockContext();
rockContext.Database.CommandTimeout = _commandTimeout;
var hostingMetricsCategoryId = CategoryCache.GetId(SystemGuid.Category.METRIC_HOSTING_METRICS.AsGuid());
if (!hostingMetricsCategoryId.HasValue)
{
throw new Exception(@"Unable to find the ""Hosting Metrics"" Category ID.");
}
var metricIdsQuery = new MetricCategoryService(rockContext).Queryable()
.Where(mc => mc.CategoryId == hostingMetricsCategoryId)
.Select(mc => mc.MetricId);
// Get all of the Metrics tied to the "Hosting Metrics" Category
var metrics = new MetricService(rockContext).Queryable("MetricPartitions")
.Where(m => metricIdsQuery.Contains(m.Id))
.ToList();
var metricValues = new List <MetricValue>();
foreach (var metric in metrics)
{
// Attempt to add any PerformanceCounter MetricValues
TryAddPerformanceCounterMetricValue(metric, metricValues);
}
/*
* 2020-04-22 - JPH
*
* The Metrics being collected by the first revision of this Job each have a single, default MetricPartition.
* If we add Metrics to this Job in the future that are more complicated, we'll want to revisit the below logic.
*
*/
var metricValueService = new MetricValueService(rockContext);
var metricValuePartitionService = new MetricValuePartitionService(rockContext);
foreach (var metricValue in metricValues)
{
foreach (var metricPartition in metricValue.Metric.MetricPartitions)
{
metricValue.MetricValuePartitions.Add(new MetricValuePartition {
MetricPartitionId = metricPartition.Id
});
}
metricValueService.Add(metricValue);
}
rockContext.SaveChanges();
/*
* 2020-05-19 - SK
* Removing the old metrics based on Maximum Metric to Retain value
*
*/
if (_maximumMetricsToRetain.HasValue)
{
foreach (var metric in metrics)
{
var deleteMetricValuesQry = metricValueService
.Queryable()
.AsNoTracking()
.Where(a => a.MetricId == metric.Id)
.OrderByDescending(a => a.MetricValueDateTime)
.Skip(_maximumMetricsToRetain.Value);
if (deleteMetricValuesQry.Any())
{
var metricValuePartitionQry = metricValuePartitionService.Queryable().AsNoTracking().Where(a => deleteMetricValuesQry.Any(u => u.Id == a.MetricValueId));
rockContext.BulkDelete(metricValuePartitionQry);
rockContext.BulkDelete(deleteMetricValuesQry);
}
}
}
context.Result = $"Calculated a total of {metricValues.Count} metric values for {metrics.Count} metrics";
}
