<?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.elephant.framework.galaxy.aroad.module.zhjs.mapper.ZhjsGcxmMapper">

  <resultMap id="zhjsGcxmMap" type="com.elephant.framework.galaxy.aroad.module.zhjs.entity.ZhjsGcxm">
        <id property="id" column="id"/>
        <result property="code" column="code"/>
        <result property="proname" column="proname"/>
        <result property="reportitemname" column="reportItemName"/>
        <result property="proabbreviation" column="proabbreviation"/>
        <result property="attribute" column="attribute"/>
        <result property="property" column="property"/>
        <result property="level" column="level"/>
        <result property="constructionunit" column="constructionunit"/>
        <result property="designunit" column="designunit"/>
        <result property="planstartdate" column="planstartdate"/>
        <result property="planenddate" column="planenddate"/>
        <result property="startdate" column="startdate"/>
        <result property="enddate" column="enddate"/>
        <result property="quantities" column="quantities"/>
        <result property="bid" column="bid"/>
        <result property="provinceinvest" column="provinceinvest"/>
        <result property="centreinvest" column="centreinvest"/>
        <result property="estimate" column="estimate"/>
        <result property="planno" column="planno"/>
        <result property="branched" column="branched"/>
        <result property="address" column="address"/>
        <result property="structure" column="structure"/>
        <result property="proinfo" column="proinfo"/>
        <result property="sort" column="sort"/>
        <result property="managesorgid" column="managesOrgId"/>
        <result property="managesorgname" column="managesOrgName"/>
        <result property="city" column="city"/>
        <result property="startstation" column="startStation"/>
        <result property="endstation" column="endStation"/>
        <result property="xy" column="xy"/>
        <result property="invmodel" column="invmodel"/>
        <result property="mileage" column="mileage"/>
        <result property="remark" column="remark"/>
        <result property="introduce" column="introduce"/>
        <result property="type" column="type"/>
        <result property="legalperson" column="legalperson"/>
        <result property="spanlength" column="spanlength"/>
  </resultMap>

      <select id="getZhjsXmqkByXmid" resultType="com.elephant.framework.galaxy.aroad.module.zhjs.entity.ZhjsXmqk">
            select
                m.id,m.estimate,m.provinceinvest,
                m.centreinvest,m.planstartdate,
                m.planenddate,m.introduce,
                b.plancontent,
                h.provinceIssuedAmount,h.ccinIssuedAmount
            from t_zhjs_gcxm m left join t_zhjs_xmtzjh h on m.id=h.proid left join t_zhjs_hzsb b on m.id=b.proid
            <where>
                <if test="xmid!='' and xmid!=null">
                      m.id=#{xmid}
                </if>
            </where>
      </select>
    <select id="getZhjsZhzsThreeAndOne" resultType="com.elephant.framework.galaxy.aroad.module.common.entity.KeyValues">
        select city as name,count(*) as number from t_zhjs_gcxm
        <where>
            <if test="type!=null">
                invmodel=#{type}
            </if>
            <if test="sx!=null">
                and attribute=#{sx}
            </if>
        </where>
        GROUP BY city
    </select>

    <select id="getZhjsZhzsTwo" resultType="java.lang.Integer">
        select count(*) from t_zhjs_gcxm
        <where>
            <if test="type!=null">
                invmodel=#{type}
            </if>
            <if test="sx!=null">
                and attribute=#{sx}
            </if>
            <if test="city!='' and city!=null">
                and city like concat('%',#{city},'%')
            </if>
        </where>
    </select>

    <select id="getGcxmAndShape" resultType="com.elephant.framework.galaxy.aroad.module.zhjs.entity.ZhjsGcxmShape">
        select g.*,s.shape from t_zhjs_gcxm g LEFT JOIN t_zhjc_xmjc_ghxm_shape s
         ON substring(g.proname,1,char_length(SUBSTRING(g.proname,1,if(LOCATE("（",g.proname)!=0,LOCATE("（",g.proname)-1,char_length(g.proname))))-4)=s.name
        <where>
            <if test="attribute!=null">
              g.attribute=#{attribute}
            </if>
            <if test="invmodel!=null">
              and g.invmodel=#{invmodel}
            </if>
        </where>
    </select>
</mapper>
