<?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="org.jeecg.modules.geoAnalysis.mapper.GeoAnalysisMapper">
    <select id="SelectByTb" resultType="org.jeecg.modules.geoAnalysis.entity.ZYFXPZB" parameterType="String">
        select *
        from geoanalysisconfig
        where MTABLENAME = #{MTABLENAME}
    </select>
    <select id="SelectByTbs" resultType="org.jeecg.modules.geoAnalysis.entity.ZYFXPZB" parameterType="String">
        select *
        from geoanalysisconfig
        where MTABLENAME = #{MTABLENAME}
          and atableename = #{atableename}
    </select>
    <select id="DrawMap" resultType="hashmap" parameterType="hashmap">

        select objectid, ST_AsText(e.info) info, AREA, ${DLMC}
        from (
                 select objectid,
                        st_intersection(a.geom, st_geomfromtext(#{shape}, 4490))                    info,
                        st_area(st_intersection(a.geom,
                                                st_geomfromtext(#{shape}, 4490)), false) * 0.001 AS AREA,
                        ${DLMC}
                 from ${table} a
                 where ST_Intersects(a.geom, st_geomfromtext(
                         #{shape}, 4490)) = 'true') e
    </select>
    <select id="DrawMapByShape" resultType="hashmap" parameterType="hashmap">

        select objectid, ST_AsText(e.info) info, AREA, ${DLMC} from (
        select objectid,
        st_intersection(a.geom, (select b.geom from ${tables} b where
        st_within(ST_SetSRID(ST_MakePoint(
        <foreach collection="shape" item="item" index="index" separator=",">
            #{item}
        </foreach>
        ),4490),geom)
        )) info,
        st_area(st_intersection(a.geom,
        (select b.geom from ${tables} b where st_within(ST_SetSRID(ST_MakePoint(
        <foreach collection="shape" item="item" index="index" separator=",">
            #{item}
        </foreach>
        ),4527),ST_SetSRID(b.geom,4527))
        )), false) * 0.001 AS AREA,
        ${DLMC}
        from ${table} a
        where ST_Intersects(a.geom, (select b.geom from ss_dkxx b where
        st_within(ST_SetSRID(ST_MakePoint(
        <foreach collection="shape" item="item" index="index" separator=",">
            #{item}
        </foreach>),4490),b.geom)
        )) = 'true'
        ) e
        <!--                select objectid,e.info.GET_WKT() info,AREA,${DLMC} from (-->
        <!--                select objectid,SDO_GEOM.SDO_INTERSECTION(a.shape,-->
        <!--                (select b.shape from ${tables} b where-->
        <!--                SDO_RELATE(b.shape,sdo_geometry(2003,'4490',null,sdo_elem_info_array(1,1003,1),sdo_ordinate_array(-->
        <!--                <foreach collection="shape" item="item" index="index" separator=",">-->
        <!--                    #{item}-->
        <!--                </foreach>-->
        <!--                )),'mask=AnyInteract querytype=WINDOW') = 'TRUE'),0.005)-->
        <!--                info,SDO_GEOM.SDO_AREA(SDO_CS.TRANSFORM(SDO_GEOM.SDO_INTERSECTION(a.shape,-->
        <!--                (select b.shape from ${tables} b where-->
        <!--                SDO_RELATE(b.shape,sdo_geometry(2003,'4490',null,sdo_elem_info_array(1,1003,1),sdo_ordinate_array(-->
        <!--                <foreach collection="shape" item="item" index="index" separator=",">-->
        <!--                    #{item}-->
        <!--                </foreach>-->
        <!--                )),'mask=AnyInteract querytype=WINDOW') = 'TRUE'),0.005),4490),0.05) * 0.0001 AS AREA ,${DLMC}-->
        <!--                from ${table} a-->
        <!--                where SDO_RELATE(a.shape,-->
        <!--                (select b.shape from ${tables} b where-->
        <!--                SDO_RELATE(b.shape,sdo_geometry(2003,'4490',null,sdo_elem_info_array(1,1003,1),sdo_ordinate_array(-->
        <!--                <foreach collection="shape" item="item" index="index" separator=",">-->
        <!--                    #{item}-->
        <!--                </foreach>-->
        <!--                )),'mask=AnyInteract querytype=WINDOW') = 'TRUE'),'mask=AnyInteract querytype=WINDOW') = 'TRUE') e-->

    </select>
    <select id="SelectDetails" resultType="hashmap" parameterType="hashmap">
        select  <foreach collection="shape" item="item" index="index" separator=",">
        ${item}
    </foreach>,t.shape.GET_WKT() as WKT
        from ${table} t where t.OBJECTID=#{id}
    </select>
    <select id="SelectByTable" resultType="org.jeecg.modules.commonQueryConfig.entity.CommonQueryConfig"
            parameterType="String">
        select *
        from COMMON_QUERY_CONFIG
        where TABLEALIAS = #{TABLEALIAS}
        order by create_time
    </select>
    <select id="SelectGdxmjbxx" resultType="hashmap" parameterType="hashmap">
        select t.xm_mc 项目名称,t.xzqh 行政区划,t.dz_ba_bh 电子监管号,t.bh 合同编号,t.yddw 用地单位,t.td_yt 土地用途,t.gy_mj "出让面积(公顷)",t.cr_nx
        出让年份,t.srr 受让人 ,to_char(t.qd_rq,'yyyy-mm-dd') 签订日期,t.je "出让价款(万元)",t.crfs 出让方式,t.jz_mj as "建筑面积(平方米)",t.Min_Rjl
        as 最小容积率,t.max_rjl as 最大容积率,t.min_lhl as 最小绿化率,t.max_lhl as 最大绿化率,t.min_jz_md as 最小建筑密度,t.max_jz_md as
        最大建筑密度,t.min_jz_gd as 最小建筑高度,t.max_jz_gd as 最大建筑高度 from gd_xmjbxx t where t.id=(select b.xmid from gd_dkxx b
        where SDO_RELATE(b.shape,sdo_geometry(2003,'4326',null,sdo_elem_info_array(1,1003,1),sdo_ordinate_array(
        <foreach collection="shape" item="item" index="index" separator=",">
            #{item}
        </foreach>
        )),'mask=AnyInteract querytype=WINDOW') = 'TRUE')
    </select>


    <select id="exportShp" resultType="hashmap" parameterType="String">
        SELECT t.dkmc dkmc, t.shape.GET_WKT() geom
        from GGG t
        <where>
            <if test="id != null">
                objectid=#{id}
            </if>
        </where>
    </select>
</mapper>
