OpenConcerto

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

svn://code.openconcerto.org/openconcerto

Rev

Rev 156 | Details | Compare with Previous | Last modification | View Log | RSS feed

Rev Author Line No. Line
83 ilm 1
/*
2
 * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS HEADER.
3
 *
4
 * Copyright 2011 OpenConcerto, by ILM Informatique. All rights reserved.
5
 *
6
 * The contents of this file are subject to the terms of the GNU General Public License Version 3
7
 * only ("GPL"). You may not use this file except in compliance with the License. You can obtain a
8
 * copy of the License at http://www.gnu.org/licenses/gpl-3.0.html See the License for the specific
9
 * language governing permissions and limitations under the License.
10
 *
11
 * When distributing the software, include this License Header Notice in each file.
12
 */
13
 
14
 package org.openconcerto.erp.core.supplychain.stock.element;
15
 
93 ilm 16
import org.openconcerto.erp.core.sales.product.model.ProductComponent;
17
import org.openconcerto.erp.core.sales.product.model.ProductHelper;
18
import org.openconcerto.erp.core.supplychain.stock.element.StockItem.TypeStockMouvement;
94 ilm 19
import org.openconcerto.sql.model.ConnectionHandlerNoSetup;
83 ilm 20
import org.openconcerto.sql.model.DBRoot;
94 ilm 21
import org.openconcerto.sql.model.SQLDataSource;
90 ilm 22
import org.openconcerto.sql.model.SQLRow;
83 ilm 23
import org.openconcerto.sql.model.SQLRowAccessor;
24
import org.openconcerto.sql.model.SQLRowValues;
25
import org.openconcerto.sql.model.SQLRowValuesListFetcher;
26
import org.openconcerto.sql.model.SQLSelect;
27
import org.openconcerto.sql.model.SQLTable;
174 ilm 28
import org.openconcerto.sql.model.SQLTableEvent;
29
import org.openconcerto.sql.model.SQLTableEvent.Mode;
83 ilm 30
import org.openconcerto.sql.model.Where;
31
import org.openconcerto.sql.utils.SQLUtils;
94 ilm 32
import org.openconcerto.utils.DecimalUtils;
33
import org.openconcerto.utils.ExceptionHandler;
90 ilm 34
import org.openconcerto.utils.ListMap;
83 ilm 35
import org.openconcerto.utils.RTInterruptedException;
94 ilm 36
import org.openconcerto.utils.Tuple3;
83 ilm 37
import org.openconcerto.utils.cc.ITransformer;
38
 
142 ilm 39
import java.awt.GraphicsEnvironment;
40
import java.io.IOException;
41
import java.math.BigDecimal;
42
import java.math.RoundingMode;
43
import java.sql.SQLException;
44
import java.text.SimpleDateFormat;
45
import java.util.ArrayList;
46
import java.util.Date;
47
import java.util.HashMap;
48
import java.util.List;
49
import java.util.Map;
50
 
51
import javax.swing.JOptionPane;
52
import javax.swing.SwingUtilities;
53
 
54
import org.apache.commons.dbutils.ResultSetHandler;
55
 
