platform-bi-server/src/main/java/com/dao/DataConnectorMapper.java

package com.dao;

import com.model.po.DataConnector;
import com.model.po.DataConnectorList;
import com.model.po.Databases;
import com.model.vo.configVo.DatabasesInfo;
import com.model.vo.configVo.GroupInfo;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;

@Mapper
@Repository
public interface DataConnectorMapper {

    /*
    查询数据源创建人ID
     */
    @Select("select c.create_id from bi_data_connectors c,bi_charts cs where cs.id = #{id} and cs.bd_data_id = c.id")
    int getCreateId(int id);

    /*
    查询数据源ID
     */
    @Select("select c.id from bi_data_connectors c,bi_charts cs where cs.id = #{id} and cs.bd_data_id = c.id")
    int getBaseId(int id);

    /*
    查询单个数据源
     */
    @Select("select ID as dataId, DATA_NAME as dataName, DATA_NOTE as note, DATA_TAG as dataTag, COLUMNS_CONFIG as columnConfig, LOAD_OBJECT as loadObject," +
            "CREATE_BY as createBy, create_id as createId, USED_NUMBER as userNumber, CREATE_DATE as createDate,DB_CONFIG as dbConfig, BD_group as connectorGroup," +
            "CON_TYPE as type from bi_data_connectors where id = #{id}")
    DataConnector getOneData(int id);

    /*
    查询自己创建的,权限分配的数据源列表,已经启用
     */
    @Select("select id as dataId, con_type as type, data_name as dataName, data_tag as dataTag, data_note as note, " +
            "bc.create_by as createBy, bc.create_date as createDate," +
            "used_number as usedNumber, db_config as dbConfig ,BD_group as connectorGroup, " +
            "create_id as createId from bi_data_connectors bc where CREATE_ID = #{id} or ( id in ( select distinct bs_db_id from BI_DB_STRATEGYS where bs_id in (" +
            "  select bo_st_id from bi_db_object  where " +
            "  (BO_TYPE='1' and bo_ob_id = #{id} ) or " +
            "  ( bo_type='0' and bo_ob_id in " +
            "  ( select br_user_group from bi_user_rel_groups where br_user_id = #{id} ) " +
            "   )" +
            "))) and is_open = '1'")
    List<DataConnectorList> getDataConnectorList(int id);


    /*
    转交数据源
     */
    @Update("update bi_data_connectors set create_by = #{name}, create_id = #{createId} where id = #{baseId}")
    void updataOrder(@Param("name") String name, @Param("createId") int userId, @Param("baseId") int baseId);

    /*
    插入数据源配置
     */
    @Insert("INSERT INTO bi_data_connectors(id,data_name,data_note,data_tag,con_type,LOAD_OBJECT,DB_CONFIG,columns_config,used_number,create_by,create_date, table_name, BD_GROUP, create_id) " +
            "VALUES (#{dataId},#{dataName}, #{note}, #{dataTag}, #{type}, #{loadObject}, #{dbConfig},#{columnConfig}, #{usedNumber},#{createBy}, to_date(#{createDate},'YYYY-MM-DD hh24:mi:ss'), #{tableName}, #{connectorGroup},#{createId})")
    @SelectKey(before=true,keyProperty="dataId",resultType=int.class,statement="SELECT BI_DASHBOARDS_squence.nextval from dual",keyColumn = "id")
    void insertDataConnector(DataConnector dataConnector);

    /*
    更新数据源配置
     */
    @Update("<script>" +
            "UPDATE bi_data_connectors set "  +
            "data_name = #{dataName}" +
            "<if test=\"dataTag != null\"> , data_tag = #{dataTag} </if>" +
            "<if test=\"loadObject != null\"> , LOAD_OBJECT = #{loadObject} </if>" +
            "<if test=\"columnConfig != null\"> , columns_config = #{columnConfig} </if>" +
            "<if test=\"dbConfig != null\"> , DB_CONFIG = #{dbConfig} </if>" +
            "<if test=\"usedNumber != null\"> , used_number = #{usedNumber} </if>" +
            "<if test=\"createBy != null\"> , create_by = #{createBy} </if>" +
            "<if test=\"type != null\"> , con_TYPE = #{type} </if>" +
            "<if test=\"note != null\"> , DATA_NOTE = #{note} </if>" +
            "<if test=\"tableName != null\"> , table_name = #{tableName} </if>" +
            "<if test=\"connectorGroup != null\"> , BD_GROUP = #{connectorGroup} </if>"+
            "where id = #{dataId}" +
            "</script>")
    void updateData(DataConnector dataConnector);

    /*
    更新数据源配置的分组
     */
    @Update("update bi_data_connectors set BD_GROUP = #{connectGroup} where id = #{dataId}")
    void updateConfigGroup(@Param("dataId") int dataId, @Param("connectGroup") int connectGroup);

    /*
    删除数据源
     */
    @Delete("<script>" +
            "delete from bi_data_connectors where id in " +
            "("+
            "<foreach collection=\"list\" index=\"index\" item=\"item\"  separator=\",\">" +
            "#{item, jdbcType = NUMERIC}"+
            "</foreach>" +
            ")"+
            "</script>")
    void deleteData(List<Integer> idList);

    /*
    保存数据库连接
     */
    @Insert("insert into BI_DATABASES(id,ADDRASS, CREATE_DATE, DATABASE_TYPE, DATA_NAME, bases_NAME, PASS_WORD, PORT, USER_NAME, note)" +
            "values(#{id},#{addrass}, to_date(#{createDate},'YYYY-MM-DD hh24:mi:ss'), #{databaseType}, #{dataName}, #{name}, #{passWord}, #{port}, #{userName}, #{note})")
    @SelectKey(before=true,keyProperty="id",resultType=int.class,statement="SELECT bi_databases_squence.nextval from dual",keyColumn = "id")
    void inputDataBases(Databases databases);

