platform-bi-server/src/main/java/com/dao/dashboard/DashboardsMapper.java

package com.dao.dashboard;

import com.model.bo.DashOrder;
import com.model.po.Dashboards;
import com.model.pojo.TestPage;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

import java.util.List;


@Mapper
@Repository
public interface DashboardsMapper {

    /*
    插入
     */
    @Insert("insert into " +
            "bi_dashboards(id, bd_name,bd_note,CONFIGURATION,create_by,create_date, BD_THUMBNAIL,relation_columns, create_id)" +
            "values(#{id}, #{bdName},#{bdNote},#{bdConfiguration},#{createBy}," +
            "to_date(to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),'YYYY-MM-DD hh24:mi:ss'), #{thumbnail},#{relationColumns}, #{createId})")
    @SelectKey(before=true,keyProperty="id",resultType=Integer.class,statement="SELECT bi_dashboards_squence.nextval from dual",keyColumn = "id")
    void setDashboards(Dashboards dashboards);

    /*
    更新
     */
    @Update("<script>" +
            "UPDATE BI_DASHBOARDS set "  +
            "BD_NAME = #{bdName}" +
            "<if test=\"bdNote != null\"> , BD_NOTE = #{bdNote} </if>" +
            "<if test=\"bdConfiguration != null\"> , CONFIGURATION = #{bdConfiguration} </if>" +
            "<if test=\"thumbnail != null\"> , BD_THUMBNAIL = #{thumbnail} </if>" +
            "<if test=\"relationColumns != null\">, relation_columns = #{relationColumns} </if>"+
            " ,UPDATE_DATE = to_date(to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),'YYYY-MM-DD hh24:mi:ss') " +
            "where id = #{id}" +
            "</script>")
    void updateDashboards(Dashboards dashboards);

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

    /*
    查询看板列表
     */
    @Select("select id, bd_note as bdNote, bd_name as bdName, CONFIGURATION as bdConfiguration, CREATE_BY as createBy, create_id as createId, CREATE_DATE as createDate, BD_THUMBNAIL as thumbnail " +
            "from BI_DASHBOARDS where id in " +
            " (select bo_da_id from bi_DASHBOARDS_object " +
            "where (bo_type = '0' and bo_ob_id in (select br_user_group from bi_user_rel_groups where br_user_id= #{userId})) " +
            " or (BO_TYPE='1' and bo_ob_id = #{userId} )) or create_id = #{userId}")
    List<Dashboards> getListDashboards(@Param("userId") int userId, TestPage testPage);

    /*
    查询看板
     */
    @Select("select id, bd_note as bdNote, bd_name as bdName, CONFIGURATION as bdConfiguration, CREATE_BY as createBy, create_id as createId, CREATE_DATE as createDate, BD_THUMBNAIL as thumbnail, relation_columns as relationColumns" +
            " from BI_DASHBOARDS where (id in " +
            " (select bo_da_id from bi_DASHBOARDS_object " +
            " where (bo_type = '0' and bo_ob_id in (select br_user_group from bi_user_rel_groups where br_user_id= #{userId}))" +
            " or (BO_TYPE='1' and bo_ob_id = #{userId} )) or create_id = #{userId}) and id = #{id}")
    Dashboards getDashboards(@Param("userId") int userId, @Param("id") int id);

    /*
    看板移交
     */
    @Update("update BI_DASHBOARDS set create_by = #{createBy}, create_id = #{createId} where id = #{id}")
    void changeDashOrder(@Param("createBy") String createBy, @Param("createId") int createId, @Param("id") int id);

    /*
    查看看板分发对象类型
     */
    @Select("select bo_type from bi_dashboards_object where bo_da_id = #{id}")
    List<String> getType(int id);

    /*
    查看看板分发用户
     */
    @Select("select bo_ob_id as id, BG_NAME as name from BI_USER_GROUPS left join BI_DASHBOARDS_OBJECT on bo_ob_id = bg_id where bo_type = '0' and bo_da_id = #{id}")
    List<DashOrder> getOrderGroupName(int id);

    /*
    查看看板分发用户
     */
    @Select("select bo_ob_id as id, BU_NAME as name from bi_users left join BI_DASHBOARDS_OBJECT on bo_ob_id = bu_id where bo_type = '1' and bo_da_id = #{id}")
    List<DashOrder> getOrderName(int id);

    /*
    添加看板对象
     */
    @Insert("insert into bi_dashboards_object(bo_id, bo_da_id, bo_type, bo_ob_id) values" +
            "(#{id}, #{daId}, #{objectType}, #{obId})")
    @SelectKey(before=true,keyProperty="id",resultType=int.class,statement="SELECT bi_dashboards_object_squence.nextval from dual",keyColumn = "bo_id")
    void addObject(@Param("daId") int daId, @Param("objectType") String objectType, @Param("obId") int obId);

    /*
   删除对象
    */
    @Delete("delete from bi_dashboards_object where bo_da_id = #{daId}")
    void delObject(int daId);

    /*
    获取看板拥有人ID
     */
    @Select("select create_id from bi_dashboards where id = #{id}")
    int getCreateIdById(int id);
}