<?xml version="1.0" encoding="gbk" standalone="no"?>
<!DOCTYPE sqlMap PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN" "http://ibatis.apache.org/dtd/sql-map-2.dtd">
<sqlMap namespace="">
    <!-- 	<typeAlias alias="Zxldbean" type="cn.vp.XXX.model.ZxldBean"/> -->
    <typeAlias alias="Zxld" type="cn.im.zxld.zxldBean"/>

    <sql id="condition_Zxld">
        where 1=1
        <isNotEmpty prepend='AND ' property='LIKExh'>
            (xh like '%$LIKExh$%' )
        </isNotEmpty>
        <isNotEmpty prepend='AND ' property='xh'>
            (xh = #xh# )
        </isNotEmpty>
        <isNotEmpty prepend='AND ' property='LIKEldbh'>
            (ldbh like '%$LIKEldbh$%' )
        </isNotEmpty>
        <isNotEmpty prepend='AND ' property='ldbh'>
            (ldbh = #ldbh# )
        </isNotEmpty>
        <isNotEmpty prepend='AND ' property='LIKEldmc'>
            (ldmc like '%$LIKEldmc$%' )
        </isNotEmpty>
        <isNotEmpty prepend='AND ' property='ldmc'>
            (ldmc = #ldmc# )
        </isNotEmpty>
        <isNotEmpty prepend='AND ' property='LIKEgxdwbh'>
            (gxdwbh like '%$LIKEgxdwbh$%' )
        </isNotEmpty>
        <isNotEmpty prepend='AND ' property='gxdwbh'>
            (gxdwbh = #gxdwbh# )
        </isNotEmpty>
        <isNotEmpty prepend='AND ' property='LIKEgxdwmc'>
            (gxdwmc like '%$LIKEgxdwmc$%' )
        </isNotEmpty>
        <isNotEmpty prepend='AND ' property='gxdwmc'>
            (gxdwmc = #gxdwmc# )
        </isNotEmpty>
        <isNotEmpty prepend='AND ' property='LIKEjd'>
            (jd like '%$LIKEjd$%' )
        </isNotEmpty>
        <isNotEmpty prepend='AND ' property='jd'>
            (jd = #jd# )
        </isNotEmpty>
        <isNotEmpty prepend='AND ' property='LIKEwd'>
            (wd like '%$LIKEwd$%' )
        </isNotEmpty>
        <isNotEmpty prepend='AND ' property='wd'>
            (wd = #wd# )
        </isNotEmpty>
        <isNotEmpty prepend='AND ' property='LIKEfx'>
            (fx like '%$LIKEfx$%' )
        </isNotEmpty>
        <isNotEmpty prepend='AND ' property='fx'>
            (fx = #fx# )
        </isNotEmpty>
        <isNotEmpty prepend='AND ' property='LIKEqs'>
            (qs like '%$LIKEqs$%' )
        </isNotEmpty>
        <isNotEmpty prepend='AND ' property='qs'>
            (qs = #qs# )
        </isNotEmpty>
        <isNotEmpty prepend='AND ' property='LIKEbz'>
            (bz like '%$LIKEbz$%' )
        </isNotEmpty>
        <isNotEmpty prepend='AND ' property='bz'>
            (bz = #bz# )
        </isNotEmpty>
        <isNotEmpty prepend='AND ' property='LIKEip'>
            (ip like '%$LIKEip$%' )
        </isNotEmpty>
        <isNotEmpty prepend='AND ' property='ip'>
            (ip = #ip# )
        </isNotEmpty>
        <isNotEmpty property='tableparam'>
            $tableparam$
        </isNotEmpty>
        <isNotEmpty property='order'>
            $order$
        </isNotEmpty>
    </sql>
    <select id="query_Zxld" resultClass="java.util.HashMap" parameterClass="java.util.Map">
        select xh, ldbh, ldmc, gxdwbh, gxdwmc, jd, wd, fx, qs, bz from its.ksh_zxld b
        <include refid="condition_Zxld"/>
    </select>
    <!-- add object -->
    <insert id="insert_Zxld" parameterClass="java.util.Map">
        insert into
        its.ksh_zxld(
        <dynamic prepend=" ">
            <isNotEmpty prepend=',' property='xh'>xh</isNotEmpty>
            <isNotEmpty prepend=',' property='ldbh'>ldbh</isNotEmpty>
            <isNotEmpty prepend=',' property='ldmc'>ldmc</isNotEmpty>
            <isNotEmpty prepend=',' property='gxdwbh'>gxdwbh</isNotEmpty>
            <isNotEmpty prepend=',' property='gxdwmc'>gxdwmc</isNotEmpty>
            <isNotEmpty prepend=',' property='jd'>jd</isNotEmpty>
            <isNotEmpty prepend=',' property='wd'>wd</isNotEmpty>
            <isNotEmpty prepend=',' property='fx'>fx</isNotEmpty>
            <isNotEmpty prepend=',' property='qs'>qs</isNotEmpty>
            <isNotEmpty prepend=',' property='bz'>bz</isNotEmpty>
        </dynamic>
        <![CDATA[
	        ) values (
	    ]]>
        <dynamic prepend=" ">
            <isNotEmpty prepend=',' property='xh'>#xh#</isNotEmpty>
            <isNotEmpty prepend=',' property='ldbh'>#ldbh#</isNotEmpty>
            <isNotEmpty prepend=',' property='ldmc'>#ldmc#</isNotEmpty>
            <isNotEmpty prepend=',' property='gxdwbh'>#gxdwbh#</isNotEmpty>
            <isNotEmpty prepend=',' property='gxdwmc'>#gxdwmc#</isNotEmpty>
            <isNotEmpty prepend=',' property='jd'>#jd#</isNotEmpty>
            <isNotEmpty prepend=',' property='wd'>#wd#</isNotEmpty>
            <isNotEmpty prepend=',' property='fx'>#fx#</isNotEmpty>
            <isNotEmpty prepend=',' property='qs'>#qs#</isNotEmpty>
            <isNotEmpty prepend=',' property='bz'>#bz#</isNotEmpty>

        </dynamic>
        <![CDATA[
	        )
	    ]]>
    </insert>

    <!-- edit object  -->
    <update id="update_Zxld" parameterClass="java.util.Map">
        update its.ksh_zxld
        <dynamic prepend="set ">
            <isNotEmpty prepend=',' property='xh'>xh=#xh#</isNotEmpty>
            <isNotEmpty prepend=',' property='ldbh'>ldbh=#ldbh#</isNotEmpty>
            <isNotEmpty prepend=',' property='ldmc'>ldmc=#ldmc#</isNotEmpty>
            <isNotEmpty prepend=',' property='gxdwbh'>gxdwbh=#gxdwbh#</isNotEmpty>
            <isNotEmpty prepend=',' property='gxdwmc'>gxdwmc=#gxdwmc#</isNotEmpty>
            <isNotEmpty prepend=',' property='jd'>jd=#jd#</isNotEmpty>
            <isNotEmpty prepend=',' property='wd'>wd=#wd#</isNotEmpty>
            <isNotEmpty prepend=',' property='fx'>fx=#fx#</isNotEmpty>
            <isNotEmpty prepend=',' property='qs'>qs=#qs#</isNotEmpty>
            <isNotEmpty prepend=',' property='bz'>bz=#bz#</isNotEmpty>
        </dynamic>
        where xh = #XGTJxh#

    </update>

    <update id="update_ZxldRemarks" parameterClass="java.util.Map">
		update its.ksh_zxld set  bz=#bz#
        <isNotEmpty prepend="," property="repairstate">
            repairstate = #repairstate#
        </isNotEmpty>
        <isNotEmpty prepend="," property="state">
            state = #state#
        </isNotEmpty>
		  where  xh = #XGTJxh#
	</update>


    <update id="updateZxldByXh" parameterClass="java.util.Map">
		update its.ksh_zxld set  jd = #jd# , wd=#wd#  where  xh = #xh#
	</update>

    <update id="update_ZxldCurrentState" parameterClass="java.util.Map">
		update its.ksh_zxld set repairstate=#repairstate#   where  xh = #XGTJxh#
	</update>

    <!-- delete object -->
    <delete id="delete_Zxld" parameterClass="java.util.Map">
		delete from its.ksh_zxld where (xh = #xh#  )
	</delete>

    <!-- batch delete object -->
    <delete id="batch_delete_Zxld" parameterClass="java.util.Map">
        delete from its.ksh_zxld where xh in
        <iterate open="(" close=")" conjunction="," property="keyword">
            #keyword[]#
        </iterate>
    </delete>

    <!-- query objects by page-->
    <select id="query_ZxldTask" resultClass="java.util.HashMap" parameterClass="java.util.Map">
        select a.*,b.csdm,b.csmc from (
        select * from its.ksh_zxld
        <include refid="condition_Zxld"/>
        )a left join xtcl b on b.csdm= detectionswitch and b.csdl = 'ywTaskEnable'

    </select>

    <!-- query objects totalcount -->
    <select id="count_Zxld" resultClass="java.lang.Integer" parameterClass="java.util.Map">
        select count(*) from its.ksh_zxld
        <include refid="condition_Zxld"/>
    </select>

    <!-- batch add objects -->
    <insert id="batch_insert_Zxld" parameterClass="java.util.List">
        <![CDATA[
          INSERT
      	]]>
        <iterate conjunction=" ">
            into its.ksh_zxld(
            <![CDATA[
			xh, ldbh, ldmc, gxdwbh, gxdwmc, jd, wd, fx, qs, bz			)values
			 ]]>
            <![CDATA[
				(
				#list[].xh#,#list[].ldbh#,#list[].ldmc#,#list[].gxdwbh#,#list[].gxdwmc#,#list[].jd#,#list[].wd#,#list[].fx#,#list[].qs#,#list[].bz#
				)
			]]>
        </iterate>
    </insert>


    <select id="query_ZxldStatus" resultClass="java.util.HashMap" parameterClass="java.util.Map">
        select t.xh, ldbh, ldmc, gxdwbh, gxdwmc, jd, wd, fx, qs, bz,state from its.ksh_zxld t
        <include refid="condition_Zxld"/>

    </select>
    <select id="count_ZxldStatus" resultClass="java.lang.Integer" parameterClass="java.util.Map">
        select count(t.xh) from its.ksh_zxld t
        <include refid="condition_Zxld"/>
    </select>

    <select id="query_ZxldStatusUnonline" resultClass="java.util.HashMap" parameterClass="java.util.Map">
        select b.xh, ldbh, ldmc, gxdwbh, gxdwmc, jd, wd, fx, qs, bz,state sbzt
        from its.ksh_zxld t
        <include refid="condition_Zxld"/>
    </select>
    <select id="count_ZxldStatusUnonline" resultClass="java.lang.Integer" parameterClass="java.util.Map">
        select count(b.xh)
        from its.ksh_zxld b
        <include refid="condition_Zxld"/>

    </select>

    <select id="query_ZxldHistoryState" resultClass="java.util.HashMap" parameterClass="java.util.Map">
        select a.xh, ldbh, ldmc, gxdwbh, gxdwmc, jd, wd, fx, qs, bz,to_char(b.create_time,'yyyy-mm-dd hh24:mi:ss') xjsj,b.state sbzt from (
        select * from   its.ksh_zxld  a
        <include refid="condition_Zxld"/>
        )a inner join ksh_zxldstate_history b on a.xh=b.xh where 1=1
        <isNotEmpty prepend="and" property="startTime">
            <![CDATA[	b.create_time >= to_date(#startTime#,'yyyy-mm-dd hh24:mi:ss')	 ]]>
        </isNotEmpty>
        <isNotEmpty prepend="and" property="endTime">
            <![CDATA[  b.create_time <=to_date(#endTime#,'yyyy-mm-dd hh24:mi:ss')	 ]]>
        </isNotEmpty>
    </select>
    <select id="count_Zxldhistorystate" resultClass="java.lang.Integer" parameterClass="java.util.Map">
        select count(*) from (
        select * from   its.ksh_zxld  a
        <include refid="condition_Zxld"/>
        )a inner join ksh_zxldstate_history b on a.xh=b.xh where 1=1
        <isNotEmpty prepend="and" property="startTime">
            <![CDATA[	b.create_time >= to_date(#startTime#,'yyyy-mm-dd hh24:mi:ss')	 ]]>
        </isNotEmpty>
        <isNotEmpty prepend="and" property="endTime">
            <![CDATA[  b.create_time <=to_date(#endTime#,'yyyy-mm-dd hh24:mi:ss')	 ]]>
        </isNotEmpty>
    </select>


    <select id="query_Zxldstatusinfo" resultClass="java.util.HashMap" parameterClass="java.util.Map">
        select a.*,b.ldbh ,b.ldmc from (
        select
        statusupbatch,xh
        from ywledydpstatusinfo
        <include refid="condition_Zxld"/>
        GROUP BY statusupbatch,xh
        ) a
        left join its.ksh_zxld b on a.xh = b.xh
    </select>
    <select id="count_Zxldstatusinfo" resultClass="java.lang.Integer" parameterClass="java.util.Map">
        select count(*) from (
        select
        statusupbatch,xh
        from ywledydpstatusinfo
        <include refid="condition_Zxld"/>
        GROUP BY statusupbatch,xh
        ) a
        left join its.ksh_zxld b on a.xh = b.xh
    </select>


    <select id="query_YwNoTaskZxldstatusinfo" resultClass="java.util.HashMap" parameterClass="java.util.Map">
        select a.*,b.csmc from (select xh,ledmc,ledbh,detectionswitch from its.zxld
        where xh not in (select xh from ywledydpstatusinfo b
        <include refid="condition_Zxld"/>
        and xh=b.xh
        )) a left join xtcl b on a.detectionswitch = b.csdm and b.csdl = 'ywTaskEnable'
    </select>

    <select id="count_YwNoTaskZxldstatusinfo" resultClass="java.lang.Integer" parameterClass="java.util.Map">
        select count(xh) from its.ksh_zxld where xh not in (select xh from ywledydpstatusinfo b
        <include refid="condition_Zxld"/>
        and xh=b.xh )
    </select>
    <!-- 根据 xh 批量更新启用状态 -->
    <update id="edit_Zxldinfo" parameterClass="java.util.Map">
        update its.ksh_zxld
        <dynamic prepend="set ">
            <isNotEmpty prepend=',' property='detectionswitch'>detectionswitch=#detectionswitch#</isNotEmpty>
            <isNotEmpty prepend=',' property='modifyaccount'>xgry=#modifyaccount#</isNotEmpty>
            <isNotEmpty prepend=',' property='modifytime'>xgrq=#modifytime#</isNotEmpty>
        </dynamic>
        where xh in
        <iterate open="(" close=")" conjunction="," property="keys">
            #keys[]#
        </iterate>
    </update>


    <select id="query_YwAllZxldstatusinfo" resultClass="java.util.HashMap" parameterClass="java.util.Map">
        select
        a.xh, ldbh, ldmc name, gxdwbh, gxdwmc, jd, wd, fx, qs, bz
        from its.ksh_zxld a
        <include refid="condition_Zxld"/>
    </select>

    <select id="count_YwAllZxldstatusinfo" resultClass="java.lang.Integer" parameterClass="java.util.Map">
        select count(xh) from its.ksh_zxld t
        <include refid="condition_Zxld"/>
    </select>

    <select id="statisticsZxldRate" resultClass="java.util.HashMap" parameterClass="java.util.Map">
        select   count(*) as totalcount,
        SUM(case when a.state is not null and  a.state=1  then 1 else 0 end) as onlinenum,
        SUM(case when  a.state =0 or a.state is null then 1 else 0 end) as unonlinenum
        from its.ksh_zxld a
	</select>

    <!-- query objects by page-->
    <select id="query_Zxldcurrentstate" resultClass="java.util.HashMap" parameterClass="java.util.Map"
            remapResults="true">
        select a.xh,ldbh,ldmc,ldmc name,gxdwbh,gxdwmc,jd,wd,fx,qs,bz,state,
        ( select xzmc from Ptxzs where  xzbh=gxdwbh) xzmc,gxdwbh xzqh,ip,
        (select csmc from Xtcl where csdl='ksh_ZxldCurrentState' and csdm=state)status ,repairstate,
        (select alarmname from  ywalarmeventcode where alarmcode=repairstate)repairstatename
        from its.ksh_zxld a where 1=1
        <isNotEmpty prepend='AND ' property='LIKEldmc'>
            (ldmc like '%$LIKEldmc$%' )
        </isNotEmpty>
        <isNotEmpty prepend="and " property='state'>
            state =#state#
        </isNotEmpty>
        <isNotEmpty prepend='AND ' property='LIKEbz'>
            (bz like '%$LIKEbz$%' )
        </isNotEmpty>
        <isNotEmpty property='emptyremarks'>
            <isEqual prepend='AND ' property='emptyremarks' compareValue="0">
                (bz is null or bz =' ' )
            </isEqual>
            <isEqual prepend='AND ' property='emptyremarks' compareValue="1">
                (bz is not null and bz !=' ' )
            </isEqual>
        </isNotEmpty>
        <isNotEmpty property='tableparam'>
            $tableparam$
        </isNotEmpty>
    </select>
    <select id="count_currentstate" resultClass="java.lang.Integer" parameterClass="java.util.Map">
        select count(a.xh) from its.ksh_zxld a where 1=1
        <isNotEmpty prepend='AND ' property='LIKEldmc'>
            (ldmc like '%$LIKEldmc$%' )
        </isNotEmpty>
        <isNotEmpty prepend="and " property='state'>
            state =#state#
        </isNotEmpty>
        <isNotEmpty prepend='AND ' property='LIKEbz'>
            (bz like '%$LIKEbz$%' )
        </isNotEmpty>
        <isNotEmpty property='emptyremarks'>
            <isEqual prepend='AND ' property='emptyremarks' compareValue="0">
                (bz is null or bz =' ' )
            </isEqual>
            <isEqual prepend='AND ' property='emptyremarks' compareValue="1">
                (bz is not null and bz !=' ' )
            </isEqual>
        </isNotEmpty>
        <isNotEmpty property='tableparam'>
            $tableparam$
        </isNotEmpty>
    </select>

    <select id="get_Zxld" resultClass="java.util.HashMap" parameterClass="java.util.Map"  remapResults="true">
        select xh, ldbh, ldmc, gxdwbh, gxdwmc, jd, wd, fx, qs, bz from its.ksh_zxld b
        <include refid="condition_Zxld"/>
    </select>


</sqlMap>











