<?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="cn.com.countrygarden.bi.main.dao.wcx.WcxSuggestMapper">
    <select id="findSuggestByTime" parameterType="cn.com.countrygarden.bi.main.dto.wcx.WcxSuggestDto"
            resultType="cn.com.countrygarden.bi.main.dto.wcx.WcxQuerySuggestDto">
        select s.create_date as createDate,
        s.suggest_title as suggestTitle,
        s.reply_number as replyNumber,
        s.suggest_content as suggestContent,
        s.suggest_id as suggestId,
        s.close_status as closeStatus,
        u.open_id as openId,
        u.head_pic_url as headPicUrl,
        u.employee_name  as  employeeName,
        f.file_url as fileUrl
        from wcx_suggest s
         left join wcx_user u on s.user_id = u.user_id
         left join wcx_file f on s.suggest_id  = f.suggest_id where  s.mod_status=0
        <if test="state != null and state != ''">
            and  s.state = #{state}
        </if>
        <if test="closeStatus != null and closeStatus != ''">
            and  s.close_status = #{closeStatus}
        </if>
        <if test="suggestTitle != null and suggestTitle != '' " >
            and s.suggest_title like concat('%',#{suggestTitle},'%')
        </if>
        order by s.create_date desc
        limit #{pageOffset}, #{pageSize}
    </select>

    <select id="findSuggestByTimeCount" parameterType="cn.com.countrygarden.bi.main.dto.wcx.WcxSuggestDto" resultType="Integer">
        select count(1)
        from wcx_suggest s
        left join wcx_user u on s.user_id = u.user_id
        left join wcx_file f on s.suggest_id  = f.suggest_id where  s.mod_status=0
        <if test="state != null and state != ''">
            and  s.state = #{state}
        </if>
        <if test="closeStatus != null and closeStatus != ''">
            and  s.close_status = #{closeStatus}
        </if>
        <if test="suggestTitle != null and suggestTitle != '' " >
            and s.suggest_title like concat('%',#{suggestTitle},'%')
        </if>
    </select>

    <select id="findSuggestByReply" parameterType="cn.com.countrygarden.bi.main.dto.wcx.WcxSuggestDto"
            resultType="cn.com.countrygarden.bi.main.dto.wcx.WcxQuerySuggestDto">
        select s.create_date as createDate,
        s.suggest_title as suggestTitle,
        s.reply_number as replyNumber,
        s.suggest_content as suggestContent,
        s.suggest_id as suggestId,
        s.close_status as closeStatus,
        u.open_id as openId,
        u.head_pic_url as headPicUrl,
        u.employee_name  as  employeeName,
        f.file_url as fileUrl
        from wcx_suggest s
        left join wcx_user u on s.user_id = u.user_id
        left join wcx_file f on s.suggest_id  = f.suggest_id where s.mod_status=0
        <if test="state != null and state != ''">
            and  s.state = #{state}
        </if>
        <if test="closeStatus != null and closeStatus != ''">
            and  s.close_status = #{closeStatus}
        </if>
        <if test="suggestTitle != null and suggestTitle != '' " >
            and s.suggest_title like concat('%',#{suggestTitle},'%')
        </if>
        order by  s.reply_number desc
        limit #{pageOffset}, #{pageSize}
    </select>

    <select id="findSuggestByReplyCount" parameterType="cn.com.countrygarden.bi.main.dto.wcx.WcxSuggestDto"  resultType="Integer">
        select count(1)
        from wcx_suggest s
        left join wcx_user u on s.user_id = u.user_id
        left join wcx_file f on s.suggest_id  = f.suggest_id where  s.mod_status=0
        <if test="state != null and state != ''">
            and  s.state = #{state}
        </if>
        <if test="closeStatus != null and closeStatus != ''">
            and  s.close_status = #{closeStatus}
        </if>
        <if test="suggestTitle != null and suggestTitle != '' " >
            and s.suggest_title like concat('%',#{suggestTitle},'%')
        </if>
    </select>

    <select id="findSuggestReplyPageCount"  resultType="Integer">
        select count(1)
        from wcx_reply r
        left join wcx_user u on r.user_id = u.user_id
        left join wcx_file f on r.reply_id  = f.reply_id where  r.mod_status=0 and r.suggest_id = #{suggestId}
        <if test="replyContent != null and replyContent != '' " >
            and r.reply_content like concat('%',#{replyContent},'%')
        </if>

    </select>

    <select id="findSuggestReply" resultType="cn.com.countrygarden.bi.main.dto.wcx.WcxQueryReplyDto" >
        select r.reply_content as replyContent,
        r.create_date as createDate,
        r.adopt_status as adoptStatus,
        r.reply_id  as replyId,
        u.head_pic_url as headPicUrl,
        u.employee_name  as  employeeName,
        f.file_url as fileUrl
        from wcx_reply r
        left join wcx_user u on r.user_id = u.user_id
        left join wcx_file f on r.reply_id  = f.reply_id where  r.mod_status=0 and r.suggest_id = #{suggestId}
        <if test="replyContent != null and replyContent != '' " >
            and r.reply_content like concat('%',#{replyContent},'%')
        </if>
        order by  r.adopt_status desc
        limit #{pageOffset}, #{pageSize}
    </select>

    <update id="deleteMySuggest" parameterType="cn.com.countrygarden.bi.main.dto.wcx.WcxSuggestDto">
             UPDATE wcx_suggest SET mod_status = 1 , mod_by = #{modBy} ,mod_time = #{modTime} WHERE suggest_id =#{suggestId}
    </update>

    <update id="deleteMySuggestReply" parameterType="cn.com.countrygarden.bi.main.dto.wcx.WcxSuggestDto">
         UPDATE wcx_reply SET mod_status = 1  , mod_by = #{modBy} ,mod_time = #{modTime}  WHERE suggest_id =#{suggestId}
    </update>


    <select id="findSuggestByTimeExport" parameterType="cn.com.countrygarden.bi.main.dto.wcx.WcxSuggestDto"
            resultType="cn.com.countrygarden.bi.main.dto.wcx.WcxQuerySuggestDto">
        select s.create_date as createDate,
        s.suggest_title as suggestTitle,
        s.reply_number as replyNumber,
        s.suggest_content as suggestContent,
        s.suggest_id as suggestId,
        s.close_status as closeStatus,
        u.open_id as openId,
        u.head_pic_url as headPicUrl,
        u.employee_name  as  employeeName,
        f.file_url as fileUrl
        from wcx_suggest s
        left join wcx_user u on s.user_id = u.user_id
        left join wcx_file f on s.suggest_id  = f.suggest_id where  s.mod_status=0
        <if test="state != null and state != ''">
            and  s.state = #{state}
        </if>
        <if test="closeStatus != null and closeStatus != ''">
            and  s.close_status = #{closeStatus}
        </if>
        <if test="suggestTitle != null and suggestTitle != '' " >
            and s.suggest_title like concat('%',#{suggestTitle},'%')
        </if>
        order by s.create_date desc
    </select>

    <select id="findSuggestByReplyExport" parameterType="cn.com.countrygarden.bi.main.dto.wcx.WcxSuggestDto"
            resultType="cn.com.countrygarden.bi.main.dto.wcx.WcxQuerySuggestDto">
        select s.create_date as createDate,
        s.suggest_title as suggestTitle,
        s.reply_number as replyNumber,
        s.suggest_content as suggestContent,
        s.suggest_id as suggestId,
        s.close_status as closeStatus,
        u.open_id as openId,
        u.head_pic_url as headPicUrl,
        u.employee_name  as  employeeName,
        f.file_url as fileUrl
        from wcx_suggest s
        left join wcx_user u on s.user_id = u.user_id
        left join wcx_file f on s.suggest_id  = f.suggest_id where s.mod_status=0
        <if test="state != null and state != ''">
            and  s.state = #{state}
        </if>
        <if test="closeStatus != null and closeStatus != ''">
            and  s.close_status = #{closeStatus}
        </if>
        <if test="suggestTitle != null and suggestTitle != '' " >
            and s.suggest_title like concat('%',#{suggestTitle},'%')
        </if>
        order by  s.reply_number desc
    </select>
    <!--根据提议id批量查询回复  -->
    <select id="findSuggestReplyById" resultType="cn.com.countrygarden.bi.main.dto.wcx.WcxSuggestReplyDto">
    	select 
    	r.suggest_id as suggestId,
	    r.reply_content as replyContent,
        r.create_date as createDate,
        r.adopt_status as adoptStatus,
        u.employee_name as  replyEmployeeName,
        f.file_url as fileUrl
        from wcx_reply r
        left join wcx_user u on r.user_id = u.user_id
        left join wcx_file f on r.reply_id  = f.reply_id 
        where  r.mod_status=0
          <if test="suggestIds != null and suggestIds.size() > 0">
              and r.suggest_id in
              <foreach collection="suggestIds" item="suggestId" open="(" close=")" separator="," >
                  #{suggestId}
              </foreach>
          </if>
    </select>
</mapper>