OpenConcerto

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

svn://code.openconcerto.org/openconcerto

Rev

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