DocumentRepositoryImpl.java

package com.tradecloud.repository.dms.impl;

import com.tradecloud.authentication.User;
import com.tradecloud.authentication.UserInfo;
import com.tradecloud.domain.dms.Document;
import com.tradecloud.domain.dms.DocumentType;
import com.tradecloud.domain.dms.TransactionType;
import com.tradecloud.domain.dms.UploadDetails;
import com.tradecloud.dto.dms.DocumentManagementReportResult;
import com.tradecloud.dto.dms.DocumentManagementReportSearchDTO;
import com.tradecloud.repository.SearchMetaParams;
import com.tradecloud.repository.base.impl  .RepositoryBaseImpl;
import com.tradecloud.repository.dms.DocumentRepository;
import org.apache.commons.lang.StringUtils;
import org.hibernate.Criteria;
import org.hibernate.criterion.Restrictions;
import org.hibernate.query.NativeQuery;
import org.hibernate.transform.AliasToBeanResultTransformer;
import org.springframework.stereotype.Component;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Isolation;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import javax.persistence.criteria.*;
import java.math.BigInteger;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Date;
import java.util.List;

@Repository(value = "documentRepository")
@Transactional(propagation = Propagation.REQUIRED, isolation = Isolation.DEFAULT)
@Component(value = "documentRepository")
public class DocumentRepositoryImpl extends RepositoryBaseImpl<Document, Object> implements DocumentRepository {

    private static final String SHIPMENT_DOCUMENTS_QUERY_FROM = """
             from document d1
                     join documenttype d1t on (d1.documenttype_id = d1t.id)
                     join documentgroup d on (d.id = d1.documentgroup_id)
                     join documentgrouptype dgt on (d.documentgrouptype_id = dgt.id)
                     join shipment s on (s.reference = d.key)
                     join shipmentshippinginfo si on (s.shippinginfo_id = si.id)
                     join serviceprovider ffwd on (ffwd.id = si.freightforwarder_id)
                     join serviceprovider ca on (ca.id = si.clearingagent_id)
            where dgt.name in ('SHIPMENT SEA', 'SHIPMENT AIR')  and d1.documentName is not null""";
    private static final String SHIPMENT_DOCUMENTS_QUERY = "select s.state  as status,\n" +
            "       ffwd.name     as \"freightForwarder\",\n" +
            "       ca.name       as \"clearingAgent\",\n" +
            "       s.number,\n" +
            "       s.reference         as reference,\n" +
            "       d1.loadeddate as \"documentUploadedDate\",\n" +
            "       d1.reviewuser as \"reviewUser\",\n" +
            "       d1.reviewedDate as \"reviewedDate\",\n" +
            "       dgt.name      as \"documentGroupType\",\n" +
            "       ''     as supplier,\n" +
            "       d1.state      as \"documentState\",\n" +
            "      CAST(d1.id as varchar)      as \"documentId\",\n" +
            "       d1.documentName      as \"fileName\",\n" +
            "       si.shippingmode      as \"entityType\",\n" +
            "       s.id      as \"entityId\",\n" +
            "       d1t.name      as \"documentType\"\n" + SHIPMENT_DOCUMENTS_QUERY_FROM;

    private static final String ORDER_DOCUMENTS_QUERY_FROM = """
             from document d1
                     join documenttype d1t on (d1.documenttype_id = d1t.id)
                     join documentgroup d on (d.id = d1.documentgroup_id)
                     join documentgrouptype dgt on (d.documentgrouptype_id = dgt.id)
                     join orders o on (o.orderreference = d.key)
                     join shippinginformation si on (o.shippinginformation_id = si.id)
                     join serviceprovider ffwd on (ffwd.id = si.freightforwarder_id)
                     join serviceprovider ca on (ca.id = si.clearingagent_id)
                     join purchaseorder po on (po.id = o.id)
                     join organisationalunitsupplier ous on (po.supplier_id = ous.id)
                     join supplier s  on (ous.supplier_id = s.id)
            where dgt.name in ('ORDER')  and d1.documentName is not null
            """;
    private static final String ORDER_DOCUMENTS_QUERY = "select o.state  as status,\n" +
            "       ffwd.name     as \"freightForwarder\",\n" +
            "       ca.name       as \"clearingAgent\",\n" +
            "       o.number,\n" +
            "       o.orderreference         as reference,\n" +
            "       d1.loadeddate as \"documentUploadedDate\",\n" +
            "       d1.reviewuser as \"reviewUser\",\n" +
            "       d1.reviewedDate as \"reviewedDate\",\n" +
            "       dgt.name      as \"documentGroupType\",\n" +
            "       s.name      as supplier,\n" +
            "       d1.state      as \"documentState\",\n" +
            "       CAST(d1.id as varchar)      as \"documentId\",\n" +
            "       d1.documentName      as \"fileName\",\n" +
            "       o.id      as \"entityId\",\n" +
            "       'PURCHASE_ORDER'      as \"entityType\",\n" +
            "       d1t.name      as \"documentType\"\n" + ORDER_DOCUMENTS_QUERY_FROM;

