|
1 package com.knowgate.hipergate;
2
3 import java.math.BigDecimal  ;
4
5 import java.sql.SQLException ;
6 import java.sql.PreparedStatement ;
7 import java.sql.ResultSet ;
8 import java.sql.Timestamp ;
9 import java.sql.Statement ;
10 import java.sql.Types ;
11
12 import java.util.Date ;
13 import java.util.Locale ;
14
15 import java.text.NumberFormat ;
16 import java.text.DecimalFormat ;
17 import java.text.FieldPosition ;
18
19 import com.knowgate.debug.DebugFile;
20 import com.knowgate.jdc.JDCConnection;
21 import com.knowgate.dataobjs.DB;
22 import com.knowgate.dataobjs.DBPersist;
23 import com.knowgate.dataobjs.DBSubset;
24 import com.knowgate.misc.Gadgets;
25 import com.knowgate.dataobjs.DBBind;
26
27
32 public abstract class AbstractOrder extends DBPersist {
33
34 private Locale oLocale;
35 private String sCurrencyFormat;
36 private DecimalFormat oCurrencyFormat = null;
37 private FieldPosition oCurrencyFieldP = null;
38 private StringBuffer oCurrencyBuffer;
39
40 protected DBSubset oLines;
41 protected DBPersist oBuyer;
42 protected DBPersist oSeller;
43 protected String sLinesTable, sPrimaryKey;
44
45
47 protected AbstractOrder(String sTableName, String sLinesName,
48 String sKeyName, String sAuditClass) {
49 super(sTableName, sAuditClass);
50 sLinesTable = sLinesName;
51 sPrimaryKey = sKeyName;
52 oLines = null;
53 oBuyer = null;
54 oSeller = null;
55 oLocale = null;
56 setCurrencyFormat("#0.00");
57 }
58
59
60
62 public String getCurrencyFormat() {
63 return sCurrencyFormat;
64 }
65
66
68 public void setCurrencyFormat(String sFormat) throws NullPointerException {
69 sCurrencyFormat = sFormat;
70 oCurrencyFormat = new DecimalFormat (sFormat);
71 oCurrencyFieldP = new FieldPosition (NumberFormat.FRACTION_FIELD);
72 oCurrencyBuffer = new StringBuffer ();
73 }
74
75
77 protected StringBuffer getDecimalFormated(String sColumnName) {
78 oCurrencyBuffer.setLength(0);
79 if (!isNull(sColumnName))
80 oCurrencyFormat.format(getDecimal(sColumnName).doubleValue(), oCurrencyBuffer, oCurrencyFieldP);
81 return oCurrencyBuffer;
82 }
83
84
86 public void setLocale(Locale oLoc) {
87 oLocale = oLoc;
88 }
89
90
92 public void setLocale(String sLanguage, String sCountry) {
93 oLocale = new Locale (sLanguage, sCountry);
94 }
95
96
98 public void setLocale(String sLanguage) {
99 oLocale = new Locale (sLanguage);
100 }
101
102
104 public Locale getLocale() {
105 return oLocale==null ? Locale.getDefault() : oLocale;
106 }
107
108
110
118 public Address getBillAddress(JDCConnection oConn) throws SQLException {
119 Address oBillAddr;
120 if (isNull(DB.gu_bill_addr)) {
121 oBillAddr = null;
122 } else {
123 oBillAddr = new Address(oConn, getString(DB.gu_bill_addr));
124 }
125 return oBillAddr;
126 }
128
130
137 public DBSubset getLines() throws IllegalStateException {
138 if (oLines==null)
139 throw new IllegalStateException ("AbstractOrder.getLines() Order lines not loaded");
140 else
141 return oLines;
142 }
143
144
146 public abstract DBSubset getLines(JDCConnection oConn) throws SQLException ;
147
148
150 public boolean load(JDCConnection oConn, Object [] PKVals) throws SQLException {
151 PreparedStatement oStmt;
152 ResultSet oRSet;
153
154 boolean bLoad = super.load(oConn, PKVals);
155
156 oLines = null;
157 oBuyer = null;
158 oSeller= null;
159
160 if (bLoad) {
161 oLines = getLines(oConn);
162
163 if (!isNull(DB.gu_contact)) {
164 oStmt = oConn.prepareStatement("SELECT "+DB.gu_address+ " FROM "+DB.k_x_contact_addr+" WHERE "+DB.gu_contact+"=?",ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_READ_ONLY);
165 oStmt.setString(1, getString(DB.gu_contact));
166 oRSet = oStmt.executeQuery();
167 if (oRSet.next()) {
168 oBuyer = new DBPersist(DB.k_member_address, "Buyer");
169 oBuyer.load(oConn, new Object []{oRSet.getString(1)});
170 }
171 oRSet.close();
172 oStmt.close();
173 } else if (!isNull(DB.gu_company)) {
174 oStmt = oConn.prepareStatement("SELECT "+DB.gu_address+ " FROM "+DB.k_x_company_addr+" WHERE "+DB.gu_company+"=?",ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_READ_ONLY);
175 oStmt.setString(1, getString(DB.gu_company));
176 oRSet = oStmt.executeQuery();
177 if (oRSet.next()) {
178 oBuyer = new DBPersist(DB.k_member_address, "Buyer");
179 oBuyer.load(oConn, new Object []{oRSet.getString(1)});
180 }
181 oRSet.close();
182 oStmt.close();
183 }
184 if (!isNull(DB.gu_shop))
185 oSeller = new Shop(oConn, getString(DB.gu_shop));
186 }
188 return bLoad;
189 }
190
191
193 private void insertLine(JDCConnection oConn, int iLine,
194 String sProductId, String sProductNm,
195 BigDecimal dSalePr, float fQuantity,
196 BigDecimal dTotalPr, float fTax,
197 short iTaxIncluded, String sPromotion,
198 String sOptions) throws SQLException {
199 PreparedStatement oStmt;
200 String sSQL;
201
202 if (DebugFile.trace) {
203 DebugFile.writeln("Begin AbstractOrder.insertLine([Connection], " + String.valueOf(iLine) + "," + (sProductId!=null ? sProductId : "null") + "," + (sProductNm!=null ? sProductNm : "null") + "," + dSalePr.toString() + "," + String.valueOf(fQuantity) + "," + dTotalPr.toString() + "," + String.valueOf(iTaxIncluded) + "," + (sPromotion!=null ? sPromotion : "") + "," + (sOptions!=null ? sOptions : ""));
204 DebugFile.incIdent();
205 }
206
207 sSQL = "INSERT INTO " + sLinesTable + " (" + sPrimaryKey + "," + DB.pg_line + "," + DB.gu_product + "," + DB.nm_product + "," + DB.pr_sale + "," + DB.nu_quantity + "," + DB.pr_total + "," + DB.pct_tax_rate + "," + DB.is_tax_included + "," + DB.tx_promotion + "," + DB.tx_options + ") VALUES (?,?,?,?,?,?,?,?,?,?,?)";
208
209 if (DebugFile.trace) DebugFile.writeln("Connection.prepareStatement(" + sSQL + ")");
210
211 oStmt = oConn.prepareStatement(sSQL);
212
213 oStmt.setString(1,getString(sPrimaryKey));
214 oStmt.setInt (2,iLine);
215 oStmt.setString(3, sProductId);
216 oStmt.setString(4, sProductNm);
217 oStmt.setBigDecimal (5, dSalePr);
218 oStmt.setFloat (6, fQuantity);
219 oStmt.setBigDecimal (7, dTotalPr);
220 oStmt.setFloat (8, fTax);
221 oStmt.setShort (9, iTaxIncluded);
222 oStmt.setString(10, sPromotion);
223 oStmt.setString(11, sOptions);
224
225 if (DebugFile.trace) DebugFile.writeln("Connection.execute()");
226
227 oStmt.execute();
228 oStmt.close();
229
230 oStmt = oConn.prepareStatement("UPDATE "+getTableName()+" SET "+DB.dt_modified+"="+DBBind.Functions.GETDATE+" WHERE "+sPrimaryKey+"=?");
231 oStmt.setObject(1, get(sPrimaryKey), Types.CHAR);
232 oStmt.executeUpdate();
233 oStmt.close();
234
235 if (DebugFile.trace) {
236 DebugFile.decIdent();
237 DebugFile.writeln("End AbstractOrder.insertLine()");
238 }
239 }
241
243 private boolean updateLine(JDCConnection oConn, int iLine,
244 String sProductId, String sProductNm,
245 BigDecimal dSalePr, float fQuantity,
246 BigDecimal dTotalPr, float fTax,
247 short iTaxIncluded, String sPromotion,
248 String sOptions) throws SQLException {
249 boolean bUpdated;
250 PreparedStatement oStmt;
251 String sSQL;
252
253 if (DebugFile.trace) {
254 DebugFile.writeln("Begin AbstractOrder.updateLine([Connection], " + String.valueOf(iLine) + "," + (sProductId!=null ? sProductId : "null") + "," + (sProductNm!=null ? sProductNm : "null") + "," + dSalePr.toString() + "," + String.valueOf(fQuantity) + "," + dTotalPr.toString() + "," + String.valueOf(iTaxIncluded) + "," + (sPromotion!=null ? sPromotion : "") + "," + (sOptions!=null ? sOptions : ""));
255 DebugFile.incIdent();
256 }
257
258 sSQL = "UPDATE " + sLinesTable + " SET " + DB.gu_product + "=?," + DB.nm_product + "=?," + DB.pr_sale + "=?," + DB.nu_quantity + "=?," + DB.pr_total + "=?," + DB.pct_tax_rate + "=?," + DB.is_tax_included + "=?," + DB.tx_promotion + "=?," + DB.tx_options + "=? WHERE " + sPrimaryKey + "=? AND " + DB.pg_line + "=?";
259
260 if (DebugFile.trace) DebugFile.writeln("Connection.prepareStatement(" + sSQL + ")");
261
262 oStmt = oConn.prepareStatement(sSQL);
263
264 oStmt.setString(1, sProductId);
265 oStmt.setString(2, sProductNm);
266 oStmt.setBigDecimal (3, dSalePr);
267 oStmt.setFloat (4, fQuantity);
268 oStmt.setBigDecimal (5, dTotalPr);
269 oStmt.setFloat (6, fTax);
270 oStmt.setShort (7, iTaxIncluded);
271 oStmt.setString(8, sPromotion);
272 oStmt.setString(9, sOptions);
273 oStmt.setString(10,getString(sPrimaryKey));
274 oStmt.setInt (11,iLine);
275
276 if (DebugFile.trace) DebugFile.writeln("Connection.executeUpdate()");
277
278 bUpdated = (oStmt.executeUpdate()>0);
279 oStmt.close();
280
281 oStmt = oConn.prepareStatement("UPDATE "+getTableName()+" SET "+DB.dt_modified+"="+DBBind.Functions.GETDATE+" WHERE "+sPrimaryKey+"=?");
282 oStmt.setObject(1, get(sPrimaryKey), Types.CHAR);
283 oStmt.executeUpdate();
284 oStmt.close();
285
286 if (DebugFile.trace) {
287 DebugFile.decIdent();
288 DebugFile.writeln("End AbstractOrder.updateLine() : " + String.valueOf(bUpdated));
289 }
290
291 return bUpdated;
292 }
294
296
316 public int addProduct(JDCConnection oConn, String sProductId, String sProductNm,
317 BigDecimal dSalePr, float fQuantity, BigDecimal dTotalPr,
318 float fTax, short iTaxIncluded, String sPromotion,
319 String sOptions)
320 throws SQLException ,IllegalArgumentException ,NullPointerException {
321 int iPgLine;
322 Object oPgLine;
323 ResultSet oLine;
324 PreparedStatement oSeek;
325
326 if (isNull(sPrimaryKey)) throw new NullPointerException ("AbstractOrder.addProduct() "+sPrimaryKey+" cannot be null");
327
328 if (DebugFile.trace) {
329 DebugFile.writeln("Begin AbstractOrder.addProduct([Connection], " + (sProductId!=null ? sProductId : "null") + "," + (sProductNm!=null ? sProductNm : "null") + "," + dSalePr.toString() + "," + String.valueOf(fQuantity) + "," + dTotalPr.toString() + "," + String.valueOf(iTaxIncluded) + "," + (sPromotion!=null ? sPromotion : "") + "," + (sOptions!=null ? sOptions : ""));
330 DebugFile.incIdent();
331 }
332
333 if (null==sProductId) {
334 sProductId = "null_"+Gadgets.leftPad(Gadgets.left(sProductNm==null ? "" : Gadgets.ASCIIEncode(sProductNm), 27),'*',27).toUpperCase();
335 iPgLine = -1;
336 }
337 else {
338 if (DebugFile.trace) DebugFile.writeln("Connection.prepareStatement(" + "SELECT " + DB.pg_line + " FROM " + sLinesTable + " WHERE " + sPrimaryKey + "=? AND " + DB.gu_product + "=?" + ")");
339
340 oSeek = oConn.prepareStatement("SELECT " + DB.pg_line + " FROM " + sLinesTable + " WHERE " + sPrimaryKey + "=? AND " + DB.gu_product + "=?",
341 ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
342 oSeek.setString(1, getString(sPrimaryKey));
343 oSeek.setString(2, sProductId);
344 oLine = oSeek.executeQuery();
345 if (oLine.next())
346 iPgLine = oLine.getInt(1);
347 else
348 iPgLine = -1;
349 oLine.close();
350 oSeek.close();
351 }
352
353 if (-1==iPgLine) {
354 if (DebugFile.trace) DebugFile.writeln("Connection.prepareStatement(" + "SELECT MAX(" + DB.pg_line + ") FROM " + sLinesTable + " WHERE " + sPrimaryKey + "=?" + ")");
355
356 oSeek = oConn.prepareStatement("SELECT MAX(" + DB.pg_line + ") FROM " + sLinesTable + " WHERE " + sPrimaryKey + "=?", ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
357 oSeek.setString(1, getString(sPrimaryKey));
358 oLine = oSeek.executeQuery();
359 if (oLine.next())
360 oPgLine = oLine.getObject(1);
361 else
362 oPgLine = null;
363 oLine.close();
364 oSeek.close();
365
366 if (null==oPgLine)
367 iPgLine = 1;
368 else
369 iPgLine = new Integer (oPgLine.toString()).intValue() + 1;
370
371 insertLine (oConn, iPgLine, sProductId, sProductNm, dSalePr, fQuantity, dTotalPr, fTax, iTaxIncluded, sPromotion, sOptions);
372 }
373 else
374 updateLine (oConn, iPgLine, sProductId, sProductNm, dSalePr, fQuantity, dTotalPr, fTax, iTaxIncluded, sPromotion, sOptions);
375
376 if (DebugFile.trace) {
377 DebugFile.decIdent();
378 DebugFile.writeln("End AbstractOrder.addProduct() : " + String.valueOf(iPgLine));
379 }
380
381 return iPgLine;
382 }
384
386
416
417 public int addProduct(JDCConnection oConn, String sProductId, String sProductNm,
418 float fQuantity, String sPromotion, String sOptions)
419 throws SQLException ,IllegalArgumentException ,NullPointerException {
420
421 if (DebugFile.trace) {
422 DebugFile.writeln("Begin AbstractOrder.addProduct([Connection], " + (sProductId!=null ? sProductId : "null") + "," + (sProductNm!=null ? sProductNm : "null") + "," + String.valueOf(fQuantity) + "," + (sPromotion!=null ? sPromotion : "") + "," + (sOptions!=null ? sOptions : ""));
423 DebugFile.incIdent();
424 }
425
426 Product oProd = new Product();
427 boolean bFound;
428 if (sProductId==null)
429 bFound = false;
430 else {
431 bFound = oProd.load(oConn, new Object []{sProductId});
432
433 if (!bFound) {
434 if (DebugFile.trace) DebugFile.decIdent();
435 throw new SQLException ("AbstractOrder.addProduct() Product "+sProductId+" not found","02000", 200);
436 }
437 }
439 float fTaxRate;
440 BigDecimal dListPr;
441 BigDecimal dSalePr;
442 short iTaxIncluded;
443 boolean bDtStart = oProd.isNull(DB.dt_start);
444 boolean bDtEnd = oProd.isNull(DB.dt_end);
445
446 if (null==sProductNm) sProductNm = oProd.getString(DB.nm_product);
447 if (oProd.isNull(DB.pr_list)) dListPr = new BigDecimal (0); else dListPr = oProd.getDecimal(DB.pr_list);
448 if (oProd.isNull(DB.is_tax_included)) iTaxIncluded = (short)0f; else iTaxIncluded = oProd.getShort(DB.is_tax_included);
449 if (oProd.isNull(DB.pct_tax_rate)) fTaxRate = 0f; else fTaxRate = oProd.getFloat(DB.pct_tax_rate);
450
451 Date dNow = new Date ();
452 dSalePr = oProd.salePrice (dNow);
453
454 if (null==dSalePr)
455 throw new NullPointerException ("Could not find Sale Price for Product " + oProd.getStringNull(DB.gu_product,"") + " on date " + dNow.toString());
456
457 int iPg = addProduct(oConn, sProductId, sProductNm, dSalePr, fQuantity,
458 new BigDecimal (dSalePr.doubleValue() * (double)fQuantity),
459 fTaxRate, iTaxIncluded, sPromotion, sOptions);
460
461 if (DebugFile.trace) {
462 DebugFile.decIdent();
463 DebugFile.writeln("End AbstractOrder.addProduct() : " + String.valueOf(iPg));
464 }
465
466 return iPg;
467 }
469
471
481 public int addProduct(JDCConnection oConn, String sProductId, float fQuantity)
482 throws SQLException ,IllegalArgumentException ,NullPointerException {
483
484 return addProduct (oConn, sProductId, null, fQuantity, null, null);
485 }
486
487
489
495 public void removeProduct(JDCConnection oConn, String sProductId) throws SQLException {
496
497 if (DebugFile.trace) {
498 DebugFile.writeln("Begin Order.removeProduct([Connection], " + (sProductId!=null ? sProductId : "null") + ")");
499 DebugFile.incIdent();
500 }
501
502 Statement oDlte = oConn.createStatement();
503
504 if (DebugFile.trace)
505 DebugFile.writeln("Statement.execute(DELETE FROM " + sLinesTable + " WHERE " + sPrimaryKey + "='" + getStringNull(sPrimaryKey,"null") + "' AND " + DB.gu_product + "='" + (sProductId!=null ? sProductId : "null") + "'");
506
507 oDlte.execute("DELETE FROM " + sLinesTable + " WHERE " + sPrimaryKey + "='" + getString(sPrimaryKey) + "' AND " + DB.gu_product + "='" + sProductId + "'");
508 oDlte.close();
509
510 PreparedStatement oStmt = oConn.prepareStatement("UPDATE "+getTableName()+" SET "+DB.dt_modified+"="+DBBind.Functions.GETDATE+" WHERE "+sPrimaryKey+"=?");
511 oStmt.setObject(1, get(sPrimaryKey), Types.CHAR);
512 oStmt.executeUpdate();
513 oStmt.close();
514
515 if (DebugFile.trace) {
516 DebugFile.decIdent();
517 DebugFile.writeln("End Order.removeProduct()");
518 }
519 }
521
523
528 public void removeAllProducts(JDCConnection oConn) throws SQLException {
529 if (DebugFile.trace) {
530 DebugFile.writeln("Begin Order.removeAllProducts([Connection])");
531 DebugFile.incIdent();
532 }
533
534 Statement oDlte = oConn.createStatement();
535
536 if (DebugFile.trace)
537 DebugFile.writeln("Statement.execute(DELETE FROM " + sLinesTable + " WHERE " + sPrimaryKey + "='" + getStringNull(sPrimaryKey,"") + "'");
538
539 oDlte.execute("DELETE FROM " + sLinesTable + " WHERE " + sPrimaryKey + "='" + getString(sPrimaryKey) + "'");
540 oDlte.close();
541
542 PreparedStatement oStmt = oConn.prepareStatement("UPDATE "+getTableName()+" SET "+DB.dt_modified+"="+DBBind.Functions.GETDATE+" WHERE "+sPrimaryKey+"=?");
543 oStmt.setObject(1, get(sPrimaryKey), Types.CHAR);
544 oStmt.executeUpdate();
545 oStmt.close();
546
547 if (DebugFile.trace) {
548 DebugFile.decIdent();
549 DebugFile.writeln("End Order.removeAllProducts()");
550 }
551 }
553
555
561 public BigDecimal computeSubtotal()
562 throws IllegalStateException {
563
564 if (null==oLines)
565 throw new IllegalStateException ("AbstractOrder.computeSubtotal() Lines must be loaded before computing subtotal");
566
567 BigDecimal dSubTotal = new BigDecimal (0d);
568
569 final int iLines = oLines.getRowCount();
570
571 if (iLines>0) {
572 final int iPrSale = oLines.getColumnPosition(DB.pr_sale);
573 final int iNuQuan = oLines.getColumnPosition(DB.nu_quantity);
574 final BigDecimal dOne = new BigDecimal (1d);
575 BigDecimal dQuantity;
576
577 for (int l = 0; l < iLines; l++) {
578 if (!oLines.isNull(iPrSale, l)) {
579 if (oLines.isNull(iNuQuan, l))
580 dQuantity = dOne;
581 else
582 dQuantity = new BigDecimal (oLines.getFloat(iNuQuan, l));
583 dSubTotal = dSubTotal.add(oLines.getDecimal(iPrSale,l).multiply(dQuantity));
584 } } }
587 return dSubTotal;
588 }
590
592
598
599 public BigDecimal computeSubtotal(JDCConnection oConn) throws SQLException {
600 if (null==oLines) getLines(oConn);
601 return computeSubtotal();
602 }
603
604
606
611 public BigDecimal computeTaxes()
612 throws IllegalStateException {
613
614 if (null == oLines)
615 throw new IllegalStateException ("AbstractOrder.computeTaxes() Lines must be loaded before computing subtotal");
616
617 BigDecimal dTaxes = new BigDecimal (0d);
618 boolean bTaxIncluded;
619 final int iLines = oLines.getRowCount();
620
621 if (iLines > 0) {
622 final int iPrSale = oLines.getColumnPosition(DB.pr_sale);
623 final int iNuQuan = oLines.getColumnPosition(DB.nu_quantity);
624 final int iPctTax = oLines.getColumnPosition(DB.pct_tax_rate);
625 final int iTaxInc = oLines.getColumnPosition(DB.is_tax_included);
626 final BigDecimal dOne = new BigDecimal (1d);
627 BigDecimal dQuantity, dTax;
628
629 for (int l = 0; l < iLines; l++) {
630 if (!oLines.isNull(iPctTax,l) && !oLines.isNull(iPrSale,l)) {
631 if (oLines.isNull(iNuQuan, l))
632 dQuantity = dOne;
633 else
634 dQuantity = new BigDecimal (oLines.getFloat(iNuQuan, l));
635 if (getFloat(DB.pct_tax_rate)>1f)
636 dTax = new BigDecimal (getFloat(DB.pct_tax_rate)/100f);
637 else
638 dTax = new BigDecimal (getFloat(DB.pct_tax_rate));
639 if (oLines.isNull(iTaxInc,l))
640 bTaxIncluded = false;
641 else if (oLines.getShort(iTaxInc,l)==(short)0)
642 bTaxIncluded = false;
643 else
644 bTaxIncluded = true;
645 if (bTaxIncluded)
646 dTaxes = dTaxes.add(oLines.getDecimal(iPrSale,l).multiply(dQuantity).multiply(dTax));
647 }
648 } } return dTaxes;
651 }
653
655 public BigDecimal computeTaxes(JDCConnection oConn) throws SQLException {
656 if (null==oLines) getLines(oConn);
657 return computeTaxes();
658 }
659
660
662
668 public BigDecimal computeTotal() throws IllegalStateException {
669 return computeSubtotal().add(computeTaxes());
670 }
671 }
672 |
Popular Tags |
Java API By Example, From Geeks To Geeks.