/// <summary> /// Returns a list of matching people /// </summary> /// <param name="searchterm"></param> /// <returns></returns> public override IQueryable<string> Search( string searchterm ) { var personService = new PersonService( new RockContext() ); return personService.Queryable(). Where( p => p.Email.Contains( searchterm ) ). OrderBy( p => p.Email ). Select( p => p.Email ).Distinct(); }
protected Person GetPersonFromId(string PersonId) { int intPersonId = Int32.Parse(PersonId); PersonService personService = new PersonService(rockContext); var person = personService.Queryable().FirstOrDefault(p => p.Id == intPersonId); return person; }
protected Person GetPersonFromForm(string formId) { AttributeValueService attributeValueService = new AttributeValueService(rockContext); PersonService personService = new PersonService(rockContext); PersonAliasService personAliasService = new PersonAliasService(rockContext); var formAttribute = attributeValueService.Queryable().FirstOrDefault(a => a.Value == formId); var person = personService.Queryable().FirstOrDefault(p => p.Id == formAttribute.EntityId); return person; }
/// <summary> /// Binds the grid. /// </summary> private void RunCommand() { gReport.CreatePreviewColumns( typeof( Person ) ); var service = new PersonService(); var people = service.Queryable().Where( p => p.LastName == "Turner" ); var parents = service.Transform( people, new Rock.Reporting.DataTransform.Person.ParentTransform() ); gReport.DataSource = parents.ToList(); gReport.DataBind(); }
/// <summary> /// Gets the group placement registrants. /// </summary> /// <param name="options">The options.</param> /// <param name="currentPerson">The current person.</param> /// <returns></returns> public List <GroupPlacementRegistrant> GetGroupPlacementRegistrants(GetGroupPlacementRegistrantsParameters options, Person currentPerson) { var rockContext = this.Context as RockContext; var registrationRegistrantService = new RegistrationRegistrantService(rockContext); var registrationRegistrantQuery = registrationRegistrantService.Queryable(); registrationRegistrantQuery = registrationRegistrantQuery .Where(a => a.Registration.RegistrationInstance.RegistrationTemplateId == options.RegistrationTemplateId); if (options.RegistrationInstanceId.HasValue) { registrationRegistrantQuery = registrationRegistrantQuery.Where(a => a.Registration.RegistrationInstanceId == options.RegistrationInstanceId.Value); } else if (options.RegistrationTemplateInstanceIds?.Any() == true) { registrationRegistrantQuery = registrationRegistrantQuery.Where(a => options.RegistrationTemplateInstanceIds.Contains(a.Registration.RegistrationInstanceId)); } if (options.RegistrantPersonDataViewFilterId.HasValue) { var dataFilter = new DataViewFilterService(rockContext).Get(options.RegistrantPersonDataViewFilterId.Value); List <string> errorMessages = new List <string>(); var personService = new PersonService(rockContext); var paramExpression = personService.ParameterExpression; var personWhereExpression = dataFilter?.GetExpression(typeof(Person), personService, paramExpression, errorMessages); if (personWhereExpression != null) { var personIdQry = personService.Queryable().Where(paramExpression, personWhereExpression, null).Select(x => x.Id); registrationRegistrantQuery = registrationRegistrantQuery.Where(a => personIdQry.Contains(a.PersonAlias.PersonId)); } } if (options.RegistrantId.HasValue) { registrationRegistrantQuery = registrationRegistrantQuery.Where(a => a.Id == options.RegistrantId.Value); } Block registrationInstanceGroupPlacementBlock = new BlockService(rockContext).Get(options.BlockId); if (registrationInstanceGroupPlacementBlock != null && currentPerson != null) { const string RegistrantAttributeFilter_RegistrationInstanceId = "RegistrantAttributeFilter_RegistrationInstanceId_{0}"; const string RegistrantAttributeFilter_RegistrationTemplateId = "RegistrantAttributeFilter_RegistrationTemplateId_{0}"; string userPreferenceKey; if (options.RegistrationInstanceId.HasValue) { userPreferenceKey = PersonService.GetBlockUserPreferenceKeyPrefix(options.BlockId) + string.Format(RegistrantAttributeFilter_RegistrationInstanceId, options.RegistrationInstanceId); } else { userPreferenceKey = PersonService.GetBlockUserPreferenceKeyPrefix(options.BlockId) + string.Format(RegistrantAttributeFilter_RegistrationTemplateId, options.RegistrationTemplateId); } var attributeFilters = PersonService.GetUserPreference(currentPerson, userPreferenceKey).FromJsonOrNull <Dictionary <int, string> >() ?? new Dictionary <int, string>(); var parameterExpression = registrationRegistrantService.ParameterExpression; Expression registrantWhereExpression = null; foreach (var attributeFilter in attributeFilters) { var attribute = AttributeCache.Get(attributeFilter.Key); var attributeFilterValues = attributeFilter.Value.FromJsonOrNull <List <string> >(); var entityField = EntityHelper.GetEntityFieldForAttribute(attribute); if (entityField != null && attributeFilterValues != null) { var attributeWhereExpression = ExpressionHelper.GetAttributeExpression(registrationRegistrantService, parameterExpression, entityField, attributeFilterValues); if (registrantWhereExpression == null) { registrantWhereExpression = attributeWhereExpression; } else { registrantWhereExpression = Expression.AndAlso(registrantWhereExpression, attributeWhereExpression); } } } if (registrantWhereExpression != null) { registrationRegistrantQuery = registrationRegistrantQuery.Where(parameterExpression, registrantWhereExpression); } } var registrationTemplatePlacement = new RegistrationTemplatePlacementService(rockContext).Get(options.RegistrationTemplatePlacementId); if (options.FilterFeeId.HasValue) { registrationRegistrantQuery = registrationRegistrantQuery.Where(a => a.Fees.Any(f => f.RegistrationTemplateFeeId == options.FilterFeeId.Value)); } if (options.FilterFeeOptionIds?.Any() == true) { registrationRegistrantQuery = registrationRegistrantQuery.Where(a => a.Fees.Any(f => f.RegistrationTemplateFeeItemId.HasValue && options.FilterFeeOptionIds.Contains(f.RegistrationTemplateFeeItemId.Value))); } // don't include registrants that are on the waiting list registrationRegistrantQuery = registrationRegistrantQuery.Where(a => a.OnWaitList == false); registrationRegistrantQuery = registrationRegistrantQuery.OrderBy(a => a.PersonAlias.Person.LastName).ThenBy(a => a.PersonAlias.Person.NickName); var registrationTemplatePlacementService = new RegistrationTemplatePlacementService(rockContext); var registrationInstanceService = new RegistrationInstanceService(rockContext); // get a queryable of PersonIds for the registration template shared groups so we can determine if the registrant has been placed var registrationTemplatePlacementGroupsPersonIdQuery = registrationTemplatePlacementService.GetRegistrationTemplatePlacementPlacementGroups(registrationTemplatePlacement).SelectMany(a => a.Members).Select(a => a.PersonId); // and also get a queryable of PersonIds for the registration instance placement groups so we can determine if the registrant has been placed IQueryable <InstancePlacementGroupPersonId> allInstancesPlacementGroupInfoQuery = null; if (!options.RegistrationInstanceId.HasValue && (options.RegistrationTemplateInstanceIds == null || !options.RegistrationTemplateInstanceIds.Any())) { // if neither RegistrationInstanceId or RegistrationTemplateInstanceIds was specified, use all of the RegistrationTemplates instances options.RegistrationTemplateInstanceIds = new RegistrationTemplateService(rockContext).GetSelect(options.RegistrationTemplateId, s => s.Instances.Select(i => i.Id)).ToArray(); } if (options.RegistrationInstanceId.HasValue) { allInstancesPlacementGroupInfoQuery = registrationInstanceService.GetRegistrationInstancePlacementGroups(registrationInstanceService.Get(options.RegistrationInstanceId.Value)) .Where(a => a.GroupTypeId == registrationTemplatePlacement.GroupTypeId) .SelectMany(a => a.Members).Select(a => a.PersonId) .Select(s => new InstancePlacementGroupPersonId { PersonId = s, RegistrationInstanceId = options.RegistrationInstanceId.Value }); } else if (options.RegistrationTemplateInstanceIds?.Any() == true) { foreach (var registrationInstanceId in options.RegistrationTemplateInstanceIds) { var instancePlacementGroupInfoQuery = registrationInstanceService.GetRegistrationInstancePlacementGroups(registrationInstanceService.Get(registrationInstanceId)) .Where(a => a.GroupTypeId == registrationTemplatePlacement.GroupTypeId) .SelectMany(a => a.Members).Select(a => a.PersonId) .Select(s => new InstancePlacementGroupPersonId { PersonId = s, RegistrationInstanceId = registrationInstanceId }); if (allInstancesPlacementGroupInfoQuery == null) { allInstancesPlacementGroupInfoQuery = instancePlacementGroupInfoQuery; } else { allInstancesPlacementGroupInfoQuery = allInstancesPlacementGroupInfoQuery.Union(instancePlacementGroupInfoQuery); } } } if (allInstancesPlacementGroupInfoQuery == null) { throw new ArgumentNullException("Registration Instance(s) must be specified"); } // select in a way to avoid lazy loading var registrationRegistrantPlacementQuery = registrationRegistrantQuery.Select(r => new { Registrant = r, r.PersonAlias.Person, r.Registration.RegistrationInstance, // marked as AlreadyPlacedInGroup if the Registrant is a member of any of the registrant template placement group or the registration instance placement groups AlreadyPlacedInGroup = registrationTemplatePlacementGroupsPersonIdQuery.Contains(r.PersonAlias.PersonId) || allInstancesPlacementGroupInfoQuery.Any(x => x.RegistrationInstanceId == r.Registration.RegistrationInstanceId && x.PersonId == r.PersonAlias.PersonId) }); var registrationRegistrantPlacementList = registrationRegistrantPlacementQuery.AsNoTracking().ToList(); var groupPlacementRegistrantList = registrationRegistrantPlacementList .Select(x => new GroupPlacementRegistrant(x.Registrant, x.Person, x.AlreadyPlacedInGroup, x.RegistrationInstance, options)) .ToList(); return(groupPlacementRegistrantList.ToList()); }
/// <summary> /// Creates a Linq Expression that can be applied to an IQueryable to filter the result set. /// </summary> /// <param name="entityType">The type of entity in the result set.</param> /// <param name="serviceInstance">A service instance that can be queried to obtain the result set.</param> /// <param name="parameterExpression">The input parameter that will be injected into the filter expression.</param> /// <param name="selection">A formatted string representing the filter settings.</param> /// <returns> /// A Linq Expression that can be used to filter an IQueryable. /// </returns> public override Expression GetExpression( Type entityType, IService serviceInstance, ParameterExpression parameterExpression, string selection ) { var settings = new FilterSettings( selection ); var context = (RockContext)serviceInstance.Context; // // Define Candidate People. // // Get the Person Data View that defines the set of candidates from which matching Group Members can be selected. var dataView = DataComponentSettingsHelper.GetDataViewForFilterComponent( settings.PersonDataViewGuid, context ); var personService = new PersonService( context ); var personQuery = personService.Queryable(); if (dataView != null) { personQuery = DataComponentSettingsHelper.FilterByDataView( personQuery, dataView, personService ); } var personKeys = personQuery.Select( x => x.Id ); // // Construct the Query to return the list of Groups matching the filter conditions. // var comparisonType = settings.PersonCountComparison; int memberCountValue = settings.PersonCount; var memberCountQuery = new GroupService( context ).Queryable(); var memberCountEqualQuery = memberCountQuery.Where( g => g.Members.Count( gm => personKeys.Contains( gm.PersonId ) ) == memberCountValue ); var compareEqualExpression = FilterExpressionExtractor.Extract<Model.Group>( memberCountEqualQuery, parameterExpression, "g" ) as BinaryExpression; var result = FilterExpressionExtractor.AlterComparisonType( comparisonType, compareEqualExpression, 0 ); return result; }
public bool FindDuplicates() { Duplicates = new Dictionary<Guid, List<Person>>(); var rockContext = new RockContext(); var locationService = new LocationService( rockContext ); var groupService = new GroupService( rockContext ); var personService = new PersonService( rockContext ); // Find any other group members (any group) that have same location var othersAtAddress = new List<int>(); string locationKey = GetLocationKey(); if ( !string.IsNullOrWhiteSpace(locationKey) && _verifiedLocations.ContainsKey( locationKey)) { int? locationId = _verifiedLocations[locationKey]; if ( locationId.HasValue ) { var location = locationService.Get( locationId.Value ); if ( location != null ) { othersAtAddress = groupService .Queryable().AsNoTracking() .Where( g => g.GroupTypeId == _locationType.Id && g.GroupLocations.Any( l => l.LocationId == location.Id ) ) .SelectMany( g => g.Members ) .Select( m => m.PersonId ) .ToList(); } } } foreach ( var person in GroupMembers .Where( m => m.Person != null && m.Person.FirstName != "" ) .Select( m => m.Person ) ) { bool otherCriteria = false; var personQry = personService .Queryable().AsNoTracking() .Where( p => p.FirstName == person.FirstName || p.NickName == person.FirstName ); if ( othersAtAddress.Any() ) { personQry = personQry .Where( p => othersAtAddress.Contains( p.Id ) ); } if ( person.BirthDate.HasValue ) { otherCriteria = true; personQry = personQry .Where( p => p.BirthDate.HasValue && p.BirthDate.Value == person.BirthDate.Value ); } if ( _homePhone != null ) { var homePhoneNumber = person.PhoneNumbers.Where( p => p.NumberTypeValueId == _homePhone.Id ).FirstOrDefault(); if ( homePhoneNumber != null ) { otherCriteria = true; personQry = personQry .Where( p => p.PhoneNumbers.Any( n => n.NumberTypeValueId == _homePhone.Id && n.Number == homePhoneNumber.Number ) ); } } if ( _cellPhone != null ) { var cellPhoneNumber = person.PhoneNumbers.Where( p => p.NumberTypeValueId == _cellPhone.Id ).FirstOrDefault(); if ( cellPhoneNumber != null ) { otherCriteria = true; personQry = personQry .Where( p => p.PhoneNumbers.Any( n => n.NumberTypeValueId == _cellPhone.Id && n.Number == cellPhoneNumber.Number ) ); } } if ( !string.IsNullOrWhiteSpace( person.Email ) ) { otherCriteria = true; personQry = personQry .Where( p => p.Email == person.Email ); } var dups = new List<Person>(); if ( otherCriteria ) { // If a birthday, email, phone, or address was entered, find anyone with same info and same first name dups = personQry.ToList(); } else { // otherwise find people with same first and last name dups = personQry .Where( p => p.LastName == person.LastName ) .ToList(); } if ( dups.Any() ) { Duplicates.Add( person.Guid, dups ); } } return Duplicates.Any(); }
private void BindGrid() { var birthDateCol = gPeople.ColumnsOfType<DateField>().First( c => c.DataField == "BirthDate" ); birthDateCol.Visible = GetAttributeValue( "ShowBirthdate" ).AsBoolean(); string type = PageParameter( "SearchType" ); string term = PageParameter( "SearchTerm" ); if ( !string.IsNullOrWhiteSpace( type ) && !string.IsNullOrWhiteSpace( term ) ) { term = term.Trim(); type = type.Trim(); var rockContext = new RockContext(); var personService = new PersonService( rockContext ); IQueryable<Person> people = null; switch ( type.ToLower() ) { case ( "name" ): { bool allowFirstNameOnly = false; if ( !bool.TryParse( PageParameter( "allowFirstNameOnly" ), out allowFirstNameOnly ) ) { allowFirstNameOnly = false; } people = personService.GetByFullName( term, allowFirstNameOnly, true ); break; } case ( "phone" ): { var phoneService = new PhoneNumberService( rockContext ); var personIds = phoneService.GetPersonIdsByNumber( term ); people = personService.Queryable().Where( p => personIds.Contains( p.Id ) ); break; } case ( "address" ): { var groupMemberService = new GroupMemberService( rockContext ); var personIds2 = groupMemberService.GetPersonIdsByHomeAddress( term ); people = personService.Queryable().Where( p => personIds2.Contains( p.Id ) ); break; } case ( "email" ): { people = personService.Queryable().Where( p => p.Email.Contains( term ) ); break; } } var personIdList = people.Select( p => p.Id ).ToList(); people = personService.Queryable(true).Where( p => personIdList.Contains( p.Id ) ); SortProperty sortProperty = gPeople.SortProperty; if ( sortProperty != null ) { people = people.Sort( sortProperty ); } else { people = people.OrderBy( p => p.LastName ).ThenBy( p => p.FirstName ); } Guid familyGuid = new Guid( Rock.SystemGuid.GroupType.GROUPTYPE_FAMILY ); Guid homeAddressTypeGuid = new Guid( Rock.SystemGuid.DefinedValue.GROUP_LOCATION_TYPE_HOME ); var personList = people.Select( p => new PersonSearchResult { Id = p.Id, FirstName = p.FirstName, NickName = p.NickName, LastName = p.LastName, BirthDate = p.BirthDate, BirthYear = p.BirthYear, BirthMonth = p.BirthMonth, BirthDay = p.BirthDay, ConnectionStatusValueId = p.ConnectionStatusValueId, RecordStatusValueId = p.RecordStatusValueId, RecordTypeValueId = p.RecordTypeValueId, SuffixValueId = p.SuffixValueId, IsDeceased = p.IsDeceased, Email = p.Email, Gender = p.Gender, PhotoId = p.PhotoId, CampusIds = p.Members .Where( m => m.Group.GroupType.Guid.Equals( familyGuid ) && m.Group.CampusId.HasValue ) .Select( m => m.Group.CampusId.Value ) .ToList(), HomeAddresses = p.Members .Where( m => m.Group.GroupType.Guid == familyGuid ) .SelectMany( m => m.Group.GroupLocations ) .Where( gl => gl.GroupLocationTypeValue.Guid.Equals( homeAddressTypeGuid ) ) .Select( gl => gl.Location ) } ).ToList(); if ( personList.Count == 1 ) { Response.Redirect( string.Format( "~/Person/{0}", personList[0].Id ), false ); Context.ApplicationInstance.CompleteRequest(); } else { if ( type.ToLower() == "name" ) { var similarNames = personService.GetSimilarNames( term, personList.Select( p => p.Id ).ToList(), true ); if ( similarNames.Any() ) { var hyperlinks = new List<string>(); foreach ( string name in similarNames.Distinct() ) { var pageRef = CurrentPageReference; pageRef.Parameters["SearchTerm"] = name; hyperlinks.Add( string.Format( "<a href='{0}'>{1}</a>", pageRef.BuildUrl(), name ) ); } string altNames = string.Join( ", ", hyperlinks ); nbNotice.Text = string.Format( "Other Possible Matches: {0}", altNames ); nbNotice.Visible = true; } } _inactiveStatus = DefinedValueCache.Read( Rock.SystemGuid.DefinedValue.PERSON_RECORD_STATUS_INACTIVE ); gPeople.EntityTypeId = EntityTypeCache.GetId<Person>(); gPeople.DataSource = personList; gPeople.DataBind(); } } }
/// <summary> /// Gets the matching people for the criteria on the form. /// </summary> /// <returns>a queryable containing the matching people</returns> private IQueryable<Person> GetMatchingPeople() { PersonService personService = new PersonService( new RockContext() ); var photoRequestGroup = Rock.SystemGuid.Group.GROUP_PHOTO_REQUEST.AsGuid(); var familyGroupType = GroupTypeCache.GetFamilyGroupType(); List<int> selectedRoleIds = cblRoles.SelectedValuesAsInt; var selectedConnectionStatuses = cblConnectionStatus.SelectedValuesAsInt; var ageBirthDate = RockDateTime.Now.AddYears( -nbAge.Text.AsInteger() ); var photoUpdatedDate = RockDateTime.Now.AddYears( - nbUpdatedLessThan.Text.AsInteger() ); var people = personService.Queryable("Members", false, false); // people opted out (or pending) var peopleOptedOut = personService.Queryable( "Members", false, false ) .Where ( p => p.Members.Where( gm => gm.Group.Guid == photoRequestGroup && ( gm.GroupMemberStatus == GroupMemberStatus.Inactive || gm.GroupMemberStatus == GroupMemberStatus.Pending ) ).Any() ); // people who have emails addresses people = people.Where( p => ! ( p.Email == null || p.Email.Trim() == string.Empty ) ); // people who match the Connection Status critera people = people.Where( p => cblConnectionStatus.SelectedValuesAsInt.Contains( p.ConnectionStatusValueId ?? -1 ) ); // people who are old enough people = people.Where( p => p.BirthDate <= ageBirthDate ); // people who are in the matching role for a family group people = people.Where( p => p.Members.Where( gm => gm.Group.GroupTypeId == familyGroupType.Id && selectedRoleIds.Contains( gm.GroupRoleId ) ).Any() ); // photo is null or photo is older than our criteria people = people.Where( p => p.PhotoId == null || p.Photo.ModifiedDateTime == null || p.Photo.ModifiedDateTime <= photoUpdatedDate ); // except people who are in the photo-opt-out group as inactive. people = people.Except( peopleOptedOut ); return people; }
/// <summary> /// Binds the attendees grid. /// </summary> private void BindGiversGrid() { // Get all the selected criteria values var dateRange = SlidingDateRangePicker.CalculateDateRangeFromDelimitedValues( drpSlidingDateRange.DelimitedValues ); var start = dateRange.Start; var end = dateRange.End; var minAmount = nreAmount.LowerValue; var maxAmount = nreAmount.UpperValue; var currencyTypeIds = new List<int>(); cblCurrencyTypes.SelectedValues.ForEach( i => currencyTypeIds.Add( i.AsInteger() ) ); var sourceTypeIds = new List<int>(); cblTransactionSource.SelectedValues.ForEach( i => sourceTypeIds.Add( i.AsInteger() ) ); var accountIds = new List<int>(); foreach ( var cblAccounts in phAccounts.Controls.OfType<RockCheckBoxList>() ) { accountIds.AddRange( cblAccounts.SelectedValuesAsInt ); } var dataViewId = dvpDataView.SelectedValueAsInt(); GiversViewBy viewBy = GiversViewBy.Giver; if ( !HideViewByOption ) { viewBy = hfViewBy.Value.ConvertToEnumOrNull<GiversViewBy>() ?? GiversViewBy.Giver; } // Clear all the existing grid columns var selectField = new SelectField(); var oldSelectField = gGiversGifts.ColumnsOfType<SelectField>().FirstOrDefault(); if (oldSelectField != null ) { selectField.SelectedKeys.AddRange( oldSelectField.SelectedKeys ); } gGiversGifts.Columns.Clear(); // Add a column for selecting rows gGiversGifts.Columns.Add( selectField ); // Add a column for the person's name gGiversGifts.Columns.Add( new RockBoundField { DataField = "PersonName", HeaderText = "Person", SortExpression = "LastName,NickName" } ); // add a column for email (but is only included on excel export) gGiversGifts.Columns.Add( new RockBoundField { DataField = "Email", HeaderText = "Email", SortExpression = "Email", Visible = false, ExcelExportBehavior = ExcelExportBehavior.AlwaysInclude } ); // Add a column for total amount gGiversGifts.Columns.Add( new CurrencyField { DataField = "TotalAmount", HeaderText = "Total", SortExpression = "TotalAmount" } ); // Add columns for the selected account totals if ( accountIds.Any() ) { var accounts = new FinancialAccountService( _rockContext ) .Queryable().AsNoTracking() .Where( a => accountIds.Contains( a.Id ) ) .ToList(); foreach ( int accountId in accountIds ) { var account = accounts.FirstOrDefault( a => a.Id == accountId ); if ( account != null ) { gGiversGifts.Columns.Add( new CurrencyField { DataField = account.Id.ToString(), HeaderText = account.Name, SortExpression = account.Id.ToString() } ); } } } // Add a column for the number of gifts var numberGiftsField = new RockBoundField { DataField = "NumberGifts", HeaderText = "Number of Gifts", SortExpression = "NumberGifts", DataFormatString = "{0:N0}", }; numberGiftsField.ItemStyle.HorizontalAlign = HorizontalAlign.Right; gGiversGifts.Columns.Add( numberGiftsField ); // Add a column to indicate if this is a first time giver gGiversGifts.Columns.Add( new BoolField { DataField = "IsFirstEverGift", HeaderText = "Is First Gift", SortExpression = "IsFirstEverGift" } ); // Add a column for the first gift date ( that matches criteria ) gGiversGifts.Columns.Add( new DateField { DataField = "FirstGift", HeaderText = "First Gift", SortExpression = "FirstGift" } ); // Add a column for the first-ever gift date ( to any tax-deductible account ) gGiversGifts.Columns.Add( new DateField { DataField = "FirstEverGift", HeaderText = "First Gift Ever", SortExpression = "FirstEverGift" } ); var transactionDetailService = new FinancialTransactionDetailService( _rockContext ); var personService = new PersonService( _rockContext ); // If dataview was selected get the person id's returned by the dataview var dataViewPersonIds = new List<int>(); if ( dataViewId.HasValue ) { var dataView = new DataViewService( _rockContext ).Get( dataViewId.Value ); if ( dataView != null ) { var errorMessages = new List<string>(); ParameterExpression paramExpression = personService.ParameterExpression; Expression whereExpression = dataView.GetExpression( personService, paramExpression, out errorMessages ); SortProperty sortProperty = null; var dataViewPersonIdQry = personService .Queryable().AsNoTracking() .Where( paramExpression, whereExpression, sortProperty ) .Select( p => p.Id ); dataViewPersonIds = dataViewPersonIdQry.ToList(); } } // Check to see if grid should display only people who gave a certain number of times and if so // set the min value int minCount = 0; var previousPersonIds = new List<int>(); if ( radByPattern.Checked ) { minCount = tbPatternXTimes.Text.AsInteger(); var missedStart = drpPatternDateRange.LowerValue; var missedEnd = drpPatternDateRange.UpperValue; if ( missedStart.HasValue && missedEnd.HasValue ) { // Get the givingids that gave any amount during the pattern's date range. These // are needed so that we know who to exclude from the result set var previousGivingIds = transactionDetailService .Queryable().AsNoTracking() .Where( d => d.Transaction.TransactionDateTime.HasValue && d.Transaction.TransactionDateTime.Value >= missedStart.Value && d.Transaction.TransactionDateTime.Value < missedEnd.Value && accountIds.Contains( d.AccountId ) && d.Amount != 0.0M ) .Select( d => d.Transaction.AuthorizedPersonAlias.Person.GivingId ); // Now get the person ids from the givingids previousPersonIds = personService .Queryable().AsNoTracking() .Where( p => previousGivingIds.Contains( p.GivingId ) ) .Select( p => p.Id ) .ToList(); } } // Call the stored procedure to get all the giving data that matches the selected criteria. // The stored procedure returns two tables. First is a list of all matching transaction summary // information and the second table is each giving leader's first-ever gift date to a tax-deductible account DataSet ds = FinancialTransactionDetailService.GetGivingAnalytics( start, end, minAmount, maxAmount, accountIds, currencyTypeIds, sourceTypeIds, dataViewId, viewBy ); // Get the results table DataTable dtResults = ds.Tables[0]; // Get the first-ever gift dates and load them into a dictionary for faster matching DataTable dtFirstEver = ds.Tables[1]; var firstEverVals = new Dictionary<int, DateTime>(); foreach( DataRow row in ds.Tables[1].Rows ) { if ( !DBNull.Value.Equals( row["FirstEverGift"] ) ) { firstEverVals.Add( (int)row["PersonId"], (DateTime)row["FirstEverGift"] ); } } // Add columns to the result set for the first-ever data dtResults.Columns.Add( new DataColumn( "IsFirstEverGift", typeof( bool ) ) ); dtResults.Columns.Add( new DataColumn( "FirstEverGift", typeof( DateTime ) ) ); foreach( DataRow row in dtResults.Rows ) { bool rowValid = true; // Get the person id int personId = (int)row["Id"]; if ( radByPattern.Checked ) { // If pattern was specified check minimum gifts and other date range int numberGifts = (int)row["NumberGifts"]; if ( numberGifts < minCount ) { rowValid = false; } else { // If this giving leader gave during the pattern date, remove the row since we // only want those who did not if ( previousPersonIds.Contains( personId ) ) { rowValid = false; } } } if ( dataViewId.HasValue ) { // If a dataview filter was specified, and this row is not part of dataview, // remove it if ( !dataViewPersonIds.Contains(personId)) { rowValid = false; // Remove person id from list so that list can be used later to optionally // add rows for remaining people who were in the dataview, but not in the // result set dataViewPersonIds.Remove( personId ); } } if ( rowValid ) { // Set the first ever information for each row bool isFirstEverGift = false; DateTime firstGift = (DateTime)row["FirstGift"]; if ( firstEverVals.ContainsKey( personId ) ) { DateTime firstEverGift = firstEverVals[personId]; isFirstEverGift = firstEverGift.Equals( firstGift ); row["FirstEverGift"] = firstEverGift; } // If only first time givers should be included, remove any that are not if ( radFirstTime.Checked && !isFirstEverGift ) { rowValid = false; } else { row["IsFirstEverGift"] = isFirstEverGift; } } if ( !rowValid ) { row.Delete(); } } // if dataview was selected and it includes people not in the result set, if ( dataViewId.HasValue && rblDataViewAction.SelectedValue == "All" && dataViewPersonIds.Any() ) { // Query for the names of each of these people foreach( var person in personService .Queryable().AsNoTracking() .Select( p => new { p.Id, p.Guid, p.NickName, p.LastName, p.Email })) { // Check for a first ever gift date var firstEverGiftDate = firstEverVals .Where( f => f.Key == person.Id ) .Select( f => f.Value ) .FirstOrDefault(); DataRow row = dtResults.NewRow(); row["Id"] = person.Id; row["Guid"] = person.Guid; row["NickName"] = person.NickName; row["LastName"] = person.LastName; row["PersonName"] = person.NickName + " " + person.LastName; row["Email"] = person.Email; row["IsFirstEverGift"] = false; row["FirstEverGift"] = firstEverGiftDate; dtResults.Rows.Add( row ); } } // Update the changes (deletes) in the datatable dtResults.AcceptChanges(); // Calculate Total if ( viewBy == GiversViewBy.Giver ) { pnlTotal.Visible = true; object amountTotalObj = dtResults.Compute( "Sum(TotalAmount)", null ); if ( amountTotalObj != null ) { decimal amountTotal = amountTotalObj.ToString().AsDecimal(); lTotal.Text = amountTotal.FormatAsCurrency(); } else { lTotal.Text = string.Empty; } } else { pnlTotal.Visible = false; } // Sort the results System.Data.DataView dv = dtResults.DefaultView; if ( gGiversGifts.SortProperty != null ) { try { var sortProperties = new List<string>(); foreach( string prop in gGiversGifts.SortProperty.Property.SplitDelimitedValues(false)) { sortProperties.Add( string.Format( "[{0}] {1}", prop, gGiversGifts.SortProperty.DirectionString ) ); } dv.Sort = sortProperties.AsDelimited( ", " ); } catch { dv.Sort = "[LastName] ASC, [NickName] ASC"; } } else { dv.Sort = "[LastName] ASC, [NickName] ASC"; } gGiversGifts.DataSource = dv; gGiversGifts.DataBind(); }
/// <summary> /// Creates a Linq Expression that can be applied to an IQueryable to filter the result set. /// </summary> /// <param name="entityType">The type of entity in the result set.</param> /// <param name="serviceInstance">A service instance that can be queried to obtain the result set.</param> /// <param name="parameterExpression">The input parameter that will be injected into the filter expression.</param> /// <param name="selection">A formatted string representing the filter settings.</param> /// <returns> /// A Linq Expression that can be used to filter an IQueryable. /// </returns> public override Expression GetExpression( Type entityType, IService serviceInstance, ParameterExpression parameterExpression, string selection ) { var settings = new FilterSettings( selection ); var context = (RockContext)serviceInstance.Context; // // Define Candidate People. // var dataView = DataComponentSettingsHelper.GetDataViewForFilterComponent( settings.PersonDataViewGuid, context ); var personService = new PersonService( context ); var personQuery = personService.Queryable(); if ( dataView != null ) { personQuery = DataComponentSettingsHelper.FilterByDataView( personQuery, dataView, personService ); } var personKeys = personQuery.Select( x => x.Id ); // // Construct the Query to return the list of Group Members matching the filter conditions. // var groupMemberQuery = new GroupMemberService( context ).Queryable(); groupMemberQuery = groupMemberQuery.Where( gm => personKeys.Contains( gm.PersonId ) ); var result = FilterExpressionExtractor.Extract<Rock.Model.GroupMember>( groupMemberQuery, parameterExpression, "gm" ); return result; }
/// <summary> /// Gets the chart data. /// </summary> /// <param name="groupBy">The group by.</param> /// <param name="graphBy">The graph by.</param> /// <param name="startDate">The start date.</param> /// <param name="endDate">The end date.</param> /// <param name="groupIds">The group ids.</param> /// <param name="campusIds">The campus ids. Include the keyword 'null' in the list to include CampusId is null</param> /// <param name="scheduleIds">The schedule ids.</param> /// <param name="dataViewId">The data view identifier.</param> /// <returns></returns> public IEnumerable<IChartData> GetChartData( ChartGroupBy groupBy = ChartGroupBy.Week, AttendanceGraphBy graphBy = AttendanceGraphBy.Total, DateTime? startDate = null, DateTime? endDate = null, string groupIds = null, string campusIds = null, int? dataViewId = null, string scheduleIds = null ) { var qryAttendance = Queryable().AsNoTracking() .Where( a => a.DidAttend.HasValue && a.DidAttend.Value && a.PersonAlias != null ); if ( startDate.HasValue ) { qryAttendance = qryAttendance.Where( a => a.StartDateTime >= startDate.Value ); } if ( endDate.HasValue ) { qryAttendance = qryAttendance.Where( a => a.StartDateTime < endDate.Value ); } if ( dataViewId.HasValue ) { var rockContext = (RockContext)this.Context; var dataView = new DataViewService( rockContext ).Get( dataViewId.Value ); if ( dataView != null ) { var personService = new PersonService( rockContext ); var errorMessages = new List<string>(); ParameterExpression paramExpression = personService.ParameterExpression; Expression whereExpression = dataView.GetExpression( personService, paramExpression, out errorMessages ); Rock.Web.UI.Controls.SortProperty sort = null; var dataViewPersonIdQry = personService .Queryable().AsNoTracking() .Where( paramExpression, whereExpression, sort ) .Select( p => p.Id ); qryAttendance = qryAttendance.Where( a => dataViewPersonIdQry.Contains( a.PersonAlias.PersonId ) ); } } if ( !string.IsNullOrWhiteSpace( groupIds ) ) { var groupIdList = groupIds.Split( ',' ).AsIntegerList(); qryAttendance = qryAttendance.Where( a => a.GroupId.HasValue && groupIdList.Contains( a.GroupId.Value ) ); } // If campuses were included, filter attendances by those that have selected campuses // if 'null' is one of the campuses, treat that as a 'CampusId is Null' var includeNullCampus = ( campusIds ?? "" ).Split( ',' ).ToList().Any( a => a.Equals( "null", StringComparison.OrdinalIgnoreCase ) ); var campusIdList = ( campusIds ?? "" ).Split( ',' ).AsIntegerList(); // remove 0 from the list, just in case it is there campusIdList.Remove( 0 ); if ( campusIdList.Any() ) { if ( includeNullCampus ) { // show records that have a campusId in the campusIdsList + records that have a null campusId qryAttendance = qryAttendance.Where( a => ( a.CampusId.HasValue && campusIdList.Contains( a.CampusId.Value ) ) || !a.CampusId.HasValue ); } else { // only show records that have a campusId in the campusIdList qryAttendance = qryAttendance.Where( a => a.CampusId.HasValue && campusIdList.Contains( a.CampusId.Value ) ); } } else if ( includeNullCampus ) { // 'null' was the only campusId in the campusIds parameter, so only show records that have a null CampusId qryAttendance = qryAttendance.Where( a => !a.CampusId.HasValue ); } // If schedules were included, filter attendances by those that have selected schedules var scheduleIdList = ( scheduleIds ?? "" ).Split( ',' ).AsIntegerList(); scheduleIdList.Remove( 0 ); if ( scheduleIdList.Any() ) { qryAttendance = qryAttendance.Where( a => a.ScheduleId.HasValue && scheduleIdList.Contains( a.ScheduleId.Value ) ); } var qryAttendanceWithSummaryDateTime = qryAttendance.GetAttendanceWithSummaryDateTime( groupBy ); var summaryQry = qryAttendanceWithSummaryDateTime.Select( a => new { a.SummaryDateTime, Campus = new { Id = a.Attendance.CampusId, Name = a.Attendance.Campus.Name }, Group = new { Id = a.Attendance.GroupId, Name = a.Attendance.Group.Name }, Schedule = new { Id = a.Attendance.ScheduleId, Name = a.Attendance.Schedule.Name }, Location = new { Id = a.Attendance.LocationId, Name = a.Attendance.Location.Name } } ); List<SummaryData> result = null; if ( graphBy == AttendanceGraphBy.Total ) { var groupByQry = summaryQry.GroupBy( a => new { a.SummaryDateTime } ).Select( s => new { s.Key, Count = s.Count() } ).OrderBy( o => o.Key ); result = groupByQry.ToList().Select( a => new SummaryData { DateTimeStamp = a.Key.SummaryDateTime.ToJavascriptMilliseconds(), DateTime = a.Key.SummaryDateTime, SeriesName = "Total", YValue = a.Count } ).ToList(); } else if ( graphBy == AttendanceGraphBy.Campus ) { var groupByQry = summaryQry.GroupBy( a => new { a.SummaryDateTime, Series = a.Campus } ).Select( s => new { s.Key, Count = s.Count() } ).OrderBy( o => o.Key ); result = groupByQry.ToList().Select( a => new SummaryData { DateTimeStamp = a.Key.SummaryDateTime.ToJavascriptMilliseconds(), DateTime = a.Key.SummaryDateTime, SeriesName = a.Key.Series.Name, YValue = a.Count } ).ToList(); } else if ( graphBy == AttendanceGraphBy.Group ) { var groupByQry = summaryQry.GroupBy( a => new { a.SummaryDateTime, Series = a.Group } ).Select( s => new { s.Key, Count = s.Count() } ).OrderBy( o => o.Key ); result = groupByQry.ToList().Select( a => new SummaryData { DateTimeStamp = a.Key.SummaryDateTime.ToJavascriptMilliseconds(), DateTime = a.Key.SummaryDateTime, SeriesName = a.Key.Series.Name, YValue = a.Count } ).ToList(); } else if ( graphBy == AttendanceGraphBy.Schedule ) { var groupByQry = summaryQry.GroupBy( a => new { a.SummaryDateTime, Series = a.Schedule } ).Select( s => new { s.Key, Count = s.Count() } ).OrderBy( o => o.Key ); result = groupByQry.ToList().Select( a => new SummaryData { DateTimeStamp = a.Key.SummaryDateTime.ToJavascriptMilliseconds(), DateTime = a.Key.SummaryDateTime, SeriesName = a.Key.Series.Name, YValue = a.Count } ).ToList(); } else if ( graphBy == AttendanceGraphBy.Location ) { var groupByQry = summaryQry.GroupBy( a => new { a.SummaryDateTime, Series = a.Location } ).Select( s => new { s.Key, Count = s.Count() } ).OrderBy( o => o.Key ); result = groupByQry.ToList().Select( a => new SummaryData { DateTimeStamp = a.Key.SummaryDateTime.ToJavascriptMilliseconds(), DateTime = a.Key.SummaryDateTime, SeriesName = a.Key.Series.Name, YValue = a.Count } ).ToList(); } return result; }
/// <summary> /// Binds the grid. /// </summary> private void BindGrid() { using ( var rockContext = new RockContext() ) { var personService = new PersonService( rockContext ); var qry = personService.Queryable().AsNoTracking() .Select( p => new { p.Id, p.NickName, p.LastName } ); AddGridColumns( qry.FirstOrDefault() ); SortProperty sortProperty = gReport.SortProperty; if ( sortProperty != null ) { gReport.SetLinqDataSource( qry.Sort( sortProperty ) ); } else { gReport.SetLinqDataSource( qry.OrderBy( p => p.LastName ).ThenBy( p => p.LastName ) ); } gReport.DataBind(); } }
/// <summary> /// Authenticates the specified request. /// </summary> /// <param name="request">The request.</param> /// <param name="username">The username.</param> /// <param name="returnUrl">The return URL.</param> /// <returns></returns> public override Boolean Authenticate( HttpRequest request, out string username, out string returnUrl ) { var fbClient = new FacebookClient(); FacebookOAuthResult oAuthResult; if ( fbClient.TryParseOAuthCallbackUrl( request.Url, out oAuthResult ) && oAuthResult.IsSuccess ) { try { var redirectUri = new Uri( GetRedirectUrl( request ) ); dynamic parameters = new ExpandoObject(); parameters.client_id = GetAttributeValue( "AppID" ); parameters.client_secret = GetAttributeValue( "AppSecret" ); parameters.redirect_uri = redirectUri.AbsoluteUri; parameters.code = oAuthResult.Code; dynamic result = fbClient.Post( "oauth/access_token", parameters ); string accessToken = result.access_token; fbClient = new FacebookClient( accessToken ); dynamic me = fbClient.Get( "me" ); string facebookId = "FACEBOOK_" + me.id.ToString(); // query for matching id in the user table var userLoginService = new UserLoginService(); var user = userLoginService.GetByUserName( facebookId ); // if not user was found see if we can find a match in the person table if ( user == null ) { try { // determine if we can find a match and if so add an user login record // get properties from Facebook dynamic object string lastName = me.last_name.ToString(); string firstName = me.first_name.ToString(); string email = me.email.ToString(); var personService = new PersonService(); var person = personService.Queryable().FirstOrDefault( u => u.LastName == lastName && u.FirstName == firstName && u.Email == email ); if ( person != null ) { // since we have the data enter the birthday from Facebook to the db if we don't have it yet DateTime birthdate = Convert.ToDateTime( me.birthday.ToString() ); if ( person.BirthDay == null ) { person.BirthDate = birthdate; personService.Save( person, person.Id ); } } else { var dvService = new DefinedValueService(); person = new Person(); person.IsSystem = false; person.RecordTypeValueId = dvService.GetIdByGuid( new Guid( SystemGuid.DefinedValue.PERSON_RECORD_TYPE_PERSON ) ); person.RecordStatusValueId = dvService.GetIdByGuid( new Guid( SystemGuid.DefinedValue.PERSON_RECORD_STATUS_ACTIVE ) ); person.FirstName = me.first_name.ToString(); person.LastName = me.last_name.ToString(); person.Email = me.email.ToString(); if ( me.gender.ToString() == "male" ) person.Gender = Gender.Male; else if ( me.gender.ToString() == "female" ) person.Gender = Gender.Female; else person.Gender = Gender.Unknown; person.BirthDate = Convert.ToDateTime( me.birthday.ToString() ); person.DoNotEmail = false; personService.Add( person, null ); personService.Save( person, null ); } user = userLoginService.Create( person, AuthenticationServiceType.External, this.TypeId, facebookId, "fb", true, person.Id ); } catch ( Exception ex ) { string msg = ex.Message; // TODO: probably should report something... } // TODO: Show label indicating inability to find user corresponding to facebook id } username = user.UserName; returnUrl = oAuthResult.State; return true; } catch ( FacebookOAuthException oae ) { string msg = oae.Message; // TODO: Add error handeling // Error validating verification code. (usually from wrong return url very picky with formatting) // Error validating client secret. // Error validating application. } } username = null; returnUrl = null; return false; }
/// <summary> /// Handles adding families from the given XML element snippet /// </summary> /// <param name="elemFamilies">The xml element containing all the families.</param> /// <param name="rockContext">The rock context.</param> private void AddFamilies( XElement elemFamilies, RockContext rockContext ) { if ( elemFamilies == null ) { return; } bool fabricateAttendance = GetAttributeValue( "FabricateAttendance" ).AsBoolean(); GroupService groupService = new GroupService( rockContext ); var allFamilies = rockContext.Groups; List<Group> allGroups = new List<Group>(); // Next create the family along with its members. foreach ( var elemFamily in elemFamilies.Elements( "family" ) ) { Guid guid = elemFamily.Attribute( "guid" ).Value.Trim().AsGuid(); var familyMembers = BuildFamilyMembersFromXml( elemFamily.Element( "members" ), rockContext ); // Call replica of groupService's SaveNewFamily method in an attempt to speed things up Group family = CreateNewFamily( familyMembers, campusId: 1 ); family.Guid = guid; // add the family to the context's list of groups allFamilies.Add( family ); // add the families address(es) AddFamilyAddresses( groupService, family, elemFamily.Element( "addresses" ), rockContext ); // add their attendance data if ( fabricateAttendance ) { AddFamilyAttendance( family, elemFamily, rockContext ); } allGroups.Add( family ); _stopwatch.Stop(); _sb.AppendFormat( "{0:00}:{1:00}.{2:00} added {3}<br/>", _stopwatch.Elapsed.Minutes, _stopwatch.Elapsed.Seconds, _stopwatch.Elapsed.Milliseconds / 10, family.Name ); _stopwatch.Start(); } rockContext.ChangeTracker.DetectChanges(); rockContext.SaveChanges( disablePrePostProcessing: true ); // Now save each person's attributevalues (who had them defined in the XML) // and add each person's ID to a dictionary for use later. AttributeValueService attributeValueService = new AttributeValueService( rockContext ); foreach ( var gm in allGroups.SelectMany( g => g.Members ) ) { // Put the person's id into the people dictionary for later use. if ( !_peopleDictionary.ContainsKey( gm.Person.Guid ) ) { _peopleDictionary.Add( gm.Person.Guid, gm.Person.Id ); } // Only save if the person had attributes, otherwise it will error. if ( _personWithAttributes.ContainsKey( gm.Person.Guid ) ) { foreach ( var attributeCache in gm.Person.Attributes.Select( a => a.Value ) ) { var newValue = gm.Person.AttributeValues[attributeCache.Key].FirstOrDefault(); if ( newValue != null ) { newValue.EntityId = gm.Person.Id; rockContext.AttributeValues.Add( newValue ); } } } } _stopwatch.Stop(); _sb.AppendFormat( "{0:00}:{1:00}.{2:00} saved attributes for everyone <br/>", _stopwatch.Elapsed.Minutes, _stopwatch.Elapsed.Seconds, _stopwatch.Elapsed.Milliseconds / 10 ); _stopwatch.Start(); // Create person alias records for each person PersonService personService = new PersonService( rockContext ); foreach ( var person in personService.Queryable( "Aliases" ) .Where( p => _peopleDictionary.Keys.Contains( p.Guid ) && !p.Aliases.Any() ) ) { person.Aliases.Add( new PersonAlias { AliasPersonId = person.Id, AliasPersonGuid = person.Guid } ); } _stopwatch.Stop(); _sb.AppendFormat( "{0:00}:{1:00}.{2:00} added person aliases<br/>", _stopwatch.Elapsed.Minutes, _stopwatch.Elapsed.Seconds, _stopwatch.Elapsed.Milliseconds / 10 ); _stopwatch.Start(); }
/// <summary> /// Authenticates the specified request. /// </summary> /// <param name="request">The request.</param> /// <param name="username">The username.</param> /// <param name="returnUrl">The return URL.</param> /// <returns></returns> public override Boolean Authenticate( HttpRequest request, out string username, out string returnUrl ) { var fbClient = new FacebookClient(); FacebookOAuthResult oAuthResult; if ( fbClient.TryParseOAuthCallbackUrl( request.Url, out oAuthResult ) && oAuthResult.IsSuccess ) { try { var redirectUri = new Uri( GetRedirectUrl( request ) ); dynamic parameters = new ExpandoObject(); parameters.client_id = GetAttributeValue( "AppID" ); parameters.client_secret = GetAttributeValue( "AppSecret" ); parameters.redirect_uri = redirectUri.AbsoluteUri; parameters.code = oAuthResult.Code; dynamic result = fbClient.Post( "oauth/access_token", parameters ); string accessToken = result.access_token; fbClient = new FacebookClient( accessToken ); dynamic me = fbClient.Get( "me" ); string facebookId = "FACEBOOK_" + me.id.ToString(); UserLogin user = null; var rockContext = new RockContext(); rockContext.WrapTransaction( () => { // query for matching id in the user table var userLoginService = new UserLoginService( rockContext ); user = userLoginService.GetByUserName( facebookId ); // if no user was found see if we can find a match in the person table if ( user == null ) { try { var familyChanges = new List<string>(); var familyMemberChanges = new List<string>(); var PersonChanges = new List<string>(); // determine if we can find a match and if so add an user login record // get properties from Facebook dynamic object string lastName = me.last_name.ToString(); string firstName = me.first_name.ToString(); string email = me.email.ToString(); var personService = new PersonService( rockContext ); var person = personService.Queryable( "Aliases" ).FirstOrDefault( u => u.LastName == lastName && u.FirstName == firstName && u.Email == email ); if ( person != null ) { // since we have the data enter the birthday from Facebook to the db if we don't have it yet DateTime birthdate = Convert.ToDateTime( me.birthday.ToString() ); if ( person.BirthDay == null ) { History.EvaluateChange( PersonChanges, "Birth Date", person.BirthDate, person.BirthDate ); person.BirthDate = birthdate; rockContext.SaveChanges(); HistoryService.SaveChanges( rockContext, typeof( Person ), Rock.SystemGuid.Category.HISTORY_PERSON_DEMOGRAPHIC_CHANGES.AsGuid(), person.Id, PersonChanges ); } } else { person = new Person(); person.IsSystem = false; person.RecordTypeValueId = DefinedValueCache.Read( SystemGuid.DefinedValue.PERSON_RECORD_TYPE_PERSON.AsGuid() ).Id; person.RecordStatusValueId = DefinedValueCache.Read( SystemGuid.DefinedValue.PERSON_RECORD_STATUS_PENDING.AsGuid() ).Id; person.FirstName = me.first_name.ToString(); person.LastName = me.last_name.ToString(); person.Email = me.email.ToString(); if ( me.gender.ToString() == "male" ) person.Gender = Gender.Male; else if ( me.gender.ToString() == "female" ) person.Gender = Gender.Female; else person.Gender = Gender.Unknown; person.BirthDate = Convert.ToDateTime( me.birthday.ToString() ); person.EmailPreference = EmailPreference.EmailAllowed; GroupService.SaveNewFamily( rockContext, person, null, false ); } user = UserLoginService.Create( rockContext, person, AuthenticationServiceType.External, this.TypeId, facebookId, "fb", true ); } catch ( Exception ex ) { string msg = ex.Message; // TODO: probably should report something... } } else { // TODO: Show label indicating inability to find user corresponding to facebook id } } ); if ( user != null ) { username = user.UserName; returnUrl = oAuthResult.State; return true; } else { username = string.Empty; returnUrl = string.Empty; return false; } } catch ( FacebookOAuthException oae ) { string msg = oae.Message; // TODO: Add error handeling // Error validating verification code. (usually from wrong return url very picky with formatting) // Error validating client secret. // Error validating application. } } username = null; returnUrl = null; return false; }
/// <summary> /// Binds the grid. /// </summary> protected void BindGrid() { var showProfilesViewed = GetAttributeValue( "SeeProfilesViewed" ).AsBoolean(); var rockContext = new RockContext(); var personViewedService = new PersonViewedService( rockContext ); var personService = new PersonService( rockContext ); if ( showProfilesViewed ) { // This grid should show the profiles viewed by this person. pnlViewed.Visible = true; pnlViewedBy.Visible = false; if ( personId.HasValue ) { var viewedList = personViewedService.Queryable() .Where( p => p.ViewerPersonAlias != null && p.ViewerPersonAlias.PersonId == personId ) .GroupBy( p => p.TargetPersonAlias.PersonId ) .Select( p => new { TargetPersonId = p.Key, FirstViewed = p.Min( g => g.ViewDateTime ), LastViewed = p.Max( g => g.ViewDateTime ), ViewedCount = p.Count() } ); var pQry = personService.Queryable(); var qry = viewedList .Join( pQry, v => v.TargetPersonId, p => p.Id, ( v, p ) => new { p.Id, FullName = p.NickName + " " + p.LastName, p.BirthDate, p.Gender, FirstViewedDate = v.FirstViewed, LastViewedDate = v.LastViewed, ViewedCount = v.ViewedCount } ); SortProperty sortProperty = gViewed.SortProperty; if ( sortProperty != null ) { qry = qry.Sort( sortProperty ); } else { qry = qry.OrderByDescending( q => q.LastViewedDate ); } gViewed.DataSource = qry.ToList(); gViewed.DataBind(); } } else { // This grid should show the profiles that have viewed this person. pnlViewed.Visible = false; pnlViewedBy.Visible = true; if ( personId.HasValue ) { var viewedList = personViewedService.Queryable() .Where( p => p.TargetPersonAlias != null && p.TargetPersonAlias.PersonId == personId ) .GroupBy( p => p.ViewerPersonAlias.PersonId ) .Select( p => new { ViewerPersonId = p.Key, FirstViewed = p.Min( g => g.ViewDateTime ), LastViewed = p.Max( g => g.ViewDateTime ), ViewedCount = p.Count() } ); var pQry = personService.Queryable(); var qry = viewedList .Join( pQry, v => v.ViewerPersonId, p => p.Id, ( v, p ) => new { p.Id, FullName = p.NickName + " " + p.LastName, p.BirthDate, p.Gender, FirstViewedDate = v.FirstViewed, LastViewedDate = v.LastViewed, ViewedCount = v.ViewedCount } ); SortProperty sortProperty = gViewedBy.SortProperty; if ( sortProperty != null ) { qry = qry.Sort( sortProperty ); } else { qry = qry.OrderByDescending( q => q.LastViewedDate ); } gViewedBy.DataSource = qry.ToList(); gViewedBy.DataBind(); } } }
/// <summary> /// Does cleanup of Person Aliases and Metaphones /// </summary> /// <param name="dataMap">The data map.</param> private void PersonCleanup( JobDataMap dataMap ) { // Add any missing person aliases var personRockContext = new Rock.Data.RockContext(); PersonService personService = new PersonService( personRockContext ); foreach ( var person in personService.Queryable( "Aliases" ) .Where( p => !p.Aliases.Any() ) .Take( 300 ) ) { person.Aliases.Add( new PersonAlias { AliasPersonId = person.Id, AliasPersonGuid = person.Guid } ); } personRockContext.SaveChanges(); // Add any missing metaphones int namesToProcess = dataMap.GetString( "MaxMetaphoneNames" ).AsInteger(); if ( namesToProcess > 0 ) { var firstNameQry = personService.Queryable().Select( p => p.FirstName ).Where( p => p != null ); var nickNameQry = personService.Queryable().Select( p => p.NickName ).Where( p => p != null ); var lastNameQry = personService.Queryable().Select( p => p.LastName ).Where( p => p != null ); var nameQry = firstNameQry.Union( nickNameQry.Union( lastNameQry ) ); var metaphones = personRockContext.Metaphones; var existingNames = metaphones.Select( m => m.Name ).Distinct(); // Get the names that have not yet been processed var namesToUpdate = nameQry .Where( n => !existingNames.Contains( n ) ) .Take( namesToProcess ) .ToList(); foreach ( string name in namesToUpdate ) { string mp1 = string.Empty; string mp2 = string.Empty; Rock.Utility.DoubleMetaphone.doubleMetaphone( name, ref mp1, ref mp2 ); var metaphone = new Metaphone(); metaphone.Name = name; metaphone.Metaphone1 = mp1; metaphone.Metaphone2 = mp2; metaphones.Add( metaphone ); } personRockContext.SaveChanges(); } }
/// <summary> /// Checks the database for existing import data. /// returns false if an error occurred /// </summary> private bool LoadExistingData( string importUser ) { //try //{ var lookupContext = new RockContext(); var personService = new PersonService( lookupContext ); var importPerson = personService.GetByFullName( importUser, includeDeceased: false, allowFirstNameOnly: true ).FirstOrDefault(); if ( importPerson == null ) { importPerson = personService.Queryable().FirstOrDefault(); if ( importPerson == null ) { LogException( "CheckExistingImport", "The named import user was not found, and none could be created." ); return false; } } ImportPersonAlias = new PersonAliasService( lookupContext ).Get( importPerson.Id ); PersonEntityTypeId = EntityTypeCache.Read( "Rock.Model.Person" ).Id; FamilyGroupTypeId = GroupTypeCache.GetFamilyGroupType().Id; ReportProgress( 0, "Checking for existing people..." ); ImportedPeople = new GroupService( lookupContext ).GetByGroupTypeId( FamilyGroupTypeId ).Where( n => n.ForeignId != null ).ToList(); CampusList = new CampusService( lookupContext ).Queryable().ToList(); return true; //} //catch ( Exception ex ) //{ // LogException( "CheckExistingImport", ex.ToString() ); // return false; //} }
/// <summary> /// Maps the communication data. /// </summary> /// <param name="tableData">The table data.</param> /// <returns></returns> private void MapCommunication( IQueryable<Row> tableData ) { var lookupContext = new RockContext(); var personService = new PersonService( lookupContext ); var attributeService = new AttributeService( lookupContext ); var definedTypePhoneType = DefinedTypeCache.Read( new Guid( Rock.SystemGuid.DefinedType.PERSON_PHONE_TYPE ), lookupContext ); var otherNumberType = definedTypePhoneType.DefinedValues.Where( dv => dv.Value.StartsWith( "Other" ) ).Select( v => (int?)v.Id ).FirstOrDefault(); if ( otherNumberType == null ) { var otherType = new DefinedValue(); otherType.IsSystem = false; otherType.DefinedTypeId = definedTypePhoneType.Id; otherType.Order = 0; otherType.Value = "Other"; otherType.Description = "Imported from FellowshipOne"; otherType.CreatedByPersonAliasId = ImportPersonAliasId; lookupContext.DefinedValues.Add( otherType ); lookupContext.SaveChanges( DisableAuditing ); } // Look up existing Person attributes var personAttributes = attributeService.GetByEntityTypeId( PersonEntityTypeId ).ToList(); // Cached Rock attributes: Facebook, Twitter, Instagram var twitterAttribute = AttributeCache.Read( personAttributes.FirstOrDefault( a => a.Key.Equals( "Twitter", StringComparison.InvariantCultureIgnoreCase ) ) ); var facebookAttribute = AttributeCache.Read( personAttributes.FirstOrDefault( a => a.Key.Equals( "Facebook", StringComparison.InvariantCultureIgnoreCase ) ) ); var instagramAttribute = AttributeCache.Read( personAttributes.FirstOrDefault( a => a.Key.Equals( "Instagram", StringComparison.InvariantCultureIgnoreCase ) ) ); var newNumbers = new List<PhoneNumber>(); var existingNumbers = new PhoneNumberService( lookupContext ).Queryable().AsNoTracking().ToList(); var newPeopleAttributes = new Dictionary<int, Person>(); int completed = 0; int totalRows = tableData.Count(); int percentage = ( totalRows - 1 ) / 100 + 1; ReportProgress( 0, string.Format( "Verifying communication import ({0:N0} found, {1:N0} already exist).", totalRows, existingNumbers.Count ) ); foreach ( var groupedRows in tableData.OrderByDescending( r => r["LastUpdatedDate"] ).GroupBy<Row, int?>( r => r["Household_ID"] as int? ) ) { foreach ( var row in groupedRows.Where( r => r != null ) ) { string value = row["Communication_Value"] as string; int? individualId = row["Individual_ID"] as int?; int? householdId = row["Household_ID"] as int?; var peopleToUpdate = new List<PersonKeys>(); if ( individualId != null ) { var matchingPerson = GetPersonKeys( individualId, householdId, includeVisitors: false ); if ( matchingPerson != null ) { peopleToUpdate.Add( matchingPerson ); } } else { peopleToUpdate = GetFamilyByHouseholdId( householdId, includeVisitors: false ); } if ( peopleToUpdate.Any() && !string.IsNullOrWhiteSpace( value ) ) { DateTime? lastUpdated = row["LastUpdatedDate"] as DateTime?; string communicationComment = row["Communication_Comment"] as string; string type = row["Communication_Type"] as string; bool isListed = (bool)row["Listed"]; value = value.RemoveWhitespace(); // Communication value is a number if ( type.Contains( "Phone" ) || type.Contains( "Mobile" ) ) { var extension = string.Empty; var countryCode = PhoneNumber.DefaultCountryCode(); var normalizedNumber = string.Empty; var countryIndex = value.IndexOf( '+' ); int extensionIndex = value.LastIndexOf( 'x' ) > 0 ? value.LastIndexOf( 'x' ) : value.Length; if ( countryIndex >= 0 ) { countryCode = value.Substring( countryIndex, countryIndex + 3 ).AsNumeric(); normalizedNumber = value.Substring( countryIndex + 3, extensionIndex - 3 ).AsNumeric(); extension = value.Substring( extensionIndex ); } else if ( extensionIndex > 0 ) { normalizedNumber = value.Substring( 0, extensionIndex ).AsNumeric(); extension = value.Substring( extensionIndex ).AsNumeric(); } else { normalizedNumber = value.AsNumeric(); } if ( !string.IsNullOrWhiteSpace( normalizedNumber ) ) { foreach ( var personKeys in peopleToUpdate ) { bool numberExists = existingNumbers.Any( n => n.PersonId == personKeys.PersonId && n.Number.Equals( value ) ); if ( !numberExists ) { var newNumber = new PhoneNumber(); newNumber.CreatedByPersonAliasId = ImportPersonAliasId; newNumber.ModifiedDateTime = lastUpdated; newNumber.PersonId = (int)personKeys.PersonId; newNumber.IsMessagingEnabled = false; newNumber.CountryCode = countryCode; newNumber.IsUnlisted = !isListed; newNumber.Extension = extension.Left( 20 ) ?? string.Empty; newNumber.Number = normalizedNumber.Left( 20 ); newNumber.Description = communicationComment; newNumber.NumberFormatted = PhoneNumber.FormattedNumber( countryCode, newNumber.Number, true ); var matchingNumberType = definedTypePhoneType.DefinedValues.Where( v => type.StartsWith( v.Value ) ) .Select( v => (int?)v.Id ).FirstOrDefault(); newNumber.NumberTypeValueId = matchingNumberType ?? otherNumberType; newNumbers.Add( newNumber ); existingNumbers.Add( newNumber ); } } completed++; } } else { Person person = null; var personKeys = peopleToUpdate.FirstOrDefault(); if ( !newPeopleAttributes.ContainsKey( personKeys.PersonId ) ) { // not in dictionary, get person from database person = personService.Queryable( includeDeceased: true ).FirstOrDefault( p => p.Id == personKeys.PersonId ); } else { // reuse person from dictionary person = newPeopleAttributes[personKeys.PersonId]; } if ( person != null ) { if ( person.Attributes == null || person.AttributeValues == null ) { // make sure we have valid objects to assign to person.Attributes = new Dictionary<string, AttributeCache>(); person.AttributeValues = new Dictionary<string, AttributeValueCache>(); } // Check for an InFellowship ID/email before checking other types of email var isLoginValue = type.IndexOf( "InFellowship", StringComparison.OrdinalIgnoreCase ) >= 0; var personAlreadyHasLogin = person.Attributes.ContainsKey( InFellowshipLoginAttribute.Key ); if ( isLoginValue && !personAlreadyHasLogin ) { AddPersonAttribute( InFellowshipLoginAttribute, person, value ); AddUserLogin( AuthProviderEntityTypeId, person, value ); } else if ( value.IsEmail() ) { // person email is empty if ( string.IsNullOrWhiteSpace( person.Email ) ) { person.Email = value.Left( 75 ); person.IsEmailActive = isListed; person.EmailPreference = isListed ? EmailPreference.EmailAllowed : EmailPreference.DoNotEmail; person.ModifiedDateTime = lastUpdated; person.EmailNote = communicationComment; lookupContext.SaveChanges( DisableAuditing ); } // this is a different email, assign it to SecondaryEmail else if ( !person.Email.Equals( value ) && !person.Attributes.ContainsKey( SecondaryEmailAttribute.Key ) ) { AddPersonAttribute( SecondaryEmailAttribute, person, value ); } } else if ( type.Contains( "Twitter" ) && !person.Attributes.ContainsKey( twitterAttribute.Key ) ) { AddPersonAttribute( twitterAttribute, person, value ); } else if ( type.Contains( "Facebook" ) && !person.Attributes.ContainsKey( facebookAttribute.Key ) ) { AddPersonAttribute( facebookAttribute, person, value ); } else if ( type.Contains( "Instagram" ) && !person.Attributes.ContainsKey( instagramAttribute.Key ) ) { AddPersonAttribute( instagramAttribute, person, value ); } if ( !newPeopleAttributes.ContainsKey( personKeys.PersonId ) ) { newPeopleAttributes.Add( personKeys.PersonId, person ); } else { newPeopleAttributes[personKeys.PersonId] = person; } } completed++; } if ( completed % percentage < 1 ) { int percentComplete = completed / percentage; ReportProgress( percentComplete, string.Format( "{0:N0} records imported ({1}% complete).", completed, percentComplete ) ); } else if ( completed % ReportingNumber < 1 ) { if ( newNumbers.Any() || newPeopleAttributes.Any() ) { SaveCommunication( newNumbers, newPeopleAttributes ); } // reset so context doesn't bloat lookupContext = new RockContext(); personService = new PersonService( lookupContext ); newPeopleAttributes.Clear(); newNumbers.Clear(); ReportPartialProgress(); } } } } if ( newNumbers.Any() || newPeopleAttributes.Any() ) { SaveCommunication( newNumbers, newPeopleAttributes ); } ReportProgress( 100, string.Format( "Finished communication import: {0:N0} records imported.", completed ) ); }
/// <summary> /// Binds the grid. /// </summary> private void BindGrid() { RockContext rockContext = new RockContext(); PersonService personService = new PersonService( rockContext ); // sample query to display a few people var qry = personService.Queryable() .Where( p => p.Gender == Gender.Male) .Take(10); gList.DataSource = qry.ToList(); gList.DataBind(); }
/// <summary> /// Shows the details. /// </summary> private void ShowDetails() { _rockContext = _rockContext ?? new RockContext(); if ( _group != null ) { // Show lava content var mergeFields = new Dictionary<string, object>(); mergeFields.Add( "Group", _group ); bool showDebug = UserCanEdit && GetAttributeValue( "EnableDebug" ).AsBoolean(); lLavaOutputDebug.Visible = showDebug; if ( showDebug ) { lLavaOutputDebug.Text = mergeFields.lavaDebugInfo( _rockContext ); } string template = GetAttributeValue( "LavaTemplate" ); lLavaOverview.Text = template.ResolveMergeFields( mergeFields ); // Set visibility based on selected mode if ( IsFullWithSpouse ) { pnlCol1.RemoveCssClass( "col-md-12" ).AddCssClass( "col-md-6" ); } else { pnlCol1.RemoveCssClass( "col-md-6" ).AddCssClass( "col-md-12" ); } pnlCol2.Visible = IsFullWithSpouse; pnlHomePhone.Visible = !IsSimple; pnlCellPhone.Visible = !IsSimple; acAddress.Visible = !IsSimple; if ( CurrentPersonId.HasValue && _autoFill ) { var personService = new PersonService( _rockContext ); Person person = personService .Queryable( "PhoneNumbers.NumberTypeValue" ).AsNoTracking() .FirstOrDefault( p => p.Id == CurrentPersonId.Value ); tbFirstName.Text = CurrentPerson.FirstName; tbLastName.Text = CurrentPerson.LastName; tbEmail.Text = CurrentPerson.Email; if ( !IsSimple ) { Guid homePhoneType = Rock.SystemGuid.DefinedValue.PERSON_PHONE_TYPE_HOME.AsGuid(); var homePhone = person.PhoneNumbers .FirstOrDefault( n => n.NumberTypeValue.Guid.Equals( homePhoneType ) ); if ( homePhone != null ) { pnHome.Text = homePhone.Number; } Guid cellPhoneType = Rock.SystemGuid.DefinedValue.PERSON_PHONE_TYPE_MOBILE.AsGuid(); var cellPhone = person.PhoneNumbers .FirstOrDefault( n => n.NumberTypeValue.Guid.Equals( cellPhoneType ) ); if ( cellPhone != null ) { pnCell.Text = cellPhone.Number; cbSms.Checked = cellPhone.IsMessagingEnabled; } var homeAddress = person.GetHomeLocation(); if ( homeAddress != null ) { acAddress.SetValues( homeAddress ); } if ( IsFullWithSpouse ) { var spouse = person.GetSpouse( _rockContext ); if ( spouse != null ) { tbSpouseFirstName.Text = spouse.FirstName; tbSpouseLastName.Text = spouse.LastName; tbSpouseEmail.Text = spouse.Email; var spouseCellPhone = spouse.PhoneNumbers .FirstOrDefault( n => n.NumberTypeValue.Guid.Equals( cellPhoneType ) ); if ( spouseCellPhone != null ) { pnSpouseCell.Text = spouseCellPhone.Number; cbSpouseSms.Checked = spouseCellPhone.IsMessagingEnabled; } } } } } } }
/// <summary> /// Gets the chart data. /// </summary> /// <param name="groupBy">The group by.</param> /// <param name="graphBy">The graph by.</param> /// <param name="startDate">The start date.</param> /// <param name="endDate">The end date.</param> /// <param name="groupIds">The group ids.</param> /// <param name="campusIds">The campus ids. Include the keyword 'null' in the list to include CampusId is null</param> /// <param name="scheduleIds">The schedule ids.</param> /// <param name="dataViewId">The data view identifier.</param> /// <returns></returns> public IEnumerable <IChartData> GetChartData(ChartGroupBy groupBy = ChartGroupBy.Week, AttendanceGraphBy graphBy = AttendanceGraphBy.Total, DateTime?startDate = null, DateTime?endDate = null, string groupIds = null, string campusIds = null, int?dataViewId = null, string scheduleIds = null) { var qryAttendance = Queryable().AsNoTracking() .Where(a => a.DidAttend.HasValue && a.DidAttend.Value && a.PersonAlias != null); if (startDate.HasValue) { qryAttendance = qryAttendance.Where(a => a.StartDateTime >= startDate.Value); } if (endDate.HasValue) { qryAttendance = qryAttendance.Where(a => a.StartDateTime < endDate.Value); } if (dataViewId.HasValue) { var rockContext = (RockContext)this.Context; var dataView = new DataViewService(rockContext).Get(dataViewId.Value); if (dataView != null) { var personService = new PersonService(rockContext); var errorMessages = new List <string>(); ParameterExpression paramExpression = personService.ParameterExpression; Expression whereExpression = dataView.GetExpression(personService, paramExpression, out errorMessages); Rock.Web.UI.Controls.SortProperty sort = null; var dataViewPersonIdQry = personService .Queryable().AsNoTracking() .Where(paramExpression, whereExpression, sort) .Select(p => p.Id); qryAttendance = qryAttendance.Where(a => dataViewPersonIdQry.Contains(a.PersonAlias.PersonId)); } } if (!string.IsNullOrWhiteSpace(groupIds)) { var groupIdList = groupIds.Split(',').AsIntegerList(); qryAttendance = qryAttendance.Where(a => a.GroupId.HasValue && groupIdList.Contains(a.GroupId.Value)); } // If campuses were included, filter attendances by those that have selected campuses // if 'null' is one of the campuses, treat that as a 'CampusId is Null' var includeNullCampus = (campusIds ?? "").Split(',').ToList().Any(a => a.Equals("null", StringComparison.OrdinalIgnoreCase)); var campusIdList = (campusIds ?? "").Split(',').AsIntegerList(); // remove 0 from the list, just in case it is there campusIdList.Remove(0); if (campusIdList.Any()) { if (includeNullCampus) { // show records that have a campusId in the campusIdsList + records that have a null campusId qryAttendance = qryAttendance.Where(a => (a.CampusId.HasValue && campusIdList.Contains(a.CampusId.Value)) || !a.CampusId.HasValue); } else { // only show records that have a campusId in the campusIdList qryAttendance = qryAttendance.Where(a => a.CampusId.HasValue && campusIdList.Contains(a.CampusId.Value)); } } else if (includeNullCampus) { // 'null' was the only campusId in the campusIds parameter, so only show records that have a null CampusId qryAttendance = qryAttendance.Where(a => !a.CampusId.HasValue); } // If schedules were included, filter attendances by those that have selected schedules var scheduleIdList = (scheduleIds ?? "").Split(',').AsIntegerList(); scheduleIdList.Remove(0); if (scheduleIdList.Any()) { qryAttendance = qryAttendance.Where(a => a.ScheduleId.HasValue && scheduleIdList.Contains(a.ScheduleId.Value)); } var qryAttendanceWithSummaryDateTime = qryAttendance.GetAttendanceWithSummaryDateTime(groupBy); var summaryQry = qryAttendanceWithSummaryDateTime.Select(a => new { a.SummaryDateTime, Campus = new { Id = a.Attendance.CampusId, Name = a.Attendance.Campus.Name }, Group = new { Id = a.Attendance.GroupId, Name = a.Attendance.Group.Name }, Schedule = new { Id = a.Attendance.ScheduleId, Name = a.Attendance.Schedule.Name }, Location = new { Id = a.Attendance.LocationId, Name = a.Attendance.Location.Name } }); List <SummaryData> result = null; if (graphBy == AttendanceGraphBy.Total) { var groupByQry = summaryQry.GroupBy(a => new { a.SummaryDateTime }).Select(s => new { s.Key, Count = s.Count() }).OrderBy(o => o.Key); result = groupByQry.ToList().Select(a => new SummaryData { DateTimeStamp = a.Key.SummaryDateTime.ToJavascriptMilliseconds(), DateTime = a.Key.SummaryDateTime, SeriesName = "Total", YValue = a.Count }).ToList(); } else if (graphBy == AttendanceGraphBy.Campus) { var groupByQry = summaryQry.GroupBy(a => new { a.SummaryDateTime, Series = a.Campus }).Select(s => new { s.Key, Count = s.Count() }).OrderBy(o => o.Key); result = groupByQry.ToList().Select(a => new SummaryData { DateTimeStamp = a.Key.SummaryDateTime.ToJavascriptMilliseconds(), DateTime = a.Key.SummaryDateTime, SeriesName = a.Key.Series.Name, YValue = a.Count }).ToList(); } else if (graphBy == AttendanceGraphBy.Group) { var groupByQry = summaryQry.GroupBy(a => new { a.SummaryDateTime, Series = a.Group }).Select(s => new { s.Key, Count = s.Count() }).OrderBy(o => o.Key); result = groupByQry.ToList().Select(a => new SummaryData { DateTimeStamp = a.Key.SummaryDateTime.ToJavascriptMilliseconds(), DateTime = a.Key.SummaryDateTime, SeriesName = a.Key.Series.Name, YValue = a.Count }).ToList(); } else if (graphBy == AttendanceGraphBy.Schedule) { var groupByQry = summaryQry.GroupBy(a => new { a.SummaryDateTime, Series = a.Schedule }).Select(s => new { s.Key, Count = s.Count() }).OrderBy(o => o.Key); result = groupByQry.ToList().Select(a => new SummaryData { DateTimeStamp = a.Key.SummaryDateTime.ToJavascriptMilliseconds(), DateTime = a.Key.SummaryDateTime, SeriesName = a.Key.Series.Name, YValue = a.Count }).ToList(); } else if (graphBy == AttendanceGraphBy.Location) { var groupByQry = summaryQry.GroupBy(a => new { a.SummaryDateTime, Series = a.Location }).Select(s => new { s.Key, Count = s.Count() }).OrderBy(o => o.Key); result = groupByQry.ToList().Select(a => new SummaryData { DateTimeStamp = a.Key.SummaryDateTime.ToJavascriptMilliseconds(), DateTime = a.Key.SummaryDateTime, SeriesName = a.Key.Series.Name, YValue = a.Count }).ToList(); } return(result); }
/// <summary> /// Handles the Click event of the btnConfirm 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 btnConfirm_Click( object sender, EventArgs e ) { if ( Page.IsValid ) { var rockContext = new RockContext(); var personService = new PersonService( rockContext ); var ids = Individuals.Select( i => i.PersonId ).ToList(); #region Individual Details Updates int? newTitleId = ddlTitle.SelectedValueAsInt(); int? newSuffixId = ddlSuffix.SelectedValueAsInt(); int? newConnectionStatusId = ddlStatus.SelectedValueAsInt(); int? newRecordStatusId = ddlRecordStatus.SelectedValueAsInt(); int? newInactiveReasonId = ddlInactiveReason.SelectedValueAsInt(); string newInactiveReasonNote = tbInactiveReasonNote.Text; Gender newGender = ddlGender.SelectedValue.ConvertToEnum<Gender>(); int? newMaritalStatusId = ddlMaritalStatus.SelectedValueAsInt(); int? newGraduationYear = null; if ( ypGraduation.SelectedYear.HasValue ) { newGraduationYear = ypGraduation.SelectedYear.Value; } int? newCampusId = cpCampus.SelectedCampusId; bool? newEmailActive = null; if ( !string.IsNullOrWhiteSpace( ddlIsEmailActive.SelectedValue ) ) { newEmailActive = ddlIsEmailActive.SelectedValue == "Active"; } EmailPreference? newEmailPreference = ddlEmailPreference.SelectedValue.ConvertToEnumOrNull<EmailPreference>(); string newEmailNote = tbEmailNote.Text; int? newReviewReason = ddlReviewReason.SelectedValueAsInt(); string newSystemNote = tbSystemNote.Text; string newReviewReasonNote = tbReviewReasonNote.Text; int inactiveStatusId = DefinedValueCache.Read( Rock.SystemGuid.DefinedValue.PERSON_RECORD_STATUS_INACTIVE ).Id; var allChanges = new Dictionary<int, List<string>>(); var people = personService.Queryable().Where( p => ids.Contains( p.Id ) ).ToList(); foreach ( var person in people ) { var changes = new List<string>(); allChanges.Add( person.Id, changes ); if ( SelectedFields.Contains( ddlTitle.ClientID ) ) { History.EvaluateChange( changes, "Title", DefinedValueCache.GetName( person.TitleValueId ), DefinedValueCache.GetName( newTitleId ) ); person.TitleValueId = newTitleId; } if ( SelectedFields.Contains( ddlSuffix.ClientID ) ) { History.EvaluateChange( changes, "Suffix", DefinedValueCache.GetName( person.SuffixValueId ), DefinedValueCache.GetName( newSuffixId ) ); person.SuffixValueId = newSuffixId; } if ( SelectedFields.Contains( ddlStatus.ClientID ) ) { History.EvaluateChange( changes, "Connection Status", DefinedValueCache.GetName( person.ConnectionStatusValueId ), DefinedValueCache.GetName( newConnectionStatusId ) ); person.ConnectionStatusValueId = newConnectionStatusId; } if ( SelectedFields.Contains( ddlRecordStatus.ClientID ) ) { History.EvaluateChange( changes, "Record Status", DefinedValueCache.GetName( person.RecordStatusValueId ), DefinedValueCache.GetName( newRecordStatusId ) ); person.RecordStatusValueId = newRecordStatusId; if ( newRecordStatusId.HasValue && newRecordStatusId.Value == inactiveStatusId ) { History.EvaluateChange( changes, "Inactive Reason", DefinedValueCache.GetName( person.RecordStatusReasonValueId ), DefinedValueCache.GetName( newInactiveReasonId ) ); person.RecordStatusReasonValueId = newInactiveReasonId; if ( !string.IsNullOrWhiteSpace( newInactiveReasonNote ) ) { History.EvaluateChange( changes, "Inactive Reason Note", person.InactiveReasonNote, newInactiveReasonNote ); person.InactiveReasonNote = newInactiveReasonNote; } } } if ( SelectedFields.Contains( ddlGender.ClientID ) ) { History.EvaluateChange( changes, "Gender", person.Gender, newGender ); person.Gender = newGender; } if ( SelectedFields.Contains( ddlMaritalStatus.ClientID ) ) { History.EvaluateChange( changes, "Marital Status", DefinedValueCache.GetName( person.MaritalStatusValueId ), DefinedValueCache.GetName( newMaritalStatusId ) ); person.MaritalStatusValueId = newMaritalStatusId; } if ( SelectedFields.Contains( ddlGradePicker.ClientID ) ) { History.EvaluateChange( changes, "Graduation Year", person.GraduationYear, newGraduationYear ); person.GraduationYear = newGraduationYear; } if ( SelectedFields.Contains( ddlIsEmailActive.ClientID ) ) { History.EvaluateChange( changes, "Email Is Active", person.IsEmailActive ?? true, newEmailActive.Value ); person.IsEmailActive = newEmailActive; } if ( SelectedFields.Contains( ddlEmailPreference.ClientID ) ) { History.EvaluateChange( changes, "Email Preference", person.EmailPreference, newEmailPreference ); person.EmailPreference = newEmailPreference.Value; } if ( SelectedFields.Contains( tbEmailNote.ClientID ) ) { History.EvaluateChange( changes, "Email Note", person.EmailNote, newEmailNote ); person.EmailNote = newEmailNote; } if ( SelectedFields.Contains( tbSystemNote.ClientID ) ) { History.EvaluateChange( changes, "System Note", person.SystemNote, newSystemNote ); person.SystemNote = newSystemNote; } if ( SelectedFields.Contains( ddlReviewReason.ClientID ) ) { History.EvaluateChange( changes, "Review Reason", DefinedValueCache.GetName( person.ReviewReasonValueId ), DefinedValueCache.GetName( newReviewReason ) ); person.ReviewReasonValueId = newReviewReason; } if ( SelectedFields.Contains( tbReviewReasonNote.ClientID ) ) { History.EvaluateChange( changes, "Review Reason Note", person.ReviewReasonNote, newReviewReasonNote ); person.ReviewReasonNote = newReviewReasonNote; } } if ( SelectedFields.Contains( cpCampus.ClientID ) && cpCampus.SelectedCampusId.HasValue ) { int campusId = cpCampus.SelectedCampusId.Value; Guid familyGuid = new Guid( Rock.SystemGuid.GroupType.GROUPTYPE_FAMILY ); var familyMembers = new GroupMemberService( rockContext ).Queryable() .Where( m => ids.Contains( m.PersonId ) && m.Group.GroupType.Guid == familyGuid ) .Select( m => new { m.PersonId, m.GroupId } ) .Distinct() .ToList(); var families = new GroupMemberService( rockContext ).Queryable() .Where( m => ids.Contains( m.PersonId ) && m.Group.GroupType.Guid == familyGuid ) .Select( m => m.Group ) .Distinct() .ToList(); foreach ( int personId in ids ) { var familyIds = familyMembers.Where( m => m.PersonId == personId ).Select( m => m.GroupId ).ToList(); if ( familyIds.Count == 1 ) { int familyId = familyIds.FirstOrDefault(); var family = families.Where( g => g.Id == familyId ).FirstOrDefault(); { if ( family != null ) { family.CampusId = campusId; } familyMembers.RemoveAll( m => m.GroupId == familyId ); } } } rockContext.SaveChanges(); } // Update following if ( SelectedFields.Contains( ddlFollow.ClientID ) ) { var personAliasEntityType = EntityTypeCache.Read( "Rock.Model.PersonAlias" ); if ( personAliasEntityType != null ) { int personAliasEntityTypeId = personAliasEntityType.Id; bool follow = true; if ( !string.IsNullOrWhiteSpace( ddlFollow.SelectedValue ) ) { follow = ddlFollow.SelectedValue == "Add"; } var personAliasService = new PersonAliasService( rockContext ); var followingService = new FollowingService( rockContext ); if ( follow ) { var paQry = personAliasService.Queryable(); var alreadyFollowingIds = followingService.Queryable() .Where( f => f.EntityTypeId == personAliasEntityTypeId && f.PersonAlias.Id == CurrentPersonAlias.Id ) .Join( paQry, f => f.EntityId, p => p.Id, ( f, p ) => new { PersonAlias = p } ) .Select( p => p.PersonAlias.PersonId ) .Distinct() .ToList(); foreach ( int id in ids.Where( id => !alreadyFollowingIds.Contains( id ) ) ) { var following = new Following { EntityTypeId = personAliasEntityTypeId, EntityId = ( people.FirstOrDefault( p => p.Id == id ).PrimaryAliasId ) ?? 0, PersonAliasId = CurrentPersonAlias.Id }; followingService.Add( following ); } } else { var paQry = personAliasService.Queryable() .Where( p => ids.Contains( p.PersonId ) ) .Select( p => p.Id ); foreach ( var following in followingService.Queryable() .Where( f => f.EntityTypeId == personAliasEntityTypeId && paQry.Contains( f.EntityId ) && f.PersonAlias.Id == CurrentPersonAlias.Id ) ) { followingService.Delete( following ); } } } } rockContext.SaveChanges(); #endregion #region Attributes var selectedCategories = new List<CategoryCache>(); foreach ( string categoryGuid in GetAttributeValue( "AttributeCategories" ).SplitDelimitedValues() ) { var category = CategoryCache.Read( categoryGuid.AsGuid(), rockContext ); if ( category != null ) { selectedCategories.Add( category ); } } var attributes = new List<AttributeCache>(); var attributeValues = new Dictionary<int, string>(); int categoryIndex = 0; foreach ( var category in selectedCategories.OrderBy( c => c.Name ) ) { PanelWidget pw = null; string controlId = "pwAttributes_" + category.Id.ToString(); if ( categoryIndex % 2 == 0 ) { pw = phAttributesCol1.FindControl( controlId ) as PanelWidget; } else { pw = phAttributesCol2.FindControl( controlId ) as PanelWidget; } categoryIndex++; if ( pw != null ) { var orderedAttributeList = new AttributeService( rockContext ).GetByCategoryId( category.Id ) .OrderBy( a => a.Order ).ThenBy( a => a.Name ); foreach ( var attribute in orderedAttributeList ) { if ( attribute.IsAuthorized( Authorization.EDIT, CurrentPerson ) ) { var attributeCache = AttributeCache.Read( attribute.Id ); Control attributeControl = pw.FindControl( string.Format( "attribute_field_{0}", attribute.Id ) ); if ( attributeControl != null && SelectedFields.Contains( attributeControl.ClientID ) ) { string newValue = attributeCache.FieldType.Field.GetEditValue( attributeControl, attributeCache.QualifierValues ); attributes.Add( attributeCache ); attributeValues.Add( attributeCache.Id, newValue ); } } } } } if ( attributes.Any() ) { foreach ( var person in people ) { person.LoadAttributes(); foreach ( var attribute in attributes ) { string originalValue = person.GetAttributeValue( attribute.Key ); string newValue = attributeValues[attribute.Id]; if ( ( originalValue ?? string.Empty ).Trim() != ( newValue ?? string.Empty ).Trim() ) { Rock.Attribute.Helper.SaveAttributeValue( person, attribute, newValue, rockContext ); string formattedOriginalValue = string.Empty; if ( !string.IsNullOrWhiteSpace( originalValue ) ) { formattedOriginalValue = attribute.FieldType.Field.FormatValue( null, originalValue, attribute.QualifierValues, false ); } string formattedNewValue = string.Empty; if ( !string.IsNullOrWhiteSpace( newValue ) ) { formattedNewValue = attribute.FieldType.Field.FormatValue( null, newValue, attribute.QualifierValues, false ); } History.EvaluateChange( allChanges[person.Id], attribute.Name, formattedOriginalValue, formattedNewValue ); } } } } // Create the history records foreach ( var changes in allChanges ) { if ( changes.Value.Any() ) { HistoryService.AddChanges( rockContext, typeof( Person ), Rock.SystemGuid.Category.HISTORY_PERSON_DEMOGRAPHIC_CHANGES.AsGuid(), changes.Key, changes.Value ); } } rockContext.SaveChanges(); #endregion #region Add Note if ( !string.IsNullOrWhiteSpace( tbNote.Text ) && CurrentPerson != null ) { string text = tbNote.Text; bool isAlert = cbIsAlert.Checked; bool isPrivate = cbIsPrivate.Checked; var noteTypeService = new NoteTypeService( rockContext ); var noteType = noteTypeService.Get( Rock.SystemGuid.NoteType.PERSON_TIMELINE.AsGuid() ); if ( noteType != null ) { var notes = new List<Note>(); var noteService = new NoteService( rockContext ); foreach ( int id in ids ) { var note = new Note(); note.IsSystem = false; note.EntityId = id; note.Caption = isPrivate ? "You - Personal Note" : string.Empty; note.Text = tbNote.Text; note.IsAlert = cbIsAlert.Checked; note.NoteType = noteType; notes.Add( note ); noteService.Add( note ); } rockContext.WrapTransaction( () => { rockContext.SaveChanges(); foreach ( var note in notes ) { note.AllowPerson( Authorization.EDIT, CurrentPerson, rockContext ); if ( isPrivate ) { note.MakePrivate( Authorization.VIEW, CurrentPerson, rockContext ); } } } ); } } #endregion #region Group int? groupId = gpGroup.SelectedValue.AsIntegerOrNull(); if ( groupId.HasValue ) { var group = new GroupService( rockContext ).Get( groupId.Value ); if ( group != null ) { var groupMemberService = new GroupMemberService( rockContext ); var existingMembers = groupMemberService.Queryable( "Group" ) .Where( m => m.GroupId == group.Id && ids.Contains( m.PersonId ) ) .ToList(); string action = ddlGroupAction.SelectedValue; if ( action == "Remove" ) { groupMemberService.DeleteRange( existingMembers ); rockContext.SaveChanges(); } else { var roleId = ddlGroupRole.SelectedValueAsInt(); var status = ddlGroupMemberStatus.SelectedValueAsEnum<GroupMemberStatus>(); // Get the attribute values updated var gm = new GroupMember(); gm.Group = group; gm.GroupId = group.Id; gm.LoadAttributes( rockContext ); var selectedGroupAttributes = new List<AttributeCache>(); var selectedGroupAttributeValues = new Dictionary<string, string>(); foreach ( var attributeCache in gm.Attributes.Select( a => a.Value ) ) { Control attributeControl = phAttributes.FindControl( string.Format( "attribute_field_{0}", attributeCache.Id ) ); if ( attributeControl != null && ( action == "Add" || SelectedFields.Contains( attributeControl.ClientID ) ) ) { string newValue = attributeCache.FieldType.Field.GetEditValue( attributeControl, attributeCache.QualifierValues ); selectedGroupAttributes.Add( attributeCache ); selectedGroupAttributeValues.Add( attributeCache.Key, newValue ); } } if ( action == "Add" ) { if ( roleId.HasValue ) { var newGroupMembers = new List<GroupMember>(); var existingIds = existingMembers.Select( m => m.PersonId ).Distinct().ToList(); foreach ( int id in ids.Where( id => !existingIds.Contains( id ) ) ) { var groupMember = new GroupMember(); groupMember.GroupId = group.Id; groupMember.GroupRoleId = roleId.Value; groupMember.GroupMemberStatus = status; groupMember.PersonId = id; groupMemberService.Add( groupMember ); newGroupMembers.Add( groupMember ); } rockContext.SaveChanges(); if ( selectedGroupAttributes.Any() ) { foreach ( var groupMember in newGroupMembers ) { foreach ( var attribute in selectedGroupAttributes ) { Rock.Attribute.Helper.SaveAttributeValue( groupMember, attribute, selectedGroupAttributeValues[attribute.Key], rockContext ); } } } } } else // Update { if ( SelectedFields.Contains( ddlGroupRole.ClientID ) && roleId.HasValue ) { foreach ( var member in existingMembers.Where( m => m.GroupRoleId != roleId.Value ) ) { if ( !existingMembers.Where( m => m.PersonId == member.PersonId && m.GroupRoleId == roleId.Value ).Any() ) { member.GroupRoleId = roleId.Value; } } } if ( SelectedFields.Contains( ddlGroupMemberStatus.ClientID ) ) { foreach ( var member in existingMembers ) { member.GroupMemberStatus = status; } } rockContext.SaveChanges(); if ( selectedGroupAttributes.Any() ) { foreach ( var groupMember in existingMembers ) { foreach ( var attribute in selectedGroupAttributes ) { Rock.Attribute.Helper.SaveAttributeValue( groupMember, attribute, selectedGroupAttributeValues[attribute.Key], rockContext ); } } } } } } } #endregion pnlEntry.Visible = false; pnlConfirm.Visible = false; nbResult.Text = string.Format( "{0} {1} succesfully updated.", ids.Count().ToString( "N0" ), ( ids.Count() > 1 ? "people were" : "person was" ) ); ; pnlResult.Visible = true; } }
private void ShowDetail(Guid personGuid) { using ( var rockContext = new RockContext() ) { var personService = new PersonService( rockContext ); var person = personService.Queryable( "PhoneNumbers.NumberTypeValue,RecordTypeValue", true, true ) .FirstOrDefault( a => a.Guid == personGuid ); if ( person != null ) { lName.Text = person.FullName; string photoTag = Rock.Model.Person.GetPersonPhotoImageTag( person, 120, 120 ); if ( person.PhotoId.HasValue ) { lPhoto.Text = string.Format( "<div class='photoframe'><a href='{0}'>{1}</a></div>", person.PhotoUrl, photoTag ); } else { lPhoto.Text = photoTag; } lGender.Text = person.Gender != Gender.Unknown ? person.Gender.ConvertToString() : ""; if ( person.BirthDate.HasValue ) { string ageText = ( person.BirthYear.HasValue && person.BirthYear != DateTime.MinValue.Year ) ? string.Format( "{0} yrs old ", person.BirthDate.Value.Age() ) : string.Empty; lAge.Text = string.Format( "{0} <small>({1})</small><br/>", ageText, person.BirthDate.Value.ToShortDateString() ); } else { lAge.Text = string.Empty; } lGrade.Text = person.GradeFormatted; lEmail.Visible = !string.IsNullOrWhiteSpace( person.Email ); lEmail.Text = person.GetEmailTag( ResolveRockUrl( "/" ), "btn btn-default", "<i class='fa fa-envelope'></i>" ); // Get all family member from all families ( including self ) var allFamilyMembers = personService.GetFamilyMembers( person.Id, true ).ToList(); // Add flag for this person in each family indicating if they are a child in family. var childGuid = Rock.SystemGuid.GroupRole.GROUPROLE_FAMILY_MEMBER_CHILD.AsGuid(); var isFamilyChild = new Dictionary<int, bool>(); foreach ( var thisPerson in allFamilyMembers.Where( m => m.PersonId == person.Id ) ) { isFamilyChild.Add( thisPerson.GroupId, thisPerson.GroupRole.Guid.Equals( childGuid ) ); } // Get the current url's root (without the person's guid) string urlRoot = Request.Url.ToString().ReplaceCaseInsensitive( personGuid.ToString(), "" ); // Get the other family members and the info needed for rendering var familyMembers = allFamilyMembers.Where( m => m.PersonId != person.Id ) .OrderBy( m => m.GroupId ) .ThenBy( m => m.Person.BirthDate ) .Select( m => new { Url = urlRoot + m.Person.Guid.ToString(), FullName = m.Person.FullName, Gender = m.Person.Gender, FamilyRole = m.GroupRole, Note = isFamilyChild[m.GroupId] ? ( m.GroupRole.Guid.Equals( childGuid ) ? " (Sibling)" : "(Parent)" ) : ( m.GroupRole.Guid.Equals( childGuid ) ? " (Child)" : "" ) } ) .ToList(); rcwFamily.Visible = familyMembers.Any(); rptrFamily.DataSource = familyMembers; rptrFamily.DataBind(); rptrPhones.DataSource = person.PhoneNumbers.Where( p => !p.IsUnlisted ).ToList(); rptrPhones.DataBind(); var schedules = new ScheduleService( rockContext ) .Queryable().AsNoTracking() .Where( s => s.CheckInStartOffsetMinutes.HasValue ) .ToList(); var scheduleIds = schedules.Select( s => s.Id ).ToList(); var activeScheduleIds = new List<int>(); foreach ( var schedule in schedules ) { if ( schedule.IsScheduleOrCheckInActive ) { activeScheduleIds.Add( schedule.Id ); } } int? personAliasId = person.PrimaryAliasId; if ( !personAliasId.HasValue ) { personAliasId = new PersonAliasService( rockContext ).GetPrimaryAliasId( person.Id ); } var attendances = new AttendanceService( rockContext ) .Queryable( "Schedule,Group,Location,AttendanceCode" ) .Where( a => a.PersonAliasId.HasValue && a.PersonAliasId == personAliasId && a.ScheduleId.HasValue && a.GroupId.HasValue && a.LocationId.HasValue && a.DidAttend.HasValue && a.DidAttend.Value && scheduleIds.Contains( a.ScheduleId.Value ) ) .OrderByDescending( a => a.StartDateTime ) .Take( 20 ) .ToList() // Run query to get recent most 20 checkins .OrderByDescending( a => a.StartDateTime ) // Then sort again by startdatetime and schedule start (which is not avail to sql query ) .ThenByDescending( a => a.Schedule.StartTimeOfDay ) .Select( a => new AttendanceInfo { Date = a.StartDateTime, GroupId = a.Group.Id, Group = a.Group.Name, LocationId = a.LocationId.Value, Location = a.Location.Name, Schedule = a.Schedule.Name, IsActive = a.StartDateTime > DateTime.Today && activeScheduleIds.Contains( a.ScheduleId.Value ), Code = a.AttendanceCode != null ? a.AttendanceCode.Code : "" } ).ToList(); // Set active locations to be a link to the room in manager page var qryParam = new Dictionary<string, string>(); qryParam.Add( "Group", "" ); qryParam.Add( "Location", "" ); foreach ( var attendance in attendances.Where( a => a.IsActive ) ) { qryParam["Group"] = attendance.GroupId.ToString(); qryParam["Location"] = attendance.LocationId.ToString(); attendance.Location = string.Format( "<a href='{0}'>{1}</a>", LinkedPageUrl( "ManagerPage", qryParam ), attendance.Location ); } rcwCheckinHistory.Visible = attendances.Any(); gHistory.DataSource = attendances; gHistory.DataBind(); } } }
private void BindGrid() { string type = PageParameter( "SearchType" ); string term = PageParameter( "SearchTerm" ); if ( !String.IsNullOrWhiteSpace( type ) && !String.IsNullOrWhiteSpace( term ) ) { var rockContext = new RockContext(); var personService = new PersonService( rockContext ); IQueryable<Person> people = null; switch ( type.ToLower() ) { case ( "name" ): { bool allowFirstNameOnly = false; if ( !bool.TryParse( PageParameter( "allowFirstNameOnly" ), out allowFirstNameOnly ) ) { allowFirstNameOnly = false; } people = personService.GetByFullName( term, allowFirstNameOnly, true ); break; } case ( "phone" ): { var phoneService = new PhoneNumberService( rockContext ); var personIds = phoneService.GetPersonIdsByNumber( term ); people = personService.Queryable().Where( p => personIds.Contains( p.Id ) ); break; } case ( "address" ): { var groupMemberService = new GroupMemberService( rockContext ); var personIds2 = groupMemberService.GetPersonIdsByHomeAddress( term ); people = personService.Queryable().Where( p => personIds2.Contains( p.Id ) ); break; } case ( "email" ): { people = personService.Queryable().Where( p => p.Email.Contains( term ) ); break; } } SortProperty sortProperty = gPeople.SortProperty; if ( sortProperty != null ) { people = people.Sort( sortProperty ); } else { people = people.OrderBy( p => p.LastName ).ThenBy( p => p.FirstName ); } var personList = people.ToList(); if ( personList.Count == 1 ) { Response.Redirect( string.Format( "~/Person/{0}", personList[0].Id ), false ); Context.ApplicationInstance.CompleteRequest(); } else { if ( type.ToLower() == "name" ) { var similiarNames = personService.GetSimiliarNames( term, personList.Select( p => p.Id ).ToList(), true ); if ( similiarNames.Any() ) { var hyperlinks = new List<string>(); foreach ( string name in similiarNames.Distinct() ) { var pageRef = CurrentPageReference; pageRef.Parameters["SearchTerm"] = name; hyperlinks.Add( string.Format( "<a href='{0}'>{1}</a>", pageRef.BuildUrl(), name ) ); } string altNames = string.Join( ", ", hyperlinks ); nbNotice.Text = string.Format( "Other Possible Matches: {0}", altNames ); nbNotice.Visible = true; } } gPeople.DataSource = personList; gPeople.DataBind(); } } }
/// <summary> /// Shows the details. /// </summary> private void ShowDetails() { _rockContext = _rockContext ?? new RockContext(); if ( _group != null ) { // Show lava content var mergeFields = new Dictionary<string, object>(); mergeFields.Add( "Group", _group ); bool showDebug = UserCanEdit && GetAttributeValue( "EnableDebug" ).AsBoolean(); lLavaOutputDebug.Visible = showDebug; if ( showDebug ) { lLavaOutputDebug.Text = mergeFields.lavaDebugInfo( _rockContext ); } string template = GetAttributeValue( "LavaTemplate" ); lLavaOverview.Text = template.ResolveMergeFields( mergeFields ); // Set visibility based on selected mode if ( IsFullWithSpouse ) { pnlCol1.RemoveCssClass( "col-md-12" ).AddCssClass( "col-md-6" ); } else { pnlCol1.RemoveCssClass( "col-md-6" ).AddCssClass( "col-md-12" ); } pnlCol2.Visible = IsFullWithSpouse; pnlHomePhone.Visible = !IsSimple; pnlCellPhone.Visible = !IsSimple; acAddress.Visible = !IsSimple; if ( CurrentPersonId.HasValue && _autoFill ) { var personService = new PersonService( _rockContext ); Person person = personService .Queryable( "PhoneNumbers.NumberTypeValue" ).AsNoTracking() .FirstOrDefault( p => p.Id == CurrentPersonId.Value ); tbFirstName.Text = CurrentPerson.FirstName; tbLastName.Text = CurrentPerson.LastName; tbEmail.Text = CurrentPerson.Email; if ( !IsSimple ) { Guid homePhoneType = Rock.SystemGuid.DefinedValue.PERSON_PHONE_TYPE_HOME.AsGuid(); var homePhone = person.PhoneNumbers .FirstOrDefault( n => n.NumberTypeValue.Guid.Equals( homePhoneType ) ); if ( homePhone != null ) { pnHome.Text = homePhone.Number; } Guid cellPhoneType = Rock.SystemGuid.DefinedValue.PERSON_PHONE_TYPE_MOBILE.AsGuid(); var cellPhone = person.PhoneNumbers .FirstOrDefault( n => n.NumberTypeValue.Guid.Equals( cellPhoneType ) ); if ( cellPhone != null ) { pnCell.Text = cellPhone.Number; cbSms.Checked = cellPhone.IsMessagingEnabled; } var homeAddress = person.GetHomeLocation(); if ( homeAddress != null ) { acAddress.SetValues( homeAddress ); } if ( IsFullWithSpouse ) { var spouse = person.GetSpouse( _rockContext ); if ( spouse != null ) { tbSpouseFirstName.Text = spouse.FirstName; tbSpouseLastName.Text = spouse.LastName; tbSpouseEmail.Text = spouse.Email; var spouseCellPhone = spouse.PhoneNumbers .FirstOrDefault( n => n.NumberTypeValue.Guid.Equals( cellPhoneType ) ); if ( spouseCellPhone != null ) { pnSpouseCell.Text = spouseCellPhone.Number; cbSpouseSms.Checked = spouseCellPhone.IsMessagingEnabled; } } } } } if ( GetAttributeValue( "PreventOvercapacityRegistrations" ).AsBoolean() ) { int openGroupSpots = 2; int openRoleSpots = 2; // If the group has a GroupCapacity, check how far we are from hitting that. if ( _group.GroupCapacity.HasValue ) { openGroupSpots = _group.GroupCapacity.Value - _group.Members.Count(); } // When someone registers for a group on the front-end website, they automatically get added with the group's default // GroupTypeRole. If that role exists and has a MaxCount, check how far we are from hitting that. if ( _defaultGroupRole != null && _defaultGroupRole.MaxCount.HasValue ) { openRoleSpots = _defaultGroupRole.MaxCount.Value - _group.Members.Where( m => m.GroupRoleId == _defaultGroupRole.Id ).Count(); } // Between the group's GroupCapacity and DefaultGroupRole.MaxCount, grab the one we're closest to hitting, and how close we are to // hitting it. int openSpots = Math.Min( openGroupSpots, openRoleSpots ); // If there's only one spot open, disable the spouse fields and display a warning message. if ( openSpots == 1 ) { tbSpouseFirstName.Enabled = false; tbSpouseLastName.Enabled = false; pnSpouseCell.Enabled = false; cbSpouseSms.Enabled = false; tbSpouseEmail.Enabled = false; nbWarning.Text = "This group is near its capacity. Only one individual can register."; nbWarning.Visible = true; } // If no spots are open, display a message that says so. if ( openSpots <= 0 ) { nbNotice.Text = "This group is at or exceeds capacity."; nbNotice.Visible = true; pnlView.Visible = false; } } } }
/// <summary> /// Binds the attendees grid. /// </summary> private void BindAttendeesGrid() { var dateRange = SlidingDateRangePicker.CalculateDateRangeFromDelimitedValues( drpSlidingDateRange.DelimitedValues ); if ( dateRange.End == null || dateRange.End > RockDateTime.Now ) { dateRange.End = RockDateTime.Now; } var rockContext = new RockContext(); // make a qryPersonAlias so that the generated SQL will be a "WHERE .. IN ()" instead of an OUTER JOIN (which is incredibly slow for this) var qryPersonAlias = new PersonAliasService( rockContext ).Queryable(); var qryAttendance = new AttendanceService( rockContext ).Queryable(); qryAttendance = qryAttendance.Where( a => a.DidAttend.HasValue && a.DidAttend.Value ); var groupType = this.GetSelectedTemplateGroupType(); var qryAllVisits = qryAttendance; if ( groupType != null ) { var childGroupTypeIds = new GroupTypeService( rockContext ).GetChildGroupTypes( groupType.Id ).Select( a => a.Id ); qryAllVisits = qryAttendance.Where( a => childGroupTypeIds.Any( b => b == a.Group.GroupTypeId ) ); } else { return; } var groupIdList = new List<int>(); string groupIds = GetSelectedGroupIds().AsDelimited( "," ); if ( !string.IsNullOrWhiteSpace( groupIds ) ) { groupIdList = groupIds.Split( ',' ).AsIntegerList(); qryAttendance = qryAttendance.Where( a => a.GroupId.HasValue && groupIdList.Contains( a.GroupId.Value ) ); } //// If campuses were included, filter attendances by those that have selected campuses //// if 'null' is one of the campuses, treat that as a 'CampusId is Null' var includeNullCampus = clbCampuses.SelectedValues.Any( a => a.Equals( "null", StringComparison.OrdinalIgnoreCase ) ); var campusIdList = clbCampuses.SelectedValues.AsIntegerList(); // remove 0 from the list, just in case it is there campusIdList.Remove( 0 ); if ( campusIdList.Any() ) { if ( includeNullCampus ) { // show records that have a campusId in the campusIdsList + records that have a null campusId qryAttendance = qryAttendance.Where( a => ( a.CampusId.HasValue && campusIdList.Contains( a.CampusId.Value ) ) || !a.CampusId.HasValue ); } else { // only show records that have a campusId in the campusIdList qryAttendance = qryAttendance.Where( a => a.CampusId.HasValue && campusIdList.Contains( a.CampusId.Value ) ); } } else if ( includeNullCampus ) { // 'null' was the only campusId in the campusIds parameter, so only show records that have a null CampusId qryAttendance = qryAttendance.Where( a => !a.CampusId.HasValue ); } // have the "Missed" query be the same as the qry before the Main date range is applied since it'll have a different date range var qryMissed = qryAttendance; if ( dateRange.Start.HasValue ) { qryAttendance = qryAttendance.Where( a => a.StartDateTime >= dateRange.Start.Value ); } if ( dateRange.End.HasValue ) { qryAttendance = qryAttendance.Where( a => a.StartDateTime < dateRange.End.Value ); } // we want to get the first 2 visits at a minimum so we can show the date in the grid int nthVisitsTake = 2; int? byNthVisit = null; if ( radByVisit.Checked ) { // If we are filtering by nth visit, we might want to get up to first 5 byNthVisit = ddlNthVisit.SelectedValue.AsIntegerOrNull(); if ( byNthVisit.HasValue && byNthVisit > 2 ) { nthVisitsTake = byNthVisit.Value; } } ChartGroupBy groupBy = hfGroupBy.Value.ConvertToEnumOrNull<ChartGroupBy>() ?? ChartGroupBy.Week; IQueryable<PersonWithSummary> qryByPersonWithSummary = null; if ( byNthVisit.HasValue && byNthVisit.Value == 0 ) { // Show members of the selected groups that did not attend at all during selected date range // Get all the person ids that did attend var attendeePersonIds = qryAttendance.Select( a => a.PersonAlias.PersonId ); // Get all the active members of the selected groups who have no attendance within selected date range and campus qryByPersonWithSummary = new GroupMemberService( rockContext ) .Queryable().AsNoTracking() .Where( m => groupIdList.Contains( m.GroupId ) && !attendeePersonIds.Contains( m.PersonId ) && m.GroupMemberStatus == GroupMemberStatus.Active ) .Select( m => new PersonWithSummary { PersonId = m.PersonId, FirstVisits = new DateTime[] { }.AsQueryable(), LastVisit = new AttendancePersonAlias(), AttendanceSummary = new DateTime[] { }.AsQueryable() } ); } else { var qryAttendanceWithSummaryDateTime = qryAttendance.GetAttendanceWithSummaryDateTime( groupBy ); var qryGroup = new GroupService( rockContext ).Queryable(); var qryJoinPerson = qryAttendance.Join( qryPersonAlias, k1 => k1.PersonAliasId, k2 => k2.Id, ( a, pa ) => new { CampusId = a.CampusId, GroupId = a.GroupId, ScheduleId = a.ScheduleId, StartDateTime = a.StartDateTime, PersonAliasId = pa.Id, PersonAliasPersonId = pa.PersonId } ); var qryJoinFinal = qryJoinPerson.Join( qryGroup, k1 => k1.GroupId, k2 => k2.Id, ( a, g ) => new AttendancePersonAlias { CampusId = a.CampusId, GroupId = a.GroupId, GroupName = g.Name, ScheduleId = a.ScheduleId, StartDateTime = a.StartDateTime, PersonAliasId = a.PersonAliasId, PersonAliasPersonId = a.PersonAliasPersonId } ); var qryByPerson = qryJoinFinal.GroupBy( a => a.PersonAliasPersonId ).Select( a => new { PersonId = a.Key, Attendances = a } ); int? attendedMinCount = null; int? attendedMissedCount = null; DateRange attendedMissedDateRange = new DateRange(); if ( radByPattern.Checked ) { attendedMinCount = tbPatternXTimes.Text.AsIntegerOrNull(); if ( cbPatternAndMissed.Checked ) { attendedMissedCount = tbPatternMissedXTimes.Text.AsIntegerOrNull(); attendedMissedDateRange = new DateRange( drpPatternDateRange.LowerValue, drpPatternDateRange.UpperValue ); if ( !attendedMissedDateRange.Start.HasValue || !attendedMissedDateRange.End.HasValue ) { nbMissedDateRangeRequired.Visible = true; return; } } } nbMissedDateRangeRequired.Visible = false; // get either the first 2 visits or the first 5 visits (using a const take of 2 or 5 vs a variable to help the SQL optimizer) qryByPersonWithSummary = qryByPerson.Select( a => new PersonWithSummary { PersonId = a.PersonId, FirstVisits = qryAllVisits.Where( b => qryPersonAlias.Where( pa => pa.PersonId == a.PersonId ).Any( pa => pa.Id == b.PersonAliasId ) ).Select( s => s.StartDateTime ).OrderBy( x => x ).Take( 2 ), LastVisit = a.Attendances.OrderByDescending( x => x.StartDateTime ).FirstOrDefault(), AttendanceSummary = qryAttendanceWithSummaryDateTime.Where( x => qryPersonAlias.Where( pa => pa.PersonId == a.PersonId ).Any( pa => pa.Id == x.Attendance.PersonAliasId ) ).GroupBy( g => g.SummaryDateTime ).Select( s => s.Key ) } ); if ( nthVisitsTake > 2 ) { qryByPersonWithSummary = qryByPerson.Select( a => new PersonWithSummary { PersonId = a.PersonId, FirstVisits = qryAllVisits.Where( b => qryPersonAlias.Where( pa => pa.PersonId == a.PersonId ).Any( pa => pa.Id == b.PersonAliasId ) ).Select( s => s.StartDateTime ).OrderBy( x => x ).Take( 5 ), LastVisit = a.Attendances.OrderByDescending( x => x.StartDateTime ).FirstOrDefault(), AttendanceSummary = qryAttendanceWithSummaryDateTime.Where( x => qryPersonAlias.Where( pa => pa.PersonId == a.PersonId ).Any( pa => pa.Id == x.Attendance.PersonAliasId ) ).GroupBy( g => g.SummaryDateTime ).Select( s => s.Key ) } ); } if ( byNthVisit.HasValue ) { // only return attendees where their nth visit is within the selected daterange int skipCount = byNthVisit.Value - 1; qryByPersonWithSummary = qryByPersonWithSummary.Where( a => a.FirstVisits.OrderBy( x => x ).Skip( skipCount ).Take( 1 ).Any( d => d >= dateRange.Start && d < dateRange.End ) ); } if ( attendedMinCount.HasValue ) { qryByPersonWithSummary = qryByPersonWithSummary.Where( a => a.AttendanceSummary.Count() >= attendedMinCount ); } if ( attendedMissedCount.HasValue ) { if ( attendedMissedDateRange.Start.HasValue && attendedMissedDateRange.End.HasValue ) { var attendedMissedPossible = GetPossibleAttendancesForDateRange( attendedMissedDateRange, groupBy ); int attendedMissedPossibleCount = attendedMissedPossible.Count(); qryMissed = qryMissed.Where( a => a.StartDateTime >= attendedMissedDateRange.Start.Value && a.StartDateTime < attendedMissedDateRange.End.Value ); var qryMissedAttendanceByPersonAndSummary = qryMissed.GetAttendanceWithSummaryDateTime( groupBy ) .GroupBy( g1 => new { g1.SummaryDateTime, g1.Attendance.PersonAlias.PersonId } ) .GroupBy( a => a.Key.PersonId ) .Select( a => new { PersonId = a.Key, AttendanceCount = a.Count() } ); var qryMissedByPerson = qryMissedAttendanceByPersonAndSummary .Where( x => ( attendedMissedPossibleCount - x.AttendanceCount ) >= attendedMissedCount ); // filter to only people that missed at least X weeks/months/years between specified missed date range qryByPersonWithSummary = qryByPersonWithSummary.Where( a => qryMissedByPerson.Any( b => b.PersonId == a.PersonId ) ); } } } var personService = new PersonService( rockContext ); // Filter by dataview var dataViewId = dvpDataView.SelectedValueAsInt(); if ( dataViewId.HasValue ) { var dataView = new DataViewService( _rockContext ).Get( dataViewId.Value ); if ( dataView != null ) { var errorMessages = new List<string>(); ParameterExpression paramExpression = personService.ParameterExpression; Expression whereExpression = dataView.GetExpression( personService, paramExpression, out errorMessages ); SortProperty sort = null; var dataViewPersonIdQry = personService .Queryable().AsNoTracking() .Where( paramExpression, whereExpression, sort ) .Select( p => p.Id ); qryByPersonWithSummary = qryByPersonWithSummary.Where( a => dataViewPersonIdQry.Contains( a.PersonId ) ); } } // declare the qryResult that we'll use in case they didn't choose IncludeParents or IncludeChildren (and the Anonymous Type will also work if we do include parents or children) var qryPerson = personService.Queryable(); var qryResult = qryByPersonWithSummary.Join( qryPerson, a => a.PersonId, p => p.Id, ( a, p ) => new { a.PersonId, ParentId = (int?)null, ChildId = (int?)null, Person = p, Parent = (Person)null, Child = (Person)null, a.FirstVisits, a.LastVisit, p.PhoneNumbers, a.AttendanceSummary } ); var includeParents = hfViewBy.Value.ConvertToEnumOrNull<ViewBy>().GetValueOrDefault( ViewBy.Attendees ) == ViewBy.ParentsOfAttendees; var includeChildren = hfViewBy.Value.ConvertToEnumOrNull<ViewBy>().GetValueOrDefault( ViewBy.Attendees ) == ViewBy.ChildrenOfAttendees; // if Including Parents, join with qryChildWithParent instead of qryPerson if ( includeParents ) { var qryChildWithParent = new PersonService( rockContext ).GetChildWithParent(); qryResult = qryByPersonWithSummary.Join( qryChildWithParent, a => a.PersonId, p => p.Child.Id, ( a, p ) => new { a.PersonId, ParentId = (int?)p.Parent.Id, ChildId = (int?)null, Person = p.Child, Parent = p.Parent, Child = (Person)null, a.FirstVisits, a.LastVisit, p.Parent.PhoneNumbers, a.AttendanceSummary } ); } if ( includeChildren ) { var qryParentWithChildren = new PersonService( rockContext ).GetParentWithChild(); qryResult = qryByPersonWithSummary.Join( qryParentWithChildren, a => a.PersonId, p => p.Parent.Id, ( a, p ) => new { a.PersonId, ParentId = (int?)null, ChildId = (int?)p.Child.Id, Person = p.Parent, Parent = (Person)null, Child = p.Child, a.FirstVisits, a.LastVisit, p.Child.PhoneNumbers, a.AttendanceSummary } ); } var parentField = gAttendeesAttendance.Columns.OfType<PersonField>().FirstOrDefault( a => a.HeaderText == "Parent" ); if ( parentField != null ) { parentField.Visible = includeParents; } var parentEmailField = gAttendeesAttendance.Columns.OfType<RockBoundField>().FirstOrDefault( a => a.HeaderText == "Parent Email" ); if ( parentEmailField != null ) { parentEmailField.ExcelExportBehavior = includeParents ? ExcelExportBehavior.AlwaysInclude : ExcelExportBehavior.NeverInclude; } var childField = gAttendeesAttendance.Columns.OfType<PersonField>().FirstOrDefault( a => a.HeaderText == "Child" ); if ( childField != null ) { childField.Visible = includeChildren; } var childEmailField = gAttendeesAttendance.Columns.OfType<RockBoundField>().FirstOrDefault( a => a.HeaderText == "Child Email" ); if ( childEmailField != null ) { childEmailField.ExcelExportBehavior = includeChildren ? ExcelExportBehavior.AlwaysInclude : ExcelExportBehavior.NeverInclude; } SortProperty sortProperty = gAttendeesAttendance.SortProperty; if ( sortProperty != null ) { if ( sortProperty.Property == "AttendanceSummary.Count" ) { if ( sortProperty.Direction == SortDirection.Descending ) { qryResult = qryResult.OrderByDescending( a => a.AttendanceSummary.Count() ); } else { qryResult = qryResult.OrderBy( a => a.AttendanceSummary.Count() ); } } else if ( sortProperty.Property == "FirstVisit.StartDateTime" ) { if ( sortProperty.Direction == SortDirection.Descending ) { qryResult = qryResult.OrderByDescending( a => a.FirstVisits.Min() ); } else { qryResult = qryResult.OrderBy( a => a.FirstVisits.Min() ); } } else { qryResult = qryResult.Sort( sortProperty ); } } else { qryResult = qryResult.OrderBy( a => a.Person.LastName ).ThenBy( a => a.Person.NickName ); } var attendancePercentField = gAttendeesAttendance.Columns.OfType<RockTemplateField>().First( a => a.HeaderText.EndsWith( "Attendance %" ) ); attendancePercentField.HeaderText = string.Format( "{0}ly Attendance %", groupBy.ConvertToString() ); // Calculate all the possible attendance summary dates UpdatePossibleAttendances( dateRange, groupBy ); // pre-load the schedule names since FriendlyScheduleText requires building the ICal object, etc _scheduleNameLookup = new ScheduleService( rockContext ).Queryable() .ToList() .ToDictionary( k => k.Id, v => v.FriendlyScheduleText ); if ( includeParents ) { gAttendeesAttendance.PersonIdField = "ParentId"; gAttendeesAttendance.DataKeyNames = new string[] { "ParentId", "PersonId" }; } else if ( includeChildren ) { gAttendeesAttendance.PersonIdField = "ChildId"; gAttendeesAttendance.DataKeyNames = new string[] { "ChildId", "PersonId" }; } else { gAttendeesAttendance.PersonIdField = "PersonId"; gAttendeesAttendance.DataKeyNames = new string[] { "PersonId" }; } // Create the dynamic attendance grid columns as needed CreateDynamicAttendanceGridColumns(); try { nbAttendeesError.Visible = false; // increase the timeout from 30 to 90. The Query can be slow if SQL hasn't calculated the Query Plan for the query yet. // Sometimes, most of the time consumption is figuring out the Query Plan, but after it figures it out, it caches it so that the next time it'll be much faster rockContext.Database.CommandTimeout = 90; gAttendeesAttendance.SetLinqDataSource( qryResult.AsNoTracking() ); gAttendeesAttendance.DataBind(); } catch ( Exception exception ) { LogAndShowException( exception ); } }
/// <summary> /// Executes the specified context. /// </summary> /// <param name="context">The context.</param> public void Execute( IJobExecutionContext context ) { var rockContext = new RockContext(); var personService = new PersonService( rockContext ); JobDataMap dataMap = context.JobDetail.JobDataMap; Guid? systemEmailGuid = dataMap.GetString( "BirthdayEmail" ).AsGuidOrNull(); SystemEmailService emailService = new SystemEmailService( rockContext ); SystemEmail systemEmail = null; if ( systemEmailGuid.HasValue ) { systemEmail = emailService.Get( systemEmailGuid.Value ); } if ( systemEmail == null ) { // no email specified, so nothing to do return; } var activeStatusGuid = Rock.SystemGuid.DefinedValue.PERSON_RECORD_STATUS_ACTIVE.AsGuid(); // only include alive people that have record status of Active var personQry = personService.Queryable( false, false ).Where( a => a.RecordStatusValue.Guid == activeStatusGuid && a.IsDeceased == false ); var ageRange = ( dataMap.GetString( "AgeRange" ) ?? string.Empty ).Split( ',' ); if ( ageRange.Length == 2 ) { int? minimumAge = ageRange[0].AsIntegerOrNull(); int? maximumAge = ageRange[1].AsIntegerOrNull(); personQry = personQry.WhereAgeRange( minimumAge, maximumAge, true ); } // only include people whose birthday is today (which can be determined from the computed DaysUntilBirthday column) personQry = personQry.Where( a => a.DaysUntilBirthday.HasValue && a.DaysUntilBirthday == 0 ); var connectionStatusGuids = ( dataMap.GetString( "ConnectionStatuses" ) ?? string.Empty ).Split( ',' ).AsGuidList(); if ( connectionStatusGuids.Any() ) { personQry = personQry.Where( a => connectionStatusGuids.Contains( a.ConnectionStatusValue.Guid ) ); } // only include people that have an email address and want an email personQry = personQry.Where( a => ( a.Email != null ) && ( a.Email != "" ) && ( a.EmailPreference == EmailPreference.EmailAllowed ) ); var recipients = new List<RecipientData>(); var personList = personQry.AsNoTracking().ToList(); foreach ( var person in personList ) { var mergeFields = Rock.Lava.LavaHelper.GetCommonMergeFields( null ); mergeFields.Add( "Person", person ); recipients.Add( new RecipientData( person.Email, mergeFields ) ); } var appRoot = Rock.Web.Cache.GlobalAttributesCache.Read( rockContext ).GetValue( "ExternalApplicationRoot" ); Email.Send( systemEmail.Guid, recipients, appRoot ); context.Result = string.Format( "{0} birthday emails sent", recipients.Count() ); }
/// <summary> /// Displays the duplicates. /// </summary> /// <param name="direction">The direction.</param> private void DisplayDuplicates( Direction direction ) { bool displayed = false; if ( Convert.ToBoolean( GetAttributeValue( "Duplicates" ) ) ) { PersonService personService = new PersonService( new RockContext() ); var matches = personService.Queryable().Where( p => p.Email.ToLower() == tbEmail.Text.ToLower() && p.LastName.ToLower() == tbLastName.Text.ToLower() ).ToList(); if ( matches.Count > 0 ) { gDuplicates.AllowPaging = false; gDuplicates.ShowActionRow = false; gDuplicates.DataSource = matches; gDuplicates.DataBind(); ShowPanel( 1 ); displayed = true; } else { displayed = false; } } if ( !displayed ) { if ( direction == Direction.Forward ) { DisplaySuccess( CreateUser( CreatePerson(), true ) ); } else { DisplayUserInfo( direction ); } } }
/// <summary> /// Handles the Click event of the lbMerge 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 lbMerge_Click( object sender, EventArgs e ) { if ( MergeData.People.Count < 2 ) { nbPeople.Visible = true; return; } bool reconfirmRequired = ( MergeData.People.Select( p => p.Email ).Distinct().Count() > 1 && MergeData.People.Where( p => p.HasLogins ).Any() ); GetValuesSelection(); int? primaryPersonId = null; var oldPhotos = new List<int>(); var rockContext = new RockContext(); rockContext.WrapTransaction( () => { var personService = new PersonService( rockContext ); var userLoginService = new UserLoginService( rockContext ); var groupService = new GroupService( rockContext ); var groupMemberService = new GroupMemberService( rockContext ); var binaryFileService = new BinaryFileService( rockContext ); var phoneNumberService = new PhoneNumberService( rockContext ); var taggedItemService = new TaggedItemService( rockContext ); Person primaryPerson = personService.Get( MergeData.PrimaryPersonId ?? 0 ); if ( primaryPerson != null ) { primaryPersonId = primaryPerson.Id; var changes = new List<string>(); foreach ( var p in MergeData.People.Where( p => p.Id != primaryPerson.Id ) ) { changes.Add( string.Format( "Merged <span class='field-value'>{0} [ID: {1}]</span> with this record.", p.FullName, p.Id ) ); } // Photo Id int? newPhotoId = MergeData.GetSelectedValue( MergeData.GetProperty( "Photo" ) ).Value.AsIntegerOrNull(); if ( !primaryPerson.PhotoId.Equals( newPhotoId ) ) { changes.Add( "Modified the photo." ); primaryPerson.PhotoId = newPhotoId; } primaryPerson.TitleValueId = GetNewIntValue( "Title", changes ); primaryPerson.FirstName = GetNewStringValue( "FirstName", changes ); primaryPerson.NickName = GetNewStringValue( "NickName", changes ); primaryPerson.MiddleName = GetNewStringValue( "MiddleName", changes ); primaryPerson.LastName = GetNewStringValue( "LastName", changes ); primaryPerson.SuffixValueId = GetNewIntValue( "Suffix", changes ); primaryPerson.RecordTypeValueId = GetNewIntValue( "RecordType", changes ); primaryPerson.RecordStatusValueId = GetNewIntValue( "RecordStatus", changes ); primaryPerson.RecordStatusReasonValueId = GetNewIntValue( "RecordStatusReason", changes ); primaryPerson.ConnectionStatusValueId = GetNewIntValue( "ConnectionStatus", changes ); primaryPerson.IsDeceased = GetNewBoolValue( "Deceased", changes ) ?? false; primaryPerson.Gender = (Gender)GetNewEnumValue( "Gender", typeof( Gender ), changes ); primaryPerson.MaritalStatusValueId = GetNewIntValue( "MaritalStatus", changes ); primaryPerson.SetBirthDate( GetNewDateTimeValue( "BirthDate", changes ) ); primaryPerson.AnniversaryDate = GetNewDateTimeValue( "AnniversaryDate", changes ); primaryPerson.GraduationYear = GetNewIntValue( "GraduationYear", changes ); primaryPerson.Email = GetNewStringValue( "Email", changes ); primaryPerson.IsEmailActive = GetNewBoolValue( "EmailActive", changes ) ?? true; primaryPerson.EmailNote = GetNewStringValue( "EmailNote", changes ); primaryPerson.EmailPreference = (EmailPreference)GetNewEnumValue( "EmailPreference", typeof( EmailPreference ), changes ); primaryPerson.SystemNote = GetNewStringValue( "InactiveReasonNote", changes ); primaryPerson.SystemNote = GetNewStringValue( "SystemNote", changes ); // Update phone numbers var phoneTypes = DefinedTypeCache.Read( Rock.SystemGuid.DefinedType.PERSON_PHONE_TYPE.AsGuid() ).DefinedValues; foreach ( var phoneType in phoneTypes ) { var phoneNumber = primaryPerson.PhoneNumbers.Where( p => p.NumberTypeValueId == phoneType.Id ).FirstOrDefault(); string oldValue = phoneNumber != null ? phoneNumber.Number : string.Empty; string key = "phone_" + phoneType.Id.ToString(); string newValue = GetNewStringValue( key, changes ); bool phoneNumberDeleted = false; if ( !oldValue.Equals( newValue, StringComparison.OrdinalIgnoreCase ) ) { // New phone doesn't match old if ( !string.IsNullOrWhiteSpace( newValue ) ) { // New value exists if ( phoneNumber == null ) { // Old value didn't exist... create new phone record phoneNumber = new PhoneNumber { NumberTypeValueId = phoneType.Id }; primaryPerson.PhoneNumbers.Add( phoneNumber ); } // Update phone number phoneNumber.Number = newValue; } else { // New value doesn't exist if ( phoneNumber != null ) { // old value existed.. delete it primaryPerson.PhoneNumbers.Remove( phoneNumber ); phoneNumberService.Delete( phoneNumber ); phoneNumberDeleted = true; } } } // check to see if IsMessagingEnabled is true for any of the merged people for this number/numbertype if ( phoneNumber != null && !phoneNumberDeleted && !phoneNumber.IsMessagingEnabled ) { var personIds = MergeData.People.Select( a => a.Id ).ToList(); var isMessagingEnabled = phoneNumberService.Queryable().Where( a => personIds.Contains( a.PersonId ) && a.Number == phoneNumber.Number && a.NumberTypeValueId == phoneNumber.NumberTypeValueId ).Any( a => a.IsMessagingEnabled ); if ( isMessagingEnabled ) { phoneNumber.IsMessagingEnabled = true; } } } // Save the new record rockContext.SaveChanges(); // Update the attributes primaryPerson.LoadAttributes( rockContext ); foreach ( var property in MergeData.Properties.Where( p => p.Key.StartsWith( "attr_" ) ) ) { string attributeKey = property.Key.Substring( 5 ); string oldValue = primaryPerson.GetAttributeValue( attributeKey ) ?? string.Empty; string newValue = GetNewStringValue( property.Key, changes ) ?? string.Empty; if ( !oldValue.Equals( newValue ) ) { var attribute = primaryPerson.Attributes[attributeKey]; Rock.Attribute.Helper.SaveAttributeValue( primaryPerson, attribute, newValue, rockContext ); } } HistoryService.SaveChanges( rockContext, typeof( Person ), Rock.SystemGuid.Category.HISTORY_PERSON_DEMOGRAPHIC_CHANGES.AsGuid(), primaryPerson.Id, changes ); // Delete the unselected photos string photoKeeper = primaryPerson.PhotoId.HasValue ? primaryPerson.PhotoId.Value.ToString() : string.Empty; foreach ( var photoValue in MergeData.Properties .Where( p => p.Key == "Photo" ) .SelectMany( p => p.Values ) .Where( v => v.Value != "" && v.Value != photoKeeper ) .Select( v => v.Value ) ) { int photoId = 0; if ( int.TryParse( photoValue, out photoId ) ) { var photo = binaryFileService.Get( photoId ); if ( photo != null ) { string errorMessages; if ( binaryFileService.CanDelete( photo, out errorMessages ) ) { binaryFileService.Delete( photo ); } } } } rockContext.SaveChanges(); // Delete merged person's family records and any families that would be empty after merge foreach ( var p in MergeData.People.Where( p => p.Id != primaryPersonId.Value ) ) { // Delete the merged person's phone numbers (we've already updated the primary persons values) foreach ( var phoneNumber in phoneNumberService.GetByPersonId( p.Id ) ) { phoneNumberService.Delete( phoneNumber ); } // If there was more than one email address and user has logins, then set any of the local // logins ( database & AD ) to require a reconfirmation if ( reconfirmRequired ) { foreach ( var login in userLoginService.GetByPersonId( p.Id ) ) { var component = Rock.Security.AuthenticationContainer.GetComponent( login.EntityType.Name ); if ( component != null && !component.RequiresRemoteAuthentication ) { login.IsConfirmed = false; } } } rockContext.SaveChanges(); // Delete the merged person's other family member records and the family if they were the only one in the family Guid familyGuid = Rock.SystemGuid.GroupType.GROUPTYPE_FAMILY.AsGuid(); foreach ( var familyMember in groupMemberService.Queryable().Where( m => m.PersonId == p.Id && m.Group.GroupType.Guid == familyGuid ) ) { groupMemberService.Delete( familyMember ); rockContext.SaveChanges(); // Get the family var family = groupService.Queryable( "Members" ).Where( f => f.Id == familyMember.GroupId ).FirstOrDefault(); if ( !family.Members.Any() ) { // If there are not any other family members, delete the family record. // If theres any people that have this group as a giving group, set it to null (the person being merged should be the only one) foreach ( Person gp in personService.Queryable().Where( g => g.GivingGroupId == family.Id ) ) { gp.GivingGroupId = null; } // save to the database prior to doing groupService.Delete since .Delete quietly might not delete if thinks the Family is used for a GivingGroupId rockContext.SaveChanges(); // Delete the family string errorMessage; if ( groupService.CanDelete( family, out errorMessage ) ) { var oldFamilyChanges = new List<string>(); History.EvaluateChange( oldFamilyChanges, "Family", family.Name, string.Empty ); HistoryService.SaveChanges( rockContext, typeof( Person ), Rock.SystemGuid.Category.HISTORY_PERSON_FAMILY_CHANGES.AsGuid(), primaryPersonId.Value, oldFamilyChanges, family.Name, typeof( Group ), family.Id ); groupService.Delete( family ); rockContext.SaveChanges(); } } } } // Flush any security roles that the merged person's other records were a part of foreach ( var p in MergeData.People.Where( p => p.Id != primaryPersonId.Value ) ) { foreach ( var groupMember in groupMemberService.Queryable().Where( m => m.PersonId == p.Id ) ) { Group group = new GroupService( rockContext ).Get( groupMember.GroupId ); if ( group.IsSecurityRole || group.GroupType.Guid.Equals( Rock.SystemGuid.GroupType.GROUPTYPE_SECURITY_ROLE.AsGuid() ) ) { Rock.Security.Role.Flush( group.Id ); Rock.Security.Authorization.Flush(); } } } // now that the Merge is complete, the EntitySet can be marked to be deleted by the RockCleanup job var entitySetService = new EntitySetService( rockContext ); var entitySet = entitySetService.Get( MergeData.EntitySetId ); if ( entitySet != null ) { entitySet.ExpireDateTime = RockDateTime.Now.AddMinutes(-1); entitySet.EntitySetPurposeValueId = null; rockContext.SaveChanges(); } } } ); foreach ( var p in MergeData.People.Where( p => p.Id != primaryPersonId.Value ) ) { // Run merge proc to merge all associated data var parms = new Dictionary<string, object>(); parms.Add( "OldId", p.Id ); parms.Add( "NewId", primaryPersonId.Value ); DbService.ExecuteCommand( "spCrm_PersonMerge", CommandType.StoredProcedure, parms ); } NavigateToLinkedPage( "PersonDetailPage", "PersonId", primaryPersonId.Value ); }
/// <summary> /// Handles adding families from the given XML element snippet /// </summary> /// <param name="elemFamilies">The xml element containing all the families.</param> /// <param name="rockContext">The rock context.</param> private void AddFamilies( XElement elemFamilies, RockContext rockContext ) { if ( elemFamilies == null ) { return; } // Persist the storage type's settings specific to the photo binary file type var settings = new Dictionary<string, string>(); if ( _personImageBinaryFileType.Attributes == null ) { _personImageBinaryFileType.LoadAttributes(); } foreach ( var attributeValue in _personImageBinaryFileType.AttributeValues ) { settings.Add( attributeValue.Key, attributeValue.Value.Value ); } _personImageBinaryFileTypeSettings = settings.ToJson(); bool fabricateAttendance = GetAttributeValue( "FabricateAttendance" ).AsBoolean(); GroupService groupService = new GroupService( rockContext ); var allFamilies = rockContext.Groups; List<Group> allGroups = new List<Group>(); var attendanceData = new Dictionary<Guid, List<Attendance>>(); // Next create the family along with its members and related data foreach ( var elemFamily in elemFamilies.Elements( "family" ) ) { Guid guid = elemFamily.Attribute( "guid" ).Value.Trim().AsGuid(); var familyMembers = BuildFamilyMembersFromXml( elemFamily.Element( "members" ), rockContext ); // Call replica of groupService's SaveNewFamily method in an attempt to speed things up Group family = CreateNewFamily( familyMembers, campusId: 1 ); family.Guid = guid; // add the family to the context's list of groups allFamilies.Add( family ); // add the families address(es) AddFamilyAddresses( groupService, family, elemFamily.Element( "addresses" ), rockContext ); // add their attendance data if ( fabricateAttendance ) { AddFamilyAttendance( family, elemFamily, rockContext, attendanceData ); } allGroups.Add( family ); _stopwatch.Stop(); AppendFormat( "{0:00}:{1:00}.{2:00} added {3}<br/>", _stopwatch.Elapsed.Minutes, _stopwatch.Elapsed.Seconds, _stopwatch.Elapsed.Milliseconds / 10, family.Name ); _stopwatch.Start(); } rockContext.ChangeTracker.DetectChanges(); rockContext.SaveChanges( disablePrePostProcessing: true ); // Now save each person's attributevalues (who had them defined in the XML) // and add each person's ID to a dictionary for use later. _stopwatch.Stop(); AppendFormat( "{0:00}:{1:00}.{2:00} saving attributes for everyone...<br/>", _stopwatch.Elapsed.Minutes, _stopwatch.Elapsed.Seconds, _stopwatch.Elapsed.Milliseconds / 10 ); _stopwatch.Start(); AttributeValueService attributeValueService = new AttributeValueService( rockContext ); foreach ( var gm in allGroups.SelectMany( g => g.Members ) ) { // Put the person's id into the people dictionary for later use. if ( !_peopleDictionary.ContainsKey( gm.Person.Guid ) ) { _peopleDictionary.Add( gm.Person.Guid, gm.Person.Id ); } // Only save if the person had attributes, otherwise it will error. if ( _personWithAttributes.ContainsKey( gm.Person.Guid ) ) { foreach ( var attributeCache in gm.Person.Attributes.Select( a => a.Value ) ) { var newValue = gm.Person.AttributeValues[attributeCache.Key]; if ( newValue != null ) { var attributeValue = new AttributeValue(); attributeValue.AttributeId = newValue.AttributeId; attributeValue.EntityId = gm.Person.Id; attributeValue.Value = newValue.Value; rockContext.AttributeValues.Add( attributeValue ); } } } } rockContext.ChangeTracker.DetectChanges(); rockContext.SaveChanges( disablePrePostProcessing: true ); _stopwatch.Stop(); AppendFormat( "{0:00}:{1:00}.{2:00} attributes saved<br/>", _stopwatch.Elapsed.Minutes, _stopwatch.Elapsed.Seconds, _stopwatch.Elapsed.Milliseconds / 10 ); _stopwatch.Start(); // Create person alias records for each person manually since we set disablePrePostProcessing=true on save PersonService personService = new PersonService( rockContext ); foreach ( var person in personService.Queryable( "Aliases", true ) .Where( p => _peopleDictionary.Keys.Contains( p.Guid ) && !p.Aliases.Any() ) ) { person.Aliases.Add( new PersonAlias { AliasPersonId = person.Id, AliasPersonGuid = person.Guid } ); } rockContext.ChangeTracker.DetectChanges(); rockContext.SaveChanges( disablePrePostProcessing: true ); _stopwatch.Stop(); AppendFormat( "{0:00}:{1:00}.{2:00} added person aliases<br/>", _stopwatch.Elapsed.Minutes, _stopwatch.Elapsed.Seconds, _stopwatch.Elapsed.Milliseconds / 10 ); _stopwatch.Start(); // Put the person alias ids into the people alias dictionary for later use. PersonAliasService personAliasService = new PersonAliasService( rockContext ); foreach ( var personAlias in personAliasService.Queryable( "Person" ) .Where( a => _peopleDictionary.Keys.Contains( a.Person.Guid ) && a.PersonId == a.AliasPersonId ) ) { _peopleAliasDictionary.Add( personAlias.Person.Guid, personAlias.Id ); } // Now that person aliases have been saved, save the attendance records var attendanceService = new AttendanceService( rockContext ); var attendanceGuids = attendanceData.Select( a => a.Key ).ToList(); foreach ( var aliasKeyValue in _peopleAliasDictionary .Where( a => attendanceGuids.Contains( a.Key )) ) { foreach ( var attendance in attendanceData[aliasKeyValue.Key] ) { attendance.PersonAliasId = aliasKeyValue.Value; attendanceService.Add( attendance ); } } rockContext.ChangeTracker.DetectChanges(); rockContext.SaveChanges( disablePrePostProcessing: true ); _stopwatch.Stop(); AppendFormat( "{0:00}:{1:00}.{2:00} added attendance records<br/>", _stopwatch.Elapsed.Minutes, _stopwatch.Elapsed.Seconds, _stopwatch.Elapsed.Milliseconds / 10 ); _stopwatch.Start(); // Now re-process the family section looking for any giving data. // We do this last because we need the personAliases that were just added. // Persist the storage type's settings specific to the contribution binary file type settings = new Dictionary<string, string>(); if ( _checkImageBinaryFileType.Attributes == null ) { _checkImageBinaryFileType.LoadAttributes(); } foreach ( var attributeValue in _checkImageBinaryFileType.AttributeValues ) { settings.Add( attributeValue.Key, attributeValue.Value.Value ); } _checkImageBinaryFileTypeSettings = settings.ToJson(); foreach ( var elemFamily in elemFamilies.Elements( "family" ) ) { // add the families giving data if ( GetAttributeValue( "EnableGiving" ).AsBoolean() ) { // Support multiple giving elements per family foreach ( var elementGiving in elemFamily.Elements( "giving" ) ) { AddFamilyGiving( elementGiving, elemFamily.Attribute( "name" ).Value, rockContext ); } } } if ( GetAttributeValue( "EnableGiving" ).AsBoolean() ) { // Now add the batches to the service to be persisted var financialBatchService = new FinancialBatchService( rockContext ); foreach ( var financialBatch in _contributionBatches ) { financialBatchService.Add( financialBatch.Value ); } } rockContext.ChangeTracker.DetectChanges(); rockContext.SaveChanges( disablePrePostProcessing: true ); }
/// <summary> /// Gets financial transaction details based on selected filter values. /// </summary> /// <param name="start">The start.</param> /// <param name="end">The end.</param> /// <param name="minAmount">The minimum amount.</param> /// <param name="maxAmount">The maximum amount.</param> /// <param name="currencyTypeIds">The currency type ids.</param> /// <param name="sourceTypeIds">The source type ids.</param> /// <param name="accountIds">The account ids.</param> /// <param name="dataViewId">The data view identifier.</param> /// <returns></returns> public IQueryable <FinancialTransactionDetail> GetGifts( DateTime?start, DateTime?end, decimal?minAmount, decimal?maxAmount, List <int> currencyTypeIds, List <int> sourceTypeIds, List <int> accountIds, int?dataViewId) { // Base Transaction Detail query var qry = GetGifts(); // Start Date Filter if (start.HasValue) { qry = qry.Where(t => t.Transaction.TransactionDateTime >= start.Value); } // End Date Filter if (end.HasValue) { qry = qry.Where(t => t.Transaction.TransactionDateTime < end.Value); } // Account Id Filter var distictAccountIds = accountIds.Where(i => i != 0).Distinct().ToList(); if (distictAccountIds.Any()) { qry = qry .Where(t => distictAccountIds.Contains(t.AccountId)); } // Currency Type Filter var distictCurrencyTypeIds = currencyTypeIds.Where(i => i != 0).Distinct().ToList(); if (distictCurrencyTypeIds.Any()) { qry = qry .Where(t => t.Transaction.FinancialPaymentDetail != null && t.Transaction.FinancialPaymentDetail.CurrencyTypeValueId.HasValue && distictCurrencyTypeIds.Contains(t.Transaction.FinancialPaymentDetail.CurrencyTypeValueId.Value)); } // Source Type Filter var distictSourceTypeIds = sourceTypeIds.Where(i => i != 0).Distinct().ToList(); if (distictSourceTypeIds.Any()) { qry = qry .Where(t => t.Transaction.SourceTypeValueId.HasValue && distictSourceTypeIds.Contains(t.Transaction.SourceTypeValueId.Value)); } // Amount Range Filter if (minAmount.HasValue || maxAmount.HasValue) { var givingIdQry = qry .GroupBy(d => d.Transaction.AuthorizedPersonAlias.Person.GivingId) .Select(d => new { d.Key, Total = d.Sum(t => t.Amount) }) .Where(s => (!minAmount.HasValue || s.Total >= minAmount.Value) && (!maxAmount.HasValue || s.Total <= maxAmount.Value)) .Select(s => s.Key); // put all the givingIds into a List instead of a subquery to prevent a timeout issue var givingIdList = givingIdQry.ToList(); qry = qry .Where(d => givingIdList.Contains(d.Transaction.AuthorizedPersonAlias.Person.GivingId)); } // Data View Filter if (dataViewId.HasValue) { var rockContext = (RockContext)this.Context; if (rockContext != null) { var personService = new PersonService(rockContext); var dataView = new DataViewService(rockContext).Get(dataViewId.Value); if (dataView != null) { var errorMessages = new List <string>(); ParameterExpression paramExpression = personService.ParameterExpression; Expression whereExpression = dataView.GetExpression(personService, paramExpression, out errorMessages); SortProperty sortProperty = null; var dataViewGivingIdQry = personService .Queryable().AsNoTracking() .Where(paramExpression, whereExpression, sortProperty) .Select(p => p.GivingId); qry = qry .Where(t => dataViewGivingIdQry.Contains(t.Transaction.AuthorizedPersonAlias.Person.GivingId)); } } } return(qry); }