/*
 * Copyright (C) 2003-2016 eXo Platform SAS.
 *
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU Affero General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU Affero General Public License for more details.
 *
 * You should have received a copy of the GNU Affero General Public License
 * along with this program. If not, see <http://www.gnu.org/licenses/>.
 */
package org.exoplatform.cs.dao;

import java.util.Calendar;
import java.util.List;


import javax.persistence.TemporalType;
import org.exoplatform.cs.dto.IssueType;
import org.exoplatform.cs.entity.SpaceEntity;
import org.exoplatform.cs.service.CSConstants;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import org.exoplatform.commons.persistence.impl.GenericDAOJPAImpl;
import org.exoplatform.cs.entity.TopicEntity;

/**
 * Created by The eXo Platform SAS
 * 
 * @author boubaker.khanfir@exoplatform.com
 * @since Apr 27, 2016
 */
public class TopicDAO extends GenericDAOJPAImpl<TopicEntity, String> {
  private static final Logger LOG = LoggerFactory.getLogger(TopicDAO.class);

  public List<TopicEntity> getTopicsBySpace(String spaceGroupId, String selectedStatus) {
    try {
      if(selectedStatus != null && !selectedStatus.isEmpty()){
        return getEntityManager().createNamedQuery("topicEntity.getTopicsBySpaceAndStatus", TopicEntity.class).
            setParameter("status",selectedStatus).
            setParameter("spaceGroupID", spaceGroupId).getResultList();
      } else {
        return getEntityManager().createNamedQuery("topicEntity.getTopicsBySpace", TopicEntity.class).
            setParameter("spaceGroupID", spaceGroupId).getResultList();
      }
    } catch (Exception e) {
      LOG.error("Exception while attempting to get topics of space '" + spaceGroupId + "'.", e);
      throw e;
    }

  }

  public List<TopicEntity> getTopicsByEnvironment(Long environmentId) {
    try {
        return getEntityManager().createNamedQuery("topicEntity.getTopicsByEnvironment", TopicEntity.class).
            setParameter("environmentID", environmentId).getResultList();
    } catch (Exception e) {
      LOG.error("Exception while attempting to get topics By Environment '" + environmentId + "'.", e);
      throw e;
    }

  }

  /**
   * Fetches all topics by assignee
   *
   * @param assignee
   * The assignee ID
   * @param allTickets true if we need to get closed tickets, false otherwise
   * @param selectedStatus
   * @return List of Topic entities
   */
  public List<TopicEntity> getTopicsByAssignee(String assignee, boolean allTickets, String selectedStatus) {
    try {
      if(allTickets) {
        if(selectedStatus != null && ! selectedStatus.isEmpty()){
          return getEntityManager().createNamedQuery("topicEntity.getTopicsByAssigneeAndStatus", TopicEntity.class)
              .setParameter("assignee", assignee).setParameter("status",selectedStatus).getResultList();
        } else {
          return getEntityManager().createNamedQuery("topicEntity.getTopicsByAssignee", TopicEntity.class)
              .setParameter("assignee", assignee).getResultList();
        }
      } else {
        if(selectedStatus != null && ! selectedStatus.isEmpty()){
          return getEntityManager().createNamedQuery("topicEntity.getTopicsByAssigneeAndStatus", TopicEntity.class)
              .setParameter("assignee", assignee)
              .setParameter("status", selectedStatus)
              .getResultList();
        }else {
          return getEntityManager().createNamedQuery("topicEntity.getOpenTopicsByAssignee", TopicEntity.class)
              .setParameter("assignee", assignee)
              .setParameter("status", CSConstants.STATUS_CLOSED).getResultList();
        }
      }
    } catch (Exception e) {
      LOG.error("Exception while attempting to get topics by assignee '" + assignee + "'.", e);
      throw e;
    }

  }

