Untitled
unknown
plain_text
5 months ago
14 kB
3
Indexable
create FUNCTION DT_THOI_KHOA_BIEU_CUSTOM_NGHI_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;
fromTime STRING(50);
toTime STRING(50);
loaiHinhDaoTaoFilter STRING(50);
khoaSinhVienFilter STRING(50);
donViFilter STRING(50);
hocKy STRING(50);
namHoc STRING(50);
sortKey STRING(20);
sortMode STRING(20);
ks_maHocPhan STRING(50);
ks_tenMonHoc STRING(100);
ks_ngayNghi STRING(50);
ks_coSo STRING(20);
ks_phong STRING(50);
ks_thu STRING(50);
ks_tiet STRING(50);
ks_userMod STRING(50);
ks_timeMod NUMBER(20);
ks_giangVien STRING(100);
ks_troGiang STRING(100);
ks_ghiChu STRING(100);
listKhoaSinhVienFilter STRING(1000);
listHeFilter STRING(1000);
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, '$.loaiHinhDaoTaoFilter') INTO loaiHinhDaoTaoFilter FROM DUAL;
SELECT JSON_VALUE(filter, '$.khoaSinhVienFilter') INTO khoaSinhVienFilter FROM DUAL;
SELECT JSON_VALUE(filter, '$.donViFilter') INTO donViFilter FROM DUAL;
SELECT JSON_VALUE(filter, '$.listHeFilter') INTO listHeFilter FROM DUAL;
SELECT JSON_VALUE(filter, '$.listKhoaSinhVienFilter') INTO listKhoaSinhVienFilter FROM DUAL;
SELECT JSON_VALUE(filter, '$.sortKey') INTO sortKey FROM DUAL;
SELECT JSON_VALUE(filter, '$.sortMode') INTO sortMode FROM DUAL;
SELECT JSON_VALUE(filter, '$.ks_maHocPhanN') INTO ks_maHocPhan FROM DUAL;
SELECT JSON_VALUE(filter, '$.ks_tenMonHocN') INTO ks_tenMonHoc FROM DUAL;
SELECT JSON_VALUE(filter, '$.ks_coSoN') INTO ks_coSo FROM DUAL;
SELECT JSON_VALUE(filter, '$.ks_ngayNghiN') INTO ks_ngayNghi FROM DUAL;
SELECT JSON_VALUE(filter, '$.ks_phongN') INTO ks_phong FROM DUAL;
SELECT JSON_VALUE(filter, '$.ks_thuN') INTO ks_thu FROM DUAL;
SELECT JSON_VALUE(filter, '$.ks_tietN') INTO ks_tiet FROM DUAL;
SELECT JSON_VALUE(filter, '$.ks_userModN') INTO ks_userMod FROM DUAL;
SELECT JSON_VALUE(filter, '$.ks_timeModN') INTO ks_timeMod FROM DUAL;
SELECT JSON_VALUE(filter, '$.ks_giangVienN') INTO ks_giangVien FROM DUAL;
SELECT JSON_VALUE(filter, '$.ks_troGiangN') INTO ks_troGiang FROM DUAL;
SELECT JSON_VALUE(filter, '$.ks_ghiChuN') INTO ks_ghiChu FROM DUAL;
SELECT COUNT(*)
INTO totalItem
FROM DT_THOI_KHOA_BIEU_CUSTOM NGHI
WHERE NGHI.IS_NGHI = 1
AND NGHI.NGAY_HOC 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
ORDER BY "tenDonVi" nulls last)
SELECT *
FROM (SELECT NGHI.ID AS "id",
MH.TEN AS "tenMonHoc",
NGHI.MA_HOC_PHAN AS "maHocPhan",
NGHI.CO_SO AS "coSo",
NGHI.NGAY_HOC AS "ngayNghi",
NGHI.THU AS "thu",
NGHI.TIET_BAT_DAU AS "tietBatDau",
NGHI.SO_TIET_BUOI AS "soTietBuoi",
NGHI.PHONG AS "phong",
NGHI.GHI_CHU AS "ghiChu",
NGHI.THOI_GIAN_BAT_DAU AS "ngayBatDau",
NGHI.THOI_GIAN_KET_THUC AS "ngayKetThuc",
NGHI.IS_HOAN_TAC AS "isHoanTac",
REPLACE(NGHI.MODIFIER, '@hcmussh.edu.vn', '') AS "userModified",
NGHI.TIME_MODIFIED AS "timeModified",
GV.giangVien AS "giangVien",
GVI.troGiang AS "troGiang",
NGHI.IS_GIANG_VIEN_BAO_NGHI AS "isGiangVienBaoNghi",
ROW_NUMBER() OVER (ORDER BY NGHI.THU, NGHI.MA_HOC_PHAN) R
FROM DT_THOI_KHOA_BIEU_CUSTOM NGHI
LEFT JOIN DM_MON_HOC MH ON MH.MA = NGHI.MA_MON_HOC
LEFT JOIN (SELECT *
FROM (SELECT MA_HOC_PHAN,
KHOA_SINH_VIEN,
KHOA_DANG_KY,
LOAI_HINH_DAO_TAO,
ROW_NUMBER() over (PARTITION BY MA_HOC_PHAN ORDER BY ID) AS R
FROM DT_THOI_KHOA_BIEU)
WHERE R = 1) TKB ON TKB.MA_HOC_PHAN = NGHI.MA_HOC_PHAN
LEFT JOIN (SELECT GV.ID_TUAN,
LISTAGG(CAN_BO."trinhDo" || ' ' || CAN_BO."hoTen", ',')
WITHIN GROUP (ORDER BY NULL) AS giangVien
FROM DT_THOI_KHOA_BIEU_GIANG_VIEN GV
LEFT JOIN CAN_BO ON CAN_BO."shcc" = GV.GIANG_VIEN
WHERE GV.TYPE = 'GV'
AND NGAY_BAT_DAU IS NOT NULL
GROUP BY GV.ID_TUAN) GV
ON GV.ID_TUAN = NGHI.ID
LEFT JOIN (SELECT GV.ID_TUAN,
LISTAGG(CAN_BO."trinhDo" || ' ' || CAN_BO."hoTen", ',')
WITHIN GROUP (ORDER BY NULL) AS troGiang
FROM DT_THOI_KHOA_BIEU_GIANG_VIEN GV
LEFT JOIN CAN_BO ON CAN_BO."shcc" = GV.GIANG_VIEN
WHERE GV.TYPE = 'TG'
AND NGAY_BAT_DAU IS NOT NULL
GROUP BY GV.ID_TUAN) GVI
ON GVI.ID_TUAN = NGHI.ID
WHERE (NGHI.IS_NGHI = 1 OR NGHI.IS_HOAN_TAC = 1)
AND NGHI.NGAY_HOC BETWEEN fromTime AND toTime
AND (namHoc IS NULL OR NGHI.NAM_HOC = namHoc)
AND (hocKy IS NULL OR NGHI.HOC_KY = hocKy)
AND (loaiHinhDaoTaoFilter IS NULL OR TKB.LOAI_HINH_DAO_TAO = loaiHinhDaoTaoFilter)
AND (khoaSinhVienFilter IS NULL OR TKB.KHOA_SINH_VIEN = khoaSinhVienFilter)
AND (donViFilter IS NULL OR TKB.KHOA_DANG_KY = donViFilter)
AND (
(ks_maHocPhan IS NULL OR lower(NGHI.MA_HOC_PHAN) LIKE ('%' || lower(ks_maHocPhan) || '%'))
AND (ks_tenMonHoc IS NULL OR lower(MH.TEN) LIKE ('%' || lower(ks_tenMonHoc) || '%'))
AND (ks_ngayNghi IS NULL OR NGHI.NGAY_HOC = ks_ngayNghi)
AND (ks_phong IS NULL OR NGHI.PHONG = ks_phong)
AND (ks_coSo IS NULL OR NGHI.CO_SO = ks_coSo)
AND (ks_thu IS NULL OR NGHI.THU = ks_thu)
AND (ks_tiet IS NULL OR
ks_tiet BETWEEN NGHI.TIET_BAT_DAU AND (NGHI.SO_TIET_BUOI + NGHI.TIET_BAT_DAU - 1))
AND (ks_userMod IS NULL OR lower(NGHI.MODIFIER) LIKE ('%' || lower(ks_userMod) || '%'))
AND (ks_timeMod IS NULL OR NGHI.TIME_MODIFIED = ks_timeMod)
AND (ks_giangVien IS NULL OR INSTR(LOWER(GV.giangVien), LOWER(ks_giangVien)) > 0)
AND (ks_troGiang IS NULL OR INSTR(LOWER(GVI.troGiang), LOWER(ks_troGiang)) > 0)
AND (ks_ghiChu IS NULL OR lower(NGHI.GHI_CHU) LIKE ('%' || lower(ks_ghiChu) || '%'))
)
AND (listKhoaSinhVienFilter IS NULL OR
TKB.KHOA_SINH_VIEN IN (SELECT regexp_substr(listKhoaSinhVienFilter, '[^,]+', 1, level)
from dual
connect by regexp_substr(listKhoaSinhVienFilter, '[^,]+', 1, level) is not null))
AND (listHeFilter IS NULL OR
TKB.LOAI_HINH_DAO_TAO IN (SELECT regexp_substr(listHeFilter, '[^,]+', 1, level)
from dual
connect by regexp_substr(listHeFilter, '[^,]+', 1, level) is not null))
ORDER BY CASE
WHEN sortMode = 'ASC' THEN
DECODE(sortKey,
'maHocPhan', NLSSORT(NGHI.MA_HOC_PHAN, 'NLS_SORT = BINARY_AI'),
'tenMonHoc', NLSSORT(MH.TEN, 'NLS_SORT = VIETNAMESE'),
'phong', NLSSORT(NGHI.PHONG, 'NLS_SORT = BINARY_AI'),
'thu', NLSSORT(NGHI.THU, 'NLS_SORT = VIETNAMESE'),
'tiet', NLSSORT(NGHI.TIET_BAT_DAU, 'NLS_SORT = BINARY_AI'),
'ngayNghi', NLSSORT(NGHI.NGAY_HOC, 'NLS_SORT = BINARY_AI'),
'userMod', NLSSORT(NGHI.MODIFIER, 'NLS_SORT = BINARY_AI'),
'timeMod', NLSSORT(NGHI.TIME_MODIFIED, 'NLS_SORT = BINARY_AI'),
NLSSORT(NGHI.MA_HOC_PHAN, 'NLS_SORT = BINARY_AI'))
END ASC NULLS LAST,
CASE
WHEN sortMode = 'DESC' THEN
DECODE(sortKey,
'maHocPhan', NLSSORT(NGHI.MA_HOC_PHAN, 'NLS_SORT = BINARY_AI'),
'tenMonHoc', NLSSORT(MH.TEN, 'NLS_SORT = VIETNAMESE'),
'phong', NLSSORT(NGHI.PHONG, 'NLS_SORT = BINARY_AI'),
'thu', NLSSORT(NGHI.THU, 'NLS_SORT = VIETNAMESE'),
'tiet', NLSSORT(NGHI.TIET_BAT_DAU, 'NLS_SORT = BINARY_AI'),
'userMod', NLSSORT(NGHI.MODIFIER, 'NLS_SORT = BINARY_AI'),
'timeMod', NLSSORT(NGHI.TIME_MODIFIED, 'NLS_SORT = BINARY_AI'),
NLSSORT(NGHI.MA_HOC_PHAN, '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