Example usage for org.hibernate Query setDate

List of usage examples for org.hibernate Query setDate

  1. HOME
  2. Java
  3. org.hibernate
  4. org.hibernate.*
  5. Query
  6. setDate

Introduction

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

Prototype

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

Source Link

Document

Bind the val (time is truncated) of a given Date object to a named query parameter.

Usage

From source file:org.sakaiproject.sitestats.impl.StatsManagerImpl.java

License:Educational Community License

public List<Stat> getLessonBuilderStats(final String siteId, final String pageAction,
        final List<String> resourceIds, final Date iDate, final Date fDate, final List<String> userIds,
        final boolean inverseUserSelection, final PagingPosition page, final List<String> totalsBy,
        final String sortBy, final boolean sortAscending, final int maxResults) {

    StatsSqlBuilder sqlBuilder = new StatsSqlBuilder(getDbVendor(), Q_TYPE_LESSON, totalsBy, siteId,
            (Set<String>) null, null, false, pageAction, resourceIds, iDate, fDate, userIds,
            inverseUserSelection, sortBy, sortAscending);

    final String hql = sqlBuilder.getHQL();

    final Map<Integer, Integer> columnMap = sqlBuilder.getHQLColumnMap();

    HibernateCallback hcb = new HibernateCallback() {

        public Object doInHibernate(Session session) throws HibernateException, SQLException {

            Query q = session.createQuery(hql);
            q.setString("siteid", siteId);

            if (userIds != null && !userIds.isEmpty()) {
                if (userIds.size() <= 1000) {
                    q.setParameterList("users", userIds);
                } else {
                    int nUsers = userIds.size();
                    int blockId = 0, startIndex = 0;
                    int blocks = (int) (nUsers / 1000);
                    blocks = (blocks * 1000 == nUsers) ? blocks : blocks + 1;
                    for (int i = 0; i < blocks - 1; i++) {
                        q.setParameterList("users" + blockId, userIds.subList(startIndex, startIndex + 1000));
                        blockId++;//from   ww  w .  ja  v a2s  .  c o  m
                        startIndex += 1000;
                    }
                    q.setParameterList("users" + blockId, userIds.subList(startIndex, nUsers));
                }
            }
            if (pageAction != null) {
                q.setString("action", pageAction);
            }

            if (resourceIds != null && !resourceIds.isEmpty()) {
                List<String> simpleResourceIds = new ArrayList<String>();
                List<String> wildcardResourceIds = new ArrayList<String>();
                for (String rId : resourceIds) {
                    if (rId.endsWith("/")) {
                        wildcardResourceIds.add(rId + "%");
                    } else {
                        simpleResourceIds.add(rId);
                    }
                }
                if (simpleResourceIds.size() > 0) {
                    q.setParameterList("resources", resourceIds);
                }
                for (int i = 0; i < wildcardResourceIds.size(); i++) {
                    q.setString("resource" + i, wildcardResourceIds.get(i));
                }
            }

            if (iDate != null) {
                q.setDate("idate", iDate);
            }
            if (fDate != null) {
                // adjust final date
                Calendar c = Calendar.getInstance();
                c.setTime(fDate);
                c.add(Calendar.DAY_OF_YEAR, 1);
                Date fDate2 = c.getTime();
                q.setDate("fdate", fDate2);
            }
            if (page != null) {
                q.setFirstResult(page.getFirst() - 1);
                q.setMaxResults(page.getLast() - page.getFirst() + 1);
            }
            if (maxResults > 0) {
                q.setMaxResults(maxResults);
            }

            if (LOG.isDebugEnabled()) {
                LOG.debug("getLessonBuilderStats(): " + q.getQueryString());
            }

            List<Object[]> records = q.list();
            List<LessonBuilderStat> results = new ArrayList<LessonBuilderStat>();
            Set<String> siteUserIds = null;
            if (inverseUserSelection) {
                siteUserIds = getSiteUsers(siteId);
            }
            if (records.size() > 0) {
                Calendar cal = Calendar.getInstance();
                for (Iterator<Object[]> iter = records.iterator(); iter.hasNext();) {
                    if (!inverseUserSelection) {
                        Object[] s = iter.next();
                        LessonBuilderStat stat = new LessonBuilderStatImpl();
                        if (columnMap.containsKey(StatsSqlBuilder.C_SITE)) {
                            int ix = (Integer) columnMap.get(StatsSqlBuilder.C_SITE);
                            stat.setSiteId((String) s[ix]);
                        }
                        if (columnMap.containsKey(StatsSqlBuilder.C_USER)) {
                            int ix = (Integer) columnMap.get(StatsSqlBuilder.C_USER);
                            stat.setUserId((String) s[ix]);
                        }
                        if (columnMap.containsKey(StatsSqlBuilder.C_PAGE)) {
                            int ix = (Integer) columnMap.get(StatsSqlBuilder.C_PAGE);
                            stat.setPageRef((String) s[ix]);
                        }
                        if (columnMap.containsKey(StatsSqlBuilder.C_PAGE_ACTION)) {
                            int ix = (Integer) columnMap.get(StatsSqlBuilder.C_PAGE_ACTION);
                            stat.setPageAction((String) s[ix]);
                        }
                        if (columnMap.containsKey(StatsSqlBuilder.C_PAGE_ID)) {
                            int ix = (Integer) columnMap.get(StatsSqlBuilder.C_PAGE_ID);
                            stat.setPageId((Long) s[ix]);
                        }
                        if (columnMap.containsKey(StatsSqlBuilder.C_DATE)) {
                            int ix = (Integer) columnMap.get(StatsSqlBuilder.C_DATE);
                            stat.setDate((Date) s[ix]);
                        }
                        if (columnMap.containsKey(StatsSqlBuilder.C_DATEMONTH)
                                && columnMap.containsKey(StatsSqlBuilder.C_DATEYEAR)) {
                            int ixY = (Integer) columnMap.get(StatsSqlBuilder.C_DATEYEAR);
                            int ixM = (Integer) columnMap.get(StatsSqlBuilder.C_DATEMONTH);
                            int yr = 0, mo = 0;
                            if (getDbVendor().equals("oracle")) {
                                yr = Integer.parseInt((String) s[ixY]);
                                mo = Integer.parseInt((String) s[ixM]) - 1;
                            } else {
                                yr = ((Integer) s[ixY]).intValue();
                                mo = ((Integer) s[ixM]).intValue() - 1;
                            }
                            cal.set(Calendar.YEAR, yr);
                            cal.set(Calendar.MONTH, mo);
                            stat.setDate(cal.getTime());
                        } else if (columnMap.containsKey(StatsSqlBuilder.C_DATEYEAR)) {
                            int ix = (Integer) columnMap.get(StatsSqlBuilder.C_DATEYEAR);
                            int yr = 0;
                            if (getDbVendor().equals("oracle")) {
                                yr = Integer.parseInt((String) s[ix]);
                            } else {
                                yr = ((Integer) s[ix]).intValue();
                            }
                            cal.set(Calendar.YEAR, yr);
                            stat.setDate(cal.getTime());
                        }
                        if (columnMap.containsKey(StatsSqlBuilder.C_TOTAL)) {
                            int ix = (Integer) columnMap.get(StatsSqlBuilder.C_TOTAL);
                            Long total = (Long) s[ix];
                            if (total != null) {
                                stat.setCount(total.longValue());
                            }
                        }

                        stat.setPageTitle(getLessonPageTitle(stat.getPageId()));

                        results.add(stat);
                    } else {
                        if (siteUserIds != null) {
                            siteUserIds.remove((Object) iter.next());
                        }
                    }
                }
            }
            if (inverseUserSelection) {
                long id = 0;
                Iterator<String> iU = siteUserIds.iterator();
                while (iU.hasNext()) {
                    String userId = iU.next();
                    LessonBuilderStat c = new LessonBuilderStatImpl();
                    c.setId(id++);
                    c.setUserId(userId);
                    c.setSiteId(siteId);
                    c.setCount(0);
                    results.add(c);
                }
            }
            return results;
        }
    };
    return (List<Stat>) getHibernateTemplate().execute(hcb);
}

