| 156 |
ilm |
1 |
/*
|
|
|
2 |
* DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS HEADER.
|
|
|
3 |
*
|
| 182 |
ilm |
4 |
* Copyright 2011-2019 OpenConcerto, by ILM Informatique. All rights reserved.
|
| 156 |
ilm |
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.erp.core.sales.product.model.ProductComponent;
|
|
|
17 |
import org.openconcerto.erp.core.sales.product.model.ProductHelper;
|
|
|
18 |
import org.openconcerto.erp.core.sales.product.model.ProductHelper.SupplierPriceField;
|
|
|
19 |
import org.openconcerto.erp.importer.ArrayTableModel;
|
|
|
20 |
import org.openconcerto.erp.importer.DataImporter;
|
| 182 |
ilm |
21 |
import org.openconcerto.erp.preferences.GestionArticleGlobalPreferencePanel;
|
| 156 |
ilm |
22 |
import org.openconcerto.sql.Configuration;
|
| 182 |
ilm |
23 |
import org.openconcerto.sql.element.SQLElement;
|
| 156 |
ilm |
24 |
import org.openconcerto.sql.model.DBRoot;
|
|
|
25 |
import org.openconcerto.sql.model.SQLRow;
|
|
|
26 |
import org.openconcerto.sql.model.SQLRowAccessor;
|
| 182 |
ilm |
27 |
import org.openconcerto.sql.model.SQLRowListRSH;
|
| 156 |
ilm |
28 |
import org.openconcerto.sql.model.SQLRowValues;
|
|
|
29 |
import org.openconcerto.sql.model.SQLRowValuesCluster.StoreMode;
|
|
|
30 |
import org.openconcerto.sql.model.SQLRowValuesListFetcher;
|
| 182 |
ilm |
31 |
import org.openconcerto.sql.model.SQLSelect;
|
| 156 |
ilm |
32 |
import org.openconcerto.sql.model.SQLTable;
|
| 182 |
ilm |
33 |
import org.openconcerto.sql.preferences.SQLPreferences;
|
| 156 |
ilm |
34 |
import org.openconcerto.sql.utils.SQLUtils;
|
| 177 |
ilm |
35 |
import org.openconcerto.utils.Tuple2;
|
| 182 |
ilm |
36 |
import org.openconcerto.utils.Tuple3;
|
| 156 |
ilm |
37 |
|
|
|
38 |
import java.io.File;
|
|
|
39 |
import java.io.IOException;
|
|
|
40 |
import java.math.BigDecimal;
|
|
|
41 |
import java.sql.SQLException;
|
|
|
42 |
import java.util.ArrayList;
|
| 182 |
ilm |
43 |
import java.util.Arrays;
|
| 156 |
ilm |
44 |
import java.util.Calendar;
|
|
|
45 |
import java.util.Collection;
|
|
|
46 |
import java.util.Date;
|
|
|
47 |
import java.util.HashMap;
|
|
|
48 |
import java.util.List;
|
|
|
49 |
import java.util.Map;
|
| 182 |
ilm |
50 |
import java.util.Map.Entry;
|
| 156 |
ilm |
51 |
import java.util.Set;
|
|
|
52 |
|
| 182 |
ilm |
53 |
import javax.swing.JOptionPane;
|
|
|
54 |
|
| 156 |
ilm |
55 |
import org.apache.commons.dbutils.ResultSetHandler;
|
|
|
56 |
|
|
|
57 |
public class InventaireFromEtatStockImporter {
|
|
|
58 |
|
| 177 |
ilm |
59 |
private Map<String, SQLRowValues> kits = new HashMap<String, SQLRowValues>();
|
|
|
60 |
private List<String> codeKits = new ArrayList<String>();
|
|
|
61 |
private SQLRowAccessor depot;
|
| 182 |
ilm |
62 |
private static String FAMILLE = "Famille";
|
|
|
63 |
private static String CODE = "Code";
|
|
|
64 |
private static String NOM = "Nom";
|
|
|
65 |
private static String TAILLE = "Taille";
|
|
|
66 |
private static String COULEUR = "Couleur";
|
|
|
67 |
private static String QTE = "Qté réelle relevée";
|
|
|
68 |
private static String QTE_OPENCONCERTO = "QTE OpenConcerto";
|
|
|
69 |
private final Map<String, Integer> mapCouleur = new HashMap<String, Integer>();
|
|
|
70 |
private final Map<String, Integer> mapTaille = new HashMap<String, Integer>();
|
|
|
71 |
private final DBRoot root;
|
|
|
72 |
private final SQLElement artElt;
|
| 156 |
ilm |
73 |
|
| 182 |
ilm |
74 |
public InventaireFromEtatStockImporter(SQLElement articleElt, SQLRowAccessor depot) {
|
| 177 |
ilm |
75 |
this.depot = depot;
|
| 182 |
ilm |
76 |
this.root = articleElt.getTable().getDBRoot();
|
|
|
77 |
this.artElt = articleElt;
|
|
|
78 |
{
|
|
|
79 |
SQLSelect sel = new SQLSelect();
|
|
|
80 |
sel.addSelect(articleElt.getTable().getTable("ARTICLE_DECLINAISON_COULEUR").getKey());
|
|
|
81 |
sel.addSelect(articleElt.getTable().getTable("ARTICLE_DECLINAISON_COULEUR").getField("NOM"));
|
|
|
82 |
|
|
|
83 |
List<SQLRow> l = SQLRowListRSH.execute(sel);
|
|
|
84 |
|
|
|
85 |
for (SQLRow sqlRow : l) {
|
|
|
86 |
mapCouleur.put(sqlRow.getString("NOM").trim(), sqlRow.getID());
|
|
|
87 |
}
|
|
|
88 |
}
|
|
|
89 |
|
|
|
90 |
{
|
|
|
91 |
SQLSelect sel = new SQLSelect();
|
|
|
92 |
sel.addSelect(articleElt.getTable().getTable("ARTICLE_DECLINAISON_TAILLE").getKey());
|
|
|
93 |
sel.addSelect(articleElt.getTable().getTable("ARTICLE_DECLINAISON_TAILLE").getField("NOM"));
|
|
|
94 |
|
|
|
95 |
List<SQLRow> l = SQLRowListRSH.execute(sel);
|
|
|
96 |
|
|
|
97 |
for (SQLRow sqlRow : l) {
|
|
|
98 |
mapTaille.put(sqlRow.getString("NOM").trim(), sqlRow.getID());
|
|
|
99 |
}
|
|
|
100 |
}
|
| 156 |
ilm |
101 |
}
|
|
|
102 |
|
|
|
103 |
public void importArticles(File file, DBRoot root) throws IOException, SQLException {
|
|
|
104 |
|
|
|
105 |
final SQLTable table = root.findTable("ARTICLE");
|
|
|
106 |
final SQLTable tableArtElt = root.findTable("ARTICLE_ELEMENT");
|
|
|
107 |
|
| 182 |
ilm |
108 |
// Récupération des couples articles/stocks existant
|
|
|
109 |
fillArticles();
|
| 156 |
ilm |
110 |
|
| 182 |
ilm |
111 |
// Matching des colonnes
|
|
|
112 |
Map<String, Integer> columnMapping = new HashMap<String, Integer>();
|
|
|
113 |
columnMapping.put(CODE, null);
|
|
|
114 |
columnMapping.put(NOM, null);
|
|
|
115 |
columnMapping.put(TAILLE, null);
|
|
|
116 |
columnMapping.put(FAMILLE, null);
|
|
|
117 |
columnMapping.put(COULEUR, null);
|
|
|
118 |
columnMapping.put(QTE, null);
|
|
|
119 |
columnMapping.put(QTE_OPENCONCERTO, null);
|
|
|
120 |
{
|
|
|
121 |
// Searching column index from column Header
|
|
|
122 |
final DataImporter importer = new DataImporter(table);
|
|
|
123 |
importer.setSkipFirstLine(false);
|
|
|
124 |
ArrayTableModel m = importer.createModelFrom(file);
|
|
|
125 |
List<Object> line = m.getLineValuesAt(0);
|
|
|
126 |
for (int i = 0; i < line.size(); i++) {
|
|
|
127 |
Object object = line.get(i);
|
|
|
128 |
if (object != null) {
|
|
|
129 |
for (String key : columnMapping.keySet()) {
|
|
|
130 |
if (object.toString().equalsIgnoreCase(key)) {
|
|
|
131 |
columnMapping.put(key, i);
|
|
|
132 |
}
|
|
|
133 |
}
|
|
|
134 |
}
|
|
|
135 |
}
|
|
|
136 |
}
|
| 156 |
ilm |
137 |
|
| 182 |
ilm |
138 |
String msg = "Colonnes importées : \n";
|
|
|
139 |
final SQLPreferences prefs = SQLPreferences.getMemCached(table.getDBRoot());
|
|
|
140 |
final boolean hasDeclinaison = prefs.getBoolean(GestionArticleGlobalPreferencePanel.ACTIVER_DECLINAISON, false);
|
|
|
141 |
List<String> required;
|
|
|
142 |
if (hasDeclinaison) {
|
|
|
143 |
required = Arrays.asList(CODE, QTE, QTE_OPENCONCERTO, TAILLE, COULEUR);
|
|
|
144 |
} else {
|
|
|
145 |
required = Arrays.asList(CODE, QTE, QTE_OPENCONCERTO);
|
|
|
146 |
}
|
|
|
147 |
|
|
|
148 |
for (Entry<String, Integer> e : columnMapping.entrySet()) {
|
|
|
149 |
if (e.getValue() != null) {
|
|
|
150 |
msg += e.getKey() + " : " + getColumnName(e.getValue()) + "\n";
|
|
|
151 |
} else {
|
|
|
152 |
if (required.contains(e.getKey())) {
|
|
|
153 |
JOptionPane.showMessageDialog(null, "Aucune colonne " + e.getKey() + " trouvée, import annulé!\nCette colonne est obligatoire.");
|
|
|
154 |
return;
|
|
|
155 |
}
|
|
|
156 |
msg += e.getKey() + " : non importée\n";
|
| 156 |
ilm |
157 |
}
|
| 182 |
ilm |
158 |
}
|
|
|
159 |
msg += "\nVoulez vous continuer ?";
|
|
|
160 |
int a = JOptionPane.showConfirmDialog(null, msg);
|
|
|
161 |
if (a == JOptionPane.YES_OPTION) {
|
| 156 |
ilm |
162 |
|
| 182 |
ilm |
163 |
final DataImporter importer = new DataImporter(table) {
|
|
|
164 |
@Override
|
|
|
165 |
protected void customizeRowValuesToFetch(SQLRowValues vals) {
|
| 156 |
ilm |
166 |
|
| 182 |
ilm |
167 |
vals.putRowValues("ID_STOCK").putNulls("ID", "QTE_REEL", "QTE_TH", "ID_DEPOT_STOCK");
|
|
|
168 |
}
|
|
|
169 |
};
|
|
|
170 |
importer.setSkipFirstLine(true);
|
| 156 |
ilm |
171 |
|
| 182 |
ilm |
172 |
ArrayTableModel m = importer.createModelFrom(file);
|
| 156 |
ilm |
173 |
|
| 182 |
ilm |
174 |
Calendar c = Calendar.getInstance();
|
|
|
175 |
// c.set(Calendar.DAY_OF_MONTH, 1);
|
|
|
176 |
// c.set(Calendar.MONTH, Calendar.JANUARY);
|
|
|
177 |
// c.set(Calendar.HOUR_OF_DAY, 0);
|
|
|
178 |
Date today = c.getTime();
|
| 156 |
ilm |
179 |
|
| 182 |
ilm |
180 |
// TODO ne pas vider les stocks des kits, recalculer les stocks des kits
|
| 156 |
ilm |
181 |
|
| 182 |
ilm |
182 |
SQLRowValues rowVals = new SQLRowValues(table.getTable("ETAT_STOCK"));
|
|
|
183 |
rowVals.put("DATE", today);
|
|
|
184 |
rowVals.put("INVENTAIRE", Boolean.TRUE);
|
|
|
185 |
rowVals.put("ID_DEPOT_STOCK", this.depot.getID());
|
|
|
186 |
SQLRow rowEtat = rowVals.commit();
|
| 156 |
ilm |
187 |
|
| 182 |
ilm |
188 |
for (int i = 1; i < m.getRowCount(); i++) {
|
|
|
189 |
List<Object> o = m.getLineValuesAt(i);
|
|
|
190 |
if (o.size() >= 5) {
|
|
|
191 |
System.err.println(o);
|
|
|
192 |
String code = o.get(columnMapping.get(CODE)).toString();
|
|
|
193 |
if (code.trim().length() > 0) {
|
| 156 |
ilm |
194 |
|
| 182 |
ilm |
195 |
final String stringQty = o.get(columnMapping.get(QTE)).toString();
|
|
|
196 |
Double qty = stringQty.trim().length() == 0 ? 0 : Double.valueOf(stringQty);
|
|
|
197 |
final String stringQtyOld = o.get(columnMapping.get(QTE_OPENCONCERTO)).toString();
|
|
|
198 |
float qtyOld = stringQtyOld.trim().length() == 0 ? 0 : Float.valueOf(stringQtyOld);
|
| 156 |
ilm |
199 |
|
| 182 |
ilm |
200 |
String couleur = "";
|
|
|
201 |
if (columnMapping.get(COULEUR) != null) {
|
|
|
202 |
couleur = o.get(columnMapping.get(COULEUR)).toString();
|
|
|
203 |
}
|
| 156 |
ilm |
204 |
|
| 182 |
ilm |
205 |
String taille = "";
|
|
|
206 |
if (columnMapping.get(TAILLE) != null) {
|
|
|
207 |
taille = o.get(columnMapping.get(TAILLE)).toString();
|
| 156 |
ilm |
208 |
}
|
|
|
209 |
|
| 182 |
ilm |
210 |
// SQLRowAccessor match = findArticle(code, couleur, taille);
|
| 156 |
ilm |
211 |
|
| 182 |
ilm |
212 |
Tuple2<SQLRowValues, SQLRowValues> match = findArticle(code, couleur, taille);
|
|
|
213 |
if (match != null) {
|
| 156 |
ilm |
214 |
|
| 182 |
ilm |
215 |
SQLRowAccessor stockValues = match.get1();
|
| 156 |
ilm |
216 |
|
| 182 |
ilm |
217 |
final SQLTable tableMvt = table.getTable("MOUVEMENT_STOCK");
|
|
|
218 |
SQLRowValues rowValsMvtStockClotureFermeture = new SQLRowValues(tableMvt);
|
|
|
219 |
rowValsMvtStockClotureFermeture.put("QTE", -qtyOld);
|
|
|
220 |
rowValsMvtStockClotureFermeture.put("NOM", "Clôture stock avant inventaire");
|
|
|
221 |
rowValsMvtStockClotureFermeture.put("ID_ARTICLE", match.get0().getID());
|
|
|
222 |
rowValsMvtStockClotureFermeture.put("DATE", today);
|
|
|
223 |
rowValsMvtStockClotureFermeture.put("REEL", Boolean.TRUE);
|
|
|
224 |
rowValsMvtStockClotureFermeture.put("ID_STOCK", stockValues.getID());
|
| 156 |
ilm |
225 |
|
| 182 |
ilm |
226 |
BigDecimal prc = getPRC(match.get0(), Math.round(qtyOld), today);
|
|
|
227 |
if (prc == null) {
|
|
|
228 |
prc = BigDecimal.ZERO;
|
|
|
229 |
}
|
|
|
230 |
if (tableMvt.contains("PRICE")) {
|
|
|
231 |
rowValsMvtStockClotureFermeture.put("PRICE", prc);
|
|
|
232 |
}
|
|
|
233 |
rowValsMvtStockClotureFermeture.put("CLOTURE", Boolean.TRUE);
|
|
|
234 |
rowValsMvtStockClotureFermeture.put("ID_ETAT_STOCK", rowEtat.getID());
|
|
|
235 |
rowValsMvtStockClotureFermeture.getGraph().store(StoreMode.COMMIT, false);
|
|
|
236 |
|
|
|
237 |
SQLRowValues rowValsItem = new SQLRowValues(table.getTable("ETAT_STOCK_ELEMENT"));
|
|
|
238 |
rowValsItem.put("ID_ETAT_STOCK", rowEtat.getID());
|
|
|
239 |
rowValsItem.put("PA", prc);
|
|
|
240 |
rowValsItem.put("PV", BigDecimal.ZERO);
|
|
|
241 |
rowValsItem.put("QTE", qtyOld);
|
|
|
242 |
rowValsItem.put("T_PA", prc.multiply(new BigDecimal(qtyOld)));
|
|
|
243 |
rowValsItem.put("T_PV", BigDecimal.ZERO);
|
|
|
244 |
rowValsItem.put("CODE", match.get0().getString("CODE"));
|
|
|
245 |
rowValsItem.put("NOM", match.get0().getString("NOM"));
|
|
|
246 |
rowValsItem.put("ID_ARTICLE", match.get0().getID());
|
|
|
247 |
rowValsItem.getGraph().store(StoreMode.COMMIT, false);
|
|
|
248 |
|
|
|
249 |
SQLRowValues rowValsMvtStockClotureOuverture = new SQLRowValues(tableMvt);
|
|
|
250 |
rowValsMvtStockClotureOuverture.put("QTE", qty);
|
|
|
251 |
rowValsMvtStockClotureOuverture.put("NOM", "Mise en stock inventaire");
|
|
|
252 |
rowValsMvtStockClotureOuverture.put("ID_ETAT_STOCK", rowEtat.getID());
|
|
|
253 |
rowValsMvtStockClotureOuverture.put("ID_ARTICLE", match.get0().getID());
|
|
|
254 |
rowValsMvtStockClotureOuverture.put("DATE", today);
|
|
|
255 |
rowValsMvtStockClotureOuverture.put("REEL", Boolean.TRUE);
|
|
|
256 |
rowValsMvtStockClotureOuverture.put("ID_STOCK", stockValues.getID());
|
|
|
257 |
rowValsMvtStockClotureOuverture.put("OUVERTURE", Boolean.TRUE);
|
|
|
258 |
if (tableMvt.contains("PRICE")) {
|
|
|
259 |
rowValsMvtStockClotureOuverture.put("PRICE", getPRC(match.get0(), qty.intValue(), today));
|
|
|
260 |
}
|
|
|
261 |
rowValsMvtStockClotureOuverture.getGraph().store(StoreMode.COMMIT, false);
|
|
|
262 |
|
|
|
263 |
// if (!match.isForeignEmpty("ID_STOCK")) {
|
|
|
264 |
// match.getForeign("ID_STOCK").createEmptyUpdateRow().put("QTE_REEL",
|
|
|
265 |
// qty).commit();
|
|
|
266 |
// } else {
|
|
|
267 |
final SQLRowValues createEmptyUpdateRow = match.get1().createEmptyUpdateRow();
|
|
|
268 |
createEmptyUpdateRow.put("QTE_REEL", qty);
|
|
|
269 |
createEmptyUpdateRow.getGraph().store(StoreMode.COMMIT, false);
|
|
|
270 |
|
|
|
271 |
// }
|
|
|
272 |
|
|
|
273 |
} else {
|
|
|
274 |
System.err.println("Aucun article correspondant au code " + code);
|
|
|
275 |
}
|
| 156 |
ilm |
276 |
}
|
|
|
277 |
}
|
|
|
278 |
}
|
|
|
279 |
|
| 182 |
ilm |
280 |
/**
|
|
|
281 |
* Mise à jour des kits
|
|
|
282 |
*/
|
| 156 |
ilm |
283 |
|
| 182 |
ilm |
284 |
List<String> reqs = new ArrayList<String>();
|
|
|
285 |
for (String code : codeKits) {
|
|
|
286 |
System.err.println(code);
|
|
|
287 |
SQLRowValues rowValsKit = kits.get(code);
|
|
|
288 |
StockItem item = new StockItem(rowValsKit, ProductComponent.findOrCreateStock(rowValsKit, depot));
|
|
|
289 |
Collection<SQLRowValues> elts = rowValsKit.getReferentRows(tableArtElt.getField("ID_ARTICLE_PARENT"));
|
|
|
290 |
for (SQLRowValues sqlRowValues : elts) {
|
|
|
291 |
if (sqlRowValues.getForeign("ID_ARTICLE") != null) {
|
|
|
292 |
item.addItemComponent(
|
|
|
293 |
new StockItemComponent(new StockItem(sqlRowValues.getForeign("ID_ARTICLE"), ProductComponent.findOrCreateStock(sqlRowValues.getForeign("ID_ARTICLE"), depot)),
|
|
|
294 |
sqlRowValues.getBigDecimal("QTE_UNITAIRE"), sqlRowValues.getInt("QTE")));
|
|
|
295 |
}
|
| 156 |
ilm |
296 |
}
|
| 182 |
ilm |
297 |
item.updateQtyFromChildren();
|
|
|
298 |
reqs.add(item.getUpdateRequest());
|
| 156 |
ilm |
299 |
}
|
|
|
300 |
|
| 182 |
ilm |
301 |
List<? extends ResultSetHandler> handlers = new ArrayList<ResultSetHandler>(reqs.size());
|
|
|
302 |
for (String s : reqs) {
|
|
|
303 |
handlers.add(null);
|
|
|
304 |
}
|
|
|
305 |
// FIXME FIRE TABLE CHANGED TO UPDATE ILISTE ??
|
|
|
306 |
SQLUtils.executeMultiple(table.getDBSystemRoot(), reqs, handlers);
|
|
|
307 |
|
|
|
308 |
/**
|
|
|
309 |
* Mise à jour des prix mini
|
|
|
310 |
*/
|
|
|
311 |
// for (SQLRowValues rowValsArt : rowValsArtNonSync) {
|
|
|
312 |
// SQLRow rowArt = rowValsArt.asRow();
|
|
|
313 |
// List<SQLRow> rowsPVMin =
|
|
|
314 |
// rowArt.getReferentRows(tableArtElt.getTable("ARTICLE_PRIX_MIN_VENTE"));
|
|
|
315 |
// List<SQLRow> rowsPA =
|
|
|
316 |
// rowArt.getReferentRows(tableArtElt.getTable("ARTICLE_TARIF_FOURNISSEUR"));
|
|
|
317 |
//
|
|
|
318 |
// // On récupére les derniers prix min valides
|
|
|
319 |
// Map<Integer, SQLRow> mapLastValidRows = new HashMap<Integer, SQLRow>();
|
|
|
320 |
// for (SQLRow rowPVMin : rowsPVMin) {
|
|
|
321 |
// final int qteMinPrice = rowPVMin.getInt("QTE");
|
|
|
322 |
// SQLRow rowValsLastValid = mapLastValidRows.get(qteMinPrice);
|
|
|
323 |
// if (rowValsLastValid == null || rowValsLastValid.getDate("DATE") == null ||
|
|
|
324 |
// rowValsLastValid.getDate("DATE").before(rowPVMin.getDate("DATE"))) {
|
|
|
325 |
// mapLastValidRows.put(qteMinPrice, rowPVMin);
|
|
|
326 |
// }
|
|
|
327 |
// }
|
|
|
328 |
//
|
|
|
329 |
// // On récupére les derniers Prix d'achat valide
|
|
|
330 |
// Map<Integer, SQLRow> mapLastValidAchatRows = new HashMap<Integer, SQLRow>();
|
|
|
331 |
// for (SQLRow rowPA : rowsPA) {
|
|
|
332 |
// final int qtePRC = rowPA.getInt("QTE");
|
|
|
333 |
// SQLRow rowValsLastValid = mapLastValidAchatRows.get(qtePRC);
|
|
|
334 |
// if (rowValsLastValid == null || rowValsLastValid.getDate("DATE_PRIX") == null ||
|
|
|
335 |
// rowValsLastValid.getDate("DATE_PRIX").before(rowPA.getDate("DATE_PRIX"))) {
|
|
|
336 |
// mapLastValidAchatRows.put(qtePRC, rowPA);
|
|
|
337 |
// }
|
|
|
338 |
// }
|
|
|
339 |
//
|
|
|
340 |
// // Mise à jour, si Prix < au prix min, du PRC et des prix min
|
|
|
341 |
// for (Integer qte : mapLastValidAchatRows.keySet()) {
|
|
|
342 |
// SQLRow rowVals = mapLastValidAchatRows.get(qte);
|
|
|
343 |
// checkMinPrice(rowVals, mapLastValidRows.get(qte));
|
|
|
344 |
// }
|
|
|
345 |
// }
|
| 156 |
ilm |
346 |
}
|
|
|
347 |
}
|
|
|
348 |
|
|
|
349 |
private void checkMinPrice(SQLRow rowValsSuplierLastValid, SQLRow lastValidRow) {
|
|
|
350 |
boolean update = false;
|
|
|
351 |
final ProductHelper helper = new ProductHelper(rowValsSuplierLastValid.getTable().getDBRoot());
|
|
|
352 |
|
|
|
353 |
BigDecimal result = helper.getEnumPrice(rowValsSuplierLastValid, SupplierPriceField.COEF_PRIX_MINI);
|
|
|
354 |
if (result != null) {
|
|
|
355 |
final int qteSuplier = rowValsSuplierLastValid.getInt("QTE");
|
|
|
356 |
|
|
|
357 |
final Calendar date2 = rowValsSuplierLastValid.getDate("DATE_PRIX");
|
|
|
358 |
if (date2 != null) {
|
|
|
359 |
if (lastValidRow != null) {
|
|
|
360 |
final Calendar date1 = lastValidRow.getDate("DATE");
|
|
|
361 |
if ((date1.get(Calendar.YEAR) == date2.get(Calendar.YEAR) && date1.get(Calendar.MONTH) == date2.get(Calendar.MONTH)
|
|
|
362 |
&& date1.get(Calendar.DAY_OF_MONTH) == date2.get(Calendar.DAY_OF_MONTH)) || date1.after(date2)) {
|
|
|
363 |
if (lastValidRow.getBigDecimal("PRIX") != null && lastValidRow.getInt("QTE") <= qteSuplier) {
|
|
|
364 |
try {
|
|
|
365 |
lastValidRow.asRowValues().put("PRIX", result).commit();
|
|
|
366 |
} catch (SQLException e) {
|
|
|
367 |
// TODO Auto-generated catch block
|
|
|
368 |
e.printStackTrace();
|
|
|
369 |
}
|
|
|
370 |
// purchaseMinPriceListTable.setPriceMin(result);
|
|
|
371 |
update = true;
|
|
|
372 |
}
|
|
|
373 |
} else {
|
|
|
374 |
if (date1.before(date2)) {
|
|
|
375 |
SQLRowValues rowValsToInsert = new SQLRowValues(lastValidRow.getTable());
|
|
|
376 |
rowValsToInsert.put("PRIX", result);
|
|
|
377 |
rowValsToInsert.put("DATE", rowValsSuplierLastValid.getObject("DATE_PRIX"));
|
|
|
378 |
rowValsToInsert.put("QTE", rowValsSuplierLastValid.getObject("QTE"));
|
|
|
379 |
rowValsToInsert.put("ID_ARTICLE", rowValsSuplierLastValid.getInt("ID_ARTICLE"));
|
|
|
380 |
try {
|
|
|
381 |
rowValsToInsert.commit();
|
|
|
382 |
} catch (SQLException e) {
|
|
|
383 |
// TODO Auto-generated catch block
|
|
|
384 |
e.printStackTrace();
|
|
|
385 |
}
|
|
|
386 |
}
|
|
|
387 |
}
|
|
|
388 |
}
|
|
|
389 |
}
|
|
|
390 |
}
|
|
|
391 |
|
|
|
392 |
}
|
|
|
393 |
|
|
|
394 |
public BigDecimal getPRC(SQLRowValues rowVals, int qty, Date d) {
|
|
|
395 |
// SQLTable table = rowVals.getTable().getDBRoot().getTable("ARTICLE_PRIX_REVIENT");
|
|
|
396 |
// Collection<SQLRow> prcs = rowVals.asRow().getReferentRows(table);
|
|
|
397 |
//
|
|
|
398 |
// BigDecimal result = null;
|
|
|
399 |
// final List<PriceByQty> prices = new ArrayList<PriceByQty>();
|
|
|
400 |
//
|
|
|
401 |
// for (SQLRow row : prcs) {
|
|
|
402 |
// Calendar date = Calendar.getInstance();
|
|
|
403 |
// date.set(Calendar.DAY_OF_MONTH, 1);
|
|
|
404 |
// date.set(Calendar.MONTH, 1);
|
|
|
405 |
// date.set(Calendar.YEAR, 2001);
|
|
|
406 |
// if (row.getObject("DATE") != null) {
|
|
|
407 |
// date = row.getDate("DATE");
|
|
|
408 |
// }
|
|
|
409 |
// prices.add(new PriceByQty(row.getLong("QTE"), row.getBigDecimal("PRIX"),
|
|
|
410 |
// date.getTime()));
|
|
|
411 |
// }
|
|
|
412 |
//
|
|
|
413 |
// result = PriceByQty.getPriceForQty(qty, prices, d);
|
|
|
414 |
// if (result == null) {
|
|
|
415 |
// // Can occur during editing
|
|
|
416 |
// result = BigDecimal.ZERO;
|
|
|
417 |
// }
|
|
|
418 |
|
|
|
419 |
ProductComponent comp = new ProductComponent(rowVals, new BigDecimal(qty), null, null);
|
|
|
420 |
return comp.getPRC(d);
|
|
|
421 |
// return result;
|
|
|
422 |
}
|
|
|
423 |
|
| 182 |
ilm |
424 |
private final Map<String, Tuple2<SQLRowValues, SQLRowValues>> mapArticle = new HashMap<String, Tuple2<SQLRowValues, SQLRowValues>>();
|
|
|
425 |
private final Map<String, Tuple2<SQLRowValues, SQLRowValues>> mapArticleVirtuel = new HashMap<String, Tuple2<SQLRowValues, SQLRowValues>>();
|
|
|
426 |
private final Map<Tuple3<String, String, String>, Tuple2<SQLRowValues, SQLRowValues>> mapDeclArticle = new HashMap<Tuple3<String, String, String>, Tuple2<SQLRowValues, SQLRowValues>>();
|
|
|
427 |
|
|
|
428 |
private void fillArticles() throws SQLException {
|
| 156 |
ilm |
429 |
final SQLTable table = Configuration.getInstance().getRoot().findTable("ARTICLE");
|
|
|
430 |
SQLRowValues graph = new SQLRowValues(table);
|
|
|
431 |
graph.put("ID", null);
|
|
|
432 |
graph.put("CODE", null);
|
|
|
433 |
graph.put("NOM", null);
|
| 182 |
ilm |
434 |
graph.put("VIRTUEL", null);
|
|
|
435 |
graph.putRowValues("ID_ARTICLE_DECLINAISON_COULEUR").putNulls("ID", "NOM");
|
|
|
436 |
graph.putRowValues("ID_ARTICLE_DECLINAISON_TAILLE").putNulls("ID", "NOM");
|
|
|
437 |
|
| 177 |
ilm |
438 |
final SQLTable foreignTableStock = table.getForeignTable("ID_STOCK");
|
|
|
439 |
SQLRowValues graphStock = new SQLRowValues(foreignTableStock);
|
|
|
440 |
graphStock.putNulls("ID_DEPOT_STOCK", "ID", "QTE_REEL", "QTE_TH", "QTE_LIV_ATTENTE", "QTE_RECEPT_ATTENTE");
|
|
|
441 |
graphStock.put("ID_ARTICLE", graph);
|
| 156 |
ilm |
442 |
|
| 182 |
ilm |
443 |
SQLRowValues graphStockArt = new SQLRowValues(foreignTableStock);
|
|
|
444 |
graphStockArt.putNulls("ID_DEPOT_STOCK", "ID", "QTE_REEL", "QTE_TH", "QTE_LIV_ATTENTE", "QTE_RECEPT_ATTENTE");
|
|
|
445 |
graph.put("ID_STOCK", graphStockArt);
|
|
|
446 |
|
| 156 |
ilm |
447 |
final SQLTable tableArtElt = table.getTable("ARTICLE_ELEMENT");
|
|
|
448 |
SQLRowValues artElt = new SQLRowValues(tableArtElt);
|
|
|
449 |
artElt.put("ID", null);
|
|
|
450 |
artElt.put("QTE", null);
|
|
|
451 |
artElt.put("QTE_UNITAIRE", null);
|
|
|
452 |
artElt.put("ID_ARTICLE_PARENT", graph);
|
| 177 |
ilm |
453 |
final SQLRowValues articleParent = artElt.putRowValues("ID_ARTICLE");
|
| 182 |
ilm |
454 |
articleParent.putNulls("ID", "CODE", "NOM", "VIRTUEL");
|
|
|
455 |
articleParent.putRowValues("ID_ARTICLE_DECLINAISON_COULEUR").putNulls("ID", "NOM");
|
|
|
456 |
articleParent.putRowValues("ID_ARTICLE_DECLINAISON_TAILLE").putNulls("ID", "NOM");
|
|
|
457 |
|
| 177 |
ilm |
458 |
SQLRowValues graphStockItem = new SQLRowValues(foreignTableStock);
|
|
|
459 |
graphStockItem.put("ID_ARTICLE", articleParent);
|
| 156 |
ilm |
460 |
|
| 182 |
ilm |
461 |
SQLRowValues graphStockArtItem = new SQLRowValues(foreignTableStock);
|
|
|
462 |
graphStockArtItem.putNulls("ID_DEPOT_STOCK", "ID", "QTE_REEL", "QTE_TH", "QTE_LIV_ATTENTE", "QTE_RECEPT_ATTENTE");
|
|
|
463 |
articleParent.put("ID_STOCK", graphStockArtItem);
|
|
|
464 |
|
| 156 |
ilm |
465 |
SQLRowValuesListFetcher fetcher = SQLRowValuesListFetcher.create(graph);
|
|
|
466 |
List<SQLRowValues> results = fetcher.fetch();
|
|
|
467 |
|
|
|
468 |
Calendar c = Calendar.getInstance();
|
|
|
469 |
// c.set(Calendar.DAY_OF_MONTH, 1);
|
|
|
470 |
c.add(Calendar.MONTH, -2);
|
|
|
471 |
c.set(Calendar.DAY_OF_MONTH, 31);
|
|
|
472 |
Date dEndYear = c.getTime();
|
|
|
473 |
|
|
|
474 |
for (SQLRowValues sqlRowValues : results) {
|
|
|
475 |
final String code = sqlRowValues.getString("CODE");
|
|
|
476 |
|
| 177 |
ilm |
477 |
Collection<SQLRowValues> stocks = sqlRowValues.getReferentRows(foreignTableStock);
|
|
|
478 |
|
|
|
479 |
SQLRowValues rowValsStock = null;
|
|
|
480 |
for (SQLRowValues sqlRowValues2 : stocks) {
|
|
|
481 |
if (sqlRowValues2.getForeignID("ID_DEPOT_STOCK") == depot.getID()) {
|
|
|
482 |
rowValsStock = sqlRowValues2;
|
|
|
483 |
}
|
|
|
484 |
}
|
| 182 |
ilm |
485 |
// if (rowValsStock == null) {
|
|
|
486 |
// rowValsStock = ProductComponent.findOrCreateStock(sqlRowValues, depot).asRowValues();
|
|
|
487 |
// }
|
|
|
488 |
|
|
|
489 |
if (sqlRowValues.getBoolean("VIRTUEL")) {
|
|
|
490 |
mapArticleVirtuel.put(sqlRowValues.getString("CODE"), Tuple2.create(sqlRowValues, rowValsStock));
|
| 177 |
ilm |
491 |
}
|
|
|
492 |
|
| 182 |
ilm |
493 |
final SQLRowAccessor couleur = sqlRowValues.getObject("ID_ARTICLE_DECLINAISON_COULEUR") == null ? null : sqlRowValues.getNonEmptyForeign("ID_ARTICLE_DECLINAISON_COULEUR");
|
|
|
494 |
final SQLRowAccessor taille = sqlRowValues.getObject("ID_ARTICLE_DECLINAISON_TAILLE") == null ? null : sqlRowValues.getNonEmptyForeign("ID_ARTICLE_DECLINAISON_TAILLE");
|
|
|
495 |
if (couleur == null && taille == null) {
|
|
|
496 |
mapArticle.put(sqlRowValues.getString("CODE"), Tuple2.create(sqlRowValues, rowValsStock));
|
|
|
497 |
} else if (couleur == null) {
|
|
|
498 |
mapDeclArticle.put(Tuple3.create(sqlRowValues.getString("CODE"), null, taille.getString("NOM")), Tuple2.create(sqlRowValues, rowValsStock));
|
|
|
499 |
} else if (taille == null) {
|
|
|
500 |
mapDeclArticle.put(Tuple3.create(sqlRowValues.getString("CODE"), couleur.getString("NOM"), null), Tuple2.create(sqlRowValues, rowValsStock));
|
|
|
501 |
} else {
|
|
|
502 |
mapDeclArticle.put(Tuple3.create(sqlRowValues.getString("CODE"), couleur.getString("NOM"), taille.getString("NOM")), Tuple2.create(sqlRowValues, rowValsStock));
|
|
|
503 |
}
|
| 177 |
ilm |
504 |
|
| 156 |
ilm |
505 |
final Set<SQLRowValues> referentRows = sqlRowValues.getReferentRows(tableArtElt.getField("ID_ARTICLE_PARENT"));
|
|
|
506 |
if (referentRows.size() == 0) {
|
|
|
507 |
// if (!sqlRowValues.isForeignEmpty("ID_STOCK")) {
|
|
|
508 |
// SQLRowAccessor rowValsStock = sqlRowValues.getForeign("ID_STOCK");
|
|
|
509 |
// int qteReel = rowValsStock.getInt("QTE_REEL");
|
|
|
510 |
// {
|
|
|
511 |
// SQLRowValues rowValsMvtStockCloture = new
|
|
|
512 |
// SQLRowValues(table.getTable("MOUVEMENT_STOCK"));
|
|
|
513 |
// rowValsMvtStockCloture.put("QTE", -qteReel);
|
|
|
514 |
// rowValsMvtStockCloture.put("NOM", "Clôture du stock avant inventaire");
|
|
|
515 |
// rowValsMvtStockCloture.put("ID_ARTICLE", sqlRowValues.getID());
|
|
|
516 |
// rowValsMvtStockCloture.put("DATE", dEndYear);
|
|
|
517 |
// rowValsMvtStockCloture.put("REEL", Boolean.TRUE);
|
|
|
518 |
// rowValsMvtStockCloture.put("PRICE", getPRC(sqlRowValues, qteReel, dEndYear));
|
|
|
519 |
// rowValsMvtStockCloture.commit();
|
|
|
520 |
//
|
|
|
521 |
// rowValsStock.createEmptyUpdateRow().put("QTE_REEL", 0).commit();
|
|
|
522 |
// }
|
|
|
523 |
//
|
|
|
524 |
// } else {
|
|
|
525 |
// sqlRowValues.putRowValues("ID_STOCK").commit();
|
|
|
526 |
// }
|
|
|
527 |
} else {
|
|
|
528 |
boolean contains = false;
|
|
|
529 |
for (SQLRowValues sqlRowValues2 : referentRows) {
|
| 180 |
ilm |
530 |
if (sqlRowValues2.getForeign("ID_ARTICLE") != null && !sqlRowValues2.isForeignEmpty("ID_ARTICLE") && sqlRowValues2.getForeign("ID_ARTICLE").getString("CODE") != null) {
|
| 156 |
ilm |
531 |
if (codeKits.contains(sqlRowValues2.getForeign("ID_ARTICLE").getString("CODE"))) {
|
|
|
532 |
contains = true;
|
|
|
533 |
break;
|
|
|
534 |
}
|
|
|
535 |
}
|
|
|
536 |
}
|
|
|
537 |
if (!contains) {
|
|
|
538 |
codeKits.add(0, code);
|
|
|
539 |
} else {
|
|
|
540 |
codeKits.add(code);
|
|
|
541 |
}
|
|
|
542 |
kits.put(code, sqlRowValues);
|
|
|
543 |
// if (sqlRowValues.isForeignEmpty("ID_STOCK")) {
|
|
|
544 |
// sqlRowValues.putRowValues("ID_STOCK").commit();
|
|
|
545 |
// }
|
|
|
546 |
}
|
|
|
547 |
}
|
|
|
548 |
}
|
| 182 |
ilm |
549 |
|
|
|
550 |
private Tuple2<SQLRowValues, SQLRowValues> findArticle(String code, String couleur, String taille) throws SQLException {
|
|
|
551 |
if (!mapCouleur.containsKey(couleur)) {
|
|
|
552 |
SQLRowValues rowVals = new SQLRowValues(root.getTable("ARTICLE_DECLINAISON_COULEUR"));
|
|
|
553 |
rowVals.put("NOM", couleur);
|
|
|
554 |
mapCouleur.put(couleur, rowVals.commit().getID());
|
|
|
555 |
}
|
|
|
556 |
if (!mapTaille.containsKey(taille)) {
|
|
|
557 |
SQLRowValues rowVals = new SQLRowValues(root.getTable("ARTICLE_DECLINAISON_TAILLE"));
|
|
|
558 |
rowVals.put("NOM", taille);
|
|
|
559 |
mapTaille.put(taille, rowVals.commit().getID());
|
|
|
560 |
}
|
|
|
561 |
|
|
|
562 |
Tuple2<SQLRowValues, SQLRowValues> t;
|
|
|
563 |
if ((couleur == null || couleur.trim().length() == 0) && (taille == null || taille.trim().length() == 0)) {
|
|
|
564 |
|
|
|
565 |
t = mapArticle.get(code);
|
|
|
566 |
if (t.get1() == null) {
|
|
|
567 |
t = Tuple2.create(t.get0(), ProductComponent.findOrCreateStock(t.get0(), depot).asRowValues());
|
|
|
568 |
mapArticle.put(code, t);
|
|
|
569 |
}
|
|
|
570 |
} else if (couleur == null || couleur.trim().length() == 0) {
|
|
|
571 |
t = mapDeclArticle.get(Tuple3.create(code, null, taille.trim()));
|
|
|
572 |
if (t == null) {
|
|
|
573 |
SQLRowValues artRow = cloneFromArticle(mapArticleVirtuel.get(code).get0(), null, mapTaille.get(taille.trim())).asRowValues();
|
|
|
574 |
final SQLRowValues stockRow = ProductComponent.findOrCreateStock(artRow, depot).asRowValues();
|
|
|
575 |
t = Tuple2.create(artRow, stockRow);
|
|
|
576 |
mapDeclArticle.put(Tuple3.create(code, null, taille.trim()), t);
|
|
|
577 |
} else if (t.get1() == null) {
|
|
|
578 |
t = Tuple2.create(t.get0(), ProductComponent.findOrCreateStock(t.get0(), depot).asRowValues());
|
|
|
579 |
mapDeclArticle.put(Tuple3.create(code, null, taille.trim()), t);
|
|
|
580 |
}
|
|
|
581 |
} else if (taille == null || taille.trim().length() == 0) {
|
|
|
582 |
t = mapDeclArticle.get(Tuple3.create(code, couleur.trim(), null));
|
|
|
583 |
if (t == null) {
|
|
|
584 |
SQLRowValues artRow = cloneFromArticle(mapArticleVirtuel.get(code).get0(), mapCouleur.get(couleur.trim()), null).asRowValues();
|
|
|
585 |
final SQLRowValues stockRow = ProductComponent.findOrCreateStock(artRow, depot).asRowValues();
|
|
|
586 |
t = Tuple2.create(artRow, stockRow);
|
|
|
587 |
mapDeclArticle.put(Tuple3.create(code, couleur.trim(), null), t);
|
|
|
588 |
} else if (t.get1() == null) {
|
|
|
589 |
t = Tuple2.create(t.get0(), ProductComponent.findOrCreateStock(t.get0(), depot).asRowValues());
|
|
|
590 |
mapDeclArticle.put(Tuple3.create(code, couleur.trim(), null), t);
|
|
|
591 |
}
|
|
|
592 |
} else {
|
|
|
593 |
t = mapDeclArticle.get(Tuple3.create(code, couleur.trim(), taille.trim()));
|
|
|
594 |
if (t == null) {
|
|
|
595 |
if (mapArticle.get(code) != null) {
|
|
|
596 |
SQLRowValues artRow = cloneFromArticle(mapArticleVirtuel.get(code).get0(), mapCouleur.get(couleur.trim()), mapTaille.get(taille.trim())).asRowValues();
|
|
|
597 |
final SQLRowValues stockRow = ProductComponent.findOrCreateStock(artRow, depot).asRowValues();
|
|
|
598 |
t = Tuple2.create(artRow, stockRow);
|
|
|
599 |
mapDeclArticle.put(Tuple3.create(code, couleur.trim(), taille.trim()), t);
|
|
|
600 |
}
|
|
|
601 |
} else if (t.get1() == null) {
|
|
|
602 |
t = Tuple2.create(t.get0(), ProductComponent.findOrCreateStock(t.get0(), depot).asRowValues());
|
|
|
603 |
mapDeclArticle.put(Tuple3.create(code, couleur.trim(), taille.trim()), t);
|
|
|
604 |
}
|
|
|
605 |
}
|
|
|
606 |
return t;
|
|
|
607 |
}
|
|
|
608 |
|
|
|
609 |
public SQLRow cloneFromArticle(SQLRowAccessor rArt, Integer idCouleur, Integer idTaille) throws SQLException {
|
|
|
610 |
if (rArt == null) {
|
|
|
611 |
return null;
|
|
|
612 |
}
|
|
|
613 |
SQLRowValues copy = artElt.createCopy(rArt.getID());
|
|
|
614 |
copy.put("VIRTUEL", Boolean.FALSE);
|
|
|
615 |
copy.put("ID_ARTICLE_VIRTUEL_PERE", rArt.getID());
|
|
|
616 |
if (idCouleur != null) {
|
|
|
617 |
copy.put("ID_ARTICLE_DECLINAISON_COULEUR", idCouleur);
|
|
|
618 |
}
|
|
|
619 |
if (idTaille != null) {
|
|
|
620 |
copy.put("ID_ARTICLE_DECLINAISON_TAILLE", idTaille);
|
|
|
621 |
}
|
|
|
622 |
return copy.commit();
|
|
|
623 |
}
|
|
|
624 |
|
|
|
625 |
private String getColumnName(int columnNumber) {
|
|
|
626 |
int dividend = columnNumber;
|
|
|
627 |
String columnName = "";
|
|
|
628 |
int modulo;
|
|
|
629 |
|
|
|
630 |
while (dividend >= 0) {
|
|
|
631 |
modulo = dividend % 26;
|
|
|
632 |
columnName = String.valueOf((char) (65 + modulo)) + columnName;
|
|
|
633 |
dividend = (int) ((dividend - modulo) / 26);
|
|
|
634 |
if (dividend <= 0) {
|
|
|
635 |
break;
|
|
|
636 |
} else {
|
|
|
637 |
dividend--;
|
|
|
638 |
}
|
|
|
639 |
}
|
|
|
640 |
|
|
|
641 |
return columnName;
|
|
|
642 |
}
|
| 156 |
ilm |
643 |
}
|
