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
75 String date = rs.getString("date");
\r
76 String type = rs.getString("type");
\r
77 int feedid = rs.getInt("feedid");
\r
78 int subid = type.equals("del") ? rs.getInt("delivery_subid") : 0;
\r
79 int count = rs.getInt("count");
\r
80 sb.append(date + "," + type + "," + feedid + "," + subid + "," + count + "\n");
\r
85 } catch (SQLException e) {
\r
86 logger.error(e.toString());
\r
88 logger.debug("Query time: " + (System.currentTimeMillis() - start) + " ms");
\r
89 try (PrintWriter os = new PrintWriter(outfile)) {
\r
90 os.print("date,type,feedid,subid,count\n");
\r
91 os.print(sb.toString());
\r
92 } catch (FileNotFoundException e) {
\r
93 System.err.println("File cannot be written: " + outfile);
\r
94 logger.error(e.toString());
\r
98 public void run2() {
\r
99 JSONObject jo = new JSONObject();
\r
100 SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
\r
101 long start = System.currentTimeMillis();
\r
104 @SuppressWarnings("resource")
\r
105 Connection conn = db.getConnection();
\r
106 try(PreparedStatement ps = conn.prepareStatement(SELECT_SQL_OLD)) {
\r
107 ps.setLong(1, from);
\r
109 ps.setFetchSize(100000);
\r
110 try(ResultSet rs = ps.executeQuery()) {
\r
111 while (rs.next()) {
\r
112 String id = rs.getString("PUBLISH_ID");
\r
113 String date = sdf.format(new Date(getPstart(id)));
\r
114 JSONObject datemap = jo.optJSONObject(date);
\r
115 if (datemap == null) {
\r
116 datemap = new JSONObject();
\r
117 jo.put(date, datemap);
\r
119 int feed = rs.getInt("FEEDID");
\r
120 JSONObject feedmap = datemap.optJSONObject("" + feed);
\r
121 if (feedmap == null) {
\r
122 feedmap = new JSONObject();
\r
123 feedmap.put("pubcount", 0);
\r
124 datemap.put("" + feed, feedmap);
\r
126 String type = rs.getString("TYPE");
\r
127 if (type.equals("pub")) {
\r
129 int n = feedmap.getInt("pubcount");
\r
130 feedmap.put("pubcount", n + 1);
\r
131 } catch (JSONException e) {
\r
132 feedmap.put("pubcount", 1);
\r
133 logger.error(e.toString());
\r
135 } else if (type.equals("del")) {
\r
136 String subid = "" + rs.getInt("DELIVERY_SUBID");
\r
138 int n = feedmap.getInt(subid);
\r
139 feedmap.put(subid, n + 1);
\r
140 } catch (JSONException e) {
\r
141 feedmap.put(subid, 1);
\r
142 logger.error(e.toString());
\r
149 } catch (SQLException e) {
\r
150 logger.error(e.toString());
\r
152 logger.debug("Query time: " + (System.currentTimeMillis() - start) + " ms");
\r
154 PrintWriter os = new PrintWriter(outfile);
\r
155 os.println(toHTML(jo));
\r
157 } catch (FileNotFoundException e) {
\r
158 System.err.println("File cannot be written: " + outfile);
\r
159 logger.error(e.toString());
\r
163 private long getPstart(String t) {
\r
164 if (t.indexOf('.') > 0)
\r
165 t = t.substring(0, t.indexOf('.'));
\r
166 return Long.parseLong(t);
\r
169 @SuppressWarnings("unused")
\r
170 private static String toHTMLNested(JSONObject jo) {
\r
171 StringBuilder s = new StringBuilder();
\r
172 s.append("<table>\n");
\r
173 s.append("<tr><th>Date</th><th>Feeds</th></tr>\n");
\r
174 String[] dates = JSONObject.getNames(jo);
\r
175 Arrays.sort(dates);
\r
176 for (int i = dates.length - 1; i >= 0; i--) {
\r
177 String date = dates[i];
\r
178 JSONObject j2 = jo.getJSONObject(date);
\r
179 String[] feeds = JSONObject.getNames(j2);
\r
180 Arrays.sort(feeds);
\r
181 s.append("<tr><td>" + date + "</td><td>");
\r
182 s.append(feeds.length).append(feeds.length > 1 ? " Feeds\n" : " Feed\n");
\r
183 s.append("<table>\n");
\r
184 s.append("<tr><th>Feed ID</th><th>Publish Count</th><th>Subscriptions</th></tr>\n");
\r
185 for (String feed : feeds) {
\r
186 JSONObject j3 = j2.getJSONObject(feed);
\r
187 String[] subs = JSONObject.getNames(j3);
\r
189 s.append("<tr><td>" + feed + "</td>");
\r
190 s.append("<td>" + j3.getInt("pubcount") + "</td>");
\r
191 int scnt = j3.length() - 1;
\r
192 s.append("<td>").append(scnt).append(" Subcription");
\r
195 s.append("<table>\n");
\r
196 s.append("<tr><th>Sub ID</th><th>Delivery Count</th></tr>\n");
\r
197 for (String sub : subs) {
\r
198 if (!sub.equals("pubcount")) {
\r
199 s.append("<tr><td>" + sub + "</td>");
\r
200 s.append("<td>" + j3.getInt(sub) + "</td>");
\r
201 s.append("</td></tr>\n");
\r
204 s.append("</table>\n");
\r
206 s.append("</td></tr>\n");
\r
208 s.append("</table>\n");
\r
209 s.append("</td></tr>\n");
\r
211 s.append("</table>\n");
\r
212 return s.toString();
\r
215 private static String toHTML(JSONObject jo) {
\r
216 StringBuilder s = new StringBuilder();
\r
217 s.append("<table>\n");
\r
218 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
219 String[] dates = JSONObject.getNames(jo);
\r
220 Arrays.sort(dates);
\r
221 for (int i = dates.length - 1; i >= 0; i--) {
\r
222 String date = dates[i];
\r
223 JSONObject j2 = jo.getJSONObject(date);
\r
224 int rc1 = countrows(j2);
\r
225 String[] feeds = JSONObject.getNames(j2);
\r
226 Arrays.sort(feeds);
\r
227 s.append("<tr><td rowspan=\"" + rc1 + "\">")
\r
230 s.append("<td rowspan=\"" + rc1 + "\">")
\r
231 .append(feeds.length)
\r
234 for (String feed : feeds) {
\r
235 JSONObject j3 = j2.getJSONObject(feed);
\r
236 int pubcount = j3.getInt("pubcount");
\r
237 int subcnt = j3.length() - 1;
\r
238 int rc2 = (subcnt < 1) ? 1 : subcnt;
\r
239 String[] subs = JSONObject.getNames(j3);
\r
242 .append("<td rowspan=\"" + rc2 + "\">")
\r
245 s.append("<td rowspan=\"" + rc2 + "\">")
\r
248 s.append("<td rowspan=\"" + rc2 + "\">")
\r
252 for (String sub : subs) {
\r
253 if (!sub.equals("pubcount")) {
\r
255 s.append("<td>" + sub + "</td>");
\r
256 s.append("<td>" + j3.getInt(sub) + "</td>");
\r
257 s.append("</tr>\n");
\r
261 if (px2.equals(""))
\r
262 s.append("<td></td><td></td></tr>\n");
\r
266 s.append("</table>\n");
\r
267 return s.toString();
\r
270 private static int countrows(JSONObject x) {
\r
272 for (String feed : JSONObject.getNames(x)) {
\r
273 JSONObject j3 = x.getJSONObject(feed);
\r
274 int subcnt = j3.length() - 1;
\r
275 int rc2 = (subcnt < 1) ? 1 : subcnt;
\r
278 return (n > 0) ? n : 1;
\r
282 * Convert a .CSV file (as generated by the normal FeedReport mechanism) to an HTML table.
\r
286 public void main(String[] args) {
\r
287 int rtype = 0; // 0 -> day, 1 -> week, 2 -> month, 3 -> year
\r
288 String infile = null;
\r
289 String outfile = null;
\r
290 for (int i = 0; i < args.length; i++) {
\r
291 if (args[i].equals("-t")) {
\r
292 switch (args[++i].charAt(0)) {
\r
306 } else if (infile == null) {
\r
308 } else if (outfile == null) {
\r
312 if (infile == null) {
\r
313 System.err.println("usage: FeedReport [ -t <reporttype> ] [ <input .csv> ] [ <output .html> ]");
\r
317 JSONObject jo = new JSONObject();
\r
318 try(LineNumberReader lr = new LineNumberReader(new FileReader(infile))) {
\r
319 String line = lr.readLine();
\r
320 while (line != null) {
\r
321 String[] tt = line.split(",");
\r
322 if (tt[0].startsWith("2")) {
\r
323 String date = tt[0];
\r
326 String[] xx = date.split("-");
\r
327 Calendar cal = new GregorianCalendar(new Integer(xx[0]), new Integer(xx[1]) - 1, new Integer(xx[2]));
\r
328 date = xx[0] + "-W" + cal.get(Calendar.WEEK_OF_YEAR);
\r
331 date = date.substring(0, 7);
\r
334 date = date.substring(0, 4);
\r
337 JSONObject datemap = jo.optJSONObject(date);
\r
338 if (datemap == null) {
\r
339 datemap = new JSONObject();
\r
340 jo.put(date, datemap);
\r
342 int feed = Integer.parseInt(tt[2]);
\r
343 JSONObject feedmap = datemap.optJSONObject("" + feed);
\r
344 if (feedmap == null) {
\r
345 feedmap = new JSONObject();
\r
346 feedmap.put("pubcount", 0);
\r
347 datemap.put("" + feed, feedmap);
\r
349 String type = tt[1];
\r
350 int count = Integer.parseInt(tt[4]);
\r
351 if (type.equals("pub")) {
\r
353 int n = feedmap.getInt("pubcount");
\r
354 feedmap.put("pubcount", n + count);
\r
355 } catch (JSONException e) {
\r
356 feedmap.put("pubcount", count);
\r
357 logger.error(e.toString());
\r
359 } else if (type.equals("del")) {
\r
360 String subid = tt[3];
\r
362 int n = feedmap.getInt(subid);
\r
363 feedmap.put(subid, n + count);
\r
364 } catch (JSONException e) {
\r
365 feedmap.put(subid, count);
\r
366 logger.error(e.toString());
\r
370 line = lr.readLine();
\r
373 String t = toHTML(jo);
\r
376 t = t.replaceAll("<th>Date</th>", "<th>Week</th>");
\r
379 t = t.replaceAll("<th>Date</th>", "<th>Month</th>");
\r
382 t = t.replaceAll("<th>Date</th>", "<th>Year</th>");
\r
385 System.out.println(t);
\r
386 } catch (Exception e) {
\r
387 System.err.println(e);
\r
388 logger.error(e.toString());
\r