Untitled

 avatar
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