Untitled
unknown
plain_text
24 days ago
21 kB
2
Indexable
create FUNCTION SDH_HOI_DONG_SEARCH_PAGE(pageNumber IN OUT NUMBER, pageSize IN OUT NUMBER, filter IN STRING,
pageTotal OUT NUMBER,
totalItem OUT NUMBER) RETURN SYS_REFCURSOR
AS
my_cursor SYS_REFCURSOR;
ks_tenSinhVien STRING(200);
loaiHoiDong NVARCHAR2(20);
khoaHocVien NVARCHAR2(20);
donVi NVARCHAR2(20);
loaiHinhDaoTao NVARCHAR2(20);
ks_phong STRING(20);
ks_tenCanBo STRING(200);
ks_mssv STRING(20);
sortKey STRING(20);
sortMode STRING(20);
ks_tenDeTai STRING(100);
BEGIN
SELECT JSON_VALUE(filter, '$.ks_tenSinhVien') INTO ks_tenSinhVien FROM DUAL;
SELECT JSON_VALUE(filter, '$.ks_phong') INTO ks_phong FROM DUAL;
SELECT JSON_VALUE(filter, '$.ks_tenCanBo') INTO ks_tenCanBo FROM DUAL;
SELECT JSON_VALUE(filter, '$.ks_tenDeTai') INTO ks_tenDeTai FROM DUAL;
SELECT JSON_VALUE(filter, '$.ks_mssv') INTO ks_mssv FROM DUAL;
SELECT JSON_VALUE(filter, '$.sortKey') INTO sortKey FROM DUAL;
SELECT JSON_VALUE(filter, '$.sortMode') INTO sortMode FROM DUAL;
SELECT JSON_VALUE(filter, '$.khoaHocVien') INTO khoaHocVien FROM DUAL;
SELECT JSON_VALUE(filter, '$.donVi') INTO donVi FROM DUAL;
SELECT JSON_VALUE(filter, '$.loaiHinhDaoTao') INTO loaiHinhDaoTao FROM DUAL;
SELECT JSON_VALUE(filter, '$.loaiHoiDong') INTO loaiHoiDong FROM DUAL;
SELECT COUNT(*)
INTO totalItem
FROM (SELECT HOI_DONG."id" AS "id",
HOI_DONG."tenHoiDong" AS "tenHoiDong",
HOI_DONG."maLoaiHoiDong" AS "maLoaiHoiDong",
HOI_DONG."hoTenSinhVien" AS "hoTenSinhVien",
COALESCE(HOI_DONG."mssv", SINH_VIEN.MSSV) AS "mssv",
HOI_DONG."phong" AS "phong",
HOI_DONG."ngayBaoCao" AS "ngayBaoCao",
HOI_DONG."tenDeTai" AS "tenDeTai"
FROM (SELECT DISTINCT *
FROM (SELECT HD.ID as "id",
LHD.TEN as "tenHoiDong",
LHD.MA as "maLoaiHoiDong",
SV.HO || ' ' || SV.TEN as "hoTenSinhVien",
SV.MSSV as "mssv",
HD.PHONG as "phong",
HD.TIME as "ngayBaoCao",
DT.TEN_DE_TAI as "tenDeTai"
FROM SDH_HOI_DONG HD
LEFT JOIN FW_SINH_VIEN_SDH SV ON HD.MSSV = SV.MSSV
LEFT JOIN SDH_DE_TAI DT ON SV.MSSV = DT.MSSV
LEFT JOIN SDH_THANH_VIEN_HOI_DONG TVHD ON TVHD.ID_HOI_DONG = HD.ID
LEFT JOIN SDH_LOAI_HOI_DONG LHD ON LHD.MA = HD.MA_LOAI_HOI_DONG
LEFT JOIN (SELECT *
FROM (SELECT CBNT.HO || ' ' || CBNT.TEN || '(' || 'CBNT' || ')' AS "hoTen",
CAST(CBNT.SHCC AS VARCHAR2(20)) AS "shcc",
DT.VIET_TAT AS "trinhDo",
'CBNT' AS "belongTo"
FROM DT_CAN_BO_NGOAI_TRUONG CBNT
LEFT JOIN DM_TRINH_DO DT ON DT.MA = CBNT.TRINH_DO
UNION
SELECT CB.HO || ' ' || CB.TEN AS "hoTen",
CAST(CB.SHCC AS VARCHAR2(20)) AS "shcc",
DT.VIET_TAT AS "trinhDo",
CASE WHEN CB.LOAI_CAN_BO = 'TG' THEN 'CBNT' ELSE 'CBTT' END AS "belongTo"
FROM TCHC_CAN_BO CB
LEFT JOIN DM_TRINH_DO DT ON DT.MA = CB.HOC_VI
WHERE CB.NGAY_NGHI IS NULL
UNION
SELECT SDHCB.HO || ' ' || SDHCB.TEN || '(' || 'CBSDH' || ')' AS "hoTen",
CAST(SDHCB.ID AS VARCHAR2(20)) AS "shcc",
TRINH_DO AS "trinhDo",
'CBSDH' AS "belongTo"
FROM SDH_CAN_BO SDHCB)) TCCB
ON TVHD.ID_CAN_BO = TCCB."shcc"
WHERE (ks_tenSinhVien IS NULL OR
LOWER(SV.HO || ' ' || SV.TEN) LIKE '%' || LOWER(TRIM(ks_tenSinhVien)) || '%')
AND (ks_tenDeTai IS NULL OR LOWER(DT.TEN_DE_TAI) LIKE '%' || LOWER(TRIM(ks_tenDeTai)) || '%')
AND (ks_phong IS NULL OR LOWER(HD.PHONG) LIKE ' %' || LOWER(TRIM(ks_phong)) || '%')
AND (ks_mssv IS NULL OR LOWER(SV.MSSV) LIKE '%' || LOWER(TRIM(ks_mssv)) || '%')
AND (khoaHocVien IS NULL OR khoaHocVien = SV.NAM_TUYEN_SINH)
AND (donVi IS NULL OR donVi = SV.MA_KHOA)
AND (loaiHinhDaoTao IS NULL OR loaiHinhDaoTao = SV.BAC_DAO_TAO)
AND (loaiHoiDong IS NULL OR loaiHoiDong = LHD.MA)
AND (ks_tenCanBo IS NULL OR LOWER(TCCB."hoTen") LIKE '%' || LOWER(TRIM(ks_tenCanBo)) || '%')
-- AND SV.TINH_TRANG = 1 -- Còn học
)) HOI_DONG
FULL OUTER JOIN (SELECT * FROM FW_SINH_VIEN_SDH WHERE TINH_TRANG = 1) SINH_VIEN
ON HOI_DONG."mssv" = SINH_VIEN.MSSV);
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 TCCB AS (SELECT *
FROM (SELECT CBNT.HO || ' ' || CBNT.TEN || '(' || 'CBNT' || ')' AS "hoTen",
CAST(CBNT.SHCC AS VARCHAR2(20)) AS "shcc",
HOC_HAM.VIET_TAT AS "hocHam",
DT.VIET_TAT AS "hocVi",
'CBNT' AS "belongTo"
FROM DT_CAN_BO_NGOAI_TRUONG CBNT
LEFT JOIN DM_TRINH_DO DT ON DT.MA = CBNT.TRINH_DO
LEFT JOIN DM_CHUC_DANH_KHOA_HOC HOC_HAM ON HOC_HAM.MA = CBNT.HOC_HAM
UNION
SELECT CB.HO || ' ' || CB.TEN AS "hoTen",
CAST(CB.SHCC AS VARCHAR2(20)) AS "shcc",
HOC_HAM.VIET_TAT AS "hocHam",
DT.VIET_TAT AS "hocVi",
CASE WHEN CB.LOAI_CAN_BO = 'TG' THEN 'CBNT' ELSE 'CBTT' END AS "belongTo"
FROM TCHC_CAN_BO CB
LEFT JOIN DM_TRINH_DO DT ON DT.MA = CB.HOC_VI
LEFT JOIN DM_CHUC_DANH_KHOA_HOC HOC_HAM ON HOC_HAM.MA = CB.CHUC_DANH
WHERE CB.NGAY_NGHI IS NULL
UNION
SELECT SDHCB.HO || ' ' || SDHCB.TEN || '(' || 'CBSDH' || ')' AS "hoTen",
CAST(SDHCB.ID AS VARCHAR2(20)) AS "shcc",
HOC_HAM.VIET_TAT AS "hocHam",
DT.VIET_TAT AS "hocVi",
'CBSDH' AS "belongTo"
FROM SDH_CAN_BO SDHCB
LEFT JOIN DM_TRINH_DO DT ON DT.MA = SDHCB.TRINH_DO
LEFT JOIN DM_CHUC_DANH_KHOA_HOC HOC_HAM ON HOC_HAM.MA = SDHCB.HOC_HAM)),
SO_QUYET_DINH_HD_FILTERED AS (SELECT SQD.ID_HOI_DONG,
SQD.SO_QUYET_DINH,
ROW_NUMBER() OVER (PARTITION BY SQD.ID_HOI_DONG ORDER BY SQD.ID DESC) RN
FROM SDH_SO_QUYET_DINH SQD
WHERE (SQD.ACTIVE = 2 OR SQD.ACTIVE = 3))
SELECT *
FROM (SELECT COALESCE(HOI_DONG."mssv", SINH_VIEN.MSSV) AS "mssv",
HOI_DONG."id" AS "id",
HOI_DONG."phanHeDaoTao" AS "phanHeDaoTao",
HOI_DONG."pass" AS "pass",
HOI_DONG."tenHoiDong" AS "tenHoiDong",
HOI_DONG."loaiHoiDong" AS "loaiHoiDong",
HOI_DONG."tenLoaiHoiDong" AS "tenLoaiHoiDong",
HOI_DONG."hoTenSinhVien" AS "hoTenSinhVien",
HOI_DONG."thongTinHocVien" AS "thongTinHocVien",
HOI_DONG."phong" AS "phong",
HOI_DONG."ngayBaoCao" AS "ngayBaoCao",
HOI_DONG."tenDeTai" AS "tenDeTai",
HOI_DONG."soQuyetDinh" AS "soQuyetDinh",
HOI_DONG."soCongVan" AS "soCongVan",
HOI_DONG."diemDat" AS "diemDat",
HOI_DONG."diem" AS "diem",
HOI_DONG."tanThanh" AS "tanThanh",
HOI_DONG."xuatSac" AS "xuatSac",
HOI_DONG."soNguoiThamDu" AS "soNguoiThamDu",
HOI_DONG."noiDungHoiDong" AS "noiDungHoiDong",
HOI_DONG."monHoc" AS "monHoc",
HOI_DONG."thanhVienHoiDong" AS "thanhVienHoiDong",
ROW_NUMBER() OVER (ORDER BY HOI_DONG."id", SINH_VIEN.MSSV) AS R
FROM (SELECT HD.ID as "id",
HD.PHAN_HE as "phanHeDaoTao",
HD.PASS as "pass",
LHD.TEN as "tenHoiDong",
LHD.MA as "loaiHoiDong",
LHD.LOAI_HOI_DONG AS "tenLoaiHoiDong",
SV.HO || ' ' || SV.TEN as "hoTenSinhVien",
SV.MSSV as "mssv",
DBMS_LOB.SUBSTR(HDXD."thongTinHocVien", 4000, 1) as "thongTinHocVien",
HD.PHONG as "phong",
HD.TIME as "ngayBaoCao",
DT.TEN_DE_TAI as "tenDeTai",
SQDF.SO_QUYET_DINH AS "soQuyetDinh",
HSDK.SO_CONG_VAN AS "soCongVan",
TD.DIEM_DAT AS "diemDat",
DHD.DIEM AS "diem",
DHD.TAN_THANH AS "tanThanh",
DHD.XUAT_SAC AS "xuatSac",
DHD.TONG_SO_NGUOI AS "soNguoiThamDu",
DBMS_LOB.SUBSTR(HD.NOI_DUNG_HOI_DONG, 4000, 1) AS "noiDungHoiDong",
HD.MA_MON_HOC AS "monHoc",
regexp_replace(JSON_ARRAYAGG(to_clob(JSON_OBJECT(
key TO_CHAR('shcc') VALUE CONVERT(TVHD.ID_CAN_BO, 'utf8'),
key TO_CHAR('vaiTro') VALUE CONVERT(VAI_TRO.MA, 'utf8'),
key TO_CHAR('tenVaiTro') VALUE CONVERT(VAI_TRO.VAI_TRO, 'utf8'),
key TO_CHAR('hocVi') VALUE CONVERT(TCCB."hocVi", 'utf8'),
key TO_CHAR('hocHam') VALUE CONVERT(TCCB."hocHam", 'utf8'),
key TO_CHAR('hoTenCanBo') VALUE CONVERT(TCCB."hoTen", 'utf8'),
key TO_CHAR('belongTo') VALUE CONVERT(TVHD.BELONG_TO, 'utf8')
)) ORDER BY VAI_TRO.PRIORITY returning clob), '}"',
'}') AS "thanhVienHoiDong",
ROW_NUMBER() OVER (ORDER BY HD.ID) AS R
FROM SDH_HOI_DONG HD
LEFT JOIN FW_SINH_VIEN_SDH SV ON HD.MSSV = SV.MSSV
LEFT JOIN SDH_DE_TAI DT ON SV.MSSV = DT.MSSV
LEFT JOIN SDH_THANH_VIEN_HOI_DONG TVHD ON TVHD.ID_HOI_DONG = HD.ID
LEFT JOIN SDH_DM_VAI_TRO_HOI_DONG VAI_TRO ON VAI_TRO.MA = TVHD.VAI_TRO
LEFT JOIN TCCB ON TVHD.ID_CAN_BO = TCCB."shcc"
LEFT JOIN SO_QUYET_DINH_HD_FILTERED SQDF ON SQDF.RN = 1 AND SQDF.ID_HOI_DONG = HD.ID
LEFT JOIN HCTH_SO_DANG_KY HSDK ON HSDK.ID = SQDF.SO_QUYET_DINH
LEFT JOIN SDH_DIEM_HOI_DONG DHD ON HD.ID = DHD.ID_HOI_DONG AND HD.MSSV = DHD.MSSV
LEFT JOIN SDH_LOAI_HOI_DONG LHD ON LHD.MA = HD.MA_LOAI_HOI_DONG
LEFT JOIN (SELECT HDXD.SO_QUYET_DINH as "soQuyetDinh",
HDXD.ID_HOI_DONG as "idHoiDong",
regexp_replace(JSON_ARRAYAGG(to_clob(JSON_OBJECT(
key TO_CHAR('hoTenSinhVien') VALUE
CONVERT(SV.HO || ' ' || SV.TEN, 'utf8'),
key TO_CHAR('mssv') VALUE CONVERT(SV.MSSV, 'utf8')
)) ORDER BY SV.MSSV returning clob),
'}"',
'}') as "thongTinHocVien"
FROM SDH_HOI_DONG_XET_DUYET HDXD
LEFT JOIN FW_SINH_VIEN_SDH SV ON HDXD.MSSV = SV.MSSV
GROUP BY HDXD.ID_HOI_DONG, HDXD.SO_QUYET_DINH) HDXD
ON HDXD."idHoiDong" = HD.ID AND HDXD."soQuyetDinh" = SQDF.SO_QUYET_DINH
LEFT JOIN SDH_LOP_HOC_VIEN DTL ON DTL.MA = SV.LOP
LEFT JOIN SDH_DIEM_THANG_DIEM_KHOA_HV TDKHV ON TDKHV.KHOA_HOC_VIEN = DTL.KHOA_SINH_VIEN
LEFT JOIN SDH_DIEM_THANG_DIEM TD ON TD.ID = TDKHV.ID_THANG_DIEM
WHERE (ks_tenSinhVien IS NULL OR
LOWER(SV.HO || ' ' || SV.TEN) LIKE '%' || LOWER(TRIM(ks_tenSinhVien)) || '%')
AND (ks_tenDeTai IS NULL OR LOWER(DT.TEN_DE_TAI) LIKE '%' || LOWER(TRIM(ks_tenDeTai)) || '%')
AND (ks_phong IS NULL OR LOWER(HD.PHONG) LIKE ' %' || LOWER(TRIM(ks_phong)) || '%')
AND (ks_mssv IS NULL OR LOWER(SV.MSSV) LIKE '%' || LOWER(TRIM(ks_mssv)) || '%')
AND (khoaHocVien IS NULL OR khoaHocVien = SV.NAM_TUYEN_SINH)
AND (donVi IS NULL OR donVi = SV.MA_KHOA)
AND (loaiHinhDaoTao IS NULL OR loaiHinhDaoTao = SV.BAC_DAO_TAO)
AND (loaiHoiDong IS NULL OR loaiHoiDong = LHD.MA)
AND (ks_tenCanBo IS NULL OR LOWER(TCCB."hoTen") LIKE '%' || LOWER(TRIM(ks_tenCanBo)) || '%')
-- AND SV.TINH_TRANG = 1 -- Còn học
GROUP BY HD.ID, HD.MA_MON_HOC, HD.PASS, LHD.TEN, HD.PHAN_HE, LHD.MA, LHD.LOAI_HOI_DONG,
LHD.PRIORITY,
SV.HO || ' ' || SV.TEN,
HD.PHONG, HD.TIME,
DT.TEN_DE_TAI,
SV.MSSV, SQDF.SO_QUYET_DINH, HSDK.SO_CONG_VAN, TD.DIEM_DAT, DHD.DIEM, DHD.TAN_THANH,
DHD.XUAT_SAC,
DHD.TONG_SO_NGUOI, DBMS_LOB.SUBSTR(HD.NOI_DUNG_HOI_DONG, 4000, 1),
DBMS_LOB.SUBSTR(HDXD."thongTinHocVien", 4000, 1)) HOI_DONG
FULL OUTER JOIN (SELECT * FROM FW_SINH_VIEN_SDH WHERE TINH_TRANG = 1) SINH_VIEN
ON HOI_DONG."mssv" = SINH_VIEN.MSSV
ORDER BY CASE
WHEN sortMode = 'ASC' THEN
DECODE(sortKey,
'id', NLSSORT(HOI_DONG."id", 'NLS_SORT = BINARY_AI'),
'hoTenSinhVien', NLSSORT(HOI_DONG."tenHoiDong", 'NLS_SORT = BINARY_AI'),
'mssv',
NLSSORT(COALESCE(HOI_DONG."mssv", SINH_VIEN.MSSV), 'NLS_SORT = BINARY_AI'),
'phong', NLSSORT(HOI_DONG."phong", 'NLS_SORT = BINARY_AI'),
'time', NLSSORT(HOI_DONG."ngayBaoCao", 'NLS_SORT = BINARY_AI'),
'tenDeTai', NLSSORT(HOI_DONG."tenDeTai", 'NLS_SORT = BINARY_AI')
) END ASC NULLS LAST,
CASE
WHEN sortMode = 'DESC' THEN
DECODE(sortKey,
'id', NLSSORT(HOI_DONG."id", 'NLS_SORT = BINARY_AI'),
'hoTenSinhVien', NLSSORT(HOI_DONG."tenHoiDong", 'NLS_SORT = BINARY_AI'),
'mssv',
NLSSORT(COALESCE(HOI_DONG."mssv", SINH_VIEN.MSSV), 'NLS_SORT = BINARY_AI'),
'phong', NLSSORT(HOI_DONG."phong", 'NLS_SORT = BINARY_AI'),
'time', NLSSORT(HOI_DONG."ngayBaoCao", 'NLS_SORT = BINARY_AI'),
'tenDeTai', NLSSORT(HOI_DONG."tenDeTai", '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