    private static final String PRODUCT_DOCUMENTS_QUERY_FROM = """
             from document d1
                     join documenttype d1t on (d1.documenttype_id = d1t.id)
                     join documentgroup d on (d.id = d1.documentgroup_id)
                     join documentgrouptype dgt on (d.documentgrouptype_id = dgt.id)
                     join product p on (CAST(p.id as varchar) = d.key)
                     join organisationalunitsupplier ous on (p.supplier_id = ous.id)
                     join supplier s  on (ous.supplier_id = s.id)
                     join organisationalunit o on (o.id=p.organisationalunit_id)
                     where dgt.name in ('PRODUCT')  and d1.documentName is not null
            """;
    private static final String PRODUCT_DOCUMENTS_QUERY = "select p.state  as status,\n" +
            "       p.code         as reference,\n" +
            "       d1.loadeddate as \"documentUploadedDate\",\n" +
            "       d1.reviewuser as \"reviewUser\",\n" +
            "       d1.reviewedDate as \"reviewedDate\",\n" +
            "       dgt.name      as \"documentGroupType\",\n" +
            "       s.name      as supplier,\n" +
            "       ''    as \"freightForwarder\",\n" +
            "       ''       as \"clearingAgent\",\n" +
            "       '' as number,\n" +
            "       d1.state      as \"documentState\",\n" +
            "      CAST(d1.id as varchar)      as \"documentId\",\n" +
            "       d1.documentName      as \"fileName\",\n" +
            "       p.id      as \"entityId\",\n" +
            "       'PRODUCT'      as \"entityType\",\n" +
            "       d1t.name      as \"documentType\"\n" + PRODUCT_DOCUMENTS_QUERY_FROM;

    private static final String UPLOADED_DOCUMENTS_QUERY_FROM = """
             from document d1
                     join documenttype d1t on (d1.documenttype_id = d1t.id)
                     join documentgroup d on (d.id = d1.documentgroup_id)
                     join documentgrouptype dgt on (d.documentgrouptype_id = dgt.id)
                     where dgt.name in ('UPLOADED_REPORTS')  and d1.documentName is not null
            """;
    private static final String UPLOADED_DOCUMENTS_QUERY = "select \n" +
            "       d1.key as reference,\n" +
            "       d1.loadeddate as \"documentUploadedDate\",\n" +
            "       d1.reviewuser as \"reviewUser\",\n" +
            "       d1.reviewedDate as \"reviewedDate\",\n" +
            "       dgt.name as \"documentGroupType\",\n" +
            "       d1.state as \"documentState\",\n" +
            "      CAST(d1.id as varchar) as \"documentId\",\n" +
            "       d1.documentName as \"fileName\",\n" +
            "       'UPLOADED_REPORTS' as \"entityType\",\n" +
            "       d1t.name  as \"documentType\"\n" + UPLOADED_DOCUMENTS_QUERY_FROM;

    public List<Document> findByReference(String reference) {
        Criteria criteria = getCurrentSession().createCriteria(Document.class)
                .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
        if (reference != null) {
            criteria.add(Restrictions.ilike("key", "%" + reference + "%"));
        }

        List<Document> list = criteria.list();
        Collections.sort(list);
        return list;
    }

    public List<Document> DMSfindByReference(String reference) {
        Criteria criteria = getCurrentSession().createCriteria(Document.class)
                .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
        if (reference != null) {
            criteria.add(Restrictions.ilike("documentName", "%" + reference + "%"));
        }

        List<Document> list = criteria.list();
        Collections.sort(list);
        return list;
    }

    public List<Document> findByProperty(String property) {
        Criteria criteria = getCurrentSession().createCriteria(Document.class)
                .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
        if (property != null) {
            criteria.createCriteria("properties").add(Restrictions.ilike("value", "%" + property + "%"));
        }

        List<Document> list = criteria.list();
        Collections.sort(list);
        return list;
    }

