Payment Bug - w/Suggested Fix

[dwkrueger]

Consider the following scenario which has occurned several times in our office. The scenario involves our office manager, Nancy, our check out person, Sarah and the patient Jim.

Near the end of the day the following occurs
Sarah, "Okay Jim, I see that you have already made a payment of $65 for your work today."
Jim, "That's right, I wanted to get things taken care of before I went back."
Sarah, "I appreciate that, but I'm sorry that I didn't see that you had $10 left on the account from last time. Would you like to take care of that now?"
Jim, "Sure, infact I have another $10 bill here can I give that to you but I need a new reciept for my flexa account at work."
Sarah, "No problem Jim. Hey, Nancy, I know you had the deposit aready completed but I need to change a payment."
Nancy, "Thanks Sarah, That's no problem I can change that in a minute."
Sarah, "Let me just change the payment for you." Sarah double clicks the payment on the account module to make the change.
Nancy then opens up the deposit and deletes the deposit. "Sarah, Just let me know when the payment is in so I can recreate the deposit"
Sarah, "Okay it is in. Wait. I can't change the payment amount becuase it says it is attached to a deposit."
Nancy, "Just hit Okay, then try and delete it"
Sarah, "No, I still can't edit the payment. I really sorry Jim"
Nancy, "Just create a new payment so we can get Jim a receipt"
Jim is issued a receipt but the old payment cannot be deleted or altered.

What hapened here was FormPayments is open on computer 1 when a deposit is deleted on Computer 2. After the deposit is deleted the user at computer 1 clicks okay and Payment table is updated via a call to Payments.Update(). The call to Payments.Update contains a Payment object with
the old DepositNum in it so the update writes a DepositNum that does not exist (it was deleted on Computer 2). This is the non insurance model I am sure the error probably exists when entering insurance claim issues.

This propblem can be fixed by locking the record row but the deposit form will have to look for the lock and handle it with out crashing the program. I also don't know if the database program supports row locking. I would think that this could be an issue for the distributed web services model.

The more easy quick fix would be to update the payment and set DepositNum = 0 when the FormPayment checks to see if payment is attached to a deposit. But you will have to SELECT DepositNum where DepositNum == Payment.DepositNum. If resultTable.Rows.Count == 0 then Perform the Update. But the problem with this fix is you have to fix the error with the FormPayments.

Alternatively you can find alinenated payment by SELECT DISTINCT(payments.DepositNum) from payments ORDER BY DepositNum. Select DepositNum from Deposit. Iterate through and develop a list of DepositNums that don't exist. Then UPDATE payments SET DepositNum = 0 where (DepositNum = missing Num1 or DepositNum = missing Num2 ....)

The iteration should be fast and the querries should be fast and the iterations should be fast. The problem can still occur if a person is messing with a payment. But if you tag the check to the FormDeposit.Load() then you can prompt user to make sure no one has any payment transactions open prior to continuing.

Finally you could add the same itteration fix the the Database Maintence. But I think that it is benign enough to put it with the Deposit or FormPayment.

I know the intro was wordy but hopefully it made it more interesting to read. I may not have had the table names exactly right but I think the concept is clear.

[jordansparks]

Thanks. The solution is to use our new CRUD framework to only update columns that have changed. This is how we have prevented collisions in appointments and patients for years. Our new framework lets us easily use the same strategy for other tables. And the way to fix existing problems is to add it to the db maint.