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
93 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.accounting.ui;
15
 
16
import org.openconcerto.erp.config.ComptaPropsConfiguration;
17
import org.openconcerto.erp.core.finance.accounting.element.ComptePCESQLElement;
18
import org.openconcerto.erp.generationEcritures.GenerationEcritures;
19
import org.openconcerto.erp.importer.ArrayTableModel;
20
import org.openconcerto.erp.importer.DataImporter;
21
import org.openconcerto.openoffice.ContentTypeVersioned;
22
import org.openconcerto.sql.model.ConnectionHandlerNoSetup;
23
import org.openconcerto.sql.model.DBRoot;
151 ilm 24
import org.openconcerto.sql.model.SQLBackgroundTableCache;
93 ilm 25
import org.openconcerto.sql.model.SQLDataSource;
149 ilm 26
import org.openconcerto.sql.model.SQLRow;
27
import org.openconcerto.sql.model.SQLRowListRSH;
28
import org.openconcerto.sql.model.SQLSelect;
93 ilm 29
import org.openconcerto.sql.utils.SQLUtils;
30
import org.openconcerto.ui.DefaultGridBagConstraints;
31
import org.openconcerto.ui.ReloadPanel;
32
import org.openconcerto.ui.SwingThreadUtils;
33
import org.openconcerto.utils.ExceptionHandler;
34
import org.openconcerto.utils.GestionDevise;
35
 
36
import java.awt.FileDialog;
37
import java.awt.Frame;
38
import java.awt.GridBagConstraints;
39
import java.awt.GridBagLayout;
40
import java.awt.event.ActionEvent;
41
import java.awt.event.ActionListener;
42
import java.io.File;
43
import java.io.FilenameFilter;
44
import java.io.IOException;
45
import java.sql.SQLException;
46
import java.text.DateFormat;
47
import java.text.SimpleDateFormat;
48
import java.util.Date;
49
import java.util.HashMap;
149 ilm 50
import java.util.List;
93 ilm 51
import java.util.Map;
52
 
53
import javax.swing.JButton;
54
import javax.swing.JDialog;
55
import javax.swing.JLabel;
56
import javax.swing.JOptionPane;
57
import javax.swing.JPanel;
58
import javax.swing.SwingUtilities;
59
 
