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.core.supplychain.stock.element;

import org.openconcerto.erp.core.sales.product.model.PriceByQty;

import org.openconcerto.erp.importer.ArrayTableModel;

import org.openconcerto.erp.importer.DataImporter;

import org.openconcerto.erp.preferences.GestionArticleGlobalPreferencePanel;

import org.openconcerto.sql.Configuration;

import org.openconcerto.sql.element.SQLElement;

import org.openconcerto.sql.model.DBRoot;

import org.openconcerto.sql.model.SQLRow;

import org.openconcerto.sql.model.SQLRowAccessor;

import org.openconcerto.sql.model.SQLRowListRSH;

import org.openconcerto.sql.model.SQLRowValues;

import org.openconcerto.sql.model.SQLRowValuesListFetcher;

import org.openconcerto.sql.model.SQLSelect;

import org.openconcerto.sql.model.SQLTable;

import org.openconcerto.sql.preferences.SQLPreferences;

import org.openconcerto.utils.Tuple3;

import java.io.File;

import java.io.IOException;

import java.math.BigDecimal;

import java.sql.SQLException;

import java.util.ArrayList;

import java.util.Arrays;

import java.util.Calendar;

import java.util.Collection;

import java.util.Date;

import java.util.HashMap;

import java.util.List;

import java.util.Map;

import java.util.Map.Entry;

import java.util.Set;

import javax.swing.JOptionPane;

public class EtatStockFromInventoryFileCreator {

    // Map<String, SQLRowValues> kits = new HashMap<String, SQLRowValues>();

    // List<String> codeKits = new ArrayList<String>();

    // List<SQLRowValues> rowValsArtNonSync = new ArrayList<SQLRowValues>();

    private static String FAMILLE = "Famille";

    private static String CODE = "Code";

    private static String NOM = "Nom";

    private static String TAILLE = "Taille";

    private static String COULEUR = "Couleur";

    private static String QTE = "Qté réelle relevée";

    private final Map<String, Integer> mapCouleur = new HashMap<String, Integer>();

    private final Map<String, Integer> mapTaille = new HashMap<String, Integer>();

    private final DBRoot root;

    private final SQLElement artElt;

    public EtatStockFromInventoryFileCreator(SQLElement artElt) {

        this.artElt = artElt;

        this.root = artElt.getTable().getDBRoot();

    }

