1 /*******************************************************************************
\r
2 * ============LICENSE_START==================================================
\r
4 * * ===========================================================================
\r
5 * * Copyright © 2017 AT&T Intellectual Property. All rights reserved.
\r
6 * * ===========================================================================
\r
7 * * Licensed under the Apache License, Version 2.0 (the "License");
\r
8 * * you may not use this file except in compliance with the License.
\r
9 * * You may obtain a copy of the License at
\r
11 * * http://www.apache.org/licenses/LICENSE-2.0
\r
13 * * Unless required by applicable law or agreed to in writing, software
\r
14 * * distributed under the License is distributed on an "AS IS" BASIS,
\r
15 * * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
\r
16 * * See the License for the specific language governing permissions and
\r
17 * * limitations under the License.
\r
18 * * ============LICENSE_END====================================================
\r
20 * * ECOMP is a trademark and service mark of AT&T Intellectual Property.
\r
22 ******************************************************************************/
\r
25 package com.att.research.datarouter.reports;
\r
27 import java.io.FileNotFoundException;
\r
28 import java.io.FileReader;
\r
29 import java.io.LineNumberReader;
\r
30 import java.io.PrintWriter;
\r
31 import java.sql.Connection;
\r
32 import java.sql.PreparedStatement;
\r
33 import java.sql.ResultSet;
\r
34 import java.sql.SQLException;
\r
35 import java.text.SimpleDateFormat;
\r
36 import java.util.Arrays;
\r
37 import java.util.Calendar;
\r
38 import java.util.Date;
\r
39 import java.util.GregorianCalendar;
\r
41 import org.json.JSONException;
\r
42 import org.json.JSONObject;
\r
44 import com.att.research.datarouter.provisioning.utils.DB;
\r
47 * Generate a feeds report. The report is a .CSV file.
\r
49 * @author Robert P. Eby
\r
50 * @version $Id: FeedReport.java,v 1.2 2013/11/06 16:23:55 eby Exp $
\r
52 public class FeedReport extends ReportBase {
\r
53 private static final String SELECT_SQL =
\r
54 // Note to use the time in the publish_id, use date(from_unixtime(substring(publish_id, 1, 10)))
\r
55 // To just use month, substring(from_unixtime(event_time div 1000), 1, 7)
\r
56 "select date(from_unixtime(event_time div 1000)) as date, type, feedid, delivery_subid, count(*) as count" +
\r
57 " from LOG_RECORDS" +
\r
58 " where type = 'pub' or type = 'del'" +
\r
59 " group by date, type, feedid, delivery_subid";
\r
60 private static final String SELECT_SQL_OLD =
\r
61 "select PUBLISH_ID, TYPE, FEEDID, DELIVERY_SUBID from LOG_RECORDS where EVENT_TIME >= ? and EVENT_TIME <= ?";
\r
65 boolean alg1 = true;
\r
66 JSONObject jo = new JSONObject();
\r
67 long start = System.currentTimeMillis();
\r
68 StringBuilder sb = new StringBuilder();
\r
71 @SuppressWarnings("resource")
\r
72 Connection conn = db.getConnection();
\r
73 PreparedStatement ps = conn.prepareStatement(SELECT_SQL);
\r
74 // ps.setLong(1, from);
\r
75 // ps.setLong(2, to);
\r
76 ResultSet rs = ps.executeQuery();
\r
79 String date = rs.getString("date");
\r
80 String type = rs.getString("type");
\r
81 int feedid = rs.getInt("feedid");
\r
82 int subid = type.equals("del") ? rs.getInt("delivery_subid") : 0;
\r
83 int count = rs.getInt("count");
\r
84 sb.append(date + "," + type + "," + feedid + "," + subid + "," + count + "\n");
\r
86 String date = rs.getString("date");
\r
87 JSONObject datemap = jo.optJSONObject(date);
\r
88 if (datemap == null) {
\r
89 datemap = new JSONObject();
\r
90 jo.put(date, datemap);
\r
92 int feed = rs.getInt("FEEDID");
\r
93 JSONObject feedmap = datemap.optJSONObject(""+feed);
\r
94 if (feedmap == null) {
\r
95 feedmap = new JSONObject();
\r
96 feedmap.put("pubcount", 0);
\r
97 datemap.put(""+feed, feedmap);
\r
99 String type = rs.getString("TYPE");
\r
100 int count = rs.getInt("count");
\r
101 if (type.equals("pub")) {
\r
102 feedmap.put("pubcount", count);
\r
103 } else if (type.equals("del")) {
\r
104 String subid = ""+rs.getInt("DELIVERY_SUBID");
\r
105 feedmap.put(subid, count);
\r
112 } catch (SQLException e) {
\r
113 e.printStackTrace();
\r
115 logger.debug("Query time: " + (System.currentTimeMillis()-start) + " ms");
\r
117 PrintWriter os = new PrintWriter(outfile);
\r
119 os.print("date,type,feedid,subid,count\n");
\r
120 os.print(sb.toString());
\r
122 os.println(toHTML(jo));
\r
125 } catch (FileNotFoundException e) {
\r
126 System.err.println("File cannot be written: "+outfile);
\r
130 public void run2() {
\r
131 JSONObject jo = new JSONObject();
\r
132 SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
\r
133 long start = System.currentTimeMillis();
\r
136 @SuppressWarnings("resource")
\r
137 Connection conn = db.getConnection();
\r
138 PreparedStatement ps = conn.prepareStatement(SELECT_SQL_OLD);
\r
139 ps.setLong(1, from);
\r
141 ps.setFetchSize(100000);
\r
142 ResultSet rs = ps.executeQuery();
\r
143 while (rs.next()) {
\r
144 String id = rs.getString("PUBLISH_ID");
\r
145 String date = sdf.format(new Date(getPstart(id)));
\r
146 JSONObject datemap = jo.optJSONObject(date);
\r
147 if (datemap == null) {
\r
148 datemap = new JSONObject();
\r
149 jo.put(date, datemap);
\r
151 int feed = rs.getInt("FEEDID");
\r
152 JSONObject feedmap = datemap.optJSONObject(""+feed);
\r
153 if (feedmap == null) {
\r
154 feedmap = new JSONObject();
\r
155 feedmap.put("pubcount", 0);
\r
156 datemap.put(""+feed, feedmap);
\r
158 String type = rs.getString("TYPE");
\r
159 if (type.equals("pub")) {
\r
161 int n = feedmap.getInt("pubcount");
\r
162 feedmap.put("pubcount", n+1);
\r
163 } catch (JSONException e) {
\r
164 feedmap.put("pubcount", 1);
\r
166 } else if (type.equals("del")) {
\r
167 String subid = ""+rs.getInt("DELIVERY_SUBID");
\r
169 int n = feedmap.getInt(subid);
\r
170 feedmap.put(subid, n+1);
\r
171 } catch (JSONException e) {
\r
172 feedmap.put(subid, 1);
\r
179 } catch (SQLException e) {
\r
180 e.printStackTrace();
\r
182 logger.debug("Query time: " + (System.currentTimeMillis()-start) + " ms");
\r
184 PrintWriter os = new PrintWriter(outfile);
\r
185 os.println(toHTML(jo));
\r
187 } catch (FileNotFoundException e) {
\r
188 System.err.println("File cannot be written: "+outfile);
\r
191 private long getPstart(String t) {
\r
192 if (t.indexOf('.') > 0)
\r
193 t = t.substring(0, t.indexOf('.'));
\r
194 return Long.parseLong(t);
\r
196 @SuppressWarnings("unused")
\r
197 private static String toHTMLNested(JSONObject jo) {
\r
198 StringBuilder s = new StringBuilder();
\r
199 s.append("<table>\n");
\r
200 s.append("<tr><th>Date</th><th>Feeds</th></tr>\n");
\r
201 String[] dates = JSONObject.getNames(jo);
\r
202 Arrays.sort(dates);
\r
203 for (int i = dates.length-1; i >= 0; i--) {
\r
204 String date = dates[i];
\r
205 JSONObject j2 = jo.getJSONObject(date);
\r
206 String[] feeds = JSONObject.getNames(j2);
\r
207 Arrays.sort(feeds);
\r
208 s.append("<tr><td>"+date+"</td><td>");
\r
209 s.append(feeds.length).append(feeds.length > 1 ? " Feeds\n" : " Feed\n");
\r
210 s.append("<table>\n");
\r
211 s.append("<tr><th>Feed ID</th><th>Publish Count</th><th>Subscriptions</th></tr>\n");
\r
212 for (String feed : feeds) {
\r
213 JSONObject j3 = j2.getJSONObject(feed);
\r
214 String[] subs = JSONObject.getNames(j3);
\r
216 s.append("<tr><td>"+feed+"</td>");
\r
217 s.append("<td>"+j3.getInt("pubcount")+"</td>");
\r
218 int scnt = j3.length()-1;
\r
219 s.append("<td>").append(scnt).append(" Subcription");
\r
222 s.append("<table>\n");
\r
223 s.append("<tr><th>Sub ID</th><th>Delivery Count</th></tr>\n");
\r
224 for (String sub : subs) {
\r
225 if (!sub.equals("pubcount")) {
\r
226 s.append("<tr><td>"+sub+"</td>");
\r
227 s.append("<td>"+j3.getInt(sub)+"</td>");
\r
228 s.append("</td></tr>\n");
\r
231 s.append("</table>\n");
\r
233 s.append("</td></tr>\n");
\r
235 s.append("</table>\n");
\r
236 s.append("</td></tr>\n");
\r
238 s.append("</table>\n");
\r
239 return s.toString();
\r
241 private static String toHTML(JSONObject jo) {
\r
242 StringBuilder s = new StringBuilder();
\r
243 s.append("<table>\n");
\r
244 s.append("<tr><th>Date</th><th>Feeds</th><th>Feed ID</th><th>Publish Count</th><th>Subs</th><th>Sub ID</th><th>Delivery Count</th></tr>\n");
\r
245 String[] dates = JSONObject.getNames(jo);
\r
246 Arrays.sort(dates);
\r
247 for (int i = dates.length-1; i >= 0; i--) {
\r
248 String date = dates[i];
\r
249 JSONObject j2 = jo.getJSONObject(date);
\r
250 int rc1 = countrows(j2);
\r
251 String[] feeds = JSONObject.getNames(j2);
\r
252 Arrays.sort(feeds);
\r
253 s.append("<tr><td rowspan=\"" + rc1 + "\">")
\r
256 s.append("<td rowspan=\"" + rc1 + "\">")
\r
257 .append(feeds.length)
\r
260 for (String feed : feeds) {
\r
261 JSONObject j3 = j2.getJSONObject(feed);
\r
262 int pubcount = j3.getInt("pubcount");
\r
263 int subcnt = j3.length()-1;
\r
264 int rc2 = (subcnt < 1) ? 1 : subcnt;
\r
265 String[] subs = JSONObject.getNames(j3);
\r
268 .append("<td rowspan=\"" + rc2 + "\">")
\r
271 s.append("<td rowspan=\"" + rc2 + "\">")
\r
274 s.append("<td rowspan=\"" + rc2 + "\">")
\r
278 for (String sub : subs) {
\r
279 if (!sub.equals("pubcount")) {
\r
281 s.append("<td>"+sub+"</td>");
\r
282 s.append("<td>"+j3.getInt(sub)+"</td>");
\r
283 s.append("</tr>\n");
\r
287 if (px2.equals(""))
\r
288 s.append("<td></td><td></td></tr>\n");
\r
292 s.append("</table>\n");
\r
293 return s.toString();
\r
295 private static int countrows(JSONObject x) {
\r
297 for (String feed : JSONObject.getNames(x)) {
\r
298 JSONObject j3 = x.getJSONObject(feed);
\r
299 int subcnt = j3.length()-1;
\r
300 int rc2 = (subcnt < 1) ? 1 : subcnt;
\r
303 return (n > 0) ? n : 1;
\r
307 * Convert a .CSV file (as generated by the normal FeedReport mechanism) to an HTML table.
\r
310 public static void main(String[] args) {
\r
311 int rtype = 0; // 0 -> day, 1 -> week, 2 -> month, 3 -> year
\r
312 String infile = null;
\r
313 String outfile = null;
\r
314 for (int i = 0; i < args.length; i++) {
\r
315 if (args[i].equals("-t")) {
\r
316 switch (args[++i].charAt(0)) {
\r
317 case 'w': rtype = 1; break;
\r
318 case 'm': rtype = 2; break;
\r
319 case 'y': rtype = 3; break;
\r
320 default: rtype = 0; break;
\r
322 } else if (infile == null) {
\r
324 } else if (outfile == null) {
\r
328 if (infile == null) {
\r
329 System.err.println("usage: FeedReport [ -t <reporttype> ] [ <input .csv> ] [ <output .html> ]");
\r
333 JSONObject jo = new JSONObject();
\r
334 LineNumberReader lr = new LineNumberReader(new FileReader(infile));
\r
335 String line = lr.readLine();
\r
336 while (line != null) {
\r
337 String[] tt = line.split(",");
\r
338 if (tt[0].startsWith("2")) {
\r
339 String date = tt[0];
\r
342 String[] xx = date.split("-");
\r
343 Calendar cal = new GregorianCalendar(new Integer(xx[0]), new Integer(xx[1])-1, new Integer(xx[2]));
\r
344 date = xx[0] + "-W" + cal.get(Calendar.WEEK_OF_YEAR);
\r
346 case 2: date = date.substring(0, 7); break;
\r
347 case 3: date = date.substring(0, 4); break;
\r
349 JSONObject datemap = jo.optJSONObject(date);
\r
350 if (datemap == null) {
\r
351 datemap = new JSONObject();
\r
352 jo.put(date, datemap);
\r
354 int feed = Integer.parseInt(tt[2]);
\r
355 JSONObject feedmap = datemap.optJSONObject(""+feed);
\r
356 if (feedmap == null) {
\r
357 feedmap = new JSONObject();
\r
358 feedmap.put("pubcount", 0);
\r
359 datemap.put(""+feed, feedmap);
\r
361 String type = tt[1];
\r
362 int count = Integer.parseInt(tt[4]);
\r
363 if (type.equals("pub")) {
\r
365 int n = feedmap.getInt("pubcount");
\r
366 feedmap.put("pubcount", n+count);
\r
367 } catch (JSONException e) {
\r
368 feedmap.put("pubcount", count);
\r
370 } else if (type.equals("del")) {
\r
371 String subid = tt[3];
\r
373 int n = feedmap.getInt(subid);
\r
374 feedmap.put(subid, n+count);
\r
375 } catch (JSONException e) {
\r
376 feedmap.put(subid, count);
\r
380 line = lr.readLine();
\r
383 String t = toHTML(jo);
\r
385 case 1: t = t.replaceAll("<th>Date</th>", "<th>Week</th>"); break;
\r
386 case 2: t = t.replaceAll("<th>Date</th>", "<th>Month</th>"); break;
\r
387 case 3: t = t.replaceAll("<th>Date</th>", "<th>Year</th>"); break;
\r
389 System.out.println(t);
\r
390 } catch (Exception e) {
\r
391 System.err.println(e);
\r
392 e.printStackTrace();
\r