Напреден апликативен дизајн

Напредниот апликативен дизајн има за цел да ги примени сите опишани кориснички сценарија и улоги од првите фази во финалната апликација. За таа цел создадена е нова апликација која користи модерни технологии.

За базата повторно се користи Postgres 16 базата, за backend се користи Spring Boot API напишан во Kotlin и за frontend се користи Angular 18.

Во пасусите во продолжение ќе бидат објаснети и покажани сите кориснилки сценарија имплементирани во апликацијата.

Кориснички сценарија

Регистрација на корисник

Купување на билет

Стартување на возење (патник)

Вршење на контрола

Пишување казна

Стартување на рута на автобус

Материјализирани views

Напредните извештаи од минатите фази се искористени за да се направат materialized views. Материјализираните views значително го намалуваат времето на извршување на квери поради тоа што го чуваат и резултатот. Ова е соодветно поради тоа што ваквите статистики нема потреба да бидат up to date во секој момент, доволно е да се рефрешираат на одреден период (на пример 12 часа).

Во продолжение се прикажани материјалиизираните views (за некои од нив има и графици кои може да се погледнат во амин панелот).

Средна разлика на простигнување на инстанците од постојка А до постојка Б

Идејата е да може да се има естимација на време за кое автобусот би стигнал на одредена постојка.

CREATE MATERIALIZED VIEW avg_time_diffs AS
WITH ordered_instanca AS (SELECT ilpl.instanca_na_linija_id,
                                 ilpl.postojka_na_linija_id,
                                 pnl.reden_broj,
                                 ilpl.timestamp
                          FROM instanca_na_linija_postojka_na_linija ilpl
                                   JOIN
                               postojka_na_linija pnl ON ilpl.postojka_na_linija_id = pnl.id
                          ORDER BY ilpl.instanca_na_linija_id,
                                   pnl.reden_broj),
     time_differences AS (SELECT o1.instanca_na_linija_id,
                                 o1.postojka_na_linija_id                          AS start_station_id,
                                 o1.timestamp                                      AS start_time,
                                 o2.postojka_na_linija_id                          AS end_station_id,
                                 o2.timestamp                                      AS end_time,
                                 EXTRACT(EPOCH FROM (o2.timestamp - o1.timestamp)) AS time_diff
                          FROM ordered_instanca o1
                                   JOIN
                               ordered_instanca o2 ON o1.instanca_na_linija_id = o2.instanca_na_linija_id
                          WHERE o1.reden_broj + 1 = o2.reden_broj)
SELECT
    row_number() over () as id,
    start_station_id,
       end_station_id,
       AVG(time_diff)::double precision AS avg_time_diff_seconds
FROM time_differences
GROUP BY start_station_id,
         end_station_id
ORDER BY start_station_id,
         end_station_id;

create unique index avg_time_diffs_index on avg_time_diffs (id);

Број на казни според линија

create materialized view kazna_po_linija as
select row_number() over () as id, l.id as line_id, count(l.id)
from linija l
         left join instanca_na_linija inl on l.id = inl.linija_id
         left join kontrola k on inl.id = k.instanca_na_linija_id
         left join kazna kaz on k.id = kaz.kontrola_id
where extract(year from kaz.date_created) = extract(year from now())
group by l.id
order by count(l.id) desc;

create unique index index_kazna_po_linija on kazna_po_linija(id);

Зафатеност на линиите по интервали од 1 час

create materialized view most_busy_part_of_the_day as
select row_number() over () as id, id as linija_id, interval_1h, count(id || '-' || interval_1h) broj_pati
from (select li.id,
             (to_char(extract(hour from v.start_date), '00') || ':00 -' || to_char(extract(hour from (v.start_date
                 + '1 hours'::interval))::int, '00') || ':00') as interval_1h
      from linija li
               left join instanca_na_linija inl on li.id = inl.linija_id
               left join vozenje v on inl.id = v.instanca_na_linija_id
      where v.start_date between (make_date(extract(year from (now()))::int, 1, 1)) and (make_date(extract(year from (now()))::int, 12, 31))) as hours
group by id, interval_1h
order by interval_1h desc;

