WebSVN - OpenConcerto - Diff - Rev 144 and 156 - /trunk/OpenConcerto/src/org/openconcerto/erp/importer/DataImporter.java


/*
 * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS HEADER.
 * 
 * Copyright 2011 OpenConcerto, by ILM Informatique. All rights reserved.
 * 
 * The contents of this file are subject to the terms of the GNU General Public License Version 3
 * only ("GPL"). You may not use this file except in compliance with the License. You can obtain a
 * copy of the License at http://www.gnu.org/licenses/gpl-3.0.html See the License for the specific
 * language governing permissions and limitations under the License.
 * 
 * When distributing the software, include this License Header Notice in each file.
 */
 
 package org.openconcerto.erp.importer;
 
import org.openconcerto.erp.config.ComptaPropsConfiguration;
import org.openconcerto.openoffice.spreadsheet.Sheet;
import org.openconcerto.openoffice.spreadsheet.SpreadSheet;
import org.openconcerto.sql.Configuration;
import org.openconcerto.sql.model.SQLBase;
import org.openconcerto.sql.model.SQLField;
import org.openconcerto.sql.model.SQLRowValues;
import org.openconcerto.sql.model.SQLRowValuesCluster.StoreMode;
import org.openconcerto.sql.model.SQLRowValuesListFetcher;
import org.openconcerto.sql.model.SQLTable;
import org.openconcerto.sql.users.UserManager;
import org.openconcerto.utils.text.CSVReader;
import org.openconcerto.utils.text.CSVWriter;
import org.openconcerto.utils.text.CharsetHelper;
 
import java.io.BufferedInputStream;
import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.OutputStreamWriter;
import java.nio.charset.Charset;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;
 
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellValue;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
 
public class DataImporter {
    private boolean skipFirstLine = true;
    private SQLTable table;
    private final Map<Integer, ValueConverter> map = new HashMap<Integer, ValueConverter>();
    private final Map<SQLField, List<Integer>> fieldMap = new HashMap<SQLField, List<Integer>>();
    private final Map<Integer, Constraint> constraints = new HashMap<Integer, Constraint>();
    private List<SQLField> uniqueField = new ArrayList<SQLField>();
    private List<SQLRowValues> valuesToUpdate = new ArrayList<SQLRowValues>();
    private List<SQLRowValues> valuesToInsert = new ArrayList<SQLRowValues>();
    private Map<ValueConverter, SQLField> foreignMap = new HashMap<ValueConverter, SQLField>();
 
    public DataImporter() {
    }
 
    public DataImporter(SQLTable table) {
        this.table = table;
    }
 
    public void setTable(SQLTable table) {
        this.table = table;
    }
 
    public static void main(String[] args) throws Exception {
        System.setProperty(SQLBase.STRUCTURE_USE_XML, "true");
        final ComptaPropsConfiguration conf = ComptaPropsConfiguration.create();
        Configuration.setInstance(conf);
        try {
            conf.getBase();
        } catch (Exception e) {
            e.printStackTrace();
        }
 
        final ComptaPropsConfiguration comptaPropsConfiguration = ((ComptaPropsConfiguration) Configuration.getInstance());
        comptaPropsConfiguration.setUpSocieteDataBaseConnexion(39);
        UserManager.getInstance().setCurrentUser(2);
 
        SQLTable table = Configuration.getInstance().getRoot().findTable("ARTICLE");
        DataImporter importer = new DataImporter(table);
        importer.skipFirstLine = false;
        importer.map(0, table.getField("CODE"));
        importer.map(8, table.getField("ID_FOURNISSEUR"));
        importer.map(4, table.getField("NOM"));
        importer.addContraint(0, new NotEmptyConstraint());
        importer.addUniqueField(table.getField("CODE"));
        // ArrayTableModel m = importer.createModelFromODS(new File("c:/products-en.ods"));
        // ArrayTableModel m = importer.createModelFromCSV(new File("c:/products-en.csv"));
        // ArrayTableModel m = importer.createModelFromCSV(new File("c:/products-en.scsv.csv"));
        ArrayTableModel m = importer.createModelFromXLS(new File("c:/products-en.xls"), 0);
        m.dump(0, 4);
        m = importer.createConvertedModel(m);
        System.out.println("Dump");
        m.dump(0, 4);
        importer.importFromModel(m);
        System.out.println(importer.getValuesToInsert().size() + " rows to insert");
        System.out.println(importer.getValuesToUpdate().size() + " rows to update");
        // importer.commit();
 
    }
 
