<?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.frequency.customer.mapper.CustomerMapper">

    <resultMap type="com.ruoyi.frequency.customer.entity.Customer" id="CustomerResult">
        <result property="id"    column="id"    />
        <result property="createBy"    column="create_by"    />
        <result property="createTime"    column="create_time"    />
        <result property="updateBy"    column="update_by"    />
        <result property="updateTime"    column="update_time"    />
        <result property="delFlag"    column="del_flag"    />
        <result property="account"    column="account"    />
        <result property="mobile"    column="mobile"    />
        <result property="nickname"    column="nickname"    />
        <result property="avatar"    column="avatar"    />
        <result property="areaCode"    column="area_code"    />
        <result property="loginPwd"    column="login_pwd"    />
        <result property="payPwd"    column="pay_pwd"    />
        <result property="cancellationTime"    column="cancellation_time"    />
        <result property="clientId"    column="client_id"    />
        <result property="wechatOpenid"    column="wechat_openid"    />
        <result property="wechatAppletOpenid"    column="wechat_applet_openid"    />
        <result property="appleOpenid"    column="apple_openid"    />
        <result property="closeMessage"    column="close_message"    />
        <result property="invitationCode"    column="invitation_code"    />
        <result property="balance"    column="balance"    />
        <result property="enterDate"    column="enter_date"    />
        <result property="onlineStatus"    column="online_status"    />
        <result property="onlineDuration"    column="online_duration"    />
        <result property="levelId"    column="level_id"    />
        <result property="star"    column="star"    />
        <result property="consumptionAmount"    column="consumption_amount"    />
        <result property="area"    column="area"    />
        <result property="levelName"    column="levelName"    />
        <result property="fansNum"    column="fansNum"    />
        <result property="followNum"    column="followNum"    />
        <result property="name"    column="name"    />
    </resultMap>

    <sql id="selectCustomerVo">
        SELECT
            t.id,
            t.create_by,
            t.create_time,
            t.update_by,
            t.update_time,
            t.del_flag,
            t.account,
            t.mobile,
            t.nickname,
            t.name,
            t.avatar,
            t.area_code,
            t.login_pwd,
            t.pay_pwd,
            t.cancellation_time,
            t.client_id,
            t.wechat_openid,
            t.wechat_applet_openid,
            t.apple_openid,
            t.close_message,
            t.invitation_code,
            t.balance,
            t.give_balance,
            t.enter_date,
            t.online_status,
            t.online_duration,
            t.level_id,
            t.remark,
            t.star,
            t.consumption_amount,
            t.area,
            t.integral,
            l.`name` levelName,
            (SELECT count( 1 ) FROM t_follow f WHERE f.del_flag = 1 AND f.user_type = 1 AND f.user_id = t.id) fansNum,
            (SELECT count( 1 ) FROM t_follow f WHERE f.del_flag = 1 AND f.follower_user_type = 1 AND f.follower_user_id = t.id) followNum,
            (SELECT count(1)
             FROM t_order o
             WHERE o.first_status = 2 AND o.customer_id = t.id ) orderCount,
                (
            SELECT
                IFNULL(ROUND(AVG(o.amount), 2) , 0)
            FROM
                t_order o
            WHERE
                o.first_status = 2
              AND o.customer_id = t.id
                ) unitPrice
                , (
            SELECT
                create_time
            FROM
                t_order o
            WHERE
                o.first_status = 2
              AND o.customer_id = t.id
            ORDER BY
                o.create_time DESC
                LIMIT 1
                ) lastTransactionTime,
                (
            SELECT
                IFNULL(sum (o.amount), 0)
            FROM
                t_order o
            WHERE
                o.first_status = 2
              AND o.customer_id = t.id
              AND DATE_FORMAT(o.create_time
                , '%Y-%m-01') >= DATE_FORMAT(CURDATE()
                , '%Y-%m-01')
                ) monthAmount
        FROM
            t_customer AS t
                LEFT JOIN t_level l ON l.type = 1 and l.id = (
                SELECT
                    l2.id
                FROM
                    t_level l2
                WHERE
                    l2.del_flag = 1
                  and l2.type =1
                  and l2.compliance &lt;= t.consumption_amount
                ORDER BY
                    l2.compliance DESC
                LIMIT 1
            )
    </sql>

    <select id="selectCustomerList" parameterType="com.ruoyi.frequency.customer.entity.Customer" resultMap="CustomerResult">
        <include refid="selectCustomerVo"/>
        <where> and t.del_flag != 2
            <if test="delFlag != null "> and t.del_flag = #{delFlag}</if>
            <if test="account != null  and account != ''"> and t.account like concat('%', #{account}, '%')</if>
            <if test="mobile != null  and mobile != ''"> and t.mobile like concat('%', #{mobile}, '%')</if>
            <if test="nickname != null  and nickname != ''"> and t.nickname like concat('%', #{nickname}, '%')</if>
            <if test="avatar != null  and avatar != ''"> and t.avatar = #{avatar}</if>
            <if test="areaCode != null  and areaCode != ''"> and t.area_code = #{areaCode}</if>
            <if test="loginPwd != null  and loginPwd != ''"> and t.login_pwd = #{loginPwd}</if>
            <if test="payPwd != null  and payPwd != ''"> and t.pay_pwd = #{payPwd}</if>
            <if test="cancellationTime != null "> and t.cancellation_time = #{cancellationTime}</if>
            <if test="clientId != null  and clientId != ''"> and t.client_id = #{clientId}</if>
            <if test="wechatOpenid != null  and wechatOpenid != ''"> and t.wechat_openid = #{wechatOpenid}</if>
            <if test="wechatAppletOpenid != null  and wechatAppletOpenid != ''"> and t.wechat_applet_openid = #{wechatAppletOpenid}</if>
            <if test="appleOpenid != null  and appleOpenid != ''"> and t.apple_openid = #{appleOpenid}</if>
            <if test="closeMessage != null "> and t.close_message = #{closeMessage}</if>
            <if test="invitationCode != null  and invitationCode != ''"> and t.invitation_code = #{invitationCode}</if>
            <if test="balance != null "> and t.balance = #{balance}</if>
            <if test="enterDate != null "> and t.enter_date = #{enterDate}</if>
            <if test="onlineDuration != null "> and t.online_duration = #{onlineDuration}</if>
            <if test="levelId != null "> and l.id = #{levelId}</if>
            <if test="star != null "> and t.star = #{star}</if>
            <if test="beginTime != null ">
                and  t.create_time >= #{beginTime}

            </if>
            <if test="endTime != null ">
                and  t.create_time &lt; #{endTime}
            </if>
            <if test="onlineStatus != null ">
                <if test="onlineStatus == 1 ">
                    AND t.id in
                    <foreach item="id" collection="userIds" open="(" separator="," close=")">
                        #{id}
                    </foreach>
                </if>
                <if test="onlineStatus == 2 ">
                    t.id not in
                    <foreach item="id" collection="userIds" open="(" separator="," close=")">
                        #{id}
                    </foreach>
                </if>
            </if>
        </where>
        <choose>
            <when test="sortType != null ">
                <if test="sortType == 1 ">
                    order by t.online_duration desc
                </if>
                <if test="sortType == 2 ">
                    order by t.online_duration
                </if>
            </when>
            <otherwise>
                order by t.create_time desc
            </otherwise>
        </choose>
    </select>

    <select id="statistics" parameterType="com.ruoyi.frequency.customer.entity.Customer" resultType="com.ruoyi.frequency.customer.model.result.CustomerCount">
        SELECT
        count(1) number,
        ifnull(sum(t.online_duration),0) online_duration,
        ifnull(sum(t.balance),0) balance,
        ifnull(sum(t.give_balance),0) giveBalance,
        ifnull(sum(t.consumption_amount),0) consumption_amount,
        ifnull(sum((SELECT count( 1 ) FROM t_follow f WHERE f.del_flag = 1 AND f.user_type = 1 AND f.user_id = t.id)),0) fansNum,
        ifnull(sum((SELECT count( 1 ) FROM t_follow f WHERE f.del_flag = 1 AND f.follower_user_type = 1 AND f.follower_user_id = t.id)),0) followNum
        FROM
        t_customer AS t
        where
        t.id in
        <foreach item="id" collection="list" open="(" separator="," close=")">
            #{id}
        </foreach>
    </select>

    <select id="onlineNumber" parameterType="com.ruoyi.frequency.customer.entity.Customer" resultType="java.lang.Integer">
        SELECT
        count(1) number
        FROM
        t_customer AS t
        LEFT JOIN t_level l ON l.type = 1 and l.id = (
        SELECT
        l2.id
        FROM
        t_level l2
        WHERE
        l2.del_flag = 1
        and l2.type =1
        and l2.compliance &lt;= t.consumption_amount
        ORDER BY
        l2.compliance DESC
        LIMIT 1
        )
        <where> and t.del_flag != 2
            <if test="delFlag != null "> and t.del_flag = #{delFlag}</if>
            <if test="account != null  and account != ''"> and t.account like concat('%', #{account}, '%')</if>
            <if test="mobile != null  and mobile != ''"> and t.mobile like concat('%', #{mobile}, '%')</if>
            <if test="nickname != null  and nickname != ''"> and t.nickname like concat('%', #{nickname}, '%')</if>
            <if test="avatar != null  and avatar != ''"> and t.avatar = #{avatar}</if>
            <if test="areaCode != null  and areaCode != ''"> and t.area_code = #{areaCode}</if>
            <if test="loginPwd != null  and loginPwd != ''"> and t.login_pwd = #{loginPwd}</if>
            <if test="payPwd != null  and payPwd != ''"> and t.pay_pwd = #{payPwd}</if>
            <if test="cancellationTime != null "> and t.cancellation_time = #{cancellationTime}</if>
            <if test="clientId != null  and clientId != ''"> and t.client_id = #{clientId}</if>
            <if test="wechatOpenid != null  and wechatOpenid != ''"> and t.wechat_openid = #{wechatOpenid}</if>
            <if test="wechatAppletOpenid != null  and wechatAppletOpenid != ''"> and t.wechat_applet_openid = #{wechatAppletOpenid}</if>
            <if test="appleOpenid != null  and appleOpenid != ''"> and t.apple_openid = #{appleOpenid}</if>
            <if test="closeMessage != null "> and t.close_message = #{closeMessage}</if>
            <if test="invitationCode != null  and invitationCode != ''"> and t.invitation_code = #{invitationCode}</if>
            <if test="balance != null "> and t.balance = #{balance}</if>
            <if test="enterDate != null "> and t.enter_date = #{enterDate}</if>
            <if test="onlineDuration != null "> and t.online_duration = #{onlineDuration}</if>
            <if test="levelId != null "> and l.id = #{levelId}</if>
            <if test="star != null "> and t.star = #{star}</if>
            <if test="beginTime != null ">
                and  t.create_time >= #{beginTime}

            </if>
            <if test="endTime != null ">
                and  t.create_time &lt; #{endTime}
            </if>
            <if test="onlineStatus != null ">
                <if test="onlineStatus == 1 ">
                    AND t.id in
                    <foreach item="id" collection="userIds" open="(" separator="," close=")">
                        #{id}
                    </foreach>
                </if>
                <if test="onlineStatus == 2 ">
                    t.id not in
                    <foreach item="id" collection="userIds" open="(" separator="," close=")">
                        #{id}
                    </foreach>
                </if>
            </if>
        </where>
        <choose>
            <when test="sortType != null ">
                <if test="sortType == 1 ">
                    order by t.online_duration desc
                </if>
                <if test="sortType == 2 ">
                    order by t.online_duration
                </if>
            </when>
            <otherwise>
                order by t.create_time desc
            </otherwise>
        </choose>
    </select>

    <select id="selectCustomerById" parameterType="Long" resultMap="CustomerResult">
        <include refid="selectCustomerVo"/>
        where t.id = #{id}
    </select>

    <insert id="insertCustomer" parameterType="com.ruoyi.frequency.customer.entity.Customer">
        insert into t_customer
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="id != null">id,</if>
            <if test="createUser != null">create_by,</if>
            <if test="createTime != null">create_time,</if>
            <if test="updateUser != null">update_by,</if>
            <if test="updateTime != null">update_time,</if>
            <if test="delFlag != null">del_flag,</if>
            <if test="account != null">account,</if>
            <if test="mobile != null">mobile,</if>
            <if test="nickname != null">nickname,</if>
            <if test="avatar != null">avatar,</if>
            <if test="areaCode != null">area_code,</if>
            <if test="loginPwd != null">login_pwd,</if>
            <if test="payPwd != null">pay_pwd,</if>
            <if test="cancellationTime != null">cancellation_time,</if>
            <if test="clientId != null">client_id,</if>
            <if test="wechatOpenid != null">wechat_openid,</if>
            <if test="wechatAppletOpenid != null">wechat_applet_openid,</if>
            <if test="appleOpenid != null">apple_openid,</if>
            <if test="closeMessage != null">close_message,</if>
            <if test="invitationCode != null and invitationCode != ''">invitation_code,</if>
            <if test="balance != null">balance,</if>
            <if test="enterDate != null">enter_date,</if>
            <if test="onlineStatus != null">online_status,</if>
            <if test="onlineDuration != null">online_duration,</if>
            <if test="levelId != null">level_id,</if>
            <if test="star != null">star,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="id != null">#{id},</if>
            <if test="createUser != null">#{createUser},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="updateUser != null">#{updateUser},</if>
            <if test="updateTime != null">#{updateTime},</if>
            <if test="delFlag != null">#{delFlag},</if>
            <if test="account != null">#{account},</if>
            <if test="mobile != null">#{mobile},</if>
            <if test="nickname != null">#{nickname},</if>
            <if test="avatar != null">#{avatar},</if>
            <if test="areaCode != null">#{areaCode},</if>
            <if test="loginPwd != null">#{loginPwd},</if>
            <if test="payPwd != null">#{payPwd},</if>
            <if test="cancellationTime != null">#{cancellationTime},</if>
            <if test="clientId != null">#{clientId},</if>
            <if test="wechatOpenid != null">#{wechatOpenid},</if>
            <if test="wechatAppletOpenid != null">#{wechatAppletOpenid},</if>
            <if test="appleOpenid != null">#{appleOpenid},</if>
            <if test="closeMessage != null">#{closeMessage},</if>
            <if test="invitationCode != null and invitationCode != ''">#{invitationCode},</if>
            <if test="balance != null">#{balance},</if>
            <if test="enterDate != null">#{enterDate},</if>
            <if test="onlineStatus != null">#{onlineStatus},</if>
            <if test="onlineDuration != null">#{onlineDuration},</if>
            <if test="levelId != null">#{levelId},</if>
            <if test="star != null">#{star},</if>
        </trim>
    </insert>
    <insert id="insertOnlineLog">
        insert into t_online_log (user_id,user_type,create_time)
        values (#{userVo.userId},#{userVo.userType},NOW());
    </insert>

    <update id="updateCustomer" parameterType="com.ruoyi.frequency.customer.entity.Customer">
        update t_customer
        <trim prefix="SET" suffixOverrides=",">
            <if test="createUser != null">create_by = #{createUser},</if>
            <if test="createTime != null">create_time = #{createTime},</if>
            <if test="updateUser != null">update_by = #{updateUser},</if>
            <if test="updateTime != null">update_time = #{updateTime},</if>
            <if test="delFlag != null">del_flag = #{delFlag},</if>
            <if test="account != null">account = #{account},</if>
            <if test="mobile != null">mobile = #{mobile},</if>
            <if test="nickname != null">nickname = #{nickname},</if>
            <if test="avatar != null">avatar = #{avatar},</if>
            <if test="areaCode != null">area_code = #{areaCode},</if>
            <if test="loginPwd != null">login_pwd = #{loginPwd},</if>
            <if test="payPwd != null">pay_pwd = #{payPwd},</if>
            <if test="cancellationTime != null">cancellation_time = #{cancellationTime},</if>
            <if test="clientId != null">client_id = #{clientId},</if>
            <if test="wechatOpenid != null">wechat_openid = #{wechatOpenid},</if>
            <if test="wechatAppletOpenid != null">wechat_applet_openid = #{wechatAppletOpenid},</if>
            <if test="appleOpenid != null">apple_openid = #{appleOpenid},</if>
            <if test="closeMessage != null">close_message = #{closeMessage},</if>
            <if test="invitationCode != null and invitationCode != ''">invitation_code = #{invitationCode},</if>
            <if test="balance != null">balance = #{balance},</if>
            <if test="enterDate != null">enter_date = #{enterDate},</if>
            <if test="onlineStatus != null">online_status = #{onlineStatus},</if>
            <if test="onlineDuration != null">online_duration = #{onlineDuration},</if>
            <if test="levelId != null">level_id = #{levelId},</if>
            <if test="star != null">star = #{star},</if>
        </trim>
        where id = #{id}
    </update>
    <update id="saveIdNo">
        UPDATE t_customer
        SET nickname = (
            SELECT
                tt.num
            FROM
                (
                    SELECT
                        count(*) AS num
                    FROM
                        t_customer
                    WHERE
                        create_time &lt;= (
                            SELECT
                                create_time
                            FROM
                                t_customer
                            WHERE
                                id = #{id}
                        )
                ) tt
        )
        WHERE
            id = #{id}
    </update>

    <delete id="deleteCustomerById" parameterType="Long">
        delete from t_customer where id = #{id}
    </delete>

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

    <select id="info" resultType="com.ruoyi.controller.resp.UserResultInfo">
        SELECT
            t.id userId,
            t.invitation_code,
            1 userType,
            t.create_time,
            t.update_time,
            t.del_flag,
            t.nickname,
            t.avatar,
            t.mobile,
            t.login_pwd,
            t.pay_pwd,
            t.cancellation_time,
            t.client_id,
            t.wechat_openid,
            t.apple_openid,
            t.balance,
            t.close_message,
            t.star,
            l.`name` levelName,
            l.icon,
            l.edge_piping_type,
            t.online_duration,
            t.enter_date,
            t.area,
            t.integral,
            t.give_balance,
            t.sex
        FROM
            t_customer AS t
                LEFT JOIN t_level l ON l.type = 1 and l.id = (
                SELECT
                    l2.id
                FROM
                    t_level l2
                WHERE
                    l2.del_flag = 1
                  and l2.type = 1
                  and l2.compliance &lt;= t.consumption_amount
                ORDER BY
                    l2.compliance DESC
                LIMIT 1
            )
        WHERE
            t.id = #{userId}
    </select>


    <select id="longestIdNo" resultType="java.lang.String">
        SELECT
            nickname
        FROM
                (SELECT nickname FROM t_customer t) tt
        ORDER BY
            LENGTH(tt.nickname) DESC
        LIMIT 1
    </select>


    <select id="customerPage" parameterType="com.ruoyi.frequency.customer.entity.Customer" resultMap="CustomerResult">
        <include refid="selectCustomerVo"/>
        <where> AND t.del_flag = 1
            <if test="req.keyword != null  and req.keyword != ''"> AND t.nickname like concat('%', #{req.keyword}, '%')</if>
            <if test="req.type != null ">
                <if test="req.type == 1 ">
                    AND  ( SELECT
                    count(1)
                    FROM
                    t_follow f
                    WHERE
                    f.follower_user_id = #{userVo.userId}
                    AND f.follower_user_type = #{userVo.userType}
                    AND f.user_type = 1
                    AND f.user_id = t.id
                    AND f.del_flag = 1
<!--                    <if test="req.blockUserList!=null and req.blockUserList.size() > 0">-->
<!--                        AND (-->
<!--                        (f.follower_user_type = 1 and f.follower_user_id not in-->
<!--                        <foreach collection="req.blockUserList" item="userVo" open="(" close=")" separator=",">-->
<!--                            IF(#{userVo.userType} = 1,#{userVo.userId},'')-->
<!--                        </foreach>)-->
<!--                        )-->
<!--                    </if>-->

<!--                    <if test="req.shieldUserList!=null and req.shieldUserList.size() > 0">-->
<!--                        AND (-->
<!--                        (f.follower_user_type = 1 and f.follower_user_id not in-->
<!--                        <foreach collection="req.shieldUserList" item="userVo" open="(" close=")" separator=",">-->
<!--                            IF(#{userVo.userType} = 1,#{userVo.userId},'')-->
<!--                        </foreach>)-->
<!--                        )-->
<!--                    </if>-->
                    ) > 0
                </if>
            </if>
        </where>
        order by t.create_time desc
    </select>
    <select id="canCancellation" resultType="java.lang.Integer">
        select count(*) from t_order t where t.customer_id = #{userId} and t.status in (1,2,3)
    </select>
    <select id="getOrderInfo"
            resultType="com.ruoyi.frequency.illegaldeductionrecord.model.result.OrderInfoResp">
        select * from t_order where id = #{orderId}
    </select>
</mapper>
