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 \r
  41 import org.json.JSONException;\r
  42 import org.json.JSONObject;\r
  43 import org.onap.dmaap.datarouter.provisioning.utils.DB;\r
  44 \r
  45 /**\r
  46  * Generate a feeds report.  The report is a .CSV file.\r
  47  *\r
  48  * @author Robert P. Eby\r
  49  * @version $Id: FeedReport.java,v 1.2 2013/11/06 16:23:55 eby Exp $\r
  50  */\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
  61 \r
  62     @Override\r
  63     public void run() {\r
  64         boolean alg1 = true;\r
  65         JSONObject jo = new JSONObject();\r
  66         long start = System.currentTimeMillis();\r
  67         StringBuilder sb = new StringBuilder();\r
  68         try {\r
  69             DB db = new DB();\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
  76             while (rs.next()) {\r
  77                 if (alg1) {\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
  84                 } else {\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
  90                     }\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
  97                     }\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
 105                     }\r
 106                 }\r
 107             }\r
 108             rs.close();\r
 109             ps.close();\r
 110             db.release(conn);\r
 111         } catch (SQLException e) {\r
 112             e.printStackTrace();\r
 113         }\r
 114         logger.debug("Query time: " + (System.currentTimeMillis() - start) + " ms");\r
 115         try {\r
 116             PrintWriter os = new PrintWriter(outfile);\r
 117             if (alg1) {\r
 118                 os.print("date,type,feedid,subid,count\n");\r
 119                 os.print(sb.toString());\r
 120             } else {\r
 121                 os.println(toHTML(jo));\r
 122             }\r
 123             os.close();\r
 124         } catch (FileNotFoundException e) {\r
 125             System.err.println("File cannot be written: " + outfile);\r
 126         }\r
 127     }\r
 128 \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
 133         try {\r
 134             DB db = new DB();\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
 139             ps.setLong(2, to);\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
 149                 }\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
 156                 }\r
 157                 String type = rs.getString("TYPE");\r
 158                 if (type.equals("pub")) {\r
 159                     try {\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
 164                     }\r
 165                 } else if (type.equals("del")) {\r
 166                     String subid = "" + rs.getInt("DELIVERY_SUBID");\r
 167                     try {\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
 172                     }\r
 173                 }\r
 174             }\r
 175             rs.close();\r
 176             ps.close();\r
 177             db.release(conn);\r
 178         } catch (SQLException e) {\r
 179             e.printStackTrace();\r
 180         }\r
 181         logger.debug("Query time: " + (System.currentTimeMillis() - start) + " ms");\r
 182         try {\r
 183             PrintWriter os = new PrintWriter(outfile);\r
 184             os.println(toHTML(jo));\r
 185             os.close();\r
 186         } catch (FileNotFoundException e) {\r
 187             System.err.println("File cannot be written: " + outfile);\r
 188         }\r
 189     }\r
 190 \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
 195     }\r
 196 \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
 216                 Arrays.sort(subs);\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
 221                 if (scnt > 1)\r
 222                     s.append("s");\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
 230                     }\r
 231                 }\r
 232                 s.append("</table>\n");\r
 233 \r
 234                 s.append("</td></tr>\n");\r
 235             }\r
 236             s.append("</table>\n");\r
 237             s.append("</td></tr>\n");\r
 238         }\r
 239         s.append("</table>\n");\r
 240         return s.toString();\r
 241     }\r
 242 \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
 256                     .append(date)\r
 257                     .append("</td>");\r
 258             s.append("<td rowspan=\"" + rc1 + "\">")\r
 259                     .append(feeds.length)\r
 260                     .append("</td>");\r
 261             String px1 = "";\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
 268                 Arrays.sort(subs);\r
 269                 s.append(px1)\r
 270                         .append("<td rowspan=\"" + rc2 + "\">")\r
 271                         .append(feed)\r
 272                         .append("</td>");\r
 273                 s.append("<td rowspan=\"" + rc2 + "\">")\r
 274                         .append(pubcount)\r
 275                         .append("</td>");\r
 276                 s.append("<td rowspan=\"" + rc2 + "\">")\r
 277                         .append(subcnt)\r
 278                         .append("</td>");\r
 279                 String px2 = "";\r
 280                 for (String sub : subs) {\r
 281                     if (!sub.equals("pubcount")) {\r
 282                         s.append(px2);\r
 283                         s.append("<td>" + sub + "</td>");\r
 284                         s.append("<td>" + j3.getInt(sub) + "</td>");\r
 285                         s.append("</tr>\n");\r
 286                         px2 = "<tr>";\r
 287                     }\r
 288                 }\r
 289                 if (px2.equals(""))\r
 290                     s.append("<td></td><td></td></tr>\n");\r
 291                 px1 = "<tr>";\r
 292             }\r
 293         }\r
 294         s.append("</table>\n");\r
 295         return s.toString();\r
 296     }\r
 297 \r
 298     private static int countrows(JSONObject x) {\r
 299         int n = 0;\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
 304             n += rc2;\r
 305         }\r
 306         return (n > 0) ? n : 1;\r
 307     }\r
 308 \r
 309     /**\r
 310      * Convert a .CSV file (as generated by the normal FeedReport mechanism) to an HTML table.\r
 311      *\r
 312      * @param args\r
 313      */\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
 321                     case 'w':\r
 322                         rtype = 1;\r
 323                         break;\r
 324                     case 'm':\r
 325                         rtype = 2;\r
 326                         break;\r
 327                     case 'y':\r
 328                         rtype = 3;\r
 329                         break;\r
 330                     default:\r
 331                         rtype = 0;\r
 332                         break;\r
 333                 }\r
 334             } else if (infile == null) {\r
 335                 infile = args[i];\r
 336             } else if (outfile == null) {\r
 337                 outfile = args[i];\r
 338             }\r
 339         }\r
 340         if (infile == null) {\r
 341             System.err.println("usage: FeedReport [ -t <reporttype> ] [ <input .csv> ] [ <output .html> ]");\r
 342             System.exit(1);\r
 343         }\r
 344         try {\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
 352                     switch (rtype) {\r
 353                         case 1:\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
 357                             break;\r
 358                         case 2:\r
 359                             date = date.substring(0, 7);\r
 360                             break;\r
 361                         case 3:\r
 362                             date = date.substring(0, 4);\r
 363                             break;\r
 364                     }\r
 365                     JSONObject datemap = jo.optJSONObject(date);\r
 366                     if (datemap == null) {\r
 367                         datemap = new JSONObject();\r
 368                         jo.put(date, datemap);\r
 369                     }\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
 376                     }\r
 377                     String type = tt[1];\r
 378                     int count = Integer.parseInt(tt[4]);\r
 379                     if (type.equals("pub")) {\r
 380                         try {\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
 385                         }\r
 386                     } else if (type.equals("del")) {\r
 387                         String subid = tt[3];\r
 388                         try {\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
 393                         }\r
 394                     }\r
 395                 }\r
 396                 line = lr.readLine();\r
 397             }\r
 398             lr.close();\r
 399             String t = toHTML(jo);\r
 400             switch (rtype) {\r
 401                 case 1:\r
 402                     t = t.replaceAll("<th>Date</th>", "<th>Week</th>");\r
 403                     break;\r
 404                 case 2:\r
 405                     t = t.replaceAll("<th>Date</th>", "<th>Month</th>");\r
 406                     break;\r
 407                 case 3:\r
 408                     t = t.replaceAll("<th>Date</th>", "<th>Year</th>");\r
 409                     break;\r
 410             }\r
 411             System.out.println(t);\r
 412         } catch (Exception e) {\r
 413             System.err.println(e);\r
 414             e.printStackTrace();\r
 415         }\r
 416     }\r
 417 }\r