<?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.dsno1.corp.CorpInfoMapper">

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

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

	<!-- 字段 -->
	<sql id="Field">
		f.CORP_NAME,
		f.CODE,
		f.COMPANY_AREA,
		f.ADDRESS,
		f.CREATE_DATE,
		f.SCALE,
		f.LONGITUDE,
		f.LATITUDE,
		f.POSTAL_CODE,
		f.EMPLOYEES,
		f.AREA_COVERED,
		f.TOTALASSETS,
		f.REGCAPITAL,
		f.SALESREVENUE,
		f.ANNUALPROFIT,
		f.ECO_TYPE,
		f.ECO_TYPE2,
		f.ECO_TYPE_NAME,
		f.INDUSTRY,
		f.CORP_TYPE,
		f.CORP_TYPE2,
		f.CORP_TYPE3,
		f.CORP_TYPE4,
		f.CORP_TYPE_NAME,
		f.CORP_TYPE_SMALL,
		f.LR_NAME,
		f.LR_POSITION,
		f.LR_PHONE,
		f.LR_MOBILE,
		f.CONTACTS,
		f.CONTACTS_PHONE,
		f.CHECKPERSON,
		f.CHECKPERSONPHONE,
		f.ISDELETE,
		f.OPERATOR,
		f.OPERATTIME,
		f.CREATTIME,
		f.CREATOR,
		f.CORPINFO_ID,
		f.FOURTYPE,
		f.ISRECEIVE,
		f.PROVINCE,
		f.CITY,
		f.COUNTY,
		f.SMS_COUNT,
		f.ISREMIND,
		f.ISUSE,
		f.VILLAGE,
		f.LOGIN_USER_ID,
		f.ADDRESS_BUSINESS,
		f.ADDRESS_OFFICE,
		f.CORP_STATE,
		f.FIXED_ASSETS,
		f.YEAR_OUTPUT_VALUE,
		f.REG_TYPE,
		f.SUBORDINATION,
		f.CORP_OF_TYPE,
		f.CORP_OF_TYPE2,
		f.INDUSTRY_DEPARTMENT,
		f.SCALE_TYPE,
		f.SAFETY_NAME,
		f.SAFETY_POST,
		f.SAFETY_NUMBER,
		f.SAFETY_PHONE,
		f.CORP_CONFIRM,
		f.CORP_EXAMINE,
		f.REPORT_CYCLE,
		f.REPORT_STATUS,
		f.STREET,
		f.ISDELIVER,
		f.DELIVERDATE,
		f.CREATUSERID,
		f.TRAINTYPE,
		f.VIPLEVEL,
		f.USERS_NUM,
		f.STATUS,
		f.REGULARTYPE,
	    f.PERSONNEL_POSITION,
	    f.enterpriseId,
	    f.PERSONNEL_TYPE
	</sql>

	<!-- 字段用于新增 -->
	<sql id="Field2">
		CORP_NAME,
		CODE,
		COMPANY_AREA,
		ADDRESS,
		CREATE_DATE,
		SCALE,
		LONGITUDE,
		LATITUDE,
		POSTAL_CODE,
		EMPLOYEES,
		AREA_COVERED,
		TOTALASSETS,
		REGCAPITAL,
		SALESREVENUE,
		ANNUALPROFIT,
		ECO_TYPE,
		ECO_TYPE2,
		ECO_TYPE_NAME,
		INDUSTRY,
		CORP_TYPE,
		CORP_TYPE2,
		CORP_TYPE3,
		CORP_TYPE4,
		CORP_TYPE_NAME,
		CORP_TYPE_SMALL,
		LR_NAME,
		LR_POSITION,
		LR_PHONE,
		LR_MOBILE,
		CONTACTS,
		CONTACTS_PHONE,
		CHECKPERSON,
		CHECKPERSONPHONE,
		ISDELETE,
		OPERATOR,
		OPERATTIME,
		CREATTIME,
		CREATOR,
		CORPINFO_ID,
		FOURTYPE,
		ISRECEIVE,
		PROVINCE,
		CITY,
		COUNTY,
		SMS_COUNT,
		USE_DATE_START,
		USE_DATE_END,
		ISUSE,
		VILLAGE,
		LOGIN_USER_ID,
		ADDRESS_BUSINESS,
		ADDRESS_OFFICE,
		CORP_STATE,
		FIXED_ASSETS,
		YEAR_OUTPUT_VALUE,
		REG_TYPE,
		SUBORDINATION,
		CORP_OF_TYPE,
		CORP_OF_TYPE2,
		INDUSTRY_DEPARTMENT,
		SCALE_TYPE,
		SAFETY_NAME,
		SAFETY_POST,
		SAFETY_NUMBER,
		SAFETY_PHONE,
		CORP_CONFIRM,
		CORP_EXAMINE,
		REPORT_CYCLE,
		REPORT_STATUS,
		STREET,
		ISDELIVER,
		DELIVERDATE,
		CREATUSERID,
		TRAINTYPE,
		VIPLEVEL,
		USERS_NUM,
		STATUS,
		REGULARTYPE,
		PERSONNEL_POSITION,
		PERSONNEL_TYPE
	</sql>

	<!-- 字段值 -->
	<sql id="FieldValue">
		#{CORP_NAME},
		#{CODE},
		#{COMPANY_AREA},
		#{ADDRESS},
		#{CREATE_DATE},
		#{SCALE},
		#{LONGITUDE},
		#{LATITUDE},
		#{POSTAL_CODE},
		#{EMPLOYEES},
		#{AREA_COVERED},
		#{TOTALASSETS},
		#{REGCAPITAL},
		#{SALESREVENUE},
		#{ANNUALPROFIT},
		#{ECO_TYPE},
		#{ECO_TYPE2},
		#{ECO_TYPE_NAME},
		#{INDUSTRY},
		#{CORP_TYPE},
		#{CORP_TYPE2},
		#{CORP_TYPE3},
		#{CORP_TYPE4},
		#{CORP_TYPE_NAME},
		#{CORP_TYPE_SMALL},
		#{LR_NAME},
		#{LR_POSITION},
		#{LR_PHONE},
		#{LR_MOBILE},
		#{CONTACTS},
		#{CONTACTS_PHONE},
		#{CHECKPERSON},
		#{CHECKPERSONPHONE},
		#{ISDELETE},
		#{OPERATOR},
		#{OPERATTIME},
		#{CREATTIME},
		#{CREATOR},
		#{CORPINFO_ID},
		#{FOURTYPE},
		#{ISRECEIVE},
		#{PROVINCE},
		#{CITY},
		#{COUNTY},
		#{SMS_COUNT},
		#{USE_DATE_START},
		#{USE_DATE_END},
		#{ISUSE},
		#{VILLAGE},
		#{LOGIN_USER_ID},
		#{ADDRESS_BUSINESS},
		#{ADDRESS_OFFICE},
		#{CORP_STATE},
		#{FIXED_ASSETS},
		#{YEAR_OUTPUT_VALUE},
		#{REG_TYPE},
		#{SUBORDINATION},
		#{CORP_OF_TYPE},
		#{CORP_OF_TYPE2},
		#{INDUSTRY_DEPARTMENT},
		#{SCALE_TYPE},
		#{SAFETY_NAME},
		#{SAFETY_POST},
		#{SAFETY_NUMBER},
		#{SAFETY_PHONE},
		#{CORP_CONFIRM},
		#{CORP_EXAMINE},
		#{REPORT_CYCLE},
		#{REPORT_STATUS},
		#{STREET},
		#{ISDELIVER},
		#{DELIVERDATE},
		#{CREATUSERID},
		#{TRAINTYPE},
		#{VIPLEVEL},
		#{USERS_NUM},
		#{STATUS},
		#{REGULARTYPE},
		#{PERSONNEL_POSITION},
		#{PERSONNEL_TYPE}
	</sql>

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

	<insert id="saveTrial" parameterType="pd">
		insert into
		BUS_CORP_TRIAL
		(
		ID ,CORPINFO_ID ,ISDELETE ,OPERATTIME ,OPERATOR ,ISTRIAL ,TRIALTIME
		) values (
		#{ID} ,#{CORPINFO_ID} ,#{ISDELETE} ,#{OPERATTIME} ,#{OPERATOR} ,#{ISTRIAL} ,#{TRIALTIME}
		)
	</insert>

	<insert id="goStop" parameterType="pd">
		insert into
		BUS_CORP_STOP
		(ID ,OPERATTIME ,OPERATOR ,STOPREASON ,ISDELETE ,CORPINFO_ID, ISSTOP
		) values (
		#{ID} ,#{OPERATTIME} ,#{OPERATOR} ,#{STOPREASON} ,#{ISDELETE} ,#{CORPINFO_ID} ,#{ISSTOP}
		)
	</insert>
	<update id="goFormal" parameterType="pd">
		update
		<include refid="tableName"></include>
		set
			STATUS = '1'
		where
			CORPINFO_ID = #{CORPINFO_ID}
	</update>
	<delete id="deleteTrial" parameterType="pd">
		update
		bus_corp_trial
		set
		ISDELETE = '1'
		where
		CORPINFO_ID = #{CORPINFO_ID}
	</delete>
	<delete id="deleteStop" parameterType="pd">
		update
		bus_corp_stop
		set
		ISDELETE = '1'
		where
		CORPINFO_ID = #{CORPINFO_ID}
	</delete>
	<delete id="deleteStart" parameterType="pd">
		update
		bus_corp_stop
		set
		ISDELETE = '1'
		where
		CORPINFO_ID = #{CORPINFO_ID}
	</delete>

	<insert id="goStart" parameterType="pd">
		insert into
		BUS_CORP_STOP
		(ID ,OPERATTIME ,OPERATOR ,STOPREASON ,ISDELETE ,CORPINFO_ID, ISSTOP
		) values (
		#{ID} ,#{OPERATTIME} ,#{OPERATOR} ,#{STOPREASON} ,#{ISDELETE} ,#{CORPINFO_ID} ,#{ISSTOP}
		)
	</insert>

	<!--批量添加停用企业-->
	<insert id="batchGoStop" parameterType="java.util.List">
		insert into
		BUS_CORP_STOP
		(ID ,OPERATTIME ,OPERATOR ,STOPREASON ,ISDELETE ,CORPINFO_ID, ISSTOP)
		values
		<foreach collection="list" item="item" separator="," index="index">
			<trim prefix=" (" suffix=")" suffixOverrides=",">
			   #{item.ID},
		       #{item.OPERATTIME},
		       #{item.OPERATOR},
		       #{item.STOPREASON},
		       #{item.ISDELETE},
		       #{item.CORPINFO_ID},
		       #{item.ISSTOP}
			</trim>
		</foreach>
	</insert>

	<insert id="batchGoStart" parameterType="java.util.List">
		insert into
		BUS_CORP_STOP
		(ID ,OPERATTIME ,OPERATOR ,STOPREASON ,ISDELETE ,CORPINFO_ID, ISSTOP)
		values
		<foreach collection="list" item="item" separator="," index="index">
			<trim prefix=" (" suffix=")" suffixOverrides=",">
				#{item.ID},
				#{item.OPERATTIME},
				#{item.OPERATOR},
				#{item.STOPREASON},
				#{item.ISDELETE},
				#{item.CORPINFO_ID},
				#{item.ISSTOP}
			</trim>
		</foreach>
	</insert>

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

	<update id="editStatus" parameterType="pd">
		update
		<include refid="tableName"></include>
		set
		STATUS = '3'
		where
		CORPINFO_ID = #{CORPINFO_ID}
	</update>
	<update id="editStartStatus" parameterType="pd">
		update
		<include refid="tableName"></include>
		set
		STATUS = '1'
		where
		CORPINFO_ID = #{CORPINFO_ID}
	</update>
	<update id="editTrialStatus" parameterType="pd">
		update
		<include refid="tableName"></include>
		set
		STATUS = '2'
		where
		CORPINFO_ID = #{CORPINFO_ID}
	</update>
	<!-- 修改短信条数 -->
		<update id="updateSmsCount" parameterType="pd">
		update
		<include refid="tableName"></include>
		set
			SMS_COUNT =  #{SMS_COUNT}
		where
			CORPINFO_ID = #{CORPINFO_ID}
	</update>
		<update id="updateISuse" parameterType="pd">
		update
		<include refid="tableName"></include>
		set
			ISUSE =  #{ISUSE}
		where
			CORPINFO_ID = #{CORPINFO_ID}
	</update>
		<update id="updateuse" parameterType="pd">
		update
		<include refid="tableName"></include>
		set
			ISUSE =  #{ISUSE},
			USE_DATE_START=#{USE_DATE_START},
			USE_DATE_END=#{USE_DATE_END}
		where
			CORPINFO_ID = #{CORPINFO_ID}
	</update>
