<?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.zcloud.mapper.dsno2.hidden.HiddenMapper">

	<!--表名 -->
	<sql id="tableName">
		BUS_HIDDEN
	</sql>

	<!--数据字典表名 -->
	<sql id="dicTableName">
		SYS_DICTIONARIES
	</sql>

	<!-- 字段 -->
	<sql id="Field">
		f.SOURCE,
		f.RISK_UNIT,
		f.IDENTIFICATION,
		f.RISK_DESCR,
		f.RISK_POSITION,
		f.LEVEL,
		f.CHECK_CONTENT,
		f.HIDDENDESCR,
		f.HIDDENPART,
		f.CREATOR,
		f.CREATTIME,
		f.RECTIFYDESCR,
		f.RECTIFICATIONTYPE,
		f.RECTIFICATIONOR,
		f.RECTIFICATIONTIME,
		f.HIDDENLEVEL,
		f.STATE,
		f.CHECKDEPT,
		f.CHECKOR,
		f.CHECKTIME,
		f.CHECKDESCR,
		f.ISQUALIFIED,
		f.ISDELETE,
		f.CORPINFO_ID,
		f.HIDDENFINDDEPT,
		f.RECTIFICATIONDEADLINE,
		f.RECTIFICATIONDEPT,
		f.HIDDEN_ID,
		f.CHECKRECORD_ID,
		f.RECORDITEM_ID,
		f.RISKITEM_ID,
		f.REVIEWOR,
		f.REVIEWTIME,
		f.REVIEWDEPT,
		f.LONGITUDE,
		f.LATITUDE,
		f.HAVESCHEME,
		f.HIDDENTYPE,
		f.ISCONFIRM,
		f.LISTMANAGER_ID,
		f.ADD_LIBRARY_FLAG
	</sql>

	<!-- 字段用于新增 -->
	<sql id="Field2">
		SOURCE,
		RISK_UNIT,
		IDENTIFICATION,
		RISK_DESCR,
		RISK_POSITION,
		LEVEL,
		CHECK_CONTENT,
		HIDDENDESCR,
		HIDDENPART,
		CREATOR,
		CREATTIME,
		RECTIFYDESCR,
		RECTIFICATIONTYPE,
		RECTIFICATIONOR,
		RECTIFICATIONTIME,
		HIDDENLEVEL,
		STATE,
		CHECKDEPT,
		CHECKOR,
		CHECKTIME,
		CHECKDESCR,
		ISQUALIFIED,
		ISDELETE,
		CORPINFO_ID,
		HIDDENFINDDEPT,
		RECTIFICATIONDEADLINE,
		RECTIFICATIONDEPT,
		HIDDEN_ID,
		CHECKRECORD_ID,
		RECORDITEM_ID,
		RISKITEM_ID,
		REVIEWOR,
		REVIEWTIME,
		REVIEWDEPT,
		LONGITUDE,
		LATITUDE,
		HAVESCHEME,
		HIDDENTYPE,
		ISCONFIRM,
		LISTMANAGER_ID,
		ADD_LIBRARY_FLAG
	</sql>

	<!-- 字段值 -->
	<sql id="FieldValue">
		#{SOURCE},
		#{RISK_UNIT},
		#{IDENTIFICATION},
		#{RISK_DESCR},
		#{RISK_POSITION},
		#{LEVEL},
		#{CHECK_CONTENT},
		#{HIDDENDESCR},
		#{HIDDENPART},
		#{CREATOR},
		#{CREATTIME},
		#{RECTIFYDESCR},
		#{RECTIFICATIONTYPE},
		#{RECTIFICATIONOR},
		#{RECTIFICATIONTIME},
		#{HIDDENLEVEL},
		#{STATE},
		#{CHECKDEPT},
		#{CHECKOR},
		#{CHECKTIME},
		#{CHECKDESCR},
		#{ISQUALIFIED},
		#{ISDELETE},
		#{CORPINFO_ID},
		#{HIDDENFINDDEPT},
		#{RECTIFICATIONDEADLINE},
		#{RECTIFICATIONDEPT},
		#{HIDDEN_ID},
		#{CHECKRECORD_ID},
		#{RECORDITEM_ID},
		#{RISKITEM_ID},
		#{REVIEWOR},
		#{REVIEWTIME},
		#{REVIEWDEPT},
		#{LONGITUDE},
		#{LATITUDE},
		#{HAVESCHEME},
		#{HIDDENTYPE},
		#{ISCONFIRM},
		#{LISTMANAGER_ID},
		#{ADD_LIBRARY_FLAG}
	</sql>

	<!-- 新增-->
	<insert id="save" parameterType="pd">
		insert into
	<include refid="tableName"></include>
		(
	<include refid="Field2"></include>
		) values (
	<include refid="FieldValue"></include>
		)
	</insert>

	<!-- 删除-->
	<delete id="delete" parameterType="pd">
		update
		<include refid="tableName"></include>
		set
			ISDELETE = '1'
		where
			HIDDEN_ID = #{HIDDEN_ID}
	</delete>

	<!-- 修改 -->
	<update id="edit" parameterType="pd">
		update
		<include refid="tableName"></include>
		set
 					RECTIFICATIONOR = #{RECTIFICATIONOR},
				    RECTIFICATIONDEPT= #{RECTIFICATIONDEPT}
		where
			HIDDEN_ID = #{HIDDEN_ID}
	</update>

		<!-- 修改信息 -->
	<update id="editInfo" parameterType="pd">
		update
		<include refid="tableName"></include>
		set
			<if test="RISK_UNIT != null and RISK_UNIT != ''">
				RISK_UNIT = #{RISK_UNIT},
 			</if>
			<if test="IDENTIFICATION != null and IDENTIFICATION != ''">
				IDENTIFICATION = #{IDENTIFICATION},
 			</if>
			<if test="RISK_DESCR != null and RISK_DESCR != ''">
				RISK_DESCR = #{RISK_DESCR},
 			</if>
			<if test="RISK_POSITION != null and RISK_POSITION != ''">
				RISK_POSITION = #{RISK_POSITION},
 			</if>
			<if test="LEVEL != null and LEVEL != ''">
				LEVEL = #{LEVEL},
 			</if>
			<if test="HIDDENTYPE != null and HIDDENTYPE != ''">
				HIDDENTYPE = #{HIDDENTYPE},
 			</if>

			CHECK_CONTENT = #{CHECK_CONTENT},
			HIDDENDESCR = #{HIDDENDESCR},
			HIDDENPART = #{HIDDENPART},

			<if test="RECTIFYDESCR != null and RECTIFYDESCR != ''">
				RECTIFYDESCR = #{RECTIFYDESCR},
 			</if>
			RECTIFICATIONTYPE = #{RECTIFICATIONTYPE},
			HIDDENLEVEL = #{HIDDENLEVEL},
			CHECKDEPT = #{CHECKDEPT},
			CHECKTIME = #{CHECKTIME},
			CHECKDESCR = #{CHECKDESCR},
			ISQUALIFIED = #{ISQUALIFIED},
			RECTIFICATIONDEADLINE = #{RECTIFICATIONDEADLINE},
			HIDDEN_ID = HIDDEN_ID,
			RECTIFICATIONOR = #{RECTIFICATIONOR},
		    RECTIFICATIONDEPT= #{RECTIFICATIONDEPT}
		where
			HIDDEN_ID = #{HIDDEN_ID}
	</update>

	<update id="changeState" parameterType="pd">
		update
		<include refid="tableName"></include>
		set
 			STATE = #{STATE}

		<if test="BUHEGE != null and BUHEGE != ''"><!-- 关键词检索 -->
			,RECTIFYDESCR = null
			,RECTIFICATIONTIME = null
		</if>
		where
			HIDDEN_ID = #{HIDDEN_ID}
	</update>
	<update id="editISCONFIRM" parameterType="pd">
		update
		<include refid="tableName"></include>
		set
 			ISCONFIRM = #{ISCONFIRM},
 			RECTIFICATIONDEPT = #{RECTIFICATIONDEPT},
 			RECTIFICATIONOR = #{RECTIFICATIONOR},
 			RECTIFICATIONDEADLINE = #{RECTIFICATIONDEADLINE}
		where
			HIDDEN_ID = #{HIDDEN_ID}
	</update>
	<update id="editHIDDENLEVEL" parameterType="pd">
		update
		<include refid="tableName"></include>
		set
 			HIDDENLEVEL = #{HIDDENLEVEL}
		where
			HIDDEN_ID = #{HIDDEN_ID}
	</update>

	<!-- 提交隐患 -->
	<update id="submit" parameterType="pd">
		update
		<include refid="tableName"></include>
		set
 			STATE = (case RECTIFICATIONTYPE when '2' then '1' when '1' then '4' end)
		where
			CHECKRECORD_ID = #{CHECKRECORD_ID} and ISDELETE = '0'
	</update>

	<!-- 提交清单隐患 -->
	<update id="submitList" parameterType="pd">
		update
		<include refid="tableName"></include>
		set
 			STATE = (case RECTIFICATIONTYPE when '2' then '1' when '1' then '4' end)
 			<if test="CHECKRECORD_ID != null and CHECKRECORD_ID != ''">
 				, CHECKRECORD_ID = #{CHECKRECORD_ID}
 			</if>
		where
			LISTMANAGER_ID = #{LISTMANAGER_ID}
			and (
				RECORDITEM_ID is null or RECORDITEM_ID = ''
				)
			and (
				RISKITEM_ID is null or RISKITEM_ID = ''
				)
			and ISDELETE = '0'
			and STATE = '0'
	</update>

	<select id="listHiddenAll" parameterType="pd" resultType="pd">
		select
		<include refid="Field"></include>
		from
		<include refid="tableName"></include> f
		where ( f.CHECKRECORD_ID = #{CHECKRECORD_ID} or LISTMANAGER_ID = #{LISTMANAGER_ID} ) and f.ISDELETE = '0' and f.STATE = '1'
	</select>
	<!-- 整改 -->
	<update id="rectify" parameterType="pd">
		update
		<include refid="tableName"></include>
		set
			RECTIFYDESCR = #{RECTIFYDESCR},
			RECTIFICATIONTIME = #{RECTIFICATIONTIME},
<!-- 			REVIEWOR = #{REVIEWOR}, -->
<!-- 			REVIEWDEPT = #{REVIEWDEPT}, -->
			HAVESCHEME = #{HAVESCHEME},
			STATE = '3'
		where
			HIDDEN_ID = #{HIDDEN_ID}
	</update>
	<!-- 复查 -->
	<update id="review" parameterType="pd">
		update
		<include refid="tableName"></include>
		set
			<if test="ISPASS != null and ISPASS != ''"><!-- 关键词检索 -->
				<if test="ISPASS == 1"><!-- 关键词检索 -->
					STATE = '3',
					REVIEWTIME = #{REVIEWTIME},
					CHECKDEPT = #{CHECKDEPT}
				</if>
				<if test="ISPASS == 0"><!-- 关键词检索 -->
					STATE = '1',
					RECTIFYDESCR = null,
					RECTIFICATIONTIME = null,
					REJECTREASON = #{REJECTREASON},
					HAVESCHEME = null
				</if>
			</if>
		where
			HIDDEN_ID = #{HIDDEN_ID}
	</update>
	<!-- 验收 -->
	<update id="check" parameterType="pd">
		update
		<include refid="tableName"></include>
		set
<!-- 			CHECKOR = #{CHECKOR}, -->
			CHECKTIME = #{CHECKTIME},
<!-- 			CHECKDESCR = #{CHECKDESCR}, -->
<!-- 			ISQUALIFIED = #{ISQUALIFIED}, -->
			STATE = '4'
		where
			HIDDEN_ID = #{HIDDEN_ID}
	</update>

	<!-- 定时修改过期隐患 -->
	<update id="editDeadline" parameterType="pd">
		update
		<include refid="tableName"></include> f
		set
			STATE = '-1'
		where
			f.RECTIFICATIONDEADLINE != ''
			and
			DATE_FORMAT( f.RECTIFICATIONDEADLINE,'%Y-%m-%d') &lt; DATE_FORMAT( NOW(),'%Y-%m-%d')
			and
			f.ISDELETE = '0'
			and
			f.STATE = '1'
	</update>
	<!-- 查询昨日过期隐患。发短信 -->
	<select id="getYestoday" parameterType="pd" resultType="pd">
		SELECT
			<include refid="Field"></include>
		FROM
			<include refid="tableName"></include> f
		WHERE
			f.RECTIFICATIONDEADLINE != ''
			AND TO_DAYS( NOW( ) ) - TO_DAYS( f.RECTIFICATIONDEADLINE ) = 1
			AND f.ISDELETE = '0'
			AND f.STATE = '-1'
	</select>

	<!-- 通过ID获取数据 -->
	<select id="findById" parameterType="pd" resultType="pd">
		select
		<include refid="Field"></include>,
		if (f.HAVEPLAN is null , '0', f.HAVEPLAN) as HAVEPLAN,
		cr.NAME CREATORNAME,
		re.NAME RECTIFICATIONORNAME,
		ch.NAME CHECKORNAME,
		crd.NAME HIDDENFINDDEPTNAME,
		red.NAME RECTIFICATIONDEPTNAME,
		chd.NAME CHECKDEPTNAME,
		lv.NAME HIDDENLEVELNAME,
		bc.LIST_NAME,
		ht.NAME HIDDENTYPENAME,
		hto.NAME as HIDDENTYPE1_NAME,
		htt.NAME as HIDDENTYPE2_NAME,
		htth.NAME as HIDDENTYPE3_NAME
		from
		<include refid="tableName"></include> f
		left join sys_user cr on cr.USER_ID = f.CREATOR
		left join sys_user re on re.USER_ID = f.RECTIFICATIONOR
		left join sys_user ch on ch.USER_ID = f.CHECKOR
		left join oa_department crd on crd.DEPARTMENT_ID = f.HIDDENFINDDEPT
		left join oa_department red on red.DEPARTMENT_ID = f.RECTIFICATIONDEPT
		left join oa_department chd on chd.DEPARTMENT_ID = f.CHECKDEPT
		left join bus_checkrecord bc on bc.CHECKRECORD_ID = f.CHECKRECORD_ID and bc.ISDELETE = 0
		left join <include refid="dicTableName"></include> lv on f.HIDDENLEVEL = lv.BIANMA
		left join <include refid="dicTableName"></include> ht on f.HIDDENTYPE = ht.BIANMA
		left join `zcloud_system`.sys_dictionaries hto on f.HIDDENTYPE1 = hto.DICTIONARIES_ID
		left join `zcloud_system`.sys_dictionaries htt on f.HIDDENTYPE2 = htt.DICTIONARIES_ID
		left join `zcloud_system`.sys_dictionaries htth on f.HIDDENTYPE3 = htth.DICTIONARIES_ID
		where
			f.HIDDEN_ID = #{HIDDEN_ID}
	</select>

	<!-- 列表 -->
	<select id="datalistPage" parameterType="page" resultType="pd">
		select
		<include refid="Field"></include>,
		cr.NAME CREATORNAME,
		re.NAME RECTIFICATIONORNAME,
		ch.NAME CHECKORNAME,
		rev.NAME REVIEWORNAME,
		crd.NAME HIDDENFINDDEPTNAME,
		red.NAME RECTIFICATIONDEPTNAME,
		chd.NAME CHECKDEPTNAME,
		lv.NAME HIDDENLEVELNAME,
		(select group_concat(chr.NAME) from BUS_HIDDENCHECK bh left join sys_user chr on chr.USER_ID=bh.USER_ID where bh.HIDDEN_ID=f.HIDDEN_ID ) CHECKNAME,
		(select group_concat(bh.USER_ID) from BUS_HIDDENCHECK bh  where bh.HIDDEN_ID=f.HIDDEN_ID ) CHECKUSERS
		from
		<include refid="tableName"></include> f
		left join sys_user cr on cr.USER_ID = f.CREATOR
		left join sys_user re on re.USER_ID = f.RECTIFICATIONOR
		left join sys_user ch on ch.USER_ID = f.CHECKOR
		left join sys_user rev on rev.USER_ID = f.REVIEWOR

		left join oa_department crd on crd.DEPARTMENT_ID = f.HIDDENFINDDEPT
		left join oa_department red on red.DEPARTMENT_ID = f.RECTIFICATIONDEPT
		left join oa_department chd on chd.DEPARTMENT_ID = f.CHECKDEPT

		left join
		<include refid="dicTableName"></include> lv
		on f.HIDDENLEVEL = lv.BIANMA
		where f.ISDELETE = '0' and f.STATE != '0'
		<if test="pd.KEYWORDS != null and pd.KEYWORDS != ''"><!-- 关键词检索 -->
			and
				(
					f.RISK_UNIT LIKE CONCAT(CONCAT('%', #{pd.KEYWORDS}),'%')
					or
					f.RISK_POSITION LIKE CONCAT(CONCAT('%', #{pd.KEYWORDS}),'%')
					or
					f.CHECK_CONTENT LIKE CONCAT(CONCAT('%', #{pd.KEYWORDS}),'%')
					or
					f.HIDDENDESCR LIKE CONCAT(CONCAT('%', #{pd.KEYWORDS}),'%')
				)
		</if>

		<if test="pd.HIDDENDESCR != null and pd.HIDDENDESCR != ''"><!-- 关键词检索 -->
			and f.HIDDENDESCR LIKE CONCAT(CONCAT('%', #{pd.HIDDENDESCR}),'%')
		</if>
		<if test="pd.ISQUALIFIED != null and pd.ISQUALIFIED != ''"><!-- 关键词检索 -->
			and f.ISQUALIFIED = #{pd.ISQUALIFIED}
		</if>
		<if test="pd.DEPARTMENT_ID != null and pd.DEPARTMENT_ID != ''"><!-- 关键词检索 -->
			and f.HIDDENFINDDEPT = #{pd.DEPARTMENT_ID}
		</if>
		<if test="pd.HIDDENLEVEL != null and pd.HIDDENLEVEL != ''"><!-- 关键词检索 -->
			and f.HIDDENLEVEL = #{pd.HIDDENLEVEL}
		</if>
		<if test="pd.STARTTIME != null and pd.STARTTIME != ''"><!-- 关键词检索 -->
			and f.CREATTIME &gt;= #{pd.STARTTIME}
		</if>
		<if test="pd.ENDTIME != null and pd.ENDTIME != ''">
			and f.CREATTIME &lt;= #{pd.ENDTIME}
		</if>
		<if test="pd.STATE != null and pd.STATE !='' and pd.STATE != 0" >
			and f.STATE = #{pd.STATE}
		</if>
		<if test="pd.SELF_USER_ID != null and pd.SELF_USER_ID != ''">
			and f.CREATOR = #{pd.SELF_USER_ID}
		</if>
		<if test="pd.DISPOSESTATE != null and pd.DISPOSESTATE != ''"><!-- 关键词检索 -->
			<if test="pd.DISPOSESTATE==2"><!-- 整改 -->
				<if test="pd.ISMAIN != null and pd.ISMAIN != ''">
					<choose>
						<when test="pd.ISMAIN == 1">
							AND f.CORPINFO_ID = #{pd.CORPINFO_ID}
						</when>
						<otherwise>
							and f.RECTIFICATIONOR = #{pd.SELFUSERID}
						</otherwise>
					</choose>
				</if>

				and f.STATE in('1','-1')
			</if>
			<if test="pd.DISPOSESTATE==3"><!-- 验收 -->
				and f.STATE = '3'
				<if test="pd.ISMAIN != null and pd.ISMAIN != ''">
					<choose>
						<when test="pd.ISMAIN == 1">
							AND f.CORPINFO_ID = #{pd.CORPINFO_ID}
						</when>
						<otherwise>
								and
							(
								select count(1)
								from BUS_HIDDENCHECK bh
								where
								bh.HIDDEN_ID=f.HIDDEN_ID
								and bh.USER_ID=#{pd.SELFUSERID}
								and bh.STATUS=0
							)>0
						</otherwise>
					</choose>
				</if>

			</if>
			<if test="pd.DISPOSESTATE==4"><!-- 已验收 -->
				and f.STATE = '4'

					<choose>
						<when test="pd.ISMAIN == 1">
							AND f.CORPINFO_ID = #{pd.CORPINFO_ID}
						</when>
						<otherwise>
									and (
								f.RECTIFICATIONOR = #{pd.SELFUSERID}
								or f.CREATOR = #{pd.SELFUSERID}
								or (
									select count(1)
									from BUS_HIDDENCHECK bh
									where
										bh.HIDDEN_ID=f.HIDDEN_ID
										and bh.USER_ID=#{pd.SELFUSERID}
									)>0
								)
						</otherwise>
					</choose>

			</if>
			<if test="pd.DISPOSESTATE==-1"><!-- 所有 -->
				<if test="pd.CORPINFO_ID != null and pd.CORPINFO_ID != ''"><!-- 关键词检索 -->
					and
						 f.CORPINFO_ID = #{pd.CORPINFO_ID}
				</if>
				<if test="pd.STATE != null and pd.STATE != ''"><!-- 关键词检索 -->
					and
						 f.STATE = #{pd.STATE}
				</if>
			</if>
		</if>
		<!-- 由BI页跳转过来参数 -->
		<if test="pd.IS_DAY != null and pd.IS_DAY != ''"><!-- 由BI页跳转过来参数 -->
			and
				TO_DAYS(f.CREATTIME) = TO_DAYS(NOW())
		</if>
		<if test="pd.IS_WEEK != null and pd.IS_WEEK != ''"><!-- 由BI页跳转过来参数 -->
			and
				YEARWEEK(DATE_FORMAT(f.CREATTIME,'%Y-%m-%d'),1) = YEARWEEK(NOW(),1)
		</if>
		<if test="pd.IS_XUN != null and pd.IS_XUN != ''"><!-- 由BI页跳转过来参数 -->
				<if test="pd.STARTTIME != null and pd.STARTTIME != ''"><!-- 由BI页跳转过来参数 -->
					and f.CREATTIME &gt;= #{pd.STARTTIME}
				</if>
				<if test="pd.ENDTIME != null and pd.ENDTIME != ''">
					and f.CREATTIME &lt;= #{pd.ENDTIME}
				</if>
		</if>
		<if test="pd.IS_MONTH != null and pd.IS_MONTH != ''"><!-- 由BI页跳转过来参数 -->
			and
				DATE_FORMAT(f.CREATTIME,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m')
		</if>
		<if test="pd.IS_QUARTER != null and pd.IS_QUARTER != ''"><!-- 由BI页跳转过来参数 -->
			and f.CREATTIME BETWEEN #{pd.STARTTIME} and #{pd.ENDTIME}
		</if>
		<if test="pd.IS_YEAR != null and pd.IS_YEAR != ''"><!-- 由BI页跳转过来参数 -->
			and
				YEAR(f.CREATTIME)=YEAR(NOW())
		</if>
		<if test="pd.BISTATE != null and pd.BISTATE != ''"><!-- 由BI页跳转过来参数 -->
			<choose>
				<when test='pd.BISTATE=="1"'>
					and	f.STATE != '0'
				</when>
				<when test='pd.BISTATE=="2"'>
					and f.STATE != '4'
				</when>
				<when test='pd.BISTATE=="3"'>
					and f.STATE = '4'
				</when>
			</choose>
		</if>
		order by f.CREATTIME  desc
	</select>

	<select id="listAll" parameterType="pd" resultType="pd">
		select
		<include refid="Field"></include>,
		cr.NAME CREATORNAME,
		re.NAME RECTIFICATIONORNAME,
		ch.NAME CHECKORNAME,
		rev.NAME REVIEWORNAME,
		crd.NAME HIDDENFINDDEPTNAME,
		red.NAME RECTIFICATIONDEPTNAME,
		chd.NAME CHECKDEPTNAME,
		lv.NAME HIDDENLEVELNAME,
		(select group_concat(chr.NAME) from BUS_HIDDENCHECK bh left join sys_user chr on chr.USER_ID=bh.USER_ID where bh.HIDDEN_ID=f.HIDDEN_ID ) CHECKNAME
		from
		<include refid="tableName"></include> f
		left join sys_user cr on cr.USER_ID = f.CREATOR
		left join sys_user re on re.USER_ID = f.RECTIFICATIONOR
		left join sys_user ch on ch.USER_ID = f.CHECKOR
		left join sys_user rev on rev.USER_ID = f.REVIEWOR
		left join oa_department crd on crd.DEPARTMENT_ID = f.HIDDENFINDDEPT
		left join oa_department red on red.DEPARTMENT_ID = f.RECTIFICATIONDEPT
		left join oa_department chd on chd.DEPARTMENT_ID = f.CHECKDEPT
		left join
		<include refid="dicTableName"></include> lv
		on f.HIDDENLEVEL = lv.BIANMA
		where f.ISDELETE = '0' and f.STATE != '0'
		<if test="SOURCE != null and SOURCE != ''">
			and f.SOURCE=#{SOURCE}
		</if>
		<if test="IS_CANCEL != null and IS_CANCEL != ''"><!-- 是否作废 -->
			and f.CHECKRECORD_ID is null
		</if>
		<if test="CORPINFO_ID != null and CORPINFO_ID != ''">
				and f.CORPINFO_ID = #{CORPINFO_ID}
		</if>

		<if test="FIND_CHECKRECORD_ID != null and FIND_CHECKRECORD_ID != ''">
			and f.CHECKRECORD_ID=#{CHECKRECORD_ID}
		</if>
		<if test="KEYWORDS != null and KEYWORDS != ''"><!-- 关键词检索 -->
			and
				(
					f.RISK_UNIT LIKE CONCAT(CONCAT('%', #{KEYWORDS}),'%')
					or
					f.RISK_POSITION LIKE CONCAT(CONCAT('%', #{KEYWORDS}),'%')
					or
					f.CHECK_CONTENT LIKE CONCAT(CONCAT('%', #{KEYWORDS}),'%')
					or
					f.HIDDENDESCR LIKE CONCAT(CONCAT('%', #{KEYWORDS}),'%')
				)
		</if>
		<if test="HIDDENDESCR != null and HIDDENDESCR != ''"><!-- 关键词检索 -->
			and f.HIDDENDESCR LIKE CONCAT(CONCAT('%', #{HIDDENDESCR}),'%')
		</if>
		<if test="ISQUALIFIED != null and ISQUALIFIED != ''"><!-- 关键词检索 -->
			and f.ISQUALIFIED = #{ISQUALIFIED}
		</if>
		<if test="DEPARTMENT_ID != null and DEPARTMENT_ID != ''"><!-- 关键词检索 -->
			and f.HIDDENFINDDEPT = #{DEPARTMENT_ID}
		</if>
		<if test="HIDDENLEVEL != null and HIDDENLEVEL != ''"><!-- 关键词检索 -->
			and f.HIDDENLEVEL = #{HIDDENLEVEL}
		</if>
		<if test="STARTTIME != null and STARTTIME != ''"><!-- 关键词检索 -->
			and f.CREATTIME &gt;= #{STARTTIME}
		</if>
		<if test="ENDTIME != null and ENDTIME != ''">
			and f.CREATTIME &lt;= #{ENDTIME}
		</if>
		<if test="STATE != null and STATE !='' and STATE != 0" >
			and f.STATE = #{STATE}
		</if>
		<if test="SELF_USER_ID != null and SELF_USER_ID != ''">
			and f.CREATOR = #{SELF_USER_ID}
		</if>
		<if test="DISPOSESTATE != null and DISPOSESTATE != ''"><!-- 关键词检索 -->
			<if test="DISPOSESTATE==2"><!-- 整改 -->
				and f.RECTIFICATIONOR = #{SELFUSERID}
				and f.STATE in('1','-1')
			</if>
			<if test="DISPOSESTATE==3"><!-- 验收 -->
				and f.STATE = '3'
				and (select count(1) from BUS_HIDDENCHECK bh where bh.USER_ID=#{SELFUSERID} and bh.HIDDEN_ID=f.HIDDEN_ID and bh.STATUS=0)>0
			</if>
			<if test="DISPOSESTATE==4"><!-- 已验收 -->
				and f.STATE = '4'
				and (f.RECTIFICATIONOR = #{SELFUSERID} or f.CREATOR = #{SELFUSERID} or (select count(1) from BUS_HIDDENCHECK bh where bh.USER_ID=#{SELFUSERID} and bh.HIDDEN_ID=f.HIDDEN_ID)>0)
			</if>
			<if test="DISPOSESTATE==-1"><!-- 所有 -->
				<if test="CORPINFO_ID != null and CORPINFO_ID != ''"><!-- 关键词检索 -->
					and
						 f.CORPINFO_ID = #{CORPINFO_ID}
				</if>
				<if test="STATE != null and STATE != ''"><!-- 关键词检索 -->
					and
						 f.STATE = #{STATE}
				</if>
			</if>
		</if>
		<if test="HIDDEN_IDS != null and HIDDEN_IDS != ''"><!-- 关键词检索 -->
			and
				(
					f.HIDDEN_ID in
					<foreach item="item" index="index" collection="HIDDEN_IDS" open="(" separator="," close=")">
			                #{item}
					</foreach>
				)
		</if>
			order by f.STATE ,f.CREATTIME desc
	</select>

	<select id="listAllToPrint" parameterType="pd" resultType="pd">
		select
		<include refid="Field"></include>,
		cr.NAME CREATORNAME,
		re.NAME RECTIFICATIONORNAME,
		ch.NAME CHECKORNAME,
		rev.NAME REVIEWORNAME,
		crd.NAME HIDDENFINDDEPTNAME,
		red.NAME RECTIFICATIONDEPTNAME,
		chd.NAME CHECKDEPTNAME,
		lv.NAME HIDDENLEVELNAME,
		( SELECT max( bh.CHECK_TIME ) FROM BUS_HIDDENCHECK bh WHERE bh.HIDDEN_ID = f.HIDDEN_ID ) CHECK_TIME,
		(select group_concat(chr.NAME) from BUS_HIDDENCHECK bh left join sys_user chr on chr.USER_ID=bh.USER_ID where bh.HIDDEN_ID=f.HIDDEN_ID ) CHECKNAME
		from
		<include refid="tableName"></include> f
		left join sys_user cr on cr.USER_ID = f.CREATOR
		left join sys_user re on re.USER_ID = f.RECTIFICATIONOR
		left join sys_user ch on ch.USER_ID = f.CHECKOR
		left join sys_user rev on rev.USER_ID = f.REVIEWOR
		left join oa_department crd on crd.DEPARTMENT_ID = f.HIDDENFINDDEPT
		left join oa_department red on red.DEPARTMENT_ID = f.RECTIFICATIONDEPT
		left join oa_department chd on chd.DEPARTMENT_ID = f.CHECKDEPT
		left join
		<include refid="dicTableName"></include> lv
		on f.HIDDENLEVEL = lv.BIANMA
		where f.ISDELETE = '0' and f.STATE != '0'
		<if test="SOURCE != null and SOURCE != ''">
			and f.SOURCE=#{SOURCE}
		</if>
		<if test="IS_CANCEL != null and IS_CANCEL != ''"><!-- 是否作废 -->
			and f.CHECKRECORD_ID is null
		</if>
		<if test="CORPINFO_ID != null and CORPINFO_ID != ''">
				and f.CORPINFO_ID = #{CORPINFO_ID}
		</if>

		<if test="FIND_CHECKRECORD_ID != null and FIND_CHECKRECORD_ID != ''">
			and f.CHECKRECORD_ID=#{CHECKRECORD_ID}
		</if>
		<if test="KEYWORDS != null and KEYWORDS != ''"><!-- 关键词检索 -->
			and
				(
					f.RISK_UNIT LIKE CONCAT(CONCAT('%', #{KEYWORDS}),'%')
					or
					f.RISK_POSITION LIKE CONCAT(CONCAT('%', #{KEYWORDS}),'%')
					or
					f.CHECK_CONTENT LIKE CONCAT(CONCAT('%', #{KEYWORDS}),'%')
					or
					f.HIDDENDESCR LIKE CONCAT(CONCAT('%', #{KEYWORDS}),'%')
				)
		</if>
		<if test="HIDDENDESCR != null and HIDDENDESCR != ''"><!-- 关键词检索 -->
			and f.HIDDENDESCR LIKE CONCAT(CONCAT('%', #{HIDDENDESCR}),'%')
		</if>
		<if test="ISQUALIFIED != null and ISQUALIFIED != ''"><!-- 关键词检索 -->
			and f.ISQUALIFIED = #{ISQUALIFIED}
		</if>
		<if test="DEPARTMENT_ID != null and DEPARTMENT_ID != ''"><!-- 关键词检索 -->
			and f.HIDDENFINDDEPT = #{DEPARTMENT_ID}
		</if>
		<if test="HIDDENLEVEL != null and HIDDENLEVEL != ''"><!-- 关键词检索 -->
			and f.HIDDENLEVEL = #{HIDDENLEVEL}
		</if>
		<if test="STARTTIME != null and STARTTIME != ''"><!-- 关键词检索 -->
			and f.CREATTIME &gt;= #{STARTTIME}
		</if>
		<if test="ENDTIME != null and ENDTIME != ''">
			and f.CREATTIME &lt;= #{ENDTIME}
		</if>
		<if test="STATE != null and STATE !='' and STATE != 0" >
			and f.STATE = #{STATE}
		</if>
		<if test="SELF_USER_ID != null and SELF_USER_ID != ''">
			and f.CREATOR = #{SELF_USER_ID}
		</if>
		<if test="DISPOSESTATE != null and DISPOSESTATE != ''"><!-- 关键词检索 -->
			<if test="DISPOSESTATE==2"><!-- 整改 -->
				and f.RECTIFICATIONOR = #{SELFUSERID}
				and f.STATE in('1','-1')
			</if>
			<if test="DISPOSESTATE==3"><!-- 验收 -->
				and f.STATE = '3'
				and (select count(1) from BUS_HIDDENCHECK bh where bh.USER_ID=#{SELFUSERID} and bh.HIDDEN_ID=f.HIDDEN_ID and bh.STATUS=0)>0
			</if>
			<if test="DISPOSESTATE==4"><!-- 已验收 -->
				and f.STATE = '4'
				and (f.RECTIFICATIONOR = #{SELFUSERID} or f.CREATOR = #{SELFUSERID} or (select count(1) from BUS_HIDDENCHECK bh where bh.USER_ID=#{SELFUSERID} and bh.HIDDEN_ID=f.HIDDEN_ID)>0)
			</if>
			<if test="DISPOSESTATE==-1"><!-- 所有 -->
				<if test="CORPINFO_ID != null and CORPINFO_ID != ''"><!-- 关键词检索 -->
					and
						 f.CORPINFO_ID = #{CORPINFO_ID}
				</if>
				<if test="STATE != null and STATE != ''"><!-- 关键词检索 -->
					and
						 f.STATE = #{STATE}
				</if>
			</if>
		</if>
		<if test="HIDDEN_IDS != null and HIDDEN_IDS != ''"><!-- 关键词检索 -->
			and
				(
					f.HIDDEN_ID in
					<foreach item="item" index="index" collection="HIDDEN_IDS" open="(" separator="," close=")">
			                #{item}
					</foreach>
				)
		</if>
			order by f.CREATTIME desc
	</select>

	<!-- 批量删除 -->
	<delete id="deleteAll" parameterType="String">
		update
		<include refid="tableName"></include>
		set
			ISDELETE = '1'
		where
			HIDDEN_ID in
		<foreach item="item" index="index" collection="ArrayDATA_IDS" open="(" separator="," close=")">
                 #{item}
		</foreach>
	</delete>

	<!-- 批量修改 -->
	<delete id="updateAll" parameterType="pd">
		update
		<include refid="tableName"></include>
		set
			CHECKRECORD_ID = #{CHECKRECORD_ID},
			STATE = '0'<!-- 未提交隐患,不计入统计,列表内不可看 -->
		where
			HIDDEN_ID in
		<foreach item="item" index="index" collection="IDS" open="(" separator="," close=")">
                 #{item}
		</foreach>
	</delete>

	<!-- 按日统计 -->
	<select id="hiddenStatisticsByDay" parameterType="pd" resultType="pd">
        SELECT
		  d.date,IFNULL(f.count,0) as count
		FROM
		  v_dates d
		left join (SELECT
				DATE_FORMAT( t.CREATTIME, '%Y-%m-%d') AS CREATTIME,
				count(1) as count
			FROM
				BUS_HIDDEN t
			WHERE t.ISDELETE = '0' and t.STATE != '0' and
				t.HIDDENLEVEL = #{HIDDENLEVEL} and t.CORPINFO_ID=#{CORPINFO_ID}
			  <if test="USER_ID != null and USER_ID != ''"><!-- 关键词检索 -->
				and
					t.CREATOR = #{USER_ID}
			</if>
			GROUP BY
			DATE_FORMAT( t.CREATTIME, '%Y-%m-%d') ) f on f.CREATTIME = d.date
		WHERE
		  d.date BETWEEN #{START_DAY} AND #{END_DAY}
		ORDER BY
		  d.date desc
   	</select>

   	<!-- 总数统计 -->
	<select id="hiddenStatisticsAll" parameterType="pd" resultType="pd">
        SELECT
				t.STATE,
				count(1) as count
			FROM
				BUS_HIDDEN t
			WHERE
				  t.CORPINFO_ID=#{CORPINFO_ID} and t.ISDELETE = '0' and t.STATE != '0'
			   <if test="USER_ID != null and USER_ID != ''"><!-- 关键词检索 -->
					and
						t.CREATOR = #{USER_ID}
				</if>
			GROUP BY
				t.STATE
   	</select>
	<!--  根据用户ID获取发现隐患信息  -->
	<select id="getCountByCreatorId" parameterType="pd" resultType="pd">
       SELECT
			f.CREATOR,f.STATE
		FROM
			BUS_HIDDEN f
		WHERE f.ISDELETE = '0' and f.STATE != '0'
		<if test="DEPARTMENT_IDS != null and DEPARTMENT_IDS != ''"><!-- 关键词检索 -->
			and
				(
					f.REVIEWDEPT in
					<foreach item="item" index="index" collection="DEPARTMENT_IDS" open="(" separator="," close=")">
			                #{item}
					</foreach>
					or f.REVIEWOR = #{SELFUSERID}
				)
		</if>
	   <if test="SELFUSERID != null and SELFUSERID != ''"><!-- 关键词检索 -->
				and
					f.CREATOR = #{SELFUSERID}
		</if>
		<if test="CORPINFO_ID != null and CORPINFO_ID != ''">
			and f.CORPINFO_ID =#{CORPINFO_ID}
		</if>

   	</select>
   	<!--根据用户ID获取隐患信息 -->
	<select id="getCountByRectifiCationorId" parameterType="pd" resultType="pd">
       SELECT
			f.CREATOR,f.STATE
		FROM
			BUS_HIDDEN f
		WHERE f.ISDELETE = '0' and f.STATE != '0'
		<if test="DEPARTMENT_IDS != null and DEPARTMENT_IDS != ''"><!-- 关键词检索 -->
			and
				(
					f.RECTIFICATIONDEPT in
					<foreach item="item" index="index" collection="DEPARTMENT_IDS" open="(" separator="," close=")">
			                #{item}
					</foreach>
					or  f.RECTIFICATIONOR = #{SELFUSERID}
				)
		</if>
		<if test="SELFUSERID != null and SELFUSERID != ''">
			and f.RECTIFICATIONOR = #{SELFUSERID}
		</if>
		<if test="CORPINFO_ID != null and CORPINFO_ID != ''">
			and f.CORPINFO_ID =#{CORPINFO_ID}
		</if>
   	</select>

   	   	<!--根据用户ID获取待验收隐患-->
	<select id="getCountByCheckorId" parameterType="pd" resultType="pd">
       SELECT
			f.CREATOR,f.STATE
		FROM
			BUS_HIDDEN f
		WHERE f.ISDELETE = '0' and f.STATE != '0'
		<!-- <if test="DEPARTMENT_IDS != null and DEPARTMENT_IDS != ''">关键词检索
			and
				(
					f.CHECKDEPT in
					<foreach item="item" index="index" collection="DEPARTMENT_IDS" open="(" separator="," close=")">
			                #{item}
					</foreach>
					or  f.CHECKOR = #{SELFUSERID}
				)
		</if> -->
		<if test="SELFUSERID != null and SELFUSERID != ''">
			and (select count(1) from BUS_HIDDENCHECK bh where bh.USER_ID=#{SELFUSERID} and bh.HIDDEN_ID=f.HIDDEN_ID and bh.STATUS=0)>0
		</if>
		<if test="CORPINFO_ID != null and CORPINFO_ID != ''">
			and f.CORPINFO_ID =#{CORPINFO_ID}
		</if>
   	</select>

   	<!--根据企业ID获取驾驶舱隐患统计信息 -->
	<select id="getBICount" parameterType="pd" resultType="pd">
       SELECT
			<include refid="Field"></include>
		FROM
			BUS_HIDDEN f
		WHERE f.ISDELETE = '0' and f.STATE != '0'
		<if test="CORPINFO_ID != null and CORPINFO_ID != ''">
			and f.CORPINFO_ID =#{CORPINFO_ID}
		</if>
		<if test="userId != null and userId != ''">
			and f.CREATOR =#{userId}
		</if>
		<if test="IS_DAY != null and IS_DAY != ''"><!-- 关键词检索 -->
			and
				TO_DAYS(f.CREATTIME) = TO_DAYS(NOW())
		</if>
		<if test="IS_WEEK != null and IS_WEEK != ''"><!-- 关键词检索 -->
			and
				YEARWEEK(DATE_FORMAT(f.CREATTIME,'%Y-%m-%d'),1) = YEARWEEK(NOW(),1)
		</if>
		<if test="IS_XUN != null and IS_XUN != ''"><!-- 关键词检索 -->
				<if test="STARTTIME != null and STARTTIME != ''"><!-- 关键词检索 -->
					and f.CREATTIME &gt;= #{STARTTIME}
				</if>
				<if test="ENDTIME != null and ENDTIME != ''">
					and f.CREATTIME &lt;= #{ENDTIME}
				</if>
		</if>
		<if test="IS_MONTH != null and IS_MONTH != ''"><!-- 关键词检索 -->
			and
				DATE_FORMAT(f.CREATTIME,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m')
		</if>
		<if test="IS_QUARTER != null and IS_QUARTER != ''"><!-- 关键词检索 -->
			and f.CREATTIME BETWEEN #{STARTTIME} and #{ENDTIME}
		</if>
		<if test="IS_YEAR != null and IS_YEAR != ''"><!-- 关键词检索 -->
			and
				YEAR(f.CREATTIME)=YEAR(NOW())
		</if>
   	</select>

   	<!-- 清单其他隐患 -->
	<select id="getOtherHidden" parameterType="pd" resultType="pd">
       SELECT
			<include refid="Field"></include>
		FROM
			BUS_HIDDEN f
		WHERE f.ISDELETE = '0'
		and f.SOURCE = '3'
		<if test="STATE != null and STATE != ''"><!-- 关键词检索 -->
			and
				f.STATE = #{STATE}
		</if>
		<if test="USER_ID != null and USER_ID != ''"><!-- 关键词检索 -->
			and
				f.CREATOR = #{USER_ID}
		</if>
		<if test="LISTMANAGER_ID != null and LISTMANAGER_ID != ''"><!-- 关键词检索 -->
			and
				f.LISTMANAGER_ID = #{LISTMANAGER_ID}
		</if>
		<if test="CHECKRECORD_ID != null and CHECKRECORD_ID != ''"><!-- 关键词检索 -->
			and
				f.CHECKRECORD_ID = #{CHECKRECORD_ID}
		</if>
		<if test="IS_DAY != null and IS_DAY != ''"><!-- 关键词检索 -->
			and
				TO_DAYS(f.CREATTIME) = TO_DAYS(NOW())
		</if>
		<if test="IS_WEEK != null and IS_WEEK != ''"><!-- 关键词检索 -->
			and
				YEARWEEK(DATE_FORMAT(f.CREATTIME,'%Y-%m-%d')) = YEARWEEK(NOW())
		</if>
		<if test="IS_XUN != null and IS_XUN != ''"><!-- 关键词检索 -->
				<if test="BEGINTIME != null and BEGINTIME != ''"><!-- 关键词检索 -->
					and f.CREATTIME &gt;= #{BEGINTIME}
				</if>
				<if test="ENDTIME != null and ENDTIME != ''">
					and f.CREATTIME &lt;= #{ENDTIME}
				</if>
		</if>

		<if test="IS_MONTH != null and IS_MONTH != ''"><!-- 关键词检索 -->
			and
				DATE_FORMAT(f.CREATTIME,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m')
		</if>
		<if test="IS_QUARTER != null and IS_QUARTER != ''"><!-- 关键词检索 -->
			and
				QUARTER(f.CREATTIME) = QUARTER(NOW())
		</if>
		<if test="IS_HALFYEAR != null and IS_HALFYEAR != ''">
			AND
			CONVERT(concat(DATE_FORMAT(f.CREATTIME, '%Y'), INTERVAL(CONVERT(MONTH(f.CREATTIME),SIGNED),7,13) ),SIGNED) =
			CONVERT(concat(DATE_FORMAT(NOW(), '%Y'), INTERVAL(CONVERT(MONTH(NOW()),SIGNED),7,13) ),SIGNED)
		</if>
		<if test="IS_YEAR != null and IS_YEAR != ''"><!-- 关键词检索 -->
			and
				YEAR(f.CREATTIME)=YEAR(NOW())
		</if>
		ORDER BY f.CREATTIME
   	</select>

	<!--  -->
	<select id="getCountByHiddenType" parameterType="pd" resultType="pd">
		SELECT
			HIDDENTYPE,
			COUNT(1) AS count
		FROM
			BUS_HIDDEN
		WHERE
			CORPINFO_ID = #{CORPINFO_ID}
		GROUP BY
			HIDDENTYPE
	</select>


  	<select id="getHiddenNum" parameterType="pd" resultType="pd">
  		<if test="TYPE != null and TYPE != ''">
			<if test="TYPE==1"><!-- 所有 -->
				SELECT
					count( 1 ) NUMBER,
					DATE_FORMAT( h.CREATTIME, '%Y-%m' ) TIME
				FROM
					bus_hidden h
				WHERE
					h.ISDELETE = '0'
					AND h.CORPINFO_ID = #{CORPINFO_ID}
					AND h.state != '0'
					AND DATE_FORMAT( h.CREATTIME, '%Y-%m' ) &gt;= #{TIME}
					AND DATE_FORMAT( h.CREATTIME, '%Y-%m' ) &lt;= DATE_FORMAT( NOW(), '%Y-%m' )
				GROUP BY
					DATE_FORMAT( h.CREATTIME, '%Y-%m' )
				ORDER BY
		   			DATE_FORMAT( h.CREATTIME, '%Y-%m' )
			</if>
			<if test="TYPE==2"><!-- 未整改   -->
				SELECT
					count( 1 ) NUMBER,
					DATE_FORMAT( h.CREATTIME, '%Y-%m' ) TIME
				FROM
					bus_hidden h
				WHERE
					h.ISDELETE = '0'
					AND h.CORPINFO_ID = #{CORPINFO_ID}
					AND h.STATE in('1','-1')
					AND DATE_FORMAT( h.CREATTIME, '%Y-%m' ) &gt;= #{TIME}
					AND DATE_FORMAT( h.CREATTIME, '%Y-%m' ) &lt;= DATE_FORMAT( NOW(), '%Y-%m' )
				GROUP BY
					DATE_FORMAT( h.CREATTIME, '%Y-%m' )
				ORDER BY
		   			DATE_FORMAT( h.CREATTIME, '%Y-%m' )
			</if>
			<if test="TYPE==3"><!-- 已整改   -->
				SELECT
					count( 1 ) NUMBER,
					DATE_FORMAT( h.RECTIFICATIONTIME, '%Y-%m' ) TIME
				FROM
					bus_hidden h
				WHERE
					h.ISDELETE = '0'
					AND h.CORPINFO_ID = #{CORPINFO_ID}
					AND h.STATE = '3'
					AND DATE_FORMAT( h.RECTIFICATIONTIME, '%Y-%m' ) &gt;= #{TIME}
					AND DATE_FORMAT( h.RECTIFICATIONTIME, '%Y-%m' ) &lt;= DATE_FORMAT( NOW(), '%Y-%m' )
				GROUP BY
					DATE_FORMAT( h.RECTIFICATIONTIME, '%Y-%m' )
				ORDER BY
		   			DATE_FORMAT( h.RECTIFICATIONTIME, '%Y-%m' )
			</if>
		</if>
  	</select>
    <select id="librarylistPage" resultType="com.zcloud.entity.PageData">
		select c.CORP_TYPE  as INDUSTRY_TYPE_ONE,
			   ito.NAME     as INDUSTRY_TYPE_ONE_NAME,
			   c.CORP_TYPE2 as INDUSTRY_TYPE_TWO,
			   itt.NAME     as INDUSTRY_TYPE_TWO_NAME,
			   c.CORP_TYPE3 as INDUSTRY_TYPE_THREE,
			   itth.NAME    as INDUSTRY_TYPE_THREE_NAME,
			   c.CORP_TYPE4 as INDUSTRY_TYPE_FOUR,
			   itf.NAME 	as INDUSTRY_TYPE_FOUR_NAME,
			   c.CORPINFO_ID as CORP_INFO_ID,
			   c.CORP_NAME as CORP_INFO_NAME,
			   a.RISK_UNIT 	as RISK_UNIT_NAME,
			   a.IDENTIFICATION,
			   a.HIDDENTYPE,
			   a.HIDDENPART as HIDDEN_PART,
			   a.HIDDENDESCR as HIDDEN_DESCR,
			   a.RECTIFYDESCR,
			   a.HIDDEN_ID,
			   a.HIDDENTYPE1,
			   hto.NAME as HIDDENTYPE1_NAME,
			   a.HIDDENTYPE2,
			   htt.NAME as HIDDENTYPE2_NAME,
			   a.HIDDENTYPE3,
			   htth.NAME as HIDDENTYPE3_NAME
		from bus_hidden a
				 left join bus_corp_info c on c.CORPINFO_ID = a.CORPINFO_ID
				 left join `zcloud_system`.sys_dictionaries ito on c.CORP_TYPE = ito.DICTIONARIES_ID
				 left join `zcloud_system`.sys_dictionaries itt on c.CORP_TYPE2 = itt.DICTIONARIES_ID
				 left join `zcloud_system`.sys_dictionaries itth on c.CORP_TYPE3 = itth.DICTIONARIES_ID
				 left join `zcloud_system`.sys_dictionaries itf on c.CORP_TYPE4 = itf.DICTIONARIES_ID
				 left join `zcloud_system`.sys_dictionaries hto on a.HIDDENTYPE1 = hto.DICTIONARIES_ID
				 left join `zcloud_system`.sys_dictionaries htt on a.HIDDENTYPE2 = htt.DICTIONARIES_ID
				 left join `zcloud_system`.sys_dictionaries htth on a.HIDDENTYPE3 = htth.DICTIONARIES_ID
		where a.ISDELETE = '0' and a.ADD_LIBRARY_FLAG is null
		<if test="pd.CORP_TYPE != null and pd.CORP_TYPE != ''">
			and c.CORP_TYPE = #{pd.CORP_TYPE}
		</if>
		<if test="pd.CORP_TYPE2 != null and pd.CORP_TYPE2 != ''">
			and c.CORP_TYPE2 = #{pd.CORP_TYPE2}
		</if>
		<if test="pd.CORP_TYPE3 != null and pd.CORP_TYPE3 != ''">
			and c.CORP_TYPE3 = #{pd.CORP_TYPE3}
		</if>
		<if test="pd.CORP_TYPE4 != null and pd.CORP_TYPE3 != ''">
			and c.CORP_TYPE4 = #{pd.CORP_TYPE4}
		</if>
		<if test="pd.RISK_UNIT_NAME != null and pd.RISK_UNIT_NAME != ''">
			and a.RISK_UNIT LIKE CONCAT(CONCAT('%', #{pd.RISK_UNIT_NAME}),'%')
		</if>
		<if test="pd.IDENTIFICATION != null and pd.IDENTIFICATION != ''">
			and a.IDENTIFICATION LIKE CONCAT(CONCAT('%', #{pd.IDENTIFICATION}),'%')
		</if>
		<if test="pd.HIDDENPART != null and pd.HIDDENPART != ''">
			and a.HIDDENPART LIKE CONCAT(CONCAT('%', #{pd.HIDDENPART}),'%')
		</if>
		<if test="pd.HIDDENDESCR != null and pd.HIDDENDESCR != ''">
			and a.HIDDENDESCR LIKE CONCAT(CONCAT('%', #{pd.HIDDENDESCR}),'%')
		</if>
		<if test="pd.HIDDENTYPE1 != null and pd.HIDDENTYPE1 != ''">
			and a.HIDDENTYPE1 = #{pd.HIDDENTYPE1}
		</if>
		<if test="pd.HIDDENTYPE2 != null and pd.HIDDENTYPE2 != ''">
			and a.HIDDENTYPE2 = #{pd.HIDDENTYPE2}
		</if>
		<if test="pd.HIDDENTYPE3 != null and pd.HIDDENTYPE3 != ''">
			and a.HIDDENTYPE3 = #{pd.HIDDENTYPE3}
		</if>
		<if test="pd.STATE != null and pd.STATE != '' ">
			and a.STATE = #{pd.STATE}
		</if>

	</select>
    <!-- 公司停止试用，删除所有隐患 -->
	<update id="hiddenEndTrial" parameterType="pd">
		update
		<include refid="tableName"></include>
		set
			ISDELETE = '1'
		where
			CORPINFO_ID = #{CORPINFO_ID}
			AND ISDELETE = '0'
	</update>
	<update id="changeLibrary" parameterType="pd">
		update
		<include refid="tableName"></include>
		set
		ADD_LIBRARY_FLAG = #{ADD_LIBRARY_FLAG}
		where
		    <if test="HIDDEN_ID != null and HIDDEN_ID != ''">
				HIDDEN_ID = #{HIDDEN_ID}
			</if>
			<if test="HIDDEN_IDS != null and HIDDEN_IDS.length > 0">
		    	HIDDEN_ID in
					<foreach item="item" index="index" collection="HIDDEN_IDS" open="(" separator="," close=")">
						#{item}
					</foreach>
			</if>
	</update>

	<select id="getRectifyExamine" parameterType="pd" resultType="pd" >
		SELECT
			(select count(1) from bus_hidden h where DATE_FORMAT(h.CREATTIME,'%Y-%m') = #{MONTH} and h.CORPINFO_ID=c.CORPINFO_ID and h.ISDELETE=0 and h.STATE != 0) as HIDDEN_COUNT,
			(select count(1) from bus_hidden h where DATE_FORMAT(h.CREATTIME,'%Y-%m') = #{MONTH} and h.CORPINFO_ID=c.CORPINFO_ID and h.ISDELETE=0 and h.STATE = 4) as RECTIFY_COUNT,
			c.CORPINFO_ID
		FROM
			BUS_CORP_INFO c
		where
			c.ISDELETE = '0'
	</select>
</mapper>
