001: /*
002: Copyright (C) 2003 Know Gate S.L. All rights reserved.
003: C/Oņa, 107 1š2 28050 Madrid (Spain)
004:
005: Redistribution and use in source and binary forms, with or without
006: modification, are permitted provided that the following conditions
007: are met:
008:
009: 1. Redistributions of source code must retain the above copyright
010: notice, this list of conditions and the following disclaimer.
011:
012: 2. The end-user documentation included with the redistribution,
013: if any, must include the following acknowledgment:
014: "This product includes software parts from hipergate
015: (http://www.hipergate.org/)."
016: Alternately, this acknowledgment may appear in the software itself,
017: if and wherever such third-party acknowledgments normally appear.
018:
019: 3. The name hipergate must not be used to endorse or promote products
020: derived from this software without prior written permission.
021: Products derived from this software may not be called hipergate,
022: nor may hipergate appear in their name, without prior written
023: permission.
024:
025: This library is distributed in the hope that it will be useful,
026: but WITHOUT ANY WARRANTY; without even the implied warranty of
027: MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
028:
029: You should have received a copy of hipergate License with this code;
030: if not, visit http://www.hipergate.org or mail to info@hipergate.org
031: */
032:
033: package com.knowgate.hipergate;
034:
035: import java.math.BigDecimal;
036:
037: import java.sql.Date;
038: import java.sql.Connection;
039: import java.sql.SQLException;
040: import java.sql.CallableStatement;
041: import java.sql.PreparedStatement;
042: import java.sql.Statement;
043: import java.sql.ResultSet;
044: import java.sql.ResultSetMetaData;
045: import java.sql.Types;
046:
047: import com.knowgate.debug.DebugFile;
048:
049: import com.knowgate.jdc.JDCConnection;
050: import com.knowgate.dataobjs.DB;
051: import com.knowgate.dataobjs.DBPersist;
052: import com.knowgate.dataobjs.DBSubset;
053:
054: import com.knowgate.misc.Gadgets;
055:
056: /**
057: * Read and write products from k_products table
058: * @author Sergio Montoro Ten
059: * @version 3.0
060: */
061: public class Product extends DBPersist {
062:
063: /**
064: * Create empty Product
065: */
066: public Product() {
067: super (DB.k_products, "Product");
068: }
069:
070: /**
071: * Create empty Product and set gu_product
072: * @param sIdProduct GUID for Product
073: */
074: public Product(String sIdProduct) {
075: super (DB.k_products, "Product");
076:
077: put(DB.gu_product, sIdProduct);
078: }
079:
080: /**
081: * Load Product from database
082: * @param oConn Database Connection
083: * @param sIdProduct GUID of Product to be loaded
084: * @throws SQLException
085: */
086: public Product(JDCConnection oConn, String sIdProduct)
087: throws SQLException {
088: super (DB.k_products, "Product");
089:
090: Object aProd[] = { sIdProduct };
091:
092: load(oConn, aProd);
093: } // Product
094:
095: // ----------------------------------------------------------
096:
097: /**
098: * Get product GUID given its Name (nm_product) and WorkArea (gu_owner)
099: * @param oConn Connection
100: * @param sProductNm String Product Name (k_products.nm_product column)
101: * @param sWorkAreaId String WorkArea GUID (k_products.gu_owner column)
102: * @return String GUID of product or <b>null</b> if no product with such name
103: * was found at given WorkArea
104: * @throws SQLException
105: * @since 3.0
106: */
107: public static String getIdFromName(Connection oConn,
108: String sProductNm, String sWorkAreaId) throws SQLException {
109:
110: String sProdId;
111:
112: PreparedStatement oStmt = oConn
113: .prepareStatement("SELECT " + DB.gu_product + " FROM "
114: + DB.k_products + " WHERE " + DB.nm_product
115: + "=? AND " + DB.gu_owner + "=?",
116: ResultSet.TYPE_FORWARD_ONLY,
117: ResultSet.CONCUR_READ_ONLY);
118: oStmt.setString(1, sProductNm);
119: oStmt.setString(2, sWorkAreaId);
120: ResultSet oRSet = oStmt.executeQuery();
121: if (oRSet.next())
122: sProdId = oRSet.getString(1);
123: else
124: sProdId = null;
125: oRSet.close();
126: oStmt.close();
127:
128: return sProdId;
129: }
130:
131: // ----------------------------------------------------------
132:
133: /**
134: * Get product GUID given its Reference (id_ref) and WorkArea (gu_owner)
135: * @param oConn Connection
136: * @param sProductId String Product Reference (k_products.id_ref column)
137: * @param sWorkAreaId String WorkArea GUID (k_products.gu_owner column)
138: * @return String GUID of product or <b>null</b> if no product with such reference
139: * was found at given WorkArea
140: * @throws SQLException
141: * @since 3.0
142: */
143: public static String getIdFromReference(Connection oConn,
144: String sProductId, String sWorkAreaId) throws SQLException {
145:
146: String sProdGuid;
147:
148: PreparedStatement oStmt = oConn
149: .prepareStatement("SELECT " + DB.gu_product + " FROM "
150: + DB.k_products + " WHERE " + DB.id_ref
151: + "=? AND " + DB.gu_owner + "=?",
152: ResultSet.TYPE_FORWARD_ONLY,
153: ResultSet.CONCUR_READ_ONLY);
154: oStmt.setString(1, sProductId);
155: oStmt.setString(2, sWorkAreaId);
156: ResultSet oRSet = oStmt.executeQuery();
157: if (oRSet.next())
158: sProdGuid = oRSet.getString(1);
159: else
160: sProdGuid = null;
161: oRSet.close();
162: oStmt.close();
163:
164: return sProdGuid;
165: }
166:
167: // ----------------------------------------------------------
168:
169: /**
170: * Get first fare found valid for a given date
171: * @param oConn JDCConnection
172: * @param dtWhen Date
173: * @return BigDecimal
174: * @throws SQLException
175: */
176: public BigDecimal getFareForDate(JDCConnection oConn, Date dtWhen)
177: throws SQLException {
178: BigDecimal oFare = null;
179:
180: if (DebugFile.trace) {
181: DebugFile.writeln("Begin Product.getFare([Connection],"
182: + dtWhen.toString() + ")");
183: DebugFile.incIdent();
184: }
185:
186: if (oConn.getDataBaseProduct() == JDCConnection.DBMS_POSTGRESQL) {
187: if (DebugFile.trace) {
188: DebugFile
189: .writeln("Connection.prepareStatement(SELECT "
190: + DB.pr_sale + " FROM "
191: + DB.k_prod_fares + " WHERE "
192: + DB.gu_product + "='"
193: + getStringNull(DB.gu_product, null)
194: + "' AND " + DB.dt_start + "<=? AND "
195: + DB.dt_end + ">=?)");
196: }
197: PreparedStatement oStmt = oConn.prepareStatement("SELECT "
198: + DB.pr_sale + " FROM " + DB.k_prod_fares
199: + " WHERE " + DB.gu_product + "=? AND "
200: + DB.dt_start + "<=? AND " + DB.dt_end + ">=?",
201: ResultSet.TYPE_FORWARD_ONLY,
202: ResultSet.CONCUR_READ_ONLY);
203: oStmt.setString(1, getStringNull(DB.gu_product, null));
204: oStmt.setDate(2, dtWhen);
205: oStmt.setDate(3, dtWhen);
206: ResultSet oRSet = oStmt.executeQuery();
207: if (oRSet.next())
208: oFare = oRSet.getBigDecimal(1);
209: oRSet.close();
210: oStmt.close();
211: } else {
212: if (DebugFile.trace) {
213: DebugFile
214: .writeln("Connection.prepareCall({ call k_sp_get_date_fare('"
215: + getStringNull(DB.gu_product, null)
216: + "','" + dtWhen.toString() + "',?) }");
217: }
218: CallableStatement oCall = oConn
219: .prepareCall("{ call k_sp_get_date_fare(?,?,?) }");
220: oCall.setString(1, getStringNull(DB.gu_product, null));
221: oCall.setDate(2, dtWhen);
222: oCall.registerOutParameter(3, java.sql.Types.DECIMAL);
223: oCall.execute();
224: oFare = oCall.getBigDecimal(3);
225: oCall.close();
226: }
227:
228: if (DebugFile.trace) {
229: DebugFile.decIdent();
230: if (null == oFare)
231: DebugFile.writeln("End Product.getFare() : null");
232: else
233: DebugFile.writeln("End Product.getFare() : "
234: + oFare.toString());
235: }
236: return oFare;
237: }
238:
239: // ----------------------------------------------------------
240:
241: /**
242: * Get a given fare price for a product
243: * @param oConn JDCConnection
244: * @param sIdFare String Identifier of fare to wich price is going to be retrived
245: * @return BigDecimal Product price for the fare or <b>null</b> if fare does not exist.
246: * @throws SQLException
247: * @since v2.2
248: */
249: public BigDecimal getFare(JDCConnection oConn, String sIdFare)
250: throws SQLException {
251: BigDecimal oFare = null;
252:
253: if (DebugFile.trace) {
254: DebugFile.writeln("Begin Product.getFare([Connection],"
255: + sIdFare + ")");
256: DebugFile.incIdent();
257: }
258:
259: if (oConn.getDataBaseProduct() == JDCConnection.DBMS_POSTGRESQL) {
260: if (DebugFile.trace) {
261: DebugFile.writeln("Connection.prepareStatement(SELECT "
262: + DB.pr_sale + " FROM " + DB.k_prod_fares
263: + " WHERE " + DB.gu_product + "='"
264: + getStringNull(DB.gu_product, null) + "' AND "
265: + DB.id_fare + "='" + sIdFare + "')");
266: }
267: PreparedStatement oStmt = oConn.prepareStatement("SELECT "
268: + DB.pr_sale + " FROM " + DB.k_prod_fares
269: + " WHERE " + DB.gu_product + "=? AND "
270: + DB.id_fare + "=?", ResultSet.TYPE_FORWARD_ONLY,
271: ResultSet.CONCUR_READ_ONLY);
272: oStmt.setString(1, getStringNull(DB.gu_product, null));
273: oStmt.setString(2, sIdFare);
274: ResultSet oRSet = oStmt.executeQuery();
275: if (oRSet.next())
276: oFare = oRSet.getBigDecimal(1);
277: oRSet.close();
278: oStmt.close();
279: } else {
280: if (DebugFile.trace) {
281: DebugFile
282: .writeln("Connection.prepareCall({ call k_sp_get_prod_fare('"
283: + getStringNull(DB.gu_product, null)
284: + "','" + sIdFare + "',?) }");
285: }
286: CallableStatement oCall = oConn
287: .prepareCall("{ call k_sp_get_prod_fare(?,?,?) }");
288: oCall.setString(1, getStringNull(DB.gu_product, null));
289: oCall.setString(2, sIdFare);
290: oCall.registerOutParameter(3, java.sql.Types.DECIMAL);
291: oCall.execute();
292: oFare = oCall.getBigDecimal(3);
293: oCall.close();
294: }
295:
296: if (DebugFile.trace) {
297: DebugFile.decIdent();
298: if (null == oFare)
299: DebugFile.writeln("End Product.getFare() : null");
300: else
301: DebugFile.writeln("End Product.getFare() : "
302: + oFare.toString());
303: }
304:
305: return oFare;
306: }
307:
308: // ----------------------------------------------------------
309:
310: /**
311: * Get product fares
312: * @param oConn JDCConnection
313: * @return DBSubset with the following columns<br>
314: * <table><tr><td>id_fare</td><td>pr_sale</td><td>id_currency</td>pct_tax_rate</td><td>is_tax_included</td><td>dt_start</td><td>dt_end</td><td>tp_fare</td></tr></table>
315: * @throws SQLException
316: */
317: public DBSubset getFares(JDCConnection oConn) throws SQLException {
318: DBSubset oFares = new DBSubset(DB.k_prod_fares, DB.id_fare
319: + "," + DB.pr_sale + "," + DB.id_currency + ","
320: + DB.pct_tax_rate + "," + DB.is_tax_included + ","
321: + DB.dt_start + "," + DB.dt_end + "," + DB.tp_fare,
322: DB.gu_product + "=?", 10);
323: oFares.load(oConn, new Object[] { getStringNull(DB.gu_product,
324: null) });
325:
326: return oFares;
327: }
328:
329: // ----------------------------------------------------------
330:
331: /**
332: * Get product fares of a given type
333: * @param oConn JDCConnection
334: * @return DBSubset with the following columns<br>
335: * <table><tr><td>id_fare</td><td>pr_sale</td><td>id_currency</td>pct_tax_rate</td><td>is_tax_included</td><td>dt_start</td><td>dt_end</td><td>tp_fare</td></tr></table>
336: * @throws SQLException
337: */
338: public DBSubset getFaresOfType(JDCConnection oConn, String sType)
339: throws SQLException {
340: DBSubset oFares = new DBSubset(DB.k_prod_fares, DB.id_fare
341: + "," + DB.pr_sale + "," + DB.id_currency + ","
342: + DB.pct_tax_rate + "," + DB.is_tax_included + ","
343: + DB.dt_start + "," + DB.dt_end + "," + DB.tp_fare,
344: DB.gu_product + "=? AND " + DB.tp_fare + "=?", 10);
345: oFares.load(oConn, new Object[] {
346: getStringNull(DB.gu_product, null), sType });
347: return oFares;
348: }
349:
350: // ----------------------------------------------------------
351:
352: /**
353: * Get product fares of a given type valid for the specified date
354: * @param oConn JDCConnection
355: * @return DBSubset with the following columns<br>
356: * <table><tr><td>id_fare</td><td>pr_sale</td><td>id_currency</td>pct_tax_rate</td><td>is_tax_included</td><td>dt_start</td><td>dt_end</td><td>tp_fare</td></tr></table>
357: * @throws SQLException
358: */
359: public DBSubset getFaresOfType(JDCConnection oConn, String sType,
360: Date dtWhen) throws SQLException {
361: DBSubset oFares = new DBSubset(DB.k_prod_fares, DB.id_fare
362: + "," + DB.pr_sale + "," + DB.id_currency + ","
363: + DB.pct_tax_rate + "," + DB.is_tax_included + ","
364: + DB.dt_start + "," + DB.dt_end + "," + DB.tp_fare,
365: DB.gu_product + "=? AND " + DB.tp_fare + "=? AND "
366: + "(" + DB.dt_start + " IS NULL OR "
367: + DB.dt_start + "<=?) AND " + "(" + DB.dt_end
368: + " IS NULL OR " + DB.dt_end + ">=?)", 10);
369: oFares.load(oConn, new Object[] {
370: getStringNull(DB.gu_product, null), sType, dtWhen,
371: dtWhen });
372: return oFares;
373: }
374:
375: // ----------------------------------------------------------
376:
377: /**
378: * Get Images associated to this Product
379: * @param oConn Database Connection
380: * @return A DBSubset with all columns from k_images table for images with
381: * gu_product field is equal to this Product GUID.
382: * @throws SQLException
383: * @see com.knowgate.hipergate.Image
384: */
385: public DBSubset getImages(JDCConnection oConn) throws SQLException {
386: if (DebugFile.trace) {
387: DebugFile.writeln("Begin Product.getImages([Connection])");
388: DebugFile.incIdent();
389: }
390:
391: int iLoca;
392: Image oImg = new Image();
393: Object aProd[] = { get(DB.gu_product) };
394:
395: oLocations = new DBSubset(DB.k_images, oImg.getTable(oConn)
396: .getColumnsStr(), DB.gu_product + "=?", 10);
397: iLoca = oLocations.load(oConn, aProd);
398:
399: oImg = null;
400:
401: if (DebugFile.trace) {
402: DebugFile.decIdent();
403: DebugFile.writeln("End Product.getImages()");
404: }
405:
406: return oLocations;
407: } // getImages
408:
409: // ----------------------------------------------------------
410:
411: /**
412: * <p>Get Product Locations</p>
413: * Location semantics depend upon what Product is used for.<br>
414: * <ul>
415: * <li>For Downloadable Products, ProductLocations represent mirror download URLs.
416: * <li>For Versioned Products, ProductLocations represent different versions of the same File.
417: * <li>For Compound Products, ProductLocations represent parts of the Product each one being a File.
418: * <li>For Physical Products, ProductLocations represent stock of Product at different warehouses.
419: * </ul>
420: * @param oConn Database Connection
421: * @return A DBSubset with all columns from k_prod_locats for ProductLocations
422: * with gu_product is equal to this Product GUID.
423: * @throws SQLException
424: */
425: public DBSubset getLocations(JDCConnection oConn)
426: throws SQLException {
427: if (DebugFile.trace) {
428: DebugFile
429: .writeln("Begin Product.getLocations([Connection])");
430: DebugFile.incIdent();
431: }
432: int iLoca;
433: ProductLocation oLoca = new ProductLocation();
434: Object aProd[] = { get(DB.gu_product) };
435:
436: oLocations = new DBSubset(DB.k_prod_locats, oLoca.getTable(
437: oConn).getColumnsStr(), DB.gu_product + "=?", 10);
438: iLoca = oLocations.load(oConn, aProd);
439:
440: oLoca = null;
441:
442: if (DebugFile.trace) {
443: DebugFile.decIdent();
444: DebugFile.writeln("End Product.getLocations() : "
445: + String.valueOf(iLoca));
446: }
447:
448: return oLocations;
449: } // getLocations
450:
451: // ----------------------------------------------------------
452:
453: /**
454: * <p>Get First ProductLocation for this Product.</p>
455: * First ProductLocation is that one find in the first place when querying
456: * to the database. Thus there is no particular criteria for what is a first
457: * ProductLocation. This method is particularly usefull when retrieving
458: * the ProductLocation for products that always have a single Productlocation.
459: * @param oConn Database Connection
460: * @return ProductLocation or <b>null</b> if no ProductLocation is found at
461: * k_prod_locats with gu_product equal to this Product GUID.
462: * @throws SQLException
463: */
464: public ProductLocation getFirstLocation(JDCConnection oConn)
465: throws SQLException {
466: ResultSet oRSet;
467: ResultSetMetaData oMeta;
468: PreparedStatement oStmt;
469: ProductLocation oLoca;
470: Object oVal;
471: int iColCount;
472:
473: if (DebugFile.trace) {
474: DebugFile.writeln("Begin Product.getFirstLocation()");
475: DebugFile.incIdent();
476: }
477:
478: oStmt = oConn
479: .prepareStatement("SELECT l.* FROM " + DB.k_prod_locats
480: + " l, " + DB.k_products + " p WHERE l."
481: + DB.gu_product + "=p." + DB.gu_product
482: + " AND p." + DB.gu_product + "=?",
483: ResultSet.TYPE_FORWARD_ONLY,
484: ResultSet.CONCUR_READ_ONLY);
485:
486: oStmt.setString(1, getString(DB.gu_product));
487: oRSet = oStmt.executeQuery();
488:
489: if (oRSet.next()) {
490: oLoca = new ProductLocation();
491: oMeta = oRSet.getMetaData();
492: iColCount = oMeta.getColumnCount();
493:
494: for (int iCol = 1; iCol <= iColCount; iCol++) {
495: oVal = oRSet.getObject(iCol);
496: if (null != oVal)
497: oLoca.put(oMeta.getColumnName(iCol).toLowerCase(),
498: oVal);
499: }
500: } else
501: oLoca = null;
502:
503: if (DebugFile.trace) {
504: DebugFile.decIdent();
505: DebugFile.writeln("End Product.getFirstLocation()");
506: }
507:
508: return oLoca;
509: } // getFirstLocation
510:
511: // ----------------------------------------------------------
512:
513: /**
514: * <p>Store Product</p>
515: * If no gu_product is provided, this method will automatically add a new row to k_prod_attr table for th enewly created product
516: * If gu_product is null then a new GUID is automatically assigned.<br>
517: * If dt_modified is null then it is assigned to current system date.<br>
518: * If dt_uploaded is null then it is assigned to current system date.<br>
519: * If is_compound is null then it is assigned to 0.<br>
520: * If id_status is null then it is assigned to 1.<br>
521: * @param oConn Database Connection
522: * @throws SQLException
523: */
524: public boolean store(JDCConnection oConn) throws SQLException {
525: java.sql.Timestamp dtSQL = new java.sql.Timestamp(
526: new java.util.Date().getTime());
527:
528: if (DebugFile.trace) {
529: DebugFile.writeln("Begin Product.store()");
530: DebugFile.incIdent();
531: }
532:
533: boolean bNew = !AllVals.containsKey(DB.gu_product);
534:
535: if (bNew)
536:
537: put(DB.gu_product, Gadgets.generateUUID());
538:
539: else if (!AllVals.containsKey(DB.dt_modified) && exists(oConn))
540:
541: put(DB.dt_modified, dtSQL);
542:
543: if (!AllVals.containsKey(DB.dt_uploaded))
544: put(DB.dt_uploaded, dtSQL);
545:
546: if (!AllVals.containsKey(DB.is_compound))
547: put(DB.is_compound, new Short((short) 0));
548:
549: if (!AllVals.containsKey(DB.id_status))
550: put(DB.id_status, new Short((short) 1));
551:
552: boolean bRetVal = super .store(oConn);
553:
554: if (bNew) {
555: DebugFile.writeln("new ProductAttribute("
556: + getStringNull(DB.gu_product, "null") + ")");
557: new ProductAttribute(getString(DB.gu_product)).store(oConn);
558: }
559:
560: if (DebugFile.trace) {
561: DebugFile.decIdent();
562: DebugFile.writeln("End Product.store() : "
563: + getString(DB.gu_product));
564: }
565:
566: return bRetVal;
567: } // store
568:
569: // ----------------------------------------------------------
570:
571: /**
572: * <p>Delete Product</p>
573: * Images and Productlocations are deleted first, including disk files.
574: * Then k_sp_del_product storedprocedure is called.
575: * @param oConn Database Connection
576: * @throws SQLException
577: */
578: public boolean delete(JDCConnection oConn) throws SQLException {
579: CallableStatement oStmt;
580:
581: if (DebugFile.trace) {
582: DebugFile.writeln("Begin Product.delete(Connection)");
583: DebugFile.incIdent();
584: }
585:
586: try {
587: eraseImages(oConn);
588: } catch (SQLException sqle) {
589: if (DebugFile.trace)
590: DebugFile.writeln("SQLException: " + sqle.getMessage());
591: }
592:
593: // Begin SQLException
594: eraseLocations(oConn);
595:
596: if (DebugFile.trace)
597: DebugFile
598: .writeln("Connection.prepareCall({call k_sp_del_product ('"
599: + getStringNull(DB.gu_product, "null")
600: + "')}");
601:
602: oStmt = oConn.prepareCall("{call k_sp_del_product ('"
603: + getString(DB.gu_product) + "')}");
604: oStmt.execute();
605: oStmt.close();
606: // End SQLException
607:
608: if (DebugFile.trace) {
609: DebugFile.decIdent();
610: DebugFile.writeln("End Product.delete() : "
611: + getString(DB.gu_product));
612: }
613:
614: return true;
615: } // delete
616:
617: // ----------------------------------------------------------
618:
619: /**
620: * <p>Delete Associated images</p>
621: * @param oConn Database Connection
622: * @throws SQLException
623: * @see com.knowgate.hipergate.Image#delete(JDCConnection)
624: */
625: private void eraseImages(JDCConnection oConn) throws SQLException {
626: if (DebugFile.trace) {
627: DebugFile.writeln("Begin Product.eraseImages(Connection)");
628: DebugFile.incIdent();
629: }
630:
631: DBSubset oImages = new DBSubset(DB.k_images, DB.gu_image + ","
632: + DB.path_image, DB.gu_product + "=?", 10);
633: int iImgCount = oImages.load(oConn,
634: new Object[] { getString(DB.gu_product) });
635: Image oImg = new Image();
636:
637: for (int i = 0; i < iImgCount; i++) {
638: oImg.replace(DB.gu_image, oImages.get(0, i));
639: oImg.replace(DB.path_image, oImages.get(1, i));
640: oImg.delete(oConn);
641: } // next
642:
643: oImg = null;
644: oImages = null;
645:
646: if (DebugFile.trace) {
647: DebugFile.decIdent();
648: DebugFile.writeln("End Product.eraseImages()");
649: }
650: } // eraseImages
651:
652: // ----------------------------------------------------------
653:
654: /**
655: * <p>Delete ProductLocations including disk files.</p>
656: * @param oConn Database Connection
657: * @throws SQLException
658: * @see com.knowgate.hipergate.ProductLocation#delete(JDCConnection)
659: */
660: public int eraseLocations(JDCConnection oConn) throws SQLException {
661:
662: if (DebugFile.trace) {
663: DebugFile
664: .writeln("Begin Product.eraseLocations(Connection)");
665: DebugFile.incIdent();
666: }
667:
668: DBSubset oLocs = getLocations(oConn);
669: int iLocs = oLocs.getRowCount();
670: ProductLocation oLoca = new ProductLocation();
671:
672: for (int f = 0; f < iLocs; f++) {
673: oLoca = new ProductLocation(oConn, oLocs.getString(0, f));
674: oLoca.delete(oConn);
675: } // next (f)
676:
677: if (DebugFile.trace) {
678: DebugFile.decIdent();
679: DebugFile.writeln("End Product.eraseLocations() : "
680: + oLocs.getRowCount());
681: }
682:
683: return oLocs.getRowCount();
684: } // eraseLocations()
685:
686: // ----------------------------------------------------------
687:
688: /**
689: * <p>Add Product to Category</p>
690: * Insert Product GUID into table k_x_cat_objs.<br>
691: * If Product already belongs to Category no error is raised.
692: * @param oConn Database Connection
693: * @param idCategory GUID of Category
694: * @param iOdPosition Position of Product into Category.
695: * Positions of products inside categories do not need to be unique,
696: * this value is only used for ordering product when displaying them.
697: * @throws SQLException
698: */
699: public int addToCategory(JDCConnection oConn, String idCategory,
700: int iOdPosition) throws SQLException {
701: boolean bAlreadyExists;
702: int iRetVal;
703: PreparedStatement oStmt;
704: ResultSet oRSet;
705:
706: oStmt = oConn.prepareStatement("SELECT NULL FROM "
707: + DB.k_x_cat_objs + " WHERE " + DB.gu_category
708: + "=? AND " + DB.gu_object + "=?");
709: oStmt.setString(1, idCategory);
710: oStmt.setString(2, getString(DB.gu_product));
711: oRSet = oStmt.executeQuery();
712: bAlreadyExists = oRSet.next();
713: oRSet.close();
714: oStmt.close();
715:
716: if (!bAlreadyExists) {
717: oStmt = oConn.prepareStatement("INSERT INTO "
718: + DB.k_x_cat_objs + " (" + DB.gu_category + ","
719: + DB.gu_object + "," + DB.id_class + ","
720: + DB.od_position + ") VALUES (?,?,?,?)");
721: oStmt.setString(1, idCategory);
722: oStmt.setString(2, getString(DB.gu_product));
723: oStmt.setInt(3, ClassId);
724: oStmt.setInt(4, iOdPosition);
725: iRetVal = oStmt.executeUpdate();
726: oStmt.close();
727: } else
728: iRetVal = 0;
729:
730: return iRetVal;
731: }
732:
733: // ----------------------------------------------------------
734:
735: /**
736: * Remove product from Category
737: * @param oConn Database Conenction
738: * @param idCategory Category GUID
739: * @throws SQLException
740: */
741: public int removeFromCategory(JDCConnection oConn, String idCategory)
742: throws SQLException {
743: int iDeleted = 0;
744: PreparedStatement oStmt;
745:
746: oStmt = oConn.prepareStatement("DELETE FROM " + DB.k_x_cat_objs
747: + " WHERE " + DB.gu_category + "=? AND " + DB.gu_object
748: + "=? AND " + DB.id_class + "="
749: + String.valueOf(ClassId));
750: oStmt.setString(1, idCategory);
751: oStmt.setString(2, getString(DB.gu_product));
752: iDeleted = oStmt.executeUpdate();
753: oStmt.close();
754:
755: return iDeleted;
756: }
757:
758: // ----------------------------------------------------------
759:
760: /**
761: * get position of Product inside a Category.
762: * @param oConn Database Conenction
763: * @param sCategoryId Category GUID
764: * @return Product Position or <b>null</b> if this Product was not found inside
765: * specified Category.
766: * @throws SQLException
767: */
768: public Integer getPosition(JDCConnection oConn, String sCategoryId)
769: throws SQLException {
770: Statement oStmt;
771: ResultSet oRSet;
772: CallableStatement oCall;
773: Object oPos;
774: Integer iPos;
775:
776: if (DebugFile.trace) {
777: DebugFile
778: .writeln("Begin Product.getPosition([Connection], "
779: + sCategoryId + ")");
780: DebugFile.incIdent();
781: }
782:
783: if (oConn.getDataBaseProduct() == JDCConnection.DBMS_POSTGRESQL) {
784: oStmt = oConn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
785: ResultSet.CONCUR_READ_ONLY);
786:
787: if (DebugFile.trace)
788: DebugFile
789: .writeln("Statement.executeQuery(SELECT k_sp_cat_obj_position('"
790: + getStringNull(DB.gu_product, "null")
791: + "','" + sCategoryId + "'))");
792:
793: oRSet = oStmt
794: .executeQuery("SELECT k_sp_cat_obj_position ('"
795: + getString(DB.gu_product) + "','"
796: + sCategoryId + "')");
797: oRSet.next();
798: oPos = new Integer(oRSet.getInt(1));
799: oRSet.close();
800: oStmt.close();
801: } else {
802: // Patched for MySQL at v 3.0.13
803: oCall = oConn
804: .prepareCall("{ call k_sp_cat_obj_position(?,?,?)}");
805: oCall.setString(1, getString(DB.gu_product));
806: oCall.setString(2, sCategoryId);
807: oCall.registerOutParameter(3, Types.INTEGER);
808: oCall.execute();
809: oPos = oCall.getObject(3);
810: oCall.close();
811: oCall = null;
812: }
813:
814: if (null == oPos)
815: iPos = null;
816: else
817: iPos = new Integer(oPos.toString());
818:
819: if (DebugFile.trace) {
820: DebugFile.decIdent();
821: DebugFile.writeln("End Product.getPosition()");
822: }
823:
824: return iPos;
825: } // getPosition
826:
827: // ----------------------------------------------------------
828:
829: /**
830: * <p>Get Sale Price for a given date.</p>
831: * This method takes into account pr_sale, pr_list, dt_start and dt_end fields.<br>
832: * There are two possible prices, <i>list price</i> or <i>sale</i> (bargain) <i>price</i>.<br>
833: * Sale price is returned if it exists at database and given date is between dt_start and dt_end.<br>
834: * Otherwise List price is returned.<br>
835: * This method does not take into account any information from k_prod_fares table.<br>
836: * Product Price is taken from k_products table following these rules:<br>
837: * <ul>
838: * <li>if dt_start AND dt_end are NULL then pr_list price is assigned.
839: * <li>if dt_start is NULL AND dt_end is NOT NULL AND pr_sale is NULL then pr_list is assigned.
840: * <li>if dt_start is NULL AND dt_end is NOT NULL AND pr_sale is NOT NULL AND dtForDate is less than or equal to dt_end then pr_sale is assigned.
841: * <li>if dt_start is NULL AND dt_end is NOT NULL AND pr_sale is NOT NULL AND dtForDate is greater than dt_end then pr_list is assigned.
842: * <li>if dt_start is NOT NULL AND dt_end is NULL AND pr_sale is NULL then pr_list is assigned.
843: * <li>if dt_start is NOT NULL AND dt_end is NULL AND pr_sale is NOT NULL AND dtForDate is greater than or equal to dt_start then pr_sale is assigned.
844: * <li>if dt_start is NOT NULL AND dt_end is NULL AND pr_sale is NOT NULL AND dtForDate is less than dt_start then pr_list is assigned.
845: * <li>if dt_start AND dt_end are NOT NULL AND pr_sale IS NULL then pr_list price is assigned.
846: * <li>if dt_start AND dt_end are NOT NULL AND pr_sale IS NOT NULL AND dtForDate is greater than or equal to dt_start AND dtForDate is less than or equal to dt_end then pr_sale is assigned.
847: * <li>if dt_start AND dt_end are NOT NULL AND pr_sale IS NOT NULL AND dtForDate is less than dt_start OR dtForDate is greater than dt_end then pr_sale is assigned.
848: * </ul>
849: * @param dtForDate Date for testing List or Sale Price.
850: * @return Price for selling the Product at a given Date.
851: */
852: public BigDecimal salePrice(java.util.Date dtForDate) {
853: java.util.Date dtForDateStart;
854: java.util.Date dtForDateEnd;
855: BigDecimal dRetVal;
856:
857: if (DebugFile.trace) {
858: DebugFile.writeln("Begin Product.salePrice(" + dtForDate
859: + ")");
860: DebugFile.incIdent();
861: }
862:
863: if (isNull(DB.pr_list) && isNull(DB.pr_sale))
864: dRetVal = null;
865: else {
866: if ((isNull(DB.dt_start) && isNull(DB.dt_end))
867: || null == dtForDate) {
868: if (isNull(DB.pr_list))
869: dRetVal = null;
870: else
871: dRetVal = getDecimal(DB.pr_list);
872: } else {
873: dtForDateStart = new java.util.Date(dtForDate.getTime());
874: dtForDateStart.setHours(0);
875: dtForDateStart.setMinutes(1);
876: dtForDateStart.setSeconds(1);
877: dtForDateEnd = new java.util.Date(dtForDate.getTime());
878: dtForDateEnd.setHours(0);
879: dtForDateEnd.setMinutes(1);
880: dtForDateEnd.setSeconds(1);
881:
882: if (!isNull(DB.dt_start)) {
883: if (isNull(DB.dt_end))
884: if (dtForDateStart
885: .compareTo(getDate(DB.dt_start)) > 0)
886: dRetVal = getDecimal(DB.pr_sale);
887: else
888: dRetVal = getDecimal(DB.pr_list);
889: else if (dtForDateStart
890: .compareTo(getDate(DB.dt_start)) > 0
891: && dtForDateEnd
892: .compareTo(getDate(DB.dt_end)) < 0)
893: dRetVal = getDecimal(DB.pr_sale);
894: else
895: dRetVal = getDecimal(DB.pr_list);
896: } else {
897: if (dtForDateEnd.compareTo(getDate(DB.dt_end)) < 0)
898: dRetVal = getDecimal(DB.pr_list);
899: else
900: dRetVal = getDecimal(DB.pr_sale);
901: }
902: } // fi ((isNull(DB.dt_start) && isNull(DB.dt_end)) || null==dtForDate)
903: } // fi (isNull(DB.pr_list) && isNull(DB.pr_sale))
904:
905: if (DebugFile.trace) {
906: DebugFile.decIdent();
907: DebugFile.writeln("End Product.salePrice() : " + dRetVal);
908: }
909:
910: return dRetVal;
911: } // salePrice
912:
913: // ----------------------------------------------------------
914:
915: private DBSubset oLocations;
916:
917: public static final short ClassId = 15;
918: } // Product
|