SSystemsView

in der kdb - Abfrage für SSystems / Shibboleth

SELECT DISTINCT
	p.id AS id,
	p.apid AS apid,
	p.login AS login,
	p.login_old AS login_old,
	p.lastname AS lastname,
	p.firstname AS firstname,
	p.birthname AS birthname,
	p.birthdate AS birthdate,
	c.pass AS pass,
	c.pass_freeradius AS pw_sha512,
	c.unsec AS unsec,
	(CASE g.caption 
		WHEN 'lecturers' THEN 
			p.email_ksh
# geändert am 08.04.2025
#			IF((RIGHT(p.mail, 9) = '@ksh-m.de'), p.mail, IF(ISNULL(va2.source), p.mail, va2.source)) 
		WHEN 'professors' THEN p.mail 
		WHEN 'emeriti' THEN p.mail 
		WHEN 'students' THEN 
			IF(ISNULL(va1.source), p.mail, va1.source) 
		WHEN 'staff' THEN p.mail 
		WHEN 'easyroamadmin' THEN p.mail 
		WHEN 'seminarif' THEN p.mail 
		WHEN 'tutors' THEN p.mail 
	END) AS email,
	GROUP_CONCAT(DISTINCT g.caption SEPARATOR ',') AS groups 
FROM
	kdb.persons p 
	LEFT JOIN kdb.codes c ON (c.pid = p.id)
	JOIN kdb.rel_group_person rgp ON (rgp.kpid = p.id)
	JOIN kdb.groups g ON (g.id = rgp.groupid)
	LEFT JOIN mailserver.virtual_aliases va1 ON (va1.login = p.login) and (va1.domain_id = 15)
	LEFT JOIN mailserver.virtual_aliases va2 ON (va2.login = p.login) and (va2.domain_id = 20) 
WHERE
	(rgp.groupid in (91,92,93,94,95,105,108,150))
	AND (rgp.active = 1) 
	AND (p.login > '') 
	AND (c.unsec > '') 
	AND (c.pass > '')
GROUP BY
	p.id