<?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.ProfessionalIndicatorScoreDao">


    <select id="findProjectUserPageList"
            resultType="com.testor.module.train.project.model.stat.TrainProjectUserListVo">
        SELECT
            DISTINCT
            u.user_id,
            u.login_name,
            u.user_name,
            u.dept_id,
            ue.user_type_code,
            my.person_affiliation,
            s.total_score,
            tn.trainNum,
            tesn.testNum,
            gn.gradeNum
        FROM
        ( SELECT tab_status, person_affiliation, person_id FROM t_train_my_management m, t_train_project p WHERE m.task_id = p.id ) my
            LEFT JOIN t_sys_user u ON my.person_id = u.user_id
            LEFT JOIN iam_user_mapping i ON u.user_id = i.user_id
            LEFT JOIN t_sys_user_extend ue ON ue.user_id = u.user_id
            LEFT JOIN t_sys_org o ON o.org_id = u.org_id
            LEFT JOIN t_professional_indicator_score s ON s.user_id = u.user_id
            LEFT JOIN (
                SELECT
                u.login_name,
                COUNT( m.id ) AS trainNum
                FROM
                t_train_my_management  m
                LEFT JOIN t_sys_user u ON m.person_id = u.user_id
                LEFT JOIN t_train_project p ON m.task_id = p.id
                WHERE
                m.tab_status = 1  AND    p.status = 0
                GROUP BY
                u.login_name
            ) tn ON tn.login_name = u.login_name
            LEFT JOIN (
                SELECT
                u.login_name,
                COUNT( m.id ) AS testNum
                FROM
                t_train_my_management m
                LEFT JOIN t_sys_user u ON m.person_id = u.user_id
                WHERE
                m.is_add_exam = '1'
                AND m.tab_status = '2'
                GROUP BY
                u.login_name
            ) tesn ON tesn.login_name = u.login_name
            LEFT JOIN (
                SELECT
                u.login_name,
                COUNT( g.id ) AS gradeNum
                FROM
                t_train_grade g
                LEFT JOIN t_sys_user u ON u.user_id = g.user_id
                GROUP BY
                u.login_name
            ) gn ON gn.login_name = u.login_name
        WHERE
        u.status = 0 AND my.tab_status = 1
        <if test="param.userType!=null and param.userType!=''">
            AND  ue.user_type_code = #{param.userType}
        </if>
        <if test="param.personAffiliation!=null and param.personAffiliation!=''">
            AND my.person_affiliation = #{param.personAffiliation}
        </if>
        <if test="param.userName!=null and param.userName!=''">
            AND u.user_name LIKE CONCAT('%',#{param.userName},'%')
        </if>
        <if test="list != null and list.size()>0">
            AND u.user_id  IN
            <foreach item="item" index="index" collection="list" open="(" close=")" separator=",">
                #{item}
            </foreach>
        </if>
        ORDER BY CASE
        WHEN u.user_id = #{param.userId} THEN 1
        ELSE 2
        END

    </select>



</mapper>
