<?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.basic.info.persistence.mapper.UserMapper">
    <select id="selectListAll" resultType="com.zcloud.basic.info.persistence.dataobject.UserDO">
        select
        c.corp_name as corpinfoName,
        d.name as department_name,
        p.post_name,
        u.id,
        u.user_id,
        u.username,
        u.name,
        u.corpinfo_id,
        u.main_corp_flag,
        u.user_type,
        u.department_id,
        u.post_id,
        u.role_id,
        u.email,
        u.phone,
        u.personnel_type,
        u.personnel_type_name,
        u.nation,
        u.nation_name,
        u.user_id_card,
        u.user_avatar_url,
        u.current_address,
        u.location_address,
        u.rank_level,
        u.rank_level_name,
        u.sort,
        u.version,
        u.create_id,
        u.create_name,
        u.create_time,
        u.update_id,
        u.update_name,
        u.update_time,
        u.remarks,
        u.delete_enum,
        u.tenant_id,
        u.org_id,
        u.env,
        u.department_leader_flag,
        u.deputy_leader_flag,
        u.cultural_level,
        u.cultural_level_name,
        u.marital_status,
        u.marital_status_name,
        u.political_affiliation,
        u.political_affiliation_name,
        u.employment_flag,
        u.flow_flag,
        u.rz_flag,
        CASE
        WHEN LENGTH(FROM_BASE64(u.user_id_card)) > 0
        AND MOD(SUBSTRING(FROM_BASE64(u.user_id_card), 17, 1), 2) = 1 THEN
        '男'
        WHEN LENGTH(FROM_BASE64(u.user_id_card)) > 0
        AND MOD(SUBSTRING(FROM_BASE64(u.user_id_card), 17, 1), 2) = 0 THEN
        '女'
        END AS sex,

        CASE
        WHEN LENGTH(FROM_BASE64(u.user_id_card)) > 0 THEN
        (YEAR(NOW()) - SUBSTRING(FROM_BASE64(u.user_id_card), 7, 4)) ELSE NULL
        END AS age,

        cast(substring(FROM_BASE64(u.user_id_card), 7, 8) AS DATE) AS birthday
        from user u
        left join corp_info c on c.id = u.corpinfo_id
        left join department d on d.id = u.department_id
        left join post p on p.id = u.post_id
        <where>
            <if test="params.noMain != null">
                and u.id != u.corpinfo_id
            </if>
            and u.delete_enum = 'FALSE'
            <if test="params.corpinfoId != null">
                and u.corpinfo_id = #{params.corpinfoId}
            </if>
            <if test="params.departmentId != null">
                and u.department_id = #{params.departmentId}
            </if>
            <if test="params.employmentFlag != null">
                and u.employment_flag = #{params.employmentFlag}
            </if>
            <if test="params.flowFlag != null">
                and u.flow_flag = #{params.flowFlag}
            </if>
            <if test="params.rzFlag != null">
                and u.rz_flag = #{params.rzFlag}
            </if>


            <if test="params.postId != null">
                and u.post_id = #{params.postId}
            </if>
            <if test="params.userIdList != null and params.userIdList.size() > 0">
                and u.id in
                <foreach item="item" index="index" collection="params.userIdList" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
        </where>
        order by u.sort ,
        u.create_time desc
    </select>

    <select id="selectUserPage" resultType="com.zcloud.basic.info.persistence.dataobject.UserDO">
        select
            d.name as department_name,
            CASE
                WHEN c.type IN (3,4, 5) THEN u.post_name
                ELSE p.post_name
                END AS post_name,
            u.id,
            u.user_id,
            u.username,
            u.name,
            u.corpinfo_id,
            u.main_corp_flag,
            u.user_type,
            u.department_id,
            u.post_id,
            u.role_id,
            u.email,
            u.phone,
            u.personnel_type,
            u.personnel_type_name,
            u.nation,
            u.nation_name,
            u.user_id_card,
            u.user_avatar_url,
            u.current_address,
            u.location_address,
            u.rank_level,
            u.rank_level_name,
            u.sort,
            u.version,
            u.create_id,
            u.create_name,
            u.create_time,
            u.update_id,
            u.update_name,
            u.update_time,
            u.remarks,
            u.delete_enum,
            u.tenant_id,
            u.org_id,
            u.env,
            u.department_leader_flag,
            u.deputy_leader_flag,
            u.cultural_level,
            u.cultural_level_name,
            u.marital_status,
            u.marital_status_name,
            u.political_affiliation,
            u.political_affiliation_name,
            u.employment_flag,
            u.flow_flag,
            CASE
                WHEN LENGTH(FROM_BASE64(u.user_id_card)) > 0
                    AND MOD(SUBSTRING(FROM_BASE64(u.user_id_card), 17, 1), 2) = 1 THEN
                    '男'
                WHEN LENGTH(FROM_BASE64(u.user_id_card)) > 0
                    AND MOD(SUBSTRING(FROM_BASE64(u.user_id_card), 17, 1), 2) = 0 THEN
                    '女'
                END AS sex,

            CASE
                WHEN LENGTH(FROM_BASE64(u.user_id_card)) > 0 THEN
                    (YEAR(NOW()) - SUBSTRING(FROM_BASE64(u.user_id_card), 7, 4)) ELSE NULL
                END AS age,

            cast(substring(FROM_BASE64(u.user_id_card), 7, 8) AS DATE) AS birthday
        from user u
        left join corp_info c on c.id = u.corpinfo_id
        left join department d on d.id = u.department_id
        left join post p on p.id = u.post_id
        ${ew.customSqlSegment}
    </select>
    <select id="pageByNopermission" resultType="com.zcloud.basic.info.persistence.dataobject.UserDO">
        select
            d.name as department_name,
            CASE
                WHEN c.type IN (3,4, 5) THEN u.post_name
                ELSE p.post_name
                END AS post_name,
            c.corp_name as corpinfoName,
            u.id,
            u.user_id,
            u.username,
            u.name,
            u.corpinfo_id,
            u.main_corp_flag,
            u.user_type,
            u.department_id,
            u.post_id,
            u.role_id,
            u.email,
            u.phone,
            u.personnel_type,
            u.personnel_type_name,
            u.nation,
            u.nation_name,
            u.user_id_card,
            u.user_avatar_url,
            u.current_address,
            u.location_address,
            u.rank_level,
            u.rank_level_name,
            u.sort,
            u.version,
            u.create_id,
            u.create_name,
            u.create_time,
            u.update_id,
            u.update_name,
            u.update_time,
            u.remarks,
            u.delete_enum,
            u.tenant_id,
            u.org_id,
            u.env,
            u.department_leader_flag,
            u.deputy_leader_flag,
            u.cultural_level,
            u.cultural_level_name,
            u.marital_status,
            u.marital_status_name,
            u.political_affiliation,
            u.political_affiliation_name,
            u.employment_flag,
            u.flow_flag,
            CASE
                WHEN LENGTH(FROM_BASE64(u.user_id_card)) > 0
                    AND MOD(SUBSTRING(FROM_BASE64(u.user_id_card), 17, 1), 2) = 1 THEN
                    '男'
                WHEN LENGTH(FROM_BASE64(u.user_id_card)) > 0
                    AND MOD(SUBSTRING(FROM_BASE64(u.user_id_card), 17, 1), 2) = 0 THEN
                    '女'
                END AS sex,

            CASE
                WHEN LENGTH(FROM_BASE64(u.user_id_card)) > 0 THEN
                    (YEAR(NOW()) - SUBSTRING(FROM_BASE64(u.user_id_card), 7, 4)) ELSE NULL
                END AS age,

            cast(substring(FROM_BASE64(u.user_id_card), 7, 8) AS DATE) AS birthday
        from user u
                 left join corp_info c on c.id = u.corpinfo_id
                 left join department d on d.id = u.department_id
                 left join post p on p.id = u.post_id
            ${ew.customSqlSegment}
    </select>

    <select id="getInfoById" resultType="com.zcloud.basic.info.persistence.dataobject.UserDO">
        select
        d.name as department_name,
        CASE
        WHEN c.type IN (3,4, 5) THEN u.post_name
        ELSE p.post_name
        END AS post_name,
        c.corp_name corpinfo_name,
        u.id,
        u.password,
        u.user_id,
        u.username,
        u.name,
        u.corpinfo_id,
        u.main_corp_flag,
        u.user_type,
        u.department_id,
        u.post_id,
        u.role_id,
        u.email,
        u.phone,
        u.personnel_type,
        u.personnel_type_name,
        u.nation,
        u.nation_name,
        u.user_id_card,
        u.user_avatar_url,
        u.current_address,
        u.location_address,
        u.rank_level,
        u.rank_level_name,
        u.sort,
        u.version,
        u.create_id,
        u.create_name,
        u.create_time,
        u.update_id,
        u.update_name,
        u.update_time,
        u.remarks,
        u.delete_enum,
        u.tenant_id,
        u.org_id,
        u.env,
        u.department_leader_flag,
        u.deputy_leader_flag,
        u.cultural_level,
        u.cultural_level_name,
        u.marital_status,
        u.marital_status_name,
        u.political_affiliation,
        u.political_affiliation_name,
        u.employment_flag,
        u.flow_flag,
        u.rz_flag,
        CASE
        WHEN LENGTH(FROM_BASE64(u.user_id_card)) > 0
        AND MOD(SUBSTRING(FROM_BASE64(u.user_id_card), 17, 1), 2) = 1 THEN
        '男'
        WHEN LENGTH(FROM_BASE64(u.user_id_card)) > 0
        AND MOD(SUBSTRING(FROM_BASE64(u.user_id_card), 17, 1), 2) = 0 THEN
        '女'
        END AS sex,

        CASE
        WHEN LENGTH(FROM_BASE64(u.user_id_card)) > 0 THEN
        (YEAR(NOW()) - SUBSTRING(FROM_BASE64(u.user_id_card), 7, 4)) ELSE NULL
        END AS age,

        cast(substring(FROM_BASE64(u.user_id_card), 7, 8) AS DATE) AS birthday
        from user u
        left join corp_info c on c.id = u.corpinfo_id
        left join department d on d.id = u.department_id
        left join post p on p.id = u.post_id
        <where>
            u.id = #{id}
        </where>
    </select>

    <select id="getInfoByUserName" resultType="com.zcloud.basic.info.persistence.dataobject.UserDO">
        select
        d.name as department_name,
        CASE
        WHEN c.type IN (3,4, 5) THEN u.post_name
        ELSE p.post_name
        END AS post_name,
        c.corp_name corpinfo_name,
        u.id,
        u.password,
        u.user_id,
        u.username,
        u.name,
        u.corpinfo_id,
        u.main_corp_flag,
        u.user_type,
        u.department_id,
        u.post_id,
        u.role_id,
        u.email,
        u.phone,
        u.personnel_type,
        u.personnel_type_name,
        u.nation,
        u.nation_name,
        u.user_id_card,
        u.user_avatar_url,
        u.current_address,
        u.location_address,
        u.rank_level,
        u.rank_level_name,
        u.sort,
        u.version,
        u.create_id,
        u.create_name,
        u.create_time,
        u.update_id,
        u.update_name,
        u.update_time,
        u.remarks,
        u.delete_enum,
        u.tenant_id,
        u.org_id,
        u.env,
        u.department_leader_flag,
        u.deputy_leader_flag,
        u.cultural_level,
        u.cultural_level_name,
        u.marital_status,
        u.marital_status_name,
        u.political_affiliation,
        u.political_affiliation_name,
        u.employment_flag,
        u.flow_flag,
        u.rz_flag,
        CASE
        WHEN LENGTH(FROM_BASE64(u.user_id_card)) > 0
        AND MOD(SUBSTRING(FROM_BASE64(u.user_id_card), 17, 1), 2) = 1 THEN
        '男'
        WHEN LENGTH(FROM_BASE64(u.user_id_card)) > 0
        AND MOD(SUBSTRING(FROM_BASE64(u.user_id_card), 17, 1), 2) = 0 THEN
        '女'
        END AS sex,

        CASE
        WHEN LENGTH(FROM_BASE64(u.user_id_card)) > 0 THEN
        (YEAR(NOW()) - SUBSTRING(FROM_BASE64(u.user_id_card), 7, 4)) ELSE NULL
        END AS age,

        cast(substring(FROM_BASE64(u.user_id_card), 7, 8) AS DATE) AS birthday
        from user u
        left join corp_info c on c.id = u.corpinfo_id
        left join department d on d.id = u.department_id
        left join post p on p.id = u.post_id
        <where>
            <if test="params.corpinfoId != null and params.corpinfoId != ''">
                and u.corpinfo_id = #{params.corpinfoId}
            </if>
            and u.username = #{params.username}
            and u.delete_enum = 'FALSE'
        </where>
        order by u.create_time desc
        limit 1
    </select>
     <select id="getDepartmentLeaderByDepartmentId" resultType="com.zcloud.basic.info.persistence.dataobject.DepartmentLeaderStatictiscDO">
        select
        u.department_id AS departmentId,
        GROUP_CONCAT(DISTINCT u.name) AS departmentLeaderNameList
        from user u
        where
        u.delete_enum = 'FALSE'
        and u.department_leader_flag = '1'
        and u.department_id in
        <foreach item="item" index="index" collection="departmentIds" open="(" separator="," close=")">
            #{item}
        </foreach>
        group by u.department_id
    </select>


    <select id="countUser" resultType="Integer">
        select count(1)
        from user
        <where>
            <if test="params.id != null">
                and id != #{params.id}
            </if>
            <if test="params.username != null">
                and username = #{params.username}
            </if>
            <if test="params.userIdCard != null">
                and user_id_card = #{params.userIdCard}
            </if>
            <if test="params.phone != null">
                and phone = #{params.phone}
            </if>
        </where>
    </select>

    <select id="listXgf" resultType="com.zcloud.basic.info.persistence.dataobject.UserDO">
        SELECT
            u.id AS id,
        u.username AS username,
        u.name AS name,
        u.main_corp_flag AS mainCorpFlag,
        u.user_type AS userType,

        u.post_id AS postId,
        u.role_id AS roleId,
        u.email AS email,
        u.phone AS phone,
        u.personnel_type AS personnelType,
        u.personnel_type_name AS personnelTypeName,
        u.nation AS nation,
        u.nation_name AS nationName,
        u.user_id_card AS userIdCard,
        u.user_avatar_url AS userAvatarUrl,
        u.current_address AS currentAddress,
        u.location_address AS locationAddress,
        u.rank_level AS rankLevel,
        u.rank_level_name AS rankLevelName,
        u.sort AS sort,
        u.department_leader_flag AS departmentLeaderFlag,
        u.flow_flag AS flowFlag,
        u.employment_flag                AS employmentFlag,
        ud.name AS departmentName,
        cu.corp_name AS corpName,
        u.department_id AS departmentId,
        u.corpinfo_id                 AS corpinfoId
        FROM
        user u
        LEFT JOIN department ud ON u.department_id = ud.id
        LEFT JOIN corp_info cu ON u.corpinfo_id = cu.id
        WHERE
        u.id != u.corpinfo_id
        AND u.delete_enum = 'FALSE'
        <if test="params.eqCorpinfoId != null">
            AND u.corpinfo_id = #{params.eqCorpinfoId} and u.employment_flag !='3'
        </if>
        <if test="params.eqDepartmentId != null and params.eqDepartmentId != ''">
            AND u.department_id = #{params.eqDepartmentId}
        </if>
        <if test="params.inDepartmentId != null and params.inDepartmentId.length > 0">
            u.department_id IN
            <foreach collection="params.inDepartmentId" item="departmentId" open="(" close=")" separator=",">
                #{departmentId}
            </foreach>
        </if>
        <if test="params.likeName != null and params.likeName != ''">
            AND u.name LIKE CONCAT('%', #{params.likeName}, '%')
        </if>
        <if test="params.likeUserIdCard != null and params.likeUserIdCard != ''">
            AND u.user_id_card LIKE CONCAT('%', #{params.likeUserIdCard}, '%')
        </if>
        <if test="params.eqEmploymentFlag != null and params.eqEmploymentFlag != ''">
            AND u.employment_flag = #{params.eqEmploymentFlag}
        </if>

        ORDER BY CASE
        WHEN u.employment_flag IN ('10', '11') THEN 1
        ELSE 2
        END ASC,
        u.sort ASC, u.create_time DESC


    </select>
    <select id="getListByUserIds" resultType="com.zcloud.basic.info.persistence.dataobject.UserCorpInfoDO">
        select u.id as userId, ci.corp_name as corpName,ci.id as corpinfoId
        from user u
        left join corp_info ci on u.corpinfo_id = ci.id
        where u.id in
        <foreach collection="userIds" item="item" open="(" close=")" separator=",">
            #{item}
        </foreach>
        and ci.use_flag=1
        and u.employment_flag =1
        and u.delete_enum = 'FALSE'
    </select>
    <update id="deletedUserByPhone">
        update user set delete_enum = 'TRUE' where phone = #{phone}
    </update>
    <select id="getUserIdByPhone"  resultType="com.zcloud.basic.info.persistence.dataobject.UserDO">
        select * from user where phone =  #{phone} limit 1;
    </select>
    <update id="updateFaceByPhone">
        update user set user_avatar_url = #{userAvatarUrl} where phone = #{phone}
    </update>
</mapper>

