<?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.busi.mapper.BusiNoticeSignMapper">

    <resultMap type="BusiNoticeSign" id="BusiNoticeSignResult">
        <result property="id"    column="id"    />
        <result property="noticeId"    column="notice_id"    />
        <result property="userId"    column="user_id"    />
        <result property="nickname"    column="nickname"    />
        <result property="avatar"    column="avatar"    />
        <result property="isSuper"    column="is_super"    />
        <result property="tel"    column="tel"    />
        <result property="addrId"    column="addr_id"    />
        <result property="status"    column="status"    />
        <result property="content"    column="content"    />
        <result property="images"    column="images"    />
        <result property="articleRef"    column="article_ref"    />
        <result property="remark"    column="remark"    />
        <result property="creator"    column="creator"    />
        <result property="createTime"    column="create_time"    />
        <result property="updater"    column="updater"    />
        <result property="updateTime"    column="update_time"    />
        <result property="delFlag"    column="del_flag"    />
    </resultMap>

    <sql id="selectBusiNoticeSignVo">
        select id, notice_id, user_id, nickname, avatar, is_super, tel, addr_id, status, content, images, article_ref, remark, creator, create_time, updater, update_time, del_flag from dl_busi_notice_sign
    </sql>

    <select id="queryListPage" parameterType="BusiNoticeSign" resultMap="BusiNoticeSignResult">
        <include refid="selectBusiNoticeSignVo"/>
        <where>
                        <if test="entity.noticeId != null  and entity.noticeId != ''"> and notice_id like concat('%', #{entity.noticeId}, '%')</if>
                        <if test="entity.nickname != null  and entity.nickname != ''"> and nickname like concat('%', #{entity.nickname}, '%')</if>
                        <if test="entity.isSuper != null "> and is_super = #{entity.isSuper}</if>
                        <if test="entity.tel != null  and entity.tel != ''"> and tel like concat('%', #{entity.tel}, '%')</if>
                        <if test="entity.status != null  and entity.status != ''"> and status = #{entity.status}</if>
        </where>
        order by isSuper desc, create_time asc
    </select>
    <select id="selectReportNumByIdList" resultType="java.util.Map">
        SELECT
            dbns.id,
            COUNT( dbns.id ) AS reportNum
        FROM
            dl_busi_notice_sign dbns
        WHERE
            dbns.del_flag = 0
          AND dbns.notice_id IN
        <foreach collection="list" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>
        GROUP BY
            dbns.id
    </select>
    <select id="reportList" resultType="com.ruoyi.busi.domain.BusiNoticeSign">
        select sign.*,card.platform_name as platformName,
               card.account_name as accountName,card.fans_num as fansNum
                ,card.platform_code as platformCode,card.index_url,card.account_number,CONCAT(address.city_name, ' ', address.detail) as addrName,card.wx_num,sign.create_time as signTime
        from dl_busi_notice_sign sign
        inner join dl_member_busi_card card on sign.card_id = card.id
        left join dl_member_address address on address.id = sign.addr_id
        <where>
            <if test="entity.noticeId != null  and entity.noticeId != ''"> and sign.notice_id like concat('%', #{entity.noticeId}, '%')</if>

            <if test="entity.status != null  and entity.status != ''"> and sign.status = #{entity.status}</if>
        </where>
        order by sign.is_super desc, sign.create_time asc
    </select>
</mapper>
