<?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.agri.system.mapper.SysUserAgriMapper">
    
    <resultMap type="SysUserAgri" id="SysUserAgriResult">
        <result property="id"    column="id"    />
        <result property="agriId"    column="agri_id"    />
        <result property="userId"    column="user_id"    />
        <result property="role"    column="role"    />
        <result property="permMask"    column="perm_mask"    />
        <result property="status"    column="status"    />
        <result property="status"    column="invite_status"    />
        <result property="inviteBy"    column="invite_by"    />
        <result property="inviteTime"    column="invite_time"    />
        <result property="acceptTime"    column="accept_time"    />
        <result property="remark"    column="remark"    />
        <result property="createTime"    column="create_time"    />
        <result property="createBy"    column="create_by"    />
        <result property="updateTime"    column="update_time"    />
        <result property="updateBy"    column="update_by"    />
        <result property="version"    column="version"    />
        <result property="disabled"    column="disabled"    />
        <association  property="sysUser"  resultMap="SysUserResult" />
    </resultMap>

    <resultMap type="SysUser" id="SysUserResult">
        <id     property="userId"        column="user_id"         />
        <result property="userName"      column="user_name"       />
        <result property="nickName"      column="nick_name"       />
        <result property="email"         column="email"           />
        <result property="phonenumber"   column="phonenumber"     />
        <result property="sex"           column="sex"             />
        <result property="avatar"        column="avatar"          />
        <result property="status"        column="status"          />
        <result property="delFlag"       column="del_flag"        />
    </resultMap>
    <sql id="selectSysUserAgriVo">
        SELECT
            id,
            agri_id,
            user_id,
            role,
            perm_mask,
            STATUS,
            invite_by,
            invite_time,
            accept_time,
            remark,
            create_time,
            create_by,
            update_time,
            update_by,
            version
        FROM
            sys_user_agri
    </sql>
    <sql id="userVo">
    </sql>

    <select id="selectSysUserAgriList" parameterType="SysUserAgri" resultMap="SysUserAgriResult">
        <include refid="selectSysUserAgriVo"/>
        <where>  
            <if test="agriId != null "> and agri_id = #{agriId}</if>
            <if test="userId != null "> and user_id = #{userId}</if>
            <if test="role != null "> and role = #{role}</if>
            <if test="permMask != null "> and perm_mask = #{permMask}</if>
            <if test="status != null "> and status = #{status}</if>
            <if test="inviteBy != null "> and invite_by = #{inviteBy}</if>
            <if test="inviteTime != null "> and invite_time = #{inviteTime}</if>
            <if test="acceptTime != null "> and accept_time = #{acceptTime}</if>
            <if test="version != null "> and version = #{version}</if>
        </where>
    </select>
    
    <select id="selectSysUserAgriById" parameterType="Long" resultMap="SysUserAgriResult">
        <include refid="selectSysUserAgriVo"/>
        where id = #{id}
    </select>

    <insert id="insertSysUserAgri" parameterType="SysUserAgri">
        insert into sys_user_agri
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="id != null">id,</if>
            <if test="agriId != null">agri_id,</if>
            <if test="userId != null">user_id,</if>
            <if test="role != null">role,</if>
            <if test="permMask != null">perm_mask,</if>
            <if test="status != null">status,</if>
            <if test="inviteBy != null">invite_by,</if>
            <if test="inviteTime != null">invite_time,</if>
            <if test="acceptTime != null">accept_time,</if>
            <if test="remark != null">remark,</if>
    create_time,
    create_by,
    update_time,
    update_by,
            <if test="version != null">version,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="id != null">#{id},</if>
            <if test="agriId != null">#{agriId},</if>
            <if test="userId != null">#{userId},</if>
            <if test="role != null">#{role},</if>
            <if test="permMask != null">#{permMask},</if>
            <if test="status != null">#{status},</if>
            <if test="inviteBy != null">#{inviteBy},</if>
            <if test="inviteTime != null">#{inviteTime},</if>
            <if test="acceptTime != null">#{acceptTime},</if>
            <if test="remark != null">#{remark},</if>
        #{createTime},
        #{createBy},
        #{updateTime},
        #{updateBy},
            <if test="version != null">#{version},</if>
         </trim>
    </insert>

    <update id="updateSysUserAgri" parameterType="SysUserAgri">
        update sys_user_agri
        <trim prefix="SET" suffixOverrides=",">
            <if test="agriId != null">agri_id = #{agriId},</if>
            <if test="userId != null">user_id = #{userId},</if>
            <if test="role != null">role = #{role},</if>
            <if test="permMask != null">perm_mask = #{permMask},</if>
            <if test="status != null">status = #{status},</if>
            <if test="inviteBy != null">invite_by = #{inviteBy},</if>
            <if test="inviteTime != null">invite_time = #{inviteTime},</if>
            <if test="acceptTime != null">accept_time = #{acceptTime},</if>
            <if test="remark != null">remark = #{remark},</if>
            <if test="createTime != null">create_time = #{createTime},</if>
            <if test="createBy != null">create_by = #{createBy},</if>
            <if test="updateTime != null">update_time = #{updateTime},</if>
            <if test="updateBy != null">update_by = #{updateBy},</if>
            <if test="version != null">version = #{version},</if>
        </trim>
        where id = #{id}
    </update>

    <delete id="deleteSysUserAgriById" parameterType="Long">
        delete from sys_user_agri where id = #{id}
    </delete>

    <delete id="deleteSysUserAgriByIds" parameterType="String">
        delete from sys_user_agri where id in 
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>

    <sql id="agriUserVo">
        SELECT
        user_agri.id,
        user_agri.agri_id,
        user_agri.role,
        user_agri.perm_mask,
        user_agri.STATUS,
        user_agri.invite_by,
        user_agri.invite_time,
        user_agri.accept_time,
        user_agri.remark,
        user_agri.create_time,
        user_agri.create_by,
        user_agri.update_time,
        user_agri.update_by,
        user_agri.version,
        `u`.user_id,
        `u`.user_name,
        `u`.nick_name,
        `u`.email,
        `u`.phonenumber,
        `u`.sex,
        `u`.status,
        `u`.del_flag
    </sql>
    <select id="findAgriUser"  parameterType="SysUserAgri" resultMap="SysUserAgriResult">
        <include refid="agriUserVo"/>
        FROM
            sys_user_agri user_agri
        LEFT JOIN sys_user `u` ON user_agri.user_id = `u`.user_id and `u`.del_flag=0
        <where>
            <if test="agriId != null "> and user_agri.agri_id = #{agriId}</if>
            <if test="role != null "> and user_agri.role = #{role}</if>
            <if test="permMask != null "> and user_agri.perm_mask = #{permMask}</if>
            <if test="status != null "> and user_agri.status = #{status}</if>
            <if test="inviteBy != null "> and user_agri.invite_by = #{inviteBy}</if>
            <if test="inviteTime != null "> and user_agri.invite_time = #{inviteTime}</if>
            <if test="acceptTime != null "> and user_agri.accept_time = #{acceptTime}</if>
            <if test="version != null "> and user_agri.version = #{version}</if>
             <if test="sysUser != null ">
                 <if test="sysUser.idList != null and sysUser.idList.size() > 0">
                     `u`.user_id IN
                     <foreach collection="idList" item="id" open="(" separator="," close=")">
                         #{id}
                     </foreach>
                 </if>

                 <if test="sysUser.userId != null and sysUser.userId != 0">
                     AND `u`.user_id = #{sysUser.userId}
                 </if>
                 <if test="sysUser.userName != null and sysUser.userName != ''">
                     AND `u`.user_name like concat('%', #{sysUser.userName}, '%')
                 </if>
                 <if test="sysUser.status != null and sysUser.status != ''">
                     AND `u`.status = #{sysUser.status}
                 </if>
                 <if test="sysUser.phonenumber != null and sysUser.phonenumber != ''">
                     AND `u`.phonenumber like concat('%', #{sysUser.phonenumber}, '%')
                 </if>
             </if>
        </where>
    </select>


    <select id="findAllUser"  parameterType="SysUserAgri" resultMap="SysUserAgriResult">
        <include refid="agriUserVo"/>,
        IF(user_agri.agri_id IS NOT NULL, TRUE, FALSE) AS disabled
        FROM
            sys_user `u`
        LEFT JOIN sys_user_agri user_agri  ON user_agri.user_id = `u`.user_id
        <if test="agriId != null "> and user_agri.agri_id = #{agriId}</if>
        <if test="agriId == null "> and user_agri.agri_id is NULL</if>
        <where>
            and `u`.del_flag=0 and `u`.status = 0
            <if test="role != null "> and user_agri.role = #{role}</if>
            <if test="permMask != null "> and user_agri.perm_mask = #{permMask}</if>
            <if test="status != null "> and user_agri.status = #{status}</if>
            <if test="inviteBy != null "> and user_agri.invite_by = #{inviteBy}</if>
            <if test="inviteTime != null "> and user_agri.invite_time = #{inviteTime}</if>
            <if test="acceptTime != null "> and user_agri.accept_time = #{acceptTime}</if>
            <if test="version != null "> and user_agri.version = #{version}</if>
            <if test="sysUser != null ">
                <if test="sysUser.userId != null and sysUser.userId != 0">
                    AND `u`.user_id = #{sysUser.userId}
                </if>
                <if test="sysUser.userName != null and sysUser.userName != ''">
                    AND `u`.user_name like concat('%', #{sysUser.userName}, '%')
                </if>
                <if test="sysUser.status != null and sysUser.status != ''">
                    AND `u`.status = #{sysUser.status}
                </if>
                <if test="sysUser.phonenumber != null and sysUser.phonenumber != ''">
                    AND `u`.phonenumber like concat('%', #{sysUser.phonenumber}, '%')
                </if>
            </if>
        </where>
    </select>
</mapper>