<?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.zcloud.primeport.persistence.mapper.PersonApplyMapper">

    <select id="listPage" resultType="com.zcloud.primeport.persistence.dataobject.PersonApplyDO"></select>
    <select id="xgfPersonPermissionsList"
            resultType="com.zcloud.primeport.persistence.dataobject.PersonApplyDO">
        SELECT
            pa.id,
            pa.xgf_apply_person_id,
            pa.mkmj_permission,
            pa.person_corp_id,
            pa.person_corp_name,
            pa.person_department_id,
            pa.person_department_name,
            pa.employee_person_user_id,
            pa.employee_person_user_name,
            pa.user_face_url,
            pa.user_phone,
            pa.user_card,
            xap.gate_level_auth_area,
            xap.visit_start_time,
            xap.visit_end_time,
            xap.project_name,
            xap.audit_user_id,
            xap.audit_user_name,
            xap.audit_dept_id,
            xap.audit_dept_name,
            xap.audit_corp_id,
            xap.audit_corp_name,
            xap.audit_time
        FROM
            person_apply AS pa
                LEFT JOIN
            xgf_apply_person AS xap
            ON
                pa.xgf_apply_person_id = xap.id
        WHERE
            pa.delete_enum = 'FALSE' AND
            xap.audit_flag = 2 AND
            pa.person_corp_id = #{params.applyCorpId}
        <if test="params.employeePersonUserName != null and params.employeePersonUserName != ''">
            AND xap.employee_person_user_name LIKE CONCAT('%', #{params.employeePersonUserName}, '%')
        </if>
        <if test="params.projectName != null and params.projectName != ''">
            AND xap.project_name like  CONCAT('%', #{params.projectName}, '%')
        </if>
        ORDER BY
        pa.employee_person_user_id,
        pa.id DESC
    </select>
    <select id="xgfPersonnelVehicleManagementList"
            resultType="com.zcloud.primeport.persistence.dataobject.PersonApplyDO">
        SELECT
            c.id apply_corp_id,
            c.corp_name apply_corp_name,
            IFNULL( SUM( vxc.project_count ), 0 ) project_count
        FROM
            corp_info c
                LEFT JOIN vi_xgf_count vxc ON c.id = vxc.apply_corp_id
        WHERE
            c.delete_enum = 'FALSE'
        and c.type = 3
        GROUP BY
            c.id
    </select>

</mapper>