    public void importArticles(File file, Date d) throws IOException, SQLException {

        final SQLTable table = this.root.findTable("ARTICLE");

        {

            SQLSelect sel = new SQLSelect();

            sel.addSelect(table.getDBRoot().getTable("ARTICLE_DECLINAISON_COULEUR").getKey());

            sel.addSelect(table.getDBRoot().getTable("ARTICLE_DECLINAISON_COULEUR").getField("NOM"));

            List<SQLRow> l = SQLRowListRSH.execute(sel);

            for (SQLRow sqlRow : l) {

                mapCouleur.put(sqlRow.getString("NOM").trim(), sqlRow.getID());

            }

        }

        {

            SQLSelect sel = new SQLSelect();

            sel.addSelect(table.getDBRoot().getTable("ARTICLE_DECLINAISON_TAILLE").getKey());

            sel.addSelect(table.getDBRoot().getTable("ARTICLE_DECLINAISON_TAILLE").getField("NOM"));

            List<SQLRow> l = SQLRowListRSH.execute(sel);

            for (SQLRow sqlRow : l) {

                mapTaille.put(sqlRow.getString("NOM").trim(), sqlRow.getID());

            }

        }

        fillArticles();

        Map<String, Integer> columnMapping = new HashMap<String, Integer>();

        columnMapping.put(CODE, null);

        columnMapping.put(NOM, null);

        columnMapping.put(TAILLE, null);

        columnMapping.put(FAMILLE, null);

        columnMapping.put(COULEUR, null);

        columnMapping.put(QTE, null);

        {

            // Searching column index from column Header

            final DataImporter importer = new DataImporter(table);

            importer.setSkipFirstLine(false);

            ArrayTableModel m = importer.createModelFrom(file);

            List<Object> line = m.getLineValuesAt(0);

            for (int i = 0; i < line.size(); i++) {

                Object object = line.get(i);

                if (object != null) {

                    for (String key : columnMapping.keySet()) {

                        if (object.toString().equalsIgnoreCase(key)) {

                            columnMapping.put(key, i);

                        }

                    }

                }

            }

        }

        String msg = "Colonnes importées : \n";

        final SQLPreferences prefs = SQLPreferences.getMemCached(table.getDBRoot());

        final boolean hasDeclinaison = prefs.getBoolean(GestionArticleGlobalPreferencePanel.ACTIVER_DECLINAISON, false);

        List<String> required;

        if (hasDeclinaison) {

            required = Arrays.asList(CODE, QTE, TAILLE, COULEUR);

        } else {

            required = Arrays.asList(CODE, QTE);

        }

        for (Entry<String, Integer> e : columnMapping.entrySet()) {

            if (e.getValue() != null) {

                msg += e.getKey() + " : " + getColumnName(e.getValue()) + "\n";

            } else {

                if (required.contains(e.getKey())) {

                    JOptionPane.showMessageDialog(null, "Aucune colonne " + e.getKey() + " trouvée, import annulé!\nCette colonne est obligatoire.");

                    return;

                }

                msg += e.getKey() + " : non importée\n";

            }

        }

        msg += "\nVoulez vous continuer ?";

        int a = JOptionPane.showConfirmDialog(null, msg);

        if (a == JOptionPane.YES_OPTION) {

            final DataImporter importer = new DataImporter(table) {

                @Override

                protected void customizeRowValuesToFetch(SQLRowValues vals) {

                    vals.putRowValues("ID_STOCK").putNulls("ID", "QTE_REEL", "QTE_TH");

                }

            };

            importer.setSkipFirstLine(true);

            ArrayTableModel m = importer.createModelFrom(file);

            SQLRowValues rowValsEtatStock = new SQLRowValues(table.getTable("ETAT_STOCK"));

            rowValsEtatStock.put("DATE", d);

            SQLRow etatStock = rowValsEtatStock.commit();

            BigDecimal total = BigDecimal.ZERO;

            for (int i = 0; i < m.getRowCount(); i++) {

                List<Object> o = m.getLineValuesAt(i);

                String code = o.get(columnMapping.get(CODE)).toString();

                if (code.trim().length() == 0) {

                    break;

                }

                final String stringQty = o.get(columnMapping.get(QTE)).toString();

                Integer qty = stringQty.trim().length() == 0 ? 0 : Integer.valueOf(stringQty);

                String couleur = "";

                if (columnMapping.get(COULEUR) != null) {

                    couleur = o.get(columnMapping.get(COULEUR)).toString();

                }

                String taille = "";

                if (columnMapping.get(TAILLE) != null) {

                    taille = o.get(columnMapping.get(TAILLE)).toString();

                }

                SQLRowAccessor match = findArticle(code, couleur, taille);

                if (match != null) {

                    SQLRowValues stockValues = new SQLRowValues(table.getTable("ETAT_STOCK_ELEMENT"));

                    final BigDecimal qtyB = new BigDecimal(qty);

                    stockValues.put("QTE", qtyB);

                    stockValues.put("NOM", match.getString("NOM"));

                    stockValues.put("CODE", match.getString("CODE"));

                    stockValues.put("ID_ARTICLE", match.getID());

                    final BigDecimal prc = getPRC(match, qty, d);

                    stockValues.put("PA", prc);

                    final BigDecimal totalElt = prc.multiply(qtyB);

                    stockValues.put("T_PA", totalElt);

                    stockValues.put("ID_ETAT_STOCK", etatStock.getID());

                    stockValues.commit();

                    total = total.add(totalElt);

                } else {

                    System.err.println("Aucun article correspondant au code " + code);

                }

            }

            etatStock.createEmptyUpdateRow().put("MONTANT_HA", total).commit();

        }

    }