    /*
    查询单个数据库连接密码
     */
    @Select("select pass_word from BI_DATABASES where  id= #{id}")
    String getBasesById(int id);

    /*
    查询单个数据库连接
     */
    @Select("select id, bases_name as name, note, addrass, data_name as dataName, DATABASE_TYPE as databaseType," +
            " PASS_WORD as passWord, USER_NAME as userName, PORT, CREATE_DATE as createDate from bi_databases where id = #{id}")
    DatabasesInfo getDatabases(int id);

    /*
    查询数据库配置列表
     */
    @Select("select id, bases_name as name, note, addrass, data_name as dataName, DATABASE_TYPE as databaseType," +
            " PASS_WORD as passWord, USER_NAME as userName, PORT, CREATE_DATE as createDate from bi_databases")
    List<Databases> getDatabasesList();

    /*
    修改数据库配置列表
     */
    @Update("<script>"+
            "UPDATE BI_DATABASES set "  +
            "BASES_NAME = #{name}" +
            "<if test=\"addrass != null\"> , ADDRASS = #{addrass} </if>" +
            "<if test=\"port != null\"> , PORT = #{port} </if>" +
            "<if test=\"databaseType != null\"> , DATABASE_TYPE = #{databaseType} </if>" +
            "<if test=\"dataName != null\"> , DATA_NAME = #{dataName} </if>" +
            "<if test=\"userName != null\"> , USER_NAME = #{userName} </if>" +
            "<if test=\"passWord != null\"> , PASS_WORD = #{passWord} </if>" +
            "<if test=\"note != null\"> , NOTE = #{note} </if>" +
            "where id = #{id}" +
            "</script>")
    void updatabases(Databases databases);

    /*
    删除数据库配置
     */
    @Delete("<script>" +
            "delete from bi_databases where id in " +
            "("+
            "<foreach collection=\"list\" index=\"index\" item=\"item\"  separator=\",\">" +
            "#{item, jdbcType = NUMERIC}"+
            "</foreach>" +
            ")"+
            "</script>")
    void deleteDatabases(List<Integer> idList);

    /*
    查询数据源列数据
     */
    @Select("select columns_Config as columnConfig from bi_data_connectors " +
            " where id =#{id}")
    DataConnector getColumnData(int id);

    /*
    查询数据库配置
     */
    @Select("select db_config from bi_data_connectors where id = #{id}")
    String getDbConfig(int id);

    /*
    创建数据源分组
     */
    @Insert("insert into bi_base_group_by(bb_id, bb_group_name, bb_index, bb_father_id, create_date)" +
            "values(#{id}, #{groupName}, #{groupIndex}, #{fatherId}, to_date(to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),'YYYY-MM-DD hh24:mi:ss'))")
    @SelectKey(before=true,keyProperty="id",resultType=int.class,statement="SELECT bi_base_group_by_squence.nextval from dual",keyColumn = "bb_id")
    void setConnectorGroup(GroupInfo group);

    /*
    更新数据源分组
     */
    @Update("<script>"+
            "UPDATE bi_base_group_by set "+
            "bb_group_name = #{groupName}" +
            "<if test=\"groupIndex != null\"> , bb_index = #{groupIndex} </if>" +
            "<if test=\"fatherId != null\"> , bb_father_id = #{fatherId} </if>" +
            "<if test=\"createBy != null\"> , create_by = #{createBy} ,</if>" +
            "updata_date = to_date(to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),'YYYY-MM-DD hh24:mi:ss') "+
            " where bb_id = #{id}" +
            "</script>")
    void updataConnectorGroup(GroupInfo groupInfo);

    /*
    删除分组
     */
    @Delete("<script>" +
            "delete from bi_base_group_by where bb_id in" +
            "(" +
            "<foreach collection=\"list\" index=\"index\" item=\"item\"  separator=\",\">" +
            "#{item, jdbcType = NUMERIC}" +
            " </foreach>" +
            ")" +
            "</script>")
    void delConnectorGroup(List<Integer> isList);

    /*
    查询分组是否有子分组
     */
    @Select("select BB_GROUP_NAME from bi_base_group_by where bb_father_id = #{id}")
    List<String> getFatherId(int id);

    /*
    查询是否有数据源正在使用
     */
    @Select("select data_name from bi_data_connectors where bd_group = #{id}")
    List<String> getConName(int id);

    /*
    查询分组
     */
    @Select("select bb_id as id, bb_group_name as groupName, bb_index as groupIndex, bb_father_id as fatherId, create_by as createBy," +
            "create_date as createDate from bi_base_group_by")
    List<GroupInfo> getConnectorGroup();

    /*
   查询数据源是否关联图表
    */
    @Select("select chart_name from bi_charts where bd_data_ID = #{id}")
    List<String> getChartsName(int id);

    /*
    查询数据源创建人ID
     */
    @Select("select create_id from bi_data_connectors where id = #{id}")
    int getCreateIdById(@Param("id") int id);

    /*
    获取数据（根据权限）
     */
    @Select("${sql}")
    List<Map<String, Object>> getValues(@Param("sql") String sql);

    /*
    通过数据源ID取sql
     */
    @Select("select table_name from bi_data_connectors where id = #{id}")
    String getSqlByid(int id);

}