<?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.sop.business.mapper.SysUserMapper">

    <select id="findProcessDefinitionIdByDeploymentId" resultType="java.lang.String">
        select ARP.ID_ from ACT_RE_PROCDEF ARP where ARP.DEPLOYMENT_ID_ = #{deploymentId}
    </select>

    <sql id="flowTaskListCommonSql">
        (select
        distinct cast(art.ASSIGNEE_ as Decimal(24)) as userId,
        tfcf.type,
        tfcfe.code,
        tffa.status,
        tffa.setup,
        tfcf.version,
        tfcfe.obj_id as objId,
        tfcfe.crm_no as crmNo,
        tbc.name as crmName,
        tbs.id as serviceId,
        tbs.name as serviceName,
        tbs.type as serviceType,
        sc.id as customId,
        sc.type as customType,
        sc.name as customName,
        tfcfe.create_id as createId,
        su.real_name as createRealName,
        art.PROC_INST_ID_ as flowId,
        IFNULL(art.NAME_,'--') as taskName,
        art.TASK_DEF_KEY_ as taskDefKey,
        art.ID_ as taskId,
        tfcfe.flow_process_var as flowProcessVar,
        tfcfe.t_f_custom_flow_id as customFlowId,
        tfcf.flow_deployment_id as flowDeploymentId,
        tfcfe.id as customFlowEntityid,
        tffa.update_time as flowTime,
        datediff(now(),FROM_UNIXTIME(tffa.update_time / 1000,'%Y-%m-%d %H:%i:%s')) as diffTime
        from
        ACT_RU_TASK art
        join t_f_flow_approve tffa on art.PROC_INST_ID_ = tffa.flow_id
        left join t_f_custom_flow_entity tfcfe on tfcfe.flow_id = tffa.flow_id
        left join t_f_custom_flow tfcf on tfcf.id = tfcfe.t_f_custom_flow_id
        left join t_b_crm tbc on tbc.crm_no = tfcfe.crm_no
        left join t_b_service tbs on tbs.id = tbc.service_id
        left join sys_custom sc on sc.id = tbc.custom_id
        left join sys_user su ON tfcfe.create_id = su.id
        <where> 1 = 1
            and tbs.status!='CANCEL'
            and art.ASSIGNEE_ = cast(#{userId} as char)
            <if test="flowTaskTypeEnum != null and flowTaskTypeEnum != '' and flowTaskTypeEnum == 'DRAFT'">
                and tffa.status = #{flowTaskTypeEnum}
            </if>
            <if test="type != null and type != ''">
                <choose>
                    <when test="type == 'SOP_FLOW'">
                        and (tfcf.type = 'OFFICE_SOP_FLOW' or tfcf.type = 'CLOUD_MARK_SOP_FLOW')
                    </when>
                    <otherwise>
                        and tfcf.type = #{type}
                    </otherwise>
                </choose>
            </if>
            <if test="serviceId != null and serviceId != ''">
                and tbs.id = #{serviceId}
            </if>
            <if test="customName != null and customName != ''">
                and sc.name like concat('%', #{customName}, '%')
            </if>
            <if test="crmNo != null and crmNo != ''">
                and tbc.crm_no = #{crmNo}
            </if>
        </where>
        UNION ALL
        select
        distinct cast(ari.USER_ID_ as Decimal(24)) as userId,
        tfcf.type,
        tfcfe.code,
        tffa.status,
        tffa.setup,
        tfcf.version,
        tfcfe.obj_id as objId,
        tfcfe.crm_no as crmNo,
        tbc.name as crmName,
        tbs.id as serviceId,
        tbs.name as serviceName,
        tbs.type as serviceType,
        sc.id as customId,
        sc.type as customType,
        sc.name as customName,
        tfcfe.create_id as createId,
        su.real_name as createRealName,
        art.PROC_INST_ID_ as flowId,
        IFNULL(art.NAME_,'--') as taskName,
        art.TASK_DEF_KEY_ as taskDefKey,
        art.ID_ as taskId,
        tfcfe.flow_process_var as flowProcessVar,
        tfcfe.t_f_custom_flow_id as customFlowId,
        tfcf.flow_deployment_id as flowDeploymentId,
        tfcfe.id as customFlowEntityid,
        tffa.update_time as flowTime,
        datediff(now(),FROM_UNIXTIME(tffa.update_time / 1000,'%Y-%m-%d %H:%i:%s')) as diffTime
        from
        ACT_RU_TASK art
        left join act_ru_identitylink ari on ari.TASK_ID_ = art.ID_
        join t_f_flow_approve tffa on tffa.flow_id = art.PROC_INST_ID_
        left join t_f_custom_flow_entity tfcfe on tfcfe.flow_id = tffa.flow_id
        left join t_f_custom_flow tfcf on tfcf.id = tfcfe.t_f_custom_flow_id
        left join t_b_crm tbc on tbc.crm_no = tfcfe.crm_no
        left join t_b_service tbs on tbs.id = tbc.service_id
        left join sys_custom sc on sc.id = tbc.custom_id
        left join sys_user su ON tfcfe.create_id = su.id
        <where> 1 = 1
            and tbs.status!='CANCEL'
            and art.ASSIGNEE_ is null
            and ari.USER_ID_ = cast(#{userId} as char)
            and ari.TYPE_ = 'candidate'
            <if test="flowTaskTypeEnum != null and flowTaskTypeEnum != '' and flowTaskTypeEnum == 'DRAFT'">
                and tffa.status = #{flowTaskTypeEnum}
            </if>
            <if test="type != null and type != ''">
                <choose>
                    <when test="type == 'SOP_FLOW'">
                        and (tfcf.type = 'OFFICE_SOP_FLOW' or tfcf.type = 'CLOUD_MARK_SOP_FLOW')
                    </when>
                    <otherwise>
                        and tfcf.type = #{type}
                    </otherwise>
                </choose>
            </if>
            <if test="serviceId != null and serviceId != ''">
                and tbs.id = #{serviceId}
            </if>
            <if test="customName != null and customName != ''">
                and sc.name like concat('%', #{customName}, '%')
            </if>
            <if test="crmNo != null and crmNo != ''">
                and tbc.crm_no = #{crmNo}
            </if>
        </where>
        ) t
        <where>
            <if test="flowTaskTypeEnum != null and flowTaskTypeEnum != '' and flowTaskTypeEnum == 'OVER_TIME'">
                and t.diffTime <![CDATA[ >= ]]> 1
            </if>
        </where>
    </sql>

    <select id="getFlowTaskList" resultType="com.qmth.sop.business.bean.result.WorkTaskResult">
        select t.* from
        <include refid="flowTaskListCommonSql" /> order by t.type,t.code desc,t.flowTime desc
    </select>

    <select id="getFlowTaskListCount" resultType="java.lang.Integer">
        select count(1) from
        <include refid="flowTaskListCommonSql" />
    </select>

    <select id="findSysUserResultList" resultType="com.qmth.sop.business.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,
        group_concat(CONCAT(sr.id, "," ,sr.name,"," ,sr.type) 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="orgId != null and orgId != ''">
                and su.org_id = #{orgId}
            </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>

    <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="findVerifyCodeByUser" resultType="com.qmth.sop.business.bean.dto.VerifyCodeCheckDto">
        SELECT
        id,
        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="query" resultType="com.qmth.sop.business.bean.dto.UserDto">
        SELECT
        a.id,
        a.login_name loginName,
        a.real_name realName,
        a.mobile_number mobileNumber,
        a.gender,
        a.enable,
        a.remark,
        a.org_id orgId,
        b.name orgName
        FROM sys_user a
        left join sys_org b on a.org_id = b.id
        <where>
            <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>
            <if test="userInfo == null or userInfo == ''">
                and a.source = 'SYSTEM'
            </if>
        </where>
        order by a.update_time desc
    </select>

    <select id="queryUser" resultType="com.qmth.sop.business.bean.dto.FormWidgetDto">
        select t.value,
            (select group_concat(t.realName, '(', sr.name, ')' SEPARATOR ';')
            from sys_role sr
            where FIND_IN_SET(sr.id, roleIds)) as 'label'
        from
            (select su.id as 'value',su.real_name as realName,
            (select group_concat(sur.role_id)
            from sys_user_role sur
            where sur.user_id = su.id
            <if test="roleIdList != null and roleIdList != '' and roleIdList.size() > 0">
                and sur.role_id in
                <foreach collection="roleIdList" item="item" index="index" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
                ) as roleIds
        from sys_user su
            <where> 1 = 1 and su.id <![CDATA[ <> ]]> 1
                <if test="userSourceEnum != null and userSourceEnum != '' and userSourceEnum != 'ALL'">
                        and su.source = #{userSourceEnum}
                </if>
            </where>) t where t.roleIds is not null
    </select>

    <select id="selectRoleNames" resultType="com.qmth.sop.business.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="findByRoleType" resultType="com.qmth.sop.business.bean.result.SysUserBriefResult">
        SELECT
            su.id AS id,
            su.login_name AS loginName,
            su.real_name AS realName,
            su.mobile_number AS mobileNumber
        FROM
            sys_user_role sur
                LEFT JOIN
            sys_role sr ON sur.role_id = sr.id
                LEFT JOIN
            sys_user su ON sur.user_id = su.id
        <where>
            <if test="roleType != null">
                AND sr.type = #{roleType}
            </if>
        </where>
    </select>

    <select id="findOrgUserName" resultType="com.qmth.sop.business.bean.dto.OrgUserNameDto">
        select
            su.id,
            IF(so.name is null,su.real_name,concat(su.real_name,'(',so.name,')')) as orgUserName
        from
            sys_user su
            left join sys_org so on so.id = su.org_id
        <where> 1 = 1
            <if test="userIds != null and userIds != '' and userIds.size > 0">
                AND su.id IN
                <foreach collection="userIds" item="item" index="index" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
        </where>
    </select>
</mapper>
