CartonRepositoryImpl.java
package com.tradecloud.repository.export.carton.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.item.LineItemState;
import com.tradecloud.dto.export.ExportDocumentsInvoicesOnShipmentsDTO;
import com.tradecloud.dto.export.ExportInvoiceSearch;
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.carton.CartonRepository;
import com.tradecloud.repository.export.impl.ExportInvoiceRepositoryImpl;
import org.apache.commons.lang3.ArrayUtils;
import org.apache.log4j.Logger;
import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.criterion.CriteriaSpecification;
import org.hibernate.criterion.MatchMode;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Restrictions;
import org.hibernate.query.NativeQuery;
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.Predicate;
import javax.persistence.criteria.Root;
import java.util.*;
@Repository(value = "cartonRepository")
@Transactional(propagation = Propagation.REQUIRED, isolation = Isolation.DEFAULT)
public class CartonRepositoryImpl extends RepositoryBaseImpl<ExportInvoice, ExportInvoiceSearch> implements CartonRepository {
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) {
final ExportInvoiceSearch exportInvoiceSearch = new ExportInvoiceSearch();
exportInvoiceSearch.setExport(export);
exportInvoiceSearch.setIgnored(true);
exportInvoiceSearch.setExportRequired(true);
// Criteria searchCriteria = getSession().createCriteria(ExportInvoice.class);
// searchCriteria.add(Restrictions.eq(EXPORT, export));
// searchCriteria.add(Restrictions.isNotNull(EXPORT));
// searchCriteria.add(Restrictions.eq(IGNORED, true));
return getExportInvoices(exportInvoiceSearch);
}
@Override
public List<ExportInvoice> findByInvoiceReferenceOnly(String reference, boolean exact) {
final ExportInvoiceSearch exportInvoiceSearch = new ExportInvoiceSearch();
exportInvoiceSearch.setExactReference(exact);
exportInvoiceSearch.setReference(reference);
return getExportInvoices(exportInvoiceSearch);
}
private List<ExportInvoice> getExportInvoices(ExportInvoiceSearch exportInvoiceSearch) {
CriteriaBuilder builder = getSessionCustom().getCriteriaBuilder();
javax.persistence.criteria.CriteriaQuery criteria = builder.createQuery(ExportInvoice.class);
Root root = criteria.from(ExportInvoice.class);
criteria.select(root);
buildPredicates(exportInvoiceSearch, builder, criteria, root);
org.hibernate.query.Query query = getSessionCustom().createQuery(criteria);
List resultList = query.getResultList();
return resultList;
}
private void buildPredicates(ExportInvoiceSearch search, CriteriaBuilder builder, javax.persistence.criteria.CriteriaQuery criteria, Root root) {
List<Predicate> predicates = new ArrayList<>();
if (search.getReference() != null && !search.getReference().isEmpty()) {
if (search.getExactReference() == null || !search.getExactReference()) {
predicates.add(builder.like(builder.lower(root.get(REFERENCE)), "%" + search.getReference().toLowerCase() + "%"));
} else {
predicates.add(builder.equal(root.get(REFERENCE), search.getReference()));
}
}
if (search.getExport() != null) {
predicates.add(builder.equal(root.get(EXPORT), search.getExport()));
}
if (search.getIgnored() != null) {
predicates.add(builder.equal(root.get(IGNORED), search.getIgnored()));
}
if (search.getExportRequired() != null && search.getExportRequired()) {
predicates.add(builder.isNotNull(root.get(EXPORT)));
}
if (search.getCountry() != null) {
predicates.add(builder.equal(root.get("exportConsignee").get("countryOfDestination"),
search.getCountry()));
}
if (search.getConsignee() != null) {
predicates.add(builder.equal(root.get("exportConsignee"), search.getConsignee()));
}
if (search.getCreatedDateFrom() != null) {
predicates.add(builder.greaterThanOrEqualTo(root.get(CREATED), DateUtils.getStartOfDay(search.getCreatedDateFrom())));
}
if (search.getCreatedDateTo() != null) {
predicates.add(builder.lessThanOrEqualTo(root.get(CREATED), DateUtils.getStartOfDay(search.getCreatedDateTo())));
}
if (search.getExportCountryOfDestination() != null) {
predicates.add(builder.equal(root.get("exportConsignee").get("countryOfDestination"),
search.getExportCountryOfDestination()));
}
if (ArrayUtils.isNotEmpty(search.getDocumentStates())) {
predicates.add(root.get("state").in(search.getDocumentStates()));
} /*else {
predicates.add(builder.notEqual(root.get("state"), DocumentState.COMPLETE));
}*/
if (search.getNonexistantconsignee() != null) {
predicates.add(builder.equal(root.get("exportConsigneeReference"), search.getNonexistantconsignee()));
}
if (search.getDepotObject() != null) {
predicates.add(builder.equal(root.get(DEPOT_OBJECT), search.getDepotObject()));
}
if (search.getDivision() != null) {
predicates.add(builder.equal(root.get(DIVISION), search.getDivision()));
}
if (search.getOrganisationalUnitDivision() != null) {
predicates.add(builder.equal(root.get(ORGANISATIONAL_UNIT_DIVISION), search.getOrganisationalUnitDivision()));
}
if (search.getConsignmentName() != null) {
predicates.add(builder.like(root.get("consignment").get("reference"),
"%" + search.getConsignmentName() + "%"));
}
if (!predicates.isEmpty()) {
Predicate[] toArray = predicates.stream().toArray(Predicate[]::new);
criteria.where(toArray);
}
}
@Override
public List<ExportInvoice> findByInvoiceReference(String reference) {
final ExportInvoiceSearch exportInvoiceSearch = new ExportInvoiceSearch();
exportInvoiceSearch.setExportRequired(true);
exportInvoiceSearch.setIgnored(true);
exportInvoiceSearch.setReference(reference);
return getExportInvoices(exportInvoiceSearch);
}
@Override
public List<ExportInvoice> findByInvoiceReferenceOnly(String reference) {
final ExportInvoiceSearch exportInvoiceSearch = new ExportInvoiceSearch();
exportInvoiceSearch.setReference(reference);
return getExportInvoices(exportInvoiceSearch);
}
// @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();
}
@Override
public boolean allItemInState(Long id, LineItemState itemState) {
StringBuilder queryString = new StringBuilder("select count (*) from ExportInvoice ei join ei.actualConsignments ac ")
.append("join ac.actualOrders o join o.actualLineItems i where ei.id=:invoiceId and i.state<>:itemState");
Long count = (Long) getSessionCustom().createQuery(queryString.toString())
.setParameter("invoiceId", id)
.setParameter("itemState", itemState).uniqueResult();
return count == 0;
}
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, " +
"e.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 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(CREATED));
return criteria.list();
}
@Override
public List<ExportInvoice> search(ExportInvoiceSearch search) {
// return searchUnallocatedExportInvoices(search);
CriteriaBuilder builder = getSessionCustom().getCriteriaBuilder();
javax.persistence.criteria.CriteriaQuery criteria = builder.createQuery(ExportInvoice.class);
Root root = criteria.from(ExportInvoice.class);
criteria.select(root);
buildPredicates(search, builder, criteria, root);
SearchMetaParams metaParams = search.getSearchMetaParams();
if (metaParams != null && metaParams.getOrderBy() != null) {
if (metaParams.isAsc()) {
criteria.orderBy(builder.asc(root.get(metaParams.getOrderBy())));
} else {
criteria.orderBy(builder.desc(root.get(metaParams.getOrderBy())));
}
} else {
criteria.orderBy(builder.desc(root.get(CREATED)));
}
org.hibernate.query.Query query = getSessionCustom().createQuery(criteria);
if (metaParams != null && metaParams.isPaged()) {
query.setFirstResult(metaParams.getRowIndex());
query.setMaxResults(metaParams.getRowCount());
}
List resultList = query.getResultList();
return resultList;
}
@Override
public long count(ExportInvoiceSearch search) {
CriteriaBuilder builder = getSessionCustom().getCriteriaBuilder();
javax.persistence.criteria.CriteriaQuery criteria = builder.createQuery(Long.class);
Root root = criteria.from(ExportInvoice.class);
criteria.select(builder.count(root));
buildPredicates(search, builder, criteria, root);
org.hibernate.query.Query<Long> query = getSessionCustom().createQuery(criteria);
return query.uniqueResult();
}
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
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, null));
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;
}
/**
* 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 (ArrayUtils.isNotEmpty(search.getDocumentStates())) {
criteria.add(Restrictions.in("state", search.getDocumentStates()));
} else {
criteria.add(Restrictions.ne("state", DocumentState.COMPLETE));
}
if (search.getExportRequired() != null && search.getExportRequired()) {
criteria.add(Restrictions.isNull("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) {
org.hibernate.query.Query query = getSessionCustom().createNativeQuery(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> findByProduct(String productCode, String countryOfOriginCode, String orgCode, Long supplier) {
StringBuilder queryString = new StringBuilder("select ei from ExportInvoice ei join ei.actualConsignments ac ")
.append("join ac.actualOrders o join o.actualLineItems i where ")
.append(" ei.state ='AWAITING_TARIFFING' ")
.append(" and i.code=:pCode ")
.append(" and i.organisation=:orgCode")
.append(" and i.countryOfOrigin.code=:pCountry ")
.append(" and i.supplier.id=:pSupplier");
final org.hibernate.query.Query query = getSessionCustom().createQuery(queryString.toString())
.setParameter("pCode", productCode)
.setParameter("orgCode", orgCode)
.setParameter("pCountry", countryOfOriginCode)
.setParameter("pSupplier", supplier);
return query.getResultList();
}
@Override
public String isLinked(List<String> cartonReferences, String exportRef) {
StringBuilder queryString = new StringBuilder("select string_agg(reference, ', ') from costsinvoice ci ");
queryString.append("left join exportinvoice ei on ci.id = ei.id join export e on (e.id= ei.export_id) ");
queryString.append("where ci.reference in (:references) ");
queryString.append("and ei.export_id is not null ");
if (exportRef != null && !exportRef.isEmpty()) {
queryString.append("and e.exportersReference <> :exportersReference");
}
NativeQuery nativeQuery = getSessionCustom().createNativeQuery(queryString.toString());
nativeQuery.setParameterList("references", new ArrayList(cartonReferences));
if (exportRef != null && !exportRef.isEmpty()) {
nativeQuery.setParameter("exportersReference", exportRef);
}
return (String) nativeQuery.uniqueResult();
}
@Override
public void deleteCostsInvoice(Long id) {
NativeQuery nativeQuery = getSessionCustom().createNativeQuery("delete from costsinvoice where id=:id");
nativeQuery.setParameter("id",id).executeUpdate();
}
}