<commit_before>from aspen import Response
from gittip import db

^L
if not user.ADMIN:
    raise Response(404)


_suspicious = db.fetchall("""

    SELECT id
         , balance
      FROM participants
     WHERE is_suspicious
  ORDER BY ctime

""")
if _suspicious is None:
    _suspicious = []


def suspicious():
    for person in _suspicious:
        person['gives_to'] = db.fetchall("""

            SELECT DISTINCT tippee AS id
                 , is_suspicious
              FROM tips
              JOIN participants p
                ON tippee = p.id
             WHERE tipper=%s


        """, (person['id'],))
        person['receives_from'] = db.fetchall("""

            SELECT DISTINCT tipper AS id
                 , is_suspicious
              FROM tips
              JOIN participants p
                ON tipper = p.id
             WHERE tippee=%s

        """, (person['id'],))

        person['transfers'] = db.fetchall("""

            SELECT tippee
                 , tipper
                 , amount
              FROM transfers
             WHERE tipper=%s OR tippee=%s
          ORDER BY timestamp


        """, (person['id'], person['id']))

        person['exchanges'] = db.fetchall("""

            SELECT amount, fee, timestamp
              FROM exchanges
             WHERE participant_id=%s
          ORDER BY timestamp


        """, (person['id'],))

        yield person
suspicious = list(suspicious())
names = set([person['id'] for person in suspicious])

^L
<style>
    TABLE {
        width: auto;
    }
    TD, TH {
        text-align: left;
        vertical-align: top;
        border-bottom: 1px solid #B2A196;
    }
    .amount {
        text-align: right;
        padding-right: 6pt;
        font-family: Lucida Mono, monospace;
    }
    .date {
        font-family: Lucida Mono, monospace;
        font-size: smaller;
        color: #999;
    }
    .suspicious {
        font-weight: bold;
        color: red;
    }
    .charge {
        color: red;
    }
</style>
<h3>Fraud, Grommit!</h3>
<table>
    <tr>
        <th>Suspicious Person</th>
        <th></th>
        <th>Balance</th>
        <th>Tip Graph</th>
        <th>Transfers</th>
        <th>Exchanges</th>
    </tr>
    {% for row in suspicious %}
    <tr>
        <td><a href="/{{ row['id'] }}/">{{ row['id'] }}</a></td>
        <td class="dollar-sign">$</td>
        <td class="amount">{{ row['balance'] }}</td>
        <td>
            {% for person in row['gives_to'] %}
                &rarr;<a href="/{{ person['id'] }}/"{% if person['is_suspicious'] %} class="suspicious"{% end %}>{{ person['id'] }}</a><br />
            {% end %}
            {% for person in row['receives_from'] %}
                &larr;<a href="/{{ person['id'] }}/"{% if person['is_suspicious'] %} class="suspicious"{% end %}>{{ person['id'] }}</a><br />
            {% end %}
        </td>
        <td>
            {% for transfer in row['transfers'] %}
                {% if row['id'] == transfer['tipper'] %}
                <span class="amount">{{ str(transfer['amount']).rjust(6).replace(' ', '&nbsp;') }}</span>&rarr;
                <a href="/{{ transfer['tippee'] }}/"{% if transfer['tippee'] in names %}
                    class="suspicious"{% end %}>{{ transfer['tippee'] }}</a><br />
                {% else %}
                <span class="amount">{{ str(transfer['amount']).rjust(6).replace(' ', '&nbsp;') }}</span>&larr;
                <a href="/{{ transfer['tipper'] }}/"{% if transfer['tipper'] in names %}
                    class="suspicious"{% end %}>{{ transfer['tipper'] }}</a><br />
                {% end %}
            {% end %}
        </td>
        <td>
            {% for exchange in row['exchanges'] %}
            <span class="amount{% if exchange['amount'] < 0 %} charge{% end %}">
                {{ str(exchange['amount']).rjust(7).replace(' ', '&nbsp;') }}
                {{ str(exchange['fee']).rjust(4).replace(' ', '&nbsp;') }}
            </span>
            <span class="date">
                {{ str(exchange['timestamp'])[:10] }}
            </span>
            <br />
            {% end %}
        </td>
    </tr>
    {% end %}
