wlyy2.0/patient-co-statistics-es/src/main/java/com/yihu/wlyy/statistics/controller/ElasticsearchUtil.java

package com.yihu.wlyy.statistics.controller;

import com.alibaba.druid.sql.ast.SQLExpr;
import com.alibaba.druid.sql.ast.expr.SQLQueryExpr;
import com.alibaba.druid.sql.parser.SQLExprParser;
import com.yihu.wlyy.statistics.etl.save.es.ElasticFactory;
import com.yihu.wlyy.statistics.vo.SaveModel;
import org.elasticsearch.action.search.SearchResponse;
import org.nlpcn.es4sql.domain.Select;
import org.nlpcn.es4sql.jdbc.ObjectResult;
import org.nlpcn.es4sql.jdbc.ObjectResultsExtractor;
import org.nlpcn.es4sql.parse.ElasticSqlExprParser;
import org.nlpcn.es4sql.parse.SqlParser;
import org.nlpcn.es4sql.query.AggregationQueryAction;
import org.nlpcn.es4sql.query.DefaultQueryAction;
import org.nlpcn.es4sql.query.SqlElasticSearchRequestBuilder;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;
import org.springframework.util.StringUtils;

import java.util.ArrayList;
import java.util.List;

/**
 * Created by chenweida on 2017/7/17.
 * SELECT town,townName,sum(result1) result1 FROM wlyy_quota_test
 where quotaCode='1'
 group by town,townName ， date_histogram(field='quotaDate','interval'='week')
 */
@Component
public class ElasticsearchUtil {

    @Autowired
    private ElasticFactory elasticFactory;
    @Value("${es.type}")
    private String esType;
    @Value("${es.index}")
    private String esIndex;

    /**
     *
     * @param quotaCode  指标quotacode
     * @param startDate 开始日期 yyyy-MM-dd
     * @param endDate  结束日期 yyyy-MM-dd
     * @param timeLevel  1增量 2到达量
     * @param areaLevel 1 省 2 市 3 区县 4 机构 5团队
     * @param interval 1日 2周 3月
     * @return
     */
    public List<SaveModel> findQuotas(String quotaCode,
                                      String startDate,
                                      String endDate,
                                      String timeLevel,
                                      String areaLevel,
                                      String interval) {

        StringBuffer sql = new StringBuffer();
        StringBuffer groupBy = new StringBuffer();
        if (SaveModel.teamLevel.equals(areaLevel)) {
            sql.append("select team,teamName,result1,result2 from wlyy_quota_test where ");
        } else if (SaveModel.OrgLevel.equals(areaLevel)) {
            sql.append("select hospital,hospitalName,sum(result1) result1,sum(result2) result2 from wlyy_quota_test where ");
            groupBy.append(" group by hospital,hospitalName");
        } else if (SaveModel.townLevel.equals(areaLevel)) {
            sql.append("select town,townName,sum(result1) result1,sum(result2) result2 from wlyy_quota_test where ");
            groupBy.append(" group by town,townName");
        } else if (SaveModel.cityLevel.equals(areaLevel)) {
            sql.append("select city,cityName,sum(result1) result1,sum(result2) result2 from wlyy_quota_test where");
            groupBy.append(" group by city,cityName");
        }

        sql.append(" quotaCode='" + quotaCode + "'  ");
        sql.append(" and timeLevel='" + timeLevel + "'  ");
        sql.append(" and areaLevel='5'  ");
        sql.append(" and quotaDate >='" + startDate + "'  ");
        sql.append(" and quotaDate <='" + endDate + "'  ");

        sql.append(groupBy);
        return excute(sql.toString());
    }

    public List<SaveModel> findQuotas(String quotaCode,
                                      String quotaDate,
                                      String timeLevel,
                                      String areaLevel) {

        return null;
    }

    public List<SaveModel> findQuotasByChllevel(String quotaCode,
                                                String startDate,
                                                String endDate,
                                                String timeLevel,
                                                String areaLevel,
                                                String childAreaLevel) {

        return null;
    }

    public List<SaveModel> findQuotasByChllevel(String quotaCode,
                                                String quotaDate,
                                                String timeLevel,
                                                String areaLevel,
                                                String childAreaLevel) {

        return null;
    }

    /**
     * 执行sql查询es
     *
     * @param sql
     * @return
     */
    public List<SaveModel> excute(String sql) {
        List<SaveModel> saveModels = new ArrayList<>();
        try {
            SQLExprParser parser = new ElasticSqlExprParser(sql);
            SQLExpr expr = parser.expr();
            SQLQueryExpr queryExpr = (SQLQueryExpr) expr;

            Select select = null;
            select = new SqlParser().parseSelect(queryExpr);

            //通过抽象语法树，封装成自定义的Select，包含了select、from、where group、limit等
            AggregationQueryAction action = null;
            DefaultQueryAction queryAction = null;
            SqlElasticSearchRequestBuilder requestBuilder = null;
            if (select.isAgg) {
                //包含计算的的排序分组的
                action = new AggregationQueryAction(elasticFactory.getTransportClient(), select);
                requestBuilder = action.explain();
            } else {
                //封装成自己的Select对象
                queryAction = new DefaultQueryAction(elasticFactory.getTransportClient(), select);
                requestBuilder = queryAction.explain();
            }
            SearchResponse response = (SearchResponse) requestBuilder.get();
            ObjectResult temp = new ObjectResultsExtractor(true, true, true).extractResults(response.getAggregations(), true);
            List<String> heads = temp.getHeaders();
            temp.getLines().stream().forEach(one -> {
                try {
                    SaveModel saveModel = new SaveModel();
                    for (int i = 0; i < one.size(); i++) {
                        String key = "set" + UpFirstStr(heads.get(i));
                        Object value = one.get(i);
                        if (value instanceof String) {
                            SaveModel.class.getMethod(key, String.class).invoke(saveModel, value);
                        } else if (value instanceof Integer) {
                            SaveModel.class.getMethod(key, Integer.class).invoke(saveModel, value);
                        } else if (value instanceof Double) {
                            SaveModel.class.getMethod(key, Integer.class).invoke(saveModel, ((Double) value).intValue());
                        }
                    }
                    saveModels.add(saveModel);
                } catch (Exception e) {
                    e.printStackTrace();
                }
            });
        } catch (Exception e) {
            e.printStackTrace();
        }
        return saveModels;
    }

    /**
     * 首字母大写
     *
     * @param str
     * @return
     */
    public String UpFirstStr(String str) {
        return str.replaceFirst(str.substring(0, 1), str.substring(0, 1).toUpperCase());
    }
}