/* ============================================================================
   Report Name : รายงานสถิติความสัมพันธ์ผลตรวจ AFB และ TB-LAMP (Summary Matrix)
   View Reference : dbo.view_lab_statistic_Result_List
   ============================================================================ */
WITH CTE_AFB AS (
    SELECT 
        HN,
        LN AS AFB_LN,
        [RES ITEM CODE] AS AFB_Code,
        LTRIM(RTRIM([RES ITEM RESULT])) AS AFB_Result,
        [REQ ITEM CHECKIN DATETIME] AS AFB_Date,
        ROW_NUMBER() OVER (PARTITION BY HN ORDER BY [REQ ITEM CHECKIN DATETIME] DESC) AS rn
    FROM dbo.view_lab_statistic_Result_List
    WHERE [RES ITEM CODE] LIKE '%MBB00%'
      AND LTRIM(RTRIM([RES ITEM RESULT])) IN ('Non seen', 'Positive AFB 1+', 'Positive AFB 2+', 'Positive AFB 3+')
      AND [REQ ITEM CHECKIN DATETIME] BETWEEN '2025-10-01 00:00:00' AND '2026-09-30 23:59:59'
      AND ISNULL([ORDER INACTIVE], 'N') = 'N'
      AND ISNULL([RES ITEM INACTIVE], 'N') = 'N'
),
CTE_LAMP AS (
    SELECT 
        HN,
        LN AS LAMP_LN,
        [RES ITEM CODE] AS LAMP_Code,
        LTRIM(RTRIM([RES ITEM RESULT])) AS LAMP_Result,
        [REQ ITEM CHECKIN DATETIME] AS LAMP_Date,
        ROW_NUMBER() OVER (PARTITION BY HN ORDER BY [REQ ITEM CHECKIN DATETIME] DESC) AS rn
    FROM dbo.view_lab_statistic_Result_List
    WHERE [RES ITEM CODE] = 'MB1036'
      AND LTRIM(RTRIM([RES ITEM RESULT])) IN ('Detected', 'Not detected')
      AND [REQ ITEM CHECKIN DATETIME] BETWEEN '2026-06-12 00:00:00' AND '2026-09-30 23:59:59'
      AND ISNULL([ORDER INACTIVE], 'N') = 'N'
      AND ISNULL([RES ITEM INACTIVE], 'N') = 'N'
),
Matched_Patients AS (
    /* จับคู่ผู้ป่วย (HN) ที่มีผลตรวจทั้ง 2 รายการ โดยยึดผลตรวจครั้งล่าสุดของแต่ละรายการ */
    SELECT 
        A.HN,
        A.AFB_Result,
        L.LAMP_Result
    FROM CTE_AFB A
    INNER JOIN CTE_LAMP L ON A.HN = L.HN
    WHERE A.rn = 1 AND L.rn = 1
)
SELECT 
    AFB_Result AS [ผลตรวจ AFB (MBB00)],
    SUM(CASE WHEN LAMP_Result = 'Detected' THEN 1 ELSE 0 END) AS [LAMP: Detected (ราย)],
    SUM(CASE WHEN LAMP_Result = 'Not detected' THEN 1 ELSE 0 END) AS [LAMP: Not detected (ราย)],
    COUNT(*) AS [รวมทั้งหมด (ราย)],
    CAST(ROUND(
        SUM(CASE WHEN LAMP_Result = 'Detected' THEN 1.0 ELSE 0.0 END) 
        / NULLIF(COUNT(*), 0) * 100, 2
    ) AS DECIMAL(5,2)) AS [ร้อยละที่ตรวจพบ (% Detected)]
FROM Matched_Patients
GROUP BY 
    AFB_Result,
    CASE AFB_Result
        WHEN 'Non seen' THEN 1
        WHEN 'Positive AFB 1+' THEN 2
        WHEN 'Positive AFB 2+' THEN 3
        WHEN 'Positive AFB 3+' THEN 4
        ELSE 5
    END
ORDER BY 
    CASE AFB_Result
        WHEN 'Non seen' THEN 1
        WHEN 'Positive AFB 1+' THEN 2
        WHEN 'Positive AFB 2+' THEN 3
        WHEN 'Positive AFB 3+' THEN 4
        ELSE 5
    END;