<?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.ozs.kpim.mapper.kpiconfig.KpiConfigMapper">

	<sql id="base_column_list">
		id,name,type,rule_type_id,rule,kpi_code,unit,description,enable_status,edit_status,
		update_time,update_user_id,relationship
	</sql>

	<!--插入一条(所有字段) -->
	<insert id="insert" parameterType="com.ozs.kpim.dto.kpiconfig.KpiConfigPO">
		INSERT INTO kpim_info_config(
		id,name,type,rule_type_id,rule,kpi_code,unit,description,enable_status,
		edit_status,update_time,update_user_id,relationship
		)
		VALUE(#{id},#{name},#{type},#{ruleTypeId},#{rule},#{kpiCode},#{unit},#{description},#{enableStatus},
		#{editStatus},NOW(),#{updateUserId},#{relationship});
	</insert>

	<!--更新（所有字段,id,kpi_code除外） -->
	<update id="update" parameterType="com.ozs.kpim.dto.kpiconfig.KpiConfigPO">
		UPDATE kpim_info_config
		SET
		name = #{name},
		type = #{type},
		rule_type_id = #{ruleTypeId},
		rule = #{rule},
		unit = #{unit},
		description = #{description},
		enable_status = #{enableStatus},
		edit_status = #{editStatus},
		update_time = NOW(),
		update_user_id = #{updateUserId},
		relationship = #{relationship}
		WHERE id = #{id}
	</update>

	<!--启用/停用 -->
	<update id="updateEnableStatus">
		UPDATE kpim_info_config
		SET enable_status = #{enableStatus}
		WHERE id = #{id}
	</update>

	<!--删除主题 -->
	<delete id="delete">
		DELETE FROM kpim_info_config WHERE id = #{id}
	</delete>

	<!--查询最大KPI CODE -->
	<select id="getMaxKpiCode" resultType="java.lang.String">
		SELECT
		UPPER(CONCAT('K',IFNULL(MAX(CAST(SUBSTRING(kpi_code,2) AS
		SIGNED)),0))) AS max_code FROM kpim_info_config for update
	</select>

	<!--根据ID查询详情 -->
	<select id="getById" resultType="com.ozs.kpim.dto.kpiconfig.KpiConfigPO">
		SELECT
		A.id,A.name,A.type,A.rule_type_id,A.rule,A.kpi_code,
		A.unit,A.description,A.enable_status,A.edit_status,
		A.update_time,A.update_user_id,A.relationship,
		SU.nickname as updateUserName,
		B.type_desc AS ruleTypeName,
		B.type as ruleType,
		B.format as format,
		C.name AS typeName,
		krkf.function_id,
		kif.function_value

		FROM kpim_info_config A
		LEFT JOIN kpim_dim_rule_type B
		ON A.rule_type_id
		= B.id
		LEFT JOIN kpim_dim_type C
		ON A.type = C.id
		LEFT JOIN dc_info_user
		SU
		ON A.update_user_id = SU.id
		LEFT JOIN kpim_rel_kpi_function krkf ON A.id=krkf.kpi_id
		LEFT JOIN kpim_info_function kif ON krkf.function_id=kif.id
		
		WHERE A.id =
		#{id}
	</select>

	<!--根据kpiCode查询数量 -->
	<select id="getCountByKpiCode" resultType="java.lang.Integer">
		SELECT COUNT(1) FROM kpim_info_config WHERE kpi_code = #{kpiCode}
	</select>

	<!--KPI列表查询 -->
	<select id="listKpiConfigs" resultType="com.ozs.kpim.dto.kpiconfig.KpiConfigForListDTO">

		SELECT
		DISTINCT(A.id),A.name,A.type,A.rule_type_id,A.rule,A.kpi_code,
		A.unit,A.description,A.enable_status,A.edit_status,
		A.update_time,A.update_user_id,
		SU.nickname as updateUserName,
		T.topicName,
		C.name as typeName,

		B.type_desc AS ruleTypeName,
		B.type as ruleType,
		B.format as format
		FROM kpim_info_config A
		LEFT JOIN kpim_dim_rule_type B
		ON
		A.rule_type_id = B.id
		LEFT JOIN kpim_dim_type C
		ON A.type = C.id
		LEFT
		JOIN dc_info_user SU
		ON A.update_user_id = SU.id

		<if
			test="  (topicIds == null or topicIds.length == 0) and (topicCodes == null or topicCodes.length ==0)">
			LEFT
		</if>
		<if
			test="  (topicIds != null and topicIds.length > 0) or (topicCodes != null and topicCodes.length > 0)">
			INNER
		</if>
		JOIN
		(
		SELECT
		GROUP_CONCAT(A.name) AS topicName ,B.kpi_id
		FROM kpim_info_topic A
		LEFT JOIN kpim_rel_kpi_topic B
		ON A.id = B.topic_id
		WHERE 1=1
		<if test=" topicIds != null and topicIds.length > 0 ">
			and A.id IN
			<foreach collection="topicIds" open="(" close=")" separator=","
				item="item">
				#{item}
			</foreach>
		</if>
		<if test=" topicCodes != null and topicCodes.length > 0 ">
			and A.topic_code IN
			<foreach collection="topicCodes" open="(" close=")"
				separator="," item="item">
				#{item}
			</foreach>
		</if>
		GROUP BY B.kpi_id
		) T
		ON A.id = T.kpi_id

		<where>
			<if test=" name != null and name !='' ">
				AND A.name LIKE CONCAT('%',#{name},'%')
			</if>
			<if test=" type != null ">
				AND A.type = #{type}
			</if>
			<if test=" enableStatus != null ">
				AND A.enable_status = #{enableStatus}
			</if>
			<if test=" editStatus != null ">
				AND A.edit_status = #{editStatus}
			</if>
		</where>

	</select>

	<!--查询主题下的KPI配置信息 -->
	<select id="listKpiConfigsByTopic" resultType="com.ozs.kpim.dto.kpiconfig.KpiConfigForListDTO">
		SELECT
		C.id,C.kpi_code,C.name,C.type,C.rule_type_id,
		D.name as typeName,E.type_desc as ruleTypeName,E.type as ruleType,E.format
		as format
		FROM kpim_info_topic A
		LEFT JOIN kpim_rel_kpi_topic B
		ON A.id =
		B.topic_id
		LEFT JOIN kpim_info_config C
		ON B.kpi_id = C.id
		LEFT JOIN
		kpim_dim_type D
		ON C.type = D.id
		LEFT JOIN kpim_dim_rule_type E
		ON
		C.rule_type_id = E.id
		WHERE 1 = 1
		<if test="enableStatus != null">
			and C.enable_status = #{enableStatus}
		</if>
		<if test="topicId != null and topicId !='' ">
			and A.id = #{topicId}
		</if>
		<if test="topicCode != null and topicCode !='' ">
			and A.topic_code = #{topicCode}
		</if>

		GROUP BY B.kpi_id
	</select>

</mapper>