BOERepositoryImpl.java

package com.tradecloud.repository.impl;

import com.tradecloud.authentication.MultiTenantUtil;
import com.tradecloud.domain.base.utils.DateUtils;
import com.tradecloud.domain.export.EdifactStatus;
import com.tradecloud.domain.infrastructure.persistence.CriteriaBuilder;
import com.tradecloud.domain.model.organisationalunit.OrganisationalUnit;
import com.tradecloud.domain.shipment.clearing.BOEReport;
import com.tradecloud.domain.shipment.clearing.BOEReportLine;
import com.tradecloud.domain.shipment.clearing.CustomsDeclaration;
import com.tradecloud.dto.shipment.BOESearch;
import com.tradecloud.repository.BOERepository;
import com.tradecloud.repository.base.impl.RepositoryBaseImpl;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
import org.hibernate.Criteria;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.Restrictions;
import org.hibernate.query.NativeQuery;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Isolation;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import java.math.BigInteger;
import java.text.SimpleDateFormat;
import java.util.List;
import java.util.Set;
import java.util.stream.Collectors;

@Repository(value = "boeRepository")
@Transactional(propagation = Propagation.REQUIRED, isolation = Isolation.DEFAULT)
public class BOERepositoryImpl extends RepositoryBaseImpl<BOEReportLine, BOESearch> implements BOERepository {

    private static final long serialVersionUID = 1L;
    private Logger log = Logger.getLogger(BOERepositoryImpl.class);
    private SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

    @Override
    public List<BOEReportLine> shipmentStatusSearch(BOESearch search) {
        DetachedCriteria criteria = DetachedCriteria.forClass(BOEReportLine.class, "boeReportLine");
        criteria.addOrder(org.hibernate.criterion.Order.asc("id"));
        addSearchRestrictions(criteria, search);

        List<BOEReportLine> boeReportLines = getExecutableCriteriaList(criteria, search.getSearchMetaParams(), "id", true);
        return boeReportLines;
    }

    private void addSearchRestrictions(DetachedCriteria criteria, BOESearch search) {
        criteria.createAlias("boeReport", "breport");

        if (search.getTypeFilter() != null) {
            switch (search.getTypeFilter()) {
                case "1":
                    criteria.add(Restrictions.eq("breport.imports", search.isImports()));
                    break;
                case "2":
                    criteria.add(Restrictions.ne("breport.imports", search.isImports()));
                    break;
                default:
                    //nothing selected = 0, no filter
            }
        } else
            criteria.add(Restrictions.eq("breport.imports", search.isImports()));

        if (search.getEdifactStatus() == null) {
            //criteria.add(Restrictions.eq("customsStatus", EdifactStatus._1.getStatusNumber().toString()));
        } else {
            criteria.add(Restrictions.eq("customsStatus", search.getEdifactStatus().getStatusNumber().toString()));
        }

        if (search.getPaymentMethod() != null) {
            criteria.add(Restrictions.eq("paymentMethod", search.getPaymentMethod().getLabel()));
        }
        if (search.getEdifactStatus() != null) {
            criteria.add(Restrictions.eq("customsStatus", search.getEdifactStatus().getStatusNumber().toString()));
        }
        if (search.getFreightForwarder() != null) {
            criteria.add(Restrictions.eq("freightForwarder", search.getFreightForwarder().getName()));
        }
        if (search.getSupplier() != null) {
            criteria.add(Restrictions.eq("supplierName", search.getSupplier().getName()));
        }

        if (search.isFilteredByUserOrg()) {
            Set<OrganisationalUnit> organisationalUnits = MultiTenantUtil.getActiveUser().getOrganisationalUnits();
            if (organisationalUnits.isEmpty()) {
                throw new IllegalArgumentException("User org units not set");
            }

            if (CollectionUtils.isNotEmpty(search.getOrganisationalUnitList())) {
                search.getOrganisationalUnitList().removeIf(o -> !organisationalUnits.contains(o));
                criteria.add(Restrictions.in("division", search.getOrganisationalUnitList().stream()
                        .map(o -> o.getNameCode()).collect(Collectors.toList())));
            } else {
                criteria.add(Restrictions.in("division", organisationalUnits.stream()
                        .map(o -> o.getNameCode()).collect(Collectors.toList())));
            }

        } else if (search.getOrganisationalUnit() != null) {
            if (search.getOrganisationalUnit() != null) {
                criteria.add(Restrictions.eq("division", search.getOrganisationalUnit().getNameCode()));
            } else if (search.getOrganisationalUnitList() != null && !search.getOrganisationalUnitList().isEmpty()) {
                criteria.add(Restrictions.in("division", search.getOrganisationalUnitList().stream()
                        .map(o -> o.getNameCode()).collect(Collectors.toList())));
            }
        }

        if (search.getClearingAgent() != null) {
            criteria.add(Restrictions.eq("clearingAgent", search.getClearingAgent().getName()));
        }
        if (search.getShipmentReference() != null) {
            criteria.add(Restrictions.like("shipmentReference", StringUtils.trim("%" + search.getShipmentReference() + "%")));
        }
        if (search.getMrnnumber() != null) {
            criteria.add(Restrictions.like("mrn", StringUtils.trim("%" + search.getMrnnumber() + "%")));
        }

        List<Long> reportLatestVersion = getReportLatestVersion(search);
        if (reportLatestVersion != null && !reportLatestVersion.isEmpty())
            criteria.add(Restrictions.in("breport.id", reportLatestVersion));

        CriteriaBuilder.addDateRangeCriteria(criteria, "customsStatusDate", search.getCreatedDateFrom(), search.getCreatedDateTo(), true);
        criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
    }

