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 = findMatchingRow(model, converters, i, cache);

            updateOrInsert(model, converters, i, existingRow);
        }

    }

    protected SQLRowValues findMatchingRow(ArrayTableModel model, final ValueConverter[] converters, int i, Map<SQLField, Map<Object, SQLRowValues>> cache) {
        SQLRowValues existingRow = null;

        if (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;
                }
            }
        }
        return 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.");

    }
}