1 package org.paneris.jammyjoes.controller;
2
3 import java.sql.Date;
4 import java.util.Collections;
5 import java.util.Enumeration;
6 import java.util.LinkedList;
7 import java.util.List;
8 import java.util.Vector;
9
10 import org.melati.Melati;
11 import org.melati.poem.AccessPoemException;
12 import org.melati.poem.AccessToken;
13 import org.melati.poem.BaseFieldAttributes;
14 import org.melati.poem.Capability;
15 import org.melati.poem.Field;
16 import org.melati.poem.PoemThread;
17 import org.melati.servlet.Form;
18 import org.melati.template.ServletTemplateContext;
19 import org.melati.template.SimpleDateAdaptor;
20 import org.melati.util.ContextUtil;
21 import org.melati.poem.util.EnumUtils;
22 import org.paneris.jammyjoes.model.JammyjoesDatabase;
23 import org.paneris.jammyjoes.model.Product;
24 import org.paneris.jammyjoes.model.Product.StockValues;
25 import org.paneris.jammyjoes.servlet.JammyJoesMelatiServlet;
26
27 public class ProductReport extends JammyJoesMelatiServlet {
28
29 private static final long serialVersionUID = 1L;
30 private ProductSearch util;
31
32 protected String jammyjoesRequest(Melati melati, ServletTemplateContext context) throws Exception {
33
34 util = new ProductSearch(null,0, new Logger(null));
35 JammyjoesDatabase db = (JammyjoesDatabase) melati.getDatabase();
36 ServletTemplateContext tc = melati.getServletTemplateContext();
37 Capability admin = db.getCanAdminister();
38 AccessToken token = PoemThread.accessToken();
39 if (!token.givesCapability(admin))
40 throw new AccessPoemException(token, admin);
41 String template = "view/StockReview.wm";
42
43 int option = 0;
44 Integer optionInteger = Form.getIntegerField(tc, "option");
45 SimpleDateAdaptor sd = new SimpleDateAdaptor();
46 Date date = (Date) sd.rawFrom(tc, "date");
47 context.put("date", date);
48
49 if (optionInteger != null)
50 option = optionInteger.intValue();
51 context.put("option", new Integer(option));
52 Integer type = Form.getIntegerField(tc, "field_type");
53 Integer supplier = Form.getIntegerField(tc, "field_supplier");
54 Integer manufacturer = Form.getIntegerField(tc, "field_manufacturer");
55 context.put(
56 "type",
57 new Field(
58 type,
59 new BaseFieldAttributes(db.getProductTable().getTypeColumn(), true)));
60 context.put(
61 "supplier",
62 new Field(
63 supplier,
64 new BaseFieldAttributes(db.getProductTable().getSupplierColumn(), true)));
65 context.put(
66 "manufacturer",
67 new Field(
68 manufacturer,
69 new BaseFieldAttributes(db.getProductTable().getManufacturerColumn(),
70 true)));
71 if (Form.getFormNulled(tc, "submit") != null) {
72 Vector where = new Vector();
73 List results = new LinkedList();
74 if (type != null)
75 where.add(q("type") + " = " + type);
76 if (supplier != null)
77 where.add(q("supplier") + " = " + supplier);
78 if (manufacturer != null)
79 where.add(q("manufacturer") + " = " + manufacturer);
80 if (option == 1) {
81 template = "view/AnalyzePrices.wm";
82 where.add(discontinuedNoStock(db));
83 }
84 String orderbyClause = null;
85 if (option == 4) {
86 template = "view/StockReview.wm";
87 where.add(discontinued(db));
88 where.add(q("stocklevel") + " <= " + q("reorderlevel"));
89 }
90 if (option == 5) {
91 template = "view/ReconciliationReport.wm";
92 where.add(discontinuedNoStock(db));
93 }
94 if (option == 6) {
95 template = "view/StockValuationReport.wm";
96 }
97 String whereClause = EnumUtils.concatenated(" AND ", where.elements());
98 Enumeration r = db.getProductTable().selection(whereClause, orderbyClause, false);
99 double totalStock = 0;
100 double totalSold = 0;
101 double totalMargin = 0;
102 double averageMargin = 0;
103 double averageMarkup = 0;
104 double totalCostPrice = 0;
105 double totalCostPriceIncVat = 0;
106 double totalRetailPrice = 0;
107 double totalRetailPriceIncVat = 0;
108 while (r.hasMoreElements()) {
109 Product p = (Product) r.nextElement();
110 if (option == 1) {
111 results.add(p);
112 if (p.getMargin() != null)
113 totalMargin += p.getMargin().doubleValue();
114 totalCostPrice += p.getCostprice().doubleValue();
115 totalCostPriceIncVat += p.getCostpriceIncVat().doubleValue();
116 totalRetailPrice += p.getRetailprice().doubleValue();
117 if (p.getRetailpriceincvat() != null)
118 totalRetailPriceIncVat += p.getRetailpriceincvat().doubleValue();
119 if (p.getMarginpercent() != null)
120 averageMargin += p.getMarginpercent().doubleValue();
121 if (p.getMarkuppercent() != null)
122 averageMarkup += p.getMarkuppercent().doubleValue();
123 }
124 if (option == 4 || option == 5) {
125 results.add(p);
126 }
127 if (option == 6) {
128 StockValues stockValues = p.getStockLevels(date);
129 if (stockValues.getLevel() != 0) {
130 results.add(p);
131 totalCostPrice += stockValues.getCostPriceExVat();
132 totalCostPriceIncVat += stockValues.getCostPriceIncVat();
133 totalRetailPrice += stockValues.getRetailPriceExVat();
134 totalRetailPriceIncVat += stockValues.getRetailPriceIncVat();
135 }
136 }
137 }
138 double count = results.size();
139 if (option == 1) {
140 context.put("totalMargin", new Double(totalMargin));
141 context.put("totalCostPrice", new Double(totalCostPrice));
142 context.put("totalRetailPrice", new Double(totalRetailPrice));
143 context.put("totalRetailPriceIncVat", new Double(totalRetailPriceIncVat));
144 context.put("averageMargin", new Double(totalMargin / count));
145 context.put("averageCostPrice", new Double(totalCostPrice / count));
146 context.put("averageCostPriceIncVat", new Double(totalCostPriceIncVat / count));
147 context.put("averageRetailPrice", new Double(totalRetailPrice / count));
148 context.put("averageRetailPriceIncVat", new Double(totalRetailPriceIncVat / count));
149 context.put("averageMarginPercent", new Double(averageMargin / count));
150 context.put("averageMarkupPercent", new Double(averageMarkup / count));
151 }
152 if (option == 4 || option == 5 || option == 6) {
153 context.put("totalMargin", new Double(totalMargin));
154 context.put("totalCostPrice", new Double(totalCostPrice));
155 context.put("totalCostPriceIncVat", new Double(totalCostPriceIncVat));
156 context.put("totalRetailPrice", new Double(totalRetailPrice));
157 context.put("totalRetailPriceIncVat", new Double(totalRetailPriceIncVat));
158 context.put("averageMargin", new Double(totalMargin / totalStock));
159 context.put("averageCostPrice", new Double(totalCostPrice / totalStock));
160 context.put("averageRetailPrice", new Double(totalRetailPrice / totalStock));
161 context.put("averageRetailPriceIncVat", new Double(totalRetailPriceIncVat / totalStock));
162 context.put(
163 "averageMarginPercent",
164 new Double((totalRetailPrice - totalCostPrice) / totalRetailPrice * 100));
165 context.put(
166 "averageMarkupPercent",
167 new Double((totalRetailPrice - totalCostPrice) / totalCostPrice * 100));
168 }
169 if (option == 4)
170 Collections.sort(results, new SupplierNameSort());
171 context.put("count", new Double(count));
172 context.put("averageStock", new Integer(new Double((totalStock / count)).intValue()));
173 context.put("totalStock", new Double(totalStock));
174 context.put("totalSold", new Double(totalSold));
175 context.put("results", results);
176 context.put("util", new ContextUtil());
177 }
178 return template;
179 }
180
181 private String discontinuedNoStock(JammyjoesDatabase db) {
182 String discontinued = "(" + discontinued(db);
183 discontinued += " OR (" + q("stocklevel") + " != 0 AND " + q("stocklevel") + " != NULL))";
184 return discontinued;
185 }
186
187 private String discontinued(JammyjoesDatabase db) {
188 String discontinued =
189 "(" + q("status") + " != " + db.getProductStatusTable().getDiscontinued().getTroid();
190 discontinued += " AND "
191 + q("status")
192 + " != "
193 + db.getProductStatusTable().getNotStocked().getTroid()
194 + ")";
195 return discontinued;
196 }
197
198 public String q(String name) {
199 return util.q(name);
200 }
201
202 }