Example usage for org.hibernate SQLQuery setDate

List of usage examples for org.hibernate SQLQuery setDate

Introduction

In this page you can find the example usage for org.hibernate SQLQuery setDate.

Prototype

@Deprecated
@SuppressWarnings("unchecked")
default Query<R> setDate(int position, Date val) 

Source Link

Document

Bind a positional Date-valued parameter using just the Date portion.

Usage

From source file:org.egov.services.deduction.ScheduledRemittanceService.java

License:Open Source License

private List<AutoRemittanceBean> getNonControleCodeJVRecoveries(final Integer dept) {

    final StringBuffer qry = new StringBuffer(2048);
    qry.append(" SELECT DISTINCT gl.id AS generalledgerId,  vh.fundid           AS fundId,  gl.creditamount   "
            + "   AS gldtlAmount,ih.bankaccountid AS bankAccountId "
            + " FROM VOUCHERHEADER vh ,  VOUCHERMIS mis,  GENERALLEDGER gl ,  VOUCHERHEADER payment,  "
            + " EGF_INSTRUMENTHEADER ih, EGF_INSTRUMENTVOUCHER iv ,TDS recovery,miscbilldetail mb "
            + " WHERE  recovery.GLCODEID =gl.GLCODEID  AND vh.ID =gl.VOUCHERHEADERID "
            + " AND gl.remittanceDate    IS NULL AND mis.VOUCHERHEADERID   =vh.ID AND vh.STATUS =0  "
            + " AND ih.id =iv.instrumentheaderid "
            + " AND iv.voucherheaderid    =payment.id and payment.status=0 AND ih.id_status NOT     IN ("
            + "select id from egw_status where moduletype='Instrument' and description in ('"
            + FinancialConstants.INSTRUMENT_CANCELLED_STATUS + "','"
            + FinancialConstants.INSTRUMENT_SURRENDERED_STATUS + "','"
            + FinancialConstants.INSTRUMENT_SURRENDERED_FOR_REASSIGN_STATUS + "') "
            + " ) and mb.billvhid=vh.id and mb.payvhid=payment.id " + " AND recovery.ID      ="
            + recovery.getId() + "  ");

    if (lastRunDate != null)
        qry.append(" and (ih.instrumentdate<= :lastrundate or ih.transactiondate<=:lastrundate )");
    if (startDate != null)
        qry.append(" and (ih.instrumentdate >=:startdate or ih.transactiondate>=:startdate )");

    final SQLQuery query = persistenceService.getSession().createSQLQuery(qry.toString());
    query.addScalar("generalledgerId", IntegerType.INSTANCE).addScalar("fundId", IntegerType.INSTANCE)
            .addScalar("gldtlAmount", DoubleType.INSTANCE).addScalar("bankAccountId", IntegerType.INSTANCE);
    if (lastRunDate != null)
        query.setDate("lastrundate", new java.sql.Date(lastRunDate.getTime()));

    if (startDate != null)
        query.setDate("startdate", new java.sql.Date(startDate.getTime()));

    query.setResultTransformer(Transformers.aliasToBean(AutoRemittanceBean.class));
    if (LOGGER.isDebugEnabled())
        LOGGER.debug("getNonControleCodeJVRecoveries query " + qry);
    return query.list();

}

From source file:org.kimios.kernel.reporting.impl.DocumentHitsReport.java

License:Open Source License

