<?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.dk.mdm.mapper.core.UserMapper">

    <!-- 通用设置 -->
    <!-- 通用查询列 -->
    <sql id="Base_Column_List">
        tu.user_id, tu.user_code, tu.user_name, tu.user_pwd, tu.flg_init_pwd, tu.pwd_date, tu.user_type, tu.client_id, tu.role_ids, tu.org_id, tu.staff_id, tu.remarks,
        tu.flg_valid, tu.fty_id, tu.op_create_time, tu.op_create_user_id, tu.op_update_time, tu.op_update_user_id, tu.op_app_code, tu.op_timestamp, tu.op_db_user
    </sql>

    <sql id="select">
        SELECT tu.user_id,
               tu.user_code,
               tu.user_name,
               tu.pwd_date,
               tu.client_id,
               tu.role_ids,
               tu.org_id,
               tu.staff_id,
               tu.remarks,
               tu.fty_id,
               org.org_name,
               ts.staff_name,
               tf.fty_code,
               tf.fty_name,
               tu.op_app_code AS app_code,
               tu.flg_valid
    </sql>

    <sql id="selectTable">
        FROM core.t_user tu
        LEFT JOIN core.t_organization org ON tu.org_id = org.org_id
        LEFT JOIN core.t_staff ts ON tu.staff_id = ts.staff_id
        LEFT JOIN core.t_factory tf ON tu.fty_id = tf.fty_id
    </sql>

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.dk.common.model.response.core.UserResponse">
        <id column="user_id" property="userId"/>
        <result column="user_code" property="userCode"/>
        <result column="user_name" property="userName"/>
        <result column="user_pwd" property="userPwd"/>
        <result column="flg_init_pwd" property="flgInitPwd"/>
        <result column="pwd_date" property="pwdDate"
                typeHandler="com.dk.common.infrastructure.handler.TimestampTypeHandler"/>
        <result column="user_type" property="userType"/>
        <result column="user_type_name" property="userTypeName"/>
        <result column="client_id" property="clientId"/>
        <result column="role_ids" property="roleIds"
                typeHandler="com.dk.common.infrastructure.handler.IntListTypeHandler"/>
        <result column="role_name" property="roleName"/>
        <result column="org_id" property="orgId"/>
        <result column="staff_id" property="staffId"/>
        <result column="org_name" property="orgName"/>
        <result column="org_level_name" property="orgLevelName"/>
        <result column="staff_name" property="staffName"/>
        <result column="remarks" property="remarks"/>
        <result column="flg_valid" property="flgValid"/>
        <result column="fty_id" property="ftyId"/>
        <result column="is_locked" property="flgLocked"/>
        <result column="op_create_time" property="opCreateTime"
                typeHandler="com.dk.common.infrastructure.handler.TimestampTypeHandler"/>
        <result column="op_create_user_id" property="opCreateUserId"/>
        <result column="op_update_time" property="opUpdateTime"
                typeHandler="com.dk.common.infrastructure.handler.TimestampTypeHandler"/>
        <result column="op_update_user_id" property="opUpdateUserId"/>
        <result column="op_app_code" property="opAppCode"/>
        <result column="op_timestamp" property="opTimestamp"
                typeHandler="com.dk.common.infrastructure.handler.TimestampTypeHandler"/>
        <result column="op_db_user" property="opDbUser"/>
    </resultMap>

    <resultMap id="BaseResultMapWithPwd" type="com.dk.common.model.response.core.UserResponse">
        <id column="user_id" property="userId"/>
        <result column="user_code" property="userCode"/>
        <result column="user_name" property="userName"/>
        <result column="user_pwd" property="userPwd"/>
        <result column="flg_init_pwd" property="flgInitPwd"/>
        <result column="pwd_date" property="pwdDate"
                typeHandler="com.dk.common.infrastructure.handler.TimestampTypeHandler"/>
        <result column="user_type" property="userType"/>
        <result column="client_id" property="clientId"/>
        <result column="role_ids" property="roleIds"
                typeHandler="com.dk.common.infrastructure.handler.IntListTypeHandler"/>
        <result column="org_id" property="orgId"/>
        <result column="staff_id" property="staffId"/>
        <result column="remarks" property="remarks"/>
        <result column="flg_valid" property="flgValid"/>
        <result column="fty_id" property="ftyId"/>
        <result column="op_create_time" property="opCreateTime"
                typeHandler="com.dk.common.infrastructure.handler.TimestampTypeHandler"/>
        <result column="op_create_user_id" property="opCreateUserId"/>
        <result column="op_update_time" property="opUpdateTime"
                typeHandler="com.dk.common.infrastructure.handler.TimestampTypeHandler"/>
        <result column="op_update_user_id" property="opUpdateUserId"/>
        <result column="op_app_code" property="opAppCode"/>
        <result column="app_code" property="appCode"/>
        <result column="op_timestamp" property="opTimestamp"
                typeHandler="com.dk.common.infrastructure.handler.TimestampTypeHandler"/>
        <result column="op_db_user" property="opDbUser"/>
        <result column="org_name" property="orgName"/>
        <result column="staff_name" property="staffName"/>
        <result column="fty_code" property="ftyCode"/>
        <result column="fty_name" property="ftyName"/>
    </resultMap>

    <!-- 通用条件列 -->
    <sql id="Condition">
        <where>
            <if test="userCode != null and userCode != ''">
                And position (#{userCode} in tu.user_code)>0
            </if>
            <if test="userName != null and userName != ''">
                And position (#{userName} in tu.user_name)>0
            </if>
            <if test="userType != null">
                AND tu.user_type = #{userType}
            </if>
            <if test="clientId != null and clientId != ''">
                AND tu.client_id = #{clientId}
            </if>
            <if test="orgId != null">
                AND tu.org_id = #{orgId}
            </if>
            <if test="staffId != null">
                AND tu.staff_id = #{staffId}
            </if>
            <if test="remarks != null and remarks != ''">
                AND tu.remarks = #{remarks}
            </if>
            <if test="flgValid != null">
                AND tu.flg_valid = #{flgValid}
            </if>
            <if test="ftyId != null">
                AND tu.fty_id = #{ftyId}
            </if>
            <if test="userId != null">
                AND tu.user_id = #{userId}
            </if>
            <if test="flgValidList != null and flgValidList.size()>0">
                AND tu.flg_valid =
                any(#{flgValidList, typeHandler=BooleanListTypeHandler})
            </if>
            <if test="orgIds != null and orgIds.size()>0">
                AND tu.org_id =
                any(#{orgIds, typeHandler=IntListTypeHandler})
            </if>
            <if test="roleIds != null and roleIds.size()>0">
                AND tu.role_ids &amp;&amp; #{roleIds, typeHandler=IntListTypeHandler}
            </if>
        </where>
    </sql>

    <!-- 查询表t_user，（条件查询+分页）列表 -->
    <select id="selectByCond" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List"/> ,org.org_name, ts.staff_name, case when tu.user_type = 1 then '文员' when
        tu.user_type = 2 then '生产工号（班组）' else '接口用户' end user_type_name,org.level_name AS org_level_name,
        (select string_agg( tr.role_name,' | ')
        from core.t_role as tr
        where tr.role_id = any(tu.role_ids)
        ) as role_name, tul.is_locked
        FROM core.t_user AS tu
        LEFT JOIN core.t_organization org ON tu.org_id = org.org_id
        LEFT JOIN core.t_staff ts ON tu.staff_id = ts.staff_id
        LEFT JOIN core.t_user_locked tul ON tul.user_id = tu.user_id
        <include refid="Condition"/>
        ORDER BY tu.flg_valid DESC,tu.user_code
        <if test="pageSize != null and currentPage != null and pageSize != 0 and currentPage != 0">
            limit #{end} offset #{start}
        </if>
    </select>

    <!-- 查询表t_user，（条件查询）个数 -->
    <select id="countByCond" resultType="Long">
        SELECT
        count(1)
        FROM core.t_user AS tu
        <include refid="Condition"/>
    </select>

    <!-- 根据编码查询用户表 -->
    <select id="selectByCode" resultMap="BaseResultMapWithPwd">
        <include refid="select"/>, tu.user_pwd
        <include refid="selectTable"/>
        WHERE tu.user_code = #{userCode}
        AND tf.fty_code = #{ftyCode}
        AND tu.flg_valid
    </select>

    <!--    用户密码加密-->
    <select id="encodePassword" resultType="java.lang.String">
        select core.f_encrypt_user_pwd(#{userPwd}, #{userId});
    </select>

    <!-- 根据Id查询用户表 -->
    <select id="selectById" resultMap="BaseResultMap">
        <include refid="select"/>,
        org.org_name, ts.staff_name, case when tu.user_type = 1 then '文员' when
        tu.user_type = 2 then '生产工号（班组）' else '接口用户' end user_type_name,org.level_name AS org_level_name,
        (select string_agg( tr.role_name,' | ')
        from core.t_role as tr
        where tr.role_id = any(tu.role_ids)
        ) as role_name
        <include refid="selectTable"/>
        where tu.user_id = #{id}
    </select>

    <!-- 更新登录状态 -->
    <update id="updateUserLogin">
        update core.t_user_login
        <set>
            <if test="isLogin != null" >
                is_login=#{isLogin},
            </if>
            <if test="tokenKey != null" >
                token_key=#{tokenKey},
            </if>
            <if test="tokenInvalidTime != null" >
                token_invalid_time=#{tokenInvalidTime},
            </if>
        </set>
        where user_id = #{userId} and app_code = #{appCode}
    </update>

    <!-- 插入登录履历 -->
    <insert id="insertUserLoginLog">
        insert into core.t_user_login_log
        (
            user_id,
            app_code,
            login_type,
            client_ip,
            client_info
        )
        values
       (
           #{userId},
           #{appCode},
           #{loginType},
           #{clientIp},
           #{clientInfo}
       )
    </insert>
    <select id="selectUserWithPwdById" resultMap="BaseResultMapWithPwd">
        <include refid="select"/>,user_pwd
        <include refid="selectTable"/>
        where tu.user_id = #{id}
    </select>

    <!-- 修改密码 -->
    <update id="updatePassword">
        update core.t_user
        <set>
            <if test="pwdDate != null">
                pwd_date = #{pwdDate},
            </if>
            <if test="flgInitPwd != null">
                flg_init_pwd = #{flgInitPwd},
            </if>
            <if test="userPwd != null">
                user_pwd = #{userPwd},
            </if>
        </set>
            where user_id = #{userId}
    </update>

    <!--用户编码是否重复-->
    <select id="selectUserByCode" resultType="Long">
        select count(1)
        from core.t_user
        where #{userCode} = user_code and fty_id = #{ftyId}
        <if test="userId != null and userId != 0">
            and user_id != #{userId}
        </if>
    </select>

    <!-- 解锁 -->
    <update id="unLock">
        update core.t_user_locked
        <set>
            is_locked = false ,
            locked_time = null
        </set>
        where user_id = #{userId}
    </update>

    <!--获取用户权限-->
    <select id="getUserRight" resultType="Map">
        select core.f_get_user_fun_for_edit(#{userId}, NULL)
    </select>

    <!--获取已选的用户权限-->
    <select id="getUserRightSelected" resultType="Map">
        select tur.fun_uuid,
               apm.menu_uuid AS "id",
               apm.parent_uuid AS "parentId",
               sys.f_get_name_i18n_lang(apm.menu_name_i18n,#{i18n}) AS "menuName"
        from core.t_user_right tur
         inner join sys.t_function tf
                    on tur.fun_uuid = tf.fun_uuid
         inner join sys.t_app_menu apm
                    on tur.fun_uuid = apm.fun_uuid
        where tur.user_id = #{userId}
    </select>

    <select id="getUserRightCurrent" resultType="Map">
        select tur.fun_uuid as "funUuid",
               sys.f_get_name_i18n_lang(apm.menu_name_i18n,#{i18n}) AS "menuName"
        from core.t_user_right tur
                 inner join sys.t_function tf
                            on tur.fun_uuid = tf.fun_uuid
                 inner join sys.t_app_menu apm
                            on tur.fun_uuid = apm.fun_uuid
        where tur.user_id = #{userId} and tur.right_type = 1
    </select>

    <!--批量插入班组信息-->
    <insert id="insertWorkTeamBatch">
        insert into core.t_work_team
        (
        <trim suffixOverrides=",">
            user_id,
            wt_job_id,
            staff_ids,
            staff_id,
            display_no,
            remarks,
            fty_id,
        </trim>
        )
        values
        <foreach collection="list" index="index" item="item" separator=",">
            (
            <trim suffixOverrides=",">
                #{item.userId},
                #{item.wtJobId},
                #{item.staffIds,typeHandler=IntListTypeHandler},
                #{item.staffId},
                #{item.displayNo},
                #{item.remarks},
                #{item.ftyId},
            </trim>
            )
        </foreach>
    </insert>

    <!--删除班组信息-->
    <delete id="deleteWorkTeam" >
        delete from core.t_work_team where user_id= #{userId} and fty_id = #{ftyId}
    </delete>

    <!--获取用户班组信息-->
    <select id="getUserWorkTeamById" resultType="Map">
        select team.team_id AS "teamId",team.wt_job_id AS "jobId",job.job_name AS "jobId_Name",
               team.remarks,team.staff_ids AS "staffIds",team.staff_id AS "staffId",
               (select json_agg(t.*) from (select ts.staff_code AS "staffCode",ts.staff_name AS "staffName",
                                                  concat('[',ts.staff_code, ']',ts.staff_name) as "codeName",
                                                  ts.staff_id AS "staffId"
               ,case when ts.staff_id = team.staff_id then true else false end "flgDefaultStaff"
               from core.t_staff ts where ts.staff_id = ANY(team.staff_ids)) t) AS "staffList"
          from core.t_work_team AS team
          left join core.t_job AS job
            on team.wt_job_id = job.job_id
         where team.user_id = #{userId}
    </select>

    <!--删除用户权限-->
    <delete id="deleteUserRight" >
        delete from core.t_user_right where user_id= #{userId} and fty_id = #{ftyId}
    </delete>

    <!--保存用户权限-->
    <insert id="saveUserRight">
        insert into core.t_user_right
        (
        <trim suffixOverrides=",">
            user_id,
            fun_uuid,
            right_type,
            app_code,
            fty_id,
        </trim>
        )
        values
        <foreach collection="list" index="index" item="item" separator=",">
            (
            <trim suffixOverrides=",">
                #{item.userId},
                #{item.funUuid}::uuid,
                #{item.rightType},
                #{item.appCode},
                #{item.ftyId},
            </trim>
            )
        </foreach>
    </insert>

    <!--更新用户权限-->
    <select id="resetUserFun" resultType="boolean">
        select core.f_reset_user_fun(#{userId})
    </select>

    <!--获取用户班组信息-->
    <select id="getWorkTeamByUserId" resultType="Map">
        <include refid="getWorkTeamSelect"/>
    </select>

    <!--获取用户班组信息-->
    <select id="getWorkTeamByMap" resultType="Map">
        <include refid="getWorkTeamSelect"/>
    </select>

    <sql id="getWorkTeamSelect">
        select team.team_id AS "teamId",team.wt_job_id AS "jobId",job.job_name AS "jobName",
               team.remarks,team.staff_ids AS "staffIds",team.staff_id AS "staffId",sys.f_code_name(staff.staff_code,staff.staff_name) as "staffName",
               (select string_agg(sys.f_code_name(ts.staff_code,concat(ts.staff_name,case when ts.staff_id = team.staff_id
                                                                                        then '*' else null end)),' | ')
                from core.t_staff as ts
                where ts.staff_id = any(team.staff_ids)
               ) as "staffNames"
        from core.t_work_team AS team
                 left join core.t_job AS job
                           on team.wt_job_id = job.job_id
                 left join core.t_staff AS staff
                           on team.staff_id = staff.staff_id
        where team.user_id = #{userId}
    </sql>

    <!-- 批量修改用户密码 -->
    <update id="updateUsersPwd" >
        update core.t_user
        <set>
            <if test="pwdDate != null">
                pwd_date = #{pwdDate},
            </if>
            <if test="flgInitPwd != null">
                flg_init_pwd = #{flgInitPwd},
            </if>
            <if test="userPwd != null">
                user_pwd = #{userPwd},
            </if>
        </set>
        where user_id = #{userId}
    </update>

    <!-- 批量解锁用户-->
    <update id="unLocks" >
        update core.t_user_locked
        <set>
            is_locked = false ,
            miss_count = 0,
            locked_time = null
        </set>
        where user_id = any(#{userIds,typeHandler=com.dk.common.infrastructure.handler.IntListTypeHandler})
    </update>

    <!-- 更新用户启用状态-->
    <update id="updateEnable">
        update core.t_user
        <set>
            flg_valid = #{flgValid}
        </set>
        where user_id = #{userId}
    </update>

    <!--新建时，通过序列获取用户id-->
    <select id="selectUserIdBySeq" resultType="Integer">
        select NEXTVAL('core.T_user_user_id_seq')
    </select>

<!--    通过生产工号查询ID-->
    <select id="selectByProdCode" resultType="Integer">
        select user_id from core.t_user
        where user_code = #{userCode} and flg_valid and user_type = 2 and fty_id = #{ftyId}
    </select>
</mapper>
