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.Vector;
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 Approvals portlet
051: *
052: * @author Zahid Syed
053: */
054:
055: public class ApprovalQueryUtils implements OraclePortletConstants {
056:
057: private static Logger logger = OraclePortletLogger.getLogger();
058:
059: /**
060: * This method is used to obtain approval related records in the form of a Vector
061: **/
062:
063: public static Vector getPendingApprovalRecords(
064: HttpServletRequest httpReq) {
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: "getPendingApprovalRecords : Could not obtain connection :");
078: return null;
079: } else {
080: stmt = conn.createStatement();
081: rset = stmt
082: .executeQuery(PENDING_APPROVAL_HEADERS_QUERY);
083:
084: while (rset.next()) {
085: ApprovalHeaderBean ahb = new ApprovalHeaderBean();
086:
087: ahb.setSegmentOne(rset.getString(1));
088: ahb.setDescription(rset.getString(2));
089: ahb.setRequisitionType(rset.getString(3));
090: ahb.setCreationDate(rset.getString(4));
091: ahb.setCreator(rset.getString(5));
092:
093: records.addElement(ahb);
094:
095: }
096:
097: conn.close();
098: return records;
099: }
100:
101: } catch (Exception ex) {
102:
103: logMessage(Level.SEVERE,
104: "getPendingApprovalRecords : query execution and reading resultset :"
105: + ex);
106: return null;
107: }
108:
109: }
110:
111: /**
112: * This method is used to obtain line items for a requisition with pending approval
113: **/
114: public static Vector getPendingApprovalLines(ResultSet rset) {
115:
116: Vector records = new Vector();
117:
118: try {
119: while (rset.next()) {
120: ApprovalLinesBean alb = new ApprovalLinesBean();
121:
122: alb.setReqLineID(rset.getString(1));
123: alb.setItemDescription(rset.getString(2));
124: alb.setVendorName(rset.getString(3));
125: alb.setUnitPrice(rset.getString(4));
126: alb.setCurrency(rset.getString(5));
127: alb.setQuantity(rset.getString(6));
128: alb.setNeedByDate(rset.getString(7));
129:
130: records.addElement(alb);
131:
132: }
133: } catch (Exception ex) {
134:
135: logMessage(Level.SEVERE,
136: "getPendingApprovalLines : reading resultset : "
137: + ex);
138: }
139: return records;
140:
141: }
142:
143: /**
144: * This method retrieves the line items for a requisition based upon a key and dispatches the
145: * control to the relevant JSP, to render the results
146: **/
147: public static void appendApprovalLines(String queryValue,
148: RenderRequest request, RenderResponse response,
149: PortletSession session, HttpServletRequest httpReq)
150: throws PortletException, IOException {
151:
152: Vector linesRecords = new Vector();
153: PortletContext ctx = session.getPortletContext();
154: PortletRequestDispatcher reqDisp = null;
155:
156: Connection conn = null;
157: ResultSet rset = null;
158: Statement stmt = null;
159:
160: try {
161:
162: conn = (Connection) OracleConnectionUtils
163: .getOracleConnection(httpReq);
164: if (conn == null) {
165: logMessage(Level.SEVERE,
166: "appendApprovalLines : Could not obtain connection :");
167: // Dispatch the request to a JSP that displays error
168: reqDisp = ctx.getRequestDispatcher(NO_RECORDS_JSP);
169: reqDisp.include(request, response);
170: } else {
171: stmt = conn.createStatement();
172:
173: // Use the value "queryValue" in the query
174: rset = stmt.executeQuery(APPROVAL_LINES_QUERY
175: + APPROVAL_LINES_SUB_QUERY1 + queryValue
176: + APPROVAL_LINES_SUB_QUERY2);
177:
178: // Obtain the records in the form of a Vector of records by passing the result set
179: //to a utility method
180: linesRecords = (Vector) getPendingApprovalLines(rset);
181:
182: // Set the lines records as an attribute before dispatching to the JSP
183: request.setAttribute("ApprovalLines", linesRecords);
184:
185: conn.close();
186:
187: reqDisp = ctx.getRequestDispatcher(APPROVAL_LINES_JSP);
188: reqDisp.include(request, response);
189:
190: }
191:
192: } catch (Exception ex) {
193:
194: logMessage(Level.SEVERE,
195: "appendApprovalLines : Query execution and dispatch to JSP : "
196: + ex);
197:
198: // Dispatch the request to a JSP that displays error
199: reqDisp = ctx.getRequestDispatcher(NO_DETAILS_JSP);
200: reqDisp.include(request, response);
201: }
202: }
203:
204: /**
205: * This method logs the debug messages
206: **/
207: private static void logMessage(Object debugLevel, String msg) {
208:
209: logger.log((Level) debugLevel, msg);
210: }
211:
212: }
|