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.concurrent.ExecutionException;
import java.util.prefs.Preferences;

import javax.swing.AbstractAction;
import javax.swing.JFileChooser;
import javax.swing.SwingWorker;

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);
                final SQLTable t = l.getSource().getPrimaryTable();
                final Set<Integer> userSelectedIDs = l.getSelection().getUserSelectedIDs();
                new SwingWorker<List<SQLRow>, Void>() {
                    @Override
                    protected List<SQLRow> doInBackground() throws Exception {
                        final SQLSelect sel = new SQLSelect().addSelectStar(t);
                        // XML field values are quite large so only fetch them when needed
                        sel.setWhere(new Where(t.getKey(), userSelectedIDs));
                        return SQLRowListRSH.execute(sel);
                    }

                    @Override
                    protected void done() {
                        List<SQLRow> execute;
                        try {
                            execute = get();
                            exportXML(l, execute);
                        } catch (InterruptedException | ExecutionException e) {
                            e.printStackTrace();
                        }

                    }
                }.execute();
            }
        }, 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) {
        if (invoice.getTable().getName().equals("SAISIE_VENTE_FACTURE")) {
            return BigDecimal.valueOf(invoice.getLong("NET_A_PAYER")).movePointLeft(2);
        } else {
            return BigDecimal.valueOf(invoice.getLong("MONTANT")).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;
        }

        final IgnoreReason addEcheance(final SQLRowValues prlvt) {
            Date date = prlvt.getDate("DATE").getTime();

            // don't ask direct debit too far in advance
            if (date.after(this.upperBound)) {
                return IgnoreReason.TOO_FAR_IN_FUTURE;
            } else if (date.before(this.lowerBound)) {
                date = this.lowerBound;
            }

            final Element elem;
            try {
                elem = createDDTx(this.elemCreator, prlvt);
            } catch (MissingInfoException e) {
                return IgnoreReason.MISSING_INFO;
            }

            // needed so that EndToEndId is unique
            if (!this.invoiceNumbers.add(prlvt.getForeign("ID_MOUVEMENT").getForeign("ID_PIECE").getString("NOM")))
                throw new IllegalStateException("Duplicate invoice number : " + prlvt);
            final SQLRowAccessor mandate = prlvt.getForeign("ID_SEPA_MANDATE");
            if (!mandate.getBoolean("ACTIVE"))
                throw new IllegalStateException("Inactive mandate for " + prlvt);
            // 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(prlvt.getIDNumber());
            ListMap<String, InvoiceElem> bySeqType = this.map.get(date);
            if (bySeqType == null) {
                bySeqType = new ListMap<>();
                this.map.put(date, bySeqType);
            }
            bySeqType.add(mandate.getString("SequenceType"), new InvoiceElem(prlvt, elem));

            this.sum = this.sum.add(BigDecimal.valueOf(prlvt.getLong("MONTANT")).movePointLeft(2));

            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;
        private final SQLTable table;

        protected GenerationResult(SQLTable table, Collection<? extends Number> passedIDs, List<SQLRowValues> withDDWithoutMessage, ListMap<IgnoreReason, SQLRowValues> ignoredInvoices,
                SQLRow insertedMessage) {
            super();
            this.table = table;
            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;
        }

        public SQLTable getTable() {
            return table;
        }

        @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(final SQLTable table, 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 SQLField invoiceSDDMessageF = table.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é);
                if (lockedSociété.getString("IBAN").trim().length() == 0) {
                    throw new IllegalStateException("Missing société IBAN " + SDDMessageSQLElement.this.rowSociété);
                }

                if (lockedSociété.getString("BIC").trim().length() == 0) {
                    throw new IllegalStateException("Missing société BIC " + SDDMessageSQLElement.this.rowSociété);
                }

                // find and lock invoices with TYPE_REGLEMENT direct debit and no message
                final SQLRowValues invoiceVals = new SQLRowValues(table);

                final boolean fromInvoices = table.getName().equals("SAISIE_VENTE_FACTURE");
                if (fromInvoices) {
                    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");
                } else {
                    invoiceVals.putRowValues("ID_CLIENT").putNulls("NOM", "BIC", "IBAN");
                    invoiceVals.putRowValues("ID_SEPA_MANDATE").setAllToNull();
                    invoiceVals.putNulls("MONTANT", "DATE");
                    invoiceVals.putRowValues("ID_MOUVEMENT").putRowValues("ID_PIECE").setAllToNull();
                }

                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);
                        if (fromInvoices) {
                            final SQLSelectJoin join = sel.getJoin(table.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(table.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) {
                    if (fromInvoices) {
                        final IgnoreReason ignoredReason = map.addInvoice(invoice);
                        if (ignoredReason != IgnoreReason.NONE) {
                            ignoredInvoices.add(ignoredReason, invoice);
                        }
                    } else {
                        final IgnoreReason ignoredReason = map.addEcheance(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(table, 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);
        String iban = lockedSociété.getString("IBAN").replaceAll(" ", "");
        creditorAccount.addContent(new Element("Id", painNS).addContent(elemCreator.createWithNonEmptyText("IBAN", iban, "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;
        if (invoiceElem.get0().contains("ID_SEPA_MANDATE")) {
            mandate = invoiceElem.get0().getForeign("ID_SEPA_MANDATE");
        } else {
            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;
        final boolean fromInvoice = invoice.getTable().getName().equals("SAISIE_VENTE_FACTURE");
        if (fromInvoice) {
            mandate = invoice.getForeign("ID_MODE_REGLEMENT").getForeign("ID_SEPA_MANDATE");
        } else {
            mandate = invoice.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;
        if (fromInvoice) {
            info = (invoice.getString("NUMERO") + ' ' + invoice.getString("NOM")).trim();
        } else {
            info = invoice.getForeign("ID_MOUVEMENT").getForeign("ID_PIECE").getString("NOM");
        }
        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;
        }
    }

}