<?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 is_showband 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>

    <select id="selectProductManager" parameterType="String" resultType="HashMap">
        SELECT * FROM `product_manage` WHERE product_id = (SELECT b.`bandproduct_id` FROM hhr_user_school a  LEFT JOIN hhr_school b ON a.`school_id`=b.`id`  WHERE a.user_id=#{id})
    </select>

</mapper>