83 ilm 56
public class StockItemsUpdater {
57
 
58
    private final StockLabel label;
59
    private final List<? extends SQLRowAccessor> items;
93 ilm 60
    private final TypeStockUpdate type;
83 ilm 61
    private final boolean createMouvementStock;
62
    private final SQLRowAccessor rowSource;
156 ilm 63
    private boolean resetStockTH = false;
83 ilm 64
 
93 ilm 65
    private boolean headless = false;
83 ilm 66
 
93 ilm 67
    public static enum TypeStockUpdate {
83 ilm 68
 
93 ilm 69
        VIRTUAL_RECEPT(true, TypeStockMouvement.THEORIQUE), REAL_RECEPT(true, TypeStockMouvement.REEL), VIRTUAL_DELIVER(false, TypeStockMouvement.THEORIQUE), REAL_DELIVER(false,
132 ilm 70
                TypeStockMouvement.REEL), REAL_VIRTUAL_RECEPT(true,
71
                        TypeStockMouvement.REEL_THEORIQUE), RETOUR_AVOIR_CLIENT(true, TypeStockMouvement.RETOUR), REAL_VIRTUAL_DELIVER(false, TypeStockMouvement.REEL_THEORIQUE);
83 ilm 72
 
93 ilm 73
        private final boolean entry;
74
        private final TypeStockMouvement type;
75
 
76
        /**
77
         *
78
         * @param entry
79
         */
80
        TypeStockUpdate(boolean entry, TypeStockMouvement type) {
83 ilm 81
            this.entry = entry;
93 ilm 82
            this.type = type;
83 ilm 83
        }
84
 
85
        public boolean isEntry() {
86
            return entry;
87
        }
88
 
93 ilm 89
        public TypeStockMouvement getType() {
90
            return type;
83 ilm 91
        }
92
    };
93
 
93 ilm 94
    public StockItemsUpdater(StockLabel label, SQLRowAccessor rowSource, List<? extends SQLRowAccessor> items, TypeStockUpdate t) {
83 ilm 95
        this(label, rowSource, items, t, true);
96
    }
97
 
93 ilm 98
    public StockItemsUpdater(StockLabel label, SQLRowAccessor rowSource, List<? extends SQLRowAccessor> items, TypeStockUpdate t, boolean createMouvementStock) {
83 ilm 99
        this.label = label;
100
        this.items = items;
101
        this.type = t;
102
        this.createMouvementStock = createMouvementStock;
103
        this.rowSource = rowSource;
93 ilm 104
        this.headless = GraphicsEnvironment.isHeadless();
83 ilm 105
    }
106
 
93 ilm 107
    public void setHeadless(boolean headless) {
108
        this.headless = headless;
109
    }
110
 
94 ilm 111
    List<Tuple3<SQLRowAccessor, Integer, BigDecimal>> reliquat = new ArrayList<Tuple3<SQLRowAccessor, Integer, BigDecimal>>();
112
 
113
    public void addReliquat(SQLRowAccessor article, int qte, BigDecimal qteUnit) {
114
        reliquat.add(Tuple3.create(article, qte, qteUnit));
115
    }
116
 
83 ilm 117
    List<String> requests = new ArrayList<String>();
118
 
119
    public void update() throws SQLException {
120
        final SQLTable stockTable = this.rowSource.getTable().getTable("STOCK");
121
 
122
        if (this.createMouvementStock) {
123
            clearExistingMvt(this.rowSource);
124
        }
125
 
126
        // Mise à jour des stocks des articles non composés
127
        List<StockItem> stockItems = fetch();
128
 
90 ilm 129
        final ListMap<SQLRow, SQLRowValues> cmd = new ListMap<SQLRow, SQLRowValues>();
130
 
83 ilm 131
        for (StockItem stockItem : stockItems) {
94 ilm 132
 
83 ilm 133
            if (stockItem.isStockInit()) {
134
                requests.add(stockItem.getUpdateRequest());
135
            } else {
136
                SQLRowValues rowVals = new SQLRowValues(stockTable);
137
                rowVals.put("QTE_REEL", stockItem.getRealQty());
138
                rowVals.put("QTE_TH", stockItem.getVirtualQty());
139
                rowVals.put("QTE_LIV_ATTENTE", stockItem.getDeliverQty());
140
                rowVals.put("QTE_RECEPT_ATTENTE", stockItem.getReceiptQty());
156 ilm 141
                rowVals.put("ID_ARTICLE", stockItem.getArticle().getID());
142
                rowVals.put("ID_DEPOT_STOCK", stockItem.stock.getForeignID("ID_DEPOT_STOCK"));
143
                rowVals.commit();
144
                if (stockItem.getArticle().getForeignID("ID_DEPOT_STOCK") == stockItem.stock.getForeignID("ID_DEPOT_STOCK")) {
145
                    SQLRowValues rowValsArt = stockItem.getArticle().createEmptyUpdateRow();
146
                    rowValsArt.put("ID_STOCK", rowVals);
147
                    rowValsArt.commit();
148
                }
83 ilm 149
            }
142 ilm 150
            if (!this.type.isEntry()) {
151
                stockItem.fillCommandeFournisseur(cmd);
152
            }
83 ilm 153
        }
154
 
94 ilm 155
        final List<? extends ResultSetHandler> handlers = new ArrayList<ResultSetHandler>(requests.size());
83 ilm 156
        for (String s : requests) {
157
            handlers.add(null);
158
        }
94 ilm 159
        try {
160
            SQLUtils.executeAtomic(stockTable.getDBSystemRoot().getDataSource(), new ConnectionHandlerNoSetup<Object, IOException>() {
161
                @Override
162
                public Object handle(SQLDataSource ds) throws SQLException, IOException {
163
                    SQLUtils.executeMultiple(stockTable.getDBSystemRoot(), requests, handlers);
174 ilm 164
                    // FIXME FIRE ONLY CHANGED ROWS
165
                    stockTable.fire(new SQLTableEvent(stockTable, SQLRow.NONEXISTANT_ID, Mode.ROW_UPDATED));
94 ilm 166
                    return null;
167
                }
168
            });
169
        } catch (IOException e) {
170
            ExceptionHandler.handle("Erreur de la mise à jour des stocks!", e);
171
        }
83 ilm 172
 
173
        final DBRoot root = this.rowSource.getTable().getDBRoot();
174
        if (root.contains("ARTICLE_ELEMENT")) {
93 ilm 175
            // Mise à jour des stocks des nomenclatures
83 ilm 176
            ComposedItemStockUpdater comp = new ComposedItemStockUpdater(root, stockItems);
177
            comp.update();
178
        }
179
 
90 ilm 180
        // FIXME Créer une interface de saisie de commande article en dessous du seuil mini de stock
93 ilm 181
        if (!headless && cmd.size() > 0) {
90 ilm 182
            String msg = "Les articles suivants sont inférieurs au stock minimum : \n";
183
            for (SQLRow row : cmd.keySet()) {
184
                for (SQLRowValues rowVals : cmd.get(row)) {
185
                    msg += rowVals.getString("CODE") + " " + rowVals.getString("NOM") + "\n";
186
                }
187
            }
188
            final String msgFinal = msg;
189
            SwingUtilities.invokeLater(new Runnable() {
190
 
191
                @Override
192
                public void run() {
193
                    JOptionPane.showMessageDialog(null, msgFinal, "Alerte de stock minimum", JOptionPane.WARNING_MESSAGE);
194
                }
195
            });
196
        }
197
 
83 ilm 198
    }
199
 
200
    /**
201
     * Suppression des anciens mouvements
202
     *
203
     * @param rowSource
204
     * @throws SQLException
205
     * @throws RTInterruptedException
206
     */
207
    private void clearExistingMvt(SQLRowAccessor rowSource) throws RTInterruptedException, SQLException {
208
 
209
        List<String> multipleRequests = new ArrayList<String>();
210
 
211
        final SQLTable table = this.rowSource.getTable().getTable("MOUVEMENT_STOCK");
212
        SQLRowValues rowVals = new SQLRowValues(table);
213
        rowVals.put("QTE", null);
214
        rowVals.put("REEL", null);
215
        SQLRowValues rowValsArt = new SQLRowValues(this.rowSource.getTable().getTable("ARTICLE"));
216
        SQLRowValues rowValsStock = new SQLRowValues(this.rowSource.getTable().getTable("STOCK"));
217
        rowValsStock.put("QTE_REEL", null);
218
        rowValsStock.put("QTE_TH", null);
219
        rowValsStock.put("QTE_RECEPT_ATTENTE", null);
220
        rowValsStock.put("QTE_LIV_ATTENTE", null);
221
 
156 ilm 222
        rowVals.put("ID_STOCK", rowValsStock);
83 ilm 223
        rowVals.put("ID_ARTICLE", rowValsArt);
224
 
225
        SQLRowValuesListFetcher fetcher = SQLRowValuesListFetcher.create(rowVals);
226
        fetcher.setSelTransf(new ITransformer<SQLSelect, SQLSelect>() {
227
 
228
            @Override
229
            public SQLSelect transformChecked(SQLSelect input) {
230
                Where w = new Where(table.getField("SOURCE"), "=", StockItemsUpdater.this.rowSource.getTable().getName());
231
                w = w.and(new Where(table.getField("IDSOURCE"), "=", StockItemsUpdater.this.rowSource.getID()));
232
                input.setWhere(w);
233
                return input;
234
            }
235
        });
236
 
142 ilm 237
        // On stocke les items pour le calcul total des stocks (sinon le calcul est faux si
238
        // l'article apparait plusieurs fois
239
        // ou si
240
        // on archive un mvt reel et theorique)
241
        Map<Number, StockItem> items = new HashMap<Number, StockItem>();
83 ilm 242
        List<SQLRowValues> result = fetcher.fetch();
243
        for (SQLRowValues sqlRowValues : result) {
142 ilm 244
            final StockItem item;
156 ilm 245
            if (!items.containsKey(sqlRowValues.getForeignIDNumber("ID_STOCK"))) {
246
                item = new StockItem(sqlRowValues.getForeign("ID_ARTICLE"), sqlRowValues.getForeign("ID_STOCK"));
247
                items.put(sqlRowValues.getForeignIDNumber("ID_STOCK"), item);
142 ilm 248
            } else {
156 ilm 249
                item = items.get(sqlRowValues.getForeignIDNumber("ID_STOCK"));
142 ilm 250
            }
93 ilm 251
            final TypeStockMouvement t;
83 ilm 252
            if (sqlRowValues.getBoolean("REEL")) {
93 ilm 253
                t = TypeStockMouvement.REEL;
83 ilm 254
            } else {
93 ilm 255
                t = TypeStockMouvement.THEORIQUE;
83 ilm 256
            }
257
            item.updateQty(sqlRowValues.getFloat("QTE"), t, true);
258
            String req = "UPDATE " + sqlRowValues.getTable().getSQLName().quote() + " SET \"ARCHIVE\"=1 WHERE \"ID\"=" + sqlRowValues.getID();
259
            multipleRequests.add(req);
260
            multipleRequests.add(item.getUpdateRequest());
261
        }
262
 
263
        List<? extends ResultSetHandler> handlers = new ArrayList<ResultSetHandler>(multipleRequests.size());
264
        for (String s : multipleRequests) {
265
            handlers.add(null);
266
        }
267
        SQLUtils.executeMultiple(table.getDBSystemRoot(), multipleRequests, handlers);
268
    }
269
 
270
    /**
93 ilm 271
     * Récupére les stocks associés aux articles non composés (inclus les fils des nomenclatures) et
272
     * les met à jour
83 ilm 273
     *
274
     * @return la liste des stocks à jour
275
     */
276
    private List<StockItem> fetch() {
277
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
156 ilm 278
        Map<Number, StockItem> stockItems = new HashMap<Number, StockItem>();
279
 
93 ilm 280
        String mvtStockTableQuoted = rowSource.getTable().getTable("MOUVEMENT_STOCK").getSQLName().quote();
83 ilm 281
 
174 ilm 282
        ProductHelper helper = new ProductHelper(rowSource.getTable().getDBRoot());
93 ilm 283
        // Liste des éléments à mettre à jour
284
        List<ProductComponent> productComponents = new ArrayList<ProductComponent>();
174 ilm 285
        helper.fillProductComponent(this.items, productComponents, 1, 0, 1);
93 ilm 286
        // for (int i = 0; i < items.size(); i++) {
287
        // SQLRowAccessor r = items.get(i);
288
        //
289
        // // On ne calcul pas les stocks pour les éléments ayant des fils (le mouvement de stock
290
        // // des fils impactera les stocks automatiquement)
291
        // if (r.getTable().contains("NIVEAU")) {
292
        // if (i + 1 < items.size()) {
293
        // SQLRowAccessor rNext = items.get(i + 1);
294
        // if (rNext.getInt("NIVEAU") > r.getInt("NIVEAU")) {
295
        // continue;
296
        // }
297
        // }
298
        // }
299
        // if (!r.isForeignEmpty("ID_ARTICLE")) {
300
        // productComponents.add(ProductComponent.createFrom(r));
301
        // }
302
        // }
83 ilm 303
 
93 ilm 304
        // Liste des articles non composés à mettre à jour (avec les fils des nomenclatures)
305
        List<ProductComponent> boms = helper.getChildWithQtyFrom(productComponents);
83 ilm 306
 
93 ilm 307
        for (ProductComponent productComp : boms) {
308
 
132 ilm 309
            if (productComp.getProduct().getBoolean("GESTION_STOCK") && productComp.getQty().signum() != 0) {
156 ilm 310
                final StockItem stockItem;
311
                if (!stockItems.containsKey(productComp.getStock().getID())) {
312
                    stockItem = new StockItem(productComp.getProduct(), productComp.getStock());
313
                    stockItems.put(productComp.getStock().getID(), stockItem);
314
                } else {
315
                    stockItem = stockItems.get(productComp.getStock().getID());
316
                }
93 ilm 317
                double qteFinal = productComp.getQty().doubleValue();
94 ilm 318
 
319
                // reliquat
320
                for (Tuple3<SQLRowAccessor, Integer, BigDecimal> t : reliquat) {
321
                    if (stockItem.getArticle() != null && stockItem.getArticle().equalsAsRow(t.get0())) {
322
                        double qteFinalReliquat = t.get2().multiply(new BigDecimal(t.get1()), DecimalUtils.HIGH_PRECISION).doubleValue();
323
                        qteFinal -= qteFinalReliquat;
324
                    }
325
                }
93 ilm 326
                if (!this.type.isEntry()) {
327
                    qteFinal = -qteFinal;
328
                }
329
 
330
                stockItem.updateQty(qteFinal, this.type.getType());
156 ilm 331
 
93 ilm 332
                if (this.createMouvementStock) {
333
                    final Date time = this.rowSource.getDate("DATE").getTime();
334
                    BigDecimal prc = productComp.getPRC(time);
132 ilm 335
                    if (this.type.getType() == TypeStockMouvement.REEL || this.type.getType() == TypeStockMouvement.REEL_THEORIQUE || this.type.getType() == TypeStockMouvement.RETOUR) {
156 ilm 336
                        String mvtStockQuery = "INSERT INTO " + mvtStockTableQuoted + " (\"QTE\",\"DATE\",\"ID_ARTICLE\",\"ID_STOCK\",\"SOURCE\",\"IDSOURCE\",\"NOM\",\"REEL\",\"ORDRE\"";
93 ilm 337
 
338
                        if (prc != null) {
339
                            mvtStockQuery += ",\"PRICE\"";
340
                        }
341
 
156 ilm 342
                        mvtStockQuery += ") VALUES(" + qteFinal + ",'" + dateFormat.format(time) + "'," + productComp.getProduct().getID() + "," + productComp.getStock().getID() + ",'"
343
                                + this.rowSource.getTable().getName() + "'," + this.rowSource.getID() + ",'" + this.label.getLabel(this.rowSource, productComp.getProduct())
344
                                + "',true, (SELECT (MAX(\"ORDRE\")+1) FROM " + mvtStockTableQuoted + ")";
93 ilm 345
                        if (prc != null) {
346
                            mvtStockQuery += "," + prc.setScale(6, RoundingMode.HALF_UP).toString();
347
                        }
348
                        mvtStockQuery += ")";
349
                        this.requests.add(mvtStockQuery);
83 ilm 350
                    }
132 ilm 351
                    if (this.type.getType() == TypeStockMouvement.THEORIQUE || this.type.getType() == TypeStockMouvement.REEL_THEORIQUE || this.type.getType() == TypeStockMouvement.RETOUR) {
156 ilm 352
                        String mvtStockQuery = "INSERT INTO " + mvtStockTableQuoted + " (\"QTE\",\"DATE\",\"ID_ARTICLE\",\"ID_STOCK\",\"SOURCE\",\"IDSOURCE\",\"NOM\",\"REEL\",\"ORDRE\"";
93 ilm 353
                        if (prc != null) {
354
                            mvtStockQuery += ",\"PRICE\"";
355
                        }
356
 
156 ilm 357
                        mvtStockQuery += ") VALUES(" + qteFinal + ",'" + dateFormat.format(time) + "'," + productComp.getProduct().getID() + "," + productComp.getStock().getID() + ",'"
358
                                + this.rowSource.getTable().getName() + "'," + this.rowSource.getID() + ",'" + this.label.getLabel(this.rowSource, productComp.getProduct())
359
                                + "',false, (SELECT (MAX(\"ORDRE\")+1) FROM " + mvtStockTableQuoted + ")";
93 ilm 360
                        if (prc != null) {
361
                            mvtStockQuery += "," + prc.setScale(6, RoundingMode.HALF_UP).toString();
362
                        }
363
                        mvtStockQuery += ")";
83 ilm 364
                        this.requests.add(mvtStockQuery);
365
                    }
366
                }
367
            }
368
        }
93 ilm 369
 
156 ilm 370
        return new ArrayList<StockItem>(stockItems.values());
83 ilm 371
    }
156 ilm 372
 
373
    public void setResetStockTH(boolean resetStockTH) {
374
        this.resetStockTH = resetStockTH;
375
    }
83 ilm 376
}