ReportCallable.java

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;
	}
	
}