datarouter-prov/src/main/java/org/onap/dmaap/datarouter/reports/FeedReport.java

   1 /*******************************************************************************\r
   2  * ============LICENSE_START==================================================\r
   3  * * org.onap.dmaap\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
  10  * *\r
  11  *  *      http://www.apache.org/licenses/LICENSE-2.0\r
  12  * *\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
  19  * *\r
  20  * * ECOMP is a trademark and service mark of AT&T Intellectual Property.\r
  21  * *\r
  22  ******************************************************************************/\r
  23 \r
  24 \r
  25 package org.onap.dmaap.datarouter.reports;\r
  26 \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
  43 \r
  44 /**\r
  45  * Generate a feeds report.  The report is a .CSV file.\r
  46  *\r
  47  * @author Robert P. Eby\r
  48  * @version $Id: FeedReport.java,v 1.2 2013/11/06 16:23:55 eby Exp $\r
  49  */\r
  50 public class FeedReport extends ReportBase {\r
  51 \r
  52     @Override\r
  53     public void run() {\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
  65                 while (rs.next()) {\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
  72                 }\r
  73             }\r
  74         } catch (SQLException e) {\r
  75             logger.error(e.toString());\r
  76         }\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
  84         }\r
  85     }\r
  86 \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
  96             ps.setLong(2, to);\r
  97             ps.setFetchSize(100000);\r
  98             try(ResultSet rs = ps.executeQuery()) {\r
  99                 while (rs.next()) {\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
 106                     }\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
 113                     }\r
 114                     String type = rs.getString("TYPE");\r
 115                     if (type.equals("pub")) {\r
 116                         try {\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
 122                         }\r
 123                     } else if (type.equals("del")) {\r
 124                         String subid = "" + rs.getInt("DELIVERY_SUBID");\r
 125                         try {\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
 131                         }\r
 132                     }\r
 133                 }\r
 134             }\r
 135         } catch (SQLException e) {\r
 136             logger.error(e.toString());\r
 137         }\r
 138         logger.debug("Query time: " + (System.currentTimeMillis() - start) + " ms");\r
 139         try {\r
 140             PrintWriter os = new PrintWriter(outfile);\r
 141             os.println(toHTML(jo));\r
 142             os.close();\r
 143         } catch (FileNotFoundException e) {\r
 144             System.err.println("File cannot be written: " + outfile);\r
 145             logger.error(e.toString());\r
 146         }\r
 147     }\r
 148 \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
 153     }\r
 154 \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
 174                 Arrays.sort(subs);\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
 179                 if (scnt > 1)\r
 180                     s.append("s");\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
 188                     }\r
 189                 }\r
 190                 s.append("</table>\n");\r
 191 \r
 192                 s.append("</td></tr>\n");\r
 193             }\r
 194             s.append("</table>\n");\r
 195             s.append("</td></tr>\n");\r
 196         }\r
 197         s.append("</table>\n");\r
 198         return s.toString();\r
 199     }\r
 200 \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
 214                 .append(date)\r
 215                 .append("</td>");\r
 216             s.append("<td rowspan=\"" + rc1 + "\">")\r
 217                 .append(feeds.length)\r
 218                 .append("</td>");\r
 219             String px1 = "";\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
 226                 Arrays.sort(subs);\r
 227                 s.append(px1)\r
 228                     .append("<td rowspan=\"" + rc2 + "\">")\r
 229                     .append(feed)\r
 230                     .append("</td>");\r
 231                 s.append("<td rowspan=\"" + rc2 + "\">")\r
 232                     .append(pubcount)\r
 233                     .append("</td>");\r
 234                 s.append("<td rowspan=\"" + rc2 + "\">")\r
 235                     .append(subcnt)\r
 236                     .append("</td>");\r
 237                 String px2 = "";\r
 238                 for (String sub : subs) {\r
 239                     if (!sub.equals("pubcount")) {\r
 240                         s.append(px2);\r
 241                         s.append("<td>" + sub + "</td>");\r
 242                         s.append("<td>" + j3.getInt(sub) + "</td>");\r
 243                         s.append("</tr>\n");\r
 244                         px2 = "<tr>";\r
 245                     }\r
 246                 }\r
 247                 if (px2.equals(""))\r
 248                     s.append("<td></td><td></td></tr>\n");\r
 249                 px1 = "<tr>";\r
 250             }\r
 251         }\r
 252         s.append("</table>\n");\r
 253         return s.toString();\r
 254     }\r
 255 \r
 256     private static int countrows(JSONObject x) {\r
 257         int n = 0;\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
 262             n += rc2;\r
 263         }\r
 264         return (n > 0) ? n : 1;\r
 265     }\r
 266 \r
 267     /**\r
 268      * Convert a .CSV file (as generated by the normal FeedReport mechanism) to an HTML table.\r
 269      *\r
 270      * @param args\r
 271      */\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
 279                     case 'w':\r
 280                         rtype = 1;\r
 281                         break;\r
 282                     case 'm':\r
 283                         rtype = 2;\r
 284                         break;\r
 285                     case 'y':\r
 286                         rtype = 3;\r
 287                         break;\r
 288                     default:\r
 289                         rtype = 0;\r
 290                         break;\r
 291                 }\r
 292             } else if (infile == null) {\r
 293                 infile = args[i];\r
 294             } else if (outfile == null) {\r
 295                 outfile = args[i];\r
 296             }\r
 297         }\r
 298         if (infile == null) {\r
 299             System.err.println("usage: FeedReport [ -t <reporttype> ] [ <input .csv> ] [ <output .html> ]");\r
 300             System.exit(1);\r
 301         }\r
 302         try {\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
 310                         switch (rtype) {\r
 311                             case 1:\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
 315                                 break;\r
 316                             case 2:\r
 317                                 date = date.substring(0, 7);\r
 318                                 break;\r
 319                             case 3:\r
 320                                 date = date.substring(0, 4);\r
 321                                 break;\r
 322                         }\r
 323                         JSONObject datemap = jo.optJSONObject(date);\r
 324                         if (datemap == null) {\r
 325                             datemap = new JSONObject();\r
 326                             jo.put(date, datemap);\r
 327                         }\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
 334                         }\r
 335                         String type = tt[1];\r
 336                         int count = Integer.parseInt(tt[4]);\r
 337                         if (type.equals("pub")) {\r
 338                             try {\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
 344                             }\r
 345                         } else if (type.equals("del")) {\r
 346                             String subid = tt[3];\r
 347                             try {\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
 353                             }\r
 354                         }\r
 355                     }\r
 356                     line = lr.readLine();\r
 357                 }\r
 358             }\r
 359             String t = toHTML(jo);\r
 360             switch (rtype) {\r
 361                 case 1:\r
 362                     t = t.replaceAll("<th>Date</th>", "<th>Week</th>");\r
 363                     break;\r
 364                 case 2:\r
 365                     t = t.replaceAll("<th>Date</th>", "<th>Month</th>");\r
 366                     break;\r
 367                 case 3:\r
 368                     t = t.replaceAll("<th>Date</th>", "<th>Year</th>");\r
 369                     break;\r
 370             }\r
 371             System.out.println(t);\r
 372         } catch (Exception e) {\r
 373             System.err.println(e);\r
 374             logger.error(e.toString());\r
 375         }\r
 376     }\r
 377 }\r