/*

  * Copyright 2005-2008 The Kuali Foundation

  *

  *

  * Licensed under the Educational Community License, Version 2.0 (the "License");

  * you may not use this file except in compliance with the License.

  * You may obtain a copy of the License at

  *

  * http://www.opensource.org/licenses/ecl2.php

  *

  * Unless required by applicable law or agreed to in writing, software

  * distributed under the License is distributed on an "AS IS" BASIS,

  * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.

  * See the License for the specific language governing permissions and

  * limitations under the License.

  */

 package org.kuali.rice.kew.stats.dao.impl;

 import java.sql.Connection;

 import java.sql.PreparedStatement;

 import java.sql.ResultSet;

 import java.sql.SQLException;

 import java.sql.Timestamp;

 import java.util.ArrayList;

 import java.util.Calendar;

 import java.util.Date;

 import java.util.List;

 import org.apache.ojb.broker.PersistenceBroker;

 import org.apache.ojb.broker.accesslayer.LookupException;

 import org.kuali.rice.kew.stats.Stats;

 import org.kuali.rice.kew.stats.dao.StatsDAO;

 import org.kuali.rice.kew.util.KEWConstants;

 import org.kuali.rice.kew.web.KeyValue;

 import org.springmodules.orm.ojb.support.PersistenceBrokerDaoSupport;

 public class StatsDAOOjbImpl extends PersistenceBrokerDaoSupport implements StatsDAO {

     private static final org.apache.log4j.Logger LOG = org.apache.log4j.Logger.getLogger(StatsDAOOjbImpl.class);

     public static final String SQL_NUM_ACTIVE_ITEMS = "select count(*) from krew_actn_itm_t";

     public static final String SQL_NUM_DOC_TYPES_REPORT = "select count(*) as num from krew_doc_typ_t where cur_ind = 1";

     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";

     public static final String SQL_NUM_USERS = "select count(distinct prncpl_id) as prsn_count from KREW_USR_OPTN_T";

     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";

     public void NumActiveItemsReport(Stats stats) throws SQLException, LookupException {

         LOG.debug("NumActiveItemsReport()");

         PersistenceBroker broker = this.getPersistenceBroker(false);

         Connection conn = broker.serviceConnectionManager().getConnection();

         PreparedStatement ps = conn.prepareStatement(StatsDAOOjbImpl.SQL_NUM_ACTIVE_ITEMS);

         ResultSet rs = ps.executeQuery();

         while (rs.next()) {

             stats.setNumActionItems(new Integer(rs.getInt(1)).toString());

         }

         closeDatabaseObjects(rs, ps, conn/*, broker*/);

     }

     public void NumberOfDocTypesReport(Stats stats) throws SQLException, LookupException {

         LOG.debug("NumberOfDocTypesReport()");

         PersistenceBroker broker = this.getPersistenceBroker(false);

         Connection conn = broker.serviceConnectionManager().getConnection();

         PreparedStatement ps = conn.prepareStatement(StatsDAOOjbImpl.SQL_NUM_DOC_TYPES_REPORT);

         ResultSet rs = ps.executeQuery();

         while (rs.next()) {

             stats.setNumDocTypes(new Integer(rs.getInt(1)).toString());

         }

         closeDatabaseObjects(rs, ps, conn/*, broker*/);

     }

     public void DocumentsRoutedReport(Stats stats, Date begDate, Date endDate) throws SQLException, LookupException {

         LOG.debug("DocumentsRoutedReport()");

         PersistenceBroker broker = this.getPersistenceBroker(false);

         Connection conn = broker.serviceConnectionManager().getConnection();

         PreparedStatement ps = conn.prepareStatement(StatsDAOOjbImpl.SQL_DOCUMENTS_ROUTED);

         ps.setTimestamp(1, new Timestamp(begDate.getTime()));

         ps.setTimestamp(2, new Timestamp(endDate.getTime()));

         ResultSet rs = ps.executeQuery();

         while (rs.next()) {

             String actionType = rs.getString(2);

             String number = new Integer(rs.getInt(1)).toString();

             if (actionType.equals(KEWConstants.ROUTE_HEADER_APPROVED_CD)) {

                 stats.setApprovedNumber(number);

             } else if (actionType.equals(KEWConstants.ROUTE_HEADER_CANCEL_CD)) {

                 stats.setCanceledNumber(number);

             } else if (actionType.equals(KEWConstants.ROUTE_HEADER_DISAPPROVED_CD)) {

                 stats.setDisapprovedNumber(number);

             } else if (actionType.equals(KEWConstants.ROUTE_HEADER_ENROUTE_CD)) {

                 stats.setEnrouteNumber(number);

             } else if (actionType.equals(KEWConstants.ROUTE_HEADER_EXCEPTION_CD)) {

                 stats.setExceptionNumber(number);

             } else if (actionType.equals(KEWConstants.ROUTE_HEADER_FINAL_CD)) {

                 stats.setFinalNumber(number);

             } else if (actionType.equals(KEWConstants.ROUTE_HEADER_INITIATED_CD)) {

                 stats.setInitiatedNumber(number);

             } else if (actionType.equals(KEWConstants.ROUTE_HEADER_PROCESSED_CD)) {

                 stats.setProcessedNumber(number);

             } else if (actionType.equals(KEWConstants.ROUTE_HEADER_SAVED_CD)) {

                 stats.setSavedNumber(number);

             }

         }

         closeDatabaseObjects(rs, ps, conn/*, broker*/);

     }

     public void NumUsersReport(Stats stats) throws SQLException, LookupException {

         LOG.debug("NumUsersReport()");

         PersistenceBroker broker = this.getPersistenceBroker(false);

         Connection conn = broker.serviceConnectionManager().getConnection();

         PreparedStatement ps = conn.prepareStatement(StatsDAOOjbImpl.SQL_NUM_USERS);

         ResultSet rs = ps.executeQuery();

         while (rs.next()) {

             stats.setNumUsers(new Integer(rs.getInt("prsn_count")).toString());

         }

         closeDatabaseObjects(rs, ps, conn/*, broker*/);

     }

     public void NumInitiatedDocsByDocTypeReport(Stats stats) throws SQLException, LookupException {

         LOG.debug("NumInitiatedDocsByDocType()");

         PersistenceBroker broker = this.getPersistenceBroker(false);

         Connection conn = broker.serviceConnectionManager().getConnection();

         PreparedStatement ps = conn.prepareStatement(StatsDAOOjbImpl.SQL_NUM_DOCS_INITIATED);

         Calendar calendar = Calendar.getInstance();

         calendar.add(Calendar.DAY_OF_YEAR, -29);

         calendar.set(Calendar.HOUR_OF_DAY, 0);

         calendar.set(Calendar.MINUTE, 0);

         calendar.set(Calendar.SECOND, 0);

         calendar.set(Calendar.MILLISECOND, 0);

         ps.setTimestamp(1, new Timestamp(calendar.getTime().getTime()));

         ResultSet rs = ps.executeQuery();

         List numDocs = new ArrayList();

         while (rs.next()) {

             numDocs.add(new KeyValue(rs.getString(2), new Integer(rs.getInt(1)).toString()));

         }

         stats.setNumInitiatedDocsByDocType(numDocs);

         closeDatabaseObjects(rs, ps, conn/*, broker*/);

     }

     private void closeDatabaseObjects(ResultSet rs, PreparedStatement ps, Connection conn/*, PersistenceBroker broker*/) {

         try {

             rs.close();

         } catch (SQLException ex) {

             LOG.warn("Failed to close ResultSet.", ex);

         }

         try {

             ps.close();

         } catch (SQLException ex) {

             LOG.warn("Failed to close PreparedStatement.", ex);

         }

         try {

             conn.close();

         } catch (SQLException ex) {

             LOG.warn("Failed to close Connection.", ex);

         }

 /*

         try {

             broker.close();

         } catch (Exception ex) {

             LOG.warn("Failed to close broker.", ex);

         }

 */

     }

 }