<?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.TContractorDailyPlanDao">
    <!-- 开启二级缓存 -->
    <!-- <cache type="org.mybatis.caches.ehcache.LoggingEhcache"/> -->
    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.testor.module.contractor.manage.model.domain.TContractorDailyPlan">
                <id column="id" property="id"/>
                <id column="contractor_id" property="contractorId"/>
                <id column="proj_id" property="projId"/>
                <id column="biz_id" property="bizId"/>
                <id column="process_id" property="processId"/>
                <id column="process_status" property="processStatus"/>
                <id column="work_site" property="workSite"/>
                <id column="work_content" property="workContent"/>
                <id column="work_start_time" property="workStartTime"/>
                <id column="work_finish_time" property="workFinishTime"/>
                <id column="special_period" property="specialPeriod"/>
                <id column="allow_tools_id" property="allowToolsId"/>
                <id column="allow_material_id" property="allowMaterialId"/>
                <id column="allow_protective_id" property="allowProtectiveId"/>
                <id column="site_manager" property="siteManager"/>
                <id column="tel" property="tel"/>
                <id column="work_pers_num" property="workPersNum"/>
                <id column="department_id" property="departmentId"/>
    </resultMap>
    <select id="listEntity" resultType="com.testor.module.contractor.manage.model.dto.TContractorDailyPlanDTO">
        SELECT
            tcdp.*,
            tci.name AS contractorName,
            tcp.name AS projectName,
            COALESCE(hazard.hazardCount, 0) AS hazardCount,
            CASE
            WHEN inspect.inspectCount > 0 THEN '已检查'
            ELSE '未检查'
            END AS checkStatus
        FROM
            t_contractor_daily_plan AS tcdp
                LEFT JOIN t_contractor_ledger AS tcl ON tcl.id = tcdp.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 = tcdp.proj_id
                left JOIN (SELECT plan_id,count(id) as hazardCount FROM t_contractor_daily_plan_inspect_result_hazard GROUP BY plan_id) as hazard on hazard.plan_id = tcdp.id
                left join (SELECT plan_id,count(id) as inspectCount FROM t_contractor_daily_plan_inspect GROUP BY plan_id) as inspect on inspect.plan_id = tcdp.id
        <!--<where>
            tcdp.status != '1'
            and tcdp.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})
            <if test="param.contractorName != null and param.contractorName != ''">
                AND tci.name like concat('%',#{param.contractorName},'%')
            </if>
            <if test="param.projectName != null and param.projectName != ''">
                AND tcp.name like concat('%',#{param.projectName},'%')
            </if>
            <if test="param.processStatus != null and param.processStatus != ''">
                AND tcdp.process_status like concat('%',#{param.processStatus},'%')
            </if>
            order by tcdp.update_date desc
        </where>-->
        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 tcdp.status != '1'
        <if test="param.contractorName != null and param.contractorName != ''">
            AND tci.name like concat('%',#{param.contractorName},'%')
        </if>
        <if test="param.projectName != null and param.projectName != ''">
            AND tcp.name like concat('%',#{param.projectName},'%')
        </if>
        <if test="param.processStatus != null and param.processStatus != ''">
            AND tcdp.process_status like concat('%',#{param.processStatus},'%')
        </if>
        order by tcdp.update_date desc
    </select>
</mapper>