* * Licensed under the Apache License, Version 2.0 (the "License");\r
* * you may not use this file except in compliance with the License.\r
* * You may obtain a copy of the License at\r
- * * \r
+ * *\r
* * http://www.apache.org/licenses/LICENSE-2.0\r
- * * \r
+ * *\r
* * Unless required by applicable law or agreed to in writing, software\r
* * distributed under the License is distributed on an "AS IS" BASIS,\r
* * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.\r
* @version $Id: SubscriberReport.java,v 1.2 2013/11/06 16:23:55 eby Exp $\r
*/\r
public class SubscriberReport extends ReportBase {\r
- private static final String SELECT_SQL =\r
- "select date(from_unixtime(EVENT_TIME div 1000)) as DATE, DELIVERY_SUBID, RESULT, COUNT(RESULT) as COUNT" +\r
- " from LOG_RECORDS" +\r
- " where TYPE = 'del' and EVENT_TIME >= ? and EVENT_TIME <= ?" +\r
- " group by DATE, DELIVERY_SUBID, RESULT";\r
- private static final String SELECT_SQL2 =\r
- "select date(from_unixtime(EVENT_TIME div 1000)) as DATE, DELIVERY_SUBID, COUNT(CONTENT_LENGTH_2) as COUNT" +\r
- " from LOG_RECORDS" +\r
- " where TYPE = 'dlx' and CONTENT_LENGTH_2 = -1 and EVENT_TIME >= ? and EVENT_TIME <= ?" +\r
- " group by DATE, DELIVERY_SUBID";\r
+ private static final String SELECT_SQL =\r
+ "select date(from_unixtime(EVENT_TIME div 1000)) as DATE, DELIVERY_SUBID, RESULT, COUNT(RESULT) as COUNT" +\r
+ " from LOG_RECORDS" +\r
+ " where TYPE = 'del' and EVENT_TIME >= ? and EVENT_TIME <= ?" +\r
+ " group by DATE, DELIVERY_SUBID, RESULT";\r
+ private static final String SELECT_SQL2 =\r
+ "select date(from_unixtime(EVENT_TIME div 1000)) as DATE, DELIVERY_SUBID, COUNT(CONTENT_LENGTH_2) as COUNT" +\r
+ " from LOG_RECORDS" +\r
+ " where TYPE = 'dlx' and CONTENT_LENGTH_2 = -1 and EVENT_TIME >= ? and EVENT_TIME <= ?" +\r
+ " group by DATE, DELIVERY_SUBID";\r
+\r
+ private class Counters {\r
+ private String date;\r
+ private int sub;\r
+ private int c100, c200, c300, c400, c500, cm1, cdlx;\r
+\r
+ public Counters(String date, int sub) {\r
+ this.date = date;\r
+ this.sub = sub;\r
+ c100 = c200 = c300 = c400 = c500 = cm1 = cdlx = 0;\r
+ }\r
+\r
+ public void addCounts(int status, int n) {\r
+ if (status < 0) {\r
+ cm1 += n;\r
+ } else if (status >= 100 && status <= 199) {\r
+ c100 += n;\r
+ } else if (status >= 200 && status <= 299) {\r
+ c200 += n;\r
+ } else if (status >= 300 && status <= 399) {\r
+ c300 += n;\r
+ } else if (status >= 400 && status <= 499) {\r
+ c400 += n;\r
+ } else if (status >= 500 && status <= 599) {\r
+ c500 += n;\r
+ }\r
+ }\r
+\r
+ public void addDlxCount(int n) {\r
+ cdlx += n;\r
+ }\r
+\r
+ @Override\r
+ public String toString() {\r
+ return date + "," + sub + "," +\r
+ c100 + "," + c200 + "," + c300 + "," + c400 + "," + c500 + "," +\r
+ cm1 + "," + cdlx;\r
+ }\r
+ }\r
\r
- private class Counters {\r
- private String date;\r
- private int sub;\r
- private int c100, c200, c300, c400, c500, cm1, cdlx;\r
- public Counters(String date, int sub) {\r
- this.date = date;\r
- this.sub = sub;\r
- c100 = c200 = c300 = c400 = c500 = cm1 = cdlx = 0;\r
- }\r
- public void addCounts(int status, int n) {\r
- if (status < 0) {\r
- cm1 += n;\r
- } else if (status >= 100 && status <= 199) {\r
- c100 += n;\r
- } else if (status >= 200 && status <= 299) {\r
- c200 += n;\r
- } else if (status >= 300 && status <= 399) {\r
- c300 += n;\r
- } else if (status >= 400 && status <= 499) {\r
- c400 += n;\r
- } else if (status >= 500 && status <= 599) {\r
- c500 += n;\r
- }\r
- }\r
- public void addDlxCount(int n) {\r
- cdlx += n;\r
- }\r
- @Override\r
- public String toString() {\r
- return date + "," + sub + "," +\r
- c100 + "," + c200 + "," + c300 + "," + c400 + "," + c500 + "," +\r
- cm1 + "," + cdlx;\r
- }\r
- }\r
+ @Override\r
+ public void run() {\r
+ Map<String, Counters> map = new HashMap<String, Counters>();\r
+ long start = System.currentTimeMillis();\r
\r
- @Override\r
- public void run() {\r
- Map<String, Counters> map = new HashMap<String, Counters>();\r
- long start = System.currentTimeMillis();\r
- try {\r
- DB db = new DB();\r
- @SuppressWarnings("resource")\r
- Connection conn = db.getConnection();\r
- PreparedStatement ps = conn.prepareStatement(SELECT_SQL);\r
- ps.setLong(1, from);\r
- ps.setLong(2, to);\r
- ResultSet rs = ps.executeQuery();\r
- while (rs.next()) {\r
- String date = rs.getString("DATE");\r
- int sub = rs.getInt("DELIVERY_SUBID");\r
- int res = rs.getInt("RESULT");\r
- int count = rs.getInt("COUNT");\r
- String key = date + "," + sub;\r
- Counters c = map.get(key);\r
- if (c == null) {\r
- c = new Counters(date, sub);\r
- map.put(key, c);\r
- }\r
- c.addCounts(res, count);\r
- }\r
- rs.close();\r
- ps.close();\r
+ try {\r
+ DB db = new DB();\r
+ @SuppressWarnings("resource")\r
+ Connection conn = db.getConnection();\r
+ try(PreparedStatement ps = conn.prepareStatement(SELECT_SQL)) {\r
+ ps.setLong(1, from);\r
+ ps.setLong(2, to);\r
+ try(ResultSet rs = ps.executeQuery()) {\r
+ while (rs.next()) {\r
+ String date = rs.getString("DATE");\r
+ int sub = rs.getInt("DELIVERY_SUBID");\r
+ int res = rs.getInt("RESULT");\r
+ int count = rs.getInt("COUNT");\r
+ String key = date + "," + sub;\r
+ Counters c = map.get(key);\r
+ if (c == null) {\r
+ c = new Counters(date, sub);\r
+ map.put(key, c);\r
+ }\r
+ c.addCounts(res, count);\r
+ }\r
+ }\r
+ }\r
\r
- ps = conn.prepareStatement(SELECT_SQL2);\r
- ps.setLong(1, from);\r
- ps.setLong(2, to);\r
- rs = ps.executeQuery();\r
- while (rs.next()) {\r
- String date = rs.getString("DATE");\r
- int sub = rs.getInt("DELIVERY_SUBID");\r
- int count = rs.getInt("COUNT");\r
- String key = date + "," + sub;\r
- Counters c = map.get(key);\r
- if (c == null) {\r
- c = new Counters(date, sub);\r
- map.put(key, c);\r
- }\r
- c.addDlxCount(count);\r
- }\r
- rs.close();\r
- ps.close();\r
+ try( PreparedStatement ps2 = conn.prepareStatement(SELECT_SQL2)) {\r
+ ps2.setLong(1, from);\r
+ ps2.setLong(2, to);\r
+ try(ResultSet rs2 = ps2.executeQuery()) {\r
+ while (rs2.next()) {\r
+ String date = rs2.getString("DATE");\r
+ int sub = rs2.getInt("DELIVERY_SUBID");\r
+ int count = rs2.getInt("COUNT");\r
+ String key = date + "," + sub;\r
+ Counters c = map.get(key);\r
+ if (c == null) {\r
+ c = new Counters(date, sub);\r
+ map.put(key, c);\r
+ }\r
+ c.addDlxCount(count);\r
+ }\r
+ }\r
+ }\r
\r
- db.release(conn);\r
- } catch (SQLException e) {\r
- e.printStackTrace();\r
- }\r
- logger.debug("Query time: " + (System.currentTimeMillis()-start) + " ms");\r
- try {\r
- PrintWriter os = new PrintWriter(outfile);\r
- os.println("date,subid,count100,count200,count300,count400,count500,countminus1,countdlx");\r
- for (String key : new TreeSet<String>(map.keySet())) {\r
- Counters c = map.get(key);\r
- os.println(c.toString());\r
- }\r
- os.close();\r
- } catch (FileNotFoundException e) {\r
- System.err.println("File cannot be written: "+outfile);\r
- }\r
- }\r
+ db.release(conn);\r
+ } catch (SQLException e) {\r
+ e.printStackTrace();\r
+ }\r
+ logger.debug("Query time: " + (System.currentTimeMillis() - start) + " ms");\r
+ try (PrintWriter os = new PrintWriter(outfile)){\r
+ os.println("date,subid,count100,count200,count300,count400,count500,countminus1,countdlx");\r
+ for (String key : new TreeSet<String>(map.keySet())) {\r
+ Counters c = map.get(key);\r
+ os.println(c.toString());\r
+ }\r
+ } catch (FileNotFoundException e) {\r
+ System.err.println("File cannot be written: " + outfile);\r
+ }\r
+ }\r
}\r