ExportExcelDAOImpl.java

package mobvista.prd.datasource.dao.impl;

import mobvista.prd.datasource.dao.DBCommonDAO;
import mobvista.prd.datasource.dao.ExportExcelDAO;

import java.sql.Connection;
import java.sql.DriverManager;
import java.util.List;
import java.util.Map;

/**
 * Created by fl on 2017/5/15.
 */
public class ExportExcelDAOImpl extends DBCommonDAOImpl implements ExportExcelDAO {

    public static final String DB_DRIVER = "com.mysql.jdbc.Driver";
    public static final String DB_URL = "jdbc:mysql://datameta.c5yzcdreb1xr.us-east-1.rds.amazonaws.com:3306/monitor_effect";
    public static final String DB_USER = "huifang";
    public static final String DB_PWD = "Mobvista_123";


    /**
     *
     * @param date
     * @param tagType
     * @param dataType  整体和分国家
     * @return
     * @throws Exception
     */
    @Override
    public List<Map<String, Object>> findDataByTag(String date, String tagType, String dataType) throws Exception {
        StringBuilder builder = new StringBuilder();
        builder.append("SELECT a.day_key, a.dimension_type2, a.dimension_desc, a.dimension_desc2,\n" )
                .append("  a.dimension_desc3, a.dimension_desc4, concat('', a.video_value) as sample, concat('', b.video_value) as recommend\n")
                .append("FROM dmp_report_reuslt a\n" )
                .append("JOIN dmp_report_reuslt b\n" )
                .append("on a.day_key=b.day_key and a.dimension_type1=b.dimension_type1\n" )
                .append("  and a.dimension_type2=b.dimension_type2 and a.dimension_desc=b.dimension_desc\n")
                .append("  and a.dimension_desc2=b.dimension_desc2 and a.dimension_desc3=b.dimension_desc3\n")
                .append("  and a.dimension_desc4=b.dimension_desc4 AND b.video_desc='算法推算量'\n")
                .append("where a.day_key=? AND a.dimension_type1='DMP标签数据'\n")
                .append("AND a.dimension_type2=? and a.dimension_desc=? and a.video_desc='样本量' ");
        if ("分国家".equals(dataType)) {
            builder.append(" and a.dimension_desc2 in ('DE','HK','TW','JO','BH','FR','SA','QA','SG','UK','ID','OM','US','EG','AE','IN','KR','CN','KW','MY','ES','TH','PH','VN','PK','CA','MX')");
        }
        return findMapList(builder.toString(), new Object[]{date, tagType, dataType});
    }


    /**
     *
     * @param date
     * @param dataType  整体和分国家
     * @return
     * @throws Exception
     */
    public List<Map<String, Object>> findInterestTagData (String date, String dataType) throws Exception {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT t.day_key, t.dimension_type2, t.dimension_desc,\n")
           .append("  t.dimension_desc2, t.dimension_desc3, t.dimension_desc4, concat('', t.video_value) as sample\n" )
           .append("FROM dmp_report_reuslt t\n" )
           .append("WHERE t.day_key=? and t.dimension_type1='DMP标签数据'\n" )
           .append("AND t.dimension_type2='兴趣' and t.dimension_desc=? and t.video_desc='样本量'");
        if ("分国家".equals(dataType)) {
            sql.append(" and t.dimension_desc2 in ('DE','HK','TW','JO','BH','FR','SA','QA','SG','UK','ID','OM','US','EG','AE','IN','KR','CN','KW','MY','ES','TH','PH','VN','PK','CA','MX')");
        }
        return findMapList(sql.toString(), new Object[]{date, dataType});
    }


    /**
     * 查询M系统分国家、app和平台DAU
     *
     * @param country
     * @param appId
     * @param date
     * @param platform
     * @return
     * @throws Exception
     */
    public String getAppDau (String country, String appId, String date, String platform) throws Exception {
        Connection conn = null;
        try {
            Class.forName(DB_DRIVER);
            conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PWD);
            DBCommonDAO dao = new DBCommonDAOImpl();

            StringBuilder sql = new StringBuilder();
            sql.append("select dev_num ")
                    .append("from country_platform_dau  ")
                    .append("where country_code=? and date=? and app_id=? and platform=? and unit_id='null'");

            List<Map<String, Object>> list = dao.findMapList(conn, sql.toString(), new Object[]{country, date, appId, platform}, false);
            return list.size() > 0 ? String.valueOf(list.get(0).get("dev_num")) : "0";

        } finally {
            if (conn != null) {
                conn.close();
            }
        }
    }

    /**
     * 查询各个数据源的相关数据
     * @param date
     * @param type
     * @return
     * @throws Exception
     */
    public List<Map<String, Object>> findSourcesData (String date, String type) throws Exception {
        StringBuilder sql = new StringBuilder();
        sql.append("select t.day_key, t.dimension_type1, t.dimension_type2, t.video_value\n")
                .append("from dmp_report_reuslt t\n")
                .append("where t.day_key=?")
                .append(" and t.dimension_type1 = ?");
        if ("DMP新增数据".equals(type) || "DMP日活数据".equals(type)) {
            sql.append(" and t.dimension_type2 in ('3S', 'ALL', 'DSP', 'GA', 'M')");
        }
        return findMapList(sql.toString(), new Object[]{date, type});
    }

    /**
     * 获取各种ID统计数据
     * @param date
     * @return
     * @throws Exception
     */
    public List<Map<String, Object>> findIdsData(String date) throws Exception {
        StringBuilder sql = new StringBuilder();
        sql.append("select t.day_key, t.dimension_type2, sum(t.dau) as dau, sum(t.new) as new\n")
           .append("from (\n")
                .append("  SELECT t.day_key, t.dimension_type2, t.video_value AS dau, 0 AS new\n")
                .append("  FROM dmp_report_reuslt t\n" )
                .append( "  WHERE t.day_key = ? AND t.dimension_type1 = 'DMP日活数据'\n" )
                .append("       AND t.dimension_type2 IN ('androidid', 'gaid', 'idfa', 'imei', 'mac')\n")
                .append("  UNION ALL\n" )
                .append("  SELECT t.day_key, t.dimension_type2, 0 AS dau, t.video_value AS new\n" )
                .append("  FROM dmp_report_reuslt t\n" )
                .append("  WHERE t.day_key = ? AND t.dimension_type1 = 'DMP新增数据'\n" )
                .append("        AND t.dimension_type2 IN ('androidid', 'gaid', 'idfa', 'imei', 'mac')\n")
                .append(") t\n")
                .append("GROUP BY t.day_key, t.dimension_type2");
        return findMapList(sql.toString(), new Object[]{date, date});
    }

    public static void main(String[] args) throws Exception {
        ExportExcelDAO dao = new ExportExcelDAOImpl();
        List<Map<String, Object>> list = dao.findIdsData("20170822");
        System.out.println(list.size());
        for (Map<String, Object> map: list) {
            System.out.println(map.toString());
        }
    }
}