<?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.mmc.iuav.user.dao.RealNameAuthDao">

    <insert id="addRealNameAuth" parameterType="com.mmc.iuav.user.entity.RealNameAuthDO" useGeneratedKeys="true"
            keyProperty="id">
        insert into real_name_auth(user_account_id, union_id, front_id_img, back_id_img, id_number, user_name, sex,
                                   nationality,
                                   birthday, address, issue,
                                   start_date, end_date, is_fake_front, is_fake_back, check_status, remark, create_time)
        values (#{userAccountId}, #{unionId}, #{frontIdImg}, #{backIdImg}, #{idNumber}, #{userName}, #{sex},
                #{nationality},
                #{birthday}, #{address}, #{issue},
                #{startDate}, #{endDate}, #{fakeFront}, #{fakeBack}, #{checkStatus}, #{remark}, NOW())
    </insert>

    <update id="updateRealNameAuth" parameterType="com.mmc.iuav.user.entity.RealNameAuthDO">
        update real_name_auth
        <set>
            <if test=" frontIdImg != null and frontIdImg != '' ">
                front_id_img = #{frontIdImg},
            </if>
            <if test=" backIdImg != null and backIdImg != '' ">
                back_id_img = #{backIdImg},
            </if>
            <if test=" idNumber != null and idNumber != '' ">
                id_number = #{idNumber},
            </if>
            <if test=" userName != null and userName != '' ">
                user_name = #{userName},
            </if>
            <if test=" sex != null ">
                sex = #{sex},
            </if>
            <if test=" nationality != null and nationality != '' ">
                nationality = #{nationality},
            </if>
            <if test=" birthday != null ">
                birthday = #{birthday},
            </if>
            <if test=" address != null and address != '' ">
                address = #{address},
            </if>
            <if test=" issue != null and issue != '' ">
                issue = #{issue},
            </if>
            <if test=" startDate != null ">
                start_date = #{startDate},
            </if>
            <if test=" fakeFront != null ">
                is_fake_front = #{fakeFront},
            </if>
            <if test=" fakeBack != null ">
                is_fake_back = #{fakeBack},
            </if>
            <if test=" checkStatus != null ">
                check_status = #{checkStatus},
            </if>
            <if test=" endDate != null ">
                end_date = #{endDate},
            </if>
            <if test=" remark != null and remark != '' ">
                remark = #{remark},
            </if>
            <if test=" unionId != null and unionId != '' ">
                union_id = #{unionId},
            </if>
            <if test=" userAccountId != null and userAccountId != '' ">
                user_account_id = #{userAccountId},
            </if>
            <if test="createTime !=null">
                create_time = NOW(),
            </if>
        </set>
        where id = #{id}
    </update>

    <update id="updateRealNameAuthRemark">
        update real_name_auth
        set remark = #{remark}
        where id = #{id}
    </update>

    <select id="existHasBeenAuth" resultType="com.mmc.iuav.user.entity.RealNameAuthDO">
        select rna.id,
               rna.user_account_id,
               rna.union_id,
               rna.front_id_img,
               rna.back_id_img,
               rna.id_number,
               rna.user_name,
               rna.sex,
               rna.nationality,
               rna.birthday,
               rna.address,
               rna.issue,
               rna.start_date,
               rna.end_date,
               rna.is_fake_front,
               rna.is_fake_back,
               rna.check_status,
               rna.remark,
               rna.create_time
        from real_name_auth rna
        where rna.id_number = #{idNumber}
          and rna.check_status = 1
          and rna.is_deleted = 0
        limit 1
    </select>

    <select id="getRealNameAuthByUnionId" resultType="com.mmc.iuav.user.entity.RealNameAuthDO">
        select rna.id,
               rna.user_account_id,
               rna.union_id,
               rna.front_id_img,
               rna.back_id_img,
               rna.id_number,
               rna.user_name,
               rna.sex,
               rna.nationality,
               rna.birthday,
               rna.address,
               rna.issue,
               rna.start_date,
               rna.end_date,
               rna.is_fake_front,
               rna.is_fake_back,
               rna.check_status,
               rna.remark,
               rna.create_time
        from real_name_auth rna
        where rna.union_id = #{unionId}
          and rna.is_deleted = 0
    </select>

    <select id="getRealNameAuthBakByUnionId" resultType="com.mmc.iuav.user.entity.RealNameAuthDO">
        select rna.id,
               rna.user_account_id,
               rna.union_id,
               rna.front_id_img,
               rna.back_id_img,
               rna.id_number,
               rna.user_name,
               rna.sex,
               rna.nationality,
               rna.birthday,
               rna.address,
               rna.issue,
               rna.start_date,
               rna.end_date,
               rna.is_fake_front,
               rna.is_fake_back,
               rna.check_status,
               rna.remark,
               rna.create_time
        from real_name_auth_bak rna
        where rna.union_id = #{unionId}
          and rna.is_deleted = 0
          and rna.check_status = 1
    </select>

    <select id="getRealNameAuthByUId" resultType="com.mmc.iuav.user.entity.RealNameAuthDO">
        select rna.id, rna.union_id, rna.user_name, rna.id_number, rna.check_status, rna.sex
        from real_name_auth rna
        where rna.user_account_id = #{userAccountId}
          and rna.is_deleted = 0
    </select>

    <select id="getRealNameAuthById" resultType="com.mmc.iuav.user.entity.RealNameAuthDO">
        select rna.id,
               rna.user_account_id,
               rna.union_id,
               rna.front_id_img,
               rna.back_id_img,
               rna.id_number,
               rna.user_name,
               rna.sex,
               rna.nationality,
               rna.birthday,
               rna.address,
               rna.issue,
               rna.start_date,
               rna.end_date,
               rna.is_fake_front,
               rna.is_fake_back,
               rna.check_status,
               rna.remark,
               rna.create_time
        from real_name_auth rna
        where rna.id = #{id}
          and rna.is_deleted = 0
    </select>

    <select id="countListRealNameAuth" resultType="java.lang.Integer">
        select count(*) from user_account ua inner join real_name_auth rna on
        ua.id=rna.user_account_id
        where ua.`disable` = 0 and rna.check_status = 1
        <if test="keyword != null and keyword != '' ">
            and ( ua.id like concat('%',#{keyword},'%')
            or
            rna.user_name like concat('%',#{keyword},'%')
            or ua.phone_num like
            concat('%',#{keyword},'%')
            )
        </if>
    </select>

    <select id="listRealNameAuth" resultType="com.mmc.iuav.user.entity.RealNameAuthDO"
            parameterType="com.mmc.iuav.user.model.qo.RealNameAuthQO">
        select
        rna.id,rna.user_account_id,ua.nick_name,rna.user_name,ua.phone_num,rna.check_status,rna.create_time,rna.remark
        from user_account ua inner join real_name_auth rna on
        ua.id=rna.user_account_id
        where ua.`disable` = 0 and rna.check_status = 1
        <if test="keyword != null and keyword != '' ">
            and ( ua.id like concat('%',#{keyword},'%')
            or
            rna.user_name like concat('%',#{keyword},'%')
            or ua.phone_num like
            concat('%',#{keyword},'%')
            )
        </if>
        order by rna.create_time DESC
        limit #{pageNo},#{pageSize}
    </select>

    <select id="userDetail" resultType="com.mmc.iuav.user.entity.RealNameAuthDO">
        select rna.id,
               rna.user_account_id,
               rna.union_id,
               rna.front_id_img,
               rna.back_id_img,
               rna.id_number,
               rna.user_name,
               rna.sex,
               rna.nationality,
               rna.birthday,
               rna.address,
               rna.issue,
               rna.start_date,
               rna.end_date,
               rna.is_fake_front,
               rna.is_fake_back,
               rna.check_status,
               rna.remark,
               rna.create_time
        from real_name_auth rna
        where rna.user_account_id = #{userAccountId}
          and rna.is_deleted = 0
        limit 1
    </select>
    <select id="getRealNameAuthCount" resultType="java.lang.Integer">
        SELECT count(*)
        FROM user_account ua
                 LEFT JOIN company_member cm ON cm.user_account_id = ua.id
                 LEFT JOIN company_info ci ON ci.id = cm.company_info_id
                 LEFT JOIN real_name_auth rna ON rna.user_account_id = ua.id
        WHERE ua.DISABLE = 0
          AND ua.port_type = 100
          and rna.check_status = 1
    </select>
</mapper>