<?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.gxwebsoft.shop.mapper.OrderMapper">

  <!-- 关联查询sql -->
  <sql id="selectSql">
    SELECT a.*,b.image as merchantAvatar
    FROM shop_order a
    LEFT JOIN shop_merchant b ON a.merchant_id = b.merchant_id
    <where>
      <if test="param.orderId != null">
        AND a.order_id = #{param.orderId}
      </if>
      <if test="param.type != null">
        AND a.type = #{param.type}
      </if>
      <if test="param.channel != null">
        AND a.type = #{param.channel}
      </if>
      <if test="param.orderNo != null">
        AND a.order_no LIKE CONCAT('%', #{param.orderNo}, '%')
      </if>
      <if test="param.transactionId != null">
        AND a.transaction_id LIKE CONCAT('%', #{param.transactionId}, '%')
      </if>
      <if test="param.refundOrder != null">
        AND a.refund_order LIKE CONCAT('%', #{param.refundOrder}, '%')
      </if>
      <if test="param.merchantId != null">
        AND a.merchant_id = #{param.merchantId}
      </if>
      <if test="param.merchantCode != null">
        AND a.merchant_code = #{param.merchantCode}
      </if>
      <if test="param.couponId != null">
        AND a.coupon_id = #{param.couponId}
      </if>
      <if test="param.cardId != null">
        AND a.card_id LIKE CONCAT('%', #{param.cardId}, '%')
      </if>
      <if test="param.adminId != null">
        AND a.admin_id = #{param.adminId}
      </if>
      <if test="param.confirmId != null">
        AND a.confirm_id = #{param.confirmId}
      </if>
      <if test="param.icCard != null">
        AND a.ic_card LIKE CONCAT('%', #{param.icCard}, '%')
      </if>
      <if test="param.realName != null">
        AND a.real_name LIKE CONCAT('%', #{param.realName}, '%')
      </if>
      <if test="param.phone != null">
        AND a.phone LIKE CONCAT('%', #{param.phone}, '%')
      </if>
      <if test="param.totalPrice != null">
        AND a.total_price = #{param.totalPrice}
      </if>
      <if test="param.reducePrice != null">
        AND a.reduce_price = #{param.reducePrice}
      </if>
      <if test="param.payPrice != null">
        AND a.pay_price = #{param.payPrice}
      </if>
      <if test="param.price != null">
        AND a.price = #{param.price}
      </if>
      <if test="param.money != null">
        AND a.money = #{param.money}
      </if>
      <if test="param.refundMoney != null">
        AND a.refund_money = #{param.refundMoney}
      </if>
      <if test="param.coachPrice != null">
        AND a.coach_price = #{param.coachPrice}
      </if>
      <if test="param.coachId != null">
        AND a.coach_id = #{param.coachId}
      </if>
      <if test="param.payType != null">
        AND a.pay_type = #{param.payType}
      </if>
      <if test="param.payStatus != null">
        AND a.pay_status = #{param.payStatus}
      </if>
      <if test="param.orderStatus != null">
        AND a.order_status = #{param.orderStatus}
      </if>
      <if test="param.couponType != null">
        AND a.coupon_type = #{param.couponType}
      </if>
      <if test="param.couponDesc != null">
        AND a.coupon_desc LIKE CONCAT('%', #{param.couponDesc}, '%')
      </if>
      <if test="param.qrcode != null">
        AND a.qrcode LIKE CONCAT('%', #{param.qrcode}, '%')
      </if>
      <if test="param.returnNum != null">
        AND a.return_num = #{param.returnNum}
      </if>
      <if test="param.returnMoney != null">
        AND a.return_money = #{param.returnMoney}
      </if>
      <if test="param.startTime != null">
        AND a.start_time LIKE CONCAT('%', #{param.startTime}, '%')
      </if>
      <if test="param.isInvoice != null">
        AND a.is_invoice = #{param.isInvoice}
      </if>
      <if test="param.payTime != null">
        AND a.pay_time LIKE CONCAT('%', #{param.payTime}, '%')
      </if>
      <if test="param.refundTime != null">
        AND a.refund_time LIKE CONCAT('%', #{param.refundTime}, '%')
      </if>
      <if test="param.refundApplyTime != null">
        AND a.refund_apply_time LIKE CONCAT('%', #{param.refundApplyTime}, '%')
      </if>
      <if test="param.expirationTime != null">
        AND a.expiration_time &lt;= #{param.expirationTime}
      </if>
      <if test="param.deliveryTime != null">
        AND a.delivery_time &lt;= #{param.deliveryTime}
      </if>
      <if test="param.deliveryStatus != null">
        AND a.delivery_status = #{param.deliveryStatus}
      </if>
      <if test="param.checkBill != null">
        AND a.check_bill = #{param.checkBill}
      </if>
      <if test="param.isSettled != null">
        AND a.is_settled = #{param.isSettled}
      </if>
      <if test="param.version != null">
        AND a.version = #{param.version}
      </if>
      <if test="param.userId != null">
        AND a.user_id = #{param.userId}
      </if>
      <if test="param.comments != null">
        AND a.comments LIKE CONCAT('%', #{param.comments}, '%')
      </if>
      <if test="param.sortNumber != null">
        AND a.sort_number = #{param.sortNumber}
      </if>
      <if test="param.deleted != null">
        AND a.deleted = #{param.deleted}
      </if>
      <if test="param.deleted == null">
        AND a.deleted = 0
      </if>
      <if test="param.createTimeStart != null">
        AND a.create_time &gt;= #{param.createTimeStart}
      </if>
      <if test="param.createTimeEnd != null">
        AND a.create_time &lt;= #{param.createTimeEnd}
      </if>
      <if test="param.keywords != null">
        AND (a.order_no LIKE CONCAT('%', #{param.keywords}, '%')
        OR a.order_id = #{param.keywords}
        OR a.phone = #{param.keywords}
        OR a.user_id = #{param.keywords}
        OR a.merchant_id = #{param.keywords}
        OR a.merchant_code =#{param.keywords}
        )
      </if>
    </where>
    ORDER BY a.order_id DESC
  </sql>

  <!-- 分页查询 -->
  <select id="selectPageRel" resultType="com.gxwebsoft.shop.entity.Order">
    <include refid="selectSql"></include>
  </select>

  <!-- 查询全部 -->
  <select id="selectListRel" resultType="com.gxwebsoft.shop.entity.Order">
    <include refid="selectSql"></include>
  </select>

  <!-- 按OutTradeNo查询 -->
  <select id="getByOutTradeNo" resultType="com.gxwebsoft.shop.entity.Order">
    <include refid="selectSql"></include>
  </select>

  <!-- 更新订单状态 -->
  <select id="updateByOutTradeNo" resultType="com.gxwebsoft.shop.entity.Order">
    UPDATE shop_order SET pay_status = #{param.payStatus}, pay_time = #{param.payTime}, pay_price = #{param.payPrice}, transaction_id = #{param.transactionId},expiration_time = #{param.expirationTime} WHERE order_no = #{param.orderNo}
  </select>

  <!-- 查询过期订单 -->
  <select id="selectExpirationOrder" resultType="com.gxwebsoft.shop.entity.Order">
    SELECT * from shop_order where type = 1 AND pay_status = 0 AND order_status = 0 AND expiration_time &lt;= #{param.expirationTime}
  </select>

  <!-- 更明过期订单的状态 -->
  <select id="updateExpirationOrder" resultType="com.gxwebsoft.shop.entity.Order">
    UPDATE shop_order SET order_status = 2 WHERE order_id = #{orderId}
  </select>

</mapper>
