/// <summary> /// Maps the contribution. /// </summary> /// <param name="csvData">The table data.</param> private int MapContribution(CSVInstance csvData) { var lookupContext = new RockContext(); int transactionEntityTypeId = EntityTypeCache.Read("Rock.Model.FinancialTransaction").Id; var transactionTypeContributionId = DefinedValueCache.Read(new Guid(Rock.SystemGuid.DefinedValue.TRANSACTION_TYPE_CONTRIBUTION), lookupContext).Id; var currencyTypes = DefinedTypeCache.Read(new Guid(Rock.SystemGuid.DefinedType.FINANCIAL_CURRENCY_TYPE)); int currencyTypeACH = currencyTypes.DefinedValues.FirstOrDefault(dv => dv.Guid.Equals(new Guid(Rock.SystemGuid.DefinedValue.CURRENCY_TYPE_ACH))).Id; int currencyTypeCash = currencyTypes.DefinedValues.FirstOrDefault(dv => dv.Guid.Equals(new Guid(Rock.SystemGuid.DefinedValue.CURRENCY_TYPE_CASH))).Id; int currencyTypeCheck = currencyTypes.DefinedValues.FirstOrDefault(dv => dv.Guid.Equals(new Guid(Rock.SystemGuid.DefinedValue.CURRENCY_TYPE_CHECK))).Id; int currencyTypeCreditCard = currencyTypes.DefinedValues.FirstOrDefault(dv => dv.Guid.Equals(new Guid(Rock.SystemGuid.DefinedValue.CURRENCY_TYPE_CREDIT_CARD))).Id; int?currencyTypeNonCash = currencyTypes.DefinedValues.Where(dv => dv.Value.Equals("Non-Cash")).Select(dv => (int?)dv.Id).FirstOrDefault(); if (currencyTypeNonCash == null) { var newTenderNonCash = new DefinedValue(); newTenderNonCash.Value = "Non-Cash"; newTenderNonCash.Description = "Non-Cash"; newTenderNonCash.DefinedTypeId = currencyTypes.Id; lookupContext.DefinedValues.Add(newTenderNonCash); lookupContext.SaveChanges(); currencyTypeNonCash = newTenderNonCash.Id; } var creditCardTypes = DefinedTypeCache.Read(new Guid(Rock.SystemGuid.DefinedType.FINANCIAL_CREDIT_CARD_TYPE)).DefinedValues; int sourceTypeOnsite = DefinedValueCache.Read(new Guid(Rock.SystemGuid.DefinedValue.FINANCIAL_SOURCE_TYPE_ONSITE_COLLECTION), lookupContext).Id; int sourceTypeWebsite = DefinedValueCache.Read(new Guid(Rock.SystemGuid.DefinedValue.FINANCIAL_SOURCE_TYPE_WEBSITE), lookupContext).Id; int sourceTypeKiosk = DefinedValueCache.Read(new Guid(Rock.SystemGuid.DefinedValue.FINANCIAL_SOURCE_TYPE_KIOSK), lookupContext).Id; var refundReasons = DefinedTypeCache.Read(new Guid(Rock.SystemGuid.DefinedType.FINANCIAL_TRANSACTION_REFUND_REASON), lookupContext).DefinedValues; var accountList = new FinancialAccountService(lookupContext).Queryable().AsNoTracking().ToList(); int?defaultBatchId = null; if (ImportedBatches.ContainsKey(0)) { defaultBatchId = ImportedBatches[0]; } // Get all imported contributions var importedContributions = new FinancialTransactionService(lookupContext).Queryable().AsNoTracking() .Where(c => c.ForeignId != null) .ToDictionary(t => (int)t.ForeignId, t => (int?)t.Id); // List for batching new contributions var newTransactions = new List <FinancialTransaction>(); int completed = 0; ReportProgress(0, string.Format("Verifying contribution import ({0:N0} already exist).", importedContributions.Count)); string[] row; // Uses a look-ahead enumerator: this call will move to the next record immediately while ((row = csvData.Database.FirstOrDefault()) != null) { string individualIdKey = row[IndividualID]; int? individualId = individualIdKey.AsType <int?>(); string contributionIdKey = row[ContributionID]; int? contributionId = contributionIdKey.AsType <int?>(); if (contributionId != null && !importedContributions.ContainsKey((int)contributionId)) { var transaction = new FinancialTransaction(); transaction.CreatedByPersonAliasId = ImportPersonAliasId; transaction.ModifiedByPersonAliasId = ImportPersonAliasId; transaction.TransactionTypeValueId = transactionTypeContributionId; transaction.ForeignKey = contributionId.ToString(); transaction.ForeignId = contributionId; int?giverAliasId = null; var personKeys = GetPersonKeys(individualId); if (personKeys != null && personKeys.PersonAliasId > 0) { giverAliasId = personKeys.PersonAliasId; transaction.CreatedByPersonAliasId = giverAliasId; transaction.AuthorizedPersonAliasId = giverAliasId; transaction.ProcessedByPersonAliasId = giverAliasId; } string summary = row[Memo] as string; if (summary != null) { transaction.Summary = summary; } string batchIdKey = row[ContributionBatchID]; int? batchId = batchIdKey.AsType <int?>(); if (batchId != null && ImportedBatches.Any(b => b.Key.Equals(batchId))) { transaction.BatchId = ImportedBatches.FirstOrDefault(b => b.Key.Equals(batchId)).Value; } else { // use the default batch for any non-matching transactions transaction.BatchId = defaultBatchId; } string receivedDateKey = row[ReceivedDate]; DateTime?receivedDate = receivedDateKey.AsType <DateTime?>(); if (receivedDate != null) { transaction.TransactionDateTime = receivedDate; transaction.CreatedDateTime = receivedDate; transaction.ModifiedDateTime = ImportDateTime; } string contributionType = row[ContributionTypeName].ToStringSafe().ToLower(); if (contributionType != null) { // set default source to onsite, exceptions listed below transaction.SourceTypeValueId = sourceTypeOnsite; int?paymentCurrencyTypeId = null, creditCardTypeId = null; if (contributionType == "cash") { paymentCurrencyTypeId = currencyTypeCash; } else if (contributionType == "check") { paymentCurrencyTypeId = currencyTypeCheck; } else if (contributionType == "ach") { paymentCurrencyTypeId = currencyTypeACH; transaction.SourceTypeValueId = sourceTypeWebsite; } else if (contributionType == "credit card") { paymentCurrencyTypeId = currencyTypeCreditCard; transaction.SourceTypeValueId = sourceTypeWebsite; } else { paymentCurrencyTypeId = currencyTypeNonCash; } var paymentDetail = new FinancialPaymentDetail(); paymentDetail.CreatedDateTime = receivedDate; paymentDetail.CreatedByPersonAliasId = giverAliasId; paymentDetail.ModifiedDateTime = ImportDateTime; paymentDetail.ModifiedByPersonAliasId = giverAliasId; paymentDetail.CurrencyTypeValueId = paymentCurrencyTypeId; paymentDetail.CreditCardTypeValueId = creditCardTypeId; paymentDetail.ForeignKey = contributionId.ToString(); paymentDetail.ForeignId = contributionId; transaction.FinancialPaymentDetail = paymentDetail; } string checkNumber = row[CheckNumber] as string; // if the check number is valid, put it in the transaction code if (checkNumber.AsType <int?>() != null) { transaction.TransactionCode = checkNumber; } // check for SecureGive kiosk transactions else if (!string.IsNullOrEmpty(checkNumber) && checkNumber.StartsWith("SG")) { transaction.SourceTypeValueId = sourceTypeKiosk; } string fundName = row[FundName] as string; string subFund = row[SubFundName] as string; string fundGLAccount = row[FundGLAccount] as string; string subFundGLAccount = row[SubFundGLAccount] as string; string isFundActiveKey = row[FundIsActive]; Boolean?isFundActive = isFundActiveKey.AsType <Boolean?>(); string statedValueKey = row[StatedValue]; decimal?statedValue = statedValueKey.AsType <decimal?>(); string amountKey = row[Amount]; decimal?amount = amountKey.AsType <decimal?>(); if (fundName != null & amount != null) { int transactionAccountId; var parentAccount = accountList.FirstOrDefault(a => a.Name.Equals(fundName) && a.CampusId == null); if (parentAccount == null) { parentAccount = AddAccount(lookupContext, fundName, fundGLAccount, null, null, isFundActive); accountList.Add(parentAccount); } if (!String.IsNullOrWhiteSpace(subFund)) { int?campusFundId = null; // assign a campus if the subfund is a campus fund var campusFund = CampusList.FirstOrDefault(c => subFund.StartsWith(c.Name) || subFund.StartsWith(c.ShortCode)); if (campusFund != null) { // use full campus name as the subfund subFund = campusFund.Name; campusFundId = campusFund.Id; } // add info to easily find/assign this fund in the view subFund = string.Format("{0} {1}", subFund, fundName); var childAccount = accountList.FirstOrDefault(c => c.Name.Equals(subFund) && c.ParentAccountId == parentAccount.Id); if (childAccount == null) { // create a child account with a campusId if it was set childAccount = AddAccount(lookupContext, subFund, subFundGLAccount, campusFundId, parentAccount.Id, isFundActive); accountList.Add(childAccount); } transactionAccountId = childAccount.Id; } else { transactionAccountId = parentAccount.Id; } if (amount == 0 && statedValue != null && statedValue != 0) { amount = statedValue; } var transactionDetail = new FinancialTransactionDetail(); transactionDetail.Amount = (decimal)amount; transactionDetail.CreatedDateTime = receivedDate; transactionDetail.AccountId = transactionAccountId; transaction.TransactionDetails.Add(transactionDetail); if (amount < 0) { transaction.RefundDetails = new FinancialTransactionRefund(); transaction.RefundDetails.CreatedDateTime = receivedDate; transaction.RefundDetails.RefundReasonValueId = refundReasons.Where(dv => summary != null && dv.Value.Contains(summary)) .Select(dv => (int?)dv.Id).FirstOrDefault(); transaction.RefundDetails.RefundReasonSummary = summary; } } newTransactions.Add(transaction); completed++; if (completed % (ReportingNumber * 10) < 1) { ReportProgress(0, string.Format("{0:N0} contributions imported.", completed)); } else if (completed % ReportingNumber < 1) { SaveContributions(newTransactions); newTransactions.Clear(); ReportPartialProgress(); } } } if (newTransactions.Any()) { SaveContributions(newTransactions); } ReportProgress(100, string.Format("Finished contribution import: {0:N0} contributions imported.", completed)); return(completed); }
/// <summary> /// Maps the batch data. /// </summary> /// <param name="csvData">The table data.</param> /// <exception cref="System.NotImplementedException"></exception> private int MapBatch(CSVInstance csvData) { var batchStatusClosed = Rock.Model.BatchStatus.Closed; var newBatches = new List <FinancialBatch>(); int completed = 0; ReportProgress(0, string.Format("Verifying batch import ({0:N0} already exist).", ImportedBatches.Count)); string[] row; // Uses a look-ahead enumerator: this call will move to the next record immediately while ((row = csvData.Database.FirstOrDefault()) != null) { string batchIdKey = row[BatchID]; int? batchId = batchIdKey.AsType <int?>(); if (batchId != null && !ImportedBatches.ContainsKey((int)batchId)) { var batch = new FinancialBatch(); batch.CreatedByPersonAliasId = ImportPersonAliasId; batch.ForeignKey = batchId.ToString(); batch.ForeignId = batchId; batch.Note = string.Empty; batch.Status = batchStatusClosed; batch.AccountingSystemCode = string.Empty; string name = row[BatchName] as string; if (name != null) { name = name.Trim(); batch.Name = name.Left(50); batch.CampusId = CampusList.Where(c => name.StartsWith(c.Name) || name.StartsWith(c.ShortCode)) .Select(c => (int?)c.Id).FirstOrDefault(); } string batchDateKey = row[BatchDate]; DateTime?batchDate = batchDateKey.AsType <DateTime?>(); if (batchDate != null) { batch.BatchStartDateTime = batchDate; batch.BatchEndDateTime = batchDate; } string amountKey = row[BatchAmount]; decimal?amount = amountKey.AsType <decimal?>(); if (amount != null) { batch.ControlAmount = amount.HasValue ? amount.Value : new decimal(); } newBatches.Add(batch); completed++; if (completed % (ReportingNumber * 10) < 1) { ReportProgress(0, string.Format("{0:N0} batches imported.", completed)); } else if (completed % ReportingNumber < 1) { SaveFinancialBatches(newBatches); newBatches.ForEach(b => ImportedBatches.Add((int)b.ForeignId, (int?)b.Id)); newBatches.Clear(); ReportPartialProgress(); } } } // add a default batch to use with contributions if (!ImportedBatches.ContainsKey(0)) { var defaultBatch = new FinancialBatch(); defaultBatch.CreatedDateTime = ImportDateTime; defaultBatch.CreatedByPersonAliasId = ImportPersonAliasId; defaultBatch.Status = Rock.Model.BatchStatus.Closed; defaultBatch.Name = string.Format("Default Batch (Imported {0})", ImportDateTime); defaultBatch.ControlAmount = 0.0m; defaultBatch.ForeignKey = "0"; defaultBatch.ForeignId = 0; newBatches.Add(defaultBatch); } if (newBatches.Any()) { SaveFinancialBatches(newBatches); newBatches.ForEach(b => ImportedBatches.Add((int)b.ForeignId, (int?)b.Id)); } ReportProgress(100, string.Format("Finished batch import: {0:N0} batches imported.", completed)); return(completed); }
/// <summary> /// Checks if the file matches a known format. /// </summary> /// <param name="fileName">Name of the file.</param> /// <returns></returns> private bool FileTypeMatches( CSVInstance.RockDataType filetype, string name ) { if ( name.ToUpper().StartsWith( filetype.ToString() ) ) { return true; } return false; }
/// <summary> /// Loads the individual data. /// </summary> /// <param name="csvData">The CSV data.</param> private int LoadIndividuals(CSVInstance csvData) { var lookupContext = new RockContext(); var groupTypeRoleService = new GroupTypeRoleService(lookupContext); var groupMemberService = new GroupMemberService(lookupContext); // Marital statuses: Married, Single, Separated, etc var maritalStatusTypes = DefinedTypeCache.Read(new Guid(Rock.SystemGuid.DefinedType.PERSON_MARITAL_STATUS), lookupContext).DefinedValues; // Connection statuses: Member, Visitor, Attendee, etc var connectionStatusTypes = DefinedTypeCache.Read(new Guid(Rock.SystemGuid.DefinedType.PERSON_CONNECTION_STATUS), lookupContext).DefinedValues; int memberConnectionStatusId = connectionStatusTypes.FirstOrDefault(dv => dv.Guid == new Guid(Rock.SystemGuid.DefinedValue.PERSON_CONNECTION_STATUS_MEMBER)).Id; int visitorConnectionStatusId = connectionStatusTypes.FirstOrDefault(dv => dv.Guid == new Guid(Rock.SystemGuid.DefinedValue.PERSON_CONNECTION_STATUS_VISITOR)).Id; int attendeeConnectionStatusId = connectionStatusTypes.FirstOrDefault(dv => dv.Guid == new Guid(Rock.SystemGuid.DefinedValue.PERSON_CONNECTION_STATUS_ATTENDEE)).Id; // Suffix type: Dr., Jr., II, etc var suffixTypes = DefinedTypeCache.Read(new Guid(Rock.SystemGuid.DefinedType.PERSON_SUFFIX), lookupContext).DefinedValues; // Title type: Mr., Mrs. Dr., etc var titleTypes = DefinedTypeCache.Read(new Guid(Rock.SystemGuid.DefinedType.PERSON_TITLE), lookupContext).DefinedValues; // Record statuses: Active, Inactive, Pending int?recordStatusActiveId = DefinedValueCache.Read(new Guid(Rock.SystemGuid.DefinedValue.PERSON_RECORD_STATUS_ACTIVE), lookupContext).Id; int?recordStatusInactiveId = DefinedValueCache.Read(new Guid(Rock.SystemGuid.DefinedValue.PERSON_RECORD_STATUS_INACTIVE), lookupContext).Id; int?recordStatusPendingId = DefinedValueCache.Read(new Guid(Rock.SystemGuid.DefinedValue.PERSON_RECORD_STATUS_PENDING), lookupContext).Id; // Deceased record status reason (others available: No Activity, Moved, etc) var recordStatusDeceasedId = DefinedValueCache.Read(new Guid(Rock.SystemGuid.DefinedValue.PERSON_RECORD_STATUS_REASON_DECEASED)).Id; // Record type: Person int?personRecordTypeId = DefinedValueCache.Read(new Guid(Rock.SystemGuid.DefinedValue.PERSON_RECORD_TYPE_PERSON), lookupContext).Id; // Group roles: Owner, Adult, Child, others GroupTypeRole ownerRole = groupTypeRoleService.Get(new Guid(Rock.SystemGuid.GroupRole.GROUPROLE_KNOWN_RELATIONSHIPS_OWNER)); int adultRoleId = groupTypeRoleService.Get(new Guid(Rock.SystemGuid.GroupRole.GROUPROLE_FAMILY_MEMBER_ADULT)).Id; int childRoleId = groupTypeRoleService.Get(new Guid(Rock.SystemGuid.GroupRole.GROUPROLE_FAMILY_MEMBER_CHILD)).Id; // Phone types: Home, Work, Mobile var numberTypeValues = DefinedTypeCache.Read(new Guid(Rock.SystemGuid.DefinedType.PERSON_PHONE_TYPE), lookupContext).DefinedValues; // Personal note type id var personalNoteTypeId = new NoteTypeService(lookupContext).Get(new Guid(Rock.SystemGuid.NoteType.PERSON_TIMELINE_NOTE)).Id; // School defined type var schoolDefinedType = DefinedTypeCache.Read(new Guid("576FF1E2-6225-4565-A16D-230E26167A3D")); // Look up existing Person attributes var personAttributes = new AttributeService(lookupContext).GetByEntityTypeId(PersonEntityTypeId).ToList(); var schoolAttribute = AttributeCache.Read(personAttributes.FirstOrDefault(a => a.Key == "School")); // Text field type id int textFieldTypeId = FieldTypeCache.Read(new Guid(Rock.SystemGuid.FieldType.TEXT), lookupContext).Id; int dateFieldTypeId = FieldTypeCache.Read(new Guid(Rock.SystemGuid.FieldType.DATE), lookupContext).Id; // Attribute entity type id int attributeEntityTypeId = EntityTypeCache.Read("Rock.Model.Attribute").Id; // Visit info category var visitInfoCategory = new CategoryService(lookupContext).GetByEntityTypeId(attributeEntityTypeId) .Where(c => c.Name == "Visit Information").FirstOrDefault(); // Look for custom attributes in the Individual file var allFields = csvData.TableNodes.FirstOrDefault().Children.Select((node, index) => new { node = node, index = index }).ToList(); Dictionary <int, string> customAttributes = allFields .Where(f => f.index > SecurityNote) .ToDictionary(f => f.index, f => f.node.Name.RemoveWhitespace()); // Add any attributes if they don't already exist if (customAttributes.Any()) { var newAttributes = new List <Rock.Model.Attribute>(); foreach (var newAttributePair in customAttributes.Where(ca => !personAttributes.Any(a => a.Key == ca.Value))) { var newAttribute = new Rock.Model.Attribute(); newAttribute.Name = newAttributePair.Value; newAttribute.Key = newAttributePair.Value.RemoveWhitespace(); newAttribute.Description = newAttributePair.Value + " created by CSV import"; newAttribute.EntityTypeQualifierValue = string.Empty; newAttribute.EntityTypeQualifierColumn = string.Empty; newAttribute.EntityTypeId = PersonEntityTypeId; newAttribute.FieldTypeId = textFieldTypeId; newAttribute.DefaultValue = string.Empty; newAttribute.IsMultiValue = false; newAttribute.IsGridColumn = false; newAttribute.IsRequired = false; newAttribute.Order = 0; newAttributes.Add(newAttribute); } lookupContext.Attributes.AddRange(newAttributes); lookupContext.SaveChanges(DisableAuditing); personAttributes.AddRange(newAttributes); } // Set the supported date formats var dateFormats = new[] { "yyyy-MM-dd", "MM/dd/yyyy", "MM/dd/yy" }; var currentFamilyGroup = new Group(); var newFamilyList = new List <Group>(); var newVisitorList = new List <Group>(); var newNoteList = new List <Note>(); int completed = 0; int newFamilies = 0; int newPeople = 0; ReportProgress(0, string.Format("Starting Individual import ({0:N0} already exist).", ImportedPeopleKeys.Count())); string[] row; row = csvData.Database.FirstOrDefault(); while (row != null) { int groupRoleId = adultRoleId; bool isFamilyRelationship = true; string rowFamilyName = row[FamilyName]; string rowFamilyKey = row[FamilyId]; string rowPersonKey = row[PersonId]; int? rowFamilyId = rowFamilyKey.AsType <int?>(); int? rowPersonId = rowPersonKey.AsType <int?>(); // Check that this person isn't already in our data var personExists = ImportedFamilies.Any(g => g.Members.Any(m => m.Person.ForeignKey == rowPersonKey)); if (!personExists) { #region person create var person = new Person(); person.ForeignKey = rowPersonKey; person.ForeignId = rowPersonId; person.SystemNote = string.Format("Imported via Excavator on {0}", ImportDateTime); person.RecordTypeValueId = personRecordTypeId; person.CreatedByPersonAliasId = ImportPersonAliasId; string firstName = row[FirstName].Left(50); string nickName = row[NickName].Left(50); person.FirstName = firstName; person.NickName = string.IsNullOrWhiteSpace(nickName) ? firstName : nickName; person.MiddleName = row[MiddleName].Left(50); person.LastName = row[LastName].Left(50); DateTime createdDateValue; if (DateTime.TryParseExact(row[CreatedDate], dateFormats, CultureInfo.InvariantCulture, DateTimeStyles.None, out createdDateValue)) { person.CreatedDateTime = createdDateValue; person.ModifiedDateTime = ImportDateTime; } else { person.CreatedDateTime = ImportDateTime; person.ModifiedDateTime = ImportDateTime; } DateTime birthDate; if (DateTime.TryParseExact(row[DateOfBirth], dateFormats, CultureInfo.InvariantCulture, DateTimeStyles.None, out birthDate)) { person.BirthDay = birthDate.Day; person.BirthMonth = birthDate.Month; person.BirthYear = birthDate.Year; } DateTime graduationDate; if (DateTime.TryParseExact(row[GraduationDate], dateFormats, CultureInfo.InvariantCulture, DateTimeStyles.None, out graduationDate)) { person.GraduationYear = graduationDate.Year; } DateTime anniversary; if (DateTime.TryParseExact(row[Anniversary], dateFormats, CultureInfo.InvariantCulture, DateTimeStyles.None, out anniversary)) { person.AnniversaryDate = anniversary; } string gender = row[Gender]; if (gender != null) { switch (gender.Trim().ToLower()) { case "m": case "male": person.Gender = Rock.Model.Gender.Male; break; case "f": case "female": person.Gender = Rock.Model.Gender.Female; break; default: person.Gender = Rock.Model.Gender.Unknown; break; } } string prefix = row[Prefix]; if (!string.IsNullOrWhiteSpace(prefix)) { prefix = prefix.RemoveSpecialCharacters().Trim(); person.TitleValueId = titleTypes.Where(s => prefix == s.Value.RemoveSpecialCharacters()) .Select(s => (int?)s.Id).FirstOrDefault(); } string suffix = row[Suffix]; if (!string.IsNullOrWhiteSpace(suffix)) { suffix = suffix.RemoveSpecialCharacters().Trim(); person.SuffixValueId = suffixTypes.Where(s => suffix == s.Value.RemoveSpecialCharacters()) .Select(s => (int?)s.Id).FirstOrDefault(); } string maritalStatus = row[MaritalStatus]; if (!string.IsNullOrWhiteSpace(maritalStatus)) { person.MaritalStatusValueId = maritalStatusTypes.Where(dv => dv.Value == maritalStatus) .Select(dv => (int?)dv.Id).FirstOrDefault(); } else { person.MaritalStatusValueId = maritalStatusTypes.Where(dv => dv.Value == "Unknown") .Select(dv => ( int? )dv.Id).FirstOrDefault(); } string familyRole = row[FamilyRole]; if (!string.IsNullOrWhiteSpace(familyRole)) { if (familyRole == "Visitor") { isFamilyRelationship = false; } if (familyRole == "Child" || person.Age < 18) { groupRoleId = childRoleId; } } string connectionStatus = row[ConnectionStatus]; if (!string.IsNullOrWhiteSpace(connectionStatus)) { if (connectionStatus == "Member") { person.ConnectionStatusValueId = memberConnectionStatusId; } else if (connectionStatus == "Visitor") { person.ConnectionStatusValueId = visitorConnectionStatusId; } else { // look for user-defined connection type or default to Attendee var customConnectionType = connectionStatusTypes.Where(dv => dv.Value == connectionStatus) .Select(dv => (int?)dv.Id).FirstOrDefault(); person.ConnectionStatusValueId = customConnectionType ?? attendeeConnectionStatusId; person.RecordStatusValueId = recordStatusActiveId; } } string recordStatus = row[RecordStatus]; if (!string.IsNullOrWhiteSpace(recordStatus)) { switch (recordStatus.Trim().ToLower()) { case "active": person.RecordStatusValueId = recordStatusActiveId; break; case "inactive": person.RecordStatusValueId = recordStatusInactiveId; break; default: person.RecordStatusValueId = recordStatusPendingId; break; } } string isDeceasedValue = row[IsDeceased]; if (!string.IsNullOrWhiteSpace(isDeceasedValue)) { switch (isDeceasedValue.Trim().ToLower()) { case "y": case "yes": person.IsDeceased = true; person.RecordStatusReasonValueId = recordStatusDeceasedId; person.RecordStatusValueId = recordStatusInactiveId; break; default: person.IsDeceased = false; break; } } var personNumbers = new Dictionary <string, string>(); personNumbers.Add("Home", row[HomePhone]); personNumbers.Add("Mobile", row[MobilePhone]); personNumbers.Add("Work", row[WorkPhone]); string smsAllowed = row[AllowSMS]; foreach (var numberPair in personNumbers.Where(n => !string.IsNullOrWhiteSpace(n.Value))) { var extension = string.Empty; var countryCode = Rock.Model.PhoneNumber.DefaultCountryCode(); var normalizedNumber = string.Empty; var countryIndex = numberPair.Value.IndexOf('+'); int extensionIndex = numberPair.Value.LastIndexOf('x') > 0 ? numberPair.Value.LastIndexOf('x') : numberPair.Value.Length; if (countryIndex >= 0) { countryCode = numberPair.Value.Substring(countryIndex, countryIndex + 3).AsNumeric(); normalizedNumber = numberPair.Value.Substring(countryIndex + 3, extensionIndex - 3).AsNumeric().TrimStart(new Char[] { '0' }); extension = numberPair.Value.Substring(extensionIndex); } else if (extensionIndex > 0) { normalizedNumber = numberPair.Value.Substring(0, extensionIndex).AsNumeric(); extension = numberPair.Value.Substring(extensionIndex).AsNumeric(); } else { normalizedNumber = numberPair.Value.AsNumeric(); } if (!string.IsNullOrWhiteSpace(normalizedNumber)) { var currentNumber = new PhoneNumber(); currentNumber.CountryCode = countryCode; currentNumber.CreatedByPersonAliasId = ImportPersonAliasId; currentNumber.Extension = extension.Left(20); currentNumber.Number = normalizedNumber.TrimStart(new Char[] { '0' }).Left(20); currentNumber.NumberFormatted = PhoneNumber.FormattedNumber(currentNumber.CountryCode, currentNumber.Number); currentNumber.NumberTypeValueId = numberTypeValues.Where(v => v.Value.Equals(numberPair.Key)) .Select(v => ( int? )v.Id).FirstOrDefault(); if (numberPair.Key == "Mobile") { switch (smsAllowed.Trim().ToLower()) { case "y": case "yes": case "active": currentNumber.IsMessagingEnabled = true; break; default: currentNumber.IsMessagingEnabled = false; break; } } person.PhoneNumbers.Add(currentNumber); } } // Map Person attributes person.Attributes = new Dictionary <string, AttributeCache>(); person.AttributeValues = new Dictionary <string, AttributeValueCache>(); bool isEmailActive; switch (row[IsEmailActive].Trim().ToLower()) { case "n": case "no": case "inactive": isEmailActive = false; break; default: isEmailActive = true; break; } EmailPreference emailPreference; switch (row[AllowBulkEmail].Trim().ToLower()) { case "n": case "no": case "inactive": emailPreference = EmailPreference.NoMassEmails; break; default: emailPreference = EmailPreference.EmailAllowed; break; } person.EmailPreference = emailPreference; string primaryEmail = row[Email].Trim().Left(75); if (!string.IsNullOrWhiteSpace(primaryEmail)) { if (primaryEmail.IsEmail()) { person.Email = primaryEmail; person.IsEmailActive = isEmailActive; } else { LogException("InvalidPrimaryEmail", string.Format("PersonId: {0} - Email: {1}", rowPersonKey, primaryEmail)); } } string schoolName = row[School]; if (!string.IsNullOrWhiteSpace(schoolName)) { // Add school if it doesn't exist Guid schoolGuid; var schoolExists = lookupContext.DefinedValues.Any(s => s.DefinedTypeId == schoolDefinedType.Id && s.Value.Equals(schoolName)); if (!schoolExists) { var newSchool = new DefinedValue(); newSchool.DefinedTypeId = schoolDefinedType.Id; newSchool.Value = schoolName; newSchool.Order = 0; lookupContext.DefinedValues.Add(newSchool); lookupContext.SaveChanges(); schoolGuid = newSchool.Guid; } else { schoolGuid = lookupContext.DefinedValues.FirstOrDefault(s => s.Value.Equals(schoolName)).Guid; } AddPersonAttribute(schoolAttribute, person, schoolGuid.ToString().ToUpper()); } foreach (var attributePair in customAttributes) { string newAttributeValue = row[attributePair.Key]; if (!string.IsNullOrWhiteSpace(newAttributeValue)) { // check if this attribute value is a date DateTime valueAsDateTime; if (DateTime.TryParseExact(newAttributeValue, dateFormats, CultureInfo.InvariantCulture, DateTimeStyles.None, out valueAsDateTime)) { newAttributeValue = valueAsDateTime.ToString("yyyy-MM-dd"); } int?newAttributeId = personAttributes.Where(a => a.Key == attributePair.Value.RemoveWhitespace()) .Select(a => (int?)a.Id).FirstOrDefault(); if (newAttributeId != null) { var newAttribute = AttributeCache.Read((int)newAttributeId); AddPersonAttribute(newAttribute, person, newAttributeValue); } } } // Add notes to timeline var notePairs = new Dictionary <string, string>(); notePairs.Add("General", row[GeneralNote]); notePairs.Add("Medical", row[MedicalNote]); notePairs.Add("Security", row[SecurityNote]); foreach (var notePair in notePairs.Where(n => !string.IsNullOrWhiteSpace(n.Value))) { var newNote = new Note(); newNote.NoteTypeId = personalNoteTypeId; newNote.CreatedByPersonAliasId = ImportPersonAliasId; newNote.CreatedDateTime = ImportDateTime; newNote.Text = notePair.Value; newNote.ForeignKey = rowPersonKey; newNote.ForeignId = rowPersonId; newNote.Caption = string.Format("{0} Note", notePair.Key); if (!notePair.Key.Equals("General")) { newNote.IsAlert = true; } newNoteList.Add(newNote); } #endregion person create var groupMember = new GroupMember(); groupMember.Person = person; groupMember.GroupRoleId = groupRoleId; groupMember.CreatedDateTime = ImportDateTime; groupMember.ModifiedDateTime = ImportDateTime; groupMember.CreatedByPersonAliasId = ImportPersonAliasId; groupMember.GroupMemberStatus = GroupMemberStatus.Active; if (rowFamilyKey != currentFamilyGroup.ForeignKey) { // person not part of the previous family, see if that family exists or create a new one currentFamilyGroup = ImportedFamilies.FirstOrDefault(g => g.ForeignKey == rowFamilyKey); if (currentFamilyGroup == null) { currentFamilyGroup = CreateFamilyGroup(row[FamilyName], rowFamilyKey); newFamilyList.Add(currentFamilyGroup); newFamilies++; } else { lookupContext.Groups.Attach(currentFamilyGroup); lookupContext.Entry(currentFamilyGroup).State = EntityState.Modified; } currentFamilyGroup.Members.Add(groupMember); } else { // person is part of this family group, check if they're a visitor if (isFamilyRelationship || currentFamilyGroup.Members.Count() < 1) { currentFamilyGroup.Members.Add(groupMember); } else { var visitorFamily = CreateFamilyGroup(person.LastName + " Family", rowFamilyKey); visitorFamily.Members.Add(groupMember); newFamilyList.Add(visitorFamily); newVisitorList.Add(visitorFamily); newFamilies++; } } // look ahead 1 row string rowNextFamilyKey = "-1"; if ((row = csvData.Database.FirstOrDefault()) != null) { rowNextFamilyKey = row[FamilyId]; } newPeople++; completed++; if (completed % (ReportingNumber * 10) < 1) { ReportProgress(0, string.Format("{0:N0} people imported.", completed)); } if (newPeople >= ReportingNumber && rowNextFamilyKey != currentFamilyGroup.ForeignKey) { SaveIndividuals(newFamilyList, newVisitorList, newNoteList); lookupContext.SaveChanges(); ReportPartialProgress(); // Clear out variables currentFamilyGroup = new Group(); newFamilyList.Clear(); newVisitorList.Clear(); newNoteList.Clear(); newPeople = 0; } } else { row = csvData.Database.FirstOrDefault(); } } // Save any changes to new families if (newFamilyList.Any()) { SaveIndividuals(newFamilyList, newVisitorList, newNoteList); } // Save any changes to existing families lookupContext.SaveChanges(); DetachAllInContext(lookupContext); lookupContext.Dispose(); ReportProgress(0, string.Format("Finished individual import: {0:N0} families and {1:N0} people added.", newFamilies, completed)); return(completed); }
/// <summary> /// Maps the contribution. /// </summary> /// <param name="csvData">The table data.</param> private int MapContribution( CSVInstance csvData ) { var lookupContext = new RockContext(); int transactionEntityTypeId = EntityTypeCache.Read( "Rock.Model.FinancialTransaction" ).Id; var transactionTypeContributionId = DefinedValueCache.Read( new Guid( Rock.SystemGuid.DefinedValue.TRANSACTION_TYPE_CONTRIBUTION ), lookupContext ).Id; var currencyTypes = DefinedTypeCache.Read( new Guid( Rock.SystemGuid.DefinedType.FINANCIAL_CURRENCY_TYPE ) ); int currencyTypeACH = currencyTypes.DefinedValues.FirstOrDefault( dv => dv.Guid.Equals( new Guid( Rock.SystemGuid.DefinedValue.CURRENCY_TYPE_ACH ) ) ).Id; int currencyTypeCash = currencyTypes.DefinedValues.FirstOrDefault( dv => dv.Guid.Equals( new Guid( Rock.SystemGuid.DefinedValue.CURRENCY_TYPE_CASH ) ) ).Id; int currencyTypeCheck = currencyTypes.DefinedValues.FirstOrDefault( dv => dv.Guid.Equals( new Guid( Rock.SystemGuid.DefinedValue.CURRENCY_TYPE_CHECK ) ) ).Id; int currencyTypeCreditCard = currencyTypes.DefinedValues.FirstOrDefault( dv => dv.Guid.Equals( new Guid( Rock.SystemGuid.DefinedValue.CURRENCY_TYPE_CREDIT_CARD ) ) ).Id; int? currencyTypeNonCash = currencyTypes.DefinedValues.Where( dv => dv.Value.Equals( "Non-Cash" ) ).Select( dv => ( int? )dv.Id ).FirstOrDefault(); if ( currencyTypeNonCash == null ) { var newTenderNonCash = new DefinedValue(); newTenderNonCash.Value = "Non-Cash"; newTenderNonCash.Description = "Non-Cash"; newTenderNonCash.DefinedTypeId = currencyTypes.Id; lookupContext.DefinedValues.Add( newTenderNonCash ); lookupContext.SaveChanges(); currencyTypeNonCash = newTenderNonCash.Id; } var creditCardTypes = DefinedTypeCache.Read( new Guid( Rock.SystemGuid.DefinedType.FINANCIAL_CREDIT_CARD_TYPE ) ).DefinedValues; int sourceTypeOnsite = DefinedValueCache.Read( new Guid( Rock.SystemGuid.DefinedValue.FINANCIAL_SOURCE_TYPE_ONSITE_COLLECTION ), lookupContext ).Id; int sourceTypeWebsite = DefinedValueCache.Read( new Guid( Rock.SystemGuid.DefinedValue.FINANCIAL_SOURCE_TYPE_WEBSITE ), lookupContext ).Id; int sourceTypeKiosk = DefinedValueCache.Read( new Guid( Rock.SystemGuid.DefinedValue.FINANCIAL_SOURCE_TYPE_KIOSK ), lookupContext ).Id; var refundReasons = DefinedTypeCache.Read( new Guid( Rock.SystemGuid.DefinedType.FINANCIAL_TRANSACTION_REFUND_REASON ), lookupContext ).DefinedValues; var accountList = new FinancialAccountService( lookupContext ).Queryable().AsNoTracking().ToList(); int? defaultBatchId = null; if ( ImportedBatches.ContainsKey( 0 ) ) { defaultBatchId = ImportedBatches[0]; } // Get all imported contributions var importedContributions = new FinancialTransactionService( lookupContext ).Queryable().AsNoTracking() .Where( c => c.ForeignId != null ) .ToDictionary( t => ( int )t.ForeignId, t => ( int? )t.Id ); // List for batching new contributions var newTransactions = new List<FinancialTransaction>(); int completed = 0; ReportProgress( 0, string.Format( "Verifying contribution import ({0:N0} already exist).", importedContributions.Count ) ); string[] row; // Uses a look-ahead enumerator: this call will move to the next record immediately while ( (row = csvData.Database.FirstOrDefault()) != null ) { string individualIdKey = row[IndividualID]; int? individualId = individualIdKey.AsType<int?>(); string contributionIdKey = row[ContributionID]; int? contributionId = contributionIdKey.AsType<int?>(); if ( contributionId != null && !importedContributions.ContainsKey( ( int )contributionId ) ) { var transaction = new FinancialTransaction(); transaction.CreatedByPersonAliasId = ImportPersonAliasId; transaction.ModifiedByPersonAliasId = ImportPersonAliasId; transaction.TransactionTypeValueId = transactionTypeContributionId; transaction.ForeignKey = contributionId.ToString(); transaction.ForeignId = contributionId; int? giverAliasId = null; var personKeys = GetPersonKeys( individualId ); if ( personKeys != null && personKeys.PersonAliasId > 0 ) { giverAliasId = personKeys.PersonAliasId; transaction.CreatedByPersonAliasId = giverAliasId; transaction.AuthorizedPersonAliasId = giverAliasId; transaction.ProcessedByPersonAliasId = giverAliasId; } else if ( AnonymousGiverAliasId != null && AnonymousGiverAliasId > 0 ) { giverAliasId = AnonymousGiverAliasId; transaction.AuthorizedPersonAliasId = giverAliasId; transaction.ProcessedByPersonAliasId = giverAliasId; } string summary = row[Memo] as string; if ( !String.IsNullOrWhiteSpace( summary ) ) { transaction.Summary = summary; } string batchIdKey = row[ContributionBatchID]; int? batchId = batchIdKey.AsType<int?>(); if ( batchId != null && ImportedBatches.Any( b => b.Key.Equals( batchId ) ) ) { transaction.BatchId = ImportedBatches.FirstOrDefault( b => b.Key.Equals( batchId ) ).Value; } else { // use the default batch for any non-matching transactions transaction.BatchId = defaultBatchId; } string receivedDateKey = row[ReceivedDate]; DateTime? receivedDate = receivedDateKey.AsType<DateTime?>(); if ( receivedDate != null ) { transaction.TransactionDateTime = receivedDate; transaction.CreatedDateTime = receivedDate; transaction.ModifiedDateTime = ImportDateTime; } string contributionType = row[ContributionTypeName].ToStringSafe().ToLower(); if ( !String.IsNullOrWhiteSpace( contributionType ) ) { // set default source to onsite, exceptions listed below transaction.SourceTypeValueId = sourceTypeOnsite; int? paymentCurrencyTypeId = null, creditCardTypeId = null; if ( contributionType == "cash" ) { paymentCurrencyTypeId = currencyTypeCash; } else if ( contributionType == "check" ) { paymentCurrencyTypeId = currencyTypeCheck; } else if ( contributionType == "ach" ) { paymentCurrencyTypeId = currencyTypeACH; transaction.SourceTypeValueId = sourceTypeWebsite; } else if ( contributionType == "credit card" ) { paymentCurrencyTypeId = currencyTypeCreditCard; transaction.SourceTypeValueId = sourceTypeWebsite; } else { paymentCurrencyTypeId = currencyTypeNonCash; } var paymentDetail = new FinancialPaymentDetail(); paymentDetail.CreatedDateTime = receivedDate; paymentDetail.CreatedByPersonAliasId = giverAliasId; paymentDetail.ModifiedDateTime = ImportDateTime; paymentDetail.ModifiedByPersonAliasId = giverAliasId; paymentDetail.CurrencyTypeValueId = paymentCurrencyTypeId; paymentDetail.CreditCardTypeValueId = creditCardTypeId; paymentDetail.ForeignKey = contributionId.ToString(); paymentDetail.ForeignId = contributionId; transaction.FinancialPaymentDetail = paymentDetail; } string checkNumber = row[CheckNumber] as string; // if the check number is valid, put it in the transaction code if ( checkNumber.AsType<int?>() != null ) { transaction.TransactionCode = checkNumber; } // check for SecureGive kiosk transactions else if ( !string.IsNullOrEmpty( checkNumber ) && checkNumber.StartsWith( "SG" ) ) { transaction.SourceTypeValueId = sourceTypeKiosk; } string fundName = row[FundName] as string; string subFund = row[SubFundName] as string; string fundGLAccount = row[FundGLAccount] as string; string subFundGLAccount = row[SubFundGLAccount] as string; string isFundActiveKey = row[FundIsActive]; Boolean? isFundActive = isFundActiveKey.AsType<Boolean?>(); string isSubFundActiveKey = row[SubFundIsActive]; Boolean? isSubFundActive = isSubFundActiveKey.AsType<Boolean?>(); string statedValueKey = row[StatedValue]; decimal? statedValue = statedValueKey.AsType<decimal?>(); string amountKey = row[Amount]; decimal? amount = amountKey.AsType<decimal?>(); if ( !String.IsNullOrWhiteSpace( fundName ) & amount != null ) { int transactionAccountId; var parentAccount = accountList.FirstOrDefault( a => a.Name.Equals( fundName.Truncate( 50 ) ) && a.CampusId == null ); if ( parentAccount == null ) { parentAccount = AddAccount( lookupContext, fundName, fundGLAccount, null, null, isFundActive ); accountList.Add( parentAccount ); } if ( !String.IsNullOrWhiteSpace( subFund ) ) { int? campusFundId = null; // assign a campus if the subfund is a campus fund var campusFund = CampusList.FirstOrDefault( c => subFund.StartsWith( c.Name ) || subFund.StartsWith( c.ShortCode ) ); if ( campusFund != null ) { // use full campus name as the subfund subFund = campusFund.Name; campusFundId = campusFund.Id; } // add info to easily find/assign this fund in the view subFund = string.Format( "{0} {1}", subFund, fundName ); var childAccount = accountList.FirstOrDefault( c => c.Name.Equals( subFund.Truncate( 50 ) ) && c.ParentAccountId == parentAccount.Id ); if ( childAccount == null ) { // create a child account with a campusId if it was set childAccount = AddAccount( lookupContext, subFund, subFundGLAccount, campusFundId, parentAccount.Id, isSubFundActive ); accountList.Add( childAccount ); } transactionAccountId = childAccount.Id; } else { transactionAccountId = parentAccount.Id; } if ( amount == 0 && statedValue != null && statedValue != 0 ) { amount = statedValue; } var transactionDetail = new FinancialTransactionDetail(); transactionDetail.Amount = ( decimal )amount; transactionDetail.CreatedDateTime = receivedDate; transactionDetail.AccountId = transactionAccountId; transaction.TransactionDetails.Add( transactionDetail ); if ( amount < 0 ) { transaction.RefundDetails = new FinancialTransactionRefund(); transaction.RefundDetails.CreatedDateTime = receivedDate; transaction.RefundDetails.RefundReasonValueId = refundReasons.Where( dv => summary != null && dv.Value.Contains( summary ) ) .Select( dv => ( int? )dv.Id ).FirstOrDefault(); transaction.RefundDetails.RefundReasonSummary = summary; } } newTransactions.Add( transaction ); completed++; if ( completed % (ReportingNumber * 10) < 1 ) { ReportProgress( 0, string.Format( "{0:N0} contributions imported.", completed ) ); } else if ( completed % ReportingNumber < 1 ) { SaveContributions( newTransactions ); newTransactions.Clear(); ReportPartialProgress(); } } } if ( newTransactions.Any() ) { SaveContributions( newTransactions ); } ReportProgress( 100, string.Format( "Finished contribution import: {0:N0} contributions imported.", completed ) ); return completed; }
/// <summary> /// Loads the family data. /// </summary> /// <param name="csvData">The CSV data.</param> private int LoadMetrics(CSVInstance csvData) { // Required variables var lookupContext = new RockContext(); var metricService = new MetricService(lookupContext); var metricCategoryService = new MetricCategoryService(lookupContext); var categoryService = new CategoryService(lookupContext); var metricSourceTypes = DefinedTypeCache.Read(new Guid(Rock.SystemGuid.DefinedType.METRIC_SOURCE_TYPE)).DefinedValues; var metricManualSource = metricSourceTypes.FirstOrDefault(m => m.Guid == new Guid(Rock.SystemGuid.DefinedValue.METRIC_SOURCE_VALUE_TYPE_MANUAL)); var scheduleService = new ScheduleService(lookupContext); var scheduleMetrics = scheduleService.Queryable().AsNoTracking() .Where(s => s.Category.Guid == new Guid(Rock.SystemGuid.Category.SCHEDULE_SERVICE_TIMES)).ToList(); var scheduleCategoryId = categoryService.Queryable().AsNoTracking() .Where(c => c.Guid == new Guid(Rock.SystemGuid.Category.SCHEDULE_SERVICE_TIMES)).FirstOrDefault().Id; var metricEntityTypeId = EntityTypeCache.Read <MetricCategory>(false, lookupContext).Id; var campusEntityTypeId = EntityTypeCache.Read <Campus>(false, lookupContext).Id; var scheduleEntityTypeId = EntityTypeCache.Read <Schedule>(false, lookupContext).Id; var allMetrics = metricService.Queryable().AsNoTracking().ToList(); var metricCategories = categoryService.Queryable().AsNoTracking() .Where(c => c.EntityType.Guid == new Guid(Rock.SystemGuid.EntityType.METRICCATEGORY)).ToList(); var defaultMetricCategory = metricCategories.FirstOrDefault(c => c.Name == "Metrics"); if (defaultMetricCategory == null) { defaultMetricCategory = new Category(); defaultMetricCategory.Name = "Metrics"; defaultMetricCategory.IsSystem = false; defaultMetricCategory.EntityTypeId = metricEntityTypeId; defaultMetricCategory.EntityTypeQualifierColumn = string.Empty; defaultMetricCategory.EntityTypeQualifierValue = string.Empty; defaultMetricCategory.IconCssClass = string.Empty; defaultMetricCategory.Description = string.Empty; lookupContext.Categories.Add(defaultMetricCategory); lookupContext.SaveChanges(); metricCategories.Add(defaultMetricCategory); } var metricValues = new List <MetricValue>(); Metric currentMetric = null; int completed = 0; ReportProgress(0, string.Format("Starting metrics import ({0:N0} already exist).", 0)); string[] row; // Uses a look-ahead enumerator: this call will move to the next record immediately while ((row = csvData.Database.FirstOrDefault()) != null) { string metricCampus = row[MetricCampus]; string metricName = row[MetricName]; string metricCategoryString = row[MetricCategory]; string metricNote = row[MetricNote]; if (!string.IsNullOrEmpty(metricName)) { decimal? value = row[MetricValue].AsDecimalOrNull(); DateTime?valueDate = row[MetricService].AsDateTime(); var metricCategoryId = defaultMetricCategory.Id; // create the category if it doesn't exist Category newMetricCategory = null; if (!string.IsNullOrEmpty(metricCategoryString)) { newMetricCategory = metricCategories.FirstOrDefault(c => c.Name == metricCategoryString); if (newMetricCategory == null) { newMetricCategory = new Category(); newMetricCategory.Name = metricCategoryString; newMetricCategory.IsSystem = false; newMetricCategory.EntityTypeId = metricEntityTypeId; newMetricCategory.EntityTypeQualifierColumn = string.Empty; newMetricCategory.EntityTypeQualifierValue = string.Empty; newMetricCategory.IconCssClass = string.Empty; newMetricCategory.Description = string.Empty; lookupContext.Categories.Add(newMetricCategory); lookupContext.SaveChanges(); metricCategories.Add(newMetricCategory); } metricCategoryId = newMetricCategory.Id; } // create metric if it doesn't exist currentMetric = allMetrics.FirstOrDefault(m => m.Title == metricName && m.MetricCategories.Any(c => c.CategoryId == metricCategoryId)); if (currentMetric == null) { currentMetric = new Metric(); currentMetric.Title = metricName; currentMetric.IsSystem = false; currentMetric.IsCumulative = false; currentMetric.SourceSql = string.Empty; currentMetric.Subtitle = string.Empty; currentMetric.Description = string.Empty; currentMetric.IconCssClass = string.Empty; currentMetric.SourceValueTypeId = metricManualSource.Id; currentMetric.CreatedByPersonAliasId = ImportPersonAliasId; currentMetric.CreatedDateTime = ImportDateTime; currentMetric.ForeignKey = string.Format("Metric imported {0}", ImportDateTime); currentMetric.MetricPartitions = new List <MetricPartition>(); currentMetric.MetricPartitions.Add(new MetricPartition { Label = "Campus", EntityTypeId = campusEntityTypeId, Metric = currentMetric }); currentMetric.MetricPartitions.Add(new MetricPartition { Label = "Service", EntityTypeId = scheduleEntityTypeId, Metric = currentMetric }); metricService.Add(currentMetric); lookupContext.SaveChanges(); if (currentMetric.MetricCategories == null || !currentMetric.MetricCategories.Any(a => a.CategoryId == metricCategoryId)) { metricCategoryService.Add(new MetricCategory { CategoryId = metricCategoryId, MetricId = currentMetric.Id }); lookupContext.SaveChanges(); } allMetrics.Add(currentMetric); } // create values for this metric var metricValue = new MetricValue(); metricValue.MetricValueType = MetricValueType.Measure; metricValue.CreatedByPersonAliasId = ImportPersonAliasId; metricValue.CreatedDateTime = ImportDateTime; metricValue.MetricValueDateTime = valueDate; metricValue.MetricId = currentMetric.Id; metricValue.Note = string.Empty; metricValue.XValue = string.Empty; metricValue.YValue = value; metricValue.ForeignKey = string.Format("Metric Value imported {0}", ImportDateTime); metricValue.Note = metricNote; if (valueDate.HasValue) { var metricPartitionScheduleId = currentMetric.MetricPartitions.FirstOrDefault(p => p.Label == "Service").Id; var date = (DateTime)valueDate; var scheduleName = date.DayOfWeek.ToString(); if (date.TimeOfDay.TotalSeconds > 0) { scheduleName = scheduleName + string.Format(" {0}", date.ToString("hh:mm")) + string.Format("{0}", date.ToString("tt").ToLower()); } if (!scheduleMetrics.Any(s => s.Name == scheduleName)) { Schedule newSchedule = new Schedule(); newSchedule.Name = scheduleName; newSchedule.CategoryId = scheduleCategoryId; newSchedule.CreatedByPersonAliasId = ImportPersonAliasId; newSchedule.CreatedDateTime = ImportDateTime; newSchedule.ForeignKey = string.Format("Metric Schedule imported {0}", ImportDateTime); scheduleMetrics.Add(newSchedule); lookupContext.Schedules.Add(newSchedule); lookupContext.SaveChanges(); } var scheduleId = scheduleMetrics.FirstOrDefault(s => s.Name == scheduleName).Id; metricValue.MetricValuePartitions.Add(new MetricValuePartition { MetricPartitionId = metricPartitionScheduleId, EntityId = scheduleId }); } if (!string.IsNullOrWhiteSpace(metricCampus)) { var campus = CampusList.Where(c => c.Name.Equals(metricCampus, StringComparison.InvariantCultureIgnoreCase) || c.ShortCode.Equals(metricCampus, StringComparison.InvariantCultureIgnoreCase)).FirstOrDefault(); if (campus == null) { var newCampus = new Campus(); newCampus.IsSystem = false; newCampus.Name = metricCampus; newCampus.ShortCode = metricCampus.RemoveWhitespace(); newCampus.IsActive = true; lookupContext.Campuses.Add(newCampus); lookupContext.SaveChanges(DisableAuditing); CampusList.Add(newCampus); campus = newCampus; } if (campus != null) { var metricPartitionCampusId = currentMetric.MetricPartitions.FirstOrDefault(p => p.Label == "Campus").Id; metricValue.MetricValuePartitions.Add(new MetricValuePartition { MetricPartitionId = metricPartitionCampusId, EntityId = campus.Id }); } } metricValues.Add(metricValue); completed++; if (completed % (ReportingNumber * 10) < 1) { ReportProgress(0, string.Format("{0:N0} metrics imported.", completed)); } else if (completed % ReportingNumber < 1) { SaveMetrics(metricValues); ReportPartialProgress(); // Reset lookup context lookupContext = new RockContext(); metricValues.Clear(); } } } // Check to see if any rows didn't get saved to the database if (metricValues.Any()) { SaveMetrics(metricValues); } ReportProgress(0, string.Format("Finished metrics import: {0:N0} metrics added or updated.", completed)); return(completed); }
/// <summary> /// Maps the pledge. /// </summary> /// <param name="csvData">todo: describe csvData parameter on MapPledge</param> /// <returns></returns> /// <exception cref="System.NotImplementedException"></exception> private int MapPledge(CSVInstance csvData) { var lookupContext = new RockContext(); var accountList = new FinancialAccountService(lookupContext).Queryable().AsNoTracking().ToList(); var importedPledges = new FinancialPledgeService(lookupContext).Queryable().AsNoTracking() .Where(p => p.ForeignId != null) .ToDictionary(t => (int)t.ForeignId, t => (int?)t.Id); var pledgeFrequencies = DefinedTypeCache.Read(new Guid(Rock.SystemGuid.DefinedType.FINANCIAL_FREQUENCY), lookupContext).DefinedValues; var oneTimePledgeFrequencyId = pledgeFrequencies.FirstOrDefault(f => f.Guid == new Guid(Rock.SystemGuid.DefinedValue.TRANSACTION_FREQUENCY_ONE_TIME)).Id; var newPledges = new List <FinancialPledge>(); var completed = 0; ReportProgress(0, $"Verifying pledge import ({importedPledges.Count:N0} already exist)."); string[] row; // Uses a look-ahead enumerator: this call will move to the next record immediately while ((row = csvData.Database.FirstOrDefault()) != null) { var amountKey = row[TotalPledge]; var amount = amountKey.AsType <decimal?>(); var startDateKey = row[StartDate]; if (string.IsNullOrWhiteSpace(startDateKey)) { startDateKey = "01/01/0001"; } var startDate = startDateKey.AsType <DateTime?>(); var endDateKey = row[EndDate]; if (string.IsNullOrWhiteSpace(endDateKey)) { endDateKey = "12/31/9999"; } var endDate = endDateKey.AsType <DateTime?>(); var createdDateKey = row[PledgeCreatedDate]; if (string.IsNullOrWhiteSpace(createdDateKey)) { createdDateKey = ImportDateTime.ToString(); } var createdDate = createdDateKey.AsType <DateTime?>(); var modifiedDateKey = row[PledgeModifiedDate]; if (string.IsNullOrWhiteSpace(modifiedDateKey)) { modifiedDateKey = ImportDateTime.ToString(); } var modifiedDate = modifiedDateKey.AsType <DateTime?>(); var pledgeIdKey = row[PledgeId]; var pledgeId = pledgeIdKey.AsType <int?>(); if (amount != null && !importedPledges.ContainsKey((int)pledgeId)) { var individualIdKey = row[IndividualID]; var personKeys = GetPersonKeys(individualIdKey); if (personKeys != null && personKeys.PersonAliasId > 0) { var pledge = new FinancialPledge { PersonAliasId = personKeys.PersonAliasId, CreatedByPersonAliasId = ImportPersonAliasId, StartDate = (DateTime)startDate, EndDate = (DateTime)endDate, TotalAmount = (decimal)amount, CreatedDateTime = createdDate, ModifiedDateTime = modifiedDate, ModifiedByPersonAliasId = ImportPersonAliasId, ForeignKey = pledgeIdKey, ForeignId = pledgeId }; var frequency = row[PledgeFrequencyName].ToString().ToLower(); if (!string.IsNullOrWhiteSpace(frequency)) { frequency = frequency.ToLower(); if (frequency.Equals("one time") || frequency.Equals("one-time") || frequency.Equals("as can")) { pledge.PledgeFrequencyValueId = oneTimePledgeFrequencyId; } else { pledge.PledgeFrequencyValueId = pledgeFrequencies .Where(f => f.Value.ToLower().StartsWith(frequency) || f.Description.ToLower().StartsWith(frequency)) .Select(f => f.Id).FirstOrDefault(); } } var fundName = row[FundName] as string; var subFund = row[SubFundName] as string; var fundGLAccount = row[FundGLAccount] as string; var subFundGLAccount = row[SubFundGLAccount] as string; var isFundActiveKey = row[FundIsActive]; var isFundActive = isFundActiveKey.AsType <bool?>(); var isSubFundActiveKey = row[SubFundIsActive]; var isSubFundActive = isSubFundActiveKey.AsType <bool?>(); if (!string.IsNullOrWhiteSpace(fundName)) { var parentAccount = accountList.FirstOrDefault(a => a.Name.Equals(fundName.Truncate(50))); if (parentAccount == null) { parentAccount = AddAccount(lookupContext, fundName, string.Empty, null, null, isFundActive, null, null, null, null, "", "", null); accountList.Add(parentAccount); } if (!string.IsNullOrWhiteSpace(subFund)) { int?campusFundId = null; // assign a campus if the subfund is a campus fund var campusFund = CampusList.FirstOrDefault(c => subFund.Contains(c.Name) || subFund.Contains(c.ShortCode)); if (campusFund != null) { campusFundId = campusFund.Id; } // add info to easily find/assign this fund in the view subFund = $"{fundName} {subFund}"; var childAccount = accountList.FirstOrDefault(c => c.Name.Equals(subFund.Truncate(50)) && c.ParentAccountId == parentAccount.Id); if (childAccount == null) { // create a child account with a campusId if it was set childAccount = AddAccount(lookupContext, subFund, string.Empty, campusFundId, parentAccount.Id, isSubFundActive, null, null, null, null, "", "", null); accountList.Add(childAccount); } pledge.AccountId = childAccount.Id; } else { pledge.AccountId = parentAccount.Id; } } newPledges.Add(pledge); completed++; if (completed % (ReportingNumber * 10) < 1) { ReportProgress(0, $"{completed:N0} pledges imported."); } else if (completed % ReportingNumber < 1) { SavePledges(newPledges); ReportPartialProgress(); newPledges.Clear(); } } } } if (newPledges.Any()) { SavePledges(newPledges); } ReportProgress(100, $"Finished pledge import: {completed:N0} pledges imported."); return(completed); }
/// <summary> /// Loads the family data. /// </summary> /// <param name="csvData">The CSV data.</param> private int LoadMetrics(CSVInstance csvData) { // Required variables var lookupContext = new RockContext(); var metricService = new MetricService(lookupContext); var metricCategoryService = new MetricCategoryService(lookupContext); var categoryService = new CategoryService(lookupContext); var metricSourceTypes = DefinedTypeCache.Read(new Guid(Rock.SystemGuid.DefinedType.METRIC_SOURCE_TYPE)).DefinedValues; var metricManualSource = metricSourceTypes.FirstOrDefault(m => m.Guid == new Guid(Rock.SystemGuid.DefinedValue.METRIC_SOURCE_VALUE_TYPE_MANUAL)); var metricEntityTypeId = EntityTypeCache.Read <Rock.Model.MetricCategory>(false, lookupContext).Id; var campusEntityTypeId = EntityTypeCache.Read <Rock.Model.Campus>(false, lookupContext).Id; var campuses = CampusCache.All(); var allMetrics = metricService.Queryable().AsNoTracking().ToList(); var metricCategories = categoryService.Queryable().AsNoTracking() .Where(c => c.EntityType.Guid == new Guid(Rock.SystemGuid.EntityType.METRICCATEGORY)).ToList(); var defaultMetricCategory = metricCategories.FirstOrDefault(c => c.Name == "Metrics"); if (defaultMetricCategory == null) { defaultMetricCategory = new Category(); defaultMetricCategory.Name = "Metrics"; defaultMetricCategory.IsSystem = false; defaultMetricCategory.EntityTypeId = metricEntityTypeId; defaultMetricCategory.EntityTypeQualifierColumn = string.Empty; defaultMetricCategory.EntityTypeQualifierValue = string.Empty; defaultMetricCategory.IconCssClass = string.Empty; defaultMetricCategory.Description = string.Empty; lookupContext.Categories.Add(defaultMetricCategory); lookupContext.SaveChanges(); metricCategories.Add(defaultMetricCategory); } var metricValues = new List <MetricValue>(); Metric currentMetric = null; int completed = 0; ReportProgress(0, string.Format("Starting metrics import ({0:N0} already exist).", 0)); string[] row; // Uses a look-ahead enumerator: this call will move to the next record immediately while ((row = csvData.Database.FirstOrDefault()) != null) { string metricCampus = row[MetricCampus]; string metricName = row[MetricName]; string metricCategoryString = row[MetricCategory]; if (!string.IsNullOrEmpty(metricName)) { decimal? value = row[MetricValue].AsDecimalOrNull(); DateTime?valueDate = row[MetricService].AsDateTime(); var metricCategoryId = defaultMetricCategory.Id; // create the category if it doesn't exist Category newMetricCategory = null; if (!string.IsNullOrEmpty(metricCategoryString)) { newMetricCategory = metricCategories.FirstOrDefault(c => c.Name == metricCategoryString); if (newMetricCategory == null) { newMetricCategory = new Category(); newMetricCategory.Name = metricCategoryString; newMetricCategory.IsSystem = false; newMetricCategory.EntityTypeId = metricEntityTypeId; newMetricCategory.EntityTypeQualifierColumn = string.Empty; newMetricCategory.EntityTypeQualifierValue = string.Empty; newMetricCategory.IconCssClass = string.Empty; newMetricCategory.Description = string.Empty; lookupContext.Categories.Add(newMetricCategory); lookupContext.SaveChanges(); metricCategories.Add(newMetricCategory); } metricCategoryId = newMetricCategory.Id; } if (valueDate.HasValue) { var timeFrame = (DateTime)valueDate; if (timeFrame.TimeOfDay.TotalSeconds > 0) { metricName = string.Format("{0} {1}", timeFrame.ToString("HH:mm"), metricName); } } // create metric if it doesn't exist currentMetric = allMetrics.FirstOrDefault(m => m.Title == metricName && m.MetricCategories.Any(c => c.CategoryId == metricCategoryId)); if (currentMetric == null) { currentMetric = new Metric(); currentMetric.Title = metricName; currentMetric.IsSystem = false; currentMetric.IsCumulative = false; currentMetric.SourceSql = string.Empty; currentMetric.Subtitle = string.Empty; currentMetric.Description = string.Empty; currentMetric.IconCssClass = string.Empty; currentMetric.SourceValueTypeId = metricManualSource.Id; currentMetric.CreatedByPersonAliasId = ImportPersonAliasId; currentMetric.CreatedDateTime = ImportDateTime; metricService.Add(currentMetric); lookupContext.SaveChanges(); if (currentMetric.MetricCategories == null || !currentMetric.MetricCategories.Any(a => a.CategoryId == metricCategoryId)) { metricCategoryService.Add(new MetricCategory { CategoryId = metricCategoryId, MetricId = currentMetric.Id }); lookupContext.SaveChanges(); } allMetrics.Add(currentMetric); } var campusId = campuses.Where(c => c.Name == metricCampus || c.ShortCode == metricCampus) .Select(c => (int?)c.Id).FirstOrDefault(); // create values for this metric var metricValue = new MetricValue(); metricValue.MetricValueType = MetricValueType.Measure; metricValue.CreatedByPersonAliasId = ImportPersonAliasId; metricValue.CreatedDateTime = ImportDateTime; metricValue.MetricValueDateTime = valueDate; metricValue.MetricId = currentMetric.Id; metricValue.Note = string.Empty; metricValue.XValue = string.Empty; metricValue.YValue = value; metricValues.Add(metricValue); completed++; if (completed % (ReportingNumber * 10) < 1) { ReportProgress(0, string.Format("{0:N0} metrics imported.", completed)); } else if (completed % ReportingNumber < 1) { SaveMetrics(metricValues); ReportPartialProgress(); // Reset lookup context lookupContext = new RockContext(); metricValues.Clear(); } } } // Check to see if any rows didn't get saved to the database if (metricValues.Any()) { SaveMetrics(metricValues); } ReportProgress(0, string.Format("Finished metrics import: {0:N0} metrics added or updated.", completed)); return(completed); }
/// <summary> /// Loads the family data. /// </summary> /// <param name="csvData">The CSV data.</param> private int LoadMetrics( CSVInstance csvData ) { // Required variables var lookupContext = new RockContext(); var metricService = new MetricService( lookupContext ); var categoryService = new CategoryService( lookupContext ); var metricSourceTypes = DefinedTypeCache.Read( new Guid( Rock.SystemGuid.DefinedType.METRIC_SOURCE_TYPE ) ).DefinedValues; var metricManualSource = metricSourceTypes.FirstOrDefault( m => m.Guid == new Guid( Rock.SystemGuid.DefinedValue.METRIC_SOURCE_VALUE_TYPE_MANUAL ) ); var metricEntityTypeId = EntityTypeCache.Read<Rock.Model.MetricCategory>( false, lookupContext ).Id; var campusEntityTypeId = EntityTypeCache.Read<Rock.Model.Campus>( false, lookupContext ).Id; var campuses = CampusCache.All(); var allMetrics = metricService.Queryable().AsNoTracking().ToList(); var metricCategories = categoryService.Queryable().AsNoTracking() .Where( c => c.EntityType.Guid == new Guid( Rock.SystemGuid.EntityType.METRICCATEGORY ) ).ToList(); var defaultMetricCategory = metricCategories.FirstOrDefault( c => c.Name == "Metrics" ); if ( defaultMetricCategory == null ) { defaultMetricCategory = new Category(); defaultMetricCategory.Name = "Metrics"; defaultMetricCategory.IsSystem = false; defaultMetricCategory.EntityTypeId = metricEntityTypeId; defaultMetricCategory.EntityTypeQualifierColumn = string.Empty; defaultMetricCategory.EntityTypeQualifierValue = string.Empty; lookupContext.Categories.Add( defaultMetricCategory ); lookupContext.SaveChanges(); metricCategories.Add( defaultMetricCategory ); } var metricValues = new List<MetricValue>(); Metric currentMetric = null; int completed = 0; ReportProgress( 0, string.Format( "Starting metrics import ({0:N0} already exist).", 0 ) ); string[] row; // Uses a look-ahead enumerator: this call will move to the next record immediately while ( (row = csvData.Database.FirstOrDefault()) != null ) { string metricCampus = row[MetricCampus]; string metricName = row[MetricName]; string metricCategory = row[MetricCategory]; if ( !string.IsNullOrEmpty( metricName ) ) { decimal? value = row[MetricValue].AsDecimalOrNull(); DateTime? valueDate = row[MetricService].AsDateTime(); var metricCategoryId = defaultMetricCategory.Id; // create the category if it doesn't exist Category newMetricCategory = null; if ( !string.IsNullOrEmpty( metricCategory ) ) { newMetricCategory = metricCategories.FirstOrDefault( c => c.Name == metricCategory ); if ( newMetricCategory == null ) { newMetricCategory = new Category(); newMetricCategory.Name = metricCategory; newMetricCategory.IsSystem = false; newMetricCategory.EntityTypeId = metricEntityTypeId; newMetricCategory.EntityTypeQualifierColumn = string.Empty; newMetricCategory.EntityTypeQualifierValue = string.Empty; lookupContext.Categories.Add( newMetricCategory ); lookupContext.SaveChanges(); metricCategories.Add( newMetricCategory ); } metricCategoryId = newMetricCategory.Id; } if ( valueDate.HasValue ) { var timeFrame = ( DateTime )valueDate; if ( timeFrame.TimeOfDay.TotalSeconds > 0 ) { metricName = string.Format( "{0} {1}", timeFrame.ToString( "HH:mm" ), metricName ); } } // create metric if it doesn't exist currentMetric = allMetrics.FirstOrDefault( m => m.Title == metricName && m.MetricCategories.Any( c => c.CategoryId == metricCategoryId ) ); if ( currentMetric == null ) { currentMetric = new Metric(); currentMetric.Title = metricName; currentMetric.IsSystem = false; currentMetric.IsCumulative = false; currentMetric.SourceSql = string.Empty; currentMetric.Subtitle = string.Empty; currentMetric.Description = string.Empty; currentMetric.IconCssClass = string.Empty; currentMetric.EntityTypeId = campusEntityTypeId; currentMetric.SourceValueTypeId = metricManualSource.Id; currentMetric.CreatedByPersonAliasId = ImportPersonAliasId; currentMetric.CreatedDateTime = ImportDateTime; currentMetric.MetricCategories.Add( new MetricCategory { CategoryId = metricCategoryId } ); lookupContext.Metrics.Add( currentMetric ); lookupContext.SaveChanges(); allMetrics.Add( currentMetric ); } var campusId = campuses.Where( c => c.Name == metricCampus || c.ShortCode == metricCampus ) .Select( c => ( int? )c.Id ).FirstOrDefault(); // create values for this metric var metricValue = new MetricValue(); metricValue.MetricValueType = MetricValueType.Measure; metricValue.CreatedByPersonAliasId = ImportPersonAliasId; metricValue.CreatedDateTime = ImportDateTime; metricValue.MetricValueDateTime = valueDate; metricValue.MetricId = currentMetric.Id; metricValue.EntityId = campusId; metricValue.Note = string.Empty; metricValue.XValue = string.Empty; metricValue.YValue = value; metricValues.Add( metricValue ); completed++; if ( completed % (ReportingNumber * 10) < 1 ) { ReportProgress( 0, string.Format( "{0:N0} metrics imported.", completed ) ); } else if ( completed % ReportingNumber < 1 ) { SaveMetrics( metricValues ); ReportPartialProgress(); // Reset lookup context lookupContext = new RockContext(); metricValues.Clear(); } } } // Check to see if any rows didn't get saved to the database if ( metricValues.Any() ) { SaveMetrics( metricValues ); } ReportProgress( 0, string.Format( "Finished metrics import: {0:N0} metrics added or updated.", completed ) ); return completed; }
/// <summary> /// Maps the contribution. /// </summary> /// <param name="csvData">The table data.</param> private int MapContribution(CSVInstance csvData) { var lookupContext = new RockContext(); var currencyTypes = DefinedTypeCache.Read(new Guid(Rock.SystemGuid.DefinedType.FINANCIAL_CURRENCY_TYPE)); var currencyTypeACH = currencyTypes.DefinedValues.FirstOrDefault(dv => dv.Guid.Equals(new Guid(Rock.SystemGuid.DefinedValue.CURRENCY_TYPE_ACH))).Id; var currencyTypeCash = currencyTypes.DefinedValues.FirstOrDefault(dv => dv.Guid.Equals(new Guid(Rock.SystemGuid.DefinedValue.CURRENCY_TYPE_CASH))).Id; var currencyTypeCheck = currencyTypes.DefinedValues.FirstOrDefault(dv => dv.Guid.Equals(new Guid(Rock.SystemGuid.DefinedValue.CURRENCY_TYPE_CHECK))).Id; var currencyTypeCreditCard = currencyTypes.DefinedValues.FirstOrDefault(dv => dv.Guid.Equals(new Guid(Rock.SystemGuid.DefinedValue.CURRENCY_TYPE_CREDIT_CARD))).Id; var currencyTypeNonCash = currencyTypes.DefinedValues.Where(dv => dv.Value.Equals("Non-Cash")).Select(dv => (int?)dv.Id).FirstOrDefault(); if (currencyTypeNonCash == null) { var newTenderNonCash = new DefinedValue { Value = "Non-Cash", Description = "Non-Cash", DefinedTypeId = currencyTypes.Id }; lookupContext.DefinedValues.Add(newTenderNonCash); lookupContext.SaveChanges(); currencyTypeNonCash = newTenderNonCash.Id; } var creditCardTypes = DefinedTypeCache.Read(new Guid(Rock.SystemGuid.DefinedType.FINANCIAL_CREDIT_CARD_TYPE)).DefinedValues; var sourceTypeOnsite = DefinedValueCache.Read(new Guid(Rock.SystemGuid.DefinedValue.FINANCIAL_SOURCE_TYPE_ONSITE_COLLECTION), lookupContext).Id; var sourceTypeWebsite = DefinedValueCache.Read(new Guid(Rock.SystemGuid.DefinedValue.FINANCIAL_SOURCE_TYPE_WEBSITE), lookupContext).Id; var sourceTypeKiosk = DefinedValueCache.Read(new Guid(Rock.SystemGuid.DefinedValue.FINANCIAL_SOURCE_TYPE_KIOSK), lookupContext).Id; var refundReasons = DefinedTypeCache.Read(new Guid(Rock.SystemGuid.DefinedType.FINANCIAL_TRANSACTION_REFUND_REASON), lookupContext).DefinedValues; var accountList = new FinancialAccountService(lookupContext).Queryable().AsNoTracking().ToList(); int?defaultBatchId = null; if (ImportedBatches.ContainsKey(0)) { defaultBatchId = ImportedBatches[0]; } // Look for custom attributes in the Contribution file var allFields = csvData.TableNodes.FirstOrDefault().Children.Select((node, index) => new { node = node, index = index }).ToList(); var customAttributes = allFields .Where(f => f.index > ContributionCreditCardType) .ToDictionary(f => f.index, f => f.node.Name); // Get all imported contributions var importedContributions = new FinancialTransactionService(lookupContext).Queryable().AsNoTracking() .Where(c => c.ForeignId != null) .Select(t => (int)t.ForeignId) .OrderBy(t => t).ToList(); // List for batching new contributions var newTransactions = new List <FinancialTransaction>(); var completed = 0; ReportProgress(0, $"Verifying contribution import ({importedContributions.Count:N0} already exist)."); string[] row; // Uses a look-ahead enumerator: this call will move to the next record immediately while ((row = csvData.Database.FirstOrDefault()) != null) { var individualIdKey = row[IndividualID]; var contributionIdKey = row[ContributionID]; var contributionId = contributionIdKey.AsType <int?>(); if (contributionId != null && !importedContributions.Contains((int)contributionId)) { var transaction = new FinancialTransaction { CreatedByPersonAliasId = ImportPersonAliasId, ModifiedByPersonAliasId = ImportPersonAliasId, TransactionTypeValueId = TransactionTypeContributionId, ForeignKey = contributionId.ToString(), ForeignId = contributionId }; int?giverAliasId = null; var personKeys = GetPersonKeys(individualIdKey); if (personKeys != null && personKeys.PersonAliasId > 0) { giverAliasId = personKeys.PersonAliasId; transaction.CreatedByPersonAliasId = giverAliasId; transaction.AuthorizedPersonAliasId = giverAliasId; transaction.ProcessedByPersonAliasId = giverAliasId; } else if (AnonymousGiverAliasId != null && AnonymousGiverAliasId > 0) { giverAliasId = AnonymousGiverAliasId; transaction.AuthorizedPersonAliasId = giverAliasId; transaction.ProcessedByPersonAliasId = giverAliasId; } var summary = row[Memo] as string; if (!string.IsNullOrWhiteSpace(summary)) { transaction.Summary = summary; } var batchIdKey = row[ContributionBatchID]; var batchId = batchIdKey.AsType <int?>(); if (batchId != null && ImportedBatches.Any(b => b.Key.Equals(batchId))) { transaction.BatchId = ImportedBatches.FirstOrDefault(b => b.Key.Equals(batchId)).Value; } else { // use the default batch for any non-matching transactions transaction.BatchId = defaultBatchId; } var receivedDateKey = row[ReceivedDate]; var receivedDate = receivedDateKey.AsType <DateTime?>(); if (receivedDate != null) { transaction.TransactionDateTime = receivedDate; transaction.CreatedDateTime = receivedDate; transaction.ModifiedDateTime = ImportDateTime; } var contributionType = row[ContributionTypeName]; var creditCardType = row[ContributionCreditCardType]; if (!string.IsNullOrWhiteSpace(contributionType)) { // set default source to onsite, exceptions listed below transaction.SourceTypeValueId = sourceTypeOnsite; int?paymentCurrencyTypeId = null, creditCardTypeId = null; if (contributionType.Equals("cash", StringComparison.CurrentCultureIgnoreCase)) { paymentCurrencyTypeId = currencyTypeCash; } else if (contributionType.Equals("check", StringComparison.CurrentCultureIgnoreCase)) { paymentCurrencyTypeId = currencyTypeCheck; } else if (contributionType.Equals("ach", StringComparison.CurrentCultureIgnoreCase)) { paymentCurrencyTypeId = currencyTypeACH; transaction.SourceTypeValueId = sourceTypeWebsite; } else if (contributionType.Equals("credit card", StringComparison.CurrentCultureIgnoreCase)) { paymentCurrencyTypeId = currencyTypeCreditCard; transaction.SourceTypeValueId = sourceTypeWebsite; // Determine CC Type if (!string.IsNullOrWhiteSpace(creditCardType)) { creditCardTypeId = creditCardTypes.Where(c => c.Value.StartsWith(creditCardType, StringComparison.CurrentCultureIgnoreCase) || c.Description.StartsWith(creditCardType, StringComparison.CurrentCultureIgnoreCase)) .Select(c => c.Id).FirstOrDefault(); } } else { paymentCurrencyTypeId = currencyTypeNonCash; } var paymentDetail = new FinancialPaymentDetail { CreatedDateTime = receivedDate, CreatedByPersonAliasId = giverAliasId, ModifiedDateTime = ImportDateTime, ModifiedByPersonAliasId = giverAliasId, CurrencyTypeValueId = paymentCurrencyTypeId, CreditCardTypeValueId = creditCardTypeId, ForeignKey = contributionId.ToString(), ForeignId = contributionId }; transaction.FinancialPaymentDetail = paymentDetail; } var transactionCode = row[CheckNumber] as string; // if transaction code provided, put it in the transaction code if (!string.IsNullOrEmpty(transactionCode)) { transaction.TransactionCode = transactionCode; // check for SecureGive kiosk transactions if (transactionCode.StartsWith("SG")) { transaction.SourceTypeValueId = sourceTypeKiosk; } } var fundName = row[FundName] as string; var subFund = row[SubFundName] as string; var fundGLAccount = row[FundGLAccount] as string; var subFundGLAccount = row[SubFundGLAccount] as string; var isFundActiveKey = row[FundIsActive]; var isFundActive = isFundActiveKey.AsType <bool?>(); var isSubFundActiveKey = row[SubFundIsActive]; var isSubFundActive = isSubFundActiveKey.AsType <bool?>(); var statedValueKey = row[StatedValue]; var statedValue = statedValueKey.AsType <decimal?>(); var amountKey = row[Amount]; var amount = amountKey.AsType <decimal?>(); if (!string.IsNullOrWhiteSpace(fundName) & amount != null) { int transactionAccountId; var parentAccount = accountList.FirstOrDefault(a => a.Name.Equals(fundName.Truncate(50))); if (parentAccount == null) { parentAccount = AddAccount(lookupContext, fundName, fundGLAccount, null, null, isFundActive, null, null, null, null, "", "", null); accountList.Add(parentAccount); } if (!string.IsNullOrWhiteSpace(subFund)) { int?campusFundId = null; // assign a campus if the subfund is a campus fund var campusFund = CampusList.FirstOrDefault(c => subFund.Contains(c.Name) || subFund.Contains(c.ShortCode)); if (campusFund != null) { campusFundId = campusFund.Id; } // add info to easily find/assign this fund in the view subFund = $"{fundName} {subFund}"; var childAccount = accountList.FirstOrDefault(c => c.Name.Equals(subFund.Truncate(50)) && c.ParentAccountId == parentAccount.Id); if (childAccount == null) { // create a child account with a campusId if it was set childAccount = AddAccount(lookupContext, subFund, subFundGLAccount, campusFundId, parentAccount.Id, isSubFundActive, null, null, null, null, "", "", null); accountList.Add(childAccount); } transactionAccountId = childAccount.Id; } else { transactionAccountId = parentAccount.Id; } if (amount == 0 && statedValue != null && statedValue != 0) { amount = statedValue; } var transactionDetail = new FinancialTransactionDetail { Amount = (decimal)amount, CreatedDateTime = receivedDate, AccountId = transactionAccountId }; transaction.TransactionDetails.Add(transactionDetail); if (amount < 0) { transaction.RefundDetails = new FinancialTransactionRefund(); transaction.RefundDetails.CreatedDateTime = receivedDate; transaction.RefundDetails.RefundReasonValueId = refundReasons.Where(dv => summary != null && dv.Value.Contains(summary)) .Select(dv => (int?)dv.Id).FirstOrDefault(); transaction.RefundDetails.RefundReasonSummary = summary; } } newTransactions.Add(transaction); completed++; if (completed % (ReportingNumber * 10) < 1) { ReportProgress(0, $"{completed:N0} contributions imported."); } else if (completed % ReportingNumber < 1) { SaveContributions(newTransactions); newTransactions.Clear(); ReportPartialProgress(); } } } if (newTransactions.Any()) { SaveContributions(newTransactions); } ReportProgress(100, $"Finished contribution import: {completed:N0} contributions imported."); return(completed); }
/// <summary> /// Loads the family data. /// </summary> /// <param name="csvData">The CSV data.</param> private int LoadFamily( CSVInstance csvData ) { // Required variables var lookupContext = new RockContext(); var locationService = new LocationService( lookupContext ); int familyGroupTypeId = GroupTypeCache.GetFamilyGroupType().Id; int numImportedFamilies = ImportedFamilies.Count(); int homeLocationTypeId = DefinedValueCache.Read( new Guid( Rock.SystemGuid.DefinedValue.GROUP_LOCATION_TYPE_HOME ) ).Id; int workLocationTypeId = DefinedValueCache.Read( new Guid( Rock.SystemGuid.DefinedValue.GROUP_LOCATION_TYPE_WORK ) ).Id; var newGroupLocations = new Dictionary<GroupLocation, string>(); var currentFamilyGroup = new Group(); var newFamilyList = new List<Group>(); var updatedFamilyList = new List<Group>(); var dateFormats = new[] { "yyyy-MM-dd", "MM/dd/yyyy", "MM/dd/yy" }; string currentFamilyKey = string.Empty; int completed = 0; ReportProgress( 0, string.Format( "Starting family import ({0:N0} already exist).", numImportedFamilies ) ); string[] row; // Uses a look-ahead enumerator: this call will move to the next record immediately while ( (row = csvData.Database.FirstOrDefault()) != null ) { string rowFamilyKey = row[FamilyId]; int? rowFamilyId = rowFamilyKey.AsType<int?>(); string rowFamilyName = row[FamilyName]; if ( rowFamilyKey != null && rowFamilyKey != currentFamilyGroup.ForeignKey ) { currentFamilyGroup = ImportedFamilies.FirstOrDefault( g => g.ForeignKey == rowFamilyKey ); if ( currentFamilyGroup == null ) { currentFamilyGroup = new Group(); currentFamilyGroup.ForeignKey = rowFamilyKey; currentFamilyGroup.ForeignId = rowFamilyId; currentFamilyGroup.Name = row[FamilyName]; currentFamilyGroup.CreatedByPersonAliasId = ImportPersonAliasId; currentFamilyGroup.GroupTypeId = familyGroupTypeId; newFamilyList.Add( currentFamilyGroup ); } else { lookupContext.Groups.Attach( currentFamilyGroup ); } // Set the family campus string campusName = row[Campus]; if ( !string.IsNullOrWhiteSpace( campusName ) ) { var familyCampus = CampusList.Where( c => c.Name.Equals( campusName, StringComparison.InvariantCultureIgnoreCase ) || c.ShortCode.Equals( campusName, StringComparison.InvariantCultureIgnoreCase ) ).FirstOrDefault(); if ( familyCampus == null ) { familyCampus = new Campus(); familyCampus.IsSystem = false; familyCampus.Name = campusName; familyCampus.ShortCode = campusName.RemoveWhitespace(); lookupContext.Campuses.Add( familyCampus ); lookupContext.SaveChanges( DisableAuditing ); CampusList.Add( familyCampus ); } currentFamilyGroup.CampusId = familyCampus.Id; } // Add the family addresses since they exist in this file string famAddress = row[Address]; string famAddress2 = row[Address2]; string famCity = row[City]; string famState = row[State]; string famZip = row[Zip]; string famCountry = row[Country]; Location primaryAddress = locationService.Get( famAddress, famAddress2, famCity, famState, famZip, famCountry, verifyLocation: false ); if ( primaryAddress != null ) { var primaryLocation = new GroupLocation(); primaryLocation.LocationId = primaryAddress.Id; primaryLocation.IsMailingLocation = true; primaryLocation.IsMappedLocation = true; primaryLocation.GroupLocationTypeValueId = homeLocationTypeId; newGroupLocations.Add( primaryLocation, rowFamilyKey ); } string famSecondAddress = row[SecondaryAddress]; string famSecondAddress2 = row[SecondaryAddress2]; string famSecondCity = row[SecondaryCity]; string famSecondState = row[SecondaryState]; string famSecondZip = row[SecondaryZip]; string famSecondCountry = row[SecondaryCountry]; Location secondaryAddress = locationService.Get( famSecondAddress, famSecondAddress2, famSecondCity, famSecondState, famSecondZip, famSecondCountry, verifyLocation: false ); if ( secondaryAddress != null ) { var secondaryLocation = new GroupLocation(); secondaryLocation.LocationId = secondaryAddress.Id; secondaryLocation.IsMailingLocation = true; secondaryLocation.IsMappedLocation = true; secondaryLocation.GroupLocationTypeValueId = workLocationTypeId; newGroupLocations.Add( secondaryLocation, rowFamilyKey ); } DateTime createdDateValue; if ( DateTime.TryParseExact( row[CreatedDate], dateFormats, CultureInfo.InvariantCulture, DateTimeStyles.None, out createdDateValue ) ) { currentFamilyGroup.CreatedDateTime = createdDateValue; currentFamilyGroup.ModifiedDateTime = ImportDateTime; } else { currentFamilyGroup.CreatedDateTime = ImportDateTime; currentFamilyGroup.ModifiedDateTime = ImportDateTime; } completed++; if ( completed % (ReportingNumber * 10) < 1 ) { ReportProgress( 0, string.Format( "{0:N0} families imported.", completed ) ); } else if ( completed % ReportingNumber < 1 ) { SaveFamilies( newFamilyList, newGroupLocations ); ReportPartialProgress(); // Reset lookup context lookupContext.SaveChanges(); lookupContext = new RockContext(); locationService = new LocationService( lookupContext ); newFamilyList.Clear(); newGroupLocations.Clear(); } } } // Check to see if any rows didn't get saved to the database if ( newGroupLocations.Any() ) { SaveFamilies( newFamilyList, newGroupLocations ); } lookupContext.SaveChanges(); DetachAllInContext( lookupContext ); lookupContext.Dispose(); ReportProgress( 0, string.Format( "Finished family import: {0:N0} families added or updated.", completed ) ); return completed; }
/// <summary> /// Maps the batch data. /// </summary> /// <param name="csvData">The table data.</param> /// <exception cref="System.NotImplementedException"></exception> private int MapBatch( CSVInstance csvData ) { var batchStatusClosed = Rock.Model.BatchStatus.Closed; var newBatches = new List<FinancialBatch>(); int completed = 0; ReportProgress( 0, string.Format( "Verifying batch import ({0:N0} already exist).", ImportedBatches.Count ) ); string[] row; // Uses a look-ahead enumerator: this call will move to the next record immediately while ( (row = csvData.Database.FirstOrDefault()) != null ) { string batchIdKey = row[BatchID]; int? batchId = batchIdKey.AsType<int?>(); if ( batchId != null && !ImportedBatches.ContainsKey( ( int )batchId ) ) { var batch = new FinancialBatch(); batch.CreatedByPersonAliasId = ImportPersonAliasId; batch.ForeignKey = batchId.ToString(); batch.ForeignId = batchId; batch.Note = string.Empty; batch.Status = batchStatusClosed; batch.AccountingSystemCode = string.Empty; string name = row[BatchName] as string; if ( !String.IsNullOrWhiteSpace( name ) ) { name = name.Trim(); batch.Name = name.Left( 50 ); batch.CampusId = CampusList.Where( c => name.StartsWith( c.Name ) || name.StartsWith( c.ShortCode ) ) .Select( c => ( int? )c.Id ).FirstOrDefault(); } string batchDateKey = row[BatchDate]; DateTime? batchDate = batchDateKey.AsType<DateTime?>(); if ( batchDate != null ) { batch.BatchStartDateTime = batchDate; batch.BatchEndDateTime = batchDate; } string amountKey = row[BatchAmount]; decimal? amount = amountKey.AsType<decimal?>(); if ( amount != null ) { batch.ControlAmount = amount.HasValue ? amount.Value : new decimal(); } newBatches.Add( batch ); completed++; if ( completed % (ReportingNumber * 10) < 1 ) { ReportProgress( 0, string.Format( "{0:N0} batches imported.", completed ) ); } else if ( completed % ReportingNumber < 1 ) { SaveFinancialBatches( newBatches ); newBatches.ForEach( b => ImportedBatches.Add( ( int )b.ForeignId, ( int? )b.Id ) ); newBatches.Clear(); ReportPartialProgress(); } } } // add a default batch to use with contributions if ( !ImportedBatches.ContainsKey( 0 ) ) { var defaultBatch = new FinancialBatch(); defaultBatch.CreatedDateTime = ImportDateTime; defaultBatch.CreatedByPersonAliasId = ImportPersonAliasId; defaultBatch.Status = Rock.Model.BatchStatus.Closed; defaultBatch.Name = string.Format( "Default Batch (Imported {0})", ImportDateTime ); defaultBatch.ControlAmount = 0.0m; defaultBatch.ForeignKey = "0"; defaultBatch.ForeignId = 0; newBatches.Add( defaultBatch ); } if ( newBatches.Any() ) { SaveFinancialBatches( newBatches ); newBatches.ForEach( b => ImportedBatches.Add( ( int )b.ForeignId, ( int? )b.Id ) ); } ReportProgress( 100, string.Format( "Finished batch import: {0:N0} batches imported.", completed ) ); return completed; }
/// <summary> /// Maps the pledge. /// </summary> /// <param name="queryable">The queryable.</param> /// <exception cref="System.NotImplementedException"></exception> private int MapPledge( CSVInstance csvData ) { var lookupContext = new RockContext(); var accountList = new FinancialAccountService( lookupContext ).Queryable().AsNoTracking().ToList(); var importedPledges = new FinancialPledgeService( lookupContext ).Queryable().AsNoTracking() .Where( p => p.ForeignId != null ) .ToDictionary( t => ( int )t.ForeignId, t => ( int? )t.Id ); var pledgeFrequencies = DefinedTypeCache.Read( new Guid( Rock.SystemGuid.DefinedType.FINANCIAL_FREQUENCY ), lookupContext ).DefinedValues; int oneTimePledgeFrequencyId = pledgeFrequencies.FirstOrDefault( f => f.Guid == new Guid( Rock.SystemGuid.DefinedValue.TRANSACTION_FREQUENCY_ONE_TIME ) ).Id; var newPledges = new List<FinancialPledge>(); int completed = 0; ReportProgress( 0, string.Format( "Verifying pledge import ({0:N0} already exist).", importedPledges.Count ) ); string[] row; // Uses a look-ahead enumerator: this call will move to the next record immediately while ( (row = csvData.Database.FirstOrDefault()) != null ) { string amountKey = row[TotalPledge]; decimal? amount = amountKey.AsType<decimal?>(); string startDateKey = row[StartDate]; if ( String.IsNullOrWhiteSpace( startDateKey ) ) { startDateKey = "01/01/0001"; } DateTime? startDate = startDateKey.AsType<DateTime?>(); string endDateKey = row[EndDate]; if ( String.IsNullOrWhiteSpace( endDateKey ) ) { endDateKey = "12/31/9999"; } DateTime? endDate = endDateKey.AsType<DateTime?>(); string pledgeIdKey = row[PledgeId]; int? pledgeId = pledgeIdKey.AsType<int?>(); if ( amount != null && !importedPledges.ContainsKey( ( int )pledgeId ) ) { string individualIdKey = row[IndividualID]; int? individualId = individualIdKey.AsType<int?>(); var personKeys = GetPersonKeys( individualId ); if ( personKeys != null && personKeys.PersonAliasId > 0 ) { var pledge = new FinancialPledge(); pledge.PersonAliasId = personKeys.PersonAliasId; pledge.CreatedByPersonAliasId = ImportPersonAliasId; pledge.StartDate = ( DateTime )startDate; pledge.EndDate = ( DateTime )endDate; pledge.TotalAmount = ( decimal )amount; pledge.CreatedDateTime = ImportDateTime; pledge.ModifiedDateTime = ImportDateTime; pledge.ModifiedByPersonAliasId = ImportPersonAliasId; pledge.ForeignKey = pledgeIdKey; pledge.ForeignId = pledgeId; string frequency = row[PledgeFrequencyName].ToString().ToLower(); if ( !String.IsNullOrWhiteSpace( frequency ) ) { frequency = frequency.ToLower(); if ( frequency.Equals( "one time" ) || frequency.Equals( "one-time" ) || frequency.Equals( "as can" ) ) { pledge.PledgeFrequencyValueId = oneTimePledgeFrequencyId; } else { pledge.PledgeFrequencyValueId = pledgeFrequencies .Where( f => f.Value.ToLower().StartsWith( frequency ) || f.Description.ToLower().StartsWith( frequency ) ) .Select( f => f.Id ).FirstOrDefault(); } } string fundName = row[FundName] as string; string subFund = row[SubFundName] as string; string fundGLAccount = row[FundGLAccount] as string; string subFundGLAccount = row[SubFundGLAccount] as string; string isFundActiveKey = row[FundIsActive]; Boolean? isFundActive = isFundActiveKey.AsType<Boolean?>(); string isSubFundActiveKey = row[SubFundIsActive]; Boolean? isSubFundActive = isSubFundActiveKey.AsType<Boolean?>(); if ( !String.IsNullOrWhiteSpace( fundName ) ) { var parentAccount = accountList.FirstOrDefault( a => a.Name.Equals( fundName.Truncate( 50 ) ) && a.CampusId == null ); if ( parentAccount == null ) { parentAccount = AddAccount( lookupContext, fundName, string.Empty, null, null, isFundActive ); accountList.Add( parentAccount ); } if ( !String.IsNullOrWhiteSpace( subFund ) ) { int? campusFundId = null; // assign a campus if the subfund is a campus fund var campusFund = CampusList.FirstOrDefault( c => subFund.StartsWith( c.Name ) || subFund.StartsWith( c.ShortCode ) ); if ( campusFund != null ) { // use full campus name as the subfund subFund = campusFund.Name; campusFundId = campusFund.Id; } // add info to easily find/assign this fund in the view subFund = string.Format( "{0} {1}", subFund, fundName ); var childAccount = accountList.FirstOrDefault( c => c.Name.Equals( subFund.Truncate( 50 ) ) && c.ParentAccountId == parentAccount.Id ); if ( childAccount == null ) { // create a child account with a campusId if it was set childAccount = AddAccount( lookupContext, subFund, string.Empty, campusFundId, parentAccount.Id, isSubFundActive ); accountList.Add( childAccount ); } pledge.AccountId = childAccount.Id; } else { pledge.AccountId = parentAccount.Id; } } newPledges.Add( pledge ); completed++; if ( completed % (ReportingNumber * 10) < 1 ) { ReportProgress( 0, string.Format( "{0:N0} pledges imported.", completed ) ); } else if ( completed % ReportingNumber < 1 ) { SavePledges( newPledges ); ReportPartialProgress(); newPledges.Clear(); } } } } if ( newPledges.Any() ) { SavePledges( newPledges ); } ReportProgress( 100, string.Format( "Finished pledge import: {0:N0} pledges imported.", completed ) ); return completed; }
/// <summary> /// Maps the pledge. /// </summary> /// <param name="queryable">The queryable.</param> /// <exception cref="System.NotImplementedException"></exception> private int MapPledge(CSVInstance csvData) { var lookupContext = new RockContext(); var accountList = new FinancialAccountService(lookupContext).Queryable().AsNoTracking().ToList(); var importedPledges = new FinancialPledgeService(lookupContext).Queryable().AsNoTracking().ToList(); var pledgeFrequencies = DefinedTypeCache.Read(new Guid(Rock.SystemGuid.DefinedType.FINANCIAL_FREQUENCY), lookupContext).DefinedValues; int oneTimePledgeFrequencyId = pledgeFrequencies.FirstOrDefault(f => f.Guid == new Guid(Rock.SystemGuid.DefinedValue.TRANSACTION_FREQUENCY_ONE_TIME)).Id; var newPledges = new List <FinancialPledge>(); int completed = 0; ReportProgress(0, string.Format("Verifying pledge import ({0:N0} already exist).", importedPledges.Count)); string[] row; // Uses a look-ahead enumerator: this call will move to the next record immediately while ((row = csvData.Database.FirstOrDefault()) != null) { string amountKey = row[TotalPledge]; decimal? amount = amountKey.AsType <decimal?>(); string startDateKey = row[StartDate]; DateTime?startDate = startDateKey.AsType <DateTime?>(); string endDateKey = row[EndDate]; DateTime?endDate = endDateKey.AsType <DateTime?>(); if (amount != null && startDate != null && endDate != null) { string individualIdKey = row[IndividualID]; int? individualId = individualIdKey.AsType <int?>(); var personKeys = GetPersonKeys(individualId); if (personKeys != null && personKeys.PersonAliasId > 0) { var pledge = new FinancialPledge(); pledge.PersonAliasId = personKeys.PersonAliasId; pledge.CreatedByPersonAliasId = ImportPersonAliasId; pledge.StartDate = (DateTime)startDate; pledge.EndDate = (DateTime)endDate; pledge.TotalAmount = (decimal)amount; string frequency = row[PledgeFrequencyName].ToString().ToLower(); if (frequency != null) { frequency = frequency.ToLower(); if (frequency.Equals("one time") || frequency.Equals("as can")) { pledge.PledgeFrequencyValueId = oneTimePledgeFrequencyId; } else { pledge.PledgeFrequencyValueId = pledgeFrequencies .Where(f => f.Value.ToLower().StartsWith(frequency) || f.Description.ToLower().StartsWith(frequency)) .Select(f => f.Id).FirstOrDefault(); } } string fundName = row[FundName] as string; string subFund = row[SubFundName] as string; string fundGLAccount = row[FundGLAccount] as string; string subFundGLAccount = row[SubFundGLAccount] as string; string isFundActiveKey = row[FundIsActive]; Boolean?isFundActive = isFundActiveKey.AsType <Boolean?>(); if (fundName != null) { var parentAccount = accountList.FirstOrDefault(a => a.Name.Equals(fundName) && a.CampusId == null); if (parentAccount == null) { parentAccount = AddAccount(lookupContext, fundName, string.Empty, null, null, isFundActive); accountList.Add(parentAccount); } if (subFund != null) { int?campusFundId = null; // assign a campus if the subfund is a campus fund var campusFund = CampusList.FirstOrDefault(c => subFund.StartsWith(c.Name) || subFund.StartsWith(c.ShortCode)); if (campusFund != null) { // use full campus name as the subfund subFund = campusFund.Name; campusFundId = campusFund.Id; } // add info to easily find/assign this fund in the view subFund = string.Format("{0} {1}", subFund, fundName); var childAccount = accountList.FirstOrDefault(c => c.Name.Equals(subFund) && c.ParentAccountId == parentAccount.Id); if (childAccount == null) { // create a child account with a campusId if it was set childAccount = AddAccount(lookupContext, subFund, string.Empty, campusFundId, parentAccount.Id, isFundActive); accountList.Add(childAccount); } pledge.AccountId = childAccount.Id; } else { pledge.AccountId = parentAccount.Id; } } newPledges.Add(pledge); completed++; if (completed % (ReportingNumber * 10) < 1) { ReportProgress(0, string.Format("{0:N0} pledges imported.", completed)); } else if (completed % ReportingNumber < 1) { SavePledges(newPledges); ReportPartialProgress(); newPledges.Clear(); } } } } if (newPledges.Any()) { SavePledges(newPledges); } ReportProgress(100, string.Format("Finished pledge import: {0:N0} pledges imported.", completed)); return(completed); }
/// <summary> /// Loads the individual data. /// </summary> /// <param name="csvData">The CSV data.</param> private int LoadIndividuals(CSVInstance csvData) { var lookupContext = new RockContext(); // Marital statuses: Married, Single, Separated, etc var maritalStatusTypes = DefinedTypeCache.Read(new Guid(Rock.SystemGuid.DefinedType.PERSON_MARITAL_STATUS), lookupContext).DefinedValues; // Connection statuses: Member, Visitor, Attendee, etc var connectionStatusTypes = DefinedTypeCache.Read(new Guid(Rock.SystemGuid.DefinedType.PERSON_CONNECTION_STATUS), lookupContext).DefinedValues; // Suffix types: Dr., Jr., II, etc var suffixTypes = DefinedTypeCache.Read(new Guid(Rock.SystemGuid.DefinedType.PERSON_SUFFIX), lookupContext).DefinedValues; // Title types: Mr., Mrs. Dr., etc var titleTypes = DefinedTypeCache.Read(new Guid(Rock.SystemGuid.DefinedType.PERSON_TITLE), lookupContext).DefinedValues; // Group roles: Owner, Adult, Child, others var familyRoles = GroupTypeCache.Read(new Guid(Rock.SystemGuid.GroupType.GROUPTYPE_FAMILY), lookupContext).Roles; // Phone types: Home, Work, Mobile var numberTypeValues = DefinedTypeCache.Read(new Guid(Rock.SystemGuid.DefinedType.PERSON_PHONE_TYPE), lookupContext).DefinedValues; // School Person attribute var schoolAttribute = FindEntityAttribute(lookupContext, "Education", "School", PersonEntityTypeId); // Visit info category var visitInfoCategory = new CategoryService(lookupContext).GetByEntityTypeId(AttributeEntityTypeId) .Where(c => c.Name == "Visit Information").FirstOrDefault(); // Look for custom attributes in the Individual file var allFields = csvData.TableNodes.FirstOrDefault().Children.Select((node, index) => new { node = node, index = index }).ToList(); var customAttributes = allFields .Where(f => f.index > SecurityNote) .ToDictionary(f => f.index, f => f.node.Name); var personAttributes = new List <Rock.Model.Attribute>(); // Add any attributes if they don't already exist if (customAttributes.Any()) { foreach (var avp in customAttributes.Where(ca => !personAttributes.Any(a => a.Name.Equals(ca.Value, StringComparison.InvariantCultureIgnoreCase)))) { var newAttribute = AddEntityAttribute(lookupContext, PersonEntityTypeId, string.Empty, string.Empty, string.Empty, string.Empty, avp.Value, string.Empty, TextFieldTypeId, true, null, null, ImportPersonAliasId ); personAttributes.Add(newAttribute); } } var currentFamilyGroup = new Group(); var newFamilyList = new List <Group>(); var newVisitorList = new List <Group>(); var newNoteList = new List <Note>(); var completed = 0; var newFamilies = 0; var newPeople = 0; ReportProgress(0, string.Format("Starting Individual import ({0:N0} already exist).", ImportedPeopleKeys.Count())); string[] row; row = csvData.Database.FirstOrDefault(); while (row != null) { int?groupRoleId = null; var isFamilyRelationship = true; var rowFamilyName = row[FamilyName]; var rowFamilyKey = row[FamilyId]; var rowPersonKey = row[PersonId]; var rowFamilyId = rowFamilyKey.AsType <int?>(); var rowPersonId = rowPersonKey.AsType <int?>(); // Check that this person isn't already in our data var newPerson = true; if (ImportedPeopleKeys.Count() > 0) { var personKeys = GetPersonKeys(rowPersonKey); if (personKeys != null) { newPerson = false; } } if (newPerson) { #region person create var person = new Person { ForeignKey = rowPersonKey, ForeignId = rowPersonId, SystemNote = string.Format("Imported via Excavator on {0}", ImportDateTime), RecordTypeValueId = PersonRecordTypeId, CreatedByPersonAliasId = ImportPersonAliasId }; var firstName = row[FirstName].Left(50); var nickName = row[NickName].Left(50); person.FirstName = firstName; person.NickName = string.IsNullOrWhiteSpace(nickName) ? firstName : nickName; person.MiddleName = row[MiddleName].Left(50); person.LastName = row[LastName].Left(50); var createdDateValue = ParseDateOrDefault(row[CreatedDate], null); if (createdDateValue.HasValue) { person.CreatedDateTime = createdDateValue; person.ModifiedDateTime = ImportDateTime; } else { person.CreatedDateTime = ImportDateTime; person.ModifiedDateTime = ImportDateTime; } var birthDate = ParseDateOrDefault(row[DateOfBirth], null); if (birthDate.HasValue) { person.BirthDay = ((DateTime)birthDate).Day; person.BirthMonth = ((DateTime)birthDate).Month; person.BirthYear = ((DateTime)birthDate).Year; } var graduationDate = ParseDateOrDefault(row[GraduationDate], null); if (graduationDate.HasValue) { person.GraduationYear = ((DateTime)graduationDate).Year; } var anniversary = ParseDateOrDefault(row[Anniversary], null); if (anniversary.HasValue) { person.AnniversaryDate = anniversary; } var gender = row[Gender]; if (gender != null) { switch (gender.Trim().ToLower()) { case "m": case "male": person.Gender = Rock.Model.Gender.Male; break; case "f": case "female": person.Gender = Rock.Model.Gender.Female; break; default: person.Gender = Rock.Model.Gender.Unknown; break; } } var prefix = row[Prefix]; if (!string.IsNullOrWhiteSpace(prefix)) { prefix = prefix.RemoveSpecialCharacters(); person.TitleValueId = titleTypes.Where(s => prefix.Equals(s.Value.RemoveSpecialCharacters(), StringComparison.CurrentCultureIgnoreCase)) .Select(s => (int?)s.Id).FirstOrDefault(); if (!person.TitleValueId.HasValue) { var newTitle = AddDefinedValue(lookupContext, Rock.SystemGuid.DefinedType.PERSON_TITLE, prefix); if (newTitle != null) { titleTypes.Add(newTitle); person.TitleValueId = newTitle.Id; } } } var suffix = row[Suffix]; if (!string.IsNullOrWhiteSpace(suffix)) { suffix = suffix.RemoveSpecialCharacters(); person.SuffixValueId = suffixTypes.Where(s => suffix.Equals(s.Value.RemoveSpecialCharacters(), StringComparison.CurrentCultureIgnoreCase)) .Select(s => (int?)s.Id).FirstOrDefault(); if (!person.SuffixValueId.HasValue) { var newSuffix = AddDefinedValue(lookupContext, Rock.SystemGuid.DefinedType.PERSON_SUFFIX, suffix); if (newSuffix != null) { suffixTypes.Add(newSuffix); person.SuffixValueId = newSuffix.Id; } } } var maritalStatus = row[MaritalStatus]; if (!string.IsNullOrWhiteSpace(maritalStatus)) { maritalStatus = maritalStatus.RemoveSpecialCharacters(); person.MaritalStatusValueId = maritalStatusTypes.Where(s => maritalStatus.Equals(s.Value.RemoveSpecialCharacters(), StringComparison.CurrentCultureIgnoreCase)) .Select(dv => (int?)dv.Id).FirstOrDefault(); if (!person.MaritalStatusValueId.HasValue) { var newMaritalStatus = AddDefinedValue(lookupContext, Rock.SystemGuid.DefinedType.PERSON_MARITAL_STATUS, maritalStatus); if (newMaritalStatus != null) { maritalStatusTypes.Add(newMaritalStatus); person.MaritalStatusValueId = newMaritalStatus.Id; } } } if (person.MaritalStatusValueId == null) { person.MaritalStatusValueId = maritalStatusTypes.Where(dv => dv.Value.Equals("Unknown", StringComparison.CurrentCultureIgnoreCase)) .Select(dv => (int?)dv.Id).FirstOrDefault(); } var familyRole = row[FamilyRole]; if (!string.IsNullOrWhiteSpace(familyRole)) { familyRole = familyRole.RemoveSpecialCharacters().Trim(); groupRoleId = familyRoles.Where(dv => string.Equals(dv.Name, familyRole, StringComparison.CurrentCultureIgnoreCase)) .Select(dv => (int?)dv.Id).FirstOrDefault(); if (!groupRoleId.HasValue) { AddGroupRole(lookupContext, Rock.SystemGuid.GroupType.GROUPTYPE_FAMILY, familyRole); familyRoles = GroupTypeCache.Read(Rock.SystemGuid.GroupType.GROUPTYPE_FAMILY).Roles; groupRoleId = familyRoles.Where(dv => dv.Name == familyRole) .Select(dv => (int?)dv.Id).FirstOrDefault(); } if (familyRole.Equals("Visitor", StringComparison.CurrentCultureIgnoreCase)) { isFamilyRelationship = false; } } if (groupRoleId == null) { groupRoleId = FamilyAdultRoleId; } var recordStatus = row[RecordStatus]; if (!string.IsNullOrWhiteSpace(recordStatus)) { switch (recordStatus.Trim().ToLower()) { case "active": person.RecordStatusValueId = ActivePersonRecordStatusId; break; case "inactive": person.RecordStatusValueId = InactivePersonRecordStatusId; break; default: person.RecordStatusValueId = PendingPersonRecordStatusId; break; } } else { person.RecordStatusValueId = ActivePersonRecordStatusId; } var connectionStatus = row[ConnectionStatus]; if (!string.IsNullOrWhiteSpace(connectionStatus)) { if (connectionStatus.Equals("Member", StringComparison.CurrentCultureIgnoreCase)) { person.ConnectionStatusValueId = MemberConnectionStatusId; } else if (connectionStatus.Equals("Visitor", StringComparison.CurrentCultureIgnoreCase)) { person.ConnectionStatusValueId = VisitorConnectionStatusId; } else if (connectionStatus.Equals("Business", StringComparison.CurrentCultureIgnoreCase)) { person.RecordTypeValueId = BusinessRecordTypeId; } else if (connectionStatus.Equals("Inactive", StringComparison.CurrentCultureIgnoreCase)) { person.RecordStatusValueId = InactivePersonRecordStatusId; } else { // create user-defined connection type if it doesn't exist person.ConnectionStatusValueId = connectionStatusTypes.Where(dv => dv.Value.Equals(connectionStatus, StringComparison.CurrentCultureIgnoreCase)) .Select(dv => (int?)dv.Id).FirstOrDefault(); if (!person.ConnectionStatusValueId.HasValue) { var newConnectionStatus = AddDefinedValue(lookupContext, Rock.SystemGuid.DefinedType.PERSON_CONNECTION_STATUS, connectionStatus); if (newConnectionStatus != null) { connectionStatusTypes.Add(newConnectionStatus); person.ConnectionStatusValueId = newConnectionStatus.Id; } } } } else { person.ConnectionStatusValueId = VisitorConnectionStatusId; } var isDeceasedValue = row[IsDeceased]; if (!string.IsNullOrWhiteSpace(isDeceasedValue)) { switch (isDeceasedValue.Trim().ToLower()) { case "y": case "yes": case "true": person.IsDeceased = true; person.RecordStatusReasonValueId = DeceasedPersonRecordReasonId; person.RecordStatusValueId = InactivePersonRecordStatusId; break; default: person.IsDeceased = false; break; } } var personNumbers = new Dictionary <string, string>(); personNumbers.Add("Home", row[HomePhone]); personNumbers.Add("Mobile", row[MobilePhone]); personNumbers.Add("Work", row[WorkPhone]); var smsAllowed = row[AllowSMS]; foreach (var numberPair in personNumbers.Where(n => !string.IsNullOrWhiteSpace(n.Value) && n.Value.AsNumeric().AsType <Int64>() > 0)) { var extension = string.Empty; var countryCode = PhoneNumber.DefaultCountryCode(); var normalizedNumber = string.Empty; var countryIndex = numberPair.Value.IndexOf('+'); var extensionIndex = numberPair.Value.LastIndexOf('x') > 0 ? numberPair.Value.LastIndexOf('x') : numberPair.Value.Length; if (countryIndex >= 0) { countryCode = numberPair.Value.Substring(countryIndex, countryIndex + 3); normalizedNumber = numberPair.Value.Substring(countryIndex + 3, extensionIndex - 3).AsNumeric().TrimStart(new Char[] { '0' }); extension = numberPair.Value.Substring(extensionIndex); } else if (extensionIndex > 0) { normalizedNumber = numberPair.Value.Substring(0, extensionIndex).AsNumeric(); extension = numberPair.Value.Substring(extensionIndex).AsNumeric(); } else { normalizedNumber = numberPair.Value.AsNumeric(); } if (!string.IsNullOrWhiteSpace(normalizedNumber)) { var currentNumber = new PhoneNumber(); currentNumber.CountryCode = countryCode; currentNumber.CreatedByPersonAliasId = ImportPersonAliasId; currentNumber.Extension = extension.Left(20); currentNumber.Number = normalizedNumber.TrimStart(new char[] { '0' }).Left(20); currentNumber.NumberFormatted = PhoneNumber.FormattedNumber(currentNumber.CountryCode, currentNumber.Number); currentNumber.NumberTypeValueId = numberTypeValues.Where(v => v.Value.Equals(numberPair.Key, StringComparison.CurrentCultureIgnoreCase)) .Select(v => (int?)v.Id).FirstOrDefault(); if (numberPair.Key == "Mobile") { switch (smsAllowed.Trim().ToLower()) { case "y": case "yes": case "active": case "true": currentNumber.IsMessagingEnabled = true; break; default: currentNumber.IsMessagingEnabled = false; break; } } person.PhoneNumbers.Add(currentNumber); } } // Map Person attributes person.Attributes = new Dictionary <string, AttributeCache>(); person.AttributeValues = new Dictionary <string, AttributeValueCache>(); bool isEmailActive; switch (row[IsEmailActive].Trim().ToLower()) { case "n": case "no": case "inactive": isEmailActive = false; break; default: isEmailActive = true; break; } EmailPreference emailPreference; switch (row[AllowBulkEmail].Trim().ToLower()) { case "n": case "no": case "inactive": emailPreference = EmailPreference.NoMassEmails; break; default: emailPreference = EmailPreference.EmailAllowed; break; } person.EmailPreference = emailPreference; var primaryEmail = row[Email].Trim().Left(75); if (!string.IsNullOrWhiteSpace(primaryEmail)) { if (primaryEmail.IsEmail()) { person.Email = primaryEmail; person.IsEmailActive = isEmailActive; } else { LogException("InvalidPrimaryEmail", string.Format("PersonId: {0} - Email: {1}", rowPersonKey, primaryEmail)); } } var schoolName = row[School]; if (!string.IsNullOrWhiteSpace(schoolName)) { AddEntityAttributeValue(lookupContext, schoolAttribute, person, schoolName, null, true); } foreach (var attributePair in customAttributes) { string newAttributeValue = row[attributePair.Key]; if (!string.IsNullOrWhiteSpace(newAttributeValue)) { // check if this attribute value is a date var valueAsDateTime = ParseDateOrDefault(newAttributeValue, null); if (valueAsDateTime.HasValue) { newAttributeValue = ((DateTime)valueAsDateTime).ToString("yyyy-MM-dd"); } var newAttribute = personAttributes.Where(a => a.Key == attributePair.Value.RemoveWhitespace()) .FirstOrDefault(); if (newAttribute != null) { AddEntityAttributeValue(lookupContext, newAttribute, person, newAttributeValue, null, false); } } } // Add notes to timeline var notePairs = new Dictionary <string, string> { { "General", row[GeneralNote] }, { "Medical", row[MedicalNote] }, { "Security", row[SecurityNote] } }; foreach (var notePair in notePairs.Where(n => !string.IsNullOrWhiteSpace(n.Value))) { var splitNotePair = notePair.Value.Split('^'); foreach (string noteValue in splitNotePair) { var newNote = new Note { NoteTypeId = PersonalNoteTypeId, CreatedByPersonAliasId = ImportPersonAliasId, CreatedDateTime = ImportDateTime, Text = noteValue, ForeignKey = rowPersonKey, ForeignId = rowPersonId, Caption = string.Format("{0} Note", notePair.Key) }; if (noteValue.StartsWith("[ALERT]", StringComparison.CurrentCultureIgnoreCase)) { newNote.IsAlert = true; } if (notePair.Key.Equals("Security")) { // Pastoral note type id var securityNoteType = new NoteTypeService(lookupContext).Get(PersonEntityTypeId, "Secure Note", true); if (securityNoteType != null) { newNote.NoteTypeId = securityNoteType.Id; } } if (notePair.Key.Equals("Medical")) { newNote.IsAlert = true; } newNoteList.Add(newNote); } } #endregion person create var groupMember = new GroupMember { Person = person, GroupRoleId = (int)groupRoleId, CreatedDateTime = ImportDateTime, ModifiedDateTime = ImportDateTime, CreatedByPersonAliasId = ImportPersonAliasId, GroupMemberStatus = GroupMemberStatus.Active }; if (rowFamilyKey != currentFamilyGroup.ForeignKey) { // person not part of the previous family, see if that family exists or create a new one currentFamilyGroup = ImportedFamilies.FirstOrDefault(g => g.ForeignKey == rowFamilyKey); if (currentFamilyGroup == null) { currentFamilyGroup = CreateFamilyGroup(row[FamilyName], rowFamilyKey); newFamilyList.Add(currentFamilyGroup); newFamilies++; } else { lookupContext.Groups.Attach(currentFamilyGroup); lookupContext.Entry(currentFamilyGroup).State = EntityState.Modified; } currentFamilyGroup.Members.Add(groupMember); } else { // person is part of this family group, check if they're a visitor if (isFamilyRelationship || currentFamilyGroup.Members.Count() < 1) { currentFamilyGroup.Members.Add(groupMember); } else { var visitorFamily = CreateFamilyGroup(person.LastName + " Family", rowFamilyKey); visitorFamily.Members.Add(groupMember); newFamilyList.Add(visitorFamily); newVisitorList.Add(visitorFamily); newFamilies++; } } // look ahead 1 row var rowNextFamilyKey = "-1"; if ((row = csvData.Database.FirstOrDefault()) != null) { rowNextFamilyKey = row[FamilyId]; } newPeople++; completed++; if (completed % (ReportingNumber * 10) < 1) { ReportProgress(0, string.Format("{0:N0} people processed.", completed)); } if (newPeople >= ReportingNumber && rowNextFamilyKey != currentFamilyGroup.ForeignKey) { SaveIndividuals(newFamilyList, newVisitorList, newNoteList); lookupContext.SaveChanges(); ReportPartialProgress(); // Clear out variables currentFamilyGroup = new Group(); newFamilyList.Clear(); newVisitorList.Clear(); newNoteList.Clear(); newPeople = 0; } } else { row = csvData.Database.FirstOrDefault(); } } // Save any changes to new families if (newFamilyList.Any()) { SaveIndividuals(newFamilyList, newVisitorList, newNoteList); } // Save any changes to existing families lookupContext.SaveChanges(); DetachAllInContext(lookupContext); lookupContext.Dispose(); ReportProgress(0, string.Format("Finished individual import: {0:N0} families and {1:N0} people added.", newFamilies, completed)); return(completed); }
/// <summary> /// Loads the individual data. /// </summary> /// <param name="csvData">The CSV data.</param> private int LoadIndividuals( CSVInstance csvData ) { var lookupContext = new RockContext(); var groupTypeRoleService = new GroupTypeRoleService( lookupContext ); var groupMemberService = new GroupMemberService( lookupContext ); // Marital statuses: Married, Single, Separated, etc var maritalStatusTypes = DefinedTypeCache.Read( new Guid( Rock.SystemGuid.DefinedType.PERSON_MARITAL_STATUS ), lookupContext ).DefinedValues; // Connection statuses: Member, Visitor, Attendee, etc var connectionStatusTypes = DefinedTypeCache.Read( new Guid( Rock.SystemGuid.DefinedType.PERSON_CONNECTION_STATUS ), lookupContext ).DefinedValues; int memberConnectionStatusId = connectionStatusTypes.FirstOrDefault( dv => dv.Guid == new Guid( Rock.SystemGuid.DefinedValue.PERSON_CONNECTION_STATUS_MEMBER ) ).Id; int visitorConnectionStatusId = connectionStatusTypes.FirstOrDefault( dv => dv.Guid == new Guid( Rock.SystemGuid.DefinedValue.PERSON_CONNECTION_STATUS_VISITOR ) ).Id; int attendeeConnectionStatusId = connectionStatusTypes.FirstOrDefault( dv => dv.Guid == new Guid( Rock.SystemGuid.DefinedValue.PERSON_CONNECTION_STATUS_ATTENDEE ) ).Id; // Suffix type: Dr., Jr., II, etc var suffixTypes = DefinedTypeCache.Read( new Guid( Rock.SystemGuid.DefinedType.PERSON_SUFFIX ), lookupContext ).DefinedValues; // Title type: Mr., Mrs. Dr., etc var titleTypes = DefinedTypeCache.Read( new Guid( Rock.SystemGuid.DefinedType.PERSON_TITLE ), lookupContext ).DefinedValues; // Record statuses: Active, Inactive, Pending int? recordStatusActiveId = DefinedValueCache.Read( new Guid( Rock.SystemGuid.DefinedValue.PERSON_RECORD_STATUS_ACTIVE ), lookupContext ).Id; int? recordStatusInactiveId = DefinedValueCache.Read( new Guid( Rock.SystemGuid.DefinedValue.PERSON_RECORD_STATUS_INACTIVE ), lookupContext ).Id; int? recordStatusPendingId = DefinedValueCache.Read( new Guid( Rock.SystemGuid.DefinedValue.PERSON_RECORD_STATUS_PENDING ), lookupContext ).Id; // Deceased record status reason (others available: No Activity, Moved, etc) var recordStatusDeceasedId = DefinedValueCache.Read( new Guid( Rock.SystemGuid.DefinedValue.PERSON_RECORD_STATUS_REASON_DECEASED ) ).Id; // Record type: Person int? personRecordTypeId = DefinedValueCache.Read( new Guid( Rock.SystemGuid.DefinedValue.PERSON_RECORD_TYPE_PERSON ), lookupContext ).Id; // Group roles: Owner, Adult, Child, others GroupTypeRole ownerRole = groupTypeRoleService.Get( new Guid( Rock.SystemGuid.GroupRole.GROUPROLE_KNOWN_RELATIONSHIPS_OWNER ) ); int adultRoleId = groupTypeRoleService.Get( new Guid( Rock.SystemGuid.GroupRole.GROUPROLE_FAMILY_MEMBER_ADULT ) ).Id; int childRoleId = groupTypeRoleService.Get( new Guid( Rock.SystemGuid.GroupRole.GROUPROLE_FAMILY_MEMBER_CHILD ) ).Id; // Phone types: Home, Work, Mobile var numberTypeValues = DefinedTypeCache.Read( new Guid( Rock.SystemGuid.DefinedType.PERSON_PHONE_TYPE ), lookupContext ).DefinedValues; // Personal note type id var personalNoteTypeId = new NoteTypeService( lookupContext ).Get( new Guid( Rock.SystemGuid.NoteType.PERSON_TIMELINE_NOTE ) ).Id; // School defined type var schoolDefinedType = DefinedTypeCache.Read( new Guid( "576FF1E2-6225-4565-A16D-230E26167A3D" ) ); // Look up existing Person attributes var personAttributes = new AttributeService( lookupContext ).GetByEntityTypeId( PersonEntityTypeId ).ToList(); var schoolAttribute = AttributeCache.Read( personAttributes.FirstOrDefault( a => a.Key == "School" ) ); // Text field type id int textFieldTypeId = FieldTypeCache.Read( new Guid( Rock.SystemGuid.FieldType.TEXT ), lookupContext ).Id; int dateFieldTypeId = FieldTypeCache.Read( new Guid( Rock.SystemGuid.FieldType.DATE ), lookupContext ).Id; // Attribute entity type id int attributeEntityTypeId = EntityTypeCache.Read( "Rock.Model.Attribute" ).Id; // Visit info category var visitInfoCategory = new CategoryService( lookupContext ).GetByEntityTypeId( attributeEntityTypeId ) .Where( c => c.Name == "Visit Information" ).FirstOrDefault(); // Look for custom attributes in the Individual file var allFields = csvData.TableNodes.FirstOrDefault().Children.Select( ( node, index ) => new { node = node, index = index } ).ToList(); Dictionary<int, string> customAttributes = allFields .Where( f => f.index > SecurityNote ) .ToDictionary( f => f.index, f => f.node.Name.RemoveWhitespace() ); // Add any attributes if they don't already exist if ( customAttributes.Any() ) { var newAttributes = new List<Rock.Model.Attribute>(); foreach ( var newAttributePair in customAttributes.Where( ca => !personAttributes.Any( a => a.Key == ca.Value ) ) ) { var newAttribute = new Rock.Model.Attribute(); newAttribute.Name = newAttributePair.Value; newAttribute.Key = newAttributePair.Value.RemoveWhitespace(); newAttribute.Description = newAttributePair.Value + " created by CSV import"; newAttribute.EntityTypeQualifierValue = string.Empty; newAttribute.EntityTypeQualifierColumn = string.Empty; newAttribute.EntityTypeId = PersonEntityTypeId; newAttribute.FieldTypeId = textFieldTypeId; newAttribute.DefaultValue = string.Empty; newAttribute.IsMultiValue = false; newAttribute.IsGridColumn = false; newAttribute.IsRequired = false; newAttribute.Order = 0; newAttributes.Add( newAttribute ); } lookupContext.Attributes.AddRange( newAttributes ); lookupContext.SaveChanges( DisableAuditing ); personAttributes.AddRange( newAttributes ); } // Set the supported date formats var dateFormats = new[] { "yyyy-MM-dd", "MM/dd/yyyy", "MM/dd/yy" }; var currentFamilyGroup = new Group(); var newFamilyList = new List<Group>(); var newVisitorList = new List<Group>(); var newNoteList = new List<Note>(); int completed = 0; int newFamilies = 0; int newPeople = 0; ReportProgress( 0, string.Format( "Starting Individual import ({0:N0} already exist).", ImportedPeopleKeys.Count() ) ); string[] row; row = csvData.Database.FirstOrDefault(); while ( row != null ) { int groupRoleId = adultRoleId; bool isFamilyRelationship = true; string rowFamilyName = row[FamilyName]; string rowFamilyKey = row[FamilyId]; string rowPersonKey = row[PersonId]; int? rowFamilyId = rowFamilyKey.AsType<int?>(); int? rowPersonId = rowPersonKey.AsType<int?>(); // Check that this person isn't already in our data var personExists = ImportedFamilies.Any( g => g.Members.Any( m => m.Person.ForeignKey == rowPersonKey ) ); if ( !personExists ) { #region person create var person = new Person(); person.ForeignKey = rowPersonKey; person.ForeignId = rowPersonId; person.SystemNote = string.Format( "Imported via Excavator on {0}", ImportDateTime ); person.RecordTypeValueId = personRecordTypeId; person.CreatedByPersonAliasId = ImportPersonAliasId; string firstName = row[FirstName].Left( 50 ); string nickName = row[NickName].Left( 50 ); person.FirstName = firstName; person.NickName = string.IsNullOrWhiteSpace( nickName ) ? firstName : nickName; person.MiddleName = row[MiddleName].Left( 50 ); person.LastName = row[LastName].Left( 50 ); DateTime createdDateValue; if ( DateTime.TryParseExact( row[CreatedDate], dateFormats, CultureInfo.InvariantCulture, DateTimeStyles.None, out createdDateValue ) ) { person.CreatedDateTime = createdDateValue; person.ModifiedDateTime = ImportDateTime; } else { person.CreatedDateTime = ImportDateTime; person.ModifiedDateTime = ImportDateTime; } DateTime birthDate; if ( DateTime.TryParseExact( row[DateOfBirth], dateFormats, CultureInfo.InvariantCulture, DateTimeStyles.None, out birthDate ) ) { person.BirthDay = birthDate.Day; person.BirthMonth = birthDate.Month; person.BirthYear = birthDate.Year; } DateTime graduationDate; if ( DateTime.TryParseExact( row[GraduationDate], dateFormats, CultureInfo.InvariantCulture, DateTimeStyles.None, out graduationDate ) ) { person.GraduationYear = graduationDate.Year; } DateTime anniversary; if ( DateTime.TryParseExact( row[Anniversary], dateFormats, CultureInfo.InvariantCulture, DateTimeStyles.None, out anniversary ) ) { person.AnniversaryDate = anniversary; } string gender = row[Gender]; if ( gender != null ) { switch ( gender.Trim().ToLower() ) { case "m": case "male": person.Gender = Rock.Model.Gender.Male; break; case "f": case "female": person.Gender = Rock.Model.Gender.Female; break; default: person.Gender = Rock.Model.Gender.Unknown; break; } } string prefix = row[Prefix]; if ( !string.IsNullOrWhiteSpace( prefix ) ) { prefix = prefix.RemoveSpecialCharacters().Trim(); person.TitleValueId = titleTypes.Where( s => prefix == s.Value.RemoveSpecialCharacters() ) .Select( s => (int?)s.Id ).FirstOrDefault(); } string suffix = row[Suffix]; if ( !string.IsNullOrWhiteSpace( suffix ) ) { suffix = suffix.RemoveSpecialCharacters().Trim(); person.SuffixValueId = suffixTypes.Where( s => suffix == s.Value.RemoveSpecialCharacters() ) .Select( s => (int?)s.Id ).FirstOrDefault(); } string maritalStatus = row[MaritalStatus]; if ( !string.IsNullOrWhiteSpace( maritalStatus ) ) { person.MaritalStatusValueId = maritalStatusTypes.Where( dv => dv.Value == maritalStatus ) .Select( dv => (int?)dv.Id ).FirstOrDefault(); } else { person.MaritalStatusValueId = maritalStatusTypes.Where( dv => dv.Value == "Unknown" ) .Select( dv => ( int? )dv.Id ).FirstOrDefault(); } string familyRole = row[FamilyRole]; if ( !string.IsNullOrWhiteSpace( familyRole ) ) { if ( familyRole == "Visitor" ) { isFamilyRelationship = false; } if ( familyRole == "Child" || person.Age < 18 ) { groupRoleId = childRoleId; } } string connectionStatus = row[ConnectionStatus]; if ( !string.IsNullOrWhiteSpace( connectionStatus ) ) { if ( connectionStatus == "Member" ) { person.ConnectionStatusValueId = memberConnectionStatusId; } else if ( connectionStatus == "Visitor" ) { person.ConnectionStatusValueId = visitorConnectionStatusId; } else { // look for user-defined connection type or default to Attendee var customConnectionType = connectionStatusTypes.Where( dv => dv.Value == connectionStatus ) .Select( dv => (int?)dv.Id ).FirstOrDefault(); person.ConnectionStatusValueId = customConnectionType ?? attendeeConnectionStatusId; person.RecordStatusValueId = recordStatusActiveId; } } string recordStatus = row[RecordStatus]; if ( !string.IsNullOrWhiteSpace( recordStatus ) ) { switch ( recordStatus.Trim().ToLower() ) { case "active": person.RecordStatusValueId = recordStatusActiveId; break; case "inactive": person.RecordStatusValueId = recordStatusInactiveId; break; default: person.RecordStatusValueId = recordStatusPendingId; break; } } string isDeceasedValue = row[IsDeceased]; if ( !string.IsNullOrWhiteSpace( isDeceasedValue ) ) { switch ( isDeceasedValue.Trim().ToLower() ) { case "y": case "yes": person.IsDeceased = true; person.RecordStatusReasonValueId = recordStatusDeceasedId; person.RecordStatusValueId = recordStatusInactiveId; break; default: person.IsDeceased = false; break; } } var personNumbers = new Dictionary<string, string>(); personNumbers.Add( "Home", row[HomePhone] ); personNumbers.Add( "Mobile", row[MobilePhone] ); personNumbers.Add( "Work", row[WorkPhone] ); string smsAllowed = row[AllowSMS]; foreach ( var numberPair in personNumbers.Where( n => !string.IsNullOrWhiteSpace( n.Value ) ) ) { var extension = string.Empty; var countryCode = Rock.Model.PhoneNumber.DefaultCountryCode(); var normalizedNumber = string.Empty; var countryIndex = numberPair.Value.IndexOf( '+' ); int extensionIndex = numberPair.Value.LastIndexOf( 'x' ) > 0 ? numberPair.Value.LastIndexOf( 'x' ) : numberPair.Value.Length; if ( countryIndex >= 0 ) { countryCode = numberPair.Value.Substring( countryIndex, countryIndex + 3 ).AsNumeric(); normalizedNumber = numberPair.Value.Substring( countryIndex + 3, extensionIndex - 3 ).AsNumeric().TrimStart( new Char[] { '0' } ); extension = numberPair.Value.Substring( extensionIndex ); } else if ( extensionIndex > 0 ) { normalizedNumber = numberPair.Value.Substring( 0, extensionIndex ).AsNumeric(); extension = numberPair.Value.Substring( extensionIndex ).AsNumeric(); } else { normalizedNumber = numberPair.Value.AsNumeric(); } if ( !string.IsNullOrWhiteSpace( normalizedNumber ) ) { var currentNumber = new PhoneNumber(); currentNumber.CountryCode = countryCode; currentNumber.CreatedByPersonAliasId = ImportPersonAliasId; currentNumber.Extension = extension.Left( 20 ); currentNumber.Number = normalizedNumber.TrimStart( new Char[] { '0' } ).Left( 20 ); currentNumber.NumberFormatted = PhoneNumber.FormattedNumber( currentNumber.CountryCode, currentNumber.Number ); currentNumber.NumberTypeValueId = numberTypeValues.Where( v => v.Value.Equals( numberPair.Key ) ) .Select( v => ( int? )v.Id ).FirstOrDefault(); if ( numberPair.Key == "Mobile" ) { switch ( smsAllowed.Trim().ToLower() ) { case "y": case "yes": case "active": currentNumber.IsMessagingEnabled = true; break; default: currentNumber.IsMessagingEnabled = false; break; } } person.PhoneNumbers.Add( currentNumber ); } } // Map Person attributes person.Attributes = new Dictionary<string, AttributeCache>(); person.AttributeValues = new Dictionary<string, AttributeValueCache>(); bool isEmailActive; switch ( row[IsEmailActive].Trim().ToLower() ) { case "n": case "no": case "inactive": isEmailActive = false; break; default: isEmailActive = true; break; } EmailPreference emailPreference; switch ( row[AllowBulkEmail].Trim().ToLower() ) { case "n": case "no": case "inactive": emailPreference = EmailPreference.NoMassEmails; break; default: emailPreference = EmailPreference.EmailAllowed; break; } person.EmailPreference = emailPreference; string primaryEmail = row[Email].Trim().Left( 75 ); if ( !string.IsNullOrWhiteSpace( primaryEmail ) ) { if ( primaryEmail.IsEmail() ) { person.Email = primaryEmail; person.IsEmailActive = isEmailActive; } else { LogException( "InvalidPrimaryEmail", string.Format( "PersonId: {0} - Email: {1}", rowPersonKey, primaryEmail ) ); } } string schoolName = row[School]; if ( !string.IsNullOrWhiteSpace( schoolName ) ) { // Add school if it doesn't exist Guid schoolGuid; var schoolExists = lookupContext.DefinedValues.Any( s => s.DefinedTypeId == schoolDefinedType.Id && s.Value.Equals( schoolName ) ); if ( !schoolExists ) { var newSchool = new DefinedValue(); newSchool.DefinedTypeId = schoolDefinedType.Id; newSchool.Value = schoolName; newSchool.Order = 0; lookupContext.DefinedValues.Add( newSchool ); lookupContext.SaveChanges(); schoolGuid = newSchool.Guid; } else { schoolGuid = lookupContext.DefinedValues.FirstOrDefault( s => s.Value.Equals( schoolName ) ).Guid; } AddPersonAttribute( schoolAttribute, person, schoolGuid.ToString().ToUpper() ); } foreach ( var attributePair in customAttributes ) { string newAttributeValue = row[attributePair.Key]; if ( !string.IsNullOrWhiteSpace( newAttributeValue ) ) { // check if this attribute value is a date DateTime valueAsDateTime; if ( DateTime.TryParseExact( newAttributeValue, dateFormats, CultureInfo.InvariantCulture, DateTimeStyles.None, out valueAsDateTime ) ) { newAttributeValue = valueAsDateTime.ToString( "yyyy-MM-dd" ); } int? newAttributeId = personAttributes.Where( a => a.Key == attributePair.Value.RemoveWhitespace() ) .Select( a => (int?)a.Id ).FirstOrDefault(); if ( newAttributeId != null ) { var newAttribute = AttributeCache.Read( (int)newAttributeId ); AddPersonAttribute( newAttribute, person, newAttributeValue ); } } } // Add notes to timeline var notePairs = new Dictionary<string, string>(); notePairs.Add( "General", row[GeneralNote] ); notePairs.Add( "Medical", row[MedicalNote] ); notePairs.Add( "Security", row[SecurityNote] ); foreach ( var notePair in notePairs.Where( n => !string.IsNullOrWhiteSpace( n.Value ) ) ) { var newNote = new Note(); newNote.NoteTypeId = personalNoteTypeId; newNote.CreatedByPersonAliasId = ImportPersonAliasId; newNote.CreatedDateTime = ImportDateTime; newNote.Text = notePair.Value; newNote.ForeignKey = rowPersonKey; newNote.ForeignId = rowPersonId; newNote.Caption = string.Format( "{0} Note", notePair.Key ); if ( !notePair.Key.Equals( "General" ) ) { newNote.IsAlert = true; } newNoteList.Add( newNote ); } #endregion person create var groupMember = new GroupMember(); groupMember.Person = person; groupMember.GroupRoleId = groupRoleId; groupMember.CreatedDateTime = ImportDateTime; groupMember.ModifiedDateTime = ImportDateTime; groupMember.CreatedByPersonAliasId = ImportPersonAliasId; groupMember.GroupMemberStatus = GroupMemberStatus.Active; if ( rowFamilyKey != currentFamilyGroup.ForeignKey ) { // person not part of the previous family, see if that family exists or create a new one currentFamilyGroup = ImportedFamilies.FirstOrDefault( g => g.ForeignKey == rowFamilyKey ); if ( currentFamilyGroup == null ) { currentFamilyGroup = CreateFamilyGroup( row[FamilyName], rowFamilyKey ); newFamilyList.Add( currentFamilyGroup ); newFamilies++; } else { lookupContext.Groups.Attach( currentFamilyGroup ); lookupContext.Entry( currentFamilyGroup ).State = EntityState.Modified; } currentFamilyGroup.Members.Add( groupMember ); } else { // person is part of this family group, check if they're a visitor if ( isFamilyRelationship || currentFamilyGroup.Members.Count() < 1 ) { currentFamilyGroup.Members.Add( groupMember ); } else { var visitorFamily = CreateFamilyGroup( person.LastName + " Family", rowFamilyKey ); visitorFamily.Members.Add( groupMember ); newFamilyList.Add( visitorFamily ); newVisitorList.Add( visitorFamily ); newFamilies++; } } // look ahead 1 row string rowNextFamilyKey = "-1"; if ( (row = csvData.Database.FirstOrDefault()) != null ) { rowNextFamilyKey = row[FamilyId]; } newPeople++; completed++; if ( completed % (ReportingNumber * 10) < 1 ) { ReportProgress( 0, string.Format( "{0:N0} people imported.", completed ) ); } if ( newPeople >= ReportingNumber && rowNextFamilyKey != currentFamilyGroup.ForeignKey ) { SaveIndividuals( newFamilyList, newVisitorList, newNoteList ); lookupContext.SaveChanges(); ReportPartialProgress(); // Clear out variables currentFamilyGroup = new Group(); newFamilyList.Clear(); newVisitorList.Clear(); newNoteList.Clear(); newPeople = 0; } } else { row = csvData.Database.FirstOrDefault(); } } // Save any changes to new families if ( newFamilyList.Any() ) { SaveIndividuals( newFamilyList, newVisitorList, newNoteList ); } // Save any changes to existing families lookupContext.SaveChanges(); DetachAllInContext( lookupContext ); lookupContext.Dispose(); ReportProgress( 0, string.Format( "Finished individual import: {0:N0} families and {1:N0} people added.", newFamilies, completed ) ); return completed; }
/// <summary> /// Loads the family data. /// </summary> /// <param name="csvData">The CSV data.</param> private int LoadFamily(CSVInstance csvData) { // Required variables var lookupContext = new RockContext(); var locationService = new LocationService(lookupContext); var newGroupLocations = new Dictionary <GroupLocation, string>(); var currentFamilyGroup = new Group(); var newFamilyList = new List <Group>(); var updatedFamilyList = new List <Group>(); var currentFamilyKey = string.Empty; var completed = 0; ReportProgress(0, $"Starting family import ({ImportedFamilies.Count:N0} already exist)."); string[] row; // Uses a look-ahead enumerator: this call will move to the next record immediately while ((row = csvData.Database.FirstOrDefault()) != null) { var rowFamilyKey = row[FamilyId]; var rowFamilyId = rowFamilyKey.AsType <int?>(); var rowFamilyName = row[FamilyName]; if (rowFamilyKey != null && rowFamilyKey != currentFamilyGroup.ForeignKey) { currentFamilyGroup = ImportedFamilies.FirstOrDefault(g => g.ForeignKey == rowFamilyKey); if (currentFamilyGroup == null) { currentFamilyGroup = new Group { ForeignKey = rowFamilyKey, ForeignId = rowFamilyId, Name = row[FamilyName], CreatedByPersonAliasId = ImportPersonAliasId, GroupTypeId = FamilyGroupTypeId }; newFamilyList.Add(currentFamilyGroup); } else { currentFamilyGroup.Name = row[FamilyName]; lookupContext.Groups.Attach(currentFamilyGroup); } // Set the family campus var campusName = row[Campus]; if (!string.IsNullOrWhiteSpace(campusName)) { var familyCampus = CampusList.FirstOrDefault(c => c.Name.Equals(campusName, StringComparison.InvariantCultureIgnoreCase) || c.ShortCode.Equals(campusName, StringComparison.InvariantCultureIgnoreCase)); if (familyCampus == null) { familyCampus = new Campus { IsSystem = false, Name = campusName, ShortCode = campusName.RemoveWhitespace(), IsActive = true }; lookupContext.Campuses.Add(familyCampus); lookupContext.SaveChanges(DisableAuditing); CampusList.Add(familyCampus); } currentFamilyGroup.CampusId = familyCampus.Id; } // Add the family addresses since they exist in this file var famAddress = row[Address]; var famAddress2 = row[Address2]; var famCity = row[City]; var famState = row[State]; var famZip = row[Zip]; var famCountry = row[Country]; var primaryAddress = locationService.Get(famAddress, famAddress2, famCity, famState, famZip, famCountry, verifyLocation: false); if (primaryAddress != null && currentFamilyGroup.GroupLocations.Count == 0) { var primaryLocation = new GroupLocation { LocationId = primaryAddress.Id, IsMailingLocation = true, IsMappedLocation = true, GroupLocationTypeValueId = HomeLocationTypeId }; newGroupLocations.Add(primaryLocation, rowFamilyKey); } var famSecondAddress = row[SecondaryAddress]; var famSecondAddress2 = row[SecondaryAddress2]; var famSecondCity = row[SecondaryCity]; var famSecondState = row[SecondaryState]; var famSecondZip = row[SecondaryZip]; var famSecondCountry = row[SecondaryCountry]; var secondaryAddress = locationService.Get(famSecondAddress, famSecondAddress2, famSecondCity, famSecondState, famSecondZip, famSecondCountry, verifyLocation: false); if (secondaryAddress != null && currentFamilyGroup.GroupLocations.Count < 2) { var secondaryLocation = new GroupLocation { LocationId = secondaryAddress.Id, IsMailingLocation = true, IsMappedLocation = false, GroupLocationTypeValueId = PreviousLocationTypeId }; newGroupLocations.Add(secondaryLocation, rowFamilyKey); } var createdDateValue = ParseDateOrDefault(row[CreatedDate], ImportDateTime); currentFamilyGroup.CreatedDateTime = createdDateValue; currentFamilyGroup.ModifiedDateTime = ImportDateTime; completed++; if (completed % (ReportingNumber * 10) < 1) { ReportProgress(0, $"{completed:N0} families imported."); } else if (completed % ReportingNumber < 1) { SaveFamilies(newFamilyList, newGroupLocations); ReportPartialProgress(); // Reset lookup context lookupContext.SaveChanges(); lookupContext = new RockContext(); locationService = new LocationService(lookupContext); newFamilyList.Clear(); newGroupLocations.Clear(); } } } // Check to see if any rows didn't get saved to the database if (newGroupLocations.Any()) { SaveFamilies(newFamilyList, newGroupLocations); } lookupContext.SaveChanges(); DetachAllInContext(lookupContext); lookupContext.Dispose(); ReportProgress(0, $"Finished family import: {completed:N0} families added or updated."); return(completed); }
/// <summary> /// Loads the family data. /// </summary> /// <param name="csvData">The CSV data.</param> private int LoadFamily(CSVInstance csvData) { // Required variables var lookupContext = new RockContext(); var locationService = new LocationService(lookupContext); int familyGroupTypeId = GroupTypeCache.GetFamilyGroupType().Id; int numImportedFamilies = ImportedFamilies.Count(); int homeLocationTypeId = DefinedValueCache.Read(new Guid(Rock.SystemGuid.DefinedValue.GROUP_LOCATION_TYPE_HOME)).Id; int workLocationTypeId = DefinedValueCache.Read(new Guid(Rock.SystemGuid.DefinedValue.GROUP_LOCATION_TYPE_WORK)).Id; var newGroupLocations = new Dictionary <GroupLocation, string>(); var currentFamilyGroup = new Group(); var newFamilyList = new List <Group>(); var updatedFamilyList = new List <Group>(); var dateFormats = new[] { "yyyy-MM-dd", "MM/dd/yyyy", "MM/dd/yy" }; string currentFamilyKey = string.Empty; int completed = 0; ReportProgress(0, string.Format("Starting family import ({0:N0} already exist).", numImportedFamilies)); string[] row; // Uses a look-ahead enumerator: this call will move to the next record immediately while ((row = csvData.Database.FirstOrDefault()) != null) { string rowFamilyKey = row[FamilyId]; int? rowFamilyId = rowFamilyKey.AsType <int?>(); string rowFamilyName = row[FamilyName]; if (rowFamilyKey != null && rowFamilyKey != currentFamilyGroup.ForeignKey) { currentFamilyGroup = ImportedFamilies.FirstOrDefault(g => g.ForeignKey == rowFamilyKey); if (currentFamilyGroup == null) { currentFamilyGroup = new Group(); currentFamilyGroup.ForeignKey = rowFamilyKey; currentFamilyGroup.ForeignId = rowFamilyId; currentFamilyGroup.Name = row[FamilyName]; currentFamilyGroup.CreatedByPersonAliasId = ImportPersonAliasId; currentFamilyGroup.GroupTypeId = familyGroupTypeId; newFamilyList.Add(currentFamilyGroup); } else { lookupContext.Groups.Attach(currentFamilyGroup); } // Set the family campus string campusName = row[Campus]; if (!string.IsNullOrWhiteSpace(campusName)) { var familyCampus = CampusList.Where(c => c.Name.Equals(campusName, StringComparison.InvariantCultureIgnoreCase) || c.ShortCode.Equals(campusName, StringComparison.InvariantCultureIgnoreCase)).FirstOrDefault(); if (familyCampus == null) { familyCampus = new Campus(); familyCampus.IsSystem = false; familyCampus.Name = campusName; familyCampus.ShortCode = campusName.RemoveWhitespace(); lookupContext.Campuses.Add(familyCampus); lookupContext.SaveChanges(DisableAuditing); CampusList.Add(familyCampus); } currentFamilyGroup.CampusId = familyCampus.Id; } // Add the family addresses since they exist in this file string famAddress = row[Address]; string famAddress2 = row[Address2]; string famCity = row[City]; string famState = row[State]; string famZip = row[Zip]; string famCountry = row[Country]; Location primaryAddress = locationService.Get(famAddress, famAddress2, famCity, famState, famZip, famCountry, verifyLocation: false); if (primaryAddress != null) { var primaryLocation = new GroupLocation(); primaryLocation.LocationId = primaryAddress.Id; primaryLocation.IsMailingLocation = true; primaryLocation.IsMappedLocation = true; primaryLocation.GroupLocationTypeValueId = homeLocationTypeId; newGroupLocations.Add(primaryLocation, rowFamilyKey); } string famSecondAddress = row[SecondaryAddress]; string famSecondAddress2 = row[SecondaryAddress2]; string famSecondCity = row[SecondaryCity]; string famSecondState = row[SecondaryState]; string famSecondZip = row[SecondaryZip]; string famSecondCountry = row[SecondaryCountry]; Location secondaryAddress = locationService.Get(famSecondAddress, famSecondAddress2, famSecondCity, famSecondState, famSecondZip, famSecondCountry, verifyLocation: false); if (secondaryAddress != null) { var secondaryLocation = new GroupLocation(); secondaryLocation.LocationId = secondaryAddress.Id; secondaryLocation.IsMailingLocation = true; secondaryLocation.IsMappedLocation = true; secondaryLocation.GroupLocationTypeValueId = workLocationTypeId; newGroupLocations.Add(secondaryLocation, rowFamilyKey); } DateTime createdDateValue; if (DateTime.TryParseExact(row[CreatedDate], dateFormats, CultureInfo.InvariantCulture, DateTimeStyles.None, out createdDateValue)) { currentFamilyGroup.CreatedDateTime = createdDateValue; currentFamilyGroup.ModifiedDateTime = ImportDateTime; } else { currentFamilyGroup.CreatedDateTime = ImportDateTime; currentFamilyGroup.ModifiedDateTime = ImportDateTime; } completed++; if (completed % (ReportingNumber * 10) < 1) { ReportProgress(0, string.Format("{0:N0} families imported.", completed)); } else if (completed % ReportingNumber < 1) { SaveFamilies(newFamilyList, newGroupLocations); ReportPartialProgress(); // Reset lookup context lookupContext.SaveChanges(); lookupContext = new RockContext(); locationService = new LocationService(lookupContext); newFamilyList.Clear(); newGroupLocations.Clear(); } } } // Check to see if any rows didn't get saved to the database if (newGroupLocations.Any()) { SaveFamilies(newFamilyList, newGroupLocations); } lookupContext.SaveChanges(); DetachAllInContext(lookupContext); lookupContext.Dispose(); ReportProgress(0, string.Format("Finished family import: {0:N0} families added or updated.", completed)); return(completed); }
/// <summary> /// Maps the batch data. /// </summary> /// <param name="csvData">The table data.</param> /// <exception cref="System.NotImplementedException"></exception> private int MapBatch(CSVInstance csvData) { var newBatches = new List <FinancialBatch>(); var earliestBatchDate = ImportDateTime; var completed = 0; ReportProgress(0, $"Verifying batch import ({ImportedBatches.Count:N0} already exist)."); string[] row; // Uses a look-ahead enumerator: this call will move to the next record immediately while ((row = csvData.Database.FirstOrDefault()) != null) { var batchIdKey = row[BatchID]; var batchId = batchIdKey.AsType <int?>(); if (batchId != null && !ImportedBatches.ContainsKey((int)batchId)) { var batch = new FinancialBatch { CreatedByPersonAliasId = ImportPersonAliasId, ForeignKey = batchId.ToString(), ForeignId = batchId, Note = string.Empty, Status = BatchStatus.Closed, AccountingSystemCode = string.Empty }; var name = row[BatchName] as string; if (!string.IsNullOrWhiteSpace(name)) { name = name.Trim(); batch.Name = name.Left(50); batch.CampusId = CampusList.Where(c => name.StartsWith(c.Name) || name.StartsWith(c.ShortCode)) .Select(c => (int?)c.Id).FirstOrDefault(); } var batchDate = ParseDateOrDefault(row[BatchDate], null); if (batchDate.HasValue) { batch.BatchStartDateTime = batchDate; batch.BatchEndDateTime = batchDate; if (earliestBatchDate > batchDate) { earliestBatchDate = (DateTime)batchDate; } } var amountKey = row[BatchAmount]; var amount = amountKey.AsType <decimal?>(); if (amount != null) { batch.ControlAmount = amount.HasValue ? amount.Value : new decimal(); } newBatches.Add(batch); completed++; if (completed % (ReportingNumber * 10) < 1) { ReportProgress(0, $"{completed:N0} batches imported."); } else if (completed % ReportingNumber < 1) { SaveFinancialBatches(newBatches); foreach (var b in newBatches) { if (!ImportedBatches.ContainsKey((int)b.ForeignId)) { ImportedBatches.Add((int)b.ForeignId, b.Id); } else { LogException("Duplicate Batch", string.Format("Batch #{0} is a duplicate and will be skipped. Please check the source data.", b.ForeignId)); } } newBatches.Clear(); ReportPartialProgress(); } } } // add a default batch to use with contributions if (!ImportedBatches.ContainsKey(0)) { var defaultBatch = new FinancialBatch { CreatedDateTime = ImportDateTime, CreatedByPersonAliasId = ImportPersonAliasId, Status = BatchStatus.Closed, BatchStartDateTime = earliestBatchDate, Name = $"Default Batch {ImportDateTime}", ControlAmount = 0.0m, ForeignKey = "0", ForeignId = 0 }; newBatches.Add(defaultBatch); } if (newBatches.Any()) { SaveFinancialBatches(newBatches); newBatches.ForEach(b => ImportedBatches.Add((int)b.ForeignId, (int?)b.Id)); } ReportProgress(100, $"Finished batch import: {completed:N0} batches imported."); return(completed); }