| | | 1 | | namespace Syki.Back.Features.Academic.GetTeachers; |
| | | 2 | | |
| | 2 | 3 | | public class GetTeachersService(SykiDbContext ctx) : IAcademicService |
| | | 4 | | { |
| | | 5 | | public async Task<List<TeacherOut>> Get(Guid institutionId) |
| | | 6 | | { |
| | 2 | 7 | | FormattableString sql = $@" |
| | 2 | 8 | | SELECT |
| | 2 | 9 | | t.id, |
| | 2 | 10 | | t.name, |
| | 2 | 11 | | u.email, |
| | 2 | 12 | | COALESCE(tc.total, 0) AS campi, |
| | 2 | 13 | | COALESCE(td.total, 0) AS disciplines |
| | 2 | 14 | | FROM |
| | 2 | 15 | | syki.teachers t |
| | 2 | 16 | | INNER JOIN |
| | 2 | 17 | | syki.users u ON u.id = t.id |
| | 2 | 18 | | LEFT JOIN ( |
| | 2 | 19 | | SELECT syki_teacher_id, COUNT(1) AS total |
| | 2 | 20 | | FROM syki.teachers__campi |
| | 2 | 21 | | GROUP BY syki_teacher_id |
| | 2 | 22 | | ) tc ON tc.syki_teacher_id = t.id |
| | 2 | 23 | | LEFT JOIN ( |
| | 2 | 24 | | SELECT syki_teacher_id, COUNT(1) AS total |
| | 2 | 25 | | FROM syki.teachers__disciplines |
| | 2 | 26 | | GROUP BY syki_teacher_id |
| | 2 | 27 | | ) td ON td.syki_teacher_id = t.id |
| | 2 | 28 | | WHERE |
| | 2 | 29 | | u.institution_id = {institutionId} |
| | 2 | 30 | | ORDER BY |
| | 2 | 31 | | t.name |
| | 2 | 32 | | "; |
| | | 33 | | |
| | 2 | 34 | | return await ctx.Database.SqlQuery<TeacherOut>(sql).ToListAsync(); |
| | 2 | 35 | | } |
| | | 36 | | } |