<?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.RegBusSzDao">
    
	<sql id="regBusSzColumns">
		a.id AS "id",
		a.ywh AS "ywh",
		a.ysdm AS "ysdm",
		a.szmc AS "szmc",
		a.szzh AS "szzh",
		a.ysxlh AS "ysxlh",
		a.szry AS "szry",
		a.szsj AS "szsj",
		a.bz AS "bz",
		a.isdz AS "isdz",
		a.szlx AS "szlx",
		a.szsl AS "szsl",
		a.currmaxszbh AS "currmaxszbh",
		a.proc_ins_id AS "procInsId",
		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="regBusSzJoins">
	</sql>
    
	<select id="get" resultType="RegBusSz">
		SELECT 
			<include refid="regBusSzColumns"/>
		FROM reg_bus_sz a
		<include refid="regBusSzJoins"/>
		WHERE a.id = #{id}
	</select>
	
	<select id="findList" resultType="RegBusSz">
		SELECT 
			<include refid="regBusSzColumns"/>
		FROM reg_bus_sz a
		<include refid="regBusSzJoins"/>
		<where>
			a.del_flag = #{DEL_FLAG_NORMAL}
			<if test="ywh != null and ywh != ''">
				AND a.ywh = #{ywh}
			</if>
			<if test="szmc != null and szmc != ''">
				AND a.szmc LIKE 
					<if test="dbName == 'oracle'">'%'||#{szmc}||'%'</if>
					<if test="dbName == 'mssql'">'%'+#{szmc}+'%'</if>
					<if test="dbName == 'mysql'">concat('%',#{szmc},'%')</if>
			</if>
			<if test="szzh != null and szzh != ''">
				AND a.szzh LIKE 
					<if test="dbName == 'oracle'">'%'||#{szzh}||'%'</if>
					<if test="dbName == 'mssql'">'%'+#{szzh}+'%'</if>
					<if test="dbName == 'mysql'">concat('%',#{szzh},'%')</if>
			</if>
			<if test="ysxlh != null and ysxlh != ''">
				AND a.ysxlh = #{ysxlh}
			</if>
			
			<if test="szsj != null and szsj != ''">
				AND to_char(a.szsj,'yyyymmdd') LIKE 
					<if test="dbName == 'oracle'">'%'||to_char(#{szsj},'yyyymmdd')||'%'</if>
					<if test="dbName == 'mssql'">'%'+#{szsj}+'%'</if>
					<if test="dbName == 'mysql'">concat('%',#{szsj},'%')</if>
			</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="findAllList" resultType="RegBusSz">
		SELECT
			<include refid="regBusSzColumns"/>
		FROM reg_bus_sz a
		<include refid="regBusSzJoins"/>
		<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>
	
	<select id="findMaxSzzh" resultType="string">
		select MAX(a.currmaxszbh) from reg_bus_sz a  
		<where> a.szmc = #{szmc} AND a.del_flag = '0'
		<if test="ywh != null and ywh != ''">
			<if test="dbName == 'oracle'">
				AND to_char(a.szsj,'yyyy') = #{ywh}
			</if>
			<if test="dbName == 'mysql'">
				AND SUBSTR(SZSJ,1,4) = #{ywh}
			</if>
		</if>
		</where>
	</select>

	<select id="getBsstLZzqk" resultType="Integer" parameterType="java.lang.String">
		  select sum(a.szsl) from REG_BUS_SZ a,
		    (select max(b.id),b.ywh from reg_bus_bdcqzsdjxx b ,REG_BUS_SZ t
   			 where b.ywh=t.ywh AND b.rights = '1' and b.bdcdyh like '%'||#{lxType}||'%'
		  		AND to_char(t.szsj,'yyyymm') &gt;= #{qssj1} 
  				AND to_char(t.szsj,'yyyymm') &lt;= #{jssj1}
  			 group by b.ywh)s
  		  where s.ywh = a.ywh
		  union all
		  select sum(a.szsl) from REG_BUS_SZ a,
		    (select max(b.id),b.ywh from reg_bus_bdcqzsdjxx b ,REG_BUS_SZ t
   			 where b.ywh=t.ywh AND b.rights = '2' and b.bdcdyh like '%'||#{lxType}||'%'
		  		AND to_char(t.szsj,'yyyymm') &gt;= #{qssj1} 
  				AND to_char(t.szsj,'yyyymm') &lt;= #{jssj1}
  			 group by b.ywh)s
  		  where s.ywh = a.ywh
	</select>
	<select id="getBsstWZzqk" resultType="Integer" parameterType="java.lang.String">
		  select sum(a.szsl) from REG_BUS_SZ a,
		    (select max(b.id),b.ywh from reg_bus_bdcqzsdjxx b ,REG_BUS_SZ t
   			 where b.ywh=t.ywh AND b.rights = '1' and b.bdcdyh like '%'||#{lxType}||'%'
		  		AND to_char(t.szsj,'yyyymm') &gt;= #{qssj1} 
  				AND to_char(t.szsj,'yyyymm') &lt;= #{jssj1}
  			 group by b.ywh)s
  		  where s.ywh = a.ywh
		  union all
		  select sum(a.szsl) from REG_BUS_SZ a,
		    (select max(b.id),b.ywh from reg_bus_bdcqzsdjxx b ,REG_BUS_SZ t
   			 where b.ywh=t.ywh AND b.rights = '2' and b.bdcdyh like '%'||#{lxType}||'%'
		  		AND to_char(t.szsj,'yyyymm') &gt;= #{qssj1} 
  				AND to_char(t.szsj,'yyyymm') &lt;= #{jssj1}
  			 group by b.ywh)s
  		  where s.ywh = a.ywh
		  union all
	      select sum(a.szsl) from REG_BUS_SZ a,
		    (select max(b.id),b.ywh from reg_bus_bdcqzsdjxx b ,REG_BUS_SZ t
   			 where b.ywh=t.ywh AND b.rights = '3' and b.bdcdyh like '%'||#{lxType}||'%'
		  		AND to_char(t.szsj,'yyyymm') &gt;= #{qssj1} 
  				AND to_char(t.szsj,'yyyymm') &lt;= #{jssj1}
  			 group by b.ywh)s
	 	  where s.ywh = a.ywh
	</select>
	<select id="getBsstFZzqk" resultType="Integer" parameterType="java.lang.String">
		  select sum(a.szsl) from REG_BUS_SZ a,
		    (select max(b.id),b.ywh from reg_bus_bdcqzsdjxx b ,REG_BUS_SZ t
   			 where b.ywh=t.ywh and b.rights = '1' and b.bdcdyh like '%'||#{lxType}||'%'
		  		AND to_char(t.szsj,'yyyymm') &gt;= #{qssj1} 
  				AND to_char(t.szsj,'yyyymm') &lt;= #{jssj1}
  			 group by b.ywh)s
  		  where s.ywh = a.ywh
		  union all
		  select sum(a.szsl) from REG_BUS_SZ a,
		    (select max(b.id),b.ywh from reg_bus_bdcqzsdjxx b ,REG_BUS_SZ t
   			 where b.ywh=t.ywh AND b.rights = '2' and b.bdcdyh like '%'||#{lxType}||'%'
		  		AND to_char(t.szsj,'yyyymm') &gt;= #{qssj1} 
  				AND to_char(t.szsj,'yyyymm') &lt;= #{jssj1}
  			 group by b.ywh)s
  		  where s.ywh = a.ywh
	  	  union all
	  	  select sum(a.szsl) from REG_BUS_SZ a,
		    (select max(b.id),b.ywh from reg_bus_bdcqzsdjxx b ,REG_BUS_SZ t
   			 where b.ywh=t.ywh AND b.rights = '5' and b.bdcdyh like '%'||#{lxType}||'%'
   			 	AND b.djlx in ('213','431','427','428','316','606')
		  		AND to_char(t.szsj,'yyyymm') &gt;= #{qssj1} 
  				AND to_char(t.szsj,'yyyymm') &lt;= #{jssj1}
  			 group by b.ywh)s
  		  where s.ywh = a.ywh
  		  union all
  		  select sum(a.szsl) from REG_BUS_SZ a,
		    (select max(b.id),b.ywh from reg_bus_bdcqzsdjxx b ,REG_BUS_SZ t
   			 where b.ywh=t.ywh AND b.bdcdyh like '%'||#{lxType}||'%'
   			 	AND b.rights in ('4','5') 
			  	AND b.djlx not in ('213','431','427','428','316','606')
		  		AND to_char(t.szsj,'yyyymm') &gt;= #{qssj1} 
  				AND to_char(t.szsj,'yyyymm') &lt;= #{jssj1}
  			 group by b.ywh)s
 		  where s.ywh = a.ywh
	</select>
	
	<select id="getzzsl" resultType="Integer" parameterType="java.lang.String">
		  select sum(b.szsl)
		  from REG_BUS_SZ b
   			 <where>
			  		to_char(b.szsj,'yyyymm') &gt;= #{qssj1} 
	  				AND to_char(b.szsj,'yyyymm') &lt;= #{jssj1}
	  			<if test="del_flag != null and del_flag !=''">
  					AND b.del_flag = 0
  				</if>
  				AND b.szmc = #{szmc}
  		  </where> 
	</select>
	 
	<insert id="insert">
		INSERT INTO reg_bus_sz(
			id,
			ywh,
			ysdm,
			szmc,
			szzh,
			ysxlh,
			szry,
			szsj,
			bz,
			isdz,
			szlx,
			szsl,
			currmaxszbh,
			proc_ins_id,
			create_by,
			create_date,
			update_by,
			update_date,
			remarks,
			del_flag
		) VALUES (
			#{id},
			#{ywh},
			#{ysdm},
			#{szmc},
			#{szzh},
			#{ysxlh},
			#{szry},
			#{szsj},
			#{bz},
			#{isdz},
			#{szlx},
			#{szsl},
			#{currmaxszbh},
			#{procInsId},
			#{createBy.id},
			#{createDate},
			#{updateBy.id},
			#{updateDate},
			#{remarks},
			#{delFlag}
		)
	</insert>
	
	<update id="update">
		UPDATE reg_bus_sz SET 	
			ywh = #{ywh},
			ysdm = #{ysdm},
			szmc = #{szmc},
			szzh = #{szzh},
			ysxlh = #{ysxlh},
			szry = #{szry},
			szsj = #{szsj},
			bz = #{bz},
			isdz = #{isdz},
			szlx = #{szlx},
			szsl = #{szsl},
			currmaxszbh = #{currmaxszbh},
			proc_ins_id = #{procInsId},
			update_by = #{updateBy.id},
			update_date = #{updateDate},
			remarks = #{remarks}
		WHERE id = #{id}
	</update>
	
	<update id="delete">
		DELETE FROM reg_bus_sz 
		WHERE id = #{id}
	</update>
	
	<update id="deleteByywh">
		UPDATE reg_bus_sz SET 	
			del_flag ='1'
		WHERE ywh = #{ywh} AND del_flag='0'
	</update>
	
</mapper>