<?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.testor.module.train.project.dao.TTrainProjectDao">
    <!-- 开启二级缓存 -->
    <!-- <cache type="org.mybatis.caches.ehcache.LoggingEhcache"/> -->
    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.testor.module.train.project.model.domain.TTrainProject">
        <id column="id" property="id"/>
        <id column="parent_id" property="parentId"/>
        <id column="project_name" property="projectName"/>
        <id column="visit_start_time" property="visitStartTime"/>
        <id column="visit_end_time" property="visitEndTime"/>
        <id column="visit_way" property="visitWay"/>
        <id column="visit_type" property="visitType"/>
        <id column="course_id" property="courseId"/>
        <id column="is_add_exam" property="isAddExam"/>
        <id column="test_paper_id" property="testPaperId"/>
        <id column="exam_type" property="examType"/>
        <id column="exam_name" property="examName"/>
        <id column="exam_start_time" property="examStartTime"/>
        <id column="exam_end_time" property="examEndTime"/>
        <id column="exam_site" property="examSite"/>
        <id column="exam_duration" property="examDuration"/>
        <id column="should_learn_number" property="shouldLearnNumber"/>
        <id column="finish_number" property="finishNumber"/>
        <id column="percentage" property="percentage"/>
        <!--        <id column="person_affiliation" property="personAffiliation"/>-->
        <!--        <id column="company" property="company"/>-->
        <id column="process_status" property="processStatus"/>
        <id column="train_type" property="trainType"/>
    </resultMap>
    <select id="getAffiliation" parameterType="com.testor.module.train.project.model.dto.TTrainProjectParam"
            resultType="com.testor.module.train.project.model.dto.TTrainProjectParam" databaseId="mysql">
        select *
        from (select p.project_name
        , p.id
        , p.visit_way
        , p.visit_type
        , p.should_learn_number
        , p.finish_number
        , p.visit_start_time
        , p.visit_end_time
        , p.qr_code
        , p.org_name
        , p.org_id
        , p.create_date
        , p.train_type
        from t_train_project p
        left join t_train_my_management my
        on p.id = my.task_id
        where p.status = 0
        <if test="param.projectName!=null and param.projectName!=''">
            AND p.project_name LIKE CONCAT('%',#{param.projectName},'%')
        </if>
        <if test="param.personAffiliation!=null and param.personAffiliation!=''">
            AND my.person_affiliation =#{param.personAffiliation}
        </if>
        <if test="param.company!=null and param.company!=''">
            AND find_in_set(my.org_id,#{param.company})
        </if>
        <if test="param.EndTime!=null and param.EndTime!=''and param.StartTime!=null and param.StartTime!=''">
            AND p.visit_start_time between #{param.StartTime} and #{param.EndTime}
        </if>
        <if test="param.trainType!=null and param.trainType!=''">
            AND p.train_type =#{param.trainType}
        </if>
        <if test="param.visitType!=null and param.visitType!=''">
            AND p.visit_type =#{param.visitType}
        </if>
        order by p.create_date desc
        ) mm
        where mm.org_id in (select org.org_id
        from t_sys_org org
        where find_in_set(org.org_id, #{parentIds})
        or org.parent_ids like concat('%', #{orgId}, '%')
        or org.org_id = #{orgId}) group by id
    </select>
    <select id="getAffiliation" parameterType="com.testor.module.train.project.model.dto.TTrainProjectParam"
            resultType="com.testor.module.train.project.model.dto.TTrainProjectParam" databaseId="kingbase">
        SELECT
        project_name ,
        id ,
        visit_way ,
        visit_type ,
        should_learn_number ,
        finish_number ,
        visit_start_time ,
        visit_end_time ,
        qr_code ,
        org_name ,
        org_id ,
        create_date ,
        train_type
        FROM
        t_train_project
        WHERE
        id IN (
        select id
        from (select p.project_name
        , p.id
        , p.visit_way
        , p.visit_type
        , p.should_learn_number
        , p.finish_number
        , p.visit_start_time
        , p.visit_end_time
        , p.qr_code
        , p.org_name
        , p.org_id
        , p.create_date
        , p.train_type
        from t_train_project p
        left join t_train_my_management my
        on p.id = my.task_id
        where p.status = 0
        <if test="param.projectName!=null and param.projectName!=''">
            AND p.project_name LIKE CONCAT('%',#{param.projectName},'%')
        </if>
        <if test="param.personAffiliation!=null and param.personAffiliation!=''">
            AND my.person_affiliation =#{param.personAffiliation}
        </if>
        <if test="param.company!=null and param.company!=''">
            AND find_in_set(my.org_id,#{param.company})
        </if>
        <if test="param.EndTime!=null and param.EndTime!=''and param.StartTime!=null and param.StartTime!=''">
            AND p.visit_start_time between #{param.StartTime} and #{param.EndTime}
        </if>
        <if test="param.trainType!=null and param.trainType!=''">
            AND p.train_type =#{param.trainType}
        </if>
        <if test="param.visitType!=null and param.visitType!=''">
            AND p.visit_type LIKE CONCAT('%',#{param.visitType},'%')
        </if>
        order by p.create_date desc
        ) mm
        where mm.org_id in (select org.org_id
        from t_sys_org org
        where find_in_set(org.org_id, #{parentIds})
        or org.parent_ids like concat('%', #{orgId}, '%')
        or org.org_id = #{orgId}) group by id)
    </select>

    <select id="getProjectPerson" resultType="com.testor.module.train.project.model.dto.TTrainProjectParam">
        select p.project_name
             , p.id
             , p.visit_way
             , p.visit_type
             , p.should_learn_number
             , p.finish_number
             , my.percentage
             , p.visit_start_time
             , p.visit_end_time
             , p.course_id
             , p.qr_code
             , p.org_name
             , p.org_id
             , p.create_date
             , p.train_type
             , my.person_affiliation
             , my.company
             , my.id        as myManagementId
             , my.person_id as personId
        from (select project_name
                   , id
                   , visit_way
                   , visit_type
                   , should_learn_number
                   , finish_number
                   , visit_start_time
                   , visit_end_time
                   , qr_code
                   , org_name
                   , org_id
                   , create_date
                   , course_id
                   , train_type
              from t_train_project
              where id = #{id}) p
                 join t_train_my_management my on p.id = my.task_id
    </select>


    <select id="findProjectTotalTime" resultType="com.testor.module.train.project.model.stat.ProjectStatTotalVo" databaseId="mysql">
        SELECT
        SUM(TIMESTAMPDIFF( HOUR, p.visit_start_time, p.visit_end_time )) AS project_total_time




        FROM
        t_train_project p
        WHERE
        p.STATUS = 0
        <if test="startTime !=null and   startTime !=''">
            AND p.visit_start_time &gt;= #{startTime}
        </if>
        <if test=" endTime !=null and  endTime !=''">
            AND p.visit_start_time &lt;= #{endTime}
        </if>
        <if test="list != null and list.size()>0">
            AND p.org_id IN
            <foreach item="item" index="index" collection="list" open="(" close=")" separator=",">
                #{item}
            </foreach>
        </if>
        <if test=" projectId !=null and  projectId !=''">
            AND p.id = #{projectId}
        </if>
    </select>
    <select id="findProjectTotalTime" resultType="com.testor.module.train.project.model.stat.ProjectStatTotalVo" databaseId="kingbase" >
        SELECT
        SUM(EXTRACT(EPOCH FROM AGE(p.visit_end_time, p.visit_start_time)) / 3600) AS project_total_time



        FROM
        t_train_project p
        WHERE
        p.STATUS = 0
        <if test="startTime !=null and   startTime !=''">
            AND p.visit_start_time &gt;= #{startTime}
        </if>
        <if test=" endTime !=null and  endTime !=''">
            AND p.visit_start_time &lt;= #{endTime}
        </if>
        <if test="list != null and list.size()>0">
            AND p.org_id IN
            <foreach item="item" index="index" collection="list" open="(" close=")" separator=",">
                #{item}
            </foreach>
        </if>
        <if test=" projectId !=null and  projectId !=''">
            AND p.id = #{projectId}
        </if>
    </select>


    <select id="findProjectPersonList"
            resultType="com.testor.module.train.project.model.dto.TTrainProjectParam">
        SELECT
            m.id AS myManagementId,
            p.course_id,
            m.person_id AS personId,
            m.visit_start_time,
            m.visit_end_time ,
            m.tab_status
        FROM
        t_train_my_management m
        LEFT JOIN t_train_project p ON p.id = m.task_id
        WHERE
        p.STATUS = 0
        <if test="startTime !=null and   startTime !=''">
            AND p.visit_start_time &gt;= #{startTime}
        </if>
        <if test=" endTime !=null and  endTime !=''">
            AND p.visit_start_time &lt;= #{endTime}
        </if>
        <if test="list != null and list.size()>0">
            AND p.org_id IN
            <foreach item="item" index="index" collection="list" open="(" close=")" separator=",">
                #{item}
            </foreach>
        </if>
        <if test=" projectId !=null and  projectId !=''">
            AND p.id = #{projectId}
        </if>
    </select>

    <select id="findProjectNumByOrgOrVisitType"
            resultType="com.testor.module.train.project.model.stat.ProjectStatTotalVo">
        SELECT
            COUNT( id ) AS fanChartValue,
            <if test=" chartType !=null and  chartType !='' and chartType == 1 ">
                p.org_id ,
                o.org_name  AS fanChartKey
            </if>
            <if test=" chartType !=null and  chartType !='' and  chartType == 2 ">
                 p.visit_type  AS fanChartKey
            </if>
        FROM
            t_train_project p
            LEFT JOIN t_sys_org  o ON p.org_id = o.org_id
        WHERE
        p.STATUS = 0
        <if test="startTime !=null and   startTime !=''">
            AND p.visit_start_time &gt;= #{startTime}
        </if>
        <if test=" endTime !=null and  endTime !=''">
            AND p.visit_start_time &lt;= #{endTime}
        </if>
        <if test="list != null and list.size()>0">
            AND p.org_id IN
            <foreach item="item" index="index" collection="list" open="(" close=")" separator=",">
                #{item}
            </foreach>
        </if>
        <if test=" chartType !=null and  chartType !='' and  chartType == 1 ">
            GROUP BY  p.org_id
        </if>
        <if test=" chartType !=null and  chartType !='' and  chartType == 2 ">
            GROUP BY  p.visit_type
        </if>

    </select>

    <select id="findPersonNumByOrgOrVisitType"
            resultType="com.testor.module.train.project.model.stat.ProjectStatTotalVo">
        SELECT
        COUNT( m.id ) AS fanChartValue,
        <if test=" chartType !=null and  chartType !='' and chartType == 1 ">
            p.org_id ,
            o.org_name  AS fanChartKey
        </if>
        <if test=" chartType !=null and  chartType !='' and  chartType == 2 ">
            p.visit_type  AS fanChartKey
        </if>
        FROM
            t_train_my_management m
            LEFT JOIN t_train_project p  ON m.task_id = p.id
            LEFT JOIN t_sys_org  o ON p.org_id = o.org_id
        WHERE
        p.STATUS = 0
        <if test="startTime !=null and   startTime !=''">
            AND p.visit_start_time &gt;= #{startTime}
        </if>
        <if test=" endTime !=null and  endTime !=''">
            AND p.visit_start_time &lt;= #{endTime}
        </if>
        <if test="list != null and list.size()>0">
            AND p.org_id IN
            <foreach item="item" index="index" collection="list" open="(" close=")" separator=",">
                #{item}
            </foreach>
        </if>
        <if test=" chartType !=null and  chartType !='' and  chartType == 1 ">
            GROUP BY  p.org_id
        </if>
        <if test=" chartType !=null and  chartType !='' and  chartType == 2 ">
            GROUP BY  p.visit_type
        </if>

    </select>

    <select id="findTrainTimeByOrgOrVisitType"  resultType="com.testor.module.train.project.model.stat.ProjectStatTotalVo" databaseId="mysql" >
        SELECT
            SUM(TIMESTAMPDIFF( HOUR, p.visit_start_time, p.visit_end_time )) AS fanChartValue,

        <if test=" chartType !=null and  chartType !='' and chartType == 1 ">
            p.org_id ,
            o.org_name  AS fanChartKey
        </if>
        <if test=" chartType !=null and  chartType !='' and  chartType == 2 ">
            p.visit_type  AS fanChartKey
        </if>
        FROM
             t_train_project p
            LEFT JOIN t_sys_org o ON p.org_id = o.org_id
        WHERE
        p.STATUS = 0
        <if test="startTime !=null and   startTime !=''">
            AND p.visit_start_time &gt;= #{startTime}
        </if>
        <if test=" endTime !=null and  endTime !=''">
            AND p.visit_start_time &lt;= #{endTime}
        </if>
        <if test="list != null and list.size()>0">
            AND p.org_id IN
            <foreach item="item" index="index" collection="list" open="(" close=")" separator=",">
                #{item}
            </foreach>
        </if>
        <if test=" chartType !=null and  chartType !='' and  chartType == 1 ">
            GROUP BY  p.org_id
        </if>
        <if test=" chartType !=null and  chartType !='' and  chartType == 2 ">
            GROUP BY  p.visit_type
        </if>
    </select>
    <select id="findTrainTimeByOrgOrVisitType"  resultType="com.testor.module.train.project.model.stat.ProjectStatTotalVo" databaseId="kingbase" >
        SELECT

        SUM(EXTRACT(EPOCH FROM AGE(p.visit_end_time, p.visit_start_time)) / 3600) AS fanChartValue

        <if test=" chartType !=null and  chartType !='' and chartType == 1 ">
            p.org_id ,
            o.org_name  AS fanChartKey
        </if>
        <if test=" chartType !=null and  chartType !='' and  chartType == 2 ">
            p.visit_type  AS fanChartKey
        </if>
        FROM
        t_train_project p
        LEFT JOIN t_sys_org o ON p.org_id = o.org_id
        WHERE
        p.STATUS = 0
        <if test="startTime !=null and   startTime !=''">
            AND p.visit_start_time &gt;= #{startTime}
        </if>
        <if test=" endTime !=null and  endTime !=''">
            AND p.visit_start_time &lt;= #{endTime}
        </if>
        <if test="list != null and list.size()>0">
            AND p.org_id IN
            <foreach item="item" index="index" collection="list" open="(" close=")" separator=",">
                #{item}
            </foreach>
        </if>
        <if test=" chartType !=null and  chartType !='' and  chartType == 1 ">
            GROUP BY  p.org_id
        </if>
        <if test=" chartType !=null and  chartType !='' and  chartType == 2 ">
            GROUP BY  p.visit_type
        </if>
    </select>

    <select id="findTrainTimeTotal" resultType="java.lang.String" databaseId="mysql">
        SELECT
        SUM(
        TIMESTAMPDIFF( HOUR, p.visit_start_time, p.visit_end_time )) AS total
        FROM
        t_train_project p
        LEFT JOIN t_sys_org o ON p.org_id = o.org_id
        WHERE
        p.STATUS = 0
        <if test="startTime !=null and   startTime !=''">
            AND p.visit_start_time &gt;= #{startTime}
        </if>
        <if test=" endTime !=null and  endTime !=''">
            AND p.visit_start_time &lt;= #{endTime}
        </if>
    </select>
    <select id="findTrainTimeTotal" resultType="java.lang.String" databaseId="kingbase">
        SELECT  SUM(EXTRACT(EPOCH FROM AGE(p.visit_end_time, p.visit_start_time)) / 3600) AS total
        FROM
        t_train_project p
        LEFT JOIN t_sys_org o ON p.org_id = o.org_id
        WHERE
        p.STATUS = 0
        <if test="startTime !=null and   startTime !=''">
            AND p.visit_start_time &gt;= #{startTime}
        </if>
        <if test=" endTime !=null and  endTime !=''">
            AND p.visit_start_time &lt;= #{endTime}
        </if>
    </select>



    <select id="findGradeStat" resultType="com.testor.module.train.project.model.stat.ProjectStatTotalVo">
        SELECT
            SUM(m.assess_minute) AS gradeScoreTotal,
            COUNT(m.user_id) AS  gradePersonCount
        FROM
            t_train_grade m
                LEFT JOIN t_train_project p ON m.task_id = p.id
                LEFT JOIN t_sys_org o ON p.org_id = o.org_id
        WHERE
        p.STATUS = 0
        <if test="startTime !=null and   startTime !=''">
            AND p.visit_start_time &gt;= #{startTime}
        </if>
        <if test=" endTime !=null and  endTime !=''">
            AND p.visit_start_time &lt;= #{endTime}
        </if>
        <if test="list != null and list.size()>0">
            AND p.org_id IN
            <foreach item="item" index="index" collection="list" open="(" close=")" separator=",">
                #{item}
            </foreach>
        </if>
        <if test=" projectId !=null and  projectId !=''">
            AND p.id = #{projectId}
        </if>
    </select>
    <select id="findTrainTypeStat" resultType="com.testor.module.train.project.model.stat.ProjectStatTotalVo">
        SELECT
            COUNT( m.id ) AS fanChartValue,
            p.train_type AS fanChartKey
        FROM
            t_train_my_management m
                LEFT JOIN t_train_project p ON m.task_id = p.id
                LEFT JOIN t_sys_org o ON p.org_id = o.org_id
        WHERE
        p.STATUS = 0
        <if test="startTime !=null and   startTime !=''">
            AND p.visit_start_time &gt;= #{startTime}
        </if>
        <if test=" endTime !=null and  endTime !=''">
            AND p.visit_start_time &lt;= #{endTime}
        </if>
        <if test="list != null and list.size()>0">
            AND p.org_id IN
            <foreach item="item" index="index" collection="list" open="(" close=")" separator=",">
                #{item}
            </foreach>
        </if>
        GROUP BY   p.train_type
    </select>
    <select id="findExamStates" resultType="com.testor.module.train.project.model.stat.ProjectScoreStatVo">
        SELECT
            COUNT( m.id ) AS examNum,
            COUNT( CASE WHEN m.is_add_exam = '1' THEN m.id ELSE NULL END ) AS examNumFinish,
            CONCAT(
                    FORMAT((
                               COUNT( CASE WHEN m.is_add_exam = '1' THEN m.id ELSE NULL END ) / COUNT( m.id ) * 100
                               ),
                           0
                        ),
                    '%'
                ) AS examFinishPercentage
        FROM
            t_train_my_management m
        WHERE
                m.id IN (
                SELECT
                    m.parent_id
                FROM
                    t_train_my_management m
                        LEFT JOIN t_train_project p ON p.id = m.task_id
                WHERE
                    p.id = #{projectId}
                  AND p.is_add_exam = '1'
            )
    </select>
    <select id="findScoreStat" resultType="java.lang.String">
        SELECT
            m.test_score
        FROM
            t_train_my_management m
        WHERE
                m.id IN (
                SELECT
                    m.parent_id
                FROM
                    t_train_my_management m
                        LEFT JOIN t_train_project p ON p.id = m.task_id
                WHERE
                    p.id = #{projectId}
                  AND p.is_add_exam = '1'
            ) AND m.test_score IS NOT NULL

    </select>
    <select id="findTrainProjectCompleted"   resultType="com.testor.module.train.project.model.stat.ProjectStatTotalVo">
        SELECT
            COUNT(m.person_id ) AS projectPersonCompleted
        FROM
            t_train_my_management m
            LEFT JOIN t_train_project p ON p.id = m.task_id
        WHERE
         p.STATUS = 0 AND m.tab_status = '1' AND m.current_status = '3'
        <if test="startTime !=null and   startTime !=''">
            AND p.visit_start_time &gt;= #{startTime}
        </if>
        <if test=" endTime !=null and  endTime !=''">
            AND p.visit_start_time &lt;= #{endTime}
        </if>
        <if test="list != null and list.size()>0">
            AND p.org_id IN
            <foreach item="item" index="index" collection="list" open="(" close=")" separator=",">
                #{item}
            </foreach>
        </if>
        <if test=" projectId !=null and  projectId !=''">
            AND p.id = #{projectId}
        </if>
    </select>
    <select id="findProjectNumByOrgId" resultType="java.lang.String">
        SELECT
        COUNT( id ) AS fanChartValue
        FROM
        t_train_project p
        LEFT JOIN t_sys_org  o ON p.org_id = o.org_id
        WHERE
        p.STATUS = 0
        <if test="startTime !=null and   startTime !=''">
            AND p.visit_start_time &gt;= #{startTime}
        </if>
        <if test=" endTime !=null and  endTime !=''">
            AND p.visit_start_time &lt;= #{endTime}
        </if>
        <if test="list != null and list.size()>0">
            AND p.org_id IN
            <foreach item="item" index="index" collection="list" open="(" close=")" separator=",">
                #{item}
            </foreach>
        </if>
    </select>
    <select id="findPersonNumByOrgOrgId" resultType="java.lang.String">
        SELECT
        COUNT( m.id ) AS fanChartValue
        FROM
        t_train_my_management m
        LEFT JOIN t_train_project p  ON m.task_id = p.id
        LEFT JOIN t_sys_org  o ON p.org_id = o.org_id
        WHERE
        p.STATUS = 0
        <if test="startTime !=null and   startTime !=''">
            AND p.visit_start_time &gt;= #{startTime}
        </if>
        <if test=" endTime !=null and  endTime !=''">
            AND p.visit_start_time &lt;= #{endTime}
        </if>
        <if test="list != null and list.size()>0">
            AND p.org_id IN
            <foreach item="item" index="index" collection="list" open="(" close=")" separator=",">
                #{item}
            </foreach>
        </if>
    </select>

    <select id="findTrainTimeSumByOrgId" resultType="java.lang.String" databaseId="mysql" >
        SELECT
        SUM(TIMESTAMPDIFF( HOUR, p.visit_start_time, p.visit_end_time )) AS fanChartValue
        FROM
        t_train_project p
        LEFT JOIN t_sys_org o ON p.org_id = o.org_id
        WHERE
        p.STATUS = 0
        <if test="startTime !=null and   startTime !=''">
            AND p.visit_start_time &gt;= #{startTime}
        </if>
        <if test=" endTime !=null and  endTime !=''">
            AND p.visit_start_time &lt;= #{endTime}
        </if>
        <if test="list != null and list.size()>0">
            AND p.org_id IN
            <foreach item="item" index="index" collection="list" open="(" close=")" separator=",">
                #{item}
            </foreach>
        </if>
    </select>
    <select id="findTrainTimeSumByOrgId" resultType="java.lang.String" databaseId="kingbase">
        SELECT
        ROUND(SUM(EXTRACT(EPOCH FROM AGE(p.visit_end_time, p.visit_start_time)) / 3600)) AS fanChartValue
        FROM
        t_train_project p
        LEFT JOIN
        t_sys_org o ON p.org_id = o.org_id
        WHERE
        p.STATUS = 0
        <if test="startTime !=null and   startTime !=''">
            AND p.visit_start_time &gt;= #{startTime}
        </if>
        <if test=" endTime !=null and  endTime !=''">
            AND p.visit_start_time &lt;= #{endTime}
        </if>
        <if test="list != null and list.size()>0">
            AND p.org_id IN
            <foreach item="item" index="index" collection="list" open="(" close=")" separator=",">
                #{item}
            </foreach>
        </if>
    </select>

</mapper>