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

    <!-- edit object  -->
    <update id="batchupdateMonthlyKPI" parameterClass="java.util.List">
        <iterate open="begin" close="end;">
            update MonthlyKPI
            set finalscore=#list[].finalscore#
            where id=#list[].id# ;
        </iterate>
    </update>


    <!-- query objects by page-->
    <select id="queryMonthlyKPI" resultClass="java.util.HashMap" parameterClass="java.util.Map">
		select id, targetdimension, ordernum, scope, checkitem, assessment, subitempoints, finalscore,ver,usingver,maxver from MonthlyKPI
		where  instr( ver ,','|| usingver)>0
	</select>

    <!-- query objects totalcount -->
    <select id="getMonthlyKPITotalScore" resultClass="java.lang.Integer" parameterClass="java.util.Map">
		select sum(finalscore) from MonthlyKPI where  instr(ver,','|| usingver)>0 group by usingver
	</select>

    <select id="queryMonthlyKPIByVer" resultClass="java.util.HashMap" parameterClass="java.util.Map">
        select id, targetdimension, ordernum, scope, checkitem, assessment, subitempoints,
        finalscore,ver,usingver,maxver,xgry,xgrq,cjry,cjrq from MonthlyKPI
        <isNotEmpty prepend="where " property="ver">
            instr( ver ,','|| #ver#)>0
        </isNotEmpty>
        <isEmpty prepend="where " property="ver">
            instr( ver ,','|| usingver)>0
        </isEmpty>

    </select>
    <select id="countMonthlyKPIByVer" resultClass="java.lang.Integer" parameterClass="java.util.Map">
        select count(*) from MonthlyKPI
        <isNotEmpty prepend="where " property="ver">
            instr( ver ,','|| #ver#)>0
        </isNotEmpty>
        <isEmpty prepend="where " property="ver">
            instr( ver ,','|| usingver)>0
        </isEmpty>
    </select>
    <select id="maxMonthlyKPIByVer" resultClass="java.util.HashMap" parameterClass="java.util.Map">
		select max(length(ver)) verlist ,max(maxver) maxver,max(usingver)usingver from MonthlyKPI
	</select>
    <insert id="insert_MonthlyKPIByVer" parameterClass="java.util.Map">
        insert into
        MonthlyKPI(
        <dynamic prepend=" ">
            <isNotEmpty prepend=',' property='id'>id</isNotEmpty>
            <isNotEmpty prepend=',' property='targetdimension'>targetdimension</isNotEmpty>
            <isNotEmpty prepend=',' property='ordernum'>ordernum</isNotEmpty>
            <isNotEmpty prepend=',' property='scope'>scope</isNotEmpty>
            <isNotEmpty prepend=',' property='checkitem'>checkitem</isNotEmpty>
            <isNotEmpty prepend=',' property='assessment'>assessment</isNotEmpty>
            <isNotEmpty prepend=',' property='subitempoints'>subitempoints</isNotEmpty>
            <isNotEmpty prepend=',' property='finalscore'>finalscore</isNotEmpty>
            <isNotEmpty prepend=',' property='ver'>ver</isNotEmpty>
            <isNotEmpty prepend=',' property='usingver'>usingver</isNotEmpty>
            <isNotEmpty prepend=',' property='maxver'>maxver</isNotEmpty>
            <isNotEmpty prepend=',' property='xgry'>xgry</isNotEmpty>
            <isNotEmpty prepend=',' property='xgrq'>xgrq</isNotEmpty>
            <isNotEmpty prepend=',' property='cjry'>cjry</isNotEmpty>
            <isNotEmpty prepend=',' property='cjrq'>cjrq</isNotEmpty>
        </dynamic>
        <![CDATA[
	        ) values (
	    ]]>
        <dynamic prepend=" ">
            <isNotEmpty prepend=',' property='id'>#id#</isNotEmpty>
            <isNotEmpty prepend=',' property='targetdimension'>#targetdimension#</isNotEmpty>
            <isNotEmpty prepend=',' property='ordernum'>#ordernum#</isNotEmpty>
            <isNotEmpty prepend=',' property='scope'>#scope#</isNotEmpty>
            <isNotEmpty prepend=',' property='checkitem'>#checkitem#</isNotEmpty>
            <isNotEmpty prepend=',' property='assessment'>#assessment#</isNotEmpty>
            <isNotEmpty prepend=',' property='subitempoints'>#subitempoints#</isNotEmpty>
            <isNotEmpty prepend=',' property='finalscore'>#finalscore#</isNotEmpty>
            <isNotEmpty prepend=',' property='ver'>#ver#</isNotEmpty>
            <isNotEmpty prepend=',' property='usingver'>#usingver#</isNotEmpty>
            <isNotEmpty prepend=',' property='maxver'>#maxver#</isNotEmpty>
            <isNotEmpty prepend=',' property='xgry'>#xgry#</isNotEmpty>
            <isNotEmpty prepend=',' property='xgrq'>#xgrq#</isNotEmpty>
            <isNotEmpty prepend=',' property='cjry'>#cjry#</isNotEmpty>
            <isNotEmpty prepend=',' property='cjrq'>#cjrq#</isNotEmpty>
        </dynamic>
        <![CDATA[
	        )
	    ]]>
    </insert>
    <update id="update_MonthlyKPIByxh" parameterClass="java.util.Map">
        update MonthlyKPI
        <dynamic prepend="set ">
            <isNotEmpty property="XGTJid">
                <isNotEmpty prepend=',' property='targetdimension'>targetdimension=#targetdimension#</isNotEmpty>
                <isNotEmpty prepend=',' property='ordernum'>ordernum=#ordernum#</isNotEmpty>
                <isNotEmpty prepend=',' property='scope'>scope=#scope#</isNotEmpty>
                <isNotEmpty prepend=',' property='checkitem'>checkitem=#checkitem#</isNotEmpty>
                <isNotEmpty prepend=',' property='assessment'>assessment=#assessment#</isNotEmpty>
                <isNotEmpty prepend=',' property='subitempoints'>subitempoints=#subitempoints#</isNotEmpty>
                <isNotEmpty prepend=',' property='finalscore'>finalscore=#finalscore#</isNotEmpty>
                <isNotEmpty prepend=',' property='ver'>ver=ver||','||#usingver#</isNotEmpty>
            </isNotEmpty>
            <isNotEmpty prepend=',' property='xgry'>xgry=#xgry#</isNotEmpty>
            <isNotEmpty prepend=',' property='xgrq'>xgrq=#xgrq#</isNotEmpty>
            <isNotEmpty prepend=',' property='usingver'>usingver=#usingver#</isNotEmpty>
            <isNotEmpty prepend=',' property='maxver'>maxver=#maxver#</isNotEmpty>
        </dynamic>
        <isNotEmpty property="XGTJid" prepend="where ">
            id = #XGTJid#
        </isNotEmpty>
    </update>
    <update id="update_Max_Usingver" parameterClass="java.util.Map">
        update MonthlyKPI
        <dynamic prepend="set ">
            <isNotEmpty prepend=',' property='usingver'>usingver=#usingver#</isNotEmpty>
            <isNotEmpty prepend=',' property='maxver'>maxver=#maxver#</isNotEmpty>
        </dynamic>
    </update>
    <update id="update_MonthlyKPIVerByUsingVer" parameterClass="java.util.Map">
        update MonthlyKPI set ver=ver||','||#usingver# where usingver =#oldusingver# and <![CDATA[instr(ver,','||#oldusingver#)>0]]>
    </update>



    <update id="update_MonthlyKPIByUsingVer" parameterClass="java.util.Map">
        update MonthlyKPI set ver=ver||','||#usingver#, usingver=#usingver# where usingver =#oldusingver# and <![CDATA[instr(ver,','||#oldusingver#)>0]]>
        <isNotEmpty property="id" prepend="AND ">
            id !=#id#
        </isNotEmpty>
    </update>
    <update id="update_MaxMonthlyKPIByUsingVer" parameterClass="java.util.Map">
        update MonthlyKPI
        <dynamic prepend="set ">
            <isNotEmpty prepend=',' property='xgry'>xgry=#xgry#</isNotEmpty>
            <isNotEmpty prepend=',' property='xgrq'>xgrq=#xgrq#</isNotEmpty>
            <isNotEmpty prepend=',' property='maxver'>maxver=#maxver#</isNotEmpty>
            <isNotEmpty prepend=',' property='usingver'>usingver=#usingver#</isNotEmpty>
        </dynamic>
        where usingver=#oldusingver#
    </update>


    <delete id="delete_MonthlyKPIByUsingVer" parameterClass="java.util.Map">
        delete from MonthlyKPI       where id =#keyword#
    </delete>

</sqlMap>











