ExportInvoiceRepositoryImpl.java
package com.tradecloud.repository.export.impl;
import com.tradecloud.domain.base.utils.DateUtils;
import com.tradecloud.domain.document.DocumentState;
import com.tradecloud.domain.export.Export;
import com.tradecloud.domain.export.ExportInvoice;
import com.tradecloud.domain.export.ExportParty;
import com.tradecloud.domain.model.organisationalunit.OrganisationalUnit;
import com.tradecloud.domain.party.base.Address;
import com.tradecloud.domain.shipment.LandShipment;
import com.tradecloud.dto.export.ExportDocumentsInvoicesOnShipmentsDTO;
import com.tradecloud.dto.export.ExportInvoiceDTO;
import com.tradecloud.dto.export.ExportInvoiceSearch;
import com.tradecloud.dto.export.ExportInvoicesReportDTO;
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.ExportInvoiceRepository;
import org.apache.commons.lang3.ObjectUtils;
import org.apache.log4j.Logger;
import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.criterion.*;
import org.hibernate.query.NativeQuery;
import org.hibernate.transform.ResultTransformer;
import org.hibernate.transform.Transformers;
import org.hibernate.type.StandardBasicTypes;
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.CriteriaBuilder;
import javax.persistence.criteria.Root;
import java.math.BigDecimal;
import java.math.BigInteger;
import java.util.*;
@Repository(value = "exportInvoiceRepository")
@Transactional(propagation = Propagation.REQUIRED, isolation = Isolation.DEFAULT)
public class ExportInvoiceRepositoryImpl extends RepositoryBaseImpl<ExportInvoice, ExportInvoiceSearch> implements ExportInvoiceRepository {
private static final long serialVersionUID = 1L;
private static final Logger log = Logger.getLogger(ExportInvoiceRepositoryImpl.class);
private static final String REFERENCE = "reference";
private static final String CONSIGNEE = "exportConsignee";
private static final String CONSIGNEE_REFERENCE = "exportconsigneereference";
private static final String CONSIGNEE_COUNTRY_OF_DESTINATION = "exportConsignee.countryOfDestination";
private static final String CREATED = "created";
private static final String DEPOT = "exportInvoiceDepot";
private static final String DEPOT_OBJECT = "depot";
private static final String EXPORT = "export";
private static final String IGNORED = "ignored";
private static final String DIVISION = "organisationalUnit";
private static final String ORGANISATIONAL_UNIT_DIVISION = "organisationalUnitById";
private static final String CONSIGNMENT = "consignment";
private final String INCLUDE_ORG_UNIT_CHILDREN_TIER = "BUSINESS_UNIT";
@Override
public List<ExportInvoice> findByExport(Export export) {
CriteriaBuilder builder = getSession().getCriteriaBuilder();
javax.persistence.criteria.CriteriaQuery<ExportInvoice> criteria = builder.createQuery(ExportInvoice.class);
Root<ExportInvoice> root = criteria.from(ExportInvoice.class);
criteria.select(root).where(builder.equal(root.get(EXPORT), export),
builder.isNotNull(root.get(EXPORT)),
builder.notEqual(root.get("state"), DocumentState.DELETED),
builder.equal(root.get(IGNORED), true));
org.hibernate.query.Query query = getSessionCustom().createQuery(criteria);
// query.setReadOnly(true);
// query.setFlushMode(FlushMode.MANUAL);
return query.list();
}
@Override
public List<ExportInvoiceDTO> findByExportDTO(Export export) {
String query = "select inv.id, cinv.state,cinv.reference ,e.exportersReference from exportinvoice inv join" +
" costsinvoice cinv on (cinv.id=inv.id) " +
" join export e on (e.id= inv.export_id) where state<>'DELETED' and ignored and e.id=:export_id";
NativeQuery nativeQuery = getSessionCustom().createNativeQuery(query);
nativeQuery.setParameter("export_id", export.getId());
return nativeQuery.addScalar("state", StandardBasicTypes.STRING)
.addScalar("reference", StandardBasicTypes.STRING)
.addScalar("exportersReference", StandardBasicTypes.STRING)
.addScalar("id", StandardBasicTypes.LONG)
.setResultTransformer(Transformers.aliasToBean(ExportInvoiceDTO.class)).list();
}
@Override
public long findByExportCount(Export export) {
CriteriaBuilder builder = getSession().getCriteriaBuilder();
javax.persistence.criteria.CriteriaQuery<Long> criteria = builder.createQuery(Long.class);
Root<ExportInvoice> root = criteria.from(ExportInvoice.class);
criteria.select(builder.count(root)).where(builder.equal(root.get(EXPORT), export),
builder.isNotNull(root.get(EXPORT)),
builder.notEqual(root.get("state"), DocumentState.DELETED),
builder.equal(root.get(IGNORED), true));
org.hibernate.query.Query query = getSessionCustom().createQuery(criteria);
query.setReadOnly(true);
return (long) query.uniqueResult();
}
@Override
public List<ExportInvoice> findByInvoiceReference(String reference) {
return findByInvoiceReference(reference, false, true);
}
@Override
public List<ExportInvoice> findByInvoiceReference(String reference, boolean exact, boolean onShipment) {
Criteria searchCriteria = getSession().createCriteria(ExportInvoice.class);
searchCriteria.add(Restrictions.like(REFERENCE, reference, !exact ? MatchMode.ANYWHERE : MatchMode.EXACT));
searchCriteria.add(Restrictions.eq(IGNORED, onShipment));
searchCriteria.add(Restrictions.isNotNull(EXPORT));
return searchCriteria.list();
}
@Override
public List<ExportInvoice> findByInvoiceReferenceOnly(String reference) {
return findByInvoiceReferenceOnly(reference, false);
}
@Override
public List<ExportInvoice> findByInvoiceReferenceOnly(String reference, boolean exact) {
Criteria searchCriteria = getSession().createCriteria(ExportInvoice.class);
searchCriteria.add(Restrictions.like(REFERENCE, reference, !exact ? MatchMode.ANYWHERE : MatchMode.EXACT));
return searchCriteria.list();
}
@Override
public List<ExportInvoice> fullSearch(ExportInvoiceSearch search) {
Query query = createSortedQuery(search, null, mapFieldsToValues(search), CREATED, false);
return query.list();
}
@Override
public List<ExportInvoice> searchExportInvoices(ExportInvoiceSearch search) {
Criteria searchCriteria = getSession().createCriteria(ExportInvoice.class);
if (search.getReference() != null) {
if (search.isStrictReference()) {
searchCriteria.add(Restrictions.eq(REFERENCE, search.getReference()));
} else {
searchCriteria.add(Restrictions.like(REFERENCE, search.getReference(), MatchMode.ANYWHERE));
}
}
if (search.getConsignee() != null) {
searchCriteria.add(Restrictions.eq(CONSIGNEE, search.getConsignee()));
}
if (search.getDepot() != null) {
searchCriteria.add(Restrictions.eq(DEPOT, search.getDepot()));
}
if (search.getCreatedDateFrom() != null && search.getCreatedDateTo() != null) {
expandDateRangeIfEqual(search);
searchCriteria.add(Restrictions.between(CREATED, search.getCreatedDateFrom(), search.getCreatedDateTo()));
}
if (search.getDivision() != null) {
searchCriteria.add(Restrictions.eq(DIVISION, search.getDivision()));
}
if (search.getExportCountryOfDestination() != null) {
searchCriteria.add(Restrictions.eq(CONSIGNEE_COUNTRY_OF_DESTINATION, search.getExportCountryOfDestination()));
}
searchCriteria.add(Restrictions.eq(IGNORED, false));
searchCriteria.add(Restrictions.isNull(EXPORT));
searchCriteria.addOrder(Order.desc(CREATED));
SearchMetaParams searchMetaParams = search.getSearchMetaParams();
if (searchMetaParams != null) {
searchCriteria.setFirstResult(searchMetaParams.getRowIndex());
searchCriteria.setMaxResults(searchMetaParams.getRowCount());
}
return (List<ExportInvoice>) searchCriteria.list();
}
@Override
public List<ExportDocumentsInvoicesOnShipmentsDTO> searchInvoicesOnShipments(ExportInvoiceSearch search) {
Query query = searchInvoicesOnShipments(search, false);
query.setResultTransformer(Transformers.aliasToBean(ExportDocumentsInvoicesOnShipmentsDTO.class));
SearchMetaParams params = search.getSearchMetaParams();
if (params != null) {
query.setFirstResult(params.getRowIndex());
query.setMaxResults(params.getRowCount());
}
return query.list();
}
@Override
public long countInvoicesOnShipments(ExportInvoiceSearch search) {
Query query = searchInvoicesOnShipments(search, true);
return new Long("" + query.list().get(0)).longValue();
}
private Query searchInvoicesOnShipments(ExportInvoiceSearch search, boolean count) {
StringBuilder queryString = new StringBuilder();
if (count) {
queryString.append("select count (*) from exportinvoice ei " +
" left join export e on ei.export_id = e.id where ei.export_id is not null ");
} else {
queryString.append("select e.shipmentnumber as shipmentNumber, " +
"e.exportersReference as exportersReference, " +
"ep_e.name as shipmentConsignee, " +
"ci.reference as invoiceReference, " +
"ep_ei.name as invoiceConsignee, " +
"eec.transactionvalue as shipmentAmount, " +
"co.name as countryOfDestination, " +
"e.exportstatus as shipmentState, " +
"ei.consolidated as shipmentType, " +
"e.created as shipmentCreationDate " +
"from exportinvoice ei " +
"left join export e on ei.export_id = e.id " +
"left join costsinvoice ci on ei.id = ci.id " +
"left join exportCosting eec on e.exportcosting_id = eec.id " +
"left join exportParty ep_ei on ep_ei.id = ei.exportconsignee_id " +
"left join exportParty ep_e on e.exportconsignee_id = ep_e.id " +
"left join country co on e.countryofdestination_code = co.code " +
"where ei.export_id is not null ");
}
if (search.getExportShipmentNumber() != null) {
queryString.append(" and ");
queryString.append("e.shipmentnumber = :exportShipmentNumber ");
}
if (search.getExportExportersReference() != null) {
queryString.append(" and ");
queryString.append("e.exportersReference = :exportExportersReference ");
}
if (search.getConsignee() != null) {
queryString.append(" and ");
queryString.append("e.exportconsignee_id = :consignee ");
}
if (search.getReference() != null) {
queryString.append(" and ");
queryString.append("ci.reference = :invoiceReference ");
}
if (search.getExportCountryOfDestination() != null) {
queryString.append(" and ");
queryString.append("e.countryofdestination_code = :countryOfDestination ");
}
if (search.getExportState() != null) {
queryString.append(" and ");
queryString.append("e.exportstatus = :exportState ");
}
if (search.getExportCreatedDateFrom() != null) {
queryString.append(" and ");
queryString.append("e.created >= :exportCreatedDateFrom ");
}
if (search.getExportCreatedDateTo() != null) {
queryString.append(" and ");
queryString.append("e.created <= :exportCreatedDateTo ");
}
if (!count) {
queryString.append(" order by e.invoiceCreatedDate");
} else {
queryString.append(";");
}
Query query;
if (count) {
query = getSessionCustom().createSQLQuery(queryString.toString());
} else {
query = getSessionCustom().createSQLQuery(queryString.toString())
.addScalar("shipmentNumber", StandardBasicTypes.STRING)
.addScalar("exportersReference", StandardBasicTypes.STRING)
.addScalar("shipmentConsignee", StandardBasicTypes.STRING)
.addScalar("invoiceReference", StandardBasicTypes.STRING)
.addScalar("invoiceConsignee", StandardBasicTypes.STRING)
.addScalar("shipmentAmount", StandardBasicTypes.BIG_DECIMAL)
.addScalar("countryOfDestination", StandardBasicTypes.STRING)
.addScalar("shipmentCreationDate", StandardBasicTypes.DATE)
.addScalar("shipmentState", StandardBasicTypes.STRING)
.addScalar("shipmentType", StandardBasicTypes.BOOLEAN);
}
if (search.getExportShipmentNumber() != null) {
query.setParameter("exportShipmentNumber", search.getExportShipmentNumber());
}
if (search.getExportExportersReference() != null) {
query.setParameter("exportExportersReference", search.getExportExportersReference());
}
if (search.getConsignee() != null) {
query.setParameter("consignee", search.getConsignee());
}
if (search.getExportCountryOfDestination() != null) {
query.setParameter("countryOfDestination", search.getExportCountryOfDestination());
}
if (search.getExportCreatedDateFrom() != null) {
query.setParameter("exportCreatedDateFrom", DateUtils.getStartOfDay(search.getExportCreatedDateFrom()));
}
if (search.getExportCreatedDateTo() != null) {
query.setParameter("exportCreatedDateTo", DateUtils.getEndOfDay(search.getExportCreatedDateTo()));
}
if (search.getExportState() != null) {
query.setParameter("exportState", search.getExportState());
}
return query;
}
private Criteria createUnallocatedExportInvoiceSearchCriteria(ExportInvoiceSearch search) {
Criteria criteria = createInvoicesNotLinkedToShipmentCriteria(search);
if (search.getReference() != null) {
if (search.isStrictReference()) {
criteria.add(Restrictions.eq(REFERENCE, search.getReference()));
} else {
criteria.add(Restrictions.like(REFERENCE, search.getReference(), MatchMode.ANYWHERE));
}
}
if (search.getNonexistantconsignee() != null) {
criteria.add(Restrictions.eq("exportConsigneeReference", search.getNonexistantconsignee()));
}
if (search.getDepotObject() != null) {
criteria.add(Restrictions.eq(DEPOT_OBJECT, search.getDepotObject()));
}
if (search.getDivision() != null) {
criteria.add(Restrictions.eq(DIVISION, search.getDivision()));
}
if (search.getOrganisationalUnitDivision() != null) {
criteria.add(Restrictions.eq(ORGANISATIONAL_UNIT_DIVISION, search.getOrganisationalUnitDivision()));
}
if (search.getConsignmentName() != null) {
criteria.add(Restrictions.like("con.reference", search.getConsignmentName(), MatchMode.ANYWHERE));
}
return criteria;
}
@Override
public List<ExportInvoice> searchUnallocatedExportInvoices(ExportInvoiceSearch search) {
Criteria criteria = createUnallocatedExportInvoiceSearchCriteria(search);
// TES-327
SearchMetaParams searchMetaParams = search.getSearchMetaParams();
if (searchMetaParams != null) {
criteria.setFirstResult(searchMetaParams.getRowIndex());
criteria.setMaxResults(searchMetaParams.getRowCount());
}
// do not change sorting see TRM-968
criteria.addOrder(Order.desc(REFERENCE));
return criteria.list();
}
public long searchUnallocatedExportInvoicesCount(ExportInvoiceSearch search) {
Criteria criteria = createUnallocatedExportInvoiceSearchCriteria(search);
criteria.setProjection(Projections.rowCount());
return (Long) criteria.uniqueResult();
}
@Override
public List<ExportInvoice> search(ExportInvoiceSearch search) {
if (!search.isSearchAll() && search.getExport() == null) {
return searchUnallocatedExportInvoices(search);
}
log.info("Searching export invoices..");
return searchByCriteria(search);
}
@Override
public long count(ExportInvoiceSearch search) {
return countByCriteria(search);
}
private void expandDateRangeIfEqual(ExportInvoiceSearch search) {
if (search.getCreatedDateFrom() != null && search.getCreatedDateFrom().equals(search.getCreatedDateTo())) {
Calendar c = Calendar.getInstance();
c.setTime(search.getCreatedDateTo());
c.roll(Calendar.DATE, 1);
search.setCreatedDateTo(new Date(c.getTimeInMillis()));
}
}
@Override
public LandShipment createShipment(List<ExportInvoice> invoices) {
throw new UnsupportedOperationException("Not supported yet.");
}
@Override
protected Collection<CriteriaValue> mapFieldsToValues(ExportInvoiceSearch search) {
Collection<CriteriaValue> fields = new LinkedHashSet<>();
if (search.isStrictReference()) {
fields.add(new CriteriaValue(CriteriaOperation.EQUALS, REFERENCE, search.getReference()));
} else {
fields.add(new CriteriaValue(CriteriaOperation.LIKE, REFERENCE, search.getReference()));
}
fields.add(new CriteriaValue(CriteriaOperation.EQUALS, CONSIGNEE, search.getConsignee()));
fields.add(new CriteriaValue(CriteriaOperation.EQUALS, CONSIGNEE_REFERENCE, search.getNonexistantconsignee()));
fields.add(new CriteriaValue(CriteriaOperation.EQUALS, DEPOT, search.getDepot()));
fields.add(new CriteriaValue(CriteriaOperation.EQUALS, DIVISION, search.getDivision()));
if (!search.isSearchAll()) {
fields.add(new CriteriaValue(CriteriaOperation.EQUALS, IGNORED, false));
}
fields.add(new CriteriaValue(CriteriaOperation.EQUALS, EXPORT, search.getExport()));
fields.add(new CriteriaValue(CriteriaOperation.GREATER_THAN, CREATED, search.getCreatedDateFrom()));
fields.add(new CriteriaValue(CriteriaOperation.LESS_THAN, CREATED, search.getCreatedDateTo()));
if (search.getConsignmentName() != null) {
fields.add(new CriteriaValue(CriteriaOperation.LIKE, CONSIGNMENT, search.getConsignmentName()));
}
return fields;
}
public List<ExportInvoicesReportDTO> searchInvoicesNotLinkedToShipment(ExportInvoiceSearch search) {
Criteria criteria = createInvoicesNotLinkedToShipmentCriteria(search);
criteria.setProjection(Projections.projectionList()
.add(Projections.property("reference"))
.add(Projections.property(CREATED))
.add(Projections.property("ec.externalReference"))
.add(Projections.property("ec.name"))
.add(Projections.property("ec.countryOfDestination.code"))
.add(Projections.property("grossValue"))
.add(Projections.property("id")));
criteria.setResultTransformer(new ResultTransformer() {
@Override
public Object transformTuple(Object[] tuple, String[] aliases) {
ExportInvoicesReportDTO dto = new ExportInvoicesReportDTO();
dto.setNumber("" + tuple[0]);
Date createdDate = (Date) tuple[1];
dto.setCreatedDate(createdDate);
dto.setConsigneeNumber(tuple[2] != null ? "" + tuple[2] : "");
dto.setConsigneeName("" + tuple[3]);
String countryCode = tuple[4] != null ? "" + tuple[4] : "";
String countryName = "";
if (!countryCode.isEmpty()) {
if (!countryCode.equals("")) {
Query query = getSession().createQuery("select name from Country where code = :code");
query.setParameter("code", countryCode);
countryName = (String) query.list().get(0);
}
}
dto.setCountryOfDestination(countryName);
dto.setValue(new BigDecimal("" + tuple[5]).setScale(2, BigDecimal.ROUND_HALF_UP));
long daysNotShipped = DateUtils.getDaysBetween2(createdDate, new Date(System.currentTimeMillis()));
dto.setDaysNotLinkedToShipment(new BigDecimal(daysNotShipped));
dto.setId(new Long("" + tuple[6]));// 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) {
criteria.setFirstResult(params.getRowIndex());
criteria.setMaxResults(params.getRowCount());
}
criteria.addOrder(Order.desc(CREATED));
return criteria.list();
}
@Override
public long searchInvoicesByLocationCount(ExportInvoiceSearch search) {
Criteria criteria = createInvoicesByLocationCriteria(search);
criteria.setProjection(Projections.projectionList().add(Projections.property("reference")).add(Projections.property(CREATED))
.add(Projections.property("ex.shipmentNumber")).add(Projections.property("id")));
criteria.setProjection(Projections.rowCount());
return ((Long) criteria.uniqueResult());
}
@Override
public List<ExportInvoicesReportDTO> searchInvoicesByLocation(ExportInvoiceSearch search) {
Criteria criteria = createInvoicesByLocationCriteria(search);
criteria.setProjection(Projections.projectionList().add(Projections.property("reference")).add(Projections.property(CREATED))
.add(Projections.property("ex.shipmentNumber")).add(Projections.property("id")));
criteria.setResultTransformer(new ResultTransformer() {
@Override
public Object transformTuple(Object[] tuple, String[] aliases) {
ExportInvoicesReportDTO dto = new ExportInvoicesReportDTO();
String qryString = "select sum(ali.invoiceQuantity) from CostsInvoice as ci " +
"left join ci.actualConsignments as ac " +
"left join ac.actualOrders as ao " +
"left join ao.actualLineItems as ali " +
"where ci.id=:id";
Query quantityQry = getSession().createQuery(qryString);
quantityQry.setParameter("id", tuple[3]);
Query consigneeQry = getSession().createQuery("select ep from ExportInvoice ei left join ei.exportConsignee ep where ei.id = :id");
consigneeQry.setParameter("id", tuple[3]);
ExportParty exportParty = (ExportParty) consigneeQry.getSingleResult();
BigDecimal sum = (BigDecimal) quantityQry.getSingleResult();
dto.setNumber("" + tuple[0]);
Date createdDate = (Date) tuple[1];
dto.setCreatedDate(createdDate);
dto.setShipmentReference((String) ObjectUtils.firstNonNull(tuple[2], ""));
if (exportParty != null) {
dto.setConsigneeNumber((String) ObjectUtils.firstNonNull(exportParty.getCode(), tuple[2]));
dto.setConsigneeName(exportParty.getName());
if (exportParty.getCountryOfDestination() != null) {
dto.setCountryOfDestination(ObjectUtils.firstNonNull(exportParty.getCountryOfDestination().getName(),
exportParty.getCountryOfDestination().getCode()));
}
Address physicalAddress = exportParty.getPhysicalAddress();
Address deliveryAddress = exportParty.getDeliveryAddress();
dto.setConsigneeAddress(physicalAddress != null ? physicalAddress.prettyPrint() : "");
dto.setDeliveryAddress(deliveryAddress != null ? deliveryAddress.prettyPrint() : "");
}
dto.setNumberOfUnits(sum != null ? sum.intValue() : 0);
dto.setId(new Long("" + tuple[3]));
dto.setCartonsPerLocation(0);
return dto;
}
@Override
public List transformList(List collection) {
return collection;
}
});
SearchMetaParams params = search.getSearchMetaParams();
if (params != null) {
criteria.setFirstResult(params.getRowIndex());
criteria.setMaxResults(params.getRowCount());
}
criteria.addOrder(Order.desc(CREATED));
return criteria.list();
}
public long countInvoicesNotLinkedToShipment(ExportInvoiceSearch search) {
Criteria criteria = createInvoicesNotLinkedToShipmentCriteria(search);
criteria.setProjection(Projections.rowCount());
return (Long) criteria.uniqueResult();
}
/**
* NOTE: when working on this class in future.
* When working on this class ensure that both 'Exports > Shipments > Create Road Shipment' and 'Exports > Report >
* Invoices Not Linked To Shipment Report' render the same results.
*
* @param search the export invoice search object
* @return return criteria
*/
private Criteria createInvoicesNotLinkedToShipmentCriteria(ExportInvoiceSearch search) {
Criteria criteria = getSession().createCriteria(ExportInvoice.class);
criteria.createAlias("exportConsignee", "ec", CriteriaSpecification.LEFT_JOIN);
criteria.createAlias("consignment", "con", CriteriaSpecification.LEFT_JOIN);
if (search.getCountry() != null) {
criteria.add(Restrictions.eq("ec.countryOfDestination.code", search.getCountry().getCode()));
}
if (search.getConsignee() != null) {
criteria.add(Restrictions.eq("ec.id", search.getConsignee().getId()));
}
if (search.getCreatedDateFrom() != null) {
criteria.add(Restrictions.ge(CREATED, DateUtils.getStartOfDay(search.getCreatedDateFrom())));
}
if (search.getCreatedDateTo() != null) {
criteria.add(Restrictions.le(CREATED, DateUtils.getEndOfDay(search.getCreatedDateTo())));
}
if (search.getExportCountryOfDestination() != null) {
criteria.add(Restrictions.eq("ec.countryOfDestination", search.getExportCountryOfDestination()));
}
if (search.getOrganisationalUnits() != null && !search.getOrganisationalUnits().isEmpty()) {
List<OrganisationalUnit> units = search.getOrganisationalUnits();
if (units.stream().filter(o -> o.getTier().getName().equals(INCLUDE_ORG_UNIT_CHILDREN_TIER)).count() > 0) {
criteria.createAlias("organisationalUnitById", "ou");
criteria.add(Restrictions.or(
Restrictions.in("organisationalUnitById", units),
Restrictions.in("ou.parent", units)));
} else {
criteria.add(Restrictions.in("organisationalUnitById", units));
}
}
criteria.add(Restrictions.isNull("export"));
return criteria;
}
private Criteria createInvoicesByLocationCriteria(ExportInvoiceSearch search) {
Criteria criteria = getSession().createCriteria(ExportInvoice.class);
criteria.createAlias("exportConsignee", "ec", CriteriaSpecification.LEFT_JOIN);
criteria.createAlias("consignment", "con", CriteriaSpecification.LEFT_JOIN);
criteria.createAlias("export", "ex", CriteriaSpecification.LEFT_JOIN);
if (search.getExportShipmentNumber() != null) {
criteria.add(Restrictions.like("ex.shipmentNumber", search.getExportShipmentNumber(), MatchMode.ANYWHERE));
}
if (search.getCountry() != null) {
criteria.add(Restrictions.eq("ec.countryOfDestination.code", search.getCountry().getCode()));
}
if (search.getConsignee() != null) {
criteria.add(Restrictions.eq("ec.id", search.getConsignee().getId()));
}
if (search.getCreatedDateFrom() != null) {
criteria.add(Restrictions.ge(CREATED, DateUtils.getStartOfDay(search.getCreatedDateFrom())));
}
if (search.getCreatedDateTo() != null) {
criteria.add(Restrictions.le(CREATED, DateUtils.getEndOfDay(search.getCreatedDateTo())));
}
if (search.getExportCountryOfDestination() != null) {
criteria.add(Restrictions.eq("ec.countryOfDestination", search.getExportCountryOfDestination()));
}
criteria.add(Restrictions.isNotNull("exportConsignee"));
criteria.add(Restrictions.isNotNull("export"));
return criteria;
}
@Override
public void updateInvoices(ExportParty exportParty, String exportPartyCode) {
String update = "UPDATE ExportInvoice e SET e.exportConsignee = :exportConsignee WHERE e.exportConsigneeReference=:exportPartyCode";
Query query = getSessionCustom().createQuery(update);
query.setEntity("exportConsignee", exportParty);
query.setString("exportPartyCode", exportPartyCode.trim());
query.executeUpdate();
}
@Override
public void synchroniseAllExportInvoices(String tierCode) {
// String tier_id = "select id from organisationalunittier where code='" + tierName + "'";
String orgUnitByExternalRef =
"select ou.id from organisationalunit ou " + "join organisationalunit_externalreference ouer on ouer.organisationalunit_id = ou.id "
+ "join externalreference er on er.id = ouer.externalreferences_id "
+ "where er.referencevalue = e.organisationalunit and ou.tier_code ='" + tierCode + "' ";
String updateInvoiceOrgUnit2 =
"update exportinvoice e set organisationalunitbyid_id=(" + orgUnitByExternalRef + ") "
+ "where e.organisationalunitbyid_id is null";
String depot = "select code from depot d where d.code=e.exportinvoicedepot ";
String updateInvoiceDepot = "update exportinvoice e set depot_code=(" + depot + ") where e.depot_code is null";
String exportParty = "select id from exportparty p where p.externalreference=e.exportconsigneereference";
String updateInvoiceExportConsignee =
"update exportinvoice e set exportconsignee_id=(" + exportParty + ") " + "where e.exportconsignee_id is null";
update(updateInvoiceOrgUnit2);
update(updateInvoiceDepot);
update(updateInvoiceExportConsignee);
}
private void update(String updateInvoiceOrgUnit2) {
Query query = getSessionCustom().createSQLQuery(updateInvoiceOrgUnit2);
query.executeUpdate();
}
@Override
public long countInvoiceReference(String reference) {
org.hibernate.query.Query query = getSessionCustom()
.createQuery("select count(*) from ExportInvoice where reference=:reference and state<>'DELETED'")
.setParameter("reference", reference);
return (Long) query.uniqueResult();
}
@Override
public List<ExportInvoice> find(Set<Long> ids) {
org.hibernate.query.Query query = getSessionCustom()
.createQuery(" from ExportInvoice where id in (:ids) and state<>'DELETED'")
.setParameterList("ids", ids);
return query.list();
}
@Override
public List<ExportInvoiceDTO> findInvoiceDTO(String reference) {
String query = "select cinv.state,cinv.reference ,e.exportersReference from exportinvoice inv join costsinvoice cinv on (cinv.id=inv.id) " +
" join export e on (e.id= inv.export_id) where state<>'DELETED' and ignored and e.exportersReference=:reference";
NativeQuery nativeQuery = getSessionCustom().createNativeQuery(query);
nativeQuery.setParameter("reference", reference);
return nativeQuery.addScalar("state", StandardBasicTypes.STRING)
.addScalar("reference", StandardBasicTypes.STRING)
.addScalar("exportersReference", StandardBasicTypes.STRING)
.setResultTransformer(Transformers.aliasToBean(ExportInvoiceDTO.class)).list();
}
@Override
public List<ExportInvoiceDTO> findInvoiceNotLinkedDTO(String excludeExportReference, List<String> cartonRefs) {
String query = "select cinv.state,cinv.reference,e.exportersReference from exportinvoice inv join costsinvoice cinv on (cinv.id=inv.id) " +
" left join export e on (e.id= inv.export_id) where state<>'DELETED' and ignored" +
" and (e.id is null or e.exportersReference<>:reference) " +
"and cinv.reference in (:cartonRefs)";
NativeQuery nativeQuery = getSessionCustom().createNativeQuery(query);
nativeQuery.setParameter("reference", excludeExportReference);
nativeQuery.setParameterList("cartonRefs", cartonRefs);
return nativeQuery.addScalar("state", StandardBasicTypes.STRING)
.addScalar("reference", StandardBasicTypes.STRING)
.addScalar("exportersReference", StandardBasicTypes.STRING)
.setResultTransformer(Transformers.aliasToBean(ExportInvoiceDTO.class)).list();
}
@Override
public long countDistinctConIncoterms(List<Long> collect) {
String query = "select count(distinct(i.incoterm_code)) from exportinvoice inv join costsinvoice cinv on (cinv.id=inv.id) " +
" join consignment c on (c.id= cinv.consignment_id) join consignmentshippinginfo i on (c.shippinginfo_id=i.id) " +
"where cinv.id in (:exportInvIds)";
NativeQuery nativeQuery = getSessionCustom().createNativeQuery(query);
nativeQuery.setParameterList("exportInvIds", collect);
return ((BigInteger) nativeQuery.uniqueResult()).longValue();
}
}