<?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.mcwl.communityCenter.mapper.CommunityUserMapper">
    <update id="updateBlackCommunityUser">
        update cc_community_user
        set is_black       = #{blackCommunityUser.isBlack},
            black_reason   = #{blackCommunityUser.blackReason},
            black_end_time = #{blackCommunityUser.blackEndTime}
        where tenant_id = #{blackCommunityUser.tenantId}
          and community_id = #{blackCommunityUser.communityId}
          and user_id = #{blackCommunityUser.userId}
          and ((CONVERT_TZ(NOW(), 'SYSTEM', '+08:00') &gt;= start_time
            AND CONVERT_TZ(NOW(), 'SYSTEM', '+08:00') &lt;= end_time) or (start_time is null and end_time is null))
          and del_flag = '0';
    </update>
    <update id="updateManageCommunityUser">
        update cc_community_user
        set user_type = #{manageCommunityUser.userType}
        where tenant_id = #{manageCommunityUser.tenantId}
          and community_id = #{manageCommunityUser.communityId}
          and user_id = #{manageCommunityUser.userId}
          and (black_end_time is null or NOW() &gt;= black_end_time)
          and ((CONVERT_TZ(NOW(), 'SYSTEM', '+08:00') &gt;= start_time
            AND CONVERT_TZ(NOW(), 'SYSTEM', '+08:00') &lt;= end_time) or (start_time is null and end_time is null))
          and del_flag = '0';
    </update>

    <select id="selectByTenantIdAndCommunityIdAndUserId"
            resultType="com.mcwl.communityCenter.domain.CommunityUser">
        select *
        from cc_community_user
        where tenant_id = #{tenantId}
          and community_id = #{communityId}
          and user_id = #{userId}
          and (black_end_time is null or NOW() &gt;= black_end_time)
          and ((CONVERT_TZ(NOW(), 'SYSTEM', '+08:00') &gt;= start_time
            AND CONVERT_TZ(NOW(), 'SYSTEM', '+08:00') &lt;= end_time) or (start_time is null and end_time is null))
          and del_flag = '0';
    </select>
    <select id="getJoinNum" resultType="java.lang.Integer">
        select count(*)
        from cc_community_user
        where tenant_id = #{tenantId}
          and community_id = #{communityId}
          and ((CONVERT_TZ(NOW(), 'SYSTEM', '+08:00') &gt;= start_time
            AND CONVERT_TZ(NOW(), 'SYSTEM', '+08:00') &lt;= end_time) or (start_time is null and end_time is null))
          and del_flag = '0';
    </select>
    <select id="isJoinCommunity" resultType="java.lang.Integer">
        select COALESCE(count(*), 0)
        from cc_community_user
        where tenant_id = #{tenantId}
          and community_id = #{communityId}
          and user_id = #{userId}
          and ((CONVERT_TZ(NOW(), 'SYSTEM', '+08:00') &gt;= start_time
            AND CONVERT_TZ(NOW(), 'SYSTEM', '+08:00') &lt;= end_time) or (start_time is null and end_time is null))
          and del_flag = '0';
    </select>
    <select id="getCommunityUserList" resultType="com.mcwl.communityCenter.domain.vo.CommunityUserVo">
        select cu.id,
        cu.tenant_id,
        cu.community_id,
        cu.user_id,
        cu.user_type,
        cu.is_black,
        u.avatar,
        u.nick_name,
        IF(cu.community_price != 0, '收费', '免费') AS join_type,
        cu.start_time,
        cu.end_time
        from cc_community_user cu
        left join sys_user u on cu.user_id = u.user_id
        <where>
            cu.community_id = #{communityUserListPageRes.communityId}
            and cu.tenant_id = #{communityUserListPageRes.tenantId}
            and cu.del_flag = '0'
            and ((CONVERT_TZ(NOW(), 'SYSTEM', '+08:00') &gt;= cu.start_time
            AND CONVERT_TZ(NOW(), 'SYSTEM', '+08:00')&lt;= cu.end_time) or (start_time is null and end_time is null))
            <if test="communityUserListPageRes.searchContent != null and communityUserListPageRes.searchContent != ''">
                and u.nick_name like concat('%', #{communityUserListPageRes.searchContent}, '%')
            </if>
        </where>
        order by cu.user_type desc, cu.is_black, cu.create_time
    </select>
    <select id="selectCommunityUser" resultType="com.mcwl.communityCenter.domain.CommunityUser">
        select id,
               tenant_id,
               community_id,
               user_id,
               user_type,
               invite_id,
               community_price,
               start_time,
               end_time,
               is_black,
               black_reason,
               black_end_time
        from cc_community_user
        where tenant_id = #{tenantId}
          and community_id = #{communityId}
          and user_id = #{userId}
          and ((CONVERT_TZ(NOW(), 'SYSTEM', '+08:00') &gt;= start_time
            AND CONVERT_TZ(NOW(), 'SYSTEM', '+08:00') &lt;= end_time) or (start_time is null and end_time is null))
          and (black_end_time is null or NOW() &gt;= black_end_time)
    </select>
    <select id="getAllCommunityUser" resultType="com.mcwl.communityCenter.domain.CommunityUser">
        select id,
               tenant_id,
               community_id,
               user_id,
               user_type,
               invite_id,
               community_price,
               start_time,
               end_time,
               is_black,
               black_reason,
               black_end_time
        from cc_community_user
        where del_flag = '0'
          and ((CONVERT_TZ(NOW(), 'SYSTEM', '+08:00') &gt;= start_time
            AND CONVERT_TZ(NOW(), 'SYSTEM', '+08:00') &lt;= end_time) or (start_time is null and end_time is null))
    </select>
</mapper>