<?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.energy.manage.service.mappers.area.AreaMapper">

    <sql id="BASE_AREA_SQL">
        id AS areaId,
        province AS province,
        city AS city,
        area AS area,
        area_code as areaCode
    </sql>

    <select id="findAll" resultType="com.energy.manage.common.po.area.AreaPO">
        SELECT
        <include refid="BASE_AREA_SQL"/>
        FROM sys_areas_info
        ORDER BY id ASC
    </select>


    <select id="findAllProvince" resultType="com.energy.manage.common.po.area.AreaPO">
        SELECT
        <include refid="BASE_AREA_SQL"/>
        FROM sys_areas_info
        WHERE type = 1
        ORDER BY id ASC
    </select>

    <select id="selectCityByPId" resultType="com.energy.manage.common.po.area.AreaPO">
        SELECT
        <include refid="BASE_AREA_SQL"/>
        FROM sys_areas_info
        WHERE type = 2 AND root_id = #{provinceId,jdbcType=INTEGER}
        ORDER BY id ASC
    </select>

    <select id="selectAreaByCId" resultType="com.energy.manage.common.po.area.AreaPO">
        SELECT
        <include refid="BASE_AREA_SQL"/>
        FROM sys_areas_info
        WHERE type = 3 AND root_id = #{cityId,jdbcType=INTEGER}
        ORDER BY id ASC
    </select>

    <update id="updateAreaByAId">
        UPDATE sys_areas_info
        <set>
            <if test="areaType == 2">
                city = #{area,jdbcType=VARCHAR}
            </if>
            <if test="areaType == 3">
                area = #{area,jdbcType=VARCHAR}
            </if>
        </set>
        WHERE id = #{areaId,jdbcType=INTEGER}
    </update>

    <delete id="deleteAreaById">
        DELETE FROM sys_areas_info WHERE id = #{areaId,jdbcType=INTEGER}
    </delete>

    <select id="getEnableProvince" resultType="com.energy.manage.common.po.area.AreaPO">
        SELECT
        <include refid="BASE_AREA_SQL"/>
        FROM sys_areas_info
        WHERE type = 1 AND id NOT IN (SELECT DISTINCT province_id FROM xw_office_areas)
        <if test="null != provinceName and '' != provinceName">
            AND province LIKE CONCAT('%',#{provinceName,jdbcType=VARCHAR},'%')
        </if>
        ORDER BY id ASC
    </select>

    <select id="selectProvinceById" resultType="com.energy.manage.common.po.area.AreaPO">
        SELECT
        <include refid="BASE_AREA_SQL"/>
        FROM sys_areas_info
        WHERE type = 1 AND id IN
        <foreach collection="list" item="item" index="index" open="(" separator="," close=")">#{item,jdbcType=INTEGER}
        </foreach>
        ORDER BY id ASC
    </select>


    <resultMap id="enableAreaMap" type="com.energy.manage.service.domain.vo.area.ProvinceVo">
        <id property="provinceId" column="provinceId" jdbcType="INTEGER"/>
        <result property="province" column="province" jdbcType="VARCHAR"/>
        <collection property="children" ofType="com.energy.manage.service.domain.vo.area.CityVo">
            <id property="cityId" column="cityId" jdbcType="INTEGER"/>
            <result property="city" column="city" jdbcType="VARCHAR"/>
            <result property="areaCode" column="areaCode" jdbcType="VARCHAR"/>
            <collection property="children" ofType="com.energy.manage.service.domain.vo.area.AreaVo">
                <id property="areaId" column="areaId" jdbcType="INTEGER"/>
                <result property="area" column="area" jdbcType="VARCHAR"/>
                <result property="isSelect" column="isSelect" jdbcType="INTEGER"/>
            </collection>
        </collection>
    </resultMap>

    <select id="getEnableArea" resultMap="enableAreaMap">
        SELECT
        pro.id AS provinceId,
        pro.province AS province,
        city.id AS cityId,
        city.city AS city,
        city.area_code  AS areaCode,
        area.id AS areaId,
        area.area AS area

        FROM sys_areas_info pro
        INNER JOIN sys_areas_info city ON city.root_id = pro.id AND city.type = 2
        INNER JOIN sys_areas_info area ON area.root_id = city.id AND area.type = 3
        ORDER BY pro.id ASC,city.id ASC,area.id ASC
    </select>

    <select id="getUserEnableArea" resultMap="enableAreaMap">
        SELECT * FROM (
        (SELECT
        pro.id AS provinceId,
        pro.province AS province,
        city.id AS cityId,
        city.city AS city,
        area.id AS areaId,
        area.area AS area,
        0 AS isSelect
        FROM sys_areas_info pro
        INNER JOIN sys_areas_info city ON city.root_id = pro.id AND city.type = 2
        INNER JOIN sys_areas_info area ON area.root_id = city.id AND area.type = 3
        WHERE pro.id IN (SELECT province_id FROM xw_office_areas WHERE office_id = #{officeId,jdbcType=INTEGER})
        AND area.id NOT IN (
        SELECT area_id
        FROM xw_office_area_user
        <where>
            office_id = #{officeId,jdbcType=INTEGER}
            <if test="roleId != null and roleId > 0">
                AND role_id = #{roleId,jdbcType=INTEGER}
            </if>
        </where>
        ))
        UNION ALL
        (SELECT
        pro.id AS provinceId,
        pro.province AS province,
        city.id AS cityId,
        city.city AS city,
        area.id AS areaId,
        area.area AS area,
        1 AS isSelect
        FROM xw_office_area_user u
        LEFT JOIN sys_areas_info pro ON pro.id = u.province_id
        LEFT JOIN sys_areas_info city ON city.id = u.city_id
        LEFT JOIN sys_areas_info area ON area.id = u.area_id
        WHERE u.user_id = #{userId,jdbcType=INTEGER} AND u.office_id = #{officeId})) t
        ORDER BY t.provinceId ASC,t.cityId ASC,t.areaId ASC
    </select>

    <insert id="insertArea">
        INSERT INTO sys_areas_info (province, city, area, type, root_id)
        VALUES (#{province}, #{city}, #{area}, #{areaType}, #{rootId})
    </insert>

    <select id="getUserProvinceArea" resultType="com.energy.manage.common.po.area.AreaPO">
        SELECT
            pro.id AS areaId,
            pro.province AS province
        FROM sys_areas_info pro
        WHERE pro.id IN (SELECT distinct province_id FROM xw_office_area_user WHERE user_id = #{userId,jdbcType=INTEGER})
        ORDER BY pro.id ASC
    </select>

    <select id="getUserCityByPid" resultType="com.energy.manage.common.po.area.AreaPO">
        SELECT
            pro.id AS areaId,
            pro.province AS province,
            pro.city AS city
        FROM sys_areas_info pro
        WHERE pro.id IN (SELECT distinct city_id FROM xw_office_area_user WHERE user_id = #{userId,jdbcType=INTEGER} AND province_id = #{provinceId,jdbcType=INTEGER})
        ORDER BY pro.id ASC
    </select>

    <select id="finaProvinceByOfficeId" resultType="com.energy.manage.common.po.area.AreaPO">
        SELECT
            pro.id AS areaId,
            pro.province AS province
        FROM sys_areas_info pro
        WHERE pro.id IN (SELECT distinct province_id FROM xw_office_areas WHERE office_id = #{officeId,jdbcType=INTEGER})
        ORDER BY pro.id ASC
    </select>

    <select id="finaProvinceByRegionId" resultType="com.energy.manage.common.po.area.AreaPO">
        SELECT
            pro.id AS areaId,
            pro.province AS province
        FROM sys_areas_info pro
        WHERE pro.id IN (SELECT distinct province_id FROM xw_office_areas WHERE office_id IN (
         SELECT office_id FROM xw_region_office_info WHERE region_id = #{regionId}))
        ORDER BY pro.id ASC
    </select>

    <!-- 批量更改区号 -->
    <update id="updateAreaCodes" parameterType="java.util.List">
        <foreach collection="list" separator=";" item="item" index="index">
            update
            sys_areas_info set
            area_code = #{item.areaCode}
            where 1=1
            and id = #{item.areaId}
        </foreach>
    </update>


    <select id="selectAreaByNames" resultType="com.energy.manage.common.po.area.AreaPO">
        select
        <include refid="BASE_AREA_SQL"/>
        from sys_areas_info
        where
        1=1
        and type = #{type}

       <if test="type == 1">
            and province in
            <foreach item="item" index="index" collection="list" open="("
                     separator="," close=")">
                #{item}
            </foreach>
       </if>

       <if test="type == 2">
           and city in
        <foreach item="item" index="index" collection="list" open="("
                 separator="," close=")">
            #{item}
        </foreach>
       </if>

    </select>

</mapper>
