OpenConcerto

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

svn://code.openconcerto.org/openconcerto

Rev

Go to most recent revision | Blame | Compare with Previous | Last modification | View Log | RSS feed

/*
 * 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.core.supplychain.order.action;

import org.openconcerto.erp.core.sales.product.element.UniteVenteArticleSQLElement;
import org.openconcerto.erp.core.supplychain.order.element.EtatDemandeAchatItemSQLElement;
import org.openconcerto.erp.importer.ArrayTableModel;
import org.openconcerto.erp.importer.DataImporter;
import org.openconcerto.sql.model.DBRoot;
import org.openconcerto.sql.model.SQLBase;
import org.openconcerto.sql.model.SQLRow;
import org.openconcerto.sql.model.SQLRowAccessor;
import org.openconcerto.sql.model.SQLRowValues;
import org.openconcerto.sql.model.SQLRowValuesListFetcher;
import org.openconcerto.sql.model.SQLTable;
import org.openconcerto.sql.utils.SQLUtils;
import org.openconcerto.utils.ExceptionHandler;
import org.openconcerto.utils.StringUtils;
import org.openconcerto.utils.Tuple3;

import java.io.File;
import java.io.IOException;
import java.math.BigDecimal;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbutils.ResultSetHandler;

public class ImportProductsToOrder {

    private Map<Object, Integer> fabMap = new HashMap<Object, Integer>();
    private DBRoot root;
    private Map<String, SQLRowAccessor> artMap = new HashMap<String, SQLRowAccessor>();

    public static void main(String[] args) throws IOException, SQLException {
        ImportProductsToOrder importer = new ImportProductsToOrder();
        importer.importFile(new File("export-inventor-17-819.xls"), null);

    }

    private SQLRowAccessor rowAffaire = null;

    public void setRowAffaire(SQLRowAccessor rowAffaire) {
        this.rowAffaire = rowAffaire;
    }

    public void importFile(File file, DBRoot root) throws IOException, SQLException {
        this.root = root;
        DataImporter importer = new DataImporter();
        importer.setSkipFirstLine(true);
        ArrayTableModel m = importer.createModelFromXLS(file, 0);
        m.dump(0, 40);

        // Chargement des fab
        try {

            resolveFab(root);
        } catch (SQLException originalExn) {
            ExceptionHandler.handle("Impossible de créer les fournisseurs", originalExn);
            return;
        }

        try {

            resolveArticle(root);
        } catch (SQLException originalExn) {
            ExceptionHandler.handle("Impossible de créer les articles", originalExn);
            return;
        }

        int size = m.getRowCount();
        List<List<Object>> cleanData = new ArrayList<List<Object>>();
        fetch(cleanData, m, 0, BigDecimal.ONE, null);

        ArrayTableModel cleanModel = new ArrayTableModel(cleanData);
        // cleanModel.dump(0, 392);
        SQLTable table = root.getTable("DEMANDE_ACHAT_ELEMENT");
        importer.setTable(table);
        importer.map(0, table.getField("REPERE"));
        importer.map(1, table.getField("QTE"));
        importer.map(2, table.getField("QTE_UNITAIRE"));
        importer.map(3, table.getField("ID_UNITE_VENTE"));
        importer.map(4, table.getField("NOM"));
        importer.map(5, table.getField("CODE"));
        importer.map(6, table.getField("ID_FABRICANT"));
        importer.map(7, table.getField("ID_ETAT_DEMANDE_ACHAT_ELEMENT"));
        importer.map(8, table.getField("ID_ARTICLE"));
        importer.map(9, table.getField("REVISION"));
        importer.map(10, table.getField("REPERE_SOURCE"));
        importer.map(11, table.getField("CODE_SOURCE"));
        importer.map(12, table.getField("NOM_SOURCE"));
        if (table.contains("ID_AFFAIRE")) {
            importer.map(13, table.getField("ID_AFFAIRE"));
        }
        importer.importFromModel(cleanModel);
        // importer.commit();

        List<SQLRowValues> rowsToInsert = importer.getValuesToInsert();

        final List<String> queries = new ArrayList<String>(rowsToInsert.size());
        final List<ResultSetHandler> handlers = new ArrayList<ResultSetHandler>(rowsToInsert.size());
        final ResultSetHandler handler = new ResultSetHandler() {

            @Override
            public Object handle(ResultSet rs) throws SQLException {
                return null;
            }
        };
        SimpleDateFormat formater = new SimpleDateFormat("yyyy-MM-dd");
        String date = formater.format(new Date());
        for (SQLRowValues rowValsToInsert : rowsToInsert) {

            String query = "INSERT INTO " + rowValsToInsert.getTable().getSQLName();
            query += " (\"REPERE\", \"QTE\", \"QTE_UNITAIRE\", \"ID_UNITE_VENTE\", \"NOM\", \"CODE\", \"ID_FABRICANT\", \"ID_ETAT_DEMANDE_ACHAT_ELEMENT\", \"ID_ARTICLE\",\"REVISION\",\"REPERE_SOURCE\",\"CODE_SOURCE\",\"NOM_SOURCE\", ";
            if (table.contains("ID_AFFAIRE")) {
                query += "\"ID_AFFAIRE\",";
            }
            query += "\"DATE\",\"ORDRE\") VALUES (";
            query += SQLBase.quoteStringStd(rowValsToInsert.getString("REPERE")) + ", ";
            query += rowValsToInsert.getInt("QTE") + ", ";
            query += rowValsToInsert.getBigDecimal("QTE_UNITAIRE") + ", ";
            query += rowValsToInsert.getForeignID("ID_UNITE_VENTE") + ", ";
            query += SQLBase.quoteStringStd(rowValsToInsert.getString("NOM")) + ", ";
            query += SQLBase.quoteStringStd(rowValsToInsert.getString("CODE")) + ", ";
            query += rowValsToInsert.getForeignID("ID_FABRICANT") + ", ";
            query += rowValsToInsert.getForeignID("ID_ETAT_DEMANDE_ACHAT_ELEMENT") + ", ";
            int foreignID = rowValsToInsert.getForeignID("ID_ARTICLE");
            if (foreignID < SQLRow.UNDEFINED_ID) {
                foreignID = 1;
            }
            query += foreignID + ", ";
            query += SQLBase.quoteStringStd(rowValsToInsert.getString("REVISION")) + ", ";
            query += SQLBase.quoteStringStd(rowValsToInsert.getString("REPERE_SOURCE")) + ", ";
            query += SQLBase.quoteStringStd(rowValsToInsert.getString("CODE_SOURCE")) + ", ";
            query += SQLBase.quoteStringStd(rowValsToInsert.getString("NOM_SOURCE")) + ", ";

            if (table.contains("ID_AFFAIRE")) {
                query += rowValsToInsert.getForeignID("ID_AFFAIRE") + ", ";
            }
            query += "'" + date + "', ";
            query += "(SELECT (MAX(\"ORDRE\")+1) FROM " + rowValsToInsert.getTable().getSQLName() + "));";

            System.err.println(query);
            queries.add(query);
            handlers.add(handler);

        }

        SQLUtils.executeMultiple(table.getDBSystemRoot(), queries, handlers);

    }

    private int fetch(List<List<Object>> result, ArrayTableModel m, int index, BigDecimal qtyMult, Tuple3<String, String, String> parent) throws SQLException {

        final int size = m.getRowCount();

        // Fin du parcourt
        if (index >= size) {
            return size;
        }

        while (index < size) {
            List<Object> line = m.getLineValuesAt(index);
            // repere
            String repere = line.get(0).toString();
            if (repere.trim().length() == 0) {
                return size;
            }
            // qty
            Object c1 = line.get(1);
            int qty = 1;
            BigDecimal uQty = BigDecimal.ONE;
            int idUnit = UniteVenteArticleSQLElement.A_LA_PIECE;
            if (c1 instanceof Double) {
                Double d = (Double) c1;
                qty = d.intValue();
            } else if (c1 instanceof String) {
                String all = c1.toString().trim();
                List<String> splited = StringUtils.fastSplit(all, ' ');
                String uQtystr = splited.get(0).trim().replace(',', '.');
                uQty = new BigDecimal(uQtystr);
                if (splited.get(1).trim().equalsIgnoreCase("mm")) {
                    idUnit = 11;// mm
                } else {
                    throw new IllegalStateException("cannot parse " + all);
                }
            }

            // Test Nomenclature
            String repereNext = null;
            if (index < (size - 1)) {
                repereNext = m.getLineValuesAt(index + 1).get(0).toString();
            }
            // designation
            // ref
            Object des = line.get(2);
            Object ref = line.get(3);
            if (parent == null || parent.get0() == null || repere.startsWith(parent.get0())) {
                if (repereNext != null && repereNext.startsWith(repere)) {
                    BigDecimal qtyMultChild = qtyMult.multiply(uQty).multiply(new BigDecimal(qty));
                    index = fetch(result, m, index + 1, qtyMultChild, Tuple3.create(repere, ref.toString(), des.toString()));
                } else {

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

                    SQLRowAccessor art = artMap.get(ref.toString());

                    cleanLine.add(repere);

                    if (art != null) {
                        idUnit = art.getForeignID("ID_UNITE_VENTE");
                    }

                    cleanLine.add(qty * qtyMult.intValue());
                    cleanLine.add(uQty);
                    cleanLine.add(idUnit);

                    if (des.toString().isEmpty()) {
                        cleanLine.add(ref.toString());
                    } else {
                        cleanLine.add(des.toString());
                    }
                    cleanLine.add(ref.toString());
                    // fabricant
                    Object c4 = line.get(4);
                    if (c4 != null && c4.toString().trim().length() > 0) {
                        Integer key = fabMap.get(c4.toString());
                        if (key == null) {
                            SQLRowValues rowVAlsFab = new SQLRowValues(root.getTable("FABRICANT"));
                            rowVAlsFab.put("NOM", c4.toString());
                            final SQLRow insertedRow = rowVAlsFab.insert();
                            fabMap.put(c4.toString(), insertedRow.getID());
                            key = insertedRow.getID();
                        }
                        cleanLine.add(key);
                    } else {
                        cleanLine.add(1);
                    }

                    // ETAT
                    cleanLine.add(1);

                    // ARTICLE
                    if (art != null) {
                        cleanLine.add(art.getID());
                        // cleanLine.add(art.getBigDecimal("PA_HT"));
                    } else {
                        cleanLine.add(null);
                        // cleanLine.add(BigDecimal.ZERO);
                    }
                    // INDICE
                    Object c5 = line.get(5);
                    cleanLine.add(c5.toString());

                    // Source
                    String repereSource = "";
                    String refSource = "";
                    String desSource = "";
                    if (parent != null) {
                        repereSource = parent.get0();
                        refSource = parent.get1();
                        desSource = parent.get2();
                    }
                    cleanLine.add(repereSource.toString());
                    cleanLine.add(refSource.toString());
                    cleanLine.add(desSource.toString());

                    if (this.rowAffaire != null) {
                        cleanLine.add(this.rowAffaire.getID());
                    }

                    result.add(cleanLine);
                    index++;
                }
            } else {
                return index;
            }
        }
        return size;

    }

    private void resolveFab(DBRoot root) throws SQLException {
        final SQLTable table = root.findTable("FABRICANT");

        final SQLRowValues vals = new SQLRowValues(table);
        vals.put("NOM", null);
        final SQLRowValuesListFetcher fetcher = new SQLRowValuesListFetcher(vals);
        final List<SQLRowValues> existingRows = fetcher.fetch();

        for (SQLRowValues sqlRowValues : existingRows) {
            fabMap.put(sqlRowValues.getString("NOM"), sqlRowValues.getID());

        }
    }

    private void resolveArticle(DBRoot root) throws SQLException {
        final SQLTable table = root.findTable("ARTICLE");

        final SQLRowValues vals = new SQLRowValues(table);
        vals.put("CODE", null);
        vals.put("NOM", null);
        vals.put("PA_HT", null);
        vals.put("ID_UNITE_VENTE", null);
        final SQLRowValuesListFetcher fetcher = new SQLRowValuesListFetcher(vals);
        final List<SQLRowValues> existingRows = fetcher.fetch();

        for (SQLRowValues sqlRowValues : existingRows) {
            artMap.put(sqlRowValues.getString("CODE"), sqlRowValues);
        }
    }

}