<?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.SysInitDao" name="系统表">

    <select id="selectUcGovRegionInfo" parameterType="map" resultType="map">
        SELECT
            id,
            version,
            create_time,
            region_code,
            region_name,
            region_name_short,
            region_desc,
            parent_region_code,
            region_lvl,
            nation,
            province,
            city,
            county,
            town,
            village,
            region_req,
            region_status,
            tenant_id,
            region_uuid,
            update_time,
            update_user_id,
            create_user_id,
            description,
            virtual_flag
        FROM uc_gov_region_info
        where region_status='US01'
    </select>

    <insert id="insertSysArea" parameterType="map">
        INSERT INTO sys_area
        <trim prefix="(" suffix=")" suffixOverrides=",">
            id,
            <if test="parent_region_code != null and parent_region_code != ''">parent_id,</if>
            <if test="parent_ids != null and parent_ids != ''">parent_ids,</if>
            <if test="region_name != null and region_name != ''">name,</if>
            <if test="region_req != null">sort,</if>
            <if test="region_code != null and region_code != ''">code,</if>
            <if test="region_lvl != null and region_lvl != ''">type,</if>
            del_flag
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            #{region_code},
            <if test="parent_region_code != null and parent_region_code != ''">#{parent_region_code},</if>
            <if test="parent_ids != null and parent_ids != ''">#{parent_ids},</if>
            <if test="region_name != null and region_name != ''">#{region_name},</if>
            <if test="region_req != null">#{region_req},</if>
            <if test="region_code != null and region_code != ''">#{region_code},</if>
            <if test="region_lvl != null and region_lvl != ''">#{region_lvl}-1,</if>
            '0'
        </trim>
    </insert>

    <select id="getParentIds" parameterType="map" resultType="map">
        select CONCAT('0,', GROUP_CONCAT(region_code ORDER BY region_lvl ASC SEPARATOR ',')) AS parent_ids
        from uc_gov_region_info where 1=1
        <if test="region_lvl=='3'.toString()">
            and region_name=#{province}
        </if>
        <if test="region_lvl=='4'.toString()">
            and (region_name=#{province} or region_name=#{city})
        </if>
        <if test="region_lvl=='5'.toString()">
            and (region_name=#{province} or region_name=#{city} or region_name=#{county})
        </if>
        <if test="region_lvl=='6'.toString()">
            and (region_name=#{province} or region_name=#{city} or region_name=#{county} or region_name=#{town})
        </if>
    </select>

    <select id="getSysAreaList" parameterType="map" resultType="map">
        select
        id,
        parent_id,
        parent_ids,
        name,
        sort,
        code,
        type
        from sys_area
        where del_flag='0'
    </select>

    <select id="generateOfficeCode" parameterType="map" 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>

    <insert id="insertSysOffice" parameterType="map">
    INSERT INTO sys_office (
        id,
        <if test="parent_id != null">parent_id,</if>
        <if test="parent_ids != null">parent_ids,</if>
        <if test="name != null">name,</if>
        <if test="sort != null">sort,</if>
        <if test="area_id != null">area_id,</if>
        <if test="code != null">code,</if>
        type,
        <if test="grade != null">grade,</if>
        <if test="address != null">address,</if>
        <if test="zip_code != null">zip_code,</if>
        <if test="master != null">master,</if>
        <if test="phone != null">phone,</if>
        <if test="fax != null">fax,</if>
        <if test="email != null">email,</if>
        useable,
        <if test="primary_person != null">primary_person,</if>
        <if test="deputy_person != null">deputy_person,</if>
        <if test="remarks != null">remarks,</if>
        sys_area,
        <if test="binding_place != null">binding_place,</if>
        <if test="csc_mapjson_ht != null">csc_mapjson_ht,</if>
        <if test="company_scope_ht != null">company_scope_ht,</if>
        <if test="user_app_id != null">user_app_id,</if>
        <if test="is_manage_center != null">is_manage_center,</if>
        create_by,
        create_date
    ) VALUES (
        #{id},
        <if test="parent_id != null">#{parent_id},</if>
        <if test="parent_ids != null">#{parent_ids},</if>
        <if test="name != null">#{name},</if>
        <if test="sort != null">#{sort},</if>
        <if test="area_id != null">#{area_id},</if>
        <if test="code != null">#{code},</if>
        '1',
        <if test="grade != null">#{grade}+1,</if>
        <if test="address != null">#{address},</if>
        <if test="zip_code != null">#{zip_code},</if>
        <if test="master != null">#{master},</if>
        <if test="phone != null">#{phone},</if>
        <if test="fax != null">#{fax},</if>
        <if test="email != null">#{email},</if>
        '1',
        <if test="primary_person != null">#{primary_person},</if>
        <if test="deputy_person != null">#{deputy_person},</if>
        <if test="remarks != null">#{remarks},</if>
        'JL',
        <if test="binding_place != null">#{binding_place},</if>
        <if test="csc_mapjson_ht != null">#{csc_mapjson_ht},</if>
        <if test="company_scope_ht != null">#{company_scope_ht},</if>
        <if test="user_app_id != null">#{user_app_id},</if>
        <if test="is_manage_center != null">#{is_manage_center},</if>
        #{_user.id},
        now()
    )
    </insert>

    <select id="generateLoginName" 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 = #{id}
        AND su.login_name REGEXP CONCAT('^', so.code, '[0-9]*$')
    </select>

    <insert id="insertUser" parameterType="map">
        INSERT INTO sys_user (
        id,
        company_id,
        office_id,
        login_name,
        password,
        no,
        name,
        user_type,
        login_flag,
        create_by,
        create_date,
        remarks,
        del_flag,
        login_fail,
        mac_duty,
        bc_duty,
        duty_type
        <!--<if test="jobDuty!=null and jobDuty!=''">
            ,job_duty
        </if>-->
        ) VALUES (
        #{user_id},
        #{id},
        #{id},
        #{loginName},
        '2a5e6981f9b3660c14ad628ebfd0887236503a2bb2ac5b05a95728fd',
        #{loginName},
        CONCAT(#{name}, '-综治中心'),
        '1',
        '1',
        #{_user.id},
        NOW(),
        #{remarks},
        '0',
        '0',
        '0',
        '0',
        '0'
        <!--<if test="jobDuty!=null and jobDuty!=''">
            ,#{jobDuty}
        </if>-->
        )
    </insert>

    <insert id="insertUserRole" parameterType="map">
        INSERT INTO sys_user_role(
        user_id,
        role_id
        ) VALUES (
        #{user_id},
        #{role_id}
        )
    </insert>

</mapper>
