~fnux/meta.sr.ht

meta.sr.ht/metasrht-invoicestats -rwxr-xr-x 3.1 KiB
28f5dba0Timothée Floure auth: remove duplicated password reset log for LDAP backend 5 months ago
                                                                                
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
#!/usr/bin/env python3
from metasrht.app import db
from sqlalchemy import text
from srht.config import cfg
from srht.email import send_email
from datetime import datetime, timedelta

def summary_between(mindate, maxdate):
    sql = """
    SELECT
        "user".payment_cents,
        "user".payment_interval,
        count(invoice.*),
        sum(invoice.cents)
    FROM invoice
    JOIN "user" ON invoice.user_id = "user".id
    WHERE ((
        "user".payment_interval = 'monthly'
        AND
        invoice.valid_thru >= :monthly_min
        AND
        invoice.valid_thru < :monthly_max
    ) OR (
        "user".payment_interval = 'yearly'
        AND
        invoice.valid_thru >= :yearly_min
        AND
        invoice.valid_thru < :yearly_max
    ))
    AND (SELECT count(*) FROM "invoice" WHERE "invoice".user_id = "user".id) = 1
    GROUP BY "user".payment_cents, "user".payment_interval;
    """
    results = db.session.execute(sql, {
        "monthly_min": mindate + timedelta(days=30),
        "monthly_max": maxdate + timedelta(days=30),
        "yearly_min": mindate + timedelta(days=365),
        "yearly_max": maxdate + timedelta(days=365),
    }).fetchall()
    results = sorted(results, key=lambda tup: tup[0])
    def sub_fees(cents):
        return cents - int(cents * 0.029) - 30
    total = sub_fees(sum(tup[3]//100 for tup in results))
    monthly_total = sub_fees(sum(tup[3]//100 for tup in results
        if tup[1] == "monthly"))
    return "\n".join([
        f"{'$'+str(tup[0]//100):>4} {tup[1]:<7} " +
            f"{tup[2]:>3} users (+${tup[3]//100})" +
            f"{' (cancelled)' if tup[0] == 0 else ''}" for tup in results
        ]) + f"\n      total after fees: +${total:,.2f} (${monthly_total:,.2f}/mo)"

week = summary_between(datetime.utcnow() + timedelta(days=-7), datetime.utcnow())
last_week = summary_between(datetime.utcnow() + timedelta(days=-14),
        datetime.utcnow() + timedelta(days=-7))

month = summary_between(datetime.utcnow() + timedelta(days=-30), datetime.utcnow())
last_month = summary_between(datetime.utcnow() + timedelta(days=-60),
        datetime.utcnow() + timedelta(days=-30))

def active_paying():
    sql = """
    SELECT
        payment_cents,
        payment_interval,
        count(*) users
    FROM "user" WHERE user_type = 'active_paying' AND payment_cents != 0
    GROUP BY payment_interval, payment_cents
    ORDER BY payment_cents;
    """
    results = db.session.execute(sql).fetchall()
    return "\n".join([
        f"{'$'+str(tup[0]//100):>4} {tup[1]:<7} {tup[2]:>4} users " +
            f"(${tup[0]*tup[2]//100:.2f}/mo before fees)"
        for tup in results
    ])

paid_accounts = active_paying()

summary = f"""Summary of new paid accounts

                                   Last 7 days

{week}

                                   Last week

{last_week}

                                  Last 30 days

{month}

                                   Last month

{last_month}

Summary of active paid accounts

{paid_accounts}
"""

owner_name = cfg("sr.ht", "owner-name")
owner_email = cfg("sr.ht", "owner-email")
send_email(summary, owner_email, "sr.ht financials report",
        To=f"{owner_name} <{owner_email}>")