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


	<select id="getReportViewById" resultType="com.datau.rpm.dto.view.ReportViewDTO">
		select rir.id
		report_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,
		display_system,
		if_outside_param,use_operable_group
		FROM
		rpm_info_report rir left join
		rpm_info_report_menu
		rirm on rir.id =
		rirm.report_id
		WHERE
		rir.id =
		#{reportId}
	</select>
	<select id="listQueryByUser" resultType="com.datau.rpm.dto.view.ReportViewQueryDTO">
		SELECT
		rrrk.kpi_id,
		rname
		kpi_name,
		rrrk.report_id,
		if_query_time,
		time_type_id,
		if_query_search,
		search_type_id,
		kpi_code,
		if_number,
		time_default_offset
		FROM
		rpm_rel_report_kpi rrrk
		LEFT JOIN
		(select distinct report_id,kpi_id
		<if test="userId != null &amp;&amp; userId != '' &amp;&amp; userId != 'admin'">
			,user_id
		</if>
		from
		rpm_rel_report_kpi_user) rrrku ON
		rrrk.report_id =
		rrrku.report_id
		AND
		rrrk.kpi_id = rrrku.kpi_id
		WHERE
		1 = 1
		AND (
		if_query_time = 1
		OR
		if_query_search = 1
		)
		AND rrrk.report_id =
		#{reportId}
		<if test="userId != null &amp;&amp; userId != ''">
			AND rrrku.user_id
			= #{userId}
		</if>
		order by query_search_sort 

	</select>
	<select id="listViewPieKpiByUser" resultType="com.datau.rpm.dto.view.ReportViewPieDTO">
		SELECT
		rirc.kpi_id,
		rrrk.rname as kpi_name,
		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
		LEFT JOIN
		(select distinct report_id,kpi_id
		<if test="userId != null &amp;&amp; userId != '' &amp;&amp; userId != 'admin'">
			,user_id
		</if>
		from rpm_rel_report_kpi_user) rrrku
		ON rrrk.report_id =
		rrrku.report_id
		AND
		rrrk.kpi_id = rrrku.kpi_id
		where
		rirc.report_id = #{reportId}
		<if test="userId != null &amp;&amp; userId != ''">
			and rrrku.user_id = #{userId}
		</if>
		and
		chart_type_id =
		2
	</select>
	<select id="getSqlReportById" parameterType="java.lang.String">
		SELECT
		sql_report
		FROM
		rpm_rel_report_sql where report_id = #{reportId}
	</select>

	<select id="listReportByUserAndMenu" resultType="com.datau.rpm.dto.ReportDTO">
		SELECT distinct
		rir.id,
		rir.report_name,
		report_name NAME
		FROM
		rpm_info_report rir
		LEFT
		JOIN
		rpm_rel_report_kpi_user
		rrru
		ON rir.id = rrru.report_id
		left join
		rpm_rel_report_menu rrrm on
		rir.id = rrrm.report_id
		left join
		rpm_info_report_menu rirm on rirm.id = rrrm.menu_id
		where 1=1 and
		rir.status = 1 and rrru.user_id = #{userId} and rirm.menu_code =
		#{menuCode}
	</select>
	<select id="listRportKpiByUser" 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,
		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 rrrk LEFT JOIN

		(select distinct
		report_id,kpi_id
		<if test="userId != null &amp;&amp; userId != '' &amp;&amp; userId != 'admin'">
			,user_id
		</if>
		from
		rpm_rel_report_kpi_user) rrrku on rrrk.report_id =
		rrrku.report_id
		and
		rrrk.kpi_id = rrrku.kpi_id
		WHERE
		rrrk.report_id = #{reportId}
		<if test="userId != null &amp;&amp; userId != ''">
			and
			rrrku.user_id = #{userId}
		</if>

		order by sort,query_group_by_sort 
	</select>

	<select id="listBarAndLineChartByUser" 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
		LEFT JOIN (select distinct
		report_id,kpi_id
		<if test="userId != null &amp;&amp; userId != '' &amp;&amp; userId != 'admin'">
			,user_id
		</if>
		from rpm_rel_report_kpi_user) rrrku
		ON rrrk.report_id
		=
		rrrku.report_id
		AND rrrk.kpi_id = rrrku.kpi_id
		WHERE
		rirc.report_id =
		#{reportId}
		<if test="userId != null &amp;&amp; userId != ''">
			and rrrku.user_id = #{userId}
		</if>
		and
		rirc.chart_type_id = 1
	</select>
</mapper>
