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

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

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

	<!-- 字段 -->
	<sql id="Field">
		f.CORPINFO_ID,
		f.SCORE1,
		f.SCORE2,
		f.SCORE3,
		f.SCORE4,
		f.SCORE5,
		f.SCORE6,
		f.EXAMINE_DATE,
		f.CORP_SCORE_ID
	</sql>

	<!-- 字段用于新增 -->
	<sql id="Field2">
		CORPINFO_ID,
		SCORE1,
		SCORE2,
		SCORE3,
		SCORE4,
		SCORE5,
		SCORE6,
		EXAMINE_DATE,
		CORP_SCORE_ID
	</sql>

	<!-- 字段值 -->
	<sql id="FieldValue">
		#{CORPINFO_ID},
		#{SCORE1},
		#{SCORE2},
		#{SCORE3},
		#{SCORE4},
		#{SCORE5},
		#{SCORE6},
		#{EXAMINE_DATE},
		#{CORP_SCORE_ID}
	</sql>

	<!-- 字段值 -->
	<sql id="FieldValue2">
		#{item.CORPINFO_ID},
		#{item.SCORE1},
		#{item.SCORE2},
		#{item.SCORE3},
		#{item.SCORE4},
		#{item.SCORE5},
		#{item.SCORE6},
		#{item.EXAMINE_DATE},
		#{item.CORP_SCORE_ID}
	</sql>

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

	<!-- 新增-->
	<insert id="saveBatch" parameterType="pd">
		insert into <include refid="tableName"></include>
			(<include refid="Field2"></include>) values
		<foreach item="item" index="index" collection="list" separator="," >
			(<include refid="FieldValue2"></include>)
		</foreach>
	</insert>

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

	<!-- 修改 -->
	<update id="edit" parameterType="pd">
		update
		<include refid="tableName"></include>
		set
			CORPINFO_ID = #{CORPINFO_ID},
			SCORE1 = #{SCORE1},
			SCORE2 = #{SCORE2},
			SCORE3 = #{SCORE3},
			SCORE4 = #{SCORE4},
			SCORE5 = #{SCORE5},
			SCORE6 = #{SCORE6},
			EXAMINE_DATE = #{EXAMINE_DATE},
			CORP_SCORE_ID = CORP_SCORE_ID
		where
			CORP_SCORE_ID = #{CORP_SCORE_ID}
	</update>

	<!-- 通过ID获取数据 -->
	<select id="findById" parameterType="pd" resultType="pd">
		select
		<include refid="Field"></include>
		from
		<include refid="tableName"></include> f
		where
			f.CORPINFO_ID = #{CORPINFO_ID}
		and f.EXAMINE_DATE = #{MONTH}
	</select>

	<!-- 列表 -->
	<select id="datalistPage" parameterType="page" resultType="pd">
		select
			avg(e.SCORE1) as SCORE1,
			avg(e.SCORE2) as SCORE2,
			avg(e.SCORE3) as SCORE3,
			avg(e.SCORE4) as SCORE4,
			avg(e.SCORE5) as SCORE5,
			avg(e.SCORE6) as SCORE6,
			f.CORP_NAME AS CORP_NAME
		from
			<include refid="tableName"></include> e
		LEFT JOIN BUS_CORP_INFO f ON e.CORPINFO_ID = f.CORPINFO_ID
		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
		<if test="pd.YEAR != null and pd.YEAR != ''"><!-- 年 -->
			and  e.EXAMINE_DATE LIKE CONCAT(CONCAT( #{pd.YEAR}),'%')
		</if>
		<if test="pd.QUARTER ==1"><!-- 季度1 -->
			and ( e.EXAMINE_DATE LIKE '%-01' or e.EXAMINE_DATE LIKE '%-02' or e.EXAMINE_DATE LIKE '%-03' )
		</if>
		<if test="pd.QUARTER ==2"><!-- 季度2 -->
			and ( e.EXAMINE_DATE LIKE '%-04' or e.EXAMINE_DATE LIKE '%-05' or e.EXAMINE_DATE LIKE '%-06' )
		</if>
		<if test="pd.QUARTER ==3"><!-- 季度3 -->
			and ( e.EXAMINE_DATE LIKE '%-07' or e.EXAMINE_DATE LIKE '%-08' or e.EXAMINE_DATE LIKE '%-09' )
		</if>
		<if test="pd.QUARTER ==4"><!-- 季度4 -->
			and ( e.EXAMINE_DATE LIKE '%-10' or e.EXAMINE_DATE LIKE '%-11' or e.EXAMINE_DATE LIKE '%-12' )
		</if>
		<if test="pd.MONTH != null and pd.MONTH != ''"><!-- 企业名称 -->
			and  e.EXAMINE_DATE = #{pd.MONTH}
		</if>
		<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>
		<if test="pd.DEPT_STREET != null and pd.DEPT_STREET != ''">
			and f.STREET = #{pd.DEPT_STREET}
		</if>
		group by e.CORPINFO_ID
		ORDER BY (avg(e.SCORE1)+avg(e.SCORE2)+avg(e.SCORE3)+avg(e.SCORE4)+avg(e.SCORE5)+avg(e.SCORE6)) desc
	</select>

	<!-- 列表(全部) -->
	<select id="listAll" parameterType="pd" resultType="pd">
		select
		<include refid="Field"></include>
		from
		<include refid="tableName"></include> f
	</select>

	<!-- 批量删除 -->
	<delete id="deleteAll" parameterType="pd">
		delete from
		<include refid="tableName"></include>
		where	EXAMINE_DATE = #{MONTH}
	</delete>

</mapper>
