<?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">
<!-- 命名，每一个映射对象不一样
	namespace:必须与对应的接口全类名一致
 -->
  <mapper namespace="com.iamberry.wechat.service.mapper.AdminProbationOrderMapper">
  <sql id="allProbationOrder">
		PROBATION_ORDERID  probationOrderid,
		PROBATION_PRODUCT_ID  probationProductId,
		PROBATION_COLOR_ID probationColorId,
		PROBATION_OPENID  probationOpenid,
		PROBATION_YET_AMOUNT  probationYetAmount,
		PROBATION_AMOUNT  probationAmount,
		PROBATION_CREATE_TIME  probationCreateTime,
		PROBATION_STATUS  probationStatus,
		PROBATION_SEND_DATE  probationSendDate,
		PROBATION_ADDRESS_INFO  probationAddressInfo,
		PROBATION_RECEIVE_POSTNUM  probationReceivePostnum,
		PROBATION_RECEIVE_TEL  probationReceiveTel,
		PROBATION_RECEIVE_NAME  probationReceiveName,
		PROBATION_ADMIN_REMARK  probationAdminRemark,
		PROBATION_USER_REMARK  probationUserRemark,
		PROBATION_TRANSACTION_ID  probationTransactionId,
		PROBATION_TRANSACTION_TIME  probationTransactionTime,
		PROBATION_START_TIME  probationStartTime,
		PROBATION_END_TIME  probationEndTime,
		PROBATION_UPDATE_TIME  probationUpdateTime,
		PROBATION_COLOR_TYPE probationColorType,
		PROBATION_SEND_NAME probationSendName,
		PROBATION_SEND_TEL probationSendTel,
		PROBATION_PAUSE_START_TIME probationPauseStartTime,
		PROBATION_PAUSE_END_TIME probationPauseEndTime,
		PROBATION_POST_NUM probationPostNum, 
		PROBATION_POST_FIRM probationPostFirm 
	</sql>
	
	<!-- 分页查询试用订单列表 -->
	<select id="selectProbationList" parameterType="ProbationOrder" resultType="ProbationOrder">
		SELECT 
			<include refid="allProbationOrder" />,P.PLACE_NAME storesName, PP.PLACE_NAME vipName, U.USER_NICKNAME userNickName
		FROM 
			TB_IAMBERRY_PROBATION_SHOP_ORDER O
		<!-- 查询订单对应的用户信息 -->
		LEFT JOIN 
			TB_IAMBERRY_USER_USERINFO U 
		ON 
			O.PROBATION_OPENID = U.USER_OPENID
			<!-- 根据用户信息中的dealers查询对应的关系 -->
		LEFT JOIN 
			TB_IAMBERRY_DEALERS D 
		ON 
			U.USER_DEALERS = D.DEALER_ID
			<!-- 查询用户关系对应的商户 -->
		LEFT JOIN 
			TB_IAMBERRY_PLACE_INFO P 
		ON 
			D.DEALER_ID = P.PLACE_PIDS
			<!-- 查询商户的上级，也就是门店 -->
		LEFT JOIN 
			TB_IAMBERRY_PLACE_INFO PP 
		ON
			P.PLACE_PID = PP.PLACE_ID
		<where>
			<if test="probationStatus != null and probationStatus != -1">
				PROBATION_STATUS = #{probationStatus} 
			</if>
			<if test="probationOrderid != null and probationOrderid != ''">
				AND PROBATION_ORDERID LIKE CONCAT('%',#{probationOrderid},'%')
			</if>
			<if test="beginDate != null">
				<![CDATA[AND DATE_FORMAT(PROBATION_CREATE_TIME, '%Y-%m-%d') >= ]]>DATE_FORMAT(#{beginDate},'%Y-%m-%d') 
			</if>
			<if test="endDate != null">
				<![CDATA[AND DATE_FORMAT(PROBATION_CREATE_TIME, '%Y-%m-%d') <= ]]>DATE_FORMAT(#{endDate},'%Y-%m-%d') 
			</if>
		</where>
		ORDER BY PROBATION_ORDERID DESC 
		LIMIT ${page.recordBegin},${page.pageSize}
	</select>
	
	<!-- 查询试用订单列表的数量 -->
	<select id="selectProbationCount" parameterType="ProbationOrder" resultType="Integer">
		SELECT 
			COUNT(PROBATION_ORDERID) 
		FROM 
			TB_IAMBERRY_PROBATION_SHOP_ORDER 
		<where>
			<if test="probationStatus != null and probationStatus != -1">
				PROBATION_STATUS = #{probationStatus} 
			</if>
			<if test="probationOrderid != null and probationOrderid != ''">
				AND PROBATION_ORDERID LIKE CONCAT('%',#{probationOrderid},'%')
			</if>
		</where>
		ORDER BY PROBATION_ORDERID DESC 
	</select>
	
	<!-- 查询试用订单详细信息 -->
	<select id="getProbationOrderByOrderId" parameterType="ProbationOrder" resultType="ProbationOrder">
		SELECT 
			<include refid="allProbationOrder" />, PRODUCT_NAME productName, COLOR_DISCOUNT productDiscount, 
			COLOR_PRICE productPrice,PRODUCT_INTRODUCE_IMG productIntroduceImg, USER_NICKNAME userNickName, 
			PRODUCT_TYPE probationProductType, COLOR_69CODE productRemark 
		FROM 
			TB_IAMBERRY_PROBATION_SHOP_ORDER LEFT JOIN TB_IAMBERRY_PRODUCT_INFO 
			ON PROBATION_PRODUCT_ID = PRODUCT_ID 
			LEFT JOIN TB_IAMBERRY_USER_USERINFO ON PROBATION_OPENID = USER_OPENID 
			LEFT JOIN TB_IAMBERRY_PRODUCT_COLOR ON PROBATION_COLOR_ID = COLOR_ID 
		WHERE 
			PROBATION_ORDERID = #{probationOrderid}
	</select>
	
	<!-- 根据原状态和订单id修改该订单状态 -->
	<update id="updateProbationOrderStatus" parameterType="ProbationOrder">
		UPDATE 
			TB_IAMBERRY_PROBATION_SHOP_ORDER 
		SET 
			PROBATION_STATUS = #{probationStatus},
			<if test="probationTransactionId != null and probationTransactionId != ''">
				PROBATION_TRANSACTION_ID = #{probationTransactionId}, 
			</if>
			<if test="probationTransactionTime != null and probationTransactionTime != ''">
				PROBATION_TRANSACTION_TIME = #{probationTransactionTime}, 
			</if> 
			PROBATION_UPDATE_TIME = NOW() 
		WHERE 
			PROBATION_ORDERID = #{probationOrderid} 
			<if test="probationOldStatus != null and probationOldStatus != ''">
				AND 
					PROBATION_STATUS = #{probationOldStatus} 
			</if>
	</update>
	
	<!-- 修改订单的备注信息 -->
	<update id="updateAdminRemark" parameterType="ProbationOrder" >
		UPDATE 
			TB_IAMBERRY_PROBATION_SHOP_ORDER 
		<set>
			<if test="probationColorType != null and probationColorType != ''">
				PROBATION_COLOR_TYPE = #{probationColorType}, 
			</if>
			<if test="probationAdminRemark != null and probationAdminRemark != ''">
				PROBATION_ADMIN_REMARK = #{probationAdminRemark} 
			</if>
		</set>
		WHERE 
			PROBATION_ORDERID = #{probationOrderid}
	</update>
	
	<!-- 修改了派送员信息 -->
	<update id="updateSendName" parameterType="ProbationOrder">
		UPDATE 
			TB_IAMBERRY_PROBATION_SHOP_ORDER 
			<set>
				<if test="probationSendName != null and probationSendName != ''">
					PROBATION_SEND_NAME = #{probationSendName},
				</if>
				<if test="probationSendTel != null and probationSendTel != ''">
					PROBATION_SEND_TEL = #{probationSendTel}, 
				</if>
				<if test="probationPostFirm != null and probationPostFirm != ''">
					PROBATION_POST_FIRM = #{probationPostFirm}, 
				</if>
				<if test="probationPostNum != null and probationPostNum != ''">
					PROBATION_POST_NUM = #{probationPostNum} 
				</if>
			</set>
		WHERE 
			PROBATION_ORDERID = #{probationOrderid}
	</update>
	
	<!-- 修改试用结束时间 -->
	<update id="updateProbationEndTime" parameterType="ProbationOrder">
		UPDATE 
			TB_IAMBERRY_PROBATION_SHOP_ORDER 
		SET
			PROBATION_END_TIME = #{probationEndTime} 
		WHERE 
			PROBATION_ORDERID = #{probationOrderid}
	</update>
	
	<!-- 修改订单备注 -->
	<update id="updateUserRemark" parameterType="ProbationOrder">
		UPDATE 
			TB_IAMBERRY_PROBATION_SHOP_ORDER 
		SET
			 PROBATION_USER_REMARK = #{probationUserRemark} 
		WHERE 
			PROBATION_ORDERID = #{probationOrderid}
	</update>
	
	<!-- 修改暂停开始时间 -->
	<update id="updatePauseStartTime" parameterType="ProbationOrder">
		UPDATE 
			TB_IAMBERRY_PROBATION_SHOP_ORDER 
		SET 
			PROBATION_STATUS = #{probationStatus},
			PROBATION_PAUSE_START_TIME = NOW(),
			PROBATION_UPDATE_TIME = NOW() 
		WHERE 
			PROBATION_ORDERID = #{probationOrderid} 
			<if test="probationOldStatus != null and probationOldStatus != ''">
				AND 
					PROBATION_STATUS = #{probationOldStatus} 
			</if>
	</update>
	
	<!-- 修改派送货物时间 -->
	<update id="updateSendDate" parameterType="ProbationOrder">
		UPDATE 
			TB_IAMBERRY_PROBATION_SHOP_ORDER 
		SET 
			PROBATION_STATUS = #{probationStatus},
			PROBATION_START_TIME = NOW(),
			PROBATION_END_TIME = #{probationEndTime},
			PROBATION_SEND_DATE = NOW(),
			PROBATION_UPDATE_TIME = NOW() 
		WHERE 
			PROBATION_ORDERID = #{probationOrderid} 
			<if test="probationOldStatus != null and probationOldStatus != ''">
				AND 
					PROBATION_STATUS = #{probationOldStatus} 
			</if>
	</update>
	
	<!-- 修改暂停结束时间 -->
	<update id="updatePauseEndTime" parameterType="ProbationOrder">
		UPDATE 
			TB_IAMBERRY_PROBATION_SHOP_ORDER 
		SET 
			PROBATION_STATUS = #{probationStatus},
			PROBATION_END_TIME = #{probationEndTime},
			PROBATION_PAUSE_TOTAL_LENGTH = (PROBATION_PAUSE_TOTAL_LENGTH + #{probationPauseTotalLength}), 
			PROBATION_PAUSE_END_TIME = NOW(),
			PROBATION_UPDATE_TIME = NOW() 
		WHERE 
			PROBATION_ORDERID = #{probationOrderid} 
			<if test="probationOldStatus != null and probationOldStatus != ''">
				AND 
					PROBATION_STATUS = #{probationOldStatus} 
			</if>
	</update>
  
  </mapper>