create unique index index_most_busy_part_of_the_day on most_busy_part_of_the_day(id);

Commutes per line

create materialized view commutes_by_line as
select row_number() over () as id, l.id as linija_id, count(v.id)
from linija l
         left join instanca_na_linija inl on l.id = inl.linija_id
         left join vozenje v on inl.id = v.instanca_na_linija_id
where v.start_date between date_trunc('year', now()) and date_trunc('month', date_trunc('year', now())+ interval '7 months')
group by l.id;

Number of passengers per line and station

{{#!sql create materialized view number_passengers_per_line_and_station as select row_number() over () as id, p.id as station_id, l.id as line_id, count(p.id) from postojka p

join postojka_na_linija pnl on p.id = pnl.postojka_id join vozenje v on pnl.id = v.postojka_na_linija_start_id join patnik pat on v.patnik_id = pat.id join linija l on pnl.linija_id = l.id

where v.start_date between (make_date(extract(year from (now() - '1 years'::interval))::int, 4, 1)) and (make_date(extract(year from (now() - '1 years'::interval))::int, 6, 30)) group by p.id, l.id order by count(p.id) desc; }}

Ticket sales

create materialized view ticket_sales as
select row_number() over () as id, filtered.tip_id, filtered.interval_1h, filtered.broj_pati
from (select filter.tip_id, interval_1h, count(tip_id || '-' || interval_1h) broj_pati
      from (select *,
                   (to_char(extract(hour from b.datum_kupuvanje), '00') || ':00 -' ||
                    to_char(extract(hour from (b.datum_kupuvanje + '1 hours'::interval))::int, '00') ||
                    ':00') as interval_1h
            from tipbilet t
                     left join bilet b on t.id = b.tip_id
--     where  b.datum_kupuvanje between (make_date(extract(year from (now() - '1 years'::interval))::int, 1, 1)) and (make_date(extract(year from (now() - '1 years'::interval))::int, 12, 31))

           ) as filter
      group by filter.tip_id, interval_1h) filtered
where broj_pati = (select max(filtered.broj_pati)
                   from (select filter.tip_id, interval_1h, count(tip_id || '-' || interval_1h) broj_pati
                         from (select *,
                                      (to_char(extract(hour from b.datum_kupuvanje), '00') || ':00 -' ||
                                       to_char(extract(hour from (b.datum_kupuvanje + '1 hours'::interval))::int,
                                               '00') ||
                                       ':00') as interval_1h
                               from tipbilet t
                                        left join bilet b on t.id = b.tip_id
--     where  b.datum_kupuvanje between (make_date(extract(year from (now() - '1 years'::interval))::int, 1, 1)) and (make_date(extract(year from (now() - '1 years'::interval))::int, 12, 31))

                              ) as filter
                         group by filter.tip_id, interval_1h) filtered)
  and tip_id in (select id
                 from tipbilet
                 where ime in ('Hourly', 'Daily'));

Total income (from ticket sales and fines)

create materialized view total_income as
WITH kazna_sum AS (
    SELECT
        EXTRACT(YEAR FROM k.date_payed) AS year,
        SUM(k.iznos) AS sum_kz
    FROM
        kazna k
    WHERE
        k.plateno = TRUE
    GROUP BY
        EXTRACT(YEAR FROM k.date_payed)
),
     bilet_sum AS (
         SELECT
             EXTRACT(YEAR FROM b.datum_kupuvanje) AS year,
             SUM(tb.cena) AS sum_b
         FROM
             bilet b
                 JOIN tipbilet tb ON tb.id = b.tip_id
         GROUP BY
             EXTRACT(YEAR FROM b.datum_kupuvanje)
     )
SELECT
    row_number() over () as id,
    COALESCE(k.year, b.year)::text AS year,
    COALESCE(sum_kz, 0) as fines_income,
    COALESCE(sum_b, 0) as ticket_income,
    COALESCE(sum_kz, 0) + COALESCE(sum_b, 0) AS total_income
FROM
    kazna_sum k
        FULL OUTER JOIN bilet_sum b ON k.year = b.year
ORDER BY
    year desc;