/// <summary> /// Maps the contribution. /// </summary> /// <param name="tableData">The table data.</param> /// <param name="selectedColumns">The selected columns.</param> private void MapContribution(IQueryable <Row> tableData, List <string> selectedColumns = null) { 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; int totalRows = tableData.Count(); int percentage = (totalRows - 1) / 100 + 1; ReportProgress(0, string.Format("Verifying contribution import ({0:N0} found, {1:N0} already exist).", totalRows, importedContributions.Count)); foreach (var row in tableData.Where(r => r != null)) { int?individualId = row["Individual_ID"] as int?; int?householdId = row["Household_ID"] as int?; int?contributionId = row["ContributionID"] as 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, householdId); 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; } int?batchId = row["BatchID"] as 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; } DateTime?receivedDate = row["Received_Date"] as DateTime?; if (receivedDate != null) { transaction.TransactionDateTime = receivedDate; transaction.CreatedDateTime = receivedDate; transaction.ModifiedDateTime = ImportDateTime; } string cardType = row["Card_Type"] as string; string cardLastFour = row["Last_Four"] as string; string contributionType = row["Contribution_Type_Name"].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; if (cardType != null) { creditCardTypeId = creditCardTypes.Where(t => t.Value.Equals(cardType)).Select(t => (int?)t.Id).FirstOrDefault(); } } 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.AccountNumberMasked = cardLastFour; paymentDetail.ForeignKey = contributionId.ToString(); paymentDetail.ForeignId = contributionId; transaction.FinancialPaymentDetail = paymentDetail; } string checkNumber = row["Check_Number"] 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["Fund_Name"] as string; string subFund = row["Sub_Fund_Name"] as string; string fundGLAccount = row["Fund_GL_Account"] as string; string subFundGLAccount = row["Sub_Fund_GL_Account"] as string; Boolean?isFundActive = row["Fund_Is_active"] as Boolean?; decimal?statedValue = row["Stated_Value"] as decimal?; decimal?amount = row["Amount"] as 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 (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, 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 % percentage < 1) { int percentComplete = completed / percentage; ReportProgress(percentComplete, string.Format("{0:N0} contributions imported ({1}% complete).", completed, percentComplete)); } 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)); }
/// <summary> /// Maps the contribution. /// </summary> /// <param name="tableData">The table data.</param> /// <param name="selectedColumns">The selected columns.</param> private void MapContribution( IQueryable<Row> tableData, List<string> selectedColumns = null ) { 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; int totalRows = tableData.Count(); int percentage = ( totalRows - 1 ) / 100 + 1; ReportProgress( 0, string.Format( "Verifying contribution import ({0:N0} found, {1:N0} already exist).", totalRows, importedContributions.Count ) ); foreach ( var row in tableData.Where( r => r != null ) ) { int? individualId = row["Individual_ID"] as int?; int? householdId = row["Household_ID"] as int?; int? contributionId = row["ContributionID"] as 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, householdId ); 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; } int? batchId = row["BatchID"] as 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; } DateTime? receivedDate = row["Received_Date"] as DateTime?; if ( receivedDate != null ) { transaction.TransactionDateTime = receivedDate; transaction.CreatedDateTime = receivedDate; transaction.ModifiedDateTime = ImportDateTime; } string cardType = row["Card_Type"] as string; string cardLastFour = row["Last_Four"] as string; string contributionType = row["Contribution_Type_Name"].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; if ( cardType != null ) { creditCardTypeId = creditCardTypes.Where( t => t.Value.Equals( cardType ) ).Select( t => (int?)t.Id ).FirstOrDefault(); } } 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.AccountNumberMasked = cardLastFour; paymentDetail.ForeignKey = contributionId.ToString(); paymentDetail.ForeignId = contributionId; transaction.FinancialPaymentDetail = paymentDetail; } string checkNumber = row["Check_Number"] 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["Fund_Name"] as string; string subFund = row["Sub_Fund_Name"] as string; string fundGLAccount = row["Fund_GL_Account"] as string; string subFundGLAccount = row["Sub_Fund_GL_Account"] as string; Boolean? isFundActive = row["Fund_Is_active"] as Boolean?; decimal? statedValue = row["Stated_Value"] as decimal?; decimal? amount = row["Amount"] as 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 ( 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, 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 % percentage < 1 ) { int percentComplete = completed / percentage; ReportProgress( percentComplete, string.Format( "{0:N0} contributions imported ({1}% complete).", completed, percentComplete ) ); } 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 ) ); }
/// <summary> /// Maps the pledge. /// </summary> /// <param name="queryable">The queryable.</param> /// <exception cref="System.NotImplementedException"></exception> private void MapPledge( IQueryable<Row> tableData ) { 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; int totalRows = tableData.Count(); int percentage = ( totalRows - 1 ) / 100 + 1; ReportProgress( 0, string.Format( "Verifying pledge import ({0:N0} found).", totalRows ) ); foreach ( var row in tableData.Where( r => r != null ) ) { decimal? amount = row["Total_Pledge"] as decimal?; DateTime? startDate = row["Start_Date"] as DateTime?; DateTime? endDate = row["End_Date"] as DateTime?; if ( amount != null && startDate != null && endDate != null ) { int? individualId = row["Individual_ID"] as int?; int? householdId = row["Household_ID"] as int?; var personKeys = GetPersonKeys( individualId, householdId, includeVisitors: false ); if ( personKeys != null && personKeys.PersonAliasId > 0 ) { var pledge = new FinancialPledge(); pledge.PersonAliasId = personKeys.PersonAliasId; pledge.CreatedByPersonAliasId = ImportPersonAliasId; pledge.ModifiedDateTime = ImportDateTime; pledge.StartDate = (DateTime)startDate; pledge.EndDate = (DateTime)endDate; pledge.TotalAmount = (decimal)amount; pledge.CreatedDateTime = ImportDateTime; pledge.ModifiedDateTime = ImportDateTime; pledge.ModifiedByPersonAliasId = ImportPersonAliasId; string frequency = row["Pledge_Frequency_Name"].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["Fund_Name"] as string; string subFund = row["Sub_Fund_Name"] as string; 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, null ); 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, null ); accountList.Add( childAccount ); } pledge.AccountId = childAccount.Id; } else { pledge.AccountId = parentAccount.Id; } } newPledges.Add( pledge ); completed++; if ( completed % percentage < 1 ) { int percentComplete = completed / percentage; ReportProgress( percentComplete, string.Format( "{0:N0} pledges imported ({1}% complete).", completed, percentComplete ) ); } 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 ) ); }
/// <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 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> /// Maps the contribution. /// </summary> /// <param name="tableData">The table data.</param> /// <param name="totalRows">The total rows.</param> private void MapContribution(IQueryable <Row> tableData, long totalRows = 0) { var lookupContext = new RockContext(); var transactionTypeContributionId = DefinedValueCache.Get(Rock.SystemGuid.DefinedValue.TRANSACTION_TYPE_CONTRIBUTION.AsGuid(), lookupContext).Id; var currencyTypes = DefinedTypeCache.Get(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.Get(new Guid(Rock.SystemGuid.DefinedType.FINANCIAL_CREDIT_CARD_TYPE)).DefinedValues; var refundReasons = DefinedTypeCache.Get(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>(); if (totalRows == 0) { totalRows = tableData.Count(); } var completedItems = 0; var percentage = (totalRows - 1) / 100 + 1; ReportProgress(0, $"Verifying contribution import ({totalRows:N0} found, {importedContributions.Count:N0} already exist)."); foreach (var row in tableData.Where(r => r != null)) { var individualId = row["Individual_ID"] as int?; var householdId = row["Household_ID"] as int?; var contributionId = row["ContributionID"] as int?; if (contributionId.HasValue && !importedContributions.ContainsKey(( int )contributionId)) { var transaction = new FinancialTransaction { CreatedByPersonAliasId = ImportPersonAliasId, ModifiedByPersonAliasId = ImportPersonAliasId, TransactionTypeValueId = transactionTypeContributionId, ForeignKey = contributionId.ToString(), ForeignId = contributionId }; int?giverAliasId = null; var personKeys = GetPersonKeys(individualId, householdId); if (personKeys != null && personKeys.PersonAliasId > 0) { giverAliasId = personKeys.PersonAliasId; transaction.CreatedByPersonAliasId = giverAliasId; transaction.AuthorizedPersonAliasId = giverAliasId; transaction.ProcessedByPersonAliasId = giverAliasId; } var summary = row["Memo"] as string; if (!string.IsNullOrWhiteSpace(summary)) { transaction.Summary = summary; } var batchId = row["BatchID"] as int?; if (batchId.HasValue && 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 receivedDate = row["Received_Date"] as DateTime?; if (receivedDate.HasValue) { transaction.TransactionDateTime = receivedDate; transaction.CreatedDateTime = receivedDate; transaction.ModifiedDateTime = ImportDateTime; } var contributionFields = row.Columns.Select(c => c.Name).ToList(); var cardType = contributionFields.Contains("Card_Type") ? row["Card_Type"] as string : string.Empty; var cardLastFour = contributionFields.Contains("Last_Four") ? row["Last_Four"] as string : string.Empty; var contributionType = contributionFields.Contains("Contribution_Type_Name") ? row["Contribution_Type_Name"] as string : string.Empty; if (!string.IsNullOrWhiteSpace(contributionType)) { // set default source to onsite, exceptions listed below transaction.SourceTypeValueId = TransactionSourceTypeOnsiteId; int?paymentCurrencyTypeId = null, creditCardTypeId = null; switch (contributionType.ToLower()) { case "cash": paymentCurrencyTypeId = currencyTypeCash; break; case "check": paymentCurrencyTypeId = currencyTypeCheck; break; case "ach": paymentCurrencyTypeId = currencyTypeACH; transaction.SourceTypeValueId = TransactionSourceTypeWebsiteId; break; case "credit card": paymentCurrencyTypeId = currencyTypeCreditCard; transaction.SourceTypeValueId = TransactionSourceTypeWebsiteId; if (!string.IsNullOrWhiteSpace(cardType)) { creditCardTypeId = creditCardTypes.Where(t => t.Value.Equals(cardType, StringComparison.OrdinalIgnoreCase)) .Select(t => ( int? )t.Id).FirstOrDefault(); } break; default: paymentCurrencyTypeId = currencyTypeNonCash; break; } var paymentDetail = new FinancialPaymentDetail { CreatedDateTime = receivedDate, CreatedByPersonAliasId = giverAliasId, ModifiedDateTime = ImportDateTime, ModifiedByPersonAliasId = giverAliasId, CurrencyTypeValueId = paymentCurrencyTypeId, CreditCardTypeValueId = creditCardTypeId, AccountNumberMasked = cardLastFour, ForeignKey = contributionId.ToString(), ForeignId = contributionId }; transaction.FinancialPaymentDetail = paymentDetail; } var checkNumber = row["Check_Number"] as string; // if the check number is valid, put it in the transaction code if (checkNumber.AsType <int?>().HasValue) { transaction.TransactionCode = checkNumber; } // check for SecureGive kiosk transactions else if (!string.IsNullOrWhiteSpace(checkNumber) && checkNumber.StartsWith("SG")) { transaction.SourceTypeValueId = TransactionSourceTypeKioskId; } var fundName = contributionFields.Contains("Fund_Name") ? row["Fund_Name"] as string : string.Empty; var fundType = contributionFields.Contains("Fund_type") ? row["Fund_type"] as string : string.Empty; var subFund = contributionFields.Contains("Sub_Fund_Name") ? row["Sub_Fund_Name"] as string : string.Empty; var fundGLAccount = contributionFields.Contains("Fund_GL_Account") ? row["Fund_GL_Account"] as string : string.Empty; var subFundGLAccount = contributionFields.Contains("Sub_Fund_GL_Account") ? row["Sub_Fund_GL_Account"] as string : string.Empty; var isFundActive = contributionFields.Contains("Fund_Is_active") ? row["Fund_Is_active"] as string : null; var statedValue = row["Stated_Value"] as decimal?; var amount = row["Amount"] as decimal?; if (!string.IsNullOrWhiteSpace(fundName) && amount.HasValue) { int transactionAccountId; var parentAccount = accountList.FirstOrDefault(a => !a.CampusId.HasValue && a.Name.Equals(fundName.Truncate(50), StringComparison.OrdinalIgnoreCase)); if (parentAccount == null) { int?accountTypeDefinedTypeId = null; if (!string.IsNullOrWhiteSpace(fundType)) { accountTypeDefinedTypeId = LoadAccountTypeDefinedValue(lookupContext, fundType); } parentAccount = AddFinancialAccount(lookupContext, fundName, $"{fundName} imported {ImportDateTime}", fundGLAccount, null, null, isFundActive.AsBooleanOrNull(), receivedDate, fundName.RemoveSpecialCharacters(), accountTypeValueId: accountTypeDefinedTypeId); 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, StringComparison.OrdinalIgnoreCase) || subFund.StartsWith(c.ShortCode, StringComparison.OrdinalIgnoreCase)); 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 = $"{subFund} {fundName}"; var childAccount = accountList.FirstOrDefault(c => c.ParentAccountId == parentAccount.Id && c.Name.Equals(subFund.Truncate(50), StringComparison.OrdinalIgnoreCase)); if (childAccount == null) { // create a child account with a campusId if it was set childAccount = AddFinancialAccount(lookupContext, subFund, $"{subFund} imported {ImportDateTime}", subFundGLAccount, campusFundId, parentAccount.Id, isFundActive.AsBooleanOrNull(), receivedDate, subFund.RemoveSpecialCharacters(), accountTypeValueId: parentAccount.AccountTypeValueId); accountList.Add(childAccount); } transactionAccountId = childAccount.Id; } else { transactionAccountId = parentAccount.Id; } if (amount == 0 && statedValue.HasValue && 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); completedItems++; if (completedItems % percentage < 1) { var percentComplete = completedItems / percentage; ReportProgress(percentComplete, $"{completedItems:N0} contributions imported ({percentComplete}% complete)."); } if (completedItems % ReportingNumber < 1) { SaveContributions(newTransactions); newTransactions.Clear(); ReportPartialProgress(); } } } if (newTransactions.Any()) { SaveContributions(newTransactions); } ReportProgress(100, $"Finished contribution import: {completedItems:N0} contributions imported."); }
/// <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> /// Maps the contribution. /// </summary> /// <param name="tableData">The table data.</param> /// <param name="selectedColumns">The selected columns.</param> private void MapContribution(IQueryable <Row> tableData, List <string> selectedColumns = null) { 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 transactionTypeEventRegistrationId = DefinedValueCache.Read(new Guid(Rock.SystemGuid.DefinedValue.TRANSACTION_TYPE_EVENT_REGISTRATION), lookupContext).Id; int currencyTypeACH = DefinedValueCache.Read(new Guid(Rock.SystemGuid.DefinedValue.CURRENCY_TYPE_ACH), lookupContext).Id; int currencyTypeCash = DefinedValueCache.Read(new Guid(Rock.SystemGuid.DefinedValue.CURRENCY_TYPE_CASH), lookupContext).Id; int currencyTypeCheck = DefinedValueCache.Read(new Guid(Rock.SystemGuid.DefinedValue.CURRENCY_TYPE_CHECK), lookupContext).Id; int currencyTypeCreditCard = DefinedValueCache.Read(new Guid(Rock.SystemGuid.DefinedValue.CURRENCY_TYPE_CREDIT_CARD), lookupContext).Id; var refundReasons = DefinedTypeCache.Read(new Guid(Rock.SystemGuid.DefinedType.FINANCIAL_TRANSACTION_REFUND_REASON), lookupContext).DefinedValues; List <FinancialPledge> pledgeList = new FinancialPledgeService(lookupContext).Queryable().ToList(); List <FinancialAccount> accountList = new FinancialAccountService(lookupContext).Queryable().ToList(); // Get all imported contributions var importedContributions = new FinancialTransactionService(lookupContext).Queryable() .Select(t => new { ContributionId = t.ForeignId, TransactionId = t.Id }) .ToDictionary(t => t.ContributionId.AsType <int?>(), t => (int?)t.TransactionId); var householdAVList = new AttributeValueService(lookupContext).Queryable().Where(av => av.AttributeId == HouseholdAttributeId).ToList(); // List for batching new contributions var newTransactions = new List <FinancialTransaction>(); int completed = 0; int totalRows = tableData.Count(); int percentage = (totalRows - 1) / 100 + 1; ReportProgress(0, string.Format("Verifying contribution import ({0:N0} found, {1:N0} already exist).", totalRows, importedContributions.Count())); foreach (var row in tableData) { int?individualId = row["Individual_ID"] as int?; int?householdId = row["Household_ID"] as int?; int?contributionId = row["ContributionID"] as int?; if (contributionId != null && !importedContributions.ContainsKey(contributionId)) { var transaction = new FinancialTransaction(); string fundName = row["Fund_Name"] as string; //Crossroads - Anything under a fund name that starts with Receipt - is an Event Registration. if (fundName.StartsWith("Receipt -")) { transaction.TransactionTypeValueId = transactionTypeEventRegistrationId; } else { transaction.TransactionTypeValueId = transactionTypeContributionId; } int?associatedPersonId; if (individualId != null) { associatedPersonId = GetPersonAliasId(individualId, householdId); } //will get the exact person if Individual Id is not null. else { associatedPersonId = GetPersonId(householdAVList, householdId); } //Will attempt to get the Head first, then Spouse, then Child. Will exclude Other and Visitor if (associatedPersonId != null) { transaction.AuthorizedPersonAliasId = associatedPersonId; transaction.CreatedByPersonAliasId = ImportPersonAlias.Id; transaction.ProcessedByPersonAliasId = associatedPersonId; transaction.ForeignId = contributionId.ToString(); string summary = row["Memo"] as string; if (summary != null) { transaction.Summary = summary; } int?batchId = row["BatchID"] as int?; if (batchId != null && ImportedBatches.Any(b => b.Key == batchId)) { transaction.BatchId = ImportedBatches.FirstOrDefault(b => b.Key == batchId).Value; } DateTime?receivedDate = row["Received_Date"] as DateTime?; if (receivedDate != null) { transaction.TransactionDateTime = receivedDate; transaction.CreatedDateTime = receivedDate; } bool isTypeNonCash = false; string contributionType = row["Contribution_Type_Name"].ToString().ToLower(); if (contributionType != null) { if (contributionType == "ach") { transaction.CurrencyTypeValueId = currencyTypeACH; } else if (contributionType == "cash") { transaction.CurrencyTypeValueId = currencyTypeCash; } else if (contributionType == "check") { transaction.CurrencyTypeValueId = currencyTypeCheck; } else if (contributionType == "credit card") { transaction.CurrencyTypeValueId = currencyTypeCreditCard; } else { isTypeNonCash = true; } } string checkNumber = row["Check_Number"] as string; if (checkNumber != null && checkNumber.AsType <int?>() != null) { // routing & account set to zero transaction.CheckMicrEncrypted = Encryption.EncryptString(string.Format("{0}_{1}_{2}", 0, 0, checkNumber)); } decimal?amount = row["Amount"] as decimal?; if (fundName != null & amount != null) { FinancialAccount matchingAccount = null; int? parentAccountId = null; string parentAccountName = String.Empty; int? fundCampusId = null; fundName = fundName.Trim(); string subFund = row["Sub_Fund_Name"] as string; if (subFund != null) { subFund = subFund.Trim(); // Check if subfund was used to mark a multi-site campus fundCampusId = CampusList.Where(c => subFund.StartsWith(c.Name) || subFund.StartsWith(c.ShortCode)) .Select(c => (int?)c.Id).FirstOrDefault(); // Matched a campus, check to see if an account exists for that campus already if (fundCampusId != null) { matchingAccount = accountList.FirstOrDefault(a => a.Name.Equals(fundName) && a.CampusId != null && a.CampusId.Equals(fundCampusId)); } else { // No campus match, look for an account that matches parent name and subfund name matchingAccount = accountList.FirstOrDefault(a => a.ParentAccountId != null && a.ParentAccount.Name.Equals(fundName) && a.Name.Equals(subFund)); if (matchingAccount == null) { // Check if a parent account exists already FinancialAccount parentAccount = accountList.FirstOrDefault(a => a.Name.Equals(fundName)); if (parentAccount == null) { parentAccount = AddAccount(lookupContext, fundName, fundCampusId); accountList.Add(parentAccount); } // set data for subfund to be created parentAccountId = parentAccount.Id; fundName = subFund; parentAccountName = parentAccount.Name; } } } else { matchingAccount = accountList.FirstOrDefault(a => a.Name.Equals(fundName) && a.CampusId == null); } if (matchingAccount == null) { // No account matches, create the new account with campus Id and parent Id if they were set matchingAccount = AddAccount(lookupContext, fundName, fundCampusId, parentAccountName, parentAccountId); accountList.Add(matchingAccount); } var transactionDetail = new FinancialTransactionDetail(); transactionDetail.Amount = (decimal)amount; transactionDetail.CreatedDateTime = receivedDate; transactionDetail.AccountId = matchingAccount.Id; transactionDetail.IsNonCash = isTypeNonCash; transaction.TransactionDetails.Add(transactionDetail); if (amount < 0) { var transactionRefund = new FinancialTransactionRefund(); transactionRefund.CreatedDateTime = receivedDate; transactionRefund.RefundReasonSummary = summary; transactionRefund.RefundReasonValueId = refundReasons.Where(dv => summary != null && dv.Value.Contains(summary)) .Select(dv => (int?)dv.Id).FirstOrDefault(); transaction.Refund = transactionRefund; } } newTransactions.Add(transaction); completed++; if (completed % percentage < 1) { int percentComplete = completed / percentage; ReportProgress(percentComplete, string.Format("{0:N0} contributions imported ({1}% complete).", completed, percentComplete)); } 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)); }
/// <summary> /// Maps the pledge. /// </summary> /// <param name="queryable">The queryable.</param> /// <exception cref="System.NotImplementedException"></exception> private void MapPledge(IQueryable <Row> tableData) { var lookupContext = new RockContext(); List <FinancialAccount> accountList = new FinancialAccountService(lookupContext).Queryable().ToList(); List <FinancialPledge> importedPledges = new FinancialPledgeService(lookupContext).Queryable().ToList(); var pledgeFrequencies = DefinedTypeCache.Read(new Guid(Rock.SystemGuid.DefinedType.FINANCIAL_FREQUENCY), lookupContext).DefinedValues; var newPledges = new List <FinancialPledge>(); int completed = 0; int totalRows = tableData.Count(); int percentage = (totalRows - 1) / 100 + 1; ReportProgress(0, string.Format("Verifying pledge import ({0:N0} found).", totalRows)); foreach (var row in tableData) { decimal? amount = row["Total_Pledge"] as decimal?; DateTime?startDate = row["Start_Date"] as DateTime?; DateTime?endDate = row["End_Date"] as DateTime?; if (amount != null && startDate != null && endDate != null) { int?individualId = row["Individual_ID"] as int?; int?householdId = row["Household_ID"] as int?; int?personId = GetPersonAliasId(individualId, householdId); if (personId != null && !importedPledges.Any(p => p.PersonAliasId == personId && p.TotalAmount == amount && p.StartDate.Equals(startDate))) { var pledge = new FinancialPledge(); pledge.CreatedByPersonAliasId = ImportPersonAlias.Id; pledge.StartDate = (DateTime)startDate; pledge.EndDate = (DateTime)endDate; pledge.TotalAmount = (decimal)amount; string frequency = row["Pledge_Frequency_Name"].ToString().ToLower(); if (frequency != null) { if (frequency == "one time" || frequency == "as can") { pledge.PledgeFrequencyValueId = pledgeFrequencies.FirstOrDefault(f => f.Guid == new Guid(Rock.SystemGuid.DefinedValue.TRANSACTION_FREQUENCY_ONE_TIME)).Id; } else { pledge.PledgeFrequencyValueId = pledgeFrequencies .Where(f => f.Value.ToLower().StartsWith(frequency) || f.Description.ToLower().StartsWith(frequency)) .Select(f => f.Id).FirstOrDefault(); } } string fundName = row["Fund_Name"] as string; string subFund = row["Sub_Fund_Name"] as string; if (fundName != null) { FinancialAccount matchingAccount = null; int?fundCampusId = null; if (subFund != null) { // match by campus if the subfund appears to be a campus fundCampusId = CampusList.Where(c => c.Name.StartsWith(subFund) || c.ShortCode == subFund) .Select(c => (int?)c.Id).FirstOrDefault(); if (fundCampusId != null) { matchingAccount = accountList.FirstOrDefault(a => a.Name.StartsWith(fundName) && a.CampusId != null && a.CampusId.Equals(fundCampusId)); } else { matchingAccount = accountList.FirstOrDefault(a => a.Name.StartsWith(fundName) && a.Name.StartsWith(subFund)); } } else { matchingAccount = accountList.FirstOrDefault(a => a.Name.StartsWith(fundName)); } if (matchingAccount == null) { matchingAccount = AddAccount(lookupContext, fundName, fundCampusId); accountList.Add(matchingAccount); } pledge.AccountId = matchingAccount.Id; } // Attributes to add? // Pledge_Drive_Name newPledges.Add(pledge); completed++; if (completed % percentage < 1) { int percentComplete = completed / percentage; ReportProgress(percentComplete, string.Format("{0:N0} pledges imported ({1}% complete).", completed, percentComplete)); } else if (completed % ReportingNumber < 1) { SavePledges(newPledges); ReportPartialProgress(); } } } } if (newPledges.Any()) { SavePledges(newPledges); } ReportProgress(100, string.Format("Finished pledge import: {0:N0} pledges imported.", 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> /// 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> /// Maps the pledge. /// </summary> /// <param name="queryable">The queryable.</param> /// <exception cref="System.NotImplementedException"></exception> private void MapPledge( IQueryable<Row> tableData ) { var lookupContext = new RockContext(); List<FinancialAccount> accountList = new FinancialAccountService( lookupContext ).Queryable().ToList(); List<FinancialPledge> importedPledges = new FinancialPledgeService( lookupContext ).Queryable().ToList(); var pledgeFrequencies = DefinedTypeCache.Read( new Guid( Rock.SystemGuid.DefinedType.FINANCIAL_FREQUENCY ), lookupContext ).DefinedValues; var newPledges = new List<FinancialPledge>(); int completed = 0; int totalRows = tableData.Count(); int percentage = ( totalRows - 1 ) / 100 + 1; ReportProgress( 0, string.Format( "Verifying pledge import ({0:N0} found).", totalRows ) ); foreach ( var row in tableData ) { decimal? amount = row["Total_Pledge"] as decimal?; DateTime? startDate = row["Start_Date"] as DateTime?; DateTime? endDate = row["End_Date"] as DateTime?; if ( amount != null && startDate != null && endDate != null ) { int? individualId = row["Individual_ID"] as int?; int? householdId = row["Household_ID"] as int?; int? personId = GetPersonAliasId( individualId, householdId ); if ( personId != null && !importedPledges.Any( p => p.PersonAliasId == personId && p.TotalAmount == amount && p.StartDate.Equals( startDate ) ) ) { var pledge = new FinancialPledge(); pledge.CreatedByPersonAliasId = ImportPersonAlias.Id; pledge.StartDate = (DateTime)startDate; pledge.EndDate = (DateTime)endDate; pledge.TotalAmount = (decimal)amount; string frequency = row["Pledge_Frequency_Name"].ToString().ToLower(); if ( frequency != null ) { if ( frequency == "one time" || frequency == "as can" ) { pledge.PledgeFrequencyValueId = pledgeFrequencies.FirstOrDefault( f => f.Guid == new Guid( Rock.SystemGuid.DefinedValue.TRANSACTION_FREQUENCY_ONE_TIME ) ).Id; } else { pledge.PledgeFrequencyValueId = pledgeFrequencies .Where( f => f.Value.ToLower().StartsWith( frequency ) || f.Description.ToLower().StartsWith( frequency ) ) .Select( f => f.Id ).FirstOrDefault(); } } string fundName = row["Fund_Name"] as string; string subFund = row["Sub_Fund_Name"] as string; if ( fundName != null ) { FinancialAccount matchingAccount = null; int? fundCampusId = null; if ( subFund != null ) { // match by campus if the subfund appears to be a campus fundCampusId = CampusList.Where( c => c.Name.StartsWith( subFund ) || c.ShortCode == subFund ) .Select( c => (int?)c.Id ).FirstOrDefault(); if ( fundCampusId != null ) { matchingAccount = accountList.FirstOrDefault( a => a.Name.StartsWith( fundName ) && a.CampusId != null && a.CampusId.Equals( fundCampusId ) ); } else { matchingAccount = accountList.FirstOrDefault( a => a.Name.StartsWith( fundName ) && a.Name.StartsWith( subFund ) ); } } else { matchingAccount = accountList.FirstOrDefault( a => a.Name.StartsWith( fundName ) ); } if ( matchingAccount == null ) { matchingAccount = AddAccount( lookupContext, fundName, fundCampusId ); accountList.Add( matchingAccount ); } pledge.AccountId = matchingAccount.Id; } // Attributes to add? // Pledge_Drive_Name newPledges.Add( pledge ); completed++; if ( completed % percentage < 1 ) { int percentComplete = completed / percentage; ReportProgress( percentComplete, string.Format( "{0:N0} pledges imported ({1}% complete).", completed, percentComplete ) ); } else if ( completed % ReportingNumber < 1 ) { SavePledges( newPledges ); ReportPartialProgress(); } } } } if ( newPledges.Any() ) { SavePledges( newPledges ); } ReportProgress( 100, string.Format( "Finished pledge import: {0:N0} pledges imported.", completed ) ); }
/// <summary> /// Maps the contribution. /// </summary> /// <param name="tableData">The table data.</param> /// <param name="selectedColumns">The selected columns.</param> private void MapContribution( IQueryable<Row> tableData, List<string> selectedColumns = null ) { 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 transactionTypeEventRegistrationId = DefinedValueCache.Read( new Guid( Rock.SystemGuid.DefinedValue.TRANSACTION_TYPE_EVENT_REGISTRATION ), lookupContext ).Id; int currencyTypeACH = DefinedValueCache.Read( new Guid( Rock.SystemGuid.DefinedValue.CURRENCY_TYPE_ACH ), lookupContext ).Id; int currencyTypeCash = DefinedValueCache.Read( new Guid( Rock.SystemGuid.DefinedValue.CURRENCY_TYPE_CASH ), lookupContext ).Id; int currencyTypeCheck = DefinedValueCache.Read( new Guid( Rock.SystemGuid.DefinedValue.CURRENCY_TYPE_CHECK ), lookupContext ).Id; int currencyTypeCreditCard = DefinedValueCache.Read( new Guid( Rock.SystemGuid.DefinedValue.CURRENCY_TYPE_CREDIT_CARD ), lookupContext ).Id; var refundReasons = DefinedTypeCache.Read( new Guid( Rock.SystemGuid.DefinedType.FINANCIAL_TRANSACTION_REFUND_REASON ), lookupContext ).DefinedValues; List<FinancialPledge> pledgeList = new FinancialPledgeService( lookupContext ).Queryable().ToList(); List<FinancialAccount> accountList = new FinancialAccountService( lookupContext ).Queryable().ToList(); // Get all imported contributions var importedContributions = new FinancialTransactionService( lookupContext ).Queryable() .Select( t => new { ContributionId = t.ForeignId, TransactionId = t.Id } ) .ToDictionary( t => t.ContributionId.AsType<int?>(), t => (int?)t.TransactionId ); var householdAVList = new AttributeValueService( lookupContext ).Queryable().Where( av => av.AttributeId == HouseholdAttributeId ).ToList(); // List for batching new contributions var newTransactions = new List<FinancialTransaction>(); int completed = 0; int totalRows = tableData.Count(); int percentage = ( totalRows - 1 ) / 100 + 1; ReportProgress( 0, string.Format( "Verifying contribution import ({0:N0} found, {1:N0} already exist).", totalRows, importedContributions.Count() ) ); foreach ( var row in tableData ) { int? individualId = row["Individual_ID"] as int?; int? householdId = row["Household_ID"] as int?; int? contributionId = row["ContributionID"] as int?; if ( contributionId != null && !importedContributions.ContainsKey( contributionId ) ) { var transaction = new FinancialTransaction(); string fundName = row["Fund_Name"] as string; //Crossroads - Anything under a fund name that starts with Receipt - is an Event Registration. if ( fundName.StartsWith( "Receipt -" ) ) { transaction.TransactionTypeValueId = transactionTypeEventRegistrationId; } else { transaction.TransactionTypeValueId = transactionTypeContributionId; } int? associatedPersonId; if ( individualId != null ) { associatedPersonId = GetPersonAliasId( individualId, householdId ); } //will get the exact person if Individual Id is not null. else { associatedPersonId = GetPersonId( householdAVList, householdId ); } //Will attempt to get the Head first, then Spouse, then Child. Will exclude Other and Visitor if ( associatedPersonId != null ) { transaction.AuthorizedPersonAliasId = associatedPersonId; transaction.CreatedByPersonAliasId = ImportPersonAlias.Id; transaction.ProcessedByPersonAliasId = associatedPersonId; transaction.ForeignId = contributionId.ToString(); string summary = row["Memo"] as string; if ( summary != null ) { transaction.Summary = summary; } int? batchId = row["BatchID"] as int?; if ( batchId != null && ImportedBatches.Any( b => b.Key == batchId ) ) { transaction.BatchId = ImportedBatches.FirstOrDefault( b => b.Key == batchId ).Value; } DateTime? receivedDate = row["Received_Date"] as DateTime?; if ( receivedDate != null ) { transaction.TransactionDateTime = receivedDate; transaction.CreatedDateTime = receivedDate; } bool isTypeNonCash = false; string contributionType = row["Contribution_Type_Name"].ToString().ToLower(); if ( contributionType != null ) { if ( contributionType == "ach" ) { transaction.CurrencyTypeValueId = currencyTypeACH; } else if ( contributionType == "cash" ) { transaction.CurrencyTypeValueId = currencyTypeCash; } else if ( contributionType == "check" ) { transaction.CurrencyTypeValueId = currencyTypeCheck; } else if ( contributionType == "credit card" ) { transaction.CurrencyTypeValueId = currencyTypeCreditCard; } else { isTypeNonCash = true; } } string checkNumber = row["Check_Number"] as string; if ( checkNumber != null && checkNumber.AsType<int?>() != null ) { // routing & account set to zero transaction.CheckMicrEncrypted = Encryption.EncryptString( string.Format( "{0}_{1}_{2}", 0, 0, checkNumber ) ); } decimal? amount = row["Amount"] as decimal?; if ( fundName != null & amount != null ) { FinancialAccount matchingAccount = null; int? parentAccountId = null; string parentAccountName = String.Empty; int? fundCampusId = null; fundName = fundName.Trim(); string subFund = row["Sub_Fund_Name"] as string; if ( subFund != null ) { subFund = subFund.Trim(); // Check if subfund was used to mark a multi-site campus fundCampusId = CampusList.Where( c => subFund.StartsWith( c.Name ) || subFund.StartsWith( c.ShortCode ) ) .Select( c => (int?)c.Id ).FirstOrDefault(); // Matched a campus, check to see if an account exists for that campus already if ( fundCampusId != null ) { matchingAccount = accountList.FirstOrDefault( a => a.Name.Equals( fundName ) && a.CampusId != null && a.CampusId.Equals( fundCampusId ) ); } else { // No campus match, look for an account that matches parent name and subfund name matchingAccount = accountList.FirstOrDefault( a => a.ParentAccountId != null && a.ParentAccount.Name.Equals( fundName ) && a.Name.Equals( subFund ) ); if ( matchingAccount == null ) { // Check if a parent account exists already FinancialAccount parentAccount = accountList.FirstOrDefault( a => a.Name.Equals( fundName ) ); if ( parentAccount == null ) { parentAccount = AddAccount( lookupContext, fundName, fundCampusId ); accountList.Add( parentAccount ); } // set data for subfund to be created parentAccountId = parentAccount.Id; fundName = subFund; parentAccountName = parentAccount.Name; } } } else { matchingAccount = accountList.FirstOrDefault( a => a.Name.Equals( fundName ) && a.CampusId == null ); } if ( matchingAccount == null ) { // No account matches, create the new account with campus Id and parent Id if they were set matchingAccount = AddAccount( lookupContext, fundName, fundCampusId, parentAccountName, parentAccountId ); accountList.Add( matchingAccount ); } var transactionDetail = new FinancialTransactionDetail(); transactionDetail.Amount = (decimal)amount; transactionDetail.CreatedDateTime = receivedDate; transactionDetail.AccountId = matchingAccount.Id; transactionDetail.IsNonCash = isTypeNonCash; transaction.TransactionDetails.Add( transactionDetail ); if ( amount < 0 ) { var transactionRefund = new FinancialTransactionRefund(); transactionRefund.CreatedDateTime = receivedDate; transactionRefund.RefundReasonSummary = summary; transactionRefund.RefundReasonValueId = refundReasons.Where( dv => summary != null && dv.Value.Contains( summary ) ) .Select( dv => (int?)dv.Id ).FirstOrDefault(); transaction.Refund = transactionRefund; } } newTransactions.Add( transaction ); completed++; if ( completed % percentage < 1 ) { int percentComplete = completed / percentage; ReportProgress( percentComplete, string.Format( "{0:N0} contributions imported ({1}% complete).", completed, percentComplete ) ); } 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 ) ); }
/// <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 void MapPledge(IQueryable <Row> tableData) { 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; int totalRows = tableData.Count(); int percentage = (totalRows - 1) / 100 + 1; ReportProgress(0, string.Format("Verifying pledge import ({0:N0} found).", totalRows)); foreach (var row in tableData.Where(r => r != null)) { decimal? amount = row["Total_Pledge"] as decimal?; DateTime?startDate = row["Start_Date"] as DateTime?; DateTime?endDate = row["End_Date"] as DateTime?; if (amount != null && startDate != null && endDate != null) { int?individualId = row["Individual_ID"] as int?; int?householdId = row["Household_ID"] as int?; var personKeys = GetPersonKeys(individualId, householdId, includeVisitors: false); if (personKeys != null && personKeys.PersonAliasId > 0) { var pledge = new FinancialPledge(); pledge.PersonAliasId = personKeys.PersonAliasId; pledge.CreatedByPersonAliasId = ImportPersonAliasId; pledge.ModifiedDateTime = ImportDateTime; pledge.StartDate = (DateTime)startDate; pledge.EndDate = (DateTime)endDate; pledge.TotalAmount = (decimal)amount; string frequency = row["Pledge_Frequency_Name"].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["Fund_Name"] as string; string subFund = row["Sub_Fund_Name"] as string; 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, null); 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, null); accountList.Add(childAccount); } pledge.AccountId = childAccount.Id; } else { pledge.AccountId = parentAccount.Id; } } newPledges.Add(pledge); completed++; if (completed % percentage < 1) { int percentComplete = completed / percentage; ReportProgress(percentComplete, string.Format("{0:N0} pledges imported ({1}% complete).", completed, percentComplete)); } 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)); }
/// <summary> /// Binds the attendees grid. /// </summary> private void BindGiversGrid() { // Get all the selected criteria values var dateRange = SlidingDateRangePicker.CalculateDateRangeFromDelimitedValues( drpSlidingDateRange.DelimitedValues ); var start = dateRange.Start; var end = dateRange.End; var minAmount = nreAmount.LowerValue; var maxAmount = nreAmount.UpperValue; var currencyTypeIds = new List<int>(); cblCurrencyTypes.SelectedValues.ForEach( i => currencyTypeIds.Add( i.AsInteger() ) ); var sourceIds = new List<int>(); cblTransactionSource.SelectedValues.ForEach( i => sourceIds.Add( i.AsInteger() ) ); var accountIds = new List<int>(); foreach ( var cblAccounts in phAccounts.Controls.OfType<RockCheckBoxList>() ) { accountIds.AddRange( cblAccounts.SelectedValuesAsInt ); } var groupBy = hfGroupBy.Value.ConvertToEnumOrNull<ChartGroupBy>() ?? ChartGroupBy.Week; var graphBy = hfGraphBy.Value.ConvertToEnumOrNull<TransactionGraphBy>() ?? TransactionGraphBy.Total; GiversViewBy viewBy = GiversViewBy.Giver; if ( !HideViewByOption ) { viewBy = hfViewBy.Value.ConvertToEnumOrNull<GiversViewBy>() ?? GiversViewBy.Giver; } // Collection of async queries to run before assembling data var qryTasks = new List<Task>(); // Get all person summary data var personInfoList = new List<PersonInfo>(); qryTasks.Add( Task.Run( () => { var dt = FinancialTransactionDetailService.GetGivingAnalyticsPersonSummary( start, end, minAmount, maxAmount, accountIds, currencyTypeIds, sourceIds ) .Tables[0]; foreach ( DataRow row in dt.Rows ) { var personInfo = new PersonInfo(); if ( !DBNull.Value.Equals( row["Id"] ) ) { personInfo.Id = (int)row["Id"]; } if ( !DBNull.Value.Equals( row["Guid"] ) ) { personInfo.Guid = row["Guid"].ToString().AsGuid(); } if ( !DBNull.Value.Equals( row["NickName"] ) ) { personInfo.NickName = row["NickName"].ToString(); } if ( !DBNull.Value.Equals( row["LastName"] ) ) { personInfo.LastName = row["LastName"].ToString(); } if ( !DBNull.Value.Equals( row["Email"] ) ) { personInfo.Email = row["Email"].ToString(); } if ( !DBNull.Value.Equals( row["GivingId"] ) ) { personInfo.GivingId = row["GivingId"].ToString(); } if ( !DBNull.Value.Equals( row["FirstGift"] ) ) { personInfo.FirstGift = row["FirstGift"].ToString().AsDateTime(); } if ( !DBNull.Value.Equals( row["LastGift"] ) ) { personInfo.LastGift = row["LastGift"].ToString().AsDateTime(); } if ( !DBNull.Value.Equals( row["NumberGifts"] ) ) { personInfo.NumberGifts = (int)row["NumberGifts"]; } if ( !DBNull.Value.Equals( row["TotalAmount"] ) ) { personInfo.TotalAmount = (decimal)row["TotalAmount"]; } if ( !DBNull.Value.Equals( row["IsGivingLeader"] ) ) { personInfo.IsGivingLeader = (bool)row["IsGivingLeader"]; } if ( !DBNull.Value.Equals( row["IsAdult"] ) ) { personInfo.IsAdult = (bool)row["IsAdult"]; } if ( !DBNull.Value.Equals( row["IsChild"] ) ) { personInfo.IsChild = (bool)row["IsChild"]; } personInfo.AccountAmounts = new Dictionary<int, decimal>(); personInfoList.Add( personInfo ); } } ) ); // Get the account summary values DataTable dtAccountSummary = null; qryTasks.Add( Task.Run( () => { dtAccountSummary = FinancialTransactionDetailService.GetGivingAnalyticsAccountTotals( start, end, accountIds, currencyTypeIds, sourceIds ) .Tables[0]; } ) ); // Get the first/last ever dates var firstEverVals = new Dictionary<string, DateTime>(); var lastEverVals = new Dictionary<string, DateTime>(); qryTasks.Add( Task.Run( () => { var dt = FinancialTransactionDetailService.GetGivingAnalyticsFirstLastEverDates() .Tables[0]; foreach ( DataRow row in dt.Rows ) { if ( !DBNull.Value.Equals( row["GivingId"] ) ) { if ( !DBNull.Value.Equals( row["FirstEverGift"] ) ) { firstEverVals.Add( row["GivingId"].ToString(), row["FirstEverGift"].ToString().AsDateTime().Value ); } if ( !DBNull.Value.Equals( row["LastEverGift"] ) ) { lastEverVals.Add( row["GivingId"].ToString(), row["LastEverGift"].ToString().AsDateTime().Value ); } } } } ) ); // If a dataview filter was included, find the people who match that criteria List<int> dataViewPersonIds = null; var dataViewId = dvpDataView.SelectedValueAsInt(); if ( dataViewId.HasValue ) { qryTasks.Add( Task.Run( () => { dataViewPersonIds = new List<int>(); var dataView = new DataViewService( _rockContext ).Get( dataViewId.Value ); if ( dataView != null ) { var errorMessages = new List<string>(); var dvPersonService = new PersonService( _rockContext ); ParameterExpression paramExpression = dvPersonService.ParameterExpression; Expression whereExpression = dataView.GetExpression( dvPersonService, paramExpression, out errorMessages ); SortProperty sort = null; var dataViewPersonIdQry = dvPersonService .Queryable().AsNoTracking() .Where( paramExpression, whereExpression, sort ) .Select( p => p.Id ); dataViewPersonIds = dataViewPersonIdQry.ToList(); } } ) ); } qryTasks.Add( Task.Run( () => { // Clear all the existing grid columns var selectField = new SelectField(); var oldSelectField = gGiversGifts.ColumnsOfType<SelectField>().FirstOrDefault(); if ( oldSelectField != null ) { selectField.SelectedKeys.AddRange( oldSelectField.SelectedKeys ); } gGiversGifts.Columns.Clear(); // Add a column for selecting rows gGiversGifts.Columns.Add( selectField ); // Add a hidden column for person id gGiversGifts.Columns.Add( new RockBoundField { DataField = "Id", HeaderText = "Person Id", SortExpression = "Id", Visible = false, ExcelExportBehavior = ExcelExportBehavior.AlwaysInclude } ); // Add a column for the person's name gGiversGifts.Columns.Add( new RockBoundField { DataField = "PersonName", HeaderText = "Person", SortExpression = "LastName,NickName" } ); // add a column for email (but is only included on excel export) gGiversGifts.Columns.Add( new RockBoundField { DataField = "Email", HeaderText = "Email", SortExpression = "Email", Visible = false, ExcelExportBehavior = ExcelExportBehavior.AlwaysInclude } ); // Add a column for total amount gGiversGifts.Columns.Add( new CurrencyField { DataField = "TotalAmount", HeaderText = "Total", SortExpression = "TotalAmount" } ); // Add columns for the selected account totals if ( accountIds.Any() ) { var accounts = new FinancialAccountService( _rockContext ) .Queryable().AsNoTracking() .Where( a => accountIds.Contains( a.Id ) ) .ToList(); foreach ( int accountId in accountIds ) { var account = accounts.FirstOrDefault( a => a.Id == accountId ); if ( account != null ) { gGiversGifts.Columns.Add( new GivingAnalyticsAccountField { DataField = string.Format( "Account_{0}", account.Id ), HeaderText = account.Name, SortExpression = string.Format( "Account:{0}", account.Id ), } ); } } } // Add a column for the number of gifts var numberGiftsField = new RockBoundField { DataField = "NumberGifts", HeaderText = "Number of Gifts", SortExpression = "NumberGifts", DataFormatString = "{0:N0}", }; numberGiftsField.ItemStyle.HorizontalAlign = HorizontalAlign.Right; gGiversGifts.Columns.Add( numberGiftsField ); if ( !radFirstTime.Checked ) { // Add a column to indicate if this is a first time giver gGiversGifts.Columns.Add( new BoolField { DataField = "IsFirstEverGift", HeaderText = "Is First Gift", SortExpression = "IsFirstEverGift" } ); // Add a column for the first gift date ( that matches criteria ) gGiversGifts.Columns.Add( new DateField { DataField = "FirstGift", HeaderText = "First Gift in Period", SortExpression = "FirstGift" } ); } // Add a column for the first-ever gift date ( to any tax-deductible account ) gGiversGifts.Columns.Add( new DateField { DataField = "FirstEverGift", HeaderText = "First Gift Ever", SortExpression = "FirstEverGift" } ); // Add a column for the first gift date ( that matches criteria ) gGiversGifts.Columns.Add( new DateField { DataField = "LastGift", HeaderText = "Last Gift in Period", SortExpression = "LastGift" } ); // Add a column for the last-ever gift date ( to any tax-deductible account ) gGiversGifts.Columns.Add( new DateField { DataField = "LastEverGift", HeaderText = "Last Gift Ever", SortExpression = "LastEverGift" } ); } ) ); // Wait for all the queries to finish Task.WaitAll( qryTasks.ToArray() ); // If dataview was selected and it's being used to filter results people, not in dataview if ( dataViewId.HasValue && rblDataViewAction.SelectedValue != "All" && dataViewPersonIds.Any() ) { personInfoList = personInfoList.Where( c => dataViewPersonIds.Contains( c.Id ) ).ToList(); } // if dataview was selected and it includes people not in the result set, if ( dataViewId.HasValue && rblDataViewAction.SelectedValue == "All" && dataViewPersonIds.Any() ) { // Query for the names of each of these people foreach ( var person in new PersonService( _rockContext ) .Queryable().AsNoTracking() .Where( p => dataViewPersonIds.Contains( p.Id ) ) .Select( p => new { p.Id, p.GivingId, p.GivingLeaderId, p.Guid, p.NickName, p.LastName, p.Email } ) ) { // Check for a first ever gift date var firstEverGiftDate = firstEverVals .Where( f => f.Key == person.GivingId ) .Select( f => f.Value ) .FirstOrDefault(); // Check for a last ever gift date var lastEverGiftDate = lastEverVals .Where( f => f.Key == person.GivingId ) .Select( f => f.Value ) .FirstOrDefault(); var personInfo = new PersonInfo(); personInfo.Id = person.Id; personInfo.Guid = person.Guid; personInfo.NickName = person.NickName; personInfo.LastName = person.LastName; personInfo.Email = person.Email; personInfo.GivingId = person.GivingId; personInfo.IsGivingLeader = person.Id == person.GivingLeaderId; personInfo.FirstEverGift = firstEverGiftDate; personInfo.LastEverGift = lastEverGiftDate; personInfoList.Add( personInfo ); } } // Filter out recs that don't match the view by switch ( viewBy ) { case GiversViewBy.Giver: { personInfoList = personInfoList.Where( p => p.IsGivingLeader ).ToList(); break; } case GiversViewBy.Adults: { personInfoList = personInfoList.Where( p => p.IsAdult ).ToList(); break; } case GiversViewBy.Children: { personInfoList = personInfoList.Where( p => p.IsChild ).ToList(); break; } } // Add the first/last gift dates foreach ( var personInfo in personInfoList ) { if ( firstEverVals.ContainsKey( personInfo.GivingId ) ) { personInfo.FirstEverGift = firstEverVals[personInfo.GivingId]; } if ( lastEverVals.ContainsKey( personInfo.GivingId ) ) { personInfo.LastEverGift = lastEverVals[personInfo.GivingId]; } } // Check to see if we're only showing first time givers if ( radFirstTime.Checked ) { personInfoList = personInfoList .Where( p => p.IsFirstEverGift ) .ToList(); } // Check to see if grid should display only people who gave a certain number of times and if so // set the min value if ( radByPattern.Checked ) { int minCount = tbPatternXTimes.Text.AsInteger(); var previousGivingIds = new List<string>(); if ( cbPatternAndMissed.Checked ) { var missedStart = drpPatternDateRange.LowerValue; var missedEnd = drpPatternDateRange.UpperValue; if ( missedStart.HasValue && missedEnd.HasValue ) { // Get the givingleaderids that gave any amount during the pattern's date range. These // are needed so that we know who to exclude from the result set previousGivingIds = new FinancialTransactionDetailService( _rockContext ) .Queryable().AsNoTracking() .Where( d => d.Transaction.TransactionDateTime.HasValue && d.Transaction.TransactionDateTime.Value >= missedStart.Value && d.Transaction.TransactionDateTime.Value < missedEnd.Value && ( accountIds.Any() && accountIds.Contains( d.AccountId ) || d.Account.IsTaxDeductible ) && d.Amount != 0.0M ) .Select( d => d.Transaction.AuthorizedPersonAlias.Person.GivingId ) .ToList(); } } personInfoList = personInfoList .Where( p => !previousGivingIds.Contains( p.GivingId ) && p.NumberGifts >= minCount ) .ToList(); } // Add account summary info foreach ( DataRow row in dtAccountSummary.Rows ) { if ( !DBNull.Value.Equals( row["GivingId"] ) && !DBNull.Value.Equals( row["AccountId"] ) && !DBNull.Value.Equals( row["Amount"] ) ) { string givingId = row["GivingId"].ToString(); int accountId = (int)row["AccountId"]; decimal amount = (decimal)row["Amount"]; foreach ( var personInfo in personInfoList.Where( p => p.GivingId == givingId ) ) { personInfo.AccountAmounts.AddOrIgnore( accountId, amount ); } } } // Calculate Total if ( viewBy == GiversViewBy.Giver ) { pnlTotal.Visible = true; decimal amountTotal = personInfoList.Sum( p => p.TotalAmount ); lTotal.Text = amountTotal.FormatAsCurrency(); } else { pnlTotal.Visible = false; } var qry = personInfoList.AsQueryable(); if ( gGiversGifts.SortProperty != null ) { if ( gGiversGifts.SortProperty.Property.StartsWith( "Account" ) ) { int? accountId = gGiversGifts.SortProperty.Property.Substring( 8 ).AsIntegerOrNull(); if ( accountId.HasValue ) { foreach ( var personInfo in personInfoList ) { personInfo.SortAmount = personInfo.AccountAmounts.ContainsKey( accountId.Value ) ? personInfo.AccountAmounts[accountId.Value] : 0.0M; if ( gGiversGifts.SortProperty.Direction == SortDirection.Ascending ) { gGiversGifts.DataSource = personInfoList.OrderBy( p => p.SortAmount ).ToList(); } else { gGiversGifts.DataSource = personInfoList.OrderByDescending( p => p.SortAmount ).ToList(); } } } else { gGiversGifts.DataSource = qry.OrderBy( p => p.LastName ).ThenBy( p => p.NickName ).ToList(); } } else { gGiversGifts.DataSource = qry.Sort( gGiversGifts.SortProperty ).ToList(); } } else { gGiversGifts.DataSource = qry.OrderBy( p => p.LastName ).ThenBy( p => p.NickName ).ToList(); } gGiversGifts.DataBind(); }
/// <summary> /// Binds the attendees grid. /// </summary> private void BindGiversGrid() { // Get all the selected criteria values var dateRange = SlidingDateRangePicker.CalculateDateRangeFromDelimitedValues(drpSlidingDateRange.DelimitedValues); var start = dateRange.Start; var end = dateRange.End; var minAmount = nreAmount.LowerValue; var maxAmount = nreAmount.UpperValue; var currencyTypeIds = new List <int>(); cblCurrencyTypes.SelectedValues.ForEach(i => currencyTypeIds.Add(i.AsInteger())); var sourceTypeIds = new List <int>(); cblTransactionSource.SelectedValues.ForEach(i => sourceTypeIds.Add(i.AsInteger())); var accountIds = new List <int>(); foreach (var cblAccounts in phAccounts.Controls.OfType <RockCheckBoxList>()) { accountIds.AddRange(cblAccounts.SelectedValuesAsInt); } var dataViewId = dvpDataView.SelectedValueAsInt(); GiversViewBy viewBy = GiversViewBy.Giver; if (!HideViewByOption) { viewBy = hfViewBy.Value.ConvertToEnumOrNull <GiversViewBy>() ?? GiversViewBy.Giver; } // Clear all the existing grid columns var selectField = new SelectField(); var oldSelectField = gGiversGifts.ColumnsOfType <SelectField>().FirstOrDefault(); if (oldSelectField != null) { selectField.SelectedKeys.AddRange(oldSelectField.SelectedKeys); } gGiversGifts.Columns.Clear(); // Add a column for selecting rows gGiversGifts.Columns.Add(selectField); // Add a column for the person's name gGiversGifts.Columns.Add( new RockBoundField { DataField = "PersonName", HeaderText = "Person", SortExpression = "LastName,NickName" }); // add a column for email (but is only included on excel export) gGiversGifts.Columns.Add( new RockBoundField { DataField = "Email", HeaderText = "Email", SortExpression = "Email", Visible = false, ExcelExportBehavior = ExcelExportBehavior.AlwaysInclude }); // Add a column for total amount gGiversGifts.Columns.Add( new CurrencyField { DataField = "TotalAmount", HeaderText = "Total", SortExpression = "TotalAmount" }); // Add columns for the selected account totals if (accountIds.Any()) { var accounts = new FinancialAccountService(_rockContext) .Queryable().AsNoTracking() .Where(a => accountIds.Contains(a.Id)) .ToList(); foreach (int accountId in accountIds) { var account = accounts.FirstOrDefault(a => a.Id == accountId); if (account != null) { gGiversGifts.Columns.Add( new CurrencyField { DataField = account.Id.ToString(), HeaderText = account.Name, SortExpression = account.Id.ToString() }); } } } // Add a column for the number of gifts var numberGiftsField = new RockBoundField { DataField = "NumberGifts", HeaderText = "Number of Gifts", SortExpression = "NumberGifts", DataFormatString = "{0:N0}", }; numberGiftsField.ItemStyle.HorizontalAlign = HorizontalAlign.Right; gGiversGifts.Columns.Add(numberGiftsField); // Add a column to indicate if this is a first time giver gGiversGifts.Columns.Add( new BoolField { DataField = "IsFirstEverGift", HeaderText = "Is First Gift", SortExpression = "IsFirstEverGift" }); // Add a column for the first gift date ( that matches criteria ) gGiversGifts.Columns.Add( new DateField { DataField = "FirstGift", HeaderText = "First Gift", SortExpression = "FirstGift" }); // Add a column for the first-ever gift date ( to any tax-deductible account ) gGiversGifts.Columns.Add( new DateField { DataField = "FirstEverGift", HeaderText = "First Gift Ever", SortExpression = "FirstEverGift" }); var transactionDetailService = new FinancialTransactionDetailService(_rockContext); var personService = new PersonService(_rockContext); // If dataview was selected get the person id's returned by the dataview var dataViewPersonIds = new List <int>(); if (dataViewId.HasValue) { var dataView = new DataViewService(_rockContext).Get(dataViewId.Value); if (dataView != null) { var errorMessages = new List <string>(); ParameterExpression paramExpression = personService.ParameterExpression; Expression whereExpression = dataView.GetExpression(personService, paramExpression, out errorMessages); SortProperty sortProperty = null; var dataViewPersonIdQry = personService .Queryable().AsNoTracking() .Where(paramExpression, whereExpression, sortProperty) .Select(p => p.Id); dataViewPersonIds = dataViewPersonIdQry.ToList(); } } // Check to see if grid should display only people who gave a certain number of times and if so // set the min value int minCount = 0; var previousPersonIds = new List <int>(); if (radByPattern.Checked) { minCount = tbPatternXTimes.Text.AsInteger(); var missedStart = drpPatternDateRange.LowerValue; var missedEnd = drpPatternDateRange.UpperValue; if (missedStart.HasValue && missedEnd.HasValue) { // Get the givingids that gave any amount during the pattern's date range. These // are needed so that we know who to exclude from the result set var previousGivingIds = transactionDetailService .Queryable().AsNoTracking() .Where(d => d.Transaction.TransactionDateTime.HasValue && d.Transaction.TransactionDateTime.Value >= missedStart.Value && d.Transaction.TransactionDateTime.Value < missedEnd.Value && accountIds.Contains(d.AccountId) && d.Amount != 0.0M) .Select(d => d.Transaction.AuthorizedPersonAlias.Person.GivingId); // Now get the person ids from the givingids previousPersonIds = personService .Queryable().AsNoTracking() .Where(p => previousGivingIds.Contains(p.GivingId)) .Select(p => p.Id) .ToList(); } } // Call the stored procedure to get all the giving data that matches the selected criteria. // The stored procedure returns two tables. First is a list of all matching transaction summary // information and the second table is each giving leader's first-ever gift date to a tax-deductible account DataSet ds = FinancialTransactionDetailService.GetGivingAnalytics(start, end, minAmount, maxAmount, accountIds, currencyTypeIds, sourceTypeIds, dataViewId, viewBy); // Get the results table DataTable dtResults = ds.Tables[0]; // Get the first-ever gift dates and load them into a dictionary for faster matching DataTable dtFirstEver = ds.Tables[1]; var firstEverVals = new Dictionary <int, DateTime>(); foreach (DataRow row in ds.Tables[1].Rows) { if (!DBNull.Value.Equals(row["FirstEverGift"])) { firstEverVals.Add((int)row["PersonId"], (DateTime)row["FirstEverGift"]); } } // Add columns to the result set for the first-ever data dtResults.Columns.Add(new DataColumn("IsFirstEverGift", typeof(bool))); dtResults.Columns.Add(new DataColumn("FirstEverGift", typeof(DateTime))); foreach (DataRow row in dtResults.Rows) { bool rowValid = true; // Get the person id int personId = (int)row["Id"]; if (radByPattern.Checked) { // If pattern was specified check minimum gifts and other date range int numberGifts = (int)row["NumberGifts"]; if (numberGifts < minCount) { rowValid = false; } else { // If this giving leader gave during the pattern date, remove the row since we // only want those who did not if (previousPersonIds.Contains(personId)) { rowValid = false; } } } if (dataViewId.HasValue) { // If a dataview filter was specified, and this row is not part of dataview, // remove it if (!dataViewPersonIds.Contains(personId)) { rowValid = false; // Remove person id from list so that list can be used later to optionally // add rows for remaining people who were in the dataview, but not in the // result set dataViewPersonIds.Remove(personId); } } if (rowValid) { // Set the first ever information for each row bool isFirstEverGift = false; DateTime firstGift = (DateTime)row["FirstGift"]; if (firstEverVals.ContainsKey(personId)) { DateTime firstEverGift = firstEverVals[personId]; isFirstEverGift = firstEverGift.Equals(firstGift); row["FirstEverGift"] = firstEverGift; } // If only first time givers should be included, remove any that are not if (radFirstTime.Checked && !isFirstEverGift) { rowValid = false; } else { row["IsFirstEverGift"] = isFirstEverGift; } } if (!rowValid) { row.Delete(); } } // if dataview was selected and it includes people not in the result set, if (dataViewId.HasValue && rblDataViewAction.SelectedValue == "All" && dataViewPersonIds.Any()) { // Query for the names of each of these people foreach (var person in personService .Queryable().AsNoTracking() .Select(p => new { p.Id, p.Guid, p.NickName, p.LastName, p.Email })) { // Check for a first ever gift date var firstEverGiftDate = firstEverVals .Where(f => f.Key == person.Id) .Select(f => f.Value) .FirstOrDefault(); DataRow row = dtResults.NewRow(); row["Id"] = person.Id; row["Guid"] = person.Guid; row["NickName"] = person.NickName; row["LastName"] = person.LastName; row["PersonName"] = person.NickName + " " + person.LastName; row["Email"] = person.Email; row["IsFirstEverGift"] = false; row["FirstEverGift"] = firstEverGiftDate; dtResults.Rows.Add(row); } } // Update the changes (deletes) in the datatable dtResults.AcceptChanges(); // Calculate Total if (viewBy == GiversViewBy.Giver) { pnlTotal.Visible = true; object amountTotalObj = dtResults.Compute("Sum(TotalAmount)", null); if (amountTotalObj != null) { decimal amountTotal = amountTotalObj.ToString().AsDecimal(); lTotal.Text = amountTotal.FormatAsCurrency(); } else { lTotal.Text = string.Empty; } } else { pnlTotal.Visible = false; } // Sort the results System.Data.DataView dv = dtResults.DefaultView; if (gGiversGifts.SortProperty != null) { try { var sortProperties = new List <string>(); foreach (string prop in gGiversGifts.SortProperty.Property.SplitDelimitedValues(false)) { sortProperties.Add(string.Format("[{0}] {1}", prop, gGiversGifts.SortProperty.DirectionString)); } dv.Sort = sortProperties.AsDelimited(", "); } catch { dv.Sort = "[LastName] ASC, [NickName] ASC"; } } else { dv.Sort = "[LastName] ASC, [NickName] ASC"; } gGiversGifts.DataSource = dv; gGiversGifts.DataBind(); }
/// <summary> /// Binds the attendees grid. /// </summary> private void BindGiversGrid() { // Get all the selected criteria values var dateRange = SlidingDateRangePicker.CalculateDateRangeFromDelimitedValues( drpSlidingDateRange.DelimitedValues ); var start = dateRange.Start; var end = dateRange.End; var minAmount = nreAmount.LowerValue; var maxAmount = nreAmount.UpperValue; var currencyTypeIds = new List<int>(); cblCurrencyTypes.SelectedValues.ForEach( i => currencyTypeIds.Add( i.AsInteger() ) ); var sourceTypeIds = new List<int>(); cblTransactionSource.SelectedValues.ForEach( i => sourceTypeIds.Add( i.AsInteger() ) ); var accountIds = new List<int>(); foreach ( var cblAccounts in phAccounts.Controls.OfType<RockCheckBoxList>() ) { accountIds.AddRange( cblAccounts.SelectedValuesAsInt ); } var dataViewId = dvpDataView.SelectedValueAsInt(); GiversViewBy viewBy = GiversViewBy.Giver; if ( !HideViewByOption ) { viewBy = hfViewBy.Value.ConvertToEnumOrNull<GiversViewBy>() ?? GiversViewBy.Giver; } // Clear all the existing grid columns var selectField = new SelectField(); var oldSelectField = gGiversGifts.ColumnsOfType<SelectField>().FirstOrDefault(); if (oldSelectField != null ) { selectField.SelectedKeys.AddRange( oldSelectField.SelectedKeys ); } gGiversGifts.Columns.Clear(); // Add a column for selecting rows gGiversGifts.Columns.Add( selectField ); // Add a column for the person's name gGiversGifts.Columns.Add( new RockBoundField { DataField = "PersonName", HeaderText = "Person", SortExpression = "LastName,NickName" } ); // add a column for email (but is only included on excel export) gGiversGifts.Columns.Add( new RockBoundField { DataField = "Email", HeaderText = "Email", SortExpression = "Email", Visible = false, ExcelExportBehavior = ExcelExportBehavior.AlwaysInclude } ); // Add a column for total amount gGiversGifts.Columns.Add( new CurrencyField { DataField = "TotalAmount", HeaderText = "Total", SortExpression = "TotalAmount" } ); // Add columns for the selected account totals if ( accountIds.Any() ) { var accounts = new FinancialAccountService( _rockContext ) .Queryable().AsNoTracking() .Where( a => accountIds.Contains( a.Id ) ) .ToList(); foreach ( int accountId in accountIds ) { var account = accounts.FirstOrDefault( a => a.Id == accountId ); if ( account != null ) { gGiversGifts.Columns.Add( new CurrencyField { DataField = account.Id.ToString(), HeaderText = account.Name, SortExpression = account.Id.ToString() } ); } } } // Add a column for the number of gifts var numberGiftsField = new RockBoundField { DataField = "NumberGifts", HeaderText = "Number of Gifts", SortExpression = "NumberGifts", DataFormatString = "{0:N0}", }; numberGiftsField.ItemStyle.HorizontalAlign = HorizontalAlign.Right; gGiversGifts.Columns.Add( numberGiftsField ); // Add a column to indicate if this is a first time giver gGiversGifts.Columns.Add( new BoolField { DataField = "IsFirstEverGift", HeaderText = "Is First Gift", SortExpression = "IsFirstEverGift" } ); // Add a column for the first gift date ( that matches criteria ) gGiversGifts.Columns.Add( new DateField { DataField = "FirstGift", HeaderText = "First Gift", SortExpression = "FirstGift" } ); // Add a column for the first-ever gift date ( to any tax-deductible account ) gGiversGifts.Columns.Add( new DateField { DataField = "FirstEverGift", HeaderText = "First Gift Ever", SortExpression = "FirstEverGift" } ); var transactionDetailService = new FinancialTransactionDetailService( _rockContext ); var personService = new PersonService( _rockContext ); // If dataview was selected get the person id's returned by the dataview var dataViewPersonIds = new List<int>(); if ( dataViewId.HasValue ) { var dataView = new DataViewService( _rockContext ).Get( dataViewId.Value ); if ( dataView != null ) { var errorMessages = new List<string>(); ParameterExpression paramExpression = personService.ParameterExpression; Expression whereExpression = dataView.GetExpression( personService, paramExpression, out errorMessages ); SortProperty sortProperty = null; var dataViewPersonIdQry = personService .Queryable().AsNoTracking() .Where( paramExpression, whereExpression, sortProperty ) .Select( p => p.Id ); dataViewPersonIds = dataViewPersonIdQry.ToList(); } } // Check to see if grid should display only people who gave a certain number of times and if so // set the min value int minCount = 0; var previousPersonIds = new List<int>(); if ( radByPattern.Checked ) { minCount = tbPatternXTimes.Text.AsInteger(); var missedStart = drpPatternDateRange.LowerValue; var missedEnd = drpPatternDateRange.UpperValue; if ( missedStart.HasValue && missedEnd.HasValue ) { // Get the givingids that gave any amount during the pattern's date range. These // are needed so that we know who to exclude from the result set var previousGivingIds = transactionDetailService .Queryable().AsNoTracking() .Where( d => d.Transaction.TransactionDateTime.HasValue && d.Transaction.TransactionDateTime.Value >= missedStart.Value && d.Transaction.TransactionDateTime.Value < missedEnd.Value && accountIds.Contains( d.AccountId ) && d.Amount != 0.0M ) .Select( d => d.Transaction.AuthorizedPersonAlias.Person.GivingId ); // Now get the person ids from the givingids previousPersonIds = personService .Queryable().AsNoTracking() .Where( p => previousGivingIds.Contains( p.GivingId ) ) .Select( p => p.Id ) .ToList(); } } // Call the stored procedure to get all the giving data that matches the selected criteria. // The stored procedure returns two tables. First is a list of all matching transaction summary // information and the second table is each giving leader's first-ever gift date to a tax-deductible account DataSet ds = FinancialTransactionDetailService.GetGivingAnalytics( start, end, minAmount, maxAmount, accountIds, currencyTypeIds, sourceTypeIds, dataViewId, viewBy ); // Get the results table DataTable dtResults = ds.Tables[0]; // Get the first-ever gift dates and load them into a dictionary for faster matching DataTable dtFirstEver = ds.Tables[1]; var firstEverVals = new Dictionary<int, DateTime>(); foreach( DataRow row in ds.Tables[1].Rows ) { if ( !DBNull.Value.Equals( row["FirstEverGift"] ) ) { firstEverVals.Add( (int)row["PersonId"], (DateTime)row["FirstEverGift"] ); } } // Add columns to the result set for the first-ever data dtResults.Columns.Add( new DataColumn( "IsFirstEverGift", typeof( bool ) ) ); dtResults.Columns.Add( new DataColumn( "FirstEverGift", typeof( DateTime ) ) ); foreach( DataRow row in dtResults.Rows ) { bool rowValid = true; // Get the person id int personId = (int)row["Id"]; if ( radByPattern.Checked ) { // If pattern was specified check minimum gifts and other date range int numberGifts = (int)row["NumberGifts"]; if ( numberGifts < minCount ) { rowValid = false; } else { // If this giving leader gave during the pattern date, remove the row since we // only want those who did not if ( previousPersonIds.Contains( personId ) ) { rowValid = false; } } } if ( dataViewId.HasValue ) { // If a dataview filter was specified, and this row is not part of dataview, // remove it if ( !dataViewPersonIds.Contains(personId)) { rowValid = false; // Remove person id from list so that list can be used later to optionally // add rows for remaining people who were in the dataview, but not in the // result set dataViewPersonIds.Remove( personId ); } } if ( rowValid ) { // Set the first ever information for each row bool isFirstEverGift = false; DateTime firstGift = (DateTime)row["FirstGift"]; if ( firstEverVals.ContainsKey( personId ) ) { DateTime firstEverGift = firstEverVals[personId]; isFirstEverGift = firstEverGift.Equals( firstGift ); row["FirstEverGift"] = firstEverGift; } // If only first time givers should be included, remove any that are not if ( radFirstTime.Checked && !isFirstEverGift ) { rowValid = false; } else { row["IsFirstEverGift"] = isFirstEverGift; } } if ( !rowValid ) { row.Delete(); } } // if dataview was selected and it includes people not in the result set, if ( dataViewId.HasValue && rblDataViewAction.SelectedValue == "All" && dataViewPersonIds.Any() ) { // Query for the names of each of these people foreach( var person in personService .Queryable().AsNoTracking() .Select( p => new { p.Id, p.Guid, p.NickName, p.LastName, p.Email })) { // Check for a first ever gift date var firstEverGiftDate = firstEverVals .Where( f => f.Key == person.Id ) .Select( f => f.Value ) .FirstOrDefault(); DataRow row = dtResults.NewRow(); row["Id"] = person.Id; row["Guid"] = person.Guid; row["NickName"] = person.NickName; row["LastName"] = person.LastName; row["PersonName"] = person.NickName + " " + person.LastName; row["Email"] = person.Email; row["IsFirstEverGift"] = false; row["FirstEverGift"] = firstEverGiftDate; dtResults.Rows.Add( row ); } } // Update the changes (deletes) in the datatable dtResults.AcceptChanges(); // Calculate Total if ( viewBy == GiversViewBy.Giver ) { pnlTotal.Visible = true; object amountTotalObj = dtResults.Compute( "Sum(TotalAmount)", null ); if ( amountTotalObj != null ) { decimal amountTotal = amountTotalObj.ToString().AsDecimal(); lTotal.Text = amountTotal.FormatAsCurrency(); } else { lTotal.Text = string.Empty; } } else { pnlTotal.Visible = false; } // Sort the results System.Data.DataView dv = dtResults.DefaultView; if ( gGiversGifts.SortProperty != null ) { try { var sortProperties = new List<string>(); foreach( string prop in gGiversGifts.SortProperty.Property.SplitDelimitedValues(false)) { sortProperties.Add( string.Format( "[{0}] {1}", prop, gGiversGifts.SortProperty.DirectionString ) ); } dv.Sort = sortProperties.AsDelimited( ", " ); } catch { dv.Sort = "[LastName] ASC, [NickName] ASC"; } } else { dv.Sort = "[LastName] ASC, [NickName] ASC"; } gGiversGifts.DataSource = dv; gGiversGifts.DataBind(); }
/// <summary> /// Maps the pledge. /// </summary> /// <param name="tableData">The table data.</param> /// <param name="totalRows">The total rows.</param> /// <exception cref="System.NotImplementedException"></exception> private void MapPledge(IQueryable <Row> tableData, long totalRows = 0) { var lookupContext = new RockContext(); var accountList = new FinancialAccountService(lookupContext).Queryable().AsNoTracking().ToList(); var importedPledges = new FinancialPledgeService(lookupContext).Queryable().AsNoTracking().ToList(); var pledgeFrequencies = DefinedTypeCache.Get(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>(); if (totalRows == 0) { totalRows = tableData.Count(); } var completedItems = 0; var percentage = (totalRows - 1) / 100 + 1; ReportProgress(0, $"Verifying pledge import ({totalRows:N0} found)."); foreach (var row in tableData.Where(r => r != null)) { var amount = row["Total_Pledge"] as decimal?; var startDate = row["Start_Date"] as DateTime?; var endDate = row["End_Date"] as DateTime?; if (amount.HasValue && startDate.HasValue && endDate.HasValue) { var individualId = row["Individual_ID"] as int?; var householdId = row["Household_ID"] as int?; var personKeys = GetPersonKeys(individualId, householdId, includeVisitors: false); if (personKeys != null && personKeys.PersonAliasId > 0) { var pledge = new FinancialPledge { PersonAliasId = personKeys.PersonAliasId, CreatedByPersonAliasId = ImportPersonAliasId, ModifiedDateTime = ImportDateTime, StartDate = ( DateTime )startDate, EndDate = ( DateTime )endDate, TotalAmount = ( decimal )amount }; var frequency = row["Pledge_Frequency_Name"].ToString(); if (!string.IsNullOrWhiteSpace(frequency)) { if (frequency.Equals("one time", StringComparison.OrdinalIgnoreCase) || frequency.Equals("as can", StringComparison.OrdinalIgnoreCase)) { pledge.PledgeFrequencyValueId = oneTimePledgeFrequencyId; } else { pledge.PledgeFrequencyValueId = pledgeFrequencies .Where(f => f.Value.StartsWith(frequency, StringComparison.OrdinalIgnoreCase) || f.Description.StartsWith(frequency, StringComparison.OrdinalIgnoreCase)) .Select(f => f.Id).FirstOrDefault(); } } var fundName = row["Fund_Name"] as string; var subFund = row["Sub_Fund_Name"] as string; if (!string.IsNullOrWhiteSpace(fundName)) { var parentAccount = accountList.FirstOrDefault(a => !a.CampusId.HasValue && a.Name.Equals(fundName.Truncate(50), StringComparison.OrdinalIgnoreCase)); if (parentAccount == null) { parentAccount = AddFinancialAccount(lookupContext, fundName, $"{fundName} imported {ImportDateTime}", string.Empty, null, null, null, startDate, fundName.RemoveSpecialCharacters()); 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 = $"{subFund} {fundName}"; var childAccount = accountList.FirstOrDefault(c => c.ParentAccountId == parentAccount.Id && c.Name.Equals(subFund.Truncate(50), StringComparison.OrdinalIgnoreCase)); if (childAccount == null) { // create a child account with a campusId if it was set childAccount = AddFinancialAccount(lookupContext, subFund, $"{subFund} imported {ImportDateTime}", string.Empty, campusFundId, parentAccount.Id, null, startDate, subFund.RemoveSpecialCharacters(), accountTypeValueId: parentAccount.AccountTypeValueId); accountList.Add(childAccount); } pledge.AccountId = childAccount.Id; } else { pledge.AccountId = parentAccount.Id; } } newPledges.Add(pledge); completedItems++; if (completedItems % percentage < 1) { var percentComplete = completedItems / percentage; ReportProgress(percentComplete, $"{completedItems:N0} pledges imported ({percentComplete}% complete)."); } if (completedItems % ReportingNumber < 1) { SavePledges(newPledges); ReportPartialProgress(); newPledges.Clear(); } } } } if (newPledges.Any()) { SavePledges(newPledges); } ReportProgress(100, $"Finished pledge import: {completedItems:N0} pledges imported."); }
/// <summary> /// Maps the contribution. /// </summary> /// <param name="tableData">The table data.</param> /// <param name="selectedColumns">The selected columns.</param> private void MapContribution( IQueryable<Row> tableData, List<string> selectedColumns = null ) { 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; int currencyTypeACH = DefinedValueCache.Read( new Guid( Rock.SystemGuid.DefinedValue.CURRENCY_TYPE_ACH ), lookupContext ).Id; int currencyTypeCash = DefinedValueCache.Read( new Guid( Rock.SystemGuid.DefinedValue.CURRENCY_TYPE_CASH ), lookupContext ).Id; int currencyTypeCheck = DefinedValueCache.Read( new Guid( Rock.SystemGuid.DefinedValue.CURRENCY_TYPE_CHECK ), lookupContext ).Id; int currencyTypeCreditCard = DefinedValueCache.Read( new Guid( Rock.SystemGuid.DefinedValue.CURRENCY_TYPE_CREDIT_CARD ), lookupContext ).Id; var refundReasons = DefinedTypeCache.Read( new Guid( Rock.SystemGuid.DefinedType.FINANCIAL_TRANSACTION_REFUND_REASON ), lookupContext ).DefinedValues; var accountList = new FinancialAccountService( lookupContext ).Queryable().ToList(); // Get all imported contributions var importedContributions = new FinancialTransactionService( lookupContext ).Queryable() .Where( c => c.ForeignId != null ) .ToDictionary( t => t.ForeignId.AsType<int>(), t => (int?)t.Id ); // List for batching new contributions var newTransactions = new List<FinancialTransaction>(); int completed = 0; int totalRows = tableData.Count(); int percentage = ( totalRows - 1 ) / 100 + 1; ReportProgress( 0, string.Format( "Verifying contribution import ({0:N0} found, {1:N0} already exist).", totalRows, importedContributions.Count ) ); foreach ( var row in tableData.Where( r => r != null ) ) { int? individualId = row["Individual_ID"] as int?; int? householdId = row["Household_ID"] as int?; int? contributionId = row["ContributionID"] as int?; if ( contributionId != null && !importedContributions.ContainsKey( (int)contributionId ) ) { var transaction = new FinancialTransaction(); transaction.CreatedByPersonAliasId = ImportPersonAlias.Id; transaction.TransactionTypeValueId = transactionTypeContributionId; transaction.ForeignId = contributionId.ToString(); var personKeys = GetPersonKeys( individualId, householdId, includeVisitors: false ); if ( personKeys != null ) { transaction.AuthorizedPersonAliasId = personKeys.PersonAliasId; transaction.ProcessedByPersonAliasId = personKeys.PersonAliasId; } string summary = row["Memo"] as string; if ( summary != null ) { transaction.Summary = summary; } int? batchId = row["BatchID"] as int?; if ( batchId != null && ImportedBatches.Any( b => b.Key.Equals( batchId ) ) ) { transaction.BatchId = ImportedBatches.FirstOrDefault( b => b.Key.Equals( batchId ) ).Value; } DateTime? receivedDate = row["Received_Date"] as DateTime?; if ( receivedDate != null ) { transaction.TransactionDateTime = receivedDate; transaction.CreatedDateTime = receivedDate; } bool isTypeNonCash = false; string contributionType = row["Contribution_Type_Name"].ToString().ToLower(); if ( contributionType != null ) { if ( contributionType == "ach" ) { transaction.CurrencyTypeValueId = currencyTypeACH; } else if ( contributionType == "cash" ) { transaction.CurrencyTypeValueId = currencyTypeCash; } else if ( contributionType == "check" ) { transaction.CurrencyTypeValueId = currencyTypeCheck; } else if ( contributionType == "credit card" ) { transaction.CurrencyTypeValueId = currencyTypeCreditCard; } else { isTypeNonCash = true; } } string checkNumber = row["Check_Number"] as string; if ( checkNumber != null && checkNumber.AsType<int?>() != null ) { // set the transaction code to the check number transaction.TransactionCode = checkNumber; } string fundName = row["Fund_Name"] as string; string subFund = row["Sub_Fund_Name"] as string; decimal? amount = row["Amount"] as 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, null, null ); 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, campusFundId, parentAccount.Id ); accountList.Add( childAccount ); } transactionAccountId = childAccount.Id; } else { transactionAccountId = parentAccount.Id; } var transactionDetail = new FinancialTransactionDetail(); transactionDetail.Amount = (decimal)amount; transactionDetail.CreatedDateTime = receivedDate; transactionDetail.AccountId = transactionAccountId; transactionDetail.IsNonCash = isTypeNonCash; transaction.TransactionDetails.Add( transactionDetail ); if ( amount < 0 ) { var transactionRefund = new FinancialTransactionRefund(); transactionRefund.CreatedDateTime = receivedDate; transactionRefund.RefundReasonSummary = summary; transactionRefund.RefundReasonValueId = refundReasons.Where( dv => summary != null && dv.Value.Contains( summary ) ) .Select( dv => (int?)dv.Id ).FirstOrDefault(); transaction.Refund = transactionRefund; } } newTransactions.Add( transaction ); completed++; if ( completed % percentage < 1 ) { int percentComplete = completed / percentage; ReportProgress( percentComplete, string.Format( "{0:N0} contributions imported ({1}% complete).", completed, percentComplete ) ); } 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 ) ); }