<?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.iamberry.wechat.service.mapper.GiftCardMapper">
	
	<sql id="cardCashInfo">
		CARD_CASH_ID cardCashId,
		CARD_CASH_OPEN_ID cardCashOpenId,
		CARD_CASH_CARD_ID cardCashCardId,
		CARD_CASH_SALES_NICKNAME cardCashSalesNickname,
		CARD_CASH_STATUS CardCashStatus,
		CARD_CASH_CREATE_DATE cardCashCreateDate,
		CARD_CASH_CHECK_DATE cardCashCheckDate,
		CARD_CASH_REMARK cardCashRemark 
	</sql>
	
	<!-- 根据兑换卡密码获取兑换信息 -->
	<select id="get" parameterType="String" resultType="GiftCardInfo">
		SELECT 
			i.*, m.USER_MACHINE_STATUS machineStatus, m.USER_MACHINE_COLOR_NAME machineColor, 
			m.USER_MACHINE_ADDRESS_NAME userMachineAddressName, m.USER_MACHINE_ADDRESS_TEL userMachineAddressTel, 
			m.USER_MACHINE_ADDRESS_INFO userMachineAddressInfo,m.USER_MACHINE_POST_NAME userMachinePostName,
			m.USER_MACHINE_POST_NUM userMachinePostNum 
		FROM
			TB_IAMBERRY_GIFT_CARD_INFO i
		LEFT JOIN
			TB_IAMBERRY_USER_MACHINE m
		ON
			i.card_id = m.USER_MACHINE_CARD_ID
		WHERE
			i.CARD_PASSWORD = #{cardPwd}
	</select>
	
	<!-- 修改订单状态 -->
	<update id="update" parameterType="GiftCardInfo">
		UPDATE 
			TB_IAMBERRY_GIFT_CARD_INFO 
		<set>
			<if test="cardStatus != null">
				CARD_STATUS = 2, 
			</if>
			<if test="cardUseDate != null">
				CARD_USE_DATE = #{cardUseDate}
			</if>
		</set>
		<where>
			CARD_PASSWORD = #{cardPassword}
			<if test="cardStatus != null">
				AND CARD_STATUS = #{cardStatus}
			</if>
		</where>
	</update>
		
	<!-- 批量新增礼品卡信息 -->
	<insert id="insertGiftCardList" parameterType="ArrayList">
		<selectKey resultType="int" keyProperty="cardId" order="AFTER"> 
			SELECT 
			LAST_INSERT_ID() 
		</selectKey> 
		INSERT INTO TB_IAMBERRY_GIFT_CARD_INFO
		(
			CARD_OPEN_ID,CARD_PASSWORD,CARD_STATUS,
			CARD_CREATE_DATE
		) 
		VALUES 
		<foreach collection="list" item="item" index="index" separator="," >
		(
			#{item.cardOpenId},#{item.cardPassword},#{item.cardStatus},NOW()
		)
		</foreach>  
	</insert>
	
	<!-- 根据礼品卡密码查询该密码是否已经存在 -->
	<select id="selectCardByPassward" parameterType="String" resultType="Integer">
		SELECT 
			COUNT(CARD_ID) 
		FROM 
			TB_IAMBERRY_GIFT_CARD_INFO 
		WHERE 
			CARD_PASSWORD = #{cardPassword}
	</select>
	
	<!-- 通过cardId和openId查询礼品卡详情 -->
	<select id="selectCardInfo" parameterType="com.iamberry.wechat.core.entity.giftCard.GiftCardDto" 
		resultType="com.iamberry.wechat.core.entity.giftCard.GiftCardDto">
		SELECT 
			CARD_ID cardId,
			CARD_OPEN_ID cardOpenId,
			CARD_PASSWORD cardPassword,
			CARD_STATUS cardStatus,
			CARD_USE_DATE cardUseDate,
			CARD_CREATE_DATE cardCreateDate 
		FROM 
			TB_IAMBERRY_GIFT_CARD_INFO 
		WHERE 
			CARD_ID = #{cardId} 
		AND 
			CARD_OPEN_ID = #{cardOpenId}
	</select>
	
	<!-- 根据礼品卡旧状态修改新状态 -->
	<update id="updateCardStatus" parameterType="com.iamberry.wechat.core.entity.giftCard.GiftCardDto">
		UPDATE 
			TB_IAMBERRY_GIFT_CARD_INFO 
		<set> 
			<if test="cardStatus != null and cardStatus != ''">
			CARD_STATUS = #{cardStatus},
			</if>
		</set>
		WHERE 
			CARD_ID = #{cardId} 
			<if test="cardOpenId != null and cardOpenId != ''">
				AND CARD_OPEN_ID = #{cardOpenId} 
			</if>
			<if test="cardOldStatus != null and cardOldStatus != ''">
				AND CARD_STATUS = #{cardOldStatus}
			</if>
	</update>
	
	<!-- 添加礼品卡兑现记录 -->
	<insert id="insertCardCash" parameterType="CardCashInfo" 
		useGeneratedKeys="true" keyProperty="cardCashId">
		INSERT INTO TB_IAMBERRY_CARD_CASH
		(
			CARD_CASH_OPEN_ID,CARD_CASH_CARD_ID,CARD_CASH_SALES_NICKNAME,
			CARD_CASH_STATUS,CARD_CASH_CREATE_DATE
		)
		VALUES 
		(
			#{cardCashOpenId},#{cardCashCardId},#{cardCashSalesNickname},
			#{CardCashStatus},NOW()
		)
	</insert>
	
	<!-- 分页查询礼品卡兑现列表 -->
	<select id="selectCardCashList" parameterType="CardCashInfo" resultType="CardCashInfo">
		SELECT 
			<include refid="cardCashInfo" />,
			AGENT_AREA agentArea,
			AGENT_TEL agentTel,
			CARD_PASSWORD cardPassword 
		FROM 
			TB_IAMBERRY_CARD_CASH 
		LEFT JOIN TB_IAMBERRY_AGENT_INFO ON CARD_CASH_OPEN_ID = AGENT_OPEN_ID 
		LEFT JOIN TB_IAMBERRY_GIFT_CARD_INFO ON CARD_CASH_CARD_ID = CARD_ID 
		<where>
			<if test="cardCashSalesNickname != null and cardCashSalesNickname != ''">
				CARD_CASH_SALES_NICKNAME LIKE CONCAT('%',#{cardCashSalesNickname},'%') 
			</if>
			<if test="agentTel != null and agentTel != ''">
				AND AGENT_TEL LIKE CONCAT('%',#{agentTel},'%') 
			</if>
			<if test="cardPassword != null and cardPassword != ''">
				AND CARD_PASSWORD LIKE CONCAT('%',#{cardPassword},'%') 
			</if>
			<if test="CardCashStatus != null and CardCashStatus != ''">
				AND CARD_CASH_STATUS = #{CardCashStatus} 
			</if>
		</where>
		ORDER BY 
			CARD_CASH_STATUS ASC
		LIMIT ${page.recordBegin},${page.pageSize}
	</select>
	
	<!-- 查询礼品卡兑现总数 -->
	<select id="selectCardCashCount" parameterType="CardCashInfo" resultType="Integer">
		SELECT 
			COUNT(CARD_CASH_ID) 
		FROM 
			TB_IAMBERRY_CARD_CASH 
		LEFT JOIN TB_IAMBERRY_AGENT_INFO ON CARD_CASH_OPEN_ID = AGENT_OPEN_ID 
		LEFT JOIN TB_IAMBERRY_GIFT_CARD_INFO ON CARD_CASH_CARD_ID = CARD_ID 
		ORDER BY 
			CARD_CASH_STATUS ASC
	</select>
	
	<!-- 根据状态和id修改兑现记录状态 -->
	<update id="updateCardCashStatus" parameterType="CardCashInfo">
		UPDATE 
			TB_IAMBERRY_CARD_CASH 
		SET 
			CARD_CASH_STATUS = #{CardCashStatus},
			CARD_CASH_REMARK = #{cardCashRemark} 
		WHERE 
			CARD_CASH_ID = #{cardCashId} 
		AND 
			CARD_CASH_STATUS = #{cardCashOldStatus}
	</update>
	
	<!-- 分页查询个人所有礼品卡数据 -->
	<select id="selectCardList" parameterType="com.iamberry.wechat.core.entity.giftCard.GiftCardDto" 
		resultType="com.iamberry.wechat.core.entity.giftCard.GiftCardDto">
		SELECT 
			CARD_ID cardId,
			CARD_OPEN_ID cardOpenId,
			CARD_PASSWORD cardPassword,
			CARD_STATUS cardStatus,
			CARD_USE_DATE cardUseDate,
			CARD_CREATE_DATE cardCreateDate 
		FROM 
			TB_IAMBERRY_GIFT_CARD_INFO 
		WHERE 
			CARD_OPEN_ID = #{cardOpenId} 
		<if test="cardStatus != null and cardStatus != 4">
		AND CARD_STATUS = #{cardStatus} 
		</if>
		ORDER BY CARD_STATUS ASC, CARD_USE_DATE DESC, CARD_ID ASC
		<if test="page!=null and page.recordBegin>0 and page.pageSize>0 ">
	     limit ${page.recordBegin},${page.pageSize}
	    </if>
	     <if test="page!=null and page.recordBegin==0 and page.pageSize>0 ">
	     limit ${page.pageSize}
	    </if>
	</select>
	
	<select id="countCard" parameterType="com.iamberry.wechat.core.entity.giftCard.GiftCardDto" resultType="int">
		SELECT 
			COUNT(CARD_ID)
		FROM 
			TB_IAMBERRY_GIFT_CARD_INFO 
		WHERE 
			CARD_OPEN_ID = #{cardOpenId} 
		<if test="cardStatus != null and cardStatus != 4">
			AND CARD_STATUS = #{cardStatus} 
		</if>
	</select>
</mapper>