public String getData() throws ConfigException, DataSourceException {
    if (order != null && order.length() == 0) {
        order = null;//from  w  w  w .  j  av  a 2  s .c o m
    }
    try {
        Calendar dtFrom = Calendar.getInstance();
        dtFrom.setTime(dateFrom);
        Calendar dtTo = Calendar.getInstance();
        dtTo.setTime(dateTo);

        dtFrom.set(Calendar.SECOND, 0);
        dtFrom.set(Calendar.MINUTE, 0);
        dtFrom.set(Calendar.HOUR, 0);

        dtTo.set(Calendar.SECOND, 59);
        dtTo.set(Calendar.MINUTE, 59);
        dtTo.set(Calendar.HOUR, 23);

        String rq = "SELECT e.dm_entity_name as DocumentName, count(*) as HitsCount, e.dm_entity_path as Position "
                + "FROM document d, entity_log dl, dm_entity e " + "WHERE d.id=dl.dm_entity_id  "
                + "AND d.id=e.dm_entity_id " + "AND dl.dm_entity_type=3  " + "AND dl.action IN ( "
                + ActionType.READ + "," + ActionType.UPDATE + "," + ActionType.CREATE + ") "
                + "AND dl.log_time >= :dateFrom " + "AND dl.log_time <= :dateTo "
                + "GROUP BY e.dm_entity_name,e.dm_entity_path,e.dm_entity_id  " + "ORDER BY "
                + (order == null ? "HitsCount" : order) + " " + (asc ? "ASC" : "DESC");

        SQLQuery sql = FactoryInstantiator.getInstance().getDtrFactory().getSession().createSQLQuery(rq);
        sql.addScalar("DocumentName", StringType.INSTANCE);
        sql.addScalar("HitsCount", IntegerType.INSTANCE);
        sql.addScalar("Position", StringType.INSTANCE);
        sql.setDate("dateFrom", dtFrom.getTime());
        sql.setDate("dateTo", dtTo.getTime());

        List<Object[]> lReports = sql.list();
        Report report = new Report("DocumentHits");
        report.addColumn("Position");
        report.addColumn("DocumentName");
        report.addColumn("HitsCount");

        for (Object[] r : lReports) {
            Vector<Cell> cells = new Vector<Cell>();
            cells.add(new Cell("Position",
                    (String) ((String) r[2]).substring(0, ((String) r[2]).lastIndexOf('/'))));
            cells.add(new Cell("DocumentName", (String) r[0]));
            cells.add(new Cell("HitsCount", (Integer) r[1]));

            report.addRow(new Row(cells));
        }

        return report.toXML();
    } catch (HibernateException he) {
        he.printStackTrace();
        throw he;
    }
}

From source file:org.kimios.kernel.reporting.impl.UserActionsReport.java

License:Open Source License

