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.ProductComponent;
import org.openconcerto.erp.core.sales.product.model.ProductHelper;
import org.openconcerto.erp.core.sales.product.model.ProductHelper.SupplierPriceField;
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.SQLBase;
import org.openconcerto.sql.model.SQLInsert;
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.model.SQLUpdate;
import org.openconcerto.sql.model.Where;
import org.openconcerto.sql.preferences.SQLPreferences;
import org.openconcerto.sql.request.UpdateBuilder;
import org.openconcerto.sql.utils.SQLUtils;
import org.openconcerto.utils.CollectionUtils;
import org.openconcerto.utils.Tuple3;
import org.openconcerto.utils.cc.ITransformer;

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.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;

import javax.swing.JOptionPane;

import org.apache.commons.dbutils.ResultSetHandler;

public class InventaireFromEtatStockImporter {

    private Map<String, SQLRowValues> kits = new HashMap<String, SQLRowValues>();
    private Map<Integer, InventaireProductLine> kitsChildren = new HashMap<Integer, InventaireProductLine>();
    private List<String> codeKits = new ArrayList<String>();
    private static String FAMILLE = "Famille";
    private static String CODE = "Code";
    private static String NOM = "Nom";
    private static String TAILLE = "Taille";
    private static String DEPOT = "Dépôt";
    private static String COULEUR = "Couleur";
    private static String QTE = "Qté réelle relevée";
    private static String QTE_OPENCONCERTO = "QTE OpenConcerto";
    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 InventaireFromEtatStockImporter(SQLElement articleElt) {
        this.root = articleElt.getTable().getDBRoot();
        this.artElt = articleElt;
        {
            SQLSelect sel = new SQLSelect();
            sel.addSelect(articleElt.getTable().getTable("ARTICLE_DECLINAISON_COULEUR").getKey());
            sel.addSelect(articleElt.getTable().getTable("ARTICLE_DECLINAISON_COULEUR").getField("NOM"));

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

            for (SQLRow sqlRow : l) {
                this.mapCouleur.put(sqlRow.getString("NOM").trim(), sqlRow.getID());
            }
        }

        {
            SQLSelect sel = new SQLSelect();
            sel.addSelect(articleElt.getTable().getTable("ARTICLE_DECLINAISON_TAILLE").getKey());
            sel.addSelect(articleElt.getTable().getTable("ARTICLE_DECLINAISON_TAILLE").getField("NOM"));

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

            for (SQLRow sqlRow : l) {
                this.mapTaille.put(sqlRow.getString("NOM").trim(), sqlRow.getID());
            }
        }
    }

