<?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.testor.module.contractor.manage.dao.TContractorAllowDao">
    <!-- 开启二级缓存 -->
    <!-- <cache type="org.mybatis.caches.ehcache.LoggingEhcache"/> -->
    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.testor.module.contractor.manage.model.domain.TContractorAllow">
                <id column="id" property="id"/>
                <id column="contractor_id" property="contractorId"/>
                <id column="proj_id" property="projId"/>
                <id column="process_id" property="processId"/>
                <id column="process_status" property="processStatus"/>
                <id column="biz_id" property="bizId"/>
                <id column="work_description" property="workDescription"/>
                <id column="work_location" property="workLocation"/>
                <id column="work_plan" property="workPlan"/>
                <id column="emergency_plan" property="emergencyPlan"/>
                <id column="health_safety_notice" property="healthSafetyNotice"/>
                <id column="safety_commitment" property="safetyCommitment"/>
    </resultMap>

    <select id="pageList" resultType="com.testor.module.contractor.manage.model.dto.TContractorAllowDTO">
        SELECT
        tca.*,
        tci.NAME AS contractorName,
        tcp.NAME AS projectName,
        COALESCE(personCount, 0) AS personCount,
        COALESCE(toolsCount, 0) AS toolsCount,
        COALESCE(materialCount, 0) AS materialCount,
        COALESCE(protectiveCount, 0) AS protectiveCount,
        COALESCE(trainingRecordCount, 0) AS trainingRecordCount
        FROM
        t_contractor_allow AS tca
        left join t_contractor_ledger as tcl on tcl.id = tca.contractor_id
        LEFT JOIN t_contractor_info AS tci ON tci.id = tcl.info_id
        LEFT JOIN t_contractor_project AS tcp ON tcp.id = tca.proj_id
        LEFT JOIN ( SELECT allow_id, COUNT( id ) AS personCount FROM t_contractor_allow_person GROUP BY allow_id ) AS person ON tca.id = person.allow_id
        LEFT JOIN ( SELECT allow_id, COUNT( id ) AS toolsCount FROM t_contractor_allow_tools GROUP BY allow_id ) AS tools ON tca.id = tools.allow_id
        LEFT JOIN ( SELECT allow_id, COUNT( id ) AS materialCount FROM t_contractor_allow_material GROUP BY allow_id ) AS material ON tca.id = material.allow_id
        LEFT JOIN ( SELECT allow_id, COUNT( id ) AS protectiveCount FROM t_contractor_allow_protective GROUP BY allow_id ) AS protective ON tca.id = protective.allow_id
        LEFT JOIN ( SELECT allow_id, COUNT( id ) AS trainingRecordCount FROM t_contractor_allow_person WHERE training_record IS NOT NULL GROUP BY allow_id ) AS trainingRecord ON tca.id = trainingRecord.allow_id
        <!--where tca.org_id IN (select org.org_id from t_sys_org org where org.parent_ids like concat('%',#{param.orgId},'%') or org.org_id = #{param.orgId})-->
        WHERE EXISTS (
        SELECT 1
        FROM unnest(string_to_array(tcl.org_id, ',')) AS org(id)
        JOIN t_sys_org o ON org.id = o.org_id
        WHERE
        o.parent_ids LIKE concat('%',#{param.orgId},'%')
        OR o.org_id = #{param.orgId}
        )
        and tca.status = '0'
        <if test="param.contractorName != null and param.contractorName != ''">
            AND tci.name like concat('%',#{param.contractorName},'%')
        </if>
        <if test="param.processStatus != null and param.processStatus != ''">
            AND tca.process_status like concat('%',#{param.processStatus},'%')
        </if>
        order by tca.update_date desc

    </select>

</mapper>