From source file:org.sakaiproject.sitestats.impl.StatsManagerImpl.java

License:Educational Community License

public int getResourceStatsRowCount(final String siteId, final String resourceAction,
        final List<String> resourceIds, final Date iDate, final Date fDate, final List<String> userIds,
        final boolean inverseUserSelection, final List<String> totalsBy) {

    StatsSqlBuilder sqlBuilder = new StatsSqlBuilder(getDbVendor(), Q_TYPE_RESOURCE, totalsBy, siteId,
            (Set<String>) null, null, showAnonymousAccessEvents, resourceAction, resourceIds, iDate, fDate,
            userIds, inverseUserSelection, null, true);
    final String hql = sqlBuilder.getHQL();

    HibernateCallback hcb = new HibernateCallback() {
        public Object doInHibernate(Session session) throws HibernateException, SQLException {
            Query q = session.createQuery(hql);
            if (siteId != null) {
                q.setString("siteid", siteId);
            }//from w ww  . j av a2s.c  om
            if (userIds != null && !userIds.isEmpty()) {
                if (userIds.size() <= 1000) {
                    q.setParameterList("users", userIds);
                } else {
                    int nUsers = userIds.size();
                    int blockId = 0, startIndex = 0;
                    int blocks = (int) (nUsers / 1000);
                    blocks = (blocks * 1000 == nUsers) ? blocks : blocks + 1;
                    for (int i = 0; i < blocks - 1; i++) {
                        q.setParameterList("users" + blockId, userIds.subList(startIndex, startIndex + 1000));
                        blockId++;
                        startIndex += 1000;
                    }
                    q.setParameterList("users" + blockId, userIds.subList(startIndex, nUsers));
                }
            }
            if (resourceAction != null)
                q.setString("action", resourceAction);
            if (resourceIds != null && !resourceIds.isEmpty())
                q.setParameterList("resources", resourceIds);
            if (iDate != null)
                q.setDate("idate", iDate);
            if (fDate != null) {
                // adjust final date
                Calendar c = Calendar.getInstance();
                c.setTime(fDate);
                c.add(Calendar.DAY_OF_YEAR, 1);
                Date fDate2 = c.getTime();
                q.setDate("fdate", fDate2);
            }
            LOG.debug("getEventStatsRowCount(): " + q.getQueryString());
            Integer rowCount = q.list().size();
            if (!inverseUserSelection) {
                return rowCount;
            } else {
                return getSiteUsers(siteId).size() - rowCount;
            }
        }
    };
    return (Integer) getHibernateTemplate().execute(hcb);
}

