Untitled
user_4058768
plain_text
4 months ago
7.1 kB
2
Indexable
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 ; /
Editor is loading...
Leave a Comment