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

import com.usoftchina.bi.server.model.po.User;
import com.usoftchina.bi.server.model.po.UserGroup;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

import java.util.List;

@Mapper
@Repository
public interface UserMapper {

    /**
     * 获取表记录数
     * @return
     */
    @Select("SELECT COUNT(*) FROM bi_users")
    int getCount();

    /**
     * 获取主账套
     * @return
     */
    @Select("SELECT id FROM BI_DATABASES where ismaster = 1 and rownum<2")
    Integer getMasterId();

    /**
     * 获取目标账套数据链接ID
     * @return
     */
    @Select("SELECT id FROM BI_DATABASES where USER_NAME = #{name} and rownum<2")
    String getMasterIdByName(@Param("name") String name);

    /**
     * 查询UAS的人员资料表数据
     * @return
     */
    @Select("SELECT EM_ID as id,EM_DEFAULTHSNAME as post, EM_TYPE role, EM_DEPART as department, EM_NAME as name, EM_PASSWORD as passWord, "
            + "EM_MOBILE as phone, EM_CODE as userName,EM_CLASS AS state FROM EMPLOYEE")
    List<User> getUASEmployee();

    /**
     * 批量插入人员资料
     * @param userList
     */
    @Insert("<script>"
            + "INSERT INTO BI_USERS(BU_ID, BU_NAME, BU_POST,BU_ROLE, USER_NAME, PASS_WORD, BU_DEPARTMENT, CREATE_DATE,BU_PHONE) "
            + "SELECT M.* FROM ("
            +   "<foreach collection=\"list\" item=\"item\" index=\"index\" separator=\"UNION ALL\">"
            +       "SELECT "
            +           "#{item.id, jdbcType=INTEGER},"
            +           "#{item.name, jdbcType=VARCHAR},"
            +           "#{item.post, jdbcType=VARCHAR},"
            +           "case when #{item.role, jdbcType=VARCHAR} = 'admin' then 'superAdmin' else #{item.role, jdbcType=VARCHAR} end,"
            +           "#{item.userName, jdbcType=VARCHAR},"
            +           "#{item.passWord, jdbcType=VARCHAR},"
            +           "#{item.department, jdbcType=VARCHAR},"
            +           "SYSDATE, "
            +           "#{item.phone, jdbcType=VARCHAR} "
            +       "FROM DUAL"
            +   "</foreach>"
            + ")M"
            + "</script>")
    void batchInsert(@Param("list") List<User> userList);

    /*
    新增一条用户数据
     */
    @Insert("insert into bi_users(bu_id, bu_name, BU_POST, USER_NAME, PASS_WORD, BU_DEPARTMENT, CREATE_DATE,BU_PHONE) values" +
            "(#{id,jdbcType=INTEGER}, #{name,jdbcType=VARCHAR}, #{post,jdbcType=VARCHAR}, #{userName,jdbcType=VARCHAR}, #{passWord,jdbcType=VARCHAR}, #{department,jdbcType=VARCHAR}, to_date(to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),'YYYY-MM-DD hh24:mi:ss'), #{phone, jdbcType=VARCHAR})")
    @SelectKey(before=true,keyProperty="id",resultType=int.class,statement="SELECT bi_users_sequence.nextval from dual",keyColumn = "bu_id")
    void addUser(User user);

    /*
    更新用户数据
     */
    @Update("<script>" +
            "UPDATE bi_users set " +
            " <if test=\"userName != null\"> USER_NAME = #{userName,jdbcType=VARCHAR}, </if>" +
            " <if test=\"passWord != null\">PASS_WORD = #{passWord,jdbcType=VARCHAR}, </if>" +
            " <if test=\"name != null\">bu_name = #{name,jdbcType=VARCHAR}, </if>" +
            " <if test=\"post != null\">BU_POST = #{post,jdbcType=VARCHAR}, </if>" +
            " <if test=\"department != null\">BU_DEPARTMENT = #{department,jdbcType=VARCHAR}, </if>" +
            " <if test=\"role != null\">BU_ROLE = #{role,jdbcType=VARCHAR}, </if>" +
            " <if test=\"phone != null\">BU_PHONE = #{phone,jdbcType=VARCHAR}, </if>" +
            "  updata_date = to_date(to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),'YYYY-MM-DD hh24:mi:ss')" +
            "  where bu_id = #{id,jdbcType=INTEGER}" +
            " </script>")
    void updateUser(User user);

