U.fin — финансы агентства Unlock

[{"_1":2,"_3":-5,"_4":-5},"loaderData",{"_5":6,"_7":8,"_9":10},"actionData","errors","root",{"_179":180,"_181":182},"routes/_app.x.$handle.$name",{"_104":103,"_32":49,"_113":94,"_27":114,"_13":14,"_115":-5,"_11":116,"_117":118,"_119":120,"_121":122,"_123":124,"_21":-5,"_125":-5,"_126":-5,"_127":25,"_128":129,"_130":63,"_131":-5,"_132":-5,"_133":134},"routes/_app.x.$handle.$name.branch.$branch.version.$version.diff",{"_11":12,"_13":14,"_15":16,"_17":-5,"_18":-5},"project",{"_47":48,"_32":49,"_36":50,"_51":-5,"_52":25,"_53":54,"_55":54,"_56":57,"_58":-5,"_18":59,"_60":61,"_62":63,"_64":-5,"_65":-5,"_66":67,"_68":69,"_70":71,"_72":73,"_74":75,"_76":-5,"_77":78,"_79":78,"_80":81,"_82":81,"_83":81,"_84":78,"_85":78,"_86":87,"_88":89,"_90":91,"_92":25},"version",2,"changes",[19,20],"aiChatInfo","userId",{"_21":41,"_23":-7,"_24":25,"_26":25,"_27":42,"_29":42},{"_21":22,"_23":-7,"_24":25,"_26":25,"_27":28,"_29":28},"file",{"_30":31,"_32":28,"_33":34,"_35":-5,"_36":37,"_38":14,"_39":40,"_24":25},"previous","deleted",false,"deletedHere","path","frontend","latestPath","fileId","d6425b9f-9139-4189-8273-fccd35c26d0e","name","fileType","directory","content","createdAt",["D",1787314822204],"branchVersion","branchId","b8e0918e-f674-49d7-9108-412c36e6cc77",{"_30":43,"_32":44,"_33":21,"_35":45,"_36":46,"_38":14,"_39":40,"_24":25},"frontend/index.html","ae509a26-9d5a-11f1-9d07-1607ee4eb77e","index.html","\n\n\n \n \n U.fin — финансы агентства Unlock\n \n \n \n \n\n\n

\n \n\n\n",["D",1787314822204],"id","d38ab4c8-7c2c-436f-9fef-9b1d67be3d19","ufin2",["D",1787314298248],"deletedAt","legacyEnvVars","privacy","public","httpPrivacy","description","U.fin v2","imageUrl","caa5b6a0-dd4e-4cd6-a997-9389a6a43948","pinnedFileId","aded32d8-9d5a-11f1-8da2-1607ee4eb77e","pinPreview",true,"pinMigrated","screenshotVersion","likeCount",0,"orgId","019eff71-5749-76b5-abdd-e25a47b3deca","tursoName","d38ab4c87c2c436f9fef9b1d67be3d19","tursoHostname","d38ab4c87c2c436f9fef9b1d67be3d19-val-town.aws-us-east-2.turso.io","tursoMeasuredAt",["D",1789484509295],"pinned","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm","user",{"_104":103,"_112":108},"org",{"_104":103,"_105":106,"_107":108,"_18":59,"_109":110,"_111":25},"branch",{"_13":93,"_32":94,"_47":40,"_95":48,"_96":-5,"_97":-5,"_98":-5,"_99":-5,"_100":-5,"_101":49,"_102":103},"isOwned",13,"main","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","tambovtsev007","handle","displayName","Кирилл Тамбовцев","avatar","https://lh3.googleusercontent.com/a/ACg8ocKCNneTjcn307kcxhB_NS1R7_0SBhSKM-2hy73z0JvhXFipwkhs=s96-c","tier","free","canRestrictVals","profileImageUrl","branchName","","adoptOrgId",{"_47":48,"_32":49,"_36":174,"_51":-5,"_52":25,"_53":54,"_55":54,"_56":57,"_58":-5,"_18":59,"_60":61,"_62":63,"_64":-5,"_65":-5,"_66":67,"_68":69,"_70":71,"_72":73,"_74":175,"_76":-5,"_77":78,"_79":78,"_80":81,"_82":81,"_83":81,"_84":78,"_85":78,"_86":176,"_88":177,"_90":178,"_92":25},"tree",[160,162],"files",[160,161,162],"counts",{"_150":67,"_151":140,"_152":67,"_153":154,"_155":156},"contents",[135,136],"upstreamProject","upstreamBranch","emptyEnvVars","flags",[],"hasHttpFiles","grants","orgs","customDomains",{},{"_47":147,"_32":28,"_30":31,"_139":-5,"_39":40,"_38":14,"_141":63,"_36":148,"_13":67,"_33":34,"_35":-5,"_24":25,"_145":-5,"_27":149},{"_47":137,"_32":138,"_30":61,"_139":-5,"_39":40,"_38":140,"_141":25,"_36":142,"_13":67,"_33":143,"_35":144,"_24":25,"_145":-5,"_27":146},"d4a1a576-f9bb-49c7-a445-8f67da5e076d","index.ts","parentId",1,"latest",["D",1787314821565],"http","// U.fin v2 — финансовая система агентства недвижимости Unlock.\n// Спроектирована с нуля под логику агентства: сделки с задатками, брокеры,\n// застройщики, план месяц/год. Финансовое ядро: счета, операции, статьи с классами.\n// Backend: Hono + SQLite (val-scoped). Вход по PIN.\nimport { Hono } from \"npm:hono\";\nimport { sqlite } from \"https://esm.town/v/std/sqlite/main.ts\";\nimport { readFile, serveFile } from \"https://esm.town/v/std/utils/index.ts\";\n\nconst app = new Hono();\napp.onError((err) => Promise.reject(err));\n\nconst today = () => new Date().toISOString().slice(0, 10);\nconst num = (v: unknown) => (v === null || v === undefined || v === \"\" ? 0 : Number(v));\nconst rows = (r: any) => r.rows as any[][];\n\n// ---------------- Схема ----------------\nawait sqlite.batch([\n `CREATE TABLE IF NOT EXISTS uf_settings (key TEXT PRIMARY KEY, value TEXT)`,\n `CREATE TABLE IF NOT EXISTS uf_accounts (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n name TEXT NOT NULL,\n acc_type TEXT DEFAULT 'Безналичный',\n bank TEXT DEFAULT '',\n grp TEXT DEFAULT '',\n start_balance REAL DEFAULT 0,\n start_date TEXT,\n active INTEGER DEFAULT 1\n )`,\n `CREATE TABLE IF NOT EXISTS uf_categories (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n name TEXT NOT NULL,\n kind TEXT NOT NULL, -- income | expense\n cls TEXT DEFAULT 'pl', -- pl | assets | liabilities | capital (не-pl не попадает в прибыль)\n parent_id INTEGER,\n sort INTEGER DEFAULT 0\n )`,\n `CREATE TABLE IF NOT EXISTS uf_contractors (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n name TEXT NOT NULL,\n role TEXT DEFAULT 'other', -- developer | broker | client | bank | staff | other\n inn TEXT DEFAULT '',\n phone TEXT DEFAULT '',\n comment TEXT DEFAULT ''\n )`,\n `CREATE TABLE IF NOT EXISTS uf_projects (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n name TEXT NOT NULL,\n grp TEXT DEFAULT '',\n status TEXT DEFAULT 'work',\n date_start TEXT, date_end TEXT\n )`,\n `CREATE TABLE IF NOT EXISTS uf_deals (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n name TEXT NOT NULL,\n client TEXT DEFAULT '',\n object_name TEXT DEFAULT '', -- ЖК / объект\n developer_id INTEGER, -- застройщик (контрагент)\n broker_id INTEGER, -- брокер (контрагент)\n status TEXT DEFAULT 'deposit', -- deposit | booked | deal | paid | failed\n price REAL DEFAULT 0, -- стоимость объекта\n commission REAL DEFAULT 0, -- комиссия агентства (наш доход)\n broker_fee REAL DEFAULT 0, -- вознаграждение брокера\n deposit_amount REAL DEFAULT 0, -- задаток\n d_deposit TEXT, d_booked TEXT, d_deal TEXT, d_closed TEXT,\n created_at TEXT,\n comment TEXT DEFAULT ''\n )`,\n `CREATE TABLE IF NOT EXISTS uf_ops (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n date TEXT NOT NULL,\n op_type TEXT NOT NULL, -- income | expense | transfer\n confirmed INTEGER DEFAULT 1,\n amount REAL NOT NULL,\n account_id INTEGER,\n account2_id INTEGER,\n category_id INTEGER,\n contractor_id INTEGER,\n deal_id INTEGER,\n project_id INTEGER,\n purpose TEXT DEFAULT ''\n )`,\n `CREATE TABLE IF NOT EXISTS uf_plans (\n period TEXT PRIMARY KEY, -- YYYY-MM\n income REAL DEFAULT 0,\n expense REAL DEFAULT 0,\n deals INTEGER DEFAULT 0\n )`,\n `CREATE TABLE IF NOT EXISTS uf_deal_notes (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n deal_id INTEGER NOT NULL,\n text TEXT NOT NULL,\n created_at TEXT\n )`,\n `CREATE INDEX IF NOT EXISTS uf_ops_date ON uf_ops(date)`,\n `CREATE INDEX IF NOT EXISTS uf_ops_deal ON uf_ops(deal_id)`,\n]);\n\nasync function setting(key: string, def = \"\"): Promise {\n const r = await sqlite.execute({ sql: `SELECT value FROM uf_settings WHERE key=?`, args: [key] });\n return rows(r).length ? String(rows(r)[0][0]) : def;\n}\nasync function setSetting(key: string, value: string) {\n await sqlite.execute({ sql: `INSERT INTO uf_settings(key,value) VALUES(?,?) ON CONFLICT(key) DO UPDATE SET value=excluded.value`, args: [key, value] });\n}\nif (!(await setting(\"pin\"))) await setSetting(\"pin\", \"1111\");\nif (!(await setting(\"company\"))) await setSetting(\"company\", \"Unlock\");\n\n// ---------------- Auth ----------------\napp.post(\"/api/login\", async (c) => {\n const b = await c.req.json();\n const pin = await setting(\"pin\", \"1111\");\n return c.json({ ok: String(b.pin) === pin });\n});\napp.use(\"/api/*\", async (c, next) => {\n if (c.req.path === \"/api/login\") return next();\n const pin = await setting(\"pin\", \"1111\");\n if (c.req.header(\"x-pin\") !== pin) return c.json({ error: \"unauthorized\" }, 401);\n await next();\n});\n\n// Класс-фильтр: только pl-статьи попадают в доходы/расходы (прибыль).\nconst PL = `COALESCE(cat.cls,'pl')='pl'`;\n\n// ---------------- Миграция из старой базы (pf_*) ----------------\napp.post(\"/api/admin/migrate\", async (c) => {\n const log: string[] = [];\n const t = (await sqlite.execute(\n `SELECT name FROM sqlite_master WHERE type='table' AND name LIKE 'pf_%'`,\n )).rows.map((r: any) => String(r[0]));\n if (!t.includes(\"pf_ops\")) return c.json({ error: \"старые таблицы pf_* не найдены\" }, 400);\n\n // чистим целевые таблицы (идемпотентно)\n for (const tbl of [\"uf_ops\", \"uf_deals\", \"uf_contractors\", \"uf_accounts\", \"uf_categories\", \"uf_projects\", \"uf_plans\", \"uf_deal_notes\"]) {\n await sqlite.execute(`DELETE FROM ${tbl}`);\n }\n\n // Счета (id сохраняем)\n await sqlite.execute(`INSERT INTO uf_accounts(id,name,acc_type,bank,grp,start_balance,start_date,active)\n SELECT id,TRIM(name),acc_type,bank,grp,start_balance,start_date,active FROM pf_accounts`);\n log.push(`accounts: ${num((await sqlite.execute(`SELECT COUNT(*) FROM uf_accounts`)).rows[0][0])}`);\n\n // Статьи: cat_class → cls ('' | income | outcome → pl)\n await sqlite.execute(`INSERT INTO uf_categories(id,name,kind,cls,parent_id,sort)\n SELECT id,TRIM(name),kind,\n CASE LOWER(COALESCE(cat_class,'')) WHEN 'assets' THEN 'assets' WHEN 'liabilities' THEN 'liabilities' WHEN 'capital' THEN 'capital' ELSE 'pl' END,\n parent_id,sort FROM pf_categories`);\n log.push(`categories: ${num((await sqlite.execute(`SELECT COUNT(*) FROM uf_categories`)).rows[0][0])}`);\n\n // Контрагенты: роль эвристикой по группе/имени; уточняется вручную в справочнике\n await sqlite.execute(`INSERT INTO uf_contractors(id,name,role,inn,comment)\n SELECT id,TRIM(name),\n CASE\n WHEN LOWER(COALESCE(grp,'')) LIKE '%застройщик%' OR LOWER(name) LIKE '%застройщик%' OR LOWER(name) LIKE '%сз %' OR LOWER(name) LIKE '%сз«%' OR LOWER(name) LIKE '%спецзастройщик%' THEN 'developer'\n WHEN LOWER(COALESCE(grp,'')) LIKE '%брокер%' OR LOWER(name) LIKE '%брокер%' THEN 'broker'\n WHEN LOWER(COALESCE(grp,'')) LIKE '%банк%' OR LOWER(name) LIKE '%банк%' THEN 'bank'\n WHEN LOWER(COALESCE(grp,'')) LIKE '%сотрудник%' THEN 'staff'\n ELSE 'other'\n END, inn, COALESCE(comment,'') FROM pf_contractors`);\n log.push(`contractors: ${num((await sqlite.execute(`SELECT COUNT(*) FROM uf_contractors`)).rows[0][0])}`);\n\n // Проекты\n await sqlite.execute(`INSERT INTO uf_projects(id,name,grp,status,date_start,date_end)\n SELECT id,TRIM(name),COALESCE(grp,''),COALESCE(status,'work'),date_start,date_end FROM pf_projects`);\n log.push(`projects: ${num((await sqlite.execute(`SELECT COUNT(*) FROM uf_projects`)).rows[0][0])}`);\n\n // Сделки: статусы done→paid, fail→failed, work/nuance→deal; комиссия = amount\n await sqlite.execute(`INSERT INTO uf_deals(id,name,client,status,commission,d_deal,d_closed,created_at,comment)\n SELECT id,TRIM(name),COALESCE(client,''),\n CASE status WHEN 'done' THEN 'paid' WHEN 'fail' THEN 'failed' ELSE 'deal' END,\n amount,\n created_at,\n CASE WHEN status IN ('done','fail') THEN created_at ELSE NULL END,\n created_at,COALESCE(comment,'') FROM pf_deals`);\n log.push(`deals: ${num((await sqlite.execute(`SELECT COUNT(*) FROM uf_deals`)).rows[0][0])}`);\n\n // Привязка брокера к сделке по имени сделки = имени контрагента-брокера (если совпало)\n await sqlite.execute(`UPDATE uf_deals SET broker_id=(SELECT c.id FROM uf_contractors c WHERE TRIM(c.name)=TRIM(uf_deals.name) LIMIT 1)`);\n\n // Операции (id и все связи сохраняем)\n await sqlite.execute(`INSERT INTO uf_ops(id,date,op_type,confirmed,amount,account_id,account2_id,category_id,contractor_id,deal_id,project_id,purpose)\n SELECT id,date,op_type,confirmed,amount,account_id,account2_id,category_id,contractor_id,deal_id,project_id,COALESCE(purpose,'') FROM pf_ops`);\n log.push(`ops: ${num((await sqlite.execute(`SELECT COUNT(*) FROM uf_ops`)).rows[0][0])}`);\n\n // Планы: из БДР-бюджетов (сумма по всем бюджетам за период)\n if (t.includes(\"pf_budget_values\")) {\n await sqlite.execute(`INSERT INTO uf_plans(period,income,expense,deals)\n SELECT period,\n SUM(CASE WHEN row_key LIKE 'inc_%' OR row_key='oi' THEN amount ELSE 0 END),\n SUM(CASE WHEN row_key IN ('var','fix','oe','dep','int','tax') THEN amount ELSE 0 END),\n 0\n FROM pf_budget_values GROUP BY period`);\n log.push(`plans: ${num((await sqlite.execute(`SELECT COUNT(*) FROM uf_plans`)).rows[0][0])}`);\n }\n\n // Роль developer контрагентам, платившим нам по сделкам (доход, привязанный к сделке)\n await sqlite.execute(`UPDATE uf_contractors SET role='developer' WHERE role='other' AND id IN (\n SELECT DISTINCT o.contractor_id FROM uf_ops o WHERE o.op_type='income' AND o.deal_id IS NOT NULL AND o.contractor_id IS NOT NULL)`);\n\n return c.json({ ok: true, log });\n});\n\n// ---------------- Meta ----------------\napp.get(\"/api/meta\", async (c) => {\n const [acc, cats, contr, proj] = await Promise.all([\n sqlite.execute(`SELECT id,name,acc_type,bank,grp,start_balance,start_date,active FROM uf_accounts ORDER BY name`),\n sqlite.execute(`SELECT id,name,kind,cls,parent_id,sort FROM uf_categories ORDER BY kind,sort,name`),\n sqlite.execute(`SELECT id,name,role,inn,phone,comment FROM uf_contractors ORDER BY name`),\n sqlite.execute(`SELECT id,name,grp,status,date_start,date_end FROM uf_projects ORDER BY name`),\n ]);\n // балансы по счетам\n const bal = await sqlite.execute(`\n SELECT a.id, a.start_balance\n + COALESCE((SELECT SUM(CASE o.op_type WHEN 'income' THEN o.amount WHEN 'expense' THEN -o.amount\n WHEN 'transfer' THEN CASE WHEN o.account_id=a.id THEN -o.amount ELSE 0 END END)\n FROM uf_ops o WHERE o.confirmed=1 AND (o.account_id=a.id OR (o.op_type='transfer' AND o.account2_id=a.id))),0)\n + COALESCE((SELECT SUM(o.amount) FROM uf_ops o WHERE o.confirmed=1 AND o.op_type='transfer' AND o.account2_id=a.id),0)\n FROM uf_accounts a`);\n const balMap: Record = {};\n for (const r of rows(bal)) balMap[String(r[0])] = num(r[1]);\n const company = await setting(\"company\", \"Unlock\");\n return c.json({\n company,\n accounts: rows(acc).map((r) => ({ id: r[0], name: r[1], acc_type: r[2], bank: r[3], grp: r[4], start_balance: num(r[5]), start_date: r[6], active: num(r[7]), balance: balMap[String(r[0])] ?? num(r[5]) })),\n categories: rows(cats).map((r) => ({ id: r[0], name: r[1], kind: r[2], cls: r[3], parent_id: r[4], sort: num(r[5]) })),\n contractors: rows(contr).map((r) => ({ id: r[0], name: r[1], role: r[2], inn: r[3], phone: r[4], comment: r[5] })),\n projects: rows(proj).map((r) => ({ id: r[0], name: r[1], grp: r[2], status: r[3], date_start: r[4], date_end: r[5] })),\n });\n});\n\n// ---------------- Дашборд ----------------\napp.get(\"/api/dashboard\", async (c) => {\n const month = c.req.query(\"month\") || today().slice(0, 7);\n const year = month.slice(0, 4);\n\n // помесячные факт-суммы за 12 месяцев года (только pl-классы)\n const months = await sqlite.execute({\n sql: `SELECT substr(o.date,1,7) m,\n SUM(CASE WHEN o.op_type='income' AND o.confirmed=1 AND ${PL} THEN o.amount ELSE 0 END),\n SUM(CASE WHEN o.op_type='expense' AND o.confirmed=1 AND ${PL} THEN o.amount ELSE 0 END)\n FROM uf_ops o LEFT JOIN uf_categories cat ON cat.id=o.category_id\n WHERE substr(o.date,1,4)=? GROUP BY m ORDER BY m`,\n args: [year],\n });\n const byM: Record = {};\n for (const r of rows(months)) byM[String(r[0])] = { income: num(r[1]), expense: num(r[2]) };\n\n const mi = byM[month]?.income || 0, me = byM[month]?.expense || 0;\n let yi = 0, ye = 0;\n for (const k in byM) { yi += byM[k].income; ye += byM[k].expense; }\n\n // на заходе: незакрытые сделки, комиссия минус уже полученное\n const pipe = await sqlite.execute(`\n SELECT COALESCE(SUM(d.commission - COALESCE((SELECT SUM(o.amount) FROM uf_ops o WHERE o.deal_id=d.id AND o.op_type='income' AND o.confirmed=1),0)),0),\n COUNT(*)\n FROM uf_deals d WHERE d.status IN ('deposit','booked','deal')`);\n const pipeline = Math.max(0, num(rows(pipe)[0][0])), pipeCount = num(rows(pipe)[0][1]);\n\n // от застройщиков за месяц: доход по сделкам или от контрагентов-застройщиков\n const dev = await sqlite.execute({\n sql: `SELECT COALESCE(SUM(o.amount),0) FROM uf_ops o\n LEFT JOIN uf_categories cat ON cat.id=o.category_id\n LEFT JOIN uf_contractors ct ON ct.id=o.contractor_id\n WHERE o.op_type='income' AND o.confirmed=1 AND ${PL} AND substr(o.date,1,7)=?\n AND (o.deal_id IS NOT NULL OR ct.role='developer')`,\n args: [month],\n });\n // брокерам за месяц: расход контрагентам-брокерам или по статье с «брокер»\n const brok = await sqlite.execute({\n sql: `SELECT COALESCE(SUM(o.amount),0) FROM uf_ops o\n LEFT JOIN uf_categories cat ON cat.id=o.category_id\n LEFT JOIN uf_contractors ct ON ct.id=o.contractor_id\n WHERE o.op_type='expense' AND o.confirmed=1 AND ${PL} AND substr(o.date,1,7)=?\n AND (ct.role='broker' OR LOWER(COALESCE(cat.name,'')) LIKE '%брокер%')`,\n args: [month],\n });\n\n // средний чек: средняя комиссия сделок, прошедших в месяце\n const avgCheck = await sqlite.execute({\n sql: `SELECT COALESCE(AVG(commission),0), COUNT(*) FROM uf_deals\n WHERE commission>0 AND status IN ('deal','paid') AND substr(COALESCE(d_deal,created_at),1,7)=?`,\n args: [month],\n });\n\n // воронка месяца\n const f = async (sql: string) => num(rows(await sqlite.execute({ sql, args: [month] }))[0][0]);\n const funnel = {\n deposits: await f(`SELECT COUNT(*) FROM uf_deals WHERE substr(COALESCE(d_deposit,created_at),1,7)=?`),\n booked: await f(`SELECT COUNT(*) FROM uf_deals WHERE d_booked IS NOT NULL AND substr(d_booked,1,7)=?`),\n deals: await f(`SELECT COUNT(*) FROM uf_deals WHERE status IN ('deal','paid') AND substr(COALESCE(d_deal,created_at),1,7)=?`),\n failed: await f(`SELECT COUNT(*) FROM uf_deals WHERE status='failed' AND substr(COALESCE(d_closed,created_at),1,7)=?`),\n };\n\n // кредиты/овердрафт: непогашенный остаток по не-pl статьям (assets/liabilities)\n const credit = await sqlite.execute(`\n SELECT COALESCE(SUM(CASE o.op_type WHEN 'income' THEN o.amount WHEN 'expense' THEN -o.amount ELSE 0 END),0)\n FROM uf_ops o JOIN uf_categories cat ON cat.id=o.category_id\n WHERE o.confirmed=1 AND cat.cls IN ('assets','liabilities')`);\n\n // средний расход/мес: по последним 6 завершённым месяцам с операциями\n const avgExp = await sqlite.execute({\n sql: `SELECT COALESCE(AVG(s),0) FROM (\n SELECT SUM(o.amount) s FROM uf_ops o LEFT JOIN uf_categories cat ON cat.id=o.category_id\n WHERE o.op_type='expense' AND o.confirmed=1 AND ${PL} AND substr(o.date,1,7) = {};\n for (const r of rows(dealsByM)) dealsMap[String(r[0])] = num(r[1]);\n\n return c.json({\n month, year,\n month_income: mi, month_expense: me, month_np: mi - me,\n year_income: yi, year_expense: ye, year_np: yi - ye,\n months: byM,\n deals_by_month: dealsMap,\n pipeline, pipeline_count: pipeCount,\n from_developers: num(rows(dev)[0][0]),\n to_brokers: num(rows(brok)[0][0]),\n avg_check: num(rows(avgCheck)[0][0]), avg_check_count: num(rows(avgCheck)[0][1]),\n funnel,\n credit_debt: num(rows(credit)[0][0]),\n avg_expense: num(rows(avgExp)[0][0]),\n plan_month: pm ? { income: num(pm[0]), expense: num(pm[1]), deals: num(pm[2]) } : null,\n plan_year: { income: num(py[0]), expense: num(py[1]), deals: num(py[2]) },\n struct: rows(struct).map((r) => ({ name: r[0], value: num(r[1]) })),\n });\n});\n\n// ---------------- Операции ----------------\napp.get(\"/api/ops\", async (c) => {\n const q = c.req.query();\n const cond: string[] = []; const args: any[] = [];\n if (q.from) { cond.push(`o.date>=?`); args.push(q.from); }\n if (q.to) { cond.push(`o.date<=?`); args.push(q.to); }\n if (q.type) { cond.push(`o.op_type=?`); args.push(q.type); }\n if (q.account) { cond.push(`(o.account_id=? OR o.account2_id=?)`); args.push(q.account, q.account); }\n if (q.category) { cond.push(`o.category_id=?`); args.push(q.category); }\n if (q.contractor) { cond.push(`o.contractor_id=?`); args.push(q.contractor); }\n if (q.deal) { cond.push(`o.deal_id=?`); args.push(q.deal); }\n if (q.search) { cond.push(`(o.purpose LIKE ? OR ct.name LIKE ? OR cat.name LIKE ?)`); const s = `%${q.search}%`; args.push(s, s, s); }\n const where = cond.length ? `WHERE ` + cond.join(\" AND \") : \"\";\n const lim = Math.min(num(q.limit) || 100, 500), off = num(q.offset) || 0;\n const r = await sqlite.execute({\n sql: `SELECT o.id,o.date,o.op_type,o.confirmed,o.amount,o.account_id,o.account2_id,o.category_id,o.contractor_id,o.deal_id,o.project_id,o.purpose,\n a.name,a2.name,cat.name,ct.name,d.name\n FROM uf_ops o\n LEFT JOIN uf_accounts a ON a.id=o.account_id LEFT JOIN uf_accounts a2 ON a2.id=o.account2_id\n LEFT JOIN uf_categories cat ON cat.id=o.category_id LEFT JOIN uf_contractors ct ON ct.id=o.contractor_id\n LEFT JOIN uf_deals d ON d.id=o.deal_id\n ${where} ORDER BY o.date DESC, o.id DESC LIMIT ${lim} OFFSET ${off}`,\n args,\n });\n const tot = await sqlite.execute({\n sql: `SELECT COUNT(*),\n COALESCE(SUM(CASE WHEN o.op_type='income' AND o.confirmed=1 THEN o.amount END),0),\n COALESCE(SUM(CASE WHEN o.op_type='expense' AND o.confirmed=1 THEN o.amount END),0)\n FROM uf_ops o\n LEFT JOIN uf_categories cat ON cat.id=o.category_id LEFT JOIN uf_contractors ct ON ct.id=o.contractor_id\n ${where}`,\n args,\n });\n return c.json({\n total: num(rows(tot)[0][0]), sum_income: num(rows(tot)[0][1]), sum_expense: num(rows(tot)[0][2]),\n items: rows(r).map((x) => ({\n id: x[0], date: x[1], op_type: x[2], confirmed: num(x[3]), amount: num(x[4]),\n account_id: x[5], account2_id: x[6], category_id: x[7], contractor_id: x[8], deal_id: x[9], project_id: x[10], purpose: x[11],\n account: x[12], account2: x[13], category: x[14], contractor: x[15], deal: x[16],\n })),\n });\n});\napp.post(\"/api/ops\", async (c) => {\n const b = await c.req.json();\n await sqlite.execute({\n sql: `INSERT INTO uf_ops(date,op_type,confirmed,amount,account_id,account2_id,category_id,contractor_id,deal_id,project_id,purpose)\n VALUES(?,?,?,?,?,?,?,?,?,?,?)`,\n args: [b.date || today(), b.op_type, b.confirmed ? 1 : 0, num(b.amount), b.account_id || null, b.account2_id || null, b.category_id || null, b.contractor_id || null, b.deal_id || null, b.project_id || null, b.purpose || \"\"],\n });\n return c.json({ ok: true });\n});\napp.put(\"/api/ops/:id\", async (c) => {\n const b = await c.req.json();\n await sqlite.execute({\n sql: `UPDATE uf_ops SET date=?,op_type=?,confirmed=?,amount=?,account_id=?,account2_id=?,category_id=?,contractor_id=?,deal_id=?,project_id=?,purpose=? WHERE id=?`,\n args: [b.date, b.op_type, b.confirmed ? 1 : 0, num(b.amount), b.account_id || null, b.account2_id || null, b.category_id || null, b.contractor_id || null, b.deal_id || null, b.project_id || null, b.purpose || \"\", c.req.param(\"id\")],\n });\n return c.json({ ok: true });\n});\napp.delete(\"/api/ops/:id\", async (c) => {\n await sqlite.execute({ sql: `DELETE FROM uf_ops WHERE id=?`, args: [c.req.param(\"id\")] });\n return c.json({ ok: true });\n});\n\n// ---------------- Сделки ----------------\napp.get(\"/api/deals\", async (c) => {\n const q = c.req.query();\n const cond: string[] = []; const args: any[] = [];\n if (q.status) { cond.push(`d.status=?`); args.push(q.status); }\n if (q.search) { cond.push(`(d.name LIKE ? OR d.client LIKE ? OR d.object_name LIKE ?)`); const s = `%${q.search}%`; args.push(s, s, s); }\n const where = cond.length ? `WHERE ` + cond.join(\" AND \") : \"\";\n const r = await sqlite.execute({\n sql: `SELECT d.id,d.name,d.client,d.object_name,d.developer_id,d.broker_id,d.status,d.price,d.commission,d.broker_fee,d.deposit_amount,\n d.d_deposit,d.d_booked,d.d_deal,d.d_closed,d.created_at,d.comment,\n dev.name,br.name,\n COALESCE((SELECT SUM(o.amount) FROM uf_ops o WHERE o.deal_id=d.id AND o.op_type='income' AND o.confirmed=1),0) received,\n COALESCE((SELECT SUM(o.amount) FROM uf_ops o WHERE o.deal_id=d.id AND o.op_type='expense' AND o.confirmed=1),0) spent\n FROM uf_deals d\n LEFT JOIN uf_contractors dev ON dev.id=d.developer_id LEFT JOIN uf_contractors br ON br.id=d.broker_id\n ${where} ORDER BY COALESCE(d.d_deal,d.created_at) DESC, d.id DESC`,\n args,\n });\n return c.json({\n items: rows(r).map((x) => ({\n id: x[0], name: x[1], client: x[2], object_name: x[3], developer_id: x[4], broker_id: x[5], status: x[6],\n price: num(x[7]), commission: num(x[8]), broker_fee: num(x[9]), deposit_amount: num(x[10]),\n d_deposit: x[11], d_booked: x[12], d_deal: x[13], d_closed: x[14], created_at: x[15], comment: x[16],\n developer: x[17], broker: x[18], received: num(x[19]), spent: num(x[20]),\n })),\n });\n});\nconst dealArgs = (b: any) => [b.name || \"\", b.client || \"\", b.object_name || \"\", b.developer_id || null, b.broker_id || null, b.status || \"deposit\", num(b.price), num(b.commission), num(b.broker_fee), num(b.deposit_amount), b.d_deposit || null, b.d_booked || null, b.d_deal || null, b.d_closed || null, b.comment || \"\"];\napp.post(\"/api/deals\", async (c) => {\n const b = await c.req.json();\n await sqlite.execute({\n sql: `INSERT INTO uf_deals(name,client,object_name,developer_id,broker_id,status,price,commission,broker_fee,deposit_amount,d_deposit,d_booked,d_deal,d_closed,comment,created_at)\n VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)`,\n args: [...dealArgs(b), today()],\n });\n return c.json({ ok: true });\n});\napp.put(\"/api/deals/:id\", async (c) => {\n const b = await c.req.json();\n await sqlite.execute({\n sql: `UPDATE uf_deals SET name=?,client=?,object_name=?,developer_id=?,broker_id=?,status=?,price=?,commission=?,broker_fee=?,deposit_amount=?,d_deposit=?,d_booked=?,d_deal=?,d_closed=?,comment=? WHERE id=?`,\n args: [...dealArgs(b), c.req.param(\"id\")],\n });\n return c.json({ ok: true });\n});\napp.delete(\"/api/deals/:id\", async (c) => {\n await sqlite.execute({ sql: `DELETE FROM uf_deals WHERE id=?`, args: [c.req.param(\"id\")] });\n return c.json({ ok: true });\n});\napp.get(\"/api/deals/:id/notes\", async (c) => {\n const r = await sqlite.execute({ sql: `SELECT id,text,created_at FROM uf_deal_notes WHERE deal_id=? ORDER BY id DESC`, args: [c.req.param(\"id\")] });\n return c.json({ items: rows(r).map((x) => ({ id: x[0], text: x[1], created_at: x[2] })) });\n});\napp.post(\"/api/deals/:id/notes\", async (c) => {\n const b = await c.req.json();\n await sqlite.execute({ sql: `INSERT INTO uf_deal_notes(deal_id,text,created_at) VALUES(?,?,?)`, args: [c.req.param(\"id\"), b.text || \"\", new Date().toISOString()] });\n return c.json({ ok: true });\n});\n\n// ---------------- Справочники CRUD ----------------\nfunction crud(name: string, table: string, cols: string[]) {\n app.post(`/api/${name}`, async (c) => {\n const b = await c.req.json();\n await sqlite.execute({\n sql: `INSERT INTO ${table}(${cols.join(\",\")}) VALUES(${cols.map(() => \"?\").join(\",\")})`,\n args: cols.map((k) => b[k] ?? null),\n });\n return c.json({ ok: true });\n });\n app.put(`/api/${name}/:id`, async (c) => {\n const b = await c.req.json();\n await sqlite.execute({\n sql: `UPDATE ${table} SET ${cols.map((k) => `${k}=?`).join(\",\")} WHERE id=?`,\n args: [...cols.map((k) => b[k] ?? null), c.req.param(\"id\")],\n });\n return c.json({ ok: true });\n });\n app.delete(`/api/${name}/:id`, async (c) => {\n await sqlite.execute({ sql: `DELETE FROM ${table} WHERE id=?`, args: [c.req.param(\"id\")] });\n return c.json({ ok: true });\n });\n}\ncrud(\"accounts\", \"uf_accounts\", [\"name\", \"acc_type\", \"bank\", \"grp\", \"start_balance\", \"start_date\", \"active\"]);\ncrud(\"categories\", \"uf_categories\", [\"name\", \"kind\", \"cls\", \"parent_id\", \"sort\"]);\ncrud(\"contractors\", \"uf_contractors\", [\"name\", \"role\", \"inn\", \"phone\", \"comment\"]);\ncrud(\"projects\", \"uf_projects\", [\"name\", \"grp\", \"status\", \"date_start\", \"date_end\"]);\n\n// ---------------- План ----------------\napp.get(\"/api/plans\", async (c) => {\n const year = c.req.query(\"year\") || today().slice(0, 4);\n const r = await sqlite.execute({ sql: `SELECT period,income,expense,deals FROM uf_plans WHERE period LIKE ? ORDER BY period`, args: [year + \"-%\"] });\n // факт для сравнения\n const fact = await sqlite.execute({\n sql: `SELECT substr(o.date,1,7) m,\n SUM(CASE WHEN o.op_type='income' AND o.confirmed=1 AND ${PL} THEN o.amount ELSE 0 END),\n SUM(CASE WHEN o.op_type='expense' AND o.confirmed=1 AND ${PL} THEN o.amount ELSE 0 END)\n FROM uf_ops o LEFT JOIN uf_categories cat ON cat.id=o.category_id\n WHERE substr(o.date,1,4)=? GROUP BY m`,\n args: [year],\n });\n const factMap: Record = {};\n for (const x of rows(fact)) factMap[String(x[0])] = { income: num(x[1]), expense: num(x[2]) };\n const dealsFact = await sqlite.execute({\n sql: `SELECT substr(COALESCE(d_deal,created_at),1,7) m, COUNT(*) FROM uf_deals WHERE status IN ('deal','paid') AND substr(COALESCE(d_deal,created_at),1,4)=? GROUP BY m`,\n args: [year],\n });\n for (const x of rows(dealsFact)) { factMap[String(x[0])] = { ...(factMap[String(x[0])] || {}), deals: num(x[1]) }; }\n return c.json({\n items: rows(r).map((x) => ({ period: x[0], income: num(x[1]), expense: num(x[2]), deals: num(x[3]) })),\n fact: factMap,\n });\n});\napp.put(\"/api/plans/:period\", async (c) => {\n const b = await c.req.json();\n await sqlite.execute({\n sql: `INSERT INTO uf_plans(period,income,expense,deals) VALUES(?,?,?,?)\n ON CONFLICT(period) DO UPDATE SET income=excluded.income,expense=excluded.expense,deals=excluded.deals`,\n args: [c.req.param(\"period\"), num(b.income), num(b.expense), num(b.deals)],\n });\n return c.json({ ok: true });\n});\n\n// ---------------- Отчёты ----------------\napp.get(\"/api/report/cashflow\", async (c) => {\n const year = c.req.query(\"year\") || today().slice(0, 4);\n const r = await sqlite.execute({\n sql: `SELECT COALESCE(root.id,cat.id) rid, COALESCE(root.name,cat.name,'Без статьи') rname,\n COALESCE(root.kind,cat.kind,o.op_type) rkind, COALESCE(root.cls,cat.cls,'pl') rcls,\n substr(o.date,1,7) m, SUM(o.amount)\n FROM uf_ops o LEFT JOIN uf_categories cat ON cat.id=o.category_id\n LEFT JOIN uf_categories root ON root.id=cat.parent_id\n WHERE o.confirmed=1 AND o.op_type IN ('income','expense') AND substr(o.date,1,4)=?\n GROUP BY rid, m`,\n args: [year],\n });\n return c.json({ rows: rows(r).map((x) => ({ id: x[0], name: x[1], kind: x[2], cls: x[3], month: x[4], value: num(x[5]) })) });\n});\napp.get(\"/api/report/pl\", async (c) => {\n const year = c.req.query(\"year\") || today().slice(0, 4);\n const r = await sqlite.execute({\n sql: `SELECT substr(o.date,1,7) m,\n SUM(CASE WHEN o.op_type='income' AND ${PL} THEN o.amount ELSE 0 END),\n SUM(CASE WHEN o.op_type='expense' AND ${PL} THEN o.amount ELSE 0 END)\n FROM uf_ops o LEFT JOIN uf_categories cat ON cat.id=o.category_id\n WHERE o.confirmed=1 AND substr(o.date,1,4)=? GROUP BY m ORDER BY m`,\n args: [year],\n });\n return c.json({ rows: rows(r).map((x) => ({ month: x[0], income: num(x[1]), expense: num(x[2]) })) });\n});\n\n// ---------------- Настройки ----------------\napp.get(\"/api/settings\", async (c) => c.json({ company: await setting(\"company\", \"Unlock\") }));\napp.post(\"/api/settings\", async (c) => {\n const b = await c.req.json();\n if (b.company !== undefined) await setSetting(\"company\", String(b.company));\n if (b.pin) await setSetting(\"pin\", String(b.pin));\n return c.json({ ok: true });\n});\n\n// ---------------- Статика ----------------\napp.get(\"/frontend/app.js\", async () => {\n let out = \"\";\n for (let i = 1; i <= 9; i++) {\n try { out += await readFile(`/frontend/app.part${i}.txt`, import.meta.url); } catch (_e) { break; }\n }\n return new Response(out, { headers: { \"content-type\": \"text/javascript;charset=UTF-8\" } });\n});\napp.get(\"/\", (c) => serveFile(\"/frontend/index.html\", import.meta.url));\napp.get(\"/frontend/*\", (c) => serveFile(c.req.path, import.meta.url));\n\nexport default app.fetch;\n","label",[138],"af943855-9598-4b6c-974d-b40c098081dd",["D",1787314822204],[28],"pulls","branches","forks","envVars",{"_157":67,"_159":67},"connections",{"_157":67,"_158":67},"count","unfilledCount","emptyCount",{"_47":147,"_32":28,"_30":31,"_139":-5,"_39":40,"_38":14,"_141":63,"_36":171,"_13":67,"_33":34,"_35":-5,"_24":25,"_145":-5,"_164":172,"_27":173},{"_47":167,"_32":44,"_30":43,"_139":31,"_39":40,"_38":14,"_141":63,"_36":168,"_13":67,"_33":21,"_35":45,"_24":25,"_145":-5,"_164":169,"_27":170},{"_47":137,"_32":138,"_30":61,"_139":-5,"_39":40,"_38":140,"_141":25,"_36":163,"_13":67,"_33":143,"_35":144,"_24":25,"_145":-5,"_164":165,"_27":166},["D",1787314821565],"children",[],[138],"33ecea45-688f-420c-a1a3-84b5cb5fe6a6",["D",1787314822204],[],[28,44],["D",1787314822204],[161],[28],["D",1787314298248],["D",1789484509295],{"_104":103,"_112":108},{"_104":103,"_105":106,"_107":108,"_18":59,"_109":110,"_111":25},{"_13":93,"_32":94,"_47":40,"_95":48,"_96":-5,"_97":-5,"_98":-5,"_99":-5,"_100":-5,"_101":49,"_102":103},"ctx",{"_86":-5,"_132":212,"_213":214,"_215":216,"_217":25,"_218":25,"_219":25,"_220":63,"_221":-5,"_222":-7,"_223":-7,"_224":67,"_225":25,"_226":25,"_227":-5,"_228":25,"_229":25,"_230":25,"_231":-5},"ENV",{"_183":184,"_185":186,"_187":94,"_188":189,"_190":191,"_192":193,"_194":195,"_196":197,"_198":199,"_200":201,"_202":203,"_204":205,"_206":207,"_208":209,"_210":211},"REACT_APP_SENTRY_DSN","https://586020d86f05489db63a554690acf1e9@o1398359.ingest.sentry.io/6724499","RENDER_GIT_COMMIT","e87eb04e707cf64e7e532c8e8ec395f209e17f15","RENDER_GIT_BRANCH","POSTHOG_API_TOKEN","phc_nmoyuAkStEZuUp69rYlg1DZgrIt19Gx0Y4Hwf8tGgJP","POSTHOG_API_HOST","https://wart.val.town","WEB_API_TEMPLATE","https://{val}.web.val.run","APP_URL","https://www.val.town","MODULE_URL","https://esm.town","VANITY_WEB_API_TEMPLATE","https://{label}.val.run/","DENOLS_URL","https://lsp.val.town","NODE_ENV","production","CLOUDFLARE_TURNSTILE_SITE_KEY","0x4AAAAAACtaIZJH7s9rBMZ6","GOOGLE_CLIENT_ID","875989696899-jtsqkhlbdv5jrlsk8631p7ipjg3c6o62.apps.googleusercontent.com","STRIPE_PUBLISHABLE_KEY","pk_live_51N2zvWEpZTTLipIkVEwXrUG9AT0yeOOEYxszarZT1jpsZV58Ly6wlg3SltZpHN26CUalOBmUSY7adQRQiSkCZz2500qSXNkf8Y","API_URL","https://api.val.town",[],"preferencesKeybindings","default","preferencesTheme","light","preferencesFixers","preferencesCopilot","preferencesSelectionAi","preferencesCloseBrackets","preferencesChatPanelState","preferencesDprint","preferencesDenofmt","notificationCount","flagDenols","isBusinessOrgMember","businessOrgHandle","isBusinessOrgAdmin","isHigherTownieTier","navCollapsed","currentOrgId"]

Diff · v2