1 /*******************************************************************************
\r
2 * ============LICENSE_START==================================================
\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
11 * * http://www.apache.org/licenses/LICENSE-2.0
\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
20 * * ECOMP is a trademark and service mark of AT&T Intellectual Property.
\r
22 ******************************************************************************/
\r
25 package org.onap.dmaap.datarouter.reports;
\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 import org.onap.dmaap.datarouter.provisioning.utils.ProvDbUtils;
\r
39 * Generate a subscribers report. The report is a .CSV file. It contains information per-day and per-subscriber,
\r
40 * 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 * @author Robert P. Eby
\r
43 * @version $Id: SubscriberReport.java,v 1.2 2013/11/06 16:23:55 eby Exp $
\r
45 public class SubscriberReport extends ReportBase {
\r
46 private static final String SELECT_SQL =
\r
47 "select date(from_unixtime(EVENT_TIME div 1000)) as DATE, DELIVERY_SUBID, RESULT, COUNT(RESULT) as COUNT" +
\r
48 " from LOG_RECORDS" +
\r
49 " where TYPE = 'del' and EVENT_TIME >= ? and EVENT_TIME <= ?" +
\r
50 " group by DATE, DELIVERY_SUBID, RESULT";
\r
51 private static final String SELECT_SQL2 =
\r
52 "select date(from_unixtime(EVENT_TIME div 1000)) as DATE, DELIVERY_SUBID, COUNT(CONTENT_LENGTH_2) as COUNT" +
\r
53 " from LOG_RECORDS" +
\r
54 " where TYPE = 'dlx' and CONTENT_LENGTH_2 = -1 and EVENT_TIME >= ? and EVENT_TIME <= ?" +
\r
55 " group by DATE, DELIVERY_SUBID";
\r
57 private class Counters {
\r
58 private String date;
\r
60 private int c100, c200, c300, c400, c500, cm1, cdlx;
\r
62 public Counters(String date, int sub) {
\r
65 c100 = c200 = c300 = c400 = c500 = cm1 = cdlx = 0;
\r
68 public void addCounts(int status, int n) {
\r
71 } else if (status >= 100 && status <= 199) {
\r
73 } else if (status >= 200 && status <= 299) {
\r
75 } else if (status >= 300 && status <= 399) {
\r
77 } else if (status >= 400 && status <= 499) {
\r
79 } else if (status >= 500 && status <= 599) {
\r
84 public void addDlxCount(int n) {
\r
89 public String toString() {
\r
90 return date + "," + sub + "," +
\r
91 c100 + "," + c200 + "," + c300 + "," + c400 + "," + c500 + "," +
\r
98 Map<String, Counters> map = new HashMap<>();
\r
99 long start = System.currentTimeMillis();
\r
101 try (Connection conn = ProvDbUtils.getInstance().getConnection()) {
\r
102 try(PreparedStatement ps = conn.prepareStatement(SELECT_SQL)) {
\r
103 ps.setLong(1, from);
\r
105 try(ResultSet rs = ps.executeQuery()) {
\r
106 while (rs.next()) {
\r
107 String date = rs.getString("DATE");
\r
108 int sub = rs.getInt("DELIVERY_SUBID");
\r
109 int res = rs.getInt("RESULT");
\r
110 int count = rs.getInt("COUNT");
\r
111 String key = date + "," + sub;
\r
112 Counters c = map.get(key);
\r
114 c = new Counters(date, sub);
\r
117 c.addCounts(res, count);
\r
122 try( PreparedStatement ps2 = conn.prepareStatement(SELECT_SQL2)) {
\r
123 ps2.setLong(1, from);
\r
124 ps2.setLong(2, to);
\r
125 try (ResultSet rs2 = ps2.executeQuery()) {
\r
126 while (rs2.next()) {
\r
127 String date = rs2.getString("DATE");
\r
128 int sub = rs2.getInt("DELIVERY_SUBID");
\r
129 int count = rs2.getInt("COUNT");
\r
130 String key = date + "," + sub;
\r
131 Counters c = map.get(key);
\r
133 c = new Counters(date, sub);
\r
136 c.addDlxCount(count);
\r
140 } catch (SQLException e) {
\r
141 logger.error("SQLException: " + e.getMessage());
\r
143 logger.debug("Query time: " + (System.currentTimeMillis() - start) + " ms");
\r
144 try (PrintWriter os = new PrintWriter(outfile)) {
\r
145 os.println("date,subid,count100,count200,count300,count400,count500,countminus1,countdlx");
\r
146 for (String key : new TreeSet<>(map.keySet())) {
\r
147 Counters c = map.get(key);
\r
148 os.println(c.toString());
\r
150 } catch (FileNotFoundException e) {
\r
151 System.err.println("File cannot be written: " + outfile);
\r
152 logger.error("FileNotFoundException: " + e.getMessage());
\r