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