<?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="io.renren.dao.SysPeopleMapper">

    <resultMap type="SysPeople" id="SysPeopleResult">
        <result property="id"    column="id"    />
        <result property="name"    column="name"    />
        <result property="phone"    column="phone"    />
        <result property="sex"    column="sex"    />
        <result property="avatar"    column="avatar"    />
        <result property="branchId"    column="branch_id"    />
        <result property="position"    column="position"    />
        <result property="idcard"    column="idcard"    />
        <result property="carNo"    column="car_no"    />
        <result property="doorNo"    column="door_no"    />
        <result property="guid"    column="guid"    />
        <result property="faceGuid"    column="face_guid"    />
        <result property="userId"    column="user_id"    />
        <result property="remark"    column="remark"    />
        <result property="createBy"    column="create_by"    />
        <result property="createTime"    column="create_time"    />
        <result property="updateBy"    column="update_by"    />
        <result property="updateTime"    column="update_time"    />
        <result property="gh"    column="gh"    />
        <result property="joinTime"    column="join_time"    />
        <result property="down"    column="down"    />
        <result property="delFlag"    column="del_flag"    />
        <result property="xfFlag"    column="xf_flag"    />
        <result property="czje"    column="czje"    />
        <result property="btje"    column="btje"    />
        <result property="equipmentId"    column="equipment_id"    />
        <result property="type"    column="type"    />
        <result property="xfje"    column="xfje"    />
        <result property="xfcs"    column="xfcs"    />
        <result property="loginPwd"    column="login_pwd"    />
        <result property="isLock"    column="is_lock"    />

        <association property="branch"    column="branch_id" javaType="SysBranch" resultMap="branchResult" />

    </resultMap>

    <resultMap id="branchResult" type="SysBranch">
        <id     property="id"    column="branch_id"     />
        <result property="name"  column="branchName"   />
    </resultMap>

    <sql id="selectSysPeopleVo">
        select p.id, p.name, p.phone, p.sex, p.avatar, p.branch_id, p.position, p.idcard, p.car_no,p.door_no, p.guid, p.face_guid,
               p.user_id, p.remark, p.create_by, p.create_time, p.update_by, p.update_time ,b.name branchName,p.gh,p.join_time,p.down,p.del_flag,p.xf_flag,p.czje,p.btje,spe.equipment_id,
        p.type, p.xfje, p.xfcs,p.login_pwd,p.is_lock
        from sys_people p left join sys_branch b on p.branch_id=b.id
        left join sys_user u on u.user_id = p.user_id
        left join sys_dept d on u.dept_id = d.dept_id
        left join sys_people_equipment spe on spe.people_id=p.id
        <if test="equipmentId != null  and equipmentId != ''"> and spe.equipment_id = #{equipmentId}</if>
    </sql>

    <select id="selectSysPeopleList" parameterType="SysPeople" resultMap="SysPeopleResult">
        <include refid="selectSysPeopleVo"/>
        <where>
            1=1 and is_consume='1'
            <if test="name != null  and name != ''"> and p.name like concat('%', #{name}, '%')</if>
            <if test="phone != null  and phone != ''"> and p.phone like concat('%', #{phone}, '%')</if>
            <if test="sex != null  and sex != ''"> and p.sex = #{sex}</if>
            <if test="delFlag != null"> and p.del_flag = #{delFlag}</if>
            <if test="xfFlag != null  and xfFlag != ''"> and p.xf_flag = #{xfFlag}</if>
            <if test="branchId != null ">
                AND (p.branch_id = #{branchId} OR p.branch_id IN ( SELECT t.id FROM sys_branch t WHERE find_in_set(#{branchId}, ancestors) ))
            </if>
            <if test="delFlag != null">
                <if test="delFlag == 0">
                    <if test="equipmentId != null  and equipmentId != ''"> and spe.equipment_id is  null </if>
                </if>
                <if test="delFlag == 2">
                    <if test="equipmentId != null  and equipmentId != ''"> and spe.equipment_id is not null </if>
                </if>
            </if>
            <if test="pageNo != null"> LIMIT  #{pageNo}</if>
            <!-- 数据范围过滤 -->
            ${params.dataScope}
        </where>
    </select>

    <select id="selectSysPeopleById" parameterType="Long" resultMap="SysPeopleResult">
        <include refid="selectSysPeopleVo"/>
        where p.id = #{id}
    </select>

    <insert id="insertSysPeople" parameterType="SysPeople">
        insert into sys_people
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="id != null">id,</if>
            <if test="name != null and name != ''">name,</if>
            <if test="phone != null">phone,</if>
            <if test="sex != null">sex,</if>
            <if test="avatar != null">avatar,</if>
            <if test="branchId != null">branch_id,</if>
            <if test="position != null">position,</if>
            <if test="idcard != null">idcard,</if>
            <if test="carNo != null">car_no,</if>
            <if test="doorNo != null">door_no,</if>
            <if test="guid != null">guid,</if>
            <if test="faceGuid != null">face_guid,</if>
            <if test="userId != null">user_id,</if>
            <if test="remark != null">remark,</if>
            <if test="createBy != null">create_by,</if>
            <if test="createTime != null">create_time,</if>
            <if test="updateBy != null">update_by,</if>
            <if test="updateTime != null">update_time,</if>
            <if test="gh != null">gh,</if>
            <if test="joinTime != null">join_time,</if>
            <if test="down != null">down,</if>
            <if test="xfFlag != null">xf_flag,</if>
            <if test="czje != null">czje,</if>
            <if test="btje != null">btje,</if>
            <if test="isConsume != null">is_consume,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="id != null">#{id},</if>
            <if test="name != null and name != ''">#{name},</if>
            <if test="phone != null">#{phone},</if>
            <if test="sex != null">#{sex},</if>
            <if test="avatar != null">#{avatar},</if>
            <if test="branchId != null">#{branchId},</if>
            <if test="position != null">#{position},</if>
            <if test="idcard != null">#{idcard},</if>
            <if test="carNo != null">#{carNo},</if>
            <if test="doorNo != null">#{doorNo},</if>
            <if test="guid != null">#{guid},</if>
            <if test="faceGuid != null">#{faceGuid},</if>
            <if test="userId != null">#{userId},</if>
            <if test="remark != null">#{remark},</if>
            <if test="createBy != null">#{createBy},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="updateBy != null">#{updateBy},</if>
            <if test="updateTime != null">#{updateTime},</if>
            <if test="gh != null">#{gh},</if>
            <if test="joinTime != null">#{joinTime},</if>
            <if test="down != null">#{down},</if>
            <if test="xfFlag != null">#{xfFlag},</if>
            <if test="czje != null">Round(#{czje},2),</if>
            <if test="btje != null">Round(#{btje},2),</if>
            <if test="isConsume != null">#{isConsume},</if>
        </trim>
    </insert>

    <update id="updateSysPeople" parameterType="SysPeople">
        update sys_people
        <trim prefix="SET" suffixOverrides=",">
            <if test="name != null and name != ''">name = #{name},</if>
            <if test="phone != null">phone = #{phone},</if>
            <if test="sex != null">sex = #{sex},</if>
            <if test="avatar != null">avatar = #{avatar},</if>
            <if test="branchId != null">branch_id = #{branchId},</if>
            <if test="position != null">position = #{position},</if>
            <if test="idcard != null">idcard = #{idcard},</if>
            <if test="carNo != null">car_no = #{carNo},</if>
            <if test="doorNo != null">door_no = #{doorNo},</if>
            <if test="guid != null">guid = #{guid},</if>
            <if test="faceGuid != null">face_guid = #{faceGuid},</if>
            <if test="userId != null">user_id = #{userId},</if>
            <if test="remark != null">remark = #{remark},</if>
            <if test="createBy != null">create_by = #{createBy},</if>
            <if test="createTime != null">create_time = #{createTime},</if>
            <if test="updateBy != null">update_by = #{updateBy},</if>
            <if test="updateTime != null">update_time = #{updateTime},</if>
            <if test="gh != null">gh = #{gh},</if>
            <if test="joinTime != null">join_time = #{joinTime},</if>
            <if test="down != null">down = #{down},</if>
            <if test="czje != null">czje = Round(#{czje},2),</if>
            <if test="btje != null">btje = Round(#{btje},2),</if>
            <if test="xfFlag != null">xf_flag = #{xfFlag},</if>
            <if test="loginPwd != null">login_pwd = #{loginPwd},</if>
            <if test="isLock != null">is_lock = #{isLock},</if>
        </trim>
        where id = #{id}
    </update>

    <update id="deleteSysPeopleById" parameterType="Long">
        update  sys_people set del_flag=2 where id = #{id}
    </update>

    <update id="deleteSysPeopleByIds" parameterType="String">
        update sys_people set del_flag=2 where id in
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </update>

    <select id="selectByGuid" parameterType="SysPeople" resultMap="SysPeopleResult">
        <include refid="selectSysPeopleVo"/>
        where p.guid = #{guid}
    </select>

    <select id="selectSysPeopleByUserId" parameterType="Long" resultMap="SysPeopleResult">
        <include refid="selectSysPeopleVo"/>
        where p.user_id = #{userId}
    </select>


    <select id="selectSysPeopleByBranchUser" parameterType="String" resultMap="SysPeopleResult">
        <include refid="selectSysPeopleVo"/>
        where p.name = #{userName} and b.name = #{branchName}
    </select>

    <select id="selectSysPeopleByOpenId" parameterType="String" resultMap="SysPeopleResult">
        <include refid="selectSysPeopleVo"/>
        where p.openid = #{openid}
    </select>

    <select id="selectSysPeopleByIdPwd" parameterType="Long" resultMap="SysPeopleResult">
        <include refid="selectSysPeopleVo"/>
        where p.gh = #{gh} and p.login_pwd = #{loginPwd}
    </select>


</mapper>
