<?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.yeejoin.amos.boot.module.jxiop.api.mapper.PersonBasicMapper">



    <select id="queryPage" resultType="com.yeejoin.amos.boot.module.jxiop.api.dto.UserMapperDto">

        select
        person_basic.sequence_nbr sequenceNbr,
        person_basic.head_photo headphoto,
        person_account.`name` name,
        person_basic.sex,
        person_basic.phone phone,
        person_account.project_name projectName,
        person_basic.post_name postName,
        person_account.account_name accountName
        from person_basic
        LEFT JOIN person_account
        on person_account.person_id=person_basic.sequence_nbr
        <where>
            person_basic.is_delete=0
            <if test="orgCode != null and orgCode != ''">
                AND person_basic.project_org_code like concat('%',#{orgCode},'%')
            </if>
            <if test="name != null and name != ''">
                AND person_account.name like concat('%',#{name},'%')
            </if>
            <if test="accountName != null and accountName != ''">
                AND person_account.account_name like concat('%',#{accountName},'%')
            </if>
            <if test="projectName != null and projectName != ''">
                AND person_account.project_name like concat('%',#{projectName},'%')
            </if>
        </where>
        ORDER BY  person_basic.rec_date DESC
        limit #{current},#{size}
    </select>

    <select id="queryPagecount" resultType="com.yeejoin.amos.boot.module.jxiop.api.dto.UserMapperDto">

        select
        person_basic.sequence_nbr sequenceNbr
        from person_basic
        LEFT JOIN person_account
        on person_account.person_id=person_basic.sequence_nbr
        <where>
            person_basic.is_delete=0
            <if test="orgCode != null and orgCode != ''">
                AND person_basic.project_org_code like concat('%',#{orgCode},'%')
            </if>
            <if test="name != null and name != ''">
                AND person_account.name like concat('%',#{name},'%')
            </if>
            <if test="accountName != null and accountName != ''">
                AND person_account.account_name like concat('%',#{accountName},'%')
            </if>
            <if test="projectName != null and projectName != ''">
                AND person_account.project_name like concat('%',#{projectName},'%')
            </if>
        </where>
    </select>

    <delete id="deleteList"  parameterType="int">
        delete from person_basic where sequence_nbr in
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>;
        delete from person_account where person_id in
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>;
        delete from person_certificate where person_id in
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>;
        delete from person_skill_education where person_id in
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>

    <select id="getEXPersonUser" resultType="com.yeejoin.amos.boot.module.jxiop.api.dto.EXPersonUser">

        select
        person_account.`name` name,
        person_account.job_number jobNumber,
        person_account.project_name projectName,
        person_account.project_department_name departmentName,
        person_account.ID_type idType,
        person_account.ID_number idNumber,
        person_account.account_name accountName
        from person_basic
        LEFT JOIN person_account
        on person_account.person_id=person_basic.sequence_nbr
        <where>
            person_basic.is_delete=0
            <if test="name != null and name != ''">
                AND person_account.name like concat('%',#{name},'%')
            </if>
            <if test="accountName != null and accountName != ''">
                AND person_account.account_name like concat('%',#{accountName},'%')
            </if>
            <if test="projectName != null and projectName != ''">
                AND person_account.project_name like concat('%',#{projectName},'%')
            </if>
            <if test="orgCode != null and orgCode != ''">
                AND person_basic.project_org_code like concat('%',#{orgCode},'%')
            </if>

        </where>
        ORDER BY  person_basic.rec_date DESC

    </select>

    <select id="getEXPersonUserAll" resultType="com.yeejoin.amos.boot.module.jxiop.api.dto.EXPersonUser">

        select
        person_account.`name` name,
        person_account.job_number jobNumber,
        person_account.project_name projectName,
        person_account.project_department_name departmentName,
        person_account.ID_type idType,
        person_account.ID_number idNumber,
        person_account.account_name accountName,
        person_basic.sex sex,
        person_basic.birthday birthday,
        person_basic.age age,
        person_basic.native_place nativePlace,
        person_basic.folk_key folkKey,
        person_basic.politic_countenance politicCountenance,
        person_basic.registered_residence_address registeredResidenceAddress,
        person_basic.present_address presentAddress,
        person_basic.emergency_phone emergencyPhone,
        person_basic.phone phoneNum,
        person_basic.height,
        person_basic.weight,
        person_basic.work_time workTime,
        person_basic.station_work_time stationWorkTime,
        person_basic.work_place_key workPlaceKey,
        person_basic.post_name postName,
        person_skill_education.is_special_work isSpecialWork,
        person_skill_education.work_type_key workTypeKey,
        person_skill_education.work_name workName,
        person_skill_education.professional_technology_name  professionalTechnologyName,
        person_skill_education.skill_level_key skillLevelKey,
        person_skill_education.professional_technology_level_key professionalTechnologyLevelKey,
        person_skill_education.professional_qualification_certificate professionalQualificationCertificate,
        person_skill_education.professional_career_certificate professionalCareerCertificate,
        person_skill_education.initial_education_key initialEducationKey,
        person_skill_education.current_education_key currentEducationKey,
        person_skill_education.current_school currentSchool,
        person_skill_education.current_major currentMajor,
        person_certificate.document_type documentType,
        person_certificate.certificate_name certificateName,
        person_certificate.certificate_time certificateTime,
        person_certificate.issuing_authority issuingAuthority,
        person_certificate.certificate_num certificateNum,
        person_certificate.certificate_project certificateProject,
        person_certificate.valid_period validPeriod,
        person_certificate.certificate_grade certificateGrade
        from person_basic
        LEFT JOIN person_account
        on person_account.person_id=person_basic.sequence_nbr
        LEFT JOIN person_certificate
        on person_certificate.person_id=person_basic.sequence_nbr
        LEFT JOIN person_skill_education
        on person_skill_education.person_id=person_basic.sequence_nbr
        <where>
            person_basic.is_delete=0
            <if test="name != null and name != ''">
                AND person_account.name like concat('%',#{name},'%')
            </if>
            <if test="accountName != null and accountName != ''">
                AND person_account.account_name like concat('%',#{accountName},'%')
            </if>
            <if test="projectName != null and projectName != ''">
                AND person_account.project_name like concat('%',#{projectName},'%')
            </if>
            <if test="orgCode != null and orgCode != ''">
                AND person_basic.project_org_code like concat('%',#{orgCode},'%')
            </if>

            <if test="ids != null">
                and person_basic.sequence_nbr  in
                <foreach collection="ids" index="index" item="id" open="(" close=")" separator=",">
                    #{id}
                </foreach>
            </if>

        </where>
        ORDER BY  person_basic.rec_date DESC

    </select>

    <select id="getPersonYardStatistics" resultType="java.util.Map">
        SELECT
            a.qrcode_color as qrCodeColor,
            count( 1 ) as value
        FROM
            person_basic a,
            station_basic c
            <where>
                a.project_org_code = c.project_org_code
                <if test="parentCode != null and parentCode != ''">
                    AND a.project_org_code like concat(#{parentCode},'%')
                </if>
                <if test="platformStationId != null and platformStationId != ''">
                    AND c.platform_station_id = #{platformStationId}
                </if>
                <if test="areaCode != null and areaCode != ''">
                    AND c.area_code = #{areaCode}
                </if>
            </where>
        GROUP BY
         a.qrcode_color
    </select>

    <select id="getPersonYardByPage" resultType="java.util.Map">
        SELECT
            ifnull(b.name, '') as objectName ,
            ifnull(a.qrcode_color, '') AS qrCodeColor,
            ifnull(c.station_name, '') AS stationName,
            ifnull(a.rec_date, '') as recDate,
            ifnull(a.qrcode_date, '') as qrcodeDate,
            a.sequence_nbr as objectId,
            b.project_name as unitName,
            case when a.qrcode_color = 'red' then 1
            when a.qrcode_color = 'yellow' then 2
            else 3 end as sort
        FROM
        person_basic a
        INNER JOIN station_basic c ON a.project_org_code = c.project_org_code
        LEFT JOIN person_account b ON a.sequence_nbr = b.person_id
        <where>
            a.qrcode_color <![CDATA[<>]]> ''
            <if test="parentCode != null and parentCode != ''">
                AND a.project_org_code like concat(#{parentCode},'%')
            </if>
            <if test="date != null and date != ''">
                AND a.rec_date like concat(#{date},'%')
            </if>
            <if test="qrCodeColor != null and qrCodeColor != ''">
                AND a.qrcode_color = #{qrCodeColor}
            </if>
            <if test="ne != null and ne != ''">
                AND a.qrcode_color != #{ne}
            </if>
            <if test="platformStationId != null and platformStationId != ''">
                AND c.platform_station_id = #{platformStationId}
            </if>
            <if test="areaCode != null and areaCode != ''">
                AND c.area_code = #{areaCode}
            </if>
        </where>
        ORDER BY sort asc,a.rec_date DESC
        limit #{current},#{size}
    </select>

    <select id="getPersonYardByPageCount" resultType="java.lang.Integer">
        SELECT
        count(1)
        FROM
        person_basic a
        INNER JOIN station_basic c ON a.project_org_code = c.project_org_code
        LEFT JOIN person_account b ON a.sequence_nbr = b.person_id
        <where>
            a.qrcode_color <![CDATA[<>]]> ''
            <if test="parentCode != null and parentCode != ''">
                AND a.project_org_code like concat(#{parentCode},'%')
            </if>
            <if test="date != null and date != ''">
                AND a.rec_date like concat(#{date},'%')
            </if>
            <if test="qrCodeColor != null and qrCodeColor != ''">
                AND a.qrcode_color = #{qrCodeColor}
            </if>
            <if test="ne != null and ne != ''">
                AND a.qrcode_color != #{ne}
            </if>
            <if test="platformStationId != null and platformStationId != ''">
                AND c.platform_station_id = #{platformStationId}
            </if>
            <if test="areaCode != null and areaCode != ''">
                AND c.area_code = #{areaCode}
            </if>
        </where>
    </select>

    <select id="getPersonStatistics" resultType="com.yeejoin.amos.boot.module.jxiop.api.entity.StationQrCodeStatistics">
        SELECT
            project_org_code AS `code`,
            count( qrcode_color = 'red' OR NULL ) AS redNum,
            count( qrcode_color = 'yellow' OR NULL ) AS yellowNum,
            count( qrcode_color = 'green' OR NULL ) AS greenNum,
            'person' AS type,
            CURRENT_DATE AS recordDate
        FROM
            person_basic
        GROUP BY
            project_org_code
    </select>

    <select id="getPersonDetailInfoByObjectId" resultType="java.util.Map">
        SELECT
            IFNULL(b.`name`, '') as `name`,
            IFNULL(a.phone, '') as phone,
            IFNULL(a.post_name, '') as postName,
            IFNULL(b.project_name, '') as stationName,
            '' as unitName,
            IFNULL(a.qrcode_color, 'green') as qrCodeColor,
            IFNULL(a.rec_date, '') AS recDate,
            IFNULL(a.qrcode_date, '') AS qrcodeDate,
            IFNULL(head_photo, '') AS headPhoto
        FROM
            person_basic a
                LEFT JOIN person_account b ON a.sequence_nbr = b.person_id
                LEFT JOIN station_basic c ON a.project_org_code = c.project_org_code
        where
            a.sequence_nbr = #{objectId}
    </select>
</mapper>
