<?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.zcloud.mapper.edu.api.EducationApiMapper">

    <select id="getDiagnosis" parameterType="pd" resultType="pd">
        SELECT
            (select count(1) from BUS_CURRICULUM f  where DATE_FORMAT(f.CREATTIME,'%Y-%m-%d') <![CDATA[ <= ]]> #{ENDTIME} and DATE_FORMAT(f.CREATTIME,'%Y-%m-%d') <![CDATA[ >= ]]> #{STARTTIME} and f.CORPINFO_ID = #{CORPINFO_ID}) as curriculum_count,
            (select count(1) from BUS_COURSEPAPERS f  where DATE_FORMAT(f.CREATTIME,'%Y-%m-%d') <![CDATA[ <= ]]> #{ENDTIME} and DATE_FORMAT(f.CREATTIME,'%Y-%m-%d') <![CDATA[ >= ]]> #{STARTTIME} and f.CORPINFO_ID = #{CORPINFO_ID}) as paper_count,
            (select count(1) from BUS_STUDYTASK f  where DATE_FORMAT(f.CREATTIME,'%Y-%m-%d') <![CDATA[ <= ]]> #{ENDTIME} and DATE_FORMAT(f.CREATTIME,'%Y-%m-%d') <![CDATA[ >= ]]> #{STARTTIME} and f.CORPINFO_ID = #{CORPINFO_ID} ) as task_count
    </select>

    <select id="studytaskList" parameterType="pd" resultType="pd">
        select
            f.*,
            dic1.name as post_type_name,
            dic2.name as train_type_name,
            (SELECT count(*) FROM bus_stagestudentrelation s1 WHERE s1.STUDYTASK_ID = f.STUDYTASK_ID and s1.ISDELETE = '0' ) AS userCount,
            (SELECT count(*) FROM bus_stagestudentrelation s2 WHERE s2.STUDYTASK_ID = f.STUDYTASK_ID and s2.ISDELETE = '0' and CAST(st.PASSSCORE AS DECIMAL) &lt;= CAST(s2.STAGEEXAMSCORE AS DECIMAL) and s2.STAGEEXAMSCORE > '-1') AS hg,
            (SELECT count(*) FROM bus_stagestudentrelation s3 WHERE s3.STUDYTASK_ID = f.STUDYTASK_ID and s3.ISDELETE = '0' and s3.STAGEEXAMSCORE > '-1') AS cj
        from
            BUS_STUDYTASK f
        LEFT JOIN sys_dictionaries dic1 ON dic1.DICTIONARIES_ID = f.POSTTYPE
        LEFT JOIN sys_dictionaries dic2 ON dic2.DICTIONARIES_ID = f.TRAINTYPE
        LEFT JOIN BUS_STAGEEXAMPAPER st on st.STUDYTASK_ID = f.STUDYTASK_ID
        where f.CORPINFO_ID = #{CORPINFO_ID}
            and DATE_FORMAT(f.CREATTIME,'%Y-%m-%d') <![CDATA[ <= ]]> #{ENDTIME} and DATE_FORMAT(f.CREATTIME,'%Y-%m-%d') <![CDATA[ >= ]]> #{STARTTIME}
        ORDER BY f.OPERATTIME desc ,f.PEIXUE_START_TIME DESC
    </select>

    <select id="eduMonthSta" parameterType="pd" resultType="pd">
        select
            count(1) task_count,
            (SELECT count(1) FROM bus_stagestudentrelation s1 WHERE s1.STUDYTASK_ID = f.STUDYTASK_ID and s1.ISDELETE = '0' ) AS userCount
        from
            BUS_STUDYTASK f
        where f.CORPINFO_ID = #{CORPINFO_ID}
          and DATE_FORMAT(f.CREATTIME,'%Y-%m-%d') <![CDATA[ <= ]]> #{ENDTIME} and DATE_FORMAT(f.CREATTIME,'%Y-%m-%d') <![CDATA[ >= ]]> #{STARTTIME}
    </select>
</mapper>
