OpenConcerto

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

svn://code.openconcerto.org/openconcerto

Rev

Rev 144 | Go to most recent revision | Only display areas with differences | Regard whitespace | Details | Blame | Last modification | View Log | RSS feed

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