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

   <resultMap id="broadBandOrder" type="com.winsun.bean.BroadBandOrder">
      <result column="order_number" property="orderNumber" />
      <result column="xb_order_id" property="xbOrderId" />
      <result column="order_id" property="orderId" />
      <result column="order_status" property="orderStatus" />
      <result column="hehuoren_id" property="hehuorenId" />
      <result column="package_id" property="packageId" />
      <result column="customer_name" property="customerName" />
      <result column="business_number" property="businessNumber" />
      <result column="contact_number" property="contactNumber" />
      <result column="id_card" property="idCard" />
      <result column="business_iccid" property="businessIccid" />
      <result column="kapin" property="kapin" />
      <result column="id_cardz" property="idCardz" />
      <result column="id_cardf" property="idCardf" />
      <result column="id_cardzs" property="idCardzs" />
      <result column="studen_card" property="studenCard" />
      <result column="create_time" property="createTime" />
      <result column="success_time" property="successTime" />
      <result column="address" property="address" />
      <result column="remarks" property="remarks" />
      <result column="site" property="site" />
      <result column="msg" property="msg" />
      <result column="send_type" property="sendType" />
      <result column="now_package" property="nowPackage" />
      <result column="business_package" property="businessPackage" />
      <result column="user_bussiness_type" property="userBussinessType" />
      <result column="user_type" property="userType" />
      <result column="accept_result" property="acceptResult" />
      <result column="accept_name" property="acceptName" />
      <result column="accept_comment" property="acceptComment" />
      <result column="accept_date" property="acceptDate" />
      <result column="yj_count" property="yjCount" />
      <result column="yj_type" property="yjType" />
      <result column="tj_type" property="tjType" />
        <result column="xb_type" property="xbType" />
        <result column="net_number" property="netNumber" />
        <result column="net_password" property="netPassword" />
        <result column="userSchool" property="userSchool" />
        <result column="check_fail" property="checkFail" />
        <result column="expenses" property="expenses" />
        <result column="identifying" property="identifying" />
        <result column="kd_order_id" property="kdOrderId" />
        <result column="gift_account" property="giftAccount" />
        <result column="gift_password" property="giftCypher" />
        <result column="hehuoren_area" property="hehuorenArea" />
        <result column="hehuoren_name" property="hehuorenName" />
        <result column="hehuoren_school" property="hehuorenSchool" />
        <result column="hehuoren_phone" property="hehuorenPhone" />
        <result column="supervisor_name" property="supervisorName" />
        <result column="th_status" property="thStatus" />
        <result column="rh_time" property="rhTime" />
        <result column="orderSeq" property="orderSeq" />
        <result column="productName" property="productName" />
        <result column="order_set_meal" property="orderSetMeal" />
        <result column="order_university_name" property="orderUniversityName" />
        <result column="orderName" property="orderName" />
        <result column="orderDate" property="orderDate" />
        <result column="status" property="status" />
        <result column="order_region" property="orderRegion" />
        <result column="ipay" property="iPay" />
        <result column="payType" property="payType" />
        <result column="webOrderAmount" property="webOrderAmount" />
        <result column="order_payment_time" property="orderPaymentTime" />
        <result column="orderReqtranSeq" property="orderReqtranSeq" />
        <result column="customeRid" property="customerId" />
        <result column="orderPhone" property="orderPhone" />
        <result column="order_customer_type" property="orderCustomerType" />
        <result column="order_customer_student_id" property="orderCustomerStudentId" />
        <result column="order_customer_remarks" property="orderCustomerRemarks" />
        <result column="order_customer_account" property="orderCustomerAccount" />
        <result column="order_customer_pwd" property="orderCustomerPwd" />
        <result column="uptranseq" property="uptranseq" />
        <result column="expenses" property="expenses" />
        <result column="order_rf2" property="orderRf2"></result>
        <result column="orderSeqTHD" property="orderseqThd" />
   </resultMap>

   <select id="selectBroadBandOrderList" resultMap="broadBandOrder">
      select
          ho.*,
          ov.productName,
          ov.order_set_meal,
          ov.order_university_name,
          ov.orderName,
          ov.ipay,
          ov.payType,
          ov.webOrderAmount,
          ov.order_payment_time,
          ov.orderReqtranSeq,
          ov.customeRid,
          ov.orderPhone,
          ov.order_customer_type,
          ov.order_customer_student_id,
          ov.order_customer_remarks,
          ov.order_customer_account,
          ov.order_customer_pwd,
          ov.uptranseq,
          ov.expenses,
          ov.order_rf2,
          ov.orderSeqTHD,
          ov.order_region,
          ov.orderDate
      from (
          select * from hhr_order
            where
            kd_order_id is not null
            and user_type in (3,5,9,10)
           <if test="createTimeStart != null and createTimeStart != ''">
               and create_time &gt;= #{createTimeStart}
           </if>
           <if test="createTimeEnd != null and createTimeEnd != ''">
               and create_time &lt;= #{createTimeEnd}
           </if>
           <if test="successTimeStart != null and successTimeStart != ''">
               and success_time = &gt;= #{successTimeStart}
           </if>
           <if test="successTimeEnd != null and successTimeEnd != ''">
               and success_time = &lt;= #{successTimeEnd}
           </if>
           <if test="userType != null and userType != ''">
               and user_type = #{userType}
           </if>
           <if test="hehuorenArea != null and hehuorenArea != ''">
               and hehuoren_area = #{hehuorenArea}
           </if>
           <if test="orderStatus != null and orderStatus != ''">
               and order_status = #{orderStatus}
           </if>
           <if test="hehuorenSchool != null and hehuorenSchool != ''">
               and hehuoren_school = #{hehuorenSchool}
           </if>
           <if test="orderNumber != null and orderNumber != ''">
               and order_number = #{orderNumber}
           </if>
           <if test="hehuorenName != null and hehuorenName != ''">
               and hehuoren_name = #{hehuorenName}
           </if>
           <if test="contactNumber != null and contactNumber != ''">
               and contact_number = #{contactNumber}
           </if>
           <if test="businessNumber != null and businessNumber != ''">
               and business_number = #{businessNumber}
           </if>
           <if test="customerName != null and customerName != ''">
               and customer_name = #{customerName}
           </if>
           <if test="orderId != null and orderId != ''">
               and order_id = #{orderId}
           </if>
          ) ho,order_view ov
      where
          ho.kd_order_id = ov.orderSeq
      order by create_time desc
   </select>

    <select id="selectOrderIdByOrderNumber" parameterType="String" resultType="com.winsun.bean.Order">
        select * from hhr_order where  order_number = #{orderNumber}
    </select>

    <select id="selectRepeatOrder" parameterType="String" resultType="com.winsun.bean.Order">
        select * from hhr_order where user_type in('3','5','9','10') and order_status !='异常单' and id_card=#{idCard}
    </select>

    <select id="selectBroadList" parameterType="String" resultType="HashMap">
        select * from hhr_kd_dmt where account_number = #{accountNumber}
    </select>


    <select id="selectReportOrder" parameterType="String" resultType="com.winsun.bean.OrderView">
        select * from order_view where orderSeq = #{orderSeq}
    </select>

    <select id="checkOldKdUser" parameterType="String" resultType="HashMap">
        select * from hhr_kd_dmt_1 where account_number = #{accountNumber}
    </select>

    <select id="checkOldhhrOrder" parameterType="String" resultType="HashMap">
        select * from hhr_order where order_status !='异常单' and  net_number = #{accountNumber}
    </select>

    <update id="updateCompleteOrder">
        UPDATE hhr_order SET order_status = '已完成' WHERE id IN (SELECT id FROM(SELECT id FROM hhr_order WHERE order_status='已发货' AND TO_DAYS(NOW()) - TO_DAYS(create_time) > 7) t1)
    </update>

    <select id="selectKdOrder" parameterType="String" resultType="HashMap">
        select * from hhr_order where order_status !='异常单' and  kd_order_id = #{KdOrderID}
    </select>

    <!-- 更新未下单并且融合时间小于当前时间的订单 -->
    <update id="updateNosendAndRhTimeLtNowOrder">
        update hhr_order set order_status = "待受理" where DATE_FORMAT(rh_time, "%y-%m-%d") &lt; date_format(now(), "%y-%m-%d") and order_status = "未下单"
    </update>

</mapper>