<?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.ruoyi.ss.city.mapper.CityMapper">

    <resultMap type="CityVO" id="RlCityResult">
        <result property="cityId"    column="city_id"    />
        <result property="name"    column="name"    />
        <result property="pinyin"    column="pinyin"    />
        <result property="adcode"    column="adcode"    />
        <result property="citycode"    column="citycode"    />
        <result property="isOpen"    column="is_open"    />
    </resultMap>

    <sql id="selectRlCityVo">
        select c.city_id, c.name, c.pinyin, c.adcode, c.citycode, cc.name cityName, c.is_open from ss_city c
        left join ss_city_code cc on c.citycode = cc.code
    </sql>

    <select id="selectRlCityList" parameterType="City" resultMap="RlCityResult">
        <include refid="selectRlCityVo"/>
        <where>
            <if test="name != null  and name != ''"> and c.name like concat('%', #{name}, '%')</if>
            <if test="pinyin != null  and pinyin != ''"> and c.pinyin = #{pinyin}</if>
            <if test="adcode != null  and adcode != ''"> and c.adcode = #{adcode}</if>
            <if test="citycode != null  and citycode != ''"> and c.citycode = #{citycode}</if>
            <if test="isOpen != null  and isOpen != ''"> and c.is_open = #{isOpen}</if>
        </where>
        order by c.is_open desc, c.city_id
    </select>

    <select id="selectRlCityByCityId" parameterType="Long" resultMap="RlCityResult">
        <include refid="selectRlCityVo"/>
        where city_id = #{cityId}
    </select>

    <select id="selectRlCityByAdcode" parameterType="String" resultMap="RlCityResult">
        <include refid="selectRlCityVo"/>
        where adcode = #{adcode}
    </select>

    <select id="selectAllCity" parameterType="String"  resultMap="RlCityResult">
        SELECT
            c.city_id,
            c.NAME,
            c.pinyin,
            c.adcode,
            c.citycode,
            cc.NAME cityName,
            c.is_open
        FROM
            ss_city c
                LEFT JOIN ss_city_code cc ON c.citycode = cc.CODE
        WHERE
            c.adcode LIKE '%00' and adcode != '100000'  AND c.adcode NOT LIKE '%0000'
            <if test="cityName != null  and cityName != ''">
                and c.name like concat('%', #{cityName}, '%')
            </if>
        ORDER BY
            c.city_id
    </select>

    <insert id="insertRlCity" parameterType="City" useGeneratedKeys="true" keyProperty="cityId">
        insert into ss_city
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="name != null">name,</if>
            <if test="pinyin != null">pinyin,</if>
            <if test="adcode != null">adcode,</if>
            <if test="citycode != null">citycode,</if>
            <if test="isOpen != null">is_open,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="name != null">#{name},</if>
            <if test="pinyin != null">#{pinyin},</if>
            <if test="adcode != null">#{adcode},</if>
            <if test="citycode != null">#{citycode},</if>
            <if test="isOpen != null">#{isOpen},</if>
         </trim>
    </insert>

    <update id="updateRlCity" parameterType="City">
        update ss_city
        <trim prefix="SET" suffixOverrides=",">
            <if test="name != null">name = #{name},</if>
            <if test="pinyin != null">pinyin = #{pinyin},</if>
            <if test="adcode != null">adcode = #{adcode},</if>
            <if test="citycode != null">citycode = #{citycode},</if>
            <if test="isOpen != null">is_open = #{isOpen},</if>
        </trim>
        where city_id = #{cityId}
    </update>

    <delete id="deleteRlCityByCityId" parameterType="Long">
        delete from ss_city where city_id = #{cityId}
    </delete>

    <delete id="deleteRlCityByCityIds" parameterType="String">
        delete from ss_city where city_id in
        <foreach item="cityId" collection="array" open="(" separator="," close=")">
            #{cityId}
        </foreach>
    </delete>
</mapper>
