<?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
			tl.cn cn,
			tl.cn_city cnCity,
			tpci.NAME placeName,
			count(tw.id) sum
		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>
		GROUP BY tl.cn,tl.cn_city,tpci.NAME
		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) sum
		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>
		GROUP BY tl.cn,tl.cn_city,tpci.NAME
		) t
	</select>
</mapper>