60
public class ImportEcriturePanel extends JPanel {
61
 
149 ilm 62
    private final Map<String, Integer> mapJournal = new HashMap<>();
63
    private final Map<String, Integer> mapCompte = new HashMap<>();
93 ilm 64
 
65
    public ImportEcriturePanel() {
66
        super(new GridBagLayout());
67
 
68
        JLabel label = new JLabel("Import depuis un fichier CSV, XLS ou ODT.");
69
        JLabel label2 = new JLabel("Le fichier doit contenir les colonnes :");
144 ilm 70
        JLabel label3 = new JLabel(" - Date dd/MM/yyyy (dd/MM/yy pour le CSV)");
93 ilm 71
        JLabel label4 = new JLabel(" - Journal");
72
        JLabel label5 = new JLabel(" - N° de compte");
73
        JLabel label6 = new JLabel(" - Nom de la pièce");
74
        JLabel label7 = new JLabel(" - Libellé");
75
        JLabel label8 = new JLabel(" - Débit");
76
        JLabel label9 = new JLabel(" - Crédit");
77
        final JButton button = new JButton("Sélectionner le ficher");
78
        GridBagConstraints c = new DefaultGridBagConstraints();
79
        c.gridwidth = 2;
80
        this.add(label, c);
81
        c.gridy++;
82
        this.add(label2, c);
83
        c.gridy++;
84
        this.add(label3, c);
85
        c.gridy++;
86
        this.add(label4, c);
87
        c.gridy++;
88
        this.add(label5, c);
89
        c.gridy++;
90
        this.add(label6, c);
91
        c.gridy++;
92
        this.add(label7, c);
93
        c.gridy++;
94
        this.add(label8, c);
95
        c.gridy++;
96
        this.add(label9, c);
97
        c.gridy++;
98
        c.gridwidth = 1;
99
        c.weightx = 1;
100
        final ReloadPanel rlPanel = new ReloadPanel();
101
        c.anchor = GridBagConstraints.EAST;
102
        c.fill = GridBagConstraints.NONE;
103
        this.add(rlPanel, c);
104
        c.gridx++;
105
        c.weightx = 0;
106
        this.add(button, c);
107
 
108
        button.addActionListener(new ActionListener() {
109
 
110
            @Override
111
            public void actionPerformed(ActionEvent e) {
112
                button.setEnabled(false);
113
                final Frame frame = SwingThreadUtils.getAncestorOrSelf(Frame.class, ImportEcriturePanel.this);
114
                final FileDialog fd = new FileDialog(frame, "Import d'écritures", FileDialog.LOAD);
115
                fd.setVisible(true);
116
                rlPanel.setMode(ReloadPanel.MODE_ROTATE);
117
                if (fd.getFile() != null) {
118
 
119
                    new Thread() {
149 ilm 120
                        @Override
93 ilm 121
                        public void run() {
149 ilm 122
                            final File fileToImport = new File(fd.getDirectory(), fd.getFile());
93 ilm 123
                            try {
149 ilm 124
                                final ArrayTableModel model = loadData(fileToImport);
125
                                try {
126
                                    final DBRoot rootSociete = ((ComptaPropsConfiguration) ComptaPropsConfiguration.getInstance()).getRootSociete();
127
                                    SQLUtils.executeAtomic(rootSociete.getDBSystemRoot().getDataSource(), new ConnectionHandlerNoSetup<Object, IOException>() {
128
                                        @Override
129
                                        public Object handle(final SQLDataSource ds) throws SQLException, IOException {
130
                                            try {
131
                                                SQLSelect sel = new SQLSelect();
132
                                                sel.addSelectStar(rootSociete.getTable("JOURNAL"));
133
                                                List<SQLRow> rowsJrnl = SQLRowListRSH.execute(sel);
134
                                                for (SQLRow sqlRow : rowsJrnl) {
135
                                                    mapJournal.put(sqlRow.getString("CODE"), sqlRow.getID());
136
                                                }
137
                                                final DateFormat format = new SimpleDateFormat("dd/MM/yyyy");
138
                                                final String mouvementName = "Import " + format.format(new Date());
151 ilm 139
                                                SQLBackgroundTableCache.getInstance().getCacheForTable(rootSociete.getTable("COMPTE_PCE")).setEnableReloadIfTableModified(false);
149 ilm 140
                                                // Vérification des données
141
                                                boolean ok = importTableModel(model, mouvementName, frame, true);
142
                                                if (ok) {
151 ilm 143
 
149 ilm 144
                                                    // Importation des données
145
                                                    importTableModel(model, mouvementName, frame, false);
146
                                                    SwingUtilities.invokeLater(new Runnable() {
147
                                                        @Override
148
                                                        public void run() {
149
                                                            JOptionPane.showMessageDialog(null, "Importation des écritures terminée");
150
                                                        }
151
                                                    });
152
                                                }
153
                                            } catch (Exception exn) {
132 ilm 154
                                                ExceptionHandler.handle("Erreur pendant l'importation", exn);
151 ilm 155
                                            } finally {
156
                                                SQLBackgroundTableCache.getInstance().getCacheForTable(rootSociete.getTable("COMPTE_PCE")).setEnableReloadIfTableModified(true);
132 ilm 157
                                            }
149 ilm 158
                                            return null;
93 ilm 159
                                        }
149 ilm 160
                                    });
161
                                } catch (Exception exn) {
162
                                    ExceptionHandler.handle(frame, "Erreur lors de l'insertion dans la base", exn);
163
                                }
164
 
165
                            } catch (Exception e) {
156 ilm 166
                                if (e.getMessage() != null && e.getMessage().toLowerCase().contains("file format")) {
149 ilm 167
                                    JOptionPane.showMessageDialog(ImportEcriturePanel.this, "Format de fichier non pris en charge");
168
                                } else {
169
                                    ExceptionHandler.handle(frame, "Erreur lors de la lecture du fichier " + fileToImport.getAbsolutePath(), e);
170
                                }
93 ilm 171
                            }
149 ilm 172
 
93 ilm 173
                            frame.dispose();
174
                        }
175
                    }.start();
176
                }
177
            }
178
        });
179
    }
180
 
149 ilm 181
    public ArrayTableModel loadData(File f) throws IOException {
182
        final DataImporter importer = new DataImporter();
93 ilm 183
        importer.setSkipFirstLine(false);
149 ilm 184
        return importer.createModelFrom(f);
185
    }
93 ilm 186
 
149 ilm 187
    public boolean importTableModel(ArrayTableModel m, String mvtName, final Frame owner, boolean dryRun) throws Exception {
188
        final DateFormat dF = new SimpleDateFormat("dd/MM/yyyy");
189
        final GenerationEcritures gen = new GenerationEcritures();
190
        int idMvt = -1;
191
        if (!dryRun) {
192
            idMvt = gen.getNewMouvement("", 1, 1, mvtName);
193
        }
194
        long soldeGlobal = 0;
195
        String dateOrigin = null;
196
        final int rowCount = m.getRowCount();
197
        for (int i = 0; i < rowCount; i++) {
198
            int column = 0;
199
            try {
200
                // Column 0
201
                final Object firstValue = m.getValueAt(i, column);
202
                if (firstValue == null) {
93 ilm 203
                    break;
204
                }
149 ilm 205
                final Date dateValue;
206
                if (firstValue.getClass().isAssignableFrom(Date.class)) {
207
                    dateValue = (Date) firstValue;
208
                } else if (firstValue.toString().trim().isEmpty()) {
209
                    break;
210
                } else {
211
                    dateValue = dF.parse(firstValue.toString());
212
                }
213
                final String dateStringValue = dF.format(dateValue);
214
                if (dateOrigin == null) {
215
                    dateOrigin = dateStringValue;
216
                }
217
                // Date
218
                gen.putValue("DATE", dateValue);
219
                column++;
93 ilm 220
 
149 ilm 221
                if (!dateOrigin.equals(dateStringValue)) {
222
                    dateOrigin = dateStringValue;
223
                    if (!dryRun) {
224
                        idMvt = gen.getNewMouvement("", 1, 1, mvtName);
225
                    } else if (soldeGlobal != 0) {
226
                        final double soldeMvt = soldeGlobal / 100.0;
227
                        SwingUtilities.invokeLater(new Runnable() {
93 ilm 228
 
149 ilm 229
                            @Override
230
                            public void run() {
231
                                JOptionPane.showMessageDialog(null,
232
                                        "Le mouvement du " + dateStringValue + " ne respecte pas la partie double (Solde du mouvement : " + soldeMvt + ")!\nImport annulé!");
233
                            }
234
                        });
235
                        return false;
236
                    }
237
                }
93 ilm 238
 
149 ilm 239
                // Journal
240
                // Column 1
241
                final String valueJrnl = m.getValueAt(i, column).toString();
242
                if (!dryRun && mapJournal.get(valueJrnl) == null) {
93 ilm 243
                    SwingUtilities.invokeAndWait(new Runnable() {
149 ilm 244
                        @Override
93 ilm 245
                        public void run() {
149 ilm 246
                            final JDialog diag = new JDialog(owner);
93 ilm 247
                            diag.setModal(true);
248
                            diag.setContentPane(new SelectionJournalImportPanel(valueJrnl, mapJournal, null));
149 ilm 249
                            diag.setTitle("Import d'écritures");
93 ilm 250
                            diag.setLocationRelativeTo(null);
251
                            diag.pack();
252
                            diag.setVisible(true);
253
                        }
254
                    });
149 ilm 255
                }
256
                gen.putValue("ID_JOURNAL", this.mapJournal.get(valueJrnl));
257
                column++;
93 ilm 258
 
149 ilm 259
                // Compte
260
                // Column 2
261
                final String trim = m.getValueAt(i, column).toString().trim();
262
                String numCompt = trim;
263
                if (trim.contains(".")) {
264
                    numCompt = trim.substring(0, trim.indexOf('.'));
93 ilm 265
                }
149 ilm 266
                numCompt = numCompt.trim();
267
                if (!dryRun) {
268
                    int idCpt = getOrCreateCompte(numCompt);
269
                    gen.putValue("ID_COMPTE_PCE", idCpt);
270
                }
271
                column++;
93 ilm 272
 
149 ilm 273
                // Nom de la pièce
274
                // Column 3
275
                String stringPiece = m.getValueAt(i, column).toString();
276
                if (stringPiece != null && stringPiece.length() > 0 && stringPiece.contains(".")) {
277
                    stringPiece = stringPiece.substring(0, stringPiece.indexOf('.'));
278
                }
279
                column++;
280
                // Column 4
281
                gen.putValue("NOM", m.getValueAt(i, column).toString() + " " + stringPiece);
282
                column++;
93 ilm 283
 
149 ilm 284
                // Montants
285
                // Column 5
286
                final String stringValueD = m.getValueAt(i, column).toString();
287
                long montantD = GestionDevise.parseLongCurrency(stringValueD);
288
                column++;
289
                // Column 6
290
                final String stringValueC = m.getValueAt(i, column).toString();
291
                long montantC = GestionDevise.parseLongCurrency(stringValueC);
292
                gen.putValue("CREDIT", montantC);
293
                gen.putValue("DEBIT", montantD);
294
                soldeGlobal += montantD;
295
                soldeGlobal -= montantC;
93 ilm 296
 
149 ilm 297
                // Mouvement
298
                gen.putValue("ID_MOUVEMENT", idMvt);
93 ilm 299
 
149 ilm 300
                gen.putValue("NOM", m.getValueAt(i, 4).toString() + " " + stringPiece);
301
            } catch (Exception e) {
302
                throw new IllegalStateException("Donnée invalide sur la ligne " + (i + 1) + " , colonne " + (column + 1), e);
93 ilm 303
            }
149 ilm 304
            if (!dryRun) {
305
                gen.ajoutEcriture();
306
            }
93 ilm 307
        }
149 ilm 308
        if (soldeGlobal != 0) {
309
            throw new IllegalArgumentException("La partie double n'est respectée (solde = " + soldeGlobal + "). Import annulé!");
93 ilm 310
        }
149 ilm 311
        return true;
312
    }
93 ilm 313
 
149 ilm 314
    private int getOrCreateCompte(String numeroCompte) {
315
        if (mapCompte.containsKey(numeroCompte)) {
316
            return mapCompte.get(numeroCompte);
317
        }
318
        int id = ComptePCESQLElement.getId(numeroCompte);
319
        mapCompte.put(numeroCompte, id);
320
        return id;
93 ilm 321
    }
322
}