<?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.cku.dao.SPOrderInfoMapper" >
  <resultMap id="BaseResultMap" type="com.cku.model.SPOrderInfo" >
    <id column="id" property="id" jdbcType="INTEGER" />
    <result column="cku_id" property="ckuId" jdbcType="VARCHAR" />
    <result column="username" property="username" jdbcType="VARCHAR" />
    <result column="MailAddressID" property="mailaddressid" jdbcType="INTEGER" />
    <result column="OrderID" property="orderid" jdbcType="VARCHAR" />
    <result column="sp_integral" property="spIntegral" jdbcType="INTEGER" />
    <result column="SP_prices" property="spPrices" jdbcType="DECIMAL" />
    <result column="OrderUndo" property="orderundo" jdbcType="CHAR" />
    <result column="OrderUndoTime" property="orderundotime" jdbcType="TIMESTAMP" />
    <result column="OrderUndoType" property="orderundotype" jdbcType="CHAR" />
    <result column="OrderConfirm" property="orderconfirm" jdbcType="CHAR" />
    <result column="OrderConfirmTime" property="orderconfirmtime" jdbcType="TIMESTAMP" />
    <result column="PayConfirm" property="payconfirm" jdbcType="CHAR" />
    <result column="PayConfirmTime" property="payconfirmtime" jdbcType="TIMESTAMP" />
    <result column="PayInfo" property="payinfo" jdbcType="VARCHAR" />
    <result column="ExpressConfirm" property="expressconfirm" jdbcType="CHAR" />
    <result column="ExpressConfirmTime" property="expressconfirmtime" jdbcType="TIMESTAMP" />
    <result column="ExpressCompany" property="expresscompany" jdbcType="VARCHAR" />
    <result column="ExpressNumber" property="expressnumber" jdbcType="VARCHAR" />
    <result column="add_time" property="addTime" jdbcType="TIMESTAMP" />
    <result column="out_trade_no" property="outTradeNo" jdbcType="VARCHAR" />
    <result column="locked" property="locked" jdbcType="CHAR" />
 	<collection property="SPCartsList" javaType="list" ofType="com.cku.model.SPCarts">
 		    <id column="id" property="id" jdbcType="INTEGER" />
		    <result column="OrderID" property="orderid" jdbcType="VARCHAR" />
		    <result column="cku_id" property="ckuId" jdbcType="VARCHAR" />
		    <result column="pid" property="pid" jdbcType="INTEGER" />
		    <result column="pcount" property="pcount" jdbcType="INTEGER" />
		    <result column="sp_price" property="spPrice" jdbcType="DECIMAL" />
		    <result column="total_price" property="totalPrice" jdbcType="DECIMAL" />
		    <result column="sp_integral" property="spIntegral" jdbcType="INTEGER" />
		    <result column="add_time" property="addTime" jdbcType="TIMESTAMP" />
		    <result column="xmid" property="xmid" jdbcType="VARCHAR" />
		    <result column="pz_id" property="pzId" jdbcType="VARCHAR" />
		    <result column="PayConfirm" property="payconfirm" jdbcType="CHAR" />
		    <result column="operate" property="operate" jdbcType="CHAR" />
		    <result column="operate_time" property="operateTime" jdbcType="TIMESTAMP" />
		    <result column="memberid" property="memberid" jdbcType="VARCHAR" />
	</collection>
  </resultMap>
  <resultMap id="ResultMapWithBLOBs" type="com.cku.model.SPOrderInfo" extends="BaseResultMap" >
    <result column="OrderRemarks" property="orderremarks" jdbcType="LONGVARCHAR" />
  </resultMap>
  <sql id="Base_Column_List" >
    id, cku_id, username, MailAddressID, OrderID, sp_integral, SP_prices, OrderUndo, 
    OrderUndoTime, OrderUndoType, OrderConfirm, OrderConfirmTime, PayConfirm, PayConfirmTime, 
    PayInfo, ExpressConfirm, ExpressConfirmTime, ExpressCompany, ExpressNumber, add_time, 
    out_trade_no, locked
  </sql>
  <sql id="Blob_Column_List" >
    OrderRemarks
  </sql>
  <select id="selectByPrimaryKey" resultMap="ResultMapWithBLOBs" parameterType="java.lang.Integer" >
    select 
    <include refid="Base_Column_List" />
    ,
    <include refid="Blob_Column_List" />
    from SP_OrderInfo
    where id = #{id,jdbcType=INTEGER}
  </select>
  <!-- 根据订单号查询订单详情  zhuoHeng-->
  <select id="selectByOrderID" resultMap="BaseResultMap" parameterType="java.lang.Integer" >
    select 
    <include refid="Base_Column_List" />
    ,
    <include refid="Blob_Column_List" />
    from SP_OrderInfo
    where OrderID = #{OrderID,jdbcType=VARCHAR}
    and cku_id = #{ckuId,jdbcType=VARCHAR}
  </select>
  <!--  -->
  <update id="updateOrderPayConfim"  parameterType="com.cku.model.SPOrderInfo" >
  update SP_OrderInfo set PayConfirm = '1',PayConfirmTime = getdate() where OrderID = #{orderID}
  </update>
  <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer" >
    delete from SP_OrderInfo
    where id = #{id,jdbcType=INTEGER}
  </delete>
  <insert id="insert" parameterType="com.cku.model.SPOrderInfo" >
    insert into SP_OrderInfo (id, cku_id, username, 
      MailAddressID, OrderID, sp_integral, 
      SP_prices, OrderUndo, OrderUndoTime, 
      OrderUndoType, OrderConfirm, OrderConfirmTime, 
      PayConfirm, PayConfirmTime, PayInfo, 
      ExpressConfirm, ExpressConfirmTime, ExpressCompany, 
      ExpressNumber, add_time, out_trade_no, 
      locked, OrderRemarks)
    values (#{id,jdbcType=INTEGER}, #{ckuId,jdbcType=VARCHAR}, #{username,jdbcType=VARCHAR}, 
      #{mailaddressid,jdbcType=INTEGER}, #{orderid,jdbcType=VARCHAR}, #{spIntegral,jdbcType=INTEGER}, 
      #{spPrices,jdbcType=DECIMAL}, #{orderundo,jdbcType=CHAR}, #{orderundotime,jdbcType=TIMESTAMP}, 
      #{orderundotype,jdbcType=CHAR}, #{orderconfirm,jdbcType=CHAR}, #{orderconfirmtime,jdbcType=TIMESTAMP}, 
      #{payconfirm,jdbcType=CHAR}, #{payconfirmtime,jdbcType=TIMESTAMP}, #{payinfo,jdbcType=VARCHAR}, 
      #{expressconfirm,jdbcType=CHAR}, #{expressconfirmtime,jdbcType=TIMESTAMP}, #{expresscompany,jdbcType=VARCHAR}, 
      #{expressnumber,jdbcType=VARCHAR}, #{addTime,jdbcType=TIMESTAMP}, #{outTradeNo,jdbcType=VARCHAR}, 
      #{locked,jdbcType=CHAR}, #{orderremarks,jdbcType=LONGVARCHAR})
  </insert>
  <insert id="insertSelective" parameterType="com.cku.model.SPOrderInfo" >
    insert into SP_OrderInfo
    <trim prefix="(" suffix=")" suffixOverrides="," >
      <if test="id != null" >
        id,
      </if>
      <if test="ckuId != null" >
        cku_id,
      </if>
      <if test="username != null" >
        username,
      </if>
      <if test="mailaddressid != null" >
        MailAddressID,
      </if>
      <if test="orderid != null" >
        OrderID,
      </if>
      <if test="spIntegral != null" >
        sp_integral,
      </if>
      <if test="spPrices != null" >
        SP_prices,
      </if>
      <if test="orderundo != null" >
        OrderUndo,
      </if>
      <if test="orderundotime != null" >
        OrderUndoTime,
      </if>
      <if test="orderundotype != null" >
        OrderUndoType,
      </if>
      <if test="orderconfirm != null" >
        OrderConfirm,
      </if>
      <if test="orderconfirmtime != null" >
        OrderConfirmTime,
      </if>
      <if test="payconfirm != null" >
        PayConfirm,
      </if>
      <if test="payconfirmtime != null" >
        PayConfirmTime,
      </if>
      <if test="payinfo != null" >
        PayInfo,
      </if>
      <if test="expressconfirm != null" >
        ExpressConfirm,
      </if>
      <if test="expressconfirmtime != null" >
        ExpressConfirmTime,
      </if>
      <if test="expresscompany != null" >
        ExpressCompany,
      </if>
      <if test="expressnumber != null" >
        ExpressNumber,
      </if>
      <if test="addTime != null" >
        add_time,
      </if>
      <if test="outTradeNo != null" >
        out_trade_no,
      </if>
      <if test="locked != null" >
        locked,
      </if>
      <if test="orderremarks != null" >
        OrderRemarks,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides="," >
      <if test="id != null" >
        #{id,jdbcType=INTEGER},
      </if>
      <if test="ckuId != null" >
        #{ckuId,jdbcType=VARCHAR},
      </if>
      <if test="username != null" >
        #{username,jdbcType=VARCHAR},
      </if>
      <if test="mailaddressid != null" >
        #{mailaddressid,jdbcType=INTEGER},
      </if>
      <if test="orderid != null" >
        #{orderid,jdbcType=VARCHAR},
      </if>
      <if test="spIntegral != null" >
        #{spIntegral,jdbcType=INTEGER},
      </if>
      <if test="spPrices != null" >
        #{spPrices,jdbcType=DECIMAL},
      </if>
      <if test="orderundo != null" >
        #{orderundo,jdbcType=CHAR},
      </if>
      <if test="orderundotime != null" >
        #{orderundotime,jdbcType=TIMESTAMP},
      </if>
      <if test="orderundotype != null" >
        #{orderundotype,jdbcType=CHAR},
      </if>
      <if test="orderconfirm != null" >
        #{orderconfirm,jdbcType=CHAR},
      </if>
      <if test="orderconfirmtime != null" >
        #{orderconfirmtime,jdbcType=TIMESTAMP},
      </if>
      <if test="payconfirm != null" >
        #{payconfirm,jdbcType=CHAR},
      </if>
      <if test="payconfirmtime != null" >
        #{payconfirmtime,jdbcType=TIMESTAMP},
      </if>
      <if test="payinfo != null" >
        #{payinfo,jdbcType=VARCHAR},
      </if>
      <if test="expressconfirm != null" >
        #{expressconfirm,jdbcType=CHAR},
      </if>
      <if test="expressconfirmtime != null" >
        #{expressconfirmtime,jdbcType=TIMESTAMP},
      </if>
      <if test="expresscompany != null" >
        #{expresscompany,jdbcType=VARCHAR},
      </if>
      <if test="expressnumber != null" >
        #{expressnumber,jdbcType=VARCHAR},
      </if>
      <if test="addTime != null" >
        #{addTime,jdbcType=TIMESTAMP},
      </if>
      <if test="outTradeNo != null" >
        #{outTradeNo,jdbcType=VARCHAR},
      </if>
      <if test="locked != null" >
        #{locked,jdbcType=CHAR},
      </if>
      <if test="orderremarks != null" >
        #{orderremarks,jdbcType=LONGVARCHAR},
      </if>
    </trim>
  </insert>
  <update id="updateByPrimaryKeySelective" parameterType="com.cku.model.SPOrderInfo" >
    update SP_OrderInfo
    <set >
      <if test="ckuId != null" >
        cku_id = #{ckuId,jdbcType=VARCHAR},
      </if>
      <if test="username != null" >
        username = #{username,jdbcType=VARCHAR},
      </if>
      <if test="mailaddressid != null" >
        MailAddressID = #{mailaddressid,jdbcType=INTEGER},
      </if>
      <if test="orderid != null" >
        OrderID = #{orderid,jdbcType=VARCHAR},
      </if>
      <if test="spIntegral != null" >
        sp_integral = #{spIntegral,jdbcType=INTEGER},
      </if>
      <if test="spPrices != null" >
        SP_prices = #{spPrices,jdbcType=DECIMAL},
      </if>
      <if test="orderundo != null" >
        OrderUndo = #{orderundo,jdbcType=CHAR},
      </if>
      <if test="orderundotime != null" >
        OrderUndoTime = #{orderundotime,jdbcType=TIMESTAMP},
      </if>
      <if test="orderundotype != null" >
        OrderUndoType = #{orderundotype,jdbcType=CHAR},
      </if>
      <if test="orderconfirm != null" >
        OrderConfirm = #{orderconfirm,jdbcType=CHAR},
      </if>
      <if test="orderconfirmtime != null" >
        OrderConfirmTime = #{orderconfirmtime,jdbcType=TIMESTAMP},
      </if>
      <if test="payconfirm != null" >
        PayConfirm = #{payconfirm,jdbcType=CHAR},
      </if>
      <if test="payconfirmtime != null" >
        PayConfirmTime = #{payconfirmtime,jdbcType=TIMESTAMP},
      </if>
      <if test="payinfo != null" >
        PayInfo = #{payinfo,jdbcType=VARCHAR},
      </if>
      <if test="expressconfirm != null" >
        ExpressConfirm = #{expressconfirm,jdbcType=CHAR},
      </if>
      <if test="expressconfirmtime != null" >
        ExpressConfirmTime = #{expressconfirmtime,jdbcType=TIMESTAMP},
      </if>
      <if test="expresscompany != null" >
        ExpressCompany = #{expresscompany,jdbcType=VARCHAR},
      </if>
      <if test="expressnumber != null" >
        ExpressNumber = #{expressnumber,jdbcType=VARCHAR},
      </if>
      <if test="addTime != null" >
        add_time = #{addTime,jdbcType=TIMESTAMP},
      </if>
      <if test="outTradeNo != null" >
        out_trade_no = #{outTradeNo,jdbcType=VARCHAR},
      </if>
      <if test="locked != null" >
        locked = #{locked,jdbcType=CHAR},
      </if>
      <if test="orderremarks != null" >
        OrderRemarks = #{orderremarks,jdbcType=LONGVARCHAR},
      </if>
    </set>
    where id = #{id,jdbcType=INTEGER}
  </update>
  <update id="updateByPrimaryKeyWithBLOBs" parameterType="com.cku.model.SPOrderInfo" >
    update SP_OrderInfo
    set cku_id = #{ckuId,jdbcType=VARCHAR},
      username = #{username,jdbcType=VARCHAR},
      MailAddressID = #{mailaddressid,jdbcType=INTEGER},
      OrderID = #{orderid,jdbcType=VARCHAR},
      sp_integral = #{spIntegral,jdbcType=INTEGER},
      SP_prices = #{spPrices,jdbcType=DECIMAL},
      OrderUndo = #{orderundo,jdbcType=CHAR},
      OrderUndoTime = #{orderundotime,jdbcType=TIMESTAMP},
      OrderUndoType = #{orderundotype,jdbcType=CHAR},
      OrderConfirm = #{orderconfirm,jdbcType=CHAR},
      OrderConfirmTime = #{orderconfirmtime,jdbcType=TIMESTAMP},
      PayConfirm = #{payconfirm,jdbcType=CHAR},
      PayConfirmTime = #{payconfirmtime,jdbcType=TIMESTAMP},
      PayInfo = #{payinfo,jdbcType=VARCHAR},
      ExpressConfirm = #{expressconfirm,jdbcType=CHAR},
      ExpressConfirmTime = #{expressconfirmtime,jdbcType=TIMESTAMP},
      ExpressCompany = #{expresscompany,jdbcType=VARCHAR},
      ExpressNumber = #{expressnumber,jdbcType=VARCHAR},
      add_time = #{addTime,jdbcType=TIMESTAMP},
      out_trade_no = #{outTradeNo,jdbcType=VARCHAR},
      locked = #{locked,jdbcType=CHAR},
      OrderRemarks = #{orderremarks,jdbcType=LONGVARCHAR}
    where id = #{id,jdbcType=INTEGER}
  </update>
  <update id="updateByPrimaryKey" parameterType="com.cku.model.SPOrderInfo" >
    update SP_OrderInfo
    set cku_id = #{ckuId,jdbcType=VARCHAR},
      username = #{username,jdbcType=VARCHAR},
      MailAddressID = #{mailaddressid,jdbcType=INTEGER},
      OrderID = #{orderid,jdbcType=VARCHAR},
      sp_integral = #{spIntegral,jdbcType=INTEGER},
      SP_prices = #{spPrices,jdbcType=DECIMAL},
      OrderUndo = #{orderundo,jdbcType=CHAR},
      OrderUndoTime = #{orderundotime,jdbcType=TIMESTAMP},
      OrderUndoType = #{orderundotype,jdbcType=CHAR},
      OrderConfirm = #{orderconfirm,jdbcType=CHAR},
      OrderConfirmTime = #{orderconfirmtime,jdbcType=TIMESTAMP},
      PayConfirm = #{payconfirm,jdbcType=CHAR},
      PayConfirmTime = #{payconfirmtime,jdbcType=TIMESTAMP},
      PayInfo = #{payinfo,jdbcType=VARCHAR},
      ExpressConfirm = #{expressconfirm,jdbcType=CHAR},
      ExpressConfirmTime = #{expressconfirmtime,jdbcType=TIMESTAMP},
      ExpressCompany = #{expresscompany,jdbcType=VARCHAR},
      ExpressNumber = #{expressnumber,jdbcType=VARCHAR},
      add_time = #{addTime,jdbcType=TIMESTAMP},
      out_trade_no = #{outTradeNo,jdbcType=VARCHAR},
      locked = #{locked,jdbcType=CHAR}
    where id = #{id,jdbcType=INTEGER}
  </update>
  <!-- 根据当前登录人和订单状态查询订单 -->
  <!-- 查询所有订单 -->
  <select id="selectPayOrder" resultMap="BaseResultMap">
	SELECT
		TOP ${count} *
	FROM
		SP_OrderInfo
	WHERE
		id NOT IN (
			SELECT
				TOP ${indexNum} id
			FROM
				SP_OrderInfo
			WHERE
				cku_id =#{ckuId}
			AND PayConfirm =${PayConfirm}
			ORDER BY
				PayConfirmTime DESC
		)
	AND cku_id =#{ckuId}
	AND PayConfirm =${PayConfirm}
	ORDER BY
		PayConfirmTime DESC
  </select>
  <!-- 订单总数 -->
  <select id="getCount" resultType="java.lang.Long" >
	SELECT COUNT(orders.id) FROM SP_OrderInfo orders,SP_carts cart WHERE  orders.OrderID=cart.OrderID AND orders.cku_id=#{ckuId} AND orders.PayConfirm=#{PayConfirm}  AND cart.pid IN(45,140,141,142)  
</select>
  
  
  <!-- 查询未支付的订单 -->
  <select id="checkSignUnfinished" resultType="java.lang.Integer">
  select count(*)
    from SP_OrderInfo where cku_id=#{ckuId} AND PayConfirm='0'
  </select>
  <!-- 取消订单删除订单表中记录 -->
  <delete id="deleteByOrderId" parameterType="java.lang.Integer" >
    delete from SP_OrderInfo
    where OrderID = #{orderId}
  </delete>
  <!-- 未支付订单数 -->
  <select id="getCountByUnPay" resultType="java.lang.Long">
  SELECT count(id) from SP_OrderInfo where PayConfirm='0' and cku_id=#{ckuId}
  </select>
  <!-- 已支付订单数 -->
  <select id="getCountByPay" resultType="java.lang.Long">
  SELECT count(id) from SP_OrderInfo where PayConfirm='1' and cku_id=#{ckuId}
  </select>
</mapper>