<?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.bus.RegBusDyaqDao">
    
	<sql id="regBusDyaqColumns">
		a.id AS "id",
		a.bdcdyh AS "bdcdyh",
		a.ywh AS "ywh",
		a.dybdclx AS "dybdclx",
		a.dyr AS "dyr",
		a.zwr AS "zwr",
		a.zwrzjzl AS "zwrzjzl",
		a.zwrzjh AS "zwrzjh",
		a.dyfs AS "dyfs",
		a.djlx AS "djlx",
		a.djyy AS "djyy",
		a.zjjzwzl AS "zjjzwzl",
		a.zjjzwdyfw AS "zjjzwdyfw",
		a.bdbzzqse AS "bdbzzqse",
		a.dbze AS "dbze",
		a.zwlxqssj AS "zwlxqssj",
		a.zwlxjssj AS "zwlxjssj",
		a.zgzqqdss AS "zgzqqdss",
		a.zgzqse AS "zgzqse",
		a.pgjz AS "pgjz",
		a.pgjzhj AS "pgjzhj",
		a.jzmjhj AS "jzmjhj",
		a.zxdyywh AS "zxdyywh",
		a.zxdyyy AS "zxdyyy",
		a.zxsj AS "zxsj",
		a.bdcdjzmh AS "bdcdjzmh",
		a.qxdm AS "qxdm",
		a.djjg AS "djjg",
		a.dbr AS "dbr",
		a.djsj AS "djsj",
		a.fj AS "fj",
		a.qszt AS "qszt",
		a.isfdsj AS "isfdsj",
		a.ysdm AS "ysdm",
		a.tdzh AS "tdzh",
		a.dyqr AS "dyqr",
		a.dygyr AS "dygyr",
		a.isczyd AS "isczyd",
		a.dbfw AS "dbfw",
		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"
	</sql>
	
	<sql id="regBusDyaqJoins">
	</sql>
    
	<select id="get" resultType="RegBusDyaq">
		SELECT 
			<include refid="regBusDyaqColumns"/>
		FROM reg_bus_dyaq a
		<include refid="regBusDyaqJoins"/>
		WHERE a.id = #{id}
	</select>
	
	<select id="getByZxywh" resultType="RegBusDyaq">
		SELECT 
			<include refid="regBusDyaqColumns"/>
		FROM reg_bus_dyaq a
		<include refid="regBusDyaqJoins"/>
		WHERE a.zxdyywh = #{id}
	</select>
	
	<select id="finddyxxs" resultType="RegBusDyaq">
		SELECT 
			<include refid="regBusDyaqColumns"/>
		FROM reg_bus_dyaq a,reg_bus_bdcqzsdjxx b
		<include refid="regBusDyaqJoins"/>
		<where> 
		<if test="bdcdyh != null and bdcdyh != ''">
				AND a.bdcdyh = #{bdcdyh}
			</if>
			<if test="ywh != null and ywh != ''">
				AND a.ywh = #{ywh}
			</if>
			<if test="djlx != null and djlx != ''">
		        AND b.djlx = #{djlx}
			</if>
			<if test="isfdsj != null and isfdsj != ''">
				<if test="isfdsj == 100">
					AND a.isfdsj = '0'  
				</if>
				<if test="isfdsj != 100">
					AND a.isfdsj = #{isfdsj}  
				</if>
			</if>
			  AND b.del_flag = 0
			  AND b.dbsj is not null
			  AND a.ywh = b.ywh  
			  AND a.bdcdyh = b.bdcdyh 
			  GROUP BY a.ywh  
		</where>
			ORDER BY a.create_date DESC
	</select>
	<select id="OracleFinddyxxs" resultType="RegBusDyaq">
