<?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.xinelu.manage.mapper.agency.AgencyMapper">

    <resultMap type="Agency" id="AgencyResult">
        <result property="id" column="id"/>
        <result property="parentId" column="parent_id"/>
        <result property="agencyCategoryId" column="agency_category_id"/>
        <result property="agencyCategoryName" column="agency_category_name"/>
        <result property="areaCode" column="area_code"/>
        <result property="areaName" column="area_name"/>
        <result property="agencyName" column="agency_name"/>
        <result property="agencyCode" column="agency_code"/>
        <result property="agencyAbbreviation" column="agency_abbreviation"/>
        <result property="agencyStatus" column="agency_status"/>
        <result property="nodeType" column="node_type"/>
        <result property="orgAgencyCode" column="org_agency_code"/>
        <result property="agencyCategoryManageLevel" column="agency_category_manage_level"/>
        <result property="agencyContacts" column="agency_contacts"/>
        <result property="agencyPhone" column="agency_phone"/>
        <result property="agencyAddress" column="agency_address"/>
        <result property="agencyRemark" column="agency_remark"/>
        <result property="agencySort" column="agency_sort"/>
        <result property="createBy" column="create_by"/>
        <result property="createTime" column="create_time"/>
        <result property="updateBy" column="update_by"/>
        <result property="updateTime" column="update_time"/>
    </resultMap>

    <sql id="selectAgencyVo">
        select id,
               parent_id,
               agency_category_id,
               agency_category_name,
               area_code,
               area_name,
               agency_name,
               agency_code,
               agency_abbreviation,
               agency_status,
               node_type,
               org_agency_code,
               agency_category_manage_level,
               agency_contacts,
               agency_phone,
               agency_address,
               agency_remark,
               agency_sort,
               create_by,
               create_time,
               update_by,
               update_time
        from agency
    </sql>

    <select id="selectAgencyList" parameterType="Agency" resultMap="AgencyResult">
        <include refid="selectAgencyVo"/>
        <where>
            <if test="parentId != null ">
                and parent_id = #{parentId}
            </if>
            <if test="agencyCategoryId != null ">
                and agency_category_id = #{agencyCategoryId}
            </if>
            <if test="agencyCategoryName != null  and agencyCategoryName != ''">
                and agency_category_name like concat('%', #{agencyCategoryName}, '%')
            </if>
            <if test="areaCode != null  and areaCode != ''">
                and area_code = #{areaCode}
            </if>
            <if test="areaName != null  and areaName != ''">
                and area_name like concat('%', #{areaName}, '%')
            </if>
            <if test="agencyName != null  and agencyName != ''">
                and agency_name like concat('%', #{agencyName}, '%')
            </if>
            <if test="agencyCode != null  and agencyCode != ''">
                and agency_code = #{agencyCode}
            </if>
            <if test="agencyAbbreviation != null  and agencyAbbreviation != ''">
                and agency_abbreviation = #{agencyAbbreviation}
            </if>
            <if test="agencyStatus != null  and agencyStatus != ''">
                and agency_status = #{agencyStatus}
            </if>
            <if test="nodeType != null  and nodeType != ''">
                and node_type = #{nodeType}
            </if>
            <if test="orgAgencyCode != null  and orgAgencyCode != ''">
                and org_agency_code = #{orgAgencyCode}
            </if>
            <if test="agencyCategoryManageLevel != null  and agencyCategoryManageLevel != ''">
                and agency_category_manage_level = #{agencyCategoryManageLevel}
            </if>
            <if test="agencyContacts != null  and agencyContacts != ''">
                and agency_contacts = #{agencyContacts}
            </if>
            <if test="agencyPhone != null  and agencyPhone != ''">
                and agency_phone = #{agencyPhone}
            </if>
            <if test="agencyAddress != null  and agencyAddress != ''">
                and agency_address = #{agencyAddress}
            </if>
            <if test="agencyRemark != null  and agencyRemark != ''">
                and agency_remark = #{agencyRemark}
            </if>
            <if test="agencySort != null ">
                and agency_sort = #{agencySort}
            </if>
        </where>
    </select>

    <select id="selectAgencyVOList" resultType="com.xinelu.manage.vo.agency.AgencyVO">
        <include refid="selectAgencyVo"/>
    </select>

    <select id="selectAgencyById" parameterType="Long"
            resultMap="AgencyResult">
        <include refid="selectAgencyVo"/>
        where id = #{id}
    </select>

    <select id="selectSubordinateAgencyById" resultType="com.xinelu.manage.domain.agency.Agency">
        <include refid="selectAgencyVo"/>
        where parent_id =
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </select>

    <select id="selectAgencyVOById" resultType="com.xinelu.manage.vo.agency.AgencyVO">
        select id,
               ay.parent_id,
               agency_category_id,
               agency_category_name,
               area_code,
               area_name,
               agency_name,
               agency_code,
               agency_abbreviation,
               agency_status,
               node_type,
               org_agency_code,
               agency_category_manage_level,
               agency_contacts,
               agency_phone,
               agency_address,
               agency_remark,
               agency_sort,
               (select agency_name from agency where id = ay.parent_id) AS parentAgencyName
        from agency ay
        where id = #{id}
    </select>

    <insert id="insertAgency" parameterType="Agency" useGeneratedKeys="true"
            keyProperty="id">
        insert into agency
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="parentId != null">parent_id,
            </if>
            <if test="agencyCategoryId != null">agency_category_id,
            </if>
            <if test="agencyCategoryName != null">agency_category_name,
            </if>
            <if test="areaCode != null">area_code,
            </if>
            <if test="areaName != null">area_name,
            </if>
            <if test="agencyName != null">agency_name,
            </if>
            <if test="agencyCode != null">agency_code,
            </if>
            <if test="agencyAbbreviation != null">agency_abbreviation,
            </if>
            <if test="agencyStatus != null">agency_status,
            </if>
            <if test="nodeType != null">node_type,
            </if>
            <if test="orgAgencyCode != null">org_agency_code,
            </if>
            <if test="agencyCategoryManageLevel != null">agency_category_manage_level,
            </if>
            <if test="agencyContacts != null">agency_contacts,
            </if>
            <if test="agencyPhone != null">agency_phone,
            </if>
            <if test="agencyAddress != null">agency_address,
            </if>
            <if test="agencyRemark != null">agency_remark,
            </if>
            <if test="agencySort != null">agency_sort,
            </if>
            <if test="createBy != null">create_by,
            </if>
            <if test="createTime != null">create_time,
            </if>
            <if test="updateBy != null">update_by,
            </if>
            <if test="updateTime != null">update_time,
            </if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="parentId != null">#{parentId},
            </if>
            <if test="agencyCategoryId != null">#{agencyCategoryId},
            </if>
            <if test="agencyCategoryName != null">#{agencyCategoryName},
            </if>
            <if test="areaCode != null">#{areaCode},
            </if>
            <if test="areaName != null">#{areaName},
            </if>
            <if test="agencyName != null">#{agencyName},
            </if>
            <if test="agencyCode != null">#{agencyCode},
            </if>
            <if test="agencyAbbreviation != null">#{agencyAbbreviation},
            </if>
            <if test="agencyStatus != null">#{agencyStatus},
            </if>
            <if test="nodeType != null">#{nodeType},
            </if>
            <if test="orgAgencyCode != null">#{orgAgencyCode},
            </if>
            <if test="agencyCategoryManageLevel != null">#{agencyCategoryManageLevel},
            </if>
            <if test="agencyContacts != null">#{agencyContacts},
            </if>
            <if test="agencyPhone != null">#{agencyPhone},
            </if>
            <if test="agencyAddress != null">#{agencyAddress},
            </if>
            <if test="agencyRemark != null">#{agencyRemark},
            </if>
            <if test="agencySort != null">#{agencySort},
            </if>
            <if test="createBy != null">#{createBy},
            </if>
            <if test="createTime != null">#{createTime},
            </if>
            <if test="updateBy != null">#{updateBy},
            </if>
            <if test="updateTime != null">#{updateTime},
            </if>
        </trim>
    </insert>

    <update id="updateAgency" parameterType="Agency">
        update agency
        <trim prefix="SET" suffixOverrides=",">
            <if test="parentId != null">parent_id =
                #{parentId},
            </if>
            <if test="agencyCategoryId != null">agency_category_id =
                #{agencyCategoryId},
            </if>
            <if test="agencyCategoryName != null">agency_category_name =
                #{agencyCategoryName},
            </if>
            <if test="areaCode != null">area_code =
                #{areaCode},
            </if>
            <if test="areaName != null">area_name =
                #{areaName},
            </if>
            <if test="agencyName != null">agency_name =
                #{agencyName},
            </if>
            <if test="agencyCode != null">agency_code =
                #{agencyCode},
            </if>
            <if test="agencyAbbreviation != null">agency_abbreviation =
                #{agencyAbbreviation},
            </if>
            <if test="agencyStatus != null">agency_status =
                #{agencyStatus},
            </if>
            <if test="nodeType != null">node_type =
                #{nodeType},
            </if>
            <if test="orgAgencyCode != null">org_agency_code =
                #{orgAgencyCode},
            </if>
            <if test="agencyCategoryManageLevel != null">agency_category_manage_level =
                #{agencyCategoryManageLevel},
            </if>
            <if test="agencyContacts != null">agency_contacts =
                #{agencyContacts},
            </if>
            <if test="agencyPhone != null">agency_phone =
                #{agencyPhone},
            </if>
            <if test="agencyAddress != null">agency_address =
                #{agencyAddress},
            </if>
            <if test="agencyRemark != null">agency_remark =
                #{agencyRemark},
            </if>
            <if test="agencySort != null">agency_sort =
                #{agencySort},
            </if>
            <if test="createBy != null">create_by =
                #{createBy},
            </if>
            <if test="createTime != null">create_time =
                #{createTime},
            </if>
            <if test="updateBy != null">update_by =
                #{updateBy},
            </if>
            <if test="updateTime != null">update_time =
                #{updateTime},
            </if>
        </trim>
        where id = #{id}
    </update>

    <update id="updateAgencyById" parameterType="Agency">
        update agency
        <trim prefix="SET" suffixOverrides=",">
            parent_id = #{parentId},
            agency_category_id = #{agencyCategoryId},
            agency_category_name = #{agencyCategoryName},
            area_code = #{areaCode},
            area_name = #{areaName},
            <if test="agencyName != null">agency_name = #{agencyName},</if>
            agency_code = #{agencyCode},
            agency_abbreviation = #{agencyAbbreviation},
            agency_status = #{agencyStatus},
            node_type = #{nodeType},
            org_agency_code = #{orgAgencyCode},
            agency_category_manage_level = #{agencyCategoryManageLevel},
            agency_contacts = #{agencyContacts},
            agency_phone = #{agencyPhone},
            agency_address = #{agencyAddress},
            agency_remark = #{agencyRemark},
            agency_sort = #{agencySort},
            <if test="updateBy != null">update_by =
                #{updateBy},
            </if>
            <if test="updateTime != null">update_time =
                #{updateTime},
            </if>
        </trim>
        where id = #{id}
    </update>

    <delete id="deleteAgencyById" parameterType="Long">
        delete
        from agency
        where id = #{id}
    </delete>

    <delete id="deleteAgencyByIds" parameterType="String">
        delete from agency where id in
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>

    <select id="getAllAgencyInfo" resultType="com.xinelu.manage.domain.agency.Agency">
        select id,
        parent_id,
        agency_category_id,
        agency_category_name,
        area_code,
        area_name,
        agency_name,
        agency_code,
        agency_abbreviation,
        agency_status,
        node_type,
        org_agency_code,
        agency_category_manage_level,
        agency_contacts,
        agency_phone,
        agency_address,
        agency_remark,
        agency_sort,
        create_by,
        create_time,
        update_by,
        update_time
        from agency
        <where>
            <if test="agencyNames != null and agencyNames.size() > 0">
                and agency_name in
                <foreach item="agencyName" collection="agencyNames" open="(" separator="," close=")">
                    #{agencyName}
                </foreach>
            </if>
        </where>
    </select>

    <insert id="insertAgencyImportList" parameterType="java.util.List">
        insert into agency(
        parent_id,
        agency_category_id,
        agency_category_name,
        area_code,
        area_name,
        agency_name,
        agency_code,
        agency_abbreviation,
        agency_status,
        node_type,
        org_agency_code,
        agency_category_manage_level,
        agency_contacts,
        agency_phone,
        agency_address,
        agency_remark,
        agency_sort,
        create_by,
        create_time
        ) values
        <foreach item="Agency" index="index" collection="list" separator=",">
            (
            #{Agency.parentId},
            #{Agency.agencyCategoryId},
            #{Agency.agencyCategoryName},
            #{Agency.areaCode},
            #{Agency.areaName},
            #{Agency.agencyName},
            #{Agency.agencyCode},
            #{Agency.agencyAbbreviation},
            #{Agency.agencyStatus},
            #{Agency.nodeType},
            #{Agency.orgAgencyCode},
            #{Agency.agencyCategoryManageLevel},
            #{Agency.agencyContacts},
            #{Agency.agencyPhone},
            #{Agency.agencyAddress},
            #{Agency.agencyRemark},
            #{Agency.agencySort},
            #{Agency.createBy},
            #{Agency.createTime}
            )
        </foreach>
    </insert>

    <select id="selectAgencyNameByAgencyNameInt" resultType="java.lang.Integer">
        select count(1)
        from agency
        where agency_name = #{agencyName}
    </select>
</mapper>