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

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

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

	<!-- 企业基本信息列表 -->
	<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.ISRECEIVE,
			f.PROVINCE,
			f.CITY,
			f.COUNTY,
			r.ROLE_NAME VIPLEVELNAME,
		   (select
				sum(l.FILE_SIZE) as total_usage
				from
				BUS_FILEUPLOADLOG l
				where l.CORPINFO_ID = f.CORPINFO_ID
		   ) as TOTAL_USAGE
		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 SYS_ROLE r ON r.ROLE_ID = f.VIPLEVEL
		where f.ISDELETE = '0'  and f.ISSMALL is null
		<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.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.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>
		group  by f.CORPINFO_ID
	</select>



</mapper>
