<?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.hzs.sale.order.mapper.SaOrderMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="SaOrder" type="com.hzs.common.domain.sale.order.SaOrder">
        <id column="PK_ID" property="pkId"/>
        <result column="ORDER_CODE" property="orderCode"/>
        <result column="ORDER_TYPE" property="orderType"/>
        <result column="PK_RATE" property="pkRate"/>
        <result column="ORDER_AMOUNT" property="orderAmount"/>
        <result column="ORDER_ACHIEVE" property="orderAchieve"/>
        <result column="POSTAGE" property="postage"/>
        <result column="PK_MEMBER" property="pkMember"/>
        <result column="REC_NAME" property="recName"/>
        <result column="REC_PHONE" property="recPhone"/>
        <result column="REC_PROVINCE" property="recProvince"/>
        <result column="REC_CITY" property="recCity"/>
        <result column="REC_COUNTY" property="recCounty"/>
        <result column="REC_ADDRESS" property="recAddress"/>
        <result column="PAY_TIME" property="payTime"/>
        <result column="REMARK" property="remark"/>
        <result column="PK_REFERENCE" property="pkReference"/>
        <result column="PK_PLACEMENT" property="pkPlacement"/>
        <result column="TRAN_TYPE" property="tranType"/>
        <result column="DELIVERY_WAY" property="deliveryWay"/>
        <result column="ORDER_STATUS" property="orderStatus"/>
        <result column="PK_VERTEX" property="pkVertex"/>
        <result column="PAY_TYPE" property="payType"/>
        <result column="DEL_FLAG" property="delFlag"/>
        <result column="PK_CREATOR" property="pkCreator"/>
        <result column="CREATION_TIME" property="creationTime"/>
        <result column="PK_MODIFIED" property="pkModified"/>
        <result column="MODIFIED_TIME" property="modifiedTime"/>
        <result column="PK_COUNTRY" property="pkCountry"/>
        <result column="PK_MEMBER_STORE" property="pkMemberStore"/>
        <result column="PK_STOREHOUSE" property="pkStorehouse"/>
        <result column="PK_MEMBER_STORE" property="pkMemberStore"/>
        <result column="PK_ORIGINAL_ORDER" property="pkOriginalOrder"/>
        <result column="UPLOAD_ACHIEVE" property="uploadAchieve"/>
        <result column="ORDER_TYPE_EXTEND" property="orderTypeExtend"/>
        <result column="CONSUME_AMOUNT" property="consumeAmount"/>
        <result column="SYSTEM_TYPE" property="systemType"/>
        <result column="BOX_NUM" property="boxNum"/>
    </resultMap>

    <!-- 订单VO查询映射结果 -->
    <resultMap id="SaOrderVO" type="com.hzs.sale.order.vo.SaOrderVO">
        <result column="ORDER_CODE" property="orderCode"/>
        <result column="ORDER_TYPE" property="orderType"/>
        <result column="PK_RATE" property="pkRate"/>
        <result column="ORDER_AMOUNT" property="orderAmount"/>
        <result column="ORDER_ACHIEVE" property="orderAchieve"/>
        <result column="ORDER_ASS_ACHIEVE" property="orderAssAchieve"/>
        <result column="POSTAGE" property="postage"/>
        <result column="MEMBER_CODE" property="memberCode"/>
        <result column="MEMBER_NAME" property="memberName"/>
        <result column="REFERENCE" property="reference"/>
        <result column="REFERENCE_NAME" property="referenceName"/>
        <result column="REC_NAME" property="recName"/>
        <result column="REC_PHONE" property="recPhone"/>
        <result column="REC_PROVINCE" property="recProvince"/>
        <result column="REC_CITY" property="recCity"/>
        <result column="REC_COUNTY" property="recCounty"/>
        <result column="REC_ADDRESS" property="recAddress"/>
        <result column="PAY_TIME" property="payTime"/>
        <result column="REMARK" property="remark"/>
        <result column="TRAN_TYPE" property="tranType"/>
        <result column="DELIVERY_WAY" property="deliveryWay"/>
        <result column="VERTEX" property="vertex"/>
        <result column="VERTEX_NAME" property="vertexName"/>
        <result column="DEL_FLAG" property="delFlag"/>
        <result column="PK_CREATOR" property="pkCreator"/>
        <result column="CREATION_TIME" property="creationTime"/>
        <result column="PK_MODIFIED" property="pkModified"/>
        <result column="MODIFIED_TIME" property="modifiedTime"/>
    </resultMap>

    <!-- 打印订单映射map -->
    <resultMap id="orderPrintMap" type="com.hzs.sale.order.vo.MyOrderPrintVO">
        <result column="member_code" property="memberCode"/>
        <result column="member_name" property="memberName"/>
        <result column="phone" property="phone"/>
        <result column="order_code" property="orderCode"/>
        <result column="pay_time" property="payTime"/>
        <result column="rec_name" property="recName"/>
        <result column="rec_phone" property="recPhone"/>
        <result column="rec_province" property="recProvince"/>
        <result column="rec_city" property="recCity"/>
        <result column="rec_county" property="recCounty"/>
        <result column="rec_address" property="recAddress"/>
        <collection property="detailList" ofType="com.hzs.sale.order.vo.MyOrderPrintDetailVO">
            <result column="product_name" property="productName"/>
            <result column="quantity" property="quantity"/>
            <result column="price" property="price"/>
            <result column="achievement" property="achievement"/>
            <result column="ass_achievement" property="assAchievement"/>
        </collection>
    </resultMap>

    <!-- 订单+详情返回映射 -->
    <resultMap id="saOrderExtResultMap" type="com.hzs.common.domain.sale.ext.SaOrderExt" extends="SaOrder">
        <result column="MEMBER_CODE" property="memberCode"/>
        <result column="MEMBER_NAME" property="memberName"/>
        <result column="CREATOR_CODE" property="creatorCode"/>
        <result column="PK_SETTLE_COUNTRY" property="pkSettleCountry"/>
        <result column="PK_CENTER_CODE" property="pkCenterCode"/>
        <result column="PK_STORE_LEVEL" property="pkStoreLevel"/>
        <result column="IN_EXCHANGE_RATE" property="inExchangeRate"/>
        <result column="PK_CORP" property="pkCorp"/>
        <collection property="orderItemsExtList" ofType="com.hzs.common.domain.sale.ext.SaOrderItemsExt">
            <result column="ITEMS_ID" property="pkId"/>
            <result column="PK_UNIT" property="pkUnit"/>
            <result column="PK_PRODUCT" property="pkProduct"/>
            <result column="PRODUCT_NAME" property="productName"/>
            <result column="PRODUCT_CODE" property="productCode"/>
            <result column="COVER" property="cover"/>
            <result column="WEIGHT" property="weight"/>
            <result column="SPECS_NAME" property="specsName"/>
            <result column="SPECS_NAME_ID" property="specsNameId"/>
            <result column="QUANTITY" property="quantity"/>
            <result column="WARES_QUANTITY" property="waresQuantity"/>
            <result column="PRICE" property="price"/>
            <result column="ACHIEVEMENT" property="achievement"/>
            <result column="ASS_ACHIEVEMENT" property="assAchievement"/>
            <result column="ORDER_STATUS" property="orderStatus"/>
            <result column="IS_GIFT" property="isGift"/>
            <result column="PK_ORDER" property="pkOrder"/>
            <result column="PK_WARES" property="pkWares"/>
            <result column="PK_WARES_DETAIL" property="pkWaresDetail"/>
            <result column="PK_WARES_SPECS_SKU" property="pkWaresSpecsSku"/>
            <result column="PK_GRADE" property="pkGrade"/>
            <result column="PK_AWARDS" property="pkAwards"/>
            <result column="EXCHANGE_GOODS" property="exchangeGoods"/>
            <result column="IS_MAKER_GIFT" property="isMakerGift"/>
            <result column="EDIT_FLAG" property="editFlag"/>
            <result column="WARES_CODE" property="waresCode"/>
            <result column="MAKER_INCOME" property="makerIncome"/>
            <result column="ITEM_PK_STOREHOUSE" property="pkStorehouse"/>
            <result column="USE_PICK" property="usePick"/>
        </collection>
        <collection property="orderBonusList" ofType="com.hzs.common.domain.sale.order.SaOrderBonus">
            <result column="PK_BONUS_ORDER" property="pkOrder"/>
            <result column="PK_BONUS_ITEMS" property="pkBonusItems"/>
        </collection>
    </resultMap>

    <select id="querySaOrderByCondition" resultMap="SaOrderVO">
        select so.order_code,so.order_type,so.pk_rate,so.order_amount,so.order_achieve,so.postage,
        cm.member_code,cm.member_name,par.member_code REFERENCE,par.member_name referenceName,so.rec_name,so.rec_phone,
        so.rec_province,so.rec_city,so.rec_county,so.rec_address,so.pay_time,so.remark,
        so.tran_type,so.delivery_way,ve.member_code vertex,ve.member_name vertexName from sa_order so
        inner join cu_member cm
        on so.pk_member = cm.pk_id
        left join cu_member par
        on so.pk_reference = par.pk_id
        left join cu_member ve
        on so.pk_vertex = ve.pk_id
        where so.del_flag=0 and cm.del_flag=0
        and so.pk_country = #{pkCountry}
        <if test="orderStatus != null">
            and so.order_status=#{orderStatus}
        </if>
        <if test="orderCode != null and orderCode != ''">
            and so.order_code like #{orderCode} || '%'
        </if>
        <if test="memberCode != null and memberCode != ''">
            and cm.member_code like #{memberCode} || '%'
        </if>
        <if test="memberName != null and memberName != ''">
            and cm.member_name like #{memberName} || '%'
        </if>
        <if test="parentCode != null and parentCode != ''">
            and par.member_code like #{parentCode} || '%'
        </if>
        <if test="parentName != null and parentName != ''">
            and par.member_name like #{parentName} || '%'
        </if>
        <if test="orderType != null">
            and so.order_type = #{orderType}
        </if>
        <if test="recName != null and recName !=''">
            and so.rec_name like #{recName} || '%'
        </if>
        <if test="recPhone != null and recPhone != ''">
            and so.rec_phone like #{recPhone} || '%'
        </if>
        <if test="startTime != null and endTime != ''">
            and so.pay_time >= #{startTime}
            and so.pay_time &lt; #{endTime}
        </if>
        <if test="tranType != null">
            and so.tran_type = #{tranType}
        </if>
    </select>

    <select id="selectSum" resultType="decimal">
        select ifnull(sum(ORDER_AMOUNT), 0)
        from SA_ORDER
        where DEL_FLAG = '0'
          and ORDER_STATUS = '1'
    </select>

    <!-- 查询订单相关字段 -->
    <sql id="orderSelect">
        so.*,
        cm.member_code,
        cm.member_name,
        cm.pk_settle_country,
        soi.pk_id items_id,
        soi.pk_product,
        bp.product_name,
        bp.product_code,
        bp.cover,
        bp.weight,
        soi.specs_name,
        soi.specs_name_id,
        soi.quantity,
        soi.price,
        soi.achievement,
        soi.ass_achievement,
        soi.order_status,
        soi.is_gift,
        bc.in_exchange_rate
    </sql>

    <!-- 根据订单id查询订单 -->
    <select id="queryOrderByPkId" resultMap="saOrderExtResultMap">
        select
        <include refid="orderSelect"/>
        from sa_order so
        inner join cu_member cm
        on cm.pk_id = so.pk_member
        left join sa_order_items soi
        on soi.pk_order = so.pk_id
        and soi.del_flag = 0
        inner join bd_product bp
        on bp.pk_id = soi.pk_product
        inner join bd_currency bc
        on bc.pk_id = so.pk_rate
        where so.del_flag = 0
        and so.pk_id = #{pkId}
        <if test="null != pkCountry">
            and so.pk_country = #{pkCountry}
        </if>
    </select>

    <!-- 根据订单编号查询订单 -->
    <select id="queryOrderByCode" resultMap="saOrderExtResultMap">
        select
        so.*,
        cm.member_code,
        cm.member_name,
        cm.pk_settle_country,
        soi.pk_id items_id,
        soi.pk_product,
        soi.pk_wares,
        bp.product_name,
        bp.product_code,
        bp.cover,
        bp.weight,
        soi.specs_name,
        soi.specs_name_id,
        soi.quantity,
        soi.price,
        soi.achievement,
        soi.ass_achievement,
        soi.order_status,
        soi.is_gift,
        soi.soi.wares_quantity,
        bc.in_exchange_rate,
        case
        when soi.order_status in (1, 2) then
        0
        else
        1
        end EDIT_FLAG
        from sa_order so
        inner join cu_member cm
        on cm.pk_id = so.pk_member
        left join sa_order_items soi
        on soi.pk_order = so.pk_id
        and soi.del_flag = 0
        inner join bd_product bp
        on bp.pk_id = soi.pk_product
        inner join bd_currency bc
        on bc.pk_id = so.pk_rate
        where so.del_flag = 0
        and so.order_code = #{orderCode}
        <if test="null != pkCountry">
            and so.pk_country = #{pkCountry}
        </if>
    </select>

    <!-- 根据订单编号查询订单以及详情 -->
    <select id="queryOrderListByCodeList" resultMap="saOrderExtResultMap">
        select
        <include refid="orderSelect"/>
        from sa_order so
        inner join cu_member cm
        on cm.pk_id = so.pk_member
        left join sa_order_items soi
        on soi.pk_order = so.pk_id
        and soi.del_flag = 0
        inner join bd_product bp
        on bp.pk_id = soi.pk_product
        inner join bd_currency bc
        on bc.pk_id = so.pk_rate
        where so.del_flag = 0
        and so.order_code in
        <foreach collection="orderCodeList" item="item" open="(" close=")" separator=",">
            #{item}
        </foreach>
        <if test="null != pkCountry">
            and so.pk_country = #{pkCountry}
        </if>
        order by so.order_code desc
    </select>

    <!-- 根据订单编号查询订单以及详情 -->
    <select id="queryOrderListByIdList" resultMap="saOrderExtResultMap">
        select
        <include refid="orderSelect"/>
        from sa_order so
        inner join cu_member cm
        on cm.pk_id = so.pk_member
        left join sa_order_items soi
        on soi.pk_order = so.pk_id
        and soi.del_flag = 0
        inner join bd_product bp
        on bp.pk_id = soi.pk_product
        inner join bd_currency bc
        on bc.pk_id = so.pk_rate
        where so.del_flag = 0
        and so.pk_id in
        <foreach collection="orderIdList" item="item" open="(" close=")" separator=",">
            #{item}
        </foreach>
        <if test="null != pkCountry">
            and so.pk_country = #{pkCountry}
        </if>
        order by so.order_code desc, soi.pk_id
    </select>

    <!-- 我的订单列表（前台） -->
    <select id="myOrderList" resultType="com.hzs.common.domain.sale.ext.SaOrderExt">
        <!-- 直销订单 -->
        select
        so.pk_id,
        so.order_code,
        so.creation_time,
        so.pay_time,
        so.order_type,
        so.order_amount,
        so.order_achieve,
        so.order_ass_achieve,
        so.postage,
        cm2.member_code creator_code,
        cm2.member_name creator_name,
        cm2.service_code creator_service_code,
        cm2.nick_name creator_nick_name,
        cm2.pk_vertex creator_pk_vertex,
        cm1.member_code member_code,
        cm1.member_name member_name,
        cm1.phone member_phone,
        cm1.service_code member_service_code,
        cm1.nick_name member_nick_name,
        cm1.pk_vertex member_pk_vertex,
        so.order_status,
        so.rec_name,
        so.rec_phone,
        so.rec_province,
        so.rec_city,
        so.rec_county,
        so.rec_address,
        so.remark,
        so.pk_country,
        so.pk_member,
        so.pk_creator,
        0 as merchant_type,
        bc.in_exchange_rate
        from (
        select distinct so.*
        from sa_order so
        <if test="param.orderStatus != null">
            left join sa_order_items soi
            on soi.pk_order = so.pk_id
            and soi.del_flag = 0
        </if>
        where so.del_flag = 0
        <if test="pkCountry != null">
            and so.pk_country = #{pkCountry}
        </if>
        and (so.pk_member = #{memberId} or so.pk_creator = #{memberId})
        <if test="param.orderStatus != null">
            <choose>
                <when test="param.orderStatus == 1">
                    <!-- 前台如果查询待发货，需要包括已合单 -->
                    and soi.order_status in (1, 2)
                </when>
                <otherwise>
                    and soi.order_status = #{param.orderStatus}
                </otherwise>
            </choose>
        </if>
        <if test="param.creationTimeStart != null">
            and so.creation_time >= #{param.creationTimeStart}
        </if>
        <if test="param.creationTimeEnd != null">
            and #{param.creationTimeEnd} >= so.creation_time
        </if>
        <if test="param.orderCode != null and param.orderCode != ''">
            and so.order_code like #{param.orderCode} || '%'
        </if>
        <if test="param.orderType != null">
            and so.order_type = #{param.orderType}
        </if>
        ) so
        inner join cu_member cm1
        on cm1.pk_id = so.pk_member
        inner join cu_member cm2
        on cm2.pk_id = so.pk_creator
        inner join bd_currency bc
        on bc.pk_id = so.pk_rate
        <if test="param.memberCode != null and param.memberCode != ''">
            where cm1.member_code like #{param.memberCode} || '%'
        </if>
        order by creation_time desc
    </select>
    <!--    自助撤单列表-->
    <select id="selfRevokeOrderList" resultType="com.hzs.common.domain.sale.ext.SaOrderExt">
        <!-- 直销订单 -->
        select
        so.pk_id,
        so.PK_CREATOR pkCreator,
        so.PK_MEMBER pkMember,
        so.order_code,
        so.creation_time,
        so.pay_time,
        so.order_type,
        so.order_amount,
        so.order_achieve,
        so.order_ass_achieve,
        so.postage,
        cm2.member_code creator_code,
        cm2.member_name creator_name,
        cm1.member_code member_code,
        cm1.member_name member_name,
        cm1.phone member_phone,
        cm1.SERVICE_CODE memberServiceCode,
        cm1.NICK_NAME memberNickName,
        cm1.PK_VERTEX memberPkVertex,
        so.order_status,
        so.rec_name,
        so.rec_phone,
        so.rec_province,
        so.rec_city,
        so.rec_county,
        so.rec_address,
        so.remark,
        so.pk_country,
        0 as merchant_type,
        bc.in_exchange_rate
        from (
        select distinct so.*
        from sa_order so
        <if test="param.orderStatus != null">
            left join sa_order_items soi
            on soi.pk_order = so.pk_id
            and soi.del_flag = 0
        </if>
        where so.del_flag = 0
        and so.order_type not in (13,30)
        AND TO_CHAR(so.PAY_TIME,'YYYY-MM-DD') = TO_CHAR(SYSDATE, 'YYYY-MM-DD')
        <if test="pkCountry != null">
            and so.pk_country = #{pkCountry}
        </if>
        and so.pk_creator = #{memberId}
        <if test="param.orderStatus != null">
            <choose>
                <when test="param.orderStatus == 1">
                    <!-- 前台如果查询待发货，需要包括已合单 -->
                    and soi.order_status in (1, 2)
                </when>
                <otherwise>
                    and soi.order_status = #{param.orderStatus}
                </otherwise>
            </choose>
        </if>
        <if test="param.creationTimeStart != null">
            and so.creation_time >= #{param.creationTimeStart}
        </if>
        <if test="param.creationTimeEnd != null">
            and #{param.creationTimeEnd} >= so.creation_time
        </if>
        <if test="param.orderCode != null and param.orderCode != ''">
            and so.order_code like #{param.orderCode} || '%'
        </if>
        ) so
        inner join cu_member cm1
        on cm1.pk_id = so.pk_member
        inner join cu_member cm2
        on cm2.pk_id = so.pk_creator
        inner join bd_currency bc
        on bc.pk_id = so.pk_rate
        <if test="param.memberCode != null and param.memberCode != ''">
            where cm1.member_code like #{param.memberCode} || '%'
        </if>
        order by creation_time desc
    </select>

    <!-- 我的订单数量（前台） -->
    <select id="myOrderNum" resultType="int">
        select sum(pk_id)
        from (select count(so.pk_id) pk_id
        from sa_order so
        where del_flag = 0
        and so.pk_country = #{pkCountry}
        and (so.pk_member = #{memberId} or so.pk_creator = #{memberId})
        and exists (select 1
        from sa_order_items soi
        where soi.pk_order = so.pk_id
        and soi.del_flag = 0
        and soi.pk_product != 0
        and soi.order_status in
        <foreach collection="statusList" item="item" open="(" close=")" separator=",">
            #{item}
        </foreach>
        )
        )
    </select>

    <!-- 订单业务列表 -->
    <select id="queryOrderBusinessList" resultType="com.hzs.sale.order.vo.OrderBusinessVO">
        select so.*,
        cm.member_code,
        cm.member_name,
        bp.product_name,
        soi.specs_name,
        soi.price,
        soi.achievement,
        soi.ass_achievement,
        soi.quantity,
        bc.in_exchange_rate
        from sa_order so
        inner join cu_member cm
        on cm.pk_id = so.pk_member
        left join sa_order_items soi
        on soi.pk_order = so.pk_id
        and soi.del_flag = 0
        inner join bd_product bp
        on bp.pk_id = soi.pk_product
        inner join bd_currency bc
        on bc.pk_id = so.pk_rate
        where so.del_flag = 0
        and so.pk_country = #{pkCountry}
        <!-- 订单显示的时候，待支付只有注册订单显示，其它状态不做限制 -->
        and (
        soi.order_status in (1, 2, 3, 5)
        or (soi.order_status = 0 and so.order_type in (1))
        )
        <!-- 续约定单不能撤单
        and so.order_type != 30
         -->
        <if test="param.memberCode != null and param.memberCode != ''">
            and cm.member_code like #{param.memberCode} || '%'
        </if>
        <if test="param.memberName != null and param.memberName != ''">
            and cm.member_name like #{param.memberName} || '%'
        </if>
        <if test="param.orderCode != null and param.orderCode != ''">
            and so.order_code like #{param.orderCode} || '%'
        </if>
        <if test="param.productName != null and param.productName != ''">
            and bp.product_name like #{param.productName} || '%'
        </if>
        <if test="param.orderType != null">
            and so.order_type = #{param.orderType}
        </if>
        <if test="param.recProvince != null">
            and so.rec_province = #{param.recProvince}
        </if>
        <if test="param.recCity != null">
            and so.rec_city = #{param.recCity}
        </if>
        <if test="param.recCounty != null">
            and so.rec_county = #{param.recCounty}
        </if>
        <if test="param.recAddress != null and param.recAddress != ''">
            and so.rec_address like #{param.recAddress} || '%'
        </if>
        <if test="param.creationTime != null">
            and soi.creation_time >= #{param.creationTime[0]}
            and #{param.creationTime[1]} >= soi.creation_time
        </if>
        <if test="param.payTime != null">
            and so.pay_time >= #{param.payTime[0]}
            and #{param.payTime[1]} >= so.pay_time
        </if>
        <if test="param.orderCodeList != null and param.orderCodeList.size > 0">
            and so.order_code in
            <foreach collection="param.orderCodeList" item="item" open="(" close=")" separator=",">
                #{item}
            </foreach>
        </if>
        <if test="param.memberCodeList != null and param.memberCodeList.size > 0">
            and cm.member_code in
            <foreach collection="param.memberCodeList" item="item" open="(" close=")" separator=",">
                #{item}
            </foreach>
        </if>
        <!-- 地区权限处理 -->
        <if test="param.areaScopeList != null and param.areaScopeList.size > 0">
            and so.rec_province in
            <foreach collection="param.areaScopeList" item="item" open="(" close=")" separator=",">
                #{item}
            </foreach>
        </if>
        <!-- 体系权限处理 -->
        <if test="param.vertexIdList != null and param.vertexIdList.size > 0">
            and so.pk_vertex in
            <foreach collection="param.vertexIdList" item="item" open="(" close=")" separator=",">
                #{item}
            </foreach>
        </if>
        order by so.creation_time desc
    </select>

    <select id="selectByOrderInfoList" resultType="com.hzs.sale.order.vo.WaresOrderVo">
        select
        so.PK_ID pkOrder,
        so.ORDER_CODE orderCode,
        so.ORDER_TYPE orderType,
        cm.MEMBER_CODE memberCode,
        cm.MEMBER_NAME memberName,
        cm.PHONE phone,
        so.REC_NAME recName,
        so.REC_PHONE recPhone,
        so.REC_PROVINCE recProvince,
        so.REC_CITY recCity,
        so.REC_COUNTY recCountry,
        so.REC_ADDRESS recAddress,
        so.POSTAGE postage,
        so.DELIVERY_WAY deliveryWay,
        so.TRAN_TYPE tranType,
        so.ORDER_STATUS orderStatus,
        so.PAY_TYPE payType,
        so.REMARK remark,
        so.CREATION_TIME creationTime,
        so.PAY_TIME payTime,
        so.ORDER_AMOUNT orderAmount,
        so.ORDER_ACHIEVE orderAchieve,
        so.box_num,
        m.MEMBER_CODE buyMemberCode,
        m.MEMBER_NAME buyMemberName,
        so.ORDER_ASS_ACHIEVE orderAssAchieve,
        ver.VERTEX_NAME vertexName
        from sa_order so
        left join CU_MEMBER cm on cm.PK_ID = so.PK_MEMBER
        left join CU_MEMBER m on m.PK_ID = so.PK_CREATOR
        left join BD_VERTEX ver on ver.PK_ID = cm.PK_VERTEX and ver.DEL_FLAG = 0
        where so.DEL_FLAG = 0
        <if test="addPurchaseFlag != null and addPurchaseFlag == 0">
            and so.PK_ID in (
                select distinct soi.pk_order
                from sa_order_items soi
                where soi.is_maker_gift = 3
                and soi.del_flag = 0
            )
        </if>
        <if test="pkCountry != null">
            and so.PK_COUNTRY = #{pkCountry}
        </if>
        <if test="vertexId != null">
            and cm.PK_VERTEX = #{vertexId}
        </if>
        <if test="buyMemberCode != null and buyMemberCode != ''">
            and m.MEMBER_CODE like #{buyMemberCode}||'%'
        </if>
        <if test="buyMemberName != null and buyMemberName != ''">
            and m.MEMBER_NAME like #{buyMemberName}||'%'
        </if>
        <if test="orderCode != null and orderCode != ''">
            and so.ORDER_CODE like '%' || #{orderCode}||'%'
        </if>
        <if test="memberCode != null and memberCode != ''">
            and cm.MEMBER_CODE like '%' || #{memberCode}||'%'
        </if>
        <if test="memberName != null and memberName != ''">
            and cm.MEMBER_NAME like '%' || #{memberName}||'%'
        </if>
        <if test="phone != null and phone != ''">
            and cm.PHONE like #{phone}||'%'
        </if>
        <if test="orderType != null">
            and so.ORDER_TYPE = #{orderType}
        </if>
        <if test="orderStatus != null">
            and so.ORDER_STATUS = #{orderStatus}
        </if>

        <if test="deliveryWay != null and deliveryWay != ''">
            and so.DELIVERY_WAY = #{deliveryWay}
        </if>
        <if test="tranType != null">
            and so.TRAN_TYPE = #{tranType}
        </if>
        <if test="payType != null">
            and so.PAY_TYPE = #{payType}
        </if>

        <if test="recName != null and recName != ''">
            and so.REC_NAME like '%' || #{recName} || '%'
        </if>
        <if test="recPhone != null and recPhone != ''">
            and so.REC_PHONE like '%' || #{recPhone} || '%'
        </if>
        <if test="recProvince != null">
            and so.REC_PROVINCE = #{recProvince}
        </if>
        <if test="recCity != null">
            and so.REC_CITY = #{recCity}
        </if>
        <if test="recCountry != null">
            and so.REC_COUNTY = #{recCountry}
        </if>
        <if test="recAddress != null and recAddress != ''">
            and so.REC_ADDRESS = #{recAddress}
        </if>
        <if test="startCreateDate != null">
            and so.CREATION_TIME &gt;= #{startCreateDate}
        </if>
        <if test="endCreateDate != null">
            and so.CREATION_TIME &lt;= #{endCreateDate}
        </if>
        <if test="startPayDate != null">
            and so.PAY_TIME &gt;= #{startPayDate}
        </if>
        <if test="endPayDate != null">
            and so.PAY_TIME &lt;= #{endPayDate}
        </if>
        <!-- 地区权限处理 -->
        <if test="areaScopeList != null and areaScopeList.size > 0">
            and so.rec_province in
            <foreach collection="areaScopeList" item="item" open="(" close=")" separator=",">
                #{item}
            </foreach>
        </if>
        <!-- 体系权限处理 -->
        <if test="vertexIdList != null and vertexIdList.size > 0">
            and cm.pk_vertex in
            <foreach collection="vertexIdList" item="item" open="(" close=")" separator=",">
                #{item}
            </foreach>
        </if>
        order by so.creation_time desc
    </select>

    <!-- 订单开票列表 -->
    <select id="listOrderInvoicing" resultType="com.hzs.sale.order.vo.OrderInvoicingVO">
        select
        so.pk_id pkorder,
        so.order_code,
        so.order_type,
        cm.member_code,
        cm.member_name,
        cm.phone,
        so.rec_name,
        so.rec_phone,
        so.rec_province,
        so.rec_city,
        so.rec_county,
        so.rec_address,
        so.order_amount,
        so.postage,
        so.order_achieve,
        so.order_ass_achieve,
        so.delivery_way,
        so.pay_type,
        so.remark,
        sor.remarks,
        so.system_type,
        so.creation_time,
        so.pay_time,
        top.pay_money
        from sa_order so
        left join cu_member cm on cm.pk_id = so.pk_member
        left join sa_order_remarks sor on sor.pk_order = so.pk_id and sor.del_flag = 0
        left join t_online_payment top on top.business_type in (1, 5) and top.business_code = so.order_code
        and top.del_flag = 0 and top.pay_status = 1
        where so.DEL_FLAG = 0
        <if test="pkCountry != null">
            and so.PK_COUNTRY = #{pkCountry}
        </if>
        <if test="orderCode != null and orderCode != ''">
            and so.ORDER_CODE like '%' || #{orderCode}||'%'
        </if>
        <if test="memberCode != null and memberCode != ''">
            and cm.MEMBER_CODE like '%' || #{memberCode}||'%'
        </if>
        <if test="memberName != null and memberName != ''">
            and cm.MEMBER_NAME like '%' || #{memberName}||'%'
        </if>
        <if test="phone != null and phone != ''">
            and cm.PHONE like #{phone}||'%'
        </if>
        <if test="orderType != null">
            and so.ORDER_TYPE = #{orderType}
        </if>
        <if test="deliveryWay != null">
            and so.DELIVERY_WAY = #{deliveryWay}
        </if>
        <if test="recName != null and recName != ''">
            and so.REC_NAME like '%' || #{recName} || '%'
        </if>
        <if test="recPhone != null and recPhone != ''">
            and so.REC_PHONE like '%' || #{recPhone} || '%'
        </if>
        <if test="recProvince != null">
            and so.REC_PROVINCE = #{recProvince}
        </if>
        <if test="recCity != null">
            and so.REC_CITY = #{recCity}
        </if>
        <if test="recCountry != null">
            and so.REC_COUNTY = #{recCountry}
        </if>
        <if test="recAddress != null and recAddress != ''">
            and so.REC_ADDRESS like '%' || #{recAddress}
        </if>
        <if test="payType != null">
            and so.PAY_TYPE = #{payType}
        </if>
        <if test="startCreateDate != null">
            and so.CREATION_TIME &gt;= #{startCreateDate}
        </if>
        <if test="endCreateDate != null">
            and so.CREATION_TIME &lt;= #{endCreateDate}
        </if>
        <if test="startPayDate != null">
            and so.PAY_TIME &gt;= #{startPayDate}
        </if>
        <if test="endPayDate != null">
            and so.PAY_TIME &lt;= #{endPayDate}
        </if>
        <if test="systemType != null">
            and so.SYSTEM_TYPE = #{systemType}
        </if>
        <!-- 地区权限处理 -->
        <if test="areaScopeList != null and areaScopeList.size > 0">
            and so.rec_province in
            <foreach collection="areaScopeList" item="item" open="(" close=")" separator=",">
                #{item}
            </foreach>
        </if>
        <!-- 体系权限处理 -->
        <if test="vertexIdList != null and vertexIdList.size > 0">
            and cm.pk_vertex in
            <foreach collection="vertexIdList" item="item" open="(" close=")" separator=",">
                #{item}
            </foreach>
        </if>
        order by so.creation_time desc
    </select>

    <!-- 查询订单以及会员信息 -->
    <select id="queryOrderMemberInfo" resultMap="saOrderExtResultMap">
        select so.*, cm.member_code, cm.member_name, cm.pk_center_code
        from sa_order so
        inner join cu_member cm
        on cm.pk_id = so.pk_member
        where so.del_flag = 0
        <if test="null != pkCountry">
            and so.pk_country = #{pkCountry}
        </if>
        <if test="pkMemberList != null and pkMemberList.size > 0">
            and so.pk_member in
            <foreach collection="pkMemberList" item="item" open="(" close=")" separator=",">
                #{item}
            </foreach>
        </if>
        <if test="orderTypeList != null and orderTypeList.size > 0">
            and so.order_type in
            <foreach collection="orderTypeList" item="item" open="(" close=")" separator=",">
                #{item}
            </foreach>
        </if>
        <if test="orderCodeList != null and orderCodeList.size > 0">
            and so.order_code in
            <foreach collection="orderCodeList" item="item" open="(" close=")" separator=",">
                #{item}
            </foreach>
        </if>
    </select>

    <!-- 查询选择订单列表 -->
    <select id="queryOrderSelect" resultType="com.hzs.common.domain.sale.ext.SaOrderItemsExt">
        select soi.pk_id,
        soi.pk_order,
        so.order_code,
        so.pk_member,
        cm.member_code,
        bp.product_name || '(' || soi.specs_name || ')' product_name,
        soi.quantity,
        so.rec_name,
        so.rec_phone,
        so.rec_province,
        so.rec_city,
        so.rec_county,
        so.rec_address,
        sd.logistics_code,
        sd.logistics_company
        from sa_order so
        inner join cu_member cm
        on cm.pk_id = so.pk_member
        left join sa_order_items soi
        on soi.pk_order = so.pk_id
        inner join bd_product bp
        on bp.pk_id = soi.pk_product
        left join sa_deliver_items sdi
        on sdi.pk_order = soi.pk_order
        and sdi.pk_order_items = sdi.pk_id
        left join sa_deliver sd
        on sd.pk_id = sdi.pk_deliver
        where so.del_flag = 0
        and so.pk_original_order is not null
        <if test="pkCountry != null">
            and so.pk_country = #{pkCountry}
        </if>
        <if test="param.orderCode != null and param.orderCode != ''">
            and so.order_code = #{param.orderCode}
        </if>
        <if test="param.memberCode != null and param.memberCode != ''">
            and cm.member_code like #{param.memberCode} || '%'
        </if>
        <if test="param.productName != null and param.productName != ''">
            and bp.product_name like #{param.productName} || '%'
        </if>
        <if test="param.recName != null and param.recName != ''">
            and so.rec_name = like #{param.recName} || '%'
        </if>
        <if test="param.recPhone != null and param.recPhone != ''">
            and so.rec_phone = #{param.recPhone}
        </if>
        <if test="param.recProvince != null">
            and so.rec_province = #{param.recProvince}
        </if>
        <if test="param.recCity != null">
            and so.rec_city = #{param.recCity}
        </if>
        <if test="param.recCounty != null">
            and so.rec_county = #{param.recCounty}
        </if>
        <if test="param.recAddress != null and param.recAddress != ''">
            and so.rec_address like #{param.recAddress} || '%'
        </if>
        <if test="param.logisticsCode != null and param.logisticsCode != ''">
            and sd.logistics_code like #{param.logisticsCode} || '%'
        </if>
        <if test="param.logisticsCompany != null and param.logisticsCompany != ''">
            sd.logistics_company like #{param.logisticsCompany} || '%'
        </if>
        <if test="param.pkOrderList != null and param.pkOrderList.size > 0">
            and so.pk_id in
            <foreach collection="param.pkOrderList" item="item" open="(" close=")" separator=",">
                #{item}
            </foreach>
        </if>
        <if test="param.orderCodeList != null and param.orderCodeList.size > 0">
            and so.order_code in
            <foreach collection="param.orderCodeList" item="item" open="(" close=")" separator=",">
                #{item}
            </foreach>
        </if>
    </select>

    <!-- 定时关单 -->
    <update id="closeOrder">
        update sa_order
        set DEL_FLAG      = 1,
            MODIFIED_TIME = SYSDATE
        where (sysdate - interval '${data}' hour) > CREATION_TIME
          and ORDER_STATUS = 0
          and DEL_FLAG = 0
          and PK_COUNTRY = #{pkCountry}
          and ORDER_TYPE = #{orderType}
    </update>

    <update id="updateSaOrderByMember">
        update SA_ORDER
        set DEL_FLAG = 1,
        PK_MODIFIED = #{pkApprove},
        MODIFIED_TIME = #{currentDateTime}
        where del_flag= 0 and order_type in (1,4,7,20,21,24) and pk_member in
        <foreach collection="memberList" item="item" open="(" close=")" separator=",">
            #{item}
        </foreach>
    </update>

    <!-- 自助服务可申请订单列表 -->
    <select id="selfOrderList" resultType="com.hzs.common.domain.sale.ext.SaOrderExt">

        select * from (
        select distinct so.*,
        cm1.member_code member_code,
        cm1.MEMBER_NAME memberName,
        cm1.SERVICE_CODE memberServiceCode,
        cm1.NICK_NAME memberNickName,
        cm1.PK_VERTEX memberPkVertex,
        null approveStatus,
        null selfCode,
        null rejectMsg
        from (select so.*
        from sa_order so
        inner join cu_member cm
        on cm.pk_id = so.pk_member
        where so.del_flag = 0
        and order_status in (1)
        and so.pk_country = #{pkCountry}
        and so.pk_creator = #{memberId}
        AND TO_CHAR(so.PAY_TIME,'YYYY-MM-DD') = TO_CHAR(SYSDATE, 'YYYY-MM-DD')
        <if test="param.orderCode != null and param.orderCode != ''">
            and so.order_code like #{param.orderCode} || '%'
        </if>
        ) so
        inner join cu_member cm1
        on cm1.pk_id = so.pk_member
        left JOIN SA_ORDER_SELF_LOG css on css.PK_ORDER=so.PK_ID
        where css.PK_ORDER is null
        UNION
        select so.*,
        cm1.member_code member_code,
        cm1.MEMBER_NAME memberName,
        cm1.SERVICE_CODE memberServiceCode,
        cm1.NICK_NAME memberNickName,
        cm1.PK_VERTEX memberPkVertex,
        css.APPROVE_STATUS approveStatus,
        css.SELF_CODE selfCode,
        css.REJECT_MSG rejectMsg
        from (select so.*
        from sa_order so
        inner join cu_member cm
        on cm.pk_id = so.pk_member
        where so.del_flag = 0
        and order_status in (1)
        and so.pk_country = #{pkCountry}
        and so.pk_creator = #{memberId}
        <if test="param.orderCode != null and param.orderCode != ''">
            and so.order_code like #{param.orderCode} || '%'
        </if>
        ) so
        inner join cu_member cm1
        on cm1.pk_id = so.pk_member
        inner JOIN SA_ORDER_SELF_LOG css on css.PK_ORDER=so.PK_ID
        )
        order by creation_time desc
    </select>
    <!--自助服务-修改收货地址订单列表-->
    <select id="selfAddressOrderList" resultType="com.hzs.common.domain.sale.ext.SaOrderExt">
        select * from (
        select distinct so.*,
        cm1.member_code member_code,
        cm1.MEMBER_NAME memberName,
        cm1.SERVICE_CODE memberServiceCode,
        cm1.NICK_NAME memberNickName,
        cm1.PK_VERTEX memberPkVertex,
        null approveStatus,
        null rejectMsg
        from (select so.*
        from sa_order so
        inner join cu_member cm
        on cm.pk_id = so.pk_member
        where so.del_flag = 0
        and order_status in (1)
        and so.pk_country = #{pkCountry}
        and so.pk_creator = #{memberId}
        AND TO_CHAR(so.PAY_TIME,'YYYY-MM-DD') = TO_CHAR(SYSDATE, 'YYYY-MM-DD')
        <if test="param.orderCode != null and param.orderCode != ''">
            and so.order_code like #{param.orderCode} || '%'
        </if>
        ) so
        inner join cu_member cm1
        on cm1.pk_id = so.pk_member
        LEFT JOIN cu_self_service_log css on css.ORDER_CODE=so.ORDER_CODE
        where css.ORDER_CODE is null
        UNION

        select so.*,
        cm1.member_code member_code,
        cm1.MEMBER_NAME memberName,
        cm1.SERVICE_CODE memberServiceCode,
        cm1.NICK_NAME memberNickName,
        cm1.PK_VERTEX memberPkVertex,
        css.APPROVE_STATUS approveStatus,
        css.REJECT_MSG rejectMsg
        from (select so.*
        from sa_order so
        inner join cu_member cm
        on cm.pk_id = so.pk_member
        where so.del_flag = 0
        and order_status in (1)
        and so.pk_country = #{pkCountry}
        and so.pk_creator = #{memberId}
        <if test="param.orderCode != null and param.orderCode != ''">
            and so.order_code like #{param.orderCode} || '%'
        </if>
        ) so
        inner join cu_member cm1
        on cm1.pk_id = so.pk_member
        inner JOIN
        (
        SELECT ORDER_CODE, APPROVE_STATUS, CREATION_TIME,EDIT_TYPE,REJECT_MSG
        FROM (
        SELECT ORDER_CODE, APPROVE_STATUS, CREATION_TIME,EDIT_TYPE,REJECT_MSG,
        ROW_NUMBER() OVER (PARTITION BY ORDER_CODE ORDER BY CREATION_TIME DESC) as rn
        FROM cu_self_service_log
        ) subquery
        WHERE rn = 1
        ) css
        on css.ORDER_CODE=so.ORDER_CODE
        where css.ORDER_CODE is not null
        <if test="param.editType != null">
            and css.EDIT_TYPE = #{param.editType}
        </if>
        AND TO_CHAR(css.CREATION_TIME,'YYYY-MM-DD') = TO_CHAR(SYSDATE, 'YYYY-MM-DD')


        )
        order by creation_time desc
    </select>

    <!-- 查询订单类型 -->
    <select id="queryOrderType" resultType="Integer">
        select distinct so.order_type
        from sa_order so
        where so.del_flag = 0
          and so.pk_country = #{pkCountry}
          and (so.pk_member = #{memberId} or so.pk_creator = #{memberId})
    </select>

    <!-- 二次发货列表 -->
    <select id="secondList" resultType="com.hzs.sale.deliver.vo.DeliverSecondVO">
        select so.pk_id,
        so.order_code,
        so.order_type,
        cm.member_code,
        cm.member_name,
        cm.phone,
        so.rec_name,
        so.rec_phone,
        so.rec_province,
        so.rec_city,
        so.rec_county,
        so.rec_address,
        so.order_amount,
        so.postage,
        so.order_achieve,
        so.order_ass_achieve,
        so.delivery_way,
        so.pay_type,
        so.remark,
        so.creation_time,
        so.pay_time,
        bs.name storehouseName,
        bc.in_exchange_rate
        from sa_order so
        inner join cu_member cm
        on cm.pk_id = so.pk_member
        inner join bd_currency bc
        on bc.pk_id = so.pk_rate
        left join bd_storehouse bs
        on bs.pk_id = so.pk_storehouse
        where so.del_flag = 0
        and so.pk_country = #{pkCountry}
        and (select count(soi.pk_id)
        from sa_order_items soi
        where soi.pk_order = so.pk_id
        and soi.del_flag = 0
        and soi.order_status in (0, 1, 2, 4)) = 0
        <if test="param.orderCode != null and param.orderCode != ''">
            and so.order_code like '%' || #{param.orderCode} || '%'
        </if>
        <if test="param.pkStorehouse != null ">
            and bs.pk_id = #{param.pkStorehouse}
        </if>
        <if test="param.memberCode != null and param.memberCode != ''">
            and cm.member_code like #{param.memberCode} || '%'
        </if>
        <if test="param.memberName != null and param.memberName != ''">
            and cm.member_name like #{param.memberName} || '%'
        </if>
        <if test="param.phone != null and param.phone != ''">
            and cm.phone like #{param.phone} || '%'
        </if>
        <if test="param.orderType != null">
            and so.order_type = #{param.orderType}
        </if>
        <if test="param.deliveryWay != null">
            and so.delivery_way = #{param.deliveryWay}
        </if>
        <if test="param.recName != null and param.recName != ''">
            and so.rec_name like '%' || #{param.recName} || '%'
        </if>
        <if test="param.recPhone != null and param.recPhone != ''">
            and so.rec_phone like '%' || #{param.recPhone} || '%'
        </if>
        <if test="param.recProvince != null">
            and so.rec_province = #{param.recProvince}
        </if>
        <if test="param.recCity != null">
            and so.rec_city = #{param.recCity}
        </if>
        <if test="param.recCounty != null">
            and so.rec_county = #{param.recCounty}
        </if>
        <if test="param.recAddress != null and param.recAddress != ''">
            and so.rec_address like #{param.recAddress} || '%'
        </if>
        <if test="param.payType != null">
            and so.pay_type = #{param.payType}
        </if>
        <if test="param.creationTime != null">
            and so.creation_time >= #{param.creationTime[0]}
            and #{param.creationTime[1]} >= so.creation_time
        </if>
        <if test="param.payTime != null">
            and so.pay_time >= #{param.payTime[0]}
            and #{param.payTime[1]} >= so.pay_time
        </if>
        <!-- 地区权限处理 -->
        <if test="param.areaScopeList != null and param.areaScopeList.size > 0">
            and so.rec_province in
            <foreach collection="param.areaScopeList" item="item" open="(" close=")" separator=",">
                #{item}
            </foreach>
        </if>
        <!-- 体系权限处理 -->
        <if test="param.vertexIdList != null and param.vertexIdList.size > 0">
            and cm.pk_vertex in
            <foreach collection="param.vertexIdList" item="item" open="(" close=")" separator=",">
                #{item}
            </foreach>
        </if>
        order by so.creation_time desc
    </select>

    <!--    查询当天已支付订单信息-->
    <select id="querySaOrderByOrderType" resultMap="SaOrder">
        select so.pk_member,
        so.ORDER_TYPE
        from sa_order so
        where so.order_status = 1 and (so.ORDER_TYPE=1 or so.ORDER_TYPE=2 or so.ORDER_TYPE=3)
        <if test="payTime == null">
            AND TO_CHAR(so.PAY_TIME,'YYYY-MM-dd') = TO_CHAR(SYSDATE, 'YYYY-MM-dd')
        </if>
        <if test="payTime != null and payTime!='' ">
            AND TO_CHAR(so.PAY_TIME,'YYYY-MM-dd') = #{payTime}
        </if>

    </select>

    <!-- 查询指定数量订单SEQ -->
    <select id="querySeqList" resultType="long">
        SELECT SA_ORDER_SEQ.nextval
        FROM (select level from dual connect by #{param} >= level)
    </select>

    <!-- 批量插入订单数据 -->
    <insert id="insertBach">
        INSERT ALL
        <foreach collection="orderList" item="item" separator=" ">
            into SA_ORDER (
            PK_ID, ORDER_CODE, ORDER_TYPE, PK_RATE,
            ORDER_AMOUNT, ORDER_ACHIEVE, UPLOAD_ACHIEVE, REC_NAME, REC_PHONE,
            REC_PROVINCE, REC_CITY, REC_COUNTY, REC_ADDRESS,
            PAY_TIME, PAY_TYPE, ORDER_STATUS,
            PK_MEMBER, PK_REFERENCE, PK_PLACEMENT, PK_STOREHOUSE,
            PK_CREATOR, CREATION_TIME, PK_COUNTRY
            )
            values (
            #{item.pkId}, #{item.orderCode}, #{item.orderType}, #{item.pkRate, jdbcType=NUMERIC},
            #{item.orderAmount}, #{item.orderAchieve}, #{item.orderAchieve}, #{item.recName}, #{item.recPhone},
            #{item.recProvince}, #{item.recCity}, #{item.recCounty, jdbcType=NUMERIC}, #{item.recAddress},
            #{item.payTime}, #{item.payType}, #{item.orderStatus},
            #{item.pkMember}, #{item.pkReference}, #{item.pkPlacement}, #{item.pkStorehouse, jdbcType=NUMERIC},
            #{item.pkCreator}, #{item.creationTime}, #{item.pkCountry}
            )
        </foreach>
        SELECT 1 FROM dual
    </insert>

    <!--    查询最新待发货订单-->
    <select id="lastOrderList" resultType="java.lang.String">
        SELECT *
        FROM (
                 SELECT so.order_code orderCode
                 FROM sa_order so
                          LEFT JOIN sa_order_items soi ON soi.pk_order = so.pk_id
                     AND soi.del_flag = 0
                 WHERE so.del_flag = 0
                   and so.order_type not in (13, 30)
                   and soi.del_flag = 0
                   and so.ORDER_STATUS = 1
                   and so.PAY_TIME is not null
                   and so.pk_creator = #{memberId}
                   AND soi.order_status = 1
                 ORDER BY so.PAY_TIME DESC
             )
        WHERE ROWNUM = 1
    </select>

    <select id="checkMemberIsFirstOrder" resultType="com.hzs.sale.order.vo.MemberFirstOrderVo">
        select cm.MEMBER_CODE memberCode ,(select count(so.pk_id) from sa_order so
        where so.PK_REFERENCE = cm.PK_ID and so.del_flag = 0
        and so.ORDER_TYPE in (1,7,24) and so.ORDER_STATUS = 1 ) orderCount from cu_member cm
        where 1 = 1
        <if test="memberCodeList != null and memberCodeList.size > 0">
            and cm.MEMBER_CODE in
            <foreach collection="memberCodeList" item="item" open="(" close=")" separator=",">
                #{item}
            </foreach>
        </if>
    </select>

    <!-- 查询最新注册订单或升级订单 -->
    <select id="queryLastRegisterOrUpdate" resultMap="SaOrder">
        select *
        from (select so.*
              from sa_order so
              where so.pk_member = #{pkMember}
                and so.pk_country = #{pkCountry}
                and so.order_type in (1, 2, 7, 24, 25)
                and so.del_flag = 0
              order by so.pay_time desc) so
        where rownum = 1
    </select>

    <!-- 根据订单编号查询订单信息 -->
    <select id="queryByPkIdOrCode" resultMap="saOrderExtResultMap">
        select so.*,
        soi.pk_id items_id
        from sa_order so
        left join sa_order_items soi
        on so.pk_id = soi.pk_order
        where 1=1
        <if test="pkOrder != null">
            and so.pk_id = #{pkOrder}
        </if>
        <if test="orderCode != null and orderCode != ''">
            and so.order_code = #{orderCode}
        </if>
        <if test="pkCountry != null">
            and so.pk_country = #{pkCountry}
        </if>
    </select>

    <select id="singleItemStat" resultType="com.hzs.sale.order.vo.SingleItemStatVo">

        select
        a.show_time payTime ,nvl(b.productQuantity,0) productQuantity,nvl(c.waresQuantity,0) waresQuantity
        ,nvl(d.giftQuantity,0) giftQuantity
        from ( SELECT ROWNUM, TRUNC(to_Date(#{startPayDateStr},'yyyy-mm-dd'), 'dd') + ROWNUM - 1 AS SHOW_TIME
        FROM DUAL
        CONNECT BY ROWNUM &lt; TRUNC(to_Date(#{endPayDateStr},'yyyy-mm-dd'), 'dd') -
        TRUNC(to_Date(#{startPayDateStr},'yyyy-mm-dd'), 'dd') + 1 )
        a

        left join
        (
        select nvl(sum(oi.QUANTITY),0) productQuantity,to_date(to_char(so.PAY_TIME,'YYYY-MM-DD'),'YYYY-MM-DD') payTime
        from SA_ORDER_ITEMS oi
        left join SA_ORDER so on so.pk_id = oi.PK_ORDER
        left join bd_product bp on bp.pk_id = oi.PK_PRODUCT
        left join bd_wares bw on bw.pk_id = oi.PK_WARES
        where oi.del_flag = 0 and so.ORDER_STATUS = 1 and oi.IS_GIFT = 1
        <if test="startPayDate != null">
            and so.PAY_TIME &gt;= #{startPayDate}
        </if>
        <if test="endPayDate != null">
            and so.PAY_TIME &lt;= #{endPayDate}
        </if>
        <if test="skuCode != null  and skuCode != ''">
            and oi.sku_code = #{skuCode}
        </if>
        <if test="productName != null  and productName != ''">
            and bp.PRODUCT_NAME like '%' || #{productName} || '%'
        </if>
        <if test="orderType != null">
            and so.ORDER_TYPE = #{orderType}
        </if>
        group by to_date(to_char(so.PAY_TIME,'YYYY-MM-DD'),'YYYY-MM-DD')
        ) b on b.payTime = a.show_time

        left join (
        select nvl(sum(oi.WARES_QUANTITY),0) waresQuantity, to_date(to_char(so.PAY_TIME,'YYYY-MM-DD'),'YYYY-MM-DD')
        payTime

        from SA_ORDER_ITEMS oi
        left join SA_ORDER so on so.pk_id = oi.PK_ORDER
        left join bd_product bp on bp.pk_id = oi.PK_PRODUCT
        left join bd_wares bw on bw.pk_id = oi.PK_WARES
        where oi.del_flag = 0 and so.ORDER_STATUS = 1 and oi.IS_GIFT = 1
        <if test="startPayDate != null">
            and so.PAY_TIME &gt;= #{startPayDate}
        </if>
        <if test="endPayDate != null">
            and so.PAY_TIME &lt;= #{endPayDate}
        </if>
        <if test="skuCode != null  and skuCode != ''">
            and oi.sku_code = #{skuCode}
        </if>
        <if test="productName != null  and productName != ''">
            and bp.PRODUCT_NAME like '%' || #{productName} || '%'
        </if>
        <if test="orderType != null">
            and so.ORDER_TYPE = #{orderType}
        </if>
        group by to_date(to_char(so.PAY_TIME,'YYYY-MM-DD'),'YYYY-MM-DD')
        )c on c.payTime = a.show_time
        left join (
        select
        nvl(sum(oi.QUANTITY),0) giftQuantity ,to_date(to_char(so.PAY_TIME,'YYYY-MM-DD'),'YYYY-MM-DD') payTime
        from SA_ORDER_ITEMS oi
        left join SA_ORDER so on so.pk_id = oi.PK_ORDER
        left join bd_product bp on bp.pk_id = oi.PK_PRODUCT
        left join bd_wares bw on bw.pk_id = oi.PK_WARES
        where oi.del_flag = 0 and so.ORDER_STATUS = 1 and oi.IS_GIFT = 0
        <if test="startPayDate != null">
            and so.PAY_TIME &gt;= #{startPayDate}
        </if>
        <if test="endPayDate != null">
            and so.PAY_TIME &lt;= #{endPayDate}
        </if>
        <if test="skuCode != null  and skuCode != ''">
            and oi.sku_code = #{skuCode}
        </if>
        <if test="productName != null  and productName != ''">
            and bp.PRODUCT_NAME like '%' || #{productName} || '%'
        </if>
        <if test="orderType != null">
            and so.ORDER_TYPE = #{orderType}
        </if>
        group by to_date(to_char(so.PAY_TIME,'YYYY-MM-DD'),'YYYY-MM-DD')


        )d on d.payTime = a.show_time
        order by a.show_Time desc
    </select>

    <select id="queryRepeatSaOrderByDay" resultMap="saOrderExtResultMap">
        select od.*, bc.in_exchange_rate
        from (
                 select so.pk_id,
                        so.pk_member,
                        so.order_code,
                        so.order_type,
                        so.pk_rate,
                        so.order_amount,
                        so.order_achieve,
                        so.upload_achieve,
                        so.pk_reference,
                        so.pk_vertex,
                        so.pk_country,
                        so.pay_time,
                        nvl(so.order_type_extend, 0) order_type_extend,
                        so.pk_creator,
                        st.pk_id                     items_id,
                        st.pk_order,
                        st.pk_product,
                        st.pk_wares,
                        st.price,
                        st.achievement,
                        st.quantity,
                        st.pk_grade,
                        st.pk_awards,
                        st.pk_wares_specs_sku,
                        st.pk_wares_detail,
                        st.wares_quantity,
                        st.is_gift,
                        st.specs_name,
                        st.ass_achievement,
                        st.is_maker_gift,
                        0                            channel,
                        sb.pk_order                  pk_bonus_order,
                        bt.bonus_value               pk_bonus_items,
                        bw.wares_code
                 from sa_order so
                          inner join sa_order_items st
                                     on so.pk_id = st.pk_order
                          left join sa_order_bonus sb
                                    on st.pk_id = sb.pk_order
                          left join bd_bonus_items bt
                                    on bt.pk_id = sb.pk_bonus_items
                          left join bd_wares bw
                                    on bw.pk_id = st.pk_wares
                          left join sa_cabin_card_detail sd
                                    on sd.pk_order = so.pk_id and sd.del_flag = 0
                 where so.order_status = 1
                   and so.del_flag = 0
                   and so.order_type in (1, 2, 24, 25)
                   and so.pay_time >= #{startDate}
                   and so.pay_time &lt; #{endDate}) od
                 left join bd_currency bc
                           on od.pk_rate = bc.pk_id
        order by pay_time
    </select>

    <select id="queryRefundList" resultType="com.hzs.common.domain.third.pay.ext.TOnlineRefundExt">
        select cm.member_code,
        cm.member_name,
        top.business_type pay_business_type,
        top.business_code pay_business_code,
        tor.business_code,
        tor.refund_status,
        tor.refund_money,
        tor.finish_money,
        tor.creation_time,
        tor.finish_time,
        tor.refund_channel,
        tor.refund_code,
        tor.error_msg
        from T_ONLINE_REFUND tor
        inner join T_ONLINE_PAYMENT top
        on top.pk_id = tor.pk_online_payment
        left join cu_member cm
        on cm.pk_id = top.pk_creator
        where tor.del_flag = 0
        <if test="userId != null">
            and tor.PK_CREATOR = #{userId}
        </if>
        order by creation_time desc
    </select>

    <!-- 查询会员最后注册升级单及等级 -->
    <select id="getLastOrderGrade" resultType="com.hzs.common.domain.sale.ext.SaOrderExt">
        select so.*
        from (select so.*, bg.grade_value, rownum grade_rownum
              from (select so.*
                    from (select so.*, rownum
                          from sa_order so
                          where so.del_flag = 0
                            and so.order_status = 1
                            and so.order_type in (1, 2)
                            and so.pk_member = #{pkMember}
                          order by so.pay_time desc) so
                    where rownum = 1) so
                       left join bd_grade bg
                                 on so.order_achieve >= bg.start_value
                                     and bg.del_flag = 0
                                     and bg.pk_country = so.pk_country
              order by bg.grade_value desc) so
        where rownum = 1
    </select>

</mapper>
