<?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.dcsoft.system.mapper.SysPeopleOtherMapper">

    <resultMap type="SysPeopleOther" id="SysPeopleOtherResult">
        <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="guid"    column="guid"    />
        <result property="faceGuid"    column="face_guid"    />
        <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="down"    column="down"    />
        <result property="branchName"    column="branchName"    />
        <result property="delFlag"    column="del_flag"    />
        <result property="validTimeStart"    column="valid_time_start"    />
        <result property="validTimeEnd"    column="valid_time_end"    />
        <result property="file1"    column="file1"    />
        <result property="file2"    column="file2"    />
        <result property="file3"    column="file3"    />

        <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"   />
        <result property="ruleId"  column="rule_id"   />
    </resultMap>

    <sql id="selectSysPeopleOtherVo">
        select p.id, p.name, p.phone, p.sex, p.avatar, p.branch_id, p.position, p.idcard, p.car_no, p.guid, p.face_guid,p.valid_time_start,p.valid_time_end,
                p.remark, p.create_by, p.create_time, p.update_by, p.update_time ,b.name branchName,p.down,p.del_flag,b.rule_id,p.file1,p.file2,p.file3
        from sys_people_other p
        left join sys_branch b on p.branch_id=b.id
        left join sys_dept d on b.dept_id = d.dept_id
    </sql>

    <select id="selectSysPeopleOtherList" parameterType="SysPeopleOther" resultMap="SysPeopleOtherResult">
        <include refid="selectSysPeopleOtherVo"/>
        <where>
            <if test="sysPeopleOther.id != null  and sysPeopleOther.id != ''"> and p.id = #{sysPeopleOther.id}</if>
            <if test="sysPeopleOther.delFlag != null  and sysPeopleOther.delFlag != ''"> and p.del_flag = #{sysPeopleOther.delFlag}</if>
            <if test="sysPeopleOther.name != null  and sysPeopleOther.name != ''"> and p.name like concat('%', #{sysPeopleOther.name}, '%')</if>
            <if test="sysPeopleOther.phone != null  and sysPeopleOther.phone != ''"> and p.phone like concat('%', #{sysPeopleOther.phone}, '%')</if>
            <if test="sysPeopleOther.sex != null  and sysPeopleOther.sex != ''"> and p.sex = #{sysPeopleOther.sex}</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="list != null and list.size() > 0">
                and p.branch_id in
                <foreach collection="list" item="item" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
            <if test="sysPeopleOther.downFlag != null  and sysPeopleOther.downFlag != ''">
                <if test="sysPeopleOther.downFlag=='yxf'">
                    and p.down >0
                </if>
                <if test="sysPeopleOther.downFlag=='wxf'">
                    and p.down =0
                </if>
            </if>
            <if test="sysPeopleOther.guidFlag != null  and sysPeopleOther.guidFlag != ''">
                <if test="sysPeopleOther.guidFlag=='yzc'">
                    and p.guid is not null
                </if>
                <if test="sysPeopleOther.guidFlag=='wzc'">
                    and p.guid is  null
                </if>

             </if>
            <if test="sysPeopleOther.faceFlag != null  and sysPeopleOther.faceFlag != ''">
                <if test="sysPeopleOther.faceFlag=='yzc'">
                    and p.face_guid is not null
                </if>
                <if test="sysPeopleOther.faceFlag=='wzc'">
                    and p.face_guid is  null
                </if>

            </if>
            <if test="sysPeopleOther.cardFlag != null  and sysPeopleOther.cardFlag != ''">
                <if test="sysPeopleOther.cardFlag=='yzc'">
                    and p.door_no is not null
                </if>
                <if test="sysPeopleOther.cardFlag=='wzc'">
                    and p.door_no is  null
                </if>

            </if>
            <if test="sysPeopleOther.delFlag != null"> and p.del_flag = #{sysPeopleOther.delFlag}</if>
            <if test="sysPeopleOther.branchParent != null  and sysPeopleOther.branchParent != ''"> and b.id  like concat(#{sysPeopleOther.branchParent}, '%')</if>

            <!-- 数据范围过滤 -->
        </where>
    </select>

    <select id="selectSysPeopleOtherById" parameterType="Long" resultMap="SysPeopleOtherResult">
        <include refid="selectSysPeopleOtherVo"/>
        where p.id = #{id}
    </select>

    <insert id="insertSysPeopleOther" parameterType="SysPeopleOther" useGeneratedKeys="true" keyProperty="id">
        insert into sys_people_other
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <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="guid != null">guid,</if>
            <if test="faceGuid != null">face_guid,</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="down != null">down,</if>
            <if test="delFlag != null">del_flag,</if>
            <if test="validTimeStart != null">valid_time_start,</if>
            <if test="validTimeEnd != null">valid_time_end,</if>
            <if test="file1 != null">file1,</if>
            <if test="file2 != null">file2,</if>
            <if test="file3 != null">file3,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <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="guid != null">#{guid},</if>
            <if test="faceGuid != null">#{faceGuid},</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="down != null">#{down},</if>
            <if test="delFlag != null">#{delFlag},</if>
            <if test="validTimeStart != null">#{validTimeStart},</if>
            <if test="validTimeEnd != null">#{validTimeEnd},</if>
            <if test="file1 != null">#{file1},</if>
            <if test="file2 != null">#{file2},</if>
            <if test="file3 != null">#{file3},</if>
        </trim>
    </insert>

    <update id="updateSysPeopleOther" parameterType="SysPeopleOther">
        update sys_people_other
        <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="guid != null">guid = #{guid},</if>
            <if test="faceGuid != null">face_guid = #{faceGuid},</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="down != null">down = #{down},</if>
            <if test="delFlag != null">del_flag = #{delFlag},</if>
            <if test="validTimeStart != null">valid_time_start = #{validTimeStart},</if>
            <if test="validTimeEnd != null">valid_time_end = #{validTimeEnd},</if>
            <if test="file1 != null">file1 = #{file1},</if>
            <if test="file2 != null">file2 = #{file2},</if>
            <if test="file3 != null">file3 = #{file3},</if>
        </trim>
        where id = #{id}
    </update>

    <update id="deleteSysPeopleOtherById" parameterType="Long">
        update  sys_people_other set del_flag='2'  where id = #{id}
    </update>

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

    <update id="deleteSysPeopleOtherByGuid" parameterType="String">
        update  sys_people_other set del_flag='2'  where guid = #{guid}
    </update>
    <update id="updatePeople">
        update sys_people_other set del_flag='2', update_time = now()  where gh = #{gh}
    </update>
    <update id="updatePeopleInfo">
        update sys_people_other set openid= #{openid} where phone = #{phone} and name = #{name}
    </update>

    <select id="selectByGuid" parameterType="SysPeopleOther" resultMap="SysPeopleOtherResult">
        <include refid="selectSysPeopleOtherVo"/>
        where p.guid = #{guid}
    </select>

    <select id="selectSysPeopleOtherByUserId" parameterType="Long" resultMap="SysPeopleOtherResult">
        <include refid="selectSysPeopleOtherVo"/>
        where p.id = #{userId}
    </select>


    <select id="selectSysPeopleOtherByBranchUser" parameterType="String" resultMap="SysPeopleOtherResult">
        <include refid="selectSysPeopleOtherVo"/>
        where p.name = #{userName} and b.name = #{branchName} and  p.del_flag = '0'
    </select>

    <select id="selectSysPeopleOtherByGh" parameterType="String" resultMap="SysPeopleOtherResult">
        <include refid="selectSysPeopleOtherVo"/>
        where p.gh = #{gh} and  p.del_flag = '0'
    </select>

    <select id="selectSysPeopleOtherByPhoneUser" parameterType="SysPeopleOther" resultMap="SysPeopleOtherResult">
        <include refid="selectSysPeopleOtherVo"/>
        where p.name = #{name} and
        p.phone = #{phone} and  p.del_flag = '0'
    </select>
    <select id="queryPeopleInfo" resultType="com.dcsoft.system.domain.SysPeopleOther">
        select id from sys_people_other where del_flag = '0' and down = '0' limit 5
    </select>
    <select id="queryPeople" resultType="com.dcsoft.system.domain.SysPeopleOther">
        select id, gh
        from sys_people_other
        where del_flag = '0'
        <if test="list != null and list.size() > 0">
            and gh in
            <foreach collection="list" item="item" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
    </select>
    <select id="queryPeopleDetails" resultType="com.dcsoft.system.domain.SysPeopleOther">
        select name from sys_people_other where del_flag = '0' and gh = #{gh}
    </select>

    <select id="selectSysPeopleOtherByUserName" parameterType="String" resultMap="SysPeopleOtherResult">
        <include refid="selectSysPeopleOtherVo"/>
        where p.name = #{userName}
        limit 1
    </select>
    <select id="selectDoorNo" resultType="java.lang.String">
        select sp.door_no doorNo
        from sys_empower_record ser
                 left join sys_people_other sp on ser.people_id = sp.id
        where sp.del_flag = '0'
          and ser.id = #{id}
        limit 1
    </select>
    <select id="selectPeopleById" resultType="java.lang.String">
        select id from sys_people_other where del_flag = '0' and door_no = #{card} limit 1
    </select>
    <select id="queryPeopleById" resultMap="SysPeopleOtherResult">
        <include refid="selectSysPeopleOtherVo"/>
        where p.id = #{id}
    </select>
    <select id="selectSysPeopleOtherByOpenId" parameterType="String" resultMap="SysPeopleOtherResult">
        <include refid="selectSysPeopleOtherVo"/>
        where p.openid = #{openid} limit 1
    </select>
    <select id="selectSysPeopleOtherGh" parameterType="String" resultMap="SysPeopleOtherResult">
        <include refid="selectSysPeopleOtherVo"/>
        where p.phone = #{phone} and p.name = #{userName} limit 1
    </select>
    <select id="queryPeopleName" resultType="com.dcsoft.system.domain.SysPeopleOther">
        select phone, name, id, branch_id branchId from sys_people_other where del_flag = '0' and phone = #{phone}
    </select>
</mapper>
