<?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.datau.rpm.mapper.ReportMapper">

	<update id="updateStatus" parameterType="com.datau.rpm.dto.ReportDTO">
		update rpm_info_report
		set status=#{status}
		where id=#{id}
	</update>
	<delete id="removeReport">
		delete from rpm_info_report
		where
		id=#{id}
	</delete>
	<select id="listReportType" parameterType="java.lang.Integer"
		resultType="com.datau.rpm.dto.RpmDimTypeAll">
		select id,name from
		rpm_dim_type_all
		where type_id =
		#{typeId}
	</select>
	<select id="listReport" resultType="com.datau.rpm.dto.ReportDTO">
		SELECT
		r.id,
		r.report_name,
		r.show_type,
		r.update_time,
		r.update_user_id,
		r. STATUS,
		r.report_level,
		r.current_step,
		r.if_finish,
		rm.menu_code,
		rm.menu_name,
		rdta1. NAME report_type_value,
		diu.nickname
		update_user_name,
		rdrs.VALUE
		status_value,
		rdta8. NAME
		report_level_value,
		rdta7. NAME show_type_value,
		rdta14.name
		if_finish_value
		FROM
		rpm_info_report r
		LEFT JOIN rpm_info_report_menu rm
		ON r.id = rm.report_id
		LEFT JOIN rpm_dim_type_all rdta1 ON
		r.report_type = rdta1.id
		AND rdta1.type_id = 1
		LEFT JOIN
		rpm_dim_type_all rdta8 ON r.report_level = rdta8.id
		AND rdta8.type_id =
		8
		LEFT JOIN dc_info_user diu ON r.update_user_id = diu.id
		LEFT JOIN
		rpm_dim_report_status rdrs ON r.STATUS = rdrs.id
		LEFT JOIN
		rpm_dim_type_all rdta7 ON r.show_type = rdta7.id
		AND rdta7.type_id = 7
		LEFT JOIN rpm_dim_type_all rdta14 ON r.if_finish = rdta14.id
		AND
		rdta14.type_id = 14
		where
		1=1
		<if test="reportName != null &amp;&amp; reportName != ''">
			and r.report_name like concat('%',#{reportName},'%')
		</if>
		<if test="reportLevel != null">
			and r.report_level =#{reportLevel}
		</if>
		<if test="ifFinish != null">
			and r.if_finish =#{ifFinish}
		</if>
	</select>
	<insert id="saveOne" parameterType="com.datau.rpm.dto.save.SaveOneDTO">
		insert into
		rpm_info_report(id,report_name,report_type,show_type,display_system,report_level,update_user_id,update_time,if_finish,current_step,status,if_drill_down,if_page,if_total,chart_type,chart_class_code,chart_refrech,data_source)
		values(#{id},#{reportName},#{reportType},#{showType},#{displaySystem},#{reportLevel},#{updateUserId},now(),#{ifFinish},#{currentStep},#{status},1,1,1,#{chartType},#{chartClassCode},#{chartRefrech},#{dataSource})
	</insert>
	<insert id="saveReportKpi" parameterType="com.datau.rpm.dto.save.SaveRportKpiDTO">
		insert into
		rpm_rel_report_kpi(report_id,kpi_id,
		rpm_rel_report_kpi.rname,sort,if_time,if_number,kpi_code,editStatus,if_drill_down,if_query_time,if_query_search,if_query_group_by,if_query_order_by,if_query_color,if_group_show,if_related)
		values
		<foreach collection="list" item="kpi" separator=",">
			(#{kpi.reportId},#{kpi.kpiId},#{kpi.rname},#{kpi.sort},#{kpi.ifTime},#{kpi.ifNumber},#{kpi.kpiCode},#{kpi.editStatus},0,0,0,0,0,0,0,0)
		</foreach>
	</insert>

	<select id="getReportByName" resultType="com.datau.rpm.dto.ReportDTO">
		select id,
		report_name,
		rir.desc,
		show_type,
		display_system,
		if_drill_down,
		update_time,
		update_user_id,
		STATUS,
		report_level,
		if_page,
		if_total,
		layout_id,
		color_group_id,
		current_step,
		if_finish from rpm_info_report rir where
		rir.report_name = #{reportName}
	</select>
	<update id="updateSaveTwo" parameterType="com.datau.rpm.dto.save.SaveTwoDTO">
		update rpm_info_report
		set
		update_user_id =
		#{updateUserId},
		update_time=
		now()
		<if test="ifFinish != null ">
			,
			if_finish=
			#{ifFinish}
		</if>
		<if test="currentStep != null ">
			,
			current_step=
			#{currentStep}
		</if>
		<if test="status != null ">
			,
			status= #{status}
		</if>
		<if test="DML != null ">
			,
			DML= #{DML}
		</if>
		<if test="databaseId != null ">
			,
			database_id= #{databaseId}
		</if>
		<if test="tableId != null ">
			,
			table_id= #{tableId}
		</if>
		where
		id
		=
		#{id}
	</update>
	<update id="updateReportKpi" parameterType="java.util.Map">
		update rpm_rel_report_kpi set
		if_query_group_by = case when 1=2 then
		null
		<foreach collection="list" separator=" " item="kpi">
			<if test="kpi.ifQueryGroupBy != null ">
				when kpi_id =
				#{kpi.kpiId} then #{kpi.ifQueryGroupBy}
			</if>
		</foreach>
		else null
		end,
		if_query_order_by = case when 1=2 then null
		<foreach collection="list" separator=" " item="kpi">
			<if test="kpi.ifQueryOrderBy != null ">
				when kpi_id =
				#{kpi.kpiId} then #{kpi.ifQueryOrderBy}
			</if>
		</foreach>
		else null
		end,
		if_query_time = case when 1=2 then null
		<foreach collection="list" separator=" " item="kpi">
			<if test="kpi.ifQueryTime != null ">
				when kpi_id =
				#{kpi.kpiId} then #{kpi.ifQueryTime}
			</if>
		</foreach>
		else null
		end,
		time_type_id = case when 1=2 then null
		<foreach collection="list" separator=" " item="kpi">
			<if test="kpi.timeTypeId != null ">
				when kpi_id =
				#{kpi.kpiId} then #{kpi.timeTypeId}
			</if>
		</foreach>
		else null
		end,
		if_query_search = case when 1=2 then null
		<foreach collection="list" separator=" " item="kpi">
			<if test="kpi.ifQuerySearch != null ">
				when kpi_id =
				#{kpi.kpiId} then #{kpi.ifQuerySearch}
			</if>
		</foreach>
		else null
		end,
		search_type_id = case when 1=2 then null
		<foreach collection="list" separator=" " item="kpi">
			<if test="kpi.searchTypeId != null ">
				when kpi_id =
				#{kpi.kpiId} then #{kpi.searchTypeId}
			</if>
		</foreach>
		else null
		end,
		if_query_color = case when 1=2 then null
		<foreach collection="list" separator=" " item="kpi">
			<if test="kpi.ifQueryColor != null ">
				when kpi_id =
				#{kpi.kpiId} then #{kpi.ifQueryColor}
			</if>
		</foreach>
		else 0
		end,
		query_color_upper = case when 1=2 then null
		<foreach collection="list" separator=" " item="kpi">
			<if test="kpi.queryColorUpper != null ">
				when kpi_id =
				#{kpi.kpiId} then #{kpi.queryColorUpper}
			</if>
		</foreach>
		else null
		end,
		query_color_lower = case when 1=2 then null
		<foreach collection="list" separator=" " item="kpi">
			<if test="kpi.queryColorLower != null ">
				when kpi_id =
				#{kpi.kpiId} then #{kpi.queryColorLower}
			</if>
		</foreach>
		else null
		end,
		query_color_id = case when 1=2 then null
		<foreach collection="list" separator=" " item="kpi">
			<if test="kpi.queryColorId != null ">
				when kpi_id =
				#{kpi.kpiId} then #{kpi.queryColorId}
			</if>
		</foreach>
		else null
		end,
		query_group_by_sort = case when 1=2 then null
		<foreach collection="list" separator=" " item="kpi">
			<if test="kpi.queryGroupBySort != null ">
				when kpi_id =
				#{kpi.kpiId} then #{kpi.queryGroupBySort}
			</if>
		</foreach>
		else null
		end
		,
		query_search_sort = case when 1=2 then null
		<foreach collection="list" separator=" " item="kpi">
			<if test="kpi.querySearchSort != null ">
				when kpi_id =
				#{kpi.kpiId} then #{kpi.querySearchSort}
			</if>
		</foreach>
		else null
		end
		,
		time_default_offset = case when 1=2 then null
		<foreach collection="list" separator=" " item="kpi">
			<if test="kpi.timeDefaultOffset != null ">
				when kpi_id =
				#{kpi.kpiId} then #{kpi.timeDefaultOffset}
			</if>
		</foreach>
		else null
		end
		,
		if_query_default_sort = case when 1=2 then null
		<foreach collection="list" separator=" " item="kpi">
			<if test="kpi.ifQueryDefaultSort != null ">
				when kpi_id =
				#{kpi.kpiId} then #{kpi.ifQueryDefaultSort}
			</if>
		</foreach>
		else null
		end
		,
		query_default_order = case when 1=2 then null
		<foreach collection="list" separator=" " item="kpi">
			<if test="kpi.queryDefaultOrder != null ">
				when kpi_id =
				#{kpi.kpiId} then #{kpi.queryDefaultOrder}
			</if>
		</foreach>
		else null
		end
		,
		if_total = case when 1=2 then null
		<foreach collection="list" separator=" " item="kpi">
			<if test="kpi.ifTotal != null ">
				when kpi_id =
				#{kpi.kpiId} then #{kpi.ifTotal}
			</if>
		</foreach>
		else null
		end
		,
		total_show_sort = case when 1=2 then null
		<foreach collection="list" separator=" " item="kpi">
			<if test="kpi.totalShowSort != null ">
				when kpi_id =
				#{kpi.kpiId} then #{kpi.totalShowSort}
			</if>
		</foreach>
		else null
		end
		,
		if_operable_group_by = case when 1=2 then null
		<foreach collection="list" separator=" " item="kpi">
			<if test="kpi.ifOperableGroupBy != null ">
				when kpi_id =
				#{kpi.kpiId} then #{kpi.ifOperableGroupBy}
			</if>
		</foreach>
		else null
		end
		where 1=1 and
		kpi_id in (
		<foreach collection="list" separator="," item="kpi">
			#{kpi.kpiId}
		</foreach>
		) and report_id = #{reportId}
	</update>
	<update id="updateSaveThree" parameterType="com.datau.rpm.dto.save.SaveThreeDTO">
		update
		rpm_info_report set
		update_user_id =
		#{updateUserId},update_time=
		now()
		<if test="ifFinish != null ">
			,
			if_finish=
			#{ifFinish}
		</if>
		<if test="currentStep != null ">
			,
			current_step=
			#{currentStep}
		</if>
		<if test="status != null ">
			,
			status= #{status}
		</if>

		,if_page = #{ifPage},if_total = #{ifTotal}
		<if test="totalShowType != null ">
			,
			total_show_type= #{totalShowType}
		</if>
		<if test="ifOutsideParam != null ">
			,
			if_outside_param= #{ifOutsideParam}
		</if>
		<if test="useOperableGroup != null ">
			,
			use_operable_group= #{useOperableGroup}
		</if>
		where
		id
		= #{id}
	</update>
	<select id="getReportById" resultType="com.datau.rpm.dto.ReportDTO">
		select id,
		report_name,
		rir.desc,
		show_type,
		display_system,
		if_drill_down,
		update_time,
		update_user_id,
		STATUS,
		report_level,
		if_page,
		if_total,
		layout_id,
		color_group_id,
		current_step,
		if_finish,
		chart_type,
		DML,
		chart_class_code,
		database_id,
		table_id,
		chart_json,
		rich_text,
		file_path,
		chart_refrech,
		data_source, if_outside_param,total_show_type
		from
		rpm_info_report rir
		where
		rir.id = #{id}
	</select>
	<update id="updateSaveFour" parameterType="com.datau.rpm.dto.save.SaveFourDTO">
		update
		rpm_info_report
		set
		update_user_id =
		#{updateUserId},update_time=
		now()
		<if test="ifFinish != null ">
			,
			if_finish=
			#{ifFinish}
		</if>
		<if test="currentStep != null ">
			,
			current_step=
			#{currentStep}
		</if>
		<if test="status != null ">
			,
			status= #{status}
		</if>
		,if_drill_down = #{ifDrillDown}
		where
		id = #{id}
	</update>
	<update id="updateReportKpiFour" parameterType="java.util.Map">
		update
		rpm_rel_report_kpi set
		if_drill_down = 1
		,
		subreport_id = case
		<foreach collection="list" separator=" " item="kpi">
			<if test="kpi.subreportId != null ">
				when kpi_id =
				#{kpi.kpiId} then #{kpi.subreportId}
			</if>
		</foreach>
		else null
		end
		,
		if_related = case when 1=2 then null
		<foreach collection="list" separator=" " item="kpi">
			<if test="kpi.ifRelated != null ">
				when kpi_id =
				#{kpi.kpiId} then #{kpi.ifRelated}
			</if>
		</foreach>
		else null
		end
		,
		related_kpi_id = case when 1=2 then null
		<foreach collection="list" separator=" " item="kpi">
			<if test="kpi.relatedKpiId != null ">
				when kpi_id =
				#{kpi.kpiId} then #{kpi.relatedKpiId}
			</if>
		</foreach>
		else null
		end
		,
		related_kpi_code = case when 1=2 then null
		<foreach collection="list" separator=" " item="kpi">
			<if test="kpi.relatedKpiCode != null ">
				when kpi_id =
				#{kpi.kpiId} then #{kpi.relatedKpiCode}
			</if>
		</foreach>
		else null
		end
		,
		related_sub_kpi_id = case when 1=2 then null
		<foreach collection="list" separator=" " item="kpi">
			<if test="kpi.relatedSubKpiId != null ">
				when kpi_id =
				#{kpi.kpiId} then #{kpi.relatedSubKpiId}
			</if>
		</foreach>
		else null
		end
		,
		related_sub_kpi_code = case when 1=2 then null
		<foreach collection="list" separator=" " item="kpi">
			<if test="kpi.relatedSubKpiCode != null ">
				when kpi_id =
				#{kpi.kpiId} then #{kpi.relatedSubKpiCode}
			</if>
		</foreach>
		else null
		end
		,
		time_related_selected = case when 1=2 then null
		<foreach collection="list" separator=" " item="kpi">
			<if test="kpi.timeRelatedSelected != null ">
				when kpi_id =
				#{kpi.kpiId} then #{kpi.timeRelatedSelected}
			</if>
		</foreach>
		else null
		end
		,
		time_range_offset = case when 1=2 then null
		<foreach collection="list" separator=" " item="kpi">
			<if test="kpi.timeRangeOffset != null ">
				when kpi_id =
				#{kpi.kpiId} then #{kpi.timeRangeOffset}
			</if>
		</foreach>
		else null
		end
		where 1=1 and
		kpi_id in (
		<foreach collection="list" separator="," item="kpi">
			#{kpi.kpiId}
		</foreach>
		) and report_id = #{reportId}
	</update>
	<update id="updateReportKpiRelated" parameterType="java.util.Map">
		update
		rpm_rel_report_kpi set
		if_related = 1
		where 1=1 and
		kpi_id in (
		<foreach collection="list" separator="," item="kpiId">
			#{kpiId}
		</foreach>
		) and report_id = #{reportId}
	</update>
	<update id="updateGroupKpiReportKpi">
		update
		rpm_rel_report_kpi set
		if_group_show = 1
		where
		1=1
		and
		kpi_id =
		#{kpiId}
		and report_id = #{reportId}
	</update>
	<update id="updateSaveFive" parameterType="com.datau.rpm.dto.save.SaveFiveDTO">
		update
		rpm_info_report
		set
		update_user_id =
		#{updateUserId},update_time=
		now()
		<if test="ifFinish != null ">
			,
			if_finish=
			#{ifFinish}
		</if>
		<if test="currentStep != null ">
			,
			current_step=
			#{currentStep}
		</if>
		<if test="status != null ">
			,
			status= #{status}
		</if>
		<if test="chartJson != null ">
			,
			chart_json= #{chartJson}
		</if>
		<if test="richText != null ">
			,
			rich_text= #{richText}
		</if>
		<if test="filePath != null ">
			,
			file_path= #{filePath}
		</if>
		,layout_id =
		#{layoutId},color_group_id = #{colorGroupId}
		where
		id
		= #{id}
	</update>
	<insert id="saveReportChart" parameterType="com.datau.rpm.dto.save.RpmInfoReportChartDTO">
		insert into
		rpm_info_report_chart(id,chart_type_id,
		kpi_id,axis,position,graph,report_id)
		values
		<foreach collection="list" item="chart" separator=",">
			(uuid(),#{chart.chartTypeId},#{chart.kpiId},#{chart.axis},#{chart.position},#{chart.graph},#{chart.reportId})
		</foreach>

	</insert>
	<select id="listColorGroup" resultType="com.datau.rpm.dto.RpmDimReportColorGroup">
		select id ,color_desc
		from
		rpm_dim_report_color_group
	</select>
	<select id="listQueryColor" resultType="com.datau.rpm.dto.RpmDimReportColorGroup">
		select id ,colorDesc
		from
		rpm_dim_report_query_color
	</select>
	<update id="updateReportMenuReport">
		update rpm_info_report_menu set report_id =
		#{reportId} where menu_code
		= #{menuCode}
	</update>
	<select id="getSaveReportById" resultType="com.datau.rpm.dto.save.SaveReportDTO">
		select rir.id,
		report_name,
		rir.desc,
		show_type,
		display_system,
		if_drill_down,
		rir.update_time,
		rir.update_user_id,
		STATUS,
		report_level,
		if_page,
		if_total,
		layout_id,
		color_group_id,
		current_step,
		if_finish,
		rirm.menu_code,
		total_show_type,
		DML,
		chart_type,
		chart_class_code,
		database_id,
		table_id,
		chart_json,
		rich_text,
		file_path,
		chart_refrech,
		data_source,
		if_outside_param,
		use_operable_group
		FROM
		rpm_info_report rir
		left join
		rpm_rel_report_menu
		rrrm on
		rir.id =
		rrrm.report_id
		left join
		rpm_info_report_menu
		rirm on
		rrrm.menu_id =
		rirm.id
		WHERE
		rir.id =
		#{reportId}
	</select>

	<select id="listSaveRportKpi" resultType="com.datau.rpm.dto.save.SaveRportKpiDTO">
		SELECT
		report_id,
		kpi_id,
		kpi_code,
		if_drill_down,
		subreport_id,
		sort,
		if_query_time,
		time_type_id,
		if_query_search,
		search_type_id,
		if_query_group_by,
		if_query_order_by,
		if_query_color,
		query_color_upper,
		query_color_lower,
		query_color_id,
		if_group_show,
		rpm_rel_report_kpi.rname,
		if_related,
		if_time,
		if_number,
		sort,query_group_by_sort,query_search_sort,
		time_default_offset,
		time_related_selected,
		time_range_offset,
		related_kpi_id,
		related_kpi_code,
		related_sub_kpi_id,
		related_sub_kpi_code,
		if_query_default_sort,
		query_default_order,
		if_total,
		total_show_sort,
		if_operable_group_by
		FROM
		rpm_rel_report_kpi
		WHERE
		report_id =
		#{reportId}
		order by
		sort
	</select>

	<select id="listBarAndLineCharts" resultType="com.datau.rpm.dto.save.RpmInfoReportChartDTO">
		SELECT
		rirc.id,
		rirc.chart_type_id,
		rirc.kpi_id,
		rirc.axis,
		rirc.position,
		rirc.graph,
		rirc.report_id,
		rrrk.kpi_code
		FROM
		rpm_info_report_chart rirc
		LEFT JOIN
		rpm_rel_report_kpi rrrk ON rirc.kpi_id = rrrk.kpi_id
		AND rirc.report_id
		= rrrk.report_id
		WHERE
		rirc.report_id = #{reportId} and
		rirc.chart_type_id = 1
	</select>

	<select id="listChartPieKpiId" resultType="java.lang.String">
		SELECT
		kpi_id
		FROM
		rpm_info_report_chart
		where
		report_id = #{reportId} and
		chart_type_id =
		2
	</select>
	<select id="listSonReport" resultType="com.datau.rpm.dto.ReportDTO">
		select
		id,report_name,report_name name from
		rpm_info_report where if_finish =
		1 and report_level = 2
	</select>
	<select id="listParentReport" resultType="com.datau.rpm.dto.ReportDTO">
		SELECT
		rir.id,
		report_name,
		report_name NAME
		FROM
		rpm_info_report rir
		LEFT
		JOIN rpm_rel_report_menu rrrm ON rir.id = rrrm.report_id
		WHERE
		if_finish = 1
		AND report_level = 1
		AND rrrm.menu_id IN (
		SELECT
		id
		FROM
		rpm_info_report_menu
		WHERE 1=1
		<if test="menuCode != null &amp;&amp; menuCode != ''">
			and
			menu_code like
			CONCAT(#{menuCode},'%')
		</if>
		)
	</select>
	<select id="listUser" resultType="com.datau.rpm.dto.UserDTO">
		select id,nickname,nickname
		name from
		dc_info_user where user_type = 1
	</select>
	<select id="listRelUser" resultType="com.datau.rpm.dto.UserDTO">
		SELECT
		distinct id,
		nickname,nickname
		name
		FROM
		dc_info_user diu
		left join
		rpm_rel_report_kpi_user rrru on
		diu.id =
		rrru.user_id
		where
		rrru.report_id = #{reportId} and
		diu.user_type = 1
	</select>
	<update id="updateSaveSix" parameterType="com.datau.rpm.dto.save.SaveReportDTO">
		update rpm_info_report
		set
		update_user_id =
		#{updateUserId},update_time=
		now(),if_finish=
		#{ifFinish},current_step=
		#{currentStep},status= #{status}
		where
		id
		=
		#{id}
	</update>
	<delete id="removeReportRelUserByReportId" parameterType="java.lang.String">
		delete
		from rpm_rel_report_user where report_id = #{reportId}

	</delete>
	<insert id="saveRpmRelReportUser" parameterType="com.datau.rpm.dto.RpmRelReportUserDTO">
		REPLACE into rpm_rel_report_user(report_id,user_id) values
		<foreach collection="list" item="dto" separator=",">
			(#{dto.reportId},#{dto.userId})
		</foreach>
	</insert>
	<update id="updateMenuReportToNull">
		update rpm_info_report_menu set report_id =
		null where
		report_id
		= #{reportId}
	</update>
	<update id="updateOne" parameterType="com.datau.rpm.dto.save.SaveOneDTO">
		update rpm_info_report set
		report_name = #{reportName},
		report_type = #{reportType},
		show_type =
		#{showType},
		display_system = #{displaySystem},
		report_level =
		#{reportLevel},
		chart_type = #{chartType},
		chart_class_code =
		#{chartClassCode},
		chart_refrech = #{chartRefrech},
		data_source =
		#{dataSource},
		update_user_id = #{updateUserId},
		update_time=now()
		where
		id = #{id}
	</update>
	<delete id="removeNotKpi">
		delete from rpm_rel_report_kpi where report_id =
		#{reportId}
		and kpi_id
		not in (
		<foreach collection="list" item="kpiId" separator=",">
			#{kpiId}
		</foreach>
		)
	</delete>
	<update id="updateUpReportKpi" parameterType="com.datau.rpm.dto.save.SaveRportKpiDTO">
		insert into
		rpm_rel_report_kpi(report_id,kpi_id,
		rpm_rel_report_kpi.rname,sort,if_time,if_number,kpi_code,editStatus)
		values
		<foreach collection="list" item="kpi" separator=",">
			(#{kpi.reportId},#{kpi.kpiId},#{kpi.rname},#{kpi.sort},#{kpi.ifTime},#{kpi.ifNumber},#{kpi.kpiCode},#{kpi.editStatus})
		</foreach>
		on duplicate key update
		rname = values(rname),
		sort = values(sort),
		if_time =
		values(if_time)
		,
		if_number =
		values(if_number)
		,kpi_code =
		values(kpi_code)

	</update>
	<update id="cleanKpiConfigThree">
		update rpm_rel_report_kpi set
		if_query_group_by =
		null,
		if_query_order_by =
		null,
		if_query_time = null,
		time_type_id = null,
		if_query_search = null,
		search_type_id = null,
		if_query_color = null,
		query_color_upper = null,
		query_color_lower = null,
		query_color_id =
		null,
		query_group_by_sort = null
		,
		query_search_sort = null,
		time_query_default = null
		,
		time_default_offset = null,
		time_related_range = null,
		time_range_offset = null,
		if_query_default_sort = null,
		query_default_order = null,
		if_total =
		null,
		total_show_sort = null,
		if_operable_group_by = 0
		where
		report_id =
		#{reportId}
	</update>
	<update id="cleanKpiConfigFour">
		update rpm_rel_report_kpi set
		if_drill_down = null,
		subreport_id = null,
		if_related = null
		where report_id =
		#{reportId}
	</update>
	<update id="cleanKpiConfigFive">
		update rpm_rel_report_kpi set
		if_drill_down = null,
		subreport_id = null,
		if_related = null,if_group_show = 0
		where report_id
		=
		#{reportId}
	</update>
	<delete id="deleteReportChartByReportId">
		delete from rpm_info_report_chart where report_id =
		#{reportId}
	</delete>
	<select id="countSubReportById" resultType="java.lang.Integer">
		select count(0)
		from
		rpm_rel_report_kpi where subreport_id = #{reportId}
	</select>
	<delete id="removeReportKpi">
		delete from rpm_rel_report_kpi where report_id =
		#{reportId}
	</delete>
	<delete id="removeReportChart">
		delete from rpm_info_report_chart where report_id =
		#{reportId}
	</delete>


	<select id="listReportKPI" resultType="com.datau.rpm.dto.ReportKPIDTO">
		select
		report_id,kpi_id,if_drill_down,subreport_id,sort,
		if_query_time,time_type_id,if_query_search,search_type_id,
		if_query_group_by,if_query_order_by,if_query_color,
		query_color_upper,query_color_lower,query_color_id,if_group_show,
		rname,if_related,if_time
		from
		rpm_rel_report_kpi
		where
		report_id=#{reportId}
		order by sort,query_group_by_sort
	</select>

	<select id="listReportIdByGroupSort" resultType="String">
		select kpi_id
		from rpm_rel_report_kpi
		where
		report_id=#{reportId} and
		if_query_group_by is not null and if_query_group_by = 1 order by
		query_group_by_sort
	</select>

	<select id="getReportChartType" resultType="com.datau.rpm.dto.save.RpmInfoReportChartDTO">
		select
		id,chart_type_id,kpi_id,axis,position,graph,report_id
		from
		rpm_info_report_chart
		where report_id=#{reportId}
	</select>

	<insert id="saveReportSql" parameterType="com.datau.rpm.dto.ReportSqlDTO">
		replace into rpm_rel_report_sql
		(report_id,chart_type,sql_report,sql_count,sql_total,database_id)
		values
		<foreach collection="list" item="item" index="index"
			separator=",">
			(#{item.reportId},#{item.chartType},#{item.sqlReport},#{item.sqlCount},#{item.sqlTotal},#{item.databaseId})
		</foreach>
	</insert>
	<select id="usedKPI" resultType="int">
		select
		count(report_id)
		from
		rpm_rel_report_kpi
		where
		kpi_id=#{kpiId}
	</select>
	<select id="getReportSql" resultType="com.datau.rpm.dto.ReportSqlDTO">
		select
		report_id,chart_type,sql_report,sql_count,sql_total,database_id
		from
		rpm_rel_report_sql
		where
		report_id=#{reportId} and
		chart_type=#{chartType}
	</select>

	<delete id="deleteReportChartNotKpi" parameterType="java.util.Map">
		delete
		from rpm_info_report_chart where report_id =
		#{reportId} and
		kpi_id not
		in (
		<foreach collection="list" item="kpi" separator=",">
			#{kpi.kpiId}
		</foreach>
		)
	</delete>
	<insert id="saveRpmInfoReportMenu">
		insert into rpm_rel_report_menu
		select #{reportId},id
		from
		rpm_info_report_menu where menu_code =
		#{menuCode}
	</insert>

	<delete id="removeRpmInfoReportMenuByReportId">
		delete from rpm_rel_report_menu where report_id =
		#{id}
	</delete>
	<select id="listReportByMenu" resultType="com.datau.rpm.dto.ReportDTO">
		SELECT
		rir.id,
		report_name,
		report_name NAME
		FROM
		rpm_info_report rir
		LEFT
		JOIN
		rpm_rel_report_menu rrrm ON rir.id = rrrm.report_id
		WHERE
		if_finish = 1
		AND report_level = 1
		AND rrrm.menu_id = (
		SELECT
		id
		FROM
		rpm_info_report_menu
		WHERE 1=1
		and
		menu_code = #{menuCode}
		)
	</select>
	<select id="listKpiByReportId" resultType="com.datau.rpm.dto.ReportKPIDTO">
		select
		report_id,kpi_id,
		rname
		from
		rpm_rel_report_kpi
		where
		report_id=#{reportId}
		order by sort
	</select>
	<select id="listKpiByReportIds" resultType="com.datau.rpm.dto.ReportKPIDTO">
		select
		report_id,kpi_id,
		rname
		from
		rpm_rel_report_kpi
		where
		report_id in (
		<foreach collection="list" item="reportId" separator=",">
			#{reportId}
		</foreach>
		)
		order by sort
	</select>
	<select id="listKpiByReportAndUser" resultType="com.datau.rpm.dto.ReportKPIDTO">
		select
		rrrk.report_id,rrrk.kpi_id,
		rrrk.rname
		from
		rpm_rel_report_kpi rrrk
		left
		join rpm_rel_report_kpi_user rrrku on rrrk.report_id =
		rrrku.report_id
		and rrrk.kpi_id = rrrku.kpi_id
		where
		rrrku.report_id=#{reportId}
		and
		rrrku.user_id = #{userId}
		order by sort
	</select>
	<select id="listKpiByReport" resultType="com.datau.rpm.dto.ReportKPIDTO">
		select
		rrrk.report_id,rrrk.kpi_id,
		rrrk.rname,rrrku.user_id
		from
		rpm_rel_report_kpi rrrk
		left
		join rpm_rel_report_kpi_user rrrku on
		rrrk.report_id =
		rrrku.report_id
		and rrrk.kpi_id = rrrku.kpi_id
		where
		rrrku.report_id=#{reportId}
		order by
		sort
	</select>
	<delete id="removeReportKpiRelUserByReportId" parameterType="java.lang.String">
		delete
		from rpm_rel_report_kpi_user where report_id = #{reportId}
	</delete>
	<insert id="saveRpmRelReportKpiUser" parameterType="com.datau.rpm.dto.RpmRelReportKpiUserDTO">
		REPLACE into rpm_rel_report_kpi_user(report_id,kpi_id,user_id) values
		<foreach collection="list" item="dto" separator=",">
			(#{dto.reportId},#{dto.kpiId},#{dto.userId})
		</foreach>
	</insert>
	<select id="getRelReportKpi" resultType="com.datau.rpm.dto.save.SaveRportKpiDTO">
		SELECT
		rrrk.report_id,
		rrrk.kpi_id,
		kpi_code,
		if_drill_down,
		subreport_id,
		sort,
		if_query_time,
		time_type_id,
		if_query_search,
		search_type_id,
		if_query_group_by,
		if_query_order_by,
		if_query_color,
		query_color_upper,
		query_color_lower,
		query_color_id,
		if_group_show,
		rname,
		if_related,
		if_time,
		if_number
		FROM
		rpm_rel_report_kpi rrrk
		WHERE
		rrrk.report_id =
		#{reportId} and
		rrrk.kpi_id = #{kpiId}
	</select>
	<select id="getRelReportKpiByKpiCode" resultType="com.datau.rpm.dto.save.SaveRportKpiDTO">
		SELECT
		rrrk.report_id,
		rrrk.kpi_id,
		kpi_code,
		if_drill_down,
		subreport_id,
		sort,
		if_query_time,
		time_type_id,
		if_query_search,
		search_type_id,
		if_query_group_by,
		if_query_order_by,
		if_query_color,
		query_color_upper,
		query_color_lower,
		query_color_id,
		if_group_show,
		rname,
		if_related,
		if_time,
		if_number
		FROM
		rpm_rel_report_kpi rrrk
		WHERE
		rrrk.report_id =
		#{reportId} and
		rrrk.kpi_code = #{kpiCode}
	</select>

	<select id="listChartType" resultType="com.datau.rpm.dto.chart.ChartTypeDTO">
		select id,value
		from
		rpm_dim_chart_type
	</select>

	<select id="listTotalKpis" resultType="java.lang.String">
		select
		kpi_id from
		rpm_rel_report_kpi
		where report_id = #{reportId} and if_total =
		#{ifTotal} order by
		total_show_sort
	</select>
	<select id="listTotalKpiCodes" resultType="java.lang.String">
		select
		kpi_code from
		rpm_rel_report_kpi
		where report_id = #{reportId}
		<if test='ifTotal != "-1"'>
			and if_total =
			#{ifTotal}
		</if>
		order by
		total_show_sort
	</select>
	<!-- 外部参数表字段 -->
	<sql id="base_outside_param">
		id,report_id,param_key,param_sort,param_desc,param_type
	</sql>


	<insert id="saveRelReportOutsideParam" parameterType="com.datau.rpm.dto.save.RelReportOutsideParamDTO">
		insert into
		rpm_rel_report_outside_param(id,report_id,param_key,param_sort,param_desc,param_type)
		values
		<foreach collection="list" item="dto" separator=",">
			(uuid(),#{dto.reportId},#{dto.paramKey},#{dto.paramSort},#{dto.paramDesc},#{dto.paramType})
		</foreach>

	</insert>
	<select id="listRelReportOutsideParam" resultType="com.datau.rpm.dto.save.RelReportOutsideParamDTO">
		select
		<include refid="base_outside_param" />
		from
		rpm_rel_report_outside_param
		where report_id = #{reportId} order by
		param_sort
	</select>
	<delete id="removeRelReportOutsideParam" parameterType="java.lang.String">
		delete
		from
		rpm_rel_report_outside_param
		where report_id = #{reportId}
	</delete>


	<!-- 报表KPI下钻关联表表字段 -->
	<sql id="base_report_kpi_drill">
		report_id,kpi_id,sort,related_kpi_id,related_kpi_code,related_sub_kpi_id,related_sub_kpi_code,time_related_selected,time_range_offset
	</sql>


	<insert id="saveRelReportKpiDrill" parameterType="com.datau.rpm.dto.save.RelReportKpiDrillDTO">
		insert into
		rpm_rel_report_kpi_drill(report_id,kpi_id,sort,related_kpi_id,related_kpi_code,related_sub_kpi_id,related_sub_kpi_code,time_related_selected,time_range_offset)
		values
		<foreach collection="list" item="dto" separator=",">
			(#{dto.reportId},#{dto.kpiId},#{dto.sort},#{dto.relatedKpiId},#{dto.relatedKpiCode},#{dto.relatedSubKpiId},#{dto.relatedSubKpiCode},#{dto.timeRelatedSelected},#{dto.timeRangeOffset})
		</foreach>

	</insert>
	<select id="listRelReportKpiDrill" resultType="com.datau.rpm.dto.save.RelReportKpiDrillDTO">
		select
		<include refid="base_report_kpi_drill" />
		from
		rpm_rel_report_kpi_drill
		where report_id = #{reportId} order by
		sort
	</select>
	<delete id="removeRelReportKpiDrill" parameterType="java.lang.String">
		delete
		from
		rpm_rel_report_kpi_drill
		where report_id = #{reportId}
	</delete>
	<select id="listReportIdByOperableGroup" resultType="String">
		select
		kpi_id
		from rpm_rel_report_kpi
		where
		report_id=#{reportId} and
		if_operable_group_by = 1
	</select>
</mapper>