<?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">

<!-- rscp_image_cover -->
<mapper namespace="com.upyuns.platform.rs.datacenter.mapper.RscpImageCoverMapper">
    <!-- This code was generated by TableGo tools, mark 1 begin. -->
    <!-- 字段映射 -->
    <resultMap id="rscpImageCoverMap" type="com.upyuns.platform.rs.datacenter.entity.RscpImageCover">
        <id column="id" property="id" jdbcType="VARCHAR" />
        <result column="type" property="type" jdbcType="INTEGER" />
        <result column="name" property="name" jdbcType="VARCHAR" />
        <result column="ratio" property="ratio" jdbcType="INTEGER" />
        <result column="range" property="range" jdbcType="VARCHAR" />
        <result column="date_year" property="dateYear" jdbcType="VARCHAR" />
        <result column="date_month" property="dateMonth" jdbcType="VARCHAR" />
        <result column="is_del" property="isDel" jdbcType="INTEGER" />
        <result column="crt_time" property="crtTime" jdbcType="INTEGER" />
        <result column="upd_time" property="updTime" jdbcType="INTEGER" />
        <result column="area_code" property="areaCode" jdbcType="INTEGER" />
        <result column="area_name" property="areaName" jdbcType="VARCHAR" />
    </resultMap>
    <!-- This code was generated by TableGo tools, mark 1 end. -->

    <!-- This code was generated by TableGo tools, mark 2 begin. -->
    <!-- 表查询字段 -->
    <sql id="allColumns">
        ric.id, ric.type, ric.name, ric.ratio, ric.range, ric.date_year, ric.date_month,
        ric.is_del, ric.crt_time, ric.upd_time, ric.area_code, ric.area_name
    </sql>

    <sql id="allColumns2">
        ric.id, ric.type, ric.name, ric.ratio, ric.date_year, ric.date_month,
        ric.is_del, ric.crt_time, ric.upd_time, ric.area_code, ric.area_name
    </sql>

    <!-- This code was generated by TableGo tools, mark 2 end. -->

<!--    <insert id="coverSecondInsert">-->
<!--        INSERT INTO-->

<!--        rscp_image_cover-->