<!-- 		SELECT  -->
<!-- 			<include refid="regBusDyaqColumns"/> -->
<!-- 		FROM reg_bus_dyaq a  -->
<!-- 		where a.bdcdyh = #{bdcdyh} AND a.isfdsj = '0'  -->
<!-- 		and exists -->
<!-- 			(select MAX(b.ywh) from reg_bus_dyaq b,reg_bus_bdcqzsdjxx c  -->
<!-- 			where b.bdcdyh = #{bdcdyh} AND c.djlx = #{djlx} AND b.isfdsj = '0'  -->
<!-- 			AND c.del_flag = 0 AND c.dbsj is not null AND b.ywh = c.ywh AND b.bdcdyh = c.bdcdyh)  -->
<!-- 		ORDER BY a.create_date DESC -->
		
		SELECT ywh, bdbzzqse
		FROM (select b.ywh,b.bdbzzqse,row_number() over(partition by b.ywh order by b.create_date desc) rn 
			from reg_bus_dyaq b, reg_bus_bdcqzsdjxx c
			where b.bdcdyh = #{bdcdyh} AND c.djlx = #{djlx} AND b.isfdsj = '0' 
			AND c.del_flag = 0 AND c.dbsj is not null AND b.ywh = c.ywh AND b.bdcdyh = c.bdcdyh
		) 
		where rn = 1 
	</select>
	
	<select id="findList" resultType="RegBusDyaq">
		SELECT 
			<include refid="regBusDyaqColumns"/>
		FROM reg_bus_dyaq a
		<include refid="regBusDyaqJoins"/>
		<where>
			a.del_flag = #{DEL_FLAG_NORMAL}
			<if test="bdcdyh != null and bdcdyh != ''">
				AND a.bdcdyh = #{bdcdyh}
			</if>
			<if test="ywh != null and ywh != ''">
				AND a.ywh = #{ywh}
			</if>
			<if test="djlx != null and djlx != ''">
			 	<if test="djlx == 'NOT400'">
			        AND a.djlx != '400'
			    </if>
			</if>
			<if test="dbr != null and dbr != ''">
			    <if test="dbr == 'NOTNULL'">
			        AND a.dbr is not null
			    </if>
			    <if test="dbr != 'NOTNULL' and dbr != 'ISNULL'">
			        AND a.dbr = #{dbr}
			    </if>
			    <if test="dbr == 'ISNULL'">
			        AND a.dbr is null
			    </if>
			</if>
			<if test="isfdsj != null and isfdsj != ''">
				<if test="isfdsj == 100">
					AND a.isfdsj = '0'  
				</if>
				<if test="isfdsj != 100">
					AND a.isfdsj = #{isfdsj}  
				</if>
			</if>
			<if test="qszt != null and qszt != ''">
				AND a.qszt = #{qszt}
			</if>
			<if test="dyfs != null and dyfs != ''">
				AND a.dyfs = #{dyfs}
			</if>
			<if test="dyqr != null and dyqr != ''">
				AND a.dyqr = #{dyqr}
			</if>
		</where>
		<choose>
			<when test="page !=null and page.orderBy != null and page.orderBy != ''">
				ORDER BY ${page.orderBy}
			</when>
			<when test="remarks == 1">
				ORDER BY a.bdcdyh DESC
			</when>
			<otherwise>
				ORDER BY a.create_date DESC
			</otherwise>
		</choose>
	</select>
	
	<select id="findListxf" resultType="RegBusDyaq">
		SELECT 
			<include refid="regBusDyaqColumns"/>
		FROM reg_bus_dyaq a,reg_bus_bdcqzsdjxx b,reg_bus_rights c 
		<include refid="regBusDyaqJoins"/>
		<where>
			<if test="bdcdyh != null and bdcdyh != ''">
				AND a.bdcdyh = #{bdcdyh}
			</if>
			<if test="ywh != null and ywh != ''">
				AND a.ywh = #{ywh}
			</if>
			<if test="qszt != null and qszt != ''">
				AND a.qszt = #{qszt}
			</if>
			and a.bdcdyh = c.BDCDYH 
			and a.bdcdyh = b.BDCDYH 
			and b.dbsj is not null  
			and c.xtxq is not null 
			and a.ywh = c.xtxq 
			and a.ywh = b.ywh 
			and b.islogout = 0 and b.del_flag = 0
		</where>
		<choose>
			<when test="page !=null and page.orderBy != null and page.orderBy != ''">
				ORDER BY ${page.orderBy}
			</when>
			<otherwise>
				ORDER BY a.ywh ASC 
			</otherwise>
		</choose>
	</select>
	
	<select id="findFdsjList" resultType="RegBusDyaq">
		SELECT 
			<include refid="regBusDyaqColumns"/>
		FROM reg_bus_dyaq a,
		(SELECT MAX(isfdsj) c FROM reg_bus_dyaq
		<if test="ywh != null and ywh != ''">
			where ywh = #{ywh}
		</if>
		<if test="bdcdyh != null and bdcdyh != ''">
			AND bdcdyh = #{bdcdyh}
		</if>
		 ORDER BY isfdsj desc )b
		<include refid="regBusDyaqJoins"/>
		<where>
			<if test="ywh != null and ywh != ''">
				AND a.ywh = #{ywh}
			</if>
			<if test="bdcdyh != null and bdcdyh != ''">
				AND a.bdcdyh = #{bdcdyh}
			</if>
			and a.del_flag = #{DEL_FLAG_NORMAL}
			and a.isfdsj = b.c
		</where>
	</select>
	
	<select id="findAllList" resultType="RegBusDyaq">
		SELECT 
			<include refid="regBusDyaqColumns"/>
		FROM reg_bus_dyaq a
		<include refid="regBusDyaqJoins"/>
		<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.create_date DESC
			</otherwise>
		</choose>
	</select>
	
	<insert id="insert">
		INSERT INTO reg_bus_dyaq(
			id,
			bdcdyh,
			ywh,
			dybdclx,
			dyr,
			zwr,
			zwrzjzl,
			zwrzjh,
			dyfs,
			djlx,
			djyy,
			zjjzwzl,
			zjjzwdyfw,
			bdbzzqse,
			dbze,
			zwlxqssj,
			zwlxjssj,
			zgzqqdss,
			zgzqse,
			pgjz,
			pgjzhj,
			jzmjhj,
			zxdyywh,
			zxdyyy,
			zxsj,
			bdcdjzmh,
			qxdm,
			djjg,
			dbr,
			djsj,
			fj,
			qszt,
			isfdsj,
			ysdm,
			tdzh,
			dyqr,
			dygyr,
			isczyd,
			dbfw,
			create_by,
			create_date,
			update_by,
			update_date,
			del_flag
		) VALUES (
			#{id},
			#{bdcdyh},
			#{ywh},
			#{dybdclx},
			#{dyr},
			#{zwr},
			#{zwrzjzl},
			#{zwrzjh},
			#{dyfs},
			#{djlx},
			#{djyy},
			#{zjjzwzl},
			#{zjjzwdyfw},
			#{bdbzzqse},
			#{dbze},
			#{zwlxqssj},
			#{zwlxjssj},
			#{zgzqqdss},
			#{zgzqse},
			#{pgjz},
			#{pgjzhj},
			#{jzmjhj},
			#{zxdyywh},
			#{zxdyyy},
			#{zxsj},
			#{bdcdjzmh},
			#{qxdm},
			#{djjg},
			#{dbr},
			#{djsj},
			#{fj},
			#{qszt},
			#{isfdsj},
			#{ysdm},
			#{tdzh},
			#{dyqr},
			#{dygyr},
			#{isczyd},
			#{dbfw},
			#{createBy.id},
			#{createDate},
			#{updateBy.id},
			#{updateDate},
			#{delFlag}
		)
	</insert>
	
	<update id="update">
		UPDATE reg_bus_dyaq SET 	
			bdcdyh = #{bdcdyh},
			ywh = #{ywh},
			dybdclx = #{dybdclx},
			dyr = #{dyr},
			zwr = #{zwr},
			zwrzjzl = #{zwrzjzl},
			zwrzjh = #{zwrzjh},
			dyfs = #{dyfs},
			djlx = #{djlx},
			djyy = #{djyy},
			zjjzwzl = #{zjjzwzl},
			zjjzwdyfw = #{zjjzwdyfw},
			bdbzzqse = #{bdbzzqse},
			dbze = #{dbze},
			zwlxqssj = #{zwlxqssj},
			zwlxjssj = #{zwlxjssj},
			zgzqqdss = #{zgzqqdss},
			zgzqse = #{zgzqse},
			pgjz = #{pgjz},
			pgjzhj = #{pgjzhj},
			jzmjhj = #{jzmjhj},
			zxdyywh = #{zxdyywh},
			zxdyyy = #{zxdyyy},
			zxsj = #{zxsj},
			bdcdjzmh = #{bdcdjzmh},
			qxdm = #{qxdm},
			djjg = #{djjg},
			dbr = #{dbr},
			djsj = #{djsj},
			fj = #{fj},
			qszt = #{qszt},
			isfdsj = #{isfdsj},
			ysdm = #{ysdm},
			tdzh = #{tdzh},
			dyqr = #{dyqr},
			dygyr = #{dygyr},
			isczyd = #{isczyd},
			dbfw = #{dbfw},
			update_by = #{updateBy.id},
			update_date = #{updateDate}
		WHERE id = #{id}
	</update>
	
	<update id="updatedy">
		UPDATE reg_bus_dyaq SET 	
			bdcdyh = #{bdcdyh},
			ywh = #{ywh},
			dybdclx = #{dybdclx},
			dyr = #{dyr},
			zwr = #{zwr},
			zwrzjzl = #{zwrzjzl},
			zwrzjh = #{zwrzjh},
			dyfs = #{dyfs},
			djlx = #{djlx},
			djyy = #{djyy},
			zjjzwzl = #{zjjzwzl},
			zjjzwdyfw = #{zjjzwdyfw},
			bdbzzqse = #{bdbzzqse},
			dbze = #{dbze},
			zwlxqssj = #{zwlxqssj},
			zwlxjssj = #{zwlxjssj},
			zgzqqdss = #{zgzqqdss},
			zgzqse = #{zgzqse},
			pgjz = #{pgjz},
			pgjzhj = #{pgjzhj},
			jzmjhj = #{jzmjhj},
			zxdyywh = #{zxdyywh},
			zxdyyy = #{zxdyyy},
			zxsj = #{zxsj},
			bdcdjzmh = #{bdcdjzmh},
			qxdm = #{qxdm},
			djjg = #{djjg},
			dbr = #{dbr},
			djsj = #{djsj},
			fj = #{fj},
			qszt = #{qszt},
			isfdsj = #{isfdsj},
			ysdm = #{ysdm},
			tdzh = #{tdzh},
			dyqr = #{dyqr},
			dygyr = #{dygyr},
			isczyd = #{isczyd},
			dbfw = #{dbfw},
			update_by = #{updateBy.id},
			update_date = #{updateDate}
		WHERE ywh = #{ywh} and bdcdyh = #{bdcdyh} and djlx = #{djlx}
	</update>
	
	<update id="OracleUpdatedbze" parameterType="Map">
		UPDATE reg_bus_dyaq SET 	
			<if test="dbze != null and dbze != ''">
				dbze = #{dbze},
			</if>
			<if test="zgzqse != null and zgzqse != ''">
				zgzqse = #{zgzqse},
			</if>
			jzmjhj = #{jzmjhj},
			djyy = #{djyy},
			fj = #{fj},
			pgjzhj = #{pgjzhj},
			zwlxqssj = to_date(#{zwlxqssj},'yyyy-MM-dd hh24:mi:ss'),
			zwlxjssj = to_date(#{zwlxjssj},'yyyy-MM-dd hh24:mi:ss')
		WHERE ywh = #{ywh} 
	</update>
	<update id="updatedbze">
		UPDATE reg_bus_dyaq SET 	
			<if test="dbze != null and dbze != ''">
				dbze = #{dbze},
			</if>
			<if test="zgzqse != null and zgzqse != ''">
				zgzqse = #{zgzqse},
			</if>
			jzmjhj = #{jzmjhj},
			djyy = #{djyy},
			fj = #{fj},
			pgjzhj = #{pgjzhj},
			zwlxqssj = #{zwlxqssj},
			zwlxjssj = #{zwlxjssj}
		WHERE ywh = #{ywh} 
	</update>

	<update id="updatezwr" parameterType="Map">
		UPDATE reg_bus_dyaq SET 	
			zwr = #{zwr},
			zwrzjzl = #{zwrzjzl},
			zwrzjh = #{zwrzjh},
			dybdclx = #{dybdclx},
			dyfs = #{dyfs}
		WHERE ywh = #{ywh} 
	</update>
	
	<update id="delete">
		UPDATE reg_bus_dyaq SET 
			del_flag = #{DEL_FLAG_DELETE}
		WHERE id = #{id}
	</update>
	
	<update id="deletexfdy">
		DELETE FROM reg_bus_dyaq 
		<where>
		<if test= "ywh != null and ywh != ''">
				AND  ywh = #{ywh}
			</if >
			<if test= "bdcdyh != null and bdcdyh != ''">
				AND  bdcdyh = #{bdcdyh}
			</if >
			<if test= "djlx != null and djlx != ''">
				AND  djlx = #{djlx}
			</if >
		</where>
	</update>
	
	<update id="deleteywh">
		DELETE from reg_bus_dyaq
		WHERE ywh = #{ywh}
	</update>
	
	<select id="getQlxxByDyzmh" resultType="RegBusDyaq">
		SELECT * FROM REG_BUS_DYAQ where ywh = (select ywh from reg_bus_bdcqzsdjxx where bdcqzh like '%'||#{bdcqzh}||'%' and del_flag='0' and islogout='0' and rownum=1) and rownum=1
	</select>
</mapper>