def notify_participants(self):
previous_ts_end = self.db.one("""
SELECT ts_end
FROM paydays
WHERE ts_start < %s
ORDER BY ts_end DESC
LIMIT 1
""", (self.ts_start, ),
default=constants.BIRTHDAY)
# Income notifications
r = self.db.all(
"""
SELECT tippee, json_agg(t) AS transfers
FROM transfers t
WHERE "timestamp" > %s
AND "timestamp" <= %s
GROUP BY tippee
""", (previous_ts_end, self.ts_end))
for tippee_id, transfers in r:
successes = [t for t in transfers if t['status'] == 'succeeded']
if not successes:
continue
by_team = {
k: sum(t['amount'] for t in v)
for k, v in group_by(successes, 'team').items()
}
personal = by_team.pop(None, 0)
by_team = {
Participant.from_id(k).username: v
for k, v in by_team.items()
}
Participant.from_id(tippee_id).notify(
'income',
total=sum(t['amount'] for t in successes),
personal=personal,
by_team=by_team,
)
# Low-balance notifications
participants = self.db.all(
"""
SELECT p.*::participants
FROM participants p
WHERE balance < giving
AND giving > 0
AND EXISTS (
SELECT 1
FROM transfers t
WHERE t.tipper = p.id
AND t.timestamp > %s
AND t.timestamp <= %s
AND t.status = 'succeeded'
)
""", (previous_ts_end, self.ts_end))
for p in participants:
p.notify('low_balance')
def notify_participants(self):
previous_ts_end = self.db.one("""
SELECT ts_end
FROM paydays
WHERE ts_start < %s
ORDER BY ts_end DESC
LIMIT 1
""", (self.ts_start,), default=constants.BIRTHDAY)
# Income notifications
r = self.db.all("""
SELECT tippee, json_agg(t) AS transfers
FROM transfers t
WHERE "timestamp" > %s
AND "timestamp" <= %s
GROUP BY tippee
""", (previous_ts_end, self.ts_end))
for tippee_id, transfers in r:
successes = [t for t in transfers if t['status'] == 'succeeded']
if not successes:
continue
by_team = {k: sum(t['amount'] for t in v)
for k, v in group_by(successes, 'team').items()}
personal = by_team.pop(None, 0)
by_team = {Participant.from_id(k).username: v for k, v in by_team.items()}
Participant.from_id(tippee_id).notify(
'income',
total=sum(t['amount'] for t in successes),
personal=personal,
by_team=by_team,
)
# Low-balance notifications
participants = self.db.all("""
SELECT p.*::participants
FROM participants p
WHERE balance < (
SELECT sum(amount)
FROM current_tips t
JOIN participants p2 ON p2.id = t.tippee
WHERE t.tipper = p.id
AND p2.mangopay_user_id IS NOT NULL
AND p2.status = 'active'
)
AND EXISTS (
SELECT 1
FROM transfers t
WHERE t.tipper = p.id
AND t.timestamp > %s
AND t.timestamp <= %s
AND t.status = 'succeeded'
)
""", (previous_ts_end, self.ts_end))
for p in participants:
p.notify('low_balance')
def notify_participants(self):
previous_ts_end = self.db.one(
"""
SELECT ts_end
FROM paydays
WHERE ts_start < %s
ORDER BY ts_end DESC
LIMIT 1
""",
(self.ts_start,),
default=constants.BIRTHDAY,
)
# Income notifications
r = self.db.all(
"""
SELECT tippee, json_agg(t) AS transfers
FROM transfers t
WHERE "timestamp" > %s
AND "timestamp" <= %s
GROUP BY tippee
""",
(previous_ts_end, self.ts_end),
)
for tippee_id, transfers in r:
successes = [t for t in transfers if t["status"] == "succeeded"]
if not successes:
continue
by_team = {k: sum(t["amount"] for t in v) for k, v in group_by(successes, "team").items()}
personal = by_team.pop(None, 0)
by_team = {Participant.from_id(k).username: v for k, v in by_team.items()}
Participant.from_id(tippee_id).notify(
"income", total=sum(t["amount"] for t in successes), personal=personal, by_team=by_team
)
# Low-balance notifications
participants = self.db.all(
"""
SELECT p.*::participants
FROM participants p
WHERE balance < giving
AND giving > 0
AND EXISTS (
SELECT 1
FROM transfers t
WHERE t.tipper = p.id
AND t.timestamp > %s
AND t.timestamp <= %s
AND t.status = 'succeeded'
)
""",
(previous_ts_end, self.ts_end),
)
for p in participants:
p.notify("low_balance")
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 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 notify_participants(self):
previous_ts_end = self.db.one("""
SELECT ts_end
FROM paydays
WHERE ts_start < %s
ORDER BY ts_end DESC
LIMIT 1
""", (self.ts_start, ),
default=constants.BIRTHDAY)
# Income notifications
r = self.db.all(
"""
SELECT tippee, json_agg(t) AS transfers
FROM transfers t
WHERE "timestamp" > %s
AND "timestamp" <= %s
AND context <> 'refund'
GROUP BY tippee
""", (previous_ts_end, self.ts_end))
for tippee_id, transfers in r:
successes = [t for t in transfers if t['status'] == 'succeeded']
if not successes:
continue
by_team = {
k: sum(t['amount'] for t in v)
for k, v in group_by(successes, 'team').items()
}
personal = by_team.pop(None, 0)
by_team = {
Participant.from_id(k).username: v
for k, v in by_team.items()
}
p = Participant.from_id(tippee_id)
p.notify(
'income',
total=sum(t['amount'] for t in successes),
personal=personal,
by_team=by_team,
new_balance=p.balance,
)
# Identity-required notifications
participants = self.db.all("""
SELECT p.*::participants
FROM participants p
WHERE mangopay_user_id IS NULL
AND kind IN ('individual', 'organization')
AND (p.goal IS NULL OR p.goal >= 0)
AND EXISTS (
SELECT 1
FROM current_tips t
JOIN participants p2 ON p2.id = t.tipper
WHERE t.tippee = p.id
AND t.amount > 0
AND p2.balance > t.amount
)
""")
for p in participants:
p.notify('identity_required', force_email=True)
# Low-balance notifications
participants = self.db.all(
"""
SELECT p.*::participants
FROM participants p
WHERE balance < (
SELECT sum(amount)
FROM current_tips t
JOIN participants p2 ON p2.id = t.tippee
WHERE t.tipper = p.id
AND p2.mangopay_user_id IS NOT NULL
AND p2.status = 'active'
AND p2.is_suspended IS NOT true
)
AND EXISTS (
SELECT 1
FROM transfers t
WHERE t.tipper = p.id
AND t.timestamp > %s
AND t.timestamp <= %s
AND t.status = 'succeeded'
)
""", (previous_ts_end, self.ts_end))
for p in participants:
p.notify('low_balance', low_balance=p.balance)
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
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)
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)
tr.save()
return record_transfer_result(db, t_id, tr)
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 notify_participants(self):
previous_ts_end = self.db.one("""
SELECT ts_end
FROM paydays
WHERE ts_start < %s
ORDER BY ts_end DESC
LIMIT 1
""", (self.ts_start, ),
default=constants.BIRTHDAY)
# Income notifications
n = 0
r = self.db.all(
"""
SELECT tippee, json_agg(t) AS transfers
FROM transfers t
WHERE "timestamp" > %s
AND "timestamp" <= %s
AND context IN ('tip', 'take', 'final-gift')
AND status = 'succeeded'
GROUP BY tippee
""", (previous_ts_end, self.ts_end))
for tippee_id, transfers in r:
p = Participant.from_id(tippee_id)
for t in transfers:
t['amount'] = Money(**t['amount'])
by_team = {
k: (MoneyBasket(t['amount']
for t in v), len(set(t['tipper'] for t in v)))
for k, v in group_by(transfers, 'team').items()
}
total = sum((t[0] for t in by_team.values()), MoneyBasket())
nothing = (MoneyBasket(), 0)
personal, personal_npatrons = by_team.pop(None, nothing)
teams = p.get_teams()
team_ids = set(t.id for t in teams) | set(by_team.keys())
team_names = {t.id: t.name for t in teams}
get_username = lambda i: team_names.get(i) or self.db.one(
"SELECT username FROM participants WHERE id = %s", (i, ))
by_team = {
get_username(t_id): by_team.get(t_id, nothing)
for t_id in team_ids
}
p.notify(
'income~v2',
total=total.fuzzy_sum(p.main_currency),
personal=personal,
personal_npatrons=personal_npatrons,
by_team=by_team,
mangopay_balance=p.get_balances(),
)
n += 1
log("Sent %i income notifications." % n)
# Donation renewal notifications
n = 0
participants = self.db.all("""
SELECT (SELECT p FROM participants p WHERE p.id = t.tipper) AS p
, json_agg((SELECT a FROM (
SELECT t.periodic_amount, t.tippee_username
) a))
FROM (
SELECT t.*, p2.username AS tippee_username
FROM current_tips t
JOIN participants p2 ON p2.id = t.tippee
WHERE t.amount > 0
AND ( t.paid_in_advance IS NULL OR
t.paid_in_advance < t.amount
)
AND p2.status = 'active'
AND p2.is_suspended IS NOT true
AND p2.has_payment_account
) t
WHERE EXISTS (
SELECT 1
FROM transfers tr
WHERE tr.tipper = t.tipper
AND COALESCE(tr.team, tr.tippee) = t.tippee
AND tr.context IN ('tip', 'take')
AND tr.status = 'succeeded'
AND tr.timestamp >= (current_timestamp - interval '9 weeks')
)
AND (
SELECT count(*)
FROM notifications n
WHERE n.participant = t.tipper
AND n.event = 'donate_reminder'
AND n.is_new
) < 2
GROUP BY t.tipper
ORDER BY t.tipper
""")
for p, donations in participants:
for tip in donations:
tip['periodic_amount'] = Money(**tip['periodic_amount'])
p.notify('donate_reminder', donations=donations)
n += 1
log("Sent %i donate_reminder notifications." % n)
def resolve_takes(tips, takes, ref_currency):
"""Resolve many-to-many donations (team takes)
"""
total_income = MoneyBasket(t.full_amount for t in tips)
if total_income == 0:
return (), total_income
takes = [t for t in takes if t.amount > 0]
total_takes = MoneyBasket(t.amount for t in takes)
if total_takes == 0:
return (), total_income
fuzzy_income_sum = total_income.fuzzy_sum(ref_currency)
fuzzy_takes_sum = total_takes.fuzzy_sum(ref_currency)
tips_by_currency = group_by(tips, lambda t: t.full_amount.currency)
takes_by_preferred_currency = group_by(takes,
lambda t: t.main_currency)
takes_by_secondary_currency = {c: [] for c in tips_by_currency}
takes_ratio = min(fuzzy_income_sum / fuzzy_takes_sum, 1)
for take in takes:
take.amount = (take.amount * takes_ratio).round_up()
take.accepted_currencies = take.accepted_currencies.split(',')
for accepted in take.accepted_currencies:
skip = (accepted == take.main_currency
or accepted not in takes_by_secondary_currency)
if skip:
continue
takes_by_secondary_currency[accepted].append(take)
tips_ratio = min(fuzzy_takes_sum / fuzzy_income_sum, 1)
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 = (tip.full_amount * tip.ratio).round_up()
naive_amounts_sum = MoneyBasket(
tip.amount for tip in tips).fuzzy_sum(ref_currency)
total_to_transfer = min(fuzzy_takes_sum, fuzzy_income_sum)
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 = (tip.full_amount *
min_tip_ratio).round_up()
tip.leeway = min_tip_amount - tip.amount
else:
tip.leeway = tip.amount.zero()
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 = MoneyBasket(
tip.leeway for tip in tips).fuzzy_sum(ref_currency)
if leeway == 0:
# We don't actually have any leeway, give up
adjust_tips = False
else:
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
transfers = []
for tip in tips:
if adjust_tips:
tip_amount = (tip.amount +
tip.leeway * leeway_ratio).round_up()
if tip_amount == 0:
continue
assert tip_amount > 0
assert tip_amount <= tip.full_amount
tip.amount = tip_amount
else:
tip.amount = (tip.full_amount * tips_ratio).round_up()
tip_currency = tip.amount.currency
sorted_takes = chain(
takes_by_preferred_currency.get(tip_currency, ()),
takes_by_secondary_currency.get(tip_currency, ()))
for take in sorted_takes:
if take.amount == 0 or tip.tipper == take.member:
continue
transfer_amount = min(tip.amount,
take.amount.convert(tip_currency))
transfers.append(
TakeTransfer(tip.tipper, take.member, transfer_amount))
if transfer_amount == tip.amount:
take.amount -= transfer_amount.convert(
take.amount.currency)
else:
take.amount = take.amount.zero()
tip.amount -= transfer_amount
if tip.amount == 0:
break
leftover = total_income - MoneyBasket(t.amount for t in transfers)
return transfers, leftover
def notify_participants(self):
previous_ts_end = self.db.one("""
SELECT ts_end
FROM paydays
WHERE ts_start < %s
ORDER BY ts_end DESC
LIMIT 1
""", (self.ts_start,), default=constants.BIRTHDAY)
# Income notifications
n = 0
get_username = lambda i: self.db.one(
"SELECT username FROM participants WHERE id = %s", (i,)
)
r = self.db.all("""
SELECT tippee, json_agg(t) AS transfers
FROM transfers t
WHERE "timestamp" > %s
AND "timestamp" <= %s
AND context IN ('tip', 'take', 'final-gift')
AND status = 'succeeded'
GROUP BY tippee
""", (previous_ts_end, self.ts_end))
for tippee_id, transfers in r:
p = Participant.from_id(tippee_id)
for t in transfers:
t['amount'] = Money(**t['amount'])
by_team = {k: MoneyBasket(t['amount'] for t in v)
for k, v in group_by(transfers, 'team').items()}
total = sum(by_team.values(), MoneyBasket())
personal = by_team.pop(None, 0)
by_team = {get_username(k): v for k, v in by_team.items()}
p.notify(
'income',
total=total.fuzzy_sum(p.main_currency),
personal=personal,
by_team=by_team,
new_balance=p.get_balances(),
)
n += 1
log("Sent %i income notifications." % n)
# Identity-required notifications
n = 0
participants = self.db.all("""
SELECT p
FROM participants p
WHERE mangopay_user_id IS NULL
AND kind IN ('individual', 'organization')
AND (p.goal IS NULL OR p.goal >= 0)
AND EXISTS (
SELECT 1
FROM current_tips t
JOIN participants p2 ON p2.id = t.tipper
WHERE t.tippee = p.id
AND t.amount > 0
AND t.is_funded
)
AND NOT EXISTS (
SELECT 1
FROM notifications n
WHERE n.participant = p.id
AND n.event = 'identity_required'
AND n.ts > (current_timestamp - interval '6 months')
AND (n.is_new OR n.email_sent IS TRUE)
)
""")
for p in participants:
p.notify('identity_required', force_email=True)
n += 1
log("Sent %i identity_required notifications." % n)
# Low-balance notifications
n = 0
participants = self.db.all("""
SELECT p, COALESCE(w.balance, zero(needed)) AS balance, needed
FROM (
SELECT t.tipper, sum(t.amount) AS needed
FROM current_tips t
JOIN participants p2 ON p2.id = t.tippee
WHERE (p2.mangopay_user_id IS NOT NULL OR p2.kind = 'group')
AND p2.status = 'active'
AND p2.is_suspended IS NOT true
GROUP BY t.tipper, t.amount::currency
) a
JOIN participants p ON p.id = a.tipper
LEFT JOIN wallets w ON w.owner = p.id
AND w.balance::currency = needed::currency
AND w.is_current IS TRUE
WHERE COALESCE(w.balance, zero(needed)) < needed
AND EXISTS (
SELECT 1
FROM transfers t
WHERE t.tipper = p.id
AND t.timestamp > %s
AND t.timestamp <= %s
AND t.status = 'succeeded'
AND t.amount::currency = needed::currency
)
""", (previous_ts_end, self.ts_end))
for p, balance, needed in participants:
p.notify('low_balance', low_balance=balance, needed=needed)
n += 1
log("Sent %i low_balance notifications." % n)
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 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
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)
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 notify_participants(self):
previous_ts_end = self.db.one("""
SELECT ts_end
FROM paydays
WHERE ts_start < %s
ORDER BY ts_end DESC
LIMIT 1
""", (self.ts_start,), default=constants.BIRTHDAY)
# Income notifications
n = 0
get_username = lambda i: self.db.one(
"SELECT username FROM participants WHERE id = %s", (i,)
)
r = self.db.all("""
SELECT tippee, json_agg(t) AS transfers
FROM transfers t
WHERE "timestamp" > %s
AND "timestamp" <= %s
AND context IN ('tip', 'take', 'final-gift')
AND status = 'succeeded'
GROUP BY tippee
""", (previous_ts_end, self.ts_end))
for tippee_id, transfers in r:
p = Participant.from_id(tippee_id)
for t in transfers:
t['amount'] = Money(**t['amount'])
by_team = {k: MoneyBasket.sum(t['amount'] for t in v)
for k, v in group_by(transfers, 'team').items()}
total = sum(by_team.values(), MoneyBasket())
personal = by_team.pop(None, 0)
by_team = {get_username(k): v for k, v in by_team.items()}
p.notify(
'income',
total=total.fuzzy_sum(p.main_currency),
personal=personal,
by_team=by_team,
new_balance=p.get_balances(),
)
n += 1
log("Sent %i income notifications." % n)
# Identity-required notifications
n = 0
participants = self.db.all("""
SELECT p
FROM participants p
WHERE mangopay_user_id IS NULL
AND kind IN ('individual', 'organization')
AND (p.goal IS NULL OR p.goal >= 0)
AND EXISTS (
SELECT 1
FROM current_tips t
JOIN participants p2 ON p2.id = t.tipper
WHERE t.tippee = p.id
AND t.amount > 0
AND t.is_funded
)
""")
for p in participants:
p.notify('identity_required', force_email=True)
n += 1
log("Sent %i identity_required notifications." % n)
# Low-balance notifications
n = 0
participants = self.db.all("""
SELECT p, COALESCE(w.balance, zero(needed)) AS balance, needed
FROM (
SELECT t.tipper, sum(t.amount) AS needed
FROM current_tips t
JOIN participants p2 ON p2.id = t.tippee
WHERE p2.mangopay_user_id IS NOT NULL
AND p2.status = 'active'
AND p2.is_suspended IS NOT true
GROUP BY t.tipper, t.amount::currency
) a
JOIN participants p ON p.id = a.tipper
LEFT JOIN wallets w ON w.owner = p.id
AND w.balance::currency = needed::currency
AND w.is_current IS TRUE
WHERE COALESCE(w.balance, zero(needed)) < needed
AND EXISTS (
SELECT 1
FROM transfers t
WHERE t.tipper = p.id
AND t.timestamp > %s
AND t.timestamp <= %s
AND t.status = 'succeeded'
AND t.amount::currency = needed::currency
)
""", (previous_ts_end, self.ts_end))
for p, balance, needed in participants:
p.notify('low_balance', low_balance=balance, needed=needed)
n += 1
log("Sent %i low_balance notifications." % n)
def notify_participants(self):
previous_ts_end = self.db.one("""
SELECT ts_end
FROM paydays
WHERE ts_start < %s
ORDER BY ts_end DESC
LIMIT 1
""", (self.ts_start, ),
default=constants.BIRTHDAY)
# Income notifications
r = self.db.all(
"""
SELECT tippee, json_agg(t) AS transfers
FROM transfers t
WHERE "timestamp" > %s
AND "timestamp" <= %s
AND context IN ('tip', 'take', 'final-gift')
GROUP BY tippee
""", (previous_ts_end, self.ts_end))
for tippee_id, transfers in r:
successes = [t for t in transfers if t['status'] == 'succeeded']
if not successes:
continue
p = Participant.from_id(tippee_id)
for t in transfers:
t['amount'] = Money(**t['amount'])
t['converted_amount'] = t['amount'].convert(p.main_currency)
by_team = {
k: sum(t['converted_amount'] if k is None else t['amount']
for t in v)
for k, v in group_by(successes, 'team').items()
}
personal = by_team.pop(None, 0)
by_team = {
Participant.from_id(k).username: v
for k, v in by_team.items()
}
p.notify(
'income',
total=sum(t['converted_amount'] for t in successes),
personal=personal,
by_team=by_team,
new_balance=p.balance,
)
# Identity-required notifications
participants = self.db.all("""
SELECT p
FROM participants p
WHERE mangopay_user_id IS NULL
AND kind IN ('individual', 'organization')
AND (p.goal IS NULL OR p.goal >= 0)
AND EXISTS (
SELECT 1
FROM current_tips t
JOIN participants p2 ON p2.id = t.tipper
WHERE t.tippee = p.id
AND t.amount > 0
AND t.is_funded
)
""")
for p in participants:
p.notify('identity_required', force_email=True)
# Low-balance notifications
participants = self.db.all(
"""
SELECT p, needed
FROM (
SELECT t.tipper, sum(t.amount) AS needed
FROM current_tips t
JOIN participants p2 ON p2.id = t.tippee
WHERE p2.mangopay_user_id IS NOT NULL
AND p2.status = 'active'
AND p2.is_suspended IS NOT true
GROUP BY t.tipper, t.amount::currency
) a
JOIN participants p ON p.id = a.tipper
LEFT JOIN wallets w ON w.owner = p.id AND w.balance::currency = needed::currency
WHERE COALESCE(w.balance, zero(needed)) < needed
AND EXISTS (
SELECT 1
FROM transfers t
WHERE t.tipper = p.id
AND t.timestamp > %s
AND t.timestamp <= %s
AND t.status = 'succeeded'
)
""", (previous_ts_end, self.ts_end))
for p, needed in participants:
p.notify('low_balance', low_balance=p.balance, needed=needed)