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

/*

 * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS HEADER.

 *

 * Copyright 2011-2019 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.openoffice.spreadsheet.Sheet;

import org.openconcerto.openoffice.spreadsheet.SpreadSheet;

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.utils.ooxml.XLSXDocument;

import org.openconcerto.utils.ooxml.XLSXSheet;

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.formula.eval.NotImplementedException;

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<>();

    private final Map<SQLField, List<Integer>> fieldMap = new HashMap<>();

    private final Map<Integer, Constraint> constraints = new HashMap<>();

    private List<SQLField> uniqueField = new ArrayList<>();

    private List<SQLRowValues> valuesToUpdate = new ArrayList<>();

    private List<SQLRowValues> valuesToInsert = new ArrayList<>();

    private Map<ValueConverter, SQLField> foreignMap = new HashMap<>();

    public DataImporter() {

    }

    public DataImporter(SQLTable table) {

        this.table = table;

    }

    public void setTable(SQLTable table) {

        this.table = table;

    }

    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 this.valuesToInsert;

    }

    public List<SQLRowValues> getValuesToUpdate() {

        return this.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) {

        this.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);

        this.foreignMap.put(converter, field);

    }

    public void map(int columnIndex, SQLField field, ValueConverter converter) {

        final Integer value = Integer.valueOf(columnIndex);

        this.map.put(value, converter);

        List<Integer> l = this.fieldMap.get(field);

        if (l == null) {

            l = new ArrayList<>();

            this.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 (this.skipFirstLine) {

            start = 1;

        }

        final List<List<Object>> rows = new ArrayList<>(rowCount - start);

        for (int i = start; i < rowCount; i++) {

            List<Object> row = new ArrayList<>();

            for (int j = 0; j < columnCount; j++) {

                row.add(sheet.getValueAt(j, i));

            }

            rows.add(row);

        }

        return new ArrayTableModel(rows);

    }

    public ArrayTableModel createModelFromXLSXGM(File xlsFile, int sheetNumber) throws IOException {

        XLSXDocument doc = XLSXDocument.createFromFile(xlsFile);

        XLSXSheet sheet = doc.getSheet(sheetNumber);

        final int rowCount = sheet.getRowCount();

        final int columnCount = sheet.getColumnCount();

        int start = 0;

        if (this.skipFirstLine) {

            start = 1;

        }

        final List<List<Object>> selectedRows = new ArrayList<>(rowCount - start);

        for (int i = start; i < rowCount; i++) {

            List<Object> row = new ArrayList<>();

            for (int j = 0; j < columnCount; j++) {

                row.add(sheet.getValueAt(j, i));

            }

            selectedRows.add(row);

        }

        return new ArrayTableModel(selectedRows);

    }

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

        final List<List<Object>> rows = createFromExcel(workBook.getCreationHelper().createFormulaEvaluator(), sheet.rowIterator(), sheet.rowIterator());

        workBook.close();

        return new ArrayTableModel(rows);

    }

    private List<List<Object>> createFromExcel(FormulaEvaluator evaluator, Iterator<Row> rowsIterator1, Iterator<Row> rowsIterator2) {

        int columnCount = 0;

        int rowCount = 0;

        while (rowsIterator1.hasNext()) {

            Row row = rowsIterator1.next();

            int i = row.getPhysicalNumberOfCells();

            if (i > columnCount) {

                columnCount = i;

            }

            rowCount++;

        }

        // Extract data

        int start = 0;

        if (this.skipFirstLine) {

            start = 1;

            rowsIterator2.next();

        }

        final List<List<Object>> rows = new ArrayList<>(rowCount - start);

        while (rowsIterator2.hasNext()) {

            final Row row = rowsIterator2.next();

            final List<Object> rowData = new ArrayList<>();

            for (int i = 0; i < columnCount; i++) {

                final Cell cell = row.getCell(i);

                if (cell == null) {

                    rowData.add("");

                } else {

                    CellValue cellValue = null;

                    try {

                        cellValue = evaluator.evaluate(cell);

                    } catch (NotImplementedException exception) {

                        exception.printStackTrace();

                    }

                    if (cellValue == null) {

                        rowData.add("");

                    } else {

                        switch (cellValue.getCellType()) {

                        case BOOLEAN:

                            rowData.add(Boolean.valueOf(cellValue.getBooleanValue()));

                            break;

                        case NUMERIC:

                            if (HSSFDateUtil.isCellDateFormatted(cell)) {

                                rowData.add(cell.getDateCellValue());

                            } else {

                                rowData.add(Double.valueOf(cellValue.getNumberValue()));

                            }

                            break;

                        case STRING:

                            rowData.add(cellValue.getStringValue());

                            break;

                        case FORMULA:

                            rowData.add(cell.getCellFormula());

                            break;

                        case BLANK:

                            rowData.add("");

                            break;

                        default:

                            rowData.add(cellValue.getStringValue());

                            break;

                        }

                    }

                }

            }

            rows.add(rowData);

        }

        return 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 (this.skipFirstLine) {

            start = 1;

        }

        final List<List<Object>> rows = new ArrayList<>(rowCount - start);

        for (int i = start; i < rowCount; i++) {

            List<Object> row = new ArrayList<>();

            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<>(this.map.keySet());

        colsUsed.addAll(this.constraints.keySet());

        final int columnCount = 1 + Collections.max(colsUsed);

        final List<List<Object>> rows = new ArrayList<>(rowCount);

        for (int i = 0; i < rowCount; i++) {

            boolean validRow = true;

            final List<Object> row = new ArrayList<>();

            for (int j = 0; j < columnCount; j++) {

                Object value = model.getValueAt(i, j);

                ValueConverter converter = this.map.get(j);

                if (converter != null) {

                    value = converter.convertFrom(value);

                }

                final Constraint constraint = this.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) {

        // Nothing

    }

    public void importFromModel(ArrayTableModel model) throws IOException {

        final int rowCount = model.getRowCount();

        // Load existing data for duplication check

        final SQLRowValues vals = new SQLRowValues(this.table);

        for (SQLField field : this.fieldMap.keySet()) {

            if (field.getTable().equals(this.table)) {

                vals.put(field.getName(), null);

            } else {

                final Set<SQLField> foreignKeys = this.table.getForeignKeys(field.getTable());

                for (SQLField sqlField : foreignKeys) {

                    vals.put(sqlField.getName(), null);

                }

            }

        }

        customizeRowValuesToFetch(vals);

        SQLRowValuesListFetcher fetcher = SQLRowValuesListFetcher.create(vals);

        List<SQLRowValues> existingRows = fetcher.fetch();

        final int existingRowsCount = existingRows.size();

        final ValueConverter[] converters = this.map.values().toArray(new ValueConverter[this.map.size()]);

        // Une map <Object(valeur),SQLRowValues> pour chaque champs unique

        Map<SQLField, Map<Object, SQLRowValues>> cache = new HashMap<>();

        for (SQLField field : this.uniqueField) {

            Map<Object, SQLRowValues> m = new HashMap<>();

            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 = this.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<>();

        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 = this.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(this.table)) {

                    newValues.put(fieldName, objectToInsert);

                } else {

                    final SQLField sqlField = this.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(this.table, newValues);

        patchRowValues(rowVals, model.getLineValuesAt(i), existingRow);

        if (existingRow == null) {

            this.valuesToInsert.add(rowVals);

        } else {

            this.valuesToUpdate.add(rowVals);

        }

    }

    public void doAfterImport() throws SQLException {

        // Nothing

    }

    protected void patchRowValues(SQLRowValues rowVals, List<Object> lineValues, SQLRowValues existingRow) {

        // Nothing

    }

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

        } else if (name.endsWith(".xlsx") || name.endsWith(".xlsm")) {

            return createModelFromXLSXGM(file, sheetNumber);

        }

        throw new IllegalArgumentException("File format not supported. Please provide an ods, csv, xls or xlsx file.");

    }

}