  /**
   * Fetches all topics by assignee
   * @param assignee The assignee ID
   * @param selectedSpace the space to retrieve tickets from
   * @param allTickets true if we need to get closed tickets, false otherwise
   * @return List of Topic entities
   */
  public List<TopicEntity> getTopicsByAssignee(String assignee, String selectedSpace, boolean allTickets, String selectedStatus) {
    try {
      if(allTickets) {
        if(selectedStatus != null && ! selectedStatus.isEmpty()){
          return getEntityManager().createNamedQuery("topicEntity.getTopicsBySpaceAndAssigneeAndStatus", TopicEntity.class)
              .setParameter("assignee", assignee).setParameter("status",selectedStatus).setParameter("spaceGroupID", selectedSpace).getResultList();
        } else {
          return getEntityManager().createNamedQuery("topicEntity.getTopicsBySpaceAndAssignee", TopicEntity.class)
              .setParameter("assignee", assignee).setParameter("spaceGroupID", selectedSpace).getResultList();
        }
      } else {
        if(selectedStatus != null && ! selectedStatus.isEmpty()){
          return getEntityManager().createNamedQuery("topicEntity.getTopicsBySpaceAndAssigneeAndStatus", TopicEntity.class)
              .setParameter("assignee", assignee).setParameter("status",selectedStatus).setParameter("spaceGroupID", selectedSpace).getResultList();
        } else {
          return getEntityManager().createNamedQuery("topicEntity.getOpenTopicsByAssigneeAndSpace", TopicEntity.class)
              .setParameter("assignee", assignee)
              .setParameter("spaceGroupID", selectedSpace)
              .setParameter("status", CSConstants.STATUS_CLOSED).getResultList();
        }
      }
    } catch (Exception e) {
      LOG.error("Exception while attempting to get topics by assignee '" + assignee + "'.", e);
      throw e;
    }

  }

  public List<TopicEntity> getOpenTickets() {
    return getEntityManager().createNamedQuery("topicEntity.getOpenTopics", TopicEntity.class)
        .setParameter("status", CSConstants.STATUS_CLOSED).getResultList();
  }

  public List<TopicEntity> findUpdatedBetween(Calendar fromDate, Calendar toDate) {
    return getEntityManager().createNamedQuery(
        "topicEntity.findUpdatedTopics", TopicEntity.class)
        .setParameter("from", fromDate, TemporalType.TIMESTAMP).
            setParameter("to", toDate, TemporalType.TIMESTAMP).getResultList();
  }

  public List<TopicEntity> getUpdatedTicketsByStatus(String status, Calendar fromDate, Calendar toDate) {
    return getEntityManager().createNamedQuery(
            "topicEntity.findUpdatedTicketsByStatus", TopicEntity.class)
            .setParameter("status", status).
            setParameter("from", fromDate, TemporalType.TIMESTAMP).
                    setParameter("to", toDate, TemporalType.TIMESTAMP).getResultList();
  }

  public List<TopicEntity> getUpdatedTicketsBySpaceAndStatus( String space, String status, Calendar fromDate, Calendar toDate) {
    return getEntityManager().createNamedQuery(
            "topicEntity.findUpdatedTopicsBySpaceAndStatus", TopicEntity.class)
            .setParameter("status", status)
            .setParameter("spaceGroupID", space).
                    setParameter("from", fromDate, TemporalType.TIMESTAMP).
                    setParameter("to", toDate, TemporalType.TIMESTAMP).getResultList();
  }

  public List<TopicEntity> getUpdatedTicketsBySpace(String space, Calendar fromDate, Calendar toDate) {
    return getEntityManager().createNamedQuery(
            "topicEntity.findUpdatedTopicsBySpace", TopicEntity.class)
            .setParameter("spaceGroupID", space).
                    setParameter("from", fromDate, TemporalType.TIMESTAMP).
                    setParameter("to", toDate, TemporalType.TIMESTAMP).getResultList();
  }



  public List<TopicEntity> getAllTickets() {
    return getEntityManager().createNamedQuery(
            "topicEntity.findAllTickets", TopicEntity.class).getResultList();
  }

  public List<TopicEntity> getAllTicketsWithPagination(int offset, int limit) {
    return getEntityManager().createNamedQuery(
            "topicEntity.findAllTickets", TopicEntity.class).setFirstResult(offset).setMaxResults(limit).getResultList();
  }




  public List<TopicEntity> getCreatedTicketsBySpace(String space, Calendar fromDate, Calendar toDate) {
    return getEntityManager().createNamedQuery(
            "topicEntity.findCreatedTopicsBySpace", TopicEntity.class)
            .setParameter("spaceGroupID", space).
                    setParameter("from", fromDate, TemporalType.TIMESTAMP).
                    setParameter("to", toDate, TemporalType.TIMESTAMP).getResultList();
  }


