<?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="lingtao.net.dao.QuoteDataMapper">

	<select id="quoteDatas" parameterType="QuoteData" resultType="QuoteData">
		SELECT 
		 	*,
		  	b.`role` 
		FROM
			tbl_quote_data a 
		LEFT JOIN 
			tbl_sys_user b 
		ON 
			a.`username` = b.`username` 
		<where>
			<!-- 只有一个店铺或者搜索店铺的时候 -->
			<if test="roleSearch != null and roleSearch != ''">
				and b.role like '%${roleSearch}%'
  			</if>
  			<!-- 没有条件查询，默认查出所有的店铺数据 -->
  			<if test = "roleArr != null and roleArr != '' " > 
				and	b.role in
				<foreach collection="roleArr" item="item" open="(" separator="," close=")">
					#{item}
				</foreach>
				<foreach collection="roleArr" item="item">
					or b.role like '%${item}%'
				</foreach>
			</if>
			<!-- 有条件查询，查出拥有的店铺以及条件数据 -->
			<if test = "roleSearchArr != null and roleSearchArr != '' " > 
				and	(
				<foreach collection="roleSearchArr" item="item" separator="or">
					b.role like '%${item}%'
				</foreach>
				)
			</if>
			<if test="realname !=null and realname != '' ">
				and a.realname like '%${realname}%'
			</if>
			<if test="remark !=null and remark != '' ">
				and remark like '%${remark}%'
			</if>
			<if test="wangwang !=null and wangwang != '' ">
				and wangwang like '%${wangwang}%'
			</if>
			<if test="price !=null and price != '' ">
				and price >= #{price}
			</if>
			<if test="isBuy != null and isBuy != ''">
				and isBuy = #{isBuy}
			</if>
			<if test="isBuyToDay != null and isBuyToDay != ''">
				and isBuyToDay = #{isBuyToDay}
			</if>
			<if test="isSelect != null and isSelect != ''">
				and isSelect = #{isSelect}
			</if>
			<if test="isFillIn != null and isFillIn != ''">
				and isFillIn = #{isFillIn}
			</if>
			<if test="proTypeLabel !=null and proTypeLabel != '' ">
				and proTypeLabel = #{proTypeLabel}
			</if>
			<if test="orderNumber !=null and orderNumber != '' ">
				and orderNumber like '%${orderNumber}%'
			</if>
			<if test="commentManager !=null and commentManager != '' ">
				and commentManager like '%${commentManager}%'
			</if>
			<if test="quoteTimeBegin !=null and quoteTimeBegin != '' ">
				<![CDATA[and quoteTime >= #{quoteTimeBegin} ]]>
			</if>
			<if test="quoteTimeEnd !=null and quoteTimeEnd != '' ">
				 <![CDATA[and quoteTime <= #{quoteTimeEnd} ]]>
			</if>
			<if test="isSelfShop != null and isSelfShop != ''">
				and b.role NOT LIKE '%1010%' 
				AND b.role NOT LIKE '%1021%' 
				AND b.role NOT LIKE '%1022%' 
				AND b.role NOT LIKE '%1023%' 
				AND b.role NOT LIKE '%1024%' 
				AND b.role NOT LIKE '%1025%' 
				AND b.role NOT LIKE '%1016%' 
				AND b.role NOT LIKE '%1018%' 
			</if>
		</where>
		ORDER BY id DESC
	</select>
	
	<insert id="addQuoteData" parameterType="QuoteData">
		INSERT INTO tbl_quote_data
			(id,username,realname,role,shopname,price,isBuy,isBuyToDay,remark,remarkJudge,quoteTime,isSelect,isFillIn,wangwang,proTypeLabel)
		VALUES
			(NULL,#{username},#{realname},#{role},#{shopname},#{price},#{isBuy},#{isBuyToDay},#{remark},#{remarkJudge},now(),#{isSelect},#{isFillIn},#{wangwang},#{proTypeLabel})
	</insert>
	
	<update id="updateById" parameterType="QuoteData">
		UPDATE 
		  	tbl_quote_data 
		<set>
			<if test="commentManager != null and commentManager != ''">
			  	commentManager = #{commentManager},
			  	commentManagerDate = now(),
			</if>
			<if test="price != null and price != ''">
			  	price = #{price},
			</if>
			<if test="buyPrice != null and buyPrice != ''">
			  	buyPrice = #{buyPrice},
			</if>
			<if test="wangwang != null and wangwang != ''">
			  	wangwang = #{wangwang},
			  	isFillIn = #{isFillIn},
			  	fillInDate = now(),
			</if>
			<if test="commentSelf != null and commentSelf != ''">
			  	commentSelf = #{commentSelf},
			  	commentSelfDate = now(),
			</if>
		</set>
		WHERE 
			id  = #{id} 
	</update>
	
	<!-- 改变当天成交状态 -->
  	<update id="changeIsBuyToDay">
		UPDATE 
	  		tbl_quote_data 
		SET
		  isBuyToDay = 
		  	CASE
		    WHEN 
		    	isBuyToDay = '1' 
		    THEN 
		    	'0' 
		    ELSE 
		    	'1' 
		  END,
		  isBuyToDayDate = now()
		WHERE id = #{id} 
   	</update>
	
	<!-- 店长改变成交状态 -->
  	<update id="changeIsBuy">
		UPDATE 
	  		tbl_quote_data 
		SET
		  isBuy = 
		  	CASE
		    WHEN 
		    	isBuy = '1' 
		    THEN 
		    	'0' 
		    ELSE 
		    	'1' 
		  END,
		  buyDate = now()
		WHERE id = #{id} 
   	</update>
   	
   	<!-- 查询3分钟内的数据 -->
	<select id="getQuoteDataByMinutes" parameterType="String" resultType="String">
		SELECT
		  remarkJudge
		FROM
		  tbl_quote_data
		WHERE id >
		  (SELECT
		    id - 10
		  FROM
		    tbl_quote_data
		  WHERE quoteTime > DATE_SUB(NOW(), INTERVAL 3 MINUTE)
		  LIMIT 1)
		AND 
			quoteTime > DATE_SUB(NOW(), INTERVAL 3 MINUTE)
		AND
			username = #{username}
  	</select>
  	
  	<select id="getShopName" resultType="String">
  		SELECT 
  			DISTINCT shopname FROM tbl_quote_data  
  		WHERE 
  			shopname != ""
			<if test="quoteTimeBegin !=null and quoteTimeBegin != '' ">
				<![CDATA[and quoteTime >= #{quoteTimeBegin} ]]>
			</if>
			<if test="quoteTimeEnd !=null and quoteTimeEnd != '' ">
				<![CDATA[and quoteTime <= #{quoteTimeEnd} ]]>
			</if>
  		ORDER BY -shopname DESC
	</select>
 	 
  	<!-- 根据店铺查找人员 -->
  	<select id="getRealnames" parameterType="String" resultType="String">
	  	SELECT DISTINCT 
		  a.realname 
		FROM
		  tbl_quote_data a 
		  LEFT JOIN tbl_sys_user b 
		    ON a.`username` = b.`username` 
		WHERE b.role LIKE '%${shopname}%'
		AND b.userStatus = '1'
  	</select>
  	
  	<!-- 根据店铺查找报价数据 -->
  	<select id="getEchartList" parameterType="QuoteData" resultType="QuoteData">
		SELECT 
			t.alldata,
		  	t.allBuy,
		  	t.alldata - t.allBuy allNotBuy,
		  	t.todayBuy,
		  	t.allBuy - t.todayBuy notTodayBuy,
		  	t.todayBuyPrice + t.notTodayBuyPrice allBuyPrice,
		  	t.todayBuyPrice,
		  	t.notTodayBuyPrice,
		  	t.allBuy / t.alldata allBuyPercentage 
		FROM
		  	(SELECT 
		      COUNT(*) alldata,
		    (SELECT 
		      COUNT(*) 
		    FROM
		      (SELECT 
		        COUNT(*) 
		      FROM
		        tbl_quote_data 
		      WHERE (isBuyToDay = "1" 
		          OR isBuy = "1") 
				<if test="shopname !=null and shopname != '' and shopname != '999'">
			    	AND shopname = #{shopname}
			   	</if>
			   	<if test="realname !=null and realname != '' ">
			    	AND realname = #{realname}
			   	</if>
			   	<if test="proTypeLabel !=null and proTypeLabel != '' ">
			    	AND proTypeLabel = #{proTypeLabel}
			   	</if>
		      	<if test="quoteTimeBegin !=null and quoteTimeBegin != '' ">
					<![CDATA[and quoteTime >= #{quoteTimeBegin} ]]>
				</if>
				<if test="quoteTimeEnd !=null and quoteTimeEnd != '' ">
					<![CDATA[and quoteTime <= #{quoteTimeEnd} ]]>
				</if>
		        AND price >= 300 
		      GROUP BY wangwang) b) allBuy,
		    (SELECT 
		      COUNT(*) 
		    FROM
		      (SELECT 
		        COUNT(*) 
		      FROM
		        tbl_quote_data 
		      WHERE isBuyToDay = "1" 
		        <if test="shopname !=null and shopname != '' and shopname != '999'">
			    	AND shopname = #{shopname}
			   	</if>
			   	<if test="realname !=null and realname != '' ">
			    	AND realname = #{realname}
			   	</if>
			   	<if test="proTypeLabel !=null and proTypeLabel != '' ">
			    	AND proTypeLabel = #{proTypeLabel}
			   	</if>
		      	<if test="quoteTimeBegin !=null and quoteTimeBegin != '' ">
					<![CDATA[and quoteTime >= #{quoteTimeBegin} ]]>
				</if>
				<if test="quoteTimeEnd !=null and quoteTimeEnd != '' ">
					<![CDATA[and quoteTime <= #{quoteTimeEnd} ]]>
				</if>
		        AND price >= 300 
		      GROUP BY wangwang) b) todayBuy,
		    (SELECT 
		      ROUND(SUM(IFNULL(price, 0)), 2) 
		    FROM
		      (SELECT 
		        price 
		      FROM
		        tbl_quote_data 
		      WHERE isBuyToDay = "1" 
		        <if test="shopname !=null and shopname != '' and shopname != '999'">
			    	AND shopname = #{shopname}
			   	</if>
			   	<if test="realname !=null and realname != '' ">
			    	AND realname = #{realname}
			   	</if>
			   	<if test="proTypeLabel !=null and proTypeLabel != '' ">
			    	AND proTypeLabel = #{proTypeLabel}
			   	</if>
		      	<if test="quoteTimeBegin !=null and quoteTimeBegin != '' ">
					<![CDATA[and quoteTime >= #{quoteTimeBegin} ]]>
				</if>
				<if test="quoteTimeEnd !=null and quoteTimeEnd != '' ">
					<![CDATA[and quoteTime <= #{quoteTimeEnd} ]]>
				</if>
		        AND price >= 300 
		      GROUP BY wangwang) b) todayBuyPrice,
		    (SELECT 
		       IFNULL(ROUND(SUM(IFNULL(buyPrice, 0)), 2), 0) 
		    FROM
		      (SELECT 
		        buyPrice 
		      FROM
		        tbl_quote_data 
		      WHERE isBuy = "1" 
		        AND isBuyToDay = "0" 
		        <if test="shopname !=null and shopname != '' and shopname != '999'">
			    	AND shopname = #{shopname}
			   	</if>
			   	<if test="realname !=null and realname != '' ">
			    	AND realname = #{realname}
			   	</if>
			   	<if test="proTypeLabel !=null and proTypeLabel != '' ">
			    	AND proTypeLabel = #{proTypeLabel}
			   	</if>
		      	<if test="quoteTimeBegin !=null and quoteTimeBegin != '' ">
					<![CDATA[and quoteTime >= #{quoteTimeBegin} ]]>
				</if>
				<if test="quoteTimeEnd !=null and quoteTimeEnd != '' ">
					<![CDATA[and quoteTime <= #{quoteTimeEnd} ]]>
				</if>
		        AND buyPrice >= 300 
		      GROUP BY wangwang) b) notTodayBuyPrice 
		  FROM
		    (SELECT 
		      COUNT(*),
		      realname,
		      shopname 
		    FROM
		      tbl_quote_data a 
		    WHERE 1 = 1 
		      	<if test="shopname !=null and shopname != '' and shopname != '999'">
			    	AND shopname = #{shopname}
			   	</if>
			   	<if test="realname !=null and realname != '' ">
			    	AND realname = #{realname}
			   	</if>
			   	<if test="proTypeLabel !=null and proTypeLabel != '' ">
			    	AND proTypeLabel = #{proTypeLabel}
			   	</if>
		      	<if test="quoteTimeBegin !=null and quoteTimeBegin != '' ">
					<![CDATA[and quoteTime >= #{quoteTimeBegin} ]]>
				</if>
				<if test="quoteTimeEnd !=null and quoteTimeEnd != '' ">
					<![CDATA[and quoteTime <= #{quoteTimeEnd} ]]>
				</if>
		      AND price >= 300 
		      AND isFillin = '1' 
		    GROUP BY wangwang) tab) t  
  	</select>
  	
  	<!-- 根据店铺查找客服流失数据 -->
  	<select id="getKefuEchartList" parameterType="QuoteData" resultType="QuoteData">
		SELECT 
			t.alldata,
		  	t.allBuy,
		  	t.alldata - t.allBuy t.notBuy
		FROM
		  	(SELECT 
		      COUNT(*) alldata,
		    (SELECT 
		      COUNT(*) 
		    FROM
		      (SELECT 
		        COUNT(*) 
		      FROM
		        tbl_quote_data 
		      WHERE (isBuyToDay = "1" 
		          OR isBuy = "1") 
				<if test="shopname !=null and shopname != '' and shopname != '999'">
			    	AND shopname = #{shopname}
			   	</if>
			   	<if test="realname !=null and realname != '' ">
			    	AND realname = #{realname}
			   	</if>
			   	<if test="proTypeLabel !=null and proTypeLabel != '' ">
			    	AND proTypeLabel = #{proTypeLabel}
			   	</if>
		      	<if test="quoteTimeBegin !=null and quoteTimeBegin != '' ">
					<![CDATA[and quoteTime >= #{quoteTimeBegin} ]]>
				</if>
				<if test="quoteTimeEnd !=null and quoteTimeEnd != '' ">
					<![CDATA[and quoteTime <= #{quoteTimeEnd} ]]>
				</if>
		        AND price >= 200 
		      GROUP BY wangwang) b) allBuy
		      FROM
		    (SELECT 
		      COUNT(*),
		      realname,
		      shopname 
		    FROM
		      tbl_quote_data a 
		    WHERE 1 = 1 
		      	<if test="shopname !=null and shopname != '' and shopname != '999'">
			    	AND shopname = #{shopname}
			   	</if>
			   	<if test="realname !=null and realname != '' ">
			    	AND realname = #{realname}
			   	</if>
			   	<if test="proTypeLabel !=null and proTypeLabel != '' ">
			    	AND proTypeLabel = #{proTypeLabel}
			   	</if>
		      	<if test="quoteTimeBegin !=null and quoteTimeBegin != '' ">
					<![CDATA[and quoteTime >= #{quoteTimeBegin} ]]>
				</if>
				<if test="quoteTimeEnd !=null and quoteTimeEnd != '' ">
					<![CDATA[and quoteTime <= #{quoteTimeEnd} ]]>
				</if>
		      AND price >= 200 
		      AND isFillin = '1' 
		    GROUP BY wangwang,realname) tab) t  
  	</select>
</mapper>