/*
*
* Copyright 2013 Entando S.r.l. (http://www.entando.com) All rights reserved.
*
* This file is part of Entando software.
* Entando is a free software;
* You can redistribute it and/or modify it
* under the terms of the GNU General Public License (GPL) as published by the Free Software Foundation; version 2.
*
* See the file License for the specific language governing permissions
* and limitations under the License
*
*
*
* Copyright 2013 Entando S.r.l. (http://www.entando.com) All rights reserved.
*
*/
package com.agiletec.plugins.jpstats.aps.system.services.stats;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Properties;
import java.util.TreeMap;
import java.util.concurrent.TimeUnit;
import org.apache.commons.lang.StringUtils;
import org.jfree.data.time.Day;
import org.jfree.data.time.TimeSeries;
import com.agiletec.aps.system.common.AbstractDAO;
import com.agiletec.aps.system.services.lang.ILangManager;
import com.agiletec.aps.system.services.page.IPage;
import com.agiletec.aps.system.services.page.IPageManager;
import com.agiletec.aps.util.DateConverter;
import com.agiletec.plugins.jacms.aps.system.services.content.IContentManager;
import com.agiletec.plugins.jacms.aps.system.services.content.model.ContentRecordVO;
import com.agiletec.plugins.jacms.aps.system.services.content.model.SmallContentType;
import com.agiletec.plugins.jpstats.aps.system.services.stats.model.ContentStatistic;
import com.agiletec.plugins.jpstats.aps.system.services.stats.model.DateStatistic;
import com.agiletec.plugins.jpstats.aps.system.services.stats.model.PageStatistic;
import com.agiletec.plugins.jpstats.aps.system.services.stats.model.VisitsStat;
/**
* Data Access Object for the Statistics Manager
* @version 1.2
* @author M.Lisci - E.Santoboni
*/
public class StatsDAO extends AbstractDAO implements IStatsDAO {
protected String getDriverName() throws Throwable {
String driverName = null;
Method method = this.getDataSource().getClass().getDeclaredMethod("getDriverClassName");
String className = (String) method.invoke(this.getDataSource());
if (StringUtils.isNotBlank(className)) {
Iterator<Entry<Object, Object>> it = this.getDatabaseTypeDrivers().entrySet().iterator();
while (it.hasNext()) {
Entry<Object, Object> entry = it.next();
List<String> values = (List<String>) entry.getValue();
if (null != values && !values.isEmpty()) {
if (values.contains(className)) {
driverName = (String) entry.getKey();
break;
}
}
}
}
return driverName;
}
private String convertSecondsToInterval(int seconds) {
int day = (int)TimeUnit.SECONDS.toDays(seconds);
long hours = TimeUnit.SECONDS.toHours(seconds) - (day *24);
long minute = TimeUnit.SECONDS.toMinutes(seconds) - (TimeUnit.SECONDS.toHours(seconds)* 60);
long second = TimeUnit.SECONDS.toSeconds(seconds) - (TimeUnit.SECONDS.toMinutes(seconds) *60);
return day + " days " + String.format("%02d",hours)+ ":" + String.format("%02d",minute) + ":" + String.format("%02d",second);
}
@Override
public List<StatsRecord> loadStatsRecord(Date from, Date to) {
List<StatsRecord> records = new ArrayList<StatsRecord>();
Connection conn = null;
PreparedStatement stat = null;
ResultSet res = null;
String startString = new Timestamp(from.getTime()).toString();
String endString = new Timestamp(to.getTime()).toString();
try {
conn = this.getConnection();
stat = conn.prepareStatement(LOAD_RECORDS);
stat.setString(1, startString);
stat.setString(2, endString);
res = stat.executeQuery();
while (res.next()) {
StatsRecord record = this.createStatsRecord(res);
records.add(record);
}
} catch (Throwable t) {
processDaoException(t, "Error getting Ip address ", "loadStatsRecord");
} finally {
closeDaoResources(res, stat, conn);
}
return records;
}
/**
* Adds a record to the statistic table
* @param statsRecord
*/
@Override
public void addStatsRecord(StatsRecord statsRecord) {
Connection conn = null;
PreparedStatement prepStat = null;
try {
conn = this.getConnection();
conn.setAutoCommit(false);
prepStat = conn.prepareStatement(ADD_RECORD);
prepStat.setString(1, statsRecord.getIp());
prepStat.setString(2, statsRecord.getReferer());
prepStat.setString(3, statsRecord.getSessionId());
prepStat.setString(4, statsRecord.getRole());
prepStat.setString(5, statsRecord.getTimestamp());
prepStat.setString(6, statsRecord.getYear());
prepStat.setString(7, statsRecord.getMonth());
prepStat.setString(8, statsRecord.getDay());
prepStat.setString(9, statsRecord.getHour());
prepStat.setString(10, statsRecord.getPageCode());
prepStat.setString(11, statsRecord.getLangcode());
prepStat.setString(12, statsRecord.getUseragent());
prepStat.setString(13, statsRecord.getBrowserLang());
prepStat.setString(14, statsRecord.getContentId());
prepStat.executeUpdate();
conn.commit();
} catch (Throwable t) {
processDaoException(t, "Error adding a statistic record", "addStatsRecord");
} finally {
closeDaoResources(null, prepStat, conn);
}
}
@Override
public void deleteStatsRecord(Date from, Date to) {
Connection conn = null;
PreparedStatement prepStat = null;
try {
conn = this.getConnection();
conn.setAutoCommit(false);
prepStat = conn.prepareStatement(REMOVE_RECORDS);
prepStat.setString(1, (new java.sql.Timestamp(from.getTime())).toString());
prepStat.setString(2, (new java.sql.Timestamp(to.getTime())).toString());
prepStat.executeUpdate();
conn.commit();
} catch (Throwable t) {
processDaoException(t, "Error removing statistic records", "deleteStatsRecord");
} finally {
closeDaoResources(null, prepStat, conn);
}
}
@Override
public List<VisitsStat> searchVisitsForDate(Date from, Date to) {
List<VisitsStat> visitsStats = new ArrayList<VisitsStat>();
Connection conn = null;
PreparedStatement stat = null;
ResultSet res = null;
try {
conn = this.getConnection();
stat = conn.prepareStatement(SEARCH_DAILY_VISITS);
stat.setString(1, DateConverter.getFormattedDate(from, "yyyy-MM-dd 00:00:00.000"));
stat.setString(2, DateConverter.getFormattedDate(to, "yyyy-MM-dd 23:59:59.999"));
res = stat.executeQuery();
Calendar calendar = Calendar.getInstance();
while (res.next()) {
DateStatistic statistic = new DateStatistic();
int hit = res.getInt(1);
calendar.set(res.getInt(2), res.getInt(3)-1, res.getInt(4), 0, 0, 0);
Date day = calendar.getTime();
statistic.setDate(day);
statistic.setVisits(new Integer(hit));
visitsStats.add(statistic);
}
} catch (Throwable t) {
processDaoException(t, "Error searching visits for date", "searchVisitsForDate");
} finally {
closeDaoResources(res, stat, conn);
}
return visitsStats;
}
@Override
public List<VisitsStat> searchVisitsForPages(Date from, Date to) {
List<VisitsStat> visitsStats = new ArrayList<VisitsStat>();
Connection conn = null;
PreparedStatement stat = null;
ResultSet res = null;
try {
conn = this.getConnection();
stat = conn.prepareStatement(SEARCH_PAGE_VISITS);
stat.setString(1, DateConverter.getFormattedDate(from, "yyyy-MM-dd 00:00:00.000"));
stat.setString(2, DateConverter.getFormattedDate(to, "yyyy-MM-dd 23:59:59.999"));
res = stat.executeQuery();
IPageManager pageManager = this.getPageManager();
String langCode = this.getLangManager().getDefaultLang().getCode();
while (res.next()) {
PageStatistic statistic = new PageStatistic();
String code = res.getString(1);
statistic.setCode(code);
IPage page = pageManager.getPage(code);
String descr = (page!=null) ? page.getTitle(langCode) : code;
statistic.setDescr(descr);
statistic.setVisits(new Integer(res.getInt(2)));
visitsStats.add(statistic);
}
} catch (Throwable t) {
processDaoException(t, "Error searching visits for pages", "searchVisitsForPages");
} finally {
closeDaoResources(res, stat, conn);
}
return visitsStats;
}
@Override
public List<VisitsStat> searchVisitsForContents(Date from, Date to) {
List<VisitsStat> visitsStats = new ArrayList<VisitsStat>();
Connection conn = null;
PreparedStatement stat = null;
ResultSet res = null;
try {
conn = this.getConnection();
stat = conn.prepareStatement(SEARCH_CONTENT_VISITS);
stat.setString(1, DateConverter.getFormattedDate(from, "yyyy-MM-dd 00:00:00.000"));
stat.setString(2, DateConverter.getFormattedDate(to, "yyyy-MM-dd 23:59:59.999"));
res = stat.executeQuery();
Map<String, SmallContentType> contentTypes = this.getContentManager().getSmallContentTypesMap();
while (res.next()) {
ContentStatistic statistic = new ContentStatistic();
String id = res.getString(1);
statistic.setId(id);
ContentRecordVO content = this.getContentManager().loadContentVO(id);
if (content == null) {
statistic.setDescr(id);
} else {
SmallContentType contentType = contentTypes.get(content.getTypeCode());
statistic.setDescr(content.getDescr());
statistic.setType(contentType.getDescr());
}
statistic.setVisits(new Integer(res.getInt(2)));
visitsStats.add(statistic);
}
} catch (Throwable t) {
processDaoException(t, "Error searching visits for contents", "searchVisitsForContents");
} finally {
closeDaoResources(res, stat, conn);
}
return visitsStats;
}
/**
* Gets the hits between two dates
* @param start Calendar
* @param end Calendar
* @return a TimeSeries object, used to render the chart
*/
@Override
public TimeSeries getHitsByInterval(Calendar start, Calendar end) {
Connection conn = null;
PreparedStatement stat = null;
ResultSet res = null;
TimeSeries hitsPage = new TimeSeries("Japs_Chart_v0.0", Day.class);
String startString = new Timestamp(start.getTimeInMillis()).toString();
String endString = new Timestamp(end.getTimeInMillis()).toString();
try {
conn = this.getConnection();
stat = conn.prepareStatement(HITS_BY_INTERVAL);
stat.setString(1, startString);
stat.setString(2, endString);
res = stat.executeQuery();
Day initDay = new Day(start.getTime());
Day endDay = new Day(end.getTime());
while (res.next()) {
Day day = new Day(res.getInt("day_value"),res.getInt("month_value"),res.getInt("year_value"));
hitsPage.add(day,res.getInt("hits"));
}
try {
hitsPage.add(initDay,0);
} catch (Throwable t) {}
try {
hitsPage.add(endDay,0);
} catch (Throwable t) {}
} catch (Throwable t) {
processDaoException(t, "Error getting hits by interval ", "getHitsByInterval");
} finally {
closeDaoResources(res, stat, conn);
}
return hitsPage;
}
/**
* Gets the average time spent on the site by session
* @param start Calendar
* @param end Calendar
* @return a string whith the format hh:mm:ss
*/
@Override
public String getAverageTimeSite(Calendar start, Calendar end) {
Connection conn = null;
PreparedStatement stat = null;
ResultSet res = null;
String mediaSessioni = null;
String startString = new Timestamp(start.getTimeInMillis()).toString();
String endString = new Timestamp(end.getTimeInMillis()).toString();
try {
conn = this.getConnection();
String queryName = this.GetAVERAGE_TIME_SITE(this.getDriverName());
stat = conn.prepareStatement(queryName);
stat.setString(1, startString);
stat.setString(2, endString);
res = stat.executeQuery();
String media = null;
while (res.next()) {
int seconds = res.getInt(1);
media = this.convertSecondsToInterval(seconds);
}
mediaSessioni = media;
} catch (Throwable t) {
processDaoException(t, "Error getting average time site", "getAverageTimeSite");
} finally {
closeDaoResources(res, stat, conn);
}
return this.roundInterval(mediaSessioni);
}
/**
* Gets the average time spent on a page by pagecode and by session
* @param start Calendar
* @param end Calendar
* @return a string whith the format hh:mm:ss
*/
@Override
public String getAverageTimePage(Calendar start, Calendar end) {
Connection conn = null;
PreparedStatement stat = null;
ResultSet res = null;
String mediaTimePage = new String();
String startString = new Timestamp(start.getTimeInMillis()).toString();
String endString = new Timestamp(end.getTimeInMillis()).toString();
try {
conn = this.getConnection();
String queryName = this.GetAVERAGE_TIME_PAGE(this.getDriverName());
stat = conn.prepareStatement(queryName);
stat.setString(1, startString);
stat.setString(2, endString);
res = stat.executeQuery();
while (res.next()) {
int seconds = res.getInt("media");
mediaTimePage = this.convertSecondsToInterval(seconds);
}
} catch (Throwable t) {
processDaoException(t, "Error getting average time page", "getAverageTimePage");
} finally {
closeDaoResources(res, stat, conn);
}
return roundInterval(mediaTimePage);
}
/**
* Gets the average amount of pages visited in each session
* @param start Calendar
* @param end Calendar
* @return int the average amount of pages visited in each session
*/
@Override
public int getNumPageSession(Calendar start, Calendar end) {
Connection conn = null;
PreparedStatement stat = null;
ResultSet res = null;
int mediaPage = 0;
String startString = new Timestamp(start.getTimeInMillis()).toString();
String endString = new Timestamp(end.getTimeInMillis()).toString();
try {
conn = this.getConnection();
stat = conn.prepareStatement(AVERAGE_PAGE);
stat.setString(1, startString);
stat.setString(2, endString);
res = stat.executeQuery();
while (res.next()) {
mediaPage = res.getInt(1);
}
} catch (Throwable t) {
processDaoException(t, "Error getting average num page session ", "getNumPageSession");
} finally {
closeDaoResources(res, stat, conn);
}
return mediaPage;
}
/**
* Gets the ten most visited pages
* @param start Calendar
* @param end Calendar
* @return a map (pagecode:hits) used to render the chart
*/
@Override
public Map<String, Integer> getTopPages(Calendar start, Calendar end) {
Connection conn = null;
PreparedStatement stat = null;
ResultSet res = null;
Map<String, Integer> hitsPage = new TreeMap<String, Integer>();
String startString = new Timestamp(start.getTimeInMillis()).toString();
String endString = new Timestamp(end.getTimeInMillis()).toString();
try {
conn = this.getConnection();
String queryName = this.GetGET_TOP_PAGES(this.getDriverName());
stat = conn.prepareStatement(queryName);
stat.setString(1, startString);
stat.setString(2, endString);
res = stat.executeQuery();
while (res.next()) {
int count = res.getInt("hits");
hitsPage.put(res.getString("pagecode"), new Integer(count));
}
} catch (Throwable t) {
processDaoException(t, "Error getting the most visited pages ", "getPageVisitedDesc");
} finally {
closeDaoResources(res, stat, conn);
}
return hitsPage;
}
/**
* Gets the ten most visited contents
* If the content does not exists anymore the function
* prints [DELETED] instead of the description
* @param start Calendar
* @param end Calendar
* @param contentManager
* @return a map (content_descr:hits) used to render the chart
*/
@Override
public Map<String, Integer> getTopContents(Calendar start, Calendar end) {
IContentManager contentManager = this.getContentManager();
Connection conn = null;
PreparedStatement stat = null;
ResultSet res = null;
Map<String, Integer> topContents = new TreeMap<String, Integer>();
String startString = new Timestamp(start.getTimeInMillis()).toString();
String endString = new Timestamp(end.getTimeInMillis()).toString();
try {
conn = this.getConnection();
String queryName = this.GetGET_TOP_CONTENTS(this.getDriverName());
stat = conn.prepareStatement(queryName);
stat.setString(1, startString);
stat.setString(2, endString);
res = stat.executeQuery();
while (res.next()) {
String contentId = res.getString("content");
String contentDescr = null;
ContentRecordVO content = contentManager.loadContentVO(contentId);
if (null == content) {
contentDescr = "[DELETED]";
} else {
contentDescr = content.getDescr();
}
int count = res.getInt("hits");
topContents.put(contentDescr, new Integer(count));
}
} catch (Throwable t) {
processDaoException(t, "Error getting the most visited contents ", "getTopContents");
} finally {
closeDaoResources(res, stat, conn);
}
return topContents;
}
/**
*
* @return Calendar the first date stored in the statistic table
* If the table is empty returns the current date
*/
@Override
public Calendar getFirstCalendarDay() {
Connection conn = null;
Calendar firstDay = Calendar.getInstance();
PreparedStatement stat = null;
ResultSet res = null;
try {
conn = this.getConnection();
String queryName = this.GetGET_FIRST_DATE(this.getDriverName());
stat = conn.prepareStatement(queryName);
res = stat.executeQuery();
while (res.next()) {
int year = Integer.parseInt(res.getString("year_value"));
int month = Integer.parseInt(res.getString("month_value"));
int day = Integer.parseInt(res.getString("day_value"));
firstDay.set(year,month-1, day,0,0,0);
firstDay.set(Calendar.MILLISECOND, 0);
}
} catch (Throwable t) {
processDaoException(t, "Error getting the first day ", "getFirstCalendarDay");
} finally {
closeDaoResources(res, stat, conn);
}
return firstDay;
}
/**
* Gets a map of Ip Address (ip,hits)
* @param start Calendar
* @param end Calendar
* @return a map of Ip (ip,hits)
*/
@Override
public Map<String, Integer> getIPByDateInterval(Calendar start, Calendar end) {
Connection conn = null;
PreparedStatement stat = null;
ResultSet res = null;
Map<String, Integer> statsRecord = new TreeMap<String, Integer>();
String startString = new Timestamp(start.getTimeInMillis()).toString();
String endString = new Timestamp(end.getTimeInMillis()).toString();
try {
conn = this.getConnection();
stat = conn.prepareStatement(GET_IP);
stat.setString(1, startString);
stat.setString(2, endString);
res = stat.executeQuery();
while (res.next()) {
String ip = res.getString(1);
int count = res.getInt(2);
statsRecord.put(ip, new Integer(count));
}
} catch (Throwable t) {
processDaoException(t, "Error getting Ip address ", "getIPByDateInterval");
} finally {
closeDaoResources(res, stat, conn);
}
return statsRecord;
}
/**
* Rounds a string cutting the milliseconds
* Queries the gets average time can return null values
* This function transform null values in 00:00:00
* @param interval String
* @return
*/
private String roundInterval(String interval) {
if(interval==null) interval = "00:00:00";
int length = interval.length();
if (interval.indexOf(".")!=-1) {
length=interval.indexOf(".");
}
return interval.substring(0,length);
}
private StatsRecord createStatsRecord(ResultSet res) throws Throwable {
//ip, referer, session_id, role, timestamp, year, month, day, hour, pagecode, langcode, useragent, browserlang, content
Calendar calendar = this.extractRecordDate(res);
StatsRecord record = new StatsRecord(calendar);
record.setIp(res.getString("ip"));
record.setReferer(res.getString("referer"));
record.setSessionId(res.getString("session_id"));
record.setRole(res.getString("role"));
record.setPageCode(res.getString("pagecode"));
record.setLangcode(res.getString("langcode"));
record.setUseragent(res.getString("useragent"));
record.setBrowserLang(res.getString("browserlang"));
record.setContentId(res.getString("content"));
return record;
}
private Calendar extractRecordDate(ResultSet res) throws SQLException {
Calendar calendar = Calendar.getInstance();
String year = res.getString("year_value");
String month = res.getString("month_value");
String day = res.getString("day_value");
String hour = res.getString("hour_value");
calendar.set(Calendar.YEAR, Integer.parseInt(year));
calendar.set(Calendar.MONTH, Integer.parseInt(month)-1);
calendar.set(Calendar.DAY_OF_MONTH, Integer.parseInt(day));
String[] array = hour.split(":");
if (array.length == 3) {
calendar.set(Calendar.HOUR_OF_DAY, Integer.parseInt(array[0].trim()));
calendar.set(Calendar.MINUTE, Integer.parseInt(array[1].trim()));
calendar.set(Calendar.SECOND, Integer.parseInt(array[2].trim()));
}
return calendar;
}
protected IContentManager getContentManager() {
return _contentManager;
}
public void setContentManager(IContentManager contentManager) {
this._contentManager = contentManager;
}
public IPageManager getPageManager() {
return _pageManager;
}
public void setPageManager(IPageManager pageManager) {
this._pageManager = pageManager;
}
public ILangManager getLangManager() {
return _langManager;
}
public void setLangManager(ILangManager langManager) {
this._langManager = langManager;
}
private IContentManager _contentManager;
private IPageManager _pageManager;
private ILangManager _langManager;
private String GetAVERAGE_TIME_PAGE(String driver) {
String q = AVERAGE_TIME_PAGE_postgres;
if (driver.equalsIgnoreCase("postgres")) {
q = AVERAGE_TIME_PAGE_postgres;
} else if (driver.equalsIgnoreCase("mysql")) {
return AVERAGE_TIME_PAGE_mysql;
}else if (driver.equalsIgnoreCase("derby")) {
return AVERAGE_TIME_PAGE_derby;
}
return q;
}
private String GetAVERAGE_TIME_SITE(String driver) {
String q = AVERAGE_TIME_SITE_postgres;
if (driver.equalsIgnoreCase("postgres")) {
q = AVERAGE_TIME_SITE_postgres;
} else if (driver.equalsIgnoreCase("mysql")) {
return AVERAGE_TIME_SITE_mysql;
} else if (driver.equalsIgnoreCase("derby")) {
return AVERAGE_TIME_SITE_derby;
}
return q;
}
private String GetGET_FIRST_DATE(String driver) {
String q = GET_FIRST_DATE;
if (driver.equalsIgnoreCase("derby")) {
q = GET_FIRST_DATE_derby;
}
return q;
}
private String GetGET_TOP_CONTENTS(String driver) {
String q = GET_TOP_CONTENTS;
if (driver.equalsIgnoreCase("derby")) {
q = GET_TOP_CONTENTS_derby;
}
return q;
}
private String GetGET_TOP_PAGES(String driver) {
String q = GET_TOP_PAGES;
if (driver.equalsIgnoreCase("derby")) {
q = GET_TOP_PAGES_derby;
}
return q;
}
private final String ADD_RECORD =
"INSERT INTO jpstats_statistics (ip, referer, session_id, role, timestamp, year_value, month_value, day_value, hour_value, pagecode, langcode, useragent, browserlang, content) "
+ "VALUES ( ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? )";
private final String REMOVE_RECORDS =
"DELETE FROM jpstats_statistics WHERE timestamp >= ? AND timestamp <= ? ";
private final String LOAD_RECORDS =
"SELECT * FROM jpstats_statistics WHERE timestamp >= ? AND timestamp <= ? ORDER BY timestamp DESC";
private final String SEARCH_DAILY_VISITS =
"SELECT count(*) as hits, year_value, month_value, day_value FROM jpstats_statistics " +
"WHERE timestamp >= ? AND timestamp <= ? GROUP BY year_value, month_value, day_value ORDER BY hits DESC";
private final String SEARCH_PAGE_VISITS =
"SELECT pagecode, COUNT(*) AS hits FROM jpstats_statistics WHERE timestamp >= ? AND timestamp <= ? " +
"GROUP BY pagecode ORDER BY hits DESC";
private final String SEARCH_CONTENT_VISITS =
"SELECT content, COUNT(*) AS hits FROM jpstats_statistics WHERE timestamp >= ? AND timestamp <= ? " +
"AND content IS NOT NULL GROUP BY content ORDER BY hits DESC";
private final String HITS_BY_INTERVAL =
"SELECT count(*) as hits, day_value, month_value, year_value FROM jpstats_statistics " +
" WHERE timestamp >= ? AND timestamp <= ? " +
" GROUP BY year_value, month_value, day_value ORDER BY year_value, month_value, day_value ASC";
private final String AVERAGE_TIME_SITE_postgres =
"SELECT avg(x) AS media " +
" FROM( SELECT session_id, extract(EPOCH FROM MAX(timestamp)::TIMESTAMP - MIN(timestamp)::TIMESTAMP) as x " +
" FROM jpstats_statistics " +
" WHERE timestamp >= ? AND timestamp <= ?" +
" GROUP BY session_id " +
" HAVING count(session_id)>1 )AS SUBQUERY";
private final String AVERAGE_TIME_SITE_mysql =
"SELECT avg(x) AS media " +
" FROM( SELECT session_id, TIMESTAMPDIFF(second, MIN(timestamp(timestamp)), MAX(timestamp(timestamp))) AS x" +
" FROM jpstats_statistics " +
" WHERE timestamp >= ? AND timestamp <= ?" +
" GROUP BY session_id " +
" HAVING count(session_id)>1 )AS SUBQUERY";
private final String AVERAGE_TIME_SITE_derby =
"SELECT avg(x) AS media " +
" FROM( SELECT session_id, {fn TIMESTAMPDIFF(SQL_TSI_SECOND, MIN(timestamp(timestamp)), MAX(timestamp(timestamp)))} AS x" +
" FROM jpstats_statistics " +
" WHERE timestamp >= ? AND timestamp <= ?" +
" GROUP BY session_id " +
" HAVING count(session_id)>1 )AS SUBQUERY";
private final String AVERAGE_TIME_PAGE_postgres =
"SELECT AVG(x) AS media" +
" FROM( SELECT session_id as s, pagecode as p, extract(EPOCH FROM MAX(timestamp)::TIMESTAMP - MIN(timestamp)::TIMESTAMP) as x " +
" FROM jpstats_statistics WHERE timestamp >= ? AND timestamp <= ? GROUP BY p, s )AS SUBQUERY ";
private final String AVERAGE_TIME_PAGE_mysql =
"SELECT AVG(x) AS media" +
" FROM( SELECT session_id as s, pagecode as p, TIMESTAMPDIFF(second, MIN(timestamp(timestamp)), MAX(timestamp(timestamp))) AS x " +
" FROM jpstats_statistics WHERE timestamp >= ? AND timestamp <= ? GROUP BY p, s )AS SUBQUERY ";
private final String AVERAGE_TIME_PAGE_derby =
"SELECT AVG(x) AS media" +
" FROM( SELECT session_id as s, pagecode as p, {fn TIMESTAMPDIFF(SQL_TSI_SECOND, MIN(timestamp(timestamp)), MAX(timestamp(timestamp)))} AS x " +
" FROM jpstats_statistics WHERE timestamp >= ? AND timestamp <= ? GROUP BY pagecode, session_id )AS SUBQUERY ";
private final String AVERAGE_PAGE=
"SELECT AVG(x) AS media " +
" FROM(SELECT session_id, COUNT(pagecode) AS x " +
" FROM jpstats_statistics " +
" WHERE timestamp >= ? AND timestamp <= ? " +
" GROUP BY session_id )AS SUBQUERY";
private final String GET_TOP_PAGES =
"SELECT pagecode,COUNT(*) AS hits FROM jpstats_statistics " +
" WHERE timestamp >= ? AND timestamp <= ? " +
" GROUP BY pagecode " +
" ORDER BY hits DESC" +
" LIMIT 10;";
private final String GET_TOP_PAGES_derby =
"SELECT * FROM ( SELECT ROW_NUMBER() OVER() AS rownum, pagecode,COUNT(*) AS hits FROM jpstats_statistics " +
" WHERE timestamp >= ? AND timestamp <= ? " +
" GROUP BY pagecode " +
" ORDER BY hits DESC) as tmp WHERE rownum <= 10";
private final String GET_TOP_CONTENTS =
"SELECT content, COUNT(content) AS hits FROM jpstats_statistics " +
"WHERE timestamp >= ? AND timestamp <= ? and content IS NOT NULL " +
"GROUP BY content " +
"ORDER BY hits " +
"DESC LIMIT 10";
private final String GET_TOP_CONTENTS_derby =
"SELECT * FROM ( SELECT ROW_NUMBER() OVER() AS rownum, content, COUNT(content) AS hits FROM jpstats_statistics " +
"WHERE timestamp >= ? AND timestamp <= ? and content IS NOT NULL " +
"GROUP BY content " +
"ORDER BY hits " +
"DESC) as tmp WHERE rownum <= 10";
private final String GET_FIRST_DATE =
"SELECT year_value, month_value, day_value FROM jpstats_statistics ORDER BY timestamp ASC LIMIT 1";
private final String GET_FIRST_DATE_derby =
"SELECT * FROM ( SELECT ROW_NUMBER() OVER() AS rownum, year_value, month_value, day_value FROM jpstats_statistics ORDER BY timestamp ASC) as tmp WHERE rownum <= 1";
private final String GET_IP =
"SELECT DISTINCT ip, count(*) as count " +
"FROM jpstats_statistics WHERE timestamp >= ? AND timestamp <= ? GROUP BY ip";
protected Properties getDatabaseTypeDrivers() {
return _databaseTypeDrivers;
}
public void setDatabaseTypeDrivers(Properties databaseTypeDrivers) {
this._databaseTypeDrivers = databaseTypeDrivers;
}
private Properties _databaseTypeDrivers;
}