Untitled

create OR REPLACE FUNCTION SDH_HOC_PHAN_GET_STUDENT(maHocPhan IN STRING, filter IN STRING) RETURN SYS_REFCURSOR
AS
    my_cursor     SYS_REFCURSOR;
    ks_mssv       STRING(50);
    ks_ho         STRING(50);
    ks_ten        STRING(50);
    ks_lop        STRING(50);
    ks_tenNganh   STRING(50);
    ks_khoaDaoTao STRING(50);
    ks_tinhPhi    STRING(50);
BEGIN
    SELECT JSON_VALUE(filter, '$.ks_mssv') INTO ks_mssv FROM DUAL;
    SELECT JSON_VALUE(filter, '$.ks_ho') INTO ks_ho FROM DUAL;
    SELECT JSON_VALUE(filter, '$.ks_ten') INTO ks_ten FROM DUAL;
    SELECT JSON_VALUE(filter, '$.ks_lop') INTO ks_lop FROM DUAL;
    SELECT JSON_VALUE(filter, '$.ks_tenNganh') INTO ks_tenNganh FROM DUAL;
    SELECT JSON_VALUE(filter, '$.ks_khoaDaoTao') INTO ks_khoaDaoTao FROM DUAL;
    SELECT JSON_VALUE(filter, '$.ks_tinhPhi') INTO ks_tinhPhi FROM DUAL;

    OPEN my_cursor FOR
        WITH DIEM_TONGKET AS (SELECT DKHP.MSSV,
                                     DKHP.MA_HOC_PHAN,
                                     SDH_DIEM.DIEM AS DIEM_TONG_KET,
                                     SDH_DIEM.ID   AS ID_DIEM_TONG_KET
                              FROM SDH_DIEM
                                       INNER JOIN SDH_DANG_KY_HOC_PHAN DKHP
                                                  ON SDH_DIEM.MSSV = DKHP.MSSV AND SDH_DIEM.MA_HOC_PHAN = DKHP.MA_HOC_PHAN
                              WHERE SDH_DIEM.LOAI_DIEM = 'TK'
                                AND SDH_DIEM.PHAN_TRAM_DIEM IS NULL),
             DIEM_HISTORY AS (SELECT SDH_DIEM_HISTORY.LOAI_DIEM,
                                     SDH_DIEM_HISTORY.MA_HOC_PHAN,
                                     SDH_DIEM_HISTORY.MSSV,
                                     SDH_DIEM_HISTORY.USER_MODIFIED,
                                     SDH_DIEM_HISTORY.TIME_MODIFIED,
                                     ROW_NUMBER() OVER (PARTITION BY SDH_DIEM_HISTORY.MSSV, SDH_DIEM_HISTORY.MA_HOC_PHAN, SDH_DIEM_HISTORY.LOAI_DIEM
                                         ORDER BY SDH_DIEM_HISTORY.ID DESC) AS RN
                              FROM SDH_DIEM_HISTORY)
        SELECT *
        FROM (SELECT DISTINCT SV.MSSV                       AS  "mssv",
                              SV.HO                         AS  "ho",
                              SV.TEN                        AS  "ten",
                              SV.LOP                        AS  "lop",
                              DM_NGANH_SAU_DAI_HOC.TEN      AS  "tenNganh",
                              SDH_KHOA_DAO_TAO.KHOA_DAO_TAO AS  "khoaDaoTao",
--                               TC_SDH_HOC_PHI.CONG_NO        AS  "tinhPhi",
                              DKHP.MA_HOC_PHAN              AS  "maHocPhan",
                              DTK.DIEM_TONG_KET             AS  "tongKet",
                              DTK.ID_DIEM_TONG_KET          AS  "idDiemTongKet",
                              DKHP.GHI_CHU_DIEM             AS  "ghiChuDiem",

                              JSON_ARRAYAGG(
                                      JSON_OBJECT(
                                              KEY TO_CHAR('idDiem') VALUE SDH_DIEM.ID,
                                              key TO_CHAR('maLoaiDiem') VALUE CONVERT(CHP.MA_LOAI_DIEM, 'utf8'),
                                              key TO_CHAR('loaiLamTron') VALUE CONVERT(LD.LOAI_LAM_TRON, 'utf8'),
                                              key TO_CHAR('tenLoaiDiem') VALUE CONVERT(LD.TEN, 'utf8'),
                                              key TO_CHAR('diem') VALUE CONVERT(SDH_DIEM.DIEM, 'utf8'),
                                              key TO_CHAR('diemDacBiet') VALUE CONVERT(SDH_DIEM.DIEM_DAC_BIET, 'utf8'),
                                              key ('phanTram') VALUE CONVERT(CHP.PHAN_TRAM, 'utf8')
                                          , key TO_CHAR('timeModified') VALUE
                                              CONVERT(DIEM_HISTORY.TIME_MODIFIED, 'utf8'),
                                              key TO_CHAR('userModified') VALUE
                                              CONVERT(DIEM_HISTORY.USER_MODIFIED, 'utf8')
                                      )
                              )                             AS  "diemThanhPhan",
                              ROW_NUMBER() OVER (ORDER BY NULL) R
              FROM SDH_DANG_KY_HOC_PHAN DKHP
                       LEFT JOIN FW_SINH_VIEN_SDH SV ON SV.MSSV = DKHP.MSSV
                       LEFT JOIN SDH_LOP_HOC_VIEN LOPHV ON LOPHV.MA = SV.LOP
                       LEFT JOIN SDH_KHOA_DAO_TAO ON LOPHV.KHOA_SINH_VIEN = SDH_KHOA_DAO_TAO.MA_KHOA
                       LEFT JOIN SDH_THOI_KHOA_BIEU TKB ON TKB.MA_HOC_PHAN = DKHP.MA_HOC_PHAN
                  --                        LEFT JOIN TC_SDH_HOC_PHI ON TC_SDH_HOC_PHI.MSSV = DKHP.MSSV AND
--                                                    TC_SDH_HOC_PHI.MA_HOC_PHAN = DKHP.MA_HOC_PHAN
                       LEFT JOIN DM_NGANH_SAU_DAI_HOC ON SV.MA_NGANH = DM_NGANH_SAU_DAI_HOC.MA_NGANH
                       LEFT JOIN SDH_CONFIG_HOC_PHAN CHP ON CHP.MA_HOC_PHAN = DKHP.MA_HOC_PHAN
                       LEFT JOIN SDH_DM_LOAI_DIEM LD ON LD.MA = CHP.MA_LOAI_DIEM
                       LEFT JOIN SDH_DIEM ON (SDH_DIEM.MA_HOC_PHAN = CHP.MA_HOC_PHAN AND
                                              SDH_DIEM.LOAI_DIEM = CHP.MA_LOAI_DIEM AND
                                              SDH_DIEM.MSSV = DKHP.MSSV)
                       LEFT JOIN DIEM_TONGKET DTK ON DTK.MSSV = DKHP.MSSV AND DTK.MA_HOC_PHAN = DKHP.MA_HOC_PHAN
                       LEFT JOIN DIEM_HISTORY ON DIEM_HISTORY.MSSV = SDH_DIEM.MSSV AND
                                                 DIEM_HISTORY.MA_HOC_PHAN = SDH_DIEM.MA_HOC_PHAN AND
                                                 DIEM_HISTORY.LOAI_DIEM = SDH_DIEM.LOAI_DIEM AND
                                                 DIEM_HISTORY.RN = 1
              WHERE DKHP.MA_HOC_PHAN = maHocPhan
                AND ((ks_mssv IS NULL OR lower(SV.MSSV) LIKE ('%' || lower(ks_mssv) || '%'))
                  AND (ks_ho IS NULL OR lower(SV.HO) LIKE ('%' || lower(ks_ho) || '%'))
                  AND (ks_ten IS NULL OR lower(SV.TEN) LIKE ('%' || lower(ks_ten) || '%'))
                  AND (ks_lop IS NULL OR lower(SV.LOP) LIKE ('%' || lower(ks_lop) || '%'))
                  AND (ks_tenNganh IS NULL OR lower(DM_NGANH_SAU_DAI_HOC.TEN) LIKE ('%' || lower(ks_tenNganh) || '%'))
                  AND (ks_khoaDaoTao IS NULL OR
                       lower(SDH_KHOA_DAO_TAO.KHOA_DAO_TAO) LIKE ('%' || lower(ks_khoaDaoTao) || '%')))
--                   AND (ks_tinhPhi IS NULL OR TO_NUMBER(ks_tinhPhi) = TC_SDH_HOC_PHI.CONG_NO))
              GROUP BY SV.MSSV, SV.HO, SV.TEN, SV.LOP, DM_NGANH_SAU_DAI_HOC.TEN, SDH_KHOA_DAO_TAO.KHOA_DAO_TAO,
                       DKHP.MA_HOC_PHAN, DTK.DIEM_TONG_KET, DTK.ID_DIEM_TONG_KET, TKB.IS_KHOA, DKHP.GHI_CHU_DIEM);
--         TC_SDH_HOC_PHI.CONG_NO
    return my_cursor;
end ;
/