Untitled
unknown
plain_text
5 days ago
21 kB
1
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