Untitled
unknown
plain_text
5 months ago
12 kB
2
Indexable
create FUNCTION SDH_THOI_KHOA_BIEU_SEARCH_ALL_LICH(pageNumber IN OUT NUMBER, pageSize IN OUT NUMBER,
filter IN STRING,
totalItem OUT NUMBER,
pageTotal OUT NUMBER) RETURN SYS_REFCURSOR
AS
data SYS_REFCURSOR;
fromTime NUMBER(20);
toTime NUMBER(20);
status STRING(5);
ks_maHocPhan STRING(100);
ks_tenMon STRING(500);
ks_lop STRING(500);
ks_ngayHoc STRING(100);
ks_thu STRING(50);
ks_time STRING(100);
ks_phong STRING(100);
ks_coSo STRING(50);
ks_khoa STRING(100);
ks_giangVien STRING(100);
ks_troGiang STRING(100);
sortKey STRING(20);
sortMode STRING(20);
ks_tenCoSo STRING(200);
BEGIN
SELECT JSON_VALUE(filter, '$.fromTime') INTO fromTime FROM DUAL;
SELECT JSON_VALUE(filter, '$.toTime') INTO toTime FROM DUAL;
SELECT JSON_VALUE(filter, '$.status') INTO status FROM DUAL;
SELECT JSON_VALUE(filter, '$.ks_maHocPhan') INTO ks_maHocPhan FROM DUAL;
SELECT JSON_VALUE(filter, '$.ks_tenMon') INTO ks_tenMon FROM DUAL;
SELECT JSON_VALUE(filter, '$.ks_lop') INTO ks_lop FROM DUAL;
SELECT JSON_VALUE(filter, '$.ks_ngayHoc') INTO ks_ngayHoc FROM DUAL;
SELECT JSON_VALUE(filter, '$.ks_thu') INTO ks_thu FROM DUAL;
SELECT JSON_VALUE(filter, '$.ks_time') INTO ks_time FROM DUAL;
SELECT JSON_VALUE(filter, '$.ks_phong') INTO ks_phong FROM DUAL;
SELECT JSON_VALUE(filter, '$.ks_coSo') INTO ks_coSo FROM DUAL;
SELECT JSON_VALUE(filter, '$.ks_khoa') INTO ks_khoa FROM DUAL;
SELECT JSON_VALUE(filter, '$.ks_giangVien') INTO ks_giangVien FROM DUAL;
SELECT JSON_VALUE(filter, '$.ks_troGiang') INTO ks_troGiang FROM DUAL;
SELECT JSON_VALUE(filter, '$.ks_tenCoSo') INTO ks_tenCoSo FROM DUAL;
SELECT JSON_VALUE(filter, '$.sortKey') INTO sortKey FROM DUAL;
SELECT JSON_VALUE(filter, '$.sortMode') INTO sortMode FROM DUAL;
SELECT COUNT(*)
INTO totalItem
FROM SDH_THOI_KHOA_BIEU_CUSTOM TKB
WHERE TKB.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 data 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 TKB.MA_HOC_PHAN AS "maHocPhan",
DMMH.TEN_TIENG_VIET AS "tenMonHoc",
TKB.THOI_GIAN_BAT_DAU AS "ngayHoc",
TKB.THU AS "thu",
TKB.TIET_BAT_DAU AS "tietBatDau",
TKB.SO_TIET_BUOI AS "soTietBuoi",
TKB.PHONG AS "phong",
COALESCE(P.CO_SO, TKB.CO_SO) AS "coSo",
CS.TEN AS "tenCoSo",
DV1.TEN AS "tenKhoaDangKy",
TKB.MA_MON_HOC AS "maMonHoc",
TO_CHAR(GV."hoTen") AS "dataTenGiangVien",
TO_CHAR(TG."hoTen") AS "dataTenTroGiang",
TO_CHAR(GV."email") AS "emailGiangVien",
CH_BAT_DAU.THOI_GIAN_BAT_DAU AS "tgBatDau",
CH_KET_THUC.THOI_GIAN_KET_THUC AS "tgKetThuc",
TKB.THOI_GIAN_BAT_DAU AS "ngayBatDau",
TKB.THOI_GIAN_KET_THUC AS "ngayKetThuc",
TKB.IS_VANG AS "isVang",
TKB.IS_NGHI AS "isNghi",
TKB.GHI_CHU AS "ghiChu",
TKB.ID AS "idTuan",
TKB.ID_THOI_KHOA_BIEU AS "idThoiKhoaBieu",
TKB.THOI_GIAN_BAT_DAU AS "time",
CH.BUOI AS "buoi",
ROW_NUMBER() OVER (ORDER BY TKB.THU, TKB.MA_HOC_PHAN) R
FROM SDH_THOI_KHOA_BIEU_CUSTOM TKB
LEFT JOIN DM_PHONG P ON P.TEN = TKB.PHONG
LEFT JOIN DM_CO_SO CS ON CS.MA = P.CO_SO
LEFT JOIN DM_CA_HOC CH ON CH.MA_CO_SO = P.CO_SO AND CH.TEN = TKB.TIET_BAT_DAU
LEFT JOIN SDH_THOI_KHOA_BIEU DTKB ON DTKB.ID = TKB.ID_THOI_KHOA_BIEU
LEFT JOIN SDH_DM_MON_HOC_MOI DMMH ON DMMH.MA = TKB.MA_MON_HOC
LEFT JOIN SDH_KHUNG_DAO_TAO KDT ON DTKB.MA_KHUNG_DAO_TAO = KDT.ID
LEFT JOIN DM_NGANH_SAU_DAI_HOC NGANHSDH ON NGANHSDH.MA_NGANH = KDT.MA_NGANH
LEFT JOIN DM_HOC_SDH PH ON PH.MA = DTKB.LOAI_HINH_DAO_TAO
LEFT JOIN DM_KHOA_SAU_DAI_HOC DV1 ON DV1.MA = KDT.MA_KHOA
LEFT JOIN TKB_Giang_Vien TKBGV ON TKBGV.ID_THOI_KHOA_BIEU = TKB.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'
LEFT JOIN DM_CA_HOC CH_BAT_DAU
ON CH_BAT_DAU.MA_CO_SO = DTKB.CO_SO AND CH_BAT_DAU.TEN = TKB.TIET_BAT_DAU
LEFT JOIN DM_CA_HOC CH_KET_THUC ON CH_KET_THUC.MA_CO_SO = DTKB.CO_SO AND
TO_NUMBER(CH_KET_THUC.TEN) =
TO_NUMBER(TKB.TIET_BAT_DAU + TKB.SO_TIET_BUOI - 1)
WHERE (TKB.THOI_GIAN_BAT_DAU BETWEEN fromTime AND toTime)
AND NVL(TKB.IS_NGAY_LE, 0) != 1) LICH
WHERE ((ks_maHocPhan IS NULL OR LOWER(TRIM(LICH."maHocPhan")) LIKE '%' || lower(trim(ks_maHocPhan)) || '%')
AND (ks_tenMon IS NULL OR LOWER(TRIM(LICH."tenMonHoc")) LIKE '%' || lower(trim(ks_tenMon)) || '%')
AND (ks_thu IS NULL OR LOWER(TRIM(LICH."thu")) LIKE '%' || lower(trim(ks_thu)) || '%')
AND (ks_phong IS NULL OR LOWER(TRIM(LICH."phong")) LIKE '%' || lower(trim(ks_phong)) || '%')
AND (ks_tenCoSo IS NULL OR LOWER(TRIM(LICH."tenCoSo")) LIKE '%' || lower(trim(ks_tenCoSo)) || '%')
AND (ks_khoa IS NULL OR LOWER(TRIM(LICH."tenKhoaDangKy")) LIKE LOWER('%' || TRIM(ks_khoa) || '%'))
AND
(ks_giangVien IS NULL OR lower(LICH."dataTenGiangVien") like '%' || lower(trim(ks_giangVien)) || '%')
AND (ks_troGiang IS NULL OR lower(LICH."dataTenTroGiang") like '%' || lower(trim(ks_troGiang)) || '%')
AND R BETWEEN (pageNumber - 1) * pageSize + 1 AND pageNumber * pageSize)
ORDER BY CASE
WHEN sortMode = 'ASC' THEN
DECODE(sortKey,
'maHocPhan', LICH."maHocPhan",
'tenMon', LICH."tenMonHoc",
'thu', LICH."thu",
'time', (LICH."time"),
'phong', LICH."phong",
'khoa', LICH."tenKhoaDangKy",
'giangVien', LICH."dataTenGiangVien",
'troGiang', LICH."dataTenTroGiang",
LICH."time"
)
END ASC NULLS LAST,
CASE
WHEN sortMode = 'DESC' THEN
DECODE(sortKey,
'maHocPhan', LICH."maHocPhan",
'tenMon', LICH."tenMonHoc",
'thu', LICH."thu",
'time', (LICH."time"),
'phong', LICH."phong",
'khoa', LICH."tenKhoaDangKy",
'giangVien', LICH."dataTenGiangVien",
'troGiang', LICH."dataTenTroGiang",
LICH."time")
END DESC NULLS LAST;
return data;
end;
/
Editor is loading...
Leave a Comment