Skip to content

Create a report of institutions/views with modified bentos

Essentially, create a report based on this SQL query:

SELECT
	i.code,
	i.name,
	c.user_view,
	ARRAY_TO_STRING(c.configured, ', ') as configured,
	ARRAY_TO_STRING(t.templated, ', ') as templated
FROM
	institutions i
INNER JOIN
	institution_groups g
	ON g.id = i.institution_group_id
INNER JOIN (
	SELECT
		institution_id,
		user_view,
		ARRAY_AGG(CASE
					WHEN pb.code IN ('journals', 'journals-primo')
					THEN -1
					ELSE predefined_bento_id
					END
				  ORDER BY "order") as visible_bentos,
		ARRAY_AGG(pb.code ORDER BY "order") as configured
	FROM
		configured_bentos cb
	INNER JOIN
		predefined_bentos pb
		ON cb.predefined_bento_id = pb.id
	WHERE
		active
		AND shown_by_default
	GROUP BY
		institution_id,
		user_view
	) c
	ON c.institution_id = i.id
INNER JOIN (
	SELECT
		inst_type,
		user_view,
		ARRAY_AGG(CASE
					WHEN pb.code IN ('journals', 'journals-primo')
					THEN -1
					ELSE predefined_bento_id
					END
				  ORDER BY default_order) as visible_bentos,
		ARRAY_AGG(pb.code ORDER BY default_order) as templated
	FROM
		template_view_bentos tvb
	INNER JOIN
		predefined_bentos pb
		ON tvb.predefined_bento_id = pb.id
	WHERE
		shown_by_default
	GROUP BY
		inst_type,
		user_view
	) t
	ON t.inst_type = g.inst_type
	   AND t.user_view = c.user_view
INNER JOIN (
		SELECT DISTINCT
			cb.institution_id,
			cb.user_view
		FROM
			configured_bentos cb
			LEFT JOIN template_view_bentos tvb ON cb.template_view_bento_id = tvb.id
		WHERE tvb.id IS NULL
			  OR (cb. "order" <> tvb.default_order
				  OR cb.display_name <> ''
				  OR cb.description <> ''
				  OR cb.shown_by_default <> tvb.shown_by_default
			     )
	) modified
	ON modified.institution_id = i.id
	   AND modified.user_view = c.user_view
INNER JOIN
	user_views u
    ON u.code = c.user_view
WHERE
	c.visible_bentos <> t.visible_bentos
ORDER BY
	i.code, u.id
Edited by Josh Wier