· 4 years ago · Sep 06, 2021, 07:10 PM
1const fs = require("fs");
2const sqlite3 = require("sqlite3").verbose();
3
4const db = new sqlite3.Database("./db");
5const mints = JSON.parse(fs.readFileSync("./mints_1.json"));
6
7async function getAllTransactions() {
8 const select = `
9 SELECT *
10 FROM transactions
11 WHERE slot > 93982093
12 ORDER BY slot;
13 `;
14 return new Promise((resolve, reject) => {
15 db.all(select, (err, results) => {
16 if (err) {
17 reject(err);
18 return;
19 }
20 const parsedJson = results
21 .map((row) => {
22 row["txn"] = JSON.parse(row["txn"]);
23 row["meta"] = JSON.parse(row["meta"]);
24 return row;
25 })
26 .filter((row) => !hasError(row["meta"]));
27 resolve(parsedJson);
28 });
29 });
30}
31
32function getInstructions(txn) {
33 return txn["message"]["instructions"];
34}
35
36function isTransferTxn(instructions) {
37 return (
38 instructions.length === 1 &&
39 instructions[0]["program"] === "system" &&
40 instructions[0]["parsed"]["type"] === "transfer"
41 );
42}
43
44function isTransferToSeller(instructions) {
45 return (
46 instructions[0]["parsed"]["info"]["destination"] ===
47 "8749adNqCXzVjdYVCUFcjUUxsPcHuCW482roGqsxtMRX"
48 );
49}
50
51function isTransferFromSeller(instructions) {
52 return (
53 instructions[0]["parsed"]["info"]["source"] ===
54 "8749adNqCXzVjdYVCUFcjUUxsPcHuCW482roGqsxtMRX"
55 );
56}
57
58function sourceInfo(instructions) {
59 // returns [sender, unitsInLamports]
60 const info = instructions[0]["parsed"]["info"];
61 const sender = info["source"];
62 const units = info["lamports"];
63 return [sender, units];
64}
65
66function destInfo(instructions) {
67 // returns [dest, unitsInLamports]
68 const info = instructions[0]["parsed"]["info"];
69 const dest = info["destination"];
70 const units = info["lamports"];
71 return [dest, units];
72}
73
74function isBearTransfer(txn) {
75 const instructions = txn["message"]["instructions"];
76 const accountKeys = txn["message"]["accountKeys"];
77 return (
78 instructions.length === 2 &&
79 instructions[0]["parsed"]["type"] === "createAccount" &&
80 instructions[1]["parsed"]["type"] === "initializeAccount" &&
81 accountKeys.length > 0 &&
82 accountKeys[0]["signer"] &&
83 accountKeys[0]["pubkey"] ===
84 "8749adNqCXzVjdYVCUFcjUUxsPcHuCW482roGqsxtMRX" &&
85 instructions[0]["parsed"]["info"]["source"] ===
86 "8749adNqCXzVjdYVCUFcjUUxsPcHuCW482roGqsxtMRX" &&
87 isMint(instructions[1]["parsed"]["info"]["mint"])
88 );
89}
90
91function hasError(meta) {
92 return !!meta["err"];
93}
94
95function isMint(mintAddress) {
96 return mints.includes(mintAddress);
97}
98
99async function getAllTransactionsForAddress(address) {
100 const select = `
101 SELECT *
102 FROM transactions
103 WHERE txn LIKE '%${address}%'
104 ORDER BY slot ASC;
105 `;
106 return new Promise((resolve, reject) => {
107 db.all(select, (err, results) => {
108 if (err) {
109 reject(err);
110 return;
111 }
112 const successTxns = results.filter((row) => {
113 const meta = JSON.parse(row["meta"]);
114 return !hasError(meta);
115 });
116 resolve(successTxns.map((row) => JSON.parse(row["txn"])));
117 });
118 });
119}
120
121async function generateHTMLReport(remainingRefunded) {
122 let html = "";
123 const explorerURI = (tx) => `https://explorer.solana.com/tx/${tx}`;
124 const addresses = Object.keys(remainingRefunded);
125 for (let i = 0; i < addresses.length; i++) {
126 try {
127 const txns = await getAllTransactionsForAddress(addresses[i]);
128 html += "<ul>";
129 html += `<li> ${i + 1}. ${addresses[i]}. Remaining: ${
130 remainingRefunded[addresses[i]] / 1e9
131 } SOL </li>`;
132 const sentTransactions = txns
133 .filter((txn) => {
134 const ins = getInstructions(txn);
135 return isTransferTxn(ins) && isTransferToSeller(ins);
136 })
137 .map((txn) => {
138 const [sender, lamports] = sourceInfo(getInstructions(txn));
139 html += `<li>Sent ${
140 lamports / 1e9
141 } SOL. <a target="_blank" href="${explorerURI(
142 txn["signatures"][0]
143 )}">Tx</a></li>`;
144 });
145 const refundTransactions = txns
146 .filter((txn) => {
147 const ins = getInstructions(txn);
148 return isTransferTxn(ins) && isTransferFromSeller(ins);
149 })
150 .map((txn) => {
151 const [dest, lamports] = destInfo(getInstructions(txn));
152 html += `<li>Refunded ${
153 lamports / 1e9
154 } SOL. <a target="_blank" href="${explorerURI(
155 txn["signatures"][0]
156 )}">Tx</a></li>`;
157 });
158 const bearTransactions = txns.filter(isBearTransfer).map((txn) => {
159 const [dest, lamports] = destInfo(getInstructions(txn));
160 html += `<li>Sent 1 Bear. `;
161 txn["signatures"].map((sig) => {
162 html += `<a target="_blank" href="${explorerURI(sig)}">Tx</a> `;
163 });
164 html += `</li>`;
165 });
166 html += "</ul><br/><br />";
167 } catch (e) {
168 console.log(e);
169 console.log("failed for address, ", addresses[i]);
170 }
171 }
172 fs.writeFileSync("refunds.html", html, "utf-8");
173}
174
175(async () => {
176 const txn_results = await getAllTransactions();
177
178 const txns = txn_results.map((row) => row["txn"]);
179
180 const sentTransactions = txns.filter((txn) => {
181 const ins = getInstructions(txn);
182 return isTransferTxn(ins) && isTransferToSeller(ins);
183 });
184
185 const refundTransactions = txns.filter((txn) => {
186 const ins = getInstructions(txn);
187 return isTransferTxn(ins) && isTransferFromSeller(ins);
188 });
189
190 const bearTransactions = txns.filter(isBearTransfer);
191
192 const sentToSeller = sentTransactions
193 .map(getInstructions)
194 .reduce((accum, instructions) => {
195 const [sender, lamports] = sourceInfo(instructions);
196 const prev = accum[sender] || 0;
197 return {
198 ...accum,
199 [sender]: prev + lamports,
200 };
201 }, {});
202
203 // check refunded
204 const refundedFromSeller = refundTransactions
205 .map(getInstructions)
206 .reduce((accum, instructions) => {
207 const [dest, lamports] = destInfo(instructions);
208 const prev = accum[dest] || 0;
209 return {
210 ...accum,
211 [dest]: prev + lamports,
212 };
213 });
214
215 const bearCost = 3e9;
216
217 const bearsSent = bearTransactions
218 .map(getInstructions)
219 .reduce((accum, instructions) => {
220 const info = instructions[1]["parsed"]["info"];
221 const dest = info["owner"];
222 const prev = accum[dest] || 0;
223 return {
224 ...accum,
225 [dest]: prev + bearCost,
226 };
227 }, {});
228
229 // push these results to db, so we can explore later
230 db.serialize(() => {
231 db.run("DROP TABLE IF EXISTS refunds");
232
233 db.run(
234 "CREATE TABLE IF NOT EXISTS refunds (sig TEXT, address TEXT, amount INTEGER, type TEXT)"
235 );
236
237 const insert = db.prepare(
238 "INSERT INTO refunds VALUES ($sig, $address, $amount, $type)"
239 );
240 sentTransactions.map((txn) => {
241 const instructions = getInstructions(txn);
242 const [sender, lamports] = sourceInfo(instructions);
243 insert.run({
244 $sig: txn["signatures"][0],
245 $address: sender,
246 $amount: lamports,
247 $type: "send",
248 });
249 });
250 sentTransactions.map((txn) => {
251 const instructions = getInstructions(txn);
252 const [dest, lamports] = sourceInfo(instructions);
253 insert.run({
254 $sig: txn["signatures"][0],
255 $address: dest,
256 $amount: lamports,
257 $type: "refund",
258 });
259 });
260 bearTransactions.map((txn) => {
261 const instructions = getInstructions(txn);
262 const info = instructions[1]["parsed"]["info"];
263 const dest = info["owner"];
264 insert.run({
265 $sig: txn["signatures"].join(","),
266 $address: dest,
267 $amount: bearCost,
268 $type: "bear",
269 });
270 });
271 insert.finalize();
272 });
273
274 // check for remaining refunds
275 const remainingRefund = {};
276
277 const bearBuyers = Object.keys(sentToSeller);
278 for (let i = 0; i < bearBuyers.length; i++) {
279 const buyer = bearBuyers[i];
280 const buyerPaid = sentToSeller[buyer];
281 const buyerRefunded = refundedFromSeller[buyer] || 0;
282 const bearsEqvSent = bearsSent[buyer] || 0;
283 const remaining = buyerPaid - buyerRefunded - bearsEqvSent;
284 if (remaining > 0) {
285 remainingRefund[buyer] = remaining;
286 }
287 }
288
289 fs.writeFileSync("refunds.json", JSON.stringify(remainingRefund), "utf-8");
290
291 await generateHTMLReport(remainingRefund);
292
293 const totalRefundLeft = Object.values(remainingRefund).reduce(
294 (a, b) => a + b,
295 0
296 );
297
298 console.log(
299 "Buyers left to be refunded: ",
300 Object.keys(remainingRefund).length
301 );
302 console.log("Amount left to be refunded: ", totalRefundLeft);
303
304 db.close();
305})();
306