package jnpf.dmsMapper;
|
|
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
|
import jnpf.dmsEntity.permission.entity.DmsPermissionResourceEntity;
|
import jnpf.dmsEntity.permission.vo.DmsPermissionResourceVO;
|
import org.apache.ibatis.annotations.Mapper;
|
import org.apache.ibatis.annotations.Param;
|
import org.apache.ibatis.annotations.Select;
|
|
import java.util.Date;
|
import java.util.List;
|
import java.util.UUID;
|
|
@Mapper
|
public interface DmsPermissionResourceMapper extends BaseMapper<DmsPermissionResourceEntity> {
|
String DOCUMENT_DIRECTORY_NAME = "COALESCE((SELECT initial_version.file_name "
|
+ "FROM public.dms_file_versions initial_version "
|
+ "WHERE initial_version.tenant_id = d.tenant_id AND initial_version.document_id = d.id "
|
+ "ORDER BY initial_version.version_no, initial_version.id LIMIT 1), d.name)";
|
|
String RESOURCE_VIEW_SELECT_SQL = "SELECT r.id, r.resource_type, "
|
+ "CASE r.resource_type WHEN 'FOLDER' THEN f.parent_id WHEN 'DOCUMENT' THEN d.folder_id "
|
+ "WHEN 'FILE_VERSION' THEN v.document_id END AS parent_id, "
|
+ "CASE r.resource_type WHEN 'FOLDER' THEN f.name WHEN 'DOCUMENT' THEN "
|
+ DOCUMENT_DIRECTORY_NAME + " "
|
+ "WHEN 'FILE_VERSION' THEN v.file_name END AS name, "
|
+ "CASE r.resource_type WHEN 'DOCUMENT' THEN d.id "
|
+ "WHEN 'FILE_VERSION' THEN v.document_id END AS document_id, "
|
+ "CASE WHEN r.resource_type = 'DOCUMENT' THEN " + DOCUMENT_DIRECTORY_NAME
|
+ " ELSE version_document.name END AS document_name, "
|
+ "COALESCE(d.document_no, version_document.document_no) AS document_no, "
|
+ "COALESCE(d.document_type, version_document.document_type) AS document_type, "
|
+ "COALESCE(d.responsible_dept_id, version_document.responsible_dept_id) AS responsible_dept_id, "
|
+ "COALESCE(v.version_no, current_version.version_no) AS version_no, "
|
+ "COALESCE(v.version_label, current_version.version_label) AS version_label, "
|
+ "COALESCE(v.file_name, current_version.file_name) AS file_name, "
|
+ "COALESCE(v.mime_type, current_version.mime_type) AS mime_type, "
|
+ "COALESCE(v.size_bytes, current_version.size_bytes) AS size_bytes, "
|
+ "COALESCE(v.checksum_sha256, current_version.checksum_sha256) AS checksum_sha256, "
|
+ "COALESCE(v.prepared_by, current_version.prepared_by) AS prepared_by, "
|
+ "COALESCE(v.prepared_on, current_version.prepared_on) AS prepared_on, "
|
+ "COALESCE(version_state.lifecycle_status, current_state.lifecycle_status) AS lifecycle_status, "
|
+ "COALESCE(version_state.effective_from, current_state.effective_from) AS effective_from, "
|
+ "COALESCE(version_state.effective_to, current_state.effective_to) AS effective_to, "
|
+ "COALESCE(version_state.review_cycle_months, current_state.review_cycle_months) AS review_cycle_months, "
|
+ "COALESCE(version_state.last_reviewed_on, current_state.last_reviewed_on) AS last_reviewed_on, "
|
+ "COALESCE(version_state.next_review_on, current_state.next_review_on) AS next_review_on, "
|
+ "CASE WHEN r.resource_type = 'FOLDER' THEN f.sort_code END AS sort_code, "
|
+ "CASE WHEN r.resource_type = 'FOLDER' THEN f.revision END AS revision, "
|
+ "r.source_type, r.source_id, CASE r.resource_type "
|
+ "WHEN 'FOLDER' THEN f.created_at WHEN 'DOCUMENT' THEN current_version.created_at "
|
+ "WHEN 'FILE_VERSION' THEN v.created_at ELSE r.created_at END AS created_at ";
|
|
String RESOURCE_VIEW_FROM_SQL = "FROM public.dms_permission_resources r "
|
+ "LEFT JOIN public.dms_folders f ON r.resource_type = 'FOLDER' "
|
+ "AND f.tenant_id = r.tenant_id AND f.id = r.id "
|
+ "LEFT JOIN public.dms_documents d ON r.resource_type = 'DOCUMENT' "
|
+ "AND d.tenant_id = r.tenant_id AND d.id = r.id "
|
+ "LEFT JOIN public.dms_file_versions v ON r.resource_type = 'FILE_VERSION' "
|
+ "AND v.tenant_id = r.tenant_id AND v.id = r.id "
|
+ "LEFT JOIN public.dms_documents version_document ON r.resource_type = 'FILE_VERSION' "
|
+ "AND version_document.tenant_id = v.tenant_id AND version_document.id = v.document_id "
|
+ "LEFT JOIN public.dms_version_states current_state ON r.resource_type = 'DOCUMENT' "
|
+ "AND current_state.tenant_id = r.tenant_id AND current_state.document_id = r.id "
|
+ "AND current_state.lifecycle_status = 'EFFECTIVE' "
|
+ "AND CURRENT_TIMESTAMP >= current_state.effective_from "
|
+ "AND (current_state.effective_to IS NULL OR current_state.effective_to > CURRENT_TIMESTAMP) "
|
+ "LEFT JOIN public.dms_file_versions current_version ON current_version.tenant_id = current_state.tenant_id "
|
+ "AND current_version.document_id = current_state.document_id "
|
+ "AND current_version.id = current_state.file_version_id "
|
+ "LEFT JOIN public.dms_version_states version_state ON r.resource_type = 'FILE_VERSION' "
|
+ "AND version_state.tenant_id = v.tenant_id AND version_state.document_id = v.document_id "
|
+ "AND version_state.file_version_id = v.id ";
|
|
String RESOURCE_VIEW_SQL = RESOURCE_VIEW_SELECT_SQL + RESOURCE_VIEW_FROM_SQL;
|
|
String FILE_SEARCH_SCOPE = "WITH RECURSIVE search_folders(id) AS ( "
|
+ "<choose><when test='parentId != null'> "
|
+ "SELECT f.id FROM public.dms_folders f WHERE f.tenant_id = #{tenantId} "
|
+ "AND f.id = #{parentId} AND f.f_delete_mark IS NULL "
|
+ "</when><otherwise>SELECT NULL::UUID WHERE FALSE</otherwise></choose> "
|
+ "UNION ALL SELECT child.id FROM search_folders parent "
|
+ "JOIN public.dms_folders child ON child.tenant_id = #{tenantId} "
|
+ "AND child.parent_id = parent.id AND child.f_delete_mark IS NULL) ";
|
|
String FILE_SEARCH_MATCH = "AND ("
|
+ "STRPOS(LOWER(COALESCE(v.file_name, '')), LOWER(#{keyword})) > 0 "
|
+ "OR STRPOS(LOWER(COALESCE(version_document.name, '')), LOWER(#{keyword})) > 0 "
|
+ "OR STRPOS(LOWER(COALESCE(version_document.document_no, '')), LOWER(#{keyword})) > 0) ";
|
|
@Select("SELECT uuidv7()")
|
UUID nextUuidV7();
|
|
@Select("SELECT 1 FROM (SELECT pg_advisory_xact_lock(hashtextextended(#{tenantId} || ':' "
|
+ "|| #{sourceType} || ':' || #{sourceId}, 0))) advisory_lock")
|
Integer lockSource(@Param("tenantId") String tenantId,
|
@Param("sourceType") String sourceType,
|
@Param("sourceId") String sourceId);
|
|
@Select("SELECT id, tenant_id, resource_type, source_type, source_id, registered_by, created_at "
|
+ "FROM public.dms_permission_resources WHERE tenant_id = #{tenantId} AND id = #{id}")
|
DmsPermissionResourceEntity selectByTenantAndId(@Param("tenantId") String tenantId,
|
@Param("id") UUID id);
|
|
@Select("SELECT id, tenant_id, resource_type, source_type, source_id, registered_by, created_at "
|
+ "FROM public.dms_permission_resources WHERE tenant_id = #{tenantId} "
|
+ "AND source_type = #{sourceType} AND source_id = #{sourceId}")
|
DmsPermissionResourceEntity selectBySource(@Param("tenantId") String tenantId,
|
@Param("sourceType") String sourceType,
|
@Param("sourceId") String sourceId);
|
|
@Select({"<script>",
|
"SELECT id, tenant_id, resource_type, source_type, source_id, registered_by, created_at",
|
"FROM public.dms_permission_resources WHERE tenant_id = #{tenantId} AND id IN",
|
"<foreach collection='ids' item='id' open='(' separator=',' close=')'>#{id}</foreach>",
|
"</script>"})
|
List<DmsPermissionResourceEntity> selectByTenantAndIds(@Param("tenantId") String tenantId,
|
@Param("ids") List<UUID> ids);
|
|
@Select(RESOURCE_VIEW_SQL + "WHERE r.tenant_id = #{tenantId} AND r.id = #{id} "
|
+ "AND (r.resource_type != 'FOLDER' OR f.f_delete_mark IS NULL)")
|
DmsPermissionResourceVO selectView(@Param("tenantId") String tenantId, @Param("id") UUID id);
|
|
@Select({"<script>", RESOURCE_VIEW_SQL,
|
"WHERE r.tenant_id = #{tenantId}",
|
"AND (r.resource_type != 'FOLDER' OR f.f_delete_mark IS NULL)",
|
"<if test='resourceType != null and resourceType != \"\"'>",
|
"AND r.resource_type = #{resourceType}",
|
"</if>",
|
"<if test='cursorTime != null'>",
|
"AND (r.created_at, r.id) < (#{cursorTime}, #{cursorId})",
|
"</if>",
|
"ORDER BY r.created_at DESC, r.id DESC LIMIT #{limit}",
|
"</script>"})
|
List<DmsPermissionResourceVO> selectPage(@Param("tenantId") String tenantId,
|
@Param("resourceType") String resourceType,
|
@Param("cursorTime") Date cursorTime,
|
@Param("cursorId") UUID cursorId,
|
@Param("limit") int limit);
|
|
@Select({"<script>", RESOURCE_VIEW_SQL,
|
"WHERE r.tenant_id = #{tenantId}",
|
"AND (r.resource_type != 'FOLDER' OR f.f_delete_mark IS NULL)",
|
"<choose>",
|
"<when test='parentId == null'>",
|
"AND r.resource_type = 'FOLDER' AND f.parent_id IS NULL",
|
"</when>",
|
"<otherwise>",
|
"AND ((r.resource_type = 'FOLDER' AND f.parent_id = #{parentId})",
|
"OR (r.resource_type = 'DOCUMENT' AND d.folder_id = #{parentId})",
|
"OR (r.resource_type = 'FILE_VERSION' AND v.document_id = #{parentId}))",
|
"</otherwise>",
|
"</choose>",
|
"ORDER BY CASE r.resource_type WHEN 'FOLDER' THEN 0 WHEN 'DOCUMENT' THEN 1 ELSE 2 END,",
|
"CASE WHEN r.resource_type = 'FOLDER' THEN f.sort_code END, name, r.id LIMIT #{limit}",
|
"</script>"})
|
List<DmsPermissionResourceVO> selectChildren(@Param("tenantId") String tenantId,
|
@Param("parentId") UUID parentId,
|
@Param("limit") int limit);
|
|
@Select({"<script>", FILE_SEARCH_SCOPE, RESOURCE_VIEW_SQL,
|
"WHERE r.tenant_id = #{tenantId} AND r.resource_type = 'FILE_VERSION'",
|
"<if test='parentId != null'>",
|
"AND (v.document_id = #{parentId}",
|
"OR version_document.folder_id IN (SELECT id FROM search_folders))",
|
"</if>",
|
FILE_SEARCH_MATCH,
|
"ORDER BY v.file_name, v.id LIMIT #{limit}",
|
"</script>"})
|
List<DmsPermissionResourceVO> searchFiles(@Param("tenantId") String tenantId,
|
@Param("parentId") UUID parentId,
|
@Param("keyword") String keyword,
|
@Param("limit") int limit);
|
|
}
|
