smartsearch/src/main/java/com/bowintek/practice/services/impl/GenSqlStringServiceImpl.java

package com.bowintek.practice.services.impl;

import com.bowintek.practice.mapper.SrSaerchtempMapper;
import com.bowintek.practice.mapper.SrSubjectMapper;
import com.bowintek.practice.mapper.SrSubjectfieldMapper;
import com.bowintek.practice.mapper.cquery.SubjectCQuery;
import com.bowintek.practice.mapper.cquery.TempCQuery;
import com.bowintek.practice.model.*;
import com.bowintek.practice.services.service.GenSqlStringService;
import com.bowintek.practice.services.service.TempService;
import com.bowintek.practice.util.multipdb.DBTypeEnum;
import com.bowintek.practice.util.multipdb.SwitchDataSource;
import com.bowintek.practice.vo.temp.*;
import org.apache.ibatis.jdbc.SqlRunner;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;

import java.sql.Connection;
import java.sql.DriverManager;
import java.text.MessageFormat;
import java.util.*;
import java.util.concurrent.RecursiveTask;
import java.util.stream.Collectors;

@Component
public class GenSqlStringServiceImpl implements GenSqlStringService {
    @Autowired
    SrSubjectMapper srSubjectMapper;
    @Autowired
    SrSubjectfieldMapper srSubjectfieldMapper;
    @Autowired
    TempCQuery tempCQuery;

    @Value("${spring.datasource.postgre.url}")
    private String postgreUrl;
    @Value("${spring.datasource.postgre.username}")
    private String postgreUsername;
    @Value("${spring.datasource.postgre.password}")
    private String postgrePassword;
    @Value("${spring.datasource.postgre.driver-class-name}")
    private String postgreDriver;

    @Override
    public List<Map<String,Object>> getListBySqlString(String sqlString){
        //return tempCQuery.getListBySqlString(sqlString);
        try {
            //加载数据库驱动
            Class.forName(postgreDriver);
            Connection connection = DriverManager.getConnection(postgreUrl, postgreUsername, postgrePassword);
            SqlRunner sqlRunner = new SqlRunner(connection);
            return sqlRunner.selectAll(sqlString);
        }
        catch (Exception ex){
            ex.printStackTrace();
            return null;
        }
    }

    @Override
    public Map<String,Object> getMapBySqlString(String sqlString){
        try {
            //加载数据库驱动
            Class.forName(postgreDriver);
            Connection connection = DriverManager.getConnection(postgreUrl, postgreUsername, postgrePassword);
            SqlRunner sqlRunner = new SqlRunner(connection);
            return sqlRunner.selectOne(sqlString);
        }
        catch (Exception ex){
            ex.printStackTrace();
            return null;
        }
    }

    @Override
    public int getIntBySqlString(String sqlString, String colName) {
        Map<String, Object> map = getMapBySqlString(sqlString);
        return Integer.parseInt(map.get(colName).toString());
    }

