AnsweredAssumed Answered

Intriguing Find problem

Question asked by Oliver_Reid on Jul 29, 2014
Latest reply on Aug 4, 2014 by LyndsayHowarth

2 tables

 

Items

 

Paypal_receipts

 

Paypal_receipts can get updated if Paypal approval happens after a delay on the Paypal side, can be reversed or refunded.

 

a Paypal receipt can cover several items

 

An item can be paid for in istallments via several Payapal receipts

 

 

So its a many-many relation with a join table.

 

The join table records contain the amoumt from the related receipt that applies to the related item sold. (E.g. a receipt could cover down paymnets on three items in a web cart -- there would be three join records allcoatiing the total to the three items)

 

10 days after an item is sold I want to mark it as 'fully paid up' if

 

--It is fully paid for

 

--All the payment receipts connected to it show as 'payment completed'

 

--All the other items sold connected to those receipts are also fully paid for and 10 days old and all the receipts for those items ....recursively

 

--In other words I don't want mark an item as fully paid so long as any item any of its receipts apply to are also 10 days old and "fully paid."

 

I think I should search the join table for self-contain subsets : i.e. its specific set of (item_id, receipt_id) value pairs where none of the individual id values occur in a record not in this subset.

 

Then if and only if

 

all the Items relating to any of those pairs are fully paid up and 10 days and ,

 

all the related receipts show as "payment completed"

 

I can mark all those items as fully paid.

 

I will get there - but am posting this as I am sure someone will come up with a really elegant, compact solution (Sql query? Recursive Custom Function?)

 

I have about 8.000 Items to run through, but after the first run the ones paid up (Say $7,500) will not need to be checked again.

 

For Set theory enthusiasts, the nub of the problem is

 

If is "R" relation that connects members of set "S" of ordered pairs to all members of S that share either or both pair value(s) , I need to find all subsets of S that are closed under R

 

Have fun!

Outcomes