| | | 1 | | using Dapper; |
| | | 2 | | using Npgsql; |
| | | 3 | | |
| | | 4 | | namespace Syki.Back.Features.Academic.GetAcademicInsights; |
| | | 5 | | |
| | 2 | 6 | | public class GetAcademicInsightsService(NpgsqlDataSource dataSource) : IAcademicService |
| | | 7 | | { |
| | | 8 | | public async Task<AcademicInsightsOut> Get(Guid institutionId) |
| | | 9 | | { |
| | 2 | 10 | | await using var connection = await dataSource.OpenConnectionAsync(); |
| | | 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 | | (SELECT COUNT(1) FROM syki.webhook_subscriptions WHERE institution_id = i.id) AS webhooks |
| | | 24 | | FROM |
| | | 25 | | syki.institutions i |
| | | 26 | | WHERE |
| | | 27 | | i.id = @Id |
| | | 28 | | "; |
| | | 29 | | |
| | 2 | 30 | | var parameters = new { Id = institutionId }; |
| | | 31 | | |
| | 2 | 32 | | return await connection.QueryFirstAsync<AcademicInsightsOut>(sql, parameters); |
| | 2 | 33 | | } |
| | | 34 | | } |