    //查询语句生成，要素点：查询主表、关联子表、查询字段、查询条件
    //查询主表：FROM (sr_subject.execSql) AS T
    //查询字段-维度：sr_tempdimension.fieldAlias
    //查询字段-度量：sr_tempmeasure.fieldAlias + 函数名称/表达式； SUM({$FIELD$}) / COUNT(*) 替换中间字段名称
    //查询条件：检测出 日期型、字符型、数字型，在针对性生成条件语句
    //输出字段 AS：ReFieldName0,1,2...；依据维度度量排序来
    //表名 AS：T 主表，T0,1,2...；子表，依据维度度量子表排序来
    @Override
    public GenSqlStringResult Generation(TempObjectModel data, String userID){
        GenSqlStringResult result = new GenSqlStringResult();
        result.excelNameList = new ArrayList<>();

        //取得字段原本定义
        data.sub = srSubjectMapper.selectByPrimaryKey(data.temp.getSubId());
        List<SrSubjectfield> fileds = getSubjectfieldsBySubId(data.temp.getSubId());
        //循环分析出关联表
        //isForeignKey leftFieldId rightFieldId referencesTab displayColumn
        int tabIndex = 0;
        HashMap<String, SqlColumnModel> aliasMap = new HashMap<>();
        List<SqlFieldModel> models = new ArrayList<>();
        data.bases.forEach(it->{ models.add(SqlFieldModel.GenModel(it.getFieldId(),it.getTempFeildId()));});
        data.measures.forEach(it->{ models.add(SqlFieldModel.GenModel(it.getFieldId(),it.getTempFeildId()));});

        //生成主要的字段、关联表、表T名称、字段T名称对照表
        String leftTableString = "";
        for(int i=0;i<models.size();i++){
            SrSubjectfield fd = findFieldByFieldId(fileds, models.get(i).getFieldId());
            SqlColumnModel cm = getSqlColumn(fd.getIsForeignKey(), fd.getFieldAlias(), fd.getFieldCode(), fd.getForeignKey(),
                    fd.getReferencesTab(), fd.getDisplayColumn(), tabIndex);
            if(cm.getIsForeignKey()) {
                tabIndex++;
                leftTableString+=cm.getLeftJoinString();
            }
            aliasMap.put(models.get(i).getTempFieldId(), cm);
        }

        int fieldIndex = 0;
        String colString = "", groupString = "";
        //维度生成，Group生成
        for(int i=0;i<data.bases.size();i++){
            SrTempdimension dim = data.bases.get(i);
            SqlColumnModel cm = aliasMap.get(dim.getTempFeildId());

            colString += MessageFormat.format("{0} {1} AS {2}\n\t",
                    getCommaString(fieldIndex), cm.getDisplayColumn(), getFieldName(fieldIndex));
            groupString += MessageFormat.format("{0} {1}\n\t",getCommaString(fieldIndex), cm.getDisplayColumn());
            cm.setMeasureColumn(cm.getDisplayColumn());

            //生成字段-名称对照表
            result.excelNameList.add(ExceNameModel.GenModel(
                    getFieldName(dim.getFieldName(), dim.getDisplayName()),
                    getFieldName(fieldIndex)));
            fieldIndex++;
        }

        //度量生成
        for(int i=0;i<data.measures.size();i++){
            SrTempmeasure msr = data.measures.get(i);
            SqlColumnModel cm = aliasMap.get(msr.getTempFeildId());

            colString += MessageFormat.format("{0} {1} AS {2}\n\t",
                    getCommaString(fieldIndex),
                    getMeasurString(msr.getOperation(), cm.getDisplayColumn()),
                    getFieldName(fieldIndex));
            cm.setMeasureColumn(getMeasurString(msr.getOperation(), cm.getDisplayColumn()));

            //生成字段-名称对照表
            result.excelNameList.add(ExceNameModel.GenModel(
                    getFieldName(msr.getFieldName(), msr.getDisplayName()),
                    getFieldName(fieldIndex)));
            fieldIndex++;
        }

        //查询条件的生成
        String whereString = "";
        for(int i=0;i<data.wheres.size();i++){
            SrTempfield stf = data.wheres.get(i);

            if(stf.getQueryType()==1 || stf.getQueryType()==4) whereString += genWhereString(stf);
            else if(stf.getQueryType()==2) whereString += genWhereNumber(stf);
            else if(stf.getQueryType()==3) whereString += genWhereDate(stf);
        }

        //生成排序字段
        String orderByString = "";
        if(IsNullEmpty(data.pagination.orderBy) || !aliasMap.containsKey(data.pagination.orderBy))
            data.pagination.orderBy = aliasMap.keySet().stream().findFirst().get();
        System.out.println("data.orderBy:"+data.pagination.orderBy);

        if(aliasMap.containsKey(data.pagination.orderBy)){
            if(IsNullEmpty(data.pagination.orderByString)) data.pagination.orderByString = "asc";
            SqlColumnModel cmOrder = aliasMap.get(data.pagination.orderBy);
            orderByString = MessageFormat.format("ORDER BY {0} {1}", cmOrder.getMeasureColumn(), data.pagination.orderByString);
        }

        String limitString = MessageFormat.format("LIMIT {0} OFFSET {1}",
                data.pagination.rows, (data.pagination.page-1) * data.pagination.rows);


        String mainSqlString = MessageFormat.format("SELECT {0} \nFROM {1} AS T {2} \nWHERE 1=1 {3} \nGROUP BY {4} \n{5} \n{6}",
                colString, data.sub.getTabCode(), leftTableString, whereString, groupString, orderByString, limitString);
        result.setMainSqlString(mainSqlString);

        String countSqlString = MessageFormat.format("SELECT COUNT(*) CNT FROM (SELECT {0} \nFROM {1} AS T {2} \nWHERE 1=1 {3} \nGROUP BY {4})",
                colString, data.sub.getTabCode(), leftTableString, whereString, groupString);
        result.setCountSqlStrimg(countSqlString);

        System.out.println(mainSqlString);
        System.out.println(countSqlString);
        return result;
    }

    public String getFieldName(String fieldName,String displayName){
        return IsNullEmpty(displayName)? fieldName : displayName;
    }

