<?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.canteen.CanteenScoreMapper">
    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="cn.com.countrygarden.bi.main.model.canteen.CanteenScore">
                                    <id column="id" property="id" />
                                                <result column="staff_id" property="staffId" />
                                                <result column="investigate_id" property="investigateId" />
                                                <result column="problem_id" property="problemId" />
                                                <result column="questionnaire_problem_option_id" property="questionnaireProblemOptionId" />
                                                <result column="score" property="score" />
                                                <result column="answer" property="answer" />
                                                <result column="flag" property="flag" />
                                                <result column="create_by" property="createBy" />
                                                <result column="create_time" property="createTime" />
                                                <result column="update_by" property="updateBy" />
                                                <result column="update_time" property="updateTime" />
                        </resultMap>
      <select id="bdpsndoccadati" resultMap="BaseResultMap">
            select cs.id as id from canteen_score cs
             where cs.staff_id=#{bdid} and cs.investigate_id=#{canteeninsdid}    
     </select>

     <update id="updatescore">
               update canteen_score cs set		
	          flag = 0 where cs.staff_id=#{bdid} and cs.investigate_id=#{canteeninsdid}  
     </update>

     <delete id="deletescore">
     delete FROM canteen_score WHERE flag=0;
     </delete>

     <select id="getcascore" resultType="cn.com.countrygarden.bi.main.dto.CanteenScorestatisticsDTO" 
     parameterType="cn.com.countrygarden.bi.main.dto.CanteenScorestatisticsDTO">
               
          select rq.id AS rqid,ci.id AS ciid,rq.name as rqname,ci.title as ciname,ci.area_id as areaid,ci.areaname as areaname,ci.arname as arname,ci.project_id as projectid,ci.projectname AS projectname,ci.prname as prname,sum(cs.score) as sumscore,convert(sum(cs.score)/(select count(distinct css.staff_id) from canteen_score css where rq.id=ci.questionnaire_id and css.investigate_id=ci.id ),decimal(10,2)) as avgscore,
          (select count(distinct css.staff_id) from canteen_score css
          where rq.id=ci.questionnaire_id and css.investigate_id=ci.id ) as sumbeing,rq.create_time as createTime from canteen_score cs,canteen_investigate ci,review_questionnaire rq
          where cs.investigate_id=ci.id and ci.questionnaire_id=rq.id and rq.investigationmanagement_id=#{investigationmanagementId}
           <if test="areaId!=null and areaId!=''">
                 and ci.area_id=#{areaId}
           </if>
           
           <if test="projectId!=null and projectId!=''">
                and ci.project_id =#{projectId}
           </if>
           
           <if test="rqname!=null and rqname!=''">
                and rq.name like concat('%',#{rqname},'%')
           </if>
           
           <if test="begintime!=null and begintime!='' and finishtime!=null and finishtime!=''">
                  and rq.create_time between #{begintime} and #{finishtime}
           </if>

           group by areaid,rqid,ciid,areaname,projectid,projectname,arname,prname
           
           order by avgscore desc

     </select>

     <select id="soceranwen" resultType="cn.com.countrygarden.bi.main.model.review.ReviewQuestionnaireProblem">
            select rqp.id as id,rqp.id as titleid,rqp.title_description as titleDescription,rqp.questionnaire_problem_classification_id as questionnaireProblemClassificationId,rqp.question_type_id as questionTypeId
            from review_questionnaire_problem rqp,canteen_investigate ci,review_questionnaire rq
            where rqp.questionnaire_id=rq.id and rq.id=ci.questionnaire_id and ci.id=#{ciid} and rqp.questionnaire_id=#{rqid}
     </select>

     <select id="socedaan" resultType="cn.com.countrygarden.bi.main.dto.CanteenScoresocedaanDTO">
           

            select cs.id as id,cs.depname as depname,cs.staff_id as staffId,cs.name as `name`,cs.problem_id as problemId,rqp.question_type_id as typeid,rqp.title_description as title,cs.questionnaire_problem_option_id as questionnaireproblemoptionId
            ,rqpo.name as rqponame,cs.score as score,cs.answer as answer,cs.create_time as createTime from biapp.canteen_score cs
           left join biapp.review_questionnaire_problem_option rqpo on rqpo.id=cs.questionnaire_problem_option_id 
             left join biapp.review_questionnaire_problem rqp on cs.problem_id=rqp.id            
           where cs.investigate_id=#{ciid} and rqp.questionnaire_id=#{rqid}
     </select>
     
     <select id="canteenareatu" resultType="cn.com.countrygarden.bi.main.dto.CanteenScorestatisticsDTO" parameterType="java.lang.String">
                   select ci.areaname as areaname,
                   sum(cs.score) as sumscore, convert(sum(cs.score)/(select count(distinct css.staff_id) from canteen_score css,review_questionnaire rq,canteen_investigate cii where rq.id=cii.questionnaire_id and css.investigate_id=cii.id ),decimal(10,2)) as avgscore
                   from canteen_investigate ci,canteen_score cs
                   where ci.id=cs.investigate_id 
                   
                    <if test="areaId!=null and areaId!=''">
                          and ci.area_id=#{areaId}
                    </if>
                   
                   group by areaname order by avgscore DESC 
     
     </select>
     
     <select id="canteenprojectu" resultType="cn.com.countrygarden.bi.main.dto.CanteenScorestatisticsDTO" parameterType="java.lang.String">
                   select ci.projectname as projectname,
                   sum(cs.score) as sumscore, convert(sum(cs.score)/(select count(distinct css.staff_id) from canteen_score css,review_questionnaire rq,canteen_investigate cii where rq.id=cii.questionnaire_id and css.investigate_id=cii.id ),decimal(10,2)) as avgscore
                   from canteen_investigate ci,canteen_score cs
                   where ci.id=cs.investigate_id 
                   
                   <if test="areaId!=null and areaId!=''">
                         and ci.area_id=#{areaId}
                   </if>
                   
                   group by projectname order by avgscore DESC 
     
     </select>
</mapper>