<?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.member.self.mapper.CuSelfServiceLogMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.hzs.common.domain.member.self.CuSelfServiceLog">
        <id column="PK_ID" property="pkId"/>
        <result column="DEL_FLAG" property="delFlag"/>
        <result column="CREATION_TIME" property="creationTime"/>
        <result column="MODIFIED_TIME" property="modifiedTime"/>
        <result column="PK_COUNTRY" property="pkCountry"/>
        <result column="PK_CREATOR" property="pkCreator"/>
        <result column="PK_MODIFIED" property="pkModified"/>
        <result column="PK_MEMBER" property="pkMember"/>
        <result column="EDIT_TYPE" property="editType"/>
        <result column="OLD_VALUE" property="oldValue"/>
        <result column="NEW_VALUE" property="newValue"/>
        <result column="BUSINESS_NO" property="businessNo"/>
        <result column="APPROVE_STATUS" property="approveStatus"/>
        <result column="APPROVE_NUMBER" property="approveNumber"/>
        <result column="EDIT_TYPE_PAY" property="editTypePay"/>
        <result column="SINGLE_AMOUNT" property="singleAmount"/>
        <result column="ORDER_CODE" property="orderCode"/>
        <result column="APPROVE_TIME" property="approveTime"/>
        <result column="POSTAGE" property="postage"/>
        <result column="SELF_REMARK" property="selfRemark"/>
        <result column="DESCRIPTION" property="description"/>
        <result column="PK_ACCOUNT" property="pkAccount"/>
    </resultMap>

    <resultMap id="SelfModifyAddress" type="com.hzs.member.self.vo.SelfModifyAddressVO">
        <id column="PK_ID" property="pkId"/>
        <result column="CREATION_TIME" property="creationTime"/>
        <result column="MODIFIED_TIME" property="modifiedTime"/>
        <result column="PK_COUNTRY" property="pkCountry"/>
        <result column="PK_CREATOR" property="pkCreator"/>
        <result column="PK_MODIFIED" property="pkModified"/>
        <result column="PK_MEMBER" property="pkMember"/>
        <result column="EDIT_TYPE" property="editType"/>
        <result column="OLD_VALUE" property="oldValue"/>
        <result column="NEW_VALUE" property="newValue"/>
        <result column="BUSINESS_NO" property="businessNo"/>
        <result column="MEMBER_NAME" property="memberName"/>
        <result column="APPROVE_STATUS" property="approveStatus"/>
        <result column="APPROVE_NUMBER" property="approveNumber"/>
        <result column="EDIT_TYPE_PAY" property="editTypePay"/>
        <result column="SINGLE_AMOUNT" property="singleAmount"/>
        <result column="ORDER_CODE" property="orderCode"/>
        <result column="PAY_TIME" property="payTime"/>
        <result column="POSTAGE" property="postage"/>
        <result column="SELF_REMARK" property="selfRemark"/>
        <result column="MEMBER_CODE" property="memberCode"/>
        <result column="REC_NAME" property="recName"/>
        <result column="REC_PHONE" property="recPhone"/>
        <result column="order_type" property="orderType"/>
        <result column="order_status" property="orderStatus"/>
        <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="itemList" ofType="com.hzs.member.self.vo.MyOrderItemsVO">
            <result column="pk_Order" property="pkOrder"/>
            <result column="PRODUCT_NAME" property="productName"/>
            <result column="SPECS_NAME" property="specsName"/>
            <result column="QUANTITY" property="quantity"/>
            <result column="PRICE" property="price"/>
            <result column="ACHIEVEMENT" property="achievement"/>
            <result column="ASS_ACHIEVEMENT" property="assAchievement"/>
            <result column="IS_GIFT" property="isGift"/>
        </collection>
    </resultMap>

    <!--   自助服务-查询已申请修改收货地址-->
    <select id="querySelfModifyAddressList" parameterType="com.hzs.member.self.vo.SelfModifyAddressVO"
            resultMap="SelfModifyAddress">
        select so.*,
        cm1.member_code member_code,
        cm1.MEMBER_NAME,
        cm2.member_code creator_code,
        css.SELF_REMARK,
        css.BUSINESS_NO,
        css.APPROVE_STATUS,
        css.NEW_VALUE
        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 so.pk_country = #{pkCountry}
        <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="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="orderType != null">
            and order_type = #{orderType}
        </if>
        <if test="orderStatus != null">
            and so.order_status = #{orderStatus}
        </if>

        <if test="recProvince != null">
            and so.REC_PROVINCE = #{recProvince}
        </if>
        <if test="recCity != null">
            and so.REC_CITY = #{recCity}
        </if>
        <if test="recCounty != null">
            and so.REC_COUNTY = #{recCounty}
        </if>
        <if test="recAddress != null and recAddress != '' ">
            and so.REC_ADDRESS like #{recAddress} || '%'
        </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
        LEFT JOIN cu_self_service_log css on css.ORDER_CODE=so.ORDER_CODE
        where css.ORDER_CODE is not null and css.del_flag = 0
        <if test="startDate != null and endDate != null">
            and css.creation_time between #{startDate, jdbcType=TIMESTAMP} and #{endDate, jdbcType=TIMESTAMP}
        </if>
        <if test="businessNo != null">
            and css.BUSINESS_NO = #{businessNo}
        </if>
        <if test="editType != null ">
            and css.EDIT_TYPE = #{editType}
        </if>
        order by css.creation_time desc
    </select>
    <!--管理端查询会员修改地址-->
    <select id="querySelfModifyAddressManageList" parameterType="com.hzs.member.self.vo.SelfModifyAddressVO"
            resultType="com.hzs.member.self.vo.SelfModifyAddressVO">
        select so.*,
        cm1.member_code member_code,
        cm1.MEMBER_NAME,
        cm2.member_code creator_code,
        css.SELF_REMARK,
        css.BUSINESS_NO,
        css.APPROVE_STATUS,
        css.NEW_VALUE,
        css.SELF_REMARK as selfRemark,
        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 so.pk_country = #{pkCountry}
        <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="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="orderType != null">
            and order_type = #{orderType}
        </if>
        <if test="orderStatus != null">
            and so.order_status = #{orderStatus}
        </if>

        <if test="recProvince != null">
            and so.REC_PROVINCE = #{recProvince}
        </if>
        <if test="recCity != null">
            and so.REC_CITY = #{recCity}
        </if>
        <if test="recCounty != null">
            and so.REC_COUNTY = #{recCounty}
        </if>
        <if test="recAddress != null and recAddress != '' ">
            and so.REC_ADDRESS like #{recAddress} || '%'
        </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
        LEFT JOIN cu_self_service_log css on css.ORDER_CODE=so.ORDER_CODE
        where css.ORDER_CODE is not null and css.del_flag = 0
        <if test="startDate != null and endDate != null">
            and css.creation_time between #{startDate, jdbcType=TIMESTAMP} and #{endDate, jdbcType=TIMESTAMP}
        </if>
        <if test="businessNo != null">
            and css.BUSINESS_NO = #{businessNo}
        </if>
        <if test="editType != null ">
            and css.EDIT_TYPE = #{editType}
        </if>
        order by css.creation_time desc
    </select>

    <!--      查询用户是否存在已支付订单-->
    <select id="showHiddenAddressList" resultType="java.lang.Integer">
        select count(0)
        from sa_order so
        where so.del_flag = 0
          and order_status in (1)
          and so.pk_creator = #{memberId}
          AND TO_CHAR(so.PAY_TIME, 'YYYY-MM-DD') = TO_CHAR(SYSDATE, 'YYYY-MM-DD')
    </select>

    <!-- 根据订单ID查询订单明细列表 -->
    <select id="queryListByOrderId" resultType="com.hzs.member.self.vo.MyOrderItemsVO">
        select soi.*, bp.product_name, bp.cover
        from sa_order_items soi
        inner join bd_product bp
        on bp.pk_id = soi.pk_product
        where soi.del_flag = 0
        and soi.pk_order in
        <foreach collection="orderIdList" item="item" open="(" close=")" separator=",">
            #{item}
        </foreach>
    </select>


    <select id="selectSelfServiceByPkMember" resultType="com.hzs.common.domain.member.self.CuSelfServiceLogExt">
        select l.*, m.member_code memberCode
        from cu_self_service_log l
        left join cu_member m
        on l.pk_member = m.pk_id
        and m.del_flag = 0
        where l.del_flag = 0
        and l.pk_country = #{pkCountry}
        and l.edit_type = #{editType}
        <if test="pkMember != null">
            and l.pk_member = #{pkMember}
        </if>
        <if test="businessNo != null and businessNo != ''">
            and l.business_no = #{businessNo}
        </if>
        <if test="memberCode != null and memberCode != ''">
            and m.member_code like '%'|| #{memberCode} || '%'
        </if>

        <if test="memberName != null and memberName != ''">
            and l.OLD_VALUE like '%'|| #{memberName} || '%'
        </if>
        <if test="phone != null and phone != ''">
            and l.OLD_VALUE like '%'|| #{phone} || '%'
        </if>
        <if test="approveStatus != null">
            and l.APPROVE_STATUS = #{approveStatus}
        </if>
        <if test="startDate != null and startDate != ''">
            and l.CREATION_TIME >= to_date(#{startDate}, 'yyyy-mm-dd')
        </if>
        <if test="endDate != null and endDate != ''">
            and l.CREATION_TIME &lt;= to_date(#{endDate} || ' 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
        </if>
        order by nvl(l.approve_time,l.creation_time) desc
    </select>


    <select id="selectDomicileChangeDetail" resultType="com.hzs.common.domain.member.self.CuSelfServiceLogExt">
        select l.*, m.member_code memberCode
        from cu_self_service_log l
                 left join cu_member m
                           on l.pk_member = m.pk_id
                               and m.del_flag = 0
        where l.del_flag = 0
          and l.pk_id = #{pkId}
    </select>

    <select id="selectSelfServiceByBusinessNo" resultMap="BaseResultMap">
        select l.*
        from cu_self_service_log l
        where l.del_flag = 0
          and l.edit_type = #{editType}
          and l.business_no = #{businessNo}
    </select>

    <select id="selectByMaxApproveNumber" parameterType="com.hzs.common.domain.member.self.CuSelfServiceLog"
            resultType="integer">
        select nvl(max(APPROVE_NUMBER), 0)
        from CU_SELF_SERVICE_LOG
        where EDIT_TYPE = #{editType}
          and PK_MEMBER = #{pkMember}
          and APPROVE_STATUS = #{approveStatus}
    </select>

</mapper>