    public void commit() throws SQLException {
        for (SQLRowValues row : this.valuesToInsert) {
            row.getGraph().store(StoreMode.INSERT, false);
        }
        for (SQLRowValues row : this.valuesToUpdate) {
            row.getGraph().store(StoreMode.COMMIT, false);
        }
        doAfterImport();
    }
 
    public List<SQLRowValues> getValuesToInsert() {
        return valuesToInsert;
    }
 
    public List<SQLRowValues> getValuesToUpdate() {
        return valuesToUpdate;
    }
 
    public void addUniqueField(SQLField field) {
        if (this.uniqueField.contains(field)) {
            throw new IllegalStateException("Field " + field + " already specified");
        }
        this.uniqueField.add(field);
 
    }
 
    public void addContraint(int columnIndex, Constraint c) {
        constraints.put(Integer.valueOf(columnIndex), c);
    }
 
    public void map(int columnIndex, SQLField field) {
        map(columnIndex, field, new ValueConverter(field));
    }
 
    public void map(int columnIndex, ValueConverter converter) {
        map(columnIndex, converter.getField(), converter);
    }
 
    public void map(int columnIndex, SQLField field, SQLField foreignField) {
        final ValueConverter converter = new ValueConverter(foreignField);
        map(columnIndex, foreignField, converter);
        foreignMap.put(converter, field);
 
    }
 
    public void map(int columnIndex, SQLField field, ValueConverter converter) {
        final Integer value = Integer.valueOf(columnIndex);
        map.put(value, converter);
        List<Integer> l = fieldMap.get(field);
        if (l == null) {
            l = new ArrayList<Integer>();
            fieldMap.put(field, l);
        } else if (!field.getType().getJavaType().equals(String.class)) {
            throw new IllegalArgumentException("Mapping multiple column is only supoprted for String values");
        }
        if (l.contains(value)) {
            throw new IllegalArgumentException("Column " + columnIndex + " already mapped for field " + field.getFullName());
        }
        l.add(value);
    }
 
    public ArrayTableModel createModelFromODS(File odsFile, int sheetNumber) throws IOException {
        final SpreadSheet spreadSheet = SpreadSheet.createFromFile(odsFile);
        if (spreadSheet.getSheetCount() < 1) {
            return null;
        }
        final Sheet sheet = spreadSheet.getSheet(sheetNumber);
        final int rowCount = sheet.getRowCount();
        int columnCount = 0;
        if (rowCount > 0) {
            final int maxColumnCount = sheet.getColumnCount();
            for (int j = 0; j < maxColumnCount; j++) {
                final Object valueAt = sheet.getValueAt(j, 0);
                if (valueAt == null || valueAt.toString().trim().isEmpty()) {
                    break;
                }
                columnCount++;
            }
        }
        int start = 0;
        if (skipFirstLine) {
            start = 1;
        }
        final List<List<Object>> rows = new ArrayList<List<Object>>(rowCount - start);
        for (int i = start; i < rowCount; i++) {
            List<Object> row = new ArrayList<Object>();
            for (int j = 0; j < columnCount; j++) {
                row.add(sheet.getValueAt(j, i));
            }
            rows.add(row);
        }
 
        return new ArrayTableModel(rows);
    }
 
