<?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.UserMapper">

    <resultMap type="com.ruoyi.vo.UserInfo" id="UserInfo">
        <result property="userId"    column="id"    />
        <result property="userType"    column="userType"    />
        <result property="name"    column="name"    />
        <result property="nickname"    column="nickname"    />
        <result property="avatar"    column="avatar"    />
        <result property="mobile"    column="mobile"    />
        <result property="ifHighend"    column="ifHighend"    />
        <result property="isAuth"    column="isAuth"    />
        <result property="icon"    column="icon"    />
        <result property="edgePipingType"    column="edge_piping_type"    />
        <result property="levelName"    column="levelName"    />
        <result property="area"    column="area"    />
        <result property="enterDate"    column="enter_date"    />
        <result property="specialityArea"    column="speciality_area"    />
        <result property="isShield"    column="isShield"    />
        <result property="isBlock"    column="isBlock"    />
        <result property="sex"    column="sex"    />
        <result property="wecomQrcode"    column="wecom_qrcode"    />
        <result property="isKujialeAuth"    column="isKujialeAuth"    />
        <result property="name"    column="name"    />
        <result property="isDdAuth"    column="is_dd_auth"    />
        <result property="orderAmount"    column="orderAmount"    />
        <result property="orderMonthNum"    column="orderMonthNum"    />
        <result property="orderNum"    column="orderNum"    />
        <result property="bondAmount"    column="bond_amount"    />
        <result property="worksNum"    column="worksNum"    />
        <result property="postNum"    column="postNum"    />
        <result property="fansNum"    column="fansNum"    />
    </resultMap>

    <resultMap type="com.ruoyi.vo.UserVo" id="UserVo">
        <result property="userId"    column="user_id"    />
        <result property="userType"    column="user_type"    />
    </resultMap>

    <select id="selectUserInfoList" resultType="java.util.List" resultMap="UserInfo">
        SELECT
        tt.id,
        tt.userType,
        tt.mobile,
        tt.nickname,
        tt.name,
        tt.avatar,
        case when f.id is null then 1 else 2 end as isFollow,
        case when s.id is null then 1 else 2 end as isShield,
        case when b.id is null then 1 else 2 end as isBlock,
        tt.ifHighend,
        tt.isAuth,
        tt.isKujialeAuth,
        tt.icon,
        tt.edge_piping_type,
        tt.levelName,
        tt.area,
        tt.enter_date,
        tt.speciality_area,
        tt.sex,
        tt.wecom_qrcode,
        tt.is_dd_auth,
        tt.orderNum,
        tt.orderMonthNum,
        tt.orderAmount,
        tt.bond_amount,
        tt.star,
        tt.worksNum,
        tt.postNum,
        tt.fansNum
        FROM
        (
        SELECT
        c.id,
        1 userType,
        c.mobile,
        c.nickname,
        c.name,
        c.avatar,
        1 ifHighend,
        1 isAuth,
        1 isKujialeAuth,
        l.icon,
        l.edge_piping_type,
        l.name levelName,
        c.area,
        c.enter_date,
        null branch_work,
        null speciality_area,
        c.sex sex,
        null wecom_qrcode,
        1 is_dd_auth,
        (select count(1) from t_order o where c.id = o.customer_id and o.status >= 4) orderNum,
        null orderMonthNum,
        null orderAmount,
        null bond_amount,
        c.star,
        (select count(1) from t_works w where c.id = w.customer_id and w.del_flag = 1 and w.is_hide != 2 ) worksNum,
        (select count(1) from t_post p where c.id = p.user_id and p.user_type = 1 and p.del_flag = 1 ) postNum,
        (select count(1) from t_follow f where c.id = f.follower_user_id and f.follower_user_type = 1 and f.del_flag = 1 ) fansNum
        FROM
        t_customer c
        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;= c.consumption_amount
        ORDER BY
        l2.compliance DESC
        LIMIT 1
        )
        <where>
            <choose>
                <when test="customerIds != null and customerIds.size() > 0">
                    c.id in
                    <foreach collection="customerIds" item="id" open="(" close=")" separator=",">
                        #{id}
                    </foreach>
                </when>
                <otherwise>
                    FALSE
                </otherwise>
            </choose>
        </where>

        UNION ALL
        SELECT
        s.id,
        2 userType,
        s.mobile,
        s.nickname,
        s.name,
        s.avatar,
        case when h.id is null then 1 else 2 end as ifHighend,
        case when a.id is not null and a.type = 1 then a.result
        else 0
        end as isAuth,
        case when a.id is not null and a.type = 3 then a.result
        else 0
        end as isKujialeAuth,
        l.icon,
        l.edge_piping_type,
        l.name levelName,
        s.area,
        s.enter_date,
        case when h.id is null then a.branch_work else h.branch_work end as branch_work,
        s.speciality_area,
        a.sex ,
        s.wecom_qrcode,
        s.is_dd_auth,
        (select count(1) from t_order o where s.id = o.store_id and o.status >= 4) orderNum,

        (select count(1) from t_order o where s.id = o.store_id and o.status >= 4 and month(o.create_time) = month(now())) orderMonthNum,

        ifnull((select ifnull(sum(o.amount),0) from t_order o where s.id = o.store_id and o.status >= 4),0) orderAmount,

        s.bond_amount,
            s.star,
        (select count(1) from t_works w where s.id = w.store_id and w.del_flag = 1 and w.is_hide != 2 ) worksNum,
        (select count(1) from t_post p where s.id = p.user_id and p.user_type = 2 and p.del_flag = 1 ) postNum,
        (select count(1) from t_follow f where s.id = f.follower_user_id and f.follower_user_type = 2 and f.del_flag = 1 ) fansNum

        FROM
        t_store s
        LEFT JOIN t_highend_auth h ON s.id = h.store_id and h.status >= 3 and h.del_flag = 1
        LEFT JOIN t_auth a ON s.id = a.store_id and a.del_flag = 1 and a.pay_status = 2
        LEFT JOIN t_level l ON l.type = 2
        and l.id = (
        SELECT
        l2.id
        FROM
        t_level l2
        WHERE
        l2.del_flag = 1
        and l2.type = 2
        and l2.compliance &lt;= s.income
        ORDER BY
        l2.compliance DESC
        LIMIT 1
        )
        <where>
            <choose>
                <when test="storeIds != null and storeIds.size() > 0">
                    s.id in
                    <foreach collection="storeIds" item="id" open="(" close=")" separator=",">
                        #{id}
                    </foreach>
                </when>
                <otherwise>
                    FALSE
                </otherwise>
            </choose>
        </where>

        UNION ALL

        SELECT
        id,
        3 userType,
        mobile,
        nickname,
        name,
        avatar,
        1 ifHighend,
        1 isAuth,
        1 isKujialeAuth,
        null icon,
        null edge_piping_type,
        null levelName,
        null area,
        null enter_date,
        null branch_work,
        null speciality_area,
        null sex,
        null wecom_qrcode,
        1 is_dd_auth,
        null orderNum,
        null orderMonthNum,
        null orderAmount,
        null bond_amount,
            null star,
            0 worksNum,
        0 postNum,
        0 fansNum
        FROM
        t_sale
        <where>
            <choose>
                <when test="saleIds != null and saleIds.size() > 0">
                    id in
                    <foreach collection="saleIds" item="id" open="(" close=")" separator=",">
                        #{id}
                    </foreach>
                </when>
                <otherwise>
                    FALSE
                </otherwise>
            </choose>
        </where>

        UNION ALL
        SELECT
        e.id,
        4 userType,
        NULL mobile,
        e. NAME nickname,
        e. NAME,
        e.avatar,
        null ifHighend,
        NULL isAuth,
        NULL isKujialeAuth,
        NULL icon,
        NULL edge_piping_type,
        NULL levelName,
        NULL area,
        NULL enter_date,
        NULL branch_work,
        e.good_at speciality_area,
        NULL sex,
        e.wecom wecom_qrcode,
        NULL is_dd_auth,
        NULL orderNum,
        NULL orderMonthNum,
        NULL orderAmount,
        NULL bond_amount,
        NULL star,
        0 worksNum,
        (
        SELECT
        count(1)
        FROM
        t_post p
        WHERE
        e.id = p.user_id
        AND p.user_type = 4
        AND p.del_flag = 1
        ) postNum,
        (
        SELECT
        count(1)
        FROM
        t_follow f
        WHERE
        e.id = f.follower_user_id
        AND f.follower_user_type = 4
        AND f.del_flag = 1
        ) fansNum
        FROM
        t_enterprise e
        <where>
            <choose>
                <when test="enterpriseIds != null and enterpriseIds.size() > 0">
                    and  e.id in
                    <foreach collection="enterpriseIds" item="id" open="(" close=")" separator=",">
                        #{id}
                    </foreach>
                </when>
                <otherwise>
                    and FALSE
                </otherwise>
            </choose>
        </where>
        ) tt
        LEFT JOIN t_follow f on f.user_id = #{userVo.userId}
        and f.user_type =  #{userVo.userType}
        and f.follower_user_id = tt.id
        and f.follower_user_type = tt.userType
        and f.del_flag = 1

        LEFT JOIN t_shield s on s.user_id = #{userVo.userId}
        and s.user_type =  #{userVo.userType}
        and s.shield_user_id = tt.id
        and s.shield_user_type = tt.userType
        and s.del_flag = 1

        LEFT JOIN t_block b on b.user_id = #{userVo.userId}
        and b.user_type =  #{userVo.userType}
        and b.block_user_id = tt.id
        and b.block_user_type = tt.userType
        and b.del_flag = 1
    </select>


    <select id="blockUserList" resultType="java.util.List" resultMap="UserVo">
        SELECT
            IF
            ( t.user_id = #{userVo.userId} AND t.user_type = #{userVo.userType}, t.block_user_id, t.user_id ) user_id,
            IF
            ( t.user_id = #{userVo.userId} AND t.user_type = #{userVo.userType}, t.block_user_type, t.user_type ) user_type
        FROM
            t_block t
        WHERE
            t.del_flag = 1
          AND (
            ( t.user_id = #{userVo.userId} AND t.user_type = #{userVo.userType} )
                OR ( t.block_user_id = #{userVo.userId} AND t.block_user_type = #{userVo.userType} )
            )
    </select>

    <select id="shieldUserList" resultType="java.util.List" resultMap="UserVo">
        SELECT
            t.shield_user_type user_type,
            t.shield_user_id user_id
        FROM
            t_shield t
        WHERE
            t.del_flag = 1
          AND t.user_type = #{userVo.userType}
          AND t.user_id = #{userVo.userId}
    </select>
</mapper>
