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


    <select id="queryListPage"  resultType="com.ruoyi.busi.vo.BusiNoticeVo">
        select main.id, main.user_id, main.title, main.platform_code, main.province, main.city, main.fee_down, main.fee_up, main.is_self_price, main.gift_detail,
        main.gift_price, main.end_date, main.brand, main.is_show_brand, main.need_num, main.fans_down, main.fans_up, main.is_eligible, main.pic, main.collect, main.detail, main.images,
        main.blogger_types, main.is_show_tel, main.wechat, main.tel, main.group_image, main.is_use_coupon, main.approval_status, main.approval_user_id, main.approval_time,
        main.approval_remark, main.creator, main.create_time, main.updater, main.update_time, main.del_flag,main.is_platform_free,main.is_urgent,
        uTable.nick_name as userNickName,auditTable.nick_name as approvalUserName,ifnull(noticeView.view_num,0) as viewNum,
        bTable.title as platformName
        from dl_busi_notice main
        left join sys_user uTable on main.user_id = uTable.user_id
        left join sys_user auditTable on main.approval_user_id = auditTable.user_id
        left join dl_base_category bTable on main.platform_code = bTable.code
        left join dl_busi_notice_view noticeView on noticeView.id = main.id
        <where>
            main.del_flag = '0' and main.approval_status != 8
            <if test="entity.userNickName != null "> and uTable.nick_name like concat('%', #{entity.userNickName}, '%')</if>
            <if test="entity.userId != null "> and main.user_id = #{entity.userId}</if>
            <if test="entity.province != null "> and main.province =  #{entity.province}</if>
            <if test="entity.city != null "> and main.city =  #{entity.city}</if>
            <if test="entity.title != null  and entity.title != ''"> and main.title like concat('%', #{entity.title}, '%')</if>
            <if test="entity.platformCode != null  and entity.platformCode != ''"> and main.platform_code = #{entity.platformCode}</if>
            <if test="entity.isUrgent != null  and entity.isUrgent != ''"> and main.is_urgent =  #{entity.isUrgent}</if>
            <if test="entity.isPlatformFree != null  and entity.isPlatformFree != ''"> and main.is_platform_free =  #{entity.isPlatformFree}</if>

            <if test="entity.params.beginFeeDown != null and entity.params.beginFeeDown != ''">
                and main.fee_down  <![CDATA[>= ]]>  #{entity.params.beginFeeDown}
            </if>
            <if test=" entity.params.endFeeDown != null and entity.params.endFeeDown != ''">
                and main.fee_down <![CDATA[<= ]]>  #{entity.params.endFeeDown}
            </if>

            <if test="entity.params.beginFansDown != null and entity.params.beginFansDown != ''">
                and main.fans_down  <![CDATA[>= ]]>  #{entity.params.beginFansDown}
            </if>
            <if test=" entity.params.endFansDown != null and entity.params.endFansDown != ''">
                and main.fans_down <![CDATA[<= ]]>  #{entity.params.endFansDown}
            </if>
            <if test="entity.brand != null  and entity.brand != ''"> and main.brand like concat('%', #{entity.brand}, '%')</if>
            <if test="entity.pic != null  and entity.pic != ''"> and main.pic = #{entity.pic}</if>
            <if test="entity.collect != null  and entity.collect != ''"> and main.collect = #{entity.collect}</if>
            <if test="entity.bloggerTypes != null  and entity.bloggerTypes != ''"> and main.blogger_types = #{entity.bloggerTypes}</if>
            <if test="entity.isUseCoupon != null "> and main.is_use_coupon = #{entity.isUseCoupon}</if>
            <if test="entity.approvalStatus != null  and entity.approvalStatus != ''"> and main.approval_status = #{entity.approvalStatus}</if>
            <if test="entity.params.beginCreateTime != null and entity.params.beginCreateTime != '' and entity.params.endCreateTime != null and entity.params.endCreateTime != ''"> and main.create_time between #{entity.params.beginCreateTime} and #{entity.params.endCreateTime}</if>
        </where>
        order by FIELD(main.approval_status,0,1,9,2)  asc, main.create_time desc
    </select>
    <select id="queryListByUserId" resultType="com.ruoyi.busi.vo.BusiNoticeVo">
        SELECT
            main.*,
            dbns.status,
            dbns.is_super
        FROM
            dl_busi_notice main
                LEFT JOIN dl_busi_notice_sign dbns ON main.id = dbns.notice_id AND dbns.del_flag = 0
        WHERE
            dbns.user_id = #{userId}
order by dbns.create_time desc
    </select>
    <select id="queryAppListPage" resultType="com.ruoyi.busi.vo.BusiNoticeVo">
        SELECT
            dbn.*,
            dbnv.view_num AS viewNum,
            su.avatar,
            su.nick_name AS userNickName,
--         count(sign.id) as reportNum
        FROM
            dl_busi_notice dbn
                LEFT JOIN dl_busi_notice_view dbnv ON dbn.id = dbnv.id
                LEFT JOIN sys_user su ON dbn.user_id = su.user_id
--         left join dl_busi_notice_sign sign on sign.notice_id = dbn.id and sign.del_flag = 0
        <if test="entity.userId!=null and entity.userId!=''">
            left join dl_member_blacklist blacklist on blacklist.black_user_id = dbn.user_id and blacklist.del_flag = 0 and blacklist.user_id = #{entity.userId}
        </if>
        WHERE
            dbn.del_flag = 0 and dbn.end_date &gt;= CURDATE()
          AND (dbn.approval_status = '1')
        <if test="entity.userId!=null and entity.userId!=''">
            and blacklist.id is null
        </if>
        <if test="entity.gift!=null and entity.gift!=''">
            AND (( dbn.fee_down is null or  dbn.fee_down = 0 ) and dbn.fee_up = 0)
        </if>
        <if test="entity.cityName!=null and entity.cityName!=''">
            AND dbn.city =#{entity.cityName}
        </if>
        <if test="entity.platformCode!=null and entity.platformCode!=''">
            AND ( dbn.platform_code =#{entity.platformCode}  )
        </if>
        <if test="entity.isUrgent != null  and entity.isUrgent != ''"> and dbn.is_urgent =  #{entity.isUrgent}</if>
        <if test="entity.isPlatformFree != null  and entity.isPlatformFree != ''"> and dbn.is_platform_free =  #{entity.isPlatformFree}</if>
        <if test="entity.bloggerType!=null and entity.bloggerType!=''">
            AND ( dbn.blogger_types LIKE CONCAT('%',#{entity.bloggerType},'%')  )
        </if>
        <if test="entity.searchValue!=null and entity.searchValue!=''">
            AND ( dbn.title LIKE CONCAT('%',#{entity.searchValue},'%') OR
                  dbn.detail LIKE CONCAT('%',#{entity.searchValue},'%')  )
        </if>
        <if test="entity.rewardType=='money'">
            AND ( dbn.fee_down IS NOT NULL OR dbn.fee_up IS NOT NULL )
        </if>
        <if test="entity.rewardType=='gift'">
            AND ( dbn.have_gift = '1' )
        </if>
        <choose>
            <!-- 情况一：同时填写了下限和上限 -->
            <when test="entity.fansDown != null and entity.fansUp != null">
                AND (
                dbn.fans_down <![CDATA[ <= ]]> #{entity.fansUp}
                AND (dbn.fans_up IS NULL OR dbn.fans_up >= #{entity.fansDown})
                )
            </when>

            <!-- 情况二：只填写了下限 -->
            <when test="entity.fansDown != null">
                AND (dbn.fans_up IS NULL OR dbn.fans_up >= #{entity.fansDown})
            </when>

            <!-- 情况三：只填写了上限 -->
            <when test="entity.fansUp != null">
                AND dbn.fans_down <![CDATA[ <= ]]> #{entity.fansUp}
            </when>
        </choose>

        <choose>
            <!-- 情况一：同时填写了下限和上限 -->
            <when test="entity.feeDown != null and entity.feeUp != null">
                AND (
                dbn.fee_down <![CDATA[ <= ]]> #{entity.feeUp}
                AND (dbn.fee_up IS NULL OR dbn.fee_up >= #{entity.feeDown})
                )
            </when>

            <!-- 情况二：只填写了下限 -->
            <when test="entity.feeDown != null">
                AND (dbn.fee_up IS NULL OR dbn.fee_up >= #{entity.feeDown})
            </when>

            <!-- 情况三：只填写了上限 -->
            <when test="entity.feeUp != null">
                AND dbn.fee_down <![CDATA[ <= ]]> #{entity.feeUp}
            </when>
        </choose>

        group by dbn.id
        ORDER BY
         <choose>
             <when test="entity.sortBy=='new'">
                 -- 查最新的 --
                 dbn.create_time DESC
             </when>
             <when test="entity.sortBy=='money'">
                 -- 查高奖励 --
                 dbn.fee_up DESC
             </when>
             <otherwise>
                 -- 默认正序排列 --
                 dbn.create_time DESC
             </otherwise>
         </choose>

    </select>
    <select id="selectByIdVo" resultType="com.ruoyi.busi.vo.BusiNoticeVo">
        select  main.*,
               uTable.nick_name as userNickName,ifnull(noticeView.view_num,0) as viewNum,
               bTable.title as platformName
        from dl_busi_notice main
                 left join sys_user uTable on main.user_id = uTable.user_id
                 left join dl_base_category bTable on main.platform_code = bTable.code
                 left join dl_busi_notice_view noticeView on noticeView.id = main.id
        WHERE
            main.id = #{noticeId}
    </select>
    <select id="appFootprintList" resultType="com.ruoyi.busi.vo.BusiNoticeVo">
        SELECT
        dbn.*,
        dbnv.view_num AS viewNum,
        su.avatar,
        su.nick_name AS userNickName,
        count(sign.id) as reportNum
        FROM
        dl_busi_notice dbn
        LEFT JOIN dl_busi_notice_view dbnv ON dbn.id = dbnv.id
        inner join dl_member_footprint footprint on footprint.notice_id = dbn.id and footprint.user_id = #{entity.userId}
        LEFT JOIN sys_user su ON dbn.user_id = su.user_id
        left join dl_busi_notice_sign sign on sign.notice_id = dbn.id and sign.del_flag = 0
        WHERE
        dbn.del_flag = 0
        AND (dbn.approval_status = '1')
        and dbn.end_date &gt;= CURDATE()
        <if test="entity.gift!=null and entity.gift!=''">
            AND ( dbn.gift_detail IS NOT NULL AND dbn.gift_detail != ''  )
        </if>
        <if test="entity.platformCode!=null and entity.platformCode!=''">
            AND ( dbn.platform_code =#{entity.platformCode}  )
        </if>
        <if test="entity.isUrgent != null  and entity.isUrgent != ''"> and dbn.is_urgent =  #{entity.isUrgent}</if>
        <if test="entity.isPlatformFree != null  and entity.isPlatformFree != ''"> and dbn.is_platform_free =  #{entity.isPlatformFree}</if>
        <if test="entity.bloggerType!=null and entity.bloggerType!=''">
            AND ( dbn.blogger_types LIKE CONCAT('%',#{entity.bloggerType},'%')  )
        </if>
        <if test="entity.searchValue!=null and entity.searchValue!=''">
            AND ( dbn.title LIKE CONCAT('%',#{entity.searchValue},'%') OR
            dbn.detail LIKE CONCAT('%',#{entity.searchValue},'%')  )
        </if>
        <if test="entity.rewardType=='money'">
            AND ( dbn.fee_down IS NOT NULL OR dbn.fee_up IS NOT NULL )
        </if>
        <if test="entity.rewardType=='gift'">
            AND ( dbn.gift_detail IS NOT NULL AND dbn.gift_detail!='' )
        </if>
        <if test="entity.fansUp!=null">
            AND ( dbn.fans_up &lt;=#{entity.fansUp} )
        </if>
        <if test="entity.fansDown!=null">
            AND ( dbn.fans_up &gt;=#{entity.fansDown} )
        </if>
        <if test="entity.feeUp!=null">
            AND ( dbn.fee_up &lt;=#{entity.feeUp} )
        </if>
        <if test="entity.feeDown!=null">
            AND ( dbn.fee_down &gt;=#{entity.feeDown} )
        </if>
        group by dbn.id
        ORDER BY

        -- 默认正序排列 --
        footprint.create_time desc

        limit #{num}
    </select>
    <select id="subscribeList" resultType="com.ruoyi.busi.vo.BusiNoticeVo">
        SELECT
        dbn.*,
        dbnv.view_num AS viewNum,
        su.avatar,
        su.nick_name AS userNickName,
        count(sign.id) as reportNum
        FROM
        dl_busi_notice dbn
        LEFT JOIN dl_busi_notice_view dbnv ON dbn.id = dbnv.id
        LEFT JOIN sys_user su ON dbn.user_id = su.user_id
        left join dl_busi_notice_sign sign on sign.notice_id = dbn.id and sign.del_flag = 0
        WHERE
        dbn.del_flag = 0
        AND (dbn.approval_status = '1')
        and dbn.end_date &gt;= CURDATE()
        <if test="entity.platformCode!=null and entity.platformCode.size>0">
            and dbn.platform_code IN
            <foreach collection="entity.platformCode" item="it" open="(" close=")" separator=",">
          -- platform_code 包含it
              #{it}
        </foreach>
        </if>
        <if test="entity.bloggerTypeCode!=null and entity.bloggerTypeCode.size>0">
            and
            <foreach collection="entity.bloggerTypeCode" item="it" open="(" close=")" separator="or">
                 dbn.blogger_types like concat('%',#{it},'%')
            </foreach>
        </if>
        <if test="entity.settleTypeCode!=null and entity.settleTypeCode!=''">
            and dbn.is_platform_free = #{entity.settleTypeCode}
        </if>

        <if test="entity.keywordsList!=null and entity.keywordsList.size>0  ">
            and
            <foreach collection="entity.keywordsList" item="it" open="(" close=")" separator="or">
                title  like concat('%',#{it},'%') or detail  like concat('%',#{it},'%') or brand  like concat('%',#{it},'%')
            </foreach>
        </if>
        <if test="entity.fansUp!=null">
            AND ( dbn.fans_up &lt;=#{entity.fansUp} )
        </if>
        <if test="entity.fansDown!=null">
            AND ( dbn.fans_up &gt;=#{entity.fansDown} )
        </if>
        <if test="entity.feeUp!=null">
            AND ( dbn.fee_up &lt;=#{entity.feeUp} )
        </if>
        <if test="entity.feeDown!=null">
            AND ( dbn.fee_down &gt;=#{entity.feeDown} )
        </if>
        <if test="entity.isUrgent != null  and entity.isUrgent != ''"> and dbn.is_urgent =  #{entity.isUrgent}</if>
        <if test="entity.isPlatformFree != null  and entity.isPlatformFree != ''"> and dbn.is_platform_free =  #{entity.isPlatformFree}</if>
        group by dbn.id
        ORDER BY

            -- 默认正序排列 --
        dbn.create_time DESC


    </select>
    <select id="loveList" resultType="com.ruoyi.busi.vo.BusiNoticeVo">
        SELECT
            dbn.*,
            dbnv.view_num AS viewNum,
            su.avatar,
            su.nick_name AS userNickName,
            count(sign.id) as reportNum
        FROM
            dl_busi_notice dbn
                LEFT JOIN dl_busi_notice_view dbnv ON dbn.id = dbnv.id
                LEFT JOIN sys_user su ON dbn.user_id = su.user_id
                inner join dl_busi_user_love love on dbn.user_id = love.love_user_id and love.user_id = #{entity.userId}
                left join dl_busi_notice_sign sign on sign.notice_id = dbn.id and sign.del_flag = 0
        WHERE
            dbn.del_flag = 0
          AND (dbn.approval_status = '1')
          and dbn.end_date &gt;= CURDATE()
        group by dbn.id
        ORDER BY
            dbn.create_time DESC
    </select>
    <select id="myNoticeList" resultType="com.ruoyi.busi.vo.BusiNoticeVo">
        SELECT
            dbn.*,
            dbnv.view_num AS viewNum,
            su.avatar,
            su.nick_name AS userNickName,
            count(sign.id) as reportNum,
            mySign.create_time as signTime,
            card.account_name as cardName,
        CASE
        WHEN mySign.status = '01' AND dbn.end_date &gt;= CURDATE() THEN '审核中'
        WHEN mySign.status = '02' THEN '已通过'
        WHEN mySign.status = '01' AND dbn.end_date &lt; CURDATE() THEN '未合作'
        ELSE '已过期'
        END AS signStatus,
        mySign.is_evaluate as isEvaluate,
        mySign.id as signId
        FROM
            dl_busi_notice dbn
            LEFT JOIN dl_busi_notice_view dbnv ON dbn.id = dbnv.id
            LEFT JOIN sys_user su ON dbn.user_id = su.user_id
            LEFT join dl_busi_notice_sign sign on sign.notice_id = dbn.id and sign.del_flag = 0
            inner join dl_busi_notice_sign mySign on dbn.id = mySign.notice_id and dbn.del_flag = 0 and mySign.user_id = #{entity.userId}
            inner join dl_member_busi_card card on card.id = mySign.card_id and card.del_flag = 0
        WHERE
            dbn.del_flag = 0
          AND (dbn.approval_status = '1')
        <if test="entity.searchValue!=null and entity.searchValue!=''  ">
            AND (dbn.title LIKE CONCAT('%',#{entity.searchValue},'%') OR
            dbn.detail LIKE CONCAT('%',#{entity.searchValue},'%'))
        </if>
        <if test="entity.reportStatus!=null and entity.reportStatus!=''  ">
            <choose>
                <when test="entity.reportStatus=='审核中'">
                  and  mySign.status ='01'  and dbn.end_date &gt;= CURDATE()
                </when>
                <when test="entity.reportStatus=='已通过'">
                  and  mySign.status ='02'
                </when>
                <when test="entity.reportStatus=='未合作'">
                  and mySign.status ='01' and dbn.end_date &lt; CURDATE()
                </when>

            </choose>
        </if>
        group by dbn.id,mySign.id
        ORDER BY
            dbn.create_time DESC
    </select>
    <select id="myPublishNoticeList" resultType="com.ruoyi.busi.vo.BusiNoticeVo">
        SELECT
        dbn.*,
        dbnv.view_num AS viewNum,
        su.avatar,
        su.nick_name AS userNickName,
        count(sign.id) as reportNum,
        CASE
        WHEN  dbn.end_date  &lt; CURDATE() THEN '已关闭'
        WHEN dbn.approval_status = '0'  THEN '审核中'
        WHEN dbn.approval_status = '1' THEN '已通过'
        WHEN dbn.approval_status = '9'  THEN '审核不通过'
        WHEN dbn.approval_status = '2'  THEN '已关闭'
        ELSE '已过期'
        END AS reportStatusText
        FROM
        dl_busi_notice dbn
        LEFT JOIN dl_busi_notice_view dbnv ON dbn.id = dbnv.id
        LEFT JOIN sys_user su ON dbn.user_id = su.user_id
        LEFT join dl_busi_notice_sign sign on sign.notice_id = dbn.id and sign.del_flag = 0
        WHERE
        dbn.del_flag = 0  and  dbn.approval_status !=8  and dbn.user_id = #{entity.userId}
        <if test="entity.searchValue!=null and entity.searchValue!=''  ">
            AND (dbn.title LIKE CONCAT('%',#{entity.searchValue},'%') OR
            dbn.detail LIKE CONCAT('%',#{entity.searchValue},'%'))
        </if>
        <if test="entity.status!=null and entity.status!=''  ">
            <choose>
                <when test="entity.status=='进行中'">
                    and  dbn.approval_status !='2'  and dbn.end_date &gt;= CURDATE()
                </when>
                <when test="entity.status=='已关闭'">
                    and  (dbn.approval_status ='2' or dbn.end_date &lt; CURDATE())
                </when>
                <when test="entity.status=='审核通过'">
                    and  (dbn.approval_status ='1' or dbn.end_date &lt; CURDATE())
                </when>
            </choose>
        </if>
        group by dbn.id
        ORDER BY
        dbn.create_time DESC
    </select>

</mapper>
