Untitled

 avatar
unknown
plain_text
6 months ago
12 kB
2
Indexable
create FUNCTION SDH_THOI_KHOA_BIEU_CUSTOM_VANG_SEARCH_PAGE(pageNumber IN OUT NUMBER, pageSize IN OUT NUMBER,
                                                           filter IN STRING,
                                                           totalItem OUT NUMBER,
                                                           pageTotal OUT NUMBER) RETURN SYS_REFCURSOR
AS
    my_cursor       SYS_REFCURSOR;
    hocKy           STRING(50);
    namHoc          STRING(50);
    fromTime        STRING(50);
    toTime          STRING(50);
    sortKey         STRING(20);
    sortMode        STRING(20);
    bacDaoTaoFilter STRING(50);
    ks_maHocPhan    STRING(100);
    ks_tenMonHoc    STRING(500);
    ks_ngayVang     STRING(50);
    ks_tiet         STRING(50);
    ks_ghiChu       STRING(100);
    ks_userMod      STRING(50);
    ks_timeMod      NUMBER(20);
    ks_lop          STRING(500);
    ks_ngayHoc      STRING(100);
    ks_thu          STRING(50);
    ks_phong        STRING(100);
    ks_khoa         STRING(100);
    ks_giangVien    STRING(100);
    ks_troGiang     STRING(100);

