WebSVN - OpenConcerto - Diff - Rev 174 and 182 - /trunk/OpenConcerto/src/org/openconcerto/erp/core/supplychain/stock/element/MouvementStockSQLElement.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.
 import org.openconcerto.erp.config.ComptaPropsConfiguration;
 import org.openconcerto.erp.core.common.element.ComptaSQLConfElement;
 import org.openconcerto.erp.core.sales.product.element.ReferenceArticleSQLElement;
 import org.openconcerto.erp.core.sales.product.element.UniteVenteArticleSQLElement;
+import org.openconcerto.erp.core.sales.product.model.ProductComponent;
 import org.openconcerto.erp.core.supplychain.order.component.CommandeSQLComponent;
 import org.openconcerto.erp.core.supplychain.order.ui.CommandeItemTable;
+import org.openconcerto.erp.core.supplychain.stock.element.StockItem.TypeStockMouvement;
 import org.openconcerto.erp.core.supplychain.supplier.component.MouvementStockSQLComponent;
 import org.openconcerto.erp.preferences.GestionArticleGlobalPreferencePanel;
 import org.openconcerto.erp.preferences.GestionCommercialeGlobalPreferencePanel;
 import org.openconcerto.sql.Configuration;
 import org.openconcerto.sql.element.SQLComponent;
 import org.openconcerto.sql.element.SQLElement;
 import org.openconcerto.sql.element.TreesOfSQLRows;
+import org.openconcerto.sql.model.ConnectionHandlerNoSetup;
+import org.openconcerto.sql.model.DBRoot;
 import org.openconcerto.sql.model.SQLBackgroundTableCache;
 import org.openconcerto.sql.model.SQLBase;
+import org.openconcerto.sql.model.SQLDataSource;
 import org.openconcerto.sql.model.SQLInjector;
 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.SQLSelect;
 import org.openconcerto.sql.model.SQLTable;
 import org.openconcerto.sql.model.Where;
 import org.openconcerto.sql.preferences.SQLPreferences;
 import org.openconcerto.sql.request.ListSQLRequest;
 import org.openconcerto.sql.users.UserManager;
+import org.openconcerto.sql.utils.SQLUtils;
 import org.openconcerto.sql.view.EditFrame;
 import org.openconcerto.sql.view.EditPanel;
 import org.openconcerto.sql.view.EditPanel.EditMode;
 import org.openconcerto.sql.view.list.RowValuesTableModel;
 import org.openconcerto.ui.FrameUtil;
+import org.openconcerto.utils.ExceptionHandler;
 import org.openconcerto.utils.ListMap;
 import java.math.BigDecimal;
+import java.math.RoundingMode;
 import java.sql.SQLException;
+import java.text.SimpleDateFormat;
 import java.util.ArrayList;
 import java.util.Collection;
+import java.util.Date;
 import java.util.List;
 import java.util.Map.Entry;
 import javax.swing.JOptionPane;
 import javax.swing.SwingUtilities;