From source file:org.sakaiproject.sitestats.impl.StatsManagerImpl.java

License:Educational Community License

public List<Stat> getVisitsTotalsStats(final String siteId, final Date iDate, final Date fDate,
        final PagingPosition page, final List<String> totalsBy, final String sortBy,
        final boolean sortAscending, final int maxResults) {

    StatsSqlBuilder sqlBuilder = new StatsSqlBuilder(getDbVendor(), Q_TYPE_VISITSTOTALS, totalsBy, siteId,
            (Set<String>) null, null, showAnonymousAccessEvents, null, null, iDate, fDate, null, false, sortBy,
            sortAscending);//  w w  w  .  j  a v a2s . c  om
    final String hql = sqlBuilder.getHQL();
    final Map<Integer, Integer> columnMap = sqlBuilder.getHQLColumnMap();

    // DO IT!
    HibernateCallback hcb = new HibernateCallback() {
        public Object doInHibernate(Session session) throws HibernateException, SQLException {
            Query q = session.createQuery(hql);
            if (siteId != null) {
                q.setString("siteid", siteId);
            }
            if (iDate != null)
                q.setDate("idate", iDate);
            if (fDate != null) {
                // adjust final date
                Calendar c = Calendar.getInstance();
                c.setTime(fDate);
                c.add(Calendar.DAY_OF_YEAR, 1);
                Date fDate2 = c.getTime();
                q.setDate("fdate", fDate2);
            }
            if (page != null) {
                q.setFirstResult(page.getFirst() - 1);
                q.setMaxResults(page.getLast() - page.getFirst() + 1);
            }
            if (maxResults > 0) {
                q.setMaxResults(maxResults);
            }
            LOG.debug("getVisitsTotalsStats(): " + q.getQueryString());
            List<Object[]> records = q.list();
            List<SiteVisits> results = new ArrayList<SiteVisits>();
            if (records.size() > 0) {
                Calendar cal = Calendar.getInstance();
                for (Iterator<Object[]> iter = records.iterator(); iter.hasNext();) {
                    Object[] s = iter.next();
                    SiteVisits c = new SiteVisitsImpl();
                    if (columnMap.containsKey(StatsSqlBuilder.C_SITE)) {
                        int ix = (Integer) columnMap.get(StatsSqlBuilder.C_SITE);
                        c.setSiteId((String) s[ix]);
                    }
                    if (columnMap.containsKey(StatsSqlBuilder.C_DATE)) {
                        int ix = (Integer) columnMap.get(StatsSqlBuilder.C_DATE);
                        c.setDate((Date) s[ix]);
                    }
                    if (columnMap.containsKey(StatsSqlBuilder.C_DATEMONTH)
                            && columnMap.containsKey(StatsSqlBuilder.C_DATEYEAR)) {
                        int ixY = (Integer) columnMap.get(StatsSqlBuilder.C_DATEYEAR);
                        int ixM = (Integer) columnMap.get(StatsSqlBuilder.C_DATEMONTH);
                        int yr = 0, mo = 0;
                        if (getDbVendor().equals("oracle")) {
                            yr = Integer.parseInt((String) s[ixY]);
                            mo = Integer.parseInt((String) s[ixM]) - 1;
                        } else {
                            yr = ((Integer) s[ixY]).intValue();
                            mo = ((Integer) s[ixM]).intValue() - 1;
                        }
                        cal.set(Calendar.YEAR, yr);
                        cal.set(Calendar.MONTH, mo);
                        c.setDate(cal.getTime());
                    } else if (columnMap.containsKey(StatsSqlBuilder.C_DATEYEAR)) {
                        int ix = (Integer) columnMap.get(StatsSqlBuilder.C_DATEYEAR);
                        int yr = 0;
                        if (getDbVendor().equals("oracle")) {
                            yr = Integer.parseInt((String) s[ix]);
                        } else {
                            yr = ((Integer) s[ix]).intValue();
                        }
                        cal.set(Calendar.YEAR, yr);
                        c.setDate(cal.getTime());
                    }
                    if (columnMap.containsKey(StatsSqlBuilder.C_VISITS)) {
                        int ix = (Integer) columnMap.get(StatsSqlBuilder.C_VISITS);
                        try {
                            c.setTotalVisits(((Long) s[ix]).longValue());
                        } catch (ClassCastException cce) {
                            c.setTotalVisits(((Integer) s[ix]).intValue());
                        }
                    }
                    if (columnMap.containsKey(StatsSqlBuilder.C_UNIQUEVISITS)) {
                        int ix = (Integer) columnMap.get(StatsSqlBuilder.C_UNIQUEVISITS);
                        try {
                            c.setTotalUnique(((Long) s[ix]).longValue());
                        } catch (ClassCastException cce) {
                            c.setTotalUnique(((Integer) s[ix]).intValue());
                        }
                    }
                    results.add(c);
                }
            }
            return results;
        }
    };
    return (List<Stat>) getHibernateTemplate().execute(hcb);
}

From source file:org.sakaiproject.sitestats.impl.StatsManagerImpl.java

License:Educational Community License

