| | | 1 | | using Dapper; |
| | | 2 | | |
| | | 3 | | namespace Estud.Back.Features.Notifications.GetInstitutionNotifications; |
| | | 4 | | |
| | 0 | 5 | | public class GetInstitutionNotificationsService(EstudDbContext ctx) : IEstudService |
| | | 6 | | { |
| | | 7 | | public async Task<OneOf<GetInstitutionNotificationsOut, EstudError>> Get(GetInstitutionNotificationsIn data) |
| | | 8 | | { |
| | 0 | 9 | | var connection = await ctx.GetOpenConnectionAsync(); |
| | | 10 | | |
| | 0 | 11 | | var sql = @" |
| | 0 | 12 | | SELECT |
| | 0 | 13 | | n.id, |
| | 0 | 14 | | n.title, |
| | 0 | 15 | | n.created_at, |
| | 0 | 16 | | n.description, |
| | 0 | 17 | | COUNT(un.user_id) AS recipients, |
| | 0 | 18 | | COUNT(un.viewed_at) AS viewed, |
| | 0 | 19 | | COUNT(*) OVER() AS total_rows |
| | 0 | 20 | | FROM |
| | 0 | 21 | | estud.notifications n |
| | 0 | 22 | | LEFT JOIN |
| | 0 | 23 | | estud.user_notifications un ON un.notification_id = n.id |
| | 0 | 24 | | WHERE |
| | 0 | 25 | | n.institution_id = @InstitutionId |
| | 0 | 26 | | AND n.notification_type = @NotificationType |
| | 0 | 27 | | GROUP BY |
| | 0 | 28 | | n.id, n.title, n.description, n.created_at |
| | 0 | 29 | | ORDER BY |
| | 0 | 30 | | n.created_at DESC |
| | 0 | 31 | | LIMIT @PageSize |
| | 0 | 32 | | OFFSET @Offset |
| | 0 | 33 | | "; |
| | | 34 | | |
| | 0 | 35 | | var parameters = new |
| | 0 | 36 | | { |
| | 0 | 37 | | data.PageSize, |
| | 0 | 38 | | ctx.RequestUser.InstitutionId, |
| | 0 | 39 | | Offset = (data.Page - 1) * data.PageSize, |
| | 0 | 40 | | NotificationType = (int)NotificationType.Custom, |
| | 0 | 41 | | }; |
| | | 42 | | |
| | 0 | 43 | | var rows = (await connection.QueryAsync<InstitutionNotificationRow>(sql, parameters)).ToList(); |
| | | 44 | | |
| | 0 | 45 | | var items = rows.ConvertAll(r => new GetInstitutionNotificationsItemOut |
| | 0 | 46 | | { |
| | 0 | 47 | | Id = r.Id, |
| | 0 | 48 | | Title = r.Title, |
| | 0 | 49 | | Viewed = r.Viewed, |
| | 0 | 50 | | CreatedAt = r.CreatedAt, |
| | 0 | 51 | | Recipients = r.Recipients, |
| | 0 | 52 | | Description = r.Description, |
| | 0 | 53 | | ViewRate = r.Recipients > 0 ? Math.Round(100M * (1M * r.Viewed / (1M * r.Recipients)), 2) : 0, |
| | 0 | 54 | | }); |
| | | 55 | | |
| | 0 | 56 | | return new GetInstitutionNotificationsOut |
| | 0 | 57 | | { |
| | 0 | 58 | | Items = items, |
| | 0 | 59 | | Page = data.Page, |
| | 0 | 60 | | PageSize = data.PageSize, |
| | 0 | 61 | | Total = rows.FirstOrDefault()?.TotalRows ?? 0, |
| | 0 | 62 | | }; |
| | 0 | 63 | | } |
| | | 64 | | } |