<?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.ruoyi.mall.mapper.MallMemberUserMapper">

    <resultMap type="MallMemberUser" id="MallMemberUserResult">
        <result property="id"    column="id"    />
        <result property="phone"    column="phone"    />
        <result property="wechat"    column="wechat"    />
        <result property="sex"    column="sex"    />
        <result property="openid"    column="openid"    />
        <result property="avatar"    column="avatar"    />
        <result property="name"    column="name"    />
        <result property="password"    column="password"    />
        <result property="token"    column="token"    />
        <result property="status"    column="status"    />
        <result property="introUser"    column="intro_user"    />
        <result property="money"    column="money"    />
        <result property="allMoney"    column="all_money"    />
        <result property="qrCode"    column="qr_code"    />
        <result property="createAt"    column="create_at"    />
        <result property="updateAt"    column="update_at"    />
        <result property="delFlag"    column="del_flag"    />
        <result property="salesmanId"    column="salesman_id"    />
        <result property="province"    column="province"    />
        <result property="city"    column="city"    />
        <result property="area"    column="area"    />
        <result property="address"    column="address"    />
        <result property="shopImage"    column="shop_image"    />
        <result property="shopName"    column="shop_name"    />
        <result property="personName"    column="person_name"    />
        <result property="loginAt"    column="login_at"    />
        <result property="bindingAt"    column="binding_at"    />
        <result property="vip"    column="vip"    />
    </resultMap>

    <resultMap type="com.ruoyi.mall.domain.vo.MemberUserVO" id="MemberUserVOResult">
        <result property="id"    column="id"    />
        <result property="phone"    column="phone"    />
        <result property="wechat"    column="wechat"    />
        <result property="sex"    column="sex"    />
        <result property="openid"    column="openid"    />
        <result property="avatar"    column="avatar"    />
        <result property="name"    column="name"    />
        <result property="qrCode"    column="qr_code"    />
        <result property="vip"    column="vip"    />
    </resultMap>

    <resultMap type="com.ruoyi.mall.domain.vo.MemberCollectVO" id="MemberCollectVOResult">
        <result property="memberId"    column="member_id"    />
        <result property="goodsId"    column="goods_id"    />
    </resultMap>

    <resultMap type="com.ruoyi.mall.domain.vo.MemberFootMarkVO" id="MemberFootMarkVOResult">
        <result property="id"    column="id"    />
        <result property="memberId"    column="member_id"    />
        <result property="goodsId"    column="goods_id"    />
        <result property="updateTime"    column="update_time"    />
    </resultMap>

    <resultMap type="com.ruoyi.mall.domain.vo.IndexGoodsVO" id="IndexGoodsVOResult">
        <result property="id"    column="id"    />
        <result property="goodsName"    column="goods_name"    />
        <result property="goodsTitle"    column="goods_title"    />
        <result property="classId"    column="class_id"    />
        <result property="goodsImg"    column="goods_img"    />
        <result property="postFee"    column="post_fee"    />
        <result property="monthlySales"    column="monthly_sales"    />
        <result property="stock"    column="stock"    />
        <result property="spec"    column="spec"    />
        <result property="param"    column="param"    />
        <result property="shareImg"    column="share_img"    />
        <result property="shareTitle"    column="share_title"    />
        <result property="productName"    column="product_name"    />
        <result property="price"    column="price"    />
        <result property="priceN"    column="price_n"    />
    </resultMap>

