<?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.qmth.teachcloud.common.mapper.SysUserMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.qmth.teachcloud.common.entity.SysUser">
    <result column="id" property="id" />
        <result column="school_id" property="schoolId" />
        <result column="login_name" property="loginName" />
        <result column="real_name" property="realName" />
        <result column="password" property="password" />
        <result column="mobile_number" property="mobileNumber" />
        <result column="org_id" property="orgId" />
        <result column="enable" property="enable" />
        <result column="pwd_update_time" property="pwdUpdateTime" />
        <result column="remark" property="remark" />
        <result column="create_id" property="createId" />
        <result column="create_time" property="createTime" />
    </resultMap>

    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        select id, school_id, login_name, real_name, password, mobile_number, org_id, enable, pwd_update_time, remark, create_id, create_time from sys_user
    </sql>
    <update id="updateVerifyCodeExpiredTime">
        UPDATE basic_verify_code
        SET
            expire_time = create_time
        <where>
            <if test="mobileNumber != null and mobileNumber != ''">
                and mobile_number = #{mobileNumber}
            </if>
            <if test="userId != null and userId != ''">
                and user_id = #{userId}
            </if>
        </where>
    </update>
    <select id="listForPrintSystem" resultType="com.qmth.teachcloud.common.bean.dto.UserDto">
        SELECT
            a.id,
            a.school_id schoolId,
            a.login_name loginName,
            a.real_name realName,
            a.mobile_number mobileNumber,
            a.enable,
            a.remark,
            a.org_id orgId,
            a.code code,
            c.marker_status as markerStatus,
            c.marker_leader_status as markerLeaderStatus,
            b.name orgName
        FROM
            sys_user a
                left join
            sys_org b on a.org_id = b.id
                LEFT JOIN
            cloud_user_push_status c on a.school_id = c.school_id and a.id = c.user_id
        <where>
            <if test="schoolId != null and schoolId != ''">
                and a.school_id = #{schoolId}
            </if>
            <if test="userInfo != null and userInfo != ''">
                and (a.login_name like concat('%', #{userInfo} , '%') or a.real_name like concat('%', #{userInfo} , '%') or a.mobile_number like concat('%', #{userInfo} , '%'))
            </if>
            <if test="orgId != null and orgId != ''">
                and a.org_id = #{orgId}
            </if>
            <if test="roleId != null and roleId != ''">
                and a.id in (select user_id from sys_user_role b where b.role_id = #{roleId})
            </if>
            <if test="enable != null and enable != '' or enable == 0">
                and a.enable = #{enable}
            </if>
        </where>
        order by a.create_time desc
    </select>

    <select id="listForReportSystem" resultType="com.qmth.teachcloud.common.bean.dto.UserDto">
        SELECT
            a.id,
            a.school_id schoolId,
            a.login_name loginName,
            a.real_name realName,
            a.mobile_number mobileNumber,
            a.enable,
            a.remark,
            a.org_id orgId,
            a.code code,
            b.name orgName
        FROM
            sys_user a
                left join
            sys_org b on a.org_id = b.id
        <where>
            <if test="schoolId != null and schoolId != ''">
                and a.school_id = #{schoolId}
            </if>
            <if test="roleId != null and roleId != ''">
                and a.id in (select user_id from sys_user_role b where b.role_id = #{roleId})
            </if>
            <if test="enable != null and enable != '' or enable == 0">
                and a.enable = #{enable}
            </if>
            <if test="loginName != null and loginName != ''">
                and a.login_name like concat('%', #{loginName} , '%')
            </if>
            <if test="realName != null and realName != ''">
                and a.real_name like concat('%', #{realName} , '%')
            </if>
        </where>
        order by a.create_time desc
    </select>
    
    <select id="listUser" resultType="com.qmth.teachcloud.common.bean.dto.BlurryUserDto">
        SELECT
           distinct a.id, a.real_name name
        FROM
            sys_user a
                LEFT JOIN
            sys_user_role b ON a.id = b.user_id
                LEFT JOIN
            sys_role c ON b.role_id = c.id
                LEFT JOIN
            basic_user_course d ON a.id = d.user_id
                LEFT JOIN
            basic_course e ON d.course_id = e.id
        <where>
            and a.enable = true
            <if test="schoolId != null and schoolId != ''">
                and a.school_id = #{schoolId}
            </if>
            <if test="type != null and type != ''">
                and c.type = #{type}
            </if>
            <if test="courseCode != null and courseCode != ''">
                and e.code = #{courseCode}
            </if>
            <if test="param != null and param != ''">
                and a.id = #{param}
            </if>
            <if test="orgIds != null and orgIds != '' and orgIds.size > 0">
                AND a.org_id IN
                <foreach collection="orgIds" item="item" index="index" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
        </where>
    </select>
    <select id="listCustomerPage" resultType="com.qmth.teachcloud.common.bean.dto.UserDto">
        SELECT
        a.id,
        a.school_id schoolId,
        a.login_name loginName,
        a.real_name realName,
        a.mobile_number mobileNumber,
        a.code as code,
        a.enable,
        a.remark,
        a.org_id orgId
        FROM
        sys_user a
        <where>
            a.school_id is null and a.id not in (select user_id from sys_user_role b left join sys_role c on b.role_id = c.id where c.type = 'ADMIN')
            <if test="realName != null and realName != ''">
                and a.real_name like concat('%', #{realName} , '%')
            </if>
            <if test="enable != null and enable != '' or enable == 0">
                and a.enable = #{enable}
            </if>
        </where>
    </select>

    <sql id="approveUserResultHeaderCommon">
        select
            DISTINCT su.id,
            su.school_id as schoolId,
            su.login_name as loginName,
            su.real_name as realName,
            su.code,
            su.mobile_number as mobileNumber,
            su.org_id as orgId,
            so.name as orgName,
            so.`type` as orgType
            from sys_user su
            left join sys_user_role sur on sur.user_id = su.id
            left join sys_role_privilege srp on srp.role_id = sur.role_id
            left join sys_org so on so.id = su.org_id
    </sql>

    <select id="filterFlowPrivilege" resultType="com.qmth.teachcloud.common.bean.params.ApproveUserResult">
        <include refid="approveUserResultHeaderCommon" />
        <if test="filter != null and filter != '' and filter == 1">
            join(
            select
            sp.id
            from
            sys_privilege sp
            where sp.url = #{approveName}) t on
            t.id = srp.privilege_id
        </if>
           <where>
                and su.enable = true
               <if test="orgIds != null and orgIds != '' and orgIds.size > 0">
                   AND su.org_id IN
                   <foreach collection="orgIds" item="item" index="index" open="(" separator="," close=")">
                       #{item}
                   </foreach>
               </if>
               <if test="schoolId != null and schoolId != ''">
                   and su.school_id = #{schoolId}
               </if>
               <if test="realName != null and realName != ''">
                   and su.real_name like concat('%', #{realName} , '%')
               </if>
           </where>
    </select>

    <select id="findById" resultType="com.qmth.teachcloud.common.bean.params.ApproveUserResult">
        <include refid="approveUserResultHeaderCommon" />
        <where>
            <if test="id != null and id != ''">
                and su.id = #{id}
            </if>
                and su.enable = 1
        </where>
    </select>

    <select id="findByIds" resultType="com.qmth.teachcloud.common.bean.params.ApproveUserResult">
        select group_concat(su.real_name,'(',so.name,')' order by field(su.id,
            <if test="ids != null and ids != '' and ids.size > 0">
                <foreach collection="ids" item="item" index="index" separator=",">
                    #{item}
                </foreach>
            </if>
            )) as pendApproveUsers from sys_user su left join sys_org so on so.id = su.org_id
        <where>
            <if test="ids != null and ids != '' and ids.size > 0">
                AND su.id IN
                <foreach collection="ids" item="item" index="index" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
                and su.enable = 1
        </where>
    </select>

    <select id="selectRoleNames" resultType="com.qmth.teachcloud.common.bean.result.UserRoleNameResult">
        select group_concat(sr.name) as roleNames
             ,sur.user_id as userId from sys_role sr
        join sys_user_role sur on sur.role_id = sr.id
        <where>
            <if test="userIds != null and userIds != '' and userIds.size > 0">
                AND sur.user_id IN
                <foreach collection="userIds" item="item" index="index" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
            and sur.user_id > 1
        </where>
        group by sur.user_id
    </select>

    <select id="findByIdsForResult" resultType="com.qmth.teachcloud.common.bean.params.ApproveUserResult">
        <include refid="approveUserResultHeaderCommon" />
        <where>
            <if test="ids != null and ids != '' and ids.size > 0">
                AND su.id IN
                <foreach collection="ids" item="item" index="index" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
            and su.enable = 1
        </where>
    </select>

    <select id="findVerifyCodeByUser" resultType="com.qmth.teachcloud.common.bean.dto.VerifyCodeCheckDto">
        SELECT
            id,
            school_id AS schoolId,
            org_id AS orgId,
            user_id AS userId,
            mobile_number AS mobileNumber,
            expire_time AS expireTime,
            valid_period AS validPeriod,
            verify_code AS verifyCode,
            create_id AS createId,
            create_time AS createTime,
            update_id AS updateId,
            update_time AS updateTime
        FROM
            basic_verify_code
        <where>
            <if test="userId != null and userId != ''">
                AND user_id = #{userId}
            </if>
            <if test="mobileNumber != null and mobileNumber != ''">
                AND mobile_number = #{mobileNumber}
            </if>
        </where>
        ORDER BY update_time DESC
    </select>

    <select id="findSysUserResultBySchoolId" resultType="com.qmth.teachcloud.common.bean.result.SysUserResult">
        SELECT
            su.id AS id,
            su.real_name AS realName,
            su.mobile_number AS mobileNumber,
            so.id AS orgId,
            sr.id AS roleId,
            sr.name AS roleName
        FROM
            sys_user su
                LEFT JOIN
            sys_user_role sur ON su.id = sur.user_id
                LEFT JOIN
            sys_role sr ON sur.role_id = sr.id
                LEFT JOIN
            sys_org so ON su.org_id = so.id
        <where>
            <if test="schoolId != null and schoolId != ''">
                AND su.school_id = #{schoolId}
            </if>
        </where>
    </select>

    <select id="findByRoldIdAndMemberName" resultType="com.qmth.teachcloud.common.bean.params.ApproveUserResult">
        select DISTINCT su.id,
            su.school_id as schoolId,
            su.login_name as loginName,
            su.real_name as realName,
            su.code,
            su.mobile_number as mobileNumber,
            su.org_id as orgId,
            so.name as orgName,
            so.`type` as orgType,
            srgm.role_id as roleId,
            srgm.user_id as userId
            from sys_role_group_member srgm
            join sys_user su on su.id = srgm.member_id
            join sys_user_role sur on sur.user_id = su.id
            join sys_org so on so.id = su.org_id
        <where>
            <if test="roleId != null and roleId != ''">
                and srgm.role_id = #{roleId}
            </if>
            <if test="name != null and name != ''">
                and su.real_name like concat('%',#{name},'%')
            </if>
            <if test="schoolId != null and schoolId != ''">
                and su.school_id = #{schoolId}
            </if>
            and su.enable = 1
            and srgm.enable = 1
        </where>
    </select>
    <select id="findCloudUserPushStatus"
            resultType="com.qmth.teachcloud.common.bean.dto.CloudUserPushStatusDto">
        SELECT
            school_id AS schoolId,
            user_id AS userId,
            marker_status AS markerStatus,
            marker_leader_status AS markerLeaderStatus,
            sync_status AS syncStatus
        FROM
            cloud_user_push_status
        <where>
            <if test="schoolId != null and schoolId != ''">
                AND school_id = #{schoolId}
            </if>
            <if test="userId != null and userId != ''">
                AND user_id = #{userId}
            </if>
        </where>
    </select>

    <select id="listExportData" resultType="com.qmth.teachcloud.common.bean.dto.UserExportDto">
        SELECT
        a.id,
        a.school_id schoolId,
        a.login_name loginName,
        a.real_name realName,
        a.mobile_number mobileNumber,
        a.enable,
        a.remark,
        a.org_id orgId,
        a.code code,
        c.marker_status as markerStatus,
        c.marker_leader_status as markerLeaderStatus,
        b.name orgName
        FROM
        sys_user a
        left join
        sys_org b on a.org_id = b.id
        LEFT JOIN
        cloud_user_push_status c on a.school_id = c.school_id and a.id = c.user_id
        <where>
            <if test="schoolId != null and schoolId != ''">
                and a.school_id = #{schoolId}
            </if>
            <if test="userInfo != null and userInfo != ''">
                and (a.login_name like concat('%', #{userInfo} , '%') or a.real_name like concat('%', #{userInfo} , '%') or a.mobile_number like concat('%', #{userInfo} , '%'))
            </if>
            <if test="orgId != null and orgId != ''">
                and a.org_id = #{orgId}
            </if>
            <if test="roleId != null and roleId != ''">
                and a.id in (select user_id from sys_user_role b where b.role_id = #{roleId})
            </if>
            <if test="enable != null and enable != '' or enable == 0">
                and a.enable = #{enable}
            </if>
        </where>
        order by a.create_time desc
    </select>

    <select id="findSysUserResultListNew" resultType="com.qmth.teachcloud.common.bean.result.SysUserResult">
        select
        su.id as id,
        su.login_name as loginName,
        su.real_name as realName,
        su.real_name as name,
        su.mobile_number as mobileNumber,
        so.id as orgId,
        group_concat(CONCAT(sr.id, ",", sr.name) SEPARATOR ';') as roleIds
        from
        sys_user su
        join sys_org so on
        so.id = su.org_id
        join sys_user_role sur on
        sur.user_id = su.id
        join sys_role sr on
        sr.id = sur.role_id
        <where> 1 = 1
            <if test="schoolId != null and schoolId != ''">
                and su.school_id = #{schoolId}
            </if>
            <if test="roleIds != null and roleIds != '' and roleIds.size > 0">
                AND sr.id IN
                <foreach collection="roleIds" item="item" index="index" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
            and su.enable = 1
            and so.enable = 1
            and sur.enable = 1
            and sr.enable = 1
        </where>
        group by
        su.id,
        su.login_name,
        su.real_name,
        su.mobile_number,
        so.id
        order by
        su.login_name
    </select>

    <select id="findCourseSysUserResultList" resultType="com.qmth.teachcloud.common.bean.result.SysUserResult">
        select
            su.id as id,
            su.login_name as loginName,
            su.real_name as realName,
            su.mobile_number as mobileNumber,
            so.id as orgId,
            so.name as orgName,
            so.code as orgCode
        from
            sys_user su
                join sys_org so on
                so.id = su.org_id
                join sys_user_role sur on
                sur.user_id = su.id
                join sys_role sr on
                sr.id = sur.role_id
                join teach_course tc on
                tc.user_id = su.id
                join basic_course bc on bc.id = tc.basic_course_id
        <where>
            <if test="schoolId != null and schoolId != ''">
                and tc.school_id = #{schoolId}
            </if>
            <if test="courseCode != null and courseCode != ''">
                and bc.code = #{courseCode}
            </if>
        </where>
        group by
            su.id,
            su.login_name,
            su.real_name,
            su.mobile_number,
            so.id,
            so.name,
            so.code
        order by
            su.login_name
    </select>
    <select id="findByRoleTypeAndOrgId" resultType="com.qmth.teachcloud.common.bean.dto.UserRoleDto">
        SELECT
            su.id userId,
            su.login_name loginName,
            su.real_name realName,
            sr.id roleId,
            sr.name roleName
        FROM
            sys_user su
                JOIN
            sys_user_role sur ON su.id = sur.user_id
                JOIN
            sys_role sr ON sur.role_id = sr.id
        WHERE
            su.org_id = #{orgId}
          AND sr.type = #{roleType}
    </select>
</mapper>
