ReportCallable.java 6.65 KB
Newer Older
zhangxiaoyan committed
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
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) {
manxiaoqiang committed
107
		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" +
zhangxiaoyan committed
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
				"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("'");
manxiaoqiang committed
138
			searchsb.append(" or pkg_name like ").append("'").append("%"+searchString+"%").append("'");
zhangxiaoyan committed
139 140 141 142 143 144 145 146 147 148 149
			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 = "";
zhangxiaoyan committed
150
		String newResult = "";
zhangxiaoyan committed
151 152 153 154 155 156 157 158 159 160
		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");
manxiaoqiang committed
161 162
			} else{//
				resultOrderSql = String.format(orderSql, "num", "DESC");
zhangxiaoyan committed
163 164 165 166 167 168
			}
			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):"",
manxiaoqiang committed
169
					resultOrderSql,
zhangxiaoyan committed
170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193
					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"));
manxiaoqiang committed
194
			app.setLocation(rs.getString("location"));
zhangxiaoyan committed
195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214
			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;
	}
	
}