<?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.video.CorpInfo4Mapper">

	<!--表名 -->
	<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
	</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
	</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}


	</sql>




	<!-- 列表 -->
	<select id="videolistPage" parameterType="page" resultType="pd">
		select
		<include refid="Field"></include>,
		prvince.name as prvinceName,city.name as cityName ,county.name as countyName ,village.name as villageName,
		(select count(1) from BUS_VIDEOMANAGER t where t.CORPINFO_ID = f.CORPINFO_ID and t.ISDELETE = 0) as video_count
		from
		<include refid="tableName"></include>
		f
		LEFT JOIN SYS_DICTIONARIES prvince ON prvince.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
		where 1=1 and ISDELETE ='0' and f.ISSMALL is null
		<!-- and  (select count(1) from BUS_VIDEOMANAGER t where t.CORPINFO_ID = f.CORPINFO_ID) > 0-->
		<if test="pd.KEYWORDS != null and pd.KEYWORDS != ''"><!-- 企业名称 -->
			and  f.CORP_NAME LIKE CONCAT(CONCAT('%', #{pd.KEYWORDS}),'%')
		</if>
		<if test="pd.KEYQYLX != null and pd.KEYQYLX != ''"><!-- 请选企业类型 -->
			and ( f.CORP_OF_TYPE LIKE CONCAT(CONCAT('%', #{pd.KEYQYLX}),'%') or f.CORP_OF_TYPE2 LIKE CONCAT(CONCAT('%', #{pd.KEYQYLX}),'%'))
		</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.KEYVILLAGE != null and pd.KEYVILLAGE != ''"><!-- 所属乡镇 -->
			and f.VILLAGE = #{pd.KEYVILLAGE}
		</if>
		<if test="pd.DEPT_PROVINCE != null and pd.DEPT_PROVINCE != '' and pd.DEPT_PROVINCE != '000000'">
			and f.PROVINCE = #{pd.DEPT_PROVINCE}
		</if>
		<if test="pd.DEPT_CITY != null and pd.DEPT_CITY != ''">
			and f.CITY = #{pd.DEPT_CITY}
		</if>
		<if test="pd.DEPT_COUNTY != null and pd.DEPT_COUNTY != ''">
			and f.COUNTY = #{pd.DEPT_COUNTY}
		</if>
		<if test="pd.DEPT_VILLAGE != null and pd.DEPT_VILLAGE != ''">
			and f.VILLAGE = #{pd.DEPT_VILLAGE}
		</if>
		order by f.USE_DATE_END desc
	</select>

</mapper>
