ExportDocumentsRepositoryImpl.java

package com.tradecloud.repository.export.impl;

import com.tradecloud.domain.export.Export;
import com.tradecloud.domain.export.ExportDocument;
import com.tradecloud.dto.export.ExportDocumentSearch;
import com.tradecloud.dto.export.ExportDocumentsReportDTO;
import com.tradecloud.repository.SearchMetaParams;
import com.tradecloud.repository.base.impl.CriteriaOperation;
import com.tradecloud.repository.base.impl.CriteriaValue;
import com.tradecloud.repository.base.impl.RepositoryBaseImpl;
import com.tradecloud.repository.export.ExportDocumentsRepository;
import org.apache.log4j.Logger;
import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.hibernate.transform.ResultTransformer;
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.BigDecimal;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.*;

@Repository(value = "exportDocumentsRepository")
@Transactional(propagation = Propagation.REQUIRED, isolation = Isolation.DEFAULT)
public class ExportDocumentsRepositoryImpl extends RepositoryBaseImpl<ExportDocument, ExportDocumentSearch> implements ExportDocumentsRepository {
    private final DateFormat DF = new SimpleDateFormat("yyyy-MM-dd");

    private static final long serialVersionUID = 1L;
    private static final Logger log = Logger.getLogger(ExportDocumentsRepositoryImpl.class);
    private static final String EXPORT = "export";
    private static final String DOCUMENT_TYPE = "documentType";
    private static final String STATUS = "status";
    private static final String CREATED = "created";

    @Override
    public List<ExportDocument> findByExport(Export export) {
        String queryString = "from ExportDocument ed where ed.export = :export order by ed.created desc";
        org.hibernate.query.Query<ExportDocument> query = getSession().createQuery(queryString, ExportDocument.class);
        query.setParameter("export", export);

        return query.list();
    }

    @Override
    public boolean documentExists(Export export, ExportDocument.Type type) {
        Criteria searchCriteria = getSession().createCriteria(ExportDocument.class);
        searchCriteria.add(Restrictions.eq(EXPORT, export));
        searchCriteria.add(Restrictions.eq(DOCUMENT_TYPE, type));
        searchCriteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
        Long count = (Long) searchCriteria.setProjection(Projections.rowCount()).uniqueResult();
        return count > 0;
    }

    @Override
    public List<ExportDocument> search(ExportDocumentSearch search) {
        return searchByCriteria(search);
    }

    @Override
    public long count(ExportDocumentSearch search) {
        return countByCriteria(search);
    }

    @Override
    protected Collection<CriteriaValue> mapFieldsToValues(ExportDocumentSearch search) {
        Collection<CriteriaValue> fields = new LinkedHashSet<>();
        fields.add(new CriteriaValue(CriteriaOperation.LIKE, DOCUMENT_TYPE, search.getDocumentType()));
        fields.add(new CriteriaValue(CriteriaOperation.LIKE, STATUS, search.getStatus()));
        fields.add(new CriteriaValue(CriteriaOperation.EQUALS, EXPORT, search.getExport()));
        return fields;
    }

    public List customsWorkSheet(String shipmentNumber) {
        String sql = "select s.shipmentnumber ,ci.reference, ai.code, ai.description," +
                " ai.tariffheading, ai.weight, ai.invoicequantity, ai.invoiceprice, c.name from export s  " +
                "left join exportinvoice ei on s.id = ei.export_id " +
                "left join costsinvoice ci on ei.id = ci.id " +
                "left join costsinvoice_actualconsignment ca on ci.id = ca.costsinvoice_id " +
                "left join actualconsignment ac on ca.actualconsignments_id = ac.id " +
                "left join actualorder ao on ac.id = ao.actualconsignment_id " +
                "join actuallineitem ai on ao.id = ai.actualorder_id " +
                "left join country c on ai.countryoforigin_code = c.code " +
                " where s.shipmentnumber = '" + shipmentNumber + "'";

        return getCurrentSession().createSQLQuery(sql).list();
    }