    @Override
    public List<Document> findByDocumentType(DocumentType documentType) {
        List<Document> documents = (List<Document>) findByNamedQueryAndNamedParam("findDocumentByType",
                new String[]{"documentType"}, new Object[]{documentType});

        return documents;
    }

    @Override
    public List<Document> find(String reference, String groupName, String typeName) {
        return find(reference, groupName, typeName, null, null);
    }

    @Override
    public List<Document> find(String reference, String groupName, String typeName, Date uploadDateFrom, Date uploadDateTo) {
        CriteriaBuilder criteriaBuilder = getSession().getCriteriaBuilder();
        CriteriaQuery<Document> criteriaQuery = criteriaBuilder.createQuery(Document.class);
        Root<Document> root = criteriaQuery.from(Document.class);

        Predicate date = null;
        if (uploadDateFrom != null && uploadDateTo != null) {
            date = criteriaBuilder.between(root.get("loadedDate"), uploadDateFrom, uploadDateTo);
        }

        Join<User, UserInfo> childJoinGroup = root.join("documentGroup");
        Join<User, UserInfo> childJoinGroupType = childJoinGroup.join("documentGroupType");
        Join<User, UserInfo> childJoin = root.join("documentType");

        Predicate orReference = null;
        if (reference != null) {
            orReference = criteriaBuilder.or(
                    criteriaBuilder.like(root.get("key"), reference),
                    criteriaBuilder.like(childJoinGroup.get("key"), reference));
        }
        Predicate groupNamePredicate = null;
        if (groupName != null) {
            groupNamePredicate = criteriaBuilder.equal(childJoinGroupType.<String>get("name"), groupName);
        }

        Predicate andType = null;
        if (typeName != null) {
            //andType = criteriaBuilder.and(criteriaBuilder.equal(childJoin.<String>get("name"), typeName));
        }

        List<Predicate> predicates = new ArrayList<>();
        if (orReference != null)
            predicates.add(orReference);
        if (andType != null)
            predicates.add(andType);
        if (date != null)
            predicates.add(date);

        if (orReference != null && andType != null) {
            criteriaQuery.where(predicates.toArray(new Predicate[0]));
        } else if (orReference != null) {
            criteriaQuery.where(predicates.toArray(new Predicate[0]));
        } else if (typeName != null) {
            predicates.clear();
            predicates.add(criteriaBuilder.equal(childJoin.<String>get("name"), typeName));
            if (groupNamePredicate != null)
                predicates.add(groupNamePredicate);
            if (date != null)
                predicates.add(date);
            criteriaQuery.where(predicates.toArray(new Predicate[0]));
        }

        org.hibernate.query.Query<Document> q = getSession().createQuery(criteriaQuery);
        return q.list();
    }

    @Override
    public UploadDetails getDetails(Document document) {
        NativeQuery<UploadDetails> nativeQuery = getSession()
                .createNativeQuery("select uploaddetails.id,uploaddetails.created,uploaddetails.updated, null as data " +
                        "from uploaddetails right outer join document on document.details_id = uploaddetails.id " +
                        "and document.id= :documentId", UploadDetails.class);
        nativeQuery.setParameter("documentId", document.getId());
        /*nativeQuery.addScalar("id", StandardBasicTypes.LONG)
                .addScalar("created", StandardBasicTypes.DATE)
                .addScalar("updated", StandardBasicTypes.DATE)
                .setResultTransformer(Transformers.aliasToBean(UploadDetails.class));*/
        return nativeQuery.uniqueResult();
    }

    @Override
    public long count(DocumentManagementReportSearchDTO searchDTO) {
        StringBuilder queryBuilder = new StringBuilder("select count(*) " + getShipmentDocumentsQueryCount(searchDTO.getTransactionType()));
        NativeQuery query = getNativeQuery(searchDTO, queryBuilder, false);
        return ((BigInteger) query.uniqueResult()).longValue();
    }

    @Override
    public List<DocumentManagementReportResult> search(DocumentManagementReportSearchDTO searchDTO) {
        StringBuilder queryBuilder = new StringBuilder(getShipmentDocumentsQuery(searchDTO.getTransactionType()));
        NativeQuery query = getNativeQuery(searchDTO, queryBuilder, true);
        query.setResultTransformer(new AliasToBeanResultTransformer(DocumentManagementReportResult.class));
        return query.list();
    }

