| | | 1 | | using Dapper; |
| | | 2 | | using Npgsql; |
| | | 3 | | |
| | | 4 | | namespace Syki.Back.Features.Academic.GetAcademicInsights; |
| | | 5 | | |
| | 1 | 6 | | public class GetAcademicInsightsService(DatabaseSettings settings) : IAcademicService |
| | | 7 | | { |
| | | 8 | | public async Task<AcademicInsightsOut> Get(Guid institutionId) |
| | | 9 | | { |
| | 1 | 10 | | using var connection = new NpgsqlConnection(settings.ConnectionString); |
| | | 11 | | |
| | | 12 | | const string sql = @" |
| | | 13 | | SELECT |
| | | 14 | | (SELECT COUNT(1) FROM syki.campi WHERE institution_id = i.id) AS campus, |
| | | 15 | | (SELECT COUNT(1) FROM syki.courses WHERE institution_id = i.id) AS courses, |
| | | 16 | | (SELECT COUNT(1) FROM syki.disciplines WHERE institution_id = i.id) AS disciplines, |
| | | 17 | | (SELECT COUNT(1) FROM syki.course_curriculums WHERE institution_id = i.id) AS course_curriculums, |
| | | 18 | | (SELECT COUNT(1) FROM syki.course_offerings WHERE institution_id = i.id) AS course_offerings, |
| | | 19 | | (SELECT COUNT(1) FROM syki.classes WHERE institution_id = i.id) AS classes, |
| | | 20 | | (SELECT COUNT(1) FROM syki.teachers WHERE institution_id = i.id) AS teachers, |
| | | 21 | | (SELECT COUNT(1) FROM syki.students WHERE institution_id = i.id) AS students, |
| | | 22 | | (SELECT COUNT(1) FROM syki.notifications WHERE institution_id = i.id) AS notifications |
| | | 23 | | FROM |
| | | 24 | | syki.institutions i |
| | | 25 | | WHERE |
| | | 26 | | i.id = @Id |
| | | 27 | | "; |
| | | 28 | | |
| | 1 | 29 | | var parameters = new { Id = institutionId }; |
| | | 30 | | |
| | 1 | 31 | | return await connection.QueryFirstAsync<AcademicInsightsOut>(sql, parameters); |
| | 1 | 32 | | } |
| | | 33 | | } |