OpenConcerto

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

svn://code.openconcerto.org/openconcerto

Rev

Rev 156 | Details | Compare with Previous | Last modification | View Log | RSS feed

Rev Author Line No. Line
151 ilm 1
/*
2
 * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS HEADER.
3
 *
4
 * Copyright 2011 OpenConcerto, by ILM Informatique. All rights reserved.
5
 *
6
 * The contents of this file are subject to the terms of the GNU General Public License Version 3
7
 * only ("GPL"). You may not use this file except in compliance with the License. You can obtain a
8
 * copy of the License at http://www.gnu.org/licenses/gpl-3.0.html See the License for the specific
9
 * language governing permissions and limitations under the License.
10
 *
11
 * When distributing the software, include this License Header Notice in each file.
12
 */
13
 
14
 package org.openconcerto.erp.core.finance.payment.element;
15
 
16
import org.openconcerto.erp.config.ComptaPropsConfiguration;
17
import org.openconcerto.erp.core.common.element.ComptaSQLConfElement;
18
import org.openconcerto.erp.core.sales.invoice.element.SaisieVenteFactureSQLElement;
19
import org.openconcerto.sql.element.SQLComponent;
20
import org.openconcerto.sql.element.UISQLComponent;
21
import org.openconcerto.sql.model.ConnectionHandlerNoSetup;
22
import org.openconcerto.sql.model.DBRoot;
23
import org.openconcerto.sql.model.SQLDataSource;
24
import org.openconcerto.sql.model.SQLField;
25
import org.openconcerto.sql.model.SQLRow;
26
import org.openconcerto.sql.model.SQLRowAccessor;
27
import org.openconcerto.sql.model.SQLRowListRSH;
28
import org.openconcerto.sql.model.SQLRowValues;
29
import org.openconcerto.sql.model.SQLRowValuesListFetcher;
30
import org.openconcerto.sql.model.SQLSchema;
31
import org.openconcerto.sql.model.SQLSelect;
32
import org.openconcerto.sql.model.SQLSelect.LockStrength;
33
import org.openconcerto.sql.model.SQLSelectJoin;
34
import org.openconcerto.sql.model.SQLTable;
35
import org.openconcerto.sql.model.Where;
36
import org.openconcerto.sql.preferences.SQLPreferences;
37
import org.openconcerto.sql.request.SQLFieldTranslator;
38
import org.openconcerto.sql.utils.SQLCreateTable;
39
import org.openconcerto.sql.utils.SQLUtils;
40
import org.openconcerto.sql.view.list.IListe;
41
import org.openconcerto.sql.view.list.IListeAction.IListeEvent;
42
import org.openconcerto.sql.view.list.RowAction;
43
import org.openconcerto.utils.CollectionMap2Itf.ListMapItf;
44
import org.openconcerto.utils.DecimalUtils;
45
import org.openconcerto.utils.ExceptionHandler;
46
import org.openconcerto.utils.FileUtils;
47
import org.openconcerto.utils.ListMap;
48
import org.openconcerto.utils.StringUtils;
49
import org.openconcerto.utils.TimeUtils;
50
import org.openconcerto.utils.Tuple2;
51
import org.openconcerto.utils.XMLDateFormat;
52
import org.openconcerto.utils.cc.ITransformer;
53
import org.openconcerto.utils.i18n.Grammar_fr;
54
import org.openconcerto.xml.JDOM2Utils;
55
 
56
import java.awt.Component;
57
import java.awt.event.ActionEvent;
58
import java.io.File;
59
import java.io.IOException;
60
import java.math.BigDecimal;
61
import java.sql.Clob;
62
import java.sql.SQLException;
63
import java.text.DateFormat;
64
import java.text.SimpleDateFormat;
65
import java.util.ArrayList;
66
import java.util.Calendar;
67
import java.util.Collection;
68
import java.util.Collections;
69
import java.util.Date;
70
import java.util.HashMap;
71
import java.util.HashSet;
72
import java.util.List;
73
import java.util.Map;
74
import java.util.Map.Entry;
75
import java.util.NavigableMap;
76
import java.util.Set;
77
import java.util.TreeMap;
177 ilm 78
import java.util.concurrent.ExecutionException;
151 ilm 79
import java.util.prefs.Preferences;
80
 
81
import javax.swing.AbstractAction;
82
import javax.swing.JFileChooser;
177 ilm 83
import javax.swing.SwingWorker;
151 ilm 84
 
85
import org.jdom2.Document;
86
import org.jdom2.Element;
87
import org.jdom2.Namespace;
88
 
