<?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="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="java.lang.Integer">
        select count(*) from real_name_auth where union_id <![CDATA[<>]]> #{unionId} and id_number = #{idNumber} and check_status = 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
    </select>
</mapper>