[DR] Remove DR code smells - additional

Change-Id: Ibd27fc27530f7762809d5a6bd73a82c7973c74f4
Issue-ID: DMAAP-1195
Signed-off-by: HelenaLydon <helena.lydon@est.tech>

diff --git a/datarouter-prov/src/main/java/org/onap/dmaap/datarouter/provisioning/StatisticsServlet.java b/datarouter-prov/src/main/java/org/onap/dmaap/datarouter/provisioning/StatisticsServlet.java
index fcdca0a..aba230b 100755 (executable)
--- a/datarouter-prov/src/main/java/org/onap/dmaap/datarouter/provisioning/StatisticsServlet.java
+++ b/datarouter-prov/src/main/java/org/onap/dmaap/datarouter/provisioning/StatisticsServlet.java
@@ -63,6 +63,21 @@ public class StatisticsServlet extends BaseServlet {
     private static final String FMT2 = "yyyy-MM-dd'T'HH:mm:ss.SSS'Z'";\r
     public static final String FEEDID = "FEEDID";\r
 \r
     private static final String FMT2 = "yyyy-MM-dd'T'HH:mm:ss.SSS'Z'";\r
     public static final String FEEDID = "FEEDID";\r
 \r

+    //sql Strings\r
+    private static final String SQL_SELECT_NAME = "SELECT (SELECT NAME FROM FEEDS AS f WHERE f.FEEDID in(";\r
+    private static final String SQL_FEED_ID = ") and f.FEEDID=e.FEEDID) AS FEEDNAME, e.FEEDID as FEEDID, ";\r
+    private static final String SQL_SELECT_COUNT = "(SELECT COUNT(*) FROM LOG_RECORDS AS c WHERE c.FEEDID in(";\r
+    private static final String SQL_TYPE_PUB = ") and c.FEEDID=e.FEEDID AND c.TYPE='PUB') AS FILES_PUBLISHED,";\r
+    private static final String SQL_SELECT_SUM = "(SELECT SUM(content_length) FROM LOG_RECORDS AS c WHERE c.FEEDID in(";\r
+    private static final String SQL_PUBLISH_LENGTH = ") and c.FEEDID=e.FEEDID AND c.TYPE='PUB') AS PUBLISH_LENGTH, COUNT(e.EVENT_TIME) as FILES_DELIVERED,";\r
+    private static final String SQL_SUBSCRIBER_URL = " sum(m.content_length) as DELIVERED_LENGTH,SUBSTRING_INDEX(e.REQURI,'/',+3) as SUBSCRIBER_URL,";\r
+    private static final String SQL_SUB_ID = " e.DELIVERY_SUBID as SUBID, ";\r
+    private static final String SQL_DELIVERY_TIME = " e.EVENT_TIME AS PUBLISH_TIME, m.EVENT_TIME AS DELIVERY_TIME, ";\r
+    private static final String SQL_AVERAGE_DELAY = " AVG(e.EVENT_TIME - m.EVENT_TIME)/1000 as AverageDelay FROM LOG_RECORDS";\r
+    private static final String SQL_JOIN_RECORDS = " e JOIN LOG_RECORDS m ON m.PUBLISH_ID = e.PUBLISH_ID AND e.FEEDID IN (";\r
+    private static final String SQL_STATUS_204 = " AND m.STATUS=204 AND e.RESULT=204 ";\r
+    private static final String SQL_GROUP_SUB_ID = " group by SUBID";\r
+\r

 \r
     /**\r
      * DELETE a logging URL -- not supported.\r
 \r
     /**\r
      * DELETE a logging URL -- not supported.\r


@@ -313,20 +328,12 @@ public class StatisticsServlet extends BaseServlet {
 \r
         if (endTime == null && startTime == null) {\r
 \r
 \r
         if (endTime == null && startTime == null) {\r
 \r

-            sql = "SELECT (SELECT NAME FROM FEEDS AS f WHERE f.FEEDID in(" + feedids\r
-                + ") and f.FEEDID=e.FEEDID) AS FEEDNAME, e.FEEDID as FEEDID, "\r
-                + "(SELECT COUNT(*) FROM LOG_RECORDS AS c WHERE c.FEEDID in("\r
-                + feedids\r
-                + ") and c.FEEDID=e.FEEDID AND c.TYPE='PUB') AS FILES_PUBLISHED,(SELECT SUM(content_length)"\r
-                + " FROM LOG_RECORDS AS c WHERE c.FEEDID in("\r
-                + feedids\r
-                + ") and c.FEEDID=e.FEEDID AND c.TYPE='PUB') AS PUBLISH_LENGTH, COUNT(e.EVENT_TIME) as FILES_DELIVERED,"\r
-                + "sum(m.content_length) as DELIVERED_LENGTH,SUBSTRING_INDEX(e.REQURI,'/',+3) as SUBSCRIBER_URL,"\r
-                + " e.DELIVERY_SUBID as SUBID, "\r
-                + "e.EVENT_TIME AS PUBLISH_TIME, m.EVENT_TIME AS DELIVERY_TIME, "\r
-                + " AVG(e.EVENT_TIME - m.EVENT_TIME)/1000 as AverageDelay FROM LOG_RECORDS"\r
-                + " e JOIN LOG_RECORDS m ON m.PUBLISH_ID = e.PUBLISH_ID AND e.FEEDID IN ("\r
-                + feedids + ") " + subid + " AND m.STATUS=204 AND e.RESULT=204  group by SUBID";\r
+            sql =  SQL_SELECT_NAME + feedids + SQL_FEED_ID + SQL_SELECT_COUNT + feedids + SQL_TYPE_PUB\r
+                    + SQL_SELECT_SUM\r
+                    + feedids + SQL_PUBLISH_LENGTH\r
+                + SQL_SUBSCRIBER_URL + SQL_SUB_ID + SQL_DELIVERY_TIME + SQL_AVERAGE_DELAY + SQL_JOIN_RECORDS\r
+                    + feedids + ") " + subid\r
+                    + SQL_STATUS_204 + SQL_GROUP_SUB_ID;\r

 \r
             return sql;\r
         } else if (startTime != null && endTime == null) {\r
 \r
             return sql;\r
         } else if (startTime != null && endTime == null) {\r


@@ -336,22 +343,12 @@ public class StatisticsServlet extends BaseServlet {
             long currentTimeInMilli = cal.getTimeInMillis();\r
             long compareTime = currentTimeInMilli - inputTimeInMilli;\r
 \r
             long currentTimeInMilli = cal.getTimeInMillis();\r
             long compareTime = currentTimeInMilli - inputTimeInMilli;\r
 \r

-            sql = "SELECT (SELECT NAME FROM FEEDS AS f WHERE f.FEEDID in(" + feedids\r
-                + ") and f.FEEDID=e.FEEDID) AS FEEDNAME, e.FEEDID as FEEDID, "\r
-                + "(SELECT COUNT(*) FROM LOG_RECORDS AS c WHERE c.FEEDID in("\r
-                + feedids\r
-                + ") and c.FEEDID=e.FEEDID AND c.TYPE='PUB') AS FILES_PUBLISHED,"\r
-                + "(SELECT SUM(content_length) FROM LOG_RECORDS AS c WHERE c.FEEDID in("\r
-                + feedids\r
-                + ") and c.FEEDID=e.FEEDID AND c.TYPE='PUB') AS PUBLISH_LENGTH, COUNT(e.EVENT_TIME) as FILES_DELIVERED,"\r
-                + "sum(m.content_length) as DELIVERED_LENGTH,SUBSTRING_INDEX(e.REQURI,'/',+3) as SUBSCRIBER_URL,"\r
-                + " e.DELIVERY_SUBID as SUBID, "\r
-                + "e.EVENT_TIME AS PUBLISH_TIME, m.EVENT_TIME AS DELIVERY_TIME,  "\r
-                + "AVG(e.EVENT_TIME - m.EVENT_TIME)/1000 as AverageDelay "\r
-                + "FROM LOG_RECORDS e JOIN LOG_RECORDS m ON m.PUBLISH_ID = e.PUBLISH_ID AND e.FEEDID IN ("\r
-                + feedids + ") " + subid + " AND m.STATUS=204 AND e.RESULT=204 and e.event_time>="\r
-                + compareTime + " group by SUBID";\r
-\r
+            sql = SQL_SELECT_NAME + feedids + SQL_FEED_ID + SQL_SELECT_COUNT + feedids + SQL_TYPE_PUB\r
+                    + SQL_SELECT_SUM\r
+                    + feedids + SQL_PUBLISH_LENGTH\r
+                + SQL_SUBSCRIBER_URL + SQL_SUB_ID + SQL_DELIVERY_TIME + SQL_AVERAGE_DELAY + SQL_JOIN_RECORDS\r
+                    + feedids + ") " + subid\r
+                + SQL_STATUS_204 + " and e.event_time>=" + compareTime + SQL_GROUP_SUB_ID;\r

             return sql;\r
 \r
         } else {\r
             return sql;\r
 \r
         } else {\r


@@ -362,23 +359,11 @@ public class StatisticsServlet extends BaseServlet {
             long startInMillis = startDate.getTime();\r
             long endInMillis = endDate.getTime();\r
 \r
             long startInMillis = startDate.getTime();\r
             long endInMillis = endDate.getTime();\r
 \r

-            sql = "SELECT (SELECT NAME FROM FEEDS AS f WHERE f.FEEDID in(" + feedids\r
-                + ") and f.FEEDID=e.FEEDID) AS FEEDNAME, e.FEEDID as FEEDID, "\r
-                          + "(SELECT COUNT(*) FROM LOG_RECORDS AS c WHERE c.FEEDID in("\r
-                + feedids\r
-                + ") and c.FEEDID=e.FEEDID AND c.TYPE='PUB') AS FILES_PUBLISHED,"\r
-                          + "(SELECT SUM(content_length) FROM LOG_RECORDS AS c WHERE c.FEEDID in("\r
-                + feedids\r
-                + ") and c.FEEDID=e.FEEDID AND c.TYPE='PUB') AS PUBLISH_LENGTH, COUNT(e.EVENT_TIME) as FILES_DELIVERED,"\r
-                          + "sum(m.content_length) as DELIVERED_LENGTH,"\r
-                          + "SUBSTRING_INDEX(e.REQURI,'/',+3) as SUBSCRIBER_URL,"\r
-                          + "e.DELIVERY_SUBID as SUBID, "\r
-                          + "e.EVENT_TIME AS PUBLISH_TIME, m.EVENT_TIME AS DELIVERY_TIME,  "\r
-                          + "AVG(e.EVENT_TIME - m.EVENT_TIME)/1000 as AverageDelay FROM LOG_RECORDS"\r
-                          + " e JOIN LOG_RECORDS m ON m.PUBLISH_ID = e.PUBLISH_ID AND e.FEEDID IN ("\r
-                + feedids + ")" + subid + " AND m.STATUS=204 AND e.RESULT=204 and e.event_time between " + startInMillis\r
-                + " and " + endInMillis + " group by SUBID";\r
-\r
+            sql = SQL_SELECT_NAME + feedids + SQL_FEED_ID + SQL_SELECT_COUNT + feedids + SQL_TYPE_PUB\r
+                    + SQL_SELECT_SUM\r
+                    + feedids + SQL_PUBLISH_LENGTH + SQL_SUBSCRIBER_URL\r
+                    + SQL_SUB_ID + SQL_DELIVERY_TIME + SQL_AVERAGE_DELAY + SQL_JOIN_RECORDS + feedids + ")" + subid + SQL_STATUS_204\r
+                    +" and e.event_time between " + startInMillis + " and " + endInMillis + SQL_GROUP_SUB_ID;\r

 \r
             return sql;\r
         }\r
 \r
             return sql;\r
         }\r