    public static boolean IsNullEmpty(String value) {
        return value == null || value.isEmpty();
    }
    public String genWhereString(SrTempfield stf){
        if(IsNullEmpty(stf.getOperation()) || IsNullEmpty(stf.getValue1()))
            return genRemark("genWhereString", stf.getTempFeildId());

        if(stf.getOperation().equals("="))
            return MessageFormat.format("\n\tAND T.{0}={1}", stf.getFieldAlias(),
                    "'"+stf.getValue1()+"'");
        else if(stf.getOperation().equals("like"))
            return MessageFormat.format("\n\tAND T.{0} like {1}", stf.getFieldAlias(),
                    "'%"+stf.getValue1()+"%'");
        else
            return MessageFormat.format("\n\tAND T.{0} {1}", stf.getFieldAlias(), stf.getOperation());
    }

    public String genWhereNumber(SrTempfield stf){
        if(IsNullEmpty(stf.getOperation()) || IsNullEmpty(stf.getValue1()) ||
                (stf.getOperation().equals("limit") && IsNullEmpty(stf.getValue2())))
            return genRemark("genWhereNumber", stf.getTempFeildId());

        if(stf.getOperation().equals("limit"))
            return MessageFormat.format("\n\tAND T.{0} >= {1} AND T.{2}<={3}",
                    stf.getFieldAlias(), stf.getValue1(), stf.getFieldAlias(), stf.getValue2());
        else
            return MessageFormat.format("\n\tAND T.{0} {1} {2}", stf.getFieldAlias(), stf.getOperation(), stf.getValue1());
    }

    public String genWhereDate(SrTempfield stf){
        if(IsNullEmpty(stf.getOperation())) return genRemark("genWhereDate", stf.getTempFeildId());

        String where = "";
        String[] arys = stf.getOperation().split(",");
        if(stf.getOperation().equals("date") || stf.getOperation().equals("datetime")) {
            if (!IsNullEmpty(stf.getValue1()))
                where += MessageFormat.format("\n\tAND T.{0} >= {1}", stf.getFieldAlias(),
                        "'" + stf.getValue1() + "'");
            if (!IsNullEmpty(stf.getValue2()))
                where += MessageFormat.format("\n\tAND T.{0} <= {1}", stf.getFieldAlias(),
                        "'" + stf.getValue2() + "'");
        }
        else if(arys.length>1){
            if (!IsNullEmpty(stf.getValue1()))
                where += MessageFormat.format("\n\tAND T.{0} {1} {2}", stf.getFieldAlias(),
                        arys[1], "'" + stf.getValue1() + "'");
        }

        return IsNullEmpty(where)?genRemark("genWhereDate", stf.getTempFeildId()) : where;
    }

    public String genRemark(String fun,String key) {
        //return "\n/*" + fun + " " + key + " is gen err*/";
        return "";
    }

    public SrSubjectfield findFieldByFieldId(List<SrSubjectfield> fields, String fieldId){
        return fields.stream().filter(t->t.getFieldId().equals(fieldId)).findAny().orElse(null);
    }

    public List<SrSubjectfield> getSubjectfieldsBySubId(String subId){
        SrSubjectfieldExample example = new SrSubjectfieldExample();
        SrSubjectfieldExample.Criteria criteria = example.or();
        criteria.andSubIdIn(Arrays.asList(new String[]{subId}));
        List<SrSubjectfield> fileds = srSubjectfieldMapper.selectByExample(example);
        System.out.println("getSubjectfieldsBySubId Size:"+fileds.size());
        return fileds;
    }

    public SqlColumnModel getSqlColumn(int isForeignKey, String filedAlias, String leftFieldId, String rightFieldId,
                                       String referencesTab, String displayColumn, int index){
        SqlColumnModel model = new SqlColumnModel();
        if(isForeignKey==0 || IsNullEmpty(leftFieldId) || IsNullEmpty(rightFieldId) || IsNullEmpty(referencesTab) || IsNullEmpty(displayColumn)){
            model.setIsForeignKey(false);
            model.setTableAlias("T");
            model.setDisplayColumn(MessageFormat.format("{0}.{1}","T", filedAlias));
            return model;
        }

        model.setIsForeignKey(true);
        model.setTableAlias("T"+index);
        String leftJoinString = MessageFormat.format("\nLEFT JOIN {0} AS {1} on T.{2}={3}.{4}",
                referencesTab, model.getTableAlias(), leftFieldId, model.getTableAlias(), rightFieldId);
        model.setLeftJoinString(leftJoinString);
        model.setDisplayColumn(MessageFormat.format("{0}.{1}",model.getTableAlias(), displayColumn));

        return model;
    }

    public String getFieldName(int index){
        return "RENAME"+index;
    }

    public String getCommaString(boolean is){
        return is?",":"";
    }

    public String getCommaString(int index){
        return getCommaString(index>0);
    }

    public String getMeasurString(String oprationStr, String fieldName){
        return oprationStr.replaceAll("\\{\\$FIELD\\$\\}", fieldName);
    }
}