public String getData() throws ConfigException, DataSourceException {
    if (order != null && order.length() == 0) {
        order = null;//ww  w .j  a v a 2 s. c  o m
    }
    if (actionType != null && order != null && order.length() == 0) {
        actionType = null;
    }
    String tmpTable = null;
    try {
        Report temporaryReport = new Report("UserActions");
        temporaryReport.addColumn("Position");
        temporaryReport.addColumn("ActionType");
        temporaryReport.addColumn("ActionTypeParameters");
        temporaryReport.addColumn("Date");

        Calendar dtFrom = Calendar.getInstance();
        dtFrom.setTime(dateFrom);
        Calendar dtTo = Calendar.getInstance();
        dtTo.setTime(dateTo);
        dtFrom.set(Calendar.SECOND, 0);
        dtFrom.set(Calendar.MINUTE, 0);
        dtFrom.set(Calendar.HOUR, 0);
        dtTo.set(Calendar.SECOND, 59);
        dtTo.set(Calendar.MINUTE, 59);
        dtTo.set(Calendar.HOUR, 23);

        /* Workspace log */

        String rqWorkspaceLog = "SELECT w.action as ActionType, ";
        rqWorkspaceLog += "w.action_parameter as ActionTypeParameters, ";
        rqWorkspaceLog += "w.dm_entity_id as EntityUid, ";
        rqWorkspaceLog += "w.log_time as Date, ";
        rqWorkspaceLog += "e.dm_entity_name as EntityName ";
        rqWorkspaceLog += "FROM authentication_source a, entity_log w ";
        rqWorkspaceLog += "LEFT JOIN dm_entity e ";
        rqWorkspaceLog += "ON w.dm_entity_id = e.dm_entity_id ";
        rqWorkspaceLog += "WHERE a.source_name=w.user_source ";
        rqWorkspaceLog += "AND w.username=:userName ";
        rqWorkspaceLog += "AND w.user_source=:userSource ";
        rqWorkspaceLog += "AND w.log_time >= :dateFrom ";
        rqWorkspaceLog += "AND w.log_time <= :dateTo";

        SQLQuery sqlWorkspaceLog = FactoryInstantiator.getInstance().getDtrFactory().getSession()
                .createSQLQuery(rqWorkspaceLog);
        sqlWorkspaceLog.addScalar("ActionType", IntegerType.INSTANCE);
        sqlWorkspaceLog.addScalar("ActionTypeParameters", StringType.INSTANCE);
        sqlWorkspaceLog.addScalar("EntityUid", LongType.INSTANCE);
        sqlWorkspaceLog.addScalar("Date", StringType.INSTANCE);
        sqlWorkspaceLog.addScalar("EntityName", StringType.INSTANCE);
        sqlWorkspaceLog.setString("userName", user.getUid());
        sqlWorkspaceLog.setString("userSource", user.getAuthenticationSourceName());
        sqlWorkspaceLog.setDate("dateFrom", dtFrom.getTime());
        sqlWorkspaceLog.setDate("dateTo", dtTo.getTime());

        List<Object[]> reportWorkspaceLog = sqlWorkspaceLog.list();
        for (Object[] r : reportWorkspaceLog) {
            Vector<Cell> cells = new Vector<Cell>();
            cells.add(new Cell("ActionType", (Integer) r[0]));
            cells.add(new Cell("ActionTypeParameters", r[1] == null ? "" : (String) r[1]));
            cells.add(new Cell("Date", (String) r[3]));
            cells.add(new Cell("EntityName", r[4] == null ? "" : (String) r[4]));
            cells.add(new Cell("Position", new String("/")));
            temporaryReport.addRow(new Row(cells));
        }

        /* Folder log */

        String rqFolderLog = "SELECT f.action as ActionType, ";
        rqFolderLog += "f.action_parameter as ActionTypeParameters, ";
        rqFolderLog += "f.dm_entity_id as EntityUid, ";
        rqFolderLog += "f.log_time as Date, ";
        rqFolderLog += "e.dm_entity_name as EntityName, ";
        rqFolderLog += "entt.dm_entity_path as Position ";
        rqFolderLog += "FROM authentication_source a, entity_log f ";
        rqFolderLog += "LEFT JOIN dm_entity e ";
        rqFolderLog += "ON f.dm_entity_id = e.dm_entity_id ";
        rqFolderLog += "LEFT JOIN dm_entity entt ";
        rqFolderLog += "ON f.dm_entity_id = entt.dm_entity_id ";
        rqFolderLog += "WHERE a.source_name=f.user_source ";
        rqFolderLog += "AND f.username=:userName ";
        rqFolderLog += "AND f.user_source=:userSource ";
        rqFolderLog += "AND f.log_time >= :dateFrom ";
        rqFolderLog += "AND f.log_time <= :dateTo ";

        SQLQuery sqlFolderLog = FactoryInstantiator.getInstance().getDtrFactory().getSession()
                .createSQLQuery(rqFolderLog);
        sqlFolderLog.addScalar("ActionType", IntegerType.INSTANCE);
        sqlFolderLog.addScalar("ActionTypeParameters", StringType.INSTANCE);
        sqlFolderLog.addScalar("EntityUid", LongType.INSTANCE);
        sqlFolderLog.addScalar("Date", StringType.INSTANCE);
        sqlFolderLog.addScalar("EntityName", StringType.INSTANCE);
        sqlFolderLog.addScalar("Position", StringType.INSTANCE);
        sqlFolderLog.setString("userName", user.getUid());
        sqlFolderLog.setString("userSource", user.getAuthenticationSourceName());
        sqlFolderLog.setDate("dateFrom", dtFrom.getTime());
        sqlFolderLog.setDate("dateTo", dtTo.getTime());

        List<Object[]> reportFolderLog = sqlFolderLog.list();
        for (Object[] r : reportFolderLog) {
            Vector<Cell> cells = new Vector<Cell>();
            cells.add(new Cell("ActionType", (Integer) r[0]));
            cells.add(new Cell("ActionTypeParameters", r[1] == null ? "" : (String) r[1]));
            cells.add(new Cell("Date", (String) r[3]));
            cells.add(new Cell("EntityName", r[4] == null ? "" : (String) r[4]));
            cells.add(new Cell("Position", r[5] == null ? "" : (String) r[5]));
            temporaryReport.addRow(new Row(cells));
        }

        /* Document log */

        String rqDocumentLog = "SELECT d.action as ActionType, ";
        rqDocumentLog += "d.action_parameter as ActionTypeParameters, ";
        rqDocumentLog += "d.dm_entity_id as EntityUid, ";
        rqDocumentLog += "d.log_time as Date, ";
        rqDocumentLog += "entt.dm_entity_name as EntityName, ";
        rqDocumentLog += "entt.dm_entity_path as Position ";
        rqDocumentLog += "FROM authentication_source a, entity_log d ";
        rqDocumentLog += "LEFT JOIN dm_entity entt ";
        rqDocumentLog += "ON d.dm_entity_id = entt.dm_entity_id ";
        rqDocumentLog += "WHERE a.source_name=d.user_source ";
        rqDocumentLog += "AND d.username=:userName ";
        rqDocumentLog += "AND d.user_source=:userSource ";
        rqDocumentLog += "AND d.log_time >= :dateFrom ";
        rqDocumentLog += "AND d.log_time <= :dateTo";

        SQLQuery sqlDocumentLog = FactoryInstantiator.getInstance().getDtrFactory().getSession()
                .createSQLQuery(rqDocumentLog);
        sqlDocumentLog.addScalar("ActionType", IntegerType.INSTANCE);
        sqlDocumentLog.addScalar("ActionTypeParameters", StringType.INSTANCE);
        sqlDocumentLog.addScalar("EntityUid", LongType.INSTANCE);
        sqlDocumentLog.addScalar("Date", StringType.INSTANCE);
        sqlDocumentLog.addScalar("EntityName", StringType.INSTANCE);
        sqlDocumentLog.addScalar("Position", StringType.INSTANCE);
        sqlDocumentLog.setString("userName", user.getUid());
        sqlDocumentLog.setString("userSource", user.getAuthenticationSourceName());
        sqlDocumentLog.setDate("dateFrom", dtFrom.getTime());
        sqlDocumentLog.setDate("dateTo", dtTo.getTime());

        List<Object[]> reportDocumentLog = sqlDocumentLog.list();
        for (Object[] r : reportDocumentLog) {
            Vector<Cell> cells = new Vector<Cell>();
            cells.add(new Cell("ActionType", (Integer) r[0]));
            cells.add(new Cell("ActionTypeParameters", r[1] == null ? "" : (String) r[1]));
            cells.add(new Cell("Date", (String) r[3]));
            cells.add(new Cell("EntityName", r[4] == null ? "" : (String) r[4]));
            cells.add(new Cell("Position", r[5] == null ? "" : (String) r[5]));
            temporaryReport.addRow(new Row(cells));
        }

        /* Create temporary table */

        tmpTable = "tmp_" + sessionUid.substring(0, 8) + "_" + new Date().getTime();
        String rqCreateTable = "CREATE TABLE " + tmpTable + " ( ";
        rqCreateTable += "ReportActionType character varying(2), ";
        rqCreateTable += "ReportActionTypeParameters character varying(255), ";
        rqCreateTable += "ReportEntityName character varying(255), ";
        rqCreateTable += "ReportDate character varying(255), ";
        rqCreateTable += "ReportPosition character varying(255) )";

        SQLQuery sqlCreateTable = FactoryInstantiator.getInstance().getDtrFactory().getSession()
                .createSQLQuery(rqCreateTable);
        sqlCreateTable.executeUpdate();

        for (Row row : temporaryReport.getBody().getRows()) {
            String action = String.valueOf(row.getValue("ActionType"));
            String parameters = String.valueOf(row.getValue("ActionTypeParameters"));
            String entityName = String.valueOf(row.getValue("EntityName"));
            String date = String.valueOf(row.getValue("Date"));
            String position = String.valueOf(row.getValue("Position"));
            String rqInsertTable = "INSERT INTO " + tmpTable + " ( ";
            rqInsertTable += "ReportActionType, ReportActionTypeParameters, ReportEntityName, ReportDate, ReportPosition ";
            rqInsertTable += " ) VALUES (:actionType,:parameters,:entityName,:date,:position)";
            SQLQuery sqlInsertTable = getSession().createSQLQuery(rqInsertTable);
            sqlInsertTable.setString("actionType", action);
            sqlInsertTable.setString("parameters", parameters);
            sqlInsertTable.setString("entityName", entityName);
            sqlInsertTable.setString("date", date);
            sqlInsertTable.setString("position", position);
            sqlInsertTable.executeUpdate();
        }

        /* Report */

        String rq = "SELECT ReportActionType, ReportActionTypeParameters, ReportEntityName, ReportDate, ReportPosition ";
        rq += "FROM " + tmpTable + " ";
        rq += (actionType != null ? " WHERE ReportActionType=:actionType " : " ");
        rq += " ORDER BY " + (order == null ? "ReportDate" : order) + " " + (asc ? "ASC" : "DESC");

        SQLQuery sql = FactoryInstantiator.getInstance().getDtrFactory().getSession().createSQLQuery(rq);
        sql.addScalar("ReportActionType", StringType.INSTANCE);
        sql.addScalar("ReportActionTypeParameters", StringType.INSTANCE);
        sql.addScalar("ReportEntityName", StringType.INSTANCE);
        sql.addScalar("ReportDate", StringType.INSTANCE);
        sql.addScalar("ReportPosition", StringType.INSTANCE);
        if (actionType != null) {
            sql.setString("actionType", actionType);
        }

        Report report = new Report("UserActions");
        report.addColumn("Position");
        report.addColumn("EntityName");
        report.addColumn("ActionType");
        report.addColumn("ActionTypeParameters");
        report.addColumn("Date");

        List<Object[]> reports = sql.list();
        for (Object[] r : reports) {
            Vector<Cell> cells = new Vector<Cell>();
            cells.add(new Cell("ActionType", (String) r[0]));
            cells.add(new Cell("ActionTypeParameters", (String) r[1]));
            cells.add(new Cell("EntityName", (String) r[2]));
            cells.add(new Cell("Date", (String) r[3]));

            String pos = ((String) r[4]);
            int index = pos.lastIndexOf('/');
            if (index != -1 && !"/".equals(pos)) {
                pos = pos.substring(0, index);
            }
            cells.add(new Cell("Position", pos));
            report.addRow(new Row(cells));
        }
        return report.toXML();
    } catch (HibernateException he) {
        he.printStackTrace();
        throw he;
    } finally {
        /* Drop temporary table */
        FactoryInstantiator.getInstance().getDtrFactory().getSession().createSQLQuery("DROP TABLE " + tmpTable)
                .executeUpdate();
    }
}