    private NativeQuery getNativeQuery(DocumentManagementReportSearchDTO searchDTO, StringBuilder queryBuilder, boolean search) {
        documentFilter(searchDTO, queryBuilder);

        switch (searchDTO.getTransactionType()) {
            case SHIPMENT -> shipmentFilters(searchDTO, queryBuilder);
            case ORDER -> orderFilters(searchDTO, queryBuilder);
            case PRODUCT -> productFilters(searchDTO, queryBuilder);
        }
        SearchMetaParams searchMetaParams = searchDTO.getSearchMetaParams();
        if (search) {
            addOrderBy(searchMetaParams, queryBuilder);
        }
        NativeQuery query = getSessionCustom().createNativeQuery(queryBuilder.toString());
        setDocumentParameters(searchDTO, query);

        switch (searchDTO.getTransactionType()) {
            case SHIPMENT -> shipmentOrderParameters(searchDTO, query);
            case ORDER -> {
                shipmentOrderParameters(searchDTO, query);
                setSupplierFilter(searchDTO, query);
            }
            case PRODUCT -> {
                setSupplierFilter(searchDTO, query);
                productOrderShipParameters(searchDTO, query);
            }

        }
        if (searchMetaParams != null && search) {
            if (searchMetaParams.getRowCount() != -1) {
                query.setMaxResults(searchMetaParams.getRowCount());
            }
            if (searchMetaParams.getRowIndex() != -1) {
                query.setFirstResult(searchMetaParams.getRowIndex());
            }

        }
        return query;
    }

    private static void setSupplierFilter(DocumentManagementReportSearchDTO searchDTO, NativeQuery query) {
        if (searchDTO.getSupplier() != null) {
            query.setParameter("supplierId", searchDTO.getSupplier().getId());
        }
    }

    private void addOrderBy(SearchMetaParams searchMetaParams, StringBuilder queryBuilder) {
        if (searchMetaParams != null) {
            if (searchMetaParams.getOrderBy() != null) {
//                if (searchMetaParams != null && searchMetaParams.getOrderBy() != null) {
//                    queryBuilder.append(" order by " + searchMetaParams.getOrderBy() + " ");
//                    queryBuilder.append((searchMetaParams.isAsc() ? "ASC" : "DESC"));
//                } else {
                // insert default 'order by' clause.
                // product, orders, consignment and shipment tables all have a 'created' field.
                queryBuilder.append(" order by d1.created DESC");
//                }
            }
        }
    }

    private String getShipmentDocumentsQuery(TransactionType transactionType) {
        return switch (transactionType) {
            case SHIPMENT -> SHIPMENT_DOCUMENTS_QUERY;
            case ORDER -> ORDER_DOCUMENTS_QUERY;
            case PRODUCT -> PRODUCT_DOCUMENTS_QUERY;
            case UPLOADED_REPORTS -> UPLOADED_DOCUMENTS_QUERY;
        };
    }

    private String getShipmentDocumentsQueryCount(TransactionType transactionType) {
        return switch (transactionType) {
            case SHIPMENT -> SHIPMENT_DOCUMENTS_QUERY_FROM;
            case ORDER -> ORDER_DOCUMENTS_QUERY_FROM;
            case PRODUCT -> PRODUCT_DOCUMENTS_QUERY_FROM;
            case UPLOADED_REPORTS ->  UPLOADED_DOCUMENTS_QUERY_FROM;
        };
    }

    private void shipmentOrderParameters(DocumentManagementReportSearchDTO searchDTO, NativeQuery query) {
        productOrderShipParameters(searchDTO, query);
        if (StringUtils.isNotBlank(searchDTO.getNumber())) {
            query.setParameter("number", toLIKE(searchDTO.getNumber()));
        }
        if (searchDTO.getFreightForwarder() != null) {
            query.setParameter("ffwdId", searchDTO.getFreightForwarder().getId());
        }

        if (searchDTO.getClearingAgent() != null) {
            query.setParameter("caId", searchDTO.getClearingAgent().getId());
        }
    }

    private void productOrderShipParameters(DocumentManagementReportSearchDTO searchDTO, NativeQuery query) {
        if (StringUtils.isNotBlank(searchDTO.getReference())) {
            query.setParameter("reference", toLIKE(searchDTO.getReference()));
        }
        if (searchDTO.getStatus() != null) {
            query.setParameter("status", searchDTO.getStatus());
        }
    }

    private void shipmentFilters(DocumentManagementReportSearchDTO searchDTO, StringBuilder stringBuilder) {
        if (StringUtils.isNotBlank(searchDTO.getReference())) {
            stringBuilder.append(" and s.reference ilike :reference");
        }
        if (StringUtils.isNotBlank(searchDTO.getNumber())) {
            stringBuilder.append(" and s.number ilike :number");
        }
        shipmentOrderFilter(searchDTO, stringBuilder, "s");
    }

