Untitled

create FUNCTION SDH_THOI_KHOA_BIEU_CUSTOM_CHECK_GHEP_LOP(filter IN STRING) RETURN SYS_REFCURSOR
AS
    my_cursor       SYS_REFCURSOR;
    i_phong         STRING(100);
    idTuan          STRING(100);
    thoiGianBatDau  STRING(100);
    thoiGianKetThuc STRING(100);
    giangVien       STRING(100);
    namHoc          STRING(100);
    hocKy           STRING(100);
BEGIN
    SELECT JSON_VALUE(filter, '$.phong') INTO i_phong FROM DUAL;
    SELECT JSON_VALUE(filter, '$.thoiGianBatDau') INTO thoiGianBatDau FROM DUAL;
    SELECT JSON_VALUE(filter, '$.thoiGianKetThuc') INTO thoiGianKetThuc FROM DUAL;
    SELECT JSON_VALUE(filter, '$.idTuan') INTO idTuan FROM DUAL;
    SELECT JSON_VALUE(filter, '$.giangVien') INTO giangVien FROM DUAL;
    SELECT JSON_VALUE(filter, '$.namHoc') INTO namHoc FROM DUAL;
    SELECT JSON_VALUE(filter, '$.hocKy') INTO hocKy FROM DUAL;


    OPEN my_cursor FOR
        WITH TCCB AS (SELECT CBNT.SHCC                              AS SHCC,
                             CBNT.HO                                AS HO,
                             CBNT.TEN                               AS TEN,
                             CAST(DV.MA AS NVARCHAR2(20))           AS MA_DON_VI,
                             HOC_HAM.VIET_TAT || ' ' || TD.VIET_TAT AS TRINH_DO,
                             'Thỉnh giảng'                          AS "type",
                             CBNT.EMAIL                             AS "email"
                      FROM DT_CAN_BO_NGOAI_TRUONG CBNT
                               LEFT JOIN DM_DON_VI DV
                                         on CBNT.DON_VI = DV.MA
                               LEFT JOIN DM_TRINH_DO TD ON TD.MA = CBNT.TRINH_DO
                               LEFT JOIN DM_CHUC_DANH_KHOA_HOC HOC_HAM ON CBNT.HOC_HAM = HOC_HAM.MA
                      UNION
                      SELECT CB.SHCC                                AS SHCC,
                             CB.HO                                  AS HO,
                             CB.TEN                                 AS TEN,
                             CAST(DV.MA AS NVARCHAR2(20))           AS MA_DON_VI,
                             HOC_HAM.VIET_TAT || ' ' || TD.VIET_TAT AS TRINH_DO,
                             'Trong trường'                         AS "type",
                             CB.EMAIL                               AS "email"
                      FROM TCHC_CAN_BO CB
                               LEFT JOIN DM_NGACH_CDNN NGACH on CB.NGACH = NGACH.MA
                               LEFT JOIN DM_DON_VI DV on CB.MA_DON_VI = DV.MA
                               LEFT JOIN DM_TRINH_DO TD ON TD.MA = CB.HOC_VI
                               LEFT JOIN DM_CHUC_DANH_KHOA_HOC HOC_HAM ON CB.CHUC_DANH = HOC_HAM.MA
                      WHERE CB.TEN IS NOT NULL
                      UNION
                      SELECT CAST(SDHCB.ID AS NVARCHAR2(20))              AS SHCC,
                             SDHCB.HO                                     AS HO,
                             SDHCB.TEN                                    AS TEN,
                             u'SDH'                                       AS MA_DON_VI,
                             HOC_HAM.VIET_TAT || ' ' || TRINH_DO.VIET_TAT AS TRINH_DO,
                             'SDH'                                        AS "type",
                             SDHCB.EMAIL_TRUONG                           AS "email"
                      FROM SDH_CAN_BO SDHCB
                               LEFT JOIN DM_CHUC_DANH_KHOA_HOC HOC_HAM ON SDHCB.HOC_HAM = HOC_HAM.MA
                               LEFT JOIN DM_TRINH_DO TRINH_DO ON TRINH_DO.MA = SDHCB.TRINH_DO
                      ORDER BY MA_DON_VI nulls last)

        SELECT CUSTOM.ID          AS "idThoiKhoaBieu",
               CUSTOM.MA_HOC_PHAN AS "maHocPhan",
               CUSTOM.MA_MON_HOC  AS "maMonHoc",
               MH.TEN_TIENG_VIET  AS "tenMonHoc",
               JSON_ARRAYAGG(
                       JSON_OBJECT(
                               KEY TO_CHAR('shcc') VALUE TO_CHAR(TCCB.SHCC),
                               KEY TO_CHAR('hoTen') VALUE TO_CHAR(TCCB.HO || ' ' || TCCB.TEN),
                               KEY TO_CHAR('loaiGV') VALUE TO_CHAR(TKB_GV.TYPE),
                               KEY TO_CHAR('trinhDo') VALUE TO_CHAR(TCCB.TRINH_DO),
                               KEY TO_CHAR('email') VALUE TO_CHAR(TCCB."email")
                       ))         AS "giangVien"
        FROM SDH_THOI_KHOA_BIEU_CUSTOM CUSTOM
                 LEFT JOIN SDH_THOI_KHOA_BIEU_GIANG_VIEN TKB_GV ON CUSTOM.ID = TKB_GV.ID_THOI_KHOA_BIEU
                 LEFT JOIN TCCB ON TKB_GV.GIANG_VIEN = TCCB.SHCC
                 LEFT JOIN SDH_DM_MON_HOC_MOI MH ON MH.MA = CUSTOM.MA_MON_HOC
        WHERE CUSTOM.PHONG = i_phong
          AND CUSTOM.THOI_GIAN_BAT_DAU = thoiGianBatDau
          AND CUSTOM.THOI_GIAN_KET_THUC = thoiGianKetThuc
          AND idTuan != CUSTOM.ID
          AND TKB_GV.GIANG_VIEN = giangVien
          AND CUSTOM.NAM_HOC = namHoc
          AND CUSTOM.HOC_KY = hocKy
        GROUP BY CUSTOM.ID, CUSTOM.MA_HOC_PHAN, CUSTOM.MA_MON_HOC, MH.TEN_TIENG_VIET;

    RETURN my_cursor;
END;
/