<!-- 修改 -->
	<update id="edit" parameterType="pd">
		update
		<include refid="tableName"></include>
		set
			CORP_NAME = #{CORP_NAME},
			CODE = #{CODE},
			COMPANY_AREA = #{COMPANY_AREA},
			ADDRESS = #{ADDRESS},
			CREATE_DATE = #{CREATE_DATE},
			SCALE = #{SCALE},
			LONGITUDE = #{LONGITUDE},
			LATITUDE = #{LATITUDE},
			POSTAL_CODE = #{POSTAL_CODE},
			EMPLOYEES = #{EMPLOYEES},
			AREA_COVERED = #{AREA_COVERED},
			TOTALASSETS = #{TOTALASSETS},
			REGCAPITAL = #{REGCAPITAL},
			SALESREVENUE = #{SALESREVENUE},
			ANNUALPROFIT = #{ANNUALPROFIT},
			ECO_TYPE = #{ECO_TYPE},
			ECO_TYPE2 = #{ECO_TYPE2},
			ECO_TYPE_NAME=#{ECO_TYPE_NAME},
			INDUSTRY = #{INDUSTRY},
			CORP_TYPE = #{CORP_TYPE},
			CORP_TYPE2 = #{CORP_TYPE2},
			CORP_TYPE3 = #{CORP_TYPE3},
			CORP_TYPE4 = #{CORP_TYPE4},
			CORP_TYPE_NAME = #{CORP_TYPE_NAME},
			CORP_TYPE_SMALL = #{CORP_TYPE_SMALL},
			LR_NAME = #{LR_NAME},
			LR_POSITION = #{LR_POSITION},
			LR_PHONE = #{LR_PHONE},
			LR_MOBILE = #{LR_MOBILE},
			CONTACTS = #{CONTACTS},
			CONTACTS_PHONE = #{CONTACTS_PHONE},
			CHECKPERSON = #{CHECKPERSON},
			CHECKPERSONPHONE = #{CHECKPERSONPHONE},
			OPERATOR = #{OPERATOR},
			OPERATTIME = #{OPERATTIME},
			CREATOR = #{CREATOR},
			<if test="FOURTYPE != null and FOURTYPE != ''">
				FOURTYPE=#{FOURTYPE},
			</if>
			PROVINCE=#{PROVINCE},
			CITY=#{CITY},
			COUNTY=#{COUNTY},
			VILLAGE = #{VILLAGE},
	        LOGIN_USER_ID = #{LOGIN_USER_ID},
	        ADDRESS_BUSINESS = #{ADDRESS_BUSINESS},
	        ADDRESS_OFFICE = #{ADDRESS_OFFICE},
	        CORP_STATE = #{CORP_STATE},
	        FIXED_ASSETS = #{FIXED_ASSETS},
	        YEAR_OUTPUT_VALUE = #{YEAR_OUTPUT_VALUE},
	        REG_TYPE = #{REG_TYPE},
	        SUBORDINATION = #{SUBORDINATION},
	        CORP_OF_TYPE = #{CORP_OF_TYPE},
	        CORP_OF_TYPE2 = #{CORP_OF_TYPE2},
	        INDUSTRY_DEPARTMENT = #{INDUSTRY_DEPARTMENT},
	        SCALE_TYPE = #{SCALE_TYPE},
	        SAFETY_NAME = #{SAFETY_NAME},
	        SAFETY_POST = #{SAFETY_POST},
	        SAFETY_NUMBER = #{SAFETY_NUMBER},
	        SAFETY_PHONE = #{SAFETY_PHONE},
	        CORP_CONFIRM = #{CORP_CONFIRM},
	        CORP_EXAMINE = #{CORP_EXAMINE},
			STREET = #{STREET},
	        REPORT_CYCLE=#{REPORT_CYCLE},
			REPORT_STATUS=#{REPORT_STATUS},
			TRAINTYPE=#{TRAINTYPE},
			VIPLEVEL = #{VIPLEVEL},
			USERS_NUM = #{USERS_NUM},
			REGULARTYPE = #{REGULARTYPE},
			PERSONNEL_POSITION = #{PERSONNEL_POSITION}
			<if test="enterpriseId != null and enterpriseId != ''">
				,enterpriseId=#{enterpriseId}
			</if>
		where
			CORPINFO_ID = #{CORPINFO_ID}
	</update>

	<!--修改交付信息-->
	<update id="editDeliver" parameterType="pd">
		update
		<include refid="tableName"></include>
		set
		ISDELIVER = '1',
		DELIVERDATE = #{DELIVERDATE}
		where
		CORPINFO_ID = #{CORPINFO_ID}
	</update>


	<update id="editIsReceive" parameterType="pd">
		update
		<include refid="tableName"></include>
		set
		ISRECEIVE = #{ISRECEIVE}
		where
		CORPINFO_ID = #{CORPINFO_ID}
	</update>

	<!-- 通过ID获取数据 -->
	<select id="findById" parameterType="pd" resultType="pd">
		select
		<include refid="Field"></include>,
		a.ROLE_NAME as ROLE_NAME,
		s.NAME as SCALE_NAME,
		r.CORPINFO_ID,
		r.AQ_BZ_LEVEL,
		r.AQ_BZ_NO,
		r.AQ_BZ_UNIT,
		r.AQ_BZ_TIME,
		r.WHETHER_HYGIENE,
		r.WHETHER_HAZARDS,
		r.WHETHER_SCARCE,
		r.WHETHER_CHEMICALS,
		r.WHETHER_SPECIALEQUIPMENT,
		r.WHETHER_SPECIALPEOPLE,
		r.WHETHER_COALGAS,
		r.WHETHER_FIRE,
		r.WHETHER_CONFINED,
		r.WHETHER_POWDER,
		r.WHETHER_LIGHTNING,
		r.WHETHER_ACTINOGEN,
		r.WHETHER_LIQUIDAMMONIA,
		r.WHETHER_PIPELINE,
		cs.NAME CORP_STATE_NAME,
		sub.NAME SUBORDINATIONNAME,
		abl.NAME AQ_BZ_LEVEL_NAME,
		abm.NAME as TRAINTYPE_NAME,
		province.NAME as PROVINCE_NAME,
		city.NAME as CITY_NAME,
		county.NAME as COUNTY_NAME,
		village.NAME as VILLAGE_NAME,
		street.NAME as STREET_NAME,
		type.name as SMALL_TYPE_NAME,
		type2.name as SMALL_TYPE2_NAME,
		an.AGENT_NUMBER_ID,
		b.ISTRIAL,
		t.NAME as REGULARTYPE_NAME,
		b.TRIALTIME,
		/*ss.NAME AS IMPLEMENTATION_PERRON,
		ss.PHONE AS IMPLEMENTATION_PERRON_PHONE,
		xm.NAME AS PROJECT_LEADER,
		xm.PHONE AS PROJECT_LEADER_PHONE,
		yw.NAME AS MAINTENANCE_PERSON,
		yw.PHONE AS MAINTENANCE_PERSON_PHONE,
		bam.AGENCY_NAME as AGENCY,*/
		(select max(endtime.USE_DATE_END) from bus_corp_time endtime where  endtime.CORPINFO_ID = f.CORPINFO_ID and endtime.ISDELETE = '0')  USE_DATE_END,
		bmff.DELIVERY_INFO as DELIVERY_INFO
		from
		<include refid="tableName"></include> f
		left join sys_role a on a.ROLE_ID = f.VIPLEVEL
		/*LEFT JOIN sys_user ss ON ss.USER_ID = m.IMPLEMENTATION_PERRON
		LEFT JOIN sys_user xm ON xm.USER_ID = m.PROJECT_LEADER
		LEFT JOIN sys_user yw ON yw.USER_ID = m.MAINTENANCE_PERSON*/
		 left join BUS_CORP_TRIAL b on b.CORPINFO_ID = f.CORPINFO_ID
		left join SYS_DICTIONARIES s on s.DICTIONARIES_ID = f.SCALE
		left join SYS_DICTIONARIES abm on abm.DICTIONARIES_ID = f.TRAINTYPE
        LEFT JOIN bus_corp_info_related r on r.CORPINFO_ID =f.CORPINFO_ID
        LEFT JOIN SYS_DICTIONARIES cs on cs.DICTIONARIES_ID = f.CORP_STATE
        LEFT JOIN SYS_DICTIONARIES sub on sub.DICTIONARIES_ID = f.SUBORDINATION
        LEFT JOIN SYS_DICTIONARIES abl on abl.DICTIONARIES_ID = r.AQ_BZ_LEVEL
		left join SYS_DICTIONARIES type on type.DICTIONARIES_ID = f.SMALL_TYPE
		left join SYS_DICTIONARIES type2 on type2.DICTIONARIES_ID = f.SMALL_TYPE2
		left join SYS_DICTIONARIES province on province.BIANMA = f.PROVINCE
		left join SYS_DICTIONARIES city on city.BIANMA = f.CITY
		left join SYS_DICTIONARIES county on county.BIANMA = f.COUNTY
		left join SYS_DICTIONARIES village on village.BIANMA = f.VILLAGE
		left join SYS_DICTIONARIES street on street.BIANMA = f.STREET
		left join BUS_AGENT_NUMBER an on an.USER_ID = f.CREATUSERID and an.CORPLEVEL = f.VIPLEVEL and an.ISDELETE = '0'
		/*LEFT JOIN BUS_AGENCY_MANAGEMENT bam ON bam.AGENCY_MANAGEMENT_ID = m.AGENCY_MANAGEMENT_ID and bam.ISDELETE = '0'*/
		left join BUS_CORPTYPE t on t.CORPTYPE_ID = f.REGULARTYPE and t.ISDELETE = '0'
		LEFT JOIN BUS_MAINTENANCE_FLOW bmff on bmff.ISDELETE = '0' and bmff.CORPINFO_ID = f.CORPINFO_ID and bmff.STEP = '5' and bmff.DELIVERY_INFO is not null
		where
			f.CORPINFO_ID = #{CORPINFO_ID}
		ORDER BY bmff.CREATTIME DESC
		LIMIT 0,1
	</select>


	<!-- 通过企业名获取数据 -->
	<select id="findByName" parameterType="pd" resultType="pd" >
		select
			<include refid="Field"></include>
		from
			<include refid="tableName"></include>f
		where
			f.CORP_NAME = #{CORP_NAME} and f.ISDELETE = '0'
			<if test="CORPINFO_ID != null and CORPINFO_ID != ''">
				and f.CORPINFO_ID != #{CORPINFO_ID}
			</if>
	</select>

	<select id="findBytrialId" parameterType="pd" resultType="pd" >
		select
		b.ID,
		f.CORPINFO_ID,
		f.CORP_NAME,
		b.OPERATTIME,
		b.OPERATOR,
		b.ISTRIAL,
		b.TRIALTIME,
		DATEDIFF(
		MAX( b.TRIALTIME ),
		CURDATE()) TRIALDAY
		FROM
		bus_corp_info f
		left join bus_corp_trial b on b.CORPINFO_ID = f.CORPINFO_ID
		where b.CORPINFO_ID = #{CORPINFO_ID} and b.ISDELETE = '0'
	</select>

	<!-- 企业基本信息列表 -->
	<select id="corplistPage" parameterType="page" resultType="pd">
		select
			d17.DICTIONARIES_ID DICTIONARIES_ID17,
			d17.NAME DNAME17,
			d18.DICTIONARIES_ID DICTIONARIES_ID18,
			d18.NAME DNAME18,
			d19.DICTIONARIES_ID DICTIONARIES_ID19,
			d19.NAME DNAME19,
			cir.IS_MAJOR_HAZARD,
			cir.IS_MAIN_TECHNOLO,
			cir.IS_MAJOR_HAZARD_PUSH,
			cir.IS_MAIN_TECHNOLO_PUSH,
			cir.IS_SPE_CORP,
			cir.IS_SPE_CORP_PUSH,
			f.CORP_NAME,
			f.CODE,
			f.COMPANY_AREA,
			f.ADDRESS,
			f.CREATE_DATE,
			f.SCALE,
			f.LONGITUDE,
			f.LATITUDE,
			f.POSTAL_CODE,
			f.EMPLOYEES,
			f.AREA_COVERED,
			f.TOTALASSETS,
			f.REGCAPITAL,
			f.SALESREVENUE,
			f.ANNUALPROFIT,
			f.ECO_TYPE,
			f.INDUSTRY,
			f.CORP_TYPE,
			f.CORP_TYPE_SMALL,
			f.LR_NAME,
			f.LR_POSITION,
			f.LR_PHONE,
			f.LR_MOBILE,
			f.CONTACTS,
			f.CONTACTS_PHONE,
			f.CHECKPERSON,
			f.CHECKPERSONPHONE,
			f.ISDELETE,
			f.OPERATOR,
			f.OPERATTIME,
			f.CREATTIME,
			f.CREATOR,
			f.CORPINFO_ID,
			f.FOURTYPE,
			f.ISRECEIVE,
			f.PROVINCE,
			f.CITY,
			f.COUNTY,
			f.SMS_COUNT,
			f.ISUSE,
			(f.SMS_COUNT-(select count(1) from bus_sms_log where CORPINFO_ID=f.CORPINFO_ID and state='OK')) as sycount,
			(select   max(endtime.USE_DATE_END) from bus_corp_time endtime where  endtime.CORPINFO_ID = f.CORPINFO_ID and endtime.ISDELETE = '0')  USE_DATE_END,
			<!-- max(bct.USE_DATE_END) USE_DATE_END, -->
			f.ISDELIVER,
			f.DELIVERDATE,
			ct.REMAININGTIME,
			r.ROLE_NAME VIPLEVELNAME,
			r.HAS_PLS,
			f.VIPLEVEL,
			f.STATUS
		from
		<include refid="tableName"></include> f
			left join
			<include refid="dicTableName"></include> d17
			on f.INDUSTRY = d17.DICTIONARIES_ID
			left join
			<include refid="dicTableName"></include> d18
			on f.CORP_TYPE = d18.DICTIONARIES_ID
			left join
			<include refid="dicTableName"></include> d19
			on f.CORP_TYPE_SMALL = d19.DICTIONARIES_ID
			left join bus_corp_time bct on bct.CORPINFO_ID = f.CORPINFO_ID and bct.ISDELETE = '0' and bct.isin = '1'
		     left join  bus_corp_info_related cir on  f.CORPINFO_ID = cir.CORPINFO_ID
			LEFT JOIN SYS_ROLE r ON r.ROLE_ID = f.VIPLEVEL
			LEFT JOIN(
				SELECT
				DATEDIFF(
				MAX( md.USE_DATE_END ),
				CURDATE()) REMAININGTIME,
				md.CORPINFO_ID
				FROM
				bus_corp_time md
				group by	md.CORPINFO_ID
			) ct on ct.CORPINFO_ID = f.CORPINFO_ID
		where f.ISDELETE = '0' and f.ISSMALL is NULL  and f.STATUS = '1'
		<if test="pd.KEYWORDS != null and pd.KEYWORDS != ''"><!-- 关键词检索 -->
			and
			(
			f.CORP_NAME LIKE CONCAT(CONCAT('%', #{pd.KEYWORDS}),'%')
			)
		</if>
		<if test="pd.KEYWORDS_USER != null and pd.KEYWORDS_USER != ''"><!-- 关键词检索 -->
			and
			(
			f.CORP_NAME LIKE CONCAT(CONCAT('%', #{pd.KEYWORDS_USER}),'%')
			)
		</if>
		<if test="pd.issmall == 0"><!-- 是否十小企业 -->
			and f.ISSMALL IS NULL
		</if>
		<if test="pd.issmall == 1"><!-- 是否十小企业 -->
			and f.ISSMALL = '1'
		</if>
		<if test="pd.CREATOR != null and pd.CREATOR != ''"><!-- 关键词检索 -->
			and
			(
			f.CREATOR LIKE CONCAT(CONCAT('%', #{pd.CREATOR}),'%')
			)
		</if>
		<if test="pd.CREATUSERID != null and pd.CREATUSERID != ''"><!-- 关键词检索 -->
			and f.CREATUSERID = #{pd.CREATUSERID}
		</if>

		<if test="pd.POSSESSION != null and pd.POSSESSION != '' and pd.POSSESSIONLEVEL != null and pd.POSSESSIONLEVEL != '' ">

			<choose>
				<when test='pd.POSSESSIONLEVEL=="1"'>
					and f.PROVINCE =
				</when>
				<when test='pd.POSSESSIONLEVEL=="2"'>
					and f.CITY =
				</when>
				<when test='pd.POSSESSIONLEVEL=="3"'>
					and f.COUNTY =
				</when>
				<when test='pd.POSSESSIONLEVEL=="4"'>
					and f.VILLAGE =
				</when>
				<when test='pd.POSSESSIONLEVEL=="5"'>
					and f.STREET =
				</when>

			</choose>
			#{pd.POSSESSION}
		</if>
		<if test="pd.DELIVERSTARTTIME != null and pd.DELIVERSTARTTIME != '' and pd.DELIVERENDTIME != null and pd.DELIVERENDTIME != '' ">
			and f.DELIVERDATE between #{pd.DELIVERSTARTTIME} and #{pd.DELIVERENDTIME}
		</if>
		<if test="pd.STEP != null and pd.STEP != ''">
			<!--and DATE_SUB(bct.USE_DATE_END, INTERVAL #{pd.VALIDITY} DAY) &lt;= CURDATE() -->

		</if>

		<if test="pd.PAGETYPE != null and pd.PAGETYPE != ''">
			<choose>
				<when test='pd.PAGETYPE=="1"'>
					and (
					f.ISDELIVER ='0'
					or (
					f.ISDELIVER='1' and bct.USE_DATE_START is not null and bct.USE_DATE_END is not null
					)
					)
				</when>
                <when test='pd.PAGETYPE=="2"'>
                    and f.ISDELIVER='1' and bct.CORP_TIME_ID is null
                </when>
            </choose>
                </if>
                <if test="pd.COUNTY != null and pd.COUNTY != ''">
                    and f.COUNTY = #{pd.COUNTY}
                </if>
                <if test="pd.VILLAGE != null and pd.VILLAGE != ''">
                    and f.VILLAGE = #{pd.VILLAGE}
                </if>
                <if test="pd.STREET != null and pd.STREET != ''">
                    and f.STREET = #{pd.STREET}
                </if>
        <!--		<if test="pd.CREATOR != null and pd.CREATOR != ''">&lt;!&ndash; 关键词检索 &ndash;&gt;-->
<!--			and-->
<!--				(-->
<!--					f.CREATOR = #{pd.CREATOR}-->
<!--				)-->
<!--		</if>-->
		group  by f.CORPINFO_ID
	</select>

	<select id="corpstoplistPage" parameterType="page" resultType="pd">
		select
			d17.DICTIONARIES_ID DICTIONARIES_ID17,
			d17.NAME DNAME17,
			d18.DICTIONARIES_ID DICTIONARIES_ID18,
			d18.NAME DNAME18,
			d19.DICTIONARIES_ID DICTIONARIES_ID19,
			d19.NAME DNAME19,
			f.CORP_NAME,
			f.CODE,
			f.COMPANY_AREA,
			f.ADDRESS,
			f.CREATE_DATE,
			f.SCALE,
			f.LONGITUDE,
			f.LATITUDE,
			f.POSTAL_CODE,
			f.EMPLOYEES,
			f.AREA_COVERED,
			f.TOTALASSETS,
			f.REGCAPITAL,
			f.SALESREVENUE,
			f.ANNUALPROFIT,
			f.ECO_TYPE,
			f.INDUSTRY,
			f.CORP_TYPE,
			f.CORP_TYPE_SMALL,
			f.LR_NAME,
			f.LR_POSITION,
			f.LR_PHONE,
			f.LR_MOBILE,
			f.CONTACTS,
			f.CONTACTS_PHONE,
			f.CHECKPERSON,
			f.CHECKPERSONPHONE,
			f.ISDELETE,
			f.CREATTIME,
			f.CREATOR,
			f.CORPINFO_ID,
			f.FOURTYPE,
			f.ISRECEIVE,
			f.PROVINCE,
			f.CITY,
			f.COUNTY,
			f.SMS_COUNT,
			f.ISUSE,
			(f.SMS_COUNT-(select count(1) from bus_sms_log where CORPINFO_ID=f.CORPINFO_ID and state='OK')) as sycount,
			(select   max(endtime.USE_DATE_END) from bus_corp_time endtime where  endtime.CORPINFO_ID = f.CORPINFO_ID and endtime.ISDELETE = '0')  USE_DATE_END,
			<!-- max(bct.USE_DATE_END) USE_DATE_END, -->
			f.ISDELIVER,
			f.DELIVERDATE,
			(
			SELECT
			DATEDIFF(
			MAX( md.USE_DATE_END ),
			CURDATE()) REMAININGTIME
			FROM
			bus_corp_time md
			WHERE
			<!-- DATE_FORMAT( md.USE_DATE_END, '%Y-%m-%d' ) >= CURDATE() AND -->
			md.CORPINFO_ID = f.CORPINFO_ID
			) REMAININGTIME,
			r.ROLE_NAME VIPLEVELNAME,
			f.VIPLEVEL,
			f.STATUS,
			b.STOPREASON,
			b.OPERATTIME,
			b.OPERATOR,
			b.ISSTOP
		from
			<include refid="tableName"></include> f
		left join
			<include refid="dicTableName"></include> d17
			on f.INDUSTRY = d17.DICTIONARIES_ID
		left join
			<include refid="dicTableName"></include> d18
			on f.CORP_TYPE = d18.DICTIONARIES_ID
		left join
			<include refid="dicTableName"></include> d19
			on f.CORP_TYPE_SMALL = d19.DICTIONARIES_ID
		left join bus_corp_time bct on bct.CORPINFO_ID = f.CORPINFO_ID and bct.ISDELETE = '0' and bct.isin = '1'
		LEFT JOIN SYS_ROLE r ON r.ROLE_ID = f.VIPLEVEL
		left join bus_corp_stop b on b.CORPINFO_ID = f.CORPINFO_ID and b.ISDELETE = '0' and b.ISSTOP = '0'
		LEFT JOIN BUS_CORP_TIME endtime ON endtime.CORPINFO_ID = f.CORPINFO_ID and endtime.ISDELETE = '0'
		where f.ISDELETE = '0' and f.STATUS = '3'

		<if test="pd.VIPLEVEL != null and pd.VIPLEVEL != ''"> <!-- 会员级别检索 -->
			and
			(
			r.ROLE_ID = #{pd.VIPLEVEL}
			)
		</if>

		<if test="pd.KEYWORDS != null and pd.KEYWORDS != ''"><!-- 关键词检索 -->
			and
			(
			f.CORP_NAME LIKE CONCAT(CONCAT('%', #{pd.KEYWORDS}),'%')
			)
		</if>
		<if test="pd.PROJECTLEADER != null and pd.PROJECTLEADER != ''"> <!-- 项目负责人检索 -->
			and
			(
			su1.NAME LIKE CONCAT(CONCAT('%', #{pd.PROJECTLEADER}),'%')
			)
		</if>
		<if test="pd.KEYWORDS_USER != null and pd.KEYWORDS_USER != ''"><!-- 关键词检索 -->
			and
			(
			f.CORP_NAME LIKE CONCAT(CONCAT('%', #{pd.KEYWORDS_USER}),'%')
			)
		</if>

		<if test="pd.issmall == 0"><!-- 是否十小企业 -->
			and f.ISSMALL IS NULL
		</if>
		<if test="pd.issmall == 1"><!-- 是否十小企业 -->
			and f.ISSMALL = '1'
		</if>
		<if test="pd.CREATOR != null and pd.CREATOR != ''"><!-- 关键词检索 -->
			and
			(
			f.CREATOR LIKE CONCAT(CONCAT('%', #{pd.CREATOR}),'%')
			)
		</if>
		<if test="pd.CREATUSERID != null and pd.CREATUSERID != ''"><!-- 关键词检索 -->
			and f.CREATUSERID = #{pd.CREATUSERID}
		</if>

		<if test="pd.POSSESSION != null and pd.POSSESSION != '' and pd.POSSESSIONLEVEL != null and pd.POSSESSIONLEVEL != '' ">

			<choose>
				<when test='pd.POSSESSIONLEVEL=="1"'>
					and f.PROVINCE =
				</when>
				<when test='pd.POSSESSIONLEVEL=="2"'>
					and f.CITY =
				</when>
				<when test='pd.POSSESSIONLEVEL=="3"'>
					and f.COUNTY =
				</when>
				<when test='pd.POSSESSIONLEVEL=="4"'>
					and f.VILLAGE =
				</when>
				<when test='pd.POSSESSIONLEVEL=="5"'>
					and f.STREET =
				</when>

			</choose>
			#{pd.POSSESSION}
		</if>
		<if test="pd.STARTTIME != null and pd.STARTTIME != '' and pd.ENDTIME != null and pd.ENDTIME != '' ">
			and b.OPERATTIME between #{pd.STARTTIME} and #{pd.ENDTIME}
		</if>
		<if test="pd.VALIDITY != null and pd.VALIDITY != ''">
			and DATE_SUB(bct.USE_DATE_END, INTERVAL #{pd.VALIDITY} DAY) &lt;= CURDATE()

		</if>
		<if test="pd.PAGETYPE != null and pd.PAGETYPE != ''">
			<choose>
				<when test='pd.PAGETYPE=="1"'>
					and (
					f.ISDELIVER ='0'
					or (
					f.ISDELIVER='1' and bct.USE_DATE_START is not null and bct.USE_DATE_END is not null
					)
					)
				</when>
				<when test='pd.PAGETYPE=="2"'>
					and f.ISDELIVER='1' and bct.CORP_TIME_ID is  null
				</when>
			</choose>
		</if>
		<if test="pd.COUNTY != null and pd.COUNTY != ''">
			and f.COUNTY = #{pd.COUNTY}
		</if>
		<if test="pd.VILLAGE != null and pd.VILLAGE != ''">
			and f.VILLAGE = #{pd.VILLAGE}
		</if>
		<if test="pd.STREET != null and pd.STREET != ''">
			and f.STREET = #{pd.STREET}
		</if>
		<!--		<if test="pd.CREATOR != null and pd.CREATOR != ''">&lt;!&ndash; 关键词检索 &ndash;&gt;-->
		<!--			and-->
		<!--				(-->
		<!--					f.CREATOR = #{pd.CREATOR}-->
		<!--				)-->
		<!--		</if>-->
		group by f.CORPINFO_ID order by b.OPERATTIME desc
	</select>



	<!-- 企业安全评估汇总列表 -->
	<select id="smallAuditlistPage" parameterType="page" resultType="pd">
		select
		f.CORPINFO_ID,
		f.CORP_NAME,
		f.LONGITUDE,
		f.LATITUDE,
		f.CODE,
		f.CREATE_DATE,
		f.ADDRESS,
		f.CONTACTS,
		f.CONTACTS_PHONE,
		f.AUDITSTATUS,
		f.LR_MOBILE,
		type.NAME as TYPE_NAME,
		province.NAME as PROVINCE_NAME,
		city.NAME as CITY_NAME,
		county.NAME as COUNTY_NAME,
		village.NAME as VILLAGE_NAME,
		street.NAME as STREET_NAME,
		u.NAME AS GRID_MEMBER,
		(select count(1) from bus_small_safetytrainrecordhz s where s.ISDETELE='0' AND s.ISEFFECTIVES='0' AND s.CORPINFO_ID=f.CORPINFO_ID) as AQPGCOUNT,
		(select count(1) from bus_imgfiles img where img.TYPE=102 AND img.FOREIGN_KEY=f.CORPINFO_ID) as img1,
		(select count(1) from bus_imgfiles img where img.TYPE=103 AND img.FOREIGN_KEY=f.CORPINFO_ID) as img2
		from
		<include refid="tableName"></include> f
		left join BUS_SMALL_DETAILEDINFO d on d.CORPINFO_ID = f.CORPINFO_ID
		left join SYS_DICTIONARIES type on type.DICTIONARIES_ID = f.SMALL_TYPE
		left join SYS_DICTIONARIES province on province.BIANMA = f.PROVINCE
		left join SYS_DICTIONARIES city on city.BIANMA = f.CITY
		left join SYS_DICTIONARIES county on county.BIANMA = f.COUNTY
		left join SYS_DICTIONARIES village on village.BIANMA = f.VILLAGE
		left join SYS_DICTIONARIES street on street.BIANMA = f.STREET
		left join sys_user u on u.USER_ID=f.GRID_MEMBER
		where f.ISDELETE='0' and f.ISSMALL = '1'
		<if test="pd.AUDITSTATUS != null and pd.AUDITSTATUS != ''">
			and f.AUDITSTATUS = #{pd.AUDITSTATUS}
		</if>
		<if test="pd.STATUS != null and pd.STATUS == 1">
			and f.AUDITSTATUS in (0,-1)
		</if>
		<if test="pd.STATUS != null and pd.STATUS == 2">
			and f.AUDITSTATUS in (2)
		</if>
		<if test="pd.STATUS != null and pd.STATUS == 3">
			and f.AUDITSTATUS in (1,-1)
		</if>
		<if test="pd.STATUS != null and pd.STATUS == 4">
			and f.AUDITSTATUS in (3,-3)
		</if>
		<if test="pd.KEYWORDS != null and pd.KEYWORDS != ''"><!-- 关键词检索 -->
			and f.CORP_NAME LIKE CONCAT(CONCAT('%', #{pd.KEYWORDS}),'%')
		</if>
		<if test="pd.PROVINCE != null and pd.PROVINCE != ''">
			and f.PROVINCE = #{pd.PROVINCE}
		</if>
		<if test="pd.CITY != null and pd.CITY != ''">
			and f.CITY = #{pd.CITY}
		</if>
		<if test="pd.COUNTY != null and pd.COUNTY != ''">
			and f.COUNTY = #{pd.COUNTY}
		</if>
		<if test="pd.VILLAGE != null and pd.VILLAGE != ''">
			and f.VILLAGE = #{pd.VILLAGE}
		</if>
		<if test="pd.STREET != null and pd.STREET != ''">
			and f.STREET = #{pd.STREET}
		</if>
		<if test="pd.SMALL_TYPE != null and pd.SMALL_TYPE != ''">
			and f.SMALL_TYPE = #{pd.SMALL_TYPE}
		</if>
		<if test="pd.SMALL_TYPE2 != null and pd.SMALL_TYPE2 != ''">
			and f.SMALL_TYPE2 = #{pd.SMALL_TYPE2}
		</if>
		<if test="pd.IS_FOUR != null and pd.IS_FOUR != ''">
			and (select count(1) from OA_MFOLDER m where m.PARENT_ID in (select MFOLDER_ID from OA_MFOLDER p where p.NAME in ('风险分布图','风险告知卡') and p.CORPINFO_ID = f.CORPINFO_ID)) >0
		</if>

		order by f.AUDITSTATUS desc
	</select>
		<!-- 资源库企业列表 -->
	<select id="reslistPage" parameterType="page" resultType="pd">
		select
			f.CORPINFO_ID,
			f.CORP_NAME,
			f.CREATTIME,
			f.COMPANY_AREA,
			f.ADDRESS
		from
		<include refid="tableName"></include> f
		where f.ISDELETE = '0'
		and f.CORPINFO_ID NOT IN ( SELECT CORPINFO_ID FROM bus_resourcecorp where ISDELETE = '0')
		<if test="pd.KEYWORDS != null and pd.KEYWORDS != ''"><!-- 关键词检索 -->
			and
				(
					f.CORP_NAME LIKE CONCAT(CONCAT('%', #{pd.KEYWORDS}),'%')
				)
		</if>
		order by f.CREATTIME desc
	</select>
	<!-- 列表(全部) -->
	<select id="listAll" parameterType="pd" resultType="pd">
		select
		<include refid="Field"></include>
		from
		<include refid="tableName"></include> f where f.ISDELETE = '0'

		<if test="CREATOR != null and CREATOR != ''"><!-- 关键词检索 -->
			and
				(
					f.CREATOR = #{CREATOR}
				)
		</if>

	</select>

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

	<select id="listByCorpType" parameterType="page" resultType="pd">
		select  f.CORPINFO_ID ,f.CORP_NAME
		from  <include refid="tableName"></include> f
		where f.ISDELETE = '0'
		<if test="INDUSTRY != null and INDUSTRY != ''">
			and f.INDUSTRY =#{INDUSTRY}
		</if>
		<if test="CORP_TYPE != null and CORP_TYPE != ''">
			and f.CORP_TYPE =#{CORP_TYPE}
		</if>
		<if test="CORP_TYPE_SMALL != null and CORP_TYPE_SMALL != ''">
			and f.CORP_TYPE_SMALL =#{CORP_TYPE_SMALL}
		</if>
	</select>

	<select id="findCount" resultType ="Integer" parameterType="String">
			select  count(*)
		from  <include refid="tableName"></include> f
	</select>

	<select id="getAllCorpId" parameterType="pd" resultType="pd">
		select
			<include refid="Field"></include>
		from <include refid="tableName"></include> f
		where f.ISDELETE = '0'
		order by CORP_NAME
	</select>

	<select id="findAllUsedCorp" resultType ="pd" parameterType="pd">
		select
			f.CORP_NAME,
			f.CORPINFO_ID,
		    ct.REMAININGTIME
			from
			bus_corp_info f
				left join	(
				SELECT
					DATEDIFF(
							MAX( md.USE_DATE_END ),
							CURDATE()) REMAININGTIME,
					md.CORPINFO_ID
				FROM
					bus_corp_time md
				group by	md.CORPINFO_ID
			) ct on ct.CORPINFO_ID = f.CORPINFO_ID
		where ct.REMAININGTIME is not null
	</select>

	<select id="usedCorplistPage" parameterType="page" resultType="pd">
		select
		f.CORP_NAME,
		f.CODE,
		f.COMPANY_AREA,
		f.ADDRESS,
		f.CREATE_DATE,
		f.SCALE,
		f.LONGITUDE,
		f.LATITUDE,
		f.POSTAL_CODE,
		f.EMPLOYEES,
		f.AREA_COVERED,
		f.TOTALASSETS,
		f.REGCAPITAL,
		f.SALESREVENUE,
		f.ANNUALPROFIT,
		f.ECO_TYPE,
		f.INDUSTRY,
		f.CORP_TYPE,
		f.CORP_TYPE_SMALL,
		f.LR_NAME,
		f.LR_POSITION,
		f.LR_PHONE,
		f.LR_MOBILE,
		f.CONTACTS,
		f.CONTACTS_PHONE,
		f.CHECKPERSON,
		f.CHECKPERSONPHONE,
		f.ISDELETE,
		f.OPERATOR,
		f.OPERATTIME,
		f.CREATTIME,
		f.CREATOR,
		f.CORPINFO_ID,
		f.FOURTYPE,
		f.ISRECEIVE,
		f.PROVINCE,
		f.CITY,
		f.COUNTY,
		f.SMS_COUNT,
		f.ISUSE,
		(f.SMS_COUNT-(select count(1) from bus_sms_log where CORPINFO_ID=f.CORPINFO_ID and state='OK')) as sycount,
		(select   max(endtime.USE_DATE_END) from bus_corp_time endtime where  endtime.CORPINFO_ID = f.CORPINFO_ID and endtime.ISDELETE = '0')  USE_DATE_END,
		<!-- max(bct.USE_DATE_END) USE_DATE_END, -->
		f.ISDELIVER,
		f.DELIVERDATE,
		ct.REMAININGTIME,
		f.VIPLEVEL,
		f.STATUS
		from
		<include refid="tableName"></include> f
		LEFT JOIN(
			SELECT
				DATEDIFF(
				MAX( md.USE_DATE_END ),
				CURDATE()) REMAININGTIME,
				md.CORPINFO_ID
				FROM
				bus_corp_time md
				group by md.CORPINFO_ID
		) ct on ct.CORPINFO_ID = f.CORPINFO_ID
		where f.ISDELETE = '0' and f.ISSMALL is NULL  and f.STATUS = '1' and ct.REMAININGTIME is not null
		<if test="pd.KEYWORDS != null and pd.KEYWORDS != ''"><!-- 关键词检索 -->
			and
			(
			f.CORP_NAME LIKE CONCAT(CONCAT('%', #{pd.KEYWORDS}),'%')
			)
		</if>
		<if test="pd.CREATOR != null and pd.CREATOR != ''"><!-- 关键词检索 -->
			and
			(
			f.CREATOR LIKE CONCAT(CONCAT('%', #{pd.CREATOR}),'%')
			)
		</if>
		<if test="pd.CREATUSERID != null and pd.CREATUSERID != ''"><!-- 关键词检索 -->
			and f.CREATUSERID = #{pd.CREATUSERID}
		</if>

		<if test="pd.POSSESSION != null and pd.POSSESSION != '' and pd.POSSESSIONLEVEL != null and pd.POSSESSIONLEVEL != '' ">
			<choose>
				<when test='pd.POSSESSIONLEVEL=="1"'>
					and f.PROVINCE =
				</when>
				<when test='pd.POSSESSIONLEVEL=="2"'>
					and f.CITY =
				</when>
				<when test='pd.POSSESSIONLEVEL=="3"'>
					and f.COUNTY =
				</when>
				<when test='pd.POSSESSIONLEVEL=="4"'>
					and f.VILLAGE =
				</when>
				<when test='pd.POSSESSIONLEVEL=="5"'>
					and f.STREET =
				</when>
			</choose>
			#{pd.POSSESSION}
		</if>
		<if test="pd.VALIDITY != null and pd.VALIDITY != ''">
			<!-- and DATE_SUB(bct.USE_DATE_END, INTERVAL #{pd.VALIDITY} DAY) &lt;= CURDATE()-->
			and ct.REMAININGTIME &lt;= CURDATE()
			and  ct.REMAININGTIME &lt;= #{pd.VALIDITY} and ct.REMAININGTIME &gt;= '0'
		</if>
		order  by  ct.REMAININGTIME desc
	</select>

	<!--市场管理-人员指定-项目负责人下拉列表-->
	<select id="getProjectLeadersList" parameterType="pd" resultType="pd">
		<choose>
			<when test=" WHETHER_PRE_SALE_SERVICE != '' and WHETHER_PRE_SALE_SERVICE != null ">
				<if test='WHETHER_PRE_SALE_SERVICE == "0"'><!--  从代理机构中选择项目负责人  -->
					select
					su.USER_ID,
					su.USERNAME,
					su.NAME
					from SYS_USER su, SYS_ROLE sr
					where su.ISDELETE = '0' and su.ROLE_ID = sr.ROLE_ID
					and (sr.RNUMBER = 'R20230626510739' or sr.RNUMBER = 'R20230626511126' or sr.RNUMBER = 'R20230626578562' or sr.RNUMBER = 'R20230626219293')
					and  su.AGENCY_MANAGEMENT_ID = #{AGENCY_MANAGEMENT_ID}
				</if>
				<if test='WHETHER_PRE_SALE_SERVICE == "1"'><!--  从平台选择项目负责人  -->
					select
					su.USER_ID,
					su.USERNAME,
					su.NAME
					from SYS_USER su ,SYS_ROLE sr
					where (su.CORPINFO_ID is null or su.CORPINFO_ID = '')
					and (su.AGENCY_MANAGEMENT_ID is null or su.AGENCY_MANAGEMENT_ID = '')
					and su.ROLE_ID = sr.ROLE_ID
					and (sr.RNUMBER = 'R20230626510739' or sr.RNUMBER = 'R20230626511126' or sr.RNUMBER = 'R20230626578562' or sr.RNUMBER = 'R20230626219293')
					and su.ISDELETE = '0'
				</if>
			</when>
			<otherwise>
				select
				su.USER_ID,
				su.USERNAME,
				su.NAME
				from SYS_USER su ,SYS_ROLE sr
				where (su.CORPINFO_ID is null or su.CORPINFO_ID = '')
				and (su.AGENCY_MANAGEMENT_ID is null or su.AGENCY_MANAGEMENT_ID = '')
				and su.ROLE_ID = sr.ROLE_ID
				and (sr.RNUMBER = 'R20230626510739' or sr.RNUMBER = 'R20230626511126' or sr.RNUMBER = 'R20230626578562' or sr.RNUMBER = 'R20230626219293')

				and su.ISDELETE = '0'
			</otherwise>
		</choose>
	</select>
	<!--根据企业ID获取剩余使用时长  -->
	<select id="getMonEditInfo" resultType="pd" parameterType="pd">
		select
		    (select   max(endtime.USE_DATE_END)
		    	from bus_corp_time endtime
		    	where  endtime.CORPINFO_ID = f.CORPINFO_ID
		    	and endtime.ISDELETE = '0') as useRemainingDuration
		from
			<include refid="tableName"></include> f
		where
			f.CORPINFO_ID = #{CORPINFO_ID} and f.ISDELETE = '0'
	</select>
	<!--市场管理-获取会员等级列表-->
	<select id="getVipLevelList" resultType="pd">
		select
			r.ROLE_ID ID,
		    r.ROLE_NAME NAME,
		    r.SORT SORT
		from SYS_ROLE r where r.PARENT_ID = '0' and r.ISMAIN = '0'
	</select>

	<!--根据id查询企业可新建用户数-->
	<select id="findUserNumById" resultType="pd" parameterType="pd">
		select
		    f.CORPINFO_ID CORPINFO_ID,
			f.USERS_NUM USERS_NUM,
		    f.VIPLEVEL VIPLEVEL
		from <include refid="tableName"></include> f
		where f.CORPINFO_ID = #{CORPINFO_ID} and f.ISDELETE = '0'
	</select>

	<!--市场管理-修改项目负责人-->
	<update id="editProjectLeader" parameterType="pd">
		update
			bus_maintenanceuser_info bmi
		set
			bmi.PROJECT_LEADER = #{PROJECTLEADER_ID}
		where
			bmi.CORPINFO_ID = #{CORPINFO_ID} and bmi.ISDELETE = '0'
	</update>
	<!--市场管理-会员等级调整-->
	<update id="editVipLevel" parameterType="pd">
		update
			<include refid="tableName"></include> f,
		    SYS_USER su
		set
			f.VIPLEVEL = #{VIP_CHANGE_ID},
		    su.ROLE_ID = ( select pr.NEW_LEVEL from
				(select r1.ROLE_ID OLD_LEVEL,r2.ROLE_ID NEW_LEVEL from SYS_ROLE r1,SYS_ROLE r2
					where r1.PARENT_ID = #{VIP_OLD_ID}
						and r2.PARENT_ID = #{VIP_CHANGE_ID}
						and r1.ISMAIN = r2.ISMAIN ) pr
				where pr.OLD_LEVEL = su.ROLE_ID )
		where
			f.CORPINFO_ID = #{CORPINFO_ID}
			and f.ISDELETE = '0'
		    and su.CORPINFO_ID = #{CORPINFO_ID}
			and su.ISDELETE = '0'
	</update>

	<!--更改交付日期-->
	<update id="updateDeliveryDate" parameterType="pd">
		update
			<include refid="tableName"></include> f
		set f.DELIVERDATE = #{DELIVERDATE},
		    f.ISDELIVER = '1'
		where f.CORPINFO_ID = #{CORPINFO_ID}
	</update>

	<!--会员调整(机构)-->
    <update id="editVipGearLevel" parameterType="pd">
		update
			<include refid="tableName"></include> f,
		    SYS_USER su
		set
			f.VIPLEVEL = #{VIP_CHANGE_ID},
		  	f.USERS_NUM = #{GEAR_CHANGE_NUMBER},
		    su.ROLE_ID = ( select pr.NEW_LEVEL from
				(select r1.ROLE_ID OLD_LEVEL,r2.ROLE_ID NEW_LEVEL from SYS_ROLE r1,SYS_ROLE r2
					where r1.PARENT_ID = #{VIP_OLD_ID}
						and r2.PARENT_ID = #{VIP_CHANGE_ID}
						and r1.ISMAIN = r2.ISMAIN ) pr
				where pr.OLD_LEVEL = su.ROLE_ID )
		where f.CORPINFO_ID = #{CORPINFO_ID} and
		      f.ISDELETE = '0' and
		      su.CORPINFO_ID = #{CORPINFO_ID} and
		      su.ISDELETE = '0'
	</update>

	<!--停用-修改停用企业信息-->
	<update id="stopCorp" parameterType="java.util.List">
		update <include refid="tableName"></include> f
		set f.STATUS = '3'
		where f.CORPINFO_ID in
			<foreach collection="list" separator="," item="item" index="index" close=")" open="(">
				#{item}
			</foreach>
	</update>

	<!--启用-修改停用企业信息-->
	<update id="enableCorp" parameterType="java.util.List">
		update <include refid="tableName"></include> f
		set f.STATUS = '1'
		where f.CORPINFO_ID in
		<foreach collection="list" separator="," item="item" index="index" close=")" open="(">
			#{item}
		</foreach>
	</update>

	<update id="stopCorpInfo">
		update bus_corp_stop bcs
		set bcs.ISDELETE = '1'
		where bcs.CORPINFO_ID in
		<foreach collection="list" separator="," item="item" index="index" close=")" open="(">
			#{item}
		</foreach>
	</update>

	<update id="enableCorpInfo">
		update bus_corp_stop bcs
		set bcs.ISDELETE = '1'
		where bcs.CORPINFO_ID in
		<foreach collection="list" separator="," item="item" index="index" close=")" open="(">
			#{item}
		</foreach>
	</update>

	<!--移除停用记录-->
	<update id="removeStopRecord">
		update bus_corp_stop_schedule bcss
		set bcss.ISDELETE = '1'
		where bcss.ID = #{SCHEDULED_ID} and bcss.ISDELETE = '0'
	</update>

	<select id="getAllList" resultType="com.zcloud.entity.PageData">
		select a.*
		from bus_corp_info a
		where a.ISDELETE = '0'
		<if test="CORP_NAME != '' and CORP_NAME != null">
			and a.CORP_NAME LIKE CONCAT(CONCAT('%', #{CORP_NAME}),'%')
		</if>
		limit 0,10
	</select>

	<select id="getDeacivateRecordList" resultType="pd" parameterType="pd">
		select bcss.STOP_REASON STOP_REASON,
		       bcss.ID SCHEDULED_ID,
		       bcss.CORPINFO_ID CORPINFO_ID,
		       bcss.STOP_TIME_START STOP_TIME_START,
		       bcss.STOP_TIME_END STOP_TIME_END,
		       (case when bcss.STOP_TIME_START <![CDATA[ < ]]> CURDATE() and bcss.STOP_TIME_END <![CDATA[ < ]]> CURDATE() then 'stopped'
		           when bcss.STOP_TIME_START <![CDATA[ > ]]> CURDATE() and bcss.STOP_TIME_END <![CDATA[ > ]]> CURDATE() then 'unstop'
		           else 'none'
		           end
			   ) as ISSTOPPED
		from bus_corp_stop_schedule bcss
		where bcss.ISDELETE = '0' and bcss.CORPINFO_ID = #{CORPINFO_ID}
	</select>

	<!--根据企业id获取开始时间结束时间在此企业停用期间内的-->
	<select id="getEffectDeactivateListByCorpId" resultType="pd" parameterType="pd">
		select ID,
			   CORPINFO_ID,
			   CREATOR,
			   CREATTIME,
			   OPERATOR,
			   OPERATTIME,
			   ISDELETE,
			   STOP_REASON,
			   STOP_TIME_START,
			   STOP_TIME_END
		from bus_corp_stop_schedule bcss
		where bcss.CORPINFO_ID = #{CORPINFO_ID}
		  and bcss.ISDELETE = '0'
		  and ((#{STOP_TIME_START} between bcss.STOP_TIME_START and bcss.STOP_TIME_END)
		           or (#{STOP_TIME_END} between bcss.STOP_TIME_START and bcss.STOP_TIME_END)
		           or (bcss.STOP_TIME_START between #{STOP_TIME_START} and #{STOP_TIME_END})
		           or (bcss.STOP_TIME_END) between #{STOP_TIME_START} and #{STOP_TIME_END})
	</select>
</mapper>
