<?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.AdminOrderMapper">
	<!-- 后台订单返利信息查询 -->
	<select id="selectOrderRebackByOrderId" parameterType="String" resultType="OrderRebackMoney">
		
		SELECT ( SELECT place_openid FROM tb_iamberry_place_info b WHERE b.place_id = a.logs_place_id
		) name ,
		 (SELECT place_username FROM tb_iamberry_place_info b WHERE b.place_id = a.logs_place_id
		) account ,
		(
		SELECT role_name FROM tb_iamberry_place_role WHERE role_id = (
		SELECT Place_role_id FROM tb_iamberry_place_info b WHERE b.place_id = a.logs_place_id)
		) role,
		logs_num money,logs_create_date TIME,logs_introduction remark FROM tb_iamberry_place_logs a WHERE logs_introduction LIKE  CONCAT('%',#{orderId},'%') 
		UNION ALL
		SELECT 
		(SELECT b.user_openid
		 FROM   tb_iamberry_user_userinfo b WHERE b.user_openid = a.cash_logs_openid
		) name,(SELECT b.user_nickname
		 FROM   tb_iamberry_user_userinfo b WHERE b.user_openid = a.cash_logs_openid
		)  account
		,'微代理'  role
		,cash_logs_num money,cash_logs_create_date TIME,cash_logs_introduction remark FROM tb_iamberry_user_cash_logs  a WHERE cash_logs_orderid =#{orderId} 
		AND cash_logs_type = 1
		AND cash_logs_res_type = 4 
		
	</select>

	<!--查询未发货数量-->
	<select id="getNotSendCount" parameterType="String" resultType="Integer">
		SELECT
		COUNT(*)
		FROM tb_iamberry_shop_sales_order
		WHERE  sales_openid=#{openid}
		AND sales_status = 2
	</select>

	<!-- 后台订单列表查询 -->
	<select id="selectShopOrder" parameterType="Order" resultType="Order">
		SELECT O.SALES_ORDERID salesOrderid, O.SALES_ADDRESS_NAME salesAddressName,
		U.USER_NICKNAME userNickname, O.SALES_ADDRESS_TEL salesAddressTel,
		O.SALES_AMOUNT salesAmount, O.SALES_RATE_AMOUNT salesRateAmount,
		O.SALES_INTEGRAL_NUM salesIntegralNum, O.SALES_YET_AMOUNT salesYetAmount,
		O.SALES_STATUS salesStatus, O.SALES_CREATE_DATE salesCreateDate,
		O.SALES_POST_NUM salesPostNum,O.SALES_POST_FIRM salesPostFirm,
		O.SALES_ADMIN_REMARK salesAdminRemark, O.SALES_COLOR_TYPE salesColorType,
		O.SALES_USER_REMARK salesUserRemark,O.SALES_IS_RFUND salesIsRfund,
		P.PLACE_NAME storesName,
		PP.PLACE_NAME vipName,
		PP.PLACE_ROLE_ID vipRole,
		PP.PLACE_OPENID placeOpenID,
		U.USER_RES_TYPE userResource,
		o.sales_is_customized salesIsCustomized,
		o.sales_transportation_type salesTransportationType,
		o.sales_channel_id salesChannelId,
		o.sales_channel_type salesChannelType,
		o.sales_channel_address salesChannelAddress,
		chan.sales_channel_name salesChannelName,
		(SELECT IFNULL(SUM(reback_money),0) FROM tb_iamberry_reback WHERE reback_orderid = O.SALES_ORDERID and Reback_status  = 2) placeId
		FROM
		TB_IAMBERRY_SHOP_SALES_ORDER O
		LEFT JOIN
		TB_IAMBERRY_USER_USERINFO U ON O.SALES_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
		LEFT JOIN (
			SELECT
			mc.main_channel_id AS sales_channel_id,
			mc.main_channel_name AS sales_channel_name,
			1 AS sales_channel_type
			FROM
			tb_iamberry_main_channel mc
			UNION ALL
			SELECT
			cc.child_channel_id AS sales_channel_id,
			cc.child_channel_name AS sales_channel_name,
			2 AS sales_channel_type
			FROM
			tb_iamberry_child_channel cc
		) chan ON O.sales_channel_type = chan.sales_channel_type AND O.sales_channel_id = chan.sales_channel_id
		WHERE
		O.SALES_ORDER_TYPE = 1
		AND  O.sales_transportation_type != 2
		<if test="salesStatus != null and salesStatus != -1">
			AND O.SALES_STATUS = #{salesStatus}
		</if>
		<if test="salesOrderid != null and salesOrderid!=''">
			AND O.SALES_ORDERID = #{salesOrderid}
		</if>
		ORDER BY O.SALES_CREATE_DATE DESC
		LIMIT ${page.recordBegin},${page.pageSize}
	</select>

	<!-- 根据订单号，获取订单所属人 -->
	<select id="selectSalesOpenIdByOrderId" parameterType="String" resultType="String">
		SELECT
			SALES_OPENID
		FROM
			TB_IAMBERRY_SHOP_SALES_ORDER
		WHERE
			SALES_ORDERID = #{orderId}
		LIMIT
			0, 1
	</select>

	<select id="selectShopOrderByOrderId" parameterType="Order" resultType="Order">
		SELECT O.SALES_ORDERID salesOrderid, O.SALES_ADDRESS_NAME salesAddressName,
		U.USER_NICKNAME userNickname, O.SALES_ADDRESS_TEL salesAddressTel,
		O.SALES_AMOUNT salesAmount, O.SALES_RATE_AMOUNT salesRateAmount,
		O.SALES_INTEGRAL_NUM salesIntegralNum, O.SALES_YET_AMOUNT salesYetAmount,
		O.SALES_STATUS salesStatus, O.SALES_CREATE_DATE salesCreateDate,
		O.SALES_ADMIN_REMARK salesAdminRemark, O.SALES_COLOR_TYPE salesColorType,
		o.sales_post_num salesPostNum,o.sales_post_firm salesPostFirm,
		O.SALES_USER_REMARK salesUserRemark, O.SALES_IS_RFUND salesIsRfund
		FROM
		TB_IAMBERRY_SHOP_SALES_ORDER O LEFT JOIN TB_IAMBERRY_USER_USERINFO U
		ON O.SALES_OPENID = U.USER_OPENID
		<where>
				O.sales_transportation_type != 2
			<if test="salesStatus != null and salesStatus != -1">
				AND O.SALES_STATUS = #{salesStatus}
			</if>
			<if test="salesOrderid != null and salesOrderid!=''">
				AND O.SALES_ORDERID LIKE CONCAT('%',#{salesOrderid},'%')
			</if>
			<if test="salesAddressName != null and salesAddressName != ''">
				AND O.SALES_ADDRESS_NAME LIKE CONCAT('%',#{salesAddressName},'%')
			</if>
			<if test="salesAddressTel != null and salesAddressTel != ''">
				AND O.SALES_ADDRESS_TEL LIKE CONCAT('%',#{salesAddressTel},'%')
			</if>
			<if test="salesAddressInfo != null and salesAddressInfo != ''">
				AND O.SALES_ADDRESS_INFO LIKE CONCAT('%',#{salesAddressInfo},'%')
			</if>
			<if test="beginDate != null">
				<![CDATA[AND DATE_FORMAT(O.SALES_CREATE_DATE, '%Y-%m-%d') >= ]]>DATE_FORMAT(#{beginDate},'%Y-%m-%d')
			</if>
			<if test="endDate != null">
				<![CDATA[AND DATE_FORMAT(O.SALES_CREATE_DATE, '%Y-%m-%d') <= ]]>DATE_FORMAT(#{endDate},'%Y-%m-%d')
			</if>
		</where>
		ORDER BY O.SALES_CREATE_DATE DESC
		LIMIT ${page.recordBegin},${page.pageSize}
	</select>

	<!-- 根据条件获取查询数量 -->
	<select id="selectShopOrderCount" parameterType="Order" resultType="int">
		SELECT
		COUNT(O.SALES_ORDERID)
		FROM
		TB_IAMBERRY_SHOP_SALES_ORDER O LEFT JOIN TB_IAMBERRY_USER_USERINFO U
		ON O.SALES_OPENID = U.USER_OPENID
		WHERE
		O.SALES_ORDER_TYPE = 1
		AND O.sales_transportation_type != 2
		<if test="salesStatus != null and salesStatus != -1">
			AND O.SALES_STATUS = #{salesStatus}
		</if>
		<if test="salesOrderid != null and salesOrderid!=''">
			AND O.SALES_ORDERID LIKE CONCAT('%',#{salesOrderid},'%')
		</if>
		<if test="salesAddressName != null and salesAddressName != ''">
			AND O.SALES_ADDRESS_NAME LIKE CONCAT('%',#{salesAddressName},'%')
		</if>
		<if test="salesAddressTel != null and salesAddressTel != ''">
			AND O.SALES_ADDRESS_TEL LIKE CONCAT('%',#{salesAddressTel},'%')
		</if>
		<if test="salesAddressInfo != null and salesAddressInfo != ''">
			AND O.SALES_ADDRESS_INFO LIKE CONCAT('%',#{salesAddressInfo},'%')
		</if>
		<if test="beginDate != null">
			<![CDATA[AND DATE_FORMAT(O.SALES_CREATE_DATE, '%Y-%m-%d') >= ]]>DATE_FORMAT(#{beginDate},'%Y-%m-%d')
		</if>
		<if test="endDate != null">
			<![CDATA[AND DATE_FORMAT(O.SALES_CREATE_DATE, '%Y-%m-%d') <= ]]>DATE_FORMAT(#{endDate},'%Y-%m-%d')
		</if>
	</select>

	<!-- 根据orderId查询订单信息 -->
	<select id="getShopOrderByOrderId" parameterType="String" resultType="Order">
		SELECT O.SALES_ORDERID salesOrderid, U.USER_NICKNAME userNickname, O.SALES_STATUS salesStatus, 
			O.SALES_YET_AMOUNT salesYetAmount, O.SALES_INTEGRAL_NUM salesIntegralNum, O.SALES_RATE_AMOUNT salesRateAmount, 
			O.SALES_POST_FIRM salesPostFirm, O.SALES_POST_NUM salesPostNum, O.SALES_CREATE_DATE salesCreateDate, 
			O.SALES_ADDRESS_POSTNUM salesAddressPostnum,  O.SALES_REWARD_INTEGRAL_NUM salesRewardIntegralNum, 
			O.SALES_ADDRESS_NAME salesAddressName, O.SALES_ADDRESS_TEL salesAddressTel, O.SALES_SEND_DATE salesSendDate, 
			O.SALES_USER_REMARK salesUserRemark, O.SALES_COLOR_TYPE salesColorType, O.SALES_ADMIN_REMARK salesAdminRemark, 
			O.SALES_RETURN_DES salesReturnDes, O.SALES_ADDRESS_INFO salesAddressInfo, O.SALES_RETURN_DATE salesReturnDate,
			O.SALES_AMOUNT salesAmount, O.SALES_TRANSACTION_DATE salesTransactionDate, O.SALES_OPENID salesOpenid, 
			O.SALES_COUPON_RECEIVE_ID couponId, O.SALES_BALANCE_AMOUNT balancePayAmount, O.SALES_IS_RFUND salesIsRfund, O.sales_postage salesPostage
		FROM 
			TB_IAMBERRY_SHOP_SALES_ORDER O LEFT JOIN TB_IAMBERRY_USER_USERINFO U 
			ON O.SALES_OPENID = U.USER_OPENID 
		WHERE 
			O.SALES_ORDERID = #{0} 
		AND 
			O.SALES_ORDER_TYPE = 1 
	</select>

	<!-- 根据orderId获取订单项数据 -->
	<select id="getShopOrderItemByOrderId" parameterType="String" resultType="OrderItem">
		SELECT
			I.item_id itemId,
			I.item_sales_orderid itemSalesOrderid,
			I.ITEM_PRODUCT_NAME itemProductName,
			I.ITEM_PRODUCT_PIC itemProductPic,
			I.ITEM_PRODUCT_PRICE itemProductPrice,
			I.ITEM_PRODUCT_DISCOUNT itemProductDiscount,
			I.ITEM_NUM itemNum,
			I.ITEM_TOTAL itemTotal,
			I.item_color_id itemColorId,
			I.item_product_id itemProductId,
			C.COLOR_NAME productColor,
			C.COLOR_69CODE produectRemark,
			I.ITEM_PRODUCT_TYPE itemProductType
		FROM 
			TB_IAMBERRY_SHOP_ORDER_ITEM I LEFT JOIN TB_IAMBERRY_PRODUCT_INFO P 
			ON I.ITEM_PRODUCT_ID = P.PRODUCT_ID 
			LEFT JOIN TB_IAMBERRY_PRODUCT_COLOR C ON I.ITEM_COLOR_ID = C.COLOR_ID 
		WHERE 
			ITEM_SALES_ORDERID = #{0} 
		ORDER BY ITEM_ID DESC
	</select>

	<!-- 根据orderId查询订单状态 -->
	<select id="selectOrderStatusByOrderId" parameterType="String" resultType="Order">
		SELECT 
			SALES_STATUS salesStatus, SALES_PROXY_REWARD salesProxyReward, 
			SALES_POST_FIRM salesPostFirm, SALES_POST_NUM salesPostNum ,
			sales_openid salesOpenid, SALES_IS_RFUND salesIsRfund,
			SALES_YET_AMOUNT salesYetAmount, sales_return_des salesReturnDes,
			SALES_ADDRESS_INFO salesAddressInfo
		FROM 
			TB_IAMBERRY_SHOP_SALES_ORDER
		WHERE 
			SALES_ORDERID = #{salesOrderid}
	</select>

	<!-- 根据orderId修改订单状态 -->
	<update id="updateOrderStatusByOrderId" parameterType="Order">
		UPDATE
		TB_IAMBERRY_SHOP_SALES_ORDER
		SET
		<if test="salesChangeOrderid != null and salesChangeOrderid != ''">
			SALES_CHANGE_ORDERID = #{salesChangeOrderid},
		</if>
		<if test="salesAuditRemark != null and salesAuditRemark != ''">
			SALES_AUDIT_REMARK = #{salesAuditRemark},
		</if>
		SALES_STATUS = #{salesStatus}
		WHERE
		SALES_ORDERID = #{salesOrderid}
		AND
		SALES_STATUS = #{oldStatus}
	</update>

	<!-- 根据orderId修改订单状态 -->
	<update id="updateOrderStatusByOrderIdAndToStatus" parameterType="Order">
		UPDATE 
			TB_IAMBERRY_SHOP_SALES_ORDER 
		SET 
			SALES_STATUS = #{salesStatus} 
		WHERE 
			SALES_ORDERID = #{salesOrderid} AND SALES_STATUS = #{salesOpenid}
	</update>

	<!-- 根据orderid添加发货信息并修改状态 -->
	<update id="deliverOrderByOrderId" parameterType="Order">
		UPDATE 
			TB_IAMBERRY_SHOP_SALES_ORDER 
		SET 
			SALES_STATUS = #{salesStatus}, 
			SALES_POST_NUM = #{salesPostNum}, 
			SALES_POST_FIRM = #{salesPostFirm}, 
			SALES_SEND_DATE = NOW() 
		WHERE 
			SALES_ORDERID = #{salesOrderid}
	</update>

	<!-- 根据orderid添加发货信息并修改状态 -->
	<update id="updateOrderDeliver" parameterType="Order">
		UPDATE 
			TB_IAMBERRY_SHOP_SALES_ORDER 
		SET 
			SALES_STATUS = #{salesStatus}, 
			SALES_POST_NUM = #{salesPostNum}, 
			SALES_POST_FIRM = #{salesPostFirm}, 
			SALES_SEND_DATE = NOW() 
		WHERE 
			SALES_ORDERID = #{salesOrderid} 
		AND 
			SALES_STATUS = #{oldStatus}
	</update>

	<!-- 修改订单的备注信息 -->
	<update id="updateOrderAdminRemarkByOrderId" parameterType="Order" >
		UPDATE
		TB_IAMBERRY_SHOP_SALES_ORDER
		<set>
			<if test="salesColorType != null and salesColorType != ''">
				SALES_COLOR_TYPE = #{salesColorType},
			</if>
			<if test="salesAdminRemark != null and salesAdminRemark != ''">
				SALES_ADMIN_REMARK = #{salesAdminRemark}
			</if>
		</set>
		WHERE
		SALES_ORDERID = #{salesOrderid}
	</update>

	<!-- 修改订单退款状态 -->
	<update id="updateOrderRfund" parameterType="Order" >
		UPDATE
		TB_IAMBERRY_SHOP_SALES_ORDER
		<set>
			<if test="salesIsRfund != null and salesIsRfund != ''">
				SALES_IS_RFUND = #{salesIsRfund}
			</if>
		</set>
		WHERE
		SALES_ORDERID = #{salesOrderid}
	</update>

	<!-- 查询订单中没有奖励积分的数据 -->
	<select id="selectOrderByIntegral" resultType="Order" resultMap="orderMap">
		SELECT 
			O.SALES_ORDERID, 
			O.SALES_OPENID, 
			O.SALES_AMOUNT, 
			O.SALES_REWARD_INTEGRAL_NUM  
		FROM 
			TB_IAMBERRY_SHOP_SALES_ORDER O 
		WHERE 
			O.SALES_PROXY_REWARD = 1 
		AND 
			<![CDATA[ TIMESTAMPDIFF(SECOND,O.SALES_SEND_DATE,NOW()) >= #{deadline} ]]>
		AND 
			O.SALES_STATUS = 5 
		ORDER BY O.SALES_SEND_DATE ASC 
		LIMIT ${page.recordBegin},${page.pageSize}
	</select>

	<resultMap type="com.iamberry.wechat.core.entity.order.Order" id="orderMap">
		<id column="SALES_ORDERID" property="salesOrderid"/>
		<result column="SALES_OPENID" property="salesOpenid"/>
		<result column="SALES_AMOUNT" property="salesAmount"/>
		<result column="SALES_REWARD_INTEGRAL_NUM" property="salesRewardIntegralNum"/>
		<collection property="orderItemList" column="SALES_ORDERID" ofType="com.iamberry.wechat.core.entity.order.OrderItem" select="selectOrderItem"/>
	</resultMap>

	<!-- 查询订单的订单项 -->
	<select id="selectOrderItem" parameterType="String" resultType="com.iamberry.wechat.core.entity.order.OrderItem">
		SELECT I.ITEM_ID itemId, 
			I.ITEM_SALES_ORDERID itemSalesOrderid, I.ITEM_PRODUCT_NAME itemProductName, 
			I.ITEM_NUM itemNum, I.ITEM_PRODUCT_TYPE itemProductType 
		FROM 
			TB_IAMBERRY_SHOP_ORDER_ITEM I 
		WHERE 
			I.ITEM_SALES_ORDERID = #{itemSalesOrderid} 
		ORDER BY I.ITEM_ID DESC
	</select>


	<!-- 通过openid查询用户购买商品的渠道 -->
	<select id="getUserByOpenId" parameterType="String" resultType="Member">
		SELECT 
			USER_RES_TYPE userResType,
			USER_ID userId, 
			USER_OPENID userOpenid,
			USER_INTEGRAL userIntegral,
			USER_INCOME userIncome,
			USER_DEALERS userDealers,
			USER_TEMPLATE_ID templateId,
			USER_SURPLUS_INTEGRAL userSurplusIntegral 
		FROM 
			TB_IAMBERRY_USER_USERINFO 
		WHERE 
			USER_OPENID = #{openId}
	</select>

	<!-- 根据推荐人id查询推荐人信息 -->
	<select id="getUserByUserRefereeid" parameterType="Integer" resultType="Member">
		SELECT 
			USER_OPENID userOpenid,
			USER_INTEGRAL userIntegral,
			USER_INCOME userIncome,
			USER_SURPLUS_INTEGRAL userSurplusIntegral 
		FROM 
			TB_IAMBERRY_USER_USERINFO 
		WHERE 
			USER_ID = #{userId}
	</select>

	<!-- 根据userid修改会员表信息 -->
	<update id="updateUserByUserId" parameterType="Member">
		UPDATE
		TB_IAMBERRY_USER_USERINFO
		SET
		USER_INCOME =(USER_INCOME + #{userIncome})
		<where>
			<if test="userId != null and userId != ''">
				USER_ID = #{userId}
			</if>
			<if test="userOpenid != null and userOpenid != ''">
				AND USER_OPENID = #{userOpenid}
			</if>
		</where>
	</update>

	<update id="updatePlaceInfo" parameterType="PlaceInfo">
	     update 
	     	tb_iamberry_place_info 
	     SET 
	          PLACE_AMOUNT =(PLACE_AMOUNT + #{amount}) 
	      WHERE 
	     	PLACE_ID = #{id}
	      AND
	      	<![CDATA[PLACE_AMOUNT + #{amount} >= 0]]>
	</update>

	<select id="getPlaceInfoByPlaceId" parameterType="integer" resultType="PlaceInfo">
		SELECT 
			PLACE_ID id,
			PLACE_SUPPER_ID supperId,
			PLACE_NAME  name,
			PLACE_ADDR   addr,
			PLACE_USERNAME  userName,
			PLACE_TEL  tel,
			PLACE_AMOUNT  amount,
			PLACE_CREATE_DATE  createDate,
			PLACE_STATUS  status,
			PLACE_OPENID  openid,
			PLACE_ROLE_ID roleId,
			PLACE_PID  placePid ,
			PLACE_INIT_CODE  initCode 
        FROM tb_iamberry_place_info WHERE place_id = #{appid}
	</select>

	<!-- 根据id查询系统规则表信息 -->
	<select id="getReluByRid" parameterType="Integer" resultType="ShopSystemRule">
		SELECT R_NUM ruleNum,r_desc ruleDesc, R_TYPE ruleType FROM TB_IAMBERRY_ADMIN_RELU WHERE R_ID = #{rId}
	</select>

	<!-- 根据类型查询系统推责表信息 -->
	<select id="getReluByRtype" parameterType="Integer" resultType="ShopSystemRule">
		SELECT R_NUM ruleNum FROM TB_IAMBERRY_ADMIN_RELU WHERE R_TYPE = #{ruleType} 
	</select>

	<!-- 查询订单表中有多少是可以奖励积分的单的数量 -->
	<select id="getOrderNum"  resultType="Integer" parameterType="Order">
		SELECT 
			COUNT(SALES_ORDERID) 
		FROM 
			TB_IAMBERRY_SHOP_SALES_ORDER O 
		WHERE 
			O.SALES_PROXY_REWARD = 1 
		AND 
			<![CDATA[ TIMESTAMPDIFF(SECOND,O.SALES_SEND_DATE,NOW()) >= #{deadline} ]]> 
		AND 
			O.SALES_STATUS = 5 
		ORDER BY O.SALES_SEND_DATE ASC 
	</select>

	<!-- 对客户进行积分奖励 -->
	<update id="updateOrderIntegral" parameterType="String">
		UPDATE 
			TB_IAMBERRY_SHOP_SALES_ORDER 
		SET 
			SALES_PROXY_REWARD = 2, 
			SALES_STATUS = 12 
		WHERE 
			SALES_ORDERID = #{0}
	</update>

	<!-- 根据规定的订单关闭时间查询大于该时间且是待支付的订单 -->
	<select id="getOrderByCreateTime" resultType="Order" parameterType="Order">
		SELECT 
			T.SALES_ORDERID salesOrderid,T.SALES_COUPON_RECEIVE_ID couponId,T.SALES_OPENID salesOpenid 
			
		FROM 
			TB_IAMBERRY_SHOP_SALES_ORDER T 
		WHERE 
			T.SALES_STATUS = 1 
		AND 
			<![CDATA[ TIMESTAMPDIFF(SECOND,T.SALES_CREATE_DATE,NOW()) >= #{deadline} ]]>
	</select>

	<!-- 根据规定的订单关闭时间查询大于该时间且是待支付的订单 -->
	<select id="getOrderByCreateTimeAndFiveMinutes" resultType="Order" parameterType="Order">
		
			SELECT 
				T.SALES_ORDERID salesOrderid,T.SALES_OPENID salesOpenid ,
				T.SALES_CREATE_DATE salesCreateDate
			FROM 
				TB_IAMBERRY_SHOP_SALES_ORDER T 
			WHERE 
				T.SALES_STATUS = 1 
			AND 
				<![CDATA[  #{deadline}-TIMESTAMPDIFF(SECOND,T.SALES_CREATE_DATE,NOW()) <= 900 ]]>
	</select>


	<!-- 根据orderId修改订单状态，关闭订单 -->
	<update id="updateOrderByOrderId" parameterType="java.util.List">
		UPDATE
		TB_IAMBERRY_SHOP_SALES_ORDER
		SET
		SALES_STATUS = 0
		WHERE
		SALES_ORDERID  IN (
		<foreach collection="list" item="item" separator=",">
			#{item}
		</foreach>)
	</update>


	<!-- 获取某个订单的所有产品的总个数 -->
	<select id="getItemsNumByOrderId"  parameterType="string" resultType="com.iamberry.wechat.core.entity.order.OrderItem">
    select 
    	sum(g.item_num) itemNum,
    	c.color_69code produectRemark 
    from 
    	tb_iamberry_shop_order_item g  LEFT JOIN tb_iamberry_product_color c on g.item_color_id = c.color_id 
    where 
		g.item_sales_orderid=#{id} 
	GROUP BY 
		c.color_69code
  </select>

	<!-- 查询状态为待发货的所有订单号 -->
	<select id="getOrderList" resultType="Order" parameterType="Order">
  	SELECT 
  		SALES_ORDERID salesOrderid 
  	FROM 
  		TB_IAMBERRY_SHOP_SALES_ORDER 
  	WHERE 
  		SALES_STATUS = #{salesStatus} 
  </select>

	<!-- 批量修改订单状态 -->
	<update id="updateOrderListStatus" parameterType="java.util.List">
		<foreach collection="list" item="item" index="index" separator=";" open="" close="">
			UPDATE
			TB_IAMBERRY_SHOP_SALES_ORDER
			SET
			SALES_STATUS = 5,
			SALES_POST_NUM = #{item.salesPostNum,jdbcType=VARCHAR},
			SALES_POST_FIRM = #{item.salesPostFirm,jdbcType=VARCHAR},
			SALES_SEND_DATE = #{item.salesSendDate,javaType=DATE}
			WHERE
			SALES_ORDERID  =  #{item.salesOrderid,jdbcType=VARCHAR}
		</foreach>
	</update>


	<!-- 获取销售数量 -->
	<select id="getSalesVolumes" parameterType="Map" resultType="java.lang.Integer" >
		SELECT ifnull(SUM(TISOI.item_num),0)
		FROM tb_iamberry_shop_order_item TISOI
		LEFT JOIN tb_iamberry_shop_sales_order TISSO ON TISOI.item_sales_orderid = TISSO.sales_orderid
		where TISSO.sales_openid in (
		SELECT TIUU.user_openid
		FROM tb_iamberry_user_userinfo TIUU
		WHERE TIUU.user_dealers = #{userDealers}
		)
		AND TISOI.item_product_type IN
		<foreach collection="itemProductType" item="item" open="(" separator="," close=")">
			#{item}
		</foreach>
		AND TISSO.sales_status in (2,5,12)
	</select>

	<!-- 获取个人已购买的机器台数 -->
	<select id="getPersonalPurchase" parameterType="Map" resultType="java.lang.Integer" >
		SELECT ifnull(SUM(TISOI.item_num),0)
		FROM tb_iamberry_shop_order_item TISOI
		LEFT JOIN tb_iamberry_shop_sales_order TISSO ON TISOI.item_sales_orderid = TISSO.sales_orderid
		where TISSO.sales_openid = #{userOpenid}
		AND TISOI.item_product_type IN
		<foreach collection="itemProductType" item="item" open="(" separator="," close=")">
			#{item}
		</foreach>
		AND  TISSO.sales_status in (2,5,12)
	</select>

	<!-- 获取个人消费的总额 -->
	<select id="getTotalConsume" parameterType="Map" resultType="java.lang.Integer" >
		SELECT ifnull(SUM(TISOI.item_total),0)
		FROM tb_iamberry_shop_order_item TISOI
		LEFT JOIN tb_iamberry_shop_sales_order TISSO ON TISOI.item_sales_orderid = TISSO.sales_orderid
		where TISSO.sales_openid = #{userOpenid}
		AND TISOI.item_product_type IN
		<foreach collection="itemProductType" item="item" open="(" separator="," close=")">
			#{item}
		</foreach>
		AND  TISSO.sales_status in (2,5,12)
	</select>

	<!-- 获取已购买机器的总人数 -->
	<select id="getAlreadyNumber" parameterType="Member" resultType="java.lang.Integer" >
		SELECT COUNT(distinct TISSO.sales_openid)
		FROM tb_iamberry_shop_order_item TISOI
		LEFT JOIN tb_iamberry_shop_sales_order TISSO ON TISSO.sales_orderid = TISOI.item_sales_orderid
		LEFT JOIN tb_iamberry_user_userinfo TIUU ON TISSO.sales_openid = TIUU.user_openid
		WHERE TIUU.user_dealers = #{userDealers}
		AND TISOI.item_product_type IN
		<foreach collection="ids" item="item" open="(" separator="," close=")">
			#{item}
		</foreach>
		AND  TISSO.sales_status in (2,5,12)
	</select>

	<!-- 获取已购机器台数 -->
	<select id="getUnitsNumber" parameterType="Member" resultType="java.lang.Integer" >
		SELECT ifnull(SUM(TISOI.item_num),0)
		FROM tb_iamberry_shop_order_item TISOI
		LEFT JOIN tb_iamberry_shop_sales_order TISSO ON TISSO.sales_orderid = TISOI.item_sales_orderid
		LEFT JOIN tb_iamberry_user_userinfo TIUU ON TISSO.sales_openid = TIUU.user_openid
		WHERE TIUU.user_dealers = #{userDealers}
		AND TISOI.item_product_type IN
		<foreach collection="ids" item="item" open="(" separator="," close=")">
			#{item}
		</foreach>
		AND  TISSO.sales_status in (2,5,12)
	</select>


	<!-- 获取总的机器消费金额 -->
	<select id="getTotalAmount" parameterType="Member" resultType="java.lang.Integer" >
		SELECT ifnull(SUM(TISOI.item_total),0)
		FROM tb_iamberry_shop_order_item TISOI
		LEFT JOIN tb_iamberry_shop_sales_order TISSO ON TISSO.sales_orderid = TISOI.item_sales_orderid
		LEFT JOIN tb_iamberry_user_userinfo TIUU ON TISSO.sales_openid = TIUU.user_openid
		WHERE TIUU.user_dealers = #{userDealers}
		AND TISOI.item_product_type IN
		<foreach collection="ids" item="item" open="(" separator="," close=")">
			#{item}
		</foreach>
		AND  TISSO.sales_status in (2,5,12)
	</select>

	<!-- 查询本周销售数量 -->
	<select id="listWeekNum" resultType="StatisticsSalesOrder">
		SELECT
			date_format(o.sales_create_date,'%Y-%m-%d') salesDate,
			SUM(CASE WHEN i.item_color_id = 41 OR i.item_color_id = 42 OR i.item_color_id = 37 OR i.item_color_id = 39 THEN i.item_num ELSE 0 END) salesNum,
			SUM(CASE WHEN i.item_color_id = 37 THEN i.item_num ELSE 0 END) salesRedNum,
			SUM(CASE WHEN i.item_color_id = 39 THEN i.item_num ELSE 0 END) salesBlackNum,
			SUM(CASE WHEN i.item_color_id = 41 OR i.item_color_id = 42 THEN i.item_num ELSE 0 END) salesBlueNum
		FROM
			tb_iamberry_shop_sales_order o
		LEFT JOIN tb_iamberry_shop_order_item i ON o.sales_orderid = i.item_sales_orderid
		WHERE
			YEARWEEK(date_format(o.sales_create_date,'%Y-%m-%d'),1) = YEARWEEK(now())
		AND
			o.sales_status in (2,5,12)
		GROUP BY
			salesDate
	</select>

	<!-- 查询上周销售数量 -->
	<select id="listLastWeekNum" resultType="StatisticsSalesOrder">
		SELECT
			date_format(o.sales_create_date,'%Y-%m-%d') salesDate,
			SUM(CASE WHEN i.item_color_id = 41 OR i.item_color_id = 42 OR i.item_color_id = 37 OR i.item_color_id = 39 THEN i.item_num ELSE 0 END) salesNum,
			SUM(CASE WHEN i.item_color_id = 37 THEN i.item_num ELSE 0 END) salesRedNum,
			SUM(CASE WHEN i.item_color_id = 39 THEN i.item_num ELSE 0 END) salesBlackNum,
			SUM(CASE WHEN i.item_color_id = 41 OR i.item_color_id = 42 THEN i.item_num ELSE 0 END) salesBlueNum
		FROM
			tb_iamberry_shop_sales_order o
		LEFT JOIN tb_iamberry_shop_order_item i ON o.sales_orderid = i.item_sales_orderid
		WHERE
			YEARWEEK(date_format(o.sales_create_date,'%Y-%m-%d'),1) = YEARWEEK(now())-1
		AND
			o.sales_status in (2,5,12)
		GROUP BY
			salesDate
	</select>

	<!-- 按月查询销售数量 -->
	<select id="listMonthNum" parameterType="StatisticsSalesOrder" resultType="StatisticsSalesOrder">
		SELECT
			date_format(o.sales_create_date,'%Y-%m-%d') salesDate,
			SUM(CASE WHEN i.item_color_id = 41 OR i.item_color_id = 42 OR i.item_color_id = 37 OR i.item_color_id = 39 THEN i.item_num ELSE 0 END) salesNum,
			SUM(CASE WHEN i.item_color_id = 37 THEN i.item_num ELSE 0 END) salesRedNum,
			SUM(CASE WHEN i.item_color_id = 39 THEN i.item_num ELSE 0 END) salesBlackNum,
			SUM(CASE WHEN i.item_color_id = 41 OR i.item_color_id = 42 THEN i.item_num ELSE 0 END) salesBlueNum
		FROM
			tb_iamberry_shop_sales_order o
		LEFT JOIN tb_iamberry_shop_order_item i ON o.sales_orderid = i.item_sales_orderid
		WHERE
			date_format(o.sales_create_date,'%Y-%m') = date_format(#{salesDate},'%Y-%m')
		AND
			o.sales_status in (2,5,12)
		GROUP BY
			salesDate
	</select>

	<!-- 按年查询销售数量 -->
	<select id="listYearNum" parameterType="StatisticsSalesOrder" resultType="StatisticsSalesOrder">
		SELECT
			date_format(o.sales_create_date,'%m') salesMonth,
			SUM(CASE WHEN i.item_color_id = 41 OR i.item_color_id = 42 OR i.item_color_id = 37 OR i.item_color_id = 39 THEN i.item_num ELSE 0 END) salesNum,
			SUM(CASE WHEN i.item_color_id = 37 THEN i.item_num ELSE 0 END) salesRedNum,
			SUM(CASE WHEN i.item_color_id = 39 THEN i.item_num ELSE 0 END) salesBlackNum,
			SUM(CASE WHEN i.item_color_id = 41 OR i.item_color_id = 42 THEN i.item_num ELSE 0 END) salesBlueNum
		FROM
			tb_iamberry_shop_sales_order o
		LEFT JOIN tb_iamberry_shop_order_item i ON o.sales_orderid = i.item_sales_orderid
		WHERE
			date_format(o.sales_create_date,'%Y') = date_format('2017-09-19','%Y')
		AND
			o.sales_status in (2,5,12)
		GROUP BY
			date_format(o.sales_create_date,'%Y-%m')
	</select>


	<!--  菠萝派接口调用，其他接口请勿使用 - stast -->
	<select id="selectShopOrderByPolaById" parameterType="String" resultType="Order">
		SELECT
			O.SALES_ORDERID salesOrderid,
			O.sales_openid salesOpenid,
			O.SALES_ADDRESS_NAME salesAddressName,
			U.USER_NICKNAME userNickname,
			O.SALES_ADDRESS_TEL salesAddressTel,
			O.SALES_AMOUNT salesAmount,
			O.SALES_RATE_AMOUNT salesRateAmount,
			O.SALES_INTEGRAL_NUM salesIntegralNum,
			O.SALES_YET_AMOUNT salesYetAmount,
			O.SALES_STATUS salesStatus,
			O.SALES_CREATE_DATE salesCreateDate,
			O.SALES_POST_NUM salesPostNum,
			O.SALES_POST_FIRM salesPostFirm,
			O.SALES_ADMIN_REMARK salesAdminRemark,
			O.SALES_COLOR_TYPE salesColorType,
			O.SALES_USER_REMARK salesUserRemark,
			O.SALES_IS_RFUND salesIsRfund,
			O.sales_transaction_date salesTransactionDate,
			O.sales_address_info salesAddressInfo,
			O.sales_address_postnum salesAddressPostnum,
			O.sales_return_des salesReturnDes,
			U.USER_RES_TYPE userResource
		FROM
			TB_IAMBERRY_SHOP_SALES_ORDER O
		LEFT JOIN TB_IAMBERRY_USER_USERINFO U ON O.SALES_OPENID = U.USER_OPENID
		WHERE
			O.SALES_ORDERID = #{salesOrderid}
	</select>

	<select id="selectShopOrderByPola" parameterType="Order" resultType="Order">
		SELECT
			O.SALES_ORDERID salesOrderid,
			O.SALES_ADDRESS_NAME salesAddressName,
			U.USER_NICKNAME userNickname,
			O.SALES_ADDRESS_TEL salesAddressTel,
			O.SALES_AMOUNT salesAmount,
			O.SALES_RATE_AMOUNT salesRateAmount,
			O.SALES_INTEGRAL_NUM salesIntegralNum,
			O.SALES_YET_AMOUNT salesYetAmount,
			O.SALES_STATUS salesStatus,
			O.SALES_CREATE_DATE salesCreateDate,
			O.SALES_POST_NUM salesPostNum,
			O.SALES_POST_FIRM salesPostFirm,
			O.SALES_ADMIN_REMARK salesAdminRemark,
			O.SALES_COLOR_TYPE salesColorType,
			O.SALES_USER_REMARK salesUserRemark,
			O.SALES_IS_RFUND salesIsRfund,
			O.sales_transaction_date salesTransactionDate,
			O.sales_address_info salesAddressInfo,
			O.sales_address_postnum salesAddressPostnum,
		    O.sales_return_des salesReturnDes,
			U.USER_RES_TYPE userResource
		FROM
			TB_IAMBERRY_SHOP_SALES_ORDER O
		LEFT JOIN TB_IAMBERRY_USER_USERINFO U ON O.SALES_OPENID = U.USER_OPENID
		WHERE
			O.SALES_ORDER_TYPE = 1
		AND O.SALES_STATUS !=  1
		AND O.SALES_STATUS !=  0
		AND O.sales_is_customized = 1
		AND O.sales_transportation_type = 1
		<if test="salesStatus != null and salesStatus != -1">
			AND O.SALES_STATUS = #{salesStatus}
		</if>
		<if test="salesOrderid != null and salesOrderid!=''">
			AND O.SALES_ORDERID = #{salesOrderid}
		</if>
		<if test="beginDate != null and beginDate!='' and selectDateType == 2">
			AND O.sales_create_date &gt;= #{beginDate}
		</if>
		<if test="endDate != null and endDate!='' and selectDateType == 2">
			AND O.sales_create_date &lt;= #{endDate}
		</if>
		ORDER BY O.SALES_CREATE_DATE DESC
		LIMIT ${page.recordBegin},${page.pageSize}
	</select>

	<!-- 根据orderId获取订单项数据 -->
	<select id="getShopOrderItemByOrderIdToPola" parameterType="String" resultType="OrderItem">
		SELECT
		I.item_id itemId,
		I.ITEM_PRODUCT_NAME itemProductName, I.ITEM_PRODUCT_PIC itemProductPic, I.ITEM_PRODUCT_PRICE itemProductPrice,
		I.ITEM_PRODUCT_DISCOUNT itemProductDiscount, I.ITEM_NUM itemNum, I.ITEM_TOTAL itemTotal, C.COLOR_NAME productColor,
		C.COLOR_69CODE produectRemark, C.COLOR_POLY_CODE colorPolyCode, I.ITEM_PRODUCT_TYPE itemProductType
		FROM
		TB_IAMBERRY_SHOP_ORDER_ITEM I LEFT JOIN TB_IAMBERRY_PRODUCT_INFO P
		ON I.ITEM_PRODUCT_ID = P.PRODUCT_ID
		LEFT JOIN TB_IAMBERRY_PRODUCT_COLOR C ON I.ITEM_COLOR_ID = C.COLOR_ID
		WHERE
		ITEM_SALES_ORDERID = #{0}
		ORDER BY ITEM_ID DESC
	</select>

	<select id="selectOrderNum" parameterType="Order" resultType="Integer">
		SELECT
		count(O.SALES_ORDERID)
		FROM
		TB_IAMBERRY_SHOP_SALES_ORDER O
		LEFT JOIN TB_IAMBERRY_USER_USERINFO U ON O.SALES_OPENID = U.USER_OPENID
		WHERE
		O.SALES_ORDER_TYPE = 1
		AND O.SALES_STATUS !=  1
		AND O.SALES_STATUS !=  0
		AND O.sales_is_customized = 1
		AND O.sales_transportation_type = 1
		<if test="salesStatus != null and salesStatus != -1">
			AND O.SALES_STATUS = #{salesStatus}
		</if>
		<if test="salesOrderid != null and salesOrderid!=''">
			AND O.SALES_ORDERID = #{salesOrderid}
		</if>
		<if test="beginDate != null and beginDate!=''">
			AND O.sales_create_date &gt;= #{beginDate}
		</if>
		<if test="endDate != null and endDate!=''">
			AND O.sales_create_date &lt;= #{endDate}
		</if>
		ORDER BY O.SALES_CREATE_DATE DESC
		LIMIT ${page.recordBegin},${page.pageSize}
	</select>

	<!-- 根据orderId修改订单状态 -->
	<update id="updateOrderByPola" parameterType="Order">
		UPDATE
		TB_IAMBERRY_SHOP_SALES_ORDER
		SET
		<if test="salesUserRemark != null and salesUserRemark != ''">
			sales_user_remark = #{salesUserRemark}
		</if>
		WHERE
		SALES_ORDERID = #{salesOrderid}
	</update>
	<!--  菠萝派接口调用，其他接口请勿使用 - end -->

	<select id="selectNumberBacklog" resultType="NumberBacklogDto">
		SELECT
		(CASE WHEN a.sendTheGoods is null THEN 0 ELSE a.sendTheGoods END) as sendTheGoods,
		(CASE WHEN b.returnGoods is null THEN 0 ELSE b.returnGoods END) as returnGoods,
		(CASE WHEN c.returnRefund is null THEN 0 ELSE c.returnRefund END) as returnRefund,
		(CASE WHEN d.exchange is null THEN 0 ELSE d.exchange END) as exchange
		FROM
		(
		(
		SELECT
		sum(1) AS sendTheGoods
		FROM
		tb_iamberry_shop_sales_order
		WHERE
		sales_status = 2
		) AS a
		CROSS JOIN (
		SELECT
		sum(1) AS returnGoods
		FROM
		tb_iamberry_shop_sales_order
		WHERE
		sales_status = 3
		) AS b
		CROSS JOIN (
		SELECT
		sum(1) AS returnRefund
		FROM
		tb_iamberry_shop_sales_order
		WHERE
		sales_status = 6
		) AS c
		CROSS JOIN (
		SELECT
		sum(1) AS exchange
		FROM
		tb_iamberry_shop_sales_order
		WHERE
		sales_status = 11
		) AS d
		)
	</select>

	<resultMap  id="BaseResultMap" type="Order" >
		<result    column="sales_orderid"    property="salesOrderid" />
		<result    column="sales_openid"    property="salesOpenid" />
		<result    column="sales_yet_amount"    property="salesYetAmount" />
		<result    column="sales_amount"    property="salesAmount" />
		<result    column="sales_reward_num"    property="salesRewardNum" />
		<result    column="sales_integral_num"    property="salesIntegralNum" />
		<result    column="sales_rate_amount"    property="salesRateAmount" />
		<result    column="sales_create_date"    property="salesCreateDate" />
		<result    column="sales_reward_integral_num"    property="salesRewardIntegralNum" />
		<result    column="sales_post_num"    property="salesPostNum" />
		<result    column="sales_post_firm"    property="salesPostFirm" />
		<result    column="sales_status"    property="salesStatus" />
		<result    column="sales_send_date"    property="salesSendDate" />
		<result    column="sales_return_date"    property="salesReturnDate" />
		<result    column="sales_address_info"    property="salesAddressInfo" />
		<result    column="sales_address_postnum"    property="salesAddressPostnum" />
		<result    column="sales_address_tel"    property="salesAddressTel" />
		<result    column="sales_address_name"    property="salesAddressName" />
		<result    column="sales_return_des"    property="salesReturnDes" />
		<result    column="sales_admin_remark"    property="salesAdminRemark" />
		<result    column="sales_user_remark"    property="salesUserRemark" />
		<result    column="sales_transaction_id"    property="salesTransactionId" />
		<result    column="sales_transaction_date"    property="salesTransactionDate" />
		<result    column="sales_refund_no"    property="salesRefundNo" />
		<result    column="sales_transaction_refund_id"    property="salesTransactionRefundId" />
		<result    column="sales_color_type"    property="salesColorType" />
		<result    column="sales_proxy_reward"    property="salesProxyReward" />
		<result    column="sales_coupon_receive_id"    property="couponId" />
		<result    column="sales_porderid"    property="salesPorderid" />
		<result    column="sales_order_type"    property="salesOrderType" />
		<result    column="sales_is_rfund"    property="salesIsRfund" />
		<result    column="sales_audit_remark"    property="salesAuditRemark" />
		<result    column="sales_is_customized"    property="salesIsCustomized" />
		<collection property="orderItemList" column="sales_orderid" javaType="ArrayList" select="getOrderItemListToExcel"/>
	</resultMap>

	<select id="getOrderListToExcel" resultMap="BaseResultMap" parameterType="Order" >
		select
			t.*,
			chan.sales_channel_name,
			uuer.user_nickname
		from tb_iamberry_shop_sales_order t
		LEFT JOIN tb_iamberry_user_userinfo uuer ON t.sales_openid = uuer.user_openid
		LEFT JOIN (
			SELECT
				mc.main_channel_id AS sales_channel_id,
				mc.main_channel_name AS sales_channel_name,
				1 AS sales_channel_type
			FROM
				tb_iamberry_main_channel mc
			UNION ALL
				SELECT
					cc.child_channel_id AS sales_channel_id,
					cc.child_channel_name AS sales_channel_name,
					2 AS sales_channel_type
				FROM
					tb_iamberry_child_channel cc
		) chan ON t.sales_channel_type = chan.sales_channel_type AND t.sales_channel_id = chan.sales_channel_id
		<where>
			t.sales_status IN (2,5,9,10,11,12)
			<if test="beginDate != null">
				<![CDATA[AND DATE_FORMAT(t.SALES_CREATE_DATE, '%Y-%m-%d') >= ]]>DATE_FORMAT(#{beginDate},'%Y-%m-%d')
			</if>
			<if test="endDate != null">
				<![CDATA[AND DATE_FORMAT(t.SALES_CREATE_DATE, '%Y-%m-%d') <= ]]>DATE_FORMAT(#{endDate},'%Y-%m-%d')
			</if>
		</where>
		ORDER BY t.SALES_CREATE_DATE DESC
	</select>

	<select id="getOrderItemListToExcel" resultType="OrderItem" parameterType="String" >
		select
		    t.item_id,
			t.item_sales_orderid,
			t.item_product_id,
			t.item_color_id,
			t.item_num,
			t.item_total,
			t.item_create_date,
			t.item_product_name,
			t.item_product_discount,
			t.item_product_price,
			t.item_product_pic,
			t.item_product_type,
			pt.type_name AS productTypeName,
			pc.color_name AS productColor
		from tb_iamberry_shop_order_item t
		LEFT JOIN tb_iamberry_product_info pi ON t.item_product_id = pi.product_id
		LEFT JOIN tb_iamberry_product_color pc ON t.item_color_id = pc.color_id
		LEFT JOIN tb_iamberry_product_type pt ON pi.product_type = pt.type_id
		WHERE t.item_sales_orderid = #{salesOrderid}
	</select>


	<!-- 根据orderId查询订单信息 -->
	<select id="getPaySuccessOrderByOrderId" parameterType="Order" resultType="Order">
		SELECT O.SALES_ORDERID salesOrderid, U.USER_NICKNAME userNickname, O.SALES_STATUS salesStatus,
		O.SALES_YET_AMOUNT salesYetAmount, O.SALES_INTEGRAL_NUM salesIntegralNum, O.SALES_RATE_AMOUNT salesRateAmount,
		O.SALES_POST_FIRM salesPostFirm, O.SALES_POST_NUM salesPostNum, O.SALES_CREATE_DATE salesCreateDate,
		O.SALES_ADDRESS_POSTNUM salesAddressPostnum,  O.SALES_REWARD_INTEGRAL_NUM salesRewardIntegralNum,
		O.SALES_ADDRESS_NAME salesAddressName, O.SALES_ADDRESS_TEL salesAddressTel, O.SALES_SEND_DATE salesSendDate,
		O.SALES_USER_REMARK salesUserRemark, O.SALES_COLOR_TYPE salesColorType, O.SALES_ADMIN_REMARK salesAdminRemark,
		O.SALES_RETURN_DES salesReturnDes, O.SALES_ADDRESS_INFO salesAddressInfo, O.SALES_RETURN_DATE salesReturnDate,
		O.SALES_AMOUNT salesAmount, O.SALES_TRANSACTION_DATE salesTransactionDate, O.SALES_OPENID salesOpenid,
		O.SALES_COUPON_RECEIVE_ID couponId, O.SALES_BALANCE_AMOUNT balancePayAmount, O.SALES_IS_RFUND salesIsRfund
		FROM
		TB_IAMBERRY_SHOP_SALES_ORDER O LEFT JOIN TB_IAMBERRY_USER_USERINFO U
		ON O.SALES_OPENID = U.USER_OPENID
		<where>
			O.sales_status IN (2,5,9,10,11,12)
			<if test="salesStatus != null and salesStatus != -1">
				AND O.SALES_STATUS = #{salesStatus}
			</if>
			<if test="salesOrderid != null and salesOrderid!=''">
				AND O.SALES_ORDERID LIKE CONCAT('%',#{salesOrderid},'%')
			</if>
			<if test="salesAddressName != null and salesAddressName != ''">
				AND O.SALES_ADDRESS_NAME LIKE CONCAT('%',#{salesAddressName},'%')
			</if>
			<if test="salesAddressTel != null and salesAddressTel != ''">
				AND O.SALES_ADDRESS_TEL LIKE CONCAT('%',#{salesAddressTel},'%')
			</if>
			<if test="salesAddressInfo != null and salesAddressInfo != ''">
				AND O.SALES_ADDRESS_INFO LIKE CONCAT('%',#{salesAddressInfo},'%')
			</if>
			<if test="beginDate != null">
				<![CDATA[AND DATE_FORMAT(O.SALES_CREATE_DATE, '%Y-%m-%d') >= ]]>DATE_FORMAT(#{beginDate},'%Y-%m-%d')
			</if>
			<if test="endDate != null">
				<![CDATA[AND DATE_FORMAT(O.SALES_CREATE_DATE, '%Y-%m-%d') <= ]]>DATE_FORMAT(#{endDate},'%Y-%m-%d')
			</if>
		</where>
		ORDER BY O.SALES_CREATE_DATE DESC
	</select>


</mapper>