BEGIN
    SELECT JSON_VALUE(filter, '$.hocKy') INTO hocKy FROM DUAL;
    SELECT JSON_VALUE(filter, '$.namHoc') INTO namHoc FROM DUAL;
    SELECT JSON_VALUE(filter, '$.fromTime') INTO fromTime FROM DUAL;
    SELECT JSON_VALUE(filter, '$.toTime') INTO toTime FROM DUAL;
    SELECT JSON_VALUE(filter, '$.sortKey') INTO sortKey FROM DUAL;
    SELECT JSON_VALUE(filter, '$.sortMode') INTO sortMode FROM DUAL;
    SELECT JSON_VALUE(filter, '$.loaiHinhDaoTao') INTO bacDaoTaoFilter FROM DUAL;
    SELECT JSON_VALUE(filter, '$.ks_maHocPhanV') INTO ks_maHocPhan FROM DUAL;
    SELECT JSON_VALUE(filter, '$.ks_tenMonHocV') INTO ks_tenMonHoc FROM DUAL;
    SELECT JSON_VALUE(filter, '$.ks_ngayVangV') INTO ks_ngayVang FROM DUAL;
    SELECT JSON_VALUE(filter, '$.ks_tietV') INTO ks_tiet FROM DUAL;
    SELECT JSON_VALUE(filter, '$.ks_ghiChuV') INTO ks_ghiChu FROM DUAL;
    SELECT JSON_VALUE(filter, '$.ks_userModV') INTO ks_userMod FROM DUAL;
    SELECT JSON_VALUE(filter, '$.ks_timeModV') INTO ks_timeMod FROM DUAL;
    SELECT JSON_VALUE(filter, '$.ks_lopV') INTO ks_lop FROM DUAL;
    SELECT JSON_VALUE(filter, '$.ks_ngayHocV') INTO ks_ngayHoc FROM DUAL;
    SELECT JSON_VALUE(filter, '$.ks_phongV') INTO ks_phong FROM DUAL;
    SELECT JSON_VALUE(filter, '$.ks_thuV') INTO ks_thu FROM DUAL;
    SELECT JSON_VALUE(filter, '$.ks_khoaV') INTO ks_khoa FROM DUAL;
    SELECT JSON_VALUE(filter, '$.ks_giangVienV') INTO ks_giangVien FROM DUAL;
    SELECT JSON_VALUE(filter, '$.ks_troGiangV') INTO ks_troGiang FROM DUAL;

    SELECT COUNT(*)
    INTO totalItem
    FROM SDH_THOI_KHOA_BIEU_CUSTOM VANG
    WHERE VANG.IS_VANG = 1
      AND VANG.THOI_GIAN_BAT_DAU BETWEEN fromTime AND toTime;

    IF pageNumber < 1 THEN pageNumber := 1; END IF;
    IF pageSize < 1 THEN pageSize := 1; END IF;
    pageTotal := CEIL(totalItem / pageSize);
    pageNumber := LEAST(pageNumber, pageTotal);

    OPEN my_cursor FOR
        WITH CAN_BO AS (SELECT CBNT.SHCC                                                      AS "shcc",
                               UPPER(CBNT.HO) || ' ' || UPPER(CBNT.TEN) || '(' || 'TG' || ')' AS "hoTen",
                               DV.TEN                                                         AS "tenDonVi",
                               HOC_HAM.VIET_TAT || ' ' || TD.VIET_TAT                         AS "trinhDo",
                               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",
                               UPPER(CB.HO) || ' ' || UPPER(CB.TEN)   AS "hoTen",
                               DV.TEN                                 AS "tenDonVi",
                               HOC_HAM.VIET_TAT || ' ' || TD.VIET_TAT AS "trinhDo",
                               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.NGAY_NGHI IS NULL
                        UNION
                        SELECT CAST(SDHCB.ID AS NVARCHAR2(20))                                   AS "shcc",
                               UPPER(SDHCB.HO) || ' ' || UPPER(SDHCB.TEN) || '(' || 'SDH' || ')' AS "hoTen",
                               u'Cán bộ Sau đại học'                                             AS "tenDonVi",
                               HOC_HAM.VIET_TAT || ' ' || TRINH_DO.VIET_TAT                      AS "trinhDo",
                               SDHCB.EMAIL                                                       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 "tenDonVi" nulls last),
             TKB_Giang_Vien AS (SELECT TKBGV.*,
                                       ROW_NUMBER() OVER (PARTITION BY TKBGV.ID_THOI_KHOA_BIEU ORDER BY TKBGV.GIANG_VIEN) AS RN
                                FROM SDH_THOI_KHOA_BIEU_GIANG_VIEN TKBGV)
        SELECT *
        FROM (SELECT VANG.ID                                        AS       "idTuan",
                     COALESCE(MH.TEN_TIENG_VIET, MH.TEN_TIENG_ANH, MHM.TEN_TIENG_VIET,
                              MHM.TEN_TIENG_ANH)                    AS       "tenMonHoc",
                     VANG.MA_HOC_PHAN                               AS       "maHocPhan",
                     VANG.CO_SO                                     AS       "coSo",
                     VANG.TIME_VANG                                 AS       "ngayVang",
                     VANG.THU                                       AS       "thu",
                     VANG.TIET_BAT_DAU                              AS       "tietBatDau",
                     VANG.SO_TIET_BUOI                              AS       "soTietBuoi",
                     VANG.PHONG                                     AS       "phong",
                     VANG.GHI_CHU                                   AS       "ghiChu",
                     VANG.THOI_GIAN_BAT_DAU                         AS       "ngayBatDau",
                     VANG.THOI_GIAN_KET_THUC                        AS       "ngayKetThuc",
                     REPLACE(VANG.USER_VANG, '@hcmussh.edu.vn', '') AS       "userVang",
                     GV."hoTen"                                     AS       "giangVien",
                     TG."hoTen"                                     AS       "troGiang",
                     ROW_NUMBER() OVER (ORDER BY VANG.THU, VANG.MA_HOC_PHAN) R
              FROM SDH_THOI_KHOA_BIEU_CUSTOM VANG
                       LEFT JOIN DM_MON_HOC_SDH MH ON MH.MA = VANG.MA_MON_HOC
                       LEFT JOIN SDH_DM_MON_HOC_MOI MHM ON MHM.MA = VANG.MA_MON_HOC
                       LEFT JOIN (SELECT *
                                  FROM (SELECT MA_HOC_PHAN,
                                               BAC_DAO_TAO,
                                               ROW_NUMBER() over (PARTITION BY MA_HOC_PHAN ORDER BY ID) AS R
                                        FROM SDH_THOI_KHOA_BIEU)
                                  WHERE R = 1) TKB ON TKB.MA_HOC_PHAN = VANG.MA_HOC_PHAN
                       LEFT JOIN TKB_Giang_Vien TKBGV ON TKBGV.ID_THOI_KHOA_BIEU = VANG.ID AND TKBGV.RN = 1
                       LEFT JOIN CAN_BO GV ON GV."shcc" = TKBGV.GIANG_VIEN AND TKBGV.TYPE = 'GV'
                       LEFT JOIN CAN_BO TG ON TG."shcc" = TKBGV.GIANG_VIEN AND TKBGV.TYPE = 'TG'
              WHERE (VANG.IS_VANG = 1)
                AND VANG.THOI_GIAN_BAT_DAU BETWEEN fromTime AND toTime
                AND (namHoc IS NULL OR VANG.NAM_HOC = namHoc)
                AND (hocKy IS NULL OR VANG.HOC_KY = hocKy)
                AND (bacDaoTaoFilter IS NULL OR TKB.BAC_DAO_TAO = bacDaoTaoFilter)
                AND (
                  (ks_maHocPhan IS NULL OR lower(VANG.MA_HOC_PHAN) LIKE ('%' || lower(ks_maHocPhan) || '%'))
                      AND (ks_tenMonHoc IS NULL OR lower(MH.TEN_TIENG_VIET) LIKE ('%' || lower(ks_tenMonHoc) || '%'))
                      AND (ks_ngayVang IS NULL OR VANG.TIME_VANG = ks_ngayVang)
                      AND (ks_phong IS NULL OR lower(VANG.PHONG) LIKE ('%' || lower(ks_phong) || '%'))
                      AND (ks_thu IS NULL OR VANG.THU = ks_thu)
                      AND (ks_tiet IS NULL OR
                           ks_tiet BETWEEN VANG.TIET_BAT_DAU AND (VANG.SO_TIET_BUOI + VANG.TIET_BAT_DAU - 1))
                      AND (ks_userMod IS NULL OR lower(VANG.MODIFIER) LIKE ('%' || lower(ks_userMod) || '%'))
                      AND (ks_timeMod IS NULL OR VANG.TIME_MODIFIED = ks_timeMod)
                      AND (ks_giangVien IS NULL OR INSTR(LOWER(GV."hoTen"), LOWER(ks_giangVien)) > 0)
                      AND (ks_troGiang IS NULL OR INSTR(LOWER(TG."hoTen"), LOWER(ks_troGiang)) > 0)
                      AND (ks_ghiChu IS NULL OR lower(VANG.GHI_CHU) LIKE ('%' || lower(ks_ghiChu) || '%'))
                  )
              ORDER BY CASE
                           WHEN sortMode = 'ASC' THEN
                               DECODE(sortKey,
                                      'maHocPhan', NLSSORT(VANG.MA_HOC_PHAN, 'NLS_SORT = BINARY_AI'),
                                      'tenMonHoc',
                                      NLSSORT(COALESCE(MH.TEN_TIENG_VIET, MH.TEN_TIENG_ANH, MHM.TEN_TIENG_VIET,
                                                       MHM.TEN_TIENG_ANH), 'NLS_SORT = VIETNAMESE'),
                                      'phong', NLSSORT(VANG.PHONG, 'NLS_SORT = BINARY_AI'),
                                      'thu', NLSSORT(VANG.THU, 'NLS_SORT = VIETNAMESE'),
                                      'tiet', NLSSORT(VANG.TIET_BAT_DAU, 'NLS_SORT = BINARY_AI'),
                                      'ngayVang', NLSSORT(VANG.TIME_VANG, 'NLS_SORT = BINARY_AI'),
                                      'userMod', NLSSORT(VANG.MODIFIER, 'NLS_SORT = BINARY_AI'),
                                      'timeMod', NLSSORT(VANG.TIME_MODIFIED, 'NLS_SORT = BINARY_AI'),
                                      NLSSORT(VANG.TIME_VANG, 'NLS_SORT = BINARY_AI'))
                           END ASC NULLS LAST,

                       CASE
                           WHEN sortMode = 'DESC' THEN
                               DECODE(sortKey,
                                      'maHocPhan', NLSSORT(VANG.MA_HOC_PHAN, 'NLS_SORT = BINARY_AI'),
                                      'tenMonHoc',
                                      NLSSORT(COALESCE(MH.TEN_TIENG_VIET, MH.TEN_TIENG_ANH, MHM.TEN_TIENG_VIET,
                                                       MHM.TEN_TIENG_ANH), 'NLS_SORT = VIETNAMESE'),
                                      'phong', NLSSORT(VANG.PHONG, 'NLS_SORT = BINARY_AI'),
                                      'thu', NLSSORT(VANG.THU, 'NLS_SORT = VIETNAMESE'),
                                      'tiet', NLSSORT(VANG.TIET_BAT_DAU, 'NLS_SORT = BINARY_AI'),
                                      'ngayVang', NLSSORT(VANG.TIME_VANG, 'NLS_SORT = BINARY_AI'),
                                      'userMod', NLSSORT(VANG.MODIFIER, 'NLS_SORT = BINARY_AI'),
                                      'timeMod', NLSSORT(VANG.TIME_MODIFIED, 'NLS_SORT = BINARY_AI'),
                                      NLSSORT(VANG.TIME_VANG, 'NLS_SORT = BINARY_AI'))
                           END DESC NULLS LAST)
        WHERE R BETWEEN (pageNumber - 1) * pageSize + 1 AND pageNumber * pageSize;

    RETURN my_cursor;
END ;
/

Editor is loading...
Leave a Comment