<?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.rongwei.trainingcommon.sys.dao.TrainingDemandSurveyBackupsDao">


    <select id="getQuestionnaireInformation" resultType="com.rongwei.training.vo.QuestionnaireRecordingVo">
        SELECT
        dsc.TEMPID as tempId,
        dsc.TEMPNAME as tempName,
        dsc.NUM,
        dsc.QUESTIONNAME as questionName,
        SUM(IF(dsua.ANSWER REGEXP '(^|-;-)迫切需要(-;|$)',1,0)) AS pqxy,
        SUM(IF(dsua.ANSWER REGEXP '(^|-;-)比较需要(-;|$)',1,0)) AS bjxy,
        SUM(IF(dsua.ANSWER REGEXP '(^|-;-)一般需要(-;|$)',1,0)) AS ybxy,
        SUM(IF(dsua.ANSWER REGEXP '(^|-;-)不需要(-;|$)',1,0)) AS bxy,
        count(dsua.ID) AS totalCount,
        tdsb.ID
        FROM demand_survey_content dsc
        LEFT JOIN training_demand_survey_backups tdsb ON dsc.SURVERID = tdsb.ID
        LEFT JOIN training_demand_survey tds ON tds.ID = tdsb.OLDSURVEYID
        LEFT JOIN demand_survey_user_answer dsua ON tdsb.ID = dsua.SURVERID AND dsua.SURVERCONTENTID = dsc.ID
        <where>
            FIND_IN_SET(dsc.QUESTIONTYPE, '1,2')
            AND dsc.`OPTIONS` LIKE '%一般需要%'
            AND dsc.`OPTIONS` LIKE '%不需要%'
            AND dsc.`OPTIONS` LIKE '%迫切需要%'
            AND dsc.`OPTIONS` LIKE '%比较需要%'
            -- AND dsua.ID IS NOT NULL
            and tdsb.ID= #{id}
        </where>
        GROUP BY
        dsc.QUESTIONID,
        dsc.QUESTIONNAME,
        dsc.TEMPNAME,
        dsc.TEMPID
        ORDER BY CASE dsc.TEMPNAME
        WHEN '培训内容' THEN 1
        WHEN '期望培训形式' THEN 2
        WHEN '期望授课人' THEN 3
        WHEN '影响培训开展得因素' THEN 4
        ELSE 5
        END,
        dsc.NUM asc
    </select>
</mapper>