    /*
    查询id
     */
    @Select("select bu_id as id from bi_users where user_name = #{userName,jdbcType=VARCHAR}")
    int getUserId(String userName);

    /*
    查询Role
     */
    @Select("select bu_role as id from bi_users where bu_id = #{id}")
    String getRole(int id);

    /*
    查询uas用户信息
     */
    @Select("SELECT EM_DEFAULTHSNAME as post, EM_DEPART as department,EM_TYPE as role, EM_NAME as name, EM_PASSWORD as passWord," +
            " EM_MOBILE as phone, EM_CODE as userName,EM_CLASS AS state FROM EMPLOYEE where EM_CODE = #{userName}")
    User getUserMessByName(@Param("userName") String userName);

    /*
    查询uas用户信息
     */
    @Select("SELECT EM_DEFAULTHSNAME as post, EM_DEPART as department,EM_TYPE as role, EM_NAME as name, EM_PASSWORD as passWord," +
            " EM_MOBILE as phone, EM_CODE as userName,EM_CLASS AS state FROM EMPLOYEE where EM_MOBILE = #{mobile}")
    User getUserMessByMobile(@Param("mobile") String mobile);

    /*
    获取用户信息
     */
    @Select("SELECT BU_ID as id, BU_NAME as name, BU_DEPARTMENT as department, BU_POST as post, BU_ROLE as role," +
            " USER_NAME as userName, PASS_WORD as passWord, BU_PHONE as phone, BU_CLASS as state " +
            "FROM BI_USERS WHERE (USER_NAME = #{userName} OR BU_PHONE = #{userName}) and rownum = 1")
    User getLogin(String userName);

    /**
     * 通过用户名查询用户信息
     * @param userName
     * @return
     */
    @Select("SELECT BU_ID as id, BU_NAME as name, BU_DEPARTMENT as department, BU_POST as post, BU_ROLE as role," +
            " USER_NAME as userName, PASS_WORD as passWord, BU_PHONE as phone, BU_CLASS as state " +
            "FROM BI_USERS WHERE USER_NAME = #{userName} AND NVL(BU_CLASS,' ') <> '离职'")
    User getBIUserByName(@Param("userName") String userName);

    /**
     * 通过手机号查询用户信息
     * @param mobile
     * @return
     */
    @Select("SELECT BU_ID as id, BU_NAME as name, BU_DEPARTMENT as department, BU_POST as post, BU_ROLE as role," +
            " USER_NAME as userName, PASS_WORD as passWord, BU_PHONE as phone, BU_CLASS as state " +
            "FROM BI_USERS WHERE BU_PHONE = #{mobile} AND NVL(BU_CLASS,' ') <> '离职'")
    User getBIUserByMobile(@Param("mobile") String mobile);

    /*
    查询bi系统是否存在该用户
     */
    @Select("select bu_name as name from bi_users where user_name = #{userName}")
    String getUserName(String userName);

    /*
    通过ID获取名称
     */
    @Select("select bu_name from bi_users where bu_id = #{id}")
    String getName(int id);

    /*
    获取用户列表
     */
    @Select("select BU_ID as id, BU_NAME as name, BU_DEPARTMENT as department, BU_POST as post, BU_ROLE as role," +
            "user_name as userName, pass_word as passWord, create_date as createDate " +
            "from bi_users WHERE NVL(BU_CLASS,' ') <> '离职' AND BU_ROLE <> 'superAdmin'")
    List<User> getUserList();

    @Select("select BU_ID as id, BU_NAME as name, BU_DEPARTMENT as department, BU_POST as post, BU_ROLE as role," +
            "user_name as userName, pass_word as passWord, create_date as createDate " +
            "from bi_users where bu_id = #{id}")
    User getUserById(@Param("id") int id);

    /*
    匹配用户20个
     */
    @Select("select BU_ID as id, BU_NAME as name, BU_DEPARTMENT as department, BU_POST as post, BU_ROLE as role " +
            "from bi_users where bu_name ${condition} AND NVL(BU_CLASS,' ') <> '离职' AND BU_ROLE <> 'superAdmin' and rownum < #{count, jdbcType=INTEGER}")
    List<User> getNameList(@Param("condition") String condition, @Param("count") Long count);

