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         private long getPstart(String t) {\r
 191                 if (t.indexOf('.') > 0)\r
 192                         t = t.substring(0, t.indexOf('.'));\r
 193                 return Long.parseLong(t);\r
 194         }\r
 195         @SuppressWarnings("unused")\r
 196         private static String toHTMLNested(JSONObject jo) {\r
 197                 StringBuilder s = new StringBuilder();\r
 198                 s.append("<table>\n");\r
 199                 s.append("<tr><th>Date</th><th>Feeds</th></tr>\n");\r
 200                 String[] dates = JSONObject.getNames(jo);\r
 201                 Arrays.sort(dates);\r
 202                 for (int i = dates.length-1; i >= 0; i--) {\r
 203                         String date = dates[i];\r
 204                         JSONObject j2 = jo.getJSONObject(date);\r
 205                         String[] feeds = JSONObject.getNames(j2);\r
 206                         Arrays.sort(feeds);\r
 207                         s.append("<tr><td>"+date+"</td><td>");\r
 208                         s.append(feeds.length).append(feeds.length > 1 ? " Feeds\n" : " Feed\n");\r
 209                         s.append("<table>\n");\r
 210                         s.append("<tr><th>Feed ID</th><th>Publish Count</th><th>Subscriptions</th></tr>\n");\r
 211                         for (String feed : feeds) {\r
 212                                 JSONObject j3 = j2.getJSONObject(feed);\r
 213                                 String[] subs = JSONObject.getNames(j3);\r
 214                                 Arrays.sort(subs);\r
 215                                 s.append("<tr><td>"+feed+"</td>");\r
 216                                 s.append("<td>"+j3.getInt("pubcount")+"</td>");\r
 217                                 int scnt = j3.length()-1;\r
 218                                 s.append("<td>").append(scnt).append(" Subcription");\r
 219                                 if (scnt > 1)\r
 220                                         s.append("s");\r
 221                                 s.append("<table>\n");\r
 222                                 s.append("<tr><th>Sub ID</th><th>Delivery Count</th></tr>\n");\r
 223                                 for (String sub : subs) {\r
 224                                         if (!sub.equals("pubcount")) {\r
 225                                                 s.append("<tr><td>"+sub+"</td>");\r
 226                                                 s.append("<td>"+j3.getInt(sub)+"</td>");\r
 227                                                 s.append("</td></tr>\n");\r
 228                                         }\r
 229                                 }\r
 230                                 s.append("</table>\n");\r
 231 \r
 232                                 s.append("</td></tr>\n");\r
 233                         }\r
 234                         s.append("</table>\n");\r
 235                         s.append("</td></tr>\n");\r
 236                 }\r
 237                 s.append("</table>\n");\r
 238                 return s.toString();\r
 239         }\r
 240         private static String toHTML(JSONObject jo) {\r
 241                 StringBuilder s = new StringBuilder();\r
 242                 s.append("<table>\n");\r
 243                 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
 244                 String[] dates = JSONObject.getNames(jo);\r
 245                 Arrays.sort(dates);\r
 246                 for (int i = dates.length-1; i >= 0; i--) {\r
 247                         String date = dates[i];\r
 248                         JSONObject j2 = jo.getJSONObject(date);\r
 249                         int rc1 = countrows(j2);\r
 250                         String[] feeds = JSONObject.getNames(j2);\r
 251                         Arrays.sort(feeds);\r
 252                         s.append("<tr><td rowspan=\"" + rc1 + "\">")\r
 253                          .append(date)\r
 254                          .append("</td>");\r
 255                         s.append("<td rowspan=\"" + rc1 + "\">")\r
 256                          .append(feeds.length)\r
 257                          .append("</td>");\r
 258                         String px1 = "";\r
 259                         for (String feed : feeds) {\r
 260                                 JSONObject j3 = j2.getJSONObject(feed);\r
 261                                 int pubcount = j3.getInt("pubcount");\r
 262                                 int subcnt = j3.length()-1;\r
 263                                 int rc2 = (subcnt < 1) ? 1 : subcnt;\r
 264                                 String[] subs = JSONObject.getNames(j3);\r
 265                                 Arrays.sort(subs);\r
 266                                 s.append(px1)\r
 267                                  .append("<td rowspan=\"" + rc2 + "\">")\r
 268                                  .append(feed)\r
 269                                  .append("</td>");\r
 270                                 s.append("<td rowspan=\"" + rc2 + "\">")\r
 271                                  .append(pubcount)\r
 272                                  .append("</td>");\r
 273                                 s.append("<td rowspan=\"" + rc2 + "\">")\r
 274                                  .append(subcnt)\r
 275                                  .append("</td>");\r
 276                                 String px2 = "";\r
 277                                 for (String sub : subs) {\r
 278                                         if (!sub.equals("pubcount")) {\r
 279                                                 s.append(px2);\r
 280                                                 s.append("<td>"+sub+"</td>");\r
 281                                                 s.append("<td>"+j3.getInt(sub)+"</td>");\r
 282                                                 s.append("</tr>\n");\r
 283                                                 px2 = "<tr>";\r
 284                                         }\r
 285                                 }\r
 286                                 if (px2.equals(""))\r
 287                                         s.append("<td></td><td></td></tr>\n");\r
 288                                 px1 = "<tr>";\r
 289                         }\r
 290                 }\r
 291                 s.append("</table>\n");\r
 292                 return s.toString();\r
 293         }\r
 294         private static int countrows(JSONObject x) {\r
 295                 int n = 0;\r
 296                 for (String feed : JSONObject.getNames(x)) {\r
 297                         JSONObject j3 = x.getJSONObject(feed);\r
 298                         int subcnt = j3.length()-1;\r
 299                         int rc2 = (subcnt < 1) ? 1 : subcnt;\r
 300                         n += rc2;\r
 301                 }\r
 302                 return (n > 0) ? n : 1;\r
 303         }\r
 304 \r
 305         /**\r
 306          * Convert a .CSV file (as generated by the normal FeedReport mechanism) to an HTML table.\r
 307          * @param args\r
 308          */\r
 309         public static void main(String[] args) {\r
 310                 int rtype = 0;  // 0 -> day, 1 -> week, 2 -> month, 3 -> year\r
 311                 String infile  = null;\r
 312                 String outfile = null;\r
 313                 for (int i = 0; i < args.length; i++) {\r
 314                         if (args[i].equals("-t")) {\r
 315                                 switch (args[++i].charAt(0)) {\r
 316                                 case 'w':       rtype = 1; break;\r
 317                                 case 'm':       rtype = 2; break;\r
 318                                 case 'y':       rtype = 3; break;\r
 319                                 default:        rtype = 0; break;\r
 320                                 }\r
 321                         } else if (infile == null) {\r
 322                                 infile = args[i];\r
 323                         } else if (outfile == null) {\r
 324                                 outfile = args[i];\r
 325                         }\r
 326                 }\r
 327                 if (infile == null) {\r
 328                         System.err.println("usage: FeedReport [ -t <reporttype> ] [ <input .csv> ] [ <output .html> ]");\r
 329                         System.exit(1);\r
 330                 }\r
 331                 try {\r
 332                         JSONObject jo = new JSONObject();\r
 333                         LineNumberReader lr = new LineNumberReader(new FileReader(infile));\r
 334                         String line = lr.readLine();\r
 335                         while (line != null) {\r
 336                                 String[] tt = line.split(",");\r
 337                                 if (tt[0].startsWith("2")) {\r
 338                                         String date = tt[0];\r
 339                                         switch (rtype) {\r
 340                                         case 1:\r
 341                                                 String[] xx = date.split("-");\r
 342                                                 Calendar cal = new GregorianCalendar(new Integer(xx[0]), new Integer(xx[1])-1, new Integer(xx[2]));\r
 343                                                 date = xx[0] + "-W" + cal.get(Calendar.WEEK_OF_YEAR);\r
 344                                                 break;\r
 345                                         case 2: date = date.substring(0, 7); break;\r
 346                                         case 3: date = date.substring(0, 4); break;\r
 347                                         }\r
 348                                         JSONObject datemap = jo.optJSONObject(date);\r
 349                                         if (datemap == null) {\r
 350                                                 datemap = new JSONObject();\r
 351                                                 jo.put(date, datemap);\r
 352                                         }\r
 353                                         int feed = Integer.parseInt(tt[2]);\r
 354                                         JSONObject feedmap = datemap.optJSONObject(""+feed);\r
 355                                         if (feedmap == null) {\r
 356                                                 feedmap = new JSONObject();\r
 357                                                 feedmap.put("pubcount", 0);\r
 358                                                 datemap.put(""+feed, feedmap);\r
 359                                         }\r
 360                                         String type = tt[1];\r
 361                                         int count   = Integer.parseInt(tt[4]);\r
 362                                         if (type.equals("pub")) {\r
 363                                                 try {\r
 364                                                         int n = feedmap.getInt("pubcount");\r
 365                                                         feedmap.put("pubcount", n+count);\r
 366                                                 } catch (JSONException e) {\r
 367                                                         feedmap.put("pubcount", count);\r
 368                                                 }\r
 369                                         } else if (type.equals("del")) {\r
 370                                                 String subid = tt[3];\r
 371                                                 try {\r
 372                                                         int n = feedmap.getInt(subid);\r
 373                                                         feedmap.put(subid, n+count);\r
 374                                                 } catch (JSONException e) {\r
 375                                                         feedmap.put(subid, count);\r
 376                                                 }\r
 377                                         }\r
 378                                 }\r
 379                                 line = lr.readLine();\r
 380                         }\r
 381                         lr.close();\r
 382                         String t = toHTML(jo);\r
 383                         switch (rtype) {\r
 384                         case 1: t = t.replaceAll("<th>Date</th>", "<th>Week</th>"); break;\r
 385                         case 2: t = t.replaceAll("<th>Date</th>", "<th>Month</th>"); break;\r
 386                         case 3: t = t.replaceAll("<th>Date</th>", "<th>Year</th>"); break;\r
 387                         }\r
 388                         System.out.println(t);\r
 389                 } catch (Exception e) {\r
 390                         System.err.println(e);\r
 391                         e.printStackTrace();\r
 392                 }\r
 393         }\r
 394 }\r