<?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.SysQuestionnaireDao">
    <select id="list" parameterType="com.api.model.butlerService.SearchQuestionnaire" resultType="com.api.vo.butlerService.VoQuestionnaire">
        select * from sys_questionnaire
        <where>
            <if test="title != null and title != ''">
                and title like concat('%',#{title},'%')
            </if>
            <if test="status == 1">
                and #{nowDate} <![CDATA[ < ]]> begin_date
            </if>
            <if test="status == 2">
                and #{nowDate} <![CDATA[ <= ]]> begin_date
                and #{nowDate} <![CDATA[ >= ]]> end_date
            </if>
            <if test="status == 3">
                and #{nowDate} <![CDATA[ > ]]> end_date
            </if>
            <if test="answerType != null">
                and answer_type = #{answerType}
            </if>
            and is_delete = 1
        </where>
    </select>

    <insert id="insertQuestionnaire" parameterType="com.api.model.butlerService.SysQuestionnaire">
        insert into sys_questionnaire(
            title,
            description,
            answer_type,
            begin_date,
            end_date,
            is_delete,
            create_id,
            create_date,
            is_release,
            answer_num
        ) values (
            #{title},
            #{description},
            #{answerType},
            #{beginDate},
            #{endDate},
            #{isDelete},
            #{createId},
            #{createDate},
            #{isRelease},
            #{answerNum}
        )
        <selectKey keyProperty="id" resultType="java.lang.Integer">
            select LAST_INSERT_ID() AS id
        </selectKey>
    </insert>

    <insert id="insertQuestionnaireTopic" parameterType="com.api.model.butlerService.SysQuestionnaireTopic">
        insert into sys_questionnaire_topic(
            type,
            topic,
            questionnaire_id,
            create_id,
            create_date
        ) values (
            #{type},
            #{topic},
            #{questionnaireId},
            #{createId},
            #{createDate}
        )
        <selectKey keyProperty="id" resultType="java.lang.Integer">
            select LAST_INSERT_ID() AS id
        </selectKey>
    </insert>

    <insert id="insertQuestionnaireChoice" parameterType="com.api.model.butlerService.SysQuestionnaireChoice">
        insert into sys_questionnaire_multiple_choice(
            options,
            answer,
            questionnaire_id,
            questionnaire_topic_id,
            create_id,
            create_date
        ) values (
            #{options},
            #{answer},
            #{questionnaireId},
            #{questionnaireTopicId},
            #{createId},
            #{createDate}
        )
    </insert>

    <select id="findById" parameterType="java.lang.Integer" resultType="com.api.vo.butlerService.VoFindByIdQuestionnaire">
        select * from sys_questionnaire where id = #{id} and is_delete = 1
    </select>

    <select id="findTopicByQuestionnaireId" parameterType="java.lang.Integer" resultType="com.api.vo.butlerService.VoFindByIdTopic">
        select * from sys_questionnaire_topic where questionnaire_id = #{id}
    </select>

    <select id="findChoiceByTopicId" parameterType="java.lang.Integer" resultType="com.api.vo.butlerService.VoFindByIdChoice">
        select * from sys_questionnaire_multiple_choice where questionnaire_topic_id = #{id}
    </select>

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

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

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

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

    <insert id="insertChoiceAnswer" parameterType="com.api.model.butlerService.SysQuestionChoiceAnswer">
        insert into sys_questionnaire_single_multiple_choice_answer(
            questionnaire_id,
            questionnaire_topic_id,
            questionnaire_multiple_choice_id,
            create_id,
            create_date
        ) values (
            #{questionnaireId},
            #{questionnaireTopicId},
            #{questionnaireMultipleChoiceId},
            #{createId},
            #{createDate}
        )
    </insert>

    <insert id="insertJudgmentAnswer" parameterType="com.api.model.butlerService.SysQuestionJudgmentAnswer">
        insert into sys_questionnaire_judgment_answer(
            questionnaire_id,
            questionnaire_topic_id,
            answer,
            create_id,
            create_date
        ) values (
            #{questionnaireId},
            #{questionnaireTopicId},
            #{answer},
            #{createId},
            #{createDate}
        )
    </insert>

    <insert id="insertShortAnswer" parameterType="com.api.model.butlerService.SysQuestionnaireShortAnswer">
        insert into sys_questionnaire_short_answer(
            questionnaire_id,
            questionnaire_topic_id,
            answer,
            create_id,
            create_date
        ) values (
            #{questionnaireId},
            #{questionnaireTopicId},
            #{answer},
            #{createId},
            #{createDate}
        )
    </insert>

    <select id="findTopicByTopicId" parameterType="java.lang.Integer" resultType="com.api.model.butlerService.SysQuestionnaireTopic">
        select * from sys_questionnaire_topic where id =#{id}
    </select>
    <update id="accumulationAnswerNum" parameterType="java.lang.Integer" >
        update sys_questionnaire set answer_num = answer_num + 1 where id =#{id}
    </update>

    <select id="findShortAnswerByCreateId" parameterType="java.lang.Integer" resultType="com.api.model.butlerService.SysQuestionnaireShortAnswer">
        select * from sys_questionnaire_short_answer where create_id = #{id}
    </select>

    <select id="findReportById" parameterType="java.lang.Integer" resultType="com.api.vo.butlerService.VoReportQuestionnaire">
        select * from sys_questionnaire where id =#{id}
    </select>

    <select id="findReportTopicByQId" parameterType="java.lang.Integer" resultType="com.api.vo.butlerService.VoReportQuestionnaireTopic">
        select * from sys_questionnaire_topic where questionnaire_id = #{id}
    </select>

    <select id="findReportChoiceByTopicId" parameterType="java.lang.Integer" resultType="com.api.vo.butlerService.VoReportQuestionnaireChoice">
        select * from sys_questionnaire_multiple_choice where questionnaire_topic_id = #{id}
    </select>

    <select id="countChoice" parameterType="java.lang.Integer" resultType="java.lang.Integer">
        select count(distinct create_id) from sys_questionnaire_single_multiple_choice_answer
        where questionnaire_multiple_choice_id = #{id}
    </select>

    <select id="countTopic" parameterType="java.lang.Integer" resultType="java.lang.Integer">
        select count(distinct create_id) from sys_questionnaire_single_multiple_choice_answer
        where questionnaire_topic_id = #{id}
    </select>

    <select id="countJudgmentTrueByTId" parameterType="java.lang.Integer" resultType="java.lang.Integer">
        select count(*) from sys_questionnaire_judgment_answer where answer = 1 and questionnaire_topic_id = #{id}
    </select>

    <select id="countJudgmentFalseByTId" parameterType="java.lang.Integer" resultType="java.lang.Integer">
        select count(*) from sys_questionnaire_judgment_answer where answer = 0 and questionnaire_topic_id = #{id}
    </select>

    <select id="countShort" parameterType="java.lang.Integer" resultType="java.lang.Integer">
        select count(*) from sys_questionnaire_short_answer where questionnaire_topic_id = #{id}
    </select>

    <select id="listShortAnswer" parameterType="com.api.model.butlerService.SearchShortAnswer" resultType="com.api.vo.butlerService.VoReportQuestionnaireShort">
        select sqsa.id,ur.name,sqsa.answer from sys_questionnaire_short_answer as sqsa,user_resident as ur
        where sqsa.create_id = ur.id and questionnaire_topic_id = #{id}
    </select>

</mapper>