| 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.setFilenameFilter(new FilenameFilter() {
|
|
|
116 |
@Override
|
|
|
117 |
public boolean accept(File dir, String name) {
|
|
|
118 |
return name.endsWith("." + ContentTypeVersioned.SPREADSHEET.getExtension());
|
|
|
119 |
}
|
|
|
120 |
});
|
|
|
121 |
fd.setVisible(true);
|
|
|
122 |
rlPanel.setMode(ReloadPanel.MODE_ROTATE);
|
|
|
123 |
if (fd.getFile() != null) {
|
|
|
124 |
|
|
|
125 |
new Thread() {
|
| 149 |
ilm |
126 |
@Override
|
| 93 |
ilm |
127 |
public void run() {
|
| 149 |
ilm |
128 |
final File fileToImport = new File(fd.getDirectory(), fd.getFile());
|
| 93 |
ilm |
129 |
try {
|
| 149 |
ilm |
130 |
final ArrayTableModel model = loadData(fileToImport);
|
|
|
131 |
try {
|
|
|
132 |
final DBRoot rootSociete = ((ComptaPropsConfiguration) ComptaPropsConfiguration.getInstance()).getRootSociete();
|
|
|
133 |
SQLUtils.executeAtomic(rootSociete.getDBSystemRoot().getDataSource(), new ConnectionHandlerNoSetup<Object, IOException>() {
|
|
|
134 |
@Override
|
|
|
135 |
public Object handle(final SQLDataSource ds) throws SQLException, IOException {
|
|
|
136 |
try {
|
|
|
137 |
SQLSelect sel = new SQLSelect();
|
|
|
138 |
sel.addSelectStar(rootSociete.getTable("JOURNAL"));
|
|
|
139 |
List<SQLRow> rowsJrnl = SQLRowListRSH.execute(sel);
|
|
|
140 |
for (SQLRow sqlRow : rowsJrnl) {
|
|
|
141 |
mapJournal.put(sqlRow.getString("CODE"), sqlRow.getID());
|
|
|
142 |
}
|
|
|
143 |
final DateFormat format = new SimpleDateFormat("dd/MM/yyyy");
|
|
|
144 |
final String mouvementName = "Import " + format.format(new Date());
|
| 151 |
ilm |
145 |
SQLBackgroundTableCache.getInstance().getCacheForTable(rootSociete.getTable("COMPTE_PCE")).setEnableReloadIfTableModified(false);
|
| 149 |
ilm |
146 |
// Vérification des données
|
|
|
147 |
boolean ok = importTableModel(model, mouvementName, frame, true);
|
|
|
148 |
if (ok) {
|
| 151 |
ilm |
149 |
|
| 149 |
ilm |
150 |
// Importation des données
|
|
|
151 |
importTableModel(model, mouvementName, frame, false);
|
|
|
152 |
SwingUtilities.invokeLater(new Runnable() {
|
|
|
153 |
@Override
|
|
|
154 |
public void run() {
|
|
|
155 |
JOptionPane.showMessageDialog(null, "Importation des écritures terminée");
|
|
|
156 |
}
|
|
|
157 |
});
|
|
|
158 |
}
|
|
|
159 |
} catch (Exception exn) {
|
| 132 |
ilm |
160 |
ExceptionHandler.handle("Erreur pendant l'importation", exn);
|
| 151 |
ilm |
161 |
} finally {
|
|
|
162 |
SQLBackgroundTableCache.getInstance().getCacheForTable(rootSociete.getTable("COMPTE_PCE")).setEnableReloadIfTableModified(true);
|
| 132 |
ilm |
163 |
}
|
| 149 |
ilm |
164 |
return null;
|
| 93 |
ilm |
165 |
}
|
| 149 |
ilm |
166 |
});
|
|
|
167 |
} catch (Exception exn) {
|
|
|
168 |
ExceptionHandler.handle(frame, "Erreur lors de l'insertion dans la base", exn);
|
|
|
169 |
}
|
|
|
170 |
|
|
|
171 |
} catch (Exception e) {
|
| 156 |
ilm |
172 |
if (e.getMessage() != null && e.getMessage().toLowerCase().contains("file format")) {
|
| 149 |
ilm |
173 |
JOptionPane.showMessageDialog(ImportEcriturePanel.this, "Format de fichier non pris en charge");
|
|
|
174 |
} else {
|
|
|
175 |
ExceptionHandler.handle(frame, "Erreur lors de la lecture du fichier " + fileToImport.getAbsolutePath(), e);
|
|
|
176 |
}
|
| 93 |
ilm |
177 |
}
|
| 149 |
ilm |
178 |
|
| 93 |
ilm |
179 |
frame.dispose();
|
|
|
180 |
}
|
|
|
181 |
}.start();
|
|
|
182 |
}
|
|
|
183 |
}
|
|
|
184 |
});
|
|
|
185 |
}
|
|
|
186 |
|
| 149 |
ilm |
187 |
public ArrayTableModel loadData(File f) throws IOException {
|
|
|
188 |
final DataImporter importer = new DataImporter();
|
| 93 |
ilm |
189 |
importer.setSkipFirstLine(false);
|
| 149 |
ilm |
190 |
return importer.createModelFrom(f);
|
|
|
191 |
}
|
| 93 |
ilm |
192 |
|
| 149 |
ilm |
193 |
public boolean importTableModel(ArrayTableModel m, String mvtName, final Frame owner, boolean dryRun) throws Exception {
|
|
|
194 |
final DateFormat dF = new SimpleDateFormat("dd/MM/yyyy");
|
|
|
195 |
final GenerationEcritures gen = new GenerationEcritures();
|
|
|
196 |
int idMvt = -1;
|
|
|
197 |
if (!dryRun) {
|
|
|
198 |
idMvt = gen.getNewMouvement("", 1, 1, mvtName);
|
|
|
199 |
}
|
|
|
200 |
long soldeGlobal = 0;
|
|
|
201 |
String dateOrigin = null;
|
|
|
202 |
final int rowCount = m.getRowCount();
|
|
|
203 |
for (int i = 0; i < rowCount; i++) {
|
|
|
204 |
int column = 0;
|
|
|
205 |
try {
|
|
|
206 |
// Column 0
|
|
|
207 |
final Object firstValue = m.getValueAt(i, column);
|
|
|
208 |
if (firstValue == null) {
|
| 93 |
ilm |
209 |
break;
|
|
|
210 |
}
|
| 149 |
ilm |
211 |
final Date dateValue;
|
|
|
212 |
if (firstValue.getClass().isAssignableFrom(Date.class)) {
|
|
|
213 |
dateValue = (Date) firstValue;
|
|
|
214 |
} else if (firstValue.toString().trim().isEmpty()) {
|
|
|
215 |
break;
|
|
|
216 |
} else {
|
|
|
217 |
dateValue = dF.parse(firstValue.toString());
|
|
|
218 |
}
|
|
|
219 |
final String dateStringValue = dF.format(dateValue);
|
|
|
220 |
if (dateOrigin == null) {
|
|
|
221 |
dateOrigin = dateStringValue;
|
|
|
222 |
}
|
|
|
223 |
// Date
|
|
|
224 |
gen.putValue("DATE", dateValue);
|
|
|
225 |
column++;
|
| 93 |
ilm |
226 |
|
| 149 |
ilm |
227 |
if (!dateOrigin.equals(dateStringValue)) {
|
|
|
228 |
dateOrigin = dateStringValue;
|
|
|
229 |
if (!dryRun) {
|
|
|
230 |
idMvt = gen.getNewMouvement("", 1, 1, mvtName);
|
|
|
231 |
} else if (soldeGlobal != 0) {
|
|
|
232 |
final double soldeMvt = soldeGlobal / 100.0;
|
|
|
233 |
SwingUtilities.invokeLater(new Runnable() {
|
| 93 |
ilm |
234 |
|
| 149 |
ilm |
235 |
@Override
|
|
|
236 |
public void run() {
|
|
|
237 |
JOptionPane.showMessageDialog(null,
|
|
|
238 |
"Le mouvement du " + dateStringValue + " ne respecte pas la partie double (Solde du mouvement : " + soldeMvt + ")!\nImport annulé!");
|
|
|
239 |
}
|
|
|
240 |
});
|
|
|
241 |
return false;
|
|
|
242 |
}
|
|
|
243 |
}
|
| 93 |
ilm |
244 |
|
| 149 |
ilm |
245 |
// Journal
|
|
|
246 |
// Column 1
|
|
|
247 |
final String valueJrnl = m.getValueAt(i, column).toString();
|
|
|
248 |
if (!dryRun && mapJournal.get(valueJrnl) == null) {
|
| 93 |
ilm |
249 |
SwingUtilities.invokeAndWait(new Runnable() {
|
| 149 |
ilm |
250 |
@Override
|
| 93 |
ilm |
251 |
public void run() {
|
| 149 |
ilm |
252 |
final JDialog diag = new JDialog(owner);
|
| 93 |
ilm |
253 |
diag.setModal(true);
|
|
|
254 |
diag.setContentPane(new SelectionJournalImportPanel(valueJrnl, mapJournal, null));
|
| 149 |
ilm |
255 |
diag.setTitle("Import d'écritures");
|
| 93 |
ilm |
256 |
diag.setLocationRelativeTo(null);
|
|
|
257 |
diag.pack();
|
|
|
258 |
diag.setVisible(true);
|
|
|
259 |
}
|
|
|
260 |
});
|
| 149 |
ilm |
261 |
}
|
|
|
262 |
gen.putValue("ID_JOURNAL", this.mapJournal.get(valueJrnl));
|
|
|
263 |
column++;
|
| 93 |
ilm |
264 |
|
| 149 |
ilm |
265 |
// Compte
|
|
|
266 |
// Column 2
|
|
|
267 |
final String trim = m.getValueAt(i, column).toString().trim();
|
|
|
268 |
String numCompt = trim;
|
|
|
269 |
if (trim.contains(".")) {
|
|
|
270 |
numCompt = trim.substring(0, trim.indexOf('.'));
|
| 93 |
ilm |
271 |
}
|
| 149 |
ilm |
272 |
numCompt = numCompt.trim();
|
|
|
273 |
if (!dryRun) {
|
|
|
274 |
int idCpt = getOrCreateCompte(numCompt);
|
|
|
275 |
gen.putValue("ID_COMPTE_PCE", idCpt);
|
|
|
276 |
}
|
|
|
277 |
column++;
|
| 93 |
ilm |
278 |
|
| 149 |
ilm |
279 |
// Nom de la pièce
|
|
|
280 |
// Column 3
|
|
|
281 |
String stringPiece = m.getValueAt(i, column).toString();
|
|
|
282 |
if (stringPiece != null && stringPiece.length() > 0 && stringPiece.contains(".")) {
|
|
|
283 |
stringPiece = stringPiece.substring(0, stringPiece.indexOf('.'));
|
|
|
284 |
}
|
|
|
285 |
column++;
|
|
|
286 |
// Column 4
|
|
|
287 |
gen.putValue("NOM", m.getValueAt(i, column).toString() + " " + stringPiece);
|
|
|
288 |
column++;
|
| 93 |
ilm |
289 |
|
| 149 |
ilm |
290 |
// Montants
|
|
|
291 |
// Column 5
|
|
|
292 |
final String stringValueD = m.getValueAt(i, column).toString();
|
|
|
293 |
long montantD = GestionDevise.parseLongCurrency(stringValueD);
|
|
|
294 |
column++;
|
|
|
295 |
// Column 6
|
|
|
296 |
final String stringValueC = m.getValueAt(i, column).toString();
|
|
|
297 |
long montantC = GestionDevise.parseLongCurrency(stringValueC);
|
|
|
298 |
gen.putValue("CREDIT", montantC);
|
|
|
299 |
gen.putValue("DEBIT", montantD);
|
|
|
300 |
soldeGlobal += montantD;
|
|
|
301 |
soldeGlobal -= montantC;
|
| 93 |
ilm |
302 |
|
| 149 |
ilm |
303 |
// Mouvement
|
|
|
304 |
gen.putValue("ID_MOUVEMENT", idMvt);
|
| 93 |
ilm |
305 |
|
| 149 |
ilm |
306 |
gen.putValue("NOM", m.getValueAt(i, 4).toString() + " " + stringPiece);
|
|
|
307 |
} catch (Exception e) {
|
|
|
308 |
throw new IllegalStateException("Donnée invalide sur la ligne " + (i + 1) + " , colonne " + (column + 1), e);
|
| 93 |
ilm |
309 |
}
|
| 149 |
ilm |
310 |
if (!dryRun) {
|
|
|
311 |
gen.ajoutEcriture();
|
|
|
312 |
}
|
| 93 |
ilm |
313 |
}
|
| 149 |
ilm |
314 |
if (soldeGlobal != 0) {
|
|
|
315 |
throw new IllegalArgumentException("La partie double n'est respectée (solde = " + soldeGlobal + "). Import annulé!");
|
| 93 |
ilm |
316 |
}
|
| 149 |
ilm |
317 |
return true;
|
|
|
318 |
}
|
| 93 |
ilm |
319 |
|
| 149 |
ilm |
320 |
private int getOrCreateCompte(String numeroCompte) {
|
|
|
321 |
if (mapCompte.containsKey(numeroCompte)) {
|
|
|
322 |
return mapCompte.get(numeroCompte);
|
|
|
323 |
}
|
|
|
324 |
int id = ComptePCESQLElement.getId(numeroCompte);
|
|
|
325 |
mapCompte.put(numeroCompte, id);
|
|
|
326 |
return id;
|
| 93 |
ilm |
327 |
}
|
|
|
328 |
}
|