    public BigDecimal getPRC(SQLRowAccessor rowVals, int qty, Date d) {

        if (rowVals.getTable().getDBRoot().contains("ARTICLE_PRIX_REVIENT")) {

            SQLTable table = rowVals.getTable().getDBRoot().getTable("ARTICLE_PRIX_REVIENT");

            Collection<SQLRow> prcs = rowVals.asRow().getReferentRows(table);

            BigDecimal result = null;

            final List<PriceByQty> prices = new ArrayList<PriceByQty>();

            for (SQLRow row : prcs) {

                Calendar date = Calendar.getInstance();

                date.set(Calendar.DAY_OF_MONTH, 1);

                date.set(Calendar.MONTH, 1);

                date.set(Calendar.YEAR, 2001);

                if (row.getObject("DATE") != null) {

                    date = row.getDate("DATE");

                }

                prices.add(new PriceByQty(row.getLong("QTE"), row.getBigDecimal("PRIX"), date.getTime()));

            }

            result = PriceByQty.getPriceForQty(qty, prices, d);

            if (result == null) {

                // Can occur during editing

                result = BigDecimal.ZERO;

            }

            return result;

        } else {

            return rowVals.getBigDecimal("PA_HT");

        }

    }

    private final Map<String, SQLRowAccessor> mapArticle = new HashMap<String, SQLRowAccessor>();

    private final Map<Tuple3<String, String, String>, SQLRowAccessor> mapDeclArticle = new HashMap<Tuple3<String, String, String>, SQLRowAccessor>();

    private void fillArticles() throws SQLException {

        final SQLTable table = Configuration.getInstance().getRoot().findTable("ARTICLE");

        SQLRowValues graph = new SQLRowValues(table);

        graph.put("ID", null);

        graph.put("CODE", null);

        graph.put("SYNC_ID", null);

        graph.put("NOM", null);

        graph.put("PA_HT", null);

        graph.putRowValues("ID_STOCK").putNulls("ID", "QTE_REEL", "QTE_TH", "QTE_LIV_ATTENTE", "QTE_RECEPT_ATTENTE");

        graph.putRowValues("ID_ARTICLE_DECLINAISON_COULEUR").putNulls("ID", "NOM");

        graph.putRowValues("ID_ARTICLE_DECLINAISON_TAILLE").putNulls("ID", "NOM");

        final SQLTable tableArtElt = table.getTable("ARTICLE_ELEMENT");

        SQLRowValues artElt = new SQLRowValues(tableArtElt);

        artElt.put("ID", null);

        artElt.put("QTE", null);

        artElt.put("QTE_UNITAIRE", null);

        artElt.put("ID_ARTICLE_PARENT", graph);

        final SQLRowValues putRowValues = artElt.putRowValues("ID_ARTICLE");

        putRowValues.putNulls("ID", "CODE", "NOM").putRowValues("ID_STOCK").putNulls("QTE_TH", "QTE_REEL", "QTE_LIV_ATTENTE", "QTE_RECEPT_ATTENTE");

        putRowValues.putRowValues("ID_ARTICLE_DECLINAISON_TAILLE").putNulls("ID", "NOM");

        putRowValues.putRowValues("ID_ARTICLE_DECLINAISON_COULEUR").putNulls("ID", "NOM");

        SQLRowValuesListFetcher fetcher = SQLRowValuesListFetcher.create(graph);

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

        for (SQLRowValues sqlRowValues : results) {

            final Set<SQLRowValues> referentRows = sqlRowValues.getReferentRows(tableArtElt.getField("ID_ARTICLE_PARENT"));

            // On ne prend que les articles simples

            if (referentRows.size() == 0) {

                final SQLRowAccessor couleur = sqlRowValues.getObject("ID_ARTICLE_DECLINAISON_COULEUR") == null ? null : sqlRowValues.getNonEmptyForeign("ID_ARTICLE_DECLINAISON_COULEUR");

                final SQLRowAccessor taille = sqlRowValues.getObject("ID_ARTICLE_DECLINAISON_TAILLE") == null ? null : sqlRowValues.getNonEmptyForeign("ID_ARTICLE_DECLINAISON_TAILLE");

                if (couleur == null && taille == null) {

                    mapArticle.put(sqlRowValues.getString("CODE"), sqlRowValues);

                } else if (couleur == null) {

                    mapDeclArticle.put(Tuple3.create(sqlRowValues.getString("CODE"), null, taille.getString("NOM")), sqlRowValues);

                } else if (taille == null) {

                    mapDeclArticle.put(Tuple3.create(sqlRowValues.getString("CODE"), couleur.getString("NOM"), null), sqlRowValues);

                } else {

                    mapDeclArticle.put(Tuple3.create(sqlRowValues.getString("CODE"), couleur.getString("NOM"), taille.getString("NOM")), sqlRowValues);

                }

            } else {

            }

        }

    }

