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
40 import org.json.JSONException;
\r
41 import org.json.JSONObject;
\r
42 import org.onap.dmaap.datarouter.provisioning.utils.ProvDbUtils;
\r
45 * Generate a feeds report. The report is a .CSV file.
\r
47 * @author Robert P. Eby
\r
48 * @version $Id: FeedReport.java,v 1.2 2013/11/06 16:23:55 eby Exp $
\r
50 public class FeedReport extends ReportBase {
\r
54 boolean alg1 = true;
\r
55 JSONObject jo = new JSONObject();
\r
56 long start = System.currentTimeMillis();
\r
57 StringBuilder sb = new StringBuilder();
\r
58 try (Connection conn = ProvDbUtils.getInstance().getConnection();
\r
59 PreparedStatement ps = conn.prepareStatement(
\r
60 // Note to use the time in the publish_id, use date(from_unixtime(substring(publish_id, 1, 10)))
\r
61 // To just use month, substring(from_unixtime(event_time div 1000), 1, 7)
\r
62 "select date(from_unixtime(event_time div 1000)) as date, type, feedid, delivery_subid, count(*) "
\r
63 + "as count from LOG_RECORDS where type = 'pub' or type = 'del' group by date, type, feedid, delivery_subid")) {
\r
64 try (ResultSet rs = ps.executeQuery()) {
\r
66 String date = rs.getString("date");
\r
67 String type = rs.getString("type");
\r
68 int feedid = rs.getInt("feedid");
\r
69 int subid = type.equals("del") ? rs.getInt("delivery_subid") : 0;
\r
70 int count = rs.getInt("count");
\r
71 sb.append(date + "," + type + "," + feedid + "," + subid + "," + count + "\n");
\r
74 } catch (SQLException e) {
\r
75 logger.error(e.toString());
\r
77 logger.debug("Query time: " + (System.currentTimeMillis() - start) + " ms");
\r
78 try (PrintWriter os = new PrintWriter(outfile)) {
\r
79 os.print("date,type,feedid,subid,count\n");
\r
80 os.print(sb.toString());
\r
81 } catch (FileNotFoundException e) {
\r
82 System.err.println("File cannot be written: " + outfile);
\r
83 logger.error(e.toString());
\r
87 public void run2() {
\r
88 JSONObject jo = new JSONObject();
\r
89 SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
\r
90 long start = System.currentTimeMillis();
\r
91 try (Connection conn = ProvDbUtils.getInstance().getConnection();
\r
92 PreparedStatement ps = conn.prepareStatement(
\r
93 "select PUBLISH_ID, TYPE, FEEDID, DELIVERY_SUBID from LOG_RECORDS "
\r
94 + "where EVENT_TIME >= ? and EVENT_TIME <= ?")) {
\r
95 ps.setLong(1, from);
\r
97 ps.setFetchSize(100000);
\r
98 try(ResultSet rs = ps.executeQuery()) {
\r
100 String id = rs.getString("PUBLISH_ID");
\r
101 String date = sdf.format(new Date(getPstart(id)));
\r
102 JSONObject datemap = jo.optJSONObject(date);
\r
103 if (datemap == null) {
\r
104 datemap = new JSONObject();
\r
105 jo.put(date, datemap);
\r
107 int feed = rs.getInt("FEEDID");
\r
108 JSONObject feedmap = datemap.optJSONObject("" + feed);
\r
109 if (feedmap == null) {
\r
110 feedmap = new JSONObject();
\r
111 feedmap.put("pubcount", 0);
\r
112 datemap.put("" + feed, feedmap);
\r
114 String type = rs.getString("TYPE");
\r
115 if (type.equals("pub")) {
\r
117 int n = feedmap.getInt("pubcount");
\r
118 feedmap.put("pubcount", n + 1);
\r
119 } catch (JSONException e) {
\r
120 feedmap.put("pubcount", 1);
\r
121 logger.error(e.toString());
\r
123 } else if (type.equals("del")) {
\r
124 String subid = "" + rs.getInt("DELIVERY_SUBID");
\r
126 int n = feedmap.getInt(subid);
\r
127 feedmap.put(subid, n + 1);
\r
128 } catch (JSONException e) {
\r
129 feedmap.put(subid, 1);
\r
130 logger.error(e.toString());
\r
135 } catch (SQLException e) {
\r
136 logger.error(e.toString());
\r
138 logger.debug("Query time: " + (System.currentTimeMillis() - start) + " ms");
\r
140 PrintWriter os = new PrintWriter(outfile);
\r
141 os.println(toHTML(jo));
\r
143 } catch (FileNotFoundException e) {
\r
144 System.err.println("File cannot be written: " + outfile);
\r
145 logger.error(e.toString());
\r
149 private long getPstart(String t) {
\r
150 if (t.indexOf('.') > 0)
\r
151 t = t.substring(0, t.indexOf('.'));
\r
152 return Long.parseLong(t);
\r
155 @SuppressWarnings("unused")
\r
156 private static String toHTMLNested(JSONObject jo) {
\r
157 StringBuilder s = new StringBuilder();
\r
158 s.append("<table>\n");
\r
159 s.append("<tr><th>Date</th><th>Feeds</th></tr>\n");
\r
160 String[] dates = JSONObject.getNames(jo);
\r
161 Arrays.sort(dates);
\r
162 for (int i = dates.length - 1; i >= 0; i--) {
\r
163 String date = dates[i];
\r
164 JSONObject j2 = jo.getJSONObject(date);
\r
165 String[] feeds = JSONObject.getNames(j2);
\r
166 Arrays.sort(feeds);
\r
167 s.append("<tr><td>" + date + "</td><td>");
\r
168 s.append(feeds.length).append(feeds.length > 1 ? " Feeds\n" : " Feed\n");
\r
169 s.append("<table>\n");
\r
170 s.append("<tr><th>Feed ID</th><th>Publish Count</th><th>Subscriptions</th></tr>\n");
\r
171 for (String feed : feeds) {
\r
172 JSONObject j3 = j2.getJSONObject(feed);
\r
173 String[] subs = JSONObject.getNames(j3);
\r
175 s.append("<tr><td>" + feed + "</td>");
\r
176 s.append("<td>" + j3.getInt("pubcount") + "</td>");
\r
177 int scnt = j3.length() - 1;
\r
178 s.append("<td>").append(scnt).append(" Subcription");
\r
181 s.append("<table>\n");
\r
182 s.append("<tr><th>Sub ID</th><th>Delivery Count</th></tr>\n");
\r
183 for (String sub : subs) {
\r
184 if (!sub.equals("pubcount")) {
\r
185 s.append("<tr><td>" + sub + "</td>");
\r
186 s.append("<td>" + j3.getInt(sub) + "</td>");
\r
187 s.append("</td></tr>\n");
\r
190 s.append("</table>\n");
\r
192 s.append("</td></tr>\n");
\r
194 s.append("</table>\n");
\r
195 s.append("</td></tr>\n");
\r
197 s.append("</table>\n");
\r
198 return s.toString();
\r
201 private static String toHTML(JSONObject jo) {
\r
202 StringBuilder s = new StringBuilder();
\r
203 s.append("<table>\n");
\r
204 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
205 String[] dates = JSONObject.getNames(jo);
\r
206 Arrays.sort(dates);
\r
207 for (int i = dates.length - 1; i >= 0; i--) {
\r
208 String date = dates[i];
\r
209 JSONObject j2 = jo.getJSONObject(date);
\r
210 int rc1 = countrows(j2);
\r
211 String[] feeds = JSONObject.getNames(j2);
\r
212 Arrays.sort(feeds);
\r
213 s.append("<tr><td rowspan=\"" + rc1 + "\">")
\r
216 s.append("<td rowspan=\"" + rc1 + "\">")
\r
217 .append(feeds.length)
\r
220 for (String feed : feeds) {
\r
221 JSONObject j3 = j2.getJSONObject(feed);
\r
222 int pubcount = j3.getInt("pubcount");
\r
223 int subcnt = j3.length() - 1;
\r
224 int rc2 = (subcnt < 1) ? 1 : subcnt;
\r
225 String[] subs = JSONObject.getNames(j3);
\r
228 .append("<td rowspan=\"" + rc2 + "\">")
\r
231 s.append("<td rowspan=\"" + rc2 + "\">")
\r
234 s.append("<td rowspan=\"" + rc2 + "\">")
\r
238 for (String sub : subs) {
\r
239 if (!sub.equals("pubcount")) {
\r
241 s.append("<td>" + sub + "</td>");
\r
242 s.append("<td>" + j3.getInt(sub) + "</td>");
\r
243 s.append("</tr>\n");
\r
247 if (px2.equals(""))
\r
248 s.append("<td></td><td></td></tr>\n");
\r
252 s.append("</table>\n");
\r
253 return s.toString();
\r
256 private static int countrows(JSONObject x) {
\r
258 for (String feed : JSONObject.getNames(x)) {
\r
259 JSONObject j3 = x.getJSONObject(feed);
\r
260 int subcnt = j3.length() - 1;
\r
261 int rc2 = (subcnt < 1) ? 1 : subcnt;
\r
264 return (n > 0) ? n : 1;
\r
268 * Convert a .CSV file (as generated by the normal FeedReport mechanism) to an HTML table.
\r
272 public void main(String[] args) {
\r
273 int rtype = 0; // 0 -> day, 1 -> week, 2 -> month, 3 -> year
\r
274 String infile = null;
\r
275 String outfile = null;
\r
276 for (int i = 0; i < args.length; i++) {
\r
277 if (args[i].equals("-t")) {
\r
278 switch (args[++i].charAt(0)) {
\r
292 } else if (infile == null) {
\r
294 } else if (outfile == null) {
\r
298 if (infile == null) {
\r
299 System.err.println("usage: FeedReport [ -t <reporttype> ] [ <input .csv> ] [ <output .html> ]");
\r
303 JSONObject jo = new JSONObject();
\r
304 try(LineNumberReader lr = new LineNumberReader(new FileReader(infile))) {
\r
305 String line = lr.readLine();
\r
306 while (line != null) {
\r
307 String[] tt = line.split(",");
\r
308 if (tt[0].startsWith("2")) {
\r
309 String date = tt[0];
\r
312 String[] xx = date.split("-");
\r
313 Calendar cal = new GregorianCalendar(new Integer(xx[0]), new Integer(xx[1]) - 1, new Integer(xx[2]));
\r
314 date = xx[0] + "-W" + cal.get(Calendar.WEEK_OF_YEAR);
\r
317 date = date.substring(0, 7);
\r
320 date = date.substring(0, 4);
\r
323 JSONObject datemap = jo.optJSONObject(date);
\r
324 if (datemap == null) {
\r
325 datemap = new JSONObject();
\r
326 jo.put(date, datemap);
\r
328 int feed = Integer.parseInt(tt[2]);
\r
329 JSONObject feedmap = datemap.optJSONObject("" + feed);
\r
330 if (feedmap == null) {
\r
331 feedmap = new JSONObject();
\r
332 feedmap.put("pubcount", 0);
\r
333 datemap.put("" + feed, feedmap);
\r
335 String type = tt[1];
\r
336 int count = Integer.parseInt(tt[4]);
\r
337 if (type.equals("pub")) {
\r
339 int n = feedmap.getInt("pubcount");
\r
340 feedmap.put("pubcount", n + count);
\r
341 } catch (JSONException e) {
\r
342 feedmap.put("pubcount", count);
\r
343 logger.error(e.toString());
\r
345 } else if (type.equals("del")) {
\r
346 String subid = tt[3];
\r
348 int n = feedmap.getInt(subid);
\r
349 feedmap.put(subid, n + count);
\r
350 } catch (JSONException e) {
\r
351 feedmap.put(subid, count);
\r
352 logger.error(e.toString());
\r
356 line = lr.readLine();
\r
359 String t = toHTML(jo);
\r
362 t = t.replaceAll("<th>Date</th>", "<th>Week</th>");
\r
365 t = t.replaceAll("<th>Date</th>", "<th>Month</th>");
\r
368 t = t.replaceAll("<th>Date</th>", "<th>Year</th>");
\r
371 System.out.println(t);
\r
372 } catch (Exception e) {
\r
373 System.err.println(e);
\r
374 logger.error(e.toString());
\r