001 /**
002 * Copyright 2005-2013 The Kuali Foundation
003 *
004 * Licensed under the Educational Community License, Version 2.0 (the "License");
005 * you may not use this file except in compliance with the License.
006 * You may obtain a copy of the License at
007 *
008 * http://www.opensource.org/licenses/ecl2.php
009 *
010 * Unless required by applicable law or agreed to in writing, software
011 * distributed under the License is distributed on an "AS IS" BASIS,
012 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
013 * See the License for the specific language governing permissions and
014 * limitations under the License.
015 */
016 package org.kuali.rice.kew.stats.dao.impl;
017
018 import org.apache.ojb.broker.PersistenceBroker;
019 import org.apache.ojb.broker.accesslayer.LookupException;
020 import org.kuali.rice.core.api.util.ConcreteKeyValue;
021 import org.kuali.rice.core.api.util.KeyValue;
022 import org.kuali.rice.kew.stats.Stats;
023 import org.kuali.rice.kew.stats.dao.StatsDAO;
024 import org.kuali.rice.kew.api.KewApiConstants;
025 import org.springmodules.orm.ojb.support.PersistenceBrokerDaoSupport;
026
027 import java.sql.Connection;
028 import java.sql.PreparedStatement;
029 import java.sql.ResultSet;
030 import java.sql.SQLException;
031 import java.sql.Timestamp;
032 import java.util.ArrayList;
033 import java.util.Calendar;
034 import java.util.Date;
035 import java.util.List;
036
037
038 public class StatsDAOOjbImpl extends PersistenceBrokerDaoSupport implements StatsDAO {
039
040 private static final org.apache.log4j.Logger LOG = org.apache.log4j.Logger.getLogger(StatsDAOOjbImpl.class);
041
042 public static final String SQL_NUM_ACTIVE_ITEMS = "select count(*) from krew_actn_itm_t";
043 public static final String SQL_NUM_DOC_TYPES_REPORT = "select count(*) as num from krew_doc_typ_t where cur_ind = 1";
044 public static final String SQL_DOCUMENTS_ROUTED = "select count(*) as count, krew_doc_hdr_t.doc_hdr_stat_cd from krew_doc_hdr_t where krew_doc_hdr_t.crte_dt between ? and ? group by doc_hdr_stat_cd";
045 public static final String SQL_NUM_USERS = "select count(distinct prncpl_id) as prsn_count from KREW_USR_OPTN_T";
046 public static final String SQL_NUM_DOCS_INITIATED = "select count(*), krew_doc_typ_t.doc_typ_nm from krew_doc_hdr_t, krew_doc_typ_t where krew_doc_hdr_t.crte_dt > ? and krew_doc_hdr_t.doc_typ_id = krew_doc_typ_t.doc_typ_id group by krew_doc_typ_t.doc_typ_nm";
047
048 @Override
049 public void NumActiveItemsReport(Stats stats) throws SQLException, LookupException {
050
051 LOG.debug("NumActiveItemsReport()");
052 PersistenceBroker broker = this.getPersistenceBroker(false);
053 Connection conn = broker.serviceConnectionManager().getConnection();
054 PreparedStatement ps = conn.prepareStatement(StatsDAOOjbImpl.SQL_NUM_ACTIVE_ITEMS);
055 ResultSet rs = ps.executeQuery();
056
057 while (rs.next()) {
058 stats.setNumActionItems(new Integer(rs.getInt(1)).toString());
059 }
060
061 closeDatabaseObjects(rs, ps, conn/*, broker*/);
062 }
063
064 @Override
065 public void NumberOfDocTypesReport(Stats stats) throws SQLException, LookupException {
066
067 LOG.debug("NumberOfDocTypesReport()");
068 PersistenceBroker broker = this.getPersistenceBroker(false);
069 Connection conn = broker.serviceConnectionManager().getConnection();
070 PreparedStatement ps = conn.prepareStatement(StatsDAOOjbImpl.SQL_NUM_DOC_TYPES_REPORT);
071 ResultSet rs = ps.executeQuery();
072
073 while (rs.next()) {
074 stats.setNumDocTypes(new Integer(rs.getInt(1)).toString());
075 }
076
077 closeDatabaseObjects(rs, ps, conn/*, broker*/);
078 }
079
080 @Override
081 public void DocumentsRoutedReport(Stats stats, Date begDate, Date endDate) throws SQLException, LookupException {
082
083 LOG.debug("DocumentsRoutedReport()");
084 PersistenceBroker broker = this.getPersistenceBroker(false);
085 Connection conn = broker.serviceConnectionManager().getConnection();
086 PreparedStatement ps = conn.prepareStatement(StatsDAOOjbImpl.SQL_DOCUMENTS_ROUTED);
087 ps.setTimestamp(1, new Timestamp(begDate.getTime()));
088 ps.setTimestamp(2, new Timestamp(endDate.getTime()));
089 ResultSet rs = ps.executeQuery();
090
091 while (rs.next()) {
092
093 String actionType = rs.getString(2);
094 String number = new Integer(rs.getInt(1)).toString();
095 if (actionType.equals(KewApiConstants.ROUTE_HEADER_CANCEL_CD)) {
096 stats.setCanceledNumber(number);
097 } else if (actionType.equals(KewApiConstants.ROUTE_HEADER_DISAPPROVED_CD)) {
098 stats.setDisapprovedNumber(number);
099 } else if (actionType.equals(KewApiConstants.ROUTE_HEADER_ENROUTE_CD)) {
100 stats.setEnrouteNumber(number);
101 } else if (actionType.equals(KewApiConstants.ROUTE_HEADER_EXCEPTION_CD)) {
102 stats.setExceptionNumber(number);
103 } else if (actionType.equals(KewApiConstants.ROUTE_HEADER_FINAL_CD)) {
104 stats.setFinalNumber(number);
105 } else if (actionType.equals(KewApiConstants.ROUTE_HEADER_INITIATED_CD)) {
106 stats.setInitiatedNumber(number);
107 } else if (actionType.equals(KewApiConstants.ROUTE_HEADER_PROCESSED_CD)) {
108 stats.setProcessedNumber(number);
109 } else if (actionType.equals(KewApiConstants.ROUTE_HEADER_SAVED_CD)) {
110 stats.setSavedNumber(number);
111 }
112 }
113
114 closeDatabaseObjects(rs, ps, conn/*, broker*/);
115 }
116
117 @Override
118 public void NumUsersReport(Stats stats) throws SQLException, LookupException {
119
120 LOG.debug("NumUsersReport()");
121 PersistenceBroker broker = this.getPersistenceBroker(false);
122 Connection conn = broker.serviceConnectionManager().getConnection();
123 PreparedStatement ps = conn.prepareStatement(StatsDAOOjbImpl.SQL_NUM_USERS);
124 ResultSet rs = ps.executeQuery();
125
126 while (rs.next()) {
127 stats.setNumUsers(new Integer(rs.getInt("prsn_count")).toString());
128 }
129
130 closeDatabaseObjects(rs, ps, conn/*, broker*/);
131 }
132
133 @Override
134 public void NumInitiatedDocsByDocTypeReport(Stats stats) throws SQLException, LookupException {
135
136 LOG.debug("NumInitiatedDocsByDocType()");
137 PersistenceBroker broker = this.getPersistenceBroker(false);
138 Connection conn = broker.serviceConnectionManager().getConnection();
139 PreparedStatement ps = conn.prepareStatement(StatsDAOOjbImpl.SQL_NUM_DOCS_INITIATED);
140 Calendar calendar = Calendar.getInstance();
141 calendar.add(Calendar.DAY_OF_YEAR, -29);
142 calendar.set(Calendar.HOUR_OF_DAY, 0);
143 calendar.set(Calendar.MINUTE, 0);
144 calendar.set(Calendar.SECOND, 0);
145 calendar.set(Calendar.MILLISECOND, 0);
146 ps.setTimestamp(1, new Timestamp(calendar.getTime().getTime()));
147 ResultSet rs = ps.executeQuery();
148
149 List<KeyValue> numDocs = new ArrayList<KeyValue>();
150
151 while (rs.next()) {
152 numDocs.add(new ConcreteKeyValue(rs.getString(2), new Integer(rs.getInt(1)).toString()));
153 }
154 stats.setNumInitiatedDocsByDocType(numDocs);
155
156 closeDatabaseObjects(rs, ps, conn/*, broker*/);
157
158 }
159
160 private void closeDatabaseObjects(ResultSet rs, PreparedStatement ps, Connection conn/*, PersistenceBroker broker*/) {
161
162 try {
163 rs.close();
164 } catch (SQLException ex) {
165 LOG.warn("Failed to close ResultSet.", ex);
166 }
167
168 try {
169 ps.close();
170 } catch (SQLException ex) {
171 LOG.warn("Failed to close PreparedStatement.", ex);
172 }
173
174 try {
175 conn.close();
176 } catch (SQLException ex) {
177 LOG.warn("Failed to close Connection.", ex);
178 }
179 /*
180 try {
181 broker.close();
182 } catch (Exception ex) {
183 LOG.warn("Failed to close broker.", ex);
184 }
185 */
186 }
187
188 }