534 lines
20 KiB
JavaScript
534 lines
20 KiB
JavaScript
|
const express = require('express');
|
||
|
|
const cors = require('cors');
|
||
|
|
const path = require('path');
|
||
|
|
const multer = require('multer');
|
||
|
|
const db = require('./db');
|
||
|
|
const issueService = require('./issueService');
|
||
|
|
|
||
|
|
const app = express();
|
||
|
|
const PORT = process.env.PORT || 3000;
|
||
|
|
|
||
|
|
app.use(cors());
|
||
|
|
app.use(express.json());
|
||
|
|
|
||
|
|
const router = express.Router();
|
||
|
|
router.use(express.static(path.join(__dirname, 'public')));
|
||
|
|
router.use('/uploads', express.static(path.join(__dirname, 'uploads')));
|
||
|
|
|
||
|
|
// Multer storage for issue attachments
|
||
|
|
const storage = multer.diskStorage({
|
||
|
|
destination: function (req, file, cb) {
|
||
|
|
issueService.ensureDirs();
|
||
|
|
cb(null, issueService.UPLOADS_DIR);
|
||
|
|
},
|
||
|
|
filename: function (req, file, cb) {
|
||
|
|
const ext = path.extname(file.originalname);
|
||
|
|
const safeBase = path.basename(file.originalname, ext).replace(/[^a-zA-Z0-9_-]/g, '_');
|
||
|
|
const uniqueSuffix = Date.now() + '-' + Math.round(Math.random() * 1e6);
|
||
|
|
cb(null, `${safeBase}_${uniqueSuffix}${ext}`);
|
||
|
|
}
|
||
|
|
});
|
||
|
|
|
||
|
|
const upload = multer({
|
||
|
|
storage: storage,
|
||
|
|
limits: { fileSize: 25 * 1024 * 1024 }, // 25MB max
|
||
|
|
});
|
||
|
|
|
||
|
|
// Ensure giaoban_ghichu table exists
|
||
|
|
async function ensureTable(dbName) {
|
||
|
|
try {
|
||
|
|
await db.query(`
|
||
|
|
CREATE TABLE IF NOT EXISTS current.giaoban_ghichu (
|
||
|
|
id SERIAL PRIMARY KEY,
|
||
|
|
maba VARCHAR(50) NOT NULL,
|
||
|
|
mabn VARCHAR(20) NOT NULL,
|
||
|
|
loai_danhsach VARCHAR(20) NOT NULL,
|
||
|
|
ghichu TEXT,
|
||
|
|
ngay_capnhat TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
CONSTRAINT unq_giaoban_item_${dbName ? dbName.replace(/[^a-zA-Z0-9]/g, '_') : 'default'} UNIQUE(maba, loai_danhsach)
|
||
|
|
);
|
||
|
|
`, [], dbName);
|
||
|
|
} catch (err) {
|
||
|
|
// If permission or table exists, ignore
|
||
|
|
}
|
||
|
|
}
|
||
|
|
|
||
|
|
// 1. Danh sách database
|
||
|
|
router.get('/api/databases', async (req, res) => {
|
||
|
|
try {
|
||
|
|
const list = [
|
||
|
|
{ id: 'an_omon_full_04052026', name: 'BVĐK Ô Môn - Cần Thơ (33 GB - Đầy đủ khoa phòng chuẩn)' },
|
||
|
|
{ id: 'tuan_dbgovap_full', name: 'BVĐK Gò Vấp - TP.HCM (65 GB - Dữ liệu lớn)' },
|
||
|
|
{ id: 'binh_bvq12_062026052026042026032026022026', name: 'BV Quận 12 - TP.HCM (21 GB)' },
|
||
|
|
{ id: 'bvq12_697', name: 'BV Quận 12 - Bản 697 (17 GB)' },
|
||
|
|
{ id: 'an_dalieu_full_05082026', name: 'BV Da Liễu (31 GB)' },
|
||
|
|
];
|
||
|
|
res.json({ success: true, data: list });
|
||
|
|
} catch (error) {
|
||
|
|
res.status(500).json({ success: false, message: error.message });
|
||
|
|
}
|
||
|
|
});
|
||
|
|
|
||
|
|
// Helper function to build namkt and thangkt partition filter
|
||
|
|
function getThangNamFilter(fromDateStr, toDateStr, prefix = '') {
|
||
|
|
try {
|
||
|
|
const f = new Date(fromDateStr);
|
||
|
|
const t = new Date(toDateStr);
|
||
|
|
if (isNaN(f.getTime()) || isNaN(t.getTime())) return '';
|
||
|
|
|
||
|
|
const thangs = new Set();
|
||
|
|
const nams = new Set();
|
||
|
|
let curr = new Date(f.getFullYear(), f.getMonth(), 1);
|
||
|
|
const end = new Date(t.getFullYear(), t.getMonth(), 1);
|
||
|
|
let count = 0;
|
||
|
|
while (curr <= end && count < 24) {
|
||
|
|
thangs.add(String(curr.getMonth() + 1).padStart(2, '0'));
|
||
|
|
nams.add(String(curr.getFullYear()));
|
||
|
|
curr.setMonth(curr.getMonth() + 1);
|
||
|
|
count++;
|
||
|
|
}
|
||
|
|
const p = prefix ? `${prefix}.` : '';
|
||
|
|
const thangsArr = Array.from(thangs).map(m => `'${m}'`).join(',');
|
||
|
|
const namsArr = Array.from(nams).map(y => `'${y}'`).join(',');
|
||
|
|
return ` AND ${p}namkt IN (${namsArr}) AND ${p}thangkt IN (${thangsArr}) `;
|
||
|
|
} catch (e) {
|
||
|
|
return '';
|
||
|
|
}
|
||
|
|
}
|
||
|
|
|
||
|
|
// Helper for Outpatient query
|
||
|
|
async function getOutpatientData(database, fromDate, toDate) {
|
||
|
|
const params = [fromDate, toDate];
|
||
|
|
const fnKb = getThangNamFilter(fromDate, toDate, 'kb');
|
||
|
|
const fnLs = getThangNamFilter(fromDate, toDate, 'ls');
|
||
|
|
const fnNone = getThangNamFilter(fromDate, toDate, '');
|
||
|
|
|
||
|
|
// A. Thống kê theo Khoa khám bệnh
|
||
|
|
const sqlKhoa = `
|
||
|
|
SELECT
|
||
|
|
COALESCE(dv.madv, 'KHAC') AS madv,
|
||
|
|
COALESCE(dv.tendv, 'Khoa khác') AS tendv,
|
||
|
|
COUNT(kb.makb) AS tong_kham,
|
||
|
|
SUM(CASE WHEN dt.bhyt IN (1, 2) THEN 1 ELSE 0 END) AS bhyt,
|
||
|
|
SUM(CASE WHEN dt.bhyt IN (0, 4) THEN 1 ELSE 0 END) AS thuphi,
|
||
|
|
SUM(CASE WHEN kb.dakham = 6 THEN 1 ELSE 0 END) AS chuyenvien
|
||
|
|
FROM current.khambenh kb
|
||
|
|
LEFT JOIN current.psdangky dk ON kb.mabn = dk.mabn AND kb.makb = dk.makb AND COALESCE(dk.xoa, 0) = 0
|
||
|
|
LEFT JOIN current.dmdoituong dt ON dk.madt = dt.madt
|
||
|
|
LEFT JOIN current.dmdonvi dv ON kb.madv = dv.madv
|
||
|
|
WHERE kb.ngaykcb BETWEEN $1 AND $2 ${fnKb}
|
||
|
|
AND COALESCE(kb.xoa, 0) = 0
|
||
|
|
AND COALESCE(kb.dakham, 0) != 0
|
||
|
|
GROUP BY dv.madv, dv.tendv
|
||
|
|
ORDER BY tong_kham DESC;
|
||
|
|
`;
|
||
|
|
|
||
|
|
// B. Bệnh án ngoại trú (Mới vs Tái khám)
|
||
|
|
const sqlBANT = `
|
||
|
|
SELECT
|
||
|
|
SUM(CASE WHEN COALESCE(dk.taikham, 0) = 0 THEN 1 ELSE 0 END) AS moi,
|
||
|
|
SUM(CASE WHEN COALESCE(dk.taikham, 0) = 1 THEN 1 ELSE 0 END) AS taikham,
|
||
|
|
COUNT(*) AS tong
|
||
|
|
FROM current.bnnoitru nt
|
||
|
|
JOIN current.psdangky dk ON nt.mabn = dk.mabn AND nt.makb = dk.makb AND COALESCE(dk.xoa, 0) = 0
|
||
|
|
WHERE nt.namvien = 0 AND COALESCE(nt.xoa, 0) = 0
|
||
|
|
AND nt.ngayvv BETWEEN $1 AND $2;
|
||
|
|
`;
|
||
|
|
|
||
|
|
// C. Chuyên khoa YDCT
|
||
|
|
const sqlYDCT = `
|
||
|
|
SELECT
|
||
|
|
SUM(CASE WHEN cls.tencls ILIKE '%Điện châm%' THEN ls.soluong ELSE 0 END) AS dien_cham,
|
||
|
|
SUM(CASE WHEN cls.tencls ILIKE '%Vật lý trị liệu%' OR cls.tencls ILIKE '%VLTL%' OR cls.macls IN ('DI131','DI135') THEN ls.soluong ELSE 0 END) AS vltl,
|
||
|
|
SUM(CASE WHEN cls.tencls ILIKE '%Sắc thuốc%' THEN ls.soluong ELSE 0 END) AS sac_thuoc,
|
||
|
|
SUM(CASE WHEN cls.tencls ILIKE '%Cấy chỉ%' OR cls.tencls ILIKE '%Chôn chỉ%' THEN ls.soluong ELSE 0 END) AS cay_chi
|
||
|
|
FROM current.chidinhcls ls
|
||
|
|
JOIN current.dmcls cls ON ls.macls = cls.macls
|
||
|
|
WHERE ls.ngaykcb BETWEEN $1 AND $2 ${fnLs}
|
||
|
|
AND COALESCE(ls.xoa, 0) = 0;
|
||
|
|
`;
|
||
|
|
|
||
|
|
// D. Cận lâm sàng
|
||
|
|
const sqlCLS = `
|
||
|
|
SELECT
|
||
|
|
cls.kho,
|
||
|
|
cls.maloai,
|
||
|
|
COALESCE(loaicls.tenloai, cls.maloai) AS tenloai,
|
||
|
|
COUNT(DISTINCT ls.makb) AS so_bn,
|
||
|
|
COUNT(*) AS so_luot
|
||
|
|
FROM current.chidinhcls ls
|
||
|
|
JOIN current.dmcls cls ON ls.macls = cls.macls
|
||
|
|
LEFT JOIN current.dmloaicls loaicls ON cls.maloai = loaicls.maloai
|
||
|
|
WHERE ls.ngaykcb BETWEEN $1 AND $2 ${fnLs}
|
||
|
|
AND COALESCE(ls.xoa, 0) = 0
|
||
|
|
AND cls.kho IN ('XN', 'HA', 'CN')
|
||
|
|
GROUP BY cls.kho, cls.maloai, loaicls.tenloai
|
||
|
|
ORDER BY cls.kho, count(*) DESC;
|
||
|
|
`;
|
||
|
|
|
||
|
|
// E. Top 10 chẩn đoán
|
||
|
|
const sqlTop10 = `
|
||
|
|
SELECT maicd, kqcdoan, COUNT(*) AS sl
|
||
|
|
FROM current.khambenh
|
||
|
|
WHERE ngaykcb BETWEEN $1 AND $2 ${fnNone}
|
||
|
|
AND COALESCE(xoa, 0) = 0
|
||
|
|
AND maicd IS NOT NULL AND maicd != ''
|
||
|
|
GROUP BY maicd, kqcdoan
|
||
|
|
ORDER BY sl DESC
|
||
|
|
LIMIT 10;
|
||
|
|
`;
|
||
|
|
|
||
|
|
// F. Tiêm ngừa
|
||
|
|
const sqlTiemNgua = `
|
||
|
|
SELECT COUNT(kb.makb) AS sl
|
||
|
|
FROM current.khambenh kb
|
||
|
|
LEFT JOIN current.dmphong ph ON kb.maphong = ph.maphong
|
||
|
|
LEFT JOIN current.dmdonvi dv ON kb.madv = dv.madv
|
||
|
|
WHERE kb.ngaykcb BETWEEN $1 AND $2 ${fnKb}
|
||
|
|
AND COALESCE(kb.xoa, 0) = 0
|
||
|
|
AND COALESCE(kb.dakham, 0) != 0
|
||
|
|
AND (
|
||
|
|
COALESCE(ph.loai_bc_giaoban, 0) = 2 OR
|
||
|
|
ph.maphong = 'TN' OR
|
||
|
|
dv.madv = 'PTN' OR
|
||
|
|
ph.tenphong ILIKE '%Tiêm ngừa%' OR
|
||
|
|
ph.tenphong ILIKE '%Tiêm chủng%'
|
||
|
|
);
|
||
|
|
`;
|
||
|
|
|
||
|
|
const [resKhoa, resBANT, resYDCT, resCLS, resTop10, resTiemNgua] = await Promise.all([
|
||
|
|
db.query(sqlKhoa, params, database),
|
||
|
|
db.query(sqlBANT, params, database),
|
||
|
|
db.query(sqlYDCT, params, database),
|
||
|
|
db.query(sqlCLS, params, database),
|
||
|
|
db.query(sqlTop10, params, database),
|
||
|
|
db.query(sqlTiemNgua, params, database),
|
||
|
|
]);
|
||
|
|
|
||
|
|
return {
|
||
|
|
khoa: resKhoa.rows,
|
||
|
|
bant: resBANT.rows[0] || { moi: 0, taikham: 0, tong: 0 },
|
||
|
|
ydct: resYDCT.rows[0] || { dien_cham: 0, vltl: 0, sac_thuoc: 0, cay_chi: 0 },
|
||
|
|
cls: resCLS.rows,
|
||
|
|
top10: resTop10.rows,
|
||
|
|
tiemngua: parseInt(resTiemNgua.rows[0]?.sl || 0, 10),
|
||
|
|
};
|
||
|
|
}
|
||
|
|
|
||
|
|
// 2. API Báo cáo Giao ban Ngoại trú
|
||
|
|
router.get('/api/giao-ban-ngoai-tru', async (req, res) => {
|
||
|
|
try {
|
||
|
|
const { database, from, to, compareFrom, compareTo } = req.query;
|
||
|
|
const dbName = database || process.env.PG_DATABASE;
|
||
|
|
const fromDate = from || '2026-03-01 00:00:00';
|
||
|
|
const toDate = to || '2026-03-31 23:59:59';
|
||
|
|
|
||
|
|
const currentData = await getOutpatientData(dbName, fromDate, toDate);
|
||
|
|
|
||
|
|
let compareData = null;
|
||
|
|
if (compareFrom && compareTo) {
|
||
|
|
compareData = await getOutpatientData(dbName, compareFrom, compareTo);
|
||
|
|
}
|
||
|
|
|
||
|
|
res.json({
|
||
|
|
success: true,
|
||
|
|
data: {
|
||
|
|
current: currentData,
|
||
|
|
compare: compareData,
|
||
|
|
periodCurrent: { from: fromDate, to: toDate },
|
||
|
|
periodCompare: compareFrom && compareTo ? { from: compareFrom, to: compareTo } : null,
|
||
|
|
},
|
||
|
|
});
|
||
|
|
} catch (error) {
|
||
|
|
console.error('Error in /api/giao-ban-ngoai-tru:', error);
|
||
|
|
res.status(500).json({ success: false, message: error.message });
|
||
|
|
}
|
||
|
|
});
|
||
|
|
|
||
|
|
// Helper for Inpatient query
|
||
|
|
async function getInpatientData(database, fromDate, toDate) {
|
||
|
|
const params = [fromDate, toDate];
|
||
|
|
|
||
|
|
const sqlKhoa = `
|
||
|
|
SELECT
|
||
|
|
dv.madv,
|
||
|
|
dv.tendv,
|
||
|
|
-- 1. Bệnh cũ (tồn đầu kỳ)
|
||
|
|
SUM(CASE WHEN nt.ngayvv < $1 AND (nt.ravien = 0 OR nt.ngayrv >= $1) THEN 1 ELSE 0 END) AS benh_cu,
|
||
|
|
-- 2. Bệnh mới (vào viện trong kỳ)
|
||
|
|
SUM(CASE WHEN nt.ngayvv BETWEEN $1 AND $2 THEN 1 ELSE 0 END) AS benh_moi,
|
||
|
|
-- 3. Xuất viện:
|
||
|
|
SUM(CASE WHEN nt.ngayrv BETWEEN $1 AND $2 AND nt.ravien = 1 AND COALESCE(nt.makq, '01') NOT IN ('04','05','06') THEN 1 ELSE 0 END) AS on_xin_ve,
|
||
|
|
SUM(CASE WHEN nt.ngayrv BETWEEN $1 AND $2 AND (nt.makq IN ('04','06') OR (nt.maxt = '09' AND nt.makq = '04')) THEN 1 ELSE 0 END) AS nang_xin_ve,
|
||
|
|
SUM(CASE WHEN nt.ngayrv BETWEEN $1 AND $2 AND (nt.ravien = 2 OR nt.maxt = '08') THEN 1 ELSE 0 END) AS tron_vien,
|
||
|
|
SUM(CASE WHEN nt.ngayrv BETWEEN $1 AND $2 AND (nt.ravien = 4 OR nt.maxt = '03') THEN 1 ELSE 0 END) AS chuyen_vien,
|
||
|
|
SUM(CASE WHEN nt.ngayrv BETWEEN $1 AND $2 AND (nt.ravien = 3 OR nt.maxt = '06') THEN 1 ELSE 0 END) AS tu_vong,
|
||
|
|
-- 4. Bệnh hiện còn (tồn cuối kỳ)
|
||
|
|
SUM(CASE WHEN nt.ngayvv <= $2 AND (nt.ravien = 0 OR nt.ngayrv > $2) THEN 1 ELSE 0 END) AS benh_hien_con,
|
||
|
|
-- 5. Bệnh theo dõi nặng
|
||
|
|
SUM(CASE WHEN nt.ngayvv <= $2 AND (nt.ravien = 0 OR nt.ngayrv >= $1) AND (COALESCE(nt.benhnang, 0) = 1 OR COALESCE(nt.mabatheodoi, '') != '' OR dv.madv IN ('17','29')) THEN 1 ELSE 0 END) AS benh_theo_doi,
|
||
|
|
-- 6. Chăm sóc Cấp I
|
||
|
|
SUM(CASE WHEN nt.ngayvv <= $2 AND (nt.ravien = 0 OR nt.ngayrv >= $1) AND (nt.chamsoc ILIKE '%cấp I%' OR nt.chamsoc ILIKE '%cấp 1%' OR nt.chamsoc IN ('1', 'CS I', 'CSI')) THEN 1 ELSE 0 END) AS chamsoc_cap1
|
||
|
|
FROM current.bnnoitru nt
|
||
|
|
JOIN current.dmdonvi dv ON nt.madv = dv.madv
|
||
|
|
WHERE nt.namvien = 1 AND COALESCE(nt.xoa, 0) = 0
|
||
|
|
AND nt.ngayvv >= ($1::date - INTERVAL '180 days')
|
||
|
|
GROUP BY dv.madv, dv.tendv
|
||
|
|
HAVING SUM(CASE WHEN nt.ngayvv <= $2 AND (nt.ravien = 0 OR nt.ngayrv >= $1) THEN 1 ELSE 0 END) > 0
|
||
|
|
ORDER BY benh_moi DESC;
|
||
|
|
`;
|
||
|
|
|
||
|
|
const resKhoa = await db.query(sqlKhoa, params, database);
|
||
|
|
return {
|
||
|
|
khoa: resKhoa.rows,
|
||
|
|
};
|
||
|
|
}
|
||
|
|
|
||
|
|
// 3. API Báo cáo Giao ban Nội trú
|
||
|
|
router.get('/api/giao-ban-noi-tru', async (req, res) => {
|
||
|
|
try {
|
||
|
|
const { database, from, to, compareFrom, compareTo } = req.query;
|
||
|
|
const dbName = database || process.env.PG_DATABASE;
|
||
|
|
const fromDate = from || '2026-03-01 00:00:00';
|
||
|
|
const toDate = to || '2026-03-31 23:59:59';
|
||
|
|
|
||
|
|
await ensureTable(dbName);
|
||
|
|
const currentData = await getInpatientData(dbName, fromDate, toDate);
|
||
|
|
|
||
|
|
let compareData = null;
|
||
|
|
if (compareFrom && compareTo) {
|
||
|
|
compareData = await getInpatientData(dbName, compareFrom, compareTo);
|
||
|
|
}
|
||
|
|
|
||
|
|
res.json({
|
||
|
|
success: true,
|
||
|
|
data: {
|
||
|
|
current: currentData,
|
||
|
|
compare: compareData,
|
||
|
|
periodCurrent: { from: fromDate, to: toDate },
|
||
|
|
periodCompare: compareFrom && compareTo ? { from: compareFrom, to: compareTo } : null,
|
||
|
|
},
|
||
|
|
});
|
||
|
|
} catch (error) {
|
||
|
|
console.error('Error in /api/giao-ban-noi-tru:', error);
|
||
|
|
res.status(500).json({ success: false, message: error.message });
|
||
|
|
}
|
||
|
|
});
|
||
|
|
|
||
|
|
// 4. API Bảng 3: Danh sách Bệnh nhân Chuyển viện
|
||
|
|
router.get('/api/danh-sach-chuyen-vien', async (req, res) => {
|
||
|
|
try {
|
||
|
|
const { database, from, to, madv } = req.query;
|
||
|
|
const dbName = database || process.env.PG_DATABASE;
|
||
|
|
const fromDate = from || '2026-03-01 00:00:00';
|
||
|
|
const toDate = to || '2026-03-31 23:59:59';
|
||
|
|
|
||
|
|
await ensureTable(dbName);
|
||
|
|
|
||
|
|
let sqlWhere = `
|
||
|
|
WHERE nt.namvien = 1 AND COALESCE(nt.xoa, 0) = 0
|
||
|
|
AND nt.ngayrv BETWEEN $1 AND $2
|
||
|
|
AND (nt.ravien = 4 OR nt.maxt = '03')
|
||
|
|
`;
|
||
|
|
const params = [fromDate, toDate];
|
||
|
|
if (madv && madv !== 'ALL') {
|
||
|
|
params.push(madv);
|
||
|
|
sqlWhere += ` AND nt.madv = $${params.length} `;
|
||
|
|
}
|
||
|
|
|
||
|
|
const sql = `
|
||
|
|
SELECT
|
||
|
|
nt.mabn,
|
||
|
|
nt.maba,
|
||
|
|
bn.holot || ' ' || bn.ten AS hoten,
|
||
|
|
bn.diachi,
|
||
|
|
to_char(nt.ngayvv, 'DD/MM/YYYY HH24:MI') AS ngayvv,
|
||
|
|
to_char(nt.ngayrv, 'DD/MM/YYYY HH24:MI') AS ngayrv,
|
||
|
|
COALESCE(dv.tendv, nt.madv) AS tendv,
|
||
|
|
nt.kqcdoan AS chandoan_chinh,
|
||
|
|
nt.kqcdoanp AS chandoan_phu,
|
||
|
|
COALESCE(cls_sub.ketqua_cls, 'Chưa có kết quả') AS ketqua_cls,
|
||
|
|
COALESCE(gb.ghichu, nt.ghichu, '') AS ghichu
|
||
|
|
FROM current.bnnoitru nt
|
||
|
|
JOIN current.dmbenhnhan bn ON nt.mabn = bn.mabn
|
||
|
|
LEFT JOIN current.dmdonvi dv ON nt.madv = dv.madv
|
||
|
|
LEFT JOIN current.giaoban_ghichu gb ON nt.maba = gb.maba AND gb.loai_danhsach = 'CHUYEN_VIEN'
|
||
|
|
LEFT JOIN LATERAL (
|
||
|
|
SELECT string_agg(DISTINCT cls.tencls || COALESCE(': ' || NULLIF(ha.ketluan,''), ''), '; ') AS ketqua_cls
|
||
|
|
FROM current.chidinhcls ls
|
||
|
|
JOIN current.dmcls cls ON ls.macls = cls.macls
|
||
|
|
LEFT JOIN current.pskhamha ha ON ls.mabn = ha.mabn AND ls.macls = ha.macls AND ls.makb = ha.makb
|
||
|
|
WHERE ls.mabn = nt.mabn AND ls.makb = nt.makb AND COALESCE(ls.xoa, 0) = 0
|
||
|
|
) cls_sub ON true
|
||
|
|
${sqlWhere}
|
||
|
|
ORDER BY nt.ngayrv DESC
|
||
|
|
LIMIT 100;
|
||
|
|
`;
|
||
|
|
|
||
|
|
const result = await db.query(sql, params, dbName);
|
||
|
|
const cleanedRows = result.rows.map(row => ({
|
||
|
|
...row,
|
||
|
|
ketqua_cls: db.cleanRtf(row.ketqua_cls)
|
||
|
|
}));
|
||
|
|
|
||
|
|
res.json({ success: true, data: cleanedRows });
|
||
|
|
} catch (error) {
|
||
|
|
console.error('Error in /api/danh-sach-chuyen-vien:', error);
|
||
|
|
res.status(500).json({ success: false, message: error.message });
|
||
|
|
}
|
||
|
|
});
|
||
|
|
|
||
|
|
// 5. API Bảng 4: Danh sách Bệnh nhân Theo dõi & Chăm sóc Cấp I
|
||
|
|
router.get('/api/danh-sach-theo-doi', async (req, res) => {
|
||
|
|
try {
|
||
|
|
const { database, from, to, madv } = req.query;
|
||
|
|
const dbName = database || process.env.PG_DATABASE;
|
||
|
|
const fromDate = from || '2026-03-01 00:00:00';
|
||
|
|
const toDate = to || '2026-03-31 23:59:59';
|
||
|
|
|
||
|
|
await ensureTable(dbName);
|
||
|
|
|
||
|
|
let sqlWhere = `
|
||
|
|
WHERE nt.namvien = 1 AND COALESCE(nt.xoa, 0) = 0
|
||
|
|
AND (nt.ngayvv <= $2 AND (nt.ravien = 0 OR nt.ngayrv >= $1))
|
||
|
|
AND (
|
||
|
|
COALESCE(nt.benhnang, 0) = 1 OR
|
||
|
|
COALESCE(nt.mabatheodoi, '') != '' OR
|
||
|
|
nt.chamsoc ILIKE '%cấp I%' OR
|
||
|
|
nt.chamsoc ILIKE '%cấp 1%' OR
|
||
|
|
nt.chamsoc IN ('1', 'CS I', 'CSI') OR
|
||
|
|
nt.madv IN ('17', '29')
|
||
|
|
)
|
||
|
|
`;
|
||
|
|
const params = [fromDate, toDate];
|
||
|
|
if (madv && madv !== 'ALL') {
|
||
|
|
params.push(madv);
|
||
|
|
sqlWhere += ` AND nt.madv = $${params.length} `;
|
||
|
|
}
|
||
|
|
|
||
|
|
const sql = `
|
||
|
|
SELECT
|
||
|
|
nt.mabn,
|
||
|
|
nt.maba,
|
||
|
|
bn.holot || ' ' || bn.ten AS hoten,
|
||
|
|
bn.diachi,
|
||
|
|
to_char(nt.ngayvv, 'DD/MM/YYYY HH24:MI') AS ngayvv,
|
||
|
|
to_char(nt.ngayrv, 'DD/MM/YYYY HH24:MI') AS ngayrv,
|
||
|
|
COALESCE(dv.tendv, nt.madv) AS tendv,
|
||
|
|
nt.kqcdoan AS chandoan_chinh,
|
||
|
|
nt.kqcdoanp AS chandoan_phu,
|
||
|
|
COALESCE(nt.chamsoc, 'Theo dõi đặc biệt') AS chamsoc,
|
||
|
|
COALESCE(cls_sub.ketqua_cls, 'Chưa có kết quả') AS ketqua_cls,
|
||
|
|
COALESCE(gb.ghichu, nt.ghichu, '') AS ghichu
|
||
|
|
FROM current.bnnoitru nt
|
||
|
|
JOIN current.dmbenhnhan bn ON nt.mabn = bn.mabn
|
||
|
|
LEFT JOIN current.dmdonvi dv ON nt.madv = dv.madv
|
||
|
|
LEFT JOIN current.giaoban_ghichu gb ON nt.maba = gb.maba AND gb.loai_danhsach = 'THEO_DOI'
|
||
|
|
LEFT JOIN LATERAL (
|
||
|
|
SELECT string_agg(DISTINCT cls.tencls || COALESCE(': ' || NULLIF(ha.ketluan,''), ''), '; ') AS ketqua_cls
|
||
|
|
FROM current.chidinhcls ls
|
||
|
|
JOIN current.dmcls cls ON ls.macls = cls.macls
|
||
|
|
LEFT JOIN current.pskhamha ha ON ls.mabn = ha.mabn AND ls.macls = ha.macls AND ls.makb = ha.makb
|
||
|
|
WHERE ls.mabn = nt.mabn AND ls.makb = nt.makb AND COALESCE(ls.xoa, 0) = 0
|
||
|
|
) cls_sub ON true
|
||
|
|
${sqlWhere}
|
||
|
|
ORDER BY nt.ngayvv DESC
|
||
|
|
LIMIT 100;
|
||
|
|
`;
|
||
|
|
|
||
|
|
const result = await db.query(sql, params, dbName);
|
||
|
|
const cleanedRows = result.rows.map(row => ({
|
||
|
|
...row,
|
||
|
|
ketqua_cls: db.cleanRtf(row.ketqua_cls)
|
||
|
|
}));
|
||
|
|
|
||
|
|
res.json({ success: true, data: cleanedRows });
|
||
|
|
} catch (error) {
|
||
|
|
console.error('Error in /api/danh-sach-theo-doi:', error);
|
||
|
|
res.status(500).json({ success: false, message: error.message });
|
||
|
|
}
|
||
|
|
});
|
||
|
|
|
||
|
|
// 6. API Cập nhật Ghi chú trực tiếp từ lưới (Bảng 3 & Bảng 4)
|
||
|
|
router.post('/api/cap-nhat-ghi-chu', async (req, res) => {
|
||
|
|
try {
|
||
|
|
const { maba, mabn, loai, ghichu, database } = req.body;
|
||
|
|
if (!maba || !loai) {
|
||
|
|
return res.status(400).json({ success: false, message: 'Thiếu maba hoặc loại danh sách' });
|
||
|
|
}
|
||
|
|
const dbName = database || process.env.PG_DATABASE;
|
||
|
|
await ensureTable(dbName);
|
||
|
|
|
||
|
|
const sqlUpsert = `
|
||
|
|
INSERT INTO current.giaoban_ghichu (maba, mabn, loai_danhsach, ghichu, ngay_capnhat)
|
||
|
|
VALUES ($1, $2, $3, $4, NOW())
|
||
|
|
ON CONFLICT (maba, loai_danhsach)
|
||
|
|
DO UPDATE SET ghichu = EXCLUDED.ghichu, ngay_capnhat = NOW();
|
||
|
|
`;
|
||
|
|
await db.query(sqlUpsert, [maba, mabn || '', loai, ghichu || ''], dbName);
|
||
|
|
|
||
|
|
// Đồng bộ vào bnnoitru.ghichu nếu cần
|
||
|
|
try {
|
||
|
|
await db.query(`UPDATE current.bnnoitru SET ghichu = $1 WHERE maba = $2;`, [ghichu || '', maba], dbName);
|
||
|
|
} catch (e) {
|
||
|
|
// ignore
|
||
|
|
}
|
||
|
|
|
||
|
|
res.json({ success: true, message: 'Đã lưu ghi chú thành công!' });
|
||
|
|
} catch (error) {
|
||
|
|
console.error('Error in /api/cap-nhat-ghi-chu:', error);
|
||
|
|
res.status(500).json({ success: false, message: error.message });
|
||
|
|
}
|
||
|
|
});
|
||
|
|
|
||
|
|
// 7. API Quản lý Issue & Báo lỗi (lưu file và tổng hợp)
|
||
|
|
router.get('/api/issues', (req, res) => {
|
||
|
|
try {
|
||
|
|
const list = issueService.getAllIssues();
|
||
|
|
res.json({ success: true, data: list });
|
||
|
|
} catch (error) {
|
||
|
|
res.status(500).json({ success: false, message: error.message });
|
||
|
|
}
|
||
|
|
});
|
||
|
|
|
||
|
|
router.post('/api/issues', upload.single('attachment'), (req, res) => {
|
||
|
|
try {
|
||
|
|
const issue = issueService.createIssue(req.body, req.file);
|
||
|
|
res.json({ success: true, data: issue, message: 'Đã ghi nhận báo lỗi thành công!' });
|
||
|
|
} catch (error) {
|
||
|
|
console.error('Error creating issue:', error);
|
||
|
|
res.status(500).json({ success: false, message: error.message });
|
||
|
|
}
|
||
|
|
});
|
||
|
|
|
||
|
|
router.put('/api/issues/:id', (req, res) => {
|
||
|
|
try {
|
||
|
|
const updated = issueService.updateIssue(req.params.id, req.body);
|
||
|
|
res.json({ success: true, data: updated, message: 'Cập nhật trạng thái issue thành công!' });
|
||
|
|
} catch (error) {
|
||
|
|
console.error('Error updating issue:', error);
|
||
|
|
res.status(500).json({ success: false, message: error.message });
|
||
|
|
}
|
||
|
|
});
|
||
|
|
|
||
|
|
router.delete('/api/issues/:id', (req, res) => {
|
||
|
|
try {
|
||
|
|
issueService.deleteIssue(req.params.id);
|
||
|
|
res.json({ success: true, message: 'Đã xóa issue thành công!' });
|
||
|
|
} catch (error) {
|
||
|
|
console.error('Error deleting issue:', error);
|
||
|
|
res.status(500).json({ success: false, message: error.message });
|
||
|
|
}
|
||
|
|
});
|
||
|
|
|
||
|
|
// Mount router on both /web-giao-ban and /
|
||
|
|
app.use('/web-giao-ban', router);
|
||
|
|
app.use('/', router);
|
||
|
|
|
||
|
|
app.listen(PORT, '0.0.0.0', () => {
|
||
|
|
console.log(`=======================================================`);
|
||
|
|
console.log(`[DH-HIS] Phần mềm Quản lý Giao ban hằng ngày (Node.js)`);
|
||
|
|
console.log(`Server is running at: http://localhost:${PORT}`);
|
||
|
|
console.log(`=======================================================`);
|
||
|
|
});
|