    public ArrayTableModel createModelFromXLS(File xlsFile, int sheetNumber) throws IOException {
        final InputStream inputStream = new FileInputStream(xlsFile);
        final POIFSFileSystem fileSystem = new POIFSFileSystem(new BufferedInputStream(inputStream));
        final HSSFWorkbook workBook = new HSSFWorkbook(fileSystem);
        final HSSFSheet sheet = workBook.getSheetAt(sheetNumber);
        Iterator<Row> rowsIterator = sheet.rowIterator();
        int columnCount = 0;
        int rowCount = 0;
        while (rowsIterator.hasNext()) {
            Row row = rowsIterator.next();
            int i = row.getPhysicalNumberOfCells();
            if (i > columnCount) {
                columnCount = i;
            }
            rowCount++;
        }
        // Extract data
        rowsIterator = sheet.rowIterator();
        int start = 0;
        if (skipFirstLine) {
            start = 1;
            rowsIterator.next();
        }
        final List<List<Object>> rows = new ArrayList<List<Object>>(rowCount - start);
        FormulaEvaluator evaluator = workBook.getCreationHelper().createFormulaEvaluator();
 
        while (rowsIterator.hasNext()) {
            final Row row = rowsIterator.next();
            final List<Object> rowData = new ArrayList<Object>();
            for (int i = 0; i < columnCount; i++) {
                final Cell cell = row.getCell(i);
 
                if (cell == null) {
                    rowData.add("");
                } else {
                    CellValue cellValue = evaluator.evaluate(cell);
                    if (cellValue == null) {
                        rowData.add("");
                    } else {
                        switch (cellValue.getCellType()) {
                        case Cell.CELL_TYPE_BOOLEAN:
                            rowData.add(Boolean.valueOf(cellValue.getBooleanValue()));
                            break;
                        case Cell.CELL_TYPE_NUMERIC:
 
                            if (HSSFDateUtil.isCellDateFormatted(cell)) {
                                System.out.println("Row No.: " + row.getRowNum() + " " + cell.getDateCellValue());
                                rowData.add(cell.getDateCellValue());
                            } else {
                                rowData.add(Double.valueOf(cellValue.getNumberValue()));
                            }
                            break;
                        case Cell.CELL_TYPE_STRING:
                            rowData.add(cellValue.getStringValue());
                            break;
                        case Cell.CELL_TYPE_FORMULA:
                            rowData.add(cell.getCellFormula());
                            break;
                        case Cell.CELL_TYPE_BLANK:
                            rowData.add("");
                            break;
                        default:
                            rowData.add(cellValue.getStringValue());
                            break;
 
                        }
                    }
                }
            }
 
            rows.add(rowData);
 
        }
        inputStream.close();
        return new ArrayTableModel(rows);
 
    }
 
    public ArrayTableModel createModelFromCSV(File csvFile) throws IOException {
        Charset cs = CharsetHelper.guessEncoding(csvFile, 4096, Charset.forName("Cp1252"));
 
        BufferedReader r = new BufferedReader(new InputStreamReader(new FileInputStream(csvFile), cs));
        String l = r.readLine();
        if (l == null) {
            r.close();
            return null;
        }
        char separator = ',';
        int cCount = 0;
        int scCount = 0;
        for (int i = 0; i < l.length(); i++) {
            char c = l.charAt(i);
            if (c == ',') {
                cCount++;
            } else if (c == ';') {
                scCount++;
            }
        }
        r.close();
        if (scCount > cCount) {
            separator = ';';
        }
 
        CSVReader csvReader = new CSVReader(new InputStreamReader(new FileInputStream(csvFile), cs), separator);
        List<String[]> lines = csvReader.readAll();
        final int rowCount = lines.size();
        final int columnCount = lines.get(0).length;
 
        int start = 0;
        if (skipFirstLine) {
            start = 1;
        }
        final List<List<Object>> rows = new ArrayList<List<Object>>(rowCount - start);
        for (int i = start; i < rowCount; i++) {
            List<Object> row = new ArrayList<Object>();
            String[] values = lines.get(i);
            for (int j = 0; j < columnCount; j++) {
                row.add(values[j]);
            }
            rows.add(row);
        }
        csvReader.close();
        return new ArrayTableModel(rows);
 
    }
 