    @Override
    public long shipmentStatusCount(BOESearch search) {
        DetachedCriteria criteria = DetachedCriteria.forClass(BOEReportLine.class);
        addSearchRestrictions(criteria, search);
        //log.debug(criteriaToSQLQuery(criteria));
        long count = getExecutableCriteriaCount(criteria);
        log.debug("Count for Class:" + CustomsDeclaration.class + " is " + count);
        return count;
    }

    @Override
    public BOEReport find(CustomsDeclaration customsDeclaration) {
        String s = getLatestReportQuery();
        String sql = s + "and br.customsdeclaration_id = :customsdeclaration_id limit 1";
        BigInteger reportId = (BigInteger) getCurrentSession().createNativeQuery(sql)
                .setParameter("customsdeclaration_id", customsDeclaration.getId())
                .setParameter("eventtype", customsDeclaration.getEdifactStatus().name()).uniqueResult();
        if (reportId != null) {
            return (BOEReport) getCurrentSession().createQuery("select p from BOEReport p where p.id=:id ")
                    .setParameter("id", reportId.longValue()).uniqueResult();
        }
        return null;
    }

    private List<Long> getReportLatestVersion(BOESearch boeSearch) {
        String s = getLatestReportQuery();
        List<BigInteger> list;

        if (boeSearch.getCreatedDateFrom() != null &&
                boeSearch.getCreatedDateTo() != null) {
            String sql = s +
                    "and sarsevent.created >= :from and sarsevent.created<= :to";

            NativeQuery nativeQuery = getCurrentSession().createNativeQuery(sql)
                    .setParameter("from", DateUtils.getStartOfDay(boeSearch.getCreatedDateFrom()))
                    .setParameter("to", DateUtils.getEndOfDay(boeSearch.getCreatedDateTo()));
            if (boeSearch != null && boeSearch.getEdifactStatus() != null) {
                nativeQuery.setParameter("eventtype", boeSearch.getEdifactStatus().name());
            } else {
                nativeQuery.setParameter("eventtype", EdifactStatus._1.name());
            }
            list = (List<BigInteger>) nativeQuery.list();
        } else {
            NativeQuery nativeQuery = getCurrentSession().createNativeQuery(s);

            if (boeSearch != null && boeSearch.getEdifactStatus() != null) {
                nativeQuery.setParameter("eventtype", boeSearch.getEdifactStatus().name());
            } else {
                nativeQuery.setParameter("eventtype", EdifactStatus._1.name());
            }

            list = (List<BigInteger>) nativeQuery.list();
        }

        return list.stream().map(BigInteger::longValue).collect(Collectors.toList());
    }

    private String getLatestReportQuery() {
        String s = "select br.id from boereport br join (select customsdeclaration_id, max(created) " +
                "from boereport group by customsdeclaration_id) sbr " +
                "on br.customsdeclaration_id = sbr.customsdeclaration_id and br.created = sbr.max " +
                "join customsdeclaration on br.customsdeclaration_id = customsdeclaration.id " +
                "join customsdeclaration_sarsevent on customsdeclaration.id = customsdeclaration_sarsevent.customsdeclaration_id " +
                "join sarsevent on customsdeclaration_sarsevent.events_id = sarsevent.id where eventtype=:eventtype ";
        return s;
    }

    @Override
    //TODO: fix this
    public void deleteFor(Long declarationId) {
        String queryString = "select distinct(id) from boereport where customsdeclaration_id= :id";
        NativeQuery nativeQuery = getCurrentSession().createNativeQuery(queryString);
        List<BigInteger> list = nativeQuery.setParameter("id", declarationId).getResultList();

        for (BigInteger id : list) {
            BOEReport report = getCurrentSession().get(BOEReport.class, id.longValue());
            getCurrentSession().delete(report);
            getCurrentSession().flush();
        }
    }
}