<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//oos.juyouhx.com//DTD Mapper 3.0//EN" "http://oss.juyouhx.com/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.scpyun.platform.standard.jilinsscgsdp.sys" name="系统表">

    <select id="getJobDuty" parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventInsertVo"
            resultType="String" expose="true">
        SELECT
        IFNULL(job_duty,'') job_duty
        FROM
        sys_user
        WHERE
        id = #{_user.id}
    </select>

    <select id="findAreaNode" parameterType="string" resultType="map">
        select id,name,type,'0' as isAll ,'0' as isValue ,parent_ids as parentIds from sys_area where parent_id = #{parentId}
    </select>

    <select id="findOfficeId" parameterType="string" resultType="string">
        select office_id as officeId from sys_user where id =#{id}
    </select>

    <select id="findOffieNode" parameterType="string" resultType="map">
        select id,name,grade as type,'1' as isAll ,'0' as isValue,parent_ids as parentIds from sys_office where parent_id = #{parentId}
    </select>

    <select id="findAllAreaNode" resultType="com.scpyun.platform.jilinsscgsdp.pojo.Area">
        SELECT
        id id,
        parent_id parentId,
        name name,
        type type,
        sort sort
        FROM
        sys_area
        WHERE
        del_flag = '0'
        ORDER BY
        sort ASC
    </select>

    <!--<select id="getJobDuty" parameterType="map" resultType="String" expose="true">
        SELECT
            IFNULL(job_duty,'') job_duty
        FROM
            sys_user
        WHERE
            id = #{_user.id}
    </select>-->


    <select id="selectUserOfficeId" parameterType="string" resultType="string">
        select office_id from sys_user where id = #{id}
    </select>

    <select id="getOfficeIdByCode" parameterType="map" resultType="String">
        SELECT
        id
        FROM
        sys_office
        WHERE
        code = #{_user.company_code}
    </select>

    <select id="getOfficeByCompany" parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventFlowLogInsertVo"
            resultType="String">
        SELECT
        id
        FROM
        sys_office
        WHERE
        del_flag = '0'
        AND id = #{_user.parent_id}
    </select>

    <select id="getHierarchy" parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqSysOfficeVo"
            resultType="Integer">
        SELECT
        CASE grade
        WHEN 2 THEN
        IF(fax IS NOT NULL OR fax != '',6,7)
        WHEN 3 THEN
        IF(fax IS NOT NULL OR fax != '',6,7)
        WHEN 4 THEN
        IF(fax IS NOT NULL OR fax != '',4,5)
        WHEN 5 THEN
        3
        WHEN 6 THEN
        2
        ELSE 0
        END hierarchy
        FROM
        sys_office
        WHERE
        id = #{officeId}
    </select>

    <select id="selectDictList" parameterType="map" resultType="map" name="查询字典" desc="查询字典" expose="true" nologin="true">
        SELECT
        id,
        value,
        label
        FROM
        sys_dict
        WHERE
        del_flag = '0'
        AND type = #{type}
        <if test="parent_id != null and parent_id != ''">
            AND parent_id = #{parent_id}
        </if>
        <if test="parent_id == null or parent_id == ''">
            AND parent_id = '0'
        </if>
        ORDER BY sort
    </select>

    <select id="getAreaCode" parameterType="map" resultType="String">
        SELECT
        area_id
        FROM
        sys_office
        WHERE
        grade = '3'
        AND FIND_IN_SET(parent_ids,#{_user.company_id})
    </select>

    <select id="selectCommunityCode" parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventInsertVo"
            resultType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventInsertVo" expose="true">
        SELECT
        sc3.area_id AS cityId,
        sc2.area_id AS areaId,
        sc1.area_id AS streetId,
        sc.area_id AS communityId
        FROM
        sys_office sc
        LEFT JOIN sys_office sc1 on sc.parent_id = sc1.id
        LEFT JOIN sys_office sc2 on sc1.parent_id = sc2.id
        LEFT JOIN sys_office sc3 on sc2.parent_id = sc3.id
        WHERE
        sc.id = #{_user.company_id}
    </select>

    <select id="getOfficeCode" parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqSysOfficeVo"
            resultType="string">
        SELECT
        CONCAT(#{_user.company_sys_area, jdbcType=VARCHAR},LPAD(right(ifnull(max(code),0),5)+1,5,'0')) code
        FROM
        sys_office
        WHERE
        code LIKE CONCAT(#{_user.company_sys_area, jdbcType=VARCHAR},'_____')
    </select>

    <select id="getAreaId" parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqSysOfficeVo" resultType="String">
        SELECT
        area_id
        FROM
        sys_office
        WHERE
        id = #{_user.company_id}
    </select>

    <select id="selectStreetCode" parameterType="map" resultType="map">
        SELECT
        sc2.area_id AS city_id,
        sc1.area_id AS area_id,
        sc.area_id AS street_id
        FROM
        sys_office sc
        LEFT JOIN sys_office sc1 on sc.parent_id = sc1.id
        LEFT JOIN sys_office sc2 on sc1.parent_id = sc2.id
        WHERE
        sc.id = #{_user.company_id}
    </select>

    <select id="selectAreaCode" parameterType="map" resultType="map">
        SELECT
        sc1.area_id AS city_id,
        sc.area_id AS area_id
        FROM
        sys_office sc
        LEFT JOIN sys_office sc1 on sc.parent_id = sc1.id
        WHERE
        sc.id = #{_user.company_id}
    </select>

    <select id="getRoleUser" parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventFlowLogInsertVo"
            resultType="String">
        SELECT
        GROUP_CONCAT(sr.NAME) name
        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
        WHERE
        su.id = #{params._user.id}
        AND job_duty = '1'
    </select>

    <select id="getRoleUserNew" parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventFlowLogInsertVo"
            resultType="String">
        SELECT
        GROUP_CONCAT(sr.NAME) name
        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
        WHERE
        su.id = #{_user.id}
        AND job_duty = '1'
    </select>

    <select id="selectCityList" parameterType="map" resultType="map" name="查询城市层级" desc="查询城市层级"
            expose="true" nologin="true">
        SELECT
        `code` parent_id,
        `name`
        FROM
        sys_area
        WHERE
        <if test="parent_id == null or parent_id == ''">
            parent_id = '220000000000'
        </if>
        <if test="parent_id != null and parent_id != ''">
            parent_id = #{parent_id}
        </if>
        order by sort asc
    </select>

    <select id="selectMenuList" parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.SysMenuVo"
            resultType="com.scpyun.platform.jilinsscgsdp.bean.vo.SysMenuVo">
        SELECT
        sm.id,
        sm.parent_id parentId,
        sm.parent_ids parentIds,
        sm.system_module_code systemModuleCode,
        sm.name,
        sm.sort,
        sm.href,
        sm.target,
        sm.icon,
        sm.is_show isShow,
        sm.menu_permission menuPermission,
        sm.permission,
        sm.remarks,
        sm.del_flag delFlag,
        sm.wx_href wxHref,
        sm.wx_show wxShow,
        sm.wx_icon wxIcon,
        sm.path,
        sm.path_icon pathIcon,
        sm.type,
        sm.btn_permission btnPermission
        FROM
        sys_menu sm
        LEFT JOIN sys_role_menu srm ON sm.id = srm.menu_id
        LEFT JOIN sys_user_role sur ON sur.role_id = srm.role_id
        WHERE
        sm.del_flag ='0'
        AND sm.is_show = '1'
        <if test="_user.loginName != 'admin'">
            AND sur.user_id = #{_user.id}
        </if>
        <if test="id != null and id != ''">
            AND sm.id = #{id}
        </if>
        <if test="parentId != null and parentId != ''">
            AND sm.parent_id = #{parentId}
        </if>
        GROUP BY sm.id
        ORDER BY sm.sort
    </select>

    <select id="selectOfficeList" parameterType="map" resultType="map" name="查询部门" expose="true">
        SELECT
        id,
        name,
        (select count(*) from sys_user where company_id=sys_office.id and del_flag='0') as account_count
        FROM
        sys_office
        WHERE
        id != #{_user.company_id}
        <!--<choose>
            <when test="_user.company_name.indexOf('综治中心') >= 0">
                AND (grade = #{_user.company_grade} OR grade = #{_user.company_grade} + 1)
                AND FIND_IN_SET( #{_user.parent_id},parent_ids)
            </when >
            <otherwise>
            </otherwise>
        </choose>-->
        AND grade = #{_user.company_grade}
        <!--AND parent_id = #{_user.parent_id}-->
        and create_by = #{_user.id} AND fax IS NOT NULL
        <if test="event_id!=null and event_id!=''">
            and id not in (select office_id from jl_event_flow_log where event_id=#{event_id} and del_flag='0' and status='1' and is_dispatch_office='1')
            and id not in (select company_id from sys_user where id=(select create_by from jl_event where id=#{event_id}))
        </if>
        having account_count>0
        ORDER BY `code` DESC
    </select>

    <insert id="saveOffice" parameterType="map">
        INSERT INTO sys_office(
        id,
        parent_id,
        parent_ids,
        name,
        sort,
        area_id,
        code,
        type,
        grade,
        address,
        useable,
        create_by,
        create_date,
        sys_area,
        remarks,
        fax,
        dict_type_id
        ) VALUES(
        #{uuid},
        #{_user.parent_id},
        #{_user.parent_ids},
        #{name},
        #{sort},
        #{areaId},
        #{code},
        '1',
        #{_user.company_grade},
        #{address},
        '1',
        #{_user.id},
        NOW(),
        #{_user.company_sys_area},
        #{remarks},
        'B0E01AB397FA-42179EB8139B4C4ECCB9',
        #{dictTypeId}
        )
    </insert>

    <select id="getLoginName" parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqSysOfficeVo"
            resultType="string">
        SELECT
        concat(so.code, ifnull(max(cast(replace(su.login_name, so.code,'') as SIGNED )),0) + 1) AS login_name
        FROM
        sys_user su, sys_office so
        WHERE
        so.id = #{_user.company_id}
        AND su.login_name REGEXP CONCAT('^', so.code, '[0-9]*$')
    </select>

    <insert id="addRoleForUsers" parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqSysOfficeVo">
        INSERT INTO sys_user_role(
        user_id,
        role_id
        ) VALUES (
        #{userId},
        #{roleId}
        )
    </insert>

    <insert id="saveUser" parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqSysOfficeVo">
        INSERT INTO sys_user (
        id,
        company_id,
        office_id,
        login_name,
        password,
        no,
        name,
        phone,
        mobile,
        user_type,
        login_flag,
        create_by,
        create_date,
        remarks,
        del_flag,
        login_fail,
        cardno,
        mac_duty,
        bc_duty,
        duty_type
        <if test="jobDuty!=null and jobDuty!=''">
            ,job_duty
        </if>
        ) VALUES (
        #{userId},
        #{uuid},
        #{uuid},
        #{loginName},
        '2a5e6981f9b3660c14ad628ebfd0887236503a2bb2ac5b05a95728fd',
        #{loginName},
        #{userName},
        #{phone},
        #{phone},
        '1',
        '1',
        #{_user.id},
        NOW(),
        #{remarks},
        '0',
        '0',
        TO_BASE64(AES_ENCRYPT(#{cardno},'*#id#')),
        '0',
        '0',
        '0'
        <if test="jobDuty!=null and jobDuty!=''">
            ,#{jobDuty}
        </if>
        )
    </insert>

    <select id="selectOfficeParentList" parameterType="page" resultType="map" name="查询机构" expose="true">
        SELECT
        id,
        parent_id,
        parent_ids,
        name,
        sort,
        area_id,
        code,
        grade,
        address,
        remarks,
        del_flag,
        sys_area
        FROM
        sys_office
        WHERE
        del_flag = '0'
        AND fax IS NOT NULL
        <if test="params.name != null and params.name != ''">
            AND name LIKE CONCAT('%',#{params.name},'%')
        </if>
        <choose>
            <when test="params.sysUserId != null and params.sysUserId != '' and params.companyId != null and params.companyId != ''">
                AND (parent_ids LIKE CONCAT('%',#{params.companyId},'%') OR create_by = #{params.sysUserId})
            </when>
            <when test="params.sysUserId != null and params.sysUserId != ''">
                AND create_by = #{params.sysUserId}
            </when>
            <otherwise>
                and find_in_set(#{params.companyId},parent_ids)
            </otherwise>
        </choose>
        ORDER BY sort ASC,create_date DESC
    </select>

    <select id="selectOfficeParentInfo" parameterType="map" resultType="map" name="查询机构详情" expose="true">
        SELECT
        so.id,
        so.parent_id,
        so.parent_ids,
        so.name,
        so.sort,
        so.area_id,
        so.code,
        so.grade,
        so.address,
        so.remarks,
        so.del_flag,
        so.sys_area,
        sd.id dict_type_id,
        sd.label,
        sd.value
        FROM
        sys_office so
        LEFT JOIN sys_dict sd ON so.dict_type_id = sd.id
        WHERE
        so.del_flag = '0'
        and so.id=#{id}
    </select>

    <update id="updateSysOffice" parameterType="map" name="修改机构" expose="true">
        update sys_office
        <set>
            <if test="parent_id != null and parent_id != ''">parent_id = #{parent_id},</if>
            <if test="parent_ids != null and parent_ids != ''">parent_ids = #{parent_ids},</if>
            <if test="name != null and name != ''">name = #{name},</if>
            <if test="sort != null">sort = #{sort},</if>
            <if test="area_id != null and area_id != ''">area_id = #{area_id},</if>
            <if test="code != null and code != ''">code = #{code},</if>
            <if test="grade != null and grade != ''">grade = #{grade},</if>
            <if test="address != null and address != ''">address = #{address},</if>
            <if test="remarks != null and remarks != ''">remarks = #{remarks},</if>
            <if test="del_flag != null and del_flag != ''">del_flag = #{del_flag},</if>
            <if test="sys_area != null and sys_area != ''">sys_area = #{sys_area},</if>
            <if test="dict_type_id != null and dict_type_id != ''">dict_type_id = #{dict_type_id},</if>
        </set>
        where id = #{id}
    </update>

    <select id="selectOfficeTypeParentList" parameterType="map" resultType="map" name="添加用户查询机构" expose="true">
        SELECT
        id,
        parent_id,
        parent_ids,
        name,
        sort,
        area_id,
        code,
        grade,
        address,
        remarks,
        del_flag,
        sys_area
        FROM
        sys_office
        WHERE
        del_flag = '0'
        <if test="officeType == '1'.toString()">
            AND grade = #{_user.company_grade} + 1
            AND parent_ids LIKE CONCAT('%',#{_user.parent_id},'%')
        </if>
        <if test="officeType == '2'.toString()">
            AND parent_id = #{_user.parent_id}
            AND fax IS NOT NULL
        </if>
    </select>

    <select id="selectUserCompanyList" parameterType="page" resultType="map" name="查询用户" expose="true">
        SELECT
        *
        FROM
        (SELECT
        su.id,
        su.company_id,
        su.name AS user_name,
        su.phone,
        su.remarks,
        CAST(AES_DECRYPT(FROM_BASE64(su.cardno),'*#id#') AS CHAR) AS cardno,
        so.`name` officeName,
        su.login_name,
        su.mobile
        FROM
        sys_user su
        LEFT JOIN sys_office so ON su.office_id = so.id
        <where>
            su.del_flag = '0'
            AND so.del_flag = '0'
            <if test="params.id == null or params.id == ''">
                AND (so.parent_ids LIKE CONCAT('%',#{params._user.company_id},'%') OR su.create_by = #{params._user.id})
            </if>
            <if test="params.id != null and params.id != ''">
                AND su.company_id = #{params.id}
            </if>
            <if test="params.user_id != null and params.user_id != ''">
                AND su.id = #{params.user_id}
            </if>
            <if test="params.user_name != null and params.user_name != ''">
                AND su.name LIKE CONCAT('%',#{params.user_name},'%')
            </if>
            <if test="params.office_name != null and params.office_name != ''">
                AND so.`name` LIKE CONCAT('%',#{params.office_name},'%')
            </if>
            <if test="params.phone != null and params.phone != ''">
                AND su.phone LIKE CONCAT('%',#{params.phone},'%')
            </if>
        </where>
        ORDER BY so.`code` ASC)a
    </select>

    <select id="selectRoleNameList" parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqSysOfficeVo"
            resultType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqSysOfficeVo">
        SELECT
        DISTINCT sr.id roleId,
        sr.name
        FROM
        sys_role sr
        INNER JOIN sys_user_role sur ON sr.id = sur.role_id
        INNER JOIN sys_user su ON su.id = sur.user_id
        WHERE
        sr.del_flag = '0'
        AND su.company_id = #{uuid}
        AND (su.job_duty != '1' or su.job_duty is null)
        <if test="officeType == '2'.toString()">
            AND sr.id != '035b1402ba384a57b0859c1dd0f905fa'
            AND sr.id != '271cbc62dafd4a64bc37ad3f1f776677'
        </if>
    </select>

    <select id="getUserById" parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqSysOfficeVo"
            resultType="String">
        SELECT
        id
        FROM
        sys_user
        WHERE
        job_duty = '1'
        LIMIT 1
    </select>

    <select id="getUserByIdNew" parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqSysOfficeVo"
            resultType="String">
        <!--SELECT
        id
        FROM
        sys_user
        WHERE
        job_duty = '1'
        LIMIT 1-->
        select user_id as id from sys_user_role where role_id='8545188e-2dfc-11ef-af17-f8f21ee9b878'
        limit 1
    </select>

    <select id="selectRoleList" parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqSysOfficeVo"
            resultType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqSysOfficeVo">
        SELECT
        role_id roleId
        FROM
        sys_user_role
        WHERE
        user_id = #{id}
    </select>

    <select id="getOfficeComprehensive" parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventFlowLogInsertVo"
            resultType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqSysOfficeVo">
        SELECT
        id,
        parent_ids
        FROM
        sys_office
        WHERE
        <if test="_user.company_grade == '2'.toString()">
            <!-- 查询省级综治中心-->
            AND grade = '2'
            AND `name` LIKE '%综治中心%'
            AND parent_id = #{_user.parent_id}
        </if>
        <if test="_user.company_grade == '3'.toString()">
            <!-- 查询市级综治中心-->
            AND grade = '3'
            AND `name` LIKE '%综治中心%'
            AND parent_id = #{_user.parent_id}
        </if>
        <if test="_user.company_grade == '4'.toString()">
            <!-- 查区级综治中心-->
            AND grade = '4'
            AND `name` LIKE '%综治中心%'
            AND parent_id = #{_user.parent_id}
        </if>
    </select>

    <select id="getUserLoginInfo" parameterType="java.lang.String" resultType="map">
        SELECT
        su.id,
        su.company_id,
        sc. CODE AS company_code,
        sc.name AS company_name,
        sc.type company_type,
        sc.grade company_grade,
        sc.sys_area company_sys_area,
        sc.phone company_phone,
        sc.parent_id parent_id,
        scparent.code parent_code,
        sc.parent_ids parent_ids,
        su.login_name loginName,
        su.no,
        su.name,
        su.email,
        su.phone,
        su.mobile,
        su.user_type userType,
        '' AS office_childs_code,
        sc. CODE AS bind_seller_company_code,
        su.login_name bind_seller_login_code,
        sc.sys_area bind_seller_sys_area,
        sc.binding_place company_binding_place,
        su.cardno
        FROM
        sys_user su
        inner join sys_office sc on su.company_id = sc.id
        left join sys_office scparent on sc.parent_id = scparent.id
        WHERE
        su.del_flag = '0'
        AND su.login_flag = '1'
        AND sc.del_flag = '0'
        AND sc.type = '1'
        AND su.login_name = #{loginName}
    </select>

    <select id="getById" parameterType="map" resultType="map">
        SELECT
        so.name AS company_name,
        so.code AS company_code,
        su.id,
        su.company_id,
        su.login_name,
        su.no,
        su.name,
        su.email,
        su.phone,
        su.mobile,
        su.user_type,
        su.photo,
        su.login_ip,
        DATE_FORMAT(su.login_date,'%Y-%m-%d %H:%i:%S') as login_date,
        su.login_flag,
        su.create_by,
        DATE_FORMAT(su.create_date,'%Y-%m-%d %H:%i:%S') as create_date,
        su.update_by,
        DATE_FORMAT(su.update_date,'%Y-%m-%d %H:%i:%S') as update_date,
        su.remarks,
        su.cardno
        FROM
        sys_user su, sys_office so
        WHERE
        su.office_id = so.id
        AND su.id = #{id, jdbcType=VARCHAR}
        AND su.del_flag = '0'
    </select>

    <select id="menuForLoginList" parameterType="map" resultType="map">
        SELECT DISTINCT
        sm.href,
        sm.permission
        -- , sm.tags as tags
        FROM
        sys_menu sm
        LEFT JOIN sys_role_menu srm ON sm.id = srm.menu_id
        JOIN sys_role sr ON sr.id = srm.role_id
        JOIN sys_user_role sur ON sr.id = sur.role_id
        WHERE
        sm.del_flag = '0'
        AND sr.del_flag = '0'
        AND ((sm.href IS NOT NULL and sm.href != '')
        OR (sm.permission IS NOT NULL and sm.permission != ''))
        AND sur.user_id = #{id, jdbcType=VARCHAR}
    </select>

    <select id="getAreaById" parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventFlowLogInsertVo"
            resultType="string">
        SELECT
        area_id
        FROM
        sys_office
        WHERE
        id = #{_user.company_id}
    </select>

    <update id="updateUser" parameterType="map" expose="true">
        UPDATE
        sys_user
        SET
        <if test="user_name != null and user_name != ''">
            `name` = #{user_name},
        </if>
        <if test="phone != null and phone != ''">
            phone = #{phone},
            mobile = #{phone},
        </if>
        <if test="cardno != null and cardno != ''">
            cardno = TO_BASE64(AES_ENCRYPT(#{cardno},'*#id#')),
        </if>
        <if test="del_flag != null and del_flag != ''">
            del_flag = #{del_flag},
        </if>
        <if test="del_flag == '1'.toString()">
            remarks = CONCAT(phone,'|||',mobile,'|||',cardno,'|||',remarks),
            phone = null,
            mobile = null,
            cardno = null,
        </if>
        update_by = #{_user.id},
        update_date = NOW()
        WHERE
        id = #{id}
    </update>

    <select id="getPhone" parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqSysOfficeVo" resultType="Integer">
        SELECT
        IFNULL(COUNT(1),0)
        FROM
        sys_user
        WHERE
        phone = #{phone}
        AND del_flag = '0'
    </select>

    <select id="getCityId" parameterType="map" resultType="map">
        SELECT
        sc3.area_id city_id,
        sc2.area_id area_id,
        sc1.area_id street_id,
        sc.area_id community_id,
        sc.`code` community_code
        FROM
        sys_office sc
        LEFT JOIN sys_office sc1 on sc.parent_id = sc1.id
        LEFT JOIN sys_office sc2 on sc1.parent_id = sc2.id
        LEFT JOIN sys_office sc3 on sc2.parent_id = sc3.id
        WHERE
        sc.id = #{_user.company_id}
    </select>

    <select id="findGridUserList" parameterType="page" resultType="map" expose="true">
        SELECT
        su.id,
        su.name,
        su.phone,
        CAST(AES_DECRYPT(FROM_BASE64(su.cardno),'HMzOraRAn6nWZmty10Lmt123') AS CHAR) as cardno,
        <!-- 此处加密可以在程序返回中进行脱敏处理，减轻数据库的计算压力
        CONCAT(LEFT(CAST(AES_DECRYPT(FROM_BASE64(su.cardno),'*#id#') AS CHAR),3), '****' ,RIGHT(CAST(AES_DECRYPT(FROM_BASE64(su.cardno),'*#id#') AS CHAR),4)) as cardno,-->
        su.remarks,
        su.no,
        su.login_flag
        FROM
        sys_user su
        LEFT JOIN sys_user_role sur ON su.id = sur.user_id
        WHERE
        sur.role_id = '8545188e-2dfc-11ef-af17-f8f21ee9b878'
        AND su.del_flag = '0'
        AND su.company_id = #{params._user.company_id}
    </select>

    <update id="updateGridUser" parameterType="map" expose="true">
        UPDATE
        sys_user
        SET
        <if test="name != null and name != ''">
            name = #{name},
        </if>
        <if test="phone != null and phone != ''">
            phone = #{phone},
            mobile = #{phone},
        </if>
        <if test="del_flag != null and del_flag != ''">
            del_flag = #{del_flag},
        </if>
        cardno = TO_BASE64(AES_ENCRYPT(#{cardno},'*#id#')),
        remarks = #{remarks}
        WHERE
        id = #{id}
    </update>


    <select id="selectUserAppIdList" parameterType="map" resultType="map" expose="true" name="查询视频appid">
        SELECT
        `name`,
        user_app_id userId
        FROM
        sys_office
        WHERE
        user_app_id IS NOT NULL
    </select>

    <select id="getUserAppidByCode" parameterType="map" resultType="map" expose="true" name="查询视频appid">
        SELECT
        `name`,
        user_app_id userId
        FROM
        sys_office
        WHERE
        `code` = #{_user.company_code}
    </select>

    <select id="getFaxById" parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventFlowLogInsertVo"
            resultType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqSysOfficeVo" expose="true">
        SELECT
        IFNULL(IF(fax IS NOT NULL AND fax != '',1,0),0) num,
        create_by createBy
        FROM
        sys_office
        WHERE
        id = #{_user.company_id}
    </select>

    <select id="selectJuniorList" parameterType="map" resultType="map" expose="true">
        SELECT
        id crossOfficeId,
        `name`
        FROM
        sys_office
        WHERE
        parent_id = #{_user.company_id}
        <!--and is_manage_center='1'-->
        and fax is null
        <if test="event_id!=null and event_id!=''">
            and id not in (select company_id from sys_user where id=(select create_by from jl_event where id=#{event_id}))
        </if>
    </select>

    <select id="selectCreateByIdList" parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventFlowLogInsertVo"
            resultType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventFlowLogInsertVo">
        SELECT
        id crossOfficeId,
        `name`
        FROM
        sys_office
        WHERE
        id = #{id}
    </select>

    <select id="selectParentByIdList" parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventFlowLogInsertVo"
            resultType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventFlowLogInsertVo">
        SELECT
        id crossOfficeId,
        `name`
        FROM
        sys_office
        WHERE
        parent_id = #{_user.parent_id}
        AND parent_ids = #{_user.parent_ids}
    </select>

    <select id="selectParentByIdListNew" parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventFlowLogInsertVo"
            resultType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqEventFlowLogInsertVo">
        SELECT
        id crossOfficeId,
        `name`
        FROM
        sys_office
        WHERE
        create_by = #{_user.id}
        and fax IS NOT NULL
        and del_flag='0'
    </select>

    <select id="selectRoleMenuList" parameterType="map" resultType="map" expose="true">
        SELECT
        sm.id,
        sm.`name`
        FROM
        sys_user su
        LEFT JOIN sys_user_role sru ON su.id = sru.user_id
        LEFT JOIN sys_role sr ON sr.id = sru.role_id
        LEFT JOIN sys_role_menu srm ON sr.id = srm.role_id
        LEFT JOIN sys_menu sm ON srm.menu_id = sm.id
        WHERE
        sm.parent_id = '1'
        AND su.id = #{_user.id}
    </select>

    <select id="selectFamilyList" resultType="map" expose="true">
        SELECT
        id,
        `value`,
        label
        FROM
        sys_dict
        WHERE
        type = 'sys_type_family'
        ORDER BY sort ASC
    </select>

    <insert id="saveOfficeTask" parameterType="map">
        INSERT INTO sys_office(
        id,
        parent_id,
        parent_ids,
        name,
        sort,
        area_id,
        code,
        type,
        grade,
        useable,
        create_by,
        create_date,
        sys_area
        ) VALUES(
        #{code},
        #{parent_id},
        #{parent_ids},
        #{name},
        #{sort},
        #{code},
        #{company_code},
        '1',
        #{type} + 1,
        '1',
        '1',
        NOW(),
        'JL'
        )
    </insert>

    <select id="selectAreaOfficeList" resultType="map">
        SELECT
        sa.id,
        sa.`code`,
        sa.parent_id,
        sa.parent_ids,
        sa.name,
        sa.sort,
        sa.type
        FROM
        sys_area sa
        LEFT JOIN sys_office so ON sa.code = so.id
        WHERE
        so.id IS NULL
        ORDER BY id ASC
        LIMIT 500
    </select>

    <select id="selectGridUserList" parameterType="map" resultType="map" expose="true">
        SELECT
        id crossOfficeId,
        name
        FROM
        sys_user
        WHERE
        job_duty = '1'
        AND company_id = #{_user.company_id}
    </select>

    <select id="selectOfficeUserList" parameterType="page" resultType="map" expose="true">
        SELECT
        IF(so.fax != '' AND so.fax IS NOT NULL,'进驻部门','综治中心') type,
        CONCAT(IF(so3.grade > '2',so3.name,''),IF(so2.grade > '2',so2.name,''),IF(so1.grade > '2',so1.name,''),so.name)
        officeName,
        su.name,
        su.id userId,
        cast( AES_DECRYPT( FROM_BASE64 ( su.cardno ), '*#id#' ) AS CHAR ) cardno,
        su.mobile,
        su.phone,
        su.login_name
        FROM
        sys_office so
        LEFT JOIN sys_user su ON so.id = su.company_id
        LEFT JOIN sys_office so1 ON so.parent_id = so1.id
        LEFT JOIN sys_office so2 ON so1.parent_id = so2.id
        LEFT JOIN sys_office so3 ON so2.parent_id = so3.id
        WHERE
        so.del_flag = '0'
        AND su.del_flag = '0'
<!--        <if test="params.grade != null and params.grade != ''">-->
<!--            AND so.grade = #{params.grade} + 1-->
<!--        </if>-->
<!--        <if test="params.grade == null or params.grade == ''">-->
<!--            AND so.grade > #{params._user.company_grade}-->
<!--        </if>-->
        <if test="params.id != null and params.id != ''">
<!--            AND so.parent_ids LIKE CONCAT('%',#{params.id},'%')-->
            AND so.id = #{params.id}
        </if>
        <if test="params.fax == '1'.toString()">
            AND so.fax IS NULL
        </if>
        <if test="params.fax == '2'.toString()">
            AND so.fax IS NOT NULL
        </if>
        <if test="params.name != null and params.name != ''">
            AND su.name LIKE CONCAT('%',#{params.name},'%')
        </if>
        <if test="params.mobile != null and params.mobile != ''">
            AND su.mobile LIKE CONCAT('%',#{params.mobile},'%')
        </if>
        <if test="params.cardno != null and params.cardno != ''">
            AND cast( AES_DECRYPT( FROM_BASE64 ( su.cardno ), '*#id#' ) AS CHAR ) LIKE CONCAT('%',#{params.cardno},'%')
        </if>
        ORDER BY so.grade ASC
    </select>

    <select id="findOfficeChildrenList" parameterType="map" resultType="map" expose="true">
        SELECT
        a.*,
        (SELECT id FROM sys_user WHERE company_id = a.id ORDER BY create_date ASC LIMIT 1) sysUserId,
        'true' as hasChild
        FROM
        sys_office a
        WHERE
        a.del_flag = '0'
        <if test="id != null and id != ''">
            AND a.parent_id =  if (#{id} = '0', '000000000000',#{id})
        </if>
        and a.fax is null
        order by sort
    </select>

    <update id="updateUserById" parameterType="map" expose="true">
        UPDATE
        sys_user
        SET
        <if test="del_flag != null and del_flag != ''">
            del_flag = '1',
            mobile = null,
            phone = null,
            cardno = null,
        </if>
        <if test="password != null and password != ''">
            password = '2a5e6981f9b3660c14ad628ebfd0887236503a2bb2ac5b05a95728fd',
        </if>
        <if test="name != null and name != ''">
            name = #{name},
        </if>
        <if test="cardno != null and cardno != ''">
            cardno = TO_BASE64(AES_ENCRYPT(#{cardno},'HMzOraRAn6nWZmty10Lmt123')),
        </if>
        <if test="mobile != null and mobile != ''">
            mobile = #{mobile},
        </if>
        update_by = #{_user.id},
        update_date = NOW()
        WHERE
        id = #{userId}
    </update>

    <select id="getActivateApplication"
            parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqActivateApplicationVo"
            resultType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqActivateApplicationVo" expose="true">
        SELECT
        id,
        name,
        position
        FROM
        jl_activate_application
        WHERE
        user_id = #{_user.id}
    </select>

    <insert id="saveActivateApplication"
            parameterType="com.scpyun.platform.jilinsscgsdp.bean.vo.ReqActivateApplicationVo" expose="true">
        <selectKey keyProperty="count" resultType="int" order="BEFORE">
            SELECT COUNT(1) FROM jl_activate_application WHERE id = #{id}
        </selectKey>
        <if test="count == 0">
            INSERT INTO jl_activate_application(
            id,
            name,
            position,
            user_id,
            create_by,
            create_time
            )VALUES(
            UUID(),
            #{name},
            #{position},
            #{_user.id},
            #{_user.id},
            NOW()
            )
        </if>
        <if test="count > 0">
            UPDATE
            jl_activate_application
            SET
            name = #{name},
            position = #{position},
            update_by = #{_user.id},
            update_time = NOW()
            WHERE
            id = #{id}
        </if>
    </insert>

    <!--基于用户id修改登录标识-->
    <update id="updateLoginFlagByUserId" parameterType="map">
        UPDATE
        sys_user
        <set>
            login_flag = #{login_flag, jdbcType=VARCHAR},
        </set>
        <where>
            id = #{id, jdbcType=VARCHAR}
            AND del_flag = '0'
        </where>
    </update>

    <update id="updateUserInfoById" parameterType="map" expose="true">
        UPDATE
        sys_user
        SET
            `name` = #{name},
            phone = #{phone},
            mobile = #{phone},
        cardno = TO_BASE64(AES_ENCRYPT(#{cardno},'*#id#')),
        update_by = #{_user.id},
        update_date = NOW()
        WHERE
        id = #{id}
    </update>

    <update id="updateOfficeInfoById" parameterType="map" name="修改机构" expose="true">
        update sys_office
        <set>
            <if test="parent_id != null and parent_id != ''">parent_id = #{parent_id},</if>
            <if test="parent_ids != null and parent_ids != ''">parent_ids = #{parent_ids},</if>
            <if test="name != null and name != ''">name = #{name},</if>
            <if test="sort != null">sort = #{sort},</if>
            <if test="area_id != null and area_id != ''">area_id = #{area_id},</if>
            <if test="code != null and code != ''">code = #{code},</if>
            <if test="grade != null and grade != ''">grade = #{grade},</if>
            <if test="address != null and address != ''">address = #{address},</if>
            <if test="remarks != null and remarks != ''">remarks = #{remarks},</if>
            <if test="del_flag != null and del_flag != ''">del_flag = #{del_flag},</if>
            <if test="sys_area != null and sys_area != ''">sys_area = #{sys_area},</if>
        </set>
        where id = #{id}
    </update>
    <insert id="createOffice" parameterType="map">
        INSERT INTO sys_office(
        id,
        parent_id,
        parent_ids,
        name,
        sort,
        area_id,
        code,
        type,
        grade,
        address,
        useable,
        create_by,
        create_date,
        sys_area,
        remarks,
        fax
        ) VALUES(
        #{area_id},
        #{parent_id},
        #{parent_ids},
        #{name},
        #{sort},
        #{area_id},
        #{code},
        '1',
        #{grade},
        #{address},
        '1',
        #{_user.id},
        NOW(),
        #{sys_area},
        #{remarks},
        #{fax}
        )
    </insert>
    <select id="getOfficeInfoById" parameterType="map" resultType="map" expose="true">
        select id,
                parent_id,
                parent_ids,
                name,
                sort,
                area_id,
                code,
                type,
                grade,
                address,
                zip_code,
                master,
                phone,
                fax,
                email,
                useable,
                primary_person,
                deputy_person,
                create_by,
                create_date,
                update_by,
                update_date,
                remarks,
                del_flag,
                sys_area,
                binding_place,
                csc_mapjson_ht,
                company_scope_ht,
                user_app_id,
                is_manage_center
        from sys_office
        where id = #{id}
        and del_flag = '0'
    </select>
    <select id="getAreaCount" parameterType="string" resultType="integer">
        select count(*) from sys_area where code = #{areaId}
    </select>
    <select id="getOfficeCodeById" parameterType="map"
            resultType="string">
        SELECT
        CONCAT(#{sys_area, jdbcType=VARCHAR},LPAD(right(ifnull(max(code),0),5)+1,5,'0')) code
        FROM
        sys_office
        WHERE
        code LIKE CONCAT(#{sys_area, jdbcType=VARCHAR},'_____')
    </select>
    <insert id="createUser" parameterType="map">
        INSERT INTO sys_user (
        id,
        company_id,
        office_id,
        login_name,
        password,
        no,
        name,
        phone,
        mobile,
        user_type,
        login_flag,
        create_by,
        create_date,
        remarks,
        del_flag,
        login_fail,
        cardno,
        mac_duty,
        bc_duty,
        duty_type
        <if test="jobDuty!=null and jobDuty!=''">
            ,job_duty
        </if>
        ) VALUES (
        #{id},
        #{company_id},
        #{office_id},
        #{login_name},
        '2a5e6981f9b3660c14ad628ebfd0887236503a2bb2ac5b05a95728fd',
        #{login_name},
        #{name},
        #{phone},
        #{phone},
        '1',
        '1',
        #{_user.id},
        NOW(),
        #{remarks},
        '0',
        '0',
        TO_BASE64(AES_ENCRYPT(#{cardno},'*#id#')),
        '0',
        '0',
        '0'
        <if test="jobDuty!=null and jobDuty!=''">
            ,#{jobDuty}
        </if>
        )
    </insert>
    <select id="getLoginNameByComId" parameterType="map"
            resultType="string">
        SELECT
        concat(so.code, ifnull(max(cast(replace(su.login_name, so.code,'') as SIGNED )),0) + 1) AS login_name
        FROM
        sys_user su, sys_office so
        WHERE
        so.id = #{company_id}
        AND su.login_name REGEXP CONCAT('^', so.code, '[0-9]*$')
    </select>
    <select id="getPhoneCount" parameterType="map" resultType="Integer">
        SELECT
        IFNULL(COUNT(1),0)
        FROM
        sys_user
        WHERE
        phone = #{phone}
    </select>
    <select id="getCardnoCount" parameterType="map" resultType="Integer">
        SELECT
        IFNULL(COUNT(1),0)
        FROM
        sys_user
        WHERE
        cardno = TO_BASE64(AES_ENCRYPT(#{cardno},'*#id#'))
    </select>
    <insert id="insertUserRole" parameterType="map">
        INSERT INTO sys_user_role (user_id, role_id) VALUES
        <foreach collection="role_list" index="index" item="role_id" separator="," >
            (
            #{user_id},#{role_id}
            )
        </foreach>
    </insert>
    <select id="getName"  parameterType="string" resultType="string">
        select name from sys_area where id =#{id};
    </select>
</mapper>