    public void exportModelToCSV(File csvFile, List<String[]> lines) throws IOException {
 
        char separator = ';';
 
        CSVWriter csvReader = new CSVWriter(new OutputStreamWriter(new FileOutputStream(csvFile), "CP1252"), separator);
        csvReader.writeAll(lines);
        csvReader.close();
    }
 
    public ArrayTableModel createConvertedModel(ArrayTableModel model) {
        final int rowCount = model.getRowCount();
        final ArrayList<Integer> colsUsed = new ArrayList<Integer>(map.keySet());
        colsUsed.addAll(constraints.keySet());
 
        final int columnCount = 1 + Collections.max(colsUsed);
 
        final List<List<Object>> rows = new ArrayList<List<Object>>(rowCount);
 
        for (int i = 0; i < rowCount; i++) {
            boolean validRow = true;
            final List<Object> row = new ArrayList<Object>();
            for (int j = 0; j < columnCount; j++) {
                Object value = model.getValueAt(i, j);
                ValueConverter converter = map.get(j);
                if (converter != null) {
                    value = converter.convertFrom(value);
                }
                final Constraint constraint = constraints.get(j);
                // Verification de la validité de la valeur à importer
                if (constraint != null && !constraint.isValid(value)) {
                    validRow = false;
                    break;
                }
                row.add(value);
            }
            if (validRow) {
                rows.add(row);
            }
        }
 
        return new ArrayTableModel(rows);
    }
 
    protected void customizeRowValuesToFetch(SQLRowValues vals) {
 
    }
 
    public void importFromModel(ArrayTableModel model) throws IOException {
        final int rowCount = model.getRowCount();
        // Load existing data for duplication check
        final SQLRowValues vals = new SQLRowValues(table);
 
        for (SQLField field : this.fieldMap.keySet()) {
            if (field.getTable().equals(table)) {
                vals.put(field.getName(), null);
            } else {
                final Set<SQLField> foreignKeys = table.getForeignKeys(field.getTable());
                for (SQLField sqlField : foreignKeys) {
                    vals.put(sqlField.getName(), null);
                }
            }
        }
        customizeRowValuesToFetch(vals);
        System.out.println("Fetching values");
        SQLRowValuesListFetcher fetcher = SQLRowValuesListFetcher.create(vals);
        List<SQLRowValues> existingRows = fetcher.fetch();
        System.out.println("Computing cache");
        final int existingRowsCount = existingRows.size();
        final ValueConverter[] converters = map.values().toArray(new ValueConverter[map.size()]);
 
        // Une map <Object(valeur),SQLRowValues> pour chaque champs unique
        Map<SQLField, Map<Object, SQLRowValues>> cache = new HashMap<SQLField, Map<Object, SQLRowValues>>();
        for (SQLField field : this.uniqueField) {
            Map<Object, SQLRowValues> m = new HashMap<Object, SQLRowValues>();
            cache.put(field, m);
            final String fieldName = field.getName();
            for (int j = 0; j < existingRowsCount; j++) {
                SQLRowValues row = existingRows.get(j);
                m.put(row.getObject(fieldName), row);
            }
        }
 
        // Parcours des lignes des données à importer
        for (int i = 0; i < rowCount; i++) {
 
            // Recherche d'existant
            SQLRowValues existingRow = null;
            for (SQLField field : this.uniqueField) {
                List<Integer> cols = fieldMap.get(field);
                Object objectToInsert = null;
                for (Integer col : cols) {
                    Object v = model.getValueAt(i, col);
                    if (objectToInsert == null) {
                        objectToInsert = v;
                    } else if (v instanceof String) {
                        objectToInsert = objectToInsert.toString() + "\n" + (String) v;
                    }
 
                }
 
                existingRow = cache.get(field).get(objectToInsert);
                if (existingRow != null) {
                    break;
                }
            }
 
            updateOrInsert(model, converters, i, existingRow);
        }
 
    }
 
