<?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.winsun.mapper.UniversityInfoMapper">

    <select id="getBroadBandConfigList" resultType="Map">
        select
            ui.*, pm.product_title, pm.product_region, pm.product_price, pm.product_meal, pm.product_rate, pm.product_university, pm.product_school_abbreviated, pm.product_state,DATE_FORMAT(product_create_time,'%Y-%m-%d %H:%i:%s') as create_time, GROUP_CONCAT(hs.kdsl) as kdsl, GROUP_CONCAT(hs.kdy_btn) as kdy_btn
        from
            university_info ui
        left join product_manage pm ON ui.university_id = pm.university_id
        left join
            (
                select
                    bandproduct_id,
                    concat(school_name, '：', case kdsl when 1 then '已显示' when 0 then '未显示' else '未设置' end) kdsl,
                    concat(school_name, '：', case kdy_btn when 1 then '已显示' when 0 then '未显示' else  '未设置' end) kdy_btn
                from hhr_school where bandproduct_id != '') hs
            on pm.product_id = hs.bandproduct_id
        <where>
            <if test = "productUniversity != null and productUniversity != ''">
                and product_university like concat('%', #{productUniversity}, '%')
            </if>
            <if test = "productRegion != null and productRegion != ''">
                and product_region like concat('%', #{productRegion}, '%')
            </if>
        </where>
        group by ui.university_id
    </select>

</mapper>