platform-bi-server/src/main/java/com/dao/chart/ShowChartsMapper.java

package com.dao.chart;

import com.model.po.TargetData;
import com.model.pojo.TestPage;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import java.util.LinkedHashMap;
import java.util.List;

@Mapper
@Repository
public interface ShowChartsMapper {
    /*
    查询X轴列数
     */
    @Select("select count(*) from (select DISTINCT ${xAxisName} from ${tableName})")
    int getNumForX(@Param("xAxisName") String xAxisName, @Param("tableName") String tableName);

    //x轴
    @Select("select * from (select DISTINCT ${xAxisName} from ${tableName}) where rownum <= #{num}")
    List<String> getXAxis(@Param("xAxisName") String xAxisName, @Param("tableName") String tableName, @Param("screenToColumn") String screenToColumn, @Param("num") int num);


    //x轴数据
    @Select("select ${dataType}(${yAxisName}) from ${tableName} where ${xColumn} = #{xdata} ${screen}")
    String getValues(@Param("yAxisName") String yAxisName, @Param("xColumn") String xColumn,
                     @Param("tableName") String tableName, @Param("dataType") String dataType,
                     @Param("xdata") String xdata, @Param("screen") String screen);

    //x轴为空时的数据
    @Select("select ${dataType}(${yAxisName}) from ${tableName} where ${xColumn} is null ${screen}")
    String getValuesIsNull(@Param("yAxisName") String yAxisName, @Param("xColumn") String xColumn,
                     @Param("tableName") String tableName, @Param("dataType") String dataType,
                           @Param("screen") String screen);



    //查询分组系列
    @Select("select ${groupsBy} from ${tableName} where 1=1 ${screen} and rownum<=#{count} group by ${groupsBy}")
    List<String> getGroups(@Param("groupsBy") String groupsBy, @Param("tableName") String tableName, @Param("screen") String screen, @Param("count") int count);

    //查询分组系列个数
    @Select("select count(*) from (select ${groupsBy} from ${tableName} where 1=1 ${screen} group by ${groupsBy})")
    int getGroupsCount(@Param("groupsBy") String groupsBy, @Param("tableName") String tableName, @Param("screen") String screen);


    //查询分组的值
    @Select("select ${dataType}(${yAxisName}) from ${tableName} where ${groupsColumn} and ${xColumn} ${screen}")
    String getGroupsValue(@Param("dataType") String dataType, @Param("yAxisName") String yAxisName, @Param("tableName") String tableName,
                          @Param("groupsColumn") String groupsColumn,
                          @Param("xColumn") String xColumn,
                          @Param("screen") String screen);


    //时间类型的X轴
    @Select("select distinct to_char(${xAxisName},${timeType})time from ${tableName} where rownum <= #{count} ORDER by to_char(${xAxisName},${timeType}) ASC")
    List<String> getTimeDate(@Param("xAxisName") String xAxisName, @Param("tableName") String tableName,
                             @Param("timeType") String timeType, @Param("screen") String screen, @Param("count") int count);

    //时间类型的X轴查count
    @Select("select count(*) from (select distinct to_char(${xAxisName},${timeType})time from ${tableName} ${screen} ORDER by to_char(${xAxisName},${timeType}) ASC)")
    int getTimeDateConunt(@Param("xAxisName") String xAxisName, @Param("tableName") String tableName,
                             @Param("timeType") String timeType, @Param("screen") String screen);

    //半年
    @Select("select distinct case when to_char(${xAxisName},'MM')<=6 then to_char(${xAxisName},'YYYY')|| '-H1'" +
            "else to_char(${xAxisName},'YYYY')||'-H2' end time from ${tableName} ${screen} order by time")
    List<String> getTimeYear(@Param("xAxisName") String xAxisName, @Param("tableName") String tableName, @Param("screen") String screen);
    
    //无分组时间类型值
    @Select("select ${dataType}(${yAxisName}) from ${tableName} where (to_char(${xAxisName},${timeType})) = #{xColumnIndex}  ${screen}")
    String getTimeValue (@Param("dataType") String dataType, @Param("yAxisName") String yAxisName,
                         @Param("tableName") String tableName, @Param("xAxisName") String xAxisName,
                         @Param("timeType") String timeType, @Param("xColumnIndex") String xColumnIndex,
                         @Param("screen") String screen);

    //无分组半年时间值
    @Select("select ${dataType}(${yAxisName}) from ${tableName} where TO_CHAR(${xAxisName},'YYYY-MM') between #{firstIndex} and #{afterIndex} ${screen}")
    String getTimeValueYear(@Param("dataType") String dataType, @Param("yAxisName") String yAxisName, @Param("tableName") String tableName,
                            @Param("xAxisName") String xAxisName, @Param("firstIndex") String firstIndex,
                            @Param("afterIndex") String afterIndex, @Param("screen") String screen);

    //时间为空时间值
    @Select("select ${dataType}(${yAxisName}) from ${tableName} where ${xAxisName} is null ${screen}")
    String getTimeIsNull(@Param("dataType") String dataType, @Param("yAxisName") String yAxisName, @Param("tableName") String tableName,
                         @Param("xAxisName") String xAxisName, @Param("screen") String screen);


