users.spt

from dateutil.parser import parse as parse_date

from liberapay.exceptions import LoginRequired
from liberapay.i18n.base import LOCALE_EN as locale
from liberapay.models.participant import Participant

PT_STATUS_MAP = {
    'failed': 'danger',
    'pre': 'muted',
}
ACCOUNT_MARKS = set(website.db.one("SELECT array_to_json(enum_range(NULL::account_mark))"))
ACCOUNT_MARKS.add('')

[---]

if user.ANON:
    raise LoginRequired

if not user.is_admin:
    raise response.error(403)

if request.method == 'POST':
    event_data = {}
    p = Participant.from_id(request.body.get_int('p_id'))
    updated = 0
    with website.db.get_cursor() as cursor:
        if 'mark_as' in request.body:
            new_value = request.body.get_choice('mark_as', ACCOUNT_MARKS)
            if new_value == '':
                new_value = None
            r = cursor.one("""
                UPDATE participants
                   SET marked_as = %(new_value)s
                 WHERE id = %(p_id)s
                   AND coalesce(marked_as::text, '') <> %(new_value)s
             RETURNING id
            """, dict(p_id=p.id, new_value=new_value))
            if r:
                updated += 1
                event_data['marked_as'] = new_value

        if event_data:
            if request.body.get('reason'):
                event_data['reason'] = request.body['reason']
            p.add_event(cursor, 'flags_changed', event_data, user.id)

    raise response.json({'msg': (
        f"Done, {updated} attribute has been updated." if updated == 1 else
        f"Done, {updated} attributes have been updated."
    )})

last_showed = request.qs.get_int('last_showed', default=None)
mode = request.qs.get('mode', 'all')
if mode == 'all':
    participants = website.db.all("""
        SELECT p
             , (SELECT c.name FROM communities c WHERE c.participant = p.id) AS c_name
             , ( SELECT json_build_object(
                            'address', e.address,
                            'verified', e.verified,
                            'blacklisted', EXISTS (
                                SELECT 1
                                  FROM email_blacklist bl
                                 WHERE lower(bl.address) = lower(e.address)
                                   AND (bl.ignore_after IS NULL OR bl.ignore_after > current_timestamp)
                            )
                        )
                   FROM emails e
                  WHERE e.participant = p.id
               ORDER BY e.address = p.email DESC NULLS LAST
                      , e.verified DESC
                      , e.added_time
                  LIMIT 1
               ) AS email
             , ( SELECT json_agg(json_build_object(
                            'provider', a.provider,
                            'id', a.id,
                            'country', a.country,
                            'charges_enabled', a.charges_enabled,
                            'verified', a.verified
                        ))
                   FROM ( SELECT a.*
                            FROM payment_accounts a
                           WHERE a.participant = p.id
                             AND a.is_current
                        ORDER BY a.provider DESC, a.id DESC
                        ) a
               ) AS payment_accounts
             , ( SELECT json_agg(json_build_object(
                            'type', s.type, 'content', s.content, 'lang', s.lang
                        ))
                   FROM ( SELECT s.*
                            FROM statements s
                           WHERE s.participant = p.id
                        ORDER BY s.lang, s.type DESC
                        ) s
               ) AS statements
             , ( SELECT e.payload
                   FROM events e
                  WHERE e.participant = p.id
                    AND e.type = 'sign_up_request'
               ) AS sign_up_request
             , ( SELECT row_to_json(e)
                   FROM ( SELECT e.*, p2.username AS recorder_name
                            FROM events e
                       LEFT JOIN participants p2 ON p2.id = e.recorder
                           WHERE e.participant = p.id
                             AND e.type IN ('is_suspended', 'visibility_override', 'flags_changed')
                        ORDER BY ts DESC
                           LIMIT 1
                        ) e
               ) AS last_change
          FROM participants p
         WHERE coalesce(p.id < %s, true)
           AND (p.status <> 'stub' OR p.receiving > 0)
      ORDER BY p.id DESC
         LIMIT 25
    """, (last_showed,))
    page_end = participants[-1][0].id if participants else None
