1 Reply Latest reply on Aug 4, 2014 7:29 AM by LyndsayHowarth

    Intriguing Find problem

    Oliver_Reid

      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!

        • 1. Re: Intriguing Find problem
          LyndsayHowarth

          You might find this is easier to solve using a progressive boolean methodoldogy to obtain your sets.

           

          Each outstanding item in your hierachy of checks has a 0 or 1 value... at a join-record level and at a sum(join-record::check1) level. If at the end of the process you end up with >0... then it is not "fully paid up".

           

          - Lyndsay