<?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.dayou.mapper.BrokerageBaseAmountMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.dayou.vo.BaseAmountVO">
        <result column="id" property="id" />
        <result column="deleted" property="deleted" />
        <result column="created" property="created" />
        <result column="modified" property="modified" />
        <result column="user_id" property="userId" />
        <result column="base_amount" property="baseAmount" />
        <result column="name" property="name" />
        <result column="staff_no" property="staffNo"/>
        <collection property="departmentPostVOList" ofType="com.dayou.vo.DepartmentPostVO" select="selectDepartmentPostVO" column="user_id"/>
    </resultMap>

    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        id,
        deleted,
        created,
        modified,
        user_id, base_amount
    </sql>

    <select id="selectDepartmentPostVO" parameterType="java.lang.Long" resultType="com.dayou.vo.DepartmentPostVO">
        SELECT
            d.NAME AS departmentName,
            p.name as postName
        FROM
            user_post up
                left join
            post p
            on up.post_id = p.id
                LEFT JOIN department d ON p.department_id = d.id
        where up.user_id = #{user_id} and up.deleted = 0 and d.deleted=0 and p.deleted=0
    </select>
    <select id="xPage" resultMap="BaseResultMap" parameterType="com.dayou.vo.BaseAmountVO">
        SELECT
            ba.id,
            u.NAME,
            u.staff_no,
            ba.base_amount,
            u.id as user_id
        FROM
            user u
                LEFT JOIN brokerage_base_amount ba ON u.id = ba.user_id
        WHERE
            u.deleted = 0
        <if test="base!=null and base.staffNo!=null and base.staffNo!='' ">
            and u.staff_no like concat('%',#{base.staffNo},'%')
        </if>
        <if test="base!=null and base.name!=null and base.name!='' ">
            and u.name like concat('%',#{base.name},'%')
        </if>
        <if test="base!=null and base.keyword!=null and base.keyword!='' ">
            and u.name like concat('%',#{base.keyword},'%') or u.staff_no like concat('%',#{base.keyword},'%')
        </if>
        ORDER BY
            ba.id DESC
    </select>
</mapper>