From source file:org.openmrs.module.amrsreports.db.hibernate.MohHibernateCoreDAO.java

License:Open Source License

@Override
public List<Object> executeSqlQuery(String query, Map<String, Object> substitutions) {
    SQLQuery q = sessionFactory.getCurrentSession().createSQLQuery(query);

    for (Map.Entry<String, Object> e : substitutions.entrySet()) {
        if (e.getValue() instanceof Collection) {
            q.setParameterList(e.getKey(), (Collection) e.getValue());
        } else if (e.getValue() instanceof Object[]) {
            q.setParameterList(e.getKey(), (Object[]) e.getValue());
        } else if (e.getValue() instanceof Cohort) {
            q.setParameterList(e.getKey(), ((Cohort) e.getValue()).getMemberIds());
        } else if (e.getValue() instanceof Date) {
            q.setDate(e.getKey(), (Date) e.getValue());
        } else {//from  ww  w  .j a  v a 2 s. co m
            q.setParameter(e.getKey(), e.getValue());
        }

    }

    q.setReadOnly(true);

    List<Object> r = q.list();

    return r;
}

From source file:org.openmrs.module.encounteraudit.api.db.hibernate.HibernateEncounterAuditDAO.java

License:Open Source License

@Override
public List<Encounter> getAuditEncounters(Date fromDate, Date toDate, int sampleSize, Location location,
        EncounterType encounterType, String creatorId) {

    if (sampleSize < 1) {
        // by default return 25 records
        sampleSize = 25;/*  w w w.  j  av a  2  s  .co  m*/
    }

    StringBuilder sql = new StringBuilder("select * from encounter e where ");
    sql.append(" encounter_datetime > :fromDate and ");
    sql.append(" encounter_datetime < :toDate ");
    if (location != null) {
        sql.append(" and location_id = :locationId ");
    }
    if (encounterType != null) {
        sql.append(" and encounter_type = :encounterType ");
    }
    if (creatorId.length() != 0) {
        sql.append(" and creator = :creatorId and changed_by IS NULL ");
    }
    sql.append(" order by rand() ");
    sql.append("limit 0,:sampleSize ");

    SQLQuery query = sessionFactory.getCurrentSession().createSQLQuery(sql.toString())
            .addEntity(Encounter.class);
    query.setDate("fromDate", fromDate);
    query.setDate("toDate", toDate);
    query.setInteger("sampleSize", new Integer(sampleSize));

    if (location != null) {
        query.setInteger("locationId", new Integer(location.getLocationId()));
    }
    if (encounterType != null) {
        query.setInteger("encounterType", new Integer(encounterType.getEncounterTypeId()));
    }
    if (creatorId.length() != 0) {
        query.setString("creatorId", creatorId);
    }

    List<Encounter> encounterList = query.list();

    return encounterList;
}