+import org.apache.commons.dbutils.ResultSetHandler;
 public class MouvementStockSQLElement extends ComptaSQLConfElement {
     public MouvementStockSQLElement() {
         super("MOUVEMENT_STOCK", "un mouvement de stock", "mouvements de stock");
+    }
         // QTE_RECEPT_ATTENTE
         // Stock Th : inc/dec QTE_TH, inc/dec QTE_LIV_ATTENTE/inc/dec
         // QTE_RECEPT_ATTENTE
         final ListMap<SQLRow, SQLRowValues> map = new ListMap<>();
+        if (rowsMvt == null || rowsMvt.isEmpty()) {
+            return map;
+        }
+        final DBRoot root = rowsMvt.iterator().next().getTable().getDBRoot();
-        final SQLTable tableCommandeElement = Configuration.getInstance().getBase().getTable("COMMANDE_ELEMENT");
+        final SQLTable tableCommandeElement = root.getTable("COMMANDE_ELEMENT");
-        final SQLElement elementStock = Configuration.getInstance().getDirectory().getElement("STOCK");
+        final SQLTable tableStock = root.getTable("STOCK");
         for (SQLRow rowMvtStock : rowsMvt) {
             boolean retour = rowMvtStock.getString("SOURCE") == null || rowMvtStock.getString("SOURCE").startsWith("AVOIR_CLIENT");
             // Mise à jour des stocks
             if (rowMvtStock.getBoolean("REEL")) {
                 float qte = rowStock.getFloat("QTE_REEL");
                 float qteMvt = rowMvtStock.getFloat("QTE");
-                SQLRowValues rowVals = new SQLRowValues(elementStock.getTable());
+                SQLRowValues rowVals = new SQLRowValues(tableStock);
                 float qteNvlle;
                 float qteNvlleEnAttenteRecept = rowStock.getFloat("QTE_RECEPT_ATTENTE");
                 float qteNvlleEnAttenteExp = rowStock.getFloat("QTE_LIV_ATTENTE");
                 if (archive) {
                 rowVals.put("QTE_LIV_ATTENTE", qteNvlleEnAttenteExp);
                 try {
                     rowVals.update(rowStock.getID());
-                    SQLPreferences prefs = new SQLPreferences(getTable().getDBRoot());
+                    SQLPreferences prefs = new SQLPreferences(root);
                     boolean gestionStockMin = prefs.getBoolean(GestionArticleGlobalPreferencePanel.WARNING_STOCK_MIN, true);
                     if (!archive && gestionStockMin && rowStock.getObject("QTE_MIN") != null && qteNvlle < rowStock.getFloat("QTE_MIN")) {
                         // final float qteShow = qteNvlle;
                         SQLInjector inj = SQLInjector.getInjector(rowArticle.getTable(), tableCommandeElement);
+                }
             } else {
                 float qte = rowStock.getFloat("QTE_TH");
                 float qteMvt = rowMvtStock.getFloat("QTE");
-                SQLRowValues rowVals = new SQLRowValues(elementStock.getTable());
+                SQLRowValues rowVals = new SQLRowValues(tableStock);
                 float qteNvlle;
                 float qteNvlleEnAttenteRecept = rowStock.getFloat("QTE_RECEPT_ATTENTE");
                 float qteNvlleEnAttenteExp = rowStock.getFloat("QTE_LIV_ATTENTE");
                             SQLRowValues rowValsMatch = null;
                             int index = 0;
                             for (int i = 0; i < model.getRowCount(); i++) {
                                 final SQLRowValues rowValsCmdElt = model.getRowValuesAt(i);
+                                Number lineArticleNumber = rowValsCmdElt.contains("ID_ARTICLE") ? rowValsCmdElt.getNonEmptyForeignIDNumber("ID_ARTICLE") : null;
+                                Number lineToAddArticleNumber = rowValsElt.contains("ID_ARTICLE") ? rowValsElt.getNonEmptyForeignIDNumber("ID_ARTICLE") : null;
+                                if (lineToAddArticleNumber == null || lineArticleNumber == null || lineArticleNumber.equals(lineToAddArticleNumber)) {
-                                if (ReferenceArticleSQLElement.isReferenceEquals(rowValsCmdElt, rowValsElt)) {
+                                    if (ReferenceArticleSQLElement.isReferenceEquals(rowValsCmdElt, rowValsElt)) {
-                                    rowValsMatch = rowValsCmdElt;
+                                        rowValsMatch = rowValsCmdElt;
-                                    index = i;
+                                        index = i;
-                                    break;
+                                        break;
+                                    }
+                                }
+                            }
                             if (rowValsMatch != null) {
                                 int qte = rowValsMatch.getInt("QTE");
                                 BigDecimal qteUV = rowValsMatch.getBigDecimal("QTE_UNITAIRE");
         } else {
             System.err.println("Aucun mouvement associé, impossible de modifier ou d'accéder à la source de cette ecriture!");
+        }
+    }
+    public void transfertStock(BigDecimal qteReel, final Date dateValue, final SQLRow selectedRowArticle, final SQLRow selectedRowDepotDepart, final SQLRow selectedRowDepotArrivee,
+            final String labelTrStock) {
+        final boolean usePrice = getTable().contains("PRICE");
+        try {
+            SQLUtils.executeAtomic(selectedRowDepotDepart.getTable().getDBSystemRoot().getDataSource(), new ConnectionHandlerNoSetup<Object, SQLException>() {
+                @Override
+                public Object handle(SQLDataSource ds) throws SQLException {
+                    List<StockItem> stockItems = new ArrayList<StockItem>();
+                    List<String> multipleRequestsHundred = new ArrayList<String>(100);
+                    {
+                        // DEPART
+                        final SQLRowAccessor rowStockDepart = ProductComponent.findOrCreateStock(selectedRowArticle, selectedRowDepotDepart);
+                        StockItem item = new StockItem(selectedRowArticle, rowStockDepart);
+                        if (!item.isStockInit()) {
+                            SQLRowValues rowVals = new SQLRowValues(getTable().getTable("STOCK"));
+                            rowVals.put("ID_ARTICLE", selectedRowArticle.getID());
+                            rowVals.put("ID_DEPOT_STOCK", selectedRowDepotDepart.getID());
+                            rowVals.commit();
+                            selectedRowArticle.fetchValues();
+                            item = new StockItem(selectedRowArticle, rowStockDepart);
+                        }
+                        stockItems.add(item);
+                        double diff = -qteReel.doubleValue();
+                        item.updateQty(diff, TypeStockMouvement.REEL);
+                        multipleRequestsHundred.add(getMvtRequest(dateValue, BigDecimal.ZERO, diff, item, getLabel(labelTrStock, selectedRowDepotDepart, selectedRowDepotArrivee), true, usePrice));
+                        item.updateQty(diff, TypeStockMouvement.THEORIQUE);
+                        multipleRequestsHundred.add(getMvtRequest(dateValue, BigDecimal.ZERO, diff, item, getLabel(labelTrStock, selectedRowDepotDepart, selectedRowDepotArrivee), false, usePrice));
+                        multipleRequestsHundred.add(item.getUpdateRequest());
+                    }
+                    // ARRIVEE
+                    {
+                        final SQLRowAccessor rowStockArrivee = ProductComponent.findOrCreateStock(selectedRowArticle, selectedRowDepotArrivee);
+                        StockItem item = new StockItem(selectedRowArticle, rowStockArrivee);
+                        if (!item.isStockInit()) {
+                            SQLRowValues rowVals = new SQLRowValues(getTable().getTable("STOCK"));
+                            rowVals.put("ID_ARTICLE", selectedRowArticle.getID());
+                            rowVals.put("ID_DEPOT_STOCK", selectedRowDepotArrivee.getID());
+                            rowVals.commit();
+                            selectedRowArticle.fetchValues();
+                            item = new StockItem(selectedRowArticle, rowStockArrivee);
+                        }
+                        stockItems.add(item);
+                        double diff = qteReel.doubleValue();
+                        item.updateQty(diff, TypeStockMouvement.REEL);
+                        multipleRequestsHundred.add(getMvtRequest(dateValue, BigDecimal.ZERO, diff, item, getLabel(labelTrStock, selectedRowDepotDepart, selectedRowDepotArrivee), true, usePrice));
+                        item.updateQty(diff, TypeStockMouvement.THEORIQUE);
+                        multipleRequestsHundred.add(getMvtRequest(dateValue, BigDecimal.ZERO, diff, item, getLabel(labelTrStock, selectedRowDepotDepart, selectedRowDepotArrivee), false, usePrice));
+                        multipleRequestsHundred.add(item.getUpdateRequest());
+                    }
+                    final int size = multipleRequestsHundred.size();
+                    List<? extends ResultSetHandler> handlers = new ArrayList<ResultSetHandler>(size);
+                    for (int i = 0; i < size; i++) {
+                        handlers.add(null);
+                    }
+                    SQLUtils.executeMultiple(selectedRowArticle.getTable().getDBSystemRoot(), multipleRequestsHundred, handlers);
+                    final DBRoot root = getTable().getDBRoot();
+                    if (root.contains("ARTICLE_ELEMENT")) {
+                        // Mise à jour des stocks des nomenclatures
+                        ComposedItemStockUpdater comp = new ComposedItemStockUpdater(root, stockItems);
+                        comp.update();
+                    }
+                    return null;
+                }
+            });
+        } catch (SQLException e1) {
+            ExceptionHandler.handle("Stock update error", e1);
+        }
+    }
+    private String getLabel(String label, SQLRowAccessor fromDepot, SQLRowAccessor toDepot) {
+        return label + " de " + fromDepot.getString("NOM") + " vers " + toDepot.getString("NOM");
+    }
+    private String getMvtRequest(Date time, BigDecimal prc, double qteFinal, StockItem item, String label, boolean reel, boolean usePrice) {
+        String mvtStockTableQuoted = getTable().getSQLName().quote();
+        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
+        String mvtStockQuery = "INSERT INTO " + mvtStockTableQuoted + " (\"QTE\",\"DATE\",\"ID_ARTICLE\",\"ID_STOCK\",\"NOM\",\"REEL\",\"ORDRE\"";
+        if (usePrice && prc != null) {
+            mvtStockQuery += ",\"PRICE\"";
+        }
+        mvtStockQuery += ") VALUES(" + qteFinal + ",'" + dateFormat.format(time) + "'," + item.getArticle().getID() + "," + item.stock.getID() + ",'" + label + "'," + reel
+                + ", (SELECT (MAX(\"ORDRE\")+1) FROM " + mvtStockTableQuoted + ")";
+        if (usePrice && prc != null) {
+            mvtStockQuery += "," + prc.setScale(6, RoundingMode.HALF_UP).toString();
+        }
+        mvtStockQuery += ")";
+        return mvtStockQuery;
+    }
     @Override
     protected String createCode() {
         return createCodeOfPackage() + ".transaction";
+    }
+}

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/MouvementStockSQLElement.java - Rev 174 → 182