<?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.dxhy.dkdj.dao.DkdjDao" >

	<resultMap id="BaseResultMap" type="com.dxhy.api.model.dkdj.TdxZzsDkdjwspzEntity" >
		<id column="id" property="id" jdbcType="BIGINT" />
		<result column="dkdjwspzh" property="dkdjwspzh" jdbcType="VARCHAR" />
		<result column="tfrq" property="tfrq" jdbcType="VARCHAR" />
		<result column="kjywrsbh" property="kjywrsbh" jdbcType="VARCHAR" />
		<result column="kjywrmc" property="kjywrmc" jdbcType="VARCHAR" />
		<result column="bkjnsrsbh" property="bkjnsrsbh" jdbcType="VARCHAR" />
		<result column="bkjnsrmc" property="bkjnsrmc" jdbcType="VARCHAR" />
		<result column="jsje" property="jsje" jdbcType="DECIMAL" />
		<result column="sjje" property="sjje" jdbcType="DECIMAL" />
		<result column="yxse" property="yxse" jdbcType="DECIMAL" />
		<result column="rzssq" property="rzssq" jdbcType="VARCHAR" />
		<result column="rzzt" property="rzzt" jdbcType="VARCHAR" />
		<result column="rzlx" property="rzlx" jdbcType="VARCHAR" />
		<result column="rzclzt" property="rzclzt" jdbcType="VARCHAR" />
		<result column="rzsbyy" property="rzsbyy" jdbcType="VARCHAR" />
		<result column="rzsj" property="rzsj" jdbcType="VARCHAR" />
		<result column="bdklx" property="bdklx" jdbcType="VARCHAR" />
		<result column="bdkyy" property="bdkyy" jdbcType="VARCHAR" />
		<result column="czr" property="czr" jdbcType="VARCHAR" />
		<result column="czsj" property="czsj" jdbcType="TIMESTAMP" />
		<result column="update_time" property="updateTime" jdbcType="TIMESTAMP" />
		<result column="create_time" property="createTime" jdbcType="TIMESTAMP" />
	</resultMap>
	<sql id="Base_Column_List" >
		id,
		dkdjwspzh,
		tfrq,
		kjywrsbh,
		kjywrmc,
		bkjnsrsbh,
		bkjnsrmc,
		jsje,
		sjje,
		yxse,
		rzssq,
		rzzt,
		rzlx,
		rzclzt,
		rzsbyy,
		rzsj,
		bdklx,
		bdkyy,
		czr,
		czsj,
		update_time,
		create_time
  </sql>
	<!--合计信息-->
	<select id="selectDkdjAuthPageHj" resultType="com.dxhy.api.model.dkdj.TdxZzsDkdjwspzEntity" databaseId="mysql">
		select
		ifnull(sum(jsje),0) jsje,
		ifnull(sum(sjje),0) sjje,
		ifnull(sum(yxse),0) yxse
		from t_dx_zzs_dkdjwspz

		WHERE

		<include refid="page_where"/>
	</select>

	<!--合计信息-->
	<select id="selectDkdjAuthPageHj" resultType="com.dxhy.api.model.dkdj.TdxZzsDkdjwspzEntity" databaseId="oracle">
		select
		COALESCE(sum(jsje),0) jsje,
		COALESCE(sum(sjje),0) sjje,
		COALESCE(sum(yxse),0) yxse
		from t_dx_zzs_dkdjwspz

		WHERE

		<include refid="page_where_oracle"/>
	</select>

	<!--列表信息-->
	<select id="selectDkdjAuthPageList" resultType="com.dxhy.api.model.dkdj.TdxZzsDkdjwspzEntity"  databaseId="mysql">
		select
		<include refid="Base_Column_List" />
		from t_dx_zzs_dkdjwspz
		WHERE

		<include refid="page_where"/>
		order by tfrq desc
	</select>

	<select id="selectDkdjAuthPageList" resultType="com.dxhy.api.model.dkdj.TdxZzsDkdjwspzEntity" databaseId="oracle">
		select
		<include refid="Base_Column_List" />
		from t_dx_zzs_dkdjwspz
		WHERE

		<include refid="page_where_oracle"/>
		order by tfrq desc
	</select>

	<sql  id="page_where">
		1=1
		and kjywrsbh in
		<foreach collection="kjywrsbh" index="index" item="item" open="(" separator="," close=")">
			#{item}
		</foreach>

		<!--判断查询页面 0待认证  1认证结果查询 -->
		<if test="searchPageType != null and searchPageType != '' and searchPageType != 'null' and searchPageType =='0'.toString() ">
			and rzzt = '0' and rzclzt in('0','3','8')
		</if>

		<if test="searchPageType != null and searchPageType != '' and searchPageType != 'null' and searchPageType =='1'.toString() ">
			and rzclzt !='0'
		</if>

		<!--认证类型 1认证抵扣(rzlx in 01,02)，2认证不抵扣(rzlx in 03,04)-->
		<!--<if test="rzlx != null and rzlx != '' and rzlx != 'null' and rzlx =='1'.toString() ">-->
			<!--and rzlx in ('01','02')-->
		<!--</if>-->
		<!--<if test="rzlx != null and rzlx != '' and rzlx != 'null' and rzlx =='2'.toString() ">-->
			<!--and rzlx in ('03','04')-->
		<!--</if>-->
		<if test="rzlx != null and rzlx != '' and rzlx != 'null' ">
			and rzlx = #{rzlx}
		</if>

		<if test="rzclzt != null and rzclzt != '' and rzclzt != 'null' ">
			and rzclzt  = #{rzclzt}
		</if>

		<if test="rzzt != null and rzzt != '' and rzzt != 'null' ">
			and rzzt  = #{rzzt}
		</if>
		<if test="bdkyy != null and bdkyy != '' and bdkyy != 'null' ">
			and bdkyy like CONCAT(#{bdkyy},'%')
		</if>
		<if test="bdklx != null and bdklx != '' and bdklx != 'null' ">
			and bdklx = #{bdklx}
		</if>

		<if test="rzssq != null and rzssq != '' and rzssq != 'null' ">
			and rzssq  = #{rzssq}
		</if>

		<if test="czr != null and czr != '' and czr != 'null' ">
			and czr  = #{czr}
		</if>


		<if test="dkdjwspzh != null and dkdjwspzh != '' and dkdjwspzh != 'null' ">
			and dkdjwspzh = #{dkdjwspzh}
		</if>
		<if test="bkjnsrsbh != null and bkjnsrsbh != '' and bkjnsrsbh != 'null' ">
			and bkjnsrsbh = #{bkjnsrsbh}
		</if>
		<if test="bkjnsrmc != null and bkjnsrmc != '' and bkjnsrmc != 'null' ">
			and bkjnsrmc = #{bkjnsrmc}
		</if>

		<if test="tfrqStart != null and tfrqStart != '' and tfrqStart != 'null' ">
			and tfrq &gt;= #{tfrqStart}
		</if>
		<if test="tfrqEnd != null and tfrqEnd != '' and tfrqEnd != 'null' ">
			and tfrq &lt;= #{tfrqEnd}
		</if>
	</sql>

	<sql  id="page_where_oracle">
		1=1
		and kjywrsbh in
		<foreach collection="kjywrsbh" index="index" item="item" open="(" separator="," close=")">
			#{item}
		</foreach>

		<!--判断查询页面 0待认证  1认证结果查询 -->
		<if test="searchPageType != null and searchPageType != '' and searchPageType != 'null' and searchPageType =='0'.toString() ">
			and rzzt = '0' and rzclzt in('0','3','8')
		</if>

		<if test="searchPageType != null and searchPageType != '' and searchPageType != 'null' and searchPageType =='1'.toString() ">
			and rzclzt !='0'
		</if>

		<!--认证类型 1认证抵扣(rzlx in 01,02)，2认证不抵扣(rzlx in 03,04)-->
		<!--<if test="rzlx != null and rzlx != '' and rzlx != 'null' and rzlx =='1'.toString() ">-->
		<!--and rzlx in ('01','02')-->
		<!--</if>-->
		<!--<if test="rzlx != null and rzlx != '' and rzlx != 'null' and rzlx =='2'.toString() ">-->
		<!--and rzlx in ('03','04')-->
		<!--</if>-->
		<if test="rzlx != null and rzlx != '' and rzlx != 'null' ">
			and rzlx = #{rzlx}
		</if>

		<if test="rzclzt != null and rzclzt != '' and rzclzt != 'null' ">
			and rzclzt  = #{rzclzt}
		</if>

		<if test="rzzt != null and rzzt != '' and rzzt != 'null' ">
			and rzzt  = #{rzzt}
		</if>
		<if test="bdkyy != null and bdkyy != '' and bdkyy != 'null' ">
			and bdkyy like CONCAT(#{bdkyy},'%')
		</if>
		<if test="bdklx != null and bdklx != '' and bdklx != 'null' ">
			and bdklx = #{bdklx}
		</if>

		<if test="rzssq != null and rzssq != '' and rzssq != 'null' ">
			and rzssq  = #{rzssq}
		</if>

		<if test="czr != null and czr != '' and czr != 'null' ">
			and czr  = #{czr}
		</if>


		<if test="dkdjwspzh != null and dkdjwspzh != '' and dkdjwspzh != 'null' ">
			and dkdjwspzh = #{dkdjwspzh}
		</if>
		<if test="bkjnsrsbh != null and bkjnsrsbh != '' and bkjnsrsbh != 'null' ">
			and bkjnsrsbh = #{bkjnsrsbh}
		</if>
		<if test="bkjnsrmc != null and bkjnsrmc != '' and bkjnsrmc != 'null' ">
			and bkjnsrmc = #{bkjnsrmc}
		</if>

		<if test="tfrqStart != null and tfrqStart != '' and tfrqStart != 'null' ">
			and tfrq &gt;= to_date(#{tfrqStart},'yyyy-mm-dd hh24:mi:ss')
		</if>
		<if test="tfrqEnd != null and tfrqEnd != '' and tfrqEnd != 'null' ">
			and tfrq &lt;= to_date(#{tfrqEnd},'yyyy-mm-dd hh24:mi:ss')
		</if>
	</sql>


</mapper>