OpenConcerto

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

svn://code.openconcerto.org/openconcerto

Rev

Rev 144 | Rev 174 | Go to most recent revision | Details | Compare with Previous | Last modification | View Log | RSS feed

Rev Author Line No. Line
19 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.importer;
15
 
16
import org.openconcerto.erp.config.ComptaPropsConfiguration;
17
import org.openconcerto.openoffice.spreadsheet.Sheet;
18
import org.openconcerto.openoffice.spreadsheet.SpreadSheet;
19
import org.openconcerto.sql.Configuration;
80 ilm 20
import org.openconcerto.sql.model.SQLBase;
19 ilm 21
import org.openconcerto.sql.model.SQLField;
22
import org.openconcerto.sql.model.SQLRowValues;
156 ilm 23
import org.openconcerto.sql.model.SQLRowValuesCluster.StoreMode;
19 ilm 24
import org.openconcerto.sql.model.SQLRowValuesListFetcher;
25
import org.openconcerto.sql.model.SQLTable;
26
import org.openconcerto.sql.users.UserManager;
27
import org.openconcerto.utils.text.CSVReader;
83 ilm 28
import org.openconcerto.utils.text.CSVWriter;
19 ilm 29
import org.openconcerto.utils.text.CharsetHelper;
30
 
31
import java.io.BufferedInputStream;
32
import java.io.BufferedReader;
33
import java.io.File;
34
import java.io.FileInputStream;
83 ilm 35
import java.io.FileOutputStream;
19 ilm 36
import java.io.IOException;
37
import java.io.InputStream;
38
import java.io.InputStreamReader;
83 ilm 39
import java.io.OutputStreamWriter;
19 ilm 40
import java.nio.charset.Charset;
41
import java.sql.SQLException;
42
import java.util.ArrayList;
43
import java.util.Collections;
44
import java.util.HashMap;
45
import java.util.Iterator;
46
import java.util.List;
47
import java.util.Map;
48
import java.util.Set;
49
 
93 ilm 50
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
19 ilm 51
import org.apache.poi.hssf.usermodel.HSSFSheet;
52
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
53
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
54
import org.apache.poi.ss.usermodel.Cell;
55
import org.apache.poi.ss.usermodel.CellValue;
56
import org.apache.poi.ss.usermodel.FormulaEvaluator;
57
import org.apache.poi.ss.usermodel.Row;
58
 
