<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.hssx.pcbms.mapper.UserMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.hssx.pcbms.entity.User">
        <id column="id" property="id" />
        <result column="head_url" property="headUrl" />
        <result column="name" property="name" />
        <result column="phone" property="phone" />
        <result column="dept_id" property="deptId" />
        <result column="is_pass" property="isPass" />
        <result column="indate" property="indate" />
        <result column="password" property="password" />
        <result column="role_name" property="roleName" />
        <result column="email" property="email" />
        <result column="sex" property="sex" />
        <result column="birthday" property="birthday" />
        <result column="office_phone" property="officePhone" />
        <result column="job_title" property="jobTitle" />
        <result column="login_name" property="loginName" />
        <result column="login_password" property="loginPassword" />
        <result column="xing_zhi" property="xingZhi" />
        <result column="key_code" property="keyCode" />
    </resultMap>
    <resultMap id="BaseResultMapVO" type="com.hssx.pcbms.entity.vo.UserVO">
        <id column="id" property="id" />
        <result column="name" property="name" />
        <result column="phone" property="phone" />
        <result column="dept_id" property="deptId" />
        <result column="role_name" property="roleName" />
        <result column="total" property="totalScore"/>
        <association property="score" resultMap="BaseResultMapScore">
        </association>
    </resultMap>
    <resultMap id="BaseResultMapDeptVO" type="com.hssx.pcbms.entity.vo.DepartmentVO">
        <result column="department_guid" property="departmentGuid" />
        <result column="department_name" property="departmentName" />
        <collection property="userVOS" ofType="com.hssx.pcbms.entity.vo.UserVO">
            <result column="id" property="id" />
            <result column="name" property="name" />
            <result column="phone" property="phone" />
            <result column="total" property="totalScore"/>
            <result column="dept_id" property="deptId" />
            <result column="role_name" property="roleName" />
        </collection>
    </resultMap>

    <resultMap id="BaseResultMapScore" type="com.hssx.pcbms.entity.Score">
        <id column="score_id" property="scoreId" />
        <result column="personal_score" property="personalScore" />
        <result column="idea_score" property="ideaScore" />
        <result column="total" property="total" />
        <result column="reason" property="reason" />
        <result column="rater_id" property="raterId" />
        <result column="rater" property="rater" />
        <result column="uid" property="uid" />
        <result column="scoring_year_month" property="scoringYearMonth" />
    </resultMap>

    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        id, head_url, name, phone, dept_id, is_pass, indate, password, role_name, email, sex, birthday, office_phone, job_title, login_name, login_password, xing_zhi, key_code
    </sql>
    <select id="selectDeptUserListByCondition" resultMap="BaseResultMapDeptVO">
        SELECT
            dt.department_guid,dt.department_name,u.id,  u.name, u.phone,
             u.dept_id,sr.rolename role_name,
             (SELECT total FROM score  WHERE uid = u.id AND scoring_year_month = DATE_FORMAT(NOW(),'%Y-%m')) total
        FROM
        department dt
        left join
        USER u
        ON u.dept_id = dt.department_guid
        LEFT JOIN user_role ur
        ON u.id = ur.user_id
        LEFT JOIN sys_role sr
        ON sr.id = ur.role_id
    </select>
    
    <select id="selectOperateUsersListByCondition" resultMap="BaseResultMap">
        select
            u.id,u.head_url,u.name
        from
            USER u
        LEFT JOIN user_role ur
        ON u.id = ur.user_id
        LEFT JOIN sys_role sr
        ON sr.id = ur.role_id
        where sr.id=2
    </select>
    <select id="selectLeaderUsersListByCondition" resultMap="BaseResultMap">
        select
            u.id,u.head_url,u.name
        from
            USER u
        LEFT JOIN user_role ur
        ON u.id = ur.user_id
        LEFT JOIN sys_role sr
        ON sr.id = ur.role_id
        where sr.id=1
    </select>
</mapper>
