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 Requisition Info portlet
051: *
052: * @author Zahid Syed
053: */
054:
055: public class RequisitionQueryUtils implements OraclePortletConstants {
056:
057: private static Logger logger = OraclePortletLogger.getLogger();
058:
059: /**
060: * This method is used to obtain requisition summary records in the form of a Vector
061: **/
062:
063: public static Vector getRequisitionInfoRecords(
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: "getRequisitionInfoRecords : Could not obtain connection :");
078: return null;
079: } else {
080: stmt = conn.createStatement();
081: rset = stmt.executeQuery(REQUISITION_INFO_QUERY
082: + numberOfRecords);
083:
084: while (rset.next()) {
085: RequisitionInfoBean rib = new RequisitionInfoBean();
086:
087: rib.setReqHeaderID(rset.getString(1));
088: rib.setSegmentOne(rset.getString(2));
089: rib.setDescription(rset.getString(3));
090: rib.setRequisitionType(rset.getString(4));
091: rib.setCreationDate(rset.getString(5));
092: rib.setCreator(rset.getString(6));
093: rib.setAuthStatus(rset.getString(7));
094:
095: records.addElement(rib);
096:
097: }
098:
099: conn.close();
100: return records;
101: }
102: } catch (Exception ex) {
103: logMessage(Level.SEVERE,
104: "getRequisitionInfoRecords : query execution and reading resultset :"
105: + ex);
106: return null;
107: }
108:
109: }
110:
111: /**
112: * This method is used to obtain details related to a requisition, from the result set
113: **/
114:
115: public static Vector getRequisitionDetails(ResultSet rset) {
116:
117: Vector records = new Vector();
118:
119: try {
120: while (rset.next()) {
121: RequisitionDetailsBean rdb = new RequisitionDetailsBean();
122:
123: rdb.setReqLineID(rset.getString(1));
124: rdb.setItemDescription(rset.getString(2));
125: rdb.setVendorName(rset.getString(3));
126: rdb.setUnitPrice(rset.getString(4));
127: rdb.setCurrency(rset.getString(5));
128: rdb.setQuantity(rset.getString(6));
129: rdb.setNeedByDate(rset.getString(7));
130: rdb.setLocation(rset.getString(8));
131:
132: records.addElement(rdb);
133:
134: }
135: } catch (Exception ex) {
136:
137: logMessage(Level.SEVERE,
138: "getRequisitionDetails : reading resultset :" + ex);
139: }
140:
141: return records;
142:
143: }
144:
145: /**
146: * This method retrieves the line items for a requisition based upon a key and dispatches the
147: * control to the relevant JSP, to render the results
148: **/
149: public static void appendRequisitionDetails(String queryValue,
150: RenderRequest request, RenderResponse response,
151: PortletSession session, HttpServletRequest httpReq)
152: throws PortletException, IOException {
153:
154: Vector linesRecords = new Vector();
155: PortletContext ctx = session.getPortletContext();
156: PortletRequestDispatcher reqDisp = null;
157:
158: Connection conn = null;
159: ResultSet rset = null;
160: Statement stmt = null;
161:
162: try {
163:
164: conn = (Connection) OracleConnectionUtils
165: .getOracleConnection(httpReq);
166:
167: if (conn == null) {
168: logMessage(Level.SEVERE,
169: "appendRequisitionDetails : Could not obtain connection :");
170: // Dispatch the request to a JSP that displays error
171: reqDisp = ctx.getRequestDispatcher(NO_RECORDS_JSP);
172: reqDisp.include(request, response);
173: } else {
174: stmt = conn.createStatement();
175:
176: // Use the value "queryValue" in the query
177: rset = stmt.executeQuery(REQUISITION_DETAILS_QUERY
178: + queryValue);
179:
180: // Obtain the records in the form of a Vector of records by passing the result set
181: //to a utility method
182: linesRecords = (Vector) getRequisitionDetails(rset);
183:
184: // Set the line records as an attribute before dispatching to the JSP
185: request
186: .setAttribute("RequisitionDetails",
187: linesRecords);
188:
189: // Close the connection to Oracle server
190: conn.close();
191:
192: reqDisp = ctx
193: .getRequestDispatcher(REQUISITION_DETAILS_JSP);
194: reqDisp.include(request, response);
195:
196: }
197:
198: } catch (Exception ex) {
199:
200: logMessage(Level.SEVERE,
201: "appendRequisitionDetails : Query execution and dispatch to JSP :"
202: + ex);
203:
204: // Dispatch the request to a JSP that displays error
205: reqDisp = ctx.getRequestDispatcher(NO_DETAILS_JSP);
206: reqDisp.include(request, response);
207: }
208: }
209:
210: /**
211: * This method returns the number of records to display, by reading a properties file
212: **/
213: public static String getNumberOfRecordsToDisplay() {
214:
215: int numberOfRecords = 0;
216: int defaultNumberOfRecords = 10;
217: ResourceBundle rbundle = null;
218:
219: try {
220: rbundle = ResourceBundle.getBundle("oracleconfig");
221: } catch (MissingResourceException excp) {
222: logMessage(Level.WARNING,
223: "getNumberOfRecordsToDisplay : File oracleconfig.properties NOT found : "
224: + excp);
225: return (Integer.toString(defaultNumberOfRecords));
226: }
227:
228: String numberOfRecordsStr = rbundle
229: .getString(NUMBER_OF_REQUISITION_RECORDS);
230: try {
231: numberOfRecords = Integer.parseInt(numberOfRecordsStr);
232: } catch (Exception ex) {
233: logMessage(Level.INFO,
234: "getNumberOfRecordsToDisplay : Could not parse the property value : "
235: + ex);
236: return (Integer.toString(defaultNumberOfRecords));
237: }
238:
239: if (numberOfRecords < 1) {
240: numberOfRecords = defaultNumberOfRecords;
241: }
242: return (Integer.toString(numberOfRecords));
243: }
244:
245: /**
246: * This method logs the debug messages
247: **/
248: private static void logMessage(Object debugLevel, String msg) {
249:
250: logger.log((Level) debugLevel, msg);
251: }
252:
253: }
|