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 PreparedStatement ps = conn.prepareStatement(SELECT_SQL);
\r
73 // ps.setLong(1, from);
\r
74 // ps.setLong(2, to);
\r
75 ResultSet rs = ps.executeQuery();
\r
78 String date = rs.getString("date");
\r
79 String type = rs.getString("type");
\r
80 int feedid = rs.getInt("feedid");
\r
81 int subid = type.equals("del") ? rs.getInt("delivery_subid") : 0;
\r
82 int count = rs.getInt("count");
\r
83 sb.append(date + "," + type + "," + feedid + "," + subid + "," + count + "\n");
\r
85 String date = rs.getString("date");
\r
86 JSONObject datemap = jo.optJSONObject(date);
\r
87 if (datemap == null) {
\r
88 datemap = new JSONObject();
\r
89 jo.put(date, datemap);
\r
91 int feed = rs.getInt("FEEDID");
\r
92 JSONObject feedmap = datemap.optJSONObject("" + feed);
\r
93 if (feedmap == null) {
\r
94 feedmap = new JSONObject();
\r
95 feedmap.put("pubcount", 0);
\r
96 datemap.put("" + feed, feedmap);
\r
98 String type = rs.getString("TYPE");
\r
99 int count = rs.getInt("count");
\r
100 if (type.equals("pub")) {
\r
101 feedmap.put("pubcount", count);
\r
102 } else if (type.equals("del")) {
\r
103 String subid = "" + rs.getInt("DELIVERY_SUBID");
\r
104 feedmap.put(subid, count);
\r
111 } catch (SQLException e) {
\r
112 e.printStackTrace();
\r
114 logger.debug("Query time: " + (System.currentTimeMillis() - start) + " ms");
\r
116 PrintWriter os = new PrintWriter(outfile);
\r
118 os.print("date,type,feedid,subid,count\n");
\r
119 os.print(sb.toString());
\r
121 os.println(toHTML(jo));
\r
124 } catch (FileNotFoundException e) {
\r
125 System.err.println("File cannot be written: " + outfile);
\r
129 public void run2() {
\r
130 JSONObject jo = new JSONObject();
\r
131 SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
\r
132 long start = System.currentTimeMillis();
\r
135 @SuppressWarnings("resource")
\r
136 Connection conn = db.getConnection();
\r
137 PreparedStatement ps = conn.prepareStatement(SELECT_SQL_OLD);
\r
138 ps.setLong(1, from);
\r
140 ps.setFetchSize(100000);
\r
141 ResultSet rs = ps.executeQuery();
\r
142 while (rs.next()) {
\r
143 String id = rs.getString("PUBLISH_ID");
\r
144 String date = sdf.format(new Date(getPstart(id)));
\r
145 JSONObject datemap = jo.optJSONObject(date);
\r
146 if (datemap == null) {
\r
147 datemap = new JSONObject();
\r
148 jo.put(date, datemap);
\r
150 int feed = rs.getInt("FEEDID");
\r
151 JSONObject feedmap = datemap.optJSONObject("" + feed);
\r
152 if (feedmap == null) {
\r
153 feedmap = new JSONObject();
\r
154 feedmap.put("pubcount", 0);
\r
155 datemap.put("" + feed, feedmap);
\r
157 String type = rs.getString("TYPE");
\r
158 if (type.equals("pub")) {
\r
160 int n = feedmap.getInt("pubcount");
\r
161 feedmap.put("pubcount", n + 1);
\r
162 } catch (JSONException e) {
\r
163 feedmap.put("pubcount", 1);
\r
165 } else if (type.equals("del")) {
\r
166 String subid = "" + rs.getInt("DELIVERY_SUBID");
\r
168 int n = feedmap.getInt(subid);
\r
169 feedmap.put(subid, n + 1);
\r
170 } catch (JSONException e) {
\r
171 feedmap.put(subid, 1);
\r
178 } catch (SQLException e) {
\r
179 e.printStackTrace();
\r
181 logger.debug("Query time: " + (System.currentTimeMillis() - start) + " ms");
\r
183 PrintWriter os = new PrintWriter(outfile);
\r
184 os.println(toHTML(jo));
\r
186 } catch (FileNotFoundException e) {
\r
187 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
197 @SuppressWarnings("unused")
\r
198 private static String toHTMLNested(JSONObject jo) {
\r
199 StringBuilder s = new StringBuilder();
\r
200 s.append("<table>\n");
\r
201 s.append("<tr><th>Date</th><th>Feeds</th></tr>\n");
\r
202 String[] dates = JSONObject.getNames(jo);
\r
203 Arrays.sort(dates);
\r
204 for (int i = dates.length - 1; i >= 0; i--) {
\r
205 String date = dates[i];
\r
206 JSONObject j2 = jo.getJSONObject(date);
\r
207 String[] feeds = JSONObject.getNames(j2);
\r
208 Arrays.sort(feeds);
\r
209 s.append("<tr><td>" + date + "</td><td>");
\r
210 s.append(feeds.length).append(feeds.length > 1 ? " Feeds\n" : " Feed\n");
\r
211 s.append("<table>\n");
\r
212 s.append("<tr><th>Feed ID</th><th>Publish Count</th><th>Subscriptions</th></tr>\n");
\r
213 for (String feed : feeds) {
\r
214 JSONObject j3 = j2.getJSONObject(feed);
\r
215 String[] subs = JSONObject.getNames(j3);
\r
217 s.append("<tr><td>" + feed + "</td>");
\r
218 s.append("<td>" + j3.getInt("pubcount") + "</td>");
\r
219 int scnt = j3.length() - 1;
\r
220 s.append("<td>").append(scnt).append(" Subcription");
\r
223 s.append("<table>\n");
\r
224 s.append("<tr><th>Sub ID</th><th>Delivery Count</th></tr>\n");
\r
225 for (String sub : subs) {
\r
226 if (!sub.equals("pubcount")) {
\r
227 s.append("<tr><td>" + sub + "</td>");
\r
228 s.append("<td>" + j3.getInt(sub) + "</td>");
\r
229 s.append("</td></tr>\n");
\r
232 s.append("</table>\n");
\r
234 s.append("</td></tr>\n");
\r
236 s.append("</table>\n");
\r
237 s.append("</td></tr>\n");
\r
239 s.append("</table>\n");
\r
240 return s.toString();
\r
243 private static String toHTML(JSONObject jo) {
\r
244 StringBuilder s = new StringBuilder();
\r
245 s.append("<table>\n");
\r
246 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
247 String[] dates = JSONObject.getNames(jo);
\r
248 Arrays.sort(dates);
\r
249 for (int i = dates.length - 1; i >= 0; i--) {
\r
250 String date = dates[i];
\r
251 JSONObject j2 = jo.getJSONObject(date);
\r
252 int rc1 = countrows(j2);
\r
253 String[] feeds = JSONObject.getNames(j2);
\r
254 Arrays.sort(feeds);
\r
255 s.append("<tr><td rowspan=\"" + rc1 + "\">")
\r
258 s.append("<td rowspan=\"" + rc1 + "\">")
\r
259 .append(feeds.length)
\r
262 for (String feed : feeds) {
\r
263 JSONObject j3 = j2.getJSONObject(feed);
\r
264 int pubcount = j3.getInt("pubcount");
\r
265 int subcnt = j3.length() - 1;
\r
266 int rc2 = (subcnt < 1) ? 1 : subcnt;
\r
267 String[] subs = JSONObject.getNames(j3);
\r
270 .append("<td rowspan=\"" + rc2 + "\">")
\r
273 s.append("<td rowspan=\"" + rc2 + "\">")
\r
276 s.append("<td rowspan=\"" + rc2 + "\">")
\r
280 for (String sub : subs) {
\r
281 if (!sub.equals("pubcount")) {
\r
283 s.append("<td>" + sub + "</td>");
\r
284 s.append("<td>" + j3.getInt(sub) + "</td>");
\r
285 s.append("</tr>\n");
\r
289 if (px2.equals(""))
\r
290 s.append("<td></td><td></td></tr>\n");
\r
294 s.append("</table>\n");
\r
295 return s.toString();
\r
298 private static int countrows(JSONObject x) {
\r
300 for (String feed : JSONObject.getNames(x)) {
\r
301 JSONObject j3 = x.getJSONObject(feed);
\r
302 int subcnt = j3.length() - 1;
\r
303 int rc2 = (subcnt < 1) ? 1 : subcnt;
\r
306 return (n > 0) ? n : 1;
\r
310 * Convert a .CSV file (as generated by the normal FeedReport mechanism) to an HTML table.
\r
314 public static void main(String[] args) {
\r
315 int rtype = 0; // 0 -> day, 1 -> week, 2 -> month, 3 -> year
\r
316 String infile = null;
\r
317 String outfile = null;
\r
318 for (int i = 0; i < args.length; i++) {
\r
319 if (args[i].equals("-t")) {
\r
320 switch (args[++i].charAt(0)) {
\r
334 } else if (infile == null) {
\r
336 } else if (outfile == null) {
\r
340 if (infile == null) {
\r
341 System.err.println("usage: FeedReport [ -t <reporttype> ] [ <input .csv> ] [ <output .html> ]");
\r
345 JSONObject jo = new JSONObject();
\r
346 LineNumberReader lr = new LineNumberReader(new FileReader(infile));
\r
347 String line = lr.readLine();
\r
348 while (line != null) {
\r
349 String[] tt = line.split(",");
\r
350 if (tt[0].startsWith("2")) {
\r
351 String date = tt[0];
\r
354 String[] xx = date.split("-");
\r
355 Calendar cal = new GregorianCalendar(new Integer(xx[0]), new Integer(xx[1]) - 1, new Integer(xx[2]));
\r
356 date = xx[0] + "-W" + cal.get(Calendar.WEEK_OF_YEAR);
\r
359 date = date.substring(0, 7);
\r
362 date = date.substring(0, 4);
\r
365 JSONObject datemap = jo.optJSONObject(date);
\r
366 if (datemap == null) {
\r
367 datemap = new JSONObject();
\r
368 jo.put(date, datemap);
\r
370 int feed = Integer.parseInt(tt[2]);
\r
371 JSONObject feedmap = datemap.optJSONObject("" + feed);
\r
372 if (feedmap == null) {
\r
373 feedmap = new JSONObject();
\r
374 feedmap.put("pubcount", 0);
\r
375 datemap.put("" + feed, feedmap);
\r
377 String type = tt[1];
\r
378 int count = Integer.parseInt(tt[4]);
\r
379 if (type.equals("pub")) {
\r
381 int n = feedmap.getInt("pubcount");
\r
382 feedmap.put("pubcount", n + count);
\r
383 } catch (JSONException e) {
\r
384 feedmap.put("pubcount", count);
\r
386 } else if (type.equals("del")) {
\r
387 String subid = tt[3];
\r
389 int n = feedmap.getInt(subid);
\r
390 feedmap.put(subid, n + count);
\r
391 } catch (JSONException e) {
\r
392 feedmap.put(subid, count);
\r
396 line = lr.readLine();
\r
399 String t = toHTML(jo);
\r
402 t = t.replaceAll("<th>Date</th>", "<th>Week</th>");
\r
405 t = t.replaceAll("<th>Date</th>", "<th>Month</th>");
\r
408 t = t.replaceAll("<th>Date</th>", "<th>Year</th>");
\r
411 System.out.println(t);
\r
412 } catch (Exception e) {
\r
413 System.err.println(e);
\r
414 e.printStackTrace();
\r