gt:         return new LocalDateTime().withTime(0, 0, 0, 0).toDateTime();
context: /*
   * eGov suite of products aim to improve the internal efficiency,transparency,
   *    accountability and the service delivery of the government  organizations.
   *
   *     Copyright (C) <2015>  eGovernments Foundation
   *
   *     The updated version of eGov suite of products as by eGovernments Foundation
   *     is available at http://www.egovernments.org
   *
   *     This program is free software: you can redistribute it and/or modify
   *     it under the terms of the GNU General Public License as published by
   *     the Free Software Foundation, either version 3 of the License, or
   *     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 General Public License for more details.
   *
   *     You should have received a copy of the GNU General Public License
   *     along with this program. If not, see http://www.gnu.org/licenses/ or
   *     http://www.gnu.org/licenses/gpl.html .
   *
   *     In addition to the terms of the GPL license to be adhered to in using this
   *     program, the following additional terms are to be complied with:
   *
   *         1) All versions of this program, verbatim or modified must carry this
   *            Legal Notice.
   *
   *         2) Any misrepresentation of the origin of the material is prohibited. It
   *            is required that all modified versions of this material be marked in
   *            reasonable ways as different from the original version.
   *
   *         3) This license does not grant any rights to any user of the program
   *            with regards to rights under trademark law for use of the trade names
   *            or trademarks of eGovernments Foundation.
   *
   *   In case of any queries, you can reach eGovernments Foundation at contact@egovernments.org.
   */
  
  package org.egov.pgr.service.reports;
  
  import org.hibernate.SQLQuery;
  import org.hibernate.Session;
  import org.joda.time.DateTime;
  import org.joda.time.LocalDateTime;
  import org.springframework.stereotype.Service;
  import org.springframework.transaction.annotation.Transactional;
  
  import javax.persistence.EntityManager;
  import javax.persistence.PersistenceContext;
  import java.util.Date;
  
  @Service
  @Transactional(readOnly = true)
  public class DrillDownReportService {
  
      @PersistenceContext
      private EntityManager entityManager;
      
      public SQLQuery getDrillDownReportQuery(final DateTime fromDate, final DateTime toDate,
              final String complaintDateType, final String groupBy, final String department, final String boundary,
              final String complainttype, final String selecteduser) {
  
          final StringBuilder query = new StringBuilder();
  
          if (boundary != null && !"".equals(boundary)) {
              if (department != null && !"".equals(department)) {
  
                  if (complainttype != null && !"".equals(complainttype))
                      query.append("  SELECT   emp.name||'~'|| pos.name    as name, ");
                  /* Next is userwise. */
                  else
                      query.append(" SELECT ctype.name as name, ");
                  /*
                   * means user selected boundary and department. Next is complaint type.
                   */ } else
                  query.append(" SELECT dept.name as name, "); /* Means get department list */
  
          } else if (department != null && !"".equals(department)) {
              if (complainttype != null && !"".equals(complainttype))
                  query.append("  SELECT   emp.name||'~'|| pos.name    as name, ");
              else
                  query.append(" SELECT ctype.name as name, ");
          } else if (complainttype != null && !"".equals(complainttype))
              query.append(" SELECT ctype.name as name, ");
          else if (selecteduser != null && !"".equals(selecteduser))
              query.append("  SELECT   emp.name||'~'|| pos.name    as name, ");
          else if (groupBy != null && !"".equals(groupBy) && groupBy.equalsIgnoreCase("ByBoundary"))
              query.append("SELECT bndry.name as name, ");
          else
              query.append("SELECT dept.name as name, ");
  
          query.append("   COUNT(CASE WHEN cs.name IN ('REGISTERED') THEN 1 END) registered , "
                  + " COUNT(CASE WHEN cs.name IN ('FORWARDED','PROCESSING','REOPENED','NOTCOMPLETED') THEN 1 END) inprocess, "
                  + " COUNT(CASE WHEN cs.name IN ('COMPLETED','WITHDRAWN','CLOSED') THEN 1 END) Completed, "
                  + " COUNT(CASE WHEN cs.name IN ('REJECTED') THEN 1 END) Rejected ,");
          query.append("SUM(CASE WHEN state.value in ('COMPLETED','REJECTED','WITHDRAWN') AND "
                  + "(cd.createddate - state.lastmodifieddate) < (interval '1h' * ctype.slahours) THEN 1 "
                  + "WHEN (state.value not in ('COMPLETED','REJECTED','WITHDRAWN') AND "
                  + "(cd.createddate - CURRENT_DATE) < (interval '1h' * ctype.slahours)) THEN 1 else 0 END) withinsla, ");
          query.append(" SUM(CASE WHEN state.value in ('COMPLETED','REJECTED','WITHDRAWN') AND "
                  + "(cd.createddate - state.lastmodifieddate) > (interval '1h' * ctype.slahours) THEN 1 "
                  + "WHEN (state.value not in ('COMPLETED','REJECTED','WITHDRAWN') AND "
                  + "(cd.createddate - CURRENT_DATE ) > (interval '1h' * ctype.slahours)) THEN 1 ELSE 0 END) beyondsla ");
          query.append(
                  " FROM egpgr_complaintstatus cs ,egpgr_complainttype ctype , eg_wf_states state, egpgr_complaint cd  left JOIN eg_boundary bndry on cd.location =bndry.id  left JOIN eg_department dept on cd.department =dept.id left join eg_position pos on cd.assignee=pos.id  left join view_egeis_employee emp on pos.id=emp.position ");
  
          buildWhereClause(fromDate, toDate, complaintDateType, query, department, boundary, complainttype, selecteduser);
  
          buildGroupByClause(groupBy, department, boundary, complainttype, selecteduser, query);
  
          return setParameterForDrillDownReportQuery(query.toString(), fromDate, toDate, complaintDateType);
      }
  
      private void buildGroupByClause(final String groupBy, final String department, final String boundary,
              final String complainttype, final String selecteduser, final StringBuilder query) {
          if (boundary != null && !"".equals(boundary)) {
              if (department != null && !"".equals(department)) {
                  if (complainttype != null && !"".equals(complainttype))
                      query.append("  group by emp.name||'~'|| pos.name ");
                  else
  
                      query.append("  group by ctype.name ");
  
              } else
                  query.append("  group by dept.name ");
          } else if (department != null && !"".equals(department)) {
  
              if (complainttype != null && !"".equals(complainttype))
                  query.append("  group by emp.name||'~'|| pos.name ");
              else
                  query.append("  group by ctype.name ");
          } else if (complainttype != null && !"".equals(complainttype))
              query.append(" group by ctype.name  ");
          else if (groupBy != null && !"".equals(groupBy) && groupBy.equalsIgnoreCase("ByBoundary"))
              query.append("  group by bndry.name ");
          else
              query.append("  group by dept.name ");
      }
  
      private void buildWhereClause(final DateTime fromDate, final DateTime toDate, final String complaintDateType,
              final StringBuilder query, final String department, final String boundary, final String complainttype,
              final String selecteduser) {
  
          query.append(" WHERE cd.status  = cs.id and cd.complainttype= ctype.id  and cd.state_id = state.id ");
  
          if (complaintDateType != null && complaintDateType.equals("lastsevendays"))
              query.append(" and cd.createddate >=   :fromDates ");
          else if (complaintDateType != null && complaintDateType.equals("lastthirtydays"))
              query.append(" and cd.createddate >=   :fromDates ");
          else if (complaintDateType != null && complaintDateType.equals("lastninetydays"))
              query.append(" and cd.createddate >=   :fromDates ");
          else if (fromDate != null && toDate != null)
              query.append(" and ( cd.createddate BETWEEN :fromDates and :toDates) ");
          else if (fromDate != null)
              query.append(" and cd.createddate >=   :fromDates ");
          else if (toDate != null)
              query.append(" and cd.createddate <=  :toDates ");
  
          if (boundary != null && !"".equals(boundary))
              if (boundary.equalsIgnoreCase("NOT AVAILABLE"))
                  query.append(" and  bndry.name is null ");
              else {
                  query.append(" and upper(trim(bndry.name))= '");
                  query.append(boundary.toUpperCase()).append("' ");
              }
          if (department != null && !"".equals(department))
              if (department.equalsIgnoreCase("NOT AVAILABLE"))
                  query.append(" and  dept.name is null ");
              else {
                  query.append(" and upper(trim(dept.name))=  '");
                  query.append(department.toUpperCase()).append("' ");
              }
          if (complainttype != null && !"".equals(complainttype)) {
              query.append(" and upper(trim(ctype.name))= '");
              query.append(complainttype.toUpperCase()).append("' ");
          }
  
      }
  
      private SQLQuery setParameterForDrillDownReportQuery(final String querykey, final DateTime fromDate,
              final DateTime toDate, final String complaintDateType) {
          final SQLQuery qry = entityManager.unwrap(Session.class).createSQLQuery(querykey);
  
          if (complaintDateType != null && complaintDateType.equals("lastsevendays"))
              qry.setParameter("fromDates", getCurrentDateWithOutTime().minusDays(7).toDate());
          else if (complaintDateType != null && complaintDateType.equals("lastthirtydays"))
              qry.setParameter("fromDates", getCurrentDateWithOutTime().minusDays(30).toDate());
          else if (complaintDateType != null && complaintDateType.equals("lastninetydays"))
              qry.setParameter("fromDates", getCurrentDateWithOutTime().minusDays(90).toDate());
          else if (fromDate != null && toDate != null) {
              qry.setParameter("fromDates", resetTimeByPassingDate(fromDate));
              qry.setParameter("toDates", getEndOfDayByDate(toDate));
  
          } else if (fromDate != null)
              qry.setParameter("fromDates", resetTimeByPassingDate(fromDate));
          else if (toDate != null)
              qry.setParameter("toDates", getEndOfDayByDate(toDate));
          return qry;
  
      }
  
      private Date getEndOfDayByDate(final DateTime fromDate) {
          return fromDate.withTime(23, 59, 59, 999).toDate();
      }
  
      private Date resetTimeByPassingDate(final DateTime fromDate) {
          return fromDate.withTime(0, 0, 0, 0).toDate();
      }
  
      private DateTime getCurrentDateWithOutTime() {
  