</table>
<br />
<hr /><commit_msg>Add summary info to fraud dashboard (#355)<commit_after>from aspen import Response
from gittip import db
from collections import defaultdict
from decimal import Decimal

^L
if not user.ADMIN:
    raise Response(404)


_suspicious = db.fetchall("""

    SELECT id
         , balance
      FROM participants
     WHERE is_suspicious
  ORDER BY ctime

""")
if _suspicious is None:
    _suspicious = []


def suspicious():
    for person in _suspicious:
        person['gives_to'] = db.fetchall("""

            SELECT DISTINCT tippee AS id
                 , is_suspicious
              FROM tips
              JOIN participants p
                ON tippee = p.id
             WHERE tipper=%s


        """, (person['id'],))
        person['receives_from'] = db.fetchall("""

            SELECT DISTINCT tipper AS id
                 , is_suspicious
              FROM tips
              JOIN participants p
                ON tipper = p.id
             WHERE tippee=%s

        """, (person['id'],))

        person['transfers'] = db.fetchall("""

            SELECT tippee
                 , tipper
                 , amount
              FROM transfers
             WHERE tipper=%s OR tippee=%s
          ORDER BY timestamp


        """, (person['id'], person['id']))

        person['exchanges'] = list(db.fetchall("""

            SELECT amount, fee, timestamp
              FROM exchanges
             WHERE participant_id=%s
          ORDER BY timestamp


        """, (person['id'],)))

        yield person
suspicious = list(suspicious())
names = set([person['id'] for person in suspicious])

total_charged = 0
total_withdrawn = 0
total_fees = 0
total_escrowed = 0
total_legit = 0

bystanders = defaultdict(lambda: [Decimal('0.00')])

for person in suspicious:
    total_escrowed += person['balance']
    for transfer in person['transfers']:
        if transfer['tippee'] not in names:
            total_legit += transfer['amount']
            bystanders[transfer['tippee']][0] += transfer['amount']
    for exchange in person['exchanges']:
        amount, fee = exchange['amount'], exchange['fee']
        total_fees += fee
        if amount < 0:
            total_withdrawn += abs(exchange['amount'])
        else:
            total_charged += exchange['amount'] + fee

assert total_legit == total_charged - total_withdrawn - total_fees - total_escrowed

pad = lambda d, n: str(d).rjust(n).replace(' ', '&nbsp;')

w = 7
total_charged = pad(total_charged, w)
total_withdrawn = pad(total_withdrawn, w)
total_fees = pad(total_fees, w)
total_escrowed = pad(total_escrowed, w)
total_legit = pad(total_legit, w)

bystander_balances = db.fetchall("""

    SELECT id, balance FROM participants WHERE id = ANY(%s)

""", (list(bystanders),))
if bystander_balances is None:
    bystander_balances = []
for row in bystander_balances:
    bystanders[row['id']].append(row['balance'])

bystanders = sorted(bystanders.items(), key=lambda i: i[1], reverse=True)

^L
<style>
    TABLE {
        width: auto;
    }
    TD, TH {
        text-align: left;
        vertical-align: top;
        border-bottom: 1px solid #B2A196;
    }
    .amount {
        text-align: right;
        padding-right: 6pt;
        font-family: Lucida Mono, monospace;
    }
    .date {
        font-family: Lucida Mono, monospace;
        font-size: smaller;
        color: #999;
    }
    .suspicious {
        font-weight: bold;
        color: red;
    }
    .charge {
        color: red;
    }
    TABLE {
        border-spacing: 0;
        border-collapse: collapse;
    }
    TD {
        padding: 1pt 3pt;
    }
</style>
<h1>Fraud, Grommit!</h1>
<table>
    <tr><th>Charged</th>    <td class="amount">${{ total_charged }}</td></tr>
    <tr><th>Withdrawn</th>  <td class="amount">${{ total_withdrawn }}</td></tr>
    <tr><th>Fees</th>       <td class="amount">${{ total_fees }}</td></tr>
    <tr><th>Escrowed</th>   <td class="amount">${{ total_escrowed }}</td></tr>
    <tr><th>Innocent
            Bystanders</th> <td class="amount">${{ total_legit }}</td></tr>
</table>

<h2>Innocent Bystanders (N = {{ len(bystanders) }})</h2>
<table>
    <tr><th></th><th>Stolen</th><th>Balance</th></tr>
    {% for name, (amount, balance) in bystanders %}
    <tr>
        <td><a href="/{{ name }}/">{{ name }}</a></td>
        <td class="amount">${{ pad(amount, 6) }}</td>
        <td class="amount">${{ pad(balance, 7) }}</td>
    </tr>
    {% end %}
</table>

<h2>Suspicious Participants</h2>
<table>
    <tr>
        <th>Suspicious Person</th>
        <th></th>
        <th>Balance</th>
        <th>Tip Graph</th>
        <th>Transfers</th>
        <th>Exchanges</th>
    </tr>
    {% for row in suspicious %}
    <tr>
        <td><a href="/{{ row['id'] }}/">{{ row['id'] }}</a></td>
        <td class="dollar-sign">$</td>
        <td class="amount">{{ row['balance'] }}</td>
        <td>
            {% for person in row['gives_to'] %}
                &rarr;<a href="/{{ person['id'] }}/"{% if person['is_suspicious'] %} class="suspicious"{% end %}>{{ person['id'] }}</a><br />
            {% end %}
            {% for person in row['receives_from'] %}
                &larr;<a href="/{{ person['id'] }}/"{% if person['is_suspicious'] %} class="suspicious"{% end %}>{{ person['id'] }}</a><br />
            {% end %}
        </td>
        <td>
            {% for transfer in row['transfers'] %}
                {% if row['id'] == transfer['tipper'] %}
                <span class="amount">{{ pad(transfer['amount'], 6)}}</span>&rarr;
                <a href="/{{ transfer['tippee'] }}/"{% if transfer['tippee'] in names %}
                    class="suspicious"{% end %}>{{ transfer['tippee'] }}</a><br />
                {% else %}
                <span class="amount">{{ pad(transfer['amount'], 6) }}</span>&larr;
                <a href="/{{ transfer['tipper'] }}/"{% if transfer['tipper'] in names %}
                    class="suspicious"{% end %}>{{ transfer['tipper'] }}</a><br />
                {% end %}
            {% end %}
        </td>
        <td>
            {% for exchange in row['exchanges'] %}
            <span class="amount{% if exchange['amount'] < 0 %} charge{% end %}">
                {{ pad(exchange['amount'], 7) }}
                {{ pad(exchange['fee'], 4) }}
            </span>
            <span class="date">
                {{ str(exchange['timestamp'])[:10] }}
            </span>
            <br />
            {% end %}
        </td>
    </tr>
    {% end %}
</table>
<br />
<hr />
{'fraction_non_alphanumeric': 0.17898978539968138, 'fraction_numerical': 0.006184987348889514, 'mean_word_length': 1.9102808835560403, 'pattern_counts': {'":': 0, '<': 180, '<?xml version=': 0, '>': 177, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '634703', 'n_tokens_mistral': 3620, 'n_tokens_neox': 3261, 'n_words': 771}