Главная » Поступающим в 2025 году » Конкурсные списки (заочная форма обучения)

SELECT u.*, a.*, a.id app_id, a.id_execution, a.user_id user_id, info.phone_home, tao.name target_name, f.title faculty_title, f.id faculty_id, f.abbr faculty_abbr, sp.id speciality_id, sp.name speciality, a1.cg_id agree_spec, ac.amount payment, ac.manual payment_manual, a1.order_id agree_order_id, MAX(a.docs_place) docs, MAX(a.agreed) agreement, a.payment_type_id, info.pr_rights, m.sum_mark, m1.sum_mark_avg, IF(aach.sum_score IS NULL, 0, IF(aach.sum_score>10 AND a.level_id NOT IN (4,12),10,aach.sum_score)) sum_ach, IF(t.ach_sum IS NULL, 0, IF(t.ach_sum>10 AND a.level_id NOT IN (4,12),10,t.ach_sum)) ach_sum_new, lka_sd3.date_sign date_agreed_sign, info.id_source, dsn.id_protected id_protected_snils, lka_sn.snils snils_value, ue.personal_number FROM app a LEFT OUTER JOIN (SELECT SUM(t.sum_mark) sum_mark, t.app_id FROM ((SELECT SUM(COALESCE(IF(m.exam_type=3,100, m.mark), 0)) sum_mark, m.app_id FROM marks m LEFT OUTER JOIN app a ON a.id=m.app_id LEFT OUTER JOIN spec sp ON sp.id=a.cg_id LEFT OUTER JOIN cg_exams ce ON ce.spec_id=sp.id AND ce.subject_id=m.subject_id WHERE (m.exam_type=1 OR (m.exam_type=2 AND m.checked>0 AND m.is_published=1) OR m.exam_type=3 OR m.exam_type=4 ) AND ce.is_option=0 AND sp.year=DATE_FORMAT(NOW(),'%Y') AND m.mark>=ce.min_ball AND a.cg_id = '1527' GROUP BY a.id ORDER BY a.id DESC) UNION (SELECT MAX(COALESCE(IF(m.exam_type=3,100, m.mark), 0)) sum_mark, m.app_id FROM marks m LEFT OUTER JOIN app a ON a.id=m.app_id LEFT OUTER JOIN spec sp ON sp.id=a.cg_id LEFT OUTER JOIN cg_exams ce ON ce.spec_id=sp.id AND ce.subject_id=m.subject_id WHERE (m.exam_type=1 OR (m.exam_type=2 AND m.checked>0 AND m.is_published=1) OR m.exam_type=3 OR m.exam_type=4) AND ce.is_option=1 AND sp.year=DATE_FORMAT(NOW(),'%Y') AND m.mark>=ce.min_ball AND a.cg_id = '1527' GROUP BY a.id ORDER BY m.mark DESC, m.exam_type DESC) ) AS t GROUP BY t.app_id) m ON m.app_id = a.id LEFT OUTER JOIN (select sum(COALESCE(IF(exam_type=3,100,mark), 0))/count(mark) sum_mark_avg, app_id from marks WHERE (mark is not NULL AND mark<>0 AND mark<>'') group by app_id) m1 ON m1.app_id = a.id LEFT OUTER JOIN user u ON u.id = a.user_id LEFT OUTER JOIN user_execution ue ON ue.id = a.id_execution LEFT OUTER JOIN lka_signdocs lka_sd3 ON lka_sd3.id_execution = a.id_execution AND lka_sd3.is_deleted=0 AND lka_sd3.id_type=3 LEFT OUTER JOIN abt_directions sp ON sp.id = a.cg_id LEFT OUTER JOIN abt_competitions acg ON acg.id=a.id_comp_group LEFT OUTER JOIN abt_profiles prof ON prof.id=acg.id_profile LEFT OUTER JOIN asu_contracts ac ON ac.eios_id = u.id AND ac.code=sp.code AND (ac.manual>0 OR ac.amount>0) AND ac.year=DATE_FORMAT(NOW(),'%Y') LEFT OUTER JOIN app a1 ON a1.id_execution = a.id_execution AND a1.agreed>0 AND a1.is_deleted=0 AND (a1.create_date>UNIX_TIMESTAMP(STR_TO_DATE('2024-06-19','%Y-%m-%d')) OR a1.update_date>UNIX_TIMESTAMP(STR_TO_DATE('2024-06-19','%Y-%m-%d'))) LEFT OUTER JOIN (select sum(COALESCE(score, 0)) sum_score, id_execution from abt_ach WHERE is_deleted=0 GROUP BY id_execution) aach ON aach.id_execution = a.id_execution LEFT JOIN ( SELECT app.id app_id, SUM(COALESCE(IF(subjects_abt.id > 0, IF(ach_exams.subject_id > 0, abt_ach.score, 0), abt_ach.score), 0)) ach_sum FROM app LEFT JOIN abt_ach ON abt_ach.id_execution = app.id_execution AND abt_ach.is_deleted = 0 LEFT JOIN docs ON abt_ach.id_document = docs.id LEFT JOIN subjects_abt ON docs.id_subject= subjects_abt.id LEFT JOIN cg_exams ach_exams ON ach_exams.spec_id = app.cg_id AND ach_exams.subject_id = subjects_abt.id WHERE app.is_deleted = 0 AND app.cg_id = '1527' GROUP BY app.id )t ON t.app_id = a.id LEFT OUTER JOIN abit_add_info info ON info.id_execution = a.id_execution LEFT OUTER JOIN faculty f ON f.id = sp.id_department LEFT OUTER JOIN orders ord ON ord.id = a.order_id LEFT OUTER JOIN target_orgs tao ON tao.id = a.target_id LEFT OUTER JOIN docs dsn ON dsn.id_user = a.user_id AND dsn.id_type=104 AND dsn.id_protected>0 AND dsn.is_deleted=0 LEFT OUTER JOIN lka_snils lka_sn ON lka_sn.id_user=u.id WHERE u.lastname IS NOT NULL AND a.is_deleted=0 AND ue.date_award>0 AND sp.year=2024 AND (a.create_date>UNIX_TIMESTAMP(STR_TO_DATE('2024-06-19','%Y-%m-%d')) OR a.update_date>UNIX_TIMESTAMP(STR_TO_DATE('2024-06-19','%Y-%m-%d'))) AND sp.id NOT IN (2641, 2642) AND sp.id = '1527' AND a.spec_type_id = '2' AND (a.payment_type_id=2 OR a.payment_type_id=3) GROUP BY u.id ORDER BY sum_mark+ach_sum_new desc