-
-
Notifications
You must be signed in to change notification settings - Fork 260
/
host-settlement.ts
240 lines (208 loc) · 7.91 KB
/
host-settlement.ts
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
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
#!/usr/bin/env node
import '../../server/env';
import config from 'config';
import { parse as json2csv } from 'json2csv';
import { groupBy, pick, sumBy } from 'lodash';
import moment from 'moment';
import { v4 as uuid } from 'uuid';
import activityType from '../../server/constants/activities';
import expenseStatus from '../../server/constants/expense_status';
import expenseTypes from '../../server/constants/expense_type';
import { SETTLEMENT_EXPENSE_PROPERTIES } from '../../server/constants/transactions';
import { uploadToS3 } from '../../server/lib/awsS3';
import { getPendingHostFeeShare, getPendingPlatformTips } from '../../server/lib/host-metrics';
import { parseToBoolean } from '../../server/lib/utils';
import models, { sequelize } from '../../server/models';
import { PayoutMethodTypes } from '../../server/models/PayoutMethod';
const today = moment.utc();
const defaultDate = process.env.START_DATE ? moment.utc(process.env.START_DATE) : moment.utc();
const DRY = process.env.DRY;
const HOST_ID = process.env.HOST_ID;
const isProduction = config.env === 'production';
// Only run on the 1th of the month
if (isProduction && new Date().getDate() !== 1 && !process.env.OFFCYCLE) {
console.log('OC_ENV is production and today is not the 1st of month, script aborted!');
process.exit();
} else if (parseToBoolean(process.env.SKIP_HOST_SETTLEMENT)) {
console.log('Skipping because SKIP_HOST_SETTLEMENT is set.');
process.exit();
}
if (DRY) {
console.info('Running dry, changes are not going to be persisted to the DB.');
}
const ATTACHED_CSV_COLUMNS = ['createdAt', 'description', 'amount', 'currency', 'OrderId', 'TransactionGroup'];
export async function run(baseDate: Date | moment.Moment = defaultDate): Promise<void> {
const momentDate = moment(baseDate).subtract(1, 'month');
const year = momentDate.year();
const month = momentDate.month();
const startDate = new Date(year, month, 1);
const endDate = new Date(year, month + 1, 1);
console.info(`Invoicing hosts pending fees and tips for ${momentDate.format('MMMM')}.`);
const payoutMethods = groupBy(
await models.PayoutMethod.findAll({
where: { CollectiveId: SETTLEMENT_EXPENSE_PROPERTIES.FromCollectiveId, isSaved: true },
}),
'type',
);
const hosts = await sequelize.query(
`
SELECT c.*
FROM "Collectives" c
INNER JOIN "Transactions" t ON t."HostCollectiveId" = c.id AND t."deletedAt" IS NULL
WHERE c."isHostAccount" IS TRUE
AND t."createdAt" >= :startDate AND t."createdAt" < :endDate
AND c.id != 8686 -- Make sure we don't invoice OC Inc as reverse settlements are not supported yet
GROUP BY c.id
`,
{
mapToModel: true,
type: sequelize.QueryTypes.SELECT,
model: models.Collective,
replacements: { startDate: startDate, endDate: endDate },
},
);
for (const host of hosts) {
const pendingPlatformTips = await getPendingPlatformTips(host, { startDate, endDate });
const pendingHostFeeShare = await getPendingHostFeeShare(host, { startDate, endDate });
if (HOST_ID && host.id !== parseInt(HOST_ID)) {
continue;
}
const plan = await host.getPlan();
let items = [];
const transactions = await sequelize.query(
`SELECT t.*
FROM "Transactions" as t
INNER JOIN "TransactionSettlements" ts ON ts."TransactionGroup" = t."TransactionGroup" AND t.kind = ts.kind
WHERE t."CollectiveId" = :CollectiveId
AND t."createdAt" >= :startDate AND t."createdAt" < :endDate
AND t."kind" IN ('PLATFORM_TIP_DEBT', 'HOST_FEE_SHARE_DEBT')
AND t."isDebt" IS TRUE
AND t."deletedAt" IS NULL
AND ts."status" != 'SETTLED'`,
{
replacements: { CollectiveId: host.id, startDate: startDate, endDate: endDate },
model: models.Transaction,
mapToModel: true, // pass true here if you have any mapped fields
},
);
items.push({
incurredAt: new Date(),
amount: pendingPlatformTips,
description: 'Platform Tips',
});
items.push({
incurredAt: new Date(),
amount: pendingHostFeeShare,
description: 'Shared Revenue',
});
if (plan.pricePerCollective) {
const activeHostedCollectives = await host.getHostedCollectivesCount();
const amount = (activeHostedCollectives || 0) * plan.pricePerCollective;
if (amount) {
items.push({
incurredAt: new Date(),
amount,
description: 'Fixed Fee per Hosted Collective',
});
}
}
const totalAmountCharged = sumBy(items, 'amount');
if (totalAmountCharged < 1000) {
console.warn(
`${host.name} (#${host.id}) skipped, total amount pending ${totalAmountCharged / 100} < 10.00 ${
host.currency
}.\n`,
);
continue;
}
console.info(
`${host.name} (#${host.id}) has ${transactions.length} pending transactions and owes ${
totalAmountCharged / 100
} (${host.currency})`,
);
let csv;
if (transactions.length) {
csv = json2csv(transactions.map(t => pick(t, ATTACHED_CSV_COLUMNS)));
}
if (DRY) {
console.debug(`Items:\n${json2csv(items)}\n`);
} else {
const connectedAccounts = await host.getConnectedAccounts({
where: { deletedAt: null },
});
let payoutMethod =
payoutMethods[PayoutMethodTypes.OTHER]?.[0] || payoutMethods[PayoutMethodTypes.BANK_ACCOUNT]?.[0];
if (
connectedAccounts?.find(c => c.service === 'transferwise') &&
payoutMethods[PayoutMethodTypes.BANK_ACCOUNT]?.[0]
) {
const currencyCompatibleAccount = payoutMethods[PayoutMethodTypes.BANK_ACCOUNT].find(
pm => pm.data?.['currency'] === host.currency,
);
payoutMethod = currencyCompatibleAccount || payoutMethods[PayoutMethodTypes.BANK_ACCOUNT]?.[0];
} else if (
connectedAccounts?.find(c => c.service === 'paypal') &&
!host.settings?.disablePaypalPayouts &&
payoutMethods[PayoutMethodTypes.PAYPAL]?.[0]
) {
payoutMethod = payoutMethods[PayoutMethodTypes.PAYPAL]?.[0];
}
if (!payoutMethod) {
console.error('No Payout Method found, Open Collective Inc. needs to have at least one payout method.');
process.exit();
}
// Create the Expense
const transactionIds = transactions.map(t => t.TransactionId);
const expense = await models.Expense.create({
...SETTLEMENT_EXPENSE_PROPERTIES,
PayoutMethodId: payoutMethod.id,
amount: totalAmountCharged,
CollectiveId: host.id,
currency: host.currency,
description: `Platform settlement for ${momentDate.utc().format('MMMM')}`,
incurredAt: today,
data: { isPlatformTipSettlement: true, transactionIds },
type: expenseTypes.INVOICE,
status: expenseStatus.PENDING,
});
// Create Expense Items
items = items.map(i => ({
...i,
ExpenseId: expense.id,
CreatedByUserId: SETTLEMENT_EXPENSE_PROPERTIES.UserId,
}));
await models.ExpenseItem.bulkCreate(items);
// Attach CSV
if (csv) {
const Body = csv;
const filenameBase = `${host.name}-${momentDate.format('MMMM-YYYY')}`;
const Key = `${filenameBase}.${uuid().split('-')[0]}.csv`;
const { Location: url } = await uploadToS3({
Bucket: config.aws.s3.bucket,
Key,
Body,
ACL: 'public-read',
ContentType: 'text/csv',
});
await models.ExpenseAttachedFile.create({
url,
ExpenseId: expense.id,
CreatedByUserId: SETTLEMENT_EXPENSE_PROPERTIES.UserId,
});
}
// Mark transactions as invoiced
await models.TransactionSettlement.markTransactionsAsInvoiced(transactions, expense.id);
await expense.createActivity(activityType.COLLECTIVE_EXPENSE_CREATED);
}
}
}
if (require.main === module) {
run(defaultDate)
.catch(e => {
console.error(e);
process.exit(1);
})
.then(() => {
process.exit();
});
}