1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20 package Utilities;
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39 import Exceptions.CustomerNotFoundException;
40 import javafx.beans.property.ReadOnlyDoubleProperty;
41 import javafx.beans.property.ReadOnlyDoubleWrapper;
42 import javafx.beans.property.ReadOnlyStringProperty;
43 import javafx.beans.property.ReadOnlyStringWrapper;
44 import javafx.collections.FXCollections;
45 import javafx.collections.ObservableList;
46
47 import java.math.BigDecimal;
48 import java.sql.Connection;
49 import java.sql.PreparedStatement;
50 import java.sql.ResultSet;
51 import java.sql.SQLException;
52 import java.util.ArrayList;
53 import java.util.List;
54 import java.util.Objects;
55
56
57
58
59 public class Order {
60
61
62 private final ObservableList<formattedProductProps> orders;
63 private final String year;
64 private final Integer custID;
65 private final BigDecimal paid;
66 private final Boolean delivered;
67 private final String uName;
68
69 private final ReadOnlyDoubleWrapper progress = new ReadOnlyDoubleWrapper(this, "progress");
70 private final ReadOnlyStringWrapper message = new ReadOnlyStringWrapper(this, "message");
71
72 public Order(ObservableList<formattedProductProps> orders, String year, Integer custID, BigDecimal paid, Boolean delivered, String uName) {
73 this.orders = orders;
74 this.year = year;
75 this.custID = custID;
76 this.paid = paid;
77 this.delivered = delivered;
78 this.uName = uName;
79 }
80
81 public Order(ObservableList<formattedProductProps> orders, String year, Integer custID, BigDecimal paid, Boolean delivered) {
82 this(orders, year, custID, paid, delivered, DbInt.getUserName());
83 }
84
85 public static orderDetails getOrder(String year, Integer id) {
86 orderDetails order = null;
87 try (Connection con = DbInt.getConnection(year);
88 PreparedStatement prep = con.prepareStatement("SELECT Cost, Quant, paid, delivered FROM ordersview WHERE custId=?", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
89
90 prep.setInt(1, id);
91
92
93 try (ResultSet rs = prep.executeQuery()) {
94 rs.next();
95 order = new orderDetails(rs.getBigDecimal("Cost"), rs.getInt("Quant"), rs.getBigDecimal("paid"), rs.getInt("delivered"));
96 }
97 } catch (SQLException e) {
98 LogToFile.log(e, Severity.SEVERE, CommonErrors.returnSqlMessage(e));
99 }
100 return order;
101 }
102
103 public static orderArray createOrderArray(String year) {
104 return createOrderArray(year, DbInt.getUserName());
105 }
106
107 public static orderArray createOrderArray(String year, String uName) {
108 BigDecimal totL = BigDecimal.ZERO;
109 int QuantL = 0;
110
111
112 ArrayList<formattedProduct> allProducts = new ArrayList<formattedProduct>();
113
114 int noProductsOrdered = 0;
115
116
117
118 int quant;
119 try (Connection con = DbInt.getConnection(year);
120 PreparedStatement prep = con.prepareStatement("SELECT * FROM (SELECT * FROM products) products LEFT JOIN (SELECT SUM(Quantity),ProductId FROM orderedproductsview WHERE " + (Objects.equals(uName, "") ? "''=?" : "uName=?") + " GROUP BY ProductId) orderedproductsview ON orderedproductsview.ProductId=products.idproducts ORDER BY products.idproducts", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
121 prep.setString(1, uName);
122
123 try (ResultSet rs = prep.executeQuery()) {
124
125 while (rs.next()) {
126 quant = rs.getInt("SUM(Quantity)");
127 if (quant > 0) {
128 BigDecimal unitCost = rs.getBigDecimal("Cost");
129 allProducts.add(new formattedProduct(rs.getInt("idproducts"), rs.getString("ID"), rs.getString("Name"), rs.getString("UnitSize"), rs.getBigDecimal("Cost"), rs.getString("Category"), quant, unitCost.multiply(new BigDecimal(quant))));
130 totL = unitCost.multiply(new BigDecimal(quant)).add(totL);
131 QuantL += quant;
132 noProductsOrdered++;
133
134 }
135 }
136 }
137 } catch (SQLException e) {
138 LogToFile.log(e, Severity.SEVERE, CommonErrors.returnSqlMessage(e));
139 }
140
141
142
143
144 formattedProduct[] orderedProducts = new formattedProduct[noProductsOrdered];
145
146
147 System.arraycopy(allProducts.toArray(), 0, orderedProducts, 0, noProductsOrdered);
148 return new orderArray(orderedProducts, totL, QuantL);
149 }
150
151 public static orderArray createOrderArray(String year, int cID, Boolean excludeZeroOrders) {
152 return createOrderArray(year, cID, excludeZeroOrders, "*");
153 }
154
155 public static orderArray createOrderArray(String year, int cID, Boolean excludeZeroOrders, String Category) {
156 BigDecimal totL = BigDecimal.ZERO;
157 int QuantL = 0;
158 Customer customer;
159 try {
160 customer = new Customer(cID, year);
161
162
163
164 ArrayList<formattedProduct> allProducts = new ArrayList<formattedProduct>();
165 int OrderID = customer.getOrderId();
166
167 int noProductsOrdered = 0;
168
169
170
171
172 int quant;
173 if (!Objects.equals(Category, "*")) {
174 try (Connection con = DbInt.getConnection(year);
175 PreparedStatement prep = con.prepareStatement("SELECT * FROM (SELECT * FROM products WHERE Category=?) products LEFT JOIN (SELECT SUM(Quantity),ProductId FROM orderedproductsview WHERE orderID=? GROUP BY ProductId) orderedproductsview ON orderedproductsview.ProductId=products.idproducts ORDER BY products.idproducts", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
176
177 prep.setString(1, Category);
178
179 prep.setInt(2, OrderID);
180
181 try (ResultSet rs = prep.executeQuery()) {
182 while (rs.next()) {
183
184 quant = rs.getInt("SUM(Quantity)");
185 if (((quant > 0) && excludeZeroOrders) || !excludeZeroOrders) {
186 BigDecimal unitCost = rs.getBigDecimal("Cost");
187 allProducts.add(new formattedProduct(rs.getInt("idproducts"), rs.getString("ID"), rs.getString("Name"), rs.getString("UnitSize"), rs.getBigDecimal("Cost"), rs.getString("Category"), quant, unitCost.multiply(new BigDecimal(quant))));
188 totL = unitCost.multiply(new BigDecimal(quant)).add(totL);
189 QuantL += quant;
190 noProductsOrdered++;
191
192 }
193 }
194 }
195 } catch (SQLException e) {
196 LogToFile.log(e, Severity.SEVERE, CommonErrors.returnSqlMessage(e));
197 }
198 } else {
199 try (Connection con = DbInt.getConnection(year);
200 PreparedStatement prep = con.prepareStatement("SELECT * FROM (SELECT * FROM products) products LEFT JOIN (SELECT SUM(Quantity),ProductId FROM orderedproductsview WHERE orderID=? GROUP BY ProductId) orderedproductsview ON orderedproductsview.ProductId=products.idproducts ORDER BY products.idproducts", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
201
202 prep.setInt(1, OrderID);
203
204 try (ResultSet rs = prep.executeQuery()) {
205
206 while (rs.next()) {
207 quant = rs.getInt("SUM(Quantity)");
208 if (((quant > 0) && excludeZeroOrders) || !excludeZeroOrders) {
209 BigDecimal unitCost = rs.getBigDecimal("Cost");
210 allProducts.add(new formattedProduct(rs.getInt("idproducts"), rs.getString("ID"), rs.getString("Name"), rs.getString("UnitSize"), rs.getBigDecimal("Cost"), rs.getString("Category"), quant, unitCost.multiply(new BigDecimal(quant))));
211 totL = unitCost.multiply(new BigDecimal(quant)).add(totL);
212 QuantL += quant;
213 noProductsOrdered++;
214
215 }
216 }
217 }
218 } catch (SQLException e) {
219 LogToFile.log(e, Severity.SEVERE, CommonErrors.returnSqlMessage(e));
220 }
221 }
222
223
224
225
226
227 formattedProduct[] orderedProducts = new formattedProduct[noProductsOrdered];
228
229
230 System.arraycopy(allProducts.toArray(), 0, orderedProducts, 0, noProductsOrdered);
231
232 return new orderArray(orderedProducts, totL, QuantL);
233 } catch (CustomerNotFoundException e) {
234 LogToFile.log(e, Severity.WARNING, "Customer not found. Refresh and try again.");
235 }
236 return new orderArray(null, BigDecimal.ZERO, 0);
237
238 }
239
240 public static orderArray createNewOrder(formattedProductProps[] orderData) {
241 BigDecimal totL = BigDecimal.ZERO;
242 int QuantL = 0;
243 formattedProduct[] orders = new formattedProduct[orderData.length];
244 for (int i = 0; i < orderData.length; i++) {
245 orders[i] = new formattedProduct(orderData[i].getProductKey(), orderData[i].getProductID(), orderData[i].getProductName(), orderData[i].getProductSize(), orderData[i].getProductUnitPrice(), orderData[i].getProductCategory(), orderData[i].getOrderedQuantity(), orderData[i].getExtendedCost());
246 totL = totL.add(orders[i].extendedCost);
247 QuantL += orders[i].orderedQuantity;
248 }
249 return new orderArray(orders, totL, QuantL);
250
251 }
252
253 private static int IntegerLength(int n) {
254 if (n < 100000) {
255
256 if (n < 100) {
257
258 if (n < 10) { return 1; } else { return 2; }
259 } else {
260
261 if (n < 1000) { return 3; } else {
262
263 if (n < 10000) { return 4; } else { return 5; }
264 }
265 }
266 } else {
267
268 if (n < 10000000) {
269
270 if (n < 1000000) { return 6; } else { return 7; }
271 } else {
272
273 if (n < 100000000) { return 8; } else {
274
275 if (n < 1000000000) { return 9; } else { return 10; }
276 }
277 }
278 }
279
280 }
281
282 public double getProgress() {
283 return progress.get();
284 }
285
286 public ReadOnlyDoubleProperty progressProperty() {
287 return progress.getReadOnlyProperty();
288 }
289
290 public String getMessage() {
291 return message.get();
292 }
293
294 public ReadOnlyStringProperty messageProperty() {
295 return message.getReadOnlyProperty();
296 }
297
298 public String updateOrder(failCallback fail) throws Exception {
299 List<Integer> Ids = new ArrayList<>();
300 int numRows = orders.size();
301 ObservableList<formattedProductProps> orderNoZero = FXCollections.observableArrayList();
302
303 for (formattedProductProps order : orders) {
304
305
306 if (order.getOrderedQuantity() > 0) {
307 orderNoZero.add(order);
308 }
309 }
310
311 try (Connection con = DbInt.getConnection(year);
312 PreparedStatement prep = con.prepareStatement("SELECT idOrders FROM ordersview WHERE custId=?", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
313 prep.setInt(1, custID);
314 try (ResultSet rs = prep.executeQuery()) {
315 while (rs.next()) {
316
317 Ids.add(rs.getInt(1));
318
319 }
320 }
321 }
322 int progressDivisor = (2 * numRows);
323 int progressIncrement = 50 / progressDivisor;
324 progress.set(getProgress() + progressIncrement);
325 fail.doAction();
326
327
328
329 if (Ids.size() > 0) {
330 message.set("Building Order Update");
331
332 try (Connection con = DbInt.getConnection(year);
333 PreparedStatement prep = con.prepareStatement("SELECT idOrders FROM ordersview WHERE custId=?", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
334 prep.setInt(1, custID);
335 try (ResultSet rs = prep.executeQuery()) {
336 while (rs.next()) {
337
338 Ids.add(rs.getInt(1));
339
340 }
341 }
342 }
343 int OrderID = Ids.get(Ids.size() - 1);
344
345 try (Connection con = DbInt.getConnection(year);
346 PreparedStatement prep = con.prepareStatement("DELETE FROM orderedproductsview WHERE orderID=?", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
347 prep.setInt(1, OrderID);
348 prep.execute();
349 }
350 try (Connection con = DbInt.getConnection(year);
351 PreparedStatement writeOrd = con.prepareStatement("INSERT INTO orderedproductsview(uName,custId, orderID, ProductID, Quantity) VALUES(?,?,?,?,?)", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
352 for (formattedProductProps prod : orderNoZero) {
353
354 writeOrd.setString(1, uName);
355 writeOrd.setInt(2, custID);
356 writeOrd.setInt(3, OrderID);
357 writeOrd.setInt(4, prod.getProductKey());
358 writeOrd.setInt(5, prod.getOrderedQuantity());
359
360 fail.doAction();
361 message.set("Adding Order");
362
363 writeOrd.executeUpdate();
364 }
365 }
366 try (Connection con = DbInt.getConnection(year);
367 PreparedStatement writeOrd = con.prepareStatement("UPDATE ordersview SET paid=?, delivered=? WHERE idOrders=?", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
368 writeOrd.setBigDecimal(1, paid);
369 writeOrd.setInt(2, delivered ? 1 : 0);
370 writeOrd.setInt(3, OrderID);
371
372 fail.doAction();
373 message.set("Adding Order");
374
375 writeOrd.executeUpdate();
376 }
377 }
378 else {
379 message.set("Building Order");
380
381
382
383 fail.doAction();
384
385
386 try (Connection con = DbInt.getConnection(year);
387 PreparedStatement writeOrd = con.prepareStatement("INSERT INTO ordersview(uName,custId, paid, delivered) VALUES(?,?, ?, ?)", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
388 writeOrd.setString(1, uName);
389 writeOrd.setInt(2, custID);
390 writeOrd.setBigDecimal(3, paid);
391 writeOrd.setInt(4, delivered ? 1 : 0);
392
393 fail.doAction();
394 message.set("Adding Order");
395
396 writeOrd.executeUpdate();
397 }
398 try (Connection con = DbInt.getConnection(year);
399 PreparedStatement prep = con.prepareStatement("SELECT idOrders FROM ordersview WHERE custId=?", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
400 prep.setInt(1, custID);
401
402 try (ResultSet rs = prep.executeQuery()) {
403 while (rs.next()) {
404
405 Ids.add(rs.getInt(1));
406
407 }
408 }
409 }
410 int OrderID = Ids.get(Ids.size() - 1);
411
412
413 try (Connection con = DbInt.getConnection(year);
414 PreparedStatement writeOrd = con.prepareStatement("INSERT INTO orderedproductsview(uName,custId, orderID, ProductID, Quantity) VALUES(?,?,?,?,?)", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
415 for (formattedProductProps prod : orderNoZero) {
416
417 writeOrd.setString(1, uName);
418 writeOrd.setInt(2, custID);
419 writeOrd.setInt(3, OrderID);
420 writeOrd.setInt(4, prod.getProductKey());
421 writeOrd.setInt(5, prod.getOrderedQuantity());
422
423 fail.doAction();
424 message.set("Adding Order");
425
426 writeOrd.executeUpdate();
427 }
428 }
429 }
430 try (Connection con = DbInt.getConnection(year);
431 PreparedStatement prep = con.prepareStatement("SELECT idOrders FROM ordersview WHERE custId=?", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
432 prep.setInt(1, custID);
433 try (ResultSet rs = prep.executeQuery()) {
434 while (rs.next()) {
435
436 Ids.add(rs.getInt(1));
437
438 }
439 }
440 }
441 return Ids.get(Ids.size() - 1).toString();
442 }
443
444 public interface failCallback {
445 void doAction() throws InterruptedException;
446 }
447
448 public static class orderArray {
449 public final formattedProduct[] orderData;
450 public BigDecimal totalCost;
451 public int totalQuantity;
452
453 public orderArray(formattedProduct[] orderData, BigDecimal totalCost, int totalQuantity) {
454 this.orderData = orderData;
455 this.totalCost = totalCost;
456 this.totalQuantity = totalQuantity;
457 }
458 }
459
460 public static class orderDetails {
461 public final BigDecimal totalCost;
462 public final int totalQuantity;
463 public final BigDecimal paid;
464 public final boolean delivered;
465
466 public orderDetails(BigDecimal totalCost, int totalQuantity, BigDecimal paid, boolean delivered) {
467 this.totalCost = totalCost;
468 this.totalQuantity = totalQuantity;
469 this.paid = paid;
470 this.delivered = delivered;
471 }
472
473 public orderDetails(BigDecimal totalCost, int totalQuantity, BigDecimal paid, int delivered) {
474 this.totalCost = totalCost;
475 this.totalQuantity = totalQuantity;
476 this.paid = paid;
477 this.delivered = delivered == 1;
478 }
479 }
480 }