Java tutorial
/** * The contents of this file are subject to the OpenMRS Public License Version * 1.0 (the "License"); you may not use this file except in compliance with the * License. You may obtain a copy of the License at http://license.openmrs.org * * Software distributed under the License is distributed on an "AS IS" basis, * WITHOUT WARRANTY OF ANY KIND, either express or implied. See the License for * the specific language governing rights and limitations under the License. * * Copyright (C) OpenMRS, LLC. All Rights Reserved. */ package org.openmrs.module.usagestatistics.api.db.hibernate; import java.math.BigInteger; import java.sql.Connection; import java.sql.SQLException; import java.sql.Statement; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Date; import java.util.List; import org.apache.commons.logging.Log; import org.apache.commons.logging.LogFactory; import org.hibernate.Criteria; import org.hibernate.Hibernate; import org.hibernate.SQLQuery; import org.hibernate.Session; import org.hibernate.SessionFactory; import org.hibernate.criterion.Restrictions; import org.openmrs.Encounter; import org.openmrs.Order; import org.openmrs.Patient; import org.openmrs.User; import org.openmrs.Visit; import org.openmrs.api.db.DAOException; import org.openmrs.module.usagestatistics.ActionCriteria; import org.openmrs.module.usagestatistics.Constants; import org.openmrs.module.usagestatistics.pojo.Usage; //import org.openmrs.module.usagestatistics.Options; //import org.openmrs.module.usagestatistics.ActionCriteria; import org.openmrs.module.usagestatistics.api.db.UsageStatisticsDAO; import org.openmrs.module.usagestatistics.pojo.EncounterUsage; import org.openmrs.module.usagestatistics.pojo.OrderUsage; import org.openmrs.module.usagestatistics.pojo.PatientUsage; import org.openmrs.module.usagestatistics.pojo.VisitUsage; import org.openmrs.module.usagestatistics.resultpojo.LogDetailUsage; import org.openmrs.module.usagestatistics.util.PagingInfo; //import org.openmrs.module.usagestatistics.util.PagingInfo; import org.openmrs.module.usagestatistics.util.StatsUtils; /** * Hibernate data access layer */ /** * * @author kavya */ public class HibernateUsageStatisticsDAO implements UsageStatisticsDAO { protected static final Log log = LogFactory.getLog(HibernateUsageStatisticsDAO.class); protected SessionFactory sessionFactory; protected static final String TABLE_USAGE = Constants.MODULE_ID + "_usage"; protected static final String TABLE_PATIENT = Constants.MODULE_ID + "_patient"; protected static final String TABLE_ENCOUNTER = Constants.MODULE_ID + "_encounter"; protected static final String TABLE_VISIT = Constants.MODULE_ID + "_visit"; protected static final String TABLE_ORDER = Constants.MODULE_ID + "_order"; protected static final String TABLE_AGGREGATED = Constants.MODULE_ID + "_aggregated"; protected static final SimpleDateFormat dfSQL = new SimpleDateFormat("yyyy-MM-dd"); /** * Set session factory * * @param sessionFactory */ public void setSessionFactory(SessionFactory sessionFactory) { this.sessionFactory = sessionFactory; } /** * @see * org.openmrs.module.usagestatistics.db.UsageStatisticsDAO#saveUsage(Usage) */ public void saveUsage(Usage usage) throws DAOException { /*User user = new User(1); Patient patient = new Patient(4); Usage usage2 = new Usage(user, patient); Session currentSession = sessionFactory.getCurrentSession(); currentSession.saveOrUpdate(usage2);*/ //currentSession.getTransaction().commit(); sessionFactory.getCurrentSession().saveOrUpdate(usage); } public void saveEncounterUsage(EncounterUsage encounterUsage) throws DAOException { sessionFactory.getCurrentSession().saveOrUpdate(encounterUsage); } public void saveOrderUsage(OrderUsage orderUsage) throws DAOException { sessionFactory.getCurrentSession().saveOrUpdate(orderUsage); } public void savePatientUsage(PatientUsage patientUsage) throws DAOException { sessionFactory.getCurrentSession().saveOrUpdate(patientUsage); } public void saveVisitUsage(VisitUsage visitUsage) throws DAOException { sessionFactory.getCurrentSession().saveOrUpdate(visitUsage); } /** * @see * org.openmrs.module.usagestatistics.db.UsageStatisticsDAO#getLastUsage(User, * Patient, int) */ @SuppressWarnings("unchecked") public Usage getLastUsage(User user, Patient patient, int maxAgeSecs) throws DAOException { Criteria query = sessionFactory.getCurrentSession().createCriteria(Usage.class); if (user != null) { query.add(Restrictions.eq("user", user)); } if (patient != null) { query.add(Restrictions.eq("patient", patient)); } if (maxAgeSecs != 0) { Date from = StatsUtils.addSecondsToDate(null, -maxAgeSecs); query.add(Restrictions.ge("timestamp", from)); } List<Usage> events = query.addOrder(org.hibernate.criterion.Order.desc("timestamp")).setMaxResults(1) .list(); return events.size() == 1 ? events.get(0) : null; } public PatientUsage getPatientUsage(Usage usage) throws DAOException { Criteria query = sessionFactory.getCurrentSession().createCriteria(PatientUsage.class); if (usage != null) { query.add(Restrictions.eq("usage", usage)); } List<PatientUsage> events = query.list(); return events.size() == 1 ? events.get(0) : null; } public EncounterUsage getEncounterUsage(Usage usage, Encounter encounter) throws DAOException { Criteria query = sessionFactory.getCurrentSession().createCriteria(EncounterUsage.class); if (usage != null) { query.add(Restrictions.eq("usage", usage)); } if (encounter != null) { query.add(Restrictions.eq("encounter", encounter)); } List<EncounterUsage> events = query.list(); return events.size() == 1 ? events.get(0) : null; } public OrderUsage getOrderUsage(Usage usage, Order order) throws DAOException { Criteria query = sessionFactory.getCurrentSession().createCriteria(OrderUsage.class); if (usage != null) { query.add(Restrictions.eq("usage", usage)); } if (order != null) { query.add(Restrictions.eq("order", order)); } List<OrderUsage> events = query.list(); return events.size() == 1 ? events.get(0) : null; } public VisitUsage getVisitUsage(Usage usage, Visit visit) throws DAOException { Criteria query = sessionFactory.getCurrentSession().createCriteria(VisitUsage.class); if (usage != null) { query.add(Restrictions.eq("usage", usage)); } if (visit != null) { query.add(Restrictions.eq("visit", visit)); } List<VisitUsage> events = query.list(); return events.size() == 1 ? events.get(0) : null; } public List<EncounterUsage> getEncounterUsages(Usage usage) throws DAOException { Criteria query = sessionFactory.getCurrentSession().createCriteria(EncounterUsage.class); if (usage != null) { query.add(Restrictions.eq("usage", usage)); } List<EncounterUsage> events = query.list(); return events; } public List<OrderUsage> getOrderUsages(Usage usage) throws DAOException { Criteria query = sessionFactory.getCurrentSession().createCriteria(OrderUsage.class); if (usage != null) { query.add(Restrictions.eq("usage", usage)); } List<OrderUsage> events = query.list(); return events; } public List<VisitUsage> getVisitUsages(Usage usage) throws DAOException { Criteria query = sessionFactory.getCurrentSession().createCriteria(VisitUsage.class); if (usage != null) { query.add(Restrictions.eq("usage", usage)); } List<VisitUsage> events = query.list(); return events; } /** * @see * org.openmrs.module.usagestatistics.db.UsageStatisticsDAO#isPatientVoidedInDatabase(org.openmrs.Patient) */ public boolean isPatientVoidedInDatabase(Patient patient) throws DAOException { String sql = "SELECT voided FROM patient WHERE patient_id = " + patient.getPatientId() + ";"; Object result = sessionFactory.getCurrentSession().createSQLQuery(sql).uniqueResult(); return (result instanceof Number) ? (((Number) result).intValue() == 1) : (Boolean) result; } /** * Utility method to execute a native SQL query in the current Hibernate * session * * @param sql the native SQL query to execute * @return a list of object arrays for each row */ @SuppressWarnings("unchecked") protected List<Object[]> executeSQLQuery(String sql) { Session session = sessionFactory.getCurrentSession(); SQLQuery query = session.createSQLQuery(sql); return query.list(); } /** * Utility method to execute a native SQL query in JDBC * * @param sql the native SQL query to execute * @return a list of object arrays for each row */ protected int executeJDBCUpdate(String sql) { Connection conn = sessionFactory.getCurrentSession().connection(); try { Statement ps = conn.createStatement(); return ps.executeUpdate(sql); } catch (SQLException ex) { throw new DAOException(ex); } } // For Debugging @SuppressWarnings("unchecked") public Usage getUsageById(int id) throws DAOException { Criteria query = sessionFactory.getCurrentSession().createCriteria(Usage.class); //System.out.println("all ="+query.list().toString()); query.add(Restrictions.eq("usageId", id)); //System.out.println("result ="+query.list().toString()); List<Usage> events = query.list(); return events.size() == 1 ? events.get(0) : null; } // For Debugging @SuppressWarnings("unchecked") public List<Usage> getAllUsages() throws DAOException { Criteria query = sessionFactory.getCurrentSession().createCriteria(Usage.class); List<Usage> events = query.list(); return events; } // For Debugging @SuppressWarnings("unchecked") public List<PatientUsage> getAllPatientUsages() throws DAOException { Criteria query = sessionFactory.getCurrentSession().createCriteria(PatientUsage.class); List<PatientUsage> events = query.list(); return events; } // For Debugging @SuppressWarnings("unchecked") public List<EncounterUsage> getAllEncounterUsages() throws DAOException { Criteria query = sessionFactory.getCurrentSession().createCriteria(EncounterUsage.class); List<EncounterUsage> events = query.list(); return events; } // For Debugging @SuppressWarnings("unchecked") public List<VisitUsage> getAllVisitUsages() throws DAOException { Criteria query = sessionFactory.getCurrentSession().createCriteria(VisitUsage.class); List<VisitUsage> events = query.list(); return events; } // For Debugging @SuppressWarnings("unchecked") public List<OrderUsage> getAllOrderUsages() throws DAOException { Criteria query = sessionFactory.getCurrentSession().createCriteria(OrderUsage.class); List<OrderUsage> events = query.list(); return events; } //updates the usagestatistics_aggregated table public int aggregateUsages() throws DAOException { Session session = sessionFactory.getCurrentSession(); // Get last date in aggregate table String sql = "SELECT MAX(DATE(timestamp)) FROM " + TABLE_AGGREGATED + ";"; Date lastDate = (Date) session.createSQLQuery(sql).uniqueResult(); // Delete all data from that last date as it may be incomplete if (lastDate != null) { sql = "DELETE FROM " + TABLE_AGGREGATED + " WHERE DATE(timestamp) = '" + dfSQL.format(lastDate) + "';"; session.createSQLQuery(sql).executeUpdate(); } StringBuffer sb = new StringBuffer(); sb.append("INSERT INTO " + TABLE_AGGREGATED + " SELECT "); sb.append(" u.timestamp) as `timestamp`, "); sb.append(" u.user_id, "); sb.append(" COUNT(up.created) as `patient_views`, "); sb.append(" SUM(up.created) as `patient_creates`, "); sb.append(" SUM(up.updated) as `patient_updates`, "); sb.append(" SUM(up.voided) as `patient_voids`, "); sb.append(" SUM(ue.viewed) as `encounter_views`, "); sb.append(" SUM(ue.created) as `encounter_creates`, "); sb.append(" SUM(ue.updated) as `encounter_updates`, "); sb.append(" SUM(ue.voided) as `encounter_voids`, "); sb.append(" SUM(uv.viewed) as `visit_views`, "); sb.append(" SUM(uv.created) as `visit_creates`, "); sb.append(" SUM(uv.updated) as `visit_updates`, "); sb.append(" SUM(uv.voided) as `visit_voids`, "); sb.append(" SUM(uo.viewed) as `order_views`, "); sb.append(" SUM(uo.created) as `order_creates`, "); sb.append(" SUM(uo.updated) as `order_updates`, "); sb.append(" SUM(uo.voided) as `order_voids` "); sb.append("FROM " + TABLE_USAGE + " u "); sb.append("LEFT OUTER JOIN users us ON u.user_id = us.user_id "); sb.append("LEFT OUTER JOIN " + TABLE_PATIENT + " up ON up.usage_id = u.usage_id "); sb.append("LEFT OUTER JOIN " + TABLE_ENCOUNTER + " ue ON ue.usage_id = u.usage_id "); sb.append("LEFT OUTER JOIN " + TABLE_VISIT + " uv ON uv.usage_id = u.usage_id "); sb.append("LEFT OUTER JOIN " + TABLE_ORDER + " uo ON uo.usage_id = u.usage_id "); if (lastDate != null) { sb.append("WHERE DATE(u.timestamp) >= '" + dfSQL.format(lastDate) + "' "); } sb.append("GROUP BY DATE(u.timestamp), u.user_id;"); return executeJDBCUpdate(sb.toString()); } public int deleteUsages(Date until) throws DAOException { String sql = "DELETE FROM " + TABLE_USAGE + " WHERE timestamp < '" + dfSQL.format(until) + "';"; SQLQuery query = sessionFactory.getCurrentSession().createSQLQuery(sql); return query.executeUpdate(); } public int getUsageCount() throws DAOException { Session session = sessionFactory.getCurrentSession(); Number res = (Number) session.createSQLQuery("SELECT COUNT(*) FROM " + TABLE_USAGE + ";").uniqueResult(); return res.intValue(); } //updates the records being viewed field on the summary page public int getRecordsAccessedCount(Date from) throws DAOException { Session session = sessionFactory.getCurrentSession(); String sql = "SELECT COUNT(DISTINCT patient_id) FROM " + TABLE_USAGE + " WHERE timestamp >= FROM_UNIXTIME(" + (from.getTime() / 1000) + ");"; Number res = (Number) session.createSQLQuery(sql).uniqueResult(); return res.intValue(); } //updates the most active user for patient field on the summary page public List<Object[]> getMostActiveUsersPatient(Date since, int maxResults) throws DAOException { StringBuffer sb = new StringBuffer(); sb.append("SELECT "); //sb.append(" ud.user_id, "); sb.append( " CONCAT(IFNULL(n.given_name,''), ' ', IFNULL(n.middle_name,''), ' ', IFNULL(n.family_name,'')) AS user_name "); //sb.append(" ud.patient_views "); sb.append("FROM ( "); sb.append(" SELECT user_id, SUM(patient_views) as patient_views "); sb.append(" FROM " + TABLE_AGGREGATED + " "); if (since != null) { sb.append(" WHERE `timestamp` > '" + dfSQL.format(since) + "' "); } sb.append(" GROUP BY user_id "); sb.append(") ud "); sb.append("INNER JOIN users u ON ud.user_id = u.user_id "); sb.append("INNER JOIN person_name n ON n.person_id = u.person_id "); sb.append("ORDER BY patient_views DESC "); sb.append("LIMIT " + maxResults + ";"); return executeSQLQuery(sb.toString()); } //updates the most active user for encounter field on the summary page public List<Object[]> getMostActiveUsersEncounter(Date since, int maxResults) throws DAOException { StringBuffer sb = new StringBuffer(); sb.append("SELECT "); //sb.append(" ud.user_id, "); sb.append( " CONCAT(IFNULL(n.given_name,''), ' ', IFNULL(n.middle_name,''), ' ', IFNULL(n.family_name,'')) AS user_name "); //sb.append(" ud.encounter_views "); sb.append("FROM ( "); sb.append(" SELECT user_id, SUM(encounter_views) as encounter_views "); sb.append(" FROM " + TABLE_AGGREGATED + " "); if (since != null) { sb.append(" WHERE `timestamp` > '" + dfSQL.format(since) + "' "); } sb.append(" GROUP BY user_id "); sb.append(") ud "); sb.append("INNER JOIN users u ON ud.user_id = u.user_id "); sb.append("INNER JOIN person_name n ON n.person_id = u.person_id "); sb.append("ORDER BY encounter_views DESC "); sb.append("LIMIT " + maxResults + ";"); return executeSQLQuery(sb.toString()); } //updates the most active user for visit field on the summary page public List<Object[]> getMostActiveUsersVisit(Date since, int maxResults) throws DAOException { StringBuffer sb = new StringBuffer(); sb.append("SELECT "); //sb.append(" ud.user_id, "); sb.append( " CONCAT(IFNULL(n.given_name,''), ' ', IFNULL(n.middle_name,''), ' ', IFNULL(n.family_name,'')) AS user_name "); //sb.append(" ud.visit_views "); sb.append("FROM ( "); sb.append(" SELECT user_id, SUM(visit_views) as visit_views "); sb.append(" FROM " + TABLE_AGGREGATED + " "); if (since != null) { sb.append(" WHERE `timestamp` > '" + dfSQL.format(since) + "' "); } sb.append(" GROUP BY user_id "); sb.append(") ud "); sb.append("INNER JOIN users u ON ud.user_id = u.user_id "); sb.append("INNER JOIN person_name n ON n.person_id = u.person_id "); sb.append("ORDER BY visit_views DESC "); sb.append("LIMIT " + maxResults + ";"); return executeSQLQuery(sb.toString()); } //updates the most active user for order field on the summary page public List<Object[]> getMostActiveUsersOrder(Date since, int maxResults) throws DAOException { StringBuffer sb = new StringBuffer(); sb.append("SELECT "); //sb.append(" ud.user_id, "); sb.append( " CONCAT(IFNULL(n.given_name,''), ' ', IFNULL(n.middle_name,''), ' ', IFNULL(n.family_name,'')) AS user_name "); //sb.append(" ud.order_views "); sb.append("FROM ( "); sb.append(" SELECT user_id, SUM(order_views) as order_views "); sb.append(" FROM " + TABLE_AGGREGATED + " "); if (since != null) { sb.append(" WHERE `timestamp` > '" + dfSQL.format(since) + "' "); } sb.append(" GROUP BY user_id "); sb.append(") ud "); sb.append("INNER JOIN users u ON ud.user_id = u.user_id "); sb.append("INNER JOIN person_name n ON n.person_id = u.person_id "); sb.append("ORDER BY order_views DESC "); sb.append("LIMIT " + maxResults + ";"); return executeSQLQuery(sb.toString()); } //update the chart on the summary page public List<Object[]> getDateRangeStats(Date from, Date until, String filter) throws DAOException { StringBuffer sb = new StringBuffer(); sb.append("SELECT "); sb.append(" DATE(timestamp) as `date`, "); sb.append(" SUM(" + filter + "_views) as views, "); sb.append(" SUM(" + filter + "_creates) as creates, "); sb.append(" SUM(" + filter + "_updates) as updates, "); sb.append(" SUM(" + filter + "_voids) as voids "); sb.append("FROM " + TABLE_AGGREGATED + " "); sb.append("WHERE 1=1 "); if (from != null) { sb.append(" AND date > '" + dfSQL.format(from) + "' "); } if (until != null) { sb.append(" AND date < '" + dfSQL.format(until) + "' "); } sb.append("GROUP BY `date` "); sb.append("ORDER BY `date` ASC;"); return executeSQLQuery(sb.toString()); } //updates the chart on the hourly and day of week pages public List<Object[]> getTimeBasedTotals(Date from, Date until, String filter, String func) { StringBuffer sb = new StringBuffer(); sb.append("SELECT "); sb.append(" " + func + "(`timestamp`) as ` " + func + " `, "); sb.append(" SUM(" + filter + "_views) as views, "); sb.append(" SUM(" + filter + "_creates) as creates, "); sb.append(" SUM(" + filter + "_updates) as updates, "); sb.append(" SUM(" + filter + "_voids) as voids "); sb.append("FROM " + TABLE_AGGREGATED + " "); sb.append("WHERE 1=1 "); if (from != null) { sb.append(" AND " + "timestamp >= '" + dfSQL.format(from) + "' "); } if (until != null) { sb.append(" AND " + "timestamp < '" + dfSQL.format(until) + "' "); } sb.append("GROUP BY " + func + "(`timestamp`) "); sb.append("ORDER BY " + func + "(`timestamp`); "); return executeSQLQuery(sb.toString()); } private void appendDateRange(StringBuffer sb, String alias, Date from, Date until) { if (from != null) { sb.append(" AND " + alias + ".timestamp >= '" + dfSQL.format(from) + "' "); } if (until != null) { sb.append(" AND " + alias + ".timestamp < '" + dfSQL.format(until) + "' "); } } private List<Object[]> completeAggregateQuery(StringBuffer sb, Date from, Date until, String groupBy, String filter, String orderBy) { if (from != null) { sb.append(" WHERE DATE(d.timestamp) >= '" + dfSQL.format(from) + "' "); } if (until != null) { sb.append(" AND DATE(d.timestamp) < '" + dfSQL.format(until) + "' "); } sb.append("GROUP BY " + groupBy + " "); if (filter.equalsIgnoreCase("Patient")) { sb.append( "HAVING (SUM(d.patient_views > 0) || SUM(d.patient_creates > 0) || SUM(d.patient_updates > 0) || SUM(d.patient_voids > 0)) "); } else if (filter.equalsIgnoreCase("Encounter")) { sb.append( "HAVING (SUM(d.encounter_views > 0) || SUM(d.encounter_creates > 0) || SUM(d.encounter_updates > 0) || SUM(d.encounter_voids > 0)) "); } else if (filter.equalsIgnoreCase("Visit")) { sb.append( "HAVING (SUM(d.visit_views > 0) || SUM(d.visit_creates > 0) || SUM(d.visit_updates > 0) || SUM(d.visit_voids > 0)) "); } else if (filter.equalsIgnoreCase("Order")) { sb.append( "HAVING (SUM(d.order_views > 0) || SUM(d.order_creates > 0) || SUM(d.order_updates > 0) || SUM(d.order_voids > 0)) "); } sb.append("ORDER BY " + orderBy + ";"); return executeSQLQuery(sb.toString()); } //updates the table on the roles page public List<Object[]> getRolesStats(Date from, Date until, String filter) throws DAOException { StringBuffer sb = new StringBuffer(); sb.append("SELECT r.role, "); sb.append(" COUNT(DISTINCT d.user_id) as `active_users`, "); sb.append(" SUM(d." + filter + "_views) as views, "); sb.append(" SUM(d." + filter + "_creates) as creates, "); sb.append(" SUM(d." + filter + "_updates) as updates, "); sb.append(" SUM(d." + filter + "_voids) as voids, "); sb.append(" MAX(DATE(d.timestamp)) as `last_usage` "); sb.append("FROM role r "); sb.append("LEFT OUTER JOIN user_role ur ON ur.role = r.role "); sb.append("LEFT OUTER JOIN " + TABLE_AGGREGATED + " d ON d.user_id = ur.user_id "); return completeAggregateQuery(sb, from, until, "r.role", filter, "r.role"); } //updates the table on the users page public List<Object[]> getUsersStats(Date from, Date until, String role, String filter) throws DAOException { StringBuffer sb = new StringBuffer(); sb.append("SELECT "); sb.append(" u.user_id, "); sb.append( " CONCAT(IFNULL(n.given_name,''), ' ', IFNULL(n.middle_name,''), ' ', IFNULL(n.family_name,'')) AS user_name, "); sb.append(" SUM(d." + filter + "_views) as views, "); sb.append(" SUM(d." + filter + "_creates) as creates, "); sb.append(" SUM(d." + filter + "_updates) as updates, "); sb.append(" SUM(d." + filter + "_voids) as voids, "); sb.append(" MAX(DATE(d.timestamp)) as `last_usage` "); sb.append("FROM users u "); sb.append("INNER JOIN person_name n ON n.person_id = u.person_id "); if (role != null) { sb.append("INNER JOIN user_role ur ON ur.user_id = u.user_id AND ur.role = '" + role + "' "); } sb.append("LEFT OUTER JOIN " + TABLE_AGGREGATED + " d ON d.user_id = u.user_id "); return completeAggregateQuery(sb, from, until, "u.user_id", filter, "user_name"); } //updates the searches page public int[] getFoundByTotals(Date from, Date until, String filter) throws DAOException { int[] totals = new int[4]; totals[1] = getFoundByTotal(from, until, filter, Usage.FOUNDBY_LINK); totals[2] = getFoundByTotal(from, until, filter, Usage.FOUNDBY_ID_QUERY); totals[3] = getFoundByTotal(from, until, filter, Usage.FOUNDBY_NAME_QUERY); totals[0] = (totals[1] + totals[2] + totals[3]); return totals; } protected int getFoundByTotal(Date from, Date until, String filter, int foundBy) { StringBuffer sb = new StringBuffer(); sb.append("SELECT COUNT(*) "); sb.append("FROM " + TABLE_PATIENT + " e "); sb.append("INNER JOIN usagestatistics_usage u ON e.usage_id = u.usage_id "); sb.append("WHERE e.found_by = " + foundBy + " "); if (filter.equalsIgnoreCase("Created")) { sb.append("AND e.created = 1 "); } else if (filter.equalsIgnoreCase("Updated")) { sb.append("AND e.updated = 1 "); } else if (filter.equalsIgnoreCase("Voided")) { sb.append("AND e.voided = 1 "); } else if (filter.equalsIgnoreCase("Encounter")) { sb.append("AND e.usage_id IN (SELECT usage_id FROM " + TABLE_ENCOUNTER + ") "); } else if (filter.equalsIgnoreCase("Visit")) { sb.append("AND e.usage_id IN (SELECT usage_id FROM " + TABLE_VISIT + ") "); } else if (filter.equalsIgnoreCase("Order")) { sb.append("AND e.usage_id IN (SELECT usage_id FROM " + TABLE_ORDER + ") "); } appendDateRange(sb, "u", from, until); SQLQuery query = sessionFactory.getCurrentSession().createSQLQuery(sb.toString()); return ((BigInteger) query.uniqueResult()).intValue(); } public List<Usage> getUsages(User user, Patient patient, Date from, Date until, String filter, PagingInfo paging) throws DAOException { StringBuffer sb = new StringBuffer(); sb.append("SELECT SQL_CALC_FOUND_ROWS {s.*} "); sb.append("FROM " + TABLE_USAGE + " s "); //sb.append("INNER JOIN " + TABLE_PATIENT + " p ON p.usage_id = s.usage_id"); sb.append("WHERE 1=1 "); if (user != null) { sb.append(" AND s.user_id = " + user.getUserId() + " "); } if (patient != null) { sb.append(" AND s.patient_id = " + patient.getPatientId() + " "); } if (from != null) { sb.append(" AND s.timestamp > '" + dfSQL.format(from) + "' "); } if (until != null) { sb.append(" AND s.timestamp < '" + dfSQL.format(until) + "' "); } if (filter.equalsIgnoreCase("Created")) { sb.append(" AND s.created = 1 "); } else if (filter.equalsIgnoreCase("Updated")) { sb.append(" AND s.updated = 1 "); } else if (filter.equalsIgnoreCase("Voided")) { sb.append(" AND s.voided = 1 "); } else if (filter.equalsIgnoreCase("Encounter")) { sb.append(" AND s.usage_id IN (SELECT usage_id FROM " + TABLE_ENCOUNTER + ") "); } else if (filter.equalsIgnoreCase("Visit")) { sb.append(" AND s.usage_id IN (SELECT usage_id FROM " + TABLE_VISIT + ") "); } else if (filter.equalsIgnoreCase("Order")) { sb.append(" AND s.usage_id IN (SELECT usage_id FROM " + TABLE_ORDER + ") "); } sb.append("ORDER BY s.timestamp DESC "); sb.append("LIMIT " + paging.getPageOffset() + ", " + paging.getPageSize() + ";"); Session session = sessionFactory.getCurrentSession(); List<Usage> results = sessionFactory.getCurrentSession().createSQLQuery(sb.toString()) .addEntity("s", Usage.class).list(); int count = ((Number) session.createSQLQuery("SELECT FOUND_ROWS();").uniqueResult()).intValue(); paging.setResultsTotal(count); return results; } //updates the logs page public List<LogDetailUsage> getLogDetailUsage(Date from, Date until, User user, Patient patient) throws DAOException { Criteria query = sessionFactory.getCurrentSession().createCriteria(Usage.class); query.add(Restrictions.between("timestamp", from, until)); if (user != null) { // null means all users query.add(Restrictions.eq("user", user)); } if (patient != null) { // null means all patients query.add(Restrictions.eq("patient", patient)); } List<Usage> matchUsages = query.list(); if (matchUsages.size() < 1) { return null; } List<LogDetailUsage> logDetailUsages = new ArrayList<LogDetailUsage>(); for (Usage usage : matchUsages) { LogDetailUsage logDetailUsage = new LogDetailUsage(); logDetailUsage.setDatetime(usage.getTimestamp()); logDetailUsage.setUser(usage.getUser()); logDetailUsage.setPatient(usage.getPatient()); PatientUsage patientUsage = getPatientUsage(usage); logDetailUsage.setPatientUsage(patientUsage); List<EncounterUsage> encounterUsages = getEncounterUsages(usage); logDetailUsage.setEncounterUsages(encounterUsages); List<VisitUsage> visitUsages = getVisitUsages(usage); logDetailUsage.setVisitUsages(visitUsages); List<OrderUsage> orderUsages = getOrderUsages(usage); logDetailUsage.setOrderUsages(orderUsages); logDetailUsages.add(logDetailUsage); } return logDetailUsages; } }