<?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.system.mapper_shate.RegionalFactorMapper">
    <resultMap id="RM_Seeding" type="com.ruoyi.system.domain_shate.WaterFactor">
        <result property="id" column="id"/>
        <result property="zone" column="zone"/>
        <result property="level1" column="level1"/>
        <result property="level2" column="level2"/>
        <result property="level3" column="level3"/>
        <result property="level4" column="level4"/>
        <result property="level5" column="level5"/>
        <result property="imageDate" column="Image_date"/>
        <result property="productCode1" column="Product_code1"/>
        <result property="productCode2" column="Product_code2"/>
        <result property="year" column="year1"/>
        <result property="createdBy" column="created_by"/>
        <result property="craeteTime" column="created_time"/>
    </resultMap>


    <resultMap id="RM_Road" type="com.ruoyi.system.domain_shate.RoadFactor">
        <result property="id" column="id"/>
        <result property="zone" column="zone"/>
        <result property="level1" column="level1"/>
        <result property="level2" column="level2"/>
        <result property="level3" column="level3"/>
        <result property="level4" column="level4"/>
        <result property="level5" column="level5"/>
        <result property="imageDate" column="Image_date"/>
        <result property="productCode1" column="Product_code1"/>
        <result property="productCode2" column="Product_code2"/>
        <result property="year" column="year1"/>
        <result property="createdBy" column="created_by"/>
        <result property="craeteTime" column="created_time"/>
    </resultMap>

    <resultMap id="RM_Region" type="com.ruoyi.system.domain_shate.RegionIndex">
        <result property="id" column="id"/>
        <result property="zone" column="zone"/>
        <result property="waterArea" column="Water_area"/>
        <result property="roadLength" column="Road_length"/>
        <result property="imageDate" column="Image_date"/>
        <result property="productCode" column="Product_code"/>
        <result property="productCode1" column="Product_code1"/>
        <result property="createdBy" column="created_by"/>
        <result property="craeteTime" column="created_time"/>
        <result property="year" column="year1"/>
    </resultMap>

    <resultMap id="RM_Climate" type="com.ruoyi.system.domain_shate.Climate">
        <result property="zone" column="zone"/>
        <result property="temperature" column="temperature"/>
        <result property="precipitation" column="precipitation"/>
        <result property="humidity" column="humidity"/>
        <result property="wind" column="wind"/>
    </resultMap>

    <sql id="columns">
	    <![CDATA[
        ID,zone,level5,year1,level1,level2,level3,level4,Image_date,Product_code1,Product_code2,created_by
        ]]>
	</sql>
    <sql id="Region">
	    <![CDATA[
        ID,zone,year1,Water_area,Road_length,Image_date,Product_code,created_by
        ]]>
	</sql>

    <sql id="Climate">
	    <![CDATA[
        ID,zone,temperature,precipitation,humidity,wind
        ]]>
	</sql>

    <select id="sqlClimate" resultMap="RM_Climate">
        SELECT <include refid="Climate"/> FROM climate_index
        where 1=1
        <if test="zone!= null and zone !=''">
            AND zone = #{zone}
        </if>
    </select>

    <select id="sqlWater" resultMap="RM_Seeding">
        SELECT  <include refid="columns"/>  FROM  water
        WHERE 1=1
        <if test="zone!= null and zone !=''">
            AND zone = #{zone}
        </if>
        <if test="year!= null and year !=''">
            AND year1 = #{year}
        </if>
    </select>
    <select id="sqlYear" resultMap="RM_Seeding">
        SELECT  distinct year1,ZONE  FROM  water
    </select>

    <select id="sqlRegion" resultMap="RM_Region">
        select a.zone zone,a.year1 year1,a.water_area water_area,
        b.road_length road_length,a.image_date image_date,
        a.product_code product_code,b.product_code product_code1
        FROM water_area a join road_length b
        on a.image_date=b.image_date
        and a.zone=b.zone
        WHERE 1=1
        <if test="zone!= null and zone !=''">
            AND a.zone = #{zone}
        </if>
        <if test="year!= null and year !=''">
            AND a.year1 = #{year}
        </if>
    </select>

    <select id="sqlYear1" resultMap="RM_Region">
        SELECT  distinct year1,ZONE  FROM  water_area
    </select>

    <select id="sqlRoad" resultMap="RM_Road">
        SELECT  <include refid="columns"/>  FROM  road
        WHERE 1=1
        <if test="zone!= null and zone !=''">
            AND zone = #{zone}
        </if>
        <if test="year!= null and year !=''">
            AND year1 = #{year}
        </if>
    </select>
    <select id="sqlYear2" resultMap="RM_Road">
        SELECT  distinct year1,ZONE  FROM  road
    </select>

    <insert id="InsertWater">
        <![CDATA[
        INSERT INTO water (
            zone ,
            level1,
            level2 ,
            level3 ,
            level4,
            level5,
            Image_date ,
            Product_code1 ,
            Product_code2,
            year1,
            created_by
        ) VALUES (
                     #{zone,jdbcType=VARCHAR},
                     #{level1,jdbcType=DOUBLE},
                     #{level2,jdbcType=DOUBLE},
                     #{level3,jdbcType=DOUBLE},
                     #{level4,jdbcType=DOUBLE},
                     #{level5,jdbcType=DOUBLE},
                     #{imageDate,jdbcType=VARCHAR},
                     #{productCode1,jdbcType=VARCHAR},
                     #{productCode2,jdbcType=VARCHAR},
                     #{year,jdbcType=VARCHAR},
                     #{createdBy,jdbcType=VARCHAR}
                 )
        ]]>
    </insert>
    <insert id="InsertRoad">
        <![CDATA[
        INSERT INTO road (
            zone ,
            level1,
            level2 ,
            level3 ,
            level4,
            level5,
            Image_date ,
            Product_code1 ,
            Product_code2,
            year1,
            created_by
        ) VALUES (
                     #{zone,jdbcType=VARCHAR},
                     #{level1,jdbcType=DOUBLE},
                     #{level2,jdbcType=DOUBLE},
                     #{level3,jdbcType=DOUBLE},
                     #{level4,jdbcType=DOUBLE},
                     #{level5,jdbcType=DOUBLE},
                     #{imageDate,jdbcType=VARCHAR},
                     #{productCode1,jdbcType=VARCHAR},
                     #{productCode2,jdbcType=VARCHAR},
                     #{year,jdbcType=VARCHAR},
                     #{createdBy,jdbcType=VARCHAR}
                 )
        ]]>
    </insert>


    <insert id="InsertWaterArea">
        <![CDATA[
        INSERT INTO water_area (
            zone ,
            Water_area,
            Image_date ,
            Product_code,
            year1
        ) VALUES (
                     #{zone,jdbcType=VARCHAR},
                     #{waterArea,jdbcType=DOUBLE},
                     #{imageDate,jdbcType=VARCHAR},
                     #{productCode,jdbcType=VARCHAR},
                     #{year,jdbcType=VARCHAR}
                 )
        ]]>
    </insert>
    <insert id="InsertRoadLength">
        <![CDATA[
        INSERT INTO road_length (
            zone ,
            road_length ,
            image_date ,
            product_code
        ) VALUES (
                     #{zone,jdbcType=VARCHAR},
                     #{roadLength,jdbcType=DOUBLE},
                     #{imageDate,jdbcType=VARCHAR},
                     #{productCode,jdbcType=VARCHAR}
                 )
        ]]>
    </insert>


</mapper>