WebSVN - OpenConcerto - Diff - Rev 177 and 182 - /trunk/OpenConcerto/src/org/openconcerto/erp/core/supplychain/stock/element/EtatStockSnapshotCreator.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.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.model.Where;
 import org.openconcerto.utils.DecimalUtils;
 import org.openconcerto.utils.ExceptionHandler;
 import org.openconcerto.utils.cc.ITransformer;
 import java.math.BigDecimal;
 import java.sql.SQLException;
 import java.util.Date;
 import java.util.HashMap;
 import java.util.List;
 import java.util.Map;
 public class EtatStockSnapshotCreator {
     private final Date d;
     private final DBRoot root;
     private final SQLRowAccessor depot;
+    private final boolean withAllProducts;
-    public EtatStockSnapshotCreator(SQLRowAccessor depot, Date d, DBRoot root) {
+    public EtatStockSnapshotCreator(SQLRowAccessor depot, Date d, DBRoot root, boolean withAllProducts) {
         this.d = d;
         this.depot = depot;
         this.root = root;
+        this.withAllProducts = withAllProducts;
+    }
     public void create() {
         // Récupération des inventaires
         SQLTable tableEtatStock = this.root.getTable("ETAT_STOCK");
         SQLSelect sel = new SQLSelect();
         sel.addSelectStar(tableEtatStock);
         Where wEtat = new Where(tableEtatStock.getField("INVENTAIRE"), "=", Boolean.TRUE);
         wEtat = wEtat.and(new Where(tableEtatStock.getField("ID_DEPOT_STOCK"), "=", this.depot.getID()));
         sel.setWhere(wEtat);
         List<SQLRow> rowsEtatStock = SQLRowListRSH.execute(sel);
         Map<Integer, Integer> mapEtatStock = new HashMap<Integer, Integer>();
         for (SQLRow sqlRow : rowsEtatStock) {
             SQLTable tableMvtStock = this.root.getTable("MOUVEMENT_STOCK");
             SQLTable tableStock = this.root.getTable("STOCK");
             SQLSelect selMvt = new SQLSelect();
             selMvt.addSelect(tableMvtStock.getKey(), "MIN");
             Where wMvt = new Where(tableMvtStock.getField("OUVERTURE"), "=", Boolean.TRUE);
             wMvt = wMvt.and(new Where(tableMvtStock.getField("ID_ETAT_STOCK"), "=", sqlRow.getID()));
             wMvt = wMvt.and(new Where(tableMvtStock.getField("ID_STOCK"), "=", tableStock.getKey()));
             wMvt = wMvt.and(new Where(tableStock.getField("ID_DEPOT_STOCK"), "=", depot.getID()));
             selMvt.setWhere(wMvt);
             Integer idMvt = (Integer) tableMvtStock.getDBSystemRoot().getDataSource().executeScalar(selMvt.asString());
             if (idMvt != null) {
                 mapEtatStock.put(sqlRow.getID(), idMvt);
+            }
+        }
         Map<Integer, EtatStock> mapStockSnap = new HashMap<Integer, EtatStock>();
+        {
-            final SQLTable tableStock = this.root.getTable("MOUVEMENT_STOCK");
+            final SQLTable tableMvtStock = this.root.getTable("MOUVEMENT_STOCK");
-            final SQLRowValues vals = new SQLRowValues(tableStock);
+            final SQLRowValues vals = new SQLRowValues(tableMvtStock);
             vals.put("QTE", null);
-            if (tableStock.contains("PRICE")) {
+            if (tableMvtStock.contains("PRICE")) {
                 vals.put("PRICE", null);
+            }
             vals.put("ID_ARTICLE", null);
             vals.putRowValues("ID_STOCK").putNulls("QTE_REEL").putRowValues("ID_DEPOT_STOCK").putNulls("ID", "NOM", "CODE");
             // Calendar cal0116 = Calendar.getInstance();
             // cal0116.set(2016, Calendar.JANUARY, 1, 0, 0, 0);
             // final Date dateDeb = cal0116.getTime();
             // Récupération du dernier etat de stock
             SQLSelect selEtatD = new SQLSelect();
             selEtatD.addSelectStar(tableEtatStock);
             Where wEtatD = new Where(tableEtatStock.getField("INVENTAIRE"), "=", Boolean.TRUE);
             wEtatD = wEtatD.and(new Where(tableEtatStock.getField("ID_DEPOT_STOCK"), "=", this.depot.getID()));
             selEtatD.setWhere(wEtatD);
             List<SQLRow> rowsEtatStockD = SQLRowListRSH.execute(selEtatD);
             SQLRow rowEtatStockDeb = null;
             for (SQLRow sqlRow : rowsEtatStockD) {
                 if (sqlRow.getDate("DATE").getTime().before(this.d)) {
                     if (rowEtatStockDeb == null || rowEtatStockDeb.getDate("DATE").before(sqlRow.getDate("DATE"))) {
                         rowEtatStockDeb = sqlRow;
+                    }
+                }
+            }
             final Date dateDeb;
             final Integer idMvtStockDeb;
             if (rowEtatStockDeb != null) {
                 dateDeb = rowEtatStockDeb.getDate("DATE").getTime();
                 idMvtStockDeb = mapEtatStock.get(rowEtatStockDeb.getID());
             } else {
                 dateDeb = null;
                 idMvtStockDeb = null;
+            }
             final SQLRowValuesListFetcher fetcher = SQLRowValuesListFetcher.create(vals);
             fetcher.setSelTransf(new ITransformer<SQLSelect, SQLSelect>() {
                 @Override
                 public SQLSelect transformChecked(SQLSelect sel) {
-                    Where w = (new Where(tableStock.getField("DATE"), "<=", d));
+                    Where w = (new Where(tableMvtStock.getField("DATE"), "<=", d));
                     if (dateDeb != null) {
-                        w = w.and(new Where(tableStock.getField("DATE"), ">=", dateDeb));
+                        w = w.and(new Where(tableMvtStock.getField("DATE"), ">=", dateDeb));
-                        w = w.and(new Where(tableStock.getKey(), ">=", idMvtStockDeb));
+                        w = w.and(new Where(tableMvtStock.getKey(), ">=", idMvtStockDeb));
-                        w = w.and(new Where(tableStock.getField("CLOTURE"), "!=", Boolean.TRUE));
+                        w = w.and(new Where(tableMvtStock.getField("CLOTURE"), "!=", Boolean.TRUE));
+                    }
-                    w = w.and(new Where(tableStock.getField("REEL"), "=", Boolean.TRUE));
+                    w = w.and(new Where(tableMvtStock.getField("REEL"), "=", Boolean.TRUE));
-                    w = w.and(new Where(sel.getJoin(tableStock.getField("ID_STOCK")).getJoinedTable().getField("ID_DEPOT_STOCK"), "=", depot.getID()));
+                    w = w.and(new Where(sel.getJoin(tableMvtStock.getField("ID_STOCK")).getJoinedTable().getField("ID_DEPOT_STOCK"), "=", depot.getID()));
                     sel.setWhere(w);
                     return sel;
+                }
             });
             List<SQLRowValues> list = fetcher.fetch();
             BigDecimal totalHT = BigDecimal.ZERO;
             for (int i = 0; i < list.size(); i++) {
                 SQLRowValues rowVF = list.get(i);
                 if (!rowVF.isForeignEmpty("ID_ARTICLE") && rowVF.getForeignID("ID_ARTICLE") > rowVF.getForeign("ID_ARTICLE").getTable().getUndefinedID()) {
                     final int foreignIDArt = rowVF.getForeignID("ID_ARTICLE");
                     if (!mapStockSnap.containsKey(foreignIDArt)) {
                         mapStockSnap.put(foreignIDArt, new EtatStock(rowVF.getForeign("ID_ARTICLE")));
+                    }
                     EtatStock et = mapStockSnap.get(foreignIDArt);
                     et.setQte(et.getQte().add(new BigDecimal(rowVF.getFloat("QTE"))));
                     BigDecimal bigDecimal = BigDecimal.ZERO;
-                    if (tableStock.contains("PRICE")) {
+                    if (tableMvtStock.contains("PRICE")) {
                         bigDecimal = rowVF.getBigDecimal("PRICE");
+                    }
                     et.setPa(bigDecimal);
                     totalHT = totalHT.add(bigDecimal.multiply(new BigDecimal(rowVF.getFloat("QTE"), DecimalUtils.HIGH_PRECISION)));
+                }
+            }
+            // Ajout des articles sans mouvement de stock sur la période
+            final SQLTable tableStock = tableMvtStock.getTable("STOCK");
+            final SQLRowValues valsStock = new SQLRowValues(tableStock);
+            valsStock.putRowValues("ID_ARTICLE").putNulls("CODE", "NOM", "OBSOLETE");
+            valsStock.putNulls("QTE_REEL").putRowValues("ID_DEPOT_STOCK").putNulls("ID", "NOM", "CODE");
+            SQLRowValuesListFetcher fetcherStock = SQLRowValuesListFetcher.create(valsStock);
+            SQLTable tableArt = tableStock.getForeignTable("ID_ARTICLE");
+            fetcherStock.addSelTransf(new ITransformer<SQLSelect, SQLSelect>() {
+                @Override
+                public SQLSelect transformChecked(SQLSelect input) {
+                    Where w = new Where(tableStock.getField("ID_DEPOT_STOCK"), "=", depot.getID());
+                    w = w.and(new Where(input.getAlias(tableArt).getField("OBSOLETE"), "=", Boolean.FALSE));
+                    input.setWhere(w);
+                    System.err.println(input.asString());
+                    return input;
+                }
+            }, 0);
+            List<SQLRowValues> resultAllStock = fetcherStock.fetch();
+            for (SQLRowValues sqlRowValues : resultAllStock) {
+                final int foreignIDArt = sqlRowValues.getForeignID("ID_ARTICLE");
+                if (!mapStockSnap.containsKey(foreignIDArt)) {
+                    mapStockSnap.put(foreignIDArt, new EtatStock(sqlRowValues.getForeign("ID_ARTICLE")));
+                }
+            }
+            if (this.withAllProducts) {
+                // Ajout de tous les articles non obsoletes
+                final SQLRowValues valsArt = new SQLRowValues(tableArt);
+                valsArt.putNulls("CODE", "NOM", "OBSOLETE");
+                SQLRowValuesListFetcher fetcherArt = SQLRowValuesListFetcher.create(valsArt);
+                fetcherArt.addSelTransf(new ITransformer<SQLSelect, SQLSelect>() {
+                    @Override
+                    public SQLSelect transformChecked(SQLSelect input) {
+                        Where w = new Where(tableArt.getField("VIRTUEL"), "=", Boolean.FALSE);
+                        w = w.and(new Where(tableArt.getField("OBSOLETE"), "=", Boolean.FALSE));
+                        input.setWhere(w);
+                        System.err.println(input.asString());
+                        return input;
+                    }
+                }, 0);
+                List<SQLRowValues> resultArt = fetcherArt.fetch();
+                for (SQLRowValues sqlRowValues : resultArt) {
+                    final int idArt = sqlRowValues.getID();
+                    if (!mapStockSnap.containsKey(idArt)) {
+                        mapStockSnap.put(idArt, new EtatStock(sqlRowValues));
+                    }
+                }
+            }
             SQLRowValues rowVals = new SQLRowValues(tableEtatStock);
             rowVals.put("DATE", d);
             rowVals.put("MONTANT_HA", totalHT);
             rowVals.put("ID_DEPOT_STOCK", depot.getID());
             for (EtatStock etatItem : mapStockSnap.values()) {
                 SQLRowValues rowValsItem = new SQLRowValues(tableEtatStock.getTable("ETAT_STOCK_ELEMENT"));
                 rowValsItem.put("ID_ETAT_STOCK", rowVals);
                 rowValsItem.put("PA", etatItem.getPa());
                 rowValsItem.put("PV", etatItem.getPv());
                 rowValsItem.put("QTE", etatItem.getQte());
                 rowValsItem.put("T_PA", etatItem.getTotalPA());
                 rowValsItem.put("T_PV", etatItem.getTotalPV());
                 rowValsItem.put("CODE", etatItem.getArticle().getString("CODE"));
                 rowValsItem.put("NOM", etatItem.getArticle().getString("NOM"));
                 rowValsItem.put("ID_ARTICLE", etatItem.getArticle().getID());
+            }
             try {
                 rowVals.commit();
             } catch (SQLException e) {
                 ExceptionHandler.handle("Erreur lors de la création de l'état", e);
+            }
+        }
+    }
+}

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/EtatStockSnapshotCreator.java - Rev 177 → 182