Changes between Version 3 and Version 4 of DatabaseProgramming

Timestamp:

06/08/26 18:17:22 (11 days ago)

Author:

231003

Comment:

--

DatabaseProgramming

@@ -123 +123 @@
 }}}
 
-=== `get_customer_monthly_spending`
-
-Оваа функција служи за пресметување на вкупната сума што одреден клиент ја има потрошено во даден месец за сите успешно завршени плаќања за билети (single или pass).
-
-{{{
-
-CREATE OR REPLACE FUNCTION get_customer_monthly_spending(
-    p_customer_id BIGINT,
-    p_month DATE
-)
-RETURNS NUMERIC
-LANGUAGE plpgsql
-AS $$
+=== `count_active_lines`
+
+Оваа функција е имплементирана со цел да обезбеди брз и едноставен начин за добивање на бројот на активни линии во системот. Нејзината намена е да поддржи анализа и следење на состојбата на транспортната мрежа преку централизирано пресметување на активните линии.
+
+{{{
+
+CREATE OR REPLACE FUNCTION count_active_lines()
+RETURNS INTEGER AS $$
 DECLARE
-    v_month_start DATE;
-    v_month_end DATE;
-    v_total NUMERIC;
-BEGIN
-    IF NOT EXISTS (
-        SELECT 1
-        FROM Customer c
-        WHERE c.user_id = p_customer_id
-    ) THEN
-        RAISE EXCEPTION 'Customer with id % does not exist.', p_customer_id;
-    END IF;
-
-    v_month_start := date_trunc('month', p_month)::date;
-    v_month_end := (v_month_start + INTERVAL '1 month')::date;
-
-    SELECT
-        COALESCE(SUM(COALESCE(st.amount, pass.amount, 0)), 0)
-    INTO v_total
-    FROM Customer_Payment_Ticket cpt
-    JOIN Payment p
-        ON cpt.payment_id = p.payment_id
-    JOIN Ticket t
-        ON cpt.ticket_id = t.ticket_id
-    LEFT JOIN Single_ticket st
-        ON t.ticket_id = st.ticket_id
-    LEFT JOIN Pass_ticket pt
-        ON t.ticket_id = pt.ticket_id
-    LEFT JOIN Pass_type pass
-        ON pt.type_id = pass.id
-    WHERE cpt.user_id = p_customer_id
-      AND p.status = 'Completed'::payment_status
-      AND p.payment_date >= v_month_start
-      AND p.payment_date < v_month_end;
-
-    RETURN v_total;
-END;
-$$;
-
-}}}
-
-=== `get_driver_shift_count`
-
-Оваа функција служи за да го пресмета бројот на смени (line assignments) што одреден возач ги има извршено во даден временски интервал.
-
-{{{
-
-CREATE OR REPLACE FUNCTION get_driver_shift_count(
-    p_driver_id BIGINT,
-    p_date_from TIMESTAMP,
-    p_date_to TIMESTAMP
-)
-RETURNS INTEGER
-LANGUAGE plpgsql
-AS $$
-DECLARE
-    v_count INTEGER;
-BEGIN
-    IF p_date_from >= p_date_to THEN
-        RAISE EXCEPTION 'Start date must be before end date.';
-    END IF;
-
-    IF NOT EXISTS (
-        SELECT 1
-        FROM Driver d
-        WHERE d.user_id = p_driver_id
-    ) THEN
-        RAISE EXCEPTION 'Driver with id % does not exist.', p_driver_id;
-    END IF;
-
+    v_active_count INTEGER;
+BEGIN
     SELECT COUNT(*)
-    INTO v_count
-    FROM Line_assignment la
-    WHERE la.driver_id = p_driver_id
-      AND la.start_time >= p_date_from
-      AND la.start_time < p_date_to;
-
-    RETURN v_count;
-END;
-$$;
-
+    INTO v_active_count
+    FROM Line
+    WHERE is_active = TRUE;
+    
+    RETURN v_active_count;
+END;
+$$ LANGUAGE plpgsql;
+
+SELECT count_active_lines();
 }}}
 
@@ -439 +366 @@
     RAISE NOTICE 'Line assignment created. assignment_id=%', v_assignment_id;
 END;
+$$;
+
+}}}
+
+=== `generate_line_assignment`
+
+Оваа процедура служи за внесување на нова распределба на линија, при што се зачувуваат информациите за возачот, администраторот, автобусот и возниот ред што ќе се користи. Со нејзина помош процесот на креирање на распределби е поедноставен и се намалува можноста за грешки при внесувањето на податоците.
+
+{{{
+
+CREATE OR REPLACE PROCEDURE generate_line_assignment(
+    assignment_driver_id BIGINT,
+    assignment_admin_id BIGINT,
+    assignment_chassis_number VARCHAR,
+    assignment_schedule_id INT,
+    assignment_start_time TIMESTAMP,
+    assignment_end_time TIMESTAMP
+)
+LANGUAGE plpgsql AS $$
+
+DECLARE
+    new_assignment_id BIGINT;
+
+BEGIN
+
+    INSERT INTO Line_assignment(
+        driver_id,
+        admin_id,
+        chassis_number,
+        schedule_id,
+        start_time,
+        end_time
+    )
+    VALUES(
+        assignment_driver_id,
+        assignment_admin_id,
+        assignment_chassis_number,
+        assignment_schedule_id,
+        assignment_start_time,
+        assignment_end_time
+    )
+    RETURNING assignment_id INTO new_assignment_id;
+
+    COMMIT;
+
+END;
+$$;
+
+}}}
+
+=== `register_customer`
+
+Оваа процедура овозможува регистрација на нов корисник во системот преку внесување на неговите лични податоци и автоматско креирање на запис во табелата за патници. Со тоа се обезбедува едноставен и организиран процес на регистрација, при што податоците за корисникот и неговиот тип се зачувуваат на соодветните места во базата на податоци.
+
+{{{
+
+create or replace procedure register_customer(
+    customer_name varchar,
+    customer_surname varchar,
+    customer_email varchar,
+    customer_password varchar,
+    customer_address varchar,
+    customer_birth date,
+    customer_phone varchar,
+    customer_id_card varchar,
+    customer_type_value customer_type
+)
+language plpgsql as $$
+
+declare
+    new_user_id bigint;
+
+begin
+
+    insert into ApplicationUser(
+        name,
+        surname,
+        email,
+        password,
+        address,
+        date_of_birth,
+        phone_number,
+        id_card
+    )
+    values(
+        customer_name,
+        customer_surname,
+        customer_email,
+        customer_password,
+        customer_address,
+        customer_birth,
+        customer_phone,
+        customer_id_card
+    )
+    returning user_id into new_user_id;
+
+    insert into Customer(user_id, type)
+    values(new_user_id, customer_type_value);
+
+    commit;
+
+end;
 $$;
 
@@ -533 +562 @@
 
 }}}
+
+=== `payment_expired`
+
+Овој тригер автоматски го проверува статусот на секоја уплата при нејзино внесување или ажурирање во системот. Доколку уплатата е сè уште во статус „Pending“, а датумот за плаќање е поминат, статусот автоматски се менува во „Failed“, со што се обезбедува точно евидентирање на неуспешните уплати.
+
+{{{
+
+create or replace function payment_expired()
+returns trigger as $$
+
+begin
+
+    if NEW.status = 'Pending'
+    and NEW.payment_date < current_date then
+
+        NEW.status := 'Failed';
+
+    end if;
+
+    return NEW;
+
+end;
+$$ language plpgsql;
+
+create trigger payment_status_trigger
+before insert or update on Payment
+for each row
+execute function payment_expired();
+
+}}}