WebSVN - OpenConcerto - Diff - Rev 156 and 177 - /trunk/OpenConcerto/src/org/openconcerto/erp/core/finance/payment/element/SDDMessageSQLElement.java

 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;
         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 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);
+                        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(), l.getSelection().getUserSelectedIDs()));
+                        sel.setWhere(new Where(t.getKey(), userSelectedIDs));
-                exportXML(l, SQLRowListRSH.execute(sel));
+                        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();
+            }
+        }
+    }
     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);
+            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);
             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() {
         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(Collection<? extends Number> passedIDs, List<SQLRowValues> withDDWithoutMessage, ListMap<IgnoreReason, SQLRowValues> ignoredInvoices, SQLRow insertedMessage) {
+        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;
         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(Collection<? extends Number> invoiceIDs) throws SQLException {
+    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);
                 sel.setLockStrength(LockStrength.SHARE);
                 return sel;
+            }
         });
-        final SQLTable invoiceT = getDirectory().getElement(SaisieVenteFactureSQLElement.class).getTable();
-        final SQLField invoiceSDDMessageF = invoiceT.getField(SaisieVenteFactureSQLElement.MESSAGE_FIELD_NAME);
+        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(invoiceT);
+                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_CLIENT").putNulls("NOM", "BIC", "IBAN");
-                invoiceVals.putRowValues("ID_MODE_REGLEMENT").putNulls("AJOURS", "LENJOUR").putRowValues("ID_SEPA_MANDATE").setAllToNull();
+                    invoiceVals.putRowValues("ID_MODE_REGLEMENT").putNulls("AJOURS", "LENJOUR").putRowValues("ID_SEPA_MANDATE").setAllToNull();
-                invoiceVals.putNulls("NET_A_PAYER", "DATE", "NUMERO", "NOM");
+                    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(invoiceT.getField("ID_MODE_REGLEMENT"));
+                            final SQLSelectJoin join = sel.getJoin(table.getField("ID_MODE_REGLEMENT"));
-                        join.setWhere(new Where(join.getJoinedTable().getField("ID_TYPE_REGLEMENT"), "=", TypeReglementSQLElement.PRELEVEMENT));
+                            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())));
+                        .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);
+                        final IgnoreReason ignoredReason = map.addInvoice(invoice);
-                    if (ignoredReason != IgnoreReason.NONE) {
+                        if (ignoredReason != IgnoreReason.NONE) {
-                        ignoredInvoices.add(ignoredReason, invoice);
+                            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) {
+                    }
                     // update message serial
                     getTable().getDBRoot().setMetadata(SERIAL_MD, msgSerial);
+                }
-                return new GenerationResult(invoiceIDs, ddInvoices, ignoredInvoices, newMsg);
+                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) {
         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", lockedSociété, "IBAN")));
+        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);
         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 {
-        final SQLRowAccessor mandate = invoiceElem.get0().getForeign("ID_MODE_REGLEMENT").getForeign("ID_SEPA_MANDATE");
+            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();
         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) {
-        final SQLRowAccessor mandate = invoice.getForeign("ID_MODE_REGLEMENT").getForeign("ID_SEPA_MANDATE");
+            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));
         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) {
-        final String info = (invoice.getString("NUMERO") + ' ' + invoice.getString("NOM")).trim();
+            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;
+    }

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

svn://code.openconcerto.org/openconcerto

(root)/trunk/OpenConcerto/src/org/openconcerto/erp/core/finance/payment/element/SDDMessageSQLElement.java @ 180 - Rev 156 → 177