/// <summary>
        /// Update a consumer.
        /// </summary>
        /// <param name="entity">The ConsumerTrust's Entity row.</param>
        /// <returns>The ConsumerId of the Consumer row.</returns>
        private Guid UpdateConsumer(EntityRow entity)
        {
            DataModel            dataModel            = new DataModel();
            DataModelTransaction dataModelTransaction = DataModelTransaction.Current;
            DateTime             modified             = DateTime.UtcNow;
            CountryRow           country;
            Guid             countryId;
            Guid?            provinceId = null;
            EntityRow        dollars;
            Guid             dollarsId;
            ConsumerRow      consumer      = null;
            ConsumerTrustRow consumerTrust = null;
            SecurityRow      security      = null;
            WorkingOrderRow  workingOrder  = null;

            MatchRow[] matches;
            Guid       consumerId;
            Guid       consumerTrustId;
            Guid       entityId;
            Guid       securityId;
            Guid       workingOrderId;
            Int64      consumerVersion;
            Int64      consumerTrustVersion;
            Int64      entityVersion;
            Int64      securityVersion;
            Int64      workingOrderVersion;
            Boolean    updateConsumer      = false;
            Boolean    updateConsumerTrust = false;
            Boolean    updateEntity        = false;
            Boolean    updateSecurity      = false;

            country = TradingSupport.FindCountryByKey(
                this.Record.ConfigurationId,
                "FK_Country_Security",
                new object[] { this.Record.CountryCode });
            countryId = country.CountryId;
            country.ReleaseReaderLock(dataModelTransaction.TransactionId);

            if (this.Record.ProvinceCode != null)
            {
                ProvinceRow province = TradingSupport.FindProvinceByKey(
                    this.Record.ConfigurationId,
                    "FK_Province_Consumer",
                    new object[] { this.Record.ProvinceCode });
                provinceId = province.ProvinceId;
                province.ReleaseReaderLock(dataModelTransaction.TransactionId);
            }

            dollars = TradingSupport.FindEntityByKey(
                this.Record.ConfigurationId,
                "FK_Security_WorkingOrder_SettlementId",
                new object[] { this.Record.Currency });
            dollarsId = dollars.EntityId;
            dollars.ReleaseReaderLock(dataModelTransaction.TransactionId);

            try
            {
                entity.AcquireReaderLock(dataModelTransaction.TransactionId, DataModel.LockTimeout);
                entityId      = entity.EntityId;
                entityVersion = entity.RowVersion;
                if (TradingSupport.IsColumnOld(entity, "Name", this.Record.OriginalAccountNumber))
                {
                    updateEntity = true;
                }
            }
            finally
            {
                entity.ReleaseLock(dataModelTransaction.TransactionId);
            }

            try
            {
                security = DataModel.Security.SecurityKey.Find(entityId);
                security.AcquireReaderLock(dataModelTransaction.TransactionId, DataModel.LockTimeout);
                securityId      = entityId;
                securityVersion = security.RowVersion;
                workingOrder    = security.GetWorkingOrderRowsByFK_Security_WorkingOrder_SecurityId()[0];
                if (TradingSupport.IsColumnOld(security, "CountryId", countryId))
                {
                    updateSecurity = true;
                }
            }
            finally
            {
                security.ReleaseLock(dataModelTransaction.TransactionId);
            }


            // Control the working order:
            workingOrder.AcquireWriterLock(dataModelTransaction);

            try
            {
                consumerTrust = DataModel.ConsumerTrust.ConsumerTrustKey.Find(entityId);
                consumerTrust.AcquireReaderLock(dataModelTransaction.TransactionId, DataModel.LockTimeout);
                consumerTrustId      = consumerTrust.ConsumerTrustId;
                consumerTrustVersion = consumerTrust.RowVersion;
                consumer             = DataModel.Consumer.ConsumerKey.Find(consumerTrust.ConsumerId);
                if (TradingSupport.IsColumnOld(consumerTrust, "SavingsAccount", this.Record.SavingsAccount) ||
                    TradingSupport.IsColumnOld(consumerTrust, "SavingsBalance", this.Record.SavingsBalance) ||
                    TradingSupport.IsColumnOld(consumerTrust, "Tag", this.Record.Tag))
                {
                    updateConsumerTrust = true;
                }
            }
            finally
            {
                consumerTrust.ReleaseLock(dataModelTransaction.TransactionId);
            }

            try
            {
                consumer.AcquireReaderLock(dataModelTransaction.TransactionId, DataModel.LockTimeout);
                consumerId      = consumer.ConsumerId;
                consumerVersion = consumer.RowVersion;
                if (TradingSupport.IsColumnOld(consumer, "Address1", this.Record.Address1) ||
                    TradingSupport.IsColumnOld(consumer, "Address2", this.Record.Address2) ||
                    TradingSupport.IsColumnOld(consumer, "BankAccountNumber", this.Record.BankAccountNumber) ||
                    TradingSupport.IsColumnOld(consumer, "BankRoutingNumber", this.Record.BankRoutingNumber) ||
                    TradingSupport.IsColumnOld(consumer, "City", this.Record.City) ||
                    TradingSupport.IsColumnOld(consumer, "DateOfBirth", this.Record.DateOfBirth) ||
                    TradingSupport.IsColumnOld(consumer, "FirstName", this.Record.FirstName) ||
                    TradingSupport.IsColumnOld(consumer, "IsEmployed", this.Record.IsEmployed) ||
                    TradingSupport.IsColumnOld(consumer, "LastName", this.Record.LastName) ||
                    TradingSupport.IsColumnOld(consumer, "PostalCode", this.Record.PostalCode) ||
                    TradingSupport.IsColumnOld(consumer, "MiddleName", this.Record.MiddleName) ||
                    TradingSupport.IsColumnOld(consumer, "PhoneNumber", this.Record.PhoneNumber) ||
                    TradingSupport.IsColumnOld(consumer, "ProvinceId", provinceId) ||
                    TradingSupport.IsColumnOld(consumer, "SocialSecurityNumber", this.Record.SocialSecurityNumber) ||
                    TradingSupport.IsColumnOld(consumer, "Suffix", this.Record.Suffix))
                {
                    updateConsumer = true;
                }
            }
            finally
            {
                consumer.ReleaseLock(dataModelTransaction.TransactionId);
            }

            try
            {
                //workingOrder.AcquireReaderLock(dataModelTransaction.TransactionId, DataModel.LockTimeout);
                workingOrderId      = workingOrder.WorkingOrderId;
                workingOrderVersion = workingOrder.RowVersion;
                matches             = workingOrder.GetMatchRows();
            }
            finally
            {
                //workingOrder.ReleaseLock(dataModelTransaction.TransactionId);
            }

            foreach (MatchRow match in matches)
            {
                if (WorkingOrderPersistence.IsSettled(dataModelTransaction, match))
                {
                    throw new FaultException <SecurityFault>(
                              new SecurityFault("Cannot update account that is settled")
                    {
                        FaultCode = ErrorCode.RecordExists
                    },
                              "Cannot update account that is settled");
                }
            }

            // We need write access to the containing blotter in order to add a record to it.
            if (!TradingSupport.HasAccess(dataModelTransaction, PersistenceHelper.GetBlotterForConsumer(dataModelTransaction, consumerId), AccessRight.Write))
            {
                throw new SecurityException("Current user does not have write access to the original blotter");
            }

            if (updateConsumer)
            {
                dataModel.UpdateConsumer(
                    this.Record.Address1 != null ? (object)this.Record.Address1 : DBNull.Value,
                    this.Record.Address2 != null ? (object)this.Record.Address2 : DBNull.Value,
                    this.Record.BankAccountNumber != null ? (object)this.Record.BankAccountNumber : DBNull.Value,
                    this.Record.BankRoutingNumber != null ? (object)this.Record.BankRoutingNumber : DBNull.Value,
                    this.Record.City != null ? (object)this.Record.City : DBNull.Value,
                    consumerId,
                    new object[] { consumerId },
                    this.Record.DateOfBirth != null ? (object)this.Record.DateOfBirth.Value : DBNull.Value,
                    null,
                    null,
                    this.Record.FirstName != null ? (object)this.Record.FirstName : DBNull.Value,
                    this.Record.IsEmployed != null ? (object)this.Record.IsEmployed.Value : DBNull.Value,
                    this.Record.LastName != null ? (object)this.Record.LastName : DBNull.Value,
                    this.Record.MiddleName != null ? (object)this.Record.MiddleName : DBNull.Value,
                    this.Record.PhoneNumber != null ? (object)StringUtilities.CleanUpAlphaNumericString(this.Record.PhoneNumber) : DBNull.Value,
                    this.Record.PostalCode != null ? (object)this.Record.PostalCode : DBNull.Value,
                    provinceId,
                    consumerVersion,
                    this.Record.Salutation,
                    StringUtilities.CleanUpAlphaNumericString(this.Record.SocialSecurityNumber),
                    this.Record.Suffix != null ? (object)this.Record.Suffix : DBNull.Value);
            }

            if (updateConsumerTrust)
            {
                dataModel.UpdateConsumerTrust(
                    null,
                    consumerTrustId,
                    new object[] { consumerTrustId },
                    null,
                    null,
                    consumerTrustVersion,
                    this.Record.SavingsAccount,
                    this.Record.SavingsBalance,
                    this.Record.Tag != null ? (object)this.Record.Tag : DBNull.Value,
                    null,
                    null);
            }

            if (updateEntity)
            {
                dataModel.UpdateEntity(
                    null,
                    null,
                    entityId,
                    new object[] { entityId },
                    null,
                    null,
                    null,
                    null,
                    null,
                    null,
                    null,
                    null,
                    null,
                    null,
                    null,
                    modified,
                    this.Record.SavingsEntityCode,
                    entityVersion,
                    null,
                    null);
            }

            if (updateSecurity)
            {
                dataModel.UpdateSecurity(
                    null,
                    countryId,
                    null,
                    null,
                    null,
                    1,
                    1,
                    securityVersion,
                    securityId,
                    new object[] { securityId },
                    null,
                    null,
                    null);
            }

            dataModel.UpdateWorkingOrder(
                null,
                this.Record.Blotter,
                null,
                null,
                null,
                null,
                null,
                null,
                null,
                null,
                null,
                null,
                null,
                modified,
                TradingSupport.UserId,
                null,
                workingOrderVersion,
                null,
                null,
                dollarsId,
                null,
                null,
                StatusMap.FromCode(Status.New),
                null,
                null,
                null,
                null,
                null,
                null,
                modified,
                workingOrderId,
                new object[] { workingOrderId });

            return(consumerId);
        }
        /// <summary>
        /// If a matching consumer debt record already exists, update the account with this, rather than creating a new one.
        /// </summary>
        /// <param name="entity">The entity row of the consumer debt record.</param>
        /// <returns>The entityId.</returns>
        internal Guid Update(EntityRow entity)
        {
            DataModel            dataModel            = new DataModel();
            DataModelTransaction dataModelTransaction = DataModelTransaction.Current;
            CountryRow           country;
            Guid            countryId;
            Guid?           provinceId = null;
            EntityRow       dollars;
            Guid            dollarsId;
            ConsumerRow     consumer;
            ConsumerDebtRow consumerDebt;
            CreditCardRow   creditCard;
            SecurityRow     security;
            WorkingOrderRow workingOrder;
            Guid            consumerId;
            Guid            consumerDebtId = entity.EntityId;
            Guid            creditCardId;
            Guid            entityId   = entity.EntityId;
            Guid            securityId = entity.EntityId;
            Guid            workingOrderId;
            Int64           consumerVersion;
            Int64           consumerDebtVersion;
            Int64           creditCardVersion;
            Int64           entityVersion = entity.RowVersion;
            Int64           securityVersion;
            Int64           workingOrderVersion;
            Boolean         updateConsumer     = false;
            Boolean         updateConsumerDebt = false;
            Boolean         updateCreditCard   = false;
            Boolean         updateEntity       = false;
            Boolean         updateSecurity     = false;
            DateTime        currentUTCTime     = DateTime.UtcNow;

            entity.ReleaseReaderLock(dataModelTransaction.TransactionId);

            // We need write access to the containing blotter in order to add a record to it.
            if (!DataModelFilters.HasAccess(dataModelTransaction, TradingSupport.UserId, this.Record.Blotter, AccessRight.Write))
            {
                throw new SecurityException("Current user does not have write access to the selected blotter");
            }
#if false
            // We need write access to the consumer debt's entity in order to update it.
            if (!TradingSupport.HasAccess(dataModelTransaction, entityId, AccessRight.Write))
            {
                throw new SecurityException("Current user does not have write access to the selected consumer debt");
            }
#endif
            // Via the country row...
            country = TradingSupport.FindCountryByKey(
                this.Record.ConfigurationId,
                "FK_Country_Security",
                new object[] { this.Record.CountryCode });
            countryId = country.CountryId;
            country.ReleaseReaderLock(dataModelTransaction.TransactionId);

            // ... get the province Id.
            if (this.Record.ProvinceCode != null)
            {
                ProvinceRow province = TradingSupport.FindProvinceByKey(
                    this.Record.ConfigurationId,
                    "FK_Province_Consumer",
                    new object[] { this.Record.ProvinceCode });
                provinceId = province.ProvinceId;
                province.ReleaseReaderLock(dataModelTransaction.TransactionId);
            }

            // Get the USD security Id.
            dollars = TradingSupport.FindEntityByKey(
                this.Record.ConfigurationId,
                "FK_Security_WorkingOrder_SettlementId",
                new object[] { this.Record.Currency });
            dollarsId = dollars.EntityId;
            dollars.ReleaseReaderLock(dataModelTransaction.TransactionId);

            // See if the entity needs to be updated.
            entity.AcquireReaderLock(dataModelTransaction);
            entityVersion = entity.RowVersion;
            if (TradingSupport.IsColumnOld(entity, "Name", this.Record.OriginalAccountNumber))
            {
                updateEntity = true;
            }
            entity.ReleaseLock(dataModelTransaction.TransactionId);


            // Get security's children see if we need to update securityRow.
            security = DataModel.Security.SecurityKey.Find(entityId);
            security.AcquireReaderLock(dataModelTransaction.TransactionId, DataModel.LockTimeout);
            securityVersion = security.RowVersion;
            consumerDebt    = DataModel.ConsumerDebt.ConsumerDebtKey.Find(security.SecurityId);
            workingOrder    = security.GetWorkingOrderRowsByFK_Security_WorkingOrder_SecurityId()[0];
            if (TradingSupport.IsColumnOld(security, "CountryId", countryId))
            {
                updateSecurity = true;
            }
            security.ReleaseLock(dataModelTransaction.TransactionId);

            // Control the working order:
            workingOrder.AcquireWriterLock(dataModelTransaction);

            // Get the consumer debt's children and see if we need to update the consumer debt.
            consumerDebt.AcquireReaderLock(dataModelTransaction.TransactionId, DataModel.LockTimeout);
            consumerDebtVersion = consumerDebt.RowVersion;
            creditCard          = DataModel.CreditCard.CreditCardKey.Find(consumerDebt.CreditCardId);
            if (TradingSupport.IsColumnOld(consumerDebt, "DateOfDelinquency", this.Record.DateOfDelinquency) ||
                TradingSupport.IsColumnOld(consumerDebt, "Representative", this.Record.Representative) ||
                TradingSupport.IsColumnOld(consumerDebt, "Tag", this.Record.Tag))
            {
                updateConsumerDebt = true;
            }
            consumerDebt.ReleaseLock(dataModelTransaction.TransactionId);

            creditCard.AcquireReaderLock(dataModelTransaction.TransactionId, DataModel.LockTimeout);
            creditCardId      = creditCard.CreditCardId;
            creditCardVersion = creditCard.RowVersion;
            consumer          = DataModel.Consumer.ConsumerKey.Find(creditCard.ConsumerId);
            if (TradingSupport.IsColumnOld(creditCard, "AccountBalance", this.Record.AccountBalance) ||
                TradingSupport.IsColumnOld(creditCard, "AccountNumber", this.Record.AccountCode) ||
                TradingSupport.IsColumnOld(creditCard, "DebtHolder", this.Record.DebtHolder) ||
                TradingSupport.IsColumnOld(creditCard, "OriginalAccountNumber", this.Record.OriginalAccountNumber))
            {
                updateCreditCard = true;
            }
            creditCard.ReleaseLock(dataModelTransaction.TransactionId);

            consumer.AcquireReaderLock(dataModelTransaction.TransactionId, DataModel.LockTimeout);
            consumerId      = consumer.ConsumerId;
            consumerVersion = consumer.RowVersion;
            if (TradingSupport.IsColumnOld(consumer, "Address1", this.Record.Address1) ||
                TradingSupport.IsColumnOld(consumer, "Address2", this.Record.Address2) ||
                TradingSupport.IsColumnOld(consumer, "City", this.Record.City) ||
                TradingSupport.IsColumnOld(consumer, "DateOfBirth", this.Record.DateOfBirth) ||
                TradingSupport.IsColumnOld(consumer, "FirstName", this.Record.FirstName) ||
                TradingSupport.IsColumnOld(consumer, "LastName", this.Record.LastName) ||
                TradingSupport.IsColumnOld(consumer, "PostalCode", this.Record.PostalCode) ||
                TradingSupport.IsColumnOld(consumer, "MiddleName", this.Record.MiddleName) ||
                TradingSupport.IsColumnOld(consumer, "PhoneNumber", this.Record.PhoneNumber) ||
                TradingSupport.IsColumnOld(consumer, "ProvinceId", provinceId) ||
                TradingSupport.IsColumnOld(consumer, "SocialSecurityNumber", this.Record.SocialSecurityNumber) ||
                TradingSupport.IsColumnOld(consumer, "Suffix", this.Record.Suffix))
            {
                updateConsumer = true;
            }
            consumer.ReleaseLock(dataModelTransaction.TransactionId);

            workingOrder.AcquireReaderLock(dataModelTransaction.TransactionId, DataModel.LockTimeout);
            workingOrderId      = workingOrder.WorkingOrderId;
            workingOrderVersion = workingOrder.RowVersion;

            //workingOrder.ReleaseLock(dataModelTransaction.TransactionId);

            // We need write access to the containing blotter in order to add a record to it.
            if (!TradingSupport.HasAccess(dataModelTransaction, PersistenceHelper.GetBlotterForConsumer(dataModelTransaction, consumerId), AccessRight.Write))
            {
                throw new SecurityException("Current user does not have write access to the original blotter");
            }

            if (updateConsumer)
            {
                dataModel.UpdateConsumer(
                    this.Record.Address1 != null ? (object)this.Record.Address1 : DBNull.Value,
                    this.Record.Address2 != null ? (object)this.Record.Address2 : DBNull.Value,
                    null,
                    null,
                    this.Record.City != null ? (object)this.Record.City : DBNull.Value,
                    consumerId,
                    new object[] { consumerId },
                    this.Record.DateOfBirth != null ? (object)this.Record.DateOfBirth.Value : DBNull.Value,
                    null,
                    null,
                    this.Record.FirstName != null ? (object)this.Record.FirstName : DBNull.Value,
                    null,
                    this.Record.LastName != null ? (object)this.Record.LastName : DBNull.Value,
                    this.Record.MiddleName != null ? (object)this.Record.MiddleName : DBNull.Value,
                    this.Record.PhoneNumber != null ? (object)StringUtilities.CleanUpAlphaNumericString(this.Record.PhoneNumber) : DBNull.Value,
                    this.Record.PostalCode != null ? (object)this.Record.PostalCode : DBNull.Value,
                    provinceId,
                    consumerVersion,
                    null,
                    StringUtilities.CleanUpAlphaNumericString(this.Record.SocialSecurityNumber),
                    this.Record.Suffix != null ? (object)this.Record.Suffix : DBNull.Value);
            }

            if (updateConsumerDebt)
            {
                dataModel.UpdateConsumerDebt(
                    null,
                    consumerDebtId,
                    new object[] { consumerDebtId },
                    null,
                    null,
                    this.Record.DateOfDelinquency != null ? (object)this.Record.DateOfDelinquency.Value : DBNull.Value,
                    null,
                    null,
                    this.Record.Representative != null ? (object)this.Record.Representative : DBNull.Value,
                    consumerDebtVersion,
                    this.Record.Tag != null ? (object)this.Record.Tag : DBNull.Value,
                    null,
                    null);
            }

            if (updateCreditCard)
            {
                dataModel.UpdateCreditCard(
                    this.Record.AccountBalance,
                    this.Record.AccountCode,
                    null,
                    creditCardId,
                    new object[] { creditCardId },
                    this.Record.DebtHolder,
                    null,
                    null,
                    null,
                    StringUtilities.CleanUpAlphaNumericString(this.Record.OriginalAccountNumber),
                    creditCardVersion,
                    null);
            }

            if (updateEntity)
            {
                dataModel.UpdateEntity(
                    null,
                    null,
                    entityId,
                    new object[] { entityId },
                    null,
                    null,
                    null,
                    null,
                    null,
                    null,
                    null,
                    null,
                    null,
                    null,
                    null,
                    currentUTCTime,
                    this.Record.OriginalAccountNumber,
                    entityVersion,
                    null,
                    null);
            }

            if (updateSecurity)
            {
                dataModel.UpdateSecurity(
                    null,
                    countryId,
                    null,
                    null,
                    null,
                    1,
                    1,
                    securityVersion,
                    securityId,
                    new object[] { securityId },
                    null,
                    null,
                    null);
            }


            dataModel.UpdateWorkingOrder(
                null,
                this.Record.Blotter,
                null,
                null,
                null,
                null,
                null,
                null,
                null,
                null,
                null,
                null,
                null,
                currentUTCTime,
                TradingSupport.UserId,
                null,
                workingOrderVersion,
                null,
                null,
                dollarsId,
                null,
                null,
                null,
                null,
                null,
                null,
                null,
                null,
                null,
                currentUTCTime,
                workingOrderId,
                new object[] { workingOrderId });

            return(entityId);
        }