115 lines
8.2 KiB
JavaScript
115 lines
8.2 KiB
JavaScript
// Дашборд по заказам CRM (таблица crm_order в ClickHouse). Сервер на node:http без зависимостей.
|
||
// Источник данных: ClickHouse через платформу (CH_BASE_URL/CH_API_KEY появляются при run, если интеграция подключена).
|
||
// Если базы нет или запрос упал — демо-данные той же структуры, и в интерфейсе видна плашка «демо».
|
||
const http = require("http");
|
||
const fs = require("fs");
|
||
const path = require("path");
|
||
const PORT = process.env.PORT || 3000;
|
||
|
||
// ---------- запросы к crm_order (готовы к живой базе) ----------
|
||
const Q = {
|
||
kpi: (f, t) => `SELECT uniqExact(cust_order_id) AS orders,
|
||
uniqExactIf(cust_order_id, order_status_name IN ('Закрыт','Выполнен','Завершен','Завершён')) AS closed,
|
||
uniqExactIf(cust_order_id, po_reject_reason_name != '' OR order_cancel_reason_id > 0) AS rejected,
|
||
uniqExactIf(cust_order_id, install_done IS NOT NULL AND toString(install_done) NOT IN ('', '0')) AS installed
|
||
FROM crm_order WHERE toDate(order_create_date) BETWEEN '${f}' AND '${t}'`,
|
||
byMonth: (f, t) => `SELECT toStartOfMonth(toDate(order_create_date)) AS m, uniqExact(cust_order_id) AS orders
|
||
FROM crm_order WHERE toDate(order_create_date) BETWEEN '${f}' AND '${t}' GROUP BY m ORDER BY m`,
|
||
byFilial: (f, t) => `SELECT filial_name AS k, uniqExact(cust_order_id) AS v FROM crm_order
|
||
WHERE toDate(order_create_date) BETWEEN '${f}' AND '${t}' GROUP BY k ORDER BY v DESC LIMIT 12`,
|
||
byChannel: (f, t) => `SELECT sales_channel_name AS k, uniqExact(cust_order_id) AS v FROM crm_order
|
||
WHERE toDate(order_create_date) BETWEEN '${f}' AND '${t}' GROUP BY k ORDER BY v DESC LIMIT 8`,
|
||
funnel: (f, t) => `SELECT uniqExact(cust_order_id) AS created,
|
||
uniqExactIf(cust_order_id, toString(get_tv) NOT IN ('', '0')) AS tv_requested,
|
||
uniqExactIf(cust_order_id, toString(tv_defined) NOT IN ('', '0')) AS tv_defined,
|
||
uniqExactIf(cust_order_id, toString(sent_installation) NOT IN ('', '0')) AS sent_install,
|
||
uniqExactIf(cust_order_id, toString(install_done) NOT IN ('', '0')) AS installed,
|
||
uniqExactIf(cust_order_id, toString(process_done) NOT IN ('', '0')) AS done
|
||
FROM crm_order WHERE toDate(order_create_date) BETWEEN '${f}' AND '${t}'`,
|
||
topOffers: (f, t) => `SELECT new_product_offer_name AS k, uniqExact(cust_order_id) AS v FROM crm_order
|
||
WHERE toDate(order_create_date) BETWEEN '${f}' AND '${t}' AND new_product_offer_name != '' GROUP BY k ORDER BY v DESC LIMIT 10`,
|
||
rejects: (f, t) => `SELECT po_reject_reason_name AS k, uniqExact(cust_order_id) AS v FROM crm_order
|
||
WHERE toDate(order_create_date) BETWEEN '${f}' AND '${t}' AND po_reject_reason_name != '' GROUP BY k ORDER BY v DESC LIMIT 8`,
|
||
byTown: (f, t) => `SELECT town_name AS k, uniqExact(cust_order_id) AS v FROM crm_order
|
||
WHERE toDate(order_create_date) BETWEEN '${f}' AND '${t}' AND town_name != '' GROUP BY k ORDER BY v DESC LIMIT 10`,
|
||
};
|
||
|
||
async function ch(sql) {
|
||
if (!process.env.CH_BASE_URL || !process.env.CH_API_KEY) throw new Error("ClickHouse не подключён");
|
||
const r = await fetch(process.env.CH_BASE_URL + "/query", {
|
||
method: "POST", headers: { "Content-Type": "application/json", Authorization: "Bearer " + process.env.CH_API_KEY },
|
||
body: JSON.stringify({ sql, max_rows: 5000, timeout: 60 }),
|
||
});
|
||
const j = await r.json();
|
||
if (!j.ok) throw new Error(j.error || "ошибка ClickHouse");
|
||
return j.data || [];
|
||
}
|
||
|
||
// ---------- демо-данные той же структуры ----------
|
||
const FILIALS = ["Алматинская ОДТ", "Астанинская ОДТ", "Шымкентская ОДТ", "Карагандинская ОДТ", "Актюбинская ОДТ", "Павлодарская ОДТ"];
|
||
const CHANNELS = ["Контакт-центр", "Сайт / приложение", "Офис продаж", "Дилеры", "Прямые продажи B2B"];
|
||
const OFFERS = ["Интернет 500 Мбит/с + ТВ", "Интернет 200 Мбит/с", "Пакет «Всё включено»", "TV+ базовый", "Бизнес-интернет 1 Гбит/с", "Домашний телефон + интернет"];
|
||
const TOWNS = ["Алматы", "Астана", "Шымкент", "Караганда", "Актобе", "Павлодар", "Тараз", "Усть-Каменогорск"];
|
||
const REJECTS = ["Нет техвозможности", "Передумал клиент", "Дорого", "Ушёл к конкуренту", "Дубликат заказа", "Не вышли на связь"];
|
||
function rng(seed) { let s = seed >>> 0; return () => ((s = (s * 1664525 + 1013904223) >>> 0) / 4294967296); }
|
||
function demoRows() {
|
||
const r = rng(42); const rows = []; const today = new Date(); today.setHours(0, 0, 0, 0);
|
||
for (let i = 0; i < 6000; i++) {
|
||
const d = new Date(today); d.setDate(d.getDate() - Math.floor(r() * 180));
|
||
const stage = r();
|
||
rows.push({ id: 100000 + i, date: d, filial: FILIALS[Math.floor(r() * r() * FILIALS.length)], channel: CHANNELS[Math.floor(r() * r() * CHANNELS.length)],
|
||
offer: OFFERS[Math.floor(r() * OFFERS.length)], town: TOWNS[Math.floor(r() * r() * TOWNS.length)],
|
||
tv: stage > 0.08, tvdef: stage > 0.18, sent: stage > 0.3, inst: stage > 0.42, done: stage > 0.5,
|
||
reject: stage < 0.15 ? REJECTS[Math.floor(r() * REJECTS.length)] : "" });
|
||
}
|
||
return rows;
|
||
}
|
||
const DEMO = demoRows();
|
||
const iso = (d) => d.toISOString().slice(0, 10);
|
||
function demo(f, t) {
|
||
const rows = DEMO.filter((x) => { const s = iso(x.date); return s >= f && s <= t; });
|
||
const cnt = (key) => { const m = {}; rows.forEach((x) => { const k = x[key]; if (k) m[k] = (m[k] || 0) + 1; }); return Object.entries(m).sort((a, b) => b[1] - a[1]).map(([k, v]) => [k, v]); };
|
||
const months = {}; rows.forEach((x) => { const k = iso(x.date).slice(0, 7) + "-01"; months[k] = (months[k] || 0) + 1; });
|
||
return {
|
||
kpi: [[rows.length, rows.filter((x) => x.done).length, rows.filter((x) => x.reject).length, rows.filter((x) => x.inst).length]],
|
||
byMonth: Object.entries(months).sort().map(([m, v]) => [m, v]),
|
||
byFilial: cnt("filial"), byChannel: cnt("channel"), topOffers: cnt("offer"), rejects: cnt("reject"), byTown: cnt("town"),
|
||
funnel: [[rows.length, rows.filter((x) => x.tv).length, rows.filter((x) => x.tvdef).length, rows.filter((x) => x.sent).length, rows.filter((x) => x.inst).length, rows.filter((x) => x.done).length]],
|
||
};
|
||
}
|
||
|
||
const cache = new Map();
|
||
async function summary(f, t) {
|
||
const key = f + ":" + t; const hit = cache.get(key);
|
||
if (hit && Date.now() - hit.at < 120000) return hit.v;
|
||
let v;
|
||
try {
|
||
const out = {};
|
||
for (const [k, mk] of Object.entries(Q)) out[k] = await ch(mk(f, t));
|
||
v = { source: "clickhouse", database: process.env.CH_DATABASE || "orders", ...out };
|
||
} catch (e) {
|
||
v = { source: "demo", reason: String(e.message || e), ...demo(f, t) };
|
||
}
|
||
cache.set(key, { v, at: Date.now() });
|
||
return v;
|
||
}
|
||
|
||
const MIME = { ".html": "text/html; charset=utf-8", ".css": "text/css", ".js": "application/javascript", ".svg": "image/svg+xml", ".png": "image/png", ".woff2": "font/woff2", ".woff": "font/woff", ".json": "application/json" };
|
||
http.createServer(async (req, res) => {
|
||
const u = new URL(req.url, "http://x");
|
||
if (u.pathname === "/api/summary") {
|
||
const t = u.searchParams.get("to") || iso(new Date());
|
||
const f = u.searchParams.get("from") || iso(new Date(Date.now() - 89 * 864e5));
|
||
try { const v = await summary(f, t); res.setHeader("Content-Type", "application/json"); res.end(JSON.stringify({ ok: true, from: f, to: t, ...v })); }
|
||
catch (e) { res.statusCode = 500; res.end(JSON.stringify({ ok: false, error: String(e) })); }
|
||
return;
|
||
}
|
||
let p = u.pathname === "/" ? "/index.html" : u.pathname;
|
||
const full = path.join(__dirname, path.normalize(p));
|
||
if (full.startsWith(__dirname) && fs.existsSync(full) && fs.statSync(full).isFile()) {
|
||
res.setHeader("Content-Type", MIME[path.extname(full)] || "application/octet-stream");
|
||
return fs.createReadStream(full).pipe(res);
|
||
}
|
||
res.statusCode = 404; res.end("not found");
|
||
}).listen(PORT, () => console.log("dashboard on " + PORT));
|