kb2.sql

SELECT 
	zno.zno_score_sum + certificate.certification_sum * s.weight_certification + mse.markexchange * s.weight_award AS kb, 
	e.id as enrolment_id 
FROM q_dc_enrolment e
LEFT OUTER JOIN q_ob_specoffer s ON e.specoffer_id = s.id
LEFT OUTER JOIN (
	-- бали за зно, посумовані
	SELECT 
	SUM(mse.markexchange * ss.weightsubject) AS zno_score_sum,
	ees.enrolment_id
	FROM q_dt_enrolmentenrolsubject ees
	LEFT OUTER JOIN q_dc_enrolment ei ON ees.enrolment_id = ei.id
	LEFT OUTER JOIN q_ob_specoffer si ON ei.specoffer_id = si.id
	LEFT OUTER JOIN q_od_specoffersubject ss ON ss.specoffer_id = si.id
	LEFT OUTER JOIN q_od_markscaleexchange mse ON ees.markscale_id = mse.markscale_id AND markmin <= ees.mark AND markmax >= ees.mark
	LEFT OUTER JOIN q_od_personenrolsubject pes ON ees.personenrolsubject_id = pes.id
	GROUP BY ees.enrolment_id
) zno ON zno.enrolment_id = e.id
LEFT OUTER JOIN (
	-- Бали з атестату, не помножені на ваговий коефіцієнт
	SELECT mse.markexchange AS certification_sum, pp.person_id FROM q_od_personpaper pp
	LEFT OUTER JOIN q_rf_papertype pt ON pt.id = pp.papertype_id
	LEFT OUTER JOIN q_od_markscaleexchange mse ON pp.markscale_id = mse.markscale_id AND markmin <=  pp.mark AND markmax >=  pp.mark
	WHERE pt.name = 'Атестат про повну загальну середню освіту'
) certificate ON certificate.person_id = e.person_id
LEFT OUTER JOIN (
	-- Всі решта бали(дивись фільтрування)
	SELECT
	MAX(ees.id) AS id,
	--mse.markexchange AS award_score,
	ees.enrolment_id
	FROM q_dt_enrolmentenrolsubject ees
	LEFT OUTER JOIN q_dc_enrolment ei ON ees.enrolment_id = ei.id
	LEFT OUTER JOIN q_ob_specoffer si ON ei.specoffer_id = si.id
	LEFT OUTER JOIN q_od_personenrolsubject pes ON ees.personenrolsubject_id = pes.id
	WHERE ees.personaward_id IS NOT NULL
	GROUP BY ees.enrolment_id
) other ON other.enrolment_id = e.id
LEFT OUTER JOIN q_dt_enrolmentenrolsubject aees ON aees.id = other.id
LEFT OUTER JOIN q_od_markscaleexchange mse ON aees.markscale_id = mse.markscale_id AND mse.markmin <= aees.mark AND mse.markmax >= aees.mark