<?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.qmth.sop.business.mapper.TBUserArchivesMapper">

    <select id="findUserArchivesPage" resultType="com.qmth.sop.business.bean.result.UserArchivesResult">
        SELECT
            tbua.id AS userArchivesId,
            tbua.code,
            tbua.name,
            tbua.province,
            tbua.city,
            tbua.area,
            tbua.identity,
            tbua.gender,
            education,
            tbua.mobile_number AS mobileNumber,
            tbua.email,
            base_photo_path AS basePhotoPath,
            tbuas.supplier_id AS supplierId,
            ss.name AS supplierName,
            tbuas.archives_time AS archivesTime,
            tbuas.authentication_time AS authenticationTime,
            tbuas.authentication_score AS authenticationScore,
            tbuas.authentication_valid_time AS authenticationValidTime,
            tbuas.remark,
            su.id AS userId,
            tbua.project_experience projectExperience
        FROM
            t_b_user_archives tbua
                LEFT JOIN
            t_b_user_archives_supplier tbuas ON tbua.id = tbuas.user_archives_id
                LEFT JOIN
            sys_supplier ss ON ss.id = tbuas.supplier_id
                LEFT JOIN
            sys_user su ON su.mobile_number = tbua.mobile_number
        <where>
            AND tbua.enable
            <if test="city != null and city != ''">
                AND tbua.city LIKE CONCAT('%',#{city},'%')
            </if>
            <if test="supplierId != null">
                AND ss.id = #{supplierId}
            </if>
            <if test="archivesName != null and archivesName != ''">
                AND tbua.name LIKE CONCAT('%',#{archivesName},'%')
            </if>
            <if test="archivesTimeStart != null">
                AND tbuas.archives_time >= #{archivesTimeStart}
            </if>
            <if test="archivesTimeEnd != null">
                AND #{archivesTimeEnd} >= tbuas.archives_time
            </if>
            <if test="remainValidTime != null">
                AND tbuas.authentication_valid_time >= #{remainValidTime}
            </if>
            <if test="status != null">
                <choose>
                    <when test="status">
                        AND tbuas.authentication_valid_time >= #{currentTime}
                    </when>
                    <otherwise>
                        AND (tbuas.authentication_valid_time IS NULL OR tbuas.authentication_valid_time &lt;= #{currentTime})
                    </otherwise>
                </choose>
            </if>
            <if test="roleType != null">
                AND EXISTS(SELECT
                            1
                        FROM
                            sys_user_role sur
                        LEFT JOIN
                            sys_role sr ON sur.role_id = sr.id
                        WHERE
                            su.id = sur.user_id
                                AND sr.type = #{roleType})
            </if>
            <if test="unUploadPic != null">
                <choose>
                    <when test="unUploadPic">
                        AND tbua.base_photo_path IS NOT NULL
                    </when>
                    <otherwise>
                        AND tbua.base_photo_path IS NULL
                    </otherwise>
                </choose>
            </if>
        </where>
        ORDER BY tbua.update_time DESC,tbua.code DESC
    </select>
    <select id="findSubTotal" resultType="com.qmth.sop.business.bean.result.UserArchivesResult">
        SELECT
            tbua.id AS userArchivesId,
            tbuas.authentication_valid_time AS authenticationValidTime,
            su.id AS userId
        FROM
            t_b_user_archives tbua
                LEFT JOIN
            t_b_user_archives_supplier tbuas ON tbua.id = tbuas.user_archives_id
                LEFT JOIN
            sys_user su ON su.mobile_number = tbua.mobile_number
    </select>
    <select id="findExportData" resultType="com.qmth.sop.business.bean.dto.UserArchivesImportDto">
        SELECT
            tbua.name,
            tbua.province,
            tbua.city,
            tbua.area,
            tbua.gender AS genderEnum,
            tbua.identity,
            tbua.education AS educationEnum,
            tbua.mobile_number AS mobileNumber,
            tbua.email,
            ss.name AS supplierName,
            tbuas.archives_time AS archivesTime,
            tbuas.authentication_score AS authenticationScore,
            tbuas.authentication_valid_time AS authenticationValidTime,
            tbuas.remark,
            tbua.project_experience as projectExperience,
            su.id AS userId
        FROM
            t_b_user_archives tbua
        LEFT JOIN
            t_b_user_archives_supplier tbuas ON tbua.id = tbuas.user_archives_id
        LEFT JOIN
            sys_supplier ss ON ss.id = tbuas.supplier_id
        LEFT JOIN
            sys_user su ON su.mobile_number = tbua.mobile_number
        <where>
            AND tbua.enable
            <if test="city != null and city != ''">
                AND tbua.city LIKE CONCAT('%',#{city},'%')
            </if>
            <if test="supplierId != null">
                AND ss.id = #{supplierId}
            </if>
            <if test="archivesName != null and archivesName != ''">
                AND tbua.name LIKE CONCAT('%',#{archivesName},'%')
            </if>
            <if test="archivesTimeStart != null">
                AND tbuas.archives_time >= #{archivesTimeStart}
            </if>
            <if test="archivesTimeEnd != null">
                AND #{archivesTimeEnd} >= tbuas.archives_time
            </if>
            <if test="remainValidTime != null">
                AND tbuas.authentication_valid_time >= #{remainValidTime}
            </if>
            <if test="status != null">
                <choose>
                    <when test="status">
                        AND tbuas.authentication_valid_time >= #{currentTime}
                    </when>
                    <otherwise>
                        AND (tbuas.authentication_valid_time IS NULL OR tbuas.authentication_valid_time &lt;= #{currentTime})
                    </otherwise>
                </choose>
            </if>
            <if test="roleType != null">
                AND EXISTS(SELECT
                1
                FROM
                sys_user_role sur
                LEFT JOIN
                sys_role sr ON sur.role_id = sr.id
                WHERE
                su.id = sur.user_id
                AND sr.type = #{roleType})
            </if>
            <if test="unUploadPic != null">
                <choose>
                    <when test="unUploadPic">
                        AND tbua.base_photo_path IS NOT NULL
                    </when>
                    <otherwise>
                        AND tbua.base_photo_path IS NULL
                    </otherwise>
                </choose>
            </if>
        </where>
        ORDER BY tbua.update_time DESC,tbua.code DESC
    </select>
    <select id="findUserArchivesById" resultType="com.qmth.sop.business.bean.result.UserArchivesResult">
        SELECT
            tbua.id AS userArchivesId,
            tbua.code,
            tbua.name,
            tbua.province,
            tbua.city,
            tbua.area,
            tbua.identity,
            tbua.gender,
            education,
            tbua.mobile_number AS mobileNumber,
            tbua.email,
            base_photo_path AS basePhotoPath,
            tbuas.supplier_id AS supplierId,
            ss.name AS supplierName,
            tbuas.archives_time AS archivesTime,
            tbuas.authentication_score AS authenticationScore,
            tbuas.authentication_valid_time AS authenticationValidTime,
            IF(REPLACE(UNIX_TIMESTAMP(NOW(3)), '.', '') &lt;= tbuas.authentication_valid_time,TRUE,FALSE) AS authenticationStatus,
            tbuas.remark,
            su.id AS userId,
            tbua.status AS status,
            tbua.enable as enable
        FROM
            t_b_user_archives tbua
                LEFT JOIN
            t_b_user_archives_supplier tbuas ON tbua.id = tbuas.user_archives_id
                LEFT JOIN
            sys_supplier ss ON ss.id = tbuas.supplier_id
                LEFT JOIN
            sys_user su ON su.mobile_number = tbua.mobile_number
        <where>
            <if test="userId != null">
                AND su.id = #{userId}
            </if>
            <if test="archivesId != null">
                AND tbua.id = #{archivesId}
            </if>
        </where>
    </select>

    <select id="findArchivesSourceByType" resultType="com.qmth.sop.business.bean.result.ArchivesSourceResult">
        SELECT
            tbua.id AS archivesId,
            su.id AS userId,
            tbua.name AS name,
            tbua.province AS province,
            tbua.city AS city,
            tbua.gender AS gender,
            ss.id AS supplierId,
            ss.name AS supplierName,
            tbua.status AS archivesStatus,
            tbuas.authentication_valid_time AS authenticationValidTime,
            IF(REPLACE(UNIX_TIMESTAMP(NOW(3)), '.', '') &lt;= tbuas.authentication_valid_time,TRUE,FALSE) AS authenticationStatus
        FROM
            t_b_user_archives tbua
                LEFT JOIN
            t_b_user_archives_supplier tbuas ON tbua.id = tbuas.user_archives_id
                LEFT JOIN
            sys_supplier ss ON tbuas.supplier_id = ss.id
                LEFT JOIN
            sys_user su ON tbua.mobile_number = su.mobile_number
        <where>
            AND tbua.enable
            <if test="roleTypes != null and roleTypes != '' and roleTypes.size() > 0">
                AND EXISTS( SELECT
                        1
                    FROM
                        sys_user_role sur
                    LEFT JOIN
                        sys_role sr ON sur.role_id = sr.id
                    <where>
                        sur.user_id = su.id
                        and sr.type in
                        <foreach collection="roleTypes" item="item" index="index" open="(" separator="," close=")">
                            #{item}
                        </foreach>
                    </where>)
            </if>
        </where>
    </select>

    <select id="findArchivesUsed" resultType="com.qmth.sop.business.bean.result.ArchivesSourceResult">
        SELECT
            tbua.id AS archivesId,
            su.id AS userId,
            tbua.name AS name,
            tbua.province AS province,
            tbua.city AS city,
            tbua.gender AS gender,
            ss.id AS supplierId,
            ss.name AS supplierName,
            tbua.status AS archivesStatus,
            tbuas.authentication_valid_time AS authenticationValidTime
        FROM
            t_b_user_archives tbua
                LEFT JOIN
            t_b_user_archives_supplier tbuas ON tbua.id = tbuas.user_archives_id
                LEFT JOIN
            sys_supplier ss ON tbuas.supplier_id = ss.id
                LEFT JOIN
            sys_user su ON tbua.mobile_number = su.mobile_number
        <where>
            AND tbua.enable
            <if test="roleType != null">
                AND EXISTS( SELECT
                    1
                FROM
                    sys_user_role sur
                LEFT JOIN
                    sys_role sr ON sur.role_id = sr.id
                WHERE
                    sur.user_id = su.id AND sr.type = #{roleType})
            </if>
            <if test="crmNo != null and crmNo != ''">
                AND EXISTS( SELECT
                    1
                FROM
                    t_b_user_archives_allocation tbuaa
                WHERE
                    tbuaa.archives_id = tbua.id AND tbuaa.crm_no = #{crmNo})
            </if>
        </where>
    </select>

    <select id="findUserArchivesList" resultType="com.qmth.sop.business.bean.result.UserArchivesResult">
        SELECT
            tbua.id AS userArchivesId,
            tbua.code,
            tbua.name,
            tbua.province,
            tbua.city,
            tbua.area,
            tbua.identity,
            tbua.gender,
            education,
            tbua.mobile_number AS mobileNumber,
            tbua.email,
            base_photo_path AS basePhotoPath,
            tbuas.supplier_id AS supplierId,
            ss.name AS supplierName,
            tbuas.archives_time AS archivesTime,
            tbuas.authentication_score AS authenticationScore,
            tbuas.authentication_valid_time AS authenticationValidTime,
            tbuas.remark,
            tbua.project_experience as projectExperience,
            su.id AS userId
        FROM
            t_b_user_archives tbua
        LEFT JOIN
            t_b_user_archives_supplier tbuas ON tbua.id = tbuas.user_archives_id
        LEFT JOIN
            sys_supplier ss ON ss.id = tbuas.supplier_id
        LEFT JOIN
            sys_user su ON su.mobile_number = tbua.mobile_number
        <where>
            AND tbua.enable
            <if test="city != null and city != ''">
                AND tbua.city LIKE CONCAT('%',#{city},'%')
            </if>
            <if test="supplierId != null">
                AND ss.id = #{supplierId}
            </if>
            <if test="archivesName != null and archivesName != ''">
                AND tbua.name LIKE CONCAT('%',#{archivesName},'%')
            </if>
            <if test="archivesTimeStart != null">
                AND tbuas.archives_time >= #{archivesTimeStart}
            </if>
            <if test="archivesTimeEnd != null">
                AND #{archivesTimeEnd} >= tbuas.archives_time
            </if>
            <if test="remainValidTime != null">
                AND tbuas.authentication_valid_time >= #{remainValidTime}
            </if>
            <if test="status != null">
                <choose>
                    <when test="status">
                        AND tbuas.authentication_valid_time >= #{currentTime}
                    </when>
                    <otherwise>
                        AND (tbuas.authentication_valid_time IS NULL OR tbuas.authentication_valid_time &lt;= #{currentTime})
                    </otherwise>
                </choose>
            </if>
            <if test="roleType != null">
                AND EXISTS(SELECT
                1
                FROM
                sys_user_role sur
                LEFT JOIN
                sys_role sr ON sur.role_id = sr.id
                WHERE
                su.id = sur.user_id
                AND sr.type = #{roleType})
            </if>
            <if test="unUploadPic != null">
                <choose>
                    <when test="unUploadPic">
                        AND tbua.base_photo_path IS NOT NULL
                    </when>
                    <otherwise>
                        AND tbua.base_photo_path IS NULL
                    </otherwise>
                </choose>
            </if>
        </where>
    </select>
    <select id="findTempEmp" resultType="java.util.Map">
        SELECT * FROM temp_emp
    </select>

</mapper>
