public static void CheckBatchSettlements(CMSDataContext db, IGateway gateway, DateTime start, DateTime end) { var response = gateway.GetBatchDetails(start, end); // get distinct batches var allBatchReferences = (from batchTran in response.BatchTransactions select batchTran.BatchReference).Distinct(); // first filter out batches that we have already been updated or inserted. // now find unmatched batch references var unmatchedBatchReferences = allBatchReferences.Where(br => !db.CheckedBatches.Any(tt => tt.BatchRef == br)).ToList(); // given unmatched batch references, get the matched batch transactions again var unMatchedBatchTransactions = response.BatchTransactions.Where(x => unmatchedBatchReferences.Contains(x.BatchReference)).ToList(); var batchTypes = unMatchedBatchTransactions.Select(x => x.BatchType).Distinct(); foreach (var batchType in batchTypes) { // key it by transaction reference and payment type. var unMatchedKeyedByReference = unMatchedBatchTransactions.Where(x => x.BatchType == batchType).ToDictionary(x => x.Reference, x => x); // next let's get all the approved matching transactions from our transaction table by transaction id (reference). var approvedMatchingTransactions = from transaction in db.Transactions where unMatchedKeyedByReference.Keys.Contains(transaction.TransactionId) where (transaction.PaymentType == null || transaction.PaymentType == (batchType == BatchType.Ach ? PaymentType.Ach : PaymentType.CreditCard)) where transaction.Approved == true select transaction; // next key the matching approved transactions that came from our transaction table by the transaction id (reference). var distinctTransactionIds = approvedMatchingTransactions.Select(x => x.TransactionId).Distinct(); // finally let's get a list of all transactions that need to be inserted, which we don't already have. var transactionsToInsert = from transaction in unMatchedKeyedByReference where !distinctTransactionIds.Contains(transaction.Key) select transaction.Value; var notbefore = DateTime.Parse("6/1/12"); // the date when Sage payments began in BVCMS (?) // spin through each transaction and insert them to the transaction table. foreach (var transactionToInsert in transactionsToInsert) { // get the original transaction. var originalTransaction = db.Transactions.SingleOrDefault(t => t.TransactionId == transactionToInsert.Reference && transactionToInsert.TransactionDate >= notbefore && t.PaymentType == (batchType == BatchType.Ach ? PaymentType.Ach : PaymentType.CreditCard)); // get the first and last name. string first, last; Util.NameSplit(transactionToInsert.Name, out first, out last); // get the settlement date, however we are not exactly sure why we add four hours to the settlement date. // we think it is to handle all timezones and push to the next day?? var settlementDate = AdjustSettlementDateForAllTimeZones(transactionToInsert.SettledDate); // insert the transaction record. db.Transactions.InsertOnSubmit(new Transaction { Name = transactionToInsert.Name, First = first, Last = last, TransactionId = transactionToInsert.Reference, Amt = transactionToInsert.TransactionType == TransactionType.Credit || transactionToInsert.TransactionType == TransactionType.Refund ? -transactionToInsert.Amount : transactionToInsert.Amount, Approved = transactionToInsert.Approved, Message = transactionToInsert.Message, TransactionDate = transactionToInsert.TransactionDate, TransactionGateway = gateway.GatewayType, Settled = settlementDate, Batch = settlementDate, // this date now will be the same as the settlement date. Batchref = transactionToInsert.BatchReference, Batchtyp = transactionToInsert.BatchType == BatchType.Ach ? "eft" : "bankcard", OriginalId = originalTransaction != null ? (originalTransaction.OriginalId ?? originalTransaction.Id) : (int?)null, Fromsage = true, Description = originalTransaction != null ? originalTransaction.Description : $"no description from {gateway.GatewayType}, id={transactionToInsert.TransactionId}", PaymentType = transactionToInsert.BatchType == BatchType.Ach ? PaymentType.Ach : PaymentType.CreditCard, LastFourCC = transactionToInsert.BatchType == BatchType.CreditCard ? transactionToInsert.LastDigits : null, LastFourACH = transactionToInsert.BatchType == BatchType.Ach ? transactionToInsert.LastDigits : null }); } // next update Existing transactions with new batch data if there are any. foreach (var existingTransaction in approvedMatchingTransactions) { if (!unMatchedKeyedByReference.ContainsKey(existingTransaction.TransactionId)) { continue; } // first get the matching batch transaction. var batchTransaction = unMatchedKeyedByReference[existingTransaction.TransactionId]; // get the adjusted settlement date var settlementDate = AdjustSettlementDateForAllTimeZones(batchTransaction.SettledDate); existingTransaction.Batch = settlementDate; // this date now will be the same as the settlement date. existingTransaction.Batchref = batchTransaction.BatchReference; existingTransaction.Batchtyp = batchTransaction.BatchType == BatchType.Ach ? "eft" : "bankcard"; existingTransaction.Settled = settlementDate; existingTransaction.PaymentType = batchTransaction.BatchType == BatchType.Ach ? PaymentType.Ach : PaymentType.CreditCard; existingTransaction.LastFourCC = batchTransaction.BatchType == BatchType.CreditCard ? batchTransaction.LastDigits : null; existingTransaction.LastFourACH = batchTransaction.BatchType == BatchType.Ach ? batchTransaction.LastDigits : null; } } // finally we need to mark these batches as completed if there are any. foreach (var batch in unMatchedBatchTransactions.DistinctBy(x => x.BatchReference)) { var checkedBatch = db.CheckedBatches.SingleOrDefault(bb => bb.BatchRef == batch.BatchReference); if (checkedBatch == null) { db.CheckedBatches.InsertOnSubmit( new CheckedBatch { BatchRef = batch.BatchReference, CheckedX = DateTime.Now }); } else { checkedBatch.CheckedX = DateTime.Now; } } db.SubmitChanges(); }