    public void importArticles(File file, DBRoot root) throws IOException, SQLException {

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

        // Récupération des couples articles/stocks existant
        fillArticles();

        // Matching des colonnes
        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);
        columnMapping.put(QTE_OPENCONCERTO, null);
        columnMapping.put(DEPOT, 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, QTE_OPENCONCERTO, TAILLE, COULEUR);
        } else {
            required = Arrays.asList(CODE, QTE, QTE_OPENCONCERTO);
        }

        SQLSelect selDepot = new SQLSelect();
        selDepot.addSelectStar(tableArtElt.getTable("DEPOT_STOCK"));
        final List<SQLRow> listDepot = SQLRowListRSH.execute(selDepot);
        final Map<String, SQLRow> mapDepot = new HashMap<>();
        for (SQLRow sqlRow : listDepot) {
            mapDepot.put(sqlRow.getString("NOM"), sqlRow);
        }

        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", "ID_DEPOT_STOCK");
                }
            };
            importer.setSkipFirstLine(true);

            ArrayTableModel m = importer.createModelFrom(file);

            // Check depot
            boolean multiDepotPrefs = prefs.getBoolean(GestionArticleGlobalPreferencePanel.STOCK_MULTI_DEPOT, false);
            SQLRowAccessor defaultDepotRow = null;
            boolean multiDepotFile = columnMapping.containsKey(DEPOT);
            if (!multiDepotFile) {
                if (multiDepotPrefs) {
                    // TODO popup choix depot
                } else {
                    defaultDepotRow = table.getTable("DEPOT_STOCK").getRow(DepotStockSQLElement.DEFAULT_ID);
                }
            } else {
                Set<String> errors = new HashSet<>();
                for (int i = 1; i < m.getRowCount(); i++) {
                    List<Object> o = m.getLineValuesAt(i);
                    if (o.size() >= 5) {
                        System.err.println(o);
                        String code = o.get(columnMapping.get(CODE)).toString();
                        if (code.trim().length() > 0) {

                            final String depotName = o.get(columnMapping.get(DEPOT)).toString().trim();
                            if (depotName.length() == 0) {
                                errors.add("Le Depôt n'est pas renseigné, ligne " + i + ". Import annulé!");

                            } else if (mapDepot.get(depotName) == null) {
                                errors.add("Impossible de trouver le dépôt " + depotName + ", ligne " + i + ". Import annulé!");
                            }

                        }
                    }
                }
                if (!errors.isEmpty()) {
                    JOptionPane.showMessageDialog(null, errors);
                    return;
                }
            }

            Calendar c = Calendar.getInstance();
            // c.set(Calendar.DAY_OF_MONTH, 1);
            // c.set(Calendar.MONTH, Calendar.JANUARY);
            // c.set(Calendar.HOUR_OF_DAY, 0);
            Date today = c.getTime();

            // TODO ne pas vider les stocks des kits, recalculer les stocks des kits

            Map<Integer, Integer> mapDepotEtat = new HashMap<>();

            List<SQLInsert> inserts = new ArrayList<>();
            List<SQLUpdate> updates = new ArrayList<>();
            for (int i = 1; i < m.getRowCount(); i++) {
                List<Object> o = m.getLineValuesAt(i);
                if (o.size() >= 5) {
                    System.err.println(o);
                    String code = o.get(columnMapping.get(CODE)).toString();
                    if (code.trim().length() > 0) {

                        final String stringQty = o.get(columnMapping.get(QTE)).toString();
                        Double qty = stringQty.trim().length() == 0 ? 0 : Double.valueOf(stringQty);
                        final String stringQtyOld = o.get(columnMapping.get(QTE_OPENCONCERTO)).toString();
                        float qtyOld = stringQtyOld.trim().length() == 0 ? 0 : Float.valueOf(stringQtyOld);

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

                        InventaireProductLine match = findArticle(code, couleur, taille);
                        if (match != null) {

                            SQLRowAccessor depotRow = defaultDepotRow;

                            if (multiDepotFile) {
                                final String depotName = o.get(columnMapping.get(DEPOT)).toString().trim();
                                depotRow = mapDepot.get(depotName);
                            }

                            if (!mapDepotEtat.containsKey(depotRow.getID())) {
                                mapDepotEtat.put(depotRow.getID(), createEtat(table.getTable("ETAT_STOCK"), depotRow.getID()).commit().getID());
                            }

                            int etatID = mapDepotEtat.get(depotRow.getID());

                            SQLRowAccessor stockValues = match.getOrCreateStockRowValues(depotRow);

                            final SQLTable tableMvt = table.getTable("MOUVEMENT_STOCK");
                            SQLInsert insertMvtStockClotureFermeture = new SQLInsert();
                            insertMvtStockClotureFermeture.add(tableMvt.getField("QTE"), -qtyOld);
                            insertMvtStockClotureFermeture.add(tableMvt.getField("NOM"), "Clôture stock avant inventaire");
                            insertMvtStockClotureFermeture.add(tableMvt.getField("ID_ARTICLE"), match.getRowValsArt().getID());
                            insertMvtStockClotureFermeture.add(tableMvt.getField("DATE"), today);
                            insertMvtStockClotureFermeture.add(tableMvt.getField("REEL"), Boolean.TRUE);
                            insertMvtStockClotureFermeture.add(tableMvt.getField("ID_STOCK"), stockValues.getID());

                            BigDecimal prc = getPRC(match.getRowValsArt(), Math.round(qtyOld), today);
                            if (prc == null) {
                                prc = BigDecimal.ZERO;
                            }
                            if (tableMvt.contains("PRICE")) {
                                insertMvtStockClotureFermeture.add(tableMvt.getField("PRICE"), prc);
                            }
                            insertMvtStockClotureFermeture.add(tableMvt.getField("CLOTURE"), Boolean.TRUE);
                            insertMvtStockClotureFermeture.add(tableMvt.getField("ID_ETAT_STOCK"), etatID);
                            // insertMvtStockClotureFermeture.getGraph().store(StoreMode.COMMIT,
                            // false);

                            inserts.add(insertMvtStockClotureFermeture);

                            final SQLTable tableEtatElt = table.getTable("ETAT_STOCK_ELEMENT");
                            SQLInsert insertItem = new SQLInsert();
                            insertItem.add(tableEtatElt.getField("ID_ETAT_STOCK"), etatID);
                            insertItem.add(tableEtatElt.getField("PA"), prc);
                            insertItem.add(tableEtatElt.getField("PV"), BigDecimal.ZERO);
                            insertItem.add(tableEtatElt.getField("QTE"), qtyOld);
                            insertItem.add(tableEtatElt.getField("T_PA"), prc.multiply(new BigDecimal(qtyOld)));
                            insertItem.add(tableEtatElt.getField("T_PV"), BigDecimal.ZERO);
                            insertItem.add(tableEtatElt.getField("CODE"), match.getRowValsArt().getString("CODE"));
                            insertItem.add(tableEtatElt.getField("NOM"), match.getRowValsArt().getString("NOM"));
                            insertItem.add(tableEtatElt.getField("ID_ARTICLE"), match.getRowValsArt().getID());
                            // insertItem.getGraph().store(StoreMode.COMMIT, false);
                            inserts.add(insertItem);

                            SQLInsert insertMvtStockClotureOuverture = new SQLInsert();
                            insertMvtStockClotureOuverture.add(tableMvt.getField("QTE"), qty);
                            insertMvtStockClotureOuverture.add(tableMvt.getField("NOM"), "Mise en stock inventaire");
                            insertMvtStockClotureOuverture.add(tableMvt.getField("ID_ETAT_STOCK"), etatID);
                            insertMvtStockClotureOuverture.add(tableMvt.getField("ID_ARTICLE"), match.getRowValsArt().getID());
                            insertMvtStockClotureOuverture.add(tableMvt.getField("DATE"), today);
                            insertMvtStockClotureOuverture.add(tableMvt.getField("REEL"), Boolean.TRUE);
                            insertMvtStockClotureOuverture.add(tableMvt.getField("ID_STOCK"), stockValues.getID());
                            insertMvtStockClotureOuverture.add(tableMvt.getField("OUVERTURE"), Boolean.TRUE);
                            if (tableMvt.contains("PRICE")) {
                                insertMvtStockClotureOuverture.add(tableMvt.getField("PRICE"), getPRC(match.getRowValsArt(), qty.intValue(), today));
                            }
                            inserts.add(insertMvtStockClotureOuverture);
                            // insertMvtStockClotureOuverture.getGraph().store(StoreMode.COMMIT,
                            // false);

                            // if (!match.isForeignEmpty("ID_STOCK")) {
                            // match.getForeign("ID_STOCK").createEmptyUpdateRow().put("QTE_REEL",
                            // qty).commit();
                            // } else {
                            // final SQLRowValues createEmptyUpdateRow =
                            // stockValues.createEmptyUpdateRow();
                            // createEmptyUpdateRow.put("QTE_REEL", qty);
                            // createEmptyUpdateRow.getGraph().store(StoreMode.COMMIT, false);
                            SQLUpdate up = new SQLUpdate(new Where(stockValues.getTable().getKey(), "=", stockValues.getID()));
                            up.add(stockValues.getTable().getField("QTE_REEL"), qty);
                            updates.add(up);
                            // }

                        } else {
                            System.err.println("\t Aucun article correspondant au code " + code);
                        }
                    }
                }
            }

            if (!inserts.isEmpty()) {
                SQLInsert.executeMultipleWithBatch(table.getDBSystemRoot(), inserts);
            }
            if (!updates.isEmpty()) {
                SQLUpdate.executeMultipleWithBatch(table.getDBSystemRoot(), updates);
                System.err.println(CollectionUtils.join(updates, ";\n"));
            }

            /**
             * Mise à jour des kits
             */

            final List<? extends SQLRowAccessor> rowsDepot;
            if (multiDepotFile) {
                rowsDepot = listDepot;
            } else {
                rowsDepot = Arrays.asList(defaultDepotRow);
            }

            List<String> reqs = new ArrayList<String>();
            // Recalcul des stocks pour chaque kit impacté
            for (String code : this.codeKits) {
                System.err.println(code);
                SQLRowValues rowValsKit = this.kits.get(code);

                // récupération des stocks initialisés pour ce kit
                final Set<SQLRowValues> referentRowsStock = rowValsKit.getReferentRows(table.getTable("STOCK").getField("ID_ARTICLE"));
                for (SQLRowAccessor d : referentRowsStock) {
                    StockItem item = new StockItem(rowValsKit, d);
                    Collection<SQLRowValues> elts = rowValsKit.getReferentRows(tableArtElt.getField("ID_ARTICLE_PARENT"));

                    // Recalcul du stock pour le dépot
                    for (SQLRowValues sqlRowValues : elts) {
                        final InventaireProductLine inventaireProductLine = this.kitsChildren.get(sqlRowValues.getID());

                        if (inventaireProductLine != null && sqlRowValues.getForeign("ID_ARTICLE") != null) {
                            final SQLRowValues stockArticle = inventaireProductLine.getStockRowValues(d.getForeign("ID_DEPOT_STOCK"));
                            if (stockArticle != null)
                                item.addItemComponent(new StockItemComponent(new StockItem(sqlRowValues.getForeign("ID_ARTICLE"), stockArticle), sqlRowValues.getBigDecimal("QTE_UNITAIRE"),
                                        sqlRowValues.getInt("QTE")));
                        }
                    }
                    item.updateQtyFromChildren();
                    reqs.add(item.getUpdateRequest());
                }
            }

            if (!reqs.isEmpty()) {
                List<? extends ResultSetHandler> handlers = new ArrayList<ResultSetHandler>(reqs.size());
                for (String s : reqs) {
                    handlers.add(null);
                }
                // FIXME FIRE TABLE CHANGED TO UPDATE ILISTE ??
                SQLUtils.executeMultiple(table.getDBSystemRoot(), reqs, handlers);
            }
            // Recalcul du stock théorique
            final SQLTable tableStock = root.getTable("STOCK");
            org.openconcerto.sql.request.UpdateBuilder req = new UpdateBuilder(tableStock);
            req.set("QTE_TH", SQLBase.quoteIdentifier("QTE_REEL") + "-" + SQLBase.quoteIdentifier("QTE_LIV_ATTENTE") + "+" + SQLBase.quoteIdentifier("QTE_RECEPT_ATTENTE"));
            table.getDBSystemRoot().getDataSource().execute(req.asString());

            /**
             * Mise à jour des prix mini
             */
            // for (SQLRowValues rowValsArt : rowValsArtNonSync) {
            // SQLRow rowArt = rowValsArt.asRow();
            // List<SQLRow> rowsPVMin =
            // rowArt.getReferentRows(tableArtElt.getTable("ARTICLE_PRIX_MIN_VENTE"));
            // List<SQLRow> rowsPA =
            // rowArt.getReferentRows(tableArtElt.getTable("ARTICLE_TARIF_FOURNISSEUR"));
            //
            // // On récupére les derniers prix min valides
            // Map<Integer, SQLRow> mapLastValidRows = new HashMap<Integer, SQLRow>();
            // for (SQLRow rowPVMin : rowsPVMin) {
            // final int qteMinPrice = rowPVMin.getInt("QTE");
            // SQLRow rowValsLastValid = mapLastValidRows.get(qteMinPrice);
            // if (rowValsLastValid == null || rowValsLastValid.getDate("DATE") == null ||
            // rowValsLastValid.getDate("DATE").before(rowPVMin.getDate("DATE"))) {
            // mapLastValidRows.put(qteMinPrice, rowPVMin);
            // }
            // }
            //
            // // On récupére les derniers Prix d'achat valide
            // Map<Integer, SQLRow> mapLastValidAchatRows = new HashMap<Integer, SQLRow>();
            // for (SQLRow rowPA : rowsPA) {
            // final int qtePRC = rowPA.getInt("QTE");
            // SQLRow rowValsLastValid = mapLastValidAchatRows.get(qtePRC);
            // if (rowValsLastValid == null || rowValsLastValid.getDate("DATE_PRIX") == null ||
            // rowValsLastValid.getDate("DATE_PRIX").before(rowPA.getDate("DATE_PRIX"))) {
            // mapLastValidAchatRows.put(qtePRC, rowPA);
            // }
            // }
            //
            // // Mise à jour, si Prix < au prix min, du PRC et des prix min
            // for (Integer qte : mapLastValidAchatRows.keySet()) {
            // SQLRow rowVals = mapLastValidAchatRows.get(qte);
            // checkMinPrice(rowVals, mapLastValidRows.get(qte));
            // }
            // }
        }

    }

    public static SQLInsert getCreateStock(SQLRowAccessor article, SQLRowAccessor depot) {

        SQLTable stockTable = article.getTable().getTable("STOCK");
        SQLRowValues putRowValuesStock = new SQLRowValues(stockTable);
        putRowValuesStock.putNulls(stockTable.getTable().getFieldsName());

        SQLRowValuesListFetcher fetch = SQLRowValuesListFetcher.create(putRowValuesStock);
        Where w = new Where(putRowValuesStock.getTable().getField("ID_DEPOT_STOCK"), "=", depot.getID());
        Where w2 = new Where(putRowValuesStock.getTable().getField("ID_ARTICLE"), "=", article.getID());
        Collection<SQLRowValues> rowValsResult = fetch.fetch(w.and(w2));
        if (rowValsResult.size() == 0) {
            SQLInsert insert = new SQLInsert();
            insert.add(stockTable.getField("ID_ARTICLE"), article.getID());
            insert.add(stockTable.getField("ID_DEPOT_STOCK"), depot.getID());
            insert.add(stockTable.getField("QTE_TH"), 0F);
            insert.add(stockTable.getField("QTE_REEL"), 0F);
            insert.add(stockTable.getField("QTE_RECEPT_ATTENTE"), 0F);
            insert.add(stockTable.getField("QTE_LIV_ATTENTE"), 0F);
            return insert;
        }
        return null;

    }

    private SQLRowValues createEtat(SQLTable tableEtat, int depotID) {
        SQLRowValues rowVals = new SQLRowValues(tableEtat);
        rowVals.put("DATE", new Date());
        rowVals.put("INVENTAIRE", Boolean.TRUE);
        rowVals.put("ID_DEPOT_STOCK", depotID);
        return rowVals;
    }

    private void checkMinPrice(SQLRow rowValsSuplierLastValid, SQLRow lastValidRow) {
        boolean update = false;
        final ProductHelper helper = new ProductHelper(rowValsSuplierLastValid.getTable().getDBRoot());

        BigDecimal result = helper.getEnumPrice(rowValsSuplierLastValid, SupplierPriceField.COEF_PRIX_MINI);
        if (result != null) {
            final int qteSuplier = rowValsSuplierLastValid.getInt("QTE");

            final Calendar date2 = rowValsSuplierLastValid.getDate("DATE_PRIX");
            if (date2 != null) {
                if (lastValidRow != null) {
                    final Calendar date1 = lastValidRow.getDate("DATE");
                    if ((date1.get(Calendar.YEAR) == date2.get(Calendar.YEAR) && date1.get(Calendar.MONTH) == date2.get(Calendar.MONTH)
                            && date1.get(Calendar.DAY_OF_MONTH) == date2.get(Calendar.DAY_OF_MONTH)) || date1.after(date2)) {
                        if (lastValidRow.getBigDecimal("PRIX") != null && lastValidRow.getInt("QTE") <= qteSuplier) {
                            try {
                                lastValidRow.asRowValues().put("PRIX", result).commit();
                            } catch (SQLException e) {
                                // TODO Auto-generated catch block
                                e.printStackTrace();
                            }
                            // purchaseMinPriceListTable.setPriceMin(result);
                            update = true;
                        }
                    } else {
                        if (date1.before(date2)) {
                            SQLRowValues rowValsToInsert = new SQLRowValues(lastValidRow.getTable());
                            rowValsToInsert.put("PRIX", result);
                            rowValsToInsert.put("DATE", rowValsSuplierLastValid.getObject("DATE_PRIX"));
                            rowValsToInsert.put("QTE", rowValsSuplierLastValid.getObject("QTE"));
                            rowValsToInsert.put("ID_ARTICLE", rowValsSuplierLastValid.getInt("ID_ARTICLE"));
                            try {
                                rowValsToInsert.commit();
                            } catch (SQLException e) {
                                // TODO Auto-generated catch block
                                e.printStackTrace();
                            }
                        }
                    }
                }
            }
        }

    }

    public BigDecimal getPRC(SQLRowValues rowVals, int qty, Date d) {
        // 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;
        // }

        ProductComponent comp = new ProductComponent(rowVals, new BigDecimal(qty), null, null);
        return comp.getPRC(d);
        // return result;
    }

    private final Map<String, InventaireProductLine> mapDepotArticle = new HashMap();
    private final Map<String, InventaireProductLine> mapDepotArticleVirtuel = new HashMap();
    private final Map<Tuple3<String, String, String>, InventaireProductLine> mapDepotDeclArticle = new HashMap();

    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("NOM", null);
        graph.put("VIRTUEL", null);
        graph.putRowValues("ID_ARTICLE_DECLINAISON_COULEUR").putNulls("ID", "NOM");
        graph.putRowValues("ID_ARTICLE_DECLINAISON_TAILLE").putNulls("ID", "NOM");

        final SQLTable foreignTableStock = table.getForeignTable("ID_STOCK");
        SQLRowValues graphStock = new SQLRowValues(foreignTableStock);
        graphStock.putNulls("ID", "QTE_REEL", "QTE_TH", "QTE_LIV_ATTENTE", "QTE_RECEPT_ATTENTE").putRowValues("ID_DEPOT_STOCK").putNulls("NOM");
        graphStock.put("ID_ARTICLE", graph);

        SQLRowValues graphStockArt = new SQLRowValues(foreignTableStock);
        graphStockArt.putNulls("ID", "QTE_REEL", "QTE_TH", "QTE_LIV_ATTENTE", "QTE_RECEPT_ATTENTE").putRowValues("ID_DEPOT_STOCK").putNulls("NOM");
        graph.put("ID_STOCK", graphStockArt);

        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 articleParent = artElt.putRowValues("ID_ARTICLE");
        articleParent.putNulls("ID", "CODE", "NOM", "VIRTUEL");
        articleParent.putRowValues("ID_ARTICLE_DECLINAISON_COULEUR").putNulls("ID", "NOM");
        articleParent.putRowValues("ID_ARTICLE_DECLINAISON_TAILLE").putNulls("ID", "NOM");

        SQLRowValues graphStockItem = new SQLRowValues(foreignTableStock);
        graphStockItem.put("ID_ARTICLE", articleParent);

        SQLRowValues graphStockArtItem = new SQLRowValues(foreignTableStock);
        graphStockArtItem.putNulls("ID_DEPOT_STOCK", "ID", "QTE_REEL", "QTE_TH", "QTE_LIV_ATTENTE", "QTE_RECEPT_ATTENTE");
        articleParent.put("ID_STOCK", graphStockArtItem);

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

        Calendar c = Calendar.getInstance();
        // c.set(Calendar.DAY_OF_MONTH, 1);
        c.add(Calendar.MONTH, -2);
        c.set(Calendar.DAY_OF_MONTH, 31);

        for (SQLRowValues sqlRowValues : results) {
            final String code = sqlRowValues.getString("CODE");

            if (sqlRowValues.getBoolean("VIRTUEL")) {
                this.mapDepotArticleVirtuel.put(sqlRowValues.getString("CODE"), new InventaireProductLine(sqlRowValues));
            }

            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) {
                this.mapDepotArticle.put(sqlRowValues.getString("CODE"), new InventaireProductLine(sqlRowValues));
            } else if (couleur == null) {
                this.mapDepotDeclArticle.put(Tuple3.create(sqlRowValues.getString("CODE"), null, taille.getString("NOM")), new InventaireProductLine(sqlRowValues));
            } else if (taille == null) {
                this.mapDepotDeclArticle.put(Tuple3.create(sqlRowValues.getString("CODE"), couleur.getString("NOM"), null), new InventaireProductLine(sqlRowValues));
            } else {
                this.mapDepotDeclArticle.put(Tuple3.create(sqlRowValues.getString("CODE"), couleur.getString("NOM"), taille.getString("NOM")), new InventaireProductLine(sqlRowValues));
            }

            final Set<SQLRowValues> referentRows = sqlRowValues.getReferentRows(tableArtElt.getField("ID_ARTICLE_PARENT"));
            if (referentRows.size() == 0) {
                // if (!sqlRowValues.isForeignEmpty("ID_STOCK")) {
                // SQLRowAccessor rowValsStock = sqlRowValues.getForeign("ID_STOCK");
                // int qteReel = rowValsStock.getInt("QTE_REEL");
                // {
                // SQLRowValues rowValsMvtStockCloture = new
                // SQLRowValues(table.getTable("MOUVEMENT_STOCK"));
                // rowValsMvtStockCloture.put("QTE", -qteReel);
                // rowValsMvtStockCloture.put("NOM", "Clôture du stock avant inventaire");
                // rowValsMvtStockCloture.put("ID_ARTICLE", sqlRowValues.getID());
                // rowValsMvtStockCloture.put("DATE", dEndYear);
                // rowValsMvtStockCloture.put("REEL", Boolean.TRUE);
                // rowValsMvtStockCloture.put("PRICE", getPRC(sqlRowValues, qteReel, dEndYear));
                // rowValsMvtStockCloture.commit();
                //
                // rowValsStock.createEmptyUpdateRow().put("QTE_REEL", 0).commit();
                // }
                //
                // } else {
                // sqlRowValues.putRowValues("ID_STOCK").commit();
                // }
            } else {
                boolean contains = false;
                for (SQLRowValues sqlRowValues2 : referentRows) {
                    if (sqlRowValues2.getForeign("ID_ARTICLE") != null && !sqlRowValues2.isForeignEmpty("ID_ARTICLE") && sqlRowValues2.getForeign("ID_ARTICLE").getString("CODE") != null) {
                        if (this.codeKits.contains(sqlRowValues2.getForeign("ID_ARTICLE").getString("CODE"))) {
                            contains = true;
                            break;
                        }
                    }
                }
                if (!contains) {
                    this.codeKits.add(0, code);
                } else {
                    this.codeKits.add(code);
                }
                this.kits.put(code, sqlRowValues);
                // if (sqlRowValues.isForeignEmpty("ID_STOCK")) {
                // sqlRowValues.putRowValues("ID_STOCK").commit();
                // }
            }

        }
        fillKitChildren();
    }

    private void fillKitChildren() {
        final SQLTable table = Configuration.getInstance().getRoot().findTable("ARTICLE");

        SQLRowValues graph = new SQLRowValues(table);
        graph.put("ID", null);
        graph.put("CODE", null);
        graph.put("NOM", null);
        graph.put("VIRTUEL", null);
        graph.putRowValues("ID_ARTICLE_DECLINAISON_COULEUR").putNulls("ID", "NOM");
        graph.putRowValues("ID_ARTICLE_DECLINAISON_TAILLE").putNulls("ID", "NOM");

        final SQLTable foreignTableStock = table.getForeignTable("ID_STOCK");
        SQLRowValues graphStock = new SQLRowValues(foreignTableStock);
        graphStock.putNulls("ID", "QTE_REEL", "QTE_TH", "QTE_LIV_ATTENTE", "QTE_RECEPT_ATTENTE").putRowValues("ID_DEPOT_STOCK").putNulls("NOM");
        graphStock.put("ID_ARTICLE", graph);

        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", graph);

        SQLRowValuesListFetcher fetcher = SQLRowValuesListFetcher.create(artElt);
        fetcher.appendSelTransf(new ITransformer<SQLSelect, SQLSelect>() {

            @Override
            public SQLSelect transformChecked(SQLSelect input) {
                input.setWhere(Where.inValues(tableArtElt.getField("ID_ARTICLE_PARENT"), SQLRow.getIDs(InventaireFromEtatStockImporter.this.kits.values())));
                return input;
            }
        });
        List<SQLRowValues> results = fetcher.fetch();
        for (SQLRowValues sqlRowValues : results) {
            final SQLRowAccessor foreign = sqlRowValues.getNonEmptyForeign("ID_ARTICLE");
            if (foreign != null) {
                this.kitsChildren.put(foreign.getID(), new InventaireProductLine(foreign.asRowValues()));
            }
        }
    }

    private InventaireProductLine findArticle(String code, String couleur, String taille) throws SQLException {
        if (!this.mapCouleur.containsKey(couleur)) {
            SQLRowValues rowVals = new SQLRowValues(this.root.getTable("ARTICLE_DECLINAISON_COULEUR"));
            rowVals.put("NOM", couleur);
            this.mapCouleur.put(couleur, rowVals.commit().getID());
        }
        if (!this.mapTaille.containsKey(taille)) {
            SQLRowValues rowVals = new SQLRowValues(this.root.getTable("ARTICLE_DECLINAISON_TAILLE"));
            rowVals.put("NOM", taille);
            this.mapTaille.put(taille, rowVals.commit().getID());
        }

        InventaireProductLine t;
        if ((couleur == null || couleur.trim().length() == 0) && (taille == null || taille.trim().length() == 0)) {
            t = this.mapDepotArticle.get(code);
        } else if (couleur == null || couleur.trim().length() == 0) {
            t = this.mapDepotDeclArticle.get(Tuple3.create(code, null, taille.trim()));
            if (t == null) {
                t = this.mapDepotDeclArticle.get(Tuple3.create(code, "", taille.trim()));
            }
            if (t == null) {
                System.err.println("\tCREATE ARTICLE " + code + " taille : " + taille);
                SQLRowValues rARtVirt = this.mapDepotArticleVirtuel.get(code).getRowValsArt();
                SQLRowValues artRow = cloneFromArticle(rARtVirt, null, this.mapTaille.get(taille.trim())).asRowValues();
                t = new InventaireProductLine(artRow);
                this.mapDepotDeclArticle.put(Tuple3.create(code, null, taille.trim()), t);
            }
        } else if (taille == null || taille.trim().length() == 0) {
            t = this.mapDepotDeclArticle.get(Tuple3.create(code, couleur.trim(), null));
            if (t == null) {
                t = this.mapDepotDeclArticle.get(Tuple3.create(code, couleur.trim(), ""));
            }
            if (t == null) {
                System.err.println("\tCREATE ARTICLE " + code + " couleur : " + couleur);
                SQLRowValues rARtVirt = this.mapDepotArticleVirtuel.get(code).getRowValsArt();
                SQLRowValues artRow = cloneFromArticle(rARtVirt, this.mapCouleur.get(couleur.trim()), null).asRowValues();
                t = new InventaireProductLine(artRow);
                this.mapDepotDeclArticle.put(Tuple3.create(code, couleur.trim(), null), t);
            }
        } else {
            t = this.mapDepotDeclArticle.get(Tuple3.create(code, couleur.trim(), taille.trim()));
            if (t == null) {
                if (this.mapDepotArticleVirtuel.get(code) != null) {
                    System.err.println("\tCREATE ARTICLE " + code + " taille : " + taille + " couleur : " + couleur);
                    SQLRowValues rARtVirt = this.mapDepotArticleVirtuel.get(code).getRowValsArt();
                    SQLRowValues artRow = cloneFromArticle(rARtVirt, this.mapCouleur.get(couleur.trim()), this.mapTaille.get(taille.trim())).asRowValues();
                    t = new InventaireProductLine(artRow);
                    this.mapDepotDeclArticle.put(Tuple3.create(code, couleur.trim(), taille.trim()), t);
                }
            }
        }
        return t;
    }

    public SQLRow cloneFromArticle(SQLRowAccessor rArt, Integer idCouleur, Integer idTaille) throws SQLException {
        if (rArt == null) {
            return null;
        }
        SQLRowValues copy = this.artElt.createCopy(rArt.getID());
        copy.put("VIRTUEL", Boolean.FALSE);
        copy.put("ID_ARTICLE_VIRTUEL_PERE", 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;
    }

    class InventaireProductLine {
        private final SQLRowValues rowValsArt;
        private final Map<String, SQLRowValues> stockMap = new HashMap<>();

        public InventaireProductLine(SQLRowValues rowValsArt) {
            this.rowValsArt = rowValsArt;

            Collection<SQLRowValues> stocks = this.rowValsArt.getReferentRows(this.rowValsArt.getTable().getTable("STOCK"));

            for (SQLRowValues rowValsStock : stocks) {
                this.stockMap.put(rowValsStock.getForeign("ID_DEPOT_STOCK").getString("NOM"), rowValsStock);
            }
        }

        public SQLRowValues getOrCreateStockRowValues(SQLRowAccessor depot) {
            final String depotName = depot.getString("NOM");
            if (!this.stockMap.containsKey(depotName)) {
                this.stockMap.put(depotName, ProductComponent.findOrCreateStock(this.rowValsArt, depot).asRowValues());
            }
            return this.stockMap.get(depotName);
        }

        public SQLRowValues getStockRowValues(SQLRowAccessor depot) {
            final String depotName = depot.getString("NOM");
            if (!this.stockMap.containsKey(depotName)) {
                return null;
            }
            return this.stockMap.get(depotName);
        }

        public SQLRowValues getRowValsArt() {
            return this.rowValsArt;
        }

    }
}