  public List<TopicEntity> getCreatedTickets(Calendar fromDate, Calendar toDate) {
    return getEntityManager().createNamedQuery(
            "topicEntity.findCreatedTopics", TopicEntity.class).
                    setParameter("from", fromDate, TemporalType.TIMESTAMP).
                    setParameter("to", toDate, TemporalType.TIMESTAMP).getResultList();
  }

  public List<TopicEntity> getNotAssignedTicketsBySpace(String space) {
    return getEntityManager().createNamedQuery(
            "topicEntity.findNotAssignedTopicsBySpace", TopicEntity.class)
            .setParameter("spaceGroupID", space).getResultList();
  }


  public List<TopicEntity> getNotAssignedTickets() {
    return getEntityManager().createNamedQuery(
            "topicEntity.findNotAssignedTopics", TopicEntity.class).getResultList();
  }

  public List<TopicEntity> findTicketsForReminder(Calendar toDate) {
    return getEntityManager().createNamedQuery("topicEntity.findTicketsForReminder",
            TopicEntity.class).setParameter("to",toDate).getResultList();
  }

  public List<TopicEntity> findTicketsForSLAReminder(Calendar instant) {
    return getEntityManager().createNamedQuery("topicEntity.findTicketsForSLAReminder",
            TopicEntity.class).setParameter("instant",instant).getResultList();
  }


  public Long countTickets(SpaceEntity space, IssueType type) {
    return getEntityManager().createNamedQuery("topicEntity.countTickets", Long.class)
        .setParameter("type", type).setParameter("space", space).getSingleResult();
  }
  @Deprecated
  public List<TopicEntity> getEmptyTitleOrCreationDate () {
    return getEntityManager().createNamedQuery("topicEntity.findEmptyTitleAndCreationDateTopics",
                                               TopicEntity.class).setMaxResults(50).getResultList();
  }

  public List<TopicEntity> getWithEmptyTicketID() {
    return getEntityManager().createNamedQuery("topicEntity.findEmptyTicketID",
                                               TopicEntity.class).setMaxResults(50).getResultList();
  }

  public List<TopicEntity> getOpenTicketsBySpace(String selectedSpace, String selectedStatus) {
      if(selectedStatus != null && !selectedStatus.isEmpty()) {
          return getEntityManager().createNamedQuery("topicEntity.findOpenTicketsBySpaceAndStatus",
                  TopicEntity.class).setParameter("spaceGroupID", selectedSpace)
                  .setParameter("status", selectedStatus)
                  .setParameter("closedStatus", CSConstants.STATUS_CLOSED).getResultList();
      } else {
          return getEntityManager().createNamedQuery("topicEntity.findOpenTicketsBySpace",
                  TopicEntity.class).setParameter("spaceGroupID", selectedSpace)
                  .setParameter("closedStatus", CSConstants.STATUS_CLOSED).getResultList();
      }
  }

  public TopicEntity findByTicketId(String ticketId) {
    return getEntityManager().createNamedQuery("topicEntity.findTicketByTicketId",
                                               TopicEntity.class).setParameter("ticketId",ticketId).getSingleResult();
  }

  public List<TopicEntity> findByStatus(String selectedStatus) {
    return getEntityManager().createNamedQuery("topicEntity.findAllTicketByStatus",
                                               TopicEntity.class).setParameter("status",selectedStatus).getResultList();
  }

  public List<TopicEntity> getOpenTicketsByStatus(String selectedStatus) {
      if(selectedStatus != null && !selectedStatus.isEmpty()) {
          return getEntityManager().createNamedQuery("topicEntity.findOpenTicketsByStatus", TopicEntity.class)
                  .setParameter("status", selectedStatus)
                  .setParameter("closedStatus", CSConstants.STATUS_CLOSED).getResultList();
      } else {
          return getEntityManager().createNamedQuery("topicEntity.getOpenTopics", TopicEntity.class)
                  .setParameter("status", CSConstants.STATUS_CLOSED).getResultList();

      }
  }

