<?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="org.jeecg.modules.stm.rate.mapper.RateTaskMapper">

    <select id="queryDomainScoreUser" resultType="org.jeecg.modules.stm.rate.vo.RateTaskOptVo">

        SELECT s.ratedomain,s.sondomain,s.keyitem,s.keyitem_score AS keyitemscore,
        o.keyitem_id AS keyitemid,o.inputscore,o.inputscoreof,o.task_id as taskid,o.inputuser,
        s.keyindex
        FROM rate_opt o,
        (
        SELECT i.item_text AS ratedomain, d.sondomain, k.keyitem,k.keyitem_score,k.id AS keyitemid,k.keyindex
        FROM rate_keyitem k,rate_domain d,(
        SELECT c.item_value,c.item_text
        FROM sys_dict m,sys_dict_item c
        WHERE m.dict_code='rate_domain' AND m.id=c.dict_id) i
        WHERE
        k.rate_domain_id=d.id AND i.item_value=d.ratedomain) s
        WHERE s.keyitemid=o.keyitem_id
        AND o.task_id=#{query.taskid} AND (o.inputuser=#{query.inputuser} OR o.approveuser =#{query.inputuser})
        ORDER BY s.sondomain,s.keyindex,o.val_index
    </select>

    <select id="queryDomainResScoreUser" resultType="org.jeecg.modules.stm.rate.vo.RateTaskOptVo">

        SELECT s.ratedomain,s.sondomain,s.keyitem,s.keyitem_score AS keyitemscore,
        o.keyitem_id AS keyitemid,o.score,o.scoreof,o.task_id as taskid,o.inputuser,o.rateremark
        FROM rate_opt o,
        (
        SELECT d.ratedomain as ratedomainid,d.valindex,i.item_text AS ratedomain, d.sondomain, k.keyitem,k.keyitem_score,k.id AS keyitemid
        FROM rate_keyitem k,rate_domain d,(
        SELECT c.item_value,c.item_text
        FROM sys_dict m,sys_dict_item c
        WHERE m.dict_code='rate_domain' AND m.id=c.dict_id) i
        WHERE
        k.rate_domain_id=d.id AND i.item_value=d.ratedomain) s
        WHERE s.keyitemid=o.keyitem_id
        AND o.task_id=#{query.taskid}
        <if test="query.inputuser!=null">
            AND (o.inputuser=#{query.inputuser} OR o.approveuser =#{query.inputuser})
        </if>

        ORDER BY s.ratedomainid,s.valindex,o.val_index
    </select>

    <select id="queryDomainResTabScoreUser" resultType="org.jeecg.modules.stm.rate.vo.RateTaskOptVo">

        SELECT s.ratedomain,s.sondomain,s.keyitem,s.keyitem_score AS keyitemscore,
        o.keyitem_id AS keyitemid,o.inputscore,o.task_id as taskid,o.inputuser,o.inputscoreof,o.score,o.scoreof,o.rateremark
        FROM rate_opt o,
        (
        SELECT d.ratedomain as ratedomainid,d.valindex,i.item_text AS ratedomain, d.sondomain, k.keyitem,k.keyitem_score,k.id AS keyitemid
        FROM rate_keyitem k,rate_domain d,(
        SELECT c.item_value,c.item_text
        FROM sys_dict m,sys_dict_item c
        WHERE m.dict_code='rate_domain' AND m.id=c.dict_id) i
        WHERE
        k.rate_domain_id=d.id AND i.item_value=d.ratedomain) s
        WHERE s.keyitemid=o.keyitem_id
        AND o.task_id=#{query.taskid}
        <if test="query.inputuser!=null">
            AND (o.inputuser=#{query.inputuser} OR o.approveuser =#{query.inputuser})
        </if>

        ORDER BY s.ratedomainid,s.valindex,o.val_index
    </select>


    <select id="queryDomainStaiList" resultType="org.jeecg.modules.stm.rate.vo.RateStaiVo">

        SELECT m.ratedomain_id,m.ratedomain AS name,m.ratescore AS value,keyt.stdvalue,keyt.stvalue,keyt.type
        FROM rate_domainscore m,(
        SELECT domainid, SUM(score) AS value, SUM(keyitem_score) AS stdvalue, SUM(keyitem_score)- SUM(score) AS stvalue,'领域得分' AS TYPE
        FROM (
        SELECT s.ratedomain, s.domainid,
         o.score,s.keyitem_score
        FROM rate_opt o,
         (
        SELECT i.item_text AS ratedomain,d.ratedomain AS domainid, d.sondomain, k.keyitem,k.keyitem_score,k.id AS keyitemid
        FROM rate_keyitem k,rate_domain d,(
        SELECT c.item_value,c.item_text
        FROM sys_dict m,sys_dict_item c
        WHERE m.dict_code='rate_domain' AND m.id=c.dict_id) i
        WHERE
         k.rate_domain_id=d.id AND i.item_value=d.ratedomain) s
        WHERE s.keyitemid=o.keyitem_id AND o.task_id=#{query.taskid}) tab
        GROUP BY ratedomain) keyt
        WHERE m.rate_task_id=#{query.taskid} AND m.ratedomain_id=keyt.domainid
        ORDER BY ratedomain_id desc

        <!--
        SELECT ratedomain as name, SUM(score) AS value,SUM(keyitem_score) As stdvalue,SUM(keyitem_score)-SUM(score) AS stvalue,'领域得分' as type
        FROM (
        SELECT s.ratedomain, s.domainid,
         o.score,s.keyitem_score
        FROM rate_opt o,
         (
        SELECT i.item_text AS ratedomain,d.ratedomain AS domainid, d.sondomain, k.keyitem,k.keyitem_score,k.id AS keyitemid
        FROM rate_keyitem k,rate_domain d,(
        SELECT c.item_value,c.item_text
        FROM sys_dict m,sys_dict_item c
        WHERE m.dict_code='rate_domain' AND m.id=c.dict_id) i
        WHERE
         k.rate_domain_id=d.id AND i.item_value=d.ratedomain) s
        WHERE s.keyitemid=o.keyitem_id AND o.task_id=#{query.taskid}) tab
        GROUP BY ratedomain
        ORDER BY domainid
        -->

    </select>

    <select id="querykeyitemStaiList" resultType="org.jeecg.modules.stm.rate.vo.RateStaiVo">

        SELECT ratedomain as domain,sondomain ,keyitem AS name, SUM(score) AS value
        FROM (
        SELECT s.ratedomain,s.keyitem, s.valindex,s.keyindex,
        o.score,s.sondomain,s.domainid
        FROM rate_opt o,
        (
        SELECT i.item_text AS ratedomain,d.ratedomain AS domainid, d.sondomain, k.keyitem,d.valindex,k.keyindex,k.keyitem_score,k.id AS keyitemid
        FROM rate_keyitem k,rate_domain d,(
        SELECT c.item_value,c.item_text
        FROM sys_dict m,sys_dict_item c
        WHERE m.dict_code='rate_domain' AND m.id=c.dict_id) i
        WHERE
        k.rate_domain_id=d.id AND i.item_value=d.ratedomain) s
        WHERE s.keyitemid=o.keyitem_id AND o.task_id=#{query.taskid} ) tab
        GROUP BY domainid,sondomain,keyitem
        ORDER BY domainid,sondomain,keyindex


<!--
        SELECT keyitem as name, SUM(score) AS value
        FROM (
        SELECT  s.keyitem, s.valindex,s.keyindex,
         o.score
        FROM rate_opt o,
         (
        SELECT i.item_text AS ratedomain,d.ratedomain AS domainid, d.sondomain, k.keyitem,d.valindex,k.keyindex,k.keyitem_score,k.id AS keyitemid
        FROM rate_keyitem k,rate_domain d,(
        SELECT c.item_value,c.item_text
        FROM sys_dict m,sys_dict_item c
        WHERE m.dict_code='rate_domain' AND m.id=c.dict_id) i
        WHERE
         k.rate_domain_id=d.id AND i.item_value=d.ratedomain) s
        WHERE s.keyitemid=o.keyitem_id AND o.task_id=#{query.taskid}) tab
        GROUP BY keyitem
        ORDER BY valindex,keyindex
        -->

    </select>


    <select id="queryDomainScoreList" resultType="org.jeecg.modules.stm.rate.vo.RateStaiVo">

        SELECT ratedomain as name, SUM(keyitem_score) AS value,'领域总分' as type
        FROM (
        SELECT s.ratedomain, s.domainid,
         s.keyitem_score
        FROM rate_opt o,
         (
        SELECT i.item_text AS ratedomain,d.ratedomain AS domainid, d.sondomain, k.keyitem,k.keyitem_score,k.id AS keyitemid
        FROM rate_keyitem k,rate_domain d,(
        SELECT c.item_value,c.item_text
        FROM sys_dict m,sys_dict_item c
        WHERE m.dict_code='rate_domain' AND m.id=c.dict_id) i
        WHERE
         k.rate_domain_id=d.id AND i.item_value=d.ratedomain) s
        WHERE s.keyitemid=o.keyitem_id AND o.task_id=#{query.taskid}) tab
        GROUP BY ratedomain
        ORDER BY domainid

    </select>




    <select id="queryDomainScoreRatioList" resultType="org.jeecg.modules.stm.rate.vo.RateStaiVo">


        SELECT m.ratedomain AS name, ROUND(m.ratescore/keyt.keyitem_score*100,2) AS value,100 AS stdvalue,100- ROUND(m.ratescore/keyt.keyitem_score*100,2) AS stvalue,'领域得分率' AS TYPE
        FROM rate_domainscore m,(
        SELECT domainid, SUM(keyitem_score) AS keyitem_score
        FROM (
        SELECT s.ratedomain, s.domainid,
         s.keyitem_score,o.score
        FROM rate_opt o,
         (
        SELECT i.item_text AS ratedomain,d.ratedomain AS domainid, d.sondomain, k.keyitem,k.keyitem_score,k.id AS keyitemid
        FROM rate_keyitem k,rate_domain d,(
        SELECT c.item_value,c.item_text
        FROM sys_dict m,sys_dict_item c
        WHERE m.dict_code='rate_domain' AND m.id=c.dict_id) i
        WHERE
         k.rate_domain_id=d.id AND i.item_value=d.ratedomain) s
        WHERE s.keyitemid=o.keyitem_id AND o.task_id=#{query.taskid}) tab
        GROUP BY domainid)keyt
        WHERE m.rate_task_id=#{query.taskid} AND m.ratedomain_id=keyt.domainid
        ORDER BY m.ratedomain_id desc

        <!--
        SELECT ratedomain as name, ROUND(SUM(score)/SUM(keyitem_score)*100,2) AS value,100 AS stdvalue,100-ROUND(SUM(score)/SUM(keyitem_score)*100,2) AS stvalue,'领域得分率' as type
        FROM (
        SELECT s.ratedomain, s.domainid,
         s.keyitem_score,o.score
        FROM rate_opt o,
         (
        SELECT i.item_text AS ratedomain,d.ratedomain AS domainid, d.sondomain, k.keyitem,k.keyitem_score,k.id AS keyitemid
        FROM rate_keyitem k,rate_domain d,(
        SELECT c.item_value,c.item_text
        FROM sys_dict m,sys_dict_item c
        WHERE m.dict_code='rate_domain' AND m.id=c.dict_id) i
        WHERE
         k.rate_domain_id=d.id AND i.item_value=d.ratedomain) s
        WHERE s.keyitemid=o.keyitem_id AND o.task_id=#{query.taskid}) tab
        GROUP BY ratedomain
        ORDER BY domainid
        -->
    </select>

    <select id="queryDomainRatioList" resultType="org.jeecg.modules.stm.rate.vo.RateStaiVo">

        SELECT ratedomain as name, ROUND(SUM(keyitem_score)/SUM(keyitem_score)*100,2) AS value,'领域总分' as type
        FROM (
        SELECT s.ratedomain, s.domainid,
         s.keyitem_score,o.score
        FROM rate_opt o,
         (
        SELECT i.item_text AS ratedomain,d.ratedomain AS domainid, d.sondomain, k.keyitem,k.keyitem_score,k.id AS keyitemid
        FROM rate_keyitem k,rate_domain d,(
        SELECT c.item_value,c.item_text
        FROM sys_dict m,sys_dict_item c
        WHERE m.dict_code='rate_domain' AND m.id=c.dict_id) i
        WHERE
         k.rate_domain_id=d.id AND i.item_value=d.ratedomain) s
        WHERE s.keyitemid=o.keyitem_id AND o.task_id=#{query.taskid}) tab
        GROUP BY ratedomain
        ORDER BY domainid

    </select>


    <select id="queryStdIdByLimit" resultType="org.jeecg.modules.stm.rate.vo.RateTaskOptVo">

        SELECT id as taskid
        FROM rate_std
        WHERE id IN (
        SELECT std_id
        FROM rate_opt
        WHERE task_id=#{query.taskid} AND keyitem_id in(#{query.keyitemid}) )
        AND limitstatus=1
    </select>



    <select id="queryTaskDomainScoreList" resultType="org.jeecg.modules.stm.rate.vo.RateStaiVo">

        SELECT domainid AS taskid,ratedomain AS name, SUM(score) AS value, SUM(scoreof) AS stvalue
        FROM (
        SELECT s.ratedomain, s.domainid,
         o.score,o.scoreof
        FROM rate_opt o,
         (
        SELECT i.item_text AS ratedomain,d.ratedomain AS domainid, d.sondomain, k.keyitem,k.keyitem_score,k.id AS keyitemid
        FROM rate_keyitem k,rate_domain d,(
        SELECT c.item_value,c.item_text
        FROM sys_dict m,sys_dict_item c
        WHERE m.dict_code='rate_domain' AND m.id=c.dict_id) i
        WHERE
         k.rate_domain_id=d.id AND i.item_value=d.ratedomain) s
        WHERE s.keyitemid=o.keyitem_id AND o.task_id=#{query.taskid}) tab
        GROUP BY ratedomain


    </select>


</mapper>