From source file:org.openmrs.module.orderextension.api.db.HibernateOrderExtensionDAO.java

License:Open Source License

/**
  * @see org.openmrs.module.orderextension.api.db.OrderExtensionDAO#getMaxNumberOfCyclesForRegimen(org.openmrs.module.orderextension.DrugRegimen)
  *//*from  ww  w .j ava 2 s  . c om*/
@Override
public Integer getMaxNumberOfCyclesForRegimen(Patient patient, DrugRegimen regimen) {

    SQLQuery query = sessionFactory.getCurrentSession().createSQLQuery(
            "select MAX(og.cycle_number) from orderextension_order_group og, orderextension_order er, orders o where og.id = er.group_id and er.order_id = o.order_id and o.voided = 0 and og.voided = 0 and og.order_set_id = :orderSetId and o.patient_id = :patientId and o.start_date >= :startDate");
    query.setInteger("patientId", patient.getId());
    query.setInteger("orderSetId", regimen.getOrderSet().getId());
    query.setDate("startDate", regimen.getFirstDrugOrderStartDate());

    return (Integer) query.uniqueResult();
}

From source file:org.openxdata.server.dao.hibernate.HibernateEditableDAO.java

License:Apache License

@Override
@SuppressWarnings("unchecked")
@Secured("Perm_View_Form_Data")
public List<FormDataHeader> getFormData(Integer formDefId, Integer userId, Date fromDate, Date toDate) {
    Session session = getSession();/*  ww w .  j a  v a2 s . c o m*/

    String sql = "select d.form_data_id,d.form_definition_version_id, "
            + "fd.name as formName, fdv.name as versionName, u.user_name as creator, "
            + "d.date_created, u2.user_name as changed_by, d.date_changed,d.description "
            + "from form_data d inner join users u on u.user_id=d.creator "
            + "inner join form_definition_version fdv on fdv.form_definition_version_id=d.form_definition_version_id "
            + "inner join form_definition fd on fd.form_definition_id=fdv.form_definition_id "
            + "left join users u2 on u2.user_id=d.changed_by ";

    String filter = "";
    if (formDefId != null) {
        filter += " d.form_definition_version_id = :formDefId";
    }
    if (userId != null) {
        if (!filter.equals(""))
            filter += " and";
        filter += " d.creator = :userId";
    }
    if (fromDate != null) {
        if (!filter.equals(""))
            filter += " and";
        filter += " d.date_created >= :fromDate";
    }
    if (toDate != null) {
        if (!filter.equals(""))
            filter += " and";
        filter += " d.date_created <= :toDate";
    }
    if (!filter.equals("")) {
        filter = "where " + filter;
        sql += filter;
    }

    sql += " order by d.date_changed desc, d.date_created desc";

    SQLQuery query = session.createSQLQuery(sql);
    query.addEntity(FormDataHeader.class);
    if (formDefId != null) {
        query.setInteger("formDefId", formDefId);
    }
    if (userId != null) {
        query.setInteger("userId", userId);
    }
    if (fromDate != null) {
        query.setDate("fromDate", fromDate);
    }
    if (toDate != null) {
        query.setDate("toDate", toDate);
    }

    List<FormDataHeader> items = query.list();

    return items;
}

