OpenConcerto

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

svn://code.openconcerto.org/openconcerto

Rev

Rev 156 | Show entire file | Regard whitespace | Details | Blame | Last modification | View Log | RSS feed

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