<?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.buy507.mall.dao.UmsMemberDao">

	<resultMap id="BaseResultMap" type="com.buy507.mall.dto.MemberResult">
	    <result column="id" jdbcType="BIGINT" property="id" />
	    <result column="member_level" jdbcType="INTEGER" property="memberLevel" typeHandler="org.apache.ibatis.type.EnumOrdinalTypeHandler" />
	    <result column="nickname" jdbcType="VARCHAR" property="nickname" />
	    <result column="phone" jdbcType="VARCHAR" property="phone" />
	    <result column="gender" jdbcType="INTEGER" property="gender" />
	    <result column="store_status" jdbcType="INTEGER" property="storeStatus" typeHandler="org.apache.ibatis.type.EnumOrdinalTypeHandler" />
	    <result column="balance" jdbcType="DECIMAL" property="balance" />
	    <result column="freeze" jdbcType="DECIMAL" property="freeze" />
	    <result column="totalIncome" jdbcType="DECIMAL" property="totalIncome" />
	    <result column="totalExpenditure" jdbcType="DECIMAL" property="totalExpenditure" />
	    <result column="inviter" jdbcType="VARCHAR" property="inviter" />
	    <result column="create_time" jdbcType="TIMESTAMP" property="createTime" />
    </resultMap>

    <select id="selectByExample" resultType="java.util.HashMap">
    	SELECT * FROM (
	        SELECT m.id,
	        	   m.member_level as memberLevel,
	        	   m.username as username,
	        	   m.nickname as nickname,
	        	   m.phone as phone,
	        	   m.gender as gender,
	        	   m.store_status as storeStatus,
	        	   m.balance as balance,
	        	   m.freeze as freeze,
	        	   (SELECT IFNULL(SUM(mtr.value), 0) FROM dms_member_trade_record mtr WHERE m.id = mtr.member_id and mtr.type = 0) as totalIncome,
	        	   (SELECT IFNULL(sum(mtr.value), 0) FROM dms_member_trade_record mtr WHERE m.id = mtr.member_id and mtr.type = 1) as totalExpenditure,
	        	   (SELECT a.nickname FROM ums_member a WHERE a.id = tl.parent_id) as inviter,
	        	   m.create_time as createTime
	          FROM
	            ums_member m 
	     LEFT JOIN dms_team_level tl on m.id = tl.member_id
	         WHERE
	       		1=1
	       	<if test="nickname!=null">
	        	and m.nickname like"%"#{nickname}"%"
	        </if>
	        <if test="memberLevel!=null">
	        	and m.member_level = #{memberLevel}
	        </if>
	        <if test="storeStatus!=null">
	        	and m.store_status = #{storeStatus}
	        </if>
	        
	     ) temp 
	     
	     <if test="inviter!=null">
        	WHERE temp.inviter like"%"#{inviter}"%"
         </if>
	     ORDER BY temp.createTime desc
	     
    </select>
    
    <select id="selectByMemberLevelAndNickname" resultType="com.buy507.mall.dto.MemberMonthRewardResult">
        SELECT m.id as memberId,
        	   m.member_level as memberLevel,
        	   m.nickname as nickname,
        	   m.phone as phone,
        	   m.gender as gender,
        	   m.create_time as createTime,
        	   (SELECT a.nickname FROM ums_member a WHERE a.id = tl.parent_id) as inviter
          FROM
            ums_member m left join dms_team_level tl on m.id = tl.member_id
         WHERE
       		1=1    
        <if test="memberLevel!=null">
        	and m.member_level = #{memberLevel}
        </if>
        <if test="nickname!=null">
        	and m.nickname = #{nickname}
        </if>
    </select>
    
    <select id="selectMemberTradeRecord" resultType="java.util.HashMap">
        SELECT mtr.item_type as itemType,
        	   mtr.type as type,
        	   mtr.value as value,
        	   mtr.title as title,
        	   mtr.note as note,
        	   mtr.create_time as createTime,
        	   m.username as username,
        	   m.nickname as nickname,
        	   o.order_sn as orderSn
          FROM
            dms_member_trade_record mtr
     LEFT JOIN ums_member m ON m.id = mtr.member_id
     LEFT JOIN oms_order o ON o.id = mtr.order_id
 	     WHERE mtr.member_id = #{memberId}

        <if test="orderSn!=null and orderSn!=''">
        	and o.order_sn = #{orderSn}
        </if>
        <if test="itemType!=null">
        	and mtr.item_type = #{itemType}
        </if>
        <if test="type!=null">
        	and mtr.type = #{type}
        </if>
        
     ORDER BY mtr.create_time desc
    </select>
    
</mapper>