
synopsis:

	System currency list

description:

	Prints list of numbers of available erratas for each registered system

columns:

	system_id Server ID
	org_id	Organization ID which is server registered to
	name	Name of the server
	critical	Number of critical security updates available
	important Number of important security updates available
	moderate	Number of moderate importance security updates available
	low	Number of low importance security updates available
	bug	Number of bug fixes available
	enhancement	Number of enhancements available
	score Total system score

params:

	p_crit	java.sc_crit
	p_imp	java.sc_imp
	p_mod	java.sc_mod
	p_low	java.sc_low
	p_bug	java.sc_bug
	p_enh	java.sc_enh

sql:

	select system_id, org_id, name, critical, important, moderate, low, bug, enhancement,
		((critical * :p_crit) + (important *  :p_imp) + (moderate * :p_mod) + (low * :p_low) + (bug * :p_bug) + (enhancement * :p_enh)) as score
	from (
	select S.id as system_id,
		S.org_id as org_id,
		S.name as name,
		sum(case when E.advisory_type = 'Security Advisory' and E.severity = 'errata.sev.label.critical' then 1 else 0 end) critical,
		sum(case when E.advisory_type = 'Security Advisory' and E.severity = 'errata.sev.label.important' then 1 else 0 end) important,
		sum(case when E.advisory_type = 'Security Advisory' and E.severity = 'errata.sev.label.moderate' then 1 else 0 end) moderate,
		sum(case when E.advisory_type = 'Security Advisory' and (E.severity = 'errata.sev.label.low' or E.severity is null) then 1 else 0 end) low,
		sum(case when E.advisory_type = 'Bug Fix Advisory' then 1 else 0 end) as bug,
		sum(case when E.advisory_type = 'Product Enhancement Advisory' then 1 else 0 end) as enhancement
	from rhnServer S
	left join (select distinct err.id, snc.server_id, err.synopsis, err.advisory_type, es.label as severity
		from rhnErrata err
		join rhnServerNeededCache SNC on err.id=SNC.errata_id
		left join rhnErrataSeverity es on err.SEVERITY_ID = es.id) E on S.id=E.server_id
	group by S.id, S.org_id, name
	) X
	-- where placeholder
	order by org_id, system_id