public List<Stat> getActivityTotalsStats(final String siteId, final List<String> events, final Date iDate,
        final Date fDate, final PagingPosition page, final List<String> totalsBy, final String sortBy,
        final boolean sortAscending, final int maxResults) {

    final Set<String> anonymousEvents = M_ers.getAnonymousEventIds();
    StatsSqlBuilder sqlBuilder = new StatsSqlBuilder(getDbVendor(), Q_TYPE_ACTIVITYTOTALS, totalsBy, siteId,
            events, anonymousEvents, showAnonymousAccessEvents, null, null, iDate, fDate, null, false, sortBy,
            sortAscending);/*from  w ww.j  ava2s  . c o  m*/
    final String hql = sqlBuilder.getHQL();
    final Map<Integer, Integer> columnMap = sqlBuilder.getHQLColumnMap();

    // DO IT!
    HibernateCallback hcb = new HibernateCallback() {
        public Object doInHibernate(Session session) throws HibernateException, SQLException {
            Query q = session.createQuery(hql);
            if (siteId != null) {
                q.setString("siteid", siteId);
            }
            if (events != null) {
                if (events.isEmpty()) {
                    events.add("");
                }
                q.setParameterList("events", events);
            }
            if (iDate != null)
                q.setDate("idate", iDate);
            if (fDate != null) {
                // adjust final date
                Calendar c = Calendar.getInstance();
                c.setTime(fDate);
                c.add(Calendar.DAY_OF_YEAR, 1);
                Date fDate2 = c.getTime();
                q.setDate("fdate", fDate2);
            }
            if (columnMap.containsKey(StatsSqlBuilder.C_USER) && anonymousEvents != null
                    && anonymousEvents.size() > 0) {
                q.setParameterList("anonymousEvents", anonymousEvents);
            }
            if (page != null) {
                q.setFirstResult(page.getFirst() - 1);
                q.setMaxResults(page.getLast() - page.getFirst() + 1);
            }
            if (maxResults > 0) {
                q.setMaxResults(maxResults);
            }
            LOG.debug("getActivityTotalsStats(): " + q.getQueryString());
            List<Object[]> records = q.list();
            List<EventStat> results = new ArrayList<EventStat>();
            if (records.size() > 0) {
                Calendar cal = Calendar.getInstance();
                Map<String, ToolInfo> eventIdToolMap = M_ers.getEventIdToolMap();
                Map<String, Integer> toolIdEventStatIxMap = new HashMap<String, Integer>();
                boolean groupByTool = columnMap.containsKey(StatsSqlBuilder.C_TOOL)
                        && !columnMap.containsKey(StatsSqlBuilder.C_EVENT);
                for (Iterator<Object[]> iter = records.iterator(); iter.hasNext();) {
                    Object[] s = iter.next();
                    EventStat c = null;
                    int eventStatListIndex = -1;
                    String toolId = null;
                    if (!groupByTool) {
                        c = new EventStatImpl();
                    } else {
                        int ix = (Integer) columnMap.get(StatsSqlBuilder.C_TOOL);
                        ToolInfo ti = eventIdToolMap.get((String) s[ix]);
                        toolId = ti != null ? ti.getToolId() : (String) s[ix];
                        Integer esIx = toolIdEventStatIxMap.get(toolId);
                        if (esIx == null) {
                            c = new EventStatImpl();
                        } else {
                            eventStatListIndex = esIx.intValue();
                            c = results.get(eventStatListIndex);
                        }
                    }
                    if (columnMap.containsKey(StatsSqlBuilder.C_SITE)) {
                        int ix = (Integer) columnMap.get(StatsSqlBuilder.C_SITE);
                        c.setSiteId((String) s[ix]);
                    }
                    if (columnMap.containsKey(StatsSqlBuilder.C_EVENT)) {
                        int ix = (Integer) columnMap.get(StatsSqlBuilder.C_EVENT);
                        c.setEventId((String) s[ix]);
                        ToolInfo ti = eventIdToolMap.get((String) s[ix]);
                        toolId = ti != null ? ti.getToolId() : (String) s[ix];
                        c.setToolId(toolId);
                    }
                    if (columnMap.containsKey(StatsSqlBuilder.C_TOOL)) {
                        c.setToolId(toolId);
                    }
                    if (columnMap.containsKey(StatsSqlBuilder.C_DATE)) {
                        int ix = (Integer) columnMap.get(StatsSqlBuilder.C_DATE);
                        c.setDate((Date) s[ix]);
                    }
                    if (columnMap.containsKey(StatsSqlBuilder.C_DATEMONTH)
                            && columnMap.containsKey(StatsSqlBuilder.C_DATEYEAR)) {
                        int ixY = (Integer) columnMap.get(StatsSqlBuilder.C_DATEYEAR);
                        int ixM = (Integer) columnMap.get(StatsSqlBuilder.C_DATEMONTH);
                        int yr = 0, mo = 0;
                        if (getDbVendor().equals("oracle")) {
                            yr = Integer.parseInt((String) s[ixY]);
                            mo = Integer.parseInt((String) s[ixM]) - 1;
                        } else {
                            yr = ((Integer) s[ixY]).intValue();
                            mo = ((Integer) s[ixM]).intValue() - 1;
                        }
                        cal.set(Calendar.YEAR, yr);
                        cal.set(Calendar.MONTH, mo);
                        c.setDate(cal.getTime());
                    } else if (columnMap.containsKey(StatsSqlBuilder.C_DATEYEAR)) {
                        int ix = (Integer) columnMap.get(StatsSqlBuilder.C_DATEYEAR);
                        int yr = 0;
                        if (getDbVendor().equals("oracle")) {
                            yr = Integer.parseInt((String) s[ix]);
                        } else {
                            yr = ((Integer) s[ix]).intValue();
                        }
                        cal.set(Calendar.YEAR, yr);
                        c.setDate(cal.getTime());
                    }
                    if (columnMap.containsKey(StatsSqlBuilder.C_TOTAL)) {
                        int ix = (Integer) columnMap.get(StatsSqlBuilder.C_TOTAL);
                        c.setCount(c.getCount() + ((Long) s[ix]).longValue());
                    }
                    if (eventStatListIndex == -1) {
                        results.add(c);
                        toolIdEventStatIxMap.put(toolId, results.size() - 1);
                    } else {
                        results.set(eventStatListIndex, c);
                    }
                }
            }
            return results;
        }
    };
    return (List<Stat>) getHibernateTemplate().execute(hcb);
}

