<?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.pluss.platform.mapper.SuggestMapper">

	   	<insert id="saveSuggest" parameterType="cn.pluss.platform.entity.Suggest">
        	insert into tb_pluss_suggest(userId,logo,createDt,content,picUrls,status,phone)VALUES(#{userId},#{logo},now(),#{content},#{picUrls},'0',#{phone})
    	</insert>

	   	<insert id="saveSuggestBatch">
        	insert into tb_pluss_suggest(userId,logo,createDt,content,picUrls,status,phone) VALUES
    			<foreach collection="list" item="item" index="index" separator=",">
			(#{userId},#{logo},#{createDt},#{content},#{picUrls},#{status},#{phone}		)
				</foreach>
    	</insert>

		<select id="querySuggest" parameterType="cn.pluss.platform.entity.Suggest" resultType="cn.pluss.platform.entity.Suggest">
			SELECT * from tb_pluss_suggest 
				<where> 
					<if test="id!= null"> 
					 	and id = #{id}
					 </if> 
					<if test="userId!= null"> 
					 	and userId = #{userId}
					 </if> 
					<if test="logo!= null"> 
					 	and logo = #{logo}
					 </if> 
					<if test="createDt!= null"> 
					 	and createDt = #{createDt}
					 </if> 
					<if test="content!= null"> 
					 	and content = #{content}
					 </if> 
					<if test="picUrls!= null"> 
					 	and picUrls = #{picUrls}
					 </if> 
					 <if test="status!= null"> 
                        and status = #{status}
                     </if>
                     <if test="phone!= null"> 
                        and phone = #{phone}
                     </if> 
                     
				 </where> 
		</select>

		<select id="querySuggestList" parameterType="cn.pluss.platform.entity.Suggest" resultType="cn.pluss.platform.entity.Suggest">
			SELECT * from tb_pluss_suggest 
				<where> 
					<if test="id!= null"> 
					 and 	id = #{id}
					 </if> 
					<if test="userId!= null"> 
					 and 	userId = #{userId}
					 </if> 
					<if test="logo!= null"> 
					 and 	logo = #{logo}
					 </if> 
					<if test="createDt!= null"> 
					 and 	createDt = #{createDt}
					 </if>
					<if test="startTime != null">
					and  createDt  > #{startTime}
					</if>
					<if test="endTime != null">
						and  createDt  <![CDATA[ <= ]]> #{endTime}
					</if>
					<if test="content!= null"> 
					 and 	content = #{content}
					 </if> 
					<if test="picUrls!= null"> 
					 and 	picUrls = #{picUrls}
					 </if> 
					 <if test="status!= null"> 
                        and status = #{status}
                     </if> 
                     <if test="phone!= null"> 
                        and phone like CONCAT('%',#{phone},'%')
                     </if>
				 </where> 
		</select>

		<update id="updateSuggest" parameterType="cn.pluss.platform.entity.Suggest" >
			update tb_pluss_suggest 
				  <set> 
					<if test="userId!= null"> 
					 	userId = #{userId},
					 </if> 
					<if test="logo!= null"> 
					 	logo = #{logo},
					 </if> 
					<if test="createDt!= null"> 
					 	createDt = #{createDt},
					 </if> 
					<if test="content!= null"> 
					 	content = #{content},
					 </if> 
					<if test="picUrls!= null"> 
					 	picUrls = #{picUrls},
					 </if> 
					 <if test="status!= null"> 
                        status = #{status},
                     </if> 
                     <if test="phone!= null"> 
                        phone = #{phone}
                     </if>
				</set>
			where id=#{id} 
		</update>

		<delete id="deleteSuggest" parameterType="cn.pluss.platform.entity.Suggest" >
			DELETE FROM tb_pluss_suggest where id=#{id}
		</delete>

		<select id="querySuggestPage" parameterType="java.util.Map" resultType="cn.pluss.platform.entity.Suggest">
			SELECT *, tpui.phone userPhone from tb_pluss_suggest tps LEFT JOIN tb_pluss_user_info tpui ON tps.userId = tpui.id
				<where> 
					<if test="id!= null"> 
					and tps.id = #{id}
					 </if> 
					<if test="userId!= null"> 
					and tps.userId = #{userId}
					 </if> 
					<if test="logo!= null"> 
					and tps.logo = #{logo}
					 </if> 
					<if test="createDt!= null"> 
					and tps.createDt = #{createDt}
					 </if> 
					<if test="content!= null"> 
					and tps.content like CONCAT("%",#{content},"%")
					 </if> 
					<if test="picUrls!= null"> 
					and tps.picUrls = #{picUrls}
					 </if> 
					 <if test="status!= null"> 
                        and tps.status = #{status}
                     </if> 
                     <if test="phone!= null"> 
                        and tps.phone = #{phone}
                     </if>
				 </where> 
			order by tps.id desc  limit #{pageSize}  offset  #{offset}
		</select>

		<select id="querySuggestPageCount" parameterType="java.util.Map" resultType="java.lang.Integer">
			SELECT count(*) from tb_pluss_suggest 
					<where> 
					<if test="id!= null"> 
					 and id = #{id}
					 </if> 
					<if test="userId!= null"> 
					 and userId = #{userId}
					 </if> 
					<if test="logo!= null"> 
					 and logo = #{logo}
					 </if> 
					<if test="createDt!= null"> 
					 and createDt = #{createDt}
					 </if> 
					<if test="content!= null"> 
					 and content like CONCAT("%",#{content},"%") 
					 </if> 
					<if test="picUrls!= null"> 
					 and picUrls = #{picUrls}
					 </if> 
					 <if test="status!= null"> 
                        and status = #{status}
                     </if> 
                     <if test="phone!= null"> 
                        and phone = #{phone}
                     </if>
				</where>
		</select>

</mapper>
