18 |
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.generationDoc.gestcomm;
|
|
|
15 |
|
|
|
16 |
import org.openconcerto.erp.generationDoc.AbstractListeSheetXml;
|
25 |
ilm |
17 |
import org.openconcerto.erp.generationDoc.DocumentLocalStorageManager;
|
18 |
ilm |
18 |
import org.openconcerto.erp.generationDoc.SheetXml;
|
|
|
19 |
import org.openconcerto.erp.preferences.PrinterNXProps;
|
|
|
20 |
import org.openconcerto.sql.Configuration;
|
|
|
21 |
import org.openconcerto.sql.element.SQLElement;
|
|
|
22 |
import org.openconcerto.sql.model.AliasedField;
|
|
|
23 |
import org.openconcerto.sql.model.AliasedTable;
|
|
|
24 |
import org.openconcerto.sql.model.SQLSelect;
|
|
|
25 |
import org.openconcerto.sql.model.SQLTable;
|
|
|
26 |
import org.openconcerto.sql.model.Where;
|
|
|
27 |
import org.openconcerto.utils.Tuple2;
|
|
|
28 |
|
25 |
ilm |
29 |
import java.io.File;
|
18 |
ilm |
30 |
import java.sql.Timestamp;
|
|
|
31 |
import java.text.DateFormat;
|
|
|
32 |
import java.text.SimpleDateFormat;
|
|
|
33 |
import java.util.ArrayList;
|
|
|
34 |
import java.util.Date;
|
|
|
35 |
import java.util.HashMap;
|
|
|
36 |
import java.util.List;
|
|
|
37 |
import java.util.Map;
|
|
|
38 |
|
|
|
39 |
import org.apache.commons.dbutils.handlers.ArrayListHandler;
|
|
|
40 |
|
|
|
41 |
/**
|
|
|
42 |
* Statistique des ventes d'articles
|
|
|
43 |
*
|
|
|
44 |
* @author Ludo
|
|
|
45 |
*
|
|
|
46 |
*/
|
|
|
47 |
public class EtatVentesXmlSheet extends AbstractListeSheetXml {
|
|
|
48 |
|
|
|
49 |
private static final DateFormat dateFormat = new SimpleDateFormat("dd/MM/yy");
|
|
|
50 |
|
25 |
ilm |
51 |
public static final String TEMPLATE_ID = "EtatVentes";
|
|
|
52 |
|
|
|
53 |
public static final String TEMPLATE_PROPERTY_NAME = DEFAULT_PROPERTY_NAME;
|
|
|
54 |
|
18 |
ilm |
55 |
private Timestamp du, au;
|
|
|
56 |
|
|
|
57 |
public EtatVentesXmlSheet(Date du, Date au) {
|
|
|
58 |
this.printer = PrinterNXProps.getInstance().getStringProperty("BonPrinter");
|
|
|
59 |
du.setHours(0);
|
|
|
60 |
du.setMinutes(0);
|
|
|
61 |
au.setHours(23);
|
|
|
62 |
au.setMinutes(59);
|
|
|
63 |
this.du = new Timestamp(du.getTime());
|
|
|
64 |
this.au = new Timestamp(au.getTime());
|
|
|
65 |
|
|
|
66 |
}
|
|
|
67 |
|
25 |
ilm |
68 |
|
|
|
69 |
|
21 |
ilm |
70 |
@Override
|
25 |
ilm |
71 |
public String getDefaultTemplateId() {
|
|
|
72 |
return TEMPLATE_ID;
|
21 |
ilm |
73 |
}
|
|
|
74 |
|
25 |
ilm |
75 |
@Override
|
|
|
76 |
public String getName() {
|
|
|
77 |
return "EtatVentes" + new Date().getTime();
|
18 |
ilm |
78 |
}
|
|
|
79 |
|
|
|
80 |
protected void createListeValues() {
|
|
|
81 |
SQLElement elt = Configuration.getInstance().getDirectory().getElement("SAISIE_VENTE_FACTURE_ELEMENT");
|
|
|
82 |
SQLElement elt2 = Configuration.getInstance().getDirectory().getElement("SAISIE_VENTE_FACTURE");
|
|
|
83 |
SQLElement eltEnc = Configuration.getInstance().getDirectory().getElement("ENCAISSER_MONTANT");
|
|
|
84 |
SQLElement elt3 = Configuration.getInstance().getDirectory().getElement("TICKET_CAISSE");
|
|
|
85 |
SQLElement eltMod = Configuration.getInstance().getDirectory().getElement("MODE_REGLEMENT");
|
|
|
86 |
AliasedTable table1 = new AliasedTable(eltMod.getTable(), "mod1");
|
|
|
87 |
AliasedTable tableTicket = new AliasedTable(elt3.getTable(), "ticket");
|
|
|
88 |
AliasedTable table2 = new AliasedTable(eltMod.getTable(), "mod2");
|
|
|
89 |
|
|
|
90 |
// Caisse et facture
|
|
|
91 |
SQLSelect sel = new SQLSelect(Configuration.getInstance().getBase());
|
|
|
92 |
sel.addSelect(elt.getTable().getField("NOM"));
|
|
|
93 |
sel.addSelect(elt.getTable().getField("T_PA_HT"), "SUM");
|
|
|
94 |
sel.addSelect(elt.getTable().getField("T_PV_HT"), "SUM");
|
|
|
95 |
sel.addSelect(elt.getTable().getField("T_PV_TTC"), "SUM");
|
|
|
96 |
|
|
|
97 |
sel.addSelect(elt.getTable().getField("QTE"), "SUM");
|
|
|
98 |
sel.addSelect(elt.getTable().getField("CODE"));
|
|
|
99 |
|
|
|
100 |
Where w = new Where(elt.getTable().getField("ID_TICKET_CAISSE"), "=", 1);
|
|
|
101 |
sel.addJoin("LEFT", elt.getTable().getField("ID_SAISIE_VENTE_FACTURE")).setWhere(w);
|
|
|
102 |
|
|
|
103 |
Where w2 = new Where(elt.getTable().getField("ID_SAISIE_VENTE_FACTURE"), "=", 1);
|
|
|
104 |
|
|
|
105 |
sel.addJoin("LEFT", elt2.getTable().getField("ID_MODE_REGLEMENT"), "mod1");
|
|
|
106 |
|
|
|
107 |
sel.addJoin("LEFT", elt.getTable().getField("ID_TICKET_CAISSE"), "ticket").setWhere(w2);
|
|
|
108 |
|
|
|
109 |
sel.addBackwardJoin("LEFT", "enc", eltEnc.getTable().getField("ID_TICKET_CAISSE"), "ticket");
|
|
|
110 |
sel.addJoin("LEFT", new AliasedField(eltEnc.getTable().getField("ID_MODE_REGLEMENT"), "enc"), "mod2");
|
|
|
111 |
|
|
|
112 |
sel.addRawSelect(
|
|
|
113 |
"SUM(CASE WHEN " + table1.getField("ID_TYPE_REGLEMENT").getFieldRef() + " =2 OR " + table2.getField("ID_TYPE_REGLEMENT").getFieldRef() + "=2 THEN "
|
|
|
114 |
+ sel.getAlias(elt.getTable().getField("QTE")).getFieldRef() + " ELSE 0 END)", "Cheque");
|
|
|
115 |
sel.addRawSelect(
|
|
|
116 |
"SUM(CASE WHEN " + table1.getField("ID_TYPE_REGLEMENT").getFieldRef() + "=3 OR " + table2.getField("ID_TYPE_REGLEMENT").getFieldRef() + "=3 THEN "
|
|
|
117 |
+ sel.getAlias(elt.getTable().getField("QTE")).getFieldRef() + " ELSE 0 END)", "CB");
|
|
|
118 |
sel.addRawSelect(
|
|
|
119 |
"SUM(CASE WHEN " + table1.getField("ID_TYPE_REGLEMENT").getFieldRef() + "=4 OR " + table2.getField("ID_TYPE_REGLEMENT").getFieldRef() + "=4 THEN "
|
|
|
120 |
+ sel.getAlias(elt.getTable().getField("QTE")).getFieldRef() + " ELSE 0 END)", "Especes");
|
|
|
121 |
|
|
|
122 |
Where w3 = new Where(tableTicket.getField("DATE"), this.du, this.au);
|
|
|
123 |
Where w4 = new Where(elt2.getTable().getField("DATE"), this.du, this.au);
|
|
|
124 |
sel.setWhere(w3.or(w4));
|
|
|
125 |
sel.addGroupBy(elt.getTable().getField("NOM"));
|
|
|
126 |
sel.addGroupBy(elt.getTable().getField("CODE"));
|
|
|
127 |
System.err.println(sel.asString());
|
|
|
128 |
List<Object[]> listeIds = (List<Object[]>) Configuration.getInstance().getBase().getDataSource().execute(sel.asString(), new ArrayListHandler());
|
|
|
129 |
|
|
|
130 |
if (listeIds == null) {
|
|
|
131 |
return;
|
|
|
132 |
}
|
|
|
133 |
ArrayList<Map<String, Object>> listValues = new ArrayList<Map<String, Object>>(listeIds.size());
|
|
|
134 |
double totalTPA = 0;
|
|
|
135 |
double totalTPVTTC = 0;
|
|
|
136 |
for (Object[] obj : listeIds) {
|
|
|
137 |
Map<String, Object> mValues = new HashMap<String, Object>();
|
|
|
138 |
mValues.put("NOM", obj[0]);
|
|
|
139 |
mValues.put("QTE", obj[4]);
|
|
|
140 |
final Double tPA = new Double(((Number) obj[1]).longValue() / 100.0);
|
|
|
141 |
mValues.put("T_PA", tPA);
|
|
|
142 |
final Double tPVHT = new Double(((Number) obj[2]).longValue() / 100.0);
|
|
|
143 |
mValues.put("T_PV_HT", tPVHT);
|
|
|
144 |
final Double TPVTTC = new Double(((Number) obj[3]).longValue() / 100.0);
|
|
|
145 |
mValues.put("T_PV_TTC", TPVTTC);
|
|
|
146 |
|
|
|
147 |
mValues.put("NB_CHEQUE", obj[6]);
|
|
|
148 |
mValues.put("NB_CB", obj[7]);
|
|
|
149 |
mValues.put("NB_ESPECES", obj[8]);
|
|
|
150 |
totalTPA += tPA;
|
|
|
151 |
totalTPVTTC += TPVTTC;
|
|
|
152 |
listValues.add(mValues);
|
|
|
153 |
}
|
|
|
154 |
|
|
|
155 |
// Liste des ventes comptoirs
|
|
|
156 |
final SQLTable venteComptoirT = Configuration.getInstance().getDirectory().getElement("SAISIE_VENTE_COMPTOIR").getTable();
|
|
|
157 |
SQLSelect selVC = new SQLSelect(venteComptoirT.getBase());
|
|
|
158 |
selVC.addSelect(venteComptoirT.getField("NOM"));
|
|
|
159 |
selVC.addSelect(venteComptoirT.getField("MONTANT_HT"), "SUM");
|
|
|
160 |
selVC.addSelect(venteComptoirT.getField("MONTANT_TTC"), "SUM");
|
|
|
161 |
selVC.addSelect(venteComptoirT.getField("NOM"), "COUNT");
|
|
|
162 |
Where wVC = new Where(venteComptoirT.getField("DATE"), this.du, this.au);
|
|
|
163 |
wVC = wVC.and(new Where(venteComptoirT.getField("ID_ARTICLE"), "=", venteComptoirT.getForeignTable("ID_ARTICLE").getKey()));
|
|
|
164 |
selVC.setWhere(wVC);
|
|
|
165 |
selVC.addGroupBy(venteComptoirT.getField("NOM"));
|
|
|
166 |
List<Object[]> listVC = (List<Object[]>) venteComptoirT.getDBSystemRoot().getDataSource().execute(selVC.asString(), new ArrayListHandler());
|
|
|
167 |
double totalVC = 0;
|
|
|
168 |
if (listVC.size() > 0) {
|
|
|
169 |
Map<String, Object> mValues = new HashMap<String, Object>();
|
|
|
170 |
mValues.put("NOM", " ");
|
|
|
171 |
listValues.add(mValues);
|
|
|
172 |
|
|
|
173 |
Map<String, Object> mValues2 = new HashMap<String, Object>();
|
|
|
174 |
if (listVC.size() > 1) {
|
|
|
175 |
mValues2.put("NOM", "VENTES COMPTOIR");
|
|
|
176 |
} else {
|
|
|
177 |
mValues2.put("NOM", "VENTE COMPTOIR");
|
|
|
178 |
}
|
|
|
179 |
Map<Integer, String> style = styleAllSheetValues.get(0);
|
|
|
180 |
if (style == null) {
|
|
|
181 |
style = new HashMap<Integer, String>();
|
|
|
182 |
}
|
|
|
183 |
|
|
|
184 |
style.put(listValues.size(), "Titre 1");
|
|
|
185 |
|
|
|
186 |
styleAllSheetValues.put(0, style);
|
|
|
187 |
listValues.add(mValues2);
|
|
|
188 |
|
|
|
189 |
}
|
|
|
190 |
for (Object[] row : listVC) {
|
|
|
191 |
|
|
|
192 |
Map<String, Object> mValues = new HashMap<String, Object>();
|
|
|
193 |
|
|
|
194 |
mValues.put("NOM", row[0]);
|
|
|
195 |
final Double ht = new Double(((Number) row[1]).longValue() / 100.0);
|
|
|
196 |
final Double ttc = new Double(((Number) row[2]).longValue() / 100.0);
|
|
|
197 |
|
|
|
198 |
mValues.put("QTE", row[3]);
|
|
|
199 |
mValues.put("T_PV_HT", ht);
|
|
|
200 |
mValues.put("T_PV_TTC", ttc);
|
|
|
201 |
|
|
|
202 |
totalVC += ttc;
|
|
|
203 |
listValues.add(mValues);
|
|
|
204 |
}
|
|
|
205 |
|
|
|
206 |
// Liste des Achats
|
|
|
207 |
ArrayList<Map<String, Object>> listValuesHA = new ArrayList<Map<String, Object>>(listeIds.size());
|
|
|
208 |
Map<String, Object> valuesHA = this.mapAllSheetValues.get(1);
|
|
|
209 |
if (valuesHA == null) {
|
|
|
210 |
valuesHA = new HashMap<String, Object>();
|
|
|
211 |
}
|
|
|
212 |
SQLElement eltAchat = Configuration.getInstance().getDirectory().getElement("SAISIE_ACHAT");
|
|
|
213 |
|
|
|
214 |
SQLSelect selAchat = new SQLSelect(Configuration.getInstance().getBase());
|
|
|
215 |
selAchat.addSelect(eltAchat.getTable().getField("NOM"));
|
|
|
216 |
selAchat.addSelect(eltAchat.getTable().getField("MONTANT_HT"), "SUM");
|
|
|
217 |
selAchat.addSelect(eltAchat.getTable().getField("MONTANT_TTC"), "SUM");
|
|
|
218 |
Where wHA = new Where(eltAchat.getTable().getField("DATE"), this.du, this.au);
|
|
|
219 |
selAchat.setWhere(wHA);
|
|
|
220 |
selAchat.addGroupBy(eltAchat.getTable().getField("NOM"));
|
|
|
221 |
List<Object[]> listAchat = (List<Object[]>) Configuration.getInstance().getBase().getDataSource().execute(selAchat.asString(), new ArrayListHandler());
|
|
|
222 |
|
|
|
223 |
double totalHA = 0;
|
|
|
224 |
|
|
|
225 |
for (Object[] row : listAchat) {
|
|
|
226 |
|
|
|
227 |
Map<String, Object> mValues = new HashMap<String, Object>();
|
|
|
228 |
|
|
|
229 |
mValues.put("NOM", row[0]);
|
|
|
230 |
final Double ht = new Double(((Number) row[1]).longValue() / 100.0);
|
|
|
231 |
final Double pA = new Double(((Number) row[2]).longValue() / 100.0);
|
|
|
232 |
|
|
|
233 |
mValues.put("T_PV_HT", -ht);
|
|
|
234 |
mValues.put("T_PV_TTC", -pA);
|
|
|
235 |
|
|
|
236 |
totalHA -= pA;
|
|
|
237 |
listValuesHA.add(mValues);
|
|
|
238 |
}
|
|
|
239 |
|
|
|
240 |
totalTPVTTC += totalVC;
|
|
|
241 |
|
|
|
242 |
// Récapitulatif
|
|
|
243 |
Map<String, Object> valuesE = this.mapAllSheetValues.get(2);
|
|
|
244 |
if (valuesE == null) {
|
|
|
245 |
valuesE = new HashMap<String, Object>();
|
|
|
246 |
}
|
|
|
247 |
SQLElement eltE = Configuration.getInstance().getDirectory().getElement("ENCAISSER_MONTANT");
|
|
|
248 |
SQLElement eltM = Configuration.getInstance().getDirectory().getElement("MODE_REGLEMENT");
|
|
|
249 |
SQLElement eltT = Configuration.getInstance().getDirectory().getElement("TYPE_REGLEMENT");
|
|
|
250 |
SQLSelect selE = new SQLSelect(Configuration.getInstance().getBase());
|
|
|
251 |
selE.addSelect(eltT.getTable().getField("NOM"));
|
|
|
252 |
selE.addSelect(eltT.getTable().getField("NOM"), "COUNT");
|
|
|
253 |
selE.addSelect(eltE.getTable().getField("MONTANT"), "SUM");
|
|
|
254 |
Where wE = new Where(eltE.getTable().getField("DATE"), this.du, this.au);
|
|
|
255 |
wE = wE.and(new Where(eltE.getTable().getField("ID_MODE_REGLEMENT"), "=", eltM.getTable().getKey()));
|
|
|
256 |
wE = wE.and(new Where(eltM.getTable().getField("ID_TYPE_REGLEMENT"), "=", eltT.getTable().getKey()));
|
|
|
257 |
selE.setWhere(wE);
|
|
|
258 |
selE.addGroupBy(eltT.getTable().getField("NOM"));
|
|
|
259 |
selE.addFieldOrder(eltT.getTable().getField("NOM"));
|
|
|
260 |
List<Object[]> listE = (List<Object[]>) Configuration.getInstance().getBase().getDataSource().execute(selE.asString(), new ArrayListHandler());
|
|
|
261 |
ArrayList<Map<String, Object>> listValuesE = new ArrayList<Map<String, Object>>(listeIds.size());
|
|
|
262 |
double totalE = 0;
|
|
|
263 |
|
|
|
264 |
for (Object[] o : listE) {
|
|
|
265 |
Map<String, Object> mValues = new HashMap<String, Object>();
|
|
|
266 |
|
|
|
267 |
mValues.put("NOM", o[0]);
|
|
|
268 |
|
|
|
269 |
final Double pA = new Double(((Number) o[2]).longValue() / 100.0);
|
|
|
270 |
mValues.put("QTE", o[1]);
|
|
|
271 |
mValues.put("TOTAL", pA);
|
|
|
272 |
|
|
|
273 |
totalE += pA;
|
|
|
274 |
listValuesE.add(mValues);
|
|
|
275 |
}
|
|
|
276 |
|
|
|
277 |
Map<String, Object> values = this.mapAllSheetValues.get(0);
|
|
|
278 |
if (values == null) {
|
|
|
279 |
values = new HashMap<String, Object>();
|
|
|
280 |
}
|
|
|
281 |
valuesHA.put("TOTAL", totalHA);
|
|
|
282 |
valuesE.put("TOTAL_HA", totalHA);
|
|
|
283 |
valuesE.put("TOTAL", totalE);
|
|
|
284 |
valuesE.put("TOTAL_VT", totalTPVTTC);
|
|
|
285 |
values.put("TOTAL", totalVC);
|
|
|
286 |
values.put("TOTAL_MARGE", totalTPVTTC - totalTPA);
|
|
|
287 |
|
|
|
288 |
valuesE.put("TOTAL_GLOBAL", totalTPVTTC + totalHA);
|
|
|
289 |
values.put("TOTAL_PA", totalTPA);
|
|
|
290 |
values.put("TOTAL_PV_TTC", totalTPVTTC);
|
|
|
291 |
|
|
|
292 |
String periode = "Période Du " + dateFormat.format(this.du) + " au " + dateFormat.format(this.au);
|
|
|
293 |
values.put("DATE", periode);
|
|
|
294 |
valuesHA.put("DATE", periode);
|
|
|
295 |
valuesE.put("DATE", periode);
|
|
|
296 |
System.err.println(this.du);
|
|
|
297 |
System.err.println(this.au);
|
|
|
298 |
this.listAllSheetValues.put(0, listValues);
|
|
|
299 |
this.mapAllSheetValues.put(0, values);
|
|
|
300 |
|
|
|
301 |
this.listAllSheetValues.put(1, listValuesHA);
|
|
|
302 |
this.mapAllSheetValues.put(1, valuesHA);
|
|
|
303 |
|
|
|
304 |
this.listAllSheetValues.put(2, listValuesE);
|
|
|
305 |
this.mapAllSheetValues.put(2, valuesE);
|
|
|
306 |
|
|
|
307 |
}
|
|
|
308 |
|
|
|
309 |
}
|