Changes between Version 2 and Version 3 of Faza2

Timestamp:

06/11/26 13:43:01 (5 days ago)

Author:

231042

Comment:

--

Faza2

@@ -21 +21 @@
 1. '''new_event_ticket_sales_status'''
 * '''Опис:''' Овој поглед врши агрегација на продадени билети по настан и сектор. Се користи за генерирање извештаи за пополнетост на капацитетот на објектите.
+{{{
+CREATE OR REPLACE VIEW public.new_event_ticket_sales_status
+AS WITH preaggregatedtickets AS (
+         SELECT ticket.event_event_id,
+            ticket.section_section_id,
+            count(ticket.ticket_id) AS sold_per_section
+           FROM ticket
+          WHERE ticket.refund_refund_id IS NULL
+          GROUP BY ticket.event_event_id, ticket.section_section_id
+        )
+ SELECT e.event_id,
+    e.title,
+    v.capacity AS total_capacity,
+    sum(pat.sold_per_section) OVER (PARTITION BY e.event_id)::bigint AS total_sold,
+    (v.capacity::numeric - sum(pat.sold_per_section) OVER (PARTITION BY e.event_id))::bigint AS tickets_remaining,
+    s.section_name,
+    COALESCE(pat.sold_per_section, 0::bigint) AS sold_per_section
+   FROM event e
+     JOIN venue v ON e.venue_venue_id = v.venue_id
+     LEFT JOIN preaggregatedtickets pat ON e.event_id = pat.event_event_id
+     LEFT JOIN section s ON pat.section_section_id = s.section_id;
+}}}
 2. '''new_user_ticket_history'''
 * '''Опис:''' Погледот ги поврзува корисниците, резервациите, билетите и настаните за да прикаже детална историја на купени билети, цени и статус на настаните. Клучен е за приказ на корисничкиот профил.
+{{{
+CREATE OR REPLACE VIEW public.new_user_ticket_history
+AS SELECT u.user_id,
+    u.username,
+    t.ticket_id,
+    t.price,
+    t.is_used,
+    e.title AS event_title,
+    e.date AS event_date,
+    r.status AS reservation_status,
+        CASE
+            WHEN e.date < CURRENT_DATE THEN 'Past Event'::text
+            ELSE 'Upcoming Event'::text
+        END AS event_status
+   FROM "User" u
+     JOIN reservation r ON u.user_id = r.user_user_id
+     JOIN ticket t ON r.reservation_id = t.reservation_reservation_id
+     JOIN event e ON t.event_event_id = e.event_id;
+}}}
 3. '''new_active_events_3_months'''
 * '''Опис:''' Погледот служи за брзо дофаќање на сите активни настани во наредните 3 месеци и се користи за приказ на претстојните случувања на почетната страна.
+{{{
+CREATE OR REPLACE VIEW public.new_active_events_3_months
+AS SELECT e.event_id,
+    e.title,
+    e.event_type,
+    e.date,
+    v.venue_name,
+    c.city_name
+   FROM event e
+     JOIN venue v ON e.venue_venue_id = v.venue_id
+     JOIN city c ON v.city_city_id = c.city_id
+  WHERE e.date >= CURRENT_DATE AND e.date <= (CURRENT_DATE + '3 mons'::interval)
+  ORDER BY e.date;
+}}}