59
public class DataImporter {
60
    private boolean skipFirstLine = true;
144 ilm 61
    private SQLTable table;
19 ilm 62
    private final Map<Integer, ValueConverter> map = new HashMap<Integer, ValueConverter>();
63
    private final Map<SQLField, List<Integer>> fieldMap = new HashMap<SQLField, List<Integer>>();
64
    private final Map<Integer, Constraint> constraints = new HashMap<Integer, Constraint>();
65
    private List<SQLField> uniqueField = new ArrayList<SQLField>();
66
    private List<SQLRowValues> valuesToUpdate = new ArrayList<SQLRowValues>();
67
    private List<SQLRowValues> valuesToInsert = new ArrayList<SQLRowValues>();
68
    private Map<ValueConverter, SQLField> foreignMap = new HashMap<ValueConverter, SQLField>();
69
 
144 ilm 70
    public DataImporter() {
71
    }
72
 
19 ilm 73
    public DataImporter(SQLTable table) {
74
        this.table = table;
75
    }
76
 
144 ilm 77
    public void setTable(SQLTable table) {
78
        this.table = table;
79
    }
80
 
19 ilm 81
    public static void main(String[] args) throws Exception {
80 ilm 82
        System.setProperty(SQLBase.STRUCTURE_USE_XML, "true");
19 ilm 83
        final ComptaPropsConfiguration conf = ComptaPropsConfiguration.create();
84
        Configuration.setInstance(conf);
85
        try {
86
            conf.getBase();
87
        } catch (Exception e) {
88
            e.printStackTrace();
89
        }
90
 
91
        final ComptaPropsConfiguration comptaPropsConfiguration = ((ComptaPropsConfiguration) Configuration.getInstance());
92
        comptaPropsConfiguration.setUpSocieteDataBaseConnexion(39);
93
        UserManager.getInstance().setCurrentUser(2);
94
 
95
        SQLTable table = Configuration.getInstance().getRoot().findTable("ARTICLE");
96
        DataImporter importer = new DataImporter(table);
97
        importer.skipFirstLine = false;
98
        importer.map(0, table.getField("CODE"));
99
        importer.map(8, table.getField("ID_FOURNISSEUR"));
100
        importer.map(4, table.getField("NOM"));
101
        importer.addContraint(0, new NotEmptyConstraint());
102
        importer.addUniqueField(table.getField("CODE"));
103
        // ArrayTableModel m = importer.createModelFromODS(new File("c:/products-en.ods"));
104
        // ArrayTableModel m = importer.createModelFromCSV(new File("c:/products-en.csv"));
105
        // ArrayTableModel m = importer.createModelFromCSV(new File("c:/products-en.scsv.csv"));
83 ilm 106
        ArrayTableModel m = importer.createModelFromXLS(new File("c:/products-en.xls"), 0);
19 ilm 107
        m.dump(0, 4);
108
        m = importer.createConvertedModel(m);
109
        System.out.println("Dump");
110
        m.dump(0, 4);
111
        importer.importFromModel(m);
112
        System.out.println(importer.getValuesToInsert().size() + " rows to insert");
113
        System.out.println(importer.getValuesToUpdate().size() + " rows to update");
114
        // importer.commit();
115
 
116
    }
117
 
118
    public void commit() throws SQLException {
119
        for (SQLRowValues row : this.valuesToInsert) {
156 ilm 120
            row.getGraph().store(StoreMode.INSERT, false);
19 ilm 121
        }
122
        for (SQLRowValues row : this.valuesToUpdate) {
156 ilm 123
            row.getGraph().store(StoreMode.COMMIT, false);
19 ilm 124
        }
83 ilm 125
        doAfterImport();
19 ilm 126
    }
127
 
128
    public List<SQLRowValues> getValuesToInsert() {
129
        return valuesToInsert;
130
    }
131
 
132
    public List<SQLRowValues> getValuesToUpdate() {
133
        return valuesToUpdate;
134
    }
135
 
136
    public void addUniqueField(SQLField field) {
137
        if (this.uniqueField.contains(field)) {
138
            throw new IllegalStateException("Field " + field + " already specified");
139
        }
140
        this.uniqueField.add(field);
141
 
142
    }
143
 
144
    public void addContraint(int columnIndex, Constraint c) {
145
        constraints.put(Integer.valueOf(columnIndex), c);
146
    }
147
 
148
    public void map(int columnIndex, SQLField field) {
149
        map(columnIndex, field, new ValueConverter(field));
150
    }
151
 
152
    public void map(int columnIndex, ValueConverter converter) {
153
        map(columnIndex, converter.getField(), converter);
154
    }
155
 
156
    public void map(int columnIndex, SQLField field, SQLField foreignField) {
157
        final ValueConverter converter = new ValueConverter(foreignField);
158
        map(columnIndex, foreignField, converter);
159
        foreignMap.put(converter, field);
160
 
161
    }
162
 
163
    public void map(int columnIndex, SQLField field, ValueConverter converter) {
164
        final Integer value = Integer.valueOf(columnIndex);
165
        map.put(value, converter);
166
        List<Integer> l = fieldMap.get(field);
167
        if (l == null) {
168
            l = new ArrayList<Integer>();
169
            fieldMap.put(field, l);
170
        } else if (!field.getType().getJavaType().equals(String.class)) {
171
            throw new IllegalArgumentException("Mapping multiple column is only supoprted for String values");
172
        }
173
        if (l.contains(value)) {
174
            throw new IllegalArgumentException("Column " + columnIndex + " already mapped for field " + field.getFullName());
175
        }
176
        l.add(value);
177
    }
178
 
83 ilm 179
    public ArrayTableModel createModelFromODS(File odsFile, int sheetNumber) throws IOException {
19 ilm 180
        final SpreadSheet spreadSheet = SpreadSheet.createFromFile(odsFile);
181
        if (spreadSheet.getSheetCount() < 1) {
182
            return null;
183
        }
83 ilm 184
        final Sheet sheet = spreadSheet.getSheet(sheetNumber);
19 ilm 185
        final int rowCount = sheet.getRowCount();
21 ilm 186
        int columnCount = 0;
187
        if (rowCount > 0) {
188
            final int maxColumnCount = sheet.getColumnCount();
189
            for (int j = 0; j < maxColumnCount; j++) {
190
                final Object valueAt = sheet.getValueAt(j, 0);
191
                if (valueAt == null || valueAt.toString().trim().isEmpty()) {
192
                    break;
193
                }
194
                columnCount++;
195
            }
196
        }
19 ilm 197
        int start = 0;
198
        if (skipFirstLine) {
199
            start = 1;
200
        }
201
        final List<List<Object>> rows = new ArrayList<List<Object>>(rowCount - start);
202
        for (int i = start; i < rowCount; i++) {
203
            List<Object> row = new ArrayList<Object>();
204
            for (int j = 0; j < columnCount; j++) {
205
                row.add(sheet.getValueAt(j, i));
206
            }
207
            rows.add(row);
208
        }
209
 
210
        return new ArrayTableModel(rows);
211
    }
212
 
83 ilm 213
    public ArrayTableModel createModelFromXLS(File xlsFile, int sheetNumber) throws IOException {
19 ilm 214
        final InputStream inputStream = new FileInputStream(xlsFile);
215
        final POIFSFileSystem fileSystem = new POIFSFileSystem(new BufferedInputStream(inputStream));
216
        final HSSFWorkbook workBook = new HSSFWorkbook(fileSystem);
83 ilm 217
        final HSSFSheet sheet = workBook.getSheetAt(sheetNumber);
19 ilm 218
        Iterator<Row> rowsIterator = sheet.rowIterator();
219
        int columnCount = 0;
220
        int rowCount = 0;
221
        while (rowsIterator.hasNext()) {
222
            Row row = rowsIterator.next();
223
            int i = row.getPhysicalNumberOfCells();
224
            if (i > columnCount) {
225
                columnCount = i;
226
            }
227
            rowCount++;
228
        }
229
        // Extract data
230
        rowsIterator = sheet.rowIterator();
231
        int start = 0;
232
        if (skipFirstLine) {
233
            start = 1;
234
            rowsIterator.next();
235
        }
236
        final List<List<Object>> rows = new ArrayList<List<Object>>(rowCount - start);
237
        FormulaEvaluator evaluator = workBook.getCreationHelper().createFormulaEvaluator();
238
 
239
        while (rowsIterator.hasNext()) {
240
            final Row row = rowsIterator.next();
241
            final List<Object> rowData = new ArrayList<Object>();
242
            for (int i = 0; i < columnCount; i++) {
243
                final Cell cell = row.getCell(i);
244
 
245
                if (cell == null) {
246
                    rowData.add("");
247
                } else {
248
                    CellValue cellValue = evaluator.evaluate(cell);
249
                    if (cellValue == null) {
250
                        rowData.add("");
251
                    } else {
252
                        switch (cellValue.getCellType()) {
253
                        case Cell.CELL_TYPE_BOOLEAN:
254
                            rowData.add(Boolean.valueOf(cellValue.getBooleanValue()));
255
                            break;
256
                        case Cell.CELL_TYPE_NUMERIC:
93 ilm 257
 
258
                            if (HSSFDateUtil.isCellDateFormatted(cell)) {
259
                                System.out.println("Row No.: " + row.getRowNum() + " " + cell.getDateCellValue());
260
                                rowData.add(cell.getDateCellValue());
261
                            } else {
262
                                rowData.add(Double.valueOf(cellValue.getNumberValue()));
263
                            }
19 ilm 264
                            break;
265
                        case Cell.CELL_TYPE_STRING:
266
                            rowData.add(cellValue.getStringValue());
267
                            break;
268
                        case Cell.CELL_TYPE_FORMULA:
269
                            rowData.add(cell.getCellFormula());
270
                            break;
271
                        case Cell.CELL_TYPE_BLANK:
272
                            rowData.add("");
273
                            break;
274
                        default:
275
                            rowData.add(cellValue.getStringValue());
276
                            break;
277
 
278
                        }
279
                    }
280
                }
281
            }
282
 
283
            rows.add(rowData);
284
 
285
        }
286
        inputStream.close();
287
        return new ArrayTableModel(rows);
288
 
289
    }
290
 
291
    public ArrayTableModel createModelFromCSV(File csvFile) throws IOException {
292
        Charset cs = CharsetHelper.guessEncoding(csvFile, 4096, Charset.forName("Cp1252"));
293
 
294
        BufferedReader r = new BufferedReader(new InputStreamReader(new FileInputStream(csvFile), cs));
295
        String l = r.readLine();
296
        if (l == null) {
132 ilm 297
            r.close();
19 ilm 298
            return null;
299
        }
300
        char separator = ',';
301
        int cCount = 0;
302
        int scCount = 0;
303
        for (int i = 0; i < l.length(); i++) {
304
            char c = l.charAt(i);
305
            if (c == ',') {
306
                cCount++;
307
            } else if (c == ';') {
308
                scCount++;
309
            }
310
        }
311
        r.close();
312
        if (scCount > cCount) {
313
            separator = ';';
314
        }
315
 
316
        CSVReader csvReader = new CSVReader(new InputStreamReader(new FileInputStream(csvFile), cs), separator);
317
        List<String[]> lines = csvReader.readAll();
318
        final int rowCount = lines.size();
319
        final int columnCount = lines.get(0).length;
320
 
321
        int start = 0;
322
        if (skipFirstLine) {
323
            start = 1;
324
        }
325
        final List<List<Object>> rows = new ArrayList<List<Object>>(rowCount - start);
326
        for (int i = start; i < rowCount; i++) {
327
            List<Object> row = new ArrayList<Object>();
328
            String[] values = lines.get(i);
329
            for (int j = 0; j < columnCount; j++) {
330
                row.add(values[j]);
331
            }
332
            rows.add(row);
333
        }
334
        csvReader.close();
335
        return new ArrayTableModel(rows);
336
 
337
    }
338
 
83 ilm 339
    public void exportModelToCSV(File csvFile, List<String[]> lines) throws IOException {
340
 
341
        char separator = ';';
342
 
343
        CSVWriter csvReader = new CSVWriter(new OutputStreamWriter(new FileOutputStream(csvFile), "CP1252"), separator);
344
        csvReader.writeAll(lines);
345
        csvReader.close();
346
    }
347
 
19 ilm 348
    public ArrayTableModel createConvertedModel(ArrayTableModel model) {
349
        final int rowCount = model.getRowCount();
350
        final ArrayList<Integer> colsUsed = new ArrayList<Integer>(map.keySet());
351
        colsUsed.addAll(constraints.keySet());
352
 
353
        final int columnCount = 1 + Collections.max(colsUsed);
354
 
355
        final List<List<Object>> rows = new ArrayList<List<Object>>(rowCount);
356
 
357
        for (int i = 0; i < rowCount; i++) {
358
            boolean validRow = true;
359
            final List<Object> row = new ArrayList<Object>();
360
            for (int j = 0; j < columnCount; j++) {
361
                Object value = model.getValueAt(i, j);
362
                ValueConverter converter = map.get(j);
363
                if (converter != null) {
364
                    value = converter.convertFrom(value);
365
                }
366
                final Constraint constraint = constraints.get(j);
367
                // Verification de la validité de la valeur à importer
368
                if (constraint != null && !constraint.isValid(value)) {
369
                    validRow = false;
370
                    break;
371
                }
372
                row.add(value);
373
            }
374
            if (validRow) {
375
                rows.add(row);
376
            }
377
        }
378
 
379
        return new ArrayTableModel(rows);
380
    }
381
 
83 ilm 382
    protected void customizeRowValuesToFetch(SQLRowValues vals) {
383
 
384
    }
385
 
19 ilm 386
    public void importFromModel(ArrayTableModel model) throws IOException {
387
        final int rowCount = model.getRowCount();
388
        // Load existing data for duplication check
389
        final SQLRowValues vals = new SQLRowValues(table);
390
 
391
        for (SQLField field : this.fieldMap.keySet()) {
392
            if (field.getTable().equals(table)) {
393
                vals.put(field.getName(), null);
394
            } else {
395
                final Set<SQLField> foreignKeys = table.getForeignKeys(field.getTable());
396
                for (SQLField sqlField : foreignKeys) {
397
                    vals.put(sqlField.getName(), null);
398
                }
399
            }
400
        }
83 ilm 401
        customizeRowValuesToFetch(vals);
19 ilm 402
        System.out.println("Fetching values");
83 ilm 403
        SQLRowValuesListFetcher fetcher = SQLRowValuesListFetcher.create(vals);
19 ilm 404
        List<SQLRowValues> existingRows = fetcher.fetch();
405
        System.out.println("Computing cache");
406
        final int existingRowsCount = existingRows.size();
407
        final ValueConverter[] converters = map.values().toArray(new ValueConverter[map.size()]);
408
 
409
        // Une map <Object(valeur),SQLRowValues> pour chaque champs unique
410
        Map<SQLField, Map<Object, SQLRowValues>> cache = new HashMap<SQLField, Map<Object, SQLRowValues>>();
411
        for (SQLField field : this.uniqueField) {
412
            Map<Object, SQLRowValues> m = new HashMap<Object, SQLRowValues>();
413
            cache.put(field, m);
414
            final String fieldName = field.getName();
415
            for (int j = 0; j < existingRowsCount; j++) {
416
                SQLRowValues row = existingRows.get(j);
417
                m.put(row.getObject(fieldName), row);
418
            }
419
        }
420
 
421
        // Parcours des lignes des données à importer
422
        for (int i = 0; i < rowCount; i++) {
423
 
424
            // Recherche d'existant
425
            SQLRowValues existingRow = null;
426
            for (SQLField field : this.uniqueField) {
427
                List<Integer> cols = fieldMap.get(field);
428
                Object objectToInsert = null;
429
                for (Integer col : cols) {
430
                    Object v = model.getValueAt(i, col);
431
                    if (objectToInsert == null) {
432
                        objectToInsert = v;
433
                    } else if (v instanceof String) {
434
                        objectToInsert = objectToInsert.toString() + "\n" + (String) v;
435
                    }
436
 
437
                }
438
 
439
                existingRow = cache.get(field).get(objectToInsert);
440
                if (existingRow != null) {
441
                    break;
442
                }
443
            }
444
 
445
            updateOrInsert(model, converters, i, existingRow);
446
        }
447
 
448
    }
449
 
450
    private void updateOrInsert(ArrayTableModel model, final ValueConverter[] converters, int i, SQLRowValues existingRow) {
451
 
452
        final Map<String, Object> newValues = new HashMap<String, Object>();
453
        if (existingRow != null) {
454
            // Préremplissage de la map avec la row existante
455
            newValues.putAll(existingRow.getAbsolutelyAll());
456
        }
457
        for (int j = 0; j < converters.length; j++) {
458
            ValueConverter valueConverter = converters[j];
459
 
460
            List<Integer> cols = fieldMap.get(valueConverter.getField());
461
            Object objectToInsert = null;
462
            for (Integer col : cols) {
463
                Object v = model.getValueAt(i, col);
464
                if (objectToInsert == null) {
465
                    objectToInsert = v;
466
                } else if (v instanceof String) {
467
                    objectToInsert = objectToInsert.toString() + "\n" + (String) v;
468
                }
469
 
470
            }
471
 
472
            final String fieldName = valueConverter.getFieldName();
73 ilm 473
            if (objectToInsert != null || !valueConverter.isIgnoringEmptyValue()) {
474
                if (valueConverter.getField().getTable().equals(table)) {
475
                    newValues.put(fieldName, objectToInsert);
476
                } else {
19 ilm 477
 
73 ilm 478
                    final SQLField sqlField = foreignMap.get(valueConverter);
19 ilm 479
 
73 ilm 480
                    final Object value = newValues.get(sqlField.getName());
481
                    if (value == null || value instanceof SQLRowValues) {
482
                        SQLRowValues fRowValues = (SQLRowValues) value;
483
                        if (fRowValues == null) {
484
                            fRowValues = new SQLRowValues(valueConverter.getField().getTable());
485
                            newValues.put(sqlField.getName(), fRowValues);
486
                        }
487
                        fRowValues.put(valueConverter.getField().getName(), objectToInsert);
19 ilm 488
                    }
73 ilm 489
 
19 ilm 490
                }
491
            }
492
        }
83 ilm 493
        final SQLRowValues rowVals = new SQLRowValues(table, newValues);
494
        patchRowValues(rowVals, model.getLineValuesAt(i), existingRow);
19 ilm 495
        if (existingRow == null) {
83 ilm 496
            this.valuesToInsert.add(rowVals);
19 ilm 497
        }
83 ilm 498
        // else if (!newValues.equals(existingRow.getAbsolutelyAll())) {
499
        else {
500
            this.valuesToUpdate.add(rowVals);
501
            // for (SQLRowValues ref : rowVals.getReferentRows()) {
502
            // this.valuesToUpdate.add(ref);
503
            // }
504
        }
19 ilm 505
    }
506
 
83 ilm 507
    public void doAfterImport() throws SQLException {
508
 
509
    }
510
 
511
    protected void patchRowValues(SQLRowValues rowVals, List<Object> lineValues, SQLRowValues existingRow) {
512
 
513
    }
514
 
19 ilm 515
    public void setSkipFirstLine(boolean skipFirstLine) {
516
        this.skipFirstLine = skipFirstLine;
517
    }
518
 
519
    public ArrayTableModel createModelFrom(File file) throws IOException {
83 ilm 520
        return createModelFrom(file, 0);
521
    }
522
 
523
    public ArrayTableModel createModelFrom(File file, int sheetNumber) throws IOException {
19 ilm 524
        if (!file.exists()) {
525
            throw new IllegalArgumentException(file.getAbsolutePath() + " does not exist");
526
        }
527
        String name = file.getName().toLowerCase();
528
        if (name.endsWith(".ods")) {
83 ilm 529
            return createModelFromODS(file, sheetNumber);
19 ilm 530
        } else if (name.endsWith(".csv")) {
531
            return createModelFromCSV(file);
532
        } else if (name.endsWith(".xls")) {
83 ilm 533
            return createModelFromXLS(file, sheetNumber);
19 ilm 534
        }
132 ilm 535
        throw new IllegalArgumentException("File format not supported. Please provide an ods, csv or xls file.");
19 ilm 536
 
537
    }
538
}