elif mode == 'new_recipients':
    participants = website.db.all("""
        SELECT p
             , ( SELECT json_build_object(
                            'address', e.address,
                            'verified', e.verified,
                            'blacklisted', EXISTS (
                                SELECT 1
                                  FROM email_blacklist bl
                                 WHERE lower(bl.address) = lower(e.address)
                                   AND (bl.ignore_after IS NULL OR bl.ignore_after > current_timestamp)
                            )
                        )
                   FROM emails e
                  WHERE e.participant = p.id
               ORDER BY e.address = p.email DESC NULLS LAST
                      , e.verified DESC
                      , e.added_time
                  LIMIT 1
               ) AS email
             , ( SELECT json_agg(json_build_object(
                            'provider', a.provider,
                            'id', a.id,
                            'country', a.country,
                            'charges_enabled', a.charges_enabled,
                            'verified', a.verified
                        ))
                   FROM ( SELECT a.*
                            FROM payment_accounts a
                           WHERE a.participant = p.id
                             AND a.is_current
                        ORDER BY a.provider DESC, a.id DESC
                        ) a
               ) AS payment_accounts
             , ( SELECT json_agg(json_build_object(
                            'type', s.type, 'content', s.content, 'lang', s.lang
                        ))
                   FROM ( SELECT s.*
                            FROM statements s
                           WHERE s.participant = p.id
                        ORDER BY s.lang, s.type DESC
                        ) s
               ) AS statements
             , ( SELECT row_to_json(e)
                   FROM ( SELECT e.*, p2.username AS recorder_name
                            FROM events e
                       LEFT JOIN participants p2 ON p2.id = e.recorder
                           WHERE e.participant = p.id
                             AND e.type IN ('is_suspended', 'visibility_override', 'flags_changed')
                        ORDER BY ts DESC
                           LIMIT 1
                        ) e
               ) AS last_change
             , json_agg(json_build_object(
                   'amount', pt.amount, 'context', pt.context, 'ctime', date_trunc('second', pt.ctime),
                   'payer', pt.payer, 'payer_name', payer.username,
                   'team', pt.team, 'team_name', team.username,
                   'status', pt.status, 'error', pt.error, 'payin_method', r.network,
                   'id', pt.id
               ) ORDER BY pt.row_number) AS payments
          FROM ( SELECT pt.*
                      , row_number() OVER (PARTITION BY pt.recipient ORDER BY pt.ctime DESC, pt.id DESC)
                   FROM payin_transfers pt
                  WHERE pt.status <> 'pre'
                    AND coalesce(pt.error <> 'aborted by payer', true)
               ) pt
          JOIN payins pi ON pi.id = pt.payin
          JOIN exchange_routes r ON r.id = pi.route
          JOIN participants p ON p.id = pt.recipient
          JOIN participants payer ON payer.id = pt.payer
     LEFT JOIN participants team ON team.id = pt.team
         WHERE p.marked_as IS NULL
           AND pt.row_number <= 20
      GROUP BY p.id
        HAVING coalesce(min(pt.id) < %s, true)
      ORDER BY min(pt.id) DESC
         LIMIT 25
    """, (last_showed,))
    page_end = participants[-1][-1][-1]['id'] if participants else None
else:
    raise response.invalid_input(mode, 'mode', 'querystring')

title = "Users Admin"

[---] text/html
% from 'templates/macros/admin.html' import admin_form with context
% from 'templates/macros/avatar-url.html' import avatar_img with context
% from 'templates/macros/icons.html' import fontawesome, glyphicon
% from 'templates/macros/nav.html' import querystring_nav with context
% from "templates/macros/payment-methods.html" import payment_method_icon with context

% extends "templates/layouts/admin.html"

% block content

<noscript><div class="alert alert-danger">{{ _("JavaScript is required") }}</div></noscript>

<ul class="nav nav-pills">{{ querystring_nav('mode', [
    ('all', "All"),
    ('new_recipients', "New Recipients"),
], mode, derive=False) }}</ul>
<br><br>

