/*

  * Copyright 2005-2007 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.quicklinks.dao.impl;

 import java.sql.Connection;

 import java.sql.PreparedStatement;

 import java.sql.ResultSet;

 import java.sql.SQLException;

 import java.util.ArrayList;

 import java.util.Collections;

 import java.util.List;

 import java.util.StringTokenizer;

 import org.apache.ojb.broker.PersistenceBroker;

 import org.kuali.rice.kew.docsearch.service.DocumentSearchService;

 import org.kuali.rice.kew.doctype.DocumentTypePolicy;

 import org.kuali.rice.kew.doctype.bo.DocumentType;

 import org.kuali.rice.kew.doctype.service.DocumentTypeService;

 import org.kuali.rice.kew.exception.WorkflowRuntimeException;

 import org.kuali.rice.kew.quicklinks.ActionListStats;

 import org.kuali.rice.kew.quicklinks.InitiatedDocumentType;

 import org.kuali.rice.kew.quicklinks.WatchedDocument;

 import org.kuali.rice.kew.quicklinks.dao.QuickLinksDAO;

 import org.kuali.rice.kew.service.KEWServiceLocator;

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

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

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

 import org.kuali.rice.kns.util.KNSConstants;

 import org.springmodules.orm.ojb.PersistenceBrokerCallback;

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

 public class QuickLinksDAOOjbImpl extends PersistenceBrokerDaoSupport implements QuickLinksDAO {

     public List<ActionListStats> getActionListStats(final String principalId) {

         return (List<ActionListStats>) this.getPersistenceBrokerTemplate().execute(new PersistenceBrokerCallback() {

             public Object doInPersistenceBroker(PersistenceBroker broker) {

                 PreparedStatement selectActionItems = null;

                 PreparedStatement selectDocTypeLabel = null;

                 ResultSet selectedActionItems = null;

                 ResultSet selectedDocTypeLabel = null;

                 List<ActionListStats> docTypes = new ArrayList<ActionListStats>();

                 try {

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

                     selectActionItems = connection.prepareStatement("select DOC_TYP_NM, COUNT(*) from KREW_ACTN_ITM_T where PRNCPL_ID = ? " +

                             "and (dlgn_typ is null or dlgn_typ != '" + KEWConstants.DELEGATION_SECONDARY + "') group by DOC_TYP_NM");

                     selectDocTypeLabel = connection.prepareStatement("select LBL from KREW_DOC_TYP_T WHERE DOC_TYP_NM = ? and CUR_IND = 1");

                     selectActionItems.setString(1, principalId);

                     selectedActionItems = selectActionItems.executeQuery();

                     while (selectedActionItems.next()) {

                         String docTypeName = selectedActionItems.getString(1);

                         int count = selectedActionItems.getInt(2);

                         selectDocTypeLabel.setString(1, docTypeName);

                         selectedDocTypeLabel = selectDocTypeLabel.executeQuery();

                         if (selectedDocTypeLabel.next()) {

                             docTypes.add(new ActionListStats(docTypeName, selectedDocTypeLabel.getString(1), count));

                         }

                     }

                     Collections.sort(docTypes);

                     return docTypes;

                 } catch (Exception e) {

                     throw new WorkflowRuntimeException("Error getting action list stats for user: " + principalId, e);

                 } finally {

                     if (selectActionItems != null) {

                         try {

                             selectActionItems.close();

                         } catch (SQLException e) {

                         }

                     }

                     if (selectDocTypeLabel != null) {

                         try {

                             selectDocTypeLabel.close();

                         } catch (SQLException e) {

                         }

                     }

                     if (selectedActionItems != null) {

                         try {

                             selectedActionItems.close();

                         } catch (SQLException e) {

                         }

                     }

                     if (selectedDocTypeLabel != null) {

                         try {

                             selectedDocTypeLabel.close();

                         } catch (SQLException e) {

                         }

                     }

                 }

             }

         });

     }

     public List<InitiatedDocumentType> getInitiatedDocumentTypesList(final String principalId) {

         return (List<InitiatedDocumentType>)  this.getPersistenceBrokerTemplate().execute(new PersistenceBrokerCallback() {

             public Object doInPersistenceBroker(PersistenceBroker broker) {

                 PreparedStatement selectDistinctDocumentTypes = null;

                 ResultSet selectedDistinctDocumentTypes = null;

                 List<InitiatedDocumentType> documentTypesByName = new ArrayList<InitiatedDocumentType>();

                 try {

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

 //                  select the doc type only if the SUPPORTS_QUICK_INITIATE policy is NULL or true

                     String sql = "select distinct B.DOC_TYP_NM, B.LBL from KREW_DOC_HDR_T A, KREW_DOC_TYP_T B "+

                             "where A.INITR_PRNCPL_ID = ? and A.DOC_TYP_ID = B.DOC_TYP_ID and " +

                             "B.ACTV_IND = 1 and B.CUR_IND = 1 " +

                             "order by upper(B.LBL)";

                     selectDistinctDocumentTypes = connection.prepareStatement(sql);

                     selectDistinctDocumentTypes.setString(1, principalId);

                     selectedDistinctDocumentTypes = selectDistinctDocumentTypes.executeQuery();

                     String documentNames = Utilities.getKNSParameterValue(KEWConstants.KEW_NAMESPACE, KNSConstants.DetailTypes.QUICK_LINK_DETAIL_TYPE, KEWConstants.QUICK_LINKS_RESTRICT_DOCUMENT_TYPES);

                     if (documentNames != null) {

                         // TODO Should this happen???

                         documentNames = documentNames.trim();

                     }

                     if (documentNames == null || "none".equals(documentNames)) {

                             documentNames = "";

                     }

                     List docTypesToRestrict = new ArrayList();

                     StringTokenizer st = new StringTokenizer(documentNames, ",");

                     while (st.hasMoreTokens()) {

                         docTypesToRestrict.add(st.nextToken());

                     }

                     while (selectedDistinctDocumentTypes.next()) {

                         String docTypeName = selectedDistinctDocumentTypes.getString(1);

                         String docTypeTopParent = "";

                         int firstPeriod = docTypeName.indexOf(".");

                         if (firstPeriod == -1) {

                             docTypeTopParent = docTypeName.substring(0);

                         } else {

                             docTypeTopParent = docTypeName.substring(0, firstPeriod);

                         }

                         if (!docTypesToRestrict.contains(docTypeTopParent)) {

                                 // the document types should be cached so this should be pretty quick

                                 DocumentType docType = KEWServiceLocator.getDocumentTypeService().findByName(docTypeName);

                                 DocumentTypePolicy quickInitiatePolicy = docType.getSupportsQuickInitiatePolicy();

                             if (quickInitiatePolicy.getPolicyValue().booleanValue()) {

                                     documentTypesByName.add(new InitiatedDocumentType(docTypeName, selectedDistinctDocumentTypes.getString(2)));

                             }

                         }

                     }

                     return documentTypesByName;

                 } catch (Exception e) {

                     throw new WorkflowRuntimeException("Error getting initiated document types for user: " + principalId, e);

                 } finally {

                     if (selectDistinctDocumentTypes != null) {

                         try {

                             selectDistinctDocumentTypes.close();

                         } catch (SQLException e) {

                         }

                     }

                     if (selectedDistinctDocumentTypes != null) {

                         try {

                             selectedDistinctDocumentTypes.close();

                         } catch (SQLException e) {

                         }

                     }

                 }

             }

         });

     }

     public List<KeyValue> getNamedSearches(String principalId) {

         return getDocumentSearchService().getNamedSearches(principalId);

     }

     public List<KeyValue> getRecentSearches(String principalId) {

         return getDocumentSearchService().getMostRecentSearches(principalId);

     }

     public List<WatchedDocument> getWatchedDocuments(final String principalId) {

         return (List<WatchedDocument>) this.getPersistenceBrokerTemplate().execute(new PersistenceBrokerCallback() {

             public Object doInPersistenceBroker(PersistenceBroker broker) {

                 List<WatchedDocument> watchedDocuments = new ArrayList<WatchedDocument>();

                 PreparedStatement selectWatchedDocuments = null;

                 ResultSet selectedWatchedDocuments = null;

                 try {

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

                     selectWatchedDocuments = connection.prepareStatement("select DOC_HDR_ID, DOC_HDR_STAT_CD, TTL, CRTE_DT from KREW_DOC_HDR_T where INITR_PRNCPL_ID = ? and DOC_HDR_STAT_CD in ('"+ KEWConstants.ROUTE_HEADER_ENROUTE_CD +"','"+ KEWConstants.ROUTE_HEADER_EXCEPTION_CD +"') order by CRTE_DT desc");

                     selectWatchedDocuments.setString(1, principalId);

                     selectedWatchedDocuments = selectWatchedDocuments.executeQuery();

                     while (selectedWatchedDocuments.next()) {

                         watchedDocuments.add(new WatchedDocument(selectedWatchedDocuments.getString(1), (String)KEWConstants.DOCUMENT_STATUSES.get(selectedWatchedDocuments.getString(2)), selectedWatchedDocuments.getString(3)));

                     }

                     return watchedDocuments;

                 } catch (Exception e) {

                     throw new WorkflowRuntimeException("Error getting initiated document types for user: " + principalId, e);

                 } finally {

                     if (selectWatchedDocuments != null) {

                         try {

                             selectWatchedDocuments.close();

                         } catch (SQLException e) {

                         }

                     }

                     if (selectedWatchedDocuments != null) {

                         try {

                             selectedWatchedDocuments.close();

                         } catch (SQLException e) {

                         }

                     }

                 }

             }

         });

     }

     public DocumentTypeService getDocumentTypeService() {

         return ((DocumentTypeService) KEWServiceLocator.getService(KEWServiceLocator.DOCUMENT_TYPE_SERVICE));

     }

     public DocumentSearchService getDocumentSearchService() {

         return ((DocumentSearchService) KEWServiceLocator.getService(KEWServiceLocator.DOCUMENT_SEARCH_SERVICE));

     }

 }