    private SQLRowAccessor findArticle(String code, String couleur, String taille) throws SQLException {

        if (!mapCouleur.containsKey(couleur)) {

            SQLRowValues rowVals = new SQLRowValues(root.getTable("ARTICLE_DECLINAISON_COULEUR"));

            rowVals.put("NOM", couleur);

            mapCouleur.put(couleur, rowVals.commit().getID());

        }

        if (!mapTaille.containsKey(taille)) {

            SQLRowValues rowVals = new SQLRowValues(root.getTable("ARTICLE_DECLINAISON_TAILLE"));

            rowVals.put("NOM", taille);

            mapTaille.put(taille, rowVals.commit().getID());

        }

        SQLRowAccessor sqlRowAccessor;

        if ((couleur == null || couleur.trim().length() == 0) && (taille == null || taille.trim().length() == 0)) {

            sqlRowAccessor = mapArticle.get(code);

        } else if (couleur == null || couleur.trim().length() == 0) {

            sqlRowAccessor = mapDeclArticle.get(Tuple3.create(code, null, taille.trim()));

            if (sqlRowAccessor == null) {

                sqlRowAccessor = cloneFromArticle(mapArticle.get(code), null, mapTaille.get(taille.trim()));

                mapDeclArticle.put(Tuple3.create(code, null, taille.trim()), sqlRowAccessor);

            }

        } else if (taille == null || taille.trim().length() == 0) {

            sqlRowAccessor = mapDeclArticle.get(Tuple3.create(code, couleur.trim(), null));

            if (sqlRowAccessor == null) {

                sqlRowAccessor = cloneFromArticle(mapArticle.get(code), mapCouleur.get(couleur.trim()), null);

                mapDeclArticle.put(Tuple3.create(code, couleur.trim(), null), sqlRowAccessor);

            }

        } else {

            sqlRowAccessor = mapDeclArticle.get(Tuple3.create(code, couleur.trim(), taille.trim()));

            if (sqlRowAccessor == null) {

                if (mapArticle.get(code) != null) {

                    sqlRowAccessor = cloneFromArticle(mapArticle.get(code),

                            mapCouleur.get(couleur.trim()), mapTaille.get(taille.trim()));

                    mapDeclArticle.put(Tuple3.create(code, couleur.trim(), taille.trim()), sqlRowAccessor);

                }

            }

        }

        return sqlRowAccessor;

    }

    public SQLRow cloneFromArticle(SQLRowAccessor rArt, Integer idCouleur, Integer idTaille) throws SQLException {

        if (rArt == null) {

            return null;

        }

        SQLRowValues copy = artElt.createCopy(rArt.getID());

        if (idCouleur != null) {

            copy.put("ID_ARTICLE_DECLINAISON_COULEUR", idCouleur);

        }

        if (idTaille != null) {

            copy.put("ID_ARTICLE_DECLINAISON_TAILLE", idTaille);

        }

        return copy.commit();

    }

    private String getColumnName(int columnNumber) {

        int dividend = columnNumber;

        String columnName = "";

        int modulo;

        while (dividend >= 0) {

            modulo = dividend % 26;

            columnName = String.valueOf((char) (65 + modulo)) + columnName;

            dividend = (int) ((dividend - modulo) / 26);

            if (dividend <= 0) {

                break;

            } else {

                dividend--;

            }

        }

        return columnName;

    }

}