1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
package common.task;
import common.model.AppInfo;
import org.apache.commons.collections.map.HashedMap;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import util.DBUtil;
import util.StringUtil;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.Callable;
/**
* 漏斗和留存报表数据获取线程
*/
public class ReportCallable
implements Callable<Map<String, Object>> {
protected static Logger logger = LoggerFactory.getLogger(ReportCallable.class);
private AppInfo app;
private int pageNum;
private int pageSize;
private String sortString;
private Integer isASC;
private String searchString;
private String startDate;
private String endDate;
private Map<String, Object> result = new HashMap();
private int totalNum;
private boolean isTotal;
public ReportCallable(AppInfo app, int pageNum, int pageSize, String sortString, Integer isASC, String searchString, String startDate, String endDate, boolean isTotal) {
super();
this.app = app;
this.pageNum = pageNum;
this.pageSize = pageSize;
this.sortString = sortString;
this.searchString = searchString;
this.isASC = isASC;
this.startDate = startDate;
this.endDate = endDate;
this.isTotal = isTotal;
}
public Map<String, Object> getResult() {
return result;
}
@Override
public Map<String, Object> call() throws Exception {
logger.debug("thread start....................");
String sql = generateSql(app, pageNum, pageSize, sortString, isASC, searchString, startDate, endDate, isTotal);
System.out.println(sql);
if (isTotal) {
Integer total = 0;
try {
total = queryTotalDB(sql);
}
catch (SQLException e) {
e.printStackTrace();
}
System.out.println(total);
result.put("totalElements", total);
} else {
List<AppInfo> list = new ArrayList<>();
try {
list = queryDB(sql);
}
catch (SQLException e) {
e.printStackTrace();
}
System.out.println(list);
result.put("content", list);
}
logger.debug("thread end....................");
return result;
}
public Map<String, String> getSortMap() {
Map<String, String> map = new HashMap<>();
map.put("name", "name");
map.put("reyun", "reyun");
map.put("pkgName", "pkg_name");
map.put("company", "company");
map.put("firstCate", "first_cate");
map.put("secondCate","second_cate");
map.put("os", "os");
map.put("logoUrl", "logo_url");
map.put("newDevice", "num");
return map;
}
public String generateSql(AppInfo appInfo, int pageNum, int pageSize, String sortString, Integer isASC, String searchString, String startDate, String endDate, boolean isTotal) {
String sql = "select t1.location, t1.`name`,t1.reyun,t1.pkg_name,t1.company,t1.first_cate,t1.logo_url,t1.os,t1.second_cate, sum(t2.dev_num) as num \n" +
"from (\n" +
"select * from app_info \n" +
"%s %s\n" +
") t1 \n" +
"JOIN (\n" +
"SELECT * from ot_pkg_dev_stats \n" +
"%s %s\n" +
") t2 \n" +
"on t1.pkg_name=t2.pkgname \n" +
"group by t1.`name`,t1.reyun,t1.pkg_name,t1.company,t1.first_cate,t1.logo_url,t1.os,t1.second_cate";
String orderSql = " order by %s %s";
String limitSql = " limit %s,%s";
StringBuffer app_sb = new StringBuffer();
if (appInfo.getReyun() != null) {
app_sb.append(" and ").append("reyun=").append(appInfo.getReyun());
}
if (!StringUtil.isEmpty(appInfo.getLocation())) {
app_sb.append(" and ").append("location='").append(appInfo.getLocation()).append("'");
}
if (!StringUtil.isEmpty(appInfo.getFirstCate())) {
app_sb.append(" and ").append("first_cate='").append(appInfo.getFirstCate()).append("'");
}
if (!StringUtil.isEmpty(appInfo.getSecondCate())) {
app_sb.append(" and ").append("second_cate='").append(appInfo.getSecondCate()).append("'");
}
if (!StringUtil.isEmpty(searchString)) {
StringBuffer searchsb = new StringBuffer();
searchsb.append(" name like ").append("'").append("%"+searchString+"%").append("'");
searchsb.append(" or company like ").append("'").append("%"+searchString+"%").append("'");
searchsb.append(" or pkg_name like ").append("'").append("%"+searchString+"%").append("'");
app_sb.append(" and (").append(searchsb).append(")");
}
StringBuffer data_sb = new StringBuffer();
if (!StringUtil.isEmpty(startDate)) {
data_sb.append(" and ").append("ds>='").append(startDate).append("'");
}
if (!StringUtil.isEmpty(endDate)) {
data_sb.append(" and ").append("ds<='").append(startDate).append("'");
}
String result = "";
String newResult = "";
if (isTotal) {
result = String.format("select count(1) as totalcount from ("+sql+") t", app_sb.length()>0?" where ":"",
app_sb.length()>0?app_sb.substring(5):"",
data_sb.length()>0?" where ":"",
data_sb.length()>0?data_sb.substring(5):"");
} else {
String resultOrderSql = "";
if (!StringUtil.isEmpty(sortString)) {
sortString = getSortMap().get(sortString);
resultOrderSql = String.format(orderSql, sortString, isASC!=null && isASC==0?"DESC":"ASC");
} else{//
resultOrderSql = String.format(orderSql, "num", "DESC");
}
String resultLimitSql = String.format(limitSql, pageNum*pageSize, pageSize);
result = String.format(sql + " %s %s", app_sb.length()>0?" where ":"",
app_sb.length()>0?app_sb.substring(5):"",
data_sb.length()>0?" where ":"",
data_sb.length()>0?data_sb.substring(5):"",
resultOrderSql,
resultLimitSql);
}
return result;
}
public List<AppInfo> queryDB(String sql)
throws SQLException
{
List<AppInfo> list = new ArrayList<>();
Connection connection = DBUtil.newInstance().getConn();
PreparedStatement ps = connection.prepareStatement(sql);
ResultSet rs = ps.executeQuery();
while (rs.next()) {
AppInfo app = new AppInfo();
app.setName(rs.getString("name"));
app.setNewDevice(rs.getLong("num"));
app.setReyun(rs.getInt("reyun"));
app.setPkgName(rs.getString("pkg_name"));
app.setFirstCate(rs.getString("first_cate"));
app.setCompany(rs.getString("company"));
app.setLogoUrl(rs.getString("logo_url"));
app.setOs(rs.getString("os"));
app.setSecondCate(rs.getString("second_cate"));
app.setLocation(rs.getString("location"));
list.add(app);
}
return list;
}
public int queryTotalDB(String sql)
throws SQLException
{
int result = 0;
Connection connection = DBUtil.newInstance().getConn();
PreparedStatement ps = connection.prepareStatement(sql);
ResultSet rs = ps.executeQuery();
while (rs.next()) {
result = rs.getInt("totalcount");
}
return result;
}
}