<?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.RegBusQlrDao">
    
	<sql id="regBusQlrColumns">
		a.id AS "id",
		a.ysdm AS "ysdm",
		a.ywh AS "ywh",
		a.bdcdyh AS "bdcdyh",
		a.sxh AS "sxh",
		a.qlrmc AS "qlrmc",
		a.pcode AS "pcode",
		a.bdcqzh AS "bdcqzh",
		a.qzysxlh AS "qzysxlh",
		a.sfczr AS "sfczr",
		a.zjzl AS "zjzl",
		a.zjh AS "zjh",
		a.fzjg AS "fzjg",
		a.sshy AS "sshy",
		a.gj AS "gj",
		a.hjszss AS "hjszss",
		a.xb AS "xb",
		a.dh AS "dh",
		a.dz AS "dz",
		a.yb AS "yb",
		a.gzdw AS "gzdw",
		a.dzyj AS "dzyj",
		a.qlrlx AS "qlrlx",
		a.qlbl AS "qlbl",
		a.gyfs AS "gyfs",
		a.gyqk AS "gyqk",
		a.bz AS "bz",
		a.fddbrfzr AS "fddbrfzr",
		a.fddbrfzrdh AS "fddbrfzrdh",
		a.dlrmc AS "dlrmc",
		a.dlrdh AS "dlrdh",
		a.dljgmc AS "dljgmc",
		a.qszt AS "qszt",
		a.qllx AS "qllx",
		a.gmrid AS "gmrid",
		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.pic AS "pic",
		a.tpbm AS "tpbm",
		a.piczt AS "piczt",
		a.sczt AS "sczt",
		a.veritysczt AS "veritysczt"
	</sql>
	
	<sql id="regBusQlrJoins"> 
	</sql>
    <select id="findMaxYwhLs" resultType="string">
		select max(substr(t.ywh,4)) from REG_BUS_QLR t where YWH like 'LST%'
	</select>
	<select id="findMaxYwhNf" resultType="string">
		select max(substr(t.ywh,3)) from REG_BUS_QLR t where YWH like 'LZ%'
	</select>
	<select id="get" resultType="RegBusQlr">
		SELECT 
			<include refid="regBusQlrColumns"/>
		FROM reg_bus_qlr a
		<include refid="regBusQlrJoins"/>
		WHERE a.id = #{id}
	</select>
	<select id="getQlrByDy" resultType="RegBusQlr">
		SELECT 
			<include refid="regBusQlrColumns"/>
		FROM reg_bus_qlr a
		<include refid="regBusQlrJoins"/>
		WHERE a.bz = #{bdcdyh}
	</select>
	<select id="findList" resultType="RegBusQlr">
		SELECT 
			<include refid="regBusQlrColumns"/>
		FROM reg_bus_qlr a
		<include refid="regBusQlrJoins"/>
		<if test="ywh != null and ywh != ''">
			JOIN reg_bus_qlrywhrelation re ON re.qlrid = a.id AND re.ywh = #{ywh}
		</if>
		<where>
			a.del_flag = #{DEL_FLAG_NORMAL}
			<if test="bdcdyh != null and bdcdyh != ''">
				AND a.bdcdyh = #{bdcdyh}
			</if>
			<if test="sxh != null and sxh != ''">
				AND a.sxh = #{sxh}
			</if>
			<if test="sfczr != null and sfczr != ''">
				AND a.sfczr = #{sfczr}
			</if>
			<if test="qlrmc != null and qlrmc != ''">
				AND a.qlrmc LIKE 
					<if test="dbName == 'oracle'">'%'||#{qlrmc}||'%'</if>
					<if test="dbName == 'mssql'">'%'+#{qlrmc}+'%'</if>
					<if test="dbName == 'mysql'">concat('%',#{qlrmc},'%')</if>
			</if>
			<if test="zjzl != null and zjzl != ''">
				AND a.zjzl = #{zjzl}
			</if>
			<if test="zjh != null and zjh != ''">
				AND a.zjh = #{zjh}
			</if>
		</where>
		<choose>
			<when test="page !=null and page.orderBy != null and page.orderBy != ''">
				ORDER BY ${page.orderBy}
			</when>
			<otherwise>
				ORDER BY a.sxh asc
			</otherwise>
		</choose>
	</select>
	
	
	<select id="findListNF" resultType="RegBusQlr">
		SELECT 
			<include refid="regBusQlrColumns"/>
		FROM reg_bus_qlr a
		<include refid="regBusQlrJoins"/>
		
		<where>
			a.del_flag = #{DEL_FLAG_NORMAL}
			<if test="bdcdyh != null and bdcdyh != ''">
				AND a.bdcdyh = #{bdcdyh}
			</if>
			<if test="sxh != null and sxh != ''">
				AND a.sxh = #{sxh}
			</if>
			<if test="sfczr != null and sfczr != ''">
				AND a.sfczr = #{sfczr}
			</if>
			<if test="qlrmc != null and qlrmc != ''">
				AND a.qlrmc LIKE 
					<if test="dbName == 'oracle'">'%'||#{qlrmc}||'%'</if>
					<if test="dbName == 'mssql'">'%'+#{qlrmc}+'%'</if>
					<if test="dbName == 'mysql'">concat('%',#{qlrmc},'%')</if>
			</if>
			<if test="zjzl != null and zjzl != ''">
				AND a.zjzl = #{zjzl}
			</if>
			<if test="zjh != null and zjh != ''">
				AND a.zjh = #{zjh}
			</if>
			<if test="ywh != null and ywh != ''">
				AND a.ywh LIKE
				   <if test="dbName == 'oracle'">'%'||#{ywh}||'%'</if>
					<if test="dbName == 'mssql'">'%'+#{ywh}+'%'</if>
					<if test="dbName == 'mysql'">concat('%',#{ywh},'%')</if>
			</if>
			<if test="bz != null and bz !=''">
			   AND (a.bdcdyh is null or a.bdcdyh ='')
			</if>
		</where>
		<choose>
			<when test="page !=null and page.orderBy != null and page.orderBy != ''">
				ORDER BY ${page.orderBy}
			</when>
			<otherwise>
				ORDER BY a.sxh asc
			</otherwise>
		</choose>
	</select>
	<select id="findqlrgmList" resultType="RegBusQlr">
		SELECT 
			<include refid="regBusQlrColumns"/>
		FROM reg_bus_qlr a
		<include refid="regBusQlrJoins"/>
		<where>
			a.del_flag = #{DEL_FLAG_NORMAL}
			AND (a.pcode = #{pcode} or a.qlrmc = #{qlrmc} or (a.zjzl =  #{zjzl} and a.zjh = #{zjh}))
			<if test="ywh != null and ywh != ''">
				AND a.ywh = #{ywh}
			</if>
		</where>
		<choose>
			<when test="page !=null and page.orderBy != null and page.orderBy != ''">
				ORDER BY ${page.orderBy}
			</when>
			<otherwise>
				ORDER BY a.update_date DESC
			</otherwise>
		</choose>
	</select>
	
	<select id="findList_1" resultType="RegBusQlr">
		SELECT 
			<include refid="regBusQlrColumns"/>
		FROM reg_bus_qlr a
		<include refid="regBusQlrJoins"/>
		<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 trim(a.ywh) = #{ywh}
			</if>
			<if test="sxh != null and sxh != ''">
				AND a.sxh = #{sxh}
			</if>
			<if test="zjh != null and zjh != ''">
				AND a.zjh = #{zjh}
			</if>
			<if test="sfczr != null and sfczr != ''">
				AND a.sfczr = #{sfczr}
			</if>
			<if test="qszt != null and qszt != ''">
				AND a.qszt = #{qszt}
			</if>
			<if test="pcode != null and pcode != ''">
				AND a.pcode = #{pcode}
			</if>
			<if test="gmrid != null and gmrid != ''">
				AND a.gmrid = #{gmrid}
			</if>
			<if test="qlrmc != null and qlrmc != ''">
				AND a.qlrmc LIKE 
					<if test="dbName == 'oracle'">'%'||#{qlrmc}||'%'</if>
					<if test="dbName == 'mssql'">'%'+#{qlrmc}+'%'</if>
					<if test="dbName == 'mysql'">concat('%',#{qlrmc},'%')</if>
			</if>
			<if test="zjzl != null and zjzl != ''">
				AND a.zjzl = #{zjzl}
			</if>
		</where>
		<choose>
			<when test="page !=null and page.orderBy != null and page.orderBy != ''">
				ORDER BY ${page.orderBy}
			</when>
			<otherwise>
				ORDER BY a.sxh asc
			</otherwise>
		</choose>
	</select>
	
	<select id="findListdyh" resultType="RegBusQlr">
		SELECT 
			<include refid="regBusQlrColumns"/>
		FROM reg_bus_qlr a
		<include refid="regBusQlrJoins"/>
		<where>
			a.del_flag = #{DEL_FLAG_NORMAL}
			AND a.bdcdyh is null
			<if test="ywh != null and ywh != ''">
				AND a.ywh = #{ywh}
			</if>
		</where>
		<choose>
			<when test="page !=null and page.orderBy != null and page.orderBy != ''">
				ORDER BY ${page.orderBy}
			</when>
			<otherwise>
				ORDER BY a.sxh asc
			</otherwise>
		</choose>
	</select>
	<select id="findByYwh" resultType="RegBusQlr">
		SELECT 
			<include refid="regBusQlrColumns"/>
		FROM reg_bus_qlr a
		<include refid="regBusQlrJoins"/>
		<where>
			a.del_flag = 0
			AND a.ywh = #{ywh}
		</where>
		order by a.sxh asc
	</select>
	
	<select id="findByPic" resultType="RegBusQlr">
		SELECT 
			<include refid="regBusQlrColumns"/>
		FROM reg_bus_qlr a
		<include refid="regBusQlrJoins"/>
		<where>
			a.del_flag = 0
			AND a.ywh = #{0}
			AND a.sxh = #{1}
		</where>
		order by a.sxh asc
	</select>
	
	<select id="findListSxh" resultType="RegBusQlr">
		SELECT 
			<include refid="regBusQlrColumns"/>
		FROM reg_bus_qlr a
		<include refid="regBusQlrJoins"/>
		<where>
			a.del_flag = 0
			<if test="bdcdyh != null and bdcdyh != ''">
				AND a.bdcdyh = #{bdcdyh}
			</if>
			<if test="sxh != null and sxh != ''">
				AND a.sxh = #{sxh}
			</if>
			<if test="sfczr != null and sfczr != ''">
				AND a.sfczr = #{sfczr}
			</if>
			<if test="qlrmc != null and qlrmc != ''">
				AND a.qlrmc LIKE 
					<if test="dbName == 'oracle'">'%'||#{qlrmc}||'%'</if>
					<if test="dbName == 'mssql'">'%'+#{qlrmc}+'%'</if>
					<if test="dbName == 'mysql'">concat('%',#{qlrmc},'%')</if>
			</if>
			<if test="ywh != null and ywh != ''">
				AND trim(a.ywh) = #{ywh}
			</if>
			<if test="pcode != null and pcode != ''">
				AND a.pcode = #{pcode}
			</if>
			<if test="qszt != null and qszt != ''">
				AND a.qszt = #{qszt}
			</if>
		</where>
		<choose>
			<when test="page !=null and page.orderBy != null and page.orderBy != ''">
				ORDER BY ${page.orderBy}
			</when>
			<otherwise>
				ORDER BY a.sxh ASC
			</otherwise>
		</choose>
	</select>
	
	<select id="findAllList" resultType="RegBusQlr">
		SELECT 
			<include refid="regBusQlrColumns"/>
		FROM reg_bus_qlr a
		<include refid="regBusQlrJoins"/>
		<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.update_date DESC
			</otherwise>
		</choose>
	</select>
	
	
	<insert id="insertRelationYwh" parameterType="Map">
		INSERT INTO reg_bus_qlrywhrelation(ywh, qlrid, isuse) 
			VALUES (#{ywh}, #{qlrid}, 1)
	</insert>
	<update id="updateRelationYwhIsuse" parameterType="Map">
		UPDATE reg_bus_qlrywhrelation SET
			isuse = 0 
		WHERE ywh = #{ywh} AND qlrid = #{qlrid}
	</update>
	
	<insert id="insert">
		INSERT INTO reg_bus_qlr(
			id,
			ysdm,
			ywh,
			bdcdyh,
			sxh,
			qlrmc,
			pcode,
			bdcqzh,
			qzysxlh,
			sfczr,
			zjzl,
			zjh,
			fzjg,
			sshy,
			gj,
			hjszss,
			xb,
			dh,
			dz,
			yb,
			gzdw,
			dzyj,
			qlrlx,
			qlbl,
			gyfs,
			gyqk,
			bz,
			fddbrfzr,
			fddbrfzrdh,
			dlrmc,
			dlrdh,
			dljgmc,
			qszt,
			qllx,
			gmrid,
			sczt,
			veritysczt,
			create_by,
			create_date,
			update_by,
			update_date,
			del_flag
		) VALUES (
			#{id},
			#{ysdm},
			#{ywh},
			#{bdcdyh},
			#{sxh},
			#{qlrmc},
			#{pcode},
			#{bdcqzh},
			#{qzysxlh},
			#{sfczr},
			#{zjzl},
			#{zjh},
			#{fzjg},
			#{sshy},
			#{gj},
			#{hjszss},
			#{xb},
			#{dh},
			#{dz},
			#{yb},
			#{gzdw},
			#{dzyj},
			#{qlrlx},
			#{qlbl},
			#{gyfs},
			#{gyqk},
			#{bz},
			#{fddbrfzr},
			#{fddbrfzrdh},
			#{dlrmc},
			#{dlrdh},
			#{dljgmc},
			#{qszt},
			#{qllx},
			#{gmrid},
			#{sczt},
			#{veritysczt},
			#{createBy.id},
			#{createDate},
			#{updateBy.id},
			#{updateDate},
			#{delFlag}
		)
	</insert>
	
	<update id="update">
		UPDATE reg_bus_qlr SET 	
			ysdm = #{ysdm},
			ywh = #{ywh},
			bdcdyh = #{bdcdyh},
			sxh = #{sxh},
			qlrmc = #{qlrmc},
			pcode = #{pcode},
			bdcqzh = #{bdcqzh},
			qzysxlh = #{qzysxlh},
			sfczr = #{sfczr},
			zjzl = #{zjzl},
			zjh = #{zjh},
			fzjg = #{fzjg},
			sshy = #{sshy},
			gj = #{gj},
			hjszss = #{hjszss},
			xb = #{xb},
			dh = #{dh},
			dz = #{dz},
			yb = #{yb},
			gzdw = #{gzdw},
			dzyj = #{dzyj},
			qlrlx = #{qlrlx},
			qlbl = #{qlbl},
			gyfs = #{gyfs},
			gyqk = #{gyqk},
			bz = #{bz},
			fddbrfzr = #{fddbrfzr},
			fddbrfzrdh = #{fddbrfzrdh},
			dlrmc = #{dlrmc},
			dlrdh = #{dlrdh},
			dljgmc = #{dljgmc},
			qszt = #{qszt},
			qllx = #{qllx},
			gmrid = #{gmrid},
			sczt = #{sczt},
			veritysczt = #{veritysczt},
			update_by = #{updateBy.id},
			update_date = #{updateDate},
			del_flag = #{delFlag},
			pic = #{pic},
			tpbm = #{tpbm},
			piczt = #{piczt}
		WHERE id = #{id}
	</update>
	
	<update id="delete">
		DELETE from reg_bus_qlr
		WHERE id = #{id}
	</update>
	
	<update id="deleteywh">
		DELETE from reg_bus_qlr
		WHERE ywh = #{ywh}
	</update>
	
	<!-- 房屋所有权，预告更名调用 -->
	<update id="deleteqlr">
		DELETE FROM reg_bus_qlr 
		<where>
			<if test= "ywh != null and ywh != ''">
				AND  trim(ywh) = #{ywh}
			</if>
			<if test= "pcode != null and pcode != ''">
				AND  pcode = #{pcode}
			</if>
			<if test="bdcdyh != null and bdcdyh != ''">
				AND bdcdyh = #{bdcdyh}
			</if>
			<if test= "sxh != null and sxh != ''">
				AND  sxh = #{sxh}
			</if>
		</where>
	</update>

	<select id="findryywh" resultType="String">
		SELECT a.ywh FROM reg_bus_qlr a
		<include refid="regBusQlrJoins"/>
		<where>
			a.del_flag = #{DEL_FLAG_NORMAL}
			<if test="ywh != null and ywh != ''">
				AND trim(a.ywh) = #{ywh}
			</if>
			<if test="zjh != null and zjh != ''">
				AND a.zjh = #{zjh}
			</if>
			<if test="qszt != null and qszt != ''">
				AND a.qszt = #{qszt}
			</if>
			<if test="qlrmc != null and qlrmc != ''">
				AND a.qlrmc = #{qlrmc}
			</if>
			<if test="zjzl != null and zjzl != ''">
				AND a.zjzl = #{zjzl}
			</if>
			group by a.ywh
		</where>
	</select>
	
	
	<select id="progress" resultType="Map">
		select s.taskdefkey from reg_bus_qlr q LEFT JOIN reg_bus_slsq s on q.ywh = s.ywh where q.QLRMC=#{2} and (q.zjh != #{1} and q.zjh != concat(SUBSTR(#{1},0,6),substr(#{1},9,9))) and q.ywh = #{0}
	</select>
	<select id="querywxQlr" resultType="Map">
		select wm_concat(qlrmc) qlrmc from reg_bus_qlr where ywh=#{0} and del_flag='0' order by qlbl desc
	</select>
	<select id="querywxGyr" resultType="Map">
           select wm_concat(qlrmc) gyrmc from reg_bus_qlr where ywh=#{0} and del_flag='0' and (zjh != #{1} and zjh != concat(SUBSTR(#{1},0,6),substr(#{1},9,9)))
	</select>
	<select id="getSxh" resultType="Integer">
           select sxh from reg_bus_qlr where ywh=#{0} and del_flag='0' and qlrmc = #{1} and (zjh = #{2} or zjh = concat(SUBSTR(#{2},0,6),substr(#{2},9,9)))
	</select>
	<select id="getXgry" resultType="Map">
       SELECT qlrmc name,zjh zjhm FROM REG_BUS_QLR WHERE YWH=#{0}
	</select>
	
	<select id="DjxxSelect" resultType="Map">
       SELECT bdcqzh,zl,mj,bdcdyh FROM REG_BUS_BDCQZSDJXX WHERE YWH in (select ywh from reg_bus_qlr where qlrmc=#{qlrmc} and zjzl = #{zjzl} and zjh = #{zjh} and del_flag='0') and islogout = '0' and fzsj is not null and del_flag='0'
	</select>
	
	<select id="findDyr" resultType="RegBusQlr">
       SELECT 
       <include refid="regBusQlrColumns"/>
               from reg_bus_qlr a where a.ywh = (
              select ywh from reg_bus_bdcqzsdjxx where bdcdyh = (
               select bdcdyh from reg_bus_bdcqzsdjxx where bdcqzh = #{0} and islogout='0' and del_flag='0')and islogout='0' and del_flag='0' and rights= #{1})	
               and a.del_flag='0'
        </select>
</mapper>