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

import com.usoftchina.bi.server.model.bo.DashOrder;
import com.usoftchina.bi.server.model.po.Dashboards;
import com.usoftchina.bi.core.base.TestPage;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.type.JdbcType;
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, filters, create_date, BD_THUMBNAIL,relation_columns, create_id, bd_code,bd_menuId)" +
            "values(#{id,jdbcType=INTEGER}, #{bdName,jdbcType=VARCHAR},#{bdNote,jdbcType=VARCHAR},#{bdConfiguration,jdbcType=VARCHAR},#{createBy,jdbcType=VARCHAR}, #{filters,jdbcType=VARCHAR}, " +
            "to_date(to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),'YYYY-MM-DD hh24:mi:ss'), #{thumbnail,jdbcType=VARCHAR},#{relationColumns,jdbcType=VARCHAR}, #{createId,jdbcType=INTEGER}, #{bdCode,jdbcType=VARCHAR}, #{menuId, jdbcType=INTEGER})")
    @SelectKey(before=true,keyProperty="id",resultType=Integer.class,statement="SELECT BI_DASHBOARDS_SEQUENCE.nextval from dual",keyColumn = "id")
    void setDashboards(Dashboards dashboards);

    /**
     * 保存报表-图表关系
     * @param id
     * @param array
     */
    @Insert("<script>"
            + "INSERT INTO BI_DASHBOARDS_CHART(DASHBOARD_ID, CHART_ID) "
            + "<foreach collection=\"array\" item=\"chartId\" open=\"\" close=\"\" separator=\"UNION ALL\">"
            + "SELECT #{id}, #{chartId} FROM DUAL"
            + "</foreach>"
            + "</script>")
    void saveDashboardChartRelation(@Param("id") int id, @Param("array") String[] array);

    /**
     * 删除报表图表关系
     * @param id
     */
    @Delete("DELETE FROM BI_DASHBOARDS_CHART WHERE DASHBOARD_ID = #{id}")
    void deleteDashboardChartRelation(int id);

    /*
    更新
     */
    @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>"+
            "<if test=\"filters != null\">, filters = #{filters} </if>"+
            "<if test=\"bdCode != null\">, BD_CODE = #{bdCode} </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);

    @Select("<script>"
            + "SELECT COUNT(*) FROM BI_DASHBOARDS "
            + "<where>"
            + "<if test=\"id != 0\">"
            + "id != #{id}"
            + "</if>"
            + "<if test=\"code != null\">"
            + " and bd_code = #{code}"
            + "</if>"
            + "</where>"
            + "</script>")
    int countBdCodeById(@Param("code") String code, @Param("id") int id);

    /*
    删除看板
     */
    @Delete("delete from BI_DASHBOARDS where id = #{id}")
    void delDashboards(Integer id);

    /*
    查询看板列表
     */
    @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, filters, demo, bd_code,bd_menuId  " +
            "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}")
    @ResultMap("DashBoardsResultMap")
    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, filters, demo, bd_code,bd_menuId  " +
            "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)) " +
            " or (BO_TYPE='1'))")
    @ResultMap("DashBoardsResultMap")
    List<Dashboards> getListDashboardsWithoutLimit(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, filters, demo, bd_code,bd_menuId " +
            " 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} " +
            "or id in (select bpu_dashboardid from bi_power_users where bpu_userid = #{userId} or bpu_usergroupid in (select br_user_group from bi_user_rel_groups where br_user_id = #{userId})) ) and id = #{id}")
    @Results(id = "DashBoardsResultMap", value = {
            @Result(column = "id", property = "id", jdbcType = JdbcType.INTEGER),
            @Result(column = "bdName", property = "bdName", jdbcType = JdbcType.VARCHAR),
            @Result(column = "bdNote", property = "bdNote", jdbcType = JdbcType.VARCHAR),
            @Result(column = "bdConfiguration", property = "bdConfiguration", jdbcType = JdbcType.VARCHAR),
            @Result(column = "createBy", property = "createBy", jdbcType = JdbcType.VARCHAR),
            @Result(column = "createId", property = "createId", jdbcType = JdbcType.INTEGER),
            @Result(column = "createDate", property = "createDate", jdbcType = JdbcType.VARCHAR),
            @Result(column = "thumbnail", property = "thumbnail", jdbcType = JdbcType.VARCHAR),
            @Result(column = "relationColumns", property = "relationColumns", jdbcType = JdbcType.VARCHAR),
            @Result(column = "filters", property = "filters", jdbcType = JdbcType.VARCHAR),
            @Result(column = "demo", property = "demo", jdbcType = JdbcType.BOOLEAN),
            @Result(column = "id", property = "chartIds", one = @One(select = "getChartIdsById")),
            @Result(column = "bd_code", property = "bdCode", jdbcType = JdbcType.VARCHAR),
            @Result(column = "bd_menuId", property = "menuId", jdbcType = JdbcType.VARCHAR)
    })
    Dashboards getDashboards(@Param("userId") int userId, @Param("id") int id);

    /*
   查询看板
    */
    @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, filters, demo, bd_code,bd_menuId " +
            " from BI_DASHBOARDS where id = #{id}")
    @ResultMap("DashBoardsResultMap")
    Dashboards getDashboardsWithoutLimit(@Param("id") int id);

    @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, filters, demo, bd_code,bd_menuId FROM BI_DASHBOARDS WHERE BD_CODE = #{code}")
    @ResultMap("DashBoardsResultMap")
    Dashboards getDashboardByCode(String code);

    /*
    看板移交
     */
    @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_SEQUENCE.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);

    /**
     * 获取看板下所有的图表ID
     * @param id
     * @return
     */
    @Select("select wm_concat(chart_id) from bi_dashboards_chart where dashboard_id = #{id}")
    String getChartIdsById(int id);
}
