Skip to main content

max / makenotwork

8.5 KB · 208 lines History Blame Raw
1 //! Every aggregate here groups by `currency`.
2 //!
3 //! A creator's own sales are normally all in their own currency, so the common
4 //! result has one entry. Two show up when a creator's settlement currency
5 //! changed (old sales keep the currency they were written in) and on the
6 //! platform-wide roll-ups, which span every creator. Summing those into one
7 //! number would add pounds to dollars, so the type refuses to.
8
9 use super::{
10 Cents, ClaimToken, DbTransaction, DownloadToken, ItemId, PgPool, ProjectId, PromoCodeId,
11 Result, TransactionId, UserId,
12 };
13 use crate::currency::{MoneyByCurrency, SettlementCurrency};
14
15 /// Completed revenue and sales count for all items in a project.
16 ///
17 /// Only completed transactions count; pending, failed and refunded are excluded.
18 /// Revenue is grouped by currency: a project belongs to one creator, but if that
19 /// creator's settlement currency ever changed, their older sales are denominated
20 /// in the previous one and must not be added to the newer ones.
21 #[tracing::instrument(skip_all)]
22 pub async fn get_revenue_by_project(
23 pool: &PgPool,
24 project_id: ProjectId,
25 ) -> Result<(MoneyByCurrency, i64)> {
26 let rows = sqlx::query!(
27 r#"
28 SELECT
29 t.currency AS "currency!: SettlementCurrency",
30 COALESCE(SUM(t.amount_cents), 0)::BIGINT AS "total!",
31 COUNT(*) AS "count!"
32 FROM transactions t
33 JOIN items i ON t.item_id = i.id
34 WHERE i.project_id = $1
35 AND t.status = 'completed'
36 GROUP BY t.currency
37 "#,
38 project_id as ProjectId,
39 )
40 .fetch_all(pool)
41 .await?;
42
43 let sales = rows.iter().map(|r| r.count).sum();
44 let revenue = MoneyByCurrency::from_rows(rows.into_iter().map(|r| (r.currency, r.total)));
45 Ok((revenue, sales))
46 }
47
48 /// Revenue per project for a given seller, returned as (project_id, title, revenue_cents).
49 /// Single query replaces N+1 loop in dashboard analytics.
50 #[tracing::instrument(skip_all)]
51 pub async fn get_revenue_by_user_projects(
52 pool: &PgPool,
53 user_id: UserId,
54 ) -> Result<Vec<(ProjectId, String, MoneyByCurrency)>> {
55 // Grouped by (project, currency) and folded per project in Rust. Ordering
56 // stays on the project's largest single-currency total, because ranking
57 // projects against each other across currencies is the thing there is no
58 // honest rate for; within a project the currencies are then listed
59 // largest-first by `MoneyByCurrency`.
60 let rows = sqlx::query!(
61 r#"
62 SELECT p.id AS "id: ProjectId", p.title,
63 t.currency AS "currency!: SettlementCurrency",
64 COALESCE(SUM(t.amount_cents), 0)::BIGINT AS "revenue!"
65 FROM projects p
66 JOIN items i ON i.project_id = p.id
67 JOIN transactions t ON t.item_id = i.id AND t.status = 'completed'
68 WHERE p.user_id = $1
69 GROUP BY p.id, p.title, t.currency
70 HAVING COALESCE(SUM(t.amount_cents), 0) > 0
71 ORDER BY COALESCE(SUM(t.amount_cents), 0) DESC
72 "#,
73 user_id as UserId,
74 )
75 .fetch_all(pool)
76 .await?;
77
78 let mut out: Vec<(ProjectId, String, Vec<(SettlementCurrency, i64)>)> = Vec::new();
79 for r in rows {
80 match out.iter_mut().find(|(id, _, _)| *id == r.id) {
81 Some(entry) => entry.2.push((r.currency, r.revenue)),
82 None => out.push((r.id, r.title, vec![(r.currency, r.revenue)])),
83 }
84 }
85 Ok(out
86 .into_iter()
87 .map(|(id, title, totals)| (id, title, MoneyByCurrency::from_rows(totals)))
88 .collect())
89 }
90
91 /// Revenue and sales per project for a seller within a time range.
92 ///
93 /// Used for the cross-project comparison table on the user analytics tab.
94 #[tracing::instrument(skip_all)]
95 pub async fn get_revenue_by_user_projects_in_range(
96 pool: &PgPool,
97 user_id: UserId,
98 range: &crate::db::analytics::TimeRange,
99 ) -> Result<Vec<(ProjectId, String, MoneyByCurrency, i64)>> {
100 let time_filter = match range.interval_sql() {
101 Some(interval) => format!(" AND t.completed_at >= NOW() - INTERVAL '{interval}'"),
102 None => String::new(),
103 };
104
105 // The LEFT JOINs stay: this table lists every project the creator has,
106 // including ones with no sales in the range, so a zero row is meaningful
107 // here in a way it is not in the all-time list above. A project with no
108 // sales yields a NULL currency, which folds to an empty `MoneyByCurrency`.
109 let sql = format!(
110 r"
111 SELECT p.id, p.title, t.currency,
112 COALESCE(SUM(t.amount_cents), 0)::BIGINT,
113 COUNT(t.id)::BIGINT
114 FROM projects p
115 LEFT JOIN items i ON i.project_id = p.id
116 LEFT JOIN transactions t ON t.item_id = i.id AND t.status = 'completed'{time_filter}
117 WHERE p.user_id = $1
118 GROUP BY p.id, p.title, t.currency
119 ORDER BY COALESCE(SUM(t.amount_cents), 0) DESC
120 "
121 );
122
123 // runtime-checked: dynamically-built SQL string, the `time_filter` clause is
124 // conditionally interpolated via `format!`, so the query text isn't a literal
125 // and can't be compile-checked by the macro.
126 let rows: Vec<(ProjectId, String, Option<SettlementCurrency>, i64, i64)> =
127 sqlx::query_as(&sql).bind(user_id).fetch_all(pool).await?;
128
129 let mut out: Vec<(ProjectId, String, Vec<(SettlementCurrency, i64)>, i64)> = Vec::new();
130 for (id, title, currency, revenue, sales) in rows {
131 let totals = currency.map(|c| (c, revenue));
132 match out.iter_mut().find(|(pid, _, _, _)| *pid == id) {
133 Some(entry) => {
134 entry.2.extend(totals);
135 entry.3 += sales;
136 }
137 None => out.push((id, title, totals.into_iter().collect(), sales)),
138 }
139 }
140 Ok(out
141 .into_iter()
142 .map(|(id, title, totals, sales)| (id, title, MoneyByCurrency::from_rows(totals), sales))
143 .collect())
144 }
145
146 /// Platform-wide revenue stats: completed revenue per currency, completed
147 /// count, refunded count.
148 ///
149 /// This is the roll-up that spans every creator, so more than one currency is
150 /// the expected case rather than the edge one.
151 #[tracing::instrument(skip_all)]
152 pub async fn get_platform_revenue_stats(pool: &PgPool) -> Result<(MoneyByCurrency, i64, i64)> {
153 let rows = sqlx::query!(
154 r#"
155 SELECT
156 currency AS "currency!: SettlementCurrency",
157 COALESCE(SUM(CASE WHEN status = 'completed' THEN amount_cents ELSE 0 END), 0)::BIGINT AS "revenue!",
158 COALESCE(SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END), 0)::BIGINT AS "completed!",
159 COALESCE(SUM(CASE WHEN status = 'refunded' THEN 1 ELSE 0 END), 0)::BIGINT AS "refunded!"
160 FROM transactions
161 GROUP BY currency
162 "#,
163 )
164 .fetch_all(pool)
165 .await?;
166
167 // Counts are currency-free, so they still add up across the groups.
168 let completed = rows.iter().map(|r| r.completed).sum();
169 let refunded = rows.iter().map(|r| r.refunded).sum();
170 let revenue = MoneyByCurrency::from_rows(rows.into_iter().map(|r| (r.currency, r.revenue)));
171 Ok((revenue, completed, refunded))
172 }
173
174 /// Completed and refunded sales for a specific item, for the item dashboard Sales tab.
175 #[tracing::instrument(skip_all)]
176 pub async fn get_sales_by_item(
177 pool: &PgPool,
178 item_id: ItemId,
179 seller_id: UserId,
180 ) -> Result<Vec<DbTransaction>> {
181 let rows = sqlx::query_as!(
182 DbTransaction,
183 r#"
184 SELECT
185 id AS "id: TransactionId", buyer_id AS "buyer_id: UserId", seller_id AS "seller_id: UserId",
186 item_id AS "item_id: ItemId", amount_cents AS "amount_cents: Cents", platform_fee_cents AS "platform_fee_cents: Cents",
187 currency, status AS "status: crate::db::TransactionStatus", stripe_payment_intent_id, stripe_checkout_session_id,
188 created_at AS "created_at: chrono::DateTime<chrono::Utc>", completed_at AS "completed_at: chrono::DateTime<chrono::Utc>",
189 item_title, seller_username, share_contact, project_id AS "project_id: ProjectId",
190 parent_transaction_id AS "parent_transaction_id: TransactionId", promo_code_id AS "promo_code_id: PromoCodeId",
191 guest_email, claim_token AS "claim_token: ClaimToken", claimed_by AS "claimed_by: UserId",
192 download_token AS "download_token: DownloadToken",
193 presentment_amount_cents, presentment_currency
194 FROM transactions
195 WHERE item_id = $1 AND seller_id = $2
196 AND status IN ('completed', 'refunded')
197 ORDER BY created_at DESC
198 LIMIT 200
199 "#,
200 item_id as ItemId,
201 seller_id as UserId,
202 )
203 .fetch_all(pool)
204 .await?;
205
206 Ok(rows)
207 }
208