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 org.onap.dmaap.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
43 import org.onap.dmaap.datarouter.provisioning.utils.DB;
\r
46 * Generate a feeds report. The report is a .CSV file.
\r
48 * @author Robert P. Eby
\r
49 * @version $Id: FeedReport.java,v 1.2 2013/11/06 16:23:55 eby Exp $
\r
51 public class FeedReport extends ReportBase {
\r
52 private static final String SELECT_SQL =
\r
53 // Note to use the time in the publish_id, use date(from_unixtime(substring(publish_id, 1, 10)))
\r
54 // To just use month, substring(from_unixtime(event_time div 1000), 1, 7)
\r
55 "select date(from_unixtime(event_time div 1000)) as date, type, feedid, delivery_subid, count(*) as count" +
\r
56 " from LOG_RECORDS" +
\r
57 " where type = 'pub' or type = 'del'" +
\r
58 " group by date, type, feedid, delivery_subid";
\r
59 private static final String SELECT_SQL_OLD =
\r
60 "select PUBLISH_ID, TYPE, FEEDID, DELIVERY_SUBID from LOG_RECORDS where EVENT_TIME >= ? and EVENT_TIME <= ?";
\r
64 boolean alg1 = true;
\r
65 JSONObject jo = new JSONObject();
\r
66 long start = System.currentTimeMillis();
\r
67 StringBuilder sb = new StringBuilder();
\r
70 @SuppressWarnings("resource")
\r
71 Connection conn = db.getConnection();
\r
72 try( PreparedStatement ps = conn.prepareStatement(SELECT_SQL)) {
\r
73 try (ResultSet rs = ps.executeQuery()) {
\r
76 String date = rs.getString("date");
\r
77 String type = rs.getString("type");
\r
78 int feedid = rs.getInt("feedid");
\r
79 int subid = type.equals("del") ? rs.getInt("delivery_subid") : 0;
\r
80 int count = rs.getInt("count");
\r
81 sb.append(date + "," + type + "," + feedid + "," + subid + "," + count + "\n");
\r
83 String date = rs.getString("date");
\r
84 JSONObject datemap = jo.optJSONObject(date);
\r
85 if (datemap == null) {
\r
86 datemap = new JSONObject();
\r
87 jo.put(date, datemap);
\r
89 int feed = rs.getInt("FEEDID");
\r
90 JSONObject feedmap = datemap.optJSONObject("" + feed);
\r
91 if (feedmap == null) {
\r
92 feedmap = new JSONObject();
\r
93 feedmap.put("pubcount", 0);
\r
94 datemap.put("" + feed, feedmap);
\r
96 String type = rs.getString("TYPE");
\r
97 int count = rs.getInt("count");
\r
98 if (type.equals("pub")) {
\r
99 feedmap.put("pubcount", count);
\r
100 } else if (type.equals("del")) {
\r
101 String subid = "" + rs.getInt("DELIVERY_SUBID");
\r
102 feedmap.put(subid, count);
\r
109 } catch (SQLException e) {
\r
110 e.printStackTrace();
\r
112 logger.debug("Query time: " + (System.currentTimeMillis() - start) + " ms");
\r
114 PrintWriter os = new PrintWriter(outfile);
\r
116 os.print("date,type,feedid,subid,count\n");
\r
117 os.print(sb.toString());
\r
119 os.println(toHTML(jo));
\r
122 } catch (FileNotFoundException e) {
\r
123 System.err.println("File cannot be written: " + outfile);
\r
127 public void run2() {
\r
128 JSONObject jo = new JSONObject();
\r
129 SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
\r
130 long start = System.currentTimeMillis();
\r
133 @SuppressWarnings("resource")
\r
134 Connection conn = db.getConnection();
\r
135 try(PreparedStatement ps = conn.prepareStatement(SELECT_SQL_OLD)) {
\r
136 ps.setLong(1, from);
\r
138 ps.setFetchSize(100000);
\r
139 try(ResultSet rs = ps.executeQuery()) {
\r
140 while (rs.next()) {
\r
141 String id = rs.getString("PUBLISH_ID");
\r
142 String date = sdf.format(new Date(getPstart(id)));
\r
143 JSONObject datemap = jo.optJSONObject(date);
\r
144 if (datemap == null) {
\r
145 datemap = new JSONObject();
\r
146 jo.put(date, datemap);
\r
148 int feed = rs.getInt("FEEDID");
\r
149 JSONObject feedmap = datemap.optJSONObject("" + feed);
\r
150 if (feedmap == null) {
\r
151 feedmap = new JSONObject();
\r
152 feedmap.put("pubcount", 0);
\r
153 datemap.put("" + feed, feedmap);
\r
155 String type = rs.getString("TYPE");
\r
156 if (type.equals("pub")) {
\r
158 int n = feedmap.getInt("pubcount");
\r
159 feedmap.put("pubcount", n + 1);
\r
160 } catch (JSONException e) {
\r
161 feedmap.put("pubcount", 1);
\r
163 } else if (type.equals("del")) {
\r
164 String subid = "" + rs.getInt("DELIVERY_SUBID");
\r
166 int n = feedmap.getInt(subid);
\r
167 feedmap.put(subid, n + 1);
\r
168 } catch (JSONException e) {
\r
169 feedmap.put(subid, 1);
\r
176 } catch (SQLException e) {
\r
177 e.printStackTrace();
\r
179 logger.debug("Query time: " + (System.currentTimeMillis() - start) + " ms");
\r
181 PrintWriter os = new PrintWriter(outfile);
\r
182 os.println(toHTML(jo));
\r
184 } catch (FileNotFoundException e) {
\r
185 System.err.println("File cannot be written: " + outfile);
\r
189 private long getPstart(String t) {
\r
190 if (t.indexOf('.') > 0)
\r
191 t = t.substring(0, t.indexOf('.'));
\r
192 return Long.parseLong(t);
\r
195 @SuppressWarnings("unused")
\r
196 private static String toHTMLNested(JSONObject jo) {
\r
197 StringBuilder s = new StringBuilder();
\r
198 s.append("<table>\n");
\r
199 s.append("<tr><th>Date</th><th>Feeds</th></tr>\n");
\r
200 String[] dates = JSONObject.getNames(jo);
\r
201 Arrays.sort(dates);
\r
202 for (int i = dates.length - 1; i >= 0; i--) {
\r
203 String date = dates[i];
\r
204 JSONObject j2 = jo.getJSONObject(date);
\r
205 String[] feeds = JSONObject.getNames(j2);
\r
206 Arrays.sort(feeds);
\r
207 s.append("<tr><td>" + date + "</td><td>");
\r
208 s.append(feeds.length).append(feeds.length > 1 ? " Feeds\n" : " Feed\n");
\r
209 s.append("<table>\n");
\r
210 s.append("<tr><th>Feed ID</th><th>Publish Count</th><th>Subscriptions</th></tr>\n");
\r
211 for (String feed : feeds) {
\r
212 JSONObject j3 = j2.getJSONObject(feed);
\r
213 String[] subs = JSONObject.getNames(j3);
\r
215 s.append("<tr><td>" + feed + "</td>");
\r
216 s.append("<td>" + j3.getInt("pubcount") + "</td>");
\r
217 int scnt = j3.length() - 1;
\r
218 s.append("<td>").append(scnt).append(" Subcription");
\r
221 s.append("<table>\n");
\r
222 s.append("<tr><th>Sub ID</th><th>Delivery Count</th></tr>\n");
\r
223 for (String sub : subs) {
\r
224 if (!sub.equals("pubcount")) {
\r
225 s.append("<tr><td>" + sub + "</td>");
\r
226 s.append("<td>" + j3.getInt(sub) + "</td>");
\r
227 s.append("</td></tr>\n");
\r
230 s.append("</table>\n");
\r
232 s.append("</td></tr>\n");
\r
234 s.append("</table>\n");
\r
235 s.append("</td></tr>\n");
\r
237 s.append("</table>\n");
\r
238 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
296 private static int countrows(JSONObject x) {
\r
298 for (String feed : JSONObject.getNames(x)) {
\r
299 JSONObject j3 = x.getJSONObject(feed);
\r
300 int subcnt = j3.length() - 1;
\r
301 int rc2 = (subcnt < 1) ? 1 : subcnt;
\r
304 return (n > 0) ? n : 1;
\r
308 * Convert a .CSV file (as generated by the normal FeedReport mechanism) to an HTML table.
\r
312 public static void main(String[] args) {
\r
313 int rtype = 0; // 0 -> day, 1 -> week, 2 -> month, 3 -> year
\r
314 String infile = null;
\r
315 String outfile = null;
\r
316 for (int i = 0; i < args.length; i++) {
\r
317 if (args[i].equals("-t")) {
\r
318 switch (args[++i].charAt(0)) {
\r
332 } else if (infile == null) {
\r
334 } else if (outfile == null) {
\r
338 if (infile == null) {
\r
339 System.err.println("usage: FeedReport [ -t <reporttype> ] [ <input .csv> ] [ <output .html> ]");
\r
343 JSONObject jo = new JSONObject();
\r
344 try(LineNumberReader lr = new LineNumberReader(new FileReader(infile))) {
\r
345 String line = lr.readLine();
\r
346 while (line != null) {
\r
347 String[] tt = line.split(",");
\r
348 if (tt[0].startsWith("2")) {
\r
349 String date = tt[0];
\r
352 String[] xx = date.split("-");
\r
353 Calendar cal = new GregorianCalendar(new Integer(xx[0]), new Integer(xx[1]) - 1, new Integer(xx[2]));
\r
354 date = xx[0] + "-W" + cal.get(Calendar.WEEK_OF_YEAR);
\r
357 date = date.substring(0, 7);
\r
360 date = date.substring(0, 4);
\r
363 JSONObject datemap = jo.optJSONObject(date);
\r
364 if (datemap == null) {
\r
365 datemap = new JSONObject();
\r
366 jo.put(date, datemap);
\r
368 int feed = Integer.parseInt(tt[2]);
\r
369 JSONObject feedmap = datemap.optJSONObject("" + feed);
\r
370 if (feedmap == null) {
\r
371 feedmap = new JSONObject();
\r
372 feedmap.put("pubcount", 0);
\r
373 datemap.put("" + feed, feedmap);
\r
375 String type = tt[1];
\r
376 int count = Integer.parseInt(tt[4]);
\r
377 if (type.equals("pub")) {
\r
379 int n = feedmap.getInt("pubcount");
\r
380 feedmap.put("pubcount", n + count);
\r
381 } catch (JSONException e) {
\r
382 feedmap.put("pubcount", count);
\r
384 } else if (type.equals("del")) {
\r
385 String subid = tt[3];
\r
387 int n = feedmap.getInt(subid);
\r
388 feedmap.put(subid, n + count);
\r
389 } catch (JSONException e) {
\r
390 feedmap.put(subid, count);
\r
394 line = lr.readLine();
\r
397 String t = toHTML(jo);
\r
400 t = t.replaceAll("<th>Date</th>", "<th>Week</th>");
\r
403 t = t.replaceAll("<th>Date</th>", "<th>Month</th>");
\r
406 t = t.replaceAll("<th>Date</th>", "<th>Year</th>");
\r
409 System.out.println(t);
\r
410 } catch (Exception e) {
\r
411 System.err.println(e);
\r
412 e.printStackTrace();
\r