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
156 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
 
16
import org.openconcerto.sql.model.DBRoot;
17
import org.openconcerto.sql.model.SQLRow;
177 ilm 18
import org.openconcerto.sql.model.SQLRowAccessor;
156 ilm 19
import org.openconcerto.sql.model.SQLRowListRSH;
20
import org.openconcerto.sql.model.SQLRowValues;
21
import org.openconcerto.sql.model.SQLRowValuesListFetcher;
22
import org.openconcerto.sql.model.SQLSelect;
23
import org.openconcerto.sql.model.SQLTable;
24
import org.openconcerto.sql.model.Where;
25
import org.openconcerto.utils.DecimalUtils;
26
import org.openconcerto.utils.ExceptionHandler;
27
import org.openconcerto.utils.cc.ITransformer;
28
 
29
import java.math.BigDecimal;
30
import java.sql.SQLException;
31
import java.util.Date;
32
import java.util.HashMap;
33
import java.util.List;
34
import java.util.Map;
35
 
36
public class EtatStockSnapshotCreator {
37
 
38
    private final Date d;
39
    private final DBRoot root;
177 ilm 40
    private final SQLRowAccessor depot;
156 ilm 41
 
177 ilm 42
    public EtatStockSnapshotCreator(SQLRowAccessor depot, Date d, DBRoot root) {
156 ilm 43
        this.d = d;
177 ilm 44
        this.depot = depot;
156 ilm 45
        this.root = root;
46
    }
47
 
48
    public void create() {
49
        // Récupération des inventaires
50
        SQLTable tableEtatStock = this.root.getTable("ETAT_STOCK");
51
        SQLSelect sel = new SQLSelect();
52
        sel.addSelectStar(tableEtatStock);
53
        Where wEtat = new Where(tableEtatStock.getField("INVENTAIRE"), "=", Boolean.TRUE);
177 ilm 54
        wEtat = wEtat.and(new Where(tableEtatStock.getField("ID_DEPOT_STOCK"), "=", this.depot.getID()));
156 ilm 55
        sel.setWhere(wEtat);
56
        List<SQLRow> rowsEtatStock = SQLRowListRSH.execute(sel);
57
        Map<Integer, Integer> mapEtatStock = new HashMap<Integer, Integer>();
58
        for (SQLRow sqlRow : rowsEtatStock) {
59
            SQLTable tableMvtStock = this.root.getTable("MOUVEMENT_STOCK");
177 ilm 60
            SQLTable tableStock = this.root.getTable("STOCK");
156 ilm 61
            SQLSelect selMvt = new SQLSelect();
62
            selMvt.addSelect(tableMvtStock.getKey(), "MIN");
63
            Where wMvt = new Where(tableMvtStock.getField("OUVERTURE"), "=", Boolean.TRUE);
177 ilm 64
            wMvt = wMvt.and(new Where(tableMvtStock.getField("ID_ETAT_STOCK"), "=", sqlRow.getID()));
65
            wMvt = wMvt.and(new Where(tableMvtStock.getField("ID_STOCK"), "=", tableStock.getKey()));
66
            wMvt = wMvt.and(new Where(tableStock.getField("ID_DEPOT_STOCK"), "=", depot.getID()));
156 ilm 67
            selMvt.setWhere(wMvt);
68
            Integer idMvt = (Integer) tableMvtStock.getDBSystemRoot().getDataSource().executeScalar(selMvt.asString());
69
            if (idMvt != null) {
70
                mapEtatStock.put(sqlRow.getID(), idMvt);
71
            }
72
        }
73
 
74
        Map<Integer, EtatStock> mapStockSnap = new HashMap<Integer, EtatStock>();
75
        {
76
            final SQLTable tableStock = this.root.getTable("MOUVEMENT_STOCK");
77
 
78
            final SQLRowValues vals = new SQLRowValues(tableStock);
79
 
80
            vals.put("QTE", null);
81
            if (tableStock.contains("PRICE")) {
82
                vals.put("PRICE", null);
83
            }
84
            vals.put("ID_ARTICLE", null);
177 ilm 85
            vals.putRowValues("ID_STOCK").putNulls("QTE_REEL").putRowValues("ID_DEPOT_STOCK").putNulls("ID", "NOM", "CODE");
86
 
156 ilm 87
            // Calendar cal0116 = Calendar.getInstance();
88
            // cal0116.set(2016, Calendar.JANUARY, 1, 0, 0, 0);
89
            // final Date dateDeb = cal0116.getTime();
90
 
91
            // Récupération du dernier etat de stock
92
            SQLSelect selEtatD = new SQLSelect();
93
            selEtatD.addSelectStar(tableEtatStock);
94
            Where wEtatD = new Where(tableEtatStock.getField("INVENTAIRE"), "=", Boolean.TRUE);
177 ilm 95
            wEtatD = wEtatD.and(new Where(tableEtatStock.getField("ID_DEPOT_STOCK"), "=", this.depot.getID()));
156 ilm 96
            selEtatD.setWhere(wEtatD);
97
            List<SQLRow> rowsEtatStockD = SQLRowListRSH.execute(selEtatD);
98
            SQLRow rowEtatStockDeb = null;
99
            for (SQLRow sqlRow : rowsEtatStockD) {
100
 
101
                if (sqlRow.getDate("DATE").getTime().before(this.d)) {
102
                    if (rowEtatStockDeb == null || rowEtatStockDeb.getDate("DATE").before(sqlRow.getDate("DATE"))) {
103
                        rowEtatStockDeb = sqlRow;
104
                    }
105
                }
106
 
107
            }
108
            final Date dateDeb;
109
            final Integer idMvtStockDeb;
110
            if (rowEtatStockDeb != null) {
111
                dateDeb = rowEtatStockDeb.getDate("DATE").getTime();
112
                idMvtStockDeb = mapEtatStock.get(rowEtatStockDeb.getID());
113
            } else {
114
                dateDeb = null;
115
                idMvtStockDeb = null;
116
            }
117
 
118
            final SQLRowValuesListFetcher fetcher = SQLRowValuesListFetcher.create(vals);
119
            fetcher.setSelTransf(new ITransformer<SQLSelect, SQLSelect>() {
120
 
121
                @Override
122
                public SQLSelect transformChecked(SQLSelect sel) {
123
 
124
                    Where w = (new Where(tableStock.getField("DATE"), "<=", d));
125
 
126
                    if (dateDeb != null) {
127
                        w = w.and(new Where(tableStock.getField("DATE"), ">=", dateDeb));
128
                        w = w.and(new Where(tableStock.getKey(), ">=", idMvtStockDeb));
129
                        w = w.and(new Where(tableStock.getField("CLOTURE"), "!=", Boolean.TRUE));
130
                    }
131
                    w = w.and(new Where(tableStock.getField("REEL"), "=", Boolean.TRUE));
177 ilm 132
                    w = w.and(new Where(sel.getJoin(tableStock.getField("ID_STOCK")).getJoinedTable().getField("ID_DEPOT_STOCK"), "=", depot.getID()));
156 ilm 133
 
134
                    sel.setWhere(w);
135
                    return sel;
136
                }
137
            });
138
 
139
            List<SQLRowValues> list = fetcher.fetch();
140
 
141
            BigDecimal totalHT = BigDecimal.ZERO;
142
            for (int i = 0; i < list.size(); i++) {
143
                SQLRowValues rowVF = list.get(i);
144
                if (!rowVF.isForeignEmpty("ID_ARTICLE") && rowVF.getForeignID("ID_ARTICLE") > rowVF.getForeign("ID_ARTICLE").getTable().getUndefinedID()) {
145
                    final int foreignIDArt = rowVF.getForeignID("ID_ARTICLE");
146
                    if (!mapStockSnap.containsKey(foreignIDArt)) {
147
                        mapStockSnap.put(foreignIDArt, new EtatStock(rowVF.getForeign("ID_ARTICLE")));
148
                    }
149
                    EtatStock et = mapStockSnap.get(foreignIDArt);
150
                    et.setQte(et.getQte().add(new BigDecimal(rowVF.getFloat("QTE"))));
151
                    BigDecimal bigDecimal = BigDecimal.ZERO;
152
                    if (tableStock.contains("PRICE")) {
153
                        bigDecimal = rowVF.getBigDecimal("PRICE");
154
                    }
155
                    et.setPa(bigDecimal);
156
                    totalHT = totalHT.add(bigDecimal.multiply(new BigDecimal(rowVF.getFloat("QTE"), DecimalUtils.HIGH_PRECISION)));
157
                }
158
            }
159
 
160
            SQLRowValues rowVals = new SQLRowValues(tableEtatStock);
161
            rowVals.put("DATE", d);
162
            rowVals.put("MONTANT_HA", totalHT);
177 ilm 163
            rowVals.put("ID_DEPOT_STOCK", depot.getID());
156 ilm 164
 
165
            for (EtatStock etatItem : mapStockSnap.values()) {
166
                SQLRowValues rowValsItem = new SQLRowValues(tableEtatStock.getTable("ETAT_STOCK_ELEMENT"));
167
                rowValsItem.put("ID_ETAT_STOCK", rowVals);
168
                rowValsItem.put("PA", etatItem.getPa());
169
                rowValsItem.put("PV", etatItem.getPv());
170
                rowValsItem.put("QTE", etatItem.getQte());
171
                rowValsItem.put("T_PA", etatItem.getTotalPA());
172
                rowValsItem.put("T_PV", etatItem.getTotalPV());
173
                rowValsItem.put("CODE", etatItem.getArticle().getString("CODE"));
174
                rowValsItem.put("NOM", etatItem.getArticle().getString("NOM"));
175
                rowValsItem.put("ID_ARTICLE", etatItem.getArticle().getID());
176
            }
177
            try {
178
                rowVals.commit();
179
            } catch (SQLException e) {
180
                ExceptionHandler.handle("Erreur lors de la création de l'état", e);
181
            }
182
        }
183
    }
184
 
185
}