    //查询时间类型分组的值
    @Select("select ${dataType}(${yAxisName}) from ${tableName} where ${groupsColumn}=#{groupsIndex} and TO_CHAR(${xColumn},${timeType})=#{xColumnIndex} ")
    String getGroupsValueTime(@Param("dataType") String dataType, @Param("yAxisName") String yAxisName, @Param("tableName") String tableName,
                          @Param("groupsColumn") String groupsColumn, @Param("groupsIndex") String groupsIndex,
                          @Param("xColumn") String xColumn, @Param("timeType") String timeType, @Param("xColumnIndex") String xColumnIndex
                           );

    //有分组时时间类型为空值
    @Select("select ${dataType}(${yAxisName}) from ${tableName} where ${groupsColumn} is null and TO_CHAR(${xColumn},${timeType})=#{xColumnIndex} ${screen}")
    String getGroupsValueTimeIsNull(@Param("dataType") String dataType, @Param("yAxisName") String yAxisName, @Param("tableName") String tableName,
                              @Param("groupsColumn") String groupsColumn, @Param("groupsIndex") String groupsIndex,
                              @Param("xColumn") String xColumn, @Param("timeType") String timeType,
                                    @Param("xColumnIndex") String xColumnIndex, @Param("screen") String screen
    );


    //查询分组的值（时间类型半年）
    //分组半年时间值
    @Select("select ${dataType}(${yAxisName}) from ${tableName} where TO_CHAR(${xAxisName},'YYYY-MM') between #{firstIndex} and #{afterIndex} and ${groupsColumn}=#{groupsIndex} ${screen}")
    String getTimeValueHalfYear(@Param("dataType") String dataType, @Param("yAxisName") String yAxisName, @Param("tableName") String tableName,
                            @Param("xAxisName") String xAxisName, @Param("firstIndex") String firstIndex, @Param("afterIndex") String afterIndex,
                                @Param("groupsColumn") String groupsColumn, @Param("groupsIndex") String groupsIndex, @Param("screen") String screen);


    /*
   总体未分组
    */
    @Select("select ${operation}(${columnName}) from ${tableName} ${screen}")
    Double getColumnDev(@Param("tableName") String tableName, @Param("columnName") String columnName,
                        @Param("operation") String operation, @Param("screen") String screen);

    /*
   总体未分组
    */
    @Select("select ${operation}(${columnName}) from ${tableName} ${screen}")
    Double getColumnData(@Param("tableName") String tableName, @Param("columnName") String columnName,
                        @Param("operation") String operation, @Param("screen") String screen);

    /*
    取主键名称
     */
    @Select("select column_name from user_cons_columns where table_name = #{tableName} and constraint_name = #{tableKey}")
    String getTableKey(@Param("tableName") String tableName, @Param("tableKey") String tableKey, @Param("screen") String screen);

    /*
    取目标列的值
     */
    @Select("select ${keyName} as id,${columnName} as data from (select ${keyName},${columnName} from ${tableName} order by ${columnName} ${sort}) where rownum<${line}")
    List<TargetData> getTargetData(@Param("keyName") String keyName, @Param("columnName") String columnName, @Param("tableName") String tableName, @Param("sort") String sort, @Param("line") String line);

    /*
    获取其他类数据
     */
    @Select("select ${columnList} from (select ${columnList} from ${tableName} ${screen} order by ${columnName} ${sort})")
    List<LinkedHashMap<String, Object>> getOtherData(@Param("columnList") String columnList, @Param("tableName") String tableName, @Param("columnName") String columnName,
                                                     @Param("sort") String sort, @Param("screen") String screen, TestPage testPage);

    /*
    总体统计求值
     */
    @Select("select ${operation}(${columnName}) from ${tableName} where ${columnName} between #{first} and #{last}")
    Double getValue(@Param("tableName") String tableName, @Param("columnName") String columnName, @Param("operation") String operation, @Param("first") Double first,
                    @Param("last") Double last);

    /*
    总体分组的值
     */
    @Select("select ${groupBy},${operation} from ${tableName} ${screen} group by ${groupBy} order by ${groupByOne}")
    List<LinkedHashMap<String, Object>> getGroupByValue(@Param("groupBy") String groupBy, @Param("operation") String operation, @Param("tableName") String tableName,
                                                        @Param("groupByOne") String groupByOne, @Param("screen") String screen);

    /*
    总体TH值
     */
    @Select("select median(${columnName}) from sale where ${columnName} between " +
            " (select ${fOperation}(${columnName}) from sale) and (select ${aOperation}(${columnName}) from ${tableName})" +
            " group by " +
            " ${groupBy} order by ${groupByOne}")
    List<LinkedHashMap<String, Object>> getGroupByValueTh(@Param("groupBy") String groupBy, @Param("columnName") String columnName, @Param("tableName") String tableName,
                                                        @Param("groupByOne") String groupByOne, @Param("screen") String screen, @Param("fOperation") String fOperation,
                                                          @Param("aOperation") String aOperation);

    /*
    查询筛选列信息
     */
    @Select("select * from (select distinct ${columnName} from ${tableName} )where rownum <= 20")
    List<Object> getScreenData(@Param("columnName") String columnName, @Param("tableName") String tableName);

}