WebSVN - OpenConcerto - Diff - Rev 156 and 182 - /trunk/OpenConcerto/src/org/openconcerto/erp/core/supplychain/stock/element/EtatStockFromInventoryFileCreator.java

 /*
  * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS HEADER.
+ *
- * Copyright 2011 OpenConcerto, by ILM Informatique. All rights reserved.
+ * 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 = Configuration.getInstance().getRoot().findTable("ARTICLE");
+        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, SQLRowValues> articles = getArticles();
+        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) {
+            final DataImporter importer = new DataImporter(table) {
-            @Override
+                @Override
-            protected void customizeRowValuesToFetch(SQLRowValues vals) {
+                protected void customizeRowValuesToFetch(SQLRowValues vals) {
-                vals.putRowValues("ID_STOCK").putNulls("ID", "QTE_REEL", "QTE_TH");
+                    vals.putRowValues("ID_STOCK").putNulls("ID", "QTE_REEL", "QTE_TH");
+                }
-        };
+            };
-        importer.setSkipFirstLine(true);
+            importer.setSkipFirstLine(true);
-        ArrayTableModel m = importer.createModelFrom(file);
+            ArrayTableModel m = importer.createModelFrom(file);
-        SQLRowValues rowValsEtatStock = new SQLRowValues(table.getTable("ETAT_STOCK"));
+            SQLRowValues rowValsEtatStock = new SQLRowValues(table.getTable("ETAT_STOCK"));
-        rowValsEtatStock.put("DATE", d);
+            rowValsEtatStock.put("DATE", d);
-        SQLRow etatStock = rowValsEtatStock.commit();
+            SQLRow etatStock = rowValsEtatStock.commit();
-        BigDecimal total = BigDecimal.ZERO;
+            BigDecimal total = BigDecimal.ZERO;
-        for (int i = 0; i < m.getRowCount(); i++) {
+            for (int i = 0; i < m.getRowCount(); i++) {
-            List<Object> o = m.getLineValuesAt(i);
+                List<Object> o = m.getLineValuesAt(i);
-            String code = o.get(0).toString();
+                String code = o.get(columnMapping.get(CODE)).toString();
-            if (code.trim().length() == 0) {
+                if (code.trim().length() == 0) {
-                break;
+                    break;
+                }
-            final String stringQty = o.get(3).toString();
+                final String stringQty = o.get(columnMapping.get(QTE)).toString();
-            Integer qty = stringQty.trim().length() == 0 ? 0 : Integer.valueOf(stringQty);
+                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();
+                }
-            SQLRowValues match = articles.get(code);
+                SQLRowAccessor match = findArticle(code, couleur, taille);
-            if (match != null) {
+                if (match != null) {
-                SQLRowValues stockValues = new SQLRowValues(table.getTable("ETAT_STOCK_ELEMENT"));
+                    SQLRowValues stockValues = new SQLRowValues(table.getTable("ETAT_STOCK_ELEMENT"));
-                final BigDecimal qtyB = new BigDecimal(qty);
+                    final BigDecimal qtyB = new BigDecimal(qty);
-                stockValues.put("QTE", qtyB);
+                    stockValues.put("QTE", qtyB);
-                stockValues.put("NOM", match.getString("NOM"));
+                    stockValues.put("NOM", match.getString("NOM"));
-                stockValues.put("CODE", match.getString("CODE"));
+                    stockValues.put("CODE", match.getString("CODE"));
-                stockValues.put("ID_ARTICLE", match.getID());
+                    stockValues.put("ID_ARTICLE", match.getID());
-                final BigDecimal prc = getPRC(match, qty, d);
+                    final BigDecimal prc = getPRC(match, qty, d);
-                stockValues.put("PA", prc);
+                    stockValues.put("PA", prc);
-                final BigDecimal totalElt = prc.multiply(qtyB);
+                    final BigDecimal totalElt = prc.multiply(qtyB);
-                stockValues.put("T_PA", totalElt);
+                    stockValues.put("T_PA", totalElt);
-                stockValues.put("ID_ETAT_STOCK", etatStock.getID());
+                    stockValues.put("ID_ETAT_STOCK", etatStock.getID());
-                stockValues.commit();
+                    stockValues.commit();
-                total = total.add(totalElt);
+                    total = total.add(totalElt);
-            } else {
+                } else {
-                System.err.println("Aucun article correspondant au code " + code);
+                    System.err.println("Aucun article correspondant au code " + code);
+                }
+            }
-        etatStock.createEmptyUpdateRow().put("MONTANT_HA", total).commit();
+            etatStock.createEmptyUpdateRow().put("MONTANT_HA", total).commit();
+        }
+    }
-    public BigDecimal getPRC(SQLRowValues rowVals, int qty, Date d) {
+    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 Map<String, SQLRowValues> getArticles() throws SQLException {
+    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");
-        artElt.putRowValues("ID_ARTICLE").putNulls("ID", "CODE", "NOM").putRowValues("ID_STOCK").putNulls("QTE_TH", "QTE_REEL", "QTE_LIV_ATTENTE", "QTE_RECEPT_ATTENTE");
+        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();
-        Map<String, SQLRowValues> vals = new HashMap<String, SQLRowValues>();
         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) {
-                final String code = sqlRowValues.getString("CODE");
+                    mapArticle.put(sqlRowValues.getString("CODE"), sqlRowValues);
-                vals.put(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 vals;
+            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;
+    }
+}

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

svn://code.openconcerto.org/openconcerto

(root)/trunk/OpenConcerto/src/org/openconcerto/erp/core/supplychain/stock/element/EtatStockFromInventoryFileCreator.java - Rev 156 → 182