89
public final class SDDMessageSQLElement extends ComptaSQLConfElement {
156 ilm 90
    static public final String TABLE_NAME = "SEPA_DIRECT_DEBIT_MESSAGE";
151 ilm 91
    static public final String XML_LOCATION_PREF_KEY = "SDD.XML.location";
92
    public static final String SERIAL_MD = "SDD_MESSAGE_SERIAL";
93
 
94
    public static SQLCreateTable getCreateTable(final DBRoot root) {
95
        if (root.contains(TABLE_NAME))
96
            return null;
97
        final SQLCreateTable res = new SQLCreateTable(root, TABLE_NAME);
98
        res.addVarCharColumn("MessageIdentification", 35);
99
        res.addColumn("CreationDateTime", res.getSyntax().getDateAndTimeType(), null, false);
100
        res.addIntegerColumn("NumberOfTransactions", 0);
101
        res.addDecimalColumn("ControlSum", 16, 6, BigDecimal.ZERO, false);
102
        res.addColumn("XML", res.getSyntax().getTypeNames(Clob.class).iterator().next(), null, false);
103
        return res;
104
    }
105
 
106
    private final String prefsPath;
107
    private final SQLRow rowSociété;
108
    private final SQLPreferences dbPrefs;
109
    private final SQLFieldTranslator fieldTranslator;
110
 
111
    public SDDMessageSQLElement(final ComptaPropsConfiguration conf) {
112
        super(conf.getRootSociete().findTable(TABLE_NAME, true));
113
        this.prefsPath = conf.getAppID() + '/' + this.getCode().replace('.', '/');
114
        this.getRowActions().add(new RowAction.PredicateRowAction(new AbstractAction("Exporter…") {
115
            @Override
116
            public void actionPerformed(ActionEvent e) {
117
                final IListe l = IListe.get(e);
118
                final SQLTable t = l.getSource().getPrimaryTable();
177 ilm 119
                final Set<Integer> userSelectedIDs = l.getSelection().getUserSelectedIDs();
120
                new SwingWorker<List<SQLRow>, Void>() {
121
                    @Override
122
                    protected List<SQLRow> doInBackground() throws Exception {
123
                        final SQLSelect sel = new SQLSelect().addSelectStar(t);
124
                        // XML field values are quite large so only fetch them when needed
125
                        sel.setWhere(new Where(t.getKey(), userSelectedIDs));
126
                        return SQLRowListRSH.execute(sel);
127
                    }
128
 
129
                    @Override
130
                    protected void done() {
131
                        List<SQLRow> execute;
132
                        try {
133
                            execute = get();
134
                            exportXML(l, execute);
135
                        } catch (InterruptedException | ExecutionException e) {
136
                            e.printStackTrace();
137
                        }
138
 
139
                    }
140
                }.execute();
151 ilm 141
            }
142
        }, true, false).setPredicate(IListeEvent.getNonEmptySelectionPredicate()));
143
        this.rowSociété = conf.getRowSociete();
144
        this.dbPrefs = new SQLPreferences(conf.getRootSociete());
145
        this.fieldTranslator = conf.getTranslator();
146
    }
147
 
148
    public final Preferences getPreferences() {
149
        return Preferences.userRoot().node(this.prefsPath);
150
    }
151
 
152
    @Override
153
    protected List<String> getListFields() {
154
        final List<String> l = new ArrayList<String>();
155
        l.add("MessageIdentification");
156
        l.add("CreationDateTime");
157
        l.add("NumberOfTransactions");
158
        l.add("ControlSum");
159
        return l;
160
    }
161
 
162
    @Override
163
    protected List<String> getComboFields() {
164
        final List<String> l = new ArrayList<String>();
165
        l.add("MessageIdentification");
166
        l.add("CreationDateTime");
167
        l.add("ControlSum");
168
        return l;
169
    }
170
 
171
    @Override
172
    public Set<String> getReadOnlyFields() {
173
        return this.getTable().getFieldsName();
174
    }
175
 
176
    @Override
177
    protected SQLComponent createComponent() {
178
        return new UISQLComponent(this) {
179
            @Override
180
            protected void addViews() {
181
                this.addView("MessageIdentification");
182
                this.addView("CreationDateTime");
183
                this.addView("NumberOfTransactions");
184
                this.addView("ControlSum");
185
                this.addView("XML");
186
            }
187
        };
188
    }
189
 
190
    @Override
191
    protected String createCode() {
192
        // TODO rename createCodeFromPackage() to createCodeOfPackage() and change createCode()
193
        // implementation to use a new createCodeFromPackage() which uses the class name (w/o
194
        // SQLElement suffix)
156 ilm 195
        return this.createCodeOfPackage() + ".SDDMessage";
151 ilm 196
    }
197
 
198
    public final void exportXML(final Component comp, final List<? extends SQLRowAccessor> messages) {
199
        final Preferences sddMsgPrefs = getPreferences();
200
        final String storedPath = sddMsgPrefs.get(XML_LOCATION_PREF_KEY, "");
201
        final JFileChooser fileChooser = new JFileChooser(storedPath);
202
        fileChooser.setFileSelectionMode(JFileChooser.DIRECTORIES_ONLY);
203
        final int answer = fileChooser.showDialog(comp, "Exporter " + getName().getVariant(Grammar_fr.DEFINITE_ARTICLE_PLURAL));
204
        if (answer == JFileChooser.APPROVE_OPTION) {
205
            final String newPath = fileChooser.getSelectedFile().getAbsolutePath();
206
            sddMsgPrefs.put(XML_LOCATION_PREF_KEY, newPath);
207
            final File directDebitDir = new File(newPath);
208
            try {
209
                for (final SQLRowAccessor messageRow : messages) {
210
                    FileUtils.write(messageRow.getString("XML"), new File(directDebitDir, messageRow.getString("MessageIdentification") + ".xml"), StringUtils.UTF8, false);
211
                }
212
            } catch (IOException exn) {
213
                ExceptionHandler.handle(comp, "Impossible d'exporter", exn);
214
            }
215
        }
216
    }
217
 
218
    protected static BigDecimal getInvoiceAmount(final SQLRowValues invoice) {
177 ilm 219
        if (invoice.getTable().getName().equals("SAISIE_VENTE_FACTURE")) {
220
            return BigDecimal.valueOf(invoice.getLong("NET_A_PAYER")).movePointLeft(2);
221
        } else {
222
            return BigDecimal.valueOf(invoice.getLong("MONTANT")).movePointLeft(2);
223
        }
151 ilm 224
    }
225
 
226
    static private final class InvoiceElem extends Tuple2<SQLRowValues, Element> {
227
        protected InvoiceElem(SQLRowValues a, Element b) {
228
            super(a, b);
229
        }
230
    }
231
 
232
    // Un lot est obligatoirement homogène sur les critères suivants :
233
    // - Même type de prélèvement SEPA (index 2.11 « LocalInstrument ») : SDD Core ou
234
    // SDD B2B
235
    // - Même séquence de présentation (index 2.14 « SequenceType ») : FRST ou RCUR
236
    static private final class PaymentInfo {
237
        private final Date collectionDate;
238
        private final String seqType;
239
        private final List<InvoiceElem> invoices;
240
        private final BigDecimal sum;
241
 
242
        protected PaymentInfo(Date collectionDate, String seqType, List<InvoiceElem> invoices) {
243
            super();
244
            this.collectionDate = collectionDate;
245
            if (!SEPAMandateSQLElement.SEQ_VALUES.contains(seqType))
246
                throw new IllegalArgumentException("Invalid sequence type : " + seqType);
247
            this.seqType = seqType;
248
            this.invoices = invoices;
249
            BigDecimal d = BigDecimal.ZERO;
250
            for (final InvoiceElem invoice : invoices) {
251
                d = d.add(getInvoiceAmount(invoice.get0()));
252
            }
253
            this.sum = d;
254
        }
255
 
256
        @Override
257
        public String toString() {
258
            return this.getClass().getSimpleName() + " for " + this.invoices.size() + " " + this.seqType + " invoices at " + this.collectionDate;
259
        }
260
    }
261
 
262
    static private final class InvoicesByPaymentInfo {
263
        private final Date lowerBound, upperBound;
264
 
156 ilm 265
        // { collection date -> { sequence type -> invoice } }
151 ilm 266
        private final NavigableMap<Date, ListMap<String, InvoiceElem>> map = new TreeMap<>();
267
        private final Set<Number> lockedInvoicesIDs = new HashSet<>();
268
        private BigDecimal sum = BigDecimal.ZERO;
269
        private final Set<String> invoiceNumbers = new HashSet<>();
270
        private final Set<String> invoiceMandates = new HashSet<>();
271
        private final ElementCreator elemCreator;
272
 
273
        protected InvoicesByPaymentInfo(Date lowerBound, Date upperBound, final ElementCreator elemCreator) {
274
            super();
275
            if (lowerBound.compareTo(upperBound) >= 0)
276
                throw new IllegalArgumentException("Lower date after upper date : " + lowerBound + " >= " + upperBound);
277
            this.lowerBound = lowerBound;
278
            this.upperBound = upperBound;
279
            this.elemCreator = elemCreator;
280
        }
281
 
282
        final IgnoreReason addInvoice(final SQLRowValues invoice) {
283
            Date dueDate = ModeDeReglementSQLElement.calculDate(invoice.getForeign("ID_MODE_REGLEMENT"), invoice.getObjectAs("DATE", Date.class));
284
 
285
            // don't ask direct debit too far in advance
286
            if (dueDate.after(this.upperBound)) {
287
                return IgnoreReason.TOO_FAR_IN_FUTURE;
288
            } else if (dueDate.before(this.lowerBound)) {
289
                dueDate = this.lowerBound;
290
            }
291
 
292
            final Element elem;
293
            try {
294
                elem = createDDTx(this.elemCreator, invoice);
295
            } catch (MissingInfoException e) {
296
                return IgnoreReason.MISSING_INFO;
297
            }
298
 
299
            // needed so that EndToEndId is unique
300
            if (!this.invoiceNumbers.add(invoice.getString("NUMERO")))
301
                throw new IllegalStateException("Duplicate invoice number : " + invoice);
302
            final SQLRowAccessor mandate = invoice.getForeign("ID_MODE_REGLEMENT").getForeign("ID_SEPA_MANDATE");
303
            if (!mandate.getBoolean("ACTIVE"))
304
                throw new IllegalStateException("Inactive mandate for " + invoice);
305
            // needed otherwise would have to update seqType while generating
156 ilm 306
            // MAYBE sum all invoices for a single mandate, but which date to choose ?
151 ilm 307
            if (!this.invoiceMandates.add(mandate.getString("MandateIdentification")))
308
                return IgnoreReason.DUPLICATE_MANDATE;
309
 
310
            this.lockedInvoicesIDs.add(invoice.getIDNumber());
311
            ListMap<String, InvoiceElem> bySeqType = this.map.get(dueDate);
312
            if (bySeqType == null) {
313
                bySeqType = new ListMap<>();
314
                this.map.put(dueDate, bySeqType);
315
            }
316
            bySeqType.add(mandate.getString("SequenceType"), new InvoiceElem(invoice, elem));
317
 
318
            this.sum = this.sum.add(getInvoiceAmount(invoice));
319
 
320
            return IgnoreReason.NONE;
321
        }
322
 
177 ilm 323
        final IgnoreReason addEcheance(final SQLRowValues prlvt) {
324
            Date date = prlvt.getDate("DATE").getTime();
325
 
326
            // don't ask direct debit too far in advance
327
            if (date.after(this.upperBound)) {
328
                return IgnoreReason.TOO_FAR_IN_FUTURE;
329
            } else if (date.before(this.lowerBound)) {
330
                date = this.lowerBound;
331
            }
332
 
333
            final Element elem;
334
            try {
335
                elem = createDDTx(this.elemCreator, prlvt);
336
            } catch (MissingInfoException e) {
337
                return IgnoreReason.MISSING_INFO;
338
            }
339
 
340
            // needed so that EndToEndId is unique
341
            if (!this.invoiceNumbers.add(prlvt.getForeign("ID_MOUVEMENT").getForeign("ID_PIECE").getString("NOM")))
342
                throw new IllegalStateException("Duplicate invoice number : " + prlvt);
343
            final SQLRowAccessor mandate = prlvt.getForeign("ID_SEPA_MANDATE");
344
            if (!mandate.getBoolean("ACTIVE"))
345
                throw new IllegalStateException("Inactive mandate for " + prlvt);
346
            // needed otherwise would have to update seqType while generating
347
            // MAYBE sum all invoices for a single mandate, but which date to choose ?
348
            if (!this.invoiceMandates.add(mandate.getString("MandateIdentification")))
349
                return IgnoreReason.DUPLICATE_MANDATE;
350
 
351
            this.lockedInvoicesIDs.add(prlvt.getIDNumber());
352
            ListMap<String, InvoiceElem> bySeqType = this.map.get(date);
353
            if (bySeqType == null) {
354
                bySeqType = new ListMap<>();
355
                this.map.put(date, bySeqType);
356
            }
357
            bySeqType.add(mandate.getString("SequenceType"), new InvoiceElem(prlvt, elem));
358
 
359
            this.sum = this.sum.add(BigDecimal.valueOf(prlvt.getLong("MONTANT")).movePointLeft(2));
360
 
361
            return IgnoreReason.NONE;
362
        }
363
 
151 ilm 364
        public final int getTransactionCount() {
365
            return this.lockedInvoicesIDs.size();
366
        }
367
 
368
        public final List<PaymentInfo> getPaymentInfos() {
369
            final List<PaymentInfo> res = new ArrayList<>();
370
            for (final Entry<Date, ListMap<String, InvoiceElem>> e : this.map.entrySet()) {
371
                final Date collectionDate = e.getKey();
372
                for (final Entry<String, List<InvoiceElem>> e2 : e.getValue().entrySet()) {
373
                    final String seqType = e2.getKey();
374
                    res.add(new PaymentInfo(collectionDate, seqType, e2.getValue()));
375
                }
376
            }
377
            return res;
378
        }
379
    }
380
 
381
    public static enum IgnoreReason {
382
        NONE, TOO_FAR_IN_FUTURE, DUPLICATE_MANDATE, MISSING_INFO;
383
    }
384
 
385
    public static final class GenerationResult {
386
        private final Collection<? extends Number> passedIDs;
387
        private final List<SQLRowValues> withDDWithoutMessage;
388
        private final ListMapItf<IgnoreReason, SQLRowValues> ignoredInvoices;
389
        private final SQLRow insertedMessage;
390
        private final int invoiceCount;
177 ilm 391
        private final SQLTable table;
151 ilm 392
 
177 ilm 393
        protected GenerationResult(SQLTable table, Collection<? extends Number> passedIDs, List<SQLRowValues> withDDWithoutMessage, ListMap<IgnoreReason, SQLRowValues> ignoredInvoices,
394
                SQLRow insertedMessage) {
151 ilm 395
            super();
177 ilm 396
            this.table = table;
151 ilm 397
            this.passedIDs = passedIDs;
398
            this.withDDWithoutMessage = Collections.unmodifiableList(withDDWithoutMessage);
399
            assert !ignoredInvoices.containsKey(null) && !ignoredInvoices.containsKey(IgnoreReason.NONE);
400
            this.ignoredInvoices = ListMap.unmodifiableMap(ignoredInvoices);
401
            this.insertedMessage = insertedMessage;
402
            this.invoiceCount = insertedMessage == null ? 0 : insertedMessage.getInt("NumberOfTransactions");
403
            assert this.withDDWithoutMessage.size() - this.ignoredInvoices.allValues().size() == this.invoiceCount;
404
        }
405
 
406
        // OK since both the list and the SQLRowValues are immutable
407
        public final List<SQLRowValues> getDDInvoicesWithoutMessage() {
408
            return this.withDDWithoutMessage;
409
        }
410
 
411
        // OK since both the list and the SQLRowValues are immutable
412
        public final ListMapItf<IgnoreReason, SQLRowValues> getIgnoredInvoices() {
413
            return this.ignoredInvoices;
414
        }
415
 
416
        public final SQLRow getInsertedMessage() {
417
            return this.insertedMessage;
418
        }
419
 
420
        public final int getIncludedInvoicesCount() {
421
            return this.invoiceCount;
422
        }
423
 
177 ilm 424
        public SQLTable getTable() {
425
            return table;
426
        }
427
 
151 ilm 428
        @Override
429
        public String toString() {
430
            return this.getClass().getSimpleName() + ": of the " + this.passedIDs.size() + " passed, " + this.withDDWithoutMessage.size() + " needed a SDD message, of those "
431
                    + this.ignoredInvoices.allValues().size() + " were ignored (either being too far in the future or duplicate mandates) ; the inserted row was " + this.insertedMessage;
432
        }
433
    }
434
 
177 ilm 435
    public GenerationResult generateXML(final SQLTable table, Collection<? extends Number> invoiceIDs) throws SQLException {
151 ilm 436
        final Namespace painNS = Namespace.getNamespace("urn:iso:std:iso:20022:tech:xsd:pain.008.001.02");
437
        final Element rootElem = new Element("Document", painNS);
438
        final Namespace xsiNS = Namespace.getNamespace("xsi", "http://www.w3.org/2001/XMLSchema-instance");
439
        rootElem.setAttribute("schemaLocation", "urn:iso:std:iso:20022:tech:xsd:pain.008.001.02 pain.008.001.02.xsd", xsiNS);
440
        final Document doc = new Document(rootElem);
441
        final Element ddElem = new Element("CstmrDrctDbtInitn", painNS);
442
        rootElem.addContent(ddElem);
443
 
444
        // Lead Time / Time cycle
445
        // https://www.ecb.europa.eu/paym/retpaym/undpaym/qa/html/index.en.html
446
        final Calendar now = Calendar.getInstance();
447
        final Calendar cal = (Calendar) now.clone();
448
        TimeUtils.clearTime(cal);
449
        // perhaps handle business days and difference between FRST/OOFF (5 days) and RCURR/FNAL (2
450
        // days).
451
        cal.add(Calendar.DAY_OF_YEAR, this.dbPrefs.getInt(getCode() + ".leadDays", 7));
452
        final Date lowerBound = cal.getTime();
453
 
454
        cal.setTime(now.getTime());
455
        cal.add(Calendar.DAY_OF_YEAR, 30);
456
        final Date upperBound = cal.getTime();
457
 
458
        // TODO use createFetcher()
459
        final SQLRowValuesListFetcher selSociété = SQLRowValuesListFetcher.create(this.getDirectory().getElement(this.rowSociété.getTable()).createGraph());
460
        selSociété.setFullOnly(true);
461
        selSociété.appendSelTransf(new ITransformer<SQLSelect, SQLSelect>() {
462
            @Override
463
            public SQLSelect transformChecked(SQLSelect sel) {
464
                sel.setLockStrength(LockStrength.SHARE);
465
                return sel;
466
            }
467
        });
468
 
177 ilm 469
        final SQLField invoiceSDDMessageF = table.getField(SaisieVenteFactureSQLElement.MESSAGE_FIELD_NAME);
151 ilm 470
        return SQLUtils.executeAtomic(getTable().getDBSystemRoot().getDataSource(), new ConnectionHandlerNoSetup<GenerationResult, SQLException>() {
471
            @Override
472
            public GenerationResult handle(SQLDataSource ds) throws SQLException {
473
                final SQLRowValues lockedSociété = selSociété.fetchOne(SDDMessageSQLElement.this.rowSociété.getIDNumber());
474
                if (lockedSociété == null)
475
                    throw new IllegalStateException("Missing société " + SDDMessageSQLElement.this.rowSociété);
177 ilm 476
                if (lockedSociété.getString("IBAN").trim().length() == 0) {
477
                    throw new IllegalStateException("Missing société IBAN " + SDDMessageSQLElement.this.rowSociété);
478
                }
151 ilm 479
 
177 ilm 480
                if (lockedSociété.getString("BIC").trim().length() == 0) {
481
                    throw new IllegalStateException("Missing société BIC " + SDDMessageSQLElement.this.rowSociété);
482
                }
483
 
151 ilm 484
                // find and lock invoices with TYPE_REGLEMENT direct debit and no message
177 ilm 485
                final SQLRowValues invoiceVals = new SQLRowValues(table);
486
 
487
                final boolean fromInvoices = table.getName().equals("SAISIE_VENTE_FACTURE");
488
                if (fromInvoices) {
489
                    invoiceVals.putRowValues("ID_CLIENT").putNulls("NOM", "BIC", "IBAN");
490
                    invoiceVals.putRowValues("ID_MODE_REGLEMENT").putNulls("AJOURS", "LENJOUR").putRowValues("ID_SEPA_MANDATE").setAllToNull();
491
                    invoiceVals.putNulls("NET_A_PAYER", "DATE", "NUMERO", "NOM");
492
                } else {
493
                    invoiceVals.putRowValues("ID_CLIENT").putNulls("NOM", "BIC", "IBAN");
494
                    invoiceVals.putRowValues("ID_SEPA_MANDATE").setAllToNull();
495
                    invoiceVals.putNulls("MONTANT", "DATE");
496
                    invoiceVals.putRowValues("ID_MOUVEMENT").putRowValues("ID_PIECE").setAllToNull();
497
                }
498
 
151 ilm 499
                final SQLRowValuesListFetcher fetcher = SQLRowValuesListFetcher.create(invoiceVals);
500
                fetcher.setReturnedRowsUnmodifiable(true);
501
                // required for locking rows and to make sure that there's a SEPA Mandate
502
                fetcher.setFullOnly(true);
503
                fetcher.appendSelTransf(new ITransformer<SQLSelect, SQLSelect>() {
504
                    @Override
505
                    public SQLSelect transformChecked(SQLSelect sel) {
506
                        // we will update FACTURE.ID_MESSAGE
507
                        sel.setLockStrength(LockStrength.UPDATE);
177 ilm 508
                        if (fromInvoices) {
509
                            final SQLSelectJoin join = sel.getJoin(table.getField("ID_MODE_REGLEMENT"));
510
                            join.setWhere(new Where(join.getJoinedTable().getField("ID_TYPE_REGLEMENT"), "=", TypeReglementSQLElement.PRELEVEMENT));
511
                        }
151 ilm 512
                        return sel;
513
                    }
514
                });
515
                final List<SQLRowValues> ddInvoices = fetcher
177 ilm 516
                        .fetch(new Where(table.getKey(), invoiceIDs).and(new Where(invoiceSDDMessageF, "=", invoiceSDDMessageF.getForeignTable().getUndefinedIDNumber())));
151 ilm 517
                final InvoicesByPaymentInfo map = new InvoicesByPaymentInfo(lowerBound, upperBound, new ElementCreator(painNS, SDDMessageSQLElement.this.fieldTranslator));
518
                final ListMap<IgnoreReason, SQLRowValues> ignoredInvoices = new ListMap<>();
519
                for (final SQLRowValues invoice : ddInvoices) {
177 ilm 520
                    if (fromInvoices) {
521
                        final IgnoreReason ignoredReason = map.addInvoice(invoice);
522
                        if (ignoredReason != IgnoreReason.NONE) {
523
                            ignoredInvoices.add(ignoredReason, invoice);
524
                        }
525
                    } else {
526
                        final IgnoreReason ignoredReason = map.addEcheance(invoice);
527
                        if (ignoredReason != IgnoreReason.NONE) {
528
                            ignoredInvoices.add(ignoredReason, invoice);
529
                        }
151 ilm 530
                    }
531
                }
532
 
533
                final SQLRow newMsg;
534
                final int txCount = map.getTransactionCount();
535
                if (txCount == 0) {
536
                    newMsg = null;
537
                } else {
538
                    // find and lock message serial
539
                    final SQLTable mdT = getTable().getTable(SQLSchema.METADATA_TABLENAME);
540
                    final SQLSelect sel = new SQLSelect(true).addSelect(mdT.getField("VALUE"));
541
                    sel.setWhere(new Where(mdT.getField("NAME"), "=", SERIAL_MD));
542
                    sel.setLockStrength(LockStrength.UPDATE);
543
                    final String msgSerial = String.valueOf(Integer.parseInt((String) getTable().getDBSystemRoot().getDataSource().executeScalar(sel.asString())) + 1);
544
 
545
                    // generate XML
546
                    final BigDecimal totalSum = map.sum;
547
                    final Element groupHeaderElem = createGroupHeader(painNS, lockedSociété, now, msgSerial, txCount, totalSum);
548
                    ddElem.addContent(groupHeaderElem);
549
                    final String msgID = groupHeaderElem.getChild("MsgId", painNS).getText();
550
                    final Map<SQLRow, String> end2endIDs = new HashMap<>();
551
                    int index = 1;
552
                    for (final PaymentInfo info : map.getPaymentInfos()) {
553
                        try {
554
                            createPaymentInfo(ddElem, end2endIDs, map.elemCreator, lockedSociété, info, msgID, index++);
555
                        } catch (Exception e) {
556
                            throw new IllegalStateException("Couldn't create XML for " + info, e);
557
                        }
558
                    }
559
                    assert end2endIDs.size() == txCount : "Expected " + txCount + " transactions but got " + end2endIDs.size() + " rows : " + end2endIDs;
560
 
561
                    // insert message in DB
562
                    final SQLRowValues msgVals = new SQLRowValues(getTable());
563
                    msgVals.put("MessageIdentification", msgID);
564
                    msgVals.put("CreationDateTime", now.getTime());
565
                    msgVals.put("NumberOfTransactions", txCount);
566
                    msgVals.put("ControlSum", totalSum);
567
                    msgVals.put("XML", JDOM2Utils.output(doc));
568
                    newMsg = msgVals.insert();
569
 
570
                    // update invoices with new message
571
                    for (final Entry<SQLRow, String> e : end2endIDs.entrySet()) {
572
                        final SQLRowValues vals = e.getKey().createEmptyUpdateRow();
573
                        vals.putForeignID(invoiceSDDMessageF.getName(), newMsg);
574
                        vals.put(SaisieVenteFactureSQLElement.END2END_FIELD_NAME, e.getValue());
575
                        vals.update();
576
                    }
577
                    // update message serial
578
                    getTable().getDBRoot().setMetadata(SERIAL_MD, msgSerial);
579
                }
580
 
177 ilm 581
                return new GenerationResult(table, invoiceIDs, ddInvoices, ignoredInvoices, newMsg);
151 ilm 582
            }
583
        });
584
    }
585
 
586
    static private Element createGroupHeader(final Namespace painNS, final SQLRowValues lockedSociété, final Calendar now, final String msgSerial, final int txCount, final BigDecimal total) {
587
        final Element res = new Element("GrpHdr", painNS);
588
        res.addContent(new Element("MsgId", painNS).setText("openconcerto-" + now.get(Calendar.YEAR) + "-" + msgSerial));
589
        res.addContent(new Element("CreDtTm", painNS).setText(new XMLDateFormat().format(now)));
590
        res.addContent(new Element("NbOfTxs", painNS).setText(String.valueOf(txCount)));
591
        if (DecimalUtils.decimalDigits(total) > 2)
592
            throw new IllegalArgumentException("Too many decimals : " + total);
593
        res.addContent(new Element("CtrlSum", painNS).setText(total.toPlainString()));
594
        res.addContent(new Element("InitgPty", painNS).addContent(new Element("Nm", painNS).setText(getCompanyName(lockedSociété))));
595
        return res;
596
    }
597
 
598
    static private String getCompanyName(final SQLRowValues lockedSociété) {
599
        final String companyName = lockedSociété.getString("NOM");
600
        if (StringUtils.isEmpty(companyName))
601
            throw new IllegalStateException("Empty company name : " + lockedSociété);
602
        return lockedSociété.getString("TYPE") + ' ' + companyName;
603
    }
604
 
605
    static private final DateFormat XML_DATE_FMT = new SimpleDateFormat("yyyy-MM-dd");
606
 
607
    static synchronized private final String formatDate(final Date date) {
608
        return XML_DATE_FMT.format(date);
609
    }
610
 
611
    static private void createPaymentInfo(Element ddElem, Map<SQLRow, String> end2endIDs, final ElementCreator elemCreator, final SQLRowValues lockedSociété, final PaymentInfo info,
612
            final String msgID, final int index) throws SQLException, MissingInfoException {
613
        if (info.invoices.isEmpty())
614
            return;
615
 
616
        final Namespace painNS = elemCreator.painNS;
617
        final String formattedDate = formatDate(info.collectionDate);
618
 
619
        final Element res = new Element("PmtInf", painNS);
620
        res.addContent(new Element("PmtInfId", painNS).setText("openconcerto-" + formattedDate + '.' + index));
621
        res.addContent(new Element("PmtMtd", painNS).setText("DD"));
622
        res.addContent(new Element("BtchBookg", painNS).setText("false"));
623
        res.addContent(new Element("NbOfTxs", painNS).setText(String.valueOf(info.invoices.size())));
624
        if (DecimalUtils.decimalDigits(info.sum) > 2)
625
            throw new IllegalArgumentException("Too many decimals : " + info.sum);
626
        res.addContent(new Element("CtrlSum", painNS).setText(info.sum.toPlainString()));
627
 
628
        final Element typeInformation = new Element("PmtTpInf", painNS);
629
        typeInformation.addContent(new Element("SvcLvl", painNS).addContent(new Element("Cd", painNS).setText("SEPA")));
630
        typeInformation.addContent(new Element("LclInstrm", painNS).addContent(new Element("Cd", painNS).setText("CORE")));
631
        typeInformation.addContent(new Element("SeqTp", painNS).setText(info.seqType));
632
        res.addContent(typeInformation);
633
 
634
        res.addContent(new Element("ReqdColltnDt", painNS).setText(formattedDate));
635
 
636
        final Element creditor = new Element("Cdtr", painNS);
637
        creditor.addContent(new Element("Nm", painNS).setText(getCompanyName(lockedSociété)));
638
        final Element postalAddr = new Element("PstlAdr", painNS);
639
        final SQLRowAccessor addr = lockedSociété.getNonEmptyForeign("ID_ADRESSE_COMMON");
640
        final String country = addr.getString("PAYS");
641
        final String country2;
642
        if (StringUtils.isEmpty(country, true) || country.trim().equalsIgnoreCase("France")) {
643
            country2 = "FR";
644
        } else {
645
            // TODO map to 2 letter code
646
            throw new IllegalStateException("Unknown country : " + country);
647
        }
648
        postalAddr.addContent(new Element("Ctry", painNS).setText(country2));
649
        postalAddr.addContent(new Element("AdrLine", painNS).setText(addr.getString("RUE")));
650
        postalAddr.addContent(new Element("AdrLine", painNS).setText(addr.getString("CODE_POSTAL") + " " + addr.getString("VILLE")));
651
        creditor.addContent(postalAddr);
652
        res.addContent(creditor);
653
 
654
        final Element creditorAccount = new Element("CdtrAcct", painNS);
177 ilm 655
        String iban = lockedSociété.getString("IBAN").replaceAll(" ", "");
656
        creditorAccount.addContent(new Element("Id", painNS).addContent(elemCreator.createWithNonEmptyText("IBAN", iban, "IBAN")));
151 ilm 657
        res.addContent(creditorAccount);
658
 
659
        final Element creditorAgent = new Element("CdtrAgt", painNS);
660
        creditorAgent.addContent(new Element("FinInstnId", painNS).addContent(elemCreator.createWithNonEmptyText("BIC", lockedSociété, "BIC")));
661
        res.addContent(creditorAgent);
662
 
663
        res.addContent(new Element("ChrgBr", painNS).setText("SLEV"));
664
 
665
        final Element creditorID = new Element("CdtrSchmeId", painNS);
666
        final Element other = new Element("Othr", painNS);
667
        other.addContent(elemCreator.createWithNonEmptyText("Id", lockedSociété, "SEPA_CREDITOR_ID"));
668
        other.addContent(new Element("SchmeNm", painNS).addContent(new Element("Prtry", painNS).setText("SEPA")));
669
        creditorID.addContent(new Element("Id", painNS).addContent(new Element("PrvtId", painNS).addContent(other)));
670
        res.addContent(creditorID);
671
 
672
        for (final InvoiceElem invoice : info.invoices) {
673
            final String end2endID = msgID + '.' + invoice.get0().getString("NUMERO");
674
            if (end2endIDs.put(invoice.get0().asRow(), end2endID) != null)
675
                throw new IllegalStateException("Duplicate invoice : " + invoice);
676
            try {
677
                res.addContent(fillDDTx(invoice, elemCreator, end2endID));
678
            } catch (Exception e) {
679
                throw new IllegalStateException("Couldn't create XML for " + invoice, e);
680
            }
681
        }
682
 
683
        ddElem.addContent(res);
684
    }
685
 
686
    static private Element fillDDTx(final InvoiceElem invoiceElem, final ElementCreator elemCreator, final String end2endID) throws SQLException, MissingInfoException {
687
        final Element paymentId = new Element("PmtId", elemCreator.painNS);
688
        paymentId.addContent(elemCreator.createWithNonEmptyText("InstrId", end2endID));
689
        paymentId.addContent(elemCreator.createWithNonEmptyText("EndToEndId", end2endID));
690
        invoiceElem.get1().addContent(0, paymentId);
691
 
692
        // update mandate fields
177 ilm 693
        final SQLRowAccessor mandate;
694
        if (invoiceElem.get0().contains("ID_SEPA_MANDATE")) {
695
            mandate = invoiceElem.get0().getForeign("ID_SEPA_MANDATE");
696
        } else {
697
            mandate = invoiceElem.get0().getForeign("ID_MODE_REGLEMENT").getForeign("ID_SEPA_MANDATE");
698
        }
699
 
151 ilm 700
        final String seqType = mandate.getString("SequenceType");
701
        if (seqType.equals(SEPAMandateSQLElement.SEQ_FIRST)) {
702
            mandate.createEmptyUpdateRow().put("SequenceType", SEPAMandateSQLElement.SEQ_RECURRENT).update();
703
        } else if (seqType.equals(SEPAMandateSQLElement.SEQ_FINAL) || seqType.equals(SEPAMandateSQLElement.SEQ_ONEOFF)) {
704
            mandate.createEmptyUpdateRow().put("ACTIVE", Boolean.FALSE).update();
705
        } // else SEQ_RECURRENT
706
 
707
        return invoiceElem.get1();
708
    }
709
 
710
    static private Element createDDTx(final ElementCreator elemCreator, final SQLRowValues invoice) throws MissingInfoException {
711
        final Namespace painNS = elemCreator.painNS;
712
        final Element res = new Element("DrctDbtTxInf", painNS);
713
 
714
        res.addContent(new Element("InstdAmt", painNS).setAttribute("Ccy", "EUR").setText(getInvoiceAmount(invoice).toPlainString()));
715
 
716
        final Element mandateRltdInfo = new Element("MndtRltdInf", painNS);
177 ilm 717
 
718
        final SQLRowAccessor mandate;
719
        final boolean fromInvoice = invoice.getTable().getName().equals("SAISIE_VENTE_FACTURE");
720
        if (fromInvoice) {
721
            mandate = invoice.getForeign("ID_MODE_REGLEMENT").getForeign("ID_SEPA_MANDATE");
722
        } else {
723
            mandate = invoice.getForeign("ID_SEPA_MANDATE");
724
        }
151 ilm 725
        assert !mandate.isUndefined() : "Undefined mandate returned by fetcher";
726
        mandateRltdInfo.addContent(elemCreator.createWithNonEmptyText("MndtId", mandate, "MandateIdentification"));
727
        mandateRltdInfo.addContent(elemCreator.createWithNonEmptyText("DtOfSgntr", formatDate(mandate.getObjectAs("DateOfSignature", Date.class))));
728
        mandateRltdInfo.addContent(elemCreator.createWithNonEmptyText("AmdmntInd", "false"));
729
        res.addContent(new Element("DrctDbtTx", painNS).addContent(mandateRltdInfo));
730
 
731
        final SQLRowAccessor clientRow = invoice.getForeign("ID_CLIENT");
732
        res.addContent(new Element("DbtrAgt", painNS).addContent(new Element("FinInstnId", painNS).addContent(elemCreator.createWithNonEmptyText("BIC", clientRow, "BIC"))));
733
        res.addContent(new Element("Dbtr", painNS).addContent(elemCreator.createWithNonEmptyText("Nm", clientRow, "NOM")));
734
        res.addContent(new Element("DbtrAcct", painNS).addContent(new Element("Id", painNS).addContent(elemCreator.createWithNonEmptyText("IBAN", clientRow, "IBAN"))));
735
 
736
        res.addContent(new Element("Purp", painNS).addContent(new Element("Cd", painNS).setText("OTHR")));
737
 
177 ilm 738
        final String info;
739
        if (fromInvoice) {
740
            info = (invoice.getString("NUMERO") + ' ' + invoice.getString("NOM")).trim();
741
        } else {
742
            info = invoice.getForeign("ID_MOUVEMENT").getForeign("ID_PIECE").getString("NOM");
743
        }
156 ilm 744
        if (!info.isEmpty())
745
            res.addContent(new Element("RmtInf", painNS).addContent(elemCreator.create("Ustrd").setText(elemCreator.shortenText(info, 140))));
151 ilm 746
 
747
        return res;
748
    }
749
 
750
    static public final class MissingInfoException extends Exception {
751
 
752
        private final String label;
753
 
754
        protected MissingInfoException(final String label) {
755
            super("Empty " + label);
756
            this.label = label;
757
        }
758
 
759
        public final String getLabel() {
760
            return this.label;
761
        }
762
    }
763
 
764
    static private final class ElementCreator {
156 ilm 765
        static private final String TRUNCATED_SUFFIX = "...";
766
        static private final int TRUNCATED_SUFFIX_LENGTH = TRUNCATED_SUFFIX.length();
151 ilm 767
        private final Namespace painNS;
768
        private final SQLFieldTranslator fieldTrans;
769
 
770
        protected ElementCreator(Namespace painNS, final SQLFieldTranslator fieldTrans) {
771
            super();
772
            this.painNS = painNS;
773
            this.fieldTrans = fieldTrans;
774
        }
775
 
776
        protected Element create(final String elemName) {
777
            return new Element(elemName, this.painNS);
778
        }
779
 
780
        protected Element createWithNonEmptyText(final String elemName, final SQLRowAccessor r, final String field) throws MissingInfoException {
781
            return this.createWithNonEmptyText(elemName, r.getString(field), this.fieldTrans.getDescFor(r.getTable(), field).getLabel());
782
        }
783
 
784
        protected Element createWithNonEmptyText(final String elemName, final String text) throws MissingInfoException {
785
            return this.createWithNonEmptyText(elemName, text, null);
786
        }
787
 
788
        protected Element createWithNonEmptyText(final String elemName, final String text, final String label) throws MissingInfoException {
789
            if (StringUtils.isEmpty(text))
790
                throw new MissingInfoException(label == null ? elemName : label);
791
            return create(elemName).setText(text);
792
        }
156 ilm 793
 
794
        protected String shortenText(final String text, final int maxLength) {
795
            if (maxLength <= TRUNCATED_SUFFIX_LENGTH || text.length() <= maxLength)
796
                return text;
797
            return text.substring(0, maxLength - TRUNCATED_SUFFIX_LENGTH) + TRUNCATED_SUFFIX;
798
        }
151 ilm 799
    }
800
 
801
}