<?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.yunzhi.marketing.mapper.TaDocumentVerifyMapper">
    <select id="getDocumentVerifyList" resultType="com.yunzhi.marketing.entity.TaDocumentVerify">
    SELECT
	a.* ,
	IFNULL(b.name,b.nickname) as name,
    IFNULL(b.tel,b.phone) as tel
    FROM
	ta_document_verify a
	LEFT JOIN ta_person b ON a.person_id = b.person_id
	where a.status = 1
	and a.org_id = #{orgId}
	and a.person_id = #{personId}
        order by a.verify_status asc,a.create_date desc
    </select>

    <select id="getAdminDocumentList" resultType="com.yunzhi.marketing.entity.TaDocumentVerifyModel">
        SELECT
	t.*
FROM
	(
SELECT
	IFNULL( b.NAME, b.nickname ) AS name,
	IFNULL( b.tel, b.phone ) AS tel,
	a.person_id,
	( SELECT c.create_date FROM ta_document_verify c WHERE c.person_id = a.person_id ORDER BY c.create_date DESC LIMIT 1 ) AS summit_date,
	( SELECT COUNT( 1 ) FROM ta_document_verify d WHERE d.person_id = a.person_id and d.verify_status != 0) AS total_summit,
	( SELECT COUNT( 1 ) FROM ta_document_verify e WHERE e.person_id = a.person_id AND e.verify_status = 0 ) AS unverified
FROM
	ta_document_verify a
	LEFT JOIN ta_person b ON a.person_id = b.person_id
	where a.org_id = #{orgId}
GROUP BY
	a.person_id
	) t
	<where>
	    1=1
        <if test="name != '' and name !=null">
            and t.name like CONCAT('%',#{name}, '%')
        </if>
        <if test="tel != '' and tel !=null">
            and t.tel = #{tel}
        </if>
        <if test="startDate != null">
            and t.summit_date &gt; #{startDate}
        </if>
        <if test="endDate != null">
            and t.summit_date &lt; #{ednDate}
        </if>
    </where>
ORDER BY
	t.unverified DESC,t.summit_date DESC
    </select>

</mapper>
