<?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.iformall.mapper.WxCouponActionLogMapper">
	<resultMap id="BaseResultMap" type="com.iformall.domain.po.WxCouponActionLog">
		<id column="id" jdbcType="BIGINT" property="id" />
		<result column="tenant_id" jdbcType="VARCHAR" property="tenantId" />
		<result column="coupon_id" jdbcType="BIGINT" property="couponId" />
		<result column="coupon_order_id" jdbcType="BIGINT" property="couponOrderId" />
		<result column="channel_type" jdbcType="INTEGER" property="channelType" />
		<result column="channel_id" jdbcType="BIGINT" property="channelId" />
		<result column="create_time" jdbcType="TIMESTAMP" property="createTime" />
	</resultMap>
	
	<sql id="allColumns">
	    `id`,`tenant_id`,`coupon_id`,`coupon_order_id`,`channel_type`,`channel_id`,`create_time`
	</sql>

	<sql id="dynamicWhereConditions">
	        where 1 = 1
	    
		<if test=" null != id "> 
		and `id` = #{id} 
       	
		</if>    
	    
		<if test=" null != tenantId "> 
		and `tenant_id`  = #{tenantId}
       	
		</if>    
	    
		<if test=" null != couponId "> 
		and `coupon_id` = #{couponId} 
       	
		</if>    
	    
		<if test=" null != couponOrderId "> 
		and `coupon_order_id` = #{couponOrderId} 
       	
		</if>    
	    
		<if test=" null != channelType "> 
		and `channel_type` = #{channelType} 
       	
		</if>    
	    
		<if test=" null != channelId "> 
		and `channel_id` = #{channelId} 
       	
		</if>    
	    
		<if test=" null != createTime ">
		and `create_time` = #{createTime}
		</if>
		<if test=" null != startTime ">
			and `create_time` &gt;= #{startTime}
		</if>
		<if test=" null != endTime ">
			and `create_time` &lt;= #{endTime}
		</if>

		<if test=" null != ids ">
		 	and id in  
			<foreach collection="ids" index="index" item="idItem" open="(" separator="," close=")">
			#{idItem}
			</foreach>
		</if>		
		<if test=" null != sortColumns"> order by ${sortColumns} </if>
	</sql>
	
	<select id="findList" parameterType="com.iformall.domain.po.WxCouponActionLog" resultMap="BaseResultMap">
		select <include refid="allColumns" /> from wx_coupon_action_log
		<include refid="dynamicWhereConditions" />
	</select>

	<select id="sceneDataMap" resultType="com.iformall.domain.vo.MarkingSceneDataReportVo" parameterType="hashmap">
		select  DATE_FORMAT(create_time,'%m/%d')  as xTime,
			(select Count(*) from wx_coupon_action_log c
				where DATE_FORMAT(create_time,'%m/%d')=xTime
				and c.channel_type=2
				and tenant_id=#{tenantId} ) as parkSendCount,
			(select Count(*) from wx_coupon_action_log c
				where DATE_FORMAT(create_time,'%m/%d')=xTime
				and c.channel_type=3
				and tenant_id=#{tenantId}) as verifySendCount,
			(select Count(*) from wx_coupon_action_log c
				where DATE_FORMAT(create_time,'%m/%d')=xTime
				and c.channel_type=4
				and tenant_id=#{tenantId}) as microPaySendCount,
			(select Count(*) from wx_coupon_action_log c
				where DATE_FORMAT(create_time,'%m/%d')=xTime
				and c.channel_type=5
				and tenant_id=#{tenantId}) as orderSendCount
		  	from wx_coupon_action_log
			where tenant_id=#{tenantId}
			and channel_type in (2,3,4,5)
			and create_time &gt;= #{startTime}
			and create_time &lt;= #{endTime}
			group by xTime
			order by xTime desc
	</select>

	<select id="sceneDataMapJoinCouponOrder" resultType="com.iformall.domain.vo.MarkingSceneDataReportVo" parameterType="hashmap">
		select DATE_FORMAT(create_date,'%m/%d') AS xTime,
			COUNT(wx_coupon_order.id) as tempCount
			from wx_coupon_action_log
			left join wx_coupon_order on wx_coupon_action_log.coupon_order_id=wx_coupon_order.id
			where  wx_coupon_action_log.tenant_id=#{tenantId}
			----- and wx_coupon_order.coupon_order_status = 1 ----
			and wx_coupon_action_log.create_time &gt;= #{startTime}
			and wx_coupon_action_log.create_time &lt;= #{endTime}
			and wx_coupon_action_log.channel_type = #{channelType}
			group by xTime
			order by xTime desc
	</select>

	<select id="sceneDataList" resultType="com.iformall.domain.vo.MarkingSceneDataVo" parameterType="hashmap">
		select  DATE_FORMAT(create_time,'%Y-%m-%d')  as xTime,
			Count(*) as sendCount
			from wx_coupon_action_log
			where tenant_id=#{tenantId}
			and channel_type=#{channelType}
			<if test=" null != startTime ">
				and create_time &gt;= #{startTime}
			</if>
			<if test=" null != endTime ">
				and create_time &lt;= #{endTime}
			</if>
			  group by xTime
			  order by xTime desc
	</select>

	<select id="sceneDataJoinCouponOrderList" resultType="com.iformall.domain.vo.MarkingSceneDataReportVo" parameterType="hashmap">
		select DATE_FORMAT(create_date,'%Y-%m-%d') AS xTime,
				COUNT(wx_coupon_order.id) as tempCount
		from wx_coupon_action_log
		left join wx_coupon_order on wx_coupon_action_log.coupon_order_id=wx_coupon_order.id
		where  wx_coupon_action_log.tenant_id=#{tenantId}
			and wx_coupon_order.coupon_order_status = 1
			<if test=" null != startTime ">
			and wx_coupon_action_log.create_time &gt;= #{startTime}
			</if>
			<if test=" null != endTime ">
			and wx_coupon_action_log.create_time &lt;= #{endTime}
			</if>
			and wx_coupon_action_log.channel_type = #{channelType}
			group by xTime
			order by xTime desc
	</select>


	<select id="getCountByDateLimit" resultType="java.lang.Integer">
		select COUNT(*) FROM wx_coupon_action_log
		where tenant_id = #{tenantId}
		and `channel_type` != 0
		and `channel_type` != 1
		and `create_time` &gt;= #{startTime}
		and `create_time` &lt;= #{endTime}
	</select>

	<select id="getCountByChannelId" resultType="java.lang.Integer">
		select COUNT(*) FROM wx_coupon_action_log
		where tenant_id = #{tenantId}
		and channel_type = #{channelType}
		and channel_id = #{channelId}
	</select>

	<select id="getCountByUser" resultType="java.lang.Integer" parameterType="hashmap">
		select COUNT(*)
		FROM wx_coupon_action_log a, wx_coupon_order o
		WHERE a.coupon_order_id = o.id
		AND a.tenant_id = #{tenantId}
		AND o.c_user_id = #{cUserId}
		AND date(a.create_time) = curdate()
	</select>

	<select id="getCountByUserAndCoupon" resultType="java.lang.Integer" parameterType="hashmap">
		select COUNT(*)
		FROM wx_coupon_action_log a, wx_coupon_order o
		WHERE a.coupon_order_id = o.id
		AND a.tenant_id = #{tenantId}
		AND o.c_user_id = #{cUserId}
		AND a.coupon_id = #{couponId}
		AND date(a.create_time) between date_sub(curdate(),interval #{dayNum} day) and curdate()
	</select>

	<select id="getCountByUserAndCouponAndDate" resultType="java.lang.Integer" parameterType="hashmap">
		select COUNT(*)
		FROM wx_coupon_action_log a, wx_coupon_order o
		WHERE a.coupon_order_id = o.id
		AND a.tenant_id = #{tenantId}
		AND o.c_user_id = #{cUserId}
		AND a.coupon_id = #{couponId}
		AND a.channel_type = #{channelType}
		and a.create_time &gt;= #{startTime}
		and a.create_time &lt;= #{endTime}
	</select>

</mapper>