From source file:simtra.simtraadmin.dao.ReporteDao.java

public List<String> delitosPorFecha(Date fechaInicio, Date fechaFin) {
    sesionActual();/* w ww  .j a  v  a2 s  .co m*/
    StringBuilder sql = new StringBuilder();
    sql.append(
            "SELECT CONCAT(COUNT(incidencia.incTipo),';',incidencia.incTipo,';',TO_CHAR(reporte.repFecha,'yyyy-mm-dd')) ");
    sql.append("FROM incidencia INNER JOIN reporte ON incidencia.incId = reporte.incId ");
    sql.append("WHERE incidencia.incNombre  = 'Delito, Emergencia y Trafico' ");
    sql.append("AND incidencia.incTipo IN ('Robo a mano armada', 'Robo con arma blanca', 'Pelea a bordo') ");
    sql.append("AND reporte.repfecha between :fechaInicio and :fechaFin  ");
    sql.append("GROUP BY incidencia.incTipo,TO_CHAR(reporte.repFecha,'yyyy-mm-dd') ");
    sql.append(
            "ORDER BY incidencia.incTipo,COUNT(incidencia.incTipo) DESC,TO_CHAR(reporte.repFecha,'yyyy-mm-dd') ");
    SQLQuery query = sesion.createSQLQuery(sql.toString());
    query.setDate("fechaInicio", fechaInicio);
    query.setDate("fechaFin", fechaFin);
    List<String> objeto = (List<String>) query.list();
    return objeto;
}

