<?xml version="1.0" encoding="UTF-8" ?>

<!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"
    "/WEB-INF/dtd/sql-map-2.dtd">

<sqlMap namespace="Price">
	<typeAlias alias="price" type="com.vodacar.repository.domain.Price" />
	<resultMap id="CountResult" class="com.vodacar.system.domain.Count">
		<result property="count" column="count" />
	</resultMap>
	<resultMap id="PriceResult" class="com.vodacar.repository.domain.Price">
		<result property="id" column="ID" nullValue="99999999" />
		<result property="price" column="PRICE" nullValue="" />
		<result property="explain" column="EXPLAIN" nullValue="" />
		<result property="creator" column="CREATOR" nullValue="" />
		<result property="released" column="RELEASED" nullValue="" />
		<result property="area" column="AREA" nullValue="99999999" />
		<result property="trimId" column="TRIMID" nullValue="999999999" />
		<result property="pass" column="PASS" nullValue="4" />
	</resultMap>
	<select id="getPriceMaxId" resultClass="price">select MAX("ID") as id from "DB2ADMIN"."AUTOWIKI_PRICE" </select>
	<select id="getPriceMaxIdByTrimId" resultClass="price" parameterClass="long">select MAX("ID") as id from "DB2ADMIN"."AUTOWIKI_PRICE" where "TRIMID"=#trimId#</select>
	<select id="countPrice" resultMap="CountResult" parameterClass="long">select count(*) as count from "DB2ADMIN"."AUTOWIKI_PRICE" where "TRIMID"=#trimId#</select>
	<select id="countPriceByArea" resultMap="CountResult" parameterClass="price">select count(*) as count from "DB2ADMIN"."AUTOWIKI_PRICE" where "AREA" = #area# and "TRIMID"=#trimId#</select>
	<select id="retrieveAllPrice" resultMap="PriceResult" parameterClass="long">select "ID", "PRICE", "EXPLAIN", "CREATOR", "RELEASED","AREA","TRIMID","PASS" from "DB2ADMIN"."AUTOWIKI_PRICE" where "TRIMID"=#trimId# order by "ID"</select>
	<select id="retrieveAllPriceByArea" resultMap="PriceResult" parameterClass="price">
		select "ID", "PRICE", "EXPLAIN", "CREATOR", "RELEASED","AREA","TRIMID","PASS" from "DB2ADMIN"."AUTOWIKI_PRICE","PASS" where "AREA" = #area# and "TRIMID"=#trimId# order by "ID"
	</select>
	<select id="retrievePrice" resultMap="PriceResult" parameterClass="long">
	select "ID", "PRICE", "EXPLAIN", "CREATOR", "RELEASED","AREA","TRIMID","PASS" from "DB2ADMIN"."AUTOWIKI_PRICE"
	 WHERE "ID"=#id#
	</select>
	<update id="updatePrice" parameterClass="price">
		UPDATE "DB2ADMIN"."AUTOWIKI_PRICE" SET

		<isNotEmpty  property="price">"PRICE" = #price#,</isNotEmpty>
		<isNotEmpty property="explain">"EXPLAIN" = #explain# ,</isNotEmpty>
		<isNotEmpty property="creator">"CREATOR" = #creator#,</isNotEmpty>
		<isNotEmpty property="released">"RELEASED" = #released#,</isNotEmpty>
		<isGreaterThan property="area" compareValue="0">"AREA" = #area#,</isGreaterThan>
		<isGreaterThan property="trimId" compareValue="0">"TRIMID" = #trimId# ,</isGreaterThan>
		<isNotEmpty property="pass">"PASS"=#pass#,</isNotEmpty>
		"ID" = #id# WHERE ID=#id#
	</update>
	<select id="queryCountPrice" resultClass="int">
		select count(*) as count from "DB2ADMIN"."AUTOWIKI_PRICE" 
		<dynamic prepend=" where ">
			
			<isNotEmpty prepend=" and " property="id">"ID" = #id#</isNotEmpty>
			<isNotEmpty prepend=" and " property="price">"PRICE" = #price#</isNotEmpty>
			<isNotEmpty prepend=" and " property="explain">"EXPLAIN" = #explain# </isNotEmpty>
			<isNotEmpty prepend=" and " property="creator">"CREATOR" = #creator#</isNotEmpty>
			<isNotEmpty prepend=" and " property="released">"RELEASED" = #released#</isNotEmpty>
			<isGreaterThan prepend=" and " property="area" compareValue="0">"AREA" = #area#</isGreaterThan>
			<isNotEmpty prepend=" and " property="pass">"PASS"=#pass#</isNotEmpty>
			<isGreaterThan prepend=" and " property="trimId" compareValue="0">"TRIMID" = #trimId#</isGreaterThan>
		</dynamic>

	</select>
	<select id="queryPrice" resultMap="PriceResult" >
	select	"ID", "PRICE", "EXPLAIN", "CREATOR", "RELEASED","AREA","TRIMID","PASS" from "DB2ADMIN"."AUTOWIKI_PRICE"
		<dynamic prepend=" where ">
			
			<isNotEmpty prepend=" and " property="id">"ID" = #id#</isNotEmpty>
			<isNotEmpty prepend=" and " property="price">"PRICE" = #price#</isNotEmpty>
			<isNotEmpty prepend=" and " property="explain">"EXPLAIN" = #explain# </isNotEmpty>
			<isNotNull prepend=" and " property="creator">"CREATOR" = #creator#</isNotNull>
			<isNotEmpty prepend=" and " property="released">"RELEASED" = #released#</isNotEmpty>
			<isGreaterThan prepend=" and " property="area" compareValue="0">"AREA" = #area#</isGreaterThan>
			<isNotEmpty prepend=" and " property="pass">"PASS"=#pass#</isNotEmpty>
			<isGreaterThan prepend=" and " property="trimId" compareValue="0">"TRIMID" = #trimId#</isGreaterThan>
		</dynamic>

	</select>
	<insert id="insertPrice" parameterClass="price">INSERT INTO "DB2ADMIN"."AUTOWIKI_PRICE" ( "PRICE", "EXPLAIN" , "CREATOR", "RELEASED","AREA","TRIMID","PASS") VALUES ( #price#, #explain#, #creator#, #released#,#area#,#trimId#,#pass#)</insert>
	<delete id="deletePrice" parameterClass="price">DELETE FROM "DB2ADMIN"."AUTOWIKI_PRICE" WHERE "ID"= #id#</delete>
</sqlMap>