  /**
   * Fetches all topics by creator
   *
   * @param creator
   * The creator ID
   * @param allTickets true if we need to get closed tickets, false otherwise
   * @param selectedStatus
   * @return List of Topic entities
   */
  public List<TopicEntity> getTopicsByCreator(String creator, boolean allTickets, String selectedStatus) {
    try {
      if(allTickets) {
        if(selectedStatus != null && ! selectedStatus.isEmpty()){
          return getEntityManager().createNamedQuery("topicEntity.getTopicsByCreatorAndStatus", TopicEntity.class)
                  .setParameter("creator", creator).setParameter("status",selectedStatus).getResultList();
        } else {
          return getEntityManager().createNamedQuery("topicEntity.getTopicsByCreator", TopicEntity.class)
                  .setParameter("creator", creator).getResultList();
        }
      } else {
        if(selectedStatus != null && ! selectedStatus.isEmpty()){
          return getEntityManager().createNamedQuery("topicEntity.getTopicsByCreatorAndStatus", TopicEntity.class)
                  .setParameter("creator", creator)
                  .setParameter("status", selectedStatus)
                  .getResultList();
        }else {
          return getEntityManager().createNamedQuery("topicEntity.getOpenTopicsByCreator", TopicEntity.class)
                  .setParameter("creator", creator)
                  .setParameter("status", CSConstants.STATUS_CLOSED).getResultList();
        }
      }
    } catch (Exception e) {
      LOG.error("Exception while attempting to get topics by creator '" + creator + "'.", e);
      throw e;
    }

  }

  /**
   * Fetches all topics by creator
   * @param creator The creator ID
   * @param selectedSpace the space to retrieve tickets from
   * @param allTickets true if we need to get closed tickets, false otherwise
   * @return List of Topic entities
   */
  public List<TopicEntity> getTopicsByCreator(String creator, String selectedSpace, boolean allTickets, String selectedStatus) {
    try {
      if(allTickets) {
        if(selectedStatus != null && ! selectedStatus.isEmpty()){
          return getEntityManager().createNamedQuery("topicEntity.getTopicsBySpaceAndCreatorAndStatus", TopicEntity.class)
                  .setParameter("creator", creator).setParameter("status",selectedStatus).setParameter("spaceGroupID", selectedSpace).getResultList();
        } else {
          return getEntityManager().createNamedQuery("topicEntity.getTopicsBySpaceAndCreator", TopicEntity.class)
                  .setParameter("creator", creator).setParameter("spaceGroupID", selectedSpace).getResultList();
        }
      } else {
        if(selectedStatus != null && ! selectedStatus.isEmpty()){
          return getEntityManager().createNamedQuery("topicEntity.getTopicsBySpaceAndCreatorAndStatus", TopicEntity.class)
                  .setParameter("creator", creator).setParameter("status",selectedStatus).setParameter("spaceGroupID", selectedSpace).getResultList();
        } else {
          return getEntityManager().createNamedQuery("topicEntity.getOpenTopicsByCreatorAndSpace", TopicEntity.class)
                  .setParameter("creator", creator)
                  .setParameter("spaceGroupID", selectedSpace)
                  .setParameter("status", CSConstants.STATUS_CLOSED).getResultList();
        }
      }
    } catch (Exception e) {
      LOG.error("Exception while attempting to get topics by creator '" + creator + "'.", e);
      throw e;
    }

  }


  public List<Object[]> countCustomerTicketsGroupdBySpace(boolean internal) {
    try {
      return getEntityManager().createNamedQuery("topicEntity.countCustomerTicketsGroupdBySpace", Object[].class)
              .setParameter("closedStatus", CSConstants.STATUS_CLOSED)
              .setParameter("internal", internal)
              .getResultList();
    }  catch (Exception e) {
      LOG.warn("Exception while attempting to get request", e);
      throw e;
    }
  }
  public List<Object[]> countCustomerTicketsGroupdByOwner(boolean internal) {
    try {
      return getEntityManager().createNamedQuery("topicEntity.countCustomerTicketsGroupdByOwner", Object[].class)
              .setParameter("closedStatus", CSConstants.STATUS_CLOSED)
              .setParameter("internal", internal)
              .getResultList();
    }  catch (Exception e) {
      LOG.warn("Exception while attempting to get request", e);
      throw e;
    }
  }
  public List<Object[]> countCustomerTicketsGroupdByStatus(boolean internal) {
    try {
      return getEntityManager().createNamedQuery("topicEntity.countCustomerTicketsGroupdByStatus", Object[].class)
              .setParameter("closedStatus", CSConstants.STATUS_CLOSED)
              .setParameter("internal", internal)
              .getResultList();
    }  catch (Exception e) {
      LOG.warn("Exception while attempting to get request", e);
      throw e;
    }
  }

