<?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.com.victorysoft.business.cyyzs.dao.FailureReasonDao">

    <select id="selectDetails" resultType="cn.com.victorysoft.business.cyyzs.entity.FailureReasonDetails">
        select
            *
        from
            water_failure_reason
        where
            1 = 1
        <if test="jh != null and jh != ''"> and jh = #{jh}</if>
        <if test="dwdm != null and dwdm != ''"> and dwdm = #{dwdm}</if>
        <if test="dydm != null and dydm != ''"> and dydm = #{dydm}</if>
        <if test="yclx != null and yclx != ''"> </if>
        <if test="gylx != null and gylx != ''"> and gylx = #{gylx}</if>
    </select>

    <select id="selectListGroupGylx" resultType="cn.com.victorysoft.business.cyyzs.entity.FailureReason">
        select
            a.gylx,
            (select flag_name from sys_flag where flag_code = a.gylx) gylx_name,
            sum(case when fsyz_ck = 'Y' then 1 else 0 end ) fsyz_ck,
            sum(case when jgyz = 'Y' then 1 else 0 end ) jgyz,
            sum(case when fgq = 'Y' then 1 else 0 end ) fgq,
            sum(case when psq = 'Y' then 1 else 0 end ) psq,
            sum(case when xjf = 'Y' then 1 else 0 end ) xjf,
            sum(case when mdgj = 'Y' then 1 else 0 end ) mdgj,
            sum(case when mgz = 'Y' then 1 else 0 end ) mgz
        from
            water_failure_reason a
        where
            1 = 1
        <if test="gylx != null and gylx != ''"> and a.gylx = #{gylx}</if>
        <if test="yclx != null and yclx != ''"></if>
        <if test="dydm != null and dydm != ''"> and a.dydm = #{dydm}</if>
        <if test="dwdm != null and dwdm != ''"> and a.dwdm = #{dwdm}</if>
        group by
            a.gylx
    </select>

    <select id="selectListGroupYclx" resultType="cn.com.victorysoft.business.cyyzs.entity.FailureReason">
        select
            b.yclx,
            (select flag_name from sys_flag where flag_code = b.yclx) yclx_name,
            sum(case when fsyz_ck = 'Y' then 1 else 0 end ) fsyz_ck,
            sum(case when jgyz = 'Y' then 1 else 0 end ) jgyz,
            sum(case when fgq = 'Y' then 1 else 0 end ) fgq,
            sum(case when psq = 'Y' then 1 else 0 end ) psq,
            sum(case when xjf = 'Y' then 1 else 0 end ) xjf,
            sum(case when mdgj = 'Y' then 1 else 0 end ) mdgj,
            sum(case when mgz = 'Y' then 1 else 0 end ) mgz
        from
            water_failure_reason a
        left join water_well_basic b on a.jh = b.jh
        where
            1 = 1
        <if test="gylx != null and gylx != ''"> and a.gylx = #{gylx}</if>
        <if test="yclx != null and yclx != ''"> and b.yclx = #{yclx}</if>
        <if test="dydm != null and dydm != ''"> and a.dydm = #{dydm}</if>
        <if test="dwdm != null and dwdm != ''"> and a.dwdm = #{dwdm}</if>
        group by
            b.yclx
    </select>

    <select id="getBarData" resultType="cn.com.victorysoft.business.cyyzs.entity.FailureReason">
        select
            a.gylx,
            (select flag_name from sys_flag where flag_code = a.gylx) gylx_name,
            sum(case when fsyz_ck = 'Y' then 1 else 0 end ) fsyz_ck
        from
            water_failure_reason a
        where
            1 = 1
        <if test="gylx != null and gylx != ''"> and a.gylx = #{gylx}</if>
        <if test="yclx != null and yclx != ''"></if>
        <if test="dydm != null and dydm != ''"> and a.dydm = #{dydm}</if>
        <if test="dwdm != null and dwdm != ''"> and a.dwdm = #{dwdm}</if>
        group by
            a.gylx
    </select>

    <select id="getPieData" resultType="org.apache.commons.collections.map.CaseInsensitiveMap">
        select
            sum(case when fsyz_ck = 'Y' then 1 else 0 end ) "油管腐蚀严重或穿孔",
            sum(case when jgyz = 'Y' then 1 else 0 end ) "油管结垢严重",
            sum(case when fgq = 'Y' then 1 else 0 end ) "封隔器",
            sum(case when psq = 'Y' then 1 else 0 end ) "配水器",
            sum(case when xjf = 'Y' then 1 else 0 end ) "洗井阀",
            sum(case when mdgj = 'Y' then 1 else 0 end ) "锚定工具",
            sum(case when mgz = 'Y' then 1 else 0 end ) "埋管柱"
        from
            water_failure_reason a
        where
            1 = 1
        <if test="gylx != null and gylx != ''"> and a.gylx = #{gylx}</if>
        <if test="yclx != null and yclx != ''"></if>
        <if test="dydm != null and dydm != ''"> and a.dydm = #{dydm}</if>
        <if test="dwdm != null and dwdm != ''"> and a.dwdm = #{dwdm}</if>
    </select>
</mapper>