datarouter-prov/src/main/java/com/att/research/datarouter/reports/SubscriberReport.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 com.att.research.datarouter.reports;\r
  26 \r
  27 import java.io.FileNotFoundException;\r
  28 import java.io.PrintWriter;\r
  29 import java.sql.Connection;\r
  30 import java.sql.PreparedStatement;\r
  31 import java.sql.ResultSet;\r
  32 import java.sql.SQLException;\r
  33 import java.util.HashMap;\r
  34 import java.util.Map;\r
  35 import java.util.TreeSet;\r
  36 \r
  37 import com.att.research.datarouter.provisioning.utils.DB;\r
  38 \r
  39 /**\r
  40  * Generate a subscribers report.  The report is a .CSV file.  It contains information per-day and per-subscriber,\r
  41  * on the status codes returned from each delivery attempt (1XX, 2XX, etc.) as well as a count of 4XX instead of a 100.\r
  42  *\r
  43  * @author Robert P. Eby\r
  44  * @version $Id: SubscriberReport.java,v 1.2 2013/11/06 16:23:55 eby Exp $\r
  45  */\r
  46 public class SubscriberReport extends ReportBase {\r
  47         private static final String SELECT_SQL =\r
  48                 "select date(from_unixtime(EVENT_TIME div 1000)) as DATE, DELIVERY_SUBID, RESULT, COUNT(RESULT) as COUNT" +\r
  49                 " from LOG_RECORDS" +\r
  50                 " where TYPE = 'del' and EVENT_TIME >= ? and EVENT_TIME <= ?" +\r
  51                 " group by DATE, DELIVERY_SUBID, RESULT";\r
  52         private static final String SELECT_SQL2 =\r
  53                 "select date(from_unixtime(EVENT_TIME div 1000)) as DATE, DELIVERY_SUBID, COUNT(CONTENT_LENGTH_2) as COUNT" +\r
  54                 " from LOG_RECORDS" +\r
  55                 " where TYPE = 'dlx' and CONTENT_LENGTH_2 = -1 and EVENT_TIME >= ? and EVENT_TIME <= ?" +\r
  56                 " group by DATE, DELIVERY_SUBID";\r
  57 \r
  58         private class Counters {\r
  59                 private String date;\r
  60                 private int sub;\r
  61                 private int c100, c200, c300, c400, c500, cm1, cdlx;\r
  62                 public Counters(String date, int sub) {\r
  63                         this.date = date;\r
  64                         this.sub = sub;\r
  65                         c100 = c200 = c300 = c400 = c500 = cm1 = cdlx = 0;\r
  66                 }\r
  67                 public void addCounts(int status, int n) {\r
  68                         if (status < 0) {\r
  69                                 cm1 += n;\r
  70                         } else if (status >= 100 && status <= 199) {\r
  71                                 c100 += n;\r
  72                         } else if (status >= 200 && status <= 299) {\r
  73                                 c200 += n;\r
  74                         } else if (status >= 300 && status <= 399) {\r
  75                                 c300 += n;\r
  76                         } else if (status >= 400 && status <= 499) {\r
  77                                 c400 += n;\r
  78                         } else if (status >= 500 && status <= 599) {\r
  79                                 c500 += n;\r
  80                         }\r
  81                 }\r
  82                 public void addDlxCount(int n) {\r
  83                         cdlx += n;\r
  84                 }\r
  85                 @Override\r
  86                 public String toString() {\r
  87                         return date + "," + sub + "," +\r
  88                                 c100 + "," + c200 + "," + c300 + "," + c400 + "," + c500 + "," +\r
  89                                 cm1 + "," + cdlx;\r
  90                 }\r
  91         }\r
  92 \r
  93         @Override\r
  94         public void run() {\r
  95                 Map<String, Counters> map = new HashMap<String, Counters>();\r
  96                 long start = System.currentTimeMillis();\r
  97                 try {\r
  98                         DB db = new DB();\r
  99                         @SuppressWarnings("resource")\r
 100                         Connection conn = db.getConnection();\r
 101                         PreparedStatement ps = conn.prepareStatement(SELECT_SQL);\r
 102                         ps.setLong(1, from);\r
 103                         ps.setLong(2, to);\r
 104                         ResultSet rs = ps.executeQuery();\r
 105                         while (rs.next()) {\r
 106                                 String date = rs.getString("DATE");\r
 107                                 int sub     = rs.getInt("DELIVERY_SUBID");\r
 108                                 int res     = rs.getInt("RESULT");\r
 109                                 int count   = rs.getInt("COUNT");\r
 110                                 String key  = date + "," + sub;\r
 111                                 Counters c = map.get(key);\r
 112                                 if (c == null) {\r
 113                                         c = new Counters(date, sub);\r
 114                                         map.put(key, c);\r
 115                                 }\r
 116                                 c.addCounts(res, count);\r
 117                         }\r
 118                         rs.close();\r
 119                         ps.close();\r
 120 \r
 121                         ps = conn.prepareStatement(SELECT_SQL2);\r
 122                         ps.setLong(1, from);\r
 123                         ps.setLong(2, to);\r
 124                         rs = ps.executeQuery();\r
 125                         while (rs.next()) {\r
 126                                 String date = rs.getString("DATE");\r
 127                                 int sub     = rs.getInt("DELIVERY_SUBID");\r
 128                                 int count   = rs.getInt("COUNT");\r
 129                                 String key  = date + "," + sub;\r
 130                                 Counters c = map.get(key);\r
 131                                 if (c == null) {\r
 132                                         c = new Counters(date, sub);\r
 133                                         map.put(key, c);\r
 134                                 }\r
 135                                 c.addDlxCount(count);\r
 136                         }\r
 137                         rs.close();\r
 138                         ps.close();\r
 139 \r
 140                         db.release(conn);\r
 141                 } catch (SQLException e) {\r
 142                         e.printStackTrace();\r
 143                 }\r
 144                 logger.debug("Query time: " + (System.currentTimeMillis()-start) + " ms");\r
 145                 try {\r
 146                         PrintWriter os = new PrintWriter(outfile);\r
 147                         os.println("date,subid,count100,count200,count300,count400,count500,countminus1,countdlx");\r
 148                         for (String key : new TreeSet<String>(map.keySet())) {\r
 149                                 Counters c = map.get(key);\r
 150                                 os.println(c.toString());\r
 151                         }\r
 152                         os.close();\r
 153                 } catch (FileNotFoundException e) {\r
 154                         System.err.println("File cannot be written: "+outfile);\r
 155                 }\r
 156         }\r
 157 }\r