    /*
    更新token
     */
    @Update("update bi_users set user_token = #{token} where bu_id = #{id}")
    void updateToken(@Param("token") String token, @Param("id") int id);

    /**
     * 查询名称是否重复
     * @param name
     * @return
     */
    @Select("SELECT COUNT(*) FROM BI_USER_GROUPS WHERE BG_NAME = #{name,jdbcType=VARCHAR}")
    int getGroupCountByGroupName(@Param("name")String name);

    /*
    创建用户组
     */
    @Insert("insert into bi_user_groups(bg_id, bg_name, bg_note, create_date) values(#{id}, #{userGroupName}, #{userGroupNote}, " +
            "to_date(to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),'YYYY-MM-DD hh24:mi:ss'))")
    @SelectKey(before=true,keyProperty="id",resultType=Integer.class,statement="SELECT BI_USER_GROUPS_SEQUENCE.nextval from dual",keyColumn = "bg_id")
    void insUserGroup(UserGroup userGroup);

    /*
    更新用户组
     */
    @Update("<script>" +
            "UPDATE bi_user_groups set "  +
            "bg_name = #{userGroupName}" +
            "<if test=\"userGroupNote != null\"> , bg_note = #{userGroupNote} </if>" +
            ",UPDATA_DATE = to_date(to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),'YYYY-MM-DD hh24:mi:ss') " +
            "where bg_id = #{id}" +
            "</script>")
    void updateUserGroup(UserGroup userGroup);

    /*
    查询用户组列表
     */
    @Select("select bg_id as id, bg_name as userGroupName, bg_note as userGroupNote, create_date as createDate, " +
            "updata_date as updateDate from bi_user_groups ")
    List<UserGroup> getUserGroupList();

    /*
   查询用户组
    */
    @Select("select bg_id as id, bg_name as userGroupName, bg_note as userGroupNote, create_date as createDate, " +
            "updata_date as updateDate from bi_user_groups where bg_id = #{id}")
    UserGroup getUserGroup(int id);

    /*
    删除用户组
     */
    @Delete("delete from bi_user_groups where bg_id = #{id}")
    void delUserGroup(Long id);

    /**
     * 删除用户组所拥有的权限
     * @param id
     */
    @Delete("DELETE FROM BI_POWER_USERS WHERE BPU_USERGROUPID = #{id}")
    void deletePowerByGroupId(Long id);

    /*
    添加用户到用户组
     */
    @Insert("insert into bi_user_rel_groups(br_id, br_user_id, br_user_group) values(#{id}, #{userId}, #{userGroupId})")
    @SelectKey(before=true,keyProperty="id",resultType=Integer.class,statement="SELECT BI_USER_REL_GROUPS_SEQUENCE.nextval from dual",keyColumn = "br_id")
//    void setUserInto(@Param("userId") int userId, @Param("userGroupId") int userGroupId);
    void setUserInto(@Param("userId") int userId, @Param("userGroupId") int userGroupId);

    /*
    查询用户组是否有该用户
     */
    @Select("select count(*) from bi_user_rel_groups where br_user_id = #{userId} and br_user_group = #{userGroupId} ")
    int getUserCountInGroup(@Param("userId") int userId, @Param("userGroupId") int userGroupId);

    /**
     * 获取用户名
     * @param ids
     * @return
     */
    @Select("SELECT WM_CONCAT(BU_NAME) FROM BI_USERS WHERE BU_ID IN (${ids})")
    String getUserByIds(@Param("ids") String ids);

    /*
    查询用户组下的用户信息
     */
    @Select("select bu_id as id, bu_name as name, bu_department as department, bu_post as post, bu_role as role " +
            " from bi_users,bi_user_groups,bi_user_rel_groups where bi_users.bu_id=bi_user_rel_groups.br_user_id and " +
            " bi_user_rel_groups.br_user_group=bi_user_groups.bg_id and bi_user_groups.bg_id = #{id} AND NVL(BU_CLASS,' ') <> '离职'")
    List<User> getGroupUserList(int id);

    /*
    删除用户组下的用户
     */
    @Delete("delete from bi_user_rel_groups where br_user_id = #{userId} and br_user_group = #{userGroupId} ")
    void delUserInGroup(@Param("userId") int userId, @Param("userGroupId") int userGroupId);

}