From source file:simtra.simtraadmin.dao.ReporteDao.java

public List<String> emergenciasPorFecha(Date fechaInicio, Date fechaFin) {
    sesionActual();/*from w w  w.  j  a  v  a2  s  .c om*/
    StringBuilder sql = new StringBuilder();
    sql.append(
            "SELECT CONCAT(COUNT(incidencia.incTipo),';',incidencia.incTipo,';',TO_CHAR(reporte.repFecha,'yyyy-mm-dd')) ");
    sql.append("FROM incidencia INNER JOIN reporte ON incidencia.incId = reporte.incId ");
    sql.append("WHERE incidencia.incNombre  = 'Delito, Emergencia y Trafico' ");
    sql.append(
            "AND incidencia.incTipo IN ('Protesta', 'Siniestro', 'Peligro de incendio', 'Exceso de velocidad', 'Desmayo') ");
    sql.append("AND reporte.repfecha between :fechaInicio and :fechaFin  ");
    sql.append("GROUP BY incidencia.incTipo,TO_CHAR(reporte.repFecha,'yyyy-mm-dd') ");
    sql.append(
            "ORDER BY incidencia.incTipo,COUNT(incidencia.incTipo) DESC,TO_CHAR(reporte.repFecha,'yyyy-mm-dd') ");
    SQLQuery query = sesion.createSQLQuery(sql.toString());
    query.setDate("fechaInicio", fechaInicio);
    query.setDate("fechaFin", fechaFin);
    List<String> objeto = (List<String>) query.list();
    return objeto;
}

From source file:simtra.simtraadmin.dao.ReporteDao.java

public List<String> delitosPorRutaFecha(Integer idRuta, Date fechaInicio, Date fechaFin) {
    sesionActual();/*from w  w w .j a  va2  s . c  o m*/
    System.out.println(idRuta);
    StringBuilder sql = new StringBuilder();
    sql.append(
            "SELECT CONCAT(COUNT(incidencia.incTipo),';',incidencia.incTipo,';',TO_CHAR(reporte.repFecha,'yyyy-mm-dd')) ");
    sql.append("FROM incidencia INNER JOIN reporte ON incidencia.incId = reporte.incId ");
    sql.append("WHERE incidencia.incNombre = 'Delito, Emergencia y Trafico' ");
    sql.append("AND incidencia.incTipo IN ('Robo a mano armada', 'Robo con arma blanca', 'Pelea a bordo') ");
    sql.append("AND incidencia.incObservaciones LIKE CONCAT('%(',:idRuta,')%')");
    sql.append("AND reporte.repfecha between :fechaInicio AND :fechaFin  ");
    sql.append("GROUP BY incidencia.incTipo, TO_CHAR(reporte.repFecha,'yyyy-mm-dd') ");
    sql.append(
            "ORDER BY incidencia.incTipo,TO_CHAR(reporte.repFecha,'yyyy-mm-dd'), COUNT(incidencia.incTipo) DESC ");
    SQLQuery query = sesion.createSQLQuery(sql.toString());
    query.setString("idRuta", idRuta.toString());
    query.setDate("fechaInicio", fechaInicio);
    query.setDate("fechaFin", fechaFin);
    List<String> objeto = (List<String>) query.list();
    return objeto;
}