<?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.base.mapper.CuMemberShareMapper">

    <select id="seaFlourList" resultType="com.hzs.member.marketnews.vo.SeaFlourVo">
        select ms.MEMBER_CODE memberCode,ms.MEMBER_NAME memberName,ms.PHONE phone,ms.CREATION_TIME creationTime,
        ms.pk_vertex,
        ms.service_code,
        ms.nick_name
        from CU_MEMBER ms
        inner join bd_grade bg
        on ms.pk_settle_grade = bg.pk_id
        where 1= 1 and bg.grade_value= 20
        and
        (
        (select count(so.PK_ID) from sa_order so where so.PK_CREATOR = ms.pk_id and so.del_flag = 0 and so.ORDER_STATUS = 1) = 0
        and
        (select count(sto.PK_ID) from sa_t_order sto where sto.PK_CREATOR = ms.pk_id and sto.del_flag = 0 and sto.ORDER_STATUS = 1) = 0
        )
        <if test="pkMember != null">
            and ms.PK_PARENT = #{pkMember}
        </if>
        <if test="memberInfo != null and memberInfo != ''">
            and (ms.MEMBER_NAME=#{memberInfo} or ms.MEMBER_CODE=#{memberInfo})
        </if>
        <if test="memberCode != null and memberCode != ''">
            and (ms.member_code = #{memberCode} or ms.SERVICE_CODE = #{memberCode})
        </if>
        <if test="memberName != null and memberName != ''">
            and (ms.member_name = #{memberName} or ms.NICK_NAME = #{memberName})
        </if>
        <if test="startCreationTime != null">
            and ms.CREATION_TIME >= #{startCreationTime}
        </if>
        <if test="endCreationTime != null">
            and ms.CREATION_TIME &lt; #{endCreationTime}
        </if>
        order by ms.CREATION_TIME desc
    </select>

</mapper>
