1 package org.paneris.jammyjoes.controller;
2
3 import java.sql.Date;
4 import java.util.Enumeration;
5 import java.util.HashMap;
6 import java.util.Iterator;
7 import java.util.LinkedList;
8 import java.util.List;
9 import java.util.Map;
10 import java.util.Vector;
11
12 import org.melati.Melati;
13 import org.melati.poem.AccessPoemException;
14 import org.melati.poem.AccessToken;
15 import org.melati.poem.BaseFieldAttributes;
16 import org.melati.poem.Capability;
17 import org.melati.poem.Field;
18 import org.melati.poem.PoemThread;
19 import org.melati.poem.util.EnumUtils;
20 import org.melati.servlet.Form;
21 import org.melati.template.ServletTemplateContext;
22 import org.melati.template.SimpleDateAdaptor;
23 import org.paneris.jammyjoes.model.JammyjoesDatabase;
24 import org.paneris.jammyjoes.model.Product;
25 import org.paneris.jammyjoes.model.StockTransaction;
26 import org.paneris.jammyjoes.model.StockTransactionTypeTable;
27 import org.paneris.jammyjoes.mvp.CollectionSorter;
28 import org.paneris.jammyjoes.servlet.JammyJoesMelatiServlet;
29 import org.paneris.jammyjoes.util.JammyJoesUtil;
30 import org.webmacro.servlet.WebContext;
31
32 public class SalesAnalysis extends JammyJoesMelatiServlet {
33
34 private static final long serialVersionUID = 1L;
35
36 public class StockTransactionSummary implements Comparable {
37
38 private Product product;
39 private StockTransactionTypeTable table;
40 private int internet = 0;
41 private int telephone = 0;
42 private int shop = 0;
43 private double purchases = 0D;
44 private double sales = 0D;
45 private double vatSales = 0D;
46 private double vatPurchases = 0D;
47
48 public double getPurchases() {
49 return purchases;
50 }
51
52 public int getInternet() {
53 return internet;
54 }
55
56 public Product getProduct() {
57 return product;
58 }
59
60 public double getSales() {
61 return sales;
62 }
63
64 public int getShop() {
65 return shop;
66 }
67
68 public int getQuantity() {
69 return shop + internet + telephone;
70 }
71
72 public int getTelephone() {
73 return telephone;
74 }
75
76 public double getVatSales() {
77 return vatSales;
78 }
79
80 public double getVatPurchases() {
81 return vatPurchases;
82 }
83
84 public double getVatToPay() {
85 return vatSales - vatPurchases;
86 }
87
88 public StockTransactionSummary(StockTransactionTypeTable table) {
89 this.table = table;
90 }
91
92 public void add(StockTransaction transaction) {
93 this.product = transaction.getProduct();
94 int quantity = transaction.getQuantity().intValue();
95 if (transaction.getType().equals(table.getInternetSale())) {
96 internet += quantity;
97 }
98 if (transaction.getType().equals(table.getTelephoneSale())) {
99 telephone += quantity;
100 }
101 if (transaction.getType().equals(table.getShopSale())) {
102 shop += quantity;
103 }
104 double thisSales = transaction.getRetailpriceincvat().doubleValue() * quantity;
105 double thisPurchases = transaction.getCostprice().doubleValue() * quantity;
106 if (!transaction.getVatexempt().booleanValue()) {
107 vatSales += thisSales / 1.175 * 0.175;
108 thisPurchases = thisPurchases * 1.175;
109 vatPurchases += thisPurchases / 1.175 * 0.175;
110 }
111 purchases += thisPurchases;
112 sales += thisSales;
113 }
114
115 public int compareTo(Object o) {
116 StockTransactionSummary other = (StockTransactionSummary)o;
117 return other.getProfitDouble().compareTo(this.getProfitDouble());
118 }
119
120 public double getProfit() {
121 return sales - vatSales - purchases +vatPurchases;
122 }
123
124 public double getMargin() {
125 return (getProfit() / sales) * 100;
126 }
127
128 public Double getProfitDouble() {
129 return new Double(getProfit());
130 }
131
132 }
133
134 protected String jammyjoesRequest(Melati melati, ServletTemplateContext context) throws Exception {
135
136 JammyjoesDatabase db = (JammyjoesDatabase) melati.getDatabase();
137 ServletTemplateContext tc = melati.getServletTemplateContext();
138 Capability admin = db.getCanAdminister();
139 AccessToken token = PoemThread.accessToken();
140 if (!token.givesCapability(admin))
141 throw new AccessPoemException(token, admin);
142 String template = "view/SalesAnalysis.wm";
143
144 SimpleDateAdaptor sd = new SimpleDateAdaptor();
145 Date startdate = (Date) sd.rawFrom(tc, "startdate");
146 Date enddate = (Date) sd.rawFrom(tc, "enddate");
147 context.put("startdate", startdate);
148 context.put("enddate", enddate);
149
150 Integer transactionType = Form.getIntegerField(tc, "field_type");
151 Integer supplier = Form.getIntegerField(tc, "field_supplier");
152 Integer manufacturer = Form.getIntegerField(tc, "field_manufacturer");
153
154 context.put(
155 "transactionType",
156 new Field(
157 transactionType,
158 new BaseFieldAttributes(db.getStockTransactionTable().getTypeColumn(), true)));
159
160 context.put(
161 "supplier",
162 new Field(
163 supplier,
164 new BaseFieldAttributes(db.getProductTable().getSupplierColumn(), true)));
165 context.put(
166 "manufacturer",
167 new Field(
168 manufacturer,
169 new BaseFieldAttributes(db.getProductTable().getManufacturerColumn(), true)));
170
171 StockTransactionTypeTable sttt = db.getStockTransactionTypeTable();
172
173 if (Form.getFormNulled(tc, "submit") != null) {
174 Vector where = new Vector();
175 where.add("product = product.id");
176
177 if (transactionType == null) {
178 String typeClause = "(type = " + sttt.getInternetSale().getTroid() + " OR ";
179 typeClause += "type = " + sttt.getTelephoneSale().getTroid() + " OR ";
180 typeClause += "type = " + sttt.getShopSale().getTroid() + ")";
181 where.add(typeClause);
182 } else {
183 where.add("(type = " + transactionType + ")");
184 }
185
186 if (startdate != null) {
187 where.add("date >= " + JammyJoesUtil.formatDateForSQL(db, startdate));
188 }
189 if (enddate != null) {
190 where.add("date < " + JammyJoesUtil.formatDateForSQL(db, enddate));
191 }
192
193 if (supplier != null)
194 where.add("product.supplier = " + supplier);
195 if (manufacturer != null)
196 where.add("product.manufacturer = " + manufacturer);
197
198 String whereClause = EnumUtils.concatenated(" AND ", where.elements());
199 Enumeration r = db.getStockTransactionTable().selection(whereClause, "product.name", false);
200
201 Map products = new HashMap();
202
203 while (r.hasMoreElements()) {
204 StockTransaction transaction = (StockTransaction) r.nextElement();
205 Integer id = transaction.getProduct().getTroid();
206 StockTransactionSummary summary = (StockTransactionSummary)products.get(id);
207 if (summary == null) {
208 summary = new StockTransactionSummary(db.getStockTransactionTypeTable());
209 }
210 summary.add(transaction);
211 products.put(id, summary);
212 }
213
214 int internetTotal = 0;
215 int telephoneTotal = 0;
216 int shopTotal = 0;
217 int stockTotal = 0;
218 double purchasesTotal = 0D;
219 double salesTotal = 0D;
220 double salesVatTotal = 0D;
221 double purchasesVatTotal = 0D;
222
223 List results = new LinkedList();
224 Iterator iter = new CollectionSorter().sort(products).iterator();
225 while (iter.hasNext()) {
226 StockTransactionSummary summary = (StockTransactionSummary) iter.next();
227 results.add(summary);
228 internetTotal += summary.getInternet();
229 telephoneTotal += summary.getTelephone();
230 shopTotal += summary.getShop();
231 purchasesTotal += summary.getPurchases();
232 salesTotal += summary.getSales();
233 salesVatTotal += summary.getVatSales();
234 purchasesVatTotal += summary.getVatPurchases();
235 stockTotal += summary.getProduct().getStocklevel().intValue();
236 }
237 double profitTotal = salesTotal;
238 profitTotal -= salesVatTotal;
239 profitTotal -= purchasesTotal;
240 profitTotal += purchasesVatTotal;
241
242 double marginTotal = (profitTotal / salesTotal) * 100;
243
244 double quantityTotal = internetTotal + telephoneTotal + shopTotal;
245 WebContext webContext = (WebContext)context.getContext();
246
247 webContext.put("internetTotal", internetTotal);
248 webContext.put("telephoneTotal", telephoneTotal);
249 webContext.put("quantityTotal", quantityTotal);
250 webContext.put("stockTotal", stockTotal);
251 webContext.put("shopTotal", shopTotal);
252 webContext.put("purchasesTotal", purchasesTotal);
253 webContext.put("salesTotal", salesTotal);
254 webContext.put("profitTotal", profitTotal);
255 webContext.put("marginTotal", marginTotal);
256 webContext.put("salesVatTotal", salesVatTotal);
257 webContext.put("purchasesVatTotal", purchasesVatTotal);
258 webContext.put("vatToPayTotal", salesVatTotal - purchasesVatTotal);
259 webContext.put("results", results);
260 }
261 return template;
262 }
263
264 }