From source file:org.sakaiproject.sitestats.impl.StatsManagerImpl.java

License:Educational Community License

public List<SiteVisits> getSiteVisitsByMonth(final String siteId, final Date iDate, final Date fDate) {
    if (siteId == null) {
        throw new IllegalArgumentException("Null siteId");
    } else {//from  ww w  .jav a 2s  . c om
        String iDateStr = "";
        String fDateStr = "";
        String usersStr = "";
        if (getDbVendor().equals("oracle")) {
            if (iDate != null)
                iDateStr = "and es.EVENT_DATE >= :idate ";
            if (fDate != null)
                fDateStr = "and es.EVENT_DATE < :fdate ";
            if (!showAnonymousAccessEvents)
                usersStr = "and es.USER_ID != '?' ";
        } else {
            if (iDate != null)
                iDateStr = "and es.date >= :idate ";
            if (fDate != null)
                fDateStr = "and es.date < :fdate ";
            if (!showAnonymousAccessEvents)
                usersStr = "and es.userId != '?' ";
        }
        final String hql = "select es.siteId, sum(es.count) ,count(distinct es.userId), year(es.date), month(es.date)"
                + "from EventStatImpl as es " + "where es.siteId = :siteid " + iDateStr + fDateStr + usersStr
                + "  and es.eventId = '" + SITEVISIT_EVENTID + "' "
                + "group by es.siteId, year(es.date), month(es.date)";
        final String oracleSql = "select es.SITE_ID as actSiteId, sum(es.EVENT_COUNT) as actVisits, count(distinct es.USER_ID) as actUnique, "
                + "  to_char(es.EVENT_DATE,'YYYY') as actYear, to_char(es.EVENT_DATE,'MM') as actMonth "
                + "from SST_EVENTS es " + "where es.SITE_ID = :siteid " + iDateStr + fDateStr + usersStr
                + "  and es.EVENT_ID = '" + SITEVISIT_EVENTID + "' "
                + "group by es.SITE_ID,to_char(es.EVENT_DATE,'YYYY'), to_char(es.EVENT_DATE,'MM')";

        HibernateCallback hcb = new HibernateCallback() {
            public Object doInHibernate(Session session) throws HibernateException, SQLException {
                Query q = null;
                if (getDbVendor().equals("oracle")) {
                    q = session.createSQLQuery(oracleSql).addScalar("actSiteId").addScalar("actVisits")
                            .addScalar("actUnique").addScalar("actYear").addScalar("actMonth");

                } else {
                    q = session.createQuery(hql);
                }
                q.setString("siteid", siteId);
                if (iDate != null)
                    q.setDate("idate", iDate);
                if (fDate != null) {
                    // adjust final date
                    Calendar c = Calendar.getInstance();
                    c.setTime(fDate);
                    c.add(Calendar.DAY_OF_YEAR, 1);
                    Date fDate2 = c.getTime();
                    q.setDate("fdate", fDate2);
                }
                List<Object[]> records = q.list();
                List<SiteVisits> results = new ArrayList<SiteVisits>();
                Calendar cal = Calendar.getInstance();
                if (records.size() > 0) {
                    for (Iterator<Object[]> iter = records.iterator(); iter.hasNext();) {
                        Object[] s = iter.next();
                        SiteVisits c = new SiteVisitsImpl();
                        if (getDbVendor().equals("oracle")) {
                            c.setSiteId((String) s[0]);
                            c.setTotalVisits(((BigDecimal) s[1]).longValue());
                            c.setTotalUnique(((BigDecimal) s[2]).longValue());
                            cal.set(Calendar.YEAR, Integer.parseInt((String) s[3]));
                            cal.set(Calendar.MONTH, Integer.parseInt((String) s[4]) - 1);
                        } else {
                            c.setSiteId((String) s[0]);
                            c.setTotalVisits(((Long) s[1]).longValue());
                            try {
                                c.setTotalUnique(((Integer) s[2]).intValue());
                            } catch (ClassCastException e) {
                                c.setTotalUnique(((Long) s[2]).intValue());
                            }
                            cal.set(Calendar.YEAR, ((Integer) s[3]).intValue());
                            cal.set(Calendar.MONTH, ((Integer) s[4]).intValue() - 1);
                        }
                        c.setDate(cal.getTime());
                        results.add(c);
                    }
                    return results;
                } else
                    return results;
            }
        };
        return (List<SiteVisits>) getHibernateTemplate().execute(hcb);
    }
}

