BondedServiceProviderInvoiceRepositoryImpl.java
package com.tradecloud.repository.impl;
import com.tradecloud.domain.document.Document;
import com.tradecloud.domain.document.DocumentState;
import com.tradecloud.domain.document.invoice.ActualOrder;
import com.tradecloud.domain.document.invoice.InvoiceType;
import com.tradecloud.domain.document.invoice.ServiceProviderInvoice;
import com.tradecloud.domain.document.invoice.bonded.*;
import com.tradecloud.domain.party.ServiceProvider;
import com.tradecloud.dto.clearing.BondedServiceProviderInvoiceSearch;
import com.tradecloud.dto.invoice.SupplyPaymentSearch;
import com.tradecloud.repository.BondedServiceProviderInvoiceRepository;
import com.tradecloud.schema.ISOCurrencyCodeType;
import org.apache.commons.lang3.StringUtils;
import org.hibernate.Session;
import org.hibernate.query.NativeQuery;
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 java.math.BigDecimal;
import java.util.*;
@Repository("bondedServiceProviderInvoiceRepository")
@Transactional(propagation = Propagation.REQUIRED, isolation = Isolation.DEFAULT)
public class BondedServiceProviderInvoiceRepositoryImpl
extends CostsInvoiceRepositoryImpl<ServiceProviderInvoice>
implements BondedServiceProviderInvoiceRepository {
private static final long serialVersionUID = 1L;
@Override
public void persistBondedDetails(Long invoiceId,
com.tradecloud.schema.documents.ServiceProviderInvoice dto,
ISOCurrencyCodeType invoiceCurrency) {
if (dto == null) {
throw new IllegalArgumentException("dto is required");
}
// Defensive: make sure we only persist bonded details for BONDED payloads
if (dto.getType() != null && !"BONDED".equalsIgnoreCase(dto.getType())) {
throw new IllegalStateException("persistBondedDetails called for non-BONDED dto type=" + dto.getType());
}
Session session = getCurrentSession();
// header bxireference (no entity change required)
session.createNativeQuery("update serviceproviderinvoice set bxireference = :bxi where id = :id")
.setParameter("bxi", dto.getBXIReference())
.setParameter("id", invoiceId, StandardBasicTypes.LONG)
.executeUpdate();
// replace-all deletes
session.createNativeQuery(
"delete from serviceproviderinvoice_boe_line_cost c " +
"using serviceproviderinvoice_boe_line l " +
"where c.boe_line_id = l.id and l.serviceproviderinvoice_id = :invoiceId")
.setParameter("invoiceId", invoiceId, StandardBasicTypes.LONG)
.executeUpdate();
session.createNativeQuery(
"delete from serviceproviderinvoice_boe_line where serviceproviderinvoice_id = :invoiceId")
.setParameter("invoiceId", invoiceId, StandardBasicTypes.LONG)
.executeUpdate();
session.createNativeQuery(
"delete from serviceproviderinvoice_bonded_cost where serviceproviderinvoice_id = :invoiceId")
.setParameter("invoiceId", invoiceId, StandardBasicTypes.LONG)
.executeUpdate();
boolean hasItems = dto.getItem() != null && !dto.getItem().isEmpty();
boolean hasHeaderCosts = dto.getCost() != null && !dto.getCost().isEmpty();
if (hasItems && hasHeaderCosts) {
throw new IllegalStateException("BONDED cannot combine header costs and BOE items");
}
if (hasHeaderCosts) {
// Clearing invoice (e.g. documentation fee)
for (com.tradecloud.schema.documents.Cost c : dto.getCost()) {
BigDecimal invoiceAmount = c.getInvoiceAmount() != null ? c.getInvoiceAmount() : c.getTransactionAmount();
if (invoiceAmount == null) {
throw new IllegalStateException(
"BONDED clearing cost missing invoiceAmount/transactionAmount: " + c.getCostLine());
}
session.createNativeQuery(
"insert into serviceproviderinvoice_bonded_cost " +
"(serviceproviderinvoice_id, cost_line, transaction_currency, rate_of_exchange, invoice_amount, vat_amount) " +
"values (:invoiceId, :costLine, :txCcy, :roe, :invAmt, :vatAmt)")
.setParameter("invoiceId", invoiceId, StandardBasicTypes.LONG)
.setParameter("costLine", c.getCostLine())
.setParameter("txCcy", c.getTransactionCurrency() != null ? c.getTransactionCurrency().name() : null)
.setParameter("roe", c.getRateOfExchange(), StandardBasicTypes.BIG_DECIMAL)
.setParameter("invAmt", invoiceAmount, StandardBasicTypes.BIG_DECIMAL)
.setParameter("vatAmt", c.getVatAmount(), StandardBasicTypes.BIG_DECIMAL)
.executeUpdate();
}
return;
}
// BOE duty invoice
for (com.tradecloud.schema.documents.Item item : dto.getItem()) {
Number lineIdNum = (Number) session.createNativeQuery(
"insert into serviceproviderinvoice_boe_line (" +
"serviceproviderinvoice_id, line_number, previous_line_number, order_reference, item_reference, " +
"lrn_number, mrn_number, previous_mrn_number, bill_of_entry_date, cpc_code, itac_permit, " +
"customs_value, customs_value_currency, " +
"stat_unit_qty, stat_unit_code, stat_unit2_qty, stat_unit2_code, stat_unit3_qty, stat_unit3_code, " +
"duty_country_of_origin, duty_tariff_code" +
") values (" +
":invoiceId, :lineNo, :prevLineNo, :orderRef, :itemRef, " +
":lrn, :mrn, :prevMrn, :boeDate, :cpc, :permit, " +
":custVal, :custCcy, " +
":su1Qty, :su1Code, :su2Qty, :su2Code, :su3Qty, :su3Code, " +
":coo, :tariff" +
") returning id")
.setParameter("invoiceId", invoiceId, StandardBasicTypes.LONG)
.setParameter("lineNo", item.getLineNumber())
.setParameter("prevLineNo", item.getPreviousLineNumber(), StandardBasicTypes.INTEGER)
.setParameter("orderRef", item.getOrderReference())
.setParameter("itemRef", item.getItemReference() != null ? item.getItemReference() : item.getStyleReference())
.setParameter("lrn", item.getLrnNumber())
.setParameter("mrn", item.getMrnNumber())
.setParameter("prevMrn", item.getPreviousMrnNumber())
.setParameter("boeDate",
item.getBillOfEntryDate() != null
? java.sql.Date.valueOf(item.getBillOfEntryDate().toXMLFormat().substring(0, 10))
: null,
StandardBasicTypes.DATE)
.setParameter("cpc", item.getCpcCode())
.setParameter("permit", item.getItacPermit())
.setParameter("custVal",
item.getCustomsValue() != null ? item.getCustomsValue().getValue() : null,
StandardBasicTypes.BIG_DECIMAL)
.setParameter("custCcy",
item.getCustomsValue() != null && item.getCustomsValue().getUnit() != null
? item.getCustomsValue().getUnit().name()
: null)
.setParameter("su1Qty",
item.getStatisticalUnit() != null ? item.getStatisticalUnit().getQuantity() : null,
StandardBasicTypes.BIG_DECIMAL)
.setParameter("su1Code",
item.getStatisticalUnit() != null && item.getStatisticalUnit().getUnitCode() != null
? item.getStatisticalUnit().getUnitCode().value()
: null)
.setParameter("su2Qty",
item.getStatisticalUnit2() != null ? item.getStatisticalUnit2().getQuantity() : null,
StandardBasicTypes.BIG_DECIMAL)
.setParameter("su2Code",
item.getStatisticalUnit2() != null && item.getStatisticalUnit2().getUnitCode() != null
? item.getStatisticalUnit2().getUnitCode().value()
: null)
.setParameter("su3Qty",
item.getStatisticalUnit3() != null ? item.getStatisticalUnit3().getQuantity() : null,
StandardBasicTypes.BIG_DECIMAL)
.setParameter("su3Code",
item.getStatisticalUnit3() != null && item.getStatisticalUnit3().getUnitCode() != null
? item.getStatisticalUnit3().getUnitCode().value()
: null)
.setParameter("coo",
item.getCustomsDuty() != null && item.getCustomsDuty().getCountryOfOrigin() != null
? item.getCustomsDuty().getCountryOfOrigin().value()
: null)
.setParameter("tariff",
item.getCustomsDuty() != null ? item.getCustomsDuty().getCustomsDutyTariffCode() : null)
.getSingleResult();
long boeLineId = lineIdNum.longValue();
if (item.getCost() == null) continue;
for (com.tradecloud.schema.documents.Cost c : item.getCost()) {
BigDecimal invoiceAmount = c.getInvoiceAmount() != null ? c.getInvoiceAmount() : c.getTransactionAmount();
if (invoiceAmount == null) {
throw new IllegalStateException(
"BONDED BOE cost missing invoiceAmount/transactionAmount: " + c.getCostLine());
}
session.createNativeQuery(
"insert into serviceproviderinvoice_boe_line_cost " +
"(boe_line_id, cost_line, transaction_currency, rate_of_exchange, invoice_amount, vat_amount) " +
"values (:lineId, :costLine, :txCcy, :roe, :invAmt, :vatAmt)")
.setParameter("lineId", boeLineId, StandardBasicTypes.LONG)
.setParameter("costLine", c.getCostLine())
.setParameter("txCcy", c.getTransactionCurrency() != null ? c.getTransactionCurrency().name() : null)
.setParameter("roe", c.getRateOfExchange(), StandardBasicTypes.BIG_DECIMAL)
.setParameter("invAmt", invoiceAmount, StandardBasicTypes.BIG_DECIMAL)
.setParameter("vatAmt", c.getVatAmount(), StandardBasicTypes.BIG_DECIMAL)
.executeUpdate();
}
}
}
// ===== Search support for BONDED invoices =====
@Override
public long countBondedInvoiceSearch(BondedServiceProviderInvoiceSearch search) {
Session session = getCurrentSession();
StringBuilder sql = new StringBuilder();
sql.append("select count(distinct ci.id) ");
sql.append(buildBondedSearchFromWhere(search));
NativeQuery<?> q = session.createNativeQuery(sql.toString());
applyBondedSearchParams(q, search);
Number n = (Number) q.getSingleResult();
return n != null ? n.longValue() : 0L;
}
@Override
public List<ServiceProviderInvoice> searchBondedInvoices(BondedServiceProviderInvoiceSearch search,
int first,
int pageSize,
String sortField,
boolean asc) {
Session session = getCurrentSession();
StringBuilder sql = new StringBuilder();
sql.append("select ci.id ");
sql.append(buildBondedSearchFromWhere(search));
// Sorting: keep it conservative (avoid SQL injection)
String orderBy = " order by ci.created desc";
if (StringUtils.isNotBlank(sortField)) {
String col = null;
if ("reference".equals(sortField)) {
col = "ci.reference";
} else if ("number".equals(sortField)) {
col = "ci.number";
} else if ("state".equals(sortField)) {
col = "ci.state";
} else if ("grossValue".equals(sortField)) {
col = "ci.grossvalue";
} else if ("created".equals(sortField) || "dateCreated".equals(sortField) || "datecreated".equals(sortField)) {
col = "ci.created";
} else if ("bxiReference".equals(sortField)) {
col = "spi.bxireference";
}
if (col != null) {
orderBy = " order by " + col + (asc ? " asc" : " desc");
}
}
sql.append(orderBy);
sql.append(" offset :first limit :pageSize");
NativeQuery<?> q = session.createNativeQuery(sql.toString());
applyBondedSearchParams(q, search);
q.setParameter("first", first, StandardBasicTypes.INTEGER);
q.setParameter("pageSize", pageSize, StandardBasicTypes.INTEGER);
@SuppressWarnings("unchecked")
List<Number> idNums = (List<Number>) q.getResultList();
if (idNums == null || idNums.isEmpty()) {
return Collections.emptyList();
}
List<Long> ids = idNums.stream()
.filter(Objects::nonNull)
.map(Number::longValue)
.toList();
// Loads full joined entity (costsinvoice + serviceproviderinvoice)
List<ServiceProviderInvoice> loaded = session.byMultipleIds(ServiceProviderInvoice.class).multiLoad(ids);
// Defensive: filter nulls if any disappeared concurrently
return loaded.stream().filter(Objects::nonNull).toList();
}
private String buildBondedSearchFromWhere(BondedServiceProviderInvoiceSearch search) {
StringBuilder sql = new StringBuilder();
sql.append(" from costsinvoice ci ");
sql.append(" join serviceproviderinvoice spi on spi.id = ci.id ");
sql.append(" where ci.state <> 'DELETED' ");
sql.append(" and spi.invoicetype = 'BONDED' ");
if (search == null) return sql.toString();
if (StringUtils.isNotBlank(search.getReference())) {
sql.append(" and lower(ci.reference) like :reference ");
}
if (StringUtils.isNotBlank(search.getBxiReference())) {
sql.append(" and lower(spi.bxireference) like :bxiReference ");
}
if (search.getFromCreated() != null) {
sql.append(" and ci.created >= :fromCreated ");
}
if (search.getToCreated() != null) {
sql.append(" and ci.created < :toCreatedExclusive ");
}
if (search.getStates() != null && !search.getStates().isEmpty()) {
sql.append(" and ci.state in (:states) ");
}
if (BondedSpiType.BOE.equals(search.getBondedSpiType())) {
sql.append(" and exists (select 1 from serviceproviderinvoice_boe_line bl ");
sql.append(" where bl.serviceproviderinvoice_id = spi.id) ");
} else if (BondedSpiType.CLEARING.equals(search.getBondedSpiType())) {
sql.append(" and exists (select 1 from serviceproviderinvoice_bonded_cost bc ");
sql.append(" where bc.serviceproviderinvoice_id = spi.id) ");
}
// only add clause if we will actually bind ids
boolean hasCreditorIds =
search.getCreditors() != null
&& search.getCreditors().stream().map(ServiceProvider::getId).anyMatch(Objects::nonNull);
if (hasCreditorIds) {
sql.append(" and spi.serviceprovider_id in (:creditorIds) ");
}
if (StringUtils.isNotBlank(search.getOrderReference())) {
sql.append(" and exists (select 1 from serviceproviderinvoice_boe_line bl ");
sql.append(" where bl.serviceproviderinvoice_id = spi.id ");
sql.append(" and lower(bl.order_reference) like :orderReference) ");
}
return sql.toString();
}
private void applyBondedSearchParams(NativeQuery<?> q, BondedServiceProviderInvoiceSearch search) {
if (q == null || search == null) {
return;
}
if (StringUtils.isNotBlank(search.getReference())) {
q.setParameter("reference", like(search.getReference()));
}
if (StringUtils.isNotBlank(search.getBxiReference())) {
q.setParameter("bxiReference", like(search.getBxiReference()));
}
if (StringUtils.isNotBlank(search.getOrderReference())) {
q.setParameter("orderReference", like(search.getOrderReference()));
}
if (search.getFromCreated() != null) {
q.setParameter("fromCreated", startOfDay(search.getFromCreated()), StandardBasicTypes.TIMESTAMP);
}
if (search.getToCreated() != null) {
q.setParameter("toCreatedExclusive", startOfNextDay(search.getToCreated()), StandardBasicTypes.TIMESTAMP);
}
if (search.getStates() != null && !search.getStates().isEmpty()) {
@SuppressWarnings({"rawtypes", "unchecked"})
List rawStates = (List) search.getStates();
List<String> states = new ArrayList<>();
for (Object o : rawStates) {
if (o == null) continue;
if (o instanceof Enum<?> e) states.add(e.name());
else states.add(o.toString().trim().toUpperCase());
}
if (!states.isEmpty()) {
q.setParameterList("states", states);
}
}
if (search.getCreditors() != null && !search.getCreditors().isEmpty()) {
List<Long> creditorIds = search.getCreditors().stream()
.map(ServiceProvider::getId)
.filter(id -> id != null)
.toList();
if (!creditorIds.isEmpty()) {
q.setParameterList("creditorIds", creditorIds);
}
}
}
private static Date startOfDay(Date d) {
Calendar cal = Calendar.getInstance();
cal.setTime(d);
cal.set(Calendar.HOUR_OF_DAY, 0);
cal.set(Calendar.MINUTE, 0);
cal.set(Calendar.SECOND, 0);
cal.set(Calendar.MILLISECOND, 0);
return cal.getTime();
}
private static Date startOfNextDay(Date d) {
Calendar cal = Calendar.getInstance();
cal.setTime(startOfDay(d));
cal.add(Calendar.DAY_OF_MONTH, 1);
return cal.getTime();
}
private String like(String value) {
return "%" + value.trim().toLowerCase() + "%";
}
@Override
public String findBxiReference(Long invoiceId) {
if (invoiceId == null) {
return null;
}
Session session = getCurrentSession();
return (String) session.createNativeQuery(
"select bxireference from serviceproviderinvoice where id = :id")
.setParameter("id", invoiceId, StandardBasicTypes.LONG)
.uniqueResult();
}
@Override
public String findOrderReferencesCsv(Long invoiceId) {
if (invoiceId == null) {
return null;
}
Session session = getCurrentSession();
// Postgres: string_agg is available; 'returning id' above implies Postgres.
return (String) session.createNativeQuery(
"select string_agg(distinct bl.order_reference, ', ' order by bl.order_reference) " +
"from serviceproviderinvoice_boe_line bl " +
"where bl.serviceproviderinvoice_id = :id " +
" and bl.order_reference is not null")
.setParameter("id", invoiceId, StandardBasicTypes.LONG)
.uniqueResult();
}
// ===== existing overrides =====
@Override
public List<ServiceProviderInvoice> findAllByReference(String reference) {
List<ServiceProviderInvoice> list = findAllByReference("serviceProviderInvoice.byReference", reference);
return list.stream()
.filter(spi -> spi.getInvoiceType() == InvoiceType.BONDED)
.toList();
}
@Override
public List<ActualOrder> findActualOrdersByInvoice(ServiceProviderInvoice invoice) {
// Bonded invoices aren’t shipment-backed, so this is likely not used.
return Collections.emptyList();
}
@Override
public long supplyPaymentReportCount(SupplyPaymentSearch search) {
// Not in scope for BONDED right now
return 0;
}
@Override
public List<Document> supplyPaymentReportSearch(SupplyPaymentSearch search) {
// Not in scope for BONDED right now
return Collections.emptyList();
}
@Override
public Map<Long, String> findBxiReferencesByInvoiceIds(List<Long> invoiceIds) {
if (invoiceIds == null || invoiceIds.isEmpty()) {
return Collections.emptyMap();
}
Session session = getCurrentSession();
@SuppressWarnings("unchecked")
List<Object[]> rows = (List<Object[]>) session.createNativeQuery(
"select id, bxireference " +
"from serviceproviderinvoice " +
"where id in (:ids)")
.setParameterList("ids", invoiceIds)
.getResultList();
Map<Long, String> result = new HashMap<>();
for (Object[] r : rows) {
if (r == null || r.length < 2 || r[0] == null) continue;
Long id = ((Number) r[0]).longValue();
String bxi = (String) r[1];
result.put(id, bxi);
}
return result;
}
@Override
public Map<Long, String> findOrderReferencesCsvByInvoiceIds(List<Long> invoiceIds) {
if (invoiceIds == null || invoiceIds.isEmpty()) {
return Collections.emptyMap();
}
Session session = getCurrentSession();
@SuppressWarnings("unchecked")
List<Object[]> rows = (List<Object[]>) session.createNativeQuery(
"select bl.serviceproviderinvoice_id as invoice_id, " +
" string_agg(distinct bl.order_reference, ', ' order by bl.order_reference) as order_refs " +
"from serviceproviderinvoice_boe_line bl " +
"where bl.serviceproviderinvoice_id in (:ids) " +
" and bl.order_reference is not null " +
"group by bl.serviceproviderinvoice_id")
.setParameterList("ids", invoiceIds)
.getResultList();
Map<Long, String> result = new HashMap<>();
for (Object[] r : rows) {
if (r == null || r.length < 2 || r[0] == null) continue;
Long invoiceId = ((Number) r[0]).longValue();
String csv = (String) r[1];
result.put(invoiceId, csv);
}
return result;
}
@Override
@Transactional(readOnly = true)
public List<ServiceProvider> getAllUsedServiceProvidersByBondedInvoices() {
String hql =
"select distinct sp " +
"from ServiceProviderInvoice spi " +
"join spi.serviceProvider sp " +
"where spi.invoiceType = :invoiceType " +
" and spi.state != 'DELETED' " +
"order by sp.name asc";
return getCurrentSession()
.createQuery(hql, ServiceProvider.class)
.setParameter("invoiceType", InvoiceType.BONDED)
.list();
}
@Transactional(readOnly = true)
@Override
public List<String> getAllUsedDocumentStateNamesByBondedInvoices() {
String sql =
"select distinct ci.state " +
"from costsinvoice ci " +
"join serviceproviderinvoice spi on spi.id = ci.id " +
"where spi.invoicetype = :invoiceType " +
" and ci.state <> :deleted";
@SuppressWarnings("unchecked")
List<String> rows = getCurrentSession()
.createNativeQuery(sql)
.setParameter("invoiceType", InvoiceType.BONDED.name())
.setParameter("deleted", DocumentState.DELETED.name())
.list();
return rows;
}
@Override
@Transactional(readOnly = true)
public List<BondedBoeLineEditDto> findBoeLinesWithCosts(Long invoiceId) {
if (invoiceId == null) {
return Collections.emptyList();
}
Session session = getCurrentSession();
@SuppressWarnings("unchecked")
List<Object[]> lineRows = (List<Object[]>) session.createNativeQuery(
"select l.id, l.line_number, l.previous_line_number, l.order_reference, l.item_reference, " +
" l.lrn_number, l.mrn_number, l.previous_mrn_number, l.bill_of_entry_date, l.cpc_code, l.itac_permit, " +
" l.customs_value, l.customs_value_currency, " +
" l.stat_unit_qty, l.stat_unit_code, l.stat_unit2_qty, l.stat_unit2_code, l.stat_unit3_qty, l.stat_unit3_code, " +
" l.duty_country_of_origin, l.duty_tariff_code " +
"from serviceproviderinvoice_boe_line l " +
"where l.serviceproviderinvoice_id = :invoiceId " +
"order by l.line_number")
.setParameter("invoiceId", invoiceId, StandardBasicTypes.LONG)
.getResultList();
if (lineRows == null || lineRows.isEmpty()) {
return Collections.emptyList();
}
Map<Long, BondedBoeLineEditDto> byId = new LinkedHashMap<>();
for (Object[] r : lineRows) {
if (r == null || r[0] == null) continue;
Long id = ((Number) r[0]).longValue();
BondedBoeLineEditDto dto = new BondedBoeLineEditDto();
dto.setId(id);
dto.setLineNumber((Integer) r[1]);
dto.setPreviousLineNumber((Integer) r[2]);
dto.setOrderReference((String) r[3]);
dto.setItemReference((String) r[4]);
dto.setLrnNumber((String) r[5]);
dto.setMrnNumber((String) r[6]);
dto.setPreviousMrnNumber((String) r[7]);
dto.setBillOfEntryDate((java.util.Date) r[8]); // java.sql.Date is a java.util.Date subtype
dto.setCpcCode((String) r[9]);
dto.setItacPermit((String) r[10]);
dto.setCustomsValue((BigDecimal) r[11]);
dto.setCustomsValueCurrency((String) r[12]);
dto.setStatUnitQty((BigDecimal) r[13]);
dto.setStatUnitCode((String) r[14]);
dto.setStatUnit2Qty((BigDecimal) r[15]);
dto.setStatUnit2Code((String) r[16]);
dto.setStatUnit3Qty((BigDecimal) r[17]);
dto.setStatUnit3Code((String) r[18]);
dto.setDutyCountryOfOrigin((String) r[19]);
dto.setDutyTariffCode((String) r[20]);
byId.put(id, dto);
}
// Costs
@SuppressWarnings("unchecked")
List<Object[]> costRows = (List<Object[]>) session.createNativeQuery(
"select c.boe_line_id, c.cost_line, c.transaction_currency, c.rate_of_exchange, c.invoice_amount, c.vat_amount " +
"from serviceproviderinvoice_boe_line_cost c " +
"join serviceproviderinvoice_boe_line l on l.id = c.boe_line_id " +
"where l.serviceproviderinvoice_id = :invoiceId")
.setParameter("invoiceId", invoiceId, StandardBasicTypes.LONG)
.getResultList();
if (costRows != null) {
for (Object[] r : costRows) {
if (r == null || r[0] == null) continue;
Long lineId = ((Number) r[0]).longValue();
BondedBoeLineEditDto line = byId.get(lineId);
if (line == null) continue;
BondedBoeLineCostDto c = new BondedBoeLineCostDto(
(String) r[1],
(String) r[2],
(BigDecimal) r[3],
(BigDecimal) r[4],
(BigDecimal) r[5]
);
line.getCosts().add(c);
}
}
// Initialise fixed-cost columns for UI
for (BondedBoeLineEditDto l : byId.values()) {
l.populateFixedCostsFromCosts();
}
return new ArrayList<>(byId.values());
}
@Override
@Transactional(readOnly = true)
public List<BondedHeaderCostDto> findHeaderCosts(Long invoiceId) {
if (invoiceId == null) {
return Collections.emptyList();
}
Session session = getCurrentSession();
@SuppressWarnings("unchecked")
List<Object[]> rows = (List<Object[]>) session.createNativeQuery(
"select id, cost_line, transaction_currency, rate_of_exchange, invoice_amount, vat_amount " +
"from serviceproviderinvoice_bonded_cost " +
"where serviceproviderinvoice_id = :invoiceId " +
"order by id")
.setParameter("invoiceId", invoiceId, StandardBasicTypes.LONG)
.getResultList();
if (rows == null || rows.isEmpty()) {
return Collections.emptyList();
}
List<BondedHeaderCostDto> out = new ArrayList<>();
for (Object[] r : rows) {
if (r == null) continue;
BondedHeaderCostDto dto = new BondedHeaderCostDto();
dto.setId(r[0] != null ? ((Number) r[0]).longValue() : null);
dto.setCostLine((String) r[1]);
dto.setTransactionCurrency((String) r[2]);
dto.setRateOfExchange((BigDecimal) r[3]);
dto.setInvoiceAmount((BigDecimal) r[4]);
dto.setVatAmount((BigDecimal) r[5]);
out.add(dto);
}
return out;
}
@Override
public void replaceBondedEditDetails(Long invoiceId,
String bxiReference,
List<BondedBoeLineEditDto> boeLines,
List<BondedHeaderCostDto> headerCosts) {
if (invoiceId == null) {
throw new IllegalArgumentException("invoiceId is required");
}
boeLines = (boeLines == null) ? Collections.emptyList() : boeLines;
headerCosts = (headerCosts == null) ? Collections.emptyList() : headerCosts;
if (!boeLines.isEmpty() && !headerCosts.isEmpty()) {
throw new IllegalStateException("BONDED cannot combine header costs and BOE items");
}
Session session = getCurrentSession();
// Update BXI ref
session.createNativeQuery("update serviceproviderinvoice set bxireference = :bxi where id = :id")
.setParameter("bxi", bxiReference)
.setParameter("id", invoiceId, StandardBasicTypes.LONG)
.executeUpdate();
// Replace-all deletes (same strategy as persistBondedDetails)
session.createNativeQuery(
"delete from serviceproviderinvoice_boe_line_cost c " +
"using serviceproviderinvoice_boe_line l " +
"where c.boe_line_id = l.id and l.serviceproviderinvoice_id = :invoiceId")
.setParameter("invoiceId", invoiceId, StandardBasicTypes.LONG)
.executeUpdate();
session.createNativeQuery("delete from serviceproviderinvoice_boe_line where serviceproviderinvoice_id = :invoiceId")
.setParameter("invoiceId", invoiceId, StandardBasicTypes.LONG)
.executeUpdate();
session.createNativeQuery("delete from serviceproviderinvoice_bonded_cost where serviceproviderinvoice_id = :invoiceId")
.setParameter("invoiceId", invoiceId, StandardBasicTypes.LONG)
.executeUpdate();
if (!headerCosts.isEmpty()) {
for (BondedHeaderCostDto c : headerCosts) {
session.createNativeQuery(
"insert into serviceproviderinvoice_bonded_cost " +
"(serviceproviderinvoice_id, cost_line, transaction_currency, rate_of_exchange, invoice_amount, vat_amount) " +
"values (:invoiceId, :costLine, :txCcy, :roe, :invAmt, :vatAmt)")
.setParameter("invoiceId", invoiceId, StandardBasicTypes.LONG)
.setParameter("costLine", c.getCostLine())
.setParameter("txCcy", c.getTransactionCurrency())
.setParameter("roe", c.getRateOfExchange(), StandardBasicTypes.BIG_DECIMAL)
.setParameter("invAmt", c.getInvoiceAmount(), StandardBasicTypes.BIG_DECIMAL)
.setParameter("vatAmt", c.getVatAmount(), StandardBasicTypes.BIG_DECIMAL)
.executeUpdate();
}
return;
}
// BOE items + costs
for (BondedBoeLineEditDto l : boeLines) {
Number lineIdNum = (Number) session.createNativeQuery(
"insert into serviceproviderinvoice_boe_line (" +
"serviceproviderinvoice_id, line_number, previous_line_number, order_reference, item_reference, " +
"lrn_number, mrn_number, previous_mrn_number, bill_of_entry_date, cpc_code, itac_permit, " +
"customs_value, customs_value_currency, " +
"stat_unit_qty, stat_unit_code, stat_unit2_qty, stat_unit2_code, stat_unit3_qty, stat_unit3_code, " +
"duty_country_of_origin, duty_tariff_code" +
") values (" +
":invoiceId, :lineNo, :prevLineNo, :orderRef, :itemRef, " +
":lrn, :mrn, :prevMrn, :boeDate, :cpc, :permit, " +
":custVal, :custCcy, " +
":su1Qty, :su1Code, :su2Qty, :su2Code, :su3Qty, :su3Code, " +
":coo, :tariff" +
") returning id")
.setParameter("invoiceId", invoiceId, StandardBasicTypes.LONG)
.setParameter("lineNo", l.getLineNumber())
.setParameter("prevLineNo", l.getPreviousLineNumber(), StandardBasicTypes.INTEGER)
.setParameter("orderRef", l.getOrderReference())
.setParameter("itemRef", l.getItemReference())
.setParameter("lrn", l.getLrnNumber())
.setParameter("mrn", l.getMrnNumber())
.setParameter("prevMrn", l.getPreviousMrnNumber())
.setParameter("boeDate",
l.getBillOfEntryDate() != null ? new java.sql.Date(l.getBillOfEntryDate().getTime()) : null,
StandardBasicTypes.DATE)
.setParameter("cpc", l.getCpcCode())
.setParameter("permit", l.getItacPermit())
.setParameter("custVal", l.getCustomsValue(), StandardBasicTypes.BIG_DECIMAL)
.setParameter("custCcy", l.getCustomsValueCurrency())
.setParameter("su1Qty", l.getStatUnitQty(), StandardBasicTypes.BIG_DECIMAL)
.setParameter("su1Code", l.getStatUnitCode())
.setParameter("su2Qty", l.getStatUnit2Qty(), StandardBasicTypes.BIG_DECIMAL)
.setParameter("su2Code", l.getStatUnit2Code())
.setParameter("su3Qty", l.getStatUnit3Qty(), StandardBasicTypes.BIG_DECIMAL)
.setParameter("su3Code", l.getStatUnit3Code())
.setParameter("coo", l.getDutyCountryOfOrigin())
.setParameter("tariff", l.getDutyTariffCode())
.getSingleResult();
long boeLineId = lineIdNum.longValue();
if (l.getCosts() == null) continue;
for (BondedBoeLineCostDto c : l.getCosts()) {
if (c == null) continue;
session.createNativeQuery(
"insert into serviceproviderinvoice_boe_line_cost " +
"(boe_line_id, cost_line, transaction_currency, rate_of_exchange, invoice_amount, vat_amount) " +
"values (:lineId, :costLine, :txCcy, :roe, :invAmt, :vatAmt)")
.setParameter("lineId", boeLineId, StandardBasicTypes.LONG)
.setParameter("costLine", c.getCostLine())
.setParameter("txCcy", c.getTransactionCurrency())
.setParameter("roe", c.getRateOfExchange(), StandardBasicTypes.BIG_DECIMAL)
.setParameter("invAmt", c.getInvoiceAmount(), StandardBasicTypes.BIG_DECIMAL)
.setParameter("vatAmt", c.getVatAmount(), StandardBasicTypes.BIG_DECIMAL)
.executeUpdate();
}
}
}
@Override
public void updateBxiReference(Long invoiceId, String bxiReference) {
if (invoiceId == null) {
return;
}
getCurrentSession().createNativeQuery(
"update serviceproviderinvoice set bxireference = :bxi where id = :id")
.setParameter("bxi", bxiReference)
.setParameter("id", invoiceId, org.hibernate.type.StandardBasicTypes.LONG)
.executeUpdate();
}
@Override
@SuppressWarnings("unchecked")
public List<Object[]> findBoeTariffValidationRows(Long invoiceId) {
if (invoiceId == null) {
return java.util.Collections.emptyList();
}
org.hibernate.query.NativeQuery q = getCurrentSession().createNativeQuery(
"select " +
" bl.line_number as lineNo, " +
" bl.bill_of_entry_date as boeDate, " +
" bl.duty_tariff_code as tariff " +
"from serviceproviderinvoice_boe_line bl " +
"where bl.serviceproviderinvoice_id = :id " +
"order by bl.line_number"
);
q.setParameter("id", invoiceId, org.hibernate.type.StandardBasicTypes.LONG);
q.addScalar("lineNo", org.hibernate.type.StandardBasicTypes.INTEGER);
q.addScalar("boeDate", org.hibernate.type.StandardBasicTypes.DATE);
q.addScalar("tariff", org.hibernate.type.StandardBasicTypes.STRING);
return (List<Object[]>) q.list();
}
@Override
@SuppressWarnings("unchecked")
public List<BondedBoeLineEditDto> findBoeLinesWithFixedCosts(Long invoiceId) {
if (invoiceId == null) {
return java.util.Collections.emptyList();
}
String sql =
"select " +
" bl.id, bl.line_number, bl.previous_line_number, bl.order_reference, bl.item_reference, " +
" bl.lrn_number, bl.mrn_number, bl.previous_mrn_number, bl.bill_of_entry_date, bl.cpc_code, bl.itac_permit, " +
" bl.customs_value, bl.customs_value_currency, " +
" bl.stat_unit_qty, bl.stat_unit_code, bl.stat_unit2_qty, bl.stat_unit2_code, bl.stat_unit3_qty, bl.stat_unit3_code, " +
" bl.duty_country_of_origin, bl.duty_tariff_code, " +
" max(case when c.cost_line = :vat then c.invoice_amount end) as vat_amt, " +
" max(case when c.cost_line = :duty then c.invoice_amount end) as duty_amt, " +
" max(case when c.cost_line = :doc then c.invoice_amount end) as doc_amt " +
"from serviceproviderinvoice_boe_line bl " +
"left join serviceproviderinvoice_boe_line_cost c on c.boe_line_id = bl.id " +
"where bl.serviceproviderinvoice_id = :invoiceId " +
"group by bl.id, bl.line_number, bl.previous_line_number, bl.order_reference, bl.item_reference, " +
" bl.lrn_number, bl.mrn_number, bl.previous_mrn_number, bl.bill_of_entry_date, bl.cpc_code, bl.itac_permit, " +
" bl.customs_value, bl.customs_value_currency, " +
" bl.stat_unit_qty, bl.stat_unit_code, bl.stat_unit2_qty, bl.stat_unit2_code, bl.stat_unit3_qty, bl.stat_unit3_code, " +
" bl.duty_country_of_origin, bl.duty_tariff_code " +
"order by bl.line_number";
org.hibernate.Session session = getCurrentSession();
List<Object[]> rows = (List<Object[]>) session.createNativeQuery(sql)
.setParameter("invoiceId", invoiceId, org.hibernate.type.StandardBasicTypes.LONG)
.setParameter("vat", BondedClearingCostLineType.CUSTOMS_VAT.getCode())
.setParameter("duty", BondedClearingCostLineType.CUSTOMS_DUTY.getCode())
.setParameter("doc", BondedClearingCostLineType.CUSTOMS_DOCUMENTATION_FEE.getCode())
.getResultList();
List<BondedBoeLineEditDto> result = new ArrayList<>();
for (Object[] r : rows) {
int i = 0;
BondedBoeLineEditDto dto = new BondedBoeLineEditDto();
dto.setId(r[i] != null ? ((Number) r[i]).longValue() : null);
i++;
dto.setLineNumber((Integer) r[i++]);
dto.setPreviousLineNumber((Integer) r[i++]);
dto.setOrderReference((String) r[i++]);
dto.setItemReference((String) r[i++]);
dto.setLrnNumber((String) r[i++]);
dto.setMrnNumber((String) r[i++]);
dto.setPreviousMrnNumber((String) r[i++]);
dto.setBillOfEntryDate((Date) r[i++]);
dto.setCpcCode((String) r[i++]);
dto.setItacPermit((String) r[i++]);
dto.setCustomsValue((java.math.BigDecimal) r[i++]);
dto.setCustomsValueCurrency((String) r[i++]);
dto.setStatUnitQty((java.math.BigDecimal) r[i++]);
dto.setStatUnitCode((String) r[i++]);
dto.setStatUnit2Qty((java.math.BigDecimal) r[i++]);
dto.setStatUnit2Code((String) r[i++]);
dto.setStatUnit3Qty((java.math.BigDecimal) r[i++]);
dto.setStatUnit3Code((String) r[i++]);
dto.setDutyCountryOfOrigin((String) r[i++]);
dto.setDutyTariffCode((String) r[i++]);
dto.setCustomsVatAmount((java.math.BigDecimal) r[i++]);
dto.setCustomsDutyAmount((java.math.BigDecimal) r[i++]);
dto.setDocumentationFeeAmount((java.math.BigDecimal) r[i++]);
result.add(dto);
}
return result;
}
@Override
public void replaceBoeLinesWithFixedCosts(Long invoiceId,
List<BondedBoeLineEditDto> lines,
String transactionCurrency,
java.math.BigDecimal defaultRateOfExchange) {
if (invoiceId == null) {
throw new IllegalArgumentException("invoiceId is required");
}
org.hibernate.Session session = getCurrentSession();
// delete costs first (via join), then lines
session.createNativeQuery(
"delete from serviceproviderinvoice_boe_line_cost c " +
"using serviceproviderinvoice_boe_line l " +
"where c.boe_line_id = l.id and l.serviceproviderinvoice_id = :invoiceId")
.setParameter("invoiceId", invoiceId, org.hibernate.type.StandardBasicTypes.LONG)
.executeUpdate();
session.createNativeQuery("delete from serviceproviderinvoice_boe_line where serviceproviderinvoice_id = :invoiceId")
.setParameter("invoiceId", invoiceId, org.hibernate.type.StandardBasicTypes.LONG)
.executeUpdate();
if (lines == null || lines.isEmpty()) {
return;
}
for (BondedBoeLineEditDto l : lines) {
Number lineIdNum = (Number) session.createNativeQuery(
"insert into serviceproviderinvoice_boe_line (" +
"serviceproviderinvoice_id, line_number, previous_line_number, order_reference, item_reference, " +
"lrn_number, mrn_number, previous_mrn_number, bill_of_entry_date, cpc_code, itac_permit, " +
"customs_value, customs_value_currency, " +
"stat_unit_qty, stat_unit_code, stat_unit2_qty, stat_unit2_code, stat_unit3_qty, stat_unit3_code, " +
"duty_country_of_origin, duty_tariff_code" +
") values (" +
":invoiceId, :lineNo, :prevLineNo, :orderRef, :itemRef, " +
":lrn, :mrn, :prevMrn, :boeDate, :cpc, :permit, " +
":custVal, :custCcy, " +
":su1Qty, :su1Code, :su2Qty, :su2Code, :su3Qty, :su3Code, " +
":coo, :tariff" +
") returning id")
.setParameter("invoiceId", invoiceId, org.hibernate.type.StandardBasicTypes.LONG)
.setParameter("lineNo", l.getLineNumber(), org.hibernate.type.StandardBasicTypes.INTEGER)
.setParameter("prevLineNo", l.getPreviousLineNumber(), org.hibernate.type.StandardBasicTypes.INTEGER)
.setParameter("orderRef", l.getOrderReference())
.setParameter("itemRef", l.getItemReference())
.setParameter("lrn", l.getLrnNumber())
.setParameter("mrn", l.getMrnNumber())
.setParameter("prevMrn", l.getPreviousMrnNumber())
.setParameter("boeDate", l.getBillOfEntryDate(), org.hibernate.type.StandardBasicTypes.DATE)
.setParameter("cpc", l.getCpcCode())
.setParameter("permit", l.getItacPermit())
.setParameter("custVal", l.getCustomsValue(), org.hibernate.type.StandardBasicTypes.BIG_DECIMAL)
.setParameter("custCcy", l.getCustomsValueCurrency())
.setParameter("su1Qty", l.getStatUnitQty(), org.hibernate.type.StandardBasicTypes.BIG_DECIMAL)
.setParameter("su1Code", l.getStatUnitCode())
.setParameter("su2Qty", l.getStatUnit2Qty(), org.hibernate.type.StandardBasicTypes.BIG_DECIMAL)
.setParameter("su2Code", l.getStatUnit2Code())
.setParameter("su3Qty", l.getStatUnit3Qty(), org.hibernate.type.StandardBasicTypes.BIG_DECIMAL)
.setParameter("su3Code", l.getStatUnit3Code())
.setParameter("coo", l.getDutyCountryOfOrigin())
.setParameter("tariff", l.getDutyTariffCode())
.getSingleResult();
long boeLineId = lineIdNum.longValue();
insertCostIfPresent(session, boeLineId, BondedClearingCostLineType.CUSTOMS_VAT.getCode(),
l.getCustomsVatAmount(), transactionCurrency, defaultRateOfExchange);
insertCostIfPresent(session, boeLineId, BondedClearingCostLineType.CUSTOMS_DUTY.getCode(),
l.getCustomsDutyAmount(), transactionCurrency, defaultRateOfExchange);
insertCostIfPresent(session, boeLineId, BondedClearingCostLineType.CUSTOMS_DOCUMENTATION_FEE.getCode(),
l.getDocumentationFeeAmount(), transactionCurrency, defaultRateOfExchange);
}
}
@Override
@Transactional(readOnly = true)
public Map<String, Long> findOrderIdsByOrderReferences(List<String> orderReferences) {
if (orderReferences == null || orderReferences.isEmpty()) {
return Collections.emptyMap();
}
// Defensive trim + distinct (keep stable ordering not required here)
List<String> refs = orderReferences.stream()
.filter(Objects::nonNull)
.map(String::trim)
.filter(s -> !s.isEmpty())
.distinct()
.toList();
if (refs.isEmpty()) {
return Collections.emptyMap();
}
// This matches how BusinessDisplayNodeRepositoryImpl queries Order using HQL
List<Object[]> rows = getCurrentSession()
.createQuery(
"select o.orderReference, o.id " +
"from Order o " +
"where o.orderReference in (:refs) " +
"and o.state != 'DELETED'",
Object[].class
)
.setParameterList("refs", refs)
.list();
Map<String, Long> map = new HashMap<>();
for (Object[] r : rows) {
map.put((String) r[0], (Long) r[1]);
}
return map;
}
@Override
public void replaceHeaderCosts(Long invoiceId, List<BondedHeaderCostDto> headerCosts) {
if (invoiceId == null) {
throw new IllegalArgumentException("invoiceId is required");
}
headerCosts = (headerCosts == null) ? Collections.emptyList() : headerCosts;
Session session = getCurrentSession();
// IMPORTANT: ONLY touches bonded header cost table
session.createNativeQuery(
"delete from serviceproviderinvoice_bonded_cost where serviceproviderinvoice_id = :invoiceId")
.setParameter("invoiceId", invoiceId, StandardBasicTypes.LONG)
.executeUpdate();
for (BondedHeaderCostDto c : headerCosts) {
if (c == null) continue;
session.createNativeQuery(
"insert into serviceproviderinvoice_bonded_cost " +
"(serviceproviderinvoice_id, cost_line, transaction_currency, rate_of_exchange, invoice_amount, vat_amount) " +
"values (:invoiceId, :costLine, :txCcy, :roe, :invAmt, :vatAmt)")
.setParameter("invoiceId", invoiceId, StandardBasicTypes.LONG)
.setParameter("costLine", c.getCostLine())
.setParameter("txCcy", c.getTransactionCurrency())
.setParameter("roe", c.getRateOfExchange(), StandardBasicTypes.BIG_DECIMAL)
.setParameter("invAmt", c.getInvoiceAmount(), StandardBasicTypes.BIG_DECIMAL)
.setParameter("vatAmt", c.getVatAmount(), StandardBasicTypes.BIG_DECIMAL)
.executeUpdate();
}
}
private void insertCostIfPresent(org.hibernate.Session session,
long boeLineId,
String costLine,
java.math.BigDecimal amount,
String txCurrency,
java.math.BigDecimal roe) {
if (amount == null) return;
session.createNativeQuery(
"insert into serviceproviderinvoice_boe_line_cost " +
"(boe_line_id, cost_line, transaction_currency, rate_of_exchange, invoice_amount, vat_amount) " +
"values (:lineId, :costLine, :txCcy, :roe, :invAmt, :vatAmt)")
.setParameter("lineId", boeLineId, org.hibernate.type.StandardBasicTypes.LONG)
.setParameter("costLine", costLine)
.setParameter("txCcy", txCurrency)
.setParameter("roe", roe, org.hibernate.type.StandardBasicTypes.BIG_DECIMAL)
.setParameter("invAmt", amount, org.hibernate.type.StandardBasicTypes.BIG_DECIMAL)
.setParameter("vatAmt", null, org.hibernate.type.StandardBasicTypes.BIG_DECIMAL)
.executeUpdate();
}
public void deleteBondedDetails(Long invoiceId) {
if (invoiceId == null) {
throw new IllegalArgumentException("invoiceId is required");
}
Session session = getCurrentSession();
// delete BOE cost rows via join to lines
session.createNativeQuery(
"delete from serviceproviderinvoice_boe_line_cost c " +
"using serviceproviderinvoice_boe_line l " +
"where c.boe_line_id = l.id and l.serviceproviderinvoice_id = :invoiceId")
.setParameter("invoiceId", invoiceId, StandardBasicTypes.LONG)
.executeUpdate();
// delete BOE lines
session.createNativeQuery(
"delete from serviceproviderinvoice_boe_line where serviceproviderinvoice_id = :invoiceId")
.setParameter("invoiceId", invoiceId, StandardBasicTypes.LONG)
.executeUpdate();
// delete bonded clearing costs
session.createNativeQuery(
"delete from serviceproviderinvoice_bonded_cost where serviceproviderinvoice_id = :invoiceId")
.setParameter("invoiceId", invoiceId, StandardBasicTypes.LONG)
.executeUpdate();
}
}