def transfer(db, tipper, tippee, amount, context, **kw): tipper_wallet = NS(remote_id=kw.get('tipper_wallet_id'), remote_owner_id=kw.get('tipper_mango_id')) if not all(tipper_wallet.__dict__.values()): tipper_wallet = Participant.from_id(tipper).get_current_wallet( amount.currency) tippee_wallet = NS(remote_id=kw.get('tippee_wallet_id'), remote_owner_id=kw.get('tippee_mango_id')) if not all(tippee_wallet.__dict__.values()): tippee_wallet = Participant.from_id(tippee).get_current_wallet( amount.currency, create=True) wallet_from = tipper_wallet.remote_id wallet_to = tippee_wallet.remote_id t_id = prepare_transfer( db, tipper, tippee, amount, context, wallet_from, wallet_to, team=kw.get('team'), invoice=kw.get('invoice'), bundles=kw.get('bundles'), unit_amount=kw.get('unit_amount'), ) tr = Transfer() tr.AuthorId = tipper_wallet.remote_owner_id tr.CreditedUserId = tippee_wallet.remote_owner_id tr.CreditedWalletId = wallet_to tr.DebitedFunds = amount.int() tr.DebitedWalletId = wallet_from tr.Fees = Money(0, amount.currency) tr.Tag = str(t_id) return execute_transfer(db, t_id, tr), t_id
def try_to_swap_bundle(cursor, b, original_owner): """Attempt to switch a disputed cash bundle with a "safe" one. """ currency = b.amount.currency swappable_origin_bundles = [NS(d._asdict()) for d in cursor.all(""" SELECT * FROM cash_bundles WHERE owner = %s AND disputed IS NOT TRUE AND locked_for IS NULL AND amount::currency = %s ORDER BY ts ASC """, (original_owner, currency))] try_to_swap_bundle_with(cursor, b, swappable_origin_bundles) merge_cash_bundles(cursor, original_owner) if b.withdrawal: withdrawer = cursor.one( "SELECT participant FROM exchanges WHERE id = %s", (b.withdrawal,) ) swappable_recipient_bundles = [NS(d._asdict()) for d in cursor.all(""" SELECT * FROM cash_bundles WHERE owner = %s AND disputed IS NOT TRUE AND locked_for IS NULL AND amount::currency = %s ORDER BY ts ASC, amount = %s DESC """, (withdrawer, currency, b.amount))] # Note: we don't restrict the date in the query above, so a swapped # bundle can end up "withdrawn" before it was even created try_to_swap_bundle_with(cursor, b, swappable_recipient_bundles) merge_cash_bundles(cursor, withdrawer) else: merge_cash_bundles(cursor, b.owner)
def transfer_takes(cursor, team_id, currency): """Resolve and transfer takes for the specified team """ args = dict(team_id=team_id) tips = [ NS(t._asdict()) for t in cursor.all( """ UPDATE payday_tips AS t SET is_funded = true WHERE tippee = %(team_id)s AND check_tip_funding(t) IS true RETURNING t.id, t.tipper, t.amount AS full_amount , coalesce_currency_amount(( SELECT sum(tr.amount, t.amount::currency) FROM transfers tr WHERE tr.tipper = t.tipper AND tr.team = %(team_id)s AND tr.context = 'take' AND tr.status = 'succeeded' ), t.amount::currency) AS past_transfers_sum """, args) ] takes = [ NS(t._asdict()) for t in cursor.all( """ SELECT t.member, t.amount, p.main_currency, p.accepted_currencies FROM payday_takes t JOIN payday_participants p ON p.id = t.member WHERE t.team = %(team_id)s; """, args) ] transfers, leftover = Payday.resolve_takes(tips, takes, currency) for t in transfers: cursor.run("SELECT transfer(%s, %s, %s, 'take', %s, NULL)", (t.tipper, t.member, t.amount, team_id))
def recover_lost_funds(db, exchange, lost_amount, repudiation_id): """Recover as much money as possible from a payin which has been reverted. """ original_owner = exchange.participant # Try (again) to swap the disputed bundles with db.get_cursor() as cursor: cursor.run("LOCK TABLE cash_bundles IN EXCLUSIVE MODE") disputed_bundles = [ NS(d._asdict()) for d in cursor.all( """ SELECT * FROM cash_bundles WHERE origin = %s AND disputed = true """, (exchange.id, )) ] bundles_sum = sum(b.amount for b in disputed_bundles) assert bundles_sum == lost_amount - exchange.fee for b in disputed_bundles: if b.owner == original_owner: continue try_to_swap_bundle(cursor, b, original_owner) # Move the funds back to the original wallet currency = exchange.amount.currency chargebacks_account, credit_wallet = Participant.get_chargebacks_account( currency) LiberapayOrg = Participant.from_username('LiberapayOrg') assert LiberapayOrg return_payin_bundles_to_origin(db, exchange, LiberapayOrg, create_debts=True) # Add a debt for the fee create_debt(db, original_owner, LiberapayOrg.id, exchange.fee, exchange.id) # Send the funds to the credit wallet # We have to do a SettlementTransfer instead of a normal Transfer. The amount # can't exceed the original payin amount, so we can't settle the fee debt. original_owner = Participant.from_id(original_owner) from_wallet = original_owner.get_current_wallet(currency).remote_id to_wallet = credit_wallet.remote_id t_id = prepare_transfer( db, original_owner.id, chargebacks_account.id, exchange.amount, 'chargeback', from_wallet, to_wallet, prefer_bundles_from=exchange.id, ) tr = SettlementTransfer() tr.AuthorId = original_owner.mangopay_user_id tr.CreditedUserId = chargebacks_account.mangopay_user_id tr.CreditedWalletId = to_wallet tr.DebitedFunds = exchange.amount.int() tr.DebitedWalletId = from_wallet tr.Fees = Money(0, currency) tr.RepudiationId = repudiation_id tr.Tag = str(t_id) return execute_transfer(db, t_id, tr)
def shuffle(self, log_dir='.'): if self.stage > 2: return get_transfers = lambda: [ NS(t._asdict()) for t in self.db.all(""" SELECT t.* , w.remote_owner_id AS tipper_mango_id , w2.remote_owner_id AS tippee_mango_id , w.remote_id AS tipper_wallet_id , w2.remote_id AS tippee_wallet_id FROM payday_transfers t LEFT JOIN wallets w ON w.owner = t.tipper AND w.balance::currency = t.amount::currency AND w.is_current IS TRUE LEFT JOIN wallets w2 ON w2.owner = t.tippee AND w2.balance::currency = t.amount::currency AND w2.is_current IS TRUE ORDER BY t.id """) ] if self.stage == 2: transfers = get_transfers() done = self.db.all( """ SELECT * FROM transfers t WHERE t.timestamp >= %(ts_start)s AND status = 'succeeded' """, dict(ts_start=self.ts_start)) done = set((t.tipper, t.tippee, t.context, t.team) for t in done) transfers = [ t for t in transfers if (t.tipper, t.tippee, t.context, t.team) not in done ] else: assert self.stage == 1 with self.db.get_cursor() as cursor: self.prepare(cursor, self.ts_start) self.transfer_virtually(cursor, self.ts_start) self.check_balances(cursor) cursor.run(""" UPDATE paydays SET nparticipants = (SELECT count(*) FROM payday_participants) WHERE ts_end='1970-01-01T00:00:00+00'::timestamptz; """) self.mark_stage_done(cursor) self.clean_up() transfers = get_transfers() self.transfer_for_real(transfers) self.settle_debts(self.db) self.db.self_check() self.mark_stage_done() self.db.run("DROP TABLE payday_transfers")
def on_response(self, *consumed): assert len(consumed) <= 4 c1, c2, c3, c4 = (consumed * 4)[:4] remaining = (2300 - c1, 4500 - c2, 8800 - c3, 105600 - c4) now = datetime.utcnow().replace(tzinfo=utc) ts_now = int((now - EPOCH).total_seconds()) reset = (ts_now + 15*60, ts_now + 30*60, ts_now + 60*60, ts_now + 24*60*60) watcher.on_response(None, result=NS(headers={ 'X-RateLimit': ', '.join(map(str, consumed)), 'X-RateLimit-Remaining': ', '.join(map(str, remaining)), 'X-RateLimit-Reset': ', '.join(map(str, reset)), }))
def return_payin_bundles_to_origin(db, exchange, last_resort_payer, create_debts=True): """Transfer money linked to a specific payin back to the original owner. """ currency = exchange.amount.currency chargebacks_account = Participant.get_chargebacks_account(currency)[0] original_owner = exchange.participant origin_wallet = db.one("SELECT * FROM wallets WHERE remote_id = %s", (exchange.wallet_id, )) transfer_kw = dict( tippee_wallet_id=origin_wallet.remote_id, tippee_mango_id=origin_wallet.remote_owner_id, ) payin_bundles = [ NS(d._asdict()) for d in db.all( """ SELECT * FROM cash_bundles WHERE origin = %s AND disputed = true """, (exchange.id, )) ] grouped = group_by(payin_bundles, lambda b: (b.owner, b.withdrawal)) for (current_owner, withdrawal), bundles in grouped.items(): assert current_owner != chargebacks_account.id if current_owner == original_owner: continue amount = sum(b.amount for b in bundles) if current_owner is None: if not last_resort_payer or not create_debts: continue bundles = None withdrawer = db.one( "SELECT participant FROM exchanges WHERE id = %s", (withdrawal, )) payer = last_resort_payer.id create_debt(db, withdrawer, payer, amount, exchange.id) create_debt(db, original_owner, withdrawer, amount, exchange.id) else: bundles = [b.id for b in bundles] payer = current_owner if create_debts: create_debt(db, original_owner, payer, amount, exchange.id) transfer(db, payer, original_owner, amount, 'chargeback', bundles=bundles, **transfer_kw)
def shuffle(self, log_dir='.'): if self.stage > 2: return get_transfers = lambda: [ NS(t._asdict()) for t in self.db.all(""" SELECT t.* , p.mangopay_user_id AS tipper_mango_id , p2.mangopay_user_id AS tippee_mango_id , p.mangopay_wallet_id AS tipper_wallet_id , p2.mangopay_wallet_id AS tippee_wallet_id FROM payday_transfers t JOIN participants p ON p.id = t.tipper JOIN participants p2 ON p2.id = t.tippee ORDER BY t.id """) ] if self.stage == 2: transfers = get_transfers() done = self.db.all( """ SELECT * FROM transfers t WHERE t.timestamp >= %(ts_start)s; """, dict(ts_start=self.ts_start)) done = set((t.tipper, t.tippee, t.context, t.team) for t in done) transfers = [ t for t in transfers if (t.tipper, t.tippee, t.context, t.team) not in done ] else: assert self.stage == 1 with self.db.get_cursor() as cursor: self.prepare(cursor, self.ts_start) self.transfer_virtually(cursor, self.ts_start) self.check_balances(cursor) cursor.run(""" UPDATE paydays SET nparticipants = (SELECT count(*) FROM payday_participants) WHERE ts_end='1970-01-01T00:00:00+00'::timestamptz; """) self.clean_up() self.mark_stage_done() transfers = get_transfers() self.transfer_for_real(transfers) self.settle_debts(self.db) self.db.self_check() self.mark_stage_done() self.db.run("DROP TABLE payday_transfers")
def split_bundle(cursor, b, amount): """Cut a bundle in two. Returns the new second bundle, whose amount is `amount`. """ assert b.amount > amount assert not b.locked_for b.amount = cursor.one(""" UPDATE cash_bundles SET amount = (amount - %s) WHERE id = %s RETURNING amount """, (amount, b.id)) return NS(cursor.one(""" INSERT INTO cash_bundles (owner, origin, amount, ts, withdrawal, disputed, wallet_id) VALUES (%s, %s, %s, %s, %s, %s, %s) RETURNING *; """, (b.owner, b.origin, amount, b.ts, b.withdrawal, b.disputed, b.wallet_id))._asdict())
def lock_disputed_funds(cursor, exchange, amount): """Prevent money that is linked to a chargeback from being withdrawn. """ if amount != exchange.amount + exchange.fee: raise NotImplementedError("partial disputes are not implemented") cursor.run("LOCK TABLE cash_bundles IN EXCLUSIVE MODE") disputed_bundles = [NS(d._asdict()) for d in cursor.all(""" UPDATE cash_bundles SET disputed = true WHERE origin = %s RETURNING * """, (exchange.id,))] disputed_bundles_sum = sum(b.amount for b in disputed_bundles) assert disputed_bundles_sum == exchange.amount original_owner = exchange.participant for b in disputed_bundles: if b.owner == original_owner: continue try_to_swap_bundle(cursor, b, original_owner)
def refund_payin(db, exchange, create_debts=False, refund_fee=False, dry_run=False): """Refund a specific payin. """ assert exchange.status == 'succeeded' and exchange.remote_id, exchange e_refund = db.one("SELECT e.* FROM exchanges e WHERE e.refund_ref = %s", (exchange.id,)) if e_refund and e_refund.status == 'succeeded': return 'already done', e_refund # Lock the bundles and try to swap them with db.get_cursor() as cursor: cursor.run("LOCK TABLE cash_bundles IN EXCLUSIVE MODE") bundles = [NS(d._asdict()) for d in cursor.all(""" UPDATE cash_bundles SET disputed = true WHERE origin = %s RETURNING * """, (exchange.id,))] bundles_sum = sum(b.amount for b in bundles) assert bundles_sum == exchange.amount original_owner = exchange.participant for b in bundles: if b.owner == original_owner: continue try_to_swap_bundle(cursor, b, original_owner) # Move the funds back to the original wallet LiberapayOrg = Participant.from_username('LiberapayOrg') assert LiberapayOrg return_payin_bundles_to_origin(db, exchange, LiberapayOrg, create_debts) # Add a debt for the fee if create_debts and refund_fee: create_debt(db, original_owner, LiberapayOrg.id, exchange.fee, exchange.id) # Compute and check the amount wallet = db.one("SELECT * FROM wallets WHERE remote_id = %s", (exchange.wallet_id,)) if e_refund and e_refund.status == 'pre': amount = -e_refund.amount else: amount = min(wallet.balance, exchange.amount) if amount <= 0: return ('not enough money: wallet balance = %s' % wallet.balance), None # Stop here if this is a dry run zero = exchange.fee.zero() fee, vat = (exchange.fee, exchange.vat) if refund_fee else (zero, zero) if dry_run: msg = ( '[dry run] full refund of payin #%s (liberapay id %s): amount = %s, fee = %s' % (exchange.remote_id, exchange.id, exchange.amount, exchange.fee) ) if amount + fee == exchange.amount + exchange.fee else ( '[dry run] partial refund of payin #%s (liberapay id %s): %s of %s, fee %s of %s' % (exchange.remote_id, exchange.id, amount, exchange.amount, fee, exchange.fee) ) return msg, None # Record the refund attempt participant = Participant.from_id(exchange.participant) if not (e_refund and e_refund.status == 'pre'): with db.get_cursor() as cursor: cursor.run("LOCK TABLE cash_bundles IN EXCLUSIVE MODE") bundles = [NS(d._asdict()) for d in cursor.all(""" SELECT * FROM cash_bundles WHERE origin = %s AND wallet_id = %s AND disputed = true """, (exchange.id, exchange.wallet_id))] e_refund = cursor.one(""" INSERT INTO exchanges (participant, amount, fee, vat, route, status, refund_ref, wallet_id) VALUES (%s, %s, %s, %s, %s, 'pre', %s, %s) RETURNING * """, (participant.id, -amount, -fee, -vat, exchange.route, exchange.id, exchange.wallet_id)) propagate_exchange(cursor, participant, e_refund, None, e_refund.amount, bundles=bundles) # Submit the refund m_refund = PayInRefund(payin_id=exchange.remote_id) m_refund.AuthorId = wallet.remote_owner_id m_refund.Tag = str(e_refund.id) m_refund.DebitedFunds = amount.int() m_refund.Fees = -fee.int() try: m_refund.save() except Exception as e: error = repr_exception(e) e_refund = record_exchange_result(db, e_refund.id, '', 'failed', error, participant) return 'exception', e_refund e_refund = record_exchange_result( db, e_refund.id, m_refund.Id, m_refund.Status.lower(), repr_error(m_refund), participant ) return e_refund.status, e_refund
def recompute_actual_takes(self, cursor, member=None): """Get the tips and takes for this team and recompute the actual amounts. To avoid deadlocks the given `cursor` should have already acquired an exclusive lock on the `takes` table. """ from liberapay.billing.payday import Payday tips = [ NS(t._asdict()) for t in cursor.all( """ SELECT t.id, t.tipper, t.amount AS full_amount, t.paid_in_advance , ( SELECT basket_sum(w.balance) FROM wallets w WHERE w.owner = t.tipper AND w.is_current AND %(use_mangopay)s ) AS balances , coalesce_currency_amount(( SELECT sum(tr.amount, t.amount::currency) FROM transfers tr WHERE tr.tipper = t.tipper AND tr.team = %(team_id)s AND tr.context = 'take' AND tr.status = 'succeeded' ), t.amount::currency) AS past_transfers_sum FROM current_tips t JOIN participants p ON p.id = t.tipper WHERE t.tippee = %(team_id)s AND t.is_funded AND p.is_suspended IS NOT true """, dict(team_id=self.id, use_mangopay=mangopay.sandbox)) ] takes = [ NS(r._asdict()) for r in (cursor or self.db).all( """ SELECT t.*, p.main_currency, p.accepted_currencies FROM current_takes t JOIN participants p ON p.id = t.member WHERE t.team = %s AND p.is_suspended IS NOT true """, (self.id, )) ] # Recompute the takes transfers, new_leftover = Payday.resolve_takes(tips, takes, self.main_currency) transfers_by_member = group_by(transfers, lambda t: t.member) takes_sum = { k: MoneyBasket(t.amount for t in tr_list) for k, tr_list in transfers_by_member.items() } tippers = { k: set(t.tipper for t in tr_list) for k, tr_list in transfers_by_member.items() } # Update the leftover cursor.run("UPDATE participants SET leftover = %s WHERE id = %s", (new_leftover, self.id)) self.set_attributes(leftover=new_leftover) # Update the cached amounts (actual_amount, taking, and receiving) zero = MoneyBasket() for take in takes: member_id = take.member old_amount = take.actual_amount or zero new_amount = takes_sum.get(take.member, zero) diff = new_amount - old_amount if diff != 0: take.actual_amount = new_amount cursor.run( """ UPDATE takes SET actual_amount = %(actual_amount)s WHERE id = %(id)s """, take.__dict__) ntippers = len(tippers.get(member_id, ())) member_currency, old_taking = cursor.one( "SELECT main_currency, taking FROM participants WHERE id = %s", (member_id, )) diff = diff.fuzzy_sum(member_currency) if old_taking + diff < 0: # Make sure currency fluctuation doesn't result in a negative number diff = -old_taking cursor.run( """ UPDATE participants SET taking = (taking + %(diff)s) , receiving = (receiving + %(diff)s) , nteampatrons = ( CASE WHEN (receiving + %(diff)s) = 0 THEN 0 WHEN nteampatrons < %(ntippers)s THEN %(ntippers)s ELSE nteampatrons END ) WHERE id=%(member_id)s """, dict(member_id=member_id, diff=diff, ntippers=ntippers)) if member and member.id == member_id: r = cursor.one( "SELECT taking, receiving FROM participants WHERE id = %s", (member_id, )) member.set_attributes(**r._asdict()) return takes
def recompute_actual_takes(self, cursor, member=None): """Get the tips and takes for this team and recompute the actual amounts. To avoid deadlocks the given `cursor` should have already acquired an exclusive lock on the `takes` table. """ from liberapay.billing.payday import Payday tips = [ NS(t._asdict()) for t in cursor.all( """ SELECT t.id, t.tipper, t.amount AS full_amount , coalesce_currency_amount(( SELECT sum(tr.amount, t.amount::currency) FROM transfers tr WHERE tr.tipper = t.tipper AND tr.team = %(team_id)s AND tr.context = 'take' AND tr.status = 'succeeded' ), t.amount::currency) AS past_transfers_sum FROM current_tips t JOIN participants p ON p.id = t.tipper WHERE t.tippee = %(team_id)s AND t.is_funded AND p.is_suspended IS NOT true """, dict(team_id=self.id)) ] takes = [ NS(r._asdict()) for r in (cursor or self.db).all( """ SELECT t.* FROM current_takes t JOIN participants p ON p.id = t.member WHERE t.team = %s AND p.is_suspended IS NOT true AND p.mangopay_user_id IS NOT NULL """, (self.id, )) ] # Recompute the takes takes_sum = {} tippers = {} transfers, new_leftover = Payday.resolve_takes(tips, takes, self.main_currency) for t in transfers: if t.member in takes_sum: takes_sum[t.member] += t.amount else: takes_sum[t.member] = t.amount if t.member in tippers: tippers[t.member].add(t.tipper) else: tippers[t.member] = set((t.tipper, )) # Update the leftover cursor.run("UPDATE participants SET leftover = %s WHERE id = %s", (new_leftover, self.id)) self.set_attributes(leftover=new_leftover) # Update the cached amounts (actual_amount, taking, and receiving) zero = ZERO[self.main_currency] for take in takes: member_id = take.member old_amount = take.actual_amount or zero new_amount = takes_sum.get(take.member, zero) diff = new_amount - old_amount if diff != 0: take.actual_amount = new_amount cursor.run( """ UPDATE takes SET actual_amount = %(actual_amount)s WHERE id = %(id)s """, take.__dict__) ntippers = len(tippers.get(member_id, ())) member_currency = cursor.one( "SELECT main_currency FROM participants WHERE id = %s", (member_id, )) diff = diff.convert(member_currency) cursor.run( """ UPDATE participants SET taking = (taking + %(diff)s) , receiving = (receiving + %(diff)s) , nteampatrons = ( CASE WHEN (receiving + %(diff)s) = 0 THEN 0 WHEN nteampatrons < %(ntippers)s THEN %(ntippers)s ELSE nteampatrons END ) WHERE id=%(member_id)s """, dict(member_id=member_id, diff=diff, ntippers=ntippers)) if member and member.id == member_id: r = cursor.one( "SELECT taking, receiving FROM participants WHERE id = %s", (member_id, )) member.set_attributes(**r._asdict()) return takes
def recover_lost_funds(db, exchange, lost_amount, repudiation_id): """Recover as much money as possible from a payin which has been reverted. """ original_owner = exchange.participant # Try (again) to swap the disputed bundles with db.get_cursor() as cursor: cursor.run("LOCK TABLE cash_bundles IN EXCLUSIVE MODE") disputed_bundles = [ NS(d._asdict()) for d in cursor.all( """ SELECT * FROM cash_bundles WHERE origin = %s AND disputed = true """, (exchange.id, )) ] bundles_sum = sum(b.amount for b in disputed_bundles) assert bundles_sum == lost_amount - exchange.fee for b in disputed_bundles: if b.owner == original_owner: continue try_to_swap_bundle(cursor, b, original_owner) # Move the funds back to the original wallet chargebacks_account = Participant.get_chargebacks_account() LiberapayOrg = Participant.from_username('LiberapayOrg') assert LiberapayOrg grouped = group_by(disputed_bundles, lambda b: (b.owner, b.withdrawal)) for (owner, withdrawal), bundles in grouped.items(): assert owner != chargebacks_account.id if owner == original_owner: continue amount = sum(b.amount for b in bundles) if owner is None: bundles = None withdrawer = db.one( "SELECT participant FROM exchanges WHERE id = %s", (withdrawal, )) payer = LiberapayOrg.id create_debt(db, withdrawer, payer, amount, exchange.id) create_debt(db, original_owner, withdrawer, amount, exchange.id) else: payer = owner create_debt(db, original_owner, payer, amount, exchange.id) transfer(db, payer, original_owner, amount, 'chargeback', bundles=bundles) # Add a debt for the fee create_debt(db, original_owner, LiberapayOrg.id, exchange.fee, exchange.id) # Send the funds to the credit wallet # We have to do a SettlementTransfer instead of a normal Transfer. The amount # can't exceed the original payin amount, so we can't settle the fee debt. original_owner = Participant.from_id(original_owner) t_id = prepare_transfer( db, original_owner.id, chargebacks_account.id, exchange.amount, 'chargeback', original_owner.mangopay_wallet_id, chargebacks_account.mangopay_wallet_id, prefer_bundles_from=exchange.id, ) tr = SettlementTransfer() tr.AuthorId = original_owner.mangopay_user_id tr.CreditedUserId = chargebacks_account.mangopay_user_id tr.CreditedWalletId = chargebacks_account.mangopay_wallet_id tr.DebitedFunds = Money(int(exchange.amount * 100), 'EUR') tr.DebitedWalletId = original_owner.mangopay_wallet_id tr.Fees = Money(0, 'EUR') tr.RepudiationId = repudiation_id tr.Tag = str(t_id) tr.save() return record_transfer_result(db, t_id, tr)
username='******', email_address='*****@*****.**', session_token='foobar', ) gratipay_response = NS( json=lambda: dict( initial_data, anonymous_giving=False, avatar_url='https://example.net/alice/avatar', email_lang='en', is_searchable=True, email_addresses=[ dict( address=initial_data['email_address'], verified=True, verification_start=EPOCH, verification_end=EPOCH, ), ], payment_instructions=[dict()], # TODO elsewhere=[dict()], # TODO statements=[dict()], # TODO teams=[dict()], # TODO ), status_code=200, ) class TestMigrate(Harness): def test_migrate(self): # Step 1
def resolve_takes(cursor, team_id): """Resolve many-to-many donations (team takes) """ args = dict(team_id=team_id) total_income = cursor.one( """ WITH funded AS ( UPDATE payday_tips SET is_funded = true FROM payday_participants p WHERE p.id = tipper AND tippee = %(team_id)s AND p.new_balance >= amount RETURNING amount ) SELECT COALESCE(sum(amount), 0) FROM funded; """, args) total_takes = cursor.one( """ SELECT COALESCE(sum(t.amount), 0) FROM payday_takes t WHERE t.team = %(team_id)s """, args) if total_income == 0 or total_takes == 0: return args['takes_ratio'] = min(total_income / total_takes, 1) tips_ratio = args['tips_ratio'] = min(total_takes / total_income, 1) tips = [ NS(t._asdict()) for t in cursor.all( """ SELECT t.id, t.tipper, (round_up(t.amount * %(tips_ratio)s, 2)) AS amount , t.amount AS full_amount , COALESCE(( SELECT sum(tr.amount) FROM transfers tr WHERE tr.tipper = t.tipper AND tr.team = %(team_id)s AND tr.context = 'take' AND tr.status = 'succeeded' ), 0) AS past_transfers_sum FROM payday_tips t JOIN payday_participants p ON p.id = t.tipper WHERE t.tippee = %(team_id)s AND p.new_balance >= t.amount """, args) ] takes = [ NS(t._asdict()) for t in cursor.all( """ SELECT t.member, (round_up(t.amount * %(takes_ratio)s, 2)) AS amount FROM payday_takes t WHERE t.team = %(team_id)s; """, args) ] adjust_tips = tips_ratio != 1 if adjust_tips: # The team has a leftover, so donation amounts can be adjusted. # In the following loop we compute the "weeks" count of each tip. # For example the `weeks` value is 2.5 for a donation currently at # 10€/week which has distributed 25€ in the past. for tip in tips: tip.weeks = round_up(tip.past_transfers_sum / tip.full_amount) max_weeks = max(tip.weeks for tip in tips) min_weeks = min(tip.weeks for tip in tips) adjust_tips = max_weeks != min_weeks if adjust_tips: # Some donors have given fewer weeks worth of money than others, # we want to adjust the amounts so that the weeks count will # eventually be the same for every donation. min_tip_ratio = tips_ratio * Decimal('0.1') # Loop: compute how many "weeks" each tip is behind the "oldest" # tip, as well as a naive ratio and amount based on that number # of weeks for tip in tips: tip.weeks_to_catch_up = max_weeks - tip.weeks tip.ratio = min(min_tip_ratio + tip.weeks_to_catch_up, 1) tip.amount = round_up(tip.full_amount * tip.ratio) naive_amounts_sum = sum(tip.amount for tip in tips) total_to_transfer = min(total_takes, total_income) delta = total_to_transfer - naive_amounts_sum if delta == 0: # The sum of the naive amounts computed in the previous loop # matches the end target, we got very lucky and no further # adjustments are required adjust_tips = False else: # Loop: compute the "leeway" of each tip, i.e. how much it # can be increased or decreased to fill the `delta` gap if delta < 0: # The naive amounts are too high: we want to lower the # amounts of the tips that have a "high" ratio, leaving # untouched the ones that are already low for tip in tips: if tip.ratio > min_tip_ratio: min_tip_amount = round_up(tip.full_amount * min_tip_ratio) tip.leeway = min_tip_amount - tip.amount else: tip.leeway = 0 else: # The naive amounts are too low: we can raise all the # tips that aren't already at their maximum for tip in tips: tip.leeway = tip.full_amount - tip.amount leeway = sum(tip.leeway for tip in tips) leeway_ratio = min(delta / leeway, 1) tips = sorted(tips, key=lambda tip: (-tip.weeks_to_catch_up, tip.id)) # Loop: compute the adjusted donation amounts, and do the transfers for tip in tips: if adjust_tips: tip_amount = round_up(tip.amount + tip.leeway * leeway_ratio) if tip_amount == 0: continue assert tip_amount > 0 assert tip_amount <= tip.full_amount tip.amount = tip_amount for take in takes: if take.amount == 0 or tip.tipper == take.member: continue transfer_amount = min(tip.amount, take.amount) cursor.run("SELECT transfer(%s, %s, %s, 'take', %s, NULL)", (tip.tipper, take.member, transfer_amount, team_id)) tip.amount -= transfer_amount take.amount -= transfer_amount if tip.amount == 0: break