SELECT sp.program, COUNT( DISTINCT CASE WHEN sp.father_profile_id IS NOT NULL THEN CONCAT('F-', sp.father_profile_id) WHEN sp.mother_profile_id IS NOT NULL THEN CONCAT('M-', sp.mother_profile_id) ELSE NULL END ) AS total FROM shieldtech_admission.student_profiles sp GROUP BY sp.program ORDER BY sp.program;

SELECT 'Prospect' label, DATE_FORMAT(gb.created_at,'%Y') sy, COUNT(gb.id) total FROM shieldtech_admission.guest_books gb GROUP BY sy UNION ALL SELECT 'Graduate' label, DATE_FORMAT(sp.updated_at,'%Y') sy, COUNT(sp.id) total FROM shieldtech_admission.student_profiles sp WHERE sp.grade = 'GR12' AND sp.program = 'HS' GROUP BY sy

SELECT 'Register' label, DATE_FORMAT(sp.created_at,'%Y-%m') ym, COUNT(sp.id) total FROM shieldtech_admission.admission_student_profiles sp GROUP BY ym UNION ALL SELECT 'Enroll' label, DATE_FORMAT(sp.enrollment_date,'%Y-%m') ym, COUNT(sp.id) total FROM shieldtech_admission.admission_student_profiles sp WHERE sp.enrollment_date IS NOT NULL GROUP BY ym ORDER BY ym ASC

SELECT qo.text_id AS channel, COUNT(DISTINCT gbq.guest_book_id) AS total FROM shieldtech_admission.guest_book_questionnaires gbq JOIN shieldtech_admission.questionnaire_options qo ON qo.id = gbq.questionnaire_option_id WHERE qo.question_id = 1 -- ID pertanyaan 'Bagaimana Anda tahu tentang SHI' AND gbq.answer = 1 -- HANYA opsi yang dipilih GROUP BY qo.text_id, qo.ordinal ORDER BY qo.ordinal;

SELECT qo.text_id AS channel, COUNT(DISTINCT gbq.guest_book_id) AS total FROM shieldtech_admission.guest_book_questionnaires gbq JOIN shieldtech_admission.questionnaire_options qo ON qo.id = gbq.questionnaire_option_id JOIN shieldtech_admission.guest_books gb ON gb.id = gbq.guest_book_id WHERE qo.question_id = 1 -- pertanyaan channel awareness AND gbq.answer = 1 -- HANYA opsi yang dipilih AND gb.unit_id = :unit_id GROUP BY qo.text_id, qo.ordinal ORDER BY qo.ordinal;

SELECT qo.text_id AS reason, COUNT(DISTINCT gbq.guest_book_id) AS total FROM shieldtech_admission.guest_book_questionnaires gbq JOIN shieldtech_admission.questionnaire_options qo ON qo.id = gbq.questionnaire_option_id WHERE qo.question_id = 2 -- ID pertanyaan 'Alasan memilih SHI' AND gbq.answer = 1 -- HANYA opsi yang dipilih GROUP BY qo.text_id, qo.ordinal ORDER BY qo.ordinal;

SELECT qo.text_id AS reason, COUNT(DISTINCT gbq.guest_book_id) AS total FROM shieldtech_admission.guest_book_questionnaires gbq JOIN shieldtech_admission.questionnaire_options qo ON qo.id = gbq.questionnaire_option_id JOIN shieldtech_admission.guest_books gb ON gb.id = gbq.guest_book_id WHERE qo.question_id = 2 AND gbq.answer = 1 -- HANYA opsi yang dipilih AND gb.unit_id = :unit_id GROUP BY qo.text_id, qo.ordinal ORDER BY qo.ordinal;

SELECT DATE_FORMAT(i.created_at, '%Y-%m') AS ym, u.short_name AS unit, SUM(ii.item_value) AS total_sales FROM shieldtech_finance.invoices i JOIN shieldtech_finance.invoice_items ii ON i.id = ii.invoice_id JOIN shieldtech_common.units u ON i.unit_id = u.id WHERE i.created_at IS NOT NULL AND i.status_id = 2 -- PAID AND ii.item_type IN (0, 1) -- FORM_FEE & ENROLLMENT_FEE AND YEAR(i.created_at) = :yr GROUP BY ym, u.short_name ORDER BY ym ASC, u.short_name ASC;

SELECT COALESCE(u.short_name,'All') unit, DATE_FORMAT(sp.created_at,'%Y-%m') ym, sp.gender, COUNT(sp.id) total FROM shieldtech_admission.admission_student_profiles sp LEFT JOIN shieldtech_common.units u ON u.id=sp.unit_id GROUP BY unit,ym,sp.gender ORDER BY ym ASC

SELECT s.unit_id, u.short_name AS unit, SUM(s.total_seat) AS total_seat, COALESCE(MAX(r.reserved), 0) AS reserved FROM ( -- seat biasa SELECT ms.unit_id, ms.year, SUM(msgd.slot) AS total_seat FROM shieldtech_admission.master_seats ms JOIN shieldtech_admission.master_seat_groups msg ON msg.master_seat_id = ms.id JOIN shieldtech_admission.master_seat_group_details msgd ON msgd.master_seat_group_id = msg.id WHERE ms.year = :yr AND msgd.deleted_at IS NULL GROUP BY ms.unit_id, ms.year UNION ALL -- seat special (program + needed) SELECT mss.unit_id, mss.year, SUM(mssgddd.slot) AS total_seat FROM shieldtech_admission.master_seat_specials mss JOIN shieldtech_admission.master_seat_special_groups mssg ON mssg.master_seat_special_id = mss.id JOIN shieldtech_admission.master_seat_special_group_details mssgd ON mssgd.master_seat_special_group_id = mssg.id JOIN shieldtech_admission.master_seat_special_group_detail_details mssgddd ON mssgddd.master_seat_special_group_detail_id = mssgd.id WHERE mss.year = :yr AND mss.type_id IN (0,1) AND mssgddd.deleted_at IS NULL GROUP BY mss.unit_id, mss.year ) s JOIN shieldtech_common.units u ON u.id = s.unit_id LEFT JOIN ( SELECT unit_id, COUNT(id) AS reserved FROM shieldtech_admission.admission_student_profiles WHERE enrollment_school_year = :yr GROUP BY unit_id ) r ON r.unit_id = s.unit_id GROUP BY s.unit_id, u.short_name ORDER BY u.short_name;

SELECT DATE_FORMAT(ms.updated_at,'%Y-%m') ym, SUM(msgd.slot) slot_total, COUNT(DISTINCT sp.id) enroll_total FROM shieldtech_admission.master_seats ms JOIN shieldtech_admission.master_seat_groups msg ON ms.id = msg.master_seat_id JOIN shieldtech_admission.master_seat_group_details msgd ON msg.id = msgd.master_seat_group_id LEFT JOIN shieldtech_admission.admission_student_profiles sp ON ms.unit_id = sp.unit_id AND msg.program = sp.program AND msgd.grade = sp.grade AND DATE_FORMAT(ms.updated_at, '%Y-%m') = DATE_FORMAT(sp.enrollment_date, '%Y-%m') WHERE ms.year = YEAR(CURDATE()) GROUP BY ym ORDER BY ym ASC

SELECT DATE_FORMAT(sp.created_at, '%Y-%m') ym, COUNT(sp.id) total FROM shieldtech_admission.admission_student_profiles sp JOIN shieldtech_admission.student_assessments sa ON sp.id = sa.student_id WHERE sa.exceptional_education IS NOT NULL AND sa.exceptional_education != '[]' AND sa.exceptional_education != '{}' GROUP BY ym ORDER BY ym ASC