package com.usoftchina.bi.server.dao.chart;

import com.usoftchina.bi.core.base.TestPage;
import com.usoftchina.bi.server.model.bo.*;
import com.usoftchina.bi.server.model.po.TargetData;
import org.apache.ibatis.annotations.CacheNamespace;
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
@CacheNamespace
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} ${screenToColumn}) where rownum <= #{num}")
    List<String> getXAxis(@Param("xAxisName") String xAxisName, @Param("tableName") String tableName, @Param("screenToColumn") String screenToColumn, @Param("num") int num);


    //XY轴数据
    @Select("select ${dataType}(${yAxisName}) as \"value\",${xColumn} as \"date\" from ${tableName} where 1=1 ${screen} group by ${xColumn}")
    List<LineSeriesMap> getXAndYValues(@Param("yAxisName") String yAxisName, @Param("xColumn") String xColumn,
                          @Param("tableName") String tableName, @Param("dataType") String dataType,
                          @Param("screen") String screen);

    //x轴数据
    @Select("select ${dataType}(${yAxisName}) from ${tableName} where ${xColumn} = #{xdata} ${screen}")
    String getXValue(@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 1=1 ${screen} and ${xColumn} in (select * from (select DISTINCT ${xColumn} from ${tableName} ${screenToColumn}) where rownum <= #{num}) GROUP BY ${xColumn}")
    List<Double> getValues(@Param("yAxisName") String yAxisName, @Param("xColumn") String xColumn,
                    @Param("tableName") String tableName, @Param("dataType") String dataType,
                    @Param("screen") String screen, @Param("screenToColumn") String screenToColumn, @Param("num") int num);

    //x轴数据 -> 饼图优化后
    @Select("select ${dataType}(${yAxisName}) value, ${xColumn} name from ${tableName} where 1=1 ${screen} GROUP BY ${xColumn}")
    List<PieSeriesMap> getPieXValues(@Param("yAxisName") String yAxisName, @Param("xColumn") String xColumn,
                                    @Param("tableName") String tableName, @Param("dataType") String dataType,
                                    @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 * FROM (select ${groupsBy} from ${tableName} where ${groupsBy} is not null ${screen} group by ${groupsBy}) WHERE rownum<=#{count}")
    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} ${screen} 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} and ${xAxisName} is not null 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}) as \"value\",(to_char(${xAxisName},'YYYY-MM-DD')) as \"date\" from ${tableName} ${screen} GROUP BY (to_char(${xAxisName},'YYYY-MM-DD')) ORDER BY (to_char(${xAxisName},'YYYY-MM-DD'))")
    List<LineSeriesMap> getTimeValues (@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 1=1 ${screen} GROUP BY (to_char(${xAxisName},${timeType})) ")
    List<Double> getHistogramTimeValue(@Param("dataType") String dataType, @Param("yAxisName") String yAxisName,
                                       @Param("tableName") String tableName, @Param("xAxisName") String xAxisName,
                                       @Param("timeType") String timeType, @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);

    /**
     * 无分组半年时间值(柱状图)
     * @param dataType
     * @param yAxisName
     * @param tableName
     * @param xAxisName
     * @param firstIndex
     * @param afterIndex
     * @param screen
     * @return
     */
    @Select("select ${dataType}(${yAxisName}) from ${tableName} where TO_CHAR(${xAxisName},'YYYY-MM') between #{firstIndex} and #{afterIndex} ${screen}")
    Double getHistogramTimeValueYear(@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, jdbcType=VARCHAR} ")
    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}) as \"value\",TO_CHAR(${xColumn},${timeType}) as \"date\" from ${tableName} where ${groupsColumn}=#{groupsIndex} GROUP BY TO_CHAR(${xColumn},${timeType}) ")
    List<LineSeriesMap> getGroupValuesTime(@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
    );

    //查询时间类型分组的值->优化后(柱状图)
    @Select("select ${dataType}(${yAxisName}) from ${tableName} where ${groupsColumn}=#{groupsIndex} GROUP BY TO_CHAR(${xColumn},${timeType}) ORDER BY TO_CHAR(${xColumn},${timeType})")
    List<Double> getHistogramTimeValueGroupValuesTime(@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);

    //查询时间类型分组的值->优化后
    @Select("select ${dataType}(${yAxisName}) as \"value\",TO_CHAR(${xColumn},${timeType}) as \"date\",${groupsColumn} as \"group\" from ${tableName} where ${groupsColumn} IN (SELECT ${groupsColumn} FROM ${tableName} GROUP BY ${groupsColumn}) ${screen} GROUP BY TO_CHAR(${xColumn},${timeType}),${groupsColumn} ")
    List<LineSeriesGroupMap> getGroupsValuesTime(@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("screen") String screen
    );

    //有分组时时间类型为空值
    @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 ${dataType}(${yAxisName}) from ${tableName} where TO_CHAR(${xAxisName},'YYYY-MM') between #{firstIndex} and #{afterIndex} and ${groupsColumn}=#{groupsIndex} ${screen}")
    Double getHistogramTimeValueHalfYear (@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);

    /**
     * 不重复计数
     * @return
     */
    @Select("SELECT COUNT(${columnName}) FROM (SELECT DISTINCT ${columnName} FROM ${tableName} ${screen})")
    Double getDistinctCountColumnData(@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 ${tableName} where ${columnName} between " +
            " (select ${fOperation}(${columnName}) from ${tableName}) 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 ${columnName} like '%'||#{keyword,jdbcType=VARCHAR}||'%' and rownum <= #{count, jdbcType=INTEGER}")
    List<Object> getScreenData(@Param("columnName") String columnName, @Param("tableName") String tableName, @Param("keyword") String keyword, @Param("count") int count);

    /**
     * 柱状图无分组取数
     * @param fieldName
     * @param tableName
     * @param condition
     * @param sort
     * @param rule
     * @return
     */
    @Select("SELECT ${fieldName} FROM ${tableName} WHERE ${condition} ORDER BY ${sort} ${rule}")
    List<HistogramData> getHistogramValueWithoutGroup(@Param("fieldName") String fieldName, @Param("tableName") String tableName, @Param("condition") String condition,
                                                      @Param("sort") String sort, @Param("rule") String rule);

    /**
     * 柱状图分组取数
     * @param fieldName
     * @param tableName
     * @param condition
     * @param groupCondition
     * @param sort
     * @param rule
     * @return
     */
    @Select("SELECT ${fieldName} FROM ${tableName} WHERE ${condition} GROUP BY ${groupCondition} ORDER BY ${sort} ${rule}")
    List<HistogramGroupData> getHistogramValueWithGroup(@Param("fieldName") String fieldName, @Param("tableName") String tableName, @Param("condition") String condition, @Param("groupCondition") String groupCondition,
                                                        @Param("sort") String sort, @Param("rule") String rule);

    /**
     * 折线图无分组取数
     * @param fieldName
     * @param tableName
     * @param condition
     * @return
     */
    @Select("SELECT ${fieldName} FROM ${tableName} WHERE ${condition} ORDER BY ${sort} ${rule}")
    List<LineSeriesMap> getLineValueWithoutGroup(@Param("fieldName") String fieldName, @Param("tableName") String tableName, @Param("condition") String condition,
                                                 @Param("sort") String sort, @Param("rule") String rule);

    /**
     * 折线图分组取数
     * @param fieldName
     * @param tableName
     * @param condition
     * @param groupCondition
     * @return
     */
    @Select("SELECT ${fieldName} FROM ${tableName} WHERE ${condition} GROUP BY ${groupCondition} ORDER BY ${sort} ${rule}")
    List<LineSeriesGroupMap> getLineValueWithGroup(@Param("fieldName") String fieldName, @Param("tableName") String tableName, @Param("condition") String condition, @Param("groupCondition") String groupCondition,
                                                   @Param("sort") String sort, @Param("rule") String rule);

}
