<?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.MaintenanceUserInfoMapper">

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

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

	<!-- 字段 -->
	<sql id="Field">
		f.MAINTENANCE_ID,
		f.MARKETING_PERSON,
		f.IMPLEMENTATION_PERRON,
		f.PROJECT_LEADER,
		f.MAINTENANCE_PERSON,
		f.AGENCY_MANAGEMENT_ID,
		f.ISDELETE,
		f.OPERATOR,
		f.OPERATTIME,
		f.CREATTIME,
		f.CREATOR,
		f.CORPINFO_ID,
		f.WHETHER_PRE_SALE_SERVICE,
		f.WHETHER_AFTER_SALES_SERVICE
	</sql>

	<!-- 字段用于新增 -->
	<sql id="Field2">
		MAINTENANCE_ID,
		MARKETING_PERSON,
		IMPLEMENTATION_PERRON,
		PROJECT_LEADER,
		MAINTENANCE_PERSON,
		AGENCY_MANAGEMENT_ID,
		ISDELETE,
		OPERATOR,
		OPERATTIME,
		CREATTIME,
		CREATOR,
		CORPINFO_ID,
		WHETHER_PRE_SALE_SERVICE,
		WHETHER_AFTER_SALES_SERVICE
	</sql>

	<!-- 字段值 -->
	<sql id="FieldValue">
		#{MAINTENANCE_ID},
		#{MARKETING_PERSON},
		#{IMPLEMENTATION_PERRON},
		#{PROJECT_LEADER},
		#{MAINTENANCE_PERSON},
		#{AGENCY_MANAGEMENT_ID},
		#{ISDELETE},
		#{OPERATOR},
		#{OPERATTIME},
		#{CREATTIME},
		#{CREATOR},
		#{CORPINFO_ID},
		#{WHETHER_PRE_SALE_SERVICE},
		#{WHETHER_AFTER_SALES_SERVICE}

	</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>

	<!-- 删除-->
	<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
		MARKETING_PERSON = #{MARKETING_PERSON},
<!-- 		IMPLEMENTATION_PERRON = #{IMPLEMENTATION_PERRON},
		PROJECT_LEADER = #{PROJECT_LEADER},
 		MAINTENANCE_PERSON = #{MAINTENANCE_PERSON},-->
		OPERATOR = #{OPERATOR},
		OPERATTIME = #{OPERATTIME},
		CREATTIME = #{CREATTIME},
		CREATOR = #{CREATOR},
		CORPINFO_ID = #{CORPINFO_ID}
		where
		CORPINFO_ID = #{CORPINFO_ID}
		<if test="AGENCY_MANAGEMENT_ID != null  and AGENCY_MANAGEMENT_ID != ''">
			and AGENCY_MANAGEMENT_ID = #{AGENCY_MANAGEMENT_ID}
		</if>
	</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>

	<!-- 项目负责人管理-开始部署-修改实施人员-->
    <update id="editImplementPerson" parameterType="pd">
		update
			<include refid="tableName"></include> bmi
		set
			bmi.IMPLEMENTATION_PERRON = #{IMPLEMENTPSERSON_ID}
		where
			bmi.CORPINFO_ID = #{CORPINFO_ID} and bmi.ISDELETE = '0'
	</update>

	<!--更改四类人员-->
    <update id="changePersonnel">
		update <include refid="tableName"></include> bmi
		set
		    bmi.MARKETING_PERSON = #{marketingPerson},
		    bmi.PROJECT_LEADER = #{projectPerson},
		    bmi.IMPLEMENTATION_PERRON = #{implementationPerson},
		    bmi.MAINTENANCE_PERSON = #{maintenancePerson}
		where
			bmi.CORPINFO_ID = #{CORPINFO_ID} and bmi.ISDELETE = '0'
	</update>

    <!-- 通过ID获取数据 -->
	<select id="findById" parameterType="pd" resultType="pd">
		select
		b.NAME as MARKETING_PERSON
		from
		<include refid="tableName"></include> f
		left join sys_user b on b.USER_ID = f.MARKETING_PERSON
		where
		f.CORPINFO_ID = #{CORPINFO_ID} and f.ISDELETE = '0'
		<if test="AGENCY_MANAGEMENT_ID != null  and AGENCY_MANAGEMENT_ID != ''">
			and f.AGENCY_MANAGEMENT_ID = #{AGENCY_MANAGEMENT_ID}
		</if>

	</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,
			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,
			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 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.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>
		<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'
		where f.ISDELETE = '0' and f.STATUS = '3'
		<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.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="corptriallistPage" 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.ID,
		b.TRIALTIME,
		b.OPERATTIME,
		b.OPERATOR,
		b.ISTRIAL
		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_trial b on b.CORPINFO_ID = f.CORPINFO_ID and b.ISDELETE = '0' and b.ISTRIAL = '0'
		where f.ISDELETE = '0' and f.STATUS = '2'
		<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.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'
					)
				</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="datalistPage" 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,
		<include refid="Field"></include>,
		(f.SMS_COUNT-(select count(1) from bus_sms_log where CORPINFO_ID=f.CORPINFO_ID and state='OK')) as sycount
		from
		<include refid="tableName"></include> f
			left join BUS_SMALL_DETAILEDINFO d on d.CORPINFO_ID = f.CORPINFO_ID
			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


		where f.ISDELETE = '0'
		<if test="pd.KEYWORDS != null and pd.KEYWORDS != ''"><!-- 关键词检索 -->
			and
				(
					f.CORP_NAME LIKE CONCAT(CONCAT('%', #{pd.KEYWORDS}),'%')
				)
		</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 d.SMALL_TYPE = #{pd.SMALL_TYPE}
		</if>
		<if test="pd.SMALL_TYPE2 != null and pd.SMALL_TYPE2 != ''">
			and d.SMALL_TYPE2 = #{pd.SMALL_TYPE2}
		</if>
		<if test="pd.CREATOR != null and pd.CREATOR != ''"><!-- 关键词检索 -->
			and
				(
					f.CREATOR = #{pd.CREATOR}
				)
		</if>

		order by f.USE_DATE_END
	</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>

	<update id="setVisitConfig" parameterType="pd">
		update
			<include refid="tableName"></include> f
		set
			f.VISIT_PERIOD = #{VISIT_PERIOD},
			f.VISIT_STATE = #{VISIT_STATE}
		where
			f.CORPINFO_ID = #{CORPINFO_ID}
	</update>
</mapper>
