kb.sql

SELECT 
	zno.zno_score_sum + certificate.certification_sum * s.weight_certification + other.other_sum * 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 mse.markexchange AS other_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 IN  (
		-- А реально якось краще фільтрувати ? Наприклад, щоб в майбутньому інші університети могли теж користуватись системою
		'Курси "Львівського національного університету імені Івана Франка',
		'Диплом I - III ступенів IV етапу Всеукраїнських учнівських олімпіад з базових предметів', 
		'Диплом І – ІІІ ступенів ІІІ етапу Всеукраїнських конкурсів-захистів науково-дослідницьких робіт учнів–членів Малої академії наук',
		'Довідка за результатами підсумкової атестації з зазначенням кількості балів, що додаватимуться до конкурсного бала вступника',
		'Творчий конкурс НУ ЛП'
	)
) other ON other.person_id = e.person_id