Untitled

create function eoffice_thu_den_user_fetch_page(refcursor, INOUT page_number integer, INOUT page_size integer, OUT total_items integer, OUT page_total integer, filter jsonb) returns record
    language plpgsql
as
$$
DECLARE
    _shcc        varchar(50);
    _maDonVi     varchar(50);
    _nhanSuDonVi text[];
    _searchTerm  varchar(1000);

BEGIN
    _shcc := filter ->> 'shcc';
    _maDonVi := filter ->> 'maDonVi';
    _nhanSuDonVi := COALESCE(
            (SELECT array_agg(v)
             FROM jsonb_array_elements_text(filter -> 'nhanSuDonVi') AS v),
            ARRAY []::text[]
                    );
    _searchTerm := filter ->> 'searchTerm';
    select COUNT(*)
    into total_items
    from (select thuDen.id                                  as "id",
                 thuDen.noi_dung                            as "noiDung",
                 thuDen.don_vi_gui                          as "donViGui",
                 thuDen.ngay_nhan                           as "ngayNhan",
                 thuDenDetail.id                            as "thuDenDetailId",
                 thuDenDetail.don_vi_nhan                   as "maDonViNhan",
                 eDonVi.ten                                 as "tenDonViNhan",
                 thuDenDetail.nhan_su_nhan                  as "maNhanSuNhan",
                 eoffice_user.ho || ' ' || eoffice_user.ten as "tenNhanSuNhan",
                 eDonVi.ten_viet_tat                        as "tenDonVi",
                 thuDenDetail.ngay_tiep_nhan                as "ngayTiepNhan",
                 thuDenDetail.trang_thai                    as "trangThai",
                 nguoiTiepNhan."hoTen"                      as "nguoiTiepNhan",
                 row_number() over (order by thuDen.id)     as r
          from eoffice_thu_den_detail thuDenDetail
                   left join eoffice_thu_den thuDen on thuDen.id = thuDenDetail.thu_den_id
                   left join eoffice_user on eoffice_user.shcc = thuDenDetail.nhan_su_nhan
                   left join eoffice_don_vi eDonVi on eDonVi.ma = thuDenDetail.don_vi_nhan or eDonVi.ma = _maDonVi
                   left join (select eoffice_thu_den_detail.nguoi_tiep_nhan     as "shcc",
                                     eoffice_user.ho || ' ' || eoffice_user.ten as "hoTen"
                              from eoffice_thu_den_detail
                                       left join eoffice_user
                                                 on eoffice_thu_den_detail.nguoi_tiep_nhan = eoffice_user.shcc) nguoiTiepNhan
                             on nguoiTiepNhan."shcc" = thuDenDetail.nguoi_tiep_nhan
          where thuDen.is_deleted = false
            and ((thuDenDetail.nhan_su_nhan = _shcc or thuDenDetail.nhan_su_nhan = any (_nhanSuDonVi))
              or (thuDenDetail.don_vi_nhan = _maDonVi))
            and (_searchTerm is null or eDonVi.ten ilike '%' || _searchTerm || '%'
              or eoffice_user.ho || ' ' || eoffice_user.ten ilike '%' || _searchTerm || '%'
              or thuDen.don_vi_gui ilike '%' || _searchTerm || '%'
              or thuDen.noi_dung ilike '%' || _searchTerm || '%'
              or eDonVi.ma ilike '%' || _searchTerm || '%'
              or eoffice_user.shcc ilike '%' || _searchTerm || '%')
          group by thuDen.id, thuDenDetail.id, thuDenDetail.don_vi_nhan, eDonVi.ten, thuDenDetail.nhan_su_nhan,
                   eoffice_user.ho || ' ' || eoffice_user.ten, eDonVi.ten_viet_tat,
                   thuDenDetail.ngay_tiep_nhan, thuDenDetail.trang_thai,
                   nguoiTiepNhan."hoTen"
          order by thuDen.id desc) temp;

    if page_number < 1 then page_number := 1; end if;
    if page_size < 1 then page_size := 1; end if;
    page_total := ceil(total_items::decimal / page_size::decimal);
    page_number = least(page_number, page_total);

    open $1 for
        WITH nguoiTiepNhan as (select eoffice_thu_den_detail.nguoi_tiep_nhan     as "shcc",
                                      eoffice_user.ho || ' ' || eoffice_user.ten as "hoTen",
                                      userInfo."tenDonVi"                        as "tenDonVi"
                               from eoffice_thu_den_detail
                                        left join eoffice_user
                                                  on eoffice_thu_den_detail.nguoi_tiep_nhan = eoffice_user.shcc
                                        left join lateral (select "shcc",
                                                                  "tenDonVi" as "tenDonVi"
                                                           from eoffice_get_user_by_shcc(eoffice_thu_den_detail.nguoi_tiep_nhan)
                                   ) userInfo on userInfo."shcc" = eoffice_thu_den_detail.nguoi_tiep_nhan)
        select *
        from (select thuDen.id                                  as "id",
                     thuDen.noi_dung                            as "noiDung",
                     thuDen.don_vi_gui                          as "donViGui",
                     thuDen.ngay_nhan                           as "ngayNhan",
                     thuDenDetail.id                            as "thuDenDetailId",
                     thuDenDetail.don_vi_nhan                   as "maDonViNhan",
                     eDonVi.ten                                 as "tenDonViNhan",
                     thuDenDetail.nhan_su_nhan                  as "maNhanSuNhan",
                     eoffice_user.ho || ' ' || eoffice_user.ten as "tenNhanSuNhan",
                     eDonVi.ten_viet_tat                        as "tenDonVi",
                     thuDenDetail.ngay_tiep_nhan                as "ngayTiepNhan",
                     thuDenDetail.trang_thai                    as "trangThai",
                     nguoiTiepNhan."hoTen"                      as "nguoiTiepNhan",
                     nguoiTiepNhan."tenDonVi"                   as "tenDVNguoiTiepNhan",
                     row_number() over (order by thuDen.id)     as r
              from eoffice_thu_den_detail thuDenDetail
                       left join eoffice_thu_den thuDen on thuDen.id = thuDenDetail.thu_den_id
                       left join eoffice_user on eoffice_user.shcc = thuDenDetail.nhan_su_nhan
                       left join eoffice_don_vi eDonVi on eDonVi.ma = thuDenDetail.don_vi_nhan or eDonVi.ma = _maDonVi
                       left join nguoiTiepNhan on nguoiTiepNhan."shcc" = thuDenDetail.nguoi_tiep_nhan
              where thuDen.is_deleted = false
                and ((thuDenDetail.nhan_su_nhan = _shcc or thuDenDetail.nhan_su_nhan = any (_nhanSuDonVi))
                  or (thuDenDetail.don_vi_nhan = _maDonVi)
                  )
                and (_searchTerm is null or eDonVi.ten ilike '%' || _searchTerm || '%'
                  or eoffice_user.ho || ' ' || eoffice_user.ten ilike '%' || _searchTerm || '%'
                  or thuDen.don_vi_gui ilike '%' || _searchTerm || '%'
                  or thuDen.noi_dung ilike '%' || _searchTerm || '%'
                  or eDonVi.ma ilike '%' || _searchTerm || '%'
                  or eoffice_user.shcc ilike '%' || _searchTerm || '%')
              group by thuDen.id, thuDenDetail.id, thuDenDetail.don_vi_nhan, eDonVi.ten, thuDenDetail.nhan_su_nhan,
                       eoffice_user.ho || ' ' || eoffice_user.ten, eDonVi.ten_viet_tat,
                       thuDenDetail.ngay_tiep_nhan, thuDenDetail.trang_thai,
                       nguoiTiepNhan."hoTen", nguoiTiepNhan."tenDonVi"
              order by thuDen.id desc) as td
        where td.r between (page_number - 1) * page_size + 1 and page_number * page_size;
end;
$$;

alter function eoffice_thu_den_user_fetch_page(refcursor, inout integer, inout integer, out integer, out integer, jsonb) owner to hcmut_hanh_chinh_dev;