<?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="cn.cecep.talroad.mapper.effective.RectificationEffectEvaluationMapper">

    <select id="selectFinishCountByFactory" resultType="cn.cecep.talroad.vo.effective.FactoryRectificationEffectCountVO">
        select
        factory.factory_name,
        industry.indus_name,
        factory.country_name,
        count(*) filter (where effect.problem_status = '500') as finishCount,
        count(*) filter (where effect.problem_status !='500' and effect.rectify_limit_end_time >= NOW()) as unFinishCount,
        count(*) filter (where effect.problem_status = '500' and effect.rectify_limit_end_time >= effect.rectify_time) as inTimeFinishCount,
        count(*) filter (where effect.problem_status = '500' and effect.rectify_limit_end_time &lt; effect.rectify_time) as outTimeFinishCount,
        count(*) filter (where effect.problem_status != '500' and effect.rectify_limit_end_time &lt; NOW()) as outTimeUnFinishCount
        from m_am_problem_fi effect
        left join pc_factory factory on effect.factory_id = factory.id
        left join pc_industry industry on factory.industry_category = industry.indus_code
        <where>
            <if test="query.regionCode != null and query.regionCode != ''">
                and factory.country_code = #{query.regionCode}
            </if>
            <if test="query.factoryName != null and query.factoryName != ''">
                and factory.factory_name like #{query.factoryName}
            </if>
            <if test="query.categoryCode != null and query.categoryCode != ''">
                and factory.industry_category = #{query.categoryCode}
            </if>
            <if test="query.startTime != null and query.startTime != '' and query.endTime != null and query.endTime != '' ">
                and to_char(effect.create_time, 'YYYY-MM-DD HH24:MI:SS') >= #{query.startTime} and to_char(effect.create_time, 'YYYY-MM-DD HH24:MI:SS') &lt; #{query.endTime}
            </if>
            <if test="(query.regionCode == null or query.regionCode == '')and query.regionCodes != null and query.regionCodes.size() != 0">
                and factory.country_code IN
                <foreach collection="query.regionCodes" separator="," item="item" close=")" open="(">
                    #{item,jdbcType=VARCHAR}
                </foreach>
            </if>
        </where>
        group by factory.factory_name,
        industry.indus_name,
        factory.country_name
    </select>
</mapper>
