package jnpf.dmsMapper;
|
|
import jnpf.dmsEntity.permission.vo.DmsFileCenterItemVO;
|
import jnpf.dmsPermission.DmsFileCenterQuery;
|
import org.apache.ibatis.annotations.Mapper;
|
import org.apache.ibatis.annotations.Param;
|
import org.apache.ibatis.annotations.Select;
|
|
import java.util.List;
|
|
@Mapper
|
public interface DmsFileCenterMapper {
|
String ACTIVE_READ = "p.tenant_id = #{q.tenantId} AND p.action_code = 'READ' "
|
+ "AND p.revoked_at IS NULL AND p.valid_from <= CURRENT_TIMESTAMP "
|
+ "AND (p.valid_to IS NULL OR CURRENT_TIMESTAMP < p.valid_to) "
|
+ "AND (p.max_use_count IS NULL OR p.used_count < p.max_use_count) ";
|
|
String PRINCIPAL_MATCH = "AND ((p.principal_type = 'USER' AND p.principal_id = #{q.userId}) "
|
+ "<if test='q.directOrgIds != null and !q.directOrgIds.isEmpty()'> "
|
+ "OR (p.principal_type = 'ORG' AND p.include_child_orgs = FALSE AND p.principal_id IN "
|
+ "<foreach collection='q.directOrgIds' item='id' open='(' separator=',' close=')'>#{id}</foreach>) "
|
+ "</if> "
|
+ "<if test='q.ancestorOrgIds != null and !q.ancestorOrgIds.isEmpty()'> "
|
+ "OR (p.principal_type = 'ORG' AND p.include_child_orgs = TRUE AND p.principal_id IN "
|
+ "<foreach collection='q.ancestorOrgIds' item='id' open='(' separator=',' close=')'>#{id}</foreach>) "
|
+ "</if> "
|
+ "<if test='q.positionIds != null and !q.positionIds.isEmpty()'> "
|
+ "OR (p.principal_type = 'POSITION' AND p.principal_id IN "
|
+ "<foreach collection='q.positionIds' item='id' open='(' separator=',' close=')'>#{id}</foreach>) "
|
+ "</if> "
|
+ "<if test='q.roleIds != null and !q.roleIds.isEmpty()'> "
|
+ "OR (p.principal_type = 'ROLE' AND p.principal_id IN "
|
+ "<foreach collection='q.roleIds' item='id' open='(' separator=',' close=')'>#{id}</foreach>) "
|
+ "</if> "
|
+ "<if test='q.permissionGroupIds != null and !q.permissionGroupIds.isEmpty()'> "
|
+ "OR (p.principal_type = 'GROUP' AND p.principal_id IN "
|
+ "<foreach collection='q.permissionGroupIds' item='id' open='(' separator=',' close=')'>#{id}</foreach>) "
|
+ "</if>) ";
|
|
String FOLDER_ANCESTORS = "WITH RECURSIVE folder_ancestors(id, parent_id) AS ( "
|
+ "SELECT f.id, f.parent_id FROM public.dms_folders f "
|
+ "WHERE f.tenant_id = #{q.tenantId} AND f.id = #{q.folderId} "
|
+ "UNION ALL SELECT parent.id, parent.parent_id FROM folder_ancestors child "
|
+ "JOIN public.dms_folders parent ON parent.tenant_id = #{q.tenantId} "
|
+ "AND parent.id = child.parent_id) ";
|
|
String VERSION_COLUMNS = "v.id, 'FILE_VERSION' AS resource_type, d.folder_id AS parent_id, "
|
+ "d.name, d.id AS document_id, d.document_no, d.document_type, d.responsible_dept_id, "
|
+ "v.version_no, v.version_label, v.file_name, v.mime_type, v.size_bytes, "
|
+ "v.checksum_sha256, v.prepared_by, v.prepared_on, s.lifecycle_status, "
|
+ "s.effective_from, s.effective_to, v.created_at ";
|
|
String ACTIVE_EXCLUSION = "e.tenant_id = #{q.tenantId} AND e.user_id = #{q.userId} "
|
+ "AND e.action_code = 'READ' AND e.revoked_at IS NULL "
|
+ "AND e.valid_from <= CURRENT_TIMESTAMP "
|
+ "AND (e.valid_to IS NULL OR CURRENT_TIMESTAMP < e.valid_to) ";
|
|
String SHARED_ELIGIBLE_VERSIONS = "WITH eligible_versions(file_version_id) AS ( "
|
+ "<choose><when test='q.administrator'> "
|
+ "SELECT s.file_version_id FROM public.dms_version_states s "
|
+ "WHERE s.tenant_id = #{q.tenantId} "
|
+ "</when><otherwise> "
|
+ "SELECT s.file_version_id FROM public.dms_file_permissions p "
|
+ "JOIN public.dms_version_states s ON s.tenant_id = p.tenant_id "
|
+ "AND s.document_id = p.resource_id WHERE " + ACTIVE_READ + PRINCIPAL_MATCH
|
+ "AND p.resource_type = 'DOCUMENT' "
|
+ "UNION SELECT p.resource_id FROM public.dms_file_permissions p WHERE "
|
+ ACTIVE_READ + PRINCIPAL_MATCH + "AND p.resource_type = 'FILE_VERSION' "
|
+ "</otherwise></choose>) ";
|
|
@Select({"<script>",
|
"WITH RECURSIVE parent_ancestors(id, parent_id) AS (",
|
"<choose>",
|
"<when test='q.parentId != null'>",
|
"SELECT f.id, f.parent_id FROM public.dms_folders f",
|
"WHERE f.tenant_id = #{q.tenantId} AND f.id = #{q.parentId}",
|
"</when>",
|
"<otherwise>SELECT NULL::UUID, NULL::UUID WHERE FALSE</otherwise>",
|
"</choose>",
|
"UNION ALL SELECT parent.id, parent.parent_id FROM parent_ancestors child",
|
"JOIN public.dms_folders parent ON parent.tenant_id = #{q.tenantId}",
|
"AND parent.id = child.parent_id)",
|
"<if test='!q.administrator'>",
|
", authorized_navigation_ancestors(id, parent_id) AS (",
|
"SELECT granted.id, granted.parent_id FROM public.dms_file_permissions p",
|
"JOIN public.dms_folders granted ON granted.tenant_id = p.tenant_id",
|
"AND granted.id = p.resource_id WHERE", ACTIVE_READ, PRINCIPAL_MATCH,
|
"AND p.resource_type = 'FOLDER'",
|
"UNION SELECT parent.id, parent.parent_id FROM authorized_navigation_ancestors child",
|
"JOIN public.dms_folders parent ON parent.tenant_id = #{q.tenantId}",
|
"AND parent.id = child.parent_id)",
|
"</if>",
|
"SELECT f.id, 'FOLDER' AS resource_type, f.parent_id, f.name, f.created_at",
|
"FROM public.dms_folders f",
|
"WHERE f.tenant_id = #{q.tenantId}",
|
"<choose>",
|
"<when test='q.parentId == null'>AND f.parent_id IS NULL</when>",
|
"<otherwise>AND f.parent_id = #{q.parentId}</otherwise>",
|
"</choose>",
|
"<if test='q.cursorName != null'>",
|
"AND (f.name > #{q.cursorName} OR (f.name = #{q.cursorName} AND f.id > #{q.cursorId}))",
|
"</if>",
|
"<if test='!q.administrator'>",
|
"AND (EXISTS (SELECT 1 FROM public.dms_file_permissions p WHERE", ACTIVE_READ, PRINCIPAL_MATCH,
|
"AND p.resource_type = 'FOLDER' AND (p.resource_id = f.id OR",
|
"(p.resource_scope = 'SUBTREE' AND p.resource_id IN (SELECT id FROM parent_ancestors))))",
|
"OR f.id IN (SELECT id FROM authorized_navigation_ancestors))",
|
"</if>",
|
"ORDER BY f.name, f.id LIMIT #{q.limit}",
|
"</script>"})
|
List<DmsFileCenterItemVO> selectFolders(@Param("q") DmsFileCenterQuery query);
|
|
@Select({"<script>", FOLDER_ANCESTORS,
|
"SELECT", VERSION_COLUMNS,
|
"FROM public.dms_documents d",
|
"JOIN public.dms_version_states s ON s.tenant_id = d.tenant_id AND s.document_id = d.id",
|
"JOIN public.dms_file_versions v ON v.tenant_id = s.tenant_id",
|
"AND v.document_id = s.document_id AND v.id = s.file_version_id",
|
"WHERE d.tenant_id = #{q.tenantId} AND d.folder_id = #{q.folderId}",
|
"AND s.lifecycle_status = 'EFFECTIVE' AND s.effective_from <= CURRENT_TIMESTAMP",
|
"AND (s.effective_to IS NULL OR CURRENT_TIMESTAMP < s.effective_to)",
|
"<if test='q.keyword != null'>AND d.name LIKE CONCAT(#{q.keyword}, '%')</if>",
|
"<if test='q.cursorName != null'>",
|
"AND (d.name > #{q.cursorName} OR",
|
"(d.name = #{q.cursorName} AND d.id > #{q.cursorDocumentId}))",
|
"</if>",
|
"<if test='!q.administrator'>",
|
"AND NOT EXISTS (SELECT 1 FROM public.dms_access_exclusions e WHERE", ACTIVE_EXCLUSION,
|
"AND ((e.resource_type = 'DOCUMENT' AND e.resource_id = d.id)",
|
"OR (e.resource_type = 'FILE_VERSION' AND e.resource_id = v.id)))",
|
"AND EXISTS (SELECT 1 FROM public.dms_file_permissions p WHERE", ACTIVE_READ, PRINCIPAL_MATCH,
|
"AND p.resource_type = 'FOLDER' AND (p.resource_id = #{q.folderId}",
|
"OR (p.resource_scope = 'SUBTREE' AND p.resource_id IN (SELECT id FROM folder_ancestors))))",
|
"</if>",
|
"ORDER BY d.name, d.id LIMIT #{q.limit}",
|
"</script>"})
|
List<DmsFileCenterItemVO> selectCurrentDocuments(@Param("q") DmsFileCenterQuery query);
|
|
@Select({"<script>", FOLDER_ANCESTORS,
|
"SELECT", VERSION_COLUMNS,
|
"FROM public.dms_documents d",
|
"JOIN public.dms_file_versions v ON v.tenant_id = d.tenant_id AND v.document_id = d.id",
|
"JOIN public.dms_version_states s ON s.tenant_id = v.tenant_id",
|
"AND s.document_id = v.document_id AND s.file_version_id = v.id",
|
"WHERE d.tenant_id = #{q.tenantId} AND d.folder_id = #{q.folderId}",
|
"AND s.lifecycle_status IN ('SUPERSEDED', 'EXPIRED', 'REVOKED', 'INVALIDATED', 'OBSOLETE')",
|
"<if test='q.keyword != null'>AND d.name LIKE CONCAT(#{q.keyword}, '%')</if>",
|
"<if test='q.cursorName != null'>",
|
"AND (d.name > #{q.cursorName}",
|
"OR (d.name = #{q.cursorName} AND d.id > #{q.cursorDocumentId})",
|
"OR (d.name = #{q.cursorName} AND d.id = #{q.cursorDocumentId}",
|
"AND v.version_no < #{q.cursorVersionNo})",
|
"OR (d.name = #{q.cursorName} AND d.id = #{q.cursorDocumentId}",
|
"AND v.version_no = #{q.cursorVersionNo} AND v.id < #{q.cursorId}))",
|
"</if>",
|
"<if test='!q.administrator'>",
|
"AND NOT EXISTS (SELECT 1 FROM public.dms_access_exclusions e WHERE", ACTIVE_EXCLUSION,
|
"AND ((e.resource_type = 'DOCUMENT' AND e.resource_id = d.id)",
|
"OR (e.resource_type = 'FILE_VERSION' AND e.resource_id = v.id)))",
|
"AND EXISTS (SELECT 1 FROM public.dms_file_permissions p WHERE", ACTIVE_READ, PRINCIPAL_MATCH,
|
"AND p.resource_type = 'FOLDER' AND (p.resource_id = #{q.folderId}",
|
"OR (p.resource_scope = 'SUBTREE' AND p.resource_id IN (SELECT id FROM folder_ancestors))))",
|
"</if>",
|
"ORDER BY d.name, d.id, v.version_no DESC, v.id DESC LIMIT #{q.limit}",
|
"</script>"})
|
List<DmsFileCenterItemVO> selectHistoricalVersions(@Param("q") DmsFileCenterQuery query);
|
|
@Select({"<script>",
|
"WITH RECURSIVE folder_ancestors(id, parent_id) AS (",
|
"SELECT f.id, f.parent_id FROM public.dms_folders f",
|
"JOIN public.dms_documents start_document ON start_document.tenant_id = f.tenant_id",
|
"AND start_document.folder_id = f.id",
|
"WHERE f.tenant_id = #{q.tenantId} AND start_document.id = #{q.documentId}",
|
"UNION ALL SELECT parent.id, parent.parent_id FROM folder_ancestors child",
|
"JOIN public.dms_folders parent ON parent.tenant_id = #{q.tenantId}",
|
"AND parent.id = child.parent_id)",
|
"SELECT", VERSION_COLUMNS,
|
"FROM public.dms_documents d",
|
"JOIN public.dms_file_versions v ON v.tenant_id = d.tenant_id AND v.document_id = d.id",
|
"JOIN public.dms_version_states s ON s.tenant_id = v.tenant_id",
|
"AND s.document_id = v.document_id AND s.file_version_id = v.id",
|
"WHERE d.tenant_id = #{q.tenantId} AND d.id = #{q.documentId}",
|
"AND s.lifecycle_status IN ('SUPERSEDED', 'EXPIRED', 'REVOKED', 'INVALIDATED', 'OBSOLETE')",
|
"<if test='q.cursorVersionNo != null'>",
|
"AND (v.version_no < #{q.cursorVersionNo}",
|
"OR (v.version_no = #{q.cursorVersionNo} AND v.id < #{q.cursorId}))",
|
"</if>",
|
"<if test='!q.administrator'>",
|
"AND NOT EXISTS (SELECT 1 FROM public.dms_access_exclusions e WHERE", ACTIVE_EXCLUSION,
|
"AND ((e.resource_type = 'DOCUMENT' AND e.resource_id = d.id)",
|
"OR (e.resource_type = 'FILE_VERSION' AND e.resource_id = v.id)))",
|
"AND EXISTS (SELECT 1 FROM public.dms_file_permissions p WHERE", ACTIVE_READ, PRINCIPAL_MATCH,
|
"AND ((p.resource_type = 'DOCUMENT' AND p.resource_id = d.id)",
|
"OR (p.resource_type = 'FILE_VERSION' AND p.resource_id = v.id)",
|
"OR (p.resource_type = 'FOLDER' AND (p.resource_id = d.folder_id",
|
"OR (p.resource_scope = 'SUBTREE' AND p.resource_id IN (SELECT id FROM folder_ancestors))))))",
|
"</if>",
|
"ORDER BY v.version_no DESC, v.id DESC LIMIT #{q.limit}",
|
"</script>"})
|
List<DmsFileCenterItemVO> selectDocumentVersions(@Param("q") DmsFileCenterQuery query);
|
|
@Select({"<script>", SHARED_ELIGIBLE_VERSIONS,
|
"SELECT", VERSION_COLUMNS,
|
"FROM public.dms_documents d",
|
"JOIN public.dms_version_states s ON s.tenant_id = d.tenant_id AND s.document_id = d.id",
|
"JOIN public.dms_file_versions v ON v.tenant_id = s.tenant_id",
|
"AND v.document_id = s.document_id AND v.id = s.file_version_id",
|
"JOIN eligible_versions allowed ON allowed.file_version_id = v.id",
|
"WHERE d.tenant_id = #{q.tenantId}",
|
"AND s.lifecycle_status = 'EFFECTIVE' AND s.effective_from <= CURRENT_TIMESTAMP",
|
"AND (s.effective_to IS NULL OR CURRENT_TIMESTAMP < s.effective_to)",
|
"<if test='q.cursorName != null'>",
|
"AND (d.name > #{q.cursorName} OR",
|
"(d.name = #{q.cursorName} AND d.id > #{q.cursorDocumentId}))",
|
"</if>",
|
"<if test='!q.administrator'>",
|
"AND NOT EXISTS (SELECT 1 FROM public.dms_access_exclusions e WHERE", ACTIVE_EXCLUSION,
|
"AND ((e.resource_type = 'DOCUMENT' AND e.resource_id = d.id)",
|
"OR (e.resource_type = 'FILE_VERSION' AND e.resource_id = v.id)))",
|
"</if>",
|
"ORDER BY d.name, d.id LIMIT #{q.limit}",
|
"</script>"})
|
List<DmsFileCenterItemVO> selectSharedDocuments(@Param("q") DmsFileCenterQuery query);
|
|
@Select({"<script>", SHARED_ELIGIBLE_VERSIONS,
|
"SELECT", VERSION_COLUMNS,
|
"FROM public.dms_documents d",
|
"JOIN public.dms_file_versions v ON v.tenant_id = d.tenant_id AND v.document_id = d.id",
|
"JOIN public.dms_version_states s ON s.tenant_id = v.tenant_id",
|
"AND s.document_id = v.document_id AND s.file_version_id = v.id",
|
"JOIN eligible_versions allowed ON allowed.file_version_id = v.id",
|
"WHERE d.tenant_id = #{q.tenantId}",
|
"AND s.lifecycle_status IN ('SUPERSEDED', 'EXPIRED', 'REVOKED', 'INVALIDATED', 'OBSOLETE')",
|
"<if test='q.cursorName != null'>",
|
"AND (d.name > #{q.cursorName}",
|
"OR (d.name = #{q.cursorName} AND d.id > #{q.cursorDocumentId})",
|
"OR (d.name = #{q.cursorName} AND d.id = #{q.cursorDocumentId}",
|
"AND v.version_no < #{q.cursorVersionNo})",
|
"OR (d.name = #{q.cursorName} AND d.id = #{q.cursorDocumentId}",
|
"AND v.version_no = #{q.cursorVersionNo} AND v.id < #{q.cursorId}))",
|
"</if>",
|
"<if test='!q.administrator'>",
|
"AND NOT EXISTS (SELECT 1 FROM public.dms_access_exclusions e WHERE", ACTIVE_EXCLUSION,
|
"AND ((e.resource_type = 'DOCUMENT' AND e.resource_id = d.id)",
|
"OR (e.resource_type = 'FILE_VERSION' AND e.resource_id = v.id)))",
|
"</if>",
|
"ORDER BY d.name, d.id, v.version_no DESC, v.id DESC LIMIT #{q.limit}",
|
"</script>"})
|
List<DmsFileCenterItemVO> selectSharedHistoricalVersions(@Param("q") DmsFileCenterQuery query);
|
}
|
