001: /*
002: * Copyright 2005-2007 The Kuali Foundation.
003: *
004: *
005: * Licensed under the Educational Community License, Version 1.0 (the "License");
006: * you may not use this file except in compliance with the License.
007: * You may obtain a copy of the License at
008: *
009: * http://www.opensource.org/licenses/ecl1.php
010: *
011: * Unless required by applicable law or agreed to in writing, software
012: * distributed under the License is distributed on an "AS IS" BASIS,
013: * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
014: * See the License for the specific language governing permissions and
015: * limitations under the License.
016: */
017: package edu.iu.uis.eden.stats.dao;
018:
019: import java.sql.Connection;
020: import java.sql.PreparedStatement;
021: import java.sql.ResultSet;
022: import java.sql.SQLException;
023: import java.sql.Timestamp;
024: import java.util.ArrayList;
025: import java.util.Calendar;
026: import java.util.Date;
027: import java.util.List;
028:
029: import org.apache.ojb.broker.PersistenceBroker;
030: import org.apache.ojb.broker.accesslayer.LookupException;
031: import org.springmodules.orm.ojb.support.PersistenceBrokerDaoSupport;
032:
033: import edu.iu.uis.eden.EdenConstants;
034: import edu.iu.uis.eden.stats.Stats;
035: import edu.iu.uis.eden.web.KeyValue;
036:
037: public class StatsDAOOjbImpl extends PersistenceBrokerDaoSupport
038: implements StatsDAO {
039:
040: private static final org.apache.log4j.Logger LOG = org.apache.log4j.Logger
041: .getLogger(StatsDAOOjbImpl.class);
042:
043: public static final String SQL_NUM_ACTION_PER_TIME = "select "
044: + "round(avg(count(to_char(actn_tkn_dt, ?)))) as avg from en_actn_tkn_t where actn_tkn_dt between ? and ? group by to_char(actn_tkn_dt, ?)";
045: public static final String SQL_NUM_ACTIVE_ITEMS = "select count(*) from en_actn_itm_t";
046: public static final String SQL_NUM_DOC_TYPES_REPORT = "select count(*) as num from en_doc_typ_t where doc_typ_cur_ind = 1";
047: public static final String SQL_DOCUMENTS_ROUTED = "select count(*) as count, en_doc_hdr_t.doc_rte_stat_cd from en_doc_hdr_t where en_doc_hdr_t.doc_crte_dt between ? and ? group by doc_rte_stat_cd";
048: public static final String SQL_NUM_USERS = "select count(distinct prsn_en_id) as prsn_count from en_usr_optn_t";
049: public static final String SQL_NUM_DOCS_INITIATED = "select count(*), en_doc_typ_t.doc_typ_nm from en_doc_hdr_t, en_doc_typ_t where en_doc_hdr_t.doc_crte_dt > ? and en_doc_hdr_t.doc_typ_id = en_doc_typ_t.doc_typ_id group by en_doc_typ_t.doc_typ_nm";
050:
051: public void ActionsTakenPerUnitOfTimeReport(Stats stats,
052: Date begDate, Date endDate, String unitOfTimeConst)
053: throws SQLException, LookupException {
054:
055: LOG.debug("ActionsTakenPerUnitOfTimeReport()");
056: PersistenceBroker broker = this .getPersistenceBroker(false);
057: Connection conn = broker.serviceConnectionManager()
058: .getConnection();
059: PreparedStatement ps = conn
060: .prepareStatement(StatsDAOOjbImpl.SQL_NUM_ACTION_PER_TIME);
061: ps.setString(1, unitOfTimeConst);
062: ps.setTimestamp(2, new Timestamp(begDate.getTime()));
063: ps.setTimestamp(3, new Timestamp(endDate.getTime()));
064: ps.setString(4, unitOfTimeConst);
065: ResultSet rs = ps.executeQuery();
066:
067: while (rs.next()) {
068: stats.setAvgActionsPerDoc(new Integer(rs.getInt("avg"))
069: .toString());
070: }
071:
072: closeDatabaseObjects(rs, ps, conn, broker);
073: }
074:
075: public void NumActiveItemsReport(Stats stats) throws SQLException,
076: LookupException {
077:
078: LOG.debug("NumActiveItemsReport()");
079: PersistenceBroker broker = this .getPersistenceBroker(false);
080: Connection conn = broker.serviceConnectionManager()
081: .getConnection();
082: PreparedStatement ps = conn
083: .prepareStatement(StatsDAOOjbImpl.SQL_NUM_ACTIVE_ITEMS);
084: ResultSet rs = ps.executeQuery();
085:
086: while (rs.next()) {
087: stats.setNumActionItems(new Integer(rs.getInt(1))
088: .toString());
089: }
090:
091: closeDatabaseObjects(rs, ps, conn, broker);
092: }
093:
094: public void NumberOfDocTypesReport(Stats stats)
095: throws SQLException, LookupException {
096:
097: LOG.debug("NumberOfDocTypesReport()");
098: PersistenceBroker broker = this .getPersistenceBroker(false);
099: Connection conn = broker.serviceConnectionManager()
100: .getConnection();
101: PreparedStatement ps = conn
102: .prepareStatement(StatsDAOOjbImpl.SQL_NUM_DOC_TYPES_REPORT);
103: ResultSet rs = ps.executeQuery();
104:
105: while (rs.next()) {
106: stats.setNumDocTypes(new Integer(rs.getInt(1)).toString());
107: }
108:
109: closeDatabaseObjects(rs, ps, conn, broker);
110: }
111:
112: public void DocumentsRoutedReport(Stats stats, Date begDate,
113: Date endDate) throws SQLException, LookupException {
114:
115: LOG.debug("DocumentsRoutedReport()");
116: PersistenceBroker broker = this .getPersistenceBroker(false);
117: Connection conn = broker.serviceConnectionManager()
118: .getConnection();
119: PreparedStatement ps = conn
120: .prepareStatement(StatsDAOOjbImpl.SQL_DOCUMENTS_ROUTED);
121: ps.setTimestamp(1, new Timestamp(begDate.getTime()));
122: ps.setTimestamp(2, new Timestamp(endDate.getTime()));
123: ResultSet rs = ps.executeQuery();
124:
125: while (rs.next()) {
126:
127: String actionType = rs.getString(2);
128: String number = new Integer(rs.getInt(1)).toString();
129: if (actionType
130: .equals(EdenConstants.ROUTE_HEADER_APPROVED_CD)) {
131: stats.setApprovedNumber(number);
132: } else if (actionType
133: .equals(EdenConstants.ROUTE_HEADER_CANCEL_CD)) {
134: stats.setCanceledNumber(number);
135: } else if (actionType
136: .equals(EdenConstants.ROUTE_HEADER_DISAPPROVED_CD)) {
137: stats.setDisapprovedNumber(number);
138: } else if (actionType
139: .equals(EdenConstants.ROUTE_HEADER_ENROUTE_CD)) {
140: stats.setEnrouteNumber(number);
141: } else if (actionType
142: .equals(EdenConstants.ROUTE_HEADER_EXCEPTION_CD)) {
143: stats.setExceptionNumber(number);
144: } else if (actionType
145: .equals(EdenConstants.ROUTE_HEADER_FINAL_CD)) {
146: stats.setFinalNumber(number);
147: } else if (actionType
148: .equals(EdenConstants.ROUTE_HEADER_INITIATED_CD)) {
149: stats.setInitiatedNumber(number);
150: } else if (actionType
151: .equals(EdenConstants.ROUTE_HEADER_PROCESSED_CD)) {
152: stats.setProcessedNumber(number);
153: } else if (actionType
154: .equals(EdenConstants.ROUTE_HEADER_SAVED_CD)) {
155: stats.setSavedNumber(number);
156: }
157: }
158:
159: closeDatabaseObjects(rs, ps, conn, broker);
160: }
161:
162: public void NumUsersReport(Stats stats) throws SQLException,
163: LookupException {
164:
165: LOG.debug("NumUsersReport()");
166: PersistenceBroker broker = this .getPersistenceBroker(false);
167: Connection conn = broker.serviceConnectionManager()
168: .getConnection();
169: PreparedStatement ps = conn
170: .prepareStatement(StatsDAOOjbImpl.SQL_NUM_USERS);
171: ResultSet rs = ps.executeQuery();
172:
173: while (rs.next()) {
174: stats.setNumUsers(new Integer(rs.getInt("prsn_count"))
175: .toString());
176: }
177:
178: closeDatabaseObjects(rs, ps, conn, broker);
179: }
180:
181: public void NumInitiatedDocsByDocTypeReport(Stats stats)
182: throws SQLException, LookupException {
183:
184: LOG.debug("NumInitiatedDocsByDocType()");
185: PersistenceBroker broker = this .getPersistenceBroker(false);
186: Connection conn = broker.serviceConnectionManager()
187: .getConnection();
188: PreparedStatement ps = conn
189: .prepareStatement(StatsDAOOjbImpl.SQL_NUM_DOCS_INITIATED);
190: Calendar calendar = Calendar.getInstance();
191: calendar.add(Calendar.DAY_OF_YEAR, -29);
192: calendar.set(Calendar.HOUR_OF_DAY, 0);
193: calendar.set(Calendar.MINUTE, 0);
194: calendar.set(Calendar.SECOND, 0);
195: calendar.set(Calendar.MILLISECOND, 0);
196: ps.setTimestamp(1, new Timestamp(calendar.getTime().getTime()));
197: ResultSet rs = ps.executeQuery();
198:
199: List numDocs = new ArrayList();
200:
201: while (rs.next()) {
202: numDocs.add(new KeyValue(rs.getString(2), new Integer(rs
203: .getInt(1)).toString()));
204: }
205: stats.setNumInitiatedDocsByDocType(numDocs);
206:
207: closeDatabaseObjects(rs, ps, conn, broker);
208:
209: }
210:
211: private void closeDatabaseObjects(ResultSet rs,
212: PreparedStatement ps, Connection conn,
213: PersistenceBroker broker) {
214:
215: try {
216: rs.close();
217: } catch (SQLException ex) {
218: LOG.warn("Failed to close ResultSet.", ex);
219: }
220:
221: try {
222: ps.close();
223: } catch (SQLException ex) {
224: LOG.warn("Failed to close PreparedStatement.", ex);
225: }
226:
227: try {
228: conn.close();
229: } catch (SQLException ex) {
230: LOG.warn("Failed to close Connection.", ex);
231: }
232:
233: try {
234: broker.close();
235: } catch (Exception ex) {
236: LOG.warn("Failed to close broker.", ex);
237: }
238:
239: }
240:
241: }
|