From source file:org.sakaiproject.sitestats.impl.StatsManagerImpl.java

License:Educational Community License

public long getTotalSiteVisits(final String siteId, final Date iDate, final Date fDate) {
    if (siteId == null) {
        throw new IllegalArgumentException("Null siteId");
    } else {//from  w w w .  j av  a  2s .c o  m
        String iDateStr = "";
        String fDateStr = "";
        if (iDate != null)
            iDateStr = "and ss.date >= :idate ";
        if (fDate != null)
            fDateStr = "and ss.date < :fdate ";
        final String hql = "select sum(ss.totalVisits) " + "from SiteVisitsImpl as ss "
                + "where ss.siteId = :siteid " + iDateStr + fDateStr + "group by ss.siteId";

        HibernateCallback hcb = new HibernateCallback() {
            public Object doInHibernate(Session session) throws HibernateException, SQLException {
                Query q = session.createQuery(hql);
                q.setString("siteid", siteId);
                if (iDate != null)
                    q.setDate("idate", iDate);
                if (fDate != null) {
                    // adjust final date
                    Calendar c = Calendar.getInstance();
                    c.setTime(fDate);
                    c.add(Calendar.DAY_OF_YEAR, 1);
                    Date fDate2 = c.getTime();
                    q.setDate("fdate", fDate2);
                }
                List<Object[]> res = q.list();
                if (res.size() > 0)
                    return res.get(0);
                else
                    return Long.valueOf(0);
            }
        };
        return ((Long) getHibernateTemplate().execute(hcb)).longValue();
    }
}

From source file:org.sakaiproject.sitestats.impl.StatsManagerImpl.java

License:Educational Community License

public long getTotalSiteUniqueVisits(final String siteId, final Date iDate, final Date fDate) {
    if (siteId == null) {
        throw new IllegalArgumentException("Null siteId");
    } else {/*from w  ww  .  jav a 2s.c  o  m*/
        String iDateStr = "";
        String fDateStr = "";
        String usersStr = "";
        if (iDate != null)
            iDateStr = "and es.date >= :idate ";
        if (fDate != null)
            fDateStr = "and es.date < :fdate ";
        if (!showAnonymousAccessEvents)
            usersStr = "and es.userId != '?' ";
        final String hql = "select count(distinct es.userId) " + "from EventStatImpl as es "
                + "where es.siteId = :siteid " + "and es.eventId = 'pres.begin'" + usersStr + iDateStr
                + fDateStr;

        HibernateCallback hcb = new HibernateCallback() {
            public Object doInHibernate(Session session) throws HibernateException, SQLException {
                Query q = session.createQuery(hql);
                q.setString("siteid", siteId);
                if (iDate != null)
                    q.setDate("idate", iDate);
                if (fDate != null) {
                    // adjust final date
                    Calendar c = Calendar.getInstance();
                    c.setTime(fDate);
                    c.add(Calendar.DAY_OF_YEAR, 1);
                    Date fDate2 = c.getTime();
                    q.setDate("fdate", fDate2);
                }
                List<Object[]> res = q.list();
                if (res.size() > 0)
                    return res.get(0);
                else
                    return Long.valueOf(0);
            }
        };
        try {
            return ((Long) getHibernateTemplate().execute(hcb)).longValue();
        } catch (ClassCastException e) {
            return ((Integer) getHibernateTemplate().execute(hcb)).longValue();
        }
    }
}

From source file:org.sakaiproject.sitestats.impl.StatsManagerImpl.java

License:Educational Community License

