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

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

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

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


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

[https://develop.finki.ukim.mk/projects/BusNGo/wiki/AdvancedApplicationDesign/registracija Регистрација на корисник]

[https://develop.finki.ukim.mk/projects/BusNGo/wiki/AdvancedApplicationDesign/bilet Купување на билет]

[https://develop.finki.ukim.mk/projects/BusNGo/wiki/AdvancedApplicationDesign/vozenje Стартување на возење (патник)]

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

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

[https://develop.finki.ukim.mk/projects/BusNGo/wiki/AdvancedApplicationDesign/ruta Стартување на рута на автобус]

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

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

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

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

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

{{{#!div
{{{#!sql

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);


}}}
}}}

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

{{{#!div
{{{#!sql 

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);

}}}
}}}

[[Image(fines-per-line.png)]]


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

{{{#!div
{{{#!sql

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);

}}}
}}}

[[Image(number-of-activities-per-hour.png)]]

==== Commutes per line

{{{#!sql
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

{{{#!sql
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)

{{{#!sql
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;
}}}