/// <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);
}