<?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.ylx.massage.mapper.TWxUserMapper">

    <resultMap type="TWxUserVo" id="TWxUserResult">
        <result property="id" column="id"/>
        <result property="cOpenid" column="c_openid"/>
        <result property="cPhone" column="c_phone"/>
        <result property="cIcon" column="c_icon"/>
        <result property="cNickName" column="c_nick_name"/>
        <result property="dMoney" column="d_money"/>
        <result property="nNum" column="n_num"/>
        <result property="dBalance" column="d_balance"/>
        <result property="dAllMoney" column="d_all_money"/>
        <result property="giftCardPayAmount" column="gift_card_pay_amount"/>
        <result property="currentPoints" column="current_points"/>
        <result property="totalPoints" column="total_points"/>
        <result property="createTime" column="create_time"/>
       <!-- <result property="createTime" column="create_time"/>
        <result property="updateTime" column="update_time"/>
        <result property="isDelete" column="is_delete"/>
        <result property="openid" column="openid"/>
        <result property="phone" column="phone"/>
        <result property="userName" column="user_name"/>
        <result property="atlasAdd" column="atlas_add"/>
        <result property="longitude" column="longitude"/>
        <result property="latitude" column="latitude"/>
        <result property="type" column="type"/>
        <result property="address" column="address"/>-->
    </resultMap>

    <sql id="selectTWxUserVo">
        select u.id,
               u.c_openid,
               u.c_icon,
               u.c_phone,
               u.c_unionid,
               u.c_nick_name,
               u.d_money,
               u.n_num,
               u.c_address_list,
               u.d_balance,
               u.d_all_money,
               u.c_scan_img,
               u.c_up_user,
               u.c_mr_address_id,
               u.create_time,
               u.update_time,
               u.is_delete,
               COALESCE(gco.gift_card_pay_amount, 0) gift_card_pay_amount,
               COALESCE(pul.current_points, 0) current_points,
               COALESCE(pul.total_points, 0) total_points,
               a.openid,
               a.phone,
               a.user_name,
               a.atlas_add,
               a.type,
               a.address
        FROM t_wx_user u
                 LEFT JOIN (select * from t_address where is_delete = 0 and type = 1) a ON u.c_openid = a.openid
                 LEFT JOIN (
                     SELECT user_id,
                            COALESCE(SUM(gco.pay_amount), 0) gift_card_pay_amount
                     FROM gift_card_order gco
                     WHERE gco.status = 1
                     GROUP BY user_id
                 ) gco ON gco.user_id = u.id
                 LEFT JOIN (
                     SELECT open_id,
                            COALESCE(SUM(CASE WHEN pul.op_type = 1 AND pul.is_expired != 1 THEN pul.points ELSE 0 END)
                                - SUM(CASE WHEN pul.op_type IN (2, 3) THEN pul.points ELSE 0 END), 0) current_points,
                            COALESCE(SUM(CASE WHEN pul.op_type = 1 THEN pul.points ELSE 0 END), 0) total_points
                     FROM point_user_log pul
                     GROUP BY open_id
                 ) pul ON pul.open_id = u.c_openid
    </sql>

    <!-- 查询微信用户列表 -->
    <select id="selectTWxUserList" resultMap="TWxUserResult">
        <include refid="selectTWxUserVo"/>
        <where>
            u.is_delete = 0
            <!-- 昵称 -->
            <if test="user.cNickName != null and user.cNickName != ''">
                and u.c_nick_name like concat('%', #{user.cNickName}, '%')
            </if>
            <!-- 手机号 -->
            <if test="user.cPhone != null and user.cPhone != ''">
                and u.c_phone like concat('%', #{user.cPhone}, '%')
            </if>
            <!-- 创建开始时间 -->
            <if test="user.startTime != null and user.startTime != ''">
                and u.create_time &gt;= #{user.startTime}
            </if>
            <!-- 创建结束时间 -->
            <if test="user.endTime != null and user.endTime != ''">
                and u.create_time &lt;= #{user.endTime}
            </if>
        </where>
        order by u.create_time desc
    </select>

    <select id="selectTWxUserById" parameterType="String" resultMap="TWxUserResult">
        <include refid="selectTWxUserVo"/>
        where id = #{id}
    </select>
    <select id="getBlockGetUser" resultType="com.ylx.massage.domain.vo.HomeBlock">
        SELECT
            DATE_FORMAT( create_time, '%Y-%m' ) AS MONTH,
            count(1) userNum
        FROM
            t_wx_user
        WHERE
            is_delete = 0
        GROUP BY
            MONTH
        ORDER BY
            MONTH
    </select>

    <select id="getBlockUser" resultType="java.lang.Integer">
        SELECT
            count(1) userNum
        FROM
            t_wx_user
        WHERE
            is_delete = 0

    </select>

    <insert id="insertTWxUser" parameterType="TWxUser">
        insert into t_wx_user
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="id != null">id,</if>
            <if test="cOpenid != null and cOpenid != ''">c_openid,</if>
            <if test="cSessionKey != null">c_session_key,</if>
            <if test="cIcon != null">c_icon,</if>
            <if test="cPhone != null">c_phone,</if>
            <if test="cUnionid != null">c_unionid,</if>
            <if test="cNickName != null">c_nick_name,</if>
            <if test="dMoney != null">d_money,</if>
            <if test="nNum != null">n_num,</if>
            <if test="cAddressList != null">c_address_list,</if>
            <if test="dBalance != null">d_balance,</if>
            <if test="dAllMoney != null">d_all_money,</if>
            <if test="cScanImg != null">c_scan_img,</if>
            <if test="cUpUser != null">c_up_user,</if>
            <if test="cMrAddressId != null">c_mr_address_id,</if>
            <if test="createTime != null">create_time,</if>
            <if test="updateTime != null">update_time,</if>
            <if test="isDelete != null">is_delete,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="id != null">#{id},</if>
            <if test="cOpenid != null and cOpenid != ''">#{cOpenid},</if>
            <if test="cSessionKey != null">#{cSessionKey},</if>
            <if test="cIcon != null">#{cIcon},</if>
            <if test="cPhone != null">#{cPhone},</if>
            <if test="cUnionid != null">#{cUnionid},</if>
            <if test="cNickName != null">#{cNickName},</if>
            <if test="dMoney != null">#{dMoney},</if>
            <if test="nNum != null">#{nNum},</if>
            <if test="cAddressList != null">#{cAddressList},</if>
            <if test="dBalance != null">#{dBalance},</if>
            <if test="dAllMoney != null">#{dAllMoney},</if>
            <if test="cScanImg != null">#{cScanImg},</if>
            <if test="cUpUser != null">#{cUpUser},</if>
            <if test="cMrAddressId != null">#{cMrAddressId},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="updateTime != null">#{updateTime},</if>
            <if test="isDelete != null">#{isDelete},</if>
        </trim>
    </insert>

    <update id="updateTWxUser" parameterType="TWxUser">
        update t_wx_user
        <trim prefix="SET" suffixOverrides=",">
            <if test="cOpenid != null and cOpenid != ''">c_openid = #{cOpenid},</if>
            <if test="cSessionKey != null">c_session_key = #{cSessionKey},</if>
            <if test="cIcon != null">c_icon = #{cIcon},</if>
            <if test="cPhone != null">c_phone = #{cPhone},</if>
            <if test="cUnionid != null">c_unionid = #{cUnionid},</if>
            <if test="cNickName != null">c_nick_name = #{cNickName},</if>
            <if test="dMoney != null">d_money = #{dMoney},</if>
            <if test="nNum != null">n_num = #{nNum},</if>
            <if test="cAddressList != null">c_address_list = #{cAddressList},</if>
            <if test="dBalance != null">d_balance = #{dBalance},</if>
            <if test="dAllMoney != null">d_all_money = #{dAllMoney},</if>
            <if test="cScanImg != null">c_scan_img = #{cScanImg},</if>
            <if test="cUpUser != null">c_up_user = #{cUpUser},</if>
            <if test="cMrAddressId != null">c_mr_address_id = #{cMrAddressId},</if>
            <if test="createTime != null">create_time = #{createTime},</if>
            <if test="updateTime != null">update_time = #{updateTime},</if>
            <if test="isDelete != null">is_delete = #{isDelete},</if>
        </trim>
        where id = #{id}
    </update>

    <delete id="deleteTWxUserById" parameterType="String">
        delete
        from t_wx_user
        where id = #{id}
    </delete>

    <delete id="deleteTWxUserByIds" parameterType="String">
        delete from t_wx_user where id in
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>
</mapper>