    private Query createDocumentsVATPayableQuery(ExportDocumentSearch search, boolean count) {
        StringBuilder hql = new StringBuilder();

        if (count) {
            hql.append("select distinct(e.shipmentNumber), " +
                    "e.created, " +
                    "ec.externalReference, " +
                    "ec.name, " +
                    "e.lastTransactionValue, " +
                    "c.factoryValue," +
                    "ec.countryOfDestination.code ");
        } else {
            hql.append("select count(distinct e.shipmentNumber) ");
        }

        hql.append("from ExportInvoice ei join ei.export e join e.exportConsignee ec join e.exportCosting c where e.exportStatus = 'ACCEPTED'");

        if (search.getCountryOfDestination() != null) {
            hql.append(" and ec.countryOfDestination.code = :countryOfDestinationCode ");
        }
        if (search.getConsignee() != null) {
            hql.append(" and ec.id = :exportConsigneeId");
        }
        if (search.getCreatedDateFrom() != null) {
            hql.append(" and e.created >= :createdFrom");
        }
        if (search.getCreatedDateTo() != null) {
            hql.append(" and e.created <= :createdTo");
        }
        if (search.getOrganisationalUnit() != null) {
            hql.append(" and ei.organisationalUnitById = :orgUnit");
        }

        Query query = getSession().createQuery(hql.toString());

        if (search.getCountryOfDestination() != null) {
            query.setParameter("countryOfDestinationCode", search.getCountryOfDestination().getCode());
        }
        if (search.getConsignee() != null) {
            query.setParameter("exportConsigneeId", search.getConsignee().getId());
        }
        if (search.getCreatedDateFrom() != null) {
            query.setParameter("createdFrom", search.getCreatedDateFrom());
        }
        if (search.getCreatedDateTo() != null) {
            query.setParameter("createdTo", search.getCreatedDateTo());
        }
        if (search.getOrganisationalUnit() != null) {
            query.setParameter("orgUnit", search.getOrganisationalUnit());
        }

        return query;
    }

    @Override
    public List<ExportDocumentsReportDTO> searchDocumentsVATPayable(ExportDocumentSearch search) {
        Query query = createDocumentsVATPayableQuery(search, true);

        query.setResultTransformer(new ResultTransformer() {

            @Override
            public Object transformTuple(Object[] tuple, String[] aliases) {
                ExportDocumentsReportDTO dto = new ExportDocumentsReportDTO();

                dto.setInvoiceNumber("" + tuple[0]);
                dto.setCreatedDate((Date) tuple[1]);
                dto.setConsigneeNumber(tuple[2] != null ? "" + tuple[2] : "");
                dto.setConsigneeName("" + tuple[3]);
                double d = 0;
                if (tuple[4] != null) {
                    d = Double.parseDouble("" + tuple[4]);
                }
                dto.setConsigneeInvoiceValue(new BigDecimal(d));

                double f = 0;
                if (tuple[5] != null) {
                    f = Double.parseDouble("" + tuple[5]);
                }
                dto.setFactoryValue(new BigDecimal(f));
                //   dto.setId(new Long("" + tuple[5]));// NOTE: this is the id of
                // the costs invoice and not
                // the export document.

                return dto;
            }

            @Override
            public List transformList(List collection) {
                return collection;
            }
        });

        SearchMetaParams params = search.getSearchMetaParams();
        if (params != null) {
            query.setFirstResult(params.getRowIndex());
            query.setMaxResults(params.getRowCount());
        }

        //performing a manual sort, database asks me for too many 'group by' clauses.
        List<ExportDocumentsReportDTO> list = query.list();
        Collections.sort(list, new Comparator<ExportDocumentsReportDTO>() {
            @Override
            public int compare(ExportDocumentsReportDTO o1, ExportDocumentsReportDTO o2) {
                return o1.getCreatedDate().compareTo(o2.getCreatedDate()) + o1.getInvoiceNumber().compareTo(o2.getInvoiceNumber());
            }
        });

        return list;
    }

    @Override
    public long countDocumentsVATPayable(ExportDocumentSearch search) {
        Query query = createDocumentsVATPayableQuery(search, false);

        return new Long("" + query.list().get(0)).longValue();
    }
}