<?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="wifiSumSqlMapper">
	<select id="queryWifiSum" parameterType="java.util.Map" resultType="com.cloudcross.ssp.model.WifiSum">
		select t.cn cn,t.cnCity cnCity,t.placeName placeName,t.num num from 
		((SELECT tl.cn cn,tl.cn_city cnCity,tpci. NAME placeName,count(tw.id) num FROM t_place_class_info tpci,t_place_class tpc,t_wifi tw,t_location tl 
		WHERE tpci.id = tpc.class_id AND tpc.place_id = tw.place_id AND tw.location = tl.location 
		<if test="cn != null">
			and tl.cn = #{cn}
		</if>
		<if test="cnCity != null">
			and tl.cn_city = #{cnCity}
		</if>
		<if test="placeId != null">
			and tpci.id = #{placeId}
		</if>
		<if test="searchValue != null">
			and  tpci.name like #{searchValue}
		</if>
		group by tl.cn ,tl.cn_city,tpci.NAME)
		union ALL
		(SELECT tl.cn cn,tl.cn_city cnCity,tpci. NAME placeName,tws.num num FROM t_wifi_sum tws,t_location tl,t_place_class_info tpci,t_place_class tpc 
		WHERE tws.location = tl.location and tws.place_id = tpc.place_id and tpc.class_id = tpci.id
		<if test="cn != null">
			and tl.cn = #{cn}
		</if>
		<if test="cnCity != null">
			and tl.cn_city = #{cnCity}
		</if>
		<if test="placeId != null">
			and tpci.id = #{placeId}
		</if>
		<if test="searchValue != null">
			and  tpci.name like #{searchValue}
		</if>
		))t
		limit #{pager.offset}, #{pager.limit}
	</select>
	
	<select id="queryWifiSumCount" parameterType="java.util.Map" resultType="java.lang.Integer">
		select count(1) from 
		((SELECT tl.cn cn,tl.cn_city cnCity,tpci. NAME placeName,count(tw.id) num FROM t_place_class_info tpci,t_place_class tpc,t_wifi tw,t_location tl 
		WHERE tpci.id = tpc.class_id AND tpc.place_id = tw.place_id AND tw.location = tl.location 
		<if test="cn != null">
			and tl.cn = #{cn}
		</if>
		<if test="cnCity != null">
			and tl.cn_city = #{cnCity}
		</if>
		<if test="placeId != null">
			and tpci.id = #{placeId}
		</if>
		<if test="searchValue != null">
			and tpci.name like #{searchValue}
		</if>
		group by tl.cn ,tl.cn_city,tpci.NAME)
		union ALL
		(SELECT tl.cn cn,tl.cn_city cnCity,tpci. NAME placeName,tws.num num FROM t_wifi_sum tws,t_location tl,t_place_class_info tpci,t_place_class tpc 
		WHERE tws.location = tl.location and tws.place_id = tpc.place_id and tpc.class_id = tpci.id
		<if test="cn != null">
			and tl.cn = #{cn}
		</if>
		<if test="cnCity != null">
			and tl.cn_city = #{cnCity}
		</if>
		<if test="placeId != null">
			and tpci.id = #{placeId}
		</if>
		<if test="searchValue != null">
			and tpci.name like #{searchValue}
		</if>
		))t
	</select>
	
	<select id="queryWifiSumExport" parameterType="java.util.Map" resultType="com.cloudcross.ssp.model.WifiSum">
		select t.cn cn,t.cnCity cnCity,t.placeName placeName,t.num num from 
		((SELECT tl.cn cn,tl.cn_city cnCity,tpci. NAME placeName,count(tw.id) num FROM t_place_class_info tpci,t_place_class tpc,t_wifi tw,t_location tl 
		WHERE tpci.id = tpc.class_id AND tpc.place_id = tw.place_id AND tw.location = tl.location 
		<if test="cn != null">
			and tl.cn = #{cn}
		</if>
		<if test="cnCity != null">
			and tl.cn_city = #{cnCity}
		</if>
		<if test="placeId != null">
			and tpci.id = #{placeId}
		</if>
		<if test="searchValue != null">
			and  tpci.name like #{searchValue}
		</if>
		group by tl.cn ,tl.cn_city,tpci.NAME)
		union ALL
		(SELECT tl.cn cn,tl.cn_city cnCity,tpci. NAME placeName,tws.num num FROM t_wifi_sum tws,t_location tl,t_place_class_info tpci,t_place_class tpc 
		WHERE tws.location = tl.location and tws.place_id = tpc.place_id and tpc.class_id = tpci.id
		<if test="cn != null">
			and tl.cn = #{cn}
		</if>
		<if test="cnCity != null">
			and tl.cn_city = #{cnCity}
		</if>
		<if test="placeId != null">
			and tpci.id = #{placeId}
		</if>
		<if test="searchValue != null">
			and  tpci.name like #{searchValue}
		</if>
		))t
	</select>
</mapper>