  public List<Object[]> countSpaceTicketsGroupdByStatus(String selectedSpace) {
    try {
      return getEntityManager().createNamedQuery("topicEntity.countSpaceTicketsGroupdByStatus", Object[].class)
              .setParameter("closedStatus", CSConstants.STATUS_CLOSED)
              .setParameter("spaceGroupID", selectedSpace)
              .getResultList();
    }  catch (Exception e) {
      LOG.warn("Exception while attempting to get request", e);
      throw e;
    }
  }

  public List<Object[]> countSpaceTicketsGroupdByOwner(String selectedSpace) {
    try {
      return getEntityManager().createNamedQuery("topicEntity.countSpaceTicketsGroupdByOwner", Object[].class)
              .setParameter("closedStatus", CSConstants.STATUS_CLOSED)
              .setParameter("spaceGroupID", selectedSpace)
              .getResultList();
    }  catch (Exception e) {
      LOG.warn("Exception while attempting to get request", e);
      throw e;
    }
  }

  public List<Object[]> countSpaceTicketsGroupdByAssignee(String selectedSpace) {
    try {
      return getEntityManager().createNamedQuery("topicEntity.countSpaceTicketsGroupdByAssignee", Object[].class)
              .setParameter("closedStatus", CSConstants.STATUS_CLOSED)
              .setParameter("spaceGroupID", selectedSpace)
              .getResultList();
    }  catch (Exception e) {
      LOG.warn("Exception while attempting to get request", e);
      throw e;
    }
  }


    public Long countOpenCustomerTickets(boolean internal) {
        return getEntityManager().createNamedQuery("topicEntity.countOpenCustomerTickets", Long.class)
                .setParameter("internal", internal)
                .setParameter("closedStatus", CSConstants.STATUS_CLOSED)
                .getSingleResult();
    }

    public Long countClosedCustomerTickets(boolean internal) {
        return getEntityManager().createNamedQuery("topicEntity.countClosedCustomerTickets", Long.class)
                .setParameter("internal", internal)
                .setParameter("closedStatus", CSConstants.STATUS_CLOSED)
                .getSingleResult();
    }

    public Long countOpenSpaceTickets(String selectedSpace) {
        return getEntityManager().createNamedQuery("topicEntity.countOpenSpaceTickets", Long.class)
                .setParameter("spaceGroupID", selectedSpace)
                .setParameter("closedStatus", CSConstants.STATUS_CLOSED)
                .getSingleResult();
    }

    public Long countClosedSpaceTickets(String selectedSpace) {
        return getEntityManager().createNamedQuery("topicEntity.countClosedSpaceTickets", Long.class)
                .setParameter("spaceGroupID", selectedSpace)
                .setParameter("closedStatus", CSConstants.STATUS_CLOSED)
                .getSingleResult();
    }

    public Long countOpenCustomerTicketsByDates(boolean internal, Calendar from, Calendar to) {
        return getEntityManager().createNamedQuery("topicEntity.countOpenCustomerTicketsByDates", Long.class)
                .setParameter("internal", internal)
                .setParameter("from", from, TemporalType.TIMESTAMP)
                .setParameter("to", to, TemporalType.TIMESTAMP)
                .getSingleResult();
    }

    public Long countClosedCustomerTicketsByDates(boolean internal, Calendar from, Calendar to) {
        return getEntityManager().createNamedQuery("topicEntity.countClosedCustomerTicketsByDates", Long.class)
                .setParameter("internal", internal)
                .setParameter("closedStatus", CSConstants.STATUS_CLOSED)
                .setParameter("from", from, TemporalType.TIMESTAMP)
                .setParameter("to", to, TemporalType.TIMESTAMP)
                .getSingleResult();
    }

  public List<Object[]> countCustomerTicketsGroupdByStatusAndDates(boolean internal, Calendar from, Calendar to) {
    try {
      return getEntityManager().createNamedQuery("topicEntity.countCustomerTicketsGroupdByStatusAndDates", Object[].class)
              .setParameter("internal", internal)
              .setParameter("closedStatus", CSConstants.STATUS_CLOSED)
              .setParameter("from", from, TemporalType.TIMESTAMP)
              .setParameter("to", to, TemporalType.TIMESTAMP)
              .getResultList();
    }  catch (Exception e) {
      LOG.warn("Exception while attempting to get request", e);
      throw e;
    }
  }


