Dépôt officiel du code source de l'ERP OpenConcerto

/*

 * 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 public 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…") {

            @Override

            public void actionPerformed(ActionEvent e) {

                final IListe l = IListe.get(e);

                // XML field values are quite large so only fetch them when needed

                final 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);

    }

    @Override

    protected List<String> getListFields() {

        final List<String> l = new ArrayList<String>();

        l.add("MessageIdentification");

        l.add("CreationDateTime");

        l.add("NumberOfTransactions");

        l.add("ControlSum");

        return l;

    }

    @Override

    protected List<String> getComboFields() {

        final List<String> l = new ArrayList<String>();

        l.add("MessageIdentification");

        l.add("CreationDateTime");

        l.add("ControlSum");

        return l;

    }

    @Override

    public Set<String> getReadOnlyFields() {

        return this.getTable().getFieldsName();

    }

    @Override

    protected SQLComponent createComponent() {

        return new UISQLComponent(this) {

            @Override

            protected void addViews() {

                this.addView("MessageIdentification");

                this.addView("CreationDateTime");

                this.addView("NumberOfTransactions");

                this.addView("ControlSum");

                this.addView("XML");

            }

        };

    }

    @Override

    protected 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.createCodeOfPackage() + ".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 RCUR

    static 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;

        }

        @Override

        public 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;

        // { collection date -> { sequence type -> invoice } }

        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 advance

            if (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 unique

            if (!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 generating

            // MAYBE sum all invoices for a single mandate, but which date to choose ?

            if (!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 immutable

        public final List<SQLRowValues> getDDInvoicesWithoutMessage() {

            return this.withDDWithoutMessage;

        }

        // OK since both the list and the SQLRowValues are immutable

        public final ListMapItf<IgnoreReason, SQLRowValues> getIgnoredInvoices() {

            return this.ignoredInvoices;

        }

        public final SQLRow getInsertedMessage() {

            return this.insertedMessage;

        }

        public final int getIncludedInvoicesCount() {

            return this.invoiceCount;

        }

        @Override

        public 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.html

        final 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>() {

            @Override

            public 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>() {

            @Override

            public 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 message

                final 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", "NOM");

                final SQLRowValuesListFetcher fetcher = SQLRowValuesListFetcher.create(invoiceVals);

                fetcher.setReturnedRowsUnmodifiable(true);

                // required for locking rows and to make sure that there's a SEPA Mandate

                fetcher.setFullOnly(true);

                fetcher.appendSelTransf(new ITransformer<SQLSelect, SQLSelect>() {

                    @Override

                    public SQLSelect transformChecked(SQLSelect sel) {

                        // we will update FACTURE.ID_MESSAGE

                        sel.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 serial

                    final 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 XML

                    final 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 DB

                    final 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 message

                    for (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 serial

                    getTable().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 code

            throw 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 fields

        final 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_RECURRENT

        return 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")));

        final String info = (invoice.getString("NUMERO") + ' ' + invoice.getString("NOM")).trim();

        if (!info.isEmpty())

            res.addContent(new Element("RmtInf", painNS).addContent(elemCreator.create("Ustrd").setText(elemCreator.shortenText(info, 140))));

        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 {

        static private final String TRUNCATED_SUFFIX = "...";

        static private final int TRUNCATED_SUFFIX_LENGTH = TRUNCATED_SUFFIX.length();

        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);

        }

        protected String shortenText(final String text, final int maxLength) {

            if (maxLength <= TRUNCATED_SUFFIX_LENGTH || text.length() <= maxLength)

                return text;

            return text.substring(0, maxLength - TRUNCATED_SUFFIX_LENGTH) + TRUNCATED_SUFFIX;

        }

    }

}