<!--        (-->
<!--        range, type, name, ratio, date_year, date_month, crt_time, upd_time, area_code, area_name, cover_acreage, region_acreage-->
<!--        )-->
<!--        (-->
<!--        select temp.unrange, #{type}, #{name}, cover_acreage/region_acreage, #{dateYear}, #{dateMonth}, #{crtTime}, #{updTime}, #{areaCode}, #{areaName},-->
<!--        cover_acreage, region_acreage-->

<!--        from-->

<!--        ( select ridt_temp.unrange, cast(st_area(ST_Intersection(-->
<!--        <if test="oldId != null">-->
<!--            ST_Intersection(ridt_temp.unrange, oric_temp.range)-->
<!--        </if>-->
<!--        <if test="oldId == null">-->
<!--            ridt_temp.unrange-->
<!--        </if>-->
<!--        , area_temp.geom),true)/1000/1000 as numeric(10,3)) as cover_acreage, cast(st_area(area_temp.geom,true)/1000/1000 as numeric(10,3)) as region_acreage-->
<!--        from-->
<!--        (-->
<!--        select st_union(ridt.range) as unrange-->
<!--        from rscp_image_data_total ridt-->
<!--        <where>-->
<!--            <if test="dataIds !=null and dataIds.size()>0">-->
<!--                and ( ridt.id in-->
<!--                <foreach collection="dataIds" item="dataIdsVal" open="(" close=")" separator=",">-->
<!--                    #{dataIdsVal}-->
<!--                </foreach>-->
<!--                )-->
<!--            </if>-->
<!--        </where>-->
<!--        ) ridt_temp-->
<!--        , (-->
<!--        select geom from rscp_area_info rai-->
<!--        <where>-->
<!--            <if test="areaCode != null">-->
<!--                and rai.admincode = ''||#{areaCode}-->
<!--            </if>-->
<!--        </where>-->
<!--        ) area_temp-->
<!--        <if test="oldId != null">-->
<!--            , (-->
<!--            select range from rscp_image_cover oric-->
<!--            <where>-->
<!--                and oric.id = #{oldId}-->
<!--            </where>-->
<!--            ) oric_temp-->
<!--        </if>-->
<!--        ) temp-->
<!--        )-->
<!--    </insert>-->

    <insert id="coverInsert">
        INSERT INTO

        rscp_image_cover

        (
            range, type, name, ratio, date_year, date_month, crt_time, upd_time, area_code, area_name, cover_acreage, region_acreage, image_num, range_small
        )
        (
            select temp.unrange, #{type}, #{name}, cast(((cover_acreage/region_acreage) * 100) as numeric(5,2)), #{dateYear}, #{dateMonth}, #{crtTime}, #{updTime}, #{areaCode}, #{areaName},
            cover_acreage, region_acreage, #{imageNum}, temp.unrange_small
<!--        ST_SimplifyPreserveTopology(temp.unrange_small,0.005)-->
<!--        , ST_SimplifyPreserveTopology(temp.unrange,0.05)-->

            from

            ( select
                ST_Intersection(
                    <if test="oldId != null">
                        st_union(ridt_temp.unrange, oric_temp.range)
                    </if>
                    <if test="oldId == null">
                        ridt_temp.unrange
                    </if>
                , area_temp.range_small) as unrange_small
                , ST_Intersection(
                    <if test="oldId != null">
                        st_union(ridt_temp.unrange, oric_temp.range)
                    </if>
                    <if test="oldId == null">
                        ridt_temp.unrange
                    </if>
                , area_temp.geom) as unrange
                , cast(st_area(ST_Intersection(
                <if test="oldId != null">
                    st_union(ridt_temp.unrange, oric_temp.range)
                </if>
                <if test="oldId == null">
                    ridt_temp.unrange
                </if>
                , area_temp.geom),true)/1000/1000 as numeric(10,3)) as cover_acreage, cast(st_area(area_temp.geom,true)/1000/1000 as numeric(10,3)) as region_acreage
                from
                (
                    select st_union(ridt.range) as unrange
                    from rscp_image_data_total ridt
                    <where>
                        <if test="dataIds !=null and dataIds.size()>0">
                            and ( ridt.id in
                            <foreach collection="dataIds" item="dataIdsVal" open="(" close=")" separator=",">
                                #{dataIdsVal}
                            </foreach>
                            )
                        </if>
                    </where>
                ) ridt_temp
                , (
                    select geom, geom_samll as range_small from rscp_area_info rai
                    <where>
                        <if test="areaCode != null">
                            and rai.admincode = ''||#{areaCode}
                        </if>
                    </where>
                ) area_temp
                <if test="oldId != null">
                    , (
                    select range from rscp_image_cover oric
                    <where>
                        and oric.id = #{oldId}
                    </where>
                    ) oric_temp
                </if>
            ) temp
        )
    </insert>

    <select id="queryDataList" resultMap="rscpImageCoverMap">
        select st_asgeojson(range_small,16,0) as range, *
        from rscp_image_cover ric
        <where>
            and ric.is_del = 0
            <if test="type != null">
                and ric.type  =  #{type}
            </if>
            <if test="dateYear != null">
                and ric.date_year  =  #{dateYear}
            </if>
            <if test="dateMonth != null">
                and ric.date_month  = #{dateMonth}
            </if>
            <if test="areaCode != null">
                and ric.area_code = #{areaCode}
            </if>
        </where>
    </select>

    <select id="bgQueryDataList" resultMap="rscpImageCoverMap">
        select
        <include refid="allColumns2"></include>
        from rscp_image_cover ric
        <where>
            and ric.is_del = 0
            <if test="type != null">
                and ric.type  =  #{type}
            </if>
            <if test="dateYear != null">
                and ric.date_year  =  #{dateYear}
            </if>
            <if test="dateMonth != null">
                and ric.date_month  = #{dateMonth}
            </if>
            <if test="areaCode != null">
                and ric.area_code = #{areaCode}
            </if>
<!--            <if test="imageSatelliteType != null">-->
<!--                and image_satellite_type like '%' || #{imageSatelliteType} || '%'-->
<!--            </if>-->
<!--            <if test="resolution != null">-->
<!--                and #{resolution} = any(image_resolution)-->
<!--            </if>-->
<!--            <if test="startDateTime != null">-->
<!--                and image_take_time &gt;= #{startDateTime}-->
<!--            </if>-->
<!--            <if test="endDateTime != null">-->
<!--                and image_take_time &lt;= #{endDateTime}-->
<!--            </if>-->
<!--            <if test="imageSatelliteType != null">-->
<!--                image_satellite_type = #{imageSatelliteType}-->
<!--            </if>-->
        </where>
    </select>

</mapper>