  public List<Object[]> countCustomerTicketsGroupdByAssigneeAndDates(boolean internal, Calendar from, Calendar to) {
    try {
      return getEntityManager().createNamedQuery("topicEntity.countCustomerTicketsGroupdByAssigneeAndDates", Object[].class)
              .setParameter("closedStatus", CSConstants.STATUS_CLOSED)
              .setParameter("internal", internal)
              .setParameter("from", from, TemporalType.TIMESTAMP)
              .setParameter("to", to, TemporalType.TIMESTAMP)
              .getResultList();
    }  catch (Exception e) {
      LOG.warn("Exception while attempting to get request", e);
      throw e;
    }
  }



  public List<Object[]> countCustomerTicketsGroupdByAssignee(boolean internal) {
    try {
      return getEntityManager().createNamedQuery("topicEntity.countCustomerTicketsGroupdByAssignee", Object[].class)
              .setParameter("closedStatus", CSConstants.STATUS_CLOSED)
              .setParameter("internal", internal)
              .getResultList();
    }  catch (Exception e) {
      LOG.warn("Exception while attempting to get request", e);
      throw e;
    }
  }



  public List<Object[]> countUnresolvedTicketsGroupdByAssignee() {
    try {
      return getEntityManager().createNamedQuery("topicEntity.countUnresolvedTicketsGroupdByAssignee", Object[].class)
              .getResultList();
    }  catch (Exception e) {
      LOG.warn("Exception while attempting to get request", e);
      throw e;
    }
  }



  public List<Object[]> countCustomerTicketsGroupdBySeverityAndDates(boolean internal, Calendar from, Calendar to) {
    try {
      return getEntityManager().createNamedQuery("topicEntity.countCustomerTicketsGroupdBySeverityAndDates", Object[].class)
              .setParameter("closedStatus", CSConstants.STATUS_CLOSED)
              .setParameter("internal", internal)
              .setParameter("from", from, TemporalType.TIMESTAMP)
              .setParameter("to", to, TemporalType.TIMESTAMP)
              .getResultList();
    }  catch (Exception e) {
      LOG.warn("Exception while attempting to get request", e);
      throw e;
    }
  }



  public List<Object[]> countCustomerTicketsGroupdBySeverity(boolean internal) {
    try {
      return getEntityManager().createNamedQuery("topicEntity.countCustomerTicketsGroupdBySeverity", Object[].class)
              .setParameter("closedStatus", CSConstants.STATUS_CLOSED)
              .setParameter("internal", internal)
              .getResultList();
    }  catch (Exception e) {
      LOG.warn("Exception while attempting to get request", e);
      throw e;
    }
  }



  public List<Object[]> countSpaceTicketsGroupdBySeverity(String selectedSpace) {
    try {
      return getEntityManager().createNamedQuery("topicEntity.countSpaceTicketsGroupdBySeverity", Object[].class)
              .setParameter("closedStatus", CSConstants.STATUS_CLOSED)
              .setParameter("spaceGroupID", selectedSpace)
              .getResultList();
    }  catch (Exception e) {
      LOG.warn("Exception while attempting to get request", e);
      throw e;
    }
  }



  public double getTimeToResolutionAvg(boolean internal) {
    try {
      return getEntityManager().createNamedQuery("topicEntity.getTimeToResolutionAvg", Double.class)
              .setParameter("internal", internal)
              .getSingleResult();
    }  catch (NullPointerException e) {
      return 0;
    }   catch (Exception e) {
      LOG.warn("Exception while attempting to get request", e);
      throw e;
    }
  }

  public double getTimeToFirstResponseAvg(boolean internal) {
    try {
      return getEntityManager().createNamedQuery("topicEntity.getTimeToFirstResponseAvg", Double.class)
              .setParameter("internal", internal)
              .getSingleResult();
    }  catch (NullPointerException e) {
      return 0;
    }   catch (Exception e) {
      LOG.warn("Exception while attempting to get request", e);
      throw e;
    }
  }

  public Long countSatisfiedTicketNumber(boolean internal) {
    try {
      return getEntityManager().createNamedQuery("topicEntity.countSatisfiedTicketNumber", Long.class)
              .setParameter("internal", internal)
              .getSingleResult();
    }   catch (Exception e) {
      LOG.warn("Exception while attempting to get request", e);
      throw e;
    }
  }

