|
1
32
33 package com.knowgate.hipergate;
34
35 import java.math.BigDecimal  ;
36
37 import java.sql.Date ;
38 import java.sql.Connection ;
39 import java.sql.SQLException ;
40 import java.sql.CallableStatement ;
41 import java.sql.PreparedStatement ;
42 import java.sql.Statement ;
43 import java.sql.ResultSet ;
44 import java.sql.ResultSetMetaData ;
45 import java.sql.Types ;
46
47 import com.knowgate.debug.DebugFile;
48
49 import com.knowgate.jdc.JDCConnection;
50 import com.knowgate.dataobjs.DB;
51 import com.knowgate.dataobjs.DBPersist;
52 import com.knowgate.dataobjs.DBSubset;
53
54 import com.knowgate.misc.Gadgets;
55
56
61 public class Product extends DBPersist {
62
63
66 public Product() {
67 super(DB.k_products, "Product");
68 }
69
70
74 public Product(String sIdProduct) {
75 super(DB.k_products, "Product");
76
77 put (DB.gu_product, sIdProduct);
78 }
79
80
86 public Product(JDCConnection oConn, String sIdProduct) throws SQLException {
87 super(DB.k_products, "Product");
88
89 Object aProd[] = { sIdProduct };
90
91 load(oConn, aProd);
92 }
94
96
106 public static String getIdFromName(Connection oConn, String sProductNm, String sWorkAreaId)
107 throws SQLException {
108
109 String sProdId;
110
111 PreparedStatement oStmt = oConn.prepareStatement("SELECT "+DB.gu_product+" FROM "+DB.k_products+" WHERE "+DB.nm_product+"=? AND "+DB.gu_owner+"=?",
112 ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
113 oStmt.setString(1, sProductNm);
114 oStmt.setString(2, sWorkAreaId);
115 ResultSet oRSet = oStmt.executeQuery();
116 if (oRSet.next())
117 sProdId = oRSet.getString(1);
118 else
119 sProdId = null;
120 oRSet.close();
121 oStmt.close();
122
123 return sProdId;
124 }
125
126
128
138 public static String getIdFromReference(Connection oConn, String sProductId, String sWorkAreaId)
139 throws SQLException {
140
141 String sProdGuid;
142
143 PreparedStatement oStmt = oConn.prepareStatement("SELECT "+DB.gu_product+" FROM "+DB.k_products+" WHERE "+DB.id_ref+"=? AND "+DB.gu_owner+"=?",
144 ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
145 oStmt.setString(1, sProductId);
146 oStmt.setString(2, sWorkAreaId);
147 ResultSet oRSet = oStmt.executeQuery();
148 if (oRSet.next())
149 sProdGuid = oRSet.getString(1);
150 else
151 sProdGuid = null;
152 oRSet.close();
153 oStmt.close();
154
155 return sProdGuid;
156 }
157
158
160
167 public BigDecimal getFareForDate(JDCConnection oConn, Date dtWhen) throws SQLException {
168 BigDecimal oFare = null;
169
170 if (DebugFile.trace) {
171 DebugFile.writeln("Begin Product.getFare([Connection],"+dtWhen.toString()+")" );
172 DebugFile.incIdent();
173 }
174
175 if (oConn.getDataBaseProduct()==JDCConnection.DBMS_POSTGRESQL) {
176 if (DebugFile.trace) {
177 DebugFile.writeln("Connection.prepareStatement(SELECT "+DB.pr_sale+" FROM "+DB.k_prod_fares+" WHERE "+DB.gu_product+"='"+getStringNull(DB.gu_product,null)+"' AND "+DB.dt_start+"<=? AND "+DB.dt_end+">=?)");
178 }
179 PreparedStatement oStmt = oConn.prepareStatement("SELECT "+DB.pr_sale+" FROM "+DB.k_prod_fares+" WHERE "+DB.gu_product+"=? AND "+DB.dt_start+"<=? AND "+DB.dt_end+">=?",ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_READ_ONLY);
180 oStmt.setString(1, getStringNull(DB.gu_product,null));
181 oStmt.setDate(2, dtWhen);
182 oStmt.setDate(3, dtWhen);
183 ResultSet oRSet = oStmt.executeQuery();
184 if (oRSet.next())
185 oFare = oRSet.getBigDecimal(1);
186 oRSet.close();
187 oStmt.close();
188 } else {
189 if (DebugFile.trace) {
190 DebugFile.writeln("Connection.prepareCall({ call k_sp_get_date_fare('"+getStringNull(DB.gu_product,null)+"','"+dtWhen.toString()+"',?) }");
191 }
192 CallableStatement oCall = oConn.prepareCall("{ call k_sp_get_date_fare(?,?,?) }");
193 oCall.setString(1, getStringNull(DB.gu_product,null));
194 oCall.setDate(2, dtWhen);
195 oCall.registerOutParameter(3, java.sql.Types.DECIMAL);
196 oCall.execute();
197 oFare = oCall.getBigDecimal(3);
198 oCall.close();
199 }
200
201 if (DebugFile.trace) {
202 DebugFile.decIdent();
203 if (null==oFare)
204 DebugFile.writeln("End Product.getFare() : null");
205 else
206 DebugFile.writeln("End Product.getFare() : "+oFare.toString());
207 }
208 return oFare;
209 }
210
211
213
221 public BigDecimal getFare(JDCConnection oConn, String sIdFare) throws SQLException {
222 BigDecimal oFare = null;
223
224 if (DebugFile.trace) {
225 DebugFile.writeln("Begin Product.getFare([Connection],"+sIdFare+")" );
226 DebugFile.incIdent();
227 }
228
229 if (oConn.getDataBaseProduct()==JDCConnection.DBMS_POSTGRESQL) {
230 if (DebugFile.trace) {
231 DebugFile.writeln("Connection.prepareStatement(SELECT "+DB.pr_sale+" FROM "+DB.k_prod_fares+" WHERE "+DB.gu_product+"='"+getStringNull(DB.gu_product,null)+"' AND "+DB.id_fare+"='"+sIdFare+"')");
232 }
233 PreparedStatement oStmt = oConn.prepareStatement("SELECT "+DB.pr_sale+" FROM "+DB.k_prod_fares+" WHERE "+DB.gu_product+"=? AND "+DB.id_fare+"=?",ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_READ_ONLY);
234 oStmt.setString(1, getStringNull(DB.gu_product,null));
235 oStmt.setString(2, sIdFare);
236 ResultSet oRSet = oStmt.executeQuery();
237 if (oRSet.next())
238 oFare = oRSet.getBigDecimal(1);
239 oRSet.close();
240 oStmt.close();
241 } else {
242 if (DebugFile.trace) {
243 DebugFile.writeln("Connection.prepareCall({ call k_sp_get_prod_fare('"+getStringNull(DB.gu_product,null)+"','"+sIdFare+"',?) }");
244 }
245 CallableStatement oCall = oConn.prepareCall("{ call k_sp_get_prod_fare(?,?,?) }");
246 oCall.setString(1, getStringNull(DB.gu_product,null));
247 oCall.setString(2, sIdFare);
248 oCall.registerOutParameter(3, java.sql.Types.DECIMAL);
249 oCall.execute();
250 oFare = oCall.getBigDecimal(3);
251 oCall.close();
252 }
253
254 if (DebugFile.trace) {
255 DebugFile.decIdent();
256 if (null==oFare)
257 DebugFile.writeln("End Product.getFare() : null");
258 else
259 DebugFile.writeln("End Product.getFare() : "+oFare.toString());
260 }
261
262 return oFare;
263 }
264
265
267
274 public DBSubset getFares(JDCConnection oConn) throws SQLException {
275 DBSubset oFares = new DBSubset(DB.k_prod_fares,DB.id_fare+","+DB.pr_sale+","+
276 DB.id_currency+","+DB.pct_tax_rate+","+
277 DB.is_tax_included+","+DB.dt_start+","+DB.dt_end+","+
278 DB.tp_fare,DB.gu_product+"=?", 10);
279 oFares.load(oConn, new Object []{getStringNull(DB.gu_product,null)});
280
281 return oFares;
282 }
283
284
286
293 public DBSubset getFaresOfType(JDCConnection oConn, String sType) throws SQLException {
294 DBSubset oFares = new DBSubset(DB.k_prod_fares,DB.id_fare+","+DB.pr_sale+","+
295 DB.id_currency+","+DB.pct_tax_rate+","+
296 DB.is_tax_included+","+DB.dt_start+","+DB.dt_end+","+
297 DB.tp_fare,DB.gu_product+"=? AND "+DB.tp_fare+"=?", 10);
298 oFares.load(oConn, new Object []{getStringNull(DB.gu_product,null),sType});
299 return oFares;
300 }
301
302
304
311 public DBSubset getFaresOfType(JDCConnection oConn, String sType, Date dtWhen) throws SQLException {
312 DBSubset oFares = new DBSubset(DB.k_prod_fares,DB.id_fare+","+DB.pr_sale+","+
313 DB.id_currency+","+DB.pct_tax_rate+","+
314 DB.is_tax_included+","+DB.dt_start+","+DB.dt_end+","+
315 DB.tp_fare,DB.gu_product+"=? AND "+DB.tp_fare+"=? AND "+
316 "("+DB.dt_start+" IS NULL OR "+DB.dt_start+"<=?) AND "+
317 "("+DB.dt_end+" IS NULL OR "+DB.dt_end+">=?)", 10);
318 oFares.load(oConn, new Object []{getStringNull(DB.gu_product,null),sType,dtWhen,dtWhen});
319 return oFares;
320 }
321
322
324
332 public DBSubset getImages(JDCConnection oConn) throws SQLException {
333 if (DebugFile.trace) {
334 DebugFile.writeln("Begin Product.getImages([Connection])" );
335 DebugFile.incIdent();
336 }
337
338 int iLoca;
339 Image oImg = new Image();
340 Object aProd[] = { get(DB.gu_product) };
341
342 oLocations = new DBSubset (DB.k_images, oImg.getTable(oConn).getColumnsStr(), DB.gu_product + "=?", 10);
343 iLoca = oLocations.load (oConn, aProd);
344
345 oImg = null;
346
347 if (DebugFile.trace) {
348 DebugFile.decIdent();
349 DebugFile.writeln("End Product.getImages()");
350 }
351
352 return oLocations;
353 }
355
357
371 public DBSubset getLocations(JDCConnection oConn) throws SQLException {
372 if (DebugFile.trace) {
373 DebugFile.writeln("Begin Product.getLocations([Connection])" );
374 DebugFile.incIdent();
375 }
376 int iLoca;
377 ProductLocation oLoca = new ProductLocation();
378 Object aProd[] = { get(DB.gu_product) };
379
380 oLocations = new DBSubset (DB.k_prod_locats, oLoca.getTable(oConn).getColumnsStr(), DB.gu_product + "=?", 10);
381 iLoca = oLocations.load (oConn, aProd);
382
383 oLoca = null;
384
385 if (DebugFile.trace) {
386 DebugFile.decIdent();
387 DebugFile.writeln("End Product.getLocations() : " + String.valueOf(iLoca));
388 }
389
390 return oLocations;
391 }
393
395
406 public ProductLocation getFirstLocation(JDCConnection oConn) throws SQLException {
407 ResultSet oRSet;
408 ResultSetMetaData oMeta;
409 PreparedStatement oStmt;
410 ProductLocation oLoca;
411 Object oVal;
412 int iColCount;
413
414 if (DebugFile.trace) {
415 DebugFile.writeln("Begin Product.getFirstLocation()" );
416 DebugFile.incIdent();
417 }
418
419 oStmt = oConn.prepareStatement("SELECT l.* FROM " + DB.k_prod_locats + " l, " + DB.k_products + " p WHERE l." + DB.gu_product + "=p." + DB.gu_product + " AND p." + DB.gu_product + "=?", ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
420
421 oStmt.setString(1,getString(DB.gu_product));
422 oRSet = oStmt.executeQuery();
423
424 if (oRSet.next()) {
425 oLoca = new ProductLocation();
426 oMeta = oRSet.getMetaData();
427 iColCount = oMeta.getColumnCount();
428
429 for (int iCol=1; iCol<=iColCount; iCol++) {
430 oVal = oRSet.getObject(iCol);
431 if (null!=oVal) oLoca.put(oMeta.getColumnName(iCol).toLowerCase(), oVal);
432 }
433 }
434 else
435 oLoca = null;
436
437 if (DebugFile.trace) {
438 DebugFile.decIdent();
439 DebugFile.writeln("End Product.getFirstLocation()");
440 }
441
442 return oLoca;
443 }
445
447
458 public boolean store(JDCConnection oConn) throws SQLException {
459 java.sql.Timestamp dtSQL = new java.sql.Timestamp (new java.util.Date ().
460 getTime());
461
462 if (DebugFile.trace) {
463 DebugFile.writeln("Begin Product.store()");
464 DebugFile.incIdent();
465 }
466
467 boolean bNew = !AllVals.containsKey(DB.gu_product);
468
469 if (bNew)
470
471 put(DB.gu_product, Gadgets.generateUUID());
472
473 else if (!AllVals.containsKey(DB.dt_modified) && exists(oConn))
474
475 put (DB.dt_modified, dtSQL);
476
477 if (!AllVals.containsKey(DB.dt_uploaded))
478 put (DB.dt_uploaded, dtSQL);
479
480 if (!AllVals.containsKey(DB.is_compound))
481 put (DB.is_compound, new Short ((short)0));
482
483 if (!AllVals.containsKey(DB.id_status))
484 put (DB.id_status, new Short ((short)1));
485
486 boolean bRetVal = super.store(oConn);
487
488 if (bNew) {
489 DebugFile.writeln("new ProductAttribute("+getStringNull(DB.gu_product,"null")+")");
490 new ProductAttribute(getString(DB.gu_product)).store(oConn);
491 }
492
493 if (DebugFile.trace) {
494 DebugFile.decIdent();
495 DebugFile.writeln("End Product.store() : " + getString(DB.gu_product) );
496 }
497
498 return bRetVal;
499 }
501
503
510 public boolean delete(JDCConnection oConn) throws SQLException {
511 CallableStatement oStmt;
512
513 if (DebugFile.trace) {
514 DebugFile.writeln("Begin Product.delete(Connection)" );
515 DebugFile.incIdent();
516 }
517
518 try {
519 eraseImages(oConn);
520 } catch (SQLException sqle) { if (DebugFile.trace) DebugFile.writeln("SQLException: " + sqle.getMessage());}
521
522 eraseLocations(oConn);
524
525 if (DebugFile.trace) DebugFile.writeln("Connection.prepareCall({call k_sp_del_product ('" + getStringNull(DB.gu_product,"null") + "')}");
526
527 oStmt = oConn.prepareCall("{call k_sp_del_product ('" + getString(DB.gu_product) + "')}");
528 oStmt.execute();
529 oStmt.close();
530
532 if (DebugFile.trace) {
533 DebugFile.decIdent();
534 DebugFile.writeln("End Product.delete() : " + getString(DB.gu_product) );
535 }
536
537 return true;
538 }
540
542
548 private void eraseImages(JDCConnection oConn) throws SQLException {
549 if (DebugFile.trace) {
550 DebugFile.writeln("Begin Product.eraseImages(Connection)" );
551 DebugFile.incIdent();
552 }
553
554 DBSubset oImages = new DBSubset(DB.k_images, DB.gu_image + "," + DB.path_image, DB.gu_product + "=?", 10);
555 int iImgCount = oImages.load(oConn, new Object []{getString(DB.gu_product)});
556 Image oImg = new Image();
557
558 for (int i=0; i<iImgCount; i++) {
559 oImg.replace(DB.gu_image, oImages.get(0,i));
560 oImg.replace(DB.path_image, oImages.get(1,i));
561 oImg.delete(oConn);
562 }
564 oImg = null;
565 oImages = null;
566
567 if (DebugFile.trace) {
568 DebugFile.decIdent();
569 DebugFile.writeln("End Product.eraseImages()");
570 }
571 }
573
575
581 public int eraseLocations(JDCConnection oConn) throws SQLException {
582
583 if (DebugFile.trace) {
584 DebugFile.writeln("Begin Product.eraseLocations(Connection)" );
585 DebugFile.incIdent();
586 }
587
588 DBSubset oLocs = getLocations(oConn);
589 int iLocs = oLocs.getRowCount();
590 ProductLocation oLoca = new ProductLocation();
591
592 for (int f=0; f<iLocs; f++) {
593 oLoca = new ProductLocation(oConn, oLocs.getString(0,f));
594 oLoca.delete(oConn);
595 }
597 if (DebugFile.trace) {
598 DebugFile.decIdent();
599 DebugFile.writeln("End Product.eraseLocations() : " + oLocs.getRowCount() );
600 }
601
602 return oLocs.getRowCount();
603 }
605
607
618 public int addToCategory(JDCConnection oConn, String idCategory, int iOdPosition) throws SQLException {
619 boolean bAlreadyExists;
620 int iRetVal;
621 PreparedStatement oStmt;
622 ResultSet oRSet;
623
624 oStmt = oConn.prepareStatement("SELECT NULL FROM " + DB.k_x_cat_objs + " WHERE " + DB.gu_category + "=? AND " + DB.gu_object + "=?");
625 oStmt.setString(1, idCategory);
626 oStmt.setString(2, getString(DB.gu_product));
627 oRSet = oStmt.executeQuery();
628 bAlreadyExists = oRSet.next();
629 oRSet.close();
630 oStmt.close();
631
632 if (!bAlreadyExists) {
633 oStmt = oConn.prepareStatement("INSERT INTO " + DB.k_x_cat_objs + " (" + DB.gu_category + "," + DB.gu_object + "," + DB.id_class + "," + DB.od_position + ") VALUES (?,?,?,?)");
634 oStmt.setString(1, idCategory);
635 oStmt.setString(2, getString(DB.gu_product));
636 oStmt.setInt (3, ClassId);
637 oStmt.setInt (4, iOdPosition);
638 iRetVal = oStmt.executeUpdate();
639 oStmt.close();
640 }
641 else
642 iRetVal = 0;
643
644 return iRetVal;
645 }
646
647
649
655 public int removeFromCategory(JDCConnection oConn, String idCategory) throws SQLException {
656 int iDeleted = 0;
657 PreparedStatement oStmt;
658
659 oStmt = oConn.prepareStatement("DELETE FROM " + DB.k_x_cat_objs + " WHERE " + DB.gu_category + "=? AND " + DB.gu_object + "=? AND " + DB.id_class + "=" + String.valueOf(ClassId));
660 oStmt.setString(1, idCategory);
661 oStmt.setString(2, getString(DB.gu_product));
662 iDeleted = oStmt.executeUpdate();
663 oStmt.close();
664
665 return iDeleted;
666 }
667
668
670
678 public Integer getPosition(JDCConnection oConn, String sCategoryId) throws SQLException {
679 Statement oStmt;
680 ResultSet oRSet;
681 CallableStatement oCall;
682 Object oPos;
683 Integer iPos;
684
685 if (DebugFile.trace) {
686 DebugFile.writeln("Begin Product.getPosition([Connection], " + sCategoryId + ")" );
687 DebugFile.incIdent();
688 }
689
690 if (oConn.getDataBaseProduct()==JDCConnection.DBMS_POSTGRESQL) {
691 oStmt = oConn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
692
693 if (DebugFile.trace) DebugFile.writeln("Statement.executeQuery(SELECT k_sp_cat_obj_position('" + getStringNull(DB.gu_product, "null") + "','" + sCategoryId + "'))");
694
695 oRSet = oStmt.executeQuery("SELECT k_sp_cat_obj_position ('" + getString(DB.gu_product) + "','" + sCategoryId + "')");
696 oRSet.next();
697 oPos = new Integer (oRSet.getInt(1));
698 oRSet.close();
699 oStmt.close();
700 }
701 else {
702 oCall = oConn.prepareCall("{ call k_sp_cat_obj_position('" + getString(DB.gu_product) + "','" + sCategoryId + "',?)}");
703 oCall.registerOutParameter(1, Types.INTEGER);
704 oCall.execute();
705 oPos = oCall.getObject(1);
706 oCall.close();
707 oCall = null;
708 }
709
710 if (null==oPos)
711 iPos = null;
712 else
713 iPos = new Integer (oPos.toString());
714
715 if (DebugFile.trace) {
716 DebugFile.decIdent();
717 DebugFile.writeln("End Product.getPosition()");
718 }
719
720 return iPos;
721 }
723
725
748 public BigDecimal salePrice(java.util.Date dtForDate) {
749 java.util.Date dtForDateStart;
750 java.util.Date dtForDateEnd;
751 BigDecimal dRetVal;
752
753 if (DebugFile.trace) {
754 DebugFile.writeln("Begin Product.salePrice(" + dtForDate + ")" );
755 DebugFile.incIdent();
756 }
757
758 if (isNull(DB.pr_list) && isNull(DB.pr_sale))
759 dRetVal = null;
760 else {
761 if ((isNull(DB.dt_start) && isNull(DB.dt_end)) || null==dtForDate) {
762 if (isNull(DB.pr_list))
763 dRetVal = null;
764 else
765 dRetVal = getDecimal(DB.pr_list);
766 }
767 else {
768 dtForDateStart = new java.util.Date (dtForDate.getTime());
769 dtForDateStart.setHours(0); dtForDateStart.setMinutes(1); dtForDateStart.setSeconds(1);
770 dtForDateEnd = new java.util.Date (dtForDate.getTime());
771 dtForDateEnd.setHours(0); dtForDateEnd.setMinutes(1); dtForDateEnd.setSeconds(1);
772
773 if (!isNull(DB.dt_start)) {
774 if (isNull(DB.dt_end))
775 if (dtForDateStart.compareTo(getDate(DB.dt_start))>0)
776 dRetVal = getDecimal(DB.pr_sale);
777 else
778 dRetVal = getDecimal(DB.pr_list);
779 else
780 if (dtForDateStart.compareTo(getDate(DB.dt_start))>0 && dtForDateEnd.compareTo(getDate(DB.dt_end))<0)
781 dRetVal = getDecimal(DB.pr_sale);
782 else
783 dRetVal = getDecimal(DB.pr_list);
784 }
785 else {
786 if (dtForDateEnd.compareTo(getDate(DB.dt_end))<0)
787 dRetVal = getDecimal(DB.pr_list);
788 else
789 dRetVal = getDecimal(DB.pr_sale);
790 }
791 } }
794 if (DebugFile.trace) {
795 DebugFile.decIdent();
796 DebugFile.writeln("End Product.salePrice() : " + dRetVal);
797 }
798
799 return dRetVal;
800 }
802
804 private DBSubset oLocations;
805
806 public static final short ClassId = 15;
807 } |
Popular Tags |
Java API By Example, From Geeks To Geeks.