<!--    <resultMap type="com.ruoyi.mall.domain.MallMemberCoupon" id="MallMemberCouponResult">-->
<!--        <result property="userId"    column="user_id"    />-->
<!--        <result property="couponId"    column="coupon_id"    />-->
<!--        <result property="createAt"    column="create_at"    />-->
<!--        <result property="name"    column="name"    />-->
<!--        <result property="enoughPrice"    column="enough_price"    />-->
<!--        <result property="type"    column="type"    />-->
<!--        <result property="isUsed"    column="is_used"    />-->
<!--        <result property="price"    column="price"    />-->
<!--    </resultMap>-->

    <sql id="selectMallMemberUserVo">
        select id, phone, wechat, sex, openid, avatar, `name`, password, token, status, intro_user,
               money, all_money, qr_code, create_at, update_at, del_flag , salesman_id,
               province, city, area, address, shop_image, shop_name , person_name, vip
        from mall_member_user
    </sql>

    <select id="selectMallMemberUserList" parameterType="MallMemberUser" resultType="MallMemberUser">
        select mmu.id id,mmu.vip vip, mmu.phone phone, mmu.wechat wechat, mmu.sex sex, mmu.openid openId, mmu.avatar avatar, mmu.name name,
               mmu.password password, mmu.token token, mmu.status status, mmu.intro_user introUser, mmu.money money,
               mmu.all_money allMoney, mmu.qr_code qrCode, mmu.create_at createAt, mmu.update_at updateAt, mmu.del_flag delFlag,
               mmu.salesman_id salesmanId, mmu.province province, mmu.city city, mmu.area area, mmu.address address,
               mmu.shop_image shopImage, mmu.shop_name shopName, mmu.person_name personName, mmu.login_at loginAt, ms.name salesmanName
        from mall_member_user mmu
            left join mall_salesman ms
                on mmu.salesman_id = ms.id
        <where>
            <if test="phone != null  and phone != ''"> and mmu.phone like concat('%', #{phone}, '%')</if>
            <if test="name != null  and name != ''"> and mmu.name like concat('%', #{name}, '%')</if>
            <if test="status != null  and status != ''"> and mmu.status = #{status}</if>
            <if test="vip != null  and vip != ''"> and mmu.vip = #{vip}</if>
            <if test="salesmanId != null  and salesmanId != ''"> and ms.id = #{salesmanId}</if>
            <if test="params.allMoneyBegin != null  and params.allMoneyEnd != null">
               and (mmu.all_money &gt;= #{params.allMoneyBegin} and mmu.all_money &lt;= #{params.allMoneyEnd})
            </if>
        </where>
        order by mmu.create_at desc
    </select>

    <select id="selectMallMemberUserById" parameterType="Long" resultMap="MallMemberUserResult">
        <include refid="selectMallMemberUserVo"/>
        where id = #{id}
    </select>

    <select id="selectBossMemberUser" parameterType="Long" resultMap="MallMemberUserResult">
        <include refid="selectMallMemberUserVo"/>
        where phone in ('18595881193', '18439811110', '13803863556') and openid is not null and openid not in ('')
    </select>
    <select id="queryByOpenid" parameterType="string" resultMap="MallMemberUserResult">
        <include refid="selectMallMemberUserVo"/>
        where openid = #{openid}
        limit 1
    </select>
    <select id="queryByPhone" parameterType="string" resultMap="MallMemberUserResult">
        <include refid="selectMallMemberUserVo"/>
        where phone = #{phone}
        limit 1
    </select>

    <select id="selectMemberVOUserById" parameterType="long" resultMap="MemberUserVOResult">
        select id, phone, wechat, sex, openid, avatar, name,qr_code,vip
        from mall_member_user
        where id = #{l}
        and status = '0'
    </select>
    <select id="selectCollectCount" resultType="java.lang.Integer">
        select count(1) from mall_member_conllect
        where member_id = #{userId}
        and goods_id = #{goodsId}
    </select>
    <select id="queryCollectByUserUserId" parameterType="long" resultMap="IndexGoodsVOResult">
        select mgg.id, mgg.goods_name, mgg.goods_title, mgg.class_id, mgg.goods_img, mgg.post_fee,
               mgg.monthly_sales, mgg.stock, mgg.spec, mgg.param, mgg.has_spec, mgg.is_recommend,
               mgg.is_hot, mgg.status, mgg.share_img, mgg.share_title, mgg.sort, mgp.price,
               mgp.price_n, mgp.price_m, mgp.stock, mgp.num_sale, mgp.product_name
        from mall_member_conllect mmc
            left join mall_goods_goods mgg
                on mgg.id = mmc.goods_id
            left join mall_goods_product mgp
                on mgg.id = mgp.goods_id
        where mmc.member_id = #{userId}
          and mgp.is_default = '1'
          and mgg.del_flag = '0'
          AND mgg.status = '0'
    </select>
    <select id="queryFoodMarkByUserUserId" parameterType="Long" resultMap="IndexGoodsVOResult">
        select mgg.id, mgg.goods_name, mgg.goods_title, mgg.class_id, mgg.goods_img, mgg.post_fee, mgg.monthly_sales, mgg.stock, mgg.spec,
        mgg.param, mgg.has_spec, mgg.is_recommend, mgg.is_hot, mgg.status, mgg.share_img, mgg.share_title, mgg.sort,
        mgp.price, mgp.price_n, mgp.price_m, mgp.stock, mgp.num_sale, mgp.product_name
        from mall_member_footmark mmf
        left join mall_goods_goods mgg on mgg.id = mmf.goods_id and mgg.del_flag = '0'
        left join mall_goods_product mgp on mgg.id = mgp.goods_id and mgp.is_default = '1'
        where mmf.member_id = #{userId}
        order by update_time desc
    </select>
    <select id="selectFootMarkVOUserById" parameterType="MemberFootMarkVO" resultMap="MemberFootMarkVOResult">
        select id,goods_id, member_id
        from mall_member_footmark
        where goods_id = #{goodsId}
        and member_id = #{memberId}
    </select>
    <select id="selectIsCollectBy" parameterType="MemberCollectVO" resultMap="MemberCollectVOResult">
        select goods_id, member_id
        from mall_member_conllect
        where goods_id = #{goodsId}
        and member_id = #{memberId}
    </select>
    <select id="checkUserCoupon" resultType="map">
        select user_id, coupon_id
        from mall_member_coupon
        where user_id = #{userId}
        and coupon_id = #{couponId}
        limit 1
    </select>
    <select id="getMyCoupons" parameterType="Long" resultType="MallMemberCoupon">
        select coupon_id couponId, `name` name, `enough_price` enoughPrice, `price` price, `type` type, `create_at` createAt, is_used isUsed, member_coupon_id memberCouponId
        from mall_member_coupon
        where user_id = #{userId}
            and is_used = '0'
    </select>
    <select id="getEnableCoupons" parameterType="map" resultType="MallMemberCoupon">
        select coupon_id couponId, `name` name, `enough_price` enoughPrice, `price` price, `type` type, `create_at` createAt, is_used isUsed
        from mall_member_coupon
        where user_id = #{userId}
            and #{price} &gt;= enough_price
            and is_used = '0'
    </select>
    <select id="checkCouponUsed" resultType="map">
        select mmc.create_at createAt, mmc.coupon_id couponId, mmc.price, mc.type, mmc.member_coupon_id memberCouponId
        from mall_member_coupon mmc
        left join mall_coupon mc on mc.id = mmc.coupon_id
        where mmc.user_id = #{userId}
        and mmc.coupon_id = #{couponId}
        and mmc.is_used = '0'
        order by mmc.create_at desc
            limit 1
    </select>

    <select id="checkCouponUsedNew" resultType="MallMemberCoupon">
        select user_id, coupon_id, name, enough_price, price, type, create_at, is_used
        from mall_member_coupon
        where user_id = #{userId}
          and coupon_id = #{couponId}
          and is_used = '0'
            limit 1
    </select>
    <select id="selectCollectCountByUserId" parameterType="long" resultType="int">
        select count(1) from mall_member_conllect mmc
        left join mall_goods_goods mgg on mgg.id = mmc.goods_id
        where member_id = #{userId}
        and mgg.del_flag = '0'
    </select>

    <select id="selectFootCountByUserId" parameterType="long" resultType="int">
        select count(1) from mall_member_footmark mmf
        left join mall_goods_goods mgg on mgg.id = mmf.goods_id
        where member_id = #{userId}
        and mgg.del_flag = '0'
    </select>

    <select id="selectCouponCountByUserId" parameterType="long" resultType="int">
        select count(1) from mall_member_coupon
        where user_id = #{userId}
        and is_used = '0'
    </select>

    <update id="updateCouponUsed">
        update mall_member_coupon set is_used = '1'
        where user_id = #{userId}
        and coupon_id = #{couponId}
    </update>

    <insert id="insertMallMemberUser" parameterType="MallMemberUser" useGeneratedKeys="true" keyProperty="id">
        insert into mall_member_user
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="phone != null">phone,</if>
            <if test="wechat != null">wechat,</if>
            <if test="sex != null">sex,</if>
            <if test="openid != null">openid,</if>
            <if test="avatar != null">avatar,</if>
            <if test="name != null">name,</if>
            <if test="password != null">password,</if>
            <if test="token != null">token,</if>
            <if test="status != null">status,</if>
            <if test="introUser != null">intro_user,</if>
            <if test="money != null">money,</if>
            <if test="allMoney != null">all_money,</if>
            <if test="qrCode != null">qr_code,</if>
            <if test="createAt != null">create_at,</if>
            <if test="updateAt != null">update_at,</if>
            <if test="delFlag != null">del_flag,</if>
            <if test="salesmanId != null">salesman_id,</if>
            <if test="province != null">province,</if>
            <if test="city != null">city,</if>
            <if test="area != null">area,</if>
            <if test="address != null">address,</if>
            <if test="shopImage != null">shop_image,</if>
            <if test="shopName != null">shop_name,</if>
            <if test="personName != null">person_name,</if>
            <if test="bindingAt != null">binding_at,</if>
            <if test="vip != null">vip,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="phone != null">#{phone},</if>
            <if test="wechat != null">#{wechat},</if>
            <if test="sex != null">#{sex},</if>
            <if test="openid != null">#{openid},</if>
            <if test="avatar != null">#{avatar},</if>
            <if test="name != null">#{name},</if>
            <if test="password != null">#{password},</if>
            <if test="token != null">#{token},</if>
            <if test="status != null">#{status},</if>
            <if test="introUser != null">#{introUser},</if>
            <if test="money != null">#{money},</if>
            <if test="allMoney != null">#{allMoney},</if>
            <if test="qrCode != null">#{qrCode},</if>
            <if test="createAt != null">#{createAt},</if>
            <if test="updateAt != null">#{updateAt},</if>
            <if test="delFlag != null">#{delFlag},</if>
            <if test="salesmanId != null">#{salesmanId},</if>
            <if test="province != null">#{province},</if>
            <if test="city != null">#{city},</if>
            <if test="area != null">#{area},</if>
            <if test="address != null">#{address},</if>
            <if test="shopImage != null">#{shopImage},</if>
            <if test="shopName != null">#{shopName},</if>
            <if test="personName != null">#{personName},</if>
            <if test="bindingAt != null">#{bindingAt},</if>
            <if test="vip != null">#{vip},</if>
         </trim>
    </insert>

    <insert id="insertLoginLog" parameterType="MallMemberLoginLog" useGeneratedKeys="true" keyProperty="id">
        insert into mall_member_login_log
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="userId != null">user_id,</if>
            <if test="loginAt != null">login_at,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="userId != null">#{userId},</if>
            <if test="loginAt != null">#{loginAt},</if>
         </trim>
    </insert>

    <update id="updateMallMemberUser" parameterType="MallMemberUser">
        update mall_member_user
        <trim prefix="SET" suffixOverrides=",">
            <if test="phone != null">phone = #{phone},</if>
            <if test="wechat != null">wechat = #{wechat},</if>
            <if test="sex != null">sex = #{sex},</if>
            <if test="openid != null">openid = #{openid},</if>
            <if test="avatar != null">avatar = #{avatar},</if>
            <if test="name != null">name = #{name},</if>
            <if test="password != null">password = #{password},</if>
            <if test="token != null">token = #{token},</if>
            <if test="status != null">status = #{status},</if>
            <if test="introUser != null">intro_user = #{introUser},</if>
            <if test="money != null">money = #{money},</if>
            <if test="allMoney != null">all_money = #{allMoney},</if>
            <if test="qrCode != null">qr_code = #{qrCode},</if>
            <if test="createAt != null">create_at = #{createAt},</if>
            <if test="updateAt != null">update_at = #{updateAt},</if>
            <if test="delFlag != null">del_flag = #{delFlag},</if>
            <if test="salesmanId != null">salesman_id = #{salesmanId},</if>
            <if test="province != null">province = #{province},</if>
            <if test="city != null">city = #{city},</if>
            <if test="area != null">area = #{area},</if>
            <if test="address != null">address = #{address},</if>
            <if test="shopImage != null">shop_image = #{shopImage},</if>
            <if test="shopName != null">shop_name = #{shopName},</if>
            <if test="personName != null">person_name = #{personName},</if>
            <if test="loginAt != null">login_at = #{loginAt},</if>
            <if test="bindingAt != null">binding_at = #{bindingAt},</if>
            <if test="vip != null">vip = #{vip},</if>
        </trim>
        where id = #{id}
    </update>

    <delete id="deleteMallMemberUserById" parameterType="Long">
        delete from mall_member_user where id = #{id}
    </delete>

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

    <insert id="insertMemberGoods" parameterType="MemberCollectVO">
        insert into mall_member_conllect (member_id, goods_id) values (#{memberId},#{goodsId});
    </insert>

    <insert id="insetIntoFootMarkVO" parameterType="MemberFootMarkVO">
        insert into mall_member_footmark (member_id, goods_id, update_time) values (#{memberId},#{goodsId},#{updateTime});
    </insert>

    <insert id="insertUserCoupon" parameterType="com.ruoyi.mall.domain.MallMemberCoupon">
        insert into mall_member_coupon
        (user_id, coupon_id, `name`, enough_price, `type`, create_at,price) values (#{userId}, #{couponId}, #{name}, #{ enoughPrice}, #{type}, #{createAt},#{price});
    </insert>

    <update id="updateFootMarkVO" parameterType="MemberFootMarkVO">
        update mall_member_footmark
        <trim prefix="SET" suffixOverrides=",">
            <if test="updateTime != null">update_time = #{updateTime},</if>
        </trim>
        where id = #{id}
    </update>

    <delete id="deleteMemberGoodsByParam">
        delete from mall_member_conllect
        where member_id = #{userId}
        and goods_id = #{goodsId}
    </delete>

    <delete id="deleteCollectByGoodsIds">
        delete from mall_member_conllect
        where member_id = #{userId}
        and goods_id in
        <foreach collection="ids" item="id" open="(" close=")" separator=",">
            #{id}
        </foreach>
    </delete>

    <select id="queryNoOrderDaysByUserId" parameterType="Long" resultType="integer">
        select IFNULL(
                       (
                           select DATEDIFF(sysdate(), create_at)
                           from mall_order_order
                           where user_id = #{userId}
                             and order_price > 0
                             and pay_type != '0'
                             and del_flag = '0'
                           order by create_at desc
                           limit 1 ),
                        (
                            select DATEDIFF(sysdate(), create_at) from mall_member_user
                                where id = #{userId}
                                and del_flag = '0'
                        )
                )
    </select>

    <select id="getSalesmanUserList" resultType="map">
        select id, phone, avatar, `name`, status,
        all_money allMoney, create_at createAt, salesman_id salesmanId, vip
        from mall_member_user
        <where>
            <if test="phone != null  and phone != ''"> and phone like concat('%', #{phone}, '%')</if>
            <if test="name != null  and name != ''"> and name like concat('%', #{name}, '%')</if>
            <if test="status != null  and status != ''"> and status = #{status}</if>
            <if test="vip != null  and vip != ''"> and vip = #{vip}</if>
            <if test="salesmanId != null  and salesmanId != ''"> and salesman_id = #{salesmanId}</if>
            and del_flag = '0'
        </where>
         order by create_at desc
    </select>

    <select id="getUserIntegralList" resultType="map">
        select order_no orderNo, TRUNCATE(integral, 0) integral, create_at createAt, order_type orderType, delivery_type deliveryType
        from mall_order_order
        <where>
            <if test="userId != null  and userId != ''"> and user_id = #{userId}</if>
            and status = '4'
            and TRUNCATE(integral, 0) > 0
            and del_flag = '0'
        </where>
         order by create_at desc
    </select>

    <select id="getUserAllIntegral" resultType="java.math.BigDecimal">
        select ifnull(sum(TRUNCATE(integral, 0)), 0) allIntegral
        from mall_order_order
        <where>
            <if test="userId != null  and userId != ''"> and user_id = #{userId}</if>
            and status = '4'
            and TRUNCATE(integral, 0) > 0
            and del_flag = '0'
        </where>
    </select>
</mapper>
