<?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.mapper.dsno1.api.OtherApiMapper">

    <select id="getEightWorks" parameterType="pd" resultType="pd">
        select count(1) as count,sum(case when f.APPLY_STATUS=6 then 1 else 0 end) as pass_count from BUS_BLINDBOARD f  where DATE_FORMAT(f.CREATTIME,'%Y-%m-%d') <![CDATA[ <= ]]> #{ENDTIME} and DATE_FORMAT(f.CREATTIME,'%Y-%m-%d') <![CDATA[ >= ]]> #{STARTTIME} and f.CORPINFO_ID = #{CORPINFO_ID}
        union all
        select count(1) as count,sum(case when f.APPLY_STATUS=6 then 1 else 0 end) as pass_count from BUS_BREAKGROUND f  where DATE_FORMAT(f.CREATTIME,'%Y-%m-%d') <![CDATA[ <= ]]> #{ENDTIME} and DATE_FORMAT(f.CREATTIME,'%Y-%m-%d') <![CDATA[ >= ]]> #{STARTTIME} and f.CORPINFO_ID = #{CORPINFO_ID}
        union all
        select count(1) as count,sum(case when f.APPLY_STATUS=6 then 1 else 0 end) as pass_count from BUS_CONFINEDSPACE f  where DATE_FORMAT(f.CREATTIME,'%Y-%m-%d') <![CDATA[ <= ]]> #{ENDTIME} and DATE_FORMAT(f.CREATTIME,'%Y-%m-%d') <![CDATA[ >= ]]> #{STARTTIME} and f.CORPINFO_ID = #{CORPINFO_ID}
        union all
        select count(1) as count,sum(case when f.APPLY_STATUS=5 then 1 else 0 end) as pass_count from BUS_ELECTRICITY f  where DATE_FORMAT(f.CREATTIME,'%Y-%m-%d') <![CDATA[ <= ]]> #{ENDTIME} and DATE_FORMAT(f.CREATTIME,'%Y-%m-%d') <![CDATA[ >= ]]> #{STARTTIME} and f.CORPINFO_ID = #{CORPINFO_ID}
        union all
        select count(1) as count,sum(case when f.APPLY_STATUS=6 then 1 else 0 end) as pass_count from BUS_CUTROAD f  where DATE_FORMAT(f.CREATTIME,'%Y-%m-%d') <![CDATA[ <= ]]> #{ENDTIME} and DATE_FORMAT(f.CREATTIME,'%Y-%m-%d') <![CDATA[ >= ]]> #{STARTTIME} and f.CORPINFO_ID = #{CORPINFO_ID}
        union all
        select count(1) as count,sum(case when f.APPLY_STATUS=6 then 1 else 0 end) as pass_count from BUS_HIGHWORK f  where DATE_FORMAT(f.CREATTIME,'%Y-%m-%d') <![CDATA[ <= ]]> #{ENDTIME} and DATE_FORMAT(f.CREATTIME,'%Y-%m-%d') <![CDATA[ >= ]]> #{STARTTIME} and f.CORPINFO_ID = #{CORPINFO_ID}
        union all
        select count(1) as count,sum(case when f.APPLY_STATUS=6 then 1 else 0 end) as pass_count from BUS_HOISTING f  where DATE_FORMAT(f.CREATTIME,'%Y-%m-%d') <![CDATA[ <= ]]> #{ENDTIME} and DATE_FORMAT(f.CREATTIME,'%Y-%m-%d') <![CDATA[ >= ]]> #{STARTTIME} and f.CORPINFO_ID = #{CORPINFO_ID}
        union all
        select count(1) as count,sum(case when f.APPLY_STATUS=8 then 1 else 0 end) as pass_count from BUS_HOTWORK f  where DATE_FORMAT(f.CREATTIME,'%Y-%m-%d') <![CDATA[ <= ]]> #{ENDTIME} and DATE_FORMAT(f.CREATTIME,'%Y-%m-%d') <![CDATA[ >= ]]> #{STARTTIME} and f.CORPINFO_ID = #{CORPINFO_ID}
    </select>
</mapper>