public List<SiteActivity> getSiteActivityByDay(final String siteId, final List<String> events, final Date iDate,
        final Date fDate) {
    if (siteId == null) {
        throw new IllegalArgumentException("Null siteId");
    } else {//  www .j  a v a2s  .  c o m
        String iDateStr = "";
        String fDateStr = "";
        if (getDbVendor().equals("oracle")) {
            if (iDate != null)
                iDateStr = "and s.ACTIVITY_DATE >= :idate ";
            if (fDate != null)
                fDateStr = "and s.ACTIVITY_DATE < :fdate ";
        } else {
            if (iDate != null)
                iDateStr = "and s.date >= :idate ";
            if (fDate != null)
                fDateStr = "and s.date < :fdate ";
        }
        final String hql = "select s.siteId, sum(s.count), year(s.date), month(s.date), day(s.date) "
                + "from SiteActivityImpl as s " + "where s.siteId = :siteid " + "and s.eventId in (:eventlist) "
                + iDateStr + fDateStr + "group by s.siteId, year(s.date), month(s.date), day(s.date)";
        final String oracleSql = "select s.SITE_ID as actSiteId, sum(s.ACTIVITY_COUNT) as actCount, to_char(s.ACTIVITY_DATE,'YYYY') as actYear, to_char(s.ACTIVITY_DATE,'MM') as actMonth, to_char(s.ACTIVITY_DATE,'DD') as actDay "
                + "from SST_SITEACTIVITY s " + "where s.SITE_ID = :siteid " + "and s.EVENT_ID in (:eventlist) "
                + iDateStr + fDateStr
                + "group by s.SITE_ID, to_char(s.ACTIVITY_DATE,'YYYY'), to_char(s.ACTIVITY_DATE,'MM'), to_char(s.ACTIVITY_DATE,'DD')";

        HibernateCallback hcb = new HibernateCallback() {
            public Object doInHibernate(Session session) throws HibernateException, SQLException {
                Query q = null;
                if (getDbVendor().equals("oracle")) {
                    q = session.createSQLQuery(oracleSql).addScalar("actSiteId").addScalar("actCount")
                            .addScalar("actYear").addScalar("actMonth").addScalar("actDay");

                } else {
                    q = session.createQuery(hql);
                }
                q.setString("siteid", siteId);
                if (events != null && events.size() > 0)
                    q.setParameterList("eventlist", events);
                else
                    q.setParameterList("eventlist", M_ers.getEventIds());
                if (iDate != null)
                    q.setDate("idate", iDate);
                if (fDate != null) {
                    // adjust final date
                    Calendar c = Calendar.getInstance();
                    c.setTime(fDate);
                    c.add(Calendar.DAY_OF_YEAR, 1);
                    Date fDate2 = c.getTime();
                    q.setDate("fdate", fDate2);
                }
                List<Object[]> records = q.list();
                List<SiteActivity> results = new ArrayList<SiteActivity>();
                Calendar cal = Calendar.getInstance();
                if (records.size() > 0) {
                    for (Iterator<Object[]> iter = records.iterator(); iter.hasNext();) {
                        Object[] s = iter.next();
                        SiteActivity c = new SiteActivityImpl();
                        if (getDbVendor().equals("oracle")) {
                            c.setSiteId((String) s[0]);
                            c.setCount(((BigDecimal) s[1]).longValue());
                            cal.set(Calendar.YEAR, Integer.parseInt((String) s[2]));
                            cal.set(Calendar.MONTH, Integer.parseInt((String) s[3]) - 1);
                            cal.set(Calendar.DAY_OF_MONTH, Integer.parseInt((String) s[4]));
                        } else {
                            c.setSiteId((String) s[0]);
                            c.setCount(((Long) s[1]).longValue());
                            cal.set(Calendar.YEAR, ((Integer) s[2]).intValue());
                            cal.set(Calendar.MONTH, ((Integer) s[3]).intValue() - 1);
                            cal.set(Calendar.DAY_OF_MONTH, ((Integer) s[4]).intValue());
                        }
                        c.setDate(cal.getTime());
                        c.setEventId(null);
                        results.add(c);
                    }
                    return results;
                } else
                    return results;
            }
        };
        return (List<SiteActivity>) getHibernateTemplate().execute(hcb);
    }
}

From source file:org.sakaiproject.sitestats.impl.StatsManagerImpl.java

License:Educational Community License

public List<SiteActivity> getSiteActivityByMonth(final String siteId, final List<String> events,
        final Date iDate, final Date fDate) {
    if (siteId == null) {
        throw new IllegalArgumentException("Null siteId");
    } else {//  w  w w .j  av  a  2 s .  co m
        String iDateStr = "";
        String fDateStr = "";
        if (getDbVendor().equals("oracle")) {
            if (iDate != null)
                iDateStr = "and s.ACTIVITY_DATE >= :idate ";
            if (fDate != null)
                fDateStr = "and s.ACTIVITY_DATE < :fdate ";
        } else {
            if (iDate != null)
                iDateStr = "and s.date >= :idate ";
            if (fDate != null)
                fDateStr = "and s.date < :fdate ";
        }
        final String hql = "select s.siteId, sum(s.count), year(s.date), month(s.date) "
                + "from SiteActivityImpl as s " + "where s.siteId = :siteid " + "and s.eventId in (:eventlist) "
                + iDateStr + fDateStr + "group by s.siteId, year(s.date), month(s.date)";
        final String oracleSql = "select s.SITE_ID as actSiteId, sum(s.ACTIVITY_COUNT) as actCount, to_char(s.ACTIVITY_DATE,'YYYY') as actYear, to_char(s.ACTIVITY_DATE,'MM') as actMonth "
                + "from SST_SITEACTIVITY s " + "where s.SITE_ID = :siteid " + "and s.EVENT_ID in (:eventlist) "
                + iDateStr + fDateStr
                + "group by s.SITE_ID, to_char(s.ACTIVITY_DATE,'YYYY'), to_char(s.ACTIVITY_DATE,'MM')";

        HibernateCallback hcb = new HibernateCallback() {
            public Object doInHibernate(Session session) throws HibernateException, SQLException {
                Query q = null;
                if (getDbVendor().equals("oracle")) {
                    q = session.createSQLQuery(oracleSql).addScalar("actSiteId").addScalar("actCount")
                            .addScalar("actYear").addScalar("actMonth");

                } else {
                    q = session.createQuery(hql);
                }
                q.setString("siteid", siteId);
                if (events != null && events.size() > 0)
                    q.setParameterList("eventlist", events);
                else
                    q.setParameterList("eventlist", M_ers.getEventIds());
                if (iDate != null)
                    q.setDate("idate", iDate);
                if (fDate != null) {
                    // adjust final date
                    Calendar c = Calendar.getInstance();
                    c.setTime(fDate);
                    c.add(Calendar.DAY_OF_YEAR, 1);
                    Date fDate2 = c.getTime();
                    q.setDate("fdate", fDate2);
                }
                List<Object[]> records = q.list();
                List<SiteActivity> results = new ArrayList<SiteActivity>();
                Calendar cal = Calendar.getInstance();
                if (records.size() > 0) {
                    for (Iterator<Object[]> iter = records.iterator(); iter.hasNext();) {
                        Object[] s = iter.next();
                        SiteActivity c = new SiteActivityImpl();
                        if (getDbVendor().equals("oracle")) {
                            c.setSiteId((String) s[0]);
                            c.setCount(((BigDecimal) s[1]).longValue());
                            cal.set(Calendar.YEAR, Integer.parseInt((String) s[2]));
                            cal.set(Calendar.MONTH, Integer.parseInt((String) s[3]) - 1);
                        } else {
                            c.setSiteId((String) s[0]);
                            c.setCount(((Long) s[1]).longValue());
                            cal.set(Calendar.YEAR, ((Integer) s[2]).intValue());
                            cal.set(Calendar.MONTH, ((Integer) s[3]).intValue() - 1);
                        }
                        c.setDate(cal.getTime());
                        c.setEventId(null);
                        results.add(c);
                    }
                    return results;
                } else
                    return results;
            }
        };
        return (List<SiteActivity>) getHibernateTemplate().execute(hcb);
    }
}

