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