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

import com.usoftchina.bi.core.base.TestPage;
import com.usoftchina.bi.server.model.po.Databases;
import com.usoftchina.bi.server.model.po.MessageLog;
import com.usoftchina.bi.server.model.po.RefreshData;
import com.usoftchina.bi.server.model.po.User;
import com.usoftchina.bi.server.model.vo.configVo.DatabasesInfo;
import com.usoftchina.bi.server.model.vo.dataVo.DashboardCopyInfo;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.mapping.StatementType;
import org.apache.ibatis.type.JdbcType;
import org.springframework.stereotype.Repository;

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

@Mapper
@Repository
public interface UASMapper {

    /**
     * 调用存储过程，从UAS_BI标准数据库同步表和数据过来
     */
    @Select({ "call SP_BI_REFRESHDATA(#{sob,mode=IN,jdbcType=VARCHAR},"
            + "#{address,mode=IN,jdbcType=VARCHAR},"
            + "#{port,mode=IN,jdbcType=VARCHAR},"
            + "#{sid,mode=IN,jdbcType=VARCHAR},"
            + "#{em_id,mode=IN,jdbcType=VARCHAR},"
            + "#{em_code,mode=IN,jdbcType=VARCHAR},"
            + "#{em_name,mode=IN,jdbcType=VARCHAR},"
            + "#{sob_name,mode=IN,jdbcType=VARCHAR},"
            + "#{res,mode=OUT,jdbcType=VARCHAR})" })
    @Options(statementType=StatementType.CALLABLE)
    String sp_refreshdata(RefreshData refreshData);

    /**
     * 校验账套的数据源是否存在
     */
    @Select({ "SELECT COUNT(*) FROM BI_DATABASES where USER_NAME = #{from}" })
    int checkDataBase(String from);

    /**
     * 获取数链接
     */
    @Select({ "SELECT * BI_DATABASES where id = #{id}" })
    Databases getDataBase(String id);

    //插入数据源
    @Insert("INSERT INTO BI_DATABASES(ID,BASES_NAME,ADDRASS,PORT,DATABASE_TYPE,DATA_NAME,USER_NAME,PASS_WORD,CREATE_DATE,UPDATE_DATE,NOTE) "
            +       "SELECT "
            +           "#{id, jdbcType=INTEGER},#{name},ADDRASS,PORT,DATABASE_TYPE,DATA_NAME,#{sob},PASS_WORD,CREATE_DATE,UPDATE_DATE,NOTE "
            +       "FROM BI_DATABASES where id = #{mainid}")
    @SelectKey(before=true,keyProperty="id",resultType=int.class,statement="SELECT BI_DATABASES_sequence.nextval from dual",keyColumn = "id")
    void insertDataBase(@Param("name") String name ,@Param("sob") String sob ,@Param("mainid") int mainid);

    //获取所有单个数据链接的报表 且编号符合  xxxx_xxxx_账套str
    @Select({ "SELECT DASHBOARD_ID dashboardId, " +
            "  ID dataSourceId, " +
            "  '_副本' lastName, " +
            "  substr(BD_CODE,0,instr(BD_CODE, '_', 1, 2))||#{to} newCode " +
            "FROM " +
            "  (SELECT MAX(BD_CODE) BD_CODE, " +
            "    DASHBOARD_ID, " +
            "    COUNT(*), " +
            "    MAX(id) id, " +
            "    MAX(USER_NAME) USER_NAME " +
            "  FROM (SELECT DISTINCT BD_CODE, " +
            "    DASHBOARD_ID, " +
            "    BI_DATABASES.id, " +
            "    BI_DATABASES.USER_NAME " +
            "  FROM BI_DATABASES " +
            "  LEFT JOIN BI_DATA_CONNECTORS " +
            "  ON DB_CONFIG = BI_DATABASES.id " +
            "  LEFT JOIN BI_CHARTS " +
            "  ON BI_CHARTS.BD_DATA_ID = BI_DATA_CONNECTORS.ID " +
            "  LEFT JOIN BI_DASHBOARDS_CHART " +
            "  ON BI_DASHBOARDS_CHART.CHART_ID = BI_CHARTS.id " +
            "  LEFT JOIN BI_DASHBOARDS " +
            "  ON BI_DASHBOARDS.ID = BI_DASHBOARDS_CHART.DASHBOARD_ID) " +
            "  GROUP BY DASHBOARD_ID " +
            "  HAVING COUNT(*)=1 " +
            "  ) x " +
            "WHERE X.USER_NAME = #{from} " +
            "AND REGEXP_LIKE (NVL(BD_CODE,' '), '([[:alnum:]]+_){2}')" })
    List<DashboardCopyInfo> getFromSobDashboard(@Param("from") String from,@Param("to") String to);

    /**
     * 查询数据链接
     * @return
     */
    @Select("SELECT id,BASES_NAME name,addrass,port,DATABASE_TYPE databaseType,DATA_NAME dataName,USER_NAME userName,PASS_WORD passWord,note FROM BI_DATABASES where id = #{id}")
    DatabasesInfo getDatabasesInfo(@Param("id") String id);

    /**
     * 调用存储过程，刷新数据链接后缀
     */
    @Select({ "call SP_BI_SYNC(#{from,mode=IN,jdbcType=VARCHAR},"
            + "#{res,mode=OUT,jdbcType=VARCHAR})" })
    @Options(statementType=StatementType.CALLABLE)
    String sp_sync(@Param("from") String from,@Param("res") String res);

    /**
     * 调用存储过程，删除目标账套所有报表配置（除数据链接）
     */
    @Select({ "call SP_BI_CLEAN_SOB(#{from,mode=IN,jdbcType=VARCHAR},"
            + "#{res,mode=OUT,jdbcType=VARCHAR})" })
    @Options(statementType=StatementType.CALLABLE)
    String sp_clean_sob(@Param("from") String from,@Param("res") String res);
}
