Untitled
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