% for row in participants
% set p = row.p
<div class="row user-admin">
    <div class="col-md-2 mini-user-admin">
        <a href="/{{ p.username }}/">
            <div class="name">{{ row.c_name or p.username }}<br>({{ p.kind }}, {{ p.status }})</div>
            {{ avatar_img(p, size=120) }}
            <div class="age">{{ locale.format_timedelta(to_age(p.join_time), add_direction=True) if p.join_time }}</div>
        </a>
        % if p.status != 'stub' and p.is_person
        <br>
        <a class="btn btn-default btn-sm" href="{{ p.path('ledger') }}">Go to ledger</a>
        % endif
    </div>
    <div class="col-md-6">
    % set elsewhere = p.get_accounts_elsewhere()
    % if p.status == 'stub'
        % for account in elsewhere
            <div class="account">
                <a class="account-link" href="{{ account.html_url }}">
                    {{ fontawesome(account.platform) }}
                    <span class="account-username">{{ account.friendly_name_long }}</span>
                </a>
                <br>
                % if account.description
                    <section class="profile-statement embedded raw">{{
                        account.get_excerpt(500)
                    }}</section>
                % else
                    (no description)
                % endif
            </div>
        % else
            (ghost stub account)
        % endfor
    % else
        % if p.kind != 'community'
            <p>
            % if row.email
                <strong>Email address:</strong> <span class="break-word-anywhere">{{ row.email.address }}</span>
                % if row.email.verified
                <span class="text-success">{{ glyphicon('ok-sign', "Verified") }}</span>
                % elif row.email.blacklisted
                <span class="text-danger">{{ glyphicon('exclamation-sign', "Blacklisted") }}</span>
                % else
                <span class="text-warning">{{ glyphicon('warning-sign', "Unconfirmed") }}</span>
                % endif
            % else
                <strong>Email address:</strong> none.
            % endif
            </p>

        <p><strong>Elsewhere accounts:</strong>
        % if elsewhere
            <br>
            % for account in elsewhere
                <a class="account-link" href="{{ account.html_url }}">
                    <span class="symbol-3ex">{{ fontawesome(account.platform) }}</span>
                    <span>{{ account.friendly_name_long }}</span>
                </a>
                <br>
            % endfor
        % else
            none. <span class="text-warning">{{ glyphicon('warning-sign') }}</span>
        % endif
        </p>
        % endif

        % if p.is_person
        <p><strong>Payment accounts:</strong>
        % if row.payment_accounts
            <br>
            % for account in row.payment_accounts
                <span class="symbol-3ex">{{
                    fontawesome('cc-stripe', "Stripe") if account.provider == 'stripe' else
                    fontawesome('paypal', "PayPal") if account.provider == 'paypal' else
                    fontawesome(account.provider, account.provider)
                }}</span>
                {{ account.id }}
                ({{ locale.countries.get(account.country, account.country) }})
                % if account.verified and account.charges_enabled != False
                    <span class="symbol text-success">{{ glyphicon('ok-sign', "OK") }}</span>
                % elif account.verified
                    <span class="symbol text-danger">{{ glyphicon('exclamation-sign', "Disabled") }}</span>
                % else
                    <span class="symbol text-warning">{{ glyphicon('warning-sign', "Unconfirmed") }}</span>
                % endif
                <br>
            % endfor
        % else
            none.
        % endif
        </p>

        % if row.payments
        <p><strong>Incoming payments:</strong><br>
            % for pt in row.payments
                <span class="symbol-3ex">{{ payment_method_icon(pt.payin_method) }}</span>
                {{ locale.format_money(Money(**pt.amount)) }}
                from <a href="/~{{ pt.payer }}/">{{ pt.payer_name }}</a>
                % set ctime = parse_date(pt.ctime)
                on {{ locale.format_date(ctime, 'short_yearless') }}
                at {{ locale.format_time(ctime) }}
                % if pt.team
                through team <a href="/~{{ pt.team }}/">{{ pt.team_name }}</a>
                % endif
                % if pt.status != 'succeeded'
                    ‒ <span class="text-{{ PT_STATUS_MAP.get(pt.status, 'info') }}">{{ pt.status }}</span>
                    % if pt.error
                        ‒ error: <code>{{ pt.error }}</code>
                    % endif
                % endif
                <br>
            % endfor
        </p>
        % endif

        % if row.sign_up_request is defined
        % set headers = (row.sign_up_request or {}).get('headers', {})
        % set ip_address = headers.get('Cf-Connecting-Ip')
        % set country_code = headers.get('Cf-Ipcountry')
        % if country_code
            % set country_name = locale.countries.get(country_code, country_code)
        % else
            % set country_name = 'country unknown'
        % endif
        % if ip_address
        <p><strong>IP address:</strong> {{ ip_address }} ({{ country_name }})</p>
        % else
        <p><strong>IP address:</strong> unknown.</p>
        % endif
        % endif
        % endif

        <div><strong>Profile descriptions:</strong>
        % if p.public_name
            <br>
            [<em>public_name</em>] <span class="break-word-anywhere">{{ p.public_name }}</span>
        % endif
        % if row.statements
            <br>
            % for s in row.statements or ()
                [<em>{{ locale.languages_2.get(s.lang, s.lang) }} {{ s.type }}</em>]
                % if s.type in ('profile', 'sidebar')
                    <section class="profile-statement embedded raw" lang="{{ s.lang }}">{{
                        s.content
                    }}</section>
                % else
                    {{ s.content }}
                % endif
                <br>
            % endfor
        % elif not p.public_name
            none. <span class="text-warning">{{ glyphicon('warning-sign') }}</span>
        % endif
        </div>
    % endif
    </div>
    <div class="col-md-4">
        {{ admin_form(p) }}
        <br>
        % if row.last_change
        <span class="text-muted">{{ _(
            "Last changed {timespan_ago} by {username}.",
            username=row.last_change.recorder_name,
            timespan_ago=to_age(parse_date(row.last_change.ts))
        ) }}</span>
        % endif
    </div>
</div>
<br>
% else
<p>Nothing to show.</p>
% endfor

% if page_end
<a class="btn btn-default btn-lg" href="{{ request.qs.derive(last_showed=page_end) }}">{{ _("Next") }} →</a>
% endif

% endblock