<?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.api.manage.dao.butlerService.UserAdviceDao">
    <select id="list" parameterType="com.api.model.butlerService.SearchUserAdvice" resultType="com.api.vo.butlerService.VoUserAdvice">
        select sa.id,sa.content,sa.type,sa.status,ur.name as release_name,sa.create_date as release_date,sa.score
        from sys_advice as sa,user_resident as ur
        <where>
            sa.create_id = ur.id
            and sa.is_delete = 1
            <if test="status != null">
                and sa.status = #{status}
            </if>
            <if test="releaseDateStart != null">
                and sa.create_date <![CDATA[ >= ]]> #{releaseDateStart}
            </if>
            <if test="releaseDateEnd != null">
                and sa.create_date <![CDATA[ <= ]]> #{releaseDateEnd}
            </if>
            <if test="score == 1">
                and sa.score between 1 and 5
            </if>
            <if test="score == 2">
                and sa.score between 5 and 7
            </if>
            <if test="score == 3">
                and sa.score between 7 and 11
            </if>
            <if test="type != null">
                and sa.type = #{type}
            </if>
            <if test="type == null">
                and (sa.type = 1 or sa.type = 2)
            </if>
            <if test="releaseName != null and releaseName != ''">
                and ur.name like concat('%',#{releaseName},'%')
            </if>
        </where>
    </select>

    <select id="countDetailByAdviceId" parameterType="java.lang.Integer" resultType="java.lang.Integer">
        select count(*) from sys_advice_detail where advice_id = #{id} and is_delete = 1
    </select>

    <insert id="insertDetail" parameterType="com.api.model.butlerService.SysAdviceDetail">
        insert into sys_advice_detail(
            advice_id,
            content,
            is_delete,
            create_id,
            create_date,
            create_user_type,
            parent_id
        ) values (
            #{adviceId},
            #{content},
            #{isDelete},
            #{createId},
            #{createDate},
            #{createUserType},
            #{parentId}
        )
    </insert>

    <insert id="insertAdvice" parameterType="com.api.model.butlerService.SysAdvice">
        insert into sys_advice(
            type,
            status,
            content,
            hits,
            create_id,
            create_date,
            create_user_type,
            is_delete,
            user_delete
        ) values (
            #{type},
            #{status},
            #{content},
            #{hits},
            #{createId},
            #{createDate},
            #{createUserType},
            #{isDelete},
            #{userDelete}
        )
        <selectKey keyProperty="id" resultType="java.lang.Integer">
            select LAST_INSERT_ID() AS id
        </selectKey>
    </insert>

    <delete id="delete" parameterType="java.lang.Integer">
        delete from sys_advice where id =#{id}
    </delete>

    <delete id="deleteDetail" parameterType="java.lang.Integer">
        delete from sys_advice_detail where advice_id = #{id}
    </delete>

    <update id="falseDelete" parameterType="java.lang.Integer">
        update sys_advice
        set is_delete = 0
        where id = #{id}
    </update>

    <select id="findById" parameterType="java.lang.Integer" resultType="com.api.vo.butlerService.VoFindByIdAdvice">
        select * from sys_advice where id =#{id}
    </select>

    <select id="findByAdviceIdDetail" parameterType="java.lang.Integer" resultType="com.api.vo.butlerService.VoUserAdviceDetail">
        select * from sys_advice_detail where advice_id = #{id} and is_delete = 1
        order by create_date DESC
    </select>

    <select id="countAdviceNew" resultType="java.lang.Integer">
        select count(*) from sys_advice
        where to_days(create_date) = to_days(now())
        and type = 2
    </select>

    <select id="countConsultNew" resultType="java.lang.Integer">
        select count(*) from sys_advice
        where to_days(create_date) = to_days(now())
        and type = 1
    </select>

    <update id="updateAdviceStatus" parameterType="com.api.model.butlerService.SysAdvice">
        update sys_advice set status = #{status} where id = #{id}
    </update>
</mapper>