List of usage examples for org.hibernate SQLQuery setDate
@Deprecated @SuppressWarnings("unchecked") default Query<R> setDate(int position, Date val)
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; }