From source file:org.sakaiproject.sitestats.impl.StatsManagerImpl.java

License:Educational Community License

public List<SiteActivityByTool> getSiteActivityByTool(final String siteId, final List<String> events,
        final Date iDate, final Date fDate) {
    if (siteId == null) {
        throw new IllegalArgumentException("Null siteId");
    } else {/*from w  w w.  ja  v a 2s  .  c  o m*/
        String iDateStr = "";
        String fDateStr = "";
        if (getDbVendor().equals("oracle")) {
            if (iDate != null)
                iDateStr = "and s.ACTIVITY_DATE >= :idate ";
            if (fDate != null)
                fDateStr = "and s.ACTIVITY_DATE < :fdate ";
        } else {
            if (iDate != null)
                iDateStr = "and s.date >= :idate ";
            if (fDate != null)
                fDateStr = "and s.date < :fdate ";
        }
        final String hql = "select s.siteId, sum(s.count), s.eventId " + "from SiteActivityImpl as s "
                + "where s.siteId = :siteid " + "and s.eventId in (:eventlist) " + iDateStr + fDateStr
                + "group by s.siteId, s.eventId";
        final String oracleSql = "select s.SITE_ID as actSiteId, sum(s.ACTIVITY_COUNT) as actCount, s.EVENT_ID as actEventId "
                + "from SST_SITEACTIVITY s " + "where s.SITE_ID = :siteid " + "and s.EVENT_ID in (:eventlist) "
                + iDateStr + fDateStr + "group by s.SITE_ID, s.EVENT_ID";

        HibernateCallback hcb = new HibernateCallback() {
            public Object doInHibernate(Session session) throws HibernateException, SQLException {
                Query q = null;
                if (getDbVendor().equals("oracle")) {
                    q = session.createSQLQuery(oracleSql).addScalar("actSiteId").addScalar("actCount")
                            .addScalar("actEventId");

                } else {
                    q = session.createQuery(hql);
                }
                q.setString("siteid", siteId);
                if (events != null && events.size() > 0)
                    q.setParameterList("eventlist", events);
                else
                    q.setParameterList("eventlist", M_ers.getEventIds());
                if (iDate != null)
                    q.setDate("idate", iDate);
                if (fDate != null) {
                    // adjust final date
                    Calendar c = Calendar.getInstance();
                    c.setTime(fDate);
                    c.add(Calendar.DAY_OF_YEAR, 1);
                    Date fDate2 = c.getTime();
                    q.setDate("fdate", fDate2);
                }
                List<Object[]> records = q.list();
                List<SiteActivityByTool> results = new ArrayList<SiteActivityByTool>();
                if (records.size() > 0) {
                    Map<String, ToolInfo> eventIdToolMap = M_ers.getEventIdToolMap();
                    Map<String, SiteActivityByTool> toolidSABT = new HashMap<String, SiteActivityByTool>();
                    List<ToolInfo> allTools = M_ers.getEventRegistry();
                    for (Iterator<Object[]> iter = records.iterator(); iter.hasNext();) {
                        Object[] s = iter.next();
                        SiteActivityByTool c = new SiteActivityByToolImpl();
                        if (getDbVendor().equals("oracle")) {
                            c.setSiteId((String) s[0]);
                            c.setCount(((BigDecimal) s[1]).longValue());
                        } else {
                            c.setSiteId((String) s[0]);
                            c.setCount(((Long) s[1]).longValue());
                        }
                        ToolInfo toolInfo = eventIdToolMap.get((String) s[2]);
                        if (toolInfo != null) {
                            String toolId = toolInfo.getToolId();
                            SiteActivityByTool existing = toolidSABT.get(toolId);
                            if (existing != null) {
                                // increment count for same tool
                                existing.setCount(existing.getCount() + c.getCount());
                                toolidSABT.put(toolId, existing);
                            } else {
                                // add new tool count
                                int ix = allTools.indexOf(new ToolInfo(toolId));
                                c.setTool(allTools.get(ix));
                                toolidSABT.put(toolId, c);
                            }
                        }
                    }
                    // aggregate
                    results.addAll(toolidSABT.values());
                    return results;
                } else
                    return results;
            }
        };
        return (List<SiteActivityByTool>) getHibernateTemplate().execute(hcb);
    }
}