<?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.huiju.estateagents.property.dao.TaUserVerifyMapper">

    <sql id="Base_Column_List" >
    id, person_id, org_id, role_id, phase_id, phase_name, building_id, building_name,
    unit_id, unit_name, level_id, level_name, room_no_id, room_no_name, verify_status,
    create_date, verify_name, remark
  </sql>

    <select id="selectAllByPhoneAndUserNameAndIdCard" resultType="com.huiju.estateagents.property.model.TaUserVerify" >
        select
            tav.*
        from
          ta_user_verify tav
        <where>
            <trim suffixOverrides="and | or">
                tav.verify_status = 0
                <if test="phone != null and phone != ''">
                    and tav.phone like CONCAT('%',#{phone},'%')
                </if>
                <if test="userName != null and userName != ''">
                    and tav.owner_name like CONCAT('%',#{userName},'%')
                </if>
                <if test="idCard != null and idCard != ''">
                    and tav.id_card like CONCAT('%',#{idCard},'%')
                </if>
                <if test="orgId != null and orgId != ''">
                    and tav.org_id = #{orgId,jdbcType=INTEGER}
                </if>
            </trim>
        </where>
    </select>


    <select id="selectCommunityAndAddress" resultType="com.huiju.estateagents.property.model.TaUserVerify">
        select
        *
        from ta_user_verify
        where org_id = #{orgId,jdbcType=INTEGER}
        and phase_id = #{phaseId,jdbcType=INTEGER}
        and building_id = #{buildingId,jdbcType=INTEGER}
        and unit_id = #{unitId,jdbcType=INTEGER}
        and level_id = #{levelId,jdbcType=INTEGER}
        and room_no_id = #{roomNoId,jdbcType=INTEGER}
        and verify_status = 1
        and role_id = 1
    </select>

    <select id="selectCommunityAndAddressAndRoleId" resultType="com.huiju.estateagents.property.model.TaUserVerify">
        select
        *
        from ta_user_verify
        where org_id = #{orgId,jdbcType=INTEGER}
        and phase_id = #{phaseId,jdbcType=INTEGER}
        and building_id = #{buildingId,jdbcType=INTEGER}
        and unit_id = #{unitId,jdbcType=INTEGER}
        and level_id = #{levelId,jdbcType=INTEGER}
        and room_no_id = #{roomNoId,jdbcType=INTEGER}
        and verify_status = 1
        and person_id = #{userId}
        and role_id = #{roleId}
    </select>

    <select id="selectUserVerifyCommunityAndAddress" resultType="com.huiju.estateagents.property.model.TaUserVerify">
        select
          *
        from ta_user_verify
        where org_id = #{orgId,jdbcType=INTEGER}
        and person_id = #{userId,jdbcType=INTEGER}
        and phase_id = #{phaseId,jdbcType=INTEGER}
        and building_id = #{buildingId,jdbcType=INTEGER}
        and unit_id = #{unitId,jdbcType=INTEGER}
        and level_id = #{levelId,jdbcType=INTEGER}
        and room_no_id = #{roomNoId,jdbcType=INTEGER}
        <if test="verifyStatus != null and verifyStatus != ''">
            and verify_status = #{verifyStatus}
        </if>
    </select>

    <select id="selectWxCommunityAndAddress" resultType="com.huiju.estateagents.property.model.TaUserVerify">
        select
        <include refid="Base_Column_List"/>
        from ta_user_verify
        where org_id = #{orgId,jdbcType=INTEGER}
        and person_id = #{personId,jdbcType=INTEGER}
        and phase_id = #{phaseId,jdbcType=INTEGER}
        and building_id = #{buildingId,jdbcType=INTEGER}
        and unit_id = #{unitId,jdbcType=INTEGER}
        and level_id = #{levelId,jdbcType=INTEGER}
        and room_no_id = #{roomNoId,jdbcType=INTEGER}
        <if test="verifyStatus != null and verifyStatus != ''">
            and verify_status = #{verifyStatus}
        </if>
    </select>
</mapper>
