Beispiel #1
0
        /// <summary>
        /// Handles the Delete event of the gMetricPartitions 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 gMetricPartitions_Delete( object sender, RowEventArgs e )
        {
            var rockContext = new RockContext();
            MetricPartitionService metricPartitionService = new MetricPartitionService( rockContext );
            MetricPartition metricPartition = metricPartitionService.Get( (Guid)e.RowKeyValue );

            if ( MetricPartitionsState.Count() == 1 )
            {
                mdMetricPartitionsGridWarning.Show( "Metric must have at least one partition defined", ModalAlertType.Warning );
                return;
            }

            if ( metricPartition != null )
            {
                string errorMessage;
                if ( !metricPartitionService.CanDelete( metricPartition, out errorMessage ) )
                {
                    mdMetricPartitionsGridWarning.Show( errorMessage, ModalAlertType.Information );
                    return;
                }
            }

            Guid rowGuid = (Guid)e.RowKeyValue;
            MetricPartitionsState.RemoveEntity( rowGuid );

            BindMetricPartitionsGrid();
        }
Beispiel #2
0
        /// <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 );
            MetricPartitionService metricPartitionService = new MetricPartitionService( rockContext );

            int metricId = hfMetricId.Value.AsInteger();

            if ( metricId == 0 )
            {
                metric = new Metric();
            }
            else
            {
                metric = metricService.Get( metricId );

                // remove any metricPartitions that were removed in the UI
                var selectedMetricPartitionGuids = MetricPartitionsState.Select( r => r.Guid );
                foreach ( var item in metric.MetricPartitions.Where( r => !selectedMetricPartitionGuids.Contains( r.Guid ) ).ToList() )
                {
                    metric.MetricPartitions.Remove( item );
                    metricPartitionService.Delete( item );
                }
            }

            metric.MetricPartitions = metric.MetricPartitions ?? new List<MetricPartition>();

            if ( MetricPartitionsState.Count() > 1 && MetricPartitionsState.Any(a => !a.EntityTypeId.HasValue ))
            {
                mdMetricPartitionsEntityTypeWarning.Text = "If multiple partitions are defined for a metric, all the partitions must have an EntityType assigned";
                mdMetricPartitionsEntityTypeWarning.Visible = true;
                pwMetricPartitions.Expanded = true;
                return;
            }

            mdMetricPartitionsEntityTypeWarning.Visible = false;

            foreach ( var metricPartitionState in MetricPartitionsState )
            {
                MetricPartition metricPartition = metric.MetricPartitions.Where( r => r.Guid == metricPartitionState.Guid ).FirstOrDefault();
                if ( metricPartition == null )
                {
                    metricPartition = new MetricPartition();
                    metric.MetricPartitions.Add( metricPartition );
                }
                else
                {
                    metricPartitionState.Id = metricPartition.Id;
                    metricPartitionState.Guid = metricPartition.Guid;
                }

                metricPartition.CopyPropertiesFrom( metricPartitionState );
            }

            // ensure there is at least one partition
            if ( !metric.MetricPartitions.Any() )
            {
                var metricPartition = new MetricPartition();
                metricPartition.EntityTypeId = null;
                metricPartition.IsRequired = true;
                metricPartition.Order = 0;
                metric.MetricPartitions.Add( metricPartition );
            }

            metric.Title = tbTitle.Text;
            metric.Subtitle = tbSubtitle.Text;
            metric.Description = tbDescription.Text;
            metric.IconCssClass = tbIconCssClass.Text;
            metric.SourceValueTypeId = ddlSourceType.SelectedValueAsId();
            metric.YAxisLabel = tbYAxisLabel.Text;
            metric.IsCumulative = cbIsCumulative.Checked;

            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 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 );
        }
Beispiel #3
0
        /// <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);
            }
        }
Beispiel #4
0
        /// <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);
            }
        }
Beispiel #5
0
        /// <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 );
            }
        }