/// <summary>
        /// Job that will run quick SQL queries on a schedule.
        /// 
        /// Called by the <see cref="IScheduler" /> when a
        /// <see cref="ITrigger" /> fires that is associated with
        /// the <see cref="IJob" />.
        /// </summary>
        public virtual void Execute( IJobExecutionContext context )
        {
            JobDataMap dataMap = context.JobDetail.JobDataMap;

            Guid? entryWorkflowType = dataMap.GetString( "EraEntryWorkflow" ).AsGuidOrNull();
            Guid? exitWorkflowType = dataMap.GetString( "EraExitWorkflow" ).AsGuidOrNull();
            bool updateVisitDates = dataMap.GetBooleanValue( "SetVisitDates" );
            var groupTypeList = dataMap.GetString( "GroupTypes" );

            // configuration
            //

            // giving
            int exitGivingCount = 1;

            // attendance
            int exitAttendanceCountShort = 1;
            int exitAttendanceCountLong = 8;

            // get era dataset from stored proc
            var resultContext = new RockContext();

            var eraAttribute = AttributeCache.Read( SystemGuid.Attribute.PERSON_ERA_CURRENTLY_AN_ERA.AsGuid() );
            var eraStartAttribute = AttributeCache.Read( SystemGuid.Attribute.PERSON_ERA_START_DATE.AsGuid() );
            var eraEndAttribute = AttributeCache.Read( SystemGuid.Attribute.PERSON_ERA_END_DATE.AsGuid() );

            resultContext.Database.CommandTimeout = 3600;

            var results = resultContext.Database.SqlQuery<EraResult>( "spCrm_FamilyAnalyticsEraDataset" ).ToList();

            int personEntityTypeId = EntityTypeCache.Read( "Rock.Model.Person" ).Id;
            int attributeEntityTypeId = EntityTypeCache.Read( "Rock.Model.Attribute" ).Id;
            int eraAttributeId = AttributeCache.Read( SystemGuid.Attribute.PERSON_ERA_CURRENTLY_AN_ERA.AsGuid() ).Id;
            int personAnalyticsCategoryId = CategoryCache.Read( SystemGuid.Category.HISTORY_PERSON_ANALYTICS.AsGuid() ).Id;

            foreach (var result in results )
            {
                // create new rock context for each family (https://weblog.west-wind.com/posts/2014/Dec/21/Gotcha-Entity-Framework-gets-slow-in-long-Iteration-Loops)
                RockContext updateContext = new RockContext();
                var attributeValueService = new AttributeValueService( updateContext );
                var historyService = new HistoryService( updateContext );

                // if era ensure it still meets requirements
                if ( result.IsEra )
                {
                    if (result.ExitGiftCountDuration < exitGivingCount && result.ExitAttendanceCountDurationShort < exitAttendanceCountShort && result.ExitAttendanceCountDurationLong < exitAttendanceCountLong )
                    {
                        // exit era (delete attribute value from each person in family)
                        var family = new GroupService( updateContext ).Queryable( "Members, Members.Person" ).AsNoTracking().Where( m => m.Id == result.FamilyId ).FirstOrDefault();

                        if ( family != null ) {
                            foreach ( var person in family.Members.Select( m => m.Person ) ) {

                                // remove the era flag
                                var eraAttributeValue = attributeValueService.Queryable().Where( v => v.AttributeId == eraAttribute.Id && v.EntityId == person.Id ).FirstOrDefault();
                                if ( eraAttributeValue != null )
                                {
                                    attributeValueService.Delete( eraAttributeValue );
                                }

                                // set end date
                                var eraEndAttributeValue = attributeValueService.Queryable().Where( v => v.AttributeId == eraEndAttribute.Id && v.EntityId == person.Id ).FirstOrDefault();
                                if ( eraEndAttributeValue == null )
                                {
                                    eraEndAttributeValue = new AttributeValue();
                                    eraEndAttributeValue.EntityId = person.Id;
                                    eraEndAttributeValue.AttributeId = eraEndAttribute.Id;
                                    attributeValueService.Add( eraEndAttributeValue );
                                }
                                eraEndAttributeValue.Value = RockDateTime.Now.ToString();

                                // add a history record
                                if ( personAnalyticsCategoryId != 0 && personEntityTypeId != 0 && attributeEntityTypeId != 0 && eraAttributeId != 0 )
                                {
                                    History historyRecord = new History();
                                    historyService.Add( historyRecord );
                                    historyRecord.EntityTypeId = personEntityTypeId;
                                    historyRecord.EntityId = person.Id;
                                    historyRecord.CreatedDateTime = RockDateTime.Now;
                                    historyRecord.CreatedByPersonAliasId = person.PrimaryAliasId;
                                    historyRecord.Caption = "eRA";
                                    historyRecord.Summary = "Exited eRA Status";
                                    historyRecord.Verb = "EXITED";
                                    historyRecord.RelatedEntityTypeId = attributeEntityTypeId;
                                    historyRecord.RelatedEntityId = eraAttributeId;
                                    historyRecord.CategoryId = personAnalyticsCategoryId;
                                }

                                updateContext.SaveChanges();
                            }

                            // launch exit workflow
                            if ( exitWorkflowType.HasValue )
                            {
                                LaunchWorkflow( exitWorkflowType.Value, family );
                            }
                        }
                    }
                }
                else
                {
                    // entered era
                    var family = new GroupService( updateContext ).Queryable( "Members" ).AsNoTracking().Where( m => m.Id == result.FamilyId ).FirstOrDefault();

                    if ( family != null )
                    {
                        foreach ( var person in family.Members.Select( m => m.Person ) )
                        {
                            // set era attribute to true
                            var eraAttributeValue = attributeValueService.Queryable().Where( v => v.AttributeId == eraAttribute.Id && v.EntityId == person.Id ).FirstOrDefault();
                            if ( eraAttributeValue == null )
                            {
                                eraAttributeValue = new AttributeValue();
                                eraAttributeValue.EntityId = person.Id;
                                eraAttributeValue.AttributeId = eraAttribute.Id;
                                attributeValueService.Add( eraAttributeValue );
                            }
                            eraAttributeValue.Value = bool.TrueString;

                            // add start date
                            var eraStartAttributeValue = attributeValueService.Queryable().Where( v => v.AttributeId == eraStartAttribute.Id && v.EntityId == person.Id ).FirstOrDefault();
                            if (eraStartAttributeValue == null )
                            {
                                eraStartAttributeValue = new AttributeValue();
                                eraStartAttributeValue.EntityId = person.Id;
                                eraStartAttributeValue.AttributeId = eraStartAttribute.Id;
                                attributeValueService.Add( eraStartAttributeValue );
                            }
                            eraStartAttributeValue.Value = RockDateTime.Now.ToString();

                            // delete end date if it exists
                            var eraEndAttributeValue = attributeValueService.Queryable().Where( v => v.AttributeId == eraEndAttribute.Id && v.EntityId == person.Id ).FirstOrDefault();
                            if ( eraEndAttributeValue != null )
                            {
                                attributeValueService.Delete( eraEndAttributeValue );
                            }

                            // add a history record
                            if ( personAnalyticsCategoryId != 0 && personEntityTypeId != 0 && attributeEntityTypeId != 0 && eraAttributeId != 0 )
                            {
                                History historyRecord = new History();
                                historyService.Add( historyRecord );
                                historyRecord.EntityTypeId = personEntityTypeId;
                                historyRecord.EntityId = person.Id;
                                historyRecord.CreatedDateTime = RockDateTime.Now;
                                historyRecord.CreatedByPersonAliasId = person.PrimaryAliasId;
                                historyRecord.Caption = "eRA";
                                historyRecord.Summary = "Entered eRA Status";
                                historyRecord.Verb = "ENTERED";
                                historyRecord.RelatedEntityTypeId = attributeEntityTypeId;
                                historyRecord.RelatedEntityId = eraAttributeId;
                                historyRecord.CategoryId = personAnalyticsCategoryId;
                            }

                            updateContext.SaveChanges();
                        }

                        // launch entry workflow
                        if ( entryWorkflowType.HasValue )
                        {
                            LaunchWorkflow( entryWorkflowType.Value, family );
                        }
                    }
                }

                // update stats
            }

            // load giving attributes
            resultContext.Database.ExecuteSqlCommand( "spCrm_FamilyAnalyticsGiving" );

            // load attendance attributes
            resultContext.Database.ExecuteSqlCommand( "spCrm_FamilyAnalyticsAttendance" );

            // process history for group types
            if (!string.IsNullOrWhiteSpace( groupTypeList ) )
            {
                string[] groupTypeGuids = groupTypeList.Split( ',' );

                var inactiveRecordValue = DefinedValueCache.Read( SystemGuid.DefinedValue.PERSON_RECORD_STATUS_INACTIVE );

                var groupTypeEntityTypeId = EntityTypeCache.Read( "Rock.Model.GroupType" ).Id;

                foreach ( var groupTypeGuid in groupTypeGuids )
                {
                    var groupType = GroupTypeCache.Read( groupTypeGuid.AsGuid() );

                    if ( groupType != null )
                    {
                        // if the person is in a group of that type and the last history record for that group type isn't START write a start
                        RockContext rockContext = new RockContext();

                        // get history for this group type
                        var historyRecords = new HistoryService( rockContext ).Queryable()
                                            .Where( h =>
                                                 h.EntityTypeId == personEntityTypeId
                                                 && h.RelatedEntityTypeId == groupTypeEntityTypeId
                                                 && h.RelatedEntityId == groupType.Id
                                             )
                                             .GroupBy( h => h.EntityId )
                                             .Select( g => g.OrderByDescending( h => h.CreatedDateTime ).Select( h => new { h.EntityId, h.Verb } ).FirstOrDefault() )
                                             .ToList();

                        // get group member information
                        var groupMemberInfo = new GroupMemberService( rockContext ).Queryable()
                                            .Where( m =>
                                                 m.Group.GroupTypeId == groupType.Id
                                                 && m.GroupMemberStatus == GroupMemberStatus.Active
                                                 && m.Group.IsActive
                                                 //&& m.Person.RecordStatusValueId != inactiveRecordValue.Id
                                             )
                                             .GroupBy( m => m.PersonId )
                                             .Select( g => g.OrderBy( m => m.CreatedDateTime ).Select( m => new { m.PersonId, m.CreatedDateTime, PersonAliasId = m.Person.Aliases.Select( p => p.Id ).FirstOrDefault() } ).FirstOrDefault() )
                                             .ToList();

                        var needsStartDate = groupMemberInfo.Where( m => !historyRecords.Any( h => h.EntityId == m.PersonId && h.Verb == "STARTED" ) );

                        foreach ( var startItem in needsStartDate )
                        {
                            using ( RockContext updateContext = new RockContext() )
                            {
                                var historyService = new HistoryService( updateContext );
                                History history = new History();
                                historyService.Add( history );
                                history.EntityTypeId = personEntityTypeId;
                                history.EntityId = startItem.PersonId;
                                history.RelatedEntityTypeId = groupTypeEntityTypeId;
                                history.RelatedEntityId = groupType.Id;
                                history.Caption = groupType.Name;
                                history.Summary = "Started Membership in Group Of Type";
                                history.Verb = "STARTED";
                                history.CreatedDateTime = startItem.CreatedDateTime;
                                history.CreatedByPersonAliasId = startItem.PersonAliasId;
                                history.CategoryId = personAnalyticsCategoryId;

                                updateContext.SaveChanges();
                            }
                        }

                        var needsStoppedDate = historyRecords.Where( h => h.Verb == "STARTED" && !groupMemberInfo.Any( m => m.PersonId == h.EntityId ) );

                        foreach ( var stopItem in needsStoppedDate )
                        {
                            using ( RockContext updateContext = new RockContext() )
                            {
                                var person = new PersonService( updateContext ).Get( stopItem.EntityId );

                                if ( person != null )
                                {
                                    var historyService = new HistoryService( updateContext );
                                    History history = new History();
                                    historyService.Add( history );
                                    history.EntityTypeId = personEntityTypeId;
                                    history.EntityId = person.Id;
                                    history.RelatedEntityTypeId = groupTypeEntityTypeId;
                                    history.RelatedEntityId = groupType.Id;
                                    history.Caption = groupType.Name;
                                    history.Summary = "Stopped Membership in Group Of Type";
                                    history.Verb = "STOPPED";
                                    history.CreatedDateTime = RockDateTime.Now;
                                    history.CreatedByPersonAliasId = person.PrimaryAliasId;
                                    history.CategoryId = personAnalyticsCategoryId;

                                    updateContext.SaveChanges();
                                }
                            }
                        }
                    }
                }

            }

            // process visit dates
            if ( updateVisitDates )
            {
                resultContext.Database.ExecuteSqlCommand( "spCrm_FamilyAnalyticsUpdateVisitDates" );
            }
        }