  public Long countSatisfiedTicketNumberByDates(boolean internal,Calendar from, Calendar to) {
    try {
      return getEntityManager().createNamedQuery("topicEntity.countSatisfiedTicketNumberByDates", Long.class)
              .setParameter("internal", internal)
              .setParameter("from", from, TemporalType.TIMESTAMP)
              .setParameter("to", to, TemporalType.TIMESTAMP)
              .getSingleResult();
    }   catch (Exception e) {
      LOG.warn("Exception while attempting to get request", e);
      throw e;
    }
  }

  public Long countNotSatisfiedTicketNumber(boolean internal) {
    try {
      return getEntityManager().createNamedQuery("topicEntity.countNotSatisfiedTicketNumber", Long.class)
              .setParameter("internal", internal)
              .getSingleResult();
    }   catch (Exception e) {
      LOG.warn("Exception while attempting to get request", e);
      throw e;
    }
  }

  public Long countNotSatisfiedTicketNumberByDates(boolean internal, Calendar from, Calendar to) {
    try {
      return getEntityManager().createNamedQuery("topicEntity.countNotSatisfiedTicketNumberByDates", Long.class)
              .setParameter("internal", internal)
              .setParameter("from", from, TemporalType.TIMESTAMP)
              .setParameter("to", to, TemporalType.TIMESTAMP)
              .getSingleResult();
    }   catch (Exception e) {
      LOG.warn("Exception while attempting to get request", e);
      throw e;
    }
  }


  public double getSpaceTimeToResolutionAvg(String selectedSpace) {
    try {
      return getEntityManager().createNamedQuery("topicEntity.getSpaceTimeToResolutionAvg", Double.class)
              .setParameter("spaceGroupID", selectedSpace)
              .getSingleResult();
    }  catch (NullPointerException e) {
      return 0;
    }   catch (Exception e) {
      LOG.warn("Exception while attempting to get request", e);
      throw e;
    }
  }

  public double getSpaceTimeToFirstResponseAvg(String selectedSpace) {
    try {
      return getEntityManager().createNamedQuery("topicEntity.getSpaceTimeToFirstResponseAvg", Double.class)
              .setParameter("spaceGroupID", selectedSpace)
              .getSingleResult();
    }  catch (NullPointerException e) {
      return 0;
    }   catch (Exception e) {
      LOG.warn("Exception while attempting to get request", e);
      throw e;
    }
  }

  public Long countSpaceSatisfiedTicketNumber(String selectedSpace) {
    try {
      return getEntityManager().createNamedQuery("topicEntity.countSpaceSatisfiedTicketNumber", Long.class)
              .setParameter("spaceGroupID", selectedSpace)
              .getSingleResult();
    }   catch (Exception e) {
      LOG.warn("Exception while attempting to get request", e);
      throw e;
    }
  }

  public Long countSpaceSatisfiedTicketNumberByDates(String selectedSpace, Calendar from, Calendar to) {
    try {
      return getEntityManager().createNamedQuery("topicEntity.countSpaceSatisfiedTicketNumberByDates", Long.class)
              .setParameter("spaceGroupID", selectedSpace)
              .setParameter("from", from, TemporalType.TIMESTAMP)
              .setParameter("to", to, TemporalType.TIMESTAMP)
              .getSingleResult();
    }   catch (Exception e) {
      LOG.warn("Exception while attempting to get request", e);
      throw e;
    }
  }

  public Long countSpaceNotSatisfiedTicketNumber(String selectedSpace) {
    try {
      return getEntityManager().createNamedQuery("topicEntity.countSpaceNotSatisfiedTicketNumber", Long.class)
              .setParameter("spaceGroupID", selectedSpace)
              .getSingleResult();
    }   catch (Exception e) {
      LOG.warn("Exception while attempting to get request", e);
      throw e;
    }
  }

  public Long countSpaceNotSatisfiedTicketNumberByDates(String selectedSpace, Calendar from, Calendar to) {
    try {
      return getEntityManager().createNamedQuery("topicEntity.countSpaceNotSatisfiedTicketNumberByDates", Long.class)
              .setParameter("spaceGroupID", selectedSpace)
              .setParameter("from", from, TemporalType.TIMESTAMP)
              .setParameter("to", to, TemporalType.TIMESTAMP)
              .getSingleResult();
    }   catch (Exception e) {
      LOG.warn("Exception while attempting to get request", e);
      throw e;
    }
  }
}
