<?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.thinkgem.jeesite.modules.reg.dao.base.RegBaseZrzDao">
    
	<sql id="regBaseZrzColumns">
		a.id AS "id",
		a.bsm AS "bsm",
		a.ysdm AS "ysdm",
		a.bdcdyh AS "bdcdyh",
		a.zddm AS "zddm",
		a.zrzh AS "zrzh",
		a.xmmc AS "xmmc",
		a.gzwmc AS "gzwmc",
		a.jgrq AS "jgrq",
		a.jzwgd AS "jzwgd",
		a.zzdmj AS "zzdmj",
		a.zydmj AS "zydmj",
		a.ycjzmj AS "ycjzmj",
		a.scjzmj AS "scjzmj",
		a.zcs AS "zcs",
		a.dscs AS "dscs",
		a.dxcs AS "dxcs",
		a.dxsd AS "dxsd",
		a.ghyt AS "ghyt",
		a.fwjg AS "fwjg",
		a.zts AS "zts",
		a.jzwjbyt AS "jzwjbyt",
		a.dah AS "dah",
		a.bz AS "bz",
		a.zt AS "zt",
		a.zdftfs AS "zdftfs",
		a.xmid AS "xmid",
		a.ytmc AS "ytmc",
		a.isaudit AS "isaudit",
		a.create_by AS "createBy.id",
		a.create_date AS "createDate",
		a.update_by AS "updateBy.id",
		a.update_date AS "updateDate",
		a.remarks AS "remarks",
		a.del_flag AS "delFlag",
		a.zb AS "zb",
		a.btzrzbm AS "btzrzbm",
		a.zsxid AS "zsxid"
	</sql>
	
	<sql id="regBaseZrzJoins">
	</sql>
    
	<select id="get" resultType="RegBaseZrz">
		SELECT 
			<include refid="regBaseZrzColumns"/>
		FROM reg_base_zrz a
		<include refid="regBaseZrzJoins"/>
		WHERE a.id = #{id}
	</select>
	
	<select id="getByZrzh" resultType="RegBaseZrz">
		SELECT 
			<include refid="regBaseZrzColumns"/>
		FROM reg_base_zrz a
		<include refid="regBaseZrzJoins"/>
		WHERE a.zrzh = #{zrzh}
		and a.del_flag='0'
	</select>
	
	<select id="getRegBaseZrz" resultType="RegBaseZrz">
		SELECT 
			<include refid="regBaseZrzColumns"/>
		FROM reg_base_zrz a
		<include refid="regBaseZrzJoins"/>
		WHERE a.bdcdyh = #{bdcdyh} 
		AND a.del_flag = '0'
	</select>
	
	<select id="getListByZddm" resultType="RegBaseZrz">
		SELECT 
			<include refid="regBaseZrzColumns"/>
		FROM reg_base_zrz a
		<include refid="regBaseZrzJoins"/>
		<where>
		  a.del_flag = #{DEL_FLAG_NORMAL}
		  <if test="zddm != null and zddm != ''">
				AND a.zddm = #{zddm}
		  </if>
		  <if test="zrzh != null and zrzh != ''">
				AND a.zrzh = #{zrzh}
			</if>
		</where>
	</select>
	
	<select id="getZrzMaxBsm" resultType="long">
		select IFNULL(MAX(bsm), 0) from reg_base_zrz where del_flag = '0'
	</select>
	
	<!-- 	一窗受理查询层最大数 -->
	<select id="getczds" resultType="String">
		select max(to_number(t.hh)) from REG_BASE_H t where t.bdcdyh like #{bdcdyh}||'%'
	</select>
	
	<select id="getZrzMaxBsmofOracle" resultType="long">
		select nvl(MAX(bsm), 0) from reg_base_zrz where del_flag = '0'
	</select>
	
	<select id="findzrzhsxhList" resultType="RegBaseZrz">
		SELECT 
			<include refid="regBaseZrzColumns"/>
		FROM reg_base_zrz a
		<include refid="regBaseZrzJoins"/>
		<where>
			a.del_flag = '0'
	    	AND SUBSTR(a.bdcdyh ,1,20)  =  #{bdcdyh}
		</where>
		<if test="dbName == 'oracle'">
			order by to_number(SUBSTR(bdcdyh ,21,24))  desc 
		</if>
		<if test="dbName == 'mysql'">
			order by CONVERT(SUBSTR(bdcdyh ,21,24),SIGNED)  desc 
		</if>
	</select>
	
	<select id="findzrzhList" resultType="RegBaseZrz">
		SELECT 
			<include refid="regBaseZrzColumns"/>
		FROM reg_base_zrz a
		<include refid="regBaseZrzJoins"/>
		<where>
	    	SUBSTR(a.bdcdyh ,1,19)  =  #{zddm}
		</where>
		<if test="dbName == 'oracle'">
			order by to_number(SUBSTR(bdcdyh ,21,24))  desc 
		</if>
		<if test="dbName == 'mysql'">
			order by CONVERT(SUBSTR(bdcdyh ,21,24),SIGNED)  desc 
		</if>
	</select>
	
	<select id="findList" resultType="RegBaseZrz">
		SELECT 
			<include refid="regBaseZrzColumns"/>
		FROM reg_base_zrz a
		<include refid="regBaseZrzJoins"/>
		<where>
			a.del_flag = #{DEL_FLAG_NORMAL}
			<if test="xmid != null and xmid != ''">
				AND a.xmid = #{xmid}
			</if>
			<if test="id != null and id != ''">
				AND a.id = #{id}
			</if>
			<if test="bsm != null and bsm != ''">
				AND a.bsm LIKE 
					<if test="dbName == 'oracle'">'%'||#{bsm}||'%'</if>
					<if test="dbName == 'mssql'">'%'+#{bsm}+'%'</if>
					<if test="dbName == 'mysql'">concat('%',#{bsm},'%')</if>
			</if>
			<if test="bdcdyh != null and bdcdyh != ''">
				AND a.bdcdyh LIKE 
					<if test="dbName == 'oracle'">'%'||#{bdcdyh}||'%'</if>
					<if test="dbName == 'mssql'">'%'+#{bdcdyh}+'%'</if>
					<if test="dbName == 'mysql'">concat('%',#{bdcdyh},'%')</if>
			</if>
			<if test="zddm != null and zddm != ''">
				AND a.zddm = #{zddm}
			</if>
			<if test="zrzh != null and zrzh != ''">
				AND a.zrzh = #{zrzh}
			</if>
			<if test="xmmc != null and xmmc != ''">
				AND a.xmmc LIKE 
					<if test="dbName == 'oracle'">'%'||#{xmmc}||'%'</if>
					<if test="dbName == 'mssql'">'%'+#{xmmc}+'%'</if>
					<if test="dbName == 'mysql'">concat('%',#{xmmc},'%')</if>
			</if>
			<if test="gzwmc != null and gzwmc != ''">
				AND a.gzwmc LIKE 
					<if test="dbName == 'oracle'">'%'||#{gzwmc}||'%'</if>
					<if test="dbName == 'mssql'">'%'+#{gzwmc}+'%'</if>
					<if test="dbName == 'mysql'">concat('%',#{gzwmc},'%')</if>
			</if>
			<if test="btzrzbm != null and btzrzbm != ''">
				AND a.btzrzbm = #{btzrzbm}
			</if>
			<if test="zsxid != null and zsxid != ''">
				AND a.zsxid = #{zsxid}
			</if>
			<if test="remarks != null and remarks != ''">
				AND a.create_by = '1'
			</if>
		</where>
		<choose>
			<when test="page !=null and page.orderBy != null and page.orderBy != ''">
				ORDER BY ${page.orderBy}
			</when>
			<otherwise>
				ORDER BY a.bsm
			</otherwise>
		</choose>
	</select>
	
	<select id="findZddms" parameterType="String" resultType="String">
		SELECT distinct a.zddm FROM reg_base_zrz a
		<where>
			a.del_flag ='0' AND a.xmid = #{xmid}
		</where>
				ORDER BY a.zddm
	</select>
	
	<select id="findJzwmcList" resultType="RegBaseZrz">
		SELECT 
			<include refid="regBaseZrzColumns"/>
		FROM reg_base_zrz a
		<include refid="regBaseZrzJoins"/>
		<where>
			a.del_flag = #{DEL_FLAG_NORMAL}
			<if test="xmid != null and xmid != ''">
				AND a.xmid = #{xmid}
			</if>
			<if test="gzwmc != null and gzwmc != ''">
				AND a.gzwmc = #{gzwmc}
			</if>
			<if test="bdcdyh != null and bdcdyh != ''">
				AND a.bdcdyh != #{bdcdyh}
			</if>
		</where>
		<choose>
			<when test="page !=null and page.orderBy != null and page.orderBy != ''">
				ORDER BY ${page.orderBy}
			</when>
			<otherwise>
				ORDER BY a.zrzh
			</otherwise>
		</choose>
	</select>
	
	<select id="findAllList" resultType="RegBaseZrz">
		SELECT 
			<include refid="regBaseZrzColumns"/>
		FROM reg_base_zrz a
		<include refid="regBaseZrzJoins"/>
		<where>
			a.del_flag = #{DEL_FLAG_NORMAL}
		</where>		
		<choose>
			<when test="page !=null and page.orderBy != null and page.orderBy != ''">
				ORDER BY ${page.orderBy}
			</when>
			<otherwise>
				ORDER BY a.zrzh
			</otherwise>
		</choose>
	</select>
	
	<insert id="insert">
		INSERT INTO reg_base_zrz(
			id,
			bsm,
			ysdm,
			bdcdyh,
			zddm,
			zrzh,
			xmmc,
			gzwmc,
			jgrq,
			jzwgd,
			zzdmj,
			zydmj,
			ycjzmj,
			scjzmj,
			zcs,
			dscs,
			dxcs,
			dxsd,
			ghyt,
			fwjg,
			zts,
			jzwjbyt,
			dah,
			bz,
			zt,
			zdftfs,
			xmid,
			ytmc,
			isaudit,
			create_by,
			create_date,
			update_by,
			update_date,
			remarks,
			del_flag,
			zb,
			btzrzbm,
			zsxid
		) VALUES (
			#{id},
			#{bsm},
			#{ysdm},
			#{bdcdyh},
			#{zddm},
			#{zrzh},
			#{xmmc},
			#{gzwmc},
			#{jgrq},
			#{jzwgd},
			#{zzdmj},
			#{zydmj},
			#{ycjzmj},
			#{scjzmj},
			#{zcs},
			#{dscs},
			#{dxcs},
			#{dxsd},
			#{ghyt},
			#{fwjg},
			#{zts},
			#{jzwjbyt},
			#{dah},
			#{bz},
			#{zt},
			#{zdftfs},
			#{xmid},
			#{ytmc},
			#{isaudit},
			#{createBy.id},
			#{createDate},
			#{updateBy.id},
			#{updateDate},
			#{remarks},
			#{delFlag},
			#{zb},
			#{btzrzbm},
			#{zsxid}
		)
	</insert>
	
	<update id="update">
		UPDATE reg_base_zrz SET 	
			bsm = #{bsm},
			ysdm = #{ysdm},
			bdcdyh = #{bdcdyh},
			zddm = #{zddm},
			zrzh = #{zrzh},
			xmmc = #{xmmc},
			gzwmc = #{gzwmc},
			jgrq = #{jgrq},
			jzwgd = #{jzwgd},
			zzdmj = #{zzdmj},
			zydmj = #{zydmj},
			ycjzmj = #{ycjzmj},
			scjzmj = #{scjzmj},
			zcs = #{zcs},
			dscs = #{dscs},
			dxcs = #{dxcs},
			dxsd = #{dxsd},
			ghyt = #{ghyt},
			fwjg = #{fwjg},
			zts = #{zts},
			jzwjbyt = #{jzwjbyt},
			dah = #{dah},
			bz = #{bz},
			zt = #{zt},
			zdftfs = #{zdftfs},
			xmid = #{xmid},
			ytmc = #{ytmc},
			isaudit = #{isaudit},
			update_by = #{updateBy.id},
			update_date = #{updateDate},
			remarks = #{remarks},
			del_flag = #{delFlag},
			zb = #{zb},
			btzrzbm = #{btzrzbm},
			zsxid = #{zsxid}
		WHERE id = #{id}
	</update>
	
	<update id="delete">
		UPDATE reg_base_zrz SET 
			del_flag = #{DEL_FLAG_DELETE}
		WHERE id = #{id}
	</update>
	
	<update id="zrzdelete">
		DELETE FROM reg_base_zrz 
		WHERE id = #{id}
	</update>
	<select id="findListZb" resultType="RegBaseZrz">
		SELECT 
			<include refid="regBaseZrzColumns"/>
		FROM reg_base_zrz a
		<include refid="regBaseZrzJoins"/>
		<where>
			a.del_flag = #{DEL_FLAG_NORMAL}
			AND a.zb is not null
			<if test="xmid != null and xmid != ''">
				AND a.xmid = #{xmid}
			</if>
			<if test="id != null and id != ''">
				AND a.id = #{id}
			</if>
			<if test="bsm != null and bsm != ''">
				AND a.bsm LIKE 
					<if test="dbName == 'oracle'">'%'||#{bsm}||'%'</if>
					<if test="dbName == 'mssql'">'%'+#{bsm}+'%'</if>
					<if test="dbName == 'mysql'">concat('%',#{bsm},'%')</if>
			</if>
			<if test="bdcdyh != null and bdcdyh != ''">
				AND a.bdcdyh LIKE 
					<if test="dbName == 'oracle'">'%'||#{bdcdyh}||'%'</if>
					<if test="dbName == 'mssql'">'%'+#{bdcdyh}+'%'</if>
					<if test="dbName == 'mysql'">concat('%',#{bdcdyh},'%')</if>
			</if>
			<if test="zddm != null and zddm != ''">
				AND a.zddm = #{zddm}
			</if>
			<if test="zrzh != null and zrzh != ''">
				AND a.zrzh = #{zrzh}
			</if>
			<if test="xmmc != null and xmmc != ''">
				AND a.xmmc LIKE 
					<if test="dbName == 'oracle'">'%'||#{xmmc}||'%'</if>
					<if test="dbName == 'mssql'">'%'+#{xmmc}+'%'</if>
					<if test="dbName == 'mysql'">concat('%',#{xmmc},'%')</if>
			</if>
			<if test="gzwmc != null and gzwmc != ''">
				AND a.gzwmc LIKE 
					<if test="dbName == 'oracle'">'%'||#{gzwmc}||'%'</if>
					<if test="dbName == 'mssql'">'%'+#{gzwmc}+'%'</if>
					<if test="dbName == 'mysql'">concat('%',#{gzwmc},'%')</if>
			</if>
			
		</where>
		
	</select>
	
	<!-- 权籍接口用 -->
	<select id="getByZddmZrzh" resultType="RegBaseZrz">
		SELECT 
			<include refid="regBaseZrzColumns"/>
		FROM reg_base_zrz a
		<include refid="regBaseZrzJoins"/>
		WHERE a.zddm = #{0} 
		and a.zrzh = #{1}
	</select>
	
		<select id="findByBdcqzsSub" resultType="String">
		SELECT 	a.jgrq
		FROM reg_base_zrz a
		<where>
		 a.del_flag='0'
				AND a.bdcdyh LIKE '%'||#{bdcdyh}||'%'
					
		</where>
	</select>
</mapper>