| 18 |
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.
|
| 18 |
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.generationDoc.gestcomm;
|
|
|
15 |
|
| 142 |
ilm |
16 |
import org.openconcerto.erp.core.finance.accounting.element.ComptePCESQLElement;
|
|
|
17 |
import org.openconcerto.erp.core.finance.tax.model.TaxeCache;
|
| 18 |
ilm |
18 |
import org.openconcerto.erp.generationDoc.AbstractListeSheetXml;
|
|
|
19 |
import org.openconcerto.erp.preferences.PrinterNXProps;
|
|
|
20 |
import org.openconcerto.sql.Configuration;
|
|
|
21 |
import org.openconcerto.sql.element.SQLElement;
|
| 58 |
ilm |
22 |
import org.openconcerto.sql.element.SQLElementDirectory;
|
| 18 |
ilm |
23 |
import org.openconcerto.sql.model.AliasedField;
|
|
|
24 |
import org.openconcerto.sql.model.AliasedTable;
|
| 142 |
ilm |
25 |
import org.openconcerto.sql.model.SQLRow;
|
|
|
26 |
import org.openconcerto.sql.model.SQLRowAccessor;
|
| 174 |
ilm |
27 |
import org.openconcerto.sql.model.SQLRowValues;
|
|
|
28 |
import org.openconcerto.sql.model.SQLRowValuesListFetcher;
|
| 18 |
ilm |
29 |
import org.openconcerto.sql.model.SQLSelect;
|
| 149 |
ilm |
30 |
import org.openconcerto.sql.model.SQLSelectJoin;
|
| 18 |
ilm |
31 |
import org.openconcerto.sql.model.SQLTable;
|
|
|
32 |
import org.openconcerto.sql.model.Where;
|
| 182 |
ilm |
33 |
import org.openconcerto.utils.ListMap;
|
| 149 |
ilm |
34 |
import org.openconcerto.utils.Tuple2;
|
| 18 |
ilm |
35 |
|
| 67 |
ilm |
36 |
import java.math.BigDecimal;
|
| 149 |
ilm |
37 |
import java.math.RoundingMode;
|
| 18 |
ilm |
38 |
import java.sql.Timestamp;
|
|
|
39 |
import java.text.DateFormat;
|
|
|
40 |
import java.text.SimpleDateFormat;
|
|
|
41 |
import java.util.ArrayList;
|
| 58 |
ilm |
42 |
import java.util.Calendar;
|
| 18 |
ilm |
43 |
import java.util.Date;
|
|
|
44 |
import java.util.HashMap;
|
| 142 |
ilm |
45 |
import java.util.HashSet;
|
| 18 |
ilm |
46 |
import java.util.List;
|
|
|
47 |
import java.util.Map;
|
| 142 |
ilm |
48 |
import java.util.Set;
|
| 18 |
ilm |
49 |
|
|
|
50 |
import org.apache.commons.dbutils.handlers.ArrayListHandler;
|
|
|
51 |
|
|
|
52 |
/**
|
|
|
53 |
* Statistique des ventes d'articles
|
|
|
54 |
*
|
|
|
55 |
*/
|
|
|
56 |
public class EtatVentesXmlSheet extends AbstractListeSheetXml {
|
|
|
57 |
|
| 58 |
ilm |
58 |
private static final String MODE2 = "mod2";
|
| 18 |
ilm |
59 |
|
| 58 |
ilm |
60 |
private static final String MODE1 = "mod1";
|
|
|
61 |
|
| 25 |
ilm |
62 |
public static final String TEMPLATE_ID = "EtatVentes";
|
|
|
63 |
|
|
|
64 |
public static final String TEMPLATE_PROPERTY_NAME = DEFAULT_PROPERTY_NAME;
|
|
|
65 |
|
| 18 |
ilm |
66 |
private Timestamp du, au;
|
| 149 |
ilm |
67 |
public boolean ticketCaisse = false;
|
| 174 |
ilm |
68 |
public boolean facture = false;
|
| 18 |
ilm |
69 |
|
| 174 |
ilm |
70 |
public EtatVentesXmlSheet(Date du, Date au, boolean ticketCaisse, boolean facture) {
|
| 67 |
ilm |
71 |
super();
|
| 18 |
ilm |
72 |
this.printer = PrinterNXProps.getInstance().getStringProperty("BonPrinter");
|
| 149 |
ilm |
73 |
this.ticketCaisse = ticketCaisse;
|
| 174 |
ilm |
74 |
this.facture = facture;
|
| 80 |
ilm |
75 |
if (du != null) {
|
|
|
76 |
final Calendar c1 = Calendar.getInstance();
|
|
|
77 |
c1.setTime(du);
|
|
|
78 |
c1.set(Calendar.HOUR_OF_DAY, 0);
|
|
|
79 |
c1.set(Calendar.MINUTE, 0);
|
|
|
80 |
c1.set(Calendar.SECOND, 0);
|
|
|
81 |
this.du = new Timestamp(c1.getTimeInMillis());
|
|
|
82 |
}
|
|
|
83 |
if (au != null) {
|
|
|
84 |
final Calendar c2 = Calendar.getInstance();
|
|
|
85 |
c2.setTime(au);
|
|
|
86 |
c2.set(Calendar.HOUR_OF_DAY, 23);
|
|
|
87 |
c2.set(Calendar.MINUTE, 59);
|
|
|
88 |
c2.set(Calendar.SECOND, 59);
|
|
|
89 |
this.au = new Timestamp(c2.getTimeInMillis());
|
|
|
90 |
}
|
| 18 |
ilm |
91 |
}
|
|
|
92 |
|
| 21 |
ilm |
93 |
@Override
|
| 25 |
ilm |
94 |
public String getDefaultTemplateId() {
|
|
|
95 |
return TEMPLATE_ID;
|
| 21 |
ilm |
96 |
}
|
|
|
97 |
|
| 25 |
ilm |
98 |
@Override
|
| 67 |
ilm |
99 |
protected String getStoragePathP() {
|
|
|
100 |
return "Etat Ventes";
|
|
|
101 |
}
|
|
|
102 |
|
|
|
103 |
Date d;
|
|
|
104 |
|
|
|
105 |
@Override
|
| 25 |
ilm |
106 |
public String getName() {
|
| 67 |
ilm |
107 |
if (d == null) {
|
|
|
108 |
d = new Date();
|
|
|
109 |
}
|
|
|
110 |
return "EtatVentes" + d.getTime();
|
| 18 |
ilm |
111 |
}
|
|
|
112 |
|
|
|
113 |
protected void createListeValues() {
|
| 149 |
ilm |
114 |
Map<Integer, Tuple2<BigDecimal, BigDecimal>> mapTVAVT = new HashMap<Integer, Tuple2<BigDecimal, BigDecimal>>();
|
|
|
115 |
Map<Integer, Tuple2<BigDecimal, BigDecimal>> mapTVAHA = new HashMap<Integer, Tuple2<BigDecimal, BigDecimal>>();
|
| 58 |
ilm |
116 |
final SQLElementDirectory directory = Configuration.getInstance().getDirectory();
|
|
|
117 |
final SQLElement eltVenteFacutreElement = directory.getElement("SAISIE_VENTE_FACTURE_ELEMENT");
|
|
|
118 |
final SQLElement eltVenteFacture = directory.getElement("SAISIE_VENTE_FACTURE");
|
|
|
119 |
final SQLElement eltEncaissement = directory.getElement("ENCAISSER_MONTANT");
|
|
|
120 |
final SQLElement eltTicketCaisse = directory.getElement("TICKET_CAISSE");
|
|
|
121 |
final SQLElement eltModeReglement = directory.getElement("MODE_REGLEMENT");
|
|
|
122 |
final SQLTable tableModeReglement = eltModeReglement.getTable();
|
|
|
123 |
final SQLTable tableFactureElement = eltVenteFacutreElement.getTable();
|
|
|
124 |
final SQLTable tableFacture = eltVenteFacture.getTable();
|
| 149 |
ilm |
125 |
BigDecimal totalTPVTTC = BigDecimal.ZERO;
|
|
|
126 |
BigDecimal totalTPA = BigDecimal.ZERO;
|
| 18 |
ilm |
127 |
|
| 149 |
ilm |
128 |
// Liste des valeurs de la feuille OO
|
|
|
129 |
ArrayList<Map<String, Object>> listValues = new ArrayList<Map<String, Object>>();
|
|
|
130 |
Map<Integer, String> style = styleAllSheetValues.get(0);
|
|
|
131 |
if (style == null) {
|
|
|
132 |
style = new HashMap<Integer, String>();
|
|
|
133 |
styleAllSheetValues.put(0, style);
|
|
|
134 |
}
|
| 18 |
ilm |
135 |
|
| 174 |
ilm |
136 |
final ArrayList<Map<String, Object>> listValuesStock = new ArrayList<>();
|
|
|
137 |
final Map<Integer, String> styleStock = new HashMap<>();
|
|
|
138 |
|
| 149 |
ilm |
139 |
// Ventes
|
| 174 |
ilm |
140 |
final SQLTable foreignTableArticle = tableFactureElement.getForeignTable("ID_ARTICLE");
|
| 149 |
ilm |
141 |
{
|
| 18 |
ilm |
142 |
|
| 149 |
ilm |
143 |
final AliasedTable tableModeReglement1 = new AliasedTable(tableModeReglement, MODE1);
|
|
|
144 |
final AliasedTable tableModeReglement2 = new AliasedTable(tableModeReglement, MODE2);
|
|
|
145 |
final AliasedTable tableTicket = new AliasedTable(eltTicketCaisse.getTable(), "ticket");
|
| 18 |
ilm |
146 |
|
| 174 |
ilm |
147 |
// Stock
|
|
|
148 |
SQLRowValues rowValsArtStock = new SQLRowValues(foreignTableArticle);
|
|
|
149 |
rowValsArtStock.putNulls("ID", "CODE", "NOM");
|
| 182 |
ilm |
150 |
SQLRowValues rowValsStock = new SQLRowValues(tableFactureElement.getTable("STOCK"));
|
|
|
151 |
rowValsStock.putNulls("QTE_REEL", "QTE_TH", "QTE_MIN", "QTE_RECEPT_ATTENTE", "QTE_LIV_ATTENTE").put("ID_ARTICLE", rowValsArtStock);
|
| 174 |
ilm |
152 |
SQLRowValuesListFetcher fetcherStock = SQLRowValuesListFetcher.create(rowValsArtStock);
|
|
|
153 |
List<SQLRowValues> resultStock = fetcherStock.fetch();
|
| 182 |
ilm |
154 |
|
|
|
155 |
ListMap<Integer, SQLRowValues> mapStock = new ListMap<>();
|
| 174 |
ilm |
156 |
for (SQLRowValues sqlRowValues : resultStock) {
|
| 182 |
ilm |
157 |
final Set<SQLRowValues> referentRows = sqlRowValues.getReferentRows(tableFactureElement.getTable("STOCK").getField("ID_ARTICLE"));
|
|
|
158 |
for (SQLRowValues sqlRowValues2 : referentRows) {
|
|
|
159 |
|
|
|
160 |
mapStock.add(sqlRowValues.getID(), sqlRowValues2);
|
|
|
161 |
}
|
| 174 |
ilm |
162 |
}
|
|
|
163 |
|
| 149 |
ilm |
164 |
// Requete Pour obtenir les quantités pour chaque type de réglement
|
|
|
165 |
SQLSelect sel = new SQLSelect();
|
| 18 |
ilm |
166 |
|
| 149 |
ilm |
167 |
sel.addSelect(tableFactureElement.getField("CODE"));
|
|
|
168 |
sel.addSelect(tableFactureElement.getField("NOM"));
|
|
|
169 |
sel.addSelect(tableFactureElement.getField("ID_ARTICLE"));
|
|
|
170 |
sel.addSelect(tableFactureElement.getField("ID_TAXE"));
|
| 18 |
ilm |
171 |
|
| 149 |
ilm |
172 |
// Elements assosciés à une facture
|
|
|
173 |
Where w = new Where(tableFactureElement.getField("ID_TICKET_CAISSE"), "=", tableTicket.getTable().getUndefinedID());
|
|
|
174 |
sel.addJoin("LEFT", tableFactureElement.getField("ID_SAISIE_VENTE_FACTURE")).setWhere(w);
|
|
|
175 |
SQLSelectJoin joinArt = sel.addJoin("LEFT", tableFactureElement.getField("ID_ARTICLE"));
|
|
|
176 |
SQLSelectJoin joinFamArt = sel.addJoin("LEFT", joinArt.getJoinedTable().getField("ID_FAMILLE_ARTICLE"));
|
|
|
177 |
sel.addSelect(joinFamArt.getJoinedTable().getField("NOM"));
|
| 18 |
ilm |
178 |
|
| 149 |
ilm |
179 |
// Elements associés à un ticket de caisse
|
|
|
180 |
Where w2 = new Where(tableFactureElement.getField("ID_SAISIE_VENTE_FACTURE"), "=", 1);
|
|
|
181 |
sel.addJoin("LEFT", tableFacture.getField("ID_MODE_REGLEMENT"), MODE1);
|
|
|
182 |
sel.addJoin("LEFT", tableFactureElement.getField("ID_TICKET_CAISSE"), "ticket").setWhere(w2);
|
| 18 |
ilm |
183 |
|
| 149 |
ilm |
184 |
sel.addBackwardJoin("LEFT", "enc", eltEncaissement.getTable().getField("ID_TICKET_CAISSE"), "ticket");
|
|
|
185 |
sel.addJoin("LEFT", new AliasedField(eltEncaissement.getTable().getField("ID_MODE_REGLEMENT"), "enc"), MODE2);
|
| 58 |
ilm |
186 |
|
| 149 |
ilm |
187 |
final String idTypeReglement1 = tableModeReglement1.getField("ID_TYPE_REGLEMENT").getFieldRef();
|
|
|
188 |
final String idTypeReglement2 = tableModeReglement2.getField("ID_TYPE_REGLEMENT").getFieldRef();
|
| 58 |
ilm |
189 |
|
| 149 |
ilm |
190 |
final String qte = sel.getAlias(tableFactureElement.getField("QTE")).getFieldRef();
|
|
|
191 |
sel.addRawSelect("SUM(CASE WHEN " + idTypeReglement1 + "=2 OR " + idTypeReglement2 + "=2 THEN " + qte + " ELSE 0 END)", "Cheque");
|
|
|
192 |
sel.addRawSelect("SUM(CASE WHEN " + idTypeReglement1 + "=3 OR " + idTypeReglement2 + "=3 THEN " + qte + " ELSE 0 END)", "CB");
|
|
|
193 |
sel.addRawSelect("SUM(CASE WHEN " + idTypeReglement1 + "=4 OR " + idTypeReglement2 + "=4 THEN " + qte + " ELSE 0 END)", "Especes");
|
| 58 |
ilm |
194 |
|
| 149 |
ilm |
195 |
Where w3 = new Where(tableTicket.getField("DATE"), this.du, this.au);
|
|
|
196 |
Where w4 = new Where(tableFacture.getField("DATE"), this.du, this.au);
|
|
|
197 |
Where wN = new Where(tableFactureElement.getField("NIVEAU"), "=", 1);
|
|
|
198 |
if (this.du != null && this.au != null) {
|
|
|
199 |
sel.setWhere(w3.or(w4).and(wN));
|
|
|
200 |
} else {
|
|
|
201 |
sel.setWhere(wN);
|
|
|
202 |
}
|
|
|
203 |
// FIXME traiter le cas du!=null et au==null et vice versa
|
|
|
204 |
sel.addGroupBy(tableFactureElement.getField("NOM"));
|
|
|
205 |
sel.addGroupBy(tableFactureElement.getField("CODE"));
|
|
|
206 |
sel.addGroupBy(tableFactureElement.getField("ID_ARTICLE"));
|
|
|
207 |
sel.addGroupBy(tableFactureElement.getField("ID_TAXE"));
|
|
|
208 |
sel.addGroupBy(joinFamArt.getJoinedTable().getField("NOM"));
|
|
|
209 |
sel.addFieldOrder(joinFamArt.getJoinedTable().getField("NOM"));
|
|
|
210 |
System.err.println(sel.asString());
|
| 58 |
ilm |
211 |
|
| 149 |
ilm |
212 |
// Requete pour obtenir les quantités vendus
|
|
|
213 |
SQLSelect selQte = new SQLSelect();
|
|
|
214 |
selQte.addSelect(tableFactureElement.getField("CODE"));
|
|
|
215 |
selQte.addSelect(tableFactureElement.getField("NOM"));
|
|
|
216 |
selQte.addSelect(tableFactureElement.getField("QTE"), "SUM");
|
|
|
217 |
selQte.addSelect(tableFactureElement.getField("T_PA_HT"), "SUM");
|
|
|
218 |
selQte.addSelect(tableFactureElement.getField("T_PV_HT"), "SUM");
|
|
|
219 |
selQte.addSelect(tableFactureElement.getField("T_PV_TTC"), "SUM");
|
|
|
220 |
selQte.addSelect(tableFactureElement.getField("ID_TAXE"));
|
|
|
221 |
selQte.addSelect(tableFactureElement.getField("ID_ARTICLE"));
|
| 174 |
ilm |
222 |
if (this.ticketCaisse) {
|
|
|
223 |
selQte.addJoin("LEFT", tableFactureElement.getField("ID_SAISIE_VENTE_FACTURE")).setWhere(Where.FALSE);
|
|
|
224 |
selQte.addJoin("LEFT", tableFactureElement.getField("ID_TICKET_CAISSE"), "ticket").setWhere(w2);
|
|
|
225 |
} else if (this.facture) {
|
| 149 |
ilm |
226 |
selQte.addJoin("LEFT", tableFactureElement.getField("ID_SAISIE_VENTE_FACTURE")).setWhere(w);
|
| 174 |
ilm |
227 |
selQte.addJoin("LEFT", tableFactureElement.getField("ID_TICKET_CAISSE"), "ticket").setWhere(Where.FALSE);
|
| 149 |
ilm |
228 |
} else {
|
| 174 |
ilm |
229 |
selQte.addJoin("LEFT", tableFactureElement.getField("ID_SAISIE_VENTE_FACTURE")).setWhere(w);
|
|
|
230 |
selQte.addJoin("LEFT", tableFactureElement.getField("ID_TICKET_CAISSE"), "ticket").setWhere(w2);
|
| 149 |
ilm |
231 |
}
|
|
|
232 |
SQLSelectJoin joinArt2 = selQte.addJoin("LEFT", tableFactureElement.getField("ID_ARTICLE"));
|
|
|
233 |
SQLSelectJoin joinFamArt2 = selQte.addJoin("LEFT", joinArt2.getJoinedTable().getField("ID_FAMILLE_ARTICLE"));
|
|
|
234 |
selQte.addSelect(joinFamArt2.getJoinedTable().getField("NOM"));
|
|
|
235 |
// FIXME traiter le cas du!=null et au==null et vice
|
|
|
236 |
// versa
|
|
|
237 |
if (this.du != null && this.au != null) {
|
|
|
238 |
selQte.setWhere(w3.or(w4).and(wN));
|
|
|
239 |
} else {
|
|
|
240 |
selQte.setWhere(wN);
|
|
|
241 |
}
|
|
|
242 |
selQte.addGroupBy(tableFactureElement.getField("NOM"));
|
|
|
243 |
selQte.addGroupBy(tableFactureElement.getField("CODE"));
|
|
|
244 |
selQte.addGroupBy(tableFactureElement.getField("ID_TAXE"));
|
|
|
245 |
selQte.addGroupBy(tableFactureElement.getField("ID_ARTICLE"));
|
|
|
246 |
selQte.addGroupBy(joinFamArt2.getJoinedTable().getField("NOM"));
|
| 18 |
ilm |
247 |
|
| 149 |
ilm |
248 |
List<Object[]> listeQte = (List<Object[]>) Configuration.getInstance().getBase().getDataSource().execute(selQte.asString(), new ArrayListHandler());
|
| 58 |
ilm |
249 |
|
| 149 |
ilm |
250 |
// Récupération des quantités et des montant totaux pour chaque article
|
|
|
251 |
Map<String, ArticleVendu> map = new HashMap<String, ArticleVendu>();
|
|
|
252 |
for (Object[] sqlRow : listeQte) {
|
|
|
253 |
String code = (String) sqlRow[0];
|
|
|
254 |
String nom = (String) sqlRow[1];
|
|
|
255 |
Number qteVendu = (Number) sqlRow[2];
|
|
|
256 |
Number ha = (Number) sqlRow[3];
|
|
|
257 |
Number ht = (Number) sqlRow[4];
|
|
|
258 |
BigDecimal ttc = (BigDecimal) sqlRow[5];
|
|
|
259 |
Number tvaID = (Number) sqlRow[6];
|
|
|
260 |
if (!mapTVAVT.containsKey(tvaID.intValue())) {
|
|
|
261 |
mapTVAVT.put(tvaID.intValue(), Tuple2.create((BigDecimal) ht, ttc));
|
|
|
262 |
} else {
|
|
|
263 |
Tuple2<BigDecimal, BigDecimal> t = mapTVAVT.get(tvaID.intValue());
|
|
|
264 |
mapTVAVT.put(tvaID.intValue(), Tuple2.create(t.get0().add((BigDecimal) ht), t.get1().add(ttc)));
|
|
|
265 |
}
|
|
|
266 |
Number articleID = (Number) sqlRow[7];
|
| 174 |
ilm |
267 |
ArticleVendu a = new ArticleVendu(code, nom, qteVendu.intValue(), (BigDecimal) ht, (BigDecimal) ha, ttc, tvaID.intValue(), foreignTableArticle.getRow(articleID.intValue()));
|
|
|
268 |
|
| 149 |
ilm |
269 |
map.put(articleID + "##" + code + "##" + nom + "##" + tvaID, a);
|
| 58 |
ilm |
270 |
|
| 149 |
ilm |
271 |
}
|
| 58 |
ilm |
272 |
|
| 149 |
ilm |
273 |
List<Object[]> listeIds = (List<Object[]>) Configuration.getInstance().getBase().getDataSource().execute(sel.asString(), new ArrayListHandler());
|
| 18 |
ilm |
274 |
|
| 149 |
ilm |
275 |
if (listeIds == null) {
|
|
|
276 |
return;
|
|
|
277 |
}
|
| 58 |
ilm |
278 |
|
| 149 |
ilm |
279 |
String famille = null;
|
|
|
280 |
|
|
|
281 |
for (Object[] obj : listeIds) {
|
|
|
282 |
final Object valueFam = obj[4];
|
|
|
283 |
if ((valueFam == null && famille == null) || (famille != null && !famille.equalsIgnoreCase("Non classés") && (valueFam == null || valueFam.toString().trim().length() == 0))) {
|
|
|
284 |
famille = "Non classés";
|
|
|
285 |
Map<String, Object> mValues = new HashMap<String, Object>();
|
|
|
286 |
mValues.put("NOM", famille);
|
|
|
287 |
style.put(listValues.size(), "Titre 1");
|
|
|
288 |
listValues.add(mValues);
|
| 174 |
ilm |
289 |
styleStock.put(listValuesStock.size(), "Titre 1");
|
|
|
290 |
listValuesStock.add(mValues);
|
| 149 |
ilm |
291 |
} else if (valueFam != null && !valueFam.toString().equalsIgnoreCase(famille)) {
|
|
|
292 |
famille = valueFam.toString();
|
|
|
293 |
Map<String, Object> mValues = new HashMap<String, Object>();
|
|
|
294 |
mValues.put("NOM", famille);
|
|
|
295 |
style.put(listValues.size(), "Titre 1");
|
|
|
296 |
listValues.add(mValues);
|
| 174 |
ilm |
297 |
styleStock.put(listValuesStock.size(), "Titre 1");
|
|
|
298 |
listValuesStock.add(mValues);
|
| 149 |
ilm |
299 |
}
|
|
|
300 |
|
|
|
301 |
Map<String, Object> mValues = new HashMap<String, Object>();
|
|
|
302 |
|
|
|
303 |
String code = (String) obj[0];
|
|
|
304 |
String nom = (String) obj[1];
|
|
|
305 |
Number articleID = (Number) obj[2];
|
|
|
306 |
Number taxeID = (Number) obj[3];
|
|
|
307 |
ArticleVendu a = map.get(articleID + "##" + code + "##" + nom + "##" + taxeID);
|
|
|
308 |
if (a == null) {
|
|
|
309 |
System.err.println("Aucune correspondance pour l'article " + articleID + "##" + code + "##" + nom + "##" + taxeID);
|
|
|
310 |
}
|
|
|
311 |
if (a != null && a.ttc != null && (a.ttc.signum() != 0 || (articleID != null && articleID.intValue() > 1))) {
|
|
|
312 |
mValues.put("CODE", code);
|
|
|
313 |
mValues.put("NOM", nom);
|
|
|
314 |
mValues.put("QTE", a.qte);
|
|
|
315 |
mValues.put("T_PA", a.ha);
|
|
|
316 |
mValues.put("T_PV_HT", a.ht);
|
|
|
317 |
mValues.put("TVA_TAUX", a.tva);
|
|
|
318 |
mValues.put("NUMERO_COMPTE", a.numeroCompte);
|
|
|
319 |
mValues.put("T_PV_TTC", a.ttc);
|
|
|
320 |
mValues.put("NB_CHEQUE", obj[5]);
|
|
|
321 |
mValues.put("NB_CB", obj[6]);
|
|
|
322 |
mValues.put("NB_ESPECES", obj[7]);
|
|
|
323 |
mValues.put("FAMILLE", valueFam);
|
|
|
324 |
totalTPA = totalTPA.add(a.ha);
|
|
|
325 |
totalTPVTTC = totalTPVTTC.add(a.ttc);
|
|
|
326 |
style.put(listValues.size(), "Normal");
|
|
|
327 |
listValues.add(mValues);
|
| 174 |
ilm |
328 |
|
|
|
329 |
Map<String, Object> mValuesStock = new HashMap<String, Object>();
|
|
|
330 |
mValuesStock.put("CODE", code);
|
|
|
331 |
mValuesStock.put("NOM", nom);
|
|
|
332 |
mValuesStock.put("QTE", a.qte);
|
|
|
333 |
if (mapStock.containsKey(articleID)) {
|
| 182 |
ilm |
334 |
List<SQLRowValues> rowValsArt = mapStock.get(articleID);
|
|
|
335 |
BigDecimal totalTh = BigDecimal.ZERO;
|
|
|
336 |
BigDecimal totalReel = BigDecimal.ZERO;
|
|
|
337 |
BigDecimal min = BigDecimal.ZERO;
|
|
|
338 |
BigDecimal attentR = BigDecimal.ZERO;
|
|
|
339 |
BigDecimal attenteL = BigDecimal.ZERO;
|
|
|
340 |
for (SQLRowValues rowStock : rowValsArt) {
|
|
|
341 |
totalTh = totalTh.add(new BigDecimal(rowStock.getFloat("QTE_TH")));
|
|
|
342 |
totalReel = totalReel.add(new BigDecimal(rowStock.getFloat("QTE_REEL")));
|
|
|
343 |
min = min.add(new BigDecimal(rowStock.getFloat("QTE_MIN")));
|
|
|
344 |
attentR = attentR.add(new BigDecimal(rowStock.getFloat("QTE_RECEPT_ATTENTE")));
|
|
|
345 |
attenteL = attenteL.add(new BigDecimal(rowStock.getFloat("QTE_LIV_ATTENTE")));
|
| 174 |
ilm |
346 |
}
|
| 182 |
ilm |
347 |
|
|
|
348 |
mValuesStock.put("QTE_TH", totalTh);
|
|
|
349 |
mValuesStock.put("QTE_REEL", totalReel);
|
|
|
350 |
mValuesStock.put("QTE_MIN", min);
|
|
|
351 |
mValuesStock.put("QTE_RECEPT_ATTENTE", attentR);
|
|
|
352 |
mValuesStock.put("QTE_LIV_ATTENTE", attenteL);
|
| 174 |
ilm |
353 |
styleStock.put(listValuesStock.size(), "Normal");
|
|
|
354 |
listValuesStock.add(mValuesStock);
|
| 182 |
ilm |
355 |
|
| 174 |
ilm |
356 |
}
|
|
|
357 |
|
| 149 |
ilm |
358 |
}
|
|
|
359 |
// System.out.println("EtatVentesXmlSheet.createListeValues():" + listValues);
|
|
|
360 |
}
|
| 18 |
ilm |
361 |
}
|
|
|
362 |
// Liste des ventes comptoirs
|
| 58 |
ilm |
363 |
final SQLTable venteComptoirT = directory.getElement("SAISIE_VENTE_COMPTOIR").getTable();
|
| 149 |
ilm |
364 |
SQLSelect selVC = new SQLSelect();
|
| 18 |
ilm |
365 |
selVC.addSelect(venteComptoirT.getField("NOM"));
|
|
|
366 |
selVC.addSelect(venteComptoirT.getField("MONTANT_HT"), "SUM");
|
|
|
367 |
selVC.addSelect(venteComptoirT.getField("MONTANT_TTC"), "SUM");
|
|
|
368 |
selVC.addSelect(venteComptoirT.getField("NOM"), "COUNT");
|
| 149 |
ilm |
369 |
selVC.addSelect(venteComptoirT.getField("ID_TAXE"));
|
| 80 |
ilm |
370 |
|
|
|
371 |
if (this.du != null && this.au != null) {
|
|
|
372 |
Where wVC = new Where(venteComptoirT.getField("DATE"), this.du, this.au);
|
|
|
373 |
wVC = wVC.and(new Where(venteComptoirT.getField("ID_ARTICLE"), "=", venteComptoirT.getForeignTable("ID_ARTICLE").getKey()));
|
|
|
374 |
selVC.setWhere(wVC);
|
|
|
375 |
} else {
|
|
|
376 |
selVC.setWhere(new Where(venteComptoirT.getField("ID_ARTICLE"), "=", venteComptoirT.getForeignTable("ID_ARTICLE").getKey()));
|
|
|
377 |
}
|
|
|
378 |
// FIXME traiter le cas du!=null et au==null et vice versa
|
| 18 |
ilm |
379 |
selVC.addGroupBy(venteComptoirT.getField("NOM"));
|
| 149 |
ilm |
380 |
selVC.addGroupBy(venteComptoirT.getField("ID_TAXE"));
|
| 18 |
ilm |
381 |
List<Object[]> listVC = (List<Object[]>) venteComptoirT.getDBSystemRoot().getDataSource().execute(selVC.asString(), new ArrayListHandler());
|
| 58 |
ilm |
382 |
long totalVCInCents = 0;
|
| 18 |
ilm |
383 |
if (listVC.size() > 0) {
|
|
|
384 |
Map<String, Object> mValues = new HashMap<String, Object>();
|
|
|
385 |
mValues.put("NOM", " ");
|
|
|
386 |
listValues.add(mValues);
|
|
|
387 |
|
|
|
388 |
Map<String, Object> mValues2 = new HashMap<String, Object>();
|
|
|
389 |
if (listVC.size() > 1) {
|
|
|
390 |
mValues2.put("NOM", "VENTES COMPTOIR");
|
|
|
391 |
} else {
|
|
|
392 |
mValues2.put("NOM", "VENTE COMPTOIR");
|
|
|
393 |
}
|
|
|
394 |
|
|
|
395 |
style.put(listValues.size(), "Titre 1");
|
|
|
396 |
|
|
|
397 |
listValues.add(mValues2);
|
|
|
398 |
|
|
|
399 |
}
|
| 58 |
ilm |
400 |
for (Object[] rowVenteComptoir : listVC) {
|
|
|
401 |
final Map<String, Object> mValues = new HashMap<String, Object>();
|
|
|
402 |
// Nom
|
|
|
403 |
mValues.put("NOM", rowVenteComptoir[0]);
|
|
|
404 |
// HT
|
| 149 |
ilm |
405 |
final long ht = ((Number) rowVenteComptoir[1]).longValue();
|
|
|
406 |
mValues.put("T_PV_HT", ht / 100.0D);
|
| 58 |
ilm |
407 |
// TTC
|
|
|
408 |
final long ttcInCents = ((Number) rowVenteComptoir[2]).longValue();
|
|
|
409 |
mValues.put("T_PV_TTC", ttcInCents / 100.0D);
|
|
|
410 |
totalVCInCents += ttcInCents;
|
| 149 |
ilm |
411 |
|
|
|
412 |
final Integer idTaxe = ((Number) rowVenteComptoir[4]).intValue();
|
|
|
413 |
mValues.put("ID_TAXE", idTaxe);
|
|
|
414 |
if (!mapTVAVT.containsKey(idTaxe)) {
|
|
|
415 |
mapTVAVT.put(idTaxe, Tuple2.create(new BigDecimal(ht).movePointLeft(2), new BigDecimal(ttcInCents).movePointLeft(2)));
|
|
|
416 |
} else {
|
|
|
417 |
Tuple2<BigDecimal, BigDecimal> t = mapTVAVT.get(idTaxe);
|
|
|
418 |
mapTVAVT.put(idTaxe, Tuple2.create(t.get0().add(new BigDecimal(ht).movePointLeft(2)), t.get1().add(new BigDecimal(ttcInCents).movePointLeft(2))));
|
|
|
419 |
}
|
|
|
420 |
|
| 58 |
ilm |
421 |
// Quantité
|
|
|
422 |
mValues.put("QTE", rowVenteComptoir[3]);
|
| 18 |
ilm |
423 |
listValues.add(mValues);
|
|
|
424 |
}
|
| 149 |
ilm |
425 |
// Avoir
|
|
|
426 |
{
|
| 18 |
ilm |
427 |
|
| 149 |
ilm |
428 |
SQLTable tableAvoirElement = directory.getElement("AVOIR_CLIENT_ELEMENT").getTable();
|
|
|
429 |
|
|
|
430 |
// Requete Pour obtenir les quantités pour chaque type de réglement
|
|
|
431 |
SQLSelect sel = new SQLSelect();
|
|
|
432 |
|
|
|
433 |
sel.addSelect(tableAvoirElement.getField("CODE"));
|
|
|
434 |
sel.addSelect(tableAvoirElement.getField("NOM"));
|
|
|
435 |
sel.addSelect(tableAvoirElement.getField("ID_ARTICLE"));
|
|
|
436 |
sel.addSelect(tableAvoirElement.getField("ID_TAXE"));
|
|
|
437 |
|
|
|
438 |
// Elements assosciés à une facture
|
|
|
439 |
sel.addJoin("LEFT", tableAvoirElement.getField("ID_AVOIR_CLIENT"));
|
|
|
440 |
SQLSelectJoin joinArt = sel.addJoin("LEFT", tableAvoirElement.getField("ID_ARTICLE"));
|
|
|
441 |
SQLSelectJoin joinFamArt = sel.addJoin("LEFT", joinArt.getJoinedTable().getField("ID_FAMILLE_ARTICLE"));
|
|
|
442 |
sel.addSelect(joinFamArt.getJoinedTable().getField("NOM"));
|
|
|
443 |
|
|
|
444 |
Where w4 = new Where(tableAvoirElement.getTable("AVOIR_CLIENT").getField("DATE"), this.du, this.au);
|
|
|
445 |
Where wN = new Where(tableAvoirElement.getField("NIVEAU"), "=", 1);
|
|
|
446 |
if (this.du != null && this.au != null) {
|
|
|
447 |
sel.setWhere(w4.and(wN));
|
|
|
448 |
} else {
|
|
|
449 |
sel.setWhere(wN);
|
|
|
450 |
}
|
|
|
451 |
// FIXME traiter le cas du!=null et au==null et vice versa
|
|
|
452 |
sel.addGroupBy(tableAvoirElement.getField("NOM"));
|
|
|
453 |
sel.addGroupBy(tableAvoirElement.getField("CODE"));
|
|
|
454 |
sel.addGroupBy(tableAvoirElement.getField("ID_ARTICLE"));
|
|
|
455 |
sel.addGroupBy(tableAvoirElement.getField("ID_TAXE"));
|
|
|
456 |
sel.addGroupBy(joinFamArt.getJoinedTable().getField("NOM"));
|
|
|
457 |
sel.addFieldOrder(joinFamArt.getJoinedTable().getField("NOM"));
|
|
|
458 |
System.err.println(sel.asString());
|
|
|
459 |
|
|
|
460 |
// Requete pour obtenir les quantités vendus
|
|
|
461 |
SQLSelect selQte = new SQLSelect();
|
|
|
462 |
selQte.addSelect(tableAvoirElement.getField("CODE"));
|
|
|
463 |
selQte.addSelect(tableAvoirElement.getField("NOM"));
|
|
|
464 |
selQte.addSelect(tableAvoirElement.getField("QTE"), "SUM");
|
|
|
465 |
selQte.addSelect(tableAvoirElement.getField("T_PA_HT"), "SUM");
|
|
|
466 |
selQte.addSelect(tableAvoirElement.getField("T_PV_HT"), "SUM");
|
|
|
467 |
selQte.addSelect(tableAvoirElement.getField("T_PV_TTC"), "SUM");
|
|
|
468 |
selQte.addSelect(tableAvoirElement.getField("ID_TAXE"));
|
|
|
469 |
selQte.addSelect(tableAvoirElement.getField("ID_ARTICLE"));
|
|
|
470 |
selQte.addJoin("LEFT", tableAvoirElement.getField("ID_AVOIR_CLIENT"));
|
|
|
471 |
SQLSelectJoin joinArt2 = selQte.addJoin("LEFT", tableAvoirElement.getField("ID_ARTICLE"));
|
|
|
472 |
SQLSelectJoin joinFamArt2 = selQte.addJoin("LEFT", joinArt2.getJoinedTable().getField("ID_FAMILLE_ARTICLE"));
|
|
|
473 |
selQte.addSelect(joinFamArt2.getJoinedTable().getField("NOM"));
|
|
|
474 |
// FIXME traiter le cas du!=null et au==null et vice
|
|
|
475 |
// versa
|
|
|
476 |
if (this.du != null && this.au != null) {
|
|
|
477 |
selQte.setWhere(w4.and(wN));
|
|
|
478 |
} else {
|
|
|
479 |
selQte.setWhere(wN);
|
|
|
480 |
}
|
|
|
481 |
selQte.addGroupBy(tableAvoirElement.getField("NOM"));
|
|
|
482 |
selQte.addGroupBy(tableAvoirElement.getField("CODE"));
|
|
|
483 |
selQte.addGroupBy(tableAvoirElement.getField("ID_TAXE"));
|
|
|
484 |
selQte.addGroupBy(tableAvoirElement.getField("ID_ARTICLE"));
|
|
|
485 |
selQte.addGroupBy(joinFamArt2.getJoinedTable().getField("NOM"));
|
|
|
486 |
|
|
|
487 |
List<Object[]> listeQte = (List<Object[]>) Configuration.getInstance().getBase().getDataSource().execute(selQte.asString(), new ArrayListHandler());
|
|
|
488 |
|
|
|
489 |
// Récupération des quantités et des montant totaux pour chaque article
|
|
|
490 |
Map<String, ArticleVendu> map = new HashMap<String, ArticleVendu>();
|
|
|
491 |
for (Object[] sqlRow : listeQte) {
|
|
|
492 |
String code = (String) sqlRow[0];
|
|
|
493 |
String nom = (String) sqlRow[1];
|
|
|
494 |
Number qteVendu = (Number) sqlRow[2];
|
|
|
495 |
Number ha = (Number) sqlRow[3];
|
|
|
496 |
Number ht = (Number) sqlRow[4];
|
|
|
497 |
BigDecimal ttc = (BigDecimal) sqlRow[5];
|
|
|
498 |
Number tvaID = (Number) sqlRow[6];
|
|
|
499 |
if (!mapTVAVT.containsKey(tvaID.intValue())) {
|
|
|
500 |
mapTVAVT.put(tvaID.intValue(), Tuple2.create(((BigDecimal) ht).negate(), ttc.negate()));
|
|
|
501 |
} else {
|
|
|
502 |
Tuple2<BigDecimal, BigDecimal> t = mapTVAVT.get(tvaID.intValue());
|
|
|
503 |
mapTVAVT.put(tvaID.intValue(), Tuple2.create(t.get0().subtract((BigDecimal) ht), t.get1().subtract(ttc)));
|
|
|
504 |
}
|
|
|
505 |
Number articleID = (Number) sqlRow[7];
|
|
|
506 |
ArticleVendu a = new ArticleVendu(code, nom, -qteVendu.intValue(), ((BigDecimal) ht).negate(), ((BigDecimal) ha).negate(), ttc.negate(), tvaID.intValue(),
|
| 174 |
ilm |
507 |
foreignTableArticle.getRow(articleID.intValue()));
|
| 149 |
ilm |
508 |
map.put(articleID + "##" + code + "##" + nom + "##" + tvaID, a);
|
|
|
509 |
|
|
|
510 |
}
|
|
|
511 |
|
|
|
512 |
List<Object[]> listeIds = (List<Object[]>) Configuration.getInstance().getBase().getDataSource().execute(sel.asString(), new ArrayListHandler());
|
|
|
513 |
|
|
|
514 |
if (listeIds == null) {
|
|
|
515 |
return;
|
|
|
516 |
}
|
|
|
517 |
|
|
|
518 |
Map<String, Object> mValuesA = new HashMap<String, Object>();
|
|
|
519 |
mValuesA.put("NOM", "AVOIRS");
|
|
|
520 |
style.put(listValues.size(), "Titre 1");
|
|
|
521 |
listValues.add(mValuesA);
|
|
|
522 |
|
|
|
523 |
for (Object[] obj : listeIds) {
|
|
|
524 |
final Object valueFam = obj[4];
|
|
|
525 |
|
|
|
526 |
Map<String, Object> mValues = new HashMap<String, Object>();
|
|
|
527 |
|
|
|
528 |
String code = (String) obj[0];
|
|
|
529 |
String nom = (String) obj[1];
|
|
|
530 |
Number articleID = (Number) obj[2];
|
|
|
531 |
Number taxeID = (Number) obj[3];
|
|
|
532 |
ArticleVendu a = map.get(articleID + "##" + code + "##" + nom + "##" + taxeID);
|
|
|
533 |
if (a.ttc != null && a.ttc.signum() != 0) {
|
|
|
534 |
mValues.put("CODE", code);
|
|
|
535 |
mValues.put("NOM", nom);
|
|
|
536 |
mValues.put("QTE", a.qte);
|
|
|
537 |
mValues.put("T_PA", a.ha);
|
|
|
538 |
mValues.put("T_PV_HT", a.ht);
|
|
|
539 |
mValues.put("TVA_TAUX", a.tva);
|
|
|
540 |
mValues.put("NUMERO_COMPTE", a.numeroCompte);
|
|
|
541 |
mValues.put("T_PV_TTC", a.ttc);
|
|
|
542 |
mValues.put("FAMILLE", valueFam);
|
|
|
543 |
totalTPA = totalTPA.add(a.ha);
|
|
|
544 |
totalTPVTTC = totalTPVTTC.add(a.ttc);
|
|
|
545 |
style.put(listValues.size(), "Normal");
|
|
|
546 |
listValues.add(mValues);
|
|
|
547 |
}
|
|
|
548 |
// System.out.println("EtatVentesXmlSheet.createListeValues():" + listValues);
|
|
|
549 |
}
|
|
|
550 |
}
|
|
|
551 |
totalTPVTTC = totalTPVTTC.add(new BigDecimal(totalVCInCents).movePointLeft(2));
|
|
|
552 |
|
| 18 |
ilm |
553 |
// Liste des Achats
|
| 149 |
ilm |
554 |
final ArrayList<Map<String, Object>> listValuesAchat = new ArrayList<Map<String, Object>>();
|
| 58 |
ilm |
555 |
Map<String, Object> valuesAchat = this.mapAllSheetValues.get(1);
|
|
|
556 |
if (valuesAchat == null) {
|
|
|
557 |
valuesAchat = new HashMap<String, Object>();
|
| 18 |
ilm |
558 |
}
|
| 149 |
ilm |
559 |
// Saisie Achat
|
|
|
560 |
long totalAchatInCents = 0;
|
|
|
561 |
{
|
|
|
562 |
final SQLElement eltAchat = directory.getElement("SAISIE_ACHAT");
|
|
|
563 |
final SQLTable tableAchat = eltAchat.getTable();
|
|
|
564 |
final SQLSelect selAchat = new SQLSelect();
|
| 18 |
ilm |
565 |
|
| 149 |
ilm |
566 |
selAchat.addSelect(tableAchat.getField("NOM"));
|
|
|
567 |
selAchat.addSelect(tableAchat.getField("MONTANT_HT"), "SUM");
|
|
|
568 |
selAchat.addSelect(tableAchat.getField("MONTANT_TTC"), "SUM");
|
|
|
569 |
selAchat.addSelect(tableAchat.getField("ID_TAXE"));
|
|
|
570 |
final Where wHA = new Where(tableAchat.getField("DATE"), this.du, this.au);
|
|
|
571 |
selAchat.setWhere(wHA);
|
|
|
572 |
selAchat.addGroupBy(tableAchat.getField("NOM"));
|
|
|
573 |
selAchat.addGroupBy(tableAchat.getField("ID_TAXE"));
|
|
|
574 |
List<Object[]> listAchat = (List<Object[]>) Configuration.getInstance().getBase().getDataSource().execute(selAchat.asString(), new ArrayListHandler());
|
| 18 |
ilm |
575 |
|
| 149 |
ilm |
576 |
for (Object[] row : listAchat) {
|
|
|
577 |
Map<String, Object> mValues = new HashMap<String, Object>();
|
|
|
578 |
mValues.put("NOM", row[0]);
|
|
|
579 |
final Integer tvaID = ((Number) row[3]).intValue();
|
|
|
580 |
mValues.put("TAXE", tvaID);
|
|
|
581 |
long ht = ((Number) row[1]).longValue();
|
|
|
582 |
long pA = ((Number) row[2]).longValue();
|
|
|
583 |
if (!mapTVAHA.containsKey(tvaID.intValue())) {
|
|
|
584 |
mapTVAHA.put(tvaID.intValue(), Tuple2.create(new BigDecimal(ht).movePointLeft(2), new BigDecimal(pA).movePointLeft(2)));
|
|
|
585 |
} else {
|
|
|
586 |
Tuple2<BigDecimal, BigDecimal> t = mapTVAHA.get(tvaID.intValue());
|
|
|
587 |
mapTVAHA.put(tvaID.intValue(), Tuple2.create(t.get0().add(new BigDecimal(ht).movePointLeft(2)), t.get1().add(new BigDecimal(pA).movePointLeft(2))));
|
|
|
588 |
}
|
|
|
589 |
mValues.put("T_PV_HT", -ht / 100.0D);
|
|
|
590 |
mValues.put("T_PV_TTC", -pA / 100.0D);
|
|
|
591 |
totalAchatInCents -= pA;
|
|
|
592 |
listValuesAchat.add(mValues);
|
|
|
593 |
}
|
|
|
594 |
}
|
|
|
595 |
// Facture Fournisseur
|
|
|
596 |
{
|
|
|
597 |
final SQLElement eltFactElt = directory.getElement("FACTURE_FOURNISSEUR_ELEMENT");
|
|
|
598 |
final SQLTable tableFactElt = eltFactElt.getTable();
|
|
|
599 |
final SQLTable tableFactF = eltFactElt.getTable().getForeignTable("ID_FACTURE_FOURNISSEUR");
|
|
|
600 |
final SQLSelect selAchat = new SQLSelect();
|
|
|
601 |
selAchat.addSelect(tableFactElt.getField("ID_ARTICLE"));
|
|
|
602 |
selAchat.addSelect(tableFactElt.getField("CODE"));
|
|
|
603 |
selAchat.addSelect(tableFactElt.getField("NOM"));
|
|
|
604 |
selAchat.addSelect(tableFactElt.getField("T_PA_HT"), "SUM");
|
|
|
605 |
selAchat.addSelect(tableFactElt.getField("T_PA_TTC"), "SUM");
|
|
|
606 |
selAchat.addSelect(tableFactElt.getField("ID_TAXE"));
|
|
|
607 |
selAchat.addSelect(tableFactElt.getField("QTE"), "SUM");
|
|
|
608 |
selAchat.addJoin("LEFT", tableFactElt.getField("ID_FACTURE_FOURNISSEUR"));
|
|
|
609 |
selAchat.addGroupBy(tableFactElt.getField("ID_ARTICLE"));
|
|
|
610 |
selAchat.addGroupBy(tableFactElt.getField("CODE"));
|
|
|
611 |
selAchat.addGroupBy(tableFactElt.getField("NOM"));
|
|
|
612 |
selAchat.addGroupBy(tableFactElt.getField("ID_TAXE"));
|
| 18 |
ilm |
613 |
|
| 149 |
ilm |
614 |
Where wFactF = new Where(tableFactF.getField("DATE"), this.du, this.au);
|
|
|
615 |
selAchat.setWhere(wFactF.and(new Where(tableFactElt.getField("NIVEAU"), "=", 1)));
|
|
|
616 |
List<Object[]> listAchat = (List<Object[]>) Configuration.getInstance().getBase().getDataSource().execute(selAchat.asString(), new ArrayListHandler());
|
|
|
617 |
|
|
|
618 |
for (Object[] row : listAchat) {
|
|
|
619 |
Map<String, Object> mValues = new HashMap<String, Object>();
|
|
|
620 |
mValues.put("NOM", row[2]);
|
|
|
621 |
final Integer tvaID = ((Number) row[5]).intValue();
|
|
|
622 |
mValues.put("TAXE", tvaID);
|
|
|
623 |
BigDecimal ht = ((BigDecimal) row[3]);
|
|
|
624 |
BigDecimal pA = ((BigDecimal) row[4]);
|
|
|
625 |
if (!mapTVAHA.containsKey(tvaID.intValue())) {
|
|
|
626 |
mapTVAHA.put(tvaID.intValue(), Tuple2.create(ht, pA));
|
|
|
627 |
} else {
|
|
|
628 |
Tuple2<BigDecimal, BigDecimal> t = mapTVAHA.get(tvaID.intValue());
|
|
|
629 |
mapTVAHA.put(tvaID.intValue(), Tuple2.create(t.get0().add(ht), t.get1().add(pA)));
|
|
|
630 |
}
|
|
|
631 |
mValues.put("T_PV_HT", ht.negate());
|
|
|
632 |
mValues.put("T_PV_TTC", pA.negate());
|
|
|
633 |
mValues.put("QTE", row[6]);
|
|
|
634 |
totalAchatInCents -= pA.movePointRight(2).setScale(0, RoundingMode.HALF_UP).longValue();
|
|
|
635 |
listValuesAchat.add(mValues);
|
|
|
636 |
}
|
| 18 |
ilm |
637 |
}
|
|
|
638 |
|
|
|
639 |
// Récapitulatif
|
|
|
640 |
Map<String, Object> valuesE = this.mapAllSheetValues.get(2);
|
|
|
641 |
if (valuesE == null) {
|
|
|
642 |
valuesE = new HashMap<String, Object>();
|
|
|
643 |
}
|
| 58 |
ilm |
644 |
SQLElement eltE = directory.getElement("ENCAISSER_MONTANT");
|
|
|
645 |
SQLElement eltM = directory.getElement("MODE_REGLEMENT");
|
|
|
646 |
SQLElement eltT = directory.getElement("TYPE_REGLEMENT");
|
| 149 |
ilm |
647 |
SQLSelect selE = new SQLSelect();
|
| 18 |
ilm |
648 |
selE.addSelect(eltT.getTable().getField("NOM"));
|
|
|
649 |
selE.addSelect(eltT.getTable().getField("NOM"), "COUNT");
|
|
|
650 |
selE.addSelect(eltE.getTable().getField("MONTANT"), "SUM");
|
|
|
651 |
Where wE = new Where(eltE.getTable().getField("DATE"), this.du, this.au);
|
|
|
652 |
wE = wE.and(new Where(eltE.getTable().getField("ID_MODE_REGLEMENT"), "=", eltM.getTable().getKey()));
|
|
|
653 |
wE = wE.and(new Where(eltM.getTable().getField("ID_TYPE_REGLEMENT"), "=", eltT.getTable().getKey()));
|
|
|
654 |
selE.setWhere(wE);
|
|
|
655 |
selE.addGroupBy(eltT.getTable().getField("NOM"));
|
|
|
656 |
selE.addFieldOrder(eltT.getTable().getField("NOM"));
|
|
|
657 |
List<Object[]> listE = (List<Object[]>) Configuration.getInstance().getBase().getDataSource().execute(selE.asString(), new ArrayListHandler());
|
| 149 |
ilm |
658 |
ArrayList<Map<String, Object>> listValuesE = new ArrayList<Map<String, Object>>();
|
| 58 |
ilm |
659 |
long totalEInCents = 0;
|
| 18 |
ilm |
660 |
|
|
|
661 |
for (Object[] o : listE) {
|
|
|
662 |
Map<String, Object> mValues = new HashMap<String, Object>();
|
|
|
663 |
|
|
|
664 |
mValues.put("NOM", o[0]);
|
|
|
665 |
|
| 58 |
ilm |
666 |
final long pA = ((Number) o[2]).longValue();
|
| 18 |
ilm |
667 |
mValues.put("QTE", o[1]);
|
| 58 |
ilm |
668 |
mValues.put("TOTAL", pA / 100.0D);
|
| 18 |
ilm |
669 |
|
| 58 |
ilm |
670 |
totalEInCents += pA;
|
| 18 |
ilm |
671 |
listValuesE.add(mValues);
|
|
|
672 |
}
|
|
|
673 |
|
|
|
674 |
Map<String, Object> values = this.mapAllSheetValues.get(0);
|
|
|
675 |
if (values == null) {
|
|
|
676 |
values = new HashMap<String, Object>();
|
|
|
677 |
}
|
| 58 |
ilm |
678 |
valuesAchat.put("TOTAL", totalAchatInCents / 100f);
|
|
|
679 |
valuesE.put("TOTAL_HA", totalAchatInCents / 100f);
|
|
|
680 |
valuesE.put("TOTAL", totalEInCents / 100f);
|
| 67 |
ilm |
681 |
valuesE.put("TOTAL_VT", totalTPVTTC);
|
| 58 |
ilm |
682 |
values.put("TOTAL", totalVCInCents / 100f);
|
| 67 |
ilm |
683 |
values.put("TOTAL_MARGE", totalTPVTTC.subtract(totalTPA));
|
|
|
684 |
valuesE.put("TOTAL_GLOBAL", totalTPVTTC.add(new BigDecimal(totalAchatInCents).movePointLeft(2)));
|
|
|
685 |
values.put("TOTAL_PA", totalTPA);
|
|
|
686 |
values.put("TOTAL_PV_TTC", totalTPVTTC);
|
| 80 |
ilm |
687 |
String periode = "";
|
| 93 |
ilm |
688 |
final DateFormat dateFormat = new SimpleDateFormat("dd/MM/yy");
|
| 80 |
ilm |
689 |
if (this.du != null && this.au != null) {
|
| 93 |
ilm |
690 |
periode = "Période du " + dateFormat.format(this.du) + " au " + dateFormat.format(this.au);
|
| 80 |
ilm |
691 |
} else if (du == null && au != null) {
|
| 93 |
ilm |
692 |
periode = "Période jusqu'au " + dateFormat.format(this.au);
|
|
|
693 |
} else if (du != null && au == null) {
|
|
|
694 |
periode = "Période depuis le " + dateFormat.format(this.du);
|
| 80 |
ilm |
695 |
}
|
| 18 |
ilm |
696 |
|
|
|
697 |
values.put("DATE", periode);
|
| 58 |
ilm |
698 |
valuesAchat.put("DATE", periode);
|
| 18 |
ilm |
699 |
valuesE.put("DATE", periode);
|
| 149 |
ilm |
700 |
|
|
|
701 |
Map<String, Object> valuesTotalVT = new HashMap<String, Object>();
|
|
|
702 |
valuesTotalVT.put("T_MARGE", "Total");
|
|
|
703 |
valuesTotalVT.put("T_PV_TTC", totalTPVTTC);
|
|
|
704 |
style.put(listValues.size(), "Titre 2");
|
|
|
705 |
listValues.add(valuesTotalVT);
|
| 18 |
ilm |
706 |
System.err.println(this.du);
|
|
|
707 |
System.err.println(this.au);
|
|
|
708 |
this.listAllSheetValues.put(0, listValues);
|
|
|
709 |
this.mapAllSheetValues.put(0, values);
|
|
|
710 |
|
| 58 |
ilm |
711 |
this.listAllSheetValues.put(1, listValuesAchat);
|
|
|
712 |
this.mapAllSheetValues.put(1, valuesAchat);
|
| 18 |
ilm |
713 |
|
|
|
714 |
this.listAllSheetValues.put(2, listValuesE);
|
|
|
715 |
this.mapAllSheetValues.put(2, valuesE);
|
|
|
716 |
|
| 149 |
ilm |
717 |
// Recap TVA
|
|
|
718 |
Map<Integer, String> styleTVA = styleAllSheetValues.get(3);
|
|
|
719 |
if (styleTVA == null) {
|
|
|
720 |
styleTVA = new HashMap<Integer, String>();
|
|
|
721 |
styleAllSheetValues.put(3, styleTVA);
|
|
|
722 |
}
|
|
|
723 |
ArrayList<Map<String, Object>> listValuesTVA = new ArrayList<Map<String, Object>>();
|
|
|
724 |
BigDecimal totalHT = BigDecimal.ZERO;
|
|
|
725 |
BigDecimal totalTTC = BigDecimal.ZERO;
|
|
|
726 |
BigDecimal totalHTHA = BigDecimal.ZERO;
|
|
|
727 |
BigDecimal totalTTCHA = BigDecimal.ZERO;
|
|
|
728 |
for (Integer idTaxe : mapTVAVT.keySet()) {
|
|
|
729 |
Map<String, Object> vals = new HashMap<String, Object>();
|
|
|
730 |
vals.put("VT_TAUX_TVA", TaxeCache.getCache().getTauxFromId(idTaxe));
|
|
|
731 |
BigDecimal ht = mapTVAVT.get(idTaxe).get0();
|
|
|
732 |
BigDecimal ttc = mapTVAVT.get(idTaxe).get1();
|
|
|
733 |
totalHT = totalHT.add(ht);
|
|
|
734 |
totalTTC = totalTTC.add(ttc);
|
|
|
735 |
vals.put("VT_HT", ht);
|
|
|
736 |
vals.put("VT_TVA", ttc.subtract(ht));
|
|
|
737 |
vals.put("VT_TTC", ttc);
|
|
|
738 |
if (mapTVAHA.containsKey(idTaxe)) {
|
|
|
739 |
BigDecimal haht = mapTVAHA.get(idTaxe).get0();
|
|
|
740 |
BigDecimal hattc = mapTVAHA.get(idTaxe).get1();
|
|
|
741 |
vals.put("HA_HT", haht);
|
|
|
742 |
vals.put("HA_TVA", hattc.subtract(haht));
|
|
|
743 |
vals.put("HA_TTC", hattc);
|
|
|
744 |
totalHTHA = totalHTHA.add(haht);
|
|
|
745 |
totalTTCHA = totalTTCHA.add(hattc);
|
|
|
746 |
mapTVAHA.remove(idTaxe);
|
|
|
747 |
}
|
|
|
748 |
styleTVA.put(listValuesTVA.size(), "Normal");
|
|
|
749 |
listValuesTVA.add(vals);
|
|
|
750 |
}
|
|
|
751 |
for (Integer idTaxe : mapTVAHA.keySet()) {
|
|
|
752 |
Map<String, Object> vals = new HashMap<String, Object>();
|
|
|
753 |
BigDecimal haht = mapTVAHA.get(idTaxe).get0();
|
|
|
754 |
BigDecimal hattc = mapTVAHA.get(idTaxe).get1();
|
|
|
755 |
vals.put("VT_TAUX_TVA", TaxeCache.getCache().getTauxFromId(idTaxe));
|
|
|
756 |
vals.put("HA_HT", haht);
|
|
|
757 |
vals.put("HA_TVA", hattc.subtract(haht));
|
|
|
758 |
vals.put("HA_TTC", hattc);
|
|
|
759 |
totalHTHA = totalHTHA.add(haht);
|
|
|
760 |
totalTTCHA = totalTTCHA.add(hattc);
|
|
|
761 |
styleTVA.put(listValuesTVA.size(), "Normal");
|
|
|
762 |
listValuesTVA.add(vals);
|
|
|
763 |
}
|
|
|
764 |
Map<String, Object> vals = new HashMap<String, Object>();
|
|
|
765 |
vals.put("VT_TAUX_TVA", "Total");
|
|
|
766 |
vals.put("VT_HT", totalHT);
|
|
|
767 |
vals.put("VT_TVA", totalTTC.subtract(totalHT));
|
|
|
768 |
vals.put("VT_TTC", totalTTC);
|
|
|
769 |
vals.put("HA_HT", totalHTHA);
|
|
|
770 |
vals.put("HA_TVA", totalTTCHA.subtract(totalHTHA));
|
|
|
771 |
vals.put("HA_TTC", totalTTCHA);
|
|
|
772 |
styleTVA.put(listValuesTVA.size(), "Titre 1");
|
|
|
773 |
listValuesTVA.add(vals);
|
|
|
774 |
|
|
|
775 |
Map<String, Object> valuesTVA = new HashMap<String, Object>();
|
|
|
776 |
valuesTVA.put("TOTAL_TVA", totalTTC.subtract(totalHT).subtract(totalTTCHA.subtract(totalHTHA)));
|
|
|
777 |
this.listAllSheetValues.put(3, listValuesTVA);
|
|
|
778 |
valuesTVA.put("DATE", periode);
|
|
|
779 |
this.mapAllSheetValues.put(3, valuesTVA);
|
| 174 |
ilm |
780 |
|
|
|
781 |
this.listAllSheetValues.put(4, listValuesStock);
|
|
|
782 |
this.styleAllSheetValues.put(4, styleStock);
|
|
|
783 |
this.mapAllSheetValues.put(4, values);
|
| 18 |
ilm |
784 |
}
|
|
|
785 |
|
| 142 |
ilm |
786 |
public static SQLRow rowDefaultCptService, rowDefaultCptProduit;
|
|
|
787 |
static {
|
|
|
788 |
final SQLTable tablePrefCompte = Configuration.getInstance().getRoot().findTable("PREFS_COMPTE");
|
|
|
789 |
final SQLRow rowPrefsCompte = tablePrefCompte.getRow(2);
|
|
|
790 |
rowDefaultCptService = rowPrefsCompte.getForeign("ID_COMPTE_PCE_VENTE_SERVICE");
|
|
|
791 |
if (rowDefaultCptService == null || rowDefaultCptService.isUndefined()) {
|
|
|
792 |
try {
|
|
|
793 |
rowDefaultCptService = ComptePCESQLElement.getRowComptePceDefault("VentesServices");
|
|
|
794 |
} catch (Exception e) {
|
|
|
795 |
e.printStackTrace();
|
|
|
796 |
}
|
|
|
797 |
}
|
|
|
798 |
|
|
|
799 |
rowDefaultCptProduit = rowPrefsCompte.getForeign("ID_COMPTE_PCE_VENTE_PRODUIT");
|
|
|
800 |
if (rowDefaultCptProduit == null || rowDefaultCptProduit.isUndefined()) {
|
|
|
801 |
try {
|
|
|
802 |
rowDefaultCptProduit = ComptePCESQLElement.getRowComptePceDefault("VentesProduits");
|
|
|
803 |
} catch (Exception e) {
|
|
|
804 |
e.printStackTrace();
|
|
|
805 |
}
|
|
|
806 |
}
|
|
|
807 |
}
|
|
|
808 |
|
| 58 |
ilm |
809 |
class ArticleVendu {
|
|
|
810 |
public String code, nom;
|
| 142 |
ilm |
811 |
public int qte, tvaID;
|
|
|
812 |
public BigDecimal ht, ha, ttc, tva;
|
|
|
813 |
public String numeroCompte;
|
| 58 |
ilm |
814 |
|
| 142 |
ilm |
815 |
public ArticleVendu(String code, String nom, int qte, BigDecimal ht, BigDecimal ha, BigDecimal ttc, int tvaID, SQLRow rowArticle) {
|
| 58 |
ilm |
816 |
this.code = code;
|
|
|
817 |
this.nom = nom;
|
|
|
818 |
this.qte = qte;
|
|
|
819 |
this.ht = ht;
|
|
|
820 |
this.ha = ha;
|
|
|
821 |
this.ttc = ttc;
|
| 142 |
ilm |
822 |
this.tvaID = tvaID;
|
|
|
823 |
this.tva = new BigDecimal(TaxeCache.getCache().getTauxFromId(tvaID));
|
|
|
824 |
|
|
|
825 |
SQLRowAccessor rowTVA = TaxeCache.getCache().getRowFromId(tvaID);
|
|
|
826 |
boolean service = rowArticle.getBoolean("SERVICE");
|
|
|
827 |
SQLRowAccessor rowCpt;
|
|
|
828 |
// Total Service
|
|
|
829 |
if (service) {
|
|
|
830 |
rowCpt = rowDefaultCptService;
|
|
|
831 |
if (rowTVA != null && !rowTVA.isForeignEmpty("ID_COMPTE_PCE_VENTE_SERVICE")) {
|
|
|
832 |
rowCpt = rowTVA.getForeign("ID_COMPTE_PCE_VENTE_SERVICE");
|
|
|
833 |
}
|
|
|
834 |
} else {
|
|
|
835 |
rowCpt = rowDefaultCptProduit;
|
|
|
836 |
// Compte defini par défaut dans la TVA
|
|
|
837 |
if (rowTVA != null && !rowTVA.isForeignEmpty("ID_COMPTE_PCE_VENTE")) {
|
|
|
838 |
rowCpt = rowTVA.getForeign("ID_COMPTE_PCE_VENTE");
|
|
|
839 |
}
|
|
|
840 |
|
|
|
841 |
}
|
|
|
842 |
|
|
|
843 |
if (rowArticle != null && !rowArticle.isUndefined()) {
|
|
|
844 |
SQLRowAccessor compteArticle = rowArticle.getForeign("ID_COMPTE_PCE");
|
|
|
845 |
if (compteArticle != null && !compteArticle.isUndefined()) {
|
|
|
846 |
rowCpt = compteArticle;
|
|
|
847 |
} else {
|
|
|
848 |
SQLRowAccessor familleArticle = rowArticle.getForeign("ID_FAMILLE_ARTICLE");
|
|
|
849 |
Set<SQLRowAccessor> unique = new HashSet<SQLRowAccessor>();
|
|
|
850 |
while (familleArticle != null && !familleArticle.isUndefined() && !unique.contains(familleArticle)) {
|
|
|
851 |
|
|
|
852 |
unique.add(familleArticle);
|
|
|
853 |
SQLRowAccessor compteFamilleArticle = familleArticle.getForeign("ID_COMPTE_PCE");
|
|
|
854 |
if (compteFamilleArticle != null && !compteFamilleArticle.isUndefined()) {
|
|
|
855 |
rowCpt = compteFamilleArticle;
|
|
|
856 |
break;
|
|
|
857 |
}
|
|
|
858 |
|
|
|
859 |
familleArticle = familleArticle.getForeign("ID_FAMILLE_ARTICLE_PERE");
|
|
|
860 |
}
|
|
|
861 |
}
|
|
|
862 |
}
|
|
|
863 |
if (rowCpt != null) {
|
|
|
864 |
|
|
|
865 |
this.numeroCompte = rowCpt.getString("NUMERO");
|
|
|
866 |
}
|
| 58 |
ilm |
867 |
}
|
|
|
868 |
}
|
| 18 |
ilm |
869 |
}
|
