Dépôt officiel du code source de l'ERP OpenConcerto
svn://code.openconcerto.org/openconcerto
(root)/trunk/OpenConcerto/src/org/openconcerto/erp/core/finance/payment/element/SDDMessageSQLElement.java @ 156 - Rev 151
Rev 156 | Go to most recent revision | Blame | Compare with Previous | Last modification | View Log | RSS feed
/** DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS HEADER.** Copyright 2011 OpenConcerto, by ILM Informatique. All rights reserved.** The contents of this file are subject to the terms of the GNU General Public License Version 3* only ("GPL"). You may not use this file except in compliance with the License. You can obtain a* copy of the License at http://www.gnu.org/licenses/gpl-3.0.html See the License for the specific* language governing permissions and limitations under the License.** When distributing the software, include this License Header Notice in each file.*/package org.openconcerto.erp.core.finance.payment.element;import org.openconcerto.erp.config.ComptaPropsConfiguration;import org.openconcerto.erp.core.common.element.ComptaSQLConfElement;import org.openconcerto.erp.core.sales.invoice.element.SaisieVenteFactureSQLElement;import org.openconcerto.sql.element.SQLComponent;import org.openconcerto.sql.element.UISQLComponent;import org.openconcerto.sql.model.ConnectionHandlerNoSetup;import org.openconcerto.sql.model.DBRoot;import org.openconcerto.sql.model.SQLDataSource;import org.openconcerto.sql.model.SQLField;import org.openconcerto.sql.model.SQLRow;import org.openconcerto.sql.model.SQLRowAccessor;import org.openconcerto.sql.model.SQLRowListRSH;import org.openconcerto.sql.model.SQLRowValues;import org.openconcerto.sql.model.SQLRowValuesListFetcher;import org.openconcerto.sql.model.SQLSchema;import org.openconcerto.sql.model.SQLSelect;import org.openconcerto.sql.model.SQLSelect.LockStrength;import org.openconcerto.sql.model.SQLSelectJoin;import org.openconcerto.sql.model.SQLTable;import org.openconcerto.sql.model.Where;import org.openconcerto.sql.preferences.SQLPreferences;import org.openconcerto.sql.request.SQLFieldTranslator;import org.openconcerto.sql.utils.SQLCreateTable;import org.openconcerto.sql.utils.SQLUtils;import org.openconcerto.sql.view.list.IListe;import org.openconcerto.sql.view.list.IListeAction.IListeEvent;import org.openconcerto.sql.view.list.RowAction;import org.openconcerto.utils.CollectionMap2Itf.ListMapItf;import org.openconcerto.utils.DecimalUtils;import org.openconcerto.utils.ExceptionHandler;import org.openconcerto.utils.FileUtils;import org.openconcerto.utils.ListMap;import org.openconcerto.utils.StringUtils;import org.openconcerto.utils.TimeUtils;import org.openconcerto.utils.Tuple2;import org.openconcerto.utils.XMLDateFormat;import org.openconcerto.utils.cc.ITransformer;import org.openconcerto.utils.i18n.Grammar_fr;import org.openconcerto.xml.JDOM2Utils;import java.awt.Component;import java.awt.event.ActionEvent;import java.io.File;import java.io.IOException;import java.math.BigDecimal;import java.sql.Clob;import java.sql.SQLException;import java.text.DateFormat;import java.text.SimpleDateFormat;import java.util.ArrayList;import java.util.Calendar;import java.util.Collection;import java.util.Collections;import java.util.Date;import java.util.HashMap;import java.util.HashSet;import java.util.List;import java.util.Map;import java.util.Map.Entry;import java.util.NavigableMap;import java.util.Set;import java.util.TreeMap;import java.util.prefs.Preferences;import javax.swing.AbstractAction;import javax.swing.JFileChooser;import org.jdom2.Document;import org.jdom2.Element;import org.jdom2.Namespace;public final class SDDMessageSQLElement extends ComptaSQLConfElement {static final String TABLE_NAME = "SEPA_DIRECT_DEBIT_MESSAGE";static public final String XML_LOCATION_PREF_KEY = "SDD.XML.location";public static final String SERIAL_MD = "SDD_MESSAGE_SERIAL";public static SQLCreateTable getCreateTable(final DBRoot root) {if (root.contains(TABLE_NAME))return null;final SQLCreateTable res = new SQLCreateTable(root, TABLE_NAME);res.addVarCharColumn("MessageIdentification", 35);res.addColumn("CreationDateTime", res.getSyntax().getDateAndTimeType(), null, false);res.addIntegerColumn("NumberOfTransactions", 0);res.addDecimalColumn("ControlSum", 16, 6, BigDecimal.ZERO, false);res.addColumn("XML", res.getSyntax().getTypeNames(Clob.class).iterator().next(), null, false);return res;}private final String prefsPath;private final SQLRow rowSociété;private final SQLPreferences dbPrefs;private final SQLFieldTranslator fieldTranslator;public SDDMessageSQLElement(final ComptaPropsConfiguration conf) {super(conf.getRootSociete().findTable(TABLE_NAME, true));this.prefsPath = conf.getAppID() + '/' + this.getCode().replace('.', '/');this.getRowActions().add(new RowAction.PredicateRowAction(new AbstractAction("Exporter…") {@Overridepublic void actionPerformed(ActionEvent e) {final IListe l = IListe.get(e);// XML field values are quite large so only fetch them when neededfinal SQLTable t = l.getSource().getPrimaryTable();final SQLSelect sel = new SQLSelect().addSelectStar(t);sel.setWhere(new Where(t.getKey(), l.getSelection().getUserSelectedIDs()));exportXML(l, SQLRowListRSH.execute(sel));}}, true, false).setPredicate(IListeEvent.getNonEmptySelectionPredicate()));this.rowSociété = conf.getRowSociete();this.dbPrefs = new SQLPreferences(conf.getRootSociete());this.fieldTranslator = conf.getTranslator();}public final Preferences getPreferences() {return Preferences.userRoot().node(this.prefsPath);}@Overrideprotected List<String> getListFields() {final List<String> l = new ArrayList<String>();l.add("MessageIdentification");l.add("CreationDateTime");l.add("NumberOfTransactions");l.add("ControlSum");return l;}@Overrideprotected List<String> getComboFields() {final List<String> l = new ArrayList<String>();l.add("MessageIdentification");l.add("CreationDateTime");l.add("ControlSum");return l;}@Overridepublic Set<String> getReadOnlyFields() {return this.getTable().getFieldsName();}@Overrideprotected SQLComponent createComponent() {return new UISQLComponent(this) {@Overrideprotected void addViews() {this.addView("MessageIdentification");this.addView("CreationDateTime");this.addView("NumberOfTransactions");this.addView("ControlSum");this.addView("XML");}};}@Overrideprotected String createCode() {// TODO rename createCodeFromPackage() to createCodeOfPackage() and change createCode()// implementation to use a new createCodeFromPackage() which uses the class name (w/o// SQLElement suffix)return this.createCodeFromPackage() + ".SDDMessage";}public final void exportXML(final Component comp, final List<? extends SQLRowAccessor> messages) {final Preferences sddMsgPrefs = getPreferences();final String storedPath = sddMsgPrefs.get(XML_LOCATION_PREF_KEY, "");final JFileChooser fileChooser = new JFileChooser(storedPath);fileChooser.setFileSelectionMode(JFileChooser.DIRECTORIES_ONLY);final int answer = fileChooser.showDialog(comp, "Exporter " + getName().getVariant(Grammar_fr.DEFINITE_ARTICLE_PLURAL));if (answer == JFileChooser.APPROVE_OPTION) {final String newPath = fileChooser.getSelectedFile().getAbsolutePath();sddMsgPrefs.put(XML_LOCATION_PREF_KEY, newPath);final File directDebitDir = new File(newPath);try {for (final SQLRowAccessor messageRow : messages) {FileUtils.write(messageRow.getString("XML"), new File(directDebitDir, messageRow.getString("MessageIdentification") + ".xml"), StringUtils.UTF8, false);}} catch (IOException exn) {ExceptionHandler.handle(comp, "Impossible d'exporter", exn);}}}protected static BigDecimal getInvoiceAmount(final SQLRowValues invoice) {return BigDecimal.valueOf(invoice.getLong("NET_A_PAYER")).movePointLeft(2);}static private final class InvoiceElem extends Tuple2<SQLRowValues, Element> {protected InvoiceElem(SQLRowValues a, Element b) {super(a, b);}}// Un lot est obligatoirement homogène sur les critères suivants :// - Même type de prélèvement SEPA (index 2.11 « LocalInstrument ») : SDD Core ou// SDD B2B// - Même séquence de présentation (index 2.14 « SequenceType ») : FRST ou RCURstatic private final class PaymentInfo {private final Date collectionDate;private final String seqType;private final List<InvoiceElem> invoices;private final BigDecimal sum;protected PaymentInfo(Date collectionDate, String seqType, List<InvoiceElem> invoices) {super();this.collectionDate = collectionDate;if (!SEPAMandateSQLElement.SEQ_VALUES.contains(seqType))throw new IllegalArgumentException("Invalid sequence type : " + seqType);this.seqType = seqType;this.invoices = invoices;BigDecimal d = BigDecimal.ZERO;for (final InvoiceElem invoice : invoices) {d = d.add(getInvoiceAmount(invoice.get0()));}this.sum = d;}@Overridepublic String toString() {return this.getClass().getSimpleName() + " for " + this.invoices.size() + " " + this.seqType + " invoices at " + this.collectionDate;}}static private final class InvoicesByPaymentInfo {private final Date lowerBound, upperBound;private final NavigableMap<Date, ListMap<String, InvoiceElem>> map = new TreeMap<>();private final Set<Number> lockedInvoicesIDs = new HashSet<>();private BigDecimal sum = BigDecimal.ZERO;private final Set<String> invoiceNumbers = new HashSet<>();private final Set<String> invoiceMandates = new HashSet<>();private final ElementCreator elemCreator;protected InvoicesByPaymentInfo(Date lowerBound, Date upperBound, final ElementCreator elemCreator) {super();if (lowerBound.compareTo(upperBound) >= 0)throw new IllegalArgumentException("Lower date after upper date : " + lowerBound + " >= " + upperBound);this.lowerBound = lowerBound;this.upperBound = upperBound;this.elemCreator = elemCreator;}final IgnoreReason addInvoice(final SQLRowValues invoice) {Date dueDate = ModeDeReglementSQLElement.calculDate(invoice.getForeign("ID_MODE_REGLEMENT"), invoice.getObjectAs("DATE", Date.class));// don't ask direct debit too far in advanceif (dueDate.after(this.upperBound)) {return IgnoreReason.TOO_FAR_IN_FUTURE;} else if (dueDate.before(this.lowerBound)) {dueDate = this.lowerBound;}final Element elem;try {elem = createDDTx(this.elemCreator, invoice);} catch (MissingInfoException e) {return IgnoreReason.MISSING_INFO;}// needed so that EndToEndId is uniqueif (!this.invoiceNumbers.add(invoice.getString("NUMERO")))throw new IllegalStateException("Duplicate invoice number : " + invoice);final SQLRowAccessor mandate = invoice.getForeign("ID_MODE_REGLEMENT").getForeign("ID_SEPA_MANDATE");if (!mandate.getBoolean("ACTIVE"))throw new IllegalStateException("Inactive mandate for " + invoice);// needed otherwise would have to update seqType while generatingif (!this.invoiceMandates.add(mandate.getString("MandateIdentification")))return IgnoreReason.DUPLICATE_MANDATE;this.lockedInvoicesIDs.add(invoice.getIDNumber());ListMap<String, InvoiceElem> bySeqType = this.map.get(dueDate);if (bySeqType == null) {bySeqType = new ListMap<>();this.map.put(dueDate, bySeqType);}bySeqType.add(mandate.getString("SequenceType"), new InvoiceElem(invoice, elem));this.sum = this.sum.add(getInvoiceAmount(invoice));return IgnoreReason.NONE;}public final int getTransactionCount() {return this.lockedInvoicesIDs.size();}public final List<PaymentInfo> getPaymentInfos() {final List<PaymentInfo> res = new ArrayList<>();for (final Entry<Date, ListMap<String, InvoiceElem>> e : this.map.entrySet()) {final Date collectionDate = e.getKey();for (final Entry<String, List<InvoiceElem>> e2 : e.getValue().entrySet()) {final String seqType = e2.getKey();res.add(new PaymentInfo(collectionDate, seqType, e2.getValue()));}}return res;}}public static enum IgnoreReason {NONE, TOO_FAR_IN_FUTURE, DUPLICATE_MANDATE, MISSING_INFO;}public static final class GenerationResult {private final Collection<? extends Number> passedIDs;private final List<SQLRowValues> withDDWithoutMessage;private final ListMapItf<IgnoreReason, SQLRowValues> ignoredInvoices;private final SQLRow insertedMessage;private final int invoiceCount;protected GenerationResult(Collection<? extends Number> passedIDs, List<SQLRowValues> withDDWithoutMessage, ListMap<IgnoreReason, SQLRowValues> ignoredInvoices, SQLRow insertedMessage) {super();this.passedIDs = passedIDs;this.withDDWithoutMessage = Collections.unmodifiableList(withDDWithoutMessage);assert !ignoredInvoices.containsKey(null) && !ignoredInvoices.containsKey(IgnoreReason.NONE);this.ignoredInvoices = ListMap.unmodifiableMap(ignoredInvoices);this.insertedMessage = insertedMessage;this.invoiceCount = insertedMessage == null ? 0 : insertedMessage.getInt("NumberOfTransactions");assert this.withDDWithoutMessage.size() - this.ignoredInvoices.allValues().size() == this.invoiceCount;}// OK since both the list and the SQLRowValues are immutablepublic final List<SQLRowValues> getDDInvoicesWithoutMessage() {return this.withDDWithoutMessage;}// OK since both the list and the SQLRowValues are immutablepublic final ListMapItf<IgnoreReason, SQLRowValues> getIgnoredInvoices() {return this.ignoredInvoices;}public final SQLRow getInsertedMessage() {return this.insertedMessage;}public final int getIncludedInvoicesCount() {return this.invoiceCount;}@Overridepublic String toString() {return this.getClass().getSimpleName() + ": of the " + this.passedIDs.size() + " passed, " + this.withDDWithoutMessage.size() + " needed a SDD message, of those "+ this.ignoredInvoices.allValues().size() + " were ignored (either being too far in the future or duplicate mandates) ; the inserted row was " + this.insertedMessage;}}public GenerationResult generateXML(Collection<? extends Number> invoiceIDs) throws SQLException {final Namespace painNS = Namespace.getNamespace("urn:iso:std:iso:20022:tech:xsd:pain.008.001.02");final Element rootElem = new Element("Document", painNS);final Namespace xsiNS = Namespace.getNamespace("xsi", "http://www.w3.org/2001/XMLSchema-instance");rootElem.setAttribute("schemaLocation", "urn:iso:std:iso:20022:tech:xsd:pain.008.001.02 pain.008.001.02.xsd", xsiNS);final Document doc = new Document(rootElem);final Element ddElem = new Element("CstmrDrctDbtInitn", painNS);rootElem.addContent(ddElem);// Lead Time / Time cycle// https://www.ecb.europa.eu/paym/retpaym/undpaym/qa/html/index.en.htmlfinal Calendar now = Calendar.getInstance();final Calendar cal = (Calendar) now.clone();TimeUtils.clearTime(cal);// perhaps handle business days and difference between FRST/OOFF (5 days) and RCURR/FNAL (2// days).cal.add(Calendar.DAY_OF_YEAR, this.dbPrefs.getInt(getCode() + ".leadDays", 7));final Date lowerBound = cal.getTime();cal.setTime(now.getTime());cal.add(Calendar.DAY_OF_YEAR, 30);final Date upperBound = cal.getTime();// TODO use createFetcher()final SQLRowValuesListFetcher selSociété = SQLRowValuesListFetcher.create(this.getDirectory().getElement(this.rowSociété.getTable()).createGraph());selSociété.setFullOnly(true);selSociété.appendSelTransf(new ITransformer<SQLSelect, SQLSelect>() {@Overridepublic SQLSelect transformChecked(SQLSelect sel) {sel.setLockStrength(LockStrength.SHARE);return sel;}});final SQLTable invoiceT = getDirectory().getElement(SaisieVenteFactureSQLElement.class).getTable();final SQLField invoiceSDDMessageF = invoiceT.getField(SaisieVenteFactureSQLElement.MESSAGE_FIELD_NAME);return SQLUtils.executeAtomic(getTable().getDBSystemRoot().getDataSource(), new ConnectionHandlerNoSetup<GenerationResult, SQLException>() {@Overridepublic GenerationResult handle(SQLDataSource ds) throws SQLException {final SQLRowValues lockedSociété = selSociété.fetchOne(SDDMessageSQLElement.this.rowSociété.getIDNumber());if (lockedSociété == null)throw new IllegalStateException("Missing société " + SDDMessageSQLElement.this.rowSociété);// find and lock invoices with TYPE_REGLEMENT direct debit and no messagefinal SQLRowValues invoiceVals = new SQLRowValues(invoiceT);invoiceVals.putRowValues("ID_CLIENT").putNulls("NOM", "BIC", "IBAN");invoiceVals.putRowValues("ID_MODE_REGLEMENT").putNulls("AJOURS", "LENJOUR").putRowValues("ID_SEPA_MANDATE").setAllToNull();invoiceVals.putNulls("NET_A_PAYER", "DATE", "NUMERO");final SQLRowValuesListFetcher fetcher = SQLRowValuesListFetcher.create(invoiceVals);fetcher.setReturnedRowsUnmodifiable(true);// required for locking rows and to make sure that there's a SEPA Mandatefetcher.setFullOnly(true);fetcher.appendSelTransf(new ITransformer<SQLSelect, SQLSelect>() {@Overridepublic SQLSelect transformChecked(SQLSelect sel) {// we will update FACTURE.ID_MESSAGEsel.setLockStrength(LockStrength.UPDATE);final SQLSelectJoin join = sel.getJoin(invoiceT.getField("ID_MODE_REGLEMENT"));join.setWhere(new Where(join.getJoinedTable().getField("ID_TYPE_REGLEMENT"), "=", TypeReglementSQLElement.PRELEVEMENT));return sel;}});final List<SQLRowValues> ddInvoices = fetcher.fetch(new Where(invoiceT.getKey(), invoiceIDs).and(new Where(invoiceSDDMessageF, "=", invoiceSDDMessageF.getForeignTable().getUndefinedIDNumber())));final InvoicesByPaymentInfo map = new InvoicesByPaymentInfo(lowerBound, upperBound, new ElementCreator(painNS, SDDMessageSQLElement.this.fieldTranslator));final ListMap<IgnoreReason, SQLRowValues> ignoredInvoices = new ListMap<>();for (final SQLRowValues invoice : ddInvoices) {final IgnoreReason ignoredReason = map.addInvoice(invoice);if (ignoredReason != IgnoreReason.NONE) {ignoredInvoices.add(ignoredReason, invoice);}}final SQLRow newMsg;final int txCount = map.getTransactionCount();if (txCount == 0) {newMsg = null;} else {// find and lock message serialfinal SQLTable mdT = getTable().getTable(SQLSchema.METADATA_TABLENAME);final SQLSelect sel = new SQLSelect(true).addSelect(mdT.getField("VALUE"));sel.setWhere(new Where(mdT.getField("NAME"), "=", SERIAL_MD));sel.setLockStrength(LockStrength.UPDATE);final String msgSerial = String.valueOf(Integer.parseInt((String) getTable().getDBSystemRoot().getDataSource().executeScalar(sel.asString())) + 1);// generate XMLfinal BigDecimal totalSum = map.sum;final Element groupHeaderElem = createGroupHeader(painNS, lockedSociété, now, msgSerial, txCount, totalSum);ddElem.addContent(groupHeaderElem);final String msgID = groupHeaderElem.getChild("MsgId", painNS).getText();final Map<SQLRow, String> end2endIDs = new HashMap<>();int index = 1;for (final PaymentInfo info : map.getPaymentInfos()) {try {createPaymentInfo(ddElem, end2endIDs, map.elemCreator, lockedSociété, info, msgID, index++);} catch (Exception e) {throw new IllegalStateException("Couldn't create XML for " + info, e);}}assert end2endIDs.size() == txCount : "Expected " + txCount + " transactions but got " + end2endIDs.size() + " rows : " + end2endIDs;// insert message in DBfinal SQLRowValues msgVals = new SQLRowValues(getTable());msgVals.put("MessageIdentification", msgID);msgVals.put("CreationDateTime", now.getTime());msgVals.put("NumberOfTransactions", txCount);msgVals.put("ControlSum", totalSum);msgVals.put("XML", JDOM2Utils.output(doc));newMsg = msgVals.insert();// update invoices with new messagefor (final Entry<SQLRow, String> e : end2endIDs.entrySet()) {final SQLRowValues vals = e.getKey().createEmptyUpdateRow();vals.putForeignID(invoiceSDDMessageF.getName(), newMsg);vals.put(SaisieVenteFactureSQLElement.END2END_FIELD_NAME, e.getValue());vals.update();}// update message serialgetTable().getDBRoot().setMetadata(SERIAL_MD, msgSerial);}return new GenerationResult(invoiceIDs, ddInvoices, ignoredInvoices, newMsg);}});}static private Element createGroupHeader(final Namespace painNS, final SQLRowValues lockedSociété, final Calendar now, final String msgSerial, final int txCount, final BigDecimal total) {final Element res = new Element("GrpHdr", painNS);res.addContent(new Element("MsgId", painNS).setText("openconcerto-" + now.get(Calendar.YEAR) + "-" + msgSerial));res.addContent(new Element("CreDtTm", painNS).setText(new XMLDateFormat().format(now)));res.addContent(new Element("NbOfTxs", painNS).setText(String.valueOf(txCount)));if (DecimalUtils.decimalDigits(total) > 2)throw new IllegalArgumentException("Too many decimals : " + total);res.addContent(new Element("CtrlSum", painNS).setText(total.toPlainString()));res.addContent(new Element("InitgPty", painNS).addContent(new Element("Nm", painNS).setText(getCompanyName(lockedSociété))));return res;}static private String getCompanyName(final SQLRowValues lockedSociété) {final String companyName = lockedSociété.getString("NOM");if (StringUtils.isEmpty(companyName))throw new IllegalStateException("Empty company name : " + lockedSociété);return lockedSociété.getString("TYPE") + ' ' + companyName;}static private final DateFormat XML_DATE_FMT = new SimpleDateFormat("yyyy-MM-dd");static synchronized private final String formatDate(final Date date) {return XML_DATE_FMT.format(date);}static private void createPaymentInfo(Element ddElem, Map<SQLRow, String> end2endIDs, final ElementCreator elemCreator, final SQLRowValues lockedSociété, final PaymentInfo info,final String msgID, final int index) throws SQLException, MissingInfoException {if (info.invoices.isEmpty())return;final Namespace painNS = elemCreator.painNS;final String formattedDate = formatDate(info.collectionDate);final Element res = new Element("PmtInf", painNS);res.addContent(new Element("PmtInfId", painNS).setText("openconcerto-" + formattedDate + '.' + index));res.addContent(new Element("PmtMtd", painNS).setText("DD"));res.addContent(new Element("BtchBookg", painNS).setText("false"));res.addContent(new Element("NbOfTxs", painNS).setText(String.valueOf(info.invoices.size())));if (DecimalUtils.decimalDigits(info.sum) > 2)throw new IllegalArgumentException("Too many decimals : " + info.sum);res.addContent(new Element("CtrlSum", painNS).setText(info.sum.toPlainString()));final Element typeInformation = new Element("PmtTpInf", painNS);typeInformation.addContent(new Element("SvcLvl", painNS).addContent(new Element("Cd", painNS).setText("SEPA")));typeInformation.addContent(new Element("LclInstrm", painNS).addContent(new Element("Cd", painNS).setText("CORE")));typeInformation.addContent(new Element("SeqTp", painNS).setText(info.seqType));res.addContent(typeInformation);res.addContent(new Element("ReqdColltnDt", painNS).setText(formattedDate));final Element creditor = new Element("Cdtr", painNS);creditor.addContent(new Element("Nm", painNS).setText(getCompanyName(lockedSociété)));final Element postalAddr = new Element("PstlAdr", painNS);final SQLRowAccessor addr = lockedSociété.getNonEmptyForeign("ID_ADRESSE_COMMON");final String country = addr.getString("PAYS");final String country2;if (StringUtils.isEmpty(country, true) || country.trim().equalsIgnoreCase("France")) {country2 = "FR";} else {// TODO map to 2 letter codethrow new IllegalStateException("Unknown country : " + country);}postalAddr.addContent(new Element("Ctry", painNS).setText(country2));postalAddr.addContent(new Element("AdrLine", painNS).setText(addr.getString("RUE")));postalAddr.addContent(new Element("AdrLine", painNS).setText(addr.getString("CODE_POSTAL") + " " + addr.getString("VILLE")));creditor.addContent(postalAddr);res.addContent(creditor);final Element creditorAccount = new Element("CdtrAcct", painNS);creditorAccount.addContent(new Element("Id", painNS).addContent(elemCreator.createWithNonEmptyText("IBAN", lockedSociété, "IBAN")));res.addContent(creditorAccount);final Element creditorAgent = new Element("CdtrAgt", painNS);creditorAgent.addContent(new Element("FinInstnId", painNS).addContent(elemCreator.createWithNonEmptyText("BIC", lockedSociété, "BIC")));res.addContent(creditorAgent);res.addContent(new Element("ChrgBr", painNS).setText("SLEV"));final Element creditorID = new Element("CdtrSchmeId", painNS);final Element other = new Element("Othr", painNS);other.addContent(elemCreator.createWithNonEmptyText("Id", lockedSociété, "SEPA_CREDITOR_ID"));other.addContent(new Element("SchmeNm", painNS).addContent(new Element("Prtry", painNS).setText("SEPA")));creditorID.addContent(new Element("Id", painNS).addContent(new Element("PrvtId", painNS).addContent(other)));res.addContent(creditorID);for (final InvoiceElem invoice : info.invoices) {final String end2endID = msgID + '.' + invoice.get0().getString("NUMERO");if (end2endIDs.put(invoice.get0().asRow(), end2endID) != null)throw new IllegalStateException("Duplicate invoice : " + invoice);try {res.addContent(fillDDTx(invoice, elemCreator, end2endID));} catch (Exception e) {throw new IllegalStateException("Couldn't create XML for " + invoice, e);}}ddElem.addContent(res);}static private Element fillDDTx(final InvoiceElem invoiceElem, final ElementCreator elemCreator, final String end2endID) throws SQLException, MissingInfoException {final Element paymentId = new Element("PmtId", elemCreator.painNS);paymentId.addContent(elemCreator.createWithNonEmptyText("InstrId", end2endID));paymentId.addContent(elemCreator.createWithNonEmptyText("EndToEndId", end2endID));invoiceElem.get1().addContent(0, paymentId);// update mandate fieldsfinal SQLRowAccessor mandate = invoiceElem.get0().getForeign("ID_MODE_REGLEMENT").getForeign("ID_SEPA_MANDATE");final String seqType = mandate.getString("SequenceType");if (seqType.equals(SEPAMandateSQLElement.SEQ_FIRST)) {mandate.createEmptyUpdateRow().put("SequenceType", SEPAMandateSQLElement.SEQ_RECURRENT).update();} else if (seqType.equals(SEPAMandateSQLElement.SEQ_FINAL) || seqType.equals(SEPAMandateSQLElement.SEQ_ONEOFF)) {mandate.createEmptyUpdateRow().put("ACTIVE", Boolean.FALSE).update();} // else SEQ_RECURRENTreturn invoiceElem.get1();}static private Element createDDTx(final ElementCreator elemCreator, final SQLRowValues invoice) throws MissingInfoException {final Namespace painNS = elemCreator.painNS;final Element res = new Element("DrctDbtTxInf", painNS);res.addContent(new Element("InstdAmt", painNS).setAttribute("Ccy", "EUR").setText(getInvoiceAmount(invoice).toPlainString()));final Element mandateRltdInfo = new Element("MndtRltdInf", painNS);final SQLRowAccessor mandate = invoice.getForeign("ID_MODE_REGLEMENT").getForeign("ID_SEPA_MANDATE");assert !mandate.isUndefined() : "Undefined mandate returned by fetcher";mandateRltdInfo.addContent(elemCreator.createWithNonEmptyText("MndtId", mandate, "MandateIdentification"));mandateRltdInfo.addContent(elemCreator.createWithNonEmptyText("DtOfSgntr", formatDate(mandate.getObjectAs("DateOfSignature", Date.class))));mandateRltdInfo.addContent(elemCreator.createWithNonEmptyText("AmdmntInd", "false"));res.addContent(new Element("DrctDbtTx", painNS).addContent(mandateRltdInfo));final SQLRowAccessor clientRow = invoice.getForeign("ID_CLIENT");res.addContent(new Element("DbtrAgt", painNS).addContent(new Element("FinInstnId", painNS).addContent(elemCreator.createWithNonEmptyText("BIC", clientRow, "BIC"))));res.addContent(new Element("Dbtr", painNS).addContent(elemCreator.createWithNonEmptyText("Nm", clientRow, "NOM")));res.addContent(new Element("DbtrAcct", painNS).addContent(new Element("Id", painNS).addContent(elemCreator.createWithNonEmptyText("IBAN", clientRow, "IBAN"))));res.addContent(new Element("Purp", painNS).addContent(new Element("Cd", painNS).setText("OTHR")));// TODO <RmtInf><Ustrd>ligne de facture avec n° d'abonnement et indice de paiement.return res;}static public final class MissingInfoException extends Exception {private final String label;protected MissingInfoException(final String label) {super("Empty " + label);this.label = label;}public final String getLabel() {return this.label;}}static private final class ElementCreator {private final Namespace painNS;private final SQLFieldTranslator fieldTrans;protected ElementCreator(Namespace painNS, final SQLFieldTranslator fieldTrans) {super();this.painNS = painNS;this.fieldTrans = fieldTrans;}protected Element create(final String elemName) {return new Element(elemName, this.painNS);}protected Element createWithNonEmptyText(final String elemName, final SQLRowAccessor r, final String field) throws MissingInfoException {return this.createWithNonEmptyText(elemName, r.getString(field), this.fieldTrans.getDescFor(r.getTable(), field).getLabel());}protected Element createWithNonEmptyText(final String elemName, final String text) throws MissingInfoException {return this.createWithNonEmptyText(elemName, text, null);}protected Element createWithNonEmptyText(final String elemName, final String text, final String label) throws MissingInfoException {if (StringUtils.isEmpty(text))throw new MissingInfoException(label == null ? elemName : label);return create(elemName).setText(text);}}}