import { email } from "https://esm.town/v/std/email";
import { fetch } from "https://esm.town/v/std/fetch";
import process from "node:process";
function accumulateCountsFromRowResult(
counts,
row: {
count: number;
isVisitor: number;
isVisit: number;
},
) {
if (row.isVisit == 1) {
counts.visits += Number(row.count);
}
if (row.isVisitor == 1) {
counts.visitors += Number(row.count);
}
counts.views += Number(row.count);
}
export default async function(interval: Interval) {
const apiKey = process.env.counterscaleCfApiKey;
const accountId = process.env.counterscaleCfAccountId;
const url = `https://api.cloudflare.com/client/v4/accounts/${accountId}/analytics_engine/sql`;
const query = `
SELECT SUM(_sample_interval) as count,
double1 as isVisitor,
double2 as isVisit,
blob8 as site
FROM metricsDataset
WHERE timestamp > NOW() - INTERVAL '7' DAY
GROUP BY isVisitor, isVisit, site
ORDER BY isVisitor, isVisit ASC
`;
let result = await fetch(url, {
headers: {
"Authorization": `Bearer ${apiKey}`,
"Content-Type": "application/json",
},
method: "POST",
body: query,
});
let json = await result.json();
const counts = {};
json.data.forEach((row) => {
if (!counts.hasOwnProperty(row.site)) {
counts[row.site] = {
views: 0,
visitors: 0,
visits: 0,
};
}
accumulateCountsFromRowResult(counts[row.site], row);
});
var text = "Counterscale Weekly Summary\n"
+ "========================================\n\n";
for (var site in counts) {
if (site && counts.hasOwnProperty(site)) {
const siteCounts = counts[site];
text += `${site || "unknown"}\n`;
text += "---------------\n";
text += `Visitors: ${siteCounts.visitors}\n`;
text += `Views: ${siteCounts.views}\n`;
text += "\n\n";
}
}
await email({ subject: "Counterscale Weekly Summary", text });
console.log("Email sent!");
}