1 package com.dotmarketing.portlets.htmlpageviews.factories; 2 3 import com.dotmarketing.db.DotConnect; 4 5 9 public class HTMLPageViewFactory { 10 11 private static String GET_TOTAL_HTML_PAGE_VIEWS = "select count(*) as num_views from clickstream_request where request_uri = ? and timestampper between ? and ?"; 12 13 private static String GET_UNIQUE_VISITORS = "select count(distinct cookie_id) as num_views from clickstream_request, clickstream where clickstream.clickstream_id = clickstream_request.clickstream_id and request_uri = ? and timestampper between ? and ?"; 16 17 private static String GET_TOTAL_HTML_PAGE_VIEWS_BY_LANGUAGE = "select language, count(clickstream_request_id) as pageviews from clickstream_request, language where language.id = clickstream_request.language_id and request_uri = ? and timestampper between ? and ? group by language order by pageviews desc"; 18 private static String GET_TOP_INTERNAL_REFERRING_PAGES = "select cr2.request_uri, count(cr.request_uri) as num_referring from clickstream_request cr left join clickstream_request cr2 on (cr2.request_order = cr.request_order - 1 and cr2.clickstream_id = cr.clickstream_id ) where cr2.request_uri is not null and cr2.request_uri <> ? and cr.request_uri = ? and cr.timestampper between ? and ? group by cr2.request_uri order by num_referring desc"; 19 private static String GET_TOP_INTERNAL_EXIT_PAGES = "select cr2.request_uri, count(cr.request_uri) as num_referring from clickstream_request cr left join clickstream_request cr2 on (cr2.request_order = cr.request_order + 1 and cr2.clickstream_id = cr.clickstream_id) where cr2.request_uri is not null and cr.request_uri = ? and cr2.request_uri <> ? and cr.timestampper between ? and ? group by cr2.request_uri order by num_referring desc"; 20 private static String GET_TOP_EXTERNAL_REFERRING_PAGES = "select referer, count(request_uri) as num_referring from clickstream_request, clickstream where clickstream.clickstream_id = clickstream_request.clickstream_id and request_order = 0 and request_uri = ? and timestampper between ? and ? group by referer order by num_referring desc"; 21 private static String GET_TOP_USERS = "select user_id, count(request_uri) as num_views from clickstream_request, clickstream where clickstream.clickstream_id = clickstream_request.clickstream_id and request_uri = ? and timestampper between ? and ? group by user_id order by num_views desc"; 22 private static String GET_ALL_USERS = "select distinct user_id, User_.* from clickstream_request, clickstream, User_ where clickstream.clickstream_id = clickstream_request.clickstream_id and clickstream.user_id = User_.userid and request_uri = ? and timestampper between ? and ? "; 23 24 private static String GET_CONTENTS_INODES_VIEWS = "select query_string as num_views from clickstream_request where request_uri = ? and timestampper between ? and ?"; 25 private static String GET_CONTENTS_INODES_UNIQUE_VISITORS = "select distinct query_string, cookie_id from clickstream_request, clickstream where clickstream.clickstream_id = clickstream_request.clickstream_id and request_uri = ? and timestampper between ? and ?"; 26 27 public static int getTotalHTMLPageViewsBetweenDates(String uri, java.util.Date startDate, java.util.Date endDate) { 28 DotConnect db = new DotConnect(); 29 db.setSQL(GET_TOTAL_HTML_PAGE_VIEWS); 30 db.addParam(uri); 31 db.addParam(startDate); 32 db.addParam(endDate); 33 db.getResult(); 34 return db.getInt("num_views"); 35 } 36 37 public static int getUniqueVisitorsBetweenDates(String uri, java.util.Date startDate, java.util.Date endDate) { 38 DotConnect db = new DotConnect(); 39 db.setSQL(GET_UNIQUE_VISITORS); 40 db.addParam(uri); 41 db.addParam(startDate); 42 db.addParam(endDate); 43 db.getResult(); 44 return db.getInt("num_views"); 45 } 46 47 public static java.util.List getTotalHTMLPageViewsByLanguageBetweenDates(String uri, java.util.Date startDate, 48 java.util.Date endDate) { 49 DotConnect db = new DotConnect(); 50 db.setSQL(GET_TOTAL_HTML_PAGE_VIEWS_BY_LANGUAGE); 51 db.addParam(uri); 52 db.addParam(startDate); 53 db.addParam(endDate); 54 db.getResult(); 55 return db.getResults(); 56 } 57 58 public static java.util.List getTopInternalReferringPages(String uri, java.util.Date startDate, java.util.Date endDate) { 59 DotConnect db = new DotConnect(); 60 db.setSQL(GET_TOP_INTERNAL_REFERRING_PAGES); 61 db.addParam(uri); 62 db.addParam(uri); 63 db.addParam(startDate); 64 db.addParam(endDate); 65 db.setMaxRows(5); 66 db.getResult(); 67 return db.getResults(); 68 } 69 70 public static java.util.List getTopInternalOutgoingPages(String uri, java.util.Date startDate, java.util.Date endDate) { 71 DotConnect db = new DotConnect(); 72 db.setSQL(GET_TOP_INTERNAL_EXIT_PAGES); 73 db.addParam(uri); 74 db.addParam(uri); 75 db.addParam(startDate); 76 db.addParam(endDate); 77 db.setMaxRows(5); 78 db.getResult(); 79 return db.getResults(); 80 } 81 public static java.util.List getTopExternalReferringPages(String uri, java.util.Date startDate, java.util.Date endDate) { 82 DotConnect db = new DotConnect(); 83 db.setSQL(GET_TOP_EXTERNAL_REFERRING_PAGES); 84 db.addParam(uri); 85 db.addParam(startDate); 86 db.addParam(endDate); 87 db.setMaxRows(5); 88 db.getResult(); 89 return db.getResults(); 90 } 91 92 public static java.util.List getTopUsers(String uri, java.util.Date startDate, java.util.Date endDate) { 93 DotConnect db = new DotConnect(); 94 db.setSQL(GET_TOP_USERS); 95 db.addParam(uri); 96 db.addParam(startDate); 97 db.addParam(endDate); 98 db.setMaxRows(5); 99 db.getResult(); 100 return db.getResults(); 101 } 102 103 public static java.util.List getAllUsers(String uri, java.util.Date startDate, java.util.Date endDate) { 104 DotConnect db = new DotConnect(); 105 db.setSQL(GET_ALL_USERS); 106 db.addParam(uri); 107 db.addParam(startDate); 108 db.addParam(endDate); 109 db.getResult(); 110 return db.getResults(); 111 } 112 113 public static java.util.List <Long > getContentsInodesViewsBetweenDates(String uri, java.util.Date startDate, java.util.Date endDate) { 114 DotConnect db = new DotConnect(); 115 db.setSQL(GET_CONTENTS_INODES_VIEWS); 116 db.addParam(uri); 117 db.addParam(startDate); 118 db.addParam(endDate); 119 java.util.List <java.util.HashMap <String , String >> result = db.getResults(); 120 java.util.List <Long > inodes = new java.util.ArrayList <Long >(); 121 122 java.util.StringTokenizer parameters; 123 String parameter; 124 String inode; 125 Long longInode; 126 int pos; 127 128 for (java.util.HashMap <String , String > row: result) { 129 inode = row.get("num_views"); 130 131 if ((inode != null) && (!inode.trim().equals(""))) { 132 parameters = new java.util.StringTokenizer (inode, "&"); 133 134 for (; parameters.hasMoreTokens();) { 135 parameter = parameters.nextToken(); 136 137 if (parameter.indexOf("inode=") == 0) { 138 try { 139 if (inodes.size() == 0) 140 inodes.add(new Long (parameter.substring(parameter.indexOf("=")+1))); 141 else { 142 longInode = new Long (parameter.substring(parameter.indexOf("=")+1)); 143 144 for (pos = 0; pos < inodes.size(); ++pos) { 145 if (longInode <= inodes.get(pos)) 146 break; 147 } 148 inodes.add(pos, longInode); 149 } 150 } catch (NumberFormatException e) { 151 } 152 } 153 } 154 } 155 } 156 157 return inodes; 158 } 159 160 public static java.util.List <Long > getContentsInodesUniqueVisitorsBetweenDates(String uri, java.util.Date startDate, java.util.Date endDate) { 161 DotConnect db = new DotConnect(); 162 db.setSQL(GET_CONTENTS_INODES_UNIQUE_VISITORS); 163 db.addParam(uri); 164 db.addParam(startDate); 165 db.addParam(endDate); 166 java.util.List <java.util.HashMap <String , String >> result = db.getResults(); 167 java.util.List <Long > inodes = new java.util.ArrayList <Long >(); 168 169 java.util.StringTokenizer parameters; 170 String parameter; 171 String inode; 172 Long longInode; 173 int pos; 174 175 for (java.util.HashMap <String , String > row: result) { 176 inode = row.get("query_string"); 177 178 if ((inode != null) && (!inode.trim().equals(""))) { 179 parameters = new java.util.StringTokenizer (inode, "&"); 180 181 for (; parameters.hasMoreTokens();) { 182 parameter = parameters.nextToken(); 183 184 if (parameter.indexOf("inode=") == 0) { 185 try { 186 if (inodes.size() == 0) 187 inodes.add(new Long (parameter.substring(parameter.indexOf("=")+1))); 188 else { 189 longInode = new Long (parameter.substring(parameter.indexOf("=")+1)); 190 191 for (pos = 0; pos < inodes.size(); ++pos) { 192 if (longInode <= inodes.get(pos)) 193 break; 194 } 195 inodes.add(pos, longInode); 196 } 197 } catch (NumberFormatException e) { 198 } 199 } 200 } 201 } 202 } 203 204 return inodes; 205 } 206 } | Popular Tags |