DatabaseProgramming

Materialized Views

MaterializedViews.sql​

Functions

Functions.sql​

Procedures

Procedures.sql​

Triggers

Triggers.sql​

Materialized Views Explained

1. mv_fuel_consumption - Потрошувачка на гориво

• Го следи целото горивно досие за секое возило. За секое возило ги брои вкупните пристигнувања, вкупните литри и трошоци, просечните вредности по пристигнување, датумот на последно пристигнување и посебно потрошувачката само во последните 30 дена (и литри и трошоци). Ги вклучува сите возила дури и ако немаат ниту едно пристигнување.

2. mv_passenger_complaints - Жалби по патник

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

3. mv_revenue_by_route - Финансиски извештај по линија

• За секоја линија/рута ги прикажува: агенцијата, вкупниот број патувања, бројот на продадени билети, бруто-приходот (сума на сите цени), реално наплатениот приход односно само completed плаќањата и просечната цена на билет. Откажаните и рефундираните билети се исклучени.

4. mv_trip_delays - Доцнења по патување

• За секое патување со регистрирано доцнење ги прикажува: датумот, рутата, возилото, возачот, вкупниот број записи за доцнење, вкупните и просечните минути доцнење, максималното доцнење и сите причини споени во еден текст. Патувањата без доцнење не се прикажуваат.

5. mv_vehicle_occupany_stats - Патници по возило

• За секое возило го пресметува просечниот и максималниот број на патници врз основа на логираните записи во capacity_log, заедно со вкупниот број на тие записи.

Functions Explained

1. get_upcoming_trips_for_stop - Следни возења за постојка

• Ги враќа сите денешни возења за дадена постојка чие поаѓање сè уште не поминало, со статус scheduled или in_progress, подредени по време на поаѓање.

2. passenger_has_active_pass - Проверка на активна претплата

• Проверува дали патникот има активна претплата чиј краен датум сè уште не е поминат. Враќа true или false.

3. cancel_trip - Откажување возење и известување патници

• Го менува статусот на возењето во cancelled и автоматски испраќа pending нотификација до сите патници со валиден билет за тоа возење.

4. calculate_ticket_price - Пресметување цена со попуст

• Ја зема основната цена од тарифното правило и ако е зададен попуст (чиј рок на важење не е поминат), го применува процентот и ја враќа заокружената финална цена.

Procedures Explained

1. add_trip - Додавање ново возење

• Креира ново возење со зададен идентификатор, рута, возило, возач, датум, почетно и крајно време и опционален наслов. Возењето се внесува со статус scheduled.

2. buy_ticket - Купување билет

• Купува билет за патник на одредено возење. Ако е зададено тарифно правило, цената ја пресметува преку calculate_ticket_price, инаку ја зема base_price од типот на билет. Билетот се внесува со статус valid.

3. report_delay - Евидентирање доцнење и известување патници

• Го запишува доцнењето во delay_log, го менува статусот на возењето во delayed и автоматски испраќа pending нотификација до сите патници со валиден билет за тоа возење.

4. assign_discount_to_passenger - Доделување попуст на патник

• Му доделува попуст на патник со зададен датум на истекување. Пред внесување проверува дали патникот веќе го има тој попуст активен.

5. complete_trip - Завршување возење

• Го завршува возењето со проверка дали е во статус in_progress. По потврда го менува статусот на возењето во completed и ги означува сите валидни билети како used.

6. refund_ticket - Враќање билет

• Го рефундира билетот со проверка дали постои и дали е во статус valid. По потврда го менува статусот на билетот во refunded и го ажурира поврзаното плаќање исто така во refunded.

7. schedule_driver - Закажување распоред за возач

• Му закажува смена на возач за одреден датум со почетно и крајно време. Пред внесување проверува дали возачот веќе има закажано за тој датум.

Triggers Explained

1. trg_ticket_expire - Автоматско истекување на билет

• При внесување или ажурирање на билет, ако статусот е valid и датумот на купување е постар од 1 ден, автоматски го менува статусот во expired.

2. trg_vehicle_delete - Спречување бришење на активно возило

• Пред бришење на возило проверува дали има активни возења со статус scheduled или in_progress. Ако има, бришењето се блокира.

3. trg_pass_expire - Автоматско истекување на претплата

• При внесување или ажурирање на претплатата, ако крајниот датум е поминат и статусот е active, автоматски го менува статусот во expired.

4. trg_driver_double_schedule - Спречување двојно закажување на возач

• Пред внесување или ажурирање на распоред проверува дали возачот веќе има закажано за тоа време и датум. Ако има, операцијата се блокира.

5. trg_delay_notification - Автоматска нотификација при доцнење

• По внесување на ново доцнење, автоматски испраќа pending нотификација до сите патници со валиден билет за тоа возење, со порака за бројот на минути и причината.

6. trg_trip_vehicle_active - Проверка на статус на возило пред закажување

• Пред внесување на ново возење проверува дали возилото е со статус active. Ако не е, внесувањето се блокира.