    private void orderFilters(DocumentManagementReportSearchDTO searchDTO, StringBuilder stringBuilder) {
        if (StringUtils.isNotBlank(searchDTO.getReference())) {
            stringBuilder.append(" and o.orderreference ilike :reference");
        }
        if (StringUtils.isNotBlank(searchDTO.getNumber())) {
            stringBuilder.append(" and o.number ilike :number");
        }
        if (searchDTO.getSupplier() != null) {
            stringBuilder.append(" and ous.id=:supplierId");
        }
        shipmentOrderFilter(searchDTO, stringBuilder, "o");
    }

    private void productFilters(DocumentManagementReportSearchDTO searchDTO, StringBuilder stringBuilder) {
        if (StringUtils.isNotBlank(searchDTO.getReference())) {
            stringBuilder.append(" and p.code ilike :reference");
        }
        if (searchDTO.getSupplier() != null) {
            stringBuilder.append(" and ous.id=:supplierId");
        }
        if (searchDTO.getStatus() != null) {
            stringBuilder.append(" and p.state=:status");
        }

    }

    private void shipmentOrderFilter(DocumentManagementReportSearchDTO searchDTO, StringBuilder stringBuilder, String entityVar) {
        if (searchDTO.getFreightForwarder() != null) {
            stringBuilder.append(" and ffwd.id=:ffwdId");
        }

        if (searchDTO.getClearingAgent() != null) {
            stringBuilder.append(" and ca.id=:caId");
        }

        if (searchDTO.getStatus() != null) {
            stringBuilder.append(" and " + entityVar + ".state=:status");
        }
    }

    private void setDocumentParameters(DocumentManagementReportSearchDTO searchDTO, NativeQuery query) {
        if (searchDTO.getDocumentType() != null) {

            query.setParameter("documentTypeId", searchDTO.getDocumentType().getId());
        }
        if (searchDTO.getDocumentGroupType() != null) {
            query.setParameter("documentGroupTypeId", searchDTO.getDocumentGroupType().getId());
        }
        if (searchDTO.getDocumentLoadedDate() != null && searchDTO.getDocumentLoadedDate().getFrom() != null) {
            query.setParameter("loadedDateFrom", searchDTO.getDocumentLoadedDate().getFrom());
        }

        if (searchDTO.getDocumentLoadedDate() != null && searchDTO.getDocumentLoadedDate().getTo() != null) {
            query.setParameter("loadedDateTo", searchDTO.getDocumentLoadedDate().getTo());
        }

        if (searchDTO.getReviewedDate() != null && searchDTO.getReviewedDate().getFrom() != null) {
            query.setParameter("reviewedDateFrom", searchDTO.getReviewedDate().getFrom());
        }

        if (searchDTO.getReviewedDate() != null && searchDTO.getReviewedDate().getTo() != null) {
            query.setParameter("reviewedDateTo", searchDTO.getReviewedDate().getTo());
        }


        if (searchDTO.getDocumentState() != null) {
            query.setParameter("docState", searchDTO.getDocumentState().name());
        }

    }

    private void documentFilter(DocumentManagementReportSearchDTO searchDTO, StringBuilder stringBuilder) {
        if (searchDTO.isMandatoryOnly()) {
            stringBuilder.append(" and d1t.mandatory='t'");
        }
        if (searchDTO.getDocumentType() != null) {
            stringBuilder.append(" and d1t.id=:documentTypeId");
        }
        if (searchDTO.getDocumentGroupType() != null) {
            stringBuilder.append(" and dgt.id=:documentGroupTypeId");
        }
        if (searchDTO.getDocumentLoadedDate() != null && searchDTO.getDocumentLoadedDate().getFrom() != null) {
            stringBuilder.append(" and d1.loadeddate >=:loadedDateFrom");
        }

        if (searchDTO.getDocumentLoadedDate() != null && searchDTO.getDocumentLoadedDate().getTo() != null) {
            stringBuilder.append(" and d1.loadeddate <=:loadedDateTo");
        }

        if (searchDTO.getReviewedDate() != null && searchDTO.getReviewedDate().getFrom() != null) {
            stringBuilder.append(" and d1.revieweddate >=:reviewedDateFrom");
        }

        if (searchDTO.getReviewedDate() != null && searchDTO.getReviewedDate().getTo() != null) {
            stringBuilder.append(" and d1.revieweddate <=:reviewedDateTo");
        }

        if (searchDTO.getDocumentState() != null) {
            stringBuilder.append(" and d1.state =:docState");
        }
    }
}