<?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.duty" name="定则定线定人">

    <select id="findGardenUserDutyList" parameterType="page" resultType="map" expose="true" name="查询定则信息">
        SELECT
        duty.id,
        duty.city_id,
        company.label company_name,
        duty.company_id,
        duty.duty,
        duty.`describe`
        FROM
        jl_grid_garden_user_duty duty left join (select label,value,sort from sys_dict where type='sys_type_office') company on company.value = duty.company_id
        <where>
        	duty.del_flag = '0'
        	<choose>
        		<when test="params._user.company_grade!=null and params._user.company_grade== '2'.toString() ">
        			and duty.city_id= #{params.city_id}
        		</when>
        		<otherwise>
        			and duty.city_id = #{params._user.company_id}
        		</otherwise>
        	</choose>
        </where>
        ORDER BY company.sort asc
    </select>
    
   <insert id="insetUserDuty" parameterType="map">
        INSERT INTO jl_grid_garden_user_duty(
        id,
        city_id,
        company_id,
        duty,
        `describe`,
        del_flag,
        create_by,
        create_time
        ) VALUES (
        UUID(),
        #{city_id},
        #{company_id},
        #{duty},
        #{describe},
        '0',
        #{_user.id},
        NOW()
        )
    </insert>

    <update id="updateUserDutyById" parameterType="map" name="修改定责">
        UPDATE
        jl_grid_garden_user_duty
        SET
        duty = #{duty},
        `describe` = #{describe},
        update_by = #{_user.id},
        update_time = NOW()
        WHERE
        id = #{id}
    </update>
    
    <delete id="deleteUserDutyById" parameterType="map" name="删除定责">
        delete from
        jl_grid_garden_user_duty
        WHERE
        id = #{id}
    </delete>
    
    <update id="updateGardenWorkerDuty"  parameterType="String">
        <!--
          WARNING - @mbg.generated
          This element is automatically generated by MyBatis Generator, do not modify.
          This element was generated on Mon Sep 02 14:32:15 CST 2024.
        -->
        update jl_grid_garden_user set 
        <set>
            <if test="name != null">
                name = #{name,jdbcType=VARCHAR},
            </if>
            <if test="cardno != null">
                cardno = TO_BASE64(AES_ENCRYPT(#{cardno,jdbcType=VARCHAR},'*#id#')),
            </if>
            <if test="tel != null">
                tel = #{tel,jdbcType=INTEGER},
            </if>
            <if test="responsibility != null">
                responsibility = #{responsibility,jdbcType=VARCHAR},
            </if>
            <if test="remark != null">
                remark = #{remark,jdbcType=VARCHAR},
            </if>
            <if test="cityId != null">
                city_id = #{cityId,jdbcType=VARCHAR},
            </if>
            <if test="areaId != null">
                area_id = #{areaId,jdbcType=VARCHAR},
            </if>
            <if test="streetId != null">
                street_id = #{streetId,jdbcType=VARCHAR},
            </if>
            <if test="communityId != null">
                community_id = #{communityId,jdbcType=VARCHAR},
            </if>
            <if test="gardenId != null">
                garden_id = #{gardenId,jdbcType=VARCHAR},
            </if>
            <if test="companyId != null">
                company_id = #{companyId,jdbcType=VARCHAR},
            </if>
            <if test="dutyId != null">
                duty_id = #{dutyId,jdbcType=VARCHAR},
            </if>
            <if test="delFlag != null">
                del_flag = #{delFlag,jdbcType=CHAR},
            </if>
            <if test="createBy != null">
                create_by = #{createBy,jdbcType=VARCHAR},
            </if>
            <if test="createTime != null">
                create_time = #{createTime,jdbcType=TIMESTAMP},
            </if>
            update_by = #{_user.id},
            update_time = now()
        </set>
        where id = #{id,jdbcType=VARCHAR}
    </update>
    <select id="findGardenUserDutyListByCondition" parameterType="map" resultType="map" expose="true" name="根据部门查询岗位职责">
        select id,duty,company_id companyId,city_id cityId from jl_grid_garden_user_duty where
        del_flag = '0'
        AND company_id = #{companyId}
        <choose>
        	<when test="_user.company_grade == '6'.toString()">
        		and city_id = SUBSTRING_INDEX(SUBSTRING_INDEX(#{_user.parent_ids}, ',', 3), ',', -1)
        	</when>
        	<otherwise>
        		and 1!=1
        	</otherwise>
        </choose>
    </select>
    <select id="selectGardenUserDutyByCompanyIdList" parameterType="map" resultType="map" expose="true" name="查询定则信息">
        SELECT
        id,
        duty,
        secondary_duty,
        `describe`
        FROM
        jl_grid_garden_user_duty
        WHERE
        del_flag = '0'
        AND company_id = #{company_id}
        <if test="duty != null and duty != ''">
            AND duty = #{duty}
        </if>
        ORDER BY create_time DESC
    </select>

    <select id="findGardenUserCompanyList" parameterType="page" resultType="map" expose="true" name="查询部门信息分页">
        SELECT
        id,
        company_name
        FROM
        jl_grid_garden_user_company
        WHERE
        del_flag = '0'
        AND city_id = (SELECT area_id FROM sys_office WHERE id = #{params._user.company_id})
        ORDER BY create_time DESC
    </select>

    <select id="selectGardenUserCompanyList" parameterType="map" resultType="map" expose="true" name="查询部门信息集合">
        SELECT
        id,
        company_name
        FROM
        jl_grid_garden_user_company
        WHERE
        del_flag = '0'
        AND city_id = (SELECT area_id FROM sys_office WHERE id = #{params._user.company_id})
        ORDER BY create_time DESC
    </select>


    <select id="findGardenUserList" parameterType="page" resultType="map" expose="true" name="查询定人信息">
        SELECT
        id,
        sys_user_id,
        cardno,
        responsibility,
        remark,
        (SELECT name FROM sys_area WHERE code = city_id) city,
        area_id,
        (SELECT name FROM sys_area WHERE code = area_id) area,
        street_id,
        (SELECT name FROM sys_area WHERE code = street_id) street,
        community_id,
        (SELECT name FROM sys_area WHERE code = community_id) community,
        community_code,
        (SELECT name FROM jl_grid_garden WHERE id = garden_id) garden,
        garden_id,
        (SELECT duty FROM jl_grid_garden_user_duty WHERE id = duty_id) duty,
        duty_id,
        (SELECT secondary_duty FROM jl_grid_garden_user_duty WHERE id = duty_id) secondary_duty,
        (SELECT company_name FROM jl_grid_garden_user_company WHERE id = company_id) company_name,
        company_id
        FROM
        jl_grid_garden_user
        WHERE
        del_flag = '0'
        <if test="params.city_id != null and params.city_id != ''">
            AND city_id = #{params.city_id}
        </if>
        <if test="params.area_id != null and params.area_id != ''">
            AND area_id = #{params.area_id}
        </if>
        <if test="params.street_id != null and params.street_id != ''">
            AND street_id = #{params.street_id}
        </if>
        <if test="params.community_id != null and params.community_id != ''">
            AND community_id = #{params.community_id}
        </if>
        <if test="params.garden_id != null and params.garden_id != ''">
            AND garden_id = #{params.garden_id}
        </if>
    </select>

    <select id="selectGardenUserList" parameterType="map" resultType="map" expose="true" name="查询定人信息">
        SELECT
        id,
        sys_user_id,
        cardno,
        responsibility,
        remark,
        city_id,
        (SELECT name FROM sys_area WHERE code = city_id) city,
        area_id,
        (SELECT name FROM sys_area WHERE code = area_id) area,
        street_id,
        (SELECT name FROM sys_area WHERE code = street_id) street,
        community_id,
        (SELECT name FROM sys_area WHERE code = community_id) community,
        community_code,
        (SELECT name FROM jl_grid_garden WHERE id = garden_id) garden,
        garden_id,
        (SELECT duty FROM jl_grid_garden_user_duty WHERE id = duty_id) duty,
        duty_id,
        (SELECT secondary_duty FROM jl_grid_garden_user_duty WHERE id = duty_id) secondary_duty,
        (SELECT company_name FROM jl_grid_garden_user_company WHERE id = company_id) company_name,
        company_id
        FROM
        jl_grid_garden_user
        WHERE
        del_flag = '0'
        <if test="city_id != null and city_id != ''">
            AND city_id = #{city_id}
        </if>
        <if test="area_id != null and area_id != ''">
            AND area_id = #{area_id}
        </if>
        <if test="street_id != null and street_id != ''">
            AND street_id = #{street_id}
        </if>
        <if test="community_id != null and community_id != ''">
            AND community_id = #{community_id}
        </if>
        <if test="garden_id != null and garden_id != ''">
            AND garden_id = #{garden_id}
        </if>
    </select>

    <select id="getGridGardenByGardenId" parameterType="map" resultType="map" expose="true" name="查询网格信息">
        SELECT
        id,
        name,
        garden_type,
        csc_mapjson,
        responsible_unit,
        responsibility_unit,
        unit_name,
        unit_tel,
        patrol_num,
        patrol_json,
        city_id,
        area_id,
        street_id,
        community_id,
        community_code,
        create_by,
        create_time
        FROM
        jl_grid_garden
        WHERE
        id = #{garden_id}
    </select>

    <insert id="insetUserCompany" parameterType="map" expose="true" name="添加部门">
        INSERT INTO jl_grid_garden_user_company(
        id,
        city_id,
        company_name,
        del_flag,
        create_by,
        create_time
        ) VALUES (
        UUID(),
        (SELECT area_id FROM sys_office WHERE id = #{_user.company_id}),
        #{company_name},
        '0',
        #{_user.id},
        NOW()
        )
    </insert>

    <insert id="insertGardenUser" parameterType="map">
        INSERT INTO jl_grid_garden_user(
        id,
        name,
        cardno,
        tel,
        responsibility,
        remark,
        city_id,
        area_id,
        street_id,
        community_id,
        garden_id,
        company_id,
        duty_id,
        del_flag,
        create_by,
        create_time
        ) VALUES (
        UUID(),
        #{name},
        TO_BASE64(AES_ENCRYPT(#{cardno},'*#id#')),
        #{tel},
        #{responsibility},
        #{remark},
        #{city_id},
        #{area_id},
        #{street_id},
        #{community_id},
        #{garden_id},
        #{company_id},
        #{duty_id},
        '0',
        #{_user.id},
        NOW()
        )
    </insert>

    <update id="updateUserCompanyById" parameterType="map" expose="true" name="修改部门">
        UPDATE
        jl_grid_garden_user_company
        SET
        company_name = #{company_name},
        <if test="del_flag != null and del_flag != ''">
            del_flag = #{del_flag},
        </if>
        update_by = #{_user.id},
        update_time = NOW()
        WHERE
        id = #{id}
    </update>

    <update id="updateGardenUser" parameterType="map">
        UPDATE
        jl_grid_garden_user
        SET
        cardno = TO_BASE64(AES_ENCRYPT(#{cardno},'*#id#')),
        responsibility = #{responsibility},
        name = #{name},
        tel = #{tel},
        remark = #{remark},
        company_id = #{company_id},
        duty_id = #{duty_id},
        update_by = #{_user.id},,
        update_time = NOW()
        WHERE
        id = #{id}
    </update>
    
    <update id="removeGardenUserDutys" parameterType="string">
        UPDATE
        jl_grid_garden_user
        SET
        company_id = null,
        responsibility = null,
        duty_id = null
        WHERE
        id = #{duty_id}
    </update>

</mapper>
