<?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.hdkj.lt.bf.mapper.FhzgAutoTaskCountryInfoMapper">

    <resultMap id="BaseResultMap" type="com.hdkj.lt.bf.entity.FhzgAutoTaskCountryInfo">
            <id property="id" column="id" jdbcType="INTEGER"/>
            <result property="cityCode" column="city_code" jdbcType="VARCHAR"/>
            <result property="countryCode" column="country_code" jdbcType="VARCHAR"/>
            <result property="feederTotal" column="feeder_total" jdbcType="INTEGER"/>
            <result property="contactFeederTotal" column="contact_feeder_total" jdbcType="INTEGER"/>
            <result property="simulationSuccessRate" column="simulation_success_rate" jdbcType="INTEGER"/>
            <result property="schemeSuccessRate" column="scheme_success_rate" jdbcType="INTEGER"/>
            <result property="svgSuccessRate" column="svg_success_rate" jdbcType="INTEGER"/>
    </resultMap>

    <select id="getFeederStaticData" resultType="com.hdkj.lt.bf.entity.FhzgAutoTaskCountryInfo">
        select city as city_code, city_name, maint_org as country_code, maint_org_name as country_name,count(*) as feeder_total,count(cf.feeder) as contact_feeder_total
        from dwd_shb_ds_feeder_base dsdfb
                 left join (select distinct feeder from t_feeder_topo where normal_open = 'true') cf on dsdfb.psr_id = cf.feeder
        where psr_state = '20'
        group by  city, city_name, maint_org, maint_org_name;
    </select>

    <select id="getPlanStaticData" resultType="com.hdkj.lt.bf.entity.FhzgAutoTaskCountryInfo">
        SELECT city_code,
               city_name,
               country_code,
               country_name,
               sum(is_simulation_success)                                as simulation_success,
               sum(is_simulation_success) / COUNT(*) * 100               as simulation_success_rate,
               sum(is_scheme_success)                                    as scheme_success,
               sum(is_scheme_success) / sum(is_simulation_success) * 100 as scheme_success_rate,
               sum(is_svg_success)                                       as svg_success,
               sum(is_svg_success) / sum(is_scheme_success) * 100        as svg_success_rate,
               now()                                                     as update_time
        from fhzg_auto_task_plan_detail
        WHERE
            city_code = #{cityCode} and country_code = #{countryCode}
        GROUP by city_code,
                 city_name,
                 country_code,
                 country_name
    </select>
</mapper>
