query ข้อมูลรายวัน

SELECT
CONCAT(fname,'  ',lname),
    o.vn,
    o.hn,
    o.vstdate
FROM
    ovst o
LEFT JOIN
    visit_pttype au ON o.vn = au.vn
INNER JOIN
  patient p ON p.hn = o.hn
WHERE
    o.vstdate = CURDATE()
    AND au.auth_code IS NULL

SELECT
    o.vn,
    o.hn,
    CONCAT(p.pname, ' ', p.fname, ' ', p.lname) AS pt_name, -- เพิ่มชื่อ-สกุล
    o.vstdate
FROM
    ovst o
INNER JOIN
    patient p ON o.hn = p.hn
LEFT JOIN
    opdscreen ops ON o.vn = ops.vn
WHERE
    o.vstdate = CURDATE()
    AND (
        -- ตรวจสอบว่า ops.vn เป็น NULL (ไม่มีข้อมูล opdscreen) หรือ vital sign เป็น 0/NULL
        ops.vn IS NULL OR
        ops.bps = 0 OR ops.bps IS NULL OR
        ops.bpd = 0 OR ops.bpd IS NULL OR
        ops.pulse = 0 OR ops.pulse IS NULL OR
        ops.rr = 0 OR ops.rr IS NULL OR
        ops.temperature = 0 OR ops.temperature IS NULL OR
        ops.cc IS NULL OR ops.cc = '' OR
        ops.hpi IS NULL OR ops.hpi = ''
    )

SET @today = CURDATE();
 
SELECT count(*)
--     CONCAT(p.pname, ' ', p.fname, ' ', p.lname) AS ptname,
--     v.*,
--     o.visit_type
FROM
    vn_stat v
LEFT OUTER JOIN
    patient p ON p.hn = v.hn
LEFT OUTER JOIN
    ovst o ON o.vn = v.vn
WHERE
    v.vstdate = @today
    AND v.age_y >= 0
    AND v.age_y <= 200
    AND UPPER(o.visit_type) = 'I';
ORDER BY v.vsdate

SET @today = CURDATE();
-- SET @startdate = '2024-10-01';
-- SET @enddate = '2025-09-30';
 
SELECT
    i.an,
    i.hn,
    CONCAT(p.pname, ' ', p.fname, ' ', p.lname) AS PtName,
    pt.name AS PtType,
    CONCAT(i.dchdate, ' ', i.dchtime) AS DchDate,
    w.name AS Ward,
    sp.name AS PtSpec,
    d.name AS AdmDoctor,
    i.prediag AS Pdx,
    ou.name AS DchUser
FROM
    ipt i
LEFT OUTER JOIN
    patient p ON p.hn = i.hn
LEFT OUTER JOIN
    ward w ON w.ward = i.ward
LEFT OUTER JOIN
    doctor d ON d.code = i.admdoctor
LEFT OUTER JOIN
    opduser ou ON ou.loginname = i.dch_doctor
LEFT OUTER JOIN
    pttype pt ON pt.pttype = i.pttype
LEFT OUTER JOIN
    spclty sp ON sp.spclty = i.spclty
WHERE
    i.dchdate = @today
-- i.dchdate BETWEEN @startdate and @enddate