    private void updateOrInsert(ArrayTableModel model, final ValueConverter[] converters, int i, SQLRowValues existingRow) {
 
        final Map<String, Object> newValues = new HashMap<String, Object>();
        if (existingRow != null) {
            // Préremplissage de la map avec la row existante
            newValues.putAll(existingRow.getAbsolutelyAll());
        }
        for (int j = 0; j < converters.length; j++) {
            ValueConverter valueConverter = converters[j];
 
            List<Integer> cols = fieldMap.get(valueConverter.getField());
            Object objectToInsert = null;
            for (Integer col : cols) {
                Object v = model.getValueAt(i, col);
                if (objectToInsert == null) {
                    objectToInsert = v;
                } else if (v instanceof String) {
                    objectToInsert = objectToInsert.toString() + "\n" + (String) v;
                }
 
            }
 
            final String fieldName = valueConverter.getFieldName();
            if (objectToInsert != null || !valueConverter.isIgnoringEmptyValue()) {
                if (valueConverter.getField().getTable().equals(table)) {
                    newValues.put(fieldName, objectToInsert);
                } else {
 
                    final SQLField sqlField = foreignMap.get(valueConverter);
 
                    final Object value = newValues.get(sqlField.getName());
                    if (value == null || value instanceof SQLRowValues) {
                        SQLRowValues fRowValues = (SQLRowValues) value;
                        if (fRowValues == null) {
                            fRowValues = new SQLRowValues(valueConverter.getField().getTable());
                            newValues.put(sqlField.getName(), fRowValues);
                        }
                        fRowValues.put(valueConverter.getField().getName(), objectToInsert);
                    }
 
                }
            }
        }
        final SQLRowValues rowVals = new SQLRowValues(table, newValues);
        patchRowValues(rowVals, model.getLineValuesAt(i), existingRow);
        if (existingRow == null) {
            this.valuesToInsert.add(rowVals);
        }
        // else if (!newValues.equals(existingRow.getAbsolutelyAll())) {
        else {
            this.valuesToUpdate.add(rowVals);
            // for (SQLRowValues ref : rowVals.getReferentRows()) {
            // this.valuesToUpdate.add(ref);
            // }
        }
    }
 
    public void doAfterImport() throws SQLException {
 
    }
 
    protected void patchRowValues(SQLRowValues rowVals, List<Object> lineValues, SQLRowValues existingRow) {
 
    }
 
    public void setSkipFirstLine(boolean skipFirstLine) {
        this.skipFirstLine = skipFirstLine;
    }
 
    public ArrayTableModel createModelFrom(File file) throws IOException {
        return createModelFrom(file, 0);
    }
 
    public ArrayTableModel createModelFrom(File file, int sheetNumber) throws IOException {
        if (!file.exists()) {
            throw new IllegalArgumentException(file.getAbsolutePath() + " does not exist");
        }
        String name = file.getName().toLowerCase();
        if (name.endsWith(".ods")) {
            return createModelFromODS(file, sheetNumber);
        } else if (name.endsWith(".csv")) {
            return createModelFromCSV(file);
        } else if (name.endsWith(".xls")) {
            return createModelFromXLS(file, sheetNumber);
        }
        throw new IllegalArgumentException("File format not supported. Please provide an ods, csv or xls file.");
 
    }
}

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

svn://code.openconcerto.org/openconcerto

(root)/trunk/OpenConcerto/src/org/openconcerto/erp/importer/DataImporter.java - Rev 144 → 156

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;`