001: /*
002: * Copyright 2005 Sun Microsystems, Inc. All rights reserved.
003: *
004: * Redistribution and use in source and binary forms, with or without
005: * modification, are permitted provided that the following conditions
006: * are met:
007: *
008: * - Redistributions of source code must retain the above copyright
009: * notice, this list of conditions and the following disclaimer.
010: *
011: * - Redistribution in binary form must reproduce the above copyright
012: * notice, this list of conditions and the following disclaimer in
013: * the documentation and/or other materials provided with the
014: * distribution.
015: *
016: * Neither the name of Sun Microsystems, Inc. or the names of
017: * contributors may be used to endorse or promote products derived
018: * from this software without specific prior written permission.
019: *
020: * This software is provided "AS IS," without a warranty of any
021: * kind. ALL EXPRESS OR IMPLIED CONDITIONS, REPRESENTATIONS AND
022: * WARRANTIES, INCLUDING ANY IMPLIED WARRANTY OF MERCHANTABILITY,
023: * FITNESS FOR A PARTICULAR PURPOSE OR NON-INFRINGEMENT, ARE HEREBY
024: * EXCLUDED. SUN AND ITS LICENSORS SHALL NOT BE LIABLE FOR ANY DAMAGES
025: * SUFFERED BY LICENSEE AS A RESULT OF USING, MODIFYING OR
026: * DISTRIBUTING THE SOFTWARE OR ITS DERIVATIVES. IN NO EVENT WILL SUN
027: * OR ITS LICENSORS BE LIABLE FOR ANY LOST REVENUE, PROFIT OR DATA, OR
028: * FOR DIRECT, INDIRECT, SPECIAL, CONSEQUENTIAL, INCIDENTAL OR
029: * PUNITIVE DAMAGES, HOWEVER CAUSED AND REGARDLESS OF THE THEORY OF
030: * LIABILITY, ARISING OUT OF THE USE OF OR INABILITY TO USE SOFTWARE,
031: * EVEN IF SUN HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES.
032: *
033: * You acknowledge that Software is not designed, licensed or intended
034: * for use in the design, construction, operation or maintenance of
035: * any nuclear facility.
036: */
037:
038: package com.sun.portal.oracleportlet.util;
039:
040: import java.util.*;
041: import java.io.IOException;
042: import java.io.Writer;
043: import java.sql.*;
044: import java.util.logging.*;
045:
046: import javax.portlet.*;
047: import javax.servlet.http.HttpServletRequest;
048:
049: /**
050: * This is a utility class that contains methods used by Oracle Quotations portlet
051: *
052: * @author Zahid Syed
053: */
054:
055: public class QuotationQueryUtils implements OraclePortletConstants {
056:
057: private static Logger logger = OraclePortletLogger.getLogger();
058:
059: /**
060: * This method is used to obtain records pertaining to summary of quotations
061: **/
062:
063: public static Vector getQuotationSummaryRecords(
064: HttpServletRequest httpReq, String numberOfRecords) {
065:
066: Vector records = new Vector();
067: Connection conn = null;
068: Statement stmt = null;
069: ResultSet rset = null;
070:
071: try {
072: conn = (Connection) OracleConnectionUtils
073: .getOracleConnection(httpReq);
074:
075: if (conn == null) {
076: logMessage(Level.SEVERE,
077: "getQuotationSummaryRecords : Could not obtain connection :");
078: return null;
079: } else {
080: stmt = conn.createStatement();
081: rset = stmt.executeQuery(QUOTATION_SUMMARY_QUERY
082: + numberOfRecords);
083:
084: while (rset.next()) {
085: QuotationsBean qb = new QuotationsBean();
086:
087: qb.setSegmentOne(rset.getString(1));
088: qb.setDescription(rset.getString(2));
089: qb.setSupplier(rset.getString(3));
090: qb.setSupplierQuote(rset.getString(4));
091: qb.setBuyer(rset.getString(5));
092: qb.setType(rset.getString(6));
093: qb.setStatus(rset.getString(7));
094:
095: records.addElement(qb);
096:
097: }
098:
099: conn.close();
100: return records;
101: }
102:
103: } catch (Exception ex) {
104: logMessage(Level.SEVERE,
105: "getQuotationSummaryRecords : query execution and reading resultset :"
106: + ex);
107: return null;
108: }
109: }
110:
111: /**
112: * This method is used to obtain details corresponding to a quotation, from the result set
113: **/
114:
115: public static QuotationDetailsBean getQuotationDetails(
116: ResultSet rset) {
117:
118: QuotationDetailsBean qdb = new QuotationDetailsBean();
119:
120: try {
121: while (rset.next()) {
122: qdb.setSegmentOne(rset.getString(1));
123: qdb.setSupplier(rset.getString(2));
124: qdb.setSupplierQuote(rset.getString(3));
125: qdb.setSupplierSite(rset.getString(4));
126: qdb.setContact(rset.getString(5));
127: qdb.setBilltoLocation(rset.getString(6));
128: qdb.setStartDate(rset.getString(7));
129: qdb.setEndDate(rset.getString(8));
130:
131: }
132: } catch (Exception ex) {
133:
134: logMessage(Level.SEVERE,
135: "getQuotationDetails : reading resultset :" + ex);
136: }
137:
138: return qdb;
139:
140: }
141:
142: /**
143: * This method is used to obtain quotation lines for a given quotation number
144: **/
145: public static Vector getQuotationLines(ResultSet rset) {
146:
147: Vector records = new Vector();
148:
149: try {
150: while (rset.next()) {
151: QuotationLinesBean qlb = new QuotationLinesBean();
152:
153: qlb.setLineNo(rset.getString(1));
154: qlb.setItemDescription(rset.getString(2));
155: qlb.setItemCode(rset.getString(3));
156: qlb.setCategory(rset.getString(4));
157: qlb.setUOM(rset.getString(5));
158: qlb.setUnitPrice(rset.getString(6));
159:
160: records.addElement(qlb);
161:
162: }
163: } catch (Exception ex) {
164:
165: logMessage(Level.SEVERE,
166: "getQuotationLines : reading resultset :" + ex);
167: }
168:
169: return records;
170:
171: }
172:
173: /**
174: * This method retrieves the details of a quotation based upon SEGMENT1 (quotation no)
175: * and dispatches the control to the relevant JSP, to render the results
176: **/
177: public static void appendQuotationDetails(String queryValue,
178: RenderRequest request, RenderResponse response,
179: PortletSession session, HttpServletRequest httpReq)
180: throws PortletException, IOException {
181:
182: QuotationDetailsBean qdb = new QuotationDetailsBean();
183: PortletContext ctx = session.getPortletContext();
184: PortletRequestDispatcher reqDisp = null;
185:
186: Connection conn = null;
187: ResultSet rset = null;
188: Statement stmt = null;
189:
190: try {
191:
192: conn = (Connection) OracleConnectionUtils
193: .getOracleConnection(httpReq);
194: if (conn == null) {
195: logMessage(Level.SEVERE,
196: "appendQuotationDetails : Could not obtain connection :");
197: // Dispatch the request to a JSP that displays error
198: reqDisp = ctx.getRequestDispatcher(NO_RECORDS_JSP);
199: reqDisp.include(request, response);
200: } else {
201: stmt = conn.createStatement();
202:
203: // Use the value "queryValue" in the query
204: rset = stmt.executeQuery(QUOTATION_DETAILS_QUERY
205: + queryValue + QUOTATION_DETAILS_QC1);
206:
207: // Obtain the details in the form of QuotationDetailsBean by passing the result set
208: //to a utility method
209: qdb = (QuotationDetailsBean) getQuotationDetails(rset);
210:
211: // Set the details record as an attribute before dispatching to the JSP
212: request.setAttribute("QuotationDetails", qdb);
213:
214: // Close the connection to Oracle server
215: conn.close();
216:
217: reqDisp = ctx
218: .getRequestDispatcher(QUOTATION_DETAILS_JSP);
219: reqDisp.include(request, response);
220:
221: }
222:
223: } catch (Exception ex) {
224:
225: logMessage(Level.SEVERE,
226: "appendQuotationDetails : Query execution and dispatch to JSP :"
227: + ex);
228:
229: // Dispatch the request to a JSP that displays error
230: reqDisp = ctx.getRequestDispatcher(NO_DETAILS_JSP);
231: reqDisp.include(request, response);
232: }
233: }
234:
235: /**
236: * This method retrieves the lines for a quotation based upon SEGMENT1 (quotation no)
237: * and dispatches the control to the relevant JSP, to render the result
238: **/
239: public static void appendQuotationLines(String queryValue,
240: RenderRequest request, RenderResponse response,
241: PortletSession session, HttpServletRequest httpReq)
242: throws PortletException, IOException {
243:
244: Vector linesRecords = new Vector();
245: PortletContext ctx = session.getPortletContext();
246: PortletRequestDispatcher reqDisp = null;
247:
248: Connection conn = null;
249: ResultSet rset = null;
250: Statement stmt = null;
251:
252: try {
253:
254: conn = (Connection) OracleConnectionUtils
255: .getOracleConnection(httpReq);
256: if (conn == null) {
257: logMessage(Level.SEVERE,
258: "appendQuotationLines : Could not obtain connection :");
259: // Dispatch the request to a JSP that displays error
260: reqDisp = ctx.getRequestDispatcher(NO_RECORDS_JSP);
261: reqDisp.include(request, response);
262: } else {
263: stmt = conn.createStatement();
264:
265: // Use the value "queryValue" in the query
266: rset = stmt.executeQuery(QUOTATION_LINES_QUERY
267: + queryValue + QUOTATION_LINES_QC1);
268:
269: // Obtain the records in the form of a vector by passing the result set
270: //to a utility method
271: linesRecords = (Vector) getQuotationLines(rset);
272:
273: // Set the records as an attribute before dispatching to the JSP
274: request.setAttribute("QuotationLines", linesRecords);
275:
276: // Close the connection to Oracle server
277: conn.close();
278:
279: reqDisp = ctx.getRequestDispatcher(QUOTATION_LINES_JSP);
280: reqDisp.include(request, response);
281:
282: }
283:
284: } catch (Exception ex) {
285:
286: logMessage(Level.SEVERE,
287: "appendQuotationLines : Query execution and dispatch to JSP :"
288: + ex);
289:
290: // Dispatch the request to a JSP that displays error
291: reqDisp = ctx.getRequestDispatcher(NO_DETAILS_JSP);
292: reqDisp.include(request, response);
293: }
294: }
295:
296: /**
297: * This method returns the number of records to display, by reading a properties file
298: **/
299: public static String getNumberOfRecordsToDisplay() {
300:
301: int numberOfRecords = 0;
302: int defaultNumberOfRecords = 10;
303: ResourceBundle rbundle = null;
304:
305: try {
306: rbundle = ResourceBundle.getBundle("oracleconfig");
307: } catch (MissingResourceException excp) {
308: logMessage(Level.WARNING,
309: "getNumberOfRecordsToDisplay : File oracleconfig.properties NOT found : "
310: + excp);
311: return (Integer.toString(defaultNumberOfRecords));
312: }
313:
314: String numberOfRecordsStr = rbundle
315: .getString(NUMBER_OF_QUOTATION_RECORDS);
316: try {
317: numberOfRecords = Integer.parseInt(numberOfRecordsStr);
318: } catch (Exception ex) {
319: logMessage(Level.INFO,
320: "getNumberOfRecordsToDisplay : Could not parse the property value : "
321: + ex);
322: return (Integer.toString(defaultNumberOfRecords));
323: }
324:
325: if (numberOfRecords < 1) {
326: numberOfRecords = defaultNumberOfRecords;
327: }
328: return (Integer.toString(numberOfRecords));
329: }
330:
331: /**
332: * This method logs the debug messages
333: **/
334: private static void logMessage(Object debugLevel, String msg) {
335:
336: logger.log((Level) debugLevel, msg);
337: }
338:
339: }
|